ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN E396D14CA42652EEE0530100007F49FBY select nvl(gl.short_name,gl.name) ledger, haouv_ou.name operating_unit, mp.organization_code org_code, oap.period_name, (select mtst.transaction_source_type_name from mtl_txn_source_types mtst where mtst.transaction_source_type_id=5) transaction_source, xxen_util.meaning(wt.transaction_type,'WIP_TRANSACTION_TYPE_SHORT',700) transaction_type, wt.transaction_id, wt.transaction_date, wt.creation_date, xxen_util.meaning(we.entity_type,'WIP_ENTITY',700) wip_type, we.wip_entity_name wip_job, wt.operation_seq_num operation_seq_number, wt.resource_seq_num resource_seq_number, br.resource_code, br.description resource_description, cce.cost_element, msiv.concatenated_segments outside_processing_item, msiv.description outside_processing_description, xxen_util.meaning(msiv.item_type,'ITEM_TYPE',3) outside_processing_item_type, xxen_util.meaning(br.resource_type,'BOM_RESOURCE_TYPE',700) resource_type, xxen_util.meaning(br.autocharge_type,'BOM_AUTOCHARGE_TYPE',700) charge_type, xxen_util.meaning(br.default_basis_type,'CST_BASIS',700) basis_type, xxen_util.yes(decode(br.allow_costs_flag,1,'Y')) allow_costs, br.unit_of_measure uom_code, wt.primary_quantity, xxen_util.yes(decode(br.standard_rate_flag,1,'Y')) standard_rate, gl.currency_code, wt.actual_resource_rate, wt.standard_resource_rate, wt.usage_rate_or_amount, round(nvl(wt.primary_quantity,0)*nvl(wt.usage_rate_or_amount,0)*nvl(decode(br.standard_rate_flag,2,wt.actual_resource_rate,wt.standard_resource_rate),0),2) extended_wip_amount, (select cct.cost_type from cst_cost_types cct where mp.primary_cost_method=cct.cost_type_id) cost_method, (select crc.resource_rate from cst_resource_costs crc where wt.resource_id=crc.resource_id and wt.organization_id=crc.organization_id and decode(mp.primary_cost_method,1,1,mp.avg_rates_cost_type_id)=crc.cost_type_id) current_resource_cost from mtl_parameters mp, hr_organization_information hoi, hr_all_organization_units_vl haouv_ou, gl_ledgers gl, wip_transactions wt, org_acct_periods oap, wip_entities we, bom_resources br, cst_cost_elements cce, mtl_system_items_vl msiv where 1=1 and 2=2 and mp.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='INV') and mp.organization_id=hoi.organization_id and hoi.org_information_context='Accounting Information' and to_number(hoi.org_information3)=haouv_ou.organization_id and to_number(hoi.org_information1)=gl.ledger_id and mp.organization_id=wt.organization_id and wt.transaction_id not in (select wta.transaction_id from wip_transaction_accounts wta) and abs(round(nvl(wt.primary_quantity,0)*nvl(wt.usage_rate_or_amount,0)*nvl(decode(br.standard_rate_flag,2,wt.actual_resource_rate,wt.standard_resource_rate),0),2))>=:p_minimum_amount and wt.acct_period_id=oap.acct_period_id and wt.wip_entity_id=we.wip_entity_id and wt.resource_id=br.resource_id(+) and br.cost_element_id=cce.cost_element_id(+) and br.purchase_item_id=msiv.inventory_item_id(+) and br.organization_id=msiv.organization_id(+) order by ledger, operating_unit, org_code, period_name, transaction_type, transaction_date Rebuilt to the Blitz Report SQL standards: one pass over the WIP transactions without grouping, a transaction whose resource has no basis or charge type is no longer dropped, Allow Costs and Standard Rate show Yes or blank. Organization security from the Blitz Report organization access, with operating unit and ledger security through profiles Blitz Report Use Operating Unit Security and Blitz Report Use Ledger Security. US CAC Missing WIP Accounting Transactions Report to find work in process (WIP) accounting entries where the WIP transaction has been created but the WIP accounting entries do not exist. If you enter Yes for "Only Costed Resources" the report ignores WIP transactions where the resource code is defined as not allowing costs (not costed). If you enter No for "Only Costed Resources" the report includes WIP transactions where the resource code does not allow costs as well as costed resources. And to get all transactions which are missing the WIP accounting entries, even for transactions where the resources are not costed, set the "Only Costed Resources" to No and the Minimum Transaction Amount to zero (0). /* +=============================================================================+ -- | Copyright 2022 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 is included in this | -- | permission. | -- +=============================================================================+ -- | -- | Original Author: Douglas Volz (doug@volzconsulting.com) -- | -- | Program Name: missing_wip_accounting_transactions.sql -- | -- | Parameters: -- | p_trx_date_from -- Starting transaction date, mandatory -- | p_trx_date_to -- Ending transaction date, mandatory -- | p_minimum_amount -- The absolute smallest transaction amount to be reported -- | p_only_costed_resources -- Only include transactions where the resource code is costed. -- | p_org_code -- Specific inventory organization you wish to report (optional) -- | p_operating_unit -- Operating Unit you wish to report, leave blank for all -- | operating units (optional) -- | p_ledger -- general ledger you wish to report, leave blank for all -- | ledgers (optional) -- | -- | Description: -- | Report to find WIP accounting entries where the WIP accounting entries do not -- | exist. -- | -- | Version Modified on Modified by Description -- | ======= =========== ============== ========================================= -- | 1.0 21 Jul 2022 Douglas Volz Initial Coding based on missing_material_accounting_transactions.sql -- | 1.1 23 Jul 2022 Douglas Volz Added Ledger and Operating Unit columns. -- | 1.2 23 Oct 2026 Andy Haack Rebuilt to the Blitz Report SQL standards. -- +=============================================================================+*/ Enginatics 1=1 2=2 :p_minimum_amount 1 10 1=1 wt.transaction_date>=:p_trx_date_from Date select max(oap.period_start_date) from org_acct_periods oap, mtl_parameters mp where mp.organization_id = oap.organization_id and mp.organization_id <> mp.master_organization_id Y US Transaction Date From 2 20 1=1 wt.transaction_date<:p_trx_date_to+1 Date select max(oap.schedule_close_date) from org_acct_periods oap, mtl_parameters mp where mp.organization_id = oap.organization_id and mp.organization_id <> mp.master_organization_id Y US Transaction Date To 3 30 :p_minimum_amount Number .009 Y US Minimum Transaction Amount 4 40 2=2 (wt.resource_id is null or br.allow_costs_flag=1) LOV Oracle Yes_No select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description Y Y Y US Only Costed Resources 5 50 1=1 mp.organization_code=:p_org_code LOV custom select ood.organization_code value, ood.organization_name description from org_organization_definitions ood where ood.organization_id not in (select mp.organization_id from mtl_parameters mp where mp.organization_id=mp.master_organization_id) and ood.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='INV') and (:$flex$.operating_unit is null or ood.operating_unit in (select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y')) and (:$flex$.ledger is null or ood.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and nvl(ood.disable_date,sysdate)>=sysdate order by ood.organization_code select mp.organization_code from mtl_parameters mp where mp.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') US Organization Code 6 60 1=1 haouv_ou.name=:p_operating_unit LOV INV Operating Unit 9B9627743E84607DE053BB6B635805FB select haouv.name value, 'Operating Unit' description from hr_all_organization_units_vl haouv where haouv.organization_id in ( select to_number(hoi.org_information3) from mtl_parameters mp, hr_organization_information hoi, hr_all_organization_units haou where mp.organization_id<>mp.master_organization_id and mp.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='INV') and mp.organization_id=hoi.organization_id and hoi.org_information_context='Accounting Information' and (:$flex$.ledger is null or to_number(hoi.org_information1) in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and hoi.organization_id=haou.organization_id and nvl(haou.date_to,sysdate)>=sysdate ) order by 1 US Operating Unit 7 70 1=1 gl.name=:p_ledger LOV GL Ledger (inv org related) 8E2FF36EDF3A79D2E0530100007F1FF2 select gl.name value, fifsv.id_flex_structure_name||': '||gl.description description from gl_ledgers gl, fnd_id_flex_structures_vl fifsv where gl.ledger_id in (select ood.set_of_books_id from org_organization_definitions ood where nvl(ood.disable_date,sysdate)>=sysdate and ood.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='INV')) and (:$flex$.chart_of_accounts is null or xxen_util.contains(:$flex$.chart_of_accounts,fifsv.id_flex_structure_name)='Y') and gl.chart_of_accounts_id=fifsv.id_flex_num and fifsv.id_flex_code='GL#' and fifsv.application_id=101 order by fifsv.id_flex_structure_name, gl.name US Ledger