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
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