APPS.PAY_SE_SALARY_STRUCTURE_STATS SQL Statements

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

Line: 124

         SELECT  (get_parameter
                                                      (legislative_parameters
                                                     , 'LEGAL_EMPLOYER'
                                                      )
                ) LEGAL_EMPLOYER_ID
              , (get_parameter
                                                      (legislative_parameters
                                                     , 'LE_REQUEST'
                                                      )
                ) LE_REQUEST
		 ,(get_parameter
                                                      (legislative_parameters
                                                     , 'MONTH'
                                                      )
		) L_MONTH
                ,(get_parameter
                                                      (legislative_parameters
                                                     , 'REPORT_YEAR'
                                                      )
                ) L_REPORT_YEAR
              , effective_date, business_group_id bg_id
           FROM pay_payroll_actions
          WHERE payroll_action_id = p_payroll_action_id;

Line: 192

        SELECT --o1.NAME local_unit_name,
        hoi2.org_information2 cfar_number
        FROM hr_organization_units o1
        , hr_organization_information hoi1
        , hr_organization_information hoi2
        WHERE o1.business_group_id = g_business_group_id
        AND hoi1.organization_id = o1.organization_id
        AND hoi1.organization_id = csr_v_local_unit_id
        AND hoi1.org_information1 = 'SE_LOCAL_UNIT'
        AND hoi1.org_information_context = 'CLASS'
        AND o1.organization_id = hoi2.organization_id
        AND hoi2.org_information_context = 'SE_LOCAL_UNIT_DETAILS';

Line: 209

	SELECT hoi2.org_information1 Worksite_Number,
	hoi2.org_information2 Association_Number,
	hl.meaning Agreement_Code,
	hoi2.org_information4 Weekend_duty_pay
	FROM hr_organization_units o1
	, hr_organization_information hoi1
	, hr_organization_information hoi2
	,hr_lookups hl
	WHERE  o1.business_group_id =csr_v_business_group_id --3133 --l_business_group_id
	AND hoi1.organization_id = o1.organization_id
	AND hoi1.organization_id =  csr_v_legal_employer_id --3134 --csr_v_legal_unit_id
	AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
	AND hoi1.org_information_context = 'CLASS'
	AND o1.organization_id =hoi2.organization_id
	AND hoi2.ORG_INFORMATION_CONTEXT='SE_SALARY_STRUCTURE'
	AND hl.lookup_type(+) ='SE_AGREEMENT_CODE'
	AND hl.LOOKUP_CODE(+)=hoi2.org_information3 ;

Line: 230

	SELECT o1.organization_id legal_employer_id
	FROM hr_organization_units o1
	, hr_organization_information hoi1
	WHERE  o1.business_group_id =csr_v_business_group_id --3133
	AND hoi1.organization_id = o1.organization_id
	AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
	AND hoi1.org_information_context = 'CLASS';

Line: 242

	SELECT o1.name legal_employer,
	hoi2.org_information2 Organization_Id,
	hoi2.org_information9 Membership_Number
	FROM hr_organization_units o1
	, hr_organization_information hoi1
	, hr_organization_information hoi2
	WHERE  o1.business_group_id =csr_v_business_group_id --3133 --l_business_group_id
	AND hoi1.organization_id = o1.organization_id
	AND hoi1.organization_id =  csr_v_legal_employer_id --3134 --csr_v_legal_unit_id
	AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
	AND hoi1.org_information_context = 'CLASS'
	AND o1.organization_id =hoi2.organization_id
	AND hoi2.ORG_INFORMATION_CONTEXT='SE_LEGAL_EMPLOYER_DETAILS';

