APPS.PAY_CA_RL1_MAG SQL Statements

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

Line: 88

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

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

  g_further_info_list.delete;

Line: 183

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

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

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

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

Line: 338

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

Line: 347

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

Line: 426

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

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

	            SELECT pay_assignment_actions_s.nextval
		    INTO lockingactid
		    FROM dual;

Line: 619

               /* Update the serial number column with the person id
                   */

               -- hr_utility.trace('updating asg. action');

Line: 624

               update pay_assignment_actions aa
                  set aa.serial_number = to_char(l_person_id)
                where  aa.assignment_action_id = lockingactid;

Line: 813

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

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

Line: 1251

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

  select paa.payroll_action_id, paa.assignment_id
  from pay_assignment_actions paa
  where paa.assignment_action_id = p_arch_asact_id;

Line: 1265

  select meaning
  from hr_lookups
  where
   lookup_type = 'PAY_CA_MAG_EXCEPTIONS' and
   lookup_code = p_lookup_code;

Line: 1411

   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_arch.payroll_action_id payactid,
         paa.assignment_id asgid
   FROM  pay_assignment_actions paa,
	     pay_assignment_actions paa_arch -- archiver payroll action_id niranjan

   WHERE paa.assignment_action_id = p_assg_actid
	 AND   paa_arch.assignment_action_id = to_number(substr(paa.serial_number,3,14));

Line: 1423

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

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

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

Line: 1772

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

Line: 1782

  SELECT meaning
  FROM hr_lookups
  WHERE trim(lookup_code) = p_reporting_year
        AND lookup_type = 'PAY_CA_RL1_PDF_AUTH'
        AND enabled_flag='Y';

Line: 1810

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

Line: 2078

      select trunc(sysdate) into l_session_date from dual;

Line: 2226

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

Line: 2258

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

Line: 2292

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

Line: 2325

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

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

Line: 2424

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

Line: 2459

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

Line: 2500

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

Line: 2521

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

Line: 2537

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

Line: 2557

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

Line: 2609

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

Line: 2642

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

Line: 2674

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

Line: 2708

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

Line: 2742

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

Line: 2775

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

Line: 2807

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

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

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

Line: 2999

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

Line: 3031

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

Line: 3063

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

Line: 3096

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

Line: 3126

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

Line: 3159

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

Line: 3220

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

Line: 3223

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

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

Line: 3480

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

	    /*select pay_ca_rl1_pdf_seq_s.nextval into l_sequence_number from dual;

Line: 4068

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

Line: 4207

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

Line: 4433

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

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
-- Modified for Bug 9135372
--AND	fdi.user_name like 'CAEOY_RL1_BOXO_AMOUNT_R__PER_JD_YTD'
AND	fdi.user_name like 'CAEOY_RL1_BOXO_AMOUNT____PER_JD_YTD'
and             fai.value <> '0'
ORDER BY substr(fdi.user_name,5,4);

Line: 4453

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

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

Line: 4608

/*    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;*/--niranjan

Line: 4611

    SELECT payroll_action_id
    FROM pay_assignment_actions paa
    WHERE paa.assignment_action_id = p_assact_id;

Line: 4639

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

Line: 4793

   SELECT  report_type
   FROM    pay_payroll_actions
   WHERE   payroll_action_id = p_pactid;

Line: 4807

        pay_ca_payroll_utils.delete_actionid(p_pactid);