This INV Item - Receiving Attributes query provides key receiving attributes of items, including receipt tolerances, routing method (standard, inspection, direct), exception handling actions, and UOM configurations. It ensures effective validation and control during item receipt processes in inventory management.
#sqlquery
SELECT
--SQL4Fusion (An Orbit Analytics Project)
--This query retrieves receipt control settings and UOM info for inventory items
HAOU.NAME ORGANIZATION_NAME,
DECODE(ESIB.RECEIPT_DAYS_EXCEPTION_CODE,'NONE', 'None','REJECT', 'Reject','WARNING', 'Warning',ESIB.RECEIPT_DAYS_EXCEPTION_CODE) RECEIPT_DAYS_EXCEPTION_POLICY,
ESIB.DAYS_EARLY_RECEIPT_ALLOWED DAYS_EARLY_ALLOWED,
ESIB.DAYS_LATE_RECEIPT_ALLOWED DAYS_LATE_ALLOWED,
DECODE(ESIB.QTY_RCV_EXCEPTION_CODE,
'NONE', 'None',
'REJECT', 'Reject',
'WARNING', 'Warning',
ESIB.QTY_RCV_EXCEPTION_CODE) QTY_RECEIPT_EXCEPTION_POLICY,
ESIB.QTY_RCV_TOLERANCE QTY_RECEIPT_TOLERANCE_PERCENT,
DECODE(ESIB.RECEIVING_ROUTING_ID,
'1', 'Standard',
'2', 'Inspection',
'3', 'Direct',
ESIB.RECEIVING_ROUTING_ID) RECEIVING_ROUTING_METHOD,
DECODE(ESIB.ENFORCE_SHIP_TO_LOCATION_CODE,
'NONE', 'None',
'REJECT', 'Reject',
'WARNING', 'Warning',
ESIB.ENFORCE_SHIP_TO_LOCATION_CODE) SHIP_TO_LOCATION_ENFORCEMENT,
ESIB.PRIMARY_UOM_CODE,
IIUV.UNIT_OF_MEASURE SECONDARY_UOM_CODE,
ESIB.SECONDARY_DEFAULT_IND IS_SECONDARY_UOM_DEFAULT,
ESIB.DUAL_UOM_DEVIATION_HIGH DUAL_UOM_HIGH_DEVIATION,
ESIB.DUAL_UOM_DEVIATION_LOW DUAL_UOM_LOW_DEVIATION,
ESIB.INVENTORY_ITEM_STATUS_CODE ITEM_STATUS_CODE,
ESIB.CREATION_DATE ITEM_CREATION_DATE,
ESIB.INVENTORY_ITEM_ID ITEM_ID,
HAOU.ORGANIZATION_ID INVENTORY_ORG_ID
FROM
EGP_SYSTEM_ITEMS_B ESIB,
HR_ALL_ORGANIZATION_UNITS HAOU,
INV_ITEM_UOMS_V IIUV
WHERE 1=1
AND HAOU.ORGANIZATION_ID = ESIB.ORGANIZATION_ID
AND IIUV.INVENTORY_ITEM_ID(+) = ESIB.INVENTORY_ITEM_ID
AND ESIB.SECONDARY_UOM_CODE = IIUV.UOM_CODE(+)