Creation of Purchase Orders through Interface

-- ---------------------------------------------------------------------------------
-- Purpose : Purchase Order Validations in the Staging Table
-- Date      : 26.May.2009
-- Created by : Chaitanya
-- .....
-- ----------------------------------------------------------------------------------


CREATE OR REPLACE PROCEDURE xx_po_validations(errbuf OUT VARCHAR2, retcode OUT VARCHAR2)
AS
l_count NUMBER(2);
l_subinv_name VARCHAR2(100):=NULL;
CURSOR c_po_int_valid IS
SELECT *
FROM xx_tab_po_stage
WHERE NVL(row_status,'T') IN ('E','T','R')
FOR UPDATE;
BEGIN
BEGIN
FOR c_po_int_valid_rec IN c_po_int_valid
LOOP
l_count:=0;
BEGIN
SELECT COUNT(*) INTO l_count
FROM po_headers_all ph
,po_lines_all pl
WHERE pl.po_header_id=ph.po_header_id
AND UPPER(ph.attribute1)=UPPER(c_po_int_valid_rec.PO_HEADER_REFERENCE);
IF l_count>0 THEN
UPDATE xx_tab_po_stage
SET row_status='E'
,error_message='Header and line combination is already existing.'
WHERE CURRENT OF c_po_int_valid;
ELSE
BEGIN
SELECT COUNT(*) INTO l_count
FROM po_vendors
WHERE UPPER(segment1)=UPPER(c_po_int_valid_rec.supplier_code)
AND end_date_active IS NULL;
IF l_count<>1 THEN
UPDATE xx_tab_po_stage
SET row_status='E'
,error_message='Invalid supplier code.'
WHERE CURRENT OF c_po_int_valid;
ELSE
UPDATE xx_tab_po_stage
SET row_status='V'
,supplier_id=(SELECT vendor_id
FROM po_vendors
WHERE UPPER(segment1)=UPPER(c_po_int_valid_rec.supplier_code))
--AND end_date_active IS NULL)
,supplier_site_code=(SELECT max(vendor_site_code)
FROM po_vendor_sites_all
WHERE vendor_id=(SELECT vendor_id
FROM po_vendors
WHERE UPPER(segment1)=UPPER(c_po_int_valid_rec.supplier_code)
)--AND end_date_active IS NULL)
AND inactive_date IS NULL)
WHERE CURRENT OF c_po_int_valid;
END IF;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,SQLERRM());
END;
BEGIN
SELECT COUNT(*) INTO l_count
FROM org_organization_definitions
WHERE disable_date IS NULL
AND organization_id=c_po_int_valid_rec.inventory_organization_id;
IF l_count<>1 THEN
UPDATE xx_tab_po_stage
SET row_status='E'
,error_message=error_message||' '||'Invalid inventory organization id.'
WHERE CURRENT OF c_po_int_valid;
ELSE
UPDATE xx_tab_po_stage
SET ship_to_location_id=(SELECT ship_to_location_id
FROM hr_locations_all
WHERE inventory_organization_id=c_po_int_valid_rec.inventory_organization_id
AND inactive_date IS NULL)
,bill_to_location_id=(SELECT location_id
FROM hr_locations_all
WHERE inventory_organization_id=c_po_int_valid_rec.inventory_organization_id
AND inactive_date IS NULL)
WHERE CURRENT OF c_po_int_valid;
END IF;
EXCEPTION
WHEN OTHERS THEN
UPDATE xx_tab_po_stage
SET row_status='E'
,error_message=error_message||' '||'Error while deriving ship to and bill to locations for the given organization id.'
WHERE CURRENT OF c_po_int_valid;
FND_FILE.PUT_LINE(FND_FILE.LOG,SQLERRM());
END;
BEGIN
SELECT COUNT(*) INTO l_count
FROM mtl_system_items_b
WHERE UPPER(segment1)=c_po_int_valid_rec.item
AND organization_id=c_po_int_valid_rec.inventory_organization_id;
IF l_count=0 THEN
UPDATE xx_tab_po_stage
SET row_status='E'
,error_message=error_message||' '||'Invalid item.'
WHERE CURRENT OF c_po_int_valid;
ELSE
IF c_po_int_valid_rec.inventory_organization_id = 103 THEN
SELECT max(secondary_inventory_name)
INTO l_subinv_name
FROM mtl_secondary_inventories
WHERE organization_id=c_po_int_valid_rec.inventory_organization_id;
ELSE
SELECT max(secondary_inventory_name)
INTO l_subinv_name
FROM mtl_secondary_inventories
WHERE organization_id=c_po_int_valid_rec.inventory_organization_id
AND secondary_inventory_name NOT IN(SELECT
default_stage_subinventory
FROM wsh_shipping_parameters);
END IF;
UPDATE xx_tab_po_stage
SET item_category_id=(SELECT DISTINCT category_id FROM mtl_item_categories
WHERE inventory_item_id=(SELECT inventory_item_id
FROM mtl_system_items_b
WHERE UPPER(segment1)=c_po_int_valid_rec.item
AND organization_id=c_po_int_valid_rec.inventory_organization_id
AND end_date_active IS NULL))
,item_description=(SELECT description
FROM mtl_system_items_b
WHERE UPPER(segment1)=c_po_int_valid_rec.item
AND organization_id=c_po_int_valid_rec.inventory_organization_id
AND end_date_active IS NULL)
,uom_code=(SELECT primary_uom_code
FROM mtl_system_items_b
WHERE UPPER(segment1)=c_po_int_valid_rec.item
AND organization_id=c_po_int_valid_rec.inventory_organization_id
AND end_date_active IS NULL)
,charge_account=(SELECT mp.material_account FROM MTL_PARAMETERS mp,mtl_system_items_b msi
WHERE
msi.organization_id = c_po_int_valid_rec.inventory_organization_id
AND msi.ORGANIZATION_ID = mp.ORGanization_id
AND UPPER(segment1)=c_po_int_valid_rec.item
AND msi.end_date_active IS NULL)
,item=(SELECT segment1
FROM mtl_system_items_b
WHERE UPPER(segment1)=c_po_int_valid_rec.item
AND organization_id=c_po_int_valid_rec.inventory_organization_id
AND end_date_active IS NULL)
,subinventory=l_subinv_name
WHERE CURRENT OF c_po_int_valid;
END IF;
EXCEPTION
WHEN OTHERS THEN
UPDATE xx_tab_po_stage
SET row_status='E'
,error_message=error_message||' '||'Error while deriving item category,item description,uom code,item id for the given item.'
WHERE CURRENT OF c_po_int_valid;
FND_FILE.PUT_LINE(FND_FILE.LOG,SQLERRM());
END;
END IF;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,SQLERRM());
END;
END LOOP;
Update xx_tab_po_stage
set row_status = 'E'
,error_message = 'Oracle cant Create Po With 0 or -Ve Qty'
where QUANTITY <=0;
UPDATE xx_tab_po_stage
SET rate_type='Corporate'
,rate=(SELECT conversion_rate
FROM gl_daily_rates
WHERE from_currency=currency
AND to_currency='INR'
AND conversion_date=po_date
AND conversion_type='Corporate')
WHERE row_status='V'
AND currency <> 'INR';
UPDATE xx_tab_po_stage
SET error_message=NULL
WHERE row_status='V';
COMMIT;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,SQLERRM());
END;
--Validation for Daily Rates in the General Ledger
--Upadate by Krishna on Date 15-05-2007
DECLARE
CURSOR c_rate_verify IS
SELECT *
FROM xx_tab_po_stage
Where
currency not in 'INR'
and row_status = 'V'
FOR UPDATE;
BEGIN
FOR c_rate_verify_rec IN c_rate_verify
LOOP
IF c_rate_verify_rec.rate is null then
UPDATE xx_tab_po_stage
SET row_status='E'
,error_message=error_message||' '||'Daily Rate in the GL was not Defined.'
WHERE CURRENT OF c_rate_verify;
END IF;
END LOOP;
END;
DECLARE
x NUMBER :=NULL;
CURSOR c IS
SELECT DISTINCT PO_HEADER_REFERENCE
FROM xx_tab_po_stage
WHERE row_status='V';
BEGIN
FOR c_rec IN c
LOOP
select meai_po_seq3.nextval into x from dual;
UPDATE xx_tab_po_stage
SET interface_header_id=x
WHERE UPPER(PO_HEADER_REFERENCE )=UPPER(c_rec.PO_HEADER_REFERENCE)
AND row_status='V';
END LOOP;
END;
commit;
DECLARE
x NUMBER;
y NUMBER;
Z NUMBER;
CURSOR c IS
SELECT DISTINCT interface_header_id
FROM xx_tab_po_stage;
BEGIN
FOR c_rec IN c
LOOP
x:=c_rec.interface_header_id;
DECLARE
CURSOR c1 IS
SELECT DISTINCT PCS_pono
FROM xx_tab_po_stage
WHERE interface_header_id=x
AND row_status='V';
BEGIN
y:=1;
FOR c1_rec IN c1
LOOP
SELECT meai_po_seq1.NEXTVAL INTO Z
FROM dual;
UPDATE xx_tab_po_stage
SET interface_line_id=Z
,lineno=y
WHERE interface_header_id=x
AND UPPER(PCS_pono)=UPPER(c1_rec.PCS_pono);
y:=y+1;
--Z:=Z+1;
commit;
END LOOP;
END;
END LOOP;
END;
UPDATE xx_tab_po_stage
SET ROW_STATUS = 'E'
,ERROR_MESSAGE = error_message||'Sub inventory Not Picked for Distributions.'
where subinventory is null
and INVENTORY_ORGANIZATION_ID <> 138;
commit;
DECLARE
l_count1 NUMBER:=0;
CURSOR c_verify IS
SELECT DISTINCT PO_HEADER_REFERENCE
FROM xx_tab_po_stage;
BEGIN
FOR c_verify_rec IN c_verify
LOOP
SELECT COUNT(*) INTO l_count1
FROM xx_tab_po_stage
WHERE row_status='E'
AND UPPER(PO_HEADER_REFERENCE)=UPPER(c_verify_rec.PO_HEADER_REFERENCE);
IF l_count1>0 THEN
UPDATE xx_tab_po_stage
SET row_status='R'
,error_message=error_message||' '||'Some of the lines under this slipno are errored out.'
WHERE UPPER(PO_HEADER_REFERENCE)=UPPER(c_verify_rec.PO_HEADER_REFERENCE)
AND row_status='V';
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,SQLERRM());
END;
DELETE po_lines_interface where INTERFACE_HEADER_ID in (select interface_header_id from po_headers_interface
where process_code = 'ACCEPTED');
DELETE po_distributions_interface where INTERFACE_HEADER_ID in (select interface_header_id from po_headers_interface
where process_code = 'ACCEPTED');
DELETE po_headers_interface where upper(process_code) = 'ACCEPTED';
COMMIT;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,SQLERRM());
END xx_po_validations;
/


