Query to Find Requisition Number from Purchase Order Number

Query to find the purchase order number from requisition number:

SELECT DISTINCT pha.segment1
FROM po_headers_all pha,po_distributions_all pda,po_req_distributions_all rda,
po_requisition_headers_all rha,po_requisition_lines_all rla
WHERE 1=1
AND pha.po_header_id=pda.po_header_id
AND pda.req_distribution_id=rda.distribution_id
AND rda.requisition_line_id=rla.requisition_line_id
AND rla.requisition_header_id=rha.requisition_header_id
AND rha.segment1=:P_REQ_NUM;

Query to find the requisition number from purchase order number:

SELECT DISTINCT rha.segment1
FROM po_requisition_headers_all rha,po_requisition_lines_all rla,
po_req_distributions_all rda,po_distributions_all pda,po_headers_all pha
WHERE 1=1
AND rha.requisition_header_id=rla.requisition_header_id
AND rla.requisition_line_id=rda.requisition_line_id
AND rda.distribution_id=pda.req_distribution_id
AND pda.po_header_id=pha.po_header_id
AND pha.segment1=:P_PO_NUMBER;

References:
http://amaralam.blogspot.com/2016_04_01_archive.html

How to query ECE_ tables?

Begin
 MO_GLOBAL.INIT('PO');
-- mo_global.set_policy_context('S',121);
End;


select count(1)
from ECE_POO_LINES_V; --43721

select *
from ECE_POO_HEADERS_V;

select *
from ECE_POO_LINES_V;

select *
from ECE_POO_SHIPMENTS_V;

select *
from po_headers;

P2P Cycle Tables

--Inventory
select *
from mtl_system_items_b msib
where msib.segment1='TEST-GJ1';

select *
from mtl_onhand_quantities_detail
where inventory_item_id=9727542;

select *
from mtl_onhand_quantities --view
where inventory_item_id=9727542;

--Requisitions
select *
from po_requisition_headers_all prha
where segment1='110133332';

select *
from po_requisition_lines_all prla
where prla.REQUISITION_HEADER_ID=8833662;

select *
from po_req_distributions_all prda
where prda.REQUISITION_LINE_ID=10342785;

select *
from gl_code_combinations gcc
where code_combination_id=5093;

--Suppliers
select *
from ap_suppliers
where segment1='21772';

select *
from ap_supplier_sites_all
where vendor_id=1647228;

select *
from ap_supplier_contacts
where vendor_site_id=1534087;

--TCA tables
select *
from  hz_parties
where party_name='TITAN';

select *
from hz_party_sites
where party_id=19200064;

--PO
select *
from po_headers_all
where segment1='110422393';

select *
from po_lines_all
where po_header_id=6057836;

select *
from po_line_locations_all
where po_line_id=6383221;

select *
from po_distributions_all
where line_location_id=6563044;

--Receipts
select *
from rcv_shipment_headers
where receipt_num='29193';

select *
from  rcv_shipment_lines
where shipment_header_id=11487976;

select *
from rcv_transactions
where shipment_header_id=11487976;

--Invoices (AP)
select *
from ap_invoices_all
where invoice_num='TITAN_BEL_1';

select *
from ap_invoice_lines_all
where invoice_id=14320694;

select aida.ACCOUNTING_EVENT_ID, aida.*
from ap_invoice_distributions_all aida
where invoice_id=14320694;

--Accounting(invoice)
select *
from xla_events
where event_id=46887546;

select *
from xla_ae_headers
where event_id=46887546;

select *
from xla_ae_lines
where ae_header_id=23181809;

select  *
from gl_interface
where reference26=46887546;

--Ledger
select *
from gl_je_batches
where name='Payables A 10207529 81073959';

select *
from gl_je_headers
where je_batch_id=14085651;

select *
from gl_je_lines
where je_header_id=24716350;

--Posting
select *
from gl_balances
where ledger_id=2022
and code_combination_id in (5101,2007)
and period_name='Aug-16'
and trunc(last_update_date)=trunc(sysdate);

--Payments
select aipa.accounting_event_id, aipa.*
from ap_invoice_payments_all aipa
where invoice_id='14320694';

