View: ITG_LPI_LINES_V

Product:	ITG - Internet Procurement Enterprise Connector
Description:
Implementation/DBA Data:	APPS.ITG_LPI_LINES_V

View Text

SELECT ID.INVOICE_ID
, ID.DISTRIBUTION_LINE_NUMBER
, ID.AMOUNT AMOUNT_EXTENDED_T_AMOUNT
, IA.INVOICE_CURRENCY_CODE AMOUNT_EXTENDED_T_CURR_CODE
, DECODE(UPPER(IA.INVOICE_TYPE_LOOKUP_CODE)
, 'CREDIT'
, 'CR'
, 'DR') AMOUNT_EXTENDED_T_LOOKUP_CODE
, ID.AMOUNT * DECODE(NVL(IA.EXCHANGE_RATE
, 0)
, 0
, 1
, IA.EXCHANGE_RATE) AMOUNT_EXTENDED_F_AMOUNT
, SOB.CURRENCY_CODE AMOUNT_EXTENDED_F_CURR_CODE
, DECODE(UPPER(IA.INVOICE_TYPE_LOOKUP_CODE)
, 'CREDIT'
, 'CR'
, 'DR') AMOUNT_EXTENDED_F_LOOKUP_CODE
, ID.UNIT_PRICE OPERAMT_F_UNITPRICE
, IA.INVOICE_CURRENCY_CODE OPERAMT_UNIT_F_CURRENCY_CODE
, ID.QUANTITY_INVOICED OPERAMT_UNIT_F_QUANTITY
, UOM.UOM_CODE OPERAMT_UNIT_F_UOMCODE
, ID.UNIT_PRICE * DECODE(NVL(IA.EXCHANGE_RATE
, 0)
, 0
, 1
, IA.EXCHANGE_RATE) OPERAMT_T_UNITPRICE
, SOB.CURRENCY_CODE OPERAMT_UNIT_T_CURRENCY_CODE
, ID.QUANTITY_INVOICED OPERAMT_UNIT_T_QUANTITY
, UOM.UOM_CODE OPERAMT_UNIT_T_UOMCODE
, ID.QUANTITY_INVOICED QUANTITY_ITEM_QUANTITY
, UOM.UOM_CODE QUANTITY_ITEM_UOMCODE
, DECODE(ITG_X_UTILS.GETFLEXQUALIFIERSEGMENT( CC.CHART_OF_ACCOUNTS_ID
, 'GL_ACCOUNT' )
, 'SEGMENT1'
, CC.SEGMENT1
, 'SEGMENT2'
, CC.SEGMENT2
, 'SEGMENT3'
, CC.SEGMENT3
, 'SEGMENT4'
, CC.SEGMENT4
, 'SEGMENT5'
, CC.SEGMENT5
, 'SEGMENT6'
, CC.SEGMENT6
, 'SEGMENT7'
, CC.SEGMENT7
, 'SEGMENT8'
, CC.SEGMENT8
, 'SEGMENT9'
, CC.SEGMENT9
, 'SEGMENT10'
, CC.SEGMENT10
, 'SEGMENT11'
, CC.SEGMENT11
, 'SEGMENT12'
, CC.SEGMENT12
, 'SEGMENT13'
, CC.SEGMENT13
, 'SEGMENT14'
, CC.SEGMENT14
, 'SEGMENT15'
, CC.SEGMENT15
, 'SEGMENT16'
, CC.SEGMENT16
, 'SEGMENT17'
, CC.SEGMENT17
, 'SEGMENT18'
, CC.SEGMENT18
, 'SEGMENT19'
, CC.SEGMENT19
, 'SEGMENT20'
, CC.SEGMENT20
, 'SEGMENT21'
, CC.SEGMENT21
, 'SEGMENT22'
, CC.SEGMENT22
, 'SEGMENT23'
, CC.SEGMENT23
, 'SEGMENT24'
, CC.SEGMENT24
, 'SEGMENT25'
, CC.SEGMENT25
, 'SEGMENT26'
, CC.SEGMENT26
, 'SEGMENT27'
, CC.SEGMENT27
, 'SEGMENT28'
, CC.SEGMENT28
, 'SEGMENT29'
, CC.SEGMENT29
, 'SEGMENT30'
, CC.SEGMENT30) GLNOMACCT
, TO_CHAR(ID.DISTRIBUTION_LINE_NUMBER) INVLINENUM
, DECODE(ITG_X_UTILS.GETFLEXQUALIFIERSEGMENT( CC.CHART_OF_ACCOUNTS_ID
, 'FA_COST_CTR' )
, 'SEGMENT1'
, CC.SEGMENT1
, 'SEGMENT2'
, CC.SEGMENT2
, 'SEGMENT3'
, CC.SEGMENT3
, 'SEGMENT4'
, CC.SEGMENT4
, 'SEGMENT7'
, CC.SEGMENT7
, 'SEGMENT8'
, CC.SEGMENT8
, 'SEGMENT9'
, CC.SEGMENT9
, 'SEGMENT10'
, CC.SEGMENT10
, 'SEGMENT11'
, CC.SEGMENT11
, 'SEGMENT12'
, CC.SEGMENT12
, 'SEGMENT13'
, CC.SEGMENT13
, 'SEGMENT14'
, CC.SEGMENT14
, 'SEGMENT15'
, CC.SEGMENT15
, 'SEGMENT16'
, CC.SEGMENT16
, 'SEGMENT17'
, CC.SEGMENT17
, 'SEGMENT18'
, CC.SEGMENT18
, 'SEGMENT19'
, CC.SEGMENT19
, 'SEGMENT20'
, CC.SEGMENT20
, 'SEGMENT21'
, CC.SEGMENT21
, 'SEGMENT22'
, CC.SEGMENT22
, 'SEGMENT23'
, CC.SEGMENT23
, 'SEGMENT24'
, CC.SEGMENT24
, 'SEGMENT25'
, CC.SEGMENT25
, 'SEGMENT26'
, CC.SEGMENT26
, 'SEGMENT27'
, CC.SEGMENT27
, 'SEGMENT28'
, CC.SEGMENT28
, 'SEGMENT29'
, CC.SEGMENT29
, 'SEGMENT30'
, CC.SEGMENT30) COSTCENTER
, ID.DESCRIPTION DESCRIPTN
, CC.SEGMENT1 ELEMENT1
, CC.SEGMENT2 ELEMENT2
, CC.SEGMENT3 ELEMENT3
, CC.SEGMENT4 ELEMENT4
, CC.SEGMENT5 ELEMENT5
, CC.SEGMENT6 ELEMENT6
, CC.SEGMENT7 ELEMENT7
, CC.SEGMENT8 ELEMENT8
, CC.SEGMENT9 ELEMENT9
, CC.SEGMENT10 ELEMENT10
, CC.SEGMENT11 ELEMENT11
, CC.SEGMENT12 ELEMENT12
, CC.SEGMENT13 ELEMENT13
, CC.SEGMENT14 ELEMENT14
, CC.SEGMENT15 ELEMENT15
, CC.SEGMENT16 ELEMENT16
, CC.SEGMENT17 ELEMENT17
, CC.SEGMENT18 ELEMENT18
, CC.SEGMENT19 ELEMENT19
, CC.SEGMENT20 ELEMENT20
, CC.SEGMENT21 ELEMENT21
, CC.SEGMENT22 ELEMENT22
, CC.SEGMENT23 ELEMENT23
, CC.SEGMENT24 ELEMENT24
, CC.SEGMENT25 ELEMENT25
, CC.SEGMENT26 ELEMENT26
, CC.SEGMENT27 ELEMENT27
, CC.SEGMENT28 ELEMENT28
, CC.SEGMENT29 ELEMENT29
, CC.SEGMENT30 ELEMENT30
, DECODE(ITG_X_UTILS.GETFLEXQUALIFIERSEGMENT( CC.CHART_OF_ACCOUNTS_ID
, 'GL_BALANCING' )
, 'SEGMENT1'
, CC.SEGMENT1
, 'SEGMENT2'
, CC.SEGMENT2
, 'SEGMENT3'
, CC.SEGMENT3
, 'SEGMENT4'
, CC.SEGMENT4
, 'SEGMENT5'
, CC.SEGMENT5
, 'SEGMENT6'
, CC.SEGMENT6
, 'SEGMENT7'
, CC.SEGMENT7
, 'SEGMENT8'
, CC.SEGMENT8
, 'SEGMENT9'
, CC.SEGMENT9
, 'SEGMENT10'
, CC.SEGMENT10
, 'SEGMENT11'
, CC.SEGMENT11
, 'SEGMENT12'
, CC.SEGMENT12
, 'SEGMENT13'
, CC.SEGMENT13
, 'SEGMENT14'
, CC.SEGMENT14
, 'SEGMENT15'
, CC.SEGMENT15
, 'SEGMENT16'
, CC.SEGMENT16
, 'SEGMENT17'
, CC.SEGMENT17
, 'SEGMENT18'
, CC.SEGMENT18
, 'SEGMENT19'
, CC.SEGMENT19
, 'SEGMENT20'
, CC.SEGMENT20
, 'SEGMENT21'
, CC.SEGMENT21
, 'SEGMENT22'
, CC.SEGMENT22
, 'SEGMENT23'
, CC.SEGMENT23
, 'SEGMENT24'
, CC.SEGMENT24
, 'SEGMENT25'
, CC.SEGMENT25
, 'SEGMENT26'
, CC.SEGMENT26
, 'SEGMENT27'
, CC.SEGMENT27
, 'SEGMENT28'
, CC.SEGMENT28
, 'SEGMENT29'
, CC.SEGMENT29
, 'SEGMENT30'
, CC.SEGMENT30) GLENTITYD
, DECODE(PL.ITEM_ID
, NULL
, NULL
, ITG_X_UTILS.SEGSTRING( 401
, 'MSTK'
, NULL
, MI.SEGMENT1
, MI.SEGMENT2
, MI.SEGMENT3
, MI.SEGMENT4
, MI.SEGMENT5
, MI.SEGMENT6
, MI.SEGMENT7
, MI.SEGMENT8
, MI.SEGMENT9
, MI.SEGMENT10
, MI.SEGMENT11
, MI.SEGMENT12
, MI.SEGMENT13
, MI.SEGMENT14
, MI.SEGMENT15
, MI.SEGMENT16
, MI.SEGMENT17
, MI.SEGMENT18
, MI.SEGMENT19
, MI.SEGMENT20)) ITEM
, PL.VENDOR_PRODUCT_NUM ITEMTYPE
, DECODE(PH.SEGMENT1
, NULL
, NULL
, PH.SEGMENT1||'-'||RTRIM(LTRIM(TO_CHAR(PH.REVISION_NUM)))) POID
, ID.VAT_CODE TAXCODE
, ID.EXCHANGE_RATE QUANTITY_UA_RATE_RATE
, ID.EXCHANGE_DATE DATETIME_UA_EFFECTIVE
, ID.EXCHANGE_RATE_TYPE UA_EXCHTYPE
, TO_CHAR(ID.ORG_ID) UA_ORG
FROM MTL_UNITS_OF_MEASURE UOM
, MTL_SYSTEM_ITEMS MI
, GL_SETS_OF_BOOKS SOB
, GL_CODE_COMBINATIONS CC
, PO_LINES_ALL PL
, PO_DISTRIBUTIONS_ALL PD
, PO_HEADERS_ALL PH
, AP_INVOICES_ALL IA
, AP_INVOICE_DISTRIBUTIONS_ALL ID
WHERE UOM.UNIT_OF_MEASURE = PL.UNIT_MEAS_LOOKUP_CODE
AND MI.INVENTORY_ITEM_ID (+) = PL.ITEM_ID
AND MI.ORGANIZATION_ID (+) = PL.ORG_ID
AND SOB.SET_OF_BOOKS_ID = IA.SET_OF_BOOKS_ID
AND CC.CODE_COMBINATION_ID (+) = IA.ACCTS_PAY_CODE_COMBINATION_ID
AND PL.PO_LINE_ID (+) = PD.PO_LINE_ID
AND PH.PO_HEADER_ID (+) = PD.PO_HEADER_ID
AND PD.PO_DISTRIBUTION_ID (+) = ID.PO_DISTRIBUTION_ID
AND IA.INVOICE_ID = ID.INVOICE_ID

Columns