Inventory Miscellaneous Receipt or Miscellaneous issue scripts from backend in oracle apps

Inventory Miscellaneous Receipt or Miscellaneous issue scripts in oracle apps

Step1 : Run the Below Procedure 

create or replace PROCEDURE PKMTL_TRANSACTION(p_item_id           IN   VARCHAR2,
                           p_organization_id   IN   NUMBER,
                           p_subinv            IN   VARCHAR2,
                           p_loc               IN   VARCHAR2,
                           p_lot_no            IN   VARCHAR2,
                           p_uom_code          IN   VARCHAR2,
                           p_quantity          IN   NUMBER,
                           p_transaction_type  IN   VARCHAR2,
                           p_trans_type_ref    IN   VARCHAR2,
                           p_primary_qty       IN   NUMBER,
                           x_return_status     OUT  VARCHAR2,
                           x_msg_count         OUT  NUMBER,
                           x_msg_data          OUT  VARCHAR2)
IS

l_transaction_id        NUMBER;
l_loc_id                        NUMBER;
l_trn_type_id           NUMBER;
l_segment1                      VARCHAR2(150);
l_segment2                      VARCHAR2(150);
l_segment3                      VARCHAR2(150);
l_segment4                      VARCHAR2(150);
l_segment5                      VARCHAR2(150);
l_segment6                      VARCHAR2(150);
l_segment7                      VARCHAR2(150);
l_segment8                      VARCHAR2(150);
l_segment9                      VARCHAR2(150);
l_segment10             VARCHAR2(150);
l_segment11              VARCHAR2(150);
l_expiration_date       DATE;
l_trans_count       NUMBER;
lc_result           NUMBER;
l_subinv_code mtl_item_locations_kfv.subinventory_code%type;
r_mtl_trns_rec mtl_transactions_interface%rowtype;
R_MTL_LOT_REC MTL_TRANSACTION_LOTS_INTERFACE%ROWTYPE;

G_L_TRANSACTION_ID  number;
G_L_LPN_ID          number:=0;

CURSOR cur_tns_rec
IS
SELECT mtt.*
FROM MTL_TRANSACTION_TYPES mtt
where MTT.TRANSACTION_TYPE_NAME = P_TRANSACTION_TYPE;
R_TNS_REC CUR_TNS_REC%ROWTYPE;
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
      x_return_status:= null;
      x_msg_data := null;

