APPS.PAY_CA_DIRECT_DEPOSIT_PKG SQL Statements

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

Line: 25

  select 1
  from   pay_ca_file_creation_numbers
  where  originator_id = p_originator_id;

Line: 33

  select information_value
  from   pay_ca_legislation_info
  where  information_type = 'CA_DD_FCN_ROWS'
  and    lookup_code = p_fin_institution;

Line: 42

  select count(*)
  from   pay_ca_file_creation_numbers
  where  originator_id = p_originator_id;

Line: 49

  select max(sequence_number)
  from   pay_ca_file_creation_numbers
  where  originator_id = l_orig_id;

Line: 57

select max(sequence_number)
from   pay_ca_file_creation_numbers
where originator_id        = l_orig_id
and file_creation_number =
    translate(file_creation_number,
      'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz?><~!@#$%^*()_-+=/\|}{][":;.,`''',

Line: 68

  select information_value
  from   pay_ca_legislation_info
  where  information_type = 'CA_DD_TEST'
  and    lookup_code = p_fin_institution;

Line: 76

  select fnd_number.canonical_to_number(file_creation_number) + l_var
  from   pay_ca_file_creation_numbers
  where  originator_id = l_orig_id
  and    sequence_number = l_seq_num;

Line: 84

  select 1
  from   pay_ca_file_creation_numbers
  where  originator_id = l_orig_id
  and    file_creation_number = l_fcn;

Line: 92

  select pay_ca_file_creation_numbers_s.nextval
  from dual;

Line: 98

  select min(sequence_number)
  from   pay_ca_file_creation_numbers
  where  originator_id = p_originator_id;

Line: 106

  select 1
  from   pay_ca_file_creation_numbers
  where  originator_id = p_originator_id
  and    file_creation_number = p_override_fcn;

Line: 113

PROCEDURE insert_new_fcn(p_originator_id varchar2
                        ,p_sequence_number number
                        ,p_new_fcn varchar2) is
BEGIN
--
insert into
pay_ca_file_creation_numbers
       (originator_id ,
        sequence_number,
        file_creation_number)
values (p_originator_id,
        p_sequence_number,
        lpad(p_new_fcn,4,'0'));

Line: 133

PROCEDURE delete_oldest_fcn(p_originator_id varchar2
                           ,p_sequence_number number) IS
BEGIN
--
delete from pay_ca_file_creation_numbers
where  sequence_number = p_sequence_number
and    originator_id   = p_originator_id;

Line: 215

    insert_new_fcn(p_originator_id
                  ,l_next_seq
                  ,lpad(l_new_fcn,4,'0'));

Line: 223

    select file_creation_number
    into   l_new_fcn
    from   pay_ca_file_creation_numbers
    where  sequence_number = l_max_seq
    and    originator_id = p_originator_id;

Line: 267

    ELSE  -- number of rows in FCN table is => than rows allowed, delete
          -- the row with the min sequence_number for the given Originator ID
          -- then insert the new row.
          --
      open  get_min_sequence(p_originator_id);

Line: 275

      delete_oldest_fcn(p_originator_id, l_min_seq);

Line: 363

        insert_new_fcn(p_originator_id
                       ,l_next_seq
                       ,p_override_fcn);

Line: 371

         select file_creation_number
         into   l_new_fcn
         from   pay_ca_file_creation_numbers
         where  sequence_number = l_max_seq
         and    originator_id = p_originator_id;

Line: 380

      ELSE -- Number of rows in FCN table is => than rows allowed, delete
           -- the row with the min sequence_number for the given Originator ID
           -- then insert the new row.
           --
        open  get_min_sequence(p_originator_id);

Line: 388

        delete_oldest_fcn(p_originator_id, l_min_seq);

Line: 395

        insert_new_fcn(p_originator_id
                       ,l_next_seq
                       ,p_override_fcn);

Line: 403

         select file_creation_number
         into   l_new_fcn
         from   pay_ca_file_creation_numbers
         where  sequence_number = l_max_seq
         and    originator_id = p_originator_id;

Line: 429

    insert_new_fcn(p_originator_id
                  ,l_next_seq
                  ,'0001');

Line: 437

    select file_creation_number
    into   l_new_fcn
    from   pay_ca_file_creation_numbers
    where  sequence_number = l_max_seq
    and    originator_id = p_originator_id;

Line: 484

    insert_new_fcn(p_originator_id
                  ,l_next_seq
                  ,p_override_fcn);

Line: 492

    select file_creation_number
    into   l_new_fcn
    from   pay_ca_file_creation_numbers
    where  sequence_number = l_max_seq
    and    originator_id = p_originator_id;

Line: 530

  select TO_NUMBER(information_value)
  from   pay_ca_legislation_info
  where  information_type = 'CA_DD_FCN_ROWS'
  and    lookup_code = cp_fin_institution;

Line: 541

select max(payroll_action_id)
from   pay_payroll_actions
where business_group_id = cp_bg_id
and action_type = 'M'
and org_payment_method_id = cp_org_pmt_method_id
and attribute1 is not null;

Line: 554

  select translate(attribute1,
    'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz?><~!@#$%^*()_-+=/\|}{][":;.,`''',' ') + l_var

Line: 568

  select 1
  from   pay_payroll_actions
  where  business_group_id = cp_bg_id
  and action_type = 'M'
  and org_payment_method_id = cp_org_pmt_method_id
  and attribute1 = l_fcn
  and rownum <= cp_no_fcn_rows
  order by payroll_action_id desc;

Line: 584

  select 1
  from   pay_payroll_actions
  where  business_group_id = cp_bg_id
  and    action_type = 'M'
  and    org_payment_method_id = cp_org_pmt_method_id
  and    attribute1 = p_override_fcn
  and    rownum <= cp_no_fcn_rows
  order by payroll_action_id desc;

Line: 596

  select information_value
  from   pay_ca_legislation_info
  where  information_type = 'CA_DD_TEST'
  and    lookup_code = p_fin_institution;

Line: 603

PROCEDURE insert_new_fcn(p_org_pmt_method_id number
                        ,p_new_fcn varchar2
                        ,p_pact_id number) is
BEGIN
--

update pay_payroll_actions
set attribute1 = lpad(p_new_fcn,4,'0')
where payroll_action_id = p_pact_id
and org_payment_method_id = p_org_pmt_method_id;

Line: 707

           hr_utility.trace('Final FCN Generated, before Update the l_new_fcn:'||l_new_fcn);

Line: 708

          insert_new_fcn(p_org_pmt_method_id,l_new_fcn,p_pact_id);

Line: 778

                /* Update Pay_Payroll_actions table with FCN value */

                 hr_utility.trace('l_new_fcn final:'||l_new_fcn);

Line: 781

                insert_new_fcn(p_org_payment_method_id,l_new_fcn,p_pact_id);

Line: 813

                   insert_new_fcn(p_org_payment_method_id,p_override_fcn,
                                  p_pact_id);

Line: 831

                  insert_new_fcn(p_org_payment_method_id ,l_new_fcn,p_pact_id);

Line: 853

                       /* Update Pay_Payroll_actions table with
                          FCN value */
                       insert_new_fcn(p_org_payment_method_id,l_new_fcn,
                                      p_pact_id);

Line: 863

                       insert_new_fcn(p_org_payment_method_id,p_override_fcn,
                                      p_pact_id);

Line: 884

select
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
 replace(
 replace(
replace(convert(upper(cp_input_string),'UTF8'),
           utl_raw.cast_to_varchar2(hextoraw('C380')),'A'
          ),
          utl_raw.cast_to_varchar2(hextoraw('C38A')),'E'
          ),
          utl_raw.cast_to_varchar2(hextoraw('C387')),'C'
          ),
          utl_raw.cast_to_varchar2(hextoraw('C389')),'E'
          ),
          utl_raw.cast_to_varchar2(hextoraw('C39C')),'U'
          ),
          utl_raw.cast_to_varchar2(hextoraw('C399')),'U'
          ),
          utl_raw.cast_to_varchar2(hextoraw('C39B')),'U'
          ),
          utl_raw.cast_to_varchar2(hextoraw('C394')),'O'
          ),
          utl_raw.cast_to_varchar2(hextoraw('C38F')),'I'
          ),
          utl_raw.cast_to_varchar2(hextoraw('C38E')),'I'
          ),
          utl_raw.cast_to_varchar2(hextoraw('C388')),'E'
          ),
          utl_raw.cast_to_varchar2(hextoraw('C38B')),'E'
          ),
          utl_raw.cast_to_varchar2(hextoraw('C382')),'A'
          ),
          utl_raw.cast_to_varchar2(hextoraw('C592')),'OE'
          ),
          utl_raw.cast_to_varchar2(hextoraw('C386')),'AE'
          )
from dual;