SELECT  substr(gc2.segment2,1,6)||'-'||v.vendor_name C_VENDOR_NAME,
        decode(upper(:P_SORT_OPTION),
          'VENDOR NAME',decode(:C_VENDOR_NAME_SELECT,
                      '%',upper(v.vendor_name), 
                          v.vendor_name),
                   i.invoice_type_lookup_code) C_PRIMARY_BRK,
        decode(upper(:P_SORT_OPTION),
          'VENDOR NAME',decode(:C_VENDOR_NAME_SELECT,
                      '%', decode(:SORT_BY_ALTERNATE, 'Y', upper(v.vendor_name_alt), upper(v.vendor_name)), 
                              decode(:SORT_BY_ALTERNATE, 'Y', v.vendor_name_alt, v.vendor_name)),
                   i.invoice_type_lookup_code) C_PRIMARY_BRK_REAL,
        v.vendor_name C_SHORT_VENDOR_NAME,
        v.vendor_id C_VENDOR_ID,
        i.vendor_site_id C_CONTACT_SITE_ID,
        vs.vendor_site_code C_VENDOR_SITE_CODE,
        decode(:SORT_BY_ALTERNATE, 'Y', vs.vendor_site_code_alt, vs.vendor_site_code) C_VENDOR_SITE_CODE_BRK,
        nvl(vs.state,' ')  C_VENDOR_STATE,
        nvl(substr(vs.city,1,15),' ') C_VENDOR_CITY,
        ps.payment_num C_REFERENCE_NUMBER,
        i.vendor_site_id C_ADDRESS_ID,
        nvl(substr(i.invoice_type_lookup_code,1,20), ' ') C_INVOICE_TYPE,
        i.invoice_id C_PAYMENT_SCHED_ID,   
        nvl(to_char(ps.due_date,'DD-MON-RR'),' ') C_DUE_DATE,