dbms_output.put_line('================================================================================');
      BEGIN
           SELECT mtl_material_transactions_s.NEXTVAL
           INTO   l_transaction_id
           FROM   DUAL;
      EXCEPTION WHEN OTHERS THEN
          l_transaction_id := NULL;
      END;
      OPEN  cur_tns_rec;
      FETCH cur_tns_rec INTO r_tns_rec;
      CLOSE cur_tns_rec;

      BEGIN
      
      
      --l_segment1:=01;
                 l_segment1:='01';
                 l_segment2:='510';
                 l_segment3:='4110';
                 l_segment4:='0000';
                 l_segment5:='000';
                 l_segment6:=null;
                 l_segment7:=null;
                 l_segment8:=null;
                 l_segment9:=null;
                 l_segment10:=null;
           /*SELECT gcc.segment1,
                 gcc.segment2,
                 gcc.segment3,
                 gcc.segment4,
                 gcc.segment5,
                 gcc.segment6,
                 gcc.segment7,
                 gcc.segment8,
                 gcc.segment9,
                 gcc.segment10
           INTO   l_segment11,
                 l_segment2,
                 l_segment3,
                 l_segment4,
                 l_segment5,
                 l_segment6,
                 l_segment7,
                 l_segment8,
                 l_segment9,
                 l_segment10
           FROM   gl_code_combinations_kfv gcck, gl_code_combinations gcc
           where  GCC.CODE_COMBINATION_ID= 19547
               AND    gcc.code_combination_id = gcck.code_combination_id;*/
          EXCEPTION WHEN OTHERS THEN
                   l_segment1 :=NULL;
                   l_segment2 :=NULL;
                   l_segment6 :=NULL;
                   l_segment3 :=NULL;
                   l_segment4 :=NULL;
                   l_segment5 :=NULL;
                   l_segment7 :=NULL;
                   l_segment8 :=NULL;
                   l_segment9 :=NULL;
                   l_segment10 :=NULL;
          END;

          BEGIN
              SELECT inventory_location_id,subinventory_code
              INTO   l_loc_id,l_subinv_code
              FROM   mtl_item_locations_kfv
              WHERE  concatenated_segments = p_loc
              AND    organization_id = p_organization_id;
          EXCEPTION WHEN OTHERS THEN
                    l_loc_id := NULL;
          END;

          dbms_output.put_line('p_loc: '||p_loc);
          dbms_output.put_line('l_loc_id: '||l_loc_id);

          dbms_output.put_line('Transaction_type: '||r_tns_rec.transaction_type_name);
          dbms_output.put_line('p_quantity: '||p_quantity);
          BEGIN
              r_mtl_trns_rec := NULL;
              g_l_transaction_id := NULL;
              r_mtl_trns_rec.transaction_uom             :=  p_uom_code;
              r_mtl_trns_rec.transaction_date            :=  SYSDATE;
              r_mtl_trns_rec.source_code                 :=  p_transaction_type;
              r_mtl_trns_rec.source_line_id              :=  l_transaction_id; -- 422170
              r_mtl_trns_rec.source_header_id            :=  l_transaction_id ;  -- 167091
              r_mtl_trns_rec.process_flag                :=  1;
              r_mtl_trns_rec.transaction_mode            :=  3;
              r_mtl_trns_rec.lock_flag                   :=  2;
              r_mtl_trns_rec.locator_id                  :=  l_loc_id;
              r_mtl_trns_rec.last_update_date            :=  SYSDATE;
              r_mtl_trns_rec.last_updated_by             :=  fnd_global.user_id;
              r_mtl_trns_rec.creation_date               :=  SYSDATE;
              r_mtl_trns_rec.created_by                  :=  fnd_global.user_id;
              r_mtl_trns_rec.inventory_item_id           :=  p_item_id;
              r_mtl_trns_rec.subinventory_code           :=  nvl(p_subinv,l_subinv_code);
              r_mtl_trns_rec.organization_id             :=  p_organization_id;
              r_mtl_trns_rec.transaction_quantity        :=  p_quantity;
              r_mtl_trns_rec.primary_quantity            :=  p_primary_qty;
              R_MTL_TRNS_REC.TRANSACTION_TYPE_ID         :=  R_TNS_REC.TRANSACTION_TYPE_ID;
              r_mtl_trns_rec.transaction_source_type_id  :=  r_tns_rec.transaction_source_type_id;
              r_mtl_trns_rec.transaction_action_id       :=  r_tns_rec.transaction_action_id;
              r_mtl_trns_rec.dst_segment1                :=  l_segment1;
              r_mtl_trns_rec.dst_segment2                :=  l_segment2;
              r_mtl_trns_rec.dst_segment3                :=  l_segment3;
              r_mtl_trns_rec.dst_segment4                :=  l_segment4;
              r_mtl_trns_rec.dst_segment5                :=  l_segment5;
              r_mtl_trns_rec.dst_segment6                :=  l_segment6;
              r_mtl_trns_rec.dst_segment7                :=  l_segment7;
              r_mtl_trns_rec.dst_segment8                :=  l_segment8;
              r_mtl_trns_rec.dst_segment9                :=  l_segment9;
              r_mtl_trns_rec.dst_segment10               :=  l_segment10;
              r_mtl_trns_rec.transaction_interface_id    :=  l_transaction_id;
              r_mtl_trns_rec.transaction_header_id       :=  l_transaction_id;
              r_mtl_trns_rec.transaction_reference       :=  p_trans_type_ref;
              IF g_l_lpn_id >0 THEN
                 r_mtl_trns_rec.transfer_lpn_id      := g_l_lpn_id;

                 dbms_output.put_line('g_l_lpn_id: '||g_l_lpn_id);
              END IF;

              -- Insert MTI for inv open transactions.
              insert into MTL_TRANSACTIONS_INTERFACE values R_MTL_TRNS_REC;
              dbms_output.put_line('Record inserted into mtl_transactions_interface with transaction_header_id: '||l_transaction_id);
              g_l_transaction_id := l_transaction_id;

              --dbms_output.put_line('Inserted a record'||l_transaction_id);
              x_return_status := fnd_api.g_ret_sts_success;
          EXCEPTION WHEN OTHERS THEN
                 fnd_file.put_line (fnd_file.output,'in exception ---- inserting data into  MTL_TRANSACTIONS_INTERFACE : '|| SQLERRM);
                 dbms_output.put_line('Error'||SQLERRM);
                 x_return_status := fnd_api.g_ret_sts_error;
                 x_msg_count := x_msg_count+1;
                 x_msg_data := 'Error While Inserting data to MTL_TRANSACTIONS_INTERFACE :'||SQLERRM;
          END;

  DBMS_OUTPUT.PUT_LINE('STEP1');
         IF x_return_status != fnd_api.g_ret_sts_error  THEN --Successful return of first insert
            IF p_lot_no IS NOT NULL THEN
               --Derive lot expiration date for the lot number
               BEGIN
                    SELECT expiration_date
                    INTO l_expiration_date
                    FROM MTL_LOT_NUMBERS
                    WHERE lot_number = p_lot_no
                    AND inventory_item_id = p_item_id
                    AND organization_id = p_organization_id;
                    
               EXCEPTION WHEN OTHERS THEN
                 dbms_output.put_line('Exception in fetching lot expiration_date: '||sqlerrm);
                 l_expiration_date:= null;
                 DBMS_OUTPUT.PUT_LINE('STEP2');
               END;
DBMS_OUTPUT.PUT_LINE('STEP3');
               dbms_output.put_line('Expiration_date: '||l_expiration_date);
DBMS_OUTPUT.PUT_LINE('STEP3.1');
               BEGIN
               DBMS_OUTPUT.PUT_LINE('STEP4');
                    --Insert lot number interface if the item is lot controlled.
                    r_mtl_lot_rec := NULL;
                    g_l_transaction_id := l_transaction_id;
                    r_mtl_lot_rec.transaction_interface_id    := l_transaction_id;
                    r_mtl_lot_rec.source_code                 := p_transaction_type;
                    r_mtl_lot_rec.source_line_id              := l_transaction_id;
                    r_mtl_lot_rec.last_update_date            := SYSDATE;
                    r_mtl_lot_rec.last_updated_by             := FND_GLOBAL.USER_ID;
                    r_mtl_lot_rec.creation_date               := SYSDATE;
                    r_mtl_lot_rec.created_by                  := FND_GLOBAL.USER_ID;
                    r_mtl_lot_rec.last_update_login           := FND_GLOBAL.LOGIN_ID ;
                    r_mtl_lot_rec.lot_number                  := p_lot_no;
                    r_mtl_lot_rec.lot_expiration_date         := l_expiration_date;
                    r_mtl_lot_rec.transaction_quantity        := p_quantity   ;
                    r_mtl_lot_rec.primary_quantity            := p_quantity;
                    r_mtl_lot_rec.serial_transaction_temp_id  := l_transaction_id  ;