-- ---------------------------------------------------------
-- Exporting Valid Purchase Order to Interface Tables
-- ---------------------------------------------------------

CREATE OR REPLACE PROCEDURE xx_po_import(errbuf OUT VARCHAR2, retcode OUT VARCHAR2)
IS
l_request number;
l_seq NUMBER:=0;
l_rstatus varchar2(1):=null;
l_errm varchar2(1000):=null;
l_slip number:=null;
l_po number:=null;
l_cnt number:=null;
CURSOR c_po_valid IS
SELECT Distinct
PO_HEADER_REFERENCE,
ROW_STATUS,
ERROR_MESSAGE
FROM xx_tab_po_stage
WHERE row_status IN ('E','T');

BEGIN
/* Inserting into Headers Interface for INR */

INSERT INTO po_headers_interface(
document_type_code
,creation_date
,vendor_id
,vendor_site_code
,ship_to_location_id
,bill_to_location_id
,currency_code
,agent_id
,attribute1
,interface_header_id
,vendor_doc_num
,action
--,rate
--,rate_date
,rate_type
,budget_account_id
,created_by
,last_update_login
,last_update_date
,last_updated_by)
SELECT DISTINCT
'STANDARD'
,po_date
,supplier_id
,supplier_site_code
,ship_to_location_id
,bill_to_location_id
,currency
,69
,PO_HEADER_REFERENCE
,interface_header_id
,PO_HEADER_REFERENCE
,'ORIGINAL'
--,rate
--,po_date
,rate_type
,1002
,FND_GLOBAL.user_id
,FND_GLOBAL.login_id
,sysdate
,FND_GLOBAL.user_id
FROM xx_tab_po_stage
WHERE row_status='V'
AND currency IN (SELECT DISTINCT(CURRENCY_CODE) FROM FND_CURRENCIES)
AND currency = 'INR';

