1 Package pay_retro_status_load as
2 /* $Header: pyrtsupl.pkh 120.2.12010000.1 2008/11/19 08:58:49 nerao ship $ */
3
4 --
5 -- Global Constants
6 --
7 g_update_mode constant varchar2(6):= 'UPDATE';
8 g_delete_mode constant varchar2(6):= 'DELETE';
9
10 --
11 -- Global Types
12 --
13 type t_retro_entry_rec is record
14 (element_entry_id number
15 ,reprocess_date date
16 ,retro_component_id number
17 );
18
19 type t_retro_entry_tab is table of t_retro_entry_rec
20 index by binary_integer;
21
22 --
23 -- ----------------------------------------------------------------------------
24 -- |----------------------< load_retro_asg_and_entries >----------------------|
25 -- ----------------------------------------------------------------------------
26 -- {Start Of Comments}
27 --
28 -- Description:
29 -- This procedure creates a new unprocessed retro assignment and retro entries.
30 -- If an unprocessed retro assignment already exists for the assignment, the
31 -- new retro assignment will supersede the existing one.
32 --
33 -- Prerequisites:
34 -- Please make sure that this procedure is used where the user login
35 -- information is established so that the WHO columns are populated properly.
36 -- If this procedure is called from a standalone script, it is advisable to
37 -- make the following call once before calling this procedure.
38 --
39 -- fnd_global.apps_initialize
40 -- (user_id => <User ID>
41 -- ,resp_id => <Responsibility ID>
42 -- ,resp_appl_id => <Responsibility Application ID>
43 -- );
44 --
45 -- In Parameters:
46 -- Name Reqd Type Description
47 -- p_assignment_id Yes Number Assignment ID.
48 -- p_reprocess_date Yes Date Reprocess Date of the retro
49 -- assignment. This parameter is
50 -- required when no outstanding
51 -- retro assignment does not
52 -- exist for the assignment.
53 -- The date must be equal to or
54 -- earlier than the earliest
55 -- retro entry reprocess date.
56 -- p_approval_status No Varchar2 Approval Status. If the
57 -- localization rule allows
58 -- multiple status, A (Confirmed
59 -- - Awaiting Processing) or D
60 -- (Deferred) is available.
61 -- Otherwise the value has to be
62 -- P (Included - Awaiting
63 -- Processing).
64 --
65 -- p_retro_entry_tab Yes Table Table of retro entry records.
66 --
67 -- You can add as many retro entries as you require, but the table index
68 -- should start with 1. The retro entry record is as follows.
69 --
70 -- element_entry_id Yes number Element Entry ID.
71 -- reprocess_date Yes date Reprocess Date. This parameter
72 -- is required when the equivalent
73 -- retro entry does not exist.
74 -- retro_component_id No number Retro Component ID.
75 --
76 --
77 -- Post Success:
78 -- The procedure will set the following out parameters:
79 -- Name Type Description
80 -- p_retro_assignment_id Number Retro Assignment ID
81 --
82 -- Post Failure:
83 -- The procedure will not create or update a retro assignment and raises an
84 -- error.
85 --
86 -- {End Of Comments}
87 --
88 procedure load_retro_asg_and_entries
89 (p_assignment_id in number
90 ,p_reprocess_date in date
91 ,p_approval_status in varchar2 default null
92 ,p_retro_entry_tab in t_retro_entry_tab
93 ,p_retro_assignment_id out nocopy number
94 );
95
96 --
97 -- ----------------------------------------------------------------------------
98 -- |----------------------< load_retro_asg_and_entries >----------------------|
99 -- ----------------------------------------------------------------------------
100 -- {Start Of Comments}
101 --
102 -- Description:
103 -- This is an overload version of load_retro_assignment.
104 -- This procedure creates a new unprocessed retro assignment and retro entries.
105 -- If an unprocessed retro assignment already exists for the assignment, the
106 -- new retro assignment will supersede the existing one.
107 --
108 -- Prerequisites:
109 -- Please make sure that this procedure is used where the user login
110 -- information is established so that the WHO columns are populated properly.
111 -- If this procedure is called from a standalone script, it is advisable to
112 -- make the following call once before calling this procedure.
113 --
114 -- fnd_global.apps_initialize
115 -- (user_id => <User ID>
116 -- ,resp_id => <Responsibility ID>
117 -- ,resp_appl_id => <Responsibility Application ID>
118 -- );
119 --
120 -- In Parameters:
121 -- Name Reqd Type Description
122 -- p_business_group_id Yes Number Business Group ID.
123 -- p_assignment_number Yes Varchar2 Assignment Number.
124 -- p_full_name No Varchar2 Full Name of the person to
125 -- identify the assignment for
126 -- sure.
127 -- p_reprocess_date Yes Date Reprocess Date of the retro
128 -- assignment. This parameter is
129 -- required when no outstanding
130 -- retro assignment does not
131 -- exist for the assignment.
132 -- The date must be equal to or
133 -- earlier than the earliest
134 -- retro entry reprocess date.
135 -- p_approval_status No Varchar2 Approval Status. If the
136 -- localization rule allows
137 -- multiple status, A (Confirmed
138 -- - Awaiting Processing) or D
139 -- (Deferred) is available.
140 -- Otherwise the value has to be
141 -- P (Included - Awaiting
142 -- Processing).
143 --
144 -- p_entry<entry number>_<attribute name> parameters are used for creating
145 -- a retro entry, hence if either element name or element entry ID is
146 -- specified for an entry number, corresponding reprocess date must be
147 -- specified as well. If element entry ID is specified, the value entered
148 -- for element name will be ignored. If element name is specified without
149 -- element entry ID, the procedure attempts to find the equivalent element
150 -- entry, but it will fail if there are more than one entry for the element
151 -- type and the assignment.
152 --
153 -- p_entry1_element_name No Varchar2 Element Name.
154 -- p_entry1_element_entry_id No Number Element Entry ID.
155 -- p_entry1_reprocess_date No Date Reprocess Date. This parameter
156 -- is required when the equivalent
157 -- retro entry does not exist.
158 -- p_entry1_component_name No Varchar2 Retro Component Name. Also
159 -- known as Recalculation Reason.
160 --
161 -- ::::::::::::::::::::::::::
162 --
163 -- p_entry15_element_name No Varchar2 Element Name.
164 -- p_entry15_element_entry_id No Number Element Entry ID.
165 -- p_entry15_reprocess_date No Date Reprocess Date.
166 -- p_entry15_component_name No Varchar2 Retro Component Name. Also
167 -- known as Recalculation Reason.
168 --
169 -- Post Success:
170 -- The procedure will set the following out parameters:
171 -- Name Type Description
172 -- p_retro_assignment_id Number Retro Assignment ID
173 --
174 -- Post Failure:
175 -- The procedure will not create or update a retro assignment and raises an
176 -- error.
177 --
178 -- {End Of Comments}
179 --
180 procedure load_retro_asg_and_entries
181 (p_business_group_id in number
182 ,p_assignment_number in varchar2
183 ,p_full_name in varchar2 default null
184 ,p_reprocess_date in date
185 ,p_approval_status in varchar2 default null
186 --
187 ,p_entry1_element_name in varchar2 default null
188 ,p_entry1_element_entry_id in number default null
189 ,p_entry1_reprocess_date in date default null
190 ,p_entry1_component_name in varchar2 default null
191 --
192 ,p_entry2_element_name in varchar2 default null
193 ,p_entry2_element_entry_id in number default null
194 ,p_entry2_reprocess_date in date default null
195 ,p_entry2_component_name in varchar2 default null
196 --
197 ,p_entry3_element_name in varchar2 default null
198 ,p_entry3_element_entry_id in number default null
199 ,p_entry3_reprocess_date in date default null
200 ,p_entry3_component_name in varchar2 default null
201 --
202 ,p_entry4_element_name in varchar2 default null
203 ,p_entry4_element_entry_id in number default null
204 ,p_entry4_reprocess_date in date default null
205 ,p_entry4_component_name in varchar2 default null
206 --
207 ,p_entry5_element_name in varchar2 default null
208 ,p_entry5_element_entry_id in number default null
209 ,p_entry5_reprocess_date in date default null
210 ,p_entry5_component_name in varchar2 default null
211 --
212 ,p_entry6_element_name in varchar2 default null
213 ,p_entry6_element_entry_id in number default null
214 ,p_entry6_reprocess_date in date default null
215 ,p_entry6_component_name in varchar2 default null
216 --
217 ,p_entry7_element_name in varchar2 default null
218 ,p_entry7_element_entry_id in number default null
219 ,p_entry7_reprocess_date in date default null
220 ,p_entry7_component_name in varchar2 default null
221 --
222 ,p_entry8_element_name in varchar2 default null
223 ,p_entry8_element_entry_id in number default null
224 ,p_entry8_reprocess_date in date default null
225 ,p_entry8_component_name in varchar2 default null
226 --
227 ,p_entry9_element_name in varchar2 default null
228 ,p_entry9_element_entry_id in number default null
229 ,p_entry9_reprocess_date in date default null
230 ,p_entry9_component_name in varchar2 default null
231 --
232 ,p_entry10_element_name in varchar2 default null
233 ,p_entry10_element_entry_id in number default null
234 ,p_entry10_reprocess_date in date default null
235 ,p_entry10_component_name in varchar2 default null
236 --
237 ,p_entry11_element_name in varchar2 default null
238 ,p_entry11_element_entry_id in number default null
239 ,p_entry11_reprocess_date in date default null
240 ,p_entry11_component_name in varchar2 default null
241 --
242 ,p_entry12_element_name in varchar2 default null
243 ,p_entry12_element_entry_id in number default null
244 ,p_entry12_reprocess_date in date default null
245 ,p_entry12_component_name in varchar2 default null
246 --
247 ,p_entry13_element_name in varchar2 default null
248 ,p_entry13_element_entry_id in number default null
249 ,p_entry13_reprocess_date in date default null
250 ,p_entry13_component_name in varchar2 default null
251 --
252 ,p_entry14_element_name in varchar2 default null
253 ,p_entry14_element_entry_id in number default null
254 ,p_entry14_reprocess_date in date default null
255 ,p_entry14_component_name in varchar2 default null
256 --
257 ,p_entry15_element_name in varchar2 default null
258 ,p_entry15_element_entry_id in number default null
259 ,p_entry15_reprocess_date in date default null
260 ,p_entry15_component_name in varchar2 default null
261 --
262 ,p_retro_assignment_id out nocopy number
263 );
264
265 --
266 -- ----------------------------------------------------------------------------
267 -- |----------------------< update_or_delete_retro_asg >----------------------|
268 -- ----------------------------------------------------------------------------
269 -- {Start Of Comments}
270 --
271 -- Description:
272 -- This procedure updates or deletes an existing unprocessed retro assignment.
273 -- If the delete mode is selected and the retro assignment is superseding the
274 -- previous version of retro assignment, this procedure will remove the latest
275 -- changes and revert it back to the previous state of retro assignment.
276 --
277 -- Prerequisites:
278 -- None.
279 --
280 -- In Parameters:
281 -- Name Reqd Type Description
282 -- p_business_group_id Yes Number Business Group ID.
283 -- p_assignment_number Yes Varchar2 Assignment Number.
284 -- p_full_name No Varchar2 Full Name.
285 -- p_reprocess_date No Date Reprocess Date (for update
286 -- only).
287 -- p_approval_status No Varchar2 Approval Status (for update
288 -- only).
289 -- p_update_or_delete_mode No Varchar2 UPDATE or DELETE.
290 --
291 -- Post Success:
292 -- The procedure will set the following out parameters:
293 -- Name Type Description
294 --
295 -- Post Failure:
296 -- The procedure will not update a retro entry and raises an error.
297 --
298 -- {End Of Comments}
299 --
300 procedure update_or_delete_retro_asg
301 (p_business_group_id in number
302 ,p_assignment_number in varchar2
303 ,p_full_name in varchar2 default null
304 ,p_reprocess_date in date default null
305 ,p_approval_status in varchar2 default null
306 ,p_update_or_delete_mode in varchar2 default g_update_mode
307 );
308
309 --
310 --
311 -- ----------------------------------------------------------------------------
312 -- |------------------------< update_reprocess_date >-------------------------|
313 -- ----------------------------------------------------------------------------
314 -- {Start Of Comments}
315 --
316 -- Description:
317 -- This procedure is used to update the reprocess date of the system created
318 -- retro assignments by creating new retro assignment with the new reprocess
319 -- date and superseding the system created retro assignment with the newly
320 -- created retro assignment.
321 --
322 -- Prerequisites:
323 -- Please make sure that this procedure is used where the user login
324 -- information is established so that the WHO columns are populated properly.
325 -- If this procedure is called from a standalone script, it is advisable to
326 -- make the following call once before calling this procedure.
327 --
328 -- fnd_global.apps_initialize
329 -- (user_id => <User ID>
330 -- ,resp_id => <Responsibility ID>
331 -- ,resp_appl_id => <Responsibility Application ID>
332 -- );
333 --
334 -- In Parameters:
335 -- Name Reqd Type Description
336 -- p_assignment_number Yes varchar2 Assignment Number.
337 -- p_full_name No Varchar2 Full Name of the person to
338 -- identify the assignment for
339 -- sure.
340 -- p_reprocess_date Yes Date Reprocess Date of the retro
341 -- assignment. This parameter is
342 -- required when no outstanding
343 -- retro assignment does not
344 -- exist for the assignment.
345 -- The date must be equal to or
346 -- earlier than the earliest
347 -- retro entry reprocess date.
348 --
349 --
350 -- Post Success:
351 -- The procedure will set the following out parameters:
352 -- Name Type Description
353 -- p_new_retro_asg_id Number Newly created Retro Assignment ID
354 --
355 -- Post Failure:
356 -- The procedure will not create or update a retro assignment and raises an
357 -- error.
358 --
359 -- {End Of Comments}
360 procedure update_reprocess_date
361 (p_business_group_id in number
362 ,p_assignment_number in varchar2
363 ,p_full_name in varchar2 default null
364 ,p_reprocess_date in date
365 ,p_new_retro_asg_id out nocopy number
366 );
367 --
368 end pay_retro_status_load;