select *
from ap_payment_schedules_all
where invoice_id='14320694';

select *
from ap_checks_all
where check_id=4057921;

--Accounting (payment)
select *
from xla_events
where event_id=46887547;

select *
from xla_ae_headers
where event_id=46887547;

select *
from xla_ae_lines
where ae_header_id=23181810;

select  *
from gl_interface
where reference26=46887547;

--Ledger
select *
from gl_je_batches
where name='Payables A 10207530 81074076';

select *
from gl_je_headers
where je_batch_id=14085653;

select *
from gl_je_lines
where je_header_id=24716352;

--Posting
select *
from gl_balances
where 1=1
--and ledger_id=2022
--and code_combination_id in (5101,2007, 38911)
and last_updated_by=97983
--and period_name='Aug-16'
and trunc(last_update_date)=trunc(sysdate);

Accessing Approval Management Engine (AME) - Roles and Grants

Accessing Approval Management Engine (AME) is not possible as we access other responsibility / functions in Oracle Applications like Order Management / Application Developer etc.

AME is controlled by RBAC (Role Based Access Control). So in this case even you assign responsibility to the user but if user don't have the appropriate roles then you will not be able to access Approval Management functions.

There are two seeded responsibility for AME :-

• Approvals Management Administrator
• Approvals Management Business Analyst

Let's assign the responsibility directly to user and see what happens.

 Login with respective user.

Chose Approval Management Responsibility

You will receive below error.

With the screen shots above we can see that even though the responsibility is assigned to user while trying to access it, It says There is no valid navigation for this responsibility.

Accessing AME is two step process :-

• Assign the required roles to the user : This will give user access to use Approval Management functions.
• Grant access to respective AME transaction type : This will provide access to AME transaction type. If we don't perform this step then we will be able to access AME responsibility but will not be able to access any transaction types.

We have following seeded roles provided by Oracle that can be used to access AME.

• Approvals Management Administrator
• Approvals Management Business Analyst
• Approvals Management Process Owner
• Approvals Management System Administrator
• Approvals Management System Viewer

Step -1 : Assign the Roles

• Login with administrator / sysadmin user.
  
• Navigate to User Management -> User.
  
• Enter User Name and click on Go to find the user.
• Click on Update button
  
• Click on Assign Roles button.
  
• Search the role 'Approval Management Administrator' from LOV and select it.
  
  
• Based on the role chosen you will see number of functions added under users roles.
  

Step -2 : Grant Access to Transaction Types

• Login with administrator / sysadmin user.
• Navigate to Functional Administrator -> Grants -> Create Grants.
  
  
• Enter a name and description for the Grant
• Enter effective from date and optionally effective to date.
• Select Grantee Type from Security Context region
• All Users : The grant will be applied to all the users and all users will be given same right / privileges that is part of this grant. 
• Group Of Users : The grant will be applied to users those are part of the group.
• Specific User : Grant will be applicable of specific set of users that you select.
• I have opted for specific user.
• In addition to this you can also choose Operating Unit and Responsibility to make is more secure. Say you want to provide a user to AME access to US operating Unit only. In that case you can choose operating unit. If there is not such restriction required then leave it blank.
• From Data Security region select an object. In our case it will be 'AME Transaction Types'
  
• Click Next
• In the Data Context Type select 'All Rows'.
  
• Select 'AME Calling Applications' as permission set and click Next to review the setup.
  
• Finish to complete.
  

Now you are good to use AME and setup approvals. Happy Setups...

References:
http://www.oracleappsnfusion.com/2014/01/accessing-ame.html
http://mtalmasri.blogspot.com/2012/04/oracle-ebs-repairing-xxx-is-not-valid.html

Steps to have an Automatically Approved Purchase Orders Created from Requisitions

1. Identify the Supplier and Supplier Site that is desired to have the PO created automatically for.

2. Create a Quotation (Set it to Approved Status) for each item used by the selected Supplier and Supplier Site.

3. Create an ASL for the item and add the Supplier and Supplier Site.

4. Under the ASL Attributes, add the Quotation entered in Step 2

5. Create a Sourcing Rule for the Supplier and Supplier Site

6. Create an Assignment Set for this Organization or Item and list this Sourcing Rule in that Assignment Set
    (One Assignment Set can be used that will include all of the desired Assignments)

