VIEW: APPS.MTL_ITEM_LOT_UOM_CONV_ERV

Source

View Text - Preformatted

SELECT TO_CHAR(LUC.CONVERSION_ID)||'-'||TO_CHAR(MLA.CONV_AUDIT_ID) EVENT_ID , 'LOT' CONVERSION_TYPE, LUC.CONVERSION_ID CONVERSION_ID, MLA.CONV_AUDIT_ID CONV_AUDIT_ID, LUC.LOT_NUMBER LOT_NUMBER, LUC.ORGANIZATION_ID ORGANIZATION_ID, LUC.INVENTORY_ITEM_ID INVENTORY_ITEM_ID, MSI.CONCATENATED_SEGMENTS ITEM_NUMBER, MSI.DESCRIPTION ITEM_DESCRIPTION, MP.ORGANIZATION_CODE ORGANIZATION_CODE, H.NAME ORGANIZATION_NAME, LUC.FROM_UNIT_OF_MEASURE FROM_UNIT_OF_MEASURE, LUC.FROM_UOM_CODE FROM_UOM_CODE, LUC.FROM_UOM_CLASS FROM_UOM_CLASS, LUC.TO_UNIT_OF_MEASURE TO_UNIT_OF_MEASURE, LUC.TO_UOM_CODE TO_UOM_CODE, LUC.TO_UOM_CLASS TO_UOM_CLASS, LUC.CONVERSION_RATE CONVERSION_RATE, LUC.DISABLE_DATE DISABLE_DATE, MLK.MEANING UPDATE_TYPE, MSI.PRIMARY_UOM_CODE PRIMARY_UOM_CODE, MSI.SECONDARY_UOM_CODE SECONDARY_UOM_CODE, MLA.BATCH_ID BATCH_ID, GBH.BATCH_NO BATCH_NO, MR.REASON_NAME REASON_NAME, LUC.CREATED_BY CREATED_BY, LUC.CREATION_DATE CREATION_DATE, LUC.LAST_UPDATED_BY LAST_UPDATED_BY, LUC.LAST_UPDATE_DATE LAST_UPDATE_DATE FROM MTL_LOT_UOM_CLASS_CONVERSIONS LUC, HR_ALL_ORGANIZATION_UNITS H, MTL_PARAMETERS MP, MTL_SYSTEM_ITEMS_KFV MSI, MTL_LOT_CONV_AUDIT MLA, MFG_LOOKUPS MLK, MTL_TRANSACTION_REASONS MR, GME_BATCH_HEADER GBH WHERE LUC.ORGANIZATION_ID = H.ORGANIZATION_ID AND H.ORGANIZATION_ID = MP.ORGANIZATION_ID AND LUC.ORGANIZATION_ID = MSI.ORGANIZATION_ID AND LUC.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID AND LUC.CONVERSION_ID = MLA.CONVERSION_ID AND MLA.UPDATE_TYPE_INDICATOR = MLK.LOOKUP_CODE AND MLK.lookup_type = 'INV_UPDATE_TYPE' AND MLA.REASON_ID = MR.REASON_ID(+) AND MLA.BATCH_ID = GBH.BATCH_ID(+) UNION SELECT TO_CHAR(MP.MASTER_ORGANIZATION_ID)||'-'||TO_CHAR(MUC.INVENTORY_ITEM_ID)||'-'||MUOM.UOM_CODE||'-'||MUC.UOM_CODE EVENT_ID, 'ITEM-INTRA' CONVERSION_TYPE, NULL CONVERSION_ID, NULL CONV_AUDIT_ID, NULL LOT_NUMBER, MSI.ORGANIZATION_ID ORGANIZATION_ID, MUC.INVENTORY_ITEM_ID INVENTORY_ITEM_ID, MSI.CONCATENATED_SEGMENTS ITEM_NUMBER, MSI.DESCRIPTION ITEM_DESCRIPTION, NULL ORGANIZATION_CODE, NULL ORGANIZATION_NAME, MUOM.UNIT_OF_MEASURE FROM_UNIT_OF_MEASURE, MUOM.UOM_CODE FROM_UOM_CODE, MUOM.UOM_CLASS FROM_UOM_CLASS, MUC.UNIT_OF_MEASURE TO_UNIT_OF_MEASURE, MUC.UOM_CODE TO_UOM_CODE, MUC.UOM_CLASS TO_UOM_CLASS, MUC.CONVERSION_RATE CONVERSION_RATE, MUC.DISABLE_DATE DISABLE_DATE, NULL UPDATE_TYPE, MSI.PRIMARY_UOM_CODE PRIMARY_UOM_CODE, MSI.SECONDARY_UOM_CODE SECONDARY_UOM_CODE, NULL BATCH_ID, NULL BATCH_NO, NULL REASON_NAME, MUC.CREATED_BY CREATED_BY, MUC.CREATION_DATE CREATION_DATE, MUC.LAST_UPDATED_BY LAST_UPDATED_BY, MUC.LAST_UPDATE_DATE LAST_UPDATE_DATE FROM MTL_UOM_CONVERSIONS MUC, MTL_UNITS_OF_MEASURE MUOM, MTL_SYSTEM_ITEMS_KFV MSI, MTL_PARAMETERS MP WHERE MUC.UOM_CLASS = MUOM.UOM_CLASS AND MUOM.BASE_UOM_FLAG = 'Y' AND MUC.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID AND MSI.organization_id = MP.organization_id AND MP.organization_id = MP.master_organization_id UNION SELECT TO_CHAR(MP.MASTER_ORGANIZATION_ID)||'-'||TO_CHAR(MUCC.INVENTORY_ITEM_ID)||'-'||MUCC.FROM_UOM_CODE||'-'||MUCC.TO_UOM_CODE EVENT_ID, 'ITEM-INTER' CONVERSION_TYPE, NULL CONVERSION_ID, NULL CONV_AUDIT_ID, NULL LOT_NUMBER, MSI.ORGANIZATION_ID ORGANIZATION_ID, MUCC.INVENTORY_ITEM_ID INVENTORY_ITEM_ID, MSI.CONCATENATED_SEGMENTS ITEM_NUMBER, MSI.DESCRIPTION ITEM_DESCRIPTION, NULL ORGANIZATION_CODE, NULL ORGANIZATION_NAME, MUCC.FROM_UNIT_OF_MEASURE FROM_UNIT_OF_MEASURE, MUCC.FROM_UOM_CODE FROM_UOM_CODE, MUCC.FROM_UOM_CLASS FROM_UOM_CLASS, MUCC.TO_UNIT_OF_MEASURE TO_UNIT_OF_MEASURE, MUCC.TO_UOM_CODE TO_UOM_CODE, MUCC.TO_UOM_CLASS TO_UOM_CLASS, MUCC.CONVERSION_RATE CONVERSION_RATE, MUCC.DISABLE_DATE DISABLE_DATE, NULL UPDATE_TYPE, MSI.PRIMARY_UOM_CODE PRIMARY_UOM_CODE, MSI.SECONDARY_UOM_CODE SECONDARY_UOM_CODE, NULL BATCH_ID, NULL BATCH_NO, NULL REASON_NAME, MUCC.CREATED_BY CREATED_BY, MUCC.CREATION_DATE CREATION_DATE, MUCC.LAST_UPDATED_BY LAST_UPDATED_BY, MUCC.LAST_UPDATE_DATE LAST_UPDATE_DATE FROM MTL_UOM_CLASS_CONVERSIONS MUCC , MTL_SYSTEM_ITEMS_KFV MSI, MTL_PARAMETERS MP WHERE MUCC.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID AND MSI.organization_id = MP.organization_id AND MP.organization_id = MP.master_organization_id

