-- ----------------------------------------------------------------------------------------------------
-- Purpose : To get current period statuses for all the application modules at a single query
-- Release Date: 03.Aug.2009
-- Created by : Siri
-- .....
-- ----------------------------------------------------------------------------------------------------
select gsob.NAME sob_name,
fav.APPLICATION_NAME,
gps.PERIOD_NAME,
gps.start_date,
gps.END_DATE,
(select meaning from fnd_lookups
where lookup_code = gps.CLOSING_STATUS
and lookup_type = 'IGC_CC_PERIOD_STATUS') Period_status
from GL_PERIOD_STATUSES gps,
fnd_application_vl fav,
gl_sets_of_books gsob
where gps.APPLICATION_ID = fav.APPLICATION_ID
and gsob.set_of_books_id = gps.SET_OF_BOOKS_ID
and fav.APPLICATION_SHORT_NAME = nvl(:appl_short_name,fav.APPLICATION_SHORT_NAME)
and gps.PERIOD_NAME = nvl(:p_period,to_char(sysdate,'Mon-YY'))
order by 1,2
-- ----------------------------------------------------------------------------------------------------
Showing posts with label Technical - PO. Show all posts
Showing posts with label Technical - PO. Show all posts
Monday, August 3, 2009
Friday, May 29, 2009
Purchase order and Receipt Information
-- ---------------------------------------------------------------------------------
-- Purpose : Get the details of the PO, Receipt and line status information
-- Date : 29.May.2009
-- Created by : Siri
-- .....
-- ----------------------------------------------------------------------------------
SELECT ph.segment1 po_num,
pl.LINE_NUM,
msi.segment1 item_name,
pv.VENDOR_NAME,
pll.QUANTITY ord_qty,
pll.QUANTITY_RECEIVED qty_rcvd,
pll.QUANTITY_BILLED Invcd_qty,
decode((select count(distinct rsh.RECEIPT_NUM) from RCV_SHIPMENT_lines rsl,
rcv_shipment_headers rsh
where rsl.po_line_id = pl.PO_LINE_ID
and rsl.SHIPMENT_HEADER_ID = rsh.SHIPMENT_HEADER_ID),1,
(select distinct rsh.RECEIPT_NUM from RCV_SHIPMENT_lines rsl,
rcv_shipment_headers rsh
where rsl.po_line_id = pl.PO_LINE_ID
and rsl.SHIPMENT_HEADER_ID = rsh.SHIPMENT_HEADER_ID)
,'Received in More than one Receipt') Receipt_num,
pll.PO_LINE_ID,
ph.CLOSED_CODE order_status,
pl.CLOSED_CODE line_status
from po_headers_all ph
,po_lines_all pl
,po_line_locations_all pll
,po_vendors pv
,mtl_system_items_b msi
where 1=1
and ph.type_lookup_code = 'STANDARD'
and pl.PO_HEADER_ID = ph.PO_HEADER_ID
and pll.PO_HEADER_ID = ph.PO_HEADER_ID
and pl.PO_LINE_ID = pll.PO_LINE_ID
and msi.ORGANIZATION_ID = pll.SHIP_TO_ORGANIZATION_ID
and ph.VENDOR_ID = pv.VENDOR_ID
and msi.INVENTORY_ITEM_ID = pl.ITEM_ID
and ph.SEGMENT1 = :po_num;
-- ---------------------------------------------------------------------------
-- Purpose : Get the details of the PO, Receipt and line status information
-- Date : 29.May.2009
-- Created by : Siri
-- .....
-- ----------------------------------------------------------------------------------
SELECT ph.segment1 po_num,
pl.LINE_NUM,
msi.segment1 item_name,
pv.VENDOR_NAME,
pll.QUANTITY ord_qty,
pll.QUANTITY_RECEIVED qty_rcvd,
pll.QUANTITY_BILLED Invcd_qty,
decode((select count(distinct rsh.RECEIPT_NUM) from RCV_SHIPMENT_lines rsl,
rcv_shipment_headers rsh
where rsl.po_line_id = pl.PO_LINE_ID
and rsl.SHIPMENT_HEADER_ID = rsh.SHIPMENT_HEADER_ID),1,
(select distinct rsh.RECEIPT_NUM from RCV_SHIPMENT_lines rsl,
rcv_shipment_headers rsh
where rsl.po_line_id = pl.PO_LINE_ID
and rsl.SHIPMENT_HEADER_ID = rsh.SHIPMENT_HEADER_ID)
,'Received in More than one Receipt') Receipt_num,
pll.PO_LINE_ID,
ph.CLOSED_CODE order_status,
pl.CLOSED_CODE line_status
from po_headers_all ph
,po_lines_all pl
,po_line_locations_all pll
,po_vendors pv
,mtl_system_items_b msi
where 1=1
and ph.type_lookup_code = 'STANDARD'
and pl.PO_HEADER_ID = ph.PO_HEADER_ID
and pll.PO_HEADER_ID = ph.PO_HEADER_ID
and pl.PO_LINE_ID = pll.PO_LINE_ID
and msi.ORGANIZATION_ID = pll.SHIP_TO_ORGANIZATION_ID
and ph.VENDOR_ID = pv.VENDOR_ID
and msi.INVENTORY_ITEM_ID = pl.ITEM_ID
and ph.SEGMENT1 = :po_num;
-- ---------------------------------------------------------------------------
Tuesday, May 26, 2009
Purchase Order Cancellation Interface
-- ---------------------------------------------------------------------------
-- Purpose : Purchase order Cancellation validations
-- Date : 26.May.2009
-- Created by : Siri
-- .....
-- ---------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE xx_po_can_validation(errbuf OUT VARCHAR2, retcode OUT VARCHAR2)
AS
l_count NUMBER(2):=0;
l_header_id NUMBER:=0;
l_line_id NUMBER:=0;
l_flag VARCHAR2(1):=NULL;
CURSOR c_po_cancel_valid IS
SELECT *
FROM xx_tab_po_can_stg
WHERE row_status IN ('E','T')
FOR UPDATE;
BEGIN
FOR c_po_cancel_valid_rec IN c_po_cancel_valid
LOOP
BEGIN
SELECT COUNT(*) INTO l_count
FROM po_headers_all pha
,po_lines_all pla
WHERE pha.po_header_id=pla.po_header_id
AND UPPER(pha.attribute1)=UPPER(c_po_cancel_valid_rec.po_header_reference)
AND UPPER(pla.attribute1)=UPPER(c_po_cancel_valid_rec.po_line_reference);
IF l_count=0 THEN
UPDATE xx_tab_po_can_stg
SET row_status='E'
,error_message='This Slip number,Po number is not existing'
WHERE CURRENT OF c_po_cancel_valid;
ELSIF l_count>0 THEN
SELECT pla.cancel_flag INTO l_flag
FROM po_headers_all pha
,po_lines_all pla
WHERE pha.po_header_id=pla.po_header_id
AND UPPER(pha.attribute1)=UPPER(c_po_cancel_valid_rec.po_header_reference)
AND UPPER(pla.attribute1)=UPPER(c_po_cancel_valid_rec.po_line_reference);
IF l_flag='Y' THEN
UPDATE xx_tab_po_can_stg
SET row_status='R'
,error_message='This slipno and pono combination is already closed'
WHERE CURRENT OF c_po_cancel_valid;
ELSE
SELECT pha.po_header_id
,pla.po_line_id
INTO l_header_id
,l_line_id
FROM po_headers_all pha
,po_lines_all pla
WHERE pha.po_header_id=pla.po_header_id
AND UPPER(pha.attribute1)=UPPER(c_po_cancel_valid_rec.po_header_reference)
AND UPPER(pla.attribute1)=UPPER(c_po_cancel_valid_rec.po_line_reference);
UPDATE xx_tab_po_can_stg
SET header_id=l_header_id
,line_id=l_line_id
,row_status='V'
WHERE CURRENT OF c_po_cancel_valid;
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,SQLERRM());
END;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,SQLERRM());
END xx_po_can_validation;
/
--======================================================
--Cancelling the Valid Purchase order lines
--======================================================
CREATE OR REPLACE PROCEDURE xx_po_can_import(errbuf out varchar2, retcode out varchar2)
AS
i NUMBER:=0;
x NUMBER:=0;
x_return_status VARCHAR2(1);
p_look_up_code VARCHAR2(10);
p_user_id NUMBER:=0;
p_resp_id NUMBER:=0;
p_resp_appl_id NUMBER:=0;
CURSOR c_po_cancel_import IS
SELECT *
FROM xx_tab_po_can_stg
WHERE row_status='V';
BEGIN
SELECT meai_po_cancel_seq.NEXTVAL INTO x
FROM DUAL;
SELECT fnd_profile.value('user_id') INTO p_user_id from dual;
SELECT fnd_profile.value('resp_id') INTO p_resp_id from dual;
SELECT fnd_profile.value('resp_appl_id') INTO p_resp_appl_id from dual;
FND_GLOBAL.apps_initialize ( user_id => p_user_id,
resp_id => p_resp_id,
resp_appl_id => p_resp_appl_id);
BEGIN
FOR c_po_cancel_import_rec IN c_po_cancel_import
LOOP
SELECT
distinct(type_lookup_code)
into p_look_up_code
from po_headers_all
where po_header_id = c_po_cancel_import_rec.header_id;
PO_Document_Control_PUB.control_document(
1.0,
FND_API.G_TRUE,
FND_API.G_TRUE,
x_return_status,
'PO',
p_look_up_code,
c_po_cancel_import_rec.header_id,
NULL,
NULL,
NULL,
c_po_cancel_import_rec.line_id,
NULL,
NULL,
NULL,
'CANCEL',
c_po_cancel_import_rec.cancelled_date,
NULL,
'N',
NULL,
NULL,
NULL
);
FOR i IN 1..FND_MSG_PUB.count_msg
LOOP
FND_FILE.put_line(FND_FILE.LOG,FND_MSG_PUB.Get(p_msg_index => i,p_encoded => 'F'));
END LOOP;
END LOOP;
DECLARE
l_cnt number:=0;
CURSOR c_po_valid IS
SELECT Distinct
RPAD(po_header_reference,16,' ') po_header_reference,
RPAD(ROW_STATUS,8,' ') ROW_STATUS,
RPAD(ERROR_MESSAGE,200,' ') ERROR_MESSAGE
FROM xx_tab_po_can_stg;
BEGIN
FND_FILE.PUT_LINE(FND_FILE.output,'************************|Purchase Order Cancellation 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..');
END;
INSERT INTO xx_tab_po_can_err(
po_header_reference
,po_line_reference
,cancelled_date
,row_status
,error_message
,date_of_creation
,batch_id)
SELECT po_header_reference
,po_line_reference
,cancelled_date
,row_status
,error_message
,SYSDATE
,x
FROM xx_tab_po_can_stg;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,SQLERRM());
END;
DELETE xx_tab_po_can_stg;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,SQLERRM());
END xx_po_can_import;
/
Note: This program only will cancel the PO Lines, order has to be cancelled manually.
-- ---------------------------------------------------------------------------
-- Purpose : Purchase order Cancellation validations
-- Date : 26.May.2009
-- Created by : Siri
-- .....
-- ---------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE xx_po_can_validation(errbuf OUT VARCHAR2, retcode OUT VARCHAR2)
AS
l_count NUMBER(2):=0;
l_header_id NUMBER:=0;
l_line_id NUMBER:=0;
l_flag VARCHAR2(1):=NULL;
CURSOR c_po_cancel_valid IS
SELECT *
FROM xx_tab_po_can_stg
WHERE row_status IN ('E','T')
FOR UPDATE;
BEGIN
FOR c_po_cancel_valid_rec IN c_po_cancel_valid
LOOP
BEGIN
SELECT COUNT(*) INTO l_count
FROM po_headers_all pha
,po_lines_all pla
WHERE pha.po_header_id=pla.po_header_id
AND UPPER(pha.attribute1)=UPPER(c_po_cancel_valid_rec.po_header_reference)
AND UPPER(pla.attribute1)=UPPER(c_po_cancel_valid_rec.po_line_reference);
IF l_count=0 THEN
UPDATE xx_tab_po_can_stg
SET row_status='E'
,error_message='This Slip number,Po number is not existing'
WHERE CURRENT OF c_po_cancel_valid;
ELSIF l_count>0 THEN
SELECT pla.cancel_flag INTO l_flag
FROM po_headers_all pha
,po_lines_all pla
WHERE pha.po_header_id=pla.po_header_id
AND UPPER(pha.attribute1)=UPPER(c_po_cancel_valid_rec.po_header_reference)
AND UPPER(pla.attribute1)=UPPER(c_po_cancel_valid_rec.po_line_reference);
IF l_flag='Y' THEN
UPDATE xx_tab_po_can_stg
SET row_status='R'
,error_message='This slipno and pono combination is already closed'
WHERE CURRENT OF c_po_cancel_valid;
ELSE
SELECT pha.po_header_id
,pla.po_line_id
INTO l_header_id
,l_line_id
FROM po_headers_all pha
,po_lines_all pla
WHERE pha.po_header_id=pla.po_header_id
AND UPPER(pha.attribute1)=UPPER(c_po_cancel_valid_rec.po_header_reference)
AND UPPER(pla.attribute1)=UPPER(c_po_cancel_valid_rec.po_line_reference);
UPDATE xx_tab_po_can_stg
SET header_id=l_header_id
,line_id=l_line_id
,row_status='V'
WHERE CURRENT OF c_po_cancel_valid;
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,SQLERRM());
END;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,SQLERRM());
END xx_po_can_validation;
/
--======================================================
--Cancelling the Valid Purchase order lines
--======================================================
CREATE OR REPLACE PROCEDURE xx_po_can_import(errbuf out varchar2, retcode out varchar2)
AS
i NUMBER:=0;
x NUMBER:=0;
x_return_status VARCHAR2(1);
p_look_up_code VARCHAR2(10);
p_user_id NUMBER:=0;
p_resp_id NUMBER:=0;
p_resp_appl_id NUMBER:=0;
CURSOR c_po_cancel_import IS
SELECT *
FROM xx_tab_po_can_stg
WHERE row_status='V';
BEGIN
SELECT meai_po_cancel_seq.NEXTVAL INTO x
FROM DUAL;
SELECT fnd_profile.value('user_id') INTO p_user_id from dual;
SELECT fnd_profile.value('resp_id') INTO p_resp_id from dual;
SELECT fnd_profile.value('resp_appl_id') INTO p_resp_appl_id from dual;
FND_GLOBAL.apps_initialize ( user_id => p_user_id,
resp_id => p_resp_id,
resp_appl_id => p_resp_appl_id);
BEGIN
FOR c_po_cancel_import_rec IN c_po_cancel_import
LOOP
SELECT
distinct(type_lookup_code)
into p_look_up_code
from po_headers_all
where po_header_id = c_po_cancel_import_rec.header_id;
PO_Document_Control_PUB.control_document(
1.0,
FND_API.G_TRUE,
FND_API.G_TRUE,
x_return_status,
'PO',
p_look_up_code,
c_po_cancel_import_rec.header_id,
NULL,
NULL,
NULL,
c_po_cancel_import_rec.line_id,
NULL,
NULL,
NULL,
'CANCEL',
c_po_cancel_import_rec.cancelled_date,
NULL,
'N',
NULL,
NULL,
NULL
);
FOR i IN 1..FND_MSG_PUB.count_msg
LOOP
FND_FILE.put_line(FND_FILE.LOG,FND_MSG_PUB.Get(p_msg_index => i,p_encoded => 'F'));
END LOOP;
END LOOP;
DECLARE
l_cnt number:=0;
CURSOR c_po_valid IS
SELECT Distinct
RPAD(po_header_reference,16,' ') po_header_reference,
RPAD(ROW_STATUS,8,' ') ROW_STATUS,
RPAD(ERROR_MESSAGE,200,' ') ERROR_MESSAGE
FROM xx_tab_po_can_stg;
BEGIN
FND_FILE.PUT_LINE(FND_FILE.output,'************************|Purchase Order Cancellation 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..');
END;
INSERT INTO xx_tab_po_can_err(
po_header_reference
,po_line_reference
,cancelled_date
,row_status
,error_message
,date_of_creation
,batch_id)
SELECT po_header_reference
,po_line_reference
,cancelled_date
,row_status
,error_message
,SYSDATE
,x
FROM xx_tab_po_can_stg;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,SQLERRM());
END;
DELETE xx_tab_po_can_stg;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,SQLERRM());
END xx_po_can_import;
/
Note: This program only will cancel the PO Lines, order has to be cancelled manually.
-- ---------------------------------------------------------------------------
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.
-- ---------------------------------------------------------------------------------
-- 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.
-- ---------------------------------------------------------------------------------
Subscribe to:
Posts (Atom)