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;

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

To Get the Sales order, Line, Delivery and invoice Status

-- ---------------------------------------------------------------------------------
-- Purpose : Query to retrive the Sales order,Line, Delivery and invoice Status.
-- Date      : 22.May.2009
-- Created by : Chaitanya
-- .....
-- ---------------------------------------------------------------------------------

SELECT
ooh.ORDER_NUMBER So_Num,
ooh.HEADER_ID,
ool.ordered_quantity ordered_qty,
(select sum(a.SHIPPED_QUANTITY) from wsh_delivery_details a
where SOURCE_HEADER_ID =ooh.HEADER_ID) shipped_qty,
wnd.DELIVERY_ID,
wnd.NAME delivery_name,
(select distinct batch_id from wsh_delivery_details
where SOURCE_HEADER_ID =ooh.HEADER_ID) batch_id,
initcap(ooh.FLOW_STATUS_CODE) Order_Status,
initcap(ool.FLOW_STATUS_CODE) Line_status,
decode(wnd.STATUS_CODE,'CL','Closed','Open') Delivery_status,
(select decode(count(*),0,'Invoice Not Created','Invoice Generated') from ra_customer_trx_lines_all rctl
where rctl.line_type = 'LINE'
and rctl.interface_line_attribute1 = to_char(ooh.order_number)) inv_Status
from
oe_order_headers_all ooh,
wsh_new_deliveries wnd,
oe_order_lines_all ool
where ooh.HEADER_ID = ool.HEADER_ID
and wnd.SOURCE_HEADER_ID = ooh.HEADER_ID
and ooh.order_number = :order_num;


--=======================================================
--- Query to retrive the Sales order and associate invoice.
--=======================================================

SELECT
ooh.order_number,
ooh.ORDERED_DATE,
ooh.FLOW_STATUS_CODE SO_Status,
ool.line_number,
msi.SEGMENT1 Item_Name,
ool.ordered_quantity,
rct.TRX_NUMBER Invoice_Num,
rct.TRX_date Invoice_Date,
rct.STATUS_TRX,
decode(rct.COMPLETE_FLAG,'Y','Completed','In Complete') Inv_Status,
ool.UNIT_SELLING_price*ool.ordered_quantity line_total
from
oe_order_headers_all ooh,
oe_order_lines_all ool,
hz_cust_accounts hca,
ra_customer_trx_lines_all rctl,
ra_customer_trx_all rct,
mtl_system_items msi
where
ooh.header_id=ool.header_id
and ooh.sold_to_org_id=hca.cust_account_id
and msi.INVENTORY_ITEM_ID=ool.INVENTORY_ITEM_ID
and msi.ORGANIZATION_ID=ool.SHIP_FROM_ORG_ID
and rct.CUSTOMER_TRX_ID = rctl.CUSTOMER_TRX_ID
and rctl.LINE_TYPE = 'LINE'
and rctl.interface_line_attribute1 = to_char(ooh.ORDER_NUMBER)
and rctl.QUANTITY_invoiced = ool.ORDERED_QUANTITY
and ooh.order_number= 1195372 --517980
order by ool.line_number;


Note: This Query is based on one Invoice for every sales order, and invoice will have those many lines as in the salesorder.


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

Getting the concurrent output into Excel Sheet

-- ---------------------------------------------------------------------------------
-- Purpose : Steps to get the concurrent output into Excel Sheet
-- Date      : 20.May.2009
-- Created by : Chaitanya
-- .....
-- ---------------------------------------------------------------------------------

1. To get the output in the excel format from concurrent output we need to modify any Viewer Options like (PCL).

Navigation path System Administrator à Install à Viewer Options

2. The below is the update statement which will change the PCL viewer
option to excel. After this updates the concurrent programs whichever is having
output type as PCL will open directly in ExcelSheet.

Statement: update fnd_mime_types_tl

set mime_type = 'application/vnd.ms-excel'

,description = 'Excel (SYLK) used to be application/vnd.hp-PCL: Printer Control Language'

,last_update_date = sysdate

where file_format_code = 'PCL'

and mime_type = 'application/vnd.hp-PCL';

commit;

Viewer Options Screen.

3. Run the concurrent program which is having the output type as PCL to test the excel output.

==============================================

Resetting the Workflow for Purchase Order

