[Home] [Help]
700: BEGIN
701:
702:
703: IF p_addr_type='Y' THEN
704: hr_utility.set_location('Entering pr 1' , 1234);
705: g_rpa_add_attr(p_assignment_id).address_line1 :=g_address_rec(p_assignment_id).address_line1;
706: g_rpa_add_attr(p_assignment_id).address_line2 :=g_address_rec(p_assignment_id).address_line2;
707: g_rpa_add_attr(p_assignment_id).address_line3 :=g_address_rec(p_assignment_id).address_line3;
708: g_rpa_add_attr(p_assignment_id).zip_cd :=g_address_rec(p_assignment_id).postal_code;
707: g_rpa_add_attr(p_assignment_id).address_line3 :=g_address_rec(p_assignment_id).address_line3;
708: g_rpa_add_attr(p_assignment_id).zip_cd :=g_address_rec(p_assignment_id).postal_code;
709: -- g_rpa_add_attr(p_assignment_id).add_county :=g_address_rec(p_assignment_id).region_1;
710:
711: hr_utility.set_location('pr Add 1' || g_address_rec(p_assignment_id).address_line1, 1234);
712:
713: IF g_address_rec(p_assignment_id).region_2='AA' THEN
714: g_rpa_add_attr(p_assignment_id).add_state :='91';
715: ELSIF g_address_rec(p_assignment_id).region_2='AE' THEN
728: CLOSE c_get_state;
729: g_rpa_add_attr(p_assignment_id).add_state:=l_get_state.state_code;
730: END IF; -- IF g_address_rec(p_assignment_id).region_2='AA'
731:
732: hr_utility.set_location('State ' || g_rpa_add_attr(p_assignment_id).add_state,1001);
733:
734: hr_utility.set_location('County ' || g_address_rec(p_assignment_id).region_1,1001);
735:
736: -- IF g_address_rec(p_assignment_id).region_1 IS NULL THEN
730: END IF; -- IF g_address_rec(p_assignment_id).region_2='AA'
731:
732: hr_utility.set_location('State ' || g_rpa_add_attr(p_assignment_id).add_state,1001);
733:
734: hr_utility.set_location('County ' || g_address_rec(p_assignment_id).region_1,1001);
735:
736: -- IF g_address_rec(p_assignment_id).region_1 IS NULL THEN
737: OPEN c_get_county (g_rpa_add_attr(p_assignment_id).add_state
738: ,p_effective_date
741: CLOSE c_get_county;
742: g_address_rec(p_assignment_id).region_1:=l_get_county.county_code;
743: g_rpa_add_attr(p_assignment_id).add_county := l_get_county.county_code;
744: -- END IF;
745: hr_utility.set_location('County ' || g_rpa_add_attr(p_assignment_id).add_county,1001);
746:
747: OPEN c_get_city (g_rpa_add_attr(p_assignment_id).add_state
748: ,SUBSTR (g_address_rec(p_assignment_id).region_1,7,3)
749: ,p_effective_date
751: );
752: FETCH c_get_city INTO l_get_city;
753: CLOSE c_get_city;
754: g_rpa_add_attr(p_assignment_id).add_city :=l_get_city.city_code;
755: hr_utility.set_location('City ' || g_rpa_add_attr(p_assignment_id).add_city,1001);
756:
757: ELSE -- IF p_addr_type='Y' THEN
758:
759: hr_utility.set_location('Entering pr 2' , 1234);
755: hr_utility.set_location('City ' || g_rpa_add_attr(p_assignment_id).add_city,1001);
756:
757: ELSE -- IF p_addr_type='Y' THEN
758:
759: hr_utility.set_location('Entering pr 2' , 1234);
760:
761: OPEN c_get_pr_add (p_person_id
762: ,p_effective_date);
763: FETCH c_get_pr_add INTO l_get_pr_add;
762: ,p_effective_date);
763: FETCH c_get_pr_add INTO l_get_pr_add;
764: CLOSE c_get_pr_add;
765:
766: hr_utility.set_location('Entering pr 2' || l_get_pr_add.address_line1, 1234);
767: g_rpa_add_attr(p_assignment_id).address_line1 :=l_get_pr_add.address_line1;
768: g_rpa_add_attr(p_assignment_id).address_line2 :=l_get_pr_add.address_line2;
769: g_rpa_add_attr(p_assignment_id).address_line3 :=l_get_pr_add.address_line3;
770: g_rpa_add_attr(p_assignment_id).add_county :=l_get_pr_add.region_1;
947: l_posi_extra_info per_position_extra_info%ROWTYPE;
948: l_proc_name constant varchar2(150) := g_proc_name ||'populate_add_attr';
949: l_ssn c_ssn%ROWTYPE;
950: BEGIN
951: hr_utility.set_location ('Enter '||l_proc_name,5);
952:
953: /* OPEN c_ssn (p_person_id
954: ,p_business_group_id
955: ,p_effective_date
962:
963: g_rpa_add_attr(p_assignment_id).assignment_id := p_assignment_id;
964: g_rpa_add_attr(p_assignment_id).request_id := p_request_id;
965:
966: hr_utility.set_location (l_proc_name,10);
967: g_rpa_add_attr(p_assignment_id).pay_per_num:=
968: get_pay_period_number
969: (p_person_id => p_person_id
970: ,p_assignment_id =>p_assignment_id
975: ,p_end_date =>l_end_date
976: );
977: --g_rpa_add_attr(p_assignment_id).ssn:=l_ssn.national_identifier;
978:
979: hr_utility.set_location (l_proc_name,15);
980: ghr_history_fetch.fetch_positionei
981: ( p_position_id => l_position_id
982: ,p_information_type =>'GHR_US_POS_GRP3'
983: ,p_date_effective => p_effective_date
984: ,p_pos_ei_data => l_posi_extra_info);
985:
986: g_rpa_add_attr(p_assignment_id).nfc_agency_code := l_posi_extra_info.poei_information21;
987:
988: hr_utility.set_location (l_proc_name,20);
989: ghr_history_fetch.fetch_positionei
990: ( p_position_id => l_position_id
991: ,p_information_type =>'GHR_US_POS_GRP1'
992: ,p_date_effective => p_effective_date
1011: IF g_rpa_add_attr(p_assignment_id).poi IS NULL THEN
1012: g_rpa_add_attr(p_assignment_id).poi := l_get_pos_info.poi;
1013: END IF;
1014: END IF;
1015: hr_utility.set_location (l_proc_name,25);
1016: l_pos_ag_code :=ghr_api.get_position_agency_code_pos
1017: (p_position_id =>l_position_id
1018: ,p_business_group_id =>p_business_group_id
1019: ,p_effective_date =>p_effective_date) ;
1020:
1021: g_rpa_add_attr(p_assignment_id).dept_code := SUBSTR(l_pos_ag_code,0,2);
1022:
1023:
1024: hr_utility.set_location (l_proc_name,30);
1025:
1026: ---This part fetches code from duty station table for state
1027: --county and city.
1028:
1057: ,p_addr_type =>'N'
1058: );
1059:
1060: END IF;
1061: hr_utility.set_location (l_proc_name,60);
1062:
1063: END;
1064:
1065:
1513: build_rules;
1514: l_per_ei_data:=NULL;
1515: l_no_rpa_count := FALSE;
1516:
1517: hr_utility.set_location ('Enter'||l_proc_name,05);
1518: g_rpa_attr(p_request_id).assignment_id := p_assignment_id;
1519: g_rpa_attr(p_request_id).request_id := p_request_id;
1520:
1521:
1525: ,p_per_ei_data =>l_per_ei_data
1526: );
1527:
1528:
1529: hr_utility.set_location (l_proc_name,10);
1530: -- g_rpa_attr(p_request_id).Previous_agency_code :=l_per_ei_data.pei_information7;
1531:
1532: IF p_first_noa_cd LIKE '1%' OR p_first_noa_cd LIKE '2%' OR p_first_noa_cd LIKE '5%'
1533: OR p_first_noa_cd LIKE '7%' OR p_first_noa_cd='866' OR p_sec_noa_cd LIKE '1%' OR
1555: NULL;
1556: END;
1557:
1558:
1559: hr_utility.set_location (l_proc_name,15);
1560: --modify
1561: l_per_ei_data:=NULL;
1562: ghr_history_fetch.fetch_peopleei(p_person_id =>p_person_id
1563: ,p_information_type =>'GHR_US_PER_SEPARATE_RETIRE'
1565: ,p_per_ei_data =>l_per_ei_data
1566: );
1567:
1568:
1569: hr_utility.set_location (l_proc_name||p_first_noa_cd,20);
1570:
1571: IF g_psr_month.exists(p_first_noa_cd) THEN
1572: g_rpa_attr(p_request_id).Date_last_pay_status_retired :=l_per_ei_data.pei_information21;
1573: hr_utility.set_location (l_proc_name,25);
1569: hr_utility.set_location (l_proc_name||p_first_noa_cd,20);
1570:
1571: IF g_psr_month.exists(p_first_noa_cd) THEN
1572: g_rpa_attr(p_request_id).Date_last_pay_status_retired :=l_per_ei_data.pei_information21;
1573: hr_utility.set_location (l_proc_name,25);
1574: END IF;
1575: g_rpa_attr(p_request_id).Frozen_CSRS_service :=l_per_ei_data.pei_information5;
1576: g_rpa_attr(p_request_id).CSRS_coverage_at_appointment :=l_per_ei_data.pei_information4;
1577:
1574: END IF;
1575: g_rpa_attr(p_request_id).Frozen_CSRS_service :=l_per_ei_data.pei_information5;
1576: g_rpa_attr(p_request_id).CSRS_coverage_at_appointment :=l_per_ei_data.pei_information4;
1577:
1578: hr_utility.set_location (l_proc_name,30);
1579: l_per_ei_data:=NULL;
1580:
1581: ghr_history_fetch.fetch_peopleei(p_person_id =>p_person_id
1582: ,p_information_type =>'GHR_US_PER_LEAVE_INFO'
1586:
1587:
1588: IF g_sler_month.exists(p_first_noa_cd) THEN
1589: g_rpa_attr(p_request_id).Date_sick_leave_exp_ret :=l_per_ei_data.pei_information5;
1590: hr_utility.set_location (l_proc_name,35);
1591: END IF;
1592:
1593: g_rpa_attr(p_request_id).Annual_leave_category :=NVL(l_per_ei_data.pei_information3,'0');
1594: -- g_rpa_attr(p_request_id).Annual_leave_45_day_code :=l_per_ei_data.pei_information4;
1593: g_rpa_attr(p_request_id).Annual_leave_category :=NVL(l_per_ei_data.pei_information3,'0');
1594: -- g_rpa_attr(p_request_id).Annual_leave_45_day_code :=l_per_ei_data.pei_information4;
1595: g_rpa_attr(p_request_id).Leave_ear_stat_py_period :=l_per_ei_data.pei_information6;
1596:
1597: hr_utility.set_location (l_proc_name,40);
1598:
1599: l_per_ei_data:=NULL;
1600: ghr_history_fetch.fetch_peopleei(p_person_id =>p_person_id
1601: ,p_information_type =>'GHR_US_PER_SCD_INFORMATION'
1607: g_rpa_attr(p_request_id).Date_SCD_RIF :=l_per_ei_data.pei_information5;
1608: g_rpa_attr(p_request_id).Date_TSP_vested :=l_per_ei_data.pei_information6;
1609: g_rpa_attr(p_request_id).Date_SCD_SES :=l_per_ei_data.pei_information8;
1610:
1611: hr_utility.set_location (l_proc_name,45);
1612:
1613: l_per_ei_data:=NULL;
1614: ghr_history_fetch.fetch_peopleei(p_person_id =>p_person_id
1615: ,p_information_type =>'GHR_US_PER_PROBATIONS'
1620: g_rpa_attr(p_request_id).Date_Supv_Mgr_Prob :=l_per_ei_data.pei_information8;
1621: g_rpa_attr(p_request_id).Date_Prob_period_start :=l_per_ei_data.pei_information3;
1622: g_rpa_attr(p_request_id).Supv_mgr_prob_period_req :=l_per_ei_data.pei_information6;
1623:
1624: hr_utility.set_location (l_proc_name,50);
1625:
1626: l_per_ei_data:=NULL;
1627: ghr_history_fetch.fetch_peopleei(p_person_id =>p_person_id
1628: ,p_information_type =>'GHR_US_PER_CONVERSIONS'
1789: OR (p_first_noa_cd = '002' AND (TO_NUMBER(p_sec_noa_cd) >= 300 AND TO_NUMBER(p_sec_noa_cd) <=399 )) THEN
1790: g_rpa_attr(p_request_id).action_code :=3;
1791: ELSIF TO_NUMBER(p_first_noa_cd) >= 500 AND TO_NUMBER(p_first_noa_cd) <=599 THEN
1792:
1793: g_rpa_attr(p_request_id).action_code:= ghr_utility.get_nfc_conv_action_code (p_request_id);
1794: ELSIF
1795: p_first_noa_cd = '002' AND (TO_NUMBER(p_sec_noa_cd) >= 500 AND TO_NUMBER(p_sec_noa_cd) <=599 ) THEN
1796: g_rpa_attr(p_request_id).action_code:=ghr_utility.get_nfc_conv_action_code (p_sec_noa_cd);
1797:
1792:
1793: g_rpa_attr(p_request_id).action_code:= ghr_utility.get_nfc_conv_action_code (p_request_id);
1794: ELSIF
1795: p_first_noa_cd = '002' AND (TO_NUMBER(p_sec_noa_cd) >= 500 AND TO_NUMBER(p_sec_noa_cd) <=599 ) THEN
1796: g_rpa_attr(p_request_id).action_code:=ghr_utility.get_nfc_conv_action_code (p_sec_noa_cd);
1797:
1798: ELSE
1799: g_rpa_attr(p_request_id).action_code :=2;
1800:
1800:
1801: END IF;
1802:
1803: IF g_rpa_rec(p_request_id).to_position_id IS NULL THEN
1804: hr_utility.set_location ('Inside null',05);
1805: g_rpa_rec(p_request_id).to_position_id := g_rpa_rec(p_request_id).from_position_id;
1806: END IF;
1807:
1808: l_pos_ag_code :=ghr_api.get_position_agency_code_pos
1845: ,p_date_effective => p_effective_date
1846: ,p_pos_ei_data => l_posi_extra_info);*/
1847:
1848:
1849: hr_utility.set_location ('p_effective_date'||p_effective_date,05);
1850:
1851: OPEN c_get_default_values (l_position_id
1852: ,p_effective_date);
1853: FETCH c_get_default_values INTO l_get_default_values;
1852: ,p_effective_date);
1853: FETCH c_get_default_values INTO l_get_default_values;
1854: CLOSE c_get_default_values;
1855:
1856: hr_utility.set_location ('l_get_default_values.nfc_agency_code'||l_get_default_values.nfc_agency_code,05);
1857: hr_utility.set_location ('p_request_id'||p_request_id,05);
1858:
1859:
1860: g_rpa_attr(p_request_id).nfc_agency := l_get_default_values.nfc_agency_code;
1853: FETCH c_get_default_values INTO l_get_default_values;
1854: CLOSE c_get_default_values;
1855:
1856: hr_utility.set_location ('l_get_default_values.nfc_agency_code'||l_get_default_values.nfc_agency_code,05);
1857: hr_utility.set_location ('p_request_id'||p_request_id,05);
1858:
1859:
1860: g_rpa_attr(p_request_id).nfc_agency := l_get_default_values.nfc_agency_code;
1861:
1962: -- g_rpa_attr(p_request_id).authentication_dt :=l_get_ei_val.rei_information3;
1963: --derive previous nature of action code
1964: IF p_first_noa_cd='001' THEN
1965: g_rpa_attr(p_request_id).nat_act_prev :=
1966: ghr_utility.get_nfc_prev_noa(
1967: p_person_id => p_person_id
1968: ,p_pa_notification_id => p_notification_id
1969: ,p_effective_date => p_effective_date);
1970: END IF;
1969: ,p_effective_date => p_effective_date);
1970: END IF;
1971:
1972: IF p_first_noa_cd='001' OR p_first_noa_cd='002' THEN
1973: ghr_utility.get_nfc_auth_codes(
1974: p_person_id =>p_person_id,
1975: p_pa_notification_id => p_notification_id ,
1976: p_effective_date =>p_effective_date,
1977: p_first_auth_code => g_rpa_attr(p_request_id).csc_auth_prev_noa,
2026:
2027: g_rpa_attr(p_request_id).veterans_pref_for_rif := '1' ;
2028: END IF;
2029:
2030: hr_utility.set_location ('Populate Attr Position Class Code ',54);
2031: IF (TO_NUMBER(p_first_noa_cd) >=300 AND TO_NUMBER(p_first_noa_cd) <= 399)
2032: OR (TO_NUMBER(p_sec_noa_cd) >=300 AND TO_NUMBER(p_sec_noa_cd) <= 399)
2033: --OR (TO_NUMBER(p_first_noa_cd)=507 OR TO_NUMBER(p_sec_noa_cd) =507)
2034: OR (TO_NUMBER(p_first_noa_cd)=713 OR TO_NUMBER(p_sec_noa_cd) =713)
2126: g_rpa_attr(p_request_id).special_emp_prg_code := l_get_ei_val.rei_information22;
2127: END IF;
2128:
2129: END IF;
2130: hr_utility.set_location ('Leaving Populate Attr',55);
2131: END populate_attr;
2132: -- =============================================================================
2133: -- Build_rules:
2134: -- =============================================================================
2246:
2247: l_proc constant varchar2(150) := g_proc_name||'Get_Rcds_Details';
2248: l_col_value varchar2(600);
2249: begin
2250: Hr_Utility.set_location('Entering'||l_proc, 5);
2251: for rcd_rec in csr_ext_rcd
2252: (c_hide_flag => 'N'
2253: ,c_rcd_type_cd => 'D')
2254: loop
2261: end if;
2262: g_ext_rcd(rcd_rec.ext_rcd_id).data_value := eir.string_val;
2263: g_ext_rcd(rcd_rec.ext_rcd_id).seq_num := eir.seq_num;
2264: g_ext_rcd(rcd_rec.ext_rcd_id).col_name := l_col_value;
2265: Hr_Utility.set_location(' ext_rcd_id: '||rcd_rec.ext_rcd_id, 5);
2266: Hr_Utility.set_location(' data_value: '||eir.string_val, 5);
2267: Hr_Utility.set_location(' seq_num : '||eir.seq_num, 5);
2268: Hr_Utility.set_location(' col_name : '||l_col_value, 5);
2269: end loop;
2262: g_ext_rcd(rcd_rec.ext_rcd_id).data_value := eir.string_val;
2263: g_ext_rcd(rcd_rec.ext_rcd_id).seq_num := eir.seq_num;
2264: g_ext_rcd(rcd_rec.ext_rcd_id).col_name := l_col_value;
2265: Hr_Utility.set_location(' ext_rcd_id: '||rcd_rec.ext_rcd_id, 5);
2266: Hr_Utility.set_location(' data_value: '||eir.string_val, 5);
2267: Hr_Utility.set_location(' seq_num : '||eir.seq_num, 5);
2268: Hr_Utility.set_location(' col_name : '||l_col_value, 5);
2269: end loop;
2270: end loop;
2263: g_ext_rcd(rcd_rec.ext_rcd_id).seq_num := eir.seq_num;
2264: g_ext_rcd(rcd_rec.ext_rcd_id).col_name := l_col_value;
2265: Hr_Utility.set_location(' ext_rcd_id: '||rcd_rec.ext_rcd_id, 5);
2266: Hr_Utility.set_location(' data_value: '||eir.string_val, 5);
2267: Hr_Utility.set_location(' seq_num : '||eir.seq_num, 5);
2268: Hr_Utility.set_location(' col_name : '||l_col_value, 5);
2269: end loop;
2270: end loop;
2271: Hr_Utility.set_location('Leaving'||l_proc, 80);
2264: g_ext_rcd(rcd_rec.ext_rcd_id).col_name := l_col_value;
2265: Hr_Utility.set_location(' ext_rcd_id: '||rcd_rec.ext_rcd_id, 5);
2266: Hr_Utility.set_location(' data_value: '||eir.string_val, 5);
2267: Hr_Utility.set_location(' seq_num : '||eir.seq_num, 5);
2268: Hr_Utility.set_location(' col_name : '||l_col_value, 5);
2269: end loop;
2270: end loop;
2271: Hr_Utility.set_location('Leaving'||l_proc, 80);
2272:
2267: Hr_Utility.set_location(' seq_num : '||eir.seq_num, 5);
2268: Hr_Utility.set_location(' col_name : '||l_col_value, 5);
2269: end loop;
2270: end loop;
2271: Hr_Utility.set_location('Leaving'||l_proc, 80);
2272:
2273: end Get_Rcds_Details;
2274: -- =============================================================================
2275: -- ~ Write_Warning:
2283: l_err_name varchar2(2000);
2284: l_err_no number;
2285:
2286: begin
2287: Hr_Utility.set_location('Entering'||l_proc, 5);
2288: l_err_name := p_err_name ;
2289: l_err_no := p_err_no ;
2290: --
2291: if p_err_no is null then
2309: p_business_group_id => g_business_group_id,
2310: p_ext_rslt_id => Ben_Extract.g_ext_rslt_id);
2311: end if;
2312: --
2313: Hr_Utility.set_location('Exiting'||l_proc, 15);
2314: --
2315:
2316: end Write_Warning;
2317:
2334: and typ_cd <> 'W';
2335: --
2336: begin
2337: --
2338: Hr_Utility.set_location('Entering'||l_proc, 5);
2339: l_err_name := p_err_name ;
2340: l_err_no := p_err_no ;
2341: if p_err_no is null then
2342: -- Assumed the name is Error Name
2366: p_person_id => g_person_id,
2367: p_business_group_id => g_business_group_id,
2368: p_ext_rslt_id => Ben_Extract.g_ext_rslt_id);
2369: end if;
2370: Hr_Utility.set_location('Exiting'||l_proc, 15);
2371: end Write_Error;
2372:
2373: -- =============================================================================
2374: -- ~ Extract_Exception:
2392: begin
2393: l_ext_rslt_id:= ben_extract.g_ext_rslt_id;
2394: l_return_value := '0';
2395: if g_debug then
2396: Hr_Utility.set_location('Entering : '||l_proc, 5);
2397: Hr_Utility.set_location(' l_ext_rslt_id : '||l_ext_rslt_id, 5);
2398: Hr_Utility.set_location(' p_msg_type : ' ||p_msg_type, 5);
2399: Hr_Utility.set_location(' p_msg_code : ' ||p_msg_code, 5);
2400: end if;
2393: l_ext_rslt_id:= ben_extract.g_ext_rslt_id;
2394: l_return_value := '0';
2395: if g_debug then
2396: Hr_Utility.set_location('Entering : '||l_proc, 5);
2397: Hr_Utility.set_location(' l_ext_rslt_id : '||l_ext_rslt_id, 5);
2398: Hr_Utility.set_location(' p_msg_type : ' ||p_msg_type, 5);
2399: Hr_Utility.set_location(' p_msg_code : ' ||p_msg_code, 5);
2400: end if;
2401:
2394: l_return_value := '0';
2395: if g_debug then
2396: Hr_Utility.set_location('Entering : '||l_proc, 5);
2397: Hr_Utility.set_location(' l_ext_rslt_id : '||l_ext_rslt_id, 5);
2398: Hr_Utility.set_location(' p_msg_type : ' ||p_msg_type, 5);
2399: Hr_Utility.set_location(' p_msg_code : ' ||p_msg_code, 5);
2400: end if;
2401:
2402: if p_assignment_id <> -1 and
2395: if g_debug then
2396: Hr_Utility.set_location('Entering : '||l_proc, 5);
2397: Hr_Utility.set_location(' l_ext_rslt_id : '||l_ext_rslt_id, 5);
2398: Hr_Utility.set_location(' p_msg_type : ' ||p_msg_type, 5);
2399: Hr_Utility.set_location(' p_msg_code : ' ||p_msg_code, 5);
2400: end if;
2401:
2402: if p_assignment_id <> -1 and
2403: l_ext_rslt_id <> -1 then
2416: ,Ben_Ext_Fmt.g_elmt_name)
2417: );
2418: end if;
2419: end if;
2420: Hr_Utility.set_location('Leaving: '||l_proc, 80);
2421: return l_return_value;
2422:
2423: end Extract_Exception;
2424: -- =============================================================================
2451: l_exists varchar2(2);
2452: l_proc_name constant varchar2(150) := g_proc_name ||'Exclude_Person';
2453: begin
2454:
2455: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
2456: open csr_ben_per (c_person_id => p_person_id
2457: ,c_benefit_action_id => p_benefit_action_id);
2458: fetch csr_ben_per into l_ben_per;
2459: close csr_ben_per;
2476: and l_ben_per.person_action_id
2477: between bbr.starting_person_action_id
2478: and bbr.ending_person_action_id;
2479: end if;
2480: Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
2481:
2482: end Exclude_Person;
2483:
2484: -- =============================================================================
2512: l_proc_name constant varchar2(150):= g_proc_name||'Extract_Post_Process';
2513: l_return_value varchar2(2000);
2514:
2515: begin
2516: Hr_Utility.set_location('Entering :'||l_proc_name, 5);
2517: l_return_value := '0';
2518: -- Get the record id for the Hidden Detail record
2519: Hr_Utility.set_location('..Get the hidden record for extract running..',10);
2520: for csr_rcd_rec in csr_ext_rcd
2515: begin
2516: Hr_Utility.set_location('Entering :'||l_proc_name, 5);
2517: l_return_value := '0';
2518: -- Get the record id for the Hidden Detail record
2519: Hr_Utility.set_location('..Get the hidden record for extract running..',10);
2520: for csr_rcd_rec in csr_ext_rcd
2521: (c_hide_flag => 'Y' -- Y=Record is hidden one
2522: ,c_rcd_type_cd => 'D')-- D=Detail, T=Total, H-Header
2523: -- Loop through each detail record for the extract
2579: OPEN csr_get_record_count(c_ext_rcd_id =>csr_header_rcd_id.ext_rcd_id);
2580: FETCH csr_get_record_count INTO l_record_count;
2581: CLOSE csr_get_record_count;
2582:
2583: Hr_Utility.set_location('Header Record ID ' ||csr_header_rcd_id.ext_rcd_id, 5);
2584: l_rc :=l_record_count;
2585: UPDATE ben_ext_rslt_dtl set val_06 = LPAD(l_rc,8,'0')
2586: WHERE ext_rcd_id = csr_header_rcd_id.ext_rcd_id
2587: AND ext_rslt_id = Ben_Ext_Thread.g_ext_rslt_id
2593: GHR_WF.initiate_notification (p_request_id =>l_conc_reqest_id
2594: ,p_result_id =>Ben_Ext_Thread.g_ext_rslt_id
2595: ,p_role =>g_extract_params(p_business_group_id).notify);
2596:
2597: Hr_Utility.set_location('Leaving :'||l_proc_name, 25);
2598:
2599: return l_return_value;
2600:
2601: exception
2599: return l_return_value;
2600:
2601: exception
2602: when Others then
2603: Hr_Utility.set_location('..Exception when others raised..', 20);
2604: Hr_Utility.set_location('Leaving :'||l_proc_name, 25);
2605: l_return_value := '1';
2606: return l_return_value;
2607:
2600:
2601: exception
2602: when Others then
2603: Hr_Utility.set_location('..Exception when others raised..', 20);
2604: Hr_Utility.set_location('Leaving :'||l_proc_name, 25);
2605: l_return_value := '1';
2606: return l_return_value;
2607:
2608: end Extract_Post_Process;
2641: l_session_id NUMBER;
2642: l_proc_name VARCHAR2(150) := g_proc_name ||'Pension_Extract_Process';
2643:
2644: BEGIN
2645: hr_utility.set_location('Entering: '||l_proc_name, 5);
2646:
2647: ben_ext_thread.process
2648: (errbuf => l_errbuff,
2649: retcode => l_retcode,
2651: p_ext_dfn_id => p_ext_dfn_id,
2652: p_effective_date => p_to_date,
2653: p_business_group_id => p_business_group_id);
2654:
2655: hr_utility.set_location('Leaving: '||l_proc_name, 80);
2656: EXCEPTION
2657: WHEN Others THEN
2658: hr_utility.set_location('Leaving: '||l_proc_name, 90);
2659: RAISE;
2654:
2655: hr_utility.set_location('Leaving: '||l_proc_name, 80);
2656: EXCEPTION
2657: WHEN Others THEN
2658: hr_utility.set_location('Leaving: '||l_proc_name, 90);
2659: RAISE;
2660: END NFC_Extract_Process;
2661: -- =============================================================================
2662: -- ~ Get_Remarks_Id:
2670: l_return_value varchar2(2000);
2671: l_pa_req_id number(15);
2672: l_proc_name constant varchar2(250) := g_proc_name ||'Get_Remarks_Id';
2673: begin
2674: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
2675: l_return_value := null;
2676:
2677: IF g_pa_req.EXISTS(p_assignment_id) THEN
2678: l_pa_req_id := g_pa_req(p_assignment_id).pa_request_id;
2678: l_pa_req_id := g_pa_req(p_assignment_id).pa_request_id;
2679: END IF;
2680:
2681:
2682: Hr_Utility.set_location(' l_pa_req_id: '||l_pa_req_id, 6);
2683: if l_pa_req_id is not null and
2684: g_pa_req_remark.exists(l_pa_req_id) then
2685:
2686: IF p_input_value = 'RPA_RC01' then
2704: ELSIF p_input_value = 'RPA_RC10' then
2705: l_return_value := g_pa_req_remark(l_pa_req_id).remark_code_10;
2706: END IF;
2707: END IF;
2708: Hr_Utility.set_location('l_return_value: '||l_return_value, 79);
2709: Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
2710: return l_return_value;
2711: exception
2712: when others then
2705: l_return_value := g_pa_req_remark(l_pa_req_id).remark_code_10;
2706: END IF;
2707: END IF;
2708: Hr_Utility.set_location('l_return_value: '||l_return_value, 79);
2709: Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
2710: return l_return_value;
2711: exception
2712: when others then
2713: p_error_code := sqlcode;
2711: exception
2712: when others then
2713: p_error_code := sqlcode;
2714: p_error_message := NULL;
2715: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
2716: return l_return_value;
2717:
2718: end Get_Remarks_Id;
2719:
2730: l_pa_request_id number;
2731: l_proc_name constant varchar2(250) := g_proc_name ||'Get_RPA_Data';
2732:
2733: begin
2734: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
2735: l_return_value := null;
2736:
2737: if g_pa_req.exists(p_assignment_id) then
2738: l_pa_request_id := g_pa_req(p_assignment_id).pa_request_id;
2736:
2737: if g_pa_req.exists(p_assignment_id) then
2738: l_pa_request_id := g_pa_req(p_assignment_id).pa_request_id;
2739: end if;
2740: Hr_Utility.set_location(' l_pa_request_id: '||l_pa_request_id, 6);
2741: Hr_Utility.set_location(' p_input_value: '||p_input_value, 6);
2742:
2743: if (l_pa_request_id is not null and
2744: g_rpa_rec.exists(l_pa_request_id))then
2737: if g_pa_req.exists(p_assignment_id) then
2738: l_pa_request_id := g_pa_req(p_assignment_id).pa_request_id;
2739: end if;
2740: Hr_Utility.set_location(' l_pa_request_id: '||l_pa_request_id, 6);
2741: Hr_Utility.set_location(' p_input_value: '||p_input_value, 6);
2742:
2743: if (l_pa_request_id is not null and
2744: g_rpa_rec.exists(l_pa_request_id))then
2745:
2982: elsif p_input_value ='RPA_PMSO_POI' then
2983: l_return_value := g_rpa_attr(l_pa_request_id).pmso_poi ;
2984: elsif p_input_value ='RPA_NFC_AGENCY' then
2985: l_return_value := g_rpa_attr(l_pa_request_id).nfc_agency ;
2986: Hr_Utility.set_location(' l_return_value: '||l_return_value, 6);
2987: elsif p_input_value ='RPA_PMSO_AGENCY' then
2988: l_return_value := g_rpa_attr(l_pa_request_id).pmso_agency ;
2989: elsif p_input_value ='RPA_POS_NUM' then
2990: l_return_value := g_rpa_attr(l_pa_request_id).pos_num ;
3105:
3106: end if;
3107: end if;
3108:
3109: Hr_Utility.set_location(' l_return_value: '||l_return_value, 79);
3110: Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
3111:
3112: return l_return_value;
3113:
3106: end if;
3107: end if;
3108:
3109: Hr_Utility.set_location(' l_return_value: '||l_return_value, 79);
3110: Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
3111:
3112: return l_return_value;
3113:
3114: exception
3114: exception
3115: when others then
3116: p_error_code := sqlcode;
3117: p_error_message :=p_input_value; --sqlerrm;
3118: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
3119: return l_return_value;
3120:
3121: end Get_RPA_Data;
3122: -- =============================================================================
3160: l_awd_rec_exists BOOLEAN;
3161: l_rem_code VARCHAR2(3);
3162: begin
3163:
3164: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
3165: l_return_value := null;
3166: l_rpa_rec_exists := false; l_rem_exists := false;
3167:
3168:
3166: l_rpa_rec_exists := false; l_rem_exists := false;
3167:
3168:
3169: if g_pa_req.exists(p_assignment_id) then
3170: Hr_Utility.set_location(' entering g_pa_req if', 6);
3171: l_pa_request_id := g_pa_req(p_assignment_id).pa_request_id;
3172: l_rpa_rec_exists := g_rpa_rec.exists(l_pa_request_id);
3173: l_rem_exists := g_pa_req_remark.exists(l_pa_request_id);
3174: end if;
3172: l_rpa_rec_exists := g_rpa_rec.exists(l_pa_request_id);
3173: l_rem_exists := g_pa_req_remark.exists(l_pa_request_id);
3174: end if;
3175:
3176: Hr_Utility.set_location(' l_pa_request_id: '||l_pa_request_id, 6);
3177: Hr_Utility.set_location(' p_input_value: '||p_input_value, 6);
3178:
3179: if (l_rpa_rec_exists and
3180: l_rem_exists )then
3173: l_rem_exists := g_pa_req_remark.exists(l_pa_request_id);
3174: end if;
3175:
3176: Hr_Utility.set_location(' l_pa_request_id: '||l_pa_request_id, 6);
3177: Hr_Utility.set_location(' p_input_value: '||p_input_value, 6);
3178:
3179: if (l_rpa_rec_exists and
3180: l_rem_exists )then
3181: Hr_Utility.set_location(' entering first if', 6);
3177: Hr_Utility.set_location(' p_input_value: '||p_input_value, 6);
3178:
3179: if (l_rpa_rec_exists and
3180: l_rem_exists )then
3181: Hr_Utility.set_location(' entering first if', 6);
3182:
3183: open csr_rem_dsc(g_pa_req(p_assignment_id).pa_remark_id
3184: ,l_pa_request_id
3185: ,g_rpa_rec(l_pa_request_id).effective_date);
3206: l_return_value :=fnd_date.date_to_canonical(g_rpa_rec(l_pa_request_id).effective_date);
3207: -- FND_DATE.CANONICAL_TO_DATE(g_rpa_rec(l_pa_request_id).effective_date);
3208:
3209: elsif p_input_value ='REM_DESC_0109' then
3210: --Hr_Utility.set_location(' p_input_value: '||p_input_value, 6);
3211: l_return_value :=SUBSTR(l_value,0,524);
3212: elsif p_input_value ='REM_DESC_01' then
3213: l_return_value :=SUBSTR(l_value,0,222);
3214: elsif p_input_value ='REM_DESC_02' then
3256: -- For awards
3257: l_awd_rec_exists := false; l_rem_exists := false;
3258:
3259: if g_aw_req.exists(p_assignment_id) then
3260: Hr_Utility.set_location(' entering g_aw_req if', 6);
3261: l_pa_request_id := g_aw_req(p_assignment_id).pa_request_id;
3262: l_awd_rec_exists := g_awd_rec.exists(l_pa_request_id);
3263: l_rem_exists := g_pa_req_remark.exists(l_pa_request_id);
3264: end if;
3262: l_awd_rec_exists := g_awd_rec.exists(l_pa_request_id);
3263: l_rem_exists := g_pa_req_remark.exists(l_pa_request_id);
3264: end if;
3265:
3266: Hr_Utility.set_location(' l_pa_request_id: '||l_pa_request_id, 16);
3267: Hr_Utility.set_location(' p_input_value: '||p_input_value, 16);
3268:
3269: if (l_awd_rec_exists and
3270: l_rem_exists )then
3263: l_rem_exists := g_pa_req_remark.exists(l_pa_request_id);
3264: end if;
3265:
3266: Hr_Utility.set_location(' l_pa_request_id: '||l_pa_request_id, 16);
3267: Hr_Utility.set_location(' p_input_value: '||p_input_value, 16);
3268:
3269: if (l_awd_rec_exists and
3270: l_rem_exists )then
3271: Hr_Utility.set_location(' entering first if', 6);
3267: Hr_Utility.set_location(' p_input_value: '||p_input_value, 16);
3268:
3269: if (l_awd_rec_exists and
3270: l_rem_exists )then
3271: Hr_Utility.set_location(' entering first if', 6);
3272: open csr_rem_dsc(g_aw_req(p_assignment_id).pa_remark_id
3273: ,l_pa_request_id
3274: ,g_awd_rec(l_pa_request_id).effective_date);
3275: fetch csr_rem_dsc into l_csr_rem_dsc;
3295: l_return_value :=FND_DATE.date_to_canonical(g_awd_rec(l_pa_request_id).effective_date);
3296: -- FND_DATE.CANONICAL_TO_DATE(g_rpa_rec(l_pa_request_id).effective_date);
3297:
3298: elsif p_input_value ='REM_DESC_0109' then
3299: --Hr_Utility.set_location(' p_input_value: '||p_input_value, 6);
3300: l_return_value :=SUBSTR(l_value,0,524);
3301: elsif p_input_value ='REM_DESC_01' then
3302: l_return_value :=SUBSTR(l_value,0,222);
3303: elsif p_input_value ='REM_DESC_02' then
3342: end if;
3343:
3344: -- End awards
3345:
3346: Hr_Utility.set_location(' l_return_value: '||l_return_value, 79);
3347: Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
3348:
3349: return l_return_value;
3350:
3343:
3344: -- End awards
3345:
3346: Hr_Utility.set_location(' l_return_value: '||l_return_value, 79);
3347: Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
3348:
3349: return l_return_value;
3350:
3351: exception
3351: exception
3352: when others then
3353: p_error_code := sqlcode;
3354: p_error_message := NULL;
3355: Hr_Utility.set_location('SQLERRM: '||sqlerrm, 90);
3356: Hr_Utility.set_location('error Leaving: '||l_proc_name, 90);
3357: return l_return_value;
3358:
3359: end Get_Remarks_Data;
3352: when others then
3353: p_error_code := sqlcode;
3354: p_error_message := NULL;
3355: Hr_Utility.set_location('SQLERRM: '||sqlerrm, 90);
3356: Hr_Utility.set_location('error Leaving: '||l_proc_name, 90);
3357: return l_return_value;
3358:
3359: end Get_Remarks_Data;
3360:
3370: l_return_value varchar2(2000);
3371: l_proc_name constant varchar2(250) := g_proc_name ||'Get_Award_Data';
3372: l_pa_request_id number(15);
3373: begin
3374: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
3375:
3376: l_return_value := null;
3377: if g_aw_req.exists(p_assignment_id) then
3378: l_pa_request_id := g_aw_req(p_assignment_id).pa_request_id;
3377: if g_aw_req.exists(p_assignment_id) then
3378: l_pa_request_id := g_aw_req(p_assignment_id).pa_request_id;
3379: end if;
3380:
3381: Hr_Utility.set_location(' l_pa_request_id: '||l_pa_request_id, 6);
3382: Hr_Utility.set_location(' p_input_value: '||p_input_value, 6);
3383:
3384:
3385: if (l_pa_request_id is not null and
3378: l_pa_request_id := g_aw_req(p_assignment_id).pa_request_id;
3379: end if;
3380:
3381: Hr_Utility.set_location(' l_pa_request_id: '||l_pa_request_id, 6);
3382: Hr_Utility.set_location(' p_input_value: '||p_input_value, 6);
3383:
3384:
3385: if (l_pa_request_id is not null and
3386: g_awd_rec.exists(l_pa_request_id))then
3556: exception
3557: when others then
3558: p_error_code := sqlcode;
3559: p_error_message := p_input_value;--sqlerrm;
3560: Hr_Utility.set_location('SQLERRM: '||sqlerrm, 90);
3561: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
3562: return l_return_value;
3563:
3564: end Get_Award_Data;
3557: when others then
3558: p_error_code := sqlcode;
3559: p_error_message := p_input_value;--sqlerrm;
3560: Hr_Utility.set_location('SQLERRM: '||sqlerrm, 90);
3561: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
3562: return l_return_value;
3563:
3564: end Get_Award_Data;
3565: -- =============================================================================
3576: l_address_id number;
3577: l_proc_name constant varchar2(250) := g_proc_name ||'Get_Address_Data';
3578:
3579: begin
3580: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
3581: l_return_value := null;
3582: if g_address_rec.exists(p_assignment_id) then
3583: l_address_id := g_address_rec(p_assignment_id).address_id;
3584: end if;
3584: end if;
3585:
3586:
3587: if g_debug then
3588: Hr_Utility.set_location(' p_input_value: '||p_input_value, 6);
3589: Hr_Utility.set_location(' l_address_id: '||l_address_id, 6);
3590: end if;
3591:
3592: Hr_Utility.set_location(' p_input_value: '||p_input_value, 6);
3585:
3586:
3587: if g_debug then
3588: Hr_Utility.set_location(' p_input_value: '||p_input_value, 6);
3589: Hr_Utility.set_location(' l_address_id: '||l_address_id, 6);
3590: end if;
3591:
3592: Hr_Utility.set_location(' p_input_value: '||p_input_value, 6);
3593: Hr_Utility.set_location(' l_address_id: '||l_address_id, 6);
3588: Hr_Utility.set_location(' p_input_value: '||p_input_value, 6);
3589: Hr_Utility.set_location(' l_address_id: '||l_address_id, 6);
3590: end if;
3591:
3592: Hr_Utility.set_location(' p_input_value: '||p_input_value, 6);
3593: Hr_Utility.set_location(' l_address_id: '||l_address_id, 6);
3594:
3595: if (l_address_id is not null )then
3596:
3589: Hr_Utility.set_location(' l_address_id: '||l_address_id, 6);
3590: end if;
3591:
3592: Hr_Utility.set_location(' p_input_value: '||p_input_value, 6);
3593: Hr_Utility.set_location(' l_address_id: '||l_address_id, 6);
3594:
3595: if (l_address_id is not null )then
3596:
3597: if p_input_value ='ADD_EFF_DATE' then
3639: --END IF;
3640:
3641: elsif p_input_value ='ADD_AGNCY_CD' then
3642:
3643: Hr_Utility.set_location(' Inside Agency CD: '||p_input_value, 6);
3644:
3645: l_return_value :=g_rpa_add_attr(p_assignment_id).nfc_agency_code;
3646:
3647: Hr_Utility.set_location(' l_return_value: '||l_return_value, 6);
3643: Hr_Utility.set_location(' Inside Agency CD: '||p_input_value, 6);
3644:
3645: l_return_value :=g_rpa_add_attr(p_assignment_id).nfc_agency_code;
3646:
3647: Hr_Utility.set_location(' l_return_value: '||l_return_value, 6);
3648:
3649: elsif p_input_value ='ADD_POI' then
3650:
3651: l_return_value := g_rpa_add_attr(p_assignment_id).poi;
3708:
3709: end if;
3710:
3711: if g_debug then
3712: Hr_Utility.set_location(' l_return_value: '||l_return_value, 79);
3713: Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
3714: end if;
3715: Hr_Utility.set_location(' l_return_value: '||l_return_value, 79);
3716: Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
3709: end if;
3710:
3711: if g_debug then
3712: Hr_Utility.set_location(' l_return_value: '||l_return_value, 79);
3713: Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
3714: end if;
3715: Hr_Utility.set_location(' l_return_value: '||l_return_value, 79);
3716: Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
3717:
3711: if g_debug then
3712: Hr_Utility.set_location(' l_return_value: '||l_return_value, 79);
3713: Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
3714: end if;
3715: Hr_Utility.set_location(' l_return_value: '||l_return_value, 79);
3716: Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
3717:
3718:
3719: return l_return_value;
3712: Hr_Utility.set_location(' l_return_value: '||l_return_value, 79);
3713: Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
3714: end if;
3715: Hr_Utility.set_location(' l_return_value: '||l_return_value, 79);
3716: Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
3717:
3718:
3719: return l_return_value;
3720:
3721: exception
3722: when others then
3723: p_error_code := sqlcode;
3724: p_error_message := p_input_value;--sqlerrm;
3725: Hr_Utility.set_location('SQLERRM: '||sqlerrm, 90);
3726: Hr_Utility.set_location('error Leaving: '||l_proc_name, 90);
3727: return l_return_value;
3728:
3729: end Get_Address_Data;
3722: when others then
3723: p_error_code := sqlcode;
3724: p_error_message := p_input_value;--sqlerrm;
3725: Hr_Utility.set_location('SQLERRM: '||sqlerrm, 90);
3726: Hr_Utility.set_location('error Leaving: '||l_proc_name, 90);
3727: return l_return_value;
3728:
3729: end Get_Address_Data;
3730: -- =============================================================================
3745: l_elmt_value varchar2(2000);
3746: l_proc_name constant varchar2(150) := g_proc_name ||'Get_Elmt_Val';
3747:
3748: begin
3749: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
3750: l_sel_stmt := 'select '||p_col_name ||
3751: ' from ben_ext_rslt_dtl
3752: where ext_rslt_id = :1
3753: and ext_rslt_dtl_id = :2
3760: ,p_rcd_id
3761: ,p_person_id;
3762: fetch ele_cur into l_elmt_value;
3763: close ele_cur;
3764: Hr_Utility.set_location(' p_col_name : '||p_col_name, 79);
3765: Hr_Utility.set_location(' l_elmt_value: '||l_elmt_value, 79);
3766: Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
3767: return l_elmt_value;
3768:
3761: ,p_person_id;
3762: fetch ele_cur into l_elmt_value;
3763: close ele_cur;
3764: Hr_Utility.set_location(' p_col_name : '||p_col_name, 79);
3765: Hr_Utility.set_location(' l_elmt_value: '||l_elmt_value, 79);
3766: Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
3767: return l_elmt_value;
3768:
3769: end Get_Elmt_Val;
3762: fetch ele_cur into l_elmt_value;
3763: close ele_cur;
3764: Hr_Utility.set_location(' p_col_name : '||p_col_name, 79);
3765: Hr_Utility.set_location(' l_elmt_value: '||l_elmt_value, 79);
3766: Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
3767: return l_elmt_value;
3768:
3769: end Get_Elmt_Val;
3770: -- =============================================================================
3793: l_seqnum_rec csr_seqnum%rowtype;
3794: l_proc_name varchar2(150):= g_proc_name||'Update_Record_Values';
3795: l_ext_dtl_rec_nc ben_ext_rslt_dtl%rowtype;
3796: begin
3797: Hr_Utility.set_location('Entering :'||l_proc_name, 5);
3798: -- nocopy changes
3799: l_ext_dtl_rec_nc := p_ext_dtl_rec;
3800:
3801: if p_data_ele_seqnum is null then
4114: elsif l_seqnum_rec.seq_num = 150 then
4115: p_ext_dtl_rec.val_150 := p_data_element_value;
4116: end if;
4117:
4118: Hr_Utility.set_location('Leaving :'||l_proc_name, 25);
4119: return;
4120: exception
4121: when Others then
4122: -- nocopy changes
4132: ,p_val_tab in out NOCOPY ValTabTyp) is
4133:
4134: l_proc_name varchar2(150) := g_proc_name ||'Copy_Rec_Values ';
4135: begin
4136: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
4137:
4138: p_val_tab(1) := p_rslt_rec.val_01;
4139: p_val_tab(2) := p_rslt_rec.val_02;
4140: p_val_tab(3) := p_rslt_rec.val_03;
4307: p_val_tab(148) := p_rslt_rec.val_148;
4308: p_val_tab(149) := p_rslt_rec.val_149;
4309: p_val_tab(150) := p_rslt_rec.val_150;
4310:
4311: Hr_Utility.set_location('Leaving: '||l_proc_name, 15);
4312:
4313: end Copy_Rec_Values;
4314: -- =============================================================================
4315: -- Data_Elmt_In_Rcd:
4350: l_dynamic_condition varchar2(9999);
4351: --
4352: l_val_tab_mirror ValTabTyp;
4353: begin
4354: Hr_Utility.set_location('Entering'||l_proc, 5);
4355: p_exclude_this_rcd_flag := false;
4356: p_raise_warning := false;
4357: p_rollback_person := false;
4358: -- Make mirror image of table for evaluation, since values in
4456: end if;-- IF l_cnt > 0 THEN
4457: --
4458: end loop; -- FOR xer IN c_xer
4459: --
4460: Hr_Utility.set_location('Exiting'||l_proc, 15);
4461: --
4462: end Data_Elmt_In_Rcd;
4463:
4464: -- =============================================================================
4504: --
4505: --
4506: begin
4507: --
4508: Hr_Utility.set_location('Entering'||l_proc, 5);
4509: --
4510: p_exclude_this_rcd_flag := false;
4511: p_raise_warning := false;
4512: p_rollback_person := false;
4643: end if; -- IF p_sprs_cd = 'D'
4644: --
4645: end if; -- IF l_condition = 'T'
4646: --
4647: Hr_Utility.set_location('Exiting'||l_proc, 15);
4648: --
4649: end Rcd_In_File;
4650: -- =============================================================================
4651: -- ~ Ins_Rslt_Dtl : Inserts a record into the results detail record.
4659: l_proc_name varchar2(150) := g_proc_name||'Ins_Rslt_Dtl';
4660: l_dtl_rec_nc ben_ext_rslt_dtl%rowtype;
4661:
4662: begin -- ins_rslt_dtl
4663: Hr_Utility.set_location('Entering :'||l_proc_name, 5);
4664: -- nocopy changes
4665: l_dtl_rec_nc := p_dtl_rec;
4666: -- Get the next sequence NUMBER to insert a record into the table
4667: select ben_ext_rslt_dtl_s.nextval into p_dtl_rec.ext_rslt_dtl_id from dual;
5008: ,p_dtl_rec.thrd_sort_val
5009: ,p_dtl_rec.trans_seq_num
5010: ,p_dtl_rec.rcrd_seq_num
5011: );
5012: Hr_Utility.set_location('Leaving :'||l_proc_name, 25);
5013: return;
5014:
5015: exception
5016: when Others then
5013: return;
5014:
5015: exception
5016: when Others then
5017: Hr_Utility.set_location('Leaving :'||l_proc_name, 25);
5018: p_dtl_rec := l_dtl_rec_nc;
5019: raise;
5020: end Ins_Rslt_Dtl;
5021: -- =============================================================================
5264: l_rollback_person boolean;
5265: l_rslt_dtl_id number;
5266: --
5267: begin
5268: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
5269: for i in 1..75
5270: loop
5271: l_val_tab(i) := null;
5272: end loop;
5303: begin
5304: if i.frmt_mask_lookup_cd is not null and
5305: l_ff_value is not null then
5306: if Substr(i.frmt_mask_lookup_cd,1,1) = 'N' then
5307: Hr_Utility.set_location('..Applying NUMBER format mask :ben_ext_fmt.apply_format_mask',50);
5308: l_ff_value_fmt := Ben_Ext_Fmt.apply_format_mask(To_Number(l_ff_value), i.frmt_mask_cd);
5309: l_ff_value := l_ff_value_fmt;
5310: elsif Substr(i.frmt_mask_lookup_cd,1,1) = 'D' then
5311: Hr_Utility.set_location('..Applying Date format mask :ben_ext_fmt.apply_format_mask',55);
5307: Hr_Utility.set_location('..Applying NUMBER format mask :ben_ext_fmt.apply_format_mask',50);
5308: l_ff_value_fmt := Ben_Ext_Fmt.apply_format_mask(To_Number(l_ff_value), i.frmt_mask_cd);
5309: l_ff_value := l_ff_value_fmt;
5310: elsif Substr(i.frmt_mask_lookup_cd,1,1) = 'D' then
5311: Hr_Utility.set_location('..Applying Date format mask :ben_ext_fmt.apply_format_mask',55);
5312: l_ff_value_fmt := Ben_Ext_Fmt.apply_format_mask(Fnd_Date.canonical_to_date(l_ff_value),
5313: i.frmt_mask_cd);
5314: l_ff_value := l_ff_value_fmt;
5315: end if;
5326: l_ff_value := Substr(l_ff_value, -l_max_len);
5327: else -- everything else truncs from the right.
5328: l_ff_value := Substr(l_ff_value, 1, i.max_length_num);
5329: end if;
5330: Hr_Utility.set_location('..After Max Length : '|| l_ff_value,56 );
5331: end if;
5332: -- If the data element is required, and null then exit
5333: -- no need to re-execute the other data-elements in the record.
5334: if i.rqd_flag = 'Y' and (l_ff_value is null) then
5405: ,p_benefit_action_id => l_ben_params.benefit_action_id
5406: ,p_flag_thread => 'N');
5407:
5408: end if;
5409: Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
5410:
5411: exception
5412: when Others then
5413: p_error_message :='SQL-ERRM :'||SQLERRM;
5410:
5411: exception
5412: when Others then
5413: p_error_message :='SQL-ERRM :'||SQLERRM;
5414: Hr_Utility.set_location('..'||p_error_message,85);
5415: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
5416:
5417: end Process_Ext_Rslt_Dtl_Rec;
5418: -- =============================================================================
5411: exception
5412: when Others then
5413: p_error_message :='SQL-ERRM :'||SQLERRM;
5414: Hr_Utility.set_location('..'||p_error_message,85);
5415: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
5416:
5417: end Process_Ext_Rslt_Dtl_Rec;
5418: -- =============================================================================
5419: -- ~ Process_Person_RPAs:
5434: l_proc_name varchar2(150) := g_proc_name ||'Process_Person_RPAs';
5435: l_total_lines number(15);
5436: l_processed_rpa_id number(15);
5437: begin
5438: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
5439: open csr_rslt_dtl
5440: (c_person_id => p_person_id
5441: ,c_ext_rslt_id => Ben_Ext_Thread.g_ext_rslt_id
5442: ,c_ext_dtl_rcd_id => p_ext_rcd_id);
5442: ,c_ext_dtl_rcd_id => p_ext_rcd_id);
5443: fetch csr_rslt_dtl into l_rslt_dtl_rec;
5444: close csr_rslt_dtl;
5445: l_no_per_rpas := g_rpa_rec.count;
5446: Hr_Utility.set_location('No Per RPAs: '||l_no_per_rpas, 7);
5447:
5448: if g_pa_req.exists(p_assignment_id) then
5449: l_processed_rpa_id := g_pa_req(p_assignment_id).pa_request_id;
5450: end if;
5447:
5448: if g_pa_req.exists(p_assignment_id) then
5449: l_processed_rpa_id := g_pa_req(p_assignment_id).pa_request_id;
5450: end if;
5451: Hr_Utility.set_location('l_processed_rpa_id : '||l_processed_rpa_id, 9);
5452:
5453: if l_no_per_rpas = 0 then
5454: Hr_Utility.set_location('l_processed_rpa_id : '||l_processed_rpa_id, 9);
5455: delete from ben_ext_rslt_dtl dtl
5450: end if;
5451: Hr_Utility.set_location('l_processed_rpa_id : '||l_processed_rpa_id, 9);
5452:
5453: if l_no_per_rpas = 0 then
5454: Hr_Utility.set_location('l_processed_rpa_id : '||l_processed_rpa_id, 9);
5455: delete from ben_ext_rslt_dtl dtl
5456: where dtl.ext_rslt_dtl_id = l_rslt_dtl_rec.ext_rslt_dtl_id;
5457:
5458: elsif l_no_per_rpas > 1 then
5459:
5460: l_total_lines := 1;
5461: i := p_assignment_id;
5462: j := g_rpa_rec.first;
5463: Hr_Utility.set_location('PA Request Id : '||j, 10);
5464: while j is not null
5465: loop
5466: if l_processed_rpa_id <> j then
5467: g_pa_req(i).person_id := g_rpa_rec(j).person_id;
5472: g_pa_req(i).pa_notification_id:= g_rpa_rec(j).pa_notification_id;
5473: g_pa_req(i).first_noa_code := g_rpa_rec(j).first_noa_code;
5474: g_pa_req(i).second_noa_code := g_rpa_rec(j).second_noa_code;
5475: g_pa_req(i).no_of_rpa := g_pa_req(i).no_of_rpa + 1;
5476: Hr_Utility.set_location('Calling Process_Ext_Rslt_Dtl_Rec... ', 11);
5477: Process_Ext_Rslt_Dtl_Rec
5478: (p_assignment_id => p_assignment_id
5479: ,p_organization_id => null
5480: ,p_effective_date => p_effective_date
5485: ,p_error_message => p_error_message);
5486: end if;
5487: l_prv_rpa_id := j;
5488: j := g_rpa_rec.next(l_prv_rpa_id);
5489: Hr_Utility.set_location('PA Request Id : '||j, 12);
5490: end loop; -- while loop
5491: end if;
5492: Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
5493: exception
5488: j := g_rpa_rec.next(l_prv_rpa_id);
5489: Hr_Utility.set_location('PA Request Id : '||j, 12);
5490: end loop; -- while loop
5491: end if;
5492: Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
5493: exception
5494: when others then
5495:
5496: p_error_code := sqlcode;
5494: when others then
5495:
5496: p_error_code := sqlcode;
5497: p_error_message := NULL;
5498: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
5499: end Process_Person_RPAs;
5500: -- =============================================================================
5501: -- ~ Process_Address_RPA:
5502: -- =============================================================================
5514: l_processed_add_id number(15);
5515:
5516: begin
5517:
5518: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
5519: open csr_rslt_dtl
5520: (c_person_id => p_person_id
5521: ,c_ext_rslt_id => Ben_Ext_Thread.g_ext_rslt_id
5522: ,c_ext_dtl_rcd_id => p_ext_rcd_id);
5522: ,c_ext_dtl_rcd_id => p_ext_rcd_id);
5523: fetch csr_rslt_dtl into l_rslt_dtl_rec;
5524: close csr_rslt_dtl;
5525: l_no_add_rpas := g_address_rec.count;
5526: Hr_Utility.set_location('No Address RPAs: '||l_no_add_rpas, 7);
5527: --
5528: if g_address_rec.exists(p_assignment_id) then
5529: l_processed_add_id := g_address_rec(p_assignment_id).address_id;
5530: end if;
5535: where dtl.ext_rslt_dtl_id = l_rslt_dtl_rec.ext_rslt_dtl_id;
5536:
5537: end if;
5538:
5539: Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
5540:
5541: exception
5542: when others then
5543: p_error_code := sqlcode;
5541: exception
5542: when others then
5543: p_error_code := sqlcode;
5544: p_error_message := NULL;
5545: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
5546: end Process_Address_RPA;
5547:
5548: -- =============================================================================
5549: -- ~ Process_Award_RPAs:
5564: l_proc_name varchar2(150) := g_proc_name ||'Process_Award_RPAs';
5565: l_total_lines number(15);
5566: l_processed_awd_id number(15);
5567: begin
5568: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
5569: open csr_rslt_dtl
5570: (c_person_id => p_person_id
5571: ,c_ext_rslt_id => Ben_Ext_Thread.g_ext_rslt_id
5572: ,c_ext_dtl_rcd_id => p_ext_rcd_id);
5572: ,c_ext_dtl_rcd_id => p_ext_rcd_id);
5573: fetch csr_rslt_dtl into l_rslt_dtl_rec;
5574: close csr_rslt_dtl;
5575: l_no_per_awds := g_awd_rec.count;
5576: Hr_Utility.set_location(' No Per Awards: '||l_no_per_awds, 7);
5577:
5578: if g_aw_req.exists(p_assignment_id) then
5579: l_processed_awd_id := g_aw_req(p_assignment_id).pa_request_id;
5580: end if;
5577:
5578: if g_aw_req.exists(p_assignment_id) then
5579: l_processed_awd_id := g_aw_req(p_assignment_id).pa_request_id;
5580: end if;
5581: Hr_Utility.set_location(' l_processed_awd_id : '||l_processed_awd_id, 9);
5582: if l_no_per_awds = 0 then
5583: Hr_Utility.set_location(' l_processed_awd_id : '||l_processed_awd_id, 9);
5584:
5585: delete from ben_ext_rslt_dtl dtl
5579: l_processed_awd_id := g_aw_req(p_assignment_id).pa_request_id;
5580: end if;
5581: Hr_Utility.set_location(' l_processed_awd_id : '||l_processed_awd_id, 9);
5582: if l_no_per_awds = 0 then
5583: Hr_Utility.set_location(' l_processed_awd_id : '||l_processed_awd_id, 9);
5584:
5585: delete from ben_ext_rslt_dtl dtl
5586: where dtl.ext_rslt_dtl_id = l_rslt_dtl_rec.ext_rslt_dtl_id;
5587:
5589:
5590: l_total_lines := 1;
5591: i := p_assignment_id;
5592: j := g_awd_rec.first;
5593: Hr_Utility.set_location(' Award Request Id : '||j, 10);
5594: while j is not null
5595: loop
5596: if l_processed_awd_id <> j then
5597: g_aw_req(i).person_id := g_awd_rec(j).person_id;
5602: g_aw_req(i).pa_notification_id:= g_awd_rec(j).pa_notification_id;
5603: g_aw_req(i).first_noa_code := g_awd_rec(j).first_noa_code;
5604: g_aw_req(i).second_noa_code := g_awd_rec(j).second_noa_code;
5605: g_aw_req(i).no_of_rpa := g_aw_req(i).no_of_rpa + 1;
5606: Hr_Utility.set_location('Calling Process_Ext_Rslt_Dtl_Rec... ', 11);
5607: Process_Ext_Rslt_Dtl_Rec
5608: (p_assignment_id => p_assignment_id
5609: ,p_organization_id => null
5610: ,p_effective_date => p_effective_date
5615: ,p_error_message => p_error_message);
5616: end if;
5617: l_prv_awd_id := j;
5618: j := g_awd_rec.next(l_prv_awd_id);
5619: Hr_Utility.set_location('PA Request Id : '||j, 12);
5620: end loop; -- while loop
5621: end if;
5622: Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
5623: exception
5618: j := g_awd_rec.next(l_prv_awd_id);
5619: Hr_Utility.set_location('PA Request Id : '||j, 12);
5620: end loop; -- while loop
5621: end if;
5622: Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
5623: exception
5624: when others then
5625:
5626: p_error_code := sqlcode;
5624: when others then
5625:
5626: p_error_code := sqlcode;
5627: p_error_message := NULL;
5628: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
5629: end Process_Award_RPAs;
5630:
5631: -- =============================================================================
5632: -- ~ Process_Remark_RPAs:
5652: l_processed_rmk_id NUMBER;
5653: l_processed_rmk_cd NUMBER;
5654: l_processed_awd_id NUMBER;
5655: begin
5656: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
5657: open csr_rslt_dtl
5658: (c_person_id => p_person_id
5659: ,c_ext_rslt_id => Ben_Ext_Thread.g_ext_rslt_id
5660: ,c_ext_dtl_rcd_id => p_ext_rcd_id);
5663: l_no_per_rpas := g_rpa_rec.COUNT;
5664: l_no_rpa_rems := g_pa_req_remark.COUNT;
5665: l_no_awd_rpas := g_awd_rec.COUNT;
5666:
5667: Hr_Utility.set_location('No Per RPAs: '||l_no_per_rpas, 7);
5668: Hr_Utility.set_location('No RPAs Remarks: '||l_no_rpa_rems, 7);
5669: Hr_Utility.set_location('No Award rpas: '||l_no_awd_rpas, 7);
5670:
5671: if g_pa_req.exists(p_assignment_id) then
5664: l_no_rpa_rems := g_pa_req_remark.COUNT;
5665: l_no_awd_rpas := g_awd_rec.COUNT;
5666:
5667: Hr_Utility.set_location('No Per RPAs: '||l_no_per_rpas, 7);
5668: Hr_Utility.set_location('No RPAs Remarks: '||l_no_rpa_rems, 7);
5669: Hr_Utility.set_location('No Award rpas: '||l_no_awd_rpas, 7);
5670:
5671: if g_pa_req.exists(p_assignment_id) then
5672: l_processed_rpa_id := g_pa_req(p_assignment_id).pa_request_id;
5665: l_no_awd_rpas := g_awd_rec.COUNT;
5666:
5667: Hr_Utility.set_location('No Per RPAs: '||l_no_per_rpas, 7);
5668: Hr_Utility.set_location('No RPAs Remarks: '||l_no_rpa_rems, 7);
5669: Hr_Utility.set_location('No Award rpas: '||l_no_awd_rpas, 7);
5670:
5671: if g_pa_req.exists(p_assignment_id) then
5672: l_processed_rpa_id := g_pa_req(p_assignment_id).pa_request_id;
5673: end if;
5670:
5671: if g_pa_req.exists(p_assignment_id) then
5672: l_processed_rpa_id := g_pa_req(p_assignment_id).pa_request_id;
5673: end if;
5674: Hr_Utility.set_location('l_processed_rpa_id : '||l_processed_rpa_id, 9);
5675:
5676: IF g_aw_req.exists(p_assignment_id) THEN
5677: l_processed_awd_id := g_aw_req(p_assignment_id).pa_request_id;
5678: END IF;
5677: l_processed_awd_id := g_aw_req(p_assignment_id).pa_request_id;
5678: END IF;
5679:
5680: if (l_no_per_rpas = 0 AND l_no_awd_rpas= 0) or l_no_rpa_rems = 0 then
5681: Hr_Utility.set_location('l_processed_rpa_id : '||l_processed_rpa_id, 9);
5682: delete from ben_ext_rslt_dtl dtl
5683: where dtl.ext_rslt_dtl_id = l_rslt_dtl_rec.ext_rslt_dtl_id;
5684: elsif l_no_per_rpas >= 1 then
5685: l_total_lines := 1;
5688: j := g_rpa_rec.first;
5689: l_processed_rmk_id := g_pa_req(i).pa_remark_id;
5690: l_processed_rmk_cd := g_pa_req(i).remark_code;
5691:
5692: Hr_Utility.set_location('PA Request Id : '||j, 10);
5693: Hr_Utility.set_location('l_processed_rmk_id : '||l_processed_rmk_id, 10);
5694:
5695: while j is not null
5696: loop
5689: l_processed_rmk_id := g_pa_req(i).pa_remark_id;
5690: l_processed_rmk_cd := g_pa_req(i).remark_code;
5691:
5692: Hr_Utility.set_location('PA Request Id : '||j, 10);
5693: Hr_Utility.set_location('l_processed_rmk_id : '||l_processed_rmk_id, 10);
5694:
5695: while j is not null
5696: loop
5697: if l_processed_rpa_id <> j and
5715:
5716: g_pa_req(i).remark_code := rem_rec.remark_id;
5717: g_pa_req(i).pa_remark_id := rem_rec.pa_remark_id;
5718:
5719: Hr_Utility.set_location('Calling Process_Ext_Rslt_Dtl_Rec... ', 11);
5720: Process_Ext_Rslt_Dtl_Rec
5721: (p_assignment_id => p_assignment_id
5722: ,p_organization_id => null
5723: ,p_effective_date => p_effective_date
5749:
5750: g_pa_req(i).remark_code := rem_rec.remark_id;
5751: g_pa_req(i).pa_remark_id := rem_rec.pa_remark_id;
5752:
5753: Hr_Utility.set_location('Calling Process_Ext_Rslt_Dtl_Rec... ', 11);
5754: Process_Ext_Rslt_Dtl_Rec
5755: (p_assignment_id => p_assignment_id
5756: ,p_organization_id => null
5757: ,p_effective_date => p_effective_date
5767: end if;
5768:
5769: l_prv_rpa_id := j;
5770: j := g_rpa_rec.next(l_prv_rpa_id);
5771: Hr_Utility.set_location('PA Request Id : '||j, 12);
5772: end loop; -- while loop
5773: -- Code added for Remarks
5774: ELSIF l_no_awd_rpas >=1 THEN
5775: l_total_lines := 1;
5778: j := g_awd_rec.first;
5779: l_processed_rmk_id := g_aw_req(i).pa_remark_id;
5780: l_processed_rmk_cd := g_aw_req(i).remark_code;
5781:
5782: Hr_Utility.set_location('PA Request Id : '||j, 10);
5783: Hr_Utility.set_location('l_processed_rmk_id : '||l_processed_rmk_id, 10);
5784:
5785: while j is not null
5786: loop
5779: l_processed_rmk_id := g_aw_req(i).pa_remark_id;
5780: l_processed_rmk_cd := g_aw_req(i).remark_code;
5781:
5782: Hr_Utility.set_location('PA Request Id : '||j, 10);
5783: Hr_Utility.set_location('l_processed_rmk_id : '||l_processed_rmk_id, 10);
5784:
5785: while j is not null
5786: loop
5787: if l_processed_awd_id <> j and
5805:
5806: g_aw_req(i).remark_code := rem_rec.remark_id;
5807: g_aw_req(i).pa_remark_id := rem_rec.pa_remark_id;
5808:
5809: Hr_Utility.set_location('Calling Process_Ext_Rslt_Dtl_Rec... ', 11);
5810: Process_Ext_Rslt_Dtl_Rec
5811: (p_assignment_id => p_assignment_id
5812: ,p_organization_id => null
5813: ,p_effective_date => p_effective_date
5839:
5840: g_aw_req(i).remark_code := rem_rec.remark_id;
5841: g_aw_req(i).pa_remark_id := rem_rec.pa_remark_id;
5842:
5843: Hr_Utility.set_location('Calling Process_Ext_Rslt_Dtl_Rec... ', 11);
5844: Process_Ext_Rslt_Dtl_Rec
5845: (p_assignment_id => p_assignment_id
5846: ,p_organization_id => null
5847: ,p_effective_date => p_effective_date
5857: end if;
5858:
5859: l_prv_rpa_id := j;
5860: j := g_awd_rec.next(l_prv_rpa_id);
5861: Hr_Utility.set_location('PA Request Id : '||j, 12);
5862: end loop; -- while loop
5863:
5864: END IF; -- if l_no_per_rpas = 0
5865: Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
5861: Hr_Utility.set_location('PA Request Id : '||j, 12);
5862: end loop; -- while loop
5863:
5864: END IF; -- if l_no_per_rpas = 0
5865: Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
5866:
5867: exception
5868: when others then
5869: p_error_code := sqlcode;
5867: exception
5868: when others then
5869: p_error_code := sqlcode;
5870: p_error_message := NULL;
5871: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
5872: end Process_Remark_RPAs;
5873:
5874: -- =============================================================================
5875: -- ~ Process_Multiple_RPAs
5887: l_prv_ext_rcd_id number(15);
5888: l_rpa_type varchar2(150);
5889: l_return_value varchar2(2000);
5890: begin
5891: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
5892: l_return_value := '0';
5893: l_ext_rcd_id := g_ext_rcd.first;
5894: Hr_Utility.set_location('l_ext_rcd_id: '||l_ext_rcd_id, 6);
5895: while l_ext_rcd_id is not null
5890: begin
5891: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
5892: l_return_value := '0';
5893: l_ext_rcd_id := g_ext_rcd.first;
5894: Hr_Utility.set_location('l_ext_rcd_id: '||l_ext_rcd_id, 6);
5895: while l_ext_rcd_id is not null
5896: loop
5897: if g_ext_rcd.exists(l_ext_rcd_id) then
5898: l_rpa_type := g_ext_rcd(l_ext_rcd_id).data_value;
5896: loop
5897: if g_ext_rcd.exists(l_ext_rcd_id) then
5898: l_rpa_type := g_ext_rcd(l_ext_rcd_id).data_value;
5899: end if;
5900: Hr_Utility.set_location('l_rpa_type: '||l_rpa_type, 7);
5901: if l_rpa_type ='RPA_REQ_ID' then
5902: Process_Person_RPAs
5903: (p_ext_rcd_id => l_ext_rcd_id
5904: ,p_assignment_id => p_assignment_id
5936: );
5937: end if;
5938: l_prv_ext_rcd_id := l_ext_rcd_id;
5939: l_ext_rcd_id := g_ext_rcd.next(l_prv_ext_rcd_id);
5940: Hr_Utility.set_location('l_ext_rcd_id: '||l_ext_rcd_id,10);
5941: end loop; -- while loop
5942: for rcd_rec in csr_ext_rcd
5943: (c_hide_flag => 'Y'
5944: ,c_rcd_type_cd => 'D')
5948: where dtl.ext_rslt_id = Ben_Ext_Thread.g_ext_rslt_id
5949: and dtl.ext_rcd_id = rcd_rec.ext_rcd_id
5950: and dtl.business_group_id = g_business_group_id;
5951: end loop;
5952: Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
5953: return l_return_value;
5954: exception
5955: when Others then
5956: l_return_value := '-1';
5955: when Others then
5956: l_return_value := '-1';
5957: p_error_code := sqlcode;
5958: p_error_message :=' SQL-ERRM :'||NULL;
5959: Hr_Utility.set_location(p_error_message,85);
5960: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
5961: return l_return_value;
5962: end Process_Multiple_RPAs;
5963:
5956: l_return_value := '-1';
5957: p_error_code := sqlcode;
5958: p_error_message :=' SQL-ERRM :'||NULL;
5959: Hr_Utility.set_location(p_error_message,85);
5960: Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
5961: return l_return_value;
5962: end Process_Multiple_RPAs;
5963:
5964: -- =============================================================================
5979: l_pa_request_id number;
5980: l_assignment_id number;
5981: begin
5982:
5983: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
5984: Hr_Utility.set_location(' p_input_value: '||p_input_value, 5);
5985: Hr_Utility.set_location(' p_assignment_id: '||p_assignment_id, 5);
5986: Hr_Utility.set_location(' g_assignment_id: '||g_assignment_id, 5);
5987: -- Sundar Changes Bug 4629647
5980: l_assignment_id number;
5981: begin
5982:
5983: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
5984: Hr_Utility.set_location(' p_input_value: '||p_input_value, 5);
5985: Hr_Utility.set_location(' p_assignment_id: '||p_assignment_id, 5);
5986: Hr_Utility.set_location(' g_assignment_id: '||g_assignment_id, 5);
5987: -- Sundar Changes Bug 4629647
5988: IF p_assignment_id IS NULL OR p_assignment_id = -1 THEN
5981: begin
5982:
5983: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
5984: Hr_Utility.set_location(' p_input_value: '||p_input_value, 5);
5985: Hr_Utility.set_location(' p_assignment_id: '||p_assignment_id, 5);
5986: Hr_Utility.set_location(' g_assignment_id: '||g_assignment_id, 5);
5987: -- Sundar Changes Bug 4629647
5988: IF p_assignment_id IS NULL OR p_assignment_id = -1 THEN
5989: l_assignment_id := g_assignment_id;
5982:
5983: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
5984: Hr_Utility.set_location(' p_input_value: '||p_input_value, 5);
5985: Hr_Utility.set_location(' p_assignment_id: '||p_assignment_id, 5);
5986: Hr_Utility.set_location(' g_assignment_id: '||g_assignment_id, 5);
5987: -- Sundar Changes Bug 4629647
5988: IF p_assignment_id IS NULL OR p_assignment_id = -1 THEN
5989: l_assignment_id := g_assignment_id;
5990: ELSE
6076: ,p_error_code => p_error_code
6077: ,p_error_message => p_error_message
6078: );
6079: end if;
6080: Hr_Utility.set_location(' l_return_value: '||l_return_value, 79);
6081: Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
6082: if p_error_code <> '0' then
6083: p_msg_type := 'W';
6084: end if;
6077: ,p_error_message => p_error_message
6078: );
6079: end if;
6080: Hr_Utility.set_location(' l_return_value: '||l_return_value, 79);
6081: Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
6082: if p_error_code <> '0' then
6083: p_msg_type := 'W';
6084: end if;
6085: return l_return_value;
6086: exception
6087: when Others then
6088: p_error_message := NULL; p_msg_type := 'E';
6089: p_error_code := sqlcode; l_return_value := '-1';
6090: Hr_Utility.set_location('error l_return_value: '||l_return_value, 89);
6091: Hr_Utility.set_location('error Leaving: '||l_proc_name, 90);
6092: return l_return_value;
6093: end Evaluate_Formula;
6094:
6087: when Others then
6088: p_error_message := NULL; p_msg_type := 'E';
6089: p_error_code := sqlcode; l_return_value := '-1';
6090: Hr_Utility.set_location('error l_return_value: '||l_return_value, 89);
6091: Hr_Utility.set_location('error Leaving: '||l_proc_name, 90);
6092: return l_return_value;
6093: end Evaluate_Formula;
6094:
6095: -- =============================================================================
6155: l_proc_name constant varchar2(250) := g_proc_name ||'Evaluate_Person_Inclusion';
6156:
6157: begin
6158:
6159: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
6160: g_debug := Hr_Utility.debug_enabled;
6161: g_assignment_id := -1;
6162: -- ===================================================================
6163: -- Initialize all local variables
6156:
6157: begin
6158:
6159: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
6160: g_debug := Hr_Utility.debug_enabled;
6161: g_assignment_id := -1;
6162: -- ===================================================================
6163: -- Initialize all local variables
6164: -- ===================================================================
6175: g_business_group_id := p_business_group_id;
6176: g_assignment_id := p_assignment_id; -- Sundar changes.
6177:
6178:
6179: Hr_Utility.set_location('p_business_group_id: '||j, 5);
6180:
6181: IF NOT g_extract_params.EXISTS(i) THEN
6182: -- Get the Conc. request id to get the params
6183: OPEN csr_org_req(c_ext_rslt_id => l_ext_rslt_id
6185: ,c_business_group_id => p_business_group_id);
6186: FETCH csr_org_req INTO l_conc_reqest_id;
6187: CLOSE csr_org_req;
6188:
6189: Hr_Utility.set_location('l_conc_reqest_id: '||l_conc_reqest_id, 5);
6190: -- Get the params. based on the conc. request id.
6191: OPEN csr_req_params(c_req_id => l_conc_reqest_id);
6192: FETCH csr_req_params INTO l_req_params;
6193: CLOSE csr_req_params;
6190: -- Get the params. based on the conc. request id.
6191: OPEN csr_req_params(c_req_id => l_conc_reqest_id);
6192: FETCH csr_req_params INTO l_req_params;
6193: CLOSE csr_req_params;
6194: Hr_Utility.set_location('..Extract params', 15);
6195:
6196: -- Store the params. in a PL/SQL table record
6197: g_extract_params(j).business_group_id := p_business_group_id;
6198: g_extract_params(j).concurrent_req_id := l_conc_reqest_id;
6198: g_extract_params(j).concurrent_req_id := l_conc_reqest_id;
6199: g_extract_params(j).ext_dfn_id := l_ext_dfn_id;
6200: g_extract_params(j).transmission_type := l_req_params.argument7;
6201: g_extract_params(j).date_criteria := l_req_params.argument8;
6202: Hr_Utility.set_location('..Extract params', 17);
6203:
6204: g_extract_params(j).from_date := Fnd_Date.canonical_to_date(l_req_params.argument12);
6205: g_extract_params(j).to_date := Fnd_Date.canonical_to_date(l_req_params.argument13);
6206: Hr_Utility.set_location('..Extract params', 20);
6202: Hr_Utility.set_location('..Extract params', 17);
6203:
6204: g_extract_params(j).from_date := Fnd_Date.canonical_to_date(l_req_params.argument12);
6205: g_extract_params(j).to_date := Fnd_Date.canonical_to_date(l_req_params.argument13);
6206: Hr_Utility.set_location('..Extract params', 20);
6207: g_extract_params(j).agency_code := l_req_params.argument14;
6208: g_extract_params(j).personnel_office_id := l_req_params.argument15;
6209: g_extract_params(j).transmission_indicator := l_req_params.argument16;
6210: g_extract_params(j).signon_identification := l_req_params.argument17;
6212: g_extract_params(j).dept_code := l_req_params.argument19;
6213: g_extract_params(j).payroll_id := l_req_params.argument20;
6214: g_extract_params(j).notify := l_req_params.argument21;
6215:
6216: Hr_Utility.set_location('..Stored the Conc. Program parameters', 17);
6217: END IF;
6218:
6219: -- Get the extract start and end date
6220: /*
6244: IF g_rpa_awd_attr.count >0 THEN
6245: g_rpa_awd_attr.delete;
6246: END IF;
6247:
6248: Hr_Utility.set_location(' p_assignment_id1: '||p_assignment_id, 6);
6249:
6250: -- Get the person id and bus grp id into pkg global variables
6251: open csr_per_id(c_assignment_id => p_assignment_id
6252: ,c_business_group_id => p_business_group_id
6257: i := p_assignment_id;
6258: -- if g_debug then
6259:
6260:
6261: Hr_Utility.set_location(' p_business_group_id: '||g_business_group_id, 6);
6262: Hr_Utility.set_location(' g_ext_start_dt : '||g_ext_start_dt, 6);
6263: Hr_Utility.set_location(' p_effective_date : '||p_effective_date, 6);
6264: Hr_Utility.set_location(' g_ext_end_dt: '||g_ext_end_dt, 6);
6265: Hr_Utility.set_location(' g_person_id: '||g_person_id, 6);
6258: -- if g_debug then
6259:
6260:
6261: Hr_Utility.set_location(' p_business_group_id: '||g_business_group_id, 6);
6262: Hr_Utility.set_location(' g_ext_start_dt : '||g_ext_start_dt, 6);
6263: Hr_Utility.set_location(' p_effective_date : '||p_effective_date, 6);
6264: Hr_Utility.set_location(' g_ext_end_dt: '||g_ext_end_dt, 6);
6265: Hr_Utility.set_location(' g_person_id: '||g_person_id, 6);
6266: Hr_Utility.set_location(' p_assignment_id: '||p_assignment_id, 6);
6259:
6260:
6261: Hr_Utility.set_location(' p_business_group_id: '||g_business_group_id, 6);
6262: Hr_Utility.set_location(' g_ext_start_dt : '||g_ext_start_dt, 6);
6263: Hr_Utility.set_location(' p_effective_date : '||p_effective_date, 6);
6264: Hr_Utility.set_location(' g_ext_end_dt: '||g_ext_end_dt, 6);
6265: Hr_Utility.set_location(' g_person_id: '||g_person_id, 6);
6266: Hr_Utility.set_location(' p_assignment_id: '||p_assignment_id, 6);
6267: Hr_Utility.set_location(' l_asg_type: '||l_asg_type, 6);
6260:
6261: Hr_Utility.set_location(' p_business_group_id: '||g_business_group_id, 6);
6262: Hr_Utility.set_location(' g_ext_start_dt : '||g_ext_start_dt, 6);
6263: Hr_Utility.set_location(' p_effective_date : '||p_effective_date, 6);
6264: Hr_Utility.set_location(' g_ext_end_dt: '||g_ext_end_dt, 6);
6265: Hr_Utility.set_location(' g_person_id: '||g_person_id, 6);
6266: Hr_Utility.set_location(' p_assignment_id: '||p_assignment_id, 6);
6267: Hr_Utility.set_location(' l_asg_type: '||l_asg_type, 6);
6268: -- end if;
6261: Hr_Utility.set_location(' p_business_group_id: '||g_business_group_id, 6);
6262: Hr_Utility.set_location(' g_ext_start_dt : '||g_ext_start_dt, 6);
6263: Hr_Utility.set_location(' p_effective_date : '||p_effective_date, 6);
6264: Hr_Utility.set_location(' g_ext_end_dt: '||g_ext_end_dt, 6);
6265: Hr_Utility.set_location(' g_person_id: '||g_person_id, 6);
6266: Hr_Utility.set_location(' p_assignment_id: '||p_assignment_id, 6);
6267: Hr_Utility.set_location(' l_asg_type: '||l_asg_type, 6);
6268: -- end if;
6269: -- Get the Personnel Actions for the person id within the extract
6262: Hr_Utility.set_location(' g_ext_start_dt : '||g_ext_start_dt, 6);
6263: Hr_Utility.set_location(' p_effective_date : '||p_effective_date, 6);
6264: Hr_Utility.set_location(' g_ext_end_dt: '||g_ext_end_dt, 6);
6265: Hr_Utility.set_location(' g_person_id: '||g_person_id, 6);
6266: Hr_Utility.set_location(' p_assignment_id: '||p_assignment_id, 6);
6267: Hr_Utility.set_location(' l_asg_type: '||l_asg_type, 6);
6268: -- end if;
6269: -- Get the Personnel Actions for the person id within the extract
6270: -- date range
6263: Hr_Utility.set_location(' p_effective_date : '||p_effective_date, 6);
6264: Hr_Utility.set_location(' g_ext_end_dt: '||g_ext_end_dt, 6);
6265: Hr_Utility.set_location(' g_person_id: '||g_person_id, 6);
6266: Hr_Utility.set_location(' p_assignment_id: '||p_assignment_id, 6);
6267: Hr_Utility.set_location(' l_asg_type: '||l_asg_type, 6);
6268: -- end if;
6269: -- Get the Personnel Actions for the person id within the extract
6270: -- date range
6271: Hr_Utility.set_location(' Getting RPA s for the person.... ', 7);
6267: Hr_Utility.set_location(' l_asg_type: '||l_asg_type, 6);
6268: -- end if;
6269: -- Get the Personnel Actions for the person id within the extract
6270: -- date range
6271: Hr_Utility.set_location(' Getting RPA s for the person.... ', 7);
6272:
6273: for rpa_rec in csr_ghr_per
6274: (c_person_id => g_person_id
6275: ,c_assignment_id => p_assignment_id
6294: END IF;
6295: --end if;
6296: -- Get all the RPA into PL/SQL table for the person id.
6297: --if g_debug then
6298: Hr_Utility.set_location(' pa_request_id :'||rpa_rec.pa_request_id, 8);
6299: Hr_Utility.set_location(' effective_date :'||rpa_rec.effective_date,8);
6300: Hr_Utility.set_location(' first_noa_code :'||rpa_rec.first_noa_code,8);
6301: --end if;
6302:
6295: --end if;
6296: -- Get all the RPA into PL/SQL table for the person id.
6297: --if g_debug then
6298: Hr_Utility.set_location(' pa_request_id :'||rpa_rec.pa_request_id, 8);
6299: Hr_Utility.set_location(' effective_date :'||rpa_rec.effective_date,8);
6300: Hr_Utility.set_location(' first_noa_code :'||rpa_rec.first_noa_code,8);
6301: --end if;
6302:
6303:
6296: -- Get all the RPA into PL/SQL table for the person id.
6297: --if g_debug then
6298: Hr_Utility.set_location(' pa_request_id :'||rpa_rec.pa_request_id, 8);
6299: Hr_Utility.set_location(' effective_date :'||rpa_rec.effective_date,8);
6300: Hr_Utility.set_location(' first_noa_code :'||rpa_rec.first_noa_code,8);
6301: --end if;
6302:
6303:
6304:
6319:
6320: --Checking the Agency Code
6321: l_value :=g_rpa_attr(rpa_rec.pa_request_id).nfc_agency;
6322: IF l_value <> g_extract_params(j).agency_code THEN
6323: Hr_Utility.set_location('in side not null - l_value' ||l_value , 8);
6324: Hr_Utility.set_location('in side not null - ' || g_extract_params(j).agency_code, 8);
6325:
6326: Hr_Utility.set_location('in side not null', 8);
6327: l_return_value := 'N';
6320: --Checking the Agency Code
6321: l_value :=g_rpa_attr(rpa_rec.pa_request_id).nfc_agency;
6322: IF l_value <> g_extract_params(j).agency_code THEN
6323: Hr_Utility.set_location('in side not null - l_value' ||l_value , 8);
6324: Hr_Utility.set_location('in side not null - ' || g_extract_params(j).agency_code, 8);
6325:
6326: Hr_Utility.set_location('in side not null', 8);
6327: l_return_value := 'N';
6328: return l_return_value;
6322: IF l_value <> g_extract_params(j).agency_code THEN
6323: Hr_Utility.set_location('in side not null - l_value' ||l_value , 8);
6324: Hr_Utility.set_location('in side not null - ' || g_extract_params(j).agency_code, 8);
6325:
6326: Hr_Utility.set_location('in side not null', 8);
6327: l_return_value := 'N';
6328: return l_return_value;
6329: END IF;
6330:
6344: l_return_value := 'N';
6345: return l_return_value;
6346: END IF;
6347: END IF;
6348: Hr_Utility.set_location(' RPA req id details in PL/SQL tab', 8);
6349:
6350: end loop; -- for multi_rpas in csr_rpa_rec
6351:
6352: l_remark_cnt := 0;
6384: end if;
6385: exit when l_remark_cnt = 10;
6386: end loop; -- for j in csr_re
6387: g_remark_cnt :=l_remark_cnt ;
6388: Hr_Utility.set_location(' Total remarks found :'||l_remark_cnt,8);
6389: end loop; -- for rpa_rec in csr_ghr_per
6390:
6391: -- Get the Award Actions for the person id within the extract
6392: -- date range
6389: end loop; -- for rpa_rec in csr_ghr_per
6390:
6391: -- Get the Award Actions for the person id within the extract
6392: -- date range
6393: Hr_Utility.set_location(' Getting Awards s for the person.... ', 9);
6394: for awd_rec in csr_ghr_awd
6395: (c_person_id => g_person_id
6396: ,c_assignment_id => p_assignment_id
6397: ,c_ext_start_date => g_ext_start_dt
6411: l_Has_Award_Actions := true;
6412: END IF;
6413:
6414: if g_debug then
6415: Hr_Utility.set_location(' pa_request_id: '||awd_rec.pa_request_id, 10);
6416: Hr_Utility.set_location(' effective_date : '||awd_rec.effective_date, 10);
6417: Hr_Utility.set_location(' first_noa_code : '||awd_rec.first_noa_code, 10);
6418: end if;
6419: -- Get all the RPA into PL/SQL table for the person id.
6412: END IF;
6413:
6414: if g_debug then
6415: Hr_Utility.set_location(' pa_request_id: '||awd_rec.pa_request_id, 10);
6416: Hr_Utility.set_location(' effective_date : '||awd_rec.effective_date, 10);
6417: Hr_Utility.set_location(' first_noa_code : '||awd_rec.first_noa_code, 10);
6418: end if;
6419: -- Get all the RPA into PL/SQL table for the person id.
6420: FOR multi_rpas IN csr_rpa_rec
6413:
6414: if g_debug then
6415: Hr_Utility.set_location(' pa_request_id: '||awd_rec.pa_request_id, 10);
6416: Hr_Utility.set_location(' effective_date : '||awd_rec.effective_date, 10);
6417: Hr_Utility.set_location(' first_noa_code : '||awd_rec.first_noa_code, 10);
6418: end if;
6419: -- Get all the RPA into PL/SQL table for the person id.
6420: FOR multi_rpas IN csr_rpa_rec
6421: (c_pa_request_id => awd_rec.pa_request_id)
6429: ,p_sec_noa_cd =>awd_rec.second_noa_code
6430: ,p_request_id =>awd_rec.pa_request_id
6431: ,p_notification_id =>g_aw_req(i).pa_notification_id
6432: );
6433: Hr_Utility.set_location(' Award req id details in PL/SQL tab', 11);
6434:
6435: --Checking the Agency Code in Awards
6436: l_value :=g_rpa_awd_attr(awd_rec.pa_request_id).nfc_agency_code;
6437: IF l_value <> g_extract_params(j).agency_code THEN
6498: -- End Remark Award actions
6499:
6500: END LOOP; -- for awd_rec in csr_ghr_awd
6501: -- Get the most recent Address Change with the extract date range.
6502: Hr_Utility.set_location(' Getting Primary Address Changes for the person.... ', 12);
6503:
6504: IF g_rpa_id_apt = 100 THEN
6505:
6506: Hr_Utility.set_location(' 100 - Getting Primary Address.... ', 12);
6502: Hr_Utility.set_location(' Getting Primary Address Changes for the person.... ', 12);
6503:
6504: IF g_rpa_id_apt = 100 THEN
6505:
6506: Hr_Utility.set_location(' 100 - Getting Primary Address.... ', 12);
6507: open csr_per_add_apt (c_person_id => g_person_id
6508: ,c_ext_end_date => g_ext_end_dt);
6509: fetch csr_per_add_apt into g_address_rec(i);
6510: IF (csr_per_add_apt%found ) then
6513: ,p_business_group_id =>p_business_group_id
6514: ,p_effective_date =>p_effective_date
6515: ,p_request_id =>NULL
6516: );
6517: Hr_Utility.set_location(' Person:address_id: '||g_address_rec(i).address_id, 14);
6518: l_Has_Add_chgs := true;
6519: --Checking the criteria validations in Address
6520: --Checking the Agency Code in Address
6521: l_value :=g_rpa_add_attr(p_assignment_id).nfc_agency_code;
6544: END IF; -- IF (csr_per_add_apt%found )
6545: CLOSE csr_per_add_apt;
6546:
6547: ELSE -- IF g_rpa_id_apt = 100
6548: Hr_Utility.set_location('Non 100 - Getting Primary Address.... ' || l_return_value, 12);
6549: OPEN csr_per_add (c_person_id => g_person_id
6550: ,c_ext_start_date => g_ext_start_dt
6551: ,c_ext_end_date => g_ext_end_dt);
6552: FETCH csr_per_add into g_address_rec(i);
6549: OPEN csr_per_add (c_person_id => g_person_id
6550: ,c_ext_start_date => g_ext_start_dt
6551: ,c_ext_end_date => g_ext_end_dt);
6552: FETCH csr_per_add into g_address_rec(i);
6553: Hr_Utility.set_location('Non 100 - After fetching.... ' || l_return_value, 12);
6554: IF (csr_per_add%found ) then
6555: populate_add_attr (p_person_id =>g_person_id
6556: ,p_assignment_id =>p_assignment_id
6557: ,p_business_group_id =>p_business_group_id
6560: ); --Checking the criteria validations in Address
6561: --Checking the Agency Code in Address
6562: l_value :=g_rpa_add_attr(p_assignment_id).nfc_agency_code;
6563: IF l_value <> g_extract_params(j).agency_code THEN
6564: Hr_Utility.set_location('Non 100 - Getting Primary Address.... ', 121);
6565: l_return_value := 'N';
6566: return l_return_value;
6567: END IF;
6568: --Checking the criteria validations in Address
6568: --Checking the criteria validations in Address
6569: --Checking the Agency Code in Address
6570: l_value :=g_rpa_add_attr(p_assignment_id).nfc_agency_code;
6571: IF l_value <> g_extract_params(j).agency_code THEN
6572: Hr_Utility.set_location('Non 100 - Getting Primary Address.... ', 122);
6573: l_return_value := 'N';
6574: return l_return_value;
6575: END IF;
6576:
6577: --Checking the dept code in Address
6578: IF g_extract_params(j).dept_code IS NOT NULL THEN
6579: l_value :=g_rpa_add_attr(p_assignment_id).dept_code;
6580: IF l_value <> g_extract_params(j).dept_code THEN
6581: Hr_Utility.set_location('Non 100 - Getting Primary Address.... ', 123);
6582: l_return_value := 'N';
6583: return l_return_value;
6584: END IF;
6585: END IF;
6587: --Checking the Personnel Office id in Address
6588: IF g_extract_params(j).personnel_office_id IS NOT NULL THEN
6589: l_value :=g_rpa_add_attr(p_assignment_id).poi;
6590: IF l_value <> g_extract_params(j).personnel_office_id THEN
6591: Hr_Utility.set_location('Non 100 - Getting Primary Address.... ', 124);
6592: l_return_value := 'N';
6593: return l_return_value;
6594: END IF;
6595: END IF;
6596: --Checking the dept code in Address
6597: IF g_extract_params(j).dept_code IS NOT NULL THEN
6598: l_value :=g_rpa_add_attr(p_assignment_id).dept_code;
6599: IF l_value <> g_extract_params(j).dept_code THEN
6600: Hr_Utility.set_location('Non 100 - Getting Primary Address.... ', 125);
6601: l_return_value := 'N';
6602: RETURN l_return_value;
6603: END IF;
6604: END IF;
6606: --Checking the Personnel Office id in Address
6607: IF g_extract_params(j).personnel_office_id IS NOT NULL THEN
6608: l_value :=g_rpa_add_attr(p_assignment_id).poi;
6609: IF l_value <> g_extract_params(j).personnel_office_id THEN
6610: Hr_Utility.set_location('Non 100 - Getting Primary Address.... ', 126);
6611: l_return_value := 'N';
6612: return l_return_value;
6613: END IF;
6614: END IF;
6611: l_return_value := 'N';
6612: return l_return_value;
6613: END IF;
6614: END IF;
6615: Hr_Utility.set_location(' Person:address_id: '||g_address_rec(i).address_id, 14);
6616: l_Has_Add_chgs := true;
6617: --Checking the criteria validations in Address
6618: --Checking the Agency Code in Address
6619: l_value :=g_rpa_add_attr(p_assignment_id).nfc_agency_code;
6617: --Checking the criteria validations in Address
6618: --Checking the Agency Code in Address
6619: l_value :=g_rpa_add_attr(p_assignment_id).nfc_agency_code;
6620: IF l_value <> g_extract_params(j).agency_code THEN
6621: Hr_Utility.set_location('Non 100 - Getting Primary Address.... ', 127);
6622: l_return_value := 'N';
6623: return l_return_value;
6624: END IF;
6625:
6626: --Checking the dept code in Address
6627: IF g_extract_params(j).dept_code IS NOT NULL THEN
6628: l_value :=g_rpa_add_attr(p_assignment_id).dept_code;
6629: IF l_value <> g_extract_params(j).dept_code THEN
6630: Hr_Utility.set_location('Non 100 - Getting Primary Address.... ', 128);
6631: l_return_value := 'N';
6632: return l_return_value;
6633: END IF;
6634: END IF;
6636: --Checking the Personnel Office id in Address
6637: IF g_extract_params(j).personnel_office_id IS NOT NULL THEN
6638: l_value :=g_rpa_add_attr(p_assignment_id).poi;
6639: IF l_value <> g_extract_params(j).personnel_office_id THEN
6640: Hr_Utility.set_location('Non 100 - Getting Primary Address.... ', 129);
6641: l_return_value := 'N';
6642: return l_return_value;
6643: END IF;
6644: END IF;
6654: l_Has_Award_Actions or
6655: l_Has_RPA_actions then
6656: l_return_value := 'Y';
6657: elsif l_asg_type ='B' then
6658: Hr_Utility.set_location('Non 100 - After fetching.... ' || l_return_value, 12);
6659: l_return_value := 'Y';
6660: end if;
6661:
6662: Hr_Utility.set_location(' l_return_value: '||l_return_value, 79);
6658: Hr_Utility.set_location('Non 100 - After fetching.... ' || l_return_value, 12);
6659: l_return_value := 'Y';
6660: end if;
6661:
6662: Hr_Utility.set_location(' l_return_value: '||l_return_value, 79);
6663: Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
6664: return l_return_value;
6665:
6666: exception
6659: l_return_value := 'Y';
6660: end if;
6661:
6662: Hr_Utility.set_location(' l_return_value: '||l_return_value, 79);
6663: Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
6664: return l_return_value;
6665:
6666: exception
6667: when Others then
6668: p_error_code := sqlcode; p_warning_code := '-1';
6669: p_error_message:= NULL; p_warning_message:= null;
6670: l_return_value := 'N';
6671:
6672: Hr_Utility.set_location(' l_return_value: '||l_return_value, 89);
6673: Hr_Utility.set_location('error Leaving: '||l_proc_name, 90);
6674:
6675: return l_return_value;
6676:
6669: p_error_message:= NULL; p_warning_message:= null;
6670: l_return_value := 'N';
6671:
6672: Hr_Utility.set_location(' l_return_value: '||l_return_value, 89);
6673: Hr_Utility.set_location('error Leaving: '||l_proc_name, 90);
6674:
6675: return l_return_value;
6676:
6677: end Evaluate_Person_Inclusion;
6711: l_end_date DATE;
6712: l_position_id NUMBER;
6713:
6714: BEGIN
6715: Hr_Utility.set_location('Entering :'||l_proc_name, 5);
6716: i := g_business_group_id;
6717:
6718: Hr_Utility.set_location('g_business_group_id :'||g_business_group_id, 5);
6719: Hr_Utility.set_location('p_header_type :'||p_header_type, 5);
6714: BEGIN
6715: Hr_Utility.set_location('Entering :'||l_proc_name, 5);
6716: i := g_business_group_id;
6717:
6718: Hr_Utility.set_location('g_business_group_id :'||g_business_group_id, 5);
6719: Hr_Utility.set_location('p_header_type :'||p_header_type, 5);
6720: Hr_Utility.set_location('g_extract_params(i).agency_code:'||g_extract_params(i).agency_code, 5);
6721:
6722: IF p_header_type = 'AGENCY_CODE' THEN
6715: Hr_Utility.set_location('Entering :'||l_proc_name, 5);
6716: i := g_business_group_id;
6717:
6718: Hr_Utility.set_location('g_business_group_id :'||g_business_group_id, 5);
6719: Hr_Utility.set_location('p_header_type :'||p_header_type, 5);
6720: Hr_Utility.set_location('g_extract_params(i).agency_code:'||g_extract_params(i).agency_code, 5);
6721:
6722: IF p_header_type = 'AGENCY_CODE' THEN
6723: l_return_value := g_extract_params(i).agency_code;
6716: i := g_business_group_id;
6717:
6718: Hr_Utility.set_location('g_business_group_id :'||g_business_group_id, 5);
6719: Hr_Utility.set_location('p_header_type :'||p_header_type, 5);
6720: Hr_Utility.set_location('g_extract_params(i).agency_code:'||g_extract_params(i).agency_code, 5);
6721:
6722: IF p_header_type = 'AGENCY_CODE' THEN
6723: l_return_value := g_extract_params(i).agency_code;
6724: ELSIF p_header_type = 'PERSONNEL_OFFICE_ID' THEN
6738: ,p_end_date =>l_end_date
6739: );
6740: l_return_value := LPAD(l_period_num,2,'0');
6741: END IF;
6742: hr_utility.set_location('l_return_value: '||l_return_value, 45);
6743: hr_utility.set_location('Leaving: '||l_proc_name, 45);
6744: RETURN l_return_value;
6745: EXCEPTION
6746: WHEN Others THEN
6739: );
6740: l_return_value := LPAD(l_period_num,2,'0');
6741: END IF;
6742: hr_utility.set_location('l_return_value: '||l_return_value, 45);
6743: hr_utility.set_location('Leaving: '||l_proc_name, 45);
6744: RETURN l_return_value;
6745: EXCEPTION
6746: WHEN Others THEN
6747: p_error_message :='SQL-ERRM :'||SQLERRM;
6744: RETURN l_return_value;
6745: EXCEPTION
6746: WHEN Others THEN
6747: p_error_message :='SQL-ERRM :'||SQLERRM;
6748: hr_utility.set_location('Leaving: '||l_proc_name, 45);
6749: RETURN l_return_value;
6750: END Get_NFC_ConcProg_Information;
6751:
6752: