ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 8E2FF36EDF3A79D2E0530100007F1FF2 GL Ledger (inv org related) Y 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 (:$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 AR جميع دفاتر الأستاذ المتعلقة بجميع منظمات المخزون النشطة D Alle Ledger, die sich auf alle aktiven Inventarorganisationen beziehen E Todos los libros de contabilidad relacionados con todas las organizaciones de inventario activas F Tous les grands livres relatifs à tous les organismes d'inventaire actifs I Tutti i libri contabili relativi a tutte le organizzazioni di inventario attive JA すべてのアクティブな在庫組織に関連するすべての台帳 KO 모든 활성 재고 조직과 관련된 모든 원장 PTB Todos os livros contábeis relacionados a todas as organizações de inventário ativas RU Все бухгалтерские книги, относящиеся ко всем организациям, занимающимся активным инвентаризацией. S Alla huvudböcker relaterade till alla aktiva lagerorganisationer TR Tüm aktif envanter organizasyonlarıyla ilgili tüm defterler US All ledgers related to all active inventory organizations ZHS 与所有现行清单组织有关的所有分类账 8E2FF36EDECA79D2E0530100007F1FF2 INV Category Set Y select distinct mcsv.category_set_name value, mcsv.description from mtl_category_sets_vl mcsv where :$flex$.organization_code is null or mcsv.category_set_id in (select mic.category_set_id from mtl_parameters mp, mtl_item_categories mic where xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' and mp.organization_id=mic.organization_id) order by mcsv.category_set_name US 91D022B15B12FFB9E053BB6B63587F0B INV Item (master, costing enabled only) Y Y select msiv.concatenated_segments value, msiv.description from mtl_system_items_vl msiv where msiv.organization_id=( select max(mp.master_organization_id) master_organization_id from mtl_parameters mp where mp.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') or fnd_profile.value('MFG_ORGANIZATION_ID') is null 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 msiv.costing_enabled_flag='Y' order by msiv.concatenated_segments US All costing enabled items from item master org 9B9627743E84607DE053BB6B635805FB INV Operating Unit Y 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=hoi.organization_id and hoi.org_information_context='Accounting Information' and hoi.organization_id=haou.organization_id and nvl(haou.date_to,sysdate)>=sysdate ) order by 1 AR جميع وحدات التشغيل المرتبطة بمؤسسة مخزون غير رئيسية نشطة D Alle Betriebseinheiten, die mit einer aktiven Nicht-Master-Inventarisierungsorganisation verbunden sind E Todas las unidades operativas relacionadas con una organización de inventario no maestra activa F Toutes les unités opérationnelles liées à un organisme d'inventaire non maître actif I Tutte le unità operative legate a un'organizzazione di inventario non master attiva JA アクティブな非マスターインベントリ組織に関連するすべてのオペレーティング・ユニット KO 활성 비 마스터 재고 조직과 관련된 모든 운영 단위 PTB Todas as unidades operacionais relacionadas a uma organização ativa de inventário não-mestre RU Все операционные единицы, относящиеся к действующей организации, не являющейся ведущим поставщиком инвентарных запасов. S Alla operativa enheter relaterade till en aktiv organisation som inte är huvudlager TR Etkin bir ana envanter organizasyonu ile ilgili tüm işletim birimleri US All operating units related to an active non master inventory organization ZHS 与在用的非总清单组织有关的所有业务单位： 91D3167A33531204E053BB6B63585EE3 INV Organization Code (excluding master) Y 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 (:$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 AR جميع رموز تنظيم المخزون النشط باستثناء المؤسسات الرئيسية D Alle aktiven Inventar-Organisationscodes mit Ausnahme von Master-Organisationen E Todos los códigos de organización de inventario activos, excluyendo las organizaciones maestras F Tous les codes d'organisation de l'inventaire actif, à l'exclusion des organisations maîtresses I Tutti i codici di organizzazione dell'inventario attivi, escluse le organizzazioni master JA マスター組織を除くすべてのアクティブなインベントリ組織コード KO 마스터 조직을 제외한 모든 활성 재고 조직 코드 PTB Todos os códigos ativos de organização de inventário, excluindo organizações mestre RU Все действующие инвентаризационные коды организаций, за исключением основных организаций S Alla aktiva organisationskoder för inventarier exklusive huvudorganisationer TR Ana organizasyonlar hariç tüm aktif envanter organizasyon kodları US All active inventory organization codes excluding master organizations ZHS 所有现行清单组织代码，不包括主组织 E7CCB27F39561272E0530100007F62B4 WIP Class Code (org dependent) Y select distinct wac.class_code value, wac.description from mtl_parameters mp, wip_accounting_classes wac where (:$flex$.organization_code is null or xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y') and mp.organization_id=wac.organization_id order by wac.class_code US 8E2FF36EDEC379D2E0530100007F1FF2 WIP Job Y Y select we.wip_entity_name value, xxen_util.meaning(we.entity_type,'WIP_ENTITY',700)||' ('||ood.organization_code||')'||nvl2(we.description,': '||we.description,null) description from wip_entities we, org_organization_definitions ood where (:$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 ( :$flex$.organization_code is null and ood.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) or xxen_util.contains(:$flex$.organization_code,ood.organization_code)='Y' ) and we.organization_id=ood.organization_id and nvl(ood.disable_date,sysdate)>=sysdate and we.entity_type in (1,3,5,8) --(Discrete job, Closed discrete job, Lot based job, Closed lot based job) order by we.creation_date desc US B9E8FC1CED8511DCE053BB6B6358E005 select nvl(gl.short_name, gl.name) Ledger, haou2.name Operating_Unit, mp.organization_code Org_Code, -- Revision for version 1.6 -- 'p_date_completed' For_Completion_Date, wac.class_code WIP_Class, ml1.meaning Class_Type, we.wip_entity_name WIP_Job, ml2.meaning Job_Status, -- Revision for version 1.14 wdj.date_released Date_Released, wdj.date_completed Date_Completed, wdj.last_update_date Last_Update_Date, wdj.completion_subinventory Completion_Subinventory, msiv.concatenated_segments Assembly_Number, msiv.description Assembly_Description, -- Revision for version 1.14 fcl.meaning Item_Type, misv.inventory_item_status_code Item_Status, ml3.meaning Make_Buy_Code, -- End revision for version 1.14 &category_columns -- Revision for version 1.10 muomv.uom_code UOM_Code, nvl(wdj.start_quantity, 0) Start_Quantity, nvl(wdj.quantity_completed, 0) Quantity_Completed, nvl(wdj.quantity_scrapped, 0) Quantity_Scrapped, nvl(wdj.quantity_completed, 0) + nvl(wdj.quantity_scrapped, 0) Total_Quantity, -- Check for completion quantities -- Revision for version 1.10, check if WIP scrap is financially recorded case when (wdj.quantity_completed + decode(:p_include_scrap,'N',0, wdj.quantity_scrapped)) = 0 then (select fl.meaning from fnd_lookups fl where fl.lookup_type = 'YES_NO_ALL' and fl.lookup_code = 'N') -- No completion quantities when (wdj.start_quantity - (wdj.quantity_completed + decode(:p_include_scrap,'N',0, wdj.quantity_scrapped))) = 0 then (select fl.meaning from fnd_lookups fl where fl.lookup_type = 'YES_NO_ALL' and fl.lookup_code = 'A') -- All quantities completed when (wdj.start_quantity - (wdj.quantity_completed + decode(:p_include_scrap,'N',0, wdj.quantity_scrapped))) > 0 then (select ml.meaning from mfg_lookups ml where ml.lookup_type = 'SYS_RANGE' and ml.lookup_code = 2) -- Partial quantities completed else (select fl.meaning from fnd_lookups fl where fl.lookup_type = 'YES_NO_ALL' and fl.lookup_code = 'A') -- All quantities completed end Quantities_Completed, -- Revision for version 1.10, check for WIP with material quantities not issued (select max(fl.meaning) from wip_requirement_operations wro, mtl_system_items_b msi, wip_parameters wp, fnd_lookups fl where wro.wip_entity_id = wdj.wip_entity_id and wro.organization_id = wdj.organization_id and msi.organization_id = wro.organization_id and msi.inventory_item_id = wro.inventory_item_id -- Only want to check valued items, not expense items and msi.inventory_asset_flag = 'Y' and wp.organization_id = wdj.organization_id and fl.lookup_type = 'YES_NO' and fl.lookup_code = 'Y' -- Revision for version 1.12 and 2=2 -- Include WIP bulk supply types -- Revision for version 1.13 and wro.wip_supply_type <> 6 -- Phantom -- Calculate the quantity required based on the completion quantities -- Use the completion quantities with scrap quantities unless scrap is not financially recorded -- Basis of 2 indicates the component is issued per lot not per assembly and the component yield factor is ignored having round(sum(decode(wro.basis_type, null, nvl(wro.quantity_per_assembly, 0) * 1 / nvl(wro.component_yield_factor, 1) * (nvl(wdj.quantity_completed, 0) + decode(:p_include_scrap,'N',0,nvl(wdj.quantity_scrapped, 0))), 1, nvl(wro.quantity_per_assembly, wdj.start_quantity) * 1 / nvl(wro.component_yield_factor, 1) * (nvl(wdj.quantity_completed, 0) + decode(:p_include_scrap,'N',0,nvl(wdj.quantity_scrapped, 0))), 2, nvl(wro.required_quantity,1), nvl(wro.quantity_per_assembly, 0) * 1 / nvl(wro.component_yield_factor, 1) * (nvl(wdj.quantity_completed, 0) + decode(:p_include_scrap,'N',0,nvl(wdj.quantity_scrapped, 0))) ) ) ,3) - round(sum(nvl(wro.quantity_issued, 0)),3) > 0 ) Open_Material_Quantities, -- Quantity_Left_in_WIP -- End of revision for version 1.10 -- Check for WIP Operation Resources with no earned OSP -- Revision for version 1.3 -- nvl((select 'Yes' (select max(fl.meaning) from wip_operation_resources wor, fnd_lookups fl where wor.wip_entity_id = wdj.wip_entity_id and wor.organization_id = wdj.organization_id and wor.autocharge_type in ( 3, 4 ) -- 3 = 'PO receipt' and 4 = 'PO move' and nvl(wor.applied_resource_units,0) = 0 and fl.lookup_type = 'YES_NO' and fl.lookup_code = 'Y' ) No_Earned_OSP, gl.currency_code Currency_Code, round( sum(nvl(wpb.pl_material_in,0)- nvl(wpb.tl_material_out,0)- nvl(wpb.pl_material_out,0)- nvl(wpb.tl_material_var,0)- nvl(wpb.pl_material_var,0) + -- Material Overhead Balance nvl(wpb.pl_material_overhead_in,0)- nvl(wpb.tl_material_overhead_out,0)- nvl(wpb.pl_material_overhead_out,0)- nvl(wpb.tl_material_overhead_var,0)- nvl(wpb.pl_material_overhead_var,0) + -- Resource Balance nvl(wpb.tl_resource_in,0)+ nvl(wpb.pl_resource_in,0)- nvl(wpb.tl_resource_out,0)- nvl(wpb.pl_resource_out,0)- nvl(wpb.tl_resource_var,0)- nvl(wpb.pl_resource_var,0)+ -- Outside Processing Balance nvl(wpb.tl_outside_processing_in,0)+ nvl(wpb.pl_outside_processing_in,0)- nvl(wpb.tl_outside_processing_out,0)- nvl(wpb.pl_outside_processing_out,0)- nvl(wpb.tl_outside_processing_var,0)- nvl(wpb.pl_outside_processing_var,0) + -- Overhead Balance nvl(wpb.tl_overhead_in,0)+ nvl(wpb.pl_overhead_in,0)- nvl(wpb.tl_overhead_out,0)- nvl(wpb.pl_overhead_out,0)- nvl(wpb.tl_overhead_var,0)- nvl(wpb.pl_overhead_var,0) + -- Estimated Scrap Balances nvl(wpb.tl_scrap_in,0)- nvl(wpb.tl_scrap_out,0)- nvl(wpb.tl_scrap_var,0) ),2) WIP_Value, -- Revision for version 1.15 round( sum(nvl(wpb.pl_material_in,0)+ nvl(wpb.pl_material_overhead_in,0)+ nvl(wpb.tl_resource_in,0)+ nvl(wpb.pl_resource_in,0)+ nvl(wpb.tl_outside_processing_in,0)+ nvl(wpb.pl_outside_processing_in,0)+ nvl(wpb.tl_overhead_in,0)+ nvl(wpb.pl_overhead_in,0)+ nvl(wpb.tl_scrap_in,0) ) ) WIP_Costs_In, -- WIP variance percentage = WIP Net Value / WIP Costs In -- WIP Net Value round( sum(nvl(wpb.pl_material_in,0)- nvl(wpb.tl_material_out,0)- nvl(wpb.pl_material_out,0)- nvl(wpb.tl_material_var,0)- nvl(wpb.pl_material_var,0) + -- Material Overhead Balance nvl(wpb.pl_material_overhead_in,0)- nvl(wpb.tl_material_overhead_out,0)- nvl(wpb.pl_material_overhead_out,0)- nvl(wpb.tl_material_overhead_var,0)- nvl(wpb.pl_material_overhead_var,0) + -- Resource Balance nvl(wpb.tl_resource_in,0)+ nvl(wpb.pl_resource_in,0)- nvl(wpb.tl_resource_out,0)- nvl(wpb.pl_resource_out,0)- nvl(wpb.tl_resource_var,0)- nvl(wpb.pl_resource_var,0)+ -- Outside Processing Balance nvl(wpb.tl_outside_processing_in,0)+ nvl(wpb.pl_outside_processing_in,0)- nvl(wpb.tl_outside_processing_out,0)- nvl(wpb.pl_outside_processing_out,0)- nvl(wpb.tl_outside_processing_var,0)- nvl(wpb.pl_outside_processing_var,0) + -- Overhead Balance nvl(wpb.tl_overhead_in,0)+ nvl(wpb.pl_overhead_in,0)- nvl(wpb.tl_overhead_out,0)- nvl(wpb.pl_overhead_out,0)- nvl(wpb.tl_overhead_var,0)- nvl(wpb.pl_overhead_var,0) + -- Estimated Scrap Balances nvl(wpb.tl_scrap_in,0)- nvl(wpb.tl_scrap_out,0)- nvl(wpb.tl_scrap_var,0) ) / decode( sum(nvl(wpb.pl_material_in,0)+ nvl(wpb.pl_material_overhead_in,0)+ nvl(wpb.tl_resource_in,0)+ nvl(wpb.pl_resource_in,0)+ nvl(wpb.tl_outside_processing_in,0)+ nvl(wpb.pl_outside_processing_in,0)+ nvl(wpb.tl_overhead_in,0)+ nvl(wpb.pl_overhead_in,0)+ nvl(wpb.tl_scrap_in,0) ), 0, 1, sum(nvl(wpb.pl_material_in,0)+ nvl(wpb.pl_material_overhead_in,0)+ nvl(wpb.tl_resource_in,0)+ nvl(wpb.pl_resource_in,0)+ nvl(wpb.tl_outside_processing_in,0)+ nvl(wpb.pl_outside_processing_in,0)+ nvl(wpb.tl_overhead_in,0)+ nvl(wpb.pl_overhead_in,0)+ nvl(wpb.tl_scrap_in,0) ) ) ,3) * 100 WIP_Variance_Percent, -- End revision for version 1.15 -- Check for Unprocessed Material -- Revision for version 1.3 -- nvl((select 'Yes' (select max(fl.meaning) from mtl_material_transactions_temp mmtt, fnd_lookups fl where mmtt.organization_id = wdj.organization_id and mmtt.transaction_source_type_id = 5 and mmtt.transaction_source_id = wdj.wip_entity_id and fl.lookup_type = 'YES_NO' and fl.lookup_code = 'Y' ) Unprocessed_Material, -- Check for Uncosted Material -- Revision for version 1.3 -- nvl((select 'Yes' (select max(fl.meaning) from mtl_material_transactions mmt1, fnd_lookups fl where mmt1.transaction_source_id = wdj.wip_entity_id and mmt1.organization_id = wdj.organization_id and mmt1.transaction_source_type_id = 5 and mmt1.costed_flag is not null and fl.lookup_type = 'YES_NO' and fl.lookup_code = 'Y' ) Uncosted_Material, -- Check for Pending WIP Costing -- Revision for version 1.3 -- nvl((select 'Yes' (select max(fl.meaning) from wip_cost_txn_interface wcti, fnd_lookups fl where wcti.wip_entity_id = wdj.wip_entity_id and wcti.organization_id = wdj.organization_id and fl.lookup_type = 'YES_NO' and fl.lookup_code = 'Y' ) Pending_WIP_Costing, -- Check for Pending Receiving -- Revision for version 1.3 -- nvl((select 'Yes' (select max(fl.meaning) from rcv_transactions_interface rti, fnd_lookups fl where rti.wip_entity_id = wdj.wip_entity_id and rti.to_organization_id = wdj.organization_id and fl.lookup_type = 'YES_NO' and fl.lookup_code = 'Y' ) Pending_Receiving, -- Revision for version 1.3 -- Check for Pending Purchase Requisitions (select max(fl.meaning) from po_requisitions_interface pri, wip_operation_resources wor, fnd_lookups fl where pri.wip_entity_id = wdj.wip_entity_id and pri.destination_organization_id = wdj.organization_id and wor.wip_entity_id = wdj.wip_entity_id and wor.organization_id = wdj.organization_id and wor.autocharge_type in ( 3, 4 ) -- 3 = 'PO receipt' and 4 = 'PO move' -- Only for jobs with no applied resource units, to avoid -- selecting duplicate purchase requisition interface entries and nvl(wor.applied_resource_units,0) = 0 and fl.lookup_type = 'YES_NO' and fl.lookup_code = 'Y' ) Pending_Requisitions, -- Check for Pending Material -- Revision for version 1.3 -- nvl((select 'Yes' (select max(fl.meaning) from mtl_transactions_interface mti, -- Revision for version 1.16 -- wip_discrete_jobs wdj, fnd_lookups fl where mti.transaction_source_id = wdj.wip_entity_id and mti.organization_id = wdj.organization_id and mti.transaction_source_type_id = 5 and fl.lookup_type = 'YES_NO' and fl.lookup_code = 'Y' ) Pending_Material, -- Check for Pending Shop Floor Move -- Revision for version 1.3 -- nvl((select 'Yes' (select max(fl.meaning) from wip_move_txn_interface wmti, -- Revision for version 1.16 -- wip_discrete_jobs wdj, fnd_lookups fl where wmti.wip_entity_id = wdj.wip_entity_id and wmti.organization_id = wdj.organization_id and fl.lookup_type = 'YES_NO' and fl.lookup_code = 'Y' ) Pending_Shop_Floor_Move, -- Check for WSM Split Merge Transactions -- Uncosted WSM starting jobs -- Uncosted WSM resulting jobs (select max(fl.meaning) from wsm_split_merge_transactions wsmt, wsm_sm_starting_jobs wssj, wsm_sm_resulting_jobs wsrj, fnd_lookups fl where ((wssj.wip_entity_id = wdj.wip_entity_id and wssj.organization_id = wdj.organization_id and wsmt.transaction_id = wssj.transaction_id) or (wsrj.wip_entity_id = wdj.wip_entity_id and wsrj.organization_id = wdj.organization_id and wsmt.transaction_id = wsrj.transaction_id) ) and wsmt.costed <> 4 and fl.lookup_type = 'YES_NO' and fl.lookup_code = 'Y' ) Uncosted_Split_Merge_Txn, -- Check for WSM Split Merge Transactions -- Pending WSM Jobs Interface - starting_jobs -- Pending WSM Jobs Interface - resulting_jobs (select max(fl.meaning) from wsm_split_merge_txn_interface wsmti, wsm_starting_jobs_interface wsji, wsm_resulting_jobs_interface wrji, fnd_lookups fl where (( wsji.wip_entity_id = wdj.wip_entity_id and wsji.organization_id = wdj.organization_id and wsmti.header_id = wsji.header_id ) or ( wrji.wip_entity_name = we.wip_entity_name and wrji.organization_id = we.organization_id and wsmti.header_id = wrji.header_id ) ) and wsmti.process_status <> 4 and fl.lookup_type = 'YES_NO' and fl.lookup_code = 'Y' ) Unprocessed_WSM_Txn_Interface, -- Check for Pending WSM Lots Interface - resulting_lots -- Revision for version 1.3 -- nvl((select 'Yes' (select max(fl.meaning) from wsm_resulting_lots_interface wrli, wsm_lot_split_merges_interface wlsmi, fnd_lookups fl where wrli.wip_entity_id = wdj.wip_entity_id and wrli.organization_id = wdj.organization_id and wlsmi.header_id = wrli.header_id and wlsmi.wip_flag = 1 and wlsmi.process_status <> 4 and fl.lookup_type = 'YES_NO' and fl.lookup_code = 'Y' ) Pending_Resulting_Lots, --Check for WSM_Lot_Job_Interface (select max(fl.meaning) from wsm_lot_job_interface wlji, fnd_lookups fl where wlji.wip_entity_id = wdj.wip_entity_id and wlji.organization_id = wdj.organization_id and wlji.process_status <> 4 and fl.lookup_type = 'YES_NO' and fl.lookup_code = 'Y' ) Pending_WSM_Job_Lots from wip_discrete_jobs wdj, wip_entities we, mtl_parameters mp, -- Revision for version 1.10 wip_parameters wp, wip_period_balances wpb, wip_accounting_classes wac, mtl_system_items_vl msiv, -- Revision for version 1.10 mtl_units_of_measure_vl muomv, mfg_lookups ml1, -- Class Type mfg_lookups ml2, -- Job Status -- Revision for version 1.14 mfg_lookups ml3, -- Planning Make Buy fnd_common_lookups fcl, mtl_item_status_vl misv, -- End revision for version 1.14 hr_organization_information hoi, hr_all_organization_units_vl haou, hr_all_organization_units_vl haou2, gl_ledgers gl, (-- Revision for version 1.4 -- Add a group by, need one row per WIP Entity ID -- to avoid cross-joining -- Revision for version 1.1 -- Interface select statements for WIP Jobs -- Check for Unprocessed Material select wip_interface_errs.wip_entity_id from ( select wcti.wip_entity_id wip_entity_id from wip_cost_txn_interface wcti union all -- Check for Pending Receiving select rti.wip_entity_id from rcv_transactions_interface rti where rti.wip_entity_id is not null union all -- Check for Pending Material select mti.transaction_source_id from mtl_transactions_interface mti where mti.transaction_source_type_id = 5 union all -- Check for Pending Shop Floor Move select wmti.wip_entity_id from wip_move_txn_interface wmti union all -- Check for WSM Split Merge Transactions select WSM.wip_entity_id from -- Uncosted WSM starting jobs (select wssj.wip_entity_id wip_entity_id from wsm_sm_starting_jobs wssj, wsm_split_merge_transactions wsmt where wsmt.transaction_id = wssj.transaction_id and wsmt.costed <> 4 union all -- Uncosted WSM resulting jobs select wsrj.wip_entity_id wip_entity_id from wsm_sm_resulting_jobs wsrj, wsm_split_merge_transactions wsmt where wsmt.transaction_id = wsrj.transaction_id and wsmt.costed <> 4 ) WSM union all -- Check for WSM Split Merge Interface Transactions select WSMI.wip_entity_id from -- Pending WSM Jobs Interface - starting_jobs (select wsji.wip_entity_id wip_entity_id from wsm_starting_jobs_interface wsji, wsm_split_merge_txn_interface wsmti where wsmti.header_id = wsji.header_id and wsmti.process_status <> 4 union all -- Pending WSM Jobs Interface - resulting_jobs select we.wip_entity_id wip_entity_id from wip_entities we, wsm_resulting_jobs_interface wrji, wsm_split_merge_txn_interface wsmti where wrji.wip_entity_name = we.wip_entity_name and wrji.organization_id = we.organization_id and wsmti.header_id = wrji.header_id and wsmti.process_status <> 4 ) WSMI union all -- Check for Pending WSM Lots Interface - resulting_lots select wrli.wip_entity_id from wsm_resulting_lots_interface wrli, wsm_lot_split_merges_interface wlsmi where wlsmi.header_id = wrli.header_id and wlsmi.wip_flag = 1 and wlsmi.process_status <> 4 union all --Check for WSM_Lot_Job_Interface select wlji.wip_entity_id from wsm_lot_job_interface wlji where wlji.process_status <> 4 -- Revision for version 1.3 union all -- Check for Pending Purchase Requisitions select pri.wip_entity_id from po_requisitions_interface pri, wip_operation_resources wor, wip_discrete_jobs wdj where pri.wip_entity_id = wor.wip_entity_id and pri.destination_organization_id = wor.organization_id and wor.autocharge_type in ( 3, 4 ) -- 3 = 'PO receipt' and 4 = 'PO move' -- Only for jobs with no applied resource units, to avoid -- selecting duplicate purchase requisition interface entries and nvl(wor.applied_resource_units,0) = 0 and wdj.wip_entity_id = wor.wip_entity_id and wdj.date_closed is null -- End revision for version 1.3 ) wip_interface_errs group by wip_interface_errs.wip_entity_id ) wip_interfaces -- WIP Joins where wdj.date_completed is not null -- wdj.status_type = 4 and wdj.date_closed is null and wdj.status_type = 4 -- Complete and we.wip_entity_id = wdj.wip_entity_id and mp.organization_id = wdj.organization_id -- Revision for version 1.10 and wp.organization_id = wdj.organization_id and wpb.wip_entity_id = wdj.wip_entity_id and wpb.organization_id = wdj.organization_id and wac.class_code = wdj.class_code and wac.organization_id = wdj.organization_id and wac.class_type in (1,3,5) -- ================================ -- 1 Standard Discrete -- 2 Repetitive Assembly -- 3 Asset Non-standard -- 4 Expense Non-standard -- 5 Standard Lot Based -- 6 Maintenance -- 7 Expense Non-standard Lot Based -- ================================ -- WIP Joins to WIP Interfaces and wip_interfaces.wip_entity_id (+) = wdj.wip_entity_id -- Item Master Joins and msiv.organization_id = wdj.organization_id and msiv.inventory_item_id = wdj.primary_item_id -- Revision for version 1.14 and msiv.inventory_item_status_code = misv.inventory_item_status_code -- Revision for version 1.10 and msiv.primary_uom_code = muomv.uom_code -- Lookup Joins and ml1.lookup_type = 'WIP_CLASS_TYPE' and ml1.lookup_code = wac.class_type and ml2.lookup_type = 'WIP_JOB_STATUS' and ml2.lookup_code = wdj.status_type -- Revision for version 1.14 and ml3.lookup_type = 'MTL_PLANNING_MAKE_BUY' and ml3.lookup_code = msiv.planning_make_buy_code and fcl.lookup_type (+) = 'ITEM_TYPE' and fcl.lookup_code (+) = msiv.item_type -- End revision for version 1.14 -- =========================================== -- Organization joins to the HR org model -- =========================================== and hoi.org_information_context = 'Accounting Information' and hoi.organization_id = mp.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 -- Revision for version 1.16 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 1=1 -- p_assembly_number, p_wip_job, p_wip_class_code, p_org_code, p_operating_unit, p_ledger -- Same joins as the view -- Quantities Completed and (wdj.start_quantity - (wdj.quantity_completed + decode(:p_include_scrap,'N',0, wdj.quantity_scrapped))) <= 0 -- Unprocessed Material and not exists (select 1 from mtl_material_transactions_temp mmtt where mmtt.organization_id = wdj.organization_id and mmtt.transaction_source_type_id = 5 and mmtt.transaction_source_id = wdj.wip_entity_id) -- Uncosted Material and not exists (select 1 from mtl_material_transactions mmt1 where mmt1.transaction_source_id = wdj.wip_entity_id and mmt1.organization_id = wdj.organization_id and mmt1.transaction_source_type_id = 5 and mmt1.costed_flag is not null) -- Uncosted WSM starting_jobs and not exists (select 1 from wsm_sm_starting_jobs wssj, wsm_split_merge_transactions wsmt where wssj.wip_entity_id = wdj.wip_entity_id and wssj.organization_id = wdj.organization_id and wsmt.transaction_id = wssj.transaction_id and wsmt.costed <> 4) -- Uncosted WSM resulting_jobs and not exists (select 1 from wsm_sm_resulting_jobs wsrj, wsm_split_merge_transactions wsmt where wsrj.wip_entity_id = wdj.wip_entity_id and wsrj.organization_id = wdj.organization_id and wsmt.transaction_id = wsrj.transaction_id and wsmt.costed <> 4) -- Pending WIP Costing and not exists (select 1 from wip_cost_txn_interface wcti where wcti.wip_entity_id = wdj.wip_entity_id and wcti.organization_id = wdj.organization_id ) -- Pending WSM Jobs Interface - starting_jobs and not exists (select 1 from wsm_starting_jobs_interface wsji, wsm_split_merge_txn_interface wsmti where wsji.wip_entity_id = wdj.wip_entity_id and wsji.organization_id = wdj.organization_id and wsmti.header_id = wsji.header_id and wsmti.process_status <> 4) -- Pending WSM Jobs Interface - resulting_jobs and not exists (select 1 from wsm_resulting_jobs_interface wrji, wsm_split_merge_txn_interface wsmti where we.wip_entity_id = wdj.wip_entity_id and wrji.wip_entity_name = we.wip_entity_name and wrji.organization_id = we.organization_id and wsmti.header_id = wrji.header_id and wsmti.process_status <> 4) -- Pending WSM Lots Interface - resulting_lots and not exists (select 1 from wsm_resulting_lots_interface wrli, wsm_lot_split_merges_interface wlsmi where wrli.wip_entity_id = wdj.wip_entity_id and wrli.organization_id = wdj.organization_id and wlsmi.header_id = wrli.header_id and wlsmi.wip_flag = 1 and wlsmi.process_status <> 4) -- WSM_Lot_Job_Interface and not exists (select 1 from wsm_lot_job_interface wlji where wlji.wip_entity_id = wdj.wip_entity_id and wlji.organization_id = wdj.organization_id and wlji.process_status <> 4) -- Pending Receiving and not exists (select 1 from rcv_transactions_interface rti where rti.wip_entity_id = wdj.wip_entity_id and rti.to_organization_id = wdj.organization_id ) -- Pending Material and not exists (select 1 from mtl_transactions_interface mti where mti.transaction_source_id = wdj.wip_entity_id and mti.organization_id = wdj.organization_id and mti.transaction_source_type_id = 5 and process_flag <> 9) -- Pending Shop Floor Move and not exists (select 1 from wip_move_txn_interface wmti where wmti.wip_entity_id = wdj.wip_entity_id and wmti.organization_id = wdj.organization_id) -- WIP Operation Resources with no earned OSP and not exists (select 1 from wip_operation_resources wor where wor.wip_entity_id = wdj.wip_entity_id and wor.organization_id = wdj.organization_id and wor.autocharge_type in ( 3, 4 ) and nvl(wor.applied_resource_units,0) = 0) -- WIP with material quantities not issued and not exists (select 1 from wip_requirement_operations wro, mtl_system_items_b msi, wip_parameters wp where wro.wip_entity_id = wdj.wip_entity_id and wro.organization_id = wdj.organization_id and msi.organization_id = wro.organization_id and msi.inventory_item_id = wro.inventory_item_id -- Only want to check valued items, not expense items and msi.inventory_asset_flag = 'Y' and wp.organization_id = wdj.organization_id -- Revision for version 1.12 and 2=2 -- Include WIP bulk supply types -- Revision for version 1.13 and wro.wip_supply_type <> 6 -- Phantom -- Calculate the quantity required based on the completion quantities -- Use the completion quantities with scrap quantities unless scrap is not financially recorded -- Basis of 2 indicates the component is issued per lot not per assembly and the component yield factor is ignored and round(decode(wro.basis_type, null, nvl(wro.quantity_per_assembly, 0) * 1 / nvl(wro.component_yield_factor, 1) * (nvl(wdj.quantity_completed, 0) + decode(:p_include_scrap,'N',0,nvl(wdj.quantity_scrapped, 0))), 1, nvl(wro.quantity_per_assembly, wdj.start_quantity) * 1 / nvl(wro.component_yield_factor, 1) * (nvl(wdj.quantity_completed, 0) + decode(:p_include_scrap,'N',0,nvl(wdj.quantity_scrapped, 0))), 2, nvl(wro.required_quantity,1), nvl(wro.quantity_per_assembly, 0) * 1 / nvl(wro.component_yield_factor, 1) * (nvl(wdj.quantity_completed, 0) + decode(:p_include_scrap,'N',0,nvl(wdj.quantity_scrapped, 0))) ) ,3) - round(nvl(wro.quantity_issued, 0),3) > 0 -- Quantity_Left_in_WIP ) group by nvl(gl.short_name, gl.name), haou2.name, mp.organization_code, -- Revision for version 1.6 -- 'p_date_completed', we.wip_entity_name, wac.class_code, ml1.meaning, -- Class Type we.wip_entity_name, we.organization_id, ml2.meaning, -- Job Status -- Revision for version 1.14 wdj.date_released, -- Revision for version 1.2 -- decode(wdj.attribute15, null, 'No', 'Yes'), wdj.date_completed, wdj.last_update_date, wdj.completion_subinventory, msiv.concatenated_segments, msiv.description, -- Revision for version 1.14 fcl.meaning, -- Item Type misv.inventory_item_status_code, -- Item Status ml3.meaning, -- Make Buy Code -- End revision for version 1.14 wdj.start_quantity, wdj.quantity_completed, wdj.quantity_scrapped, wp.mandatory_scrap_flag, -- Revision for version 1.10 muomv.uom_code, gl.currency_code, -- Needed for inline queries msiv.inventory_item_id, msiv.organization_id, wdj.organization_id, wdj.wip_entity_id, -- Revision for version 1.1 wip_interfaces.wip_entity_id -- =========================================== -- Check for variance tolerances -- =========================================== having abs(sum(nvl(wpb.pl_material_in,0)- nvl(wpb.tl_material_out,0)- nvl(wpb.pl_material_out,0)- nvl(wpb.tl_material_var,0)- nvl(wpb.pl_material_var,0) + -- Material Overhead Balance nvl(wpb.pl_material_overhead_in,0)- nvl(wpb.tl_material_overhead_out,0)- nvl(wpb.pl_material_overhead_out,0)- nvl(wpb.tl_material_overhead_var,0)- nvl(wpb.pl_material_overhead_var,0) + -- Resource Balance nvl(wpb.tl_resource_in,0)+ nvl(wpb.pl_resource_in,0)- nvl(wpb.tl_resource_out,0)- nvl(wpb.pl_resource_out,0)- nvl(wpb.tl_resource_var,0)- nvl(wpb.pl_resource_var,0)+ -- Outside Processing Balance nvl(wpb.tl_outside_processing_in,0)+ nvl(wpb.pl_outside_processing_in,0)- nvl(wpb.tl_outside_processing_out,0)- nvl(wpb.pl_outside_processing_out,0)- nvl(wpb.tl_outside_processing_var,0)- nvl(wpb.pl_outside_processing_var,0) + -- Overhead Balance nvl(wpb.tl_overhead_in,0)+ nvl(wpb.pl_overhead_in,0)- nvl(wpb.tl_overhead_out,0)- nvl(wpb.pl_overhead_out,0)- nvl(wpb.tl_overhead_var,0)- nvl(wpb.pl_overhead_var,0) + -- Estimated Scrap Balances nvl(wpb.tl_scrap_in,0)- nvl(wpb.tl_scrap_out,0)- -- Revision for version 1.6 nvl(wpb.tl_scrap_var,0))) < :p_wip_var_threshold -- Revision for version 1.15 and -- Revision for version 1.15 -- WIP variance percentage = WIP Net Value / WIP Costs In -- WIP Net Value abs(round( sum(nvl(wpb.pl_material_in,0)- nvl(wpb.tl_material_out,0)- nvl(wpb.pl_material_out,0)- nvl(wpb.tl_material_var,0)- nvl(wpb.pl_material_var,0) + -- Material Overhead Balance nvl(wpb.pl_material_overhead_in,0)- nvl(wpb.tl_material_overhead_out,0)- nvl(wpb.pl_material_overhead_out,0)- nvl(wpb.tl_material_overhead_var,0)- nvl(wpb.pl_material_overhead_var,0) + -- Resource Balance nvl(wpb.tl_resource_in,0)+ nvl(wpb.pl_resource_in,0)- nvl(wpb.tl_resource_out,0)- nvl(wpb.pl_resource_out,0)- nvl(wpb.tl_resource_var,0)- nvl(wpb.pl_resource_var,0)+ -- Outside Processing Balance nvl(wpb.tl_outside_processing_in,0)+ nvl(wpb.pl_outside_processing_in,0)- nvl(wpb.tl_outside_processing_out,0)- nvl(wpb.pl_outside_processing_out,0)- nvl(wpb.tl_outside_processing_var,0)- nvl(wpb.pl_outside_processing_var,0) + -- Overhead Balance nvl(wpb.tl_overhead_in,0)+ nvl(wpb.pl_overhead_in,0)- nvl(wpb.tl_overhead_out,0)- nvl(wpb.pl_overhead_out,0)- nvl(wpb.tl_overhead_var,0)- nvl(wpb.pl_overhead_var,0) + -- Estimated Scrap Balances nvl(wpb.tl_scrap_in,0)- nvl(wpb.tl_scrap_out,0)- nvl(wpb.tl_scrap_var,0) ) / -- WIP Costs In decode( sum(nvl(wpb.pl_material_in,0)+ nvl(wpb.pl_material_overhead_in,0)+ nvl(wpb.tl_resource_in,0)+ nvl(wpb.pl_resource_in,0)+ nvl(wpb.tl_outside_processing_in,0)+ nvl(wpb.pl_outside_processing_in,0)+ nvl(wpb.tl_overhead_in,0)+ nvl(wpb.pl_overhead_in,0)+ nvl(wpb.tl_scrap_in,0) ), 0, 1, sum(nvl(wpb.pl_material_in,0)+ nvl(wpb.pl_material_overhead_in,0)+ nvl(wpb.tl_resource_in,0)+ nvl(wpb.pl_resource_in,0)+ nvl(wpb.tl_outside_processing_in,0)+ nvl(wpb.pl_outside_processing_in,0)+ nvl(wpb.tl_overhead_in,0)+ nvl(wpb.pl_overhead_in,0)+ nvl(wpb.tl_scrap_in,0) ) ) ,3)) * 100 < :p_wip_var_percent Y #,##0.00000;[Red](#,##0.00000) US CAC WIP Jobs With Complete Status Which Are Ready for Close Report WIP jobs which have a status of "Complete", do not exceed variance tolerances, have completed or exceeded the WIP start quantity, with no open material requirements, no unearned OSP (outside processing) charges and no stuck transactions in interfaces. When you include scrap quantities, any scrapped assemblies are counted with the completed units. Note that for material requirements, expense items are ignored. Parameters: ========== Variance Amount Threshold: maximum absolute WIP variance or current job balance that is allowed for jobs you wish to close (required). Variance Percent Threshold: maximum absolute WIP variance percentage that is allowed for jobs you wish to close. Based on WIP Job Balance / WIP Costs In. (required). Include Scrap Quantities: include scrapped assemblies in completion and component material requirements (required). Include Bulk Supply Items: include bulk WIP supply types in the component requirements (required). 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). Organization Code: enter the specific inventory organization(s) you wish to report (optional). Class Code: enter the WIP class code to report (optional). WIP Job: enter the WIP Job to report (optional). Assembly Number: enter the specific assembly number(s) you wish to report (optional). Operating Unit: enter the specific operating unit(s) you wish to report (optional). Ledger: enter the specific ledger(s) you wish to report (optional). /* +=============================================================================+ -- | Copyright 2017 - 2024 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 A. Volz -- | -- | Version Modified on Modified by Description -- | ======= =========== ============= ========================================= -- | 1.0 16 MAR 2017 Douglas Volz Initial Coding -- | 1.1 19 MAR 2017 Douglas Volz Added interface conditions for eligibility -- | and check for no applied OSP -- | 1.3 27 MAR 2017 Douglas Volz Fix "return more than one row" error for -- | correlated sub-query on OSP and add in check for purchase requisitions -- | 1.4 27 APR 2017 Douglas Volz Fix for cross-joining results -- | 1.6 25 Oct 2017 Douglas Volz Remove p_date_completed parameter, not needed -- | 1.7 25 Jul 2018 Douglas Volz Removed all categories except Inventory -- | 1.8 25 Jul 2018 Douglas Volz Removed all category values -- | 1.9 11 Dec 2020 Douglas Volz Now for Standard, Lot Based Standard and Non- -- | Standard Asset Jobs. Added another category. -- | 1.10 26 Jan 2021 Douglas Volz Check for unissued materials and WIP scrap controls -- | 1.11 11 Feb 2021 Douglas Volz Added parameter to include scrap for requirements -- | 1.12 05 Mar 2021 Douglas Volz Added parameter to include bulk items for requirements. -- | 1.13 12 Mar 2021 Douglas Volz Add logic to ignore Phantom WIP Supply Types as -- | these requirements are never issued. -- | 1.14 15 Apr 2021 Douglas Volz Added Date Released -- | 1.15 10 Jul 2022 Douglas Volz Added WIP Variance Percentage parameter. -- | 1.16 21 Jan 2024 Douglas Volz Bug fix for Pending Material and Pending Shop Floor -- | Move. Remove tabs and add inventory access controls. -- +=============================================================================+*/ Enginatics R12 only &category_columns 1=1 2=2 :p_include_scrap :p_wip_var_percent :p_wip_var_threshold 1 10 :p_wip_var_threshold Number 500 Y US Variance Amount Threshold 2 20 :p_wip_var_percent Number 20 Y US Variance Percent Threshold 3 30 :p_include_scrap LOV Oracle Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description Y Y US Include Scrap Quantities 4 40 2=2 wro.wip_supply_type <> 4 LOV Oracle Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description N N Y US Include Bulk Supply Items 5 50 &category_columns select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>') sql_text from dual LOV INV Category Set 8E2FF36EDECA79D2E0530100007F1FF2 Y select distinct mcsv.category_set_name value, mcsv.description from mtl_category_sets_vl mcsv where :$flex$.organization_code is null or mcsv.category_set_id in (select mic.category_set_id from mtl_parameters mp, mtl_item_categories mic where xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' and mp.organization_id=mic.organization_id) order by mcsv.category_set_name coalesce(xxen_util.previous_parameter_value(:parameter_id),(select mdsv.category_set_name from mtl_default_sets_view mdsv where mdsv.functional_area_id=11)) US Category Set 1 6 60 &category_columns select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>') sql_text from dual LOV INV Category Set 8E2FF36EDECA79D2E0530100007F1FF2 Y select distinct mcsv.category_set_name value, mcsv.description from mtl_category_sets_vl mcsv where :$flex$.organization_code is null or mcsv.category_set_id in (select mic.category_set_id from mtl_parameters mp, mtl_item_categories mic where xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' and mp.organization_id=mic.organization_id) order by mcsv.category_set_name coalesce(xxen_util.previous_parameter_value(:parameter_id),(select mdsv.category_set_name from mtl_default_sets_view mdsv where mdsv.functional_area_id=5)) US Category Set 2 7 70 &category_columns select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>') sql_text from dual LOV INV Category Set 8E2FF36EDECA79D2E0530100007F1FF2 Y select distinct mcsv.category_set_name value, mcsv.description from mtl_category_sets_vl mcsv where :$flex$.organization_code is null or mcsv.category_set_id in (select mic.category_set_id from mtl_parameters mp, mtl_item_categories mic where xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' and mp.organization_id=mic.organization_id) order by mcsv.category_set_name xxen_util.previous_parameter_value(:parameter_id) US Category Set 3 8 80 1=1 mp.organization_code = :p_org_code LOV INV Organization Code (excluding master) 91D3167A33531204E053BB6B63585EE3 Y 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 (:$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 9 90 1=1 wdj.class_code = :p_class_code LOV WIP Class Code (org dependent) E7CCB27F39561272E0530100007F62B4 Y select distinct wac.class_code value, wac.description from mtl_parameters mp, wip_accounting_classes wac where (:$flex$.organization_code is null or xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y') and mp.organization_id=wac.organization_id order by wac.class_code US Class Code 10 100 1=1 we.wip_entity_name = :p_wip_job LOV WIP Job 8E2FF36EDEC379D2E0530100007F1FF2 Y Y select we.wip_entity_name value, xxen_util.meaning(we.entity_type,'WIP_ENTITY',700)||' ('||ood.organization_code||')'||nvl2(we.description,': '||we.description,null) description from wip_entities we, org_organization_definitions ood where (:$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 ( :$flex$.organization_code is null and ood.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) or xxen_util.contains(:$flex$.organization_code,ood.organization_code)='Y' ) and we.organization_id=ood.organization_id and nvl(ood.disable_date,sysdate)>=sysdate and we.entity_type in (1,3,5,8) --(Discrete job, Closed discrete job, Lot based job, Closed lot based job) order by we.creation_date desc US WIP Job 11 110 1=1 msiv.concatenated_segments = :p_item_number LOV INV Item (master, costing enabled only) 91D022B15B12FFB9E053BB6B63587F0B Y Y select msiv.concatenated_segments value, msiv.description from mtl_system_items_vl msiv where msiv.organization_id=( select max(mp.master_organization_id) master_organization_id from mtl_parameters mp where mp.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') or fnd_profile.value('MFG_ORGANIZATION_ID') is null 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 msiv.costing_enabled_flag='Y' order by msiv.concatenated_segments US Assembly Number 12 120 1=1 haou2.name = :p_operating_unit LOV INV Operating Unit 9B9627743E84607DE053BB6B635805FB Y 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=hoi.organization_id and hoi.org_information_context='Accounting Information' and hoi.organization_id=haou.organization_id and nvl(haou.date_to,sysdate)>=sysdate ) order by 1 US Operating Unit 13 130 1=1 gl.name = :p_ledger LOV GL Ledger (inv org related) 8E2FF36EDF3A79D2E0530100007F1FF2 Y 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 (:$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 :$flex$.ledger Ledger Organization Code :$flex$.ledger Ledger WIP Job :$flex$.operating_unit Operating Unit Organization Code :$flex$.organization_code Organization Code Category Set 1 :$flex$.organization_code Organization Code Category Set 2 :$flex$.organization_code Organization Code Class Code :$flex$.organization_code Organization Code WIP Job