/* Inserting into Headers Interface for other Currencies */

INSERT INTO po_headers_interface(
document_type_code
,creation_date
,vendor_id
,vendor_site_code
,ship_to_location_id
,bill_to_location_id
,currency_code
,agent_id
,attribute1
,interface_header_id
,vendor_doc_num
,action
--,rate
,rate_date
,rate_type
,budget_account_id
,created_by
,last_update_login
,last_update_date
,last_updated_by)
SELECT DISTINCT
'STANDARD'
,po_date
,supplier_id
,supplier_site_code
,ship_to_location_id
,bill_to_location_id
,currency
,69
,PO_HEADER_REFERENCE
,interface_header_id
,PO_HEADER_REFERENCE
,'ORIGINAL'
--,rate
,po_date
,rate_type
,1002
,FND_GLOBAL.user_id
,FND_GLOBAL.login_id
,sysdate
,FND_GLOBAL.user_id
FROM xx_tab_po_stage
WHERE row_status='V'
AND currency IN (SELECT DISTINCT(CURRENCY_CODE) FROM FND_CURRENCIES)
AND currency <> 'INR';

/* Inserting into Lines Interface */

INSERT INTO po_lines_interface(
line_num
,line_type_id
,item
,category_id
,item_description
,uom_code
,quantity
,unit_price
,line_attribute1
,shipment_num
,ship_to_organization_id
,ship_to_location_id
,interface_header_id
,interface_line_id
,action
,promised_date
,receiving_routing_id
,created_by
,last_update_login
,creation_date
,last_update_date
,last_updated_by)
SELECT DISTINCT
lineno
,1
,item
,item_category_id
,item_description
,uom_code
,quantity
,price
,PCS_pono
,1
,inventory_organization_id
,ship_to_location_id
,interface_header_id
,interface_line_id
,'ORIGINAL'
,promised_date
,3
,FND_GLOBAL.user_id
,FND_GLOBAL.login_id
,po_date
,sysdate
,FND_GLOBAL.user_id
FROM xx_tab_po_stage
WHERE row_status='V';

