APPS.PAY_CA_RL1_CAN_MAG SQL Statements

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

Line: 36

		SELECT ppa.start_date,
			     ppa.effective_date,
		  	   ppa.business_group_id,
		  	   ppa.report_type,
		  	   ppa.legislative_parameters
		  INTO p_year_start,
	  		   p_year_end,
			     p_business_group_id,
			     p_report_type,
			     p_legislative_parameters
		  FROM  pay_payroll_actions ppa
      WHERE payroll_action_id = p_pactid;

Line: 58

select
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(convert(p_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'
          ),
          utl_raw.cast_to_varchar2(hextoraw('C3A9')),'e'
          )
from dual;

Line: 135

  SELECT
    ppa.payroll_action_id
  FROM
    pay_payroll_actions ppa
  WHERE
    ppa.business_group_id = p_bg_id AND
    ppa.report_type = decode(p_report_type,
                              'RL1_XML_MAG', 'RL1',
                              'RL1_AMEND_MAG', 'CAEOY_RL1_AMEND_PP') AND
    ppa.effective_date = p_effective_date AND
    p_transmitter_org_id =
            pay_ca_rl1_can_mag.get_parameter('PRE_ORGANIZATION_ID',
                                         ppa.legislative_parameters);

Line: 163

  SELECT
    ppa.legislative_parameters
  FROM
    pay_payroll_actions ppa
  WHERE
    ppa.payroll_action_id = p_payroll_action_id;

Line: 171

  SELECT
    ppa.report_type
  FROM
    pay_payroll_actions ppa
  WHERE
    ppa.payroll_action_id = p_pact_id;

Line: 213

    SELECT substr(l_transmitter_number,1,2)
    INTO dummy1
    FROM dual;

Line: 222

    SELECT to_number(substr(l_transmitter_number,3,6))
    INTO dummy
    FROM dual;

Line: 281

SELECT 'RZ-'||substr(fdi.user_name,23,2) code,fai.value value
FROM  	ff_database_items fdi,
	ff_archive_items fai
WHERE	fai.user_entity_id = fdi.user_entity_id
AND	fai.context1 = p_arch_assact_id
AND	fdi.user_name like 'CAEOY_RL1_BOXO_AMOUNT____PER_JD_YTD'
ORDER BY substr(fdi.user_name,5,4);

Line: 290

select substr(ltrim(rtrim(code)),1,60) code, value value
from (
select 'B-1' code,to_char(p_cpp_withheld) value from dual
where p_cpp_withheld <> 0
union
select 'G-2' code,to_char(p_cpp_taxable) value from dual
where p_cpp_taxable <> 0
union
select decode(fdi.user_name,'CAEOY_RL1_FURTHER_INFO_AMOUNT_201_AMT_PER_JD_YTD','201',
                 'CAEOY_RL1_FURTHER_INFO_AMOUNT_211_AMT_PER_JD_YTD','211',
                 'CAEOY_RL1_FURTHER_INFO_AMOUNT_235_AMT_PER_JD_YTD','235',
       substr(replace(substr(fdi.user_name,31),'_AMT_PER_JD_YTD',''),1,1)||'-'||
        substr(replace(substr(fdi.user_name,31),'_AMT_PER_JD_YTD',''),2)) code,
       fai.value value
 from  ff_database_items fdi,
       ff_archive_items fai
  where fai.user_entity_id=fdi.user_entity_id
    and fai.context1= p_arch_assact_id
    and fdi.user_name LIKE 'CAEOY_RL1_FURTHER_INFO_AMOUNT_%_AMT_PER_JD_YTD'
union
select 'G-1' code,
pai.action_information5 value
from   pay_action_information pai
where  pai.action_context_id = p_arch_assact_id
and    pai.action_information4 = 'G-1'
) order by code;

Line: 325

  g_further_info_list.delete;

Line: 390

	p_sqlstr := 'select distinct paaf.person_id
               from  per_all_assignments_f paaf,
                     pay_payroll_actions ppa,
                     pay_payroll_actions ppa1,
                     pay_assignment_actions paa
               where ppa.payroll_action_id = paa.payroll_action_id
                      and paa.assignment_id = paaf.assignment_id
                      and ppa1.payroll_action_id = :payroll_action_id
                      and ppa.payroll_action_id =
                          to_number(pay_ca_rl1_can_mag.get_parameter(''PAY_ACT'', ppa1.legislative_parameters))
                      and paaf.person_id =
                          nvl(pay_ca_rl1_can_mag.get_parameter(''PER_ID'',ppa1.legislative_parameters),paaf.person_id)
										order by paaf.person_id';

