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

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

Line: 9


    SELECT e.employee_id
    FROM per_employees_current_x e, fnd_user u
    WHERE e.employee_id = u.employee_id
    AND u.user_id = nvl(x_userid, -1);

Line: 15


    SELECT res.resource_id
    FROM   jtf_rs_resource_extns res
    WHERE  res.source_id = nvl(x_person_id, -1)
    AND    res.user_id =  nvl(x_userid, -1)
    AND    trunc(res.START_DATE_ACTIVE) <= trunc(SYSDATE)
    AND    trunc(SYSDATE) <= trunc(NVL(RES.END_DATE_ACTIVE,SYSDATE))
    AND    res.category = p_category;

Line: 41


    l_del_clause := 'SELECT COUNT(1) FROM iex_delinquencies WHERE party_cust_id = :party_id ' ||
--BEGIN-FIX Bug#4408860-06/06/2005-jypark-exclude CLOSE status
--                    ' AND status <> ''CURRENT'' ';

Line: 46


    l_bnkrpt_clause := 'SELECT COUNT(1) FROM iex_bankruptcies bnkrpt WHERE bnkrpt.party_id = :party_id';

Line: 49


    l_del_clause := 'SELECT COUNT(1) FROM iex_delinquencies WHERE cust_account_id = :cust_account_id ' ||
--BEGIN-FIX Bug#4408860-06/06/2005-jypark-exclude CLOSE status
--                    ' AND status <> ''CURRENT'' ';

Line: 54


    l_bnkrpt_clause := 'SELECT COUNT(1) FROM iex_bankruptcies bnkrpt, hz_cust_accounts acc ' ||
                       ' WHERE bnkrpt.party_id = acc.party_id AND acc.cust_account_id = :cust_account_id';

Line: 58


    l_del_clause := 'SELECT COUNT(1) FROM iex_delinquencies WHERE customer_site_use_id = :site_use_id ' ||
--BEGIN-FIX Bug#4408860-06/06/2005-jypark-exclude CLOSE status
--                    ' AND status <> ''CURRENT'' ';

Line: 63


    l_bnkrpt_clause := 'SELECT COUNT(1) FROM iex_bankruptcies bnkrpt,hz_cust_site_uses site_use, hz_cust_acct_sites acct_site, hz_cust_accounts acc ' ||
                       ' WHERE bnkrpt.party_id = acc.party_id ' ||
                       ' AND acct_site.cust_account_id = acc.cust_account_id ' ||
                       ' AND site_use.cust_acct_site_id = acct_site.cust_acct_site_id ' ||
                       ' AND site_use.site_use_id = :site_use_id';

Line: 130


    l_pro_clause := 'SELECT COUNT(1) ' ||
                    'FROM (' ||
                    '       SELECT pro.promise_detail_id, pro.uwq_status, pro.uwq_active_date, pro.uwq_complete_date' ||
                    '       FROM iex_promise_details pro, hz_cust_accounts ca, iex_delinquencies_all del ' ||
                    '       WHERE ca.cust_account_id = pro.cust_account_id ' ||
                    '       AND pro.state = ''BROKEN_PROMISE'' ' ||
                    '       AND pro.status IN (''COLLECTABLE'', ''PENDING'') ' ||
                    '       AND pro.amount_due_remaining > 0 ' || -- added by jypark 01/02/2003
--BEGIN-FIX Bug#4349915-06/06/2005-jypark-include leasing promise and remove current resource dependency
--                    '       AND pro.resource_id = :resource_id ' ||
--END-FIX Bug#4349915-06/06/2005-jypark-include leasing promise and remove current resource dependency
                    '       AND pro.delinquency_id = del.delinquency_id '||
--BEGIN-FIX Bug#4408860-06/06/2005-jypark-exclude CLOSE status
--                    '       AND del.status <> ''CURRENT'' ' ||
                    '       AND (del.status NOT IN (''CURRENT'', ''CLOSE'') ' ||
