APPS.PAY_CA_RL1_MAG SQL Statements

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

Line: 35

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

Line: 55

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

  SELECT
    ppa.payroll_action_id
  FROM
    pay_payroll_actions ppa
  WHERE
    ppa.business_group_id = p_bg_id AND
    ppa.report_type = 'RL1' AND
    ppa.report_qualifier = 'CAEOYRL1' AND
    ppa.report_category = 'CAEOYRL1' AND
    ppa.effective_date = p_effective_date AND
    p_transmitter_org_id =
            pay_ca_rl1_mag.get_parameter('PRE_ORGANIZATION_ID',
                                         ppa.legislative_parameters);

Line: 158

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

Line: 190

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

Line: 199

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

Line: 278

	p_sqlstr := 'select distinct to_number(fai1.value)
        from    ff_archive_items fai1,
    		ff_database_items fdi1,
    		ff_archive_items fai2,
    		ff_database_items fdi2,
    		pay_assignment_actions  paa,
    		pay_payroll_actions     ppa,
    		pay_payroll_actions     ppa1,
                hr_organization_information hoi
      	where  ppa1.payroll_action_id    = :payroll_action_id
      	and    ppa.business_group_id+0 = ppa1.business_group_id
      	and    ppa.effective_date = ppa1.effective_date
      	and    ppa.report_type = ''RL1''
      	and    ppa.payroll_action_id = paa.payroll_action_id
      	and    fdi2.user_name = ''CAEOY_TAXATION_YEAR''
      	and    fai2.user_entity_id = fdi2.user_entity_id
      	and    fai2.value = pay_ca_rl1_mag.get_parameter(''REPORTING_YEAR'',ppa1.legislative_parameters)
      	and    paa.payroll_action_id= fai2.context1
      	and    paa.action_status = ''C''
      	and    paa.assignment_action_id = fai1.context1
      	and    fai1.user_entity_id = fdi1.user_entity_id
      	and    fdi1.user_name = ''CAEOY_PERSON_ID''
        and    decode(hoi.org_information3, ''Y'', hoi.organization_id, hoi.org_information20) =
               pay_ca_rl1_mag.get_parameter(''TRANSMITTER_PRE'', ppa1.legislative_parameters )
        and    hoi.org_information_context =''Prov Reporting Est''
        and    hoi.organization_id = pay_ca_rl1_mag.get_parameter(''PRE_ORGANIZATION_ID'', ppa.legislative_parameters )
	order by to_number(fai1.value)'  ;

Line: 338

    	SELECT 	paf.person_id,
      	   	paf.assignment_id,
      	   	paa.tax_unit_id,
      	   	paf.effective_end_date,
      	   	paa.assignment_action_id,
                ppa.payroll_action_id      -- Added by ssmukher for Bug 3353115
    	FROM 	pay_payroll_actions ppa,
	        pay_assignment_actions paa,
	        per_all_assignments_f paf,
      		pay_payroll_actions ppa1,
                hr_organization_information hoi
	WHERE ppa1.payroll_action_id = p_pactid
	  AND ppa.report_type = 'RL1'
	  AND ppa.business_group_id+0 = ppa1.business_group_id
	  AND ppa.effective_date = ppa1.effective_date
	  AND paa.payroll_action_id = ppa.payroll_action_id
	  AND paa.action_status = 'C'
	  AND paf.assignment_id = paa.assignment_id
	  AND paf.person_id BETWEEN p_stperson AND p_endperson
--	  AND paf.assignment_type = 'E'
	  AND paf.effective_start_date <= ppa.effective_date
	  AND paf.effective_end_date >= ppa.start_date
          and decode(hoi.org_information3, 'Y', hoi.organization_id, hoi.org_information20) =
              substr(ppa1.legislative_parameters,
                     instr(ppa1.legislative_parameters,'TRANSMITTER_PRE=')+16)
          and hoi.org_information_context = 'Prov Reporting Est'
          and hoi.organization_id =
              substr(ppa.legislative_parameters,
                     instr(ppa.legislative_parameters,'PRE_ORGANIZATION_ID=')+20)
          and paf.effective_end_date = (select max(paf1.effective_end_date)
                                        from per_all_assignments_f paf1
                                        where paf1.assignment_id = paf.assignment_id
                                        and paf1.effective_start_date <= ppa1.effective_date);

Line: 459

	            SELECT pay_assignment_actions_s.nextval
		    INTO lockingactid
		    FROM dual;

Line: 615

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

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

Line: 1036

   SELECT
         to_number(substr(paa.serial_number,3,14)) asgactid , --archiver assignment action id
         to_number(substr(paa.serial_number,17,14)) payactid, --archiver payroll action id
         paa.assignment_id asgid
   FROM
         pay_assignment_actions paa
   WHERE paa.assignment_action_id = p_assg_actid;

