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.

APPS.IEX_COLL_IND SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 39


   /* SELECT sum(TRUNC(sysdate) - ps.due_date)
          / COUNT(1)
      INTO   l_avg_days_late
      FROM   ar_payment_schedules ps, hz_cust_accounts ca
      WHERE  ps.gl_date between TRUNC(add_months(sysdate, -12)) and  TRUNC(sysdate)
      AND    ps.class in ('INV','DEP','DM','CB')
      AND    ps.gl_date_closed > TRUNC(sysdate)
      -- Begin fix bug #4949609-JYPARK-2/15/2006-add condition for performance
      AND    ps.status = 'OP'
      -- End fix bug #4949609-JYPARK-2/15/2006-add condition for performance
      AND    ps.due_date       < TRUNC(sysdate)
      AND    ps.payment_schedule_id <> -1
      AND    ca.cust_account_id = ps.customer_id
      AND    ca.party_id = p_party_id;

Line: 54


    SELECT sum(TRUNC(sysdate) - ps.due_date)
          / COUNT(1)
      INTO   l_avg_days_late
      FROM   ar_payment_schedules ps
      WHERE  ps.gl_date between TRUNC(add_months(sysdate, -12)) and  TRUNC(sysdate)
      AND    ps.class in ('INV','DEP','DM','CB')
      AND    ps.gl_date_closed > TRUNC(sysdate)
      -- Begin fix bug #4949609-JYPARK-2/15/2006-add condition for performance
      AND    ps.status = 'OP'
      -- End fix bug #4949609-JYPARK-2/15/2006-add condition for performance
      AND    ps.due_date       < TRUNC(sysdate)
      AND    ps.payment_schedule_id <> -1
      AND    ps.customer_id = p_cust_account_id;

Line: 68


    SELECT sum(TRUNC(sysdate) - ps.due_date)
          / COUNT(1)
      INTO   l_avg_days_late
      FROM   ar_payment_schedules ps
      WHERE  ps.gl_date between TRUNC(add_months(sysdate, -12)) and  TRUNC(sysdate)
      AND    ps.class in ('INV','DEP','DM','CB')
      AND    ps.gl_date_closed > TRUNC(sysdate)
      -- Begin fix bug #4949609-JYPARK-2/15/2006-add condition for performance
      AND    ps.status = 'OP'
      -- End fix bug #4949609-JYPARK-2/15/2006-add condition for performance
      AND    ps.due_date       < TRUNC(sysdate)
      AND    ps.payment_schedule_id <> -1
      AND    ps.customer_site_use_id = p_customer_site_use_id;*/

Line: 83


     SELECT round(sum(TRUNC(decode(sign(gl_date_closed-trunc(sysdate)),1,sysdate,ps.gl_date_closed)) - ps.due_date)
         / COUNT(1)) AVG_DAYS_LATE
	 INTO   l_avg_days_late
         FROM   ar_payment_schedules ps, iex_delinquencies del
      WHERE  ps.gl_date between TRUNC(add_months(sysdate, -12)) and  TRUNC(sysdate)
      and ps.payment_schedule_id=del.payment_schedule_id
      AND    ps.class in ('INV','DEP','DM','CB')
      AND    ps.due_date       <= TRUNC(sysdate)
      AND    ps.payment_schedule_id <> -1
      AND    del.party_cust_id = p_party_id;

Line: 94


    SELECT  round(sum(TRUNC(decode(sign(gl_date_closed-trunc(sysdate)),1,sysdate,ps.gl_date_closed)) - ps.due_date)
          / COUNT(1))AVG_DAYS_LATE
      INTO   l_avg_days_late
      FROM   ar_payment_schedules ps, iex_delinquencies del
      WHERE  ps.gl_date between TRUNC(add_months(sysdate, -12)) and  TRUNC(sysdate)
       and ps.payment_schedule_id=del.payment_schedule_id
      AND    ps.class in ('INV','DEP','DM','CB')
      AND    ps.due_date       <= TRUNC(sysdate)
      AND    ps.payment_schedule_id <> -1
      and ps.customer_id=p_cust_account_id;

Line: 105


    SELECT  round(sum(TRUNC(decode(sign(gl_date_closed-trunc(sysdate)),1,sysdate,ps.gl_date_closed)) - ps.due_date)
          / COUNT(1))AVG_DAYS_LATE
      INTO   l_avg_days_late
      FROM   ar_payment_schedules ps,iex_delinquencies del
      WHERE  ps.gl_date between TRUNC(add_months(sysdate, -12)) and  TRUNC(sysdate)
      and    ps.payment_schedule_id=del.payment_schedule_id
      AND    ps.class in ('INV','DEP','DM','CB')
      AND    ps.due_date       <= TRUNC(sysdate)
      AND    ps.payment_schedule_id <> -1
      AND ps.customer_site_use_id = p_customer_site_use_id;

Line: 171


  /*  SELECT sum
             (
               (
                 GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
                 GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
                 nvl(ps.acctd_amount_due_remaining, 0)
               ) *
               (TRUNC(sysdate) - ps.due_date)
             )  /
             sum (
               GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
               GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
               nvl(ps.acctd_amount_due_remaining, 0)
             )
      INTO   l_wtd_days_late
      FROM   ar_payment_schedules ps, hz_cust_accounts ca
      WHERE  ps.gl_date between TRUNC(add_months(sysdate, -12)) and  TRUNC(sysdate)
      AND    ps.class in ('INV','DEP','DM','CB')
      -- Begin fix bug #4917851-jypark-11/47/2005-remove invalid condition
      --AND    ps.gl_date_closed > TRUNC(sysdate)
      --AND    ps.due_date       < TRUNC(sysdate)
      -- End fix bug #4917851-jypark-11/47/2005-remove invalid condition
      -- Begin fix bug #4949609-JYPARK-2/15/2006-add condition for performance
      AND    ps.status = 'OP'
      -- End fix bug #4949609-JYPARK-2/15/2006-add condition for performance
      AND    ps.payment_schedule_id <> -1
      AND    ps.customer_id = ca.cust_account_id
      AND    ca.party_id = p_party_id;

Line: 200


    SELECT sum
             (
               (
                 GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
                 GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
                 nvl(ps.acctd_amount_due_remaining, 0)
               ) *
               (TRUNC(sysdate) - ps.due_date)
             )  /
             sum (
               GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
               GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
               nvl(ps.acctd_amount_due_remaining, 0)
             )
      INTO   l_wtd_days_late
      FROM   ar_payment_schedules 	ps
      WHERE  ps.gl_date between TRUNC(add_months(sysdate, -12)) and  TRUNC(sysdate)
      AND    ps.class in ('INV','DEP','DM','CB')
      -- Begin fix bug #4917851-jypark-11/47/2005-remove invalid condition
      -- AND    ps.gl_date_closed > TRUNC(sysdate)
      -- AND    ps.due_date       < TRUNC(sysdate)
      -- End fix bug #4917851-jypark-11/47/2005-remove invalid condition
      -- Begin fix bug #4949609-JYPARK-2/15/2006-add condition for performance
      AND    ps.status = 'OP'
      -- End fix bug #4949609-JYPARK-2/15/2006-add condition for performance
      AND    ps.payment_schedule_id <> -1
      AND    ps.customer_id = p_cust_account_id;

Line: 228


    SELECT sum
             (
               (
                 GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
                 GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
                 nvl(ps.acctd_amount_due_remaining, 0)
               ) *
               (TRUNC(sysdate) - ps.due_date)
             )  /
             sum (
               GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
               GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
               nvl(ps.acctd_amount_due_remaining, 0)
             )
      INTO   l_wtd_days_late
      FROM   ar_payment_schedules 	ps
      WHERE  ps.gl_date between TRUNC(add_months(sysdate, -12)) and  TRUNC(sysdate)
      AND    ps.class in ('INV','DEP','DM','CB')
      -- Begin fix bug #4917851-jypark-11/47/2005-remove invalid condition
      -- AND    ps.gl_date_closed > TRUNC(sysdate)
      -- AND    ps.due_date       < TRUNC(sysdate)
      -- End fix bug #4917851-jypark-11/47/2005-remove invalid condition
      -- Begin fix bug #4949609-JYPARK-2/15/2006-add condition for performance
      AND    ps.status = 'OP'
      -- End fix bug #4949609-JYPARK-2/15/2006-add condition for performance
      AND    ps.payment_schedule_id <> -1
      AND    ps.customer_site_use_id = p_customer_site_use_id;*/

Line: 257


    SELECT round(sum
             (
               (
                 GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
                 GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
                 nvl(ps.acctd_amount_due_remaining, 0)
               ) *
               (TRUNC(decode(sign(gl_date_closed-trunc(sysdate)),1,sysdate,ps.gl_date_closed)) - ps.due_date)
             )  /
             sum (
               GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
               GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
               nvl(ps.acctd_amount_due_remaining, 0)
             ))WTD_AVG_DAYS_LATE
      INTO   l_wtd_days_late
      FROM   ar_payment_schedules ps,iex_delinquencies del
      WHERE  ps.gl_date between TRUNC(add_months(sysdate, -12)) and  TRUNC(sysdate)
      and ps.payment_schedule_id=del.payment_schedule_id
      AND    ps.class in ('INV','DEP','DM','CB')
      AND    ps.due_date       <= TRUNC(sysdate)
      AND    ps.payment_schedule_id <> -1
      and del.party_cust_id=p_party_id;

Line: 281


    SELECT round(sum
             (
               (
                 GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
                 GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
                 nvl(ps.acctd_amount_due_remaining, 0)
               ) *
               (TRUNC(decode(sign(gl_date_closed-trunc(sysdate)),1,sysdate,ps.gl_date_closed)) - ps.due_date)
             )  /
             sum (
               GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
               GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
               nvl(ps.acctd_amount_due_remaining, 0)
             ))WTD_AVG_DAYS_LATE
      INTO   l_wtd_days_late
      FROM   ar_payment_schedules 	ps, iex_delinquencies del
      WHERE  ps.gl_date between TRUNC(add_months(sysdate, -12)) and  TRUNC(sysdate)
       and ps.payment_schedule_id=del.payment_schedule_id
      AND    ps.class in ('INV','DEP','DM','CB')
      AND    ps.due_date       <= TRUNC(sysdate)
      and ps.payment_schedule_id <> -1
     and ps.customer_id=p_cust_account_id;

Line: 305


    SELECT round(sum
             (
               (
                 GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
                 GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
                 nvl(ps.acctd_amount_due_remaining, 0)
               ) *
               (TRUNC(decode(sign(gl_date_closed-trunc(sysdate)),1,sysdate,ps.gl_date_closed)) - ps.due_date)
             )  /
             sum (
               GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
               GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
               nvl(ps.acctd_amount_due_remaining, 0)
             ))WTD_AVG_DAYS_LATE
      INTO   l_wtd_days_late
      FROM   ar_payment_schedules 	ps, iex_delinquencies del
      WHERE  ps.gl_date between TRUNC(add_months(sysdate, -12)) and  TRUNC(sysdate)
      and ps.payment_schedule_id=del.payment_schedule_id
      AND    ps.class in ('INV','DEP','DM','CB')
      AND    ps.due_date       <= TRUNC(sysdate)
      and ps.payment_schedule_id <> -1
     and ps.customer_site_use_id=p_customer_site_use_id;

Line: 378


    SELECT ROUND(SUM((ra.apply_date - ps.trx_date) * ra.amount_applied)
                / SUM(ra.amount_applied)
              , 0) WEIGHTED_AVG_DAYS_PAID
      INTO l_wtd_days_paid
      FROM ar_receivable_applications ra,
           ar_payment_schedules ps,
           hz_cust_accounts ca
     WHERE ps.customer_id = ca.cust_account_id
       AND ca.party_id = p_party_id
       AND ra.status = 'APP'
       AND ps.payment_schedule_id = ra.applied_payment_schedule_id
       AND ps.gl_date between TRUNC(add_months(sysdate, -12)) and  TRUNC(sysdate)
       AND ps.class in ('INV','DEP','DM','CB')
       -- Begin fix bug #4917851-jypark-11/47/2005-remove invalid condition
       -- AND ps.gl_date_closed > TRUNC(sysdate)
       -- AND ps.due_date       < TRUNC(sysdate)
       -- End fix bug #4917851-jypark-11/47/2005-remove invalid condition
      -- Begin fix bug #4949604-JYPARK-2/15/2006-add condition for performance
      AND    ps.status = 'CL'
      -- End fix bug #4949604-JYPARK-2/15/2006-add condition for performance
       AND ps.payment_schedule_id <> -1;

Line: 400


    SELECT ROUND(SUM((ra.apply_date - ps.trx_date) * ra.amount_applied)
                / SUM(ra.amount_applied)
              , 0) WEIGHTED_AVG_DAYS_PAID
      INTO l_wtd_days_paid
      FROM ar_receivable_applications ra,
           ar_payment_schedules ps
     WHERE ps.customer_id = p_cust_account_id
       AND ra.status = 'APP'
       AND ps.payment_schedule_id = ra.applied_payment_schedule_id
       AND ps.gl_date between TRUNC(add_months(sysdate, -12)) and  TRUNC(sysdate)
       AND ps.class in ('INV','DEP','DM','CB')
       -- Begin fix bug #4917851-jypark-11/47/2005-remove invalid condition
       -- AND ps.gl_date_closed > TRUNC(sysdate)
       -- AND ps.due_date       < TRUNC(sysdate)
       -- End fix bug #4917851-jypark-11/47/2005-remove invalid condition
      -- Begin fix bug #4949604-JYPARK-2/15/2006-add condition for performance
      AND    ps.status = 'CL'
      -- End fix bug #4949604-JYPARK-2/15/2006-add condition for performance
       AND ps.payment_schedule_id <> -1;

Line: 420


    SELECT ROUND(SUM((ra.apply_date - ps.trx_date) * ra.amount_applied)
                / SUM(ra.amount_applied)
              , 0) WEIGHTED_AVG_DAYS_PAID
      INTO l_wtd_days_paid
      FROM ar_receivable_applications ra,
           ar_payment_schedules ps
     WHERE ps.customer_site_use_id = p_customer_site_use_id
       AND ra.status = 'APP'
       AND ps.payment_schedule_id = ra.applied_payment_schedule_id
       AND ps.gl_date between TRUNC(add_months(sysdate, -12)) and  TRUNC(sysdate)
       AND ps.class in ('INV','DEP','DM','CB')
       -- Begin fix bug #4917851-jypark-11/47/2005-remove invalid condition
       -- AND ps.gl_date_closed > TRUNC(sysdate)
       -- AND ps.due_date       < TRUNC(sysdate)
       -- End fix bug #4917851-jypark-11/47/2005-remove invalid condition
      -- Begin fix bug #4949604-JYPARK-2/15/2006-add condition for performance
      AND    ps.status = 'CL'
      -- End fix bug #4949604-JYPARK-2/15/2006-add condition for performance
       AND ps.payment_schedule_id <> -1;

Line: 489


    SELECT sum
             (
               (
                 GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
                 GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
                 nvl(ps.acctd_amount_due_remaining, 0)
               ) *
               (ps.due_date - ps.trx_date)
             )  /
             sum (
               GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
               GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
               nvl(ps.acctd_amount_due_remaining, 0)
             )
      INTO   l_wtd_days_terms
      FROM   ar_payment_schedules ps, hz_cust_accounts ca
      WHERE  ps.gl_date between TRUNC(add_months(sysdate, -12)) and  TRUNC(sysdate)
      AND    ps.class in ('INV','DEP','DM','CB')
       -- Begin fix bug #4917851-jypark-11/47/2005-remove invalid condition
       -- AND ps.gl_date_closed > TRUNC(sysdate)
       -- AND ps.due_date       < TRUNC(sysdate)
       -- End fix bug #4917851-jypark-11/47/2005-remove invalid condition
      AND    ps.payment_schedule_id <> -1
      AND    ps.customer_id = ca.cust_account_id
      AND    ca.party_id = p_party_id;

Line: 515


    SELECT sum
             (
               (
                 GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
                 GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
                 nvl(ps.acctd_amount_due_remaining, 0)
               ) *
               (ps.due_date - ps.trx_date)
             )  /
             sum (
               GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
               GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
               nvl(ps.acctd_amount_due_remaining, 0)
             )
      INTO   l_wtd_days_terms
      FROM   ar_payment_schedules 	ps
      WHERE  ps.gl_date between TRUNC(add_months(sysdate, -12)) and  TRUNC(sysdate)
      AND    ps.class in ('INV','DEP','DM','CB')
       -- Begin fix bug #4917851-jypark-11/47/2005-remove invalid condition
       -- AND ps.gl_date_closed > TRUNC(sysdate)
       -- AND ps.due_date       < TRUNC(sysdate)
       -- End fix bug #4917851-jypark-11/47/2005-remove invalid condition
      AND    ps.payment_schedule_id <> -1
      AND    ps.customer_id = p_cust_account_id;

