, , , , , , , , ,

Use Of Pricing API (QP_PRICE_LIST_PUB.Process_Price_List)

Oracle Pricing provides the ability to allow for the import of large volumes of Price List data into the Pricing Tables.

The following two methods may be used to populate the price list.

A) QP: Bulk Import of Price List

B) Pricing API (QP_PRICE_LIST_PUB.Process_Price_List).

Price List Setup API.(QP_PRICE_LIST_PUB.Process_Price_List):
=================================================The Price List Setup package consists of entities to set up price lists.

The Price List Setup package QP_Price_List_PUB.Process_Price_List contains the following public record type and table of records entities:

  • Process_Price_List: QP_Price_List_PUB.Process_Price_List:.Takes two record types and six table types as input parameters. Use this API to insert, update, and delete price lists and to set up a price list for a given P_PRICE_LIST_REC record structure.

We can use the API in the following mentioned way:

  • Set up multiple price list lines by giving multiple price list line definitions in the P_ PRICE_LIST_LINE_TBL table structure.

  • Attach multiple qualifiers at the price list header level by giving multiple qualifiers in the P_QUALIFIERS_TBL table structure.

  • Attach multiple pricing attributes to price list lines by giving the pricing attributes in the P_PRICING_ATTR_TBL table structure.

  • Price_List_Rec_Type: Corresponds to the columns in the price list header tables QP_ LIST_HEADERS_B and QP_LIST_HEADERS_TL.

  • Price_List_Val_Rec_Type: Attributes that store the meaning of id or code columns in the price list header table QP_LIST_HEADERS_B, for example, Currency.

  • Price_List_Line_Rec_Type: Corresponds to columns in the price list line table and related modifiers tables QP_LIST_LINES and QP_RLTD_MODIFIERS.

  • Price_List_Line_Tbl_Type: Table of Price_List_Line_Rec_Type.

  • Price_List_Line_Val_Rec_Type: Attributes that store the meaning of id or code columns in the price list line table QP_LIST_LINES, for example, Price_By_Formula.

  • Price_List_Line_Val_Tbl_Type: Table of Price_List_Line_Val_Rec_Type.

  • Qualifiers_Rec_Type: Corresponds to the columns in the qualifier table QP_QUALIFIERS.

  • Qualifiers_Tbl_Type: Table of Qualifiers_Rec_Type.

  • Qualifiers_Val_Rec_Type: Made up of attributes that store the meaning of id or code columns in the qualifiers table QP_QUALIFIERS, for example, Qualifier_Rule.

  • Qualifiers_Val_Tbl_Type: Table of Qualifiers_Val_Rec_Type.

  • Pricing_Attr_Rec_Type: Corresponds to the columns in the pricing attributes table QP_ PRICING_ATTRIBUTES.

  • Pricing_Attr_Tbl_Type: Table of Pricing_Attr_Rec_Type.

  • Pricing_Attr_Val_Rec_Type: Attributes that store the meaning of id or code columns in the pricing attributes table QP_PRICING_ATTRIBUTES, for example, Accumulate.

  • Pricing_Attr_Val_Tbl_Type: Table of Pricing_Attr_Val_Rec_Type.

— R12 – OM – Script to insert Item into pricelist using qp_price_list_pub API
DECLARE

