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 |