CAC WIP Account Summary
Description
Categories: Enginatics
Repository: Github
Repository: Github
Report to get the WIP accounting distributions, in summary, by WIP job, resource, overhead and WIP cost update. With the Show SLA Accounting parameter you can choose to use the Release 12 Subledger Accounting (Create Accounting) account setups by selecting Yes. And if you have not modified your SLA accounting rules, select No to allow this report to run a bit faster. With parameters to limit th ...
more
select nvl(gl.short_name, gl.name) Ledger, haou2.name Operating_Unit, acct_dist.organization_code Org_Code, oap.period_name Period_Name, &segment_columns msiv.concatenated_segments Assembly_Number, msiv.description Assembly_Description, -- Revision for version 1.13 fcl.meaning item_type, -- Revision for version 1.9 &category_columns -- End revision for version 1.9 ml1.meaning Accounting_Line_Type, ml2.meaning Transaction_Type, wac.class_code WIP_Class, ml3.meaning Class_Type, -- Revision for version 1.13 ml4.meaning WIP_Type, -- Revision for version 1.17 &p_show_project &p_show_wip_job &p_show_wip_osp -- End revision for version 1.17 -- Revision for version 1.11, 1.12 and 1.16 &p_show_wip_job_uom round(sum(decode(acct_dist.transaction_type, 'Cost Update', 0, nvl(acct_dist.primary_quantity,0) ) ) ,3) Primary_Quantity, -- End revision for version 1.11 gl.currency_code Currency_Code, -- Revision for version 1.11 sum(decode(acct_dist.cost_element_id, 1, acct_dist.base_transaction_value, 0)) Material_Amount, sum(decode(acct_dist.cost_element_id, 2, acct_dist.base_transaction_value, 0)) Material_Overhead_Amount, sum(decode(acct_dist.cost_element_id, 3, acct_dist.base_transaction_value, 0)) Resource_Amount, sum(decode(acct_dist.cost_element_id, 4, acct_dist.base_transaction_value, 0)) Outside_Processing_Amount, sum(decode(acct_dist.cost_element_id, 5, acct_dist.base_transaction_value, 0)) Overhead_Amount, -- End revision for version 1.11 sum(acct_dist.base_transaction_value) Amount from wip_entities we, wip_accounting_classes wac, mtl_system_items_vl msiv, -- Revision for version 1.15 bom_departments bd, bom_resources br, org_acct_periods oap, gl_code_combinations gcc, mfg_lookups ml1, mfg_lookups ml2, mfg_lookups ml3, -- Revision for version 1.13 mfg_lookups ml4, -- Revision for version 1.10 fnd_common_lookups fcl, hr_organization_information hoi, hr_all_organization_units_vl haou, -- inv_organization_id hr_all_organization_units_vl haou2, -- operating unit gl_ledgers gl, -- Revision for version 1.18 &project_tables &wip_osp_tables -- Revision for version 1.17 &wip_sla_tables (select mp.organization_code, mp.organization_id, wt.acct_period_id, wta.reference_account, nvl(wdj.primary_item_id, wfs.primary_item_id) primary_item_id, wta.accounting_line_type, wt.transaction_type, -- Revision for version 1.17 wt.transaction_id, wt.rcv_transaction_id, wt.move_transaction_id, wt.completion_transaction_id, wt.transaction_date, wt.creation_date, wt.created_by, -- Release 12.2 wt.reason_code, wt.reference, -- End revision for version 1.17 nvl(wdj.class_code, wfs.class_code) class_code, nvl(wdj.wip_entity_id, wfs.wip_entity_id) wip_entity_id, wta.resource_id, wt.po_header_id, wt.po_line_id, wt.cost_update_id, -- Revision for version 1.17 -- Release 12.2 wt.resource_instance, -- Revision for version 1.15 wt.department_id, nvl(wdj.project_id, wfs.project_id) project_id, -- Revision for version 1.17 wt.task_id, wt.pm_cost_collected, wt.operation_seq_num, wt.resource_seq_num, wt.autocharge_type, wt.standard_rate_flag, -- Release 12.2 wt.usage_rate_of_amount, wt.actual_resource_rate, wt.standard_resource_rate, wta.rate_or_amount, wta.basis_type, wta.currency_code, wta.currency_conversion_date, wta.currency_conversion_rate, wta.overhead_basis_factor, wta.basis_resource_id, wt.activity_id, -- End revision for version 1.17 wt.transaction_uom, wt.primary_uom, wta.primary_quantity, wta.cost_element_id, wta.base_transaction_value, wta.wip_sub_ledger_id from wip_transaction_accounts wta, wip_transactions wt, wip_discrete_jobs wdj, -- Revision for version 1.18 wip_flow_schedules wfs, mtl_parameters mp -- ======================================================== -- WIP Transaction, org and item joins -- ======================================================== where wt.transaction_id = wta.transaction_id -- Revision for version 1.18 and wdj.wip_entity_id (+) = wta.wip_entity_id and wdj.organization_id (+) = wta.organization_id and wfs.wip_entity_id (+) = wta.wip_entity_id and wfs.organization_id (+) = wta.organization_id -- End revision for version 1.18 and mp.organization_id = wta.organization_id -- Revision for version 1.19 -- Improve logic to screen out zero job close and flow schedule variances -- Revision for version 1.17 -- Do not show zero job and flow schedule close variances -- and (wt.transaction_type <> 6 and wta.base_transaction_value <> 0) and 1 = case when wt.transaction_type = 5 and wta.base_transaction_value = 0 then 2 when wt.transaction_type = 6 and wta.base_transaction_value = 0 then 2 when wt.transaction_type = 7 and wta.base_transaction_value = 0 then 2 else 1 end -- End for revision for version 1.19 and mp.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) and 2=2 -- p_org_code, p_trx_date_from, p_trx_date_to, p_wip_job ) acct_dist -- ======================================================== -- WIP Transaction, org and item joins -- ======================================================== where msiv.organization_id = acct_dist.organization_id and msiv.inventory_item_id = acct_dist.primary_item_id -- Revision for version 1.15 and we.wip_entity_id = acct_dist.wip_entity_id and bd.department_id (+) = acct_dist.department_id and br.resource_id (+) = acct_dist.resource_id -- ======================================================== -- Dynamic SQL joins -- ======================================================== &project_table_joins &osp_wip_table_joins -- ======================================================== and wac.class_code (+) = acct_dist.class_code -- Revision for version 1.2 and wac.organization_id = acct_dist.organization_id and we.wip_entity_id = acct_dist.wip_entity_id -- ======================================================== -- Inventory Org accounting period joins -- ======================================================== and oap.acct_period_id = acct_dist.acct_period_id and oap.organization_id = acct_dist.organization_id -- ======================================================== -- Version 1.3, added lookup values to see more detail -- ======================================================== and ml1.lookup_type = 'CST_ACCOUNTING_LINE_TYPE' and ml1.lookup_code = acct_dist.accounting_line_type and ml2.lookup_type = 'WIP_TRANSACTION_TYPE_SHORT' and ml2.lookup_code = acct_dist.transaction_type and ml3.lookup_type = 'WIP_CLASS_TYPE' and ml3.lookup_code = wac.class_type -- Revision for version 1.13 and ml4.lookup_type = 'WIP_ENTITY' and ml4.lookup_code = we.entity_type -- ======================================================== -- Version 1.10, added Item_Type lookup values -- ======================================================== and fcl.lookup_code (+) = msiv.item_type and fcl.lookup_type (+) = 'ITEM_TYPE' -- End revision for version 1.10 -- ======================================================== -- using the base tables to avoid using -- org_organization_definitions and hr_operating_units -- ======================================================== and hoi.org_information_context = 'Accounting Information' and hoi.organization_id = acct_dist.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 1=1 -- p_assy_number, p_operating_unit, p_ledger -- ======================================================== -- Revision for version 1.17, SLA and Non-SLA joins. -- ======================================================== &wip_sla_table_joins &wip_non_sla_table_joins -- ========================================================== group by nvl(gl.short_name, gl.name), haou2.name, acct_dist.organization_code, oap.period_name, &segment_columns_grp msiv.concatenated_segments, msiv.description, -- Revision for version 1.10 fcl.meaning, -- item_type ml1.meaning, -- Accounting Line Type ml2.meaning, -- WIP Transaction Type wac.class_code, ml3.meaning, -- WIP Class Type -- Revision for version 1.13 ml4.meaning, -- WIP Entity Type &group_by_project &group_by_wip_job &group_by_wip_osp -- Added for inline column selects -- Revision for version 1.12 msiv.organization_id, msiv.inventory_item_id, -- End revision for version 1.8 gl.currency_code order by -- Fix for version 1.10 -- 1,3,4,5,6,7,8,9,10,12,13 nvl(gl.short_name, gl.name), haou2.name, acct_dist.organization_code, oap.period_name, &segment_columns_grp msiv.concatenated_segments, ml1.meaning, -- Accounting_Line_Type ml2.meaning, -- Transaction_Type wac.class_code, -- WIP_Class -- Revision for version 1.13 ml4.meaning -- WIP Entity Type &order_by_project &order_by_wip_job &order_by_wip_osp ,msiv.organization_id |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Transaction Date From |
|
Date | |
Transaction Date To |
|
Date | |
Show SLA Accounting |
|
LOV Oracle | |
Show Projects |
|
LOV Oracle | |
Show WIP Job |
|
LOV Oracle | |
Show WIP Outside Processing |
|
LOV Oracle | |
Category Set 1 |
|
LOV | |
Category Set 2 |
|
LOV | |
Assembly Number |
|
LOV | |
Organization Code |
|
LOV | |
Operating Unit |
|
LOV | |
Ledger |
|
LOV |