ORACLE MASTERMINDS: Sales Order Loader API from Blanket Sales Agreement

Wednesday 26 September 2018

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;
/

Wednesday 26 September 2018

Sales Order Loader API from Blanket Sales Agreement

No comments:

Post a Comment