WIP Value Summary
Description
- Categories
- Enginatics, R12 only
- Source
- enter the source "WIP","Material" or "Both" you wish to report (mandatory).
- Repository
- GitHub
Report to get the WIP and Material accounting distributions, by WIP job,period, Org .
It displays detailed information from both sources WIP and Material Transactions
Parameters:
===========
Period: To pull the transactions based on specific inventory period ( transaction_date).(mandatory)
Category Set 1: any item category you wish, typically the Cost or Product Line category set (optional).
Category Set 2: any item category you wish, typically the Inventory category set (optional).
Category Set 3: any item category you wish, typically the Inventory category set (optional).
Replace Colon with Dot: This is to give flexibility to replace concatenated segments '-' with '.' . If Yes, it wil always replace (optional).
WIP Job or Flow Schedule: enter the WIP Job or the Flow Schedule to report (optional).
Organization Code: enter the inventory organization(s) you wish to report (mandatory).
Operating Unit: enter the operating unit(s) you wish to report (optional).
Ledger: enter the ledger(s) you wish to report (optional).
It displays detailed information from both sources WIP and Material Transactions
Parameters:
===========
Period: To pull the transactions based on specific inventory period ( transaction_date).(mandatory)
Category Set 1: any item category you wish, typically the Cost or Product Line category set (optional).
Category Set 2: any item category you wish, typically the Inventory category set (optional).
Category Set 3: any item category you wish, typically the Inventory category set (optional).
Replace Colon with Dot: This is to give flexibility to replace concatenated segments '-' with '.' . If Yes, it wil always replace (optional).
WIP Job or Flow Schedule: enter the WIP Job or the Flow Schedule to report (optional).
Organization Code: enter the inventory organization(s) you wish to report (mandatory).
Operating Unit: enter the operating unit(s) you wish to report (optional).
Ledger: enter the ledger(s) you wish to report (optional).
SQL 224 lines
--------------------------- --WIP Transaction Accounts--- --------------------------- select 'WIP' source, nvl(gl.short_name, gl.name) ledger, hou.name operating_unit, ood.organization_code, trunc(wta.transaction_date) transaction_date, to_char(xah.period_name) period, we.wip_entity_name wip_job_number, xxen_util.meaning(wdj.status_type,'WIP_JOB_STATUS',700) job_status, wdj.net_quantity wo_quantity, msiv.concatenated_segments assembly_item_number, msiv.concatenated_segments item_number, case when :p_replace_colon_dot = 'Y' then replace( xxen_util.concatenated_segments(msiv.expense_account),'-','.') else xxen_util.concatenated_segments(msiv.expense_account) end item_expense_account, case when :p_replace_colon_dot = 'Y' then replace( xxen_util.concatenated_segments(msiv.cost_of_sales_account),'-','.') else xxen_util.concatenated_segments(msiv.cost_of_sales_account) end item_cogs_account, case when :p_replace_colon_dot = 'Y' then replace( xxen_util.concatenated_segments(msiv.sales_Account),'-','.') else xxen_util.concatenated_segments(msiv.sales_Account) end item_sales_account, msiv.description item_description, &category_columns decode(msiv.planning_make_buy_code, 2, 'Buy', 1, 'Make')"Make/Buy", msiv.item_type, null subinventory, null locator, wt.operation_seq_num operation_sequence, bd.department_code department, wt.resource_seq_num resource_seq, br.resource_code, br.description resource_description, xxen_util.meaning(br.cost_code_type,'CST_COST_CODE_TYPE',700) cost_element, &cst_segment_columns &sla_segment_columns br.unit_of_measure transaction_uom, xxen_util.meaning(wta.basis_type,'CST_BASIS',700) basis_type, wta.primary_quantity transaction_qty, wta.rate_or_amount unit_cost, wta.base_transaction_value transaction_value, gl.currency_code currency, xxen_util.meaning(wt.transaction_type,'WIP_TRANSACTION_TYPE',700) transaction_type_name, xal.accounting_class_code accounting_class, xxen_util.meaning(wta.accounting_line_type,'CST_ACCOUNTING_LINE_TYPE',700) accounting_line_type, xal.accounted_dr, xal.accounted_cr, nvl(xal.accounted_dr, 0)-nvl(xal.accounted_cr, 0)account_net, wta.activity_id, xxen_util.meaning(we.entity_type,'WIP_ENTITY',700) wip_entity_type, xxen_util.meaning(wdj.job_type,'WIP_DISCRETE_JOB',700) job_type, xxen_util.meaning(wdj.wip_supply_type,'WIP_SUPPLY',700) supply_type, null po_number, wta.wip_entity_id, wta.transaction_id, xte.entity_code, xah.ae_header_id xla_header_id, xal.ae_line_num xla_line_number from gl_ledgers gl, org_organization_definitions ood, wip_transaction_accounts wta, wip_transactions wt, wip_entities we, wip_discrete_jobs wdj, gl_code_combinations_kfv gcc1, gl_code_combinations_kfv gcc2, xla_transaction_entities_upg xte, org_acct_periods oap, xla_events xe, xla_ae_headers xah, xla_ae_lines xal, xla_distribution_links xdl, hr_operating_units hou, mtl_system_items_vl msiv, wip_repetitive_items wri, bom_departments bd, bom_resources br where 1=1 and 3=3 and wta.organization_id=ood.organization_id and ood.set_of_books_id=gl.ledger_id and ood.operating_unit=hou.organization_id and xte.source_id_int_1=wta.transaction_id and xte.security_id_int_1=wta.organization_id and xe.entity_id=xte.entity_id and xe.event_id=xah.event_id and xah.ae_header_id=xal.ae_header_id and xdl.event_id=xe.event_id and xdl.ae_header_id=xah.ae_header_id and xdl.ae_line_num=xal.ae_line_num and wta.wip_sub_ledger_id=xdl.source_distribution_id_num_1 and xal.code_combination_id=gcc2.code_combination_id and xte.ledger_id=xah.ledger_id and wta.base_transaction_value<>0 and hou.set_of_books_id=xte.ledger_id and hou.organization_id=xte.security_id_int_2 and wta.organization_id=ood.organization_id and wta.accounting_line_type<>15 and wta.transaction_id=wt.transaction_id and wta.wip_entity_id=we.wip_entity_id and wta.wip_entity_id=wdj.wip_entity_id and wta.organization_id(+)=wdj.organization_id and wta.transaction_date >= oap.period_start_date and wta.transaction_date < oap.schedule_close_date + 1 and oap.organization_id= ood.organization_id and wta.reference_account=gcc1.code_combination_id and we.organization_id(+)=msiv.organization_id and we.primary_item_id(+)=msiv.inventory_item_id and wt.organization_id=wri.organization_id(+) and wt.wip_entity_id=wri.wip_entity_id(+) and wt.line_id=wri.line_id(+) and wt.department_id=bd.department_id(+) and br.resource_id(+)=wta.resource_id and :p_source in ('Both','WIP') union --------------------------- --Material Distirbutions--- --------------------------- select 'Material' source, nvl(gl.short_name, gl.name) ledger, hou.name operating_unit, ood.organization_code, trunc(mta.transaction_date) transaction_date, to_char(xah.period_name) period, we.wip_entity_name wip_job_number, xxen_util.meaning(wdj.status_type,'WIP_JOB_STATUS',700) job_status, wdj.net_quantity wo_quantity, (select msiv_assembly.concatenated_segments from mtl_system_items_vl msiv_assembly where wdj.primary_item_id=msiv_assembly.inventory_item_id and wdj.organization_id=msiv_assembly.organization_id) assembly_item_number, msiv.concatenated_segments item_number, case when :p_replace_colon_dot = 'Y' then replace( xxen_util.concatenated_segments(msiv.expense_account),'-','.') else xxen_util.concatenated_segments(msiv.expense_account) end item_expense_account, case when :p_replace_colon_dot = 'Y' then replace( xxen_util.concatenated_segments(msiv.cost_of_sales_account),'-','.') else xxen_util.concatenated_segments(msiv.cost_of_sales_account) end item_cogs_account, case when :p_replace_colon_dot = 'Y' then replace( xxen_util.concatenated_segments(msiv.sales_Account),'-','.') else xxen_util.concatenated_segments(msiv.sales_Account) end item_sales_account, msiv.description item_description, &category_columns decode(msiv.planning_make_buy_code, 2, 'Buy', 1, 'Make')"Make/Buy", msiv.item_type, mmt.subinventory_code subinventory, mil.concatenated_segments locator, mmt.operation_seq_num operation_sequence, null department, null resource_seq, null resource_code, null resource_description, cce.cost_element, &cst_segment_columns &sla_segment_columns mmt.transaction_uom, xxen_util.meaning(mta.basis_type,'CST_BASIS',700) basis_type, mmt.transaction_quantity transaction_qty, case when mta.primary_quantity <> 0 then (mta.base_transaction_value / mta.primary_quantity) else 0 end unit_cost, mta.base_transaction_value transaction_value, mmt.currency_code currency, mtt.transaction_type_name, xal.accounting_class_code accounting_class, xxen_util.meaning(mta.accounting_line_type,'CST_ACCOUNTING_LINE_TYPE',700) accounting_line_type, xal.accounted_dr, xal.accounted_cr, nvl(xal.accounted_dr, 0)-nvl(xal.accounted_cr, 0)account_net, null activity_id, xxen_util.meaning(we.entity_type,'WIP_ENTITY',700) wip_entity_type, null job_type, null supply_type, null po_number, wdj.wip_entity_id, mmt.transaction_id, xte.entity_code, xah.ae_header_id xla_header_id, xal.ae_line_num xla_line_number from mtl_material_transactions mmt, mtl_transaction_accounts mta, mtl_system_items_vl msiv, gl_ledgers gl, org_organization_definitions ood, mtl_item_locations_kfv mil, mtl_transaction_types mtt, wip_discrete_jobs wdj, wip_entities we, cst_cost_elements cce, gl_code_combinations_kfv gcc1, gl_code_combinations_kfv gcc2, org_acct_periods oap, xla_transaction_entities_upg xte, xla_events xe, xla_ae_headers xah, xla_ae_lines xal, xla_distribution_links xdl, hr_operating_units hou where 2=2 and 3=3 and mmt.transaction_id=mta.transaction_id and mmt.inventory_item_id=mta.inventory_item_id and mta.inventory_item_id=msiv.inventory_item_id and mmt.inventory_item_id=msiv.inventory_item_id and mmt.organization_id=ood.organization_id and msiv.organization_id=mmt.organization_id and msiv.organization_id=ood.organization_id and ood.set_of_books_id=gl.ledger_id and ood.operating_unit=hou.organization_id and mmt.transaction_source_id=wdj.wip_entity_id(+) and wdj.wip_entity_id=we.wip_entity_id and mmt.organization_id=wdj.organization_id(+) and mil.inventory_location_id(+)=mmt.locator_id and mil.organization_id(+)=mmt.organization_id and mil.subinventory_code(+)=mmt.subinventory_code and mmt.transaction_type_id=mtt.transaction_type_id and mta.cost_element_id=cce.cost_element_id(+) and mta.reference_account=gcc1.code_combination_id(+) and mmt.organization_id=nvl(mmt.owning_organization_id, mmt.organization_id) and xte.source_id_int_1=mta.transaction_id and xte.security_id_int_1=mta.organization_id and xe.entity_id=xte.entity_id and xe.event_id=xah.event_id and xah.ae_header_id=xal.ae_header_id and xdl.event_id=xe.event_id and xdl.ae_header_id=xah.ae_header_id and xdl.ae_line_num=xal.ae_line_num and mta.inv_sub_ledger_id=xdl.source_distribution_id_num_1 and xal.code_combination_id=gcc2.code_combination_id and xte.ledger_id=xah.ledger_id and mta.base_transaction_value<> 0 and hou.set_of_books_id=xte.ledger_id and hou.organization_id=xte.security_id_int_2 and mta.transaction_source_type_id=5 and --'Purchase order' =1, Internal Requistion = 7 mmt.transaction_date >= oap.period_start_date and mmt.transaction_date < oap.schedule_close_date + 1 and oap.organization_id= ood.organization_id and :p_source in ('Both','Material') |
Parameters 10
| Parameter | Type | List of values SQL | |
|---|---|---|---|
| Period Name | LOV |
| |
| Organization Code | LOV |
| |
| Source | LOV | ||
| Category Set 1 | LOV |
| |
| Category Set 2 | LOV |
| |
| Category Set 3 | LOV |
| |
| WIP Job | LOV |
| |
| Operating Unit | LOV |
| |
| Ledger | LOV |
| |
| Replace Colon with Dot | LOV |