APPS.IEX_COLL_IND_PUB SQL Statements

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

Line: 44

    SELECT SUM(DECODE(prof_amt.overall_credit_limit, NULL, NULL,
                   gl_currency_api.convert_amount_sql(prof_amt.currency_code,g_base_currency_code,
              sysdate, cm_opt.default_exchange_rate_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
       AND prof.cust_account_id = -1
       AND prof_amt.site_use_id IS NULL;

Line: 59

    SELECT SUM(DECODE(prof_amt.overall_credit_limit, NULL, NULL,
              gl_currency_api.convert_amount_sql(prof_amt.currency_code, g_base_currency_code,
              sysdate, cm_opt.default_exchange_rate_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: 72

    SELECT SUM(DECODE(prof_amt.overall_credit_limit, NULL, NULL,
              gl_currency_api.convert_amount_sql(prof_amt.currency_code, g_base_currency_code,
              sysdate, cm_opt.default_exchange_rate_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: 116

    SELECT sum
             (
               (
                 IEX_COLL_IND.GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
                 IEX_COLL_IND.GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
                 nvl(ps.acctd_amount_due_remaining, 0)
               ) *
               (ps.due_date - ps.trx_date)
             )  /
             sum (
               IEX_COLL_IND.GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
               IEX_COLL_IND.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')
      AND    ps.payment_schedule_id <> -1
      AND    ps.customer_id = ca.cust_account_id
      AND    ca.party_id = p_party_id;

Line: 138

    SELECT sum
             (
               (
                 IEX_COLL_IND.GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
                 IEX_COLL_IND.GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
                 nvl(ps.acctd_amount_due_remaining, 0)
               ) *
               (ps.due_date - ps.trx_date)
             )  /
             sum (
               IEX_COLL_IND.GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
               IEX_COLL_IND.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')
      AND    ps.payment_schedule_id <> -1
      AND    ps.customer_id = p_cust_account_id;

Line: 159

    SELECT sum
             (
               (
                 IEX_COLL_IND.GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
                 IEX_COLL_IND.GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
                 nvl(ps.acctd_amount_due_remaining, 0)
               ) *
               (ps.due_date - ps.trx_date)
             )  /
             sum (
               IEX_COLL_IND.GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
               IEX_COLL_IND.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')
      AND    ps.payment_schedule_id <> -1
      AND    ps.customer_site_use_id = p_customer_site_use_id;

Line: 210

    SELECT sum
             (
               (
                 IEX_COLL_IND.GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
                 IEX_COLL_IND.GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
                 nvl(ps.acctd_amount_due_remaining, 0)
               ) *
               (TRUNC(sysdate) - ps.due_date)
             )  /
             sum (
               IEX_COLL_IND.GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
               IEX_COLL_IND.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')
      AND    ps.status = 'OP'
      AND    ps.payment_schedule_id <> -1
      AND    ps.customer_id = ca.cust_account_id
      AND    ca.party_id = p_party_id;

Line: 233

    SELECT sum
             (
               (
                 IEX_COLL_IND.GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
                 IEX_COLL_IND.GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
                 nvl(ps.acctd_amount_due_remaining, 0)
               ) *
               (TRUNC(sysdate) - ps.due_date)
             )  /
             sum (
               IEX_COLL_IND.GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
               IEX_COLL_IND.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')
      AND    ps.status = 'OP'
      AND    ps.payment_schedule_id <> -1
      AND    ps.customer_id = p_cust_account_id;

Line: 255

    SELECT sum
             (
               (
                 IEX_COLL_IND.GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
                 IEX_COLL_IND.GET_ADJ_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) +
                 nvl(ps.acctd_amount_due_remaining, 0)
               ) *
               (TRUNC(sysdate) - ps.due_date)
             )  /
             sum (
               IEX_COLL_IND.GET_APPS_TOTAL(ps.payment_schedule_id, TRUNC(sysdate)) -
               IEX_COLL_IND.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')
      AND    ps.status = 'OP'
      AND    ps.payment_schedule_id <> -1
      AND    ps.customer_site_use_id = p_customer_site_use_id;

Line: 372

    SELECT  SUM(arpcurr.functional_amount(
                ps.amount_due_original,
                g_base_currency_code,
                nvl(ps.exchange_rate,1),
                g_base_precision,
                g_base_min_acc_unit) +
                IEX_COLL_IND.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: 388

    SELECT  SUM(arpcurr.functional_amount(
      ps.amount_due_original,
      g_base_currency_code,
      nvl(ps.exchange_rate,1),
      g_base_precision,
      g_base_min_acc_unit) +
       IEX_COLL_IND.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: 402

    SELECT  SUM(arpcurr.functional_amount(
      ps.amount_due_original,
      g_base_currency_code,
      nvl(ps.exchange_rate,1),
      g_base_precision,
      g_base_min_acc_unit) +
       IEX_COLL_IND.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: 431

    select gll.currency_code, c.precision, c.minimum_accountable_unit
      INTO  g_base_currency_code,g_base_precision,g_base_min_acc_unit
      from ar_system_parameters    sp,
           gl_ledgers_public_v     gll,
           fnd_currencies     c
      where
            gll.ledger_id = sp.set_of_books_id
        and gll.currency_code   = c.currency_code;

Line: 447

       iex_debug_pub.logmessage (' IEX_COLL_IND_PUB.main selection - Exception = ' ||SQLERRM);