DBMS_OUTPUT.PUT_LINE('STEP5');
                    INSERT INTO  MTL_TRANSACTION_LOTS_INTERFACE VALUES r_mtl_lot_rec;
                    DBMS_OUTPUT.PUT_LINE('STEP6');
               EXCEPTION WHEN OTHERS THEN
                   x_return_status := fnd_api.g_ret_sts_error;
                   x_msg_count := x_msg_count+1;
                   x_msg_data := 'Error While Inserting data to MTL_TRANSACTION_LOTS_INTERFACE :'||SQLERRM;
                   DBMS_OUTPUT.PUT_LINE('STEP7');
               END;
            END IF;
DBMS_OUTPUT.PUT_LINE('STEP8');
            COMMIT;
            --call Inventory open transaction api to perform transfer of material from source locator to destination locator.
            DBMS_OUTPUT.PUT_LINE('STEP9');
            x_return_status := null;
            x_msg_count := 0;
            X_MSG_DATA := null;
            dbms_output.put_line('Before Calling process_transactions'||l_transaction_id);
            lc_result := inv_txn_manager_pub.process_transactions
                                                 (p_api_version           => 1.0,
                                                  p_init_msg_list         => fnd_api.g_false,
                                                  p_commit                => fnd_api.g_true,
                                                  p_validation_level      => fnd_api.g_valid_level_full,
                                                  x_return_status         => x_return_status,
                                                  x_msg_count             => x_msg_count,
                                                  x_msg_data              => x_msg_data,
                                                  x_trans_count           => l_trans_count,
                                                  p_table                 => 1,
                                                  P_HEADER_ID             => L_TRANSACTION_ID
                                                 );
                                                 DBMS_OUTPUT.PUT_LINE('STEP10');
            dbms_output.put_line('Return Status'||x_return_status);
            IF NVL(x_return_status,'N') <> FND_API.g_ret_sts_success THEN
               x_return_status := FND_API.g_ret_sts_error;
               dbms_output.put_line('Return Status: '||x_return_status);
               dbms_output.put_line('Message: '||x_msg_data);
               DBMS_OUTPUT.PUT_LINE('STEP11');
            END IF;
         END IF;
         COMMIT;
      EXCEPTION WHEN OTHERS THEN
                dbms_output.put_line('In Exception'||sqlerrm);
                fnd_msg_pub.count_and_get (p_count        => x_msg_count,
                                           p_data         => x_msg_data,
                                           p_encoded      =>
                                          fnd_api.g_false
                                          );
                                          DBMS_OUTPUT.PUT_LINE('STEP12');
                 ROLLBACK;



end PKMTL_TRANSACTION;

Note :In the above procedure change the l_segment values accordingly.

Step2 : Run the Below anonymous block and pass the parameters accordingly for "Miscellaneous issue"

declare
X_RETURN_STATUS     varchar2(1000);
X_MSG_COUNT         number;
X_MSG_DATA          VARCHAR2(1000);

BEGIN
  
    BEGIN
      PKMTL_TRANSACTION(287955, 207, 'Stores',NULL, NULL, 'Ea', -1, 'Miscellaneous issue',
                NULL, null, X_RETURN_STATUS, X_MSG_COUNT, X_MSG_DATA);
                
dbms_output.put_line('X_RETURN_STATUS->'||X_RETURN_STATUS||'    X_MSG_COUNT->'||X_MSG_COUNT||'    X_MSG_DATA->'||X_MSG_DATA);

    END;

Run the Below anonymous block and pass the parameters accordingly for "Miscellaneous receipt"

declare
X_RETURN_STATUS     varchar2(1000);
X_MSG_COUNT         number;
X_MSG_DATA          VARCHAR2(1000);

BEGIN
  
    BEGIN
      PKMTL_TRANSACTION(287955, 207, 'Stores',NULL, NULL, 'Ea', 1, 'Miscellaneous receipt',
                NULL, null, X_RETURN_STATUS, X_MSG_COUNT, X_MSG_DATA);
                
dbms_output.put_line('X_RETURN_STATUS->'||X_RETURN_STATUS||'    X_MSG_COUNT->'||X_MSG_COUNT||'    X_MSG_DATA->'||X_MSG_DATA);

    END;

CREATE AN INVENTORY ITEM IN ORACLE APPS FROM BACKEND(ITEM IMPORT)

CREATE AN INVENTORY ITEM IN ORACLE APPS FROM BACKEND(ITEM IMPORT) INSERT SCRIPT


STEP1: Get the template id using the below query 

SELECT template_id
      ,template_name
      ,description
FROM   MTL_ITEM_TEMPLATES; 

STEP2: Check the choosed template's related item attribute values using the below query

SELECT template_id
      ,attribute_name
      ,enabled_flag
      ,report_user_value
FROM   MTL_ITEM_TEMPL_ATTRIBUTES
WHERE  template_id = 107; --finished_goods


STEP3:  Run the below insert script to create a record in the standard item interface table to create a item based on item template 

INSERT
INTO
  MTL_SYSTEM_ITEMS_INTERFACE
  (
    process_flag,
    set_process_id,
    transaction_type,
    organization_id,
    segment1,
    description,
    TEMPLATE_ID
  )
  VALUES
  (
    1,
    1,
    'CREATE',
    204,
    'PK123456',
    'Sample PK Item Testing',
    107
  );

STEP4: Run the wrapper script given in the below link to submit the "ITEM IMPORT" concurrent program from backend

DECLARE
v_sub_status BOOLEAN;

FUNCTION submit_item_import
RETURN BOOLEAN
IS
 v_organization_id   NUMBER := 0;
 v_request_id        NUMBER := 0;
 v_phase             VARCHAR2(240);
 v_status            VARCHAR2(240);
 v_request_phase     VARCHAR2(240);
 v_request_status    VARCHAR2(240);
 v_finished          BOOLEAN;
 v_message           VARCHAR2(240);
 v_sub_status        BOOLEAN := FALSE;