--END-FIX Bug#4408860-06/06/2005-jypark-exclude CLOSE status
                    '       OR (del.status=''CURRENT'' and  del.source_program_name=''IEX_CURR_INV'')) ' || --Added for bug 6446848 06-Jan-2009 barathsr
                    '       AND ca.party_id =  :party_id ' ||
		    '       AND del.org_id =  :org_id ' ||
                    '       UNION ALL ' ||
                    '       SELECT pro.promise_detail_id, pro.uwq_status, pro.uwq_active_date, pro.uwq_complete_date' ||
                    '       FROM iex_promise_details pro, hz_cust_accounts ca ' ||
                    '       WHERE ca.cust_account_id = pro.cust_account_id ' ||
                    '       AND pro.state = ''BROKEN_PROMISE'' ' ||
                    '       AND pro.status IN (''COLLECTABLE'', ''PENDING'') ' ||
                    '       AND pro.amount_due_remaining > 0 ' || -- added by jypark 01/02/2003
--BEGIN-FIX Bug#4349915-06/06/2005-jypark-include leasing promise and remove current resource dependency
--                    '       AND pro.resource_id = :resource_id ' ||
--END-FIX Bug#4349915-06/06/2005-jypark-include leasing promise and remove current resource dependency
                    '       AND pro.delinquency_id is null ' ||
                    '       AND ca.party_id = :party_id ' ||
                    ')';

Line: 162


    l_bnkrpt_clause := 'SELECT COUNT(1) FROM iex_bankruptcies bnkrpt WHERE bnkrpt.party_id = :party_id ';

Line: 166


    l_pro_clause := 'SELECT COUNT(1) ' ||
                    'FROM (' ||
                    '       SELECT pro.promise_detail_id, pro.uwq_status, pro.uwq_active_date, pro.uwq_complete_date' ||
                    '       FROM iex_promise_details pro, iex_delinquencies_all del ' ||
                    '       WHERE pro.delinquency_id = del.delinquency_id ' ||
                    '       AND pro.state = ''BROKEN_PROMISE'' ' ||
                    '       AND pro.status IN (''COLLECTABLE'', ''PENDING'') ' ||
                    '       AND pro.amount_due_remaining > 0 ' || -- added by jypark 01/02/2003
--BEGIN-FIX Bug#4349915-06/06/2005-jypark-include leasing promise and remove current resource dependency
--                    '       AND pro.resource_id = :resource_id ' ||
--END-FIX Bug#4349915-06/06/2005-jypark-include leasing promise and remove current resource dependency
                    '       AND pro.cust_account_id = :cust_account_id ' ||
--BEGIN-FIX Bug#4408860-06/06/2005-jypark-exclude CLOSE status
--                    '       AND del.status <> ''CURRENT'' ' ||
                    '       AND (del.status NOT IN (''CURRENT'', ''CLOSE'') ' ||
		    '       OR (del.status=''CURRENT'' and  del.source_program_name=''IEX_CURR_INV'')) ' || --Added for bug 6446848 06-Jan-2009 barathsr
		    '       AND del.org_id =  :org_id ' ||
--END-FIX Bug#4408860-06/06/2005-jypark-exclude CLOSE status
                    '       UNION ALL ' ||
                    '       SELECT pro.promise_detail_id, pro.uwq_status, pro.uwq_active_date, pro.uwq_complete_date' ||
                    '       FROM iex_promise_details pro' ||
                    '       WHERE pro.state = ''BROKEN_PROMISE'' ' ||
                    '       AND pro.status IN (''COLLECTABLE'', ''PENDING'') ' ||
                    '       AND pro.amount_due_remaining > 0 ' || -- added by jypark 01/02/2003
--BEGIN-FIX Bug#4349915-06/06/2005-jypark-include leasing promise and remove current resource dependency
--                    '       AND pro.resource_id = :resource_id ' ||
--END-FIX Bug#4349915-06/06/2005-jypark-include leasing promise and remove current resource dependency
                    '       AND pro.cust_account_id = :cust_account_id ' ||
                    '       AND pro.delinquency_id is null ' ||
                    ')';

Line: 196


    l_bnkrpt_clause := 'SELECT COUNT(1) FROM iex_bankruptcies bnkrpt, hz_cust_accounts acc ' ||
                       ' WHERE bnkrpt.party_id = acc.party_id AND acc.cust_account_id = :cust_account_id';

Line: 201


    l_pro_clause := 'SELECT COUNT(1) ' ||
                    'FROM (' ||
                    '       SELECT pro.promise_detail_id, pro.uwq_status, pro.uwq_active_date, pro.uwq_complete_date' ||
                    '       FROM iex_promise_details pro, iex_delinquencies_all del ' ||
                    '       WHERE pro.state = ''BROKEN_PROMISE'' ' ||
                    '       AND pro.status IN (''COLLECTABLE'', ''PENDING'') ' ||
                    '       AND pro.amount_due_remaining > 0 ' || -- added by jypark 01/02/2003
--BEGIN-FIX Bug#4349915-06/06/2005-jypark-include leasing promise and remove current resource dependency
--                    '       AND pro.resource_id =:resource_id ' ||
--END-FIX Bug#4349915-06/06/2005-jypark-include leasing promise and remove current resource dependency
                    '       AND pro.delinquency_id = del.delinquency_id '||
--BEGIN-FIX Bug#4408860-06/06/2005-jypark-exclude CLOSE status
--                    '       AND del.status <> ''CURRENT'' ' ||
                    '       AND (del.status NOT IN (''CURRENT'', ''CLOSE'') ' ||
--END-FIX Bug#4408860-06/06/2005-jypark-exclude CLOSE status
                    '       OR (del.status=''CURRENT'' and  del.source_program_name=''IEX_CURR_INV'')) ' || --Added for bug 6446848 06-Jan-2009 barathsr
                    '       AND del.customer_site_use_id = :site_use_id ' ||
		    '       AND del.org_id =  :org_id ' ||
--BEGIN-FIX Bug#4349915-06/06/2005-jypark-include leasing promise
                    '       UNION ALL ' ||
                    '       SELECT pro.promise_detail_id, pro.uwq_status, pro.uwq_active_date, pro.uwq_complete_date' ||
                    '       FROM iex_promise_details pro, okc_k_headers_b okch ' ||
                    '       WHERE pro.state = ''BROKEN_PROMISE'' ' ||
                    '       AND pro.status IN (''COLLECTABLE'', ''PENDING'') ' ||
                    '       AND pro.amount_due_remaining > 0 ' || -- added by jypark 01/02/2003
                    '       AND pro.contract_id = okch.id ' ||
                    '       AND okch.bill_to_site_use_id = :site_use_id ' ||
--END-FIX Bug#4349915-06/06/2005-jypark-include leasing promise
                    ')';

Line: 230


    l_bnkrpt_clause := 'SELECT COUNT(1) FROM iex_bankruptcies bnkrpt,hz_cust_site_uses site_use, hz_cust_acct_sites acct_site, hz_cust_accounts acc ' ||
                       ' WHERE bnkrpt.party_id = acc.party_id ' ||
                       ' AND acct_site.cust_account_id = acc.cust_account_id ' ||
                       ' AND site_use.cust_acct_site_id = acct_site.cust_acct_site_id ' ||
                       ' AND site_use.site_use_id = :site_use_id';

Line: 322


    l_str_clause := 'SELECT COUNT(1) FROM iex_strategy_work_items wtm, iex_strategies str ' ||
                    'WHERE wtm.strategy_id = str.strategy_id ' ||
                    'AND wtm.resource_id = :resource_id ' ||
                    'AND str.status_code in (''OPEN'', ''ONHOLD'') ' ||
                    'AND wtm.status_code in (''OPEN'', ''ONHOLD'') ' ||
                    'AND str.jtf_object_type = ''PARTY'' ' ||
                    'AND str.jtf_object_id = :party_id ';

Line: 329


    l_bnkrpt_clause := 'SELECT COUNT(1) FROM iex_bankruptcies bnkrpt WHERE bnkrpt.party_id = :party_id';

Line: 332


    l_str_clause := 'SELECT COUNT(1) FROM iex_strategy_work_items wtm, iex_strategies str ' ||
                    'WHERE wtm.strategy_id = str.strategy_id ' ||
                    'AND wtm.resource_id = :resource_id ' ||
                    'AND str.status_code in (''OPEN'', ''ONHOLD'') ' ||
                    'AND wtm.status_code in (''OPEN'', ''ONHOLD'') ' ||
                    'AND str.jtf_object_type = ''IEX_ACCOUNT'' ' ||
                    'AND str.jtf_object_id = :cust_account_id ';

Line: 339


    l_bnkrpt_clause := 'SELECT COUNT(1) FROM iex_bankruptcies bnkrpt, hz_cust_accounts acc ' ||
                       ' WHERE bnkrpt.party_id = acc.party_id AND acc.cust_account_id = :cust_account_id';

Line: 343


    l_str_clause := 'SELECT COUNT(1) FROM iex_strategy_work_items wtm, iex_strategies str ' ||
                    'WHERE wtm.strategy_id = str.strategy_id ' ||
                    'AND wtm.resource_id = :resource_id '||
                    'AND str.status_code in (''OPEN'', ''ONHOLD'') ' ||
                    'AND wtm.status_code in (''OPEN'', ''ONHOLD'') ' ||
                    'AND str.jtf_object_type = ''IEX_BILLTO'' ' ||
                    'AND str.jtf_object_id = :site_use_id ';

Line: 350


    l_bnkrpt_clause := 'SELECT COUNT(1) FROM iex_bankruptcies bnkrpt,hz_cust_site_uses site_use, hz_cust_acct_sites acct_site, hz_cust_accounts acc ' ||
                       ' WHERE bnkrpt.party_id = acc.party_id ' ||
                       ' AND acct_site.cust_account_id = acc.cust_account_id ' ||
                       ' AND site_use.cust_acct_site_id = acct_site.cust_acct_site_id ' ||
                       ' AND site_use.site_use_id = :site_use_id';

Line: 405


    SELECT set_of_books_id
    FROM ar_system_parameters;

Line: 409


    SELECT default_exchange_rate_type
    FROM ar_cmgt_setup_options; */

Line: 443


    l_amt_clause := ' SELECT o_summ.last_payment_amount, o_summ.currency ' ||
                    ' FROM ar_trx_bal_summary o_summ, hz_cust_accounts acc' ||
                    ' WHERE o_summ.cust_account_id = acc.cust_account_id' ||
                    ' AND acc.party_id = :party_id ' ||
                    ' AND last_payment_date = ' || ' (SELECT MAX(last_payment_date) ' ||
                                                   '  FROM ar_trx_bal_summary ' ||
                                                   '  WHERE cust_account_id = o_summ.cust_account_id) ';

Line: 453


    l_amt_clause := ' SELECT o_summ.last_payment_amount, o_summ.currency ' ||
                    ' FROM ar_trx_bal_summary o_summ' ||
                    ' WHERE o_summ.cust_account_id = :cust_account_id ' ||
                    ' AND o_summ.last_payment_date = ' || ' (SELECT MAX(last_payment_date) ' ||
                                                   '  FROM ar_trx_bal_summary ' ||
                                                   '  WHERE cust_account_id = o_summ.cust_account_id) ';

Line: 461


    l_amt_clause := ' SELECT o_summ.last_payment_amount, o_summ.currency ' ||
                    ' FROM ar_trx_bal_summary o_summ' ||
                    ' WHERE o_summ.site_use_id = :site_use_id ' ||
                    ' AND o_summ.last_payment_date = ' || ' (SELECT MAX(last_payment_date) ' ||
                                                   '  FROM ar_trx_bal_summary ' ||
                                                   '  WHERE site_use_id = o_summ.site_use_id) ';

