ITPub博客

首页 > Linux操作系统 > Linux操作系统 > INV, RCV Interface Instance

INV, RCV Interface Instance

原创 Linux操作系统 作者:xing_lang 时间:2012-06-14 10:54:40 0 删除 编辑
CREATE OR REPLACE PACKAGE INVGRCVK_BG_1 IS
  PROCEDURE MAIN(ERRBUF      OUT VARCHAR2,
                 RETCODE     OUT VARCHAR2,
                 p_in_org_id IN NUMBER);
  PROCEDURE bg_po_receipt_sysauto(p_org_id IN NUMBER);
  PROCEDURE bg_rtv_sysauto(p_org_id IN NUMBER);
  PROCEDURE bg_mtl_sysauto(p_org_id IN NUMBER);
  PROCEDURE bg_wip_sysauto(p_org_id IN NUMBER);
  procedure bg_cut_generate_id;
  FUNCTION bg_get_receipt_qty(p_in_po_header_id   IN NUMBER,
                              p_in_po_line_number IN NUMBER,
                              p_in_shipment_no    IN NUMBER,
                              p_in_waybill        IN VARCHAR2) RETURN NUMBER;
  
  FUNCTION bg_get_conv_rate(p_in_po_number IN VARCHAR2,
                            p_in_org_id    IN NUMBER,
                            p_in_trx_date  IN DATE) RETURN NUMBER;
  FUNCTION bg_get_employee_name(p_in_user_id IN NUMBER) RETURN VARCHAR2;
