SQL Query for Oracle Fusion/Cloud Inventory Onhand Quantity - Related Items Available To Transact(ATT) Onhand

 select xxegp.item_number,

       xxegp.related_item,
       iop.organization_code related_org,
       ioqd.subinventory_code,
       nvl(sum(ioqd.transaction_quantity),0) -
       nvl((select sum(reservation_quantity)
             from inv_reservations
            where inventory_item_id=egib.inventory_item_id
              and organization_id  =egib.organization_id
              and subinventory_code=ioqd.subinventory_code
           ),0) ATT
  from (select egib.item_number,
		       egib2.item_number related_item,
		       egib2.inventory_item_id       
		  from egp_system_items_b egib,
		       EGP_ITEM_RELATIONSHIPS_B eir,
		       egp_system_items_b egib2
		 where egib.inventory_item_id = eir.inventory_item_id
		   and egib.organization_id   = eir.organization_id
		   and egib2.inventory_item_id= eir.RELATED_ITEM_ID
		   and egib2.organization_id  = eir.organization_id
        ) xxegp,
        egp_system_items_b egib,
        inv_onhand_quantities_detail ioqd,
        inv_org_parameters iop
  where egib.inventory_item_id=xxegp.inventory_item_id
    and egib.organization_id  =iop.organization_id
    and iop.master_organization_id!=iop.organization_id
    and ioqd.organization_id  =egib.organization_id
    and ioqd.inventory_item_id=egib.inventory_item_id
  group by xxegp.item_number,
       xxegp.related_item,
       egib.inventory_item_id,
       egib.organization_id,
       iop.organization_code,
       ioqd.subinventory_code

Comments

Post a Comment

Popular posts from this blog

SQL Query for User Defined Key Flexfields (KFF) Segments in oracle fusion/cloud

  Oracle Fusion User Defined Key Flexfields (KFF) Segments: select c.name ,c.key_flexfield_code,a.structure_code,b.* from FND_KF_STRUCTURES_B a, fnd_kf_segments_b b, FND_KF_FLEXFIELDS_TL c where c.language ='US' and a.structure_id = b.structure_id and a.key_flexfield_code = c.key_flexfield_code and a.application_id = c.application_id and b.created_by <> 'SEED_DATA_FROM_APPLICATION' and a.created_by <> 'SEED_DATA_FROM_APPLICATION'
Read more

Sql Query for Oracle Fusion/Cloud Shipping parameters and rule

  Shipping parameters and rule select a.organization_code,b.ship_confirm_rule_name from WSH_SHIPPING_PARAMETERS a, WSH_SHIP_CONFIRM_RULES b, hr_locations_all l1, inv_org_parameters d where a.organization_id = l1.inventory_organization_id and a.organization_code =d.organization_code and a.ship_confirm_rule_id = b.ship_confirm_rule_id and l1.country IN ('US')
Read more