[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
1015: CURSOR c_get_retro_time_period(c_element_entry_id in number
1016: ,c_assignment_id in number) IS
1017: SELECT ptp.start_date,ptp.end_date
1018: FROM per_time_periods ptp
1019: ,per_all_assignments_f paa
1020: WHERE paa.assignment_id = c_assignment_id
1021: AND ptp.payroll_id = paa.payroll_id
1022: AND pay_paywsmee_pkg.get_original_date_earned(c_element_entry_id)
1023: BETWEEN paa.effective_start_date
1944: AND effective_end_date;
1945:
1946: CURSOR c_get_org_id IS
1947: SELECT organization_id,business_group_id
1948: FROM per_all_assignments_f
1949: WHERE assignment_id = p_assignment_id
1950: AND business_group_id = g_business_group_id
1951: AND p_effective_date BETWEEN effective_start_date
1952: AND effective_end_date;
1957: l_foumula_type_id ff_formulas_f.formula_id%TYPE;
1958: l_outputs Ff_Exec.outputs_t;
1959: l_ff_value ben_ext_rslt_dtl.val_01%TYPE;
1960: l_ff_value_fmt ben_ext_rslt_dtl.val_01%TYPE;
1961: l_org_id per_all_assignments_f.organization_id%TYPE;
1962: l_bgid per_all_assignments_f.business_group_id%TYPE;
1963:
1964:
1965: BEGIN
1958: l_outputs Ff_Exec.outputs_t;
1959: l_ff_value ben_ext_rslt_dtl.val_01%TYPE;
1960: l_ff_value_fmt ben_ext_rslt_dtl.val_01%TYPE;
1961: l_org_id per_all_assignments_f.organization_id%TYPE;
1962: l_bgid per_all_assignments_f.business_group_id%TYPE;
1963:
1964:
1965: BEGIN
1966:
2122: l_defined_balance_id pay_defined_balances.defined_balance_id%TYPE;
2123: l_balance_amount NUMBER :=0;
2124: l_bal_total_amt NUMBER :=0;
2125: l_dimension_name VARCHAR2(100);
2126: i per_all_assignments_f.business_group_id%TYPE;
2127: l_legislation_code per_business_groups.legislation_code%TYPE;
2128: l_balance_type_id pay_balance_types.balance_type_id%TYPE;
2129: l_index NUMBER;
2130:
2202: ,p_error_message OUT NOCOPY VARCHAR2 )
2203: RETURN NUMBER IS
2204:
2205: l_bal_total_amt NUMBER :=0;
2206: i per_all_assignments_f.business_group_id%TYPE;
2207:
2208: BEGIN
2209:
2210: i := p_business_group_id;
2411: -- =============================================================================
2412: -- Get_Asg_Seq_Num:
2413: -- =============================================================================
2414: FUNCTION Get_Asg_Seq_Num
2415: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
2416: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
2417: ,p_effective_date IN Date
2418: ,p_error_message OUT NOCOPY Varchar2
2419: ,p_data_element_value OUT NOCOPY Varchar2
2412: -- Get_Asg_Seq_Num:
2413: -- =============================================================================
2414: FUNCTION Get_Asg_Seq_Num
2415: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
2416: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
2417: ,p_effective_date IN Date
2418: ,p_error_message OUT NOCOPY Varchar2
2419: ,p_data_element_value OUT NOCOPY Varchar2
2420: ) RETURN Number IS
2446: -- ABP as a new hire assignment
2447: --============================================================================
2448: FUNCTION Chk_New_Hire_Asg
2449: (p_person_id IN per_all_people_f.person_id%TYPE
2450: ,p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
2451: ,p_business_group_id IN NUMBER
2452: ,p_start_date IN DATE
2453: ,p_end_date IN DATE
2454: ,p_hire_date OUT NOCOPY DATE
2458: -- Cursor to derive the hire date of the EE assignment
2459: --
2460: CURSOR csr_hire_dt IS
2461: SELECT MIN(effective_start_date)
2462: FROM per_all_assignments_f
2463: WHERE assignment_id = p_assignment_id
2464: AND assignment_type = 'E';
2465:
2466: --
2562: --=============================================================================
2563: -- Function to get the age of a person given the effective date
2564: --=============================================================================
2565: FUNCTION Get_Age
2566: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
2567: ,p_effective_date IN DATE)
2568: RETURN NUMBER IS
2569:
2570: CURSOR get_dob IS
2569:
2570: CURSOR get_dob IS
2571: SELECT trunc(date_of_birth)
2572: FROM per_all_people_f per
2573: ,per_all_assignments_f paf
2574: WHERE per.person_id = paf.person_id
2575: AND paf.assignment_id = p_assignment_id
2576: AND p_effective_date BETWEEN per.effective_start_date
2577: AND per.effective_end_date
2599: -- =============================================================================
2600: -- Get_Pen_Prin_Obj_Cd
2601: -- =============================================================================
2602: FUNCTION Get_Pri_Obj_Cd_Cur
2603: ( p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
2604: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
2605: ,p_effective_date IN Date
2606: ,p_error_message OUT NOCOPY Varchar2
2607: ,p_data_element_value OUT NOCOPY Varchar2
2600: -- Get_Pen_Prin_Obj_Cd
2601: -- =============================================================================
2602: FUNCTION Get_Pri_Obj_Cd_Cur
2603: ( p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
2604: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
2605: ,p_effective_date IN Date
2606: ,p_error_message OUT NOCOPY Varchar2
2607: ,p_data_element_value OUT NOCOPY Varchar2
2608: ) RETURN Number IS
2657: -- =============================================================================
2658: -- Get_Pen_Prin_Obj_Cd
2659: -- =============================================================================
2660: FUNCTION Get_Pri_Obj_Cd
2661: ( p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
2662: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
2663: ,p_effective_date IN Date
2664: ,p_error_message OUT NOCOPY Varchar2
2665: ,p_data_element_value OUT NOCOPY Varchar2
2658: -- Get_Pen_Prin_Obj_Cd
2659: -- =============================================================================
2660: FUNCTION Get_Pri_Obj_Cd
2661: ( p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
2662: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
2663: ,p_effective_date IN Date
2664: ,p_error_message OUT NOCOPY Varchar2
2665: ,p_data_element_value OUT NOCOPY Varchar2
2666: ) RETURN Number IS
2726: --This Procedure stores the child orgs and sub orgs which are employer themselves of
2727: --the organization passed as parameter(employers) in table employer_child_list
2728: -- =============================================================================
2729: PROCEDURE Set_Er_Children ( p_org_id IN hr_all_organization_units.organization_id%TYPE
2730: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
2731: ,p_effective_date IN Date
2732: )
2733: IS
2734: -- Cursor to Check if a org hierarchy is attached to the BG.
2848: -- EE assignment. This also takes care of the EE assignment
2849: -- not attached to the payroll.
2850: --
2851: FUNCTION Chk_ABP_Processed
2852: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
2853: ,p_effective_date IN DATE
2854: ,p_business_group_id IN NUMBER)
2855: RETURN NUMBER IS
2856:
2884: -- to a payroll
2885: --
2886: CURSOR c_pay_id IS
2887: SELECT payroll_id
2888: FROM per_all_assignments_f
2889: WHERE p_effective_date between effective_start_date AND
2890: effective_end_date
2891: AND assignment_id = p_assignment_id
2892: AND payroll_id IS NOT NULL;
2995: -- ended assignment.
2996: --
2997: CURSOR c_get_term_date IS
2998: SELECT MIN(effective_start_date) - 1 term_date
2999: FROM per_all_assignments_f asg
3000: WHERE assignment_id = p_assignment_id
3001: AND assignment_status_type_id IN (SELECT assignment_status_type_id
3002: FROM per_assignment_status_types
3003: WHERE per_system_status = 'TERM_ASSIGN'
3007: -- Get the dates for any ended assignments. Note that this is for sec
3008: -- assignments only.
3009: --
3010: SELECT MAX(effective_end_date)
3011: FROM per_all_assignments_f asg
3012: WHERE assignment_id = p_assignment_id
3013: AND asg.primary_flag = 'N'
3014: AND NOT EXISTS( SELECT 1
3015: FROM per_all_assignments_f asg1
3011: FROM per_all_assignments_f asg
3012: WHERE assignment_id = p_assignment_id
3013: AND asg.primary_flag = 'N'
3014: AND NOT EXISTS( SELECT 1
3015: FROM per_all_assignments_f asg1
3016: WHERE asg1.assignment_id = p_assignment_id
3017: AND asg1.effective_start_date = asg.effective_end_date + 1
3018: AND asg.assignment_id = asg1.assignment_id )
3019: AND NOT EXISTS( SELECT 1
3016: WHERE asg1.assignment_id = p_assignment_id
3017: AND asg1.effective_start_date = asg.effective_end_date + 1
3018: AND asg.assignment_id = asg1.assignment_id )
3019: AND NOT EXISTS( SELECT 1
3020: FROM per_all_assignments_f asg1
3021: WHERE asg1.assignment_id = p_assignment_id
3022: AND asg1.effective_start_date < asg.effective_start_date
3023: AND asg.assignment_id = asg1.assignment_id
3024: AND asg1.assignment_status_type_id IN (SELECT assignment_status_type_id
3174: -- Procedure to populate all the PL/SQL tables for records
3175: -- with multiple rows
3176: --
3177: PROCEDURE Populate_Record_Structures
3178: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
3179: ,p_effective_date IN DATE
3180: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
3181: ,p_error_message OUT NOCOPY VARCHAR2 ) IS
3182:
3176: --
3177: PROCEDURE Populate_Record_Structures
3178: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
3179: ,p_effective_date IN DATE
3180: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
3181: ,p_error_message OUT NOCOPY VARCHAR2 ) IS
3182:
3183: --
3184: -- Cursor to get the Assignment Run level dimension id
3210: SELECT assignment_id
3211: ,effective_start_date start_date
3212: ,effective_end_date end_date
3213: ,fnd_number.canonical_to_number(new_val1) ptp
3214: FROM per_all_assignments_f asg,
3215: ben_ext_chg_evt_log log
3216: ,per_assignment_status_types past
3217: ,hr_soft_coding_keyflex sck
3218: WHERE asg.assignment_id = p_assignment_id
3400: SELECT asg.assignment_id
3401: ,effective_start_date start_date
3402: ,effective_end_date end_date
3403: ,least(fnd_number.canonical_to_number(nvl(sck.segment29,100)),125) ptp
3404: FROM per_all_assignments_f asg
3405: ,per_assignment_status_types past
3406: ,hr_soft_coding_keyflex sck
3407: WHERE asg.assignment_id = p_assignment_id
3408: AND asg.assignment_status_type_id = past.assignment_status_type_id
3416:
3417:
3418: CURSOR c_get_min_st_dt IS
3419: SELECT effective_start_date
3420: FROM per_all_assignments_f asg
3421: ,per_assignment_status_types past
3422: ,hr_soft_coding_keyflex sck
3423: WHERE asg.assignment_id = p_assignment_id
3424: AND asg.assignment_status_type_id = past.assignment_status_type_id
3427: AND asg.effective_start_date BETWEEN
3428: g_extract_params(p_business_group_id).extract_start_date
3429: AND g_extract_params(p_business_group_id).extract_end_date
3430: AND EXISTS (SELECT 1
3431: FROM per_all_assignments_f asg1
3432: ,per_assignment_status_types past1
3433: ,hr_soft_coding_keyflex sck1
3434: WHERE asg1.assignment_id = p_assignment_id
3435: AND asg1.effective_end_date = asg.effective_start_date - 1
3442: ORDER BY effective_start_date;
3443:
3444: CURSOR c_get_hire_dt IS
3445: SELECT MIN(effective_start_date) hire_date
3446: FROM per_all_assignments_f asg
3447: WHERE assignment_id = p_assignment_id
3448: AND assignment_status_type_id IN (SELECT assignment_status_type_id
3449: FROM per_assignment_status_types
3450: WHERE per_system_status = 'ACTIVE_ASSIGN'
3452: AND assignment_type = 'E';
3453:
3454: CURSOR c_get_term_dt IS
3455: SELECT MIN(effective_start_date) - 1 term_date
3456: FROM per_all_assignments_f asg
3457: WHERE assignment_id = p_assignment_id
3458: AND assignment_status_type_id IN (SELECT assignment_status_type_id
3459: FROM per_assignment_status_types
3460: WHERE per_system_status = 'TERM_ASSIGN'
3468: l_get_fp_nh NUMBER;
3469: l_rec_12_amt_sent_prev_r NUMBER;
3470: l_sent_to_abp NUMBER;
3471: l_rej_hf_ee NUMBER;
3472: i per_all_assignments_f.business_group_id%TYPE;
3473: l_ele_type_id pay_element_types_f.element_type_id%TYPE;
3474: l_prev_ele_type_id pay_element_types_f.element_type_id%TYPE;
3475: l_proc_name VARCHAR2(150) := g_proc_name ||'Populate_Record_Structures';
3476: l_assig_rec csr_assig%ROWTYPE;
5653: -- to be processed. The assignments that need not be processed are rejected
5654: -- here. The criteria is to filter the assignments based on the org hierarchy .
5655: -- =============================================================================
5656: FUNCTION Pension_Criteria_Full_Profile
5657: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
5658: ,p_effective_date IN DATE
5659: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
5660: ,p_warning_message OUT NOCOPY VARCHAR2
5661: ,p_error_message OUT NOCOPY VARCHAR2
5655: -- =============================================================================
5656: FUNCTION Pension_Criteria_Full_Profile
5657: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
5658: ,p_effective_date IN DATE
5659: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
5660: ,p_warning_message OUT NOCOPY VARCHAR2
5661: ,p_error_message OUT NOCOPY VARCHAR2
5662: ) RETURN VARCHAR2 IS
5663: --
5818: CURSOR c_rej_old_ee (c_ass_id IN NUMBER
5819: ,c_eff_dt IN DATE ) IS
5820: SELECT 1
5821: FROM per_periods_of_service pps
5822: ,per_all_assignments_f asg
5823: WHERE asg.assignment_id = c_ass_id
5824: AND c_eff_dt BETWEEN asg.effective_start_date AND asg.effective_end_date
5825: AND asg.period_of_service_id = pps.period_of_service_id
5826: AND pps.actual_termination_date IS NOT NULL
5879: l_rej_old_ee NUMBER;
5880: l_ext_params csr_ext_params%ROWTYPE;
5881: l_conc_reqest_id ben_ext_rslt.request_id%TYPE;
5882: l_ext_dfn_type pqp_extract_attributes.ext_dfn_type%TYPE;
5883: i per_all_assignments_f.business_group_id%TYPE;
5884: l_ext_rslt_id ben_ext_rslt.ext_rslt_id%TYPE;
5885: l_ext_dfn_id ben_ext_dfn.ext_dfn_id%TYPE;
5886: l_return_value Varchar2(2) :='N';
5887: l_proc_name Varchar2(150) := g_proc_name ||'Pension_Criteria_Full_Profile';
6279: ,p_error_message OUT NOCOPY Varchar2
6280: ) RETURN Varchar2 IS
6281:
6282: l_return_value Varchar2(50);
6283: i per_all_assignments_f.business_group_id%TYPE;
6284: l_proc_name Varchar2(150) := g_proc_name ||'Check_Addl_Assigs';
6285: l_sec_assg_rec csr_sec_assg%ROWTYPE;
6286: l_effective_date Date;
6287: l_criteria_value Varchar2(2);
6286: l_effective_date Date;
6287: l_criteria_value Varchar2(2);
6288: l_warning_message Varchar2(2000);
6289: l_error_message Varchar2(2000);
6290: l_asg_type per_all_assignments_f.assignment_type%TYPE;
6291: l_person_id per_all_people_f.person_id%TYPE;
6292: l_assignment_id per_all_assignments_f.assignment_id%TYPE;
6293: BEGIN
6294: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
6288: l_warning_message Varchar2(2000);
6289: l_error_message Varchar2(2000);
6290: l_asg_type per_all_assignments_f.assignment_type%TYPE;
6291: l_person_id per_all_people_f.person_id%TYPE;
6292: l_assignment_id per_all_assignments_f.assignment_id%TYPE;
6293: BEGIN
6294: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
6295: i := p_business_group_id;
6296:
6460: BEGIN
6461:
6462: SELECT person_id
6463: INTO l_person_id
6464: FROM per_all_assignments_f
6465: WHERE assignment_id = p_assignment_id
6466: AND ROWNUM < 2;
6467:
6468: RETURN l_person_id;
6476: --============================================================================
6477: --Function to derive the code for person detail changes
6478: --============================================================================
6479: FUNCTION Get_Change_CD_PER
6480: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
6481: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
6482: ,p_effective_date IN DATE
6483: ,p_error_message OUT NOCOPY VARCHAR2
6484: ,p_data_element_value OUT NOCOPY VARCHAR2)
6477: --Function to derive the code for person detail changes
6478: --============================================================================
6479: FUNCTION Get_Change_CD_PER
6480: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
6481: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
6482: ,p_effective_date IN DATE
6483: ,p_error_message OUT NOCOPY VARCHAR2
6484: ,p_data_element_value OUT NOCOPY VARCHAR2)
6485: RETURN NUMBER IS
6569: -- =============================================================================
6570: -- Get_Partner_Last_Name:
6571: -- =============================================================================
6572: FUNCTION Get_Partner_Last_Name
6573: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
6574: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
6575: ,p_effective_date IN Date
6576: ,p_error_message OUT NOCOPY Varchar2
6577: ,p_data_element_value OUT NOCOPY Varchar2
6570: -- Get_Partner_Last_Name:
6571: -- =============================================================================
6572: FUNCTION Get_Partner_Last_Name
6573: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
6574: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
6575: ,p_effective_date IN Date
6576: ,p_error_message OUT NOCOPY Varchar2
6577: ,p_data_element_value OUT NOCOPY Varchar2
6578: ) RETURN Number IS
6627: -- =============================================================================
6628: -- Get_Gender:
6629: -- =============================================================================
6630: FUNCTION Get_Gender
6631: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
6632: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
6633: ,p_effective_date IN Date
6634: ,p_error_message OUT NOCOPY Varchar2
6635: ,p_data_element_value OUT NOCOPY Varchar2
6628: -- Get_Gender:
6629: -- =============================================================================
6630: FUNCTION Get_Gender
6631: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
6632: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
6633: ,p_effective_date IN Date
6634: ,p_error_message OUT NOCOPY Varchar2
6635: ,p_data_element_value OUT NOCOPY Varchar2
6636: ) RETURN Number IS
6672: -- =============================================================================
6673: -- Get_Partner_Prefix:
6674: -- =============================================================================
6675: FUNCTION Get_Partner_Prefix
6676: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
6677: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
6678: ,p_effective_date IN Date
6679: ,p_error_message OUT NOCOPY Varchar2
6680: ,p_data_element_value OUT NOCOPY Varchar2
6673: -- Get_Partner_Prefix:
6674: -- =============================================================================
6675: FUNCTION Get_Partner_Prefix
6676: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
6677: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
6678: ,p_effective_date IN Date
6679: ,p_error_message OUT NOCOPY Varchar2
6680: ,p_data_element_value OUT NOCOPY Varchar2
6681: ) RETURN Number IS
6729: -- =============================================================================
6730: -- Get_Add_Fem_EE:
6731: -- =============================================================================
6732: FUNCTION Get_Add_Fem_EE
6733: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
6734: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
6735: ,p_effective_date IN Date
6736: ,p_error_message OUT NOCOPY Varchar2
6737: ,p_data_element_value OUT NOCOPY Varchar2) RETURN Number IS
6730: -- Get_Add_Fem_EE:
6731: -- =============================================================================
6732: FUNCTION Get_Add_Fem_EE
6733: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
6734: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
6735: ,p_effective_date IN Date
6736: ,p_error_message OUT NOCOPY Varchar2
6737: ,p_data_element_value OUT NOCOPY Varchar2) RETURN Number IS
6738:
6771: -- =============================================================================
6772: -- Get_EE_Num:
6773: -- =============================================================================
6774: FUNCTION Get_EE_Num
6775: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
6776: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
6777: ,p_effective_date IN Date
6778: ,p_error_message OUT NOCOPY Varchar2
6779: ,p_data_element_value OUT NOCOPY Varchar2
6772: -- Get_EE_Num:
6773: -- =============================================================================
6774: FUNCTION Get_EE_Num
6775: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
6776: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
6777: ,p_effective_date IN Date
6778: ,p_error_message OUT NOCOPY Varchar2
6779: ,p_data_element_value OUT NOCOPY Varchar2
6780: ) RETURN Number IS
6801: -- =============================================================================
6802: -- Get_Old_Asg_Seq_Num:
6803: -- =============================================================================
6804: FUNCTION Get_Old_Asg_Seq_Num
6805: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
6806: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
6807: ,p_effective_date IN DATE
6808: ,p_error_message OUT NOCOPY VARCHAR2
6809: ,p_data_element_value OUT NOCOPY VARCHAR2
6802: -- Get_Old_Asg_Seq_Num:
6803: -- =============================================================================
6804: FUNCTION Get_Old_Asg_Seq_Num
6805: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
6806: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
6807: ,p_effective_date IN DATE
6808: ,p_error_message OUT NOCOPY VARCHAR2
6809: ,p_data_element_value OUT NOCOPY VARCHAR2
6810: ) RETURN NUMBER IS
6843: -- =============================================================================
6844: -- Get_ABP_ER_Num
6845: -- =============================================================================
6846: FUNCTION Get_ABP_ER_Num
6847: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
6848: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
6849: ,p_effective_date IN Date
6850: ,p_fetch_code IN Varchar2
6851: ,p_error_message OUT NOCOPY Varchar2
6844: -- Get_ABP_ER_Num
6845: -- =============================================================================
6846: FUNCTION Get_ABP_ER_Num
6847: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
6848: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
6849: ,p_effective_date IN Date
6850: ,p_fetch_code IN Varchar2
6851: ,p_error_message OUT NOCOPY Varchar2
6852: ,p_data_element_value OUT NOCOPY Varchar2
6905: -- Cursor to fetch the organization of the EE Asg.
6906: --
6907: CURSOR csr_get_org_id IS
6908: SELECT organization_id
6909: FROM per_all_assignments_f
6910: WHERE assignment_id = p_assignment_id
6911: AND business_group_id = p_business_group_id
6912: AND p_effective_date BETWEEN effective_start_date and effective_end_date --9583106
6913: ORDER BY effective_end_date DESC;
7090: -- =============================================================================
7091: -- Get_Old_Ee_Num
7092: -- =============================================================================
7093: FUNCTION Get_Old_Ee_Num
7094: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
7095: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
7096: ,p_effective_date IN DATE
7097: ,p_error_message OUT NOCOPY VARCHAR2
7098: ,p_data_element_value OUT NOCOPY VARCHAR2
7091: -- Get_Old_Ee_Num
7092: -- =============================================================================
7093: FUNCTION Get_Old_Ee_Num
7094: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
7095: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
7096: ,p_effective_date IN DATE
7097: ,p_error_message OUT NOCOPY VARCHAR2
7098: ,p_data_element_value OUT NOCOPY VARCHAR2
7099: ) RETURN NUMBER IS
7144: --============================================================================
7145: -- Get the code for change in address for the EE
7146: --============================================================================
7147: FUNCTION Get_Change_CD_Addr
7148: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
7149: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
7150: ,p_effective_date IN DATE
7151: ,p_error_message OUT NOCOPY VARCHAR2
7152: ,p_data_element_value OUT NOCOPY VARCHAR2)
7145: -- Get the code for change in address for the EE
7146: --============================================================================
7147: FUNCTION Get_Change_CD_Addr
7148: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
7149: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
7150: ,p_effective_date IN DATE
7151: ,p_error_message OUT NOCOPY VARCHAR2
7152: ,p_data_element_value OUT NOCOPY VARCHAR2)
7153: RETURN Number IS
7190: -- =============================================================================
7191: -- Get_Street
7192: -- =============================================================================
7193: FUNCTION Get_Street
7194: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
7195: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
7196: ,p_effective_date IN Date
7197: ,p_error_message OUT NOCOPY Varchar2
7198: ,p_data_element_value OUT NOCOPY Varchar2
7191: -- Get_Street
7192: -- =============================================================================
7193: FUNCTION Get_Street
7194: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
7195: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
7196: ,p_effective_date IN Date
7197: ,p_error_message OUT NOCOPY Varchar2
7198: ,p_data_element_value OUT NOCOPY Varchar2
7199: ) RETURN Number IS
7241: -- =============================================================================
7242: -- Get_House_Num
7243: -- =============================================================================
7244: FUNCTION Get_House_Num
7245: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
7246: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
7247: ,p_effective_date IN Date
7248: ,p_error_message OUT NOCOPY Varchar2
7249: ,p_data_element_value OUT NOCOPY Varchar2
7242: -- Get_House_Num
7243: -- =============================================================================
7244: FUNCTION Get_House_Num
7245: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
7246: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
7247: ,p_effective_date IN Date
7248: ,p_error_message OUT NOCOPY Varchar2
7249: ,p_data_element_value OUT NOCOPY Varchar2
7250: ) RETURN Number IS
7295: -- =============================================================================
7296: -- Get_Addnl_House_Num
7297: -- =============================================================================
7298: FUNCTION Get_Addnl_House_Num
7299: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
7300: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
7301: ,p_effective_date IN Date
7302: ,p_error_message OUT NOCOPY Varchar2
7303: ,p_data_element_value OUT NOCOPY Varchar2
7296: -- Get_Addnl_House_Num
7297: -- =============================================================================
7298: FUNCTION Get_Addnl_House_Num
7299: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
7300: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
7301: ,p_effective_date IN Date
7302: ,p_error_message OUT NOCOPY Varchar2
7303: ,p_data_element_value OUT NOCOPY Varchar2
7304: ) RETURN Number IS
7350: -- =============================================================================
7351: -- Get_Postal_Code
7352: -- =============================================================================
7353: FUNCTION Get_Postal_Code
7354: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
7355: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
7356: ,p_effective_date IN Date
7357: ,p_error_message OUT NOCOPY Varchar2
7358: ,p_data_element_value OUT NOCOPY Varchar2
7351: -- Get_Postal_Code
7352: -- =============================================================================
7353: FUNCTION Get_Postal_Code
7354: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
7355: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
7356: ,p_effective_date IN Date
7357: ,p_error_message OUT NOCOPY Varchar2
7358: ,p_data_element_value OUT NOCOPY Varchar2
7359: ) RETURN Number IS
7410: -- =============================================================================
7411: -- Get_City
7412: -- =============================================================================
7413: FUNCTION Get_City
7414: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
7415: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
7416: ,p_effective_date IN Date
7417: ,p_error_message OUT NOCOPY Varchar2
7418: ,p_data_element_value OUT NOCOPY Varchar2
7411: -- Get_City
7412: -- =============================================================================
7413: FUNCTION Get_City
7414: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
7415: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
7416: ,p_effective_date IN Date
7417: ,p_error_message OUT NOCOPY Varchar2
7418: ,p_data_element_value OUT NOCOPY Varchar2
7419: ) RETURN Number IS
7494: -- =============================================================================
7495: -- Get_Foreign_Country
7496: -- =============================================================================
7497: FUNCTION Get_Foreign_Country
7498: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
7499: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
7500: ,p_effective_date IN Date
7501: ,p_error_message OUT NOCOPY Varchar2
7502: ,p_data_element_value OUT NOCOPY Varchar2
7495: -- Get_Foreign_Country
7496: -- =============================================================================
7497: FUNCTION Get_Foreign_Country
7498: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
7499: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
7500: ,p_effective_date IN Date
7501: ,p_error_message OUT NOCOPY Varchar2
7502: ,p_data_element_value OUT NOCOPY Varchar2
7503: ) RETURN Number IS
7537: -- =============================================================================
7538: -- Get_Marital_Status
7539: -- =============================================================================
7540: FUNCTION Get_Marital_Status
7541: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
7542: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
7543: ,p_effective_date IN Date
7544: ,p_error_message OUT NOCOPY Varchar2
7545: ,p_data_element_value OUT NOCOPY Varchar2
7538: -- Get_Marital_Status
7539: -- =============================================================================
7540: FUNCTION Get_Marital_Status
7541: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
7542: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
7543: ,p_effective_date IN Date
7544: ,p_error_message OUT NOCOPY Varchar2
7545: ,p_data_element_value OUT NOCOPY Varchar2
7546: ) RETURN Number IS
7608: -- =============================================================================
7609: -- Get_Pension_Salary -- Function to derive pension salary value for Record 08
7610: -- =============================================================================
7611: FUNCTION Get_Pension_Salary
7612: ( p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
7613: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
7614: ,p_effective_date IN DATE
7615: ,p_balance_name IN pay_balance_types.balance_name%TYPE
7616: ,p_asg_act IN NUMBER
7609: -- Get_Pension_Salary -- Function to derive pension salary value for Record 08
7610: -- =============================================================================
7611: FUNCTION Get_Pension_Salary
7612: ( p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
7613: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
7614: ,p_effective_date IN DATE
7615: ,p_balance_name IN pay_balance_types.balance_name%TYPE
7616: ,p_asg_act IN NUMBER
7617: ,p_error_message OUT NOCOPY VARCHAR2
7739: -- =============================================================================
7740: -- Get_Contribution_Amount for Record 12/41
7741: -- =============================================================================
7742: FUNCTION Get_Contribution_Amount(
7743: p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
7744: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
7745: ,p_effective_date IN Date
7746: ,p_record_number IN Number
7747: ,p_error_message OUT NOCOPY Varchar2
7740: -- Get_Contribution_Amount for Record 12/41
7741: -- =============================================================================
7742: FUNCTION Get_Contribution_Amount(
7743: p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
7744: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
7745: ,p_effective_date IN Date
7746: ,p_record_number IN Number
7747: ,p_error_message OUT NOCOPY Varchar2
7748: ,p_data_element_value OUT NOCOPY Varchar2) RETURN Number IS
7786: -- This Function gets the sub categories
7787: -- =============================================================================
7788:
7789: FUNCTION Get_Sub_Cat_12
7790: ( p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
7791: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
7792: ,p_effective_date IN Date
7793: ,p_error_message OUT NOCOPY Varchar2
7794: ,p_data_element_value OUT NOCOPY Varchar2
7787: -- =============================================================================
7788:
7789: FUNCTION Get_Sub_Cat_12
7790: ( p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
7791: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
7792: ,p_effective_date IN Date
7793: ,p_error_message OUT NOCOPY Varchar2
7794: ,p_data_element_value OUT NOCOPY Varchar2
7795: ) RETURN Number IS
7819: -- =============================================================================
7820: -- Get_Pension_Start_Year
7821: -- =============================================================================
7822: FUNCTION Get_Pension_Start_Year
7823: ( p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
7824: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
7825: ,p_effective_date IN DATE
7826: ,p_start_date IN DATE
7827: ,p_end_date IN DATE
7820: -- Get_Pension_Start_Year
7821: -- =============================================================================
7822: FUNCTION Get_Pension_Start_Year
7823: ( p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
7824: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
7825: ,p_effective_date IN DATE
7826: ,p_start_date IN DATE
7827: ,p_end_date IN DATE
7828: ,p_error_message OUT NOCOPY VARCHAR2
7930: END Get_Pension_Start_Year;
7931:
7932:
7933: FUNCTION Get_Pension_Basis_Year
7934: ( p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
7935: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
7936: ,p_effective_date IN Date
7937: ,p_record_number IN Number
7938: ,p_error_message OUT NOCOPY Varchar2
7931:
7932:
7933: FUNCTION Get_Pension_Basis_Year
7934: ( p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
7935: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
7936: ,p_effective_date IN Date
7937: ,p_record_number IN Number
7938: ,p_error_message OUT NOCOPY Varchar2
7939: ,p_data_element_value OUT NOCOPY Varchar2
7987: RETURN -1;
7988: END Get_Pension_Basis_Year;
7989:
7990: FUNCTION Get_Month_Contribution_Base
7991: ( p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
7992: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
7993: ,p_effective_date IN Date
7994: ,p_record_number IN Number
7995: ,p_error_message OUT NOCOPY Varchar2
7988: END Get_Pension_Basis_Year;
7989:
7990: FUNCTION Get_Month_Contribution_Base
7991: ( p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
7992: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
7993: ,p_effective_date IN Date
7994: ,p_record_number IN Number
7995: ,p_error_message OUT NOCOPY Varchar2
7996: ,p_data_element_value OUT NOCOPY Varchar2
8047:
8048: END Get_Month_Contribution_Base;
8049:
8050: FUNCTION Get_Year_Contribution_Amt
8051: ( p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
8052: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
8053: ,p_effective_date IN Date
8054: ,p_record_number IN Number
8055: ,p_error_message OUT NOCOPY Varchar2
8048: END Get_Month_Contribution_Base;
8049:
8050: FUNCTION Get_Year_Contribution_Amt
8051: ( p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
8052: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
8053: ,p_effective_date IN Date
8054: ,p_record_number IN Number
8055: ,p_error_message OUT NOCOPY Varchar2
8056: ,p_data_element_value OUT NOCOPY Varchar2
8103: RETURN -1;
8104: END Get_Year_Contribution_Amt;
8105:
8106: FUNCTION Get_Month_Contribution_Amt
8107: ( p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
8108: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
8109: ,p_effective_date IN Date
8110: ,p_record_number IN Number
8111: ,p_error_message OUT NOCOPY Varchar2
8104: END Get_Year_Contribution_Amt;
8105:
8106: FUNCTION Get_Month_Contribution_Amt
8107: ( p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
8108: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
8109: ,p_effective_date IN Date
8110: ,p_record_number IN Number
8111: ,p_error_message OUT NOCOPY Varchar2
8112: ,p_data_element_value OUT NOCOPY Varchar2
8165: --============================================================================
8166: -- Function to derive the display criteria for Record 01
8167: --============================================================================
8168: FUNCTION Record01_Display_Criteria
8169: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
8170: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
8171: ,p_effective_date IN DATE
8172: ,p_error_message OUT NOCOPY VARCHAR2
8173: ,p_data_element_value OUT NOCOPY VARCHAR2)
8166: -- Function to derive the display criteria for Record 01
8167: --============================================================================
8168: FUNCTION Record01_Display_Criteria
8169: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
8170: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
8171: ,p_effective_date IN DATE
8172: ,p_error_message OUT NOCOPY VARCHAR2
8173: ,p_data_element_value OUT NOCOPY VARCHAR2)
8174: RETURN NUMBER IS
8246: --============================================================================
8247: -- Record02_Display_Criteria
8248: --============================================================================
8249: FUNCTION Record02_Display_Criteria
8250: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
8251: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
8252: ,p_effective_date IN Date
8253: ,p_error_message OUT NOCOPY Varchar2
8254: ,p_data_element_value OUT NOCOPY Varchar2
8247: -- Record02_Display_Criteria
8248: --============================================================================
8249: FUNCTION Record02_Display_Criteria
8250: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
8251: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
8252: ,p_effective_date IN Date
8253: ,p_error_message OUT NOCOPY Varchar2
8254: ,p_data_element_value OUT NOCOPY Varchar2
8255: ) RETURN Number IS
8446: --============================================================================
8447: -- Function to derive the display criteria for Record 04
8448: --============================================================================
8449: FUNCTION Record04_Display_Criteria
8450: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
8451: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
8452: ,p_effective_date IN DATE
8453: ,p_error_message OUT NOCOPY VARCHAR2
8454: ,p_data_element_value OUT NOCOPY VARCHAR2)
8447: -- Function to derive the display criteria for Record 04
8448: --============================================================================
8449: FUNCTION Record04_Display_Criteria
8450: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
8451: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
8452: ,p_effective_date IN DATE
8453: ,p_error_message OUT NOCOPY VARCHAR2
8454: ,p_data_element_value OUT NOCOPY VARCHAR2)
8455:
8856: --============================================================================
8857: -- Function to derive the display criteria for Record 08
8858: --============================================================================
8859: FUNCTION Record08_Display_Criteria
8860: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
8861: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
8862: ,p_effective_date IN DATE
8863: ,p_error_message OUT NOCOPY VARCHAR2
8864: ,p_data_element_value OUT NOCOPY VARCHAR2)
8857: -- Function to derive the display criteria for Record 08
8858: --============================================================================
8859: FUNCTION Record08_Display_Criteria
8860: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
8861: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
8862: ,p_effective_date IN DATE
8863: ,p_error_message OUT NOCOPY VARCHAR2
8864: ,p_data_element_value OUT NOCOPY VARCHAR2)
8865: RETURN NUMBER IS
9087: --=============================================================================
9088: -- Function to derive the display criteria for Record 09
9089: --=============================================================================
9090: FUNCTION Record09_Display_Criteria
9091: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
9092: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
9093: ,p_effective_date IN DATE
9094: ,p_error_message OUT NOCOPY VARCHAR2
9095: ,p_data_element_value OUT NOCOPY VARCHAR2)
9088: -- Function to derive the display criteria for Record 09
9089: --=============================================================================
9090: FUNCTION Record09_Display_Criteria
9091: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
9092: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
9093: ,p_effective_date IN DATE
9094: ,p_error_message OUT NOCOPY VARCHAR2
9095: ,p_data_element_value OUT NOCOPY VARCHAR2)
9096: RETURN NUMBER IS
9121: --=============================================================================
9122: -- Function to check if Record12 needs to be displayed
9123: --=============================================================================
9124: FUNCTION Record12_Display_Criteria
9125: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
9126: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
9127: ,p_effective_date IN DATE
9128: ,p_error_message OUT NOCOPY VARCHAR2
9129: ,p_data_element_value OUT NOCOPY VARCHAR2)
9122: -- Function to check if Record12 needs to be displayed
9123: --=============================================================================
9124: FUNCTION Record12_Display_Criteria
9125: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
9126: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
9127: ,p_effective_date IN DATE
9128: ,p_error_message OUT NOCOPY VARCHAR2
9129: ,p_data_element_value OUT NOCOPY VARCHAR2)
9130: RETURN NUMBER IS
9154: --=============================================================================
9155: -- Function to derive the display criteria for Record 20
9156: --=============================================================================
9157: FUNCTION Record20_Display_Criteria
9158: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
9159: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
9160: ,p_effective_date IN DATE
9161: ,p_error_message OUT NOCOPY VARCHAR2
9162: ,p_data_element_value OUT NOCOPY VARCHAR2)
9155: -- Function to derive the display criteria for Record 20
9156: --=============================================================================
9157: FUNCTION Record20_Display_Criteria
9158: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
9159: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
9160: ,p_effective_date IN DATE
9161: ,p_error_message OUT NOCOPY VARCHAR2
9162: ,p_data_element_value OUT NOCOPY VARCHAR2)
9163: RETURN NUMBER IS
9177: --============================================================================
9178: -- Function to derive the display criteria for Record 21
9179: --============================================================================
9180: FUNCTION Record21_Display_Criteria
9181: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
9182: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
9183: ,p_effective_date IN DATE
9184: ,p_error_message OUT NOCOPY VARCHAR2
9185: ,p_data_element_value OUT NOCOPY VARCHAR2)
9178: -- Function to derive the display criteria for Record 21
9179: --============================================================================
9180: FUNCTION Record21_Display_Criteria
9181: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
9182: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
9183: ,p_effective_date IN DATE
9184: ,p_error_message OUT NOCOPY VARCHAR2
9185: ,p_data_element_value OUT NOCOPY VARCHAR2)
9186: RETURN NUMBER IS
9200: --============================================================================
9201: -- Function to derive the display criteria for Record 22
9202: --============================================================================
9203: FUNCTION Record22_Display_Criteria
9204: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
9205: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
9206: ,p_effective_date IN DATE
9207: ,p_error_message OUT NOCOPY VARCHAR2
9208: ,p_data_element_value OUT NOCOPY VARCHAR2)
9201: -- Function to derive the display criteria for Record 22
9202: --============================================================================
9203: FUNCTION Record22_Display_Criteria
9204: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
9205: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
9206: ,p_effective_date IN DATE
9207: ,p_error_message OUT NOCOPY VARCHAR2
9208: ,p_data_element_value OUT NOCOPY VARCHAR2)
9209: RETURN NUMBER IS
9223: --============================================================================
9224: -- Function to derive the display criteria for Record 05
9225: --============================================================================
9226: FUNCTION Record05_Display_Criteria
9227: ( p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
9228: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
9229: ,p_effective_date IN DATE
9230: ,p_error_message OUT NOCOPY VARCHAR2
9231: ,p_data_element_value OUT NOCOPY VARCHAR2)
9224: -- Function to derive the display criteria for Record 05
9225: --============================================================================
9226: FUNCTION Record05_Display_Criteria
9227: ( p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
9228: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
9229: ,p_effective_date IN DATE
9230: ,p_error_message OUT NOCOPY VARCHAR2
9231: ,p_data_element_value OUT NOCOPY VARCHAR2)
9232: RETURN NUMBER IS
9270: --============================================================================
9271: --This is used to decide the Record40_30 hide or show
9272: --============================================================================
9273: FUNCTION Record30_40_Display_Criteria
9274: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
9275: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
9276: ,p_effective_date IN Date
9277: ,p_sub_cat IN Varchar2
9278: ,p_error_message OUT NOCOPY Varchar2
9271: --This is used to decide the Record40_30 hide or show
9272: --============================================================================
9273: FUNCTION Record30_40_Display_Criteria
9274: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
9275: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
9276: ,p_effective_date IN Date
9277: ,p_sub_cat IN Varchar2
9278: ,p_error_message OUT NOCOPY Varchar2
9279: ,p_data_element_value OUT NOCOPY Varchar2
9289: AND pty.pension_sub_category = p_sub_cat
9290: AND prmtr_03 = 'Y'
9291: AND Fnd_Number.canonical_to_number(prmtr_10) = p_assignment_id
9292: AND person_id = (SELECT person_id
9293: FROM per_all_assignments_f
9294: WHERE assignment_id = p_assignment_id
9295: AND p_effective_date BETWEEN effective_start_date
9296: AND effective_end_date
9297: )
9319: AND prmtr_03 = 'Y'
9320: AND Fnd_Number.canonical_to_number(prmtr_10) = p_assignment_id
9321: AND pty.pension_sub_category = p_sub_cat
9322: AND person_id = (SELECT person_id
9323: FROM per_all_assignments_f
9324: WHERE assignment_id = p_assignment_id
9325: AND p_effective_date BETWEEN effective_start_date
9326: AND effective_end_date
9327: )
9380:
9381: --cursor to find the org id for the current asg
9382: CURSOR c_find_org_id IS
9383: SELECT organization_id
9384: FROM per_all_assignments_f
9385: WHERE assignment_id = p_assignment_id
9386: AND p_effective_date BETWEEN effective_start_date
9387: AND effective_end_date;
9388:
9388:
9389: -- Cursor to get the hire date of the person
9390: CURSOR c_hire_dt IS
9391: SELECT max(date_start)
9392: FROM per_all_assignments_f asg
9393: ,per_periods_of_service pps
9394: WHERE pps.person_id = asg.person_id
9395: AND asg.assignment_id = p_assignment_id
9396: AND pps.business_group_id = p_business_group_id
9567: --============================================================================
9568: --This is used to decide the Record41_31 hide or show
9569: --============================================================================
9570: FUNCTION Record31_41_Display_Criteria
9571: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
9572: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
9573: ,p_effective_date IN Date
9574: ,p_record_number IN Number
9575: ,p_error_message OUT NOCOPY Varchar2
9568: --This is used to decide the Record41_31 hide or show
9569: --============================================================================
9570: FUNCTION Record31_41_Display_Criteria
9571: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
9572: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
9573: ,p_effective_date IN Date
9574: ,p_record_number IN Number
9575: ,p_error_message OUT NOCOPY Varchar2
9576: ,p_data_element_value OUT NOCOPY Varchar2
9633: --This is used to derive the participation end date in the case , when enrollment has
9634: --come from the ORG EIT on a start of employment
9635: --============================================================================
9636: FUNCTION Get_Participation_End
9637: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
9638: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
9639: ,p_pension_type_id IN pqp_pension_types_f.pension_type_id%TYPE
9640: ,p_date_earned IN Date
9641: ,p_end_date OUT NOCOPY Date
9634: --come from the ORG EIT on a start of employment
9635: --============================================================================
9636: FUNCTION Get_Participation_End
9637: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
9638: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
9639: ,p_pension_type_id IN pqp_pension_types_f.pension_type_id%TYPE
9640: ,p_date_earned IN Date
9641: ,p_end_date OUT NOCOPY Date
9642: ) RETURN Number IS
9651:
9652: --Cursor to find the org id from the assignment id
9653: CURSOR c_find_org_id IS
9654: SELECT organization_id
9655: FROM per_all_assignments_f
9656: WHERE assignment_id = p_assignment_id
9657: AND Trunc(p_date_earned) BETWEEN effective_start_date AND effective_end_date
9658: AND business_group_id = p_business_group_id;
9659:
9796: --This is used to derive the participation start and end dates and the old start and
9797: --end dates in case of an update for FUR Pensions
9798: --============================================================================
9799: FUNCTION Get_Fur_Participation_Dates
9800: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
9801: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
9802: ,p_fetch_code IN Varchar2
9803: ,p_effective_date IN Date
9804: ,p_error_message OUT NOCOPY Varchar2
9797: --end dates in case of an update for FUR Pensions
9798: --============================================================================
9799: FUNCTION Get_Fur_Participation_Dates
9800: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
9801: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
9802: ,p_fetch_code IN Varchar2
9803: ,p_effective_date IN Date
9804: ,p_error_message OUT NOCOPY Varchar2
9805: ,p_data_element_value OUT NOCOPY Varchar2
9815: AND pty.pension_sub_category = 'FUR_S'
9816: AND prmtr_03 = 'Y'
9817: AND Fnd_Number.canonical_to_number(prmtr_10) = p_assignment_id
9818: AND person_id = (SELECT person_id
9819: FROM per_all_assignments_f
9820: WHERE assignment_id = p_assignment_id
9821: AND p_effective_date BETWEEN effective_start_date
9822: AND effective_end_date
9823: )
9845: AND prmtr_03 = 'Y'
9846: AND Fnd_Number.canonical_to_number(prmtr_10) = p_assignment_id
9847: AND pty.pension_sub_category = 'FUR_S'
9848: AND person_id = (SELECT person_id
9849: FROM per_all_assignments_f
9850: WHERE assignment_id = p_assignment_id
9851: AND p_effective_date BETWEEN effective_start_date
9852: AND effective_end_date
9853: )
9906:
9907: --cursor to find the org id for the current asg
9908: CURSOR c_find_org_id IS
9909: SELECT organization_id
9910: FROM per_all_assignments_f
9911: WHERE assignment_id = p_assignment_id
9912: AND p_effective_date BETWEEN effective_start_date
9913: AND effective_end_date;
9914:
9914:
9915: -- Cursor to get the hire date of the person
9916: CURSOR c_hire_dt IS
9917: SELECT max(date_start)
9918: FROM per_all_assignments_f asg
9919: ,per_periods_of_service pps
9920: WHERE pps.person_id = asg.person_id
9921: AND asg.assignment_id = p_assignment_id
9922: AND pps.business_group_id = p_business_group_id
10214: -- This function returns the kind of contribution for PPP Pensions
10215: -- for a particular effective date.
10216: --============================================================================
10217: FUNCTION Get_PPP_Kind
10218: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
10219: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
10220: ,p_effective_date IN DATE
10221: ,p_current IN VARCHAR2
10222: ,p_error_message OUT NOCOPY VARCHAR2
10215: -- for a particular effective date.
10216: --============================================================================
10217: FUNCTION Get_PPP_Kind
10218: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
10219: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
10220: ,p_effective_date IN DATE
10221: ,p_current IN VARCHAR2
10222: ,p_error_message OUT NOCOPY VARCHAR2
10223: ,p_data_element_value OUT NOCOPY VARCHAR2
10230: l_asg_exist NUMBER;
10231:
10232: CURSOR c_ppp_org IS
10233: SELECT organization_id
10234: FROM per_all_assignments_f
10235: WHERE assignment_id = p_assignment_id
10236: AND TRUNC(p_effective_date) BETWEEN effective_start_date
10237: AND effective_end_date;
10238:
10285: AND pty.pension_sub_category = 'PPP';*/
10286:
10287: CURSOR c_rr_cur IS
10288: SELECT 1
10289: FROM per_all_assignments_f paf,
10290: hr_organization_information hoi,
10291: pqp_pension_types_f pty,
10292: pay_all_payrolls_f ppf
10293: WHERE paf.assignment_id = p_assignment_id
10392:
10393: -- Please Note : Legislative rules do not allow any other combination
10394: --============================================================================
10395: FUNCTION Get_Fpu_Kind
10396: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
10397: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
10398: ,p_effective_date IN Date
10399: ,p_error_message OUT NOCOPY Varchar2
10400: ,p_data_element_value OUT NOCOPY Varchar2
10393: -- Please Note : Legislative rules do not allow any other combination
10394: --============================================================================
10395: FUNCTION Get_Fpu_Kind
10396: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
10397: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
10398: ,p_effective_date IN Date
10399: ,p_error_message OUT NOCOPY Varchar2
10400: ,p_data_element_value OUT NOCOPY Varchar2
10401: ) RETURN NUMBER IS
10437: ,'FPU_E','C'
10438: ,'FPU_R','A'
10439: ,'FPU_S','S'
10440: ,'FPU_T' ,'B') fpu_code
10441: FROM per_all_assignments_f paf,
10442: hr_organization_information hoi,
10443: pqp_pension_types_f pty,
10444: pay_all_payrolls_f ppf
10445: WHERE paf.assignment_id = p_assignment_id
10559: --============================================================================
10560: --This function returns the kind of contribution for OPNP Pensions, from the ASG EIT
10561: --============================================================================
10562: FUNCTION Get_Opnp_Kind
10563: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
10564: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
10565: ,p_effective_date IN Date
10566: ,p_error_message OUT NOCOPY Varchar2
10567: ,p_data_element_value OUT NOCOPY Varchar2
10560: --This function returns the kind of contribution for OPNP Pensions, from the ASG EIT
10561: --============================================================================
10562: FUNCTION Get_Opnp_Kind
10563: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
10564: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
10565: ,p_effective_date IN Date
10566: ,p_error_message OUT NOCOPY Varchar2
10567: ,p_data_element_value OUT NOCOPY Varchar2
10568: ) RETURN Number IS
10604: -- this function currently returns the start and end date of retro
10605: -- participation.
10606: -- ============================================================================
10607: FUNCTION Get_Retro_Kind_Of_Ptpn
10608: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
10609: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
10610: ,p_effective_date IN DATE
10611: ,p_retro_kind_ptpn OUT NOCOPY t_retro_ptpn_kind
10612: ,p_error_message OUT NOCOPY VARCHAR2
10605: -- participation.
10606: -- ============================================================================
10607: FUNCTION Get_Retro_Kind_Of_Ptpn
10608: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
10609: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
10610: ,p_effective_date IN DATE
10611: ,p_retro_kind_ptpn OUT NOCOPY t_retro_ptpn_kind
10612: ,p_error_message OUT NOCOPY VARCHAR2
10613: ) RETURN NUMBER IS
10725: -- this function currently returns the start and end date of retro
10726: -- participation.
10727: -- ============================================================================
10728: FUNCTION Get_Retro_Participation
10729: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
10730: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
10731: ,p_sub_cat IN VARCHAR2
10732: ,p_effective_date IN DATE
10733: ,p_retro_ptpn OUT NOCOPY t_retro_ptpn
10726: -- participation.
10727: -- ============================================================================
10728: FUNCTION Get_Retro_Participation
10729: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
10730: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
10731: ,p_sub_cat IN VARCHAR2
10732: ,p_effective_date IN DATE
10733: ,p_retro_ptpn OUT NOCOPY t_retro_ptpn
10734: ,p_error_message OUT NOCOPY VARCHAR2
10863: END Get_Retro_Participation;
10864:
10865:
10866: /*PROCEDURE Populate_Term_Rev_Data
10867: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
10868: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
10869: ,p_end_date IN DATE
10870: ,p_start_date IN DATE
10871: ,p_error_message OUT NOCOPY VARCHAR2
10864:
10865:
10866: /*PROCEDURE Populate_Term_Rev_Data
10867: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
10868: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
10869: ,p_end_date IN DATE
10870: ,p_start_date IN DATE
10871: ,p_error_message OUT NOCOPY VARCHAR2
10872: ) IS
10966: -- Termination/ participation end
10967: -- Political leave
10968: --============================================================================
10969: FUNCTION Get_Rec05_Participation
10970: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
10971: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
10972: ,p_fetch_code IN VARCHAR2
10973: ,p_effective_date IN DATE
10974: ,p_error_message OUT NOCOPY VARCHAR2
10967: -- Political leave
10968: --============================================================================
10969: FUNCTION Get_Rec05_Participation
10970: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
10971: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
10972: ,p_fetch_code IN VARCHAR2
10973: ,p_effective_date IN DATE
10974: ,p_error_message OUT NOCOPY VARCHAR2
10975: ,p_data_element_value OUT NOCOPY VARCHAR2)
10978: -- Cursor to find the org id for the current asg
10979: --
10980: CURSOR c_find_org_id IS
10981: SELECT organization_id
10982: FROM per_all_assignments_f
10983: WHERE assignment_id = p_assignment_id
10984: AND p_effective_date BETWEEN effective_start_date
10985: AND effective_end_date;
10986: --
10999: -- Cursor to get the hire date of the person
11000: --
11001: CURSOR c_hire_dt IS
11002: SELECT max(date_start)
11003: FROM per_all_assignments_f asg
11004: ,per_periods_of_service pps
11005: WHERE pps.person_id = asg.person_id
11006: AND asg.assignment_id = p_assignment_id
11007: AND pps.business_group_id = p_business_group_id
11041: CURSOR c_get_end_reason IS
11042: SELECT /*decode(nvl(leaving_reason ,'A'),'D','I','A') term_reas*/
11043: decode(nvl(leaving_reason ,'A'),'D','I','B','B','A') term_reas
11044: FROM per_periods_of_service pps,
11045: per_all_assignments_f asg
11046: WHERE asg.period_of_service_id = pps.period_of_service_id
11047: AND assignment_id = p_assignment_id
11048: AND p_effective_date between effective_start_date and
11049: effective_end_date ;
11215: order by asg.effective_start_date;
11216:
11217: CURSOR c_pay_id IS
11218: SELECT payroll_id
11219: FROM per_all_assignments_f
11220: WHERE assignment_id = p_assignment_id
11221: AND p_effective_date BETWEEN effective_start_date AND
11222: effective_end_date;
11223:
11224: --6501898
11225: --Commented for 6959318
11226: /*CURSOR c_get_term_dt IS
11227: SELECT MIN(effective_start_date) - 1 term_date
11228: FROM per_all_assignments_f asg
11229: WHERE assignment_id = p_assignment_id
11230: AND assignment_status_type_id IN (SELECT assignment_status_type_id
11231: FROM per_assignment_status_types
11232: WHERE per_system_status = 'TERM_ASSIGN'
12186: --This is used to derive the participation start and end dates and the old start and
12187: --end dates in case of an update for IPAP Pensions
12188: --============================================================================
12189: FUNCTION Get_Ipap_Participation_Dates
12190: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
12191: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
12192: ,p_fetch_code IN Varchar2
12193: ,p_effective_date IN Date
12194: ,p_error_message OUT NOCOPY Varchar2
12187: --end dates in case of an update for IPAP Pensions
12188: --============================================================================
12189: FUNCTION Get_Ipap_Participation_Dates
12190: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
12191: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
12192: ,p_fetch_code IN Varchar2
12193: ,p_effective_date IN Date
12194: ,p_error_message OUT NOCOPY Varchar2
12195: ,p_data_element_value OUT NOCOPY Varchar2
12205: AND pty.pension_sub_category = 'IPAP'
12206: AND prmtr_03 = 'Y'
12207: AND Fnd_Number.canonical_to_number(prmtr_10) = p_assignment_id
12208: AND person_id = (SELECT person_id
12209: FROM per_all_assignments_f
12210: WHERE assignment_id = p_assignment_id
12211: AND p_effective_date BETWEEN effective_start_date
12212: AND effective_end_date
12213: )
12235: AND prmtr_03 = 'Y'
12236: AND Fnd_Number.canonical_to_number(prmtr_10) = p_assignment_id
12237: AND pty.pension_sub_category = 'IPAP'
12238: AND person_id = (SELECT person_id
12239: FROM per_all_assignments_f
12240: WHERE assignment_id = p_assignment_id
12241: AND p_effective_date BETWEEN effective_start_date
12242: AND effective_end_date
12243: )
12296:
12297: --cursor to find the org id for the current asg
12298: CURSOR c_find_org_id IS
12299: SELECT organization_id
12300: FROM per_all_assignments_f
12301: WHERE assignment_id = p_assignment_id
12302: AND p_effective_date BETWEEN effective_start_date
12303: AND effective_end_date;
12304:
12304:
12305: -- Cursor to get the hire date of the person
12306: CURSOR c_hire_dt IS
12307: SELECT max(date_start)
12308: FROM per_all_assignments_f asg
12309: ,per_periods_of_service pps
12310: WHERE pps.person_id = asg.person_id
12311: AND asg.assignment_id = p_assignment_id
12312: AND pps.business_group_id = p_business_group_id
12753: --This is used to check if there are any more rows for Record 05 and insert
12754: --those records forcibly
12755: --============================================================================
12756: FUNCTION Process_Mult_Rec05
12757: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
12758: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
12759: ,p_effective_date IN Date
12760: ,p_error_message OUT NOCOPY Varchar2
12761: ,p_data_element_value OUT NOCOPY Varchar2
12754: --those records forcibly
12755: --============================================================================
12756: FUNCTION Process_Mult_Rec05
12757: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
12758: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
12759: ,p_effective_date IN Date
12760: ,p_error_message OUT NOCOPY Varchar2
12761: ,p_data_element_value OUT NOCOPY Varchar2
12762: ) RETURN Number IS
12765: l_new_rec csr_rslt_dtl%ROWTYPE;
12766: l_return_value Number := 1;
12767: l_rcd_id Number;
12768: l_mutli_assig Varchar2(50);
12769: l_asg_type per_all_assignments_f.assignment_type%TYPE;
12770: l_person_id per_all_people_f.person_id%TYPE;
12771: l_assignment_id per_all_assignments_f.assignment_id%TYPE;
12772: l_effective_date Date;
12773:
12767: l_rcd_id Number;
12768: l_mutli_assig Varchar2(50);
12769: l_asg_type per_all_assignments_f.assignment_type%TYPE;
12770: l_person_id per_all_people_f.person_id%TYPE;
12771: l_assignment_id per_all_assignments_f.assignment_id%TYPE;
12772: l_effective_date Date;
12773:
12774: BEGIN
12775: --fetch the record id from the sequence number
12824: -- Process Multiple Record 08. This is necessary for late hires
12825: -- and for change in hire date to the past (ABP Certification only test case).
12826: --=============================================================================
12827: FUNCTION Process_Mult_Rec08
12828: ( p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
12829: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
12830: ,p_effective_date IN DATE
12831: ,p_error_message OUT NOCOPY VARCHAR2
12832: ,p_data_element_value OUT NOCOPY VARCHAR2
12825: -- and for change in hire date to the past (ABP Certification only test case).
12826: --=============================================================================
12827: FUNCTION Process_Mult_Rec08
12828: ( p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
12829: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
12830: ,p_effective_date IN DATE
12831: ,p_error_message OUT NOCOPY VARCHAR2
12832: ,p_data_element_value OUT NOCOPY VARCHAR2
12833: ) RETURN NUMBER IS
13194: -- =============================================================================
13195: -- Process Multiple SubCategories for record 09
13196: -- =============================================================================
13197: FUNCTION Process_Mult_Rec09
13198: ( p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
13199: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
13200: ,p_effective_date IN Date
13201: ,p_error_message OUT NOCOPY Varchar2
13202: ,p_data_element_value OUT NOCOPY Varchar2
13195: -- Process Multiple SubCategories for record 09
13196: -- =============================================================================
13197: FUNCTION Process_Mult_Rec09
13198: ( p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
13199: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
13200: ,p_effective_date IN Date
13201: ,p_error_message OUT NOCOPY Varchar2
13202: ,p_data_element_value OUT NOCOPY Varchar2
13203: ) RETURN Number IS
13264: -- Process Multiple SubCategories for Record 12
13265: -- =============================================================================
13266: FUNCTION Process_Mult_Rec12
13267: (
13268: p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
13269: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
13270: ,p_effective_date IN Date
13271: ,p_error_message OUT NOCOPY Varchar2
13272: ,p_data_element_value OUT NOCOPY Varchar2
13265: -- =============================================================================
13266: FUNCTION Process_Mult_Rec12
13267: (
13268: p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
13269: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
13270: ,p_effective_date IN Date
13271: ,p_error_message OUT NOCOPY Varchar2
13272: ,p_data_element_value OUT NOCOPY Varchar2
13273: ) RETURN Number IS
13338: --This is used to check if there are any more rows for SI participation and insert
13339: --those records forcibly
13340: --============================================================================
13341: FUNCTION Process_Mult_Rec20
13342: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
13343: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
13344: ,p_effective_date IN Date
13345: ,p_error_message OUT NOCOPY Varchar2
13346: ,p_data_element_value OUT NOCOPY Varchar2
13339: --those records forcibly
13340: --============================================================================
13341: FUNCTION Process_Mult_Rec20
13342: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
13343: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
13344: ,p_effective_date IN Date
13345: ,p_error_message OUT NOCOPY Varchar2
13346: ,p_data_element_value OUT NOCOPY Varchar2
13347: ) RETURN Number IS
13410: --This is used to check if there are any more rows for IPAP participation and insert
13411: --those records forcibly
13412: --============================================================================
13413: FUNCTION Process_Mult_Rec30
13414: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
13415: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
13416: ,p_effective_date IN Date
13417: ,p_error_message OUT NOCOPY Varchar2
13418: ,p_data_element_value OUT NOCOPY Varchar2
13411: --those records forcibly
13412: --============================================================================
13413: FUNCTION Process_Mult_Rec30
13414: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
13415: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
13416: ,p_effective_date IN Date
13417: ,p_error_message OUT NOCOPY Varchar2
13418: ,p_data_element_value OUT NOCOPY Varchar2
13419: ) RETURN Number IS
13471: -- This is used to check if there are any additional rows for rec 31
13472: -- This is for future use.
13473: --=============================================================================
13474: FUNCTION Process_Mult_Rec31
13475: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
13476: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
13477: ,p_effective_date IN Date
13478: ,p_error_message OUT NOCOPY Varchar2
13479: ,p_data_element_value OUT NOCOPY Varchar2
13472: -- This is for future use.
13473: --=============================================================================
13474: FUNCTION Process_Mult_Rec31
13475: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
13476: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
13477: ,p_effective_date IN Date
13478: ,p_error_message OUT NOCOPY Varchar2
13479: ,p_data_element_value OUT NOCOPY Varchar2
13480: ) RETURN Number IS
13535: -- This is used to check if there are any more rows for FUR participation and
13536: -- insert those records forcibly
13537: --=============================================================================
13538: FUNCTION Process_Mult_Rec40
13539: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
13540: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
13541: ,p_effective_date IN Date
13542: ,p_error_message OUT NOCOPY Varchar2
13543: ,p_data_element_value OUT NOCOPY Varchar2
13536: -- insert those records forcibly
13537: --=============================================================================
13538: FUNCTION Process_Mult_Rec40
13539: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
13540: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
13541: ,p_effective_date IN Date
13542: ,p_error_message OUT NOCOPY Varchar2
13543: ,p_data_element_value OUT NOCOPY Varchar2
13544: ) RETURN Number IS
13605: -- This is used to check if there are any additional rows for rec 41
13606: -- This is for future use.
13607: --=============================================================================
13608: FUNCTION Process_Mult_Rec41
13609: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
13610: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
13611: ,p_effective_date IN Date
13612: ,p_error_message OUT NOCOPY Varchar2
13613: ,p_data_element_value OUT NOCOPY Varchar2
13606: -- This is for future use.
13607: --=============================================================================
13608: FUNCTION Process_Mult_Rec41
13609: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
13610: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
13611: ,p_effective_date IN Date
13612: ,p_error_message OUT NOCOPY Varchar2
13613: ,p_data_element_value OUT NOCOPY Varchar2
13614: ) RETURN Number IS
13710: --============================================================================
13711: --Function to return the contribution amount towards FUR Pensions
13712: --============================================================================
13713: FUNCTION Get_FUR_Contribution_Amt
13714: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
13715: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
13716: ,p_effective_date IN Date
13717: ,p_error_message OUT NOCOPY Varchar2
13718: ,p_data_element_value OUT NOCOPY Varchar2
13711: --Function to return the contribution amount towards FUR Pensions
13712: --============================================================================
13713: FUNCTION Get_FUR_Contribution_Amt
13714: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
13715: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
13716: ,p_effective_date IN Date
13717: ,p_error_message OUT NOCOPY Varchar2
13718: ,p_data_element_value OUT NOCOPY Varchar2
13719: ) RETURN Number IS
13799: --============================================================================
13800: --Function to return the contribution basis towards FUR/IPAP Pensions
13801: --============================================================================
13802: FUNCTION Get_Contribution_Basis
13803: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
13804: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
13805: ,p_effective_date IN Date
13806: ,p_sub_cat IN Varchar2
13807: ,p_error_message OUT NOCOPY Varchar2
13800: --Function to return the contribution basis towards FUR/IPAP Pensions
13801: --============================================================================
13802: FUNCTION Get_Contribution_Basis
13803: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
13804: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
13805: ,p_effective_date IN Date
13806: ,p_sub_cat IN Varchar2
13807: ,p_error_message OUT NOCOPY Varchar2
13808: ,p_data_element_value OUT NOCOPY Varchar2
13894: -- This Function returns the Basis Contribution amount for any sub cats in
13895: -- rec 09/31/41 that the ee might have paid for .
13896: -- =============================================================================
13897: FUNCTION Get_Basis_Amt
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_record_number IN Number
13902: ,p_error_message OUT NOCOPY Varchar2
13895: -- rec 09/31/41 that the ee might have paid for .
13896: -- =============================================================================
13897: FUNCTION Get_Basis_Amt
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_record_number IN Number
13902: ,p_error_message OUT NOCOPY Varchar2
13903: ,p_data_element_value OUT NOCOPY Varchar2)
13950: -- Get_Sub_Cat_09 for Record 09
13951: -- This Function gets the sub categories for rec 09
13952: -- =============================================================================
13953: FUNCTION Get_Sub_Cat_09
13954: ( p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
13955: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
13956: ,p_effective_date IN Date
13957: ,p_error_message OUT NOCOPY Varchar2
13958: ,p_data_element_value OUT NOCOPY Varchar2)
13951: -- This Function gets the sub categories for rec 09
13952: -- =============================================================================
13953: FUNCTION Get_Sub_Cat_09
13954: ( p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
13955: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
13956: ,p_effective_date IN Date
13957: ,p_error_message OUT NOCOPY Varchar2
13958: ,p_data_element_value OUT NOCOPY Varchar2)
13959: RETURN Number IS
13986: -- Get_Basis_Amt_Code for Record 09/31/41
13987: -- This Function gets sign for basis amount in rec 09/31/41
13988: -- =============================================================================
13989: FUNCTION Get_Basis_Amt_Code
13990: ( p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
13991: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
13992: ,p_effective_date IN Date
13993: ,p_record_number IN Number
13994: ,p_error_message OUT NOCOPY Varchar2
13987: -- This Function gets sign for basis amount in rec 09/31/41
13988: -- =============================================================================
13989: FUNCTION Get_Basis_Amt_Code
13990: ( p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
13991: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
13992: ,p_effective_date IN Date
13993: ,p_record_number IN Number
13994: ,p_error_message OUT NOCOPY Varchar2
13995: ,p_data_element_value OUT NOCOPY Varchar2)
14039: -- Get_Contrib_Amt_Code for Record 12/41
14040: -- This Function gets sign for contrib amount in rec 12/41
14041: -- =============================================================================
14042: FUNCTION Get_Contrib_Amt_Code
14043: ( p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
14044: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
14045: ,p_effective_date IN Date
14046: ,p_record_number IN Number
14047: ,p_error_message OUT NOCOPY Varchar2
14040: -- This Function gets sign for contrib amount in rec 12/41
14041: -- =============================================================================
14042: FUNCTION Get_Contrib_Amt_Code
14043: ( p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
14044: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
14045: ,p_effective_date IN Date
14046: ,p_record_number IN Number
14047: ,p_error_message OUT NOCOPY Varchar2
14048: ,p_data_element_value OUT NOCOPY Varchar2)
14093: --============================================================================
14094: --Function to return the contribution amount towards IPAP Pensions
14095: --============================================================================
14096: FUNCTION Get_IPAP_Contribution_Amt
14097: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
14098: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
14099: ,p_effective_date IN Date
14100: ,p_error_message OUT NOCOPY Varchar2
14101: ,p_data_element_value OUT NOCOPY Varchar2
14094: --Function to return the contribution amount towards IPAP Pensions
14095: --============================================================================
14096: FUNCTION Get_IPAP_Contribution_Amt
14097: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
14098: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
14099: ,p_effective_date IN Date
14100: ,p_error_message OUT NOCOPY Varchar2
14101: ,p_data_element_value OUT NOCOPY Varchar2
14102: ) RETURN Number IS
14182: --This function returns the code to indicate whether the basis/contribution is positive
14183: --or negative
14184: --============================================================================
14185: FUNCTION Get_Amt_Code
14186: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
14187: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
14188: ,p_effective_date IN Date
14189: ,p_sub_cat IN Varchar2
14190: ,p_error_message OUT NOCOPY Varchar2
14183: --or negative
14184: --============================================================================
14185: FUNCTION Get_Amt_Code
14186: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
14187: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
14188: ,p_effective_date IN Date
14189: ,p_sub_cat IN Varchar2
14190: ,p_error_message OUT NOCOPY Varchar2
14191: ,p_data_element_value OUT NOCOPY Varchar2
14226: --============================================================================
14227: --This function returns the month of contribution
14228: --============================================================================
14229: FUNCTION Get_Amt_Month
14230: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
14231: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
14232: ,p_effective_date IN Date
14233: ,p_error_message OUT NOCOPY Varchar2
14234: ,p_data_element_value OUT NOCOPY Varchar2
14227: --This function returns the month of contribution
14228: --============================================================================
14229: FUNCTION Get_Amt_Month
14230: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
14231: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
14232: ,p_effective_date IN Date
14233: ,p_error_message OUT NOCOPY Varchar2
14234: ,p_data_element_value OUT NOCOPY Varchar2
14235: ) RETURN Number IS
14263: --============================================================================
14264: --This function returns the year of contribution
14265: --============================================================================
14266: FUNCTION Get_Amt_Year
14267: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
14268: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
14269: ,p_effective_date IN DATE
14270: ,p_error_message OUT NOCOPY VARCHAR2
14271: ,p_data_element_value OUT NOCOPY VARCHAR2)
14264: --This function returns the year of contribution
14265: --============================================================================
14266: FUNCTION Get_Amt_Year
14267: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
14268: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
14269: ,p_effective_date IN DATE
14270: ,p_error_message OUT NOCOPY VARCHAR2
14271: ,p_data_element_value OUT NOCOPY VARCHAR2)
14272: RETURN NUMBER IS
14308: --============================================================================
14309: --This function returns the kind of contribution for FUR Pensions, from the ASG EIT
14310: --============================================================================
14311: FUNCTION Get_Fur_Contribution_Kind
14312: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
14313: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
14314: ,p_effective_date IN Date
14315: ,p_error_message OUT NOCOPY Varchar2
14316: ,p_data_element_value OUT NOCOPY Varchar2
14309: --This function returns the kind of contribution for FUR Pensions, from the ASG EIT
14310: --============================================================================
14311: FUNCTION Get_Fur_Contribution_Kind
14312: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
14313: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
14314: ,p_effective_date IN Date
14315: ,p_error_message OUT NOCOPY Varchar2
14316: ,p_data_element_value OUT NOCOPY Varchar2
14317: ) RETURN Number IS
14358: --=============================================================================
14359: --Function to return the kind of insurance between IPAP and ANW
14360: --=============================================================================
14361: FUNCTION Get_Ins_Cd_Anw_Ipap
14362: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
14363: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
14364: ,p_effective_date IN Date
14365: ,p_error_message OUT NOCOPY Varchar2
14366: ,p_data_element_value OUT NOCOPY Varchar2
14359: --Function to return the kind of insurance between IPAP and ANW
14360: --=============================================================================
14361: FUNCTION Get_Ins_Cd_Anw_Ipap
14362: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
14363: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
14364: ,p_effective_date IN Date
14365: ,p_error_message OUT NOCOPY Varchar2
14366: ,p_data_element_value OUT NOCOPY Varchar2
14367: ) RETURN Number IS
14392: --Function to return the type of insurance between IPAP and ANW
14393: --depending on the kind of insurance
14394: --============================================================================
14395: FUNCTION Get_Ins_Typ_Anw_Ipap
14396: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
14397: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
14398: ,p_effective_date IN Date
14399: ,p_error_message OUT NOCOPY Varchar2
14400: ,p_data_element_value OUT NOCOPY Varchar2
14393: --depending on the kind of insurance
14394: --============================================================================
14395: FUNCTION Get_Ins_Typ_Anw_Ipap
14396: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
14397: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
14398: ,p_effective_date IN Date
14399: ,p_error_message OUT NOCOPY Varchar2
14400: ,p_data_element_value OUT NOCOPY Varchar2
14401: ) RETURN Number IS
14496: --cursor to fetch the kind of employment code
14497: --from the soft coding key flex
14498: CURSOR c_get_emp_code IS
14499: SELECT scl.SEGMENT2||scl.SEGMENT3
14500: FROM per_all_assignments_f asg
14501: ,hr_soft_coding_keyflex scl
14502: WHERE asg.assignment_id = p_assignment_id
14503: AND p_effective_date BETWEEN asg.effective_start_date
14504: AND asg.effective_end_date
14672: -- Org_Id_DataElement
14673: -- =============================================================================
14674:
14675: FUNCTION Org_Id_DataElement
14676: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
14677: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
14678: ,p_effective_date IN Date
14679: ,p_error_message OUT NOCOPY Varchar2
14680: ,p_data_element_value OUT NOCOPY Varchar2
14673: -- =============================================================================
14674:
14675: FUNCTION Org_Id_DataElement
14676: (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
14677: ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
14678: ,p_effective_date IN Date
14679: ,p_error_message OUT NOCOPY Varchar2
14680: ,p_data_element_value OUT NOCOPY Varchar2
14681: ) RETURN Number IS
14715:
14716: l_ele_type_id pay_element_types_f.element_type_id%TYPE;
14717: l_prev_ele_type_id pay_element_types_f.element_type_id%TYPE;
14718: l_valid_action Varchar2(2);
14719: i per_all_assignments_f.business_group_id%TYPE;
14720: l_ext_dfn_type pqp_extract_attributes.ext_dfn_type%TYPE;
14721: l_proc_name Varchar2(150) := g_proc_name ||'Create_Addl_Assignments';
14722: l_assignment_id per_all_assignments_f.assignment_id%TYPE;
14723: l_organization_id per_all_assignments_f.organization_id%TYPE;
14718: l_valid_action Varchar2(2);
14719: i per_all_assignments_f.business_group_id%TYPE;
14720: l_ext_dfn_type pqp_extract_attributes.ext_dfn_type%TYPE;
14721: l_proc_name Varchar2(150) := g_proc_name ||'Create_Addl_Assignments';
14722: l_assignment_id per_all_assignments_f.assignment_id%TYPE;
14723: l_organization_id per_all_assignments_f.organization_id%TYPE;
14724: l_business_group_id per_all_assignments_f.business_group_id%TYPE;
14725: l_main_rec csr_rslt_dtl%ROWTYPE;
14726: l_new_rec csr_rslt_dtl%ROWTYPE;
14719: i per_all_assignments_f.business_group_id%TYPE;
14720: l_ext_dfn_type pqp_extract_attributes.ext_dfn_type%TYPE;
14721: l_proc_name Varchar2(150) := g_proc_name ||'Create_Addl_Assignments';
14722: l_assignment_id per_all_assignments_f.assignment_id%TYPE;
14723: l_organization_id per_all_assignments_f.organization_id%TYPE;
14724: l_business_group_id per_all_assignments_f.business_group_id%TYPE;
14725: l_main_rec csr_rslt_dtl%ROWTYPE;
14726: l_new_rec csr_rslt_dtl%ROWTYPE;
14727: l_effective_date Date;
14720: l_ext_dfn_type pqp_extract_attributes.ext_dfn_type%TYPE;
14721: l_proc_name Varchar2(150) := g_proc_name ||'Create_Addl_Assignments';
14722: l_assignment_id per_all_assignments_f.assignment_id%TYPE;
14723: l_organization_id per_all_assignments_f.organization_id%TYPE;
14724: l_business_group_id per_all_assignments_f.business_group_id%TYPE;
14725: l_main_rec csr_rslt_dtl%ROWTYPE;
14726: l_new_rec csr_rslt_dtl%ROWTYPE;
14727: l_effective_date Date;
14728: l_ext_rcd_id ben_ext_rcd.ext_rcd_id%TYPE;
14848: ,p_error_message OUT NOCOPY Varchar2
14849: ) RETURN Number IS
14850:
14851: l_return_value Number;
14852: i per_all_assignments_f.business_group_id%TYPE;
14853: l_ele_type_id pay_element_types_f.element_type_id%TYPE;
14854: l_prev_ele_type_id pay_element_types_f.element_type_id%TYPE;
14855: l_valid_action Varchar2(2);
14856: l_no_asg_action Number(5) := 0;
14859: l_effective_date Date;
14860: l_criteria_value Varchar2(2);
14861: l_warning_message Varchar2(2000);
14862: l_error_message Varchar2(2000);
14863: l_asg_type per_all_assignments_f.assignment_type%TYPE;
14864: l_main_rec csr_rslt_dtl%ROWTYPE;
14865: l_person_id per_all_people_f.person_id%TYPE;
14866: l_assignment_id per_all_assignments_f.assignment_id%TYPE;
14867: l_mutli_assig Varchar2(150);
14862: l_error_message Varchar2(2000);
14863: l_asg_type per_all_assignments_f.assignment_type%TYPE;
14864: l_main_rec csr_rslt_dtl%ROWTYPE;
14865: l_person_id per_all_people_f.person_id%TYPE;
14866: l_assignment_id per_all_assignments_f.assignment_id%TYPE;
14867: l_mutli_assig Varchar2(150);
14868: BEGIN
14869:
14870: Hr_Utility.set_location('Entering: '||l_proc_name, 5);