APPS.IEX_UTILITIES SQL Statements

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

Line: 83

     select nvl(rol.manager_flag,'N') manager_role
       from jtf_rs_role_relations rel, jtf_rs_roles_b rol,jtf_rs_resource_extns ext
      where rel.role_resource_id = ext.resource_id
        and rol.role_id = rel.role_id
        and rol.active_flag = 'Y'
        and rol.role_code = 'IEX_MANAGER'
        and rol.role_type_code = 'COLLECTIONS'
        and (trunc(nvl(rel.start_date_active,sysdate)) <= trunc(sysdate) and trunc(nvl(rel.end_date_active,sysdate+1)) >= trunc(sysdate))
        and ext.user_id =  nvl(l_user_id,0);

Line: 108

Function Delete_delinquncies(p_transaction_id number) return varchar2 is
 delete_flag varchar2(1) :=  'N';

Line: 113

 Delete from iex_delinquencies_all where transaction_id = p_transaction_id;

Line: 114

delete_flag := 'Y';

Line: 116

 return delete_flag;

Line: 121

         return delete_flag;

Line: 123

End Delete_delinquncies;

Line: 165

	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: 175

	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: 188

	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: 266

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

Line: 269

    vstr4            := '     (Select ' ;

Line: 301

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

Line: 391

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

Line: 394

    vstr4            := '     (Select ' ;

Line: 424

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

Line: 508

    vstr1     := 'Select Count(LOOKUP_CODE) ';

Line: 545

              '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: 560

       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: 621

    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: 628

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

Line: 647

        '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: 741

    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: 754

    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: 786

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: 832

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

Line: 850

     '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: 938

    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: 970

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: 1005

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

Line: 1023

     '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: 1110

    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: 1122

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

Line: 1138

     '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: 1278

    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: 1305

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: 1348

      ' 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: 1437

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

Line: 1493

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: 1530

        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: 1537

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

Line: 1541

        SELECT party_id  FROM HZ_CUST_ACCOUNTS
            WHERE cust_account_id = p_cust_account_id ;

Line: 1545

      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: 1549

      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 and cust_account_id = -1;

Line: 1557

      select customer_site_use_id from iex_delinquencies_all
      where delinquency_id = p_delinquency_id;

Line: 1692

	       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', 'BR')
         and a.status = 'OP'
         and b.cust_account_id = p_cust_account_id
	       and b.org_id = p_org_id
	       order by 1;

Line: 1703

	      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', 'BR')
        and a.status = 'OP'
        and b.customer_site_use_id = p_site_use_id
	      and b.org_id = p_org_id
	      order by 1;

Line: 2191

        select nvl(sum(aps.acctd_amount_due_remaining),0)
        from ar_payment_schedules_all aps,iex_delinquencies_all dd
        where dd.payment_schedule_id = aps.payment_schedule_id
        and aps.class IN ('INV', 'GUAR', 'CB', 'DM', 'DEP', 'BR')
        and aps.status='OP'
        and (trunc(aps.due_date) + p_grace_days) <= p_correspondence_date
        and nvl(aps.amount_in_dispute,0) = decode(p_include_dis_items, 'Y', nvl(aps.amount_in_dispute,0), 0)
        and aps.customer_id=p_cust_account_id
        and aps.amount_due_remaining > 0
        and aps.org_id=p_org_id;

Line: 2205

        select nvl(sum(acctd_amount_due_remaining),0)
        from  ar_payment_schedules_all
        where class IN ('CM','PMT')
        and   status = 'OP'
        and   customer_id = p_cust_account_id
        and   org_id = p_org_id;

Line: 2254

       select nvl(sum(aps.acctd_amount_due_remaining),0)
       from ar_payment_schedules_all aps,iex_delinquencies_all dd
       where dd.payment_schedule_id = aps.payment_schedule_id
       and aps.class IN ('INV', 'GUAR', 'CB', 'DM', 'DEP', 'BR')
       and aps.status='OP'
       and (trunc(aps.due_date) + p_grace_days) <= p_corr_date
       and nvl(aps.amount_in_dispute,0) = decode(p_include_dis_items, 'Y', nvl(aps.amount_in_dispute,0), 0)
       and aps.customer_site_use_id=p_site_use_id
       and aps.amount_due_remaining > 0
       and aps.org_id=p_org_id;

Line: 2266

       select nvl(sum(acctd_amount_due_remaining),0)
       from  ar_payment_schedules_all
       where class IN ('CM','PMT')
       and   status = 'OP'
       and   customer_site_use_id = p_site_use_id
       and org_id = p_org_id;

Line: 2303

           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_all sp, gl_sets_of_books sob
           WHERE   sob.set_of_books_id = sp.set_of_books_id
	         and sp.org_id = p_org_id)
           and site_use_id is null;

Line: 2340

          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_all sp, gl_sets_of_books sob
          WHERE   sob.set_of_books_id = sp.set_of_books_id
	        and sp.org_id = p_org_id);

Line: 2374

	       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_invoice_currency_code
	       and site_use_id is null;