Line: 1045

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

    SELECT
      pai.locked_action_id,  -- Archiver asg_action_id
      paa.assignment_id,
      pay_magtape_generic.date_earned(ppa.effective_date,paa.assignment_id),
         -- date_earned
      fai.value -- Jurisdiction
    FROM
      ff_archive_items fai,
      ff_database_items fdi,
      per_all_people_f ppf,
      per_all_assignments_f paf,
      pay_action_interlocks pai,
      pay_assignment_actions paa,
      pay_payroll_actions ppa,
      pay_assignment_actions paa_arch
    WHERE
      paa.assignment_action_id = p_mag_asg_action_id AND
      ppa.payroll_action_id = paa.payroll_action_id AND
      pai.locking_action_id = paa.assignment_action_id AND
      paf.assignment_id = paa.assignment_id AND
      ppf.person_id = paf.person_id AND
      pay_magtape_generic.date_earned(ppa.effective_date,paa.assignment_id)
      between
        paf.effective_start_date and paf.effective_end_date AND
      pay_magtape_generic.date_earned(ppa.effective_date,paa.assignment_id)
      between
        ppf.effective_start_date and ppf.effective_end_date AND
      fai.context1 = pai.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
    --paa_arch.payroll_action_id =
    -- to_number(pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')) AND
      paa_arch.assignment_action_id = pai.locked_action_id
    ORDER BY
      ppf.last_name,ppf.first_name,ppf.middle_names;

Line: 1228

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

Line: 1263

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

Line: 1276

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

Line: 1518

      select trunc(sysdate) into l_session_date from dual;

Line: 1648

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

Line: 1680

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

Line: 1714

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

Line: 1747

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

Line: 1781

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

Line: 1814

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

Line: 1846

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

Line: 1881

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

Line: 1919

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

Line: 1937

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

Line: 1966

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

Line: 2001

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

Line: 2034

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

Line: 2066

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

Line: 2100

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

Line: 2134

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

Line: 2167

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

Line: 2199

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

Line: 2231

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

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

Line: 2359

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

Line: 2391

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

Line: 2423

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

Line: 2455

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

Line: 2486

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

Line: 2531

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

Line: 2553

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

Line: 2575

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

Line: 2597

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

Line: 2619

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

Line: 2641

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

Line: 2663

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

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

Line: 2729

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

Line: 2751

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

Line: 2773

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

Line: 2795

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

Line: 2817

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

Line: 2839

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

Line: 2861

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

Line: 2883

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

Line: 2905

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

Line: 2927

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

Line: 2949

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

Line: 2971

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

Line: 2993

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

Line: 3015

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

Line: 3037

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

Line: 3059

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

Line: 3081

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

Line: 3103

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

Line: 3125

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

Line: 3147

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

Line: 3169

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

Line: 3191

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

Line: 3213

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

Line: 3237

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

Line: 3258

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

Line: 3280

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

Line: 3302

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

Line: 3324

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

Line: 3346

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

Line: 3368

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

Line: 3390

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

Line: 3412

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

Line: 3434

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

Line: 3456

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

Line: 3478

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

Line: 3790

	    select pay_ca_rl1_pdf_seq_s.nextval into l_sequence_number from dual;

Line: 4170

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

Line: 4396

     select decode(correspondence_language,NULL,'US',correspondence_language)
     from per_all_people_f
     where person_id = to_number(pay_ca_archive_utils.get_archive_value(
                        p_arch_assact_id,
                        'CAEOY_PERSON_ID'));

Line: 4402

SELECT 'BOXO-'||substr(fdi.user_name,23,2) DB_Name,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_R__PER_JD_YTD'
and             fai.value <> '0'
ORDER BY substr(fdi.user_name,5,4);

Line: 4414

select substr(ltrim(rtrim(code)),1,60) code,to_char(value,'999,999.99') value
from (

select pay_ca_rl1_reg.get_label(lookup_type,lookup_code,p_person_lang) code, p_cpp_withheld value
from    hr_lookups
where  lookup_type = 'PAY_CA_RL1_FOOTNOTES'
and      lookup_code = 'CPP'
and      p_cpp_withheld <> 0
union
select SUBSTR(fdi.user_name,11,4)||', '||pay_ca_rl1_reg.get_label(hl.lookup_type,hl.lookup_code,p_person_lang) code,
           to_number(fai.value) value
from     HR_LOOKUPS HL,
         ff_database_items fdi,
         ff_archive_items fai
where fai.user_entity_id=fdi.user_entity_id
and fai.context1= p_arch_assactid
and fdi.user_name like 'CAEOY_RL1_BOX%_AMT_PER_JD_YTD'
and fai.value <> '0'
and hl.lookup_type = 'PAY_CA_RL1_FOOTNOTES'
AND HL.LOOKUP_CODE = SUBSTR(replace(FDI.USER_NAME,'_AMT_PER_JD_YTD'),-2)
union all
select pay_ca_rl1_reg.get_label(hl.lookup_type,hl.lookup_code,p_person_lang) code,
to_number(pai.action_information5) value
from   pay_action_information pai
     , hr_lookups hl
where  pai.action_context_id = p_arch_assactid
and    hl.lookup_type              = 'PAY_CA_RL1_NONBOX_FOOTNOTES'
and    hl.lookup_code              = pai.action_information4
);

Line: 4457

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

Line: 4560

    SELECT to_number(substr(paa.serial_number,17,14)) payactid --archiver payroll action id
    FROM pay_assignment_actions paa
    WHERE paa.assignment_action_id = p_assact_id;

Line: 4587

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

Line: 4732

   SELECT  report_type
   FROM    pay_payroll_actions
   WHERE   payroll_action_id = p_pactid;

Line: 4746

        pay_ca_payroll_utils.delete_actionid(p_pactid);