Line: 455

  select  	paaf.person_id,
      	   	paaf.assignment_id,
      	   	paa1.tax_unit_id,
      	   	paaf.effective_end_date,
      	   	paa.assignment_action_id,
            ppa2.payroll_action_id
  from
            per_all_assignments_f paaf,
            pay_payroll_actions ppa,
            pay_payroll_actions ppa1,
            pay_payroll_actions ppa2,
            pay_assignment_actions paa,
            pay_assignment_actions paa1,
            pay_action_interlocks int
  where
            ppa.payroll_action_id = paa.payroll_action_id
            and paa.assignment_id = paaf.assignment_id
            and ppa1.payroll_action_id = p_pactid
            and paaf.person_id between p_stperson and p_endperson
            and ppa.payroll_action_id =
                to_number(pay_ca_rl1_can_mag.get_parameter('PAY_ACT', ppa1.legislative_parameters))
            and ppa2.report_type in ('RL1','CAEOY_RL1_AMEND_PP')
            and int.locking_action_id = paa.assignment_action_id
            and paa1.assignment_action_id = int.locked_action_id
            and ppa2.payroll_action_id = paa1.payroll_action_id
            and ppa2.action_status = 'C'
            and paa.assignment_action_id
                not in (select paa2.assignment_action_id
                        from pay_action_interlocks pai, pay_assignment_actions paa2
                        where paa2.assignment_action_id = pai.locked_action_id
                              and paa2.payroll_action_id =
                                  to_number(pay_ca_rl1_can_mag.get_parameter('PAY_ACT', ppa1.legislative_parameters))
                       )
-- 						and paaf.effective_start_date condition added for bug 9142143
 						and paaf.effective_start_date = (select max(paaf2.effective_start_date)
                                               from per_all_assignments_f paaf2
                                               where paaf2.assignment_id = paa.assignment_id
                                               and paaf2.effective_start_date <= ppa.effective_date);

Line: 496

  select  	paaf.person_id,
      	   	paaf.assignment_id,
      	   	paa1.tax_unit_id,
      	   	paaf.effective_end_date,
      	   	paa.assignment_action_id,
            ppa2.payroll_action_id
  from
            per_all_assignments_f paaf,
            pay_payroll_actions ppa,
            pay_payroll_actions ppa1,
            pay_payroll_actions ppa2,
            pay_assignment_actions paa,
            pay_assignment_actions paa1,
            pay_action_interlocks int
  where
            ppa.payroll_action_id = paa.payroll_action_id
            and paa.assignment_id = paaf.assignment_id
            and ppa1.payroll_action_id = p_pactid
            and paaf.person_id between p_stperson and p_endperson
            and ppa.payroll_action_id =
                to_number(pay_ca_rl1_can_mag.get_parameter('PAY_ACT', ppa1.legislative_parameters))
            and ppa2.report_type in ('RL1','CAEOY_RL1_AMEND_PP')
            and int.locking_action_id = paa.assignment_action_id
            and paa1.assignment_action_id = int.locked_action_id
            and ppa2.payroll_action_id = paa1.payroll_action_id
            and ppa2.action_status = 'C'
            and exists (select 1
                        from hr_assignment_set_amendments hasa,
                             per_assignments_f paf
                        where hasa.assignment_set_id = l_asg_set_id
                              and upper(hasa.include_or_exclude) = 'I'
                              and hasa.assignment_id = paf.assignment_id
                              and paf.person_id = paaf.person_id)
           and paa.assignment_action_id
               not in (select paa2.assignment_action_id
                       from pay_action_interlocks pai, pay_assignment_actions paa2
                       where paa2.assignment_action_id = pai.locked_action_id
                             and paa2.payroll_action_id =
                                  to_number(pay_ca_rl1_can_mag.get_parameter('PAY_ACT', ppa1.legislative_parameters))
                       )
-- 						and paaf.effective_start_date condition added for bug 9142143
 						and paaf.effective_start_date = (select max(paaf2.effective_start_date)
                                               from per_all_assignments_f paaf2
                                               where paaf2.assignment_id = paa.assignment_id
                                               and paaf2.effective_start_date <= ppa.effective_date);

Line: 633

	      SELECT pay_assignment_actions_s.nextval
		    INTO lockingactid
		    FROM dual;

Line: 795

select fai.value
from   ff_archive_items fai,
       ff_database_items fdi
where  fai.user_entity_id = fdi.user_entity_id
and    fai.context1  = b_context_id
and    fdi.user_name = b_user_name;

