List all the registered concurrent programs by module
Summary
SQL script to list all the registered concurrent programs by module.
SELECT SUBSTR(a.application_name,1,60) Application_NAME
, b.application_short_name SHORT_NAME
, DECODE(SUBSTR(cp.user_concurrent_program_name,4,1),':'
, 'Concurrent Manager Executable'
, 'Subprogram or Function') TYPE
, SUBSTR(d.concurrent_program_name,1,16) PROGRAM
, SUBSTR(cp.user_concurrent_program_name,1,55) USER_PROGRAM_NAME
FROM applsys.FND_CONCURRENT_PROGRAMS_TL cp, applsys.FND_CONCURRENT_PROGRAMS d, applsys.FND_APPLICATION_TL a, applsys.fnd_application b
WHERE cp.application_id = a.application_id
AND d.CONCURRENT_PROGRAM_ID = cp.CONCURRENT_PROGRAM_ID
AND a.APPLICATION_ID = b.APPLICATION_ID
AND b.application_short_name LIKE UPPER('PA')
UNION ALL
SELECT SUBSTR(a.application_name,1,60) c1
, b.application_short_name c2 , 'Form Executable' c3
, SUBSTR(f.form_name,1,16) c4 ,
SUBSTR(d.user_form_name,1,55) c5
FROM applsys.fnd_form f , applsys.FND_APPLICATION_TL a, applsys.fnd_application b, applsys.FND_FORM_TL d
WHERE f.application_id = a.application_id
AND d.FORM_ID = f.FORM_ID
AND a.APPLICATION_ID = b.APPLICATION_ID
AND b.application_short_name LIKE UPPER('PA') ORDER BY 1,2,3,4;
SQL script to lists all the profile settings
Set Who columns in pre-insert:
BEGIN
DATA BLOCK NAME;
FND_STANDARD.SET_WHO;
PACKAGE.VALIDATE_APPROVAL;(Define this procedure in program units)
END;
Set Who columns in pre-update:
begin
XXGOD_JOB_NO;
XXGOD_REV_ADD;
fnd_standard.set_who;
XXJOB_CARD.VALIDATE_APPROVAL1;
end;
Set triggers at when-new-record-instance-level
DECLARE
v_count number;
v_count2 number;
BEGIN
SELECT count(*) into v_count
FROM fnd_lookup_values
WHERE lookup_type = ‘Receipt Number’(count records ad new in db)
and sysdate between start_date_active and nvl(end_date_active,sysdate)
and lookup_code=fnd_global.user_id;
IF v_count = 0 then
--set datablock and procedure to validate SET_ITEM_PROPERTY('XXGOD_JOB_CARD.APPROVE',ENABLED,PROPERTY_FALSE);
END IF;
IF v_count = 1 THEN
--SET_ITEM_PROPERTY('XXGOD_QUOTATION_HDR1.SALES_ORDER',ENABLED,PROPERTY_FALSE);
IF :XXGOD_JOB_CARD.APPROVALS ='Submitted' THEN
SET_ITEM_PROPERTY('XXGOD_JOB_CARD.APPROVE',ENABLED,PROPERTY_TRUE);
ELSE
SET_ITEM_PROPERTY('XXGOD_JOB_CARD.APPROVE',ENABLED,PROPERTY_FALSE);
END IF;
END IF;
END;
Validating Procedure and Package:
PACKAGE XX_VALIDATE IS
PROCEDURE VALIDATE_APPROVAL;
PROCEDURE VALIDATE_APPROVAL1;
PROCEDURE XVALID_DESC_PRO(EVENT IN VARCHAR2);
END;
PACKAGE BODY XX_VALIDATE IS
PROCEDURE VALIDATE_APPROVAL IS
BEGIN
IF :DATA_BLOCK_NAME.APPROVALS IS NULL THEN
FND_MESSAGE.SET_STRING('Status needs to be selected');
FND_MESSAGE.SHOW;
RAISE FORM_TRIGGER_FAILURE;
END IF;
IF :DATA_BLOCK_NAME.APPROVALS!='New'THEN
FND_MESSAGE.SET_STRING('This is a new Job card,select the status as New');
FND_MESSAGE.SHOW;
RAISE FORM_TRIGGER_FAILURE;
ELSE
XX_VALIDATE_1(CALLING ANOTHER PROCEDURE);
END IF;
EXCEPTION
WHEN OTHERS THEN
FND_MESSAGE.ERROR;
RAISE FORM_TRIGGER_FAILURE;
END;
------------------------------------------------------------------------------------------------
PROCEDURE VALIDATE_APPROVAL1 IS
CURSOR C1 IS SELECT APPROVALS FROM table_name WHERE JOB_ID=:data_bolck.item;
v1 VARCHAR2(20);
BEGIN
OPEN C1;
FETCH C1 INTO v1;
IF V1!=:DATA_BLOCK_NAME.APPROVALS THEN
IF V1='New' AND :DATA_BLOCK_NAME.APPROVALS!='Submitted' THEN
FND_MESSAGE.SET_STRING('This is a new Job card,needs to be Submitted');
FND_MESSAGE.SHOW;
RAISE FORM_TRIGGER_FAILURE;
END IF;
IF V1='Submitted' and :DATA_BLOCK_NAME.APPROVALS not in('Approved','Rejected') THEN
FND_MESSAGE.SET_STRING('Submitted Job card,needs to be Approved or Rejected');
FND_MESSAGE.SHOW;
raise form_trigger_failure;
END IF;
IF V1='Rejected' and :DATA_BLOCK_NAME.APPROVALS not in( 'Rejected','Submitted','Closed') THEN
FND_MESSAGE.SET_STRING('Rejected Quotation can only be Submitted or Closed');
FND_MESSAGE.SHOW;
raise form_trigger_failure;
END IF;
if v1='Approved' and :DATA_BLOCK_NAME.approvals not in('Inactive','Active') then
fnd_message.set_string('Approved Job can be Active or Inactive');
fnd_message.show;
raise form_trigger_failure;
end if;
IF v1='Inactive' and :DATA_BLOCK_NAME.approvals!='Active' then
FND_MESSAGE.SET_STRING('Select the status as Active');
FND_MESSAGE.SHOW;
raise form_trigger_failure;
END IF;
IF v1='Active' and : DATA_BLOCK_NAME .approvals!='Closed' then
FND_MESSAGE.SET_STRING('Active Job card has to be Closed');
FND_MESSAGE.SHOW;
raise form_trigger_failure;
END IF;
if v1='Closed' and : DATA_BLOCK_NAME.approvals!='Closed' then
FND_MESSAGE.SET_STRING('Closed Job card can not be modified');
FND_MESSAGE.SHOW;
raise form_trigger_failure;
end if;
END IF;
CLOSE C1;
EXCEPTION
WHEN OTHERS THEN
FND_MESSAGE.ERROR;
RAISE FORM_TRIGGER_FAILURE;
END;
PROCEDURE XGOD_DESC_PRO(EVENT IN VARCHAR2)
IS
BEGIN
IF EVENT='PRE-FORM' THEN
FND_DESCR_FLEX.DEFINE(
BLOCK => 'XXGOD_JOB_CARD',
FIELD => 'DFF1',
APPL_SHORT_NAME => 'PO',
DESC_FLEX_NAME => 'JobCardDFV');
END IF;
IF EVENT='WHEN-NEW-FORM-INSTANCE' THEN
FND_DESCR_FLEX.DEFINE(
BLOCK => 'XXGOD_JOB_CARD',
FIELD => 'DFF1',
APPL_SHORT_NAME => 'PO',
DESC_FLEX_NAME => 'JobCardDFV');
FND_DESCR_FLEX.DEFINE(
BLOCK => 'XXGOD_JOB_CARD',
FIELD => 'DFF',
APPL_SHORT_NAME => 'PO',
DESC_FLEX_NAME => 'JobCardDFF1');
END IF;
EXCEPTION
WHEN OTHERS THEN
RAISE FORM_TRIGGER_FAILURE;
END;
END;
Summary
SQL script to list all the registered concurrent programs by module.
SELECT SUBSTR(a.application_name,1,60) Application_NAME
, b.application_short_name SHORT_NAME
, DECODE(SUBSTR(cp.user_concurrent_program_name,4,1),':'
, 'Concurrent Manager Executable'
, 'Subprogram or Function') TYPE
, SUBSTR(d.concurrent_program_name,1,16) PROGRAM
, SUBSTR(cp.user_concurrent_program_name,1,55) USER_PROGRAM_NAME
FROM applsys.FND_CONCURRENT_PROGRAMS_TL cp, applsys.FND_CONCURRENT_PROGRAMS d, applsys.FND_APPLICATION_TL a, applsys.fnd_application b
WHERE cp.application_id = a.application_id
AND d.CONCURRENT_PROGRAM_ID = cp.CONCURRENT_PROGRAM_ID
AND a.APPLICATION_ID = b.APPLICATION_ID
AND b.application_short_name LIKE UPPER('PA')
UNION ALL
SELECT SUBSTR(a.application_name,1,60) c1
, b.application_short_name c2 , 'Form Executable' c3
, SUBSTR(f.form_name,1,16) c4 ,
SUBSTR(d.user_form_name,1,55) c5
FROM applsys.fnd_form f , applsys.FND_APPLICATION_TL a, applsys.fnd_application b, applsys.FND_FORM_TL d
WHERE f.application_id = a.application_id
AND d.FORM_ID = f.FORM_ID
AND a.APPLICATION_ID = b.APPLICATION_ID
AND b.application_short_name LIKE UPPER('PA') ORDER BY 1,2,3,4;
A list of E-Business Suite modules along with some usefull columns.
SELECT * FROM
(
SELECT fat.application_name MODULE,
fa.application_short_name short_name,
fa.basepath,
fat.description,
fl.meaning,
fpi.patch_level,
fpi.product_version
FROM apps.fnd_application fa,
apps.fnd_application_tl fat,
apps.fnd_product_installations fpi,
apps.fnd_lookups fl
WHERE fa.APPLICATION_ID = fat.APPLICATION_ID
AND fa.application_id = fpi.application_id
AND fpi.status = fl.lookup_code
AND fl.lookup_type = 'FND_PRODUCT_STATUS'
AND fat.LANGUAGE = USERENV('LANG')
UNION
SELECT fa.application_short_name,
fa.basepath,
fat.application_name,
fat.description,
'Custom', ' ',
fu.user_name||' ( '||fa.creation_date ||' )'
FROM apps.fnd_application fa,
apps.fnd_application_tl fat,
apps.fnd_user fu
WHERE fa.APPLICATION_ID = fat.APPLICATION_ID
AND fat.LANGUAGE = USERENV('LANG')
AND fa.created_by = fu.user_id
AND fa.application_id NOT IN ( SELECT fpi.application_id
FROM apps.fnd_product_installations fpi )
)
--where short_name like '%AR%'
ORDER BY MODULE ASC;
Output example when filter with short_name like '%AR%'
Module:Oracle Receivables
Short_name:AR
Short_name:AR
basepath:AR_TOP
Description:Oracle Receivables
Meaning:Installed
Patch_level:11i.AR.L
SELECT * FROM
(
SELECT fat.application_name MODULE,
fa.application_short_name short_name,
fa.basepath,
fat.description,
fl.meaning,
fpi.patch_level,
fpi.product_version
FROM apps.fnd_application fa,
apps.fnd_application_tl fat,
apps.fnd_product_installations fpi,
apps.fnd_lookups fl
WHERE fa.APPLICATION_ID = fat.APPLICATION_ID
AND fa.application_id = fpi.application_id
AND fpi.status = fl.lookup_code
AND fl.lookup_type = 'FND_PRODUCT_STATUS'
AND fat.LANGUAGE = USERENV('LANG')
UNION
SELECT fa.application_short_name,
fa.basepath,
fat.application_name,
fat.description,
'Custom', ' ',
fu.user_name||' ( '||fa.creation_date ||' )'
FROM apps.fnd_application fa,
apps.fnd_application_tl fat,
apps.fnd_user fu
WHERE fa.APPLICATION_ID = fat.APPLICATION_ID
AND fat.LANGUAGE = USERENV('LANG')
AND fa.created_by = fu.user_id
AND fa.application_id NOT IN ( SELECT fpi.application_id
FROM apps.fnd_product_installations fpi )
)
--where short_name like '%AR%'
ORDER BY MODULE ASC;
Output example when filter with short_name like '%AR%'
Module:Oracle Receivables
Short_name:AR
Short_name:AR
basepath:AR_TOP
Description:Oracle Receivables
Meaning:Installed
Patch_level:11i.AR.L
SQL script to lists all the profile settings
SELECT pot.user_profile_option_name "Profile"
, DECODE( a.profile_option_value
, '1', '1 (may be "Yes")'
, '2', '2 (may be "No")'
, a.profile_option_value) "Value"
, DECODE( a.level_id
, 10001, 'Site'
, 10002, 'Appl'
, 10003, 'Resp'
, 10004, 'User'
, '????') "Levl"
, DECODE( a.level_id
, 10002, e.application_name
, 10003, c.responsibility_name
, 10004, d.user_name
, '-') "Location"
FROM applsys.fnd_application_tl e
, applsys.fnd_user d , applsys.fnd_responsibility_tl c
, applsys.fnd_profile_option_values a , applsys.fnd_profile_options b
, applsys.fnd_profile_options_tl pot
WHERE UPPER( pot.user_profile_option_name) LIKE UPPER( '%&&v_profile%')
AND pot.profile_option_name = b.profile_option_name
AND b.application_id = a.application_id (+)
AND b.profile_option_id = a.profile_option_id (+)
AND a.level_value = c.responsibility_id (+)
AND a.level_value = d.user_id (+) AND a.level_value = e.application_id
(+)
AND( UPPER( e.application_name) LIKE UPPER( '%&&v_username%')
OR UPPER( c.responsibility_name) LIKE UPPER( '%&&v_username%')
OR UPPER( d.user_name) LIKE UPPER( '%&&v_username%'))
ORDER BY "Profile", "Levl", "Location", "Value"
/
Concurrent Programs assigned to a request type
SELECT RC.REQUEST_CLASS_NAME,
CPTL.USER_CONCURRENT_PROGRAM_NAME,
CP.CONCURRENT_PROGRAM_NAME
FROM APPLSYS.FND_CONCURRENT_PROGRAMS_TL CPTL,
APPLSYS.FND_CONCURRENT_PROGRAMS CP,
APPLSYS.FND_CONCURRENT_REQUEST_CLASS RC
WHERE RC.APPLICATION_ID = CP.CLASS_APPLICATION_ID
AND RC.REQUEST_CLASS_ID = CP.CONCURRENT_CLASS_ID
AND CP.APPLICATION_ID = CPTL.APPLICATION_ID
AND CP.CONCURRENT_PROGRAM_ID = CPTL.CONCURRENT_PROGRAM_ID
ORDER BY REQUEST_CLASS_NAME, USER_CONCURRENT_PROGRAM_NAME;
SELECT RC.REQUEST_CLASS_NAME,
CPTL.USER_CONCURRENT_PROGRAM_NAME,
CP.CONCURRENT_PROGRAM_NAME
FROM APPLSYS.FND_CONCURRENT_PROGRAMS_TL CPTL,
APPLSYS.FND_CONCURRENT_PROGRAMS CP,
APPLSYS.FND_CONCURRENT_REQUEST_CLASS RC
WHERE RC.APPLICATION_ID = CP.CLASS_APPLICATION_ID
AND RC.REQUEST_CLASS_ID = CP.CONCURRENT_CLASS_ID
AND CP.APPLICATION_ID = CPTL.APPLICATION_ID
AND CP.CONCURRENT_PROGRAM_ID = CPTL.CONCURRENT_PROGRAM_ID
ORDER BY REQUEST_CLASS_NAME, USER_CONCURRENT_PROGRAM_NAME;
Set Who columns in pre-insert:
BEGIN
DATA BLOCK NAME;
FND_STANDARD.SET_WHO;
PACKAGE.VALIDATE_APPROVAL;(Define this procedure in program units)
END;
Set Who columns in pre-update:
begin
XXGOD_JOB_NO;
XXGOD_REV_ADD;
fnd_standard.set_who;
XXJOB_CARD.VALIDATE_APPROVAL1;
end;
Set triggers at when-new-record-instance-level
DECLARE
v_count number;
v_count2 number;
BEGIN
SELECT count(*) into v_count
FROM fnd_lookup_values
WHERE lookup_type = ‘Receipt Number’(count records ad new in db)
and sysdate between start_date_active and nvl(end_date_active,sysdate)
and lookup_code=fnd_global.user_id;
IF v_count = 0 then
--set datablock and procedure to validate SET_ITEM_PROPERTY('XXGOD_JOB_CARD.APPROVE',ENABLED,PROPERTY_FALSE);
END IF;
IF v_count = 1 THEN
--SET_ITEM_PROPERTY('XXGOD_QUOTATION_HDR1.SALES_ORDER',ENABLED,PROPERTY_FALSE);
IF :XXGOD_JOB_CARD.APPROVALS ='Submitted' THEN
SET_ITEM_PROPERTY('XXGOD_JOB_CARD.APPROVE',ENABLED,PROPERTY_TRUE);
ELSE
SET_ITEM_PROPERTY('XXGOD_JOB_CARD.APPROVE',ENABLED,PROPERTY_FALSE);
END IF;
END IF;
END;
Validating Procedure and Package:
PACKAGE XX_VALIDATE IS
PROCEDURE VALIDATE_APPROVAL;
PROCEDURE VALIDATE_APPROVAL1;
PROCEDURE XVALID_DESC_PRO(EVENT IN VARCHAR2);
END;
PACKAGE BODY XX_VALIDATE IS
PROCEDURE VALIDATE_APPROVAL IS
BEGIN
IF :DATA_BLOCK_NAME.APPROVALS IS NULL THEN
FND_MESSAGE.SET_STRING('Status needs to be selected');
FND_MESSAGE.SHOW;
RAISE FORM_TRIGGER_FAILURE;
END IF;
IF :DATA_BLOCK_NAME.APPROVALS!='New'THEN
FND_MESSAGE.SET_STRING('This is a new Job card,select the status as New');
FND_MESSAGE.SHOW;
RAISE FORM_TRIGGER_FAILURE;
ELSE
XX_VALIDATE_1(CALLING ANOTHER PROCEDURE);
END IF;
EXCEPTION
WHEN OTHERS THEN
FND_MESSAGE.ERROR;
RAISE FORM_TRIGGER_FAILURE;
END;
------------------------------------------------------------------------------------------------
PROCEDURE VALIDATE_APPROVAL1 IS
CURSOR C1 IS SELECT APPROVALS FROM table_name WHERE JOB_ID=:data_bolck.item;
v1 VARCHAR2(20);
BEGIN
OPEN C1;
FETCH C1 INTO v1;
IF V1!=:DATA_BLOCK_NAME.APPROVALS THEN
IF V1='New' AND :DATA_BLOCK_NAME.APPROVALS!='Submitted' THEN
FND_MESSAGE.SET_STRING('This is a new Job card,needs to be Submitted');
FND_MESSAGE.SHOW;
RAISE FORM_TRIGGER_FAILURE;
END IF;
IF V1='Submitted' and :DATA_BLOCK_NAME.APPROVALS not in('Approved','Rejected') THEN
FND_MESSAGE.SET_STRING('Submitted Job card,needs to be Approved or Rejected');
FND_MESSAGE.SHOW;
raise form_trigger_failure;
END IF;
IF V1='Rejected' and :DATA_BLOCK_NAME.APPROVALS not in( 'Rejected','Submitted','Closed') THEN
FND_MESSAGE.SET_STRING('Rejected Quotation can only be Submitted or Closed');
FND_MESSAGE.SHOW;
raise form_trigger_failure;
END IF;
if v1='Approved' and :DATA_BLOCK_NAME.approvals not in('Inactive','Active') then
fnd_message.set_string('Approved Job can be Active or Inactive');
fnd_message.show;
raise form_trigger_failure;
end if;
IF v1='Inactive' and :DATA_BLOCK_NAME.approvals!='Active' then
FND_MESSAGE.SET_STRING('Select the status as Active');
FND_MESSAGE.SHOW;
raise form_trigger_failure;
END IF;
IF v1='Active' and : DATA_BLOCK_NAME .approvals!='Closed' then
FND_MESSAGE.SET_STRING('Active Job card has to be Closed');
FND_MESSAGE.SHOW;
raise form_trigger_failure;
END IF;
if v1='Closed' and : DATA_BLOCK_NAME.approvals!='Closed' then
FND_MESSAGE.SET_STRING('Closed Job card can not be modified');
FND_MESSAGE.SHOW;
raise form_trigger_failure;
end if;
END IF;
CLOSE C1;
EXCEPTION
WHEN OTHERS THEN
FND_MESSAGE.ERROR;
RAISE FORM_TRIGGER_FAILURE;
END;
PROCEDURE XGOD_DESC_PRO(EVENT IN VARCHAR2)
IS
BEGIN
IF EVENT='PRE-FORM' THEN
FND_DESCR_FLEX.DEFINE(
BLOCK => 'XXGOD_JOB_CARD',
FIELD => 'DFF1',
APPL_SHORT_NAME => 'PO',
DESC_FLEX_NAME => 'JobCardDFV');
END IF;
IF EVENT='WHEN-NEW-FORM-INSTANCE' THEN
FND_DESCR_FLEX.DEFINE(
BLOCK => 'XXGOD_JOB_CARD',
FIELD => 'DFF1',
APPL_SHORT_NAME => 'PO',
DESC_FLEX_NAME => 'JobCardDFV');
FND_DESCR_FLEX.DEFINE(
BLOCK => 'XXGOD_JOB_CARD',
FIELD => 'DFF',
APPL_SHORT_NAME => 'PO',
DESC_FLEX_NAME => 'JobCardDFF1');
END IF;
EXCEPTION
WHEN OTHERS THEN
RAISE FORM_TRIGGER_FAILURE;
END;
END;
Wow. I am thankful to you for sharing the full source code to list all the registered concurrent programs. I have bookmarked this post and your blog too, I find so many other helpful articles too on it and is so excited that you have shared this difficult script. Thanks again.
ReplyDeletesap upgrade tools