Line: 2408

	      select nvl(sum(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', 'BR')  -- Bills Receivables change
        and a.status = 'OP'
	      and (trunc(a.due_date) + p_grace_days) <= p_correspondence_date
	      and nvl(a.amount_in_dispute,0) = decode(p_include_dis_items, 'Y', nvl(a.amount_in_dispute,0), 0)
        and b.cust_account_id = p_cust_account_id
        and a.invoice_currency_code = p_invoice_currency_code
        and a.amount_due_remaining >= p_min_dun_invoice_amount
	      and b.org_id = p_org_id;

Line: 2423

        select nvl(amount_due_remaining,0)  from  ar_payment_schedules_all where
        class IN ('CM','PMT')
        and   status = 'OP'
        and   invoice_currency_code = p_invoice_currency_code
        and   customer_id = p_cust_account_id
        and   org_id = p_org_id;

Line: 2472

	       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_invoice_currency_code;

Line: 2505

	      select nvl(sum(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', 'BR')  -- Bills Receivables change)
        and a.status = 'OP'
        and (trunc(a.due_date) + p_grace_days) <= p_correspondence_date
	      and nvl(a.amount_in_dispute,0) = decode(p_include_dis_items, 'Y', nvl(a.amount_in_dispute,0), 0)
        and b.customer_site_use_id = p_site_use_id
        and a.invoice_currency_code = p_invoice_currency_code
        and a.amount_due_remaining >= p_min_dun_invoice_amount
	      and b.org_id = p_org_id;

Line: 2519

        select nvl(amount_due_remaining,0)
        from  ar_payment_schedules_all
        where class IN ('CM' ,'PMT')
        and status = 'OP'
        and invoice_currency_code = p_invoice_currency_code
        and CUSTOMER_SITE_USE_ID = p_site_use_id
        and org_id = p_org_id;

Line: 2568

      select nvl(a.amount_due_remaining,0), a.invoice_currency_code, class
      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', 'BR')  -- Bills Receivables change
      and a.status = 'OP'
	    and (trunc(a.due_date) + p_grace_days) <= p_correspondence_date
	    and nvl(a.amount_in_dispute,0) = decode(p_include_dis_items, 'Y', nvl(a.amount_in_dispute,0), 0)
      and b.cust_account_id = p_cust_account_id
	    and b.org_id = p_org_id;

Line: 2580

      select nvl(amount_due_remaining,0) , invoice_currency_code from  ar_payment_schedules_all
      where   class IN ('CM','PMT')
      and status = 'OP'
      and customer_id = p_cust_account_id
      and org_id = p_org_id;

Line: 2683

        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', 'BR') -- Bills Receivables change
        and a.status = 'OP'
	      and (trunc(a.due_date) + p_grace_days) <= p_correspondence_date
	      and nvl(a.amount_in_dispute,0) = decode(p_include_dis_items, 'Y', nvl(a.amount_in_dispute,0), 0)
        and b.customer_site_use_id = p_site_use_id
	      and b.org_id = p_org_id;

Line: 2695

        select nvl(amount_due_remaining,0), invoice_currency_code
        from  ar_payment_schedules_all
        where   class IN ('CM' ,'PMT')
        and status = 'OP'
        and CUSTOMER_SITE_USE_ID = p_site_use_id
        and org_id = p_org_id;

Line: 2850

      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_all sp, gl_sets_of_books sob
          WHERE   sob.set_of_books_id = sp.set_of_books_id
	  and sp.org_id = p_org_id);

Line: 2858

      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', 'BR') -- Bills Receivables change
        and a.status = 'OP'
	and (trunc(a.due_date) + p_grace_days) <= p_corr_date
	and nvl(a.amount_in_dispute,0) = decode(p_include_dis_items, 'Y', nvl(a.amount_in_dispute,0), 0)
        and b.customer_site_use_id = p_site_use_id
	and b.org_id = p_org_id;

Line: 2870

      select nvl(amount_due_remaining,0), invoice_currency_code
      from  ar_payment_schedules_all
      where   class IN ('CM' ,'PMT')
      and status = 'OP'
      and CUSTOMER_SITE_USE_ID = p_site_use_id
      and org_id = p_org_id;

Line: 2878

      select nvl(amount_due_remaining,0)
      from  ar_payment_schedules_all
      where class IN ('CM' ,'PMT')
      and status = 'OP'
      and invoice_currency_code = p_invoice_currency_code
      and CUSTOMER_SITE_USE_ID = p_site_use_id
      and org_id = p_org_id;

Line: 2890

      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_all sp, gl_sets_of_books sob
          WHERE   sob.set_of_books_id = sp.set_of_books_id
	  and sp.org_id = p_org_id)
      and site_use_id is null;

Line: 2900

      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', 'BR')  -- Bills Receivables change
        and a.status = 'OP'
	and (trunc(a.due_date) + p_grace_days) <= p_corr_date
	and nvl(a.amount_in_dispute,0) = decode(p_include_dis_items, 'Y', nvl(a.amount_in_dispute,0), 0)
        and b.cust_account_id = p_cust_account_id
	and b.org_id = p_org_id;

Line: 2916

	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', 'BR')  -- Bills Receivables change
        and a.status = 'OP'
        and b.customer_site_use_id = p_site_use_id
	and b.org_id = p_org_id
	order by 1;

Line: 2927

	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: 2933

	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', 'BR')  -- Bills Receivables change)
        and a.status = 'OP'
        and (trunc(a.due_date) + p_grace_days) <= p_corr_date
	and nvl(a.amount_in_dispute,0) = decode(p_include_dis_items, 'Y', nvl(a.amount_in_dispute,0), 0)
        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
	and b.org_id = p_org_id;

Line: 2947

	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', 'BR')  -- Bills Receivables change
        and a.status = 'OP'
        and b.cust_account_id = p_cust_account_id
	and b.org_id = p_org_id
	order by 1;

Line: 2958

	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: 2965

	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', 'BR')  -- Bills Receivables change
        and a.status = 'OP'
	and (trunc(a.due_date) + p_grace_days) <= p_corr_date
	and nvl(a.amount_in_dispute,0) = decode(p_include_dis_items, 'Y', nvl(a.amount_in_dispute,0), 0)
        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
	and b.org_id = p_org_id;

Line: 2979

      select nvl(amount_due_remaining,0) , invoice_currency_code from  ar_payment_schedules_all
      where   class IN ('CM','PMT')
      and status = 'OP'
      and customer_id = p_cust_account_id
      and org_id = p_org_id;

Line: 2986

      select nvl(amount_due_remaining,0)  from  ar_payment_schedules_all where
      class IN ('CM','PMT')
      and   status = 'OP'
      and   invoice_currency_code = p_invoice_currency_code
      and   customer_id = p_cust_account_id
      and org_id = p_org_id;

Line: 2994

       select nvl(sum(aps.acctd_amount_due_remaining),0)
       from ar_payment_schedules_all aps,iex_delinquencies_all dd
       where dd.payment_schedule_id = aps.payment_schedule_id
       and aps.class IN ('INV', 'GUAR', 'CB', 'DM', 'DEP', 'BR')  -- Bills Receivables change
       and aps.status='OP'
       and (trunc(aps.due_date) + p_grace_days) <= p_corr_date
      and nvl(aps.amount_in_dispute,0) = decode(p_include_dis_items, 'Y', nvl(aps.amount_in_dispute,0), 0)
       and aps.customer_id=p_cust_account_id
       and aps.amount_due_remaining > 0
       and aps.org_id=p_org_id;

Line: 3006

       select nvl(sum(acctd_amount_due_remaining),0)
       from  ar_payment_schedules_all
       where class IN ('CM','PMT')
       and   status = 'OP'
       and   customer_id = p_cust_account_id

       and org_id = p_org_id;

Line: 3015

       select nvl(sum(aps.acctd_amount_due_remaining),0)
       from ar_payment_schedules_all aps,iex_delinquencies_all dd
       where dd.payment_schedule_id = aps.payment_schedule_id
       and aps.class IN ('INV', 'GUAR', 'CB', 'DM', 'DEP', 'BR')  -- Bills Receivables change
       and aps.status='OP'
       and (trunc(aps.due_date) + p_grace_days) <= p_corr_date
       and nvl(aps.amount_in_dispute,0) = decode(p_include_dis_items, 'Y', nvl(aps.amount_in_dispute,0), 0)
       and aps.customer_site_use_id=p_site_use_id
       and aps.amount_due_remaining > 0
       and aps.org_id=p_org_id;

Line: 3027

       select nvl(sum(acctd_amount_due_remaining),0)
       from  ar_payment_schedules_all
       where class IN ('CM','PMT')
       and   status = 'OP'
       and   customer_site_use_id = p_site_use_id

       and org_id = p_org_id;

Line: 3555

      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_all sp, gl_sets_of_books sob
          WHERE   sob.set_of_books_id = sp.set_of_books_id
	  and sp.org_id = p_org_id );

Line: 3563

      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', 'BR')  -- Bills Receivables change
        and a.status = 'OP'
        and b.customer_site_use_id = p_site_use_id
	and b.org_id=p_org_id;

Line: 3574

      select nvl(amount_due_remaining,0)
      from  ar_payment_schedules_all
      where  class IN ('CM' ,'PMT')
      and status = 'OP'
      and invoice_currency_code = p_invoice_currency_code
      and CUSTOMER_SITE_USE_ID = p_site_use_id
      and org_id = p_org_id;

Line: 3583

      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_all sp, gl_sets_of_books sob
          WHERE   sob.set_of_books_id = sp.set_of_books_id
	  and sp.org_id = p_org_id) and site_use_id is null;