View Text - HTML Formatted

SELECT TO_CHAR(LUC.CONVERSION_ID)||'-'||TO_CHAR(MLA.CONV_AUDIT_ID) EVENT_ID
, 'LOT' CONVERSION_TYPE
, LUC.CONVERSION_ID CONVERSION_ID
, MLA.CONV_AUDIT_ID CONV_AUDIT_ID
, LUC.LOT_NUMBER LOT_NUMBER
, LUC.ORGANIZATION_ID ORGANIZATION_ID
, LUC.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, MSI.CONCATENATED_SEGMENTS ITEM_NUMBER
, MSI.DESCRIPTION ITEM_DESCRIPTION
, MP.ORGANIZATION_CODE ORGANIZATION_CODE
, H.NAME ORGANIZATION_NAME
, LUC.FROM_UNIT_OF_MEASURE FROM_UNIT_OF_MEASURE
, LUC.FROM_UOM_CODE FROM_UOM_CODE
, LUC.FROM_UOM_CLASS FROM_UOM_CLASS
, LUC.TO_UNIT_OF_MEASURE TO_UNIT_OF_MEASURE
, LUC.TO_UOM_CODE TO_UOM_CODE
, LUC.TO_UOM_CLASS TO_UOM_CLASS
, LUC.CONVERSION_RATE CONVERSION_RATE
, LUC.DISABLE_DATE DISABLE_DATE
, MLK.MEANING UPDATE_TYPE
, MSI.PRIMARY_UOM_CODE PRIMARY_UOM_CODE
, MSI.SECONDARY_UOM_CODE SECONDARY_UOM_CODE
, MLA.BATCH_ID BATCH_ID
, GBH.BATCH_NO BATCH_NO
, MR.REASON_NAME REASON_NAME
, LUC.CREATED_BY CREATED_BY
, LUC.CREATION_DATE CREATION_DATE
, LUC.LAST_UPDATED_BY LAST_UPDATED_BY
, LUC.LAST_UPDATE_DATE LAST_UPDATE_DATE
FROM MTL_LOT_UOM_CLASS_CONVERSIONS LUC
, HR_ALL_ORGANIZATION_UNITS H
, MTL_PARAMETERS MP
, MTL_SYSTEM_ITEMS_KFV MSI
, MTL_LOT_CONV_AUDIT MLA
, MFG_LOOKUPS MLK
, MTL_TRANSACTION_REASONS MR
, GME_BATCH_HEADER GBH
WHERE LUC.ORGANIZATION_ID = H.ORGANIZATION_ID
AND H.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND LUC.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND LUC.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND LUC.CONVERSION_ID = MLA.CONVERSION_ID
AND MLA.UPDATE_TYPE_INDICATOR = MLK.LOOKUP_CODE
AND MLK.LOOKUP_TYPE = 'INV_UPDATE_TYPE'
AND MLA.REASON_ID = MR.REASON_ID(+)
AND MLA.BATCH_ID = GBH.BATCH_ID(+) UNION SELECT TO_CHAR(MP.MASTER_ORGANIZATION_ID)||'-'||TO_CHAR(MUC.INVENTORY_ITEM_ID)||'-'||MUOM.UOM_CODE||'-'||MUC.UOM_CODE EVENT_ID
, 'ITEM-INTRA' CONVERSION_TYPE
, NULL CONVERSION_ID
, NULL CONV_AUDIT_ID
, NULL LOT_NUMBER
, MSI.ORGANIZATION_ID ORGANIZATION_ID
, MUC.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, MSI.CONCATENATED_SEGMENTS ITEM_NUMBER
, MSI.DESCRIPTION ITEM_DESCRIPTION
, NULL ORGANIZATION_CODE
, NULL ORGANIZATION_NAME
, MUOM.UNIT_OF_MEASURE FROM_UNIT_OF_MEASURE
, MUOM.UOM_CODE FROM_UOM_CODE
, MUOM.UOM_CLASS FROM_UOM_CLASS
, MUC.UNIT_OF_MEASURE TO_UNIT_OF_MEASURE
, MUC.UOM_CODE TO_UOM_CODE
, MUC.UOM_CLASS TO_UOM_CLASS
, MUC.CONVERSION_RATE CONVERSION_RATE
, MUC.DISABLE_DATE DISABLE_DATE
, NULL UPDATE_TYPE
, MSI.PRIMARY_UOM_CODE PRIMARY_UOM_CODE
, MSI.SECONDARY_UOM_CODE SECONDARY_UOM_CODE
, NULL BATCH_ID
, NULL BATCH_NO
, NULL REASON_NAME
, MUC.CREATED_BY CREATED_BY
, MUC.CREATION_DATE CREATION_DATE
, MUC.LAST_UPDATED_BY LAST_UPDATED_BY
, MUC.LAST_UPDATE_DATE LAST_UPDATE_DATE
FROM MTL_UOM_CONVERSIONS MUC
, MTL_UNITS_OF_MEASURE MUOM
, MTL_SYSTEM_ITEMS_KFV MSI
, MTL_PARAMETERS MP
WHERE MUC.UOM_CLASS = MUOM.UOM_CLASS
AND MUOM.BASE_UOM_FLAG = 'Y'
AND MUC.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MP.ORGANIZATION_ID = MP.MASTER_ORGANIZATION_ID UNION SELECT TO_CHAR(MP.MASTER_ORGANIZATION_ID)||'-'||TO_CHAR(MUCC.INVENTORY_ITEM_ID)||'-'||MUCC.FROM_UOM_CODE||'-'||MUCC.TO_UOM_CODE EVENT_ID
, 'ITEM-INTER' CONVERSION_TYPE
, NULL CONVERSION_ID
, NULL CONV_AUDIT_ID
, NULL LOT_NUMBER
, MSI.ORGANIZATION_ID ORGANIZATION_ID
, MUCC.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, MSI.CONCATENATED_SEGMENTS ITEM_NUMBER
, MSI.DESCRIPTION ITEM_DESCRIPTION
, NULL ORGANIZATION_CODE
, NULL ORGANIZATION_NAME
, MUCC.FROM_UNIT_OF_MEASURE FROM_UNIT_OF_MEASURE
, MUCC.FROM_UOM_CODE FROM_UOM_CODE
, MUCC.FROM_UOM_CLASS FROM_UOM_CLASS
, MUCC.TO_UNIT_OF_MEASURE TO_UNIT_OF_MEASURE
, MUCC.TO_UOM_CODE TO_UOM_CODE
, MUCC.TO_UOM_CLASS TO_UOM_CLASS
, MUCC.CONVERSION_RATE CONVERSION_RATE
, MUCC.DISABLE_DATE DISABLE_DATE
, NULL UPDATE_TYPE
, MSI.PRIMARY_UOM_CODE PRIMARY_UOM_CODE
, MSI.SECONDARY_UOM_CODE SECONDARY_UOM_CODE
, NULL BATCH_ID
, NULL BATCH_NO
, NULL REASON_NAME
, MUCC.CREATED_BY CREATED_BY
, MUCC.CREATION_DATE CREATION_DATE
, MUCC.LAST_UPDATED_BY LAST_UPDATED_BY
, MUCC.LAST_UPDATE_DATE LAST_UPDATE_DATE
FROM MTL_UOM_CLASS_CONVERSIONS MUCC
, MTL_SYSTEM_ITEMS_KFV MSI
, MTL_PARAMETERS MP
WHERE MUCC.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MP.ORGANIZATION_ID = MP.MASTER_ORGANIZATION_ID