How to find the backend SQL query of the JSP and reports

How To Generate Trace Files in in HTML/JSP (using Profile Option)
Note: This requires proper responsibility to set SQL Initialization statement using Profile option. Step 1. Login to the desired Form application.
Step 2. Select +Profile >> System ('Find System Profile Values' screen will pop up)
Step 3. Check 'User' and Type in the Username (in which the account for that user will be trace) Step 4. Type 'Initialization%' (Initialization SQL Statement - Custom)in the Profile box and Hit 'Find' (Click here for preview.)
Step 5. In the User box, type the following statement and Hit 'Save' (Click here for preview)
BEGIN FND_CTL.FND_SESS_CTL('','','TRUE','TRUE','','ALTER SESSION SET TRACEFILE_IDENTIFIER = TESTING MAX_DUMP_FILE_SIZE = 5000000 EVENTS ='''''' 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12''''');END;
Example :BEGIN FND_CTL.FND_SESS_CTL ('','','TRUE','TRUE','LOG', 'ALTER SESSION SET TRACEFILE_IDENTIFIER =''MURALIAR0001'' EVENTS=''10046 TRACE NAME CONTEXT FOREVER, LEVEL 12'''); END;
Step 6. Login to HTML / JSP page with username/password and start your flow. (Everything you do once login to HTML / JSP will get trace.)
Step 7. Logout of HTML / JSP application once you completed with your flow.
Step 8. Go back to the Profile option in the Form application and delete the Initialization SQL statement, and Hit 'Save'.
Step 9. Log in to the database server or login server and retrieve your trace file. Identify and retrieve the trace file using the tracefile_identifier specified in Step 5.
In this case the tracefile_identifier is “TESTING”. (Click here for Trace file locations) * Note: If you need to regenerate your trace or tracing a new flow, then repeat Step 1 to Step 8. To avoid self-confusion, choose a different name for your trace identifier everytime you set to trace. Step 10. See TKPROF section on how to format trace file into readable text.Trace Options Definition
No Trace
Tracing is not activated
Activities will not get traced.
Regular Trace
(Level 1) Contains SQL, execution statistics, and execution plan.
Provides execution path, row counts as well as produces smallest flat file.
Trace with Binds(Level 4) Regular Trace plus value supplied to SQL statement via local variables.
Trace with Waits(Level 8) Regular Trace plus database operation timings that the SQL waited to have done in order to complete, i.e. disk access.
Trace with Binds and Waits(Level 12) Regular trace with both waits and binds information.Contains the most complete information and will produce the largest trace file.
To get the trace file path on server.
select value from v$parameter where name = 'user_dump_dest' in DB node.

Queries for use