7. Set the Profile - MRP: Default Sourcing Assignment Set to the Assignment Set created in Step 6

8. Set the Workflow Item Attributes

- Is Automatic Creation Allowed? = Yes    (This is the PO Create Documents workflow)
- Is Automatic Approval Allowed? = Yes     (This is the PO Create Documents workflow)
- Send PO Autocreation to Background? = No    (This is the Requisition approval workflow)

To update the attributes, follow these steps in workflow builder:

a. Open workflow builder and connect to the database as the APPS user. Open the PO Create Workflow. The display name is PO Create Documents.

b. Expand the data source, and then the PO Create workflow item type branch within that data source.

c. Expand the Attributes branch within the PO Create workflow branch.

d. Double click on the attribute to open the Properties window. Set the value of the attributes as indicated.

e. Repeat the steps for item type PO Requisition Approval to set the indicated attribute.

9. Create a Requisition and submit it for Approval

10. Once Approved the Create Documents workflow will kick off and create a Standard PO

11. Since Is Automatic Approval Allowed? = Yes, the PO will be automatically submitted for Approval

12. If the Buyer defaulted on the PO has the approval authority the PO will be approved.
      If not, the PO will be forwarded up the Buyer's Approval Hierarchy that is already setup.
      The hierarchy used in the Create Documents Workflow, by which the Buyer is determined is as follows:
      (Will start with the Step 1, and continue until either a Buyer is found, or the hierarchy is exhausted.)

1. Get Buyer from Requisition Line
2. Get Buyer from Item
3. Get Buyer from Category on Requisition
4. Get Buyer from Source Document (For this case the Quotation created in Step 2 above)
5. Get Buyer from Contract (The Contract would need to exist for the Supplier and Supplier site)

NOTE: In R12 and higher this logic has changed where we decide if OK to use buyer from source document.
-If OK to use buyer from source document depends on the HR: Security Profile setting and if the source document is a global agreement.
-If the source document is a CPA, it will not be considered.

References:
http://mahamadsulthanoracleapplications.blogspot.com/2013/05/steps-to-have-automatically-approved.html

Charge Account Defaulting in PO

 

Charge account defaulting rules depend on 
a) Type of Item 
b) Destination Type
c) Destination Sub-inventory.

There are 3 kinds of Items used in Purchasing.

Expense Items - Referred to as Item A
Inventory Expense Items - Referred to as Item B
Inventory Asset Items - Referred to Item C


1) Expense Items - Referred to as Item A.
These items normally have the following attributes...

INVENTORY_ASSET_FLAG = N
PURCHASING_ITEM_FLAG = Y
INVENTORY_ITEM_FLAG = N

2) Inventory Expense Items - Referred to as Item B
These items normally have the following attributes...

INVENTORY_ASSET_FLAG = N
PURCHASING_ITEM_FLAG = Y
INVENTORY_ITEM_FLAG = Y


c) Inventory Asset Items - Referred to Item C
These items have the following attributes...

INVENTORY_ASSET_FLAG = Y
PURCHASING_ITEM_FLAG = Y
INVENTORY_ITEM_FLAG = Y
COSTING_ENABLED_FLAG = Y


2. DESTINATION TYPE :
The Destination type determines the final destination of the
items.
This can be of two types 
a) EXPENSE
b) INVENTORY - 



3. DESTINATION Subinventory:
When an Item is being received into Inventory , a sub-inventory 
needs to be specified. A subinventory can be 
a) ASSET subinventory
b) EXPENSE sub-inventory


The sql below will help in identifying what type of a 
subinventory , the item is being delivered INTO.


select 
substr(EXPENSE_ACCOUNT,1,6) Exp_act , 
substr(ENCUMBRANCE_ACCOUNT,1,7) Enc_act ,
ASSET_INVENTORY,
substr(SECONDARY_INVENTORY_NAME,1,15) S_name,
substr(DESCRIPTION,1,15) descrip,
substr(SOURCE_ORGANIZATION_ID,1,8) s_o_id
from 
MTL_SECONDARY_INVENTORIES
where 
SECONDARY_INVENTORY_NAME = '&destination_subinventory';

