View: PO_SGD_MOD_LINELOCS_UDA_V

Product:	PO - Purchasing
Description:
Implementation/DBA Data:	APPS.PO_SGD_MOD_LINELOCS_UDA_V

View Text

SELECT POEXT.PO_HEADER_ID PK1_VALUE
, POEXT.DRAFT_ID PK2_VALUE
, POEXT.PO_LINE_ID PK3_VALUE
, POEXT.LINE_LOCATION_ID PK4_VALUE
, NULL PK5_VALUE
, EGA.COL_NAME
, POEXT.COL_VALUE
, DECODE(EGA.ATTR_NAME
, 'HAZMAT'
, (SELECT DISPLAYED_FIELD
FROM PO_LOOKUP_CODES
WHERE LOOKUP_TYPE = 'PO_CLM_HAZMAT'
AND LOOKUP_CODE = POEXT.COL_VALUE)
, 'FOB'
, (SELECT DISPLAYED_FIELD
FROM PO_LOOKUP_CODES
WHERE LOOKUP_TYPE = 'PO_CLM_FOB_VALUES'
AND LOOKUP_CODE = POEXT.COL_VALUE)
, 'SHIP_MODE'
, (SELECT DISPLAYED_FIELD
FROM PO_LOOKUP_CODES
WHERE LOOKUP_TYPE = 'PO_CLM_SHIPMENT_MODES'
AND LOOKUP_CODE = POEXT.COL_VALUE)
, 'PREC_MTL_CODE'
, (SELECT DISPLAYED_FIELD
FROM PO_LOOKUP_CODES
WHERE LOOKUP_TYPE = 'PO_CLM_PRECIOUS_METAL_CODE'
AND LOOKUP_CODE = POEXT.COL_VALUE)
, NULL) COL_DESC
FROM ( SELECT FCU.DESCRIPTIVE_FLEX_CONTEXT_CODE ATTR_GROUP_NAME
, FCU.APPLICATION_COLUMN_NAME
, UPPER(FCU.DESCRIPTIVE_FLEX_CONTEXT_CODE || '_' || FCU.END_USER_COLUMN_NAME) COL_NAME
, UPPER(FCU.END_USER_COLUMN_NAME) ATTR_NAME
FROM FND_DESCR_FLEX_COLUMN_USAGES FCU
WHERE FCU.APPLICATION_ID = 201
AND FCU.DESCRIPTIVE_FLEXFIELD_NAME = 'PO_SHIPMENTS_EXT_ATTRS'
AND FCU.DISPLAY_FLAG <> 'H' ) EGA
, ( WITH PO_LINELOC_EXT_UNPIVOT_DATA AS ( SELECT LLOC.PO_HEADER_ID
, LLOC.DRAFT_ID
, LLOC.PO_LINE_ID
, LLOC.LINE_LOCATION_ID
, EGG.DESCRIPTIVE_FLEX_CONTEXT_CODE ATTR_GROUP_NAME
, C_EXT_ATTR1
, C_EXT_ATTR2
, C_EXT_ATTR3
, C_EXT_ATTR4
, C_EXT_ATTR5
, C_EXT_ATTR6
, C_EXT_ATTR7
, C_EXT_ATTR8
, C_EXT_ATTR9
, C_EXT_ATTR10
, C_EXT_ATTR11
, C_EXT_ATTR12
, C_EXT_ATTR13
FROM PO_LINE_LOCATIONS_ALL_EXT_B EXT
, PO_LINE_LOCATIONS_DRAFT_ALL LLOC
, EGO_FND_DSC_FLX_CTX_EXT EGG
WHERE EXT.LINE_LOCATION_ID = LLOC.LINE_LOCATION_ID
AND EXT.DRAFT_ID = LLOC.DRAFT_ID
AND LLOC.PO_HEADER_ID = PO_GEN_DIFF_PKG.GETMODPK1
AND LLOC.DRAFT_ID = PO_GEN_DIFF_PKG.GETMODPK2
AND LLOC.PO_LINE_ID = NVL(PO_GEN_DIFF_PKG.GETMODPK3
, LLOC.PO_LINE_ID)
AND LLOC.LINE_LOCATION_ID = NVL(PO_GEN_DIFF_PKG.GETMODPK4
, LLOC.LINE_LOCATION_ID)
AND LLOC.CHANGE_STATUS IN ('UPDATE')
AND EGG.DESCRIPTIVE_FLEXFIELD_NAME = 'PO_SHIPMENTS_EXT_ATTRS'
AND EGG.DESCRIPTIVE_FLEX_CONTEXT_CODE = 'SHIP_INFO'
AND EGG.APPLICATION_ID = 201
AND EXT.ATTR_GROUP_ID = EGG.ATTR_GROUP_ID ) SELECT PO_HEADER_ID
, DRAFT_ID
, PO_LINE_ID
, LINE_LOCATION_ID
, ATTR_GROUP_NAME
, COL_NAME
, COL_VALUE
FROM PO_LINELOC_EXT_UNPIVOT_DATA UNPIVOT INCLUDE NULLS (COL_VALUE FOR COL_NAME IN ( C_EXT_ATTR1
, C_EXT_ATTR2
, C_EXT_ATTR3
, C_EXT_ATTR4
, C_EXT_ATTR5
, C_EXT_ATTR6
, C_EXT_ATTR7
, C_EXT_ATTR8
, C_EXT_ATTR9
, C_EXT_ATTR10
, C_EXT_ATTR11
, C_EXT_ATTR12
, C_EXT_ATTR13 ) ) ) POEXT
WHERE EGA.ATTR_GROUP_NAME = POEXT.ATTR_GROUP_NAME
AND EGA.APPLICATION_COLUMN_NAME = POEXT.COL_NAME

Columns