Query to find AR Invoice details in oracle fusion

 select distinct
(select rba.name from ra_batches_all rba where rba.batch_id = rcta.batch_id and rownum <=1) batch_name,
hca_bill.attribute28 account_id, 
hca_bill.account_number,
hcsua_bill.cust_acct_site_id,
hps_bill.party_site_number,
hp_bill.party_name,
rcta.customer_trx_id,
rcta.invoice_currency_code Currency,
rcta.trx_number credit_memo_number, 
rcta.trx_date credit_memo_date,
rctla.extended_amount total_amount,
rctla.inventory_item_id,
(select item_number from egp_system_items_b esi where esi.inventory_item_id = rctla.inventory_item_id and rownum <=1 ) item_number,
rctla.description,
rctla.attribute6 service_start_date,
rctla.attribute7 servic_end_date,
rctla.quantity_credited,
(select uomt.unit_of_measure 
from inv_units_of_measure_tl uomt, inv_units_of_measure_b uomb 
where uomb.uom_code = rctla.uom_code
and uomt.language =userenv('LANG')
and uomt.unit_of_measure_id = uomb.unit_of_measure_id) UOM,
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.attribute3 opp_id,
rctla.attribute1 order_id,
rctla.attribute2 order_line_item_id,
rcta.attribute_category transaction_source,
(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,
rctla.creation_date,
rctla.created_by,
rctla.last_update_login,
rctla.trx_business_category,
rctla.product_fisc_classification,
rctla.product_type,
rcta.attribute13 sfdc_sync_status
/*--rcta.end_date_commitment,
--rcta.start_date_commitment,
rcta.batch_id,
(select attribute28 from hz_cust_accounts hca where rcta.bill_to_customer_id  = hca.cust_account_id and rownum <=1) account_id,
--rcta.ct_reference,
--previous_customer_trx_id
gjh.period_name*/
from ra_customer_trx_all rcta,
ra_customer_trx_lines_all rctla,
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
/*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 rcta.customer_trx_id = rctla.customer_trx_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 rcta.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*/
--and rcta.customer_trx_id =181754
and rcta.trx_number in  ('CM00849945','CM01249142')--('INV23994226','111100001030')

Query to find AR invoice, receipt and journal details in oracle fusion

 select 
distinct 
acra.receipt_date ,
xal.accounting_date,
gjh.period_name ,
(select flv.meaning from fnd_lookup_values flv where 
flv.lookup_code = apsa.class
and language =userenv('LANG')
and lookup_type = 'INV/CM' ) activity_class,
acra.receipt_number,
hca_bill.attribute28 account_id,
hp_bill.party_name,
arm.name receipt_method,
(SELECT distinct gcc.segment3||'-'||ffvt.description
FROM
        fnd_flex_value_sets ffvs ,
fnd_flex_values ffv ,
        fnd_flex_values_tl ffvt
        where 
  ffvs.flex_value_set_id = ffv.flex_value_set_id
AND ffv.flex_value_id = ffvt.flex_value_id
        AND ffvt.language = USERENV('LANG')
        AND ffv.ENABLED_FLAG='Y'
        AND ffv.SUMMARY_FLAG !='Y'
        AND ffv.flex_value=gcc.segment3
AND rownum <=1) natural_account_description,
(SELECT distinct gcc.segment1||'-'||ffvt.description
FROM
        fnd_flex_value_sets ffvs ,
fnd_flex_values ffv ,
        fnd_flex_values_tl ffvt
        where 
  ffvs.flex_value_set_id = ffv.flex_value_set_id
AND ffv.flex_value_id = ffvt.flex_value_id
        AND ffvt.language = USERENV('LANG')
        AND ffv.ENABLED_FLAG='Y'
        AND ffv.SUMMARY_FLAG !='Y'
        AND ffv.flex_value=gcc.segment1
AND rownum <=1) balancing_segment_description,
gjl.description memo,
acra.amount,
araa.acctd_amount_applied_from Accounted_Amount,
araa.amount_applied Applied_Amount,
/*gjl.ENTERED_DR,
gjl.ENTERED_CR,
gjl.accounted_dr,
gjl.accounted_cr,
xal.accounted_dr debit,
xal.accounted_cr credit,*/
acra.attribute_category,
acra.ussgl_transaction_code_context,
acra.attribute1,
araa.apply_date activity_date,
acra.attribute2,
(select hou.name from hr_operating_units hou
where hou.organization_id=acra.org_id) activity_business_unit,
acra.attribute3,
acra.attribute4,
(select flv.meaning from fnd_lookup_values flv where 
flv.lookup_code = araa.status
and language =userenv('LANG')
and lookup_type = 'PAYMENT_TYPE' ) activity_status,
acra.attribute5,
rcta.trx_number,
rctta.name transaction_type,
rctta.type transaction_class,
xal.gl_sl_link_id
--flv.meaning
from 
ra_customer_trx_all rcta,
ra_cust_trx_types_all rctta,
--fnd_lookup_values flv,
ar_receivable_applications_all araa,
ar_payment_schedules_all apsa,
ar_cash_receipts_all acra,
ar_receipt_methods arm,
hz_cust_accounts hca_bill,
hz_parties hp_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.customer_trx_id = araa.applied_customer_trx_id 
and araa.cash_receipt_id = acra.cash_receipt_id
and rcta.org_id = acra.org_id
AND rcta.cust_trx_type_seq_id = rctta.cust_trx_type_seq_id 
--AND rctta.type =flv.lookup_code
AND acra.receipt_method_id = arm.receipt_method_id
AND araa.payment_schedule_id = apsa.payment_schedule_id
AND acra.cash_receipt_id = apsa.cash_receipt_id
AND acra.pay_from_customer=hca_bill.cust_account_id
AND hca_bill.party_id = hp_bill.party_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 xte.entity_code = 'RECEIPTS'
AND xte.source_id_int_1 = acra.cash_receipt_id
AND gjl.code_combination_id = gcc.code_combination_id
--and acra.receipt_number in ('P-11677740')--,'P-11596072')
AND gcc.segment3 = nvl(:p_account_code,gcc.segment3)
AND gcc.segment1 = nvl(:p_business_unit,gcc.segment1)
and araa.apply_date >= nvl(:p_period_from,araa.apply_date) 
and araa.apply_date <= nvl(:p_period_to,araa.apply_date)
--and rownum <=1000

