AR Potential Reconciling Items

Description
Categories: BI Publisher, Financials
Application: Receivables
Source: Potential Reconciling Items Report (XML)
Short Name: ARXPIREP_XML
DB package: AR_ARXPIREP_XMLP_PKG
select       rep.ref10 category_dbc,
                rep.ref29 category_code_dbc,
                rep.ccid ccid_dbc,
                fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg', 'SQLGL', 'GL#', c.CHART_OF_ACCOUNTS_ID, NULL, c.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') company_dbc,
                fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_acct_seg', 'SQLGL', 'GL#', c.CHART_OF_ACCOUNTS_ID, NULL, c.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'Y', 'VALUE') account_dbc,
                fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_all_seg', 'SQLGL', 'GL#', c.CHART_OF_ACCOUNTS_ID, NULL, c.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') flex_dbc,
                rep.ref26 customer_dbc,
                rep.ref24 payment_dbc,
                rep.ref25 transaction_dbc,
                rep.trx_date,
                rep.gl_date gl_date_dbc,
                decode( rep.currency, :functional_currency,null, rep.currency) currency_code_dbc,
                rep.currency currency_code_title_dbc,
                to_number(decode( rep.currency, :functional_currency, null,rep.entered_dr )) entered_dr_dbc,
                to_number(decode( rep.currency,  :functional_currency, null, rep.entered_cr )) entered_cr_dbc,
                rep.acctd_dr accounted_dr_dbc,
                rep.acctd_cr accounted_cr_dbc,
			    rep.pc_id    posting_control_id,
                substrb(rep.class_dbc,1,20) class_dbc,
			AR_ARXPIREP_XMLP_PKG.set_curr_code_dbcformula(decode ( rep.currency , :functional_currency , null , rep.currency )) Set_Curr_Code_Dbc,
			AR_ARXPIREP_XMLP_PKG.ref_curr_code_p ref_curr_code,
			AR_ARXPIREP_XMLP_PKG.Sum_Sp_Ent_Cr_DbC_p Sum_Sp_Ent_Cr_DbC,
			AR_ARXPIREP_XMLP_PKG.Sum_Sp_Acct_Dr_DbC_p Sum_Sp_Acct_Dr_DbC,
			AR_ARXPIREP_XMLP_PKG.Sum_Sp_Acct_Cr_DbC_p Sum_Sp_Acct_Cr_DbC,
			AR_ARXPIREP_XMLP_PKG.Tmp_Ent_Cr_DbC_p Tmp_Ent_Cr_DbC,
			AR_ARXPIREP_XMLP_PKG.Tmp_Acct_Dr_DbC_p Tmp_Acct_Dr_DbC,
			AR_ARXPIREP_XMLP_PKG.Tmp_Acct_Cr_DbC_p Tmp_Acct_Cr_DbC,
			AR_ARXPIREP_XMLP_PKG.Tmp_Ent_Dr_DbC_p Tmp_Ent_Dr_DbC,
			AR_ARXPIREP_XMLP_PKG.Sum_Sp_Ent_Dr_DbC_p Sum_Sp_Ent_Dr_DbC,
			AR_ARXPIREP_XMLP_PKG.posted_yes_noformula(rep.pc_id) Posted_yes_no,
			AR_ARXPIREP_XMLP_PKG.set_sum_spformula(to_number ( decode ( rep.currency , :functional_currency , null , rep.entered_dr ) ), to_number ( decode ( rep.currency , :functional_currency , null , rep.entered_cr ) ), rep.acctd_dr, rep.acctd_cr, rep.ref29) Set_Sum_Sp
 FROM
 (SELECT
        adj.apply_date                                          trx_date,
	  adj.gl_date                                             gl_date,
        ct.invoice_currency_code                                currency,
        ard.code_combination_id                                 ccid,
        ard.amount_dr                                           entered_dr,
        ard.amount_cr                                           entered_cr,
        ard.acctd_amount_dr                                     acctd_dr,
        ard.acctd_amount_cr                                     acctd_cr,
        arpt_sql_func_util.get_lookup_meaning('ARRGTA_FUNCTION_MAPPING'
                              ,'ADJ_' || ard.source_type)       ref10,
        to_char(adj.adjustment_id)                              ref22,
        to_char(ard.line_id)                                    ref23,
        adj.adjustment_number                           	    ref24,
        ct.trx_number                                           ref25,
        acct.account_number                                     ref26,
        to_char(decode(ctt.type,
                        'BR',ct.drawee_id,
                        ct.bill_to_customer_id))                 ref27,
        'ADJ'                                                   ref28,
        'ADJ_' || ard.source_type                               ref29,
	  adj.posting_control_id					    pc_id,
         arpt_sql_func_util.get_lookup_meaning('INV/CM',ctt.type) class_dbc
FROM
          &lp_ra_customer_trx_all ct ,
          &lp_ra_cust_trx_types_all ctt,
          &lp_ar_distributions_all ard ,
          &lp_hz_cust_accounts_all acct,
          &lp_ar_adjustments_all adj,
	    gl_code_combinations glc
WHERE
        nvl(adj.postable,'Y') = 'Y'
        and adj.adjustment_id = ard.source_id
        and ard.source_table = 'ADJ'
        and adj.customer_trx_id = ct.customer_trx_id
        and ctt.cust_trx_type_id = ct.cust_trx_type_id
        and acct.cust_account_id = decode(ctt.type,'BR',ct.drawee_id,ct.bill_to_customer_id)
        and ard.code_combination_id = glc.code_combination_id
        and nvl(ct.org_id, -99) = nvl(ctt.org_id, -99)
        and adj.gl_date between :p_low_date and :p_high_date
&p_org_where_ct
&p_org_where_ctt
&p_org_where_ard
&p_org_where_acct
&p_org_where_adj
UNION ALL
SELECT
	  ct.trx_date   trx_date,
        gldist.gl_date gl_date,
        ct.invoice_currency_code currency,
        gldist.code_combination_id   ccid,
        to_number(decode(gldist.account_class,
                'REC', decode(sign(nvl(gldist.amount,0)),
                                -1,null,nvl(gldist.amount,0)),
                decode(sign(nvl(gldist.amount,0)),
                        -1,-nvl(gldist.amount,0),null))) entered_dr,
        to_number(decode(gldist.account_class,
                'REC', decode(sign(nvl(gldist.amount,0)),
                                -1,-nvl(gldist.amount,0),null),
                decode(sign(nvl(gldist.amount,0)),
                        -1,null,nvl(gldist.amount,0)))) entered_cr,
        to_number(decode(gldist.account_class,
                'REC', decode(sign(nvl(gldist.acctd_amount,0)),
                                -1,null,nvl(gldist.acctd_amount,0)),
                decode(sign(nvl(gldist.acctd_amount,0)),
                        -1,-nvl(gldist.acctd_amount,0),null))) acctd_dr,
        to_number(decode(gldist.account_class,
                'REC', decode(sign(nvl(gldist.acctd_amount,0)),
                                -1,-nvl(gldist.acctd_amount,0),null),
                decode(sign(nvl(gldist.acctd_amount,0)),
                        -1,null,nvl(gldist.acctd_amount,0)))) acctd_cr,
	ARPT_SQL_FUNC_UTIL.get_lookup_meaning('ARRGTA_FUNCTION_MAPPING',
                               decode(ctt.type,
                                       'CM', 'CM_',
                                       'DM', 'DM_',
                                       'CB', 'CB_',
                                       'INV_')||
                               nvl(gldist.account_class,'REV'))  ref10,
        to_char(ct.customer_trx_id)                             ref22,
        to_char(gldist.cust_trx_line_gl_dist_id)                 ref23,
        to_char(null)                                           ref24,
        ct.trx_number                                           ref25,
        acct.account_number                                     ref26,
        to_char(ct.bill_to_customer_id)                         ref27,
        decode(ctt.type,
                'CM', 'CM',
                'DM', 'DM',
                'CB', 'CB',
                'INV')                                          ref28,
        decode(ctt.type,
                'CM', 'CM_',
                'DM', 'DM_',
                'CB', 'CB_',
                'INV_')||gldist.account_class                   ref29,
	gldist.posting_control_id					   pc_id,
             ARPT_SQL_FUNC_UTIL.get_lookup_meaning('INV/CM',ctt.type) class_dbc
FROM
	  &lp_ra_cust_trx_gl_dist_all gldist,
          &lp_ra_customer_trx_all ct,
          &lp_ra_cust_trx_types_all ctt,
          &lp_hz_cust_accounts_all acct,
      	  gl_code_combinations glc
WHERE
          gldist.account_set_flag = 'N'
          and gldist.customer_trx_id = ct.customer_trx_id
          and ct.complete_flag = 'Y'
          and ct.cust_trx_type_id = ctt.cust_trx_type_id
          and acct.cust_account_id = ct.bill_to_customer_id
          and ctt.type in ('INV','GUAR','DEP','CM', 'DM', 'CB')
          and gldist.code_combination_id = glc.code_combination_id
          and nvl(ct.org_id, -99) = nvl(ctt.org_id, -99)
          and gldist.gl_date between :p_low_date and :p_high_date
&p_org_where_gldist
&p_org_where_ct
&p_org_where_ctt
&p_org_where_acct
UNION ALL
SELECT
        crh.trx_date                                                    trx_date,
        crh.gl_date                                                     gl_date,
        cr.currency_code                                                currency,
        ard.code_combination_id                                         ccid,
        ard.amount_dr                                        entered_dr,
        ard.amount_cr                                        entered_cr,
        ard.acctd_amount_dr                                  acctd_dr,
        ard.acctd_amount_cr                                  acctd_cr,
        arpt_sql_func_util.get_lookup_meaning('ARRGTA_FUNCTION_MAPPING',
         decode( cr.type,'MISC', 'MISC_','TRADE_')||ard.source_type)   ref10,
        decode(cr.type,
               'CASH',to_char(cr.cash_receipt_id)||'C'||
                      to_char(crh.cash_receipt_history_id),
               'MISC',to_char(cr.cash_receipt_id))                      ref22,
        to_char(ard.line_id)                                            ref23,
        cr.receipt_number                                               ref24,
        decode(cr.type,
               'CASH',to_char(null),
               'MISC',to_char(crh.cash_receipt_history_id))             ref25,
        acct.account_number                                             ref26,
        to_char(cr.pay_from_customer)                                   ref27,
        decode( cr.type,
               'MISC', 'MISC',
               'TRADE')                                                 ref28,
        decode( cr.type,
               'MISC', 'MISC_',
               'TRADE_')||ard.source_type                               ref29,
	  crh.posting_control_id							pc_id,
        arpt_sql_func_util.get_lookup_meaning('ARRGTA_CATEGORIES',decode(cr.type,'CASH','TRADE',cr.type)) class_dbc
FROM    &lp_hz_cust_accounts_all acct,
        &lp_ar_distributions_all ard,
        &lp_ar_cash_receipts_all cr,
        &lp_ar_cash_receipt_history_all crh,
	  gl_code_combinations glc
WHERE   crh.cash_receipt_history_id = ard.source_id
        and ard.source_table = 'CRH'
        and crh.postable_flag = 'Y'
        and crh.cash_receipt_id = cr.cash_receipt_id
        and acct.cust_account_id(+) = cr.pay_from_customer
        and ard.code_combination_id = glc.code_combination_id
        and crh.gl_date between :p_low_date and :p_high_date
&p_org_where_acct
&p_org_where_cr
&p_org_where_crh
&p_org_where_ard
UNION ALL
SELECT /*+ index(RA AR_RECEIVABLE_APPLICATIONS_N6) */
        ra.apply_date                                                   trx_date,
        ra.gl_date                                                      gl_date,
        decode(ra.status,
                 'APP',decode(ard.source_type,  'EXCH_GAIN',
                                                      decode(cr.currency_code,
                                                      gl.currency_code,ct.invoice_currency_code,
                                                                  cr.currency_code),
                                                'EXCH_LOSS',
                                                      decode(cr.currency_code,
                                                      gl.currency_code,ct.invoice_currency_code,
                                                                  cr.currency_code),
                                                'CURR_ROUND',
                                                      decode(cr.currency_code,
                                                      gl.currency_code,ct.invoice_currency_code,
                                                                  cr.currency_code),
                                                 ct.invoice_currency_code),
                       cr.currency_code)   currency,
        ard.code_combination_id                                         ccid,
        ard.amount_dr                                                   entered_dr,
        ard.amount_cr                                                   entered_cr,
        ard.acctd_amount_dr                                             acctd_dr,
        ard.acctd_amount_cr                                             acctd_cr,
        arpt_sql_func_util.get_lookup_meaning('ARRGTA_FUNCTION_MAPPING',
          decode(ra.amount_applied_from,null, 'TRADE_',
                                   'CCURR_') || ard.source_type )    ref10,
        decode(ra.application_type,
                'CASH',to_char(cr.cash_receipt_id)||'C'||
                       to_char(ra.receivable_application_id),
                'CM', to_char(ra.receivable_application_id))            ref22,
        to_char(ard.line_id)                                            ref23,
        cr.receipt_number                                               ref24,
        ct.trx_number                                                ref25,
        acct.account_number                                             ref26,
        to_char(cr.pay_from_customer)                                   ref27,
        decode(ra.amount_applied_from,
                  null,'TRADE','CCURR')                                 ref28,
        decode(ra.amount_applied_from,
                 null, 'TRADE_',
                       'CCURR_') || ard.source_type                     ref29,
	 ra.posting_control_id								pc_id,
        arpt_sql_func_util.get_lookup_meaning('ARRGTA_CATEGORIES',decode(cr.type,'CASH','TRADE',cr.type)) class_dbc
 FROM
        &lp_ar_receivable_apps_all ra,
        &lp_ar_cash_receipts_all cr,
        &lp_ar_distributions_all ard,
        &lp_ra_customer_trx_all ct,
        &lp_hz_cust_accounts_all acct,
        &lp_ar_system_parameters_all param,
        gl_sets_of_books gl,
	  gl_code_combinations glc
WHERE   ard.source_table = 'RA'
        and ard.source_id = ra.receivable_application_id
        and nvl(ra.postable,'Y') = 'Y'
        and nvl(ra.confirmed_flag,'Y') = 'Y'
        and ra.cash_receipt_id = cr.cash_receipt_id(+)
        and ra.applied_customer_trx_id = ct.customer_trx_id(+)
        and acct.cust_account_id(+) = cr.pay_from_customer
        and param.set_of_books_id = gl.set_of_books_id
        and ra.set_of_books_id = param.set_of_books_id
        and ard.code_combination_id = glc.code_combination_id
        and ra.gl_date between :p_low_date and :p_high_Date
        and nvl(ra.org_id,-99) = nvl(param.org_id, -99)
&p_org_where_ra
&p_org_where_cr
&p_org_where_ard
&p_org_where_ct
&p_org_where_acct
UNION ALL
       SELECT /*+ ORDERED */
        ra.apply_date                                                   trx_date,
        ra.gl_date                                                      gl_date,
        ct.invoice_currency_code                                        currency,
        ard.code_combination_id                                         ccid,
        ard.amount_dr                                                   entered_dr,
        ard.amount_cr                                                   entered_cr,
        ard.acctd_amount_dr                                             acctd_dr,
        ard.acctd_amount_cr                                             acctd_cr,
        arpt_sql_func_util.get_lookup_meaning('ARRGTA_FUNCTION_MAPPING',
                            'CMAPP_' || ard.source_type)                ref10,
        to_char(ra.receivable_application_id)                           ref22,
        to_char(ard.line_id)                                            ref23,
        ct.trx_number                                                 ref24,
        ctinv.trx_number                                                ref25,
        acct.account_number                                             ref26,
        to_char(ct.bill_to_customer_id)                                 ref27,
        'CMAPP'                                                         ref28,
        'CMAPP_' || ard.source_type                                     ref29,
	  ra.posting_control_id 							pc_id,
         arpt_sql_func_util.get_lookup_meaning('INV/CM','CM') class_dbc
  FROM
             &lp_ar_receivable_apps_all ra ,
             &lp_ra_customer_trx_all ct ,
             &lp_ra_customer_trx_all ctinv ,
             &lp_ra_cust_trx_gl_dist_all gldist ,
             &lp_ar_distributions_all ard ,
             &lp_hz_cust_accounts_all acct,
	     gl_code_combinations glc
 WHERE
        ard.source_table = 'RA'
        and ard.source_id = ra.receivable_application_id
        and nvl(ra.postable,'Y') = 'Y'
        and nvl(ra.confirmed_flag,'Y') = 'Y'
        and ra.status||'' = 'APP'
        and ra.customer_trx_id = ct.customer_trx_id
        and ra.customer_trx_id = gldist.customer_trx_id
        and gldist.account_class = 'REC'
        and gldist.latest_rec_flag = 'Y'
        and ra.applied_customer_trx_id = ctinv.customer_trx_id
        and acct.cust_account_id = ct.bill_to_customer_id
        and ra.gl_date between :p_low_date and :p_high_date
	  and ard.code_combination_id = glc.code_combination_id
&p_org_where_ra
&p_org_where_ct
&p_org_where_ctinv
&p_org_where_gldist
&p_org_where_ard
&p_org_where_acct
UNION ALL
SELECT  mcd.apply_date                                          trx_date,
        mcd.gl_date                                             gl_date,
        cr.currency_code                                        currency,
        ard.code_combination_id                                 ccid,
        ard.amount_dr                                           entered_dr,
        ard.amount_cr                                           entered_cr,
        ard.acctd_amount_dr                                     acctd_dr,
        ard.acctd_amount_cr                                     acctd_cr,
        arpt_sql_func_util.get_lookup_meaning('ARRGTA_FUNCTION_MAPPING',
                   'MISC_' || ard.source_type)                  ref10,
        to_char(cr.cash_receipt_id)                             ref22,
        to_char(ard.line_id)                                    ref23,
        cr.receipt_number                                       ref24,
        to_char(mcd.misc_cash_distribution_id)                  ref25,
        null                                                    ref26,
        null                                                    ref27,
        'MISC'                                                  ref28,
        'MISC_' || ard.source_type                              ref29,
	  mcd.posting_control_id					    pc_id,
        arpt_sql_func_util.get_lookup_meaning('ARRGTA_CATEGORIES',decode(cr.type,'CASH','TRADE',cr.type)) class_dbc
 FROM
        &lp_ar_misc_cash_dists_all mcd,
        &lp_ar_distributions_all ard,
        &lp_ar_cash_receipts_all cr,
	  gl_code_combinations glc
 WHERE  mcd.cash_receipt_id = cr.cash_receipt_id
        and ard.source_table = 'MCD'
        and ard.source_id = mcd.misc_cash_distribution_id
        and mcd.gl_date between :p_low_date and :p_high_date
        and ard.code_combination_id = glc.code_combination_id
&p_org_where_mcd
&p_org_where_ard
&p_org_where_cr
UNION ALL
SELECT
        th.trx_date                                                    trx_date,
        th.gl_date                                                     gl_date,
        ct.invoice_currency_code                                        currency,
        ard.code_combination_id                                         ccid,
        ard.amount_dr                                        entered_dr,
        ard.amount_cr                                        entered_cr,
        ard.acctd_amount_dr                                  acctd_dr,
        ard.acctd_amount_cr                                  acctd_cr,
        arpt_sql_func_util.get_lookup_meaning('ARRGTA_FUNCTION_MAPPING',
          decode(ctt.type,'BR', 'BR_', 'BR_')||
          ard.source_type ||decode(NVL(ard.source_type_secondary,'1'),'1',
           NULL,'_'||ard.source_type_secondary))                      ref10,
        to_char(ct.customer_trx_id)                                     ref22,
        to_char(ard.line_id)                                            ref23,
        ct.trx_number                                                   ref24,
        to_char(th.transaction_history_id)                             ref25,
        acct.account_number                                             ref26,
        to_char(ct.drawee_id)                                           ref27,
        decode(ctt.type, 'BR', 'BR', 'BR')                              ref28,
        'BR_'||ard.source_type||decode(NVL(ard.source_type_secondary,'1'),'1',NULL,'_'||ard.source_type_secondary)   ref29,
	  th.posting_control_id								pc_id,
         arpt_sql_func_util.get_lookup_meaning('INV/CM',ctt.type) class_dbc
FROM
                &lp_ra_customer_trx_all ct,
                &lp_ar_transaction_history_all th,
                &lp_ar_distributions_all ard,
                &lp_ra_cust_trx_types_all ctt,
                &lp_hz_cust_accounts_all acct,
	gl_code_combinations glc
WHERE   th.transaction_history_id = ard.source_id
        and ard.source_table = 'TH'
        and th.postable_flag = 'Y'
        and ct.customer_trx_id = th.customer_trx_id
        and acct.cust_account_id   = ct.drawee_id
        and ctt.cust_trx_type_id = ct.cust_trx_type_id
        and th.gl_date between :p_low_date and :p_high_date
        and nvl(ct.org_id,-99) = nvl(ctt.org_id,-99)
and ard.code_combination_id = glc.code_combination_id
&p_org_where_ct
&p_org_where_th
&p_org_where_ard
&p_org_where_ctt
&p_org_where_acct)  rep,
(&lp_adj_select
 union all
&lp_cb_select
union all
&lp_cm_select
union all
&lp_dm_select
union all
&lp_inv_select
union all
&lp_misc_select
union all
&lp_trade_select) cat,
GL_CODE_COMBINATIONS c
WHERE   rep.ccid = c.code_combination_id(+)
&lp_bal_low
&lp_bal_high
and rep.ref29 = cat.journal_category
and nvl(decode(c.account_type, 'A', cat.asset_type,'R', cat.revenue_type, 'E',cat.expense_type, 'L', cat.liability_type, 'N'), 'N') = 'N'
ORDER BY 12 ASC,
  1 ASC,
  4 ASC,
  5 ASC,
  6 ASC,
  3 ASC,
	decode( :p_currency_code, 'All', 'All', null,'All',rep.currency),
    rep.ref10,
    &ACCT_FLEX_BAL_SEG_OB,
    &ACCT_FLEX_ACCT_SEG_OB,
    &ACCT_FLEX_ALL_SEG_OB,
    rep.ref29,
    rep.ref26, rep.ref24, rep.ref25, rep.gl_date