AR Invoice Interface rejected transactions

-- ---------------------------------------------------------------------------
-- Purpose : AR Invoice Interface rejected Records for Analysis
-- Date      : 31.May.2009
-- Created by : Siri
-- .....
-- ---------------------------------------------------------------------------


SELECT ril.INTERFACE_LINE_ID,
(select customer_name from ra_customers
where customer_id = ril.ORIG_SYSTEM_BILL_CUSTOMER_ID
or customer_number = ril.orig_system_bill_customer_ref) Customer_Name,
ril.trx_number,
ril.trx_date,
ril.gl_date,
ril.INTERFACE_LINE_CONTEXT,
ril.INTERFACE_LINE_ATTRIBUTE1,
ril.BATCH_SOURCE_NAME,
ril.LINE_TYPE,
ril.INTERFACE_STATUS Status ,
rie.INVALID_VALUE,
rie.MESSAGE_TEXT
from ra_interface_lines_all ril,
ra_interface_errors_all rie
where rie.INTERFACE_LINE_ID = ril.INTERFACE_LINE_ID
and ril.BATCH_SOURCE_NAME = :batch_source_name;

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

PID and SID details of a Concurrent Request

-- ----------------------------------------------------------------------------------------------------------------
-- Purpose : Getting the Process ID, session and terminal details for the Concurrent request.
-- Date      : 30.May.2009
-- Created by : Siri
-- .....
-- ----------------------------------------------------------------------------------------------------------------

SELECT req.request_id ,
prog.USER_CONCURRENT_PROGRAM_NAME Conc_prg_Name,
fu.USER_NAME requested_by,
fr.RESPONSIBILITY_KEY responsibility,
ses.LOGON_TIME,
ses.sid,
ses.serial#,
proc.SPID,
proc.pid,
ses.USERNAME "User",
ses.STATUS,
ses.sql_hash_value sql_hash,
proc.USERNAME osuser,
ses.MACHINE,
nvl(ses.TERMINAL,'Not Available') Terminal,
ses.MODULE,
ses.ACTION,
ses.MODULE_HASH,
nvl(ses.PROGRAM,'Not Available') Program
from fnd_concurrent_requests req, v$session ses, v$process proc,
v$parameter dest, v$parameter dbnm, fnd_concurrent_programs_vl prog,
fnd_responsibility fr,fnd_user fu,
fnd_executables execname
where
1=1
and req.request_id = :request_id
and req.oracle_process_id=proc.spid(+)
and proc.addr = ses.paddr(+)
and dest.name='user_dump_dest'
and dbnm.name='db_name'
and req.concurrent_program_id = prog.concurrent_program_id
and req.program_application_id = prog.application_id
and req.RESPONSIBILITY_ID = fr.RESPONSIBILITY_ID
and fu.USER_ID = req.REQUESTED_BY
and prog.application_id = execname.application_id
and prog.executable_id=execname.executable_id;

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

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;

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

AP Invoice Interface rejected Records

-- ---------------------------------------------------------------------------
-- Purpose : AP Invoice Interface rejected Records for Analysis
-- Date      : 28.May.2009
-- Created by : Siri
-- .....
-- ---------------------------------------------------------------------------


SELECT pv.vendor_name,
aii.INVOICE_ID,
aii.SOURCE,
aii.invoice_num,
(select decode(count(*),0,'Invoice N/A','Invoice Available') from ap_invoices_all
where org_id = aii.ORG_ID
and invoice_num = aii.INVOICE_NUM) Inv_in_Base,
aii.CREATION_DATE,
aii.invoice_date,
aii.GL_DATE,
ail.LINE_NUMBER,
aii.REQUEST_ID,
aii.invoice_currency_code,
nvl(airh.REJECT_LOOKUP_CODE,airl.REJECT_LOOKUP_CODE) Rejection_Reason,
(select decode(count(INACTIVE_DATE),0,'Site is Active','Site is Inactive') from po_vendor_sites_All
where vendor_site_id = aii.VENDOR_SITE_ID) Site_STatus
from ap_invoice_lines_interface ail,
AP_INTERFACE_REJECTIONS airh,
AP_INTERFACE_REJECTIONS airl,
ap_invoices_interface aii,
po_vendors pv
where aii.INVOICE_ID = ail.INVOICE_ID
and airh.PARENT_ID = aii.INVOICE_ID
and ail.INVOICE_LINE_ID = airl.PARENT_ID
and aii.VENDOR_ID = pv.VENDOR_ID
order by 2,6;

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

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.

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

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.

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