Line: 261

	SELECT hoi2.ORG_INFORMATION1 local_unit
	FROM hr_organization_units o1
	, hr_organization_information hoi1
	, hr_organization_information hoi2
	WHERE  o1.business_group_id =csr_v_business_group_id --3133 --l_business_group_id
	AND hoi1.organization_id = o1.organization_id
	AND hoi1.organization_id =  csr_v_legal_employer_id --3134 --csr_v_legal_unit_id
	AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
	AND hoi1.org_information_context = 'CLASS'
	AND o1.organization_id =hoi2.organization_id
	AND hoi2.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNITS';

Line: 299

SELECT MEANING
FROM   hr_lookups
WHERE  LOOKUP_TYPE = 'HR_SE_SEPTEMBER'
AND  ENABLED_FLAG = 'Y'
AND  LOOKUP_CODE = csr_v_month; -- 01;

Line: 358

         '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: 387

	      -- Insert the report Parameters
	OPEN csr_legal_employer_details(g_business_group_id,g_legal_employer_id);

Line: 410

	IF g_LE_request ='LE_SELECTED' THEN
		/*Legal Emplooyer Details*/
		OPEN csr_legal_employer_details(g_business_group_id,g_legal_employer_id);

Line: 534

               'select 1 from dual where to_char(:payroll_action_id) = dummy';

Line: 549

        SELECT      ue.creator_id
	FROM     ff_user_entities  ue,
        ff_database_items di
        WHERE     di.user_name = csr_v_Balance_Name
        AND     ue.user_entity_id = di.user_entity_id
        AND     ue.legislation_code = 'SE'
        AND     ue.business_group_id is NULL
        AND     ue.creator_type = 'B';

Line: 562

	SELECT hoi2.ORG_INFORMATION1 local_unit_id
	FROM hr_organization_units o1
	, hr_organization_information hoi1
	, hr_organization_information hoi2
	WHERE  o1.business_group_id =g_business_group_id
	AND hoi1.organization_id = o1.organization_id
	AND hoi1.organization_id =  csr_v_legal_unit_id
	AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
	AND hoi1.org_information_context = 'CLASS'
	AND o1.organization_id =hoi2.organization_id
	AND hoi2.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNITS';

Line: 578

        SELECT --o1.NAME local_unit_name,
        hoi2.org_information2 cfar_number
        FROM hr_organization_units o1
        , hr_organization_information hoi1
        , hr_organization_information hoi2
        WHERE o1.business_group_id = g_business_group_id
        AND hoi1.organization_id = o1.organization_id
        AND hoi1.organization_id = csr_v_local_unit_id
        AND hoi1.org_information1 = 'SE_LOCAL_UNIT'
        AND hoi1.org_information_context = 'CLASS'
        AND o1.organization_id = hoi2.organization_id
        AND hoi2.org_information_context = 'SE_LOCAL_UNIT_DETAILS';

Line: 595

	SELECT hoi2.org_information1 Worksite_Number,
	hoi2.org_information2 Association_Number,
	hl.meaning Agreement_Code,
	hoi2.org_information4 Weekend_duty_pay
	FROM hr_organization_units o1
	, hr_organization_information hoi1
	, hr_organization_information hoi2
	,hr_lookups hl
	WHERE  o1.business_group_id =csr_v_business_group_id --3133 --l_business_group_id
	AND hoi1.organization_id = o1.organization_id
	AND hoi1.organization_id =  csr_v_legal_employer_id --3134 --csr_v_legal_unit_id
	AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
	AND hoi1.org_information_context = 'CLASS'
	AND o1.organization_id =hoi2.organization_id
	AND hoi2.ORG_INFORMATION_CONTEXT='SE_SALARY_STRUCTURE'
	AND hl.lookup_type(+) ='SE_AGREEMENT_CODE'
	AND hl.LOOKUP_CODE(+)=hoi2.org_information3 ;

Line: 616

	SELECT o1.organization_id legal_employer_id
	FROM hr_organization_units o1
	, hr_organization_information hoi1
	WHERE  o1.business_group_id =csr_v_business_group_id --3133
	AND hoi1.organization_id = o1.organization_id
	AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
	AND hoi1.org_information_context = 'CLASS';