Line: 540


    SELECT sum
             (
               (
                 GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
                 GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
                 nvl(ps.acctd_amount_due_remaining, 0)
               ) *
               (ps.due_date - ps.trx_date)
             )  /
             sum (
               GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
               GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
               nvl(ps.acctd_amount_due_remaining, 0)
             )
      INTO   l_wtd_days_terms
      FROM   ar_payment_schedules 	ps
      WHERE  ps.gl_date between TRUNC(add_months(sysdate, -12)) and  TRUNC(sysdate)
      AND    ps.class in ('INV','DEP','DM','CB')
       -- Begin fix bug #4917851-jypark-11/47/2005-remove invalid condition
       -- AND ps.gl_date_closed > TRUNC(sysdate)
       -- AND ps.due_date       < TRUNC(sysdate)
       -- End fix bug #4917851-jypark-11/47/2005-remove invalid condition
      AND    ps.payment_schedule_id <> -1
      AND    ps.customer_site_use_id = p_customer_site_use_id;

Line: 680


    SELECT
         ROUND(
           ( (SUM( DECODE(PS.CLASS,
                         'INV', 1,
                         'DM',  1,
                         'CB',  1,
                         'DEP', 1,
                         'BR',  1,
                          0)
                    * PS.ACCTD_AMOUNT_DUE_REMAINING
                  ) * MAX(SP.CER_DSO_DAYS)
              )
              / DECODE(
                     SUM( DECODE(PS.CLASS,
                                'INV', 1,
                                'DM',  1,
                                'DEP', 1,
                                 0)
                           * DECODE(SIGN (TRUNC(SYSDATE) - PS.TRX_DATE - SP.CER_DSO_DAYS),
                                    -1, (PS.AMOUNT_DUE_ORIGINAL  + NVL(PS.AMOUNT_ADJUSTED,0)) * NVL(PS.EXCHANGE_RATE, 1 ),
                                     0)) ,
                     0, 1,
                     SUM( DECODE(PS.CLASS,
                                'INV', 1,
                                'DM',  1,
                                'DEP', 1,
                                 0)
                          * DECODE(SIGN (TRUNC(SYSDATE) - PS.TRX_DATE - SP.CER_DSO_DAYS),
                                   -1, (PS.AMOUNT_DUE_ORIGINAL  + NVL(PS.AMOUNT_ADJUSTED,0)) * NVL(PS.EXCHANGE_RATE, 1 ),
                                   0) )
                      )
            ), 0)                                     /* DSO */
    INTO l_conv_dso
    FROM ar_system_parameters         sp,
         hz_cust_accounts             cust_acct,
         ar_payment_schedules         ps
    WHERE ps.customer_id = cust_acct.cust_account_id
    AND cust_acct.party_id = p_party_id
    -- Begin fix bug #5261855-jypark-06/16/2006-add addtional condition for performance
    AND ps.status = 'OP'
    -- End fix bug #5261855-jypark-06/16/2006-add addtional condition for performance
    AND NVL(ps.receipt_confirmed_flag,'Y') = 'Y';

Line: 723


    SELECT
          ROUND(
           ( (SUM( DECODE(PS.CLASS,
                         'INV', 1,
                         'DM',  1,
                         'CB',  1,
                         'DEP', 1,
                         'BR',  1,
                          0)
                    * PS.ACCTD_AMOUNT_DUE_REMAINING
                  ) * MAX(SP.CER_DSO_DAYS)
              )
              / DECODE(
                     SUM( DECODE(PS.CLASS,
                                'INV', 1,
                                'DM',  1,
                                'DEP', 1,
                                 0)
                           * DECODE(SIGN (TRUNC(SYSDATE) - PS.TRX_DATE - SP.CER_DSO_DAYS),
                                    -1, (PS.AMOUNT_DUE_ORIGINAL  + NVL(PS.AMOUNT_ADJUSTED,0)) * NVL(PS.EXCHANGE_RATE, 1 ),
                                     0)) ,
                     0, 1,
                     SUM( DECODE(PS.CLASS,
                                'INV', 1,
                                'DM',  1,
                                'DEP', 1,
                                 0)
                          * DECODE(SIGN (TRUNC(SYSDATE) - PS.TRX_DATE - SP.CER_DSO_DAYS),
                                   -1, (PS.AMOUNT_DUE_ORIGINAL  + NVL(PS.AMOUNT_ADJUSTED,0)) * NVL(PS.EXCHANGE_RATE, 1 ),
                                   0) )
                      )
            ), 0)                                     /* DSO */
    INTO l_conv_dso
    FROM ar_system_parameters         sp,
         ar_payment_schedules         ps
    WHERE ps.customer_id = p_cust_account_id
    -- Begin fix bug #5261855-jypark-06/16/2006-add addtional condition for performance
    AND ps.status = 'OP'
    -- End fix bug #5261855-jypark-06/16/2006-add addtional condition for performance
    AND NVL(ps.receipt_confirmed_flag,'Y') = 'Y';

Line: 765


    SELECT
          ROUND(
           ( (SUM( DECODE(PS.CLASS,
                         'INV', 1,
                         'DM',  1,
                         'CB',  1,
                         'DEP', 1,
                         'BR',  1,
                          0)
                    * PS.ACCTD_AMOUNT_DUE_REMAINING
                  ) * MAX(SP.CER_DSO_DAYS)
              )
              / DECODE(
                     SUM( DECODE(PS.CLASS,
                                'INV', 1,
                                'DM',  1,
                                'DEP', 1,
                                 0)
                           * DECODE(SIGN (TRUNC(SYSDATE) - PS.TRX_DATE - SP.CER_DSO_DAYS),
                                    -1, (PS.AMOUNT_DUE_ORIGINAL  + NVL(PS.AMOUNT_ADJUSTED,0)) * NVL(PS.EXCHANGE_RATE, 1 ),
                                     0)) ,
                     0, 1,
                     SUM( DECODE(PS.CLASS,
                                'INV', 1,
                                'DM',  1,
                                'DEP', 1,
                                 0)
                          * DECODE(SIGN (TRUNC(SYSDATE) - PS.TRX_DATE - SP.CER_DSO_DAYS),
                                   -1, (PS.AMOUNT_DUE_ORIGINAL  + NVL(PS.AMOUNT_ADJUSTED,0)) * NVL(PS.EXCHANGE_RATE, 1 ),
                                   0) )
                      )
            ), 0)                                     /* DSO */
    INTO l_conv_dso
    FROM ar_system_parameters         sp,
         ar_payment_schedules         ps
    WHERE ps.customer_site_use_id = p_customer_site_use_id
    -- Begin fix bug #5261855-jypark-06/16/2006-add addtional condition for performance
    AND ps.status = 'OP'
    -- End fix bug #5261855-jypark-06/16/2006-add addtional condition for performance
    AND NVL(ps.receipt_confirmed_flag,'Y') = 'Y';

Line: 832


    SELECT COUNT(cr.cash_receipt_id) NSF_STOP_PAYMENT_COUNT
    INTO l_nsf_stop_payment_count
    FROM  ar_cash_receipts cr,
          ar_cash_receipt_history crh,
          hz_cust_accounts ca
    WHERE cr.cash_receipt_id = crh.cash_receipt_id
      AND crh.current_record_flag = 'Y'
      AND crh.status = 'REVERSED'
-- BEGIN fix bug #4483830--20050714-jypark-change query for NSF info
--      AND cr.status = 'REV'
--      AND cr.status = 'NSF' -- bug 5613019
        AND cr.status in ('NSF','REV') -- bug 5613019
-- END fix bug #4483830--20050714-jypark-change query for NSF info
      --AND cr.reversal_category = 'NSF'  -- big 5613019
      AND cr.reversal_category in ('NSF','REV') -- bug 5613019
      AND cr.reversal_date BETWEEN TRUNC(add_months(sysdate, -12)) AND TRUNC(sysdate)
      AND cr.pay_from_customer = ca.cust_account_id
      AND ca.party_id = p_party_id;

Line: 851


    SELECT COUNT(cr.cash_receipt_id) NSF_STOP_PAYMENT_COUNT
    INTO l_nsf_stop_payment_count
    FROM  ar_cash_receipts cr,
          ar_cash_receipt_history crh
    WHERE cr.cash_receipt_id = crh.cash_receipt_id
      AND crh.current_record_flag = 'Y'
      AND crh.status = 'REVERSED'
