eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.ITG_LPI_LINES_V

Source

View Text - Preformatted


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 and NVL(pd.distribution_type,'xx') <> 'PREPAYMENT'

View Text - HTML Formatted

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
AND NVL(PD.DISTRIBUTION_TYPE
, 'XX') <> 'PREPAYMENT'