Discrete Job Pick List to show item with 0 onhand

[uda2k2]

Discrete Job Pick List only shows layers that can be picked, but if a layer does not have any on hand qty - we want the pick list to show the item with zero on hand

FROM MFG_LOOKUPS ML1
, MFG_LOOKUPS ML2
, MTL_ITEM_LOCATIONS MIL
, MTL_ITEM_LOCATIONS MIL2
, MTL_ITEM_QUANTITIES_VIEW MOQ2
, WIP_ENTITIES WE
, MTL_SYSTEM_ITEMS MSI
, WIP_ACCOUNTING_CLASSES WAC
, MTL_SYSTEM_ITEMS MSI2
, WIP_REQUIREMENT_OPERATIONS WRO
, WIP_DISCRETE_JOBS WDJ
, mtl_item_revisions mir
, mtl_lot_numbers mln
, san_pcb_eng_data sped WHERE WDJ.ORGANIZATION_ID = _ORGANIZATION_ID
AND WDJ.STATUS_TYPE IN &C_Limit_Status
AND ML1.LOOKUP_TYPE = 'WIP_JOB_STATUS'
AND ML1.LOOKUP_CODE = WDJ.STATUS_TYPE
AND ML1.ENABLED_FLAG = 'Y'
AND MSI2.INVENTORY_ITEM_ID = MLN.INVENTORY_ITEM_ID(+)
and MLN.ORGANIZATION_ID(+) = _ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID(+) = WDJ.PRIMARY_ITEM_ID
AND MSI.ORGANIZATION_ID(+) = _ORGANIZATION_ID
AND WE.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
AND WE.ENTITY_TYPE = 1
AND WE.ORGANIZATION_ID = _ORGANIZATION_ID
&C_Limit_Jobs
--AND WE.WIP_ENTITY_NAME BETWEEN _FROM_JOB AND _TO_JOB
&C_Limit_Dates
AND WAC.CLASS_CODE = WDJ.CLASS_CODE
AND WAC.ORGANIZATION_ID = _ORGANIZATION_ID
AND WRO.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
AND WRO.ORGANIZATION_ID = _ORGANIZATION_ID
AND WRO.WIP_SUPPLY_TYPE <> 6
AND WRO.REQUIRED_QUANTITY > 0
AND WRO.REQUIRED_QUANTITY - WRO.QUANTITY_ISSUED > 0
&C_Limit_Supply_Type
&C_Limit_Subinv
AND ML2.LOOKUP_TYPE = 'WIP_SUPPLY_PICK'
AND ML2.LOOKUP_CODE = DECODE(WRO.WIP_SUPPLY_TYPE,2,2,3,2,WRO.WIP_SUPPLY_TYPE)
AND ML2.ENABLED_FLAG = 'Y'
AND MIL.INVENTORY_LOCATION_ID(+) = WRO.SUPPLY_LOCATOR_ID
AND MIL.ORGANIZATION_ID(+) = _ORGANIZATION_ID
AND MSI2.INVENTORY_ITEM_ID = WRO.INVENTORY_ITEM_ID
AND MSI2.ORGANIZATION_ID = WRO.ORGANIZATION_ID
AND MOQ2.ORGANIZATION_ID(+) = _ORGANIZATION_ID
AND MOQ2.INVENTORY_ITEM_ID(+) = WRO.INVENTORY_ITEM_ID
AND MIL2.INVENTORY_LOCATION_ID(+) = MOQ2.LOCATOR_ID
AND MIL2.ORGANIZATION_ID(+) = _ORGANIZATION_ID
and msi.inventory_item_id = mir.inventory_item_id
and msi.organization_id = mir.organization_id
AND MLN.LOT_NUMBER IS NULL
and sped.organization_id(+) = msi.organization_id sped.inventory_item_id(+) = msi.inventory_item_id and mir.effectivity_date =
(select distinct max(inner_rev.effectivity_date)
from mtl_item_revisions inner_rev
where inner_rev.inventory_item_id = msi.inventory_item_id
and inner_rev.organization_id = msi.organization_id
and inner_rev.effectivity_date <= sysdate)

***********************************************************
The current Discrete report is not picking the item which has got 0 on hand qty. Please advice me what i have to do in the above join condition to pick the item with 0 on hand qty. Thanks in advance.
--Uday

[uda2k2]

Guys this is urgent. Please help me

[kalpanamani1978]

your requirement is not clear, please explain more

[uda2k2]

ok. I have a custom WIP report. This report will show the details of the job, assembly and its components. But some components for an assembly were not shown in the report, whereas I can see those components in the front end for the assembly. I don't why those components are not pulling in my report. Need you help to fix this issue...

Thanks in advance,
Uday

[jasmine.hue]

[quote]Discrete Job Pick List only shows layers that can be picked, but if a layer does not have any on hand qty - we want the pick list to show the item with zero on hand

FROM MFG_LOOKUPS ML1
, MFG_LOOKUPS ML2
, MTL_ITEM_LOCATIONS MIL
, MTL_ITEM_LOCATIONS MIL2
, MTL_ITEM_QUANTITIES_VIEW MOQ2
, WIP_ENTITIES WE
, MTL_SYSTEM_ITEMS MSI
, WIP_ACCOUNTING_CLASSES WAC
, MTL_SYSTEM_ITEMS MSI2
, WIP_REQUIREMENT_OPERATIONS WRO
, WIP_DISCRETE_JOBS WDJ
, mtl_item_revisions mir
, mtl_lot_numbers mln
, san_pcb_eng_data sped WHERE WDJ.ORGANIZATION_ID = _ORGANIZATION_ID
AND WDJ.STATUS_TYPE IN &C_Limit_Status
AND ML1.LOOKUP_TYPE = 'WIP_JOB_STATUS'
AND ML1.LOOKUP_CODE = WDJ.STATUS_TYPE
AND ML1.ENABLED_FLAG = 'Y'
AND MSI2.INVENTORY_ITEM_ID = MLN.INVENTORY_ITEM_ID(+)
and MLN.ORGANIZATION_ID(+) = _ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID(+) = WDJ.PRIMARY_ITEM_ID
AND MSI.ORGANIZATION_ID(+) = _ORGANIZATION_ID
AND WE.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
AND WE.ENTITY_TYPE = 1
AND WE.ORGANIZATION_ID = _ORGANIZATION_ID
&C_Limit_Jobs
--AND WE.WIP_ENTITY_NAME BETWEEN _FROM_JOB AND _TO_JOB
&C_Limit_Dates
AND WAC.CLASS_CODE = WDJ.CLASS_CODE
AND WAC.ORGANIZATION_ID = _ORGANIZATION_ID
AND WRO.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
AND WRO.ORGANIZATION_ID = _ORGANIZATION_ID
AND WRO.WIP_SUPPLY_TYPE <> 6
AND WRO.REQUIRED_QUANTITY > 0
AND WRO.REQUIRED_QUANTITY - WRO.QUANTITY_ISSUED > 0
&C_Limit_Supply_Type
&C_Limit_Subinv
AND ML2.LOOKUP_TYPE = 'WIP_SUPPLY_PICK'
AND ML2.LOOKUP_CODE = DECODE(WRO.WIP_SUPPLY_TYPE,2,2,3,2,WRO.WIP_SUPPLY_TYPE)
AND ML2.ENABLED_FLAG = 'Y'
AND MIL.INVENTORY_LOCATION_ID(+) = WRO.SUPPLY_LOCATOR_ID
AND MIL.ORGANIZATION_ID(+) = _ORGANIZATION_ID
AND MSI2.INVENTORY_ITEM_ID = WRO.INVENTORY_ITEM_ID
AND MSI2.ORGANIZATION_ID = WRO.ORGANIZATION_ID
AND MOQ2.ORGANIZATION_ID(+) = _ORGANIZATION_ID
AND MOQ2.INVENTORY_ITEM_ID(+) = WRO.INVENTORY_ITEM_ID
AND MIL2.INVENTORY_LOCATION_ID(+) = MOQ2.LOCATOR_ID
AND MIL2.ORGANIZATION_ID(+) = _ORGANIZATION_ID
and msi.inventory_item_id = mir.inventory_item_id
and msi.organization_id = mir.organization_id
AND MLN.LOT_NUMBER IS NULL
and sped.organization_id(+) = msi.organization_id sped.inventory_item_id(+) = msi.inventory_item_id and mir.effectivity_date =
(select distinct max(inner_rev.effectivity_date)
from mtl_item_revisions inner_rev
where inner_rev.inventory_item_id = msi.inventory_item_id
and inner_rev.organization_id = msi.organization_id
and inner_rev.effectivity_date <= sysdate)

***********************************************************
The current Discrete report is not picking the item which has got 0 on hand qty. Please advice me what i have to do in the above join condition to pick the item with 0 on hand qty. Thanks in advance.
--Uday



<i><div align="right">Originally posted by uda2k2