Line: 495


    l_no_clause := ' SELECT o_summ.last_payment_number ' ||
                    ' FROM ar_trx_bal_summary o_summ, hz_cust_accounts acc' ||
                    ' WHERE o_summ.cust_account_id = acc.cust_account_id' ||
                    ' AND acc.party_id = :party_id ' ||
                    ' AND last_payment_date = ' || ' (SELECT MAX(last_payment_date) ' ||
                                                   '  FROM ar_trx_bal_summary ' ||
                                                   '  WHERE cust_account_id = o_summ.cust_account_id) ';

Line: 505


    l_no_clause := ' SELECT o_summ.last_payment_number ' ||
                    ' FROM ar_trx_bal_summary o_summ' ||
                    ' WHERE o_summ.cust_account_id = :cust_account_id ' ||
                    ' AND o_summ.last_payment_date = ' || ' (SELECT MAX(last_payment_date) ' ||
                                                   '  FROM ar_trx_bal_summary ' ||
                                                   '  WHERE cust_account_id = o_summ.cust_account_id) ';

Line: 513


    l_no_clause := ' SELECT o_summ.last_payment_number ' ||
                    ' FROM ar_trx_bal_summary o_summ' ||
                    ' WHERE o_summ.site_use_id = :site_use_id ' ||
                    ' AND o_summ.last_payment_date = ' || ' (SELECT MAX(last_payment_date) ' ||
                                                   '  FROM ar_trx_bal_summary ' ||
                                                   '  WHERE site_use_id = o_summ.site_use_id) ';

Line: 535


    SELECT a.score_value
    FROM iex_score_histories a
    WHERE a.creation_date =
     (SELECT MAX(creation_date)
      FROM iex_Score_histories
      WHERE score_object_code = x_score_object_code
      AND score_object_id = x_score_object_id)
    AND a.score_object_code = x_score_object_code
    AND a.score_object_id = x_score_object_id;

Line: 580


    SELECT SUM(amount_due_remaining)
    FROM ( SELECT amount_due_remaining
           FROM iex_promise_details pro, iex_delinquencies_all del
           WHERE del.party_cust_id = p_party_id
           AND pro.status in ('COLLECTABLE', 'PENDING')
           AND pro.state = 'BROKEN_PROMISE'
           AND pro.amount_due_remaining > 0
           AND pro.delinquency_id = del.delinquency_id
           AND (del.status IN ('DELINQUENT', 'PREDELINQUENT')
	   or (del.status='CURRENT' and  del.source_program_name='IEX_CURR_INV'))--Added for Bug 6446848 barathsr 06-Jan-2009
	   AND del.org_id = p_org_id
           UNION ALL
           SELECT amount_due_remaining
           FROM iex_promise_details pro, okc_k_headers_b okch, hz_cust_accounts ca
           WHERE ca.cust_account_id = pro.cust_account_id
           AND ca.party_id = p_party_id
           AND pro.status in ('COLLECTABLE', 'PENDING')
           AND pro.state = 'BROKEN_PROMISE'
           AND pro.amount_due_remaining > 0
           AND pro.contract_id = okch.id
          );

Line: 604


    SELECT SUM(amount_due_remaining)
    FROM iex_promise_details pro, iex_delinquencies_all del
    WHERE pro.cust_account_id = p_cust_account_id
    AND pro.status in ('COLLECTABLE', 'PENDING')
    AND pro.state = 'BROKEN_PROMISE'
    AND pro.amount_due_remaining > 0
    AND pro.resource_id = g_resource_id
--BEGIN-FIX Bug#4408860-06/06/2005-jypark-exclude CLOSE status
--    AND pro.delinquency_id = del.delinquency_id(+)
--    AND del.status(+) <> 'CURRENT';

