[Home] [Help]
PACKAGE BODY: APPS.PSP_ADJ_DRIVER
Source
1 PACKAGE body psp_adj_driver AS
2 /* $Header: PSPLDTRB.pls 120.10.12010000.5 2008/11/26 06:58:30 amakrish ship $ */
3
4 g_error_api_path VARCHAR2(2000);
5 g_run_id NUMBER(9) := 0;
6 --**************************************************
7 g_constraint_violation CONSTANT VARCHAR2(1) := 'V';
8 --**************************************************
9
10 g_precision NUMBER; -- Introduced this variable to store precision for bug fix 2916848
11 -- Introduced following variable to store extended precision for bug fix 2916848, currently this value
12 -- isn't used in this procedure. This variable is introduced as the common routine to fetch currency
13 -- precision returns this value as well.
14 g_ext_precision NUMBER;
15
16 g_dff_grouping_option CHAR(1); -- Introduced for bug fix 2908859
17
18 ----------P R O C E D U R E: LOAD_TABLE -------------------------------
19 --
20 --
21 -- Purpose: This procedure is called by the QUERY FIND screen of the
22 -- distribution transfers form. The purpose is to select
23 -- distribution lines from the following 3 tables that match
24 -- the query find selection criteria.
25 --
26 -- 1. PSP_DISTRIUTION_LINES_HISTORY (lines taht have not been adjusted)
27 -- 2. PSP_PRE_GEN_DIST_LINES_HISTORY (pre generated lines that have
28 -- not been adjusted)
29 -- 3. PSP_ADJUSTMENT_LINES_HISTORY (lines that have been adjusted)
30 --
31 -- For each line found, a call is made to the procedure
32 -- insert_update_sumlines to summarize the line by unique
33 -- GL/POETA information for display to the user.
34 --
35 ----------------------------------------------------------------------------------
36
37 PROCEDURE load_table(errbuf OUT NOCOPY VARCHAR2,
38 retcode OUT NOCOPY VARCHAR2,
39 p_person_id IN NUMBER,
40 p_assignment_id IN NUMBER,
41 --p_element_type_id IN NUMBER, commented for DA-ENH
42 p_begin_date IN DATE,
43 p_end_date IN DATE,
44 p_adjust_by IN VARCHAR2, --- added for DA-ENH
45 p_currency_code IN VARCHAR2, -- Introduced for bug fix 2916848
46 p_run_id IN NUMBER,
47 p_business_group_id IN Number,
48 p_set_of_books_id IN Number) IS
49
50 /* This cursor selects from three different tables.
51
52 For the table psp_distribution_lines_history, if
53 suspense account information is present, then a join
54 to the suspense table is used to get the GL or POETA
55 account information. Otherwise, the information
56 is found from element account, schedule line,
57 default labor schedule, or default org account tables.
58
59 For the tables psp_pre_gen_dist_lines_history
60 and psp_adjustment_lines_history, account information
61 is taken from suspense account if present, otherwise
62 it is taken from the line. */
63
64 --Introduced the EXISTS clause of adjust='Y' for Bug 2860013
65 CURSOR lines_c1(p_person_id IN NUMBER,
66 p_assignment_id IN NUMBER,
67 p_begin_date IN DATE,
68 p_end_date IN DATE) IS
69 SELECT ppl.element_type_id, --- added this and line below for DA-ENH
70 pegl.element_group_id, -- Modified to inline query column for bug fix 3658235
71 psl.gl_code_combination_id,
72 psl.project_id,
73 psl.expenditure_organization_id,
74 psl.expenditure_type,
75 psl.task_id,
76 psl.award_id,
77 pdl.distribution_date,
78 ROUND(pdl.distribution_amount, g_precision), -- Introduced ROUND() for bug fix 2916848
79 --- pdl.gl_project_flag, commented for DA-ENH
80 pdl.distribution_line_id distribution_line_id,
81 ppl.dr_cr_flag,
82 'D' tab_flag ,
83 --Added the following 3 new columns : For bug fix 2252881
84 pdl.effective_date,
85 psl.time_period_id,
86 psl.payroll_control_id,
87 DECODE(g_dff_grouping_option, 'Y', psl.attribute_category, NULL) attribute_category, -- Introduced DFF columns for bug fix 2908859
88 DECODE(g_dff_grouping_option, 'Y', psl.attribute1, NULL) attribute1,
89 DECODE(g_dff_grouping_option, 'Y', psl.attribute2, NULL) attribute2,
90 DECODE(g_dff_grouping_option, 'Y', psl.attribute3, NULL) attribute3,
91 DECODE(g_dff_grouping_option, 'Y', psl.attribute4, NULL) attribute4,
92 DECODE(g_dff_grouping_option, 'Y', psl.attribute5, NULL) attribute5,
93 DECODE(g_dff_grouping_option, 'Y', psl.attribute6, NULL) attribute6,
94 DECODE(g_dff_grouping_option, 'Y', psl.attribute7, NULL) attribute7,
95 DECODE(g_dff_grouping_option, 'Y', psl.attribute8, NULL) attribute8,
96 DECODE(g_dff_grouping_option, 'Y', psl.attribute9, NULL) attribute9,
97 DECODE(g_dff_grouping_option, 'Y', psl.attribute10, NULL) attribute10
98 FROM
99 psp_distribution_lines_history pdl,
100 psp_payroll_lines ppl,
101 psp_payroll_sub_lines ppsl,
102 psp_summary_lines psl,
103 --psp_group_element_list pgel, --- added for DA-ENH Modified to inline query for bug 3658235
104 (SELECT peg.element_group_id, pgel.element_type_id
105 FROM psp_element_groups peg, psp_group_element_list pgel
106 WHERE business_group_id = p_business_group_id
107 AND set_of_books_id = p_set_of_books_id
108 AND peg.element_group_id (+) = pgel.element_group_id
109 and (peg.start_date_active between p_begin_date and p_end_date -- Bug 7582233
110 OR peg.end_date_active between p_begin_date and p_end_date)) pegl,-- Introduced for bug fix 3098050
111 psp_payroll_controls ppc -- Introduced for bug fix 2916848
112 WHERE
113 psl.person_id = p_person_id
114 AND psl.summary_line_id = pdl.summary_line_id
115 AND psl.assignment_id = p_assignment_id
116 AND psl.business_group_id = p_business_group_id
117 AND psl.set_of_books_id = p_set_of_books_id
118 AND pdl.distribution_date between p_begin_date and p_end_date
119 AND pegl.element_type_id(+) = ppl.element_type_id --- added for DA-ENH
120 -- Introduced BG/SOB check on psp_element_groups for bug fix 3098050
121 --AND peg.element_group_id(+) = pgel.element_group_id
122 -- AND NVL(peg.business_group_id, p_business_group_id) = p_business_group_id -- Introduced NVL for bug fix 3145038, Commented for 3658235
123 -- AND NVL(peg.set_of_books_id, p_set_of_books_id) = p_set_of_books_id -- Introduced NVL for bug fix 3145038, Commented for 3658235
124 AND pdl.payroll_sub_line_id = ppsl.payroll_sub_line_id
125 AND ppsl.payroll_line_id = ppl.payroll_line_id
126 -- Introduced the following for bug fix 2916848
127 AND ppc.payroll_control_id = ppl.payroll_control_id
128 AND ppc.currency_code = p_currency_code
129 -- End of bug fix 2916848
130 AND pdl.reversal_entry_flag is NULL
131 AND pdl.adjustment_batch_name is null
132 AND EXISTS (SELECT 1
133 FROM psp_element_types pet
134 WHERE pet.element_type_id = ppl.element_type_id
135 AND pet.adjust = 'Y'
136 -- Introduced BG/SOB check fopr bug fix 3098050
137 AND pet.business_group_id = p_business_group_id
138 AND pet.set_of_books_id = p_set_of_books_id)
139 for update of reversal_entry_flag nowait ;
140
141 --Introduced the EXISTS clause of adjust='Y' for Bug 2860013
142 CURSOR lines_c2(p_person_id IN NUMBER,
143 p_assignment_id IN NUMBER,
144 p_begin_date IN DATE,
145 p_end_date IN DATE) IS
146 SELECT ppg.element_type_id, -- added this line and line below for DA-ENH
147 pegl.element_group_id, -- Modified to inline query column for bug 3658235
148 decode(ppg.suspense_org_account_id, NULL, ppg.gl_code_combination_id,
149 nvl(ppg.suspense_auto_glccid, pos.gl_code_combination_id)) gl_code_combination_id,
150 decode(ppg.suspense_org_account_id, NULL, ppg.project_id,
151 pos.project_id) project_id,
152 decode(ppg.suspense_org_account_id, NULL, ppg.expenditure_organization_id,
153 pos.expenditure_organization_id) expenditure_organization_id,
154 decode(ppg.suspense_org_account_id, NULL, ppg.expenditure_type,
155 nvl(ppg.suspense_auto_exp_type, pos.expenditure_type)) expenditure_type,
156 decode(ppg.suspense_org_account_id, NULL, ppg.task_id,
157 pos.task_id) task_id,
158 decode(ppg.suspense_org_account_id, NULL, ppg.award_id,
159 pos.award_id) award_id,
160 ppg.distribution_date,
161 ROUND(ppg.distribution_amount, g_precision), -- Introduced ROUND() for bug fix 2916848
162 ppg.pre_gen_dist_line_id distribution_line_id,
163 ppg.dr_cr_flag,
164 'P' tab_flag ,
165 --Added the following 3 new columns : For bug fix 2252881
166 ppg.effective_date,
167 ppg.time_period_id,
168 ppg.payroll_control_id ,
169 DECODE(g_dff_grouping_option, 'Y', DECODE(ppg.suspense_org_account_id, NULL, ppg.attribute_category, pos.attribute_category), NULL) attribute_category, -- Introduced DFF columns for bug fix 2908859
170 DECODE(g_dff_grouping_option, 'Y', DECODE(ppg.suspense_org_account_id, NULL, ppg.attribute1, pos.attribute1), NULL) attribute1,
171 DECODE(g_dff_grouping_option, 'Y', DECODE(ppg.suspense_org_account_id, NULL, ppg.attribute2, pos.attribute2), NULL) attribute2,
172 DECODE(g_dff_grouping_option, 'Y', DECODE(ppg.suspense_org_account_id, NULL, ppg.attribute3, pos.attribute3), NULL) attribute3,
173 DECODE(g_dff_grouping_option, 'Y', DECODE(ppg.suspense_org_account_id, NULL, ppg.attribute4, pos.attribute4), NULL) attribute4,
174 DECODE(g_dff_grouping_option, 'Y', DECODE(ppg.suspense_org_account_id, NULL, ppg.attribute5, pos.attribute5), NULL) attribute5,
175 DECODE(g_dff_grouping_option, 'Y', DECODE(ppg.suspense_org_account_id, NULL, ppg.attribute6, pos.attribute6), NULL) attribute6,
176 DECODE(g_dff_grouping_option, 'Y', DECODE(ppg.suspense_org_account_id, NULL, ppg.attribute7, pos.attribute7), NULL) attribute7,
177 DECODE(g_dff_grouping_option, 'Y', DECODE(ppg.suspense_org_account_id, NULL, ppg.attribute8, pos.attribute8), NULL) attribute8,
178 DECODE(g_dff_grouping_option, 'Y', DECODE(ppg.suspense_org_account_id, NULL, ppg.attribute9, pos.attribute9), NULL) attribute9,
179 DECODE(g_dff_grouping_option, 'Y', DECODE(ppg.suspense_org_account_id, NULL, ppg.attribute10, pos.attribute10), NULL) attribute10
180 FROM
181 psp_pre_gen_dist_lines_history ppg,
182 psp_organization_accounts pos ,
183 --psp_group_element_list pgel, --- added for DA-ENH Modified to inline query for bug fix 3658235
184 (SELECT peg.element_group_id, pgel.element_type_id
185 FROM psp_element_groups peg, psp_group_element_list pgel
186 WHERE business_group_id = p_business_group_id
187 AND set_of_books_id = p_set_of_books_id
188 AND peg.element_group_id (+) = pgel.element_group_id
189 and (peg.start_date_active between p_begin_date and p_end_date -- Bug 7582233
190 OR peg.end_date_active between p_begin_date and p_end_date)) pegl, -- Introduced for bug fix 3098050
191 psp_payroll_controls ppc -- Introduced for bug fix 2916848
192 WHERE
193 ppg.assignment_id = p_assignment_id
194 AND ppg.distribution_date between p_begin_date and p_end_date
195 AND ppg.business_group_id = p_business_group_id
196 AND ppg.set_of_books_id = p_set_of_books_id
197 -- Introduced the following for bug fix 2916848
198 AND ppc.payroll_control_id = ppg.payroll_control_id
199 AND ppc.currency_code = p_currency_code
200 -- End of bug fix 2916848
201 AND pegl.element_type_id(+) = ppg.element_type_id -- added for DA-ENH
202 -- Introduced BG/SOB check on psp_element_groups for bug fix 3098050
203 --AND peg.element_group_id(+) = pgel.element_group_id
204 -- AND NVL(peg.business_group_id, p_business_group_id) = p_business_group_id -- Introduced NVL for bug fix 3145038, Commented for bug 3658235
205 -- AND NVL(peg.set_of_books_id, p_set_of_books_id) = p_set_of_books_id -- Introduced NVL for bug fix 3145038, Commented for bug 3658235
206 AND ppg.status_code = 'A'
207 AND ppg.reversal_entry_flag is NULL
208 AND ppg.suspense_org_account_id = pos.organization_account_id(+)
209 AND ppg.adjustment_batch_name is null
210 AND EXISTS (SELECT 1
211 FROM psp_element_types pet
212 WHERE pet.element_type_id = ppg.element_type_id
213 AND pet.adjust = 'Y'
214 -- Introduced BG/SOB check fopr bug fix 3098050
215 AND pet.business_group_id = p_business_group_id
216 AND pet.set_of_books_id = p_set_of_books_id)
217 for update of reversal_entry_flag nowait;
218
219 --Introduced the EXISTS clause of adjust='Y' for Bug 2860013
220 CURSOR lines_c3(p_person_id IN NUMBER,
221 p_assignment_id IN NUMBER,
222 p_begin_date IN DATE,
223 p_end_date IN DATE) IS
224 SELECT pal.element_type_id, --- for DA-ENH
225 pegl.element_group_id, -- added for DA-ENH Modified to inline query column for bug 3658235
226 pal.gl_code_combination_id,
227 pal.project_id,
228 pal.expenditure_organization_id,
229 pal.expenditure_type,
230 pal.task_id,
231 pal.award_id,
232 pal.distribution_date,
233 ROUND(pal.distribution_amount, g_precision), -- Introduced for bug fix 2916848
234 pal.adjustment_line_id distribution_line_id,
235 dr_cr_flag, /* changed from 'D', Bug 1976999 */
236 'A' tab_flag ,
237 pal.effective_date,
238 pal.time_period_id,
239 pal.payroll_control_id ,
240 DECODE(g_dff_grouping_option, 'Y', pal.attribute_category, NULL) attribute_category, -- Introduced DFF columns for bug fix 2908859
241 DECODE(g_dff_grouping_option, 'Y', pal.attribute1, NULL) attribute1,
242 DECODE(g_dff_grouping_option, 'Y', pal.attribute2, NULL) attribute2,
243 DECODE(g_dff_grouping_option, 'Y', pal.attribute3, NULL) attribute3,
244 DECODE(g_dff_grouping_option, 'Y', pal.attribute4, NULL) attribute4,
245 DECODE(g_dff_grouping_option, 'Y', pal.attribute5, NULL) attribute5,
246 DECODE(g_dff_grouping_option, 'Y', pal.attribute6, NULL) attribute6,
247 DECODE(g_dff_grouping_option, 'Y', pal.attribute7, NULL) attribute7,
248 DECODE(g_dff_grouping_option, 'Y', pal.attribute8, NULL) attribute8,
249 DECODE(g_dff_grouping_option, 'Y', pal.attribute9, NULL) attribute9,
250 DECODE(g_dff_grouping_option, 'Y', pal.attribute10, NULL) attribute10
251 FROM
252 psp_adjustment_lines_history pal,
253 --psp_group_element_list pgel, --- added table for DA-ENH Modified to inline query for bug fix 3658235
254 (SELECT peg.element_group_id, pgel.element_type_id
255 FROM psp_element_groups peg, psp_group_element_list pgel
256 WHERE business_group_id = p_business_group_id
257 AND set_of_books_id = p_set_of_books_id
258 AND peg.element_group_id (+) = pgel.element_group_id
259 and (peg.start_date_active between p_begin_date and p_end_date -- Bug 7582233
260 OR peg.end_date_active between p_begin_date and p_end_date)) pegl, -- Introduced for bug fix 3098050
261 psp_payroll_controls ppc -- Introduced for bug fix 2916848
262 WHERE
263 pal.assignment_id = p_assignment_id
264 AND pal.distribution_date between p_begin_date and p_end_date
265 AND pal.business_group_id = p_business_group_id
266 AND pal.set_of_books_id = p_set_of_books_id
267 -- Introduced the following for bug fix 2916848
268 AND ppc.payroll_control_id = pal.payroll_control_id
269 AND ppc.currency_code = p_currency_code
270 -- End of bug fix 2916848
271 AND pegl.element_type_id(+) = pal.element_type_id
272 -- Introduced BG/SOB check on psp_element_groups for bug fix 3098050
273 --AND peg.element_group_id(+) = pgel.element_group_id
274 -- AND NVL(peg.business_group_id, p_business_group_id) = p_business_group_id -- Introduced NVL for bug fix 3145038, Commented for bug 3658235
275 -- AND NVL(peg.set_of_books_id, p_set_of_books_id) = p_set_of_books_id -- Introduced NVL for bug fix 3145038, Commented for bug 3658235
276 AND pal.status_code = 'A'
277 AND NVL(pal.original_line_flag,'N') ='N'
278 AND pal.reversal_entry_flag is NULL
279 AND pal.adjustment_batch_name is null
280 AND EXISTS (SELECT 1
281 FROM psp_element_types pet
282 WHERE pet.element_type_id = pal.element_type_id
283 AND pet.adjust = 'Y'
284 -- Introduced BG/SOB check fopr bug fix 3098050
285 AND pet.business_group_id = p_business_group_id
286 AND pet.set_of_books_id = p_set_of_books_id)
287 for update of reversal_entry_flag nowait ;
288
289 l_element_type_id number := 0; -- added for DA-ENH
290 l_loop_count INTEGER :=0;
291 l_group_id INTEGER :=0;
292 l_gl_code_combination_id NUMBER(15);
293 l_project_id NUMBER(15);
294 l_expenditure_organization_id NUMBER(15);
295 l_expenditure_type VARCHAR2(30);
296 l_task_id NUMBER(15);
297 l_award_id NUMBER(15);
298 l_distribution_date DATE;
299 -- l_distribution_amount NUMBER(20, 2); --Bug 2698256. Commented as part bug fix 2916848
300 l_gl_project_flag VARCHAR2(1);
301 l_distribution_line_id NUMBER(10);
302 l_dr_cr_flag VARCHAR2(1);
303 l_tab_flag VARCHAR2(1);
304 l_return_status VARCHAR2(1);
305 l_msg_count NUMBER;
306 l_msg_data VARCHAR2(2000);
307 pop_gl_ccid NUMBER(15);
308 pop_exp_type VARCHAR2(30);
309
310 no_records_found EXCEPTION;
311
312 IN_USE_EXCEPTION EXCEPTION;
313 PRAGMA EXCEPTION_INIT (IN_USE_EXCEPTION, -54); /* Bug 1609502 */
314
315 TYPE t_num_15_type IS TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER;
316 TYPE t_varchar_30_type IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
317 TYPE t_varchar_150_type IS TABLE OF VARCHAR2(150) INDEX BY BINARY_INTEGER; -- Introduced for bug fix 2908859
318 TYPE t_varchar_1_type IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
319 TYPE t_date_type IS TABLE OF DATE INDEX BY BINARY_INTEGER;
320 -- Changed the datatype defn for the following type for bug fix 2916848 from (15, 2) to 30
321 TYPE t_number_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
322
323 type temp_orig_line_rec is record
324 ( array_element_type_id t_num_15_type,
325 array_element_group_id t_num_15_type,
326 array_glccid t_num_15_type,
327 array_project_id t_num_15_type,
328 array_exp_org_id t_num_15_type,
329 array_exp_type t_varchar_30_type,
330 array_task_id t_num_15_type,
331 array_award_id t_num_15_type,
332 array_distribution_date t_date_type,
333 array_distribution_amount t_number_type, -- Corrected column type defn for bug fix 2916848
334 array_distribution_line_id t_num_15_type,
335 array_dr_cr_flag t_varchar_1_type,
336 array_tab_flag t_varchar_1_type,
337 array_effective_date t_date_type,
338 array_time_period_id t_num_15_type,
339 array_payroll_control_id t_num_15_type,
340 array_attribute_category t_varchar_30_type,
341 array_attribute1 t_varchar_150_type,
342 array_attribute2 t_varchar_150_type,
343 array_attribute3 t_varchar_150_type,
344 array_attribute4 t_varchar_150_type,
345 array_attribute5 t_varchar_150_type,
346 array_attribute6 t_varchar_150_type,
347 array_attribute7 t_varchar_150_type,
348 array_attribute8 t_varchar_150_type,
349 array_attribute9 t_varchar_150_type,
350 array_attribute10 t_varchar_150_type);
351
352 orig_line_rec temp_orig_line_rec;
353
354 cursor temp_orig_sumline_E is
355 SELECT ELEMENT_TYPE_ID,
356 GL_CODE_COMBINATION_ID,
357 PROJECT_ID,
358 EXPENDITURE_ORGANIZATION_ID,
359 EXPENDITURE_TYPE,
360 TASK_ID,
361 AWARD_ID,
362 -- DR_CR_FLAG, Commented for Bug 3625667
363 DECODE(sign(SUM(DECODE(dr_cr_flag, 'C', - distribution_amount, distribution_amount))),-1,'C','D') DR_CR_FLAG,
364 SUM(DECODE(dr_cr_flag, 'C', - distribution_amount, distribution_amount)),
365 RUN_ID,
366 SET_OF_BOOKS_ID,
367 BUSINESS_GROUP_ID,
368 DECODE(g_dff_grouping_option, 'Y', attribute_category, NULL) attribute_category,
369 DECODE(g_dff_grouping_option, 'Y', attribute1, NULL) attribute1,
370 DECODE(g_dff_grouping_option, 'Y', attribute2, NULL) attribute2,
371 DECODE(g_dff_grouping_option, 'Y', attribute3, NULL) attribute3,
372 DECODE(g_dff_grouping_option, 'Y', attribute4, NULL) attribute4,
373 DECODE(g_dff_grouping_option, 'Y', attribute5, NULL) attribute5,
374 DECODE(g_dff_grouping_option, 'Y', attribute6, NULL) attribute6,
375 DECODE(g_dff_grouping_option, 'Y', attribute7, NULL) attribute7,
376 DECODE(g_dff_grouping_option, 'Y', attribute8, NULL) attribute8,
377 DECODE(g_dff_grouping_option, 'Y', attribute9, NULL) attribute9,
378 DECODE(g_dff_grouping_option, 'Y', attribute10, NULL) attribute10
379 from PSP_TEMP_ORIG_LINES
380 where RUN_ID = g_run_id
381 group by RUN_ID,
382 SET_OF_BOOKS_ID,
383 BUSINESS_GROUP_ID,
384 ELEMENT_TYPE_ID,
385 GL_CODE_COMBINATION_ID,
386 PROJECT_ID,
387 EXPENDITURE_ORGANIZATION_ID,
388 EXPENDITURE_TYPE,
389 TASK_ID,
390 AWARD_ID,
391 -- DR_CR_FLAG, Commented for Bug 3625667
392 DECODE(g_dff_grouping_option, 'Y', attribute_category, NULL),
393 DECODE(g_dff_grouping_option, 'Y', attribute1, NULL),
394 DECODE(g_dff_grouping_option, 'Y', attribute2, NULL),
395 DECODE(g_dff_grouping_option, 'Y', attribute3, NULL),
396 DECODE(g_dff_grouping_option, 'Y', attribute4, NULL),
397 DECODE(g_dff_grouping_option, 'Y', attribute5, NULL),
398 DECODE(g_dff_grouping_option, 'Y', attribute6, NULL),
399 DECODE(g_dff_grouping_option, 'Y', attribute7, NULL),
400 DECODE(g_dff_grouping_option, 'Y', attribute8, NULL),
401 DECODE(g_dff_grouping_option, 'Y', attribute9, NULL),
402 DECODE(g_dff_grouping_option, 'Y', attribute10, NULL);
403
404 cursor temp_orig_sumline_G is
405 SELECT ELEMENT_GROUP_ID,
406 GL_CODE_COMBINATION_ID,
407 PROJECT_ID,
408 EXPENDITURE_ORGANIZATION_ID,
409 EXPENDITURE_TYPE,
410 TASK_ID,
411 AWARD_ID,
412 -- DR_CR_FLAG, Commented for Bug 3625667
413 DECODE(sign(SUM(DECODE(dr_cr_flag, 'C', - distribution_amount, distribution_amount))),-1,'C','D') DR_CR_FLAG,
414 SUM(DECODE(dr_cr_flag, 'C', - distribution_amount, distribution_amount)),
415 RUN_ID,
416 SET_OF_BOOKS_ID,
417 BUSINESS_GROUP_ID,
418 DECODE(g_dff_grouping_option, 'Y', attribute_category, NULL) attribute_category,
419 DECODE(g_dff_grouping_option, 'Y', attribute1, NULL) attribute1,
420 DECODE(g_dff_grouping_option, 'Y', attribute2, NULL) attribute2,
421 DECODE(g_dff_grouping_option, 'Y', attribute3, NULL) attribute3,
422 DECODE(g_dff_grouping_option, 'Y', attribute4, NULL) attribute4,
423 DECODE(g_dff_grouping_option, 'Y', attribute5, NULL) attribute5,
424 DECODE(g_dff_grouping_option, 'Y', attribute6, NULL) attribute6,
425 DECODE(g_dff_grouping_option, 'Y', attribute7, NULL) attribute7,
426 DECODE(g_dff_grouping_option, 'Y', attribute8, NULL) attribute8,
427 DECODE(g_dff_grouping_option, 'Y', attribute9, NULL) attribute9,
428 DECODE(g_dff_grouping_option, 'Y', attribute10, NULL) attribute10
429 from PSP_TEMP_ORIG_LINES
430 where RUN_ID = g_run_id
431 and element_group_id IS NOT NULL
432 group by RUN_ID,
433 SET_OF_BOOKS_ID,
434 BUSINESS_GROUP_ID,
435 ELEMENT_GROUP_ID,
436 GL_CODE_COMBINATION_ID,
437 PROJECT_ID,
438 EXPENDITURE_ORGANIZATION_ID,
439 EXPENDITURE_TYPE,
440 TASK_ID,
441 AWARD_ID,
442 -- DR_CR_FLAG, Commented for bug 3625667
443 DECODE(g_dff_grouping_option, 'Y', attribute_category, NULL),
444 DECODE(g_dff_grouping_option, 'Y', attribute1, NULL),
445 DECODE(g_dff_grouping_option, 'Y', attribute2, NULL),
446 DECODE(g_dff_grouping_option, 'Y', attribute3, NULL),
447 DECODE(g_dff_grouping_option, 'Y', attribute4, NULL),
448 DECODE(g_dff_grouping_option, 'Y', attribute5, NULL),
449 DECODE(g_dff_grouping_option, 'Y', attribute6, NULL),
450 DECODE(g_dff_grouping_option, 'Y', attribute7, NULL),
451 DECODE(g_dff_grouping_option, 'Y', attribute8, NULL),
452 DECODE(g_dff_grouping_option, 'Y', attribute9, NULL),
453 DECODE(g_dff_grouping_option, 'Y', attribute10, NULL);
454
455 cursor temp_orig_sumline_A is
456 select GL_CODE_COMBINATION_ID,
457 PROJECT_ID,
458 EXPENDITURE_ORGANIZATION_ID,
459 EXPENDITURE_TYPE,
460 TASK_ID,
461 AWARD_ID,
462 -- DR_CR_FLAG, Commented for bug 3625667
463 DECODE(sign(SUM(DECODE(dr_cr_flag, 'C', - distribution_amount, distribution_amount))),-1,'C','D') DR_CR_FLAG,
464 SUM(DECODE(dr_cr_flag, 'C', - distribution_amount, distribution_amount)),
465 RUN_ID,
466 SET_OF_BOOKS_ID,
467 BUSINESS_GROUP_ID,
468 DECODE(g_dff_grouping_option, 'Y', attribute_category, NULL) attribute_category,
469 DECODE(g_dff_grouping_option, 'Y', attribute1, NULL) attribute1,
470 DECODE(g_dff_grouping_option, 'Y', attribute2, NULL) attribute2,
471 DECODE(g_dff_grouping_option, 'Y', attribute3, NULL) attribute3,
472 DECODE(g_dff_grouping_option, 'Y', attribute4, NULL) attribute4,
473 DECODE(g_dff_grouping_option, 'Y', attribute5, NULL) attribute5,
474 DECODE(g_dff_grouping_option, 'Y', attribute6, NULL) attribute6,
475 DECODE(g_dff_grouping_option, 'Y', attribute7, NULL) attribute7,
476 DECODE(g_dff_grouping_option, 'Y', attribute8, NULL) attribute8,
477 DECODE(g_dff_grouping_option, 'Y', attribute9, NULL) attribute9,
478 DECODE(g_dff_grouping_option, 'Y', attribute10, NULL) attribute10
479 from PSP_TEMP_ORIG_LINES
480 where RUN_ID = g_run_id
481 group by RUN_ID,
482 SET_OF_BOOKS_ID,
483 BUSINESS_GROUP_ID,
484 GL_CODE_COMBINATION_ID,
485 PROJECT_ID,
486 EXPENDITURE_ORGANIZATION_ID,
487 EXPENDITURE_TYPE,
488 TASK_ID,
489 AWARD_ID,
490 -- DR_CR_FLAG, Commented for Bug 3625667
491 DECODE(g_dff_grouping_option, 'Y', attribute_category, NULL),
492 DECODE(g_dff_grouping_option, 'Y', attribute1, NULL),
493 DECODE(g_dff_grouping_option, 'Y', attribute2, NULL),
494 DECODE(g_dff_grouping_option, 'Y', attribute3, NULL),
495 DECODE(g_dff_grouping_option, 'Y', attribute4, NULL),
496 DECODE(g_dff_grouping_option, 'Y', attribute5, NULL),
497 DECODE(g_dff_grouping_option, 'Y', attribute6, NULL),
498 DECODE(g_dff_grouping_option, 'Y', attribute7, NULL),
499 DECODE(g_dff_grouping_option, 'Y', attribute8, NULL),
500 DECODE(g_dff_grouping_option, 'Y', attribute9, NULL),
501 DECODE(g_dff_grouping_option, 'Y', attribute10, NULL);
502
503 type temp_orig_sumline_rec is record
504 ( array_element_type_id t_num_15_type,
505 array_element_group_id t_num_15_type,
506 array_glccid t_num_15_type,
507 array_project_id t_num_15_type,
508 array_exp_org_id t_num_15_type,
509 array_exp_type t_varchar_30_type,
510 array_task_id t_num_15_type,
511 array_award_id t_num_15_type,
512 array_dr_cr_flag t_varchar_1_type,
513 array_distribution_sum t_number_type, -- Corrected column type defn for bug fix 2916848
514 array_run_id t_num_15_type,
515 array_set_of_books_id t_num_15_type,
516 array_business_group_id t_num_15_type,
517 array_acct_group_id t_num_15_type,
518 array_attribute_category t_varchar_30_type,
519 array_attribute1 t_varchar_150_type,
520 array_attribute2 t_varchar_150_type,
521 array_attribute3 t_varchar_150_type,
522 array_attribute4 t_varchar_150_type,
523 array_attribute5 t_varchar_150_type,
524 array_attribute6 t_varchar_150_type,
525 array_attribute7 t_varchar_150_type,
526 array_attribute8 t_varchar_150_type,
527 array_attribute9 t_varchar_150_type,
528 array_attribute10 t_varchar_150_type);
529
530 orig_sumline_rec temp_orig_sumline_rec;
531 BEGIN
532
533 g_error_api_path := '';
534 fnd_msg_pub.initialize;
535 errbuf := '';
536 g_run_id := p_run_id;
537
538 psp_general.get_currency_precision(p_currency_code, g_precision, g_ext_precision); -- Introduced for bug fix 2916848
539
540 g_dff_grouping_option := psp_general.get_act_dff_grouping_option(p_business_group_id); -- Introduced for bug fix 2908859
541
542 open lines_c1(p_person_id,p_assignment_id,p_begin_date,p_end_date);
543 fetch lines_c1 bulk collect into
544 orig_line_rec.array_element_type_id,
545 orig_line_rec.array_element_group_id,
546 orig_line_rec.array_glccid,
547 orig_line_rec.array_project_id,
548 orig_line_rec.array_exp_org_id,
549 orig_line_rec.array_exp_type,
550 orig_line_rec.array_task_id,
551 orig_line_rec.array_award_id,
552 orig_line_rec.array_distribution_date,
553 orig_line_rec.array_distribution_amount,
554 orig_line_rec.array_distribution_line_id,
555 orig_line_rec.array_dr_cr_flag,
556 orig_line_rec.array_tab_flag,
557 orig_line_rec.array_effective_date,
558 orig_line_rec.array_time_period_id,
559 orig_line_rec.array_payroll_control_id,
560 orig_line_rec.array_attribute_category, -- Introduced DFF columns for bug fix 2908859
561 orig_line_rec.array_attribute1,
562 orig_line_rec.array_attribute2,
563 orig_line_rec.array_attribute3,
564 orig_line_rec.array_attribute4,
565 orig_line_rec.array_attribute5,
566 orig_line_rec.array_attribute6,
567 orig_line_rec.array_attribute7,
568 orig_line_rec.array_attribute8,
569 orig_line_rec.array_attribute9,
570 orig_line_rec.array_attribute10;
571
572 close lines_c1;
573
574 if orig_line_rec.array_distribution_line_id.count > 0 then
575 l_loop_count := l_loop_count + orig_line_rec.array_distribution_line_id.count;
576 FORALL i IN 1..orig_line_rec.array_distribution_line_id.count
577 insert into psp_temp_orig_lines(
578 element_type_id,
579 element_group_id,
580 gl_code_combination_id,
581 project_id,
582 expenditure_organization_id,
583 expenditure_type,
584 task_id,
585 award_id,
586 orig_distribution_date,
587 distribution_amount,
588 orig_line_id,
589 dr_cr_flag,
590 orig_source_type,
591 effective_date,
592 time_period_id,
593 payroll_control_id,
594 run_id,
595 business_group_id,
596 set_of_books_id,
597 attribute_category, -- Introduced DFF columns for bug fix 2908859
598 attribute1,
599 attribute2,
600 attribute3,
601 attribute4,
602 attribute5,
603 attribute6,
604 attribute7,
605 attribute8,
606 attribute9,
607 attribute10)
608 values
609 (orig_line_rec.array_element_type_id(i),
610 orig_line_rec.array_element_group_id(i),
611 orig_line_rec.array_glccid(i),
612 orig_line_rec.array_project_id(i),
613 orig_line_rec.array_exp_org_id(i),
614 orig_line_rec.array_exp_type(i),
615 orig_line_rec.array_task_id(i),
616 orig_line_rec.array_award_id(i),
617 orig_line_rec.array_distribution_date(i),
618 orig_line_rec.array_distribution_amount(i),
619 orig_line_rec.array_distribution_line_id(i),
620 orig_line_rec.array_dr_cr_flag(i),
621 orig_line_rec.array_tab_flag(i),
622 orig_line_rec.array_effective_date(i),
623 orig_line_rec.array_time_period_id(i),
624 orig_line_rec.array_payroll_control_id(i),
625 g_run_id,
626 p_business_group_id,
627 p_set_of_books_id,
628 orig_line_rec.array_attribute_category(i), -- Introduced DFF columns for bug fix 2908859
629 orig_line_rec.array_attribute1(i),
630 orig_line_rec.array_attribute2(i),
631 orig_line_rec.array_attribute3(i),
632 orig_line_rec.array_attribute4(i),
633 orig_line_rec.array_attribute5(i),
634 orig_line_rec.array_attribute6(i),
635 orig_line_rec.array_attribute7(i),
636 orig_line_rec.array_attribute8(i),
637 orig_line_rec.array_attribute9(i),
638 orig_line_rec.array_attribute10(i));
639
640 /* flush all arrays */
641 orig_line_rec.array_element_type_id .delete;
642 orig_line_rec.array_element_group_id .delete;
643 orig_line_rec.array_glccid .delete;
644 orig_line_rec.array_project_id .delete;
645 orig_line_rec.array_exp_org_id .delete;
646 orig_line_rec.array_exp_type .delete;
647 orig_line_rec.array_task_id .delete;
648 orig_line_rec.array_award_id .delete;
649 orig_line_rec.array_distribution_date .delete;
650 orig_line_rec.array_distribution_amount .delete;
651 orig_line_rec.array_distribution_line_id .delete;
652 orig_line_rec.array_dr_cr_flag .delete;
653 orig_line_rec.array_tab_flag .delete;
654 orig_line_rec.array_effective_date .delete;
655 orig_line_rec.array_time_period_id .delete;
656 orig_line_rec.array_payroll_control_id .delete;
657 orig_line_rec.array_attribute_category.delete; -- Introduced for bug fix 2908859
658 orig_line_rec.array_attribute1.delete;
659 orig_line_rec.array_attribute2.delete;
660 orig_line_rec.array_attribute3.delete;
661 orig_line_rec.array_attribute4.delete;
662 orig_line_rec.array_attribute5.delete;
663 orig_line_rec.array_attribute6.delete;
664 orig_line_rec.array_attribute7.delete;
665 orig_line_rec.array_attribute8.delete;
666 orig_line_rec.array_attribute9.delete;
667 orig_line_rec.array_attribute10.delete;
668 end if;
669
670 open lines_c2(p_person_id,p_assignment_id,p_begin_date,p_end_date);
671 fetch lines_c2 bulk collect into
672 orig_line_rec.array_element_type_id,
673 orig_line_rec.array_element_group_id,
674 orig_line_rec.array_glccid,
675 orig_line_rec.array_project_id,
676 orig_line_rec.array_exp_org_id,
677 orig_line_rec.array_exp_type,
678 orig_line_rec.array_task_id,
679 orig_line_rec.array_award_id,
680 orig_line_rec.array_distribution_date,
681 orig_line_rec.array_distribution_amount,
682 orig_line_rec.array_distribution_line_id,
683 orig_line_rec.array_dr_cr_flag,
684 orig_line_rec.array_tab_flag,
685 orig_line_rec.array_effective_date,
686 orig_line_rec.array_time_period_id,
687 orig_line_rec.array_payroll_control_id,
688 orig_line_rec.array_attribute_category, -- Introduced DFF columns for bug fix 2908859
689 orig_line_rec.array_attribute1,
690 orig_line_rec.array_attribute2,
691 orig_line_rec.array_attribute3,
692 orig_line_rec.array_attribute4,
693 orig_line_rec.array_attribute5,
694 orig_line_rec.array_attribute6,
695 orig_line_rec.array_attribute7,
696 orig_line_rec.array_attribute8,
697 orig_line_rec.array_attribute9,
698 orig_line_rec.array_attribute10;
699
700 close lines_c2;
701
702 if orig_line_rec.array_distribution_line_id.count > 0 then
703 l_loop_count := l_loop_count + orig_line_rec.array_distribution_line_id.count;
704 FORALL i IN 1..orig_line_rec.array_distribution_line_id.count
705 insert into psp_temp_orig_lines(
706 element_type_id,
707 element_group_id,
708 gl_code_combination_id,
709 project_id,
710 expenditure_organization_id,
711 expenditure_type,
712 task_id,
713 award_id,
714 orig_distribution_date,
715 distribution_amount,
716 orig_line_id,
717 dr_cr_flag,
718 orig_source_type,
719 effective_date,
720 time_period_id,
721 payroll_control_id,
722 run_id,
723 business_group_id,
724 set_of_books_id,
725 attribute_category, -- Introduced DFF columns for bug fix 2908859
726 attribute1,
727 attribute2,
728 attribute3,
729 attribute4,
730 attribute5,
731 attribute6,
732 attribute7,
733 attribute8,
734 attribute9,
735 attribute10)
736 values
737 (orig_line_rec.array_element_type_id(i),
738 orig_line_rec.array_element_group_id(i),
739 orig_line_rec.array_glccid(i),
740 orig_line_rec.array_project_id(i),
741 orig_line_rec.array_exp_org_id(i),
742 orig_line_rec.array_exp_type(i),
743 orig_line_rec.array_task_id(i),
744 orig_line_rec.array_award_id(i),
745 orig_line_rec.array_distribution_date(i),
746 orig_line_rec.array_distribution_amount(i),
747 orig_line_rec.array_distribution_line_id(i),
748 orig_line_rec.array_dr_cr_flag(i),
749 orig_line_rec.array_tab_flag(i),
750 orig_line_rec.array_effective_date(i),
751 orig_line_rec.array_time_period_id(i),
752 orig_line_rec.array_payroll_control_id(i),
753 g_run_id,
754 p_business_group_id,
755 p_set_of_books_id,
756 orig_line_rec.array_attribute_category(i), -- Introduced DFF columns for bug fix 2908859
757 orig_line_rec.array_attribute1(i),
758 orig_line_rec.array_attribute2(i),
759 orig_line_rec.array_attribute3(i),
760 orig_line_rec.array_attribute4(i),
761 orig_line_rec.array_attribute5(i),
762 orig_line_rec.array_attribute6(i),
763 orig_line_rec.array_attribute7(i),
764 orig_line_rec.array_attribute8(i),
765 orig_line_rec.array_attribute9(i),
766 orig_line_rec.array_attribute10(i));
767
768 /* flush all arrays */
769 orig_line_rec.array_element_type_id .delete;
770 orig_line_rec.array_element_group_id .delete;
771 orig_line_rec.array_glccid .delete;
772 orig_line_rec.array_project_id .delete;
773 orig_line_rec.array_exp_org_id .delete;
774 orig_line_rec.array_exp_type .delete;
775 orig_line_rec.array_task_id .delete;
776 orig_line_rec.array_award_id .delete;
777 orig_line_rec.array_distribution_date .delete;
778 orig_line_rec.array_distribution_amount .delete;
779 orig_line_rec.array_distribution_line_id .delete;
780 orig_line_rec.array_dr_cr_flag .delete;
781 orig_line_rec.array_tab_flag .delete;
782 orig_line_rec.array_effective_date .delete;
783 orig_line_rec.array_time_period_id .delete;
784 orig_line_rec.array_payroll_control_id .delete;
785 orig_line_rec.array_attribute_category.delete; -- Introduced for bug fix 2908859
786 orig_line_rec.array_attribute1.delete;
787 orig_line_rec.array_attribute2.delete;
788 orig_line_rec.array_attribute3.delete;
789 orig_line_rec.array_attribute4.delete;
790 orig_line_rec.array_attribute5.delete;
791 orig_line_rec.array_attribute6.delete;
792 orig_line_rec.array_attribute7.delete;
793 orig_line_rec.array_attribute8.delete;
794 orig_line_rec.array_attribute9.delete;
795 orig_line_rec.array_attribute10.delete;
796 end if;
797
798 open lines_c3(p_person_id,p_assignment_id,p_begin_date,p_end_date);
799 fetch lines_c3 bulk collect into
800 orig_line_rec.array_element_type_id,
801 orig_line_rec.array_element_group_id,
802 orig_line_rec.array_glccid,
803 orig_line_rec.array_project_id,
804 orig_line_rec.array_exp_org_id,
805 orig_line_rec.array_exp_type,
806 orig_line_rec.array_task_id,
807 orig_line_rec.array_award_id,
808 orig_line_rec.array_distribution_date,
809 orig_line_rec.array_distribution_amount,
810 orig_line_rec.array_distribution_line_id,
811 orig_line_rec.array_dr_cr_flag,
812 orig_line_rec.array_tab_flag,
813 orig_line_rec.array_effective_date,
814 orig_line_rec.array_time_period_id,
815 orig_line_rec.array_payroll_control_id,
816 orig_line_rec.array_attribute_category, -- Introduced DFF columns for bug fix 2908859
817 orig_line_rec.array_attribute1,
818 orig_line_rec.array_attribute2,
819 orig_line_rec.array_attribute3,
820 orig_line_rec.array_attribute4,
821 orig_line_rec.array_attribute5,
822 orig_line_rec.array_attribute6,
823 orig_line_rec.array_attribute7,
824 orig_line_rec.array_attribute8,
825 orig_line_rec.array_attribute9,
826 orig_line_rec.array_attribute10;
827
828 close lines_c3;
829
830 if orig_line_rec.array_distribution_line_id.count > 0 then
831 l_loop_count := l_loop_count + orig_line_rec.array_distribution_line_id.count;
832 FORALL i IN 1..orig_line_rec.array_distribution_line_id.count
833 insert into psp_temp_orig_lines(
834 element_type_id,
835 element_group_id,
836 gl_code_combination_id,
837 project_id,
838 expenditure_organization_id,
839 expenditure_type,
840 task_id,
841 award_id,
842 orig_distribution_date,
843 distribution_amount,
844 orig_line_id,
845 dr_cr_flag,
846 orig_source_type,
847 effective_date,
848 time_period_id,
849 payroll_control_id,
850 run_id,
851 business_group_id,
852 set_of_books_id,
853 attribute_category, -- Introduced DFF columns for bug fix 2908859
854 attribute1,
855 attribute2,
856 attribute3,
857 attribute4,
858 attribute5,
859 attribute6,
860 attribute7,
861 attribute8,
862 attribute9,
863 attribute10)
864 values
865 (orig_line_rec.array_element_type_id(i),
866 orig_line_rec.array_element_group_id(i),
867 orig_line_rec.array_glccid(i),
868 orig_line_rec.array_project_id(i),
869 orig_line_rec.array_exp_org_id(i),
870 orig_line_rec.array_exp_type(i),
871 orig_line_rec.array_task_id(i),
872 orig_line_rec.array_award_id(i),
873 orig_line_rec.array_distribution_date(i),
874 orig_line_rec.array_distribution_amount(i),
875 orig_line_rec.array_distribution_line_id(i),
876 orig_line_rec.array_dr_cr_flag(i),
877 orig_line_rec.array_tab_flag(i),
878 orig_line_rec.array_effective_date(i),
879 orig_line_rec.array_time_period_id(i),
880 orig_line_rec.array_payroll_control_id(i),
881 g_run_id,
882 p_business_group_id,
883 p_set_of_books_id,
884 orig_line_rec.array_attribute_category(i), -- Introduced DFF columns for bug fix 2908859
885 orig_line_rec.array_attribute1(i),
886 orig_line_rec.array_attribute2(i),
887 orig_line_rec.array_attribute3(i),
888 orig_line_rec.array_attribute4(i),
889 orig_line_rec.array_attribute5(i),
890 orig_line_rec.array_attribute6(i),
891 orig_line_rec.array_attribute7(i),
892 orig_line_rec.array_attribute8(i),
893 orig_line_rec.array_attribute9(i),
894 orig_line_rec.array_attribute10(i));
895
896 /* flush all arrays */
897 orig_line_rec.array_element_type_id .delete;
898 orig_line_rec.array_element_group_id .delete;
899 orig_line_rec.array_glccid .delete;
900 orig_line_rec.array_project_id .delete;
901 orig_line_rec.array_exp_org_id .delete;
902 orig_line_rec.array_exp_type .delete;
903 orig_line_rec.array_task_id .delete;
904 orig_line_rec.array_award_id .delete;
905 orig_line_rec.array_distribution_date .delete;
906 orig_line_rec.array_distribution_amount .delete;
907 orig_line_rec.array_distribution_line_id .delete;
908 orig_line_rec.array_dr_cr_flag .delete;
909 orig_line_rec.array_tab_flag .delete;
910 orig_line_rec.array_effective_date .delete;
911 orig_line_rec.array_time_period_id .delete;
912 orig_line_rec.array_payroll_control_id .delete;
913 orig_line_rec.array_attribute_category.delete; -- Introduced for bug fix 2908859
914 orig_line_rec.array_attribute1.delete;
915 orig_line_rec.array_attribute2.delete;
916 orig_line_rec.array_attribute3.delete;
917 orig_line_rec.array_attribute4.delete;
918 orig_line_rec.array_attribute5.delete;
919 orig_line_rec.array_attribute6.delete;
920 orig_line_rec.array_attribute7.delete;
921 orig_line_rec.array_attribute8.delete;
922 orig_line_rec.array_attribute9.delete;
923 orig_line_rec.array_attribute10.delete;
924 end if;
925
926 if l_loop_count = 0 then
927 RAISE NO_RECORDS_FOUND;
928 end if;
929
930 if l_loop_count > 0 then
931 if p_adjust_by = 'E' then
932 open temp_orig_sumline_E;
933 fetch temp_orig_sumline_E bulk collect into
934 orig_sumline_rec.array_element_type_id,
935 orig_sumline_rec.array_glccid,
936 orig_sumline_rec.array_project_id,
937 orig_sumline_rec.array_exp_org_id,
938 orig_sumline_rec.array_exp_type,
939 orig_sumline_rec.array_task_id,
940 orig_sumline_rec.array_award_id,
941 orig_sumline_rec.array_dr_cr_flag,
942 orig_sumline_rec.array_distribution_sum,
943 orig_sumline_rec.array_run_id,
944 orig_sumline_rec.array_set_of_books_id,
945 orig_sumline_rec.array_business_group_id,
946 orig_sumline_rec.array_attribute_category, -- Introduced DFF columns for bug fix 2908859
947 orig_sumline_rec.array_attribute1,
948 orig_sumline_rec.array_attribute2,
949 orig_sumline_rec.array_attribute3,
950 orig_sumline_rec.array_attribute4,
951 orig_sumline_rec.array_attribute5,
952 orig_sumline_rec.array_attribute6,
953 orig_sumline_rec.array_attribute7,
954 orig_sumline_rec.array_attribute8,
955 orig_sumline_rec.array_attribute9,
956 orig_sumline_rec.array_attribute10;
957 close temp_orig_sumline_E;
958
959 for k in 1..orig_sumline_rec.array_run_id.count
960 loop
961 orig_sumline_rec.array_acct_group_id(k):= k;
962 end loop;
963
964 forall k in 1..orig_sumline_rec.array_run_id.count
965 insert into PSP_TEMP_ORIG_SUMLINES (
966 ELEMENT_TYPE_ID,
967 GL_CODE_COMBINATION_ID,
968 PROJECT_ID,
969 EXPENDITURE_ORGANIZATION_ID,
970 EXPENDITURE_TYPE,
971 TASK_ID,
972 AWARD_ID,
973 DR_CR_FLAG,
974 DISTRIBUTION_SUM,
975 RUN_ID,
976 SET_OF_BOOKS_ID,
977 BUSINESS_GROUP_ID,
978 ACCT_GROUP_ID,
979 attribute_category, -- Introduced DFF columns for bug fix 2908859
980 attribute1,
981 attribute2,
982 attribute3,
983 attribute4,
984 attribute5,
985 attribute6,
986 attribute7,
987 attribute8,
988 attribute9,
989 attribute10)
990 values ( orig_sumline_rec.array_element_type_id(k),
991 orig_sumline_rec.array_glccid(k),
992 orig_sumline_rec.array_project_id(k),
993 orig_sumline_rec.array_exp_org_id(k),
994 orig_sumline_rec.array_exp_type(k),
995 orig_sumline_rec.array_task_id(k),
996 orig_sumline_rec.array_award_id(k),
997 orig_sumline_rec.array_dr_cr_flag(k),
998 orig_sumline_rec.array_distribution_sum(k),
999 orig_sumline_rec.array_run_id(k),
1000 orig_sumline_rec.array_set_of_books_id(k),
1001 orig_sumline_rec.array_business_group_id(k),
1002 orig_sumline_rec.array_acct_group_id(k),
1003 orig_sumline_rec.array_attribute_category(k),
1004 orig_sumline_rec.array_attribute1(k),
1005 orig_sumline_rec.array_attribute2(k),
1006 orig_sumline_rec.array_attribute3(k),
1007 orig_sumline_rec.array_attribute4(k),
1008 orig_sumline_rec.array_attribute5(k),
1009 orig_sumline_rec.array_attribute6(k),
1010 orig_sumline_rec.array_attribute7(k),
1011 orig_sumline_rec.array_attribute8(k),
1012 orig_sumline_rec.array_attribute9(k),
1013 orig_sumline_rec.array_attribute10(k));
1014
1015 Update psp_temp_orig_lines LINE
1016 set LINE.acct_group_id = (select MAS.acct_group_id
1017 from psp_temp_orig_sumlines MAS
1018 where MAS.run_id = g_run_id and
1019 nvl(MAS.element_type_id,-9) = nvl(LINE.element_type_id,-9) and
1020 -- MAS.dr_cr_flag = LINE.dr_cr_flag and Commented for Bug 3625667
1021 nvl(MAS.gl_code_combination_id,-9) =
1022 nvl(LINE.gl_code_combination_id,-9) and
1023 nvl(MAS.project_id,-9)= nvl(LINE.project_id,-9) and
1024 nvl(MAS.task_id,-9) = nvl(LINE.task_id,-9) and
1025 nvl(MAS.expenditure_organization_id,-9) =
1026 nvl(LINE.expenditure_organization_id,-9) and
1027 nvl(MAS.award_id, -9) = nvl(LINE.award_id, -9) and
1028 (MAS.expenditure_type = LINE.expenditure_type or
1029 (MAS.expenditure_type is null and LINE.expenditure_type is null))
1030 AND (NVL(mas.attribute_category, 'NULL') = NVL(line.attribute_category, 'NULL')) -- Introduced DFF column check for bug fix 2908859
1031 AND (NVL(mas.attribute1, 'NULL') = NVL(line.attribute1, 'NULL'))
1032 AND (NVL(mas.attribute2, 'NULL') = NVL(line.attribute2, 'NULL'))
1033 AND (NVL(mas.attribute3, 'NULL') = NVL(line.attribute3, 'NULL'))
1034 AND (NVL(mas.attribute4, 'NULL') = NVL(line.attribute4, 'NULL'))
1035 AND (NVL(mas.attribute5, 'NULL') = NVL(line.attribute5, 'NULL'))
1036 AND (NVL(mas.attribute6, 'NULL') = NVL(line.attribute6, 'NULL'))
1037 AND (NVL(mas.attribute7, 'NULL') = NVL(line.attribute7, 'NULL'))
1038 AND (NVL(mas.attribute8, 'NULL') = NVL(line.attribute8, 'NULL'))
1039 AND (NVL(mas.attribute9, 'NULL') = NVL(line.attribute9, 'NULL'))
1040 AND (NVL(mas.attribute10, 'NULL') = NVL(line.attribute10, 'NULL')))
1041 where LINE.run_id = g_run_id;
1042
1043
1044 elsif p_adjust_by = 'G' then
1045 open temp_orig_sumline_G;
1046 fetch temp_orig_sumline_G bulk collect into
1047 orig_sumline_rec.array_element_group_id,
1048 orig_sumline_rec.array_glccid,
1049 orig_sumline_rec.array_project_id,
1050 orig_sumline_rec.array_exp_org_id,
1051 orig_sumline_rec.array_exp_type,
1052 orig_sumline_rec.array_task_id,
1053 orig_sumline_rec.array_award_id,
1054 orig_sumline_rec.array_dr_cr_flag,
1055 orig_sumline_rec.array_distribution_sum,
1056 orig_sumline_rec.array_run_id,
1057 orig_sumline_rec.array_set_of_books_id,
1058 orig_sumline_rec.array_business_group_id,
1059 orig_sumline_rec.array_attribute_category, -- Introduced DFF columns for bug fix 2908859
1060 orig_sumline_rec.array_attribute1,
1061 orig_sumline_rec.array_attribute2,
1062 orig_sumline_rec.array_attribute3,
1063 orig_sumline_rec.array_attribute4,
1064 orig_sumline_rec.array_attribute5,
1065 orig_sumline_rec.array_attribute6,
1066 orig_sumline_rec.array_attribute7,
1067 orig_sumline_rec.array_attribute8,
1068 orig_sumline_rec.array_attribute9,
1069 orig_sumline_rec.array_attribute10;
1070 close temp_orig_sumline_G;
1071
1072 for k in 1..orig_sumline_rec.array_run_id.count
1073 loop
1074 orig_sumline_rec.array_acct_group_id(k):= k;
1075 end loop;
1076
1077 forall k in 1..orig_sumline_rec.array_run_id.count
1078 insert into PSP_TEMP_ORIG_SUMLINES (
1079 ELEMENT_GROUP_ID,
1080 GL_CODE_COMBINATION_ID,
1081 PROJECT_ID,
1082 EXPENDITURE_ORGANIZATION_ID,
1083 EXPENDITURE_TYPE,
1084 TASK_ID,
1085 AWARD_ID,
1086 DR_CR_FLAG,
1087 DISTRIBUTION_SUM,
1088 RUN_ID,
1089 SET_OF_BOOKS_ID,
1090 BUSINESS_GROUP_ID,
1091 ACCT_GROUP_ID,
1092 attribute_category, -- Introduced DFF columns for bug fix 2908859
1093 attribute1,
1094 attribute2,
1095 attribute3,
1096 attribute4,
1097 attribute5,
1098 attribute6,
1099 attribute7,
1100 attribute8,
1101 attribute9,
1102 attribute10)
1103 values ( orig_sumline_rec.array_element_group_id(k),
1104 orig_sumline_rec.array_glccid(k),
1105 orig_sumline_rec.array_project_id(k),
1106 orig_sumline_rec.array_exp_org_id(k),
1107 orig_sumline_rec.array_exp_type(k),
1108 orig_sumline_rec.array_task_id(k),
1109 orig_sumline_rec.array_award_id(k),
1110 orig_sumline_rec.array_dr_cr_flag(k),
1111 orig_sumline_rec.array_distribution_sum(k),
1112 orig_sumline_rec.array_run_id(k),
1113 orig_sumline_rec.array_set_of_books_id(k),
1114 orig_sumline_rec.array_business_group_id(k),
1115 orig_sumline_rec.array_acct_group_id(k),
1116 orig_sumline_rec.array_attribute_category(k),
1117 orig_sumline_rec.array_attribute1(k),
1118 orig_sumline_rec.array_attribute2(k),
1119 orig_sumline_rec.array_attribute3(k),
1120 orig_sumline_rec.array_attribute4(k),
1121 orig_sumline_rec.array_attribute5(k),
1122 orig_sumline_rec.array_attribute6(k),
1123 orig_sumline_rec.array_attribute7(k),
1124 orig_sumline_rec.array_attribute8(k),
1125 orig_sumline_rec.array_attribute9(k),
1126 orig_sumline_rec.array_attribute10(k));
1127 Update psp_temp_orig_lines LINE
1128 set LINE.acct_group_id = (select MAS.acct_group_id
1129 from psp_temp_orig_sumlines MAS
1130 where MAS.run_id = g_run_id and
1131 nvl(MAS.element_group_id,-9) = nvl(LINE.element_group_id,-9) and
1132 -- MAS.dr_cr_flag = LINE.dr_cr_flag and Commented for Bug 3625667
1133 nvl(MAS.gl_code_combination_id,-9) =
1134 nvl(LINE.gl_code_combination_id,-9) and
1135 nvl(MAS.project_id,-9)= nvl(LINE.project_id,-9) and
1136 nvl(MAS.task_id,-9) = nvl(LINE.task_id,-9) and
1137 nvl(MAS.expenditure_organization_id,-9) =
1138 nvl(LINE.expenditure_organization_id,-9) and
1139 nvl(MAS.award_id, -9) = nvl(LINE.award_id, -9) and
1140 (MAS.expenditure_type = LINE.expenditure_type or
1141 (MAS.expenditure_type is null and LINE.expenditure_type is null))
1142 AND (NVL(mas.attribute_category, 'NULL') = NVL(line.attribute_category, 'NULL')) -- Introduced DFF column check for bug fix 2908859
1143 AND (NVL(mas.attribute1, 'NULL') = NVL(line.attribute1, 'NULL'))
1144 AND (NVL(mas.attribute2, 'NULL') = NVL(line.attribute2, 'NULL'))
1145 AND (NVL(mas.attribute3, 'NULL') = NVL(line.attribute3, 'NULL'))
1146 AND (NVL(mas.attribute4, 'NULL') = NVL(line.attribute4, 'NULL'))
1147 AND (NVL(mas.attribute5, 'NULL') = NVL(line.attribute5, 'NULL'))
1148 AND (NVL(mas.attribute6, 'NULL') = NVL(line.attribute6, 'NULL'))
1149 AND (NVL(mas.attribute7, 'NULL') = NVL(line.attribute7, 'NULL'))
1150 AND (NVL(mas.attribute8, 'NULL') = NVL(line.attribute8, 'NULL'))
1151 AND (NVL(mas.attribute9, 'NULL') = NVL(line.attribute9, 'NULL'))
1152 AND (NVL(mas.attribute10, 'NULL') = NVL(line.attribute10, 'NULL')))
1153 where LINE.run_id = g_run_id;
1154
1155
1156 else --- at assignment level
1157 open temp_orig_sumline_A;
1158 fetch temp_orig_sumline_A bulk collect into
1159 orig_sumline_rec.array_glccid,
1160 orig_sumline_rec.array_project_id,
1161 orig_sumline_rec.array_exp_org_id,
1162 orig_sumline_rec.array_exp_type,
1163 orig_sumline_rec.array_task_id,
1164 orig_sumline_rec.array_award_id,
1165 orig_sumline_rec.array_dr_cr_flag,
1166 orig_sumline_rec.array_distribution_sum,
1167 orig_sumline_rec.array_run_id,
1168 orig_sumline_rec.array_set_of_books_id,
1169 orig_sumline_rec.array_business_group_id,
1170 orig_sumline_rec.array_attribute_category, -- Introduced DFF columns for bug fix 2908859
1171 orig_sumline_rec.array_attribute1,
1172 orig_sumline_rec.array_attribute2,
1173 orig_sumline_rec.array_attribute3,
1174 orig_sumline_rec.array_attribute4,
1175 orig_sumline_rec.array_attribute5,
1176 orig_sumline_rec.array_attribute6,
1177 orig_sumline_rec.array_attribute7,
1178 orig_sumline_rec.array_attribute8,
1179 orig_sumline_rec.array_attribute9,
1180 orig_sumline_rec.array_attribute10;
1181 close temp_orig_sumline_A;
1182
1183 for k in 1..orig_sumline_rec.array_run_id.count
1184 loop
1185 orig_sumline_rec.array_acct_group_id(k):= k;
1186 end loop;
1187
1188 forall k in 1..orig_sumline_rec.array_run_id.count
1189 insert into PSP_TEMP_ORIG_SUMLINES (
1190 GL_CODE_COMBINATION_ID,
1191 PROJECT_ID,
1192 EXPENDITURE_ORGANIZATION_ID,
1193 EXPENDITURE_TYPE,
1194 TASK_ID,
1195 AWARD_ID,
1196 DR_CR_FLAG,
1197 DISTRIBUTION_SUM,
1198 RUN_ID,
1199 SET_OF_BOOKS_ID,
1200 BUSINESS_GROUP_ID,
1201 ACCT_GROUP_ID,
1202 attribute_category, -- Introduced DFF columns for bug fix 2908859
1203 attribute1,
1204 attribute2,
1205 attribute3,
1206 attribute4,
1207 attribute5,
1208 attribute6,
1209 attribute7,
1210 attribute8,
1211 attribute9,
1212 attribute10)
1213 values ( orig_sumline_rec.array_glccid(k),
1214 orig_sumline_rec.array_project_id(k),
1215 orig_sumline_rec.array_exp_org_id(k),
1216 orig_sumline_rec.array_exp_type(k),
1217 orig_sumline_rec.array_task_id(k),
1218 orig_sumline_rec.array_award_id(k),
1219 orig_sumline_rec.array_dr_cr_flag(k),
1220 orig_sumline_rec.array_distribution_sum(k),
1221 orig_sumline_rec.array_run_id(k),
1222 orig_sumline_rec.array_set_of_books_id(k),
1223 orig_sumline_rec.array_business_group_id(k),
1224 orig_sumline_rec.array_acct_group_id(k),
1225 orig_sumline_rec.array_attribute_category(k),
1226 orig_sumline_rec.array_attribute1(k),
1227 orig_sumline_rec.array_attribute2(k),
1228 orig_sumline_rec.array_attribute3(k),
1229 orig_sumline_rec.array_attribute4(k),
1230 orig_sumline_rec.array_attribute5(k),
1231 orig_sumline_rec.array_attribute6(k),
1232 orig_sumline_rec.array_attribute7(k),
1233 orig_sumline_rec.array_attribute8(k),
1234 orig_sumline_rec.array_attribute9(k),
1235 orig_sumline_rec.array_attribute10(k));
1236
1237
1238 Update psp_temp_orig_lines LINE
1239 set LINE.acct_group_id = (select MAS.acct_group_id
1240 from psp_temp_orig_sumlines MAS
1241 where MAS.run_id = g_run_id and
1242 -- MAS.dr_cr_flag = LINE.dr_cr_flag and Commented for bug 3625667
1243 nvl(MAS.gl_code_combination_id,-9) =
1244 nvl(LINE.gl_code_combination_id,-9) and
1245 nvl(MAS.project_id,-9)= nvl(LINE.project_id,-9) and
1246 nvl(MAS.task_id,-9) = nvl(LINE.task_id,-9) and
1247 nvl(MAS.expenditure_organization_id,-9) =
1248 nvl(LINE.expenditure_organization_id,-9) and
1249 nvl(MAS.award_id, -9) = nvl(LINE.award_id, -9) and
1250 (MAS.expenditure_type = LINE.expenditure_type or
1251 (MAS.expenditure_type is null and LINE.expenditure_type is null))
1252 AND (NVL(mas.attribute_category, 'NULL') = NVL(line.attribute_category, 'NULL')) -- Introduced DFF column check for bug fix 2908859
1253 AND (NVL(mas.attribute1, 'NULL') = NVL(line.attribute1, 'NULL'))
1254 AND (NVL(mas.attribute2, 'NULL') = NVL(line.attribute2, 'NULL'))
1255 AND (NVL(mas.attribute3, 'NULL') = NVL(line.attribute3, 'NULL'))
1256 AND (NVL(mas.attribute4, 'NULL') = NVL(line.attribute4, 'NULL'))
1257 AND (NVL(mas.attribute5, 'NULL') = NVL(line.attribute5, 'NULL'))
1258 AND (NVL(mas.attribute6, 'NULL') = NVL(line.attribute6, 'NULL'))
1259 AND (NVL(mas.attribute7, 'NULL') = NVL(line.attribute7, 'NULL'))
1260 AND (NVL(mas.attribute8, 'NULL') = NVL(line.attribute8, 'NULL'))
1261 AND (NVL(mas.attribute9, 'NULL') = NVL(line.attribute9, 'NULL'))
1262 AND (NVL(mas.attribute10, 'NULL') = NVL(line.attribute10, 'NULL')))
1263 where LINE.run_id = g_run_id;
1264 end if;
1265
1266 -- flush arrays
1267 orig_sumline_rec.array_element_type_id.delete;
1268 orig_sumline_rec.array_element_group_id.delete;
1269 orig_sumline_rec.array_glccid.delete;
1270 orig_sumline_rec.array_project_id.delete;
1271 orig_sumline_rec.array_exp_org_id.delete;
1272 orig_sumline_rec.array_exp_type.delete;
1273 orig_sumline_rec.array_task_id.delete;
1274 orig_sumline_rec.array_award_id.delete;
1275 orig_sumline_rec.array_dr_cr_flag.delete;
1276 orig_sumline_rec.array_distribution_sum.delete;
1277 orig_sumline_rec.array_run_id.delete;
1278 orig_sumline_rec.array_set_of_books_id.delete;
1279 orig_sumline_rec.array_business_group_id.delete;
1280 orig_sumline_rec.array_acct_group_id.delete;
1281 orig_sumline_rec.array_attribute_category.delete;
1282 orig_sumline_rec.array_attribute1.delete;
1283 orig_sumline_rec.array_attribute2.delete;
1284 orig_sumline_rec.array_attribute3.delete;
1285 orig_sumline_rec.array_attribute4.delete;
1286 orig_sumline_rec.array_attribute5.delete;
1287 orig_sumline_rec.array_attribute6.delete;
1288 orig_sumline_rec.array_attribute7.delete;
1289 orig_sumline_rec.array_attribute8.delete;
1290 orig_sumline_rec.array_attribute9.delete;
1291 orig_sumline_rec.array_attribute10.delete;
1292
1293
1294 end if;
1295 retcode := 0;
1296
1297 EXCEPTION
1298 WHEN IN_USE_EXCEPTION THEN
1299 retcode := 3;
1300
1301 WHEN NO_RECORDS_FOUND THEN
1302 retcode := 1;
1303
1304 WHEN OTHERS THEN
1305 g_error_api_path := 'LOAD_TABLE:'||g_error_api_path;
1306 fnd_msg_pub.get(p_msg_index => FND_MSG_PUB.G_FIRST,
1307 p_encoded => FND_API.G_FALSE,
1308 p_data => l_msg_data,
1309 p_msg_index_out => l_msg_count);
1310 errbuf := l_msg_data || fnd_global.local_chr(10) || g_error_api_path;
1311 retcode := 2;
1312 END;
1313
1314 ----------P R O C E D U R E: GET_APPROVAL_HEADER -------------------------------
1315 --
1316 --
1317 -- Purpose: This procedure is called by the find batched screen in the
1318 -- distribution transfers approval form.
1319 -- The purpose is to retrieve the display only header information
1320 -- in the approval form for the batch name the user selects in
1321 -- the find batches screen.
1322 --
1323 ----------------------------------------------------------------------------------
1324
1325 PROCEDURE get_approval_header(errbuf OUT NOCOPY VARCHAR2,
1326 retcode OUT NOCOPY VARCHAR2,
1327 p_batch_name IN VARCHAR2,
1328 p_business_group_id IN NUMBER,
1329 p_set_of_books_id IN NUMBER,
1330 l_full_name OUT NOCOPY VARCHAR2,
1331 l_employee_number OUT NOCOPY VARCHAR2,
1332 l_assignment_number OUT NOCOPY VARCHAR2,
1333 l_assignment_organization OUT NOCOPY VARCHAR2, --added for DA-ENH
1334 l_begin_date OUT NOCOPY DATE,
1335 l_end_date OUT NOCOPY DATE,
1336 l_currency_code OUT NOCOPY VARCHAR2, -- Introduced for Bug fix 2916848
1337 l_batch_comments OUT NOCOPY VARCHAR2) IS
1338
1339
1340 l_person_id NUMBER;
1341 l_assignment_id NUMBER;
1342 l_element_type_id NUMBER;
1343
1344 CURSOR app_dates_c is
1345
1346 SELECT
1347 person_id,
1348 assignment_id,
1349 min(distribution_date),
1350 max(distribution_date)
1351 FROM
1352 psp_adjustment_lines
1353 WHERE
1354 batch_name = p_batch_name
1355 and business_group_id = p_business_group_id
1356 and set_of_books_id = p_set_of_books_id
1357 GROUP BY
1358 person_id,assignment_id;
1359
1360
1361 CURSOR app_header_c is
1362 /* changed the cursor select for DA-ENH */
1363 /***** Modified the following cursor defn for R12 performance fixes (bug 4507892)
1364 select distinct ppf.full_name,
1365 ppf.employee_number,
1366 pas.assignment_number
1367 from per_people_f ppf,
1368 per_assignments_f pas
1369 where ppf.person_id = l_person_id and
1370 pas.assignment_id = l_assignment_id;
1371 end of comment for bug fix 4507892 *****/
1372 -- New cursor defn for bug fix 4507892
1373 SELECT DISTINCT ppf.full_name,
1374 ppf.employee_number,
1375 paf.assignment_number
1376 FROM per_people_f ppf,
1377 per_assignments_f paf
1378 WHERE ppf.person_id = l_person_id
1379 AND paf.person_id = ppf.person_id
1380 AND paf.assignment_id = l_assignment_id;
1381
1382 CURSOR app_comments_c is
1383 SELECT comments ,
1384 currency_code -- Introduced this for bug fix 2916848
1385 FROM psp_adjustment_control_table
1386 WHERE adjustment_batch_name = p_batch_name;
1387
1388
1389 l_msg_count NUMBER;
1390 l_msg_data VARCHAR2(2000);
1391
1392 -- new cursor for DA-ENH
1393
1394
1395 CURSOR assign_org_name_cur IS
1396 SELECT name
1397 FROM hr_all_organization_units haou,
1398 per_assignments_f paf
1399 WHERE haou.organization_id = paf.organization_id
1400 AND paf.assignment_id = l_assignment_id
1401 AND TRUNC(SYSDATE) BETWEEN paf.effective_start_date AND paf.effective_end_date;
1402
1403
1404
1405 BEGIN
1406
1407 g_error_api_path := '';
1408 fnd_msg_pub.initialize;
1409 errbuf := '';
1410 open app_dates_c;
1411 fetch app_dates_c into
1412 l_person_id,
1413 l_assignment_id,
1414 l_begin_date,
1415 l_end_date;
1416 if app_dates_c%NOTFOUND then
1417 close app_dates_c;
1418 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1419 else
1420 close app_dates_c;
1421 end if;
1422
1423 open app_header_c;
1424 fetch app_header_c into
1425 l_full_name,
1426 l_employee_number,
1427 l_assignment_number;
1428 if app_header_c%NOTFOUND then
1429 close app_header_c;
1430 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1431 else
1432 close app_header_c;
1433 end if;
1434
1435 open assign_org_name_cur;
1436 fetch assign_org_name_cur into l_assignment_organization;
1437 close assign_org_name_cur;
1438
1439 open app_comments_c;
1440 fetch app_comments_c into
1441 l_batch_comments, l_currency_code; -- Introduced currency_code for bug fix 2916848
1442 if app_comments_c%NOTFOUND then
1443 close app_comments_c;
1444 l_batch_comments :='No comments found.';
1445 else
1446 close app_comments_c;
1447 end if;
1448
1449 retcode := 0;
1450
1451 EXCEPTION
1452 WHEN OTHERS THEN
1453 g_error_api_path := 'GET_APPROVAL_HEADER:'||g_error_api_path;
1454 fnd_msg_pub.get(p_msg_index => FND_MSG_PUB.G_FIRST,
1455 p_encoded => FND_API.G_FALSE,
1456 p_data => l_msg_data,
1457 p_msg_index_out => l_msg_count);
1458 errbuf := l_msg_data || fnd_global.local_chr(10) || g_error_api_path;
1459 retcode := 2;
1460
1461 END;
1462 --------------------------------------------------------------------
1463
1464
1465 ----------P R O C E D U R E: LOAD_APPROVAL_TABLE -------------------------------
1466 --
1467 --
1468 -- Purpose: This procedure is called by the find batches screen of the
1469 -- distribution transfers approval form. The purpose is to
1470 -- select adjusted lines waiting approval for a particular batch
1471 -- name. For each line selected, the line is passed to the
1472 -- procedure approval_sumlines to sum the line by unique
1473 -- GL/POETA account with other lines in the same batch.
1474 --
1475 ----------------------------------------------------------------------------------
1476
1477 PROCEDURE load_approval_table(errbuf OUT NOCOPY VARCHAR2,
1478 retcode OUT NOCOPY VARCHAR2,
1479 p_batch_name IN VARCHAR2,
1480 p_run_id IN NUMBER,
1481 p_business_group_id IN Number,
1482 p_set_of_books_id IN NUMBER) IS
1483
1484 l_msg_count NUMBER;
1485 l_msg_data VARCHAR2(2000);
1486
1487 no_records_found EXCEPTION;
1488
1489 -- added following variable and cursor for DA-ENH
1490 l_adjust_by varchar2(1);
1491 cursor show_elements is
1492 select adjust_by
1493 from psp_adjustment_control_table
1494 where adjustment_batch_name = p_batch_name;
1495
1496 -- Introduced the following for bug fix 2890110
1497 CURSOR adj_set_cur IS
1498 SELECT adj_set_number, SUM(distribution_sum)
1499 FROM psp_temp_dest_sumlines ptdl
1500 WHERE ptdl.run_id = p_run_id
1501 AND ptdl.business_group_id = p_business_group_id
1502 AND ptdl.set_of_books_id = p_set_of_books_id
1503 AND ptdl.dr_cr_flag = 'D'
1504 GROUP BY adj_set_number;
1505
1506 l_adj_set_number NUMBER;
1507 l_adj_set_total NUMBER; -- Corrected width from (15, 2) to 30 for bug fix 2916848
1508 -- End of bug fix 2890110
1509 -- added for 4992668
1510 l_dff_grouping_option varchar2(1) DEFAULT psp_general.get_act_dff_grouping_option(p_business_group_id);
1511
1512 BEGIN
1513
1514 g_error_api_path := '';
1515 fnd_msg_pub.initialize;
1516 errbuf := '';
1517 g_run_id := p_run_id;
1518 if l_dff_grouping_option = 'Y' then
1519 -- added for DA-ENH
1520 open show_elements;
1521 fetch show_elements into l_adjust_by;
1522 close show_elements;
1523 if l_adjust_by = 'E' then
1524 INSERT into psp_temp_dest_sumlines (
1525 acct_group_id, --- added four fields for DA-ENH
1526 adj_set_number,
1527 original_line_flag,
1528 line_number,
1529 element_type_id,
1530 gl_code_combination_id,
1531 project_id,
1532 expenditure_organization_id,
1533 expenditure_type,
1534 task_id,
1535 award_id,
1536 dr_cr_flag,
1537 distribution_sum,
1538 distribution_percent,
1539 run_id,
1540 set_of_books_id,
1541 business_group_id,
1542 attribute_category, -- Introduced DFF columns for bug fix 2908859
1543 attribute1,
1544 attribute2,
1545 attribute3,
1546 attribute4,
1547 attribute5,
1548 attribute6,
1549 attribute7,
1550 attribute8,
1551 attribute9,
1552 attribute10)
1553 SELECT
1554 MIN(ROWNUM), --- added for DA-ENH
1555 adj_set_number, --- added for DA-ENH
1556 original_line_flag, --- moved from below for DA-ENH...
1557 line_number, --- added for DA-ENH
1558 element_type_id, --- added for DA-ENH
1559 gl_code_combination_id,
1560 project_id,
1561 expenditure_organization_id,
1562 expenditure_type,
1563 task_id,
1564 award_id,
1565 -- dr_cr_flag, Commented for Bug 3625667
1566 DECODE(sign(sum(decode(dr_cr_flag, 'D',distribution_amount, -distribution_amount))),-1,'C','D') dr_cr_flag,
1567 sum(decode(dr_cr_flag, 'D',distribution_amount, -distribution_amount)),
1568 percent, ---- added for DA-ENH
1569 p_run_id,
1570 p_set_of_books_id,
1571 p_business_group_id,
1572 attribute_category, -- Introduced DFF columns for bug fix 2908859
1573 attribute1,
1574 attribute2,
1575 attribute3,
1576 attribute4,
1577 attribute5,
1578 attribute6,
1579 attribute7,
1580 attribute8,
1581 attribute9,
1582 attribute10
1583 FROM psp_adjustment_lines
1584 WHERE batch_name = p_batch_name
1585 and business_group_id = p_business_group_id
1586 and set_of_books_id = p_set_of_books_id
1587 GROUP by adj_set_number,
1588 original_line_flag,
1589 line_number,
1590 element_type_id,
1591 gl_code_combination_id,
1592 project_id,
1593 expenditure_organization_id,
1594 expenditure_type,
1595 task_id,
1596 award_id,
1597 -- dr_cr_flag, Commented for Bug 3625667
1598 percent,
1599 attribute_category, -- Introduced DFF columns for bug fix 2908859
1600 attribute1,
1601 attribute2,
1602 attribute3,
1603 attribute4,
1604 attribute5,
1605 attribute6,
1606 attribute7,
1607 attribute8,
1608 attribute9,
1609 attribute10;
1610 elsif l_adjust_by = 'G' then
1611 INSERT into psp_temp_dest_sumlines (
1612 acct_group_id, --- added four fields for DA-ENH
1613 adj_set_number,
1614 original_line_flag,
1615 line_number,
1616 element_group_id,
1617 gl_code_combination_id,
1618 project_id,
1619 expenditure_organization_id,
1620 expenditure_type,
1621 task_id,
1622 award_id,
1623 dr_cr_flag,
1624 distribution_sum,
1625 distribution_percent,
1626 run_id,
1627 set_of_books_id,
1628 business_group_id,
1629 attribute_category, -- Introduced DFF columns for bug fix 2908859
1630 attribute1,
1631 attribute2,
1632 attribute3,
1633 attribute4,
1634 attribute5,
1635 attribute6,
1636 attribute7,
1637 attribute8,
1638 attribute9,
1639 attribute10)
1640 SELECT
1641 MIN(ROWNUM), --- added for DA-ENH
1642 a.adj_set_number, --- added for DA-ENH
1643 a.original_line_flag, --- moved from below for DA-ENH...
1644 a.line_number, --- added for DA-ENH
1645 b.element_group_id, --- added for DA-ENH
1646 a.gl_code_combination_id,
1647 a.project_id,
1648 a.expenditure_organization_id,
1649 a.expenditure_type,
1650 a.task_id,
1651 a.award_id,
1652 -- a.dr_cr_flag, Commented for bug 3625667
1653 decode(sign(sum(decode(a.dr_cr_flag, 'D',distribution_amount, -distribution_amount))),-1,'C','D') dr_cr_flag,
1654 sum(decode(dr_cr_flag, 'D',distribution_amount, -distribution_amount)),
1655 a.percent, ---- added for DA-ENH
1656 p_run_id,
1657 p_set_of_books_id,
1658 p_business_group_id,
1659 a.attribute_category, -- Introduced DFF columns for bug fix 2908859
1660 a.attribute1,
1661 a.attribute2,
1662 a.attribute3,
1663 a.attribute4,
1664 a.attribute5,
1665 a.attribute6,
1666 a.attribute7,
1667 a.attribute8,
1668 a.attribute9,
1669 a.attribute10
1670 FROM psp_adjustment_lines a,
1671 psp_group_element_list b
1672 WHERE a.batch_name = p_batch_name
1673 and a.business_group_id = p_business_group_id
1674 and a.set_of_books_id = p_set_of_books_id
1675 and b.element_type_id(+) = a.element_type_id
1676 GROUP by a.adj_set_number,
1677 a.original_line_flag,
1678 a.line_number,
1679 b.element_group_id,
1680 a.gl_code_combination_id,
1681 a.project_id,
1682 a.expenditure_organization_id,
1683 a.expenditure_type,
1684 a.task_id,
1685 a.award_id,
1686 -- a.dr_cr_flag, Commented for bug 3625667
1687 a.percent,
1688 a.attribute_category, -- Introduced DFF columns for bug fix 2908859
1689 a.attribute1,
1690 a.attribute2,
1691 a.attribute3,
1692 a.attribute4,
1693 a.attribute5,
1694 a.attribute6,
1695 a.attribute7,
1696 a.attribute8,
1697 a.attribute9,
1698 a.attribute10;
1699 else
1700 INSERT into psp_temp_dest_sumlines (
1701 acct_group_id, --- added four fields for DA-ENH
1702 adj_set_number,
1703 original_line_flag,
1704 line_number,
1705 gl_code_combination_id,
1706 project_id,
1707 expenditure_organization_id,
1708 expenditure_type,
1709 task_id,
1710 award_id,
1711 dr_cr_flag,
1712 distribution_sum,
1713 distribution_percent,
1714 run_id,
1715 set_of_books_id,
1716 business_group_id,
1717 attribute_category, -- Introduced DFF columns for bug fix 2908859
1718 attribute1,
1719 attribute2,
1720 attribute3,
1721 attribute4,
1722 attribute5,
1723 attribute6,
1724 attribute7,
1725 attribute8,
1726 attribute9,
1727 attribute10)
1728 SELECT
1729 MIN(ROWNUM), --- added for DA-ENH
1730 adj_set_number, --- added for DA-ENH
1731 original_line_flag, --- moved from below for DA-ENH...
1732 line_number, --- added for DA-ENH
1733 gl_code_combination_id,
1734 project_id,
1735 expenditure_organization_id,
1736 expenditure_type,
1737 task_id,
1738 award_id,
1739 -- dr_cr_flag, Commented for Bug 3625667
1740 decode(sign(sum(decode(dr_cr_flag, 'D',distribution_amount, -distribution_amount))),-1,'C','D') dr_cr_flag,
1741 sum(decode(dr_cr_flag, 'D',distribution_amount, -distribution_amount)),
1742 percent, ---- added for DA-ENH
1743 p_run_id,
1744 p_set_of_books_id,
1745 p_business_group_id,
1746 attribute_category, -- Introduced DFF columns for bug fix 2908859
1747 attribute1,
1748 attribute2,
1749 attribute3,
1750 attribute4,
1751 attribute5,
1752 attribute6,
1753 attribute7,
1754 attribute8,
1755 attribute9,
1756 attribute10
1757 FROM psp_adjustment_lines
1758 WHERE batch_name = p_batch_name
1759 and business_group_id = p_business_group_id
1760 and set_of_books_id = p_set_of_books_id
1761 GROUP by adj_set_number,
1762 original_line_flag,
1763 line_number,
1764 gl_code_combination_id,
1765 project_id,
1766 expenditure_organization_id,
1767 expenditure_type,
1768 task_id,
1769 award_id,
1770 -- dr_cr_flag, Commented for Bug 3625667
1771 percent,
1772 attribute_category, -- Introduced DFF columns for bug fix 2908859
1773 attribute1,
1774 attribute2,
1775 attribute3,
1776 attribute4,
1777 attribute5,
1778 attribute6,
1779 attribute7,
1780 attribute8,
1781 attribute9,
1782 attribute10;
1783 end if;
1784 else ---- l_dff option = 'N'
1785 open show_elements;
1786 fetch show_elements into l_adjust_by;
1787 close show_elements;
1788 if l_adjust_by = 'E' then
1789 INSERT into psp_temp_dest_sumlines (
1790 acct_group_id, --- added four fields for DA-ENH
1791 adj_set_number,
1792 original_line_flag,
1793 line_number,
1794 element_type_id,
1795 gl_code_combination_id,
1796 project_id,
1797 expenditure_organization_id,
1798 expenditure_type,
1799 task_id,
1800 award_id,
1801 dr_cr_flag,
1802 distribution_sum,
1803 distribution_percent,
1804 run_id,
1805 set_of_books_id,
1806 business_group_id)
1807 SELECT
1808 MIN(ROWNUM), --- added for DA-ENH
1809 adj_set_number, --- added for DA-ENH
1810 original_line_flag, --- moved from below for DA-ENH...
1811 line_number, --- added for DA-ENH
1812 element_type_id, --- added for DA-ENH
1813 gl_code_combination_id,
1814 project_id,
1815 expenditure_organization_id,
1816 expenditure_type,
1817 task_id,
1818 award_id,
1819 -- dr_cr_flag, Commented for Bug 3625667
1820 DECODE(sign(sum(decode(dr_cr_flag, 'D',distribution_amount, -distribution_amount))),-1,'C','D') dr_cr_flag,
1821 sum(decode(dr_cr_flag, 'D',distribution_amount, -distribution_amount)),
1822 percent, ---- added for DA-ENH
1823 p_run_id,
1824 p_set_of_books_id,
1825 p_business_group_id
1826 FROM psp_adjustment_lines
1827 WHERE batch_name = p_batch_name
1828 and business_group_id = p_business_group_id
1829 and set_of_books_id = p_set_of_books_id
1830 GROUP by adj_set_number,
1831 original_line_flag,
1832 line_number,
1833 element_type_id,
1834 gl_code_combination_id,
1835 project_id,
1836 expenditure_organization_id,
1837 expenditure_type,
1838 task_id,
1839 award_id,
1840 -- dr_cr_flag, Commented for Bug 3625667
1841 percent;
1842 elsif l_adjust_by = 'G' then
1843 INSERT into psp_temp_dest_sumlines (
1844 acct_group_id, --- added four fields for DA-ENH
1845 adj_set_number,
1846 original_line_flag,
1847 line_number,
1848 element_group_id,
1849 gl_code_combination_id,
1850 project_id,
1851 expenditure_organization_id,
1852 expenditure_type,
1853 task_id,
1854 award_id,
1855 dr_cr_flag,
1856 distribution_sum,
1857 distribution_percent,
1858 run_id,
1859 set_of_books_id,
1860 business_group_id)
1861 SELECT
1862 MIN(ROWNUM), --- added for DA-ENH
1863 a.adj_set_number, --- added for DA-ENH
1864 a.original_line_flag, --- moved from below for DA-ENH...
1865 a.line_number, --- added for DA-ENH
1866 b.element_group_id, --- added for DA-ENH
1867 a.gl_code_combination_id,
1868 a.project_id,
1869 a.expenditure_organization_id,
1870 a.expenditure_type,
1871 a.task_id,
1872 a.award_id,
1873 -- a.dr_cr_flag, Commented for bug 3625667
1874 decode(sign(sum(decode(a.dr_cr_flag, 'D',distribution_amount, -distribution_amount))),-1,'C','D') dr_cr_flag,
1875 sum(decode(dr_cr_flag, 'D',distribution_amount, -distribution_amount)),
1876 a.percent, ---- added for DA-ENH
1877 p_run_id,
1878 p_set_of_books_id,
1879 p_business_group_id
1880 FROM psp_adjustment_lines a,
1881 psp_group_element_list b
1882 WHERE a.batch_name = p_batch_name
1883 and a.business_group_id = p_business_group_id
1884 and a.set_of_books_id = p_set_of_books_id
1885 and b.element_type_id(+) = a.element_type_id
1886 GROUP by a.adj_set_number,
1887 a.original_line_flag,
1888 a.line_number,
1889 b.element_group_id,
1890 a.gl_code_combination_id,
1891 a.project_id,
1892 a.expenditure_organization_id,
1893 a.expenditure_type,
1894 a.task_id,
1895 a.award_id,
1896 -- a.dr_cr_flag, Commented for bug 3625667
1897 a.percent;
1898 else
1899 INSERT into psp_temp_dest_sumlines (
1900 acct_group_id, --- added four fields for DA-ENH
1901 adj_set_number,
1902 original_line_flag,
1903 line_number,
1904 gl_code_combination_id,
1905 project_id,
1906 expenditure_organization_id,
1907 expenditure_type,
1908 task_id,
1909 award_id,
1910 dr_cr_flag,
1911 distribution_sum,
1912 distribution_percent,
1913 run_id,
1914 set_of_books_id,
1915 business_group_id)
1916 SELECT
1917 MIN(ROWNUM), --- added for DA-ENH
1918 adj_set_number, --- added for DA-ENH
1919 original_line_flag, --- moved from below for DA-ENH...
1920 line_number, --- added for DA-ENH
1921 gl_code_combination_id,
1922 project_id,
1923 expenditure_organization_id,
1924 expenditure_type,
1925 task_id,
1926 award_id,
1927 -- dr_cr_flag, Commented for Bug 3625667
1928 decode(sign(sum(decode(dr_cr_flag, 'D',distribution_amount, -distribution_amount))),-1,'C','D') dr_cr_flag,
1929 sum(decode(dr_cr_flag, 'D',distribution_amount, -distribution_amount)),
1930 percent, ---- added for DA-ENH
1931 p_run_id,
1932 p_set_of_books_id,
1933 p_business_group_id
1934 FROM psp_adjustment_lines
1935 WHERE batch_name = p_batch_name
1936 and business_group_id = p_business_group_id
1937 and set_of_books_id = p_set_of_books_id
1938 GROUP by adj_set_number,
1939 original_line_flag,
1940 line_number,
1941 gl_code_combination_id,
1942 project_id,
1943 expenditure_organization_id,
1944 expenditure_type,
1945 task_id,
1946 award_id,
1947 -- dr_cr_flag, Commented for Bug 3625667
1948 percent;
1949 end if;
1950 end if;
1951 if sql%rowcount = 0 then
1952 raise no_records_found;
1953 end if;
1954 ----- New code ends here for DA-ENH
1955
1956 -- Introduced for bug fix 2890110
1957 OPEN adj_set_cur;
1958 LOOP
1959 FETCH adj_set_cur INTO l_adj_set_number, l_adj_set_total;
1960 EXIT WHEN adj_set_cur%NOTFOUND;
1961 --- changed g_precision to 2.. below...for 4992668
1962 UPDATE psp_temp_dest_sumlines ptdl
1963 SET distribution_percent = (ROUND((100 * ABS(distribution_sum) / l_adj_set_total), 2))
1964 WHERE ptdl.run_id = p_run_id
1965 AND ptdl.business_group_id = p_business_group_id
1966 AND ptdl.set_of_books_id = p_set_of_books_id
1967 AND ptdl.adj_set_number = l_adj_set_number;
1968 END LOOP;
1969 CLOSE adj_set_cur;
1970 UPDATE psp_temp_dest_sumlines ptdl
1971 SET distribution_percent = (-1 * distribution_percent)
1972 WHERE ptdl.run_id = p_run_id
1973 AND ptdl.business_group_id = p_business_group_id
1974 AND ptdl.set_of_books_id = p_set_of_books_id
1975 AND ptdl.dr_cr_flag = 'C';
1976 -- End of bug fix 2890110
1977
1978 --- bug 4992668
1979 psp_wf_adj_custom.dff_for_approver(p_batch_name,
1980 p_run_id,
1981 p_business_group_id,
1982 p_set_of_books_id);
1983
1984 retcode := 0;
1985 EXCEPTION
1986 WHEN NO_RECORDS_FOUND THEN
1987 retcode := 1;
1988
1989 WHEN OTHERS THEN
1990 g_error_api_path := 'LOAD_TABLE:'||g_error_api_path;
1991 fnd_msg_pub.get(p_msg_index => FND_MSG_PUB.G_FIRST,
1992 p_encoded => FND_API.G_FALSE,
1993 p_data => l_msg_data,
1994 p_msg_index_out => l_msg_count);
1995 errbuf := l_msg_data || fnd_global.local_chr(10) || g_error_api_path;
1996 retcode := 2;
1997 END;
1998 -------------------------------------------------------------
1999
2000
2001
2002 ----------P R O C E D U R E: INSERT_ADJ_LINES -------------------------------
2003 --
2004 --
2005 -- Purpose: This procedure is called by procedure generate_lines described below.
2006 -- The purpose is to insert lines into the table psp_adjustment_lines.
2007 -- These lines are both the reversal and adjusted lines.
2008 --
2009 ----------------------------------------------------------------------------------
2010
2011 procedure insert_adj_lines(p_person_id IN NUMBER,
2012 p_assignment_id IN NUMBER,
2013 p_element_type_id IN NUMBER,
2014 p_batch_name IN VARCHAR2,
2015 p_distribution_date IN DATE,
2016 p_effective_date IN DATE,
2017 p_distribution_amount IN NUMBER,
2018 p_dr_cr_flag IN VARCHAR2,
2019 p_payroll_control_id IN NUMBER,
2020 p_time_period_id IN NUMBER,
2021 p_status_code IN VARCHAR2,
2022 p_set_of_books_id IN NUMBER,
2023 p_gl_code_combination_id IN NUMBER,
2024 p_project_id IN NUMBER,
2025 p_expenditure_organization_id IN NUMBER,
2026 p_expenditure_type IN VARCHAR2,
2027 p_task_id IN NUMBER,
2028 p_award_id IN NUMBER,
2029 p_reversal_entry_flag IN VARCHAR2,
2030 p_original_line_flag IN VARCHAR2,
2031 p_distribution_percent In NUMBER,
2032 p_orig_source_type IN VARCHAR2,
2033 p_orig_line_id IN NUMBER,
2034 p_business_group_id IN NUMBER,
2035 p_return_status OUT NOCOPY VARCHAR2) IS
2036 begin
2037
2038 insert into psp_adjustment_lines(adjustment_line_id,
2039 person_id,
2040 assignment_id,
2041 element_type_id,
2042 distribution_date,
2043 effective_date,
2044 distribution_amount,
2045 dr_cr_flag,
2046 payroll_control_id,
2047 source_type,
2048 source_code,
2049 time_period_id,
2050 batch_name,
2051 status_code,
2052 set_of_books_id,
2053 gl_code_combination_id,
2054 project_id,
2055 expenditure_organization_id,
2056 expenditure_type,
2057 task_id,
2058 award_id,
2059 suspense_org_account_id,
2060 suspense_reason_code,
2061 effort_report_id,
2062 version_num,
2063 summary_line_id,
2064 reversal_entry_flag,
2065 original_line_flag,
2066 user_defined_field,
2067 percent,
2068 orig_source_type,
2069 orig_line_id,
2070 business_group_id,
2071 attribute_category,
2072 attribute1,
2073 attribute2,
2074 attribute3,
2075 attribute4,
2076 attribute5,
2077 attribute6,
2078 attribute7,
2079 attribute8,
2080 attribute9,
2081 attribute10,
2082 attribute11,
2083 attribute12,
2084 attribute13,
2085 attribute14,
2086 attribute15,
2087 last_update_date,
2088 last_updated_by,
2089 last_update_login,
2090 created_by,
2091 creation_date)
2092 values(psp_adjustment_lines_s.nextval,
2093 p_person_id,
2094 p_assignment_id,
2095 p_element_type_id,
2096 p_distribution_date,
2097 p_effective_date,
2098 p_distribution_amount,
2099 p_dr_cr_flag,
2100 p_payroll_control_id,
2101 'A',
2102 'Adjustments',
2103 p_time_period_id,
2104 p_batch_name,
2105 p_status_code,
2106 p_set_of_books_id,
2107 p_gl_code_combination_id,
2108 p_project_id,
2109 p_expenditure_organization_id,
2110 p_expenditure_type,
2111 p_task_id,
2112 p_award_id,
2113 null,
2114 null,
2115 null,
2116 null,
2117 null,
2118 p_reversal_entry_flag,
2119 p_original_line_flag,
2120 null,
2121 p_distribution_percent,
2122 p_orig_source_type,
2123 p_orig_line_id,
2124 p_business_group_id,
2125 null,
2126 null,
2127 null,
2128 null,
2129 null,
2130 null,
2131 null,
2132 null,
2133 null,
2134 null,
2135 null,
2136 null,
2137 null,
2138 null,
2139 null,
2140 null,
2141 sysdate,
2142 fnd_global.user_id,
2143 fnd_global.login_id,
2144 fnd_global.user_id,
2145 sysdate);
2146 p_return_status := fnd_api.g_ret_sts_success;
2147
2148 EXCEPTION
2149 WHEN OTHERS THEN
2150 g_error_api_path := 'INSERT_ADJ_LINES:Batch Name '||p_batch_name||':'||g_error_api_path;
2151 p_return_status := fnd_api.g_ret_sts_unexp_error;
2152
2153 end;
2154 --------------------------------------------------------------------
2155
2156
2157 ----------P R O C E D U R E: INSERT_ADJUSTMENT_CONTROL -------------------------------
2158 --
2159 --
2160 -- Purpose: This procedure is called by procedure generate_lines described below.
2161 -- The purpose is to insert a new line in the psp_adjustment_control_table
2162 -- for the batch name created by the user in the distribution transfers
2163 -- form.
2164 --
2165 ----------------------------------------------------------------------------------
2166
2167 procedure insert_adjustment_control(p_batch_name IN VARCHAR2,
2168 p_batch_comments IN VARCHAR2,
2169 p_return_status OUT NOCOPY VARCHAR2,
2170 p_gl_posting_override_date IN DATE DEFAULT NULL,
2171 -- Fixed 1087529
2172 p_person_id IN NUMBER,
2173 p_assignment_id IN NUMBER,
2174 -- p_element_type_id IN NUMBER, commented for DA-ENH
2175 p_distribution_start_date IN DATE,
2176 p_distribution_end_date IN DATE,
2177 p_currency_code IN VARCHAR2, -- Introduced for bug fix 2916848
2178 p_business_group_id IN NUMBER,
2179 p_set_of_books_id IN NUMBER,
2180 p_adjust_by IN VARCHAR2) is
2181
2182 begin
2183
2184 insert into psp_adjustment_control_table(
2185 adjustment_batch_name,
2186 comments,
2187 gl_posting_override_date,
2188 last_update_date,
2189 last_updated_by,
2190 last_update_login,
2191 created_by,
2192 creation_date,
2193 person_id,
2194 assignment_id,
2195 ---element_type_id, commented for DA-ENH
2196 distribution_start_date,
2197 distribution_end_date,
2198 currency_code, -- Introduced for bug fix 2916848
2199 business_group_id,
2200 set_of_books_id,
2201 adjust_by) --- added for DA-ENH
2202 values(
2203 p_batch_name,
2204 p_batch_comments,
2205 p_gl_posting_override_date,
2206 sysdate,
2207 fnd_global.user_id,
2208 fnd_global.login_id,
2209 fnd_global.user_id,
2210 sysdate,
2211 p_person_id,
2212 p_assignment_id,
2213 --- p_element_type_id, commented for DA-ENH
2214 p_distribution_start_date,
2215 p_distribution_end_date,
2216 p_currency_code, -- Introduced for bug fix 2916848
2217 p_business_group_id,
2218 p_set_of_books_id,
2219 p_adjust_by); --- added for DA-ENH
2220 p_return_status := fnd_api.g_ret_sts_success;
2221
2222 EXCEPTION
2223 --************************************************************
2224 WHEN DUP_VAL_ON_INDEX THEN
2225 p_return_status := g_constraint_violation;
2226 --************************************************************
2227
2228 WHEN OTHERS THEN
2229 g_error_api_path := 'INSERT_ADJUSTMENT_CONTROL:Batch Name '||p_batch_name||':'||g_error_api_path;
2230 p_return_status := fnd_api.g_ret_sts_unexp_error;
2231 end;
2232 -----------------------------------------------------------------------
2233
2234
2235 ----------P R O C E D U R E: UPDATE_ADJUSTMENT_CONTROL -------------------------------
2236 --
2237 --
2238 -- Purpose: This procedure is called by the distributions transfers
2239 -- approval form after the approver submits the batch to the summarize
2240 -- and transfer concurrent process. The purpose is to update
2241 -- the psp_adjustment_control_table with the approver id
2242 -- of the approver of the batch.
2243 --
2244 --
2245 ----------------------------------------------------------------------------------
2246
2247 procedure update_adjustment_ctrl_comment(errbuf OUT NOCOPY VARCHAR2,
2248 retcode OUT NOCOPY VARCHAR2,
2249 p_batch_name IN VARCHAR2,
2250 p_comments IN VARCHAR2)
2251 IS
2252
2253 l_msg_count NUMBER;
2254 l_msg_data VARCHAR2(2000);
2255
2256 begin
2257 g_error_api_path := '';
2258 fnd_msg_pub.initialize;
2259 errbuf := '';
2260 update psp_adjustment_control_table
2261 set comments = p_comments,
2262 approver_id = FND_GLOBAL.USER_ID, -- 1087529
2263 approval_date = SYSDATE -- Added to fix bug 1661405. approval_date is a new column added to the table
2264 where adjustment_batch_name = p_batch_name;
2265
2266 --Modified by Rashmi to update psp_payroll_control table.
2267 update psp_payroll_controls set status_code = 'N'
2268 where batch_name = p_batch_name and source_type = 'A'
2269 and status_code = 'C';
2270
2271 commit;
2272 retcode := 0;
2273
2274 EXCEPTION
2275 WHEN OTHERS THEN
2276 g_error_api_path := 'UPDATE_ADJUSTMENT_CONTROL:'||g_error_api_path;
2277 fnd_msg_pub.get(p_msg_index => FND_MSG_PUB.G_FIRST,
2278 p_encoded => FND_API.G_FALSE,
2279 p_data => l_msg_data,
2280 p_msg_index_out => l_msg_count);
2281 errbuf := l_msg_data || fnd_global.local_chr(10) || g_error_api_path;
2282 retcode := 2;
2283
2284 end;
2285 ---------------------------------------------------------------------------------
2286
2287
2288 ----------P R O C E D U R E: UPDATE_PAYROLL_CONTROL -------------------------------
2289 --
2290 --
2291 -- Purpose: This procedure is called by procedure generate_lines described below.
2292 -- The purpose is to insert a record into psp_payroll_controls for
2293 -- every time_period_id that exists within the time frame of the
2294 -- adjusted lines for the batch. The psp_adjustment_lines table is
2295 -- updated with the new payroll_control_id for the time_period_id.
2296 --
2297 ----------------------------------------------------------------------------------
2298
2299 procedure update_payroll_control(p_batch_name IN VARCHAR2,
2300 p_payroll_control_id IN NUMBER,
2301 p_business_group_id IN NUMBER,
2302 p_set_of_books_id IN NUMBER,
2303 p_currency_code IN VARCHAR2, -- Introduced for bug fix 2916848
2304 p_return_status OUT NOCOPY VARCHAR2,
2305 p_gl_posting_override_date IN DATE DEFAULT NULL) is
2306
2307 l_payroll_control_id NUMBER(10);
2308 l_time_period_id NUMBER(15);
2309 l_tot_dr NUMBER;
2310 l_tot_cr NUMBER;
2311 l_payroll_id NUMBER; /* Bug 1677534 */
2312
2313
2314 cursor time_periods_c is
2315 select distinct palh.time_period_id
2316 from psp_adjustment_lines palh
2317 where palh.batch_name = p_batch_name
2318 and palh.business_group_id = p_business_group_id
2319 and palh.set_of_books_id = p_set_of_books_id;
2320
2321
2322 /*********************************************
2323 For Bug 2252881 : Modifying the Cusor tot_dr_c to include tot_cr_c conitions also
2324 and to be able to select both teh DR amount and cr amount
2325 cursor tot_dr_c is
2326 select sum(distribution_amount)
2327 from psp_adjustment_lines
2328 where batch_name = p_batch_name
2329 and time_period_id = l_time_period_id
2330 and dr_cr_flag = 'D'
2331 and business_group_id = p_business_group_id
2332 and set_of_books_id = p_set_of_books_id
2333 group by batch_name, time_period_id, dr_cr_flag;
2334
2335 cursor tot_cr_c is
2336 select sum(distribution_amount)
2337 from psp_adjustment_lines
2338 where batch_name = p_batch_name
2339 and time_period_id = l_time_period_id
2340 and dr_cr_flag = 'C'
2341 and business_group_id = p_business_group_id
2342 and set_of_books_id = p_set_of_books_id
2343 group by batch_name, time_period_id, dr_cr_flag;
2344 *******************************************/
2345 CURSOR tot_dr_c
2346 IS
2347 SELECT SUM(DECODE(pal.dr_cr_flag,'D',pal.distribution_amount,0)) l_total_dr,
2348 SUM(DECODE(pal.dr_cr_flag,'C',pal.distribution_amount,0)) l_total_cr
2349 FROM psp_adjustment_lines pal
2350 WHERE pal.batch_name = p_batch_name
2351 and pal.time_period_id = l_time_period_id;
2352
2353 /* Bug 1677534 */
2354 cursor get_payroll_id is
2355 select payroll_id
2356 from per_time_periods
2357 where time_period_id = l_time_period_id;
2358
2359 begin
2360
2361 open time_periods_c;
2362 fetch time_periods_c into l_time_period_id;
2363 if time_periods_c%NOTFOUND then
2364 close time_periods_c;
2365 g_error_api_path := 'Error opening cursor time_periods_c: '||g_error_api_path;
2366 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2367 end if;
2368
2369
2370 open tot_dr_c;
2371 fetch tot_dr_c into l_tot_dr,l_tot_cr;
2372 -- if tot_dr_c%NOTFOUND then --Commented for bug 2252881
2373 close tot_dr_c;
2374 /*****************************
2375 Commented for Bug 2252881 : Both the credit and debit amount are being selected in the cursor tot_dr_c
2376 l_tot_dr := 0;
2377 else
2378 close tot_dr_c;
2379 end if;
2380
2381 open tot_cr_c;
2382 fetch tot_cr_c into l_tot_cr;
2383 if tot_cr_c%NOTFOUND then
2384 close tot_cr_c;
2385 l_tot_cr := 0;
2386 else
2387 close tot_cr_c;
2388 end if;
2389 ******************************/
2390
2391 /* Bug 1677534 */
2392 open get_payroll_id;
2393 fetch get_payroll_id into l_payroll_id;
2394 close get_payroll_id;
2395
2396 --Modified the code by Rashmi to assign status = 'C' (for Creation).
2397
2398 insert into psp_payroll_controls(
2399 payroll_control_id,
2400 payroll_action_id,
2401 payroll_source_code,
2402 source_type,
2403 payroll_id,
2404 time_period_id,
2405 batch_name,
2406 dist_dr_amount,
2407 dist_cr_amount,
2408 status_code,
2409 last_update_date,
2410 last_updated_by,
2411 last_update_login,
2412 created_by,
2413 creation_date,
2414 run_id,
2415 GL_POSTING_OVERRIDE_DATE,
2416 GMS_POSTING_OVERRIDE_DATE,
2417 business_group_id,
2418 set_of_books_id,
2419 -- Introduced for bug fix 2916848
2420 currency_code,
2421 exchange_rate_type)
2422 values(
2423 p_payroll_control_id,
2424 1,
2425 'Adjustments',
2426 'A',
2427 l_payroll_id, /* 1, --Bug 1677534 */
2428 l_time_period_id,
2429 p_batch_name,
2430 l_tot_dr,
2431 l_tot_cr,
2432 'C',
2433 sysdate,
2434 fnd_global.user_id,
2435 fnd_global.login_id,
2436 fnd_global.user_id,
2437 sysdate,
2438 null,
2439 p_gl_posting_override_date,
2440 null,
2441 p_business_group_id,
2442 p_set_of_books_id,
2443 -- Introduced for bug fix 2916848
2444 p_currency_code,
2445 null);
2446
2447 loop
2448 fetch time_periods_c into l_time_period_id;
2449 if time_periods_c%NOTFOUND then
2450 close time_periods_c;
2451 exit;
2452 end if;
2453
2454 open tot_dr_c;
2455 fetch tot_dr_c into l_tot_dr,l_tot_cr;
2456 --if tot_dr_c%NOTFOUND then : Commented for bug 2252881
2457 close tot_dr_c;
2458 /******************************************
2459 For Bug 2252881 : Commented as the Total Credit and Debit amount is obtained from the cursor tot_dr_c
2460 l_tot_dr := 0;
2461 else
2462 close tot_dr_c;
2463 end if;
2464
2465 open tot_cr_c;
2466 fetch tot_cr_c into l_tot_cr;
2467 if tot_cr_c%NOTFOUND then
2468 close tot_cr_c;
2469 l_tot_cr := 0;
2470 else
2471 close tot_cr_c;
2472 end if;
2473 **********************************************/
2474
2475 /* Bug 1677534 */
2476 open get_payroll_id;
2477 fetch get_payroll_id into l_payroll_id;
2478 close get_payroll_id;
2479
2480 --Modified the code by Rashmi to assign status = 'C' (for Creation).
2481
2482 select psp_payroll_controls_s.nextval into l_payroll_control_id from dual;
2483 insert into psp_payroll_controls(
2484 payroll_control_id,
2485 payroll_action_id,
2486 payroll_source_code,
2487 source_type,
2488 payroll_id,
2489 time_period_id,
2490 batch_name,
2491 dist_dr_amount,
2492 dist_cr_amount,
2493 status_code,
2494 last_update_date,
2495 last_updated_by,
2496 last_update_login,
2497 created_by,
2498 creation_date,
2499 run_id,
2500 GL_POSTING_OVERRIDE_DATE,
2501 GMS_POSTING_OVERRIDE_DATE,
2502 business_group_id,
2503 set_of_books_id,
2504 -- Introduced for bug fix 2916848
2505 currency_code,
2506 exchange_rate_type)
2507 values(
2508 l_payroll_control_id,
2509 1,
2510 'Adjustments',
2511 'A',
2512 l_payroll_id, /* 1, --Bug 1677534 */
2513 l_time_period_id,
2514 p_batch_name,
2515 l_tot_dr,
2516 l_tot_cr,
2517 'C',
2518 sysdate,
2519 fnd_global.user_id,
2520 fnd_global.login_id,
2521 fnd_global.user_id,
2522 sysdate,
2523 null,
2524 p_gl_posting_override_date,
2525 null,
2526 p_business_group_id,
2527 p_set_of_books_id,
2528 -- Introduced for bug fix 2916848
2529 p_currency_code,
2530 null);
2531
2532 update psp_adjustment_lines
2533 set payroll_control_id = l_payroll_control_id
2534 where time_period_id = l_time_period_id
2535 and batch_name = p_batch_name
2536 and business_group_id = p_business_group_id
2537 and set_of_books_id = p_set_of_books_id;
2538 IF SQL%NOTFOUND THEN
2539 close time_periods_c;
2540 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2541 END IF;
2542
2543 end loop;
2544
2545 p_return_status := fnd_api.g_ret_sts_success;
2546
2547 EXCEPTION
2548 WHEN OTHERS THEN
2549 g_error_api_path := 'UPDATE_PAYROLL_CONTROL:Batch Name '||p_batch_name||':'||g_error_api_path;
2550 p_return_status := fnd_api.g_ret_sts_unexp_error;
2551 end;
2552 --------------------------------------------------------------------------------
2553
2554
2555 ----------P R O C E D U R E: UPDATE_EFFORT_REPORTS -------------------------------
2556 --
2557 --
2558 -- Purpose: This procedure is called by procedure generate_lines described below.
2559 -- The purpose is to update the table psp_effort_reports with a
2560 -- status code of 'S' for superceded for those effort reports with
2561 -- distribution lines that have beenadjusted.
2562 --
2563 ----------------------------------------------------------------------------------
2564
2565 procedure update_effort_reports(p_batch_name IN VARCHAR2,
2566 p_business_group_id IN NUMBER,
2567 p_set_of_books_id IN NUMBER,
2568 p_return_status OUT NOCOPY VARCHAR2) is
2569 /*** Commented the following for bug fix 2724110
2570 l_per_id NUMBER(9);
2571 l_dist_date DATE;
2572 l_template_id NUMBER(9);
2573 l_begin_date DATE;
2574 l_end_date DATE;
2575
2576 -- Modified for bug 1886429,added one more condition in cursor
2577 -- to filter the person_id on the basis of effort report element_type_id
2578 -- on 24-Jul-2001,by ddubey.
2579
2580
2581 cursor adj_effort_c is
2582 select distinct person_id, distribution_date
2583 from psp_adjustment_lines
2584 where batch_name = p_batch_name
2585 and business_group_id = p_business_group_id
2586 and set_of_books_id = p_set_of_books_id
2587 and element_type_id in (select element_type_id from psp_effort_report_elements
2588 where use_in_effort_report='Y');
2589
2590 / *****************************************************************
2591 Commenting the following cursor for Bug fix :2252881:
2592
2593 cursor effort_reports_c(p_per_id IN NUMBER) is
2594 select template_id
2595 from psp_effort_reports
2596 where person_id = p_per_id;
2597 ***************************************************************
2598 ** Fixed Bug 1021852
2599 ***************************************************************
2600 cursor effort_reports_c(p_per_id IN NUMBER) is
2601 select per.template_id
2602 from psp_effort_reports per
2603 where per.person_id = p_per_id
2604 and per.business_group_id = p_business_group_id
2605 and per.set_of_books_id = p_set_of_books_id
2606 and per.template_id in (select pert.template_id
2607 from psp_effort_report_templates pert
2608 where pert.report_type = 'N'
2609 -- and pert.person_id = p_per_id
2610 and pert.business_group_id = p_business_group_id
2611 and pert.set_of_books_id = p_set_of_books_id);
2612
2613 cursor effort_templates_c(p_template_id IN NUMBER) is
2614 select begin_date, end_date
2615 from psp_effort_report_templates
2616 where template_id = p_template_id and
2617 report_type='N'
2618 and business_group_id = p_business_group_id
2619 and set_of_books_id = p_set_of_books_id;
2620 ************************************************************End of comment for bug 2252881 * /
2621
2622 / * Following cursor is modified for bug 2252881 * /
2623 CURSOR effort_reports_c(p_per_id NUMBER)
2624 IS
2625 SELECT pert.template_id, pert.begin_date, pert.end_date
2626 FROM psp_effort_report_templates pert
2627 WHERE pert.report_type ='N'
2628 AND pert.template_id IN(
2629 SELECT per.template_id
2630 FROM psp_effort_reports per
2631 WHERE per.person_id = p_per_id
2632 AND per.business_group_id = p_business_group_id
2633 AND per.set_of_books_id = p_set_of_books_id
2634 );
2635 End of comment for bug fix 2724110 ***/
2636 begin
2637 /*** Commented for bug fix 2724110
2638 open adj_effort_c; loop
2639 fetch adj_effort_c into l_per_id,
2640 l_dist_date;
2641 if adj_effort_c%NOTFOUND then
2642 close adj_effort_c;
2643 exit;
2644 end if;
2645
2646 open effort_reports_c(l_per_id); loop
2647 fetch effort_reports_c into l_template_id,l_begin_date,l_end_date;
2648 if effort_reports_c%NOTFOUND then
2649 close effort_reports_c;
2650 exit;
2651 end if;
2652 / **********************
2653 Commented as the begin and end date has been obtained from the effort_reports_c : For bug 2252881
2654 open effort_templates_c(l_template_id);
2655 fetch effort_templates_c into l_begin_date,
2656 l_end_date;
2657 if effort_templates_c%NOTFOUND then
2658 close effort_templates_c;
2659 g_error_api_path := 'Error opening cursor effort_templates_c: '||g_error_api_path;
2660 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2661 end if;
2662 ******************************************* /
2663
2664 if l_dist_date between l_begin_date and l_end_date then
2665 / *************************************************************************** /
2666 / *04/27/99 Shu Lei Distribution Adjustment Enhanced Workflow * /
2667 / * Record the status code in previous_status_code before * /
2668 / * updating status_code to "S". * /
2669 / *************************************************************************** /
2670 UPDATE psp_effort_reports
2671 SET previous_status_code = status_code
2672 WHERE person_id = l_per_id
2673 AND template_id = l_template_id;
2674
2675 update psp_effort_reports
2676 set status_code = 'S'
2677 where person_id = l_per_id
2678 and template_id = l_template_id;
2679 end if;
2680 --close effort_templates_c; :For bug 2252881
2681
2682
2683 end loop; / * effort_reports_c * /
2684 end loop; / * adj_effort_c * /
2685 end of comment for bug fix 2724110 ***/
2686
2687 -- Introduced the following for bug fix 2724110
2688 UPDATE psp_effort_reports per
2689 SET per.previous_status_code = per.status_code,
2690 per.status_code = 'S'
2691 WHERE per.status_code <> 'S'
2692 AND per.business_group_id = p_business_group_id
2693 AND per.set_of_books_id = p_set_of_books_id
2694 AND (per.person_id, per.template_id) IN
2695 (SELECT pal.person_id, pert.template_id
2696 FROM psp_effort_report_templates pert,
2697 psp_effort_reports per2,
2698 psp_adjustment_lines pal
2699 WHERE pal.batch_name = p_batch_name
2700 AND pal.business_group_id = p_business_group_id
2701 AND pal.set_of_books_id = p_set_of_books_id
2702 AND pert.business_group_id = p_business_group_id
2703 AND pert.set_of_books_id = p_set_of_books_id
2704 AND pert.template_id = per2.template_id
2705 AND pert.report_type = 'N'
2706 AND per2.person_id = pal.person_id
2707 AND pal.distribution_date BETWEEN pert.begin_date
2708 AND pert.end_date
2709 AND pal.element_type_id IN
2710 (SELECT pere.element_type_id
2711 FROM psp_effort_report_elements pere
2712 WHERE pere.use_in_effort_report = 'Y'));
2713 -- End of bug fix 2724110
2714 p_return_status := fnd_api.g_ret_sts_success;
2715
2716 EXCEPTION
2717 WHEN OTHERS THEN
2718 g_error_api_path := 'UPDATE_EFFORT_REPORTS:Batch Name '||p_batch_name||':'||g_error_api_path;
2719 p_return_status := fnd_api.g_ret_sts_unexp_error;
2720 end;
2721 --------------------------------------------------------------------------------
2722
2723
2724
2725 ----------P R O C E D U R E: GENERATE_LINES -------------------------------
2726 --
2727 --
2728 -- Purpose: This procedure is called by the submit process of the distribution
2729 -- transfers form. The purpose is to create reversal lines and new
2730 -- adjusted lines from the distribution amounts/percents specified
2731 -- by the user in the adjusted lines block of distribution transfers
2732 -- form.
2733 -- Reversal lines are generated first, and then new adjusted lines
2734 -- are created. A call is made to insert_adj_lines to insert
2735 -- the lines in psp_adjustment_lines table.
2736 --
2737 --
2738 ----------------------------------------------------------------------------------
2739
2740 procedure generate_lines(errbuf OUT NOCOPY VARCHAR2,
2741 retcode OUT NOCOPY VARCHAR2,
2742 p_person_id IN NUMBER,
2743 p_assignment_id IN NUMBER,
2744 --p_element_type_id IN NUMBER, commented for DA-ENH
2745 p_batch_name IN VARCHAR2,
2746 p_batch_comments IN VARCHAR2,
2747 p_run_id IN NUMBER,
2748 p_gl_posting_override_date IN DATE ,
2749 p_distribution_start_date IN DATE,
2750 p_distribution_end_date IN DATE,
2751 p_business_group_id IN NUMBER,
2752 p_set_of_books_id IN NUMBER,
2753 p_employee_full_name IN VARCHAR2,
2754 p_assignment_number IN VARCHAR2,
2755 ---p_earnings_element IN VARCHAR2, commented for DA-ENH
2756 p_time_out IN NUMBER,
2757 p_adjust_by IN VARCHAR2,
2758 -- Introduced for bug fix 2916848
2759 p_currency_code IN VARCHAR2,
2760 -- Introduced the following parameters for Bug 3548388
2761 p_defer_autopop_param IN VARCHAR2,
2762 p_begin_date IN DATE,
2763 p_adjustment_line_id OUT NOCOPY NUMBER,
2764 p_element_status OUT NOCOPY VARCHAR2)
2765 IS
2766
2767 l_return_status VARCHAR2(1);
2768 l_msg_count NUMBER;
2769 l_msg_data VARCHAR2(2000);
2770 l_wf_ret_status NUMBER;
2771 l_payroll_control_id integer;
2772
2773 /* Introduced the Following variables for Bug 3548388 */
2774
2775 l_gl_code_combination_id NUMBER;
2776 l_expenditure_type VARCHAR2(30);
2777 l_dummy_var NUMBER(10);
2778 l_ret_code VARCHAR2(1);
2779
2780 l_patc_status VARCHAR2(50);
2781 l_billable_flag VARCHAR2(1);
2782 l_msg_app VARCHAR2(2000);
2783 l_msg_type VARCHAR2(2000);
2784 l_msg_token1 VARCHAR2(2000);
2785 l_msg_token2 VARCHAR2(2000);
2786 l_msg_token3 VARCHAR2(2000);
2787 l_award_status VARCHAR2(2000);
2788
2789 l_chart_of_accts VARCHAR2(20);
2790 l_struc_num NUMBER; -- :=psp_general.find_chart_of_accts(p_set_of_books_id,l_chart_of_accts); commented for bug fix 3892097
2791 l_segs VARCHAR2(2000);
2792
2793 Inv_autopop_element Exception;
2794 Invalid_ptoe Exception;
2795 Invalid_ptoea Exception;
2796 Invalid_gl Exception;
2797
2798 -- to be removed
2799 p_element_name varchar2(30);
2800
2801
2802
2803 /* End of Bug 3548388 */
2804
2805
2806
2807 no_records_found EXCEPTION;
2808 workflow_failed EXCEPTION; -- Added for workflow
2809 counter NUMBER;
2810
2811 e_constraint_violation EXCEPTION;
2812
2813 --- ADDED following two cursors to check batch integrity .... for DA-ENH
2814 ---- Element wise net must be zero for the batch
2815
2816 l_element_name varchar2(80);
2817 l_element_type_id number;
2818 l_integrity_count integer;
2819
2820 cursor check_batch_integrity(p_batch_name varchar2) is
2821 select count(*), element_type_id
2822 from psp_adjustment_lines
2823 where batch_name = p_batch_name
2824 group by element_type_id
2825 having sum(decode(dr_cr_flag,'D',distribution_amount,-distribution_amount)) <> 0;
2826
2827 cursor get_element_name is
2828 select element_name
2829 from pay_element_types
2830 where element_type_id = l_element_type_id;
2831
2832 batch_net_not_zero exception;
2833 ----------- DA-ENH
2834
2835
2836 /* Added following cursor and PL/SQL tables to improve performance for DA-ENH */
2837 cursor adj_matrix is
2838 select PTDS.adj_set_number,
2839 PTOS.element_type_id,
2840 PTOS.time_period_id,
2841 Sum(decode (PTOS.dr_cr_flag, 'D', PTOS.distribution_amount,
2842 -PTOS.distribution_amount))
2843 from psp_temp_dest_sumlines PTDS,
2844 psp_temp_orig_lines PTOS
2845 where PTDS.original_line_flag = 'Y'
2846 and PTDS.acct_group_id = PTOS.acct_group_id
2847 and PTDS.run_id = g_run_id
2848 and PTOS.run_id = g_run_id
2849 group by PTDS.adj_set_number,
2850 PTOS.element_type_id,
2851 PTOS.time_period_id;
2852
2853 cursor sline_ideal_amnt_matrix is
2854 select adj_set_number,
2855 time_period_id,
2856 element_type_id,
2857 line_number,
2858 sum(decode(dr_cr_flag, 'D', round(distribution_amount, g_precision),
2859 -round(distribution_amount, g_precision))), --|>actual amnt
2860 round(sum(decode(dr_cr_flag, 'D', distribution_amount,
2861 -distribution_amount)) -
2862 sum(decode(dr_cr_flag, 'D',round(distribution_amount, g_precision), --|>delta amnt =
2863 -round(distribution_amount, g_precision))), g_precision), -- unrounded amnt - act amnt
2864 max(adjustment_line_id)
2865 from psp_adjustment_lines
2866 where batch_name = p_batch_name and
2867 original_line_flag = 'N'
2868 group by adj_set_number, time_period_id, element_type_id, line_number;
2869
2870 cursor sline_actual_amnt_matrix is
2871 select adj_set_number,
2872 element_type_id,
2873 time_period_id,
2874 sum(decode(dr_cr_flag, 'D', distribution_amount,
2875 -distribution_amount)),
2876 max(adjustment_line_id),
2877 0 --- delta sum
2878 from psp_adjustment_lines
2879 where original_line_flag = 'N' and
2880 batch_name = p_batch_name
2881 group by adj_set_number, element_type_id, time_period_id;
2882
2883
2884 TYPE t_num_15_type IS TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER;
2885 TYPE t_num_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
2886 -- Changed the datatype from (15, 2) to 30 for bug fix 2916848
2887 TYPE t_number_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
2888
2889 type ideal_amnt_matrix_rec is record
2890 (adj_set_number t_num_15_type,
2891 time_period_id t_num_15_type,
2892 element_type_id t_num_15_type,
2893 sline_number t_num_15_type, -- calling sline becos of grouping
2894 distribution_sum t_number_type, -- Corrected column type defn for bug fix 2916848
2895 delta_sum t_number_type, -- Corrected column type defn for bug fix 2916848
2896 adjustment_sline_id t_num_15_type);
2897
2898 r_sline_ideal_amnt_matrix ideal_amnt_matrix_rec;
2899
2900 type adj_matrix_rec is record
2901 (adj_set_number t_num_15_type,
2902 time_period_id t_num_15_type,
2903 element_type_id t_num_15_type,
2904 distribution_sum t_number_type); -- Corrected column type defn for bug fix 2916848
2905
2906 r_adj_matrix adj_matrix_rec;
2907
2908 type actual_amnt_matrix is record
2909 (adj_set_number t_num_15_type,
2910 time_period_id t_num_15_type,
2911 element_type_id t_num_15_type,
2912 distribution_sum t_number_type, -- Corrected column type defn for bug fix 2916848
2913 adjustment_sline_id t_num_15_type,
2914 delta_sum t_number_type); -- Corrected column type defn for bug fix 2916848
2915
2916 r_sline_actual_amnt_matrix actual_amnt_matrix;
2917
2918 --- added for bug fix 3470916
2919 --- this cursor is to get difference amounts from the RHS amounts and
2920 --- the generated new adjustment lines.
2921 cursor get_dest_diff is
2922 select sum(decode(LINE.dr_cr_flag,'D',LINE.distribution_amount,
2923 -LINE.distribution_amount)) amount,
2924 DEST.distribution_sum,
2925 LINE.adj_set_number,
2926 LINE.line_number
2927 from psp_adjustment_lines LINE,
2928 psp_temp_dest_sumlines DEST
2929 where LINE.batch_name = p_batch_name and
2930 LINE.original_line_flag = 'N' and
2931 DEST.run_id = p_run_id and
2932 DEST.original_line_flag = 'N' and
2933 DEST.adj_set_number = LINE.adj_set_number and
2934 DEST.line_number = LINE.line_number
2935 group by LINE.adj_set_number, LINE.line_number, DEST.distribution_sum;
2936
2937 r_dest_diff get_dest_diff%rowtype;
2938 --- 3776353 removed max on eleement type, time period, added asc
2939 cursor get_max_element(p_adj_set_number in integer,
2940 p_adj_line_number in integer) is
2941 select element_type_id,
2942 time_period_id
2943 from psp_adjustment_lines
2944 where batch_name = p_batch_name
2945 and original_line_flag = 'N'
2946 and adj_set_number = p_adj_set_number
2947 and line_number = p_adj_line_number
2948 order by element_type_id, time_period_id desc;
2949
2950 r_element_tp get_max_element%rowtype;
2951 l_prev_adj_set number :=null;
2952
2953
2954 /* Start of Changes for Supercencebased on migration to new OAf Effort Reporting */
2955
2956
2957 l_migration_status BOOLEAN:= psp_general.is_effort_report_migrated;
2958
2959
2960 /* End of Changes for Supercencebased on migration to new OAf Effort Reporting */
2961
2962
2963
2964 /* Introduced the following for Bug 3548388 */
2965
2966 TYPE t_varchar_30_type IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
2967 TYPE t_varchar_1_type IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
2968 TYPE t_date_type IS TABLE OF DATE INDEX BY BINARY_INTEGER;
2969
2970 type deferred_autopop_array is record
2971 ( array_element_type_id t_number_type,
2972 array_glccid t_num_15_type,
2973 array_project_id t_num_15_type,
2974 array_exp_org_id t_num_15_type,
2975 array_exp_type t_varchar_30_type,
2976 array_task_id t_num_15_type,
2977 array_award_id t_num_15_type
2978 );
2979
2980
2981
2982 deferred_autopop_rec deferred_autopop_array;
2983 final_autopop_rec deferred_autopop_array;
2984 l_count number :=0;
2985
2986
2987
2988 cursor autopop_defer_cur
2989 is
2990 select element_type_id,
2991 gl_code_combination_id,
2992 project_id,
2993 expenditure_organization_id,
2994 expenditure_type,
2995 task_id,
2996 award_id
2997 from psp_adjustment_lines
2998 where batch_name = p_batch_name
2999 and original_line_flag <> 'Y'
3000 and set_of_books_id = p_set_of_books_id
3001 and business_group_id = p_business_group_id
3002 Group by element_type_id,
3003 gl_code_combination_id,
3004 project_id,
3005 expenditure_organization_id,
3006 expenditure_type,
3007 task_id,
3008 award_id ;
3009
3010
3011 /* cursor to check the list of distinct effective dates for element type id and poeta combination */
3012
3013 cursor pa_check_effectivedate (p_element_type_id NUMBER,
3014 p_project_id NUMBER,
3015 p_task_id NUMBER,
3016 p_award_id NUMBER,
3017 p_expenditure_organization_id NUMBER,
3018 p_expenditure_type VARCHAR2)
3019 is
3020 select effective_date ,
3021 adjustment_line_id
3022 from psp_adjustment_lines
3023 where element_type_id = p_element_type_id
3024 and project_id = p_project_id
3025 and expenditure_organization_id = p_expenditure_organization_id
3026 and expenditure_type = p_expenditure_type
3027 and task_id = p_task_id
3028 and award_id = p_award_id
3029 and business_group_id = p_business_group_id
3030 and set_of_books_id = p_set_of_books_id
3031 and batch_name = p_batch_name
3032 and original_line_flag <> 'Y'
3033 group by effective_date,
3034 adjustment_line_id;
3035
3036
3037 type l_effective_date is table of psp_adjustment_lines.effective_date%type INDEX BY BINARY_INTEGER;
3038 type l_adj_line_id is table of psp_adjustment_lines.adjustment_line_id%type INDEX BY BINARY_INTEGER;
3039 type effective_date_rec is record
3040 (r_effective_date l_effective_date,
3041 r_adj_line_id l_adj_line_id);
3042
3043 effective_date_array effective_date_rec ;
3044
3045
3046
3047
3048 /* End of bug 3548388 */
3049
3050 -- Introduced for Bug fix 3741272
3051
3052 CURSOR l_exp_org_csr(p_eff_date date, p_exp_org_id Number)
3053 IS
3054 SELECT 'x'
3055 FROM psp_organizations_expend_v
3056 WHERE organization_id = p_exp_org_id
3057 and trunc(p_eff_date) between date_from and nvl(date_to,trunc(p_eff_date));
3058
3059 l_dummy VARCHAR2(1);
3060
3061 -- End of changes for Bug fix 3741272
3062
3063 TYPE t_number_15_type IS TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER;
3064 TYPE t_char_1_type IS TABLE OF CHAR(1) INDEX BY BINARY_INTEGER;
3065
3066 TYPE orig_line_rec is record
3067 (orig_line_id t_number_15_type,
3068 orig_source_type t_char_1_type);
3069 r_orig_lines orig_line_rec;
3070
3071 CURSOR orig_line_id_cur IS
3072 SELECT orig_line_id,
3073 orig_source_type
3074 FROM psp_adjustment_lines
3075 WHERE payroll_control_id = l_payroll_control_id;
3076
3077 -- Bug 6634876
3078 CURSOR net_batch_sum_cur IS
3079 SELECT sum(decode(dr_cr_flag,'D',distribution_amount,-distribution_amount)) amount
3080 FROM psp_adjustment_lines
3081 WHERE batch_name = p_batch_name;
3082
3083 r_net_zero_diff net_batch_sum_cur%rowtype;
3084
3085 begin
3086
3087 hr_utility.trace('**************************************************');
3088 hr_utility.trace('LD Debugging Starts');
3089 hr_utility.trace('Entering GENERATE_LINES procedure');
3090
3091 hr_utility.trace('Procedure IN Parameters Starts ');
3092
3093 hr_utility.trace('p_person_id = '||p_person_id);
3094 hr_utility.trace('p_assignment_id = '||p_assignment_id);
3095 hr_utility.trace('p_batch_name = '||p_batch_name);
3096 hr_utility.trace('p_batch_comments = '||p_batch_comments);
3097 hr_utility.trace('p_run_id = '||p_run_id);
3098 hr_utility.trace('p_gl_posting_override_date = '||p_gl_posting_override_date);
3099 hr_utility.trace('p_distribution_start_date = '||p_distribution_start_date);
3100 hr_utility.trace('p_distribution_end_date = '||p_distribution_end_date);
3101 hr_utility.trace('p_business_group_id = '||p_business_group_id);
3102 hr_utility.trace('p_set_of_books_id = '||p_set_of_books_id);
3103 hr_utility.trace('p_employee_full_name = '||p_employee_full_name);
3104 hr_utility.trace('p_assignment_number = '||p_assignment_number);
3105 hr_utility.trace('p_time_out = '||p_time_out);
3106 hr_utility.trace('p_adjust_by = '||p_adjust_by);
3107 hr_utility.trace('p_currency_code = '||p_currency_code);
3108 hr_utility.trace('p_defer_autopop_param = '||p_defer_autopop_param);
3109 hr_utility.trace('p_begin_date = '||p_begin_date);
3110
3111 hr_utility.trace('Procedure IN Parameters Ends ');
3112
3113 g_error_api_path := '';
3114 fnd_msg_pub.initialize;
3115 errbuf := '';
3116 retcode := 0;
3117 counter := 1;
3118
3119 l_struc_num := psp_general.find_chart_of_accts(p_set_of_books_id,l_chart_of_accts);
3120
3121 select psp_payroll_controls_s.nextval into l_payroll_control_id from dual;
3122
3123 hr_utility.trace('l_payroll_control_id = '||l_payroll_control_id);
3124 hr_utility.trace('Inserting into PSP_ADJUSTMENT_CONTROL_TABLE : batch = '||p_batch_name);
3125
3126 insert_adjustment_control(p_batch_name,
3127 p_batch_comments,
3128 l_return_status,
3129 p_gl_posting_override_date,
3130 p_person_id,
3131 p_assignment_id,
3132 ---p_element_type_id, replaced with NULL for DA-ENH
3133 p_distribution_start_date,
3134 p_distribution_end_date,
3135 p_currency_code, -- Introduced for bug fix 2916848
3136 p_business_group_id,
3137 p_set_of_books_id,
3138 p_adjust_by); --- added param for DA-ENH
3139
3140 IF l_return_status = g_constraint_violation THEN
3141 RAISE e_constraint_violation;
3142 ELSIF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3143 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3144 END IF;
3145
3146 --Reversal Lines Generation
3147
3148 hr_utility.trace('Inserting into PSP_ADJUSTMENT_LINES - 10');
3149
3150 insert into psp_adjustment_lines(adjustment_line_id,
3151 person_id,
3152 assignment_id,
3153 element_type_id,
3154 distribution_date,
3155 effective_date,
3156 distribution_amount,
3157 dr_cr_flag,
3158 payroll_control_id,
3159 source_type,
3160 source_code,
3161 time_period_id,
3162 batch_name,
3163 status_code,
3164 set_of_books_id,
3165 gl_code_combination_id,
3166 project_id,
3167 expenditure_organization_id,
3168 expenditure_type,
3169 task_id,
3170 award_id,
3171 reversal_entry_flag,
3172 original_line_flag,
3173 user_defined_field,
3174 percent,
3175 orig_source_type,
3176 orig_line_id,
3177 business_group_id,
3178 adj_set_number, --- new column for DA-ENH
3179 last_update_date,
3180 last_updated_by,
3181 last_update_login,
3182 created_by,
3183 creation_date,
3184 line_number, --- new column for DA-ENH
3185 attribute_category, -- Introduced DFF columns for bug fix 2908859
3186 attribute1,
3187 attribute2,
3188 attribute3,
3189 attribute4,
3190 attribute5,
3191 attribute6,
3192 attribute7,
3193 attribute8,
3194 attribute9,
3195 attribute10)
3196 select psp_adjustment_lines_s.nextval,
3197 p_person_id,
3198 p_assignment_id,
3199 ptol.element_type_id,
3200 ptol.orig_distribution_date,
3201 ptol.effective_date,
3202 ptol.distribution_amount,
3203 decode(ptol.dr_cr_flag,'D','C','D'), -- reverse the dr_cr_flag
3204 l_payroll_control_id,
3205 'A',
3206 'Adjustments',
3207 ptol.time_period_id,
3208 p_batch_name,
3209 'N',
3210 p_set_of_books_id,
3211 ptds.gl_code_combination_id,
3212 ptds.project_id,
3213 ptds.expenditure_organization_id,
3214 ptds.expenditure_type,
3215 ptds.task_id, ptds.award_id,
3216 null, -- reversal entry flag.
3217 'Y', -- original line flag
3218 null,
3219 ptds.distribution_percent,
3220 ptol.orig_source_type,
3221 ptol.orig_line_id,
3222 p_business_group_id,
3223 ptds.adj_set_number,
3224 sysdate,
3225 fnd_global.user_id,
3226 fnd_global.login_id,
3227 fnd_global.user_id,
3228 sysdate,
3229 ptds.line_number,
3230 ptds.attribute_category, -- Introduced DFF columns for bug fix 2908859
3231 ptds.attribute1,
3232 ptds.attribute2,
3233 ptds.attribute3,
3234 ptds.attribute4,
3235 ptds.attribute5,
3236 ptds.attribute6,
3237 ptds.attribute7,
3238 ptds.attribute8,
3239 ptds.attribute9,
3240 ptds.attribute10
3241 from psp_temp_orig_lines ptol, psp_temp_dest_sumlines ptds
3242 where ptol.business_group_id = p_business_group_id
3243 and ptol.set_of_books_id = p_set_of_books_id
3244 and ptol.business_group_id = ptds.business_group_id
3245 and ptol.set_of_books_id = ptds.set_of_books_id
3246 and ptol.acct_group_id = ptds.acct_group_id
3247 and ptds.original_line_flag = 'Y'
3248 and ptol.run_id = p_run_id
3249 and ptds.run_id = ptol.run_id;
3250
3251 if sql%rowcount = 0 then
3252 g_error_api_path := 'No records found in reversal lines:'||g_error_api_path;
3253 raise NO_RECORDS_FOUND;
3254 end if;
3255
3256 hr_utility.trace('Inserted into PSP_ADJUSTMENT_LINES - 10');
3257
3258 /***** Converted the following update statements into BULK update for R12 performance fixes (bug 4507892)
3259 UPDATE psp_distribution_lines_history
3260 SET adjustment_batch_name = p_batch_name
3261 WHERE distribution_line_id in ( select orig_line_id
3262 from psp_adjustment_lines
3263 where payroll_control_id = l_payroll_control_id
3264 and orig_source_type = 'D');
3265
3266 UPDATE psp_pre_gen_dist_lines_history
3267 SET adjustment_batch_name = p_batch_name
3268 WHERE pre_gen_dist_line_id in ( select orig_line_id
3269 from psp_adjustment_lines
3270 where payroll_control_id = l_payroll_control_id
3271 and orig_source_type = 'P');
3272
3273
3274 UPDATE psp_adjustment_lines_history
3275 SET adjustment_batch_name = p_batch_name
3276 WHERE adjustment_line_id in ( select orig_line_id
3277 from psp_adjustment_lines
3278 where payroll_control_id = l_payroll_control_id
3279 and orig_source_type = 'A');
3280 ---end of code changes for reversal line generation DA-ENH
3281 End of comment for bug fix 4507892 *****/
3282
3283 -- Introduced the following for bug fix 4507892
3284
3285 hr_utility.trace('Calling ORIG_LINE_ID_CUR cursor');
3286 OPEN orig_line_id_cur;
3287 FETCH orig_line_id_cur BULK COLLECT INTO r_orig_lines.orig_line_id, r_orig_lines.orig_source_type;
3288 CLOSE orig_line_id_cur;
3289
3290 hr_utility.trace('r_orig_lines.orig_line_id.COUNT = '||r_orig_lines.orig_line_id.COUNT);
3291
3292 FORALL I IN 1..r_orig_lines.orig_line_id.COUNT
3293 UPDATE psp_distribution_lines_history
3294 SET adjustment_batch_name = p_batch_name
3295 WHERE distribution_line_id = r_orig_lines.orig_line_id(I)
3296 AND r_orig_lines.orig_source_type(I) = 'D';
3297
3298 FORALL I IN 1..r_orig_lines.orig_line_id.COUNT
3299 UPDATE psp_pre_gen_dist_lines_history
3300 SET adjustment_batch_name = p_batch_name
3301 WHERE pre_gen_dist_line_id = r_orig_lines.orig_line_id(I)
3302 AND r_orig_lines.orig_source_type(I) = 'P';
3303
3304 FORALL I IN 1..r_orig_lines.orig_line_id.COUNT
3305 UPDATE psp_adjustment_lines_history
3306 SET adjustment_batch_name = p_batch_name
3307 WHERE adjustment_line_id = r_orig_lines.orig_line_id(I)
3308 AND r_orig_lines.orig_source_type(I) = 'A';
3309
3310 r_orig_lines.orig_line_id.DELETE;
3311 r_orig_lines.orig_source_type.DELETE;
3312 -- End of changes for bug fix 4507892
3313
3314 hr_utility.trace('Completed BULK updates');
3315
3316 ----- Insert statement to generate new adj lines.
3317 hr_utility.trace('Inserting into PSP_ADJUSTMENT_LINES - 20');
3318
3319 insert into psp_adjustment_lines
3320 (adjustment_line_id,
3321 person_id,
3322 assignment_id,
3323 element_type_id,
3324 distribution_date,
3325 effective_date,
3326 distribution_amount,
3327 dr_cr_flag,
3328 payroll_control_id,
3329 source_type,
3330 source_code,
3331 time_period_id,
3332 batch_name,
3333 status_code,
3334 set_of_books_id,
3335 gl_code_combination_id,
3336 project_id,
3337 expenditure_organization_id,
3338 expenditure_type,
3339 task_id,
3340 award_id,
3341 reversal_entry_flag,
3342 original_line_flag,
3343 user_defined_field,
3344 percent,
3345 orig_source_type,
3346 orig_line_id,
3347 business_group_id,
3348 adj_set_number, --- new column for DA-ENH
3349 last_update_date,
3350 last_updated_by,
3351 last_update_login,
3352 created_by,
3353 creation_date,
3354 line_number,
3355 attribute_category, -- Introduced DFF columns for bug fix 2908859
3356 attribute1,
3357 attribute2,
3358 attribute3,
3359 attribute4,
3360 attribute5,
3361 attribute6,
3362 attribute7,
3363 attribute8,
3364 attribute9,
3365 attribute10) --- new column for DA-ENH
3366 select psp_adjustment_lines_s.nextval,
3367 p_person_id,
3368 p_assignment_id,
3369 LINES.element_type_id,
3370 LINES.orig_distribution_date,
3371 -- LINES.effective_date, Commented for bug fix 3927570
3372 -- Introduced the following for bug fix 3892097
3373 fnd_date.canonical_to_date(DECODE(trans_type.transaction_type_count,
3374 1, fnd_date.date_to_canonical(LINES.effective_date),
3375 DECODE(dest.gl_code_combination_id,
3376 NULL, fnd_date.date_to_canonical(lines.orig_distribution_date),
3377 fnd_date.date_to_canonical(ptp.end_date)))) effective_date,
3378 -- End of changes for bug fix 3927570
3379 LINES.distribution_amount * DEST.distribution_percent/100, ------- unrounded, unlimited precision DA_ENH
3380 LINES.dr_cr_flag,
3381 l_payroll_control_id,
3382 'A',
3383 'Adjustments',
3384 LINES.time_period_id,
3385 p_batch_name,
3386 'N',
3387 p_set_of_books_id,
3388 DEST.gl_code_combination_id,
3389 DEST.project_id,
3390 DEST.expenditure_organization_id,
3391 DEST.expenditure_type,
3392 DEST.task_id,
3393 DEST.award_id,
3394 null, -- reversal entry flag.
3395 'N', -- original line flag
3396 null,
3397 DEST.distribution_percent,
3398 LINES.orig_source_type,
3399 LINES.orig_line_id,
3400 p_business_group_id,
3401 DEST.adj_set_number,
3402 sysdate,
3403 fnd_global.user_id,
3404 fnd_global.login_id,
3405 fnd_global.user_id,
3406 sysdate,
3407 DEST.line_number,
3408 dest.attribute_category, --Introduced DFF columns for bug fix 2908859
3409 dest.attribute1,
3410 dest.attribute2,
3411 dest.attribute3,
3412 dest.attribute4,
3413 dest.attribute5,
3414 dest.attribute6,
3415 dest.attribute7,
3416 dest.attribute8,
3417 dest.attribute9,
3418 dest.attribute10
3419 from psp_temp_dest_sumlines ORIG,
3420 psp_temp_dest_sumlines DEST,
3421 psp_temp_orig_lines LINES,
3422 -- Introduced the following for bug fix 3892097
3423 per_time_periods ptp,
3424 (SELECT adj_set_number,
3425 COUNT(DISTINCT DECODE(gl_code_combination_id, NULL, 'P', 'G')) transaction_type_count
3426 FROM psp_temp_dest_sumlines ptds
3427 WHERE ptds.run_id = g_run_id
3428 GROUP BY adj_set_number) trans_type
3429 -- End of changes for bug fix 3892097
3430 where ORIG.run_id = g_run_id and
3431 DEST.run_id = g_run_id and
3432 LINES.run_id = g_run_id and
3433 -- Introduced the following for bug fix 3892097
3434 ptp.time_period_id = lines.time_period_id AND
3435 dest.adj_set_number = trans_type.adj_set_number AND
3436 -- End of changes for bug fix 3892097
3437 DEST.adj_set_number = ORIG.adj_set_number and
3438 LINES.acct_group_id = ORIG.acct_group_id and
3439 nvl(ORIG.original_line_flag,'Y') = 'Y' and
3440 DEST.original_line_flag = 'N';
3441 /* ORDER BY DEST.adj_set_number, DEST.line_number, LINES.time_period_id;
3442 --- order by ensures the delta amount will be posted to line of last time periods.
3443 */
3444 hr_utility.trace('Inserted into PSP_ADJUSTMENT_LINES - 20');
3445
3446 hr_utility.trace('g_precision = '||g_precision);
3447 hr_utility.trace('Bulk Fetch SLINE_IDEAL_AMNT_MATRIX values');
3448
3449 open sline_ideal_amnt_matrix;
3450 fetch sline_ideal_amnt_matrix bulk collect into
3451 r_sline_ideal_amnt_matrix.adj_set_number,
3452 r_sline_ideal_amnt_matrix.time_period_id,
3453 r_sline_ideal_amnt_matrix.element_type_id,
3454 r_sline_ideal_amnt_matrix.sline_number,
3455 r_sline_ideal_amnt_matrix.distribution_sum,
3456 r_sline_ideal_amnt_matrix.delta_sum,
3457 r_sline_ideal_amnt_matrix.adjustment_sline_id;
3458 close sline_ideal_amnt_matrix;
3459
3460 hr_utility.trace('Updating the DISTRIBUTION_AMOUNT based on g_precision');
3461
3462 update psp_adjustment_lines
3463 set distribution_amount = round(distribution_amount, g_precision)
3464 where original_line_flag = 'N'and
3465 batch_name = p_batch_name;
3466
3467 forall I in 1..r_sline_ideal_amnt_matrix.adj_set_number.count
3468 update psp_adjustment_lines
3469 set distribution_amount = distribution_amount +
3470 decode(dr_cr_flag,'D', r_sline_ideal_amnt_matrix.delta_sum(i),
3471 -r_sline_ideal_amnt_matrix.delta_sum(i))
3472 where adjustment_line_id = r_sline_ideal_amnt_matrix.adjustment_sline_id(i);
3473
3474 r_sline_ideal_amnt_matrix.adj_set_number.delete;
3475 r_sline_ideal_amnt_matrix.time_period_id.delete;
3476 r_sline_ideal_amnt_matrix.sline_number.delete;
3477 r_sline_ideal_amnt_matrix.distribution_sum.delete;
3478 r_sline_ideal_amnt_matrix.delta_sum.delete;
3479 r_sline_ideal_amnt_matrix.adjustment_sline_id.delete;
3480
3481 hr_utility.trace('Bulk Fetch ADJ_MATRIX values');
3482
3483 open adj_matrix;
3484 fetch adj_matrix bulk collect into r_adj_matrix.adj_set_number,
3485 r_adj_matrix.time_period_id,
3486 r_adj_matrix.element_type_id,
3487 r_adj_matrix.distribution_sum;
3488 close adj_matrix;
3489
3490 hr_utility.trace('Bulk Fetch SLINE_ACTUAL_AMNT_MATRIX values');
3491
3492 open sline_actual_amnt_matrix;
3493 fetch sline_actual_amnt_matrix bulk collect into
3494 r_sline_actual_amnt_matrix.adj_set_number,
3495 r_sline_actual_amnt_matrix.time_period_id,
3496 r_sline_actual_amnt_matrix.element_type_id,
3497 r_sline_actual_amnt_matrix.distribution_sum,
3498 r_sline_actual_amnt_matrix.adjustment_sline_id,
3499 r_sline_actual_amnt_matrix.delta_sum;
3500
3501 close sline_actual_amnt_matrix;
3502
3503 /* Ensure that amount adj set/time period/element wise is in sync with new distributions,
3504 i.e amount doesnot flow between set to set, it does not flow between element to element,
3505 does not flow between time period to time period */
3506
3507 for I in 1.. r_adj_matrix.adj_set_number.count
3508 loop
3509 for j in 1..r_sline_actual_amnt_matrix.adj_set_number.count
3510 loop
3511 if r_adj_matrix.adj_set_number(i) = r_sline_actual_amnt_matrix.adj_set_number(j) and
3512 r_adj_matrix.element_type_id(i)= r_sline_actual_amnt_matrix.element_type_id(j) and
3513 r_adj_matrix.time_period_id(i) = r_sline_actual_amnt_matrix.time_period_id(j)
3514 then
3515 r_sline_actual_amnt_matrix.delta_sum(i) :=
3516 r_adj_matrix.distribution_sum(i) - r_sline_actual_amnt_matrix.distribution_sum(j);
3517
3518 hr_utility.trace('Inside Loop1');
3519 hr_utility.trace('r_sline_actual_amnt_matrix.delta_sum(i) = '||r_sline_actual_amnt_matrix.delta_sum(i));
3520
3521 end if;
3522 end loop;
3523 end loop;
3524
3525 r_adj_matrix. adj_set_number.delete;
3526 r_adj_matrix.element_type_id.delete;
3527 r_adj_matrix.distribution_sum.delete;
3528
3529 forall I in 1.. r_sline_actual_amnt_matrix.adj_set_number.count
3530 update psp_adjustment_lines
3531 set distribution_amount = distribution_amount +
3532 decode(dr_cr_flag, 'D', r_sline_actual_amnt_matrix.delta_sum(I),
3533 - r_sline_actual_amnt_matrix.delta_sum(I))
3534 where adjustment_line_id = r_sline_actual_amnt_matrix.adjustment_sline_id(i);
3535
3536 r_sline_actual_amnt_matrix.adj_set_number.delete;
3537 r_sline_actual_amnt_matrix.distribution_sum.delete;
3538 r_sline_actual_amnt_matrix.adjustment_sline_id.delete;
3539 r_sline_actual_amnt_matrix.delta_sum.delete;
3540
3541 --- bug fix 3470916
3542 -- The net of all differences between RHS amounts in
3543 -- psp_temp_dest_sumlines and psp_adjustment_lines will be zero.
3544 --- Logic is that for each dest sumlines, for wich it's sum doesnot equal
3545 --- the sum of dist amount in psp_adjustment_lines, adjust the difference
3546 -- to the last line in last time period of the element with max element_type_id
3547 --- there is no reason for this criteria in particular, but there should
3548 -- some convention to adjust.
3549
3550 hr_utility.trace('Opening GET_DEST_DIFF cursor');
3551 open get_dest_diff;
3552 loop
3553 hr_utility.trace('LOOP2');
3554 fetch get_dest_diff into r_dest_diff;
3555 if get_dest_diff%notfound then
3556 close get_dest_diff;
3557 exit;
3558 end if;
3559
3560 hr_utility.trace('r_dest_diff.amount = '||r_dest_diff.amount);
3561 hr_utility.trace('r_dest_diff.distribution_sum = '||r_dest_diff.distribution_sum);
3562
3563 if r_dest_diff.amount <> r_dest_diff.distribution_sum then
3564 if (nvl(l_prev_adj_set,0) = 0 or l_prev_adj_set <> r_dest_diff.adj_set_number) then
3565 open get_max_element(r_dest_diff.adj_set_number, r_dest_diff.line_number);
3566 fetch get_max_element into r_element_tp;
3567 close get_max_element;
3568 l_prev_adj_set := r_dest_diff.adj_set_number; --- added for 3776353
3569 end if;
3570
3571 update psp_adjustment_lines
3572 set distribution_amount = distribution_amount +
3573 decode(dr_cr_flag,'D',r_dest_diff.distribution_sum-r_dest_diff.amount,
3574 -r_dest_diff.distribution_sum+r_dest_diff.amount)
3575 where adjustment_line_id in
3576 (select max(adjustment_line_id)
3577 from psp_adjustment_lines
3578 where batch_name = p_batch_name and
3579 original_line_flag = 'N' and
3580 line_number = r_dest_diff.line_number and
3581 adj_set_number = r_dest_diff.adj_set_number and
3582 element_type_id = r_element_tp.element_type_id and
3583 time_period_id = r_element_tp.time_period_id);
3584
3585 end if;
3586 end loop;
3587
3588 ---- moved the delete from above the loop for 3776353
3589 delete psp_adjustment_lines
3590 where distribution_amount = 0
3591 and batch_name = p_batch_name;
3592
3593 --- added for DA-ENH
3594 --- adj batch integrity check, element wise-net sum must be zero,
3595 --- if net sum not zero, error buf will contain the first element with problem
3596
3597 -- Added If-End if for Bug 6634876
3598 OPEN net_batch_sum_cur;
3599 FETCH net_batch_sum_cur INTO r_net_zero_diff;
3600 CLOSE net_batch_sum_cur;
3601
3602 IF (r_net_zero_diff.amount <> 0) THEN
3603
3604 hr_utility.trace('Opening CHECK_BATCH_INTEGRITY cursor for batch_name = '||p_batch_name);
3605
3606 open check_batch_integrity(p_batch_name);
3607 fetch check_batch_integrity into l_integrity_count, l_element_type_id;
3608 close check_batch_integrity;
3609
3610 hr_utility.trace('l_integrity_count = '||l_integrity_count);
3611 hr_utility.trace('l_element_type_id = '||l_element_type_id);
3612
3613 if l_integrity_count > 0 then
3614 --- shows only the first offending element i.e net amount not zero.
3615 open get_element_name;
3616 fetch get_element_name into l_element_name;
3617 close get_element_name;
3618
3619 hr_utility.trace('l_element_name = '||l_element_name);
3620 hr_utility.trace('Before Raise error');
3621
3622 raise batch_net_not_zero;
3623
3624 hr_utility.trace('After Raise error');
3625 end if;
3626
3627 END IF; -- Added If - End If for bug 6634876
3628
3629
3630 /* Introduced the following code for Bug 3548388 */
3631
3632 if (p_defer_autopop_param = 'Y' and ( p_adjust_by ='A' or p_adjust_by = 'G' )) Then
3633
3634
3635
3636
3637
3638 deferred_autopop_rec.array_element_type_id.delete;
3639 deferred_autopop_rec.array_glccid.delete;
3640 deferred_autopop_rec.array_project_id.delete;
3641 deferred_autopop_rec.array_exp_org_id.delete;
3642 deferred_autopop_rec.array_exp_type.delete;
3643 deferred_autopop_rec.array_task_id.delete;
3644 deferred_autopop_rec.array_award_id.delete;
3645
3646 final_autopop_rec.array_element_type_id.delete;
3647 final_autopop_rec.array_glccid.delete;
3648 final_autopop_rec.array_project_id.delete;
3649 final_autopop_rec.array_exp_org_id.delete;
3650 final_autopop_rec.array_exp_type.delete;
3651 final_autopop_rec.array_task_id.delete;
3652 final_autopop_rec.array_award_id.delete;
3653
3654
3655 open autopop_defer_cur;
3656 fetch autopop_defer_cur bulk collect into
3657 deferred_autopop_rec.array_element_type_id,
3658 deferred_autopop_rec.array_glccid,
3659 deferred_autopop_rec.array_project_id,
3660 deferred_autopop_rec.array_exp_org_id,
3661 deferred_autopop_rec.array_exp_type,
3662 deferred_autopop_rec.array_task_id,
3663 deferred_autopop_rec.array_award_id;
3664 close autopop_defer_cur;
3665
3666 if ( deferred_autopop_rec.array_element_type_id.count > 0 ) then
3667
3668 FOR I IN 1 .. deferred_autopop_rec.array_element_type_id.count
3669 LOOP
3670
3671 l_count:=l_count + 1;
3672 l_expenditure_type := NULL;
3673 l_gl_code_combination_id :=NULL;
3674 l_segs := NULL;
3675
3676 If (deferred_autopop_rec.array_project_id(I) IS NOT NULL ) and
3677 (deferred_autopop_rec.array_task_id(I) IS NOT NULL) and
3678 (deferred_autopop_rec.array_exp_org_id(I) IS NOT NULL)
3679 then
3680
3681 psp_autopop.main(p_acct_type => 'E',
3682 p_person_id => p_person_id,
3683 p_assignment_id => p_assignment_id,
3684 p_element_type_id => deferred_autopop_rec.array_element_type_id(i),
3685 p_project_id => deferred_autopop_rec.array_project_id(i),
3686 p_expenditure_organization_id => deferred_autopop_rec.array_exp_org_id(i),
3687 p_task_id => deferred_autopop_rec.array_task_id(i),
3688 p_award_id => deferred_autopop_rec.array_award_id(i),
3689 p_expenditure_type => deferred_autopop_rec.array_exp_type(i),
3690 p_gl_code_combination_id => null,
3691 p_payroll_date => p_begin_date,
3692 p_set_of_books_id =>p_set_of_books_id,
3693 p_business_group_id =>p_business_group_id,
3694 ret_expenditure_type => l_expenditure_type,
3695 ret_gl_code_combination_id => l_dummy_var,
3696 retcode => l_ret_code);
3697
3698 if (l_ret_code in ('U', 'E') ) then
3699 p_adjustment_line_id := NULL;
3700 p_element_status:= psp_general.get_element_name
3701 (deferred_autopop_rec.array_element_type_id(i),
3702 trunc(SYSDATE)) ;
3703 raise Inv_autopop_element;
3704 end if ;
3705
3706
3707
3708 -- Introduced this cursor to check whether poet or poeta validations are valid
3709
3710 open pa_check_effectivedate
3711 (deferred_autopop_rec.array_element_type_id(i),
3712 deferred_autopop_rec.array_project_id(i) ,
3713 deferred_autopop_rec.array_task_id(i),
3714 deferred_autopop_rec.array_award_id(i),
3715 deferred_autopop_rec.array_exp_org_id(i),
3716 deferred_autopop_rec.array_exp_type(i));
3717 fetch pa_check_effectivedate bulk collect into
3718 effective_date_array.r_effective_date,
3719 effective_date_array.r_adj_line_id;
3720 close pa_check_effectivedate;
3721
3722 for J in 1 .. effective_date_array.r_effective_date.count
3723 loop
3724
3725
3726 l_patc_status := NULL;
3727
3728 pa_transactions_pub.validate_transaction(
3729 x_project_id => deferred_autopop_rec.array_project_id(i),
3730 x_task_id => deferred_autopop_rec.array_task_id(i),
3731 x_ei_date => effective_date_array.r_effective_date(j),
3732 x_expenditure_type => l_expenditure_type,
3733 x_non_labor_resource => null,
3734 x_person_id => p_person_id,
3735 x_incurred_by_org_id => deferred_autopop_rec.array_exp_org_id(i),
3736 x_calling_module => 'PSPAUTOB',
3737 x_msg_application=> l_msg_app,
3738 x_msg_type => l_msg_type,
3739 x_msg_token1 => l_msg_token1,
3740 x_msg_token2 => l_msg_token2,
3741 x_msg_token3 => l_msg_token3,
3742 x_msg_count => l_msg_count,
3743 x_msg_data => l_patc_status,
3744 x_billable_flag => l_billable_flag,
3745 p_sys_link_function => 'ST'); --Bug 5639589: Added parameter
3746
3747 -- Introduced for Bug fix 3741272
3748
3749 If (l_patc_status = 'PA_EXP_ORG_NOT_ACTIVE' ) Then
3750
3751 OPEN l_exp_org_csr(effective_date_array.r_effective_date(j),
3752 deferred_autopop_rec.array_exp_org_id(i));
3753 FETCH l_exp_org_csr INTO l_dummy;
3754 CLOSE l_exp_org_csr;
3755
3756 if l_dummy = 'x' then
3757 l_patc_status:= NULL ;
3758 else
3759 l_patc_status:= 'PA_EXP_ORG_NOT_ACTIVE' ;
3760 end if ;
3761
3762 End if ;
3763 -- End of changes for Bug fix 3741272
3764
3765
3766 if l_patc_status is not null then
3767
3768 p_adjustment_line_id := effective_date_array.r_adj_line_id(j);
3769 p_element_status := l_patc_status;
3770 raise Invalid_ptoe;
3771
3772 end if ;
3773
3774 if deferred_autopop_rec.array_award_id(i) IS not null
3775 then
3776
3777 gms_transactions_pub.validate_transaction (
3778 deferred_autopop_rec.array_project_id(i),
3779 deferred_autopop_rec.array_task_id(i),
3780 deferred_autopop_rec.array_award_id(i),
3781 l_expenditure_type,
3782 effective_date_array.r_effective_date(j),
3783 'PSPAUTOB',
3784 l_award_status);
3785 end if;
3786
3787 if l_award_status is not null then
3788
3789 p_adjustment_line_id := effective_date_array.r_adj_line_id(j);
3790 p_element_status := l_award_status;
3791 raise Invalid_ptoea;
3792
3793 end if;
3794
3795
3796 End loop ;
3797
3798
3799 elsif (deferred_autopop_rec.array_glccid(i) IS NOT NULL )
3800 then
3801
3802 psp_autopop.main(p_acct_type => 'N',
3803 p_person_id =>p_person_id,
3804 p_assignment_id => p_assignment_id,
3805 p_element_type_id =>deferred_autopop_rec.array_element_type_id(i),
3806 p_project_id =>null ,
3807 p_expenditure_organization_id => null,
3808 p_task_id => null,
3809 p_award_id => null,
3810 p_expenditure_type => null,
3811 p_gl_code_combination_id =>deferred_autopop_rec.array_glccid(i),
3812 p_payroll_date => p_begin_date,
3813 p_set_of_books_id =>p_set_of_books_id,
3814 p_business_group_id =>p_business_group_id,
3815 ret_expenditure_type => l_dummy_var,
3816 ret_gl_code_combination_id => l_gl_code_combination_id,
3817 retcode => l_ret_code);
3818
3819 if (l_ret_code in ('U', 'E') ) then
3820 p_adjustment_line_id := NULL;
3821 p_element_status:= psp_general.get_element_name
3822 (deferred_autopop_rec.array_element_type_id(i),
3823 trunc(SYSDATE)) ;
3824 raise Inv_autopop_element;
3825
3826 end if ;
3827
3828
3829 if(l_gl_code_combination_id is NOT NULL) then
3830
3831 l_segs :=fnd_flex_ext.get_segs(
3832 application_short_name =>'SQLGL',
3833 key_flex_code => 'GL#',
3834 structure_number => to_number(l_chart_of_accts),
3835 combination_id =>l_gl_code_combination_id);
3836
3837
3838 if(l_segs is NULL ) then
3839 p_adjustment_line_id := NULL;
3840 p_element_status:= psp_general.get_element_name
3841 (deferred_autopop_rec.array_element_type_id(i),
3842 trunc(SYSDATE)) ;
3843 raise Inv_autopop_element;
3844 end if ;
3845
3846 else
3847
3848 p_adjustment_line_id := NULL;
3849 p_element_status:= psp_general.get_element_name
3850 (deferred_autopop_rec.array_element_type_id(i),
3851 trunc(SYSDATE)) ;
3852 raise Inv_autopop_element;
3853
3854 end if;
3855
3856
3857
3858
3859 end if;
3860
3861
3862 final_autopop_rec.array_element_type_id(l_count):= deferred_autopop_rec.array_element_type_id(i);
3863 final_autopop_rec.array_glccid(l_count):= nvl(l_gl_code_combination_id,deferred_autopop_rec.array_glccid(i));
3864 final_autopop_rec.array_project_id(l_count):= deferred_autopop_rec.array_project_id(i);
3865 final_autopop_rec.array_exp_org_id(l_count):= deferred_autopop_rec.array_exp_org_id(i);
3866 final_autopop_rec.array_exp_type(l_count):= nvl(l_expenditure_type,deferred_autopop_rec.array_exp_type(i));
3867 final_autopop_rec.array_task_id(l_count) := deferred_autopop_rec.array_task_id(i);
3868 final_autopop_rec.array_award_id(l_count):= deferred_autopop_rec.array_award_id(i);
3869
3870 END LOOP; -- End of Main loop
3871
3872
3873 FORALL I in 1 .. final_autopop_rec.array_element_type_id.count
3874 Update psp_adjustment_lines
3875 set
3876 gl_code_combination_id = final_autopop_rec.array_glccid(i),
3877 project_id = final_autopop_rec.array_project_id(i),
3878 expenditure_organization_id = final_autopop_rec.array_exp_org_id(i),
3879 expenditure_type = final_autopop_rec.array_exp_type(i),
3880 task_id = final_autopop_rec.array_task_id(i),
3881 award_id = final_autopop_rec.array_award_id(i)
3882 Where Element_type_id = deferred_autopop_rec.array_element_type_id(i)
3883 And nvl(gl_code_combination_id,0) =nvl(deferred_autopop_rec.array_glccid(i),0)
3884 And nvl(project_id,0) = nvl(deferred_autopop_rec.array_project_id(i),0)
3885 And nvl(expenditure_organization_id,0) =nvl(deferred_autopop_rec.array_exp_org_id(i),0)
3886 And nvl(expenditure_type,0) = nvl(deferred_autopop_rec.array_exp_type(i),0)
3887 And nvl(task_id,0) = nvl(deferred_autopop_rec.array_task_id(i),0)
3888 And nvl(award_id,0) =nvl(deferred_autopop_rec.array_award_id(i),0)
3889 And batch_name = p_batch_name
3890 And business_group_id = p_business_group_id
3891 And set_of_books_id = p_set_of_books_id
3892 AND original_line_flag <> 'Y'; -- Added for Bug 5013847
3893
3894
3895 End if; -- End If for (autopop_defer_cur.count > 0) check
3896
3897
3898 End if; -- end if to for check (p_defer_autopop_param = 'Y')
3899
3900
3901
3902
3903
3904
3905 /* End of code changes for Bug 3548388 */
3906
3907
3908 /* Start of Code Changes for Supercedence check based on migration to new OAF EFfort Reporting */
3909
3910 IF not (l_migration_status) THEN
3911
3912 update_effort_reports(p_batch_name,
3913 p_business_group_id,
3914 p_set_of_books_id,
3915 l_return_status);
3916 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3917 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3918 END IF;
3919
3920
3921 END IF;
3922
3923
3924
3925 /* End of Code Changes for Supercedence check based on migration to new OAF EFfort Reporting */
3926
3927
3928
3929 update_payroll_control(p_batch_name,
3930 l_payroll_control_id,
3931 p_business_group_id,
3932 p_set_of_books_id,
3933 p_currency_code, -- Introduced for bug fix 2916848
3934 l_return_status,
3935 p_gl_posting_override_date);
3936 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3937 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3938 END IF;
3939
3940 delete from psp_temp_orig_lines where run_id = p_run_id;
3941 delete from psp_temp_orig_sumlines where run_id = p_run_id;
3942 delete from psp_temp_dest_sumlines where run_id = p_run_id;
3943
3944 -- 4992668
3945 psp_wf_adj_custom.prorate_dff_hook(p_batch_name,
3946 p_business_group_id,
3947 p_set_of_books_id);
3948 -- Start workflow before commiting
3949 psp_wf_adj_pkg.init_workflow (
3950 p_batch_name,
3951 p_person_id,
3952 p_employee_full_name,
3953 p_assignment_number,
3954 ---p_earnings_element, commented for DA-ENH
3955 p_distribution_start_date,
3956 p_distribution_end_date,
3957 p_currency_code, -- Introduced for bug fix 2916848
3958 p_batch_comments,
3959 p_time_out,
3960 l_wf_ret_status);
3961 if l_wf_ret_status = 0
3962 then
3963 commit;
3964 retcode := 0;
3965 else
3966 raise workflow_failed;
3967 end if;
3968
3969 hr_utility.trace('Procedure OUT parameters :-') ;
3970
3971 hr_utility.trace('OUT errbuf = '||errbuf) ;
3972 hr_utility.trace('OUT retcode = '||retcode) ;
3973 hr_utility.trace('OUT p_adjustment_line_id = '||p_adjustment_line_id) ;
3974 hr_utility.trace('OUT p_element_status = '||p_element_status) ;
3975
3976 hr_utility.trace('EXIT Leaving GENERATE_LINES procedure') ;
3977 hr_utility.trace('**************************************************');
3978
3979 EXCEPTION
3980
3981 WHEN Inv_autopop_element THEN
3982 rollback;
3983 retcode := 7;
3984
3985 WHEN Invalid_ptoe THEN
3986 rollback;
3987 retcode := 8;
3988
3989 WHEN Invalid_ptoea THEN
3990 rollback;
3991 retcode := 9;
3992
3993 WHEN Invalid_gl THEN
3994 rollback;
3995 retcode := 10;
3996
3997 WHEN e_constraint_violation THEN
3998 errbuf := 'In table psp_adjustment_control_table, primary key adjustment_batch_name constraint is violated';
3999 retcode := 4;
4000
4001 WHEN NO_RECORDS_FOUND THEN
4002 rollback;
4003 errbuf := 'No records found:'||g_error_api_path;
4004 retcode := 5;
4005
4006 WHEN workflow_failed THEN
4007 rollback;
4008 errbuf := 'Error in Starting WorkFlow';
4009 retcode := 6;
4010 WHEN batch_net_not_zero then --- added this exception for DA-ENH
4011 rollback;
4012 errbuf := l_element_name;
4013 retcode := 1;
4014
4015 hr_utility.trace('EXCEPTION');
4016 hr_utility.trace('EXC errbuf = '||errbuf) ;
4017 hr_utility.trace('EXC retcode = '||retcode) ;
4018 hr_utility.trace('EXC p_adjustment_line_id = '||p_adjustment_line_id) ;
4019 hr_utility.trace('EXC p_element_status = '||p_element_status) ;
4020 hr_utility.trace('EXC l_element_name = '||l_element_name) ;
4021 hr_utility.trace('EXCEPTION Leaving GENERATE_LINES procedure') ;
4022 hr_utility.trace('**************************************************');
4023
4024 WHEN OTHERS THEN
4025 rollback;
4026 /***** Commented the following DELETE statements for the following reasons as part of R12 performance fixes (bug 4507892)
4027 1) These DELETE statements donot have proper COMMIT logic.
4028 2) Doesnt check for proper run_id.
4029 3) Non-performant SQLs.
4030 delete from psp_temp_orig_lines;
4031 delete from psp_temp_orig_sumlines;
4032 delete from psp_temp_dest_sumlines;
4033 End of comment for bug fix 4507892 *****/
4034 g_error_api_path := 'GENERATE_LINES:'||g_error_api_path||sqlerrm;
4035 fnd_msg_pub.get(p_msg_index => FND_MSG_PUB.G_FIRST,
4036 p_encoded => FND_API.G_FALSE,
4037 p_data => l_msg_data,
4038 p_msg_index_out => l_msg_count);
4039 errbuf := errbuf || l_msg_data || fnd_global.local_chr(10) || g_error_api_path;
4040 retcode := 2;
4041
4042 end generate_lines;
4043
4044 ----------P R O C E D U R E: INSERT_PSP_CLEARING_ACCOUNT-------------------------------
4045 --
4046 --
4047 -- Purpose: This procedure is called by distribution transfers clearing account
4048 -- creation form. The purpose is to create a clearing account
4049 -- to be used by the summarization and transfer concurrent process
4050 -- to post GL balances to when an original GL account line is
4051 -- transfered to a Project account.
4052 ------------------------------------------------------------------------------------
4053
4054 PROCEDURE insert_psp_clearing_account(errbuf OUT NOCOPY VARCHAR2,
4055 retcode OUT NOCOPY VARCHAR2,
4056 p_reversing_gl_ccid IN NUMBER,
4057 p_comments IN VARCHAR2,
4058 p_business_group_id IN NUMBER,
4059 p_set_of_books_id IN NUMBER,
4060 p_payroll_id IN Number,
4061 p_rowid OUT NOCOPY VARCHAR2) IS
4062
4063 v_count number;
4064 account_exists Exception;
4065 cursor c_existing is
4066 select count(*)
4067 from psp_clearing_account
4068 where BUSINESS_GROUP_ID = p_business_group_id
4069 AND SET_OF_BOOKS_ID = p_set_of_books_id
4070 AND PAYROLL_ID = p_payroll_id;
4071 begin
4072 open c_existing ;
4073 fetch c_existing into v_count ;
4074 if v_count <>0 then
4075 close c_existing ;
4076 raise account_exists;
4077 end if;
4078 close c_existing;
4079
4080 insert into psp_clearing_account(reversing_gl_ccid,
4081 comments,
4082 business_group_id,
4083 set_of_books_id,
4084 LAST_UPDATE_DATE,
4085 LAST_UPDATED_BY,
4086 LAST_UPDATE_LOGIN,
4087 CREATED_BY,
4088 CREATION_DATE,
4089 PAYROLL_ID)
4090 values(p_reversing_gl_ccid,
4091 p_comments,
4092 p_business_group_id,
4093 p_set_of_books_id,
4094 sysdate,
4095 fnd_global.user_id,
4096 fnd_global.login_id,
4097 fnd_global.user_id,
4098 sysdate,
4099 p_payroll_id) RETURNING rowid into p_rowid;
4100
4101 EXCEPTION
4102 WHEN account_exists THEN
4103 retcode := 2;
4104 WHEN OTHERS THEN
4105 errbuf:= 'PSP_ADJ_DRIVER : INSERT_PSP_CLEARING_ACCOUNT' || sqlerrm;
4106 retcode := -1;
4107 end insert_psp_clearing_account;
4108 -----------------------------------------------------------------------------------
4109
4110 -----------------------------UPDATE Clearing Account-------------------------------
4111 PROCEDURE update_psp_clearing_account(errbuf OUT NOCOPY VARCHAR2,
4112 retcode OUT NOCOPY VARCHAR2,
4113 p_reversing_gl_ccid IN NUMBER,
4114 p_comments IN VARCHAR2,
4115 p_business_group_id IN NUMBER,
4116 p_set_of_books_id IN NUMBER,
4117 p_payroll_id IN Number,
4118 p_rowid IN VARCHAR2) IS
4119
4120 begin
4121
4122 update psp_clearing_account
4123 set reversing_gl_ccid = p_reversing_gl_ccid,
4124 comments = p_comments,
4125 business_group_id = p_business_group_id,
4126 set_of_books_id = p_set_of_books_id,
4127 LAST_UPDATE_DATE = sysdate,
4128 LAST_UPDATED_BY = fnd_global.user_id,
4129 LAST_UPDATE_LOGIN = fnd_global.login_id,
4130 payroll_id = p_payroll_id
4131 where business_group_id = p_business_group_id
4132 AND set_of_books_id = p_set_of_books_id
4133 AND rowid = p_rowid;
4134
4135 EXCEPTION
4136 WHEN OTHERS THEN
4137 errbuf:= 'PSP_ADJ_DRIVER : UPDATE_PSP_CLEARING_ACCOUNT' || sqlerrm;
4138 retcode := -1;
4139
4140 end update_psp_clearing_account;
4141 ------------------------------------------------------------------------------------
4142
4143 -----------------------------DELETE Clearing Account--------------------------------
4144 PROCEDURE delete_psp_clearing_account(errbuf OUT NOCOPY VARCHAR2,
4145 retcode OUT NOCOPY VARCHAR2,
4146 p_reversing_gl_ccid IN NUMBER,
4147 p_business_group_id IN NUMBER,
4148 p_set_of_books_id IN NUMBER,
4149 p_rowid IN VARCHAR2) IS
4150
4151
4152 begin
4153
4154 delete from psp_clearing_account
4155 where business_group_id = p_business_group_id
4156 and set_of_books_id = p_set_of_books_id
4157 and rowid = p_rowid;
4158
4159 EXCEPTION
4160 WHEN OTHERS THEN
4161 errbuf:= 'PSP_ADJ_DRIVER : DELETE_PSP_CLEARING_ACCOUNT' || sqlerrm;
4162 retcode := -1;
4163
4164 end delete_psp_clearing_account;
4165 ------------------------------------------------------------------------------------
4166
4167 -------------------------------LOCK Clearing Account--------------------------------
4168
4169 PROCEDURE LOCK_ROW_PSP_CLEARING_ACCOUNT (
4170 P_BUSINESS_GROUP_ID IN NUMBER,
4171 P_SET_OF_BOOKS_ID IN NUMBER,
4172 P_REVERSING_GL_CCID IN NUMBER,
4173 P_COMMENTS IN VARCHAR2,
4174 P_PAYROLL_ID IN NUMBER
4175 ) is
4176 cursor c1 is select
4177 BUSINESS_GROUP_ID,
4178 SET_OF_BOOKS_ID,
4179 REVERSING_GL_CCID,
4180 COMMENTS,
4181 PAYROLL_ID
4182 from PSP_CLEARING_ACCOUNT
4183 where business_group_id = p_business_group_id
4184 and set_of_books_id = p_set_of_books_id
4185 and payroll_id = p_payroll_id
4186 and reversing_gl_ccid = p_reversing_gl_ccid
4187 for update nowait;
4188 tlinfo c1%rowtype;
4189 begin
4190 open c1;
4191 fetch c1 into tlinfo;
4192 if (c1%notfound) then
4193 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
4194 app_exception.raise_exception;
4195 close c1;
4196 return;
4197 end if;
4198 close c1;
4199
4200 if ( (tlinfo.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID)
4201 AND (tlinfo.SET_OF_BOOKS_ID = P_SET_OF_BOOKS_ID)
4202 AND (tlinfo.REVERSING_GL_CCID = P_REVERSING_GL_CCID)
4203 AND ((tlinfo.COMMENTS = P_COMMENTS)
4204 OR ((tlinfo.COMMENTS is null)
4205 AND (P_COMMENTS is null)))
4206 AND (tlinfo.PAYROLL_ID = P_PAYROLL_ID)
4207 ) then
4208 null;
4209 else
4210 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
4211 app_exception.raise_exception;
4212 end if;
4213 return;
4214 END LOCK_ROW_PSP_CLEARING_ACCOUNT;
4215 ------------------------------------------------------------------------------------
4216
4217 PROCEDURE undo_adjustment(p_batch_name IN VARCHAR2,
4218 p_business_group_id IN number,
4219 p_set_of_books_id IN number,
4220 p_comments IN VARCHAR2,
4221 errbuf OUT NOCOPY VARCHAR2,
4222 return_code OUT NOCOPY NUMBER)
4223 IS
4224 undo_failed_exception EXCEPTION;
4225
4226 l_orig_line_id psp_adjustment_lines.orig_line_id%TYPE;
4227 l_orig_source_type psp_adjustment_lines.orig_source_type%TYPE;
4228 l_report_id psp_effort_reports.effort_report_id%TYPE;
4229 -- Introduced the following variables for bug fix 2724110
4230 l_person_id NUMBER(15);
4231 l_begin_date DATE;
4232 l_end_date DATE;
4233 l_batch_count NUMBER;
4234
4235 CURSOR get_line_id_csr IS
4236 SELECT orig_line_id,
4237 orig_source_type
4238 FROM psp_adjustment_lines
4239 WHERE original_line_flag = 'Y'
4240 AND batch_name = p_batch_name
4241 and business_group_id = p_business_group_id
4242 and set_of_books_id = p_set_of_books_id;
4243
4244 -- Modified the SELECT stmt of the following cursor for bug fix 2724110
4245 CURSOR get_template_id_csr IS
4246 SELECT report.effort_report_id,
4247 report.person_id,
4248 min(begin_date),
4249 max(end_date)
4250 FROM psp_effort_reports report,
4251 psp_adjustment_lines line,
4252 psp_effort_report_templates template
4253 WHERE line.batch_name = p_batch_name
4254 AND report.person_id = line.person_id
4255 AND template.template_id = report.template_id
4256 AND line.distribution_date BETWEEN template.begin_date AND template.end_date
4257 AND template.report_type = 'N'
4258 AND report.status_code = 'S'
4259 GROUP BY report.effort_report_id,
4260 report.person_id;
4261
4262 /**** Commented the following for bug fix 2724110
4263 SELECT distinct report.effort_report_id
4264 FROM psp_adjustment_lines line,
4265 psp_effort_reports report,
4266 psp_effort_report_templates template
4267 WHERE line.business_group_id = p_business_group_id
4268 and line.set_of_books_id = p_set_of_books_id
4269 and line.batch_name = p_batch_name
4270 AND line.person_id = report.person_id
4271 and line.business_group_id = template.business_group_id
4272 and line.set_of_books_id = template.set_of_books_id
4273 AND template.template_id = report.template_id
4274 AND line.distribution_date BETWEEN template.begin_date AND template.end_date
4275 AND template.report_type = 'N';
4276 End of comment for bug fix ***/
4277
4278 -- Introduced the following cursor for bug fix 2724110
4279 CURSOR addl_adj_batch_count_cur(p_person_id NUMBER,
4280 p_batch_name VARCHAR2,
4281 p_begin_date DATE,
4282 p_end_date DATE) IS
4283 SELECT COUNT(1)
4284 FROM psp_adjustment_lines pal
4285 WHERE person_id = p_person_id
4286 AND batch_name <> p_batch_name
4287 AND distribution_date BETWEEN p_begin_date AND p_end_date
4288 AND EXISTS (SELECT 1
4289 FROM psp_effort_report_elements pere
4290 WHERE pere.element_type_id = pal.element_type_id
4291 AND pere.use_in_effort_report='Y')
4292 AND ROWNUM = 1;
4293
4294 /* Introduced check on migration status before superceding */
4295
4296 l_migration_status BOOLEAN:= psp_general.is_effort_report_migrated;
4297
4298
4299
4300 BEGIN
4301 SAVEPOINT undo_adj_savepoint;
4302
4303 /*---------------------------------------------------------------------*/
4304 /*1. Update one of the history tables PSP_ADJUSTMENT_LINES_HISTORY or */
4305 /* PSP_PRE_GEN_DIST_LINES_HISTORY or PSP_DISTRIBUTION_LINES_HISTORY */
4306 /* by setting the adjustment_batch_name to NULL. */
4307 /*---------------------------------------------------------------------*/
4308 OPEN get_line_id_csr;
4309 LOOP
4310 FETCH get_line_id_csr INTO l_orig_line_id, l_orig_source_type;
4311 EXIT WHEN get_line_id_csr%NOTFOUND;
4312 IF (l_orig_source_type = 'A') THEN
4313 UPDATE psp_adjustment_lines_history
4314 SET adjustment_batch_name = NULL
4315 WHERE adjustment_line_id = l_orig_line_id;
4316 ELSIF (l_orig_source_type = 'P') THEN
4317 UPDATE psp_pre_gen_dist_lines_history
4318 SET adjustment_batch_name = NULL
4319 WHERE pre_gen_dist_line_id = l_orig_line_id;
4320 ELSIF (l_orig_source_type = 'D') THEN
4321 UPDATE psp_distribution_lines_history
4322 SET adjustment_batch_name = NULL
4323 WHERE distribution_line_id = l_orig_line_id;
4324 END IF;
4325 END LOOP;
4326 IF (get_line_id_csr%ROWCOUNT = 0) THEN
4327 errbuf := 'Failed when update history table: no rows found.';
4328 RAISE undo_failed_exception;
4329 END IF;
4330 CLOSE get_line_id_csr;
4331
4332 /*---------------------------------------------------------------------*/
4333 /*2. Update table PSP_EFFORT_REPORTS by setting status_code back to */
4334 /* what it is before the batch is created and flush the */
4335 /* previous_status_code column. */
4336 /*---------------------------------------------------------------------*/
4337 OPEN get_template_id_csr;
4338 LOOP
4339 FETCH get_template_id_csr INTO l_report_id, l_person_id, l_begin_date, l_end_date; -- Intro. addl variables for bug fix 2724110
4340 EXIT WHEN get_template_id_csr%NOTFOUND;
4341 --Introduced the folowing code for bug fix 2724110
4342 OPEN addl_adj_batch_count_cur(l_person_id, p_batch_name, l_begin_date, l_end_date);
4343 FETCH addl_adj_batch_count_cur INTO l_batch_count;
4344 CLOSE addl_adj_batch_count_cur;
4345
4346
4347
4348 -- Introduced check on migration status before reverting back superceded ER
4349
4350 IF not (l_migration_status) THEN
4351 IF (l_batch_count = 0) THEN
4352 UPDATE psp_effort_reports
4353 SET status_code = previous_status_code,
4354 previous_status_code = NULL
4355 WHERE effort_report_id = l_report_id
4356 AND previous_status_code IS NOT NULL;
4357 END IF;
4358 --End of bug fix 2724110
4359
4360
4361 END IF;
4362
4363 /*** Commented for bug fix 2724110
4364 UPDATE psp_effort_reports
4365 SET status_code = previous_status_code
4366 WHERE effort_report_id = l_report_id;
4367
4368 UPDATE psp_effort_reports
4369 SET previous_status_code = NULL
4370 WHERE effort_report_id = l_report_id;
4371 End of comment for bug fix 2724110 ***/
4372 END LOOP;
4373 CLOSE get_template_id_csr;
4374
4375 /*---------------------------------------------------------------------*/
4376 /*3. Delete the record for the rejected batch from PSP_PAYROLL_CONTROL */
4377 /*---------------------------------------------------------------------*/
4378 DELETE FROM psp_payroll_controls
4379 WHERE batch_name = p_batch_name
4380 AND source_type = 'A';
4381 IF (SQL%NOTFOUND) THEN
4382 errbuf := 'Failed when update psp_payroll_control table: no row found.';
4383 RAISE undo_failed_exception;
4384 END IF;
4385
4386 /*---------------------------------------------------------------------*/
4387 /*4. Delete the distribution lines of the rejected batch from table */
4388 /* PSP_ADJUSTMENT_LINES. */
4389 /*---------------------------------------------------------------------*/
4390 DELETE FROM psp_adjustment_lines
4391 WHERE batch_name = p_batch_name;
4392 IF (SQL%NOTFOUND) THEN
4393 errbuf := 'Failed when update psp_adjustment_lines: no row found.';
4394 RAISE undo_failed_exception;
4395 END IF;
4396
4397 /*---------------------------------------------------------------------*/
4398 /*5. Delete the record for the rejected batch from table */
4399 /* PSP_ADJUSTMENT_CONTROL_TABLE. */
4400 /*---------------------------------------------------------------------*/
4401 UPDATE psp_adjustment_control_table
4402 SET void = 'Y',
4403 comments = p_comments
4404 WHERE adjustment_batch_name = p_batch_name;
4405 IF (SQL%NOTFOUND) THEN
4406 errbuf := 'Failed when update psp_adjustment_control_table: no row found.';
4407 RAISE undo_failed_exception;
4408 END IF;
4409
4410 COMMIT;
4411 return_code := 0;
4412
4413 EXCEPTION
4414 WHEN undo_failed_exception THEN
4415 ROLLBACK TO SAVEPOINT undo_adj_savepoint;
4416 return_code := -1;
4417 --Introduced for Bug 2665152
4418 WHEN OTHERS THEN
4419 ROLLBACK TO SAVEPOINT undo_adj_savepoint;
4420 errbuf:= 'PSP_ADJ_DRIVER : UNDO_ADJUSTMENT';
4421 return_code := -1;
4422 END undo_adjustment;
4423 -----------------------------------------------------------------------------
4424
4425
4426 PROCEDURE validate_proj_before_transfer
4427 (p_run_id IN NUMBER,
4428 p_acct_group_id IN NUMBER,
4429 p_person_id IN NUMBER,
4430 p_project_id IN NUMBER,
4431 p_task_id IN NUMBER,
4432 p_award_id IN NUMBER DEFAULT NULL,
4433 p_expenditure_type IN VARCHAR2,
4434 p_expenditure_org_id IN NUMBER,
4435 p_error_flag OUT nocopy VARCHAR2,
4436 p_error_status OUT nocopy VARCHAR2,
4437 p_effective_date OUT nocopy DATE) IS
4438 l_patc_status VARCHAR2(2000);
4439 l_award_status VARCHAR2(2000);
4440 l_billable_flag VARCHAR2(1);
4441 l_msg_count NUMBER;
4442 l_msg_app VARCHAR2(2000);
4443 l_msg_type VARCHAR2(2000);
4444 l_msg_token1 VARCHAR2(2000);
4445 l_msg_token2 VARCHAR2(2000);
4446 l_msg_token3 VARCHAR2(2000);
4447
4448 TYPE t_date_type IS TABLE OF DATE INDEX BY BINARY_INTEGER;
4449 TYPE eff_date_rec_type IS RECORD (l_effective_date t_date_type);
4450 r_eff_date_record eff_date_rec_type;
4451
4452 CURSOR c_orig_values IS
4453 SELECT MAX(ptol.effective_date)
4454 FROM psp_temp_orig_lines ptol
4455 WHERE ptol.acct_group_id = p_acct_group_id
4456 AND ptol.run_id = p_run_id
4457 GROUP BY payroll_control_id,
4458 dr_cr_flag;
4459
4460 -- Introduced for Bug fix 3741272
4461
4462 CURSOR l_exp_org_csr(l_effective_date date)
4463 IS
4464 SELECT 'x'
4465 FROM psp_organizations_expend_v
4466 WHERE organization_id = p_expenditure_org_id
4467 and trunc(l_effective_date) between date_from and nvl(date_to,trunc(l_effective_date));
4468
4469 l_dummy VARCHAR2(1);
4470
4471 -- End of changes for Bug fix 3741272
4472
4473 BEGIN
4474 OPEN c_orig_values ;
4475 FETCH c_orig_values BULK COLLECT INTO
4476 r_eff_date_record.l_effective_date;
4477 CLOSE c_orig_values;
4478
4479 FOR recno IN 1..r_eff_date_record.l_effective_date.COUNT
4480 LOOP
4481 p_error_flag := 'N';
4482 l_patc_status := NULL;
4483 l_award_status := NULL;
4484 pa_transactions_pub.validate_transaction(
4485 x_project_id => p_project_id,
4486 x_task_id => p_task_id,
4487 x_ei_date => r_eff_date_record.l_effective_date(recno),
4488 x_expenditure_type => p_expenditure_type,
4489 x_non_labor_resource => NULL,
4490 x_person_id => p_person_id,
4491 x_incurred_by_org_id => p_expenditure_org_id,
4492 x_calling_module => 'PSPLDTRF',
4493 x_msg_application => l_msg_app,
4494 x_msg_type => l_msg_type,
4495 x_msg_token1 => l_msg_token1,
4496 x_msg_token2 => l_msg_token2,
4497 x_msg_token3 => l_msg_token3,
4498 x_msg_count => l_msg_count,
4499 x_msg_data => l_patc_status,
4500 x_billable_flag => l_billable_flag,
4501 p_sys_link_function => 'ST'); --Bug 5639589: Added parameter
4502
4503 -- Introduced for Bug fix 3741272
4504
4505 If (l_patc_status = 'PA_EXP_ORG_NOT_ACTIVE' ) Then
4506
4507 OPEN l_exp_org_csr(r_eff_date_record.l_effective_date(recno));
4508 FETCH l_exp_org_csr INTO l_dummy;
4509 CLOSE l_exp_org_csr;
4510
4511 if l_dummy = 'x' then
4512 l_patc_status:= NULL ;
4513 else
4514 l_patc_status:= 'PA_EXP_ORG_NOT_ACTIVE' ;
4515 end if ;
4516
4517 End if ;
4518
4519 -- End of changes for Bug fix 3741272
4520
4521 IF l_patc_status IS NOT NULL THEN
4522 p_error_status := l_patc_status;
4523 p_error_flag := 'P';
4524 p_effective_date := r_eff_date_record.l_effective_date(recno);
4525 EXIT;
4526 END IF;
4527
4528 IF p_award_id IS NOT NULL and l_patc_status is null THEN
4529 gms_transactions_pub.validate_transaction (
4530 p_project_id,
4531 p_task_id,
4532 p_award_id,
4533 p_expenditure_type,
4534 r_eff_date_record.l_effective_date(recno),
4535 'PSPLDTRF',
4536 l_award_status);
4537 END IF;
4538
4539 IF l_award_status IS NOT NULL THEN
4540 p_error_flag := 'A';
4541 p_error_status := l_award_status;
4542 p_effective_date := r_eff_date_record.l_effective_date(recno);
4543 EXIT;
4544 END IF;
4545 END LOOP;
4546 END validate_proj_before_transfer;
4547 END psp_adj_driver;