Command to find a files containing a text string in linux

 grep -rwli "string_to_search" xxsss/12.0.0/bin

Query to find out the concurrent program name from Request Set

SELECT DISTINCT frst.request_set_id,

  frst.user_request_set_name request_set,

  frst.description description,

  frsst.request_set_stage_id,

  frsst.user_stage_name stage_name,

  frsst.description stage_description,

  fcp.user_concurrent_program_name  

FROM apps.fnd_request_sets_tl frst,

  apps.fnd_request_set_stages_tl frsst,

  apps.fnd_request_set_programs frsp,

  apps.fnd_concurrent_programs_tl fcp

WHERE frst.application_id =frsst.set_application_id

AND frst.request_set_id   = frsst.request_set_id

AND frst.request_set_id = frsp.request_set_id

AND frsst.request_set_stage_id = frsp.request_set_stage_id

AND frsp.concurrent_program_id = fcp.concurrent_program_id

AND frst.language =userenv('LANG')

AND fcp.language =userenv('LANG')

AND upper(frst.user_request_set_name) like '%'||upper('Request Set Name')||'%'

order by frst.user_request_set_name, frsst.request_set_stage_id;

Query to findout the Request Set from concurrent program

SELECT DISTINCT frst.request_set_id,

  frst.user_request_set_name request_set,

  frst.description description,

  frsst.request_set_stage_id,

  frsst.user_stage_name stage_name,

  frsst.description stage_description,

  fcp.user_concurrent_program_name  

FROM apps.fnd_request_sets_tl frst,

  apps.fnd_request_set_stages_tl frsst,

  apps.fnd_request_set_programs frsp,

  apps.fnd_concurrent_programs_tl fcp

WHERE frst.application_id =frsst.set_application_id

AND frst.request_set_id   = frsst.request_set_id

AND frst.request_set_id = frsp.request_set_id

AND frsst.request_set_stage_id = frsp.request_set_stage_id

AND frsp.concurrent_program_id = fcp.concurrent_program_id

AND frst.language =userenv('LANG')

AND fcp.language =userenv('LANG')

AND upper(fcp.user_concurrent_program_name) LIKE '%'||upper('Concurrent Program Name')||'%'

order by frst.user_request_set_name, frsst.request_set_stage_id;

Query to find AR transaction details in oracle fusion

 SELECT
hou.name business_unit,
rcta.trx_number transaction_number,
rcta.creation_date transaction_creation_date,
rcta.trx_date transaction_date,
apsa.due_date,
rcta.invoice_currency_code transaction_Currency,
hp_bill.party_name customer_name,
hca_bill.account_number customer_account_number,
hca_bill.attribute2 customer_site_id,
apsa.amount_due_original entered_amount,
round(apsa.amount_due_original * nvl(apsa.exchange_rate,1),2) accounted_amount_usd,
apsa.amount_due_remaining balance_amount_foreign_currency,
round(apsa.amount_due_remaining * nvl(apsa.exchange_rate,1),2) balance_amount_usd,
rcta.customer_trx_id
FROM ra_customer_trx_all rcta,
ar_payment_schedules_all apsa,
hr_operating_units hou,
hz_cust_accounts hca_bill,
hz_parties hp_bill
where 1=1
AND rcta.customer_trx_id = apsa.customer_trx_id
AND rcta.org_id = hou.organization_id
AND rcta.bill_to_customer_id = hca_bill.cust_account_id
AND hca_bill.party_id = hp_bill.party_id
AND rcta.trx_date >= nvl(:p_from_date,rcta.trx_date)
AND rcta.trx_date <= nvl(:p_to_date,rcta.trx_date)
AND hou.organization_id = nvl(:p_business_unit,hou.organization_id)
--AND rcta.trx_number in ('INV23994226','111100001030','99999100000000')

Query to find Subscription details in oracle fusion

 SELECT 
