[Home] [Help]
86: ,'PIX'
87: ,per.per_information14
88: ,per.marital_status
89: ,paf.primary_flag
90: FROM per_all_assignments_f paf,
91: per_all_people_f per,
92: per_periods_of_service pps,
93: per_assignment_status_types ast
94: WHERE paf.assignment_id = c_assignment_id
110: ,c_business_group_id IN Number
111: ,c_person_id IN Number) IS
112: SELECT paf.organization_id
113: ,paf.payroll_id
114: FROM per_all_assignments_f paf,
115: per_all_people_f per
116: WHERE paf.assignment_id <> c_assignment_id
117: AND paf.person_id = c_person_id
118: AND paf.person_id = per.person_id
312: -- =============================================================================
313: -- Cursor to chk for other primary assig. within the extract date range.
314: -- =============================================================================
315: CURSOR csr_sec_assg
316: (c_primary_assignment_id IN per_all_assignments_f.assignment_id%TYPE
317: ,c_person_id IN per_all_people_f.person_id%TYPE
318: ,c_effective_date IN Date
319: ,c_extract_start_date IN Date
320: ,c_extract_end_date IN Date ) IS
324: ,asg.effective_start_date
325: ,asg.effective_end_date
326: ,'NO'
327: ,asg.assignment_id
328: FROM per_all_assignments_f asg
329: WHERE asg.person_id = c_person_id
330: AND asg.assignment_id <> c_primary_assignment_id
331: AND asg.assignment_type ='E'
332: AND (( c_effective_date BETWEEN asg.effective_start_date
334: )
335: OR
336: ( asg.effective_end_date =
337: (SELECT Max(asx.effective_end_date)
338: FROM per_all_assignments_f asx
339: WHERE asx.assignment_id = asg.assignment_id
340: AND asx.person_id = c_person_id
341: AND asx.assignment_type = 'E'
342: AND ((asx.effective_end_date BETWEEN c_extract_start_date
452: g_extract_params(p_business_group_id).extract_start_date
453: AND g_extract_params(p_business_group_id).extract_end_date
454: AND EXISTS(SELECT 1
455: FROM per_periods_of_service pps
456: ,per_all_assignments_f asg
457: WHERE pps.person_id = g_person_id
458: AND asg.assignment_id = p_assignment_id
459: AND asg.period_of_service_id = pps.period_of_service_id
460: AND (pps.actual_termination_date IS NULL
476: ,p_assignment_id IN NUMBER
477: ,c_asg_seq_num IN VARCHAR) IS
478: SELECT min(effective_start_date) - 1 term_date
479: ,period_of_service_id
480: FROM per_all_assignments_f asg
481: WHERE assignment_id = p_assignment_id
482: AND effective_start_date <= g_extract_params(p_business_group_id).extract_end_date
483: AND assignment_status_type_id IN (SELECT assignment_status_type_id
484: FROM per_assignment_status_types
491: -- assignments only.
492: --
493: SELECT max(effective_end_date)
494: ,period_of_service_id
495: FROM per_all_assignments_f asg
496: WHERE assignment_id = p_assignment_id
497: AND asg.primary_flag = 'N'
498: AND effective_end_date <= g_extract_params(p_business_group_id).extract_end_date
499: AND NOT EXISTS( SELECT 1
496: WHERE assignment_id = p_assignment_id
497: AND asg.primary_flag = 'N'
498: AND effective_end_date <= g_extract_params(p_business_group_id).extract_end_date
499: AND NOT EXISTS( SELECT 1
500: FROM per_all_assignments_f asg1
501: WHERE asg1.assignment_id = p_assignment_id
502: AND asg1.effective_start_date = asg.effective_end_date + 1
503: AND asg.assignment_id = asg1.assignment_id )
504: AND NOT EXISTS( SELECT 1
501: WHERE asg1.assignment_id = p_assignment_id
502: AND asg1.effective_start_date = asg.effective_end_date + 1
503: AND asg.assignment_id = asg1.assignment_id )
504: AND NOT EXISTS( SELECT 1
505: FROM per_all_assignments_f asg1
506: WHERE asg1.assignment_id = p_assignment_id
507: AND asg1.effective_start_date < asg.effective_start_date
508: AND asg.assignment_id = asg1.assignment_id
509: AND asg1.assignment_status_type_id IN (SELECT assignment_status_type_id
526: ,ext_chg_evt_log_id
527: ,fnd_number.canonical_to_number(prmtr_01)
528: FROM ben_ext_chg_evt_log bec
529: ,per_periods_of_service pps
530: ,per_all_assignments_f asg
531: WHERE bec.chg_evt_cd = 'AAT'
532: AND bec.person_id = g_person_id
533: AND bec.business_group_id = p_business_group_id
534: AND fnd_date.canonical_to_date(bec.prmtr_09)
549: SELECT NULL
550: ,to_char(effective_start_date - 1,'DD/MM/YYYY') term_date
551: ,9999999999
552: ,period_of_service_id
553: FROM per_all_assignments_f asg
554: WHERE assignment_id = p_assignment_id
555: AND assignment_status_type_id IN (SELECT assignment_status_type_id
556: FROM per_assignment_status_types
557: WHERE per_system_status = 'TERM_ASSIGN'
567: AND fnd_date.canonical_to_date(prmtr_09)
568: BETWEEN g_extract_params(p_business_group_id).extract_start_date
569: AND g_extract_params(p_business_group_id).extract_end_date )
570: AND NOT EXISTS( SELECT 1
571: FROM per_all_assignments_f asg1
572: WHERE assignment_id = p_assignment_id
573: AND effective_start_date <
574: g_extract_params(p_business_group_id).extract_start_date
575: AND asg.assignment_id = asg1.assignment_id
582: SELECT NULL
583: ,to_char(effective_end_date,'DD/MM/YYYY')
584: ,9999999999
585: ,period_of_service_id
586: FROM per_all_assignments_f asg
587: WHERE assignment_id = p_assignment_id
588: AND asg.primary_flag = 'N'
589: AND effective_end_date BETWEEN
590: g_extract_params(p_business_group_id).extract_start_date
589: AND effective_end_date BETWEEN
590: g_extract_params(p_business_group_id).extract_start_date
591: AND g_extract_params(p_business_group_id).extract_end_date
592: AND NOT EXISTS( SELECT 1
593: FROM per_all_assignments_f asg1
594: WHERE assignment_id = p_assignment_id
595: AND effective_start_date = asg.effective_end_date + 1
596: AND asg.assignment_id = asg1.assignment_id )
597: AND NOT EXISTS( SELECT 1
594: WHERE assignment_id = p_assignment_id
595: AND effective_start_date = asg.effective_end_date + 1
596: AND asg.assignment_id = asg1.assignment_id )
597: AND NOT EXISTS( SELECT 1
598: FROM per_all_assignments_f asg1
599: WHERE assignment_id = p_assignment_id
600: AND effective_start_date <
601: g_extract_params(p_business_group_id).extract_start_date
602: AND asg.assignment_id = asg1.assignment_id
619: SELECT NULL
620: ,to_char(min(effective_start_date) - 1,'DD/MM/YYYY')
621: ,9999999999
622: ,period_of_service_id
623: FROM per_all_assignments_f asg
624: WHERE assignment_id = p_assignment_id
625: AND asg.primary_flag = 'N'
626: AND effective_start_date < g_extract_params(p_business_group_id).extract_start_date
627: AND assignment_status_type_id IN (SELECT assignment_status_type_id
996: CURSOR c_get_retro_time_period(c_element_entry_id in number
997: ,c_assignment_id in number) IS
998: SELECT ptp.start_date,ptp.end_date
999: FROM per_time_periods ptp
1000: ,per_all_assignments_f paa
1001: WHERE paa.assignment_id = c_assignment_id
1002: AND ptp.payroll_id = paa.payroll_id
1003: AND pay_paywsmee_pkg.get_original_date_earned(c_element_entry_id)
1004: BETWEEN paa.effective_start_date
1923: AND effective_end_date;
1924:
1925: CURSOR c_get_org_id IS
1926: SELECT organization_id,business_group_id
1927: FROM per_all_assignments_f
1928: WHERE assignment_id = p_assignment_id
1929: AND business_group_id = g_business_group_id
1930: AND p_effective_date BETWEEN effective_start_date
1931: AND effective_end_date;
1936: l_foumula_type_id ff_formulas_f.formula_id%TYPE;
1937: l_outputs Ff_Exec.outputs_t;
1938: l_ff_value ben_ext_rslt_dtl.val_01%TYPE;
1939: l_ff_value_fmt ben_ext_rslt_dtl.val_01%TYPE;
1940: l_org_id per_all_assignments_f.organization_id%TYPE;
1941: l_bgid per_all_assignments_f.business_group_id%TYPE;
1942:
1943:
1944: BEGIN
1937: l_outputs Ff_Exec.outputs_t;
1938: l_ff_value ben_ext_rslt_dtl.val_01%TYPE;
1939: l_ff_value_fmt ben_ext_rslt_dtl.val_01%TYPE;
1940: l_org_id per_all_assignments_f.organization_id%TYPE;
1941: l_bgid per_all_assignments_f.business_group_id%TYPE;
1942:
1943:
1944: BEGIN
1945:
2101: l_defined_balance_id pay_defined_balances.defined_balance_id%TYPE;
2102: l_balance_amount NUMBER :=0;
2103: l_bal_total_amt NUMBER :=0;
2104: l_dimension_name VARCHAR2(100);
2105: i per_all_assignments_f.business_group_id%TYPE;
2106: l_legislation_code per_business_groups.legislation_code%TYPE;
2107: l_balance_type_id pay_balance_types.balance_type_id%TYPE;
2108: l_index NUMBER;
2109:
2181: ,p_error_message OUT NOCOPY VARCHAR2 )
2182: RETURN NUMBER IS
2183:
2184: l_bal_total_amt NUMBER :=0;
2185: i per_all_assignments_f.business_group_id%TYPE;
2186:
2187: BEGIN
2188:
2189: i := p_business_group_id;
2390: -- =============================================================================
2391: -- Get_Asg_Seq_Num:
2392: -- =============================================================================
2393: FUNCTION Get_Asg_Seq_Num
2394: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
2395: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
2396: ,p_effective_date IN Date
2397: ,p_error_message OUT NOCOPY Varchar2
2398: ,p_data_element_value OUT NOCOPY Varchar2
2391: -- Get_Asg_Seq_Num:
2392: -- =============================================================================
2393: FUNCTION Get_Asg_Seq_Num
2394: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
2395: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
2396: ,p_effective_date IN Date
2397: ,p_error_message OUT NOCOPY Varchar2
2398: ,p_data_element_value OUT NOCOPY Varchar2
2399: ) RETURN Number IS
2425: -- ABP as a new hire assignment
2426: --============================================================================
2427: FUNCTION Chk_New_Hire_Asg
2428: (p_person_id IN per_all_people_f.person_id%TYPE
2429: ,p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
2430: ,p_business_group_id IN NUMBER
2431: ,p_start_date IN DATE
2432: ,p_end_date IN DATE
2433: ,p_hire_date OUT NOCOPY DATE
2437: -- Cursor to derive the hire date of the EE assignment
2438: --
2439: CURSOR csr_hire_dt IS
2440: SELECT MIN(effective_start_date)
2441: FROM per_all_assignments_f
2442: WHERE assignment_id = p_assignment_id
2443: AND assignment_type = 'E';
2444:
2445: --
2541: --=============================================================================
2542: -- Function to get the age of a person given the effective date
2543: --=============================================================================
2544: FUNCTION Get_Age
2545: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
2546: ,p_effective_date IN DATE)
2547: RETURN NUMBER IS
2548:
2549: CURSOR get_dob IS
2548:
2549: CURSOR get_dob IS
2550: SELECT trunc(date_of_birth)
2551: FROM per_all_people_f per
2552: ,per_all_assignments_f paf
2553: WHERE per.person_id = paf.person_id
2554: AND paf.assignment_id = p_assignment_id
2555: AND p_effective_date BETWEEN per.effective_start_date
2556: AND per.effective_end_date
2578: -- =============================================================================
2579: -- Get_Pen_Prin_Obj_Cd
2580: -- =============================================================================
2581: FUNCTION Get_Pri_Obj_Cd_Cur
2582: ( p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
2583: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
2584: ,p_effective_date IN Date
2585: ,p_error_message OUT NOCOPY Varchar2
2586: ,p_data_element_value OUT NOCOPY Varchar2
2579: -- Get_Pen_Prin_Obj_Cd
2580: -- =============================================================================
2581: FUNCTION Get_Pri_Obj_Cd_Cur
2582: ( p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
2583: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
2584: ,p_effective_date IN Date
2585: ,p_error_message OUT NOCOPY Varchar2
2586: ,p_data_element_value OUT NOCOPY Varchar2
2587: ) RETURN Number IS
2636: -- =============================================================================
2637: -- Get_Pen_Prin_Obj_Cd
2638: -- =============================================================================
2639: FUNCTION Get_Pri_Obj_Cd
2640: ( p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
2641: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
2642: ,p_effective_date IN Date
2643: ,p_error_message OUT NOCOPY Varchar2
2644: ,p_data_element_value OUT NOCOPY Varchar2
2637: -- Get_Pen_Prin_Obj_Cd
2638: -- =============================================================================
2639: FUNCTION Get_Pri_Obj_Cd
2640: ( p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
2641: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
2642: ,p_effective_date IN Date
2643: ,p_error_message OUT NOCOPY Varchar2
2644: ,p_data_element_value OUT NOCOPY Varchar2
2645: ) RETURN Number IS
2705: --This Procedure stores the child orgs and sub orgs which are employer themselves of
2706: --the organization passed as parameter(employers) in table employer_child_list
2707: -- =============================================================================
2708: PROCEDURE Set_Er_Children ( p_org_id IN hr_all_organization_units.organization_id%TYPE
2709: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
2710: ,p_effective_date IN Date
2711: )
2712: IS
2713: -- Cursor to Check if a org hierarchy is attached to the BG.
2827: -- EE assignment. This also takes care of the EE assignment
2828: -- not attached to the payroll.
2829: --
2830: FUNCTION Chk_ABP_Processed
2831: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
2832: ,p_effective_date IN DATE
2833: ,p_business_group_id IN NUMBER)
2834: RETURN NUMBER IS
2835:
2863: -- to a payroll
2864: --
2865: CURSOR c_pay_id IS
2866: SELECT payroll_id
2867: FROM per_all_assignments_f
2868: WHERE p_effective_date between effective_start_date AND
2869: effective_end_date
2870: AND assignment_id = p_assignment_id
2871: AND payroll_id IS NOT NULL;
2974: -- ended assignment.
2975: --
2976: CURSOR c_get_term_date IS
2977: SELECT MIN(effective_start_date) - 1 term_date
2978: FROM per_all_assignments_f asg
2979: WHERE assignment_id = p_assignment_id
2980: AND assignment_status_type_id IN (SELECT assignment_status_type_id
2981: FROM per_assignment_status_types
2982: WHERE per_system_status = 'TERM_ASSIGN'
2986: -- Get the dates for any ended assignments. Note that this is for sec
2987: -- assignments only.
2988: --
2989: SELECT MAX(effective_end_date)
2990: FROM per_all_assignments_f asg
2991: WHERE assignment_id = p_assignment_id
2992: AND asg.primary_flag = 'N'
2993: AND NOT EXISTS( SELECT 1
2994: FROM per_all_assignments_f asg1
2990: FROM per_all_assignments_f asg
2991: WHERE assignment_id = p_assignment_id
2992: AND asg.primary_flag = 'N'
2993: AND NOT EXISTS( SELECT 1
2994: FROM per_all_assignments_f asg1
2995: WHERE asg1.assignment_id = p_assignment_id
2996: AND asg1.effective_start_date = asg.effective_end_date + 1
2997: AND asg.assignment_id = asg1.assignment_id )
2998: AND NOT EXISTS( SELECT 1
2995: WHERE asg1.assignment_id = p_assignment_id
2996: AND asg1.effective_start_date = asg.effective_end_date + 1
2997: AND asg.assignment_id = asg1.assignment_id )
2998: AND NOT EXISTS( SELECT 1
2999: FROM per_all_assignments_f asg1
3000: WHERE asg1.assignment_id = p_assignment_id
3001: AND asg1.effective_start_date < asg.effective_start_date
3002: AND asg.assignment_id = asg1.assignment_id
3003: AND asg1.assignment_status_type_id IN (SELECT assignment_status_type_id
3153: -- Procedure to populate all the PL/SQL tables for records
3154: -- with multiple rows
3155: --
3156: PROCEDURE Populate_Record_Structures
3157: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
3158: ,p_effective_date IN DATE
3159: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
3160: ,p_error_message OUT NOCOPY VARCHAR2 ) IS
3161:
3155: --
3156: PROCEDURE Populate_Record_Structures
3157: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
3158: ,p_effective_date IN DATE
3159: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
3160: ,p_error_message OUT NOCOPY VARCHAR2 ) IS
3161:
3162: --
3163: -- Cursor to get the Assignment Run level dimension id
3189: SELECT assignment_id
3190: ,effective_start_date start_date
3191: ,effective_end_date end_date
3192: ,fnd_number.canonical_to_number(new_val1) ptp
3193: FROM per_all_assignments_f asg,
3194: ben_ext_chg_evt_log log
3195: ,per_assignment_status_types past
3196: ,hr_soft_coding_keyflex sck
3197: WHERE asg.assignment_id = p_assignment_id
3379: SELECT asg.assignment_id
3380: ,effective_start_date start_date
3381: ,effective_end_date end_date
3382: ,least(fnd_number.canonical_to_number(nvl(sck.segment29,100)),125) ptp
3383: FROM per_all_assignments_f asg
3384: ,per_assignment_status_types past
3385: ,hr_soft_coding_keyflex sck
3386: WHERE asg.assignment_id = p_assignment_id
3387: AND asg.assignment_status_type_id = past.assignment_status_type_id
3395:
3396:
3397: CURSOR c_get_min_st_dt IS
3398: SELECT effective_start_date
3399: FROM per_all_assignments_f asg
3400: ,per_assignment_status_types past
3401: ,hr_soft_coding_keyflex sck
3402: WHERE asg.assignment_id = p_assignment_id
3403: AND asg.assignment_status_type_id = past.assignment_status_type_id
3406: AND asg.effective_start_date BETWEEN
3407: g_extract_params(p_business_group_id).extract_start_date
3408: AND g_extract_params(p_business_group_id).extract_end_date
3409: AND EXISTS (SELECT 1
3410: FROM per_all_assignments_f asg1
3411: ,per_assignment_status_types past1
3412: ,hr_soft_coding_keyflex sck1
3413: WHERE asg1.assignment_id = p_assignment_id
3414: AND asg1.effective_end_date = asg.effective_start_date - 1
3421: ORDER BY effective_start_date;
3422:
3423: CURSOR c_get_hire_dt IS
3424: SELECT MIN(effective_start_date) hire_date
3425: FROM per_all_assignments_f asg
3426: WHERE assignment_id = p_assignment_id
3427: AND assignment_status_type_id IN (SELECT assignment_status_type_id
3428: FROM per_assignment_status_types
3429: WHERE per_system_status = 'ACTIVE_ASSIGN'
3431: AND assignment_type = 'E';
3432:
3433: CURSOR c_get_term_dt IS
3434: SELECT MIN(effective_start_date) - 1 term_date
3435: FROM per_all_assignments_f asg
3436: WHERE assignment_id = p_assignment_id
3437: AND assignment_status_type_id IN (SELECT assignment_status_type_id
3438: FROM per_assignment_status_types
3439: WHERE per_system_status = 'TERM_ASSIGN'
3447: l_get_fp_nh NUMBER;
3448: l_rec_12_amt_sent_prev_r NUMBER;
3449: l_sent_to_abp NUMBER;
3450: l_rej_hf_ee NUMBER;
3451: i per_all_assignments_f.business_group_id%TYPE;
3452: l_ele_type_id pay_element_types_f.element_type_id%TYPE;
3453: l_prev_ele_type_id pay_element_types_f.element_type_id%TYPE;
3454: l_proc_name VARCHAR2(150) := g_proc_name ||'Populate_Record_Structures';
3455: l_assig_rec csr_assig%ROWTYPE;
5464: -- to be processed. The assignments that need not be processed are rejected
5465: -- here. The criteria is to filter the assignments based on the org hierarchy .
5466: -- =============================================================================
5467: FUNCTION Pension_Criteria_Full_Profile
5468: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
5469: ,p_effective_date IN DATE
5470: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
5471: ,p_warning_message OUT NOCOPY VARCHAR2
5472: ,p_error_message OUT NOCOPY VARCHAR2
5466: -- =============================================================================
5467: FUNCTION Pension_Criteria_Full_Profile
5468: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
5469: ,p_effective_date IN DATE
5470: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
5471: ,p_warning_message OUT NOCOPY VARCHAR2
5472: ,p_error_message OUT NOCOPY VARCHAR2
5473: ) RETURN VARCHAR2 IS
5474: --
5610: CURSOR c_rej_old_ee (c_ass_id IN NUMBER
5611: ,c_eff_dt IN DATE ) IS
5612: SELECT 1
5613: FROM per_periods_of_service pps
5614: ,per_all_assignments_f asg
5615: WHERE asg.assignment_id = c_ass_id
5616: AND c_eff_dt BETWEEN asg.effective_start_date AND asg.effective_end_date
5617: AND asg.period_of_service_id = pps.period_of_service_id
5618: AND pps.actual_termination_date IS NOT NULL
5627: l_rej_old_ee NUMBER;
5628: l_ext_params csr_ext_params%ROWTYPE;
5629: l_conc_reqest_id ben_ext_rslt.request_id%TYPE;
5630: l_ext_dfn_type pqp_extract_attributes.ext_dfn_type%TYPE;
5631: i per_all_assignments_f.business_group_id%TYPE;
5632: l_ext_rslt_id ben_ext_rslt.ext_rslt_id%TYPE;
5633: l_ext_dfn_id ben_ext_dfn.ext_dfn_id%TYPE;
5634: l_return_value Varchar2(2) :='N';
5635: l_proc_name Varchar2(150) := g_proc_name ||'Pension_Criteria_Full_Profile';
5941: ,p_error_message OUT NOCOPY Varchar2
5942: ) RETURN Varchar2 IS
5943:
5944: l_return_value Varchar2(50);
5945: i per_all_assignments_f.business_group_id%TYPE;
5946: l_proc_name Varchar2(150) := g_proc_name ||'Check_Addl_Assigs';
5947: l_sec_assg_rec csr_sec_assg%ROWTYPE;
5948: l_effective_date Date;
5949: l_criteria_value Varchar2(2);
5948: l_effective_date Date;
5949: l_criteria_value Varchar2(2);
5950: l_warning_message Varchar2(2000);
5951: l_error_message Varchar2(2000);
5952: l_asg_type per_all_assignments_f.assignment_type%TYPE;
5953: l_person_id per_all_people_f.person_id%TYPE;
5954: l_assignment_id per_all_assignments_f.assignment_id%TYPE;
5955: BEGIN
5956: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
5950: l_warning_message Varchar2(2000);
5951: l_error_message Varchar2(2000);
5952: l_asg_type per_all_assignments_f.assignment_type%TYPE;
5953: l_person_id per_all_people_f.person_id%TYPE;
5954: l_assignment_id per_all_assignments_f.assignment_id%TYPE;
5955: BEGIN
5956: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
5957: i := p_business_group_id;
5958:
6122: BEGIN
6123:
6124: SELECT person_id
6125: INTO l_person_id
6126: FROM per_all_assignments_f
6127: WHERE assignment_id = p_assignment_id
6128: AND ROWNUM < 2;
6129:
6130: RETURN l_person_id;
6138: --============================================================================
6139: --Function to derive the code for person detail changes
6140: --============================================================================
6141: FUNCTION Get_Change_CD_PER
6142: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
6143: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
6144: ,p_effective_date IN DATE
6145: ,p_error_message OUT NOCOPY VARCHAR2
6146: ,p_data_element_value OUT NOCOPY VARCHAR2)
6139: --Function to derive the code for person detail changes
6140: --============================================================================
6141: FUNCTION Get_Change_CD_PER
6142: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
6143: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
6144: ,p_effective_date IN DATE
6145: ,p_error_message OUT NOCOPY VARCHAR2
6146: ,p_data_element_value OUT NOCOPY VARCHAR2)
6147: RETURN NUMBER IS
6231: -- =============================================================================
6232: -- Get_Partner_Last_Name:
6233: -- =============================================================================
6234: FUNCTION Get_Partner_Last_Name
6235: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
6236: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
6237: ,p_effective_date IN Date
6238: ,p_error_message OUT NOCOPY Varchar2
6239: ,p_data_element_value OUT NOCOPY Varchar2
6232: -- Get_Partner_Last_Name:
6233: -- =============================================================================
6234: FUNCTION Get_Partner_Last_Name
6235: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
6236: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
6237: ,p_effective_date IN Date
6238: ,p_error_message OUT NOCOPY Varchar2
6239: ,p_data_element_value OUT NOCOPY Varchar2
6240: ) RETURN Number IS
6289: -- =============================================================================
6290: -- Get_Gender:
6291: -- =============================================================================
6292: FUNCTION Get_Gender
6293: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
6294: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
6295: ,p_effective_date IN Date
6296: ,p_error_message OUT NOCOPY Varchar2
6297: ,p_data_element_value OUT NOCOPY Varchar2
6290: -- Get_Gender:
6291: -- =============================================================================
6292: FUNCTION Get_Gender
6293: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
6294: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
6295: ,p_effective_date IN Date
6296: ,p_error_message OUT NOCOPY Varchar2
6297: ,p_data_element_value OUT NOCOPY Varchar2
6298: ) RETURN Number IS
6334: -- =============================================================================
6335: -- Get_Partner_Prefix:
6336: -- =============================================================================
6337: FUNCTION Get_Partner_Prefix
6338: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
6339: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
6340: ,p_effective_date IN Date
6341: ,p_error_message OUT NOCOPY Varchar2
6342: ,p_data_element_value OUT NOCOPY Varchar2
6335: -- Get_Partner_Prefix:
6336: -- =============================================================================
6337: FUNCTION Get_Partner_Prefix
6338: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
6339: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
6340: ,p_effective_date IN Date
6341: ,p_error_message OUT NOCOPY Varchar2
6342: ,p_data_element_value OUT NOCOPY Varchar2
6343: ) RETURN Number IS
6391: -- =============================================================================
6392: -- Get_Add_Fem_EE:
6393: -- =============================================================================
6394: FUNCTION Get_Add_Fem_EE
6395: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
6396: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
6397: ,p_effective_date IN Date
6398: ,p_error_message OUT NOCOPY Varchar2
6399: ,p_data_element_value OUT NOCOPY Varchar2) RETURN Number IS
6392: -- Get_Add_Fem_EE:
6393: -- =============================================================================
6394: FUNCTION Get_Add_Fem_EE
6395: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
6396: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
6397: ,p_effective_date IN Date
6398: ,p_error_message OUT NOCOPY Varchar2
6399: ,p_data_element_value OUT NOCOPY Varchar2) RETURN Number IS
6400:
6433: -- =============================================================================
6434: -- Get_EE_Num:
6435: -- =============================================================================
6436: FUNCTION Get_EE_Num
6437: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
6438: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
6439: ,p_effective_date IN Date
6440: ,p_error_message OUT NOCOPY Varchar2
6441: ,p_data_element_value OUT NOCOPY Varchar2
6434: -- Get_EE_Num:
6435: -- =============================================================================
6436: FUNCTION Get_EE_Num
6437: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
6438: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
6439: ,p_effective_date IN Date
6440: ,p_error_message OUT NOCOPY Varchar2
6441: ,p_data_element_value OUT NOCOPY Varchar2
6442: ) RETURN Number IS
6463: -- =============================================================================
6464: -- Get_Old_Asg_Seq_Num:
6465: -- =============================================================================
6466: FUNCTION Get_Old_Asg_Seq_Num
6467: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
6468: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
6469: ,p_effective_date IN DATE
6470: ,p_error_message OUT NOCOPY VARCHAR2
6471: ,p_data_element_value OUT NOCOPY VARCHAR2
6464: -- Get_Old_Asg_Seq_Num:
6465: -- =============================================================================
6466: FUNCTION Get_Old_Asg_Seq_Num
6467: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
6468: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
6469: ,p_effective_date IN DATE
6470: ,p_error_message OUT NOCOPY VARCHAR2
6471: ,p_data_element_value OUT NOCOPY VARCHAR2
6472: ) RETURN NUMBER IS
6505: -- =============================================================================
6506: -- Get_ABP_ER_Num
6507: -- =============================================================================
6508: FUNCTION Get_ABP_ER_Num
6509: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
6510: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
6511: ,p_effective_date IN Date
6512: ,p_fetch_code IN Varchar2
6513: ,p_error_message OUT NOCOPY Varchar2
6506: -- Get_ABP_ER_Num
6507: -- =============================================================================
6508: FUNCTION Get_ABP_ER_Num
6509: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
6510: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
6511: ,p_effective_date IN Date
6512: ,p_fetch_code IN Varchar2
6513: ,p_error_message OUT NOCOPY Varchar2
6514: ,p_data_element_value OUT NOCOPY Varchar2
6567: -- Cursor to fetch the organization of the EE Asg.
6568: --
6569: CURSOR csr_get_org_id IS
6570: SELECT organization_id
6571: FROM per_all_assignments_f
6572: WHERE assignment_id = p_assignment_id
6573: AND business_group_id = p_business_group_id
6574: ORDER BY effective_end_date DESC;
6575:
6751: -- =============================================================================
6752: -- Get_Old_Ee_Num
6753: -- =============================================================================
6754: FUNCTION Get_Old_Ee_Num
6755: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
6756: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
6757: ,p_effective_date IN DATE
6758: ,p_error_message OUT NOCOPY VARCHAR2
6759: ,p_data_element_value OUT NOCOPY VARCHAR2
6752: -- Get_Old_Ee_Num
6753: -- =============================================================================
6754: FUNCTION Get_Old_Ee_Num
6755: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
6756: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
6757: ,p_effective_date IN DATE
6758: ,p_error_message OUT NOCOPY VARCHAR2
6759: ,p_data_element_value OUT NOCOPY VARCHAR2
6760: ) RETURN NUMBER IS
6805: --============================================================================
6806: -- Get the code for change in address for the EE
6807: --============================================================================
6808: FUNCTION Get_Change_CD_Addr
6809: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
6810: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
6811: ,p_effective_date IN DATE
6812: ,p_error_message OUT NOCOPY VARCHAR2
6813: ,p_data_element_value OUT NOCOPY VARCHAR2)
6806: -- Get the code for change in address for the EE
6807: --============================================================================
6808: FUNCTION Get_Change_CD_Addr
6809: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
6810: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
6811: ,p_effective_date IN DATE
6812: ,p_error_message OUT NOCOPY VARCHAR2
6813: ,p_data_element_value OUT NOCOPY VARCHAR2)
6814: RETURN Number IS
6851: -- =============================================================================
6852: -- Get_Street
6853: -- =============================================================================
6854: FUNCTION Get_Street
6855: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
6856: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
6857: ,p_effective_date IN Date
6858: ,p_error_message OUT NOCOPY Varchar2
6859: ,p_data_element_value OUT NOCOPY Varchar2
6852: -- Get_Street
6853: -- =============================================================================
6854: FUNCTION Get_Street
6855: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
6856: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
6857: ,p_effective_date IN Date
6858: ,p_error_message OUT NOCOPY Varchar2
6859: ,p_data_element_value OUT NOCOPY Varchar2
6860: ) RETURN Number IS
6902: -- =============================================================================
6903: -- Get_House_Num
6904: -- =============================================================================
6905: FUNCTION Get_House_Num
6906: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
6907: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
6908: ,p_effective_date IN Date
6909: ,p_error_message OUT NOCOPY Varchar2
6910: ,p_data_element_value OUT NOCOPY Varchar2
6903: -- Get_House_Num
6904: -- =============================================================================
6905: FUNCTION Get_House_Num
6906: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
6907: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
6908: ,p_effective_date IN Date
6909: ,p_error_message OUT NOCOPY Varchar2
6910: ,p_data_element_value OUT NOCOPY Varchar2
6911: ) RETURN Number IS
6956: -- =============================================================================
6957: -- Get_Addnl_House_Num
6958: -- =============================================================================
6959: FUNCTION Get_Addnl_House_Num
6960: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
6961: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
6962: ,p_effective_date IN Date
6963: ,p_error_message OUT NOCOPY Varchar2
6964: ,p_data_element_value OUT NOCOPY Varchar2
6957: -- Get_Addnl_House_Num
6958: -- =============================================================================
6959: FUNCTION Get_Addnl_House_Num
6960: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
6961: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
6962: ,p_effective_date IN Date
6963: ,p_error_message OUT NOCOPY Varchar2
6964: ,p_data_element_value OUT NOCOPY Varchar2
6965: ) RETURN Number IS
7011: -- =============================================================================
7012: -- Get_Postal_Code
7013: -- =============================================================================
7014: FUNCTION Get_Postal_Code
7015: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
7016: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
7017: ,p_effective_date IN Date
7018: ,p_error_message OUT NOCOPY Varchar2
7019: ,p_data_element_value OUT NOCOPY Varchar2
7012: -- Get_Postal_Code
7013: -- =============================================================================
7014: FUNCTION Get_Postal_Code
7015: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
7016: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
7017: ,p_effective_date IN Date
7018: ,p_error_message OUT NOCOPY Varchar2
7019: ,p_data_element_value OUT NOCOPY Varchar2
7020: ) RETURN Number IS
7071: -- =============================================================================
7072: -- Get_City
7073: -- =============================================================================
7074: FUNCTION Get_City
7075: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
7076: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
7077: ,p_effective_date IN Date
7078: ,p_error_message OUT NOCOPY Varchar2
7079: ,p_data_element_value OUT NOCOPY Varchar2
7072: -- Get_City
7073: -- =============================================================================
7074: FUNCTION Get_City
7075: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
7076: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
7077: ,p_effective_date IN Date
7078: ,p_error_message OUT NOCOPY Varchar2
7079: ,p_data_element_value OUT NOCOPY Varchar2
7080: ) RETURN Number IS
7155: -- =============================================================================
7156: -- Get_Foreign_Country
7157: -- =============================================================================
7158: FUNCTION Get_Foreign_Country
7159: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
7160: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
7161: ,p_effective_date IN Date
7162: ,p_error_message OUT NOCOPY Varchar2
7163: ,p_data_element_value OUT NOCOPY Varchar2
7156: -- Get_Foreign_Country
7157: -- =============================================================================
7158: FUNCTION Get_Foreign_Country
7159: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
7160: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
7161: ,p_effective_date IN Date
7162: ,p_error_message OUT NOCOPY Varchar2
7163: ,p_data_element_value OUT NOCOPY Varchar2
7164: ) RETURN Number IS
7198: -- =============================================================================
7199: -- Get_Marital_Status
7200: -- =============================================================================
7201: FUNCTION Get_Marital_Status
7202: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
7203: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
7204: ,p_effective_date IN Date
7205: ,p_error_message OUT NOCOPY Varchar2
7206: ,p_data_element_value OUT NOCOPY Varchar2
7199: -- Get_Marital_Status
7200: -- =============================================================================
7201: FUNCTION Get_Marital_Status
7202: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
7203: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
7204: ,p_effective_date IN Date
7205: ,p_error_message OUT NOCOPY Varchar2
7206: ,p_data_element_value OUT NOCOPY Varchar2
7207: ) RETURN Number IS
7269: -- =============================================================================
7270: -- Get_Pension_Salary -- Function to derive pension salary value for Record 08
7271: -- =============================================================================
7272: FUNCTION Get_Pension_Salary
7273: ( p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
7274: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
7275: ,p_effective_date IN DATE
7276: ,p_balance_name IN pay_balance_types.balance_name%TYPE
7277: ,p_asg_act IN NUMBER
7270: -- Get_Pension_Salary -- Function to derive pension salary value for Record 08
7271: -- =============================================================================
7272: FUNCTION Get_Pension_Salary
7273: ( p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
7274: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
7275: ,p_effective_date IN DATE
7276: ,p_balance_name IN pay_balance_types.balance_name%TYPE
7277: ,p_asg_act IN NUMBER
7278: ,p_error_message OUT NOCOPY VARCHAR2
7400: -- =============================================================================
7401: -- Get_Contribution_Amount for Record 12/41
7402: -- =============================================================================
7403: FUNCTION Get_Contribution_Amount(
7404: p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
7405: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
7406: ,p_effective_date IN Date
7407: ,p_record_number IN Number
7408: ,p_error_message OUT NOCOPY Varchar2
7401: -- Get_Contribution_Amount for Record 12/41
7402: -- =============================================================================
7403: FUNCTION Get_Contribution_Amount(
7404: p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
7405: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
7406: ,p_effective_date IN Date
7407: ,p_record_number IN Number
7408: ,p_error_message OUT NOCOPY Varchar2
7409: ,p_data_element_value OUT NOCOPY Varchar2) RETURN Number IS
7447: -- This Function gets the sub categories
7448: -- =============================================================================
7449:
7450: FUNCTION Get_Sub_Cat_12
7451: ( p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
7452: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
7453: ,p_effective_date IN Date
7454: ,p_error_message OUT NOCOPY Varchar2
7455: ,p_data_element_value OUT NOCOPY Varchar2
7448: -- =============================================================================
7449:
7450: FUNCTION Get_Sub_Cat_12
7451: ( p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
7452: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
7453: ,p_effective_date IN Date
7454: ,p_error_message OUT NOCOPY Varchar2
7455: ,p_data_element_value OUT NOCOPY Varchar2
7456: ) RETURN Number IS
7480: -- =============================================================================
7481: -- Get_Pension_Start_Year
7482: -- =============================================================================
7483: FUNCTION Get_Pension_Start_Year
7484: ( p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
7485: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
7486: ,p_effective_date IN DATE
7487: ,p_start_date IN DATE
7488: ,p_end_date IN DATE
7481: -- Get_Pension_Start_Year
7482: -- =============================================================================
7483: FUNCTION Get_Pension_Start_Year
7484: ( p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
7485: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
7486: ,p_effective_date IN DATE
7487: ,p_start_date IN DATE
7488: ,p_end_date IN DATE
7489: ,p_error_message OUT NOCOPY VARCHAR2
7591: END Get_Pension_Start_Year;
7592:
7593:
7594: FUNCTION Get_Pension_Basis_Year
7595: ( p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
7596: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
7597: ,p_effective_date IN Date
7598: ,p_record_number IN Number
7599: ,p_error_message OUT NOCOPY Varchar2
7592:
7593:
7594: FUNCTION Get_Pension_Basis_Year
7595: ( p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
7596: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
7597: ,p_effective_date IN Date
7598: ,p_record_number IN Number
7599: ,p_error_message OUT NOCOPY Varchar2
7600: ,p_data_element_value OUT NOCOPY Varchar2
7648: RETURN -1;
7649: END Get_Pension_Basis_Year;
7650:
7651: FUNCTION Get_Month_Contribution_Base
7652: ( p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
7653: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
7654: ,p_effective_date IN Date
7655: ,p_record_number IN Number
7656: ,p_error_message OUT NOCOPY Varchar2
7649: END Get_Pension_Basis_Year;
7650:
7651: FUNCTION Get_Month_Contribution_Base
7652: ( p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
7653: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
7654: ,p_effective_date IN Date
7655: ,p_record_number IN Number
7656: ,p_error_message OUT NOCOPY Varchar2
7657: ,p_data_element_value OUT NOCOPY Varchar2
7708:
7709: END Get_Month_Contribution_Base;
7710:
7711: FUNCTION Get_Year_Contribution_Amt
7712: ( p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
7713: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
7714: ,p_effective_date IN Date
7715: ,p_record_number IN Number
7716: ,p_error_message OUT NOCOPY Varchar2
7709: END Get_Month_Contribution_Base;
7710:
7711: FUNCTION Get_Year_Contribution_Amt
7712: ( p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
7713: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
7714: ,p_effective_date IN Date
7715: ,p_record_number IN Number
7716: ,p_error_message OUT NOCOPY Varchar2
7717: ,p_data_element_value OUT NOCOPY Varchar2
7764: RETURN -1;
7765: END Get_Year_Contribution_Amt;
7766:
7767: FUNCTION Get_Month_Contribution_Amt
7768: ( p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
7769: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
7770: ,p_effective_date IN Date
7771: ,p_record_number IN Number
7772: ,p_error_message OUT NOCOPY Varchar2
7765: END Get_Year_Contribution_Amt;
7766:
7767: FUNCTION Get_Month_Contribution_Amt
7768: ( p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
7769: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
7770: ,p_effective_date IN Date
7771: ,p_record_number IN Number
7772: ,p_error_message OUT NOCOPY Varchar2
7773: ,p_data_element_value OUT NOCOPY Varchar2
7826: --============================================================================
7827: -- Function to derive the display criteria for Record 01
7828: --============================================================================
7829: FUNCTION Record01_Display_Criteria
7830: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
7831: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
7832: ,p_effective_date IN DATE
7833: ,p_error_message OUT NOCOPY VARCHAR2
7834: ,p_data_element_value OUT NOCOPY VARCHAR2)
7827: -- Function to derive the display criteria for Record 01
7828: --============================================================================
7829: FUNCTION Record01_Display_Criteria
7830: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
7831: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
7832: ,p_effective_date IN DATE
7833: ,p_error_message OUT NOCOPY VARCHAR2
7834: ,p_data_element_value OUT NOCOPY VARCHAR2)
7835: RETURN NUMBER IS
7907: --============================================================================
7908: -- Record02_Display_Criteria
7909: --============================================================================
7910: FUNCTION Record02_Display_Criteria
7911: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
7912: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
7913: ,p_effective_date IN Date
7914: ,p_error_message OUT NOCOPY Varchar2
7915: ,p_data_element_value OUT NOCOPY Varchar2
7908: -- Record02_Display_Criteria
7909: --============================================================================
7910: FUNCTION Record02_Display_Criteria
7911: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
7912: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
7913: ,p_effective_date IN Date
7914: ,p_error_message OUT NOCOPY Varchar2
7915: ,p_data_element_value OUT NOCOPY Varchar2
7916: ) RETURN Number IS
8107: --============================================================================
8108: -- Function to derive the display criteria for Record 04
8109: --============================================================================
8110: FUNCTION Record04_Display_Criteria
8111: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
8112: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
8113: ,p_effective_date IN DATE
8114: ,p_error_message OUT NOCOPY VARCHAR2
8115: ,p_data_element_value OUT NOCOPY VARCHAR2)
8108: -- Function to derive the display criteria for Record 04
8109: --============================================================================
8110: FUNCTION Record04_Display_Criteria
8111: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
8112: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
8113: ,p_effective_date IN DATE
8114: ,p_error_message OUT NOCOPY VARCHAR2
8115: ,p_data_element_value OUT NOCOPY VARCHAR2)
8116:
8517: --============================================================================
8518: -- Function to derive the display criteria for Record 08
8519: --============================================================================
8520: FUNCTION Record08_Display_Criteria
8521: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
8522: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
8523: ,p_effective_date IN DATE
8524: ,p_error_message OUT NOCOPY VARCHAR2
8525: ,p_data_element_value OUT NOCOPY VARCHAR2)
8518: -- Function to derive the display criteria for Record 08
8519: --============================================================================
8520: FUNCTION Record08_Display_Criteria
8521: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
8522: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
8523: ,p_effective_date IN DATE
8524: ,p_error_message OUT NOCOPY VARCHAR2
8525: ,p_data_element_value OUT NOCOPY VARCHAR2)
8526: RETURN NUMBER IS
8748: --=============================================================================
8749: -- Function to derive the display criteria for Record 09
8750: --=============================================================================
8751: FUNCTION Record09_Display_Criteria
8752: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
8753: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
8754: ,p_effective_date IN DATE
8755: ,p_error_message OUT NOCOPY VARCHAR2
8756: ,p_data_element_value OUT NOCOPY VARCHAR2)
8749: -- Function to derive the display criteria for Record 09
8750: --=============================================================================
8751: FUNCTION Record09_Display_Criteria
8752: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
8753: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
8754: ,p_effective_date IN DATE
8755: ,p_error_message OUT NOCOPY VARCHAR2
8756: ,p_data_element_value OUT NOCOPY VARCHAR2)
8757: RETURN NUMBER IS
8782: --=============================================================================
8783: -- Function to check if Record12 needs to be displayed
8784: --=============================================================================
8785: FUNCTION Record12_Display_Criteria
8786: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
8787: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
8788: ,p_effective_date IN DATE
8789: ,p_error_message OUT NOCOPY VARCHAR2
8790: ,p_data_element_value OUT NOCOPY VARCHAR2)
8783: -- Function to check if Record12 needs to be displayed
8784: --=============================================================================
8785: FUNCTION Record12_Display_Criteria
8786: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
8787: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
8788: ,p_effective_date IN DATE
8789: ,p_error_message OUT NOCOPY VARCHAR2
8790: ,p_data_element_value OUT NOCOPY VARCHAR2)
8791: RETURN NUMBER IS
8815: --=============================================================================
8816: -- Function to derive the display criteria for Record 20
8817: --=============================================================================
8818: FUNCTION Record20_Display_Criteria
8819: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
8820: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
8821: ,p_effective_date IN DATE
8822: ,p_error_message OUT NOCOPY VARCHAR2
8823: ,p_data_element_value OUT NOCOPY VARCHAR2)
8816: -- Function to derive the display criteria for Record 20
8817: --=============================================================================
8818: FUNCTION Record20_Display_Criteria
8819: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
8820: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
8821: ,p_effective_date IN DATE
8822: ,p_error_message OUT NOCOPY VARCHAR2
8823: ,p_data_element_value OUT NOCOPY VARCHAR2)
8824: RETURN NUMBER IS
8838: --============================================================================
8839: -- Function to derive the display criteria for Record 21
8840: --============================================================================
8841: FUNCTION Record21_Display_Criteria
8842: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
8843: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
8844: ,p_effective_date IN DATE
8845: ,p_error_message OUT NOCOPY VARCHAR2
8846: ,p_data_element_value OUT NOCOPY VARCHAR2)
8839: -- Function to derive the display criteria for Record 21
8840: --============================================================================
8841: FUNCTION Record21_Display_Criteria
8842: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
8843: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
8844: ,p_effective_date IN DATE
8845: ,p_error_message OUT NOCOPY VARCHAR2
8846: ,p_data_element_value OUT NOCOPY VARCHAR2)
8847: RETURN NUMBER IS
8861: --============================================================================
8862: -- Function to derive the display criteria for Record 22
8863: --============================================================================
8864: FUNCTION Record22_Display_Criteria
8865: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
8866: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
8867: ,p_effective_date IN DATE
8868: ,p_error_message OUT NOCOPY VARCHAR2
8869: ,p_data_element_value OUT NOCOPY VARCHAR2)
8862: -- Function to derive the display criteria for Record 22
8863: --============================================================================
8864: FUNCTION Record22_Display_Criteria
8865: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
8866: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
8867: ,p_effective_date IN DATE
8868: ,p_error_message OUT NOCOPY VARCHAR2
8869: ,p_data_element_value OUT NOCOPY VARCHAR2)
8870: RETURN NUMBER IS
8884: --============================================================================
8885: -- Function to derive the display criteria for Record 05
8886: --============================================================================
8887: FUNCTION Record05_Display_Criteria
8888: ( p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
8889: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
8890: ,p_effective_date IN DATE
8891: ,p_error_message OUT NOCOPY VARCHAR2
8892: ,p_data_element_value OUT NOCOPY VARCHAR2)
8885: -- Function to derive the display criteria for Record 05
8886: --============================================================================
8887: FUNCTION Record05_Display_Criteria
8888: ( p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
8889: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
8890: ,p_effective_date IN DATE
8891: ,p_error_message OUT NOCOPY VARCHAR2
8892: ,p_data_element_value OUT NOCOPY VARCHAR2)
8893: RETURN NUMBER IS
8931: --============================================================================
8932: --This is used to decide the Record40_30 hide or show
8933: --============================================================================
8934: FUNCTION Record30_40_Display_Criteria
8935: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
8936: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
8937: ,p_effective_date IN Date
8938: ,p_sub_cat IN Varchar2
8939: ,p_error_message OUT NOCOPY Varchar2
8932: --This is used to decide the Record40_30 hide or show
8933: --============================================================================
8934: FUNCTION Record30_40_Display_Criteria
8935: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
8936: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
8937: ,p_effective_date IN Date
8938: ,p_sub_cat IN Varchar2
8939: ,p_error_message OUT NOCOPY Varchar2
8940: ,p_data_element_value OUT NOCOPY Varchar2
8950: AND pty.pension_sub_category = p_sub_cat
8951: AND prmtr_03 = 'Y'
8952: AND Fnd_Number.canonical_to_number(prmtr_10) = p_assignment_id
8953: AND person_id = (SELECT person_id
8954: FROM per_all_assignments_f
8955: WHERE assignment_id = p_assignment_id
8956: AND p_effective_date BETWEEN effective_start_date
8957: AND effective_end_date
8958: )
8980: AND prmtr_03 = 'Y'
8981: AND Fnd_Number.canonical_to_number(prmtr_10) = p_assignment_id
8982: AND pty.pension_sub_category = p_sub_cat
8983: AND person_id = (SELECT person_id
8984: FROM per_all_assignments_f
8985: WHERE assignment_id = p_assignment_id
8986: AND p_effective_date BETWEEN effective_start_date
8987: AND effective_end_date
8988: )
9041:
9042: --cursor to find the org id for the current asg
9043: CURSOR c_find_org_id IS
9044: SELECT organization_id
9045: FROM per_all_assignments_f
9046: WHERE assignment_id = p_assignment_id
9047: AND p_effective_date BETWEEN effective_start_date
9048: AND effective_end_date;
9049:
9049:
9050: -- Cursor to get the hire date of the person
9051: CURSOR c_hire_dt IS
9052: SELECT max(date_start)
9053: FROM per_all_assignments_f asg
9054: ,per_periods_of_service pps
9055: WHERE pps.person_id = asg.person_id
9056: AND asg.assignment_id = p_assignment_id
9057: AND pps.business_group_id = p_business_group_id
9228: --============================================================================
9229: --This is used to decide the Record41_31 hide or show
9230: --============================================================================
9231: FUNCTION Record31_41_Display_Criteria
9232: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
9233: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
9234: ,p_effective_date IN Date
9235: ,p_record_number IN Number
9236: ,p_error_message OUT NOCOPY Varchar2
9229: --This is used to decide the Record41_31 hide or show
9230: --============================================================================
9231: FUNCTION Record31_41_Display_Criteria
9232: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
9233: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
9234: ,p_effective_date IN Date
9235: ,p_record_number IN Number
9236: ,p_error_message OUT NOCOPY Varchar2
9237: ,p_data_element_value OUT NOCOPY Varchar2
9294: --This is used to derive the participation end date in the case , when enrollment has
9295: --come from the ORG EIT on a start of employment
9296: --============================================================================
9297: FUNCTION Get_Participation_End
9298: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
9299: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
9300: ,p_pension_type_id IN pqp_pension_types_f.pension_type_id%TYPE
9301: ,p_date_earned IN Date
9302: ,p_end_date OUT NOCOPY Date
9295: --come from the ORG EIT on a start of employment
9296: --============================================================================
9297: FUNCTION Get_Participation_End
9298: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
9299: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
9300: ,p_pension_type_id IN pqp_pension_types_f.pension_type_id%TYPE
9301: ,p_date_earned IN Date
9302: ,p_end_date OUT NOCOPY Date
9303: ) RETURN Number IS
9312:
9313: --Cursor to find the org id from the assignment id
9314: CURSOR c_find_org_id IS
9315: SELECT organization_id
9316: FROM per_all_assignments_f
9317: WHERE assignment_id = p_assignment_id
9318: AND Trunc(p_date_earned) BETWEEN effective_start_date AND effective_end_date
9319: AND business_group_id = p_business_group_id;
9320:
9457: --This is used to derive the participation start and end dates and the old start and
9458: --end dates in case of an update for FUR Pensions
9459: --============================================================================
9460: FUNCTION Get_Fur_Participation_Dates
9461: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
9462: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
9463: ,p_fetch_code IN Varchar2
9464: ,p_effective_date IN Date
9465: ,p_error_message OUT NOCOPY Varchar2
9458: --end dates in case of an update for FUR Pensions
9459: --============================================================================
9460: FUNCTION Get_Fur_Participation_Dates
9461: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
9462: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
9463: ,p_fetch_code IN Varchar2
9464: ,p_effective_date IN Date
9465: ,p_error_message OUT NOCOPY Varchar2
9466: ,p_data_element_value OUT NOCOPY Varchar2
9476: AND pty.pension_sub_category = 'FUR_S'
9477: AND prmtr_03 = 'Y'
9478: AND Fnd_Number.canonical_to_number(prmtr_10) = p_assignment_id
9479: AND person_id = (SELECT person_id
9480: FROM per_all_assignments_f
9481: WHERE assignment_id = p_assignment_id
9482: AND p_effective_date BETWEEN effective_start_date
9483: AND effective_end_date
9484: )
9506: AND prmtr_03 = 'Y'
9507: AND Fnd_Number.canonical_to_number(prmtr_10) = p_assignment_id
9508: AND pty.pension_sub_category = 'FUR_S'
9509: AND person_id = (SELECT person_id
9510: FROM per_all_assignments_f
9511: WHERE assignment_id = p_assignment_id
9512: AND p_effective_date BETWEEN effective_start_date
9513: AND effective_end_date
9514: )
9567:
9568: --cursor to find the org id for the current asg
9569: CURSOR c_find_org_id IS
9570: SELECT organization_id
9571: FROM per_all_assignments_f
9572: WHERE assignment_id = p_assignment_id
9573: AND p_effective_date BETWEEN effective_start_date
9574: AND effective_end_date;
9575:
9575:
9576: -- Cursor to get the hire date of the person
9577: CURSOR c_hire_dt IS
9578: SELECT max(date_start)
9579: FROM per_all_assignments_f asg
9580: ,per_periods_of_service pps
9581: WHERE pps.person_id = asg.person_id
9582: AND asg.assignment_id = p_assignment_id
9583: AND pps.business_group_id = p_business_group_id
9875: -- This function returns the kind of contribution for PPP Pensions
9876: -- for a particular effective date.
9877: --============================================================================
9878: FUNCTION Get_PPP_Kind
9879: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
9880: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
9881: ,p_effective_date IN DATE
9882: ,p_current IN VARCHAR2
9883: ,p_error_message OUT NOCOPY VARCHAR2
9876: -- for a particular effective date.
9877: --============================================================================
9878: FUNCTION Get_PPP_Kind
9879: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
9880: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
9881: ,p_effective_date IN DATE
9882: ,p_current IN VARCHAR2
9883: ,p_error_message OUT NOCOPY VARCHAR2
9884: ,p_data_element_value OUT NOCOPY VARCHAR2
9891: l_asg_exist NUMBER;
9892:
9893: CURSOR c_ppp_org IS
9894: SELECT organization_id
9895: FROM per_all_assignments_f
9896: WHERE assignment_id = p_assignment_id
9897: AND TRUNC(p_effective_date) BETWEEN effective_start_date
9898: AND effective_end_date;
9899:
9946: AND pty.pension_sub_category = 'PPP';*/
9947:
9948: CURSOR c_rr_cur IS
9949: SELECT 1
9950: FROM per_all_assignments_f paf,
9951: hr_organization_information hoi,
9952: pqp_pension_types_f pty,
9953: pay_all_payrolls_f ppf
9954: WHERE paf.assignment_id = p_assignment_id
10053:
10054: -- Please Note : Legislative rules do not allow any other combination
10055: --============================================================================
10056: FUNCTION Get_Fpu_Kind
10057: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
10058: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
10059: ,p_effective_date IN Date
10060: ,p_error_message OUT NOCOPY Varchar2
10061: ,p_data_element_value OUT NOCOPY Varchar2
10054: -- Please Note : Legislative rules do not allow any other combination
10055: --============================================================================
10056: FUNCTION Get_Fpu_Kind
10057: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
10058: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
10059: ,p_effective_date IN Date
10060: ,p_error_message OUT NOCOPY Varchar2
10061: ,p_data_element_value OUT NOCOPY Varchar2
10062: ) RETURN NUMBER IS
10098: ,'FPU_E','C'
10099: ,'FPU_R','A'
10100: ,'FPU_S','S'
10101: ,'FPU_T' ,'B') fpu_code
10102: FROM per_all_assignments_f paf,
10103: hr_organization_information hoi,
10104: pqp_pension_types_f pty,
10105: pay_all_payrolls_f ppf
10106: WHERE paf.assignment_id = p_assignment_id
10220: --============================================================================
10221: --This function returns the kind of contribution for OPNP Pensions, from the ASG EIT
10222: --============================================================================
10223: FUNCTION Get_Opnp_Kind
10224: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
10225: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
10226: ,p_effective_date IN Date
10227: ,p_error_message OUT NOCOPY Varchar2
10228: ,p_data_element_value OUT NOCOPY Varchar2
10221: --This function returns the kind of contribution for OPNP Pensions, from the ASG EIT
10222: --============================================================================
10223: FUNCTION Get_Opnp_Kind
10224: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
10225: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
10226: ,p_effective_date IN Date
10227: ,p_error_message OUT NOCOPY Varchar2
10228: ,p_data_element_value OUT NOCOPY Varchar2
10229: ) RETURN Number IS
10265: -- this function currently returns the start and end date of retro
10266: -- participation.
10267: -- ============================================================================
10268: FUNCTION Get_Retro_Kind_Of_Ptpn
10269: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
10270: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
10271: ,p_effective_date IN DATE
10272: ,p_retro_kind_ptpn OUT NOCOPY t_retro_ptpn_kind
10273: ,p_error_message OUT NOCOPY VARCHAR2
10266: -- participation.
10267: -- ============================================================================
10268: FUNCTION Get_Retro_Kind_Of_Ptpn
10269: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
10270: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
10271: ,p_effective_date IN DATE
10272: ,p_retro_kind_ptpn OUT NOCOPY t_retro_ptpn_kind
10273: ,p_error_message OUT NOCOPY VARCHAR2
10274: ) RETURN NUMBER IS
10386: -- this function currently returns the start and end date of retro
10387: -- participation.
10388: -- ============================================================================
10389: FUNCTION Get_Retro_Participation
10390: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
10391: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
10392: ,p_sub_cat IN VARCHAR2
10393: ,p_effective_date IN DATE
10394: ,p_retro_ptpn OUT NOCOPY t_retro_ptpn
10387: -- participation.
10388: -- ============================================================================
10389: FUNCTION Get_Retro_Participation
10390: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
10391: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
10392: ,p_sub_cat IN VARCHAR2
10393: ,p_effective_date IN DATE
10394: ,p_retro_ptpn OUT NOCOPY t_retro_ptpn
10395: ,p_error_message OUT NOCOPY VARCHAR2
10524: END Get_Retro_Participation;
10525:
10526:
10527: /*PROCEDURE Populate_Term_Rev_Data
10528: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
10529: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
10530: ,p_end_date IN DATE
10531: ,p_start_date IN DATE
10532: ,p_error_message OUT NOCOPY VARCHAR2
10525:
10526:
10527: /*PROCEDURE Populate_Term_Rev_Data
10528: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
10529: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
10530: ,p_end_date IN DATE
10531: ,p_start_date IN DATE
10532: ,p_error_message OUT NOCOPY VARCHAR2
10533: ) IS
10627: -- Termination/ participation end
10628: -- Political leave
10629: --============================================================================
10630: FUNCTION Get_Rec05_Participation
10631: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
10632: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
10633: ,p_fetch_code IN VARCHAR2
10634: ,p_effective_date IN DATE
10635: ,p_error_message OUT NOCOPY VARCHAR2
10628: -- Political leave
10629: --============================================================================
10630: FUNCTION Get_Rec05_Participation
10631: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
10632: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
10633: ,p_fetch_code IN VARCHAR2
10634: ,p_effective_date IN DATE
10635: ,p_error_message OUT NOCOPY VARCHAR2
10636: ,p_data_element_value OUT NOCOPY VARCHAR2)
10639: -- Cursor to find the org id for the current asg
10640: --
10641: CURSOR c_find_org_id IS
10642: SELECT organization_id
10643: FROM per_all_assignments_f
10644: WHERE assignment_id = p_assignment_id
10645: AND p_effective_date BETWEEN effective_start_date
10646: AND effective_end_date;
10647: --
10660: -- Cursor to get the hire date of the person
10661: --
10662: CURSOR c_hire_dt IS
10663: SELECT max(date_start)
10664: FROM per_all_assignments_f asg
10665: ,per_periods_of_service pps
10666: WHERE pps.person_id = asg.person_id
10667: AND asg.assignment_id = p_assignment_id
10668: AND pps.business_group_id = p_business_group_id
10702: CURSOR c_get_end_reason IS
10703: SELECT /*decode(nvl(leaving_reason ,'A'),'D','I','A') term_reas*/
10704: decode(nvl(leaving_reason ,'A'),'D','I','B','B','A') term_reas
10705: FROM per_periods_of_service pps,
10706: per_all_assignments_f asg
10707: WHERE asg.period_of_service_id = pps.period_of_service_id
10708: AND assignment_id = p_assignment_id
10709: AND p_effective_date between effective_start_date and
10710: effective_end_date ;
10876: order by asg.effective_start_date;
10877:
10878: CURSOR c_pay_id IS
10879: SELECT payroll_id
10880: FROM per_all_assignments_f
10881: WHERE assignment_id = p_assignment_id
10882: AND p_effective_date BETWEEN effective_start_date AND
10883: effective_end_date;
10884:
10885: --6501898
10886: --Commented for 6959318
10887: /*CURSOR c_get_term_dt IS
10888: SELECT MIN(effective_start_date) - 1 term_date
10889: FROM per_all_assignments_f asg
10890: WHERE assignment_id = p_assignment_id
10891: AND assignment_status_type_id IN (SELECT assignment_status_type_id
10892: FROM per_assignment_status_types
10893: WHERE per_system_status = 'TERM_ASSIGN'
11817: --This is used to derive the participation start and end dates and the old start and
11818: --end dates in case of an update for IPAP Pensions
11819: --============================================================================
11820: FUNCTION Get_Ipap_Participation_Dates
11821: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
11822: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
11823: ,p_fetch_code IN Varchar2
11824: ,p_effective_date IN Date
11825: ,p_error_message OUT NOCOPY Varchar2
11818: --end dates in case of an update for IPAP Pensions
11819: --============================================================================
11820: FUNCTION Get_Ipap_Participation_Dates
11821: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
11822: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
11823: ,p_fetch_code IN Varchar2
11824: ,p_effective_date IN Date
11825: ,p_error_message OUT NOCOPY Varchar2
11826: ,p_data_element_value OUT NOCOPY Varchar2
11836: AND pty.pension_sub_category = 'IPAP'
11837: AND prmtr_03 = 'Y'
11838: AND Fnd_Number.canonical_to_number(prmtr_10) = p_assignment_id
11839: AND person_id = (SELECT person_id
11840: FROM per_all_assignments_f
11841: WHERE assignment_id = p_assignment_id
11842: AND p_effective_date BETWEEN effective_start_date
11843: AND effective_end_date
11844: )
11866: AND prmtr_03 = 'Y'
11867: AND Fnd_Number.canonical_to_number(prmtr_10) = p_assignment_id
11868: AND pty.pension_sub_category = 'IPAP'
11869: AND person_id = (SELECT person_id
11870: FROM per_all_assignments_f
11871: WHERE assignment_id = p_assignment_id
11872: AND p_effective_date BETWEEN effective_start_date
11873: AND effective_end_date
11874: )
11927:
11928: --cursor to find the org id for the current asg
11929: CURSOR c_find_org_id IS
11930: SELECT organization_id
11931: FROM per_all_assignments_f
11932: WHERE assignment_id = p_assignment_id
11933: AND p_effective_date BETWEEN effective_start_date
11934: AND effective_end_date;
11935:
11935:
11936: -- Cursor to get the hire date of the person
11937: CURSOR c_hire_dt IS
11938: SELECT max(date_start)
11939: FROM per_all_assignments_f asg
11940: ,per_periods_of_service pps
11941: WHERE pps.person_id = asg.person_id
11942: AND asg.assignment_id = p_assignment_id
11943: AND pps.business_group_id = p_business_group_id
12384: --This is used to check if there are any more rows for Record 05 and insert
12385: --those records forcibly
12386: --============================================================================
12387: FUNCTION Process_Mult_Rec05
12388: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
12389: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
12390: ,p_effective_date IN Date
12391: ,p_error_message OUT NOCOPY Varchar2
12392: ,p_data_element_value OUT NOCOPY Varchar2
12385: --those records forcibly
12386: --============================================================================
12387: FUNCTION Process_Mult_Rec05
12388: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
12389: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
12390: ,p_effective_date IN Date
12391: ,p_error_message OUT NOCOPY Varchar2
12392: ,p_data_element_value OUT NOCOPY Varchar2
12393: ) RETURN Number IS
12396: l_new_rec csr_rslt_dtl%ROWTYPE;
12397: l_return_value Number := 1;
12398: l_rcd_id Number;
12399: l_mutli_assig Varchar2(50);
12400: l_asg_type per_all_assignments_f.assignment_type%TYPE;
12401: l_person_id per_all_people_f.person_id%TYPE;
12402: l_assignment_id per_all_assignments_f.assignment_id%TYPE;
12403: l_effective_date Date;
12404:
12398: l_rcd_id Number;
12399: l_mutli_assig Varchar2(50);
12400: l_asg_type per_all_assignments_f.assignment_type%TYPE;
12401: l_person_id per_all_people_f.person_id%TYPE;
12402: l_assignment_id per_all_assignments_f.assignment_id%TYPE;
12403: l_effective_date Date;
12404:
12405: BEGIN
12406: --fetch the record id from the sequence number
12455: -- Process Multiple Record 08. This is necessary for late hires
12456: -- and for change in hire date to the past (ABP Certification only test case).
12457: --=============================================================================
12458: FUNCTION Process_Mult_Rec08
12459: ( p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
12460: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
12461: ,p_effective_date IN DATE
12462: ,p_error_message OUT NOCOPY VARCHAR2
12463: ,p_data_element_value OUT NOCOPY VARCHAR2
12456: -- and for change in hire date to the past (ABP Certification only test case).
12457: --=============================================================================
12458: FUNCTION Process_Mult_Rec08
12459: ( p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
12460: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
12461: ,p_effective_date IN DATE
12462: ,p_error_message OUT NOCOPY VARCHAR2
12463: ,p_data_element_value OUT NOCOPY VARCHAR2
12464: ) RETURN NUMBER IS
12825: -- =============================================================================
12826: -- Process Multiple SubCategories for record 09
12827: -- =============================================================================
12828: FUNCTION Process_Mult_Rec09
12829: ( p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
12830: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
12831: ,p_effective_date IN Date
12832: ,p_error_message OUT NOCOPY Varchar2
12833: ,p_data_element_value OUT NOCOPY Varchar2
12826: -- Process Multiple SubCategories for record 09
12827: -- =============================================================================
12828: FUNCTION Process_Mult_Rec09
12829: ( p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
12830: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
12831: ,p_effective_date IN Date
12832: ,p_error_message OUT NOCOPY Varchar2
12833: ,p_data_element_value OUT NOCOPY Varchar2
12834: ) RETURN Number IS
12895: -- Process Multiple SubCategories for Record 12
12896: -- =============================================================================
12897: FUNCTION Process_Mult_Rec12
12898: (
12899: p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
12900: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
12901: ,p_effective_date IN Date
12902: ,p_error_message OUT NOCOPY Varchar2
12903: ,p_data_element_value OUT NOCOPY Varchar2
12896: -- =============================================================================
12897: FUNCTION Process_Mult_Rec12
12898: (
12899: p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
12900: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
12901: ,p_effective_date IN Date
12902: ,p_error_message OUT NOCOPY Varchar2
12903: ,p_data_element_value OUT NOCOPY Varchar2
12904: ) RETURN Number IS
12969: --This is used to check if there are any more rows for SI participation and insert
12970: --those records forcibly
12971: --============================================================================
12972: FUNCTION Process_Mult_Rec20
12973: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
12974: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
12975: ,p_effective_date IN Date
12976: ,p_error_message OUT NOCOPY Varchar2
12977: ,p_data_element_value OUT NOCOPY Varchar2
12970: --those records forcibly
12971: --============================================================================
12972: FUNCTION Process_Mult_Rec20
12973: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
12974: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
12975: ,p_effective_date IN Date
12976: ,p_error_message OUT NOCOPY Varchar2
12977: ,p_data_element_value OUT NOCOPY Varchar2
12978: ) RETURN Number IS
13041: --This is used to check if there are any more rows for IPAP participation and insert
13042: --those records forcibly
13043: --============================================================================
13044: FUNCTION Process_Mult_Rec30
13045: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
13046: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
13047: ,p_effective_date IN Date
13048: ,p_error_message OUT NOCOPY Varchar2
13049: ,p_data_element_value OUT NOCOPY Varchar2
13042: --those records forcibly
13043: --============================================================================
13044: FUNCTION Process_Mult_Rec30
13045: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
13046: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
13047: ,p_effective_date IN Date
13048: ,p_error_message OUT NOCOPY Varchar2
13049: ,p_data_element_value OUT NOCOPY Varchar2
13050: ) RETURN Number IS
13102: -- This is used to check if there are any additional rows for rec 31
13103: -- This is for future use.
13104: --=============================================================================
13105: FUNCTION Process_Mult_Rec31
13106: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
13107: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
13108: ,p_effective_date IN Date
13109: ,p_error_message OUT NOCOPY Varchar2
13110: ,p_data_element_value OUT NOCOPY Varchar2
13103: -- This is for future use.
13104: --=============================================================================
13105: FUNCTION Process_Mult_Rec31
13106: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
13107: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
13108: ,p_effective_date IN Date
13109: ,p_error_message OUT NOCOPY Varchar2
13110: ,p_data_element_value OUT NOCOPY Varchar2
13111: ) RETURN Number IS
13166: -- This is used to check if there are any more rows for FUR participation and
13167: -- insert those records forcibly
13168: --=============================================================================
13169: FUNCTION Process_Mult_Rec40
13170: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
13171: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
13172: ,p_effective_date IN Date
13173: ,p_error_message OUT NOCOPY Varchar2
13174: ,p_data_element_value OUT NOCOPY Varchar2
13167: -- insert those records forcibly
13168: --=============================================================================
13169: FUNCTION Process_Mult_Rec40
13170: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
13171: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
13172: ,p_effective_date IN Date
13173: ,p_error_message OUT NOCOPY Varchar2
13174: ,p_data_element_value OUT NOCOPY Varchar2
13175: ) RETURN Number IS
13236: -- This is used to check if there are any additional rows for rec 41
13237: -- This is for future use.
13238: --=============================================================================
13239: FUNCTION Process_Mult_Rec41
13240: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
13241: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
13242: ,p_effective_date IN Date
13243: ,p_error_message OUT NOCOPY Varchar2
13244: ,p_data_element_value OUT NOCOPY Varchar2
13237: -- This is for future use.
13238: --=============================================================================
13239: FUNCTION Process_Mult_Rec41
13240: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
13241: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
13242: ,p_effective_date IN Date
13243: ,p_error_message OUT NOCOPY Varchar2
13244: ,p_data_element_value OUT NOCOPY Varchar2
13245: ) RETURN Number IS
13341: --============================================================================
13342: --Function to return the contribution amount towards FUR Pensions
13343: --============================================================================
13344: FUNCTION Get_FUR_Contribution_Amt
13345: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
13346: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
13347: ,p_effective_date IN Date
13348: ,p_error_message OUT NOCOPY Varchar2
13349: ,p_data_element_value OUT NOCOPY Varchar2
13342: --Function to return the contribution amount towards FUR Pensions
13343: --============================================================================
13344: FUNCTION Get_FUR_Contribution_Amt
13345: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
13346: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
13347: ,p_effective_date IN Date
13348: ,p_error_message OUT NOCOPY Varchar2
13349: ,p_data_element_value OUT NOCOPY Varchar2
13350: ) RETURN Number IS
13430: --============================================================================
13431: --Function to return the contribution basis towards FUR/IPAP Pensions
13432: --============================================================================
13433: FUNCTION Get_Contribution_Basis
13434: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
13435: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
13436: ,p_effective_date IN Date
13437: ,p_sub_cat IN Varchar2
13438: ,p_error_message OUT NOCOPY Varchar2
13431: --Function to return the contribution basis towards FUR/IPAP Pensions
13432: --============================================================================
13433: FUNCTION Get_Contribution_Basis
13434: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
13435: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
13436: ,p_effective_date IN Date
13437: ,p_sub_cat IN Varchar2
13438: ,p_error_message OUT NOCOPY Varchar2
13439: ,p_data_element_value OUT NOCOPY Varchar2
13525: -- This Function returns the Basis Contribution amount for any sub cats in
13526: -- rec 09/31/41 that the ee might have paid for .
13527: -- =============================================================================
13528: FUNCTION Get_Basis_Amt
13529: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
13530: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
13531: ,p_effective_date IN Date
13532: ,p_record_number IN Number
13533: ,p_error_message OUT NOCOPY Varchar2
13526: -- rec 09/31/41 that the ee might have paid for .
13527: -- =============================================================================
13528: FUNCTION Get_Basis_Amt
13529: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
13530: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
13531: ,p_effective_date IN Date
13532: ,p_record_number IN Number
13533: ,p_error_message OUT NOCOPY Varchar2
13534: ,p_data_element_value OUT NOCOPY Varchar2)
13581: -- Get_Sub_Cat_09 for Record 09
13582: -- This Function gets the sub categories for rec 09
13583: -- =============================================================================
13584: FUNCTION Get_Sub_Cat_09
13585: ( p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
13586: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
13587: ,p_effective_date IN Date
13588: ,p_error_message OUT NOCOPY Varchar2
13589: ,p_data_element_value OUT NOCOPY Varchar2)
13582: -- This Function gets the sub categories for rec 09
13583: -- =============================================================================
13584: FUNCTION Get_Sub_Cat_09
13585: ( p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
13586: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
13587: ,p_effective_date IN Date
13588: ,p_error_message OUT NOCOPY Varchar2
13589: ,p_data_element_value OUT NOCOPY Varchar2)
13590: RETURN Number IS
13617: -- Get_Basis_Amt_Code for Record 09/31/41
13618: -- This Function gets sign for basis amount in rec 09/31/41
13619: -- =============================================================================
13620: FUNCTION Get_Basis_Amt_Code
13621: ( p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
13622: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
13623: ,p_effective_date IN Date
13624: ,p_record_number IN Number
13625: ,p_error_message OUT NOCOPY Varchar2
13618: -- This Function gets sign for basis amount in rec 09/31/41
13619: -- =============================================================================
13620: FUNCTION Get_Basis_Amt_Code
13621: ( p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
13622: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
13623: ,p_effective_date IN Date
13624: ,p_record_number IN Number
13625: ,p_error_message OUT NOCOPY Varchar2
13626: ,p_data_element_value OUT NOCOPY Varchar2)
13670: -- Get_Contrib_Amt_Code for Record 12/41
13671: -- This Function gets sign for contrib amount in rec 12/41
13672: -- =============================================================================
13673: FUNCTION Get_Contrib_Amt_Code
13674: ( p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
13675: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
13676: ,p_effective_date IN Date
13677: ,p_record_number IN Number
13678: ,p_error_message OUT NOCOPY Varchar2
13671: -- This Function gets sign for contrib amount in rec 12/41
13672: -- =============================================================================
13673: FUNCTION Get_Contrib_Amt_Code
13674: ( p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
13675: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
13676: ,p_effective_date IN Date
13677: ,p_record_number IN Number
13678: ,p_error_message OUT NOCOPY Varchar2
13679: ,p_data_element_value OUT NOCOPY Varchar2)
13724: --============================================================================
13725: --Function to return the contribution amount towards IPAP Pensions
13726: --============================================================================
13727: FUNCTION Get_IPAP_Contribution_Amt
13728: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
13729: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
13730: ,p_effective_date IN Date
13731: ,p_error_message OUT NOCOPY Varchar2
13732: ,p_data_element_value OUT NOCOPY Varchar2
13725: --Function to return the contribution amount towards IPAP Pensions
13726: --============================================================================
13727: FUNCTION Get_IPAP_Contribution_Amt
13728: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
13729: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
13730: ,p_effective_date IN Date
13731: ,p_error_message OUT NOCOPY Varchar2
13732: ,p_data_element_value OUT NOCOPY Varchar2
13733: ) RETURN Number IS
13813: --This function returns the code to indicate whether the basis/contribution is positive
13814: --or negative
13815: --============================================================================
13816: FUNCTION Get_Amt_Code
13817: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
13818: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
13819: ,p_effective_date IN Date
13820: ,p_sub_cat IN Varchar2
13821: ,p_error_message OUT NOCOPY Varchar2
13814: --or negative
13815: --============================================================================
13816: FUNCTION Get_Amt_Code
13817: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
13818: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
13819: ,p_effective_date IN Date
13820: ,p_sub_cat IN Varchar2
13821: ,p_error_message OUT NOCOPY Varchar2
13822: ,p_data_element_value OUT NOCOPY Varchar2
13857: --============================================================================
13858: --This function returns the month of contribution
13859: --============================================================================
13860: FUNCTION Get_Amt_Month
13861: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
13862: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
13863: ,p_effective_date IN Date
13864: ,p_error_message OUT NOCOPY Varchar2
13865: ,p_data_element_value OUT NOCOPY Varchar2
13858: --This function returns the month of contribution
13859: --============================================================================
13860: FUNCTION Get_Amt_Month
13861: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
13862: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
13863: ,p_effective_date IN Date
13864: ,p_error_message OUT NOCOPY Varchar2
13865: ,p_data_element_value OUT NOCOPY Varchar2
13866: ) RETURN Number IS
13894: --============================================================================
13895: --This function returns the year of contribution
13896: --============================================================================
13897: FUNCTION Get_Amt_Year
13898: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
13899: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
13900: ,p_effective_date IN DATE
13901: ,p_error_message OUT NOCOPY VARCHAR2
13902: ,p_data_element_value OUT NOCOPY VARCHAR2)
13895: --This function returns the year of contribution
13896: --============================================================================
13897: FUNCTION Get_Amt_Year
13898: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
13899: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
13900: ,p_effective_date IN DATE
13901: ,p_error_message OUT NOCOPY VARCHAR2
13902: ,p_data_element_value OUT NOCOPY VARCHAR2)
13903: RETURN NUMBER IS
13939: --============================================================================
13940: --This function returns the kind of contribution for FUR Pensions, from the ASG EIT
13941: --============================================================================
13942: FUNCTION Get_Fur_Contribution_Kind
13943: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
13944: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
13945: ,p_effective_date IN Date
13946: ,p_error_message OUT NOCOPY Varchar2
13947: ,p_data_element_value OUT NOCOPY Varchar2
13940: --This function returns the kind of contribution for FUR Pensions, from the ASG EIT
13941: --============================================================================
13942: FUNCTION Get_Fur_Contribution_Kind
13943: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
13944: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
13945: ,p_effective_date IN Date
13946: ,p_error_message OUT NOCOPY Varchar2
13947: ,p_data_element_value OUT NOCOPY Varchar2
13948: ) RETURN Number IS
13989: --=============================================================================
13990: --Function to return the kind of insurance between IPAP and ANW
13991: --=============================================================================
13992: FUNCTION Get_Ins_Cd_Anw_Ipap
13993: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
13994: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
13995: ,p_effective_date IN Date
13996: ,p_error_message OUT NOCOPY Varchar2
13997: ,p_data_element_value OUT NOCOPY Varchar2
13990: --Function to return the kind of insurance between IPAP and ANW
13991: --=============================================================================
13992: FUNCTION Get_Ins_Cd_Anw_Ipap
13993: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
13994: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
13995: ,p_effective_date IN Date
13996: ,p_error_message OUT NOCOPY Varchar2
13997: ,p_data_element_value OUT NOCOPY Varchar2
13998: ) RETURN Number IS
14023: --Function to return the type of insurance between IPAP and ANW
14024: --depending on the kind of insurance
14025: --============================================================================
14026: FUNCTION Get_Ins_Typ_Anw_Ipap
14027: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
14028: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
14029: ,p_effective_date IN Date
14030: ,p_error_message OUT NOCOPY Varchar2
14031: ,p_data_element_value OUT NOCOPY Varchar2
14024: --depending on the kind of insurance
14025: --============================================================================
14026: FUNCTION Get_Ins_Typ_Anw_Ipap
14027: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
14028: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
14029: ,p_effective_date IN Date
14030: ,p_error_message OUT NOCOPY Varchar2
14031: ,p_data_element_value OUT NOCOPY Varchar2
14032: ) RETURN Number IS
14127: --cursor to fetch the kind of employment code
14128: --from the soft coding key flex
14129: CURSOR c_get_emp_code IS
14130: SELECT scl.SEGMENT2||scl.SEGMENT3
14131: FROM per_all_assignments_f asg
14132: ,hr_soft_coding_keyflex scl
14133: WHERE asg.assignment_id = p_assignment_id
14134: AND p_effective_date BETWEEN asg.effective_start_date
14135: AND asg.effective_end_date
14295: -- Org_Id_DataElement
14296: -- =============================================================================
14297:
14298: FUNCTION Org_Id_DataElement
14299: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
14300: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
14301: ,p_effective_date IN Date
14302: ,p_error_message OUT NOCOPY Varchar2
14303: ,p_data_element_value OUT NOCOPY Varchar2
14296: -- =============================================================================
14297:
14298: FUNCTION Org_Id_DataElement
14299: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
14300: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
14301: ,p_effective_date IN Date
14302: ,p_error_message OUT NOCOPY Varchar2
14303: ,p_data_element_value OUT NOCOPY Varchar2
14304: ) RETURN Number IS
14338:
14339: l_ele_type_id pay_element_types_f.element_type_id%TYPE;
14340: l_prev_ele_type_id pay_element_types_f.element_type_id%TYPE;
14341: l_valid_action Varchar2(2);
14342: i per_all_assignments_f.business_group_id%TYPE;
14343: l_ext_dfn_type pqp_extract_attributes.ext_dfn_type%TYPE;
14344: l_proc_name Varchar2(150) := g_proc_name ||'Create_Addl_Assignments';
14345: l_assignment_id per_all_assignments_f.assignment_id%TYPE;
14346: l_organization_id per_all_assignments_f.organization_id%TYPE;
14341: l_valid_action Varchar2(2);
14342: i per_all_assignments_f.business_group_id%TYPE;
14343: l_ext_dfn_type pqp_extract_attributes.ext_dfn_type%TYPE;
14344: l_proc_name Varchar2(150) := g_proc_name ||'Create_Addl_Assignments';
14345: l_assignment_id per_all_assignments_f.assignment_id%TYPE;
14346: l_organization_id per_all_assignments_f.organization_id%TYPE;
14347: l_business_group_id per_all_assignments_f.business_group_id%TYPE;
14348: l_main_rec csr_rslt_dtl%ROWTYPE;
14349: l_new_rec csr_rslt_dtl%ROWTYPE;
14342: i per_all_assignments_f.business_group_id%TYPE;
14343: l_ext_dfn_type pqp_extract_attributes.ext_dfn_type%TYPE;
14344: l_proc_name Varchar2(150) := g_proc_name ||'Create_Addl_Assignments';
14345: l_assignment_id per_all_assignments_f.assignment_id%TYPE;
14346: l_organization_id per_all_assignments_f.organization_id%TYPE;
14347: l_business_group_id per_all_assignments_f.business_group_id%TYPE;
14348: l_main_rec csr_rslt_dtl%ROWTYPE;
14349: l_new_rec csr_rslt_dtl%ROWTYPE;
14350: l_effective_date Date;
14343: l_ext_dfn_type pqp_extract_attributes.ext_dfn_type%TYPE;
14344: l_proc_name Varchar2(150) := g_proc_name ||'Create_Addl_Assignments';
14345: l_assignment_id per_all_assignments_f.assignment_id%TYPE;
14346: l_organization_id per_all_assignments_f.organization_id%TYPE;
14347: l_business_group_id per_all_assignments_f.business_group_id%TYPE;
14348: l_main_rec csr_rslt_dtl%ROWTYPE;
14349: l_new_rec csr_rslt_dtl%ROWTYPE;
14350: l_effective_date Date;
14351: l_ext_rcd_id ben_ext_rcd.ext_rcd_id%TYPE;
14471: ,p_error_message OUT NOCOPY Varchar2
14472: ) RETURN Number IS
14473:
14474: l_return_value Number;
14475: i per_all_assignments_f.business_group_id%TYPE;
14476: l_ele_type_id pay_element_types_f.element_type_id%TYPE;
14477: l_prev_ele_type_id pay_element_types_f.element_type_id%TYPE;
14478: l_valid_action Varchar2(2);
14479: l_no_asg_action Number(5) := 0;
14482: l_effective_date Date;
14483: l_criteria_value Varchar2(2);
14484: l_warning_message Varchar2(2000);
14485: l_error_message Varchar2(2000);
14486: l_asg_type per_all_assignments_f.assignment_type%TYPE;
14487: l_main_rec csr_rslt_dtl%ROWTYPE;
14488: l_person_id per_all_people_f.person_id%TYPE;
14489: l_assignment_id per_all_assignments_f.assignment_id%TYPE;
14490: l_mutli_assig Varchar2(150);
14485: l_error_message Varchar2(2000);
14486: l_asg_type per_all_assignments_f.assignment_type%TYPE;
14487: l_main_rec csr_rslt_dtl%ROWTYPE;
14488: l_person_id per_all_people_f.person_id%TYPE;
14489: l_assignment_id per_all_assignments_f.assignment_id%TYPE;
14490: l_mutli_assig Varchar2(150);
14491: BEGIN
14492:
14493: Hr_Utility.set_location('Entering: '||l_proc_name, 5);