Getting the Source Code from database using Query

-- ---------------------------------------------------------------------------------
-- Purpose : Get the Source Code from database without junck characters using Query
-- Date      : 22.May.2009
-- Created by : Chaitanya
-- .....
-- ----------------------------------------------------------------------------------


SELECT
decode(rownum ,1,'CREATE OR REPLACE '||rTRIM(rTRIM(t.text,CHR(10))),rTRIM(rTRIM(t.text,CHR(10)))) text
from dba_source t
Where type = 'PACKAGE BODY'
and name = &Package_name



-- ---------------------------------------------------------------------------------
-- Purpose : Anonoumos block to Get the Source Code from database
-- Date : 22.May.2009
-- Created by : Chaitanya
-- .....
-- ----------------------------------------------------------------------------------


DECLARE
x varchar2(200);
n number;
cursor c_rec is
select text,line,name,type from all_source
where name = &Package_name
and type like 'PACKAGE';
begin
dbms_output.put_line('================================================ ');
dbms_output.put_line('============ Package Specifications============= ');
dbms_output.put_line('================================================ ');
dbms_output.put_line(' ');
for c in c_rec
loop
n:= c.line;
select rtrim(ltrim(text,chr(10)),chr(10))
into x
from all_source
where name = c.name
and type = c.type
--and type <> 'PACKAGE'
and line = c.line;
dbms_output.put_line(x);
end loop;
exception
when others then
dbms_output.put_line('Error at '|| n || ' '|| 'line, ' || sqlerrm);
end;

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

Concurrent requests Errors report for the week

-- -------------------------------------------------------------------------------------------------------------
-- Purpose : Get the detials of errors and warnings for the concurrent requests during the week.
-- Date      : 22.May.2009
-- Created by : Chaitanya
-- .....
-- -------------------------------------------------------------------------------------------------------------


SELECT
fcr.request_id,
fcp.user_concurrent_program_name Conc_prog_name,
fcp.CONCURRENT_PROGRAM_NAME Short_name,
fe.EXECUTION_FILE_NAME execution_file,
(select meaning from fnd_lookups
where lookup_type = 'CP_EXECUTION_METHOD_CODE'
and lookup_code = fe.EXECUTION_METHOD_CODE) Execution_Method,
fa.BASEPATH Application_Top,
fr.RESPONSIBILITY_KEY Responsibility,
(select meaning from fnd_lookups
where lookup_type = 'CP_PHASE_CODE'
and lookup_code = fcr.PHASE_CODE) Phase_Code,
(select meaning from fnd_lookups
where lookup_type = 'CP_STATUS_CODE'
and lookup_code = fcr.STATUS_CODE) Status_code,
fu.USER_NAME Requested_By
from fnd_concurrent_requests fcr,
fnd_user fu,
fnd_concurrent_programs_vl fcp,
fnd_responsibility fr,
fnd_executables fe,
FND_APPLICATION fa
where fcr.REQUESTED_BY = fu.USER_ID
and fcp.CONCURRENT_PROGRAM_ID = fcr.CONCURRENT_PROGRAM_ID
and fr.RESPONSIBILITY_ID = fcr.RESPONSIBILITY_ID
and fcp.executable_ID = fe.executable_ID
and fa.APPLICATION_ID = fe.APPLICATION_ID
and fcr.STATUS_CODE in('E','X','G','D')
and fcr.ACTUAL_COMPLETION_DATE > sysdate-7
order by 9;

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