Line: 3592

      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', 'BR')  -- Bills Receivables change
        and a.status = 'OP'
        and b.cust_account_id = p_cust_account_id
	and b.org_id = p_org_id;

Line: 3606

	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', 'BR')  -- Bills Receivables change
        and a.status = 'OP'
        and b.customer_site_use_id = p_site_use_id
	and b.org_id = p_org_id
	order by 1;

Line: 3617

	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: 3623

	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', 'BR')  -- Bills Receivables change
        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
	and b.org_id = p_org_id;

Line: 3635

	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', 'BR')  -- Bills Receivables change
        and a.status = 'OP'
        and b.cust_account_id = p_cust_account_id
	and b.org_id = p_org_id
	order by 1;

Line: 3646

	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', 'BR')  -- Bills Receivables change
        and a.status = 'OP'
        and b.party_cust_id = p_party_id
	and b.org_id = p_org_id
	order by 1;

Line: 3657

	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: 3664

	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', 'BR')  -- Bills Receivables change
        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
	and b.org_id = p_org_id;

Line: 3678

	    select ag_dn_xref_id,
		   dunning_level,
		   template_id,
		   xdo_template_id,
		   fm_method,
		   upper(callback_flag) callback_flag,
		   callback_days,
		   range_of_dunning_level_from,
		   range_of_dunning_level_to,
		   min_days_between_dunning
	    from iex_ag_dn_xref
	    where dunning_plan_id = p_dunn_plan_id
	    order by AG_DN_XREF_ID;