-- ---------------------------------------------------------------------------------
-- Purpose : Steps to restart/reset the Purchase order workflow.
-- Date      : 20.May.2009
-- Created by : Chaitanya
-- .....
-- ---------------------------------------------------------------------------------


If any Standard Purchase Order got strucked in the Workflow if we need to reset it then follow the process below.

Setps:

1. SELECT ph.segment1 PO_Num,
ph.ORG_ID,
ph.TYPE_LOOKUP_CODE PO_TYPE,
ph.authorization_status,
ph.WF_ITEM_KEY,
ph.WF_ITEM_TYPE
from po_headers_all ph
where ph.segment1 = '225343' -- PO Number
and ph.TYPE_LOOKUP_CODE = 'STANDARD';

2. Download the file poxrespo.sql and run the script in the instance where the purchase order got strucked.

3. The workflow will be resetted after the script execution and user can try re-approving the order now.

Suggestion: Make sure that workflow background process is running and scheduled.

If you have any doubts follow the Oracle note.id: 390023.1

=====================================================

Resolving the exception(WSH_CHANGE_SCHED_DATE) raised

-- ---------------------------------------------------------------------------------
-- Purpose : Resolving the exception(WSH_CHANGE_SCHED_DATE) raised
-- Date      : 19.May.2009
-- Created by : Chaitanya
-- .....
-- ---------------------------------------------------------------------------------

Possibilities of the error: If an order has the scheduled shipment date greater than the actual shipping date.

Please follow the steps below.

1. If you want to run like that you may need to disable that exception once after that continue to second step.
2. Loging to OM Super User --> Shipping --> Exceptions and query for WSH_CHANGE_SCHED_DATE.
3. Tools --> Resolve/Purge Exceptions
4. Then enter the exception name which you got, this will purge the exceptions in the interface.
5. Once all the steps are complete you can proceed with normal Shipping process.

Follow the document WSHEXCEP.doc which will give more information with screenshots.


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

Restarting the Sales Order Workflow

-- ------------------------------------------------------------------------------
-- Purpose : Restarting the Sales Order Workflow
-- Date      : 18.May.2009
-- Created by : Chaitanya
-- .....
-- ------------------------------------------------------------------------------

When ever the Sales order got strucked at the shipment with status Interfaced and Sales order status as Fulfilled.
Please follow the steps given below.

1. Query for the Order which is having the status as Fulfilled.
2. Go to lines and do right click and select retry activities in the error.
3. Then it will show the activity pending list choose the invoice interface.
4. Invoice will start processing after that.

Follow the document SOWFREL.doc which will give more information with screenshots.

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

Query to Get the Trace file location of a concurrent request

-- ------------------------------------------------------------------------------
-- Purpose : Query to Get the Trace file location of a concurrent request
-- Date      : 17.May.2009
-- Created by : Chaitanya
-- .....
-- ------------------------------------------------------------------------------


SELECT req.request_id ,
'Trace id: '||oracle_Process_id,
'Trace Flag: '||req.enable_trace,
'Trace Name:
'||dest.value||'/'||lower(dbnm.value)||'_ora_'||oracle_process_id||'.trc',
'Prog. Name: '||prog.user_concurrent_program_name,
'File Name: '||execname.execution_file_name|| execname.subroutine_name ,
'Status : '||decode(phase_code,'R','Running')
||'-'||decode(status_code,'R','Normal'),
'SID Serial: '||ses.sid||','|| ses.serial#,
'Module : '||ses.module
from fnd_concurrent_requests req, v$session ses, v$process proc,
v$parameter dest, v$parameter dbnm, fnd_concurrent_programs_vl prog,
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 prog.application_id = execname.application_id
and prog.executable_id=execname.executable_id;

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

Query to retrive Account Receivable Invoice, Receipt and Payment Status

-- ------------------------------------------------------------------------------------------------------
-- Purpose : Query to retrive Account Receivable Invoice, Receipt and Payment Status
-- Date      : 17.May.2009
-- Created by : Chaitanya
-- .....
-- ------------------------------------------------------------------------------------------------------