os.subscription_number,
os.extn_attribute_char019 subscription_business_source,
null account_name,
hca_bill.attribute2 account_site_id,
hca_bill.account_number account_number,
hca_bill.orig_system_reference account_crm_account_id,
os.extn_attribute_char036 ds_account_id,
hca_bill.status customer_account_status,
null account_partner_referring_account2,
hca_bill.attribute18 renewal_manager_name,
hca_bill.attribute16 account_owner_name,
null account_balance,
os.status subscription_status,
(SELECT MAX(cp.email_address)
   FROM hz_contact_points cp,
        hz_cust_account_roles acct_role
  WHERE acct_role.cust_account_role_id = os.bill_to_contact_id
    AND acct_role.relationship_id      = cp.relationship_id
    AND cp.owner_table_name            = 'HZ_PARTIES'
    AND cp.contact_point_type          = 'EMAIL'
    AND cp.status                      = 'A'
    AND cp.primary_flag                = 'Y'
) bill_to_work_email, 
(SELECT MAX(cp.email_address)
   FROM hz_contact_points cp,
        hz_cust_account_roles acct_role
  WHERE acct_role.cust_account_role_id = os.ship_to_contact_id
    AND acct_role.relationship_id      = cp.relationship_id
    AND cp.owner_table_name            = 'HZ_PARTIES'
    AND cp.contact_point_type          = 'EMAIL'
    AND cp.status                      = 'A'
    AND cp.primary_flag                = 'Y'
) ship_to_work_email,   
hl_ship.country ship_to_country,        
--rt.name term_type,
decode(os.end_date,null,'EVERGREEN','TERMED') term_type,
os.start_date,
os.creation_date,
os.end_date,
os.extn_attribute_char018 subscription_opportunity_id,
os.source_number subscription_order_id,
null subscription_order_type,
os.currency,
'CyberSource' payment_gateway_name
FROM oss_subscriptions os,
--ra_terms rt,
hz_cust_accounts hca_bill,
hz_parties hp_bill,
hz_cust_site_uses_all hcsua_bill,
hz_cust_acct_sites_all hcasa_bill,
hz_party_sites hps_bill,
hz_locations hl_bill,
--hz_cust_accounts hca_ship,
--hz_parties hp_ship,
--hz_cust_site_uses_all hcsua_ship,
hz_cust_acct_sites_all hcasa_ship,
hz_party_sites hps_ship,
hz_locations hl_ship
WHERE 1=1
--AND os.payment_term_id = rt.term_id(+)
AND os.bill_to_acct_id = hca_bill.cust_account_id
AND hca_bill.party_id = hp_bill.party_id
AND os.bill_to_site_use_id = hcsua_bill.site_use_id
AND hcsua_bill.cust_acct_site_id = hcasa_bill.cust_acct_site_id
AND hcasa_bill.party_site_id = hps_bill.party_site_id
AND hps_bill.location_id = hl_bill.location_id
--AND os.ship_to_acct_id = hca_bill.cust_account_id
--AND hca_ship.party_id = hp_ship.party_id
--AND os.bill_to_site_use_id = hcsua_ship.site_use_id
--AND hcsua_ship.cust_acct_site_id = hcasa_ship.cust_acct_site_id
AND os.ship_to_party_site_id = hcasa_ship.party_site_id(+)
AND hcasa_ship.party_site_id = hps_ship.party_site_id(+)
AND hps_ship.location_id = hl_ship.location_id(+)
/*AND os.end_date =
(CASE :p_subscription_type
WHEN 'EVERGREEN'
THEN  is null
WHEN 'TERMED'
THEN  is not null
END)*/
AND os.status = nvl(:p_subscription_status,os.status)
AND (:p_subscription_type = 'EVERGREEN' AND os.end_date IS NULL
OR :p_subscription_type = 'TERMED' AND os.end_date IS NOT NULL
OR :p_subscription_type is null and 1=1)
AND trunc(os.creation_date) >= nvl(:p_from_date,trunc(os.creation_date))
AND trunc(os.creation_date) <= nvl(:p_to_date,trunc(os.creation_date))
AND os.subscription_number ='A-S01375171'

