CAC WIP Jobs With Complete Status Which Are Ready for Close

Description
Categories: Enginatics
Repository: Github
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, expen ...  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.
-- | Copyright 2017 - 2022 Douglas Volz Consulting, Inc. |
-- | All rights reserved. |
-- | Original Author: Douglas A. Volz
-- |
-- | Program Name: xxx_wip_jobs_status_eligible.sql
-- |
-- | Parameters:
-- | p_wip_var_threshold -- Maximum WIP variance or current job balance that is allowed
-- | for jobs you wish to close
-- | p_wip_var_percent -- Maximum WIP variance percentage that is allowed for jobs you
-- | wish to close. Based on WIP Job Balance / WIP Costs In.
-- | p_include_scrap -- Include scrap for quantity requirements
-- | p_include_bulk_items -- Include bulk WIP supply types in the component requirements
-- | p_assembly_number -- Enter the specific assembly number you wish to report (optional)
-- | p_wip_job -- Specific WIP job (optional)
-- | p_job_status -- Specific WIP job status (optional)
-- | p_wip_class_code -- Specific WIP class code (optional)
-- | 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 determine which Jobs are at the status "Complete" and are eligible
-- | for moving to the status "Complete - No Charges".
-- |
-- | Version Modified on Modified by Description
-- | ======= =========== ============= =========================================
-- | 1.0 16 MAR 2017 Douglas Volz Initial Coding
-- | 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.5 25 May 2017 Douglas Volz Added business code, product family,
-- | product line and package code item categories
-- | 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
-- | and now use multi-language tables or views.
-- | 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.
-- +=============================================================================+*/
   more
Run CAC WIP Jobs With Complete Status Which Are Ready for Close and other Oracle EBS reports with Blitz Report™ on our demo environment
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 Item_Number,
	msiv.description Item_Description,
	-- Revision for version 1.9
&category_columns
	-- End revision for version 1.9
	-- 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,
		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,
		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, 
	mfg_lookups ml2,
	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