/* Inserting into PO Distributions Interface */

INSERT INTO po_distributions_interface(
quantity_ordered
,distribution_num
,destination_type_code
,charge_account_id
,interface_header_id
,interface_line_id
,interface_distribution_id
,destination_organization_id
,destination_subinventory
,budget_account_id
,created_by
,creation_date
,last_update_date
,last_update_login
,last_updated_by)
SELECT DISTINCT
quantity
,1
,'INVENTORY'
,charge_account
,interface_header_id
,interface_line_id
,interface_line_id
,inventory_organization_id
,subinventory
,1002
,FND_GLOBAL.user_id
,po_date
,sysdate
,FND_GLOBAL.login_id
,FND_GLOBAL.user_id
FROM xx_tab_po_stage
WHERE row_status='V';
FND_FILE.PUT_LINE(FND_FILE.output,'User: '||FND_GLOBAL.USER_NAME||' '||' Program Time: '||to_char(sysdate,'DD-MON-YYYY HH:MI:SS AM'));
FND_FILE.PUT_LINE(FND_FILE.output,' ');
FND_FILE.PUT_LINE(FND_FILE.output,'*********************************************');
FND_FILE.PUT_LINE(FND_FILE.output,'**********************|Purchase Order Interface Report|***********************');
FND_FILE.PUT_LINE(FND_FILE.output,'********************************************************************');
FND_FILE.PUT_LINE(FND_FILE.output,' PO_HEADER_REFERENCE '||' '||'ROW_STATUS '||' '||' ERROR_MESSAGE');
FND_FILE.PUT_LINE(FND_FILE.output,'***********************************************************************');
l_cnt:=0;
FOR l_index IN c_po_valid
LOOP
FND_FILE.PUT_LINE(FND_FILE.output,' '||l_index.PO_HEADER_REFERENCE||' '||l_index.ROW_STATUS||' '||l_index.ERROR_MESSAGE);
l_cnt:=l_cnt+1;
END LOOP ; -- Cursor loop
FND_FILE.PUT_LINE(FND_FILE.output,'***********************************************************************');
FND_FILE.PUT_LINE(FND_FILE.output,' ');
FND_FILE.PUT_LINE(FND_FILE.output,' '||l_cnt||' '||'Purchase Order(s) Processed..');
FND_FILE.PUT_LINE(FND_FILE.output,' ');
FND_FILE.PUT_LINE(FND_FILE.output,'*****************|End-Of-Report|*******************************');
FND_FILE.PUT_LINE(FND_FILE.output,' ');

