APPS.IEX_UTILITIES SQL Statements

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

Line: 56

	SELECT nvl( SUM (acctd_amount_due_remaining), 0) AMOUNT
	into Amount
	FROM AR_PAYMENT_SCHEDULES
	WHERE customer_id = p_cust_acct_id
	AND customer_id+0 = p_cust_acct_id
	AND   class IN ('INV', 'GUAR', 'CB', 'DM', 'DEP')
	AND   status = 'OP'	;

Line: 66

	SELECT nvl(SUM (acctd_amount_due_remaining), 0) AMOUNT
	into	amount
	FROM AR_PAYMENT_SCHEDULES
	WHERE customer_id = p_cust_acct_id
	AND customer_id+0 = p_cust_acct_id
	AND class = 'PMT'
	AND status = 'OP'
	AND acctd_amount_due_remaining <> 0;

Line: 79

	SELECT nvl(SUM (acctd_amount_due_remaining), 0) AMOUNT
	into amount
	FROM AR_PAYMENT_SCHEDULES
	WHERE customer_id = p_cust_acct_id
	AND customer_id+0 = p_cust_acct_id
	AND class = 'CM'
	AND status = 'OP';

Line: 157

    vstr1            := ' Select ''X'' ';

Line: 160

    vstr4            := '     (Select ' ;

Line: 192

             ' Select ''X'' ' ||
             ' From ' || p_table_name || ' ' ||
             ' Where exists ' ||
             '     (Select ' || p_col_name ||
             '     From ' || p_table_name ||
             '     Where ' || p_col_name || ' = :a1)';

Line: 282

    vstr1            := ' Select ''X'' ';

Line: 285

    vstr4            := '     (Select ' ;

Line: 315

        vPLSQL := ' Select ''X''   ' ||
                  ' From ' || p_table_name ||
                  ' Where exists   ' ||
                  '   (Select ' || p_col_name ||
                  '    From ' || p_table_name ||
                  '    Where ' || p_col_name || ' = :a1)';

Line: 399

    vstr1     := 'Select Count(LOOKUP_CODE) ';

Line: 436

              'Select Count(LOOKUP_CODE) '  ||
              'From ' || p_lookup_view || ' ' ||
              'Where LOOKUP_TYPE = ' || l_lookup_type  || ' AND ' ||
              'LOOKUP_CODE = ' || l_lookup_code || ' AND ' ||
              'ENABLED_FLAG = ''Y''';

Line: 451

       select count(lookup_code)
         into count_id
         from iex_lookups_v
        where lookup_type = p_lookup_type
          and lookup_code = p_lookup_code
          and enabled_flag = 'Y';

Line: 512

    SELECT DISTINCT ac.employee_id
    FROM  hz_customer_profiles hp,ar_collectors ac
    WHERE  hp.collector_id = ac.collector_id
    AND    ac.employee_id is not null
    AND    hp.party_id = p_party_id;

Line: 519

    SELECT resource_id, source_id, user_id, source_name, user_name
	FROM jtf_rs_resource_extns
	WHERE source_id = p_person_id;

Line: 538

        'SELECT DISTINCT hp.employee_id
         FROM  hz_customer_profiles hp,ar_collectors ac
         WHERE m.person_id = ac.employee_id
         AND  hp.collector_id = ac.collector_id
         and ac.employee_id is not null
         AND hp.party_id = p_party_id ' ||
        'SELECT resource_id, source_id, user_id, source_name, user_name ' ||
	'FROM jtf_rs_resource_extns ' ||
	'WHERE source_id = p_person_id');

Line: 632

    SELECT acc.person_id, acc.salesforce_id, count(work_item_id)
    FROM  as_accesses acc, jtf_rs_resource_extns rs, iex_strategy_work_items wi
    WHERE acc.customer_id = p_party_id and rs.resource_id = acc.salesforce_id
      and acc.salesforce_id = wi.resource_id(+)
      and wi.status_code(+) = 'OPEN'
      and acc.sales_lead_id is null and acc.lead_id is null
      and rs.user_id is not null
      group by acc.salesforce_id, acc.person_id ORDER BY 3;

Line: 645

    SELECT ac.employee_id, ac.resource_id, count(work_item_id)
    FROM  hz_customer_profiles hp, jtf_rs_resource_extns rs, iex_strategy_work_items wi,
          ar_collectors ac
    WHERE hp.party_id      = p_party_id
      and hp.cust_account_id = -1
      and rs.resource_id   = ac.resource_id
      and hp.collector_id  = ac.collector_id
      and ac.resource_id   = wi.resource_id
      and wi.status_code   = 'OPEN'
      and rs.user_id is not null
      and    ac.employee_id is not null
      group by ac.resource_id, ac.employee_id
    union all
    SELECT ac.employee_id, ac.resource_id, 0
    FROM  hz_customer_profiles hp, jtf_rs_resource_extns rs,ar_collectors ac
    WHERE hp.party_id       = p_party_id
      and hp.cust_account_id = -1
      and rs.resource_id    = ac.resource_id
      and hp.collector_id   = ac.collector_id
      and rs.user_id is not null and
      not exists (select null from iex_strategy_work_items wi
            where ac.resource_id = wi.resource_id
      and wi.status_code = 'OPEN')
      and    ac.employee_id is not null
       group by ac.resource_id, ac.employee_id
      ) order by 3;

Line: 677

SELECT ac.employee_id, ac.resource_id, 0
FROM  hz_customer_profiles hp, ar_collectors ac
WHERE hp.party_id = p_party_id
  and hp.cust_account_id = -1
  and hp.collector_id  = ac.collector_id
  and ac.resource_type = 'RS_RESOURCE'
  -- Bug4483896. Fixed by lkkumar. Check for inactive_date. Start.
  and trunc(nvl(ac.inactive_date,sysdate)) >= trunc(sysdate)
  and nvl(ac.status,'A') = 'A'
  -- Bug4483896. Fixed by lkkumar. Check for inactive_date. End.
union all
( SELECT jtg.person_id, jtg.resource_id, count(work_item_id)
    FROM  hz_customer_profiles hp,  iex_strategy_work_items wi,
          ar_collectors ac, jtf_rs_group_members jtg
    WHERE hp.party_id  = P_PARTY_ID
      and hp.cust_account_id = -1
      and hp.collector_id  = ac.collector_id
      and ac.resource_type = 'RS_GROUP'
      and ac.resource_id  = jtg.group_id
      and jtg.resource_id = wi.resource_id
      and wi.status_code   = 'OPEN'
      -- Bug4483896. Fixed by lkkumar. Check for inactive_date. Start.
      and trunc(nvl(ac.inactive_date,sysdate)) >= trunc(sysdate)
      and nvl(ac.status,'A') = 'A'
      group by jtg.resource_id, jtg.person_id
UNION ALL
    SELECT jtg.person_id, jtg.resource_id, 0
    FROM  hz_customer_profiles hp, ar_collectors ac,
      jtf_rs_group_members jtg
    WHERE hp.party_id  = p_party_id
      and hp.cust_account_id = -1
      and hp.collector_id   = ac.collector_id
      and ac.resource_type = 'RS_GROUP'
      and ac.resource_id = jtg.group_id
      and not exists (select null from iex_strategy_work_items wi
            where jtg.resource_id = wi.resource_id
      and wi.status_code = 'OPEN')
      -- Bug4483896. Fixed by lkkumar. Check for inactive_date. Start.
      and trunc(nvl(ac.inactive_date,sysdate)) >= trunc(sysdate)
      and nvl(ac.status,'A') = 'A'
      -- Bug4483896. Fixed by lkkumar. Check for inactive_date. End.
       group by jtg.resource_id, jtg.person_id
      ) order by 3;

Line: 723

    SELECT DISTINCT person_id, salesforce_id
    FROM  as_accesses acc
    WHERE acc.customer_id = p_party_id;

Line: 741

     'SELECT ac.employee_id, ac.resource_id, count(work_item_id) ' ||
     ' FROM  hz_customer_profiles hp, jtf_rs_resource_extns rs, iex_strategy_work_items wi, '||
     ' ar_collectors ac ' ||
     ' WHERE hp.party_id      = p_party_id '    ||
     ' and rs.resource_id   = ac.resource_id'   ||
     ' and hp.collector_id  = ac.collector_id'  ||
     ' and ac.resource_id   = wi.resource_id'   ||
     ' and wi.status_code   = OPEN '   ||
     ' and rs.user_id is not null'||
     ' group by ac.resource_id, ac.employee_id ');

Line: 829

    SELECT ac.employee_id, ac.resource_id, count(work_item_id)
    FROM  hz_customer_profiles hp, jtf_rs_resource_extns rs, iex_strategy_work_items wi,
          ar_collectors ac
    WHERE hp.cust_account_id = p_account_id
      and hp.site_use_id is null
      and rs.resource_id   = ac.resource_id
      and hp.collector_id  = ac.collector_id
      and ac.resource_id   = wi.resource_id
      and wi.status_code   = 'OPEN'
      and rs.user_id is not null
      and    ac.employee_id is not null
      group by ac.resource_id, ac.employee_id
    union all
    SELECT ac.employee_id, ac.resource_id, 0
    FROM  hz_customer_profiles hp, jtf_rs_resource_extns rs,ar_collectors ac
    WHERE hp.cust_account_id       = p_account_id
      and hp.site_use_id is null
      and rs.resource_id    = ac.resource_id
      and hp.collector_id   = ac.collector_id
      and rs.user_id is not null and
      not exists (select null from iex_strategy_work_items wi
            where ac.resource_id = wi.resource_id
      and wi.status_code = 'OPEN')
      and    ac.employee_id is not null
       group by ac.resource_id, ac.employee_id
      ) order by 3;