Genral Ledger
---------------
select jh.name Journal_name,jh.period_name Period,jh.currency_code Currency ,jh.je_category Category,jh.je_source Source,jl.je_line_num Linenumber,gcc.segment1'.'gcc.segment2'.'gcc.segment3'.'gcc.segment4'.'gcc.segment5 Account,jl.entered_dr Debit,jl.entered_cr Credit
from gl_je_headers jh,
gl_je_lines jl,
gl_code_combinations gcc
where jl.CODE_COMBINATION_ID=gcc.CODE_COMBINATION_ID
and jh.JE_HEADER_ID=jl.JE_HEADER_ID
and jh.name=:name
and jh.period_name=:period
and jh.currency_code='STAT'
and jh.je_source='82'
INVOICE DETAILS
---------------------
selectap.invoice_num Invoice_num,ap.invoice_date Invoice_date,ap.invoice_currency_code Invoice_currency,ap.invoice_amount Invoice_amount,aid.amount Amount,aid.accounting_date Gl_date,pv.vendor_name Supplier,pvs.vendor_site_code Site_name--,at.name Payment_terms,ap.terms_date Term_date,alc.meaning Invoice_type,alc.lookup_code,alc1.meaning Invoice_distribution_type,alc2.meaning Payment_method,gcc.segment1'.'gcc.segment2'.'gcc.segment3'.'gcc.segment4'.'gcc.segment5 Account
from ap_invoices_all ap,
ap_invoice_distributions_all aid,
po_vendors pv,
po_vendor_sites_all pvs,
fnd_lookup_values alc,
fnd_lookup_values alc1,
fnd_lookup_values alc2--,
ap_terms_tl at,
gl_code_combinations gcc
where alc.lookup_type='INVOICE TYPE'
and alc.lookup_code=ap.INVOICE_TYPE_LOOKUP_CODE
and alc1.lookup_type='INVOICE DISTRIBUTION TYPE'
and alc1.lookup_code=aid.LINE_TYPE_LOOKUP_CODE
and alc2.lookup_type='PAYMENT METHOD'
and alc2.lookup_code=ap.PAYMENT_METHOD_LOOKUP_CODE
and ap.invoice_id=aid.invoice_id
and ap.vendor_id = pv.vendor_id
and gcc.code_combination_id=aid.DIST_CODE_COMBINATION_ID
and pv.vendor_id=pvs.vendor_id
and ap.vendor_site_id=pvs.vendor_site_id
--and ap.terms_id=at.term_id(+)
and ap.invoice_num='214901'
PAYMENT DETAILS IN 11i
-----------------------------
select alc.MEANING Payment_type,acs.name Document,aca.check_number Document_number,aca.amount Payment_amount,aca.CURRENCY_CODE Payment_currency,aca.CHECKRUN_NAME Batchname,aca.EXCHANGE_DATE Rate_date--,aca.EXCHANGE_RATE Exc_rate,aba.bank_account_name Bank_account,aba.CURRENCY_CODE Account_currency,alc1.meaning Payment_method,alc2.meaning Check_status,pv.vendor_name Supplier,pvs.VENDOR_SITE_CODE Suppliersite
from ap_checks_all aca,
ap_check_stocks_all acs,
ap_bank_accounts_all aba,
po_vendors pv,
po_vendor_sites_all pvs,
fnd_lookup_values alc,
fnd_lookup_values alc1,
fnd_lookup_values alc2--,
gl_daily_conversion_types gdct
--,gl_daily_conversion_types gdct1
where alc.lookup_type='PAYMENT TYPE'
and alc.lookup_code=aca.PAYMENT_TYPE_FLAG
and alc1.lookup_type='PAYMENT METHOD'
and alc1.lookup_code=aca.PAYMENT_METHOD_LOOKUP_CODE
and alc2.lookup_type='CHECK STATE'
and alc2.lookup_code=aca.STATUS_LOOKUP_CODE
--and aca.EXCHANGE_RATE_TYPE=gdct.CONVERSION_TYPE
and aca.check_stock_id=acs.check_stock_id
and aca.BANK_ACCOUNT_ID=aba.BANK_ACCOUNT_ID
and aca.VENDOR_ID=pv.vendor_id
and aca.VENDOR_SITE_ID=pvs.VENDOR_SITE_ID
and aca.check_number='1000013'
AR to GL query
-----------------
SELECT gjjlv.period_name "Period" , gjb.name "Batch name" , gjjlv.header_name "Journal Entry For" , gjjlv.je_source "Source" , glcc.concatenated_segments "Accounts" , gjjlv.line_entered_dr "Entered Debit" , gjjlv.line_entered_cr "Entered Credit" , gjjlv.line_accounted_dr "Accounted Debit" , gjjlv.line_accounted_cr "Accounted Credit" , gjjlv.currency_code "Currency" , arm.name "Payment Method" , acra.receipt_number "Receipt Num" , acra.receipt_date "Receipt Date" , RA.CUSTOMER_NAME "Reference" , gjjlv.created_by "Gl Transfer By"
FROM apps.gl_je_journal_lines_v gjjlv ,
gl_je_lines gje ,
gl_je_headers gjh ,
gl_je_batches gjb ,
ar_cash_receipts_all acra ,
apps.ra_customers ra ,
apps.gl_code_combinations_kfv glcc ,
ar_receipt_methods arm
WHERE gjh.period_name IN ('OCT-2007','NOV-2007')
AND glcc.code_combination_id = gje.code_combination_id
AND gjh.JE_BATCH_ID = gjb.JE_BATCH_ID
AND gjh.JE_HEADER_ID = gje.JE_HEADER_ID
AND gjh.period_name = gjb.default_period_name
AND gjh.period_name = gje.period_name
AND gjjlv.period_name = gjh.period_name
AND gjjlv.je_batch_id = gjh.je_batch_id
AND gjjlv.je_header_id = gjh.je_header_id
AND gjjlv.LINE_JE_LINE_NUM = gje.je_line_num
AND gjjlv.line_code_combination_id = glcc.code_combination_id
AND gjjlv.line_reference_4 = acra.receipt_number
AND ra.customer_id = acra.pay_from_customer
AND acra.receipt_method_id = arm.receipt_method_id
AND gjjlv.SUBLEDGER_DOC_SEQUENCE_VALUE = acra.DOC_SEQUENCE_VALUE
AND gjjlv.SUBLEDGER_DOC_SEQUENCE_id = acra.DOC_SEQUENCE_ID
--and glcc.segment1 ='30D'
AR Transactional query
------------------------------
SELECT ol.line_id,trxl.customer_trx_id, trx.trx_number
FROM ra_customer_trx_all trx,
ra_customer_trx_lines_all trxl,
oe_order_lines_all ol,
oe_order_headers_all oh,
oe_transaction_types_tl tt
WHERE trxl.interface_line_context ='ORDER ENTRY'
AND trxl.INTERFACE_LINE_ATTRIBUTE1 = to_char(oh.order_number)
AND trxl.INTERFACE_LINE_ATTRIBUTE2 = TT.name
AND TT.transaction_type_id = oh.ORDER_TYPE_ID
AND to_char(ol.LINE_ID) = TRXL.INTERFACE_LINE_ATTRIBUTE6
AND TRXL.line_type = 'LINE'
AND TRXL.INTERFACE_LINE_ATTRIBUTE11 = '0'
AND trx.CUSTOMER_TRX_ID = trxl.CUSTOMER_TRX_ID
AND oh.header_id = ol.header_id
AND EXISTS( SELECT 1 from fnd_languages where installed_flag = 'B' AND tt.language = language_code and rownum=1)
AND ol.line_id ").append(s).toString());

