SELECT gl.name "Ledger",
haou2.name "Operating Unit",
mtl_acct.organization_code "Org Code",
oap.period_name "Period Name",
gcc1.segment1 "Co",
gcc1.segment2 "Cost Ctr",
gcc1.segment3 "Acct",
gcc1.segment4 "Sub-Acct",
gcc1.segment5 "Prod Grp",
mtl_acct.item_number "Item Number",
mtl_acct.item_description "Item Description"
,
ml.meaning
"Acct Line Type",
mtl_acct.transaction_type_name "Transaction Type"
,
mtl_acct.subinventory_code "Subinv.",
mtl_acct.wip_class "WIP Class",
mtl_acct.class_type "Class Type",
mtl_acct.job "WIP Job",
mtl_acct.fg_item "FG Item Number",
mtl_acct.fg_description "FG Description",
mtl_acct.primary_uom_code "UOM Code",
SUM(mtl_acct.primary_quantity) "Quantity",
gl.currency_code "Curr Code",
-- Fix for version 1.5
mtl_acct.item_cost "Item Cost",
-- Fix for version 1.4
-- sum(nvl(al.ACCOUNTED_DR,0) - nvl(al.ACCOUNTED_CR,0)) "Amount"
SUM(Nvl(al.accounted_dr, 0) - Nvl(al.accounted_cr, 0)) "Amount"
-- End fix for version 1.4
FROM inv.org_acct_periods oap,
apps.gl_code_combinations gcc1,
apps.mfg_lookups ml,
hr.hr_organization_information hoi,
hr.hr_all_organization_units haou,-- inv_organization_id
hr.hr_all_organization_units haou2,-- operating unit
gl.gl_ledgers gl,
xla.xla_transaction_entities ent,
xla.xla_events xe,
xla.xla_distribution_links xdl,
xla.xla_ae_headers ah,
xla.xla_ae_lines al,
-- =======
-- Use this inline table to fetch the WIP and non-WIP material transactions
-- WIP is when the mmt.transaction_source_type_id = 5
-- =======
(SELECT mp.organization_code
organization_code,
mp.organization_id
organization_id,
mmt.acct_period_id
acct_period_id,
mta.reference_account
reference_account,
mta.inv_sub_ledger_id
inv_sub_ledger_id,
msi.segment1
item_number,
msi.description
item_description,
mta.accounting_line_type
accounting_line_type,
mtt.transaction_type_name
transaction_type_name,
Decode(mta.accounting_line_type, 7, 'WIP',
14, '',
1,
Decode (mmt.transaction_action_id, 2
,
Decode
(Sign (mta.primary_quantity), -1,
mmt.subinventory_code,
1,
mmt.transfer_subinventory,
mmt.subinventory_code),
3,
Decode (mmt.organization_id, mta.organization_id,
mmt.subinventory_code,
mmt.transfer_subinventory),
21,
Decode (
Sign (mta.primary_quantity),
-1,
mmt.subinventory_code,
1,
mmt.transfer_subinventory,
mmt.subinventory_code),
22,
Decode (
Sign (mta.primary_quantity),
-1,
mmt.subinventory_code,
1,
mmt.transfer_subinventory,
mmt.subinventory_code),
28,
Decode (
Sign (mta.primary_quantity),
-1,
mmt.subinventory_code,
1,
mmt.transfer_subinventory,
mmt.subinventory_code),
mmt.subinventory_code))
subinventory_code,
we.wip_entity_name
job
,
wdj.class_code
wip_class,
ml.meaning
class_type,
msi2.segment1
fg_item,
msi2.description
fg_description,
msi.primary_uom_code,
-- Revised quantity calculation for version 1.5
Sign(SUM(mta.base_transaction_value)) * Abs(SUM(
Decode(mmt.transaction_type_id, 24, mmt.quantity_adjusted,
mmt.primary_quantity)))
primary_quantity,
-- Revised item cost calculation for version 1.5
SUM(mta.base_transaction_value) / Decode(
Sign(SUM(mta.base_transaction_value))
*
Abs(
SUM(
Decode(mmt.transaction_type_id, 24,
mmt.quantity_adjusted,
mmt.primary_quantity))), 0, 1,
Sign(SUM(mta.base_transaction_value))
*
Abs(
SUM(
Decode(mmt.transaction_type_id, 24,
mmt.quantity_adjusted,
mmt.primary_quantity))))
item_cost,
-- End of revisions for version 1.5
SUM(base_transaction_value)
mta_amount
FROM inv.mtl_transaction_accounts mta,
inv.mtl_material_transactions mmt,
wip.wip_entities we,
wip.wip_accounting_classes wac,
wip.wip_discrete_jobs wdj,
inv.mtl_transaction_types mtt,
inv.mtl_system_items_b msi,
inv.mtl_system_items_b msi2,
inv.mtl_parameters mp,
apps.mfg_lookups ml
-- =======
-- Material Transaction, org and item joins
-- =======
WHERE mta.transaction_id = mmt.transaction_id
AND mmt.transaction_type_id = mtt.transaction_type_id
AND mta.organization_id = msi.organization_id
AND mta.inventory_item_id = msi.inventory_item_id
AND mp.organization_id = mta.organization_id
-- =======
-- Material Transaction date and accounting code joins
-- =======
AND mta.transaction_date >= To_date('&P_TRX_FROM_DD_MON_YYYY',
'DD/MON/YYYY HH24:MI:SS') -- P_TRX_DATE_FROM
AND mta.transaction_date < To_date('&P_TRX_TO_DD_MON_YYYY',
'DD/MON/YYYY HH24:MI:SS'
)
+ 1 -- P_TRX_DATE_TO
-- ========
-- WIP joins to material transactions and to wip tables
-- =======
AND mmt.transaction_source_type_id = 5
AND mmt.transaction_source_id = we.wip_entity_id
AND we.wip_entity_id = wdj.wip_entity_id
AND wdj.class_code = wac.class_code
AND mp.organization_id = wac.organization_id
AND wdj.primary_item_id = msi2.inventory_item_id
AND mp.organization_id = msi2.organization_id
-- =========
-- WIP MFG Lookup joins
-- =========
AND ml.lookup_type = 'WIP_CLASS_TYPE'
AND ml.lookup_code = wac.class_type
GROUP BY mp.organization_code,
mp.organization_id,
mmt.acct_period_id,
mta.reference_account,
mta.inv_sub_ledger_id,
msi.segment1,
msi.description,
mta.accounting_line_type,
mtt.transaction_type_name,
Decode(mta.accounting_line_type, 7, 'WIP',
14, '',
1,
Decode (mmt.transaction_action_id, 2, Decode
(Sign (mta.primary_quantity), -1,
mmt.subinventory_code,
1,
mmt.transfer_subinventory,
mmt.subinventory_code),
3,
Decode (mmt.organization_id, mta.organization_id,
mmt.subinventory_code,
mmt.transfer_subinventory),
21, Decode (
Sign (mta.primary_quantity), -1,
mmt.subinventory_code,
1,
mmt.transfer_subinventory,
mmt.subinventory_code),
22, Decode (
Sign (mta.primary_quantity), -1,
mmt.subinventory_code,
1,
mmt.transfer_subinventory,
mmt.subinventory_code),
28, Decode (
Sign (mta.primary_quantity), -1,
mmt.subinventory_code,
1,
mmt.transfer_subinventory,
mmt.subinventory_code),
mmt.subinventory_code)),
we.wip_entity_name,
wdj.class_code,
ml.meaning,
msi2.segment1,
msi2.description,
msi.primary_uom_code,
-- Fix for version 1.4
-- Add for inline columns
msi.inventory_item_id,
msi.organization_id
-- End fix for version 1.4
UNION ALL
SELECT mp.organization_code
organization_code,
mp.organization_id
organization_id,
mmt.acct_period_id
acct_period_id,
mta.reference_account
reference_account,
mta.inv_sub_ledger_id
inv_sub_ledger_id,
msi.segment1
item_number,
msi.description
item_description,
mta.accounting_line_type
accounting_line_type,
mtt.transaction_type_name
transaction_type_name,
Decode(mta.accounting_line_type, 7, 'WIP',
14, '',
1, Decode (mmt.transaction_action_id, 2
,
Decode
(Sign (mta.primary_quantity), -1,
mmt.subinventory_code,
1,
mmt.transfer_subinventory,
mmt.subinventory_code),
3,
Decode (mmt.organization_id, mta.organization_id, mmt.subinventory_code,
mmt.transfer_subinventory),
21, Decode (
Sign (mta.primary_quantity), -1,
mmt.subinventory_code,
1,
mmt.transfer_subinventory,
mmt.subinventory_code),
22, Decode (
Sign (mta.primary_quantity), -1,
mmt.subinventory_code,
1,
mmt.transfer_subinventory,
mmt.subinventory_code),
28, Decode (
Sign (mta.primary_quantity), -1,
mmt.subinventory_code,
1,
mmt.transfer_subinventory,
mmt.subinventory_code),
mmt.subinventory_code))
subinventory_code,
'' job
,
''
wip_class,
''
class_type,
''
fg_item,
''
fg_description,
msi.primary_uom_code,
-- Revision for version 1.4
Sign(SUM(mta.base_transaction_value)) * Abs(SUM(
Decode(mmt.transaction_type_id, 24, mmt.quantity_adjusted,
mmt.primary_quantity)))
primary_quantity,
SUM(mta.base_transaction_value) / Decode(
Sign(SUM(mta.base_transaction_value))
*
Abs(
SUM(
Decode(mmt.transaction_type_id, 24,
mmt.quantity_adjusted,
mmt.primary_quantity))), 0, 1,
Sign(SUM(mta.base_transaction_value))
*
Abs(
SUM(
Decode(mmt.transaction_type_id, 24,
mmt.quantity_adjusted,
mmt.primary_quantity))))
item_cost,
SUM(base_transaction_value)
mta_amount
-- End fix for version 1.4
FROM inv.mtl_transaction_accounts mta,
inv.mtl_material_transactions mmt,
inv.mtl_transaction_types mtt,
inv.mtl_system_items_b msi,
inv.mtl_parameters mp
-- =======
-- Material Transaction, org and item joins
-- =======
WHERE mta.transaction_id = mmt.transaction_id
AND mmt.transaction_type_id = mtt.transaction_type_id
AND mta.organization_id = msi.organization_id
AND mta.inventory_item_id = msi.inventory_item_id
AND mp.organization_id = mta.organization_id
-- ========
-- Material Transaction date and accounting code joins
-- ========
AND mta.transaction_date >= To_date('&P_TRX_FROM_DD_MON_YYYY',
'DD/MON/YYYY HH24:MI:SS') -- P_TRX_DATE_FROM
AND mta.transaction_date < To_date('&P_TRX_TO_DD_MON_YYYY',
'DD/MON/YYYY HH24:MI:SS'
)
+ 1 -- P_TRX_DATE_TO
-- =========
-- WIP joins to material transactions
-- ========
AND mmt.transaction_source_type_id <> 5
GROUP BY mp.organization_code,
mp.organization_id,
mmt.acct_period_id,
mta.reference_account,
mta.inv_sub_ledger_id,
msi.segment1,
msi.description,
mta.accounting_line_type,
mtt.transaction_type_name,
Decode(mta.accounting_line_type, 7, 'WIP',
14, '',
1,
Decode (mmt.transaction_action_id, 2, Decode
(Sign (mta.primary_quantity), -1,
mmt.subinventory_code,
1,
mmt.transfer_subinventory,
mmt.subinventory_code),
3,
Decode (mmt.organization_id, mta.organization_id,
mmt.subinventory_code,
mmt.transfer_subinventory),
21, Decode (
Sign (mta.primary_quantity), -1,
mmt.subinventory_code,
1,
mmt.transfer_subinventory,
mmt.subinventory_code),
22, Decode (
Sign (mta.primary_quantity), -1,
mmt.subinventory_code,
1,
mmt.transfer_subinventory,
mmt.subinventory_code),
28, Decode (
Sign (mta.primary_quantity), -1,
mmt.subinventory_code,
1,
mmt.transfer_subinventory,
mmt.subinventory_code),
mmt.subinventory_code)),
'',
'',
'',
'',
'',
msi.primary_uom_code,
-- Fix for version 1.4
-- Add for inline columns
msi.inventory_item_id,
msi.organization_id
-- End fix for version 1.4
) mtl_acct
-- =======
-- Inventory Org accounting period joins
-- ========
WHERE oap.period_name = ah.period_name
AND oap.organization_id = mtl_acct.organization_id
-- =======
-- Lookup values to see more detail
-- =======
AND ml.lookup_type = 'CST_ACCOUNTING_LINE_TYPE'
AND ml.lookup_code = mtl_acct.accounting_line_type
-- =======
-- using the base tables to avoid the performance issues
-- with org_organization_definitions and hr_operating_units
-- =======
AND hoi.org_information_context = 'Accounting Information'
AND hoi.organization_id = mtl_acct.organization_id
AND hoi.organization_id = haou.organization_id
-- this gets the organization name
AND haou2.organization_id = To_number(hoi.org_information3)
-- this gets the operating unit id
AND gl.ledger_id = To_number(hoi.org_information1) -- get the ledger_id
-- and gl.name = nvl('&P_LEDGER',gl.name) -- P_LEDGER
AND gl.name = Decode('&P_LEDGER', '%', gl.name,
NULL, gl.name,
'&P_LEDGER') -- P_LEDGER
-- ========
-- SLA table joins to get the exact account numbers
-- =======
AND ent.entity_code = 'MTL_ACCOUNTING_EVENTS'
AND ent.application_id = 707
AND xe.application_id = ent.application_id
AND xe.event_id = xdl.event_id
AND ah.entity_id = ent.entity_id
AND ah.ledger_id = ent.ledger_id
AND ah.application_id = al.application_id
AND ah.application_id = 707
AND ah.event_id = xe.event_id
AND ah.ae_header_id = al.ae_header_id
AND al.application_id = ent.application_id
AND al.ledger_id = ah.ledger_id
AND al.ae_header_id = xdl.ae_header_id
AND al.ae_line_num = xdl.ae_line_num
AND xdl.application_id = ent.application_id
AND xdl.source_distribution_type = 'MTL_TRANSACTION_ACCOUNTS'
AND mtl_acct.inv_sub_ledger_id = xdl.source_distribution_id_num_1
AND gcc1.code_combination_id = al.code_combination_id
-- =======
GROUP BY gl.name,
haou2.name,
mtl_acct.organization_code,
oap.period_name,
gcc1.segment1,
gcc1.segment2,
gcc1.segment3,
gcc1.segment4,
gcc1.segment5,
mtl_acct.item_number,
mtl_acct.item_description,
mtl_acct.subinventory_code,
mtl_acct.wip_class,
mtl_acct.class_type,
mtl_acct.job,
mtl_acct.fg_item,
mtl_acct.fg_description,
ml.meaning,
mtl_acct.transaction_type_name,
mtl_acct.primary_uom_code,
gl.currency_code,
mtl_acct.item_cost
ORDER BY 1,
3,
4,
7,
8,
9,
10,
11,
14,
16,
21;
SELECT b.name batch_name,
b.description batch_description,
b.running_total_accounted_dr batch_total_dr,
b.running_total_accounted_cr batch_total_cr,
b.status batch_status,
b.default_effective_date effective_date,
b.default_period_name batch_period_name,
b.creation_date,
u.user_name batch_created_by,
h.je_category,
h.je_source,
h.period_name je_period_name,
h.name journal_name,
h.status journal_status,
h.creation_date je_created_date,
u1.user_name je_created_by,
h.description je_description,
h.running_total_accounted_dr je_total_dr,
h.running_total_accounted_cr je_total_cr,
l.je_line_num line_number,
l.ledger_id,
glcc.concatenated_segments Account,
l.entered_dr,
l.entered_cr,
l.accounted_dr,
l.accounted_cr,
xlal.unrounded_accounted_dr XLA_unrounded_accounted_dr,
xlal.unrounded_accounted_cr XLA_unrounded_accounted_cr,
l.description,
xlal.code_combination_id,
xlal.accounting_class_code,
xlal.accounted_dr xlal_accounted_dr,
xlal.accounted_cr xlal_accounted_cr,
xlal.description xlal_description,
xlal.accounting_date xlal_accounting_date,
xlate.entity_code xlate_entity_code,
xlate.source_id_int_1 xlate_source_id_int_1,
xlate.source_id_int_2 xlate_source_id_int_2,
xlate.source_id_int_3 xlate_source_id_int_3,
xlate.security_id_int_1 xlate_security_id_int_1,
xlate.security_id_int_2 xlate_security_id_int_2,
xlate.transaction_number xlate_transaction_number,
mmt.transaction_id,
mmt.transaction_reference,
mmt.organization_id,
mmt.transaction_date,
mtt.transaction_type_name
FROM gl_je_batches b,
gl_je_headers h,
gl_je_lines l,
fnd_user u,
fnd_user u1,
gl_code_combinations_kfv glcc,
gl_import_references gir,
xla_ae_lines xlal,
xla_ae_headers xlah,
xla_events xlae,
xla.xla_transaction_entities xlate,
mtl_material_transactions mmt,
mtl_transaction_types mtt
WHERE b.created_by = u.user_id
AND h.created_by = u1.user_id
AND b.je_batch_id = h.je_batch_id
AND h.je_header_id = l.je_header_id
AND xlal.code_combination_id = glcc.code_combination_id
AND l.je_header_id = gir.je_header_id
AND l.je_line_num = gir.je_line_num
AND gir.gl_sl_link_table = xlal.gl_sl_link_table
AND gir.gl_sl_link_id = xlal.gl_sl_link_id
AND xlal.ae_header_id = xlah.ae_header_id
AND xlah.event_id = xlae.event_id
AND xlae.entity_id = xlate.entity_id
AND xlae.application_id = xlate.application_id
AND xlate.source_id_int_1 = mmt.transaction_id
AND mmt.transaction_type_id = mtt.transaction_type_id
AND h.je_source = 'Cost Management'
AND h.je_category = 'Inventory'
AND h.period_name = '&period_name';
SELECT gjh.period_name "Period name",
gjb.name "Batch name",
gjjlv.header_name "Journal entry",
gjjlv.je_source "Source",
glcc.concatenated_segments "Accounts",
mmt.subinventory_code "Subinventory",
glcc3.segment4 "Costcenter",
gjjlv.line_entered_dr "Entered debit",
gjjlv.line_entered_cr "Entered credit",
gjjlv.line_accounted_dr "Accounted debit",
gjjlv.line_accounted_cr "Accounted credit",
gjjlv.currency_code "Currency",
mtt.transaction_type_name "Transaction type",
To_char(mta.transaction_id)"Transaction_number",
mta.transaction_date "Transaction_date",
msi.segment1 "Reference"
FROM apps.gl_je_journal_lines_v gjjlv,
gl_je_lines gje,
mtl_transaction_accounts mta,
mtl_material_transactions mmt,
mtl_system_items_b msi,
gl_je_headers gjh,
gl_je_batches gjb,
apps.gl_code_combinations_kfv glcc,
apps.gl_code_combinations_kfv glcc2,
mtl_secondary_inventories msin,
mtl_transaction_types mtt,
mtl_secondary_inventories cost,
gl_code_combinations glcc3
WHERE gjjlv.period_name BETWEEN 'NOV-2008' AND 'DEC-2008'
AND gje.code_combination_id = gje.code_combination_id
AND gjjlv.line_je_line_num = gje.je_line_num
AND gl_sl_link_table = 'MTA'
AND gjjlv.je_header_id = gje.je_header_id
AND mmt.inventory_item_id = msi.inventory_item_id
AND gje.je_header_id = gjh.je_header_id
AND gjh.je_batch_id = gjb.je_batch_id
AND mmt.organization_id = msi.organization_id
AND mmt.organization_id = msin.organization_id
AND mmt.subinventory_code = msin.secondary_inventory_name
AND mta.gl_sl_link_id = gje.gl_sl_link_id
AND mta.reference_account = glcc.code_combination_id
AND msin.expense_account = glcc2.code_combination_id
AND mmt.transaction_id = mta.transaction_id
AND mtt.transaction_type_id = mmt.transaction_type_id
AND cost.organization_id(+) = mmt.organization_id
AND cost.secondary_inventory_name(+) = mmt.subinventory_code
AND glcc3.code_combination_id(+) = cost.expense_account