Home » SQL & PL/SQL » SQL & PL/SQL » How to show records on zero on hand (Job pick list) (EBiz, 11.5.10.2)
How to show records on zero on hand (Job pick list) [message #349510] |
Mon, 22 September 2008 02:14 |
jasmine.hue
Messages: 17 Registered: August 2008 Location: Penang, Malaysia
|
Junior Member |
|
|
Hi,
I am writing a sql code to replace the Oracle standard report on Discrete Job Pick List Report. But I have problem to display the materials with zero on hand.
Below is the code, hope someone can advice.
--***** Report Title : Discrete Job Pick List Report *****--
--***** Report Revision : 1.0 *****--
--***** Objective : To replace Oracle Standard Pick List Report *****--
--***** Prepared By : Jasmine Hue *****--
--***** Report Released Date : 28 April 2008 *****--
select moh.ORGANIZATION_ID "Org"
, WIP.WIP_ENTITY_NAME "DJ_NUM"
, (ASSY.SEGMENT1||'-'||ASSY.SEGMENT2||'-'||ASSY.SEGMENT3) "ASSEMBLY"
, ASSY.DESCRIPTION "ASSY_NAME"
, LOOKUPS.MEANING "DJ_STATUS"
, DJ.START_QUANTITY "SCHED_QTY"
, DJ.QUANTITY_COMPLETED "COMPLETED_QTY"
, DJ.COMPLETION_SUBINVENTORY "COMPLETION_SUBINV"
, (MSI.SEGMENT1||'-'||MSI.SEGMENT2||'-'||MSI.SEGMENT3) "COMPONENTS"
, MSI.DESCRIPTION "ITEM_DESC"
, DJ_REQ.QUANTITY_PER_ASSEMBLY "PER_ASSY"
, DJ_REQ.REQUIRED_QUANTITY "REQ_QTY"
, DJ_REQ.QUANTITY_ISSUED "QTY_CHARGED"
, DJ_REQ.QUANTITY_ALLOCATED "QTY_ALLOC"
, DJ_REQ.QUANTITY_BACKORDERED "BACKORD_QTY"
, LOOKUP.MEANING "SUPPLY_TYPE"
, sum(moh.PRIMARY_TRANSACTION_QUANTITY) "OH_QTY"
, MOH.REVISION "OH_REV"
, MOH.SUBINVENTORY_CODE "OH_SUBINV"
, (mil.SEGMENT1||'.'||paa.NAME||'.'||pat.TASK_NUMBER) "OH_LOC"
from MTL_ONHAND_QUANTITIES_DETAIL MOH
, mtl_item_locations mil
, PA_PROJECTS_ALL PAA
, PA_TASKS PAT
, WIP_ENTITIES WIP
, WIP_DISCRETE_JOBS DJ
, WIP_REQUIREMENT_OPERATIONS DJ_REQ
, MFG_LOOKUPS LOOKUP
, MFG_LOOKUPS LOOKUPS
, MTL_SYSTEM_ITEMS MSI
, MTL_SYSTEM_ITEMS ASSY
where --moh.INVENTORY_ITEM_ID = 17773--25294
moh.ORGANIZATION_ID = mil.ORGANIZATION_ID
and moh.LOCATOR_ID = mil.INVENTORY_LOCATION_ID
and moh.PROJECT_ID = paa.PROJECT_ID
and moh.TASK_ID = pat.TASK_ID
and moh.PROJECT_ID = pat.PROJECT_ID
and WIP.ORGANIZATION_ID = DJ.ORGANIZATION_ID
AND WIP.WIP_ENTITY_ID = DJ.WIP_ENTITY_ID
AND WIP.PRIMARY_ITEM_ID = DJ.PRIMARY_ITEM_ID
AND DJ_REQ.ORGANIZATION_ID = DJ.ORGANIZATION_ID
AND DJ_REQ.WIP_ENTITY_ID = DJ.WIP_ENTITY_ID
AND LOOKUP.LOOKUP_CODE = DJ_REQ.WIP_SUPPLY_TYPE
AND LOOKUP.LOOKUP_TYPE = 'WIP_SUPPLY'
AND LOOKUPS.LOOKUP_CODE = DJ.STATUS_TYPE
AND LOOKUPS.LOOKUP_TYPE = 'WIP_JOB_STATUS'
--AND WIP.WIP_ENTITY_NAME in ('28730')
AND LOOKUP.MEANING not in ('Phantom')
AND DJ_REQ.ORGANIZATION_ID = MOH.ORGANIZATION_ID(+)
AND DJ_REQ.INVENTORY_ITEM_ID = MOH.INVENTORY_ITEM_ID(+)
AND WIP.PRIMARY_ITEM_ID = ASSY.INVENTORY_ITEM_ID
AND WIP.ORGANIZATION_ID = ASSY.ORGANIZATION_ID
AND DJ_REQ.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND DJ_REQ.ORGANIZATION_ID = MSI.ORGANIZATION_ID(+)
group by moh.INVENTORY_ITEM_ID
, moh.ORGANIZATION_ID--, sum(moh.PRIMARY_TRANSACTION_QUANTITY)
, mil.SEGMENT1||'.'||paa.NAME||'.'||pat.TASK_NUMBER
, WIP.WIP_ENTITY_NAME,DJ.START_QUANTITY
, DJ.QUANTITY_COMPLETED
, DJ.COMPLETION_SUBINVENTORY
, DJ_REQ.QUANTITY_PER_ASSEMBLY
, DJ_REQ.REQUIRED_QUANTITY
, DJ_REQ.QUANTITY_ISSUED
, DJ_REQ.QUANTITY_ALLOCATED
, DJ_REQ.QUANTITY_BACKORDERED
, LOOKUP.MEANING
, LOOKUPS.MEANING
, MSI.DESCRIPTION
, MOH.REVISION
, MOH.SUBINVENTORY_CODE
, ASSY.SEGMENT1||'-'||ASSY.SEGMENT2||'-'||ASSY.SEGMENT3
, ASSY.DESCRIPTION
, MSI.SEGMENT1||'-'||MSI.SEGMENT2||'-'||MSI.SEGMENT3
order by MSI.SEGMENT1||'-'||MSI.SEGMENT2||'-'||MSI.SEGMENT3,MOH.SUBINVENTORY_CODE
You can reach me at
email: jasmine.hue@lkt.com.my
yahoo: lyhue802002
skype: jasminehue
Thanks,
Jasmine
[Updated on: Mon, 22 September 2008 02:24] by Moderator Report message to a moderator
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Jun 29 06:38:38 CDT 2024
|