Line: 632


    SELECT SUM(amount_due_remaining)
    FROM ( SELECT amount_due_remaining
           FROM iex_promise_details pro, iex_delinquencies_all del
           WHERE del.customer_site_use_id = p_site_use_id
           AND pro.status in ('COLLECTABLE', 'PENDING')
           AND pro.state = 'BROKEN_PROMISE'
           AND pro.amount_due_remaining > 0
           AND pro.delinquency_id = del.delinquency_id
           AND (del.status IN ('DELINQUENT', 'PREDELINQUENT')
           or (del.status='CURRENT' and  del.source_program_name='IEX_CURR_INV'))--Added for Bug 6446848 barathsr 06-Jan-2009
	   AND del.org_id = p_org_id
           UNION ALL
           SELECT amount_due_remaining
           FROM iex_promise_details pro, okc_k_headers_b okch
           WHERE okch.bill_to_site_use_id = p_site_use_id
           AND pro.status in ('COLLECTABLE', 'PENDING')
           AND pro.state = 'BROKEN_PROMISE'
           AND pro.amount_due_remaining > 0
           AND pro.contract_id = okch.id
          );

Line: 691


    SELECT SUM(promise_amount)
    FROM ( SELECT promise_amount
           FROM iex_promise_details pro, iex_delinquencies_all del
           WHERE del.party_cust_id = p_party_id
           AND pro.status in ('COLLECTABLE', 'PENDING')
           AND pro.state = 'BROKEN_PROMISE'
           AND pro.amount_due_remaining > 0
           AND pro.delinquency_id = del.delinquency_id
           AND (del.status IN ('DELINQUENT', 'PREDELINQUENT')
	   or (del.status='CURRENT' and  del.source_program_name='IEX_CURR_INV'))--Added for Bug 6446848 barathsr 06-Jan-2009
	   AND del.org_id = p_org_id
           UNION ALL
           SELECT promise_amount
           FROM iex_promise_details pro, okc_k_headers_b okch, hz_cust_accounts ca
           WHERE ca.party_id = p_party_id
           AND pro.cust_account_id = ca.cust_account_id
           AND pro.status in ('COLLECTABLE', 'PENDING')
           AND pro.state = 'BROKEN_PROMISE'
           AND pro.amount_due_remaining > 0
           AND pro.contract_id = okch.id
   );

Line: 715


    SELECT SUM(promise_amount)
    FROM iex_promise_details pro, iex_delinquencies_all del
    WHERE pro.cust_account_id = p_cust_account_id
    AND pro.amount_due_remaining > 0
    AND pro.status in ('COLLECTABLE', 'PENDING')
    AND pro.state = 'BROKEN_PROMISE'
    AND pro.resource_id = g_resource_id
--BEGIN-FIX Bug#4408860-06/06/2005-jypark-exclude CLOSE status
--    AND pro.delinquency_id = del.delinquency_id(+)
--    AND del.status(+) <> 'CURRENT';

Line: 742


    SELECT SUM(promise_amount)
    FROM ( SELECT promise_amount
           FROM iex_promise_details pro, iex_delinquencies_all del
           WHERE del.customer_site_use_id = p_site_use_id
           AND pro.status in ('COLLECTABLE', 'PENDING')
           AND pro.state = 'BROKEN_PROMISE'
           AND pro.amount_due_remaining > 0
           AND pro.delinquency_id = del.delinquency_id
           AND (del.status IN ('DELINQUENT', 'PREDELINQUENT')
	   or (del.status='CURRENT' and  del.source_program_name='IEX_CURR_INV'))--Added for Bug 6446848 barathsr 06-Jan-2009
	   AND del.org_id = p_org_id
           UNION ALL
           SELECT promise_amount
           FROM iex_promise_details pro, okc_k_headers_b okch
           WHERE okch.bill_to_site_use_id = p_site_use_id
           AND pro.status in ('COLLECTABLE', 'PENDING')
           AND pro.state = 'BROKEN_PROMISE'
           AND pro.amount_due_remaining > 0
           AND pro.contract_id = okch.id
   );