VIEW: APPS.ITG_LPI_HEADERS_V

Source

View Text - Preformatted

SELECT invoice_id, nvl(inv.invoice_amount, 0) amount_doc_t_invamount, inv.invoice_currency_code amount_doc_t_currency_code, decode(upper(inv.invoice_type_lookup_code), 'CREDIT', 'CR', 'DR') amount_doc_t_lookup_type , inv.invoice_date document_datetime, nvl( inv.invoice_amount, 0) * decode(nvl(inv.exchange_rate, 0), 0, 1, inv.exchange_rate) amount_doc_f_amount, sob.currency_code amount_doc_f_currency_code, decode(upper(inv.invoice_type_lookup_code), 'CREDIT', 'CR', 'DR') amount_doc_f_lookup_type, 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) glentitys, inv.invoice_num origref, inv.source descriptn, inv.invoice_type_lookup_code doctype, sob.name ledger, inv.payment_method_lookup_code payment, u.user_name userid, inv.doc_sequence_value voucher, v.vendor_name supplier_name, decode(v.one_time_flag, 'Y', '1', '0') suppluer_onetime, v.segment1 supplier_partnrid, 'Supplier' supplier_partnrtype, inv.invoice_currency_code supplier_currency, v.vendor_name_alt supplier_descriptn, to_char(v.vendor_id) supplier_partnridx, decode(vs.vat_code, NULL, '1', '0') supplier_taxexmpt, nvl(v.vat_registration_num, v.num_1099) supplier_taxid, decode(vs.country, 'US', 'Y', 'N') supplier_ua_usflag, vs.address_line1 supplier_address_line1, vs.address_line2 supplier_address_line2, vs.address_line3 supplier_address_line3, vs.city supplier_address_city, vs.country supplier_address_country, vs.county supplier_address_county, vs.vendor_site_code supplier_address_descriptn, decode(vs.fax_area_code, NULL, NULL, fax_area_code||' ')||vs.fax supplier_address_fax, vs.zip supplier_address_postalcode, NVL(vs.state, vs.province) supplier_address_stateprovn, decode(vs.area_code, NULL, NULL, vs.area_code||' ')||vs.phone supplier_address_telephone, NULL supplier_address_ua_vatcode, vs.purchasing_site_flag supplier_address_ua_pursite, vs.pay_site_flag supplier_address_ua_paysite, vs.rfq_only_site_flag supplier_address_ua_rfqsite, vs.pcard_site_flag supplier_address_ua_pcsite, nvl(inv.discount_amount_taken, 0) amount_discnt_t_amount, inv.invoice_currency_code amount_discnt_t_currency_code, decode(upper(inv.invoice_type_lookup_code), 'CREDIT', 'CR', 'DR') amount_discnt_t_lookup_code, nvl( inv.discount_amount_taken, 0) * decode(nvl(inv.exchange_rate, 0), 0, 1, inv.exchange_rate) amount_discnt_f_amount, sob.currency_code amount_discnt_f_currency_code, decode(upper(inv.invoice_type_lookup_code), 'CREDIT', 'CR', 'DR') amount_discnt_f_lookup_code, t.description term_descriptn, t.name termid, nvl(inv.tax_amount, 0) amount_tax_t_amount, inv.invoice_currency_code amount_tax_t_currency_code, decode(upper(inv.invoice_type_lookup_code), 'CREDIT', 'CR', 'DR') amount_tax_t_lookup_code, nvl(inv.tax_amount, 0) * decode(nvl(inv.exchange_rate, 0), 0, 1, inv.exchange_rate) amount_tax_f_amount, sob.currency_code amount_tax_f_currency_code, decode(upper(inv.invoice_type_lookup_code), 'CREDIT', 'CR', 'DR') amount_tax_f_lookup_code, nvl(inv.invoice_amount, 0) * decode(nvl(inv.exchange_rate, 0), 0, 1, inv.exchange_rate) amount_taxbase_f_amount, sob.currency_code amount_taxbase_f_currency_code, decode(upper(inv.invoice_type_lookup_code), 'CREDIT', 'CR', 'DR') amount_taxbase_f_lookup_code, nvl(inv.invoice_amount, 0) amount_taxbase_t_amount, inv.invoice_currency_code amount_taxbase_t_currency_code, decode(upper(inv.invoice_type_lookup_code), 'CREDIT', 'CR', 'DR') amount_taxbase_t_lookup_code, inv.vat_code tax_taxcode FROM fnd_user u, ap_terms t, gl_sets_of_books sob, gl_code_combinations cc, po_vendor_sites_all vs, po_vendors v, ap_invoices_all inv WHERE u.user_id = inv.last_updated_by and t.term_id (+) = inv.terms_id and sob.set_of_books_id = inv.set_of_books_id and cc.code_combination_id (+) = inv.accts_pay_code_combination_id and vs.org_id (+) = inv.org_id and vs.vendor_site_id (+) = inv.vendor_site_id and v.vendor_id = inv.vendor_id

View Text - HTML Formatted

SELECT INVOICE_ID
, NVL(INV.INVOICE_AMOUNT
, 0) AMOUNT_DOC_T_INVAMOUNT
, INV.INVOICE_CURRENCY_CODE AMOUNT_DOC_T_CURRENCY_CODE
, DECODE(UPPER(INV.INVOICE_TYPE_LOOKUP_CODE)
, 'CREDIT'
, 'CR'
, 'DR') AMOUNT_DOC_T_LOOKUP_TYPE
, INV.INVOICE_DATE DOCUMENT_DATETIME
, NVL( INV.INVOICE_AMOUNT
, 0) * DECODE(NVL(INV.EXCHANGE_RATE
, 0)
, 0
, 1
, INV.EXCHANGE_RATE) AMOUNT_DOC_F_AMOUNT
, SOB.CURRENCY_CODE AMOUNT_DOC_F_CURRENCY_CODE
, DECODE(UPPER(INV.INVOICE_TYPE_LOOKUP_CODE)
, 'CREDIT'
, 'CR'
, 'DR') AMOUNT_DOC_F_LOOKUP_TYPE
, 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) GLENTITYS
, INV.INVOICE_NUM ORIGREF
, INV.SOURCE DESCRIPTN
, INV.INVOICE_TYPE_LOOKUP_CODE DOCTYPE
, SOB.NAME LEDGER
, INV.PAYMENT_METHOD_LOOKUP_CODE PAYMENT
, U.USER_NAME USERID
, INV.DOC_SEQUENCE_VALUE VOUCHER
, V.VENDOR_NAME SUPPLIER_NAME
, DECODE(V.ONE_TIME_FLAG
, 'Y'
, '1'
, '0') SUPPLUER_ONETIME
, V.SEGMENT1 SUPPLIER_PARTNRID
, 'SUPPLIER' SUPPLIER_PARTNRTYPE
, INV.INVOICE_CURRENCY_CODE SUPPLIER_CURRENCY
, V.VENDOR_NAME_ALT SUPPLIER_DESCRIPTN
, TO_CHAR(V.VENDOR_ID) SUPPLIER_PARTNRIDX
, DECODE(VS.VAT_CODE
, NULL
, '1'
, '0') SUPPLIER_TAXEXMPT
, NVL(V.VAT_REGISTRATION_NUM
, V.NUM_1099) SUPPLIER_TAXID
, DECODE(VS.COUNTRY
, 'US'
, 'Y'
, 'N') SUPPLIER_UA_USFLAG
, VS.ADDRESS_LINE1 SUPPLIER_ADDRESS_LINE1
, VS.ADDRESS_LINE2 SUPPLIER_ADDRESS_LINE2
, VS.ADDRESS_LINE3 SUPPLIER_ADDRESS_LINE3
, VS.CITY SUPPLIER_ADDRESS_CITY
, VS.COUNTRY SUPPLIER_ADDRESS_COUNTRY
, VS.COUNTY SUPPLIER_ADDRESS_COUNTY
, VS.VENDOR_SITE_CODE SUPPLIER_ADDRESS_DESCRIPTN
, DECODE(VS.FAX_AREA_CODE
, NULL
, NULL
, FAX_AREA_CODE||' ')||VS.FAX SUPPLIER_ADDRESS_FAX
, VS.ZIP SUPPLIER_ADDRESS_POSTALCODE
, NVL(VS.STATE
, VS.PROVINCE) SUPPLIER_ADDRESS_STATEPROVN
, DECODE(VS.AREA_CODE
, NULL
, NULL
, VS.AREA_CODE||' ')||VS.PHONE SUPPLIER_ADDRESS_TELEPHONE
, NULL SUPPLIER_ADDRESS_UA_VATCODE
, VS.PURCHASING_SITE_FLAG SUPPLIER_ADDRESS_UA_PURSITE
, VS.PAY_SITE_FLAG SUPPLIER_ADDRESS_UA_PAYSITE
, VS.RFQ_ONLY_SITE_FLAG SUPPLIER_ADDRESS_UA_RFQSITE
, VS.PCARD_SITE_FLAG SUPPLIER_ADDRESS_UA_PCSITE
, NVL(INV.DISCOUNT_AMOUNT_TAKEN
, 0) AMOUNT_DISCNT_T_AMOUNT
, INV.INVOICE_CURRENCY_CODE AMOUNT_DISCNT_T_CURRENCY_CODE
, DECODE(UPPER(INV.INVOICE_TYPE_LOOKUP_CODE)
, 'CREDIT'
, 'CR'
, 'DR') AMOUNT_DISCNT_T_LOOKUP_CODE
, NVL( INV.DISCOUNT_AMOUNT_TAKEN
, 0) * DECODE(NVL(INV.EXCHANGE_RATE
, 0)
, 0
, 1
, INV.EXCHANGE_RATE) AMOUNT_DISCNT_F_AMOUNT
, SOB.CURRENCY_CODE AMOUNT_DISCNT_F_CURRENCY_CODE
, DECODE(UPPER(INV.INVOICE_TYPE_LOOKUP_CODE)
, 'CREDIT'
, 'CR'
, 'DR') AMOUNT_DISCNT_F_LOOKUP_CODE
, T.DESCRIPTION TERM_DESCRIPTN
, T.NAME TERMID
, NVL(INV.TAX_AMOUNT
, 0) AMOUNT_TAX_T_AMOUNT
, INV.INVOICE_CURRENCY_CODE AMOUNT_TAX_T_CURRENCY_CODE
, DECODE(UPPER(INV.INVOICE_TYPE_LOOKUP_CODE)
, 'CREDIT'
, 'CR'
, 'DR') AMOUNT_TAX_T_LOOKUP_CODE
, NVL(INV.TAX_AMOUNT
, 0) * DECODE(NVL(INV.EXCHANGE_RATE
, 0)
, 0
, 1
, INV.EXCHANGE_RATE) AMOUNT_TAX_F_AMOUNT
, SOB.CURRENCY_CODE AMOUNT_TAX_F_CURRENCY_CODE
, DECODE(UPPER(INV.INVOICE_TYPE_LOOKUP_CODE)
, 'CREDIT'
, 'CR'
, 'DR') AMOUNT_TAX_F_LOOKUP_CODE
, NVL(INV.INVOICE_AMOUNT
, 0) * DECODE(NVL(INV.EXCHANGE_RATE
, 0)
, 0
, 1
, INV.EXCHANGE_RATE) AMOUNT_TAXBASE_F_AMOUNT
, SOB.CURRENCY_CODE AMOUNT_TAXBASE_F_CURRENCY_CODE
, DECODE(UPPER(INV.INVOICE_TYPE_LOOKUP_CODE)
, 'CREDIT'
, 'CR'
, 'DR') AMOUNT_TAXBASE_F_LOOKUP_CODE
, NVL(INV.INVOICE_AMOUNT
, 0) AMOUNT_TAXBASE_T_AMOUNT
, INV.INVOICE_CURRENCY_CODE AMOUNT_TAXBASE_T_CURRENCY_CODE
, DECODE(UPPER(INV.INVOICE_TYPE_LOOKUP_CODE)
, 'CREDIT'
, 'CR'
, 'DR') AMOUNT_TAXBASE_T_LOOKUP_CODE
, INV.VAT_CODE TAX_TAXCODE
FROM FND_USER U
, AP_TERMS T
, GL_SETS_OF_BOOKS SOB
, GL_CODE_COMBINATIONS CC
, PO_VENDOR_SITES_ALL VS
, PO_VENDORS V
, AP_INVOICES_ALL INV
WHERE U.USER_ID = INV.LAST_UPDATED_BY
AND T.TERM_ID (+) = INV.TERMS_ID
AND SOB.SET_OF_BOOKS_ID = INV.SET_OF_BOOKS_ID
AND CC.CODE_COMBINATION_ID (+) = INV.ACCTS_PAY_CODE_COMBINATION_ID
AND VS.ORG_ID (+) = INV.ORG_ID
AND VS.VENDOR_SITE_ID (+) = INV.VENDOR_SITE_ID
AND V.VENDOR_ID = INV.VENDOR_ID