BEGIN
  FND_GLOBAL.APPS_INITIALIZE(1318,50583,401);
  MO_GLOBAL.SET_POLICY_CONTEXT('S','204');
  FND_GLOBAL.SET_NLS_CONTEXT('AMERICAN');  --This is for Language specific view
  MO_GLOBAL.INIT('INV');

  v_request_id := Fnd_Request.submit_request
                       (
                         application => 'INV',
                         program     => 'INCOIN',
                         description => NULL,
                         start_time  => SYSDATE,
                         sub_request => FALSE,
                         argument1 => 204,  -- Organization id
                         argument2 => 1,    -- All organizations
                         argument3 => 1,    -- Validate Items
                         argument4 => 1,    -- Process Items
                         argument5 => 1,    -- Delete Processed Rows
                         argument6 => NULL, -- Process Set (Null for All)
                         argument7 => 1,    -- Create or Update Items
                         argument8 => 1     -- Gather Statistics
                       );
  COMMIT; 

  IF ( v_request_id = 0 ) THEN
    dbms_output.put_line( 'Item Import Program Not Submitted');
        v_sub_status := FALSE;
  ELSE
    v_finished := fnd_concurrent.wait_for_request
                            (
                             request_id => v_request_id,
                             interval   => 0,
                             max_wait   => 0,
                             phase      => v_phase,
                             status     => v_status,
                             dev_phase  => v_request_phase,
                             dev_status => v_request_status,
                             message    => v_message
                             );

    DBMS_OUTPUT.PUT_LINE('Request Phase  : '|| v_request_phase );
    DBMS_OUTPUT.PUT_LINE('Request Status : '|| v_request_status );
    DBMS_OUTPUT.PUT_LINE('Request id     : '|| v_request_id );

  --Testing end statusv_request_id
    IF ( UPPER(v_request_status) = 'NORMAL') THEN    
          v_sub_status := TRUE;
    END IF;
  END IF;
  RETURN (v_sub_status);
EXCEPTION
  WHEN OTHERS THEN
   DBMS_OUTPUT.PUT_LINE('Error in Submitting Item Import Program and error is '||SUBSTR(SQLERRM,1,200));
   RETURN (FALSE); 
END submit_item_import;

BEGIN
  v_sub_status  := submit_item_import();
  IF v_sub_status THEN
    DBMS_OUTPUT.PUT_LINE( 'Item Import Status: Sucessful');      
  ELSE
    DBMS_OUTPUT.PUT_LINE( 'Item Import Status: Failed');
  END IF;

END;   
/
commit;


STEP5: Run the below query to verify the creation of the item 

SELECT *
FROM   mtl_system_items_b

WHERE  segment1 = 'PK123456';

procure to pay(p2p) cycle with complete flow from backend using query and scripts

****************************************************************************
Summary of the below p2p from using.

1. To Insert Data into the staging Table.
2. after insert submit the import program"Import Standard Purchase Orders" to generate the po#.
3. after generating the po# need to approve.
4. after approving load the data in to rcv interface tables.
5. after loading into the rcv interface tables run the "Receiving Transaction Processor" program to generate receipt.
6. To generate the invoice run the concurrent program "Pay On Receipt AutoInvoice".
***************************************************************************

--1. To Insert Data into the staging Table

CREATE OR REPLACE PROCEDURE XX_TABLE_INSERT
AS
  lErrbuf  VARCHAR2(10);
  lRetcode NUMBER;
BEGIN
  INSERT
  INTO xx_po_header_stg
    (
      INTERFACE_HEADER_ID ,
      BATCH_ID ,
      ACTION ,
      ORG_ID ,
      DOCUMENT_TYPE_CODE ,
      CURRENCY_CODE ,
      AGENT_NAME ,
      VENDOR_NAME ,
      VENDOR_SITE_CODE ,
      SHIP_TO_LOCATION ,
      BILL_TO_LOCATION ,
      APPROVAL_STATUS ,
      FREIGHT_CARRIER ,
      FOB ,
      FREIGHT_TERMS
    )
    VALUES
    (
      po_headers_interface_s.NEXTVAL ,
      13 ,
      'ORIGINAL',
      204,
      'STANDARD',
      'USD',
      'Stock, Ms. Pat',
      '3M Health Care',
      'CORP HQ',
      'M1- Seattle Mfg',
      'V1- New York City',
      'APPROVED' ,
      'UPS',
      'Origin',
      'Due'
    );
  FND_FILE.PUT_LINE(FND_FILE.LOG,'DATA LOADED IN TO THE xx_po_header_stg STAGING TABLE');
  COMMIT;
  INSERT
  INTO xx_po_line_stg
    (
      interface_header_id ,
      interface_line_id ,
      LINE_NUM ,
      SHIPMENT_NUM ,
      LINE_TYPE ,
      ITEM ,
      ITEM_DESCRIPTION ,
      item_id ,
      UOM_CODE ,
      QUANTITY ,
      UNIT_PRICE ,
      SHIP_TO_ORGANIZATION_CODE ,
      SHIP_TO_LOCATION ,
      list_price_per_unit
    )
    VALUES
    (
      po_headers_interface_s.currval,
      po_lines_interface_s.nextval,
      1,
      2,
      'Goods',
      'AS54999',
      'Sentinel Standard Desktop - Rugged',
      2155,
      'Ea',
      10,
      120,
      'V1',
      'V1- New York City',
      45
    );
  FND_FILE.PUT_LINE(FND_FILE.LOG,'DATA LOADED IN TO THE xx_po_line_stg STAGING TABLE');
  COMMIT;
  --call the package for inserting the data from staging table to interface table
  XX_PO_INS_PKG.XX_PO_INS_PRC(lErrbuf,lRetcode);
  FND_FILE.PUT_LINE(FND_FILE.LOG,'package => XX_PO_INS_PKG.XX_PO_INS_PRC compiled'||'lErrbuf'||lErrbuf||'lRetcode'||lRetcode );
  UPDATE po_headers_interface SET AGENT_ID=27 WHERE creation_date LIKE sysdate;
  COMMIT;