pass the value of DESTINATION_SUBINVENTORY 
from PO_DISTRIBUTIONS_ALL.DESTINATION_SUBINVENTORY

if the value of ASSET_INVENTORY = 1 then this is an ASSET subinventory.
if the value of ASSET_INVENTORY = 2 then this is NOT an ASSET subinventory.




3.Keeping in view of the types of Items and the destination mentioned above
These are the destination types that can be possible against each type of item

Item A can have ONLY one destination- EXPENSE

Item B can have the destination of 
a) EXPENSE
b) INVENTORY -- ASSET subinventory
c) INVENTORY -- EXPENSE subinventory

Item C can have the destination of 
a) EXPENSE
b) INVENTORY -- ASSET subinventory
c) INVENTORY -- EXPENSE subinventory



Defaulting Rules for 
1) Item A --- Defaults from the Expense a/c defined against the ITEM in the Receiving Organization.( Organization Items) 

=======================================================================================
2) Item B--- EXPENSE -Defaults from the Expense a/c defined against the ITEM in the
in the Receiving Organization.( Organization Items) 

2) Item B ---INVENTORY--- ASSET Subinventory .... charge account comes from 
a) EXPENSE a/c of the ASSET SUBINVENTORY in the RECEIVING Organization.
(This is ONLY if the PO Distribution , contains the Subinventory Information) 

b) If the PO distribution DOES NOT have a SUBINVENTORY information
Expense a/c defined in the Organizational Parameters of the Receiving Organization.

3) Item B ---INVENTORY--- EXPENSE subinventory -- comes from 
a) EXPENSE a/c of the EXPENSE SUBINVENTORY in the RECEIVING Organization.
(This is ONLY if the PO Distribution , contains the Subinventory Information) 

b) If the PO distribution DOES NOT have a SUBINVENTORY information
Expense a/c defined in the Organizational Parameters of the Receiving Organization.

=========================================================================================
4) Item C --- EXPENSE -Defaults from the Expense a/c defined against the ITEM in the
in the Receiving Organization.( Organization Items) 

5) Item C ---INVENTORY---ASSEST Subinventory .... charge account comes from 

a) Material a/c defined against the ASSET SUBINVENTORY /of the RECEIVING Organization.
(This is ONLY if the PO Distribution , contains the Subinventory Information)

b) Material a/c defined in the Organizational Parameters of the Receiving Organization.

6) Item C ---INVENTORY --EXPENSE Subinventory -- comes from 
a) Expense a/c defined against the EXPENSE Subinventory in Receiving Organization.
(This is ONLY if the PO Distribution , contains the Subinventory Information)

b) If the PO distirbution DOES NOT have a SUBINVENTORY information
Material a/c defined in the Organizational Parameters of the Receiving Organization.

References:
http://snroracle.blogspot.com/2013_10_01_archive.html

Oracle Purchasing Approval

 

In PO there are two methods to route documents for approval.
1.Approval Hierarchies (uses position hierarchies, A tree of hierarchy more than one person above to approve)

2.Employee/Supervisor Relationships (use employee/supervisor relationship, Just one person above to approve)
Approval Hierarchies

Purchasing utilizes positions as a roadmap to determine how and where documents will be routed once the approval process has been initiated. It is first necessary to have created all positions that are going to be used in the system. Once all positions have been created, it is necessary to build the position hierarchy.
Each position has approval limits, so when a purchase order exceeds the limits of the position, the purchase order is forwarded onto the next position in the Hierarchy
Employee/Supervisor Relationships
This type of hierarchy does not use the Approval Hierarchy form,but is defined by the employee/supervisor relationship. The supervisor of an employee is defined on the Assignment region of the Employee form
If the purchase order entered by the employee exceeds the approval limits, the purchase order is forwarded onto the employees’ supervisor, as defined on the Employee form
To implement this form of approval routing, you need only to define JOBS. The JOB will then serve as the tie to the Approval group, and based on the approval limits from the Approval Group, the Document will either be Approved or Forwarded to the Employees’ Supervisor

References:
http://2lyoracleapps.blogspot.com/

Maximum Personalization on Purchase Requisition Form

