View: EDW_OPI_LOTD_LOT_LCV

Product:	OPI - Operations Intelligence
Description:	This is source view to populate Lot Level data in Lot Dimension
Implementation/DBA Data:	APPS.EDW_OPI_LOTD_LOT_LCV

View Text

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

Columns