WIP Accounts Distribution Detail Report

Description
Categories: Volz
/* +=============================================================================+ -- | Copyright 2009-2014 Douglas Volz Consulting, Inc. | -- | All rights reserved. | -- | Permission to use this code is granted provided the original author is | -- | acknowledged. No warranties, express or otherwise are included in this | -- | permission. | -- +=====================================================...  /* +=============================================================================+
-- | Copyright 2009-2014 Douglas Volz Consulting, Inc. |
-- | All rights reserved. |
-- | Permission to use this code is granted provided the original author is |
-- | acknowledged. No warranties, express or otherwise are included in this |
-- | permission. |
-- +=============================================================================+
-- |
-- | Original Author: Douglas Volz (doug@volzconsulting.com)
-- |
-- | Program Name: XXX_XLA_WIP_ACCTS_DETAIL_REPT.sql
-- |
-- | Parameters:
-- | P_OPERATING_UNIT -- operating unit parameter
-- | P_TRX_DATE_FROM -- starting transaction date
-- | P_TRX_DATE_TO -- ending transaction date
-- |
-- | Description:
-- | Report to get the WIP accounting entries by account, transaction
-- | type and accounting line type. . A group by is used to add up entries across cost
-- | cost elements.
-- |
-- | Version Modified on Modified by Description
-- | ======= =========== ============== =========================================
-- | 1.0 06 Nov 2009 Douglas Volz Initial Coding
-- | 2.0 11 Nov 2009 Douglas Volz Fixed join for primary_assembly_id
-- | 3.0 11 Nov 2009 Douglas Volz Added Org Code and transaction ID
-- | 4.0 12 Nov 2009 Douglas Volz Added item and description
-- | 5.0 05 Jan 2010 Douglas Volz Added org joins for wac and wdj, to avoid
-- | cross-joining
-- +=============================================================================+*/
   more
select
gl.name "Ledger",
haou2.name "Operating Unit",
mp.organization_code "Org Code",
oap.period_name "Period Name",
ml.meaning "Acct Line Type",
ml2.meaning "WIP Transaction Type",
wta.transaction_id "Txn Id",
gcc1.segment1 "Co",
gcc1.segment2 "Cost Ctr",
gcc1.segment3 "Acct",
gcc1.segment4 "Sub-Acct",
gcc1.segment5 "Prod Grp",
gcc1.segment6 "Loc",
gcc1.segment7 "InterCo",
gcc1.segment8 "Future1",
gcc1.segment9 "Future2",
msi.segment1 "Item",
msi.description "Description",
wac.class_code "WIP Class",
sum(nvl(al.ACCOUNTED_DR,0) - nvl(al.ACCOUNTED_CR,0)) "Amount"
from wip.wip_transaction_accounts wta,
wip.wip_transactions wt,
wip.wip_accounting_classes wac,
wip.wip_discrete_jobs wdj,
apps.mfg_lookups ml2,
inv.mtl_system_items_b msi,
inv.org_acct_periods oap,
gl.GL_CODE_COMBINATIONS gcc1,
inv.mtl_parameters mp,
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
-- ========================================================
-- Material Transaction, org and item joins
-- ========================================================
where 1=1
and wta.transaction_id = wt.transaction_id
and wta.organization_id = msi.organization_id
-- fix for version 2.0, use wdj not wt
and wdj.primary_item_id = msi.inventory_item_id
and mp.organization_id = msi.organization_id
-- ========================================================
and wac.class_code = wdj.class_code
and wdj.wip_entity_id = wt.wip_entity_id
and wac.organization_id = wdj.organization_id
-- ========================================================
-- Inventory Org accounting period joins
-- ========================================================
and oap.acct_period_id = wt.acct_period_id
-- ========================================================
-- Version 1.3, added lookup values to see more detail
-- ========================================================
and ml.lookup_type = 'CST_ACCOUNTING_LINE_TYPE'
and ml.lookup_code = wta.accounting_line_type
and ml2.lookup_type = 'WIP_TRANSACTION_TYPE_SHORT'
and ml2.lookup_code = wt.transaction_type
-- ========================================================
-- Material Transaction date and accounting code joins
-- ========================================================
-- ========================================================
-- 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 = wta.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
-- ========================================================
-- SLA table joins to get the exact account numbers
-- ========================================================
AND ent.entity_code = 'WIP_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 = 'WIP_TRANSACTION_ACCOUNTS'
AND xdl.source_distribution_id_num_1 = wta.wip_sub_ledger_id
AND gcc1.code_combination_id = al.code_combination_id
-- ==========================================================
group by
gl.name,
haou2.name,
mp.organization_code,
oap.period_name,
ml.meaning,
ml2.meaning,
wta.transaction_id,
gcc1.segment1,
gcc1.segment2,
gcc1.segment3,
gcc1.segment4,
gcc1.segment5,
gcc1.segment6,
gcc1.segment7,
gcc1.segment8,
gcc1.segment9,
-- DECODE(wta.accounting_line_type, 7, 'WIP', 14, 'Intransit', 1, 'Subinventory'),
msi.segment1,
msi.description,
wac.class_code
order by 1,2
Parameter Name SQL text Validation
Transaction Date To
wta.transaction_date < :P_TRX_TO
Date
Transaction Date From
wta.transaction_date >= :P_TRX_FROM
Date