R12 API to apply Credit Memo on Invoice

CREATE OR REPLACE FUNCTION APPS.XX_AR_CA_CMPTL_SUB_F(p_credit_memo VARCHAR2,p_invoice_num VARCHAR2,p_amt NUMBER) RETURN NUMBER
IS
ln_request_id NUMBER;
lc_inv NUMBER;
lb_complete      BOOLEAN;
lc_phase           VARCHAR2 (100);
lc_status           VARCHAR2 (100);
lc_dev_phase   VARCHAR2 (100);
lc_dev_status   VARCHAR2 (100);
lc_message      VARCHAR2 (100);
  v_cm_payment_schedule       number          ; -- Payment Schedule ID of Credit Memo from APPS.AR_PAYMENT_SCHEDULES_ALL
  v_inv_payment_schedule      number          ; -- Payment Schedule ID of Invoice from APPS.AR_PAYMENT_SCHEDULES_ALL
  v_amount_applied            number          ;   -- Amount of credit memo to apply to invoice
  v_apply_date                date            := SYSDATE;
  v_gl_date                   date            := SYSDATE-1;
  v_ussgl_transaction_code    varchar2(1024);   -- null, but check AR_RECEIVABLE_APPLICATIONS_ALL
  v_null_flex                 varchar2(1024);   -- null, unless you have flexfield segments to define
  v_customer_trx_line_id      number;           -- null, but check AR_RECEIVABLE_APPLICATIONS_ALL
  v_comments                  varchar2(240)   :=  'Applied automatically';
  v_module_name               varchar2(128)  ;-- := 'XXCREDIT_APPLICATION.APPLY_CREDIT_MEMO'; -- If null, validation won't occur
  v_module_version            varchar2(128)   :=  '1'; -- If null, validation won't occur
  lc_msg_txt                  varchar2(1000);
  --
  -- Output
  --
  v_out_rec_application_id    number;
  v_acctd_amount_applied_from number;
  v_acctd_amount_applied_to   number;
  ln_org_id                   NUMBER := fnd_profile.value('ORG_ID');
  lc_trx                     number;


BEGIN
     
           BEGIN
         
                select payment_schedule_id
                into v_cm_payment_schedule
                from ar_payment_schedules_all ps,
                     ra_customer_trx_all ract
                where ps.customer_trx_id = ract.customer_trx_id
                AND ract.trx_number = p_credit_memo
                AND ract.org_id = ln_org_id;           
               
                select payment_schedule_id
                into v_inv_payment_schedule
                from ar_payment_schedules_all ps,
                     ra_customer_trx_all ract
                where ps.customer_trx_id = ract.customer_trx_id
                AND ract.trx_number = p_invoice_num
                AND ract.org_id = ln_org_id;
               
           EXCEPTION
           WHEN OTHERS THEN
          
                v_cm_payment_schedule := 0;
                v_inv_payment_schedule := 0;
               
           END;
          
           v_amount_applied := p_amt;
          
          fnd_client_info.set_org_context(8908);
       
          arp_process_application.cm_application(
            p_cm_ps_id                  => v_cm_payment_schedule,
            p_invoice_ps_id             => v_inv_payment_schedule,
            p_amount_applied            => v_amount_applied,
            p_apply_date                => v_apply_date,
            p_gl_date                   => v_gl_date,
            p_ussgl_transaction_code    => v_ussgl_transaction_code, -- NULL
            p_attribute_category        => v_null_flex, -- NULL
            p_attribute1                => v_null_flex, -- NULL
            p_attribute2                => v_null_flex, -- NULL
            p_attribute3                => v_null_flex, -- NULL
            p_attribute4                => v_null_flex, -- NULL
            p_attribute5                => v_null_flex, -- NULL
            p_attribute6                => v_null_flex, -- NULL
            p_attribute7                => v_null_flex, -- NULL
            p_attribute8                => v_null_flex, -- NULL
            p_attribute9                => v_null_flex, -- NULL
            p_attribute10               => v_null_flex, -- NULL
            p_attribute11               => v_null_flex, -- NULL
            p_attribute12               => v_null_flex, -- NULL
            p_attribute13               => v_null_flex, -- NULL
            p_attribute14               => v_null_flex, -- NULL
            p_attribute15               => v_null_flex, -- NULL
            p_global_attribute_category => v_null_flex, -- NULL
            p_global_attribute1         => v_null_flex, -- NULL
            p_global_attribute2         => v_null_flex, -- NULL
            p_global_attribute3         => v_null_flex, -- NULL
            p_global_attribute4         => v_null_flex, -- NULL
            p_global_attribute5         => v_null_flex, -- NULL
            p_global_attribute6         => v_null_flex, -- NULL
            p_global_attribute7         => v_null_flex, -- NULL
            p_global_attribute8         => v_null_flex, -- NULL
            p_global_attribute9         => v_null_flex, -- NULL
            p_global_attribute10        => v_null_flex, -- NULL
            p_global_attribute11        => v_null_flex, -- NULL
            p_global_attribute12        => v_null_flex, -- NULL
            p_global_attribute13        => v_null_flex, -- NULL
            p_global_attribute14        => v_null_flex, -- NULL
            p_global_attribute15        => v_null_flex, -- NULL
            p_global_attribute16        => v_null_flex, -- NULL
            p_global_attribute17        => v_null_flex, -- NULL
            p_global_attribute18        => v_null_flex, -- NULL
            p_global_attribute19        => v_null_flex, -- NULL
            p_global_attribute20        => v_null_flex, -- NULL
            p_customer_trx_line_id      => v_customer_trx_line_id, -- NULL
            p_comments                  => v_comments, -- NULL
            p_module_name               => v_module_name, -- NULL
            p_module_version            => v_module_version, -- NULL
            p_out_rec_application_id    => v_out_rec_application_id, --NULL
            p_acctd_amount_applied_from => v_acctd_amount_applied_from, -- NULL
            p_acctd_amount_applied_to   => v_acctd_amount_applied_to -- NULL
          );
         
          COMMIT;
         