SELECT ct.CUSTOMER_TRX_ID,
ps_inv.TRX_NUMBER,
ct.ct_reference,
(select sum(extenDed_amount) from ra_customer_trx_lines_all
where customer_trx_id = ct.CUSTOMER_TRX_ID) Invoice_Amount,
cr.RECEIPT_NUMBER,
cr.STATUS,
cr.AMOUNT total_Receipt_amount,
nvl((select sum(-amount)
from ar_adjustments_all
where customer_trx_id = ct.CUSTOMER_TRX_ID),0) Adjustment_Amount
/*decode((nvl((select sum(-amount)
from ar_adjustments_all
where customer_trx_id = ct.CUSTOMER_TRX_ID),0)+cr.AMOUNT) -
(select sum(extenDed_amount) from ra_customer_trx_lines_all
where customer_trx_id = ct.CUSTOMER_TRX_ID),0,'Invoice Fully Paid', 'Partially Paid') Payment_Status */
FROM ar_receivable_applications_all app,
ar_cash_receipts cr,
ar_payment_schedules_all ps_inv,
ra_customer_trx_all ct,
ar_receivables_trx_all art
WHERE 1=1
AND app.cash_receipt_id = cr.cash_receipt_id
AND ct.customer_trx_id(+) = ps_inv.customer_trx_id
AND app.applied_payment_schedule_id = ps_inv.payment_schedule_id
AND art.receivables_trx_id(+) = app.receivables_trx_id
--AND cr.RECEIPT_NUMBER = :ar_receipt_num
AND ct.TRX_NUMBER = :ar_invoice_num;

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

To Get the Request Id, Concurent Program and Requestor Informations

-- -------------------------------------------------------------------------------------------------------------
-- Purpose : To Get the Request Id, Concurent Program and Requestor Informations.
-- Date       : 14.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,
fr.RESPONSIBILITY_KEY Responsibility,
(select meaning from fnd_lookups
where lookup_type = 'CP_STATUS_CODE'
and lookup_code = fcr.STATUS_CODE) Status_code,
(select meaning from fnd_lookups
where lookup_type = 'CP_PHASE_CODE'
and lookup_code = fcr.PHASE_CODE) Phase_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
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 fcr.REQUEST_ID = :request_id;

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

Account Payable Invoice and Accounting Details.

-- -------------------------------------------------------------------------------------------------
-- Purpose : Query to get the invoice,Accounting detials based on Batch and Checknumbers
-- Date      : 12.May.2009
-- Created by : Chaitanya
-- .....
-- -------------------------------------------------------------------------------------------------


p>SELECT apc.check_Name,
ai.invoice_num,
ai.invoice_id,
apc.CHECK_NUMBER,
apc.BANK_ACCOUNT_NAME,
apc.status_lookup_code Pay_Batch_status,
(select aae.EVENT_STATUS_CODE from ap_accounting_events_all aae
where aae.SOURCE_ID = apc.CHECK_IDand aae.EVENT_TYPE_CODE = 'PAYMENT CLEARING') Check_Event_STatus,
(select aae.ACCOUNTING_DATE from ap_accounting_events_all aae
where aae.SOURCE_ID = apc.CHECK_ID
and aae.EVENT_TYPE_CODE = 'PAYMENT CLEARING') Check_Accounting_Date,
(select aae.EVENT_STATUS_CODE from ap_accounting_events_all aae
where aae.SOURCE_ID = apc.CHECK_IDand aae.EVENT_TYPE_CODE = 'INVOICE') Invoice_Event_STatus,
(select aae.ACCOUNTING_DATE from ap_accounting_events_all aae
where aae.SOURCE_ID = apc.CHECK_IDand aae.EVENT_TYPE_CODE = 'INVOICE') Invoice_Accounting_Date,
trunc(apc.creation_date) Check_CR_Date,
apc.CHECK_ID
from AP_INVOICE_PAYMENTS_all aip,
AP_PAYMENT_Schedules_all aps,
ap_checks_all apc,ap_invoices_all ai
where 1=1
and aip.invoice_id = aps.invoice_id
and aps.invoice_id = ai.invoice_id
--and apc.STATUS_LOOKUP_CODE = 'CLEARED BUT UNACCOUNTED'
--and apc.CHECKRUN_NAME = '240409KONCERNZZZ'
and aip.CHECK_ID = apc.CHECK_ID;

========================================================

Organization Hierarchy using the Query

-- ---------------------------------------------------------------------------------
-- Purpose : Organization Hierarchy using the Query
-- Date      : 12.May.2009
-- Created by : Chaitanya
-- .....
-- ----------------------------------------------------------------------------------

