SELECT DISTINCT
'WOAPINV'
|| '-'
|| ai.invoice_id
|| '-'
|| ail.line_number
|| '-'
|| aid.invoice_distribution_id
euid,
NVL (ail.attribute12, ai.attribute2) workorder_number,
DECODE (ai.invoice_type_lookup_code,
'EXPENSE REPORT', 'iExpense Charges',
'AP Direct Charges')
SOURCE,
DECODE (ai.invoice_type_lookup_code,
'STANDARD', 'Standard Invoice',
'CREDIT', 'Credit Memo',
'DEBIT', 'Debit Memo',
'EXPENSE REPORT', NULL,
'Standard Invoice')
transaction_type,
(SELECT segment1
FROM apps.mtl_system_items_b msi
WHERE msi.inventory_item_id = ail.inventory_item_id
AND msi.organization_id =
(SELECT mp.organization_id
FROM mtl_parameters mp,
ont.oe_sys_parameters_all param
WHERE 1 = 1
AND parameter_code =
'MASTER_ORGANIZATION_ID'
AND mp.organization_id =
param.parameter_value
AND ai.org_id = param.org_id))
item_num,
NVL (ail.item_description, ail.description) item_name,
unit_meas_lookup_code uom,
aid.quantity_invoiced actual_qty,
aid.unit_price actual_unit_cost,
(aid.quantity_invoiced * aid.unit_price) actual_ext_cost,
ai.invoice_num "Transaction Source Number",
ail.line_number "Transaction line Number",
ai.invoice_num
|| '-'
|| ail.line_number
|| '-'
|| aid.distribution_line_number
transaction_num,
ai.invoice_date transaction_date,
aid.amount transaction_amount,
NULL vendor_po,
gjh.period_name gl_period,
concatenated_segments gl_accounts,
ai.gl_date,
gjh.NAME gl_journal_name,
DECODE (gjh.status, 'P', 'Y', 'N') posted_to_gl,
pv.vendor_name resource_name,
NULL employee_num,
pv.segment1 vendor_num,
NULL actual_hours,
NULL effective_rate,
NULL ext_payroll_cost,
ai.invoice_id source_id,
ail.line_number source_line_id,
aid.dist_code_combination_id source_ccid,
aid.invoice_distribution_id source_dist_id,
je_source journal_source,
gjb.NAME journal_batch_name,
je_category journal_category,
gjl.je_line_num journal_line_number,
gjh.je_header_id je_header_id,
gjb.je_batch_id je_batch_id,
gjl.je_line_num je_line_num,
NULL line_item_id,
ai.vendor_id trx_party_id,
ai.vendor_site_id trx_party_site_id,
ai.org_id org_id,
gcc.segment4 LOB,
gcc.segment5 locations
FROM apps.ap_invoices_all ai,
apps.ap_invoice_lines_all ail,
apps.ap_invoice_distributions_all aid,
apps.ap_lookup_codes alc1,
apps.ap_lookup_codes alc2,
apps.po_vendors pv,
apps.ap_lookup_codes alc3,
apps.gl_code_combinations_kfv gcc,
gl_je_batches gjb,
gl_je_headers gjh,
gl_je_lines gjl,
gl_import_references gir,
xla_ae_headers xah,
xla_ae_lines xal,
xla_events xe,
xla_transaction_entities_upg xte,
xla_distribution_links xdl
WHERE ai.invoice_id = ail.invoice_id
AND aid.invoice_id = ail.invoice_id
AND aid.invoice_line_number = ail.line_number
AND aid.dist_code_combination_id = gcc.code_combination_id
AND ail.po_header_id IS NULL
AND ail.rcv_transaction_id IS NULL
AND (ail.attribute12 IS NOT NULL OR ai.attribute2 IS NOT NULL)
AND ai.vendor_id = pv.vendor_id
AND ai.invoice_type_lookup_code != 'EXPENSE REPORT'
AND alc1.lookup_code(+) = ai.invoice_type_lookup_code
AND alc1.lookup_type(+) = 'INVOICE TYPE'
AND alc2.lookup_code(+) = ai.payment_status_flag
AND alc2.lookup_type(+) = 'INVOICE PAYMENT STATUS'
AND alc3.lookup_type(+) = 'NLS TRANSLATION'
AND alc3.lookup_code(+) = DECODE (aid.match_status_flag,
NULL,
'NEVER APPROVED',
'N',
'NEVER APPROVED',
'T',
'NEEDS REAPPROVAL',
'A',
'APPROVED',
'S',
'NEVER APPROVED')
AND alc3.displayed_field = 'Validated'
AND gjb.je_batch_id = gjh.je_batch_id
AND gjh.je_header_id = gjl.je_header_id
AND gjb.je_batch_id = gir.je_batch_id
AND gjh.je_header_id = gir.je_header_id
AND gjl.je_line_num = gir.je_line_num
AND gir.reference_5 = TO_CHAR (xah.entity_id)
AND gir.reference_6 = TO_CHAR (xah.event_id)
AND gir.reference_7 = xah.ae_header_id
AND gir.reference_8 = xal.ae_line_num
AND xah.entity_id = xte.entity_id
AND xah.event_id = xe.event_id
AND xe.event_id = xdl.event_id
AND xe.event_id = aid.accounting_event_id
AND xah.ae_header_id = xal.ae_header_id
AND source_id_int_1 = ai.invoice_id
AND accounting_class_code != 'LIABILITY'
AND rounding_class_code != 'LIABILITY'
AND source_distribution_type = 'AP_INV_DIST'
AND gjh.status = 'P'
AND source_distribution_id_num_1 = aid.invoice_distribution_id
AND xdl.ae_header_id = xah.ae_header_id
AND xdl.ae_line_num = xal.ae_line_num
AND transaction_number = invoice_num
UNION
SELECT DISTINCT
'WOIEXP'
|| '-'
|| ai.invoice_id
|| '-'
|| ail.line_number
|| '-'
|| aid.invoice_distribution_id
euid,
NVL (ail.attribute12, ai.attribute2) workorder_number,
DECODE (ai.invoice_type_lookup_code,
'EXPENSE REPORT', 'iExpense Charges',
'AP Direct Charges')
SOURCE,
DECODE (ai.invoice_type_lookup_code,
'STANDARD', 'Standard Invoice',
'CREDIT', 'Credit Memo',
'DEBIT', 'Debit Memo',
'EXPENSE REPORT', NULL,
'Standard Invoice')
transaction_type,
(SELECT segment1
FROM apps.mtl_system_items_b msi
WHERE msi.inventory_item_id = ail.inventory_item_id
AND msi.organization_id =
(SELECT mp.organization_id
FROM mtl_parameters mp,
ont.oe_sys_parameters_all param
WHERE 1 = 1
AND parameter_code =
'MASTER_ORGANIZATION_ID'
AND mp.organization_id =
param.parameter_value
AND ai.org_id = param.org_id))
item_num,
NVL (ail.item_description, ail.description) item_name,
unit_meas_lookup_code uom,
aid.quantity_invoiced actual_qty,
aid.unit_price actual_unit_cost,
(aid.quantity_invoiced * aid.unit_price) actual_ext_cost,
ai.invoice_num "Transaction Source Number",
ail.line_number "Transaction line Number",
ai.invoice_num
|| '-'
|| ail.line_number
|| '-'
|| aid.distribution_line_number
transaction_num,
ai.invoice_date transaction_date,
aid.amount transaction_amount,
NULL vendor_po,
gjh.period_name gl_period,
concatenated_segments gl_accounts,
ai.gl_date,
gjh.NAME gl_journal_name,
DECODE (gjh.status, 'P', 'Y', 'N') posted_to_gl,
(SELECT DISTINCT full_name
FROM apps.per_all_people_f papf
WHERE papf.person_id = hz.person_identifier
AND TRUNC (SYSDATE) BETWEEN TRUNC (
papf.effective_start_date
)
AND TRUNC (
papf.effective_end_date
))
resource_name,
(SELECT DISTINCT employee_number
FROM apps.per_all_people_f papf
WHERE papf.person_id = hz.person_identifier
AND TRUNC (SYSDATE) BETWEEN TRUNC (
papf.effective_start_date
)
AND TRUNC (
papf.effective_end_date
))
employee_num,
pv.segment1 vendor_num,
NULL actual_hours,
NULL effective_rate,
NULL ext_payroll_cost,
ai.invoice_id source_id,
ail.line_number source_line_id,
aid.dist_code_combination_id source_ccid,
aid.invoice_distribution_id source_dist_id,
je_source journal_source,
gjb.NAME journal_batch_name,
je_category journal_category,
gjl.je_line_num journal_line_number,
gjh.je_header_id je_header_id,
gjb.je_batch_id je_batch_id,
gjl.je_line_num je_line_num,
NULL line_item_id,
ai.vendor_id trx_party_id,
ai.vendor_site_id trx_party_site_id,
ai.org_id org_id,
gcc.segment4 LOB,
gcc.segment5 locations
FROM apps.ap_invoices_all ai,
apps.ap_invoice_lines_all ail,
apps.ap_invoice_distributions_all aid,
apps.ap_lookup_codes alc1,
apps.ap_lookup_codes alc2,
apps.ap_lookup_codes alc3,
apps.po_vendors pv,
apps.gl_code_combinations_kfv gcc,
apps.hz_parties hz,
gl_je_batches gjb,
gl_je_headers gjh,
gl_je_lines gjl,
gl_import_references gir,
xla_ae_headers xah,
xla_ae_lines xal,
xla_events xe,
xla_transaction_entities_upg xte,
xla_distribution_links xdl
WHERE ai.invoice_id = ail.invoice_id
AND aid.invoice_id = ail.invoice_id
AND aid.dist_code_combination_id = gcc.code_combination_id
AND aid.invoice_line_number = ail.line_number
AND hz.party_id = ai.party_id
AND ail.po_header_id IS NULL
AND ail.rcv_transaction_id IS NULL
AND ail.attribute12 IS NOT NULL
AND ai.vendor_id = pv.vendor_id
AND ai.invoice_type_lookup_code = 'EXPENSE REPORT'
AND alc1.lookup_code(+) = ai.invoice_type_lookup_code
AND alc1.lookup_type(+) = 'INVOICE TYPE'
AND alc2.lookup_code(+) = ai.payment_status_flag
AND alc2.lookup_type(+) = 'INVOICE PAYMENT STATUS'
AND alc3.lookup_type(+) = 'NLS TRANSLATION'
AND alc3.lookup_code(+) = DECODE (aid.match_status_flag,
NULL,
'NEVER APPROVED',
'N',
'NEVER APPROVED',
'T',
'NEEDS REAPPROVAL',
'A',
'APPROVED',
'S',
'NEVER APPROVED')
AND alc3.displayed_field = 'Validated'
AND gjb.je_batch_id = gjh.je_batch_id
AND gjh.je_header_id = gjl.je_header_id
AND gjb.je_batch_id = gir.je_batch_id
AND gjh.je_header_id = gir.je_header_id
AND gjl.je_line_num = gir.je_line_num
AND gir.reference_5 = TO_CHAR (xah.entity_id)
AND gir.reference_6 = TO_CHAR (xah.event_id)
AND gir.reference_7 = xah.ae_header_id
AND gir.reference_8 = xal.ae_line_num
AND xah.entity_id = xte.entity_id
AND xah.event_id = xe.event_id
AND xe.event_id = xdl.event_id
AND xe.event_id = aid.accounting_event_id
AND xah.ae_header_id = xal.ae_header_id
AND source_id_int_1 = ai.invoice_id
AND source_distribution_type = 'AP_INV_DIST'
AND accounting_class_code != 'LIABILITY'
AND rounding_class_code != 'LIABILITY'
AND gjh.status = 'P'
AND source_distribution_id_num_1 = aid.invoice_distribution_id
AND xdl.ae_header_id = xah.ae_header_id
AND xdl.ae_line_num = xal.ae_line_num
AND transaction_number = invoice_num
UNION
SELECT 'WOARREV'
|| '-'
|| ct.customer_trx_id
|| '-'
|| ctl.customer_trx_line_id
|| '-'
|| gd.cust_trx_line_gl_dist_id
euid,
NVL (NVL (ct.interface_header_attribute1, ct.attribute10),
ctl.interface_line_attribute1)
workorder_number,
'AR Transactions' SOURCE,
DECODE (ctt.TYPE,
'INV',
'Invoice',
'CM',
'Credit Memo')
transaction_type,
(SELECT segment1
FROM apps.mtl_system_items_b msi
WHERE msi.inventory_item_id = ctl.inventory_item_id
AND msi.organization_id =
(SELECT mp.organization_id
FROM mtl_parameters mp,
ont.oe_sys_parameters_all param
WHERE 1 = 1
AND parameter_code =
'MASTER_ORGANIZATION_ID'
AND mp.organization_id =
param.parameter_value
AND ct.org_id = param.org_id))
item_num,
NVL (
(SELECT description
FROM apps.mtl_system_items_b msi1
WHERE msi1.inventory_item_id = ctl.inventory_item_id
AND msi1.organization_id =
(SELECT mp.organization_id
FROM mtl_parameters mp,
ont.oe_sys_parameters_all param
WHERE 1 = 1
AND parameter_code =
'MASTER_ORGANIZATION_ID'
AND mp.organization_id =
param.parameter_value
AND ct.org_id = param.org_id)),
ctl.description
)
item_name,
ctl.uom_code uom,
ctl.quantity_invoiced actual_quantity,
ctl.unit_selling_price actual_unit_price,
(ctl.quantity_invoiced * ctl.unit_selling_price) actual_ext_price,
ct.trx_number "Transaction Source Number",
ctl.line_number "Transaction line Number",
ct.trx_number
|| '-'
|| ctl.line_number
|| '-'
|| gd.cust_trx_line_gl_dist_id
transaction_num,
TRUNC (ct.trx_date) transaction_date,
gd.amount transaction_amount,
NULL vendor_po,
gjh.period_name gl_period,
gcc.concatenated_segments gl_accounts,
gd.gl_date,
gjh.NAME gl_journal_name,
DECODE (gjh.status, 'P', 'Y', 'N') posted_to_gl,
NULL resource_name,
NULL employee_num,
NULL vendor_num,
NULL acutal_hours,
NULL effective_rate,
NULL ext_payroll_cost,
ct.customer_trx_id source_id,
ctl.customer_trx_line_id source_line_id,
gd.code_combination_id source_ccid,
gd.cust_trx_line_gl_dist_id source_dist_id,
je_source journal_source,
gjb.NAME journal_batch_name,
je_category journal_category,
gjl.je_line_num journal_line_number,
gjh.je_header_id je_header_id,
gjb.je_batch_id je_batch_id,
gjl.je_line_num je_line_num,
inventory_item_id line_item_id,
rac_bill_party.party_id trx_party_id,
raa_bill_ps.party_site_id trx_party_site_id,
ct.org_id org_id,
gcc.segment4 LOB,
gcc.segment5 locations
FROM apps.ra_cust_trx_line_gl_dist_all gd,
apps.ra_customer_trx_all ct,
apps.ra_customer_trx_lines_all ctl,
apps.ra_cust_trx_types_all ctt,
apps.gl_code_combinations_kfv gcc,
apps.hz_cust_accounts_all rac_bill,
apps.hz_parties rac_bill_party,
apps.hz_cust_site_uses_all su_bill,
apps.hz_cust_acct_sites_all raa_bill,
apps.hz_party_sites raa_bill_ps,
gl_je_batches gjb,
gl_je_headers gjh,
gl_je_lines gjl,
gl_import_references gir,
xla_ae_headers xah,
xla_ae_lines xal,
xla_distribution_links xdl
WHERE 1 = 1
AND ct.customer_trx_id = gd.customer_trx_id
AND ctl.customer_trx_line_id = gd.customer_trx_line_id
AND 'REV' = gd.account_class
AND ctl.line_type != 'TAX'
AND ct.customer_trx_id = ctl.customer_trx_id --AND ct.trx_number = '10060'
AND ct.complete_flag = 'Y'
AND ctt.org_id = ct.org_id
AND ct.cust_trx_type_id = ctt.cust_trx_type_id
AND ctt.TYPE <> 'BR'
AND ct.bill_to_customer_id = rac_bill.cust_account_id
AND rac_bill.party_id = rac_bill_party.party_id
AND ct.bill_to_site_use_id = su_bill.site_use_id
AND su_bill.cust_acct_site_id = raa_bill.cust_acct_site_id
AND raa_bill.party_site_id = raa_bill_ps.party_site_id
AND gd.gl_posted_date IS NOT NULL
AND gd.code_combination_id = gcc.code_combination_id
AND xdl.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
AND xdl.source_distribution_id_num_1 = gd.cust_trx_line_gl_dist_id
AND gjb.je_batch_id = gjh.je_batch_id
AND gjh.je_header_id = gjl.je_header_id
AND gjb.je_batch_id = gir.je_batch_id
AND gjh.je_header_id = gir.je_header_id
AND gjl.je_line_num = gir.je_line_num
AND gir.reference_5 = TO_CHAR (xah.entity_id)
AND gir.reference_6 = TO_CHAR (xah.event_id)
AND gir.reference_7 = xah.ae_header_id
AND gir.reference_8 = xal.ae_line_num
AND xah.ae_header_id = xal.ae_header_id
AND xdl.ae_header_id = xah.ae_header_id
AND xdl.ae_line_num = xal.ae_line_num
AND gjh.status = 'P'
AND xdl.event_id = gd.event_id
AND UPPER(NVL (NVL (ct.interface_header_attribute1, ct.attribute10),
ctl.interface_line_attribute1)) LIKE
'WO%'
AND ct.org_id = <ORG_ID>
UNION
SELECT DISTINCT
'WOPORCPT'
|| '-'
|| rt.transaction_id
|| '-'
|| rsl.line_num
|| '-'
|| rt.po_distribution_id
euid,
NVL (pl.attribute3, ph.attribute3) workorder_number,
'PO Receipts' SOURCE,
DECODE (transaction_type,
'RECEIVE',
'Receive',
'RETURN TO VENDOR',
'Return',
'CORRECT',
'Correction')
transaction_type,
(SELECT segment1
FROM apps.mtl_system_items_b
WHERE inventory_item_id = rsl.item_id
AND rsl.to_organization_id = organization_id)
item_num,
NVL (
(SELECT description
FROM apps.mtl_system_items_b
WHERE inventory_item_id = rsl.item_id
AND rsl.to_organization_id = organization_id),
rsl.item_description
)
item_name,
rsl.unit_of_measure uom,
rt.quantity actual_quantity,
po_unit_price actual_unit_price,
(rt.quantity * po_unit_price) actual_ext_price,
rsh.receipt_num "Transaction Source Number",
rsl.line_num "Transaction line Number",
rsh.receipt_num || '-' || rsl.line_num || '-' || pd.distribution_num
transaction_num,
TRUNC (rt.transaction_date) transaction_date,
(rt.quantity * po_unit_price) transaction_amt,
ph.segment1 vendor_po,
jh.period_name gl_period,
concatenated_segments gl_accounts,
rrsl.accounting_date gl_date,
jh.NAME gl_journal_name,
DECODE (NVL (jb.status, 'U'), 'P', 'Y', 'N') posted_to_gl,
pv.vendor_name resource_name,
NULL employee_num,
pv.segment1 vendor_num,
NULL acutal_hours,
NULL effective_rate,
NULL ext_payroll_cost,
rt.transaction_id source_id,
rsl.shipment_line_id source_line_id,
pd.code_combination_id source_ccid,
xdl.source_distribution_id_num_1 source_dist_id,
jh.je_source journal_source,
jb.NAME journal_batch_name,
jh.je_category journal_category,
jl.je_line_num journal_line_number,
jh.je_header_id je_header_id,
jh.je_batch_id je_batch_id,
jl.je_line_num je_line_num,
rsl.item_id line_item_id,
ph.vendor_id trx_party_id,
ph.vendor_site_id trx_party_site_id,
ph.org_id org_id,
gcc.segment4 LOB,
gcc.segment5 locations
FROM apps.rcv_transactions rt,
apps.rcv_shipment_headers rsh,
apps.rcv_shipment_lines rsl,
apps.po_headers_all ph,
apps.po_lines_all pl,
apps.po_distributions_all pd,
apps.po_vendors pv,
apps.po_vendor_sites_all pvs,
apps.org_organization_definitions ood,
apps.gl_code_combinations_kfv gcc,
apps.rcv_receiving_sub_ledger rrsl,
apps.xla_distribution_links xdl,
apps.gl_import_references gir,
apps.gl_je_headers jh,
apps.gl_je_lines jl,
apps.gl_je_batches jb
WHERE 1 = 1
AND rt.source_document_code = 'PO'
AND rt.destination_type_code = 'RECEIVING'
AND rsl.destination_type_code = 'EXPENSE'
AND transaction_type IN ('RECEIVE', 'RETURN TO VENDOR', 'CORRECT')
AND rt.shipment_header_id = rsh.shipment_header_id
AND rsl.shipment_header_id = rsh.shipment_header_id
AND rsl.shipment_line_id = rt.shipment_line_id
AND ph.po_header_id = rt.po_header_id
AND pl.po_line_id = rt.po_line_id
AND pl.po_line_id = pd.po_line_id
AND ph.po_header_id = pl.po_header_id
AND pd.po_distribution_id = rsl.po_distribution_id
AND pd.code_combination_id = gcc.code_combination_id
AND rsl.to_organization_id = ood.organization_id
AND rrsl.rcv_transaction_id = rt.transaction_id
AND gir.reference_7 = xdl.ae_header_id
AND gir.reference_6 = TO_CHAR (xdl.event_id)
AND gir.reference_8 = xdl.ae_line_num
AND jh.je_header_id = jl.je_header_id
AND jl.je_header_id = gir.je_header_id
AND jl.je_line_num = gir.je_line_num
AND jb.je_batch_id = jh.je_batch_id
AND (ph.attribute3 IS NOT NULL OR pl.attribute3 IS NOT NULL)
AND ph.vendor_id = pv.vendor_id
AND pv.vendor_id = pvs.vendor_id
AND rrsl.accounting_line_type(+) = 'Accrual'
AND xdl.source_distribution_id_num_1 = rrsl.rcv_sub_ledger_id
AND xdl.source_distribution_type = 'RCV_RECEIVING_SUB_LEDGER'
UNION
SELECT 'WOMATINV'
|| '-'
|| mmt.transaction_id
|| '-'
|| mta.reference_account
euid,
mil.segment3 workorder_number,
'Inventory Cost' SOURCE,
flv.description transaction_type,
msi.segment1 item_num,
msi.description item_name,
mmt.transaction_uom uom,
ABS (mmt.transaction_quantity) actual_quantity,
rate_or_amount actual_unit_cost,
ABS (mmt.transaction_quantity) * rate_or_amount actual_ext_cost,
TO_CHAR (mmt.transaction_id) "Transaction Source Number",
NULL "Transaction line Number",
TO_CHAR (mmt.transaction_id) || '-' || mta.reference_account
transaction_num,
mmt.transaction_date transaction_date,
ABS (mta.base_transaction_value) transacation_amount,
pha.segment1 vendor_po,
jh.period_name gl_period,
concatenated_segments gl_accounts,
mmt.transaction_date gl_date,
jh.NAME gl_journal_name,
DECODE (NVL (jb.status, 'U'), 'P', 'Y', 'N') posted_to_gl,
(CASE
WHEN (mil.segment1 < '1000000')
THEN
(SELECT full_name
FROM per_all_people_f
WHERE employee_number = TO_CHAR (mil.segment1)
AND TRUNC (SYSDATE) BETWEEN effective_start_date
AND effective_end_date)
WHEN (mil.segment1 BETWEEN '1000000' AND '2000000')
THEN
(SELECT vendor_name
FROM po_vendors
WHERE segment1 = mil.segment1)
WHEN (mil.segment1 > '2000000')
THEN
(SELECT full_name
FROM per_all_people_f
WHERE npw_number = mil.segment1
AND TRUNC (SYSDATE) BETWEEN effective_start_date
AND effective_end_date)
ELSE
NULL
END)
AS resource_name,
(CASE
WHEN (mil.segment1 < '1000000')
THEN
(SELECT employee_number
FROM per_all_people_f
WHERE employee_number = mil.segment1
AND TRUNC (SYSDATE) BETWEEN effective_start_date
AND effective_end_date)
WHEN (mil.segment1 > '2000000')
THEN
(SELECT npw_number
FROM per_all_people_f
WHERE npw_number = mil.segment1
AND TRUNC (SYSDATE) BETWEEN effective_start_date
AND effective_end_date)
ELSE
NULL
END)
AS employee_num,
(CASE
WHEN (mil.segment1 BETWEEN '1000000' AND '2000000')
THEN
(SELECT segment1
FROM po_vendors
WHERE segment1 = mil.segment1)
ELSE
NULL
END)
AS vendor_num,
NULL actual_hours,
NULL effective_rate,
NULL ext_payroll_cost,
mmt.transaction_id source_id,
mil.inventory_location_id source_line_id,
gcc.code_combination_id source_ccid,
mmt.organization_id source_dist_id,
jh.je_source journal_source,
jb.NAME journal_batch_name,
jh.je_category journal_category,
jl.je_line_num journal_line_number,
jh.je_header_id je_header_id,
jb.je_batch_id je_batch_id,
jl.je_line_num je_line_num,
msi.inventory_item_id line_item_id,
pha.vendor_id trx_party_id,
pha.vendor_site_id trx_party_side_id,
ood.operating_unit org_id,
gcc.segment4 LOB,
gcc.segment5 locations
FROM mtl_material_transactions mmt,
mtl_item_locations mil,
mtl_transaction_types mtt,
mtl_txn_source_types mtst,
mtl_system_items_b msi,
po_headers_all pha,
mtl_transaction_accounts mta,
gl_code_combinations_kfv gcc,
-- mtl_generic_dispositions mgd,
xla_distribution_links xdl,
gl_import_references gir,
gl_je_headers jh,
gl_je_lines jl,
gl_je_batches jb,
fnd_lookup_values flv,
org_organization_definitions ood
WHERE 1 = 1
AND mmt.transaction_type_id = mtt.transaction_type_id
AND mmt.transaction_source_type_id = mtst.transaction_source_type_id
AND msi.inventory_item_id = mmt.inventory_item_id
AND msi.organization_id = mmt.organization_id
AND mmt.transaction_source_id = pha.po_header_id(+)
AND mil.inventory_location_id = mmt.locator_id
AND mta.transaction_id = mmt.transaction_id
AND gcc.code_combination_id = mta.reference_account --
AND mgd.disposition_id = mmt.transaction_source_id --
AND flv.meaning = mgd.segment1
AND NVL (mil.segment3, 'XxX') <> '000' --
AND mtst.transaction_source_type_name = 'Account alias'
AND mta.cost_element_id IS NULL
AND mta.transaction_date IS NOT NULL
AND xdl.source_distribution_id_num_1 = mta.inv_sub_ledger_id
AND xdl.source_distribution_type = 'MTL_TRANSACTION_ACCOUNTS'
AND gir.reference_7 = TO_CHAR (xdl.ae_header_id)
AND gir.reference_6 = TO_CHAR (xdl.event_id)
AND gir.reference_8 = xdl.ae_line_num
AND jh.je_header_id = jl.je_header_id
AND jl.je_header_id = gir.je_header_id
AND jl.je_line_num = gir.je_line_num
AND jb.je_batch_id = jh.je_batch_id
AND flv.lookup_type = 'XXMTZ_SBL_INV_WO_ISSUE_RETN'
AND flv.meaning = mmt.attribute2
AND mmt.organization_id = ood.organization_id
AND flv.enabled_flag = 'Y'
AND flv.tag = ood.operating_unit
AND flv.description = 'Return'
UNION
SELECT 'WOMATINV'
|| '-'
|| mmt.transaction_id
|| '-'
|| mta.reference_account
euid,
mil.segment3 workorder_number,
'Inventory Cost' SOURCE,
flv.description transaction_type,
msi.segment1 item_num,
msi.description item_name,
mmt.transaction_uom uom,
ABS (mmt.transaction_quantity) actual_quantity,
rate_or_amount actual_unit_cost,
ABS (mmt.transaction_quantity) * rate_or_amount actual_ext_cost,
TO_CHAR (mmt.transaction_id) "Transaction Source Number",
NULL "Transaction line Number",
TO_CHAR (mmt.transaction_id) || '-' || mta.reference_account
transaction_num,
mmt.transaction_date transaction_date,
ABS (mta.base_transaction_value) transacation_amount,
pha.segment1 vendor_po,
jh.period_name gl_period,
concatenated_segments gl_accounts,
mmt.transaction_date gl_date,
jh.NAME gl_journal_name,
DECODE (NVL (jb.status, 'U'), 'P', 'Y', 'N') posted_to_gl,
(CASE
WHEN (TO_NUMBER (mil.segment1) < TO_NUMBER ('1000000'))
THEN
(SELECT full_name
FROM per_all_people_f
WHERE employee_number = TO_CHAR (mil.segment1)
AND TRUNC (SYSDATE) BETWEEN effective_start_date
AND effective_end_date)
WHEN (TO_NUMBER (mil.segment1) BETWEEN TO_NUMBER ('1000000')
AND TO_NUMBER ('2000000'))
THEN
(SELECT vendor_name
FROM po_vendors
WHERE segment1 = mil.segment1)
WHEN (TO_NUMBER (mil.segment1) > TO_NUMBER ('2000000'))
THEN
(SELECT full_name
FROM per_all_people_f
WHERE npw_number = mil.segment1
AND TRUNC (SYSDATE) BETWEEN effective_start_date
AND effective_end_date)
ELSE
NULL
END)
AS resource_name,
(CASE
WHEN (TO_NUMBER (mil.segment1) < TO_NUMBER ('1000000'))
THEN
(SELECT employee_number
FROM per_all_people_f
WHERE employee_number = mil.segment1
AND TRUNC (SYSDATE) BETWEEN effective_start_date
AND effective_end_date)
WHEN (TO_NUMBER (mil.segment1) > TO_NUMBER ('2000000'))
THEN
(SELECT npw_number
FROM per_all_people_f
WHERE npw_number = mil.segment1
AND TRUNC (SYSDATE) BETWEEN effective_start_date
AND effective_end_date)
ELSE
NULL
END)
AS employee_num,
(CASE
WHEN (TO_NUMBER (mil.segment1) BETWEEN TO_NUMBER ('1000000')
AND TO_NUMBER ('2000000'))
THEN
(SELECT segment1
FROM po_vendors
WHERE segment1 = mil.segment1)
ELSE
NULL
END)
AS vendor_num,
NULL actual_hours,
NULL effective_rate,
NULL ext_payroll_cost,
mmt.transaction_id source_id,
mil.inventory_location_id source_line_id,
gcc.code_combination_id source_ccid,
mmt.organization_id source_dist_id,
jh.je_source journal_source,
jb.NAME journal_batch_name,
jh.je_category journal_category,
jl.je_line_num journal_line_number,
jh.je_header_id je_header_id,
jb.je_batch_id je_batch_id,
jl.je_line_num je_line_num,
msi.inventory_item_id line_item_id,
pha.vendor_id trx_party_id,
pha.vendor_site_id trx_party_side_id,
ood.operating_unit org_id,
gcc.segment4 LOB,
gcc.segment5 locations
FROM mtl_material_transactions mmt,
mtl_item_locations mil,
mtl_transaction_types mtt,
mtl_txn_source_types mtst,
mtl_system_items_b msi,
po_headers_all pha,
mtl_transaction_accounts mta,
gl_code_combinations_kfv gcc,
-- mtl_generic_dispositions mgd,
xla_distribution_links xdl,
gl_import_references gir,
gl_je_headers jh,
gl_je_lines jl,
gl_je_batches jb,
fnd_lookup_values flv,
org_organization_definitions ood
WHERE 1 = 1
AND mmt.transaction_type_id = mtt.transaction_type_id
AND mmt.transaction_source_type_id = mtst.transaction_source_type_id
AND msi.inventory_item_id = mmt.inventory_item_id
AND msi.organization_id = mmt.organization_id
AND mmt.transaction_source_id = pha.po_header_id(+)
AND mil.inventory_location_id = mmt.locator_id
AND mta.transaction_id = mmt.transaction_id
AND gcc.code_combination_id = mta.reference_account --
AND mgd.disposition_id = mmt.transaction_source_id --
AND flv.meaning = mgd.segment1
AND NVL (mil.segment3, 'XxX') <> '000' --
AND mtst.transaction_source_type_name = 'Account alias'
AND mta.cost_element_id IS NULL
AND mta.transaction_date IS NOT NULL
AND xdl.source_distribution_id_num_1 = mta.inv_sub_ledger_id
AND xdl.source_distribution_type = 'MTL_TRANSACTION_ACCOUNTS'
AND gir.reference_7 = TO_CHAR (xdl.ae_header_id)
AND gir.reference_6 = TO_CHAR (xdl.event_id)
AND gir.reference_8 = xdl.ae_line_num
AND jh.je_header_id = jl.je_header_id
AND jl.je_header_id = gir.je_header_id
AND jl.je_line_num = gir.je_line_num
AND jb.je_batch_id = jh.je_batch_id
AND flv.lookup_type = 'XXMTZ_SBL_INV_WO_ISSUE_RETN'
AND flv.meaning = mmt.attribute2
AND mmt.organization_id = ood.organization_id
AND flv.enabled_flag = 'Y'
AND flv.tag = ood.operating_unit
AND flv.description = 'Issuance'
UNION
SELECT 'WOGLMAN'
|| '-'
|| gjh.je_batch_id
|| '-'
|| gjl.je_line_num
|| '-'
|| gjh.je_header_id
"EUID",
gjl.attribute10 "workorder_number",
'Miscellaneous Cost' "Source",
DECODE (gjl.accounted_dr, NULL, 'Credit', 'Debit')
"transaction_type",
NULL item_num,
gjl.description item_name,
NULL uom,
NULL actual_quantity,
NULL actual_unit_cost,
NVL (gjl.accounted_cr, gjl.accounted_dr) actual_ext_cost,
gjh.description "Transaction Source Number",
gjl.je_line_num "Transaction line Number",
SUBSTR (gjh.NAME, 1, 18)
|| '-'
|| gjb.je_batch_id
|| '-'
|| gjl.je_line_num
"transaction_num",
gjh.date_created "transaction_date",
NVL (gjl.accounted_cr, gjl.accounted_dr) "transacation_amount",
NULL vendor_po,
gjh.period_name "gl_period",
gcc.segment1
|| '.'
|| gcc.segment2
|| '.'
|| gcc.segment3
|| '.'
|| gcc.segment4
|| '.'
|| gcc.segment5
|| '.'
|| gcc.segment6
|| '.'
|| gcc.segment7
|| '.'
|| gcc.segment8
"gl_accounts",
gjh.default_effective_date "gl_date",
gjh.NAME "gl_journal_name",
DECODE (gjh.status, 'P', 'Y', 'N') "posted_to_gl",
NULL resource_name,
NULL employee_num,
NULL vendor_num,
NULL actual_hours,
NULL effective_rate,
NULL ext_payroll_cost,
gjh.je_header_id source_id,
gjl.je_line_num source_line_id,
gcc.code_combination_id source_ccid,
NULL source_dist_id,
gjh.je_source journal_source,
gjb.NAME journal_batch_name,
gjh.je_category journal_category,
gjl.je_line_num journal_line_number,
gjh.je_header_id je_header_id,
gjb.je_batch_id je_batch_id,
gjl.je_line_num je_line_num,
NULL line_item_id,
NULL trx_party_id,
NULL trx_party_side_id,
gjb.org_id org_id,
gcc.segment4 LOB,
gcc.segment5 locations
FROM gl_je_batches gjb,
gl_je_headers gjh,
gl_je_lines gjl,
gl_code_combinations gcc
WHERE gjb.je_batch_id = gjh.je_batch_id
AND gjl.je_header_id = gjh.je_header_id
AND gcc.code_combination_id = gjl.code_combination_id
AND gjl.attribute10 IS NOT NULL
AND gjh.ledger_id = 2021
AND (gcc.segment2 LIKE '5%')
AND gjh.je_source IN ('Manual', 'Spreadsheet')
UNION
SELECT 'WOGLMAN'
|| '-'
|| gjh.je_batch_id
|| '-'
|| gjl.je_line_num
|| '-'
|| gjh.je_header_id
"EUID",
gjl.attribute10 "workorder_number",
'Miscellaneous Revenue' "Source",
DECODE (gjl.accounted_dr, NULL, 'Credit', 'Debit')
"transaction_type",
NULL item_num,
gjl.description item_name,
NULL uom,
NULL actual_quantity,
NULL actual_unit_cost,
NVL (gjl.accounted_cr, gjl.accounted_dr) actual_ext_cost,
gjh.description "Transaction Source Number",
gjl.je_line_num "Transaction line Number",
SUBSTR (gjh.NAME, 1, 18)
|| '-'
|| gjb.je_batch_id
|| '-'
|| gjl.je_line_num
"transaction_num",
gjh.date_created "transaction_date",
NVL (gjl.accounted_cr, gjl.accounted_dr) "transacation_amount",
NULL vendor_po,
gjh.period_name "gl_period",
gcc.segment1
|| '.'
|| gcc.segment2
|| '.'
|| gcc.segment3
|| '.'
|| gcc.segment4
|| '.'
|| gcc.segment5
|| '.'
|| gcc.segment6
|| '.'
|| gcc.segment7
|| '.'
|| gcc.segment8
"gl_accounts",
gjh.default_effective_date "gl_date",
gjh.NAME "gl_journal_name",
DECODE (gjh.status, 'P', 'Y', 'N') "posted_to_gl",
NULL resource_name,
NULL employee_num,
NULL vendor_num,
NULL actual_hours,
NULL effective_rate,
NULL ext_payroll_cost,
gjh.je_header_id source_id,
gjl.je_line_num source_line_id,
gcc.code_combination_id source_ccid,
NULL source_dist_id,
gjh.je_source journal_source,
gjb.NAME journal_batch_name,
gjh.je_category journal_category,
gjl.je_line_num journal_line_number,
gjh.je_header_id je_header_id,
gjb.je_batch_id je_batch_id,
gjl.je_line_num je_line_num,
NULL line_item_id,
NULL trx_party_id,
NULL trx_party_side_id,
gjb.org_id org_id,
gcc.segment4 LOB,
gcc.segment5 locations
FROM gl_je_batches gjb,
gl_je_headers gjh,
gl_je_lines gjl,
gl_code_combinations gcc
WHERE gjb.je_batch_id = gjh.je_batch_id
AND gjl.je_header_id = gjh.je_header_id
AND gcc.code_combination_id = gjl.code_combination_id
AND gjl.attribute10 IS NOT NULL
AND gjh.ledger_id = 2021
AND (gcc.segment2 LIKE '4%')
AND gjh.je_source IN ('Manual', 'Spreadsheet')
UNION
SELECT 'WOPOCHRG'
|| '-'
|| poh.po_header_id
|| '-'
|| pol.line_num
|| '-'
|| pod.distribution_num
"EUID",
NVL (pol.attribute3, poh.attribute3) "workorder_number",
'PO Charges' "Source",
NULL "transaction_type",
(SELECT DISTINCT msi.segment1
FROM apps.mtl_system_items_b msi
WHERE msi.inventory_item_id = pol.item_id AND ROWNUM = 1)
"item_num",
pol.item_description "item_name",
pol.unit_meas_lookup_code "UOM",
(pod.quantity_ordered - pod.quantity_cancelled) "actual_quantity",
pol.unit_price "actual_unit_cost",
(pol.unit_price * (pod.quantity_ordered - pod.quantity_cancelled))
"actual_ext_cost",
poh.segment1 "Transaction Source Number",
pol.line_num "Transaction line Number",
poh.segment1 || '-' || pol.line_num || '-' || pod.distribution_num
"transaction_num",
poh.approved_date "transaction_date",
(pol.unit_price * (pod.quantity_ordered - pod.quantity_cancelled))
"transacation_amount",
poh.segment1 "vendor_po",
TO_CHAR (poh.approved_date, 'MON-YY') "gl_period",
gcc.concatenated_segments "gl_accounts",
NULL "gl_date",
NULL "gl_journal_name",
NULL "posted_to_gl",
(SELECT pov1.vendor_name
FROM apps.po_vendors pov1
WHERE pov1.vendor_id = poh.vendor_id)
resource_name,
NULL employee_num,
(SELECT pov1.segment1
FROM apps.po_vendors pov1
WHERE pov1.vendor_id = poh.vendor_id)
vendor_num,
NULL actual_hours,
NULL effective_rate,
NULL ext_payroll_cost,
poh.po_header_id source_id,
pol.po_line_id source_line_id,
pod.code_combination_id source_ccid,
pod.po_distribution_id source_dist_id,
NULL journal_source,
NULL journal_batch_name,
NULL journal_category,
NULL journal_line_number,
NULL je_header_id,
NULL je_batch_id,
NULL je_line_num,
pol.item_id line_item_id,
vendor_id trx_party_id,
vendor_site_id trx_party_side_id,
poh.org_id org_id,
gcc.segment4 LOB,
gcc.segment5 locations
FROM apps.po_headers_all poh,
apps.po_lines_all pol,
apps.po_distributions_all pod,
apps.gl_code_combinations_kfv gcc,
po_line_locations_all plla
WHERE poh.po_header_id = pol.po_header_id
AND pol.po_header_id = pod.po_header_id
AND pol.po_line_id = pod.po_line_id
AND gcc.code_combination_id = pod.code_combination_id
AND pol.po_line_id = plla.po_line_id
AND poh.po_header_id = plla.po_header_id
AND plla.line_location_id = pod.line_location_id
AND pod.destination_type_code = 'EXPENSE'
AND (pol.attribute3 IS NOT NULL OR poh.attribute3 IS NOT NULL)
AND poh.authorization_status = 'APPROVED'
UNION ALL
SELECT 'WOLAB' || '-' || gjh.je_header_id || '-' || gjl.je_line_num "EUID",
gjl.attribute10 "workorder_number",
'Payroll Cost' "Source",
'Regular Time' "transaction_type",
NULL item_num,
gjl.description item_name,
NULL uom,
TO_NUMBER (gjl.attribute8) actual_quantity,
TO_NUMBER (gjl.attribute7) actual_unit_cost,
DECODE (gjl.accounted_cr,
NULL, gjl.accounted_dr,
0, gjl.accounted_dr,
gjl.accounted_cr)
actual_ext_cost,
gjh.description "Transaction Source Number",
gjl.je_line_num "Transaction line Number",
gjl.attribute9 "transaction_num",
gjh.default_effective_date "transaction_date",
DECODE (gjl.accounted_cr,
NULL, gjl.accounted_dr,
0, gjl.accounted_dr,
gjl.accounted_cr)
"transacation_amount",
NULL vendor_po,
gjh.period_name "gl_period",
gcc.segment1
|| '.'
|| gcc.segment2
|| '.'
|| gcc.segment3
|| '.'
|| gcc.segment4
|| '.'
|| gcc.segment5
|| '.'
|| gcc.segment6
|| '.'
|| gcc.segment7
|| '.'
|| gcc.segment8
"gl_accounts",
gjh.default_effective_date "gl_date",
gjh.NAME "gl_journal_name",
DECODE (gjh.status, 'P', 'Y', 'N') "posted_to_gl",
gjl.attribute6 resource_name,
gjl.attribute5 employee_num,
NULL vendor_num,
gjl.attribute8 actual_hours,
gjl.attribute7 effective_rate,
DECODE (gjl.accounted_cr,
NULL, gjl.accounted_dr,
0, gjl.accounted_dr,
gjl.accounted_cr)
ext_payroll_cost,
gjh.je_header_id source_id,
gjl.je_line_num source_line_id,
gcc.code_combination_id source_ccid,
NULL source_dist_id,
gjh.je_source journal_source,
gjb.NAME journal_batch_name,
gjh.je_category journal_category,
gjl.je_line_num journal_line_number,
gjh.je_header_id je_header_id,
gjb.je_batch_id je_batch_id,
gjl.je_line_num je_line_num,
NULL line_item_id,
NULL trx_party_id,
NULL trx_party_side_id,
gjb.org_id org_id,
gcc.segment4 LOB,
gcc.segment5 locations
FROM gl_je_batches gjb,
gl_je_headers gjh,
gl_je_lines gjl,
gl_code_combinations gcc
WHERE 1 = 1
AND gjb.je_batch_id = gjh.je_batch_id
AND gjl.je_header_id = gjh.je_header_id
AND gcc.code_combination_id = gjl.code_combination_id
AND gjl.attribute10 IS NOT NULL
AND gjh.ledger_id = 2021
AND gjh.je_source IN ('Payroll')
'WOAPINV'
|| '-'
|| ai.invoice_id
|| '-'
|| ail.line_number
|| '-'
|| aid.invoice_distribution_id
euid,
NVL (ail.attribute12, ai.attribute2) workorder_number,
DECODE (ai.invoice_type_lookup_code,
'EXPENSE REPORT', 'iExpense Charges',
'AP Direct Charges')
SOURCE,
DECODE (ai.invoice_type_lookup_code,
'STANDARD', 'Standard Invoice',
'CREDIT', 'Credit Memo',
'DEBIT', 'Debit Memo',
'EXPENSE REPORT', NULL,
'Standard Invoice')
transaction_type,
(SELECT segment1
FROM apps.mtl_system_items_b msi
WHERE msi.inventory_item_id = ail.inventory_item_id
AND msi.organization_id =
(SELECT mp.organization_id
FROM mtl_parameters mp,
ont.oe_sys_parameters_all param
WHERE 1 = 1
AND parameter_code =
'MASTER_ORGANIZATION_ID'
AND mp.organization_id =
param.parameter_value
AND ai.org_id = param.org_id))
item_num,
NVL (ail.item_description, ail.description) item_name,
unit_meas_lookup_code uom,
aid.quantity_invoiced actual_qty,
aid.unit_price actual_unit_cost,
(aid.quantity_invoiced * aid.unit_price) actual_ext_cost,
ai.invoice_num "Transaction Source Number",
ail.line_number "Transaction line Number",
ai.invoice_num
|| '-'
|| ail.line_number
|| '-'
|| aid.distribution_line_number
transaction_num,
ai.invoice_date transaction_date,
aid.amount transaction_amount,
NULL vendor_po,
gjh.period_name gl_period,
concatenated_segments gl_accounts,
ai.gl_date,
gjh.NAME gl_journal_name,
DECODE (gjh.status, 'P', 'Y', 'N') posted_to_gl,
pv.vendor_name resource_name,
NULL employee_num,
pv.segment1 vendor_num,
NULL actual_hours,
NULL effective_rate,
NULL ext_payroll_cost,
ai.invoice_id source_id,
ail.line_number source_line_id,
aid.dist_code_combination_id source_ccid,
aid.invoice_distribution_id source_dist_id,
je_source journal_source,
gjb.NAME journal_batch_name,
je_category journal_category,
gjl.je_line_num journal_line_number,
gjh.je_header_id je_header_id,
gjb.je_batch_id je_batch_id,
gjl.je_line_num je_line_num,
NULL line_item_id,
ai.vendor_id trx_party_id,
ai.vendor_site_id trx_party_site_id,
ai.org_id org_id,
gcc.segment4 LOB,
gcc.segment5 locations
FROM apps.ap_invoices_all ai,
apps.ap_invoice_lines_all ail,
apps.ap_invoice_distributions_all aid,
apps.ap_lookup_codes alc1,
apps.ap_lookup_codes alc2,
apps.po_vendors pv,
apps.ap_lookup_codes alc3,
apps.gl_code_combinations_kfv gcc,
gl_je_batches gjb,
gl_je_headers gjh,
gl_je_lines gjl,
gl_import_references gir,
xla_ae_headers xah,
xla_ae_lines xal,
xla_events xe,
xla_transaction_entities_upg xte,
xla_distribution_links xdl
WHERE ai.invoice_id = ail.invoice_id
AND aid.invoice_id = ail.invoice_id
AND aid.invoice_line_number = ail.line_number
AND aid.dist_code_combination_id = gcc.code_combination_id
AND ail.po_header_id IS NULL
AND ail.rcv_transaction_id IS NULL
AND (ail.attribute12 IS NOT NULL OR ai.attribute2 IS NOT NULL)
AND ai.vendor_id = pv.vendor_id
AND ai.invoice_type_lookup_code != 'EXPENSE REPORT'
AND alc1.lookup_code(+) = ai.invoice_type_lookup_code
AND alc1.lookup_type(+) = 'INVOICE TYPE'
AND alc2.lookup_code(+) = ai.payment_status_flag
AND alc2.lookup_type(+) = 'INVOICE PAYMENT STATUS'
AND alc3.lookup_type(+) = 'NLS TRANSLATION'
AND alc3.lookup_code(+) = DECODE (aid.match_status_flag,
NULL,
'NEVER APPROVED',
'N',
'NEVER APPROVED',
'T',
'NEEDS REAPPROVAL',
'A',
'APPROVED',
'S',
'NEVER APPROVED')
AND alc3.displayed_field = 'Validated'
AND gjb.je_batch_id = gjh.je_batch_id
AND gjh.je_header_id = gjl.je_header_id
AND gjb.je_batch_id = gir.je_batch_id
AND gjh.je_header_id = gir.je_header_id
AND gjl.je_line_num = gir.je_line_num
AND gir.reference_5 = TO_CHAR (xah.entity_id)
AND gir.reference_6 = TO_CHAR (xah.event_id)
AND gir.reference_7 = xah.ae_header_id
AND gir.reference_8 = xal.ae_line_num
AND xah.entity_id = xte.entity_id
AND xah.event_id = xe.event_id
AND xe.event_id = xdl.event_id
AND xe.event_id = aid.accounting_event_id
AND xah.ae_header_id = xal.ae_header_id
AND source_id_int_1 = ai.invoice_id
AND accounting_class_code != 'LIABILITY'
AND rounding_class_code != 'LIABILITY'
AND source_distribution_type = 'AP_INV_DIST'
AND gjh.status = 'P'
AND source_distribution_id_num_1 = aid.invoice_distribution_id
AND xdl.ae_header_id = xah.ae_header_id
AND xdl.ae_line_num = xal.ae_line_num
AND transaction_number = invoice_num
UNION
SELECT DISTINCT
'WOIEXP'
|| '-'
|| ai.invoice_id
|| '-'
|| ail.line_number
|| '-'
|| aid.invoice_distribution_id
euid,
NVL (ail.attribute12, ai.attribute2) workorder_number,
DECODE (ai.invoice_type_lookup_code,
'EXPENSE REPORT', 'iExpense Charges',
'AP Direct Charges')
SOURCE,
DECODE (ai.invoice_type_lookup_code,
'STANDARD', 'Standard Invoice',
'CREDIT', 'Credit Memo',
'DEBIT', 'Debit Memo',
'EXPENSE REPORT', NULL,
'Standard Invoice')
transaction_type,
(SELECT segment1
FROM apps.mtl_system_items_b msi
WHERE msi.inventory_item_id = ail.inventory_item_id
AND msi.organization_id =
(SELECT mp.organization_id
FROM mtl_parameters mp,
ont.oe_sys_parameters_all param
WHERE 1 = 1
AND parameter_code =
'MASTER_ORGANIZATION_ID'
AND mp.organization_id =
param.parameter_value
AND ai.org_id = param.org_id))
item_num,
NVL (ail.item_description, ail.description) item_name,
unit_meas_lookup_code uom,
aid.quantity_invoiced actual_qty,
aid.unit_price actual_unit_cost,
(aid.quantity_invoiced * aid.unit_price) actual_ext_cost,
ai.invoice_num "Transaction Source Number",
ail.line_number "Transaction line Number",
ai.invoice_num
|| '-'
|| ail.line_number
|| '-'
|| aid.distribution_line_number
transaction_num,
ai.invoice_date transaction_date,
aid.amount transaction_amount,
NULL vendor_po,
gjh.period_name gl_period,
concatenated_segments gl_accounts,
ai.gl_date,
gjh.NAME gl_journal_name,
DECODE (gjh.status, 'P', 'Y', 'N') posted_to_gl,
(SELECT DISTINCT full_name
FROM apps.per_all_people_f papf
WHERE papf.person_id = hz.person_identifier
AND TRUNC (SYSDATE) BETWEEN TRUNC (
papf.effective_start_date
)
AND TRUNC (
papf.effective_end_date
))
resource_name,
(SELECT DISTINCT employee_number
FROM apps.per_all_people_f papf
WHERE papf.person_id = hz.person_identifier
AND TRUNC (SYSDATE) BETWEEN TRUNC (
papf.effective_start_date
)
AND TRUNC (
papf.effective_end_date
))
employee_num,
pv.segment1 vendor_num,
NULL actual_hours,
NULL effective_rate,
NULL ext_payroll_cost,
ai.invoice_id source_id,
ail.line_number source_line_id,
aid.dist_code_combination_id source_ccid,
aid.invoice_distribution_id source_dist_id,
je_source journal_source,
gjb.NAME journal_batch_name,
je_category journal_category,
gjl.je_line_num journal_line_number,
gjh.je_header_id je_header_id,
gjb.je_batch_id je_batch_id,
gjl.je_line_num je_line_num,
NULL line_item_id,
ai.vendor_id trx_party_id,
ai.vendor_site_id trx_party_site_id,
ai.org_id org_id,
gcc.segment4 LOB,
gcc.segment5 locations
FROM apps.ap_invoices_all ai,
apps.ap_invoice_lines_all ail,
apps.ap_invoice_distributions_all aid,
apps.ap_lookup_codes alc1,
apps.ap_lookup_codes alc2,
apps.ap_lookup_codes alc3,
apps.po_vendors pv,
apps.gl_code_combinations_kfv gcc,
apps.hz_parties hz,
gl_je_batches gjb,
gl_je_headers gjh,
gl_je_lines gjl,
gl_import_references gir,
xla_ae_headers xah,
xla_ae_lines xal,
xla_events xe,
xla_transaction_entities_upg xte,
xla_distribution_links xdl
WHERE ai.invoice_id = ail.invoice_id
AND aid.invoice_id = ail.invoice_id
AND aid.dist_code_combination_id = gcc.code_combination_id
AND aid.invoice_line_number = ail.line_number
AND hz.party_id = ai.party_id
AND ail.po_header_id IS NULL
AND ail.rcv_transaction_id IS NULL
AND ail.attribute12 IS NOT NULL
AND ai.vendor_id = pv.vendor_id
AND ai.invoice_type_lookup_code = 'EXPENSE REPORT'
AND alc1.lookup_code(+) = ai.invoice_type_lookup_code
AND alc1.lookup_type(+) = 'INVOICE TYPE'
AND alc2.lookup_code(+) = ai.payment_status_flag
AND alc2.lookup_type(+) = 'INVOICE PAYMENT STATUS'
AND alc3.lookup_type(+) = 'NLS TRANSLATION'
AND alc3.lookup_code(+) = DECODE (aid.match_status_flag,
NULL,
'NEVER APPROVED',
'N',
'NEVER APPROVED',
'T',
'NEEDS REAPPROVAL',
'A',
'APPROVED',
'S',
'NEVER APPROVED')
AND alc3.displayed_field = 'Validated'
AND gjb.je_batch_id = gjh.je_batch_id
AND gjh.je_header_id = gjl.je_header_id
AND gjb.je_batch_id = gir.je_batch_id
AND gjh.je_header_id = gir.je_header_id
AND gjl.je_line_num = gir.je_line_num
AND gir.reference_5 = TO_CHAR (xah.entity_id)
AND gir.reference_6 = TO_CHAR (xah.event_id)
AND gir.reference_7 = xah.ae_header_id
AND gir.reference_8 = xal.ae_line_num
AND xah.entity_id = xte.entity_id
AND xah.event_id = xe.event_id
AND xe.event_id = xdl.event_id
AND xe.event_id = aid.accounting_event_id
AND xah.ae_header_id = xal.ae_header_id
AND source_id_int_1 = ai.invoice_id
AND source_distribution_type = 'AP_INV_DIST'
AND accounting_class_code != 'LIABILITY'
AND rounding_class_code != 'LIABILITY'
AND gjh.status = 'P'
AND source_distribution_id_num_1 = aid.invoice_distribution_id
AND xdl.ae_header_id = xah.ae_header_id
AND xdl.ae_line_num = xal.ae_line_num
AND transaction_number = invoice_num
UNION
SELECT 'WOARREV'
|| '-'
|| ct.customer_trx_id
|| '-'
|| ctl.customer_trx_line_id
|| '-'
|| gd.cust_trx_line_gl_dist_id
euid,
NVL (NVL (ct.interface_header_attribute1, ct.attribute10),
ctl.interface_line_attribute1)
workorder_number,
'AR Transactions' SOURCE,
DECODE (ctt.TYPE,
'INV',
'Invoice',
'CM',
'Credit Memo')
transaction_type,
(SELECT segment1
FROM apps.mtl_system_items_b msi
WHERE msi.inventory_item_id = ctl.inventory_item_id
AND msi.organization_id =
(SELECT mp.organization_id
FROM mtl_parameters mp,
ont.oe_sys_parameters_all param
WHERE 1 = 1
AND parameter_code =
'MASTER_ORGANIZATION_ID'
AND mp.organization_id =
param.parameter_value
AND ct.org_id = param.org_id))
item_num,
NVL (
(SELECT description
FROM apps.mtl_system_items_b msi1
WHERE msi1.inventory_item_id = ctl.inventory_item_id
AND msi1.organization_id =
(SELECT mp.organization_id
FROM mtl_parameters mp,
ont.oe_sys_parameters_all param
WHERE 1 = 1
AND parameter_code =
'MASTER_ORGANIZATION_ID'
AND mp.organization_id =
param.parameter_value
AND ct.org_id = param.org_id)),
ctl.description
)
item_name,
ctl.uom_code uom,
ctl.quantity_invoiced actual_quantity,
ctl.unit_selling_price actual_unit_price,
(ctl.quantity_invoiced * ctl.unit_selling_price) actual_ext_price,
ct.trx_number "Transaction Source Number",
ctl.line_number "Transaction line Number",
ct.trx_number
|| '-'
|| ctl.line_number
|| '-'
|| gd.cust_trx_line_gl_dist_id
transaction_num,
TRUNC (ct.trx_date) transaction_date,
gd.amount transaction_amount,
NULL vendor_po,
gjh.period_name gl_period,
gcc.concatenated_segments gl_accounts,
gd.gl_date,
gjh.NAME gl_journal_name,
DECODE (gjh.status, 'P', 'Y', 'N') posted_to_gl,
NULL resource_name,
NULL employee_num,
NULL vendor_num,
NULL acutal_hours,
NULL effective_rate,
NULL ext_payroll_cost,
ct.customer_trx_id source_id,
ctl.customer_trx_line_id source_line_id,
gd.code_combination_id source_ccid,
gd.cust_trx_line_gl_dist_id source_dist_id,
je_source journal_source,
gjb.NAME journal_batch_name,
je_category journal_category,
gjl.je_line_num journal_line_number,
gjh.je_header_id je_header_id,
gjb.je_batch_id je_batch_id,
gjl.je_line_num je_line_num,
inventory_item_id line_item_id,
rac_bill_party.party_id trx_party_id,
raa_bill_ps.party_site_id trx_party_site_id,
ct.org_id org_id,
gcc.segment4 LOB,
gcc.segment5 locations
FROM apps.ra_cust_trx_line_gl_dist_all gd,
apps.ra_customer_trx_all ct,
apps.ra_customer_trx_lines_all ctl,
apps.ra_cust_trx_types_all ctt,
apps.gl_code_combinations_kfv gcc,
apps.hz_cust_accounts_all rac_bill,
apps.hz_parties rac_bill_party,
apps.hz_cust_site_uses_all su_bill,
apps.hz_cust_acct_sites_all raa_bill,
apps.hz_party_sites raa_bill_ps,
gl_je_batches gjb,
gl_je_headers gjh,
gl_je_lines gjl,
gl_import_references gir,
xla_ae_headers xah,
xla_ae_lines xal,
xla_distribution_links xdl
WHERE 1 = 1
AND ct.customer_trx_id = gd.customer_trx_id
AND ctl.customer_trx_line_id = gd.customer_trx_line_id
AND 'REV' = gd.account_class
AND ctl.line_type != 'TAX'
AND ct.customer_trx_id = ctl.customer_trx_id --AND ct.trx_number = '10060'
AND ct.complete_flag = 'Y'
AND ctt.org_id = ct.org_id
AND ct.cust_trx_type_id = ctt.cust_trx_type_id
AND ctt.TYPE <> 'BR'
AND ct.bill_to_customer_id = rac_bill.cust_account_id
AND rac_bill.party_id = rac_bill_party.party_id
AND ct.bill_to_site_use_id = su_bill.site_use_id
AND su_bill.cust_acct_site_id = raa_bill.cust_acct_site_id
AND raa_bill.party_site_id = raa_bill_ps.party_site_id
AND gd.gl_posted_date IS NOT NULL
AND gd.code_combination_id = gcc.code_combination_id
AND xdl.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
AND xdl.source_distribution_id_num_1 = gd.cust_trx_line_gl_dist_id
AND gjb.je_batch_id = gjh.je_batch_id
AND gjh.je_header_id = gjl.je_header_id
AND gjb.je_batch_id = gir.je_batch_id
AND gjh.je_header_id = gir.je_header_id
AND gjl.je_line_num = gir.je_line_num
AND gir.reference_5 = TO_CHAR (xah.entity_id)
AND gir.reference_6 = TO_CHAR (xah.event_id)
AND gir.reference_7 = xah.ae_header_id
AND gir.reference_8 = xal.ae_line_num
AND xah.ae_header_id = xal.ae_header_id
AND xdl.ae_header_id = xah.ae_header_id
AND xdl.ae_line_num = xal.ae_line_num
AND gjh.status = 'P'
AND xdl.event_id = gd.event_id
AND UPPER(NVL (NVL (ct.interface_header_attribute1, ct.attribute10),
ctl.interface_line_attribute1)) LIKE
'WO%'
AND ct.org_id = <ORG_ID>
UNION
SELECT DISTINCT
'WOPORCPT'
|| '-'
|| rt.transaction_id
|| '-'
|| rsl.line_num
|| '-'
|| rt.po_distribution_id
euid,
NVL (pl.attribute3, ph.attribute3) workorder_number,
'PO Receipts' SOURCE,
DECODE (transaction_type,
'RECEIVE',
'Receive',
'RETURN TO VENDOR',
'Return',
'CORRECT',
'Correction')
transaction_type,
(SELECT segment1
FROM apps.mtl_system_items_b
WHERE inventory_item_id = rsl.item_id
AND rsl.to_organization_id = organization_id)
item_num,
NVL (
(SELECT description
FROM apps.mtl_system_items_b
WHERE inventory_item_id = rsl.item_id
AND rsl.to_organization_id = organization_id),
rsl.item_description
)
item_name,
rsl.unit_of_measure uom,
rt.quantity actual_quantity,
po_unit_price actual_unit_price,
(rt.quantity * po_unit_price) actual_ext_price,
rsh.receipt_num "Transaction Source Number",
rsl.line_num "Transaction line Number",
rsh.receipt_num || '-' || rsl.line_num || '-' || pd.distribution_num
transaction_num,
TRUNC (rt.transaction_date) transaction_date,
(rt.quantity * po_unit_price) transaction_amt,
ph.segment1 vendor_po,
jh.period_name gl_period,
concatenated_segments gl_accounts,
rrsl.accounting_date gl_date,
jh.NAME gl_journal_name,
DECODE (NVL (jb.status, 'U'), 'P', 'Y', 'N') posted_to_gl,
pv.vendor_name resource_name,
NULL employee_num,
pv.segment1 vendor_num,
NULL acutal_hours,
NULL effective_rate,
NULL ext_payroll_cost,
rt.transaction_id source_id,
rsl.shipment_line_id source_line_id,
pd.code_combination_id source_ccid,
xdl.source_distribution_id_num_1 source_dist_id,
jh.je_source journal_source,
jb.NAME journal_batch_name,
jh.je_category journal_category,
jl.je_line_num journal_line_number,
jh.je_header_id je_header_id,
jh.je_batch_id je_batch_id,
jl.je_line_num je_line_num,
rsl.item_id line_item_id,
ph.vendor_id trx_party_id,
ph.vendor_site_id trx_party_site_id,
ph.org_id org_id,
gcc.segment4 LOB,
gcc.segment5 locations
FROM apps.rcv_transactions rt,
apps.rcv_shipment_headers rsh,
apps.rcv_shipment_lines rsl,
apps.po_headers_all ph,
apps.po_lines_all pl,
apps.po_distributions_all pd,
apps.po_vendors pv,
apps.po_vendor_sites_all pvs,
apps.org_organization_definitions ood,
apps.gl_code_combinations_kfv gcc,
apps.rcv_receiving_sub_ledger rrsl,
apps.xla_distribution_links xdl,
apps.gl_import_references gir,
apps.gl_je_headers jh,
apps.gl_je_lines jl,
apps.gl_je_batches jb
WHERE 1 = 1
AND rt.source_document_code = 'PO'
AND rt.destination_type_code = 'RECEIVING'
AND rsl.destination_type_code = 'EXPENSE'
AND transaction_type IN ('RECEIVE', 'RETURN TO VENDOR', 'CORRECT')
AND rt.shipment_header_id = rsh.shipment_header_id
AND rsl.shipment_header_id = rsh.shipment_header_id
AND rsl.shipment_line_id = rt.shipment_line_id
AND ph.po_header_id = rt.po_header_id
AND pl.po_line_id = rt.po_line_id
AND pl.po_line_id = pd.po_line_id
AND ph.po_header_id = pl.po_header_id
AND pd.po_distribution_id = rsl.po_distribution_id
AND pd.code_combination_id = gcc.code_combination_id
AND rsl.to_organization_id = ood.organization_id
AND rrsl.rcv_transaction_id = rt.transaction_id
AND gir.reference_7 = xdl.ae_header_id
AND gir.reference_6 = TO_CHAR (xdl.event_id)
AND gir.reference_8 = xdl.ae_line_num
AND jh.je_header_id = jl.je_header_id
AND jl.je_header_id = gir.je_header_id
AND jl.je_line_num = gir.je_line_num
AND jb.je_batch_id = jh.je_batch_id
AND (ph.attribute3 IS NOT NULL OR pl.attribute3 IS NOT NULL)
AND ph.vendor_id = pv.vendor_id
AND pv.vendor_id = pvs.vendor_id
AND rrsl.accounting_line_type(+) = 'Accrual'
AND xdl.source_distribution_id_num_1 = rrsl.rcv_sub_ledger_id
AND xdl.source_distribution_type = 'RCV_RECEIVING_SUB_LEDGER'
UNION
SELECT 'WOMATINV'
|| '-'
|| mmt.transaction_id
|| '-'
|| mta.reference_account
euid,
mil.segment3 workorder_number,
'Inventory Cost' SOURCE,
flv.description transaction_type,
msi.segment1 item_num,
msi.description item_name,
mmt.transaction_uom uom,
ABS (mmt.transaction_quantity) actual_quantity,
rate_or_amount actual_unit_cost,
ABS (mmt.transaction_quantity) * rate_or_amount actual_ext_cost,
TO_CHAR (mmt.transaction_id) "Transaction Source Number",
NULL "Transaction line Number",
TO_CHAR (mmt.transaction_id) || '-' || mta.reference_account
transaction_num,
mmt.transaction_date transaction_date,
ABS (mta.base_transaction_value) transacation_amount,
pha.segment1 vendor_po,
jh.period_name gl_period,
concatenated_segments gl_accounts,
mmt.transaction_date gl_date,
jh.NAME gl_journal_name,
DECODE (NVL (jb.status, 'U'), 'P', 'Y', 'N') posted_to_gl,
(CASE
WHEN (mil.segment1 < '1000000')
THEN
(SELECT full_name
FROM per_all_people_f
WHERE employee_number = TO_CHAR (mil.segment1)
AND TRUNC (SYSDATE) BETWEEN effective_start_date
AND effective_end_date)
WHEN (mil.segment1 BETWEEN '1000000' AND '2000000')
THEN
(SELECT vendor_name
FROM po_vendors
WHERE segment1 = mil.segment1)
WHEN (mil.segment1 > '2000000')
THEN
(SELECT full_name
FROM per_all_people_f
WHERE npw_number = mil.segment1
AND TRUNC (SYSDATE) BETWEEN effective_start_date
AND effective_end_date)
ELSE
NULL
END)
AS resource_name,
(CASE
WHEN (mil.segment1 < '1000000')
THEN
(SELECT employee_number
FROM per_all_people_f
WHERE employee_number = mil.segment1
AND TRUNC (SYSDATE) BETWEEN effective_start_date
AND effective_end_date)
WHEN (mil.segment1 > '2000000')
THEN
(SELECT npw_number
FROM per_all_people_f
WHERE npw_number = mil.segment1
AND TRUNC (SYSDATE) BETWEEN effective_start_date
AND effective_end_date)
ELSE
NULL
END)
AS employee_num,
(CASE
WHEN (mil.segment1 BETWEEN '1000000' AND '2000000')
THEN
(SELECT segment1
FROM po_vendors
WHERE segment1 = mil.segment1)
ELSE
NULL
END)
AS vendor_num,
NULL actual_hours,
NULL effective_rate,
NULL ext_payroll_cost,
mmt.transaction_id source_id,
mil.inventory_location_id source_line_id,
gcc.code_combination_id source_ccid,
mmt.organization_id source_dist_id,
jh.je_source journal_source,
jb.NAME journal_batch_name,
jh.je_category journal_category,
jl.je_line_num journal_line_number,
jh.je_header_id je_header_id,
jb.je_batch_id je_batch_id,
jl.je_line_num je_line_num,
msi.inventory_item_id line_item_id,
pha.vendor_id trx_party_id,
pha.vendor_site_id trx_party_side_id,
ood.operating_unit org_id,
gcc.segment4 LOB,
gcc.segment5 locations
FROM mtl_material_transactions mmt,
mtl_item_locations mil,
mtl_transaction_types mtt,
mtl_txn_source_types mtst,
mtl_system_items_b msi,
po_headers_all pha,
mtl_transaction_accounts mta,
gl_code_combinations_kfv gcc,
-- mtl_generic_dispositions mgd,
xla_distribution_links xdl,
gl_import_references gir,
gl_je_headers jh,
gl_je_lines jl,
gl_je_batches jb,
fnd_lookup_values flv,
org_organization_definitions ood
WHERE 1 = 1
AND mmt.transaction_type_id = mtt.transaction_type_id
AND mmt.transaction_source_type_id = mtst.transaction_source_type_id
AND msi.inventory_item_id = mmt.inventory_item_id
AND msi.organization_id = mmt.organization_id
AND mmt.transaction_source_id = pha.po_header_id(+)
AND mil.inventory_location_id = mmt.locator_id
AND mta.transaction_id = mmt.transaction_id
AND gcc.code_combination_id = mta.reference_account --
AND mgd.disposition_id = mmt.transaction_source_id --
AND flv.meaning = mgd.segment1
AND NVL (mil.segment3, 'XxX') <> '000' --
AND mtst.transaction_source_type_name = 'Account alias'
AND mta.cost_element_id IS NULL
AND mta.transaction_date IS NOT NULL
AND xdl.source_distribution_id_num_1 = mta.inv_sub_ledger_id
AND xdl.source_distribution_type = 'MTL_TRANSACTION_ACCOUNTS'
AND gir.reference_7 = TO_CHAR (xdl.ae_header_id)
AND gir.reference_6 = TO_CHAR (xdl.event_id)
AND gir.reference_8 = xdl.ae_line_num
AND jh.je_header_id = jl.je_header_id
AND jl.je_header_id = gir.je_header_id
AND jl.je_line_num = gir.je_line_num
AND jb.je_batch_id = jh.je_batch_id
AND flv.lookup_type = 'XXMTZ_SBL_INV_WO_ISSUE_RETN'
AND flv.meaning = mmt.attribute2
AND mmt.organization_id = ood.organization_id
AND flv.enabled_flag = 'Y'
AND flv.tag = ood.operating_unit
AND flv.description = 'Return'
UNION
SELECT 'WOMATINV'
|| '-'
|| mmt.transaction_id
|| '-'
|| mta.reference_account
euid,
mil.segment3 workorder_number,
'Inventory Cost' SOURCE,
flv.description transaction_type,
msi.segment1 item_num,
msi.description item_name,
mmt.transaction_uom uom,
ABS (mmt.transaction_quantity) actual_quantity,
rate_or_amount actual_unit_cost,
ABS (mmt.transaction_quantity) * rate_or_amount actual_ext_cost,
TO_CHAR (mmt.transaction_id) "Transaction Source Number",
NULL "Transaction line Number",
TO_CHAR (mmt.transaction_id) || '-' || mta.reference_account
transaction_num,
mmt.transaction_date transaction_date,
ABS (mta.base_transaction_value) transacation_amount,
pha.segment1 vendor_po,
jh.period_name gl_period,
concatenated_segments gl_accounts,
mmt.transaction_date gl_date,
jh.NAME gl_journal_name,
DECODE (NVL (jb.status, 'U'), 'P', 'Y', 'N') posted_to_gl,
(CASE
WHEN (TO_NUMBER (mil.segment1) < TO_NUMBER ('1000000'))
THEN
(SELECT full_name
FROM per_all_people_f
WHERE employee_number = TO_CHAR (mil.segment1)
AND TRUNC (SYSDATE) BETWEEN effective_start_date
AND effective_end_date)
WHEN (TO_NUMBER (mil.segment1) BETWEEN TO_NUMBER ('1000000')
AND TO_NUMBER ('2000000'))
THEN
(SELECT vendor_name
FROM po_vendors
WHERE segment1 = mil.segment1)
WHEN (TO_NUMBER (mil.segment1) > TO_NUMBER ('2000000'))
THEN
(SELECT full_name
FROM per_all_people_f
WHERE npw_number = mil.segment1
AND TRUNC (SYSDATE) BETWEEN effective_start_date
AND effective_end_date)
ELSE
NULL
END)
AS resource_name,
(CASE
WHEN (TO_NUMBER (mil.segment1) < TO_NUMBER ('1000000'))
THEN
(SELECT employee_number
FROM per_all_people_f
WHERE employee_number = mil.segment1
AND TRUNC (SYSDATE) BETWEEN effective_start_date
AND effective_end_date)
WHEN (TO_NUMBER (mil.segment1) > TO_NUMBER ('2000000'))
THEN
(SELECT npw_number
FROM per_all_people_f
WHERE npw_number = mil.segment1
AND TRUNC (SYSDATE) BETWEEN effective_start_date
AND effective_end_date)
ELSE
NULL
END)
AS employee_num,
(CASE
WHEN (TO_NUMBER (mil.segment1) BETWEEN TO_NUMBER ('1000000')
AND TO_NUMBER ('2000000'))
THEN
(SELECT segment1
FROM po_vendors
WHERE segment1 = mil.segment1)
ELSE
NULL
END)
AS vendor_num,
NULL actual_hours,
NULL effective_rate,
NULL ext_payroll_cost,
mmt.transaction_id source_id,
mil.inventory_location_id source_line_id,
gcc.code_combination_id source_ccid,
mmt.organization_id source_dist_id,
jh.je_source journal_source,
jb.NAME journal_batch_name,
jh.je_category journal_category,
jl.je_line_num journal_line_number,
jh.je_header_id je_header_id,
jb.je_batch_id je_batch_id,
jl.je_line_num je_line_num,
msi.inventory_item_id line_item_id,
pha.vendor_id trx_party_id,
pha.vendor_site_id trx_party_side_id,
ood.operating_unit org_id,
gcc.segment4 LOB,
gcc.segment5 locations
FROM mtl_material_transactions mmt,
mtl_item_locations mil,
mtl_transaction_types mtt,
mtl_txn_source_types mtst,
mtl_system_items_b msi,
po_headers_all pha,
mtl_transaction_accounts mta,
gl_code_combinations_kfv gcc,
-- mtl_generic_dispositions mgd,
xla_distribution_links xdl,
gl_import_references gir,
gl_je_headers jh,
gl_je_lines jl,
gl_je_batches jb,
fnd_lookup_values flv,
org_organization_definitions ood
WHERE 1 = 1
AND mmt.transaction_type_id = mtt.transaction_type_id
AND mmt.transaction_source_type_id = mtst.transaction_source_type_id
AND msi.inventory_item_id = mmt.inventory_item_id
AND msi.organization_id = mmt.organization_id
AND mmt.transaction_source_id = pha.po_header_id(+)
AND mil.inventory_location_id = mmt.locator_id
AND mta.transaction_id = mmt.transaction_id
AND gcc.code_combination_id = mta.reference_account --
AND mgd.disposition_id = mmt.transaction_source_id --
AND flv.meaning = mgd.segment1
AND NVL (mil.segment3, 'XxX') <> '000' --
AND mtst.transaction_source_type_name = 'Account alias'
AND mta.cost_element_id IS NULL
AND mta.transaction_date IS NOT NULL
AND xdl.source_distribution_id_num_1 = mta.inv_sub_ledger_id
AND xdl.source_distribution_type = 'MTL_TRANSACTION_ACCOUNTS'
AND gir.reference_7 = TO_CHAR (xdl.ae_header_id)
AND gir.reference_6 = TO_CHAR (xdl.event_id)
AND gir.reference_8 = xdl.ae_line_num
AND jh.je_header_id = jl.je_header_id
AND jl.je_header_id = gir.je_header_id
AND jl.je_line_num = gir.je_line_num
AND jb.je_batch_id = jh.je_batch_id
AND flv.lookup_type = 'XXMTZ_SBL_INV_WO_ISSUE_RETN'
AND flv.meaning = mmt.attribute2
AND mmt.organization_id = ood.organization_id
AND flv.enabled_flag = 'Y'
AND flv.tag = ood.operating_unit
AND flv.description = 'Issuance'
UNION
SELECT 'WOGLMAN'
|| '-'
|| gjh.je_batch_id
|| '-'
|| gjl.je_line_num
|| '-'
|| gjh.je_header_id
"EUID",
gjl.attribute10 "workorder_number",
'Miscellaneous Cost' "Source",
DECODE (gjl.accounted_dr, NULL, 'Credit', 'Debit')
"transaction_type",
NULL item_num,
gjl.description item_name,
NULL uom,
NULL actual_quantity,
NULL actual_unit_cost,
NVL (gjl.accounted_cr, gjl.accounted_dr) actual_ext_cost,
gjh.description "Transaction Source Number",
gjl.je_line_num "Transaction line Number",
SUBSTR (gjh.NAME, 1, 18)
|| '-'
|| gjb.je_batch_id
|| '-'
|| gjl.je_line_num
"transaction_num",
gjh.date_created "transaction_date",
NVL (gjl.accounted_cr, gjl.accounted_dr) "transacation_amount",
NULL vendor_po,
gjh.period_name "gl_period",
gcc.segment1
|| '.'
|| gcc.segment2
|| '.'
|| gcc.segment3
|| '.'
|| gcc.segment4
|| '.'
|| gcc.segment5
|| '.'
|| gcc.segment6
|| '.'
|| gcc.segment7
|| '.'
|| gcc.segment8
"gl_accounts",
gjh.default_effective_date "gl_date",
gjh.NAME "gl_journal_name",
DECODE (gjh.status, 'P', 'Y', 'N') "posted_to_gl",
NULL resource_name,
NULL employee_num,
NULL vendor_num,
NULL actual_hours,
NULL effective_rate,
NULL ext_payroll_cost,
gjh.je_header_id source_id,
gjl.je_line_num source_line_id,
gcc.code_combination_id source_ccid,
NULL source_dist_id,
gjh.je_source journal_source,
gjb.NAME journal_batch_name,
gjh.je_category journal_category,
gjl.je_line_num journal_line_number,
gjh.je_header_id je_header_id,
gjb.je_batch_id je_batch_id,
gjl.je_line_num je_line_num,
NULL line_item_id,
NULL trx_party_id,
NULL trx_party_side_id,
gjb.org_id org_id,
gcc.segment4 LOB,
gcc.segment5 locations
FROM gl_je_batches gjb,
gl_je_headers gjh,
gl_je_lines gjl,
gl_code_combinations gcc
WHERE gjb.je_batch_id = gjh.je_batch_id
AND gjl.je_header_id = gjh.je_header_id
AND gcc.code_combination_id = gjl.code_combination_id
AND gjl.attribute10 IS NOT NULL
AND gjh.ledger_id = 2021
AND (gcc.segment2 LIKE '5%')
AND gjh.je_source IN ('Manual', 'Spreadsheet')
UNION
SELECT 'WOGLMAN'
|| '-'
|| gjh.je_batch_id
|| '-'
|| gjl.je_line_num
|| '-'
|| gjh.je_header_id
"EUID",
gjl.attribute10 "workorder_number",
'Miscellaneous Revenue' "Source",
DECODE (gjl.accounted_dr, NULL, 'Credit', 'Debit')
"transaction_type",
NULL item_num,
gjl.description item_name,
NULL uom,
NULL actual_quantity,
NULL actual_unit_cost,
NVL (gjl.accounted_cr, gjl.accounted_dr) actual_ext_cost,
gjh.description "Transaction Source Number",
gjl.je_line_num "Transaction line Number",
SUBSTR (gjh.NAME, 1, 18)
|| '-'
|| gjb.je_batch_id
|| '-'
|| gjl.je_line_num
"transaction_num",
gjh.date_created "transaction_date",
NVL (gjl.accounted_cr, gjl.accounted_dr) "transacation_amount",
NULL vendor_po,
gjh.period_name "gl_period",
gcc.segment1
|| '.'
|| gcc.segment2
|| '.'
|| gcc.segment3
|| '.'
|| gcc.segment4
|| '.'
|| gcc.segment5
|| '.'
|| gcc.segment6
|| '.'
|| gcc.segment7
|| '.'
|| gcc.segment8
"gl_accounts",
gjh.default_effective_date "gl_date",
gjh.NAME "gl_journal_name",
DECODE (gjh.status, 'P', 'Y', 'N') "posted_to_gl",
NULL resource_name,
NULL employee_num,
NULL vendor_num,
NULL actual_hours,
NULL effective_rate,
NULL ext_payroll_cost,
gjh.je_header_id source_id,
gjl.je_line_num source_line_id,
gcc.code_combination_id source_ccid,
NULL source_dist_id,
gjh.je_source journal_source,
gjb.NAME journal_batch_name,
gjh.je_category journal_category,
gjl.je_line_num journal_line_number,
gjh.je_header_id je_header_id,
gjb.je_batch_id je_batch_id,
gjl.je_line_num je_line_num,
NULL line_item_id,
NULL trx_party_id,
NULL trx_party_side_id,
gjb.org_id org_id,
gcc.segment4 LOB,
gcc.segment5 locations
FROM gl_je_batches gjb,
gl_je_headers gjh,
gl_je_lines gjl,
gl_code_combinations gcc
WHERE gjb.je_batch_id = gjh.je_batch_id
AND gjl.je_header_id = gjh.je_header_id
AND gcc.code_combination_id = gjl.code_combination_id
AND gjl.attribute10 IS NOT NULL
AND gjh.ledger_id = 2021
AND (gcc.segment2 LIKE '4%')
AND gjh.je_source IN ('Manual', 'Spreadsheet')
UNION
SELECT 'WOPOCHRG'
|| '-'
|| poh.po_header_id
|| '-'
|| pol.line_num
|| '-'
|| pod.distribution_num
"EUID",
NVL (pol.attribute3, poh.attribute3) "workorder_number",
'PO Charges' "Source",
NULL "transaction_type",
(SELECT DISTINCT msi.segment1
FROM apps.mtl_system_items_b msi
WHERE msi.inventory_item_id = pol.item_id AND ROWNUM = 1)
"item_num",
pol.item_description "item_name",
pol.unit_meas_lookup_code "UOM",
(pod.quantity_ordered - pod.quantity_cancelled) "actual_quantity",
pol.unit_price "actual_unit_cost",
(pol.unit_price * (pod.quantity_ordered - pod.quantity_cancelled))
"actual_ext_cost",
poh.segment1 "Transaction Source Number",
pol.line_num "Transaction line Number",
poh.segment1 || '-' || pol.line_num || '-' || pod.distribution_num
"transaction_num",
poh.approved_date "transaction_date",
(pol.unit_price * (pod.quantity_ordered - pod.quantity_cancelled))
"transacation_amount",
poh.segment1 "vendor_po",
TO_CHAR (poh.approved_date, 'MON-YY') "gl_period",
gcc.concatenated_segments "gl_accounts",
NULL "gl_date",
NULL "gl_journal_name",
NULL "posted_to_gl",
(SELECT pov1.vendor_name
FROM apps.po_vendors pov1
WHERE pov1.vendor_id = poh.vendor_id)
resource_name,
NULL employee_num,
(SELECT pov1.segment1
FROM apps.po_vendors pov1
WHERE pov1.vendor_id = poh.vendor_id)
vendor_num,
NULL actual_hours,
NULL effective_rate,
NULL ext_payroll_cost,
poh.po_header_id source_id,
pol.po_line_id source_line_id,
pod.code_combination_id source_ccid,
pod.po_distribution_id source_dist_id,
NULL journal_source,
NULL journal_batch_name,
NULL journal_category,
NULL journal_line_number,
NULL je_header_id,
NULL je_batch_id,
NULL je_line_num,
pol.item_id line_item_id,
vendor_id trx_party_id,
vendor_site_id trx_party_side_id,
poh.org_id org_id,
gcc.segment4 LOB,
gcc.segment5 locations
FROM apps.po_headers_all poh,
apps.po_lines_all pol,
apps.po_distributions_all pod,
apps.gl_code_combinations_kfv gcc,
po_line_locations_all plla
WHERE poh.po_header_id = pol.po_header_id
AND pol.po_header_id = pod.po_header_id
AND pol.po_line_id = pod.po_line_id
AND gcc.code_combination_id = pod.code_combination_id
AND pol.po_line_id = plla.po_line_id
AND poh.po_header_id = plla.po_header_id
AND plla.line_location_id = pod.line_location_id
AND pod.destination_type_code = 'EXPENSE'
AND (pol.attribute3 IS NOT NULL OR poh.attribute3 IS NOT NULL)
AND poh.authorization_status = 'APPROVED'
UNION ALL
SELECT 'WOLAB' || '-' || gjh.je_header_id || '-' || gjl.je_line_num "EUID",
gjl.attribute10 "workorder_number",
'Payroll Cost' "Source",
'Regular Time' "transaction_type",
NULL item_num,
gjl.description item_name,
NULL uom,
TO_NUMBER (gjl.attribute8) actual_quantity,
TO_NUMBER (gjl.attribute7) actual_unit_cost,
DECODE (gjl.accounted_cr,
NULL, gjl.accounted_dr,
0, gjl.accounted_dr,
gjl.accounted_cr)
actual_ext_cost,
gjh.description "Transaction Source Number",
gjl.je_line_num "Transaction line Number",
gjl.attribute9 "transaction_num",
gjh.default_effective_date "transaction_date",
DECODE (gjl.accounted_cr,
NULL, gjl.accounted_dr,
0, gjl.accounted_dr,
gjl.accounted_cr)
"transacation_amount",
NULL vendor_po,
gjh.period_name "gl_period",
gcc.segment1
|| '.'
|| gcc.segment2
|| '.'
|| gcc.segment3
|| '.'
|| gcc.segment4
|| '.'
|| gcc.segment5
|| '.'
|| gcc.segment6
|| '.'
|| gcc.segment7
|| '.'
|| gcc.segment8
"gl_accounts",
gjh.default_effective_date "gl_date",
gjh.NAME "gl_journal_name",
DECODE (gjh.status, 'P', 'Y', 'N') "posted_to_gl",
gjl.attribute6 resource_name,
gjl.attribute5 employee_num,
NULL vendor_num,
gjl.attribute8 actual_hours,
gjl.attribute7 effective_rate,
DECODE (gjl.accounted_cr,
NULL, gjl.accounted_dr,
0, gjl.accounted_dr,
gjl.accounted_cr)
ext_payroll_cost,
gjh.je_header_id source_id,
gjl.je_line_num source_line_id,
gcc.code_combination_id source_ccid,
NULL source_dist_id,
gjh.je_source journal_source,
gjb.NAME journal_batch_name,
gjh.je_category journal_category,
gjl.je_line_num journal_line_number,
gjh.je_header_id je_header_id,
gjb.je_batch_id je_batch_id,
gjl.je_line_num je_line_num,
NULL line_item_id,
NULL trx_party_id,
NULL trx_party_side_id,
gjb.org_id org_id,
gcc.segment4 LOB,
gcc.segment5 locations
FROM gl_je_batches gjb,
gl_je_headers gjh,
gl_je_lines gjl,
gl_code_combinations gcc
WHERE 1 = 1
AND gjb.je_batch_id = gjh.je_batch_id
AND gjl.je_header_id = gjh.je_header_id
AND gcc.code_combination_id = gjl.code_combination_id
AND gjl.attribute10 IS NOT NULL
AND gjh.ledger_id = 2021
AND gjh.je_source IN ('Payroll')
No comments:
Post a Comment