[Home] [Help]
336: l_con_set_name PAY_CONSOLIDATION_SETS.CONSOLIDATION_SET_NAME%TYPE;
337: l_org_name hr_all_organization_units.NAME%TYPE;
338:
339: BEGIN
340: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
341:
342: OPEN csr_ext_name( c_ext_dfn_id => p_ext_dfn_id);
343: FETCH csr_ext_name INTO l_extract_name;
344: CLOSE csr_ext_name;
372: g_conc_prog_details(0).consolset := l_con_set_name;
373: g_conc_prog_details(0).orgname := l_org_name;
374: g_conc_prog_details(0).orgid := p_org_id;
375:
376: Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
377: EXCEPTION
378: WHEN Others THEN
379: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
380: END Set_ConcProg_Parameter_Values;
375:
376: Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
377: EXCEPTION
378: WHEN Others THEN
379: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
380: END Set_ConcProg_Parameter_Values;
381: -- ====================================================================
382: -- Get_Current_Extract_Result:
383: -- Returns the person id associated with the given assignment.
461: l_end_date_year NUMBER;
462: l_year NUMBER;
463:
464: BEGIN
465: Hr_Utility.set_location('Entering :'||l_proc_name, 5);
466: --Extract the correct year from the start date and end date
467: l_start_date_year:=to_number(to_char(p_effective_start_date,'YYYY'));
468: l_end_date_year :=to_number(to_char(p_effective_end_date,'YYYY'));
469: IF ( l_start_date_year = l_end_date_year) THEN
469: IF ( l_start_date_year = l_end_date_year) THEN
470: l_year := l_start_date_year;
471: ELSE --Not a calendar month payroll
472: OPEN c_get_year(p_assignment_id,p_effective_start_date);
473: Hr_Utility.set_location('p_assignment_id :'||p_assignment_id, 5);
474: Hr_Utility.set_location('p_effective_start_date :'||p_effective_start_date, 6);
475: FETCH c_get_year INTO l_year;
476: IF c_get_year%NOTFOUND THEN
477: Hr_Utility.set_location('cursor not found', 6);
470: l_year := l_start_date_year;
471: ELSE --Not a calendar month payroll
472: OPEN c_get_year(p_assignment_id,p_effective_start_date);
473: Hr_Utility.set_location('p_assignment_id :'||p_assignment_id, 5);
474: Hr_Utility.set_location('p_effective_start_date :'||p_effective_start_date, 6);
475: FETCH c_get_year INTO l_year;
476: IF c_get_year%NOTFOUND THEN
477: Hr_Utility.set_location('cursor not found', 6);
478: l_year := to_number(to_char(p_effective_start_date,'YYYY'));
473: Hr_Utility.set_location('p_assignment_id :'||p_assignment_id, 5);
474: Hr_Utility.set_location('p_effective_start_date :'||p_effective_start_date, 6);
475: FETCH c_get_year INTO l_year;
476: IF c_get_year%NOTFOUND THEN
477: Hr_Utility.set_location('cursor not found', 6);
478: l_year := to_number(to_char(p_effective_start_date,'YYYY'));
479: END IF;
480: CLOSE c_get_year;
481: END IF;
478: l_year := to_number(to_char(p_effective_start_date,'YYYY'));
479: END IF;
480: CLOSE c_get_year;
481: END IF;
482: Hr_Utility.set_location('Leaving :'||l_proc_name, 100);
483: RETURN l_year;
484:
485: EXCEPTION
486: WHEN OTHERS THEN
483: RETURN l_year;
484:
485: EXCEPTION
486: WHEN OTHERS THEN
487: Hr_Utility.set_location('Exception occurred at :'||l_proc_name, 120);
488: RETURN -1;
489: END Get_Year;
490:
491: -- ============================================================================
515: l_proc_name Varchar2(150):= g_proc_name||'Update_Record_Values';
516: l_ext_dtl_rec_nc ben_ext_rslt_dtl%ROWTYPE;
517: BEGIN
518:
519: Hr_Utility.set_location('Entering :'||l_proc_name, 5);
520: -- nocopy changes
521: l_ext_dtl_rec_nc := p_ext_dtl_rec;
522:
523: IF p_data_ele_seqnum IS NULL THEN
685: p_ext_dtl_rec.val_74 := p_data_element_value;
686: ELSIF l_seqnum_rec.seq_num = 75 THEN
687: p_ext_dtl_rec.val_75 := p_data_element_value;
688: END IF;
689: Hr_Utility.set_location('Leaving :'||l_proc_name, 25);
690: RETURN;
691: EXCEPTION
692: WHEN Others THEN
693: -- nocopy changes
704: l_proc_name Varchar2(150) := g_proc_name||'Ins_Rslt_Dtl';
705: l_dtl_rec_nc ben_ext_rslt_dtl%ROWTYPE;
706:
707: BEGIN -- ins_rslt_dtl
708: --hr_utility.trace_on(null,'rk');
709: Hr_Utility.set_location('Entering :'||l_proc_name, 5);
710: -- nocopy changes
711: l_dtl_rec_nc := p_dtl_rec;
712: -- Get the next sequence NUMBER to insert a record into the table
705: l_dtl_rec_nc ben_ext_rslt_dtl%ROWTYPE;
706:
707: BEGIN -- ins_rslt_dtl
708: --hr_utility.trace_on(null,'rk');
709: Hr_Utility.set_location('Entering :'||l_proc_name, 5);
710: -- nocopy changes
711: l_dtl_rec_nc := p_dtl_rec;
712: -- Get the next sequence NUMBER to insert a record into the table
713: SELECT ben_ext_rslt_dtl_s .NEXTVAL INTO p_dtl_rec.ext_rslt_dtl_id FROM dual;
710: -- nocopy changes
711: l_dtl_rec_nc := p_dtl_rec;
712: -- Get the next sequence NUMBER to insert a record into the table
713: SELECT ben_ext_rslt_dtl_s .NEXTVAL INTO p_dtl_rec.ext_rslt_dtl_id FROM dual;
714: hr_utility.set_location(p_dtl_rec.EXT_RSLT_DTL_ID,10);
715: hr_utility.set_location(p_dtl_rec.EXT_RSLT_ID,10);
716: hr_utility.set_location(p_dtl_rec.BUSINESS_GROUP_ID,10);
717: hr_utility.set_location(p_dtl_rec.EXT_RCD_ID,10);
718: hr_utility.set_location(p_dtl_rec.PERSON_ID,10);
711: l_dtl_rec_nc := p_dtl_rec;
712: -- Get the next sequence NUMBER to insert a record into the table
713: SELECT ben_ext_rslt_dtl_s .NEXTVAL INTO p_dtl_rec.ext_rslt_dtl_id FROM dual;
714: hr_utility.set_location(p_dtl_rec.EXT_RSLT_DTL_ID,10);
715: hr_utility.set_location(p_dtl_rec.EXT_RSLT_ID,10);
716: hr_utility.set_location(p_dtl_rec.BUSINESS_GROUP_ID,10);
717: hr_utility.set_location(p_dtl_rec.EXT_RCD_ID,10);
718: hr_utility.set_location(p_dtl_rec.PERSON_ID,10);
719: hr_utility.set_location(p_dtl_rec.VAL_01,10);
712: -- Get the next sequence NUMBER to insert a record into the table
713: SELECT ben_ext_rslt_dtl_s .NEXTVAL INTO p_dtl_rec.ext_rslt_dtl_id FROM dual;
714: hr_utility.set_location(p_dtl_rec.EXT_RSLT_DTL_ID,10);
715: hr_utility.set_location(p_dtl_rec.EXT_RSLT_ID,10);
716: hr_utility.set_location(p_dtl_rec.BUSINESS_GROUP_ID,10);
717: hr_utility.set_location(p_dtl_rec.EXT_RCD_ID,10);
718: hr_utility.set_location(p_dtl_rec.PERSON_ID,10);
719: hr_utility.set_location(p_dtl_rec.VAL_01,10);
720: hr_utility.set_location(p_dtl_rec.VAL_02,10);
713: SELECT ben_ext_rslt_dtl_s .NEXTVAL INTO p_dtl_rec.ext_rslt_dtl_id FROM dual;
714: hr_utility.set_location(p_dtl_rec.EXT_RSLT_DTL_ID,10);
715: hr_utility.set_location(p_dtl_rec.EXT_RSLT_ID,10);
716: hr_utility.set_location(p_dtl_rec.BUSINESS_GROUP_ID,10);
717: hr_utility.set_location(p_dtl_rec.EXT_RCD_ID,10);
718: hr_utility.set_location(p_dtl_rec.PERSON_ID,10);
719: hr_utility.set_location(p_dtl_rec.VAL_01,10);
720: hr_utility.set_location(p_dtl_rec.VAL_02,10);
721: hr_utility.set_location(p_dtl_rec.VAL_03,10);
714: hr_utility.set_location(p_dtl_rec.EXT_RSLT_DTL_ID,10);
715: hr_utility.set_location(p_dtl_rec.EXT_RSLT_ID,10);
716: hr_utility.set_location(p_dtl_rec.BUSINESS_GROUP_ID,10);
717: hr_utility.set_location(p_dtl_rec.EXT_RCD_ID,10);
718: hr_utility.set_location(p_dtl_rec.PERSON_ID,10);
719: hr_utility.set_location(p_dtl_rec.VAL_01,10);
720: hr_utility.set_location(p_dtl_rec.VAL_02,10);
721: hr_utility.set_location(p_dtl_rec.VAL_03,10);
722: hr_utility.set_location(p_dtl_rec.VAL_04,10);
715: hr_utility.set_location(p_dtl_rec.EXT_RSLT_ID,10);
716: hr_utility.set_location(p_dtl_rec.BUSINESS_GROUP_ID,10);
717: hr_utility.set_location(p_dtl_rec.EXT_RCD_ID,10);
718: hr_utility.set_location(p_dtl_rec.PERSON_ID,10);
719: hr_utility.set_location(p_dtl_rec.VAL_01,10);
720: hr_utility.set_location(p_dtl_rec.VAL_02,10);
721: hr_utility.set_location(p_dtl_rec.VAL_03,10);
722: hr_utility.set_location(p_dtl_rec.VAL_04,10);
723: hr_utility.set_location(p_dtl_rec.VAL_05,10);
716: hr_utility.set_location(p_dtl_rec.BUSINESS_GROUP_ID,10);
717: hr_utility.set_location(p_dtl_rec.EXT_RCD_ID,10);
718: hr_utility.set_location(p_dtl_rec.PERSON_ID,10);
719: hr_utility.set_location(p_dtl_rec.VAL_01,10);
720: hr_utility.set_location(p_dtl_rec.VAL_02,10);
721: hr_utility.set_location(p_dtl_rec.VAL_03,10);
722: hr_utility.set_location(p_dtl_rec.VAL_04,10);
723: hr_utility.set_location(p_dtl_rec.VAL_05,10);
724: hr_utility.set_location(p_dtl_rec.VAL_06,10);
717: hr_utility.set_location(p_dtl_rec.EXT_RCD_ID,10);
718: hr_utility.set_location(p_dtl_rec.PERSON_ID,10);
719: hr_utility.set_location(p_dtl_rec.VAL_01,10);
720: hr_utility.set_location(p_dtl_rec.VAL_02,10);
721: hr_utility.set_location(p_dtl_rec.VAL_03,10);
722: hr_utility.set_location(p_dtl_rec.VAL_04,10);
723: hr_utility.set_location(p_dtl_rec.VAL_05,10);
724: hr_utility.set_location(p_dtl_rec.VAL_06,10);
725: hr_utility.set_location(p_dtl_rec.VAL_07,10);
718: hr_utility.set_location(p_dtl_rec.PERSON_ID,10);
719: hr_utility.set_location(p_dtl_rec.VAL_01,10);
720: hr_utility.set_location(p_dtl_rec.VAL_02,10);
721: hr_utility.set_location(p_dtl_rec.VAL_03,10);
722: hr_utility.set_location(p_dtl_rec.VAL_04,10);
723: hr_utility.set_location(p_dtl_rec.VAL_05,10);
724: hr_utility.set_location(p_dtl_rec.VAL_06,10);
725: hr_utility.set_location(p_dtl_rec.VAL_07,10);
726: hr_utility.set_location(p_dtl_rec.VAL_08,10);
719: hr_utility.set_location(p_dtl_rec.VAL_01,10);
720: hr_utility.set_location(p_dtl_rec.VAL_02,10);
721: hr_utility.set_location(p_dtl_rec.VAL_03,10);
722: hr_utility.set_location(p_dtl_rec.VAL_04,10);
723: hr_utility.set_location(p_dtl_rec.VAL_05,10);
724: hr_utility.set_location(p_dtl_rec.VAL_06,10);
725: hr_utility.set_location(p_dtl_rec.VAL_07,10);
726: hr_utility.set_location(p_dtl_rec.VAL_08,10);
727: hr_utility.set_location(p_dtl_rec.VAL_09,10);
720: hr_utility.set_location(p_dtl_rec.VAL_02,10);
721: hr_utility.set_location(p_dtl_rec.VAL_03,10);
722: hr_utility.set_location(p_dtl_rec.VAL_04,10);
723: hr_utility.set_location(p_dtl_rec.VAL_05,10);
724: hr_utility.set_location(p_dtl_rec.VAL_06,10);
725: hr_utility.set_location(p_dtl_rec.VAL_07,10);
726: hr_utility.set_location(p_dtl_rec.VAL_08,10);
727: hr_utility.set_location(p_dtl_rec.VAL_09,10);
728: hr_utility.set_location(p_dtl_rec.VAL_10,10);
721: hr_utility.set_location(p_dtl_rec.VAL_03,10);
722: hr_utility.set_location(p_dtl_rec.VAL_04,10);
723: hr_utility.set_location(p_dtl_rec.VAL_05,10);
724: hr_utility.set_location(p_dtl_rec.VAL_06,10);
725: hr_utility.set_location(p_dtl_rec.VAL_07,10);
726: hr_utility.set_location(p_dtl_rec.VAL_08,10);
727: hr_utility.set_location(p_dtl_rec.VAL_09,10);
728: hr_utility.set_location(p_dtl_rec.VAL_10,10);
729: hr_utility.set_location(p_dtl_rec.VAL_11,10);
722: hr_utility.set_location(p_dtl_rec.VAL_04,10);
723: hr_utility.set_location(p_dtl_rec.VAL_05,10);
724: hr_utility.set_location(p_dtl_rec.VAL_06,10);
725: hr_utility.set_location(p_dtl_rec.VAL_07,10);
726: hr_utility.set_location(p_dtl_rec.VAL_08,10);
727: hr_utility.set_location(p_dtl_rec.VAL_09,10);
728: hr_utility.set_location(p_dtl_rec.VAL_10,10);
729: hr_utility.set_location(p_dtl_rec.VAL_11,10);
730: hr_utility.set_location(p_dtl_rec.VAL_12,10);
723: hr_utility.set_location(p_dtl_rec.VAL_05,10);
724: hr_utility.set_location(p_dtl_rec.VAL_06,10);
725: hr_utility.set_location(p_dtl_rec.VAL_07,10);
726: hr_utility.set_location(p_dtl_rec.VAL_08,10);
727: hr_utility.set_location(p_dtl_rec.VAL_09,10);
728: hr_utility.set_location(p_dtl_rec.VAL_10,10);
729: hr_utility.set_location(p_dtl_rec.VAL_11,10);
730: hr_utility.set_location(p_dtl_rec.VAL_12,10);
731: hr_utility.set_location(p_dtl_rec.VAL_13,10);
724: hr_utility.set_location(p_dtl_rec.VAL_06,10);
725: hr_utility.set_location(p_dtl_rec.VAL_07,10);
726: hr_utility.set_location(p_dtl_rec.VAL_08,10);
727: hr_utility.set_location(p_dtl_rec.VAL_09,10);
728: hr_utility.set_location(p_dtl_rec.VAL_10,10);
729: hr_utility.set_location(p_dtl_rec.VAL_11,10);
730: hr_utility.set_location(p_dtl_rec.VAL_12,10);
731: hr_utility.set_location(p_dtl_rec.VAL_13,10);
732: hr_utility.set_location(p_dtl_rec.VAL_14,10);
725: hr_utility.set_location(p_dtl_rec.VAL_07,10);
726: hr_utility.set_location(p_dtl_rec.VAL_08,10);
727: hr_utility.set_location(p_dtl_rec.VAL_09,10);
728: hr_utility.set_location(p_dtl_rec.VAL_10,10);
729: hr_utility.set_location(p_dtl_rec.VAL_11,10);
730: hr_utility.set_location(p_dtl_rec.VAL_12,10);
731: hr_utility.set_location(p_dtl_rec.VAL_13,10);
732: hr_utility.set_location(p_dtl_rec.VAL_14,10);
733: hr_utility.set_location(p_dtl_rec.VAL_15,10);
726: hr_utility.set_location(p_dtl_rec.VAL_08,10);
727: hr_utility.set_location(p_dtl_rec.VAL_09,10);
728: hr_utility.set_location(p_dtl_rec.VAL_10,10);
729: hr_utility.set_location(p_dtl_rec.VAL_11,10);
730: hr_utility.set_location(p_dtl_rec.VAL_12,10);
731: hr_utility.set_location(p_dtl_rec.VAL_13,10);
732: hr_utility.set_location(p_dtl_rec.VAL_14,10);
733: hr_utility.set_location(p_dtl_rec.VAL_15,10);
734: hr_utility.set_location(p_dtl_rec.VAL_16,10);
727: hr_utility.set_location(p_dtl_rec.VAL_09,10);
728: hr_utility.set_location(p_dtl_rec.VAL_10,10);
729: hr_utility.set_location(p_dtl_rec.VAL_11,10);
730: hr_utility.set_location(p_dtl_rec.VAL_12,10);
731: hr_utility.set_location(p_dtl_rec.VAL_13,10);
732: hr_utility.set_location(p_dtl_rec.VAL_14,10);
733: hr_utility.set_location(p_dtl_rec.VAL_15,10);
734: hr_utility.set_location(p_dtl_rec.VAL_16,10);
735: hr_utility.set_location(p_dtl_rec.VAL_17,10);
728: hr_utility.set_location(p_dtl_rec.VAL_10,10);
729: hr_utility.set_location(p_dtl_rec.VAL_11,10);
730: hr_utility.set_location(p_dtl_rec.VAL_12,10);
731: hr_utility.set_location(p_dtl_rec.VAL_13,10);
732: hr_utility.set_location(p_dtl_rec.VAL_14,10);
733: hr_utility.set_location(p_dtl_rec.VAL_15,10);
734: hr_utility.set_location(p_dtl_rec.VAL_16,10);
735: hr_utility.set_location(p_dtl_rec.VAL_17,10);
736: hr_utility.set_location(p_dtl_rec.VAL_19,10);
729: hr_utility.set_location(p_dtl_rec.VAL_11,10);
730: hr_utility.set_location(p_dtl_rec.VAL_12,10);
731: hr_utility.set_location(p_dtl_rec.VAL_13,10);
732: hr_utility.set_location(p_dtl_rec.VAL_14,10);
733: hr_utility.set_location(p_dtl_rec.VAL_15,10);
734: hr_utility.set_location(p_dtl_rec.VAL_16,10);
735: hr_utility.set_location(p_dtl_rec.VAL_17,10);
736: hr_utility.set_location(p_dtl_rec.VAL_19,10);
737: hr_utility.set_location(p_dtl_rec.VAL_18,10);
730: hr_utility.set_location(p_dtl_rec.VAL_12,10);
731: hr_utility.set_location(p_dtl_rec.VAL_13,10);
732: hr_utility.set_location(p_dtl_rec.VAL_14,10);
733: hr_utility.set_location(p_dtl_rec.VAL_15,10);
734: hr_utility.set_location(p_dtl_rec.VAL_16,10);
735: hr_utility.set_location(p_dtl_rec.VAL_17,10);
736: hr_utility.set_location(p_dtl_rec.VAL_19,10);
737: hr_utility.set_location(p_dtl_rec.VAL_18,10);
738: hr_utility.set_location(p_dtl_rec.VAL_20,10);
731: hr_utility.set_location(p_dtl_rec.VAL_13,10);
732: hr_utility.set_location(p_dtl_rec.VAL_14,10);
733: hr_utility.set_location(p_dtl_rec.VAL_15,10);
734: hr_utility.set_location(p_dtl_rec.VAL_16,10);
735: hr_utility.set_location(p_dtl_rec.VAL_17,10);
736: hr_utility.set_location(p_dtl_rec.VAL_19,10);
737: hr_utility.set_location(p_dtl_rec.VAL_18,10);
738: hr_utility.set_location(p_dtl_rec.VAL_20,10);
739: hr_utility.set_location(p_dtl_rec.VAL_21,10);
732: hr_utility.set_location(p_dtl_rec.VAL_14,10);
733: hr_utility.set_location(p_dtl_rec.VAL_15,10);
734: hr_utility.set_location(p_dtl_rec.VAL_16,10);
735: hr_utility.set_location(p_dtl_rec.VAL_17,10);
736: hr_utility.set_location(p_dtl_rec.VAL_19,10);
737: hr_utility.set_location(p_dtl_rec.VAL_18,10);
738: hr_utility.set_location(p_dtl_rec.VAL_20,10);
739: hr_utility.set_location(p_dtl_rec.VAL_21,10);
740: hr_utility.set_location(p_dtl_rec.VAL_22,10);
733: hr_utility.set_location(p_dtl_rec.VAL_15,10);
734: hr_utility.set_location(p_dtl_rec.VAL_16,10);
735: hr_utility.set_location(p_dtl_rec.VAL_17,10);
736: hr_utility.set_location(p_dtl_rec.VAL_19,10);
737: hr_utility.set_location(p_dtl_rec.VAL_18,10);
738: hr_utility.set_location(p_dtl_rec.VAL_20,10);
739: hr_utility.set_location(p_dtl_rec.VAL_21,10);
740: hr_utility.set_location(p_dtl_rec.VAL_22,10);
741: hr_utility.set_location(p_dtl_rec.VAL_23,10);
734: hr_utility.set_location(p_dtl_rec.VAL_16,10);
735: hr_utility.set_location(p_dtl_rec.VAL_17,10);
736: hr_utility.set_location(p_dtl_rec.VAL_19,10);
737: hr_utility.set_location(p_dtl_rec.VAL_18,10);
738: hr_utility.set_location(p_dtl_rec.VAL_20,10);
739: hr_utility.set_location(p_dtl_rec.VAL_21,10);
740: hr_utility.set_location(p_dtl_rec.VAL_22,10);
741: hr_utility.set_location(p_dtl_rec.VAL_23,10);
742: hr_utility.set_location(p_dtl_rec.VAL_24,10);
735: hr_utility.set_location(p_dtl_rec.VAL_17,10);
736: hr_utility.set_location(p_dtl_rec.VAL_19,10);
737: hr_utility.set_location(p_dtl_rec.VAL_18,10);
738: hr_utility.set_location(p_dtl_rec.VAL_20,10);
739: hr_utility.set_location(p_dtl_rec.VAL_21,10);
740: hr_utility.set_location(p_dtl_rec.VAL_22,10);
741: hr_utility.set_location(p_dtl_rec.VAL_23,10);
742: hr_utility.set_location(p_dtl_rec.VAL_24,10);
743: hr_utility.set_location(p_dtl_rec.VAL_25,10);
736: hr_utility.set_location(p_dtl_rec.VAL_19,10);
737: hr_utility.set_location(p_dtl_rec.VAL_18,10);
738: hr_utility.set_location(p_dtl_rec.VAL_20,10);
739: hr_utility.set_location(p_dtl_rec.VAL_21,10);
740: hr_utility.set_location(p_dtl_rec.VAL_22,10);
741: hr_utility.set_location(p_dtl_rec.VAL_23,10);
742: hr_utility.set_location(p_dtl_rec.VAL_24,10);
743: hr_utility.set_location(p_dtl_rec.VAL_25,10);
744: hr_utility.set_location(p_dtl_rec.VAL_26,10);
737: hr_utility.set_location(p_dtl_rec.VAL_18,10);
738: hr_utility.set_location(p_dtl_rec.VAL_20,10);
739: hr_utility.set_location(p_dtl_rec.VAL_21,10);
740: hr_utility.set_location(p_dtl_rec.VAL_22,10);
741: hr_utility.set_location(p_dtl_rec.VAL_23,10);
742: hr_utility.set_location(p_dtl_rec.VAL_24,10);
743: hr_utility.set_location(p_dtl_rec.VAL_25,10);
744: hr_utility.set_location(p_dtl_rec.VAL_26,10);
745: hr_utility.set_location(p_dtl_rec.VAL_27,10);
738: hr_utility.set_location(p_dtl_rec.VAL_20,10);
739: hr_utility.set_location(p_dtl_rec.VAL_21,10);
740: hr_utility.set_location(p_dtl_rec.VAL_22,10);
741: hr_utility.set_location(p_dtl_rec.VAL_23,10);
742: hr_utility.set_location(p_dtl_rec.VAL_24,10);
743: hr_utility.set_location(p_dtl_rec.VAL_25,10);
744: hr_utility.set_location(p_dtl_rec.VAL_26,10);
745: hr_utility.set_location(p_dtl_rec.VAL_27,10);
746: hr_utility.set_location(p_dtl_rec.VAL_28,10);
739: hr_utility.set_location(p_dtl_rec.VAL_21,10);
740: hr_utility.set_location(p_dtl_rec.VAL_22,10);
741: hr_utility.set_location(p_dtl_rec.VAL_23,10);
742: hr_utility.set_location(p_dtl_rec.VAL_24,10);
743: hr_utility.set_location(p_dtl_rec.VAL_25,10);
744: hr_utility.set_location(p_dtl_rec.VAL_26,10);
745: hr_utility.set_location(p_dtl_rec.VAL_27,10);
746: hr_utility.set_location(p_dtl_rec.VAL_28,10);
747: hr_utility.set_location(p_dtl_rec.VAL_29,10);
740: hr_utility.set_location(p_dtl_rec.VAL_22,10);
741: hr_utility.set_location(p_dtl_rec.VAL_23,10);
742: hr_utility.set_location(p_dtl_rec.VAL_24,10);
743: hr_utility.set_location(p_dtl_rec.VAL_25,10);
744: hr_utility.set_location(p_dtl_rec.VAL_26,10);
745: hr_utility.set_location(p_dtl_rec.VAL_27,10);
746: hr_utility.set_location(p_dtl_rec.VAL_28,10);
747: hr_utility.set_location(p_dtl_rec.VAL_29,10);
748: hr_utility.set_location(p_dtl_rec.VAL_30,10);
741: hr_utility.set_location(p_dtl_rec.VAL_23,10);
742: hr_utility.set_location(p_dtl_rec.VAL_24,10);
743: hr_utility.set_location(p_dtl_rec.VAL_25,10);
744: hr_utility.set_location(p_dtl_rec.VAL_26,10);
745: hr_utility.set_location(p_dtl_rec.VAL_27,10);
746: hr_utility.set_location(p_dtl_rec.VAL_28,10);
747: hr_utility.set_location(p_dtl_rec.VAL_29,10);
748: hr_utility.set_location(p_dtl_rec.VAL_30,10);
749: hr_utility.set_location(p_dtl_rec.VAL_31,10);
742: hr_utility.set_location(p_dtl_rec.VAL_24,10);
743: hr_utility.set_location(p_dtl_rec.VAL_25,10);
744: hr_utility.set_location(p_dtl_rec.VAL_26,10);
745: hr_utility.set_location(p_dtl_rec.VAL_27,10);
746: hr_utility.set_location(p_dtl_rec.VAL_28,10);
747: hr_utility.set_location(p_dtl_rec.VAL_29,10);
748: hr_utility.set_location(p_dtl_rec.VAL_30,10);
749: hr_utility.set_location(p_dtl_rec.VAL_31,10);
750: hr_utility.set_location(p_dtl_rec.VAL_32,10);
743: hr_utility.set_location(p_dtl_rec.VAL_25,10);
744: hr_utility.set_location(p_dtl_rec.VAL_26,10);
745: hr_utility.set_location(p_dtl_rec.VAL_27,10);
746: hr_utility.set_location(p_dtl_rec.VAL_28,10);
747: hr_utility.set_location(p_dtl_rec.VAL_29,10);
748: hr_utility.set_location(p_dtl_rec.VAL_30,10);
749: hr_utility.set_location(p_dtl_rec.VAL_31,10);
750: hr_utility.set_location(p_dtl_rec.VAL_32,10);
751: hr_utility.set_location(p_dtl_rec.VAL_33,10);
744: hr_utility.set_location(p_dtl_rec.VAL_26,10);
745: hr_utility.set_location(p_dtl_rec.VAL_27,10);
746: hr_utility.set_location(p_dtl_rec.VAL_28,10);
747: hr_utility.set_location(p_dtl_rec.VAL_29,10);
748: hr_utility.set_location(p_dtl_rec.VAL_30,10);
749: hr_utility.set_location(p_dtl_rec.VAL_31,10);
750: hr_utility.set_location(p_dtl_rec.VAL_32,10);
751: hr_utility.set_location(p_dtl_rec.VAL_33,10);
752: hr_utility.set_location(p_dtl_rec.VAL_34,10);
745: hr_utility.set_location(p_dtl_rec.VAL_27,10);
746: hr_utility.set_location(p_dtl_rec.VAL_28,10);
747: hr_utility.set_location(p_dtl_rec.VAL_29,10);
748: hr_utility.set_location(p_dtl_rec.VAL_30,10);
749: hr_utility.set_location(p_dtl_rec.VAL_31,10);
750: hr_utility.set_location(p_dtl_rec.VAL_32,10);
751: hr_utility.set_location(p_dtl_rec.VAL_33,10);
752: hr_utility.set_location(p_dtl_rec.VAL_34,10);
753: hr_utility.set_location(p_dtl_rec.VAL_35,10);
746: hr_utility.set_location(p_dtl_rec.VAL_28,10);
747: hr_utility.set_location(p_dtl_rec.VAL_29,10);
748: hr_utility.set_location(p_dtl_rec.VAL_30,10);
749: hr_utility.set_location(p_dtl_rec.VAL_31,10);
750: hr_utility.set_location(p_dtl_rec.VAL_32,10);
751: hr_utility.set_location(p_dtl_rec.VAL_33,10);
752: hr_utility.set_location(p_dtl_rec.VAL_34,10);
753: hr_utility.set_location(p_dtl_rec.VAL_35,10);
754: hr_utility.set_location(p_dtl_rec.VAL_36,10);
747: hr_utility.set_location(p_dtl_rec.VAL_29,10);
748: hr_utility.set_location(p_dtl_rec.VAL_30,10);
749: hr_utility.set_location(p_dtl_rec.VAL_31,10);
750: hr_utility.set_location(p_dtl_rec.VAL_32,10);
751: hr_utility.set_location(p_dtl_rec.VAL_33,10);
752: hr_utility.set_location(p_dtl_rec.VAL_34,10);
753: hr_utility.set_location(p_dtl_rec.VAL_35,10);
754: hr_utility.set_location(p_dtl_rec.VAL_36,10);
755: hr_utility.set_location(p_dtl_rec.VAL_37,10);
748: hr_utility.set_location(p_dtl_rec.VAL_30,10);
749: hr_utility.set_location(p_dtl_rec.VAL_31,10);
750: hr_utility.set_location(p_dtl_rec.VAL_32,10);
751: hr_utility.set_location(p_dtl_rec.VAL_33,10);
752: hr_utility.set_location(p_dtl_rec.VAL_34,10);
753: hr_utility.set_location(p_dtl_rec.VAL_35,10);
754: hr_utility.set_location(p_dtl_rec.VAL_36,10);
755: hr_utility.set_location(p_dtl_rec.VAL_37,10);
756: hr_utility.set_location(p_dtl_rec.VAL_38,10);
749: hr_utility.set_location(p_dtl_rec.VAL_31,10);
750: hr_utility.set_location(p_dtl_rec.VAL_32,10);
751: hr_utility.set_location(p_dtl_rec.VAL_33,10);
752: hr_utility.set_location(p_dtl_rec.VAL_34,10);
753: hr_utility.set_location(p_dtl_rec.VAL_35,10);
754: hr_utility.set_location(p_dtl_rec.VAL_36,10);
755: hr_utility.set_location(p_dtl_rec.VAL_37,10);
756: hr_utility.set_location(p_dtl_rec.VAL_38,10);
757: hr_utility.set_location(p_dtl_rec.VAL_39,10);
750: hr_utility.set_location(p_dtl_rec.VAL_32,10);
751: hr_utility.set_location(p_dtl_rec.VAL_33,10);
752: hr_utility.set_location(p_dtl_rec.VAL_34,10);
753: hr_utility.set_location(p_dtl_rec.VAL_35,10);
754: hr_utility.set_location(p_dtl_rec.VAL_36,10);
755: hr_utility.set_location(p_dtl_rec.VAL_37,10);
756: hr_utility.set_location(p_dtl_rec.VAL_38,10);
757: hr_utility.set_location(p_dtl_rec.VAL_39,10);
758: hr_utility.set_location(p_dtl_rec.VAL_40,10);
751: hr_utility.set_location(p_dtl_rec.VAL_33,10);
752: hr_utility.set_location(p_dtl_rec.VAL_34,10);
753: hr_utility.set_location(p_dtl_rec.VAL_35,10);
754: hr_utility.set_location(p_dtl_rec.VAL_36,10);
755: hr_utility.set_location(p_dtl_rec.VAL_37,10);
756: hr_utility.set_location(p_dtl_rec.VAL_38,10);
757: hr_utility.set_location(p_dtl_rec.VAL_39,10);
758: hr_utility.set_location(p_dtl_rec.VAL_40,10);
759: hr_utility.set_location(p_dtl_rec.VAL_41,10);
752: hr_utility.set_location(p_dtl_rec.VAL_34,10);
753: hr_utility.set_location(p_dtl_rec.VAL_35,10);
754: hr_utility.set_location(p_dtl_rec.VAL_36,10);
755: hr_utility.set_location(p_dtl_rec.VAL_37,10);
756: hr_utility.set_location(p_dtl_rec.VAL_38,10);
757: hr_utility.set_location(p_dtl_rec.VAL_39,10);
758: hr_utility.set_location(p_dtl_rec.VAL_40,10);
759: hr_utility.set_location(p_dtl_rec.VAL_41,10);
760: hr_utility.set_location(p_dtl_rec.VAL_42,10);
753: hr_utility.set_location(p_dtl_rec.VAL_35,10);
754: hr_utility.set_location(p_dtl_rec.VAL_36,10);
755: hr_utility.set_location(p_dtl_rec.VAL_37,10);
756: hr_utility.set_location(p_dtl_rec.VAL_38,10);
757: hr_utility.set_location(p_dtl_rec.VAL_39,10);
758: hr_utility.set_location(p_dtl_rec.VAL_40,10);
759: hr_utility.set_location(p_dtl_rec.VAL_41,10);
760: hr_utility.set_location(p_dtl_rec.VAL_42,10);
761: hr_utility.set_location(p_dtl_rec.VAL_43,10);
754: hr_utility.set_location(p_dtl_rec.VAL_36,10);
755: hr_utility.set_location(p_dtl_rec.VAL_37,10);
756: hr_utility.set_location(p_dtl_rec.VAL_38,10);
757: hr_utility.set_location(p_dtl_rec.VAL_39,10);
758: hr_utility.set_location(p_dtl_rec.VAL_40,10);
759: hr_utility.set_location(p_dtl_rec.VAL_41,10);
760: hr_utility.set_location(p_dtl_rec.VAL_42,10);
761: hr_utility.set_location(p_dtl_rec.VAL_43,10);
762: hr_utility.set_location(p_dtl_rec.VAL_44,10);
755: hr_utility.set_location(p_dtl_rec.VAL_37,10);
756: hr_utility.set_location(p_dtl_rec.VAL_38,10);
757: hr_utility.set_location(p_dtl_rec.VAL_39,10);
758: hr_utility.set_location(p_dtl_rec.VAL_40,10);
759: hr_utility.set_location(p_dtl_rec.VAL_41,10);
760: hr_utility.set_location(p_dtl_rec.VAL_42,10);
761: hr_utility.set_location(p_dtl_rec.VAL_43,10);
762: hr_utility.set_location(p_dtl_rec.VAL_44,10);
763: hr_utility.set_location(p_dtl_rec.VAL_45,10);
756: hr_utility.set_location(p_dtl_rec.VAL_38,10);
757: hr_utility.set_location(p_dtl_rec.VAL_39,10);
758: hr_utility.set_location(p_dtl_rec.VAL_40,10);
759: hr_utility.set_location(p_dtl_rec.VAL_41,10);
760: hr_utility.set_location(p_dtl_rec.VAL_42,10);
761: hr_utility.set_location(p_dtl_rec.VAL_43,10);
762: hr_utility.set_location(p_dtl_rec.VAL_44,10);
763: hr_utility.set_location(p_dtl_rec.VAL_45,10);
764: hr_utility.set_location(p_dtl_rec.VAL_46,10);
757: hr_utility.set_location(p_dtl_rec.VAL_39,10);
758: hr_utility.set_location(p_dtl_rec.VAL_40,10);
759: hr_utility.set_location(p_dtl_rec.VAL_41,10);
760: hr_utility.set_location(p_dtl_rec.VAL_42,10);
761: hr_utility.set_location(p_dtl_rec.VAL_43,10);
762: hr_utility.set_location(p_dtl_rec.VAL_44,10);
763: hr_utility.set_location(p_dtl_rec.VAL_45,10);
764: hr_utility.set_location(p_dtl_rec.VAL_46,10);
765: hr_utility.set_location(p_dtl_rec.VAL_47,10);
758: hr_utility.set_location(p_dtl_rec.VAL_40,10);
759: hr_utility.set_location(p_dtl_rec.VAL_41,10);
760: hr_utility.set_location(p_dtl_rec.VAL_42,10);
761: hr_utility.set_location(p_dtl_rec.VAL_43,10);
762: hr_utility.set_location(p_dtl_rec.VAL_44,10);
763: hr_utility.set_location(p_dtl_rec.VAL_45,10);
764: hr_utility.set_location(p_dtl_rec.VAL_46,10);
765: hr_utility.set_location(p_dtl_rec.VAL_47,10);
766: hr_utility.set_location(p_dtl_rec.VAL_48,10);
759: hr_utility.set_location(p_dtl_rec.VAL_41,10);
760: hr_utility.set_location(p_dtl_rec.VAL_42,10);
761: hr_utility.set_location(p_dtl_rec.VAL_43,10);
762: hr_utility.set_location(p_dtl_rec.VAL_44,10);
763: hr_utility.set_location(p_dtl_rec.VAL_45,10);
764: hr_utility.set_location(p_dtl_rec.VAL_46,10);
765: hr_utility.set_location(p_dtl_rec.VAL_47,10);
766: hr_utility.set_location(p_dtl_rec.VAL_48,10);
767: hr_utility.set_location(p_dtl_rec.VAL_49,10);
760: hr_utility.set_location(p_dtl_rec.VAL_42,10);
761: hr_utility.set_location(p_dtl_rec.VAL_43,10);
762: hr_utility.set_location(p_dtl_rec.VAL_44,10);
763: hr_utility.set_location(p_dtl_rec.VAL_45,10);
764: hr_utility.set_location(p_dtl_rec.VAL_46,10);
765: hr_utility.set_location(p_dtl_rec.VAL_47,10);
766: hr_utility.set_location(p_dtl_rec.VAL_48,10);
767: hr_utility.set_location(p_dtl_rec.VAL_49,10);
768: hr_utility.set_location(p_dtl_rec.VAL_50,10);
761: hr_utility.set_location(p_dtl_rec.VAL_43,10);
762: hr_utility.set_location(p_dtl_rec.VAL_44,10);
763: hr_utility.set_location(p_dtl_rec.VAL_45,10);
764: hr_utility.set_location(p_dtl_rec.VAL_46,10);
765: hr_utility.set_location(p_dtl_rec.VAL_47,10);
766: hr_utility.set_location(p_dtl_rec.VAL_48,10);
767: hr_utility.set_location(p_dtl_rec.VAL_49,10);
768: hr_utility.set_location(p_dtl_rec.VAL_50,10);
769: hr_utility.set_location(p_dtl_rec.VAL_51,10);
762: hr_utility.set_location(p_dtl_rec.VAL_44,10);
763: hr_utility.set_location(p_dtl_rec.VAL_45,10);
764: hr_utility.set_location(p_dtl_rec.VAL_46,10);
765: hr_utility.set_location(p_dtl_rec.VAL_47,10);
766: hr_utility.set_location(p_dtl_rec.VAL_48,10);
767: hr_utility.set_location(p_dtl_rec.VAL_49,10);
768: hr_utility.set_location(p_dtl_rec.VAL_50,10);
769: hr_utility.set_location(p_dtl_rec.VAL_51,10);
770: hr_utility.set_location(p_dtl_rec.VAL_52,10);
763: hr_utility.set_location(p_dtl_rec.VAL_45,10);
764: hr_utility.set_location(p_dtl_rec.VAL_46,10);
765: hr_utility.set_location(p_dtl_rec.VAL_47,10);
766: hr_utility.set_location(p_dtl_rec.VAL_48,10);
767: hr_utility.set_location(p_dtl_rec.VAL_49,10);
768: hr_utility.set_location(p_dtl_rec.VAL_50,10);
769: hr_utility.set_location(p_dtl_rec.VAL_51,10);
770: hr_utility.set_location(p_dtl_rec.VAL_52,10);
771: hr_utility.set_location(p_dtl_rec.VAL_53,10);
764: hr_utility.set_location(p_dtl_rec.VAL_46,10);
765: hr_utility.set_location(p_dtl_rec.VAL_47,10);
766: hr_utility.set_location(p_dtl_rec.VAL_48,10);
767: hr_utility.set_location(p_dtl_rec.VAL_49,10);
768: hr_utility.set_location(p_dtl_rec.VAL_50,10);
769: hr_utility.set_location(p_dtl_rec.VAL_51,10);
770: hr_utility.set_location(p_dtl_rec.VAL_52,10);
771: hr_utility.set_location(p_dtl_rec.VAL_53,10);
772: hr_utility.set_location(p_dtl_rec.VAL_54,10);
765: hr_utility.set_location(p_dtl_rec.VAL_47,10);
766: hr_utility.set_location(p_dtl_rec.VAL_48,10);
767: hr_utility.set_location(p_dtl_rec.VAL_49,10);
768: hr_utility.set_location(p_dtl_rec.VAL_50,10);
769: hr_utility.set_location(p_dtl_rec.VAL_51,10);
770: hr_utility.set_location(p_dtl_rec.VAL_52,10);
771: hr_utility.set_location(p_dtl_rec.VAL_53,10);
772: hr_utility.set_location(p_dtl_rec.VAL_54,10);
773: hr_utility.set_location(p_dtl_rec.VAL_55,10);
766: hr_utility.set_location(p_dtl_rec.VAL_48,10);
767: hr_utility.set_location(p_dtl_rec.VAL_49,10);
768: hr_utility.set_location(p_dtl_rec.VAL_50,10);
769: hr_utility.set_location(p_dtl_rec.VAL_51,10);
770: hr_utility.set_location(p_dtl_rec.VAL_52,10);
771: hr_utility.set_location(p_dtl_rec.VAL_53,10);
772: hr_utility.set_location(p_dtl_rec.VAL_54,10);
773: hr_utility.set_location(p_dtl_rec.VAL_55,10);
774: hr_utility.set_location(p_dtl_rec.VAL_56,10);
767: hr_utility.set_location(p_dtl_rec.VAL_49,10);
768: hr_utility.set_location(p_dtl_rec.VAL_50,10);
769: hr_utility.set_location(p_dtl_rec.VAL_51,10);
770: hr_utility.set_location(p_dtl_rec.VAL_52,10);
771: hr_utility.set_location(p_dtl_rec.VAL_53,10);
772: hr_utility.set_location(p_dtl_rec.VAL_54,10);
773: hr_utility.set_location(p_dtl_rec.VAL_55,10);
774: hr_utility.set_location(p_dtl_rec.VAL_56,10);
775: hr_utility.set_location(p_dtl_rec.VAL_57,10);
768: hr_utility.set_location(p_dtl_rec.VAL_50,10);
769: hr_utility.set_location(p_dtl_rec.VAL_51,10);
770: hr_utility.set_location(p_dtl_rec.VAL_52,10);
771: hr_utility.set_location(p_dtl_rec.VAL_53,10);
772: hr_utility.set_location(p_dtl_rec.VAL_54,10);
773: hr_utility.set_location(p_dtl_rec.VAL_55,10);
774: hr_utility.set_location(p_dtl_rec.VAL_56,10);
775: hr_utility.set_location(p_dtl_rec.VAL_57,10);
776: hr_utility.set_location(p_dtl_rec.VAL_58,10);
769: hr_utility.set_location(p_dtl_rec.VAL_51,10);
770: hr_utility.set_location(p_dtl_rec.VAL_52,10);
771: hr_utility.set_location(p_dtl_rec.VAL_53,10);
772: hr_utility.set_location(p_dtl_rec.VAL_54,10);
773: hr_utility.set_location(p_dtl_rec.VAL_55,10);
774: hr_utility.set_location(p_dtl_rec.VAL_56,10);
775: hr_utility.set_location(p_dtl_rec.VAL_57,10);
776: hr_utility.set_location(p_dtl_rec.VAL_58,10);
777: hr_utility.set_location(p_dtl_rec.VAL_59,10);
770: hr_utility.set_location(p_dtl_rec.VAL_52,10);
771: hr_utility.set_location(p_dtl_rec.VAL_53,10);
772: hr_utility.set_location(p_dtl_rec.VAL_54,10);
773: hr_utility.set_location(p_dtl_rec.VAL_55,10);
774: hr_utility.set_location(p_dtl_rec.VAL_56,10);
775: hr_utility.set_location(p_dtl_rec.VAL_57,10);
776: hr_utility.set_location(p_dtl_rec.VAL_58,10);
777: hr_utility.set_location(p_dtl_rec.VAL_59,10);
778: hr_utility.set_location(p_dtl_rec.VAL_60,10);
771: hr_utility.set_location(p_dtl_rec.VAL_53,10);
772: hr_utility.set_location(p_dtl_rec.VAL_54,10);
773: hr_utility.set_location(p_dtl_rec.VAL_55,10);
774: hr_utility.set_location(p_dtl_rec.VAL_56,10);
775: hr_utility.set_location(p_dtl_rec.VAL_57,10);
776: hr_utility.set_location(p_dtl_rec.VAL_58,10);
777: hr_utility.set_location(p_dtl_rec.VAL_59,10);
778: hr_utility.set_location(p_dtl_rec.VAL_60,10);
779: hr_utility.set_location(p_dtl_rec.VAL_61,10);
772: hr_utility.set_location(p_dtl_rec.VAL_54,10);
773: hr_utility.set_location(p_dtl_rec.VAL_55,10);
774: hr_utility.set_location(p_dtl_rec.VAL_56,10);
775: hr_utility.set_location(p_dtl_rec.VAL_57,10);
776: hr_utility.set_location(p_dtl_rec.VAL_58,10);
777: hr_utility.set_location(p_dtl_rec.VAL_59,10);
778: hr_utility.set_location(p_dtl_rec.VAL_60,10);
779: hr_utility.set_location(p_dtl_rec.VAL_61,10);
780: hr_utility.set_location(p_dtl_rec.VAL_62,10);
773: hr_utility.set_location(p_dtl_rec.VAL_55,10);
774: hr_utility.set_location(p_dtl_rec.VAL_56,10);
775: hr_utility.set_location(p_dtl_rec.VAL_57,10);
776: hr_utility.set_location(p_dtl_rec.VAL_58,10);
777: hr_utility.set_location(p_dtl_rec.VAL_59,10);
778: hr_utility.set_location(p_dtl_rec.VAL_60,10);
779: hr_utility.set_location(p_dtl_rec.VAL_61,10);
780: hr_utility.set_location(p_dtl_rec.VAL_62,10);
781: hr_utility.set_location(p_dtl_rec.VAL_63,10);
774: hr_utility.set_location(p_dtl_rec.VAL_56,10);
775: hr_utility.set_location(p_dtl_rec.VAL_57,10);
776: hr_utility.set_location(p_dtl_rec.VAL_58,10);
777: hr_utility.set_location(p_dtl_rec.VAL_59,10);
778: hr_utility.set_location(p_dtl_rec.VAL_60,10);
779: hr_utility.set_location(p_dtl_rec.VAL_61,10);
780: hr_utility.set_location(p_dtl_rec.VAL_62,10);
781: hr_utility.set_location(p_dtl_rec.VAL_63,10);
782: hr_utility.set_location(p_dtl_rec.VAL_64,10);
775: hr_utility.set_location(p_dtl_rec.VAL_57,10);
776: hr_utility.set_location(p_dtl_rec.VAL_58,10);
777: hr_utility.set_location(p_dtl_rec.VAL_59,10);
778: hr_utility.set_location(p_dtl_rec.VAL_60,10);
779: hr_utility.set_location(p_dtl_rec.VAL_61,10);
780: hr_utility.set_location(p_dtl_rec.VAL_62,10);
781: hr_utility.set_location(p_dtl_rec.VAL_63,10);
782: hr_utility.set_location(p_dtl_rec.VAL_64,10);
783: hr_utility.set_location(p_dtl_rec.VAL_65,10);
776: hr_utility.set_location(p_dtl_rec.VAL_58,10);
777: hr_utility.set_location(p_dtl_rec.VAL_59,10);
778: hr_utility.set_location(p_dtl_rec.VAL_60,10);
779: hr_utility.set_location(p_dtl_rec.VAL_61,10);
780: hr_utility.set_location(p_dtl_rec.VAL_62,10);
781: hr_utility.set_location(p_dtl_rec.VAL_63,10);
782: hr_utility.set_location(p_dtl_rec.VAL_64,10);
783: hr_utility.set_location(p_dtl_rec.VAL_65,10);
784: hr_utility.set_location(p_dtl_rec.VAL_66,10);
777: hr_utility.set_location(p_dtl_rec.VAL_59,10);
778: hr_utility.set_location(p_dtl_rec.VAL_60,10);
779: hr_utility.set_location(p_dtl_rec.VAL_61,10);
780: hr_utility.set_location(p_dtl_rec.VAL_62,10);
781: hr_utility.set_location(p_dtl_rec.VAL_63,10);
782: hr_utility.set_location(p_dtl_rec.VAL_64,10);
783: hr_utility.set_location(p_dtl_rec.VAL_65,10);
784: hr_utility.set_location(p_dtl_rec.VAL_66,10);
785: hr_utility.set_location(p_dtl_rec.VAL_67,10);
778: hr_utility.set_location(p_dtl_rec.VAL_60,10);
779: hr_utility.set_location(p_dtl_rec.VAL_61,10);
780: hr_utility.set_location(p_dtl_rec.VAL_62,10);
781: hr_utility.set_location(p_dtl_rec.VAL_63,10);
782: hr_utility.set_location(p_dtl_rec.VAL_64,10);
783: hr_utility.set_location(p_dtl_rec.VAL_65,10);
784: hr_utility.set_location(p_dtl_rec.VAL_66,10);
785: hr_utility.set_location(p_dtl_rec.VAL_67,10);
786: hr_utility.set_location(p_dtl_rec.VAL_68,10);
779: hr_utility.set_location(p_dtl_rec.VAL_61,10);
780: hr_utility.set_location(p_dtl_rec.VAL_62,10);
781: hr_utility.set_location(p_dtl_rec.VAL_63,10);
782: hr_utility.set_location(p_dtl_rec.VAL_64,10);
783: hr_utility.set_location(p_dtl_rec.VAL_65,10);
784: hr_utility.set_location(p_dtl_rec.VAL_66,10);
785: hr_utility.set_location(p_dtl_rec.VAL_67,10);
786: hr_utility.set_location(p_dtl_rec.VAL_68,10);
787: hr_utility.set_location(p_dtl_rec.VAL_69,10);
780: hr_utility.set_location(p_dtl_rec.VAL_62,10);
781: hr_utility.set_location(p_dtl_rec.VAL_63,10);
782: hr_utility.set_location(p_dtl_rec.VAL_64,10);
783: hr_utility.set_location(p_dtl_rec.VAL_65,10);
784: hr_utility.set_location(p_dtl_rec.VAL_66,10);
785: hr_utility.set_location(p_dtl_rec.VAL_67,10);
786: hr_utility.set_location(p_dtl_rec.VAL_68,10);
787: hr_utility.set_location(p_dtl_rec.VAL_69,10);
788: hr_utility.set_location(p_dtl_rec.VAL_70,10);
781: hr_utility.set_location(p_dtl_rec.VAL_63,10);
782: hr_utility.set_location(p_dtl_rec.VAL_64,10);
783: hr_utility.set_location(p_dtl_rec.VAL_65,10);
784: hr_utility.set_location(p_dtl_rec.VAL_66,10);
785: hr_utility.set_location(p_dtl_rec.VAL_67,10);
786: hr_utility.set_location(p_dtl_rec.VAL_68,10);
787: hr_utility.set_location(p_dtl_rec.VAL_69,10);
788: hr_utility.set_location(p_dtl_rec.VAL_70,10);
789: hr_utility.set_location(p_dtl_rec.VAL_71,10);
782: hr_utility.set_location(p_dtl_rec.VAL_64,10);
783: hr_utility.set_location(p_dtl_rec.VAL_65,10);
784: hr_utility.set_location(p_dtl_rec.VAL_66,10);
785: hr_utility.set_location(p_dtl_rec.VAL_67,10);
786: hr_utility.set_location(p_dtl_rec.VAL_68,10);
787: hr_utility.set_location(p_dtl_rec.VAL_69,10);
788: hr_utility.set_location(p_dtl_rec.VAL_70,10);
789: hr_utility.set_location(p_dtl_rec.VAL_71,10);
790: hr_utility.set_location(p_dtl_rec.VAL_72,10);
783: hr_utility.set_location(p_dtl_rec.VAL_65,10);
784: hr_utility.set_location(p_dtl_rec.VAL_66,10);
785: hr_utility.set_location(p_dtl_rec.VAL_67,10);
786: hr_utility.set_location(p_dtl_rec.VAL_68,10);
787: hr_utility.set_location(p_dtl_rec.VAL_69,10);
788: hr_utility.set_location(p_dtl_rec.VAL_70,10);
789: hr_utility.set_location(p_dtl_rec.VAL_71,10);
790: hr_utility.set_location(p_dtl_rec.VAL_72,10);
791: hr_utility.set_location(p_dtl_rec.VAL_73,10);
784: hr_utility.set_location(p_dtl_rec.VAL_66,10);
785: hr_utility.set_location(p_dtl_rec.VAL_67,10);
786: hr_utility.set_location(p_dtl_rec.VAL_68,10);
787: hr_utility.set_location(p_dtl_rec.VAL_69,10);
788: hr_utility.set_location(p_dtl_rec.VAL_70,10);
789: hr_utility.set_location(p_dtl_rec.VAL_71,10);
790: hr_utility.set_location(p_dtl_rec.VAL_72,10);
791: hr_utility.set_location(p_dtl_rec.VAL_73,10);
792: hr_utility.set_location(p_dtl_rec.VAL_74,10);
785: hr_utility.set_location(p_dtl_rec.VAL_67,10);
786: hr_utility.set_location(p_dtl_rec.VAL_68,10);
787: hr_utility.set_location(p_dtl_rec.VAL_69,10);
788: hr_utility.set_location(p_dtl_rec.VAL_70,10);
789: hr_utility.set_location(p_dtl_rec.VAL_71,10);
790: hr_utility.set_location(p_dtl_rec.VAL_72,10);
791: hr_utility.set_location(p_dtl_rec.VAL_73,10);
792: hr_utility.set_location(p_dtl_rec.VAL_74,10);
793: hr_utility.set_location(p_dtl_rec.VAL_75,10);
786: hr_utility.set_location(p_dtl_rec.VAL_68,10);
787: hr_utility.set_location(p_dtl_rec.VAL_69,10);
788: hr_utility.set_location(p_dtl_rec.VAL_70,10);
789: hr_utility.set_location(p_dtl_rec.VAL_71,10);
790: hr_utility.set_location(p_dtl_rec.VAL_72,10);
791: hr_utility.set_location(p_dtl_rec.VAL_73,10);
792: hr_utility.set_location(p_dtl_rec.VAL_74,10);
793: hr_utility.set_location(p_dtl_rec.VAL_75,10);
794: hr_utility.set_location(p_dtl_rec.CREATED_BY,10);
787: hr_utility.set_location(p_dtl_rec.VAL_69,10);
788: hr_utility.set_location(p_dtl_rec.VAL_70,10);
789: hr_utility.set_location(p_dtl_rec.VAL_71,10);
790: hr_utility.set_location(p_dtl_rec.VAL_72,10);
791: hr_utility.set_location(p_dtl_rec.VAL_73,10);
792: hr_utility.set_location(p_dtl_rec.VAL_74,10);
793: hr_utility.set_location(p_dtl_rec.VAL_75,10);
794: hr_utility.set_location(p_dtl_rec.CREATED_BY,10);
795: hr_utility.set_location(p_dtl_rec.CREATION_DATE,10);
788: hr_utility.set_location(p_dtl_rec.VAL_70,10);
789: hr_utility.set_location(p_dtl_rec.VAL_71,10);
790: hr_utility.set_location(p_dtl_rec.VAL_72,10);
791: hr_utility.set_location(p_dtl_rec.VAL_73,10);
792: hr_utility.set_location(p_dtl_rec.VAL_74,10);
793: hr_utility.set_location(p_dtl_rec.VAL_75,10);
794: hr_utility.set_location(p_dtl_rec.CREATED_BY,10);
795: hr_utility.set_location(p_dtl_rec.CREATION_DATE,10);
796: hr_utility.set_location(p_dtl_rec.LAST_UPDATE_DATE,10);
789: hr_utility.set_location(p_dtl_rec.VAL_71,10);
790: hr_utility.set_location(p_dtl_rec.VAL_72,10);
791: hr_utility.set_location(p_dtl_rec.VAL_73,10);
792: hr_utility.set_location(p_dtl_rec.VAL_74,10);
793: hr_utility.set_location(p_dtl_rec.VAL_75,10);
794: hr_utility.set_location(p_dtl_rec.CREATED_BY,10);
795: hr_utility.set_location(p_dtl_rec.CREATION_DATE,10);
796: hr_utility.set_location(p_dtl_rec.LAST_UPDATE_DATE,10);
797: hr_utility .set_location(p_dtl_rec.LAST_UPDATED_BY,10);
790: hr_utility.set_location(p_dtl_rec.VAL_72,10);
791: hr_utility.set_location(p_dtl_rec.VAL_73,10);
792: hr_utility.set_location(p_dtl_rec.VAL_74,10);
793: hr_utility.set_location(p_dtl_rec.VAL_75,10);
794: hr_utility.set_location(p_dtl_rec.CREATED_BY,10);
795: hr_utility.set_location(p_dtl_rec.CREATION_DATE,10);
796: hr_utility.set_location(p_dtl_rec.LAST_UPDATE_DATE,10);
797: hr_utility .set_location(p_dtl_rec.LAST_UPDATED_BY,10);
798: hr_utility.set_location(p_dtl_rec.LAST_UPDATE_LOGIN,10);
791: hr_utility.set_location(p_dtl_rec.VAL_73,10);
792: hr_utility.set_location(p_dtl_rec.VAL_74,10);
793: hr_utility.set_location(p_dtl_rec.VAL_75,10);
794: hr_utility.set_location(p_dtl_rec.CREATED_BY,10);
795: hr_utility.set_location(p_dtl_rec.CREATION_DATE,10);
796: hr_utility.set_location(p_dtl_rec.LAST_UPDATE_DATE,10);
797: hr_utility .set_location(p_dtl_rec.LAST_UPDATED_BY,10);
798: hr_utility.set_location(p_dtl_rec.LAST_UPDATE_LOGIN,10);
799: hr_utility.set_location(p_dtl_rec.PROGRAM_APPLICATION_ID,10);
792: hr_utility.set_location(p_dtl_rec.VAL_74,10);
793: hr_utility.set_location(p_dtl_rec.VAL_75,10);
794: hr_utility.set_location(p_dtl_rec.CREATED_BY,10);
795: hr_utility.set_location(p_dtl_rec.CREATION_DATE,10);
796: hr_utility.set_location(p_dtl_rec.LAST_UPDATE_DATE,10);
797: hr_utility .set_location(p_dtl_rec.LAST_UPDATED_BY,10);
798: hr_utility.set_location(p_dtl_rec.LAST_UPDATE_LOGIN,10);
799: hr_utility.set_location(p_dtl_rec.PROGRAM_APPLICATION_ID,10);
800: hr_utility.set_location(p_dtl_rec.PROGRAM_ID,10);
793: hr_utility.set_location(p_dtl_rec.VAL_75,10);
794: hr_utility.set_location(p_dtl_rec.CREATED_BY,10);
795: hr_utility.set_location(p_dtl_rec.CREATION_DATE,10);
796: hr_utility.set_location(p_dtl_rec.LAST_UPDATE_DATE,10);
797: hr_utility .set_location(p_dtl_rec.LAST_UPDATED_BY,10);
798: hr_utility.set_location(p_dtl_rec.LAST_UPDATE_LOGIN,10);
799: hr_utility.set_location(p_dtl_rec.PROGRAM_APPLICATION_ID,10);
800: hr_utility.set_location(p_dtl_rec.PROGRAM_ID,10);
801: hr_utility.set_location(p_dtl_rec.PROGRAM_UPDATE_DATE,10);
794: hr_utility.set_location(p_dtl_rec.CREATED_BY,10);
795: hr_utility.set_location(p_dtl_rec.CREATION_DATE,10);
796: hr_utility.set_location(p_dtl_rec.LAST_UPDATE_DATE,10);
797: hr_utility .set_location(p_dtl_rec.LAST_UPDATED_BY,10);
798: hr_utility.set_location(p_dtl_rec.LAST_UPDATE_LOGIN,10);
799: hr_utility.set_location(p_dtl_rec.PROGRAM_APPLICATION_ID,10);
800: hr_utility.set_location(p_dtl_rec.PROGRAM_ID,10);
801: hr_utility.set_location(p_dtl_rec.PROGRAM_UPDATE_DATE,10);
802: hr_utility.set_location(p_dtl_rec.REQUEST_ID,10);
795: hr_utility.set_location(p_dtl_rec.CREATION_DATE,10);
796: hr_utility.set_location(p_dtl_rec.LAST_UPDATE_DATE,10);
797: hr_utility .set_location(p_dtl_rec.LAST_UPDATED_BY,10);
798: hr_utility.set_location(p_dtl_rec.LAST_UPDATE_LOGIN,10);
799: hr_utility.set_location(p_dtl_rec.PROGRAM_APPLICATION_ID,10);
800: hr_utility.set_location(p_dtl_rec.PROGRAM_ID,10);
801: hr_utility.set_location(p_dtl_rec.PROGRAM_UPDATE_DATE,10);
802: hr_utility.set_location(p_dtl_rec.REQUEST_ID,10);
803: hr_utility.set_location(p_dtl_rec.OBJECT_VERSION_NUMBER,10);
796: hr_utility.set_location(p_dtl_rec.LAST_UPDATE_DATE,10);
797: hr_utility .set_location(p_dtl_rec.LAST_UPDATED_BY,10);
798: hr_utility.set_location(p_dtl_rec.LAST_UPDATE_LOGIN,10);
799: hr_utility.set_location(p_dtl_rec.PROGRAM_APPLICATION_ID,10);
800: hr_utility.set_location(p_dtl_rec.PROGRAM_ID,10);
801: hr_utility.set_location(p_dtl_rec.PROGRAM_UPDATE_DATE,10);
802: hr_utility.set_location(p_dtl_rec.REQUEST_ID,10);
803: hr_utility.set_location(p_dtl_rec.OBJECT_VERSION_NUMBER,10);
804: hr_utility.set_location(p_dtl_rec.PRMY_SORT_VAL,10);
797: hr_utility .set_location(p_dtl_rec.LAST_UPDATED_BY,10);
798: hr_utility.set_location(p_dtl_rec.LAST_UPDATE_LOGIN,10);
799: hr_utility.set_location(p_dtl_rec.PROGRAM_APPLICATION_ID,10);
800: hr_utility.set_location(p_dtl_rec.PROGRAM_ID,10);
801: hr_utility.set_location(p_dtl_rec.PROGRAM_UPDATE_DATE,10);
802: hr_utility.set_location(p_dtl_rec.REQUEST_ID,10);
803: hr_utility.set_location(p_dtl_rec.OBJECT_VERSION_NUMBER,10);
804: hr_utility.set_location(p_dtl_rec.PRMY_SORT_VAL,10);
805: hr_utility.set_location(p_dtl_rec.SCND_SORT_VAL,10);
798: hr_utility.set_location(p_dtl_rec.LAST_UPDATE_LOGIN,10);
799: hr_utility.set_location(p_dtl_rec.PROGRAM_APPLICATION_ID,10);
800: hr_utility.set_location(p_dtl_rec.PROGRAM_ID,10);
801: hr_utility.set_location(p_dtl_rec.PROGRAM_UPDATE_DATE,10);
802: hr_utility.set_location(p_dtl_rec.REQUEST_ID,10);
803: hr_utility.set_location(p_dtl_rec.OBJECT_VERSION_NUMBER,10);
804: hr_utility.set_location(p_dtl_rec.PRMY_SORT_VAL,10);
805: hr_utility.set_location(p_dtl_rec.SCND_SORT_VAL,10);
806: hr_utility.set_location(p_dtl_rec.THRD_SORT_VAL,10);
799: hr_utility.set_location(p_dtl_rec.PROGRAM_APPLICATION_ID,10);
800: hr_utility.set_location(p_dtl_rec.PROGRAM_ID,10);
801: hr_utility.set_location(p_dtl_rec.PROGRAM_UPDATE_DATE,10);
802: hr_utility.set_location(p_dtl_rec.REQUEST_ID,10);
803: hr_utility.set_location(p_dtl_rec.OBJECT_VERSION_NUMBER,10);
804: hr_utility.set_location(p_dtl_rec.PRMY_SORT_VAL,10);
805: hr_utility.set_location(p_dtl_rec.SCND_SORT_VAL,10);
806: hr_utility.set_location(p_dtl_rec.THRD_SORT_VAL,10);
807: hr_utility.set_location(p_dtl_rec.TRANS_SEQ_NUM,10);
800: hr_utility.set_location(p_dtl_rec.PROGRAM_ID,10);
801: hr_utility.set_location(p_dtl_rec.PROGRAM_UPDATE_DATE,10);
802: hr_utility.set_location(p_dtl_rec.REQUEST_ID,10);
803: hr_utility.set_location(p_dtl_rec.OBJECT_VERSION_NUMBER,10);
804: hr_utility.set_location(p_dtl_rec.PRMY_SORT_VAL,10);
805: hr_utility.set_location(p_dtl_rec.SCND_SORT_VAL,10);
806: hr_utility.set_location(p_dtl_rec.THRD_SORT_VAL,10);
807: hr_utility.set_location(p_dtl_rec.TRANS_SEQ_NUM,10);
808: hr_utility.set_location(p_dtl_rec.RCRD_SEQ_NUM,10);
801: hr_utility.set_location(p_dtl_rec.PROGRAM_UPDATE_DATE,10);
802: hr_utility.set_location(p_dtl_rec.REQUEST_ID,10);
803: hr_utility.set_location(p_dtl_rec.OBJECT_VERSION_NUMBER,10);
804: hr_utility.set_location(p_dtl_rec.PRMY_SORT_VAL,10);
805: hr_utility.set_location(p_dtl_rec.SCND_SORT_VAL,10);
806: hr_utility.set_location(p_dtl_rec.THRD_SORT_VAL,10);
807: hr_utility.set_location(p_dtl_rec.TRANS_SEQ_NUM,10);
808: hr_utility.set_location(p_dtl_rec.RCRD_SEQ_NUM,10);
809: INSERT INTO ben_ext_rslt_dtl
802: hr_utility.set_location(p_dtl_rec.REQUEST_ID,10);
803: hr_utility.set_location(p_dtl_rec.OBJECT_VERSION_NUMBER,10);
804: hr_utility.set_location(p_dtl_rec.PRMY_SORT_VAL,10);
805: hr_utility.set_location(p_dtl_rec.SCND_SORT_VAL,10);
806: hr_utility.set_location(p_dtl_rec.THRD_SORT_VAL,10);
807: hr_utility.set_location(p_dtl_rec.TRANS_SEQ_NUM,10);
808: hr_utility.set_location(p_dtl_rec.RCRD_SEQ_NUM,10);
809: INSERT INTO ben_ext_rslt_dtl
810: (EXT_RSLT_DTL_ID
803: hr_utility.set_location(p_dtl_rec.OBJECT_VERSION_NUMBER,10);
804: hr_utility.set_location(p_dtl_rec.PRMY_SORT_VAL,10);
805: hr_utility.set_location(p_dtl_rec.SCND_SORT_VAL,10);
806: hr_utility.set_location(p_dtl_rec.THRD_SORT_VAL,10);
807: hr_utility.set_location(p_dtl_rec.TRANS_SEQ_NUM,10);
808: hr_utility.set_location(p_dtl_rec.RCRD_SEQ_NUM,10);
809: INSERT INTO ben_ext_rslt_dtl
810: (EXT_RSLT_DTL_ID
811: ,EXT_RSLT_ID
804: hr_utility.set_location(p_dtl_rec.PRMY_SORT_VAL,10);
805: hr_utility.set_location(p_dtl_rec.SCND_SORT_VAL,10);
806: hr_utility.set_location(p_dtl_rec.THRD_SORT_VAL,10);
807: hr_utility.set_location(p_dtl_rec.TRANS_SEQ_NUM,10);
808: hr_utility.set_location(p_dtl_rec.RCRD_SEQ_NUM,10);
809: INSERT INTO ben_ext_rslt_dtl
810: (EXT_RSLT_DTL_ID
811: ,EXT_RSLT_ID
812: ,BUSINESS_GROUP_ID
999: ,p_dtl_rec.THRD_SORT_VAL
1000: ,p_dtl_rec.TRANS_SEQ_NUM
1001: ,p_dtl_rec.RCRD_SEQ_NUM
1002: );
1003: Hr_Utility.set_location('Leaving :'||l_proc_name, 25);
1004: -- hr_utility.trace_off;
1005: RETURN;
1006:
1007: EXCEPTION
1000: ,p_dtl_rec.TRANS_SEQ_NUM
1001: ,p_dtl_rec.RCRD_SEQ_NUM
1002: );
1003: Hr_Utility.set_location('Leaving :'||l_proc_name, 25);
1004: -- hr_utility.trace_off;
1005: RETURN;
1006:
1007: EXCEPTION
1008: WHEN Others THEN
1005: RETURN;
1006:
1007: EXCEPTION
1008: WHEN Others THEN
1009: Hr_Utility.set_location('Leaving :'||l_proc_name, 25);
1010: p_dtl_rec := l_dtl_rec_nc;
1011: RAISE;
1012: END Ins_Rslt_Dtl;
1013:
1135: l_balance_amount NUMBER:=0;
1136: l_bal_total_amt NUMBER:=0;
1137: asgact_rec csr_asg_act%ROWTYPE;
1138: BEGIN
1139: Hr_Utility.set_location(' Entering: ' ||l_proc_name,10);
1140:
1141: OPEN csr_get_def_bal_type_id(p_balance_name,p_dimension_name);
1142: FETCH csr_get_def_bal_type_id INTO l_def_bal_type_id;
1143: CLOSE csr_get_def_bal_type_id;
1140:
1141: OPEN csr_get_def_bal_type_id(p_balance_name,p_dimension_name);
1142: FETCH csr_get_def_bal_type_id INTO l_def_bal_type_id;
1143: CLOSE csr_get_def_bal_type_id;
1144: Hr_Utility.set_location(' l_def_bal_type_id ' ||l_def_bal_type_id,15);
1145: IF l_def_bal_type_id IS NOT NULL THEN
1146: --Get the Assignment action ids for this assignment
1147: OPEN csr_asg_act(c_assignment_id => p_assignment_id
1148: ,c_payroll_id => g_extract_params (p_business_group_id).payroll_id
1156: l_balance_amount := Pay_Balance_Pkg.get_value
1157: (p_defined_balance_id => l_def_bal_type_id,
1158: p_assignment_action_id => asgact_rec.assignment_action_id );
1159: l_bal_total_amt := l_bal_total_amt + Nvl(l_balance_amount,0);
1160: Hr_Utility.set_location(' l_balance_amount ' ||l_balance_amount,25);
1161: Hr_Utility.set_location(' l_bal_total_amt ' ||l_bal_total_amt,25);
1162: END LOOP; -- For Loop
1163: CLOSE csr_asg_act;
1164: END IF; -- If l_def_bal_type_id
1157: (p_defined_balance_id => l_def_bal_type_id,
1158: p_assignment_action_id => asgact_rec.assignment_action_id );
1159: l_bal_total_amt := l_bal_total_amt + Nvl(l_balance_amount,0);
1160: Hr_Utility.set_location(' l_balance_amount ' ||l_balance_amount,25);
1161: Hr_Utility.set_location(' l_bal_total_amt ' ||l_bal_total_amt,25);
1162: END LOOP; -- For Loop
1163: CLOSE csr_asg_act;
1164: END IF; -- If l_def_bal_type_id
1165:
1162: END LOOP; -- For Loop
1163: CLOSE csr_asg_act;
1164: END IF; -- If l_def_bal_type_id
1165:
1166: Hr_Utility.set_location(' Leaving: ' ||l_proc_name,50);
1167: RETURN l_bal_total_amt;
1168:
1169: EXCEPTION
1170: WHEN OTHERS THEN
1169: EXCEPTION
1170: WHEN OTHERS THEN
1171: --p_error_message :='SQL-ERRM :'||SQLERRM;
1172: CLOSE csr_asg_act;
1173: Hr_Utility.set_location('..'||'SQL-ERRM :'||SQLERRM,30);
1174: Hr_Utility.set_location('Exception Occured Leaving: '||l_proc_name,40);
1175: RETURN 0;
1176: END Get_Balance_Value;
1177:
1170: WHEN OTHERS THEN
1171: --p_error_message :='SQL-ERRM :'||SQLERRM;
1172: CLOSE csr_asg_act;
1173: Hr_Utility.set_location('..'||'SQL-ERRM :'||SQLERRM,30);
1174: Hr_Utility.set_location('Exception Occured Leaving: '||l_proc_name,40);
1175: RETURN 0;
1176: END Get_Balance_Value;
1177:
1178: -- ===============================================================================
1199: l_ret_val Number:=0;
1200: l_st_dt_of_ptp varchar2(8);
1201: --
1202: BEGIN
1203: Hr_Utility.set_location('Entering: '||l_proc_name, 10);
1204: OPEN csr_get_st_dt_of_ptp(p_assignment_id,p_date_earned);
1205: FETCH csr_get_st_dt_of_ptp INTO l_st_dt_of_ptp;
1206: Hr_Utility.set_location('l_st_dt_of_ptp: '||l_st_dt_of_ptp, 20);
1207: IF (csr_get_st_dt_of_ptp%FOUND) THEN
1202: BEGIN
1203: Hr_Utility.set_location('Entering: '||l_proc_name, 10);
1204: OPEN csr_get_st_dt_of_ptp(p_assignment_id,p_date_earned);
1205: FETCH csr_get_st_dt_of_ptp INTO l_st_dt_of_ptp;
1206: Hr_Utility.set_location('l_st_dt_of_ptp: '||l_st_dt_of_ptp, 20);
1207: IF (csr_get_st_dt_of_ptp%FOUND) THEN
1208: p_data_element_value:=l_st_dt_of_ptp;
1209: l_ret_val:=0;
1210: END IF;
1207: IF (csr_get_st_dt_of_ptp%FOUND) THEN
1208: p_data_element_value:=l_st_dt_of_ptp;
1209: l_ret_val:=0;
1210: END IF;
1211: Hr_Utility.set_location('p_data_element_value: '||p_data_element_value, 30);
1212: CLOSE csr_get_st_dt_of_ptp;
1213: Hr_Utility.set_location('Leaving: '||l_proc_name, 60);
1214: RETURN l_ret_val;
1215: EXCEPTION
1209: l_ret_val:=0;
1210: END IF;
1211: Hr_Utility.set_location('p_data_element_value: '||p_data_element_value, 30);
1212: CLOSE csr_get_st_dt_of_ptp;
1213: Hr_Utility.set_location('Leaving: '||l_proc_name, 60);
1214: RETURN l_ret_val;
1215: EXCEPTION
1216: WHEN Others THEN
1217: p_error_message :='SQL-ERRM :'||SQLERRM;
1214: RETURN l_ret_val;
1215: EXCEPTION
1216: WHEN Others THEN
1217: p_error_message :='SQL-ERRM :'||SQLERRM;
1218: Hr_Utility.set_location('..'||p_error_message,85);
1219: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
1220: RETURN -1;
1221: END Get_Start_Date_PTP;
1222:
1215: EXCEPTION
1216: WHEN Others THEN
1217: p_error_message :='SQL-ERRM :'||SQLERRM;
1218: Hr_Utility.set_location('..'||p_error_message,85);
1219: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
1220: RETURN -1;
1221: END Get_Start_Date_PTP;
1222:
1223: -- ===============================================================================
1301: l_bgid per_all_assignments_f.business_group_id%TYPE;
1302:
1303:
1304: BEGIN
1305: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
1306: OPEN c_get_org_id;
1307: FETCH c_get_org_id INTO l_org_id,l_bgid;
1308: CLOSE c_get_org_id;
1309: Hr_Utility.set_location('p_ext_dtl_rcd_id: '||p_ext_dtl_rcd_id , 5);
1305: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
1306: OPEN c_get_org_id;
1307: FETCH c_get_org_id INTO l_org_id,l_bgid;
1308: CLOSE c_get_org_id;
1309: Hr_Utility.set_location('p_ext_dtl_rcd_id: '||p_ext_dtl_rcd_id , 5);
1310: Hr_Utility .set_location('p_assignment_id: '||p_assignment_id, 5);
1311: FOR i IN csr_rule_ele( c_ext_rcd_id => p_ext_dtl_rcd_id )
1312: LOOP
1313: OPEN csr_ff_type (c_formula_type_id => i.data_elmt_rl
1306: OPEN c_get_org_id;
1307: FETCH c_get_org_id INTO l_org_id,l_bgid;
1308: CLOSE c_get_org_id;
1309: Hr_Utility.set_location('p_ext_dtl_rcd_id: '||p_ext_dtl_rcd_id , 5);
1310: Hr_Utility .set_location('p_assignment_id: '||p_assignment_id, 5);
1311: FOR i IN csr_rule_ele( c_ext_rcd_id => p_ext_dtl_rcd_id )
1312: LOOP
1313: OPEN csr_ff_type (c_formula_type_id => i.data_elmt_rl
1314: ,c_effective_date => p_effective_date);
1313: OPEN csr_ff_type (c_formula_type_id => i.data_elmt_rl
1314: ,c_effective_date => p_effective_date);
1315: FETCH csr_ff_type INTO l_formula_type_id ;
1316: CLOSE csr_ff_type;
1317: Hr_Utility.set_location('l_formula_type_id: '||l_formula_type_id, 5);
1318:
1319: IF l_formula_type_id = -413 THEN -- person level rule
1320: l_outputs := Benutils.formula
1321: (p_formula_id => i.data_elmt_rl
1339: BEGIN
1340: IF i.frmt_mask_lookup_cd IS NOT NULL AND
1341: l_ff_value IS NOT NULL THEN
1342: IF Substr(i.frmt_mask_lookup_cd,1,1) = 'N' THEN
1343: Hr_Utility.set_location('..Applying NUMBER format mask :ben_ext_fmt.apply_format_mask' ,50);
1344: l_ff_value_fmt := Ben_Ext_Fmt .apply_format_mask (To_Number(l_ff_value), i.frmt_mask_cd);
1345: l_ff_value := l_ff_value_fmt;
1346: ELSIF Substr(i.frmt_mask_lookup_cd,1,1) = 'D' THEN
1347: Hr_Utility.set_location('..Applying Date format mask :ben_ext_fmt.apply_format_mask' ,55);
1343: Hr_Utility.set_location('..Applying NUMBER format mask :ben_ext_fmt.apply_format_mask' ,50);
1344: l_ff_value_fmt := Ben_Ext_Fmt .apply_format_mask (To_Number(l_ff_value), i.frmt_mask_cd);
1345: l_ff_value := l_ff_value_fmt;
1346: ELSIF Substr(i.frmt_mask_lookup_cd,1,1) = 'D' THEN
1347: Hr_Utility.set_location('..Applying Date format mask :ben_ext_fmt.apply_format_mask' ,55);
1348: l_ff_value_fmt := Ben_Ext_Fmt .apply_format_mask (Fnd_Date.canonical_to_date(l_ff_value),
1349: i.frmt_mask_cd);
1350: l_ff_value := l_ff_value_fmt;
1351: END IF;
1362: END IF;
1363: END LOOP; --For i in csr_rule_ele
1364: p_rslt_rec.prmy_sort_val := p_assignment_id;
1365: Ins_Rslt_Dtl(p_dtl_rec => p_rslt_rec);
1366: Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
1367: EXCEPTION
1368: WHEN Others THEN
1369: Hr_Utility.set_location('..error',85);
1370: Hr_Utility.set_location('SQL-ERRM :'||SQLERRM,87);
1365: Ins_Rslt_Dtl(p_dtl_rec => p_rslt_rec);
1366: Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
1367: EXCEPTION
1368: WHEN Others THEN
1369: Hr_Utility.set_location('..error',85);
1370: Hr_Utility.set_location('SQL-ERRM :'||SQLERRM,87);
1371: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
1372: END Process_Ext_Rslt_Dtl_Rec;
1373:
1366: Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
1367: EXCEPTION
1368: WHEN Others THEN
1369: Hr_Utility.set_location('..error',85);
1370: Hr_Utility.set_location('SQL-ERRM :'||SQLERRM,87);
1371: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
1372: END Process_Ext_Rslt_Dtl_Rec;
1373:
1374: -- =============================================================================
1367: EXCEPTION
1368: WHEN Others THEN
1369: Hr_Utility.set_location('..error',85);
1370: Hr_Utility.set_location('SQL-ERRM :'||SQLERRM,87);
1371: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
1372: END Process_Ext_Rslt_Dtl_Rec;
1373:
1374: -- =============================================================================
1375: -- Check_Addl_Assigs: Check if the person has any secondary active assigs within
1393: l_asg_type per_all_assignments_f.assignment_type%TYPE;
1394: l_person_id per_all_people_f.person_id%TYPE;
1395: l_assignment_id per_all_assignments_f.assignment_id%TYPE;
1396: BEGIN
1397: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
1398: bg_id := p_business_group_id;
1399:
1400: IF NOT g_primary_assig.EXISTS(p_assignment_id) THEN
1401: l_return_value := 'NOTFOUND';
1398: bg_id := p_business_group_id;
1399:
1400: IF NOT g_primary_assig.EXISTS(p_assignment_id) THEN
1401: l_return_value := 'NOTFOUND';
1402: Hr_Utility.set_location('..Not a Valid assignment: '||p_assignment_id, 6);
1403: RETURN l_return_value;
1404: ELSIF g_primary_assig (p_assignment_id).assignment_type IN ('B','E') THEN
1405: Hr_Utility.set_location('..Valid Assignment Type B : '||p_assignment_id, 6);
1406: l_person_id := g_primary_assig (p_assignment_id).person_id;
1401: l_return_value := 'NOTFOUND';
1402: Hr_Utility.set_location('..Not a Valid assignment: '||p_assignment_id, 6);
1403: RETURN l_return_value;
1404: ELSIF g_primary_assig (p_assignment_id).assignment_type IN ('B','E') THEN
1405: Hr_Utility.set_location('..Valid Assignment Type B : '||p_assignment_id, 6);
1406: l_person_id := g_primary_assig (p_assignment_id).person_id;
1407: l_asg_type := g_primary_assig (p_assignment_id).assignment_type;
1408: -- Check if there are any other assignments which might be active within the
1409: -- specified extract date range
1417: l_sec_assg_rec := sec_asg_rec;
1418: l_criteria_value := 'N';
1419: l_effective_date := Least(g_extract_params (bg_id).extract_end_date,
1420: l_sec_assg_rec.effective_end_date);
1421: Hr_Utility.set_location('..Checking for assignment id: '||
1422: l_sec_assg_rec.assignment_id, 7);
1423: Hr_Utility.set_location('..p_effective_date : '||l_effective_date, 7);
1424: -- Call the main criteria function for this assignment to check if its a
1425: -- valid assignment that can be reported based on the criteria specified.
1419: l_effective_date := Least(g_extract_params (bg_id).extract_end_date,
1420: l_sec_assg_rec.effective_end_date);
1421: Hr_Utility.set_location('..Checking for assignment id: '||
1422: l_sec_assg_rec.assignment_id, 7);
1423: Hr_Utility.set_location('..p_effective_date : '||l_effective_date, 7);
1424: -- Call the main criteria function for this assignment to check if its a
1425: -- valid assignment that can be reported based on the criteria specified.
1426: --Note that this function adds the assignment id in its PL SQL table
1427: l_criteria_value := Pension_Criteria_Full_Profile
1435: l_return_value := 'FOUND';
1436: END IF;
1437: END LOOP; -- FOR sec_asg_rec
1438: END IF;
1439: Hr_Utility.set_location('..Assignment Count : '||g_primary_assig.Count, 7);
1440: Hr_Utility.set_location('..l_person_id : '||l_person_id, 7);
1441: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
1442: RETURN l_return_value;
1443: EXCEPTION
1436: END IF;
1437: END LOOP; -- FOR sec_asg_rec
1438: END IF;
1439: Hr_Utility.set_location('..Assignment Count : '||g_primary_assig.Count, 7);
1440: Hr_Utility.set_location('..l_person_id : '||l_person_id, 7);
1441: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
1442: RETURN l_return_value;
1443: EXCEPTION
1444: WHEN Others THEN
1437: END LOOP; -- FOR sec_asg_rec
1438: END IF;
1439: Hr_Utility.set_location('..Assignment Count : '||g_primary_assig.Count, 7);
1440: Hr_Utility.set_location('..l_person_id : '||l_person_id, 7);
1441: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
1442: RETURN l_return_value;
1443: EXCEPTION
1444: WHEN Others THEN
1445: p_error_message :='SQL-ERRM :'||SQLERRM;
1442: RETURN l_return_value;
1443: EXCEPTION
1444: WHEN Others THEN
1445: p_error_message :='SQL-ERRM :'||SQLERRM;
1446: Hr_Utility.set_location('..'||p_error_message,85);
1447: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
1448: RETURN l_return_value;
1449: END Check_Addl_Assigs;
1450:
1443: EXCEPTION
1444: WHEN Others THEN
1445: p_error_message :='SQL-ERRM :'||SQLERRM;
1446: Hr_Utility.set_location('..'||p_error_message,85);
1447: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
1448: RETURN l_return_value;
1449: END Check_Addl_Assigs;
1450:
1451: -- =============================================================================
1477: l_ext_rslt_id NUMBER;
1478: l_ext_dfn_id NUMBER;
1479:
1480: BEGIN
1481: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
1482: bg_id := p_business_group_id;
1483: l_ext_rslt_id := Ben_Ext_Thread.g_ext_rslt_id;
1484: l_ext_dfn_id := Ben_Ext_Thread.g_ext_dfn_id;
1485:
1494: (c_rcd_type_cd => 'D')-- D=Detail, T=Total, H-Header
1495: LOOP
1496: l_ext_rcd_id := csr_rcd_rec.ext_rcd_id;
1497:
1498: Hr_Utility.set_location('l_ext_rcd_id: '||l_ext_rcd_id, 5);
1499:
1500: --These are single processing reCords
1501: IF g_ext_rcds(l_ext_rcd_id ).record_number IN
1502: ('010','10h','020','030','040','060','070','080','081')THEN
1500: --These are single processing reCords
1501: IF g_ext_rcds(l_ext_rcd_id ).record_number IN
1502: ('010','10h','020','030','040','060','070','080','081')THEN
1503: l_record_num := g_ext_rcds(l_ext_rcd_id).record_number;
1504: Hr_Utility.set_location('l_record_num: '||l_record_num, 5);
1505: OPEN csr_rslt_dtl
1506: (c_person_id => p_person_id
1507: ,c_ext_rslt_id => Ben_Ext_Thread.g_ext_rslt_id
1508: ,c_ext_dtl_rcd_id => l_ext_rcd_id
1567: l_organization_id := g_primary_assig(p_assignment_id).organization_id;
1568: l_business_group_id := p_business_group_id;
1569: l_effective_date := Least(g_extract_params(bg_id).extract_end_date ,
1570: g_primary_assig (p_assignment_id).effective_end_date);
1571: Hr_Utility.set_location('l_record_num: '||l_record_num, 25);
1572: l_return_value := Chk_If_Req_To_Extract
1573: (p_assignment_id => l_assignment_id
1574: ,p_business_group_id => l_business_group_id
1575: ,p_effective_date => l_effective_date
1574: ,p_business_group_id => l_business_group_id
1575: ,p_effective_date => l_effective_date
1576: ,p_record_num => l_record_num
1577: ,p_error_message => p_error_message);
1578: Hr_Utility.set_location('l_return_value: '||l_return_value, 25);
1579: IF l_return_value = 'Y' THEN
1580: -- Re-Process the person level rule based data-element for the record
1581: -- along with appropiate effective date and assignment id
1582: Process_Ext_Rslt_Dtl_Rec
1590: END IF; -- IF l_return_value = 'Y'
1591: END IF;
1592: END LOOP;
1593:
1594: Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
1595: END Create_Addl_Assignments;
1596:
1597: -- =============================================================================
1598: -- Process_Addl_Assigs: Process all the assigs in the PL/SQL table for the
1623: l_assignment_id per_all_assignments_f.assignment_id%TYPE;
1624: l_mutli_assig Varchar2(150);
1625: BEGIN
1626:
1627: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
1628:
1629: l_mutli_assig := Check_Addl_Assigs
1630: (p_assignment_id => p_assignment_id
1631: ,p_business_group_id => p_business_group_id
1642: -- created and re-calculate the person level based fast-formulas.
1643: l_assignment_id := g_primary_assig.FIRST;
1644: WHILE l_assignment_id IS NOT NULL
1645: LOOP
1646: Hr_Utility.set_location('..Checking for assignment : '||l_assignment_id, 7);
1647: IF g_primary_assig (l_assignment_id).person_id = l_person_id AND
1648: l_assignment_id <> p_assignment_id AND
1649: g_primary_assig (l_assignment_id).Assignment_Type = 'E' THEN
1650:
1647: IF g_primary_assig (l_assignment_id).person_id = l_person_id AND
1648: l_assignment_id <> p_assignment_id AND
1649: g_primary_assig (l_assignment_id).Assignment_Type = 'E' THEN
1650:
1651: Hr_Utility.set_location('..Valid Assignment : '||l_assignment_id, 8);
1652: Hr_Utility.set_location('..l_no_asg_action : '||l_no_asg_action, 8);
1653:
1654: Create_Addl_Assignments
1655: (p_assignment_id => l_assignment_id
1648: l_assignment_id <> p_assignment_id AND
1649: g_primary_assig (l_assignment_id).Assignment_Type = 'E' THEN
1650:
1651: Hr_Utility.set_location('..Valid Assignment : '||l_assignment_id, 8);
1652: Hr_Utility.set_location('..l_no_asg_action : '||l_no_asg_action, 8);
1653:
1654: Create_Addl_Assignments
1655: (p_assignment_id => l_assignment_id
1656: ,p_business_group_id => p_business_group_id
1725: END IF;
1726: l_assignment_id := g_primary_assig .NEXT(l_assignment_id);
1727: END LOOP;
1728:
1729: Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
1730: RETURN 0;
1731:
1732: EXCEPTION
1733: WHEN Others THEN
1731:
1732: EXCEPTION
1733: WHEN Others THEN
1734: p_error_message :='SQL-ERRM :'||SQLERRM;
1735: Hr_Utility.set_location('..'||p_error_message,85);
1736: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
1737: RETURN -1;
1738: END Process_Addl_Assigs;
1739: -- ===============================================================================
1732: EXCEPTION
1733: WHEN Others THEN
1734: p_error_message :='SQL-ERRM :'||SQLERRM;
1735: Hr_Utility.set_location('..'||p_error_message,85);
1736: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
1737: RETURN -1;
1738: END Process_Addl_Assigs;
1739: -- ===============================================================================
1740: -- ~ Get_Country_Code : Function to get the Country Code
1759: l_proc_name Varchar2(150) := g_proc_name ||'Get_Country_Code';
1760: l_ret_val number:=0;
1761: l_country_code varchar2(5);
1762: BEGIN
1763: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
1764:
1765: OPEN csr_get_country_code(p_business_group_id);
1766: FETCH csr_get_country_code INTO l_country_code;
1767: IF csr_get_country_code%FOUND THEN
1767: IF csr_get_country_code%FOUND THEN
1768: p_data_element_value:=l_country_code;
1769: l_ret_val := 0;
1770: END IF;
1771: Hr_Utility.set_location('l_country_code: '||l_country_code, 30);
1772: CLOSE csr_get_country_code;
1773: Hr_Utility.set_location('Leaving: '||l_proc_name, 60);
1774: RETURN l_ret_val;
1775: EXCEPTION
1769: l_ret_val := 0;
1770: END IF;
1771: Hr_Utility.set_location('l_country_code: '||l_country_code, 30);
1772: CLOSE csr_get_country_code;
1773: Hr_Utility.set_location('Leaving: '||l_proc_name, 60);
1774: RETURN l_ret_val;
1775: EXCEPTION
1776: WHEN Others THEN
1777: p_error_message :='SQL-ERRM :'||SQLERRM;
1774: RETURN l_ret_val;
1775: EXCEPTION
1776: WHEN Others THEN
1777: p_error_message :='SQL-ERRM :'||SQLERRM;
1778: Hr_Utility.set_location('..'||p_error_message,85);
1779: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
1780: RETURN 1;
1781: END Get_Country_Code;
1782:
1775: EXCEPTION
1776: WHEN Others THEN
1777: p_error_message :='SQL-ERRM :'||SQLERRM;
1778: Hr_Utility.set_location('..'||p_error_message,85);
1779: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
1780: RETURN 1;
1781: END Get_Country_Code;
1782:
1783: -- =============================================================================
1804: l_proc_name Varchar2(150) := g_proc_name ||'Pension_Extract_Process';
1805:
1806: BEGIN
1807:
1808: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
1809: g_conc_request_id := Fnd_Global.conc_request_id;
1810:
1811: IF p_end_date < p_start_date THEN
1812: Fnd_Message.set_name('PQP','PQP_230869_END_BEFORE_START');
1822: --
1823: -- Insert into pay_us_rpt_totals so that we can refer to these parameters
1824: -- when we call the criteria formula for the pension extract.
1825: --
1826: hr_utility.set_location('inserting into rpt totals : '||p_business_group_id,20);
1827: INSERT INTO pay_us_rpt_totals
1828: (session_id -- Session id
1829: ,organization_name -- Concurrent Program Name
1830: ,business_group_id -- Business Group
1862: --
1863: -- Call the actual benefit extract process with the effective date as the
1864: -- extract end date along with the ext def. id and business group id.
1865: --
1866: Hr_Utility.set_location('..Calling Benefit Ext Process'||l_proc_name, 6);
1867: Ben_Ext_Thread.process
1868: (errbuf => l_errbuff,
1869: retcode => l_retcode,
1870: p_benefit_action_id => NULL,
1871: p_ext_dfn_id => p_ext_dfn_id,
1872: p_effective_date => p_end_date,
1873: p_business_group_id => p_business_group_id);
1874:
1875: Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
1876:
1877: --hr_utility.trace_off;
1878: EXCEPTION
1879: WHEN Others THEN
1873: p_business_group_id => p_business_group_id);
1874:
1875: Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
1876:
1877: --hr_utility.trace_off;
1878: EXCEPTION
1879: WHEN Others THEN
1880: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
1881: RAISE;
1876:
1877: --hr_utility.trace_off;
1878: EXCEPTION
1879: WHEN Others THEN
1880: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
1881: RAISE;
1882: END Pension_Extract_Process;
1883:
1884: -- =============================================================================
2329: l_period_end_date DATE;
2330: l_period_start_date2 DATE;
2331: l_prev_ptp_std NUMBER;
2332: BEGIN
2333: --hr_utility.trace_on(null,'SS');
2334:
2335: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
2336: bg_id := p_business_group_id;
2337: g_ptp_index := 0;
2331: l_prev_ptp_std NUMBER;
2332: BEGIN
2333: --hr_utility.trace_on(null,'SS');
2334:
2335: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
2336: bg_id := p_business_group_id;
2337: g_ptp_index := 0;
2338: g_retro_ptp_count := 0;
2339: l_ext_rslt_id := Ben_Ext_Thread.g_ext_rslt_id;
2339: l_ext_rslt_id := Ben_Ext_Thread.g_ext_rslt_id;
2340: l_ext_dfn_id := Ben_Ext_Thread.g_ext_dfn_id;
2341: -- Executed only once during extract run
2342: IF NOT g_extract_params .EXISTS(bg_id) THEN
2343: Hr_Utility.set_location('..Exract Params PL/SQL not populated ' ,7);
2344: -- Get the extract type, Changes extract or Full Profile
2345: OPEN csr_ext_attr(c_ext_dfn_id=> l_ext_dfn_id);
2346: FETCH csr_ext_attr INTO l_ext_dfn_type;
2347: CLOSE csr_ext_attr;
2344: -- Get the extract type, Changes extract or Full Profile
2345: OPEN csr_ext_attr(c_ext_dfn_id=> l_ext_dfn_id);
2346: FETCH csr_ext_attr INTO l_ext_dfn_type;
2347: CLOSE csr_ext_attr;
2348: Hr_Utility.set_location('..After cursor csr_ext_attr',9);
2349: -- Get the Conc. request id to get the params
2350: OPEN csr_req_id(c_ext_rslt_id => l_ext_rslt_id
2351: ,c_ext_dfn_id => l_ext_dfn_id
2352: ,c_business_group_id => p_business_group_id);
2351: ,c_ext_dfn_id => l_ext_dfn_id
2352: ,c_business_group_id => p_business_group_id);
2353: FETCH csr_req_id INTO l_conc_reqest_id;
2354: CLOSE csr_req_id;
2355: Hr_Utility.set_location('..After Conc.Request id cursor csr_req_id',11);
2356: -- Get the params. based on the conc. request id.
2357: OPEN csr_ext_params (c_request_id => l_conc_reqest_id
2358: ,c_ext_dfn_id => l_ext_dfn_id
2359: ,c_business_group_id => p_business_group_id);
2379: g_extract_params(bg_id).currency_code;
2380: CLOSE csr_leg_code;
2381: g_legislation_code := g_extract_params(bg_id).legislation_code;
2382: g_business_group_id := p_business_group_id;
2383: Hr_Utility.set_location('..Stored the extract parameters in PL/SQL table', 15);
2384:
2385: /*
2386: -- Get Assignment Run dimension Id as we will be using for
2387: -- calculating the amount
2398: ,p_con_set => g_extract_params(bg_id).con_set_id
2399: ,p_org_id => g_extract_params(bg_id).org_id
2400: );
2401:
2402: Hr_Utility.set_location('..Stored the Conc. Program parameters', 17);
2403: -- Store all record ids in a PL/SQL tbl
2404: FOR rcd_rec IN csr_rcd_ids
2405: LOOP
2406: g_ext_rcds(rcd_rec.ext_rcd_id):= rcd_rec;
2453: g_person_id:= Nvl(get_current_extract_person(p_assignment_id),
2454: Ben_Ext_Person.g_person_id);
2455:
2456: --If person is not retro hired then g_retro_hires record is null
2457: Hr_Utility.set_location('c_start_date'||g_extract_params(bg_id).extract_start_date, 15);
2458: Hr_Utility.set_location('c_end_date'||g_extract_params(bg_id).extract_end_date, 15);
2459: -- Derive the effective date
2460: l_effective_date := Least(g_extract_params(bg_id).extract_end_date,
2461: p_effective_date);
2454: Ben_Ext_Person.g_person_id);
2455:
2456: --If person is not retro hired then g_retro_hires record is null
2457: Hr_Utility.set_location('c_start_date'||g_extract_params(bg_id).extract_start_date, 15);
2458: Hr_Utility.set_location('c_end_date'||g_extract_params(bg_id).extract_end_date, 15);
2459: -- Derive the effective date
2460: l_effective_date := Least(g_extract_params(bg_id).extract_end_date,
2461: p_effective_date);
2462: Hr_Utility.set_location('..Processing Assig Id : '||p_assignment_id, 17);
2458: Hr_Utility.set_location('c_end_date'||g_extract_params(bg_id).extract_end_date, 15);
2459: -- Derive the effective date
2460: l_effective_date := Least(g_extract_params(bg_id).extract_end_date,
2461: p_effective_date);
2462: Hr_Utility.set_location('..Processing Assig Id : '||p_assignment_id, 17);
2463: Hr_Utility.set_location('..Processing Person Id : '||g_person_id, 17);
2464: Hr_Utility.set_location('..Processing Eff.Date : '||p_effective_date, 17);
2465: -- Get the list of employers (HR Orgs) in the current hierarchy.
2466: -- Store this value in a PL/SQL Table.
2459: -- Derive the effective date
2460: l_effective_date := Least(g_extract_params(bg_id).extract_end_date,
2461: p_effective_date);
2462: Hr_Utility.set_location('..Processing Assig Id : '||p_assignment_id, 17);
2463: Hr_Utility.set_location('..Processing Person Id : '||g_person_id, 17);
2464: Hr_Utility.set_location('..Processing Eff.Date : '||p_effective_date, 17);
2465: -- Get the list of employers (HR Orgs) in the current hierarchy.
2466: -- Store this value in a PL/SQL Table.
2467: -- Check if the assignments need to be processed. Assignments are
2460: l_effective_date := Least(g_extract_params(bg_id).extract_end_date,
2461: p_effective_date);
2462: Hr_Utility.set_location('..Processing Assig Id : '||p_assignment_id, 17);
2463: Hr_Utility.set_location('..Processing Person Id : '||g_person_id, 17);
2464: Hr_Utility.set_location('..Processing Eff.Date : '||p_effective_date, 17);
2465: -- Get the list of employers (HR Orgs) in the current hierarchy.
2466: -- Store this value in a PL/SQL Table.
2467: -- Check if the assignments need to be processed. Assignments are
2468: -- processed if
2569: CLOSE c_get_period_start_date;
2570:
2571:
2572: IF l_check_date=to_char(l_period_start_date2,'DD') AND l_check_mon=to_char(l_period_start_date2,'MM') THEN
2573: Hr_Utility.set_location('Adding 060 record', 75);
2574: l_temp_index:=l_temp_rec060_curr_01.element_entry_id;
2575: g_rcd_060(l_temp_index).pension_sal_amount :=to_number(l_temp_rec060_curr_01.screen_entry_value);
2576: l_temp_index:=l_temp_rec060_curr_02.element_entry_id;
2577: l_temp_date:=to_date(l_temp_rec060_curr_02.source_start_date,'YYYYMMDD');
2591: l_check_date:=substr(l_temp_rec060_prev_02.source_start_date,7,2);
2592: l_check_mon:=substr(l_temp_rec060_prev_02.source_start_date,5,2);
2593:
2594: IF l_check_date='01' AND l_check_mon='01' THEN
2595: Hr_Utility.set_location('Adding 060 record', 75);
2596: l_temp_index:=l_temp_rec060_prev_01.element_entry_id;
2597: g_rcd_060(l_temp_index).pension_sal_amount :=to_number(l_temp_rec060_prev_01.screen_entry_value);
2598: l_temp_index:=l_temp_rec060_prev_02.element_entry_id;
2599: l_temp_date:=to_date(l_temp_rec060_prev_02.source_start_date,'YYYYMMDD');
2604: CLOSE csr_get_060_prev_retro_val;
2605: CLOSE csr_get_060_prev_retro_date;
2606:
2607: g_rec_060_count:=g_rcd_060.COUNT;
2608: Hr_Utility.set_location('g_rec_060_count'||g_rec_060_count, 78);
2609:
2610: --------------------Record_080_Pre_Processing ---------------
2611:
2612: --Initialze the variables
2624: LOOP
2625:
2626: OPEN csr_chk_prev_ptp_ele(p_assignment_id,p_business_group_id,l_temp_rec080_01.effective_start_date);
2627: FETCH csr_chk_prev_ptp_ele INTO l_prev_ptp_val;
2628: Hr_Utility.set_location('g_080_index: '||g_080_index,25);
2629: IF (to_number(NVL(l_prev_ptp_val,'0')) <> to_number(NVL(l_temp_rec080_01.screen_entry_value,'0')))
2630: AND csr_chk_prev_ptp_ele%FOUND THEN
2631:
2632: g_ptp_index:=100;
2634: g_rcd_080(l_index).part_time_pct_dt_change:=l_temp_rec080_01.effective_start_date;
2635: g_rcd_080(l_index).part_time_factor:=to_number(NVL(l_temp_rec080_01.screen_entry_value,'0'));
2636: g_rec_080_type1_count:=g_rec_080_type1_count+1;
2637:
2638: Hr_Utility.set_location('Full_Profile', 75);
2639: Hr_Utility.set_location('..l_index: '||l_index, 76);
2640: Hr_Utility.set_location('..g_ptp_index: '||g_ptp_index, 77);
2641: Hr_Utility.set_location('..Validg_rec_080_type1_count: '||g_rec_080_type1_count, 78);
2642: Hr_Utility.set_location('..g_rcd_080(l_index).part_time_pct_dt_change '||g_rcd_080(l_index).part_time_pct_dt_change, 79);
2635: g_rcd_080(l_index).part_time_factor:=to_number(NVL(l_temp_rec080_01.screen_entry_value,'0'));
2636: g_rec_080_type1_count:=g_rec_080_type1_count+1;
2637:
2638: Hr_Utility.set_location('Full_Profile', 75);
2639: Hr_Utility.set_location('..l_index: '||l_index, 76);
2640: Hr_Utility.set_location('..g_ptp_index: '||g_ptp_index, 77);
2641: Hr_Utility.set_location('..Validg_rec_080_type1_count: '||g_rec_080_type1_count, 78);
2642: Hr_Utility.set_location('..g_rcd_080(l_index).part_time_pct_dt_change '||g_rcd_080(l_index).part_time_pct_dt_change, 79);
2643: Hr_Utility.set_location('g_rcd_080(l_index).part_time_factor '||g_rcd_080(l_index).part_time_factor, 80);
2636: g_rec_080_type1_count:=g_rec_080_type1_count+1;
2637:
2638: Hr_Utility.set_location('Full_Profile', 75);
2639: Hr_Utility.set_location('..l_index: '||l_index, 76);
2640: Hr_Utility.set_location('..g_ptp_index: '||g_ptp_index, 77);
2641: Hr_Utility.set_location('..Validg_rec_080_type1_count: '||g_rec_080_type1_count, 78);
2642: Hr_Utility.set_location('..g_rcd_080(l_index).part_time_pct_dt_change '||g_rcd_080(l_index).part_time_pct_dt_change, 79);
2643: Hr_Utility.set_location('g_rcd_080(l_index).part_time_factor '||g_rcd_080(l_index).part_time_factor, 80);
2644: g_080_display_flag:='Y';
2637:
2638: Hr_Utility.set_location('Full_Profile', 75);
2639: Hr_Utility.set_location('..l_index: '||l_index, 76);
2640: Hr_Utility.set_location('..g_ptp_index: '||g_ptp_index, 77);
2641: Hr_Utility.set_location('..Validg_rec_080_type1_count: '||g_rec_080_type1_count, 78);
2642: Hr_Utility.set_location('..g_rcd_080(l_index).part_time_pct_dt_change '||g_rcd_080(l_index).part_time_pct_dt_change, 79);
2643: Hr_Utility.set_location('g_rcd_080(l_index).part_time_factor '||g_rcd_080(l_index).part_time_factor, 80);
2644: g_080_display_flag:='Y';
2645: END IF;
2638: Hr_Utility.set_location('Full_Profile', 75);
2639: Hr_Utility.set_location('..l_index: '||l_index, 76);
2640: Hr_Utility.set_location('..g_ptp_index: '||g_ptp_index, 77);
2641: Hr_Utility.set_location('..Validg_rec_080_type1_count: '||g_rec_080_type1_count, 78);
2642: Hr_Utility.set_location('..g_rcd_080(l_index).part_time_pct_dt_change '||g_rcd_080(l_index).part_time_pct_dt_change, 79);
2643: Hr_Utility.set_location('g_rcd_080(l_index).part_time_factor '||g_rcd_080(l_index).part_time_factor, 80);
2644: g_080_display_flag:='Y';
2645: END IF;
2646: CLOSE csr_chk_prev_ptp_ele;
2639: Hr_Utility.set_location('..l_index: '||l_index, 76);
2640: Hr_Utility.set_location('..g_ptp_index: '||g_ptp_index, 77);
2641: Hr_Utility.set_location('..Validg_rec_080_type1_count: '||g_rec_080_type1_count, 78);
2642: Hr_Utility.set_location('..g_rcd_080(l_index).part_time_pct_dt_change '||g_rcd_080(l_index).part_time_pct_dt_change, 79);
2643: Hr_Utility.set_location('g_rcd_080(l_index).part_time_factor '||g_rcd_080(l_index).part_time_factor, 80);
2644: g_080_display_flag:='Y';
2645: END IF;
2646: CLOSE csr_chk_prev_ptp_ele;
2647: END LOOP;
2660: END IF;
2661: CLOSE csr_get_prev_ptp_std_cond;
2662: END LOOP;
2663:
2664: Hr_Utility.set_location('..Validg_rec_080_type1_count: '||g_rec_080_type1_count, 78);
2665:
2666: --Initialize the counter
2667: l_02_counter:=0;
2668: FOR l_temp_rec080_02 IN csr_chk_retro_ptp_ele(p_assignment_id,p_business_group_id)
2689: END IF;
2690: l_02_counter:=l_02_counter+1;
2691: END LOOP;
2692:
2693: Hr_Utility.set_location('..g_rec_080_type2_count: '||g_rec_080_type2_count, 78);
2694: FOR l_temp_rec080_03 IN csr_chk_inci_code_ele(p_assignment_id,p_business_group_id)
2695: LOOP
2696: OPEN csr_chk_inci_code_chg_ele(p_assignment_id,p_business_group_id,l_temp_rec080_03.effective_start_date);
2697: FETCH csr_chk_inci_code_chg_ele INTO l_prev_iwc;
2703: l_index:=g_ptp_index+g_rec_080_type3_count;
2704: g_rcd_080(l_index).part_time_pct_dt_change:=l_temp_rec080_03.effective_start_date;
2705: g_rcd_080(l_index).incidental_code:=to_number(l_temp_rec080_03.segment1);
2706: g_rec_080_type3_count:=g_rec_080_type3_count+1;
2707: Hr_Utility.set_location('Full_Profile', 75);
2708: Hr_Utility.set_location('..Validg_rec_080_type3_count: '||g_rec_080_type3_count, 78);
2709: Hr_Utility.set_location('..g_rcd_080(l_index).part_time_pct_dt_change '||g_rcd_080(l_index).part_time_pct_dt_change, 79);
2710: Hr_Utility.set_location('g_rcd_080(l_index).part_time_factor '||g_rcd_080(l_index).part_time_factor, 80);
2711: g_080_display_flag:='Y';
2704: g_rcd_080(l_index).part_time_pct_dt_change:=l_temp_rec080_03.effective_start_date;
2705: g_rcd_080(l_index).incidental_code:=to_number(l_temp_rec080_03.segment1);
2706: g_rec_080_type3_count:=g_rec_080_type3_count+1;
2707: Hr_Utility.set_location('Full_Profile', 75);
2708: Hr_Utility.set_location('..Validg_rec_080_type3_count: '||g_rec_080_type3_count, 78);
2709: Hr_Utility.set_location('..g_rcd_080(l_index).part_time_pct_dt_change '||g_rcd_080(l_index).part_time_pct_dt_change, 79);
2710: Hr_Utility.set_location('g_rcd_080(l_index).part_time_factor '||g_rcd_080(l_index).part_time_factor, 80);
2711: g_080_display_flag:='Y';
2712: END IF;
2705: g_rcd_080(l_index).incidental_code:=to_number(l_temp_rec080_03.segment1);
2706: g_rec_080_type3_count:=g_rec_080_type3_count+1;
2707: Hr_Utility.set_location('Full_Profile', 75);
2708: Hr_Utility.set_location('..Validg_rec_080_type3_count: '||g_rec_080_type3_count, 78);
2709: Hr_Utility.set_location('..g_rcd_080(l_index).part_time_pct_dt_change '||g_rcd_080(l_index).part_time_pct_dt_change, 79);
2710: Hr_Utility.set_location('g_rcd_080(l_index).part_time_factor '||g_rcd_080(l_index).part_time_factor, 80);
2711: g_080_display_flag:='Y';
2712: END IF;
2713: CLOSE csr_chk_inci_code_chg_ele;
2706: g_rec_080_type3_count:=g_rec_080_type3_count+1;
2707: Hr_Utility.set_location('Full_Profile', 75);
2708: Hr_Utility.set_location('..Validg_rec_080_type3_count: '||g_rec_080_type3_count, 78);
2709: Hr_Utility.set_location('..g_rcd_080(l_index).part_time_pct_dt_change '||g_rcd_080(l_index).part_time_pct_dt_change, 79);
2710: Hr_Utility.set_location('g_rcd_080(l_index).part_time_factor '||g_rcd_080(l_index).part_time_factor, 80);
2711: g_080_display_flag:='Y';
2712: END IF;
2713: CLOSE csr_chk_inci_code_chg_ele;
2714: END LOOP;
2711: g_080_display_flag:='Y';
2712: END IF;
2713: CLOSE csr_chk_inci_code_chg_ele;
2714: END LOOP;
2715: Hr_Utility.set_location('..g_rec_080_type3_count: '||g_rec_080_type3_count, 78);
2716: --Initialize the counter
2717: l_04_counter:=0;
2718: --Check for Current Year Retro IWC Changes
2719: OPEN csr_retro_pggm_gen_info_iwc;
2742: END IF;
2743: l_04_counter:=l_04_counter+1;
2744: END LOOP;
2745: END IF;
2746: Hr_Utility.set_location('..Validg_rec_080_type4_count: '||g_rec_080_type4_count, 78);
2747: Hr_Utility.set_location('..Valid Assig Id : '||p_assignment_id, 79);
2748: ---------------End Record 080 Processing---------------------------------
2749:
2750: --------------------Record_081_Pre_Processing ---------------
2743: l_04_counter:=l_04_counter+1;
2744: END LOOP;
2745: END IF;
2746: Hr_Utility.set_location('..Validg_rec_080_type4_count: '||g_rec_080_type4_count, 78);
2747: Hr_Utility.set_location('..Valid Assig Id : '||p_assignment_id, 79);
2748: ---------------End Record 080 Processing---------------------------------
2749:
2750: --------------------Record_081_Pre_Processing ---------------
2751: --Store all the year for which hours worked or total hours have been changed retrospectively
2755: g_main_rec_081 :='N';
2756: g_rec_081_type :='C';
2757: g_rec_081_count:=0;
2758:
2759: Hr_Utility.set_location('Record_081_Pre_Processing', 75);
2760: OPEN csr_get_081_retro_values(p_assignment_id,p_business_group_id,g_extract_params(p_business_group_id).extract_start_date);
2761: l_temp_index:=0;
2762: LOOP
2763: FETCH csr_get_081_retro_values INTO l_temp_rec081;
2796: l_temp_index:=l_temp_index+1;
2797: END LOOP;
2798:
2799: g_rec_081_count:=g_rcd_081.COUNT;
2800: Hr_Utility.set_location('g_rec_081_count'||g_rec_081_count, 75);
2801: CLOSE csr_get_081_retro_values;
2802: ---------------End Record 081 Processing---------------------------------
2803:
2804: END IF; -- if l_return_value = 'Y'
2801: CLOSE csr_get_081_retro_values;
2802: ---------------End Record 081 Processing---------------------------------
2803:
2804: END IF; -- if l_return_value = 'Y'
2805: Hr_Utility.set_location('l_return_value : '||l_return_value, 79);
2806: Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
2807: --hr_utility.trace_off;
2808: RETURN l_return_value;
2809: EXCEPTION
2802: ---------------End Record 081 Processing---------------------------------
2803:
2804: END IF; -- if l_return_value = 'Y'
2805: Hr_Utility.set_location('l_return_value : '||l_return_value, 79);
2806: Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
2807: --hr_utility.trace_off;
2808: RETURN l_return_value;
2809: EXCEPTION
2810: WHEN Others THEN
2803:
2804: END IF; -- if l_return_value = 'Y'
2805: Hr_Utility.set_location('l_return_value : '||l_return_value, 79);
2806: Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
2807: --hr_utility.trace_off;
2808: RETURN l_return_value;
2809: EXCEPTION
2810: WHEN Others THEN
2811: p_error_message :='SQL-ERRM :'||SQLERRM;
2808: RETURN l_return_value;
2809: EXCEPTION
2810: WHEN Others THEN
2811: p_error_message :='SQL-ERRM :'||SQLERRM;
2812: Hr_Utility.set_location('..'||p_error_message,85);
2813: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
2814: RETURN l_return_value;
2815:
2816: END Pension_Criteria_Full_Profile;
2809: EXCEPTION
2810: WHEN Others THEN
2811: p_error_message :='SQL-ERRM :'||SQLERRM;
2812: Hr_Utility.set_location('..'||p_error_message,85);
2813: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
2814: RETURN l_return_value;
2815:
2816: END Pension_Criteria_Full_Profile;
2817: -- =============================================================================
2846: l_street per_addresses_v.region_1%TYPE;
2847: l_proc_name Varchar2(150) := g_proc_name ||'Get_Street';
2848:
2849: BEGIN
2850: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
2851:
2852: OPEN cur_get_street(g_person_id);
2853: FETCH cur_get_street INTO l_street;
2854: CLOSE cur_get_street;
2853: FETCH cur_get_street INTO l_street;
2854: CLOSE cur_get_street;
2855:
2856: p_data_element_value := Upper(l_street);
2857: Hr_Utility.set_location('Leaving: '||l_proc_name, 5);
2858: RETURN 0;
2859:
2860: EXCEPTION
2861: WHEN Others THEN
2859:
2860: EXCEPTION
2861: WHEN Others THEN
2862: p_error_message :='SQL-ERRM :'||SQLERRM;
2863: Hr_Utility.set_location('..'||p_error_message,85);
2864: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
2865: RETURN -1;
2866: END Get_Street;
2867:
2860: EXCEPTION
2861: WHEN Others THEN
2862: p_error_message :='SQL-ERRM :'||SQLERRM;
2863: Hr_Utility.set_location('..'||p_error_message,85);
2864: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
2865: RETURN -1;
2866: END Get_Street;
2867:
2868: -- =============================================================================
2898: l_proc_name Varchar2(150) := g_proc_name ||'Get_House_Num';
2899: l_ret_val Number := 0;
2900:
2901: BEGIN
2902: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
2903: OPEN cur_get_house_num(g_person_id);
2904: FETCH cur_get_house_num INTO l_house_num;
2905: CLOSE cur_get_house_num;
2906: p_data_element_value := Upper(l_house_num);
2904: FETCH cur_get_house_num INTO l_house_num;
2905: CLOSE cur_get_house_num;
2906: p_data_element_value := Upper(l_house_num);
2907: l_ret_val :=0;
2908: Hr_Utility.set_location('Leaving: '||l_proc_name, 5);
2909: RETURN l_ret_val;
2910: EXCEPTION
2911: WHEN Others THEN
2912: p_error_message :='SQL-ERRM :'||SQLERRM;
2909: RETURN l_ret_val;
2910: EXCEPTION
2911: WHEN Others THEN
2912: p_error_message :='SQL-ERRM :'||SQLERRM;
2913: Hr_Utility.set_location('..'||p_error_message,85);
2914: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
2915: RETURN l_ret_val;
2916: END Get_House_Num;
2917:
2910: EXCEPTION
2911: WHEN Others THEN
2912: p_error_message :='SQL-ERRM :'||SQLERRM;
2913: Hr_Utility.set_location('..'||p_error_message,85);
2914: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
2915: RETURN l_ret_val;
2916: END Get_House_Num;
2917:
2918: -- =============================================================================
2948: l_proc_name Varchar2(150) := g_proc_name ||'Get_Addnl_House_Num';
2949: l_ret_val Number := 0;
2950:
2951: BEGIN
2952: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
2953: OPEN cur_get_addl_house_num(g_person_id);
2954: FETCH cur_get_addl_house_num INTO l_addl_house_num;
2955: CLOSE cur_get_addl_house_num;
2956:
2956:
2957: p_data_element_value := Upper(l_addl_house_num);
2958: l_ret_val :=0;
2959:
2960: Hr_Utility.set_location('Leaving: '||l_proc_name, 15);
2961: RETURN l_ret_val;
2962:
2963: EXCEPTION
2964: WHEN Others THEN
2962:
2963: EXCEPTION
2964: WHEN Others THEN
2965: p_error_message :='SQL-ERRM :'||SQLERRM;
2966: Hr_Utility.set_location('..'||p_error_message,85);
2967: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
2968: RETURN l_ret_val;
2969: END Get_Addl_House_Num;
2970:
2963: EXCEPTION
2964: WHEN Others THEN
2965: p_error_message :='SQL-ERRM :'||SQLERRM;
2966: Hr_Utility.set_location('..'||p_error_message,85);
2967: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
2968: RETURN l_ret_val;
2969: END Get_Addl_House_Num;
2970:
2971: -- =============================================================================
2996: l_proc_name Varchar2(150) := g_proc_name ||'Get_Postal_Code';
2997: l_ret_val Number :=0;
2998:
2999: BEGIN
3000: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
3001: OPEN cur_get_postal_code(g_person_id);
3002: FETCH cur_get_postal_code INTO l_postal_code;
3003: IF cur_get_postal_code%FOUND THEN
3004: CLOSE cur_get_postal_code;
3014: CLOSE cur_get_postal_code;
3015: p_data_element_value := '';
3016: END IF;
3017: l_ret_val :=0;
3018: Hr_Utility.set_location('Leaving: '||l_proc_name, 5);
3019: RETURN l_ret_val;
3020:
3021: EXCEPTION
3022: WHEN Others THEN
3020:
3021: EXCEPTION
3022: WHEN Others THEN
3023: p_error_message :='SQL-ERRM :'||SQLERRM;
3024: Hr_Utility.set_location('..'||p_error_message,85);
3025: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
3026: RETURN l_ret_val;
3027: END Get_Postal_Code;
3028: -- =============================================================================
3021: EXCEPTION
3022: WHEN Others THEN
3023: p_error_message :='SQL-ERRM :'||SQLERRM;
3024: Hr_Utility.set_location('..'||p_error_message,85);
3025: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
3026: RETURN l_ret_val;
3027: END Get_Postal_Code;
3028: -- =============================================================================
3029: -- Get_City
3071: l_proc_name Varchar2(150) := g_proc_name ||'Get_City';
3072: l_ret_val Number :=0;
3073:
3074: BEGIN
3075: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
3076:
3077: OPEN cur_get_city(g_person_id);
3078: FETCH cur_get_city INTO l_city;
3079: CLOSE cur_get_city;
3097: END IF;
3098:
3099: l_ret_val :=0;
3100:
3101: Hr_Utility.set_location('Leaving: '||l_proc_name, 5);
3102: RETURN l_ret_val;
3103:
3104: EXCEPTION
3105: WHEN Others THEN
3103:
3104: EXCEPTION
3105: WHEN Others THEN
3106: p_error_message :='SQL-ERRM :'||SQLERRM;
3107: Hr_Utility.set_location('..'||p_error_message,85);
3108: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
3109: RETURN l_ret_val;
3110: END Get_City;
3111: --============================================================================
3104: EXCEPTION
3105: WHEN Others THEN
3106: p_error_message :='SQL-ERRM :'||SQLERRM;
3107: Hr_Utility.set_location('..'||p_error_message,85);
3108: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
3109: RETURN l_ret_val;
3110: END Get_City;
3111: --============================================================================
3112: --GET_PERSON_INITIALS
3133: l_return_value Number :=0;
3134:
3135: BEGIN
3136:
3137: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
3138:
3139: --Get the initials stored as entered by user
3140: OPEN cur_get_initials(g_person_id,p_date_earned);
3141: FETCH cur_get_initials INTO l_initials;
3141: FETCH cur_get_initials INTO l_initials;
3142: CLOSE cur_get_initials;
3143: p_data_element_value := l_initials;
3144:
3145: Hr_Utility.set_location('Leaving: '||l_proc_name, 10);
3146: RETURN l_return_value;
3147:
3148: EXCEPTION
3149: WHEN Others THEN
3147:
3148: EXCEPTION
3149: WHEN Others THEN
3150: p_error_message :='SQL-ERRM :'||SQLERRM;
3151: Hr_Utility.set_location('..'||p_error_message,7);
3152: Hr_Utility.set_location('Leaving: '||l_proc_name, 9);
3153: l_return_value := -1;
3154: RETURN l_return_value;
3155: END Get_Person_Initials;
3148: EXCEPTION
3149: WHEN Others THEN
3150: p_error_message :='SQL-ERRM :'||SQLERRM;
3151: Hr_Utility.set_location('..'||p_error_message,7);
3152: Hr_Utility.set_location('Leaving: '||l_proc_name, 9);
3153: l_return_value := -1;
3154: RETURN l_return_value;
3155: END Get_Person_Initials;
3156: -- =============================================================================
3176: l_gender Varchar2(2);
3177: l_return_value Number:=0;
3178:
3179: BEGIN
3180: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
3181: OPEN cur_get_gender(p_effective_date,p_business_group_id);
3182: FETCH cur_get_gender INTO l_gender;
3183: CLOSE cur_get_gender;
3184: p_data_element_value := l_gender;
3182: FETCH cur_get_gender INTO l_gender;
3183: CLOSE cur_get_gender;
3184: p_data_element_value := l_gender;
3185:
3186: Hr_Utility.set_location('p_data_element_value:'||p_data_element_value, 5);
3187: Hr_Utility.set_location('Leaving: '||l_proc_name, 5);
3188:
3189: l_return_value := 0;
3190: RETURN l_return_value;
3183: CLOSE cur_get_gender;
3184: p_data_element_value := l_gender;
3185:
3186: Hr_Utility.set_location('p_data_element_value:'||p_data_element_value, 5);
3187: Hr_Utility.set_location('Leaving: '||l_proc_name, 5);
3188:
3189: l_return_value := 0;
3190: RETURN l_return_value;
3191:
3191:
3192: EXCEPTION
3193: WHEN Others THEN
3194: p_error_message :='SQL-ERRM :'||SQLERRM;
3195: Hr_Utility.set_location('..'||p_error_message,85);
3196: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
3197: l_return_value := -1;
3198: RETURN l_return_value;
3199: END Get_Gender;
3192: EXCEPTION
3193: WHEN Others THEN
3194: p_error_message :='SQL-ERRM :'||SQLERRM;
3195: Hr_Utility.set_location('..'||p_error_message,85);
3196: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
3197: l_return_value := -1;
3198: RETURN l_return_value;
3199: END Get_Gender;
3200: -- =============================================================================
3225: l_proc_name Varchar2(150) := 'Get_Occupation_Code';
3226:
3227: BEGIN
3228:
3229: Hr_Utility.set_location('Entering : '||l_proc_name,10);
3230: --first fetch the employment code from the soft coding keyflex
3231: OPEN c_get_emp_code;
3232: FETCH c_get_emp_code INTO l_emp_code;
3233: IF c_get_emp_code%FOUND THEN
3231: OPEN c_get_emp_code;
3232: FETCH c_get_emp_code INTO l_emp_code;
3233: IF c_get_emp_code%FOUND THEN
3234: CLOSE c_get_emp_code;
3235: Hr_Utility.set_location('found the code as : '||l_emp_code,20);
3236: p_data_element_value := l_emp_code;
3237: ELSE
3238: CLOSE c_get_emp_code;
3239: p_data_element_value := '';
3237: ELSE
3238: CLOSE c_get_emp_code;
3239: p_data_element_value := '';
3240: END IF;
3241: Hr_Utility.set_location('Leaving : '||l_proc_name,50);
3242: RETURN 0;
3243: EXCEPTION
3244: WHEN Others THEN
3245: p_error_message := SQLERRM;
3242: RETURN 0;
3243: EXCEPTION
3244: WHEN Others THEN
3245: p_error_message := SQLERRM;
3246: Hr_Utility.set_location('error message : '||SQLERRM,10);
3247: Hr_Utility.set_location('Leaving : '||l_proc_name,50);
3248: p_data_element_value := '';
3249: RETURN 1;
3250:
3243: EXCEPTION
3244: WHEN Others THEN
3245: p_error_message := SQLERRM;
3246: Hr_Utility.set_location('error message : '||SQLERRM,10);
3247: Hr_Utility.set_location('Leaving : '||l_proc_name,50);
3248: p_data_element_value := '';
3249: RETURN 1;
3250:
3251: END Get_Occupation_Code;
3265: l_proc_name Varchar2(150) := g_proc_name ||'Get_PGGM_ER_Num';
3266: l_ret_val Number := 0;
3267:
3268: Begin
3269: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
3270:
3271: --return the pggm employer number which has been set for Parent organization
3272: --for all child orgs also,only this value has to be reported
3273: p_data_element_value:=g_pggm_employer_num;
3272: --for all child orgs also,only this value has to be reported
3273: p_data_element_value:=g_pggm_employer_num;
3274:
3275:
3276: Hr_Utility.set_location(' Leaving: ' ||l_proc_name,20);
3277: l_ret_val:=0;
3278:
3279: RETURN l_ret_val;
3280: EXCEPTION
3279: RETURN l_ret_val;
3280: EXCEPTION
3281: WHEN OTHERS THEN
3282: p_error_message :='SQL-ERRM :'||SQLERRM;
3283: Hr_Utility.set_location('..'||p_error_message,30);
3284: Hr_Utility.set_location('Leaving: '||l_proc_name,40);
3285: RETURN l_ret_val;
3286: END Get_PGGM_ER_Num;
3287:
3280: EXCEPTION
3281: WHEN OTHERS THEN
3282: p_error_message :='SQL-ERRM :'||SQLERRM;
3283: Hr_Utility.set_location('..'||p_error_message,30);
3284: Hr_Utility.set_location('Leaving: '||l_proc_name,40);
3285: RETURN l_ret_val;
3286: END Get_PGGM_ER_Num;
3287:
3288: -- ===============================================================================
3334: l_ret_val Number:=0;
3335: l_kind_of_ptp varchar2(2):='';
3336:
3337: BEGIN
3338: Hr_Utility.set_location('Entering: '||l_proc_name,5);
3339: OPEN csr_get_kind_of_ptp(p_assignment_id,p_date_earned);
3340: FETCH csr_get_kind_of_ptp INTO l_kind_of_ptp;
3341: IF csr_get_kind_of_ptp%FOUND THEN
3342: p_data_element_value:=l_kind_of_ptp;
3363: CLOSE csr_chk_primary_asg;
3364: END IF;
3365: --End of 5869420
3366:
3367: Hr_Utility.set_location(' Leaving: '||l_proc_name,20);
3368: RETURN l_ret_val;
3369: EXCEPTION
3370: WHEN OTHERS THEN
3371: p_error_message :='SQL-ERRM :'||SQLERRM;
3368: RETURN l_ret_val;
3369: EXCEPTION
3370: WHEN OTHERS THEN
3371: p_error_message :='SQL-ERRM :'||SQLERRM;
3372: Hr_Utility.set_location('..'||p_error_message,30);
3373: Hr_Utility.set_location('Leaving: '||l_proc_name,40);
3374: RETURN l_ret_val;
3375: END Get_Kind_Of_PTP;
3376: -- ===============================================================================
3369: EXCEPTION
3370: WHEN OTHERS THEN
3371: p_error_message :='SQL-ERRM :'||SQLERRM;
3372: Hr_Utility.set_location('..'||p_error_message,30);
3373: Hr_Utility.set_location('Leaving: '||l_proc_name,40);
3374: RETURN l_ret_val;
3375: END Get_Kind_Of_PTP;
3376: -- ===============================================================================
3377: -- ~ Function to get the CAO Code stored in Org DDF
3411: l_ret_val Number:=0;
3412: l_cao_code varchar2(10):='';
3413: l_org_id Number:=0;
3414: BEGIN
3415: Hr_Utility.set_location('Entering: '||l_proc_name,5);
3416: -- Get org id
3417: OPEN csr_get_org_id;
3418: FETCH csr_get_org_id INTO l_org_id;
3419: CLOSE csr_get_org_id ;
3417: OPEN csr_get_org_id;
3418: FETCH csr_get_org_id INTO l_org_id;
3419: CLOSE csr_get_org_id ;
3420:
3421: Hr_Utility.set_location('l_org_id: '||l_org_id,10);
3422:
3423: -- Get CAO Code from derived org id
3424: OPEN csr_get_cao_code(l_org_id);
3425: FETCH csr_get_cao_code INTO l_cao_code;
3426: IF csr_get_cao_code%FOUND AND ( l_cao_code <> ' ' OR l_cao_code IS NOT NULL) THEN
3427: CLOSE csr_get_cao_code;
3428: ELSE
3429: CLOSE csr_get_cao_code;
3430: Hr_Utility.set_location('CAO not found at org level',30);
3431: --Fetch CAO code of parent org
3432: Hr_Utility.set_location('Fetching CAO code of parent org',40);
3433: OPEN csr_get_cao_code(g_extract_params(p_business_group_id).org_id);
3434: FETCH csr_get_cao_code INTO l_cao_code;
3428: ELSE
3429: CLOSE csr_get_cao_code;
3430: Hr_Utility.set_location('CAO not found at org level',30);
3431: --Fetch CAO code of parent org
3432: Hr_Utility.set_location('Fetching CAO code of parent org',40);
3433: OPEN csr_get_cao_code(g_extract_params(p_business_group_id).org_id);
3434: FETCH csr_get_cao_code INTO l_cao_code;
3435: IF csr_get_cao_code%NOTFOUND THEN
3436: l_cao_code:=NULL;
3434: FETCH csr_get_cao_code INTO l_cao_code;
3435: IF csr_get_cao_code%NOTFOUND THEN
3436: l_cao_code:=NULL;
3437: END IF;
3438: Hr_Utility.set_location('l_cao_code'||l_cao_code,50);
3439: CLOSE csr_get_cao_code;
3440: END If;
3441:
3442: l_ret_val := 0;
3441:
3442: l_ret_val := 0;
3443: p_data_element_value:=SUBSTR(l_cao_code,1,5);
3444: p_data_element_value:=lpad(p_data_element_value,4,'0');
3445: Hr_Utility.set_location(' Leaving: ' ||l_proc_name,20);
3446: RETURN l_ret_val;
3447: EXCEPTION
3448: WHEN OTHERS THEN
3449: p_error_message :='SQL-ERRM :'||SQLERRM;
3446: RETURN l_ret_val;
3447: EXCEPTION
3448: WHEN OTHERS THEN
3449: p_error_message :='SQL-ERRM :'||SQLERRM;
3450: Hr_Utility.set_location('..'||p_error_message,30);
3451: Hr_Utility.set_location('Leaving: '||l_proc_name,40);
3452: RETURN l_ret_val;
3453: END Get_CAO_Code;
3454: -- ===============================================================================
3447: EXCEPTION
3448: WHEN OTHERS THEN
3449: p_error_message :='SQL-ERRM :'||SQLERRM;
3450: Hr_Utility.set_location('..'||p_error_message,30);
3451: Hr_Utility.set_location('Leaving: '||l_proc_name,40);
3452: RETURN l_ret_val;
3453: END Get_CAO_Code;
3454: -- ===============================================================================
3455: -- ~ Function to get the Employer Registration Number stored in Org DDF
3490: l_ret_val Number:=0;
3491: l_emp_reg_num varchar2(16):='';
3492: l_org_id Number:=0;
3493: BEGIN
3494: Hr_Utility.set_location('Entering: '||l_proc_name,5);
3495: -- Get org id
3496: OPEN csr_get_org_id;
3497: FETCH csr_get_org_id INTO l_org_id;
3498: CLOSE csr_get_org_id ;
3496: OPEN csr_get_org_id;
3497: FETCH csr_get_org_id INTO l_org_id;
3498: CLOSE csr_get_org_id ;
3499:
3500: Hr_Utility.set_location('l_org_id: '||l_org_id,10);
3501:
3502: -- Get Emp Reg Num Code from derived org id
3503: OPEN csr_emp_reg_num(l_org_id);
3504: FETCH csr_emp_reg_num INTO l_emp_reg_num;
3517:
3518: END If;
3519: p_data_element_value:=l_emp_reg_num;
3520:
3521: Hr_Utility.set_location(' Leaving: ' ||l_proc_name,20);
3522: RETURN l_ret_val;
3523: EXCEPTION
3524: WHEN OTHERS THEN
3525: p_error_message :='SQL-ERRM :'||SQLERRM;
3522: RETURN l_ret_val;
3523: EXCEPTION
3524: WHEN OTHERS THEN
3525: p_error_message :='SQL-ERRM :'||SQLERRM;
3526: Hr_Utility.set_location('..'||p_error_message,30);
3527: Hr_Utility.set_location('Leaving: '||l_proc_name,40);
3528: RETURN l_ret_val;
3529: END Get_Emp_Reg_Num;
3530: -- ===============================================================================
3523: EXCEPTION
3524: WHEN OTHERS THEN
3525: p_error_message :='SQL-ERRM :'||SQLERRM;
3526: Hr_Utility.set_location('..'||p_error_message,30);
3527: Hr_Utility.set_location('Leaving: '||l_proc_name,40);
3528: RETURN l_ret_val;
3529: END Get_Emp_Reg_Num;
3530: -- ===============================================================================
3531: -- ~ Function to get the PGGM Emp Number stored in PGGM General Info Element as I/P val
3575: l_proc_name Varchar2(150) :=g_proc_name || 'Get_PGGM_Ee_Num';
3576: l_ret_val Number :=0;
3577: l_pggm_ee_num varchar2(30) :='0';
3578: Begin
3579: Hr_Utility.set_location(' Entering ' || l_proc_name , 5);
3580: OPEN csr_get_pggm_ee_num(p_assignment_id,p_date_earned);
3581: FETCH csr_get_pggm_ee_num INTO l_pggm_ee_num;
3582: Hr_Utility.set_location('l_pggm_ee_num: '||l_pggm_ee_num, 45);
3583: IF csr_get_pggm_ee_num%FOUND THEN
3578: Begin
3579: Hr_Utility.set_location(' Entering ' || l_proc_name , 5);
3580: OPEN csr_get_pggm_ee_num(p_assignment_id,p_date_earned);
3581: FETCH csr_get_pggm_ee_num INTO l_pggm_ee_num;
3582: Hr_Utility.set_location('l_pggm_ee_num: '||l_pggm_ee_num, 45);
3583: IF csr_get_pggm_ee_num%FOUND THEN
3584: p_data_element_value:=l_pggm_ee_num;
3585: l_ret_val :=0;
3586: END If;
3583: IF csr_get_pggm_ee_num%FOUND THEN
3584: p_data_element_value:=l_pggm_ee_num;
3585: l_ret_val :=0;
3586: END If;
3587: Hr_Utility.set_location('p_data_element_value: '||p_data_element_value, 45);
3588: CLOSE csr_get_pggm_ee_num;
3589:
3590: --If PGGM EE Num is not entered for this assignment, and if this is a
3591: --secondary assignment, get the value from the primary assignment.
3607: RETURN l_ret_val;
3608: EXCEPTION
3609: WHEN Others THEN
3610: p_error_message :='SQL-ERRM :'||SQLERRM;
3611: Hr_Utility.set_location('..Exception Others Raised at Get_PGGM_Ee_Num'||p_error_message,40);
3612: Hr_Utility.set_location('Leaving: '||l_proc_name, 45);
3613: l_ret_val:=1;
3614: RETURN l_ret_val;
3615: END Get_PGGM_Ee_Num;
3608: EXCEPTION
3609: WHEN Others THEN
3610: p_error_message :='SQL-ERRM :'||SQLERRM;
3611: Hr_Utility.set_location('..Exception Others Raised at Get_PGGM_Ee_Num'||p_error_message,40);
3612: Hr_Utility.set_location('Leaving: '||l_proc_name, 45);
3613: l_ret_val:=1;
3614: RETURN l_ret_val;
3615: END Get_PGGM_Ee_Num;
3616:
3630: l_asg_seq_num VARCHAR2(2);
3631:
3632: BEGIN
3633:
3634: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
3635:
3636: IF g_primary_assig.EXISTS(p_assignment_id) THEN
3637:
3638: l_per_ee_num := g_primary_assig (p_assignment_id).ee_num;
3650: -- Add the asg seq number to the EE number
3651: --
3652: l_per_ee_num := l_per_ee_num || l_asg_seq_num;
3653: l_per_ee_num := lpad(l_per_ee_num,15,'0');
3654: Hr_Utility.set_location('l_asg_seq_num: '||l_asg_seq_num, 5);
3655:
3656: END IF;
3657:
3658: p_data_element_value := Nvl(l_per_ee_num,'000000000000000');
3656: END IF;
3657:
3658: p_data_element_value := Nvl(l_per_ee_num,'000000000000000');
3659:
3660: Hr_Utility.set_location('Leaving: '||l_proc_name, 5);
3661:
3662: RETURN 0;
3663:
3664: EXCEPTION
3663:
3664: EXCEPTION
3665: WHEN Others THEN
3666: p_error_message :='SQL-ERRM :'||SQLERRM;
3667: Hr_Utility.set_location('..'||p_error_message,85);
3668: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
3669: RETURN -1;
3670: END Get_EE_Num;
3671:
3664: EXCEPTION
3665: WHEN Others THEN
3666: p_error_message :='SQL-ERRM :'||SQLERRM;
3667: Hr_Utility.set_location('..'||p_error_message,85);
3668: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
3669: RETURN -1;
3670: END Get_EE_Num;
3671:
3672: -- =============================================================================
3703:
3704: l_proc_name Varchar2(150) := g_proc_name ||'Get_Hire_Date';
3705: l_hire_date DATE;
3706: BEGIN
3707: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
3708:
3709: OPEN csr_get_hire_date;
3710: FETCH csr_get_hire_date INTO l_hire_date;
3711: IF csr_get_hire_date%FOUND THEN
3713: END IF;
3714: CLOSE csr_get_hire_date;
3715:
3716:
3717: Hr_Utility.set_location('Leaving: '||l_proc_name, 45);
3718: RETURN 0;
3719: EXCEPTION
3720: WHEN Others THEN
3721: p_error_message :='SQL-ERRM :'||SQLERRM;
3718: RETURN 0;
3719: EXCEPTION
3720: WHEN Others THEN
3721: p_error_message :='SQL-ERRM :'||SQLERRM;
3722: Hr_Utility.set_location('..'||p_error_message,85);
3723: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
3724: RETURN 1;
3725: END Get_Hire_Date;
3726: -- =============================================================================
3719: EXCEPTION
3720: WHEN Others THEN
3721: p_error_message :='SQL-ERRM :'||SQLERRM;
3722: Hr_Utility.set_location('..'||p_error_message,85);
3723: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
3724: RETURN 1;
3725: END Get_Hire_Date;
3726: -- =============================================================================
3727: -- Get_Term_Reason
3751: l_reason VARCHAR2(3);
3752:
3753: BEGIN
3754:
3755: hr_utility.set_location('Entering: '||l_proc_name, 5);
3756:
3757: --
3758: -- 5869420
3759: --
3791: END IF;
3792:
3793: p_data_element_value := l_reason;
3794:
3795: hr_utility.set_location('....Termination reason is : '||l_reason,32);
3796: hr_utility.set_location('Leaving: '||l_proc_name, 45);
3797:
3798: RETURN 0;
3799:
3792:
3793: p_data_element_value := l_reason;
3794:
3795: hr_utility.set_location('....Termination reason is : '||l_reason,32);
3796: hr_utility.set_location('Leaving: '||l_proc_name, 45);
3797:
3798: RETURN 0;
3799:
3800: EXCEPTION
3799:
3800: EXCEPTION
3801: WHEN OTHERS THEN
3802: p_error_message :='SQL-ERRM :'||SQLERRM;
3803: hr_utility.set_location('..'||p_error_message,85);
3804: hr_utility.set_location('Leaving: '||l_proc_name, 90);
3805: RETURN -1;
3806: END Get_Term_Reason;
3807:
3800: EXCEPTION
3801: WHEN OTHERS THEN
3802: p_error_message :='SQL-ERRM :'||SQLERRM;
3803: hr_utility.set_location('..'||p_error_message,85);
3804: hr_utility.set_location('Leaving: '||l_proc_name, 90);
3805: RETURN -1;
3806: END Get_Term_Reason;
3807:
3808:
3853: l_proc_name Varchar2(150) :=g_proc_name || 'Get_Reason_Of_PTP';
3854: l_ret_val Number:=-1;
3855: l_reason_of_ptp varchar2(40):='';
3856: BEGIN
3857: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
3858:
3859: OPEN csr_get_reason_of_ptp(p_assignment_id,p_date_earned);
3860: FETCH csr_get_reason_of_ptp INTO l_reason_of_ptp;
3861: IF csr_get_reason_of_ptp%FOUND THEN
3880: CLOSE csr_get_reason_of_ptp;
3881: END IF;
3882: CLOSE csr_chk_primary_asg;
3883: END IF;
3884: Hr_Utility.set_location(' Leaving: ' ||l_proc_name,20);
3885: RETURN l_ret_val;
3886: EXCEPTION
3887: WHEN OTHERS THEN
3888: p_error_message :='SQL-ERRM :'||SQLERRM;
3885: RETURN l_ret_val;
3886: EXCEPTION
3887: WHEN OTHERS THEN
3888: p_error_message :='SQL-ERRM :'||SQLERRM;
3889: Hr_Utility.set_location('..'||p_error_message,30);
3890: Hr_Utility.set_location('Leaving: '||l_proc_name,40);
3891: RETURN l_ret_val;
3892: END Get_Reason_Of_PTP;
3893: -- ===============================================================================
3886: EXCEPTION
3887: WHEN OTHERS THEN
3888: p_error_message :='SQL-ERRM :'||SQLERRM;
3889: Hr_Utility.set_location('..'||p_error_message,30);
3890: Hr_Utility.set_location('Leaving: '||l_proc_name,40);
3891: RETURN l_ret_val;
3892: END Get_Reason_Of_PTP;
3893: -- ===============================================================================
3894: -- ~ Function to get the part time percent as of hire date
3938: l_ret_val Number:=-1;
3939: l_hire_date varchar2(10):='';
3940: l_ptp NUMBER;
3941: BEGIN
3942: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
3943:
3944: --Fetch the hire date for this assignment
3945: l_ret_val:= Get_Hire_Date(p_assignment_id
3946: ,p_business_group_id
3964: CLOSE csr_get_overide_ptp_val;
3965:
3966: p_data_element_value := lpad(fnd_number.number_to_canonical(ceil(l_ptp)),3,'0')||'000';
3967:
3968: Hr_Utility.set_location(' Leaving: ' ||l_proc_name,20);
3969: RETURN l_ret_val;
3970: EXCEPTION
3971: WHEN OTHERS THEN
3972: p_error_message :='SQL-ERRM :'||SQLERRM;
3969: RETURN l_ret_val;
3970: EXCEPTION
3971: WHEN OTHERS THEN
3972: p_error_message :='SQL-ERRM :'||SQLERRM;
3973: Hr_Utility.set_location('..'||p_error_message,30);
3974: Hr_Utility.set_location('Leaving: '||l_proc_name,40);
3975: RETURN l_ret_val;
3976: END Get_Hire_Date_PTP;
3977: -- ===============================================================================
3970: EXCEPTION
3971: WHEN OTHERS THEN
3972: p_error_message :='SQL-ERRM :'||SQLERRM;
3973: Hr_Utility.set_location('..'||p_error_message,30);
3974: Hr_Utility.set_location('Leaving: '||l_proc_name,40);
3975: RETURN l_ret_val;
3976: END Get_Hire_Date_PTP;
3977: -- ===============================================================================
3978: -- ~ Function to get the incidental worker code as of hire date
3999: l_ret_val Number:=-1;
4000: l_hire_date varchar2(10):='';
4001: l_iwc varchar2(1);
4002: BEGIN
4003: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
4004:
4005: --Fetch the hire date for this assignment
4006: l_ret_val:= Get_Hire_Date(p_assignment_id
4007: ,p_business_group_id
4019: l_iwc := '1';
4020: END IF;
4021: p_data_element_value:=l_iwc;
4022:
4023: Hr_Utility.set_location(' Leaving: ' ||l_proc_name,20);
4024: RETURN l_ret_val;
4025:
4026: EXCEPTION
4027: WHEN OTHERS THEN
4025:
4026: EXCEPTION
4027: WHEN OTHERS THEN
4028: p_error_message :='SQL-ERRM :'||SQLERRM;
4029: Hr_Utility.set_location('..'||p_error_message,30);
4030: Hr_Utility.set_location('Leaving: '||l_proc_name,40);
4031: RETURN l_ret_val;
4032: END Get_Hire_Date_IWC;
4033:
4026: EXCEPTION
4027: WHEN OTHERS THEN
4028: p_error_message :='SQL-ERRM :'||SQLERRM;
4029: Hr_Utility.set_location('..'||p_error_message,30);
4030: Hr_Utility.set_location('Leaving: '||l_proc_name,40);
4031: RETURN l_ret_val;
4032: END Get_Hire_Date_IWC;
4033:
4034: -- ===============================================================================
4101: l_ret_val Number:=0;
4102: l_end_date DATE;
4103: l_check varchar2(1);
4104: BEGIN
4105: Hr_Utility.set_location(' Entering: ' ||l_proc_name,10);
4106: /*OPEN csr_chk_primary_asg(p_assignment_id, p_effective_date);
4107: FETCH csr_chk_primary_asg INTO l_check;
4108: IF csr_chk_primary_asg%FOUND THEN
4109: OPEN csr_get_assgn_end_date(p_assignment_id);
4133: p_data_element_value:=to_char(l_end_date,'YYYYMMDD');
4134: END IF;
4135: CLOSE csr_get_term_date;
4136:
4137: Hr_Utility.set_location(' Leaving: ' ||l_proc_name,50);
4138: RETURN l_ret_val;
4139: EXCEPTION
4140: WHEN OTHERS THEN
4141: p_error_message :='SQL-ERRM :'||SQLERRM;
4138: RETURN l_ret_val;
4139: EXCEPTION
4140: WHEN OTHERS THEN
4141: p_error_message :='SQL-ERRM :'||SQLERRM;
4142: Hr_Utility.set_location('..'||p_error_message,30);
4143: Hr_Utility.set_location('Leaving: '||l_proc_name,40);
4144: RETURN l_ret_val;
4145: END Get_End_Date_Of_Employment;
4146: -- ===============================================================================
4139: EXCEPTION
4140: WHEN OTHERS THEN
4141: p_error_message :='SQL-ERRM :'||SQLERRM;
4142: Hr_Utility.set_location('..'||p_error_message,30);
4143: Hr_Utility.set_location('Leaving: '||l_proc_name,40);
4144: RETURN l_ret_val;
4145: END Get_End_Date_Of_Employment;
4146: -- ===============================================================================
4147: -- ~ Get_Pension_Salary
4259: l_period_start_date DATE;
4260:
4261:
4262: Begin
4263: Hr_Utility.set_location(' Entering: ' ||l_proc_name,10);
4264:
4265: l_curr_year:=Get_year(p_assignment_id
4266: ,g_extract_params(p_business_group_id).extract_start_date
4267: ,g_extract_params(p_business_group_id).extract_end_date);
4301: IF l_pen_sal_date < to_date(l_start_date_PTP,'YYYYMMDD') THEN
4302: l_pen_sal_date:=to_date(l_start_date_PTP,'YYYYMMDD');
4303: END IF;
4304:
4305: Hr_Utility.set_location(' Pen Sal Date ' ||l_pen_sal_date,30);
4306:
4307:
4308: --Check whether there is retro change in pension salary for current year or not
4309: OPEN csr_chk_curr_year_pen_sal_chg(p_assignment_id,l_pen_sal_date);
4321: ELSE
4322: l_chg_amt:=0;
4323: END IF;
4324: CLOSE csr_chk_curr_year_pen_sal_chg;
4325: Hr_Utility.set_location('l_chg_amt' ||l_chg_amt,30);
4326: --Get the original pension salary
4327: l_balance_amount:= Get_Balance_Value(p_assignment_id
4328: ,p_business_group_id
4329: ,'PGGM Pension Salary'
4331: ,l_pen_sal_date
4332: ,last_day(l_pen_sal_date) -- last day of the month
4333: );
4334:
4335: Hr_Utility.set_location('l_balance_amount' ||l_balance_amount,30);
4336: --Actual Amount is summation of Retro amount and original pension salary
4337: p_data_element_value := CEIL(l_balance_amount+l_chg_amt);
4338: p_data_element_value:=lpad(p_data_element_value,6,'0');
4339: l_ret_val:=0;
4430: IF l_pen_sal_date < to_date(l_start_date_PTP,'YYYYMMDD') THEN
4431: l_pen_sal_date:=to_date(l_start_date_PTP,'YYYYMMDD');
4432: END IF;
4433:
4434: Hr_Utility.set_location(' Pen Sal Date ' ||l_pen_sal_date,30);
4435:
4436:
4437: --Check whether there is retro change in pension salary for current year or not
4438: OPEN csr_chk_curr_year_pen_sal_chg(p_assignment_id,l_pen_sal_date);
4450: ELSE
4451: l_chg_amt:=0;
4452: END IF;
4453: CLOSE csr_chk_curr_year_pen_sal_chg;
4454: Hr_Utility.set_location('l_chg_amt' ||l_chg_amt,30);
4455: --Get the original pension salary
4456: l_balance_amount:= Get_Balance_Value(p_assignment_id
4457: ,p_business_group_id
4458: ,'PGGM Pension Salary'
4461: ,g_extract_params(p_business_group_id).extract_end_date
4462: -- last day of the month
4463: );
4464:
4465: Hr_Utility.set_location('l_balance_amount' ||l_balance_amount,30);
4466: --Actual Amount is summation of Retro amount and original pension salary
4467: p_data_element_value:=CEIL(l_balance_amount+l_chg_amt);
4468: --p_data_element_value:=lpad(p_data_element_value,6,'0');
4469:
4481: END IF;--Record 060 check
4482: p_data_element_value:=lpad(p_data_element_value,6,'0');
4483: END IF;---End of Type of report check
4484:
4485: Hr_Utility.set_location(' Leaving: ' ||l_proc_name,40);
4486: RETURN l_ret_val;
4487: EXCEPTION
4488: WHEN OTHERS THEN
4489: p_error_message :='SQL-ERRM :'||SQLERRM;
4486: RETURN l_ret_val;
4487: EXCEPTION
4488: WHEN OTHERS THEN
4489: p_error_message :='SQL-ERRM :'||SQLERRM;
4490: Hr_Utility.set_location('..'||p_error_message,50);
4491: Hr_Utility.set_location('Leaving: '||l_proc_name,60);
4492: l_ret_val:=1;
4493: RETURN l_ret_val;
4494: END Get_Pension_Salary;
4487: EXCEPTION
4488: WHEN OTHERS THEN
4489: p_error_message :='SQL-ERRM :'||SQLERRM;
4490: Hr_Utility.set_location('..'||p_error_message,50);
4491: Hr_Utility.set_location('Leaving: '||l_proc_name,60);
4492: l_ret_val:=1;
4493: RETURN l_ret_val;
4494: END Get_Pension_Salary;
4495: -- ===============================================================================
4641: asg_ids_rec csr_get_asg_action_ids%ROWTYPE;
4642: l_period_start_date DATE;
4643: l_period_end_date DATE;
4644: Begin
4645: --hr_utility.trace_on(null,'SS');
4646: Hr_Utility.set_location(' Entering: ' ||l_proc_name,10);
4647:
4648: IF g_rec_081_type='C' THEN -- Current Year
4649: --Get Hours Worked from Balance
4642: l_period_start_date DATE;
4643: l_period_end_date DATE;
4644: Begin
4645: --hr_utility.trace_on(null,'SS');
4646: Hr_Utility.set_location(' Entering: ' ||l_proc_name,10);
4647:
4648: IF g_rec_081_type='C' THEN -- Current Year
4649: --Get Hours Worked from Balance
4650: l_balance_amount_h:=Get_Balance_Value(p_assignment_id
4667: --Get input value id of extra hours
4668: OPEN csr_get_input_val_id;
4669: FETCH csr_get_input_val_id INTO l_input_value_id;
4670: CLOSE csr_get_input_val_id;
4671: Hr_Utility.set_location('l_input_value_id for extra hours'||l_input_value_id,20);
4672: --Get total extra hours from run results of whole year (current)
4673: OPEN csr_get_asg_action_ids( p_assignment_id
4674: ,g_extract_params(p_business_group_id).extract_start_date
4675: ,g_extract_params(p_business_group_id).extract_end_date
4676: );
4677: LOOP
4678: FETCH csr_get_asg_action_ids INTO asg_ids_rec;
4679: EXIT WHEN csr_get_asg_action_ids%NOTFOUND;
4680: Hr_Utility.set_location('asg action ids found'||asg_ids_rec.assignment_action_id,20);
4681: OPEN csr_get_ele_entry_id( p_assignment_id
4682: ,asg_ids_rec.date_earned
4683: );
4684: FETCH csr_get_ele_entry_id INTO l_element_entry_id;
4683: );
4684: FETCH csr_get_ele_entry_id INTO l_element_entry_id;
4685:
4686: IF csr_get_ele_entry_id%FOUND THEN
4687: Hr_Utility.set_location('l_element_entry_id found '||l_element_entry_id,21);
4688: OPEN csr_get_run_result_value
4689: ( p_element_entry_id => l_element_entry_id
4690: ,p_input_value_id => l_input_value_id
4691: ,p_asg_act_id => asg_ids_rec.assignment_action_id
4692: );
4693: FETCH csr_get_run_result_value INTO l_extra_hours;
4694: IF csr_get_run_result_value%FOUND THEN
4695:
4696: Hr_Utility.set_location('run result for extra hours found '||l_extra_hours,23);
4697: ELSE
4698: Hr_Utility.set_location('run result for extra hours not found ',24);
4699: END IF;
4700: CLOSE csr_get_run_result_value;
4694: IF csr_get_run_result_value%FOUND THEN
4695:
4696: Hr_Utility.set_location('run result for extra hours found '||l_extra_hours,23);
4697: ELSE
4698: Hr_Utility.set_location('run result for extra hours not found ',24);
4699: END IF;
4700: CLOSE csr_get_run_result_value;
4701: l_total_extra_hours := l_total_extra_hours + NVL(l_extra_hours,0);
4702: l_extra_hours :=0;
4714: FETCH csr_retro_extra_hours INTO l_sum_extra_hours;
4715: CLOSE csr_retro_extra_hours;
4716:
4717: l_total_extra_hours := l_total_extra_hours + NVL(l_sum_extra_hours,0);
4718: Hr_Utility.set_location('l_total_extra_hours'||l_total_extra_hours,20);
4719: --End Extra Hours
4720: OPEN csr_retro_hours_worked(p_assignment_id
4721: ,g_extract_params(p_business_group_id).extract_start_date
4722: ,g_extract_params(p_business_group_id).extract_end_date
4723: );
4724: FETCH csr_retro_hours_worked INTO l_sum_hours_worked;
4725: CLOSE csr_retro_hours_worked;
4726:
4727: Hr_Utility.set_location('l_sum_hours_worked'||l_sum_hours_worked,20);
4728:
4729: OPEN csr_retro_total_hours(p_assignment_id
4730: ,g_extract_params(p_business_group_id).extract_start_date
4731: ,g_extract_params(p_business_group_id).extract_end_date
4731: ,g_extract_params(p_business_group_id).extract_end_date
4732: );
4733: FETCH csr_retro_total_hours INTO l_sum_total_hours;
4734: CLOSE csr_retro_total_hours;
4735: Hr_Utility.set_location('l_sum_total_hours'||l_sum_total_hours,30);
4736: --
4737:
4738: l_sum_hours_worked:=NVL(l_sum_hours_worked,0) + NVL(l_total_extra_hours,0) + l_balance_amount_h;
4739: l_sum_total_hours :=NVL(l_sum_total_hours,0) + l_balance_amount_t;
4827:
4828: l_total_extra_hours := l_total_extra_hours + NVL(l_sum_extra_hours,0);
4829: ---------------------End Extra Hours ---------------------------------------
4830:
4831: Hr_Utility.set_location('l_total_extra_hours'||l_total_extra_hours,50);
4832:
4833: OPEN csr_retro_hours_worked(p_assignment_id
4834: ,l_period_start_date
4835: ,g_extract_params(p_business_group_id).extract_end_date
4836: );
4837: FETCH csr_retro_hours_worked INTO l_sum_hours_worked;
4838: CLOSE csr_retro_hours_worked;
4839:
4840: Hr_Utility.set_location('l_sum_hours_worked'||l_sum_hours_worked,20);
4841:
4842: OPEN csr_retro_total_hours(p_assignment_id
4843: ,l_period_start_date
4844: ,g_extract_params(p_business_group_id).extract_end_date
4844: ,g_extract_params(p_business_group_id).extract_end_date
4845: );
4846: FETCH csr_retro_total_hours INTO l_sum_total_hours;
4847: CLOSE csr_retro_total_hours;
4848: Hr_Utility.set_location('l_sum_total_hours'||l_sum_total_hours,30);
4849:
4850:
4851:
4852: l_sum_hours_worked:=NVL(l_sum_hours_worked,0) + NVL(l_total_extra_hours,0) + l_balance_amount_h;
4859: p_data_element_value:=0;
4860: END IF;
4861:
4862: --p_data_element_value:=lpad(p_data_element_value,6,'0');
4863: Hr_Utility.set_location('p_data_element_value'||p_data_element_value,50);
4864: END IF;
4865: -- hr_utility.trace_off;
4866: l_ret_val:=0;
4867: Hr_Utility.set_location(' Leaving: ' ||l_proc_name,50);
4861:
4862: --p_data_element_value:=lpad(p_data_element_value,6,'0');
4863: Hr_Utility.set_location('p_data_element_value'||p_data_element_value,50);
4864: END IF;
4865: -- hr_utility.trace_off;
4866: l_ret_val:=0;
4867: Hr_Utility.set_location(' Leaving: ' ||l_proc_name,50);
4868: RETURN l_ret_val;
4869: EXCEPTION
4863: Hr_Utility.set_location('p_data_element_value'||p_data_element_value,50);
4864: END IF;
4865: -- hr_utility.trace_off;
4866: l_ret_val:=0;
4867: Hr_Utility.set_location(' Leaving: ' ||l_proc_name,50);
4868: RETURN l_ret_val;
4869: EXCEPTION
4870: WHEN OTHERS THEN
4871: p_error_message :='SQL-ERRM :'||SQLERRM;
4868: RETURN l_ret_val;
4869: EXCEPTION
4870: WHEN OTHERS THEN
4871: p_error_message :='SQL-ERRM :'||SQLERRM;
4872: Hr_Utility.set_location('..'||p_error_message,30);
4873: Hr_Utility.set_location('Leaving: '||l_proc_name,40);
4874: l_ret_val:=1;
4875: RETURN l_ret_val;
4876: END Get_Final_Part_Time_Val;
4869: EXCEPTION
4870: WHEN OTHERS THEN
4871: p_error_message :='SQL-ERRM :'||SQLERRM;
4872: Hr_Utility.set_location('..'||p_error_message,30);
4873: Hr_Utility.set_location('Leaving: '||l_proc_name,40);
4874: l_ret_val:=1;
4875: RETURN l_ret_val;
4876: END Get_Final_Part_Time_Val;
4877: -- ===============================================================================
4945: l_ptp_value NUMBER:=0;
4946: l_ptp_diff NUMBER:=0;
4947: l_count NUMBER:=0;
4948: Begin
4949: Hr_Utility.set_location(' Entering: ' ||l_proc_name,10);
4950:
4951: IF g_extract_params(p_business_group_id).extract_type='Y' THEN
4952: l_ret_val:=Get_Final_Part_Time_Val
4953: ( p_assignment_id
4956: ,p_error_message
4957: ,p_data_element_value
4958: );
4959: ELSE --For Monthly Report
4960: Hr_Utility.set_location('g_ptp_index '||g_ptp_index,30);
4961: --Depending upon type of change in part time percentage
4962: --or incidental worker code set the part time percentage value
4963:
4964: --080 appears due to current changes in Incidental worker code
4995:
4996: ELSE
4997: --080 appears due to current changes in Part time percentage
4998: IF ( g_ptp_index=100 ) THEN
4999: Hr_Utility.set_location('g_ptp_index+g_080_index' ||(g_ptp_index+g_080_index),35);
5000: l_ptp_value:=g_rcd_080(g_ptp_index+g_080_index).part_time_factor;
5001:
5002: ELSE
5003: IF(g_ptp_index=200) THEN
5029:
5030: p_data_element_value := lpad(fnd_number.number_to_canonical(ceil(l_ptp_value)),3,'0')||'000';
5031:
5032: END IF;
5033: Hr_Utility.set_location(' Leaving: ' ||l_proc_name,50);
5034: l_ret_val:=0;
5035: RETURN l_ret_val;
5036: EXCEPTION
5037: WHEN OTHERS THEN
5035: RETURN l_ret_val;
5036: EXCEPTION
5037: WHEN OTHERS THEN
5038: p_error_message :='SQL-ERRM :'||SQLERRM;
5039: Hr_Utility.set_location('..'||p_error_message,30);
5040: Hr_Utility.set_location('Leaving: '||l_proc_name,40);
5041: l_ret_val:=1;
5042: RETURN l_ret_val;
5043: END Get_Part_Time_Percent;
5036: EXCEPTION
5037: WHEN OTHERS THEN
5038: p_error_message :='SQL-ERRM :'||SQLERRM;
5039: Hr_Utility.set_location('..'||p_error_message,30);
5040: Hr_Utility.set_location('Leaving: '||l_proc_name,40);
5041: l_ret_val:=1;
5042: RETURN l_ret_val;
5043: END Get_Part_Time_Percent;
5044: -- ===============================================================================
5087: l_error_message varchar2(150);
5088: l_orig_date_earned DATE;
5089: asgact_rec csr_asg_act%ROWTYPE;
5090: Begin
5091: Hr_Utility.set_location(' Entering: ' ||l_proc_name,10);
5092: Hr_Utility.set_location(' g_080_index'|| g_080_index,15);
5093: Hr_Utility.set_location(' g_ptp_index'|| g_ptp_index,20);
5094: -- Calculating value for Retro Change
5095: IF g_ptp_index = 400 THEN
5088: l_orig_date_earned DATE;
5089: asgact_rec csr_asg_act%ROWTYPE;
5090: Begin
5091: Hr_Utility.set_location(' Entering: ' ||l_proc_name,10);
5092: Hr_Utility.set_location(' g_080_index'|| g_080_index,15);
5093: Hr_Utility.set_location(' g_ptp_index'|| g_ptp_index,20);
5094: -- Calculating value for Retro Change
5095: IF g_ptp_index = 400 THEN
5096: IF(g_rcd_080(g_ptp_index + g_080_index).incidental_code = 1 ) THEN
5089: asgact_rec csr_asg_act%ROWTYPE;
5090: Begin
5091: Hr_Utility.set_location(' Entering: ' ||l_proc_name,10);
5092: Hr_Utility.set_location(' g_080_index'|| g_080_index,15);
5093: Hr_Utility.set_location(' g_ptp_index'|| g_ptp_index,20);
5094: -- Calculating value for Retro Change
5095: IF g_ptp_index = 400 THEN
5096: IF(g_rcd_080(g_ptp_index + g_080_index).incidental_code = 1 ) THEN
5097: l_inci_wkr_code:=0;
5130: END IF;
5131:
5132: p_data_element_value:=l_inci_wkr_code;
5133:
5134: Hr_Utility.set_location(' Leaving: ' ||l_proc_name,50);
5135: l_ret_val:=0;
5136: RETURN l_ret_val;
5137: EXCEPTION
5138: WHEN OTHERS THEN
5136: RETURN l_ret_val;
5137: EXCEPTION
5138: WHEN OTHERS THEN
5139: p_error_message :='SQL-ERRM :'||SQLERRM;
5140: Hr_Utility.set_location('..'||p_error_message,30);
5141: Hr_Utility.set_location('Leaving: '||l_proc_name,40);
5142: l_ret_val:=1;
5143: RETURN l_ret_val;
5144: END Get_Incidental_Worker;
5137: EXCEPTION
5138: WHEN OTHERS THEN
5139: p_error_message :='SQL-ERRM :'||SQLERRM;
5140: Hr_Utility.set_location('..'||p_error_message,30);
5141: Hr_Utility.set_location('Leaving: '||l_proc_name,40);
5142: l_ret_val:=1;
5143: RETURN l_ret_val;
5144: END Get_Incidental_Worker;
5145: -- ===============================================================================
5162: l_pay_day NUMBER;
5163: l_pay_start_date DATE;
5164: l_pay_end_date DATE;
5165: Begin
5166: Hr_Utility.set_location(' Entering: ' ||l_proc_name,10);
5167:
5168: --Current year is the irregular payment year
5169: l_irr_pay_year:=Get_year(p_assignment_id
5170: ,g_extract_params(p_business_group_id).extract_start_date
5171: ,g_extract_params(p_business_group_id).extract_end_date);
5172:
5173: p_data_element_value:=l_irr_pay_year;
5174:
5175: Hr_Utility.set_location(' Leaving: ' ||l_proc_name,50);
5176: l_ret_val:=0;
5177: RETURN l_ret_val;
5178: EXCEPTION
5179: WHEN OTHERS THEN
5177: RETURN l_ret_val;
5178: EXCEPTION
5179: WHEN OTHERS THEN
5180: p_error_message :='SQL-ERRM :'||SQLERRM;
5181: Hr_Utility.set_location('..'||p_error_message,30);
5182: Hr_Utility.set_location('Leaving: '||l_proc_name,40);
5183: l_ret_val:=1;
5184: RETURN l_ret_val;
5185: END Get_Irregular_Payment_Year;
5178: EXCEPTION
5179: WHEN OTHERS THEN
5180: p_error_message :='SQL-ERRM :'||SQLERRM;
5181: Hr_Utility.set_location('..'||p_error_message,30);
5182: Hr_Utility.set_location('Leaving: '||l_proc_name,40);
5183: l_ret_val:=1;
5184: RETURN l_ret_val;
5185: END Get_Irregular_Payment_Year;
5186: -- ===============================================================================
5217: l_pay_start_date DATE;
5218: l_org_id NUMBER;
5219:
5220: BEGIN
5221: Hr_Utility.set_location(' Entering: ' ||l_proc_name,10);
5222:
5223: l_irr_pay_amt :=Get_Balance_Value(p_assignment_id
5224: ,p_business_group_id
5225: ,'PGGM Pensions Irregular Payments'
5234: ELSE
5235: p_data_element_value:=l_irr_pay_amt;
5236: END IF; */
5237:
5238: Hr_Utility.set_location(' Leaving: ' ||l_proc_name,50);
5239: l_ret_val:=0;
5240: RETURN l_ret_val;
5241: EXCEPTION
5242: WHEN OTHERS THEN
5240: RETURN l_ret_val;
5241: EXCEPTION
5242: WHEN OTHERS THEN
5243: p_error_message :='SQL-ERRM :'||SQLERRM;
5244: Hr_Utility.set_location('..'||p_error_message,30);
5245: Hr_Utility.set_location('Leaving: '||l_proc_name,40);
5246: l_ret_val:=1;
5247: RETURN l_ret_val;
5248: END Get_Irregular_Payment_Amt;
5241: EXCEPTION
5242: WHEN OTHERS THEN
5243: p_error_message :='SQL-ERRM :'||SQLERRM;
5244: Hr_Utility.set_location('..'||p_error_message,30);
5245: Hr_Utility.set_location('Leaving: '||l_proc_name,40);
5246: l_ret_val:=1;
5247: RETURN l_ret_val;
5248: END Get_Irregular_Payment_Amt;
5249:
5261: l_proc_name Varchar2(150) :=g_proc_name || 'Get_Final_PTF_Year';
5262: l_ret_val Number:=0;
5263: l_final_year Number;
5264: BEGIN
5265: Hr_Utility.set_location(' Entering: ' ||l_proc_name,10);
5266: --If called from extract
5267: IF g_rec_081_type='C' THEN
5268:
5269: l_final_year:=Get_year(p_assignment_id
5274: IF g_rec_081_type='P' THEN
5275: p_data_element_value:=g_rcd_081(g_081_index).year_of_change;
5276: END IF;
5277: END IF;
5278: Hr_Utility.set_location(' Leaving: ' ||l_proc_name,50);
5279: RETURN l_ret_val;
5280: EXCEPTION
5281: WHEN OTHERS THEN
5282: p_error_message :='SQL-ERRM :'||SQLERRM;
5279: RETURN l_ret_val;
5280: EXCEPTION
5281: WHEN OTHERS THEN
5282: p_error_message :='SQL-ERRM :'||SQLERRM;
5283: Hr_Utility.set_location('..'||p_error_message,30);
5284: Hr_Utility.set_location('Leaving: '||l_proc_name,40);
5285: l_ret_val:=1;
5286: RETURN l_ret_val;
5287: END Get_Final_PTF_Year;
5280: EXCEPTION
5281: WHEN OTHERS THEN
5282: p_error_message :='SQL-ERRM :'||SQLERRM;
5283: Hr_Utility.set_location('..'||p_error_message,30);
5284: Hr_Utility.set_location('Leaving: '||l_proc_name,40);
5285: l_ret_val:=1;
5286: RETURN l_ret_val;
5287: END Get_Final_PTF_Year;
5288: -- ===============================================================================
5304: l_start_date_PTP VARCHAR2(10);
5305: l_period_start_date DATE;
5306:
5307: BEGIN
5308: Hr_Utility.set_location(' Entering: ' ||l_proc_name,10);
5309:
5310: --Get the current year
5311: l_curr_year:=Get_year(p_assignment_id
5312: ,g_extract_params(p_business_group_id).extract_start_date
5311: l_curr_year:=Get_year(p_assignment_id
5312: ,g_extract_params(p_business_group_id).extract_start_date
5313: ,g_extract_params(p_business_group_id).extract_end_date);
5314:
5315: Hr_Utility.set_location(' l_year ' ||l_curr_year,15);
5316:
5317:
5318: IF g_extract_params(p_business_group_id).extract_type='Y' THEN --Yearly Report
5319: l_curr_year:=l_curr_year+1;
5339: IF l_pen_sal_date < to_date(l_start_date_PTP,'YYYYMMDD') THEN
5340: l_pen_sal_date:=to_date(l_start_date_PTP,'YYYYMMDD');
5341: END IF;
5342:
5343: Hr_Utility.set_location(' Pen Sal Date ' ||l_pen_sal_date,30);
5344:
5345: p_data_element_value:=to_char(l_pen_sal_date,'YYYYMMDD');
5346: l_ret_val:=0;
5347: ELSE --Monthy report
5380: END IF; --060 or other record
5381:
5382: End IF;-- Report type check
5383:
5384: Hr_Utility.set_location(' Leaving: ' ||l_proc_name,50);
5385: l_ret_val:=0;
5386: RETURN l_ret_val;
5387: EXCEPTION
5388: WHEN OTHERS THEN
5386: RETURN l_ret_val;
5387: EXCEPTION
5388: WHEN OTHERS THEN
5389: p_error_message :='SQL-ERRM :'||SQLERRM;
5390: Hr_Utility.set_location('..'||p_error_message,30);
5391: Hr_Utility.set_location('Leaving: '||l_proc_name,40);
5392: l_ret_val:=1;
5393: RETURN l_ret_val;
5394: END Get_ST_DT_Change_Pens_Sal;
5387: EXCEPTION
5388: WHEN OTHERS THEN
5389: p_error_message :='SQL-ERRM :'||SQLERRM;
5390: Hr_Utility.set_location('..'||p_error_message,30);
5391: Hr_Utility.set_location('Leaving: '||l_proc_name,40);
5392: l_ret_val:=1;
5393: RETURN l_ret_val;
5394: END Get_ST_DT_Change_Pens_Sal;
5395: -- ===============================================================================
5435:
5436: l_proc_name Varchar2(150) :=g_proc_name || 'Get_ST_DT_Chg_Part_Time_Per';
5437: l_ret_val NUMBER:=0;
5438: BEGIN
5439: Hr_Utility.set_location('Entering:' ||l_proc_name,10);
5440:
5441: Hr_Utility.set_location(' ptp_chg_date ' ||g_rcd_080(g_ptp_index+g_080_index).part_time_pct_dt_change,25);
5442: Hr_Utility.set_location(' g_ptp_index,g_080_index'||g_ptp_index||g_080_index,25);
5443:
5437: l_ret_val NUMBER:=0;
5438: BEGIN
5439: Hr_Utility.set_location('Entering:' ||l_proc_name,10);
5440:
5441: Hr_Utility.set_location(' ptp_chg_date ' ||g_rcd_080(g_ptp_index+g_080_index).part_time_pct_dt_change,25);
5442: Hr_Utility.set_location(' g_ptp_index,g_080_index'||g_ptp_index||g_080_index,25);
5443:
5444: p_data_element_value:=to_char(g_rcd_080(g_ptp_index+g_080_index).part_time_pct_dt_change,'YYYYMMDD');
5445:
5438: BEGIN
5439: Hr_Utility.set_location('Entering:' ||l_proc_name,10);
5440:
5441: Hr_Utility.set_location(' ptp_chg_date ' ||g_rcd_080(g_ptp_index+g_080_index).part_time_pct_dt_change,25);
5442: Hr_Utility.set_location(' g_ptp_index,g_080_index'||g_ptp_index||g_080_index,25);
5443:
5444: p_data_element_value:=to_char(g_rcd_080(g_ptp_index+g_080_index).part_time_pct_dt_change,'YYYYMMDD');
5445:
5446:
5443:
5444: p_data_element_value:=to_char(g_rcd_080(g_ptp_index+g_080_index).part_time_pct_dt_change,'YYYYMMDD');
5445:
5446:
5447: Hr_Utility.set_location(' Leaving: ' ||l_proc_name,50);
5448: l_ret_val:=0;
5449: RETURN l_ret_val;
5450: EXCEPTION
5451: WHEN OTHERS THEN
5449: RETURN l_ret_val;
5450: EXCEPTION
5451: WHEN OTHERS THEN
5452: p_error_message :='SQL-ERRM :'||SQLERRM;
5453: Hr_Utility.set_location('..'||p_error_message,30);
5454: Hr_Utility.set_location('Leaving: '||l_proc_name,40);
5455: l_ret_val:=1;
5456: RETURN l_ret_val;
5457: END Get_ST_DT_Chg_Part_Time_Per;
5450: EXCEPTION
5451: WHEN OTHERS THEN
5452: p_error_message :='SQL-ERRM :'||SQLERRM;
5453: Hr_Utility.set_location('..'||p_error_message,30);
5454: Hr_Utility.set_location('Leaving: '||l_proc_name,40);
5455: l_ret_val:=1;
5456: RETURN l_ret_val;
5457: END Get_ST_DT_Chg_Part_Time_Per;
5458:
5481: l_chk NUMBER := 0;
5482:
5483: BEGIN
5484:
5485: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
5486:
5487: OPEN cur_chk_term;
5488: FETCH cur_chk_term INTO l_chk;
5489: IF cur_chk_term%FOUND THEN
5492: l_return_value := 0;
5493: END IF;
5494: CLOSE cur_chk_term;
5495:
5496: Hr_Utility.set_location('Leaving: '||l_proc_name, 10);
5497: RETURN l_return_value;
5498:
5499: EXCEPTION
5500: WHEN Others THEN
5497: RETURN l_return_value;
5498:
5499: EXCEPTION
5500: WHEN Others THEN
5501: Hr_Utility.set_location('Error -- Leaving: '||l_proc_name, 9);
5502: l_return_value := 0;
5503: RETURN l_return_value;
5504: END chk_term_asg_eff;
5505: -- =============================================================================
5519: l_return_value Number := 1;
5520:
5521:
5522: BEGIN
5523: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
5524:
5525: IF g_primary_assig.EXISTS(p_assignment_id) THEN
5526: p_data_element_value := g_primary_assig(p_assignment_id).organization_id;
5527: END IF;
5526: p_data_element_value := g_primary_assig(p_assignment_id).organization_id;
5527: END IF;
5528:
5529: l_return_value := 0;
5530: Hr_Utility.set_location('Leaving: '||l_proc_name, 5);
5531: RETURN l_return_value;
5532: EXCEPTION
5533: WHEN Others THEN
5534: p_error_message :='SQL-ERRM :'||SQLERRM;
5531: RETURN l_return_value;
5532: EXCEPTION
5533: WHEN Others THEN
5534: p_error_message :='SQL-ERRM :'||SQLERRM;
5535: Hr_Utility.set_location('..'||p_error_message,85);
5536: RETURN l_return_value;
5537: END Org_Id_DataElement;
5538: -- ================================================================================
5539: -- ~ Sort_Id_Generator : It is concatenated with ernum+empNumber+record.
5555: l_employer_number Number;
5556: l_asg_seq_num Varchar2(2);
5557:
5558: BEGIN
5559: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
5560: -- Get the orgId for assigntment ID
5561: l_return_value := Get_PGGM_ER_Num(p_assignment_id
5562: ,p_business_group_id
5563: ,p_effective_date
5570: IF g_primary_assig.EXISTS(p_assignment_id) THEN
5571: l_employee_number := g_primary_assig(p_assignment_id).ee_num;
5572: END IF;
5573:
5574: Hr_Utility.set_location('l_employee_number: '||l_employee_number, 5);
5575: l_employer_number := Lpad(l_employer_number,9,0);
5576: l_employee_number :=Lpad(l_employee_number,6,0);
5577: l_asg_seq_num := g_primary_assig(p_assignment_id).asg_seq_num;
5578: IF To_Number(Nvl(l_asg_seq_num,'1')) < 10 THEN
5583: l_employee_number ||
5584: l_asg_seq_num ||
5585: p_generator_record;
5586:
5587: Hr_Utility.set_location('p_data_element_value: '||p_data_element_value, 5);
5588: Hr_Utility.set_location('Leaving: '||l_proc_name, 5);
5589: l_return_value := 0;
5590:
5591: RETURN l_return_value;
5584: l_asg_seq_num ||
5585: p_generator_record;
5586:
5587: Hr_Utility.set_location('p_data_element_value: '||p_data_element_value, 5);
5588: Hr_Utility.set_location('Leaving: '||l_proc_name, 5);
5589: l_return_value := 0;
5590:
5591: RETURN l_return_value;
5592: EXCEPTION
5591: RETURN l_return_value;
5592: EXCEPTION
5593: WHEN Others THEN
5594: p_error_message :='SQL-ERRM :'||SQLERRM;
5595: Hr_Utility.set_location('..'||p_error_message,85);
5596: RETURN l_return_value;
5597: END Sort_Id_Generator;
5598:
5599: --Multiple Record Processing for Record 060
5612: l_main_rec csr_rslt_dtl%ROWTYPE;
5613: l_new_rec csr_rslt_dtl%ROWTYPE;
5614: l_evt_dates DATE;
5615: BEGIN
5616: Hr_Utility.set_location('Entering: '||l_proc_name,5);
5617:
5618:
5619:
5620: --Fetch the record id from the sequence number
5632: -- increase the OVN by 1
5633: l_main_rec.object_version_NUMBER := Nvl(l_main_rec.object_version_NUMBER,0) + 1;
5634: l_new_rec := l_main_rec;
5635: l_new_rec.val_40 := NULL;
5636: Hr_Utility.set_location('g_rec_060_count'||g_rec_060_count,80);
5637: IF g_rec_060_count > 0 THEN
5638: g_rec060_mult_flag:='Y';
5639: g_060_index:=g_rcd_060.FIRST;
5640: While g_060_index IS NOT NULL
5659: IF (g_rec_060_count >0 )
5660: THEN
5661: l_main_rec.val_40:='Delete';
5662: Upd_Rslt_Dtl(l_main_rec);
5663: Hr_Utility.set_location('Delete main rec',80);
5664: END IF;
5665:
5666: l_ret_val := 0;
5667:
5664: END IF;
5665:
5666: l_ret_val := 0;
5667:
5668: Hr_Utility.set_location('Leaving: '||l_proc_name,80);
5669: RETURN l_ret_val;
5670: EXCEPTION
5671: WHEN Others THEN
5672: p_error_message :='SQL-ERRM :'||SQLERRM;
5669: RETURN l_ret_val;
5670: EXCEPTION
5671: WHEN Others THEN
5672: p_error_message :='SQL-ERRM :'||SQLERRM;
5673: Hr_Utility.set_location('..'||p_error_message,85);
5674: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
5675: RETURN -1;
5676: END Process_Mult_Rec060;
5677:
5670: EXCEPTION
5671: WHEN Others THEN
5672: p_error_message :='SQL-ERRM :'||SQLERRM;
5673: Hr_Utility.set_location('..'||p_error_message,85);
5674: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
5675: RETURN -1;
5676: END Process_Mult_Rec060;
5677:
5678: --Multiple Record Processing for Record 080
5734: l_check_date DATE;
5735: l_ptp_index NUMBER;
5736:
5737: BEGIN
5738: Hr_Utility.set_location('Entering: '||l_proc_name,5);
5739:
5740:
5741: --Fetch the record id from the sequence number
5742: OPEN c_get_rcd_id(8);
5749: ,c_ext_rslt_id => Ben_Ext_Thread.g_ext_rslt_id
5750: ,c_ext_dtl_rcd_id => l_rcd_id
5751: );
5752:
5753: Hr_Utility.set_location('l_rcd_id'||l_rcd_id,10);
5754: Hr_Utility.set_location('g_ext_rslt_id'||Ben_Ext_Thread.g_ext_rslt_id,10);
5755: Hr_Utility.set_location('g_person_id'||g_person_id,10);
5756:
5757: FETCH csr_rslt_dtl INTO l_main_rec;
5750: ,c_ext_dtl_rcd_id => l_rcd_id
5751: );
5752:
5753: Hr_Utility.set_location('l_rcd_id'||l_rcd_id,10);
5754: Hr_Utility.set_location('g_ext_rslt_id'||Ben_Ext_Thread.g_ext_rslt_id,10);
5755: Hr_Utility.set_location('g_person_id'||g_person_id,10);
5756:
5757: FETCH csr_rslt_dtl INTO l_main_rec;
5758: IF csr_rslt_dtl%FOUND THEN
5751: );
5752:
5753: Hr_Utility.set_location('l_rcd_id'||l_rcd_id,10);
5754: Hr_Utility.set_location('g_ext_rslt_id'||Ben_Ext_Thread.g_ext_rslt_id,10);
5755: Hr_Utility.set_location('g_person_id'||g_person_id,10);
5756:
5757: FETCH csr_rslt_dtl INTO l_main_rec;
5758: IF csr_rslt_dtl%FOUND THEN
5759: Hr_Utility.set_location('080 Cursor Found '||l_main_rec.CREATED_BY,10);
5755: Hr_Utility.set_location('g_person_id'||g_person_id,10);
5756:
5757: FETCH csr_rslt_dtl INTO l_main_rec;
5758: IF csr_rslt_dtl%FOUND THEN
5759: Hr_Utility.set_location('080 Cursor Found '||l_main_rec.CREATED_BY,10);
5760: ELSE
5761: Hr_Utility.set_location('080 Cursor NOT Found ',10);
5762: END IF;
5763:
5757: FETCH csr_rslt_dtl INTO l_main_rec;
5758: IF csr_rslt_dtl%FOUND THEN
5759: Hr_Utility.set_location('080 Cursor Found '||l_main_rec.CREATED_BY,10);
5760: ELSE
5761: Hr_Utility.set_location('080 Cursor NOT Found ',10);
5762: END IF;
5763:
5764: CLOSE csr_rslt_dtl;
5765: -- increase the OVN by 1
5767: l_new_rec.val_40 := NULL;
5768: l_new_rec.object_version_NUMBER := Nvl(l_main_rec.object_version_NUMBER,0) + 1;
5769:
5770: g_080_index:=0;
5771: Hr_Utility.set_location('g_rec_080_type1_count: '||g_rec_080_type1_count,15);
5772: -- 1) Loop for Current change in part time percentage
5773: IF g_rec_080_type1_count > 0 THEN
5774: g_ptp_index:=100;
5775: g_080_index:=0;
5774: g_ptp_index:=100;
5775: g_080_index:=0;
5776: FOR l_080_index IN 0..g_rec_080_type1_count-1
5777: LOOP
5778: Hr_Utility.set_location('Loop'||g_080_index,80);
5779:
5780: g_080_display_flag:='Y';
5781: Process_Ext_Rslt_Dtl_Rec
5782: (p_assignment_id => p_assignment_id
5792: g_080_index:=0;
5793:
5794: END IF;-- End of g_rec_080_type1_count check
5795:
5796: Hr_Utility.set_location('g_rec_080_type2_count: '||g_rec_080_type2_count,15);
5797: -- 2) Loop for Retro change in part time percentage
5798: IF g_rec_080_type2_count > 0 THEN
5799: g_ptp_index:=200;
5800: FOR l_080_index IN 0..g_rec_080_type2_count-1
5814: END LOOP;
5815: g_080_index:=0;
5816: END IF;-- End of g_rec_080_type2_count check
5817:
5818: Hr_Utility.set_location('g_rec_080_type3_count: '||g_rec_080_type3_count,15);
5819: -- 3) Loop for Current change in Incidental Worker Code
5820: IF g_rec_080_type3_count > 0 THEN
5821: g_ptp_index:=300;
5822:
5853: END LOOP;
5854: g_080_index:=0;
5855: END IF;-- End of g_rec_080_type1_count check
5856:
5857: Hr_Utility.set_location('g_rec_080_type4_count: '||g_rec_080_type4_count,15);
5858: -- 4) Loop for Retro change in Incidental Worker Code
5859: IF g_rec_080_type4_count > 0 THEN
5860: g_ptp_index:=400;
5861: FOR l_080_index IN 0..g_rec_080_type4_count-1
5863: --Check if extract date is already in the global table
5864: --if extract is already processed during ptp processing then do not insert it
5865: l_ptp_index:=200;
5866: l_check_date:=g_rcd_080(g_ptp_index+g_080_index).part_time_pct_dt_change;
5867: Hr_Utility.set_location('l_check_date'||l_check_date,15);
5868: Hr_Utility.set_location('g_rec_080_type2_count'||g_rec_080_type2_count,15);
5869: For l_index IN 0..g_rec_080_type2_count-1
5870: LOOP
5871: l_080_rec_type4_flag:='Y';
5864: --if extract is already processed during ptp processing then do not insert it
5865: l_ptp_index:=200;
5866: l_check_date:=g_rcd_080(g_ptp_index+g_080_index).part_time_pct_dt_change;
5867: Hr_Utility.set_location('l_check_date'||l_check_date,15);
5868: Hr_Utility.set_location('g_rec_080_type2_count'||g_rec_080_type2_count,15);
5869: For l_index IN 0..g_rec_080_type2_count-1
5870: LOOP
5871: l_080_rec_type4_flag:='Y';
5872: Hr_Utility.set_location('g_rcd_080(l_ptp_index).part_time_pct_dt_change'||g_rcd_080(l_ptp_index).part_time_pct_dt_change,15);
5868: Hr_Utility.set_location('g_rec_080_type2_count'||g_rec_080_type2_count,15);
5869: For l_index IN 0..g_rec_080_type2_count-1
5870: LOOP
5871: l_080_rec_type4_flag:='Y';
5872: Hr_Utility.set_location('g_rcd_080(l_ptp_index).part_time_pct_dt_change'||g_rcd_080(l_ptp_index).part_time_pct_dt_change,15);
5873: IF l_check_date=g_rcd_080(l_ptp_index).part_time_pct_dt_change THEN
5874: l_080_rec_type4_flag:='N';
5875: END IF;
5876: l_ptp_index:=l_ptp_index+1;
5896: -- Delete the 080 record created by extract
5897: IF((g_rec_080_type1_count+g_rec_080_type2_count+g_rec_080_type3_count+g_rec_080_type4_count)>0) THEN
5898: l_main_rec.val_40:='Delete';
5899: Upd_Rslt_Dtl(l_main_rec);
5900: Hr_Utility.set_location('Delete main rec',80);
5901: END IF;
5902:
5903:
5904: l_ret_val := 0;
5902:
5903:
5904: l_ret_val := 0;
5905:
5906: Hr_Utility.set_location('Leaving: '||l_proc_name,80);
5907: RETURN l_ret_val;
5908: EXCEPTION
5909: WHEN Others THEN
5910: p_error_message :='SQL-ERRM :'||SQLERRM;
5907: RETURN l_ret_val;
5908: EXCEPTION
5909: WHEN Others THEN
5910: p_error_message :='SQL-ERRM :'||SQLERRM;
5911: Hr_Utility.set_location('..'||p_error_message,85);
5912: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
5913: RETURN -1;
5914: END Process_Mult_Rec080;
5915:
5908: EXCEPTION
5909: WHEN Others THEN
5910: p_error_message :='SQL-ERRM :'||SQLERRM;
5911: Hr_Utility.set_location('..'||p_error_message,85);
5912: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
5913: RETURN -1;
5914: END Process_Mult_Rec080;
5915:
5916: --Multiple Record Processing for Record 081
5932: l_new_rec csr_rslt_dtl%ROWTYPE;
5933: l_081_count NUMBER:=0;
5934:
5935: BEGIN
5936: Hr_Utility.set_location('Entering: '||l_proc_name,5);
5937:
5938: IF g_rec_081_count > 0 THEN
5939:
5940: --Fetch the record id from the sequence number
5976: -- Delete the 081 record created by extract
5977: IF(g_main_rec_081 = 'D') THEN
5978: l_main_rec.val_40:='Delete';
5979: Upd_Rslt_Dtl(l_main_rec);
5980: Hr_Utility.set_location('Delete main rec',80);
5981: END IF;
5982: END IF;
5983:
5984: l_ret_val := 0;
5982: END IF;
5983:
5984: l_ret_val := 0;
5985:
5986: Hr_Utility.set_location('Leaving: '||l_proc_name,80);
5987: RETURN l_ret_val;
5988: EXCEPTION
5989: WHEN Others THEN
5990: p_error_message :='SQL-ERRM :'||SQLERRM;
5987: RETURN l_ret_val;
5988: EXCEPTION
5989: WHEN Others THEN
5990: p_error_message :='SQL-ERRM :'||SQLERRM;
5991: Hr_Utility.set_location('..'||p_error_message,85);
5992: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
5993: RETURN -1;
5994: END Process_Mult_Rec081;
5995: -- =============================================================================
5988: EXCEPTION
5989: WHEN Others THEN
5990: p_error_message :='SQL-ERRM :'||SQLERRM;
5991: Hr_Utility.set_location('..'||p_error_message,85);
5992: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
5993: RETURN -1;
5994: END Process_Mult_Rec081;
5995: -- =============================================================================
5996: -- Process_Mult_Records: For a given assignment multiple records are created for
6011: l_error_flag Boolean;
6012: l_ret_val Number := 0;
6013:
6014: BEGIN
6015: Hr_Utility.set_location('Entering: '||l_proc_name, 4);
6016:
6017: -- Process Multiple Records for Record 060
6018: BEGIN
6019: l_ret_val := Process_Mult_Rec060
6021: ,p_business_group_id => p_business_group_id
6022: ,p_effective_date => p_effective_date
6023: ,p_error_message => p_error_message
6024: );
6025: Hr_Utility.set_location('..Processed Multi Recds for 060 : '||l_proc_name, 20);
6026: EXCEPTION
6027: WHEN Others THEN
6028: Hr_Utility.set_location('..Error in Multi Recds for 060 : '||l_proc_name, 12);
6029: l_error_message := Substr('SQL-ERRM :'||SQLERRM,1,2000);
6024: );
6025: Hr_Utility.set_location('..Processed Multi Recds for 060 : '||l_proc_name, 20);
6026: EXCEPTION
6027: WHEN Others THEN
6028: Hr_Utility.set_location('..Error in Multi Recds for 060 : '||l_proc_name, 12);
6029: l_error_message := Substr('SQL-ERRM :'||SQLERRM,1,2000);
6030: l_error_flag := TRUE;
6031: END;
6032: IF l_ret_val <> 0 THEN
6042: ,p_business_group_id => p_business_group_id
6043: ,p_effective_date => p_effective_date
6044: ,p_error_message => p_error_message
6045: );
6046: Hr_Utility.set_location('..Processed Multi Recds for 080 : '||l_proc_name, 12);
6047: EXCEPTION
6048: WHEN Others THEN
6049: Hr_Utility.set_location('..Error in Multi Recds for 080 : '||l_proc_name, 12);
6050: l_error_message := Substr('SQL-ERRM :'||SQLERRM,1,2000);
6045: );
6046: Hr_Utility.set_location('..Processed Multi Recds for 080 : '||l_proc_name, 12);
6047: EXCEPTION
6048: WHEN Others THEN
6049: Hr_Utility.set_location('..Error in Multi Recds for 080 : '||l_proc_name, 12);
6050: l_error_message := Substr('SQL-ERRM :'||SQLERRM,1,2000);
6051: l_error_flag := TRUE;
6052: END;
6053: IF l_ret_val <> 0 THEN
6063: ,p_business_group_id => p_business_group_id
6064: ,p_effective_date => p_effective_date
6065: ,p_error_message => p_error_message
6066: );
6067: Hr_Utility.set_location('..Processed Multi Recds for 081 : '||l_proc_name, 20);
6068: EXCEPTION
6069: WHEN Others THEN
6070: Hr_Utility.set_location('..Error in Multi Recds for 081 : '||l_proc_name, 12);
6071: l_error_message := Substr('SQL-ERRM :'||SQLERRM,1,2000);
6066: );
6067: Hr_Utility.set_location('..Processed Multi Recds for 081 : '||l_proc_name, 20);
6068: EXCEPTION
6069: WHEN Others THEN
6070: Hr_Utility.set_location('..Error in Multi Recds for 081 : '||l_proc_name, 12);
6071: l_error_message := Substr('SQL-ERRM :'||SQLERRM,1,2000);
6072: l_error_flag := TRUE;
6073: END;
6074: IF l_ret_val <> 0 THEN
6079: END IF;
6080:
6081:
6082:
6083: Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
6084: IF l_error_flag THEN
6085: p_error_message := l_error_message;
6086: RETURN -1;
6087: ELSE
6090:
6091: EXCEPTION
6092: WHEN Others THEN
6093: p_error_message :='SQL-ERRM :'||SQLERRM;
6094: Hr_Utility.set_location('..'||p_error_message,85);
6095: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
6096: RETURN -1;
6097:
6098: END Process_Mult_Records;
6091: EXCEPTION
6092: WHEN Others THEN
6093: p_error_message :='SQL-ERRM :'||SQLERRM;
6094: Hr_Utility.set_location('..'||p_error_message,85);
6095: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
6096: RETURN -1;
6097:
6098: END Process_Mult_Records;
6099: -- ===============================================================================
6110: l_return_value Number:=-1;
6111:
6112: BEGIN
6113:
6114: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
6115: Hr_Utility.set_location('p_header_type: '||p_header_type,10);
6116: IF p_header_type = 'EXTRACT_NAME' THEN
6117: p_data_element_value := g_conc_prog_details(0).extract_name;
6118: ELSIF p_header_type = 'PGGM_ER_NAME' THEN
6111:
6112: BEGIN
6113:
6114: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
6115: Hr_Utility.set_location('p_header_type: '||p_header_type,10);
6116: IF p_header_type = 'EXTRACT_NAME' THEN
6117: p_data_element_value := g_conc_prog_details(0).extract_name;
6118: ELSIF p_header_type = 'PGGM_ER_NAME' THEN
6119: p_data_element_value := g_conc_prog_details(0).orgname;
6125: p_data_element_value := to_char(g_conc_prog_details(0).beginningdt,'YYMM');
6126: ELSIF p_header_type = 'END_DT_PAID' THEN
6127: p_data_element_value := g_conc_prog_details(0).endingdt;
6128: ELSIF p_header_type = 'PAYROLL_NAME' THEN
6129: Hr_Utility.set_location('PAYROLL_NAME: '||g_conc_prog_details(0).payrollname, 5);
6130: p_data_element_value := g_conc_prog_details(0).payrollname;
6131: ELSIF p_header_type = 'CON_SET' THEN
6132: p_data_element_value := g_conc_prog_details(0).consolset;
6133: Hr_Utility.set_location('CON_SET: '||l_return_value, 5);
6129: Hr_Utility.set_location('PAYROLL_NAME: '||g_conc_prog_details(0).payrollname, 5);
6130: p_data_element_value := g_conc_prog_details(0).payrollname;
6131: ELSIF p_header_type = 'CON_SET' THEN
6132: p_data_element_value := g_conc_prog_details(0).consolset;
6133: Hr_Utility.set_location('CON_SET: '||l_return_value, 5);
6134: END IF;
6135: Hr_Utility.set_location('Leaving: '||l_proc_name, 45);
6136: l_return_value:=0;
6137: RETURN l_return_value;
6131: ELSIF p_header_type = 'CON_SET' THEN
6132: p_data_element_value := g_conc_prog_details(0).consolset;
6133: Hr_Utility.set_location('CON_SET: '||l_return_value, 5);
6134: END IF;
6135: Hr_Utility.set_location('Leaving: '||l_proc_name, 45);
6136: l_return_value:=0;
6137: RETURN l_return_value;
6138: EXCEPTION
6139: WHEN Others THEN
6137: RETURN l_return_value;
6138: EXCEPTION
6139: WHEN Others THEN
6140: p_error_message :='SQL-ERRM :'||SQLERRM;
6141: Hr_Utility.set_location('..Exception Others Raised at Get_Conc_Prog_Information'||p_error_message,40);
6142: Hr_Utility.set_location('Leaving: '||l_proc_name, 45);
6143: l_return_value:=1;
6144: RETURN l_return_value;
6145: END Get_Conc_Prog_Information;
6138: EXCEPTION
6139: WHEN Others THEN
6140: p_error_message :='SQL-ERRM :'||SQLERRM;
6141: Hr_Utility.set_location('..Exception Others Raised at Get_Conc_Prog_Information'||p_error_message,40);
6142: Hr_Utility.set_location('Leaving: '||l_proc_name, 45);
6143: l_return_value:=1;
6144: RETURN l_return_value;
6145: END Get_Conc_Prog_Information;
6146:
6166: BEGIN
6167:
6168: l_ret_val := 0;
6169:
6170: Hr_Utility.set_location(' Entering : ' || l_proc_name , 5);
6171: Hr_Utility.set_location(' p_assignment_id ' || p_assignment_id , 10);
6172: Hr_Utility.set_location(' p_bg_id is ' || p_business_group_id ,15);
6173: Hr_Utility.set_location(' p_date_earned ' || p_date_earned,20 );
6174: Hr_Utility.set_location(' p_data_ele_cd ' || p_data_element_cd ,25);
6167:
6168: l_ret_val := 0;
6169:
6170: Hr_Utility.set_location(' Entering : ' || l_proc_name , 5);
6171: Hr_Utility.set_location(' p_assignment_id ' || p_assignment_id , 10);
6172: Hr_Utility.set_location(' p_bg_id is ' || p_business_group_id ,15);
6173: Hr_Utility.set_location(' p_date_earned ' || p_date_earned,20 );
6174: Hr_Utility.set_location(' p_data_ele_cd ' || p_data_element_cd ,25);
6175: Hr_Utility.set_location(' g_person_id ' || g_person_id , 30);
6168: l_ret_val := 0;
6169:
6170: Hr_Utility.set_location(' Entering : ' || l_proc_name , 5);
6171: Hr_Utility.set_location(' p_assignment_id ' || p_assignment_id , 10);
6172: Hr_Utility.set_location(' p_bg_id is ' || p_business_group_id ,15);
6173: Hr_Utility.set_location(' p_date_earned ' || p_date_earned,20 );
6174: Hr_Utility.set_location(' p_data_ele_cd ' || p_data_element_cd ,25);
6175: Hr_Utility.set_location(' g_person_id ' || g_person_id , 30);
6176:
6169:
6170: Hr_Utility.set_location(' Entering : ' || l_proc_name , 5);
6171: Hr_Utility.set_location(' p_assignment_id ' || p_assignment_id , 10);
6172: Hr_Utility.set_location(' p_bg_id is ' || p_business_group_id ,15);
6173: Hr_Utility.set_location(' p_date_earned ' || p_date_earned,20 );
6174: Hr_Utility.set_location(' p_data_ele_cd ' || p_data_element_cd ,25);
6175: Hr_Utility.set_location(' g_person_id ' || g_person_id , 30);
6176:
6177: IF (p_data_element_cd = 'PGGM_ER_NUM') THEN
6170: Hr_Utility.set_location(' Entering : ' || l_proc_name , 5);
6171: Hr_Utility.set_location(' p_assignment_id ' || p_assignment_id , 10);
6172: Hr_Utility.set_location(' p_bg_id is ' || p_business_group_id ,15);
6173: Hr_Utility.set_location(' p_date_earned ' || p_date_earned,20 );
6174: Hr_Utility.set_location(' p_data_ele_cd ' || p_data_element_cd ,25);
6175: Hr_Utility.set_location(' g_person_id ' || g_person_id , 30);
6176:
6177: IF (p_data_element_cd = 'PGGM_ER_NUM') THEN
6178:
6171: Hr_Utility.set_location(' p_assignment_id ' || p_assignment_id , 10);
6172: Hr_Utility.set_location(' p_bg_id is ' || p_business_group_id ,15);
6173: Hr_Utility.set_location(' p_date_earned ' || p_date_earned,20 );
6174: Hr_Utility.set_location(' p_data_ele_cd ' || p_data_element_cd ,25);
6175: Hr_Utility.set_location(' g_person_id ' || g_person_id , 30);
6176:
6177: IF (p_data_element_cd = 'PGGM_ER_NUM') THEN
6178:
6179: l_ret_val := Get_PGGM_ER_Num(p_assignment_id
6539:
6540: EXCEPTION
6541: WHEN Others THEN
6542: p_error_message :='SQL-ERRM :'||SQLERRM;
6543: Hr_Utility.set_location('..'||p_error_message,85);
6544: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
6545: RETURN l_ret_val;
6546: END PQP_NL_GET_DATA_ELEMENT_VALUE;
6547:
6540: EXCEPTION
6541: WHEN Others THEN
6542: p_error_message :='SQL-ERRM :'||SQLERRM;
6543: Hr_Utility.set_location('..'||p_error_message,85);
6544: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
6545: RETURN l_ret_val;
6546: END PQP_NL_GET_DATA_ELEMENT_VALUE;
6547:
6548:
6584: l_chg_eff_date DATE;
6585: l_hire_date DATE;
6586:
6587: Begin
6588: Hr_Utility.set_location('Entering : '||l_proc_name, 10);
6589: --For yearly report hide this record
6590: IF NVL(g_extract_params (p_business_group_id).extract_type ,'M') = 'Y' THEN
6591: p_data_element_value := 'N';
6592: RETURN 0;
6601: p_data_element_value := 'N';
6602: END IF;
6603: CLOSE csr_chk_new_hire;
6604: END IF;
6605: Hr_Utility.set_location('Leaving: '||l_proc_name, 5);
6606: l_return_value := 0;
6607: RETURN l_return_value;
6608: EXCEPTION
6609: WHEN Others THEN
6608: EXCEPTION
6609: WHEN Others THEN
6610: p_error_message :='SQL-ERRM :'||SQLERRM;
6611: p_data_element_value := 'N';
6612: Hr_Utility.set_location('..'||p_error_message,85);
6613: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
6614: RETURN l_return_value;
6615: END Record010_Display_Criteria ;
6616:
6609: WHEN Others THEN
6610: p_error_message :='SQL-ERRM :'||SQLERRM;
6611: p_data_element_value := 'N';
6612: Hr_Utility.set_location('..'||p_error_message,85);
6613: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
6614: RETURN l_return_value;
6615: END Record010_Display_Criteria ;
6616:
6617: --============================================================================
6659: l_term_pri_asg_dt DATE;
6660: l_already_terminated NUMBER := 0;
6661:
6662: Begin
6663: Hr_Utility.set_location('Entering : '||l_proc_name, 10);
6664: --For yearly report hide this record
6665: IF NVL(g_extract_params (p_business_group_id).extract_type ,'M') = 'Y' THEN
6666: p_data_element_value := 'N';
6667: RETURN 0;
6695: END IF;
6696: CLOSE csr_chk_terminate_pri_asg;
6697: END IF;
6698: END IF;--type of report
6699: Hr_Utility.set_location('Leaving: '||l_proc_name, 50);
6700: l_return_value := 0;
6701:
6702: RETURN l_return_value;
6703: EXCEPTION
6703: EXCEPTION
6704: WHEN Others THEN
6705: p_error_message :='SQL-ERRM :'||SQLERRM;
6706: p_data_element_value := 'N';
6707: Hr_Utility.set_location('..'||p_error_message,85);
6708: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
6709: RETURN l_return_value;
6710: END Record020_Display_Criteria;
6711:
6704: WHEN Others THEN
6705: p_error_message :='SQL-ERRM :'||SQLERRM;
6706: p_data_element_value := 'N';
6707: Hr_Utility.set_location('..'||p_error_message,85);
6708: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
6709: RETURN l_return_value;
6710: END Record020_Display_Criteria;
6711:
6712: --============================================================================
6774: l_chk_term_asg NUMBER:=0;
6775: l_asg_end_date DATE;
6776: l_period_start_date DATE;
6777: Begin
6778: Hr_Utility.set_location('Entering : '||l_proc_name, 10);
6779: --For yearly report hide this record
6780: IF NVL(g_extract_params (p_business_group_id).extract_type ,'M') = 'Y' THEN
6781: p_data_element_value := 'N';
6782: RETURN 0;
6788: CLOSE csr_get_asg_end_date;
6789: --If assignment is already ended then do not process further
6790: IF NVL(l_asg_end_date,to_Date('47121231','YYYYMMDD')-1) < g_extract_params (p_business_group_id).extract_start_date THEN
6791: p_data_element_value := 'N';
6792: Hr_Utility.set_location('Leaving: '||l_proc_name, 30);
6793: RETURN 0;
6794: END IF;
6795: ----Check for irregular payment balance
6796: --Get Participation start date
6831: );
6832:
6833: IF l_irr_paymnt <= 0 THEN
6834: p_data_element_value := 'N';
6835: Hr_Utility.set_location('Leaving: '||l_proc_name, 35);
6836: Return 0;
6837: END IF;
6838:
6839: l_chk_term_asg:=chk_term_asg_eff
6876: CLOSE csr_chk_primary_asg;
6877: END IF; -- End of End of employment check
6878: END IF;--Type of report
6879:
6880: Hr_Utility.set_location('Leaving: '||l_proc_name, 5);
6881: l_return_value := 0;
6882:
6883: RETURN l_return_value;
6884: EXCEPTION
6884: EXCEPTION
6885: WHEN Others THEN
6886: p_error_message :='SQL-ERRM :'||SQLERRM;
6887: p_data_element_value := 'N';
6888: Hr_Utility.set_location('..'||p_error_message,85);
6889: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
6890: RETURN l_return_value;
6891: END Record030_Display_Criteria;
6892: --============================================================================
6885: WHEN Others THEN
6886: p_error_message :='SQL-ERRM :'||SQLERRM;
6887: p_data_element_value := 'N';
6888: Hr_Utility.set_location('..'||p_error_message,85);
6889: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
6890: RETURN l_return_value;
6891: END Record030_Display_Criteria;
6892: --============================================================================
6893: --This is used to decide the Record_040 hide or show
6990: l_eff_date DATE;
6991: l_country_code varchar2(5);
6992: l_check varchar2(10);
6993: Begin
6994: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
6995: --For yearly report hide this record
6996: IF (NVL(g_extract_params (p_business_group_id).extract_type ,'M') = 'Y') THEN
6997: p_data_element_value := 'N';
6998: RETURN 0;
7009: FETCH csr_chk_chg_address INTO l_chg_addr;
7010:
7011: IF csr_chk_chg_address%FOUND THEN
7012: CLOSE csr_chk_chg_address;
7013: Hr_Utility.set_location('Inside csr_chk_chg_address', 15);
7014: Open c_get_old_cc(l_eff_date);
7015: FETCH c_get_old_cc INTO l_old_country_code;
7016: CLOSE c_get_old_cc;
7017:
7014: Open c_get_old_cc(l_eff_date);
7015: FETCH c_get_old_cc INTO l_old_country_code;
7016: CLOSE c_get_old_cc;
7017:
7018: Hr_Utility.set_location('l_old_country_code'||l_old_country_code, 15);
7019:
7020: Open c_get_new_cc(l_eff_date);
7021: FETCH c_get_new_cc INTO l_new_country_code;
7022: CLOSE c_get_new_cc;
7020: Open c_get_new_cc(l_eff_date);
7021: FETCH c_get_new_cc INTO l_new_country_code;
7022: CLOSE c_get_new_cc;
7023:
7024: Hr_Utility.set_location('l_new_country_code'||l_new_country_code, 15);
7025:
7026: IF (l_old_country_code <> 6030 and l_new_country_code = 6030 ) or
7027: (l_old_country_code = 6030 and l_new_country_code <> 6030 ) or
7028: (l_old_country_code <> 6030 and l_new_country_code <> 6030 )
7027: (l_old_country_code = 6030 and l_new_country_code <> 6030 ) or
7028: (l_old_country_code <> 6030 and l_new_country_code <> 6030 )
7029: THEN
7030: p_data_element_value :='Y';
7031: Hr_Utility.set_location('Leaving: '||l_proc_name, 65);
7032: l_return_value := 0;
7033: RETURN l_return_value;
7034: END IF;
7035:
7049: ,p_business_group_id
7050: ,p_effective_date
7051: ,p_error_message
7052: ,l_country_code);
7053: Hr_Utility.set_location('l_country_code: '||l_country_code ||'for'||g_person_id, 60);
7054: IF (to_number(l_country_code) <> 6030) THEN
7055: p_data_element_value :='Y';
7056: END IF;
7057: END IF;
7063:
7064: END IF;
7065:
7066: End IF;
7067: Hr_Utility.set_location('Leaving: '||l_proc_name, 65);
7068: l_return_value := 0;
7069:
7070: RETURN l_return_value;
7071: EXCEPTION
7070: RETURN l_return_value;
7071: EXCEPTION
7072: WHEN Others THEN
7073: p_error_message :='SQL-ERRM :'||SQLERRM;
7074: Hr_Utility.set_location('..'||p_error_message,85);
7075: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
7076: l_return_value:=1;
7077: RETURN l_return_value;
7078: END Record040_Display_Criteria;
7071: EXCEPTION
7072: WHEN Others THEN
7073: p_error_message :='SQL-ERRM :'||SQLERRM;
7074: Hr_Utility.set_location('..'||p_error_message,85);
7075: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
7076: l_return_value:=1;
7077: RETURN l_return_value;
7078: END Record040_Display_Criteria;
7079: --============================================================================
7140: l_eff_date DATE;
7141: l_period_start_date DATE;
7142:
7143: Begin
7144: --hr_utility.trace_on(null,'SS');
7145: Hr_Utility.set_location('Entering : '||l_proc_name, 10);
7146: --If yearly report then do not show this record
7147: IF NVL(g_extract_params (p_business_group_id).extract_type ,'M') = 'Y' THEN
7148: p_data_element_value := 'N';
7141: l_period_start_date DATE;
7142:
7143: Begin
7144: --hr_utility.trace_on(null,'SS');
7145: Hr_Utility.set_location('Entering : '||l_proc_name, 10);
7146: --If yearly report then do not show this record
7147: IF NVL(g_extract_params (p_business_group_id).extract_type ,'M') = 'Y' THEN
7148: p_data_element_value := 'N';
7149: ELSE
7152:
7153: l_year:=Get_year(p_assignment_id
7154: ,g_extract_params(p_business_group_id).extract_start_date
7155: ,g_extract_params(p_business_group_id).extract_end_date);
7156: Hr_Utility.set_location('l_year : '||l_year, 15);
7157: --Get the period number of payroll from the extract start date
7158: OPEN c_get_period_num(g_extract_params(p_business_group_id).extract_start_date,
7159: p_assignment_id
7160: ,p_effective_date);
7159: p_assignment_id
7160: ,p_effective_date);
7161: FETCH c_get_period_num INTO l_mon;
7162: CLOSE c_get_period_num;
7163: Hr_Utility.set_location('l_mon : '||l_mon, 16);
7164: --Get the period start date
7165: OPEN c_get_period_start_date(l_year
7166: ,p_assignment_id
7167: ,p_effective_date);
7168: FETCH c_get_period_start_date INTO l_period_start_date;
7169: CLOSE c_get_period_start_date;
7170:
7171: l_eff_date:=l_period_start_date;
7172: Hr_Utility.set_location('l_eff_date : '||l_eff_date, 17);
7173: --OPEN csr_chk_active_asg(p_assignment_id,p_business_group_id,l_eff_date);
7174: OPEN csr_chk_active_asg(p_assignment_id,p_business_group_id,p_effective_date);
7175: FETCH csr_chk_active_asg INTO l_chk_entry;
7176: --For First month(pay period) of the year only
7193: End IF;
7194: CLOSE csr_retro_pggm_gen_info_entry ;
7195: END IF;
7196: l_return_value:=0;
7197: Hr_Utility.set_location('Leaving: '||l_proc_name, 65);
7198: -- Hr_Utility.trace_off;
7199: RETURN l_return_value;
7200: EXCEPTION
7201: WHEN Others THEN
7194: CLOSE csr_retro_pggm_gen_info_entry ;
7195: END IF;
7196: l_return_value:=0;
7197: Hr_Utility.set_location('Leaving: '||l_proc_name, 65);
7198: -- Hr_Utility.trace_off;
7199: RETURN l_return_value;
7200: EXCEPTION
7201: WHEN Others THEN
7202: p_error_message :='SQL-ERRM :'||SQLERRM;
7200: EXCEPTION
7201: WHEN Others THEN
7202: p_error_message :='SQL-ERRM :'||SQLERRM;
7203: p_data_element_value := 'N';
7204: Hr_Utility.set_location('..'||p_error_message,85);
7205: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
7206: l_return_value:=1;
7207: RETURN l_return_value;
7208: END Record060_Display_Criteria;
7201: WHEN Others THEN
7202: p_error_message :='SQL-ERRM :'||SQLERRM;
7203: p_data_element_value := 'N';
7204: Hr_Utility.set_location('..'||p_error_message,85);
7205: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
7206: l_return_value:=1;
7207: RETURN l_return_value;
7208: END Record060_Display_Criteria;
7209: --============================================================================
7246: l_period_start_date DATE;
7247: l_period_end_date DATE;
7248:
7249: Begin
7250: Hr_Utility.set_location('Entering : '||l_proc_name, 10);
7251: --If monthly report then hide this record
7252: IF NVL(g_extract_params (p_business_group_id).extract_type ,'M') = 'M' THEN
7253: p_data_element_value := 'N';
7254: RETURN 0;
7270: p_data_element_value := 'Y';
7271: ELSE
7272: CLOSE csr_chk_active_asg_period;
7273: p_data_element_value := 'N';
7274: Hr_Utility.set_location('Leaving: '||l_proc_name, 35);
7275: RETURN 0;
7276: END IF;
7277: END IF;
7278:
7280: l_year:=Get_year(p_assignment_id
7281: ,g_extract_params(p_business_group_id).extract_start_date
7282: ,g_extract_params(p_business_group_id).extract_end_date);
7283:
7284: Hr_Utility.set_location('l_year '||l_year, 25);
7285: --Get the period start and end dates
7286: OPEN c_get_period_start_date(l_year
7287: ,p_assignment_id
7288: ,p_effective_date);
7301: ,'Assignment Period To Date'
7302: ,l_period_start_date
7303: ,l_period_end_date
7304: );
7305: Hr_Utility.set_location('Irregular balance amount'||l_irr_paymnt, 25);
7306: IF l_irr_paymnt > 0 THEN
7307: p_data_element_value := 'Y';
7308: ELSE
7309: p_data_element_value := 'N';
7310: END IF;
7311: END IF;
7312:
7313: l_return_value:=0;
7314: Hr_Utility.set_location('Leaving: '||l_proc_name, 65);
7315: RETURN l_return_value;
7316: EXCEPTION
7317: WHEN Others THEN
7318: p_error_message :='SQL-ERRM :'||SQLERRM;
7316: EXCEPTION
7317: WHEN Others THEN
7318: p_error_message :='SQL-ERRM :'||SQLERRM;
7319: p_data_element_value := 'N';
7320: Hr_Utility.set_location('..'||p_error_message,85);
7321: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
7322: l_return_value:=1;
7323: RETURN l_return_value;
7324: END Record070_Display_Criteria;
7317: WHEN Others THEN
7318: p_error_message :='SQL-ERRM :'||SQLERRM;
7319: p_data_element_value := 'N';
7320: Hr_Utility.set_location('..'||p_error_message,85);
7321: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
7322: l_return_value:=1;
7323: RETURN l_return_value;
7324: END Record070_Display_Criteria;
7325: --============================================================================
7351: l_mon NUMBER:=0;
7352: l_year NUMBER:=0;
7353: l_prev_mon NUMBER:=0;
7354: Begin
7355: Hr_Utility.set_location('Entering : '||l_proc_name, 10);
7356: --If yearly report hide this record
7357: IF NVL(g_extract_params (p_business_group_id).extract_type ,'M') = 'Y' THEN
7358: p_data_element_value := 'N';
7359: RETURN 0;
7360: ELSE
7361: p_data_element_value:=g_080_display_flag;
7362: l_return_value:=0;
7363: END IF;
7364: Hr_Utility.set_location('Leaving: '||l_proc_name, 65);
7365: RETURN l_return_value;
7366: EXCEPTION
7367: WHEN Others THEN
7368: p_error_message :='SQL-ERRM :'||SQLERRM;
7366: EXCEPTION
7367: WHEN Others THEN
7368: p_error_message :='SQL-ERRM :'||SQLERRM;
7369: p_data_element_value := 'N';
7370: Hr_Utility.set_location('..'||p_error_message,85);
7371: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
7372: l_return_value:=1;
7373: RETURN l_return_value;
7374: END Record080_Display_Criteria;
7367: WHEN Others THEN
7368: p_error_message :='SQL-ERRM :'||SQLERRM;
7369: p_data_element_value := 'N';
7370: Hr_Utility.set_location('..'||p_error_message,85);
7371: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
7372: l_return_value:=1;
7373: RETURN l_return_value;
7374: END Record080_Display_Criteria;
7375: --============================================================================
7426: l_pay_end_date DATE;
7427: l_chk varchar2(1);
7428:
7429: Begin
7430: Hr_Utility.set_location('Entering: '||l_proc_name, 10);
7431: IF NVL(g_extract_params (p_business_group_id).extract_type ,'N') = 'M' THEN
7432: p_data_element_value := 'N';
7433: RETURN 0;
7434: ELSE
7449: p_data_element_value := 'Y';
7450: ELSE
7451: CLOSE csr_chk_active_asg_period;
7452: p_data_element_value := 'N';
7453: Hr_Utility.set_location('Leaving: '||l_proc_name, 35);
7454: RETURN 0;
7455: END IF;
7456: END IF;
7457:
7487:
7488:
7489: l_return_value:=0;
7490: END IF;
7491: Hr_Utility.set_location('Leaving: '||l_proc_name, 65);
7492: RETURN l_return_value;
7493: EXCEPTION
7494: WHEN Others THEN
7495: p_error_message :='SQL-ERRM :'||SQLERRM;
7493: EXCEPTION
7494: WHEN Others THEN
7495: p_error_message :='SQL-ERRM :'||SQLERRM;
7496: p_data_element_value := 'N';
7497: Hr_Utility.set_location('..'||p_error_message,85);
7498: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
7499: l_return_value:=1;
7500: RETURN l_return_value;
7501: END Record081_Display_Criteria;
7494: WHEN Others THEN
7495: p_error_message :='SQL-ERRM :'||SQLERRM;
7496: p_data_element_value := 'N';
7497: Hr_Utility.set_location('..'||p_error_message,85);
7498: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
7499: l_return_value:=1;
7500: RETURN l_return_value;
7501: END Record081_Display_Criteria;
7502:
7516: l_return_value Number :=0;
7517: l_data_element_value Varchar2(2):='Y';
7518:
7519: BEGIN
7520: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
7521: Hr_Utility.set_location('..p_record_num : '||p_record_num , 6);
7522: IF p_record_num = '010' THEN
7523: l_return_value := Record010_Display_Criteria
7524: (p_assignment_id => p_assignment_id
7517: l_data_element_value Varchar2(2):='Y';
7518:
7519: BEGIN
7520: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
7521: Hr_Utility.set_location('..p_record_num : '||p_record_num , 6);
7522: IF p_record_num = '010' THEN
7523: l_return_value := Record010_Display_Criteria
7524: (p_assignment_id => p_assignment_id
7525: ,p_business_group_id => p_business_group_id
7587: l_data_element_value := 'Y';
7588: ELSE
7589: l_data_element_value := 'N';
7590: END IF;
7591: Hr_Utility.set_location('..l_data_element_value: '||l_data_element_value,45);
7592: Hr_Utility.set_location('Leaving: '||l_proc_name, 50);
7593: IF(l_return_value <> 0) THEN
7594: l_data_element_value:='E';
7595: END IF;
7588: ELSE
7589: l_data_element_value := 'N';
7590: END IF;
7591: Hr_Utility.set_location('..l_data_element_value: '||l_data_element_value,45);
7592: Hr_Utility.set_location('Leaving: '||l_proc_name, 50);
7593: IF(l_return_value <> 0) THEN
7594: l_data_element_value:='E';
7595: END IF;
7596:
7598:
7599: EXCEPTION
7600: WHEN Others THEN
7601: p_error_message :='SQL-ERRM :'||SQLERRM;
7602: Hr_Utility.set_location('..'||p_error_message,85);
7603: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
7604: l_data_element_value:='E';
7605: RETURN l_data_element_value;
7606:
7599: EXCEPTION
7600: WHEN Others THEN
7601: p_error_message :='SQL-ERRM :'||SQLERRM;
7602: Hr_Utility.set_location('..'||p_error_message,85);
7603: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
7604: l_data_element_value:='E';
7605: RETURN l_data_element_value;
7606:
7607: END Chk_If_Req_To_Extract;
7702:
7703: l_proc_name Varchar2(150) := g_proc_name ||'.Get_Trailer_Record_Count';
7704: l_record_count Number := 0;
7705: BEGIN
7706: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
7707: OPEN csr_get_record_count(p_rcd_id,p_org_id);
7708: FETCH csr_get_record_count INTO l_record_count;
7709: CLOSE csr_get_record_count;
7710: Hr_Utility.set_location('Leaving: '||l_proc_name, 45);
7706: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
7707: OPEN csr_get_record_count(p_rcd_id,p_org_id);
7708: FETCH csr_get_record_count INTO l_record_count;
7709: CLOSE csr_get_record_count;
7710: Hr_Utility.set_location('Leaving: '||l_proc_name, 45);
7711: RETURN l_record_count;
7712: EXCEPTION
7713: WHEN Others THEN
7714: Hr_Utility.set_location('Exception Others Raised at Get_Trailer_Record_Count',40);
7710: Hr_Utility.set_location('Leaving: '||l_proc_name, 45);
7711: RETURN l_record_count;
7712: EXCEPTION
7713: WHEN Others THEN
7714: Hr_Utility.set_location('Exception Others Raised at Get_Trailer_Record_Count',40);
7715: Hr_Utility.set_location('Leaving: '||l_proc_name, 45);
7716: RETURN -1;
7717: END Get_Trailer_Record_Count;
7718:
7711: RETURN l_record_count;
7712: EXCEPTION
7713: WHEN Others THEN
7714: Hr_Utility.set_location('Exception Others Raised at Get_Trailer_Record_Count',40);
7715: Hr_Utility.set_location('Leaving: '||l_proc_name, 45);
7716: RETURN -1;
7717: END Get_Trailer_Record_Count;
7718:
7719: -- ================================================================================
7822: l_999_inserted NUMBER := 0;
7823: l_000_inserted NUMBER := 0;
7824:
7825: BEGIN
7826: Hr_Utility.set_location('Entering :---------'||l_proc_name, 5);
7827:
7828: -- Delete all the hidden Records
7829: FOR csr_rcd_rec IN csr_ext_rcd_id
7830: (c_hide_flag => 'Y' -- N=No Y=Yes
7865: --the sort value
7866: FOR val IN csr_get_rslt1
7867: (c_ext_rslt_id => Ben_Ext_Thread.g_ext_rslt_id )
7868: LOOP
7869: hr_utility.set_location('val 32 : '||val.val_32,10);
7870: IF g_org_list.EXISTS(val.val_31) THEN
7871: IF NOT g_ord_details1.EXISTS(val.val_31) THEN
7872: hr_utility.set_location('l_org_index : '||l_org_index,20);
7873: hr_utility.set_location('org : '||val.val_31,30);
7868: LOOP
7869: hr_utility.set_location('val 32 : '||val.val_32,10);
7870: IF g_org_list.EXISTS(val.val_31) THEN
7871: IF NOT g_ord_details1.EXISTS(val.val_31) THEN
7872: hr_utility.set_location('l_org_index : '||l_org_index,20);
7873: hr_utility.set_location('org : '||val.val_31,30);
7874: g_ord_details(l_org_index).gre_org_id := val.val_31;
7875: g_ord_details1(to_number(val.val_31)).gre_org_id := val.val_31;
7876: l_org_index := l_org_index + 1;
7869: hr_utility.set_location('val 32 : '||val.val_32,10);
7870: IF g_org_list.EXISTS(val.val_31) THEN
7871: IF NOT g_ord_details1.EXISTS(val.val_31) THEN
7872: hr_utility.set_location('l_org_index : '||l_org_index,20);
7873: hr_utility.set_location('org : '||val.val_31,30);
7874: g_ord_details(l_org_index).gre_org_id := val.val_31;
7875: g_ord_details1(to_number(val.val_31)).gre_org_id := val.val_31;
7876: l_org_index := l_org_index + 1;
7877: END IF;
7964: -- loop through all orgids
7965: FOR num IN 1..l_org_count
7966: LOOP
7967: l_count := 0;
7968: Hr_Utility.set_location('Current Org Id:---------'||g_ord_details(num).gre_org_id, 5);
7969: IF num <= l_org_count THEN
7970:
7971: -- Get all records for orgid
7972: FOR val IN csr_get_rslt
8118: AND dtl.ext_rslt_dtl_id = l_ext_rslt_dtl_id
8119: AND business_group_id = p_business_group_id;
8120:
8121: END LOOP;
8122: -- Hr_Utility.trace_off;
8123: Hr_Utility.set_location('Leaving :'||l_proc_name, 25);
8124: RETURN l_return_value;
8125:
8126: EXCEPTION
8119: AND business_group_id = p_business_group_id;
8120:
8121: END LOOP;
8122: -- Hr_Utility.trace_off;
8123: Hr_Utility.set_location('Leaving :'||l_proc_name, 25);
8124: RETURN l_return_value;
8125:
8126: EXCEPTION
8127: WHEN Others THEN
8124: RETURN l_return_value;
8125:
8126: EXCEPTION
8127: WHEN Others THEN
8128: Hr_Utility.set_location('..Exception when others raised..', 20);
8129: Hr_Utility.set_location('Leaving :'||l_proc_name, 25);
8130: RETURN -1;
8131: END Sort_Post_Process;
8132:
8125:
8126: EXCEPTION
8127: WHEN Others THEN
8128: Hr_Utility.set_location('..Exception when others raised..', 20);
8129: Hr_Utility.set_location('Leaving :'||l_proc_name, 25);
8130: RETURN -1;
8131: END Sort_Post_Process;
8132:
8133: END Pqp_Nl_PGGM_Pension_Extracts ;