Knowledge on Oracle: 2015

Wednesday, 5 August 2015

Oracle Apps - FND Commands

Oracle FNDLOAD download/Upload Script

Lookups
FNDLOAD apps/<apps_password> O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct <XX_FILENAME>.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME="<XX_APP_NAME>" LOOKUP_TYPE="<XX_LOOKUP_TYPE>"
FNDLOAD apps/<apps_password> O Y UPLOAD $FND_TOP/patch/115/import/aflvmlu.lct <XX_FILENAME>.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

Concurrent Program
FNDLOAD apps/<apps_password> O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct <XX_FILENAME>.ldt PROGRAM APPLICATION_SHORT_NAME="<XX_APP_NAME>" CONCURRENT_PROGRAM_NAME="<XX_CONC_SHORT_NAME>"
FNDLOAD apps/<apps_password> 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct <XX_FILENAME>.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

Profile
FNDLOAD apps/<apps_password> O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct <XX_FILENAME>.ldt PROFILE PROFILE_NAME="<XX_PROFILE_NAME>" APPLICATION_SHORT_NAME="<XX_APP_NAME>"
FND_TOP/bin/FNDLOAD apps/<apps_password> 0 Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct <XX_FILENAME>.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

Request Set
FNDLOAD apps/<apps_password> 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct <XX_FILENAME>.ldt REQ_SET REQUEST_SET_NAME="<XX_REQUEST_SET_NAME>"
FNDLOAD apps/<apps_password> O Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct <XX_FILENAME>.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

FND Message
FNDLOAD apps/<apps_password> 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct <XX_FILENAME>.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME="<XX_APP_NAME>" MESSAGE_NAME="<XX_MESSAGE_NAME>"
FNDLOAD apps/<apps_password> O Y UPLOAD $FND_TOP/patch/115/import/afmdmsg.lct <XX_FILENAME>.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

Form Function
FNDLOAD apps/<apps_password> 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct <XX_FILENAME>.ldt FUNCTION FUNCTION_NAME="<XX_FORM_FUNCTION_NAME>"
FNDLOAD apps/<apps_password> 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct <XX_FILENAME>.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

Value Set
FNDLOAD apps/<apps_password> 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct <XX_FILENAME>.ldt VALUE_SET FLEX_VALUE_SET_NAME="<XX_VALUE_SET_NAME>"
FNDLOAD apps/<apps_password> 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct <XX_FILENAME>.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

Data Definition and RTF Template
FNDLOAD apps/$CLIENT_APPS_PWD O Y DOWNLOAD $XDO_TOP/patch/115/import/xdotmpl.lct <XX_FILENAME>.ldt XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME='"<XX_APP_NAME>" DATA_SOURCE_CODE="<DD_SHORT_NAME>" TMPL_APP_SHORT_NAME="<TEMPLATE_APPLICATION_SHORT_NAME>" TEMPLATE_CODE="<TEMPLATE_SHORT_NAME>"
FNDLOAD apps/$CLIENT_APPS_PWD O Y UPLOAD $XDO_TOP/patch/115/import/xdotmpl.lct <XX_FILENAME>.ldt

Sunday, 12 July 2015

Oracle GRC

CCG 5.5.1 Metabuilder Installation

Metabuilder step by step installation details:

(Windows Installations)

1. Unzip the CCG_551_metabuilder.zip file in 'C' drive

2. Add the CCG TNS_NAME in tnsnames.ora file located under C:\DevSuiteHome_1\NETWORK\ADMIN

Note: DevSuiteHome_1 is forms repository

E.g.: CCG=

(DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=host.domain.com) (PORT=1580)) (CONNECT_DATA= (SERVICE_NAME=CCG) ) )

Note: SERVICE_NAME and <alias> name should be same.

3. Open the default.env (C:\DevSuiteHome_1\forms\server) and add the below text.

3.1. Add the value for ORACLE_HOME = C:\DevSuiteHome_1

3.2. Add FORMS_PATH = C:\DevSuiteHome_1\forms; C:\CCG_551_metabuilder\mb_ui

Note: DevSuiteHome_1 is forms repository

4. Open formsweb.cfg (C:\DevSuiteHome_1\forms\server) and add the below text at the end of page.

-----------------------------
[metabuilder]
form=metabuilder.fmx
otherparams=param_dbinstance=<Alias> param_gwyuid=amhome/<am_home_pwd>
separateFrame=True
lookandfeel=Oracle
logo=amwind
pageTitle=Oracle Metabuilder 5.5.1
webUtilArchive=/forms/java/frmwebutil.jar,/forms/java/jacob.jar
archive_jini=frmall_jinit.jar,ico.jar
imagebase=codebase
---------------------------------

Note: Replace following
<Alias> = CCG TNS_NAME Alias Name (CCG – Refer in step 2)
<am_home_pwd> = AM_HOME password

4.1. Edit the formsweb.cfg file as below (refer the line number)

5. Copy the ico.jar (CCG_551_metabuilder /mb_ui/java) and paste it in
(C:\DevSuiteHome_1\forms\java) directory.
Note: DevSuiteHome_1 is forms repository

6. Go to windows (start menu) type OC4J, click on Start OC4J Instance.
(C:\DevSuiteHome_1\j2ee\DevSuite\startinst.bat)

7. Use the following URL to connect to METABUILDER
http://<full_computer_name>:8889/forms/frmservlet?config=metabuilder

Note 1: Go to windows start menu --> Right click on computer --> Properties --> Check the <full_computer_name>.

Note 2: Username should be created in CCG with CCG Developer Role.

Thursday, 9 July 2015

Oracle APIs

1. Oracle API to create Bank BRANCH:

lv_ext_bank_branch_rec.bank_party_id := <l_bank_id>;

lv_ext_bank_branch_rec.branch_name := 'Twitter SBI Branch';

lv_ext_bank_branch_rec.branch_number := '076401251';

lv_ext_bank_branch_rec.branch_type := 'OTHER';

lv_ext_bank_branch_rec.bch_object_version_number := 1;

dbms_output.put_line('Calling API create_ext_bank_branch');

apps.IBY_EXT_BANKACCT_PUB.create_ext_bank_branch (

p_api_version => 1.0,

p_init_msg_list => apps.FND_API.G_TRUE,

p_ext_bank_branch_rec => lv_ext_bank_branch_rec,

x_branch_id => lvx_branch_id,

x_return_status => lvx_return_status,

x_msg_count => lvx_msg_count,

x_msg_data => lvx_msg_data,

x_response => lv_response_rec

);

2. Oracle API to create Supplier Bank A/C:

p_ext_bank_acct_rec.object_version_number := 1.0;

P_EXT_BANK_ACCT_REC.ACCT_OWNER_PARTY_ID := V_SUPPLIER_PARTY_ID;

P_EXT_BANK_ACCT_REC.BANK_ACCOUNT_NUM := lc_bank_acc_num;

p_ext_bank_acct_rec.bank_id := v_bank_id;

p_ext_bank_acct_rec.branch_id := v_bank_branch_id;

p_ext_bank_acct_rec.start_date := SYSDATE;

p_ext_bank_acct_rec.country_code := 'US';

p_ext_bank_acct_rec.currency := 'USD';

p_ext_bank_acct_rec.foreign_payment_use_flag := 'Y';

p_ext_bank_acct_rec.payment_factor_flag := 'N';

IBY_EXT_BANKACCT_PUB.CREATE_EXT_BANK_ACCT

(p_api_version => p_api_version,

p_init_msg_list => p_init_msg_list,

p_ext_bank_acct_rec => p_ext_bank_acct_rec,

x_acct_id => x_acct_id,

x_return_status => x_return_status,

p_association_level => 'S',

p_supplier_site_id => NULL,

p_party_site_id => NULL,

p_org_id => ln_orgid, -- Mandatory for both supplier and site

p_org_type => 'OPERATING_UNIT', --Mandatory

x_msg_count => x_msg_count,

x_msg_data => x_msg_data,

x_response => x_response);

3. Oracle API to update Branch details:

p_init_msg_list := fnd_api.g_true;

p_branch_id := 1174216;

p_branch_number := '021000021';

p_branch_name := 'JP MORGAN CHASE';

p_bch_object_version_number := 8;

CE_BANK_PUB.UPDATE_BANK_BRANCH

(p_init_msg_list => p_init_msg_list,

p_branch_id => p_branch_id,

p_branch_name => p_branch_name,

p_branch_number => p_branch_number,

p_bch_object_version_number => p_bch_object_version_number,

p_typ_object_version_number => p_typ_object_version_number,

p_rfc_object_version_number => p_rfc_object_version_number,

p_eft_object_version_number => p_eft_object_version_number,

x_return_status => x_return_status,

x_msg_count => x_msg_count,

x_msg_data => x_msg_data

);

4. Oracle API to create Bank:

lc_return_status := '';

ln_msg_count := '';

lc_msg_data := '';

lr_extbank_rec.bank_name := lc_bank_name;

lr_extbank_rec.bank_number := lc_bank_num;

lr_extbank_rec.country_code := 'US';

apps.fnd_msg_pub.delete_msg(NULL);

apps.fnd_msg_pub.initialize();

IBY_EXT_BANKACCT_PUB.create_ext_bank

( -- ------------------------------

-- Input data elements

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

p_api_version => 1.0,

p_init_msg_list => FND_API.G_TRUE,

p_ext_bank_rec => lr_extbank_rec,

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

-- Output data elements

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

x_bank_id => ln_bank_id,

x_return_status => lc_return_status,

x_msg_count => ln_msg_count,

x_msg_data => lc_msg_data,

x_response => lr_response_rec

);

5. Oracle API to application USER_NAME:

SELECT FND_USER_AP_PKG.GET_USER_NAME(l_user_id) FROM DUAL;

6. Oracle API to get INVOICE approval status:

l_invoice_validation_status := ap_invoices_pkg.get_approval_status(

l_invoice_id => p_line_rec.invoice_id,

l_invoice_amount => l_invoice_amount,

l_payment_status_flag => l_payment_status_flag,

l_invoice_type_lookup_code => l_invoice_type_lookup_code );

7. Oracle API to get INVOICE Period Name:

SELECT AP_INVOICES_PKG.GET_PERIOD_NAME( GL_DATE, NULL, ORG_ID) PERIOD_NAME FROM DUAL;

8. Oracle API to create ADHOC role:

WF_DIRECTORY.CreateAdHocRole (

role_name => v_role_name,

role_display_name =>v_role_display_name,

email_address => 'email@gmail.com',

notification_preference => 'MAILHTML'

);

9. Oracle API to create FND Attachment:

set serveroutput on

declare

l_rowid VARCHAR2(100);

l_document_id NUMBER;

l_filename VARCHAR2(500);

l_attached_document_id NUMBER;

l_media_id NUMBER;

BEGIN

l_filename := 'Attachment Through API.';

SELECT fnd_documents_s.NEXTVAL

INTO l_document_id

FROM DUAL;

SELECT fnd_attached_documents_s.NEXTVAL

INTO l_attached_document_id

FROM DUAL;

SELECT MAX (media_id) + 1 INTO l_media_id

FROM FND_DOCUMENTS_SHORT_TEXT;

INSERT INTO FND_DOCUMENTS_SHORT_TEXT (MEDIA_ID, SHORT_TEXT, APP_SOURCE_VERSION)

VALUES (l_media_id, 'Short Text For Attachment through API', NULL) ;

COMMIT;

fnd_documents_pkg.insert_row

(x_rowid => l_rowid,

x_document_id => l_document_id,

x_creation_date => SYSDATE,

x_created_by => 0,

x_last_update_date => SYSDATE,

x_last_updated_by => 0,

x_last_update_login => fnd_profile.VALUE('LOGIN_ID'),

x_datatype_id => 1,

--X_security_id => <security ID defined in your Attchments, Usaully SOB ID/ORG_ID..>,

x_publish_flag => 'Y',

x_category_id => 1,

x_security_type => 1,

x_usage_type => 'O',

x_language => USERENV('LANG'),

x_description => l_filename,

x_file_name => NULL,

x_media_id => l_media_id

);

commit;

fnd_documents_pkg.insert_tl_row

(x_document_id => l_document_id,

x_creation_date => SYSDATE,

x_created_by => 0,

x_last_update_date => SYSDATE,

x_last_updated_by => 0,

x_last_update_login => fnd_profile.VALUE('LOGIN_ID'),

x_language => USERENV('LANG'),

x_description => l_filename

);

COMMIT;

fnd_attached_documents_pkg.insert_row

(x_rowid => l_rowid,

x_attached_document_id => l_attached_document_id,

x_document_id => l_document_id,

x_creation_date => SYSDATE,

x_created_by => 0,

x_last_update_date => SYSDATE,

x_last_updated_by => 0,

x_last_update_login => fnd_profile.VALUE('LOGIN_ID'),

x_seq_num => 10,

x_entity_name => 'AP_INVOICES',

x_column1 => NULL,

x_pk1_value => '1',

x_pk2_value => NULL,

x_pk3_value => NULL,

x_pk4_value => NULL,

x_pk5_value => NULL,

x_automatically_added_flag => 'N',

x_datatype_id => 1,

x_category_id => 1,

x_security_type => 4,

--X_security_id => <security ID defined in your Attchments, Usaully SOB ID/ORG_ID..>,

x_publish_flag => 'Y',

x_language => USERENV('LANG'),

x_description => l_filename,

x_file_name => NULL,

x_media_id => l_media_id

);

COMMIT;

DBMS_OUTPUT.put_line ('MEDIA ID CREATED IS ' || l_media_id||' Document ID: '||l_document_id);

EXCEPTION WHEN OTHERS THEN

DBMS_OUTPUT.put_line ('Error : '||SQLERRM);

END;

10. Oracle API to wait for the concurrent request to complete:

fnd_concurrent.wait_for_request (request_id => ln_request_id

,interval => 2

,max_wait => 60

-- out arguments

,phase => lc_phase

,status => lc_status

,dev_phase => lc_dev_phase

,dev_status => lc_dev_status

,message => lc_message

);

11. Oracle API to submit concurrent request with XML/RTF Layout:

xml_layout := FND_REQUEST.ADD_LAYOUT( 'XXONT', --app_short_name

'GEPGS_DRAFT_INVOICE_REPORT_1', -- conc_prog_short_name,

'en',

'US',

'PDF');

v_request_id:= FND_REQUEST.SUBMIT_REQUEST (

'XXONT' -- application_short_name --

,'GEPGS_DRAFT_INVOICE_REPORT_1' -- Conc. Short name

,'GEPGS Draft Invoice Report' --Conc. Program Name

,SYSDATE

,NULL

,ln_set_of_books_id,lc_oe_id_flex_code,'D','Y'

,'','','','','','','','','','','','','','',''

,'SALES','ALL','','','Y','','','N','N','N','N'

,lc_order_num,'SINGLE','Y'

,ln_line_number,'','',''

,'','','',''

,'','','','','','','','','',''

,'','','','','','','','',''

);

12. Oracle API to submit WORKFLOW BACKGROUND PROCESS concurrent request:

v_request_id:= FND_REQUEST.SUBMIT_REQUEST (

'FND' -- application_short_name --

,'FNDWFBG' -- Conc. Short name

,'Workflow Background Process' --Conc. Program Name

,SYSDATE

,NULL

,NULL,NULL,NULL,'Y','Y',

'','','','','','','','','','',

'','','','',''

);

Oracle Queries

1. Identify the special character:

SELECT *
FROM table_name
WHERE REGEXP_LIKE(column_name, '[char]');

Eg:-

select * from per_all_people_f where regexp_like (full_name,'[-]');

2. Concurrent Request Run time:

select ((actual_completion_date-actual_start_date)*24*60) MINUTES,

actual_start_date ,

actual_completion_date ,

decode(PHASE_CODE,'C','Completed','I','Inactive','P ','Pending','R','Running','NA') phase_code,

decode(STATUS_CODE, 'A','Waiting', 'B','Resuming', 'C','Normal', 'D','Cancelled', 'E','Error', 'F','Scheduled', 'G','Warning', 'H','On Hold', 'I','Normal', 'M',

'No Manager', 'Q','Standby', 'R','Normal', 'S','Suspended', 'T','Terminating', 'U','Disabled', 'W','Paused', 'X','Terminated', 'Z','Waiting') Status

from apps.fnd_concurrent_requests

where request_id = <request_id>;

3. Oracle Database Version:

select version from v$instance;

4. Oracle Application Version:

SELECT release_name, applications_system_name, aru_release_name

FROM fnd_product_groups;

5. Oracle Application Patch Details:

--table which stores the application patch details

SELECT * FROM ad_applied_patches ;

6. Language Setup in SQL Developer:

ALTER SESSION SET NLS_LANUAGE = ‘AMERICAN’;

7. Query to get Database port number:

select listener_name, port db_port from FND_TNS_LISTENERS FTL, FND_TNS_LISTENER_PORTS FTLP

where ftl.listener_guid = ftlp.listener_guid

and listener_name like '%_DB';

8. Query to get Database Host/SID:

select HOST||'.'||domain HOST_NAME, VIRTUAL_IP||'.'||domain HOST_NAME1, INSTANCE_NAME SERVICE_ID from v$instance ins, FND_NODES fn

where upper(fn.host) = upper(ins.host_name)

and support_db = 'Y';

9. Query to get Supplier bank details:

----query to fetch AP supplier bank details with assignment level (Supplier/Site level)

SELECT APS.VENDOR_ID,

VENDOR_NAME "Supplier Name",

IEP.supplier_site_id VENDOR_SITE_ID,

ASSA.VENDOR_SITE_CODE "Site Name",

bank.bank_name "bank name",

branch.bank_branch_name "Branch Name",

ieb.bank_account_num "Account Number",

DECODE(IEP.supplier_site_id, NULL, 'Supplier', 'Site') assignment_level

FROM IBY_PMT_INSTR_USES_ALL IPI,

AP_SUPPLIERS APS,

AP_SUPPLIER_SITES_ALL ASSA,

iby_external_payees_all IEP,

iby_ext_bank_accounts ieb,

IBY_EXT_BANKS_V bank,

IBY_EXT_BANK_BRANCHES_V branch

WHERE IPI.instrument_type = 'BANKACCOUNT'

AND IPI.payment_function = 'PAYABLES_DISB'

AND IPI.EXT_PMT_PARTY_ID = IEP.EXT_PAYEE_ID

AND IEP.supplier_site_id = ASSA.VENDOR_SITE_ID(+)

AND APS.VENDOR_ID = NVL(ASSA.VENDOR_ID, APS.VENDOR_ID)

AND bank.bank_party_id = ieb.bank_id

AND branch.branch_party_id = ieb.branch_id

AND IEP.PAYEE_PARTY_ID = APS.PARTY_ID

AND IPI.instrument_id = ieb.ext_bank_account_id;

10. Query to get locked session in ORACLE:

SELECT VS.SID ||', '||VS.SERIAL# KILL_SESSION,

VS.USERNAME, DBO.owner, DBO.object_name, DBO.object_type, vs.module, vs.machine, VS.OSUSER,

VLO.ORACLE_USERNAME, FU.USER_NAME LOCKING_FND_USER_NAME, fl.start_time locking_fnd_user_login_time,

vp.pid, vp.spid AS os_process, vs.status, vs.saddr, vs.audsid, vs.process

FROM V$SESSION VS,

V$PROCESS VP,

V$LOCKED_OBJECT VLO,

DBA_OBJECTS DBO,

FND_LOGINS FL,

fnd_user fu

WHERE VS.PADDR = VP.ADDR

AND VS.SID = VLO.SESSION_ID

AND DBO.OBJECT_ID = VLO.OBJECT_ID