We keep on creating personalization on Purchase Requisition screen as per business requirements, one fine day we found the limit. Oracle allows only maximum of 100 personalizations in a standard form its what we thought but digging deeper we found that numbers could be tweaked with fractions (eg: 10.1, 10.2,10.3,11,12)

Its very rare you will need more than 100-Personalizations in a form.

References:

http://2lyoracleapps.blogspot.com/

 

Serial Number Tables for Data fix Usage

select msib.*
from mtl_system_items_b msib
where msib.segment1='908-000008-002'
and msib.organization_id=121;  --121 = Master Org_id
select * from MTL_SERIAL_NUMBERS
WHERE SERIAL_NUMBER in ('FB00000','FB00001','FB00002');
AND INVENTORY_ITEM_ID IN (200906);
--and current_organization_id=124;

select * from MTL_UNIT_TRANSACTIONS
WHERE SERIAL_NUMBER in ('FB00000','FB00001','FB00002');
AND INVENTORY_ITEM_ID IN (200906);

select * from WSH_SERIAL_NUMBERS
--SET FM_SERIAL_NUMBER =  '452979', TO_SERIAL_NUMBER = '452979'
WHERE FM_SERIAL_NUMBER in ('FB00000','FB00001','FB00002');
AND DELIVERY_DETAIL_ID = 20637412;

select * from RCV_SERIAL_TRANSACTIONS
WHERE SERIAL_NUM in ('FB00000','FB00001','FB00002');
AND  SHIPMENT_LINE_ID = 11490696;

select * from RCV_SERIALS_SUPPLY
WHERE SERIAL_NUM in ('FB00000','FB00001','FB00002');
AND  SHIPMENT_LINE_ID = 11490696;
                   

Approval List could not generated. Please contact your System Administrator to review AME rules setup.

Approval List could not generated. Please contact your System Administrator to review AME rules setup.

Cause:

Additional Requisitions (Cancelled, Incomplete, System Saved) getting inserted while creating PR.

Solution:

Execute the script to reset the Active_Shopping_Cart_Flag to null.

UPDATE po_requisition_headers_all h  

SET active_shopping_cart_flag = null  

WHERE 

h.authorization_status = :Status and 

active_shopping_cart_flag = 'Y' 

AND h.preparer_id in (select employee_id from fnd_user u where u.user_name = UPPER( :user_name ) )  

AND h.org_id =:org_id 

AND h.segment1 =:PR Number

Receiving PO Using Receiving Open Interface (ROI)

The Receiving Transaction Processor processes pending or unprocessed receiving transactions. We can receive the Purchase Order either using the Expected Receipt form or by putting the record into the Receiving Open Interface (ROI). And then if we will submit the receiving transactions processor so the PO will be received.

Records needs to be inserted into rcv_transactions_interface with processing_status_code and transaction_status_code as 'PENDING' and transaction_type of 'RECEIVE'. and also inserted into rcv_shipment_headers which creates the shipment header.

Interface Tables: -

• rcv_headers_interface
• rcv_transactions_interface
• mtl_transaction_lots_interface

Error Table: -

• po_interface_errors

Base Tables:

• rcv_shipment_headers
• rcv_shipment_lines
• rcv_transactions
• mtl_lot_numbers
• mtl_material_transactions
• rcv_lot_transactions

R12 - Sample Procedure to Receive PO by inserting records into ROI

DECLARE

x_user_id NUMBER;
x_resp_id NUMBER;
x_appl_id NUMBER;
x_po_header_id NUMBER;
x_vendor_id NUMBER;
x_segment1 VARCHAR2 (20);
x_org_id NUMBER;
x_line_num NUMBER;
l_chr_lot_number VARCHAR2 (50);
l_chr_return_status VARCHAR2 (2000);
l_num_msg_count NUMBER;
l_chr_msg_data VARCHAR2 (50);
v_count NUMBER;

BEGIN

DBMS_OUTPUT.put_line ('RCV Sample Insert Script Starts');
DBMS_OUTPUT.put_line ('**************************************');

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 = '380087' -- Enter The Po Number which needs to be received
AND org_id = 308 -- Enter the org_id
AND approved_flag = 'Y'
AND nvl(cancel_flag, 'N') = 'N';