gpr_return_status VARCHAR2 (1) := NULL;
gpr_msg_count NUMBER := 0;
gpr_msg_data VARCHAR2 (2000);
gpr_price_list_rec qp_price_list_pub.price_list_rec_type;
gpr_price_list_val_rec qp_price_list_pub.price_list_val_rec_type;
gpr_price_list_line_tbl qp_price_list_pub.price_list_line_tbl_type;
gpr_price_list_line_val_tbl qp_price_list_pub.price_list_line_val_tbl_type;
gpr_qualifiers_tbl qp_qualifier_rules_pub.qualifiers_tbl_type;
gpr_qualifiers_val_tbl qp_qualifier_rules_pub.qualifiers_val_tbl_type;
gpr_pricing_attr_tbl qp_price_list_pub.pricing_attr_tbl_type;
gpr_pricing_attr_val_tbl qp_price_list_pub.pricing_attr_val_tbl_type;
ppr_price_list_rec qp_price_list_pub.price_list_rec_type;
ppr_price_list_val_rec qp_price_list_pub.price_list_val_rec_type;
ppr_price_list_line_tbl qp_price_list_pub.price_list_line_tbl_type;
ppr_price_list_line_val_tbl qp_price_list_pub.price_list_line_val_tbl_type;
ppr_qualifiers_tbl qp_qualifier_rules_pub.qualifiers_tbl_type;
ppr_qualifiers_val_tbl qp_qualifier_rules_pub.qualifiers_val_tbl_type;
ppr_pricing_attr_tbl qp_price_list_pub.pricing_attr_tbl_type;
ppr_pricing_attr_val_tbl qp_price_list_pub.pricing_attr_val_tbl_type;
k NUMBER := 1;
j NUMBER := 1;
BEGIN
— INITIALIZATION REQUIRED FOR R12
mo_global.set_policy_context (‘S’, 308);
mo_global.init(‘ONT’);
fnd_global.apps_initialize (user_id => 2083,
resp_id => 21623,
resp_appl_id => 660);
gpr_price_list_rec.list_header_id := 33019; — Enter the list_header_id from qp_list_headers
gpr_price_list_rec.NAME := ‘TST_PRICE_LIST’; — Enter the price list name
gpr_price_list_rec.list_type_code := ‘PRL’;
gpr_price_list_rec.description := ‘TEST PRICE LIST’; –Enter the price list Description
gpr_price_list_rec.operation := qp_globals.g_opr_update;
k := 1; — create the price list line rec

gpr_price_list_line_tbl (k).list_header_id := 33019; — Enter the list_header_id from qp_list_headers
gpr_price_list_line_tbl (k).list_line_id := fnd_api.g_miss_num;
gpr_price_list_line_tbl (k).list_line_type_code := ‘PLL’;
gpr_price_list_line_tbl (k).operation := qp_globals.g_opr_create;
gpr_price_list_line_tbl (k).operand := 10; –Enter the Unit Price
gpr_price_list_line_tbl (k).arithmetic_operator := ‘UNIT_PRICE’;
j := 1;
gpr_pricing_attr_tbl (j).pricing_attribute_id := fnd_api.g_miss_num;
gpr_pricing_attr_tbl (j).list_line_id := fnd_api.g_miss_num;
gpr_pricing_attr_tbl (j).product_attribute_context := ‘ITEM’;
gpr_pricing_attr_tbl (j).product_attribute := ‘PRICING_ATTRIBUTE1’;
gpr_pricing_attr_tbl (j).product_attr_value := ‘102785’; — Enter the inventory_item_id
gpr_pricing_attr_tbl (j).product_uom_code := ‘EA’; — Enter the UOM
gpr_pricing_attr_tbl (j).excluder_flag := ‘N’;
gpr_pricing_attr_tbl (j).attribute_grouping_no := 1;
gpr_pricing_attr_tbl (j).price_list_line_index := 1;
gpr_pricing_attr_tbl (j).operation := qp_globals.g_opr_create;

dbms_output.put_line(‘Calling qp_price_list_pub.process_price_list API to Enter Item Into Price List’);
dbms_output.put_line(‘=============================================’);
qp_price_list_pub.process_price_list
(p_api_version_number => 1,
p_init_msg_list => fnd_api.g_false,
p_return_values => fnd_api.g_false,
p_commit => fnd_api.g_false,
x_return_status => gpr_return_status,
x_msg_count => gpr_msg_count,
x_msg_data => gpr_msg_data,
p_price_list_rec => gpr_price_list_rec,
p_price_list_line_tbl => gpr_price_list_line_tbl,
p_pricing_attr_tbl => gpr_pricing_attr_tbl,
x_price_list_rec => ppr_price_list_rec,
x_price_list_val_rec => ppr_price_list_val_rec,
x_price_list_line_tbl => ppr_price_list_line_tbl,
x_price_list_line_val_tbl => ppr_price_list_line_val_tbl,
x_qualifiers_tbl => ppr_qualifiers_tbl,
x_qualifiers_val_tbl => ppr_qualifiers_val_tbl,
x_pricing_attr_tbl => ppr_pricing_attr_tbl,
x_pricing_attr_val_tbl => ppr_pricing_attr_val_tbl);
IF ppr_price_list_line_tbl.count > 0 THEN
FOR k in 1 .. ppr_price_list_line_tbl.count
LOOP
dbms_output.put_line(‘No Of Record Got Insterted=> ‘ k);
dbms_output.put_line(‘Return Status = ‘ ppr_price_list_line_tbl(k).return_status);
END LOOP;
END IF;
IF ppr_price_list_line_tbl(k).return_status = fnd_api.g_ret_sts_success THEN

Commit;
DBMS_OUTPUT.put_line (‘The Item has been successfully loaded into the price list’);
Else
Rollback;
DBMS_OUTPUT.put_line (‘The Item has not been loaded into the price list’);
end if;
FOR k in 1 .. gpr_msg_count
LOOP
gpr_msg_data := oe_msg_pub.get(
p_msg_index => k,
p_encoded => ‘F’);
dbms_output.put_line(‘The Error Message Due to which The Item has not been loaded to Price List ‘ k ‘ is: ‘ gpr_msg_data);
END LOOP;
END;
— R12 – OM-Script to delete item from Price list using QP_PRICE_LIST_PUB API
DECLARE

gpr_return_status VARCHAR2 (1) := NULL;
gpr_msg_count NUMBER := 0;
gpr_msg_data VARCHAR2 (2000);
gpr_price_list_rec qp_price_list_pub.price_list_rec_type;
gpr_price_list_val_rec qp_price_list_pub.price_list_val_rec_type;
gpr_price_list_line_tbl qp_price_list_pub.price_list_line_tbl_type;
gpr_price_list_line_val_tbl qp_price_list_pub.price_list_line_val_tbl_type;
gpr_qualifiers_tbl qp_qualifier_rules_pub.qualifiers_tbl_type;
gpr_qualifiers_val_tbl qp_qualifier_rules_pub.qualifiers_val_tbl_type;
gpr_pricing_attr_tbl qp_price_list_pub.pricing_attr_tbl_type;
gpr_pricing_attr_val_tbl qp_price_list_pub.pricing_attr_val_tbl_type;
ppr_price_list_rec qp_price_list_pub.price_list_rec_type;
ppr_price_list_val_rec qp_price_list_pub.price_list_val_rec_type;
ppr_price_list_line_tbl qp_price_list_pub.price_list_line_tbl_type;
ppr_price_list_line_val_tbl qp_price_list_pub.price_list_line_val_tbl_type;
ppr_qualifiers_tbl qp_qualifier_rules_pub.qualifiers_tbl_type;
ppr_qualifiers_val_tbl qp_qualifier_rules_pub.qualifiers_val_tbl_type;
ppr_pricing_attr_tbl qp_price_list_pub.pricing_attr_tbl_type;
ppr_pricing_attr_val_tbl qp_price_list_pub.pricing_attr_val_tbl_type;
k NUMBER := 1;
j NUMBER := 1;

BEGIN

oe_debug_pub.initialize;
oe_debug_pub.setdebuglevel (5);
oe_msg_pub.initialize;

DBMS_OUTPUT.put_line ( ‘Debug File = ‘ oe_debug_pub.g_dir ‘/’ oe_debug_pub.g_file);
— setup the list_header rec for update

gpr_price_list_rec.list_header_id := 33019; — Price List Header Id (List_header_id)
gpr_price_list_rec.NAME := ‘TST_PRICE_LIST’; — Price List Name
gpr_price_list_rec.list_type_code := ‘PRL’;
gpr_price_list_rec.description := ‘TEST PRICE LIST’; — Price List Description
gpr_price_list_rec.operation := qp_globals.g_opr_update;
— delete the price list line rec

gpr_price_list_line_tbl (k).list_header_id := 33019; — Price List Header Id (List_header_id)
gpr_price_list_line_tbl (k).list_line_id := 2003808; — Price List Line Id (List_Line_id)
gpr_price_list_line_tbl (k).list_line_type_code := ‘PLL’;
gpr_price_list_line_tbl (k).operation := qp_globals.g_opr_delete;
DBMS_OUTPUT.put_line(‘Calling qp_price_list_pub.process_price_list API to Delete Item From Price List’);
DBMS_OUTPUT.put_line(‘==============================================’);
qp_price_list_pub.process_price_list
(p_api_version_number => 1,
p_init_msg_list => fnd_api.g_false,
p_return_values => fnd_api.g_false,
p_commit => fnd_api.g_false,
x_return_status => gpr_return_status,
x_msg_count => gpr_msg_count,
x_msg_data => gpr_msg_data,
p_price_list_rec => gpr_price_list_rec,
p_price_list_line_tbl => gpr_price_list_line_tbl,
p_pricing_attr_tbl => gpr_pricing_attr_tbl,
x_price_list_rec => ppr_price_list_rec,
x_price_list_val_rec => ppr_price_list_val_rec,
x_price_list_line_tbl => ppr_price_list_line_tbl,
x_price_list_line_val_tbl => ppr_price_list_line_val_tbl,
x_qualifiers_tbl => ppr_qualifiers_tbl,
x_qualifiers_val_tbl => ppr_qualifiers_val_tbl,
x_pricing_attr_tbl => ppr_pricing_attr_tbl,
x_pricing_attr_val_tbl => ppr_pricing_attr_val_tbl);