Line: 628

	SELECT o1.name legal_employer,
	hoi2.org_information2 Organization_Id,
	hoi2.org_information9 Membership_Number
	FROM hr_organization_units o1
	, hr_organization_information hoi1
	, hr_organization_information hoi2
	WHERE  o1.business_group_id =csr_v_business_group_id --3133 --l_business_group_id
	AND hoi1.organization_id = o1.organization_id
	AND hoi1.organization_id =  csr_v_legal_employer_id --3134 --csr_v_legal_unit_id
	AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
	AND hoi1.org_information_context = 'CLASS'
	AND o1.organization_id =hoi2.organization_id
	AND hoi2.ORG_INFORMATION_CONTEXT='SE_LEGAL_EMPLOYER_DETAILS';

Line: 647

	SELECT hoi2.ORG_INFORMATION1 local_unit
	FROM hr_organization_units o1
	, hr_organization_information hoi1
	, hr_organization_information hoi2
	WHERE  o1.business_group_id =csr_v_business_group_id --3133 --l_business_group_id
	AND hoi1.organization_id = o1.organization_id
	AND hoi1.organization_id =  csr_v_legal_employer_id --3134 --csr_v_legal_unit_id
	AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
	AND hoi1.org_information_context = 'CLASS'
	AND o1.organization_id =hoi2.organization_id
	AND hoi2.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNITS';

Line: 662

	SELECT hl.meaning
        FROM per_assignment_extra_info,
        hr_lookups hl
        WHERE assignment_id = csr_v_assignment_id --32516 --p_assignment_id
        AND information_type = 'SE_SALARY_STRUCTURE'
        AND hl.LOOKUP_TYPE='SE_WORKING_HOUR_TYPE'
        AND hl.lookup_code=aei_information1;

Line: 673

	SELECT period_type
	FROM pay_payrolls_f
	WHERE payroll_id=csr_v_payroll_id;

Line: 681

	SELECT balance_type_id FRoM
	pay_balance_types pbt
	WHERE (legislation_code='SE'
	OR business_group_id=csr_v_business_group_id)
	--AND pbt.balance_name LIKE 'Steering%'
	AND pbt.BALANCE_CATEGORY_ID=(SELECT
	BALANCE_CATEGORY_ID FROM
	PAY_BALANCE_CATEGORIES_F WHERE
	legislation_code='SE'
	AND category_name=csr_v_category_name/*'001- Hours worked (September)'*/);

Line: 695

	SELECT pdb.defined_balance_id
	FROM pay_balance_types pbt,
	pay_defined_balances pdb
	WHERE pbt.balance_type_id=pdb.balance_type_id
	AND pbt.balance_type_id=csr_v_balance_type_id --10506678
	AND pdb.BALANCE_DIMENSION_ID=(SELECT
	balance_dimension_id FROM
	pay_balance_dimensions WHERE
	legislation_code='SE'
	AND DATABASE_ITEM_SUFFIX='_PER_MONTH'  );

Line: 711

	SELECT papf.person_id person_id,
	papf.national_identifier person_number,
	paaf.assignment_id,
	paaf.employee_category,
	paaf.hourly_salaried_code,
	/*nvl(substr(trim(hsck.SEGMENT3),1,4),'0000') ssyk_code,
	nvl(substr(trim(hsck.SEGMENT3),5,2),'00') association_code,*/
	hsck.SEGMENT3 ssyk_code,
	hsck.SEGMENT3 association_code,
	hsck.segment14 agreement_code,
	paaf.payroll_id
	FROM per_all_assignments_f paaf,
	per_all_people_f papf,
	hr_soft_coding_keyflex hsck
	WHERE papf.business_group_id=csr_v_business_group_id -- 3133 --paaf.assignment_id = p_assignment_id
	AND paaf.soft_coding_keyflex_id=hsck.soft_coding_keyflex_id
	AND papf.person_id=paaf.person_id
	and papf.person_id between p_start_person and p_end_person
	AND paaf.primary_flag='Y'
	AND hsck.segment2=to_char(csr_v_local_unit_id) --3268)
	AND csr_v_effective_date /*'01-jan-2006'*/ BETWEEN paaf.effective_start_date
	AND paaf.effective_end_date
	AND csr_v_effective_date /*'01-jan-2006'*/ BETWEEN papf.effective_start_date
	AND papf.effective_end_date
	AND months_between (csr_v_effective_date,DATE_OF_BIRTH) >= 216	 /* Age greater than 18 */
	AND months_between (csr_v_effective_date,DATE_OF_BIRTH) <= 780 /* Age less than and equal to 65 */
	AND papf.CURRENT_EMPLOYEE_FLAG='Y'
	AND paaf.payroll_id IS NOT NULL
	AND hsck.segment14 IS NOT NULL /* need not archive the person if he doesnt have agreement code */
	ORDER BY papf.person_id;

Line: 745

	SELECT meaning  FROM hr_lookups WHERE
	lookup_type =csr_v_lookup_type --'SE_AGREEMENT_CODE'
	AND LOOKUP_CODE=csr_v_lookup_code ;

Line: 796

l_check_insert number;

Line: 927

	IF g_LE_request ='LE_SELECTED' THEN
		FOR csr_legal_employer IN csr_local_legal_employer(g_business_group_id,g_legal_employer_id) LOOP
			l_local_unit_id:=csr_legal_employer.local_unit;

Line: 1793

         SELECT u.creator_id
           FROM ff_user_entities u, ff_database_items d
          WHERE d.user_name = p_user_name
            AND u.user_entity_id = d.user_entity_id
            AND (u.legislation_code = 'SE')
            AND (u.business_group_id IS NULL)
            AND u.creator_type = 'B';

Line: 1838

         SELECT u.creator_id
           FROM ff_user_entities u, ff_database_items d
          WHERE d.user_name = p_user_name
            AND u.user_entity_id = d.user_entity_id
            AND (u.legislation_code = 'SE')
            AND (u.business_group_id IS NULL)
            AND u.creator_type = 'B';

Line: 2101

SELECT pai.action_information6 Report_Year
FROM
pay_action_information pai,
pay_payroll_actions ppa
WHERE
ppa.payroll_action_id=csr_v_payroll_action_id--175110 --csr_v_payroll_action_id
AND ppa.payroll_action_id=pai.action_context_id
AND pai.action_context_type = 'PA'
AND pai.action_information1 = 'PYSESSSA'
AND pai.action_information_category = 'EMEA REPORT DETAILS';

Line: 2114

