FUNCTION xx_abhi_filename_change (p_filename IN VARCHAR2)
RETURN VARCHAR2

IS
v_fname VARCHAR2 (50);
v_file_extn VARCHAR2 (16);
v_new_fname VARCHAR2 (50);
v_file_name VARCHAR2 (50);
BEGIN
v_file_name := p_filename;

BEGIN
SELECT SUBSTR (v_file_name, 1, INSTR (v_file_name, '.') - 1)
INTO v_fname
FROM DUAL;
EXCEPTION
WHEN OTHERS
THEN
v_fname := NULL;
END;

BEGIN
SELECT TO_CHAR (SYSDATE, 'DDMonYYYY.HHMMSS')
INTO v_file_extn
FROM DUAL;
EXCEPTION
WHEN OTHERS
THEN
v_file_extn := NULL;
END;

v_new_fname := v_fname || '.' || v_file_extn;
RETURN v_new_fname;
EXCEPTION
WHEN OTHERS
THEN
RETURN v_file_name;
END xx_tp_abhi_filename_change;

prompt
ACCEPT request prompt 'Please enter the concurrent request id for the appropriate concurrent program:'
prompt

COLUMN traceid format a8
COLUMN tracename format a80
COLUMN user_concurrent_program_name format a40
COLUMN execname format a15
COLUMN enable_trace format a12
SET lines 80
SET pages 22
SET head OFF

SELECT
req.request_id
,req.logfile_node_name node
,req.oracle_Process_id
,req.enable_trace
,dest.VALUE||'/'||LOWER(dbnm.VALUE)||'_ora_'||oracle_process_id||'.trc' trace_filename
,prog.user_concurrent_program_name
,execname.execution_file_name
,execname.subroutine_name
,phase_code
,status_code
,ses.SID
,ses.serial#
,ses.module
,ses.machine
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
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

THANKS& REGARDS

ABHI CHOUDARY.KOLLI

