SOA, Oracle Apps and OAF World: Purchasing

Showing posts with label Purchasing. Show all posts

Wednesday, 28 May 2014

Cancel PO Line API Script: Oracle EBS R12

Oracle has provided a standard API to cancel PO Line/Lines "po_document_control_pub.control_document".

Below mentioned is the sample script using which we can cancel single/multiple Purchase Order Lines.

Step:1 Create a object type to store multiple purchase order lines.

create or replace TYPE po_line_type AS TABLE OF NUMBER;

Step:2 Run the below script to cancel Purchase order line/lines.

SET serveroutput ON

DECLARE

v_error_flag NUMBER:=0;

v_po_line_id po_line_type;

v_po_hdr_id NUMBER;

v_error_msg VARCHAR2(1000);

l_return_status VARCHAR2(1000);

l_msg_data VARCHAR2(1000);

BEGIN

-------Fetching Po Header Id-----

BEGIN

SELECT po_header_id INTO v_po_hdr_id

FROM apps.po_headers_all WHERE segment1='52765';

exception

WHEN no_data_found THEN

v_error_flag:=1;

v_error_msg:='Purchase order not found (ND)';

WHEN others THEN

v_error_flag:=1;

v_error_msg:='Purchase order not found (OTH)'||sqlcode||' '||sqlerrm;

END;

IF(v_error_flag <> 1) THEN

-------Fetching Po Line Id-----

SELECT po_line_id BULK COLLECT

INTO v_po_line_id

FROM apps.po_lines_all

WHERE po_header_id=v_po_hdr_id;

-----------------INITIALIZING APPS ---------------------

apps.mo_global.init ('PO');

apps.mo_global.set_policy_context ('S',204);

apps.fnd_global.apps_initialize ( user_id => 1319, resp_id => 50578, resp_appl_id => 201 );

FOR i IN v_po_line_id.FIRST ..v_po_line_id.LAST

loop

dbms_output.put_line('lien'||v_po_line_id(i));

apps.po_document_control_pub.control_document (1.0, -- P_API_VERSION

apps.fnd_api.g_true, -- P_INIT_MSG_LIST

apps.fnd_api.g_true, -- P_COMMIT

l_return_status, -- X_RETURN_STATUS

'PO', -- P_DOC_TYPE

'STANDARD', -- P_DOC_SUBTYPE

NULL, -- P_DOC_ID

'52765', -- P_DOC_NUM

NULL, -- P_RELEASE_ID

NULL, -- P_RELEASE_NUM

v_po_line_id(i), -- P_DOC_LINE_ID

NULL, -- P_DOC_LINE_NUM

NULL, -- P_DOC_LINE_LOC_ID

NULL, -- P_DOC_SHIPMENT_NUM

'CANCEL', -- P_ACTION

SYSDATE, -- P_ACTION_DATE

NULL, -- P_CANCEL_REASON

'N', -- P_CANCEL_REQS_FLAG

NULL, -- P_PRINT_FLAG

NULL, -- P_NOTE_TO_VENDOR

'N' ); --P_USE_GLDATE

COMMIT;

END loop;

dbms_output.put_line('Status '||l_return_status);

------------------FOR ERROR ---------------

IF l_return_status != 'S' THEN

FOR err_code IN 1 .. apps.fnd_msg_pub.count_msg

loop

l_msg_data := substr (apps.fnd_msg_pub.get (p_msg_index => 1,

p_encoded => 'F'), 1, 200 );

dbms_output.put_line('Error Status'||l_msg_data);

END loop;

END IF;

ELSE

dbms_output.put_line('Error'||v_error_msg);

END IF;

END;

Tuesday, 27 May 2014

Return to Vendor of Purchase Order Receipts Script: Oracle EBS R12

Return to Vendor is done in two steps: "Return to Receiving" and then "Return to Vendor".
Follow below steps to return the received goods to vendor.

Step:1 Run the following script to find the necessary information to be inserted in RCV_TRANSACTIONS_INTERFACE table.