Line: 861

SELECT ac.employee_id, ac.resource_id, 0
FROM  hz_customer_profiles hp, ar_collectors ac
WHERE hp.cust_account_id = p_account_id
  and hp.site_use_id is null
  and hp.collector_id  = ac.collector_id
  and ac.resource_type = 'RS_RESOURCE'
union all
( SELECT jtg.person_id, jtg.resource_id, count(work_item_id)
    FROM  hz_customer_profiles hp,  iex_strategy_work_items wi,
          ar_collectors ac, jtf_rs_group_members jtg
    WHERE hp.cust_account_id  = p_account_id
      and hp.site_use_id is NULL
      and hp.collector_id  = ac.collector_id
      and ac.resource_type = 'RS_GROUP'
      and ac.resource_id  = jtg.group_id
      and jtg.resource_id = wi.resource_id
      and wi.status_code   = 'OPEN'
      group by jtg.resource_id, jtg.person_id
UNION ALL
    SELECT jtg.person_id, jtg.resource_id, 0
    FROM  hz_customer_profiles hp, ar_collectors ac,
      jtf_rs_group_members jtg
    WHERE hp.cust_account_id  = p_account_id
      and hp.site_use_id is null
      and hp.collector_id   = ac.collector_id
      and ac.resource_type = 'RS_GROUP'
      and ac.resource_id = jtg.group_id
      and not exists (select null from iex_strategy_work_items wi
            where jtg.resource_id = wi.resource_id
      and wi.status_code = 'OPEN')
       group by jtg.resource_id, jtg.person_id
      ) order by 3;*/

Line: 896

    SELECT DISTINCT person_id, salesforce_id
    FROM  as_accesses acc
    WHERE acc.customer_id = p_party_id;

Line: 914

     'SELECT ac.employee_id, ac.resource_id, count(work_item_id) ' ||
     ' FROM  hz_customer_profiles hp, jtf_rs_resource_extns rs, iex_strategy_work_items wi, '||
     ' ar_collectors ac ' ||
     ' WHERE hp.party_id      = p_party_id '    ||
     ' and rs.resource_id   = ac.resource_id'   ||
     ' and hp.collector_id  = ac.collector_id'  ||
     ' and ac.resource_id   = wi.resource_id'   ||
     ' and wi.status_code   = OPEN '   ||
     ' and rs.user_id is not null'||
     ' group by ac.resource_id, ac.employee_id ');

Line: 1001

    SELECT ac.employee_id, ac.resource_id, count(cas_id)
    FROM  hz_customer_profiles hp, jtf_rs_resource_extns rs, iex_cases_vl wi,ar_collectors ac
    WHERE hp.party_id = p_party_id
      and rs.resource_id = ac.resource_id
      and hp.collector_id = ac.collector_id
      and ac.resource_id = wi.owner_resource_id(+)
      and rs.user_id is not null
      and    ac.employee_id is not null
      group by ac.resource_id, ac.employee_id ORDER BY 3;*/

Line: 1013

    SELECT DISTINCT person_id, salesforce_id
    FROM  as_accesses acc
    WHERE acc.customer_id = p_party_id;

Line: 1029

     'SELECT ac.employee_id, ac.resource_id, count(cas_id)
      FROM  hz_customer_profiles hp, jtf_rs_resource_extns rs, iex_cases_vl wi,ar_collectors ac
      WHERE hp.party_id = p_party_id
      and rs.resource_id = ac.resource_id
      and hp.collector_id = ac.collector_id
      and ac.resource_id = wi.owner_resource_id(+)
      and rs.user_id is not null
      group by ac.resource_id, ac.employee_id ORDER BY 3');

Line: 1169

    SELECT ac.employee_id, ac.resource_id
    FROM  hz_customer_profiles hp, jtf_rs_resource_extns rs,
           ar_collectors ac
    WHERE hp.site_use_id    = p_site_use_id
      and rs.resource_id    = ac.resource_id
      and hp.collector_id   = ac.collector_id
      and rs.user_id is not null
      and    ac.employee_id is not null
      group by ac.resource_id, ac.employee_id
    union all
    SELECT ac.employee_id, ac.resource_id, 0
    FROM  hz_customer_profiles hp, jtf_rs_resource_extns rs,ar_collectors ac
    WHERE hp.site_use_id = p_site_use_id
      and rs.resource_id = ac.resource_id
      and hp.collector_id = ac.collector_id
      and rs.user_id is not null and
      not exists (select null from iex_strategy_work_items wi
            where ac.resource_id = wi.resource_id
      and wi.status_code = 'OPEN')
      and    ac.employee_id is not null
       group by ac.resource_id, ac.employee_id
      ) order by 3;

Line: 1196

SELECT ac.employee_id, ac.resource_id, 0
FROM  hz_customer_profiles hp, ar_collectors ac
WHERE hp.site_use_id = p_site_use_id
  and hp.collector_id  = ac.collector_id
  and ac.resource_type = 'RS_RESOURCE'