Queries for Use

Isupport
---------
SELECT p3.party_name Customername, p3.party_number Customernumber, p1.person_first_name' 'p1.person_middle_name' 'p1.person_last_name Contactname, hzc1.email_address Emailaddress, hzc2.raw_phone_number Primaryphonenumber, hca.cust_account_id, p3.party_id
FROM ibu_multi_parties mul,
hz_parties p1,
hz_parties p3,
hz_relationships r,
fnd_user u,
apps.hz_cust_accounts hca,
apps.HZ_CONTACT_POINTS hzc1,
apps.HZ_CONTACT_POINTS hzc2,
apps.hz_party_preferences pref
WHERE mul.party_id = r.party_id
AND mul.enable_flag = 'Y'
AND p3.party_type = 'ORGANIZATION'
AND p3.party_id = r.object_id
AND r.subject_id = p1.party_id
AND p1.party_type = 'PERSON'
AND u.employee_id IS NULL
AND r.relationship_code IN ('EMPLOYEE_OF', 'CONTACT_OF')
AND TRUNC (NVL (u.end_date, SYSDATE + 1)) >= TRUNC (SYSDATE)
AND u.user_id NOT IN ( SELECT user_id FROM jtf_um_usertype_reg WHERE status_code IN ('PENDING', 'UPGRADE_APPROVAL_PENDING')) AND u.user_id = mul.user_id
AND u.user_id = fnd_global.user_id
AND hca.party_id = p3.party_id
AND hzc1.owner_table_name(+) = 'HZ_PARTIES'
AND hzc1.contact_point_type(+) ='EMAIL'
AND hzc2.owner_table_name(+) = 'HZ_PARTIES'
AND hzc2.contact_point_type(+) = 'PHONE'
AND hzc1.primary_flag(+) = 'Y'
AND hzc2.primary_flag(+) = 'Y'
AND hzc1.owner_table_id(+) = u.customer_id
AND hzc1.owner_table_id = hzc2.owner_table_id(+)
AND pref.party_id = r.party_id
AND pref.module='IBU_ACCOUNT'
AND pref.category = 'PRIMARY_ACCOUNT'
AND pref.preference_code='CUSTOMER_ACCOUNT_ID'
AND mul.current_party = 'Y'
AND hca.cust_account_id = Decode(pref.value_number,NULL,hca.cust_account_id,0,hca.cust_account_id,pref.value_number)
OM
----
SELECT loc.location_id ShipLocId ,loc.address1', 'loc.address2', 'loc.address3', 'loc.city', 'loc.postal_code', 'loc.state', 'loc.country ShipAdd, hcsu.site_use_id ship_site_use_id
FROM apps.hz_parties hp ,
apps.hz_party_sites hps ,
apps.hz_locations loc ,
apps.hz_cust_accounts_all hca ,
apps.hz_cust_acct_sites_all hcas ,
apps.hz_cust_site_uses_all hcsu
where hps.party_id = hp.party_id
AND loc.location_id = hps.location_id
AND hca.party_id = hp.party_id
AND hcas.party_site_id = hps.party_site_id
AND hcas.cust_account_id = hca.cust_account_id
AND hcas.CUST_ACCT_SITE_ID = hcsu.cust_acct_site_id
AND hcsu.site_use_code = 'SHIP_TO'
AND hcas.org_id = hcsu.org_id
AND hcas.org_id = NVL(hca.org_id,hcas.org_id)
AND hca.cust_account_id = NVL(:1,hca.cust_account_id)
AND NVL(hcsu.status,'I') = 'A'
INSTALL BASE
-----------------
SELECT distinct KFV.attribute12 Model_type, CII.serial_number serial_number, HZA.cust_account_id cust_account_id
FROM CSI_ITEM_INSTANCES CII,
CSI_IP_ACCOUNTS CIA,
CSI_I_ORG_ASSIGNMENTS CIOA,
CSI_I_PARTIES CIP,
CSI_II_RELATIONSHIPS CIR,
CSI_INSTANCE_STATUSES CIS,
CSI_LOOKUPS CL,
CSI_LOOKUPS CL3,
CSI_SYSTEMS_VL CSV,
MTL_SYSTEM_ITEMS_KFV KFV,
OE_AGREEMENTS A1,
ASO_I_OE_ORDER_HEADERS_V OOH,
ASO_I_OE_ORDER_LINES_V OOL,
HZ_CUST_ACCOUNTS HZA,
HZ_PARTIES HZP
WHERE CII.SYSTEM_ID = CSV.SYSTEM_ID(+)
AND CII.INSTANCE_TYPE_CODE = CL.LOOKUP_CODE(+)
AND CL.LOOKUP_TYPE(+) = 'CSI_INST_TYPE_CODE'
AND CII.LAST_OE_AGREEMENT_ID = A1.AGREEMENT_ID(+)
AND CII.INSTANCE_STATUS_ID = CIS.INSTANCE_STATUS_ID
AND CII.INVENTORY_ITEM_ID = KFV.INVENTORY_ITEM_ID
AND KFV.ORGANIZATION_ID = CII.INV_MASTER_ORGANIZATION_ID
AND CIR.RELATIONSHIP_TYPE_CODE = CL3.LOOKUP_CODE(+)
AND CL3.LOOKUP_TYPE(+) = 'CSI_INSTANCE_VERSION_LABELS'
AND CII.LAST_OE_ORDER_LINE_ID = OOL.LINE_ID(+)
AND OOL.HEADER_ID = OOH.HEADER_ID(+)
AND HZA.CUST_ACCOUNT_ID= CIA.PARTY_ACCOUNT_ID
AND HZA.PARTY_ID = HZP.PARTY_ID
AND CII.INSTANCE_ID = CIR.SUBJECT_ID(+)
AND CIR.RELATIONSHIP_TYPE_CODE (+)= 'COMPONENT-OF'
AND CIR.ACTIVE_END_DATE(+)IS NULL
AND CII.INSTANCE_ID = CIOA.INSTANCE_ID (+)
AND CIOA.RELATIONSHIP_TYPE_CODE (+) = 'SOLD_FROM'
AND CII.INSTANCE_ID = CIP.INSTANCE_ID
AND CIP.RELATIONSHIP_TYPE_CODE = 'OWNER'
AND CIP.PARTY_SOURCE_TABLE = 'HZ_PARTIES'
AND CIP.INSTANCE_PARTY_ID = CIA.INSTANCE_PARTY_ID (+)
AND CIA.RELATIONSHIP_TYPE_CODE(+) = 'OWNER'
AND CIA.ACTIVE_END_DATE(+) IS NULL
AND NOT EXISTS ( SELECT 1 FROM CSI_INSTALL_PARAMETERS CIPM WHERE CIPM.INTERNAL_PARTY_ID = CIP.PARTY_ID )
AND CII.serial_number IS NOT NULL;
SERVICE CONTRACTS
------------------------
select DECODE(coverage1,NULL,'No Coverage',coverage1) Service_Coverage,DECODE(warranty1,NULL,'No',warranty1) Warranty from ( Select MAX(DECODE(cov.NAME,'Servicepass Complete',Decode(cle.sts_code,'ACTIVE',cov.NAME,NULL),'Servicepass Standard',Decode(cle.sts_code,'ACTIVE',cov.NAME,NULL),'Qualitypass',Decode(cle.sts_code,'ACTIVE',cov.NAME,NULL),NULL)) coverage1 , MAX(DECODE(cov.NAME,'WARRANTY',Decode(cle.sts_code,'ACTIVE','Yes','No'),NULL)) warranty1
FROM okc_k_lines_b cle,
oks_k_lines_b kln,
okc_k_lines_tl cov,
okc_line_styles_tl lse,
okc_statuses_tl sts,
okc_k_items cit,
OKS_AUTH_LINES_V x
WHERE cle.cle_id IS NULL
AND kln.cle_id = cle.ID
AND lse.ID = cle.lse_id
AND lse.LANGUAGE = USERENV ('LANG')
AND cit.cle_id = cle.ID
AND cit.jtot_object1_code IN ('OKX_SERVICE', 'OKX_WARRANTY', 'OKX_USAGE', 'OKS_SUBSCRIPTION')
AND sts.code = cle.sts_code
--AND sts.meaning NOT IN ('Terminated')
AND sts.LANGUAGE = USERENV ('LANG')
AND cov.ID(+) = kln.coverage_id
AND cov.LANGUAGE(+) = USERENV ('LANG')
AND x.CLE_ID = cle.id
AND ( x.id IN ( select cle_id from okc_k_items where dnz_chr_id = cit.dnz_chr_id and object1_id1 IN (select instance_id from csi_item_instances where 1 = 1 and SERIAL_NUMBER =NVL(:1,SERIAL_NUMBER)))))

How to call jsps with preferred settings in iSupport.

Recently I got a chance to work on OAF customisation in Isupport.I had created few custom pages which are to be called from Isupport tabs.I made a call to these pages after deploying as OA.jsp?page=/oracle/apps/ibu/returnmgmt/webui/RmaProRepairInfPG.And navigated to other pages from these pages.But when I navigate back to the initial page which I had called the AM context is lost.Then I changed the call to jtfcrmchrome.jsp?page/oracle/apps/ibu/returnmgmt/webui/RmaProRepairInfPG now the AM context remains true and the required functionality is achieved.