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_BUS_EVENT_SUB_PVT SQL Statements

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

Line: 8


select request_id
from AR_CONC_PROCESS_REQUESTS
where CONCURRENT_PROGRAM_NAME in ('ARSUMREF','IEX_POPULATE_UWQ_SUM');

Line: 84


	x_return_status := UPDATE_SUMMARY(l_customer_trx_id,l_org_id ,'INV');

Line: 165


	  x_return_status := UPDATE_SUMMARY(l_payment_schedule_id,l_org_id ,'REC');

Line: 237


	x_return_status := UPDATE_SUMMARY(l_receivable_application_id,l_org_id ,'CM');

Line: 309


	x_return_status := UPDATE_SUMMARY(l_adjustment_id,l_org_id ,'ADJ');

Line: 379


	 x_return_status := UPDATE_SUMMARY(l_request_id,l_org_id ,'AUTOADJ');

Line: 454


	x_return_status := UPDATE_SUMMARY(l_request_id,l_org_id ,'AUTOREC');

Line: 523


	x_return_status := UPDATE_SUMMARY(l_request_id,l_org_id ,'AUTOINV');

Line: 554


FUNCTION UPDATE_SUMMARY(
id_val    IN NUMBER
,l_org_id IN NUMBER
,trx_type IN VARCHAR2
)
RETURN VARCHAR2 IS
l_customer_trx_id NUMBER;

Line: 566


  CURSOR select_ps (cust_trx_id IN NUMBER) IS
  SELECT * from ar_payment_schedules
  WHERE customer_trx_id = cust_trx_id;

Line: 570


  CURSOR select_ps_rec (l_payment_schedule_id IN NUMBER) IS
  SELECT * from ar_payment_schedules
  WHERE payment_schedule_id = l_payment_schedule_id;

Line: 574


  CURSOR select_ps_cm (p_ra_id  IN NUMBER ) IS
  SELECT trx_ps.*
  FROM  ar_payment_schedules trx_ps,
      ar_receivable_applications ra,
      ar_payment_schedules rcpt_ps
  WHERE ra.receivable_application_id = p_ra_id
  AND   ra.status in ('APP')
  AND   ra.payment_schedule_id = rcpt_ps.payment_schedule_id
  AND   ra.applied_payment_schedule_id = trx_ps.payment_schedule_id;

Line: 584


  CURSOR select_ps_adj (p_adj_id IN NUMBER) IS
  SELECT ps.*
  FROM ar_adjustments adj,
       ar_payment_schedules ps
  WHERE adj.payment_schedule_id = ps.payment_schedule_id
   and  adj.adjustment_id = p_adj_id ;

Line: 591


  CURSOR select_ps_auto_adj (p_req_id  IN NUMBER) IS
   SELECT ps.*
   FROM ar_adjustments adj,
        ar_payment_schedules ps
   WHERE adj.request_id = p_req_id
     and adj.payment_schedule_id = ps.payment_schedule_id;

Line: 599


  CURSOR select_ps_auto_rec (p_request_id IN NUMBER) IS
    SELECT ps.*
    FROM   ar_receivable_applications ra,
        ar_payment_schedules ps
    WHERE ra.request_id = p_request_id
    AND ra.status IN('APP','UNAPP')
    AND ps.payment_schedule_id = ra.applied_payment_schedule_id;

Line: 607


   CURSOR select_ps_auto_inv (p_request_id IN NUMBER) IS
   SELECT ps.*
   FROM ra_customer_trx trx,
       ar_payment_schedules ps
   WHERE
   trx.customer_trx_id = ps.customer_trx_id
   AND trx.request_id = p_request_id;

Line: 615


  CURSOR select_pref IS SELECT PREFERENCE_VALUE FROM IEX_APP_PREFERENCES_VL
                        WHERE PREFERENCE_NAME = 'COLLECTIONS STRATEGY LEVEL';

Line: 618


  CURSOR update_iex_sum_billto(p_cust_account_id IN NUMBER,
                        p_site_use_id     IN NUMBER,
			p_org_id          IN NUMBER) IS
   SELECT
    max(gl.CURRENCY_CODE) currency,
    SUM(trx_summ.op_invoices_count) op_invoices_count,
    SUM(trx_summ.op_debit_memos_count) op_debit_memos_count,
    SUM(trx_summ.op_deposits_count) op_deposits_count,
    SUM(trx_summ.op_bills_receivables_count) op_bills_receivables_count,
    SUM(trx_summ.op_chargeback_count) op_chargeback_count,
    SUM(trx_summ.op_credit_memos_count) op_credit_memos_count,
    SUM(trx_summ.unresolved_cash_count) unresolved_cash_count,
    SUM(trx_summ.disputed_inv_count) disputed_inv_count,
    SUM(TRX_SUMM.PAST_DUE_INV_INST_COUNT) PAST_DUE_INV_INST_COUNT,
    SUM(decode(trx_summ.currency,   gl.CURRENCY_CODE,   trx_summ.best_current_receivables,
     gl_currency_api.convert_amount_sql(trx_summ.currency,   gl.CURRENCY_CODE,   sysdate,
     iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE',   ''),   trx_summ.best_current_receivables))) best_current_receivables,
    SUM(decode(trx_summ.currency,   gl.CURRENCY_CODE,   trx_summ.op_invoices_value,
     gl_currency_api.convert_amount_sql(trx_summ.currency,   gl.CURRENCY_CODE,   sysdate,
     iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE',   ''),   trx_summ.op_invoices_value))) op_invoices_value,
    (SELECT SUM(b.acctd_amount_due_remaining)
     FROM iex_delinquencies_all a,
       ar_payment_schedules_all b
     WHERE a.customer_site_use_id = trx_summ.site_use_id
     AND a.payment_schedule_id = b.payment_schedule_id
     AND b.status = 'OP'
     AND a.status IN('DELINQUENT',    'PREDELINQUENT')) past_due_inv_value,
    SUM(decode(trx_summ.currency,   gl.CURRENCY_CODE,   trx_summ.op_debit_memos_value,
     gl_currency_api.convert_amount_sql(trx_summ.currency,   gl.CURRENCY_CODE,   sysdate,
     iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE',   ''),   trx_summ.op_debit_memos_value))) op_debit_memos_value,
    SUM(decode(trx_summ.currency,   gl.CURRENCY_CODE,   trx_summ.op_deposits_value,
     gl_currency_api.convert_amount_sql(trx_summ.currency,   gl.CURRENCY_CODE,   sysdate,
     iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE',   ''),   trx_summ.op_deposits_value))) op_deposits_value,
    SUM(decode(trx_summ.currency,   gl.CURRENCY_CODE,   trx_summ.op_bills_receivables_value,
     gl_currency_api.convert_amount_sql(trx_summ.currency,   gl.CURRENCY_CODE,   sysdate,
     iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE',   ''),   trx_summ.op_bills_receivables_value))) op_bills_receivables_value,
    SUM(decode(trx_summ.currency,   gl.CURRENCY_CODE,   trx_summ.op_chargeback_value,
     gl_currency_api.convert_amount_sql(trx_summ.currency,   gl.CURRENCY_CODE,   sysdate,
     iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE',   ''),   trx_summ.op_chargeback_value))) op_chargeback_value,
    SUM(decode(trx_summ.currency,   gl.CURRENCY_CODE,   trx_summ.op_credit_memos_value,
     gl_currency_api.convert_amount_sql(trx_summ.currency,   gl.CURRENCY_CODE,   sysdate,
     iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE',   ''),   trx_summ.op_credit_memos_value))) op_credit_memos_value,
    SUM(decode(trx_summ.currency,   gl.CURRENCY_CODE,   trx_summ.unresolved_cash_value,
     gl_currency_api.convert_amount_sql(trx_summ.currency,   gl.CURRENCY_CODE,   sysdate,
     iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE',   ''),   trx_summ.unresolved_cash_value))) unresolved_cash_value,
    SUM(decode(trx_summ.currency,   gl.CURRENCY_CODE,   trx_summ.receipts_at_risk_value,
     gl_currency_api.convert_amount_sql(trx_summ.currency,   gl.CURRENCY_CODE,   sysdate,
     iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE',   ''),   trx_summ.receipts_at_risk_value))) receipts_at_risk_value,
    SUM(decode(trx_summ.currency,   gl.CURRENCY_CODE,   trx_summ.inv_amt_in_dispute,
     gl_currency_api.convert_amount_sql(trx_summ.currency,   gl.CURRENCY_CODE,   sysdate,
     iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE',   ''),   trx_summ.inv_amt_in_dispute))) inv_amt_in_dispute,
    SUM(decode(trx_summ.currency,   gl.CURRENCY_CODE,   trx_summ.pending_adj_value,
     gl_currency_api.convert_amount_sql(trx_summ.currency,   gl.CURRENCY_CODE,   sysdate,
     iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE',   ''),   trx_summ.pending_adj_value))) pending_adj_value,
    MAX(trx_summ.last_payment_date) last_payment_date,         --Added last payment columns for bug#5938261 by schekuri on 19-Mar-2007
    MAX(iex_uwq_view_pkg.get_last_payment_amount(0,   0,   trx_summ.site_use_id)) last_payment_amount,
    max(gl.CURRENCY_CODE) last_payment_amount_curr,
    MAX(iex_uwq_view_pkg.get_last_payment_number(0,   0,   trx_summ.site_use_id)) last_payment_number,
   trx_summ.site_use_id,
   trx_summ.org_id
   FROM AR_TRX_BAL_SUMMARY  trx_summ,
        GL_SETS_OF_BOOKS gl,
        AR_SYSTEM_PARAMETERS_all sys
    WHERE
    gl.SET_OF_BOOKS_ID             = sys.SET_OF_BOOKS_ID
    AND sys.org_id                 = trx_summ.org_id
    AND trx_summ.cust_account_id   = p_cust_account_id
    AND trx_summ.site_use_id       = p_site_use_id
    AND trx_summ.org_id            = p_org_id
    GROUP BY trx_summ.site_use_id, trx_summ.org_id;

Line: 689


   CURSOR update_iex_sum_acc(p_cust_account_id IN NUMBER,
			         p_org_id          IN NUMBER) IS
   SELECT
    max(gl.CURRENCY_CODE) currency,
    SUM(trx_summ.op_invoices_count) op_invoices_count,
    SUM(trx_summ.op_debit_memos_count) op_debit_memos_count,
    SUM(trx_summ.op_deposits_count) op_deposits_count,
    SUM(trx_summ.op_bills_receivables_count) op_bills_receivables_count,
    SUM(trx_summ.op_chargeback_count) op_chargeback_count,
    SUM(trx_summ.op_credit_memos_count) op_credit_memos_count,
    SUM(trx_summ.unresolved_cash_count) unresolved_cash_count,
    SUM(trx_summ.disputed_inv_count) disputed_inv_count,
    SUM(TRX_SUMM.PAST_DUE_INV_INST_COUNT) PAST_DUE_INV_INST_COUNT,
    SUM(decode(trx_summ.currency,   gl.CURRENCY_CODE,   trx_summ.best_current_receivables,
     gl_currency_api.convert_amount_sql(trx_summ.currency,   gl.CURRENCY_CODE,   sysdate,
     iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE',   ''),   trx_summ.best_current_receivables))) best_current_receivables,
    SUM(decode(trx_summ.currency,   gl.CURRENCY_CODE,   trx_summ.op_invoices_value,
     gl_currency_api.convert_amount_sql(trx_summ.currency,   gl.CURRENCY_CODE,   sysdate,
     iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE',   ''),   trx_summ.op_invoices_value))) op_invoices_value,
    (SELECT SUM(b.acctd_amount_due_remaining)
       FROM iex_delinquencies_all a,
         ar_payment_schedules_all b
       WHERE a.cust_account_id = trx_summ.cust_account_id
       AND a.payment_schedule_id = b.payment_schedule_id
       AND b.status = 'OP'
       AND a.status IN('DELINQUENT',    'PREDELINQUENT')) past_due_inv_value,
    SUM(decode(trx_summ.currency,   gl.CURRENCY_CODE,   trx_summ.op_debit_memos_value,
     gl_currency_api.convert_amount_sql(trx_summ.currency,   gl.CURRENCY_CODE,   sysdate,
     iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE',   ''),   trx_summ.op_debit_memos_value))) op_debit_memos_value,
    SUM(decode(trx_summ.currency,   gl.CURRENCY_CODE,   trx_summ.op_deposits_value,
     gl_currency_api.convert_amount_sql(trx_summ.currency,   gl.CURRENCY_CODE,   sysdate,
     iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE',   ''),   trx_summ.op_deposits_value))) op_deposits_value,
    SUM(decode(trx_summ.currency,   gl.CURRENCY_CODE,   trx_summ.op_bills_receivables_value,
     gl_currency_api.convert_amount_sql(trx_summ.currency,   gl.CURRENCY_CODE,   sysdate,
     iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE',   ''),   trx_summ.op_bills_receivables_value))) op_bills_receivables_value,
    SUM(decode(trx_summ.currency,   gl.CURRENCY_CODE,   trx_summ.op_chargeback_value,
     gl_currency_api.convert_amount_sql(trx_summ.currency,   gl.CURRENCY_CODE,   sysdate,
     iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE',   ''),   trx_summ.op_chargeback_value))) op_chargeback_value,
    SUM(decode(trx_summ.currency,   gl.CURRENCY_CODE,   trx_summ.op_credit_memos_value,
     gl_currency_api.convert_amount_sql(trx_summ.currency,   gl.CURRENCY_CODE,   sysdate,
     iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE',   ''),   trx_summ.op_credit_memos_value))) op_credit_memos_value,
    SUM(decode(trx_summ.currency,   gl.CURRENCY_CODE,   trx_summ.unresolved_cash_value,
     gl_currency_api.convert_amount_sql(trx_summ.currency,   gl.CURRENCY_CODE,   sysdate,
     iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE',   ''),   trx_summ.unresolved_cash_value))) unresolved_cash_value,
    SUM(decode(trx_summ.currency,   gl.CURRENCY_CODE,   trx_summ.receipts_at_risk_value,
     gl_currency_api.convert_amount_sql(trx_summ.currency,   gl.CURRENCY_CODE,   sysdate,
     iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE',   ''),   trx_summ.receipts_at_risk_value))) receipts_at_risk_value,
    SUM(decode(trx_summ.currency,   gl.CURRENCY_CODE,   trx_summ.inv_amt_in_dispute,
     gl_currency_api.convert_amount_sql(trx_summ.currency,   gl.CURRENCY_CODE,   sysdate,
     iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE',   ''),   trx_summ.inv_amt_in_dispute))) inv_amt_in_dispute,
    SUM(decode(trx_summ.currency,   gl.CURRENCY_CODE,   trx_summ.pending_adj_value,
     gl_currency_api.convert_amount_sql(trx_summ.currency,   gl.CURRENCY_CODE,   sysdate,
     iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE',   ''),   trx_summ.pending_adj_value))) pending_adj_value,
    MAX(trx_summ.last_payment_date) last_payment_date,                       --Added last payment columns for bug#5938261 by schekuri on 19-Mar-2007
    MAX(iex_uwq_view_pkg.get_last_payment_amount(0,   trx_summ.cust_account_id,   0)) last_payment_amount,
    max(gl.CURRENCY_CODE) last_payment_amount_curr,
    MAX(iex_uwq_view_pkg.get_last_payment_number(0,   trx_summ.cust_account_id,   0)) last_payment_number,
    TRX_SUMM.cust_account_id,
    TRX_SUMM.org_id
   FROM AR_TRX_BAL_SUMMARY  TRX_SUMM,
        GL_SETS_OF_BOOKS gl,
        AR_SYSTEM_PARAMETERS_all sys
    WHERE
    gl.SET_OF_BOOKS_ID = sys.SET_OF_BOOKS_ID
    AND sys.org_id = trx_summ.org_id
    AND TRX_SUMM.cust_account_id = p_cust_account_id
    AND   TRX_SUMM.org_id          = p_org_id
   GROUP BY TRX_SUMM.cust_account_id,TRX_SUMM.org_id;

Line: 759


   CURSOR update_iex_sum_cu(p_cust_account_id IN NUMBER,
			         p_org_id          IN NUMBER) IS
   SELECT
   max(gl.CURRENCY_CODE) currency,
    SUM(trx_summ.op_invoices_count) op_invoices_count,
    SUM(trx_summ.op_debit_memos_count) op_debit_memos_count,
    SUM(trx_summ.op_deposits_count) op_deposits_count,
    SUM(trx_summ.op_bills_receivables_count) op_bills_receivables_count,
    SUM(trx_summ.op_chargeback_count) op_chargeback_count,
    SUM(trx_summ.op_credit_memos_count) op_credit_memos_count,
    SUM(trx_summ.unresolved_cash_count) unresolved_cash_count,
    SUM(trx_summ.disputed_inv_count) disputed_inv_count,
    SUM(TRX_SUMM.PAST_DUE_INV_INST_COUNT) PAST_DUE_INV_INST_COUNT,
    SUM(decode(trx_summ.currency,   gl.CURRENCY_CODE,   trx_summ.best_current_receivables,
     gl_currency_api.convert_amount_sql(trx_summ.currency,   gl.CURRENCY_CODE,   sysdate,
     iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE',   ''),   trx_summ.best_current_receivables))) best_current_receivables,
    SUM(decode(trx_summ.currency,   gl.CURRENCY_CODE,   trx_summ.op_invoices_value,
     gl_currency_api.convert_amount_sql(trx_summ.currency,   gl.CURRENCY_CODE,   sysdate,
     iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE',   ''),   trx_summ.op_invoices_value))) op_invoices_value,
     (SELECT SUM(b.acctd_amount_due_remaining)
      FROM iex_delinquencies_all a,
           ar_payment_schedules_all b
      WHERE a.party_cust_id = party.party_id
      AND a.payment_schedule_id = b.payment_schedule_id
      AND b.status = 'OP'
      AND a.status IN('DELINQUENT',      'PREDELINQUENT')) past_due_inv_value,
    SUM(decode(trx_summ.currency,   gl.CURRENCY_CODE,   trx_summ.op_debit_memos_value,
     gl_currency_api.convert_amount_sql(trx_summ.currency,   gl.CURRENCY_CODE,   sysdate,
     iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE',   ''),   trx_summ.op_debit_memos_value))) op_debit_memos_value,
    SUM(decode(trx_summ.currency,   gl.CURRENCY_CODE,   trx_summ.op_deposits_value,
     gl_currency_api.convert_amount_sql(trx_summ.currency,   gl.CURRENCY_CODE,   sysdate,
     iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE',   ''),   trx_summ.op_deposits_value))) op_deposits_value,
    SUM(decode(trx_summ.currency,   gl.CURRENCY_CODE,   trx_summ.op_bills_receivables_value,
     gl_currency_api.convert_amount_sql(trx_summ.currency,   gl.CURRENCY_CODE,   sysdate,
     iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE',   ''),   trx_summ.op_bills_receivables_value))) op_bills_receivables_value,
    SUM(decode(trx_summ.currency,   gl.CURRENCY_CODE,   trx_summ.op_chargeback_value,
     gl_currency_api.convert_amount_sql(trx_summ.currency,   gl.CURRENCY_CODE,   sysdate,
     iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE',   ''),   trx_summ.op_chargeback_value))) op_chargeback_value,
    SUM(decode(trx_summ.currency,   gl.CURRENCY_CODE,   trx_summ.op_credit_memos_value,
     gl_currency_api.convert_amount_sql(trx_summ.currency,   gl.CURRENCY_CODE,   sysdate,
     iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE',   ''),   trx_summ.op_credit_memos_value))) op_credit_memos_value,
    SUM(decode(trx_summ.currency,   gl.CURRENCY_CODE,   trx_summ.unresolved_cash_value,
     gl_currency_api.convert_amount_sql(trx_summ.currency,   gl.CURRENCY_CODE,   sysdate,
     iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE',   ''),   trx_summ.unresolved_cash_value))) unresolved_cash_value,
    SUM(decode(trx_summ.currency,   gl.CURRENCY_CODE,   trx_summ.receipts_at_risk_value,
     gl_currency_api.convert_amount_sql(trx_summ.currency,   gl.CURRENCY_CODE,   sysdate,
     iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE',   ''),   trx_summ.receipts_at_risk_value))) receipts_at_risk_value,
    SUM(decode(trx_summ.currency,   gl.CURRENCY_CODE,   trx_summ.inv_amt_in_dispute,
     gl_currency_api.convert_amount_sql(trx_summ.currency,   gl.CURRENCY_CODE,   sysdate,
     iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE',   ''),   trx_summ.inv_amt_in_dispute))) inv_amt_in_dispute,
    SUM(decode(trx_summ.currency,   gl.CURRENCY_CODE,   trx_summ.pending_adj_value,
     gl_currency_api.convert_amount_sql(trx_summ.currency,   gl.CURRENCY_CODE,   sysdate,
     iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE',   ''),   trx_summ.pending_adj_value))) pending_adj_value,
    MAX(trx_summ.last_payment_date) last_payment_date,          --Added last payment columns for bug#5938261 by schekuri on 19-Mar-2007
    MAX(iex_uwq_view_pkg.get_last_payment_amount(party.party_id,     0,     0)) last_payment_amount,
    max(gl.CURRENCY_CODE) last_payment_amount_curr,
    MAX(iex_uwq_view_pkg.get_last_payment_number(party.party_id,     0,     0)) last_payment_number,
    party.party_id party_id,
    trx_summ.org_id org_id
   FROM AR_TRX_BAL_SUMMARY   trx_summ, hz_cust_accounts acc,hz_parties party,
        GL_SETS_OF_BOOKS gl,
        AR_SYSTEM_PARAMETERS_all sys
    WHERE
    gl.SET_OF_BOOKS_ID = sys.SET_OF_BOOKS_ID
    AND sys.org_id = trx_summ.org_id
    AND trx_summ.cust_account_id       = acc.cust_account_id
    AND party.party_id        = acc.party_id
    AND trx_summ.cust_account_id   = p_cust_account_id
    AND   trx_summ.org_id          = p_org_id
   GROUP BY party.party_id,trx_summ.org_id;

Line: 858


 OPEN  select_pref;

Line: 859


 FETCH select_pref INTO l_pref_value;

Line: 860


 CLOSE select_pref;

Line: 862


 l_cash := IEX_UTILITIES.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', 'SELECT DEFAULT_EXCHANGE_RATE_TYPE FROM AR_CMGT_SETUP_OPTIONS');

Line: 868


       iex_debug_pub.LogMessage ('IEX Update Summary started for Invoice Event with id ' || id_val);

Line: 870


 OPEN select_ps (id_val); --id_val Contains Transaction ID .

Line: 873


   FETCH select_ps INTO  l_ps_tab(i);

Line: 875


   IF select_ps%NOTFOUND  THEN
     IF i = 0 THEN
       l_ps_exists := FALSE;

Line: 886


  CLOSE select_ps;

Line: 892


       iex_debug_pub.LogMessage ('IEX Update Summary started for Receipt Event with id ' || id_val);

Line: 894


   OPEN select_ps_rec (id_val); --id_val Contains Payment Schedule ID .

Line: 897


   FETCH select_ps_rec INTO  l_ps_tab(i);

Line: 899


   IF select_ps_rec%NOTFOUND  THEN
     IF i = 0 THEN
       l_ps_exists := FALSE;

Line: 910


  CLOSE select_ps_rec;

Line: 915


       iex_debug_pub.LogMessage ('IEX Update Summary started for credit memo Event with id ' || id_val);

Line: 917


    OPEN select_ps_cm (id_val); --id_val Contains Transaction ID .

Line: 920


   FETCH select_ps_cm INTO  l_ps_tab(i);

Line: 922


   IF select_ps_cm%NOTFOUND  THEN
     IF i = 0 THEN
       l_ps_exists := FALSE;

Line: 933


  CLOSE select_ps_cm;

Line: 938


       iex_debug_pub.LogMessage ('IEX Update Summary started for Adjustment Event with id ' || id_val);

Line: 940


    OPEN select_ps_adj (id_val); --id_val Contains Adjustment ID

Line: 943


   FETCH select_ps_adj INTO  l_ps_tab(i);

Line: 945


   IF select_ps_adj%NOTFOUND  THEN
     IF i = 0 THEN
       l_ps_exists := FALSE;

Line: 956


  CLOSE select_ps_adj;

Line: 961


       iex_debug_pub.LogMessage ('IEX Update Summary started for Auto Adjustment Event with id ' || id_val);

Line: 963


    OPEN select_ps_auto_adj (id_val); --id_val Contains Request ID

Line: 966


   FETCH select_ps_auto_adj INTO  l_ps_tab(i);

Line: 968


   IF select_ps_auto_adj%NOTFOUND  THEN
     IF i = 0 THEN
       l_ps_exists := FALSE;

Line: 979


  CLOSE select_ps_auto_adj;

Line: 984


       iex_debug_pub.LogMessage ('IEX Update Summary started for Auto Receipt Event with id ' || id_val);

Line: 986


    OPEN select_ps_auto_rec (id_val); --id_val Contains Request ID

Line: 989


   FETCH select_ps_auto_rec INTO  l_ps_tab(i);

Line: 991


   IF select_ps_auto_rec%NOTFOUND  THEN
     IF i = 0 THEN
       l_ps_exists := FALSE;

Line: 1002


  CLOSE select_ps_auto_rec;

Line: 1007


       iex_debug_pub.LogMessage ('IEX Update Summary started for Auto Invoice Event with id ' || id_val);

Line: 1009


    OPEN select_ps_auto_inv (id_val); --id_val Contains Request ID

Line: 1012


   FETCH select_ps_auto_inv INTO  l_ps_tab(i);

Line: 1014


   IF select_ps_auto_inv%NOTFOUND  THEN
     IF i = 0 THEN
       l_ps_exists := FALSE;

Line: 1025


  CLOSE select_ps_auto_inv;

Line: 1034


       iex_debug_pub.LogMessage ('IEX Update Summary l_ps_exists , Starting...' );

Line: 1042


        FOR upd_billto in update_iex_sum_billto
	                  (l_ps_tab(j).customer_id,l_ps_tab(j).customer_site_use_id,
	                  l_org_id)
	LOOP
	 --Synchronize ar and iex summary tables

	 UPDATE iex_dln_uwq_summary
	  SET
	     OP_INVOICES_COUNT           = upd_billto.OP_INVOICES_COUNT,
	     OP_DEBIT_MEMOS_COUNT        = upd_billto.OP_DEBIT_MEMOS_COUNT,
	     OP_DEPOSITS_COUNT		 = upd_billto.OP_DEPOSITS_COUNT,
	     OP_BILLS_RECEIVABLES_COUNT  = upd_billto.OP_BILLS_RECEIVABLES_COUNT,
	     OP_CHARGEBACK_COUNT         = upd_billto.OP_CHARGEBACK_COUNT,
	     OP_CREDIT_MEMOS_COUNT       = upd_billto.OP_CREDIT_MEMOS_COUNT,
	     UNRESOLVED_CASH_COUNT       = upd_billto.UNRESOLVED_CASH_COUNT,
	     DISPUTED_INV_COUNT          = upd_billto.DISPUTED_INV_COUNT,
	     BEST_CURRENT_RECEIVABLES    = upd_billto.BEST_CURRENT_RECEIVABLES,
	     OP_INVOICES_VALUE           = upd_billto.OP_INVOICES_VALUE,
	     OP_DEBIT_MEMOS_VALUE        = upd_billto.OP_DEBIT_MEMOS_VALUE,
	     OP_DEPOSITS_VALUE		 = upd_billto.OP_DEPOSITS_VALUE,
	     OP_BILLS_RECEIVABLES_VALUE  = upd_billto.OP_BILLS_RECEIVABLES_VALUE,
	     OP_CHARGEBACK_VALUE	 = upd_billto.OP_CHARGEBACK_VALUE,
	     OP_CREDIT_MEMOS_VALUE	 = upd_billto.OP_CREDIT_MEMOS_VALUE,
	     UNRESOLVED_CASH_VALUE	 = upd_billto.UNRESOLVED_CASH_VALUE,
	     RECEIPTS_AT_RISK_VALUE	 = upd_billto.RECEIPTS_AT_RISK_VALUE,
	     INV_AMT_IN_DISPUTE		 = upd_billto.INV_AMT_IN_DISPUTE,
	     PENDING_ADJ_VALUE		 = upd_billto.PENDING_ADJ_VALUE,
	     PAST_DUE_INV_VALUE		 = upd_billto.PAST_DUE_INV_VALUE,
	     PAST_DUE_INV_INST_COUNT     = upd_billto.PAST_DUE_INV_INST_COUNT,
	     LAST_PAYMENT_DATE           = upd_billto.LAST_PAYMENT_DATE,   --Added last payment columns for bug#5938261 by schekuri on 19-Mar-2007
             LAST_PAYMENT_AMOUNT         = upd_billto.LAST_PAYMENT_AMOUNT,
             LAST_PAYMENT_AMOUNT_CURR    = upd_billto.LAST_PAYMENT_AMOUNT_CURR,
             LAST_PAYMENT_NUMBER         = upd_billto.LAST_PAYMENT_NUMBER,
	     LAST_UPDATE_DATE            = SYSDATE,
	     LAST_UPDATED_BY             = FND_GLOBAL.USER_ID
           WHERE cust_account_id         = l_ps_tab(j).customer_id
            AND site_use_id              = l_ps_tab(j).customer_site_use_id
            AND org_id                   = l_org_id;

Line: 1082


       ELSIF (l_pref_value = 'ACCOUNT') THEN --Update for Strategy Level Account
        FOR upd_acc in update_iex_sum_acc
	                  (l_ps_tab(j).customer_id,l_org_id)
	LOOP
	 --Synchronize ar and iex summary tables

	 UPDATE iex_dln_uwq_summary
	  SET
	     OP_INVOICES_COUNT           = upd_acc.OP_INVOICES_COUNT,
	     OP_DEBIT_MEMOS_COUNT        = upd_acc.OP_DEBIT_MEMOS_COUNT,
	     OP_DEPOSITS_COUNT		 = upd_acc.OP_DEPOSITS_COUNT,
	     OP_BILLS_RECEIVABLES_COUNT  = upd_acc.OP_BILLS_RECEIVABLES_COUNT,
	     OP_CHARGEBACK_COUNT         = upd_acc.OP_CHARGEBACK_COUNT,
	     OP_CREDIT_MEMOS_COUNT       = upd_acc.OP_CREDIT_MEMOS_COUNT,
	     UNRESOLVED_CASH_COUNT       = upd_acc.UNRESOLVED_CASH_COUNT,
	     DISPUTED_INV_COUNT          = upd_acc.DISPUTED_INV_COUNT,
	     BEST_CURRENT_RECEIVABLES    = upd_acc.BEST_CURRENT_RECEIVABLES,
	     OP_INVOICES_VALUE           = upd_acc.OP_INVOICES_VALUE,
	     OP_DEBIT_MEMOS_VALUE        = upd_acc.OP_DEBIT_MEMOS_VALUE,
	     OP_DEPOSITS_VALUE		 = upd_acc.OP_DEPOSITS_VALUE,
	     OP_BILLS_RECEIVABLES_VALUE  = upd_acc.OP_BILLS_RECEIVABLES_VALUE,
	     OP_CHARGEBACK_VALUE	 = upd_acc.OP_CHARGEBACK_VALUE,
	     OP_CREDIT_MEMOS_VALUE	 = upd_acc.OP_CREDIT_MEMOS_VALUE,
	     UNRESOLVED_CASH_VALUE	 = upd_acc.UNRESOLVED_CASH_VALUE,
	     RECEIPTS_AT_RISK_VALUE	 = upd_acc.RECEIPTS_AT_RISK_VALUE,
	     INV_AMT_IN_DISPUTE		 = upd_acc.INV_AMT_IN_DISPUTE,
	     PENDING_ADJ_VALUE		 = upd_acc.PENDING_ADJ_VALUE,
	     PAST_DUE_INV_VALUE		 = upd_acc.PAST_DUE_INV_VALUE,
	     PAST_DUE_INV_INST_COUNT     = upd_acc.PAST_DUE_INV_INST_COUNT,
	     LAST_PAYMENT_DATE           = upd_acc.LAST_PAYMENT_DATE,   --Added last payment columns for bug#5938261 by schekuri on 19-Mar-2007
             LAST_PAYMENT_AMOUNT         = upd_acc.LAST_PAYMENT_AMOUNT,
             LAST_PAYMENT_AMOUNT_CURR    = upd_acc.LAST_PAYMENT_AMOUNT_CURR,
             LAST_PAYMENT_NUMBER         = upd_acc.LAST_PAYMENT_NUMBER,
     	     LAST_UPDATE_DATE            = SYSDATE,
	     LAST_UPDATED_BY             = FND_GLOBAL.USER_ID
           WHERE cust_account_id         = l_ps_tab(j).customer_id
            AND org_id                   = l_org_id;

Line: 1121


       ELSE --Update for Strategy Level Customer
        FOR upd_cu in update_iex_sum_cu
	                  (l_ps_tab(j).customer_id,l_org_id)
	LOOP
	 --Synchronize ar and iex summary tables

	 UPDATE iex_dln_uwq_summary
	  SET
	     OP_INVOICES_COUNT           = upd_cu.OP_INVOICES_COUNT,
	     OP_DEBIT_MEMOS_COUNT        = upd_cu.OP_DEBIT_MEMOS_COUNT,
	     OP_DEPOSITS_COUNT		 = upd_cu.OP_DEPOSITS_COUNT,
	     OP_BILLS_RECEIVABLES_COUNT  = upd_cu.OP_BILLS_RECEIVABLES_COUNT,
	     OP_CHARGEBACK_COUNT         = upd_cu.OP_CHARGEBACK_COUNT,
	     OP_CREDIT_MEMOS_COUNT       = upd_cu.OP_CREDIT_MEMOS_COUNT,
	     UNRESOLVED_CASH_COUNT       = upd_cu.UNRESOLVED_CASH_COUNT,
	     DISPUTED_INV_COUNT          = upd_cu.DISPUTED_INV_COUNT,
	     BEST_CURRENT_RECEIVABLES    = upd_cu.BEST_CURRENT_RECEIVABLES,
	     OP_INVOICES_VALUE           = upd_cu.OP_INVOICES_VALUE,
	     OP_DEBIT_MEMOS_VALUE        = upd_cu.OP_DEBIT_MEMOS_VALUE,
	     OP_DEPOSITS_VALUE		 = upd_cu.OP_DEPOSITS_VALUE,
	     OP_BILLS_RECEIVABLES_VALUE  = upd_cu.OP_BILLS_RECEIVABLES_VALUE,
	     OP_CHARGEBACK_VALUE	 = upd_cu.OP_CHARGEBACK_VALUE,
	     OP_CREDIT_MEMOS_VALUE	 = upd_cu.OP_CREDIT_MEMOS_VALUE,
	     UNRESOLVED_CASH_VALUE	 = upd_cu.UNRESOLVED_CASH_VALUE,
	     RECEIPTS_AT_RISK_VALUE	 = upd_cu.RECEIPTS_AT_RISK_VALUE,
	     INV_AMT_IN_DISPUTE		 = upd_cu.INV_AMT_IN_DISPUTE,
	     PENDING_ADJ_VALUE		 = upd_cu.PENDING_ADJ_VALUE,
	     PAST_DUE_INV_VALUE		 = upd_cu.PAST_DUE_INV_VALUE,
	     PAST_DUE_INV_INST_COUNT     = upd_cu.PAST_DUE_INV_INST_COUNT,
	     LAST_PAYMENT_DATE           = upd_cu.LAST_PAYMENT_DATE,   --Added last payment columns for bug#5938261 by schekuri on 19-Mar-2007
             LAST_PAYMENT_AMOUNT         = upd_cu.LAST_PAYMENT_AMOUNT,
             LAST_PAYMENT_AMOUNT_CURR    = upd_cu.LAST_PAYMENT_AMOUNT_CURR,
             LAST_PAYMENT_NUMBER         = upd_cu.LAST_PAYMENT_NUMBER,
     	     LAST_UPDATE_DATE            = SYSDATE,
	     LAST_UPDATED_BY             = FND_GLOBAL.USER_ID
           WHERE party_id                = upd_cu.party_id
            AND org_id                   = l_org_id;

Line: 1169


       iex_debug_pub.LogMessage ('IEX Update Summary updated ' || sql%rowcount || ' Rows' );

Line: 1185


END UPDATE_SUMMARY;