AR Potential Reconciling Items
Description
Categories: BI Publisher
Application: Receivables
Source: Potential Reconciling Items Report (XML)
Short Name: ARXPIREP_XML
DB package: AR_ARXPIREP_XMLP_PKG
Source: Potential Reconciling Items Report (XML)
Short Name: ARXPIREP_XML
DB package: AR_ARXPIREP_XMLP_PKG
Run
AR Potential Reconciling Items and other Oracle EBS reports with Blitz Reportâ„¢ on our demo environment
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.< |