-- BEGIN fix bug #4483830--20050714-jypark-change query for NSF info
--      AND cr.status = 'REV'
--      AND cr.status = 'NSF'  --bug 5613019
        AND cr.status in ('NSF','REV') -- bug 5613019
-- END fix bug #4483830--20050714-jypark-change query for NSF info
      --AND cr.reversal_category = 'NSF'  -- bug 5613019
      and cr.reversal_category in ('NSF','REV') -- bug 5613019
      AND cr.reversal_date BETWEEN TRUNC(add_months(sysdate, -12)) AND TRUNC(sysdate)
      AND cr.pay_from_customer = p_cust_account_id;

Line: 868


    SELECT COUNT(cr.cash_receipt_id) NSF_STOP_PAYMENT_COUNT
    INTO l_nsf_stop_payment_count
    FROM  ar_cash_receipts cr,
          ar_cash_receipt_history crh
    WHERE cr.cash_receipt_id = crh.cash_receipt_id
      AND crh.current_record_flag = 'Y'
      AND crh.status = 'REVERSED'
-- BEGIN fix bug #4483830--20050714-jypark-change query for NSF info
--      AND cr.status = 'REV'
--      AND cr.status = 'NSF'  --bug 5613019
        and cr.status in ('NSF','REV') -- bug 5613019
-- END fix bug #4483830--20050714-jypark-change query for NSF info
      -- AND cr.reversal_category = 'NSF'  -- bug 5613019
      and cr.reversal_category in ('NSF','REV') -- bug 5613019
      AND cr.reversal_date BETWEEN TRUNC(add_months(sysdate, -12)) AND TRUNC(sysdate)
      AND cr.customer_site_use_id = p_customer_site_use_id;

Line: 909


    SELECT SUM(cr.amount) NSF_STOP_PAYMENT_AMOUNT
    INTO l_nsf_stop_payment_amount
    FROM  ar_cash_receipts_all cr,
          ar_cash_receipt_history_all crh,
          hz_cust_accounts ca
    WHERE cr.cash_receipt_id = crh.cash_receipt_id
      AND crh.current_record_flag = 'Y'
      AND crh.status = 'REVERSED'
-- BEGIN fix bug #4483830--20050714-jypark-change query for NSF info
--      AND cr.status = 'REV'
--      AND cr.status = 'NSF'  -- bug 5613019
        and cr.status in ('NSF','REV') -- bug 5613019
-- END fix bug #4483830--20050714-jypark-change query for NSF info
      -- AND cr.reversal_category = 'NSF' --bug 5613019
      and cr.reversal_category in ('NSF','REV') -- bug 5613019
      AND cr.reversal_date BETWEEN TRUNC(add_months(sysdate, -12)) AND TRUNC(sysdate)
      AND cr.pay_from_customer = ca.cust_account_id
      AND ca.party_id = p_party_id;

Line: 928


    SELECT SUM(cr.amount) NSF_STOP_PAYMENT_AMOUNT
    INTO l_nsf_stop_payment_amount
    FROM  ar_cash_receipts_all cr,
          ar_cash_receipt_history_all crh
    WHERE cr.cash_receipt_id = crh.cash_receipt_id
      AND crh.current_record_flag = 'Y'
      AND crh.status = 'REVERSED'
-- BEGIN fix bug #4483830--20050714-jypark-change query for NSF info
--      AND cr.status = 'REV'
--      AND cr.status = 'NSF'  -- bug 5613019
        and cr.status in ('NSF','REV') -- bug 5613019
-- END fix bug #4483830--20050714-jypark-change query for NSF info
      -- AND cr.reversal_category = 'NSF' --bug 5613019
      and cr.reversal_category in ('NSF','REV') -- bug 5613019
      AND cr.reversal_date BETWEEN TRUNC(add_months(sysdate, -12)) AND TRUNC(sysdate)
      AND cr.pay_from_customer = p_cust_account_id;

Line: 945


    SELECT SUM(cr.amount) NSF_STOP_PAYMENT_AMOUNT
    INTO l_nsf_stop_payment_amount
    FROM  ar_cash_receipts_all cr,
          ar_cash_receipt_history_all crh
    WHERE cr.cash_receipt_id = crh.cash_receipt_id
      AND crh.current_record_flag = 'Y'
      AND crh.status = 'REVERSED'
-- BEGIN fix bug #4483830--20050714-jypark-change query for NSF info
--      AND cr.status = 'REV'
--      AND cr.status = 'NSF' -- bug5613019
        and cr.status in ('NSF','REV') -- bug 5613019
-- END fix bug #4483830--20050714-jypark-change query for NSF info
      -- AND cr.reversal_category = 'NSF'  -- bug 5613019
      and cr.reversal_category in ('NSF','REV') -- bug 5613019
      AND cr.reversal_date BETWEEN TRUNC(add_months(sysdate, -12)) AND TRUNC(sysdate)
      AND cr.customer_site_use_id = p_customer_site_use_id;

Line: 1009


    SELECT
       sum( nvl(adj.acctd_amount,0))
    INTO    l_adj
    FROM    ar_payment_schedules ps, ar_adjustments adj, hz_cust_accounts ca
    WHERE   ps.class in ('INV', 'DM', 'CB', 'DEP' )
    AND     ps.payment_schedule_id <> -1
    AND     ps.gl_date BETWEEN TRUNC(add_months(sysdate, -12)) AND TRUNC(sysdate)
    AND     ps.customer_id = ca.cust_account_id
    AND     ca.party_id = p_party_id
    AND     adj.payment_schedule_id = ps.payment_schedule_id
    AND     adj.status = 'A'
    AND     adj.gl_date <= TRUNC(sysdate);

Line: 1022


    SELECT
       sum( nvl(adj.acctd_amount,0))
    INTO    l_adj
    FROM    ar_payment_schedules ps, ar_adjustments adj
    WHERE   ps.class in ('INV', 'DM', 'CB', 'DEP' )
    AND     ps.payment_schedule_id <> -1
    AND     ps.gl_date BETWEEN TRUNC(add_months(sysdate, -12)) AND TRUNC(sysdate)
    AND     ps.customer_id = p_cust_account_id
    AND     adj.payment_schedule_id = ps.payment_schedule_id
    AND     adj.status = 'A'
    AND     adj.gl_date <= TRUNC(sysdate);

Line: 1034


    SELECT
       sum( nvl(adj.acctd_amount,0))
    INTO    l_adj
    FROM    ar_payment_schedules ps, ar_adjustments adj
    WHERE   ps.class in ('INV', 'DM', 'CB', 'DEP' )
    AND     ps.payment_schedule_id <> -1
    AND     ps.gl_date BETWEEN TRUNC(add_months(sysdate, -12)) AND TRUNC(sysdate)
    AND     ps.customer_site_use_id = p_customer_site_use_id
    AND     adj.payment_schedule_id = ps.payment_schedule_id
    AND     adj.status = 'A'
    AND     adj.gl_date <= TRUNC(sysdate);

Line: 1111


    SELECT  SUM(arpcurr.functional_amount(
      ps.amount_due_original,
      g_curr_rec.base_currency,
      nvl(ps.exchange_rate,1),
      g_curr_rec.base_precision,
      g_curr_rec.base_min_acc_unit) +
       GET_ADJ_FOR_TOT_REC(ps.payment_schedule_id,p_end_date))
    INTO    l_tot_rec
    FROM    ar_payment_schedules   ps,
            hz_cust_accounts       ca
    WHERE   ps.class in ('INV', 'DM', 'CB', 'DEP' )
    AND     ps.payment_schedule_id <> -1
    AND     ps.gl_date BETWEEN l_temp_start AND p_end_date
    AND     ps.customer_id = ca.cust_account_id
    AND     ca.party_id = p_party_id;

Line: 1127


    SELECT  SUM(arpcurr.functional_amount(
      ps.amount_due_original,
      g_curr_rec.base_currency,
      nvl(ps.exchange_rate,1),
      g_curr_rec.base_precision,
      g_curr_rec.base_min_acc_unit) +
       GET_ADJ_FOR_TOT_REC(ps.payment_schedule_id,p_end_date))
    INTO    l_tot_rec
    FROM    ar_payment_schedules   ps
    WHERE   ps.class in ('INV', 'DM', 'CB', 'DEP' )
    AND     ps.payment_schedule_id <> -1
    AND     ps.gl_date BETWEEN l_temp_start AND p_end_date
    AND     ps.customer_id = p_cust_account_id;

