Sales Order Loader API from Blanket Sales Agreement

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