RETURN ln_request_id;
                                
END;
/

R12 PO Amendment Query

SELECT APS.segment1 VENDOR,
APS.vendor_name NAME,
POH.segment1 PO,
POH.revision_num REV,
POH.attribute11 HEADER_EFFECTIVE_DATE,
POH.note_to_vendor NOTE_TO_VENDOR,
POH.note_to_receiver NOTE_TO_RECEIVER,
POH.org_id,
POH.creation_date PO_CREATION_DATE,
TRUNC(POH.approved_date) APPROVED_DATE,
POL.item_description DESCRIPTION,
POL.line_num,
POL.unit_price,
POL.attribute12 LINES_AMMENDMENT,
POL.attribute11 LINES_EFFECTIVE_DATE,
POL.creation_date LINE_CREATION_DATE,
MSIB.segment1 ITEMCODE,
MSIB.organization_id,
NVL(PL.quantity,0),
POH.type_lookup_code,
PAH.note
FROM APPS.PO_HEADERS_ARCHIVE_ALL POH,
    APPS.PO_LINES_ARCHIVE_ALL POL,
    APPS.MTL_SYSTEM_ITEMS_B MSIB,
    APPS.AP_SUPPLIERS APS,
    APPS.PO_LINES_ALL PL,
    APPS.PO_ACTION_HISTORY PAH
WHERE POH.po_header_id = POL.po_header_id
and poh.revision_num = pol.revision_num
and POL.item_id = MSIB.inventory_item_id
and POH.vendor_id = APS.vendor_id
and POH.po_header_id = PL.po_header_id
and pol.line_num = pl.line_num
and POH.po_header_id = PAH.object_id(+)
and POH.revision_num = PAH.object_revision_num(+)
and POh.type_lookup_code = PAH.object_sub_type_code(+)
and pah.action_code(+) = 'SUBMIT'
and poh.org_id=:p_org_id
and msib.organization_id=:p_orgn_id
and POH.approved_date between :p_from_date and :p_to_date
and POH.segment1 between :p_from_po and :p_to_po
ORDER BY
poh.segment1,
POH.revision_num,
pol.line_num

R12 query to check Credit Limit of a customer

SELECT c.customer_number,c.customer_name,g.party_site_number,d.location,a.overall_credit_limit,
d.site_use_id,sum(e.amount_due_remaining) credit_balance
FROM   HZ_CUST_PROFILE_AMTS a, HZ_CUST_ACCOUNTS b, ar_customers c,
              hz_cust_site_uses_all d,ar_payment_schedules_all e,
              hz_cust_acct_sites_all f,hz_party_sites g