Line: 1141


    SELECT  SUM(arpcurr.functional_amount(
      ps.amount_due_original,
      g_curr_rec.base_currency,
      nvl(ps.exchange_rate,1),
      g_curr_rec.base_precision,
      g_curr_rec.base_min_acc_unit) +
       GET_ADJ_FOR_TOT_REC(ps.payment_schedule_id,p_end_date))
    INTO    l_tot_rec
    FROM    ar_payment_schedules   ps
    WHERE   ps.class in ('INV', 'DM', 'CB', 'DEP' )
    AND     ps.payment_schedule_id <> -1
    AND     ps.gl_date BETWEEN l_temp_start AND p_end_date
    AND     ps.customer_site_use_id = p_customer_site_use_id;

Line: 1212


    SELECT sum(GET_APPS_TOTAL(ps.payment_schedule_id,p_end_date) -
           GET_ADJ_TOTAL(ps.payment_schedule_id,p_end_date) +
           nvl(ps.acctd_amount_due_remaining,0))
    INTO   l_rem_sales
    FROM   ar_payment_schedules         ps,
           hz_cust_accounts ca
    WHERE  ps.gl_date between p_start_date and p_end_date
    AND    ps.class in ('INV','DEP','DM','CB')
    AND    ps.gl_date_closed > p_end_date
    AND    ps.customer_id = ca.cust_account_id
    -- Begin fix bug #4949598-JYPARK-2/15/2006-add condition for performance
      AND    ps.status = 'OP'
    -- End fix bug #4949598-JYPARK-2/15/2006-add condition for performance
    AND    ca.party_id = p_party_id;

Line: 1229


    SELECT sum(GET_APPS_TOTAL(ps.payment_schedule_id,p_end_date) -
           GET_ADJ_TOTAL(ps.payment_schedule_id,p_end_date) +
           nvl(ps.acctd_amount_due_remaining,0))
    INTO   l_rem_sales
    FROM   ar_payment_schedules         ps
    WHERE  ps.gl_date between p_start_date and p_end_date
    AND    ps.class in ('INV','DEP','DM','CB')
    AND    ps.gl_date_closed > p_end_date
    -- Begin fix bug #4949598-JYPARK-2/15/2006-add condition for performance
      AND    ps.status = 'OP'
    -- End fix bug #4949598-JYPARK-2/15/2006-add condition for performance
    AND    ps.customer_id = p_cust_account_id;

Line: 1244


    SELECT sum(GET_APPS_TOTAL(ps.payment_schedule_id,p_end_date) -
           GET_ADJ_TOTAL(ps.payment_schedule_id,p_end_date) +
           nvl(ps.acctd_amount_due_remaining,0))
    INTO   l_rem_sales
    FROM   ar_payment_schedules         ps
    WHERE  ps.gl_date between p_start_date and p_end_date
    AND    ps.class in ('INV','DEP','DM','CB')
    AND    ps.gl_date_closed > p_end_date
    -- Begin fix bug #4949598-JYPARK-2/15/2006-add condition for performance
      AND    ps.status = 'OP'
    -- End fix bug #4949598-JYPARK-2/15/2006-add condition for performance
    AND    ps.customer_site_use_id = p_customer_site_use_id;

Line: 1311


    SELECT sum(nvl(ps.acctd_amount_due_remaining,0))
    INTO   l_curr_rec
    FROM   ar_payment_schedules         ps,
           hz_cust_accounts             ca
    WHERE  ps.gl_date between p_start_date and p_end_date
    AND    ps.class in ('INV','DEP','DM','CB')
    AND    ps.gl_date_closed > p_end_date
    AND    ps.status = 'OP'
    AND    ps.customer_id = ca.cust_account_id
    AND    ca.party_id = p_party_id
    AND    ps.due_date > p_end_date;

Line: 1325


    SELECT sum(nvl(ps.acctd_amount_due_remaining,0))
    INTO   l_curr_rec
    FROM   ar_payment_schedules         ps
    WHERE  ps.gl_date between p_start_date and p_end_date
    AND    ps.class in ('INV','DEP','DM','CB')
    AND    ps.gl_date_closed > p_end_date
    AND    ps.status = 'OP'
    AND    ps.customer_id = p_cust_account_id
    AND    ps.due_date > p_end_date;

Line: 1337


    SELECT sum(nvl(ps.acctd_amount_due_remaining,0))
    INTO   l_curr_rec
    FROM   ar_payment_schedules         ps
    WHERE  ps.gl_date between p_start_date and p_end_date
    AND    ps.class in ('INV','DEP','DM','CB')
    AND    ps.gl_date_closed > p_end_date
    AND    ps.status = 'OP'
    AND    ps.customer_site_use_id = p_customer_site_use_id
    AND    ps.due_date > p_end_date;

Line: 1377


  SELECT   sum( nvl(ra.acctd_amount_applied_to,0)  +
                nvl(ra.acctd_earned_discount_taken,0) +
                nvl(ra.acctd_unearned_discount_taken,0))
  INTO     l_apps_tot
  FROM     ar_receivable_applications   ra
  WHERE    ra.applied_payment_schedule_id = p_payment_schedule_id
  AND      ra.status = 'APP'
  AND      nvl(ra.confirmed_flag,'Y') = 'Y'
  AND      ra.gl_date   <= p_to_date;--Added for Bug 8201317 14-Jun-2010 barathsr

Line: 1416


  SELECT  sum( nvl(a.acctd_amount,0))
  INTO    l_adj_tot
  FROM    ar_adjustments   a
  WHERE   a.payment_schedule_id = p_payment_schedule_id
  AND     a.status       = 'A'
  AND     a.gl_date       <= p_to_date;--Added for Bug 8201317 14-Jun-2010 barathsr

Line: 1444


  SELECT  sum( nvl(a.acctd_amount,0))
  INTO    l_adj_for_tot_rec
  FROM    ar_adjustments   a
  WHERE   a.payment_schedule_id = p_payment_schedule_id
  AND     a.status = 'A'
  AND     a.gl_date <= p_to_date;

Line: 1477


    SELECT SUM(DECODE(prof_amt.overall_credit_limit, NULL, NULL, gl_currency_api.convert_amount_sql(prof_amt.currency_code, g_curr_rec.base_currency,
    -- End fix bug #6014218-31-May-07 gnramasa -return null when credit limits value is null instead of -2
         --     sysdate, cm_opt.default_exchange_rate_type, prof_amt.overall_credit_limit)))
              sysdate, l_conversion_type, prof_amt.overall_credit_limit)))
      INTO l_credit_limit
      FROM hz_customer_profiles prof, hz_cust_profile_amts prof_amt
        --   ar_cmgt_setup_options cm_opt
     WHERE prof.party_id = p_party_id
       AND prof.site_use_id IS NULL
       AND prof.status = 'A'
       AND prof.cust_account_profile_id = prof_amt.cust_account_profile_id
       AND prof_amt.cust_account_id = prof.cust_account_id
       --Begin-fix bug#4610424-JYPARK-09/16/2005-exclude credir limit for account
       AND prof.cust_account_id = -1
       --End-fix bug#4610424-JYPARK-09/16/2005-exclude credir limit for account
       AND prof_amt.site_use_id IS NULL;

Line: 1497


    SELECT SUM(DECODE(prof_amt.overall_credit_limit, NULL, NULL, gl_currency_api.convert_amount_sql(prof_amt.currency_code, g_curr_rec.base_currency,
    -- End fix bug #6014218-31-May-07 gnramasa -return null when credit limits value is null instead of -2
          --    sysdate, cm_opt.default_exchange_rate_type, prof_amt.overall_credit_limit)))
                sysdate, l_conversion_type, prof_amt.overall_credit_limit)))
      INTO l_credit_limit
      FROM hz_customer_profiles prof, hz_cust_profile_amts prof_amt
        --   ar_cmgt_setup_options cm_opt
     WHERE prof.cust_account_id = p_cust_account_id
       AND prof.site_use_id IS NULL
       AND prof.status = 'A'
       AND prof.cust_account_profile_id = prof_amt.cust_account_profile_id
       AND prof_amt.cust_account_id = p_cust_account_id
       AND prof_amt.site_use_id IS NULL;