SELECT pai1.action_information3 legal_employer_id,
pai2.action_information3 local_unit_id,
pai3.action_information3 person_id,
pai1.action_information6 Membership_Number,
pai1.action_information7 Working_Site_Number,
pai1.action_information5 Organization_Id,
pai1.action_information8 Association_Number,
pai3.action_information9 Agreement_Code,
pai3.action_information4 Person_Number,
pai3.action_information7 Employee_Category,
pai3.action_information8 Working_Hours_Type,
pai3.action_information10 SSYK_Code,
pai3.action_information11 Association_Code,
pai3.action_information12 Salary_Type,
pai2.action_information5 CFAR_Number,
pai1.action_information10 Weekend_Pay_Duty,
decode(nvl(pai3.action_information13,0) ,0,'0000000',pai3.action_information13) Steering_Code_001,
decode(nvl(pai3.action_information14,0) ,0,'0000000',pai3.action_information14) Steering_Code_002,
decode(nvl(pai3.action_information15,0) ,0,'0000000',pai3.action_information15) Steering_Code_003,
decode(nvl(pai3.action_information16,0) ,0,'0000000',pai3.action_information16) Steering_Code_004,
decode(nvl(pai3.action_information17,0) ,0,'0000000',pai3.action_information17) Steering_Code_051,
decode(nvl(pai3.action_information18,0) ,0,'0000000',pai3.action_information18) Steering_Code_052,
decode(nvl(pai3.action_information19,0) ,0,'0000000',pai3.action_information19) Steering_Code_053,
decode(nvl(pai3.action_information20,0) ,0,'0000000',pai3.action_information20) Steering_Code_054,
decode(nvl(pai3.action_information21,0) ,0,'0000000',pai3.action_information21) Steering_Code_055,
decode(nvl(pai3.action_information22,0) ,0,'0000000',pai3.action_information22) Steering_Code_056,
decode(nvl(pai3.action_information23,0) ,0,'0000000',pai3.action_information23) Steering_Code_058,
decode(nvl(pai3.action_information24,0) ,0,'0000000',pai3.action_information24) Steering_Code_600,
decode(nvl(pai3.action_information25,0) ,0,'0000000',pai3.action_information25) Steering_Code_601,
decode(nvl(pai3.action_information26,0) ,0,'0000000',pai3.action_information26) Steering_Code_810,
decode(nvl(pai4.action_information5,0) ,0,'0000000',pai4.action_information5) Steering_Code_800,
decode(nvl(pai4.action_information6,0) ,0,'0000000',pai4.action_information6) Steering_Code_801,
decode(nvl(pai4.action_information7,0) ,0,'0000000',pai4.action_information7) Steering_Code_802,
decode(nvl(pai4.action_information8,0) ,0,'0000000',pai4.action_information8) Steering_Code_803,
decode(nvl(pai4.action_information9,0) ,0,'0000000',pai4.action_information9) Steering_Code_804,
decode(nvl(pai4.action_information10,0) ,0,'0000000',pai4.action_information10) Steering_Code_805,
decode(nvl(pai4.action_information11,0) ,0,'0000000',pai4.action_information11) Steering_Code_806,
decode(nvl(pai4.action_information12,0) ,0,'0000000',pai4.action_information12) Steering_Code_808
FROM
pay_action_information pai1,
pay_action_information pai2,
pay_action_information pai3,
pay_action_information pai4,
pay_payroll_actions ppa
WHERE
ppa.payroll_action_id=csr_v_payroll_action_id --175110 --175079 --175068 --csr_v_payroll_action_id
AND ppa.payroll_action_id=pai1.action_context_id
AND pai1.action_context_id=pai2.action_context_id
AND pai2.action_context_id=pai3.action_context_id
and pai3.action_context_id=pai4.action_context_id
and pai4.action_context_id=ppa.payroll_action_id
--AND pai1.action_information3=to_char(csr_v_local_unit_id ) --csr_v_local_unit_id
AND pai1.action_context_type='PA'
AND pai1.action_information_category = 'EMEA REPORT INFORMATION'
AND pai1.action_information1 = 'PYSESSSA'
--AND pai1.action_information_id=csr_v_action_information_id
AND pai1.action_information2='LE'
AND pai1.action_information3=pai2.action_information4
AND pai2.action_context_type='PA'
AND pai2.action_information2 = 'LU'
AND pai2.action_information1 = 'PYSESSSA'
AND pai2.action_information_category = 'EMEA REPORT INFORMATION'
AND pai2.action_information3=pai3.action_information5
AND pai3.action_context_type='PA'
AND pai3.action_information2 = 'PER1'
AND pai3.action_information1 = 'PYSESSSA'
AND pai3.action_information_category = 'EMEA REPORT INFORMATION'
AND pai2.action_information3=pai4.action_information4
AND pai3.action_information5=pai4.action_information4
AND pai3.action_information3=pai4.action_information3
/* Bug Fix 6209364 */
AND pai3.action_information9  IN (SELECT
	hl.meaning Agreement_Code
	FROM hr_organization_units o1
	, hr_organization_information hoi1
	,hr_lookups hl
	WHERE  o1.business_group_id =g_business_group_id
	AND hoi1.organization_id = o1.organization_id
	AND hoi1.organization_id =pai1.action_information3
	AND hoi1.ORG_INFORMATION_CONTEXT='SE_SALARY_STRUCTURE'
	AND hl.lookup_type ='SE_AGREEMENT_CODE'
	AND hl.LOOKUP_CODE=hoi1.org_information3 )