DECLARE
MSG VARCHAR2(32767);
SV_ORDER_NO_REC SYS.DBMS_DEBUG_VC2COLL := SYS.DBMS_DEBUG_VC2COLL();
CURSOR C1 IS
SELECT *
FROM XXCUS.XX_TPALL_ORDERS WHERE PROCESS_FLAG=3
AND SV_ORDER_ID IN
-- PASS SV ORDER IDS BELOW
(24453,24471,24386)
---(SELECT * FROM TABLE (CAST ( SV_ORDER_NO_REC AS SYS.DBMS_DEBUG_VC2COLL )))
ORDER BY SV_ORDER_NO;
--AND TRUNC(CREATION_DATE)>=TRUNC(SYSDATE-100);
CURSOR C2(P_PARTY_ID NUMBER) IS -- SHIP TO ERROR CHECK
SELECT *
FROM XXCUS.XX_TPALL_CUST_ADDRESS XTA
WHERE XTA.PARTY_ID =P_PARTY_ID
AND XTA.PROCESS_FLAG = 3;
CURSOR C3(P_PARTY_ID NUMBER) IS -- MEDIA SOURCE ERROR CHECK
SELECT *
FROM XXCUS.XX_TPALL_PARTIES XTP
WHERE XTP.PROCESS_FLAG = 3
AND XTP.ID = P_PARTY_ID;
--AND TRUNC (XTO.CREATION_DATE) >= TRUNC(SYSDATE-1);
CURSOR C4(P_SHIP_TO_PARTY_ID NUMBER) IS
SELECT RAA.* FROM
APPS.RA_ADDRESSES_ALL RAA
,APPS.RA_SITE_USES_ALL RSU
,XXCUS.XX_TPALL_CUST_ADDRESS XTA
WHERE RAA.ADDRESS_ID=RSU.ADDRESS_ID
AND RSU.SITE_USE_ID=XTA.TP_CUST_ACCT_SITE_USE_ID
AND XTA.PARTY_ID= P_SHIP_TO_PARTY_ID
AND UPPER (XTA.ADDRESS_USE_TYPE) = 'SHIP_TO';
C4_REC C4%ROWTYPE;
BEGIN
/*
-- PASS SV_ORDER_IDS BELOW
SV_ORDER_NO_REC.EXTEND;
SV_ORDER_NO_REC(1):=23954;
SV_ORDER_NO_REC.EXTEND;
SV_ORDER_NO_REC(2):=23962;
SV_ORDER_NO_REC.EXTEND;
SV_ORDER_NO_REC(3):=24300;
SV_ORDER_NO_REC.EXTEND;
SV_ORDER_NO_REC(4):=24308;
SV_ORDER_NO_REC.EXTEND;
SV_ORDER_NO_REC(5):=24184;
/*SV_ORDER_NO_REC.EXTEND;
SV_ORDER_NO_REC(6):=10726;
SV_ORDER_NO_REC.EXTEND;
SV_ORDER_NO_REC(7):=10821;
*/
DBMS_OUTPUT.PUT_LINE(RPAD('SV ORDER ID.',20)||' ERROR MESSAGE');
FOR C1_REC IN C1 LOOP
IF UPPER(C1_REC.ERROR_MSG) LIKE UPPER('%CALLING OE_ORDER_PUB.PROCESS_ORDER RETURN STATUS%') THEN
--DBMS_OUTPUT.PUT_LINE
--MSG:=(RPAD(C1_REC.SV_ORDER_NO,10)|| ' '||C1_REC.ERROR_MSG);
IF C1_REC.SHIP_TO_PARTY_ID IS NULL THEN
DBMS_OUTPUT.PUT_LINE(RPAD(C1_REC.SV_ORDER_ID,20)|| ' '||'ORDER FAILED DUE TO ORACLE API VALIDATION ERRORS, SHIP_TO_PARTY_ID IS NULL.');
ELSE
IF C1_REC.SHIP_TO_PARTY_ID IS NOT NULL THEN
OPEN C4(C1_REC.SHIP_TO_PARTY_ID) ;
FETCH C4 INTO C4_REC;
CLOSE C4;
IF C4_REC.CITY IN ('APO','FPO') THEN
DBMS_OUTPUT.PUT_LINE(RPAD(C1_REC.SV_ORDER_ID,20)|| ' '||'ORDER FAILED DUE TO ORACLE API VALIDATION ERRORS, LOOKS LIKE A MILITARY ORDER WITH FOLLOWING ADDRESS DETAILS; ADDRESS1: '||C4_REC.ADDRESS1||', CITY: '||C4_REC.CITY||', POSTAL CODE: '||C4_REC.POSTAL_CODE||'.');
END IF;
END IF;
--DBMS_OUTPUT.PUT_LINE(RPAD(C1_REC.SV_ORDER_ID,20)|| ' '||'ORDER FAILED DUE TO ORACLE API VALIDATION ERRORS, CHECK THE ERROR IN STAGING TABLE');
END IF;
ELSIF UPPER(C1_REC.ERROR_MSG) LIKE '%SHIP%TO%' THEN
FOR C2_REC IN C2(C1_REC.PARTY_ID) LOOP
IF UPPER(C2_REC.ERROR_MSG) LIKE UPPER('%COUNTY CODE DOES NOT EXISTS FOR CITY%') THEN
--DBMS_OUTPUT.PUT_LINE
MSG:=(RPAD(C1_REC.SV_ORDER_ID,20)||' COUNTY CODE DOES NOT EXISTS FOR CITY - '
|| UPPER (C2_REC.CITY)
|| ', STATE - '
|| UPPER (C2_REC.STATE)
|| ', POSTAL CODE - '
|| C2_REC.PO_BOX_NUMBER
|| ' COMBINATION.');
DBMS_OUTPUT.PUT_LINE(MSG);
END IF;
END LOOP;
ELSIF UPPER(C1_REC.ERROR_MSG) LIKE UPPER('%CUST ACCOUNT ID DOES NOT EXISTS%') THEN
FOR C3_REC IN C3(C1_REC.PARTY_ID) LOOP
IF UPPER(C3_REC.ERROR_MSG) LIKE UPPER('%INVALID MEDIA SOURCE CODE%') THEN
--DBMS_OUTPUT.PUT_LINE
MSG:=(RPAD(C1_REC.SV_ORDER_ID,20)||' MEDIA SOURCE '||C3_REC.ADDNL_FIELD5 ||' WAS NOT FOUND IN ORACLE. AND HENCE PARTY COULD NOT BE CREATED IN ORACLE WHICH RESULTED IN FAILURE OF THE PROCESS.');
DBMS_OUTPUT.PUT_LINE(MSG);
END IF;
END LOOP;
ELSIF UPPER(C1_REC.ERROR_MSG) LIKE UPPER('%VALIDATION FOR SALES PERSON%') THEN
--DBMS_OUTPUT.PUT_LINE
MSG:=(RPAD(C1_REC.SV_ORDER_ID,20)|| SUBSTR(C1_REC.ERROR_MSG,INSTR(C1_REC.ERROR_MSG,' PLEASE')) ||' DOES NOT EXIST IN ORACLE.');
DBMS_OUTPUT.PUT_LINE(MSG);
ELSE
DBMS_OUTPUT.PUT_LINE(RPAD(C1_REC.SV_ORDER_ID,20)|| ' '||SUBSTR(C1_REC.ERROR_MSG,1,225));
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR IN GET_ERRORS => '||SQLERRM);
END;
/

