190: CURSOR csr_local_unit_details (csr_v_local_unit_id hr_organization_information.organization_id%TYPE)
191: IS
192: SELECT --o1.NAME local_unit_name,
193: hoi2.org_information2 cfar_number
194: FROM hr_organization_units o1
195: , hr_organization_information hoi1
196: , hr_organization_information hoi2
197: WHERE o1.business_group_id = g_business_group_id
198: AND hoi1.organization_id = o1.organization_id
202: AND o1.organization_id = hoi2.organization_id
203: AND hoi2.org_information_context = 'SE_LOCAL_UNIT_DETAILS';
204:
205: /*Salary Structure EIT Details */
206: CURSOR csr_salary_structure_details(csr_v_business_group_id hr_organization_units.business_group_id%TYPE,
207: csr_v_legal_employer_id hr_organization_units.organization_id%TYPE)
208: IS
209: SELECT hoi2.org_information1 Worksite_Number,
210: hoi2.org_information2 Association_Number,
203: AND hoi2.org_information_context = 'SE_LOCAL_UNIT_DETAILS';
204:
205: /*Salary Structure EIT Details */
206: CURSOR csr_salary_structure_details(csr_v_business_group_id hr_organization_units.business_group_id%TYPE,
207: csr_v_legal_employer_id hr_organization_units.organization_id%TYPE)
208: IS
209: SELECT hoi2.org_information1 Worksite_Number,
210: hoi2.org_information2 Association_Number,
211: hl.meaning Agreement_Code,
209: SELECT hoi2.org_information1 Worksite_Number,
210: hoi2.org_information2 Association_Number,
211: hl.meaning Agreement_Code,
212: hoi2.org_information4 Weekend_duty_pay
213: FROM hr_organization_units o1
214: , hr_organization_information hoi1
215: , hr_organization_information hoi2
216: ,hr_lookups hl
217: WHERE o1.business_group_id =csr_v_business_group_id --3133 --l_business_group_id
224: AND hl.lookup_type(+) ='SE_AGREEMENT_CODE'
225: AND hl.LOOKUP_CODE(+)=hoi2.org_information3 ;
226:
227: /* Legal Employers under the Business Group */
228: CURSOR csr_legal_employer(csr_v_business_group_id hr_organization_units.business_group_id%TYPE)
229: IS
230: SELECT o1.organization_id legal_employer_id
231: FROM hr_organization_units o1
232: , hr_organization_information hoi1
227: /* Legal Employers under the Business Group */
228: CURSOR csr_legal_employer(csr_v_business_group_id hr_organization_units.business_group_id%TYPE)
229: IS
230: SELECT o1.organization_id legal_employer_id
231: FROM hr_organization_units o1
232: , hr_organization_information hoi1
233: WHERE o1.business_group_id =csr_v_business_group_id --3133
234: AND hoi1.organization_id = o1.organization_id
235: AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
235: AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
236: AND hoi1.org_information_context = 'CLASS';
237:
238: /* Legal Employer Details */
239: CURSOR csr_legal_employer_details(csr_v_business_group_id hr_organization_units.business_group_id%TYPE,
240: csr_v_legal_employer_id hr_organization_units.organization_id%TYPE)
241: IS
242: SELECT o1.name legal_employer,
243: hoi2.org_information2 Organization_Id,
236: AND hoi1.org_information_context = 'CLASS';
237:
238: /* Legal Employer Details */
239: CURSOR csr_legal_employer_details(csr_v_business_group_id hr_organization_units.business_group_id%TYPE,
240: csr_v_legal_employer_id hr_organization_units.organization_id%TYPE)
241: IS
242: SELECT o1.name legal_employer,
243: hoi2.org_information2 Organization_Id,
244: hoi2.org_information9 Membership_Number
241: IS
242: SELECT o1.name legal_employer,
243: hoi2.org_information2 Organization_Id,
244: hoi2.org_information9 Membership_Number
245: FROM hr_organization_units o1
246: , hr_organization_information hoi1
247: , hr_organization_information hoi2
248: WHERE o1.business_group_id =csr_v_business_group_id --3133 --l_business_group_id
249: AND hoi1.organization_id = o1.organization_id
254: AND hoi2.ORG_INFORMATION_CONTEXT='SE_LEGAL_EMPLOYER_DETAILS';
255:
256:
257: /*Local Units under the Legal Employer*/
258: CURSOR csr_local_legal_employer(csr_v_business_group_id hr_organization_units.business_group_id%TYPE,
259: csr_v_legal_employer_id hr_organization_units.organization_id%TYPE)
260: IS
261: SELECT hoi2.ORG_INFORMATION1 local_unit
262: FROM hr_organization_units o1
255:
256:
257: /*Local Units under the Legal Employer*/
258: CURSOR csr_local_legal_employer(csr_v_business_group_id hr_organization_units.business_group_id%TYPE,
259: csr_v_legal_employer_id hr_organization_units.organization_id%TYPE)
260: IS
261: SELECT hoi2.ORG_INFORMATION1 local_unit
262: FROM hr_organization_units o1
263: , hr_organization_information hoi1
258: CURSOR csr_local_legal_employer(csr_v_business_group_id hr_organization_units.business_group_id%TYPE,
259: csr_v_legal_employer_id hr_organization_units.organization_id%TYPE)
260: IS
261: SELECT hoi2.ORG_INFORMATION1 local_unit
262: FROM hr_organization_units o1
263: , hr_organization_information hoi1
264: , hr_organization_information hoi2
265: WHERE o1.business_group_id =csr_v_business_group_id --3133 --l_business_group_id
266: AND hoi1.organization_id = o1.organization_id
307: l_association_number varchar2(10);
308: l_legal_agreement_code varchar2(10);
309: l_agreement_code varchar2(10);
310: l_weekend_duty varchar2(10);
311: l_legal_employer hr_organization_units.name%TYPE;
312: l_organization_id varchar2(20);
313: l_membership_number varchar2(10);
314: L_CFAR_NUMBER NUMBER;
315: l_local_unit_id NUMBER;
312: l_organization_id varchar2(20);
313: l_membership_number varchar2(10);
314: L_CFAR_NUMBER NUMBER;
315: l_local_unit_id NUMBER;
316: l_legal_employer_id hr_organization_units.organization_id%TYPE;
317:
318: TYPE emp_cat_type
319: IS TABLE OF VARCHAR2(10)
320: INDEX BY BINARY_INTEGER;
556: AND ue.creator_type = 'B';
557:
558:
559: CURSOR csr_Local_unit_Legal(csr_v_legal_unit_id
560: hr_organization_units.organization_id%TYPE)
561: IS
562: SELECT hoi2.ORG_INFORMATION1 local_unit_id
563: FROM hr_organization_units o1
564: , hr_organization_information hoi1
559: CURSOR csr_Local_unit_Legal(csr_v_legal_unit_id
560: hr_organization_units.organization_id%TYPE)
561: IS
562: SELECT hoi2.ORG_INFORMATION1 local_unit_id
563: FROM hr_organization_units o1
564: , hr_organization_information hoi1
565: , hr_organization_information hoi2
566: WHERE o1.business_group_id =g_business_group_id
567: AND hoi1.organization_id = o1.organization_id
576: CURSOR csr_local_unit_details (csr_v_local_unit_id hr_organization_information.organization_id%TYPE)
577: IS
578: SELECT --o1.NAME local_unit_name,
579: hoi2.org_information2 cfar_number
580: FROM hr_organization_units o1
581: , hr_organization_information hoi1
582: , hr_organization_information hoi2
583: WHERE o1.business_group_id = g_business_group_id
584: AND hoi1.organization_id = o1.organization_id
588: AND o1.organization_id = hoi2.organization_id
589: AND hoi2.org_information_context = 'SE_LOCAL_UNIT_DETAILS';
590:
591: /*Salary Structure EIT Details */
592: CURSOR csr_salary_structure_details(csr_v_business_group_id hr_organization_units.business_group_id%TYPE,
593: csr_v_legal_employer_id hr_organization_units.organization_id%TYPE)
594: IS
595: SELECT hoi2.org_information1 Worksite_Number,
596: hoi2.org_information2 Association_Number,
589: AND hoi2.org_information_context = 'SE_LOCAL_UNIT_DETAILS';
590:
591: /*Salary Structure EIT Details */
592: CURSOR csr_salary_structure_details(csr_v_business_group_id hr_organization_units.business_group_id%TYPE,
593: csr_v_legal_employer_id hr_organization_units.organization_id%TYPE)
594: IS
595: SELECT hoi2.org_information1 Worksite_Number,
596: hoi2.org_information2 Association_Number,
597: hl.meaning Agreement_Code,
595: SELECT hoi2.org_information1 Worksite_Number,
596: hoi2.org_information2 Association_Number,
597: hl.meaning Agreement_Code,
598: hoi2.org_information4 Weekend_duty_pay
599: FROM hr_organization_units o1
600: , hr_organization_information hoi1
601: , hr_organization_information hoi2
602: ,hr_lookups hl
603: WHERE o1.business_group_id =csr_v_business_group_id --3133 --l_business_group_id
610: AND hl.lookup_type(+) ='SE_AGREEMENT_CODE'
611: AND hl.LOOKUP_CODE(+)=hoi2.org_information3 ;
612:
613: /* Legal Employers under the Business Group */
614: CURSOR csr_legal_employer(csr_v_business_group_id hr_organization_units.business_group_id%TYPE)
615: IS
616: SELECT o1.organization_id legal_employer_id
617: FROM hr_organization_units o1
618: , hr_organization_information hoi1
613: /* Legal Employers under the Business Group */
614: CURSOR csr_legal_employer(csr_v_business_group_id hr_organization_units.business_group_id%TYPE)
615: IS
616: SELECT o1.organization_id legal_employer_id
617: FROM hr_organization_units o1
618: , hr_organization_information hoi1
619: WHERE o1.business_group_id =csr_v_business_group_id --3133
620: AND hoi1.organization_id = o1.organization_id
621: AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
621: AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
622: AND hoi1.org_information_context = 'CLASS';
623:
624: /* Legal Employer Details */
625: CURSOR csr_legal_employer_details(csr_v_business_group_id hr_organization_units.business_group_id%TYPE,
626: csr_v_legal_employer_id hr_organization_units.organization_id%TYPE)
627: IS
628: SELECT o1.name legal_employer,
629: hoi2.org_information2 Organization_Id,
622: AND hoi1.org_information_context = 'CLASS';
623:
624: /* Legal Employer Details */
625: CURSOR csr_legal_employer_details(csr_v_business_group_id hr_organization_units.business_group_id%TYPE,
626: csr_v_legal_employer_id hr_organization_units.organization_id%TYPE)
627: IS
628: SELECT o1.name legal_employer,
629: hoi2.org_information2 Organization_Id,
630: hoi2.org_information9 Membership_Number
627: IS
628: SELECT o1.name legal_employer,
629: hoi2.org_information2 Organization_Id,
630: hoi2.org_information9 Membership_Number
631: FROM hr_organization_units o1
632: , hr_organization_information hoi1
633: , hr_organization_information hoi2
634: WHERE o1.business_group_id =csr_v_business_group_id --3133 --l_business_group_id
635: AND hoi1.organization_id = o1.organization_id
640: AND hoi2.ORG_INFORMATION_CONTEXT='SE_LEGAL_EMPLOYER_DETAILS';
641:
642:
643: /*Local Units under the Legal Employer*/
644: CURSOR csr_local_legal_employer(csr_v_business_group_id hr_organization_units.business_group_id%TYPE,
645: csr_v_legal_employer_id hr_organization_units.organization_id%TYPE)
646: IS
647: SELECT hoi2.ORG_INFORMATION1 local_unit
648: FROM hr_organization_units o1
641:
642:
643: /*Local Units under the Legal Employer*/
644: CURSOR csr_local_legal_employer(csr_v_business_group_id hr_organization_units.business_group_id%TYPE,
645: csr_v_legal_employer_id hr_organization_units.organization_id%TYPE)
646: IS
647: SELECT hoi2.ORG_INFORMATION1 local_unit
648: FROM hr_organization_units o1
649: , hr_organization_information hoi1
644: CURSOR csr_local_legal_employer(csr_v_business_group_id hr_organization_units.business_group_id%TYPE,
645: csr_v_legal_employer_id hr_organization_units.organization_id%TYPE)
646: IS
647: SELECT hoi2.ORG_INFORMATION1 local_unit
648: FROM hr_organization_units o1
649: , hr_organization_information hoi1
650: , hr_organization_information hoi2
651: WHERE o1.business_group_id =csr_v_business_group_id --3133 --l_business_group_id
652: AND hoi1.organization_id = o1.organization_id
674: FROM pay_payrolls_f
675: WHERE payroll_id=csr_v_payroll_id;
676:
677: /*Balances under the Balance Category*/
678: CURSOR csr_balances(csr_v_business_group_id hr_organization_units.business_group_id%type,
679: csr_v_category_name pay_balance_categories.category_name%type)
680: IS
681: SELECT balance_type_id FRoM
682: pay_balance_types pbt
704: AND DATABASE_ITEM_SUFFIX='_PER_MONTH' );
705: /* changing _PER_YTD to _PER_MONTH for bug fix 6209364 */
706:
707: /*Assignment level details for the report */
708: CURSOR csr_person_local_unit(csr_v_business_group_id hr_organization_units.business_group_id%TYPE,
709: csr_v_local_unit_id hr_organization_units.organization_id%TYPE, csr_v_effective_date date)
710: IS
711: SELECT papf.person_id person_id,
712: papf.national_identifier person_number,
705: /* changing _PER_YTD to _PER_MONTH for bug fix 6209364 */
706:
707: /*Assignment level details for the report */
708: CURSOR csr_person_local_unit(csr_v_business_group_id hr_organization_units.business_group_id%TYPE,
709: csr_v_local_unit_id hr_organization_units.organization_id%TYPE, csr_v_effective_date date)
710: IS
711: SELECT papf.person_id person_id,
712: papf.national_identifier person_number,
713: paaf.assignment_id,
769: l_termination_date date;
770: lr_Get_Defined_Balance_Id pay_defined_balances.defined_balance_id%type;
771: l_value number;
772: L_CFAR_NUMBER NUMBER;
773: l_legal_employer_id hr_organization_units.organization_id%TYPE;
774: l_virtual_date DATE;
775: l_date_birth per_all_people_f.DATE_OF_BIRTH%TYPE;
776: l_counter NUMBER :=0;
777: l_total_salary NUMBER;
798: l_agreement_code hr_lookups.meaning%TYPE;
799: l_agreement hr_lookups.lookup_code%TYPE;
800: l_asg_agreement_code hr_lookups.meaning%TYPE;
801: l_weekend_duty varchar2(10);
802: l_legal_employer hr_organization_units.name%TYPE;
803: l_organization_id varchar2(20);
804: l_membership_number varchar2(10);
805: l_assignment_id per_all_assignments_f.assignment_id%TYPE;
806: l_person_id per_all_people_f.person_id%TYPE;
2015: l_working_hour_code hr_lookups.lookup_code%type;
2016: l_lu_salary NUMBER;
2017: l_salary NUMBER:=0;
2018: l_grand_salary NUMBER:=0;
2019: l_legal_employer hr_organization_units.name%type;
2020: l_local_unit hr_organization_units.name%type;
2021: l_action_information_id pay_action_information.action_information_id%TYPE;
2022: l_month varchar2(20);
2023: l_year NUMBER;
2016: l_lu_salary NUMBER;
2017: l_salary NUMBER:=0;
2018: l_grand_salary NUMBER:=0;
2019: l_legal_employer hr_organization_units.name%type;
2020: l_local_unit hr_organization_units.name%type;
2021: l_action_information_id pay_action_information.action_information_id%TYPE;
2022: l_month varchar2(20);
2023: l_year NUMBER;
2024: l_retroactive_date_from DATE;
2204: l_lu_counter_reset VARCHAR2(10);
2205: l_prev_local_unit VARCHAR2(15);
2206: l_report_date DATE;
2207: l_person_number VARCHAR2(50);
2208: l_local_unit_id hr_organization_units.organization_id%type;
2209: l_period varchar2(50);
2210: l_report_year number(4);
2211: l_local_count NUMBER;
2212: l_CFAR_Number NUMBER;
2211: l_local_count NUMBER;
2212: l_CFAR_Number NUMBER;
2213:
2214:
2215: --l_local_unit hr_organization_units.name%TYPE;
2216:
2217: BEGIN
2218:
2219: