[Home] [Help]
PACKAGE BODY: APPS.SSPWSENT_PKG
Source
1 PACKAGE BODY SSPWSENT_PKG as
2 /* $Header: sspwsent.pkb 120.1 2005/06/15 03:20:58 tukumar noship $ */
3
4 procedure fetch_maternity_details (
5 --
6 p_maternity_ID in number,
7 p_SMP_due_date out NOCOPY date,
8 p_person_ID out NOCOPY number,
9 p_matching_date out NOCOPY date
10 ) is
11 --
12 cursor c1 is
13 --
14 select due_date,
15 person_ID,
16 matching_date
17 from ssp_maternities MAT
18 where mat.maternity_ID = p_maternity_ID;
19 --
20 begin
21 --
22 open c1;
23 fetch c1 into p_SMP_due_date,
24 p_person_ID,
25 p_matching_date;
26 close c1;
27 --
28 end fetch_maternity_details;
29 --------------------------------------------------------------------------------
30
31 PROCEDURE fetch_absence_details (p_absence_id in number,
32 p_ABSENCE_CATEGORY out NOCOPY varchar2,
33 P_PERSON_ID out NOCOPY number,
34 p_SICKNESS_START_DATE out NOCOPY date,
35 p_SICKNESS_END_DATE out NOCOPY date,
36 P_MATERNITY_ID out NOCOPY number,
37 P_SMP_DUE_DATE out NOCOPY date,
38 P_LINKED_ABSENCE_ID out NOCOPY number) is
39 cursor c1 is
40 select ABSENCE_CATEGORY,
41 PERSON_ID,
42 SICKNESS_START_DATE,
43 SICKNESS_END_DATE,
44 MATERNITY_ID,
45 SMP_DUE_DATE,
46 LINKED_ABSENCE_ID
47 from per_absence_attendances_v
48 where ABSENCE_ATTENDANCE_ID = p_absence_id;
49 BEGIN
50 open c1;
51 fetch c1 into p_ABSENCE_CATEGORY,
52 P_PERSON_ID,
53 p_SICKNESS_START_DATE,
54 p_SICKNESS_END_DATE,
55 P_MATERNITY_ID,
56 P_SMP_DUE_DATE,
57 P_LINKED_ABSENCE_ID;
58 close c1;
59 END fetch_absence_details;
60 --------------------------------------------------------------------------------
61 function fetch_element_type (p_effective_date in date,
62 p_absence_category varchar2) return number is
63 lv_element_type_id pay_element_types_f.element_type_id%type;
64 lv_element_name pay_element_types_f.element_name%TYPE ;
65
66 cursor csr_element_details (p_element_name varchar2 ) is
67 select element_type_id
68 from pay_element_types_f
69 where element_name = p_element_name
70 and p_effective_date between effective_start_date
71 and effective_end_date;
72 begin
73 if p_absence_category = 'S' then
74 lv_element_name := SSP_SSP_PKG.c_SSP_element_name;
75 elsif p_absence_category = 'M' then
76 lv_element_name := SSP_SMP_PKG.c_SMP_element_name;
77 elsif p_absence_category = 'GB_ADO' then
78 lv_element_name := SSP_SAP_PKG.c_SAP_element_name;
79 elsif p_absence_category = 'GB_PAT_BIRTH' then
80 lv_element_name := SSP_PAB_PKG.c_PAB_element_name;
81 elsif p_absence_category = 'GB_PAT_ADO' then
82 lv_element_name := SSP_PAD_PKG.c_PAD_element_name;
83 end if;
84
85 open csr_element_details (lv_element_name);
86 fetch csr_element_details into lv_element_type_id;
87 return lv_element_type_id;
88 end fetch_element_type;
89 --------------------------------------------------------------------------------
90
91 END SSPWSENT_PKG;