PACKAGE BODY: APPS.SSPWSENT_PKG

Source

1 PACKAGE BODY SSPWSENT_PKG as
2 /* $Header: sspwsent.pkb 120.2 2010/11/17 09:34:54 npannamp ship $ */
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 elsif p_absence_category = 'GB_ADDL_PAT_BIRTH' then
84    lv_element_name := SSP_APAB_PKG.c_APAB_element_name;
85 elsif p_absence_category = 'GB_ADDL_PAT_ADOPT' then
86    lv_element_name := SSP_APAD_PKG.c_APAD_element_name;
87 end if;
88 
89 open csr_element_details (lv_element_name);
90 fetch csr_element_details into lv_element_type_id;
91 return lv_element_type_id;
92 end fetch_element_type;
93 --------------------------------------------------------------------------------
94 
95 END SSPWSENT_PKG;