select hbg.BUSINESS_GROUP_NAME,
(select NAME
from GL_SETS_OF_BOOKS_V
where set_of_books_id = hle.SET_OF_BOOKS_ID) sob_name,
hou.NAME ou_name,
hle.name le_name,
ood.ORGANIZATION_NAME,
ood.ORGANIZATION_CODE,
msi.SECONDARY_INVENTORY_NAME
from hrfv_business_groups hbg,
hr_operating_units hou,
hr_legal_entities hle,
org_organization_definitions ood,
mtl_secondary_inventories msi
where
hou.BUSINESS_GROUP_ID(+) = hbg.BUSINESS_GROUP_ID
and hle.organization_id(+) = hou.legal_entity_id
and ood.OPERATING_UNIT(+)= hle.ORGANIZATION_ID
and msi.ORGANIZATION_ID(+) = ood.ORGANIZATION_ID;

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

Analysing Rejected Inventory Transactions

-- ---------------------------------------------------------------------------------
-- Purpose : Inventory Interface rejected Records for Analysis
-- Date      : 10.May.2009
-- Created by : Chaitanya
-- .....
-- ----------------------------------------------------------------------------------

SELECT mti.transaction_interface_id,
mti.source_code,
(select segment1 from mtl_system_items_b
where inventory_item_id = mti.inventory_item_id
and organization_id = mti.ORGANIZATION_ID ) Item,
(select decode(count(*),2,'Item Assigned','Not Assigned to One Org') from mtl_system_items_b
where inventory_item_id = mti.inventory_item_id
and organization_id in (mti.ORGANIZATION_ID, mti.TRANSFER_ORGANIZATION)) Item_assignment_STatus,
decode(sign(nvl((select transaction_quantity Availability from mtl_onhand_quantities
where inventory_item_id = mti.inventory_item_id
and organization_id = mti.organization_id
and subinventory_code = mti.SUBINVENTORY_CODE),0)+(mti.transaction_quantity)),-1,'Qty_Not_Available','Qty_Available') On_Hand_Availability_sign,
(select decode(count(*),1,'Y','N') Shipping_Network from MTL_SHIPPING_NETWORK_VIEW
where from_organization_id = mti.organization_id
and to_organization_id = mti.TRANSFER_ORGANIZATION) Shipping_Network,
(select organization_code from org_organization_definitions
where organization_id= mti.organization_id) from_org,
(select decode( sum(restrict_subinventories_code), 4,'Restriction N/A', 'Restrict_Subinv Enabled')
from mtl_system_items_b msi
where msi.inventory_item_id = mti.inventory_item_id
and organization_id in (mti.TRANSFER_ORGANIZATION,mti.organization_id)) Restrict_Subinv_Check,
(select decode( sum(restrict_locators_code), 4,'Restriction N/A', 'Restrict_Locator Enabled')
from mtl_system_items_b msi
where msi.inventory_item_id = mti.inventory_item_id
and organization_id in (mti.TRANSFER_ORGANIZATION,mti.organization_id)) Restrict_Locator_Check,
mti.subinventory_code from_Subinv,
(select organization_code from org_organization_definitions
where organization_id= mti.TRANSFER_ORGANIZATION) to_org,
mti.TRANSFER_ORGANIZATION,
mti.transfer_subinventory to_subinv,
mti.acct_period_id,
mti.transaction_date,
mti.ERROR_CODE,
mti.error_explanation
from mtl_transactions_interface mti
where process_flag = 3;

-- ---------------------------------------------------------------------------------
-- Purpose : To translate the oracle standard messages to English
-- Date : 22.May.2009
-- Created by : Chaitanya
-- .....
-- ----------------------------------------------------------------------------------


SELECT message_name,message_text from fnd_new_messages
where LANGUAGE_CODE = 'US'
and message_name in
(select message_name from fnd_new_messages
where message_text = 'Ugyldig overføringslageravdeling' --:ERROR_CODE
and LANGUAGE_CODE = 'N');

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

Welcome to Chaitanya's Blog

Hi All,

I am Senior Consultant (Oracle ERP - Development and Support), Working for Ernst & Young, India.
This blog opened for discussions on Oracle-ERP and knowledge sharing on Oracle Technologies.

--
Chaitanya