Line: 1513


    SELECT SUM(DECODE(prof_amt.overall_credit_limit, NULL, NULL, gl_currency_api.convert_amount_sql(prof_amt.currency_code, g_curr_rec.base_currency,
    -- End fix bug #6014218-31-May-07 gnramasa -return null when credit limits value is null instead of -2
           --   sysdate, cm_opt.default_exchange_rate_type, prof_amt.overall_credit_limit)))
	    sysdate, l_conversion_type, prof_amt.overall_credit_limit)))
      INTO l_credit_limit
      FROM hz_customer_profiles prof, hz_cust_profile_amts prof_amt
      --     ar_cmgt_setup_options cm_opt
     WHERE prof.site_use_id = p_customer_site_use_id
       AND prof.status = 'A'
       AND prof.cust_account_profile_id = prof_amt.cust_account_profile_id
       AND prof_amt.site_use_id = p_customer_site_use_id;

Line: 1552


    SELECT MAX(gl_currency_api.convert_amount_sql(trx_summ.currency, g_curr_rec.base_currency,
           --   sysdate, cm_opt.default_exchange_rate_type, trx_summ.op_bal_high_watermark))
             sysdate, l_conversion_type , trx_summ.op_bal_high_watermark))
     INTO l_high_credit_ytd
     FROM ar_trx_summary trx_summ,ar_system_parameters asp,--Added for Bug 9404646 09-Mar-2010 barathsr
      --    ar_cmgt_setup_options cm_opt,
          hz_cust_accounts ca
    WHERE trx_summ.org_id=asp.org_id--Added for Bug 9404646 09-Mar-2010 barathsr
      AND trx_summ.cust_account_id = ca.cust_account_id
      AND ca.party_id = p_party_id;

Line: 1563


    SELECT MAX(gl_currency_api.convert_amount_sql(trx_summ.currency, g_curr_rec.base_currency,
         --     sysdate, cm_opt.default_exchange_rate_type, trx_summ.op_bal_high_watermark))
                sysdate, l_conversion_type , trx_summ.op_bal_high_watermark))
     INTO l_high_credit_ytd
     FROM ar_trx_summary trx_summ,ar_system_parameters asp--Added for Bug 9404646 09-Mar-2010 barathsr
       --   ar_cmgt_setup_options cm_opt
    WHERE trx_summ.org_id=asp.org_id--Added for Bug 9404646 09-Mar-2010 barathsr
      AND trx_summ.cust_account_id = p_cust_account_id;

Line: 1572


    SELECT MAX(gl_currency_api.convert_amount_sql(trx_summ.currency, g_curr_rec.base_currency,
          --    sysdate, cm_opt.default_exchange_rate_type, trx_summ.op_bal_high_watermark))
               sysdate, l_conversion_type , trx_summ.op_bal_high_watermark))
     INTO l_high_credit_ytd
     FROM ar_trx_summary trx_summ,ar_system_parameters asp--Added for Bug 9404646 09-Mar-2010 barathsr
     --     ar_cmgt_setup_options cm_opt
    WHERE trx_summ.org_id=asp.org_id--Added for Bug 9404646 09-Mar-2010 barathsr
      AND trx_summ.site_use_id = p_customer_site_use_id;

Line: 1603


   select sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_invoices_value),0) +
   nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
   nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
   nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0))
   INTO l_amount_due_org
   from gl_sets_of_books a,
   ar_system_parameters b,
   ar_trx_summary c,
   hz_cust_accounts d
   where a.set_of_books_id = b.set_of_books_id
   and b.org_id = c.org_id
   AND c.cust_account_id = d.cust_account_id
   AND d.party_id = p_party_id
   and as_of_date BETWEEN p_start_date AND p_end_date;

Line: 1622


   select sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_invoices_value),0) +
   nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
   nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
   nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0))
   INTO l_amount_due_org
   from gl_sets_of_books a,
   ar_system_parameters b,
   ar_trx_summary c
   where a.set_of_books_id = b.set_of_books_id
   and b.org_id = c.org_id
   and c.cust_account_id = p_cust_account_id
   and as_of_date BETWEEN p_start_date AND p_end_date;

Line: 1639


   select sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_invoices_value),0) +
   nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
   nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
   nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0))
   INTO l_amount_due_org
   from gl_sets_of_books a,
   ar_system_parameters b,
   ar_trx_summary c
   where a.set_of_books_id = b.set_of_books_id
   and b.org_id = c.org_id
   AND c.site_use_id = p_customer_site_use_id
   and as_of_date BETWEEN p_start_date AND p_end_date;

Line: 1676


   select sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_adjustments_value),0))
   INTO l_adj_total
   from gl_sets_of_books a,
   ar_system_parameters b,
   ar_trx_summary c,
   hz_cust_accounts d
   where a.set_of_books_id = b.set_of_books_id
   and b.org_id = c.org_id
   AND c.cust_account_id = d.cust_account_id
   AND d.party_id = p_party_id
   and as_of_date BETWEEN p_start_date AND p_end_date;

Line: 1689


   select sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_adjustments_value),0))
   INTO l_adj_total
   from gl_sets_of_books a,
   ar_system_parameters b,
   ar_trx_summary c
   where a.set_of_books_id = b.set_of_books_id
   and b.org_id = c.org_id
   and c.cust_account_id = p_cust_account_id
   and as_of_date BETWEEN p_start_date AND p_end_date;

Line: 1700


   select sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_adjustments_value),0))
   INTO l_adj_total
   from gl_sets_of_books a,
   ar_system_parameters b,
   ar_trx_summary c
   where a.set_of_books_id = b.set_of_books_id
   and b.org_id = c.org_id
   AND c.site_use_id = p_customer_site_use_id
   and as_of_date BETWEEN p_start_date AND p_end_date;

Line: 1730


   select sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_invoices_value),0) +
   nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
   nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
   nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0) +
   nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_adjustments_value),0)+
   nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_cash_receipts_value),0)+
   nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_credit_memos_value),0))
   INTO l_amount_due_rem
   from gl_sets_of_books a,
   ar_system_parameters b,
   ar_trx_summary c,
   hz_cust_accounts d
   where a.set_of_books_id = b.set_of_books_id
   and b.org_id = c.org_id
   AND c.cust_account_id = d.cust_account_id
   AND d.party_id = p_party_id
   and as_of_date BETWEEN p_start_date AND p_end_date;

Line: 1755


   select sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_invoices_value),0) +
   nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
   nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
   nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0)+
   nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_adjustments_value),0)+
   nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_cash_receipts_value),0)+
   nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_credit_memos_value),0))
   INTO l_amount_due_rem
   from gl_sets_of_books a,
   ar_system_parameters b,
   ar_trx_summary c
   where a.set_of_books_id = b.set_of_books_id
   and b.org_id = c.org_id
   and c.cust_account_id = p_cust_account_id
   and as_of_date BETWEEN p_start_date AND p_end_date;

Line: 1778


   select sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_invoices_value),0) +
   nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
   nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
   nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0)+
   nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_adjustments_value),0)+
   nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_cash_receipts_value),0)+
   nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_credit_memos_value),0))
   INTO l_amount_due_rem
   from gl_sets_of_books a,
   ar_system_parameters b,
   ar_trx_summary c
   where a.set_of_books_id = b.set_of_books_id
   and b.org_id = c.org_id
   AND c.site_use_id = p_customer_site_use_id
   and as_of_date BETWEEN p_start_date AND p_end_date;

Line: 1821


   select (sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_invoices_value),0)+
   nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_deposits_value),0)+
   nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_debit_memos_value),0)+
   nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_chargeback_value),0) +
   nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_bills_receivables_value),0)+
   nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_credit_memos_value),0)))
   INTO l_tot_rec
   from ar_trx_bal_summary c, ar_system_parameters b, gl_sets_of_books a, hz_cust_accounts d
   where b.set_of_books_id = a.set_of_books_id
   AND c.cust_account_id = d.cust_account_id
   AND d.party_id = p_party_id
   and c.org_id = b.org_id;

Line: 1840


   SELECT SUM(NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
    fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_invoices_value),0) +
    NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
    fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
    NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
    fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
    NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
    fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0) +
    nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
    fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_bills_receivables_value),0)+
    NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
    fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_adjustments_value),0)+
    NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
    fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_credit_memos_value),0)+
    NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
    fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_cash_receipts_value),0)-
    NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
    fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.nsf_stop_payment_amount),0))
    INTO l_tot_sales_in_year
     FROM gl_sets_of_books a, ar_trx_summary c, ar_system_parameters b, hz_cust_accounts d
    WHERE c.org_id = b.org_id
    AND c.cust_account_id  = d.cust_account_id
    AND d.party_id = p_party_id
    AND b.set_of_books_id = a.set_of_books_id
    and as_of_date between TRUNC(add_months(sysdate, -12)) AND TRUNC(sysdate);