--=pai1.action_information9 /*linking the agreement code b/w assignment and Legal employer */
AND pai4.action_context_type='PA'
AND pai4.action_information2 = 'PER2'
AND pai4.action_information1 = 'PYSESSSA'
AND pai4.action_information_category = 'EMEA REPORT INFORMATION'
ORDER BY pai1.action_information3,
pai2.action_information3,
pai3.action_information9,
pai3.action_information3;

Line: 2208

SELECT nvl(COUNT(*),0)  Local_Unit_Count
FROM
pay_action_information pai,
pay_action_information pai1,
pay_payroll_actions ppa
WHERE
ppa.payroll_action_id=csr_v_payroll_action_id --175110 --csr_v_payroll_action_id
AND ppa.payroll_action_id=pai.action_context_id
AND pai.action_context_id=pai1.action_context_id
AND pai1.action_context_id=ppa.payroll_action_id
AND pai.action_context_type = 'PA'
AND pai.action_information1 = 'PYSESSSA'
AND pai.action_information2 = 'LU'
AND pai.action_information_category = 'EMEA REPORT INFORMATION'
AND pai.action_information5=csr_v_CFAR_Number --312
AND pai.action_information3= pai1.action_information5
AND pai1.action_context_type = 'PA'
AND pai1.action_information1 = 'PYSESSSA'
AND pai1.action_information2 = 'PER1'
AND pai1.action_information_category = 'EMEA REPORT INFORMATION';

Line: 2231

SELECT meaning FROM
hr_lookups WHERE lookup_type =csr_v_lookup_type-- 'EMPLOYEE_CATG'
AND lookup_code=csr_v_lookup_code; -- 'WC'

Line: 2237

SELECT lookup_code FROM
hr_lookups WHERE lookup_type =csr_v_lookup_type-- 'EMPLOYEE_CATG'
AND meaning=csr_v_lookup_meaning; -- 'WC'

Line: 2243

  SELECT
  hoi1.ORG_INFORMATION1 Working_Site_Number,
  hoi1.ORG_INFORMATION2 Association_Number,
  hoi1.ORG_INFORMATION4 Weekend_Pay_Duty
  FROM hr_organization_units o1
  , hr_organization_information hoi1
  ,hr_lookups hl
  WHERE  o1.business_group_id =g_business_group_id --3133  --3133 --csr_v_business_group_id --3133 --l_business_group_id
  AND hoi1.organization_id = o1.organization_id
  AND hoi1.organization_id =csr_v_legal_employer --3134 --pai1.action_information3  --csr_v_legal_employer_id --3134 --csr_v_legal_unit_id
  AND hoi1.ORG_INFORMATION_CONTEXT='SE_SALARY_STRUCTURE'
  AND hl.lookup_type ='SE_AGREEMENT_CODE'
  AND hl.MEANING=csr_v_agreement --'333'
  AND hl.lookup_code=hoi1.ORG_INFORMATION3;

Line: 2288

                SELECT payroll_action_id
                INTO  l_payroll_action_id
                FROM pay_payroll_actions ppa,
                fnd_conc_req_summary_v fcrs,
                fnd_conc_req_summary_v fcrs1
                WHERE  fcrs.request_id = fnd_global.conc_request_id
                AND fcrs.priority_request_id = fcrs1.priority_request_id
                AND ppa.request_id between fcrs1.request_id  and fcrs.request_id
                AND ppa.request_id = fcrs1.request_id;

Line: 3030

	--INSERT INTO raaj VALUES (p_xml);