IF ppr_price_list_line_tbl.COUNT > 0 THEN

FOR k IN 1 .. ppr_price_list_line_tbl.COUNT
LOOP
DBMS_OUTPUT.put_line(‘No Of Record Got Deleted=> ‘ k);
DBMS_OUTPUT.put_line(‘Return Status = ‘ ppr_price_list_line_tbl(k).return_status);
END LOOP;
END IF;
IF gpr_return_status = fnd_api.g_ret_sts_success THEN
Commit;
DBMS_OUTPUT.put_line (‘The Item Has Been Successfully Deleted from The Price List Using API’);
Else
Rollback;
RAISE fnd_api.g_exc_unexpected_error;
END IF;
FOR k IN 1 .. gpr_msg_count
LOOP
gpr_msg_data := oe_msg_pub.get (
p_msg_index => k,
p_encoded => ‘F’);
DBMS_OUTPUT.put_line (‘The Error Message’ k ‘ is: ‘ gpr_msg_data);
NULL;
END LOOP;
EXCEPTION
WHEN fnd_api.g_exc_error THEN
gpr_return_status := fnd_api.g_ret_sts_error;
WHEN fnd_api.g_exc_unexpected_error THEN
gpr_return_status := fnd_api.g_ret_sts_unexp_error;
FOR k IN 1 .. gpr_msg_count LOOP
gpr_msg_data := oe_msg_pub.get (
p_msg_index => k,
p_encoded => ‘F’);
DBMS_OUTPUT.put_line (‘The Error Message’ k ‘ is: ‘ gpr_msg_data);
NULL;
END LOOP;
WHEN OTHERS THEN
gpr_return_status := fnd_api.g_ret_sts_unexp_error;
END;

/by
, , , , , , , , , ,

Consigned POs not getting converted with the consigned flag checked through PDOI & Coming to the base tables with Incomplete Status

While trying to import the Consigned Purchase Orders using Standard Purchase Order Import Process, the Consigned Flag is not updated to Yes in the Purchase Order Shipment due to which the PO is coming to the system in INCOMPLETE status rather than APPROVED status.

If we will try to approve the PO in the form , then we will receive the following Error.

“Error: Line #1 Schedule # 1: The consigned setting on the shipment line does not match the consigned attribute on the ASL. The shipment line must be deleted and re-entered.”

The reason why the system is throwing the error while approving the PO is:

Approved Supplier List (ASL) for the item has the “Consigned from Supplier Flag” checked, while the corresponding PO Shipments does not have the Consigned Flag checked.
If the ASL / Item is consigned enabled, the Consigned Attributes must be passed on to the PO Shipment Line for the error to suppress

To Approve the PO, we need to perform either of the following Option.

– Deselect the “Consigned from Supplier” checkbox for the ASL if Consigned Inventory is not desired for the Supplier. (Check for the consigned_from_supplier_flag in po_asl_attributes table)

OR

– Ensure the Consigned flag is checked on the PO Shipment. (consigned_flag in po_line_locations_all should be set to Y).

What is the Solution if we need to import Consigned Purchase Orders in Bulk into the system.
We can not manully go & approve the PO one by one after taking care of the above 2 points.

As Per Oracle;

Is it possible to use the consigned flag while importing Purchase Orders ? — No, This functionality is not yet available.

Enhancement Request Bug 4261977 has been opened to request that this functionality and to be considered for inclusion in future versions of the application

Workaround which can be used to Import Consigned Purchase Orders:

  • Import the Purchase Order in Incomplete Status to the System using PDOI.
  • In the po_line_locations_all table, the consigned_flag should be NULL for these POs. We need to update this flag to Y. (update po_line_locations_all set consigned_flag = ‘Y’ where line_location_id = ***)
  • Need to call the PO approval API [po_reqapproval_init1.start_wf_process] so as to approve the PO.