Line: 1867


   select (sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_invoices_value),0)+
   nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_deposits_value),0)+
   nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_debit_memos_value),0)+
   nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_chargeback_value),0) +
   nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_bills_receivables_value),0)+
   nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_credit_memos_value),0)))
   INTO l_tot_rec
   from ar_trx_bal_summary c, ar_system_parameters b, gl_sets_of_books a
   where b.set_of_books_id = a.set_of_books_id
   AND c.cust_account_id = p_cust_account_id
   and c.org_id = b.org_id;

Line: 1885


   SELECT SUM(NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
    fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_invoices_value),0) +
    NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
    fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
    NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
    fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
    NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
    fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0) +
    nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
    fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_bills_receivables_value),0)+
    NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
    fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_adjustments_value),0)+
    NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
    fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_credit_memos_value),0)+
    NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
    fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_cash_receipts_value),0)-
    NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
    fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.nsf_stop_payment_amount),0))
    INTO l_tot_sales_in_year
     FROM gl_sets_of_books a, ar_trx_summary c, ar_system_parameters b
    WHERE c.org_id = b.org_id
    AND c.cust_account_id = p_cust_account_id
    AND b.set_of_books_id = a.set_of_books_id
    and as_of_date between TRUNC(add_months(sysdate, -12)) AND TRUNC(sysdate);

Line: 1910


    select (sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_invoices_value),0)+
   nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_deposits_value),0)+
   nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_debit_memos_value),0)+
   nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_chargeback_value),0) +
   nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_bills_receivables_value),0)+
   nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_credit_memos_value),0)))
   INTO l_tot_rec
   from ar_trx_bal_summary c, ar_system_parameters b, gl_sets_of_books a
   where b.set_of_books_id = a.set_of_books_id
   AND c.site_use_id = p_customer_site_use_id
   and c.org_id = b.org_id;

Line: 1928


   SELECT SUM(NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
    fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_invoices_value),0) +
    NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
    fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
    NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
    fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
    NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
    fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0) +
    nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
    fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_bills_receivables_value),0)+
    NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
    fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_adjustments_value),0)+
    NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
    fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_credit_memos_value),0)+
    NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
    fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_cash_receipts_value),0)-
    NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
    fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.nsf_stop_payment_amount),0))
    INTO l_tot_sales_in_year
     FROM gl_sets_of_books a, ar_trx_summary c, ar_system_parameters b
    WHERE c.org_id = b.org_id
    AND c.site_use_id = p_customer_site_use_id
    AND b.set_of_books_id = a.set_of_books_id
    and as_of_date between TRUNC(add_months(sysdate, -12)) AND TRUNC(sysdate);

Line: 1977


select nvl(amount_due_org_ocm(p_party_id,p_cust_account_id,p_customer_site_use_id,p_start_date,p_end_date),0)+
       nvl(get_adj_total_ocm(p_party_id,p_cust_account_id,p_customer_site_use_id,p_start_date , p_end_date),0)
  into l_tot_sales
  from dual;

Line: 2000


select (sysdate-gl_date)* amount_due_original amount, to_char(gl_date,'MM') month,payment_schedule_id
from ar_payment_schedules_all a, hz_cust_accounts b
where a.customer_id = b.cust_account_id
AND b.party_id = p_party_id
AND a.CLASS IN ('INV','DEP','DM','CB')
and a.gl_date between trunc(sysdate-365) AND TRUNC(sysdate);

Line: 2008


select (sysdate-gl_date)* amount_due_original amount, to_char(gl_date,'MM') month,payment_schedule_id
from ar_payment_schedules_all
where customer_id = p_cust_account_id
AND CLASS IN ('INV','DEP','DM','CB')
and gl_date between trunc(sysdate-365) AND TRUNC(sysdate);

Line: 2015


select (sysdate-gl_date)* amount_due_original amount, to_char(gl_date,'MM') month,payment_schedule_id
from ar_payment_schedules_all
where customer_site_use_id = p_customer_site_use_id
AND CLASS IN ('INV','DEP','DM','CB')
and gl_date between trunc(sysdate-365) AND TRUNC(sysdate);

Line: 2025


select sum(NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
    fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_invoices_value),0) +
    NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
    fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
    NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
    fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
    NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
    fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0)+
    NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
    fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_adjustments_value),0)) amount ,to_char(c.as_of_date,'MM') month
FROM gl_sets_of_books a, ar_trx_summary c, ar_system_parameters b , hz_cust_accounts d
    WHERE c.cust_account_id = d.cust_account_id
    AND d.party_id = p_party_id
    and c.org_id = b.org_id
    AND b.set_of_books_id = a.set_of_books_id
    AND c.as_of_date between TRUNC(sysdate-365) AND TRUNC(sysdate)
    group by to_char(c.as_of_date,'MM');

Line: 2044


select sum(NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
    fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_invoices_value),0) +
    NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
    fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
    NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
    fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
    NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
    fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0)+
    NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
    fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_adjustments_value),0)) amount ,to_char(c.as_of_date,'MM') month
FROM gl_sets_of_books a, ar_trx_summary c, ar_system_parameters b
    WHERE c.cust_account_id = p_cust_account_id
    and c.org_id = b.org_id
    AND b.set_of_books_id = a.set_of_books_id
    AND c.as_of_date between TRUNC(sysdate-365) AND TRUNC(sysdate)
    group by to_char(c.as_of_date,'MM');

Line: 2062


select sum(NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
    fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_invoices_value),0) +
    NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
    fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
    NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
    fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
    NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
    fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0)+
    NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
    fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_adjustments_value),0)) amount ,to_char(c.as_of_date,'MM') month
FROM gl_sets_of_books a, ar_trx_summary c, ar_system_parameters b
    WHERE c.site_use_id = p_customer_site_use_id
    and c.org_id = b.org_id
    AND b.set_of_books_id = a.set_of_books_id
    AND c.as_of_date between TRUNC(sysdate-365) AND TRUNC(sysdate)
    group by to_char(c.as_of_date,'MM');

Line: 2217


   select (sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_invoices_value),0)+
   nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_deposits_value),0)+
   nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_debit_memos_value),0)+
   nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_chargeback_value),0) +
   nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_bills_receivables_value),0)+
   nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_credit_memos_value),0)))
   INTO l_op_bal
   from ar_trx_bal_summary c, ar_system_parameters b, gl_sets_of_books a,hz_cust_accounts d
   where c.cust_account_id = d.cust_account_id
   AND d.party_id = p_party_id
   AND b.set_of_books_id = a.set_of_books_id
   and c.org_id = b.org_id;

Line: 2236


    select (sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_invoices_value),0)+
   nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_deposits_value),0)+
   nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_debit_memos_value),0)+
   nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_chargeback_value),0) +
   nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_bills_receivables_value),0)+
   nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_credit_memos_value),0)))
   INTO l_op_bal
   from ar_trx_bal_summary c, ar_system_parameters b, gl_sets_of_books a
   where c.cust_account_id= p_cust_account_id
   AND b.set_of_books_id = a.set_of_books_id
   and c.org_id = b.org_id;

Line: 2254


   select (sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_invoices_value),0)+
   nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_deposits_value),0)+
   nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_debit_memos_value),0)+
   nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_chargeback_value),0) +
   nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_bills_receivables_value),0)+
   nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.op_credit_memos_value),0)))
   INTO l_op_bal
   from ar_trx_bal_summary c, ar_system_parameters b, gl_sets_of_books a
   where c.site_use_id = p_customer_site_use_id
   AND b.set_of_books_id = a.set_of_books_id
   and c.org_id = b.org_id;

Line: 2278


    SELECT SUM(NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
    fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_invoices_value),0) +
    NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
    fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
    NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
    fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
    NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
    fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0) +
    nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
    fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_bills_receivables_value),0)+
    NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
    fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_adjustments_value),0))
    INTO l_tot_bal
    FROM gl_sets_of_books a, ar_trx_summary c, ar_system_parameters b, hz_cust_accounts d
    WHERE c.cust_account_id = d.cust_account_id
    AND d.party_id = p_party_id
    and c.org_id = b.org_id
    AND b.set_of_books_id = a.set_of_books_id
    and as_of_date between (trunc(sysdate) - b.cer_dso_days) AND TRUNC(sysdate);

