invoice_payment

/* Formatted on 9/19/2011 12:23:40 PM (QP5 v5.115.810.9015) */SELECT AI.INVOICE_ID C_INVOICE_ID,AI.LAST_UPDATE_DATE C_LAST_UPDATE_DATE,PV.SEGMENT1 C_SUPPLIER_CODE,DECODE (:h_sort_by_alternate,'Y',PV.VENDOR_NAME_ALT,'N',PV.VENDOR_NAME)C_SUPPLIER_NAME,DECODE (:h_sort_by_alternate,'Y',PVS.VENDOR_SITE_CODE_ALT,'N',PVS.VENDOR_SITE_CODE)C_SUPPLIER_SITE,PV.VENDOR_NAME C_SUPPLIER_NAME_DISP,PVS.VENDOR_SITE_CODE C_SUPPLIER_SITE_CODE_DISP,AI.INVOICE_NUM C_INVOICE_NUMBER,AI.INVOICE_DATE C_INVOICE_DATE,AI.INVOICE_CURRENCY_CODE C_CURRENCY,AI.INVOICE_AMOUNT C_AMOUNT,AI.EXCHANGE_RATE C_EXCHANGE_RATE,ALC1.DISPLAYED_FIELD C_TAX_CALCULATION,gcc
|| .segment1'.'|| gcc
|| .segment2'.'|| gcc
|| .segment3'.'|| gcc
|| .segment4'.'|| gcc
|| .segment5'.'|| gcc
|| .segment6'.'|| gcc
gcc.segment7,ffv1
|| .DESCRIPTION'.'|| ffv2
|| .DESCRIPTION'.'|| ffv3
|| .DESCRIPTION'.'|| ffv4
|| .DESCRIPTION'.'|| ffv5
|| .DESCRIPTION'.'|| ffv6
|| .DESCRIPTION'.'|| ffv7
LIAB_ACCT_DESC.DESCRIPTION,-- RPAD (&H_ACCT_SEGS, 600) C_INVOICE_ACCT_SEGS , /*1683898 , added rpad*/ FU.USER_NAME C_UPDATED_BY,ALC2.DISPLAYED_FIELD C_INVOICE_TYPE,FDS.NAME C_SEQUENCE_NAME,AI.DOC_SEQUENCE_VALUE C_VOUCHER_NUMBER,AI.CREATION_DATE C_CREATION_DATE,AI.CREATED_BY C_CREATED_BY,CFU.USER_NAME C_CREATED_BY_NAME,AI.LAST_UPDATED_BY C_LAST_UPDATED_BY,AI.SET_OF_BOOKs_ID CP_SET_OF_BOOKS,AI.GL_date,T.NAMEFROM AP_INVOICES_all AI,PO_VENDORS PV,PO_VENDOR_SITES_all PVS,FND_USER FU,FND_USER CFU,AP_LOOKUP_CODES ALC1,AP_LOOKUP_CODES ALC2,FND_DOCUMENT_SEQUENCES FDS,GL_CODE_COMBINATIONS GCC,AP_TERMS T,FND_FLEX_VALUES_VL ffv1,FND_FLEX_VALUES_VL ffv2,FND_FLEX_VALUES_VL ffv3,FND_FLEX_VALUES_VL ffv4,FND_FLEX_VALUES_VL ffv5,FND_FLEX_VALUES_VL ffv6,FND_FLEX_VALUES_VL ffv7
WHERE 1 = 1-- &H_INVOICE_WHERE------/*-- trunc(AI.LAST_UPDATE_DATE) between nvl(trunc(:P_START_UPDATE_DATE),AI.LAST_UPDATE_DATE) and -- nvl(trunc(:P_END_UPDATE_DATE),AI.LAST_UPDATE_DATE) and*/AND TRUNC (AI.LAST_UPDATE_DATE) BETWEEN NVL (:P_START_UPDATE_DATE,TRUNC (AI
.LAST_UPDATE_DATE))AND NVL (:P_END_UPDATE_DATE,TRUNC (AI
.LAST_UPDATE_DATE))AND AI.SET_OF_BOOKS_ID = NVL (:P_SET_OF_BOOKS_ID ,AI.SET_OF_BOOKS_ID )




