AR Balance of Advance Received in Selected Currency

Description
Categories: BI Publisher, Financials
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
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
AR_SRS_NULL
 
AR_SRS_COA_ID
 
Number
Ledger ID
 
Number
Access Set ID
 
Customer Number High
 
LOV Oracle
Customer Number Low
 
LOV Oracle
Advance Number High
 
LOV Oracle
Advance Number Low
 
LOV Oracle
Account Segment High
 
Account Segment Low
 
Control Segment High
 
Control Segment Low
 
Control Segment
 
LOV Oracle
Include Transaction Statement
 
LOV Oracle
Sort By
 
LOV Oracle
Format
 
LOV Oracle
Assigned According To
 
LOV Oracle
Currency Code
 
LOV Oracle
Currency Type
 
LOV Oracle
As Of Date
 
Date
Commitment Date
 
Date
Ledger/Ledger Set
 
LOV Oracle