WHERE       overall_credit_limit IS NOT NULL
         and a.cust_account_id = b.cust_account_id
         and b.account_number = c.customer_number
         and a.site_use_id = d.site_use_id
         and c.customer_id = e.customer_id
         and d.site_use_id = e.customer_site_use_id
--         and c.customer_number = :customer_number
         and d.cust_acct_site_id = f.cust_acct_site_id
         and g.party_site_id = f.party_site_id
         and e.org_id = :org_id
GROUP BY c.customer_name,
c.customer_number,
d.site_use_id,
d.location,
a.overall_credit_limit,
g.party_site_number
ORDER BY CUSTOMER_NAME

Collection Plan Interface in R12 Oracle Apps

DECLARE
v_request_id NUMBER;
BEGIN

INSERT INTO qa_results_interface (
                                        organization_code
                                        ,plan_name
                                        ,process_status
                                        ,insert_type
                                        ,collection_id
                                        ,last_update_date
                                        ,last_updated_by
                                        ,qa_last_updated_by
                                        ,transaction_date
                                        ,character1
                                        ,character2
                                        ,character3
                                        ,character4
                                        ,character5                          
                                        ,character6
                                        ,character7
                                        ,quantity
                                        ,ITEM_ID
                                        ,wip_entity_id
                                        ,item
                                        ,job_name                   
                                        )
VALUES (
          'XX' -- organization_code
          ,'XX_JOB COMPLETION PLAN' -- plan_name
          ,1 -- process_status
          ,1 -- insert_type = INSERT
          ,QA_COLLECTION_ID_S.NEXTVAL
          ,SYSDATE
          ,fnd_global.user_id
          ,fnd_global.user_id
          ,SYSDATE
          ,'1'
          ,'1'
          ,'1'
          ,'1'
          ,'1'
          ,'1'
          ,'1',
          1,
          1001,
          132189,
          'Test Item',
          '1234'
          );         
         
COMMIT;         
fnd_global.apps_initialize (user_id, resp_id, appl_id);
v_request_id :=
      fnd_request.submit_request('QA',
                                  'QLTTRAMB',              -- Program short name
                                  '',             -- description (Not require)
                                  '',        -- start_time (start immediately)
                                  FALSE,          --sub_request (default FALSE)
                                  '200',
                                  '1',
                                  '1133',
                                  'YES'
                                 );

COMMIT;
dbms_output.put_line(v_request_id);
END;

Oracle R12 Supplier Interface

A simple code for Supplier Interface

1) Create Supplier through Import
INSERT INTO ap_suppliers_int
(vendor_interface_id, vendor_name,
status)
VALUES (AP_SUPPLIERS_INT_S.NEXTVAL, 'ABC Industries',

'NEW')
;
Vendor_interface_id = 10006, vendor_id = 46220

2) Create Supplier Site using Import
INSERT INTO ap_supplier_sites_int
(vendor_id, vendor_site_code, address_line1, city,
state, zip, country,
org_id, operating_unit_name,
status,
purchasing_site_flag, pay_site_flag,
vendor_site_interface_id)
VALUES (46220, 'SITE1', 'ADDR1', 'Orlando',
'FL', '32803', 'US',
204, NULL,
'NEW',
'Y', 'Y',
AP_SUPPLIER_SITES_INT_S.NEXTVAL)
;

3) Create row in Contact Interface table using the
following:
INSERT INTO ap_sup_site_contact_int
(vendor_site_id, vendor_site_code,
org_id,  operating_unit_name,
first_name, last_name, prefix, title, mail_stop,
area_code,
phone, department, status, email_address, url,
alt_area_code,
alt_phone, fax_area_code, fax, vendor_id,
vendor_contact_interface_id,
vendor_interface_id, last_update_date,
last_updated_by,
creation_date, created_by, last_update_login)
VALUES (NULL, 'SITE1',
204, NULL,
'XYZ', 'YYYY', NULL, NULL, NULL, '787',
'000-0000', NULL, 'NEW', NULL, NULL, NULL,
NULL, NULL, NULL, 46220,
AP_SUP_SITE_CONTACT_INT_S.NextVal,
10006, SYSDATE, -99,
SYSDATE, -99, -99)

Interface Programs:
Supplier Open Interface Import
Supplier Sites Open Interface Import
Supplier Site Contacts Open Interface Import