Query to findout Credit memo details in oracle fusion

 select distinct
hca_bill.status account_status,
hca_bill.attribute28 account_id, 
hca_bill.account_number,
hcsua_bill.cust_acct_site_id,
hp_bill.party_name,
rcta.invoice_currency_code Currency,
decode(rcta.complete_flag,'Y','complete') credit_memo_status,
rcta.customer_trx_id credit_memo_id,
rcta.trx_number credit_memo_number, 
rcta.trx_date credit_memo_date,
rctla.line_type, 
decode(rctla.line_type,'LINE',rctla.extended_amount,0) without_tax_amount,
decode(rctla.line_type,'TAX',rctla.extended_amount,0) tax_amount,
rctla.extended_amount total_amount,
rcta.attribute_category transaction_source,
--decode((select count(*)  from xla_transaction_entities xte where rcta.customer_trx_id = xte.source_id_int_1 AND xte.entity_code = 'TRANSACTIONS' and rownum <=1),1,'Yes','No') accounting_status,
--decode((select distinct 'Y'  from xla_transaction_entities xte where xte.source_id_int_1=rcta.customer_trx_id AND xte.entity_code = 'TRANSACTIONS' and rownum <=1),'Y','Yes','No') accounting_status,
rctla.created_by,
rctla.creation_date,
(select name from ra_terms_vl rt where rt.term_id = rcta.term_id and rownum <=1) payment_term_name, 
(select description from ra_terms_vl rt where rt.term_id = rcta.term_id and rownum <=1) payment_term_desc, 
hl.state,
hl.country,
(select trx_number from ra_customer_trx_all rcm
where rcm.customer_trx_id = rcta.previous_customer_trx_id and rownum <=1) invoice_number,
rctla.attribute3 opp_id,
hou.name
from ra_customer_trx_all rcta,
ra_customer_trx_lines_all rctla,
ra_cust_trx_types_all rctta,
hr_operating_units hou,
hz_cust_accounts hca_bill,
hz_parties hp_bill,
hz_cust_site_uses_all hcsua_bill,
hz_cust_acct_sites_all hcasa_bill,
hz_party_sites hps_bill,
hz_locations hl
where 1=1
AND rcta.customer_trx_id = rctla.customer_trx_id
AND rcta.cust_trx_type_seq_id = rctta.cust_trx_type_seq_id 
AND rcta.bill_to_customer_id = hca_bill.cust_account_id
AND rcta.org_id = hou.organization_id
AND hca_bill.party_id = hp_bill.party_id
AND rcta.bill_to_site_use_id = hcsua_bill.site_use_id
AND hcsua_bill.cust_acct_site_id = hcasa_bill.cust_acct_site_id
AND hcasa_bill.party_site_id = hps_bill.party_site_id
AND hps_bill.location_id = hl.location_id
AND rcta.previous_customer_trx_id is not null
AND rcta.trx_number = nvl(:p_from_inv_number,rcta.trx_number) 
AND rcta.trx_number <= nvl(:p_to_inv_number,rcta.trx_number) 
AND rctta.type = nvl(:p_transaction_class,rctta.type)
AND hou.organization_id = nvl(:p_business_unit,hou.organization_id)
--and rcta.trx_number in  ('CM00849945','CM01249142')

AR Invoice Details in oracle fusion

 SELECT DISTINCT 