union all
( SELECT jtg.person_id, jtg.resource_id, count(work_item_id)
    FROM  hz_customer_profiles hp,  iex_strategy_work_items wi,
          ar_collectors ac, jtf_rs_group_members jtg
    WHERE hp.site_use_id  = p_site_use_id
      and hp.collector_id  = ac.collector_id
      and ac.resource_type = 'RS_GROUP'
      and ac.resource_id  = jtg.group_id
      and jtg.resource_id = wi.resource_id
      and wi.status_code   = 'OPEN'
      group by jtg.resource_id, jtg.person_id
UNION ALL
    SELECT jtg.person_id, jtg.resource_id, 0
    FROM  hz_customer_profiles hp, ar_collectors ac,
      jtf_rs_group_members jtg
    WHERE hp.site_use_id  = p_site_use_id
      and hp.collector_id   = ac.collector_id
      and ac.resource_type = 'RS_GROUP'
      and ac.resource_id = jtg.group_id
      and not exists (select null from iex_strategy_work_items wi
            where jtg.resource_id = wi.resource_id
      and wi.status_code = 'OPEN')
       group by jtg.resource_id, jtg.person_id
      ) order by 3;

Line: 1239

      ' SELECT ac.employee_id, ac.resource_id, count(work_item_id)
       FROM  hz_customer_profiles hp, jtf_rs_resource_extns rs, iex_strategy_work_items wi,ar_collectors ac
       WHERE hp.site_use_id    = p_site_use_id
       and rs.resource_id    = ac.resource_id
       and ac.resource_id    = wi.resource_id
       and hp.collector_id   = ac.collector_id
       and wi.status_code    = OPEN
       and rs.user_id is not null
       group by ac.resource_id, ac.employee_id');

Line: 1328

     SELECT meaning
     INTO   l_meaning
     FROM   iex_lookups_v
     WHERE  lookup_type = p_lookup_type
      AND  lookup_code = p_lookup_code ;

Line: 1384

PROCEDURE delete_param_value(p_param_key IN NUMBER) IS
BEGIN
  IF pg_param_tab.EXISTS(p_param_key) THEN
    pg_param_tab.DELETE(p_param_key);

Line: 1421

        SELECT  ca.party_id party_id, ca.cust_account_id cust_account_id
              FROM hz_cust_site_uses site_uses, hz_cust_acct_sites acct_sites, hz_cust_accounts ca
              WHERE site_uses.site_use_id = p_customer_site_use_id
              AND acct_sites.cust_acct_site_id = site_uses.cust_acct_site_id
              AND ca.cust_account_id = acct_sites.cust_account_id;

Line: 1428

      select dunning_letters from hz_customer_profiles
      where site_use_id = p_site_use_id and status = 'A';

Line: 1432

        SELECT party_id  FROM HZ_CUST_ACCOUNTS
            WHERE cust_account_id = p_cust_account_id ;

Line: 1436

      select dunning_letters from hz_customer_profiles
      where cust_account_id = p_cust_account_id and status = 'A' and site_use_id is null;

Line: 1440

      select dunning_letters from hz_customer_profiles
      -- begin bug 4587842 ctlee 09/06/2005
      where party_id = p_party_id and status = 'A'
      and site_use_id is null;

Line: 1448

      select customer_site_use_id from iex_delinquencies_all
      where delinquency_id = p_delinquency_id;

Line: 1572

      select currency_code, nvl(min_dunning_amount,0), nvl(min_dunning_invoice_amount,0) from hz_cust_profile_amts
        where site_use_id = p_customer_site_use_id and currency_code = (
          SELECT  sob.currency_code FROM ar_system_parameters sp, gl_sets_of_books sob
          WHERE   sob.set_of_books_id = sp.set_of_books_id);

Line: 1579

      select nvl(a.amount_due_remaining, 0), a.invoice_currency_code, class
        from ar_payment_schedules_all a, iex_delinquencies_all b     --Changed to ar_payment_schedules_all for bug#5652343 by gnramasa on 07-Mar-2007
        where a.payment_schedule_id = b.payment_schedule_id
        and b.status in ('DELINQUENT', 'PREDELINQUENT')
	AND   a.class IN ('INV', 'GUAR', 'CB', 'DM', 'DEP')
        and a.status = 'OP'
        and b.customer_site_use_id = p_site_use_id;

Line: 1588

      select currency_code, nvl(min_dunning_amount,0), nvl(min_dunning_invoice_amount,0)
      from hz_cust_profile_amts
      where cust_account_id = p_cust_account_id and currency_code = (
          SELECT  sob.currency_code FROM ar_system_parameters sp, gl_sets_of_books sob
          WHERE   sob.set_of_books_id = sp.set_of_books_id);