select DISTINCT
ACCT_SITE.CUST_ACCOUNT_ID CUSTOMER_ID,
SUBSTRB(PARTY.PARTY_NAME,1,50) CUSTOMER_NAME,
CUST_ACCT.ACCOUNT_NUMBER CUSTOMER_NUMBER,
loc.address1 Address1,
loc.city City,
loc.state State,
h.transactional_curr_code Currency1,
ccr.name Entry_Credit_Check_Rule,
nvl(ot.entry_credit_check_rule_id,-1) Entry_Rule_Id,
ccr.open_ar_balance_flag Entry_Open_Ar_Flag,
ccr.open_ar_days Entry_Open_Ar_Days,
--ccr.uninvoiced_orders_flag Entry_Uninvoiced_Flag,
--ccr.orders_on_hold_flag Entry_On_Hold_Flag,
nvl(ccr.shipping_interval,-1) Entry_Shipping_Interval,
ccr2.name Ship_Credit_Check_Rule,
nvl(ot.shipping_credit_check_rule_id ,-1) Ship_Rule_Id,
ccr2.open_ar_balance_flag Ship_Open_Ar_Flag,
ccr2.open_ar_days Ship_Open_Ar_Days,
ccr2.uninvoiced_orders_flag Ship_Uninvoiced_Flag,
ccr2.orders_on_hold_flag Ship_On_Hold_Flag,
nvl(ccr2.shipping_interval,-1) Ship_Shipping_Interval,
0 Site_Use_Id,
h.order_number Order_Number,
h.ordered_date Order_Date,
sum (decode(l.line_category_code,'ORDER',
(nvl(l.ordered_quantity,0)* nvl(l.unit_selling_price,0)),
-1 * (nvl(l.ordered_quantity,0)* nvl(l.unit_selling_price,0))
)) order_amount,
oh.creation_date Date_Placed_On_Hold,
round(sysdate - oh.creation_date) Days_On_Hold,
loc.address2,
loc.address3,
loc.postal_code,
h.cust_po_number,h.header_id
,cols.name collector_name,
oh.order_hold_id,
oh.attribute1 notification_date
from oe_hold_sources hs,
oe_order_holds oh,
oe_order_headers h,
oe_order_lines l,
oe_order_types_v ot,
oe_credit_check_rules ccr,
oe_credit_check_rules ccr2,
RA_SITE_USES_ALL SU,
HZ_PARTY_SITES PARTY_SITE,
HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
HZ_LOCATIONS LOC,
HZ_CUST_ACCT_SITES_ALL ACCT_SITE,
HZ_PARTIES PARTY,
HZ_CUST_ACCOUNTS CUST_ACCT,
ar_customer_profiles cp,
hz_customer_profiles prof,
ar_collectors cols
where
hs.hold_id = 1
-- and hs.hold_entity_code = 'O'
and hs.released_flag = 'N'
and oh.hold_source_id = hs.hold_source_id
and oh.line_id is null
and oh.hold_release_id is null
and h.header_id = oh.header_id
and h.header_id = l.header_id(+)
and h.order_type_id = ot.order_type_id
and NVL(h.cancelled_flag, 'N') = 'N'
and ot.entry_credit_check_rule_id = ccr.credit_check_rule_id(+)
and ot.shipping_credit_check_rule_id = ccr2.credit_check_rule_id(+)
and h.invoice_to_org_id = su.site_use_id(+)
AND SU.ADDRESS_ID = ACCT_SITE.CUST_ACCT_SITE_ID(+)
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
AND NVL(ACCT_SITE.ORG_ID, -99) = NVL(LOC_ASSIGN.ORG_ID, -99)
AND ACCT_SITE.CUST_ACCOUNT_ID = CUST_ACCT.CUST_ACCOUNT_ID(+)
AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
and h.invoice_to_org_id = cp.site_use_id(+)
and cust_acct.cust_account_id = prof.cust_account_id
and prof.site_use_id is null
and prof.collector_id = cols.collector_id
and CUST_ACCT.ACCOUNT_NUMBER = :p_customer_number
GROUP BY
ACCT_SITE.CUST_ACCOUNT_ID ,
SUBSTRB(PARTY.PARTY_NAME,1,50) ,
CUST_ACCT.ACCOUNT_NUMBER ,
loc.address1,loc.city,loc.state,
h.transactional_curr_code,
ccr.name,
nvl(ot.entry_credit_check_rule_id,-1),
ccr.open_ar_balance_flag,
ccr.open_ar_days,
--ccr.uninvoiced_orders_flag,
--ccr.orders_or_hold_slag,
nvl(ccr.shipping_interval,-1),
ccr2.name,
nvl(ot.shipping_credit_check_rule_id,-1),
ccr2.open_ar_balance_flag,
ccr2.open_ar_days,
ccr2.uninvoiced_orders_flag,
ccr2.orders_on_hold_flag,
nvl(ccr2.shipping_interval,-1),
0,
h.order_number,
h.ordered_date,
oh.creation_date,
sysdate - oh.creation_date,loc.address2,
loc.address3,
loc.postal_code,
h.cust_po_number,h.header_id
,cols.name,
oh.order_hold_id,
oh.attribute1
having
sum(nvl(l.ordered_quantity,0)) > 0