null customer_project,
hp_bill.party_name company_name,
hl_bill.country bill_to_country,
rcta.trx_date transaction_date,
rcta.creation_date date_created,
rctta.name transaction_type,
null document_number,
rcta.trx_number cexternal_id,
gjl.description memo,
/*( select distinct gjl.description
from
gl_je_headers gjh,
gl_je_lines gjl,
gl_import_references imp,
xla_ae_lines xal,
xla_ae_headers xah,
xla_events xe,
xla_transaction_entities xte
where 1=1
--and previous_customer_trx_id is not null
AND gjh.je_source = 'Receivables'
AND gjh.je_header_id = gjl.je_header_id
AND gjl.je_header_id = imp.je_header_id
AND gjl.je_line_num = imp.je_line_num
AND imp.gl_sl_link_id = xal.gl_sl_link_id
AND imp.gl_sl_link_table = xal.gl_sl_link_table
AND xal.application_id = xah.application_id
AND xal.ae_header_id = xah.ae_header_id
AND xah.application_id = xe.application_id
AND xah.event_id = xe.event_id
AND xe.application_id = xte.application_id
--AND xte.application_id = 222
AND xe.entity_id = xte.entity_id
AND xte.entity_code = 'TRANSACTIONS'
AND xte.source_id_int_1 = rcta.customer_trx_id) memo,*/
apsa.due_date,
null age,
rcta.invoice_currency_code Currency,
apsa.amount_due_original * nvl(apsa.exchange_rate,1) amount_foreign_currency,
apsa.amount_due_remaining * nvl(apsa.exchange_rate,1) amount_due_foreign_currency,
apsa.exchange_rate,
null Open_Balance,
null account_current_balance,
hou.organization_id,
hou.name
FROM ra_customer_trx_all rcta,
ra_cust_trx_types_all rctta,
ar_payment_schedules_all apsa,
hr_operating_units hou,
hz_cust_accounts hca_bill,
hz_parties hp_bill,
hz_cust_site_uses_all hcsua_bill,
hz_cust_acct_sites_all hcasa_bill,
hz_party_sites hps_bill,
hz_locations hl_bill,
gl_je_headers gjh,
gl_je_lines gjl,
gl_code_combinations gcc,
gl_import_references imp,
xla_ae_lines xal,
xla_ae_headers xah,
xla_events xe,
xla_transaction_entities xte
WHERE 1=1
AND rcta.cust_trx_type_seq_id = rctta.cust_trx_type_seq_id 
AND rcta.customer_trx_id = apsa.customer_trx_id
AND rcta.org_id = hou.organization_id
AND rcta.bill_to_customer_id   = hca_bill.cust_account_id
AND hca_bill.party_id = hp_bill.party_id
AND rcta.bill_to_site_use_id = hcsua_bill.site_use_id
AND hcsua_bill.cust_acct_site_id = hcasa_bill.cust_acct_site_id
AND hcasa_bill.party_site_id = hps_bill.party_site_id
AND hps_bill.location_id = hl_bill.location_id
AND gjh.je_source = 'Receivables'
AND gjh.je_header_id = gjl.je_header_id
AND gjl.je_header_id = imp.je_header_id
AND gjl.je_line_num = imp.je_line_num
AND imp.gl_sl_link_id = xal.gl_sl_link_id
AND imp.gl_sl_link_table = xal.gl_sl_link_table
AND xal.application_id = xah.application_id
AND xal.ae_header_id = xah.ae_header_id
AND xah.application_id = xe.application_id
AND xah.event_id = xe.event_id
AND xe.application_id = xte.application_id
--AND xte.application_id = 222
AND xe.entity_id = xte.entity_id
AND xte.entity_code = 'TRANSACTIONS'
AND xte.source_id_int_1 = rcta.customer_trx_id
AND gjl.code_combination_id = gcc.code_combination_id
AND gcc.segment3 = nvl(:p_account_code,gcc.segment3)
AND rcta.trx_date >= nvl(:p_from_date,rcta.trx_date)
AND rcta.trx_date <= nvl(:p_to_date,rcta.trx_date)
AND hou.organization_id = nvl(:p_business_unit,hou.organization_id)
--AND rcta.trx_number in ('INV23994226','111100001030')