END INVGRCVK_BG_1;
CREATE OR REPLACE PACKAGE BODY INVGRCVK_BG_1 IS
  /*
   * ID          Date          Modification Description
   * ----------- ------------- -----------------------------------------------------------------
   * LiuZerui    2011-05-10    Created
   *                           Concurrent Program:
   *                           Short Name        : INVGRCVK_BG_X
   *                           Executable        : INVGRCVK_BG_1
   *                           Request Group     :
   *                           Generate receipt number function : BG_GEN_RCV_NUM(215,1);
  */
  v_org_id        NUMBER;
  V_USER_ID       NUMBER;
  l_req_id        NUMBER;
  l_complete_flag BOOLEAN;
  l_phase         VARCHAR2(100);
  l_status        VARCHAR2(100);
  l_dev_phase     VARCHAR2(100);
  l_dev_status    VARCHAR2(100);
  l_message       VARCHAR2(1000);
  v_count         NUMBER := 0;
  v_receipt_number           rcv_shipment_headers.receipt_num%TYPE;
  v_rcv_h_interface_currval  NUMBER;
  v_shipment_header_id       NUMBER;
  v_interface_process_status RCV_HEADERS_INTERFACE.Processing_Status_Code%TYPE;
  /*v_shipment_num     RCV_SHIPMENT_HEADERS.Shipment_Num%TYPE;
  v_SHIPPED_DATE     RCV_SHIPMENT_HEADERS.SHIPPED_DATE%TYPE :=sysdate-30;
 
  v_packing_slip     RCV_SHIPMENT_HEADERS.Packing_Slip%TYPE := 'ps';
  V_Freight_Carrier  RCV_SHIPMENT_HEADERS.Freight_Carrier_Code%TYPE := 'COMPANY TRUCK';
  V_Bill_Lading      RCV_SHIPMENT_HEADERS.Bill_Of_Lading%TYPE := 'Test01';
  V_Container        RCV_SHIPMENT_HEADERS.Num_Of_Containers%TYPE := 119;
  v_comments         RCV_SHIPMENT_HEADERS.Comments%TYPE := 'Testing';*/
  PROCEDURE MAIN(ERRBUF      OUT VARCHAR2,
                 RETCODE     OUT VARCHAR2,
                 p_in_org_id IN NUMBER) IS
    v_po_rece NUMBER;
    v_rtv     NUMBER;
    v_mtl     NUMBER;
    v_wip     NUMBER;
 
    CURSOR c_num IS
      SELECT (SELECT COUNT(c_transaction_id)
                FROM BG_CUT_RCV_Interface
               WHERE transaction_type = 'PO'
                 AND processing_status = 'P'
                 AND organization_id = p_in_org_id
                 AND ROWNUM < 2) po,
             (SELECT COUNT(c_transaction_id)
                FROM BG_CUT_RCV_Interface
               WHERE transaction_type = 'RTV'
                 AND processing_status = 'P'
                 AND organization_id = p_in_org_id
                 AND ROWNUM < 2) rtv,
             (SELECT COUNT(c_transaction_id)
                FROM BG_CUT_MTL_Interface
               WHERE transaction_type = 'MTL'
                 AND processing_status = 'P'
                 AND organization_id = p_in_org_id
                 AND ROWNUM < 2) mtl,
             (SELECT COUNT(c_transaction_id)
                FROM BG_CUT_WIP_Interface
               WHERE transaction_type = 'WIP'
                 AND processing_status = 'P'
                 AND organization_id = p_in_org_id
                 AND ROWNUM < 2) wip
        FROM dual;
 
  BEGIN
    ---Generate CUT ID includes PO and JOB --
    bg_cut_generate_id;
    ---Generate CUT ID includes PO and JOB --
    v_org_id := p_in_org_id;
 
    OPEN c_num;
    FETCH c_num
      INTO v_po_rece, v_rtv, v_mtl, v_wip;
    CLOSE c_num;
 
    --initialize
    /* begin
      fnd_global.apps_initialize(user_id=>3272,resp_id=>53528,resp_appl_id=>401);
    end;*/
 
    --po receipt
    IF v_po_rece <> 0 THEN
      INVGRCVK_BG_1.bg_po_receipt_sysauto(v_org_id);
    END IF;
 
    --return to vendor
    IF v_rtv <> 0 THEN
      INVGRCVK_BG_1.bg_rtv_sysauto(v_org_id);
    END IF;
 
    --subinventory transfor
    IF v_mtl <> 0 THEN
      invgrcvk_bg_1.bg_mtl_sysauto(v_org_id);
    END IF;
 
    --WIP completion
    IF v_wip <> 0 THEN
      invgrcvk_bg_1.bg_wip_sysauto(v_org_id);
    END IF;
 
  END MAIN;
  ---Used to generate CUT ID includes PO and JOB --
  procedure bg_cut_generate_id is
    cursor cur_rec_po is
    --PO Part--
      select poh.org_id,
             poh.segment1 source_number,
             poh.po_header_id source_id,
             pol.po_line_id source_line_id,
             msi.segment1 item_number,
             pol.item_id item_id,
             nvl(pll.quantity - pll.quantity_received, 0) QTY,
             'PO' source_type,
             nvl(pol.attribute4, null) CUTTER_TYPE
        from po_headers_all             poh,
             po_lines_all               pol,
             apps.po_line_locations_all pll,
             mtl_system_items_b         msi
       where poh.po_header_id = pol.po_header_id
         AND msi.inventory_item_id = pol.item_id
         AND msi.organization_id = poh.org_id
            ---
         AND pll.po_header_id = poh.po_header_id
         AND pll.po_line_id = pol.po_line_id
         AND nvl(pll.cancel_flag, 'N') <> 'Y'
         AND pll.org_id = poh.org_id
         AND pll.Closed_Code = 'OPEN'
         AND nvl(pll.quantity - pll.quantity_received, 0) > 0
            --and poh.ATTRIBUTE9 = 'G'
         AND msi.segment1 LIKE 'C%'
            -- and poh.segment1 = '20120402704'
         and poh.org_id = 215
         AND poh.Authorization_Status = 'APPROVED'
         AND poh.type_lookup_code = 'STANDARD'
         AND nvl(pol.cancel_flag, 'N') <> 'Y'
            --  AND poh.creation_date be
         and nvl(pol.ATTRIBUTE3, 'N') = 'N' --Update Remark--
         and pol.attribute4 is not null --cutter type
       group by poh.org_id,
                poh.segment1,
                poh.po_header_id,
                pol.po_line_id,
                msi.segment1,
                pol.item_id,
                nvl(pll.quantity - pll.quantity_received, 0),
                nvl(pol.attribute4, null)
      
       order by 8, 2;
    --Job Part--
    cursor cur_rec_job is
      SELECT msi.organization_id org_id,
             we.wip_entity_name source_number,
             we.wip_entity_id source_id,
             null source_line_id,
             msi.segment1 item_number,
             msi.inventory_item_id item_id,
             decode(WDJ.START_QUANTITY - WDJ.QUANTITY_COMPLETED -
                    WDJ.QUANTITY_SCRAPPED,
                    0,
                    0,
                    WDJ.START_QUANTITY - WDJ.QUANTITY_COMPLETED -
                    WDJ.QUANTITY_SCRAPPED) QTY,
             'JOB' source_type,
             nvl(wdj.ATTRIBUTE13, null) CUTTER_TYPE
     
        FROM wip_entities we, wip_discrete_jobs wdj, mtl_system_items_b msi
       WHERE we.wip_entity_id = wdj.wip_entity_id
         AND msi.inventory_item_id = wdj.primary_item_id
         AND msi.organization_id = wdj.organization_id
         AND wdj.status_type = 3
            --AND we.wip_entity_name = 'CUT1203047'
         AND msi.segment1 LIKE 'C%'
         AND msi.organization_id = 215
         AND nvl(wdj.ATTRIBUTE12, 'N') = 'N' --Update Remark--
         AND wdj.ATTRIBUTE13 is not null --cutter type
       order by 8, 2;
 
    V_DB_SEQUENCE_NAME varchar2(50);
    v_init_value       number;
    v_year             varchar2(10) := null;
    v_week             varchar2(10) := null;
    v_ddl_sql          varchar2(1000);
    X                  number;
    Y                  number;
  begin
    --
    select to_char(sysdate, 'YY') into v_year from dual;
    select to_char(sysdate, 'FMWW') into v_week from dual;
    --
    V_DB_SEQUENCE_NAME := 'BG_CUT_SEQ_' || to_number(v_year || v_week) || '_S';
 
    dbms_output.put_line(V_DB_SEQUENCE_NAME);
    begin
      select count(*)
        into X
        from dba_sequences F
       where f.sequence_name = V_DB_SEQUENCE_NAME;
    exception
      when others then
        X := 0;
    end;
 
    if X = 0 then
   
      dbms_output.put_line('Create Sequence' || V_DB_SEQUENCE_NAME);
      v_init_value := v_year || v_week || '00001';
   
      v_ddl_sql := 'CREATE SEQUENCE ' || V_DB_SEQUENCE_NAME ||
                   ' MINVALUE 1 NOMAXVALUE START WITH ' || v_init_value ||
                   ' NOCACHE ORDER NOCYCLE';
   
      execute immediate v_ddl_sql;
   
      /*FND_SEQNUM.create_db_seq(V_DB_SEQUENCE_NAME,v_init_value);*/
   
    end if;
 
    V_DB_SEQUENCE_NAME := V_DB_SEQUENCE_NAME || '.nextval';
    dbms_output.put_line(V_DB_SEQUENCE_NAME);
 
    --Part 1.PO--
    FOR rec_po IN cur_rec_po LOOP
    
   
      --Insert Cut ID --
      for i in 1 .. rec_po.QTY loop
        execute immediate 'select ' || V_DB_SEQUENCE_NAME || ' from dual'
          into Y;
        insert into BG_CUT_CUTTER_ID
          (org_id,
           source_id,
           source_line_id,
           source_type,
           item_id,
           cutter_id,
           CUTTER_TYPE )
        values
          (rec_po.org_id,
           rec_po.source_id,
           rec_po.source_line_id,
           rec_po.source_type,
           rec_po.item_id,
           Y,
           rec_po.CUTTER_TYPE
           );
      end loop;
      --
       ---Update Remake column for PO line --
      update po_lines_all P
         set P.ATTRIBUTE3 = 'Y'
       where P.Po_Line_Id = rec_po.source_line_id;
      
    end loop;
 
    --Part 2.JOb--
 
    FOR rec_cut IN cur_rec_job LOOP
      --Insert Cut ID --
      for i in 1 .. rec_cut.QTY loop
        execute immediate 'select ' || V_DB_SEQUENCE_NAME || ' from dual'
          into Y;
        insert into BG_CUT_CUTTER_ID
          (org_id,
           source_id,
           source_line_id,
           source_type,
           item_id,
           cutter_id,
           CUTTER_TYPE )
        values
          (rec_cut.org_id,
           rec_cut.source_id,
           rec_cut.source_line_id,
           rec_cut.source_type,
           rec_cut.item_id,
           Y,
           rec_cut.CUTTER_TYPE );
      end loop;
      --
         update wip_discrete_jobs j
         set j.attribute12 = 'Y'
       where j.wip_entity_id = rec_cut.source_id;
      ---Update Remake column for PO line and JoB header--
    end loop;
 
    COMMIT;
  end bg_cut_generate_id;
  PROCEDURE bg_po_receipt_sysauto(p_org_id IN NUMBER) IS
 
    v_inv_period NUMBER;
    v_pur_period NUMBER;
    v_ap_period  NUMBER;
    v_ar_period  NUMBER;
    v_gl_period  NUMBER;
 
    v_conversion_rate gl_daily_rates.conversion_rate%TYPE;
    v_curr_code       po_headers_all.currency_code%TYPE;
 
    v_operator VARCHAR2(100);
 
    /*CURSOR c_curr_code(p_in_po_number IN VARCHAR2, p_in_org_id IN NUMBER) IS
    SELECT poh.currency_code
      FROM po_headers_all poh
     WHERE poh.org_id = p_in_org_id
       AND poh.segment1 = p_in_po_number;*/
 
    CURSOR c_period_status(p_trx_date DATE) IS
      SELECT (SELECT COUNT(p.status)
                FROM ORG_ACCT_PERIODS_V p
               WHERE p.organization_id = p_org_id
                 AND upper(p.status) = upper('Open')
                 AND trunc(p_trx_date) BETWEEN p.start_date AND p.end_date) inv_period,
             (SELECT COUNT(g.closing_status)
                FROM GL_PERIOD_STATUSES g
               WHERE g.application_id = 201
                 AND g.set_of_books_id = 302
                 AND upper(g.closing_status) = upper('O')
                 AND trunc(p_trx_date) BETWEEN g.start_date AND g.end_date) pur_period,
             (SELECT COUNT(g.closing_status)
                FROM GL_PERIOD_STATUSES g
               WHERE g.application_id = 200
                 AND g.set_of_books_id = 302
                 AND upper(g.closing_status) = upper('O')
                 AND trunc(p_trx_date) BETWEEN g.start_date AND g.end_date) ap_period,
             (SELECT COUNT(g.closing_status)
                FROM GL_PERIOD_STATUSES g
               WHERE g.application_id = 222
                 AND g.set_of_books_id = 302
                 AND upper(g.closing_status) = upper('O')
                 AND trunc(p_trx_date) BETWEEN g.start_date AND g.end_date) ar_period,
             (SELECT COUNT(g.closing_status)
                FROM GL_PERIOD_STATUSES g
               WHERE g.application_id = 101
                 AND g.set_of_books_id = 302
                 AND upper(g.closing_status) = upper('O')
                 AND trunc(p_trx_date) BETWEEN g.start_date AND g.end_date) gl_period
        FROM dual;
 
    CURSOR update_r(p_in_header_id VARCHAR2) IS
      SELECT receipt_header_id, processing_status_code
        FROM rcv_headers_interface
       WHERE header_interface_id = p_in_header_id;
 
    CURSOR receipt(p_req_id number) IS
      select r.receipt_num
        from rcv_shipment_headers r
       where r.request_id = p_req_id;
 
    CURSOR c_rcv_header IS
    /* SELECT c_transaction_id,PO_Number,Organization_ID,WayBill ,Operator,
                  TRANSACTION_DATE*/
      SELECT DISTINCT PO_Number,
                      Organization_ID,
                      GRN_Number,
                      WayBill,
                      Operator,
                      transaction_date
      --to_char(transaction_date,'MON-YYYY') trx_period  -- added in 2011/07/06
        FROM BG_CUT_RCV_Interface bcr
       WHERE PROCESSING_STATUS = 'P'
         AND transaction_type = 'PO'
         AND organization_id = p_org_id
       ORDER BY transaction_date;
 
    CURSOR c_rcv_po(p_in_po_number IN VARCHAR2, p_in_org_id IN NUMBER) IS
      SELECT PO_HEADER_ID, VENDOR_ID, SEGMENT1, ORG_ID, vendor_site_id
        FROM PO_HEADERS_ALL
       WHERE segment1 = p_in_po_number
         AND org_id = p_org_id;
 
    CURSOR c_rcv_cms_oracle(p_po_number   IN VARCHAR2,
                            p_in_waybill  IN VARCHAR2,
                            p_in_trx_date IN DATE) IS
      SELECT bcr.c_transaction_id,
             bcr.po_number,
             bcr.organization_id,
             bcr.item_number,
             bcr.line_number,
             bcr.shipment_line_num,
             bcr.grn_number,
             bcr.subinventory_code,
             bcr.mrb_no,
             bcr.rma_number,
             bcr.transaction_qty,
             bcr.transaction_uom,
             bcr.waybill,
             bcr.operator /*,
                      bcr.Last_Update_Date,
                      bcr.Last_Updated_By,
                      bcr.creation_date,
                      bcr.created_by*/
        FROM BG_CUT_RCV_Interface bcr
       WHERE organization_id = p_org_id
         AND bcr.waybill = p_in_waybill
         AND bcr.processing_status = 'P'
         AND trunc(bcr.transaction_date) = trunc(p_in_trx_date)
         AND bcr.transaction_type = 'PO'
         AND bcr.po_number = p_po_number
      --AND bcr.c_transaction_id = p_transaction_id
       ORDER BY bcr.line_number, bcr.shipment_line_num;
 
    CURSOR c_rcv(p_po_header_id    IN NUMBER,
                 p_po_line_number  IN NUMBER,
                 p_shipment_number IN NUMBER,
                 p_waybill         IN VARCHAR2) IS
      SELECT pl.org_Id,
             PL.ITEM_ID,
             pl.po_header_id,
             PL.PO_LINE_ID,
             PL.LINE_NUM,
             PLL.QUANTITY,
             PL.UNIT_MEAS_LOOKUP_CODE,
             MP.ORGANIZATION_CODE,
             PLL.LINE_LOCATION_ID,
             PLL.CLOSED_CODE,
             PLL.QUANTITY_RECEIVED,
             apps.invgrcvk_bg_1.bg_get_receipt_qty(p_po_header_id,
                                                   p_po_line_number,
                                                   p_shipment_number,
                                                   p_waybill) received_qty,
             PLL.CANCEL_FLAG,
             PLL.SHIPMENT_NUM,
             pda.destination_type_code,
             pda.deliver_to_person_id,
             pll.ship_to_location_id,
             pda.destination_subinventory,
             pda.destination_organization_id
        FROM PO_LINES_ALL              PL,
             PO_LINE_LOCATIONS_ALL     PLL,
             MTL_PARAMETERS            MP,
             apps.po_distributions_all pda
       WHERE PL.PO_LINE_ID = PLL.PO_LINE_ID
         AND PLL.SHIP_TO_ORGANIZATION_ID = MP.ORGANIZATION_ID
         AND pll.line_location_id = pda.line_location_id
         AND pll.shipment_num = p_shipment_number
         AND pl.line_num = p_po_line_number
         AND Pl.PO_HEADER_ID = p_po_header_id; --291502 --X_PO_HEADER_ID
 
  BEGIN
    fnd_file.put_line(fnd_file.output,
                      '***CMS Data to Oracle RCV API Insert Script***');
    dbms_output.put_line('***CMS Data to Oracle RCV API Insert Script***');
    SELECT fnd_profile.value('USER_ID') INTO v_user_id FROM dual;
 
    v_operator := INVGRCVK_BG_1.bg_get_employee_name(v_user_id);
 
    FOR rec_rcv IN c_rcv_header LOOP
      v_receipt_number           := '';
      v_rcv_h_interface_currval  := '';
      v_shipment_header_id       := '';
      v_interface_process_status := '';
   
      OPEN c_period_status(rec_rcv.transaction_date);
      FETCH c_period_status
        INTO v_inv_period,
             v_pur_period,
             v_ap_period,
             v_ar_period,
             v_gl_period;
      CLOSE c_period_status;
   
      v_conversion_rate := INVGRCVK_BG_1.bg_get_conv_rate(rec_rcv.PO_Number,
                                                          p_org_id,
                                                          rec_rcv.TRANSACTION_date);
   
      /* dbms_output.put_line('*00'||v_inv_period);
      dbms_output.put_line('*00'||v_pur_period);
      dbms_output.put_line('*00'||v_ap_period);
      dbms_output.put_line('*00'||v_ar_period);
      dbms_output.put_line('*00'||v_gl_period);
      dbms_output.put_line('*00'||v_conversion_rate);*/
      IF v_inv_period = 1 AND v_pur_period = 1 AND v_ap_period = 1 AND
         v_ar_period = 1 AND v_gl_period = 1 AND
         v_conversion_rate IS NOT NULL THEN
        dbms_output.put_line('*01');
        FOR rec_po IN c_rcv_po(rec_rcv.PO_Number, rec_rcv.organization_id) LOOP
          INSERT INTO rcv_headers_interface
            (header_interface_id,
             GROUP_ID,
             processing_status_code,
             receipt_source_code,
             transaction_type,
             auto_transact_code,
             last_update_date,
             last_updated_by,
             last_update_login,
             creation_date, --receipt date--
             created_by,
             vendor_id,
             vendor_site_id,
             expected_receipt_date,
             validation_flag,
             --SHIPMENT_NUM,
             --SHIPPED_DATE,
             --PACKING_SLIP,
             WAYBILL_AIRBILL_NUM,
             --FREIGHT_CARRIER_CODE,
             --BILL_OF_LADING,
             --NUM_OF_CONTAINERS,
             EMPLOYEE_NAME,
             ATTRIBUTE_CATEGORY,
             attribute1
             --COMMENTS
             )
          VALUES
            (rcv_headers_interface_s.NEXTVAL,
             rcv_interface_groups_s.NEXTVAL,
             'PENDING',
             'VENDOR',
             'NEW',
             'RECEIVE',
             SYSDATE,
             V_USER_ID,
             0,
             rec_rcv.TRANSACTION_DATE,
             V_USER_ID,
             rec_po.vendor_id,
             rec_po.vendor_site_id,
             SYSDATE,
             'Y',
             --v_shipment_num, --'123',
             --v_SHIPPED_DATE, --to_date('2010/10/01','yyyy/mm/dd'),
             --v_packing_slip,--'a',
             rec_rcv.WayBill,
             --V_Freight_Carrier,--'COMPANY TRUCK',
             --V_Bill_Lading,--'Test01',
             --V_Container,--1,
             v_operator, --rec_rcv.Operator,
             p_org_id,
             1
             --v_comments--'COMMENTS'
             );
       
          FOR rec_t IN c_rcv_cms_oracle(rec_rcv.PO_Number,
                                        rec_rcv.waybill,
                                        rec_rcv.transaction_date) LOOP
            dbms_output.put_line('*02');
            FOR rec IN c_rcv(rec_po.po_header_id,
                             rec_t.line_number,
                             rec_t.shipment_line_num,
                             rec_t.waybill) LOOP
              IF rec.closed_code IN ('APPROVED', 'OPEN') AND
                 rec.QUANTITY_RECEIVED < rec.QUANTITY AND
                 NVL(rec.CANCEL_FLAG, 'N') = 'N' THEN
                INSERT INTO RCV_TRANSACTIONS_INTERFACE
                  (INTERFACE_TRANSACTION_ID,
                   GROUP_ID,
                   LAST_UPDATE_DATE,
                   LAST_UPDATED_BY,
                   CREATION_DATE,
                   CREATED_BY,
                   LAST_UPDATE_LOGIN,
                   TRANSACTION_TYPE,
                   TRANSACTION_DATE,
                   PROCESSING_STATUS_CODE,
                   PROCESSING_MODE_CODE,
                   TRANSACTION_STATUS_CODE,
                   po_header_id,
                   PO_LINE_ID,
                   ITEM_ID,
                   QUANTITY,
                   UNIT_OF_MEASURE,
                   PO_LINE_LOCATION_ID,
                   AUTO_TRANSACT_CODE,
                   RECEIPT_SOURCE_CODE,
                   TO_ORGANIZATION_CODE,
                   to_organization_id,
                   ship_to_location_id,
                   SOURCE_DOCUMENT_CODE,
                   destination_type_code,
                   deliver_to_person_id,
                   deliver_to_location_id,
                   subinventory,
                   DOCUMENT_NUM,
                   HEADER_INTERFACE_ID,
                   VALIDATION_FLAG)
                VALUES
                  (RCV_TRANSACTIONS_INTERFACE_S.NEXTVAL,
                   RCV_INTERFACE_GROUPS_S.CURRVAL,
                   SYSDATE,
                   V_USER_ID,
                   SYSDATE,
                   V_USER_ID,
                   0,
                   'RECEIVE', --TRANSACTION_TYPE
                   rec_rcv.TRANSACTION_DATE, --SYSDATE ,
                   'PENDING',
                   'BATCH',
                   'PENDING',
                   rec.po_header_id,
                   rec.PO_LINE_ID,
                   rec.ITEM_ID,
                   --rec_t.transaction_qty ,
                   rec.received_qty,
                   rec_t.transaction_uom, --UNIT_OF_MEASURE
                   rec.LINE_LOCATION_ID,
                   'DELIVER',
                   'VENDOR',
                   rec.ORGANIZATION_CODE,
                   rec.org_id,
                   rec.ship_to_location_id,
                   'PO',
                   rec.destination_type_code,
                   rec.deliver_to_person_id,
                   rec.ship_to_location_id,
                   nvl(rec.destination_subinventory,
                       rec_t.subinventory_code),
                   rec_rcv.PO_Number,
                   RCV_HEADERS_INTERFACE_S.CURRVAL,
                   'Y');
                DBMS_OUTPUT.PUT_LINE('PO line: ' || rec.LINE_NUM ||
                                     ' Shipment: ' || rec.SHIPMENT_NUM ||
                                     ' has been inserted into ROI.');
              ELSE
                DBMS_OUTPUT.PUT_LINE('PO line ' || rec.LINE_NUM ||
                                     ' is either closed, cancelled, received.');
              END IF;
           
            END LOOP;
            --DBMS_OUTPUT.PUT_LINE('*** ezROI COMPLETE - End ***');            
          END LOOP;
       
        END LOOP;
     
        fnd_file.put_line(fnd_file.output,
                          '*** Call Receiving Transaction Processor Reoport ***');
     
        /* fnd_global.apps_initialize(user_id           => fnd_profile.value('USER_ID'),
        resp_id           => fnd_profile.value('RESP_ID'),
        resp_appl_id      => fnd_profile.value('RESP_APPL_ID'), --GL
        security_group_id => 0);*/
     
        l_req_id := fnd_request.submit_request('PO',
                                               'RVCTP',
                                               null,
                                               sysdate,
                                               false,
                                               'BATCH',
                                               null);
        dbms_output.put_line('l_req_id: ' || l_req_id);
     
        COMMIT;
     
        IF l_req_id <= 0 THEN
       
          fnd_file.put_line(fnd_file.output,
                            '*** Receiving Transaction Processor runing exception ***');
        ELSIF l_req_id > 0 THEN
       
          fnd_file.put_line(fnd_file.output,
                            '*** Receiving Transaction Processor Request Submit successful ***');
       
          l_complete_flag := fnd_concurrent.wait_for_request(l_req_id,
                                                             1,
                                                             3600,
                                                             l_phase,
                                                             l_status,
                                                             l_dev_phase,
                                                             l_dev_status,
                                                             l_message);
          -- use the Customized receipt number            
          SELECT rcv_headers_interface_s.CURRVAL
            INTO v_rcv_h_interface_currval
            FROM dual;
       
          fnd_file.put_line(fnd_file.output,
                            'rcv_headers_interface_s: ' ||
                            v_rcv_h_interface_currval);
       
          OPEN update_r(v_rcv_h_interface_currval);
          FETCH update_r
            INTO v_shipment_header_id, v_interface_process_status;
          CLOSE update_r;
       
          fnd_file.put_line(fnd_file.output,
                            'v_shipment_header_id: ' ||
                            v_shipment_header_id);
          fnd_file.put_line(fnd_file.output,
                            'rec_rcv.grn_number: ' || rec_rcv.grn_number);
       
          IF rec_rcv.grn_number IS NULL AND
             v_shipment_header_id IS NOT NULL THEN
            SELECT BG_GEN_RCV_NUM(p_org_id, 1)
              INTO v_receipt_number
              FROM dual;
          ELSE
            v_receipt_number := rec_rcv.grn_number;
          END IF;
       
          IF v_interface_process_status = 'SUCCESS' THEN
            UPDATE rcv_shipment_headers
               SET receipt_num = v_receipt_number --rec_rcv.grn_number
             WHERE shipment_header_id = v_shipment_header_id;
         
            UPDATE BG_CUT_RCV_Interface
               SET PROCESSING_STATUS = 'S', grn_number = v_receipt_number
             WHERE PROCESSING_STATUS = 'P'
               AND trunc(TRANSACTION_DATE) =
                   trunc(rec_rcv.transaction_date)
               AND transaction_type = 'PO'
               AND waybill = rec_rcv.waybill
               AND po_number = rec_rcv.po_number;
            --AND c_transaction_id = rec_rcv.c_transaction_id;
          END IF;
       
          COMMIT;
       
        END IF;
     
        IF l_complete_flag THEN
          FOR CURSOR3 IN receipt(l_req_id) LOOP
            IF CURSOR3.receipt_num IS NULL THEN
              --** check the po_interface_errors*--
              select count(*)
                into v_count
                from po_interface_errors a
               where a.request_id = l_req_id;
              fnd_file.put_line(fnd_file.output,
                                'The po_interface_errors exists error , you can runing the report "Receiving Interface Errors Report" to check !' ||
                                CURSOR3.receipt_num);
              DBMS_OUTPUT.PUT_LINE('The po_interface_errors exists error , you can runing the report "Receiving Interface Errors Report" to check !' ||
                                   CURSOR3.receipt_num);
              --** check the po_interface_errors*--
            ELSE
              fnd_file.put_line(fnd_file.output,
                                'Receipt Number:' || CURSOR3.receipt_num);
              DBMS_OUTPUT.PUT_LINE('Receipt Number:' ||
                                   CURSOR3.receipt_num);
            END IF;
         
          END LOOP;
        ELSE
          fnd_file.put_line(fnd_file.output,
                            '*** Receiving Transaction Processing ***');
          DBMS_OUTPUT.PUT_LINE('*** Receiving Transaction Processing ***');
        END IF;
     
      ELSIF v_conversion_rate IS NULL THEN
        fnd_file.PUT_LINE(fnd_file.OUTPUT,
                          '------------------------------------------');
        fnd_file.PUT_LINE(fnd_file.OUTPUT,
                          'The PO number: ' || rec_rcv.po_number ||
                          ' have no conversion rate in ' ||
                          to_char(rec_rcv.transaction_date, 'yyyy/mm/dd'));
        fnd_file.PUT_LINE(fnd_file.OUTPUT,
                          '------------------------------------------');
      ELSE
        fnd_file.PUT_LINE(fnd_file.OUTPUT,
                          '------------------------------------------');
        fnd_file.PUT_LINE(fnd_file.OUTPUT,
                          '*** The period is not opened! ***    Period: ' ||
                          to_char(rec_rcv.transaction_date, 'MON-YYYY'));
        fnd_file.PUT_LINE(fnd_file.OUTPUT,
                          '------------------------------------------');
     
      END IF;
   
      v_conversion_rate := NULL;
   
    END LOOP;
 
    COMMIT;
 
    /*
    fnd_file.put_line(fnd_file.output, '*** Call Receiving Transaction Processor Reoport ***');
   
    fnd_global.apps_initialize(user_id           => fnd_profile.value('USER_ID'),
                              resp_id           => fnd_profile.value('RESP_ID'),
                              resp_appl_id      => fnd_profile.value('RESP_APPL_ID'), --GL
                              security_group_id => 0);
                             
    l_req_id := fnd_request.submit_request('PO',
                                          'RVCTP',
                                          null,
                                          sysdate,
                                          false,
                                          'BATCH',
                                          null);
    dbms_output.put_line('l_req_id: '||l_req_id);
                                         
     COMMIT;
   
    IF l_req_id <= 0 THEN
       
   
     fnd_file.put_line(fnd_file.output,'*** Receiving Transaction Processor runing exception ***');
    ELSIF l_req_id > 0 THEN
   
     fnd_file.put_line(fnd_file.output,'*** Receiving Transaction Processor Request Submit successful ***');
     COMMIT;
     l_complete_flag := fnd_concurrent.wait_for_request(l_req_id,
                                                        1,
                                                        3600,
                                                        l_phase,
                                                        l_status,
                                                        l_dev_phase,
                                                        l_dev_status,
                                                        l_message);
                                                       
    END IF;
             
    IF  l_complete_flag  THEN
     FOR CURSOR3 IN receipt(l_req_id) LOOP
        IF  CURSOR3.receipt_num IS NULL THEN  
         --** check the po_interface_errors*--
         select count(*)into v_count
          from po_interface_errors a where a.request_id=l_req_id;
           fnd_file.put_line(fnd_file.output,'The po_interface_errors exists error , you can runing the report "Receiving Interface Errors Report" to check !' || CURSOR3.receipt_num);
           DBMS_OUTPUT.PUT_LINE('The po_interface_errors exists error , you can runing the report "Receiving Interface Errors Report" to check !' || CURSOR3.receipt_num);
         --** check the po_interface_errors*--
        ELSE
          fnd_file.put_line(fnd_file.output,'Receipt Number:' || CURSOR3.receipt_num);
          DBMS_OUTPUT.PUT_LINE('Receipt Number:' || CURSOR3.receipt_num);
        END IF;
   
     END LOOP;
    ELSE
       fnd_file.put_line(fnd_file.output,'*** Receiving Transaction Processing ***');
       DBMS_OUTPUT.PUT_LINE('*** Receiving Transaction Processing ***');
    END IF;*/
 
  END bg_po_receipt_sysauto;
  PROCEDURE bg_rtv_sysauto(p_org_id IN NUMBER) IS
 
    v_user_id     number;
    v_group_id    number;
    l_request_id  number;
    l_request_id2 number;
 
    v_inv_period NUMBER;
    v_pur_period NUMBER;
    v_ap_period  NUMBER;
    v_ar_period  NUMBER;
    v_gl_period  NUMBER;
 
    CURSOR c_period_status(p_trx_date DATE) IS
      SELECT (SELECT COUNT(p.status)
                FROM ORG_ACCT_PERIODS_V p
               WHERE p.organization_id = p_org_id
                 AND upper(p.status) = upper('Open')
                 AND trunc(p_trx_date) BETWEEN p.start_date AND p.end_date) inv_period,
             (SELECT COUNT(g.closing_status)
                FROM GL_PERIOD_STATUSES g
               WHERE g.application_id = 201
                 AND g.set_of_books_id = 302
                 AND upper(g.closing_status) = upper('O')
                 AND trunc(p_trx_date) BETWEEN g.start_date AND g.end_date) pur_period,
             (SELECT COUNT(g.closing_status)
                FROM GL_PERIOD_STATUSES g
               WHERE g.application_id = 200
                 AND g.set_of_books_id = 302
                 AND upper(g.closing_status) = upper('O')
                 AND trunc(p_trx_date) BETWEEN g.start_date AND g.end_date) ap_period,
             (SELECT COUNT(g.closing_status)
                FROM GL_PERIOD_STATUSES g
               WHERE g.application_id = 222
                 AND g.set_of_books_id = 302
                 AND upper(g.closing_status) = upper('O')
                 AND trunc(p_trx_date) BETWEEN g.start_date AND g.end_date) ar_period,
             (SELECT COUNT(g.closing_status)
                FROM GL_PERIOD_STATUSES g
               WHERE g.application_id = 101
                 AND g.set_of_books_id = 302
                 AND upper(g.closing_status) = upper('O')
                 AND trunc(p_trx_date) BETWEEN g.start_date AND g.end_date) gl_period
        FROM dual;
 
    CURSOR c_cms_rtv IS
      SELECT bcr.c_transaction_id,
             bcr.po_number,
             bcr.item_number,
             bcr.line_number,
             bcr.shipment_line_num,
             bcr.transaction_qty,
             bcr.transaction_uom,
             bcr.transaction_date,
             bcr.grn_number,
             bcr.subinventory_code,
             bcr.mrb_no,
             bcr.operator
        FROM BG_CUT_RCV_Interface bcr
       WHERE 1 = 1
         AND bcr.transaction_type = 'RTV'
         AND bcr.processing_status = 'P'
         AND bcr.organization_id = p_org_id
       ORDER BY bcr.transaction_date;
 
    CURSOR c_po_info(p_po_number            IN VARCHAR2,
                     p_line_number          IN NUMBER,
                     p_shipment_line_number IN NUMBER) IS
      SELECT pl.org_Id,
             PL.ITEM_ID,
             pl.po_header_id,
             PL.PO_LINE_ID,
             PL.LINE_NUM,
             PLL.QUANTITY,
             PL.UNIT_MEAS_LOOKUP_CODE,
             MP.ORGANIZATION_CODE,
             PLL.LINE_LOCATION_ID,
             PLL.CLOSED_CODE,
             PLL.QUANTITY_RECEIVED,
             PLL.CANCEL_FLAG,
             PLL.SHIPMENT_NUM,
             pda.destination_type_code,
             pda.deliver_to_person_id,
             pll.ship_to_location_id,
             pda.destination_subinventory,
             pda.destination_organization_id
        FROM PO_LINE_LOCATIONS_ALL     PLL,
             MTL_PARAMETERS            MP,
             apps.po_distributions_all pda,
             PO_LINES_ALL              PL,
             po_headers_all            poh
       WHERE PL.PO_LINE_ID = PLL.PO_LINE_ID
         AND pll.shipment_num = p_shipment_line_number
         AND pl.line_num = p_line_number
         AND PLL.SHIP_TO_ORGANIZATION_ID = MP.ORGANIZATION_ID
         AND pll.line_location_id = pda.line_location_id
         AND pl.po_header_id = poh.po_header_id
         AND pl.org_id = poh.org_id
         AND poh.Type_Lookup_Code = 'STANDARD'
         AND poh.org_id = p_org_id
         AND poh.segment1 = p_po_number; --'BSZ20110217';
 
    CURSOR c_rcv_info(p_po_header_id     IN NUMBER,
                      p_po_line_id       IN NUMBER,
                      p_line_location_id IN NUMBER,
                      p_grn_number       IN VARCHAR2) IS
      SELECT rt.transaction_id,
             rt.group_id,
             rt.po_header_id,
             rt.po_line_id,
             rt.po_line_location_id,
             rt.po_distribution_id,
             rt.organization_id,
             rt.vendor_id,
             rt.vendor_site_id,
             rt.unit_of_measure,
             rt.destination_type_code,
             rt.subinventory,
             rt.shipment_header_id,
             rt.routing_header_id,
             rt.shipment_line_id,
             rt.primary_quantity,
             rsh.expected_receipt_date
        FROM rcv_shipment_headers rsh, rcv_transactions rt
       WHERE rt.shipment_header_id = rsh.shipment_header_id
         AND rt.transaction_type = 'DELIVER'
         AND rsh.receipt_num = p_grn_number --'922748'
         AND rt.po_line_location_id = p_line_location_id --768212
         AND rt.po_line_id = p_po_line_id --744434
         AND rt.po_header_id = p_po_header_id; --340266;
 
  BEGIN
    v_user_id := fnd_global.USER_ID;
    Select RCV_INTERFACE_GROUPS_S.nextval into v_group_id from dual;
 
    FOR c_rtv IN c_cms_rtv LOOP
      OPEN c_period_status(c_rtv.transaction_date);
      FETCH c_period_status
        INTO v_inv_period,
             v_pur_period,
             v_ap_period,
             v_ar_period,
             v_gl_period;
      CLOSE c_period_status;
   
      IF v_inv_period = 1 AND v_pur_period = 1 AND v_ap_period = 1 AND
         v_ar_period = 1 AND v_gl_period = 1 THEN
     
        FOR c_po IN c_po_info(c_rtv.po_number,
                              c_rtv.line_number,
                              c_rtv.shipment_line_num) LOOP
          FOR rec IN c_rcv_info(c_po.po_header_id,
                                c_po.po_line_id,
                                c_po.line_location_id,
                                c_rtv.grn_number) LOOP
            INSERT INTO RCV_TRANSACTIONS_INTERFACE
              (INTERFACE_TRANSACTION_ID,
               GROUP_ID,
               LAST_UPDATE_DATE,
               LAST_UPDATED_BY,
               CREATION_DATE,
               CREATED_BY,
               LAST_UPDATE_LOGIN,
               TRANSACTION_TYPE,
               TRANSACTION_DATE,
               PROCESSING_STATUS_CODE,
               PROCESSING_MODE_CODE,
               TRANSACTION_STATUS_CODE,
               QUANTITY,
               PRIMARY_QUANTITY,
               UNIT_OF_MEASURE,
               ITEM_ID,
               ITEM_DESCRIPTION,
               EMPLOYEE_ID,
               SHIPMENT_HEADER_ID,
               SHIPMENT_LINE_ID,
               Routing_Header_Id,
               RECEIPT_SOURCE_CODE,
               Interface_Source_Code,
               VENDOR_ID,
               VENDOR_SITE_ID,
               FROM_ORGANIZATION_ID,
               FROM_SUBINVENTORY,
               SUBINVENTORY,
               TO_ORGANIZATION_ID,
               FROM_LOCATOR_ID,
               EXPECTED_RECEIPT_DATE,
               SOURCE_DOCUMENT_CODE,
               PARENT_TRANSACTION_ID,
               PO_HEADER_ID,
               PO_LINE_ID,
               PO_LINE_LOCATION_ID,
               PO_DISTRIBUTION_ID,
               DESTINATION_TYPE_CODE,
               DESTINATION_CONTEXT,
               DELIVER_TO_PERSON_ID,
               LOCATION_ID,
               DELIVER_TO_LOCATION_ID,
               VALIDATION_FLAG)
            VALUES
              (rcv_transactions_interface_s.nextval, --INTERFACE_TRANSACTION_ID
               v_group_id, --rcv_interface_groups_s.nextval, --GROUP_ID
               SYSDATE, --LAST_UPDATE_DATE
               v_user_id, --LAST_UPDATE_BY
               SYSDATE, --CREATION_DATE
               v_user_id, --CREATED_BY
               v_user_id, --LAST_UPDATE_LOGIN
               'RETURN TO VENDOR', --TRANSACTION_TYPE
               c_rtv.transaction_date, --SYSDATE, --TRANSACTION_DATE
               'PENDING', --PROCESSING_STATUS_CODE
               'IMMEDIATE', --PROCESSING_MODE_CODE
               'PENDING', --TRANSACTION_STATUS_CODE
               c_rtv.transaction_qty, --c_receipt.primary_quantity, --QUANTITY
               c_rtv.transaction_qty, --c_receipt.primary_quantity, --PRIMARY_QUANTITY
               c_rtv.transaction_uom, --c_receipt.unit_of_measure, --UNIT_OF_MEASURE
               Null, --ITEM_ID
               Null,
               0, --EMPLOYEE_ID
               rec.shipment_header_id, -- SHIPMENT_HEADER_ID
               rec.shipment_line_id, -- SHIPMENT_LINE_ID
               rec.routing_header_id, -- Routing_Header_Id
               'VENDOR', --RECEIPT_SOURCE_CODE
               'RCV', -- Interface_Source_Code
               rec.vendor_id, -- VENDOR_ID
               rec.vendor_site_id, -- VENDOR_SITE_ID
               rec.organization_id, -- FROM_ORGANIZATION_ID
               NULL, --'NA01'     --FROM_SUBINVENTORY
               c_rtv.subinventory_code, -- subinventory
               rec.organization_id, -- TO_ORGANIZATION_ID
               null, --FROM_LOCATOR_ID
               rec.expected_receipt_date, --EXPECTED_RECEIPT_DATE
               'PO', --SOURCE_DOCUMENT_CODE
               rec.transaction_id, -- PARENT_TRANSACTION_ID
               rec.po_header_id, -- PO_HEADER_ID
               rec.po_line_id, -- PO_LINE_ID
               rec.po_line_location_id, -- PO_LINE_LOCATION_ID
               rec.po_distribution_id, -- PO_DISTRIBUTION_ID
               'RECEIVING', --rec.destination_type_code, --'RECEIVING', --DESTINATION_TYPE_CODE
               rec.destination_type_code, --DESTINATION_CONTEXT
               null, --DELIVER_TO_PERSON_ID
               null, --LOCATION_ID
               null, --DELIVER_TO_LOCATION_ID
               'Y' --VALIDATION_FLAG
               );
         
          END LOOP;
       
        END LOOP;
     
        UPDATE bg_cut_rcv_interface
           SET processing_status = 'S'
         WHERE processing_status = 'P'
           AND transaction_type = 'RTV'
           AND c_transaction_id = c_rtv.c_transaction_id;
     
      ELSE
        fnd_file.PUT_LINE(fnd_file.OUTPUT,
                          '------------------------------------------');
        fnd_file.PUT_LINE(fnd_file.OUTPUT,
                          '*** The period is not opened! ***    Period: ' ||
                          to_char(c_rtv.transaction_date, 'MON-YYYY'));
        fnd_file.PUT_LINE(fnd_file.OUTPUT,
                          '------------------------------------------');
     
      END IF;
   
    END LOOP;
 
    COMMIT;
 
    l_request_id := fnd_request.submit_request('PO', --APPLICATIONI SHORT NAME;
                                               'RVCTP', --PROGRAME SHORT NAME;
                                               '',
                                               '',
                                               FALSE,
                                               'IMMEDIATE',
                                               v_group_id
                                               --104--104,
                                               --CHR(0)
                                               );
 
    l_request_id2 := fnd_request.submit_request('PO', --APPLICATIONI SHORT NAME;
                                                'RCVDLPDT', --PROGRAME SHORT NAME;
                                                '',
                                                '',
                                                FALSE,
                                                'P_group_id=' || v_group_id,
                                                'P_receipt_source_type=Supplier',
                                                'P_qty_precision=2',
                                                'P_org_id=' || p_org_id);
 
    FND_FILE.put_line(2,
                      'Receiving Transaction Processor: ' || l_request_id ||
                      ' Receipt Traveller Concurrent program: ' ||
                      l_request_id2);
    FND_FILE.put_line(2, 'Group Id: ' || v_group_id);
 
    COMMIT;
 
  END bg_rtv_sysauto;
  PROCEDURE bg_mtl_sysauto(p_org_id IN NUMBER) IS
 
    v_source_line_id   NUMBER := 1;
    v_source_header_id NUMBER := 1;
    v_reason_id        mtl_transaction_reasons.reason_id%TYPE;
    v_primary_qty      NUMBER;
 
    v_inv_period NUMBER;
    v_pur_period NUMBER;
    v_ap_period  NUMBER;
    v_ar_period  NUMBER;
    v_gl_period  NUMBER;
 
    CURSOR c_period_status(p_trx_date DATE) IS
      SELECT (SELECT COUNT(p.status)
                FROM ORG_ACCT_PERIODS_V p
               WHERE p.organization_id = p_org_id
                 AND upper(p.status) = upper('Open')
                 AND trunc(p_trx_date) BETWEEN p.start_date AND p.end_date) inv_period,
             (SELECT COUNT(g.closing_status)
                FROM GL_PERIOD_STATUSES g
               WHERE g.application_id = 201
                 AND g.set_of_books_id = 302
                 AND upper(g.closing_status) = upper('O')
                 AND trunc(p_trx_date) BETWEEN g.start_date AND g.end_date) pur_period,
             (SELECT COUNT(g.closing_status)
                FROM GL_PERIOD_STATUSES g
               WHERE g.application_id = 200
                 AND g.set_of_books_id = 302
                 AND upper(g.closing_status) = upper('O')
                 AND trunc(p_trx_date) BETWEEN g.start_date AND g.end_date) ap_period,
             (SELECT COUNT(g.closing_status)
                FROM GL_PERIOD_STATUSES g
               WHERE g.application_id = 222
                 AND g.set_of_books_id = 302
                 AND upper(g.closing_status) = upper('O')
                 AND trunc(p_trx_date) BETWEEN g.start_date AND g.end_date) ar_period,
             (SELECT COUNT(g.closing_status)
                FROM GL_PERIOD_STATUSES g
               WHERE g.application_id = 101
                 AND g.set_of_books_id = 302
                 AND upper(g.closing_status) = upper('O')
                 AND trunc(p_trx_date) BETWEEN g.start_date AND g.end_date) gl_period
        FROM dual;
 
    CURSOR c_mtl IS
      SELECT bcm.c_transaction_id,
             bcm.Organization_Id,
             msi.inventory_item_id,
             bcm.item_number,
             bcm.transaction_qty,
             bcm.transaction_uom,
             msi.primary_uom_code,
             bcm.subinventory_code,
             bcm.transaction_subinventory,
             bcm.transaction_date,
             bcm.operator,
             bcm.reason_name,
             bcm.reference,
             bcm.document_no,
             bcm.mrb_no
        FROM mtl_system_items_b msi, bg_cut_mtl_interface bcm
       WHERE msi.segment1 = bcm.item_number
         AND msi.organization_Id = bcm.Organization_Id
         AND bcm.transaction_type = 'MTL'
         AND bcm.processing_status = 'P'
         AND bcm.Organization_Id = p_org_id
       ORDER BY bcm.transaction_date;
 
    CURSOR c_reason(p_reason_name IN VARCHAR2) IS
      SELECT reason_id
        FROM mtl_transaction_reasons
       WHERE reason_name = p_reason_name;
    n_txn_hdr_id   number;
    n_INTERFACE_ID number;
    n_proces_txn   number;
    x_msg_count    number;
    x_trans_count  number;
    x_msg_data     varchar2(20000);
    x_returnstatus VARCHAR2(4000);
    x_errormsg     VARCHAR2(4000);
  BEGIN
    FOR rec IN c_mtl LOOP
      OPEN c_period_status(rec.transaction_date);
      FETCH c_period_status
        INTO v_inv_period,
             v_pur_period,
             v_ap_period,
             v_ar_period,
             v_gl_period;
      CLOSE c_period_status;
   
      IF v_inv_period = 1 AND v_pur_period = 1 AND v_ap_period = 1 AND
         v_ar_period = 1 AND v_gl_period = 1 THEN
     
        SELECT INV_CONVERT.inv_um_convert(rec.inventory_item_id,
                                          2,
                                          rec.transaction_qty,
                                          rec.transaction_uom,
                                          rec.primary_uom_code,
                                          NULL,
                                          NULL)
          INTO v_primary_qty
          FROM dual;
        /*    dbms_output.put_line('inventory_item_id' || rec.inventory_item_id ||
        rec.transaction_qty || rec.transaction_uom ||
        rec.primary_uom_code);*/
     
        IF v_primary_qty <> -99999 THEN
       
          IF rec.reason_name IS NOT NULL THEN
            OPEN c_reason(rec.reason_name);
            FETCH c_reason
              INTO v_reason_id;
            CLOSE c_reason;
          END IF;
          -- dbms_output.put_line('Insert data to mtl_transactions_interface!');
       
          SELECT BG_MTL_TXN_HEADER_S.NEXTVAL INTO n_txn_hdr_id FROM dual;
          SELECT BG_MTL_TXN_INTERFACE_S.NEXTVAL
            INTO n_INTERFACE_ID
            FROM dual;
       
          INSERT INTO mtl_transactions_interface
            (TRANSACTION_INTERFACE_ID, -- 1
             TRANSACTION_HEADER_ID, -- 2
             SOURCE_CODE, -- 3
             SOURCE_LINE_ID, -- 4
             SOURCE_HEADER_ID, -- 5
             PROCESS_FLAG, -- 6
             TRANSACTION_MODE, -- 7
             -- LOCK_FLAG,                      -- 8
             LAST_UPDATE_DATE, -- 9
             LAST_UPDATED_BY, -- 10
             CREATION_DATE, --11
             CREATED_BY, -- 12
             LAST_UPDATE_LOGIN, -- 13
             INVENTORY_ITEM_ID, -- 14
             ITEM_SEGMENT1, -- 15
             ORGANIZATION_ID, -- 16
             TRANSACTION_QUANTITY, -- 17
             PRIMARY_QUANTITY, -- 18
             TRANSACTION_UOM, -- 19
             TRANSACTION_DATE, -- 20
             ACCT_PERIOD_ID, -- 21
             SUBINVENTORY_CODE, -- 22
             TRANSACTION_SOURCE_NAME, -- 23
             TRANSACTION_SOURCE_TYPE_ID, -- 24 :13
             TRANSACTION_ACTION_ID, -- 25:2
             TRANSACTION_TYPE_ID, -- 26:2
             REASON_ID, --27
             TRANSACTION_REFERENCE, -- 28
             DISTRIBUTION_ACCOUNT_ID, -- 29
             TRANSFER_SUBINVENTORY, -- 30
             TRANSFER_ORGANIZATION, -- 31
             ATTRIBUTE_CATEGORY, --32
             attribute1, --33
             attribute2, --34
             attribute12, --35
             COST_GROUP_ID --36
             )
          VALUES
            (n_INTERFACE_ID,
             n_txn_hdr_id,
             'BG_CUT', --3
             0, --v_source_line_id,           --4
             0, --v_source_header_id,         --5
             1, --   PROCESS_FLAG          --6
             3, --7--3-background 2-immediate
             -- 2,                          --8
             SYSDATE, -- 9--last_update_date
             fnd_global.user_id, -- 10
             SYSDATE, --11
             fnd_global.user_id, --12
             1, --13  --FND_GLOBAL.CONC_LOGIN_ID,    --12
             rec.inventory_item_id, --14
             rec.item_number, --15
             rec.organization_id, --16
             rec.transaction_qty, --17
             v_primary_qty, --18    PRIMARY_QUANTITY
             rec.transaction_uom, --19
             rec.transaction_date, --20
             NULL, --21
             rec.subinventory_code, --22
             NULL, --23
             13, --24
             2, --2:Subinventory transfer  --25
             2, --2:Subinventory transfer-  --26
             v_reason_id, --27--32 INV - Subinventory Transfer
             rec.reference, --28
             NULL, --29 DISTRIBUTION_ACCOUNT_ID
             rec.transaction_subinventory, --30
             rec.organization_id, --31
             rec.organization_id, --32
             'N/A', --33
             rec.document_no, --34
             rec.mrb_no, --35
             NULL --36
             );
          begin
            n_proces_txn := inv_txn_manager_pub.process_transactions(p_api_version      => 1,
                                                                     p_init_msg_list    => fnd_api.g_true,
                                                                     p_commit           => fnd_api.g_true,
                                                                     p_validation_level => fnd_api.g_valid_level_full,
                                                                     x_return_status    => x_returnstatus,
                                                                     x_msg_count        => x_msg_count,
                                                                     x_msg_data         => x_msg_data,
                                                                     x_trans_count      => x_trans_count,
                                                                     p_table            => 1,
                                                                     p_header_id        => n_txn_hdr_id);
         
            dbms_output.put_line('Process - inv_txn_manager_pub [Transaction Header Id:' ||
                                 n_txn_hdr_id || ']Completed');
            dbms_output.put_line('Return Status : ' || x_returnstatus ||
                                 'Completed');
            dbms_output.put_line('Return Error :' || x_msg_data ||
                                 'Completed');
         
            UPDATE bg_cut_mtl_interface
               SET processing_status = 'S'
             WHERE processing_status = 'P'
               AND c_transaction_id = rec.c_transaction_id;
         
          exception
            when others then
           
              dbms_output.put_line('Process - inv_txn_manager_pub [Transaction Header Id:' ||
                                   n_txn_hdr_id || ']Fail');
              dbms_output.put_line('Return Status :' || x_returnstatus ||
                                   'Fail');
              dbms_output.put_line('Return Error : ' || x_msg_data ||
                                   'Fail');
           
              UPDATE bg_cut_mtl_interface
                 SET processing_status = 'E'
               WHERE processing_status = 'P'
                 AND c_transaction_id = rec.c_transaction_id;
           
          end;
       
        END IF;
     
      ELSE
        fnd_file.PUT_LINE(fnd_file.OUTPUT,
                          '------------------------------------------');
        fnd_file.PUT_LINE(fnd_file.OUTPUT,
                          '*** The period is not opened! ***    Period: ' ||
                          to_char(rec.transaction_date, 'MON-YYYY'));
        fnd_file.PUT_LINE(fnd_file.OUTPUT,
                          '------------------------------------------');
     
      END IF;
   
    END LOOP;
 
    COMMIT;
 
  END bg_mtl_sysauto;
  ---Job Complete--
  PROCEDURE bg_wip_sysauto(p_org_id IN NUMBER) IS
 
    v_inv_period NUMBER;
    v_pur_period NUMBER;
    v_ap_period  NUMBER;
    v_ar_period  NUMBER;
    v_gl_period  NUMBER;
 
    CURSOR c_period_status(p_trx_date DATE) IS
      SELECT (SELECT COUNT(p.status)
                FROM ORG_ACCT_PERIODS_V p
               WHERE p.organization_id = p_org_id
                 AND upper(p.status) = upper('Open')
                 AND trunc(p_trx_date) BETWEEN p.start_date AND p.end_date) inv_period,
             (SELECT COUNT(g.closing_status)
                FROM GL_PERIOD_STATUSES g
               WHERE g.application_id = 201
                 AND g.set_of_books_id = 302
                 AND upper(g.closing_status) = upper('O')
                 AND trunc(p_trx_date) BETWEEN g.start_date AND g.end_date) pur_period,
             (SELECT COUNT(g.closing_status)
                FROM GL_PERIOD_STATUSES g
               WHERE g.application_id = 200
                 AND g.set_of_books_id = 302
                 AND upper(g.closing_status) = upper('O')
                 AND trunc(p_trx_date) BETWEEN g.start_date AND g.end_date) ap_period,
             (SELECT COUNT(g.closing_status)
                FROM GL_PERIOD_STATUSES g
               WHERE g.application_id = 222
                 AND g.set_of_books_id = 302
                 AND upper(g.closing_status) = upper('O')
                 AND trunc(p_trx_date) BETWEEN g.start_date AND g.end_date) ar_period,
             (SELECT COUNT(g.closing_status)
                FROM GL_PERIOD_STATUSES g
               WHERE g.application_id = 101
                 AND g.set_of_books_id = 302
                 AND upper(g.closing_status) = upper('O')
                 AND trunc(p_trx_date) BETWEEN g.start_date AND g.end_date) gl_period
        FROM dual;
 
    CURSOR c_cut_wip IS
      SELECT bcw.c_transaction_id,
             bcw.transaction_type,
             bcw.transaction_date,
             bcw.organization_id,
             mp.organization_code,
             bcw.transaction_qty,
             bcw.transaction_uom,
             msi.inventory_item_id,
             bcw.item_number,
             we.wip_entity_id,
             bcw.job_number,
             we.entity_type,
             wdj.bom_revision_date,
             wdj.routing_revision_date,
             bcw.reason_name,
             bcw.Reference,
             wo.operation_seq_num      fr_oper_seq_num,
             bso.operation_code        fr_operation_code,
             wo.department_id          fr_dept_id,
             bd.department_code        fr_dept_code,
             --'Queue'  FM_INTRAOPERATION_STEP_TYPE,
             1                    FM_INTRAOPERATION_STEP_TYPE,
             wo.operation_seq_num to_oper_seq_num,
             bso.operation_code   to_operation_code,
             wo.department_id     to_dept_id,
             bd.department_code   to_dept_code,
             --'To move'  TO_INTRAOPERATION_STEP_TYPE,
             3                           TO_INTRAOPERATION_STEP_TYPE,
             wdj.completion_subinventory
        FROM mtl_parameters          mp,
             BOM_STANDARD_OPERATIONS bso,
             BOM_DEPARTMENTS         bd,
             WIP_OPERATIONS          wo,
             mtl_system_items_b      msi,
             wip_discrete_jobs       wdj,
             wip_entities            we,
             BG_CUT_WIP_Interface    bcw
       WHERE 1 = 1
         AND mp.organization_id = p_org_id
         AND wo.standard_operation_id = bso.standard_operation_id
         AND BD.DEPARTMENT_ID(+) = WO.DEPARTMENT_ID
         AND wo.wip_entity_id = we.wip_entity_id
         AND msi.inventory_item_id = we.primary_item_id
         AND msi.organization_id = we.organization_id
         AND wdj.wip_entity_id = we.wip_entity_id
         AND we.wip_entity_name = bcw.job_number
         AND bcw.transaction_type = 'WIP'
         AND bcw.processing_status = 'P'
         AND bcw.organization_id = p_org_id;
 
  BEGIN
    FOR rec IN c_cut_wip LOOP
      OPEN c_period_status(rec.transaction_date);
      FETCH c_period_status
        INTO v_inv_period,
             v_pur_period,
             v_ap_period,
             v_ar_period,
             v_gl_period;
      CLOSE c_period_status;
      /*  INSERT INTO mtl_transactions_interface
           ( source_code,
             source_line_id,
             source_header_id,
             process_flag,
             --validation_required,
             transaction_mode,
             lock_flag,
             last_update_date,
             last_updated_by,
             creation_date,
             created_by,
             LAST_UPDATE_LOGIN,
             inventory_item_id,
             organization_id,
             transaction_quantity,
             primary_quantity,
             transaction_uom,
             transaction_date,
             subinventory_code,
             transaction_source_id,
             transaction_source_type_id,
             transaction_action_id,
             transaction_type_id,
             transaction_reference,
             wip_entity_type,
             operation_seq_num,
             bom_revision_date,
             routing_revision_date,
             scheduled_flag,
             final_completion_flag,
             Flow_schedule,
             --revision,
             transaction_interface_id)
      VALUES('WIP Comp',                -- source_code
             1,                      -- source_line_id
             1,                      -- source_header_id
             1,                      -- process_flag
             3,                      -- transaction_mode
             2,                      -- lock_flag
             SYSDATE,
             fnd_global.user_id,   --last_updated_by
             SYSDATE,
             fnd_global.user_id,     -- created_by
             -1,    --LAST_UPDATE_LOGIN
             rec.inventory_item_id,  -- inventory_item_id
             rec.organization_id,
             rec.transaction_qty,
             rec.transaction_qty,    -- primary_qty
             rec.transaction_uom,
             SYSDATE,
            -- rec.transaction_date,
             rec.completion_subinventory,
             rec.wip_entity_id,      -- transaction_source_id
             5,                      -- trans_source_type_id
             31,                     -- trans_action_id
             44,                     -- trans_type_id
             rec.reference||'-001',
             rec.entity_type,        -- wip_entity_type
             rec.fr_oper_seq_num,    -- operation_seq_num
           NULL,  --  rec.bom_revision_date,  -- bom_revision_date
            NULL, -- rec.routing_revision_date,   --routing_revision_date
             NULL,                   -- scheduled_flag
             'Y',                    --final_completion_flag
             NULL,                   --Flow_schedule
             NULL );   */
   
      IF v_inv_period = 1 AND v_pur_period = 1 AND v_ap_period = 1 AND
         v_ar_period = 1 AND v_gl_period = 1 THEN
     
        INSERT INTO wip_move_txn_interface
          (LAST_UPDATE_DATE,
           LAST_UPDATED_BY,
           LAST_UPDATED_BY_NAME,
           CREATION_DATE,
           CREATED_BY,
           CREATED_BY_NAME,
           LAST_UPDATE_LOGIN,
           group_id,
           source_code,
           --source_code, source_line_id
           PROCESS_PHASE,
           PROCESS_STATUS,
           transaction_type,
           ENTITY_TYPE,
           organization_id,
           organization_code,
           wip_entity_id,
           wip_entity_name,
           PRIMARY_ITEM_ID,
           transaction_date,
           --acct_period_id,
           fm_operation_seq_num,
           fm_operation_code,
           fm_department_id,
           fm_intraoperation_step_type,
           to_operation_seq_num,
           to_operation_code,
           to_department_id,
           to_intraoperation_step_type,
           transaction_quantity,
           PRIMARY_QUANTITY,
           transaction_uom,
           REASON_NAME,
           reference)
        VALUES
          (SYSDATE, -- last_update_date
           fnd_global.user_id,
           fnd_global.user_name, --LAST_UPDATED_BY_NAME
           SYSDATE,
           fnd_global.user_id,
           fnd_global.user_name, --CREATED_BY_NAME
           -1, --LAST_UPDATE_LOGIN
           NULL, --group_id
           'WIP Comp', ---source_code
           1, -- process_phase
           1, -- process_status
           2, -- transaction_type
           1, -- entity_type
           rec.organization_id,
           rec.Organization_Code, -- must be not null ??
           rec.wip_entity_id,
           rec.job_number, -- wip_entity_name,
           rec.Inventory_Item_Id,
           rec.transaction_date,
           rec.fr_oper_seq_num,
           rec.fr_operation_code,
           rec.fr_dept_id, -- from_department_id
           rec.fm_intraoperation_step_type,
           rec.To_Oper_Seq_Num,
           rec.To_Operation_Code, -- to_operation_code
           rec.To_Dept_Id,
           rec.To_Intraoperation_Step_Type,
           rec.transaction_qty,
           rec.transaction_qty,
           rec.transaction_uom,
           rec.reason_name,
           rec.reference);
     
        UPDATE BG_CUT_WIP_Interface
           SET PROCESSING_STATUS = 'S'
         WHERE c_transaction_id = rec.c_transaction_id;
     
      ELSE
        fnd_file.PUT_LINE(fnd_file.OUTPUT,
                          '------------------------------------------');
        fnd_file.PUT_LINE(fnd_file.OUTPUT,
                          '*** The period is not opened! ***    Period: ' ||
                          to_char(rec.transaction_date, 'MON-YYYY'));
        fnd_file.PUT_LINE(fnd_file.OUTPUT,
                          '------------------------------------------');
     
      END IF;
   
    END LOOP;
 
    COMMIT;
 
    --FND_FILE.put_line(1, 'User id : ' || fnd_global.user_id);
 
  END bg_wip_sysauto;
 
  FUNCTION bg_get_receipt_qty(p_in_po_header_id   IN NUMBER,
                              p_in_po_line_number IN NUMBER,
                              p_in_shipment_no    IN NUMBER,
                              p_in_waybill        IN VARCHAR2) RETURN NUMBER IS
    v_receipt_qty NUMBER;
    v_po_number   po_headers_all.Segment1%TYPE;
 
    CURSOR c_get_po IS
      SELECT segment1
        FROM po_headers_all
       WHERE po_header_id = p_in_po_header_id;
 
    CURSOR c_get_qty IS
      SELECT bcr.transaction_qty
        FROM BG_CUT_RCV_Interface bcr
       WHERE bcr.waybill = p_in_waybill
         AND bcr.line_number = p_in_po_line_number
         AND bcr.shipment_line_num = p_in_shipment_no
         AND bcr.transaction_type = 'PO'
         AND bcr.processing_status = 'P'
         AND bcr.po_number = v_po_number;
  BEGIN
    OPEN c_get_po;
    FETCH c_get_po
      INTO v_po_number;
    CLOSE c_get_po;
 
    OPEN c_get_qty;
    FETCH c_get_qty
      INTO v_receipt_qty;
    CLOSE c_get_qty;
 
    RETURN v_receipt_qty;
 
  END bg_get_receipt_qty;
  FUNCTION bg_get_conv_rate(p_in_po_number IN VARCHAR2,
                            p_in_org_id    IN NUMBER,
                            p_in_trx_date  IN DATE) RETURN NUMBER IS
 
    v_currency_code po_headers_all.currency_code%TYPE;
    v_rate          gl_daily_rates.conversion_rate%TYPE;
 
    CURSOR c_get_currency_code IS
      SELECT poh.currency_code
        FROM po_headers_all poh
       WHERE poh.org_id = p_in_org_id
         AND poh.segment1 = p_in_po_number;
 
  BEGIN
    BEGIN
      OPEN c_get_currency_code;
      FETCH c_get_currency_code
        INTO v_currency_code;
      CLOSE c_get_currency_code;
   
      IF v_currency_code = 'CNY' THEN
        v_rate := 1;
      ELSE
        SELECT conversion_rate
          INTO v_rate
          FROM GL_DAILY_RATES
         WHERE from_currency = v_currency_code
           AND to_currency = 'CNY'
           AND conversion_date = trunc(p_in_trx_date)
           AND conversion_type = 'Corporate';
      END IF;
   
    EXCEPTION
      WHEN OTHERS THEN
        v_rate := NULL;
    END;
 
    RETURN v_rate;
 
  END bg_get_conv_rate;
  FUNCTION bg_get_employee_name(p_in_user_id IN NUMBER) RETURN VARCHAR2 IS
    v_employee_name VARCHAR2(100);
 
    CURSOR c_get_name IS
      SELECT full_name
        FROM PER_ALL_PEOPLE_F p, fnd_user f
       WHERE f.employee_id = p.person_id
         AND SYSDATE BETWEEN p.effective_start_date AND
             p.effective_end_date
         AND f.user_id = p_in_user_id;
 
  BEGIN
    OPEN c_get_name;
    FETCH c_get_name
      INTO v_employee_name;
    CLOSE c_get_name;
 
    RETURN v_employee_name;
 
  END bg_get_employee_name;
END INVGRCVK_BG_1;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9182041/viewspace-732781/,如需转载,请注明出处,否则将追究法律责任。

上一篇: Oracle ERP Maintenance
请登录后发表评论 登录
全部评论

注册时间:2008-05-15

  • 博文量
    39
  • 访问量
    115596