SELECT DISTINCT
TO_CHAR (SYSDATE, 'MM/DD/YY') AS "TODAY"
,pov.VENDOR_ID AS "CUSTOMER SUPPLIER ID"
-- TAXID # (or) D&B Number
,pov.VENDOR_NAME AS "SUPPLIER NAME"
,pov.SEGMENT1 AS "VENDOR NUMBER"
,pvsa.ADDRESS_LINE1 AS "STREET ADDRESS1"
,pvsa.CITY AS "CITY"
,pvsa.STATE AS "STATE"
,pvsa.ZIP AS "POSTAL CODE"
,pvsa.COUNTRY AS "COUNTRY"
,pov.STANDARD_INDUSTRY_CLASS AS "Industry OR Commodity Code"
,apia.INVOICE_AMOUNT AS "NET AMOUNT OF INVOICE"
,apia.INVOICE_CURRENCY_CODE AS "CURRENCY CODE"
,pha.SEGMENT1 AS "Purchase Order Number"
,pha.TYPE_LOOKUP_CODE AS "Purchase Method"
,apia.INVOICE_NUM AS "Invoice Number"
,apia.INVOICE_TYPE_LOOKUP_CODE AS "Invoice Method"
,TO_CHAR (apia.INVOICE_DATE, 'MM/DD/YYYY') AS "Invoice Date"
,TO_CHAR (apia.CREATION_DATE, 'MM/DD/YYYY') AS "CREATION_DATE"
,aipa.PAYMENT_NUM AS "Payment Number"
,apsa.PAYMENT_METHOD_LOOKUP_CODE AS "Payment Method"
,att.NAME AS "Payment Terms"
,apsa.DUE_DATE AS "Payment Date"
--days payable outstanding
,gsob.NAME AS "General Ledger"
,hbg.BUSINESS_GROUP_NAME AS "Org OR BU"
,hl1.LOCATION_CODE AS "location/plant/division"
,aipa.DISCOUNT_LOST AS "Discount Taken"
,aipa.DISCOUNT_TAKEN AS "Discount Lost"
--optional field5
,apia.DESCRIPTION
,aida.PERIOD_NAME
-- aipa.BANK_ACCOUNT_NUM,
-- aipa.EXTERNAL_BANK_ACCOUNT_ID,
-- aipa.ELECTRONIC_TRANSFER_ID,
,TO_NUMBER (aca.CHECK_NUMBER) AS "CHECK_NUMBER"
,TO_CHAR (aca.CHECK_DATE, 'MM/DD/YYYY') AS "CHECK_DATE"
,aca.STATUS_LOOKUP_CODE
-- aca.CLEARED_DATE,
,apsa.DISCOUNT_AMOUNT_AVAILABLE
,apsa.DISCOUNT_AMOUNT_REMAINING
,apsa.PAYMENT_STATUS_FLAG
,aida.AMOUNT
,aida.DESCRIPTION AS dist_desc
,gcc.SEGMENT1
,gcc.SEGMENT2
,gcc.segment3
,gcc.segment4
FROM po.PO_VENDORS pov
,po.PO_VENDOR_SITES_ALL pvsa
,ap.AP_INVOICES_ALL apia
,ap.AP_INVOICE_PAYMENTS_ALL aipa
,gl.GL_CODE_COMBINATIONS gcc
,ap.AP_INVOICE_DISTRIBUTIONS_ALL aida
,ap.AP_PAYMENT_SCHEDULES_ALL apsa
,ap.AP_TERMS_TL att
,ap.AP_CHECKS_ALL aca
,po.PO_DISTRIBUTIONS_ALL pda
,po.PO_HEADERS_ALL pha
,gl.GL_SETS_OF_BOOKS gsob
,apps.HRFV_BUSINESS_GROUPS hbg
,apps.HR_LOCATIONS hl1
,apps.HR_LOCATIONS hl2
WHERE 1 = 1
AND pvsa.VENDOR_SITE_ID = pha.VENDOR_SITE_ID
/*apia.ORG_ID = &&5
and (apia.CREATION_DATE between to_date(nvl('&&1',to_char(sysdate+1,'YYYY/MM/DD') || '00:00:00'),'YYYY/MM/DD HH24:MI:SS') and to_date(nvl(substr('&&2',1,10),nvl(substr('&&1',1,10),to_char(sysdate + 1,'YYYY/MM/DD') )) || '23:59:59','YYYY/MM/DD HH24:MI:SS')
or aida.PERIOD_NAME in (nvl('&&1','&&3'),nvl('&&1','&&4')) )*/
AND pov.VENDOR_ID = apia.VENDOR_ID
AND apia.INVOICE_ID = aida.INVOICE_ID(+)
AND apia.INVOICE_ID = apsa.INVOICE_ID(+)
AND apia.INVOICE_ID = aipa.INVOICE_ID(+)
AND apia.TERMS_ID = att.TERM_ID
AND aida.DIST_CODE_COMBINATION_ID = gcc.CODE_COMBINATION_ID
AND aipa.CHECK_ID = aca.CHECK_ID(+)
AND aida.PO_DISTRIBUTION_ID = pda.PO_DISTRIBUTION_ID(+)
AND pda.PO_HEADER_ID = pha.PO_HEADER_ID(+)
AND POV.SHIP_TO_LOCATION_ID = HL1.SHIP_TO_LOCATION_ID
AND POV.BILL_TO_LOCATION_ID = HL2.SHIP_TO_LOCATION_ID
AND POV.SET_OF_BOOKS_ID = GSOB.SET_OF_BOOKS_ID
AND HBG.LOCATION_ID = HL1.LOCATION_ID
AND ROWNUM<100