Oracle R12 lot genealogy

Query to find lot genealogy

SELECT   TO_CHAR (mmt.transaction_date, 'DD-MON-YYYY') transaction_date,
         mln.lot_number, msi.segment1 item,msi.description,
         transaction_quantity,msi.primary_uom_code uom,mmt.new_cost cost
    FROM (
          SELECT     mog.object_id , ROWNUM slno, origin_txn_id
                FROM mtl_object_genealogy mog
               WHERE 1 = 1
          CONNECT BY PRIOR mog.object_id IN (mog.parent_object_id)
          START WITH mog.parent_object_id = 1103) hier,
         mtl_lot_numbers mln,
         mtl_material_transactions mmt,
         mtl_system_items_b msi,
         mtl_transaction_types mtt
   WHERE hier.object_id = mln.gen_object_id
     AND hier.origin_txn_id = mmt.transaction_id
     AND mmt.inventory_item_id = msi.inventory_item_id
     AND mmt.organization_id = msi.organization_id
     AND mmt.transaction_type_id = mtt.transaction_type_id
ORDER BY hier.slno

Automatic Receipt Creation during Inter Organization Transfer in R12

Automatic Receipt Creation during Inter Organization Transfer in R12

 In 11i, a receipt would be created automatically during Inter Organization Transfer.In case of R12, we have to create a receipt manually. The process described below helps to achieve automatic receipt creation automatically in R12 during Inter Organization Transfer.

This can be achieved through Receipt Interface.

During Inter Organization a record would be inserted in RCV_SHIPMENT_HEADERS and RCV_SHIPMENT_LINES be the system itself despite MTL_MATERIAL_TRANSACTIONS.So
we need to use Receipt Interface to create a record in RCV_TRANSACTIONS and a record would be created in MTL_MATERIAL_TRANSACTIONS indicating the item being taken into the receiving organization.


Step 1:

A trigger has to be written in RCV_SHIPMENT_LINES to trigger a custom procedure which would insert record into interface tables.

CREATE OR REPLACE TRIGGER APPS.XX_AUTO_RCPT_TRG
AFTER INSERT
ON PO.RCV_SHIPMENT_LINES
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
DECLARE
pragma AUTONOMOUS_TRANSACTION;
l_tran_type NUMBER;
l_return number;
BEGIN

fnd_global.apps_initialize (user_id, resp_id,appl_id );

l_return := Fnd_Request.submit_request(application=>'PO',
                                          program=>'XX_INTERORG_RCPT_PRCE',
                                          description=>'XX_INTERORG_RCPT_PRCE',
                                          start_time=>TO_CHAR(SYSDATE + 2 / 1440,'dd-mon-yy hh24:mi:ss'),
                                          sub_request=>FALSE,
                                          argument1=>:NEW.shipment_line_id,
                                          argument2=>:NEW.created_by,
                                          argument3=>:NEW.shipment_header_id,
                                          argument4=>:NEW.mmt_transaction_id,
                                          argument5=>:NEW.quantity_shipped,
                                          argument6=>:NEW.unit_of_measure,
                                          argument7=>:NEW.item_id,
                                          argument8=>:NEW.to_organization_id,
                                          argument9=>:NEW.from_organization_id,
                                          argument10=>:NEW.secondary_quantity_shipped,
                                          argument11=>:NEW.secondary_unit_of_measure,
                                          argument12=>:NEW.category_id,
                                          argument13=>:NEW.item_description,
                                          argument14=>:NEW.primary_unit_of_measure,
                                          argument15=>:NEW.locator_id,
                                          argument16=>:NEW.destination_context);


commit;                                            

END;

Step 2:

A custom procedure is used to insert records into interface table and call the standard concurrent program Receiving Transaction Processor and India - Receiving Transaction Processor if India Localization is used.

CREATE OR REPLACE PROCEDURE APPS.XX_INTERORG_RCPT_PRCE(errbuf out varchar2,retbuf out varchar2,p_shipment_line_id NUMBER,
                                                       p_user_id NUMBER,
                                                        p_shipment_header_id NUMBER,
                                                        p_transaction_id NUMBER,
                                                        p_quantity_shipped NUMBER,
                                                        p_unit_of_measure VARCHAR2,
                                                        p_item_id NUMBER,
                                                        p_to_organization_id NUMBER,
                                                        p_from_organization_id NUMBER,
                                                        p_secondary_quantity_shipped NUMBER,
                                                        p_secondary_unit_of_measure VARCHAR2,
                                                        p_category_id NUMBER,
                                                        p_item_description VARCHAR2,
                                                        p_primary_unit_of_measure VARCHAR2,
                                                        p_locator_id NUMBER,
                                                        p_destination_context VARCHAR2)
