SELECT glcc.segment1 company, glcc.segment2 LOCATION,
glcc.segment3 cost_center, glcc.segment4 ACCOUNT,
glcc.segment5 product_line, glcc.segment6 channel,
glcc.segment7 project,
(SELECT flex_value || ' ' || fvt.description
FROM apps.ap_invoices_all ap1,
apps.gl_code_combinations glc,
apps.fnd_flex_values fv,
apps.fnd_flex_values_tl fvt
WHERE aia.accts_pay_code_combination_id = glc.code_combination_id
AND glc.segment4 = fv.flex_value
AND fv.flex_value_set_id = 1002653
AND ap1.invoice_id = aia.invoice_id
AND fv.flex_value_id = fvt.flex_value_id) code_combo_desc,
gjh.posted_date posted_on_dt,
CAST (TO_CHAR (TO_DATE (gjh.posted_date, 'DD-MM-YY'), 'YYYYMMDD') AS NUMBER
) AS month_dt_wid,
gjh.je_category, gjh.je_header_id gl_journal_id,
gjh.NAME document_name, gjh.external_reference acct_je_line_desc,
xal.description je_line_desc, pv.vendor_name vendor_customer,
pv.segment1 vendor_number,
(SELECT pap.employee_number
FROM apps.per_all_people_f pap
WHERE pap.person_id = pv.employee_id AND ROWNUM = 1) employee_number,
xal.accounting_class_code transaction_type,
aia.invoice_num transaction_number, '' mod_transaction_number,
CASE
WHEN xal.entered_dr IS NULL
THEN gjl.entered_dr
ELSE xal.entered_dr
END global_dr,
CASE
WHEN xal.entered_cr IS NULL
THEN gjl.entered_cr
ELSE xal.entered_cr
END global_cr,
xal.currency_code global_cur,
CASE
WHEN xal.accounted_cr IS NULL
THEN gjl.accounted_cr
ELSE xal.accounted_cr
END local_cr,
CASE
WHEN xal.accounted_dr IS NULL
THEN gjl.accounted_dr
ELSE xal.accounted_dr
END local_dr,
aida.amount transaction_amount,
aia.invoice_currency_code transaction_curr_code,
gl.currency_code local_cur, gjh.period_name fiscal_period,
(gb.begin_balance_dr - gb.begin_balance_cr) begin_balance,
( gb.period_net_dr
- gb.period_net_cr
+ gb.project_to_date_dr
- gb.project_to_date_cr
) end_balance,
gl.NAME ledger_name
FROM apps.ap_invoices_all aia,
apps.ap_invoice_lines_all aila,
apps.ap_invoice_distributions_all aida,
apps.xla_events xe,
apps.xla_distribution_links xdl,
xla.xla_transaction_entities xte,
xla.xla_ae_headers xah,
xla.xla_ae_lines xal,
apps.gl_code_combinations glcc,
apps.gl_import_references gir,
apps.gl_je_lines gjl,
apps.gl_je_headers gjh,
apps.ap_suppliers pv,
apps.gl_ledgers gl,
apps.gl_balances gb
WHERE 1 = 1
AND aia.vendor_id = pv.vendor_id
AND xte.entity_id = xah.entity_id
AND xte.ledger_id = gl.ledger_id
AND aia.invoice_id = aila.invoice_id
AND aila.invoice_id = aida.invoice_id
AND aila.line_number = aida.invoice_line_number
AND NVL (xte.source_id_int_1, -99) = aia.invoice_id
AND xte.application_id = 200
AND xte.entity_code = 'AP_INVOICES'
AND xah.ae_header_id = xal.ae_header_id
AND xah.application_id = xal.application_id
AND xal.ledger_id = gjh.ledger_id
AND xal.application_id = xte.application_id
AND glcc.code_combination_id = gjl.code_combination_id
AND xal.gl_sl_link_id = gir.gl_sl_link_id
AND xal.gl_sl_link_table = gir.gl_sl_link_table
AND gir.je_header_id = gjl.je_header_id
AND gir.je_line_num = gjl.je_line_num
AND gjh.je_header_id = gjl.je_header_id
AND gjh.ledger_id = xal.ledger_id
AND gjh.ledger_id = gl.ledger_id
AND gb.code_combination_id = glcc.code_combination_id
AND gb.period_name = gjh.period_name
AND gb.currency_code = gl.currency_code
AND xal.ae_header_id = xdl.ae_header_id
AND xal.ae_line_num = xdl.ae_line_num
AND xdl.source_distribution_type = 'AP_INV_DIST'
AND xdl.source_distribution_id_num_1 = aida.invoice_distribution_id
AND xdl.event_id = xe.event_id
AND xe.application_id = xte.application_id
AND gjh.period_name = 'OCT-14'
AND glcc.segment4 = '20600'
glcc.segment3 cost_center, glcc.segment4 ACCOUNT,
glcc.segment5 product_line, glcc.segment6 channel,
glcc.segment7 project,
(SELECT flex_value || ' ' || fvt.description
FROM apps.ap_invoices_all ap1,
apps.gl_code_combinations glc,
apps.fnd_flex_values fv,
apps.fnd_flex_values_tl fvt
WHERE aia.accts_pay_code_combination_id = glc.code_combination_id
AND glc.segment4 = fv.flex_value
AND fv.flex_value_set_id = 1002653
AND ap1.invoice_id = aia.invoice_id
AND fv.flex_value_id = fvt.flex_value_id) code_combo_desc,
gjh.posted_date posted_on_dt,
CAST (TO_CHAR (TO_DATE (gjh.posted_date, 'DD-MM-YY'), 'YYYYMMDD') AS NUMBER
) AS month_dt_wid,
gjh.je_category, gjh.je_header_id gl_journal_id,
gjh.NAME document_name, gjh.external_reference acct_je_line_desc,
xal.description je_line_desc, pv.vendor_name vendor_customer,
pv.segment1 vendor_number,
(SELECT pap.employee_number
FROM apps.per_all_people_f pap
WHERE pap.person_id = pv.employee_id AND ROWNUM = 1) employee_number,
xal.accounting_class_code transaction_type,
aia.invoice_num transaction_number, '' mod_transaction_number,
CASE
WHEN xal.entered_dr IS NULL
THEN gjl.entered_dr
ELSE xal.entered_dr
END global_dr,
CASE
WHEN xal.entered_cr IS NULL
THEN gjl.entered_cr
ELSE xal.entered_cr
END global_cr,
xal.currency_code global_cur,
CASE
WHEN xal.accounted_cr IS NULL
THEN gjl.accounted_cr
ELSE xal.accounted_cr
END local_cr,
CASE
WHEN xal.accounted_dr IS NULL
THEN gjl.accounted_dr
ELSE xal.accounted_dr
END local_dr,
aida.amount transaction_amount,
aia.invoice_currency_code transaction_curr_code,
gl.currency_code local_cur, gjh.period_name fiscal_period,
(gb.begin_balance_dr - gb.begin_balance_cr) begin_balance,
( gb.period_net_dr
- gb.period_net_cr
+ gb.project_to_date_dr
- gb.project_to_date_cr
) end_balance,
gl.NAME ledger_name
FROM apps.ap_invoices_all aia,
apps.ap_invoice_lines_all aila,
apps.ap_invoice_distributions_all aida,
apps.xla_events xe,
apps.xla_distribution_links xdl,
xla.xla_transaction_entities xte,
xla.xla_ae_headers xah,
xla.xla_ae_lines xal,
apps.gl_code_combinations glcc,
apps.gl_import_references gir,
apps.gl_je_lines gjl,
apps.gl_je_headers gjh,
apps.ap_suppliers pv,
apps.gl_ledgers gl,
apps.gl_balances gb
WHERE 1 = 1
AND aia.vendor_id = pv.vendor_id
AND xte.entity_id = xah.entity_id
AND xte.ledger_id = gl.ledger_id
AND aia.invoice_id = aila.invoice_id
AND aila.invoice_id = aida.invoice_id
AND aila.line_number = aida.invoice_line_number
AND NVL (xte.source_id_int_1, -99) = aia.invoice_id
AND xte.application_id = 200
AND xte.entity_code = 'AP_INVOICES'
AND xah.ae_header_id = xal.ae_header_id
AND xah.application_id = xal.application_id
AND xal.ledger_id = gjh.ledger_id
AND xal.application_id = xte.application_id
AND glcc.code_combination_id = gjl.code_combination_id
AND xal.gl_sl_link_id = gir.gl_sl_link_id
AND xal.gl_sl_link_table = gir.gl_sl_link_table
AND gir.je_header_id = gjl.je_header_id
AND gir.je_line_num = gjl.je_line_num
AND gjh.je_header_id = gjl.je_header_id
AND gjh.ledger_id = xal.ledger_id
AND gjh.ledger_id = gl.ledger_id
AND gb.code_combination_id = glcc.code_combination_id
AND gb.period_name = gjh.period_name
AND gb.currency_code = gl.currency_code
AND xal.ae_header_id = xdl.ae_header_id
AND xal.ae_line_num = xdl.ae_line_num
AND xdl.source_distribution_type = 'AP_INV_DIST'
AND xdl.source_distribution_id_num_1 = aida.invoice_distribution_id
AND xdl.event_id = xe.event_id
AND xe.application_id = xte.application_id
AND gjh.period_name = 'OCT-14'
AND glcc.segment4 = '20600'
Very good query
ReplyDelete