Line: 3703

	    select nvl(sum(amount_due_remaining),0) from (
	    select arp.amount_due_remaining amount_due_remaining
	    from iex_delinquencies_all del,
		 ar_payment_schedules_all arp
	    where del.payment_schedule_id = arp.payment_schedule_id
	    and del.status in ('DELINQUENT','PREDELINQUENT')
	    and del.customer_site_use_id = p_site_use_id
	    and del.staged_dunning_level is NULL
	    and (trunc(arp.due_date) + p_min_days_bw_dun) <= p_corr_date
	    and (trunc(arp.due_date) + p_grace_days) <= p_corr_date
	    and nvl(arp.amount_in_dispute,0) = decode(p_include_dis_items, 'Y', nvl(arp.amount_in_dispute,0), 0)
	    and arp.invoice_currency_code = p_currency_code
            and arp.amount_due_remaining >= p_min_dun_inv_amt
	    and del.org_id = p_org_id
	    union all  --Added for Bug 10401991 03-Feb-2011 barathsr
	    select arp.amount_due_remaining amount_due_remaining
	    from iex_delinquencies_all del,
		 ar_payment_schedules_all arp
	    where del.payment_schedule_id = arp.payment_schedule_id
	    and del.status = 'CURRENT'
	    and del.customer_site_use_id = p_site_use_id
	    and del.staged_dunning_level is NULL
	    and arp.status = 'OP'
	    -- and arp.class = 'INV'
      and (arp.class = 'INV' or arp.class = 'BR') -- Bills Receivables change
	    and (trunc(arp.due_date) + p_min_days_bw_dun) <= p_corr_date
	    and (trunc(arp.due_date) + p_grace_days) <= p_corr_date
	    and arp.amount_in_dispute >= decode(p_include_dis_items, 'Y', arp.amount_due_remaining, (arp.amount_due_original + 1))
	    and arp.invoice_currency_code = p_currency_code
            and arp.amount_due_remaining >= p_min_dun_inv_amt
	    and del.org_id = p_org_id
	    );

Line: 3739

	    select nvl(sum(amount_due_remaining),0) from (
	    select arp.amount_due_remaining amount_due_remaining
	    from iex_delinquencies_all del
		 ,ar_payment_schedules_all arp
	    where
	    del.payment_schedule_id = arp.payment_schedule_id and
	    del.status in ('DELINQUENT','PREDELINQUENT')
	    and del.customer_site_use_id = p_site_use_id
	    and del.staged_dunning_level = p_stage_no
	    and nvl(arp.amount_in_dispute,0) = decode(p_include_dis_items, 'Y', nvl(arp.amount_in_dispute,0), 0)
	    and nvl(((select trunc(correspondence_date) from iex_dunnings
			 where dunning_id =
			    (select max(iet.DUNNING_ID) from iex_dunning_transactions iet,
			                                         iex_dunnings dunn
			     where iet.PAYMENT_SCHEDULE_ID = del.payment_schedule_id
			     and dunn.dunning_id = iet.dunning_id
			     and ((dunn.dunning_mode = 'DRAFT' and dunn.confirmation_mode = 'CONFIRMED')
						OR (dunn.dunning_mode = 'FINAL'))
			     and iet.STAGE_NUMBER = p_stage_no
			     and dunn.delivery_status is null
			  --   group by iet.dunning_id  bug 9508149
			    )
			 )
		       + p_min_days_bw_dun
		      )
		     , p_corr_date
		    )
		    <= p_corr_date
	    and arp.invoice_currency_code = p_currency_code
            and arp.amount_due_remaining >= p_min_dun_inv_amt
	    and del.org_id = p_org_id
	    union all  --Added for Bug 10401991 03-Feb-2011 barathsr
	    select arp.amount_due_remaining amount_due_remaining
	    from iex_delinquencies_all del
		 ,ar_payment_schedules_all arp
	    where
	    del.payment_schedule_id = arp.payment_schedule_id and
	    del.status = 'CURRENT'
	    and del.customer_site_use_id = p_site_use_id
	    and del.staged_dunning_level = p_stage_no
	    and arp.status = 'OP'
	    -- and arp.class = 'INV'
      and (arp.class = 'INV' or arp.class = 'BR') -- Bills Receivables change
	    and arp.amount_in_dispute >= decode(p_include_dis_items, 'Y', arp.amount_due_remaining, (arp.amount_due_original + 1))
	    and nvl(
		(
		 (select trunc(correspondence_date) from iex_dunnings
		  where dunning_id =
		   (select max(iet.DUNNING_ID) from iex_dunning_transactions iet,
							 iex_dunnings dunn
		     where iet.PAYMENT_SCHEDULE_ID = del.payment_schedule_id
		     and dunn.dunning_id = iet.dunning_id
		     and ((dunn.dunning_mode = 'DRAFT' and dunn.confirmation_mode = 'CONFIRMED')
					OR (dunn.dunning_mode = 'FINAL'))
		     and iet.STAGE_NUMBER = p_stage_no
		     and dunn.delivery_status is null
		   --  group by iet.dunning_id  bug 9508149
		     ))
		    + p_min_days_bw_dun )
		    , p_corr_date )
		    <= p_corr_date
	     and arp.invoice_currency_code = p_currency_code
             and arp.amount_due_remaining >= p_min_dun_inv_amt
	     and del.org_id = p_org_id
	     );

Line: 3808

	    select nvl(sum(amount_due_remaining),0) from (
	    select arp.amount_due_remaining amount_due_remaining
	    from iex_delinquencies_all del,
		 ar_payment_schedules_all arp
	    where del.payment_schedule_id = arp.payment_schedule_id
	    and del.status in ('DELINQUENT','PREDELINQUENT')
	    and del.cust_account_id = p_cust_acct_id
	    and del.staged_dunning_level is NULL
	    and (trunc(arp.due_date) + p_min_days_bw_dun) <= p_corr_date
	    and (trunc(arp.due_date) + p_grace_days) <= p_corr_date
	    and nvl(arp.amount_in_dispute,0) = decode(p_include_dis_items, 'Y', nvl(arp.amount_in_dispute,0), 0)
	    and arp.invoice_currency_code = p_currency_code
            and arp.amount_due_remaining >= p_min_dun_inv_amt
	    and del.org_id = p_org_id
	    union all    --Added for Bug 10401991 03-Feb-2011 barathsr
	    select arp.amount_due_remaining amount_due_remaining
	    from iex_delinquencies_all del,
		 ar_payment_schedules_all arp
	    where del.payment_schedule_id = arp.payment_schedule_id
	    and del.status = 'CURRENT'
	    and del.cust_account_id = p_cust_acct_id
	    and del.staged_dunning_level is NULL
	    and arp.status = 'OP'
	    -- and arp.class = 'INV'
      and (arp.class = 'INV' or arp.class = 'BR') -- Bills Receivables change
	    and (trunc(arp.due_date) + p_min_days_bw_dun) <= p_corr_date
	    and (trunc(arp.due_date) + p_grace_days) <= p_corr_date
	    and arp.amount_in_dispute >= decode(p_include_dis_items, 'Y', arp.amount_due_remaining, (arp.amount_due_original + 1))
	    and arp.invoice_currency_code = p_currency_code
            and arp.amount_due_remaining >= p_min_dun_inv_amt
	    and del.org_id = p_org_id
	    );

Line: 3844

	    select nvl(sum(amount_due_remaining),0) from (
	    select arp.amount_due_remaining amount_due_remaining
	    from iex_delinquencies_all del
		 ,ar_payment_schedules_all arp
	    where
	    del.payment_schedule_id = arp.payment_schedule_id and
	    del.status in ('DELINQUENT','PREDELINQUENT')
	    and del.cust_account_id = p_cust_acct_id
	    and del.staged_dunning_level = p_stage_no
	    and nvl(arp.amount_in_dispute,0) = decode(p_include_dis_items, 'Y', nvl(arp.amount_in_dispute,0), 0)
	    and nvl(
		     (
			(select trunc(correspondence_date) from iex_dunnings
			 where dunning_id =
			    (select max(iet.DUNNING_ID) from iex_dunning_transactions iet,
			                                         iex_dunnings dunn
			     where iet.PAYMENT_SCHEDULE_ID = del.payment_schedule_id
			     and dunn.dunning_id = iet.dunning_id
			     and ((dunn.dunning_mode = 'DRAFT' and dunn.confirmation_mode = 'CONFIRMED')
						OR (dunn.dunning_mode = 'FINAL'))
			     and iet.STAGE_NUMBER = p_stage_no
			     and dunn.delivery_status is null
			--     group by iet.dunning_id  bug 9508149
			    )
			 )
		       + p_min_days_bw_dun
		      )
		     , p_corr_date
		    )
		    <= p_corr_date
	    and arp.invoice_currency_code = p_currency_code
            and arp.amount_due_remaining >= p_min_dun_inv_amt
	    and del.org_id = p_org_id
	    union all   --Added for Bug 10401991 03-Feb-2011 barathsr
	    select arp.amount_due_remaining amount_due_remaining
	    from iex_delinquencies_all del
		 ,ar_payment_schedules_all arp
	    where
	    del.payment_schedule_id = arp.payment_schedule_id and
	    del.status = 'CURRENT'
	    and del.cust_account_id = p_cust_acct_id
	    and del.staged_dunning_level = p_stage_no
	    and arp.status = 'OP'
	    -- and arp.class = 'INV'
      and (arp.class = 'INV' or arp.class = 'BR') -- Bills Receivables change
	    and arp.amount_in_dispute >= decode(p_include_dis_items, 'Y', arp.amount_due_remaining, (arp.amount_due_original + 1))
	    and nvl(
		(
		 (select trunc(correspondence_date) from iex_dunnings
		  where dunning_id =
		   (select max(iet.DUNNING_ID) from iex_dunning_transactions iet,
							 iex_dunnings dunn
		     where iet.PAYMENT_SCHEDULE_ID = del.payment_schedule_id
		     and dunn.dunning_id = iet.dunning_id
		     and ((dunn.dunning_mode = 'DRAFT' and dunn.confirmation_mode = 'CONFIRMED')
					OR (dunn.dunning_mode = 'FINAL'))
		     and iet.STAGE_NUMBER = p_stage_no
		     and dunn.delivery_status is null
		   --  group by iet.dunning_id  bug 9508149
		     ))
		    + p_min_days_bw_dun )
		    , p_corr_date )
		    <= p_corr_date
	     and arp.invoice_currency_code = p_currency_code
             and arp.amount_due_remaining >= p_min_dun_inv_amt
	     and del.org_id = p_org_id
	     );

Line: 3915

	    select nvl(sum(amount_due_remaining),0) from (
	    select arp.amount_due_remaining amount_due_remaining
	    from iex_delinquencies_all del,
		 ar_payment_schedules_all arp
	    where del.payment_schedule_id = arp.payment_schedule_id
	    and del.status in ('DELINQUENT','PREDELINQUENT')
	    and del.party_cust_id = p_party_id
	    and del.staged_dunning_level is NULL
	    and (trunc(arp.due_date) + p_min_days_bw_dun) <= p_corr_date
	    and (trunc(arp.due_date) + p_grace_days) <= p_corr_date
	    and nvl(arp.amount_in_dispute,0) = decode(p_include_dis_items, 'Y', nvl(arp.amount_in_dispute,0), 0)
	    and arp.invoice_currency_code = p_currency_code
            and arp.amount_due_remaining >= p_min_dun_inv_amt
	    and del.org_id = p_org_id
	    union all   --Added for Bug 10401991 03-Feb-2011 barathsr
	    select arp.amount_due_remaining amount_due_remaining
	    from iex_delinquencies_all del,
		 ar_payment_schedules_all arp
	    where del.payment_schedule_id = arp.payment_schedule_id
	    and del.status = 'CURRENT'
	    and del.party_cust_id = p_party_id
	    and del.staged_dunning_level is NULL
	    and arp.status = 'OP'
	    -- and arp.class = 'INV'
      and (arp.class = 'INV' or arp.class = 'BR') -- Bills Receivables change
	    and (trunc(arp.due_date) + p_min_days_bw_dun) <= p_corr_date
	    and (trunc(arp.due_date) + p_grace_days) <= p_corr_date
	    and arp.amount_in_dispute >= decode(p_include_dis_items, 'Y', arp.amount_due_remaining, (arp.amount_due_original + 1))
	    and arp.invoice_currency_code = p_currency_code
            and arp.amount_due_remaining >= p_min_dun_inv_amt
	    and del.org_id = p_org_id
	    );