Line: 1596

      select nvl(a.amount_due_remaining,0), a.invoice_currency_code, class
        from ar_payment_schedules_all a, iex_delinquencies_all b   --Changed to ar_payment_schedules_all for bug#5652343 by gnramasa on 07-Mar-2007
        where a.payment_schedule_id = b.payment_schedule_id
        and b.status in ('DELINQUENT', 'PREDELINQUENT')
	AND   a.class IN ('INV', 'GUAR', 'CB', 'DM', 'DEP')
        and a.status = 'OP'
        and b.cust_account_id = p_cust_account_id;

Line: 1609

	select distinct a.invoice_currency_code
        from ar_payment_schedules_all a, iex_delinquencies_all b
        where a.payment_schedule_id = b.payment_schedule_id
        and b.status in ('DELINQUENT', 'PREDELINQUENT')
	AND   a.class IN ('INV', 'GUAR', 'CB', 'DM', 'DEP')
        and a.status = 'OP'
        and b.customer_site_use_id = p_site_use_id
	order by 1;

Line: 1619

	select nvl(min_dunning_amount,0), nvl(min_dunning_invoice_amount,0)
	from hz_cust_profile_amts
	where site_use_id = p_site_use_id
	and currency_code = p_currency_code;

Line: 1625

	select sum(nvl(a.amount_due_remaining,0))
        from ar_payment_schedules_all a, iex_delinquencies_all b
        where a.payment_schedule_id = b.payment_schedule_id
        and b.status in ('DELINQUENT', 'PREDELINQUENT')
	AND   a.class IN ('INV', 'GUAR', 'CB', 'DM', 'DEP')
        and a.status = 'OP'
        and b.customer_site_use_id = p_site_use_id
        and a.invoice_currency_code = p_currency_code
        and a.amount_due_remaining >= p_min_dun_inv_amt;

Line: 1636

	select distinct a.invoice_currency_code
        from ar_payment_schedules_all a, iex_delinquencies_all b
        where a.payment_schedule_id = b.payment_schedule_id
        and b.status in ('DELINQUENT', 'PREDELINQUENT')
	AND   a.class IN ('INV', 'GUAR', 'CB', 'DM', 'DEP')
        and a.status = 'OP'
        and b.cust_account_id = p_cust_account_id
	order by 1;

Line: 1646

	select nvl(min_dunning_amount,0), nvl(min_dunning_invoice_amount,0)
	from hz_cust_profile_amts
	where cust_account_id = p_cust_account_id
	and currency_code = p_currency_code
	and site_use_id is null;

Line: 1653

	select sum(nvl(a.amount_due_remaining,0))
        from ar_payment_schedules_all a, iex_delinquencies_all b
        where a.payment_schedule_id = b.payment_schedule_id
        and b.status in ('DELINQUENT', 'PREDELINQUENT')
	AND   a.class IN ('INV', 'GUAR', 'CB', 'DM', 'DEP')
        and a.status = 'OP'
        and b.cust_account_id = p_cust_account_id
        and a.invoice_currency_code = p_currency_code
        and a.amount_due_remaining >= p_min_dun_inv_amt;

Line: 1880

	-- simple select statement
	      EXECUTE IMMEDIATE p_PopulateSql
	      INTO l_return;

Line: 1926

   select sum(amount_due_remaining) into amount_due_remaining from ar_payment_schedules_all
   where customer_trx_id = p_customer_trx_id;

Line: 1940

   select sum(amount_due_original) into amount_due_original from ar_payment_schedules_all
   where customer_trx_id = p_customer_trx_id;

Line: 1967

SELECT ac.employee_id, ac.resource_id, 0
FROM  hz_customer_profiles hp, ar_collectors ac
WHERE hp.site_use_id = p_site_use_id
  and hp.collector_id  = ac.collector_id
  and ac.resource_type = 'RS_RESOURCE'
  and trunc(nvl(ac.inactive_date,sysdate)) >= trunc(sysdate)
  and nvl(ac.status,'A') = 'A'
  and nvl(hp.status,'A') = 'A'
union all
( SELECT jtg.person_id, jtg.resource_id, count(work_item_id)
    FROM  hz_customer_profiles hp,  iex_strategy_work_items wi,
          ar_collectors ac, jtf_rs_group_members jtg
    WHERE hp.site_use_id  = p_site_use_id
      and hp.collector_id  = ac.collector_id
      and ac.resource_type = 'RS_GROUP'
      and ac.resource_id  = jtg.group_id
      and jtg.resource_id = wi.resource_id
      and wi.status_code   = 'OPEN'
      and trunc(nvl(ac.inactive_date,sysdate)) >= trunc(sysdate)
      and nvl(ac.status,'A') = 'A'
      and nvl(hp.status,'A') = 'A'
      and nvl(jtg.delete_flag,'N') = 'N'
      group by jtg.resource_id, jtg.person_id
UNION ALL
    SELECT jtg.person_id, jtg.resource_id, 0
    FROM  hz_customer_profiles hp, ar_collectors ac,
      jtf_rs_group_members jtg
    WHERE hp.site_use_id  = p_site_use_id
      and hp.collector_id   = ac.collector_id
      and ac.resource_type = 'RS_GROUP'
      and ac.resource_id = jtg.group_id
      and not exists (select null from iex_strategy_work_items wi
            where jtg.resource_id = wi.resource_id
      and wi.status_code = 'OPEN')
      and trunc(nvl(ac.inactive_date,sysdate)) >= trunc(sysdate)
      and nvl(ac.status,'A') = 'A'
      and nvl(hp.status,'A') = 'A'
      and nvl(jtg.delete_flag,'N') = 'N'
      group by jtg.resource_id, jtg.person_id
      ) order by 3;

Line: 2009

SELECT ac.employee_id, ac.resource_id, 0
FROM  hz_customer_profiles hp, ar_collectors ac
WHERE hp.cust_account_id = p_account_id
  and hp.site_use_id is null
  and hp.collector_id  = ac.collector_id
  and ac.resource_type = 'RS_RESOURCE'
  and trunc(nvl(ac.inactive_date,sysdate)) >= trunc(sysdate)
  and nvl(ac.status,'A') = 'A'
  and nvl(hp.status,'A') = 'A'
union all
( SELECT jtg.person_id, jtg.resource_id, count(work_item_id)
    FROM  hz_customer_profiles hp,  iex_strategy_work_items wi,
          ar_collectors ac, jtf_rs_group_members jtg
    WHERE hp.cust_account_id  = p_account_id
      and hp.site_use_id is NULL
      and hp.collector_id  = ac.collector_id
      and ac.resource_type = 'RS_GROUP'
      and ac.resource_id  = jtg.group_id
      and jtg.resource_id = wi.resource_id
      and wi.status_code   = 'OPEN'
      and trunc(nvl(ac.inactive_date,sysdate)) >= trunc(sysdate)
      and nvl(ac.status,'A') = 'A'
      and nvl(hp.status,'A') = 'A'
      and nvl(jtg.delete_flag,'N') = 'N'
      group by jtg.resource_id, jtg.person_id
UNION ALL
    SELECT jtg.person_id, jtg.resource_id, 0
    FROM  hz_customer_profiles hp, ar_collectors ac,
      jtf_rs_group_members jtg
    WHERE hp.cust_account_id  = p_account_id
      and hp.site_use_id is null
      and hp.collector_id   = ac.collector_id
      and ac.resource_type = 'RS_GROUP'
      and ac.resource_id = jtg.group_id
      and not exists (select null from iex_strategy_work_items wi
            where jtg.resource_id = wi.resource_id
      and wi.status_code = 'OPEN')
      and trunc(nvl(ac.inactive_date,sysdate)) >= trunc(sysdate)
      and nvl(ac.status,'A') = 'A'
      and nvl(hp.status,'A') = 'A'
      and nvl(jtg.delete_flag,'N') = 'N'
      group by jtg.resource_id, jtg.person_id
      ) order by 3;

Line: 2054

SELECT ac.employee_id, ac.resource_id, 0
FROM  hz_customer_profiles hp, ar_collectors ac
WHERE hp.party_id = p_party_id
  and hp.cust_account_id = -1
  and hp.collector_id  = ac.collector_id
  and ac.resource_type = 'RS_RESOURCE'
  and trunc(nvl(ac.inactive_date,sysdate)) >= trunc(sysdate)
  and nvl(ac.status,'A') = 'A'
  and nvl(hp.status,'A') = 'A'
union all
( SELECT jtg.person_id, jtg.resource_id, count(work_item_id)
    FROM  hz_customer_profiles hp,  iex_strategy_work_items wi,
          ar_collectors ac, jtf_rs_group_members jtg
    WHERE hp.party_id  = P_PARTY_ID
      and hp.cust_account_id = -1
      and hp.collector_id  = ac.collector_id
      and ac.resource_type = 'RS_GROUP'
      and ac.resource_id  = jtg.group_id
      and jtg.resource_id = wi.resource_id
      and wi.status_code   = 'OPEN'
      and trunc(nvl(ac.inactive_date,sysdate)) >= trunc(sysdate)
      and nvl(ac.status,'A') = 'A'
      and nvl(hp.status,'A') = 'A'
      and nvl(jtg.delete_flag,'N') = 'N'
      group by jtg.resource_id, jtg.person_id
UNION ALL
    SELECT jtg.person_id, jtg.resource_id, 0
    FROM  hz_customer_profiles hp, ar_collectors ac,
      jtf_rs_group_members jtg
    WHERE hp.party_id  = p_party_id
      and hp.cust_account_id = -1
      and hp.collector_id   = ac.collector_id
      and ac.resource_type = 'RS_GROUP'
      and ac.resource_id = jtg.group_id
      and not exists (select null from iex_strategy_work_items wi
            where jtg.resource_id = wi.resource_id
      and wi.status_code = 'OPEN')
      and trunc(nvl(ac.inactive_date,sysdate)) >= trunc(sysdate)
      and nvl(ac.status,'A') = 'A'
      and nvl(hp.status,'A') = 'A'
      and nvl(jtg.delete_flag,'N') = 'N'
      group by jtg.resource_id, jtg.person_id
      ) order by 3;

Line: 2099

    SELECT ac.employee_id, ac.resource_id, count(cas_id)
    FROM  hz_customer_profiles hp, jtf_rs_resource_extns rs, iex_cases_vl wi,ar_collectors ac
    WHERE hp.party_id = p_party_id
      and rs.resource_id = ac.resource_id
      and hp.collector_id = ac.collector_id
      and ac.resource_id = wi.owner_resource_id(+)
      and rs.user_id is not null
      and ac.employee_id is not null
      and trunc(nvl(ac.inactive_date,sysdate)) >= trunc(sysdate)
      and nvl(ac.status,'A') = 'A'
      and nvl(hp.status,'A') = 'A'
      group by ac.resource_id, ac.employee_id ORDER BY 3;

Line: 2122

  l_select1 VARCHAR2(3000);

Line: 2123

  l_select2 VARCHAR2(3000);

Line: 2124

  l_select3 VARCHAR2(3000);

Line: 2125

  l_select4 VARCHAR2(3000);

Line: 2127

  l_select5 VARCHAR2(3000);

Line: 2128

  l_select6 VARCHAR2(3000);

Line: 2181

  l_select1 :=              'SELECT ac.employee_id, ac.resource_id, 0 ';

Line: 2182

  l_select1 := l_select1 || 'FROM  hz_customer_profiles hp, ar_collectors ac,jtf_rs_resource_extns rs ';

Line: 2192

  l_select2 :=              'SELECT jtg.person_id, jtg.resource_id, count(work_item_id) ';

Line: 2193

  l_select2 := l_select2 || 'FROM hz_customer_profiles hp,  iex_strategy_work_items wi, ';

Line: 2194

  l_select2 := l_select2 || '     ar_collectors ac, jtf_rs_group_members jtg, jtf_rs_resource_extns rs ';

Line: 2195

  l_select2 := l_select2 || '     , jtf_rs_role_relations jtr,JTF_RS_ROLES_b jtrr ';

Line: 2208

  l_where2  := l_where2  || '  and nvl(jtg.delete_flag,''N'') = ''N'' ';

Line: 2209

  l_where2  := l_where2  || '  AND nvl(jtr.delete_flag,''N'') = ''N'' ';

Line: 2215

  l_select3 :=              'SELECT jtg.person_id, jtg.resource_id, 0 ';

Line: 2216

  l_select3 := l_select3 || 'FROM hz_customer_profiles hp, ar_collectors ac,  ';

Line: 2217

  l_select3 := l_select3 || '     jtf_rs_group_members jtg, jtf_rs_resource_extns rs ';

Line: 2218

  l_select3 := l_select3 || '     , jtf_rs_role_relations jtr,JTF_RS_ROLES_b jtrr ';

Line: 2226

  l_where3  := l_where3  || '  and not exists (select null from iex_strategy_work_items wi ';

Line: 2232

  l_where3  := l_where3  || ' and nvl(jtg.delete_flag,''N'') = ''N'' ';

Line: 2233

  l_where3  := l_where3  || ' and nvl(jtr.delete_flag,''N'') = ''N'' ';

Line: 2239

  l_select4 :=              'SELECT ac.employee_id, ac.resource_id, count(cas_id) ';

Line: 2242

 l_select4 := l_select4 || '  FROM  hz_customer_profiles hp, jtf_rs_resource_extns rs, iex_cases_all_b wi,ar_collectors ac ';

Line: 2258

    l_select5 :=              ' SELECT jtg.person_id, jtg.resource_id, count(t.task_id) ';

Line: 2259

  l_select5 := l_select5 || ' FROM hz_customer_profiles hp,  jtf_tasks_vl t, jtf_task_statuses_vl s, ';

Line: 2260

  l_select5 := l_select5 || '     ar_collectors ac, jtf_rs_group_members jtg, jtf_rs_resource_extns rs ';

Line: 2261

  l_select5 := l_select5 || '     , jtf_rs_role_relations jtr,JTF_RS_ROLES_b jtrr ';

Line: 2276

  l_where5  := l_where5  || '  and nvl(jtg.delete_flag,''N'') = ''N'' ';

Line: 2277

  l_where5  := l_where5  || '  and nvl(jtr.delete_flag,''N'') = ''N'' ';

Line: 2283

  l_select6 :=              'SELECT jtg.person_id, jtg.resource_id, 0 ';

Line: 2284

  l_select6 := l_select6 || ' FROM hz_customer_profiles hp, ar_collectors ac,  ';