AND AI.VENDOR_ID = PV.VENDOR_IDAND AI.VENDOR_SITE_ID = PVS.VENDOR_SITE_IDAND AI.LAST_UPDATED_BY = FU.USER_IDAND AI.CREATED_BY = CFU.USER_IDAND (AI.AUTO_TAX_CALC_FLAG = ALC1.LOOKUP_CODEOR ALC1.DISPLAYED_FIELD = 'Line')
AND AI.TERMS_ID = T.TERM_IDAND ALC1.LOOKUP_TYPE = 'AP_TAX_CALCULATION_METHOD'
AND AI.INVOICE_TYPE_LOOKUP_CODE = ALC2.LOOKUP_CODEAND ALC2.LOOKUP_TYPE = 'INVOICE TYPE'
AND AI.ACCTS_PAY_CODE_COMBINATION_ID = GCC.CODE_COMBINATION_IDAND AI.DOC_SEQUENCE_ID = FDS.DOC_SEQUENCE_ID(+)and FDS.NAME = nvl(:P_C_SEQUENCE_NAME,FDS.NAME)AND AI.TERMS_ID = NVL (:P_TERMS_ID, AI.TERMS_ID)AND AI.DOC_SEQUENCE_VALUE BETWEEN NVL (:P_C_VOUCHER_NUMBER_FROM,AI.DOC_SEQUENCE_VALUE)AND NVL (:P_C_VOUCHER_NUMBER_TO,AI.DOC_SEQUENCE_VALUE)AND pv.vendor_id =NVL (:P_SUPPLIER_NAME,pv


.vendor_id) and (gcc.segment1 = ffv1.FLEX_VALUEAND ffv1.FLEX_VALUE_SET_ID = 1014875
OR gcc.segment1 = ffv1.FLEX_VALUEAND ffv1.FLEX_VALUE_SET_ID = 1014876
OR gcc.segment1 = ffv1.FLEX_VALUEAND ffv1.FLEX_VALUE_SET_ID = 1014877
OR gcc.segment1 = ffv1.FLEX_VALUEAND ffv1.FLEX_VALUE_SET_ID = 1014878OR
gcc.segment1 = ffv1.FLEX_VALUEAND ffv1.FLEX_VALUE_SET_ID = 1014874)
AND gcc.segment2 = ffv2.FLEX_VALUEAND ffv2.FLEX_VALUE_SET_ID = 1014869
AND (gcc.segment3 = ffv3.FLEX_VALUEAND ffv3.FLEX_VALUE_SET_ID = 1014870
OR gcc.segment8 = ffv3.FLEX_VALUEAND ffv3.FLEX_VALUE_SET_ID = 1014870)
AND gcc.segment4 = ffv4.FLEX_VALUEAND ffv4.FLEX_VALUE_SET_ID = 1014885
AND gcc.segment5 = ffv5.FLEX_VALUEAND ffv5.FLEX_VALUE_SET_ID = 1014886
AND gcc.segment6 = ffv6.FLEX_VALUEAND ffv6.FLEX_VALUE_SET_ID = 1014887
AND gcc.segment7 = ffv7.FLEX_VALUEAND ffv7.FLEX_VALUE_SET_ID = 1014888ORDER BY FDS.NAME,AI.DOC_SEQUENCE_VALUE,AI.LAST_UPDATE_DATE,DECODE (:h_sort_by_alternate,'Y',PV.VENDOR_NAME_ALT,'N',PV.VENDOR_NAME),DECODE (:h_sort_by_alternate,'Y',PVS.VENDOR_SITE_CODE_ALT,'N',PVS.VENDOR_SITE_CODE),AI.INVOICE_NUM