IS
l_tran_type NUMBER;
l_uom_code VARCHAR2(5);
l_group_id NUMBER;
l_ou NUMBER;
l_return NUMBER;
l_header_check VARCHAR2(3);
l_header_check_cnt NUMBER;
l_header_check_tran NUMBER;
l_rtp_validate NUMBER;
l_hdr_interface_value NUMBER;
l_ret NUMBER;

BEGIN

fnd_file.put_line (fnd_file.LOG,'Inside the procedure');


    BEGIN

        select mmt.transaction_type_id INTO l_tran_type
        from rcv_shipment_lines rsl,mtl_material_transactions mmt
        where rsl.mmt_transaction_id = mmt.transaction_id
        and rsl.shipment_line_id = p_shipment_line_id;

    EXCEPTION
    WHEN OTHERS THEN

        l_tran_type := 2;

    END;
   
    BEGIN
   
    SELECT uom_code INTO l_uom_code
    FROM mtl_units_of_measure
    WHERE unit_of_measure = p_unit_of_measure;
   
    EXCEPTION
    WHEN OTHERS THEN
   
    l_uom_code := '';
   
    END;


    

IF l_tran_type = 21 THEN

fnd_file.put_line (fnd_file.LOG,'Inside Transaction Type If');


    BEGIN
   
     
            SELECT transaction_id  INTO l_header_check_tran
              FROM
                 (SELECT transaction_id
                    FROM mtl_material_transactions
                   WHERE shipment_number in
                        (SELECT SHIPMENT_NUM FROM RCV_SHIPMENT_HEADERS
                          WHERE SHIPMENT_HEADER_ID = p_shipment_header_id)
                            AND organization_id = p_from_organization_id
                         ORDER BY transaction_id ASC)
            WHERE ROWNUM = 1;
           
                IF l_header_check_tran = p_transaction_id THEN
               
                    l_header_check := '2';
                   
                ELSE
               
                    l_header_check := '1';
                   
                END IF;
               
                fnd_file.put_line (fnd_file.LOG,'l_header_check_tran:'||l_header_check_tran);
               
                fnd_file.put_line (fnd_file.LOG,'l_header_check:'||l_header_check);
   
    EXCEPTION
   
    WHEN OTHERS THEN
   
        fnd_file.put_line (fnd_file.LOG,'Inside Header Check Exception');
   
    END;
   
   
    IF l_header_check = '2' THEN


    fnd_file.put_line (fnd_file.LOG,'Inside Header Check IF');
   
        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,
        CREATION_DATE,
        CREATED_BY,
        LAST_UPDATE_LOGIN,
        EXPECTED_RECEIPT_DATE,
        VALIDATION_FLAG,
        COMMENTS,
        SHIPMENT_NUM,
        FROM_ORGANIZATION_ID,
        SHIP_TO_ORGANIZATION_ID,
        ORG_ID
        )       
        (select RCV_HEADERS_INTERFACE_S.NEXTVAL, --HEADER_INTERFACE_ID
        RCV_INTERFACE_GROUPS_S.NEXTVAL,  --GROUP_ID
       'PENDING', --- PROCESSING_STATUS_CODE,
       'INVENTORY', --- RECEIPT_SOURCE_CODE,
       'NEW', --- TRANSACTION_TYPE,
       'RECEIVE', --- AUT_TRANSACT_CODE,
       SYSDATE, --- LAST_UPDATE_DATE,
       p_user_id, --- LAST_UPDATE_BY,
       SYSDATE, --- CREATION_DATE,
       p_user_id, --- CREATED_BY,
       p_user_id, --- LAST_UPDATE_LOGIN,
       SYSDATE, -- EXCEPTED_RECEIPT_DATE
       'Y',--VALIDATION_FLAG
       null,--COMMENTS
       rsh.shipment_num,--SHIPMENT_NUMBER
       rsh.organization_id,--FROM_ORGANIZATION_ID
       ship_to_org_id, --SHIP_TO_ORGANIZATION_ID
       ood.operating_unit
       from rcv_shipment_headers rsh,org_organization_definitions ood
       where rsh.shipment_header_id = p_shipment_header_id
       and rsh.organization_id = ood.organization_id);
             
      
    INSERT INTO rcv_transactions_interface
    (
    INTERFACE_TRANSACTION_ID,
    HEADER_INTERFACE_ID,
    GROUP_ID,
    TRANSACTION_TYPE,
    TRANSACTION_DATE,
    PROCESSING_STATUS_CODE,
    PROCESSING_MODE_CODE,
    TRANSACTION_STATUS_CODE,
    QUANTITY,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    CREATION_DATE,
    CREATED_BY,
    LAST_UPDATE_LOGIN,
    RECEIPT_SOURCE_CODE,
    DESTINATION_TYPE_CODE,
    AUTO_TRANSACT_CODE,
    SOURCE_DOCUMENT_CODE,
    UNIT_OF_MEASURE,
    ITEM_ID,
    UOM_CODE,
    SHIPMENT_HEADER_ID,
    SHIPMENT_LINE_ID,
    TO_ORGANIZATION_ID,
    FROM_ORGANIZATION_ID,
    SHIPMENT_NUM ,
    EXPECTED_RECEIPT_DATE ,
    INTERFACE_SOURCE_CODE ,
    VALIDATION_FLAG,
    SECONDARY_QUANTITY,
    SECONDARY_UNIT_OF_MEASURE,
    ORG_ID,
    CATEGORY_ID,
    ITEM_DESCRIPTION,
    PRIMARY_QUANTITY,
    PRIMARY_UNIT_OF_MEASURE,
    LOCATOR_ID,
    DESTINATION_CONTEXT
    )
    (SELECT
    RCV_TRANSACTIONS_INTERFACE_S.NEXTVAL, --- INTERFACE_TRANSACTION_ID,
     RCV_HEADERS_INTERFACE_S.CURRVAL, --- HEADER_INTERFACE_ID,
     RCV_INTERFACE_GROUPS_S.CURRVAL, --- GROUP_ID,
     'RECEIVE', --- TRANSACTION_TYPE,       
     SYSDATE, --- TRANSACTION_DATE,
     'PENDING', --- PROCESSING_STATUS_CODE,
     'BATCH', --- PROCESSING_MODE_CODE,
     'PENDING', --- TRANSACTION_STATUS_CODE,
      p_quantity_shipped,--QUANTITY_SHIPPED
      SYSDATE, --- LAST_UPDATE_DATE,
      p_user_id, --- LAST_UPDATE_BY,
      SYSDATE, --- CREATION_DATE,
      p_user_id, --- CREATED_BY,
      p_user_id, --LAST_UPDATE_LOGIN
      'INVENTORY', --- RECEIPT_SOURCE_CODE,
      'INVENTORY', --- DESTINATION_TYPE_CODE,
      'DELIVER', --- AUT_TRANSACT_CODE,
      'INVENTORY', --- SOURCE_DOCUMENT_CODE,
      p_unit_of_measure,--UNIT_OF_MEASURE
      p_item_id,--ITEM_ID ,
      l_uom_code,--UOM_CODE
      rsh.SHIPMENT_HEADER_ID,--SHIPMENT_HEADER_ID
      p_shipment_line_id,--SHIPMENT_LINE_ID
      p_to_organization_id,--TO_ORGANIZATION_ID
      p_from_organization_id,--FROM_ORGANIZATION_ID
      rsh.SHIPMENT_NUM,--SHIPMENT_NUM
      rsh.EXPECTED_RECEIPT_DATE,--EXPECTED_RECEIPT_DATE
      'RCV',--INTERFACE_SOURCE_CODE
      'Y',--VALIDATION_FLAG
      p_secondary_quantity_shipped,--SECONDARY_QUANTITY_SHIPPED
      p_secondary_unit_of_measure,--SECONDARY_UNIT_OF_MEASURE
      ood.OPERATING_UNIT,--ORG_ID
      p_category_id,--CATEGORY_ID
      p_item_description,--ITEM_DESCRIPTION
      p_quantity_shipped,--PRIMARY_QUANTITY
      p_primary_unit_of_measure,
      p_locator_id,
      p_destination_context
      from rcv_shipment_headers rsh,org_organization_definitions ood
      where shipment_header_id = p_shipment_header_id
      and rsh.organization_id = ood.organization_id
      );
     
    
    SELECT RCV_INTERFACE_GROUPS_S.CURRVAL INTO l_group_id
    FROM DUAL;
   
COMMIT;   
     
    ELSE
   
    fnd_file.put_line (fnd_file.LOG,'Inside Header Check Else');
   
    dbms_lock.sleep (5);
   
    SELECT header_interface_id INTO l_hdr_interface_value
    FROM rcv_headers_interface
    WHERE shipment_num IN
    (SELECT SHIPMENT_NUM FROM RCV_SHIPMENT_HEADERS
     WHERE SHIPMENT_HEADER_ID = p_shipment_header_id)
     AND from_organization_id = p_from_organization_id;
   
    fnd_file.put_line (fnd_file.LOG,'l_hdr_interface_value:'||l_hdr_interface_value);
   
    SELECT group_id INTO l_group_id
    FROM rcv_headers_interface
    WHERE shipment_num IN
    (SELECT SHIPMENT_NUM FROM RCV_SHIPMENT_HEADERS
     WHERE SHIPMENT_HEADER_ID = p_shipment_header_id)
     AND from_organization_id = p_from_organization_id;
    
    fnd_file.put_line (fnd_file.LOG,'l_group_id:'||l_group_id);
   
    
    INSERT INTO rcv_transactions_interface
    (
    INTERFACE_TRANSACTION_ID,
    HEADER_INTERFACE_ID,
    GROUP_ID,
    TRANSACTION_TYPE,
    TRANSACTION_DATE,
    PROCESSING_STATUS_CODE,
    PROCESSING_MODE_CODE,
    TRANSACTION_STATUS_CODE,
    QUANTITY,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    CREATION_DATE,
    CREATED_BY,
    LAST_UPDATE_LOGIN,
    RECEIPT_SOURCE_CODE,
    DESTINATION_TYPE_CODE,
    AUTO_TRANSACT_CODE,
    SOURCE_DOCUMENT_CODE,
    UNIT_OF_MEASURE,
    ITEM_ID,
    UOM_CODE,
    SHIPMENT_HEADER_ID,
    SHIPMENT_LINE_ID,
    TO_ORGANIZATION_ID,
    FROM_ORGANIZATION_ID,
    SHIPMENT_NUM ,
    EXPECTED_RECEIPT_DATE ,
    INTERFACE_SOURCE_CODE ,
    VALIDATION_FLAG,
    SECONDARY_QUANTITY,
    SECONDARY_UNIT_OF_MEASURE,
    ORG_ID,
    CATEGORY_ID,
    ITEM_DESCRIPTION,
    PRIMARY_QUANTITY,
    PRIMARY_UNIT_OF_MEASURE,
    LOCATOR_ID,
    DESTINATION_CONTEXT
    )
    (SELECT
    RCV_TRANSACTIONS_INTERFACE_S.NEXTVAL, --- INTERFACE_TRANSACTION_ID,
     l_hdr_interface_value, --- HEADER_INTERFACE_ID,
     l_group_id, --- GROUP_ID,
     'RECEIVE', --- TRANSACTION_TYPE,       
     SYSDATE, --- TRANSACTION_DATE,
     'PENDING', --- PROCESSING_STATUS_CODE,
     'BATCH', --- PROCESSING_MODE_CODE,
     'PENDING', --- TRANSACTION_STATUS_CODE,
      p_quantity_shipped,--QUANTITY_SHIPPED
      SYSDATE, --- LAST_UPDATE_DATE,
      p_user_id, --- LAST_UPDATE_BY,
      SYSDATE, --- CREATION_DATE,
      p_user_id, --- CREATED_BY,
      p_user_id, --LAST_UPDATE_LOGIN
      'INVENTORY', --- RECEIPT_SOURCE_CODE,
      'INVENTORY', --- DESTINATION_TYPE_CODE,
      'DELIVER', --- AUT_TRANSACT_CODE,
      'INVENTORY', --- SOURCE_DOCUMENT_CODE,
      p_unit_of_measure,--UNIT_OF_MEASURE
      p_item_id,--ITEM_ID ,
      l_uom_code,--UOM_CODE
      rsh.SHIPMENT_HEADER_ID,--SHIPMENT_HEADER_ID
      p_shipment_line_id,--SHIPMENT_LINE_ID
      p_to_organization_id,--TO_ORGANIZATION_ID
      p_from_organization_id,--FROM_ORGANIZATION_ID
      rsh.SHIPMENT_NUM,--SHIPMENT_NUM
      rsh.EXPECTED_RECEIPT_DATE,--EXPECTED_RECEIPT_DATE
      'RCV',--INTERFACE_SOURCE_CODE
      'Y',--VALIDATION_FLAG
      p_secondary_quantity_shipped,--SECONDARY_QUANTITY_SHIPPED
      p_secondary_unit_of_measure,--SECONDARY_UNIT_OF_MEASURE
      ood.OPERATING_UNIT,--ORG_ID
      p_category_id,--CATEGORY_ID
      p_item_description,--ITEM_DESCRIPTION
      p_quantity_shipped,--PRIMARY_QUANTITY
      p_primary_unit_of_measure,
      p_locator_id,
      p_destination_context
      from rcv_shipment_headers rsh,org_organization_definitions ood
      where shipment_header_id = p_shipment_header_id
      and rsh.organization_id = ood.organization_id
      );
     
     
    COMMIT;
         
     END IF;
     
      INSERT INTO mtl_transaction_lots_interface
     ( transaction_interface_id,
     last_update_date,
     last_updated_by,
     creation_date,
     created_by,
     last_update_login,
     lot_number,
     transaction_quantity,
     primary_quantity,
     serial_transaction_temp_id,
     product_code,
     product_transaction_id)
     (SELECT
     mtl_material_transactions_s.nextval,--transaction_interface_id
     SYSDATE, --last_update_date
     p_user_id, --last_updated_by
     SYSDATE, --creation_date
     p_user_id, --created_by
     p_user_id, --last_update_login
     lot_number, --lot_number
     abs(transaction_quantity), --transaction_quantity
     abs(primary_quantity), --primary_quantity
     NULL, --serial_transaction_temp_id
     'RCV', --product_code
     RCV_TRANSACTIONS_INTERFACE_S.CURRVAL --product_transaction_id
     FROM MTL_TRANSACTION_LOT_NUMBERS
     WHERE transaction_id = p_transaction_id
     );
    
commit;         
  
    SELECT operating_unit INTO l_ou
    FROM org_organization_definitions
    WHERE organization_id = p_from_organization_id;
   
    fnd_file.put_line (fnd_file.LOG,'l_ou:'||l_ou);
   
    SELECT transaction_id  INTO l_rtp_validate
    FROM
    (SELECT transaction_id
    FROM mtl_material_transactions
    WHERE shipment_number in
    (SELECT SHIPMENT_NUM FROM RCV_SHIPMENT_HEADERS
     WHERE SHIPMENT_HEADER_ID = p_shipment_header_id)
     AND organization_id = p_from_organization_id
    ORDER BY transaction_id DESC)
    WHERE ROWNUM = 1;

    fnd_file.put_line (fnd_file.LOG,'l_rtp_validate:'||l_rtp_validate);

    IF p_transaction_id = l_rtp_validate THEN
   
     fnd_global.apps_initialize (p_user_id, resp_id, appl_id);
   
     l_return := Fnd_Request.submit_request(application=>'PO',
                                          program=>'RVCTP',
                                          description=>'Receiving Transaction Processor',
                                          start_time=>SYSDATE,
                                          sub_request=>FALSE,
                                          argument1=>'BATCH',
                                          argument2=>l_group_id,
                                          argument3=>l_ou);
                                         
     fnd_global.apps_initialize (p_user_id, resp_id, appl_id);
   

     l_ret := FND_REQUEST.SUBMIT_REQUEST(
                        'JA', -- Modified by Ramananda for Bug# 4336482 (Changed PO to JA)
                        'JAINRVCTP',
                        'India - Receiving Transaction Processor', -- Commented "for Receipt" By Rallamse for Bug 4336482 
                        TO_CHAR(SYSDATE + 2 / 1440,'dd-mon-yy hh24:mi:ss'), FALSE,
                         to_char(p_to_organization_id), '', '', '',
                         '', p_shipment_header_id, '', '', '', 'Y', 'JAINPORE', 'N', 'N'
                    );                                               
                                         
    END IF;                                         

commit;

END IF;    

END;
/