Home » SQL & PL/SQL » SQL & PL/SQL » How to show records on zero on hand (Job pick list) (EBiz, 11.5.10.2)
icon9.gif  How to show records on zero on hand (Job pick list) [message #349510] Mon, 22 September 2008 02:14 Go to next message
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 Razz

[Updated on: Mon, 22 September 2008 02:24] by Moderator

Report message to a moderator

Re: How to show records on zero on hand (Job pick list) [message #349514 is a reply to message #349510] Mon, 22 September 2008 02:21 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Do you think your post well-explains the issue ?
Explain it in detail with create table script and with insert script

Thumbs Up
Rajuvan.
Re: How to show records on zero on hand (Job pick list) [message #349589 is a reply to message #349514] Mon, 22 September 2008 05:22 Go to previous messageGo to next message
jasmine.hue
Messages: 17
Registered: August 2008
Location: Penang, Malaysia
Junior Member

Hi Rajuvan,

Thanks for your prompt reply.

Ok, let me explain. Attached is the file contains the data from application and my customized report. I have problem to display the records which is zero on hand. From the worksheet, I had mark the record that I missing.

Hope you can advice on my SQL.

Thanks.
Jasmine
  • Attachment: Pick List.PDF
    (Size: 62.90KB, Downloaded 2025 times)
Re: How to show records on zero on hand (Job pick list) [message #349598 is a reply to message #349510] Mon, 22 September 2008 05:46 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Hi Jasmine ,

Even Now, Its not clear Smile

Anyway , I think you need to use the Outerjoin to get the records with 'Zero on Hand' ( assumimg there wont be any record for zero) . Also use NVL to display zero .

You can refer outer join

Thumbs Up
Rajuvan.
Re: How to show records on zero on hand (Job pick list) [message #349809 is a reply to message #349598] Tue, 23 September 2008 03:39 Go to previous messageGo to next message
jasmine.hue
Messages: 17
Registered: August 2008
Location: Penang, Malaysia
Junior Member

Sad
Hi Rajuvan,

I had put the neccessary outer join, but still the records not appear.

Can you help to have a look at my sql statements?

Thanks,
Jasmine
Re: How to show records on zero on hand (Job pick list) [message #349811 is a reply to message #349510] Tue, 23 September 2008 03:47 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Quote:
I had put the neccessary outer join


I doubt Smile

At least you should put the outer join to all the MTL_ONHAND_QUANTITIES_DETAIL ( moh ) fields in the query .

Thumbs Up
Rajuvan.
Re: How to show records on zero on hand (Job pick list) [message #421313 is a reply to message #349510] Mon, 07 September 2009 07:44 Go to previous messageGo to next message
punithavel
Messages: 21
Registered: May 2007
Location: Chennai
Junior Member

All the items might not be showing because, mtl_onhand_quantities_detail table will store the onhand quantity for the transacted items only.

Thanks
Punith
Re: How to show records on zero on hand (Job pick list) [message #421316 is a reply to message #421313] Mon, 07 September 2009 07:51 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
[stage whisper] Psst Punith, Check out the date of the previous post. ... Wink [/stage whisper]
Move along here folks, nothing to see here.
Re: How to show records on zero on hand (Job pick list) [message #421319 is a reply to message #421316] Mon, 07 September 2009 08:06 Go to previous messageGo to next message
punithavel
Messages: 21
Registered: May 2007
Location: Chennai
Junior Member

Hi,

I saw you people were trying to find the solution, But i have mentioned the reason for this problem. Because I had the same problem, maybe some days later someone may have the same problem.So this explanation can be helpful to them.

Anyway thanks.
Punith Laughing
Re: How to show records on zero on hand (Job pick list) [message #421320 is a reply to message #421319] Mon, 07 September 2009 08:23 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
Ok, surely then it would be more helpful to future readers if you were to post how you resolved your problem.
Re: How to show records on zero on hand (Job pick list) [message #683745 is a reply to message #349510] Wed, 17 February 2021 11:22 Go to previous messageGo to next message
jchavez
Messages: 1
Registered: February 2021
Junior Member
Helo, Body,

I'm not an expert but I tok your script and add this and it is working I can see the Item with Zero On-hand (well, appear in blank not Zero but at least you can identify those items)

after From I added this as a table:

(select MOH.*,mil.SEGMENT1
from inv.MTL_ONHAND_QUANTITIES_DETAIL MOH left join inv.mtl_item_locations mil on moh.ORGANIZATION_ID = mil.ORGANIZATION_ID and MOH.ORGANIZATION_ID =85
and moh.LOCATOR_ID = mil.INVENTORY_LOCATION_ID) oh,

then removed these tables:
inv.MTL_ONHAND_QUANTITIES_DETAIL MOH
--, inv.mtl_item_locations mil

and finally did this

and DJ_REQ.ORGANIZATION_ID = oh.ORGANIZATION_ID (+)
AND DJ_REQ.INVENTORY_ITEM_ID = oh.INVENTORY_ITEM_ID (+)

just replace the MOH and Mil by OH

that's all

good luck my friend !!


Re: How to show records on zero on hand (Job pick list) [message #683747 is a reply to message #683745] Wed, 17 February 2021 12:16 Go to previous message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
A 12 year old post? Really?
Previous Topic: date validation
Next Topic: postgres Range Type equivalent in oracle.
Goto Forum:
  


Current Time: Fri Mar 29 03:35:24 CDT 2024