SELECT rsh.receipt_num ,
ph.segment1 po_number,
rt.transaction_id ,
rt.transaction_type ,
rt.transaction_date ,
rt.quantity ,
rt.unit_of_measure ,
rt.shipment_header_id ,
rt.shipment_line_id ,
rt.source_document_code ,
rt.destination_type_code ,
rt.employee_id ,
rt.parent_transaction_id ,
rt.po_header_id ,
rt.po_line_id ,
pl.line_num ,
pl.item_id ,
pl.unit_price ,
rt.po_line_location_id ,
rt.po_distribution_id ,
rt.routing_header_id,
rt.routing_step_id ,
rt.deliver_to_person_id ,
rt.deliver_to_location_id ,
rt.vendor_id ,
rt.vendor_site_id ,
rt.organization_id ,
rt.subinventory ,
rt.locator_id ,
rt.location_id,
rsh.ship_to_org_id
FROM apps.rcv_transactions rt,
apps.rcv_shipment_headers rsh,
apps.po_headers_all ph,
apps.po_lines_all pl
WHERE rsh.receipt_num = '9428'
AND ph.segment1 = '52762'
AND ph.po_header_id = pl.po_header_id
AND rt.po_header_id = ph.po_header_id
AND rt.shipment_header_id = rsh.shipment_header_id
AND rt.po_line_id =pl.po_line_id
AND rt.destination_type_code='INVENTORY'
AND rt.transaction_type='DELIVER';

Step:2 Return from Deliver to Receiving i.e Return to Receiving

INSERT INTO apps.rcv_transactions_interface
(interface_transaction_id,
group_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
transaction_type,
transaction_date,
processing_status_code,
processing_mode_code,
transaction_status_code,
quantity,
unit_of_measure,
item_id,
employee_id,
shipment_header_id,
shipment_line_id,
receipt_source_code,
vendor_id,
from_organization_id,
from_subinventory,
from_locator_id,
source_document_code,
parent_transaction_id,
po_header_id,
po_line_id,
po_line_location_id,
po_distribution_id,
destination_type_code,
deliver_to_person_id,
location_id,
deliver_to_location_id,
validation_flag
)
VALUES
(apps.rcv_transactions_interface_s.nextval, --INTERFACE_TRANSACTION_ID
apps.rcv_interface_groups_s.nextval, --GROUP_ID
SYSDATE, --LAST_UPDATE_DATE
1318, --LAST_UPDATE_BY
SYSDATE, --CREATION_DATE
1318, --CREATED_BY
0, --LAST_UPDATE_LOGIN
'RETURN TO RECEIVING', --TRANSACTION_TYPE
SYSDATE, --TRANSACTION_DATE
'PENDING', --PROCESSING_STATUS_CODE
'BATCH', --PROCESSING_MODE_CODE
'PENDING', --TRANSACTION_STATUS_CODE
25, --QUANTITY
'METRICTON', --UNIT_OF_MEASURE
  208955, --ITEM_ID
25, --EMPLOYEE_ID
5048067, --SHIPMENT_HEADER_ID
5035611, --SHIPMENT_LINE_ID
'VENDOR', --RECEIPT_SOURCE_CODE
557, --VENDOR_ID
204, --FROM_ORGANIZATION_ID
'Stores', --FROM_SUBINVENTORY
null, --FROM_LOCATOR_ID
'PO', --SOURCE_DOCUMENT_CODE
5090687, --TRANSACTION_ID
165864, --PO_HEADER_ID
232886, --PO_LINE_ID
323424, --PO_LINE_LOCATION_ID
329884, --PO_DISTRIBUTION_ID
'INVENTORY', --DESTINATION_TYPE_CODE
null, --DELIVER_TO_PERSON_ID
NULL, --LOCATION_ID
null, --DELIVER_TO_LOCATION_ID
'Y' --Validation_flag

);
commit;

Step:3 Submit the Receiving Transaction Processor concurrent program

SET serveroutput ON
DECLARE
v_request_id NUMBER;
BEGIN
apps.mo_global.init ('PO');
apps.mo_global.set_policy_context ('S',204);
apps.fnd_global.apps_initialize ( user_id => 1318, resp_id => 50578, resp_appl_id => 201 );
--------CALLING STANDARD RECEIVING TRANSACTION PROCESSOR ---------------------------------

v_request_id := apps.fnd_request.submit_request ( application => 'PO',
PROGRAM => 'RVCTP',
argument1 => 'BATCH',
argument2 => apps.rcv_interface_groups_s.currval,
argument3 => 204);
commit;
dbms_output.put_line('Request Id'||v_request_id);
END;

Step:4 Run the script to check data is inserted in proper way in rcv_transactions.

SELECT * FROM apps.rcv_transactions WHERE po_header_id=165864;

Step:5 Once the goods are transferred to receiving destination return to vendor.

INSERT INTO apps.rcv_transactions_interface
(interface_transaction_id,
group_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
transaction_type,
transaction_date,
processing_status_code,
processing_mode_code,
transaction_status_code,
quantity,
unit_of_measure,
item_id,
employee_id,
shipment_header_id,
shipment_line_id,
receipt_source_code,
vendor_id,
from_organization_id,
from_subinventory,
from_locator_id,
source_document_code,
parent_transaction_id,
po_header_id,
po_line_id,
po_line_location_id,
po_distribution_id,
destination_type_code,
deliver_to_person_id,
location_id,
deliver_to_location_id,
validation_flag
)
values
(apps.rcv_transactions_interface_s.nextval, --INTERFACE_TRANSACTION_ID
apps.rcv_interface_groups_s.nextval, --GROUP_ID
sysdate, --LAST_UPDATE_DATE
1318, --LAST_UPDATE_BY
sysdate, --CREATION_DATE
1318, --CREATED_BY
0, --LAST_UPDATE_LOGIN
'RETURN TO VENDOR', --TRANSACTION_TYPE
sysdate, --TRANSACTION_DATE
'PENDING', --PROCESSING_STATUS_CODE
'BATCH', --PROCESSING_MODE_CODE
'PENDING', --TRANSACTION_STATUS_CODE
25, --QUANTITY
'METRICTON', --UNIT_OF_MEASURE
208955, --ITEM_ID
25, --EMPLOYEE_ID
5048067, --SHIPMENT_HEADER_ID
5035611, --SHIPMENT_LINE_ID
'VENDOR', --RECEIPT_SOURCE_CODE
  557, --VENDOR_ID
204, --FROM_ORGANIZATION_ID
'Stores', --FROM_SUBINVENTORY
null, --FROM_LOCATOR_ID
'PO', --SOURCE_DOCUMENT_CODE
5090686, --PARENT_TRANSACTION_ID
165864, --PO_HEADER_ID
232886, --PO_LINE_ID
  323424, --PO_LINE_LOCATION_ID
329884, --PO_DISTRIBUTION_ID
'RECEIVING', --DESTINATION_TYPE_CODE
null, --DELIVER_TO_PERSON_ID
null, --LOCATION_ID
null, --DELIVER_TO_LOCATION_ID
'Y' --Validation_flag
);
commit;

Step:6 Submit the Receiving Transaction Processor concurrent program

SET serveroutput ON
DECLARE
v_request_id NUMBER;
BEGIN
apps.mo_global.init ('PO');
apps.mo_global.set_policy_context ('S',204);
apps.fnd_global.apps_initialize ( user_id => 1318, resp_id => 50578, resp_appl_id => 201 );
--------CALLING STANDARD RECEIVING TRANSACTION PROCESSOR ---------------------------------

v_request_id := apps.fnd_request.submit_request ( application => 'PO',
PROGRAM => 'RVCTP',
argument1 => 'BATCH',
argument2 => apps.rcv_interface_groups_s.currval,
argument3 => 204);
commit;
dbms_output.put_line('Request Id'||v_request_id);
END;

Step:7 Run the script to check data is inserted in proper way in rcv_transactions.

SELECT * FROM apps.rcv_transactions WHERE po_header_id=165864;

Monday, 26 May 2014

Blanket Purchase Order Import Program Script: Oracle EBS R12

To create Blanket Purchase Order(PO) using Oracle standard API please follow below steps.

Step:1 Validate and Insert PO header and line details in PO_HEADERS_INTERFACE and PO_LINES_INTERFACE tables respectively.

SET serveroutput ON
DECLARE
p_supplier VARCHAR2(1000):='Staples';
p_supplier_site VARCHAR2(1000):='STAPLES LA';
p_ship_to_location VARCHAR2(1000):='V1- New York City';
p_bill_to_location VARCHAR2(1000):='V1- New York City';
p_currency VARCHAR2(1000):='USD';
p_buyer VARCHAR2(1000):='Stock, Ms. Pat';
p_item VARCHAR2(1000):='Oil';
p_unit_of_measure VARCHAR2(1000):='METRICTON';
p_unit_price number:=1;
v_org_name VARCHAR2(30) := 'Vision Operations';
v_dest_code VARCHAR2(30) := 'INVENTORY';
v_error_flag NUMBER:= 0;
v_main_error VARCHAR2(1000):= NULL;
v_currency VARCHAR2(1000);
v_agent_id number;
v_org_id NUMBER;
v_vendor_id NUMBER;
v_vendor_site_id NUMBER;
v_ship_to_location_id NUMBER;
v_bill_to_location_id NUMBER;
v_item_id NUMBER;
v_uom_code VARCHAR2(1000);
v_intf_dist_id NUMBER;
v_intf_line_id NUMBER;
v_intf_header_id NUMBER;
v_batch_id NUMBER;
v_line_type_id NUMBER;

