VIEW: APPS.EDW_OPI_LOTD_LOT_LCV

Source

View Text - Preformatted

SELECT LT.LOT_ID||'-'||LT.ITEM_ID||'-'||'OPM'||'-'||INST.INSTANCE_CODE EDW_LOT_PK, 'LOTD' EDW_LOT_DP, to_number(NULL) ORGANIZATION_ID, to_number(NULL) INVENTORY_ITEM_ID, LOT_NO||DECODE(SUBLOT_NO,NULL,NULL,'-'||SUBLOT_NO) ||'('|| IIM.ITEM_NO ||')' NAME, decode(LT.LOT_ID,0,LOT_NO,LOT_DESC) LOT_NAME, 0 ITEM_REVISION, NULL NETTABLE_FLAG, LT.EXPIRE_DATE EXPIRATION_DATE, LOT_DESC DESCRIPTION, TO_CHAR(NULL) USER_ATTRIBUTE1, TO_CHAR(NULL) USER_ATTRIBUTE2, TO_CHAR(NULL) USER_ATTRIBUTE3, TO_CHAR(NULL) USER_ATTRIBUTE4, TO_CHAR(NULL) USER_ATTRIBUTE5, 'ALL', LT.LAST_UPDATE_DATE, LT.CREATION_DATE, LT.SUBLOT_NO LOT, LT.LOT_NO PARENT_LOT FROM IC_LOTS_MST LT, IC_ITEM_MST IIM, EDW_LOCAL_INSTANCE INST WHERE LT.ITEM_ID = IIM.ITEM_ID UNION ALL select Inventory_Item_ID || '-' || Organization_ID || '-' || lot_number || '-' || inst.instance_code, 'LOTD', Organization_ID, Inventory_Item_ID, lot_number||'('||Inventory_Item_ID || '-' || Organization_ID || ')', lot_number, to_number(NULL), NULL, expiration_date, lot_number, TO_CHAR(NULL) USER_ATTRIBUTE1, TO_CHAR(NULL) USER_ATTRIBUTE2, TO_CHAR(NULL) USER_ATTRIBUTE3, TO_CHAR(NULL) USER_ATTRIBUTE4, TO_CHAR(NULL) USER_ATTRIBUTE5, 'ALL', lt.LAST_UPDATE_DATE, lt.CREATION_DATE, NULL, NULL from mtl_lot_numbers lt, edw_local_instance inst

View Text - HTML Formatted

SELECT LT.LOT_ID||'-'||LT.ITEM_ID||'-'||'OPM'||'-'||INST.INSTANCE_CODE EDW_LOT_PK
, 'LOTD' EDW_LOT_DP
, TO_NUMBER(NULL) ORGANIZATION_ID
, TO_NUMBER(NULL) INVENTORY_ITEM_ID
, LOT_NO||DECODE(SUBLOT_NO
, NULL
, NULL
, '-'||SUBLOT_NO) ||'('|| IIM.ITEM_NO ||')' NAME
, DECODE(LT.LOT_ID
, 0
, LOT_NO
, LOT_DESC) LOT_NAME
, 0 ITEM_REVISION
, NULL NETTABLE_FLAG
, LT.EXPIRE_DATE EXPIRATION_DATE
, LOT_DESC DESCRIPTION
, TO_CHAR(NULL) USER_ATTRIBUTE1
, TO_CHAR(NULL) USER_ATTRIBUTE2
, TO_CHAR(NULL) USER_ATTRIBUTE3
, TO_CHAR(NULL) USER_ATTRIBUTE4
, TO_CHAR(NULL) USER_ATTRIBUTE5
, 'ALL'
, LT.LAST_UPDATE_DATE
, LT.CREATION_DATE
, LT.SUBLOT_NO LOT
, LT.LOT_NO PARENT_LOT
FROM IC_LOTS_MST LT
, IC_ITEM_MST IIM
, EDW_LOCAL_INSTANCE INST
WHERE LT.ITEM_ID = IIM.ITEM_ID UNION ALL SELECT INVENTORY_ITEM_ID || '-' || ORGANIZATION_ID || '-' || LOT_NUMBER || '-' || INST.INSTANCE_CODE
, 'LOTD'
, ORGANIZATION_ID
, INVENTORY_ITEM_ID
, LOT_NUMBER||'('||INVENTORY_ITEM_ID || '-' || ORGANIZATION_ID || ')'
, LOT_NUMBER
, TO_NUMBER(NULL)
, NULL
, EXPIRATION_DATE
, LOT_NUMBER
, TO_CHAR(NULL) USER_ATTRIBUTE1
, TO_CHAR(NULL) USER_ATTRIBUTE2
, TO_CHAR(NULL) USER_ATTRIBUTE3
, TO_CHAR(NULL) USER_ATTRIBUTE4
, TO_CHAR(NULL) USER_ATTRIBUTE5
, 'ALL'
, LT.LAST_UPDATE_DATE
, LT.CREATION_DATE
, NULL
, NULL
FROM MTL_LOT_NUMBERS LT
, EDW_LOCAL_INSTANCE INST