/* Inserting Errored records into error table */

INSERT INTO xx_tab_po_err
(
SUPPLIER_CODE
,PROMISED_DATE
,CURRENCY
,PO_HEADER_REFERENCE
,ITEM
,QUANTITY
,PRICE
,PCS_PONO
,INVENTORY_ORGANIZATION_ID
,ROW_STATUS
,ERROR_MESSAGE
,DATE_OF_CREATION
,BATCH_ID
,PO_DATE
)
SELECT SUPPLIER_CODE
,PROMISED_DATE
,CURRENCY
,PO_HEADER_REFERENCE
,ITEM
,QUANTITY
,PRICE
,PCS_PONO
,INVENTORY_ORGANIZATION_ID
,ROW_STATUS
,ERROR_MESSAGE
,SYSDATE
,xx_po_seq.NEXTVAL
,PO_DATE
FROM xx_tab_po_stage
where row_staus = 'E';
--l_request:=fnd_request.submit_request('PO','POXPOPDOI', null, null, FALSE,null,'STANDARD',null,'Y',null,'APPROVED',null,null,null,null);
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Purchasing Documents Import Request ID :'||l_request);
DELETE xx_tab_po_stage where row_status not in ('T','E','R');
EXCEPTION
WHEN others THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
FND_FILE.PUT_LINE(FND_FILE.LOG,SQLERRM);
END xx_po_import;
/


-- ----------------------------------------------------------------------------------
-- Running the Purchase order Import
-- ---------------------------------------------------------------------------------

Login to Applications

PO User --> Submit request --> Import Standar Purchase orders

Now all the orders will be created which are loaded in the interface.

Note: In po_headers_interface the process_code will be 'ACCEPTED' for the orders which are created, The process_code will be 'REJECTED' if it errored out.

-- ---------------------------------------------------------------------------------