Line: 818

    select target1.ORG_INFORMATION6 Type_of_Package
    from   hr_organization_information target1
    where  target1.organization_id  = pay_magtape_generic.get_parameter_value('TRANSMITTER_PRE')
    and    target1.org_information_context = 'Prov Reporting Est';

Line: 870

    SELECT
      fnd_global.local_chr(13) || fnd_global.local_chr(10)
    INTO EOL
    FROM dual;

Line: 907

      select max(paa1.payroll_action_id)
      into l_context1
      from pay_assignment_actions paa,
           pay_assignment_actions paa1,
           pay_action_interlocks int
      where int.locking_action_id = paa.assignment_action_id
        AND paa1.assignment_action_id = int.locked_action_id
        and paa.payroll_action_id = pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID');

Line: 1292

SELECT 'RZ-'||substr(fdi.user_name,23,2) code,to_number(fai.value) value
FROM  	ff_database_items fdi,
	ff_archive_items fai
WHERE	fai.user_entity_id = fdi.user_entity_id
AND	fai.context1 = p_arch_assact_id
AND	fdi.user_name like 'CAEOY_RL1_BOXO_AMOUNT____PER_JD_YTD'
ORDER BY substr(fdi.user_name,5,4);

Line: 1301

select substr(ltrim(rtrim(code)),1,60) code,value value
from (
select decode(fdi.user_name,'CAEOY_RL1_FURTHER_INFO_AMOUNT_201_AMT_PER_JD_YTD','201',
                 'CAEOY_RL1_FURTHER_INFO_AMOUNT_235_AMT_PER_JD_YTD','235',
       substr(replace(substr(fdi.user_name,31),'_AMT_PER_JD_YTD',''),1,1)||'-'||
        substr(replace(substr(fdi.user_name,31),'_AMT_PER_JD_YTD',''),2)) code,
       to_number(fai.value) value
 from  ff_database_items fdi,
       ff_archive_items fai
  where fai.user_entity_id=fdi.user_entity_id
    and fai.context1= p_arch_assact_id
    and fdi.user_name LIKE 'CAEOY_RL1_FURTHER_INFO_AMOUNT_%_AMT_PER_JD_YTD'
union
select 'G-1' code,
to_number(pai.action_information5) value
from   pay_action_information pai
where  pai.action_context_id = p_arch_assact_id
and    pai.action_information4 = 'G-1'
) order by code;

Line: 1323

   select fai.value
   from ff_archive_items fai,
        ff_database_items fdi
   where  fai.user_entity_id  = fdi.user_entity_id
   and 	fdi.user_name = 'CAEOY_RL1_PROVINCE_OF_EMPLOYMENT'
   and fai.context1 =p_arch_asact_id;

Line: 1342

    SELECT
      pai_arch.locked_action_id,
      paa_mag.assignment_id,
      pay_magtape_generic.date_earned(ppa.effective_date,paf.assignment_id),
      fai.value,
      get_parameter('PRE_ORGANIZATION_ID',ppa_arch.legislative_parameters)
    FROM
      ff_archive_items fai,
      ff_database_items fdi,
      per_all_people_f ppf,
      per_all_assignments_f paf,
      pay_action_interlocks pai_mag,
      pay_action_interlocks pai_arch,
      pay_payroll_actions ppa,
      pay_payroll_actions ppa_arch,
      pay_assignment_actions paa_mag,
      pay_assignment_actions paa_arch
    WHERE
      paa_mag.assignment_action_id = p_mag_asg_action_id AND
      ppa.payroll_action_id = paa_mag.payroll_action_id AND
      pai_mag.locking_action_id = paa_mag.assignment_action_id AND
      pai_mag.locked_action_id = pai_arch.locking_action_id AND
      pai_arch.locked_action_id = paa_arch.assignment_action_id AND
      paf.assignment_id = paa_arch.assignment_id AND
      ppf.person_id = paf.person_id AND
      pay_magtape_generic.date_earned(ppa.effective_date,paf.assignment_id)
      between
        paf.effective_start_date and paf.effective_end_date AND
      pay_magtape_generic.date_earned(ppa.effective_date,paf.assignment_id)
      between
        ppf.effective_start_date and ppf.effective_end_date AND
      fai.context1 = pai_arch.locked_action_id AND
      fdi.user_name = 'CAEOY_RL1_PROVINCE_OF_EMPLOYMENT' AND
      fai.user_entity_id = fdi.user_entity_id AND
      paa_arch.assignment_action_id = fai.context1 AND
      ppa_arch.payroll_action_id = paa_arch.payroll_action_id
    ORDER BY
      ppf.last_name,ppf.first_name,ppf.middle_names;