Line: 2285

  l_select6 := l_select6 || '     jtf_rs_group_members jtg, jtf_rs_resource_extns rs ';

Line: 2286

  l_select6 := l_select6 || '     , jtf_rs_role_relations jtr,JTF_RS_ROLES_b jtrr ';

Line: 2294

  l_where6  := l_where6  || '  and not exists (select 1 from jtf_tasks_vl t, jtf_task_statuses_vl s ';

Line: 2302

  l_where6  := l_where6  || ' and nvl(jtg.delete_flag,''N'') = ''N'' ';

Line: 2303

  l_where6  := l_where6  || ' and nvl(jtr.delete_flag,''N'') = ''N'' ';

Line: 2312

     l_query := l_select1 || l_where1 || ' and hp.party_id = :1 and hp.cust_account_id = -1 ' ;

Line: 2313

     l_query := l_query || l_union || '( ' || l_select2 || l_where2 || ' and hp.party_id = :1 and hp.cust_account_id = -1 ' || l_group2;

Line: 2314

     l_query := l_query || l_union || l_select3 || l_where3 || ' and hp.party_id = :1 and hp.cust_account_id = -1 ' || l_group3 || ' )' || l_order;

Line: 2317

     l_query := l_select1 || l_where1 || ' and hp.cust_account_id = :1 and hp.site_use_id is null ' ;

Line: 2318

     l_query := l_query || l_union || '( ' || l_select2 || l_where2 || ' and hp.cust_account_id = :1 and hp.site_use_id is null ' || l_group2;

Line: 2319

     l_query := l_query || l_union || l_select3 || l_where3 || ' and hp.cust_account_id = :1 and hp.site_use_id is null ' || l_group3 || ' )' || l_order;

Line: 2322

     l_query := l_select1 || l_where1 || ' and hp.site_use_id = :1 ' ;

Line: 2323

     l_query := l_query || l_union || '( ' || l_select2 || l_where2 || ' and hp.site_use_id = :1 ' || l_group2;

Line: 2324

     l_query := l_query || l_union || l_select3 || l_where3 || ' and hp.site_use_id = :1 ' || l_group3 || ' )' || l_order;

Line: 2327

     l_query := l_select4 || l_where4 || l_group4 ;

Line: 2332

     l_query := l_select1 || l_where1 || ' and hp.party_id = :1 and hp.cust_account_id = -1 ' ;

Line: 2333

     l_query := l_query || l_union || '( ' || l_select5 || l_where5 || ' and hp.party_id = :1 and hp.cust_account_id = -1 ' || l_group5;

Line: 2334

     l_query := l_query || l_union || l_select6 || l_where6 || ' and hp.party_id = :1 and hp.cust_account_id = -1 ' || l_group6 || ' )' || l_order;

Line: 2337

     l_query := l_select1 || l_where1 || ' and hp.cust_account_id = :1 and hp.site_use_id is null ' ;

Line: 2338

     l_query := l_query || l_union || '( ' || l_select5 || l_where5 || ' and hp.cust_account_id = :1 and hp.site_use_id is null ' || l_group5;

Line: 2339

     l_query := l_query || l_union || l_select6 || l_where6 || ' and hp.cust_account_id = :1 and hp.site_use_id is null ' || l_group6 || ' )' || l_order;

Line: 2342

     l_query := l_select1 || l_where1 || ' and hp.site_use_id = :1 ' ;

Line: 2343

     l_query := l_query || l_union || '( ' || l_select5 || l_where5 || ' and hp.site_use_id = :1 ' || l_group5;

Line: 2344

     l_query := l_query || l_union || l_select6 || l_where6 || ' and hp.site_use_id = :1 ' || l_group6 || ' )' || l_order;

Line: 2347

     l_query := l_select1 || l_where1 || ' and hp.party_id = :1 and hp.cust_account_id <> -1 and hp.site_use_id is null ' ;

Line: 2348

     l_query := l_query || l_union || '( ' || l_select5 || l_where5 || ' and hp.party_id = :1 and hp.cust_account_id <> -1 and hp.site_use_id is null ' || l_group5;

Line: 2349

     l_query := l_query || l_union || l_select6 || l_where6 || ' and hp.party_id = :1 and hp.cust_account_id <> -1 and hp.site_use_id is null ' || l_group6 || ' )' || l_order;

Line: 2435

	select count(1) into l_count
	from iex_delinquencies_all del,ra_customer_trx_lines_all trl
        where del.TRANSACTION_ID = trl.CUSTOMER_TRX_ID
          and del.status not in ('CURRENT', 'CLOSE')
          and trl.INTERFACE_LINE_CONTEXT = 'OKL_CONTRACTS'
          and trl.INTERFACE_LINE_ATTRIBUTE6 = p_contract_number;

Line: 2466

      select count(1)
      into l_req_count
      from iex_xml_request_histories
      where xml_request_id=p_xml_request_id
      and length(document)>0;

Line: 2493

	p_be_cust_acct_rec.delete;