AR Balance of Advance Received in Selected Currency
Description
Categories: BI Publisher
Imported from BI Publisher
Application: Receivables
Source: Balance of Advance Received in Selected Currency
Short Name: AR_ADV_BAL_SEL_CURR
DB package: AR_ADV_BAL_SEL_CURR_PKG
Application: Receivables
Source: Balance of Advance Received in Selected Currency
Short Name: AR_ADV_BAL_SEL_CURR
DB package: AR_ADV_BAL_SEL_CURR_PKG
Run
AR Balance of Advance Received in Selected Currency and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT class ,customer_trx_id ,adv_num ,adv_date ,cust_num ,cust_name ,agent ,curr ,advance_amount ,advance_due ,advance_applied ,(advance_amount - advance_applied) * exchange_rate adv_bal_func ,seg_all ,seg_acc ,ctrl_seg FROM (SELECT RCTT.type class ,TRX.customer_trx_id customer_trx_id ,APS.trx_number adv_num ,TRX.trx_date adv_date ,HCA.account_number cust_num ,HZ.party_name cust_name ,REP.name agent ,TRX.invoice_currency_code curr ,NVL(trx.exchange_rate,1) exchange_rate ,NVL(APS.amount_due_original,0) advance_amount ,NVL(aps.amount_due_remaining,0) advance_due ,(SELECT NVL(SUM(NVL(ara.amount,0)),0) FROM ar_adjustments_all ARA ,ra_customer_trx_all TRX1 WHERE TRX1.initial_customer_trx_id = TRX.customer_trx_id AND ARA.customer_trx_id = TRX1.customer_trx_id AND TRX1.complete_flag = 'Y' AND ARA.adjustment_type = 'C' )*-1 advance_applied , fnd_flex_xml_publisher_apis.process_kff_combination_1('flex_select_all', 'SQLGL', 'GL#', gcc.CHART_OF_ACCOUNTS_ID, NULL, gcc.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') seg_all , fnd_flex_xml_publisher_apis.process_kff_combination_1('flex_select_acc', 'SQLGL', 'GL#', gcc.CHART_OF_ACCOUNTS_ID, NULL, gcc.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'N', 'VALUE') seg_acc , &FLEX_SELECT_CTRL_SEG ctrl_seg FROM ra_customer_trx trx , ra_cust_trx_line_gl_dist_all lgd , ar_payment_schedules_all aps , ra_cust_trx_types_all rctt , gl_code_combinations gcc , ra_salesreps_all rep , hz_parties hz , hz_cust_accounts hca where trx.customer_trx_id = lgd.customer_trx_id and trx.customer_trx_id = aps.customer_trx_id and trx.cust_trx_type_id = rctt.cust_trx_type_id and trx.org_id = rctt.org_id and lgd.code_combination_id = gcc.code_combination_id and trx.primary_salesrep_id = rep.salesrep_id(+) and trx.bill_to_customer_id = hca.cust_account_id and hz.party_id = hca.party_id and lgd.account_class = 'REC' and lgd.latest_rec_flag = 'Y' and rctt.type = 'DEP' and trx.complete_flag = 'Y' and &gc_currency_code_where and &FLEX_WHERE_GL_ACCOUNT and &FLEX_WHERE_CTRL_SEG and APS.trx_number >=NVL(:P_COMMITMENT_LOW,APS.trx_number) and APS.trx_number <=NVL(:P_COMMITMENT_HIGH,APS.trx_number) and hca.cust_account_id >=NVL(:P_CUSTOMER_NUMBER_LOW,hca.cust_account_id) and hca.cust_account_id <=NVL(:P_CUSTOMER_NUMBER_HIGH,hca.cust_account_id) AND DECODE (:P_ASSGN_ACC_TO,'General Ledger Date',LGD.gl_date,TRX.trx_date) <= :P_AS_OF_DATE AND DECODE (:P_ASSGN_ACC_TO,'General Ledger Date',LGD.gl_date,TRX.trx_date) >= NVL(:P_COMMITMENT_DATE,SYSDATE) ) ORDER BY curr ,ctrl_seg ,DECODE (UPPER(:P_SORT_BY),'CUSTOMER NUMBER',cust_num,adv_num) |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Ledger/Ledger Set |
|
LOV Oracle | |
Commitment Date |
|
Date | |
As Of Date |
|
Date | |
Currency Type |
|
LOV Oracle | |
Currency Code |
|
LOV Oracle | |
Assigned According To |
|
LOV Oracle | |
Format |
|
LOV Oracle | |
Sort By |
|
LOV Oracle | |
Include Transaction Statement |
|
LOV Oracle | |
Control Segment |
|
LOV Oracle | |
Control Segment Low |
|
Char | |
Control Segment High |
|
Char | |
Account Segment Low |
|
Char | |
Account Segment High |
|
Char | |
Advance Number Low |
|
LOV Oracle | |
Advance Number High |
|
LOV Oracle | |
Customer Number Low |
|
LOV Oracle | |
Customer Number High |
|
LOV Oracle |