/* Bug: 1549982 */
/* Bug 2083419 For foreign currencies, show amounts in functional currency */
         decode(i.invoice_currency_code,:C_BASE_CURRENCY_CODE,
           decode(:C_BASE_MIN_ACCT_UNIT,
           0,round(((nvl(XXST_GET_REMAINING_AMOUNT(ps.invoice_id, ps.payment_num, ps.gross_amount, :P_DATE), 0)/(nvl(i.payment_cross_rate,1))) *
                    nvl(i.exchange_rate,1)),:C_BASE_PRECISION),
             round(((nvl(XXST_GET_REMAINING_AMOUNT(ps.invoice_id, ps.payment_num, ps.gross_amount, :P_DATE), 0)/(nvl(i.payment_cross_rate,1))) *
                    nvl(i.exchange_rate,1))/:C_BASE_MIN_ACCT_UNIT) *
                              :C_BASE_MIN_ACCT_UNIT),
       decode(i.exchange_rate,NULL,0,
           decode(:C_BASE_MIN_ACCT_UNIT,
           0,round(((nvl(XXST_GET_REMAINING_AMOUNT(ps.invoice_id, ps.payment_num, ps.gross_amount, :P_DATE), 0)/(nvl(i.payment_cross_rate,1))) *
                    nvl(i.exchange_rate,1)),:C_BASE_PRECISION),
             round(((nvl(XXST_GET_REMAINING_AMOUNT(ps.invoice_id, ps.payment_num, ps.gross_amount, :P_DATE), 0)/(nvl(i.payment_cross_rate,1))) *
                    nvl(i.exchange_rate,1))/:C_BASE_MIN_ACCT_UNIT) *
                              :C_BASE_MIN_ACCT_UNIT)))  C_AMT_DUE_REMAINING,
       decode(i.invoice_currency_code,:C_BASE_CURRENCY_CODE,
           decode(:C_BASE_MIN_ACCT_UNIT,
           0,round(((nvl(ps.gross_amount, 0)/(nvl(i.payment_cross_rate,1))) *
                    nvl(i.exchange_rate,1)),:C_BASE_PRECISION),
             round(((nvl(ps.gross_amount, 0)/(nvl(i.payment_cross_rate,1))) *
                    nvl(i.exchange_rate,1))/:C_BASE_MIN_ACCT_UNIT) *
                              :C_BASE_MIN_ACCT_UNIT),
       decode(i.exchange_rate,NULL,0,
           decode(:C_BASE_MIN_ACCT_UNIT,
           0,round(((nvl(ps.gross_amount, 0)/(nvl(i.payment_cross_rate,1))) *
                    nvl(i.exchange_rate,1)),:C_BASE_PRECISION),
             round(((nvl(ps.gross_amount, 0)/(nvl(i.payment_cross_rate,1))) *
                    nvl(i.exchange_rate,1))/:C_BASE_MIN_ACCT_UNIT) *
                              :C_BASE_MIN_ACCT_UNIT)))  C_AMT_DUE_ORIGINAL,
        i.accts_pay_code_combination_id  C_VENDOR_TRX_ID,
        i.invoice_num C_INVOICE_NUMBER,
        i.invoice_num C_INVOICE_NUM_SHORT,
        to_char(i.invoice_date,'DD-MON-RR') C_INVOICE_DATE,   
        -- ceil(to_date(to_char(SYSDATE,'DD-MON-RR')) -
       ceil(:P_DATE -  --modified by CFE 18/11/2004  
                     ps.due_date) C_DAYS_PAST_DUE,
        decode(i.invoice_currency_code,
           :C_BASE_CURRENCY_CODE, ' ',
                                  decode(i.exchange_rate, 
                                     NULL,'*', 
                                          ' ')) C_DATA_CONVERTED,
        nvl(i.exchange_rate, 1) C_EXCHANGE_RATE,
        decode(:C_VENDOR_NAME_SELECT,
           '%',decode(:SORT_BY_ALTERNATE, 'Y',  upper(v.vendor_name_alt), upper(v.vendor_name)),
                  decode(:SORT_BY_ALTERNATE, 'Y', v.vendor_name_alt, v.vendor_name)) C_VENDOR_NAME_BRK

            ,gc.segment1     C_SEGMENT1     -- code projet

FROM    ap_payment_schedules ps,
        ap_invoices i,
        po_vendors v,
        po_vendor_sites vs
-- Début ajout B.Saerens le 18/02/2004
      ,gl_code_combinations gc
     ,gl_code_combinations gc2
--       xxst_flex_value_hierarchy_v2 a,
--       fnd_flex_value_norm_hierarchy b   
-- Fin ajout B.Saerens le 18/02/2004
WHERE   i.invoice_id = ps.invoice_id
AND     i.vendor_id = v.vendor_id
AND     i.vendor_site_id = vs.vendor_site_id

AND     i.gl_date     <= :P_DATE
AND     gc.code_combination_id = (select d.DIST_CODE_COMBINATION_ID
                                                        from ap_invoice_distributions d
                                                        where d.invoice_id = i.invoice_id
                                                         and LINE_TYPE_LOOKUP_CODE = 'ITEM'
                                                         and rownum = 1)
and gc2.code_combination_id = i.accts_pay_code_combination_id
--and    gc.segment1 = b.child_flex_value_low
--and    a.flex_value_set_id = b.flex_value_set_id
--and a.parent_flex_value = b.parent_flex_value
and    (EXISTS	(	select	'x' 
			from	xxst_flex_value_hierarchy_v2		a
			,	fnd_flex_value_norm_hierarchy	b
			where	a.flex_value_set_id = b.flex_value_set_id
			and	a.parent_flex_value = b.parent_flex_value
			and	b.child_flex_value_low = gc.segment1)
OR fnd_profile.value('XXST_GL_FULL_ACCESS')='Y'
)
and    EXISTS      (Select aid.invoice_id, min(trunc(aid.accounting_date))
                          from  ap_invoice_distributions_all aid
                          Where 
                                aid.invoice_id = i.invoice_id
                          group by aid.invoice_id, trunc(aid.accounting_date)
                          having sum(aid.amount) != 0  And min(trunc(aid.accounting_date)) <= :P_DATE )

&P_VENDOR_PREDICATE
-- AND     ((to_date(to_char(SYSDATE,'DD-MON-RR')) - ps.due_date)
AND     (trunc(:P_DATE) - trunc(ps.due_date)  -- modified by CFE 18/11/2004 
              between :C_MINDAYS and  :C_MAXDAYS)
AND     i.invoice_type_lookup_code like :C_INVOICE_TYPE_SELECT
AND    ( (i.cancelled_date IS NULL) or (i.cancelled_date > :P_date)) --modified by CFE 06/03/05
AND     (nvl(XXST_GET_REMAINING_AMOUNT(ps.invoice_id, ps.payment_num, ps.gross_amount, :P_DATE), 0) * nvl(i.exchange_rate,1))  != 0
&P_AMOUNT_PREDICATE
-- AND     i.payment_status_flag in ('N','P') --modified by CFE 25/11/2004 
&P_ORDER_BY