Line: 1384

    SELECT
        pei_information7
    FROM
        per_people_extra_info pei
    WHERE
        to_number(pei.person_id) = p_person_id AND
        pei.information_type = 'PAY_CA_RL1_FORM_NO' AND
        to_number(pei.pei_information6) = p_pre AND
        substr(pei.pei_information5,1,4) = p_year
    ORDER BY pei_information7; /*Bug 13564765*/

Line: 1533

  SELECT
   meaning
  FROM
    hr_lookups
  WHERE
   lookup_type = 'PAY_CA_MAG_EXCEPTIONS' and
   lookup_code = p_lookup_code;

Line: 1556

    SELECT  ppa.report_type
    INTO l_rep_type
    from pay_payroll_actions ppa
    where payroll_action_id=l_payroll_actid;

Line: 1567

    SELECT
      fnd_global.local_chr(13) || fnd_global.local_chr(10)
    INTO EOL
    FROM dual;

Line: 1920

      SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
      INTO   l_formatted_box
      FROM   dual;

Line: 1952

      SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
      INTO   l_formatted_box
      FROM   dual;

Line: 1986

      SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
      INTO   l_formatted_box
      FROM   dual;

Line: 2019

      SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
      INTO   l_formatted_box
      FROM   dual;

Line: 2053

      SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
      INTO   l_formatted_box
      FROM   dual;

Line: 2086

      SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
      INTO   l_formatted_box
      FROM   dual;

Line: 2118

      SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
      INTO   l_formatted_box
      FROM   dual;

Line: 2153

      SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
      INTO   l_formatted_box
      FROM   dual;

Line: 2193

        SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
        INTO   l_formatted_box
        FROM   dual;

Line: 2211

        SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
        INTO   l_formatted_box
        FROM   dual;

Line: 2228

	SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
          INTO   l_formatted_box
          FROM   dual;

Line: 2263

      SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
      INTO   l_formatted_box
      FROM   dual;

Line: 2296

      SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
      INTO   l_formatted_box
      FROM   dual;

Line: 2328

      SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
      INTO   l_formatted_box
      FROM   dual;

Line: 2362

      SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
      INTO   l_formatted_box
      FROM   dual;

Line: 2396

      SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
      INTO   l_formatted_box
      FROM   dual;

Line: 2429

      SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
      INTO   l_formatted_box
      FROM   dual;

Line: 2461

      SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
      INTO   l_formatted_box
      FROM   dual;

Line: 2493

      SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
      INTO   l_formatted_box
      FROM   dual;

Line: 2525

      SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
      INTO   l_formatted_box
      FROM   dual;

Line: 2559

      SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
      INTO   l_formatted_box
      FROM   dual;

Line: 2621

      SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
      INTO   l_formatted_box
      FROM   dual;

Line: 2653

      SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
      INTO   l_formatted_box
      FROM   dual;

Line: 2685

      SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
      INTO   l_formatted_box
      FROM   dual;

Line: 2717

      SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
      INTO   l_formatted_box
      FROM   dual;

Line: 2749

      SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
      INTO   l_formatted_box
      FROM   dual;

Line: 2778

      SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
      INTO   l_formatted_box
      FROM   dual;

Line: 2811

      SELECT ltrim(rtrim(to_char(to_number(l_box),l_format_mask)))
      INTO   l_formatted_box
      FROM   dual;

Line: 2866

       SELECT ltrim(rtrim(to_char(to_number(l_further_amount(l_i)),l_format_mask)))
       INTO   l_formatted_box
       FROM   dual;

Line: 2869

       select decode(l_further_code(l_i),'201','F_1','211','F_2','235','F_3',replace(l_further_code(l_i),'-','_'))
       into l_formatted_code
       from dual;

Line: 2956

         SELECT ltrim(rtrim(to_char(to_number(l_further_amount(l_i)),l_format_mask)))
         INTO   l_formatted_box
         FROM   dual;

Line: 3089

    SELECT
      fnd_global.local_chr(13) || fnd_global.local_chr(10)
    INTO EOL
    FROM dual;

Line: 3115

      select max(paa1.payroll_action_id)
      into l_context1
      from pay_assignment_actions paa,
           pay_assignment_actions paa1,
           pay_action_interlocks int
      where int.locking_action_id = paa.assignment_action_id
        AND paa1.assignment_action_id = int.locked_action_id
        and paa.payroll_action_id = pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID');