1 PACKAGE PSP_LABOR_DIST as
2 --$Header: PSPLDCDS.pls 120.8.12010000.1 2008/07/28 08:07:21 appldev ship $
3
4 g_error_api_path VARCHAR2(1000) := '';
5 g_msg VARCHAR2(2000) := '';
6 --- added global variables for 5080403
7 g_global_element_autopop varchar2(1);
8 g_asg_element_autopop varchar2(1);
9 g_asg_ele_group_autopop varchar2(1);
10 g_asg_autopop varchar2(1);
11 g_org_schedule_autopop varchar2(1);
12 g_default_account_autopop varchar2(1);
13 g_suspense_account_autopop varchar2(1);
14 g_excess_account_autopop varchar2(1);
15 type t_num_15_type is table of number(15) index by binary_integer;
16 type t_varchar_30_type is table of varchar2(30) index by binary_integer;
17 type t_date_type is table of date index by binary_integer;
18 t_payroll_sub_line_id t_num_15_type;
19 t_effective_date t_date_type;
20 t_person_id t_num_15_type;
21 t_assignment_id t_num_15_type;
22 t_element_type_id t_num_15_type;
23 t_project_id t_num_15_type;
24 t_expenditure_organization_id t_num_15_type;
25 t_expenditure_type t_varchar_30_type;
26 t_task_id t_num_15_type;
27 t_award_id t_num_15_type;
28 t_gl_code_combination_id t_num_15_type;
29 t_account_id t_num_15_type;
30 t_cost_id t_num_15_type;
31 t_payroll_action_type t_varchar_30_type;
32 ---------------------------------
33
34 g_distribution_line_id NUMBER(10);
35 g_num_dist NUMBER := 0;
36 g_tot_dist_amount NUMBER := 0;
37
38 g_source_type psp_payroll_controls.source_type%type;
39 g_source_code psp_payroll_controls.payroll_source_code%type;
40 g_time_period_id psp_payroll_controls.time_period_id%type;
41 g_batch_name psp_payroll_controls.batch_name%type;
42 g_set_of_books_id psp_payroll_controls.set_of_books_id%type;
43 g_business_group_id psp_payroll_controls.business_group_id%type;
44 g_payroll_id psp_payroll_controls.payroll_id%type;
45 g_payroll_action_id psp_payroll_controls.cdl_payroll_action_id%type;
46
47 -- moved following variables from packaged body to here for optimization ..
48 -- in context of 4744285
49 g_salary_cap_option varchar2(50); --- added for 4304623
50 g_gen_excess_org_id number; --- 4744285
51 g_use_eff_date Varchar2(1); /* Bug 1874696 */
52 g_dff_grouping_option CHAR(1); -- Introduced for bug fix 2908859
53 g_cap_element_set_id integer; --- added for 4304623
54
55 -- PL/SQL Record and table definition for PSB - LD Integration
56
57 TYPE g_ldcostmap_rec_type IS RECORD
58 (gl_code_combination_id NUMBER,
59 project_id NUMBER,
60 task_id NUMBER,
61 award_id NUMBER,
62 expenditure_organization_id NUMBER,
63 expenditure_type VARCHAR2(30),
64 percent NUMBER(5,2),
65 effective_start_date DATE,
66 effective_end_date DATE,
67 -- description VARCHAR2(185)); Commented for bug fix 2628089
68 description VARCHAR2(365)); -- Introduced for bug fix 2628089
69
70 TYPE g_ldcostmap_tbl_type is TABLE OF g_ldcostmap_rec_type
71 INDEX BY BINARY_INTEGER;
72
73 g_charging_instructions g_ldcostmap_tbl_type;
74
75 PROCEDURE create_lines (errbuf OUT NOCOPY VARCHAR2,
76 retcode OUT NOCOPY VARCHAR2,
77 p_source_type IN VARCHAR2,
78 p_source_code IN VARCHAR2,
79 p_payroll_id IN NUMBER,
80 p_time_period_id IN NUMBER,
81 p_batch_name IN VARCHAR2,
82 p_business_group_id IN NUMBER,
83 p_set_of_books_id IN NUMBER,
84 p_start_asg_id IN NUMBER,
85 p_end_asg_id IN NUMBER);
86
87 PROCEDURE Get_Distribution_Lines
88 (p_proc_executed OUT NOCOPY VARCHAR2,
89 p_person_id IN NUMBER := FND_API.G_MISS_NUM,
90 p_sub_line_id IN NUMBER := FND_API.G_MISS_NUM,
91 p_assignment_id IN NUMBER := FND_API.G_MISS_NUM,
92 p_element_type_id IN NUMBER := FND_API.G_MISS_NUM,
93 p_payroll_start_date IN DATE := FND_API.G_MISS_DATE,
94 p_daily_rate IN NUMBER := FND_API.G_MISS_NUM,
95 p_effective_date IN DATE := FND_API.G_MISS_DATE,
96 p_mode IN VARCHAR2 := 'I',
97 p_business_group_id IN NUMBER,
98 p_set_of_books_id IN NUMBER,
99 p_attribute_category IN VARCHAR2 default null, -- Introduced DFF parameters for bug fix 2908859
100 p_attribute1 IN VARCHAR2 default null,
101 p_attribute2 IN VARCHAR2 default null,
102 p_attribute3 IN VARCHAR2 default null,
103 p_attribute4 IN VARCHAR2 default null,
104 p_attribute5 IN VARCHAR2 default null,
105 p_attribute6 IN VARCHAR2 default null,
106 p_attribute7 IN VARCHAR2 default null,
107 p_attribute8 IN VARCHAR2 default null,
108 p_attribute9 IN VARCHAR2 default null,
109 p_attribute10 IN VARCHAR2 default null,
110 p_or_gl_ccid IN NUMBER DEFAULT NULL,
111 p_or_project_id IN NUMBER DEFAULT NULL,
112 p_or_task_id IN NUMBER DEFAULT NULL,
113 p_or_award_id IN NUMBER DEFAULT NULL,
114 p_or_expenditure_org_id IN NUMBER DEFAULT NULL,
115 p_or_expenditure_type IN VARCHAR2 DEFAULT NULL,
116 p_return_status OUT NOCOPY VARCHAR2);
117
118 PROCEDURE global_earnings_element(p_proc_executed OUT NOCOPY VARCHAR2,
119 p_person_id IN NUMBER,
120 p_sub_line_id IN NUMBER,
121 p_assignment_id IN NUMBER,
122 p_element_type_id IN NUMBER,
123 p_payroll_start_date IN DATE,
124 p_daily_rate IN NUMBER,
125 p_org_def_account IN VARCHAR2,
126 p_effective_date IN DATE,
127 p_mode IN VARCHAR2 := 'I',
128 p_business_group_id IN NUMBER,
129 p_set_of_books_id IN NUMBER,
130 p_attribute_category IN VARCHAR2, -- Introduced DFF parameters for bug fix 2908859
131 p_attribute1 IN VARCHAR2,
132 p_attribute2 IN VARCHAR2,
133 p_attribute3 IN VARCHAR2,
134 p_attribute4 IN VARCHAR2,
135 p_attribute5 IN VARCHAR2,
136 p_attribute6 IN VARCHAR2,
137 p_attribute7 IN VARCHAR2,
138 p_attribute8 IN VARCHAR2,
139 p_attribute9 IN VARCHAR2,
140 p_attribute10 IN VARCHAR2,
141 p_return_status OUT NOCOPY VARCHAR2);
142
143
144 PROCEDURE element_type_hierarchy(p_proc_executed OUT NOCOPY VARCHAR2,
145 p_person_id IN NUMBER,
146 p_sub_line_id IN NUMBER,
147 p_assignment_id IN NUMBER,
148 p_element_type_id IN NUMBER,
149 p_payroll_start_date IN DATE,
150 p_daily_rate IN NUMBER,
151 p_org_def_account IN VARCHAR2,
152 p_effective_date IN DATE,
153 p_mode IN VARCHAR2 := 'I',
154 p_business_group_id IN NUMBER,
155 p_set_of_books_id IN NUMBER,
156 p_attribute_category IN VARCHAR2, -- Introduced DFF parameters for bug fix 2908859
157 p_attribute1 IN VARCHAR2,
158 p_attribute2 IN VARCHAR2,
159 p_attribute3 IN VARCHAR2,
160 p_attribute4 IN VARCHAR2,
161 p_attribute5 IN VARCHAR2,
162 p_attribute6 IN VARCHAR2,
163 p_attribute7 IN VARCHAR2,
164 p_attribute8 IN VARCHAR2,
165 p_attribute9 IN VARCHAR2,
166 p_attribute10 IN VARCHAR2,
167 p_return_status OUT NOCOPY VARCHAR2);
168
169
170 PROCEDURE element_class_hierarchy(p_proc_executed OUT NOCOPY VARCHAR2,
171 p_person_id IN NUMBER,
172 p_sub_line_id IN NUMBER,
173 p_assignment_id IN NUMBER,
174 p_element_type_id IN NUMBER,
175 p_payroll_start_date IN DATE,
176 p_daily_rate IN NUMBER,
177 p_org_def_account IN VARCHAR2,
178 p_effective_date IN DATE,
179 p_mode IN VARCHAR2 := 'I',
180 p_business_group_id IN NUMBER,
181 p_set_of_books_id IN NUMBER,
182 p_attribute_category IN VARCHAR2, -- Introduced DFF parameters for bug fix 2908859
183 p_attribute1 IN VARCHAR2,
184 p_attribute2 IN VARCHAR2,
185 p_attribute3 IN VARCHAR2,
186 p_attribute4 IN VARCHAR2,
187 p_attribute5 IN VARCHAR2,
188 p_attribute6 IN VARCHAR2,
189 p_attribute7 IN VARCHAR2,
190 p_attribute8 IN VARCHAR2,
191 p_attribute9 IN VARCHAR2,
192 p_attribute10 IN VARCHAR2,
193 p_return_status OUT NOCOPY VARCHAR2);
194
195
196 PROCEDURE assignment_hierarchy(p_proc_executed OUT NOCOPY VARCHAR2,
197 p_person_id IN NUMBER,
198 p_sub_line_id IN NUMBER,
199 p_assignment_id IN NUMBER,
200 p_element_type_id IN NUMBER,
201 p_payroll_start_date IN DATE,
202 p_daily_rate IN NUMBER,
203 p_org_def_account IN VARCHAR2,
204 p_effective_date IN DATE,
205 p_mode IN VARCHAR2 := 'I',
206 p_business_group_id IN NUMBER,
207 p_set_of_books_id IN NUMBER,
208 p_attribute_category IN VARCHAR2, -- Introduced DFF parameters for bug fix 2908859
209 p_attribute1 IN VARCHAR2,
210 p_attribute2 IN VARCHAR2,
211 p_attribute3 IN VARCHAR2,
212 p_attribute4 IN VARCHAR2,
213 p_attribute5 IN VARCHAR2,
214 p_attribute6 IN VARCHAR2,
215 p_attribute7 IN VARCHAR2,
216 p_attribute8 IN VARCHAR2,
217 p_attribute9 IN VARCHAR2,
218 p_attribute10 IN VARCHAR2,
219 p_return_status OUT NOCOPY VARCHAR2);
220
221
222 PROCEDURE org_labor_schedule_hierarchy(
223 p_proc_executed OUT NOCOPY VARCHAR2,
224 p_person_id IN NUMBER,
225 p_sub_line_id IN NUMBER,
226 p_assignment_id IN NUMBER,
227 p_element_type_id IN NUMBER,
228 p_payroll_start_date IN DATE,
229 p_daily_rate IN NUMBER,
230 p_org_def_account IN VARCHAR2,
231 p_effective_date IN DATE,
232 p_mode IN VARCHAR2 := 'I',
233 p_business_group_id IN NUMBER,
234 p_set_of_books_id IN NUMBER,
235 p_attribute_category IN VARCHAR2, -- Introduced DFF parameters for bug fix 2908859
236 p_attribute1 IN VARCHAR2,
237 p_attribute2 IN VARCHAR2,
238 p_attribute3 IN VARCHAR2,
239 p_attribute4 IN VARCHAR2,
240 p_attribute5 IN VARCHAR2,
241 p_attribute6 IN VARCHAR2,
242 p_attribute7 IN VARCHAR2,
243 p_attribute8 IN VARCHAR2,
244 p_attribute9 IN VARCHAR2,
245 p_attribute10 IN VARCHAR2,
246 p_return_status OUT NOCOPY VARCHAR2);
247
248
249 PROCEDURE default_account(
250 p_proc_executed OUT NOCOPY VARCHAR2,
251 p_person_id IN NUMBER,
252 p_sub_line_id IN NUMBER,
253 p_assignment_id IN NUMBER,
254 p_payroll_start_date IN DATE,
255 p_daily_rate IN NUMBER,
256 p_default_reason_code IN VARCHAR2,
257 p_effective_date IN DATE,
258 p_mode IN VARCHAR2 := 'I',
259 p_business_group_id IN NUMBER,
260 p_set_of_books_id IN NUMBER,
261 p_attribute_category IN VARCHAR2, -- Introduced DFF parameters for bug fix 2908859
262 p_attribute1 IN VARCHAR2,
263 p_attribute2 IN VARCHAR2,
264 p_attribute3 IN VARCHAR2,
265 p_attribute4 IN VARCHAR2,
266 p_attribute5 IN VARCHAR2,
267 p_attribute6 IN VARCHAR2,
268 p_attribute7 IN VARCHAR2,
269 p_attribute8 IN VARCHAR2,
270 p_attribute9 IN VARCHAR2,
271 p_attribute10 IN VARCHAR2,
272 p_return_status OUT NOCOPY VARCHAR2);
273
274
275 PROCEDURE suspense_account (
276 p_proc_executed OUT NOCOPY VARCHAR2,
277 p_person_id IN NUMBER,
278 p_sub_line_id IN NUMBER,
279 p_assignment_id IN NUMBER,
280 p_payroll_start_date IN DATE,
281 p_daily_rate IN NUMBER,
282 p_suspense_reason_code IN VARCHAR2,
283 p_schedule_line_id IN NUMBER,
284 p_default_org_account_id IN NUMBER,
285 p_element_account_id IN NUMBER,
286 p_org_schedule_id IN NUMBER,
287 p_effective_date IN DATE,
288 p_mode IN VARCHAR2 := 'I',
289 p_business_group_id IN NUMBER,
290 p_set_of_books_id IN NUMBER,
291 p_dist_line_id IN NUMBER,
292 p_return_status OUT NOCOPY VARCHAR2);
293
294 PROCEDURE Get_Poeta_Description
295 (p_project_id IN NUMBER,
296 p_task_id IN NUMBER,
297 p_award_id IN NUMBER,
298 p_organization_id IN NUMBER,
299 p_description OUT NOCOPY VARCHAR2,
300 p_return_status OUT NOCOPY VARCHAR2) ;
301
302 PROCEDURE insert_into_distribution_lines(
303 L_PAYROLL_SUB_LINE_ID IN NUMBER,
304 L_DISTRIBUTION_DATE IN DATE,
305 L_EFFECTIVE_DATE IN DATE,
306 L_DISTRIBUTION_AMOUNT IN NUMBER,
307 L_STATUS_CODE IN VARCHAR2,
308 L_SUSPENSE_REASON_CODE IN VARCHAR2,
309 L_DEFAULT_REASON_CODE IN VARCHAR2,
310 L_SCHEDULE_LINE_ID IN NUMBER,
311 L_DEFAULT_ORG_ACCOUNT_ID IN NUMBER,
312 L_SUSPENSE_ORG_ACCOUNT_ID IN NUMBER,
313 L_ELEMENT_ACCOUNT_ID IN NUMBER,
314 L_ORG_SCHEDULE_ID IN NUMBER,
315 L_GL_PROJECT_FLAG IN VARCHAR2,
316 L_REVERSAL_ENTRY_FLAG IN VARCHAR2,
317 P_GL_CODE_COMBINATION_ID IN NUMBER := FND_API.G_MISS_NUM,
318 P_PROJECT_ID IN NUMBER := FND_API.G_MISS_NUM,
319 P_TASK_ID IN NUMBER := FND_API.G_MISS_NUM,
323 P_EFFECTIVE_START_DATE IN DATE := FND_API.G_MISS_DATE,
320 P_AWARD_ID IN NUMBER := FND_API.G_MISS_NUM,
321 P_EXPENDITURE_ORGANIZATION_ID IN NUMBER := FND_API.G_MISS_NUM,
322 P_EXPENDITURE_TYPE IN VARCHAR2 := FND_API.G_MISS_CHAR,
324 P_EFFECTIVE_END_DATE IN DATE := FND_API.G_MISS_DATE,
325 P_MODE IN VARCHAR2 := 'I',
326 p_business_group_id IN NUMBER,
327 p_set_of_books_id IN NUMBER,
328 p_attribute_category IN VARCHAR2, -- Introduced DFF parameters for bug fix 2908859
329 p_attribute1 IN VARCHAR2,
330 p_attribute2 IN VARCHAR2,
331 p_attribute3 IN VARCHAR2,
332 p_attribute4 IN VARCHAR2,
333 p_attribute5 IN VARCHAR2,
334 p_attribute6 IN VARCHAR2,
335 p_attribute7 IN VARCHAR2,
336 p_attribute8 IN VARCHAR2,
337 p_attribute9 IN VARCHAR2,
338 p_attribute10 IN VARCHAR2,
339 p_return_status OUT NOCOPY VARCHAR2,
340 P_CAP_EXCESS_GLCCID IN NUMBER DEFAULT NULL,
341 P_CAP_EXCESS_PROJECT_ID IN NUMBER DEFAULT NULL,
342 P_CAP_EXCESS_TASK_ID IN NUMBER DEFAULT NULL,
343 P_CAP_EXCESS_AWARD_ID IN NUMBER DEFAULT NULL,
344 P_CAP_EXCESS_EXP_ORG_ID IN NUMBER DEFAULT NULL,
345 P_CAP_EXCESS_EXP_TYPE IN VARCHAR2 DEFAULT NULL);
346
347 /*
348
349 PROCEDURE autopop(p_acct_type IN VARCHAR2,
350 p_person_id IN NUMBER,
351 p_assignment_id IN NUMBER,
352 p_element_type_id IN NUMBER,
353 p_project_id IN NUMBER,
354 p_expenditure_organization_id IN NUMBER,
355 p_task_id IN NUMBER,
356 p_award_id IN NUMBER,
357 p_expenditure_type IN VARCHAR2,
358 p_gl_code_combination_id IN NUMBER,
359 p_payroll_start_date IN DATE,
360 p_effective_date IN DATE,
361 p_dist_amount IN NUMBER,
362 p_schedule_line_id IN NUMBER,
363 p_org_schedule_id IN NUMBER,
364 p_sub_line_id IN NUMBER,
365 p_effective_start_date IN DATE := FND_API.G_MISS_DATE,
366 p_effective_end_date IN DATE := FND_API.G_MISS_DATE,
367 p_mode IN VARCHAR2 := 'I',
368 p_business_group_id IN NUMBER,
369 p_set_of_books_id IN NUMBER,
370 p_return_status OUT NOCOPY VARCHAR2) ;
371
372 PROCEDURE insert_into_autopop_results(p_distribution_line_id IN NUMBER,
373 p_new_expenditure_type IN VARCHAR2,
374 p_new_gl_code_combination_id IN NUMBER,
375 p_return_status OUT NOCOPY VARCHAR2);
376
377
378
379 */
380
381 PROCEDURE update_dist_odls_autopop(p_payroll_control_id IN NUMBER,
382 p_business_group_id IN NUMBER,
383 p_Set_of_books_id IN NUMBER,
384 p_start_asg_id in integer,
385 p_end_asg_id in integer,
386 p_return_status OUT NOCOPY VARCHAR2);
387
388 PROCEDURE update_dist_schedule_autopop(p_payroll_control_id IN NUMBER,
389 p_business_group_id IN NUMBER,
390 p_Set_of_books_id IN NUMBER,
391 p_start_asg_id in integer,
392 p_end_asg_id in integer,
393 p_return_status OUT NOCOPY VARCHAR2);
394
395 Procedure apply_salary_cap(p_payroll_control_id in integer,
396 p_currency_code in varchar2,
397 p_business_group_id IN NUMBER,
398 p_Set_of_books_id IN NUMBER,
399 p_start_asg_id in integer,
400 p_end_asg_id in integer);
401
402 procedure cdl_archive(p_payroll_action_id in number,
403 p_chunk_number in number);
404
405
406 procedure Range_code (pactid IN NUMBER, sqlstr out nocopy varchar2);
407
408 procedure cdl_init(p_payroll_action_id in number);
409
410 procedure excess_account_autopop(p_payroll_control_id in number,
411 p_business_group_id in number,
412 p_set_of_books_id in number,
413 p_start_asg_id in integer,
414 p_end_asg_id in integer,
415 p_return_status out nocopy varchar2);
416
417 function get_parameter(name in varchar2,
418 parameter_list in varchar2) return varchar2;
419 pragma restrict_references (get_parameter, wnds, wnps);
420
421 TYPE t_integer IS TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER;
422 t_asg_array t_integer;
423
424 procedure asg_action_code (p_pactid IN NUMBER,
425 stasg IN NUMBER,
426 endasg IN NUMBER,
427 p_chunk_num IN NUMBER);
428
429 procedure deinit_code(pactid in number);
430 procedure generic_account_autopop(p_payroll_control_id in number,
431 p_business_group_id in number,
432 p_set_of_books_id in number,
433 p_start_asg_id in integer,
434 p_end_asg_id in integer,
435 p_schedule_type in varchar2);
436 function get_retro_parent_element_id(p_cost_id integer) return integer;
437 END PSP_LABOR_DIST;