END XX_TABLE_INSERT;
/

--2. after insert submit the import program to generate the po#.

CREATE OR REPLACE PROCEDURE submit_po_program
 AS
    l_responsibility_id NUMBER;
    l_application_id    NUMBER;
    l_user_id           NUMBER;
    l_request_id        NUMBER;
  BEGIN

      SELECT user_id INTO l_user_id
      FROM fnd_user
      WHERE user_name = 'PRUTHVI';
--To set environment context.
 apps.fnd_global.apps_initialize (l_user_id,50578,201);
 -- --Submitting Concurrent Request
  l_request_id := fnd_request.submit_request ( application => 'PO',
                                              program => 'POXPOPDOI',
                                              description => 'Import Standard Purchase Orders',
                                              start_time => sysdate,
                                              sub_request => FALSE,
                                              argument1 => NULL ,
                                              argument2 => 'Yes',
                                              argument3 => 'APPROVED',
                                              argument4 => NULL);
                                               COMMIT;
 IF l_request_id = 0
 THEN
 FND_FILE.PUT_LINE(FND_FILE.LOG,'Concurrent request failed to submit =>Import Standard Purchase Orders');
 ELSE
 FND_FILE.PUT_LINE(FND_FILE.LOG,'Successfully Submitted the Concurrent Request =>Import Standard Purchase Orders');
 END IF;
 EXCEPTION
 WHEN OTHERS THEN
 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error While Submitting Concurrent Request '||TO_CHAR(SQLCODE)||'-'||sqlerrm);
END;
/

--3. after generating the po# need to approve 

CREATE OR REPLACE procedure APPROVE_PO
AS
v_item_key VARCHAR2(100);


Cursor c_po_details is

SELECT
pha.po_header_id,
pha.org_id,
pha.segment1,
pha.agent_id,
pdt.document_subtype,
pdt.document_type_code,
pha.authorization_status
FROM apps.po_headers_all pha, apps.po_document_types_all pdt
WHERE pha.type_lookup_code = pdt.document_subtype
AND pha.org_id = pdt.org_id
AND pdt.document_type_code = 'PO'
AND authorization_status in ('INCOMPLETE', 'REQUIRES REAPPROVAL')
AND segment1 = '6259';
BEGIN
fnd_global.apps_initialize (user_id => 1013455,resp_id => 50578,resp_appl_id => 201);

FOR p_rec IN c_po_details

LOOP
mo_global.init (p_rec.document_type_code);
mo_global.set_policy_context ('S', p_rec.org_id);

SELECT p_rec.po_header_id|| '-'|| to_char(po_wf_itemkey_s.NEXTVAL)
INTO v_item_key FROM dual;
dbms_output.put_line (' Calling po_reqapproval_init1.start_wf_process for po_id=>' ||p_rec.segment1);

po_reqapproval_init1.start_wf_process(
ItemType => 'POAPPRV'
, ItemKey => v_item_key
, WorkflowProcess => 'POAPPRV_TOP'
, ActionOriginatedFrom => 'PO_FORM'
, DocumentID => p_rec.po_header_id -- po_header_id
, DocumentNumber => p_rec.segment1 -- Purchase Order Number
, PreparerID => p_rec.agent_id -- Buyer/Preparer_id
, DocumentTypeCode => p_rec.document_type_code--'PO'
, DocumentSubtype => p_rec.document_subtype --'STANDARD'
, SubmitterAction => 'APPROVE'
, forwardToID => NULL
, forwardFromID => NULL
, DefaultApprovalPathID => NULL
, Note => NULL
, PrintFlag => 'N'
, FaxFlag => 'N'
, FaxNumber => NULL
, EmailFlag => 'N'
, EmailAddress => NULL
, CreateSourcingRule => 'N'
, ReleaseGenMethod => 'N'
, UpdateSourcingRule => 'N'
, MassUpdateReleases => 'N'
, RetroactivePriceChange => 'N'
, OrgAssignChange => 'N'
, CommunicatePriceChange => 'N'
, p_Background_Flag => 'N'
, p_Initiator => NULL
, p_xml_flag => NULL
, FpdsngFlag => 'N'
, p_source_type_code => NULL);

commit;
DBMS_OUTPUT.PUT_LINE ('The PO which is Approved Now =>' || p_rec.segment1);
END LOOP;
END;
/

--4. after approving load the data in to rcv interface tables

CREATE OR REPLACE PROCEDURE RCV_PO_NUMBER
AS
X_USER_ID NUMBER;
X_PO_HEADER_ID NUMBER;
X_VENDOR_ID NUMBER;
X_SEGMENT1 VARCHAR2(20);
X_ORG_ID NUMBER;
X_LINE_NUM NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE('***ezROI RCV API Insert Script***');
SELECT PO_HEADER_ID , VENDOR_ID , SEGMENT1 , ORG_ID
INTO X_PO_HEADER_ID , X_VENDOR_ID , X_SEGMENT1 , X_ORG_ID
FROM PO_HEADERS_ALL
WHERE SEGMENT1 = 6259;--'&PO_NUMBER';
--AND ORG_ID = &ORG_ID;
SELECT USER_ID INTO X_USER_ID
FROM FND_USER
WHERE USER_NAME = UPPER('PRUTHVI');

