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