Sql Query to Extract the Move Order Issue/Move Order data in Oracle Fusion

Sql Query to Extract the Move Order Issue/Move Order data in Oracle Fusion

Hi Friends , we are going to discuss about the sql query which helps to extract the move order/move order issue in sql query. This sql query which helps you to extract the complete move order details in oracle fusion. Using this query , we can develop the Oracle Fusion BIP report for move order report. Move order reports belongs to Inventory module. This is one of the most important report in oracle fusion to extract the complete details about the move order created in oracle fusion system. The good part is , using this query we can find the source and destination sub inventory. Please find below the complete details about Sql Query to Extract the Move Order Issue/Move Order data in Oracle Fusion.

Important Tables to develop the Sql Query to Extract the Move Order Issue/Move Order data in Oracle Fusion

1.egp_system_items_vl

2.inv_units_of_measure_vl

3.INV_TRANSFER_ORDER_LINES

4.INV_TRANSFER_ORDER_DISTRIBS

5.INV_TRANSFER_ORDER_HEADERS

Detail SQL to Extract the Move Order Issue/Move Order data in Oracle Fusion

Here below is the sql query to extract the develop the move order issue/transfer report in oracle fusion.

SELECT 

ITH.HEADER_NUMBER,

ITL.SUPPLY_ORDER_REFERENCE_NUMBER,

ITL.LINE_NUMBER,

ESI.ITEM_NUMBER,

ESI.DESCRIPTION,

UOM.UNIT_OF_MEASURE,

PRH.CREATED_BY,

hauft.NAME,

hauft12.NAME source_name,

hauft22.NAME dest_name,

ITL.SHIPPED_QTY,

ITL.UNIT_PRICE,

ITL.SHIPPED_QTY * ITL.UNIT_PRICE AS Amount,

ITL.STATUS_LOOKUP,

ITL.LAST_UPDATE_DATE

from

INV_TRANSFER_ORDER_HEADERS ITH ,

INV_TRANSFER_ORDER_LINES ITL ,

INV_TRANSFER_ORDER_DISTRIBS ITD,

egp_system_items_vl  ESI,

inv_units_of_measure_vl UOM,

POR_REQUISITION_HEADERS_ALL PRH,

per_users  PU,

per_all_assignments_m PASM,

HR_ORG_UNIT_CLASSIFICATIONS_F houcf, 

HR_ALL_ORGANIZATION_UNITS_F haouf, 

HR_ORGANIZATION_UNITS_F_TL hauft,

HR_ORGANIZATION_UNITS_F_TL hauft12,

HR_ORGANIZATION_UNITS_F_TL hauft22

where

ITH.HEADER_ID = ITL.HEADER_ID

AND  ITL.INVENTORY_ITEM_ID = ESI.INVENTORY_ITEM_ID

AND QTY_UOM_CODE=UOM.UOM_CODE

AND PRH.REQUISITION_NUMBER = ITL.SUPPLY_ORDER_REFERENCE_NUMBER

AND PU.USERNAME = PRH.CREATED_BY

AND PASM.PERSON_ID = PU.PERSON_ID

AND haouf.ORGANIZATION_ID = houcf.ORGANIZATION_ID 

AND haouf.ORGANIZATION_ID = hauft.ORGANIZATION_ID 

and hauft.organization_id = PASM.ORGANIZATION_ID 

AND hauft.LANGUAGE= 'US'

and     sysdate between PASM.effective_start_date and PASM.effective_end_date

and ITL.STATUS_LOOKUP = 'CLOSED'

and itl.SOURCE_ORGANIZATION_ID=hauft12.organization_id

and itl.destination_ORGANIZATION_ID=hauft22.organization_id

order by ITH.HEADER_NUMBER,ITL.LINE_NUMBER