APPS.PAY_NL_ANNUAL_SI_FILE SQL Statements

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

Line: 210

SELECT name from hr_organization_units
where organization_id = p_org_id;

Line: 236

SELECT soft_coding_keyflex_id FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id AND
effective_start_date =
(SELECT max(effective_start_date)
FROM per_all_assignments_f asg,per_assignment_status_types past
WHERE past.per_system_status = 'ACTIVE_ASSIGN'
AND asg.assignment_status_type_id = past.assignment_status_type_id and assignment_id = p_assignment_id);

Line: 245

SELECT segment30 from hr_soft_Coding_keyflex where soft_coding_keyflex_id = p_scl_id;

Line: 277

SELECT ff.context_id     context_id
      ,pact.context_value   context_value
      , decode(context_value,'ZFW',0,'ZW',1,
               'WEWE',2,'WEWA',3,'WAOD',4,'WAOB',5,6) seq
FROM   ff_contexts         ff
      ,pay_action_contexts pact
WHERE  ff.context_name   = p_context_name
AND    pact.context_id   = ff.context_id
AND    pact.assignment_action_id=p_assignment_action_id
ORDER  BY decode(context_value,'ZFW',0,'ZW',1,
          'WEWE',2,'WEWA',3,'WAOD',4,'WAOB',5,6);

Line: 354

   SELECT org_information8 SRepName,org_information9 SRegNo,
   org_information10 ERRepName,org_information11 ERRegNo,
   DECODE(org_information3,'ZFW',1,'ZW',2,'WW',3,'WAO',4,'AMI',5,6) sort_order
   FROM hr_organization_information
   where organization_id = p_employer_id
   and org_information_context = 'NL_SIP'
   and org_information4 = p_si_provider_id
   AND p_process_date between
   FND_DATE.CANONICAL_TO_DATE(org_information1) and
   nvl(FND_DATE.CANONICAL_TO_DATE(org_information2),hr_general.end_of_time)
   ORDER BY ORG_INFORMATION7,DECODE(org_information3,'ZFW',1,'ZW',2,'WW',3,'WAO',4,'AMI',5,6) ;

Line: 402

  SELECT fnd_date.canonical_to_date(pay_core_utils.get_parameter('TAX_YEAR',legislative_parameters))
        ,pay_core_utils.get_parameter('EMPLOYER_ID',legislative_parameters)
        ,pay_core_utils.get_parameter('ORG_HIERARCHY',legislative_parameters)
        ,pay_core_utils.get_parameter('SI_PROVIDER_ID',legislative_parameters)
        ,effective_date
        ,business_group_id
  FROM  pay_payroll_actions
  WHERE payroll_action_id = p_payroll_action_id;

Line: 433

|Description: This procedure returns a sql string to select a range of|
|		  assignments eligible for archival
----------------------------------------------------------------------*/

Procedure RANGE_CODE (pactid    IN    NUMBER
                     ,sqlstr    OUT   NOCOPY VARCHAR2) is

v_log_header   VARCHAR2(255);

Line: 506

/*Return the SELECT Statement to select a range of assignments
eligible for archival */

get_all_parameters
          (
          pactid,
          l_business_group_id,
          l_si_provider_id,
          l_effective_date,
          l_tax_year_date,
          l_employer_id,
          l_org_struct_id
          );

Line: 571

	sqlstr := 'SELECT DISTINCT person_id
	FROM  per_people_f ppf
	,pay_payroll_actions ppa
	WHERE ppa.payroll_action_id = :payroll_action_id
	AND   ppa.business_group_id = ppf.business_group_id
	ORDER BY ppf.person_id';

Line: 592

       sqlstr := 'SELECT DISTINCT person_id
       FROM  per_people_f ppf
       ,pay_payroll_actions ppa
       WHERE ppa.payroll_action_id = :payroll_action_id
       AND   1 = 2
       AND   ppa.business_group_id = ppf.business_group_id
       ORDER BY ppf.person_id';

Line: 629

Select
        paa.organization_id,
        paa.soft_coding_keyflex_id,
        pap.person_id,
        paa.assignment_id,
        pap.last_name,
        paa.assignment_number,
        pap.full_name,
        pap.Date_of_Birth,
        pap.national_identifier
from
 per_all_people_f pap
,per_all_assignments_f paa
,hr_soft_coding_keyflex scl_flx
where
pap.business_group_id =p_business_group_id
and pap.person_id = paa.person_id
and paa.person_id BETWEEN p_start_person_id AND p_end_person_id
and scl_flx.soft_coding_keyflex_id = paa.soft_coding_keyflex_id
and p_Tax_Year_End_Date between pap.effective_start_date and pap.effective_end_date
and paa.effective_start_date =
		(
		SELECT MIN(asg.effective_start_date)
		FROM per_assignment_status_types past, per_all_assignments_f asg
		WHERE asg.assignment_id = paa.assignment_id
		and   past.per_system_status = 'ACTIVE_ASSIGN'
		and   asg.assignment_status_type_id = past.assignment_status_type_id
		and   asg.effective_start_date <= p_Tax_Year_End_Date
		and   nvl(asg.effective_end_date, p_Tax_Year_End_Date) >= p_Tax_Year_Start_Date
		)
and p_employer_id in
(select hr_nl_org_info.get_tax_org_id(p_org_struct_version_id,paa.organization_id) from dual)
and not exists
(select 1
from
pay_action_information ee_ats
WHERE ee_ats.action_context_type='AAP'
AND ee_ats.action_information_category = 'NL ASI EMPLOYEE DETAILS'
AND ee_ats.action_information1  =p_employer_id
AND ee_ats.action_information4  =pap.person_id
AND ee_ats.action_information2  =paa.assignment_id
AND ee_ats.action_information3  =p_si_provider_id
AND ee_ats.effective_date          =p_tax_year_end_date)
AND
(p_si_provider_id = HR_NL_ORG_INFO.GET_SI_PROVIDER_INFO(paa.organization_id,'ZFW',paa.assignment_id)
 OR p_si_provider_id = HR_NL_ORG_INFO.GET_SI_PROVIDER_INFO(paa.organization_id,'ZW',paa.assignment_id)
 OR p_si_provider_id = HR_NL_ORG_INFO.GET_SI_PROVIDER_INFO(paa.organization_id,'WEWE',paa.assignment_id)
 OR p_si_provider_id = HR_NL_ORG_INFO.GET_SI_PROVIDER_INFO(paa.organization_id,'WEWA',paa.assignment_id)
 OR p_si_provider_id = HR_NL_ORG_INFO.GET_SI_PROVIDER_INFO(paa.organization_id,'WAOB',paa.assignment_id)
 OR p_si_provider_id = HR_NL_ORG_INFO.GET_SI_PROVIDER_INFO(paa.organization_id,'WAOD',paa.assignment_id)
);

Line: 758

	SELECT pay_assignment_actions_s.NEXTVAL
	INTO   l_asg_act_id
	FROM   dual;