INSERT INTO RCV_HEADERS_INTERFACE
(
HEADER_INTERFACE_ID ,
GROUP_ID ,
PROCESSING_STATUS_CODE ,
RECEIPT_SOURCE_CODE ,
TRANSACTION_TYPE ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
VENDOR_ID ,
EXPECTED_RECEIPT_DATE ,
VALIDATION_FLAG ,
CREATION_DATE
)
SELECT
RCV_HEADERS_INTERFACE_S.NEXTVAL ,
RCV_INTERFACE_GROUPS_S.NEXTVAL ,
'PENDING' ,
'VENDOR' ,
'NEW' ,
SYSDATE ,
X_USER_ID ,
0 ,
X_VENDOR_ID ,
SYSDATE ,
'Y' ,
SYSDATE
FROM DUAL;

DECLARE
CURSOR PO_LINE IS
SELECT PL.ITEM_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
FROM PO_LINES_ALL PL ,
PO_LINE_LOCATIONS_ALL PLL ,
MTL_PARAMETERS MP
WHERE PL.PO_HEADER_ID = X_PO_HEADER_ID
AND PL.PO_LINE_ID = PLL.PO_LINE_ID
AND PLL.SHIP_TO_ORGANIZATION_ID = MP.ORGANIZATION_ID;
BEGIN
FOR CURSOR1 IN PO_LINE LOOP
IF CURSOR1.CLOSED_CODE IN ('APPROVED','OPEN')
AND CURSOR1.QUANTITY_RECEIVED < CURSOR1.QUANTITY
AND NVL(CURSOR1.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 ,
SOURCE_DOCUMENT_CODE ,
HEADER_INTERFACE_ID ,
VALIDATION_FLAG
)
SELECT
RCV_TRANSACTIONS_INTERFACE_S.NEXTVAL ,
RCV_INTERFACE_GROUPS_S.CURRVAL ,
SYSDATE ,
X_USER_ID ,
SYSDATE ,
X_USER_ID ,
0 ,
'RECEIVE' ,
SYSDATE ,
'PENDING' ,
'BATCH' ,
'PENDING' ,
X_PO_HEADER_ID,
CURSOR1.PO_LINE_ID ,
CURSOR1.ITEM_ID ,
CURSOR1.QUANTITY ,
CURSOR1.UNIT_MEAS_LOOKUP_CODE ,
CURSOR1.LINE_LOCATION_ID ,
'RECEIVE' ,
'VENDOR' ,
CURSOR1.ORGANIZATION_CODE ,
'PO' ,
RCV_HEADERS_INTERFACE_S.CURRVAL ,
'Y'
FROM DUAL;
DBMS_OUTPUT.PUT_LINE('PO line: '||CURSOR1.LINE_NUM||' Shipment: '||CURSOR1.SHIPMENT_NUM||' has been inserted into ROI.');
ELSE
DBMS_OUTPUT.PUT_LINE('PO line '||CURSOR1.LINE_NUM||' is either closed, cancelled, received.');
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('***COMPLETE - End ***');
END;
COMMIT;
END;
/

--5. after loading into the rcv interface tables run the "Receiving Transaction Processor" program to generate receipt.

CREATE OR REPLACE PROCEDURE submit_rcv_trans_program
 AS
    l_responsibility_id NUMBER;
    l_application_id    NUMBER;
    l_user_id           NUMBER;
    l_request_id        NUMBER;
  BEGIN

      SELECT user_id INTO l_user_id
      FROM fnd_user
      WHERE user_name = 'PRUTHVI';
--To set environment context.
 apps.fnd_global.apps_initialize (l_user_id,50578,201);
 -- --Submitting Concurrent Request
  l_request_id := fnd_request.submit_request ( application => 'PO',
                                              program => 'RVCTP',
                                              description => 'Receiving Transaction Processor',
                                              start_time => sysdate,
                                              sub_request => FALSE,
                                              argument1 => 'BATCH' ,
                                              argument2 => NULL,
                                              argument3 => NULL);
                                               COMMIT;
 IF l_request_id = 0
 THEN
 FND_FILE.PUT_LINE(FND_FILE.LOG,'Concurrent request failed to submit =>Receiving Transaction Processor');
 ELSE
 FND_FILE.PUT_LINE(FND_FILE.LOG,'Successfully Submitted the Concurrent Request =>Receiving Transaction Processor'||'REQUEST_ID'||l_request_id);
 END IF;
 EXCEPTION
 WHEN OTHERS THEN
 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error While Submitting Concurrent Request '||TO_CHAR(SQLCODE)||'-'||sqlerrm);
 END;
 /

****************************************************************************
CODE FOR THE PACKAGE XX_PO_INS_PKG

create or replace PACKAGE XX_PO_INS_PKG IS
PROCEDURE XX_PO_INS_PRC(Errbuf OUT VARCHAR2, Retcode OUT number) ;
END;

