Introduction:
This Post illustrates the steps required to Sales Order Loader API from Blanket Sales Agreement in Oracle EBS R12
Script to Sales Order Loader API from Blanket Sales Agreement
CREATE OR REPLACE PACKAGE XX_SO_PROG_PKG as
–***************************************************************************************************
—
— All rights reserved
–***************************************************************************************************
—
— Package Name : XX_SO_PROG_PKG
—
— Description : Sales Order Interface
—
— DEVELOPMENT/MAINTENANCE HISTORY
—
— date author Version Description
— ———– —————- ——- —————————————-
—
–**************************************************************************************************
gn_sqlldr_req_id NUMBER ;
PROCEDURE MAIN(
p_errbuf OUT VARCHAR2 ,
p_retcode OUT NUMBER,
p_warehouse IN VARCHAR2);
PROCEDURE VALIDATIONS(p_warehouse IN VARCHAR2);
END XX_SO_PROG_PKG;
/
CREATE OR REPLACE PACKAGE BODY XX_SO_PROG_PKG as
–***************************************************************************************************
—
— All rights reserved
–***************************************************************************************************
—
— Package Name : XX_SO_PROG_PKG
—
— Description : Sales Order Interface
—
— DEVELOPMENT/MAINTENANCE HISTORY
—
— date author Version Description
–**************************************************************************************************
———————————
— Procedure MAIN
———————————
PROCEDURE MAIN(
p_errbuf OUT VARCHAR2 ,
p_retcode OUT NUMBER,
p_warehouse IN VARCHAR2
)
as
gn_conc_req_id NUMBER := fnd_global.conc_request_id;
gn_parent_req_id NUMBER := 0;
l_api_version_number NUMBER := 1.0;
l_return_status VARCHAR2(2000);
l_msg_count NUMBER;
l_msg_data VARCHAR2(2000);
p_init_msg_list VARCHAR2(10) := fnd_api.g_false;
p_return_values VARCHAR2(10) := fnd_api.g_false;
p_action_commit VARCHAR2(10) := fnd_api.g_false;
l_debug_level NUMBER := 5; — om debug level (max 5)
l_msg_index NUMBER;
n_line_counter NUMBER;
BILLING_ORD_TYPE_ID NUMBER;
n_ord_type_id NUMBER;
n_order_src_id NUMBER;
n_ord_counter NUMBER;
n_ord_err_counter NUMBER;
b_msg_count NUMBER;
l_loop_count NUMBER;
l_data VARCHAR2(2000) := NULL;
l_debug_file VARCHAR2(200);
b_return_status VARCHAR2(200);
b_msg_data VARCHAR2(2000);
v_src_ord_no VARCHAR2(10);
e_exception EXCEPTION;
v_message VARCHAR2(240);
v_msg_data VARCHAR2(2000);
n_err_count NUMBER;
d_order_date DATE;
d_usage_date DATE; — Arun 19-Feb-2015
v_order_qty NUMBER := 0; — 7-APR-2015
v_period VARCHAR2(6);
n_invoive_rule_id NUMBER;
n_account_rule_id NUMBER;
d_service_start_date DATE;
d_service_end_date DATE;
v_warehouse VARCHAR2(25);
lc_hdr_message VARCHAR2(400) := NULL;
l_custno VARCHAR2(200) := NULL;
——————-
— in variables —
——————-
l_header_rec oe_order_pub.header_rec_type;
l_line_tbl oe_order_pub.line_tbl_type;
l_action_request_tbl oe_order_pub.request_tbl_type;
——————-
— out variables
——————-
l_header_rec_out oe_order_pub.header_rec_type;
l_header_val_rec_out oe_order_pub.header_val_rec_type;
l_header_adj_tbl_out oe_order_pub.header_adj_tbl_type;
l_header_adj_val_tbl_out oe_order_pub.header_adj_val_tbl_type;
l_header_price_att_tbl_out oe_order_pub.header_price_att_tbl_type;
l_header_adj_att_tbl_out oe_order_pub.header_adj_att_tbl_type;
l_header_adj_assoc_tbl_out oe_order_pub.header_adj_assoc_tbl_type;
l_header_scredit_tbl_out oe_order_pub.header_scredit_tbl_type;
l_header_scredit_val_tbl_out oe_order_pub.header_scredit_val_tbl_type;
l_line_tbl_out oe_order_pub.line_tbl_type;
l_line_val_tbl_out oe_order_pub.line_val_tbl_type;
l_line_adj_tbl_out oe_order_pub.line_adj_tbl_type;
l_line_adj_val_tbl_out oe_order_pub.line_adj_val_tbl_type;
l_line_price_att_tbl_out oe_order_pub.line_price_att_tbl_type;
l_line_adj_att_tbl_out oe_order_pub.line_adj_att_tbl_type;
l_line_adj_assoc_tbl_out oe_order_pub.line_adj_assoc_tbl_type;
l_line_scredit_tbl_out oe_order_pub.line_scredit_tbl_type;
l_line_scredit_val_tbl_out oe_order_pub.line_scredit_val_tbl_type;
l_lot_serial_tbl_out oe_order_pub.lot_serial_tbl_type;
l_lot_serial_val_tbl_out oe_order_pub.lot_serial_val_tbl_type;
l_action_request_tbl_out oe_order_pub.request_tbl_type;
——————————————–
–CURSOR CUR_HEADERS
——————————————–
CURSOR CUR_HEADERS(l_warehouse VARCHAR2) IS
SELECT distinct
obha.order_number
,sold_to_org_id
,INVOICE_TO_ORG_ID
,SHIP_TO_ORG_ID
,TRANSACTIONAL_CURR_CODE
,CONVERSION_TYPE_CODE
,OBHE.start_date_active
,org_id
,cust_po_number,OBHA.ATTRIBUTE1
FROM ONT.OE_BLANKET_HEADERS_ALL OBHA,
ONT.oe_blanket_headers_ext OBHE
WHERE 1=1
AND OBHA.CONTEXT=’XXXX’
And LAST_DAY(to_date(’01-‘ || NVL(OBHA.ATTRIBUTE1,’JAN-14’), ‘DD-MON-YY’)) <= D_USAGE_DATE – 1
AND OBHA.ORDER_NUMBER=OBHE.ORDER_NUMBER
AND OBHA.FLOW_STATUS_CODE=’ACTIVE’
AND OBHA.ship_from_org_id = (select organization_id from org_organization_definitions
where organization_code = l_warehouse)
ORDER BY SOLD_TO_ORG_ID desc;
——————————————–
–CURSOR CUR_LINES
——————————————–
CURSOR CUR_LINES(sold_to_org_id_i IN Number,
INVOICE_TO_ORG_ID_i IN NUMBER,
ship_TO_ORG_ID_i IN NUMBER,
TRANSACTIONAL_CURR_CODE_i IN varchar2,
CONVERSION_TYPE_CODE_I IN VARCHAR2,
start_date_active_I IN DATE,
org_id_i IN Number) IS
SELECT ‘ORA’ SRC
,null ORDERS_ID
,OBHA.org_id
–,OBHA.ORDER_NUMBER SALES_AGREEMENT_NUMBER
,obla.ship_to_org_id
,obla.invoice_to_org_id
,OBLA.SHIP_FROM_ORG_ID
,OBHA.sold_to_org_id
,to_number(nvl(obla.attribute5,’0′)) SA_ORDER_QTY
,to_number(nvl(obla.attribute7,’0′)) MIN_ORDER_QTY
,nvl((select SUM(order_quantity)
from XX_ORDERS_STG
where order_item=obla.ordered_item
and UPPER(inventory_org) =(select UPPER(organization_name) from APPS.ORG_ORGANIZATION_DEFINITIONS where organization_id = obla.ship_from_org_id)
and customer_number =(select account_number from APPS.hz_cust_accounts where cust_account_id = obla.sold_to_org_id )),
‘0’) ordered_quantity
,MSI.INVENTORY_ITEM_ID
,obla.ordered_item
,to_number(obla.ATTRIBUTE1) PUE
,DECODE(OBLA.ATTRIBUTE4,’S’,to_number(nvl(obla.attribute6,’0′)),’D’,
nvl( (select SUM(price)
from XX_ORDERS_STG
where order_item=obla.ordered_item
and UPPER(inventory_org)=(select UPPER(organization_name) from APPS.ORG_ORGANIZATION_DEFINITIONS where organization_id=obla.ship_from_org_id)
and customer_number=(select account_number from APPS.hz_cust_accounts where cust_account_id=obla.sold_to_org_id)),to_number(nvl(obla.attribute6,’0′)))
) price
,OBLA.ACCOUNTING_RULE_ID
,OBLA.INVOICING_RULE_ID
,OBLA.ATTRIBUTE4 DYNAMIC_STATIC
,OBLA.ATTRIBUTE2
,OBLA.ATTRIBUTE9
FROM ONT.OE_BLANKET_HEADERS_ALL OBHA,
ONT.oe_blanket_headers_ext OBHE,
ONT.OE_BLANKET_LINES_ALL OBLA,
ONT.oe_blanket_lines_ext OBLE,
INV.MTL_SYSTEM_ITEMS_B MSI
WHERE 1=1
AND OBHA.HEADER_ID = OBLA.HEADER_ID
And LAST_DAY(to_date(’01-‘ || NVL(OBHA.ATTRIBUTE1,’JAN-14’), ‘DD-MON-YY’)) <= D_USAGE_DATE – 1
–AND NVL(OBHA.ATTRIBUTE1,’ABC’)<>TO_CHAR(SYSDATE,’MON’)
AND OBHA.ORDER_NUMBER=OBHE.ORDER_NUMBER
AND OBHA.ORDER_NUMBER=OBLE.ORDER_NUMBER
AND OBLA.LINE_NUMBER=OBLE.LINE_NUMBER
AND MSI.ORGANIZATION_ID=OBLA.SHIP_FROM_ORG_ID
AND MSI.SEGMENT1=OBLA.ORDERED_ITEM
— AND TRUNC(SYSDATE) BETWEEN OBHE.START_DATE_ACTIVE AND NVL(OBHE.END_DATE_ACTIVE,SYSDATE+1)
AND OBHA.FLOW_STATUS_CODE=’ACTIVE’
AND OBHA.CONTEXT=’XXXX’
AND OBLA.CONTEXT=’XXXX’
AND TRUNC(D_USAGE_DATE) BETWEEN NVL(TO_DATE(OBLA.ATTRIBUTE2,’YYYY/MM/DD HH24:MI:SS’),D_USAGE_DATE-1 ) AND
NVL(TO_DATE(OBLA.ATTRIBUTE3,’YYYY/MM/DD HH24:MI:SS’),D_USAGE_DATE+1 )
AND NVL(OBLA.ATTRIBUTE4,’ABC’) <> ‘P’
AND OBHA.sold_to_org_id=sold_to_org_id_i
AND OBHA.INVOICE_TO_ORG_ID=INVOICE_TO_ORG_ID_I
AND OBHA.ship_TO_ORG_ID=ship_TO_ORG_ID_I
AND OBHA.TRANSACTIONAL_CURR_CODE=TRANSACTIONAL_CURR_CODE_I
AND nvl(OBHA.CONVERSION_TYPE_CODE,’NULL’)=nvl(CONVERSION_TYPE_CODE_I,’NULL’)
AND OBHE.start_date_active=start_date_active_I
AND OBHa.org_id=org_id_i
ORDER BY OBHA.sold_to_org_id,OBLE.LINE_NUMBER;
BEGIN
BEGIN
SELECT DISTINCT fcr.priority_request_id
INTO gn_parent_req_id
FROM fnd_concurrent_requests fcr,
Fnd_Concurrent_Programs fcp
WHERE fcr.request_id = gn_conc_req_id
AND fcr.concurrent_program_id = fcp.concurrent_program_id;
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line(fnd_File.log,’Error while fetching the parent request id XX_SO_PROG_PKG.MAIN – ‘|| gn_parent_req_id||’ – ‘||SQLERRM);
END;
fnd_file.put_line(fnd_file.log,’XXXX Sales Order Interface started’);
IF (l_debug_level > 0) THEN
l_debug_file := OE_DEBUG_PUB.Set_Debug_Mode(‘FILE’);
oe_debug_pub.initialize;
oe_debug_pub.setdebuglevel(l_debug_level);
Oe_Msg_Pub.initialize;
END IF;
mo_global.init(‘ONT’);
mo_global.set_policy_context(‘M’,85);
fnd_global.apps_initialize ( FND_GLOBAL.USER_ID , FND_GLOBAL.RESP_ID ,FND_GLOBAL.RESP_APPL_ID);
VALIDATIONS(p_warehouse);
n_err_count:=0;
n_ord_counter:=0;
n_ord_err_counter:=0;
select count(*) into n_err_count from XX_ORDERS_STG where status=’E’
AND upper(inventory_org) = (select upper(organization_name) from org_organization_definitions
where organization_code = p_warehouse);
IF n_err_count>0
THEN
v_message:= ‘There are exceptions in the spreadsheet data. Please look at the output.’;
raise e_exception;
END IF;
—
–Derive order type
—
begin
select transaction_type_id
into BILLING_ORD_TYPE_ID
from ONT.OE_TRANSACTION_TYPES_TL
where 1=1
and UPPER(name)=’XXXX EOM BILLING’
and language=’US’;
exception
when others
then
v_message:=’Unable to derive order type Id’;
raise e_exception;
end;
—
— Derive Order source
—
begin
SELECT order_source_id
into n_order_src_id
FROM ONT.OE_ORDER_SOURCES OOS
WHERE NAME=’XXXX’;
exception
when others
then
v_message:=’Unable to derive order source Id’;
raise e_exception;
end;
—
— Derive Order date and period
—
begin
select min(last_day(to_date(order_period,’YY-Mon’))),to_char(min(last_day(to_date(order_period,’YY-Mon’))),’MON-YY’)
into d_order_date,v_period
from XX_ORDERS_STG
WHERE upper(inventory_org) = (select upper(organization_name) from org_organization_definitions
where organization_code = p_warehouse);
D_USAGE_DATE := TO_Date(’01-‘ || V_Period,’DD-MON-YY’);
— D_USAGE_DATE := D_USAGE_DATE – 1;
exception
when others
then
v_message:=’Unable to derive order date’;
raise e_exception;
end;
If V_Period IS Null Then
P_RetCode := 2;
P_ErrBuf := ‘There is no record in the staging table to derive Order Date to process the Sales Agreements’;
Return;
End If;
fnd_file.put_line(fnd_file.log,
‘Usage Period is ‘ || V_Period || ‘, Usage Date is on or before ‘ || to_Char(D_USAGE_DATE,’DD-MON-RRRR’) ||
‘ and Ordered Date is ‘ || to_Char(D_ORDER_DATE,’DD-MON-RRRR’)||’warehouse ‘||p_warehouse);
lc_hdr_message := RPAD(‘Customer_Number’,20)||RPAD(‘ Sales_Agreement_Number ‘,25)||RPAD(‘ Period ‘,10)||RPAD(‘ Order_Number ‘,10);
fnd_file.put_line(fnd_file.output,lc_hdr_message );
for hdr_dtls in cur_headers(p_warehouse)
loop
–DBMS_LOCK.Sleep( 30 );
n_line_counter:=0;
v_src_ord_no:=null;
fnd_file.put_line(fnd_file.log,’=======================================================’);
fnd_file.put_line(fnd_file.log,’———————–Header ————————-‘);
fnd_file.put_line(fnd_file.log,’Sold to Org id-‘||hdr_dtls.sold_to_org_id||’-‘||hdr_dtls.INVOICE_TO_ORG_ID||’-‘||hdr_dtls.ship_TO_ORG_ID||’-‘||hdr_dtls.TRANSACTIONAL_CURR_CODE||’-‘||hdr_dtls.CONVERSION_TYPE_CODE||’-‘||hdr_dtls.start_date_active||’-‘||hdr_dtls.org_id);
l_header_rec := oe_order_pub.G_MISS_HEADER_REC;
l_header_rec.operation := OE_GLOBALS.G_OPR_CREATE;
l_header_rec.order_type_id := BILLING_ORD_TYPE_ID;
l_header_rec.sold_to_org_id := hdr_dtls.sold_to_org_id;
l_header_rec.ship_to_org_id := hdr_dtls.ship_to_org_id;
l_header_rec.invoice_to_org_id := hdr_dtls.invoice_to_org_id;
l_header_rec.sold_from_org_id := hdr_dtls.org_id;
l_header_rec.cust_po_number := hdr_dtls.cust_po_number;
l_header_rec.salesrep_id := -3;
l_header_rec.pricing_date := SYSDATE;
l_header_rec.flow_status_code := ‘ENTERED’;
l_header_rec.order_source_id := n_order_src_id;
l_header_rec.TRANSACTIONAL_CURR_CODE := hdr_dtls.TRANSACTIONAL_CURR_CODE;
l_header_rec.CONVERSION_TYPE_CODE := hdr_dtls.CONVERSION_TYPE_CODE;
l_header_rec.context :=’XXXX’;
l_header_rec.ordered_date := d_order_date;
l_header_rec.attribute10 := to_char(hdr_dtls.start_date_active,’YYYY/MM/DD HH24:MI:SS’);
l_header_rec.attribute11 :=v_period;
for iii in 1..1000
loop
l_line_tbl(iii) := oe_order_pub.G_MISS_LINE_REC;
end loop;
for line_dtls in cur_lines(hdr_dtls.sold_to_org_id,
hdr_dtls.INVOICE_TO_ORG_ID,
hdr_dtls.ship_TO_ORG_ID,
hdr_dtls.TRANSACTIONAL_CURR_CODE,
hdr_dtls.CONVERSION_TYPE_CODE,
hdr_dtls.start_date_active,
hdr_dtls.org_id)
loop
n_invoive_rule_id := NULL;
n_account_rule_id := NULL;
d_service_start_date := NULL;
d_service_end_date := NULL;
BEGIN
IF line_dtls.attribute9 IS NOT NULL
THEN
n_invoive_rule_id := -2; –Advance Invoice;
fnd_file.put_line(fnd_file.log,’ATTRIBUTE9 is Not Null:n_invoive_rule_id := -2 ‘);
IF line_dtls.attribute9 = ‘Advance’
THEN
BEGIN
SELECT rule_id
INTO n_account_rule_id
FROM ra_rules
WHERE NAME IN (‘XXXX Rule for Partial Periods’);
END;
BEGIN
d_service_start_date := LAST_DAY (TO_DATE (l_header_rec.attribute11,’MON-YY’)) + 1;
d_service_end_date := LAST_DAY (LAST_DAY (TO_DATE (l_header_rec.attribute11,’MON-YY’)) + 1);
END;
fnd_file.put_line(fnd_file.log,’ATTRIBUTE9: ‘||line_dtls.attribute9||’#’||n_account_rule_id);
fnd_file.put_line(fnd_file.log,’ATTRIBUTE1: ‘||l_header_rec.attribute11);
fnd_file.put_line(fnd_file.log,’D_SERVICE_START_DATE: ‘||d_service_start_date);
fnd_file.put_line(fnd_file.log,’D_SERVICE_END_DATE: ‘||d_service_end_date);
ELSIF line_dtls.attribute9 = ‘Arrears’
THEN
BEGIN
SELECT rule_id
INTO n_account_rule_id
FROM ra_rules
WHERE NAME IN (‘Immediate’);
END;
d_service_start_date := NULL;
d_service_end_date := NULL;
fnd_file.put_line(fnd_file.log,’ATTRIBUTE9: ‘||line_dtls.attribute9||’#’||n_account_rule_id);
ELSE
n_account_rule_id := NULL;
d_service_start_date := NULL;
d_service_end_date := NULL;
fnd_file.put_line(fnd_file.log,’ATTRIBUTE9 Not in Aavance/Arrears ‘||line_dtls.attribute9||’#’||n_account_rule_id||’#’||d_service_start_date||’#’||d_service_end_date);
END IF;
ELSE
n_invoive_rule_id := NULL;
n_account_rule_id := NULL;
d_service_start_date := NULL;
d_service_end_date := NULL;
fnd_file.put_line(fnd_file.log,’ATTRIBUTE9 is NUll’||line_dtls.attribute9||’#’||n_account_rule_id||’#’||d_service_start_date||’#’||d_service_end_date);
END IF;
END;
fnd_file.put_line(fnd_file.log,’Inside Line ‘|| ‘NVL(line_dtls.ORDERED_QUANTITY,0) :’ ||NVL(line_dtls.ORDERED_QUANTITY,0) ||
‘ NVL(line_dtls.MIN_ORDER_QTY,0) : ‘|| NVL(line_dtls.MIN_ORDER_QTY,0) || ‘line_dtls.ordered_item :’ || line_dtls.ordered_item );
— Arun 7-Apr-2015
V_Order_Qty := 0;
If line_dtls.Dynamic_Static = ‘S’ Then
V_Order_Qty := line_dtls.SA_ORDER_QTY;
Else
V_Order_Qty := line_dtls.ORDERED_QUANTITY;
End If;
If line_dtls.Dynamic_Static = ‘D’ Then
— IF the quantity in the Usage file is lower, then we would take the minimum quantity from the DFF.
If NVL(line_dtls.ORDERED_QUANTITY,0) < NVL(line_dtls.MIN_ORDER_QTY,0) Then
V_Order_Qty := line_dtls.MIN_ORDER_QTY;
End If;
— IF the quantity on the Usage file is more than the Minimum we would take the quantity from the Usage file.
If NVL(line_dtls.ORDERED_QUANTITY,0) > NVL(line_dtls.MIN_ORDER_QTY,0) Then
V_Order_Qty := line_dtls.ORDERED_QUANTITY;
End If;
— If there is no minimum quantity in the DFF and the quantity on the Usage file is 0 we would not create that line in the Sales Order.
If NVL(line_dtls.ORDERED_QUANTITY,0) = 0 And
NVL(line_dtls.MIN_ORDER_QTY,0) = 0 Then
fnd_file.put_line(fnd_file.log,’Inside Line going to exit ‘);
GOTO Next_Rec;
End If;
End If; — Dynamic flag logic check…
n_line_counter := n_line_counter+1;
fnd_file.put_line(fnd_file.log,’Header Line ‘||n_line_counter||’-SoldtoOrgId’||line_dtls.sold_TO_ORG_ID||’-InvtoOrgId’||line_dtls.invoice_TO_ORG_ID||’-ShiptoOrgId’||line_dtls.ship_TO_ORG_ID||’-ShipFromOrgId’||line_dtls.ship_from_org_id||’-‘||hdr_dtls.CONVERSION_TYPE_CODE||’-‘||hdr_dtls.start_date_active||’-‘||hdr_dtls.org_id
||’-‘||line_dtls.inventory_item_id||’-‘|| V_ORDER_QTY ||’-‘||to_number(nvl(line_dtls.pue,1))||’-‘||line_dtls.accounting_rule_id
||’-‘||line_dtls.invoicing_rule_id||’-‘||line_dtls.attribute2);
l_line_tbl(n_line_counter) := oe_order_pub.G_MISS_LINE_REC;
l_line_tbl(n_line_counter).operation := OE_GLOBALS.G_OPR_CREATE;
l_line_tbl(n_line_counter).inventory_item_id := line_dtls.inventory_item_id;
— l_line_tbl(n_line_counter).ordered_quantity := line_dtls.ordered_quantity*to_number(nvl(line_dtls.pue,1));
l_line_tbl(n_line_counter).ordered_quantity := V_Order_Qty*to_number(nvl(line_dtls.pue,1));
l_line_tbl(n_line_counter).ship_from_org_id := line_dtls.ship_from_org_id;
l_line_tbl(n_line_counter).ship_to_org_id := line_dtls.ship_to_org_id;
l_line_tbl(n_line_counter).invoice_to_org_id := line_dtls.invoice_to_org_id;
l_line_tbl(n_line_counter).sold_to_org_id := line_dtls.sold_to_org_id;
l_line_tbl(n_line_counter).accounting_rule_id := n_account_rule_id;
l_line_tbl(n_line_counter).invoicing_rule_id := n_invoive_rule_id;
l_line_tbl(n_line_counter).context := ‘XXXX’;
l_line_tbl(n_line_counter).attribute10 := line_dtls.attribute2;
l_line_tbl(n_line_counter).attribute14 := line_dtls.ship_to_org_id;
l_line_tbl(n_line_counter).attribute15 := line_dtls.invoice_to_org_id;
if line_dtls.price > 0 then
l_line_tbl(n_line_counter).calculate_price_flag :=’N’;
l_line_tbl(n_line_counter).UNIT_LIST_PRICE := line_dtls.price;
l_line_tbl(n_line_counter).UNIT_SELLING_PRICE := line_dtls.price;
l_line_tbl(n_line_counter).service_start_date :=d_service_start_date;
l_line_tbl(n_line_counter).service_end_date:=d_service_end_date;
end if;
<<Next_Rec>>
Null;
end loop;
—
fnd_file.put_line(fnd_file.log,’Before Calling API’);
IF n_line_counter > 0 THEN
fnd_file.put_line(fnd_file.log,’Calling API’);
OE_ORDER_PUB.process_order(– IN PARAMETERS
p_api_version_number => 1.0
, p_org_id => 82
, p_init_msg_list => fnd_api.g_false
, p_return_values => fnd_api.g_false
, p_action_commit => fnd_api.g_false
, p_header_rec =>l_header_rec
, p_line_tbl =>l_line_tbl
, p_action_request_tbl => l_action_request_tbl
— OUT PARAMETERS
, x_header_rec => l_header_rec_out
, x_header_val_rec => l_header_val_rec_out
, x_Header_Adj_tbl => l_Header_Adj_tbl_out
, x_Header_Adj_val_tbl => l_Header_Adj_val_tbl_out
, x_Header_price_Att_tbl => l_Header_price_Att_tbl_out
, x_Header_Adj_Att_tbl => l_Header_Adj_Att_tbl_out
, x_Header_Adj_Assoc_tbl => l_Header_Adj_Assoc_tbl_out
, x_Header_Scredit_tbl => l_Header_Scredit_tbl_out
, x_Header_Scredit_val_tbl => l_Header_Scredit_val_tbl_out
, x_line_tbl => l_line_tbl_out
, x_line_val_tbl => l_line_val_tbl_out
, x_Line_Adj_tbl => l_Line_Adj_tbl_out
, x_Line_Adj_val_tbl => l_Line_Adj_val_tbl_out
, x_Line_price_Att_tbl => l_Line_price_Att_tbl_out
, x_Line_Adj_Att_tbl => l_Line_Adj_Att_tbl_out
, x_Line_Adj_Assoc_tbl => l_Line_Adj_Assoc_tbl_out
, x_Line_Scredit_tbl => l_Line_Scredit_tbl_out
, x_Line_Scredit_val_tbl => l_Line_Scredit_val_tbl_out
, x_Lot_Serial_tbl => l_Lot_Serial_tbl_out
, x_Lot_Serial_val_tbl => l_Lot_Serial_val_tbl_out
, x_action_request_tbl => l_action_request_tbl_out
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data);
IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
fnd_file.put_line(fnd_file.log,’API Return status is success ‘);
fnd_file.put_line(fnd_file.log,’header.order_number IS: ‘|| TO_CHAR(l_header_rec_out.order_number));
fnd_file.put_line(fnd_file.log,’header.header_id IS: ‘ ||l_header_rec_out.header_id);
begin
l_custno := NULL;
select account_number into l_custno
from APPS.hz_cust_accounts
where cust_account_id = hdr_dtls.sold_to_org_id;
EXCEPTION WHEN OTHERS THEN
l_custno := NULL;
end;
fnd_file.put_line(fnd_file.output,’ ‘);
fnd_file.put_line(fnd_file.output,’ ‘);
fnd_file.put_line(fnd_file.output,l_custno||chr(32)||chr(32)||chr(32)||chr(32)||chr(32)||chr(32)||hdr_dtls.order_number||chr(32)||chr(32)||chr(32)||chr(32)||chr(32)||chr(32)||chr(32)||chr(32)||chr(32)||chr(32)||chr(32)||chr(32)||chr(32)||chr(32)||chr(32)||chr(32)||chr(32)||chr(32)||V_PERIOD||chr(32)||chr(32)||chr(32)||chr(32)||TO_CHAR(l_header_rec_out.order_number) );
fnd_file.put_line(fnd_file.output,’ ‘);
update ONT.OE_BLANKET_HEADERS_ALL
set ATTRIBUTE1 = V_PERIOD
where order_number in (SELECT obha.order_number
FROM ONT.OE_BLANKET_HEADERS_ALL OBHA,
oe_blanket_headers_ext OBHE
WHERE 1=1
AND OBHA.CONTEXT=’XXXX’
AND NVL(OBHA.ATTRIBUTE1,’ABC-11′) <> TO_CHAR(SYSDATE,’MON-YY’)
AND OBHA.ORDER_NUMBER = OBHE.ORDER_NUMBER
— AND TRUNC(SYSDATE) BETWEEN OBHE.START_DATE_ACTIVE AND NVL(OBHE.END_DATE_ACTIVE,SYSDATE+1)
AND sold_to_org_id = hdr_dtls.sold_to_org_id
AND INVOICE_TO_ORG_ID = hdr_dtls.INVOICE_TO_ORG_ID
AND TRANSACTIONAL_CURR_CODE = hdr_dtls.TRANSACTIONAL_CURR_CODE
AND FLOW_STATUS_CODE = ‘ACTIVE’
AND nvl(CONVERSION_TYPE_CODE,’NULL’) = nvl(hdr_dtls.CONVERSION_TYPE_CODE,’NULL’)
AND obhe.start_date_active = hdr_dtls.start_date_active
AND org_id = hdr_dtls.org_id);
n_ord_counter:=n_ord_counter+1;
COMMIT;
ELSE
fnd_file.put_line(fnd_file.log,’Return status failure ‘);
fnd_file.put_line(fnd_file.log,’l_debug_level ‘||l_debug_level);
IF (l_debug_level > 0) THEN
fnd_file.put_line(fnd_file.log,’failure’);
END IF;
n_ord_err_counter:=n_ord_err_counter+1;
END IF; — Display Return Status
IF (l_debug_level > 0) THEN
fnd_file.put_line(fnd_file.log,’process ORDER ret status IS: ‘ || l_return_status);
fnd_file.put_line(fnd_file.log,’header.order_number IS: ‘|| to_char(l_header_rec_out.order_number));
fnd_file.put_line(fnd_file.log,’header.header_id IS: ‘ ||l_header_rec_out.header_id);
fnd_file.put_line(fnd_file.log,’header.order_source_id IS: ‘|| l_header_rec_out.order_source_id);
fnd_file.put_line(fnd_file.log,’header.flow_status_code IS: ‘|| l_header_rec_out.flow_status_code);
END IF;
–Display ERROR Messages
IF (l_debug_level > 0) THEN
FOR i IN 1 .. l_msg_count
LOOP
l_data := NULL;
l_data := oe_msg_pub.get( p_msg_index => i, p_encoded => ‘F’);
fnd_file.put_line(fnd_file.log, i|| ‘) ‘|| l_data);
END LOOP;
END IF;
IF (l_debug_level > 0) THEN
OE_DEBUG_PUB.DEBUG_OFF;
END IF;
rollback;
END IF;
IF n_line_counter = 0 THEN
fnd_file.put_line(fnd_file.log,’No Order is created For Sold to Org id-: ‘||hdr_dtls.sold_to_org_id||’ as Line are not eligible’);
begin
l_custno := NULL;
select account_number into l_custno
from APPS.hz_cust_accounts
where cust_account_id = hdr_dtls.sold_to_org_id;
EXCEPTION WHEN OTHERS THEN
l_custno := NULL;
end;
fnd_file.put_line(fnd_file.output,’ ‘);
fnd_file.put_line(fnd_file.output,’ ‘);
fnd_file.put_line(fnd_file.output,l_custno||chr(32)||chr(32)||chr(32)||chr(32)||chr(32)||chr(32)||hdr_dtls.order_number||chr(32)||chr(32)||chr(32)||chr(32)||chr(32)||chr(32)||chr(32)||chr(32)||chr(32)||chr(32)||chr(32)||chr(32)||chr(32)||chr(32)||chr(32)||chr(32)||chr(32)||chr(32)||V_PERIOD||chr(32)||chr(32)||chr(32)||chr(32)|| ‘Failure as Lines are not eligible’);
fnd_file.put_line(fnd_file.output,’ ‘);
END IF;
end loop;
commit;
fnd_file.put_line(fnd_file.log,’————————–‘);
fnd_file.put_line(fnd_file.log,’No of Sales Orders created: ‘||n_ord_counter);
fnd_file.put_line(fnd_file.log,’No of Sales Orders failed: ‘||n_ord_err_counter);
fnd_file.put_line(fnd_file.log,’XXXX Sales Order Interface ended’);
BEGIN
INSERT INTO XX_ORDERS_STG_ARC (SELECT * FROM XX_ORDERS_STG
WHERE upper(inventory_org) = (select upper(organization_name) from org_organization_definitions
where organization_code = p_warehouse));
DELETE FROM XX_ORDERS_STG WHERE upper(inventory_org) = (select upper(organization_name) from org_organization_definitions
where organization_code = p_warehouse);
COMMIT;
EXCEPTION WHEN OTHERS THEN
v_message:=’Unable to derive order date’;
raise e_exception;
END;
p_errbuf:=null;
p_retcode:=0;
EXCEPTION
WHEN E_EXCEPTION
THEN
fnd_file.put_line(fnd_file.log,v_message);
p_errbuf:=v_message;
p_retcode:=2;
WHEN OTHERS
THEN
fnd_file.put_line(fnd_file.log,sqlerrm);
p_errbuf:=sqlerrm;
p_retcode:=2;
END MAIN;
———————————
— Procedure VALIDATIONS
———————————
PROCEDURE VALIDATIONS(p_warehouse VARCHAR2) as
cursor cur_err_recs(l_warehouse VARCHAR2) is
select
rpad(ORDERS_ID,10) ORDERS_ID,rpad(sales_agreement_type,20) sales_agreement_type
,rpad(sales_agreement_number,10) sales_agreement_number,rpad(customer_number,20) customer_number,rpad(order_source,15) order_source,rpad(error_message,240) error_message
,rpad(order_type,20) order_type
,rpad(order_item,15) order_item
from XX_ORDERS_STG
where status=’E’
AND upper(inventory_org) = (select upper(organization_name) from org_organization_definitions
where organization_code = p_warehouse);
cursor cur_err_txns(l_warehouse VARCHAR2) IS
select distinct SALES_AGREEMENT_number
from XX_ORDERS_STG
where 1=1
and status=’E’
AND upper(inventory_org) = (select upper(organization_name) from org_organization_definitions
where organization_code = p_warehouse);
BEGIN
fnd_file.put_line(fnd_file.log,’Validations started….’);
UPDATE XX_ORDERS_STG IIS
SET STATUS=’E’,ERROR_CODE=’OME005′,ERROR_MESSAGE=ERROR_MESSAGE||’-‘||’Invalid Item’
WHERE upper(inventory_org) = (select upper(organization_name) from org_organization_definitions
where organization_code = p_warehouse)
AND NOT EXISTS(SELECT SEGMENT1 FROM INV.MTL_SYSTEM_ITEMS_B MSI WHERE MSI.SEGMENT1= IIS.ORDER_ITEM
AND MSI.ENABLED_FLAG=’Y’ AND TRUNC(SYSDATE) BETWEEN NVL(START_DATE_ACTIVE,SYSDATE-1)
AND NVL(END_DATE_ACTIVE,SYSDATE+1));
commit;
UPDATE XX_ORDERS_STG IIS
SET STATUS=’E’,ERROR_CODE=’OME006′,ERROR_MESSAGE=ERROR_MESSAGE||’-‘||’Invalid Customer Account Number’
WHERE upper(inventory_org) = (select upper(organization_name) from org_organization_definitions
where organization_code = p_warehouse)
AND NOT EXISTS(SELECT cust_account_id FROM aPPS.hz_cust_accounts HCA WHERE iis.customer_number= HCA.account_number);
commit;
for x_trx in cur_err_txns(p_warehouse)
loop
UPDATE XX_ORDERS_STG IIS
SET STATUS=’E’,ERROR_CODE=’NOERR’,ERROR_MESSAGE=ERROR_MESSAGE||’-‘||’No Error’
WHERE SALES_AGREEMENT_number=x_trx.SALES_AGREEMENT_number and status=’U’
AND upper(inventory_org) = (select upper(organization_name) from org_organization_definitions
where organization_code = p_warehouse);
end loop;
commit;
UPDATE XX_ORDERS_STG IIS
SET ERROR_MESSAGE=LTRIM(ERROR_MESSAGE,’-‘)
WHERE upper(inventory_org) = (select upper(organization_name) from org_organization_definitions
where organization_code = p_warehouse);
commit;
fnd_file.put_line(fnd_file.output,’————————————–XXXX Sales Order Interface validation Errors———————————————————–‘);
fnd_file.put_line(fnd_file.output,’ ‘);
fnd_file.put_line(fnd_file.output,’————————————————————————————————————————————————-‘);
fnd_file.put_line(fnd_file.output,’RECORD ID ‘||’CUSTOMER NUMBER ‘||’ORDER ITEM ‘||’ERROR MESSAGE’);
fnd_file.put_line(fnd_file.output,’————————————————————————————————————————————————-‘);
for err_rec in cur_err_recs(p_warehouse)
loop
fnd_file.put_line(fnd_file.output,err_rec.ORDERS_ID||err_rec.customer_number||err_rec.order_item||err_rec.error_message);
end loop; fnd_file.put_line(fnd_file.output,’————————————————————————————————————————————————-‘);
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line(fnd_file.log,sqlerrm);
END VALIDATIONS;
END XX_SO_PROG_PKG;
Queries
Do drop a note by writing us at doyen.ebiz@gmail.com or use the comment section below to ask your questions
