View: PO_SGD_MOD_LINES_UDA_V

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

View Text

SELECT POEXT.PO_HEADER_ID PK1_VALUE
, POEXT.DRAFT_ID PK2_VALUE
, POEXT.PO_LINE_ID PK3_VALUE
, NULL PK4_VALUE
, NULL PK5_VALUE
, EGA.COL_NAME
, POEXT.COL_VALUE
, DECODE(EGA.ATTR_NAME
, 'UOM'
, (SELECT UNIT_OF_MEASURE
FROM MTL_UNITS_OF_MEASURE_VL
WHERE UOM_CODE = POEXT.COL_VALUE)
, 'MDAPS_MAIS'
, (SELECT DISPLAYED_FIELD
FROM PO_LOOKUP_CODES
WHERE LOOKUP_TYPE = 'PO_CLM_MDAPS/MAIS'
AND LOOKUP_CODE = POEXT.COL_VALUE)
, 'PRGM_CODE'
, (SELECT DISPLAYED_FIELD
FROM PO_LOOKUP_CODES
WHERE LOOKUP_TYPE = 'PO_CLM_PROGRAM_CODES'
AND LOOKUP_CODE = POEXT.COL_VALUE)
, 'FSC_PSC'
, (SELECT MEANING
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE IN ('FV_FSC_TYPE'
, 'FV_PSC_TYPE')
AND LOOKUP_CODE = POEXT.COL_VALUE
AND LANGUAGE = USERENV('LANG'))
, 'NAICS'
, (SELECT MEANING
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'FV_NAICS_TYPE'
AND LOOKUP_CODE = POEXT.COL_VALUE
AND LANGUAGE = USERENV('LANG'))
, 'CUST_PRJ_CODE'
, (SELECT DISPLAYED_FIELD
FROM PO_LOOKUP_CODES
WHERE LOOKUP_TYPE = 'PO_CLM_CUSTOMER_PROJECT_CODE'
AND LOOKUP_CODE = POEXT.COL_VALUE)
, 'INSP_RESP'
, (SELECT DISPLAYED_FIELD
FROM PO_LOOKUP_CODES
WHERE LOOKUP_TYPE = 'PO_CLM_INSPECTION_RESP'
AND LOOKUP_CODE = POEXT.COL_VALUE)
, 'INSP_LOC'
, (SELECT DISPLAYED_FIELD
FROM PO_LOOKUP_CODES
WHERE LOOKUP_TYPE = 'PO_CLM_INSPECTION_LOC'
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_LINE_EXT_ATTRS'
AND FCU.DISPLAY_FLAG <> 'H' ) EGA
, ( WITH PO_LINE_EXT_UNPIVOT_DATA AS ( SELECT PL.PO_HEADER_ID
, EXT.PO_LINE_ID
, EXT.DRAFT_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_ATTR10
, C_EXT_ATTR12
, C_EXT_ATTR13
, TO_CHAR(N_EXT_ATTR1) AS N_EXT_ATTR1
, TO_CHAR(N_EXT_ATTR2) AS N_EXT_ATTR2
, TO_CHAR(N_EXT_ATTR3) AS N_EXT_ATTR3
, TO_CHAR(N_EXT_ATTR4) AS N_EXT_ATTR4
, TO_CHAR(N_EXT_ATTR5) AS N_EXT_ATTR5
, TO_CHAR(N_EXT_ATTR6) AS N_EXT_ATTR6
, TO_CHAR(N_EXT_ATTR7) AS N_EXT_ATTR7
, TO_CHAR(N_EXT_ATTR8) AS N_EXT_ATTR8
, TO_CHAR(N_EXT_ATTR9) AS N_EXT_ATTR9
, TO_CHAR(N_EXT_ATTR10) AS N_EXT_ATTR10
, TO_CHAR(N_EXT_ATTR11) AS N_EXT_ATTR11
, TO_CHAR(N_EXT_ATTR20) AS N_EXT_ATTR20
FROM PO_LINES_ALL_EXT_B EXT
, EGO_FND_DSC_FLX_CTX_EXT EGG
, PO_LINES_DRAFT_ALL PL
WHERE EXT.PK1_VALUE IS NULL
AND EXT.PO_LINE_ID = PL.PO_LINE_ID
AND EXT.DRAFT_ID = PL.DRAFT_ID
AND PL.PO_HEADER_ID = PO_GEN_DIFF_PKG.GETMODPK1
AND PL.DRAFT_ID = PO_GEN_DIFF_PKG.GETMODPK2
AND PL.PO_LINE_ID = NVL(PO_GEN_DIFF_PKG.GETMODPK3
, PL.PO_LINE_ID)
AND EXT.ATTR_GROUP_ID = EGG.ATTR_GROUP_ID
AND EGG.DESCRIPTIVE_FLEXFIELD_NAME = 'PO_LINE_EXT_ATTRS'
AND EGG.APPLICATION_ID = 201
AND EGG.DESCRIPTIVE_FLEX_CONTEXT_CODE IN ('ADD_ITEM_INFO'
, 'FED_CUST_DESG'
, 'INSPECT_INFO'
, 'AMT_CPFF_IN'
, 'AMT_AF_IN'
, 'AMT_FF_IN'
, 'AMT_CS_IN'
, 'AMT_FAF_IN'
, 'AMT_FPR_IN'
, 'AMT_TMA_IN'
, 'AMT_CPIF_IN'
, 'AMT_FLE_IN'
, 'AMT_CPAF_IN'
, 'AMT_FIST_IN'
, 'AMT_LH_IN'
, 'AMT_FRR_IN'
, 'AMT_CC_IN'
, 'AMT_FIFT_IN'
, 'AMT_TM_IN'
, 'AMT_ODC_IN'
, 'AMT_FEPA_IN'
, 'AMT_CPFF_IR'
, 'AMT_AF_IR'
, 'AMT_FF_IR'
, 'AMT_CS_IR'
, 'AMT_FAF_IR'
, 'AMT_FPR_IR'
, 'AMT_TMA_IR'
, 'AMT_CPIF_IR'
, 'AMT_FLE_IR'
, 'AMT_CPAF_IR'
, 'AMT_FIST_IR'
, 'AMT_LH_IR'
, 'AMT_FRR_IR'
, 'AMT_CC_IR'
, 'AMT_FIFT_IR'
, 'AMT_TM_IR'
, 'AMT_ODC_IR'
, 'AMT_FEPA_IR'
, 'AMT_CPFF_IDQ'
, 'AMT_AF_IDQ'
, 'AMT_FF_IDQ'
, 'AMT_CS_IDQ'
, 'AMT_FAF_IDQ'
, 'AMT_FPR_IDQ'
, 'AMT_CPIF_IDQ'
, 'AMT_FLE_IDQ'
, 'AMT_CPAF_IDQ'
, 'AMT_FIST_IDQ'
, 'AMT_LH_IDQ'
, 'AMT_FRR_IDQ'
, 'AMT_CC_IDQ'
, 'AMT_FIFT_IDQ'
, 'AMT_TM_IDQ'
, 'AMT_ODC_IDQ'
, 'AMT_FEPA_IDQ'
, 'AMT_CPFF_IIQ'
, 'AMT_AF_IIQ'
, 'AMT_FF_IIQ'
, 'AMT_CS_IIQ'
, 'AMT_FAF_IIQ'
, 'AMT_FPR_IIQ'
, 'AMT_TMA_IIQ'
, 'AMT_CPIF_IIQ'
, 'AMT_FLE_IIQ'
, 'AMT_CPAF_IIQ'
, 'AMT_FIST_IIQ'
, 'AMT_LH_IIQ'
, 'AMT_FRR_IIQ'
, 'AMT_CC_IIQ'
, 'AMT_FIFT_IIQ'
, 'AMT_TM_IIQ'
, 'AMT_ODC_IIQ'
, 'AMT_FEPA_IIQ')
AND PL.CHANGE_STATUS IN ('UPDATE') ) SELECT PO_HEADER_ID
, PO_LINE_ID
, DRAFT_ID
, ATTR_GROUP_NAME
, COL_NAME
, COL_VALUE
FROM PO_LINE_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_ATTR10
, C_EXT_ATTR12
, C_EXT_ATTR13
, N_EXT_ATTR1
, N_EXT_ATTR2
, N_EXT_ATTR3
, N_EXT_ATTR4
, N_EXT_ATTR5
, N_EXT_ATTR6
, N_EXT_ATTR7
, N_EXT_ATTR8
, N_EXT_ATTR9
, N_EXT_ATTR10
, N_EXT_ATTR11
, N_EXT_ATTR20) ) ) POEXT
WHERE EGA.ATTR_GROUP_NAME = POEXT.ATTR_GROUP_NAME
AND EGA.APPLICATION_COLUMN_NAME = POEXT.COL_NAME

Columns