SELECT DISTINCT
u.user_id,
to_char(a.responsibility_id) responsibility_id,
b.application_id
INTO
x_user_id, x_resp_id, x_appl_id
from
apps.fnd_user_resp_groups_direct a,
apps.fnd_responsibility_vl b,
apps.fnd_user u,
apps.fnd_application fa
where
a.user_id = u.user_id
and a.responsibility_id = b.responsibility_id
and a.responsibility_application_id = b.application_id
and sysdate between a.start_date and nvl(a.end_date,sysdate+1)
and fa.application_id (+) = b.application_id
and upper(u.user_name) = 'A42485' -- Enter the User_name
and b.responsibility_name = 'Inventory'; -- Enter The Responsibility Name

DBMS_OUTPUT.put_line ('Inserting the Record into Rcv_headers_interface');
DBMS_OUTPUT.put_line ('*********************************************');

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, creation_date, created_by,
vendor_id,expected_receipt_date, validation_flag)
SELECT rcv_headers_interface_s.NEXTVAL, rcv_interface_groups_s.NEXTVAL,
'PENDING', 'VENDOR', 'NEW', SYSDATE, x_user_id, 0,SYSDATE, x_user_id,
x_vendor_id, SYSDATE, 'Y'
FROM DUAL;

DECLARE

CURSOR po_line
IS
SELECT
pl.org_Id, pl.po_header_id, 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,
pda.destination_type_code,
pda.deliver_to_person_id,
pda.deliver_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_header_id = x_po_header_id
AND pl.po_line_id = pll.po_line_id
AND pll.line_location_id = pda.line_location_id
AND pll.ship_to_organization_id = mp.organization_id;

BEGIN

FOR rec_det IN po_line LOOP

IF rec_det.closed_code IN ('APPROVED', 'OPEN')
AND rec_det.quantity_received <>

THEN

DBMS_OUTPUT.put_line ('Inserting the Record into Rcv_Transactions_Interface');
DBMS_OUTPUT.put_line ('*********************************************');

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, document_num,
destination_type_code,deliver_to_person_id,
deliver_to_location_id,subinventory,
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', rec_det.po_header_id,rec_det.po_line_id,
rec_det.item_id, rec_det.quantity,
rec_det.unit_meas_lookup_code,
rec_det.line_location_id, 'DELIVER', 'VENDOR',
rec_det.organization_code, 'PO', x_segment1,
rec_det.destination_type_code, rec_det.deliver_to_person_id,
rec_det.deliver_to_location_id, rec_det.destination_subinventory,
rcv_headers_interface_s.CURRVAL, 'Y'
FROM DUAL;

DBMS_OUTPUT.put_line ('PO line:' rec_det.line_num ' Shipment: ' rec_det.shipment_num ' has been inserted into ROI.');

select count(*)
into v_count
from mtl_system_items
where inventory_item_id = rec_det.item_id
and lot_control_code = 2 -- 2 - full_control, 1 - no control
and organization_id = rec_det.destination_organization_id;

IF v_count > 0 then

DBMS_OUTPUT.put_line ('The Ordered Item is Lot Controlled');
DBMS_OUTPUT.put_line ('Generate the Lot Number for the Lot Controlled Item');

BEGIN

-- initialization required for R12
mo_global.set_policy_context ('S', rec_det.org_id);
mo_global.init ('INV');
-- Initialization for Organization_id
inv_globals.set_org_id (rec_det.destination_organization_id);
-- initialize environment
fnd_global.apps_initialize (user_id => x_user_id,
resp_id => x_resp_id,
resp_appl_id => x_appl_id);

DBMS_OUTPUT.put_line ('Calling inv_lot_api_pub.auto_gen_lot API to Create Lot Numbers');
DBMS_OUTPUT.put_line ('*********************************************');

l_chr_lot_number :=
inv_lot_api_pub.auto_gen_lot
(p_org_id => rec_det.destination_organization_id,
p_inventory_item_id => rec_det.item_id,
p_parent_lot_number => NULL,
p_subinventory_code => NULL,
p_locator_id => NULL,
p_api_version => 1.0,
p_init_msg_list => 'F',
p_commit => 'T',
p_validation_level => 100,
x_return_status => l_chr_return_status,
x_msg_count => l_num_msg_count,
x_msg_data => l_chr_msg_data);