/by
, , , , , , , , , ,

Use of inv_lot_api_pub API for Inserting & Auto Generating the Lot Numbers

A lot number is a combination of an alphanumeric prefix and a numeric suffix. When we define an item, we can specify the starting lot prefix and the starting lot number. Oracle Inventory uses this information to generate defaults during transaction entry.

1. Establish lot control for an item.

We can establish lot control for an item when We define it. We can choose from No control or Full control. If We choose lot control We must assign lot numbers when We receive the item into inventory. Thereafter, when We transact this item, We must provide a lot number We specified when We received the item.We can update lot control options for an item if it has zero on-hand quantity.
2 . Establish lot number uniqueness.

We use the Organization Parameters window to specify whether lot numbers should be unique for an item. If We do not establish lot number uniqueness, We can assign the same lot number to multiple items in the same organization and across organizations.

If We control lot number uniqueness at the Master level, We can assign a specific lot number only to one item in the same organization and across organizations. When We perform transactions, Oracle Inventory checks the lot number uniqueness control to generate lot number defaults.

3. Optionally, determine whether to automatically generate lot number defaults.

We use the Organization Parameters window to specify how to generate lot number defaults. We can choose to generate sequential lot numbers based on an alphanumeric prefix We specify when We define an item. Oracle Inventory can also generate lot number defaults for the entire organization. In this case, We must define a lot number prefix at the Organization level in the Organization Parameters window.

How to generate/insert lot numbers using inv_lot_api_pub.auto_gen_lot API?================================================================

— R12 – INV – Sample Script to Generate Lot Number using inv_lot_api_pub

DECLARE

l_chr_lot_number VARCHAR2 (50);
l_chr_return_status VARCHAR2 (2000);
l_num_msg_count NUMBER;
l_chr_msg_data VARCHAR2 (50);

Cursor c_item_info is

select * from mtl_system_items_kfv
where concatenated_segments = ‘TSTITEM^3M’ — Enter the item for which Lot Number needs to be created
and organization_id = 381; — Enter the organization_id

BEGIN

— initialization required for R12
mo_global.set_policy_context (‘S’, 308);
mo_global.init(‘INV’);

— Initialization for Organization_id
inv_globals.set_org_id (381);

— initialize environment
fnd_global.apps_initialize (user_id => 2083,
resp_id => 20634,
resp_appl_id => 401);

For i in c_item_info

LOOP
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 => i.organization_id,
p_inventory_item_id => i.inventory_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);

dbms_output.put_line (‘The Status Returned by the API is => ‘ l_chr_return_status);

IF l_chr_return_status = ‘S’
THEN
COMMIT;
ELSE
ROLLBACK;
END IF;

dbms_output.put_line (‘The Message Count Returned by the API is => ‘ l_num_msg_count);
dbms_output.put_line (‘The Message Returned by the API is => ‘ l_chr_return_status);
dbms_output.put_line (‘Lot Number Created for the item ‘ i.concatenated_segments ‘ is => ‘ l_chr_lot_number);

END LOOP;

END;

— R12 – INV – Sample Script to Insert Lot Number using inv_lot_api_pub

DECLARE
x_object_id NUMBER;
x_return_status VARCHAR2 (1);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (4000);
x_expire_date DATE;

Cursor c_item_info is

LOOP

select * from mtl_system_items_kfv
where concatenated_segments = ‘TSTITEM^3M’ — Enter the item for which Lot Number needs to be created
and organization_id = 381; — Enter the organization_id

BEGIN

— initialization required for R12
mo_global.set_policy_context (‘S’, 308);
mo_global.init(‘INV’);

— Initialization for Organization_id
inv_globals.set_org_id (381);

— initialize environment
fnd_global.apps_initialize (user_id => 2083,
resp_id => 20634,
resp_appl_id => 401);

For i in c_item_info

dbms_output.put_line (‘Calling inv_lot_api_pub.auto_gen_lot API to Create Lot Numbers’);
dbms_output.put_line (‘*********************************************’);

inv_lot_api_pub.insertlot
(p_api_version => 1,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
p_validation_level => fnd_api.g_valid_level_full,
p_inventory_item_id => i.inventory_item_id,
p_organization_id => i.organization_id,
p_lot_number => ‘A6644001’,
p_expiration_date => x_expire_date,
x_object_id => x_object_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);

