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;
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
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;
/
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
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
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
The concurrent managers in the Oracle e-Business suite serve several important administrative functions. Foremost, the concurrent managers ensure that the applications are not overwhelmed with requests, and the second areas of functions are the management of batch processing and report generation.
This article will explore tools that are used by experienced administrators to gain insight and improved control over the concurrent management functions. We will explore how the concurrent managers can be configured via the GUI, and also explore scripts and dictionary queries that are used to improve the functionality of concurrent management.
The Master Concurrent Managers
There is a lot of talk about "the" concurrent manager in Oracle Applications. Actually, there are many Concurrent Managers, each governing flow within each Oracle Apps areas. In addition there are "super" Concurrent Managers whose job is to govern the behavior of the slave Concurrent Managers. The Oracle e-Business suite has three important master Concurrent Managers:
Internal Concurrent Manager — The master manager is called the Internal Concurrent Manager (ICM) because it controls the behavior of all of the other managers, and because the ICM is the boss, it must be running before any other managers can be activated. The main functions of the ICM are to start up and shutdown the individual concurrent managers, and reset the other managers after one them has a failure.
Standard Manager — Another important master Concurrent Manager is called the Standard Manager (SM). The SM functions to run any reports and batch jobs that have not been defined to run in any specific product manager. Examples of specific concurrent managers include the Inventory Manager, CRP Inquiry Manager, and the Receivables Tax Manager.
Conflict Resolution Manager — The Conflict Resolution Manager (CRM) functions to check concurrent program definitions for incompatibility rules. However, the ICM can be configured to take over the CRM's job to resolve incompatibilities.
Now that we understand the functions of the master Concurrent Managers, let's take a quick look at techniques that are used by Oracle Apps DBAs to monitor the tune the behavior of the Concurrent Managers.
Tuning the Concurrent Manager
All successful Oracle Apps DBAs must understand how to monitor and tune each of the Concurrent Managers. This article will explore some of the important techniques for monitoring and tuning the Oracle Apps Concurrent Manager processes. The topics will include:
Tuning the Concurrent Manager
Tuning the Internal Concurrent Manager
Purging Concurrent Requests
Troubleshooting Oracle Apps performance problems
Adjusting the Concurrent Manager Cache Size
Analyzing the Oracle Apps Dictionary Tables
Monitoring Pending Requests in the Concurrent Manager
Changing the dispatching priority within the Concurrent Manager
Let's start by looking at tuning the ICM, and drill-down into more detail.
Tuning the Internal Concurrent Manager (ICM)
The ICM performance is affected by the three important Oracle parameters PMON cycle, queue size, and sleep time.
PMON cycle — This is the number of sleep cycles that the ICM waits between the time it checks for concurrent managers failures, which defaults to 20. You should change the PMON cycle to a number lower than 20 if your concurrent managers are having problems with abnormal terminations.
Queue Size — The queue size is the number of PMON cycles that the ICM waits between checking for disabled or new concurrent managers. The default for queue size of 1 PMON cycle should be used.
Sleep Time — The sleep time parameter indicates the seconds that the ICM should wait between checking for requests that are waiting to run. The default sleep time is 60, but you can lower this number if you see you have a lot of request waiting (Pending/Normal). However, reducing this number to a very low value many cause excessive cpu utilization.
All of the concurrent managers, with the exception of the ICM and CRM, can be configured to run as many processes as needed, as well as the time and days a manager can process requests. However, the number of processes needed is dependent on each organization's environment. An Applications DBA must monitor the concurrent processing in order to decide how to configure each manager. For a fresh install of the applications, initially configure the standard manager to run with five processes, and all the other managers with two processes. After the applications have been in operation for a while, the concurrent managers should be monitored to determine is more operating system process should be allocated.
Purging Concurrent Requests
One important area of Concurrent Manager tuning is monitoring the space usage for the subsets within each concurrent manager. When the space in FND_CONCURRENT_PROCESSES and FND_CONCURRENT_REQUESTS exceed 50K, you can start to experience serious performance problems within your Oracle Applications. When you experience these space problems, a specific request called "Purge Concurrent Requests And/Or Manager Data" should be scheduled to run on a regular basis. This request can be configured to purge the request data from the FND tables as well as the log files and output files on accumulate on disk.
Adjusting the Concurrent Manager Cache Size
Concurrent manager performance can also be enhanced by increasing the manager cache size to be at lease twice the number of target processes. The cache size specifies the number of requests that will be cached each time the concurrent manager reads from the FND_CONCURRENT_REQUESTS table. Increasing the cache size will boost the throughput of the managers by attempting to avoid sleep time.
Analyzing Oracle Apps Dictionary Tables for High Performance
It is also very important to run the request Gather Table Statistics on these tables:
FND_CONCURRENT_PROCESSES
FND_CONCURRENT_PROGRAMS
FND_CONCURRENT_REQUESTS
FND_CONCURRENT_QUEUES.
Run the request "Analyze All Index Column Statistics" on the indexes of these tables. Since the APPLSYS user is the owner of these tables, so you can also just run the request Analyze Schema Statistics for APPLSYS.
To troubleshoot performance, a DBA can use three types of trace. A module trace, such as PO or AR, can be set by enabling the module's profile option Debug Trace from within the applications. Second, most concurrent requests can be set to generate a trace file by changing the request parameters. To enable trace for a specific request, log in as a user with the System Administrator responsibility. Navigate to Concurrent -> Program -> Define. Query for the request that you want to enable trace. At the bottom right of the screen you can check the box Enable Trace. (Figure 1)
Figure 1: Troubleshooting Concurrent Manager Performance.
Another popular way to troubleshoot the Concurrent Managers is to generate a trace file. This is done by setting the OS environment variable FNDSQLCHK to FULL, and running the request from the command line.
Monitoring Pending Requests in the Concurrent Managers
Occasionally, you may find that requests are stacking up in the concurrent managers with a status of "pending". This can be caused by any of these conditions:
1. The concurrent managers were brought down will a request was running.
2. The database was shutdown before shutting down the concurrent managers.
3. There is a shortage of RAM memory or CPU resources.
When you get a backlog of pending requests, you can first allocate more processes to the manager that is having the problem in order to allow most of the requests to process, and then make a list of the requests that will not complete so they can be resubmitted, and cancel them.
To allocate more processes to a manager, log in as a user with the System Administrator responsibility. Navigate to Concurrent -> Manager -> Define. Increase the number in the Processes column. Also, you may not need all the concurrent managers that Oracle supplies with an Oracle Applications install, so you can save resources by identifying the unneeded managers and disabling them.
Figure 2: Allocating more processes to the Concurrent Manager.
However, you can still have problems. If the request remains in a phase of RUNNING and a status of TERMINATING after allocating more processes to the manager, then shutdown the concurrent managers, kill any processes from the operating system that won't terminate, and execute the following sqlplus statement as the APPLSYS user to reset the managers in the FND_CONCURRENT_REQUESTS table:
update fnd_concurrent_requests
set status_code='X', phase_code='C'
where status_code='T';
Changing Dispatching Priority within the Concurrent Manager
If there are requests that have a higher priority to run over other requests, you can navigate to Concurrent --> Program --> Define to change the priority of a request. If a priority is not set for a request, it will have the same priority as all other requests, or it will be set to the value specified in the user's profile option Concurrent:Priority.
Also, you can specify that a request run using an SQL optimizer mode of FIRST_ROWS, ALL_ROWS, RULE, or CHOOSE, and this can radically effect the performance of the SQL inside the Concurrent request. If several long running requests are submitted together, they can cause fast running requests to have to wait unnecessarily. If this is occurring, try to schedule as many long running requests to run after peak business hours. Additionally, a concurrent manager can be created to run only fast running requests.
Using data Dictionary Scripts with the Concurrent Manager
Few Oracle Applications DBAs understand that sophisticated data dictionary queries can be run to reveal details about the workings within each Concurrent Manager. Oracle provides several internal tables that can be queried from SQL*Plus to see the status of the concurrent requests, and the most important are FND_CONCURRENT_PROGRAMS and FND_CONCURRENT_REQUESTS.
Oracle supplies several useful scripts, (located in $FND_TOP/sql directory), for monitoring the concurrent managers:
afcmstat.sql Displays all the defined managers, their maximum capacity, pids, and their status.
afimchk.sql Displays the status of ICM and PMON method in effect, the ICM's log file, and determines if the concurrent manger monitor is running.
afcmcreq.sql Displays the concurrent manager and the name of its log file that processed a request.
afrqwait.sql Displays the requests that are pending, held, and scheduled.
afrqstat.sql Displays of summary of concurrent request execution time and status since a particular date.
afqpmrid.sql Displays the operating system process id of the FNDLIBR process based on a concurrent request id. The process id can then be used with the ORADEBUG utility.
afimlock.sql Displays the process id, terminal, and process id that may be causing locks that the ICM and CRM are waiting to get. You should run this script if there are long delays when submitting jobs, or if you suspect the ICM is in a gridlock with another oracle process.
In addition to these canned scripts you can skill write custom Concurrent Manager scripts. For example, the following query can be executed to identify requests based on the number of minutes the request ran:
conc_stat.sql
set echo off
set feedback off
set linesize 97
set verify off
col request_id format 9999999999 heading "Request ID"
col exec_time format 999999999 heading "Exec Time|(Minutes)"
col start_date format a10 heading "Start Date"
col conc_prog format a20 heading "Conc Program Name"
col user_conc_prog format a40 trunc heading "User Program Name"
spool long_running_cr.lst
SELECT
fcr.request_id request_id,
TRUNC(((fcr.actual_completion_date-fcr.actual_start_date)/(1/24))*60) exec_time,
fcr.actual_start_date start_date,
fcp.concurrent_program_name conc_prog,
fcpt.user_concurrent_program_name user_conc_prog
FROM
fnd_concurrent_programs fcp,
fnd_concurrent_programs_tl fcpt,
fnd_concurrent_requests fcr
WHERE
TRUNC(((fcr.actual_completion_date-fcr.actual_start_date)/(1/24))*60) > NVL('&min',45)
and
fcr.concurrent_program_id = fcp.concurrent_program_id
and
fcr.program_application_id = fcp.application_id
and
fcr.concurrent_program_id = fcpt.concurrent_program_id
and
fcr.program_application_id = fcpt.application_id
and
fcpt.language = USERENV('Lang')
ORDER BY
TRUNC(((fcr.actual_completion_date-fcr.actual_start_date)/(1/24))*60) desc;
spool off
Note that this script prompts you for the number of minutes. The output from this query with a value of 60 produced the following output on my database. Here we can see important details about currently-running requests, including the request ID, the execution time, the user who submitted the program and the name of the program.
Enter value for min: 60
Exec Time
Request ID (Minutes) Start Date Conc Program Name User Program Name
----------- ---------- ---------- -------------------- --------------------------------------
1445627 218 01-SEP-02 MWCRMRGA Margin Analysis Report(COGS Breakups)
444965 211 03-JUL-01 CSTRBICR5G Cost Rollup - No Report GUI
1418262 208 22-AUG-02 MWCRMRGA Margin Analysis Report(COGS Breakups)
439443 205 28-JUN-01 CSTRBICR5G Cost Rollup - No Report GUI
516074 178 10-AUG-01 CSTRBICR6G Cost Rollup - Print Report GUI
1417551 164 22-AUG-02 MWCRMRGA Margin Analysis Report(COGS Breakups)
1449973 160 03-SEP-02 MWCRMRGA Margin Analysis Report(COGS Breakups)
520648 159 13-AUG-01 CSTRBICR5G Cost Rollup - No Report GUI
446007 122 03-JUL-01 CSTRBICR5G Cost Rollup - No Report GUI
392996 120 01-JUN-01 BMCOIN Bill and Routing Interface
Conclusion
The Oracle Concurrent Managers remain on of Oracle most important components for the Oracle Applications eBusiness suite and they perform an important TP monitor function. It is only by properly understand the functions and tuning of the Concurrent Managers that the Oracle Apps DBA can be successful in keep their sophisticated Applications optimizer for high-performance.
This article will explore tools that are used by experienced administrators to gain insight and improved control over the concurrent management functions. We will explore how the concurrent managers can be configured via the GUI, and also explore scripts and dictionary queries that are used to improve the functionality of concurrent management.
The Master Concurrent Managers
There is a lot of talk about "the" concurrent manager in Oracle Applications. Actually, there are many Concurrent Managers, each governing flow within each Oracle Apps areas. In addition there are "super" Concurrent Managers whose job is to govern the behavior of the slave Concurrent Managers. The Oracle e-Business suite has three important master Concurrent Managers:
Internal Concurrent Manager — The master manager is called the Internal Concurrent Manager (ICM) because it controls the behavior of all of the other managers, and because the ICM is the boss, it must be running before any other managers can be activated. The main functions of the ICM are to start up and shutdown the individual concurrent managers, and reset the other managers after one them has a failure.
Standard Manager — Another important master Concurrent Manager is called the Standard Manager (SM). The SM functions to run any reports and batch jobs that have not been defined to run in any specific product manager. Examples of specific concurrent managers include the Inventory Manager, CRP Inquiry Manager, and the Receivables Tax Manager.
Conflict Resolution Manager — The Conflict Resolution Manager (CRM) functions to check concurrent program definitions for incompatibility rules. However, the ICM can be configured to take over the CRM's job to resolve incompatibilities.
Now that we understand the functions of the master Concurrent Managers, let's take a quick look at techniques that are used by Oracle Apps DBAs to monitor the tune the behavior of the Concurrent Managers.
Tuning the Concurrent Manager
All successful Oracle Apps DBAs must understand how to monitor and tune each of the Concurrent Managers. This article will explore some of the important techniques for monitoring and tuning the Oracle Apps Concurrent Manager processes. The topics will include:
Tuning the Concurrent Manager
Tuning the Internal Concurrent Manager
Purging Concurrent Requests
Troubleshooting Oracle Apps performance problems
Adjusting the Concurrent Manager Cache Size
Analyzing the Oracle Apps Dictionary Tables
Monitoring Pending Requests in the Concurrent Manager
Changing the dispatching priority within the Concurrent Manager
Let's start by looking at tuning the ICM, and drill-down into more detail.
Tuning the Internal Concurrent Manager (ICM)
The ICM performance is affected by the three important Oracle parameters PMON cycle, queue size, and sleep time.
PMON cycle — This is the number of sleep cycles that the ICM waits between the time it checks for concurrent managers failures, which defaults to 20. You should change the PMON cycle to a number lower than 20 if your concurrent managers are having problems with abnormal terminations.
Queue Size — The queue size is the number of PMON cycles that the ICM waits between checking for disabled or new concurrent managers. The default for queue size of 1 PMON cycle should be used.
Sleep Time — The sleep time parameter indicates the seconds that the ICM should wait between checking for requests that are waiting to run. The default sleep time is 60, but you can lower this number if you see you have a lot of request waiting (Pending/Normal). However, reducing this number to a very low value many cause excessive cpu utilization.
All of the concurrent managers, with the exception of the ICM and CRM, can be configured to run as many processes as needed, as well as the time and days a manager can process requests. However, the number of processes needed is dependent on each organization's environment. An Applications DBA must monitor the concurrent processing in order to decide how to configure each manager. For a fresh install of the applications, initially configure the standard manager to run with five processes, and all the other managers with two processes. After the applications have been in operation for a while, the concurrent managers should be monitored to determine is more operating system process should be allocated.
Purging Concurrent Requests
One important area of Concurrent Manager tuning is monitoring the space usage for the subsets within each concurrent manager. When the space in FND_CONCURRENT_PROCESSES and FND_CONCURRENT_REQUESTS exceed 50K, you can start to experience serious performance problems within your Oracle Applications. When you experience these space problems, a specific request called "Purge Concurrent Requests And/Or Manager Data" should be scheduled to run on a regular basis. This request can be configured to purge the request data from the FND tables as well as the log files and output files on accumulate on disk.
Adjusting the Concurrent Manager Cache Size
Concurrent manager performance can also be enhanced by increasing the manager cache size to be at lease twice the number of target processes. The cache size specifies the number of requests that will be cached each time the concurrent manager reads from the FND_CONCURRENT_REQUESTS table. Increasing the cache size will boost the throughput of the managers by attempting to avoid sleep time.
Analyzing Oracle Apps Dictionary Tables for High Performance
It is also very important to run the request Gather Table Statistics on these tables:
FND_CONCURRENT_PROCESSES
FND_CONCURRENT_PROGRAMS
FND_CONCURRENT_REQUESTS
FND_CONCURRENT_QUEUES.
Run the request "Analyze All Index Column Statistics" on the indexes of these tables. Since the APPLSYS user is the owner of these tables, so you can also just run the request Analyze Schema Statistics for APPLSYS.
To troubleshoot performance, a DBA can use three types of trace. A module trace, such as PO or AR, can be set by enabling the module's profile option Debug Trace from within the applications. Second, most concurrent requests can be set to generate a trace file by changing the request parameters. To enable trace for a specific request, log in as a user with the System Administrator responsibility. Navigate to Concurrent -> Program -> Define. Query for the request that you want to enable trace. At the bottom right of the screen you can check the box Enable Trace. (Figure 1)
Figure 1: Troubleshooting Concurrent Manager Performance.
Another popular way to troubleshoot the Concurrent Managers is to generate a trace file. This is done by setting the OS environment variable FNDSQLCHK to FULL, and running the request from the command line.
Monitoring Pending Requests in the Concurrent Managers
Occasionally, you may find that requests are stacking up in the concurrent managers with a status of "pending". This can be caused by any of these conditions:
1. The concurrent managers were brought down will a request was running.
2. The database was shutdown before shutting down the concurrent managers.
3. There is a shortage of RAM memory or CPU resources.
When you get a backlog of pending requests, you can first allocate more processes to the manager that is having the problem in order to allow most of the requests to process, and then make a list of the requests that will not complete so they can be resubmitted, and cancel them.
To allocate more processes to a manager, log in as a user with the System Administrator responsibility. Navigate to Concurrent -> Manager -> Define. Increase the number in the Processes column. Also, you may not need all the concurrent managers that Oracle supplies with an Oracle Applications install, so you can save resources by identifying the unneeded managers and disabling them.
Figure 2: Allocating more processes to the Concurrent Manager.
However, you can still have problems. If the request remains in a phase of RUNNING and a status of TERMINATING after allocating more processes to the manager, then shutdown the concurrent managers, kill any processes from the operating system that won't terminate, and execute the following sqlplus statement as the APPLSYS user to reset the managers in the FND_CONCURRENT_REQUESTS table:
update fnd_concurrent_requests
set status_code='X', phase_code='C'
where status_code='T';
Changing Dispatching Priority within the Concurrent Manager
If there are requests that have a higher priority to run over other requests, you can navigate to Concurrent --> Program --> Define to change the priority of a request. If a priority is not set for a request, it will have the same priority as all other requests, or it will be set to the value specified in the user's profile option Concurrent:Priority.
Also, you can specify that a request run using an SQL optimizer mode of FIRST_ROWS, ALL_ROWS, RULE, or CHOOSE, and this can radically effect the performance of the SQL inside the Concurrent request. If several long running requests are submitted together, they can cause fast running requests to have to wait unnecessarily. If this is occurring, try to schedule as many long running requests to run after peak business hours. Additionally, a concurrent manager can be created to run only fast running requests.
Using data Dictionary Scripts with the Concurrent Manager
Few Oracle Applications DBAs understand that sophisticated data dictionary queries can be run to reveal details about the workings within each Concurrent Manager. Oracle provides several internal tables that can be queried from SQL*Plus to see the status of the concurrent requests, and the most important are FND_CONCURRENT_PROGRAMS and FND_CONCURRENT_REQUESTS.
Oracle supplies several useful scripts, (located in $FND_TOP/sql directory), for monitoring the concurrent managers:
afcmstat.sql Displays all the defined managers, their maximum capacity, pids, and their status.
afimchk.sql Displays the status of ICM and PMON method in effect, the ICM's log file, and determines if the concurrent manger monitor is running.
afcmcreq.sql Displays the concurrent manager and the name of its log file that processed a request.
afrqwait.sql Displays the requests that are pending, held, and scheduled.
afrqstat.sql Displays of summary of concurrent request execution time and status since a particular date.
afqpmrid.sql Displays the operating system process id of the FNDLIBR process based on a concurrent request id. The process id can then be used with the ORADEBUG utility.
afimlock.sql Displays the process id, terminal, and process id that may be causing locks that the ICM and CRM are waiting to get. You should run this script if there are long delays when submitting jobs, or if you suspect the ICM is in a gridlock with another oracle process.
In addition to these canned scripts you can skill write custom Concurrent Manager scripts. For example, the following query can be executed to identify requests based on the number of minutes the request ran:
conc_stat.sql
set echo off
set feedback off
set linesize 97
set verify off
col request_id format 9999999999 heading "Request ID"
col exec_time format 999999999 heading "Exec Time|(Minutes)"
col start_date format a10 heading "Start Date"
col conc_prog format a20 heading "Conc Program Name"
col user_conc_prog format a40 trunc heading "User Program Name"
spool long_running_cr.lst
SELECT
fcr.request_id request_id,
TRUNC(((fcr.actual_completion_date-fcr.actual_start_date)/(1/24))*60) exec_time,
fcr.actual_start_date start_date,
fcp.concurrent_program_name conc_prog,
fcpt.user_concurrent_program_name user_conc_prog
FROM
fnd_concurrent_programs fcp,
fnd_concurrent_programs_tl fcpt,
fnd_concurrent_requests fcr
WHERE
TRUNC(((fcr.actual_completion_date-fcr.actual_start_date)/(1/24))*60) > NVL('&min',45)
and
fcr.concurrent_program_id = fcp.concurrent_program_id
and
fcr.program_application_id = fcp.application_id
and
fcr.concurrent_program_id = fcpt.concurrent_program_id
and
fcr.program_application_id = fcpt.application_id
and
fcpt.language = USERENV('Lang')
ORDER BY
TRUNC(((fcr.actual_completion_date-fcr.actual_start_date)/(1/24))*60) desc;
spool off
Note that this script prompts you for the number of minutes. The output from this query with a value of 60 produced the following output on my database. Here we can see important details about currently-running requests, including the request ID, the execution time, the user who submitted the program and the name of the program.
Enter value for min: 60
Exec Time
Request ID (Minutes) Start Date Conc Program Name User Program Name
----------- ---------- ---------- -------------------- --------------------------------------
1445627 218 01-SEP-02 MWCRMRGA Margin Analysis Report(COGS Breakups)
444965 211 03-JUL-01 CSTRBICR5G Cost Rollup - No Report GUI
1418262 208 22-AUG-02 MWCRMRGA Margin Analysis Report(COGS Breakups)
439443 205 28-JUN-01 CSTRBICR5G Cost Rollup - No Report GUI
516074 178 10-AUG-01 CSTRBICR6G Cost Rollup - Print Report GUI
1417551 164 22-AUG-02 MWCRMRGA Margin Analysis Report(COGS Breakups)
1449973 160 03-SEP-02 MWCRMRGA Margin Analysis Report(COGS Breakups)
520648 159 13-AUG-01 CSTRBICR5G Cost Rollup - No Report GUI
446007 122 03-JUL-01 CSTRBICR5G Cost Rollup - No Report GUI
392996 120 01-JUN-01 BMCOIN Bill and Routing Interface
Conclusion
The Oracle Concurrent Managers remain on of Oracle most important components for the Oracle Applications eBusiness suite and they perform an important TP monitor function. It is only by properly understand the functions and tuning of the Concurrent Managers that the Oracle Apps DBA can be successful in keep their sophisticated Applications optimizer for high-performance.
1)Define the Mandatroy parameter called P_CONC_REQUEST_ID
2)Call the Following User exit from the Before report Triggers
SRW.USER_EXIT('FND SRWINIT');
3)Call another User Exit from After Report Trigger
SRW.USR_EXIT('FND SRWEXIT');
User Exit:
==========
It is one of the Oracle Reports 6i Built In program which will be used to stop the
report execution and pass the control into some other 3GL and retrieves the data
then complete the remaining report execution Process.
We have 5 User Exits in Oracle Applications:
FND SRWINIT
FND SRWEXIT
FND FLEXSQL
FND FLEXIDVAL
FND FORMATCURRENCY
FND SRWINIT : This User Exit we will initialize user Profile values we will call this
user exit from Before Report Trigger.
FND SRWEXIT: This User Exit will frees the Memory which is Occupied by the User Profile
values in the server. we will call this from After Report Trigger.
We have Five types of Report Triggers :
Before Parameter Form
After Parameter Form
Before Report Trigger
Between Pages
After Report Trigger
We are using Before Report trigger before data is retrieving from data base system
will capture the user profiles data and as per that data will be retrieved from
database.
Once Output is generated we do not reuire the Information in the system for that
we are using after Report Trigger call the another User Exit.
P_CONC_REQUEST_ID : This is One of the mandatory parameter we have to define for every
concurrent Program.when we submit C.P from SRS window which will generate the
REQUEST_ID that request ID will be passed into this variable.
Reports parameter (value sets, defaul types,token
Request group
Reponsibli
user
SRS
user exits.
FND_USER
FND_USER_REPS
Purchasing = functionality 500
Imp tables Join
2)Call the Following User exit from the Before report Triggers
SRW.USER_EXIT('FND SRWINIT');
3)Call another User Exit from After Report Trigger
SRW.USR_EXIT('FND SRWEXIT');
User Exit:
==========
It is one of the Oracle Reports 6i Built In program which will be used to stop the
report execution and pass the control into some other 3GL and retrieves the data
then complete the remaining report execution Process.
We have 5 User Exits in Oracle Applications:
FND SRWINIT
FND SRWEXIT
FND FLEXSQL
FND FLEXIDVAL
FND FORMATCURRENCY
FND SRWINIT : This User Exit we will initialize user Profile values we will call this
user exit from Before Report Trigger.
FND SRWEXIT: This User Exit will frees the Memory which is Occupied by the User Profile
values in the server. we will call this from After Report Trigger.
We have Five types of Report Triggers :
Before Parameter Form
After Parameter Form
Before Report Trigger
Between Pages
After Report Trigger
We are using Before Report trigger before data is retrieving from data base system
will capture the user profiles data and as per that data will be retrieved from
database.
Once Output is generated we do not reuire the Information in the system for that
we are using after Report Trigger call the another User Exit.
P_CONC_REQUEST_ID : This is One of the mandatory parameter we have to define for every
concurrent Program.when we submit C.P from SRS window which will generate the
REQUEST_ID that request ID will be passed into this variable.
Reports parameter (value sets, defaul types,token
Request group
Reponsibli
user
SRS
user exits.
FND_USER
FND_USER_REPS
Purchasing = functionality 500
Imp tables Join
About Me
Followers
kolli.venkat oracle applications
