select
-- ledger
x.ledger_name,
x.ledger_currency,
x.ledger_id,
-- accounts
x.account,
x.account_desc,
x.bal_seg_val,
x.bal_seg_desc,
x.acct_seg_val,
x.acct_seg_desc,
x.cost_ctr_seg_val,
x.cost_ctr_seg_desc,
x.additional_segment_name,
x.additional_segment_value,
x.additional_segment_desc,
-- start balances
nvl(
(select distinct
glb.begin_balance_dr
from
gl_balances glb
where
glb.actual_flag = 'A' and
glb.currency_code = x.ledger_currency and
glb.code_combination_id = x.line_ccid and
glb.ledger_id = x.ledger_id and
glb.period_name = :p_start_period_name_h
),0) acc_dr_begin,
nvl(
(select distinct
glb.begin_balance_cr
from
gl_balances glb
where
glb.actual_flag = 'A' and
glb.currency_code = x.ledger_currency and
glb.code_combination_id = x.line_ccid and
glb.ledger_id = x.ledger_id and
glb.period_name = :p_start_period_name_h
),0) acc_cr_begin,
-- start period movement prior to p_start_date
nvl(
(select
sum(gll.accounted_dr)
from
gl_je_lines gll
where
gll.effective_date >= :p_period_start_date_h and
gll.effective_date < :p_start_date_h and
gll.code_combination_id = x.line_ccid and
gll.ledger_id = x.ledger_id and
gll.status = 'P'
),0) acc_dr_period,
nvl(
(select
sum(gll.accounted_cr)
from
gl_je_lines gll
where
gll.effective_date >= :p_period_start_date_h and
gll.effective_date < :p_start_date_h and
gll.code_combination_id = x.line_ccid and
gll.ledger_id = x.ledger_id and
gll.status = 'P'
),0) acc_cr_period,
-- contra account
x.contra_acct_value,
case when :p_report_name = 'CONTRA'
then
case x.contra_acct_value
when 'MULTIPLE' then 'MULTIPLE'
when 'NO CONTRA ACCOUNT' then 'NO CONTRA ACCOUNT'
else
(select
ffvv.description
from
fnd_segment_attribute_values fsav,
fnd_id_flex_segments_vl fifsgv,
fnd_flex_values_vl ffvv
where
fsav.application_id = 101 and
fsav.id_flex_code = 'GL#' and
fsav.segment_attribute_type = 'GL_ACCOUNT' and
fsav.id_flex_num = :p_coa_id and
fsav.attribute_value = 'Y' and
fifsgv.application_id=fsav.application_id and
fifsgv.id_flex_code=fsav.id_flex_code and
fifsgv.id_flex_num=fsav.id_flex_num and
fifsgv.application_column_name = fsav.application_column_name and
fifsgv.flex_value_set_id=ffvv.flex_value_set_id and
ffvv.flex_value = x.contra_acct_value
)
end
else null
end contra_acct_value_desc,
case when :p_report_name = 'CONTRA'
then
gl_xml_journal_rpt_pkg.net_begin_balance
(p_acct_seg_where => '&p_aff_from_where2 &p_aff_to_where2',
p_status => x.batch_status_code,
p_start_date => :p_start_date_h,
p_currency => :p_currency,
p_led_id => x.ledger_id,
p_bal_seg_name => :p_gl_bal_segment,
p_bal_seg_val => x.bal_seg_val,
p_acct_seg_name => :p_gl_acc_segment,
p_acct_seg_val => x.acct_seg_val,
p_sec_seg_name => :p_gl_sec_segment,
p_sec_seg_val => x.additional_segment_value)
else to_number(null)
end cf_contra_net_begin_balance,
case when :p_report_name = 'CONTRA'
then
gl_xml_journal_rpt_pkg.net_line_balance
(p_acct_seg_where => '&p_aff_from_where2 &p_aff_to_where2',
p_status => x.batch_status_code,
p_start_date => :p_start_date_h,
p_currency => :p_currency,
p_led_id => x.ledger_id,
p_bal_seg_name => :p_gl_bal_segment,
p_bal_seg_val => x.bal_seg_val,
p_acct_seg_name => :p_gl_acc_segment,
p_acct_seg_val => x.acct_seg_val,
p_sec_seg_name => :p_gl_sec_segment,
p_sec_seg_val => x.additional_segment_value)
else to_number(null)
end cf_contra_net_line_balance,
-- journal batch
x.batch_id,
x.batch_name,
x.batch_desc,
x.batch_status_code,
x.batch_status,
x.batch_posted_date,
x.batch_period_name,
x.batch_effective_date,
x.actual_flag,
x.batch_type,
x.average_journal_flag,
x.jrnl_type,
x.approval_status_code,
x.approval_status,
x.bud_control_status,
x.budget_ctl_status,
x.batch_tot_dr,
x.batch_tot_cr,
x.batch_tot_acc_dr,
x.batch_tot_acc_cr,
x.batch_date_created,
-- journnal header
x.hdr_id,
x.hdr_name,
x.hdr_desc,
x.tax_status_code,
x.tax_status,
x.hdr_tot_dr,
x.hdr_tot_cr,
x.hdr_tot_acc_dr,
x.hdr_tot_acc_cr,
x.hdr_curr_code,
x.conversion_date,
x.conversion_rate,
x.conversion_type,
x.budget_version_id,
(select gbv.budget_name from gl_budget_versions gbv where gbv.budget_version_id = x.budget_version_id) budget_name,
x.encumbrance_type_id,
(select get.encumbrance_type from gl_encumbrance_types get where get.encumbrance_type_id = x.encumbrance_type_id) encumbrance_type,
x.hdr_extreference,
x.hdr_last_update_date,
x.hdr_eff_date,
x.doc_seq_id,
x.doc_seq_name,
x.doc_seq_value,
x.posting_seq_value,
x.close_seq_value,
nvl2(x.sl_event_id,x.xlq_reporting_seq,x.close_seq_value) cf_reporting_seq,
x.hdr_last_updated_by,
x.hdr_source,
x.hdr_category2,
x.hdr_category,
nvl2(x.sl_event_id,x.hdr_category,x.hdr_category2) cf_category,
-- journal line
x.je_line_num,
x.tax_code_id,
x.tax_type_code,
(select gtcv.tax_code from gl_tax_codes_v gtcv where to_char(gtcv.tax_code_id)=x.tax_code_id and gtcv.tax_type_code = x.tax_type_code) tax_code,
x.tax_type,
x.line_desc,
x.entered_dr,
x.entered_cr,
x.accounted_dr,
x.accounted_cr,
x.stat_amount,
x.effective_date,
x.reference1,
x.reference2,
x.reference3,
x.reference4,
x.reference5,
x.reference6,
x.reference7,
x.reference8,
x.reference9,
x.reference10,
x.line_proj_code2,
case
when x.sl_event_id is not null and x.hdr_source = 'Receivables'
then
coalesce
(
(select distinct
ractla.attribute1
from
ra_cust_trx_line_gl_dist_all ractl,
ra_customer_trx_all ract,
ra_customer_trx_lines_all ractla,
ra_batch_sources_all rabs
where
rabs.batch_source_id = ract.batch_source_id and
ract.customer_trx_id = ractla.customer_trx_id and
ractla.customer_trx_id = ractl.customer_trx_id and
ractla.customer_trx_line_id = ractl.customer_trx_line_id and
ractl.event_id = x.sl_event_id and
ractl.code_combination_id = x.line_ccid and
rownum=1
),
(select distinct
ractla.attribute1
from
ar_receivable_applications_all araa,
ra_customer_trx_all ract,
ra_customer_trx_lines_all ractla,
ra_batch_sources_all rabs
where
rabs.batch_source_id = ract.batch_source_id and
ract.customer_trx_id = ractla.customer_trx_id and
ractla.customer_trx_id = araa.customer_trx_id and
araa.event_id = x.sl_event_id and
rownum=1
),
x.line_proj_code2
)
when x.sl_event_id is not null and x.hdr_source = 'Payables'
then
case
when x.hdr_category = 'Payments'
then
nvl(
(select distinct
apila.attribute1
from
ap_invoices_all apia,
ap_invoice_lines_all apila,
ap_invoice_payments_all aip
where
aip.accounting_event_id = x.sl_event_id and
aip.payment_num = x.event_number and
apia.invoice_id = aip.invoice_id and
apila.invoice_id = aip.invoice_id and
rownum=1
),x.line_proj_code2)
else
nvl(
(select distinct
apila.attribute1
from
ap_invoices_all apia,
ap_invoice_lines_all apila,
ap_invoice_distributions_all apida,
xla_distribution_links xdl
where
xdl.application_id = 200 and
xdl.ae_header_id = x.ae_header_id and
xdl.ae_line_num = x.sl_line_num and
xdl.source_distribution_type = 'AP_INV_DIST' and
xdl.source_distribution_id_num_1 = apida.invoice_distribution_id and
apia.invoice_id = apida.invoice_id and
apila.invoice_id = apida.invoice_id and
apila.line_number = apida.invoice_line_number and
rownum=1
),x.line_proj_code2)
end
else
x.line_proj_code2
end line_proj_code,
x.line_fund_code,
(select ctsc.transaction_sub_type_name from ce_trxns_subtype_codes ctsc where ctsc.trxn_subtype_code_id = x.line_fund_code) cf_fund_code,
x.line_reference,
x.line_ccid,
x.jgzz_recon_ref,
x.sub_doc_seq_name,
x.sub_doc_seq_id,
x.sub_doc_seq_val,
-- subledger
x.transaction_no,
x.sl_doc_seq_no,
x.xlq_reporting_seq,
x.party_type_code,
x.party_id,
x.party_site_id,
case when x.sl_event_id is not null
then
case
when x.hdr_source = 'Assets' and x.party_type_code = 'C'
then to_char(x.source_id_int_1)
when x.hdr_source = 'Receivables' and x.party_type_code = 'C'
then
(select
a.account_number||'-'||c.location
from
hz_cust_accounts a,
hz_cust_acct_sites_all b,
hz_cust_site_uses_all c
where
a.cust_account_id = b.cust_account_id and
b.cust_acct_site_id = c.cust_acct_site_id and
c.site_use_id = x.party_site_id
)
when x.hdr_source = 'Payables' and x.party_type_code = 'S'
then
(select pv.segment1 from po_vendors pv where pv.vendor_id = x.party_id)
when x.hdr_source = 'Cost Management' and nvl2(x.sl_event_id,x.hdr_category,x.hdr_category2) = 'Receiving' and x.entity_code = 'WO_ACCOUNTING_EVENTS'
then
(select
substrb(pv.segment1,1,100)
from
cst_write_offs cwos,
po_distributions_all poda,
po_vendors pv
where
pv.vendor_id=cwos.vendor_id and
poda.po_distribution_id = cwos.po_distribution_id and
cwos.write_off_id = x.transaction_no
)
when x.hdr_source = 'Cost Management' and nvl2(x.sl_event_id,x.hdr_category,x.hdr_category2) = 'Receiving'
then
(select
substrb(pv.segment1,1,100)
from
rcv_transactions rt,
rcv_shipment_headers rsh,
po_vendors pv
where
pv.vendor_id=rt.vendor_id and
rsh.shipment_header_id = rt.shipment_header_id and
rt.transaction_id = x.transaction_no
)
else
null
end
else
null
end party_no,
--
case when x.sl_event_id is not null
then
case
when x.hdr_source = 'Assets'
then
(select substrb(description,1,100) from fa_additions ad where ad.asset_id = x.source_id_int_1)
when x.hdr_source = 'Receivables'
then
(select
substrb(nvl(party.party_name,''),1,100)
from
hz_parties party,
hz_cust_accounts acnt,
hz_cust_acct_sites_all site,
hz_cust_site_uses_all uses,
hz_party_sites ps,
hz_locations loc
where
party.party_id = acnt.party_id and
acnt.cust_account_id = site.cust_account_id and
site.cust_acct_site_id = uses.cust_acct_site_id and
ps.party_site_id=site.party_site_id and
loc.location_id=ps.location_id and
uses.site_use_id = x.party_site_id
)
when x.hdr_source = 'Payables' and x.party_type_code = 'S'
then
nvl
((select
decode(ai.source,'INDIA TDS',pv.vendor_name,ai.attribute1)
from
ap_invoices_all ai,
po_vendors pv
where
ai.invoice_id = x.source_id_int_1 and
ai.vendor_id = pv.vendor_id
),
(select substrb(pv.vendor_name,1,100) from po_vendors pv where pv.vendor_id = x.party_id)
)
when x.hdr_source = 'Cost Management' and nvl2(x.sl_event_id,x.hdr_category,x.hdr_category2) = 'Receiving' and x.entity_code = 'WO_ACCOUNTING_EVENTS'
then
(select
pv.vendor_name
from
cst_write_offs cwos,
po_distributions_all poda,
po_vendors pv
where
pv.vendor_id=cwos.vendor_id and
poda.po_distribution_id = cwos.po_distribution_id and
cwos.write_off_id = x.transaction_no
)
when x.hdr_source = 'Cost Management' and nvl2(x.sl_event_id,x.hdr_category,x.hdr_category2) = 'Receiving'
then
(select
pv.vendor_name
from
rcv_transactions rt,
rcv_shipment_headers rsh,
po_vendors pv
where
pv.vendor_id=rt.vendor_id and
rsh.shipment_header_id = rt.shipment_header_id and
rt.transaction_id = x.transaction_no
)
else
null
end
else
null
end party_name,
--
case
when nvl2(x.sl_event_id,x.hdr_category,x.hdr_category2) in ('Payments','Receipts')
then x.hdr_source
when nvl2(x.sl_event_id,x.hdr_category,x.hdr_category2) = 'Receiving'
then
case
when x.sl_event_id is not null and x.hdr_source = 'Cost Management' and nvl2(x.sl_event_id,x.hdr_category,x.hdr_category2) = 'Receiving' and x.entity_code = 'WO_ACCOUNTING_EVENTS'
then
(select
pha.attribute6
from
cst_write_offs cwos,
po_distributions_all poda,
po_headers_all pha
where
pha.po_header_id=poda.po_header_id and
poda.po_distribution_id = cwos.po_distribution_id and
cwos.write_off_id = x.transaction_no
)
when x.sl_event_id is not null and x.hdr_source = 'Cost Management' and nvl2(x.sl_event_id,x.hdr_category,x.hdr_category2) = 'Receiving'
then
(select
pha.attribute6
from
rcv_transactions rt,
rcv_shipment_headers rsh,
po_headers_all pha
where
pha.po_header_id=rt.po_header_id and
rsh.shipment_header_id = rt.shipment_header_id and
rt.transaction_id = x.transaction_no
)
else
null
end
when x.sl_event_id is not null and x.hdr_source = 'Receivables'
then
coalesce
(
(select distinct
rabs.name
from
ra_cust_trx_line_gl_dist_all ractl,
ra_customer_trx_all ract,
ra_customer_trx_lines_all ractla,
ra_batch_sources_all rabs
where
rabs.batch_source_id = ract.batch_source_id and
ract.customer_trx_id = ractla.customer_trx_id and
ractla.customer_trx_id = ractl.customer_trx_id and
ractla.customer_trx_line_id = ractl.customer_trx_line_id and
ractl.event_id = x.sl_event_id and
ractl.code_combination_id = x.line_ccid and
rownum=1
),
(select distinct
rabs.name
from
ar_receivable_applications_all araa,
ra_customer_trx_all ract,
ra_customer_trx_lines_all ractla,
ra_batch_sources_all rabs
where
rabs.batch_source_id = ract.batch_source_id and
ract.customer_trx_id = ractla.customer_trx_id and
ractla.customer_trx_id = araa.customer_trx_id and
araa.event_id = x.sl_event_id and
rownum=1
),
x.hdr_source
)
when x.sl_event_id is not null and x.hdr_source = 'Payables'
then
case
when x.hdr_category = 'Payments'
then
nvl(
(select distinct
apia.source
from
ap_invoices_all apia,
ap_invoice_lines_all apila,
ap_invoice_payments_all aip
where
aip.accounting_event_id = x.sl_event_id and
aip.payment_num = x.event_number and
apia.invoice_id = aip.invoice_id and
apila.invoice_id = aip.invoice_id and
rownum=1
),x.hdr_source)
else
nvl(
(select distinct
apia.source
from
ap_invoices_all apia,
ap_invoice_lines_all apila,
ap_invoice_distributions_all apida,
xla_distribution_links xdl
where
xdl.application_id = 200 and
xdl.ae_header_id = x.ae_header_id and
xdl.ae_line_num = x.sl_line_num and
xdl.source_distribution_type = 'AP_INV_DIST' and
xdl.source_distribution_id_num_1 = apida.invoice_distribution_id and
apia.invoice_id = apida.invoice_id and
apila.invoice_id = apida.invoice_id and
apila.line_number = apida.invoice_line_number and
rownum=1
),x.hdr_source)
end
else
x.hdr_source
end sl_source,
--
case
when x.hdr_source='Assets' and x.source_id_int_3 is not null
then to_char(x.source_id_int_3)
when x.hdr_source='Payables' and x.transaction_no is not null
then
case when x.hdr_category='Payments'
then
(select distinct
ab.batch_name
from
ap_batches_all ab,
ap_invoices_all ai,
ap_invoice_payments_all aip
where
ai.batch_id = ab.batch_id and
ai.invoice_id = aip.invoice_id and
aip.check_id = x.source_id_int_1 and
rownum=1
)
else
(select
ab.batch_name
from
ap_batches_all ab,
ap_invoices_all ai
where
ai.batch_id = ab.batch_id and
ai.invoice_id = x.source_id_int_1 and
rownum=1
)
end
when x.hdr_source='Receivables' and x.transaction_no is not null
then
case when x.hdr_category='Receipts'
then
(select distinct
nvl(bat.name ,'')
from
ar_cash_receipts_all cr,
ar_cash_receipt_history_all crh,
ar_batches_all bat
where
bat.batch_id = crh.batch_id and
crh.cash_receipt_id = x.source_id_int_1 and
rownum=1
)
else
(select
nvl(rab.name ,'')
from
ra_batches_all rab,
ra_customer_trx_all ct
where
rab.batch_id = ct.batch_id and
ct.customer_trx_id = x.source_id_int_1 and
rownum=1
)
end
else
null
end sl_batch_no,
--
nvl
(case when x.sl_event_id is not null
then
case
when x.hdr_source = 'Cost Management' and nvl2(x.sl_event_id,x.hdr_category,x.hdr_category2) = 'Receiving' and x.entity_code = 'WO_ACCOUNTING_EVENTS'
then
(select
to_char(cwos.write_off_id)
from
cst_write_offs cwos,
po_distributions_all poda,
po_vendors pv
where
pv.vendor_id=cwos.vendor_id and
poda.po_distribution_id = cwos.po_distribution_id and
cwos.write_off_id = x.transaction_no
)
when x.hdr_source = 'Cost Management' and nvl2(x.sl_event_id,x.hdr_category,x.hdr_category2) = 'Receiving'
then
(select
rsh.receipt_num
from
rcv_transactions rt,
rcv_shipment_headers rsh,
po_vendors pv
where
pv.vendor_id=rt.vendor_id and
rsh.shipment_header_id = rt.shipment_header_id and
rt.transaction_id = x.transaction_no
)
else
null
end
else
null
end,
x.transaction_no
) cf_receipt_num,
--
case when x.sl_event_id is not null
then
case
when x.hdr_source = 'Cost Management' and nvl2(x.sl_event_id,x.hdr_category,x.hdr_category2) = 'Receiving' and x.entity_code = 'WO_ACCOUNTING_EVENTS'
then
(select
to_char(poda.po_header_id)
from
cst_write_offs cwos,
po_distributions_all poda,
po_vendors pv
where
pv.vendor_id=cwos.vendor_id and
poda.po_distribution_id = cwos.po_distribution_id and
cwos.write_off_id = x.transaction_no
)
when x.hdr_source = 'Cost Management' and nvl2(x.sl_event_id,x.hdr_category,x.hdr_category2) = 'Receiving'
then
(select
to_char(rt.po_header_id)
from
rcv_transactions rt,
rcv_shipment_headers rsh,
po_vendors pv
where
pv.vendor_id=rt.vendor_id and
rsh.shipment_header_id = rt.shipment_header_id and
rt.transaction_id = x.transaction_no
)
else
null
end
else
null
end cmgt_po_header_id,
--
case
when (x.hdr_source ='Receivables') and nvl2(x.sl_event_id,x.hdr_category,x.hdr_category2) not in ('Misc Receipts','Receipts','Adjustment') and x.transaction_no is not null
then
(select distinct
rcta.purchase_order
from
ra_customer_trx_all rcta,
org_organization_definitions ood
where
ood.set_of_books_id = x.ledger_id and
rcta.org_id= ood.organization_id and
rcta.trx_number=x.transaction_no and
rownum=1
)
end ar_po_num,
--
x.ae_header_id,
x.entity_code,
x.source_id_int_1,
x.source_id_int_3,
x.sl_event_id ,
x.event_number,
x.sl_line_num,
x.xla_description,
x.xla_gl_sl_link_id,
--
'*' last_col
from
(
select &p_hint_clause
lgr.name ledger_name,
lgr.currency_code ledger_currency,
lgr.ledger_id ledger_id,
fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_seg', 'SQLGL', 'GL#', cc.chart_of_accounts_id, NULL, cc.code_combination_id, 'ALL', 'Y', 'VALUE') account,
fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_seg', 'SQLGL', 'GL#', cc.chart_of_accounts_id, NULL, cc.code_combination_id, 'ALL', 'Y', 'DESCRIPTION') account_desc,
fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_seg', 'SQLGL', 'GL#', cc.chart_of_accounts_id, NULL, cc.code_combination_id, 'GL_BALANCING', 'Y', 'VALUE') bal_seg_val,
fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_seg', 'SQLGL', 'GL#', cc.chart_of_accounts_id, NULL, cc.code_combination_id, 'GL_BALANCING', 'Y', 'DESCRIPTION') bal_seg_desc,
fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_seg', 'SQLGL', 'GL#', cc.chart_of_accounts_id, NULL, cc.code_combination_id, 'GL_ACCOUNT', 'Y', 'VALUE') acct_seg_val,
fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_seg', 'SQLGL', 'GL#', cc.chart_of_accounts_id, NULL, cc.code_combination_id, 'GL_ACCOUNT', 'Y', 'DESCRIPTION') acct_seg_desc,
fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_seg', 'SQLGL', 'GL#', cc.chart_of_accounts_id, NULL, cc.code_combination_id, 'FA_COST_CTR', 'Y', 'VALUE') cost_ctr_seg_val,
fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_seg', 'SQLGL', 'GL#', cc.chart_of_accounts_id, NULL, cc.code_combination_id, 'FA_COST_CTR', 'Y', 'DESCRIPTION') cost_ctr_seg_desc,
:p_sec_seg_name additional_segment_name,
&p_select_secondary_segment &p_null_secondary_segment additional_segment_value,
&p_select_secondary_seg_desc &p_null_secondary_segment additional_segment_desc,
case when :p_report_name = 'CONTRA' and (nvl(nvl(l.accounted_dr,gll.accounted_dr),0) != 0 or nvl(nvl(l.accounted_cr,gll.accounted_cr),0) !=0)
then gl_xml_journal_rpt_pkg.Get_Contra_Account(:p_gl_acc_segment,glh.je_header_id,gll.subledger_doc_sequence_id,gll.subledger_doc_sequence_value,nvl(l.accounted_dr,gll.accounted_dr),nvl(l.accounted_cr,gll.accounted_cr))
else null
end contra_acct_value,
--
glb.je_batch_id batch_id,
glb.name batch_name,
glb.description batch_desc,
glb.status batch_status_code,
xxen_util.meaning(glb.status,'MJE_BATCH_STATUS',101) batch_status,
glb.posted_date batch_posted_date,
glb.default_period_name batch_period_name,
glb.default_effective_date batch_effective_date,
glb.actual_flag actual_flag,
xxen_util.description(glb.actual_flag,'BATCH_TYPE',101) batch_type,
glb.average_journal_flag average_journal_flag,
xxen_util.meaning(glb.average_journal_flag,'AB_JOURNAL_TYPE',101) jrnl_type,
glb.approval_status_code approval_status_code,
xxen_util.meaning(glb.approval_status_code,'JE_BATCH_APPROVAL_STATUS',101) approval_status,
glb.budgetary_control_status bud_control_status,
xxen_util.meaning(glb.budgetary_control_status,'JE_BATCH_BC_STATUS',101) budget_ctl_status,
glb.running_total_dr batch_tot_dr,
glb.running_total_cr batch_tot_cr,
glb.running_total_accounted_dr batch_tot_acc_dr,
glb.running_total_accounted_cr batch_tot_acc_cr,
glb.date_created batch_date_created,
glh.je_header_id hdr_id,
glh.name hdr_name,
glh.description hdr_desc,
glh.tax_status_code tax_status_code,
xxen_util.meaning(glh.tax_status_code,'TAX_STATUS',101) tax_status,
glh.running_total_dr hdr_tot_dr,
glh.running_total_cr hdr_tot_cr,
glh.running_total_accounted_dr hdr_tot_acc_dr,
glh.running_total_accounted_cr hdr_tot_acc_cr,
glh.currency_code hdr_curr_code,
nvl(l.currency_conversion_date,glh.currency_conversion_date) conversion_date ,
to_char( nvl(l.currency_conversion_rate,decode((nvl(gll.entered_dr,0)+nvl(gll.entered_cr,0)),0,0,(nvl(gll.accounted_dr,0)+nvl(gll.accounted_cr,0))/(nvl(gll.entered_dr,0)+nvl(gll.entered_cr,0)) )) ,'999G999D9999') conversion_rate,
dct.user_conversion_type conversion_type,
glh.budget_version_id budget_version_id,
glh.encumbrance_type_id encumbrance_type_id,
glh.external_reference hdr_extreference,
glh.last_update_date hdr_last_update_date,
glh.default_effective_date hdr_eff_date,
glh.doc_sequence_id doc_seq_id,
docseq.name doc_seq_name,
glh.doc_sequence_value doc_seq_value,
glh.posting_acct_seq_value posting_seq_value,
nvl(h.close_acct_seq_value,glh.close_acct_seq_value) close_seq_value,
fu.user_name hdr_last_updated_by,
gjs.user_je_source_name hdr_source,
gjc.user_je_category_name hdr_category2,
decode(gjs.user_je_source_name ,'Assets',h.je_category_name,'Payables',h.je_category_name,'Receivables',h.je_category_name,'Cost Management',h.je_category_name,gjc.user_je_category_name) hdr_category,
gll.je_line_num je_line_num,
gll.tax_code_id tax_code_id,
gll.tax_type_code tax_type_code,
xxen_util.meaning(gll.tax_type_code,'TAX_TYPE',101) tax_type,
decode(e.event_id,null,gll.description,l.description) line_desc,
nvl(l.entered_dr,gll.entered_dr) entered_dr,
nvl(l.entered_cr,gll.entered_cr) entered_cr,
nvl(l.accounted_dr,gll.accounted_dr) accounted_dr,
nvl(l.accounted_cr,gll.accounted_cr) accounted_cr,
gll.stat_amount stat_amount,
gll.effective_date effective_date,
gll.reference_1 reference1,
gll.reference_2 reference2,
gll.reference_3 reference3,
gll.reference_4 reference4,
gll.reference_5 reference5,
gll.reference_6 reference6,
gll.reference_7 reference7,
gll.reference_8 reference8,
gll.reference_9 reference9,
gll.reference_10 reference10,
gll.attribute11 line_proj_code2,
gll.attribute12 line_fund_code,
gll.attribute13 line_reference,
gll.code_combination_id line_ccid,
decode(l.gl_sl_link_id,'',rec.jgzz_recon_ref,l.jgzz_recon_ref) jgzz_recon_ref,
subdocseq.name sub_doc_seq_name,
gll.subledger_doc_sequence_id sub_doc_seq_id,
gll.subledger_doc_sequence_value sub_doc_seq_val,
nvl(te.transaction_number,to_char(' ')) transaction_no,
h.doc_sequence_value sl_doc_seq_no,
h.close_acct_seq_value xlq_reporting_seq,
l.ae_header_id,
te.entity_code,
te.source_id_int_1 source_id_int_1,
te.source_id_int_3 source_id_int_3,
e.event_id sl_event_id ,
e.event_number,
l.ae_line_num sl_line_num,
l.description xla_description,
l.gl_sl_link_id xla_gl_sl_link_id,
l.party_type_code,
l.party_site_id party_site_id ,
l.party_id party_id
from gl_je_lines_recon rec,
gl_system_usages use1,
xla.xla_ae_headers h ,
xla.xla_ae_lines l ,
xla.xla_events e ,
xla.xla_transaction_entities te ,
xla_gl_ledgers_v gl ,
xle_entity_profiles le ,
fnd_user ue ,
fnd_application_vl app ,
xla.xla_event_types_tl et ,
xla.xla_event_classes_tl ec ,
xla_lookups lk5 ,
xla_lookups lk7 ,
gl_import_references ir,
gl_system_usages use,
&p_perf_from
&p_journal_from_clause
&p_ledger_from
where 1=1
and glh.ledger_id = lgr.ledger_id
&p_ledger_where
and glh.je_source = gjs.je_source_name
and glh.je_category = gjc.je_category_name
and glh.last_updated_by = fu.user_id
and glb.je_batch_id = glh.je_batch_id
and glh.doc_sequence_id = docseq.doc_sequence_id(+)
and gll.subledger_doc_sequence_id = subdocseq.doc_sequence_id(+)
and dct.conversion_type (+) = glh.currency_conversion_type
and gll.status = 'P'
and rec.je_header_id(+) = gll.je_header_id
and rec.je_line_num(+) = gll.je_line_num
and rec.ledger_id(+) = :p_ledger_id
and gl.ledger_id (+)= h.ledger_id
and le.legal_entity_id (+)= te.legal_entity_id
and ue.user_id (+)= e.created_by
and ec.application_id(+) = et.application_id
and ec.event_class_code (+)= et.event_class_code
and ec.language (+)= userenv('LANG')
and et.event_type_code (+)= h.event_type_code
and et.language (+)= userenv('LANG')
and et.application_id (+)= h.application_id
and app.application_id (+)= h.application_id
and te.entity_id (+)= h.entity_id
and e.event_id (+)= h.event_id
and h.ae_header_id (+)= l.ae_header_id
and lk5.lookup_code = nvl(h.funds_status_code, 'REQUIRED')
and lk5.lookup_type = 'XLA_FUNDS_STATUS'
and lk7.lookup_code(+) = l.accounting_class_code
and lk7.lookup_type(+) = 'XLA_ACCOUNTING_CLASS'
and ir.gl_sl_link_id = l.gl_sl_link_id(+)
and ir.gl_sl_link_table = l.gl_sl_link_table(+)
and ir.je_header_id(+) = gll.je_header_id
and ir.je_line_num (+)= gll.je_line_num
and gjs.je_source_name in('Assets','Inventory','Payables','Purchasing','Receivables','Revenue','Cost Management')
&p_posting_status
&p_header_posting_status
&p_das_where
&p_currency_where
&p_actual_type_where
&p_batch_where
&p_source_where
&p_category_where
&p_bal_where
&p_doc_id_where
&p_doc_val_where
&p_sub_doc_id_where
&p_sub_doc_val_where
&p_not_zero_line_where
&p_period_where
&p_contra_account_where
&p_journal_date_where
&p_journal_upd_date_where
&p_journal_last_upd_where
&p_aff_from_where
&p_aff_to_where
&p_amount_where
&p_journal_where_clause
union all
select &p_hint_clause
lgr.name ledger_name,
lgr.currency_code ledger_currency,
lgr.ledger_id ledger_id,
fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_seg', 'SQLGL', 'GL#', cc.chart_of_accounts_id, NULL, cc.code_combination_id, 'ALL', 'Y', 'VALUE') account,
fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_seg', 'SQLGL', 'GL#', cc.chart_of_accounts_id, NULL, cc.code_combination_id, 'ALL', 'Y', 'DESCRIPTION') account_desc,
fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_seg', 'SQLGL', 'GL#', cc.chart_of_accounts_id, NULL, cc.code_combination_id, 'GL_BALANCING', 'Y', 'VALUE') bal_seg_val,
fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_seg', 'SQLGL', 'GL#', cc.chart_of_accounts_id, NULL, cc.code_combination_id, 'GL_BALANCING', 'Y', 'DESCRIPTION') bal_seg_desc,
fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_seg', 'SQLGL', 'GL#', cc.chart_of_accounts_id, NULL, cc.code_combination_id, 'GL_ACCOUNT', 'Y', 'VALUE') acct_seg_val,
fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_seg', 'SQLGL', 'GL#', cc.chart_of_accounts_id, NULL, cc.code_combination_id, 'GL_ACCOUNT', 'Y', 'DESCRIPTION') acct_seg_desc,
fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_seg', 'SQLGL', 'GL#', cc.chart_of_accounts_id, NULL, cc.code_combination_id, 'FA_COST_CTR', 'Y', 'VALUE') cost_ctr_seg_val,
fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_seg', 'SQLGL', 'GL#', cc.chart_of_accounts_id, NULL, cc.code_combination_id, 'FA_COST_CTR', 'Y', 'DESCRIPTION') cost_ctr_seg_desc,
:p_sec_seg_name additional_segment_name,
&p_select_secondary_segment &p_null_secondary_segment additional_segment_value,
&p_select_secondary_seg_desc &p_null_secondary_segment additional_segment_desc,
case when :p_report_name = 'CONTRA'
then gl_xml_journal_rpt_pkg.Get_Contra_Account(:p_gl_acc_segment,glh.je_header_id,gll.subledger_doc_sequence_id,gll.subledger_doc_sequence_value,gll.accounted_dr,gll.accounted_cr)
else null
end contra_acct_value,
--
glb.je_batch_id batch_id,
glb.name batch_name,
glb.description batch_desc,
glb.status batch_status_code,
xxen_util.meaning(glb.status,'MJE_BATCH_STATUS',101) batch_status,
glb.posted_date batch_posted_date,
glb.default_period_name batch_period_name,
glb.default_effective_date batch_effective_date,
glb.actual_flag actual_flag,
xxen_util.description(glb.actual_flag,'BATCH_TYPE',101) batch_type,
glb.average_journal_flag average_journal_flag,
xxen_util.meaning(glb.average_journal_flag,'AB_JOURNAL_TYPE',101) jrnl_type,
glb.approval_status_code approval_status_code,
xxen_util.meaning(glb.approval_status_code,'JE_BATCH_APPROVAL_STATUS',101) approval_status,
glb.budgetary_control_status bud_control_status,
xxen_util.meaning(glb.budgetary_control_status,'JE_BATCH_BC_STATUS',101) budget_ctl_status,
glb.running_total_dr batch_tot_dr,
glb.running_total_cr batch_tot_cr,
glb.running_total_accounted_dr batch_tot_acc_dr,
glb.running_total_accounted_cr batch_tot_acc_cr,
glb.date_created batch_date_created,
glh.je_header_id hdr_id,
glh.name hdr_name,
glh.description hdr_desc,
glh.tax_status_code tax_status_code,
xxen_util.meaning(glh.tax_status_code,'TAX_STATUS',101) tax_status,
glh.running_total_dr hdr_tot_dr,
glh.running_total_cr hdr_tot_cr,
glh.running_total_accounted_dr hdr_tot_acc_dr,
glh.running_total_accounted_cr hdr_tot_acc_cr,
glh.currency_code hdr_curr_code,
glh.currency_conversion_date conversion_date ,
to_char(decode((nvl(gll.entered_dr,0)+nvl(gll.entered_cr,0)),0,0,(nvl(gll.accounted_dr,0)+nvl(gll.accounted_cr,0))/(nvl(gll.entered_dr,0)+nvl(gll.entered_cr,0)) ) ,'999G999D9999') conversion_rate,
dct.user_conversion_type conversion_type,
glh.budget_version_id budget_version_id,
glh.encumbrance_type_id encumbrance_type_id,
glh.external_reference hdr_extreference,
glh.last_update_date hdr_last_update_date,
glh.default_effective_date hdr_eff_date,
glh.doc_sequence_id doc_seq_id,
docseq.name doc_seq_name,
glh.doc_sequence_value doc_seq_value,
glh.posting_acct_seq_value posting_seq_value,
glh.close_acct_seq_value close_seq_value,
fu.user_name hdr_last_updated_by,
gjs.user_je_source_name hdr_source,
gjc.user_je_category_name hdr_category2,
gjc.user_je_category_name hdr_category,
gll.je_line_num je_line_num,
gll.tax_code_id tax_code_id,
gll.tax_type_code tax_type_code,
xxen_util.meaning(gll.tax_type_code,'TAX_TYPE',101) tax_type,
gll.description line_desc,
gll.entered_dr entered_dr,
gll.entered_cr entered_cr,
gll.accounted_dr accounted_dr,
gll.accounted_cr accounted_cr,
gll.stat_amount stat_amount,
gll.effective_date effective_date,
gll.reference_1 reference1,
gll.reference_2 reference2,
gll.reference_3 reference3,
gll.reference_4 reference4,
gll.reference_5 reference5,
gll.reference_6 reference6,
gll.reference_7 reference7,
gll.reference_8 reference8,
gll.reference_9 reference9,
gll.reference_10 reference10,
gll.attribute11 line_proj_code2,
gll.attribute12 line_fund_code,
gll.attribute13 line_reference,
gll.code_combination_id line_ccid,
rec.jgzz_recon_ref,
subdocseq.name sub_doc_seq_name,
gll.subledger_doc_sequence_id sub_doc_seq_id,
gll.subledger_doc_sequence_value sub_doc_seq_val,
null transaction_no,
null sl_doc_seq_no,
null xlq_reporting_seq,
null ae_header_id,
null entity_code,
null source_id_int_1,
null source_id_int_3,
null sl_event_id ,
null event_number,
null sl_line_num,
null xla_description,
null xla_gl_sl_link_id,
null party_type_code,
null party_site_id ,
null party_id
from gl_je_lines_recon rec,
&p_perf_from
&p_journal_from_clause
&p_ledger_from
where
1=1
and glh.ledger_id = lgr.ledger_id
&p_ledger_where
and rec.je_header_id(+) = gll.je_header_id
and rec.je_line_num(+) = gll.je_line_num
and rec.ledger_id(+) = :p_ledger_id
and glh.je_source = gjs.je_source_name
and glh.je_category = gjc.je_category_name
and glh.last_updated_by = fu.user_id
and glb.je_batch_id = glh.je_batch_id
and glh.doc_sequence_id = docseq.doc_sequence_id(+)
and gll.subledger_doc_sequence_id = subdocseq.doc_sequence_id(+)
and dct.conversion_type (+) = glh.currency_conversion_type
and gll.status = 'P'
and gjs.je_source_name not in('Assets','Inventory','Payables','Purchasing','Receivables','Revenue','Cost Management')
&p_posting_status
&p_header_posting_status
&p_das_where
&p_currency_where
&p_actual_type_where
&p_batch_where
&p_source_where
&p_category_where
&p_bal_where
&p_doc_id_where
&p_doc_val_where
&p_sub_doc_id_where
&p_sub_doc_val_where
&p_not_zero_line_where
&p_period_where
&p_contra_account_where
&p_journal_date_where
&p_journal_upd_date_where
&p_journal_last_upd_where
&p_aff_from_where
&p_aff_to_where
&p_amount_where
&p_journal_where_clause
) x
where
:p_report_name = :p_report_name
and :p_access_set_id = :p_access_set_id
and :p_ledger = :p_ledger
and :p_ledger_id = :p_ledger_id
and :p_ledger_type = :p_ledger_type
and :p_coa_id = :p_coa_id
and nvl(:p_status,'?') = nvl(:p_status,'?')
and nvl(:p_currency,'?') = nvl(:p_currency,'?')
and nvl(:p_select_basis,'?') = nvl(:p_select_basis,'?')
and nvl(:p_use_date_col,'?') = nvl(:p_use_date_col,'?')
and nvl(:p_start_date,sysdate) = nvl(:p_start_date,sysdate)
and nvl(:p_end_date,sysdate) = nvl(:p_end_date,sysdate)
and nvl(:p_amount_from,0) = nvl(:p_amount_from,0)
and nvl(:p_amount_to,0) = nvl(:p_amount_to,0)
and nvl(:p_amt_flag,'?') = nvl(:p_amt_flag,'?')
and nvl(:p_aff_from,'?') = nvl(:p_aff_from,'?')
and nvl(:p_aff_to,'?') = nvl(:p_aff_to,'?')
and nvl(:p_contra_acct,'?') = nvl(:p_contra_acct,'?')
order by
account,
effective_date,
doc_seq_value |