dbms_output.put_line (‘The Status Returned by the API is => ‘x_return_status);

IF x_return_status = fnd_api.g_ret_sts_success THEN
COMMIT;
ELSE
ROLLBACK;
END IF;

DBMS_OUTPUT.put_line (‘x_object_id :’ x_object_id);
DBMS_OUTPUT.put_line (‘x_msg_count :’ x_msg_count);
DBMS_OUTPUT.put_line (‘x_msg_data :’ x_msg_data);

END LOOP;
END;

/by
, , , , , , , , , ,

To Check for the available onhand For An Item in a Given Organization Using Inv_Quantity_Tree_Pub API

Inv_Quantity_Tree_Pub API can be used for querying the available Onhand in a given subinventory or organization.
The output of the API inv_quantity_tree_pub.query_quantities will show the Total Available Onhand , Reservations , Suggestions and the Actual Onhand that can be Transacted.
— R12 – INV – Sample Script to Get Onhand Using INV_Quantity_Tree_PUB API:DECLARE

l_api_return_status VARCHAR2 (1);
l_qty_oh NUMBER;
l_qty_res_oh NUMBER;
l_qty_res NUMBER;
l_qty_sug NUMBER;
l_qty_att NUMBER;
l_qty_atr NUMBER;
l_msg_count NUMBER;
l_msg_data VARCHAR2 (1000);
v_item VARCHAR2 (250) := ‘&Item_Num’;
v_org VARCHAR2 (10) := ‘&Org_code’;

Cursor c_item_info is

SELECT concatenated_segments item, msi.inventory_item_id,msi.organization_id, mp.organization_code
FROM mtl_system_items_kfv msi, mtl_parameters mp
WHERE concatenated_segments = v_item
AND msi.organization_id = mp.organization_id
AND mp.organization_code = v_org;

BEGIN

inv_quantity_tree_grp.clear_quantity_cache;

DBMS_OUTPUT.put_line (‘Transaction Mode’);

For i in c_item_info

LOOP

DBMS_OUTPUT.put_line (‘Extracting the Onhand For the Item ===========> ‘ i.item);
DBMS_OUTPUT.put_line (‘Extracting the Onhand For the Organization ======> ‘ i.organization_code);

apps.inv_quantity_tree_pub.query_quantities
(p_api_version_number => 1.0,
p_init_msg_lst => apps.fnd_api.g_false,
x_return_status => l_api_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_organization_id => i.organization_id,
p_inventory_item_id => i.inventory_item_id,
p_tree_mode => apps.inv_quantity_tree_pub.g_transaction_mode,
p_onhand_source => 3,
p_is_revision_control => FALSE,
p_is_lot_control => FALSE,
p_is_serial_control => FALSE,
p_revision => NULL,
p_lot_number => NULL,
p_subinventory_code => NULL,
p_locator_id => NULL,
x_qoh => l_qty_oh,
x_rqoh => l_qty_res_oh,
x_qr => l_qty_res,
x_qs => l_qty_sug,
x_att => l_qty_att,
x_atr => l_qty_atr);

DBMS_OUTPUT.put_line (‘Quantity on hand ======================> ‘ TO_CHAR (l_qty_oh));
DBMS_OUTPUT.put_line (‘Reservable quantity on hand ===============> ‘ TO_CHAR (l_qty_res_oh));
DBMS_OUTPUT.put_line (‘Quantity reserved =====================> ‘ TO_CHAR (l_qty_res));
DBMS_OUTPUT.put_line (‘Quantity suggested ====================> ‘ TO_CHAR (l_qty_sug));
DBMS_OUTPUT.put_line (‘Quantity Available To Transact ==============> ‘ TO_CHAR (l_qty_att));
DBMS_OUTPUT.put_line (‘Quantity Available To Reserve ==============> ‘ TO_CHAR (l_qty_atr));

END LOOP;
END;

DBMS OUTPUT:

Transaction Mode
Extracting the Onhand For the Item =========> ELXV350ELL121MH12D^NIPPONSCC
Extracting the Onhand For the Organization ====> A66
Quantity on hand =======================> 3400
Reservable quantity on hand ===============> 3400
Quantity reserved =======================> 1000
Quantity suggested ======================> 0
Quantity Available To Transact==============> 2400
Quantity Available To Reserve ==============> 2400

/by
, , , , , , , , ,

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

/by