Dear Friends 

Below is the important queries for BI Report in Fusion on ON hand Quantities


------------------ON HAND QTY--------------

select ESI.ITEM_NUMBER ,

  IODV.ORGANIZATION_NAME,

  ESI.DESCRIPTION ,

  IOP.ORGANIZATION_CODE ,

  SUM(IOQD.TRANSACTION_QUANTITY) TRX_QTY,

  UOMT.UNIT_OF_MEASURE ,

  IOQD.SUBINVENTORY_CODE ,

  IOQD.LOT_NUMBER,

  IIL.SEGMENT1,

  IIL.SEGMENT2,

  IIL.SEGMENT3,

  IIL.SEGMENT4

from INV_ONHAND_QUANTITIES_DETAIL IOQD ,

  EGP_SYSTEM_ITEMS ESI ,

  INV_ORG_PARAMETERS IOP ,

  INV_ORGANIZATION_DEFINITIONS_V IODV,

  INV_UNITS_OF_MEASURE_TL UOMT ,

  INV_UNITS_OF_MEASURE_B UOMB,

  INV_ITEM_LOCATIONS IIL

where 1                     = 1

and IODV.ORGANIZATION_ID    =IOP.ORGANIZATION_ID

and IOQD.INVENTORY_ITEM_ID  = ESI.INVENTORY_ITEM_ID

and IOQD.ORGANIZATION_ID    = ESI.ORGANIZATION_ID

and ESI.ORGANIZATION_ID     = IOP.ORGANIZATION_ID

and UOMT.UNIT_OF_MEASURE_ID = UOMB.UNIT_OF_MEASURE_ID

and UOMB.UOM_CODE           = IOQD.TRANSACTION_UOM_CODE

and IOQD.ORGANIZATION_ID    = IIL.ORGANIZATION_ID(+)

and IOQD.SUBINVENTORY_CODE  = IIL.SUBINVENTORY_CODE(+)

and IOQD.LOCATOR_ID         =IIL.INVENTORY_LOCATION_ID(+)

and ESI.ITEM_NUMBER        in '<Item Numbers>'

group by ESI.ITEM_NUMBER ,

  IODV.ORGANIZATION_NAME,

  ESI.DESCRIPTION ,

  IOP.ORGANIZATION_CODE ,

  UOMT.UNIT_OF_MEASURE ,

  IOQD.SUBINVENTORY_CODE ,

  IOQD.LOT_NUMBER,

  IIL.SEGMENT1,

  IIL.SEGMENT2,

  IIL.SEGMENT3,

  IIL.SEGMENT4

order by 1,2



Comments

Post a Comment

Popular posts from this blog

How to Customize AP Invoice Approval in Oracle Fusion cloud Application using Business Process Management(BPM)

Image
Dear Viewers ,   Lets Understand with different case studies How to Customize AP Invoice Approval in Oracle Fusion cloud Application using Business Process Management(BPM) .   Case Study 1 #Configure an approval rule to route a notification to a requestor  It covers the most common business use cases of invoice approval in a Payables Department.   Each business use case provides how to configure the invoice approval rules, using various participants, invoice attributes, and list builders such as:   • Single Approver   • Approval Group   • Supervisor Hierarchy   • Job level Hierarchy   • Position Hierarchy   The information presented in this document is intended to help various users (Payables business users, Approval workflow administrators and Consultants) who are involved in the configuration of invoice approval policy to meet the various business use cases of invoice approvals in a Payables...
Read more
Image
Sometime , We need to know specific relationship between some important tables . Lets come to know one such important SQL Query to know the relationship between Transfer Order, Pick Slip Number, and Supply Orchestration SQL Query can be used to show the link between a Transfer Order, Pick Slip Number, and Supply Orchestration Belo is the screenshot for instance    The Required output We need from the report is below  The Query used for above requirement is below  SELECT  a.pick_slip_number ,   b.source_header_number transfer_order_number,   c.supply_order_number ,   c.supply_order_reference_number FROM   INV_PICK_SLIP_NUMBERS a ,   WSH_DELIVERY_DETAILS b ,   DOS_SUPPLY_HEADERS c ,   INV_TRANSFER_ORDER_HEADERS d,   INV_TRANSFER_ORDER_LINES e WHERE 1 =1   AND a.PICK_SLIP_NUMBER = 11022   AND a.pick_slip_batch_id = b.batch_id   AND b.source_header_number = d.header_number   AND d.header_id = e.header_...
Read more

Experience the Power of SQL Function

Dear Friends   Let's experience the Power of SQL Function.   Oracle Functions are built into ORACLE and are available for use in Various Appropriate SQL Statement. ·         The SQL Function Can is used to                       i.        Perform Calculation on Data                      ii.        Modify Individual Data item                     iii.        Manipulate output for Groups of Rows.                    iv.  ...
Read more