==================================================================
create or replace PACKAGE BODY XX_PO_INS_PKG
IS
PROCEDURE XX_PO_INS_PRC (errbuf OUT VARCHAR2, retcode OUT number)
IS
CURSOR cur_head
IS
SELECT *
FROM xx_po_header_stg;
CURSOR cur_line (p_interface_header_id NUMBER)
IS
SELECT *
FROM xx_po_line_stg where interface_header_id=p_interface_header_id;
CURSOR cur_dist (p_interface_line_id NUMBER)
IS
SELECT *
FROM xx_po_distribution_stg where interface_line_id=p_interface_line_id;
lv_vendor_id NUMBER (10);
lv_agent_id NUMBER(10);
lv_itemid NUMBER;
lv_site_code VARCHAR2 (100);
lv_lookup_code VARCHAR2(25);
lv_curr_code VARCHAR2 (10);
lv_org_id NUMBER (6);
BEGIN
BEGIN
SELECT organization_id
INTO lv_org_id
FROM hr_operating_units
WHERE NAME LIKE 'Vision Operations';
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG, 'invalid org_id');
END;
BEGIN
FOR var1 IN cur_head
LOOP
BEGIN
SELECT vendor_id
INTO lv_vendor_id
FROM po_vendors
WHERE vendor_name=var1.vendor_name;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG, 'invalid vendor_id');
END;
BEGIN
SELECT vendor_site_code
INTO lv_site_code
FROM po_vendor_sites_all
WHERE vendor_site_code = var1.vendor_site_code;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'invalid vendor_site_code');
END;
BEGIN
SELECT currency_code
INTO lv_curr_code
FROM fnd_currencies
WHERE currency_code = var1.currency_code;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG, 'invalid currency_code');
END;
BEGIN
SELECT DOCUMENT_TYPE_CODE
INTO lv_lookup_code
FROM PO_DOCUMENT_TYPES
WHERE DOCUMENT_TYPE_CODE = var1.DOCUMENT_TYPE_CODE;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG, 'invalid typecode');
END;
BEGIN
SELECT person_id
INTO lv_agent_id
FROM PER_ALL_PEOPLE_F
WHERE full_name= var1.full_name;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG, 'invalid Buyer');
END;
BEGIN
INSERT INTO po_headers_interface
(interface_header_id
, batch_id
, org_id
,action
, document_type_code
, currency_code
,agent_id
, agent_name
, vendor_name
, vendor_site_code
, ship_to_location
, bill_to_location
,approval_status
, freight_terms
,fob
,freight_carrier
,created_by
, creation_date
, last_update_date
, last_updated_by
)
VALUES
(PO_HEADERS_INTERFACE_S.NEXTVAL
, var1.batch_id
, lv_org_id
,var1.action
, var1.document_type_code
, var1.currency_code
, lv_agent_id
,var1.full_name
,var1.vendor_name
, var1.vendor_site_code
, var1.ship_to_location
, var1.bill_to_location
, var1.approval_status
, var1.freight_terms
,var1.fob
,var1.freight_carrier
, -1
, SYSDATE
, SYSDATE
, -1
);
EXCEPTION WHEN OTHERS THEN
FND_FILE.put_line(FND_FILE.output,'insertion successful into headers int');
END;
FOR var2 IN cur_line (var1.interface_header_id)
LOOP
BEGIN
SELECT INVENTORY_ITEM_ID
INTO lv_itemid
FROM mtl_system_items_b
WHERE segment1 = var2.item
AND organization_id =lv_org_id;
EXCEPTION
WHEN OTHERS
THEN
-- lv_item := NULL;
fnd_file.put_line (fnd_file.LOG, 'invalid item');
END;
BEGIN
INSERT INTO po_lines_interface
(interface_line_id
, interface_header_id
, line_num
, shipment_num
, line_type
,item
, item_description
, item_id
, uom_code
, quantity
, unit_price
,ship_to_organization_code
, ship_to_location
,NEED_BY_DATE
-- ,PROMISED_DATE
, list_price_per_unit
, created_by
, creation_date
, last_update_date
, last_updated_by
)
VALUES
(PO_LINES_INTERFACE_S.NEXTVAL
,PO_HEADERS_INTERFACE_S.CURRVAL
, var2.line_num
, var2.shipment_num
, var2.line_type
, var2.item
, var2.item_description
, var2.item_id
,var2.uom_code
, var2.quantity
, var2.unit_price
,var2.ship_to_organization_code
,var2.ship_to_location
,SYSDATE
-- ,SYSDATE
, var2.list_price_per_unit
, -1
,SYSDATE
, SYSDATE
, -1
);
EXCEPTION WHEN OTHERS THEN
FND_FILE.put_line(FND_FILE.output,'insertion successful into lines int');
END;
for var3 in cur_dist(var2.interface_line_id)
LOOP
BEGIN
INSERT INTO po_distributions_interface (
interface_header_id ,
interface_line_id ,
interface_distribution_id ,
org_id ,
quantity_ordered ,
destination_organization_id ,
set_of_books_id ,
charge_account_id ,
distribution_num
, created_by
, creation_date
, last_update_date
, last_updated_by
)
VALUES
(
PO_HEADERS_INTERFACE_S.CURRVAL,
PO_LINES_INTERFACE_S.CURRVAL,
PO_DISTRIBUTIONS_INTERFACE_S.NEXTVAL ,
lv_org_id ,
var3.quantity_ordered ,
var3.destination_organization_id ,
var3.set_of_books_id ,
var3.charge_account_id ,
var3.distribution_num
, -1
,SYSDATE
, SYSDATE
, -1 );
EXCEPTION WHEN OTHERS THEN
FND_FILE.put_line(FND_FILE.output,'insertion successful into dist int');
END;
END LOOP;
END LOOP;
END LOOP;
END;
COMMIT;
END XX_PO_INS_PRC;

END XX_PO_INS_PKG ;

PURCHASE ORDER CREATE IN ORACLE APPS FROM BACKEND

PLSQL PROGRAM TO CREATE PURCHASE ORDER IN ORACLE APPS

/*
 =======================================================================================================
 Name : Create Standard Purchase Order

 Purpose : This plsql program is used to insert data into PO interface table to create Standard
           Purchase Order through PDOI having backing requisition
  : Import Standard Purchase Order Concurrent Program .

  Some mandatory columns have been defined users can have their own columns added
  as per their requirement

 =======================================================================================================
 */
DECLARE

------------------------------------------------------------------------------------
--Define Mandatory column variables to insert into interface tables. Values to all
-- Variables need to be intialized with required data before running the script.
------------------------------------------------------------------------------------

---------------------------------------------------------------------------------
--Header level information
---------------------------------------------------------------------------------
--This provides batch id which is used to run particular set of data
l_batch_id   po_headers_interface.batch_id%TYPE := 121226;