Query to find AR invoice Journal details in Oracle Fusion

 select distinct
customer_trx_id,
trx_number invoice_number, 
trx_date invoice_date,
(select trx_number from ra_customer_trx_all rcm
where rcm.previous_customer_trx_id = rcta.customer_trx_id and rownum <=1) cm_number,
(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, 
NVL((SELECT SUM(extended_amount)
FROM ra_customer_trx_lines_all lines
WHERE lines.customer_trx_id = rcta.customer_trx_id
),0) invoice_amount,
rcta.end_date_commitment,
rcta.start_date_commitment,
rcta.batch_id,
(select rba.name from ra_batches_all rba where rba.batch_id = rcta.batch_id and rownum <=1) batch_name,
--rcta.ct_reference,
--previous_customer_trx_id
gjh.period_name
from ra_customer_trx_all rcta,
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
and rcta.customer_trx_id =181754
INV23994226
111100001030
select distinct
rcta.customer_trx_id,
rcta.trx_number invoice_number, 
rcta.trx_date invoice_date,
(select trx_number from ra_customer_trx_all rcm
where rcm.previous_customer_trx_id = rcta.customer_trx_id and rownum <=1) cm_number,
(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, 
/*NVL((SELECT SUM(extended_amount)
FROM ra_customer_trx_lines_all lines
WHERE lines.customer_trx_id = rcta.customer_trx_id
),0) invoice_amount,*/
rctla.extended_amount invoice_amount,
rctla.attribute6 start_date,
rctla.attribute7 end_date,
--rcta.end_date_commitment,
--rcta.start_date_commitment,
rcta.batch_id,
(select rba.name from ra_batches_all rba where rba.batch_id = rcta.batch_id and rownum <=1) batch_name,
(select attribute28 from hz_cust_accounts hca where rcta.bill_to_customer_id  = hca.cust_account_id and rownum <=1) account_id,
--rcta.ct_reference,
--previous_customer_trx_id
gjh.period_name
from ra_customer_trx_all rcta,
ra_customer_trx_lines_all rctla,
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 rcta.customer_trx_id = rctla.customer_trx_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 rcta.customer_trx_id =181754
--and rcta.trx_number in ('INV23994226','111100001030')
and rcta.bill_to_customer_id is not null

Query to find AR customer balance in oracle fusion

 SELECT
SUM(psa.amount_due_remaining) trx_balance,
psa.invoice_currency_code trx_currency,
cab.account_number billing_acct_num,
cab.account_name billing_account,
cas.account_number shipping_acct_num,
cas.account_name shipping_account
FROM
ra_customer_trx_all cta,
ar_payment_schedules_all psa,
hz_cust_accounts cab,
hz_cust_accounts cas
WHERE
psa.customer_trx_id = cta.customer_trx_id
AND cta.bill_to_customer_id = cab.cust_account_id
AND cta.ship_to_party_id = cas.party_id
AND cab.account_name = :billing_account_name
GROUP BY
psa.invoice_currency_code,
cab.account_number,
cab.account_name,
cas.account_number,
cas.account_name

query to find responsibility name from user

SELECT fu.user_name,
frt.responsibility_name,
furg.start_date,
furg.end_date,
fr.responsibility_key,
fa.application_short_name "Application Short Name"
FROM apps.fnd_user_resp_groups_direct furg,
apps.fnd_user fu,
apps.fnd_responsibility_tl frt,
apps.fnd_responsibility fr,
apps.fnd_application_tl fat,
apps.fnd_application fa
WHERE furg.user_id = fu.user_id
AND furg.responsibility_id = frt.responsibility_id
AND fr.responsibility_id = frt.responsibility_id
AND fa.application_id = fat.application_id
AND fr.application_id = fat.application_id
AND frt.language = USERENV('LANG')
AND UPPER(fu.user_name) = UPPER('SSHARMA')
ORDER BY furg.start_date;

Query to find concurrent programs using a value set in its parameter list

SELECT
fcp.user_concurrent_program_name,
fat.application_name,
fdfc.column_seq_num,
fdfc.form_left_prompt parameter_name,
fdfc.enabled_flag
FROM
apps.fnd_descr_flex_col_usage_vl fdfc,
apps.fnd_flex_value_sets ffvs,
apps.fnd_concurrent_programs_vl fcp,
apps.fnd_application_tl fat
WHERE
1 = 1
AND ffvs.flex_value_set_id = fdfc.flex_value_set_id
AND fdfc.descriptive_flexfield_name = '$SRS$.' || fcp.concurrent_program_name
AND fcp.application_id = fat.application_id
AND ffvs.flex_value_set_name = 'JEIT_CALENDAR_MONTH' --value set name

how to compile sql, pkb, ldt and wft file from shell script

#!/bin/bash

#* $Header:FACutomizationScript.sh  99.99 MM/DD/YYYY HH:MI:SS  *

########################################################################################################################

#

# Shell Script Name : XXX_CUSTOM.sh

#

# Purpose           : XXX_CUSTOM Custom Program Migration script

# Change History    : 04-Apr-2022 

#  

########################################################################################################################

APPS_USER='apps'

read -s -p "Enter Apps Password: " APPS_PASSWD

read -s -p "Enter SID: "  DB_SID

#APPS_PASSWD

#DB_SID=

date

who am i

cd $XXX_TOP/UPGRADE

echo "Compiling plsql" 

echo "Compiling view XXX_OE_PKG_SPEC.sql"

echo " "

sqlplus -s $APPS_USER/$APPS_PASSWD@$DB_SID @$XXX_TOP/R12UPGRADE/XXX_OE_PKG_SPEC.sql

echo "Compiling XXX_OE_PKG_BODY.sql"

echo " "

sqlplus -s $APPS_USER/$APPS_PASSWD@$DB_SID @$XXX_TOP/R12UPGRADE/XXX_OE_PKG_BODY.sql

echo "Executing file XXX_POXRQERQ.ldt"

echo " "

FNDLOAD $APPS_USER/$APPS_PASSWD 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct XXX_POXRQERQ.ldt

echo "Compiling XXX_Lookup_Code.sql"

echo " "

sqlplus -s $APPS_USER/$APPS_PASSWD@$DB_SID @$XXX_TOP/R12UPGRADE/XXX_Lookup_Code.sql

echo "Compiling XXX_ACCT_GEN_PKG.pks"

echo " "

sqlplus -s $APPS_USER/$APPS_PASSWD@$DB_SID @$OVT_TOP/R12UPGRADE/XXX_ACCT_GEN_PKG.pks

echo "Compiling XXX_ACCT_GEN_PKG.pkb"

echo " "

sqlplus -s $APPS_USER/$APPS_PASSWD@$DB_SID @$OVT_TOP/R12UPGRADE/XXX_ACCT_GEN_PKG.pkb

echo "copy XXXRWMO.rdf"

echo " "

cp $XXX_TOP/R12UPGRADE/XXXRWMO.rdf $XXX_TOP/reports/US

echo "copy XXXTOPKL.rdf"

echo " "

cp $XXX_TOP/R12UPGRADE/XXXTOPKL.rdf $XXX_TOP/reports/US

echo "Upload XXX_POWFACCT_MODIFIED.wft"

echo " "

WFLOAD $APPS_USER/$APPS_PASSWD 0 Y FORCE XXX_POWFACCT_MODIFIED.wft

echo "Upload XXX_CRP_V3.wft"

echo " "

WFLOAD $APPS_USER/$APPS_PASSWD 0 Y FORCE XXX_CRP_V3.wft

date

who am i

echo "End of script"

# --------------------------------------------------------------------

#  End of Script

# --------------------------------------------------------------------