IF l_chr_return_status = 'S' THEN
COMMIT;
ELSE
ROLLBACK;
END IF;

DBMS_OUTPUT.put_line ('Lot Number Created for the item is => ' l_chr_lot_number);

END;

DBMS_OUTPUT.put_line ('Inserting the Record into mtl_transaction_lots_interface ');
DBMS_OUTPUT.put_line ('*********************************************');

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
x_user_id, --last_updated_by
sysdate, --creation_date
x_user_id, --created_by
-1, --last_update_login
l_chr_lot_number, --lot_number
rec_det.quantity, --transaction_quantity
rec_det.quantity, --primary_quantity
NULL, --serial_transaction_temp_id
'RCV', --product_code
rcv_transactions_interface_s.currval --product_transaction_id
from dual);

ELSE

DBMS_OUTPUT.put_line ('The Ordered Item is Not Lot Controlled');
DBMS_OUTPUT.put_line ('********************************************');

END IF;

ELSE
DBMS_OUTPUT.put_line ( 'PO line ' rec_det.line_num'-' rec_det.shipment_num ' is either closed, cancelled, received.');
DBMS_OUTPUT.put_line ('*********************************************');

END IF;

END LOOP;

DBMS_OUTPUT.put_line ('RCV Sample Insert Script Ends');
DBMS_OUTPUT.put_line ('*****************************************');

END;

COMMIT;

END;

-- Cross Check the Records in the Interface Table

select * from apps.rcv_headers_interface
where created_by = 2083
and group_id = ***

select *
from apps.rcv_transactions_interface
where created_by = 2083
and group_id = ***

select * from apps.mtl_transaction_lots_interface
where created_by = 2083
and lot_number = ***
and product_transaction_id in
(select interface_transaction_id from apps.rcv_transactions_interface
where created_by = 2083 and group_id = ***)

-- Check for the Error

select * from po_interface_errors
where batch_id = ***

-- Reprocessing the records from the interface if the same errored out there.

UPDATE rcv_headers_interface
SET processing_request_id = NULL,
validation_flag = 'Y',
processing_status_code = 'PENDING'
WHERE GROUP_ID = ***

UPDATE rcv_transactions_interface
SET request_id = NULL,
processing_request_id = NULL,
validation_flag = 'Y',
processing_status_code = 'PENDING',
transaction_status_code = 'PENDING',
processing_mode_code = 'BATCH'
WHERE interface_transaction_id = ***
AND batch_id = ***


-- Verification of the base tables Once the Receiving Transactions Processor is Completed

select * from apps.rcv_shipment_headers
where created_by = 2083

select * from apps.rcv_shipment_lines
where created_by = 2083
and po_header_id = 619
select * from apps.rcv_transactions
where po_header_id = 619
and created_by = 2083

select * from apps.mtl_lot_numbers
where lot_number in ('A6631684', 'A6631685', 'A6631686')
select * from apps.rcv_lot_transactions
where lot_num in ('A6631684', 'A6631685', 'A6631686')
select * from apps.mtl_material_transactions
where created_by = 2083
and rcv_transaction_id in (select transaction_id from apps.rcv_transactions
where po_header_id = 619
and created_by = 2083)

SELECT (SELECT segment1
FROM po_headers_all
WHERE po_header_id = pl.po_header_id
AND org_id = pl.org_id) po_number, pl.po_header_id,
pl.item_id, pl.po_line_id, pl.line_num, pll.shipment_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,
pda.deliver_to_location_id, pda.destination_subinventory
FROM
apps.po_lines_all pl,
apps.po_line_locations_all pll,
apps.mtl_parameters mp,
apps.po_distributions_all pda
WHERE 1 = 1
AND pl.po_header_id = 619
AND pl.org_id = 308
AND pl.po_line_id = pll.po_line_id
AND pll.line_location_id = pda.line_location_id
AND pll.ship_to_organization_id = mp.organization_id
order by 1, 5, 6

 References:
http://jyotioraapps.blogspot.com/2009/08/receiving-po-using-receiving-open.html