1 PACKAGE pqp_gb_omp_daily_absences AS
2 /* $Header: pqgbdomp.pkh 120.0.12000000.1 2007/01/16 03:44:16 appldev noship $ */
3
4
5
6 -- Through out this Package wherever any parameter/variable if defined as
7 -- p_entitlements or l_entitlements will be used to store or pass a persons
8 -- Entitltments Band Wise.
9 -- Variable/Parameter p_absences_taken_to_date stores the entitlememts
10 -- used up
11 --Variable/paramter p_entitlements_remaining stores the entitlements
12 -- that are avaialable at that point of time Band wise
13
14
15
16 -- Record to store the EIT Information of the Plan.
17 TYPE rec_plan_information IS RECORD
18 ( plan_name
19 pay_element_type_extra_info.eei_information1%TYPE
20 ,Absence_Entitlement_Days_Type
21 pay_element_type_extra_info.eei_information9%TYPE
22 ,Absence_Entitlement_Parameters
23 pay_element_type_extra_info.eei_information11%TYPE
24 ,Absence_Entitlement_Holidays
25 pay_element_type_extra_info.eei_information12%TYPE
26 ,Daily_Rate_Divisor_Type
27 pay_element_type_extra_info.eei_information13%TYPE
28 ,Daily_Rate_Divisor_Duration
29 pay_element_type_extra_info.eei_information15%TYPE
30 ,default_work_pattern
31 pay_element_type_extra_info.eei_information18%TYPE
32 ,calendar_rules_list
33 pay_element_type_extra_info.eei_information27%TYPE
34 );
35
36
37
38 -- Cursor to get the Absence Information.
39
40 CURSOR csr_absence_info ( p_absence_id IN NUMBER ) IS
41 SELECT paa.date_start
42 ,paa.date_end
43 FROM per_absence_attendances paa
44 WHERE paa.absence_attendance_id = p_absence_id ;
45
46 -- The Cursor gets the used Entitlements Band wise
47 CURSOR csr_get_band_bal ( p_assignment_id IN NUMBER
48 ,p_business_group_id IN NUMBER
49 ,p_absence_id IN NUMBER
50 ,p_lookup_type IN VARCHAR2
51 ,p_pl_typ_id IN NUMBER) IS
52 SELECT pgda.level_of_entitlement
53 ,SUM (pgda.duration) consumed
54 ,SUM (pgda.duration_in_hours) consumed_in_hours
55 FROM pqp_gap_daily_absences pgda
56 ,pqp_gap_absence_plans pgap
57 ,per_absence_attendances paa
58 ,ben_pl_f bpf
59 WHERE pgda.gap_absence_plan_id = pgap.gap_absence_plan_id
60 AND pgap.absence_attendance_id = paa.absence_attendance_id
61 AND pgap.assignment_id = p_assignment_id
62 AND paa.absence_attendance_id = p_absence_id
63 AND bpf.pl_id = pgap.pl_id
64 AND bpf.pl_typ_id = p_pl_typ_id
65 AND bpf.business_group_id = p_business_group_id
66 AND bpf.business_group_id = paa.business_group_id
67 AND ( pgda.level_of_entitlement <> 'NOT'
68 AND pgda.level_of_entitlement <> 'NOBAND'
69 AND pgda.level_of_entitlement NOT IN
70 ( SELECT lookup_code
71 FROM hr_lookups --fnd_common_lookups
72 -- changed to hr_lookups for bug 3780751
73 WHERE lookup_type = p_lookup_type )
74 )
75 GROUP BY level_of_entitlement
76 ORDER BY level_of_entitlement ;
77
78 -- Cursor to get the Part Days Information
79
80 CURSOR c_part_days ( p_absence_id IN NUMBER
81 ,p_lookup_type IN VARCHAR2 ) IS
82 SELECT NVL(abs_information1,1) part_st_dt
83 ,NVL(abs_information2,1) Part_end_dt
84 FROM per_absence_attendances
85 WHERE abs_information_category = p_lookup_type
86 AND absence_attendance_id = p_absence_id ;
87
88
89
90 CURSOR c_get_abs_id ( p_plan_id IN NUMBER
91 ,p_absence_id IN NUMBER ) IS
92 SELECT pgap.gap_absence_plan_id abs_id
93 ,pgap.object_version_number
94 FROM pqp_gap_absence_plans pgap
95 WHERE pgap.absence_attendance_id = p_absence_id
96 AND pgap.pl_id = p_plan_id ;
97
98 -------------debug------------------------------
99 PROCEDURE debug
100 (p_trace_message IN VARCHAR2,
101 p_trace_location IN NUMBER DEFAULT NULL
102 ) ;
103
104 -------------debug_enter-----------------------
105 PROCEDURE debug_enter
106 (p_proc_name IN VARCHAR2 DEFAULT NULL,
107 p_trace_on IN VARCHAR2 DEFAULT NULL
108 ) ;
109
110 -------------debug_exit-----------------------
111 PROCEDURE debug_exit
112 (p_proc_name IN VARCHAR2 DEFAULT NULL,
113 p_trace_off IN VARCHAR2 DEFAULT NULL
114 ) ;
115
116 -------------create_daily_absences--------------
117 PROCEDURE create_absence_plan_details -- create_daily_absences
118 (p_assignment_id IN NUMBER
119 ,p_person_id IN NUMBER
120 ,p_business_group_id IN NUMBER
121 ,p_absence_id IN NUMBER
122 ,p_absence_date_start IN DATE
123 ,p_absence_date_end IN DATE
124 ,p_pl_id IN NUMBER
125 ,p_pl_typ_id IN NUMBER
126 ,p_element_type_id IN NUMBER
127 ,p_create_start_date IN DATE
128 ,p_create_end_date IN DATE
129 ,p_output_type IN ff_exec.outputs_t
130 ,p_error_code OUT NOCOPY NUMBER
131 ,p_message OUT NOCOPY VARCHAR2
132 ) ;
133
134 ---------------get_plan_extra_info_n_cache_it----------------
135 PROCEDURE get_plan_extra_info_n_cache_it(
136 p_pl_id IN NUMBER
137 ,p_plan_information IN OUT NOCOPY rec_plan_information
138 ,p_pl_typ_id IN NUMBER
139 ,p_error_code OUT NOCOPY NUMBER
140 ,p_message OUT NOCOPY VARCHAR2
141 ) ;
142 -----------get_entitlement_info-------------------
143 PROCEDURE get_entitlement_info
144 (
145 p_business_group_id IN NUMBER
146 ,p_effective_date IN DATE
147 ,p_assignment_id IN NUMBER
148 ,p_pl_id IN NUMBER -- added RR
149 ,p_entitlement_tab_id IN NUMBER
150 ,p_absence_id IN NUMBER
151 ,p_absence_ent_uom IN VARCHAR2
152 ,p_start_date IN DATE
153 ,p_benefits_length_of_service IN NUMBER
154 ,p_entitlements IN OUT NOCOPY pqp_absval_pkg.t_entitlements
155 ,p_error_code OUT NOCOPY NUMBER
156 ,p_message OUT NOCOPY VARCHAR2
157 ) ;
158 ---------------get_entitlements_consumed--------------
159 PROCEDURE get_entitlements_consumed
160 (
161 p_assignment_id IN NUMBER
162 ,p_business_group_id IN NUMBER
163 ,p_effective_date IN DATE
164 ,p_absence_id IN NUMBER
165 ,p_pl_typ_id IN NUMBER
166 ,p_entitlements IN OUT NOCOPY pqp_absval_pkg.t_entitlements
167 ,p_absences_taken_to_date IN OUT NOCOPY pqp_absval_pkg.t_entitlements
168 ,p_lookup_type IN VARCHAR2
169 ,p_error_code OUT NOCOPY NUMBER
170 ,p_message OUT NOCOPY VARCHAR2
171 ) ;
172 ---------update_absence_plan_details --------------
173 PROCEDURE update_absence_plan_details
174 ( p_assignment_id IN NUMBER
175 ,p_person_id IN NUMBER
176 ,p_business_group_id IN NUMBER
177 ,p_absence_id IN NUMBER
178 ,p_absence_date_start IN DATE
179 ,p_absence_date_end IN DATE
180 ,p_pl_id IN NUMBER
181 ,p_pl_typ_id IN NUMBER
182 ,p_element_type_id IN NUMBER
183 ,p_update_start_date IN date
184 ,p_update_end_date IN date
185 ,p_output_type IN ff_exec.outputs_t
186 ,p_error_code OUT NOCOPY NUMBER
187 ,p_message OUT NOCOPY VARCHAR2 ) ;
188
189 -----------------------get_entitlements_remaining----------------
190 PROCEDURE get_entitlements_remaining
191 (p_entitlements IN pqp_absval_pkg.t_entitlements
192 ,p_absences_taken_to_date IN pqp_absval_pkg.t_entitlements
193 ,p_entitlement_UOM IN VARCHAR2
194 ,p_entitlements_remaining IN OUT NOCOPY pqp_absval_pkg.t_entitlements
195 ,p_error_code OUT NOCOPY NUMBER
196 ,p_message OUT NOCOPY VARCHAR2 ) ;
197 -----------------------get_entitlement_balance----------------
198 PROCEDURE get_entitlement_balance (
199 p_assignment_id IN NUMBER
200 ,p_business_group_id IN NUMBER
201 ,p_pl_typ_id IN NUMBER
202 ,p_effective_date IN DATE
203 ,p_absences_taken_to_date IN OUT NOCOPY pqp_absval_pkg.t_entitlements
204 ,p_error_code OUT NOCOPY NUMBER
205 ,p_message OUT NOCOPY VARCHAR2
206 ) ;
207
208 END pqp_gb_omp_daily_absences ;
[Home] [Help]