--Contains Organization ID
l_org_id   po_headers_interface.org_id%TYPE := 204;

--Contains Agent ID for which PO is created
l_agent_id   po_headers_interface.agent_id%TYPE := 27;

--Contains Vendor ID
l_vendor_id   po_headers_interface.vendor_id%TYPE := 21;

--Contains Vendor Site ID
l_vendor_site_id  po_headers_interface.vendor_site_id%TYPE := 41;

--Contains Ship to Location ID
l_ship_to_location_id    po_headers_interface.ship_to_location_id%TYPE := 204;

--Contains Bill to Location ID
l_bill_to_location_id    po_headers_interface.bill_to_location_id%TYPE := 204;

-- Contains Attribute value which can be your username
-- which can be used to check records inserted by the user
l_attribute1             po_headers_interface.attribute1%TYPE := 'TESTDATA';



---------------------------------------------------------------------------------
--Line level information
---------------------------------------------------------------------------------

-- Provide the list of requisition line ids
--l_req_line_id_tbl PO_TBL_NUMBER := PO_TBL_NUMBER(190013, 190014);

l_progress VARCHAR2(100);

BEGIN

 ---- Inserting into header interface table
   l_progress := '001';
   Insert into PO.PO_HEADERS_INTERFACE
    (INTERFACE_HEADER_ID,
     BATCH_ID,
     PROCESS_CODE,
     ACTION,
     ORG_ID,
     DOCUMENT_TYPE_CODE,
     CURRENCY_CODE,
     AGENT_ID,
     VENDOR_ID,
     VENDOR_SITE_ID,
     SHIP_TO_LOCATION_ID,
     BILL_TO_LOCATION_ID,
     ATTRIBUTE1,  
     CREATION_DATE)
  VALUES
  ( po_headers_interface_s.NEXTVAL,--- INTERFACE_HEADER_ID,
     l_batch_id,
     'PENDING', --- PROCESS_CODE,
     'ORIGINAL', --- ACTION,
     l_org_id, --- ORG_ID,
     'STANDARD', --- DOCUMENT_TYPE_CODE,
     'USD',  --- CURRENCY_CODE,
     l_agent_id,      
     l_vendor_id,
     l_vendor_Site_id,
     l_ship_to_location_id,
     l_bill_to_location_id,
     l_attribute1,
     SYSDATE ); --- CREATION_DATE,        

   l_progress := '002';

   --FOR i  IN 1..l_req_line_id_tbl.COUNT LOOP
 
  Insert into PO.PO_LINES_INTERFACE
     (INTERFACE_LINE_ID,
      INTERFACE_HEADER_ID,
      ACTION,
      REQUISITION_LINE_ID,
      LINE_LOC_POPULATED_FLAG,
      UOM_CODE,
      QUANTITY,
      ITEM_DESCRIPTION,
      ITEM_ID,
      UNIT_PRICE)
   Values
     (po_lines_interface_s.nextval, --- INTERFACE_LINE_ID,
      po_headers_interface_s.currval,--- INTERFACE_HEADER_ID,
     'ADD',--- ACTION,
       190013,--l_req_line_id_tbl(i),
      'N',
      'Ea',
      1,
      'Standard Desktop',
      205952,
      100);--- LINE_LOC_POPULATED_FLAG,  

Insert into PO.PO_LINES_INTERFACE
     (INTERFACE_LINE_ID,
      INTERFACE_HEADER_ID,
      ACTION,
      REQUISITION_LINE_ID,
      LINE_LOC_POPULATED_FLAG,
      UOM_CODE,
      QUANTITY,
      ITEM_DESCRIPTION,
      ITEM_ID,
      UNIT_PRICE )
   Values
     (po_lines_interface_s.nextval, --- INTERFACE_LINE_ID,
      po_headers_interface_s.currval,--- INTERFACE_HEADER_ID,
     'ADD',--- ACTION,
       190014,--l_req_line_id_tbl(i),
      'N',
      'Ea',
      2,
      'Standard Desktop',
      205952,
      100);

  --END LOOP;
COMMIT;

EXCEPTION

WHEN OTHERS THEN

dbms_output.put_line('Error while inserting data at :'||l_progress||'---'||SQLCODE||SQLERRM);

END;


********************************************************************************************

Once the scripts to be used to populate the interface tables are ready, the following process needs to be followed.

1. Execute the relevant script to insert data into interface tables .

2. The script provides detailed description of the required information to be given before executing.
(Ex : Header Level Information - Vendor, Vendor site id
 Line level information - Item Id, quantity, price etc)

3. Verify the data in the interface tables as:
Select * from po_headers_interface where attribute1 = &attribute1; (Attribute1 is specified through the script)

4. Navigate to Oracle Purchasing Responsibility in the application.

5. Run the concurrent request "IMPORT STANDARD PURCHASE ORDER" to create Standard Purchase Order
Run the Concurrent request "IMPORT PRICE CATALOG" to create Blanket Purchase Agreement/Quotation.

6. You may specify the Batch ID in the parameters for the concurrent program.(Batch Id is populated through the script) and submit the program.

7. After the concurrent program is completed, check the value of process_code in po_headers_interface.

If process_code is ACCEPTED, po_header_id is stamped on to the interface table and PO is created successfully. The PO created can be verified from the below query.

Select * from po_headers_all where po_header_id = (select po_header_id from po_headers_interface where attribute1 = &attribute1);

8. The process code in interface tables will be REJECTED if an error has occurred while processing the data. The errors can be checked from below table:

Select * from po_interface_errors where interface_header_id = &interface_header_id ( Interface header Id inserted into interface tables from Step 1).


9. The scripts needs to be modified or corrected as per the errors returned from above step.