[Home] [Help]
454: WHERE business_group_id = c_business_group_id;
455: l_proc_name CONSTANT VARCHAR2(150) := g_proc_name ||'Get_Leg_Code';
456:
457: BEGIN
458: hr_utility.set_location('Entering: '||l_proc_name, 5);
459:
460: OPEN csr_bg_code (p_business_group_id);
461: FETCH csr_bg_code INTO g_legislation_code;
462: CLOSE csr_bg_code;
460: OPEN csr_bg_code (p_business_group_id);
461: FETCH csr_bg_code INTO g_legislation_code;
462: CLOSE csr_bg_code;
463:
464: hr_utility.set_location('Leaving: '||l_proc_name, 80);
465: RETURN g_legislation_code;
466:
467: END Get_Leg_Code;
468: -- =============================================================================
487: l_proc_name CONSTANT VARCHAR2(150) := g_proc_name ||'Chk_IfAsg_Valid';
488: BEGIN
489: i := p_business_group_id;
490: l_return_value := 'N';
491: hr_utility.set_location('Entering: '||l_proc_name, 5);
492: --
493: -- Check the person id is eligible for passed orgid ,persontype id,
494: -- GRE Org Id and location id.
495: --
504: ,p_location_id => g_extract_params(i).location_id
505: ,p_effective_date => least(g_extract_params(i).extract_end_date,
506: p_effective_date)
507: );
508: hr_utility.set_location(' l_valid_person_chk: '||l_valid_person_chk, 6);
509: --
510: -- Now check for each assig action within the date-range
511: --
512: << Asg_Action >>
522: -- If not valid then exit, i.e. person should not be extracted
523: IF l_valid_person_chk = 'N' THEN
524: EXIT Asg_Action;
525: END IF;
526: --hr_utility.set_location(' assignment_action_id: '||act_rec.assignment_action_id, 6);
527: -- Check for each assignment action within the date range if the element(s)
528: -- have a run result in any of the assignment actions in the date-range.
529: l_ele_type_id := g_element.first;
530: WHILE l_ele_type_id IS NOT NULL
531: LOOP
532: OPEN csr_ele_run (c_asg_action_id => act_rec.assignment_action_id
533: ,c_element_type_id => l_ele_type_id);
534: FETCH csr_ele_run INTO l_valid_action;
535: --hr_utility.set_location(' l_ele_type_id: '||l_ele_type_id, 6);
536: IF csr_ele_run%FOUND THEN
537: CLOSE csr_ele_run;
538: l_return_value := 'Y';
539: --
550: END LOOP; -- WHILE l_ele_type_id
551:
552: END LOOP Asg_Action;
553:
554: hr_utility.set_location('..l_return_value: '||l_return_value, 80);
555: hr_utility.set_location('Leaving: '||l_proc_name, 80);
556: RETURN l_return_value;
557: EXCEPTION
558: WHEN OTHERS THEN
551:
552: END LOOP Asg_Action;
553:
554: hr_utility.set_location('..l_return_value: '||l_return_value, 80);
555: hr_utility.set_location('Leaving: '||l_proc_name, 80);
556: RETURN l_return_value;
557: EXCEPTION
558: WHEN OTHERS THEN
559: hr_utility.set_location('SQLERRM: '||sqlerrm, 90);
555: hr_utility.set_location('Leaving: '||l_proc_name, 80);
556: RETURN l_return_value;
557: EXCEPTION
558: WHEN OTHERS THEN
559: hr_utility.set_location('SQLERRM: '||sqlerrm, 90);
560: hr_utility.set_location('Leaving: '||l_proc_name, 90);
561: RETURN l_return_value;
562:
563: END Chk_IfAsg_Valid;
556: RETURN l_return_value;
557: EXCEPTION
558: WHEN OTHERS THEN
559: hr_utility.set_location('SQLERRM: '||sqlerrm, 90);
560: hr_utility.set_location('Leaving: '||l_proc_name, 90);
561: RETURN l_return_value;
562:
563: END Chk_IfAsg_Valid;
564:
580: l_proc_name CONSTANT VARCHAR2(150) := g_proc_name ||'Chk_IfSecAsg_Valid';
581:
582: BEGIN
583:
584: hr_utility.set_location('Entering: '||l_proc_name, 5);
585: l_return_value := 'N';
586: IF g_processing_addl_asgs THEN
587: RETURN l_return_value;
588: END IF;
602: --
603: l_effective_date := least(g_extract_params(i).extract_end_date,
604: l_sec_assg_rec.effective_end_date);
605: IF g_debug THEN
606: hr_utility.set_location('..Checking for assignment : '||
607: l_sec_assg_rec.assignment_id, 7);
608: hr_utility.set_location('..p_effective_date : '||
609: l_effective_date, 7);
610: END IF;
604: l_sec_assg_rec.effective_end_date);
605: IF g_debug THEN
606: hr_utility.set_location('..Checking for assignment : '||
607: l_sec_assg_rec.assignment_id, 7);
608: hr_utility.set_location('..p_effective_date : '||
609: l_effective_date, 7);
610: END IF;
611: -- Call the main criteria function for this assignment to check if its a valid
612: -- assignment that can be reported based on the criteria specified.
620: END IF;
621: EXIT WHEN l_return_value = 'Y';
622:
623: END LOOP;
624: hr_utility.set_location('Leaving: '||l_proc_name, 80);
625: RETURN l_return_value;
626:
627: EXCEPTION
628: WHEN others THEN
625: RETURN l_return_value;
626:
627: EXCEPTION
628: WHEN others THEN
629: hr_utility.set_location('Leaving: '||l_proc_name, 90);
630: RETURN l_return_value;
631:
632: END Chk_IfSecAsg_Valid;
633:
668: l_proc_name VARCHAR2(150) := g_proc_name ||'Pension_Extract_Process';
669:
670: BEGIN
671:
672: g_debug := hr_utility.debug_enabled;
673:
674: hr_utility.set_location('Entering: '||l_proc_name, 5);
675: g_conc_request_id := fnd_global.conc_request_id;
676: SELECT USERENV('SESSIONID') INTO l_session_id FROM dual;
670: BEGIN
671:
672: g_debug := hr_utility.debug_enabled;
673:
674: hr_utility.set_location('Entering: '||l_proc_name, 5);
675: g_conc_request_id := fnd_global.conc_request_id;
676: SELECT USERENV('SESSIONID') INTO l_session_id FROM dual;
677: DELETE FROM pay_us_rpt_totals
678: WHERE organization_name = 'Global Pension Extracts'
731: --
732: -- Call the actual benefit extract process with the effective date as the
733: -- extract end date along with the ext def. id and business group id.
734: --
735: hr_utility.set_location('..Calling Benefit Ext Process'||l_proc_name, 6);
736: ben_ext_thread.process
737: (errbuf => l_errbuff,
738: retcode => l_retcode,
739: p_benefit_action_id => NULL,
748: AND business_group_id = p_business_group_id
749: AND organization_id = p_business_group_id
750: AND location_id = p_ext_dfn_id;
751:
752: hr_utility.set_location('Leaving: '||l_proc_name, 80);
753:
754: EXCEPTION
755: WHEN Others THEN
756: hr_utility.set_location('Leaving: '||l_proc_name, 90);
752: hr_utility.set_location('Leaving: '||l_proc_name, 80);
753:
754: EXCEPTION
755: WHEN Others THEN
756: hr_utility.set_location('Leaving: '||l_proc_name, 90);
757: UPDATE pay_us_rpt_totals
758: SET attribute30 = 'EXTRACT_COMPLETED'
759: WHERE organization_name = 'Global Pension Extracts'
760: AND tax_unit_id = g_conc_request_id
805: l_proc_name VARCHAR2(150) := g_proc_name ||'Get_Element_Info';
806: l_ele_info_rec csr_ele_info%ROWTYPE;
807:
808: BEGIN
809: hr_utility.set_location('Entering: '||l_proc_name, 5);
810: l_legislation_code := g_extract_params(p_business_group_id).legislation_code;
811: -- Get the element information details based on the element_type_id,
812: -- effective_date and bus. group id or Leg. Code.
813: OPEN csr_ele_info (c_element_type_id => p_element_type_id
814: ,c_effective_date => p_effective_date
815: ,c_business_group_id => p_business_group_id
816: ,c_legislation_code => l_legislation_code);
817: FETCH csr_ele_info INTO l_ele_info_rec;
818: hr_utility.set_location('p_element_type_id: '||p_element_type_id, 5);
819: IF csr_ele_info%FOUND THEN
820: CLOSE csr_ele_info;
821: -- =======================================================================
822: -- ~ Get Info for the Element Type Id Passed
841: CLOSE csr_inv;
842:
843: ELSE
844: -- Cursor failed to get any matching record for the ele type id passed.
845: hr_utility.set_location('..Failed to get details for element_type_id : '
846: ||p_element_type_id, 60);
847: CLOSE csr_ele_info;
848: END IF;-- If csr_ele_info%FOUND
849:
846: ||p_element_type_id, 60);
847: CLOSE csr_ele_info;
848: END IF;-- If csr_ele_info%FOUND
849:
850: hr_utility.set_location('Leaving: '||l_proc_name, 80);
851:
852: END Get_Element_Info;
853: -- ================================================================================
854: -- ~ Update_Record_Values :
879: l_proc_name VARCHAR2(150):= g_proc_name||'Update_Record_Values';
880: l_ext_dtl_rec_nc ben_ext_rslt_dtl%ROWTYPE;
881:
882: BEGIN
883: hr_utility.set_location('Entering :'||l_proc_name, 5);
884: l_ext_dtl_rec_nc := p_ext_dtl_rec;
885:
886: IF p_data_ele_seqnum IS NULL THEN
887: OPEN csr_seqnum ( c_ext_rcd_id => p_ext_rcd_id
1050: p_ext_dtl_rec.val_74 := p_data_element_value;
1051: ELSIF l_seqnum_rec.seq_num = 75 THEN
1052: p_ext_dtl_rec.val_75 := p_data_element_value;
1053: END IF;
1054: hr_utility.set_location('Leaving :'||l_proc_name, 25);
1055: RETURN;
1056: EXCEPTION
1057: WHEN Others THEN
1058: p_ext_dtl_rec := l_ext_dtl_rec_nc;
1072: l_proc_name VARCHAR2(150) := g_proc_name||'Ins_Rslt_Dtl';
1073: l_dtl_rec_nc ben_ext_rslt_dtl%ROWTYPE;
1074:
1075: BEGIN
1076: Hr_Utility.set_location('Entering :'||l_proc_name, 5);
1077: l_dtl_rec_nc := p_dtl_rec;
1078:
1079: -- Get the next sequence NUMBER to insert a record into the table
1080: SELECT ben_ext_rslt_dtl_s.NEXTVAL INTO p_dtl_rec.ext_rslt_dtl_id FROM dual;
1271: ,p_dtl_rec.thrd_sort_val
1272: ,p_dtl_rec.trans_seq_num
1273: ,p_dtl_rec.rcrd_seq_num
1274: );
1275: Hr_Utility.set_location('Leaving :'||l_proc_name, 25);
1276: RETURN;
1277:
1278: EXCEPTION
1279: WHEN Others THEN
1276: RETURN;
1277:
1278: EXCEPTION
1279: WHEN Others THEN
1280: Hr_Utility.set_location('Leaving :'||l_proc_name, 25);
1281: p_dtl_rec := l_dtl_rec_nc;
1282: RAISE;
1283: END Ins_Rslt_Dtl;
1284:
1392: l_err_no NUMBER := p_err_no ;
1393:
1394: BEGIN
1395:
1396: Hr_Utility.set_location('Entering'||l_proc, 5);
1397: IF p_err_no IS NULL THEN
1398: -- Assumed the name is Error Name
1399: l_err_no := To_Number(Substr(p_err_name,5,5)) ;
1400: l_err_name := NULL ;
1415: p_business_group_id => g_business_group_id,
1416: p_ext_rslt_id => Ben_Extract.g_ext_rslt_id);
1417: END IF;
1418: --
1419: Hr_Utility.set_location('Exiting'||l_proc, 15);
1420: --
1421: --
1422: END Write_Warning;
1423: -- =============================================================================
1439: AND typ_cd <> 'W';
1440: --
1441: BEGIN
1442: --
1443: Hr_Utility.set_location('Entering'||l_proc, 5);
1444: IF p_err_no IS NULL THEN
1445: -- Assumed the name is Error Name
1446: l_err_no := To_Number(Substr(p_err_name,5,5)) ;
1447: l_err_name := NULL ;
1474: p_business_group_id => g_business_group_id,
1475: p_ext_rslt_id => Ben_Extract.g_ext_rslt_id);
1476: END IF;
1477: --
1478: Hr_Utility.set_location('Exiting'||l_proc, 15);
1479: --
1480: --
1481: END Write_Error;
1482:
1524: --
1525: --
1526: BEGIN
1527: --
1528: Hr_Utility.set_location('Entering'||l_proc, 5);
1529: --
1530: p_exclude_this_rcd_flag := FALSE;
1531: p_raise_warning := FALSE;
1532: p_rollback_person := FALSE;
1663: END IF; -- IF p_sprs_cd = 'D'
1664: --
1665: END IF; -- IF l_condition = 'T'
1666: --
1667: Hr_Utility.set_location('Exiting'||l_proc, 15);
1668: --
1669: END Rcd_In_File;
1670:
1671: -- =============================================================================
1707: l_dynamic_condition VARCHAR2(9999);
1708: --
1709: l_val_tab_mirror ValTabTyp;
1710: BEGIN
1711: Hr_Utility.set_location('Entering'||l_proc, 5);
1712: p_exclude_this_rcd_flag := FALSE;
1713: p_raise_warning := FALSE;
1714: p_rollback_person := FALSE;
1715: -- Make mirror image of table for evaluation, since values in
1814: END IF;-- IF l_cnt > 0 THEN
1815: --
1816: END LOOP; -- FOR xer IN c_xer
1817: --
1818: Hr_Utility.set_location('Exiting'||l_proc, 15);
1819: --
1820: END Data_Elmt_In_Rcd;
1821:
1822: -- =============================================================================
1827: ,p_val_tab IN OUT NOCOPY ValTabTyp) IS
1828:
1829: l_proc_name VARCHAR2(150) := g_proc_name ||'Copy_Rec_Values ';
1830: BEGIN
1831: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
1832:
1833: p_val_tab(1) := p_rslt_rec.val_01;
1834: p_val_tab(2) := p_rslt_rec.val_02;
1835: p_val_tab(3) := p_rslt_rec.val_03;
1911: p_val_tab(72) := p_rslt_rec.val_72;
1912: p_val_tab(73) := p_rslt_rec.val_73;
1913: p_val_tab(74) := p_rslt_rec.val_74;
1914: p_val_tab(75) := p_rslt_rec.val_75;
1915: Hr_Utility.set_location('Leaving: '||l_proc_name, 15);
1916:
1917: END Copy_Rec_Values;
1918:
1919: -- =============================================================================
1944: l_conc_reqest_id NUMBER(20);
1945: l_exists VARCHAR2(2);
1946: l_proc_name CONSTANT VARCHAR2(150) := g_proc_name ||'Exclude_Person';
1947: BEGIN
1948: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
1949: OPEN csr_ben_per (c_person_id => p_person_id
1950: ,c_benefit_action_id => p_benefit_action_id);
1951: FETCH csr_ben_per INTO l_ben_per;
1952: CLOSE csr_ben_per;
1966: AND l_ben_per.person_action_id
1967: BETWEEN bbr.starting_person_action_id
1968: AND bbr.ending_person_action_id;
1969: END IF;
1970: Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
1971:
1972: END Exclude_Person;
1973: -- =============================================================================
1974: -- Process_Ext_Rslt_Dtl_Rec:
2046: l_rollback_person BOOLEAN;
2047: l_rslt_dtl_id NUMBER;
2048: --
2049: BEGIN
2050: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
2051: FOR i IN 1..75
2052: LOOP
2053: l_val_tab(i) := NULL;
2054: END LOOP;
2079: BEGIN
2080: IF i.frmt_mask_lookup_cd IS NOT NULL AND
2081: l_ff_value IS NOT NULL THEN
2082: IF Substr(i.frmt_mask_lookup_cd,1,1) = 'N' THEN
2083: Hr_Utility.set_location('..Applying NUMBER format mask :ben_ext_fmt.apply_format_mask',50);
2084: l_ff_value_fmt := Ben_Ext_Fmt.apply_format_mask(To_Number(l_ff_value), i.frmt_mask_cd);
2085: l_ff_value := l_ff_value_fmt;
2086: ELSIF Substr(i.frmt_mask_lookup_cd,1,1) = 'D' THEN
2087: Hr_Utility.set_location('..Applying Date format mask :ben_ext_fmt.apply_format_mask',55);
2083: Hr_Utility.set_location('..Applying NUMBER format mask :ben_ext_fmt.apply_format_mask',50);
2084: l_ff_value_fmt := Ben_Ext_Fmt.apply_format_mask(To_Number(l_ff_value), i.frmt_mask_cd);
2085: l_ff_value := l_ff_value_fmt;
2086: ELSIF Substr(i.frmt_mask_lookup_cd,1,1) = 'D' THEN
2087: Hr_Utility.set_location('..Applying Date format mask :ben_ext_fmt.apply_format_mask',55);
2088: l_ff_value_fmt := Ben_Ext_Fmt.apply_format_mask(Fnd_Date.canonical_to_date(l_ff_value),
2089: i.frmt_mask_cd);
2090: l_ff_value := l_ff_value_fmt;
2091: END IF;
2102: l_ff_value := Substr(l_ff_value, -l_max_len);
2103: ELSE -- everything else truncs from the right.
2104: l_ff_value := Substr(l_ff_value, 1, i.max_length_num);
2105: END IF;
2106: Hr_Utility.set_location('..After Max Length : '|| l_ff_value,56 );
2107: END IF;
2108: -- If the data element is required, and null then exit
2109: -- no need to re-execute the other data-elements in the record.
2110: IF i.rqd_flag = 'Y' AND (l_ff_value IS NULL) THEN
2188: ,p_flag_thread => 'N'
2189: );
2190: END IF;
2191:
2192: Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
2193:
2194: EXCEPTION
2195: WHEN Others THEN
2196: p_error_message :='SQL-ERRM :'||SQLERRM;
2193:
2194: EXCEPTION
2195: WHEN Others THEN
2196: p_error_message :='SQL-ERRM :'||SQLERRM;
2197: Hr_Utility.set_location('..'||p_error_message,85);
2198: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
2199:
2200: END Process_Ext_Rslt_Dtl_Rec;
2201:
2194: EXCEPTION
2195: WHEN Others THEN
2196: p_error_message :='SQL-ERRM :'||SQLERRM;
2197: Hr_Utility.set_location('..'||p_error_message,85);
2198: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
2199:
2200: END Process_Ext_Rslt_Dtl_Rec;
2201:
2202: -- =============================================================================
2216: l_main_rec csr_rslt_dtl%ROWTYPE;
2217: l_new_rec csr_rslt_dtl%ROWTYPE;
2218: l_effective_date DATE;
2219: BEGIN
2220: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
2221: FOR csr_rcd_rec IN csr_ext_rcd_id(c_hide_flag => 'N' -- N=No Y=Yes
2222: ,c_rcd_type_cd => 'D')-- D=Detail, T=Total, H-Header
2223: LOOP
2224: g_ext_dtl_rcd_id := csr_rcd_rec.ext_rcd_id;
2249: ,p_error_message => p_error_message
2250: );
2251: END LOOP;
2252:
2253: Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
2254:
2255: EXCEPTION
2256:
2257: WHEN Others THEN
2255: EXCEPTION
2256:
2257: WHEN Others THEN
2258: p_error_message :='SQL-ERRM :'||SQLERRM;
2259: Hr_Utility.set_location('..'||p_error_message,85);
2260: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
2261:
2262: END Create_AsgAction_Lines;
2263:
2256:
2257: WHEN Others THEN
2258: p_error_message :='SQL-ERRM :'||SQLERRM;
2259: Hr_Utility.set_location('..'||p_error_message,85);
2260: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
2261:
2262: END Create_AsgAction_Lines;
2263:
2264: -- =============================================================================
2275: l_prev_ele_type_id pay_element_types_f.element_type_id%TYPE;
2276: l_CatchUp_ele_type_id pay_element_types_f.element_type_id%TYPE;
2277: l_AT_ele_type_id pay_element_types_f.element_type_id%TYPE;
2278: BEGIN
2279: hr_utility.set_location('Entering: '||l_proc_name, 5);
2280: -- If element set was selected
2281: FOR ele_rec IN csr_ele_id (c_element_set_id => p_element_set_id)
2282: LOOP
2283: Get_Element_Info
2291: (p_element_type_id => p_element_type_id
2292: ,p_effective_date => p_effective_date
2293: ,p_business_group_id => p_business_group_id);
2294: END IF;
2295: hr_utility.set_location('Leaving: '||l_proc_name, 80);
2296: END Get_Element_Details;
2297: -- =============================================================================
2298: -- Process_Assignments: This procedure creates a row in ben_ext_rslt_dtl table
2299: -- for each valid assig action when the reporting dimension is ASG_RUN.
2313: l_proc_name CONSTANT VARCHAR2(150) := g_proc_name ||'Process_Assignments';
2314: l_valid_person_chk VARCHAR2(2);
2315:
2316: BEGIN
2317: hr_utility.set_location('Entering: '||l_proc_name, 5);
2318:
2319: i := p_business_group_id;
2320: IF g_extract_params(i).reporting_dimension = 'ASG_RUN' THEN
2321: -- Reporting Dimension is ASG_RUN
2378:
2379: ELSE
2380: -- For all other reporting dimensions we on have one extract record for
2381: -- an assignment.
2382: hr_utility.set_location(' Summary Reporting Dimension', 5);
2383: g_gre_org_id := NULL;
2384: g_action_effective_date := NULL;
2385:
2386: g_primary_assig(p_assignment_id).Calculate_Amount := 'YES';
2403: g_gre_org_id <> act_rec.tax_unit_id) THEN
2404:
2405: g_gre_org_id := act_rec.tax_unit_id;
2406: g_action_effective_date := act_rec.effective_date;
2407: hr_utility.set_location(' g_gre_org_id: '||g_gre_org_id, 5);
2408: Create_AsgAction_Lines
2409: (p_assignment_id => p_assignment_id
2410: ,p_business_group_id => p_business_group_id
2411: ,p_person_id => g_primary_assig(p_assignment_id).person_id
2426: );
2427: END IF;
2428: END IF; -- If reporting_dimension = 'ASG_RUN'
2429:
2430: hr_utility.set_location('Leaving: '||l_proc_name, 80);
2431:
2432: END Process_Assignments;
2433:
2434: -- =============================================================================
2459: l_person_id per_all_people_f.person_id%TYPE;
2460: l_assignment_id per_all_assignments_f.assignment_id%TYPE;
2461: BEGIN
2462:
2463: hr_utility.set_location('Entering: '||l_proc_name, 5);
2464: i := p_business_group_id;
2465:
2466: IF NOT g_primary_assig.EXISTS(p_assignment_id) THEN
2467: l_return_value := 'NOTFOUND';
2464: i := p_business_group_id;
2465:
2466: IF NOT g_primary_assig.EXISTS(p_assignment_id) THEN
2467: l_return_value := 'NOTFOUND';
2468: hr_utility.set_location('..Not a Valid assignment: '||p_assignment_id, 6);
2469: RETURN l_return_value;
2470:
2471: ELSIF g_primary_assig(p_assignment_id).assignment_type IN ('B','E') THEN
2472:
2474: l_asg_type := g_primary_assig(p_assignment_id).assignment_type;
2475: g_processing_addl_asgs:= TRUE;
2476:
2477: IF g_debug THEN
2478: hr_utility.set_location('..l_asg_type : '||l_asg_type, 6);
2479: hr_utility.set_location('..p_assignment_id : '||p_assignment_id, 6);
2480: hr_utility.set_location('..l_person_id : '||l_person_id, 6);
2481: END IF;
2482: -- Check if there are any other assignments which might be active within the
2475: g_processing_addl_asgs:= TRUE;
2476:
2477: IF g_debug THEN
2478: hr_utility.set_location('..l_asg_type : '||l_asg_type, 6);
2479: hr_utility.set_location('..p_assignment_id : '||p_assignment_id, 6);
2480: hr_utility.set_location('..l_person_id : '||l_person_id, 6);
2481: END IF;
2482: -- Check if there are any other assignments which might be active within the
2483: -- specified extract date range.
2476:
2477: IF g_debug THEN
2478: hr_utility.set_location('..l_asg_type : '||l_asg_type, 6);
2479: hr_utility.set_location('..p_assignment_id : '||p_assignment_id, 6);
2480: hr_utility.set_location('..l_person_id : '||l_person_id, 6);
2481: END IF;
2482: -- Check if there are any other assignments which might be active within the
2483: -- specified extract date range.
2484: FOR sec_asg_rec IN
2492: l_sec_assg_rec := sec_asg_rec;
2493: l_criteria_value := 'N';
2494: l_effective_date := least(g_extract_params(i).extract_end_date,
2495: l_sec_assg_rec.effective_end_date);
2496: hr_utility.set_location('..Checking for assignment : '||l_sec_assg_rec.assignment_id, 7);
2497: hr_utility.set_location('..p_effective_date : '||l_effective_date, 7);
2498: -- Call the main criteria function for this assignment to check if its a valid
2499: -- assignment that can be reported based on the criteria specified.
2500: l_criteria_value := Pension_Criteria_Full_Profile
2493: l_criteria_value := 'N';
2494: l_effective_date := least(g_extract_params(i).extract_end_date,
2495: l_sec_assg_rec.effective_end_date);
2496: hr_utility.set_location('..Checking for assignment : '||l_sec_assg_rec.assignment_id, 7);
2497: hr_utility.set_location('..p_effective_date : '||l_effective_date, 7);
2498: -- Call the main criteria function for this assignment to check if its a valid
2499: -- assignment that can be reported based on the criteria specified.
2500: l_criteria_value := Pension_Criteria_Full_Profile
2501: (p_assignment_id => l_sec_assg_rec.assignment_id
2506: );
2507: END LOOP;
2508: g_processing_addl_asgs:= FALSE;
2509: END IF;
2510: hr_utility.set_location('..Assignment Count : '||g_primary_assig.COUNT, 7);
2511: hr_utility.set_location('..l_person_id : '||l_person_id, 7);
2512:
2513: -- For each assignment for this person id check if additional rows need to be
2514: -- created and re-calculate the person level based fast-formulas.
2507: END LOOP;
2508: g_processing_addl_asgs:= FALSE;
2509: END IF;
2510: hr_utility.set_location('..Assignment Count : '||g_primary_assig.COUNT, 7);
2511: hr_utility.set_location('..l_person_id : '||l_person_id, 7);
2512:
2513: -- For each assignment for this person id check if additional rows need to be
2514: -- created and re-calculate the person level based fast-formulas.
2515: g_total_dtl_lines := 0;
2516: l_assignment_id := g_primary_assig.first;
2517:
2518: WHILE l_assignment_id IS NOT NULL
2519: LOOP
2520: hr_utility.set_location('..Checking for Assignment : '||l_assignment_id, 7);
2521: IF g_primary_assig(l_assignment_id).person_id = l_person_id AND
2522: g_primary_assig(l_assignment_id).Assignment_Type = 'E' THEN
2523:
2524: g_primary_assig(l_assignment_id).Calculate_Amount := 'YES';
2522: g_primary_assig(l_assignment_id).Assignment_Type = 'E' THEN
2523:
2524: g_primary_assig(l_assignment_id).Calculate_Amount := 'YES';
2525: IF g_debug THEN
2526: hr_utility.set_location('..Valid Assignment : '||l_assignment_id, 8);
2527: hr_utility.set_location('..l_no_asg_action : '||l_no_asg_action, 8);
2528: END IF;
2529: Process_Assignments
2530: (p_assignment_id => l_assignment_id
2523:
2524: g_primary_assig(l_assignment_id).Calculate_Amount := 'YES';
2525: IF g_debug THEN
2526: hr_utility.set_location('..Valid Assignment : '||l_assignment_id, 8);
2527: hr_utility.set_location('..l_no_asg_action : '||l_no_asg_action, 8);
2528: END IF;
2529: Process_Assignments
2530: (p_assignment_id => l_assignment_id
2531: ,p_business_group_id => p_business_group_id
2574: g_primary_assig.DELETE(l_assignment_id);
2575: END IF;
2576: l_assignment_id := g_primary_assig.NEXT(l_assignment_id);
2577: END LOOP;
2578: hr_utility.set_location('Leaving: '||l_proc_name, 80);
2579:
2580: l_assignment_id := g_mx_rules.first;
2581: WHILE l_assignment_id IS NOT NULL
2582: LOOP
2589:
2590: EXCEPTION
2591: WHEN Others THEN
2592: p_error_message :='SQL-ERRM :'||SQLERRM;
2593: hr_utility.set_location('..'||p_error_message,85);
2594: hr_utility.set_location('Leaving: '||l_proc_name, 90);
2595: RETURN l_return_value;
2596: END Check_Asg_Actions;
2597:
2590: EXCEPTION
2591: WHEN Others THEN
2592: p_error_message :='SQL-ERRM :'||SQLERRM;
2593: hr_utility.set_location('..'||p_error_message,85);
2594: hr_utility.set_location('Leaving: '||l_proc_name, 90);
2595: RETURN l_return_value;
2596: END Check_Asg_Actions;
2597:
2598: -- =============================================================================
2646: Invaild_kff_flex EXCEPTION;
2647: l_return_value VARCHAR2(150);
2648: l_proc_name VARCHAR2(150) := g_proc_name ||'Get_SIT_Segment';
2649: BEGIN
2650: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
2651: p_error_code := '0';
2652: g_business_group_id := p_business_group_id;
2653: OPEN csr_asg (c_assignment_id => p_assignment_id
2654: ,c_effective_date => p_effective_date);
2747: l_return_value := l_analysis_criteria_rec.segment30;
2748: END IF;
2749:
2750: l_return_value := Nvl(l_return_value,'EXT_NULL_VALUE');
2751: Hr_Utility.set_location('Leaving: '||l_proc_name,80);
2752: RETURN l_return_value;
2753:
2754: EXCEPTION
2755: WHEN Invaild_kff_flex THEN
2755: WHEN Invaild_kff_flex THEN
2756: p_error_code := '-20001';
2757: p_err_message := 'Invalid Key Flex structure code.';
2758: l_return_value := 'EXT_ERR_WARNING';
2759: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
2760: RAISE;
2761: -- RETURN l_return_value;
2762:
2763: WHEN Others THEN
2763: WHEN Others THEN
2764: l_return_value := 'EXT_ERR_WARNING';
2765: p_error_code := '-20001';
2766: p_err_message :='SQL-ERRM :'||SQLERRM;
2767: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
2768: RETURN l_return_value;
2769:
2770: END Get_SIT_Segment;
2771:
2873: +=============================+=========================+============================+
2874: */
2875:
2876: BEGIN
2877: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
2878: -- Get the legisaltion code if null
2879: IF g_legislation_code IS NULL THEN
2880: g_legislation_code := Get_Leg_Code(p_business_group_id);
2881: END IF;
2901: CLOSE csr_asg;
2902:
2903: IF p_flex_name IN('Extra Person Info DDF',
2904: 'PER_PEOPLE_EXTRA_INFO') THEN
2905: Hr_Utility.set_location('..p_flex_name = '||p_flex_name, 6);
2906: OPEN csr_per_mult_occur(c_information_type => p_flex_context);
2907: FETCH csr_per_mult_occur INTO l_mult_occur;
2908: CLOSE csr_per_mult_occur;
2909: IF l_mult_occur <> 'Y' THEN
2906: OPEN csr_per_mult_occur(c_information_type => p_flex_context);
2907: FETCH csr_per_mult_occur INTO l_mult_occur;
2908: CLOSE csr_per_mult_occur;
2909: IF l_mult_occur <> 'Y' THEN
2910: Hr_Utility.set_location('..l_mult_occur = '||l_mult_occur, 6);
2911: OPEN csr_pei (c_person_id => l_asg_rec.person_id
2912: ,c_information_type => p_flex_context);
2913: FETCH csr_pei INTO l_key_val;
2914: CLOSE csr_pei;
2914: CLOSE csr_pei;
2915: l_key_col := 'PERSON_EXTRA_INFO_ID';
2916: l_tab_name := 'PER_PEOPLE_EXTRA_INFO';
2917: ELSE
2918: Hr_Utility.set_location('..l_mult_occur = '||l_mult_occur, 6);
2919: p_error_code := '-20001';
2920: p_err_message := 'Contexts :'||p_flex_context ||
2921: ' can have multiple occurances';
2922: l_return_value := 'EXT_ERR_WARNING';
2924: END IF;
2925:
2926: ELSIF p_flex_name IN('Assignment Developer DF',
2927: 'PER_ASSIGNMENT_EXTRA_INFO') THEN
2928: Hr_Utility.set_location('..p_flex_name = '||p_flex_name, 7);
2929: OPEN csr_asg_mult_occur(c_information_type => p_flex_context);
2930: FETCH csr_asg_mult_occur INTO l_mult_occur;
2931: CLOSE csr_asg_mult_occur;
2932: IF l_mult_occur <> 'Y' THEN
2929: OPEN csr_asg_mult_occur(c_information_type => p_flex_context);
2930: FETCH csr_asg_mult_occur INTO l_mult_occur;
2931: CLOSE csr_asg_mult_occur;
2932: IF l_mult_occur <> 'Y' THEN
2933: Hr_Utility.set_location('..l_mult_occur = '||l_mult_occur, 7);
2934: Hr_Utility.set_location('..assignment_id = '||l_asg_rec.assignment_id, 7);
2935: Hr_Utility.set_location('..p_flex_context = '||p_flex_context, 7);
2936: OPEN csr_aei (c_assignment_id => l_asg_rec.assignment_id
2937: ,c_information_type => p_flex_context);
2930: FETCH csr_asg_mult_occur INTO l_mult_occur;
2931: CLOSE csr_asg_mult_occur;
2932: IF l_mult_occur <> 'Y' THEN
2933: Hr_Utility.set_location('..l_mult_occur = '||l_mult_occur, 7);
2934: Hr_Utility.set_location('..assignment_id = '||l_asg_rec.assignment_id, 7);
2935: Hr_Utility.set_location('..p_flex_context = '||p_flex_context, 7);
2936: OPEN csr_aei (c_assignment_id => l_asg_rec.assignment_id
2937: ,c_information_type => p_flex_context);
2938: FETCH csr_aei INTO l_key_val;
2931: CLOSE csr_asg_mult_occur;
2932: IF l_mult_occur <> 'Y' THEN
2933: Hr_Utility.set_location('..l_mult_occur = '||l_mult_occur, 7);
2934: Hr_Utility.set_location('..assignment_id = '||l_asg_rec.assignment_id, 7);
2935: Hr_Utility.set_location('..p_flex_context = '||p_flex_context, 7);
2936: OPEN csr_aei (c_assignment_id => l_asg_rec.assignment_id
2937: ,c_information_type => p_flex_context);
2938: FETCH csr_aei INTO l_key_val;
2939: CLOSE csr_aei;
2937: ,c_information_type => p_flex_context);
2938: FETCH csr_aei INTO l_key_val;
2939: CLOSE csr_aei;
2940:
2941: Hr_Utility.set_location('..p_flex_name = '||p_flex_name, 7);
2942: Hr_Utility.set_location('..l_key_val = '||l_key_val, 7);
2943: l_key_col := 'ASSIGNMENT_EXTRA_INFO_ID';
2944: l_tab_name := 'PER_ASSIGNMENT_EXTRA_INFO';
2945: ELSE
2938: FETCH csr_aei INTO l_key_val;
2939: CLOSE csr_aei;
2940:
2941: Hr_Utility.set_location('..p_flex_name = '||p_flex_name, 7);
2942: Hr_Utility.set_location('..l_key_val = '||l_key_val, 7);
2943: l_key_col := 'ASSIGNMENT_EXTRA_INFO_ID';
2944: l_tab_name := 'PER_ASSIGNMENT_EXTRA_INFO';
2945: ELSE
2946: p_error_code := '-20001';
2972: CLOSE csr_info_id;
2973:
2974: l_key_col := 'ORG_INFORMATION_ID';
2975: l_tab_name := 'HR_ORGANIZATION_INFORMATION';
2976: Hr_Utility.set_location('..g_gre_org_id = '||g_gre_org_id, 7);
2977: Hr_Utility.set_location('..p_flex_context = '||p_flex_context, 7);
2978: Hr_Utility.set_location('..l_key_val = '||l_key_val, 7);
2979: END IF;
2980:
2973:
2974: l_key_col := 'ORG_INFORMATION_ID';
2975: l_tab_name := 'HR_ORGANIZATION_INFORMATION';
2976: Hr_Utility.set_location('..g_gre_org_id = '||g_gre_org_id, 7);
2977: Hr_Utility.set_location('..p_flex_context = '||p_flex_context, 7);
2978: Hr_Utility.set_location('..l_key_val = '||l_key_val, 7);
2979: END IF;
2980:
2981: END IF;
2974: l_key_col := 'ORG_INFORMATION_ID';
2975: l_tab_name := 'HR_ORGANIZATION_INFORMATION';
2976: Hr_Utility.set_location('..g_gre_org_id = '||g_gre_org_id, 7);
2977: Hr_Utility.set_location('..p_flex_context = '||p_flex_context, 7);
2978: Hr_Utility.set_location('..l_key_val = '||l_key_val, 7);
2979: END IF;
2980:
2981: END IF;
2982:
2983: ELSIF p_flex_name IN('Further Person Information ',
2984: 'Person Developer DF') THEN
2985:
2986: IF p_flex_context <> g_legislation_code THEN
2987: Hr_Utility.set_location('..Invalid p_flex_name = '||p_flex_name, 8);
2988: RAISE Invaild_DDF_or_DF;
2989: END IF;
2990: l_key_val := g_person_id;
2991: l_key_col := 'PERSON_ID';
2990: l_key_val := g_person_id;
2991: l_key_col := 'PERSON_ID';
2992: l_tab_name := 'PER_ALL_PEOPLE_F';
2993: ELSE
2994: Hr_Utility.set_location('..Invalid p_flex_name = '||p_flex_name, 8);
2995: RAISE Invaild_DDF_or_DF;
2996: END IF;
2997: --
2998: -- Now call the generic function to get the segment value for the given
3002: ,'Org Developer DF'
3003: ,'Person Developer DF'
3004: ,'Assignment Developer DF') THEN
3005:
3006: Hr_Utility.set_location('..Calling pqp_utilities.get_ddf_value', 9);
3007: Hr_Utility.set_location('..l_key_val = '||l_key_val, 7);
3008:
3009: l_ddf_seg_value := Pqp_Utilities.get_ddf_value
3010: (p_flex_name => p_flex_name
3003: ,'Person Developer DF'
3004: ,'Assignment Developer DF') THEN
3005:
3006: Hr_Utility.set_location('..Calling pqp_utilities.get_ddf_value', 9);
3007: Hr_Utility.set_location('..l_key_val = '||l_key_val, 7);
3008:
3009: l_ddf_seg_value := Pqp_Utilities.get_ddf_value
3010: (p_flex_name => p_flex_name
3011: ,p_flex_context => p_flex_context
3020: ,p_message => p_err_message
3021: );
3022: l_return_value := l_ddf_seg_value;
3023:
3024: Hr_Utility.set_location('..get_ddf_value ='||l_return_value, 10);
3025:
3026: ELSIF p_flex_name IN ('PER_ASSIGNMENT_EXTRA_INFO',
3027: 'PER_PEOPLE_EXTRA_INFO') THEN
3028: Hr_Utility.set_location('..Calling pqp_utilities.get_df_value', 9);
3024: Hr_Utility.set_location('..get_ddf_value ='||l_return_value, 10);
3025:
3026: ELSIF p_flex_name IN ('PER_ASSIGNMENT_EXTRA_INFO',
3027: 'PER_PEOPLE_EXTRA_INFO') THEN
3028: Hr_Utility.set_location('..Calling pqp_utilities.get_df_value', 9);
3029: l_df_seg_value:= Pqp_Utilities.get_df_value
3030: (p_flex_name => p_flex_name
3031: ,p_flex_context => p_flex_context
3032: ,p_flex_field_title => p_flex_field_title
3040: ,p_error_code => p_error_code
3041: ,p_message => p_err_message
3042: );
3043: l_return_value := l_df_seg_value;
3044: Hr_Utility.set_location('..get_df_value ='||l_return_value, 10);
3045: ELSE
3046: RAISE Invaild_DDF_or_DF;
3047: END IF;
3048: l_return_value := Nvl(l_return_value,'EXT_NULL_VALUE');
3045: ELSE
3046: RAISE Invaild_DDF_or_DF;
3047: END IF;
3048: l_return_value := Nvl(l_return_value,'EXT_NULL_VALUE');
3049: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
3050: RETURN l_return_value;
3051:
3052: EXCEPTION
3053: WHEN Invaild_DDF_or_DF THEN
3059:
3060: l_return_value := 'EXT_ERR_WARNING';
3061: IF csr_pei%ISOPEN THEN CLOSE csr_pei; END IF;
3062: IF csr_aei%ISOPEN THEN CLOSE csr_aei; END IF;
3063: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
3064: RETURN l_return_value;
3065:
3066: WHEN Others THEN
3067: l_return_value := 'EXT_ERR_WARNING';
3068: p_error_code := '-20001';
3069: p_err_message :='SQL-ERRM :'||SQLERRM;
3070: IF csr_pei%ISOPEN THEN CLOSE csr_pei; END IF;
3071: IF csr_aei%ISOPEN THEN CLOSE csr_aei; END IF;
3072: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
3073: RETURN l_return_value;
3074:
3075: END Get_DDF_DF_Value;
3076: -- =============================================================================
3125: l_input_value_id pay_input_values_f.input_value_id%TYPE;
3126: l_return_value NUMBER(2) :=0;
3127: l_df_st_date DATE := To_Date('1900/01/01','YYYY/MM/DD');
3128: BEGIN
3129: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
3130: OPEN csr_asg (c_assignment_id => p_assignment_id
3131: ,c_effective_date => p_effective_date);
3132: FETCH csr_asg INTO l_asg_rec;
3133: IF csr_asg%NOTFOUND THEN
3137: CLOSE csr_asg;
3138: l_return_value := -1;
3139: RETURN l_return_value;
3140: END IF;
3141: Hr_Utility.set_location('..Valid Assignment Id '||p_assignment_id, 6);
3142: CLOSE csr_asg;
3143: OPEN csr_per_dates (c_effective_date => p_effective_date
3144: ,c_person_id => l_asg_rec.person_id);
3145: FETCH csr_per_dates INTO l_per_dates;
3151: CLOSE csr_per_dates;
3152: l_return_value := -1;
3153: RETURN l_return_value;
3154: ELSE
3155: Hr_Utility.set_location('..Person Details found Id: '||l_asg_rec.person_id, 7);
3156: p_original_hire_date := l_per_dates.original_date_of_hire;
3157: p_recent_hire_date := l_per_dates.date_start;
3158: p_actual_termination_date := Nvl(l_per_dates.actual_termination_date,
3159: l_df_st_date);
3161: p_extract_date := p_effective_date;
3162: END IF;
3163: CLOSE csr_per_dates;
3164:
3165: Hr_Utility.set_location('..Getting the screen entry value', 7);
3166: l_ele_type_id := g_element.FIRST;
3167: WHILE l_ele_type_id IS NOT NULL
3168: LOOP
3169: l_input_value_id := g_element(l_ele_type_id).input_value_id;
3183: CLOSE csr_entry_dtls;
3184: l_prev_ele_type_id := l_ele_type_id;
3185: l_ele_type_id := g_element.NEXT(l_prev_ele_type_id);
3186: END LOOP; -- While Loop
3187: Hr_Utility.set_location('Leaving: '||l_proc_name, 60);
3188: RETURN l_return_value;
3189:
3190: EXCEPTION
3191: WHEN Others THEN
3189:
3190: EXCEPTION
3191: WHEN Others THEN
3192: p_err_message :='SQL-ERRM :'||SQLERRM;
3193: Hr_Utility.set_location('..'||p_err_message,85);
3194: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
3195: RETURN l_return_value;
3196: END Get_Participant_Status_Code;
3197:
3190: EXCEPTION
3191: WHEN Others THEN
3192: p_err_message :='SQL-ERRM :'||SQLERRM;
3193: Hr_Utility.set_location('..'||p_err_message,85);
3194: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
3195: RETURN l_return_value;
3196: END Get_Participant_Status_Code;
3197:
3198: -- =============================================================================
3240: l_base_salary NUMBER(15,2);
3241: l_annualization_factor per_pay_bases.pay_annualization_factor%TYPE;
3242: l_pay_basis_id per_all_assignments_f.pay_basis_id%TYPE;
3243: BEGIN
3244: hr_utility.set_location('Entering: '||l_proc_name, 5);
3245: IF g_primary_assig.EXISTS(p_assignment_id) THEN
3246: IF p_data_ele_name = 'EMPLOYMENT_CATEGORY' THEN
3247: l_return_value := g_primary_assig(p_assignment_id).employment_category;
3248: ELSIF p_data_ele_name = 'EMPLOYEMENT_STATUS' THEN
3267: ,2);
3268: END IF;
3269: END IF;
3270: IF g_debug THEN
3271: hr_utility.set_location(' ..p_data_ele_name : '||p_data_ele_name, 80);
3272: hr_utility.set_location(' ..l_return_value : '||l_return_value, 80);
3273: hr_utility.set_location('Leaving: '||l_proc_name, 80);
3274: END IF;
3275: RETURN l_return_value;
3268: END IF;
3269: END IF;
3270: IF g_debug THEN
3271: hr_utility.set_location(' ..p_data_ele_name : '||p_data_ele_name, 80);
3272: hr_utility.set_location(' ..l_return_value : '||l_return_value, 80);
3273: hr_utility.set_location('Leaving: '||l_proc_name, 80);
3274: END IF;
3275: RETURN l_return_value;
3276: EXCEPTION
3269: END IF;
3270: IF g_debug THEN
3271: hr_utility.set_location(' ..p_data_ele_name : '||p_data_ele_name, 80);
3272: hr_utility.set_location(' ..l_return_value : '||l_return_value, 80);
3273: hr_utility.set_location('Leaving: '||l_proc_name, 80);
3274: END IF;
3275: RETURN l_return_value;
3276: EXCEPTION
3277: WHEN Others THEN
3275: RETURN l_return_value;
3276: EXCEPTION
3277: WHEN Others THEN
3278: p_error_message :='SQL-ERRM :'||SQLERRM;
3279: hr_utility.set_location('..'||p_error_message,85);
3280: hr_utility.set_location('Leaving: '||l_proc_name, 90);
3281: RETURN l_return_value;
3282: END Get_Data_Elements;
3283:
3276: EXCEPTION
3277: WHEN Others THEN
3278: p_error_message :='SQL-ERRM :'||SQLERRM;
3279: hr_utility.set_location('..'||p_error_message,85);
3280: hr_utility.set_location('Leaving: '||l_proc_name, 90);
3281: RETURN l_return_value;
3282: END Get_Data_Elements;
3283:
3284: -- =============================================================================
3293: l_proc_name VARCHAR2(150) := g_proc_name ||'Get_Payroll_Date';
3294: l_return_value VARCHAR2(150);
3295: BEGIN
3296:
3297: hr_utility.set_location('Entering: '||l_proc_name, 5);
3298: IF g_extract_params(p_business_group_id).reporting_dimension <> 'ASG_RUN' THEN
3299: l_return_value := FND_DATE.date_to_canonical
3300: (g_extract_params(p_business_group_id).extract_end_date);
3301: ELSE
3301: ELSE
3302: l_return_value := g_action_effective_date;
3303: END IF;
3304: RETURN l_return_value;
3305: hr_utility.set_location('Leaving: '||l_proc_name, 80);
3306: EXCEPTION
3307: WHEN Others THEN
3308: p_error_message :='SQL-ERRM :'||SQLERRM;
3309: IF g_debug THEN
3306: EXCEPTION
3307: WHEN Others THEN
3308: p_error_message :='SQL-ERRM :'||SQLERRM;
3309: IF g_debug THEN
3310: hr_utility.set_location('..'||p_error_message,85);
3311: hr_utility.set_location('Leaving: '||l_proc_name, 90);
3312: END IF;
3313: RETURN l_return_value;
3314: END Get_Payroll_Date;
3307: WHEN Others THEN
3308: p_error_message :='SQL-ERRM :'||SQLERRM;
3309: IF g_debug THEN
3310: hr_utility.set_location('..'||p_error_message,85);
3311: hr_utility.set_location('Leaving: '||l_proc_name, 90);
3312: END IF;
3313: RETURN l_return_value;
3314: END Get_Payroll_Date;
3315: -- =============================================================================
3321:
3322: l_proc_name VARCHAR2(150) := g_proc_name ||'.Get_ConcProg_Information';
3323: l_return_value VARCHAR2(1000);
3324: BEGIN
3325: hr_utility.set_location('Entering: '||l_proc_name, 5);
3326: IF p_header_type = 'EXTRACT_NAME' THEN
3327: l_return_value := g_conc_prog_details(0).extract_name;
3328: ELSIF p_header_type = 'REPORT_OPTION' THEN
3329: l_return_value := g_conc_prog_details(0).reporting_options;
3337: l_return_value := g_conc_prog_details(0).beginningdt;
3338: ELSIF p_header_type = 'END_DT_PAID' THEN
3339: l_return_value := g_conc_prog_details(0).endingdt;
3340: ELSIF p_header_type = 'PAYROLL_NAME' THEN
3341: hr_utility.set_location('PAYROLL_NAME: '||g_conc_prog_details(0).payrollname, 5);
3342: l_return_value := g_conc_prog_details(0).payrollname;
3343: ELSIF p_header_type = 'CON_SET' THEN
3344: l_return_value := g_conc_prog_details(0).consolset;
3345: hr_utility.set_location('CON_SET: '||l_return_value, 5);
3341: hr_utility.set_location('PAYROLL_NAME: '||g_conc_prog_details(0).payrollname, 5);
3342: l_return_value := g_conc_prog_details(0).payrollname;
3343: ELSIF p_header_type = 'CON_SET' THEN
3344: l_return_value := g_conc_prog_details(0).consolset;
3345: hr_utility.set_location('CON_SET: '||l_return_value, 5);
3346: ELSIF p_header_type = 'ORG_NAME' THEN
3347: l_return_value := g_conc_prog_details(0).org_name;
3348: hr_utility.set_location('ORG_NAME: '||l_return_value, 5);
3349: ELSIF p_header_type = 'PERSON_TYPE' THEN
3344: l_return_value := g_conc_prog_details(0).consolset;
3345: hr_utility.set_location('CON_SET: '||l_return_value, 5);
3346: ELSIF p_header_type = 'ORG_NAME' THEN
3347: l_return_value := g_conc_prog_details(0).org_name;
3348: hr_utility.set_location('ORG_NAME: '||l_return_value, 5);
3349: ELSIF p_header_type = 'PERSON_TYPE' THEN
3350: l_return_value := g_conc_prog_details(0).person_type;
3351: hr_utility.set_location('PERSON_TYPE: '||l_return_value, 5);
3352: ELSIF p_header_type = 'LOCATION' THEN
3347: l_return_value := g_conc_prog_details(0).org_name;
3348: hr_utility.set_location('ORG_NAME: '||l_return_value, 5);
3349: ELSIF p_header_type = 'PERSON_TYPE' THEN
3350: l_return_value := g_conc_prog_details(0).person_type;
3351: hr_utility.set_location('PERSON_TYPE: '||l_return_value, 5);
3352: ELSIF p_header_type = 'LOCATION' THEN
3353: l_return_value := g_conc_prog_details(0).location;
3354: hr_utility.set_location('LOCATION: '||l_return_value, 5);
3355: END IF;
3350: l_return_value := g_conc_prog_details(0).person_type;
3351: hr_utility.set_location('PERSON_TYPE: '||l_return_value, 5);
3352: ELSIF p_header_type = 'LOCATION' THEN
3353: l_return_value := g_conc_prog_details(0).location;
3354: hr_utility.set_location('LOCATION: '||l_return_value, 5);
3355: END IF;
3356: hr_utility.set_location('Leaving: '||l_proc_name, 45);
3357:
3358: RETURN l_return_value;
3352: ELSIF p_header_type = 'LOCATION' THEN
3353: l_return_value := g_conc_prog_details(0).location;
3354: hr_utility.set_location('LOCATION: '||l_return_value, 5);
3355: END IF;
3356: hr_utility.set_location('Leaving: '||l_proc_name, 45);
3357:
3358: RETURN l_return_value;
3359: EXCEPTION
3360: WHEN Others THEN
3358: RETURN l_return_value;
3359: EXCEPTION
3360: WHEN Others THEN
3361: p_error_message :='SQL-ERRM :'||SQLERRM;
3362: hr_utility.set_location('..Exception Others Raised at Get_ConcProg_Information'||p_error_message,40);
3363: hr_utility.set_location('Leaving: '||l_proc_name, 45);
3364: RETURN l_return_value;
3365: END Get_ConcProg_Information;
3366: -- =============================================================================
3359: EXCEPTION
3360: WHEN Others THEN
3361: p_error_message :='SQL-ERRM :'||SQLERRM;
3362: hr_utility.set_location('..Exception Others Raised at Get_ConcProg_Information'||p_error_message,40);
3363: hr_utility.set_location('Leaving: '||l_proc_name, 45);
3364: RETURN l_return_value;
3365: END Get_ConcProg_Information;
3366: -- =============================================================================
3367: -- ~ Get_Element_Entry_Value: Gets the elements entry value from run-results in
3387: l_legislation_code per_business_groups.legislation_code%TYPE;
3388: l_index NUMBER :=0;
3389: l_proc_name VARCHAR2(150) := g_proc_name ||'Get_Element_Entry_Value';
3390: BEGIN
3391: hr_utility.set_location('Entering: '||l_proc_name, 5);
3392: l_legislation_code := g_extract_params(p_business_group_id).legislation_code;
3393:
3394: IF g_extract_params(p_business_group_id).reporting_dimension = 'ASG_RUN' THEN
3395: l_effective_date := g_action_effective_date;
3445: FETCH csr_entry INTO l_screen_entry_value;
3446: CLOSE csr_entry;
3447: l_return_value := l_screen_entry_value;
3448: END IF;
3449: hr_utility.set_location('Leaving: '||l_proc_name, 10);
3450: RETURN l_return_value;
3451: EXCEPTION
3452: WHEN Others THEN
3453: l_error_message := ' Error:'||SQLERRM;
3452: WHEN Others THEN
3453: l_error_message := ' Error:'||SQLERRM;
3454: p_error_message := l_error_message;
3455: IF g_debug THEN
3456: hr_utility.set_location('..'||p_error_message,10);
3457: hr_utility.set_location('Leaving: '||l_proc_name, 11);
3458: END IF;
3459: RETURN l_return_value;
3460:
3453: l_error_message := ' Error:'||SQLERRM;
3454: p_error_message := l_error_message;
3455: IF g_debug THEN
3456: hr_utility.set_location('..'||p_error_message,10);
3457: hr_utility.set_location('Leaving: '||l_proc_name, 11);
3458: END IF;
3459: RETURN l_return_value;
3460:
3461: END Get_Element_Entry_Value;
3482: l_organization_id NUMBER(15);
3483: l_gre_org_id NUMBER(15);
3484: BEGIN
3485: i := p_business_group_id;
3486: hr_utility.set_location('Entering: Get_Balance_Value ', 5);
3487: -- Check this balance Name is already exist in record
3488: -- if it is then get the balance type id
3489: FOR num IN 1..g_balance_detls.count LOOP
3490: IF g_balance_detls(num).balance_name = p_balance_name THEN
3515: g_balance_detls(l_index).defined_balance_id := l_defined_balance_id;
3516: END IF;
3517:
3518: IF g_debug THEN
3519: hr_utility.set_location(' p_balance_name: '||p_balance_name, 5);
3520: hr_utility.set_location(' l_balance_type_id: '||l_balance_type_id, 5);
3521: hr_utility.set_location(' g_asgrun_dim_id: '||g_asgrun_dim_id, 5);
3522: hr_utility.set_location(' l_defined_balance_id: '||l_defined_balance_id, 5);
3523:
3516: END IF;
3517:
3518: IF g_debug THEN
3519: hr_utility.set_location(' p_balance_name: '||p_balance_name, 5);
3520: hr_utility.set_location(' l_balance_type_id: '||l_balance_type_id, 5);
3521: hr_utility.set_location(' g_asgrun_dim_id: '||g_asgrun_dim_id, 5);
3522: hr_utility.set_location(' l_defined_balance_id: '||l_defined_balance_id, 5);
3523:
3524: END IF;
3517:
3518: IF g_debug THEN
3519: hr_utility.set_location(' p_balance_name: '||p_balance_name, 5);
3520: hr_utility.set_location(' l_balance_type_id: '||l_balance_type_id, 5);
3521: hr_utility.set_location(' g_asgrun_dim_id: '||g_asgrun_dim_id, 5);
3522: hr_utility.set_location(' l_defined_balance_id: '||l_defined_balance_id, 5);
3523:
3524: END IF;
3525: --
3518: IF g_debug THEN
3519: hr_utility.set_location(' p_balance_name: '||p_balance_name, 5);
3520: hr_utility.set_location(' l_balance_type_id: '||l_balance_type_id, 5);
3521: hr_utility.set_location(' g_asgrun_dim_id: '||g_asgrun_dim_id, 5);
3522: hr_utility.set_location(' l_defined_balance_id: '||l_defined_balance_id, 5);
3523:
3524: END IF;
3525: --
3526: -- Mexico Specific Changes
3530: -- Set the Balance Context
3531: pay_balance_pkg.set_context('tax_unit_id', g_gre_org_id);
3532: pay_balance_pkg.set_context('date_earned', g_action_effective_date);
3533: IF g_debug THEN
3534: hr_utility.set_location(' tax_unit_id: '||g_gre_org_id, 5);
3535: hr_utility.set_location(' date_earned: '||g_action_effective_date, 5);
3536: END IF;
3537: END IF;
3538: --
3531: pay_balance_pkg.set_context('tax_unit_id', g_gre_org_id);
3532: pay_balance_pkg.set_context('date_earned', g_action_effective_date);
3533: IF g_debug THEN
3534: hr_utility.set_location(' tax_unit_id: '||g_gre_org_id, 5);
3535: hr_utility.set_location(' date_earned: '||g_action_effective_date, 5);
3536: END IF;
3537: END IF;
3538: --
3539: --If Reporting dimension is ASG_RUN
3569: -- Set the Balance Context
3570: pay_balance_pkg.set_context('tax_unit_id', asgact_rec.tax_unit_id);
3571: pay_balance_pkg.set_context('date_earned', asgact_rec.effective_date);
3572: IF g_debug THEN
3573: hr_utility.set_location(' tax_unit_id: '||asgact_rec.tax_unit_id, 5);
3574: hr_utility.set_location(' date_earned: '||asgact_rec.effective_date, 5);
3575: END IF;
3576: END IF;
3577: IF g_legislation_code = 'MX' AND
3570: pay_balance_pkg.set_context('tax_unit_id', asgact_rec.tax_unit_id);
3571: pay_balance_pkg.set_context('date_earned', asgact_rec.effective_date);
3572: IF g_debug THEN
3573: hr_utility.set_location(' tax_unit_id: '||asgact_rec.tax_unit_id, 5);
3574: hr_utility.set_location(' date_earned: '||asgact_rec.effective_date, 5);
3575: END IF;
3576: END IF;
3577: IF g_legislation_code = 'MX' AND
3578: g_gre_org_id IS NOT NULL AND
3595: END LOOP; -- For Loop
3596: END IF; -- If l_defined_balance_id
3597: END IF; --final end if
3598:
3599: hr_utility.set_location(' l_bal_total_amt: '||l_bal_total_amt, 50);
3600: hr_utility.set_location('Leaving: Get_Balance_Value ', 50);
3601: RETURN l_bal_total_amt;
3602:
3603: EXCEPTION
3596: END IF; -- If l_defined_balance_id
3597: END IF; --final end if
3598:
3599: hr_utility.set_location(' l_bal_total_amt: '||l_bal_total_amt, 50);
3600: hr_utility.set_location('Leaving: Get_Balance_Value ', 50);
3601: RETURN l_bal_total_amt;
3602:
3603: EXCEPTION
3604:
3604:
3605: WHEN Others THEN
3606: p_error_message :='SQL-ERRM :'||SQLERRM;
3607: IF g_debug THEN
3608: hr_utility.set_location('..'||p_error_message,85);
3609: hr_utility.set_location('Leaving Get_Balance_Value function:', 90);
3610: END IF;
3611:
3612: RETURN l_bal_total_amt;
3605: WHEN Others THEN
3606: p_error_message :='SQL-ERRM :'||SQLERRM;
3607: IF g_debug THEN
3608: hr_utility.set_location('..'||p_error_message,85);
3609: hr_utility.set_location('Leaving Get_Balance_Value function:', 90);
3610: END IF;
3611:
3612: RETURN l_bal_total_amt;
3613:
3705: l_org_name hr_all_organization_units.NAME%TYPE;
3706: l_person_type per_person_types.user_person_type%TYPE;
3707: l_location hr_locations_all.location_code%TYPE;
3708: BEGIN
3709: hr_utility.set_location('Entering: '||l_proc_name, 5);
3710: OPEN csr_ext_name( c_ext_dfn_id => p_ext_dfn_id);
3711: FETCH csr_ext_name INTO l_extract_name;
3712: CLOSE csr_ext_name;
3713: IF p_element_set_id IS NOT NULL THEN
3732: FETCH csr_con_set INTO l_con_set_name;
3733: CLOSE csr_con_set;
3734: END IF;
3735: IF p_org_id IS NOT NULL THEN
3736: hr_utility.set_location('p_org_id: '||p_org_id, 5);
3737: OPEN csr_org_name( c_org_id => p_org_id);
3738: FETCH csr_org_name INTO l_org_name;
3739: CLOSE csr_org_name;
3740: END IF;
3738: FETCH csr_org_name INTO l_org_name;
3739: CLOSE csr_org_name;
3740: END IF;
3741: IF p_person_type_id IS NOT NULL THEN
3742: hr_utility.set_location('p_person_type_id: '||p_person_type_id, 5);
3743: OPEN csr_person_type( c_person_type_id => p_person_type_id);
3744: FETCH csr_person_type INTO l_person_type;
3745: CLOSE csr_person_type;
3746: END IF;
3744: FETCH csr_person_type INTO l_person_type;
3745: CLOSE csr_person_type;
3746: END IF;
3747: IF p_location_id IS NOT NULL THEN
3748: hr_utility.set_location('p_location_id: '||p_location_id, 5);
3749: OPEN csr_location_code( c_location_id => p_location_id);
3750: FETCH csr_location_code INTO l_location;
3751: CLOSE csr_location_code;
3752: END IF;
3750: FETCH csr_location_code INTO l_location;
3751: CLOSE csr_location_code;
3752: END IF;
3753: IF g_debug THEN
3754: hr_utility.set_location('l_gre_name : '||l_gre_name, 5);
3755: hr_utility.set_location('l_extract_name : '||l_extract_name, 5);
3756: hr_utility.set_location('l_element_name : '||l_element_name, 5);
3757: hr_utility.set_location('p_reporting_dimension : '||p_reporting_dimension, 5);
3758: hr_utility.set_location('p_selection_criteria : '||p_selection_criteria, 5);
3751: CLOSE csr_location_code;
3752: END IF;
3753: IF g_debug THEN
3754: hr_utility.set_location('l_gre_name : '||l_gre_name, 5);
3755: hr_utility.set_location('l_extract_name : '||l_extract_name, 5);
3756: hr_utility.set_location('l_element_name : '||l_element_name, 5);
3757: hr_utility.set_location('p_reporting_dimension : '||p_reporting_dimension, 5);
3758: hr_utility.set_location('p_selection_criteria : '||p_selection_criteria, 5);
3759: hr_utility.set_location('l_payroll_name: '||l_payroll_name, 5);
3752: END IF;
3753: IF g_debug THEN
3754: hr_utility.set_location('l_gre_name : '||l_gre_name, 5);
3755: hr_utility.set_location('l_extract_name : '||l_extract_name, 5);
3756: hr_utility.set_location('l_element_name : '||l_element_name, 5);
3757: hr_utility.set_location('p_reporting_dimension : '||p_reporting_dimension, 5);
3758: hr_utility.set_location('p_selection_criteria : '||p_selection_criteria, 5);
3759: hr_utility.set_location('l_payroll_name: '||l_payroll_name, 5);
3760: hr_utility.set_location('l_con_set_name: '||l_con_set_name, 5);
3753: IF g_debug THEN
3754: hr_utility.set_location('l_gre_name : '||l_gre_name, 5);
3755: hr_utility.set_location('l_extract_name : '||l_extract_name, 5);
3756: hr_utility.set_location('l_element_name : '||l_element_name, 5);
3757: hr_utility.set_location('p_reporting_dimension : '||p_reporting_dimension, 5);
3758: hr_utility.set_location('p_selection_criteria : '||p_selection_criteria, 5);
3759: hr_utility.set_location('l_payroll_name: '||l_payroll_name, 5);
3760: hr_utility.set_location('l_con_set_name: '||l_con_set_name, 5);
3761: hr_utility.set_location('l_org_name : '||l_org_name, 5);
3754: hr_utility.set_location('l_gre_name : '||l_gre_name, 5);
3755: hr_utility.set_location('l_extract_name : '||l_extract_name, 5);
3756: hr_utility.set_location('l_element_name : '||l_element_name, 5);
3757: hr_utility.set_location('p_reporting_dimension : '||p_reporting_dimension, 5);
3758: hr_utility.set_location('p_selection_criteria : '||p_selection_criteria, 5);
3759: hr_utility.set_location('l_payroll_name: '||l_payroll_name, 5);
3760: hr_utility.set_location('l_con_set_name: '||l_con_set_name, 5);
3761: hr_utility.set_location('l_org_name : '||l_org_name, 5);
3762: hr_utility.set_location('l_person_type : '||l_person_type, 5);
3755: hr_utility.set_location('l_extract_name : '||l_extract_name, 5);
3756: hr_utility.set_location('l_element_name : '||l_element_name, 5);
3757: hr_utility.set_location('p_reporting_dimension : '||p_reporting_dimension, 5);
3758: hr_utility.set_location('p_selection_criteria : '||p_selection_criteria, 5);
3759: hr_utility.set_location('l_payroll_name: '||l_payroll_name, 5);
3760: hr_utility.set_location('l_con_set_name: '||l_con_set_name, 5);
3761: hr_utility.set_location('l_org_name : '||l_org_name, 5);
3762: hr_utility.set_location('l_person_type : '||l_person_type, 5);
3763: hr_utility.set_location('l_location : '||l_location, 5);
3756: hr_utility.set_location('l_element_name : '||l_element_name, 5);
3757: hr_utility.set_location('p_reporting_dimension : '||p_reporting_dimension, 5);
3758: hr_utility.set_location('p_selection_criteria : '||p_selection_criteria, 5);
3759: hr_utility.set_location('l_payroll_name: '||l_payroll_name, 5);
3760: hr_utility.set_location('l_con_set_name: '||l_con_set_name, 5);
3761: hr_utility.set_location('l_org_name : '||l_org_name, 5);
3762: hr_utility.set_location('l_person_type : '||l_person_type, 5);
3763: hr_utility.set_location('l_location : '||l_location, 5);
3764: END IF;
3757: hr_utility.set_location('p_reporting_dimension : '||p_reporting_dimension, 5);
3758: hr_utility.set_location('p_selection_criteria : '||p_selection_criteria, 5);
3759: hr_utility.set_location('l_payroll_name: '||l_payroll_name, 5);
3760: hr_utility.set_location('l_con_set_name: '||l_con_set_name, 5);
3761: hr_utility.set_location('l_org_name : '||l_org_name, 5);
3762: hr_utility.set_location('l_person_type : '||l_person_type, 5);
3763: hr_utility.set_location('l_location : '||l_location, 5);
3764: END IF;
3765:
3758: hr_utility.set_location('p_selection_criteria : '||p_selection_criteria, 5);
3759: hr_utility.set_location('l_payroll_name: '||l_payroll_name, 5);
3760: hr_utility.set_location('l_con_set_name: '||l_con_set_name, 5);
3761: hr_utility.set_location('l_org_name : '||l_org_name, 5);
3762: hr_utility.set_location('l_person_type : '||l_person_type, 5);
3763: hr_utility.set_location('l_location : '||l_location, 5);
3764: END IF;
3765:
3766: g_conc_prog_details(0).extract_name := l_extract_name;
3759: hr_utility.set_location('l_payroll_name: '||l_payroll_name, 5);
3760: hr_utility.set_location('l_con_set_name: '||l_con_set_name, 5);
3761: hr_utility.set_location('l_org_name : '||l_org_name, 5);
3762: hr_utility.set_location('l_person_type : '||l_person_type, 5);
3763: hr_utility.set_location('l_location : '||l_location, 5);
3764: END IF;
3765:
3766: g_conc_prog_details(0).extract_name := l_extract_name;
3767: g_conc_prog_details(0).reporting_options := HR_GENERAL.DECODE_LOOKUP
3780: g_conc_prog_details(0).org_name := l_org_name;
3781: g_conc_prog_details(0).person_type := l_person_type;
3782: g_conc_prog_details(0).location := l_location;
3783:
3784: hr_utility.set_location('Leaving: '||l_proc_name, 80);
3785: EXCEPTION
3786: WHEN Others THEN
3787: hr_utility.set_location('ERROR: '||l_proc_name, 90);
3788: END Set_ConcProg_Parameter_Values;
3783:
3784: hr_utility.set_location('Leaving: '||l_proc_name, 80);
3785: EXCEPTION
3786: WHEN Others THEN
3787: hr_utility.set_location('ERROR: '||l_proc_name, 90);
3788: END Set_ConcProg_Parameter_Values;
3789: --=============================================================================
3790: -- Chk_Person_Asg:This is used to check the person id is valid for passed orgId,
3791: -- locationId and person type id.
3825: l_proc_name VARCHAR2(150) := g_proc_name ||'Chk_Person_Asg';
3826:
3827: BEGIN
3828: l_return_value := 'Y';
3829: hr_utility.set_location('Entering: '||l_proc_name, 5);
3830: IF p_person_type_id IS NULL AND
3831: p_location_id IS NULL AND
3832: p_org_id IS NULL AND
3833: p_gre_org_id IS NULL THEN
3830: IF p_person_type_id IS NULL AND
3831: p_location_id IS NULL AND
3832: p_org_id IS NULL AND
3833: p_gre_org_id IS NULL THEN
3834: hr_utility.set_location('Leaving: '||l_proc_name, 76);
3835: RETURN l_return_value;
3836: END IF;
3837: OPEN csr_per (c_person_id => p_person_id
3838: ,c_effective_date => p_effective_date
3849: ,p_tax_unit_id => l_tax_unit_id
3850: );
3851: IF p_gre_org_id <> l_tax_unit_id THEN
3852: l_return_value := 'N';
3853: hr_utility.set_location('Leaving: '||l_proc_name, 77);
3854: RETURN l_return_value;
3855: END IF;
3856:
3857: END IF;
3859: -- Checking the person type id
3860: IF (p_person_type_id IS NOT NULL AND
3861: l_per_rec.person_type_id <> p_person_type_id) THEN
3862: l_return_value := 'N';
3863: hr_utility.set_location('Leaving: '||l_proc_name, 78);
3864: RETURN l_return_value;
3865: END IF;
3866:
3867: IF (p_location_id IS NOT NULL AND
3875: OR
3876: (p_org_id IS NOT NULL AND
3877: l_per_rec.location_id <> p_location_id ) THEN
3878: l_return_value := 'N';
3879: hr_utility.set_location('Leaving: '||l_proc_name, 79);
3880: RETURN l_return_value;
3881: END IF;
3882: hr_utility.set_location('Leaving: '||l_proc_name, 80);
3883: RETURN l_return_value;
3878: l_return_value := 'N';
3879: hr_utility.set_location('Leaving: '||l_proc_name, 79);
3880: RETURN l_return_value;
3881: END IF;
3882: hr_utility.set_location('Leaving: '||l_proc_name, 80);
3883: RETURN l_return_value;
3884:
3885: EXCEPTION
3886: WHEN Others THEN
3883: RETURN l_return_value;
3884:
3885: EXCEPTION
3886: WHEN Others THEN
3887: hr_utility.set_location('Error: '||l_proc_name, 90);
3888: l_return_value:='N';
3889: RETURN l_return_value;
3890:
3891: END Chk_Person_Asg;
3908: l_valid_action VARCHAR2(2);
3909: l_proc_name VARCHAR2(150) := g_proc_name ||'Get_Pay_value';
3910: l_valid_person_chk VARCHAR2(2);
3911: BEGIN
3912: hr_utility.set_location('Entering: '||l_proc_name, 5);
3913: -- Check if this assignment was process in the criteria func. else return
3914: i := p_business_group_id;
3915:
3916: IF g_primary_assig.EXISTS(p_assignment_id) THEN
3977: l_ele_type_id := g_element.NEXT(l_prev_ele_type_id);
3978: END LOOP; -- While Loop
3979: END LOOP; -- For Loop
3980: END IF;
3981: hr_utility.set_location('Leaving: '||l_proc_name, 80);
3982: RETURN l_bal_total_amt;
3983: EXCEPTION
3984: WHEN Others THEN
3985: p_error_message :='SQL-ERRM :'||SQLERRM;
3983: EXCEPTION
3984: WHEN Others THEN
3985: p_error_message :='SQL-ERRM :'||SQLERRM;
3986: IF g_debug THEN
3987: hr_utility.set_location('..'||p_error_message,85);
3988: hr_utility.set_location('Leaving: '||l_proc_name, 90);
3989: END IF;
3990: RETURN l_bal_total_amt;
3991: END Get_Pay_value;
3984: WHEN Others THEN
3985: p_error_message :='SQL-ERRM :'||SQLERRM;
3986: IF g_debug THEN
3987: hr_utility.set_location('..'||p_error_message,85);
3988: hr_utility.set_location('Leaving: '||l_proc_name, 90);
3989: END IF;
3990: RETURN l_bal_total_amt;
3991: END Get_Pay_value;
3992: -- =============================================================================
4141: l_Chg_Evt_Exists VARCHAR2(2);
4142: l_dimension_name pay_balance_dimensions.dimension_name%TYPE;
4143: BEGIN
4144:
4145: hr_utility.set_location('Entering: '||l_proc_name, 5);
4146: i := p_business_group_id;
4147: l_ext_rslt_id := ben_ext_thread.g_ext_rslt_id;
4148: l_ext_dfn_id := ben_ext_thread.g_ext_dfn_id;
4149:
4147: l_ext_rslt_id := ben_ext_thread.g_ext_rslt_id;
4148: l_ext_dfn_id := ben_ext_thread.g_ext_dfn_id;
4149:
4150: IF NOT g_extract_params.EXISTS(i) THEN
4151: hr_utility.set_location('..Exract Params. PL/SQL not populated ', 6);
4152: -- Get the extract type, Changes extract or Full Profile
4153: OPEN csr_ext_attr(c_ext_dfn_id=> l_ext_dfn_id);
4154: FETCH csr_ext_attr INTO l_ext_dfn_type;
4155: CLOSE csr_ext_attr;
4152: -- Get the extract type, Changes extract or Full Profile
4153: OPEN csr_ext_attr(c_ext_dfn_id=> l_ext_dfn_id);
4154: FETCH csr_ext_attr INTO l_ext_dfn_type;
4155: CLOSE csr_ext_attr;
4156: hr_utility.set_location('..After cursor csr_ext_attr',7);
4157: -- Get the Conc. request id to get the params
4158: OPEN csr_req_id(c_ext_rslt_id => l_ext_rslt_id
4159: ,c_ext_dfn_id => l_ext_dfn_id
4160: ,c_business_group_id => p_business_group_id);
4159: ,c_ext_dfn_id => l_ext_dfn_id
4160: ,c_business_group_id => p_business_group_id);
4161: FETCH csr_req_id INTO l_conc_reqest_id;
4162: CLOSE csr_req_id;
4163: hr_utility.set_location('..After Conc. Request id cursor csr_req_id',8);
4164: -- Get the params. based on the conc. request id.
4165: OPEN csr_ext_params (c_request_id => l_conc_reqest_id
4166: ,c_ext_dfn_id => l_ext_dfn_id
4167: ,c_business_group_id => p_business_group_id);
4166: ,c_ext_dfn_id => l_ext_dfn_id
4167: ,c_business_group_id => p_business_group_id);
4168: FETCH csr_ext_params INTO l_ext_params;
4169: IF csr_ext_params%NOTFOUND THEN
4170: hr_utility.set_location('..Failed to get extract params',9);
4171: END IF;
4172: CLOSE csr_ext_params;
4173:
4174: -- Get the benefit action id.
4176: ,c_ext_rslt_id => l_ext_rslt_id
4177: ,c_business_group_id => p_business_group_id);
4178: FETCH csr_ben INTO l_ben_params;
4179: IF csr_ben%NOTFOUND THEN
4180: hr_utility.set_location('..Failed to get benefit action id',9);
4181: END IF;
4182: CLOSE csr_ben;
4183:
4184: -- Store the params. in a PL/SQL table record
4204:
4205: g_extract_params(i).org_id := l_ext_params.attribute5;
4206: g_extract_params(i).person_type_id := l_ext_params.attribute6;
4207: g_extract_params(i).location_id := l_ext_params.attribute7;
4208: hr_utility.set_location('..Stored the params. in a PL/SQL table record',9);
4209: -- Get the Legislation Code, Currency Code
4210: OPEN csr_leg_code (c_business_group_id => p_business_group_id);
4211: FETCH csr_leg_code INTO g_extract_params(i).legislation_code,
4212: g_extract_params(i).currency_code;
4212: g_extract_params(i).currency_code;
4213: CLOSE csr_leg_code;
4214: g_legislation_code := g_extract_params(i).legislation_code;
4215: g_business_group_id := p_business_group_id;
4216: hr_utility.set_location('..Stored bg grp id, leg code in PL/SQL table', 10);
4217: --
4218: -- Get Assignment Run dimension Id as we will be using for calculating
4219: -- the amount.
4220: IF g_legislation_code ='MX' THEN
4225:
4226: OPEN csr_asg_dimId(g_legislation_code, l_dimension_name);
4227: FETCH csr_asg_dimId INTO g_asgrun_dim_id;
4228: CLOSE csr_asg_dimId;
4229: hr_utility.set_location('..Got the dim id for _ASG_RUN dimension: '||g_asgrun_dim_id, 11);
4230: --
4231: -- Get the element details based on the element set or element type id
4232: -- and store in a PL/SQL table.
4233: --
4236: ,p_element_set_id => g_extract_params(i).element_set_id
4237: ,p_effective_date => g_extract_params(i).extract_end_date
4238: ,p_business_group_id => p_business_group_id
4239: );
4240: hr_utility.set_location('..Stored the Element Ids in PL/SQL table', 12);
4241: --
4242: -- Pass the conc program id's to store the values into PLSQL table.
4243: --
4244: Set_ConcProg_Parameter_Values
4255: ,p_org_id => g_extract_params(i).org_id
4256: ,p_person_type_id => g_extract_params(i).person_type_id
4257: ,p_location_id => g_extract_params(i).location_id
4258: );
4259: hr_utility.set_location('..Stored the Conc. Program parameters', 17);
4260:
4261: END IF;
4262: --
4263: -- Check if for this assignment id there are assign. action(s) which have
4267: g_person_id:= NVL(ben_ext_person.g_person_id
4268: ,get_current_extract_person(p_assignment_id)
4269: );
4270: IF g_debug THEN
4271: hr_utility.set_location('..business_group_id : '||p_business_group_id, 17);
4272: hr_utility.set_location('..Processing Assig Id : '||p_assignment_id, 17);
4273: hr_utility.set_location('..Processing Person Id : '||g_person_id, 17);
4274: hr_utility.set_location('..Processing Eff.Date : '||p_effective_date, 17);
4275: END IF;
4268: ,get_current_extract_person(p_assignment_id)
4269: );
4270: IF g_debug THEN
4271: hr_utility.set_location('..business_group_id : '||p_business_group_id, 17);
4272: hr_utility.set_location('..Processing Assig Id : '||p_assignment_id, 17);
4273: hr_utility.set_location('..Processing Person Id : '||g_person_id, 17);
4274: hr_utility.set_location('..Processing Eff.Date : '||p_effective_date, 17);
4275: END IF;
4276:
4269: );
4270: IF g_debug THEN
4271: hr_utility.set_location('..business_group_id : '||p_business_group_id, 17);
4272: hr_utility.set_location('..Processing Assig Id : '||p_assignment_id, 17);
4273: hr_utility.set_location('..Processing Person Id : '||g_person_id, 17);
4274: hr_utility.set_location('..Processing Eff.Date : '||p_effective_date, 17);
4275: END IF;
4276:
4277: OPEN csr_assig (c_assignment_id => p_assignment_id
4270: IF g_debug THEN
4271: hr_utility.set_location('..business_group_id : '||p_business_group_id, 17);
4272: hr_utility.set_location('..Processing Assig Id : '||p_assignment_id, 17);
4273: hr_utility.set_location('..Processing Person Id : '||g_person_id, 17);
4274: hr_utility.set_location('..Processing Eff.Date : '||p_effective_date, 17);
4275: END IF;
4276:
4277: OPEN csr_assig (c_assignment_id => p_assignment_id
4278: ,c_effective_date => LEAST(g_extract_params(i).extract_end_date,
4288:
4289: IF l_assig_rec.assignment_type = 'B' AND
4290: g_extract_params(i).ext_dfn_type <> 'GBL_MUL_CHG' THEN
4291:
4292: hr_utility.set_location('..Assignment is of type Benefits:(B)', 17);
4293: l_return_value := 'Y';
4294: g_primary_assig(p_assignment_id) := l_assig_rec;
4295:
4296: ELSIF g_extract_params(i).ext_dfn_type = 'GBL_FPR' THEN
4294: g_primary_assig(p_assignment_id) := l_assig_rec;
4295:
4296: ELSIF g_extract_params(i).ext_dfn_type = 'GBL_FPR' THEN
4297:
4298: hr_utility.set_location('..Reporting Dimension : GBL_FPR', 17);
4299:
4300: l_return_value := Chk_IfAsg_Valid
4301: (p_assignment_id => p_assignment_id
4302: ,p_business_group_id => p_business_group_id
4310: (p_pri_assignment_id => p_assignment_id
4311: ,p_business_group_id => p_business_group_id
4312: ,p_person_id => g_person_id
4313: );
4314: hr_utility.set_location(' Chk_IfSecAsg_Valid:l_return_value: '||l_return_value, 19);
4315: END IF;
4316:
4317: IF l_return_value = 'Y' THEN
4318: OPEN csr_assig (c_assignment_id => p_assignment_id
4320: p_effective_date)
4321: ,c_business_group_id => p_business_group_id);
4322: FETCH csr_assig INTO g_primary_assig(p_assignment_id);
4323: CLOSE csr_assig;
4324: hr_utility.set_location('..Valid Assig Id : '||p_assignment_id, 20);
4325: END IF;
4326:
4327: ELSIF g_extract_params(i).ext_dfn_type IN ('GBL_CHG' -- Global Single Change
4328: ,'GBL_MUL_CHG' -- Multi Change
4326:
4327: ELSIF g_extract_params(i).ext_dfn_type IN ('GBL_CHG' -- Global Single Change
4328: ,'GBL_MUL_CHG' -- Multi Change
4329: ) THEN
4330: hr_utility.set_location('..Reporting Dimension : GBL_MUL_CHG or GBL_CHG', 17);
4331: -- The Extract is a Change Extract, check if there are any events for this
4332: -- this person id within the given extract date-range.
4333: --
4334: OPEN csr_chk_log (c_person_id => g_person_id
4356: p_effective_date)
4357: );
4358: IF l_valid_person_chk = 'Y' THEN
4359: l_return_value := 'Y';
4360: hr_utility.set_location('..Chk_Person_Asg = Y : '||p_assignment_id, 79);
4361: ELSE
4362: l_return_value := 'N';
4363: RETURN l_return_value;
4364: END IF; -- IF l_valid_person_chk = 'Y'
4365:
4366: IF g_extract_params(i).reporting_dimension = 'CHG_ALL' THEN
4367: -- Check if the reporting dimension is All Employees who have changes
4368: -- recorded within the extract date-range.
4369: hr_utility.set_location('..Reporting Dimension : CHG_ALL', 79);
4370: OPEN csr_val_assg (c_assignment_id => p_assignment_id
4371: ,c_payroll_id => g_extract_params(i).payroll_id
4372: ,c_start_date => g_extract_params(i).extract_start_date
4373: ,c_end_date => g_extract_params(i).extract_end_date
4373: ,c_end_date => g_extract_params(i).extract_end_date
4374: );
4375: FETCH csr_val_assg INTO l_valid_action;
4376: IF csr_val_assg%NOTFOUND THEN
4377: hr_utility.set_location('..CHG_ALL l_valid_action: '||l_valid_action, 79);
4378: -- Check the person id is eligible for passed orgid ,persontype id and location id.
4379: l_return_value := 'N';
4380: END IF; -- IF csr_val_assg%NOTFOUND
4381: CLOSE csr_val_assg;
4382:
4383: ELSIF g_extract_params(i).reporting_dimension = 'CHG_PEN' THEN
4384: -- Check if the reporting dimension is Employees Enrolled in a plan
4385: -- who have changes recorded within the extract date-range.
4386: hr_utility.set_location('..Reporting Dimension : CHG_PEN', 79);
4387: l_ele_type_id := g_element.FIRST;
4388: << Chg_Action >>
4389: WHILE l_ele_type_id IS NOT NULL
4390: LOOP
4387: l_ele_type_id := g_element.FIRST;
4388: << Chg_Action >>
4389: WHILE l_ele_type_id IS NOT NULL
4390: LOOP
4391: hr_utility.set_location('..Ele_Type_ID'||l_ele_type_id, 17);
4392: hr_utility.set_location('..l_return_value'||l_return_value, 17);
4393: OPEN csr_chk_ele (c_start_date => g_extract_params(i).extract_start_date
4394: ,c_end_date => g_extract_params(i).extract_end_date
4395: ,c_assignment_id => p_assignment_id
4388: << Chg_Action >>
4389: WHILE l_ele_type_id IS NOT NULL
4390: LOOP
4391: hr_utility.set_location('..Ele_Type_ID'||l_ele_type_id, 17);
4392: hr_utility.set_location('..l_return_value'||l_return_value, 17);
4393: OPEN csr_chk_ele (c_start_date => g_extract_params(i).extract_start_date
4394: ,c_end_date => g_extract_params(i).extract_end_date
4395: ,c_assignment_id => p_assignment_id
4396: ,c_business_group_id => g_business_group_id
4400: );
4401: FETCH csr_chk_ele INTO l_valid_action;
4402: IF csr_chk_ele%FOUND THEN
4403: CLOSE csr_chk_ele;
4404: hr_utility.set_location('..g_person_id :'||g_person_id, 17);
4405: -- If valid then set return the value to Y, it means that this assignment
4406: -- should be extracted.
4407: OPEN csr_chk_asg(c_start_date => g_extract_params(i).extract_start_date
4408: ,c_end_date => g_extract_params(i).extract_end_date
4436: CLOSE csr_assig;
4437: g_primary_assig(p_assignment_id).Calculate_Amount := 'YES';
4438: END IF; -- l_return_value = 'Y'
4439: END IF; -- IF l_assig_rec.assignment_type = 'B'
4440: hr_utility.set_location('..l_return_value : '||l_return_value, 79);
4441: hr_utility.set_location('Leaving: '||l_proc_name, 80);
4442:
4443: IF csr_chk_ele%ISOPEN THEN
4444: CLOSE csr_chk_ele;
4437: g_primary_assig(p_assignment_id).Calculate_Amount := 'YES';
4438: END IF; -- l_return_value = 'Y'
4439: END IF; -- IF l_assig_rec.assignment_type = 'B'
4440: hr_utility.set_location('..l_return_value : '||l_return_value, 79);
4441: hr_utility.set_location('Leaving: '||l_proc_name, 80);
4442:
4443: IF csr_chk_ele%ISOPEN THEN
4444: CLOSE csr_chk_ele;
4445: END IF;
4448:
4449: EXCEPTION
4450: WHEN Others THEN
4451: p_error_message :='SQL-ERRM :'||SQLERRM;
4452: hr_utility.set_location('..'||p_error_message,85);
4453: hr_utility.set_location('Leaving: '||l_proc_name, 90);
4454: IF csr_chk_ele%ISOPEN THEN
4455: CLOSE csr_chk_ele;
4456: END IF;
4449: EXCEPTION
4450: WHEN Others THEN
4451: p_error_message :='SQL-ERRM :'||SQLERRM;
4452: hr_utility.set_location('..'||p_error_message,85);
4453: hr_utility.set_location('Leaving: '||l_proc_name, 90);
4454: IF csr_chk_ele%ISOPEN THEN
4455: CLOSE csr_chk_ele;
4456: END IF;
4457: RETURN l_return_value;
4480: l_proc_name VARCHAR2(150):= g_proc_name||'Del_Service_Detail_Recs';
4481: l_return_value NUMBER := 0; --0= Sucess, -1=Error
4482: l_ben_params csr_ben%ROWTYPE;
4483: BEGIN
4484: hr_utility.set_location('Entering :'||l_proc_name, 5);
4485: -- Get the record id for the Hidden Detail record
4486: hr_utility.set_location('..Get the hidden record for extract running..',10);
4487: FOR csr_rcd_rec IN csr_ext_rcd_id
4488: (c_hide_flag => 'Y' -- Y=Record is hidden one
4482: l_ben_params csr_ben%ROWTYPE;
4483: BEGIN
4484: hr_utility.set_location('Entering :'||l_proc_name, 5);
4485: -- Get the record id for the Hidden Detail record
4486: hr_utility.set_location('..Get the hidden record for extract running..',10);
4487: FOR csr_rcd_rec IN csr_ext_rcd_id
4488: (c_hide_flag => 'Y' -- Y=Record is hidden one
4489: ,c_rcd_type_cd => 'D')-- D=Detail, T=Total, H-Header
4490: -- Loop through each detail record for the extract
4520: AND dtl.person_id = err_rec.person_id
4521: AND dtl.business_group_id = p_business_group_id;
4522:
4523: END LOOP;
4524: hr_utility.set_location('Leaving :'||l_proc_name, 25);
4525:
4526: RETURN l_return_value;
4527:
4528: EXCEPTION
4526: RETURN l_return_value;
4527:
4528: EXCEPTION
4529: WHEN Others THEN
4530: hr_utility.set_location('.....Exception when others raised..', 20);
4531: hr_utility.set_location('Leaving :'||l_proc_name, 25);
4532: RETURN -1;
4533:
4534: END Del_Service_Detail_Recs;
4527:
4528: EXCEPTION
4529: WHEN Others THEN
4530: hr_utility.set_location('.....Exception when others raised..', 20);
4531: hr_utility.set_location('Leaving :'||l_proc_name, 25);
4532: RETURN -1;
4533:
4534: END Del_Service_Detail_Recs;
4535: