We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

Adding results where transaction_quantity is null

gosi75
gosi75 asked
on
Medium Priority
604 Views
Last Modified: 2013-12-07
I'm altering a report that show the quantity on stock owned by the supplier and the value of that stock. I would also like to show the products that have 0 (zero) quantity registreret on the supplier.  As I'm a novice in PL/SQL I'm not sure what would be the best approach to solve this. If my question is not detailed enough, just let me know and I will try to explain this in more detail.


select moh.inventory_item_id,
       moh.organization_id organization_id,
       sum(moh.transaction_quantity)
       ,(sum(moh.transaction_quantity) * pol.unit_price)
       ,moh.subinventory_code
       ,il.segment1
        
        ,il.description 
       ,moh.is_consigned
       ,pol.unit_price 
       ,poh.currency_code 
       ,v.vendor_name
       ,i.description
 from  apps.MTL_ONHAND_QUANTITIES_DETAIL moh, 
       apps.cst_item_costs ic, 
       apps.mtl_item_locations il, 
       apps.PO_HEADERS_ALL poh, 
       apps.PO_LINES_ALL pol, 
       apps.po_vendors v, 
       apps.po_vendor_sites_all vs,
       apps.mtl_system_items_b i 
 
where  moh.inventory_item_id = ic.inventory_item_id
      and moh.organization_id = ic.organization_id
      and moh.organization_id = il.organization_id(+)
      and moh.subinventory_code=il.subinventory_code(+)
      and moh.locator_id = il.inventory_location_id(+)
      and moh.is_consigned = '1'  
      and poh.po_header_id = pol.po_header_id 
      and pol.item_id=moh.inventory_item_id 
      and moh.owning_organization_id is not null
      and moh.owning_tp_type <> '2'
      and moh.owning_organization_id=vs.vendor_site_id 
      and v.vendor_id=vs.vendor_id 
  and poh.type_lookup_code like 'BLANKET%'
  and i.inventory_item_id(+) = moh.inventory_item_id
  and i.organization_id(+) =moh.organization_id
group by moh.inventory_item_id,
          moh.organization_id,
          moh.subinventory_code,
          ic.item_cost,
          il.segment1,
          il.description,
          moh.is_consigned,
          pol.unit_price, 
          poh.currency_code,
          v.vendor_name,
          i.description

Open in new window

Comment
Watch Question

SharathData Engineer
CERTIFIED EXPERT

Commented:
What is the result of your query and what are you expecting?
gosi75Business Intelligence Consultant

Author

Commented:
I get results where there is always existing some transaction_quantity, moh.transaction_quantity is the amount on stock owned by the supplier. I would like to join on the apps.mtl_system_items_b table so that I will get the total item list and add it to the result. That is, showing the transaction quantity when it
exists but also showing the rest of the items from the apps.mtl_system_items_b table.

I hope this helps, If not I will try to be more precise.
Information Technology Specialist
CERTIFIED EXPERT
Commented:
Use nvl(moh.transaction_quantity,0) in place of moh.transaction_quantity.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
awking00Information Technology Specialist
CERTIFIED EXPERT

Commented:
B? Did that not work?
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.