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

  • September 26, 2018 | 22 views
  • Comments