select
x.period_name,
x."Firma" as Gesellschaft,
x."Firma desc" as Gesellschaft_Beschreibung,
x.source_name as Herkunft,
x.category_name as Belegart,
x.batch_name as Stapelname,
x.posted_date as Verbuchungsdatum,
x.line_description as Buchungstext,
x.document_number as Belegnummer_GL,
x.je_header_id,
x.tax_rate_code,
x.tax_rate,
x.tax_line,
x."Konto",
x."Konto desc" as Konto_Beschreibung,
x."Intercompany",
x."Intercompany desc" as IC_Partner,
x.transaction_currency,
x.accounted_dr,
x.accounted_cr,
x.accounted_amount,
x.ledger_currency,
x.gueltigkeitsdatum,
x.invoice_date,
x.invoice_number,
--x.gl_date,
x.vendor_or_customer,
x.vat,
x.stnr,
x.journal_created_by,
x.journal_creation_date,
x.je_line_num
from
(
select x.* from ( SELECT
gjh.period_name,
gl.name ledger,
(
SELECT
gjsv.user_je_source_name
FROM
gl_je_sources_vl gjsv
WHERE
gjh.je_source = gjsv.je_source_name
) source_name,
gjh.external_reference reference,
(
SELECT
gjcv.user_je_category_name
FROM
gl_je_categories_vl gjcv
WHERE
gjh.je_category = gjcv.je_category_name
) category_name,
gjb.name batch_name,
gjh.Default_effective_date Gueltigkeitsdatum,
xxen_util.meaning(
gjb.status, 'MJE_BATCH_STATUS', 101
) batch_status,
gjh.posted_date,
gjh.name journal_name,
gjh.description journal_description,
gjh.doc_sequence_value document_number,
xxen_util.meaning(
gjh.tax_status_code, 'TAX_STATUS', 101
) tax_status_code,
gjl.description line_description,
coalesce(
zrb.tax_rate_code,
LISTAGG(zl.tax_rate_code, ', ') WITHIN GROUP(
ORDER BY
zl.tax_rate_code
)
OVER(PARTITION BY nvl(
rctla.link_to_cust_trx_line_id, rctla.customer_trx_line_id
)), aila.tax_rate_code, aila.tax_classification_code
) tax_rate_code,
coalesce(
zrb.percentage_rate, MAX(zl.tax_rate)
OVER(PARTITION BY nvl(
rctla.link_to_cust_trx_line_id, rctla.customer_trx_line_id
)), aila.tax_rate,(
SELECT
zl.tax_rate
FROM
zx.zx_lines zl
WHERE
zl.application_id = 200
AND zl.entity_code = 'AP_INVOICES'
AND zl.event_class_code IN('STANDARD INVOICES', 'EXPENSE REPORTS', 'PREPAYMENT INVOICES')
AND aila.invoice_id = zl.trx_id
AND zl.trx_level_type = 'LINE'
AND aila.line_number = zl.trx_line_id
AND ROWNUM = 1
)
) tax_rate,
xxen_util.meaning(
gjl.tax_line_flag, 'YES_NO', 0
) tax_line,
xxen_util.meaning(
gjl.taxable_line_flag, 'YES_NO', 0
) taxable_line,
xxen_util.meaning(
gjl.amount_includes_tax_flag, 'YES_NO', 0
) amount_includes_tax,
xxen_util.meaning(
xal.accounting_class_code, 'XLA_ACCOUNTING_CLASS', 602, 'Y'
) accounting_class,
xxen_util.meaning(
gcc.account_type, 'ACCOUNT_TYPE', 0
) account_type,
gcc.segment1 "Firma",
xxen_util.segment_description(gcc.segment1, 'SEGMENT1', gcc.chart_of_accounts_id) "Firma desc",
gcc.segment2 "Konto",
xxen_util.segment_description(gcc.segment2, 'SEGMENT2', gcc.chart_of_accounts_id) "Konto desc",
gcc.segment3 "Kostenstelle",
xxen_util.segment_description(gcc.segment3, 'SEGMENT3', gcc.chart_of_accounts_id) "Kostenstelle desc",
gcc.segment4 "Einzelträger",
xxen_util.segment_description(gcc.segment4, 'SEGMENT4', gcc.chart_of_accounts_id) "Einzelträger desc",
gcc.segment5 "Intercompany",
xxen_util.segment_description(gcc.segment5, 'SEGMENT5', gcc.chart_of_accounts_id) "Intercompany desc",
gcc.segment6 "Sonstiges",
xxen_util.segment_description(gcc.segment6, 'SEGMENT6', gcc.chart_of_accounts_id) "Sonstiges desc",
gcc.segment7 "Umlage",
xxen_util.segment_description(gcc.segment7, 'SEGMENT7', gcc.chart_of_accounts_id) "Umlage desc",
gcc.segment8 "Z-Mon",
xxen_util.segment_description(gcc.segment8, 'SEGMENT8', gcc.chart_of_accounts_id) "Z-Mon desc",
gcc.segment9 "Reserve1",
xxen_util.segment_description(gcc.segment9, 'SEGMENT9', gcc.chart_of_accounts_id) "Reserve1 desc",
gcc.segment10 "Reserve2",
xxen_util.segment_description(gcc.segment10, 'SEGMENT10', gcc.chart_of_accounts_id) "Reserve2 desc", nvl2(xal.gl_sl_link_id,xdl.unrounded_entered_dr,gjl.entered_dr) entered_dr,
nvl2(xal.gl_sl_link_id,xdl.unrounded_entered_cr,gjl.entered_cr) entered_cr,
nvl(nvl2(xal.gl_sl_link_id,xdl.unrounded_entered_dr,gjl.entered_dr),0)-nvl(nvl2(xal.gl_sl_link_id,xdl.unrounded_entered_cr,gjl.entered_cr),0) entered_amount,
nvl2(xal.gl_sl_link_id,xal.currency_code,gjh.currency_code) transaction_currency,
nvl2(xal.gl_sl_link_id,xdl.unrounded_accounted_dr,gjl.accounted_dr) accounted_dr,
nvl2(xal.gl_sl_link_id,xdl.unrounded_accounted_cr,gjl.accounted_cr) accounted_cr,
nvl(nvl2(xal.gl_sl_link_id,xdl.unrounded_accounted_dr,gjl.accounted_dr),0)-nvl(nvl2(xal.gl_sl_link_id,xdl.unrounded_accounted_cr,gjl.accounted_cr),0) accounted_amount,
gl.currency_code ledger_currency,
nvl(gjh.doc_sequence_value,xah.doc_sequence_value) doc_sequence_value,
(select xett.name from xla_event_types_tl xett where xte.application_id=xett.application_id and xte.entity_code=xett.entity_code and xe.event_type_code=xett.event_type_code and xett.language=userenv('lang')) event_type,
xal.currency_conversion_date,
(select gdct.user_conversion_type from gl_daily_conversion_types gdct where xal.currency_conversion_type=gdct.conversion_type) currency_conversion_type,
xal.currency_conversion_rate,
xxen_util.description(gjh.actual_flag,'BATCH_TYPE',101) balance_type,
(select gbv.budget_name from gl_budget_versions gbv where gjh.budget_version_id=gbv.budget_version_id) budget_name,
gjh.currency_conversion_date conversion_date,
gjh.currency_conversion_type conversion_type,
gjh.currency_conversion_rate conversion_rate,
xah.description accounting_event_description,
xah.accounting_date,
xe.transaction_date,
xte.transaction_number,
--AP
coalesce(aia.invoice_num,rcta.trx_number) invoice_number,
nvl(aia.description,rcta.comments) description,
nvl(aia.invoice_date,rcta.trx_date) invoice_date,
aia.gl_date,
nvl(aia.invoice_currency_code,rcta.invoice_currency_code) invoice_currency_code,
aia.payment_currency_code,
aia.payment_method_code,
aia.invoice_amount,
(select pha.segment1 from po_headers_all pha where coalesce(aia.quick_po_header_id,rt.po_header_id,wt.po_header_id)=pha.po_header_id) purchase_order,
--AR
case when xte.entity_code='TRANSACTIONS' and rcta.interface_header_context in ('ORDER ENTRY','INTERCOMPANY') then rcta.interface_header_attribute1 end sales_order,
jrrev.resource_name salesperson,
(select name from ra_rules rr where rcta.invoicing_rule_id=rule_id) invoice_rule,
(select rr.name from ra_customer_trx_lines_all rctla, ra_rules rr where rcta.customer_trx_id=rctla.customer_trx_id and rctla.line_type='LINE' and rctla.accounting_rule_id=rr.rule_id and rownum=1) accounting_rule,
rt.quantity po_quantity,
coalesce(
(select aps.vendor_name from ap_suppliers aps where coalesce(aia.vendor_id,aca.vendor_id,rt.vendor_id)=aps.vendor_id),
(select hp.party_name from hz_cust_accounts hca, hz_parties hp where coalesce(rcta.bill_to_customer_id,acra.pay_from_customer,paa.customer_id)=hca.cust_account_id and hca.party_id=hp.party_id)
) vendor_or_customer,
--StNr
(select aps.VAT_Registration_num from ap_suppliers aps where coalesce(aia.vendor_id,aca.vendor_id,rt.vendor_id)=aps.vendor_id) VAT,
(select aps.NUM_1099 from ap_suppliers aps where coalesce(aia.vendor_id,aca.vendor_id,rt.vendor_id)=aps.vendor_id) StNr,
--Projects
coalesce(
(select ppa.segment1 from pa_projects_all ppa where aida.project_id=ppa.project_id),
case when xte.entity_code='TRANSACTIONS' and rcta.interface_header_context='PROJECTS INVOICES' then rcta.interface_header_attribute1 end,
ppa.segment1
) project,
pt.task_number task,
pea.expenditure_group,
xxen_util.meaning(pea.expenditure_class_code,'EXPENDITURE CLASS CODE',275) expenditure_class_code,
xxen_util.meaning(pea.expenditure_status_code,'EXPENDITURE STATUS',275) expenditure_status_code,
pet.expenditure_category,
peia.expenditure_type,
pet.description expenditure_type_description,
peia.expenditure_item_date,
peia.quantity expenditure_item_quantity,
xxen_util.meaning(pet.unit_of_measure,'UNIT',275) expenditure_unit_of_measure,
papf.full_name incurred_by_person,
nvl(papf.employee_number,papf.npw_number) incurred_by_employee_number,
--WIP
bd.department_code,
br.resource_code,
we.wip_entity_name wip_job,
wt.operation_seq_num,
wt.transaction_quantity,
wt.transaction_uom,
wt.primary_quantity,
xxen_util.user_name(gjh.created_by) journal_created_by,
gjh.creation_date journal_creation_date,
gjb.je_batch_id,
gjl.je_header_id,
gjl.je_line_num,
gjl.context dff_context,
xal.application_id,
xal.ae_header_id,
xal.ae_line_num,
xah.event_id,
xe.event_type_code,
xe.event_date,
xal.accounting_class_code,
xte.entity_code,
xte.source_id_int_1,
xdl.source_distribution_type,
xdl.accounting_line_code,
xdl.applied_to_distribution_type,
xdl.applied_to_dist_id_num_1,
xdl.applied_to_entity_id,
xdl.applied_to_source_id_num_1,
aphd.invoice_distribution_id,
aphd.invoice_payment_id
from
gl_ledgers gl,
gl_periods gp,
gl_je_batches gjb,
gl_je_headers gjh,
gl_je_lines gjl,
gl_import_references gir,
xla_ae_lines xal,
xla_ae_headers xah,
xla_events xe,
xla.xla_transaction_entities xte,
xla_distribution_links xdl,
gl_code_combinations gcc,
(select gdr.* from gl_daily_rates gdr where gdr.to_currency=:revaluation_currency and gdr.conversion_type=(select gdct.conversion_type from gl_daily_conversion_types gdct where gdct.user_conversion_type=:revaluation_conversion_type)) gdr,
zx_rates_b zrb,
ap_invoices_all aia,
ap_checks_all aca,
ap_invoice_distributions_all aida,
ap_payment_hist_dists aphd,
ap_invoice_lines_all aila,
ra_customer_trx_all rcta,
ra_cust_trx_line_gl_dist_all rctlgda,
ra_customer_trx_lines_all rctla,
zx_lines zl,
jtf_rs_salesreps jrs,
jtf_rs_resource_extns_vl jrrev,
ar_adjustments_all aaa,
ar_cash_receipts_all acra,
pa_projects_all ppa,
pa_tasks pt,
pa_draft_revenues_all pdra,
pa_agreements_all paa,
pa_expenditure_items_all peia,
pa_expenditures_all pea,
pa_expenditure_types pet,
(select papf.* from per_all_people_f papf where sysdate>=papf.effective_start_date and sysdate<papf.effective_end_date+1) papf,
rcv_transactions rt,
wip_transactions wt,
wip_entities we,
bom_departments bd,
bom_resources br
where
gl.name=:ledger and
gp.period_year*10000+gp.period_num>=(select gp0.period_year*10000+gp0.period_num effective_period_num from gl_periods gp0 where gl.period_set_name=gp0.period_set_name and gp0.period_name=:period_name_from) and
gp.period_year*10000+gp.period_num<=(select gp0.period_year*10000+gp0.period_num effective_period_num from gl_periods gp0 where gl.period_set_name=gp0.period_set_name and gp0.period_name=:period_name_to) and
gjh.status=xxen_util.lookup_code(:status,'BATCH_STATUS',101) and
gjh.actual_flag=xxen_util.lookup_code(:balance_type,'XLA_BALANCE_TYPE',602) and
1=1 and
gl.period_set_name=gp.period_set_name and
gp.period_name=gjh.period_name and
gp.period_name=gjl.period_name and
gl.ledger_id=gjh.ledger_id and
gjb.je_batch_id=gjh.je_batch_id and
gjh.je_header_id=gjl.je_header_id and
gjl.je_header_id=gir.je_header_id(+) and
gjl.je_line_num=gir.je_line_num(+) and
gir.gl_sl_link_id=xal.gl_sl_link_id(+) and
gir.gl_sl_link_table=xal.gl_sl_link_table(+) and
xal.ae_header_id=xah.ae_header_id(+) and
xal.application_id=xah.application_id(+) and
xah.gl_transfer_status_code(+)='Y' and
xah.accounting_entry_status_code(+)='F' and
xah.event_id=xe.event_id(+) and
xah.application_id=xe.application_id(+) and
xah.entity_id=xte.entity_id(+) and
xah.application_id=xte.application_id(+) and
xal.application_id=xdl.application_id(+) and
xal.ae_header_id=xdl.ae_header_id(+) and
xal.ae_line_num=xdl.ae_line_num(+) and
gjl.code_combination_id=gcc.code_combination_id and
coalesce(
xal.currency_conversion_date,
gjh.currency_conversion_date,
trunc(xe.transaction_date)
)=gdr.conversion_date(+) and
decode(nvl2(xal.gl_sl_link_id,xal.currency_code,gjh.currency_code),:revaluation_currency,null,nvl2(xal.gl_sl_link_id,xal.currency_code,gjh.currency_code))=gdr.from_currency(+) and
gjl.tax_code_id=zrb.tax_rate_id(+) and
case when xte.application_id=200 and xte.entity_code='AP_PAYMENTS' then xte.source_id_int_1 end=aca.check_id(+) and
case
when xdl.application_id=200 and xdl.source_distribution_type='AP_INV_DIST' then xdl.source_distribution_id_num_1
when xdl.application_id=200 and xdl.applied_to_distribution_type='AP_INV_DIST' then xdl.applied_to_dist_id_num_1
end=aida.invoice_distribution_id(+) and
case when xdl.application_id=200 and xdl.source_distribution_type='AP_PMT_DIST' then xdl.source_distribution_id_num_1 end=aphd.payment_hist_dist_id(+) and
aida.invoice_id=aia.invoice_id(+) and
aida.invoice_id=aila.invoice_id(+) and
aida.invoice_line_number=aila.line_number(+) and
case when xte.application_id=222 then case when xte.entity_code in ('TRANSACTIONS','BILLS_RECEIVABLE') then xte.source_id_int_1 when xte.entity_code='ADJUSTMENTS' then aaa.customer_trx_id end end=rcta.customer_trx_id(+) and
rcta.primary_salesrep_id=jrs.salesrep_id(+) and
rcta.org_id=jrs.org_id(+) and
jrs.resource_id=jrrev.resource_id(+) and
case when xte.application_id=222 and xte.entity_code='ADJUSTMENTS' then xte.source_id_int_1 end=aaa.adjustment_id(+) and
case when xte.application_id=222 and xte.entity_code='RECEIPTS' then xte.source_id_int_1 end=acra.cash_receipt_id(+) and
case when xdl.application_id=222 and xdl.source_distribution_type='RA_CUST_TRX_LINE_GL_DIST_ALL' then xdl.source_distribution_id_num_1 end=rctlgda.cust_trx_line_gl_dist_id(+) and
rctlgda.customer_trx_line_id=rctla.customer_trx_line_id(+) and
rctla.tax_line_id=zl.tax_line_id(+) and
case when xte.application_id=275 then decode(xte.entity_code,'REVENUE',xte.source_id_int_1,'EXPENDITURES',peia.project_id) end = ppa.
project_id(+)
AND CASE
WHEN xte.application_id = 275
AND xte.entity_code = 'REVENUE' THEN
xte.source_id_int_1
END = pdra.project_id (+)
AND CASE
WHEN xte.application_id = 275
AND xte.entity_code = 'REVENUE' THEN
xte.source_id_int_2
END = pdra.draft_revenue_num (+)
AND pdra.agreement_id = paa.agreement_id (+)
AND CASE
WHEN xte.application_id = 275
AND xte.entity_code = 'EXPENDITURES' THEN
xte.source_id_int_1
END = peia.expenditure_item_id (+)
AND nvl(
aida.task_id, peia.task_id
) = pt.task_id (+)
AND peia.expenditure_id = pea.expenditure_id (+)
AND peia.expenditure_type = pet.expenditure_type (+)
AND pea.incurred_by_person_id = papf.person_id (+)
AND CASE
WHEN xte.application_id = 707
AND xte.entity_code = 'RCV_ACCOUNTING_EVENTS' THEN
xte.source_id_int_1
END = rt.transaction_id (+)
AND CASE
WHEN xte.application_id = 707
AND xte.entity_code = 'WIP_ACCOUNTING_EVENTS' THEN
xte.source_id_int_1
END = wt.transaction_id (+)
AND wt.wip_entity_id = we.wip_entity_id (+)
AND wt.department_id = bd.department_id (+)
AND wt.resource_id = br.resource_id (+)
) x
where
2=2
) x
where
x.transaction_currency not like 'STAT' |