Line: 3951

	    select nvl(sum(amount_due_remaining),0) from (
	    select arp.amount_due_remaining amount_due_remaining
	    from iex_delinquencies_all del
		 ,ar_payment_schedules_all arp
	    where
	    del.payment_schedule_id = arp.payment_schedule_id and
	    del.status in ('DELINQUENT','PREDELINQUENT')
	    and del.party_cust_id = p_party_id
	    and del.staged_dunning_level = p_stage_no
	    and nvl(arp.amount_in_dispute,0) = decode(p_include_dis_items, 'Y', nvl(arp.amount_in_dispute,0), 0)
	    and nvl(
		     (
			(select trunc(correspondence_date) from iex_dunnings
			 where dunning_id =
			    (select max(iet.DUNNING_ID) from iex_dunning_transactions iet,
			                                         iex_dunnings dunn
			     where iet.PAYMENT_SCHEDULE_ID = del.payment_schedule_id
			     and dunn.dunning_id = iet.dunning_id
			     and ((dunn.dunning_mode = 'DRAFT' and dunn.confirmation_mode = 'CONFIRMED')
						OR (dunn.dunning_mode = 'FINAL'))
			     and iet.STAGE_NUMBER = p_stage_no
			     and dunn.delivery_status is null
			  --   group by iet.dunning_id  bug 9508149
			    )
			 )
		       + p_min_days_bw_dun
		      )
		     , p_corr_date
		    )
		    <= p_corr_date
	    and arp.invoice_currency_code = p_currency_code
            and arp.amount_due_remaining >= p_min_dun_inv_amt
	    and del.org_id = p_org_id
	    union all   --Added for Bug 10401991 03-Feb-2011 barathsr
	    select arp.amount_due_remaining amount_due_remaining
	    from iex_delinquencies_all del
		 ,ar_payment_schedules_all arp
	    where
	    del.payment_schedule_id = arp.payment_schedule_id and
	    del.status = 'CURRENT'
	    and del.party_cust_id = p_party_id
	    and del.staged_dunning_level = p_stage_no
	    and arp.status = 'OP'
	    -- and arp.class = 'INV'
      and (arp.class = 'INV' or arp.class = 'BR') -- Bills Receivables change
	    and arp.amount_in_dispute >= decode(p_include_dis_items, 'Y', arp.amount_due_remaining, (arp.amount_due_original + 1))
	    and nvl(
		(
		 (select trunc(correspondence_date) from iex_dunnings
		  where dunning_id =
		   (select max(iet.DUNNING_ID) from iex_dunning_transactions iet,
							 iex_dunnings dunn
		     where iet.PAYMENT_SCHEDULE_ID = del.payment_schedule_id
		     and dunn.dunning_id = iet.dunning_id
		     and ((dunn.dunning_mode = 'DRAFT' and dunn.confirmation_mode = 'CONFIRMED')
					OR (dunn.dunning_mode = 'FINAL'))
		     and iet.STAGE_NUMBER = p_stage_no
		     and dunn.delivery_status is null
		   --  group by iet.dunning_id bug 9508149
		     ))
		    + p_min_days_bw_dun )
		    , p_corr_date )
		    <= p_corr_date
	     and arp.invoice_currency_code = p_currency_code
             and arp.amount_due_remaining >= p_min_dun_inv_amt
	     and del.org_id = p_org_id
	     );

Line: 4021

      select nvl(amount_due_remaining,0)  from  ar_payment_schedules_all  where
      class IN ('CM','PMT')
      and   status = 'OP'
      and   invoice_currency_code = p_invoice_currency_code
      and   customer_id = p_cust_account_id
      and   org_id = p_org_id;

Line: 4031

       select nvl(sum(aps.acctd_amount_due_remaining),0)
       from ar_payment_schedules_all aps,iex_delinquencies_all dd
       where dd.payment_schedule_id  = aps.payment_schedule_id
       and aps.class IN ('INV', 'GUAR', 'CB', 'DM', 'DEP', 'BR')  -- Bills Receivables change
       and aps.status='OP'
       and aps.customer_id=p_cust_account_id
       and aps.amount_due_remaining > 0
       and aps.org_id=p_org_id;

Line: 4041

       select nvl(sum(acctd_amount_due_remaining),0)
       from  ar_payment_schedules_all
       where class IN ('CM','PMT')
       and   status = 'OP'
       and   customer_id = p_cust_account_id

       and org_id = p_org_id;

Line: 4050

       select nvl(sum(aps.acctd_amount_due_remaining),0)
       from ar_payment_schedules_all aps,iex_delinquencies_all dd
       where dd.payment_schedule_id  = aps.payment_schedule_id
       and aps.class IN ('INV', 'GUAR', 'CB', 'DM', 'DEP', 'BR')   -- Bills Receivables change
       and aps.status='OP'
       and aps.customer_site_use_id=p_site_use_id
       and aps.amount_due_remaining > 0
       and aps.org_id=p_org_id;

Line: 4060

       select nvl(sum(acctd_amount_due_remaining),0)
       from  ar_payment_schedules_all
       where class IN ('CM','PMT')
       and   status = 'OP'
       and   customer_site_use_id = p_site_use_id

       and org_id = p_org_id;

Line: 4111

    l_inc_inv_curr.delete;

Line: 4140

          select site_use_code into tt_code from hz_cust_site_uses_all where site_use_id = l_site_use_id;

Line: 4599

	select max(iet.stage_number)
	into l_stage_number
	from iex_dunning_transactions iet,
	iex_dunnings dunn,
	iex_delinquencies_all del
	where iet.payment_schedule_id = del.payment_schedule_id
	and del.delinquency_id = p_delinquency_id
	and dunn.dunning_id = iet.dunning_id
	and ((dunn.dunning_mode = 'DRAFT' and dunn.confirmation_mode = 'CONFIRMED')
	      OR (dunn.dunning_mode = 'FINAL'))
	and dunn.delivery_status is null;

Line: 4645

	-- simple select statement
	      EXECUTE IMMEDIATE p_PopulateSql
	      INTO l_return;

Line: 4691

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

Line: 4705

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

Line: 4732

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: 4774

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: 4819

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: 4864

    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: 4887

  l_select1 VARCHAR2(3000);

Line: 4888

  l_select2 VARCHAR2(3000);

Line: 4889

  l_select3 VARCHAR2(3000);

Line: 4890

  l_select4 VARCHAR2(3000);