BEGIN
-----------VALIDATING THE PRICE CURRENCY CODE --------------
BEGIN
SELECT currency_code
into v_currency
FROM apps.fnd_currencies
WHERE enabled_flag = 'Y'
AND currency_flag = 'Y'
AND upper(currency_code) = upper(trim(p_currency));
EXCEPTION
WHEN no_data_found THEN
v_error_flag := 1;
v_main_error := v_main_error||'PRICE CURRENCY NOT EXIST IN EBS (WHEN ND) ';
WHEN OTHERS THEN
v_error_flag := 1;
v_main_error := v_main_error||' ERROR WHILE VALIDATING CURRENCY CODE(WHEN OTH) '||' '||SQLCODE||':'||sqlerrm ;
END;
--------- --VALIDATING AGENT AND FETCHING THE AGENT_ID-------------------------
BEGIN
SELECT ppf.person_id
INTO v_agent_id
FROM per_people_f ppf
WHERE ppf.full_name = p_buyer
and rownum=1;
EXCEPTION
WHEN no_data_found THEN
v_error_flag := 1;
v_main_error := v_main_error||'AGENT ID/TRADER PERSON NUMBER NOT EXIST IN EBS(WHEN ND) ';
WHEN OTHERS THEN
v_error_flag := 1;
v_main_error := v_main_error|| 'ERROR WHILE VALIDATING AGENT ID / TRADER PERSON NUMBER(WHEN OTH)-'|| SQLCODE ||':'||sqlerrm;
END;
---------VALIDATING ORAGANIZATION AND FETCHING ORANIZATION ID-----------------
BEGIN
SELECT organization_id
INTO v_org_id
FROM apps.hr_operating_units
WHERE NAME = v_org_name
and rownum=1;
EXCEPTION
WHEN no_data_found THEN
v_error_flag := 1;
v_main_error := v_main_error||'ORGANIZATION ID/INTERNAL COMPANY NUMBER NOT FOUND IN EBS(WHEN ND) '||v_org_name;
WHEN OTHERS THEN
v_error_flag := 1;
v_main_error := v_main_error||' ERROR WHILE VALIDATING ORGANIZATION ID/INTERNAL COMPANY NUMBER (WHEN OTH)-'||v_org_name||' '||SQLCODE||' '|| sqlerrm;
END;
---------------------VALIDATING THE VENDOR NAME AND FETCHING VENDOR_ID----------------------
BEGIN
SELECT vendor_id
INTO v_vendor_id
FROM apps.po_vendors
WHERE upper(vendor_name) = upper(p_supplier);
EXCEPTION
WHEN no_data_found THEN
v_error_flag := 1;
v_main_error := v_main_error||'VENDOR ID/COUNTERPART COMPANY NUMBER NOT EXISTS IN EBS (WHEN ND) '||p_supplier;
WHEN OTHERS THEN
v_error_flag := 1;
v_main_error := v_main_error||' ERROR WHILE VALIDATING VENDOR ID/COUNTERPART COMPANY NUMBER (WHEN OTH) '||p_supplier||' '||SQLCODE||' '||sqlerrm;
END;
---------------------VALIDATING THE VENDOR SITE ID----------------------------
BEGIN
SELECT vendor_site_id
INTO v_vendor_site_id
FROM apps.po_vendor_sites_all
WHERE upper(vendor_site_code) = upper(p_supplier_site)
AND vendor_id = v_vendor_id
AND org_id = v_org_id;
EXCEPTION
WHEN no_data_found THEN
v_error_flag := 1;
v_main_error := v_main_error||'VENDOR SITE ID NOT EXIST IN EBS (WHEN ND) '||p_supplier_site;
WHEN OTHERS THEN
v_error_flag := 1;
v_main_error := v_main_error||' ERROR WHILE VALIDATING VENDOR SITE ID(WHEN OTH)-'||p_supplier_site||' '||SQLCODE||':'|| sqlerrm;
END;
--------VALIDATING THE SHIP TO LOCATION AND FETCHING SHIP_TO_LOCATION_ID-------------
BEGIN
SELECT location_id
INTO v_ship_to_location_id
FROM hr_locations_all
WHERE location_code = p_ship_to_location;
EXCEPTION
WHEN no_data_found THEN
v_error_flag := 1;
v_main_error := v_main_error||'SHIP TO LOCATION ID/DESTINATION LOCATION NUMBER NOT FOUND IN EBS (WHEN ND)-'||p_ship_to_location;
WHEN OTHERS THEN
v_error_flag := 1;
v_main_error := v_main_error||'ERROR WHILE VALIDATING SHIP TO LOCATION ID/DESTINATION LOCATION NUMBER (WHEN OTH)'||p_ship_to_location||' '||SQLCODE||':'||sqlerrm;
END;
-------------------VALIDATING THE BILL TO LOCATION AND FETCHING THE BILL_TO_LOCATION_ID--------------------------
BEGIN
SELECT location_id
INTO v_bill_to_location_id
FROM hr_locations_all
WHERE location_code = p_bill_to_location ;
EXCEPTION
WHEN no_data_found THEN
v_error_flag := 1;
v_main_error := v_main_error||' BILL TO LOCATION ID NOT EXISTS IN EBS (WHEN ND) '||p_bill_to_location;
WHEN OTHERS THEN
v_error_flag := 1;
v_main_error := v_main_error||' ERROR WHILE VALIDATING BILL TO LOCATION ID(WHEN OTH) '||p_bill_to_location||':'||SQLCODE||':'||sqlerrm;
END;
----------------VALIDATING THE ITEM AND FETCHING ITEM ID----------------------
BEGIN
SELECT inventory_item_id
INTO v_item_id
FROM apps.mtl_system_items
WHERE enabled_flag = 'Y'
AND SYSDATE > TRUNC(NVL(start_date_active, SYSDATE -1))
AND SYSDATE < TRUNC(NVL(end_date_active, SYSDATE + 1))
AND segment1 = p_item
AND organization_id = v_org_id;
EXCEPTION
WHEN no_data_found THEN
v_error_flag := 1;
v_main_error := v_main_error||'ITEM ID/COMODITY NOT FOUND IN EBS PLEASE CHECK FOR REFERENCE DATA MAPPING FOR TRADE ';
WHEN OTHERS THEN
v_error_flag := 1;
v_main_error := v_main_error||'ERROR WHILE VALIDATING ITEM ID/COMODITY FOR TRADE '||p_item||' '||SQLCODE||' '||sqlerrm;
END;
------------------- VALIDATE UNIT_PRICE---------------------
BEGIN
IF(NVL(p_unit_price,0) < 1) THEN
v_error_flag := 1;
v_main_error := v_main_error||' UNIT_PRICE CANNOT BE LESS THAN ZERO OR NULL FOR OBLIGATION ';
END IF;
END;
-------------- VALIDATE UNIT_OF_MEASURE------------------------
BEGIN
SELECT uom_code
INTO v_uom_code
FROM apps.mtl_units_of_measure
WHERE SYSDATE < TRUNC(NVL(disable_date, SYSDATE + 1))
AND upper(unit_of_measure) = upper(p_unit_of_measure);
EXCEPTION
WHEN no_data_found THEN
v_error_flag := 1;
v_main_error := v_main_error||'NO UOM FOUND IN EBS FOR UOM CODE '||p_unit_of_measure;
WHEN too_many_rows THEN
v_error_flag := 1;
v_main_error := v_main_error||'MULTIPLE UOM CODE FOUND IN EBS FOR UOM '||p_unit_of_measure;
WHEN OTHERS THEN
v_error_flag := 1;
v_main_error := v_main_error||' ERROR WHILE FETCHING UOM CODE '||p_unit_of_measure||':'||SQLCODE||':'||sqlerrm;
END;
------------ GETTING SEQUESNCE VALUE FOR INTERFACE HEADER ID --------
SELECT apps.po_headers_interface_s.nextval
INTO v_intf_header_id
FROM dual;
-----------GENERATING BATCH ID--------------------
SELECT TO_CHAR(SYSDATE,'DDMMYYHHMMSS')
INTO v_batch_id
FROM dual;
------GETTING INTERFACE LINE ID FROM LINE INTERFACE SEQUENCE----------------
BEGIN
SELECT apps.po_lines_interface_s.nextval INTO v_intf_line_id FROM dual;
EXCEPTION
WHEN no_data_found THEN
v_error_flag := 1;
v_main_error := v_main_error||' Error In Getting Value From Sequence For Line Id ';
WHEN OTHERS THEN
v_error_flag := 1;
v_main_error := v_main_error||' Error In Getting Value From Sequence For Line Id '||SQLCODE||':'||sqlerrm;
END;
-------------Getting Line Type Id for CM line Type --------------
BEGIN
SELECT line_type_id
INTO v_line_type_id
FROM apps.po_line_types_v
WHERE NVL(inactive_date,SYSDATE+1) > SYSDATE
AND line_type = 'Goods';
EXCEPTION
WHEN no_data_found THEN
v_error_flag := 1;
v_main_error := 'ERROR WHILE FETCHING LINE TYPE ID(WHEN ND)-';
WHEN OTHERS THEN
v_error_flag := 1;
v_error_flag := v_error_flag||' ERROR WHILE FETCHING LINE TYPE ID(WHEN OTH) '||SQLCODE||':'||sqlerrm;
END;
----------- GETTING INTERFACE DISTRIBTION ID FROM DISTRIBUTION INTERFACE SEQUENCE ----------------
BEGIN
SELECT apps.po_distributions_interface_s.nextval
INTO v_intf_dist_id
FROM dual;
EXCEPTION
WHEN no_data_found THEN
v_error_flag := 1;
v_main_error := v_main_error||' ERROR IN GETTING VALUE FROM SEQUENCE FOR DISTRIBUTION ID ';
WHEN OTHERS THEN
v_error_flag := 1;
v_main_error := v_main_error||'ERROR IN GETTING VALUE FROM SEQUENCE FOR DISTRIBUTION ID '||SQLCODE||':'||sqlerrm;
END;
dbms_output.put_line(v_main_error);
--===========================Inserting Records IN Interface Table ==================================-----------------
IF v_error_flag <> 1 THEN
BEGIN
INSERT
INTO apps.po_headers_interface
(
interface_header_id,
action,
process_code,
document_type_code,
batch_id,
document_num,
currency_code,
approval_required_flag,
approval_status,
vendor_id,
terms_id,
fob,
org_id,
agent_id,
effective_date,
vendor_site_id,
ship_to_location_id,
bill_to_location_id
)
VALUES
(
v_intf_header_id, -- INTERFACE_HEADER_ID
'ORIGINAL', -- ACTION
'PENDING' , -- PROCESS_CODE
'BLANKET', -- DOCUMENT_TYPE_CODE
v_batch_id,
NULL, -- DOCUMENT_NUM
v_currency, -- CURRENCY_CODE
'Y', -- APPROVAL_REQUIRED_FLAG
'APPROVED', -- APPROVAL_STATUS
v_vendor_id, -- VENDOR_ID
NULL, -- V_PAYEMENT_TERM_ID,
NULL, -- V_DELIVERY_TERM,
v_org_id, -- ORG_ID
v_agent_id, -- AGENT_ID
to_date(SYSDATE), -- EFFECTIVE_DATE
v_vendor_site_id, -- VENDOR_SITE_ID
v_ship_to_location_id, -- SHIP_TO_LOCATION_ID
v_bill_to_location_id -- BILL_TO_LOCATION_ID
);
IF SQL%notfound THEN
v_error_flag := 1;
v_main_error := v_main_error||' '||'Record Not Inserted in Header Interface Table';
ELSE
dbms_output.put_line('Inserted Records In Header Interface Table');
END IF;
IF v_error_flag <> 1 THEN
BEGIN
INSERT
INTO apps.po_lines_interface
(
interface_line_id,
interface_header_id,
line_num,
uom_code,
item_id,
unit_price,
need_by_date,
promised_date,
line_type_id,
ship_to_organization_id,
ship_to_location,
note_to_receiver
)
VALUES
(
v_intf_line_id, --INTERFACE_LINE_ID
v_intf_header_id, --INTERFACE_HEADER_ID
NULL, --LINE_NUM
v_uom_code, --UNIT_OF_MEASURE
v_item_id, --ITEM_ID
p_unit_price, --UNIT_PRICE
SYSDATE, --NEED_BY_DATE
SYSDATE, --PROMISED_DATE
v_line_type_id, --LINE_TYPE_ID
NULL, --SHIP_TO_ORGANIZATION_ID
v_ship_to_location_id, --SHIP_TO_LOCATION
NULL --NOTE_TO_RECEIVER
);
IF SQL%notfound THEN
v_error_flag := 1;
v_main_error := v_main_error||' '||'Record Not Inserted in Lines Interface Table';
ELSE
dbms_output.put_line('Inserted Records In Lines Interface Table');
END IF;
END;
END IF;
END;
END IF;
commit;
END;

Step:2 Once the records are inserted in PO header and line interface tables run the API Import Price Catalogs(POXPDOI) to import Blanket Purchase Order.

SET serveroutput ON

DECLARE

v_request_id NUMBER;

v_org_id NUMBER := 204;

BEGIN

apps.mo_global.init ('PO');

apps.mo_global.set_policy_context ('S', 204);

apps.fnd_global.apps_initialize ( user_id => 1318, resp_id => 50578, resp_appl_id => 201 );

v_request_id :=apps.fnd_request.submit_request

('PO' ,

'POXPDOI' ,

'Import Price Catalogs' ,

SYSDATE ,

FALSE ,

NULL ,

'Blanket' ,

NULL ,

'N' ,

'APPROVED' ,

NULL ,

v_org_id, -- operating unit ID

'N' ,

NULL ,

NULL );

dbms_output.put_line(v_request_id);

commit;

END;

Step:3 Once concurrent program gets completed you can query the Blanket PO.