Line: 2298


    SELECT SUM(NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
    fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_invoices_value),0) +
    NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
    fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
    NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
    fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
    NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
    fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0) +
    nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
    fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_bills_receivables_value),0)+
    NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
    fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_adjustments_value),0))
    INTO l_tot_bal
    FROM gl_sets_of_books a, ar_trx_summary c, ar_system_parameters b
    WHERE c.cust_account_id = p_cust_account_id
    and c.org_id = b.org_id
    AND b.set_of_books_id = a.set_of_books_id
    and as_of_date between (trunc(sysdate) - b.cer_dso_days) AND TRUNC(sysdate);

Line: 2317


    SELECT SUM(NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
    fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_invoices_value),0) +
    NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
    fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_deposits_value),0) +
    NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
    fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_debit_memos_value),0) +
    NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
    fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_chargeback_value),0) +
    nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
    fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_bills_receivables_value),0)+
    NVL(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
    fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.total_adjustments_value),0))
    INTO l_tot_bal
    FROM gl_sets_of_books a, ar_trx_summary c, ar_system_parameters b
    WHERE c.site_use_id = p_customer_site_use_id
    and c.org_id = b.org_id
    AND b.set_of_books_id = a.set_of_books_id
    and as_of_date between (trunc(sysdate) - b.cer_dso_days) AND TRUNC(sysdate);

Line: 2340


SELECT cer_dso_days INTO l_dso_days FROM ar_system_parameters;

Line: 2445


   select sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.nsf_stop_payment_count),0))
   INTO l_nsf_count
   from gl_sets_of_books a,
   ar_system_parameters b,
   ar_trx_summary c,
   hz_cust_accounts d
   where a.set_of_books_id = b.set_of_books_id
   and b.org_id = c.org_id
   AND c.cust_account_id = d.cust_account_id
   AND d.party_id = p_party_id
   and as_of_date BETWEEN TRUNC(add_months(sysdate, -12)) AND TRUNC(sysdate);

Line: 2458


   select sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.nsf_stop_payment_count),0))
   INTO l_nsf_count
   from gl_sets_of_books a,
   ar_system_parameters b,
   ar_trx_summary c
   where a.set_of_books_id = b.set_of_books_id
   and b.org_id = c.org_id
   and c.cust_account_id = p_cust_account_id
   and as_of_date BETWEEN TRUNC(add_months(sysdate, -12)) AND TRUNC(sysdate);

Line: 2469


   select sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.nsf_stop_payment_count),0))
   INTO l_nsf_count
   from gl_sets_of_books a,
   ar_system_parameters b,
   ar_trx_summary c
   where a.set_of_books_id = b.set_of_books_id
   and b.org_id = c.org_id
   AND c.site_use_id = p_customer_site_use_id
   and as_of_date BETWEEN  TRUNC(add_months(sysdate, -12)) AND TRUNC(sysdate);

Line: 2499


   select sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.nsf_stop_payment_amount),0))
   INTO l_nsf_amount
   from gl_sets_of_books a,
   ar_system_parameters b,
   ar_trx_summary c,
   hz_cust_accounts d
   where a.set_of_books_id = b.set_of_books_id
   and b.org_id = c.org_id
   AND c.cust_account_id = d.cust_account_id
   AND d.party_id = p_party_id
   and as_of_date BETWEEN TRUNC(add_months(sysdate, -12)) AND TRUNC(sysdate);

Line: 2512


   select sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.nsf_stop_payment_amount),0))
   INTO l_nsf_amount
   from gl_sets_of_books a,
   ar_system_parameters b,
   ar_trx_summary c
   where a.set_of_books_id = b.set_of_books_id
   and b.org_id = c.org_id
   and c.cust_account_id = p_cust_account_id
   and as_of_date BETWEEN TRUNC(add_months(sysdate, -12)) AND TRUNC(sysdate);

Line: 2523


   select sum(nvl(gl_currency_api.convert_amount(c.currency,a.currency_code,sysdate,
   fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'),c.nsf_stop_payment_amount),0))
   INTO l_nsf_amount
   from gl_sets_of_books a,
   ar_system_parameters b,
   ar_trx_summary c
   where a.set_of_books_id = b.set_of_books_id
   and b.org_id = c.org_id
   AND c.site_use_id = p_customer_site_use_id
   and as_of_date BETWEEN TRUNC(add_months(sysdate, -12)) AND TRUNC(sysdate);

Line: 2555


   SELECT ROUND(SUM(gl_currency_api.convert_amount(c.currency,a.currency_code, SYSDATE, fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'), (NVL(SUM_APP_AMT_DAYS_LATE,0)))) /
 DECODE(SUM(gl_currency_api.convert_amount(c.currency,a.currency_code, SYSDATE,fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'), (NVL(SUM_APP_AMT,0)))),0,1,
 SUM(gl_currency_api.convert_amount(c.currency,a.currency_code, SYSDATE,fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'), (NVL(SUM_APP_AMT,0))))),2)
   INTO l_wt_days_paid_late
   FROM gl_sets_of_books a,
  ar_system_parameters b  ,
  ar_trx_summary c        ,
  hz_cust_accounts d
  WHERE a.set_of_books_id = b.set_of_books_id
AND b.org_id              = c.org_id
AND c.cust_account_id     = d.cust_account_id
AND d.party_id            = p_party_id
AND as_of_date BETWEEN TRUNC(add_months(sysdate, -12)) AND TRUNC(sysdate);

Line: 2569


   SELECT ROUND(SUM(gl_currency_api.convert_amount(c.currency,a.currency_code, SYSDATE, fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'), (NVL(SUM_APP_AMT_DAYS_LATE,0)))) /
 DECODE(SUM(gl_currency_api.convert_amount(c.currency,a.currency_code, SYSDATE,fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'), (NVL(SUM_APP_AMT,0)))),0,1,
 SUM(gl_currency_api.convert_amount(c.currency,a.currency_code, SYSDATE,fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'), (NVL(SUM_APP_AMT,0))))),2)
  INTO l_wt_days_paid_late
   FROM gl_sets_of_books a,
  ar_system_parameters b  ,
  ar_trx_summary c
  -- , hz_cust_accounts d  commented for Bug14167214 by bibeura
  WHERE a.set_of_books_id = b.set_of_books_id
AND b.org_id              = c.org_id
AND c.cust_account_id = p_cust_account_id
AND as_of_date BETWEEN TRUNC(add_months(sysdate, -12)) AND TRUNC(sysdate);

Line: 2582


   SELECT ROUND(SUM(gl_currency_api.convert_amount(c.currency,a.currency_code, SYSDATE, fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'), (NVL(SUM_APP_AMT_DAYS_LATE,0)))) /
 DECODE(SUM(gl_currency_api.convert_amount(c.currency,a.currency_code, SYSDATE,fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'), (NVL(SUM_APP_AMT,0)))),0,1,
 SUM(gl_currency_api.convert_amount(c.currency,a.currency_code, SYSDATE,fnd_profile.value('IEX_EXCHANGE_RATE_TYPE'), (NVL(SUM_APP_AMT,0))))),2)
INTO l_wt_days_paid_late
   FROM gl_sets_of_books a,
  ar_system_parameters b  ,
  ar_trx_summary c
  -- ,  hz_cust_accounts d commented for Bug14167214 by bibeura
  WHERE a.set_of_books_id = b.set_of_books_id
AND b.org_id              = c.org_id
AND c.site_use_id = p_customer_site_use_id
AND as_of_date BETWEEN TRUNC(add_months(sysdate, -12)) AND TRUNC(sysdate);

Line: 2611


  /*SELECT sob.currency_code,
         c.precision,
         c.minimum_accountable_unit
    INTO   g_curr_rec.base_currency,
           g_curr_rec.base_precision,
           g_curr_rec.base_min_acc_unit
    FROM   ar_system_parameters   sysp,
           gl_sets_of_books     sob,
           fnd_currencies     c
   WHERE  sob.set_of_books_id = sysp.set_of_books_id
     AND    sob.currency_code   = c.currency_code;

Line: 2624


  SELECT  TRUNC(add_months(sysdate, - 12)) pastYearFrom ,
          TRUNC(sysdate) pastYearTo
    INTO  g_curr_rec.past_year_from,
          g_curr_rec.past_year_to
    FROM  dual;*/