AND vp.spid = fl.process_spid(+)

AND vp.pid = fl.pid(+)

AND FL.USER_ID = FU.USER_ID(+)

AND DBO.OBJECT_NAME LIKE NVL('%'|| UPPER (:OBJECT_NAME)|| '%', DBO.OBJECT_NAME)

AND NVL (VS.STATUS, 'XX') != 'KILLED';

11. Query to find all running concurrent requests

select request_id,
fcpt.user_concurrent_program_name,
completion_text,
actual_start_date,
actual_completion_date,
to_date((actual_completion_date - actual_start_date), 'HH:MM:SS') duration
from fnd_concurrent_requests fcr,
fnd_concurrent_programs fcp,
fnd_concurrent_programs_tl fcpt
where fcr.concurrent_program_id = fcp.concurrent_program_id
and fcp.concurrent_program_id = fcpt.concurrent_program_id
and fcr.actual_start_date > sysdate - 1

order by actual_completion_date - actual_start_date desc;

12. QUERY to get the programs submitted by users

SELECT REQUEST_ID ,
REQUESTOR ,
PROGRAM ,
DECODE (phase_code, 'C', 'Completed', 'I', 'Inactive', 'P', 'Pending', 'R', 'Running') Phase,
DECODE (STATUS_CODE, 'A', 'Waiting', 'B', 'Resuming', 'C', 'Normal', 'D', 'Cancelled', 'E', 'Error', 'F', 'Scheduled', 'G', 'Warning', 'H', 'On Hold', 'I', 'Normal', 'M', 'No Manager', 'Q', 'Standby', 'R', 'Normal', 'S', 'Suspended', 'T', 'Terminating', 'U', 'Disabled', 'W', 'Paused', 'X', 'Terminated', 'Z', 'Waiting') status,
COMPLETION_TEXT ,
TO_CHAR(actual_start_date,'DD-MON-YYYY HH24:MI:SS') ACTUAL_START_DATE ,
TO_CHAR(actual_completion_date,'DD-MON-YYYY HH24:MI:SS') ACTUAL_COMPLETION_DATE ,
APPLICATIONS_SYSTEM_NAME
FROM apps.fnd_conc_req_summary_v req,
apps.FND_PRODUCT_GROUPS db
WHERE 1 =1
AND REQUESTOR = 'SYSADMIN' --- Pass the requestor USER_NAME
AND PROGRAM = 'Workflow Background Process' --- Pass the concurrent program name
AND TRUNC(actual_completion_date) >= TRUNC(SYSDATE-7)
ORDER BY request_id ASC;

13. QUERY to get concurrent programs created or updated by users

SELECT * FROM (
SELECT
papp.application_name,
user_concurrent_program_name,
'Header Details' CONTEXT,
fcpv.creation_date,
(select user_name from fnd_user fu where user_id = fcpv.created_by) created_by,
fcpv.last_update_date,
(select user_name from fnd_user fu where user_id = fcpv.last_updated_by) updated_by
FROM apps.fnd_application_vl papp,
apps.fnd_concurrent_programs_vl fcpv
WHERE papp.application_id = fcpv.application_id
UNION
SELECT papp.application_name,
user_concurrent_program_name,
'Parameter Details: SEQ:'
||COLUMN_SEQ_NUM CONTEXT,
fcpv.creation_date,
(select user_name from fnd_user fu where user_id = fcpv.created_by) created_by,
fcpv.last_update_date,
(select user_name from fnd_user fu where user_id = fcpv.last_updated_by) updated_by
FROM apps.fnd_application_vl papp,
apps.fnd_concurrent_programs_vl fcpv,
apps.FND_DESCR_FLEX_COLUMN_USAGES fdc
WHERE papp.application_id = fcpv.application_id
AND fdc.descriptive_flexfield_name = '$SRS$.'||fcpv.concurrent_program_name
AND fdc.application_id = fcpv.application_id)
WHERE USER_CONCURRENT_PROGRAM_NAME = '1096 - Annual Summary and Transmittal of U.S. Information Returns' --- Pass the concurrent program name
order by 2;