Line: 4892

  l_select5 VARCHAR2(3000);

Line: 4893

  l_select6 VARCHAR2(3000);

Line: 4946

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

Line: 4947

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

Line: 4948

  l_select1 := l_select1 || ' jtf_rs_role_relations jtr, jtf_rs_roles_b jtrr '; -- Added by bibeura for bug 12562473

Line: 4961

  l_where1  := l_where1  || '  and NVL(jtr.delete_flag,''N'') = ''N'' '; -- Added by bibeura for bug 12562473

Line: 4964

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

Line: 4965

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

Line: 4966

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

Line: 4967

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

Line: 4984

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

Line: 4985

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

Line: 4991

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

Line: 4992

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

Line: 4993

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

Line: 4994

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

Line: 5006

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

Line: 5012

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

Line: 5013

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

Line: 5019

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

Line: 5022

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

Line: 5038

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

Line: 5039

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

Line: 5040

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

Line: 5041

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

Line: 5060

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

Line: 5061

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

Line: 5067

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

Line: 5068

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

Line: 5069

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

Line: 5070

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

Line: 5082

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

Line: 5090

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

Line: 5091

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

Line: 5100

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

Line: 5101

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

Line: 5102

     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: 5105

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

Line: 5106

     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: 5107

     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: 5110

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

Line: 5111

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

Line: 5112

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

Line: 5115

     l_query := l_select4 || l_where4 || l_group4 ;

Line: 5120

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

Line: 5121

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

Line: 5122

     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: 5125

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

Line: 5126

     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: 5127

     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: 5130

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

Line: 5131

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

Line: 5132

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

Line: 5135

     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: 5136

     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: 5137

     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: 5229

  l_select1 VARCHAR2(3000);

Line: 5230

  l_select2 VARCHAR2(3000);

Line: 5265

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

Line: 5266

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

Line: 5276

  l_select2 :=              'SELECT ac.employee_id, rs.resource_id ';

Line: 5277

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

Line: 5288

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

Line: 5289

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

Line: 5292

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

Line: 5293

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

Line: 5296

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

Line: 5297

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

Line: 5300

     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: 5301

     l_query := l_query || l_union || l_select2 || l_where2 || ' and hp.party_id = :1 and hp.cust_account_id <> -1 and hp.site_use_id is null ' ;

Line: 5380

  select nvl(payment_grace_days ,0)
  from hz_customer_profiles
  where party_id = p_party_id
  and cust_account_id = -1;

Line: 5386

  select nvl(payment_grace_days ,0)
  from hz_customer_profiles
  where party_id = p_party_id
  and cust_account_id = p_cust_acct_id
  and site_use_id is null;

Line: 5393

  select nvl(payment_grace_days ,0)
  from hz_customer_profiles
  where party_id = p_party_id
  and cust_account_id = p_cust_acct_id
  and site_use_id = p_site_use_id;

Line: 5496

	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: 5527

      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: 5554

	p_be_cust_acct_rec.delete;

Line: 5597

	select nvl(decode(P_RUNNING_LEVEL,'CUSTOMER',USING_CUSTOMER_LEVEL,
	                                'ACCOUNT',USING_ACCOUNT_LEVEL,
					'BILL_TO',USING_BILLTO_LEVEL,
					'DELINQUENCY',USING_DELINQUENCY_LEVEL,'N'),'N')
	INTO l_return_value
	from IEX_QUESTIONNAIRE_ITEMS;

Line: 5615

	select value_varchar2 from hz_party_preferences
	where module = 'COLLECTIONS'
	and category='COLLECTIONS LEVEL'
	and preference_code='PARTY_ID'
	and party_id=p_party_id;

Line: 5622

	select preference_value
	from iex_app_preferences_b
	where preference_name='COLLECTIONS STRATEGY LEVEL'
	and org_id is not null
	and org_id=p_org_id
	and enabled_flag='Y';

Line: 5630

	select preference_value
	from iex_app_preferences_b
	where preference_name='COLLECTIONS STRATEGY LEVEL'
	and org_id is null
	and enabled_flag='Y';

Line: 5642

	select DEFINE_PARTY_RUNNING_LEVEL,
	       DEFINE_OU_RUNNING_LEVEL
	from IEX_QUESTIONNAIRE_ITEMS;

Line: 5647

	select distinct org_id
	from iex_delinquencies_all
	where status in ('DELINQUENT','PREDELINQUENT')
	and party_cust_id=p_partyid;

Line: 5746

        select site_use_code from hz_cust_site_uses where site_use_id = p_site_use_id;

Line: 5749

        select site_use_id from hz_cust_site_uses where site_use_code = 'BILL_TO'
            and cust_acct_site_id = (select cs.cust_acct_site_id from hz_cust_site_uses cs where cs.site_use_id =  p_site_use_id)
            and status = 'A';

Line: 5778

        select site_use_id from hz_cust_site_uses where site_use_code = 'DRAWEE'
            and cust_acct_site_id = (select cs.cust_acct_site_id from hz_cust_site_uses cs where cs.site_use_id =  p_site_use_id)
            and status = 'A';