[Home] [Help]
PACKAGE BODY: APPS.PA_BC_COSTING
Source
1 PACKAGE BODY pa_bc_costing AS
2 /* $Header: PABCCSTB.pls 120.5 2008/05/15 12:43:55 vchilla ship $ */
3
4 /*
5 * Private Procedures.
6 */
7 PROCEDURE process_rejected_exp_items( x_return_status OUT NOCOPY NUMBER
8 ,x_error_code OUT NOCOPY VARCHAR2
9 ,x_error_stage OUT NOCOPY VARCHAR2
10 );
11
12 PROCEDURE populate_pa_bc_packets( x_return_status OUT NOCOPY NUMBER
13 ,x_error_code OUT NOCOPY VARCHAR2
14 ,x_error_stage OUT NOCOPY VARCHAR2
15 ) ;
16
17 PROCEDURE populate_pa_bc_packets_cwk( x_return_status OUT NOCOPY NUMBER
18 ,x_error_code OUT NOCOPY VARCHAR2
19 ,x_error_stage OUT NOCOPY VARCHAR2
20 ) ;
21
22
23 /*
24 * Package level variables.
25 */
26 g_created_by pa_cost_distribution_lines.created_by%TYPE := FND_GLOBAL.USER_ID;
27 g_last_updated_by pa_expenditure_items.last_updated_by%TYPE := FND_GLOBAL.USER_ID;
28 g_last_update_login pa_expenditure_items.last_update_login%TYPE := FND_GLOBAL.LOGIN_ID;
29 g_request_id pa_cost_distribution_lines.request_id%TYPE ;
30 g_program_application_id pa_cost_distribution_lines.program_application_id%TYPE := FND_GLOBAL.PROG_APPL_ID;
31 g_program_id pa_cost_distribution_lines.program_id%TYPE := FND_GLOBAL.CONC_PROGRAM_ID;
32 g_packet_id pa_bc_packets.packet_id%TYPE;
33 g_sob_id pa_implementations.set_of_books_id%TYPE;
34
35 /*
36 * Package level Pl/Sql Tables.
37 */
38 l_project_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
39 l_task_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
40 l_budget_version_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
41 l_expenditure_item_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
42 l_expenditure_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
43 l_expenditure_item_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
44 l_system_linkage_function_tab PA_PLSQL_DATATYPES.Char30TabTyp;
45 l_pa_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
46 l_gl_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
47 l_funds_process_mode_tab PA_PLSQL_DATATYPES.Char1TabTyp;
48 l_bc_burden_cost_flag_tab PA_PLSQL_DATATYPES.Char1TabTyp;
49 l_exp_organization_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
50 l_document_header_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
51 l_document_line_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
52 l_line_num_tab PA_PLSQL_DATATYPES.NumTabTyp;
53 l_line_type_tab PA_PLSQL_DATATYPES.Char1TabTyp;
54 l_line_num_reversed_tab PA_PLSQL_DATATYPES.NumTabTyp;
55 l_acct_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
56 l_denom_raw_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
57 l_acct_burdened_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
58 l_denom_burdened_cost_tab PA_PLSQL_DATATYPES.NumTabTyp;
59 l_document_distribution_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
60 l_bc_packet_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
61 l_parent_bc_packet_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
62 l_org_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
63 l_burden_sum_rej_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
64 l_burden_sum_source_run_id_tab PA_PLSQL_DATATYPES.Char30TabTyp;
65 l_ind_compiled_set_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
66 l_dr_code_combination_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
67 l_gl_period_name_tab PA_PLSQL_DATATYPES.Char15TabTyp;
68 l_burden_amt_disp_method_tab PA_PLSQL_DATATYPES.Char1TabTyp;
69 l_burden_cost_flag_tab PA_PLSQL_DATATYPES.Char1TabTyp;
70 l_pa_bc_packet_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
71 l_rejn_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
72 l_pkt_reference1_Tab PA_PLSQL_DATATYPES.Char80TabTyp;
73 l_pkt_reference2_Tab PA_PLSQL_DATATYPES.Char80TabTyp;
74 l_pkt_reference3_Tab PA_PLSQL_DATATYPES.Char80TabTyp;
75
76
77 P_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
78
79 PROCEDURE print_msg (l_debug_flag varchar2 default 'N'
80 ,p_msg varchar2) IS
81
82 BEGIN
83 If l_debug_flag = 'Y' Then
84 --dbms_output.put_line('LOG:'||p_msg);
85 PA_DEBUG.write_file('LOG',p_msg);
86 --r_debug.r_msg('LOG:'||p_msg);
87 End If;
88 END print_msg;
89
90 /*
91 * This procedure can be the same for both ER Distribution and VI Adjustment processes.
92 * The following procedure,
93 * 1. Sends Credit/Debit raw lines created during this run into pa_bc_packets.
94 * -- Credit lines in case of reversing CDLs
95 * -- Debit lines in case of new CDLs.
96 * 2. Inserts Credit burden lines for reversing CDLs.
97 * -- FChecked burden amount is,
98 * ---- (cdl.burdened_amount - cdl.amount) for burden_amt_display_method = 'S'.
99 * ---- burden amount derived for burden_amt_display_method = 'D'.
100 * 3. Calls FC API.
101 * 4. Deletes CDLs that were created in this run and failed Funds Check .
102 */
103
104
105 PROCEDURE costing_fc_proc ( p_calling_module IN VARCHAR2
106 ,p_request_id IN NUMBER
107 ,x_return_status OUT NOCOPY NUMBER
108 ,x_error_code OUT NOCOPY VARCHAR2
109 ,x_error_stage OUT NOCOPY NUMBER
110 )
111 IS
112
113
114 /*
115 * Processing related variables.
116 */
117 l_calling_module VARCHAR2(20) ;
118 l_records_affected NUMBER := 0;
119 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
120 l_error_code VARCHAR2(1000);
121 l_error_stage VARCHAR2(1000);
122 l_debug_mode VARCHAR2(1);
123 l_stage NUMBER ;
124 l_bunch_size PLS_INTEGER := 100;
125 l_this_fetch PLS_INTEGER := 0;
126 l_totally_fetched PLS_INTEGER := 0;
127 l_totally_processed PLS_INTEGER := 0;
128 l_ei_to_process_from pa_expenditure_items_all.expenditure_item_id%TYPE := 0;
129
130
131 /*
132 * Cursor Declaration.
133 */
134
135 /*=========================================================+
136 | Burdening Enhancements |
137 | o Funds Check both R and I lines. |
138 | o Transfer Status Code P for R lines and G for I lines. |
139 | Contengent Worker Enhancement |
140 | o Funds Check both R and I lines. |
141 +=========================================================*/
142 /*=============================+
143 | Parent_bc_packet_id. |
144 | o -7777 |
145 | ---- 'BTC' |
146 | o -1 |
147 | ---- Fresh CDLs. |
148 | o NULL |
149 | ---- 'I' lines. |
150 | ---- All others |
151 +=============================*/
152 CURSOR pa_bc_packet_cur
153 IS
154 SELECT cdl.expenditure_item_id
155 ,cdl.line_num
156 ,cdl.line_type
157 ,cdl.line_num_reversed
158 -- ,cdl.acct_raw_cost
159 ,DECODE(ei.system_linkage_function ,'BTC' ,cdl.acct_burdened_cost
160 ,DECODE(cdl.line_type, 'R', cdl.acct_raw_cost, cdl.acct_burdened_cost ))
161 -- ,cdl.denom_raw_cost
162 ,DECODE(ei.system_linkage_function ,'BTC' ,cdl.denom_burdened_cost
163 ,DECODE(cdl.line_type, 'R', cdl.denom_raw_cost, cdl.denom_burdened_cost ))
164 ,cdl.acct_burdened_cost
165 ,cdl.denom_burdened_cost
166 ,cdl.project_id
167 ,cdl.pa_date
168 ,cdl.gl_date
169 ,cdl.burden_sum_rejection_code
170 ,cdl.burden_sum_source_run_id
171 ,cdl.ind_compiled_set_id
172 ,cdl.dr_code_combination_id
173 ,glp.period_name
174 ,ei.expenditure_item_date
175 ,ei.expenditure_type
176 ,ei.task_id
177 ,NVL(ei.override_to_organization_id, exp.incurred_by_organization_id)
178 ,NVL(ei.org_id, -99)
179 ,ei.system_linkage_function
180 ,NVL(pt.burden_amt_display_method, 'S')
181 ,NVL(pt.burden_cost_flag, 'N')
182 ,bv.budget_version_id
183 ,DECODE(ei.system_linkage_function, 'BTC', -7777, DECODE(cdl.line_type, 'I', NULL, DECODE(cdl.line_num_reversed, NULL, -1, NULL)))
184 -- ,cdl.system_reference3 po_line_id -- R12 change
185 ,ei.po_line_id po_line_id -- R12 change
186 ,'EXP' pkt_reference1
187 ,cdl.expenditure_item_id pkt_reference2
188 ,cdl.line_num pkt_reference3
189 FROM pa_expenditure_items_all ei
190 ,pa_cost_distribution_lines_all cdl
191 ,pa_project_types_all pt
192 ,pa_projects_all p
193 ,pa_expenditures exp
194 ,pa_budget_versions bv
195 ,pa_budgetary_control_options pbct
196 ,gl_period_statuses glp
197 WHERE ei.cost_distributed_flag = 'S'
198 AND ei.request_id = g_request_id
199 AND ei.cost_dist_rejection_code IS NULL
200 AND (ei.system_linkage_function IN ('VI') OR
201 (ei.system_linkage_function = 'BTC' AND ei.adjustment_type = 'BURDEN_RESUMMARIZE'))
202 AND ei.expenditure_id = exp.expenditure_id
203 AND ei.expenditure_item_id > l_ei_to_process_from
204 /*
205 * With I lines, this check is no longer valid.
206 * transfer_status_code check is not needed.
207 * AND cdl.transfer_status_code = DECODE(cdl.line_type, 'R', 'P', 'G')
208 */
209 AND cdl.request_id = g_request_id
210 AND cdl.line_type in ('R', 'I')
211 AND cdl.expenditure_item_id = ei.expenditure_item_id
212 AND NVL(cdl.reversed_flag, 'N') <> 'Y'
213 AND cdl.project_id = p.project_id
214 AND p.project_type = pt.project_type
215 --R12 AND NVL(pt.org_Id, -99) = NVL(p.org_Id, -99)
216 AND pt.org_Id = p.org_Id
217 AND glp.application_id = 101
218 AND glp.set_of_books_id = g_sob_id
219 /* AND TRUNC(glp.END_DATE) = TRUNC(cdl.gl_date) Commented for 2843753, 2961161*/
220 /* Added for 2843753,2961161 */
221 AND TRUNC(cdl.gl_date) between TRUNC(glp.START_DATE) and TRUNC(glp.END_DATE)
222 AND pbct.project_id = bv.project_id
223 AND pbct.BDGT_CNTRL_FLAG = 'Y'
224 AND pbct.BUDGET_TYPE_CODE = bv.budget_type_code
225 AND (pbct.EXTERNAL_BUDGET_CODE = 'GL'
226 OR
227 pbct.EXTERNAL_BUDGET_CODE is NULL)
228 AND bv.project_id = cdl.project_id
229 AND bv.current_flag = 'Y'
230 AND bv.budget_status_code = 'B'
231 --FP M changes
232 And adjustment_period_flag = 'N'
233 ORDER BY cdl.expenditure_item_id
234 ,cdl.line_num
235 ;
236
237
238 BEGIN
239 pa_debug.init_err_stack('pa_bc_costing.costing_fc_proc');
240
241 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
242 l_debug_mode := NVL(l_debug_mode, 'Y');
243
244 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
245
246 l_stage := 100;
247 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':From costing_fc_proc';
248 IF P_DEBUG_MODE = 'Y' THEN
249 pa_debug.write_file('costing_fc_proc: ' || pa_debug.g_err_stage);
250 END IF;
251
252 /*
253 * Copy incoming parameters into Local variables.
254 */
255 l_calling_module := p_calling_module ;
256 g_request_id := p_request_id ;
257
258 pa_debug.g_err_stage := 'Request Id is [' || to_char(g_request_id) || ']' ;
259 IF P_DEBUG_MODE = 'Y' THEN
260 pa_debug.write_file('costing_fc_proc: ' || pa_debug.g_err_stage);
261 END IF;
262
263 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':ORACLE error occurred while selecting pa_implementations';
264 /*
265 * Get the sob_id.
266 */
267 SELECT set_of_books_id
268 INTO g_sob_id
269 FROM pa_implementations;
270
271 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':After selecting from pa_implementations. Sob_id is [' || TO_CHAR(g_sob_id) || ']' ;
272 IF P_DEBUG_MODE = 'Y' THEN
273 pa_debug.write_file('costing_fc_proc: ' || pa_debug.g_err_stage);
274 END IF;
275
276 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':ORACLE error occurred Opening pa_bc_packet_cur.';
277
278 /*
279 * Select Expenditure_item_ids to process.
280 *
281 * We should get rid of this sql - because the columns selected here
282 * can be received from the pro*C process as arrays.
283 */
284 l_stage := 200;
285 OPEN pa_bc_packet_cur;
286 /*
287 * Resetting fetch-related variables.
288 */
289 l_this_fetch := 0;
290 l_totally_fetched := 0;
291
292 /*
293 * Loop until all EIs are processed.
294 */
295 LOOP
296
297 PA_DEBUG.g_err_stage := TO_CHAR(l_stage) || ':Fetching a Set of CDLs to Process.';
298 IF P_DEBUG_MODE = 'Y' THEN
299 PA_DEBUG.write_file('costing_fc_proc: ' || PA_DEBUG.g_err_stage);
300 END IF;
301
302 l_stage := 300;
303 FETCH pa_bc_packet_cur
304 BULK COLLECT
305 INTO l_expenditure_item_id_tab
306 ,l_line_num_tab
307 ,l_line_type_tab
308 ,l_line_num_reversed_tab
309 ,l_acct_raw_cost_tab
310 ,l_denom_raw_cost_tab
311 ,l_acct_burdened_cost_tab
312 ,l_denom_burdened_cost_tab
313 ,l_project_id_tab
314 ,l_pa_date_tab
315 ,l_gl_date_tab
316 ,l_burden_sum_rej_code_tab
317 ,l_burden_sum_source_run_id_tab
318 ,l_ind_compiled_set_id_tab
319 ,l_dr_code_combination_id_tab
320 ,l_gl_period_name_tab
321 ,l_expenditure_item_date_tab
322 ,l_expenditure_type_tab
323 ,l_task_id_tab
324 ,l_exp_organization_id_tab
325 ,l_org_id_tab
326 ,l_system_linkage_function_tab
327 ,l_burden_amt_disp_method_tab
328 ,l_burden_cost_flag_tab
329 ,l_budget_version_id_tab
330 ,l_parent_bc_packet_id_tab
331 ,l_document_line_id_tab
332 ,l_pkt_reference1_Tab
333 ,l_pkt_reference2_Tab
334 ,l_pkt_reference3_Tab
335 LIMIT l_bunch_size;
336
337 /*==========================================+
338 | Once fetched, reset l_ei_to_process_from |
339 +==========================================*/
340 l_ei_to_process_from := 0;
341
342 l_this_fetch := pa_bc_packet_cur%ROWCOUNT - l_totally_fetched;
343 l_totally_fetched := pa_bc_packet_cur%ROWCOUNT;
344 l_totally_processed := l_totally_processed + l_this_fetch;
345
346 PA_DEBUG.g_err_stage := TO_CHAR(l_stage) || ':Fetched [' || l_this_fetch || '] CDL(s) to process.';
347 IF P_DEBUG_MODE = 'Y' THEN
348 PA_DEBUG.write_file('costing_fc_proc: ' || PA_DEBUG.g_err_stage);
349 END IF;
350
351 IF (l_this_fetch = 0) THEN
352 PA_DEBUG.g_err_stage := TO_CHAR(l_stage) || ':No more CDL(s) to process. Exiting';
353 IF P_DEBUG_MODE = 'Y' THEN
354 PA_DEBUG.write_file('costing_fc_proc: ' || PA_DEBUG.g_err_stage);
355 END IF;
356 x_return_status := 0;
357 x_error_code := FND_API.G_RET_STS_SUCCESS;
358 x_error_stage := l_stage;
359 EXIT;
360 END IF;
361 /*
365 * -- If the nth CDL is a reversing one, (line_num_reversed <> NULL)
362 * We got to ensure that all cdls of an ei end-up in the same packet.
363 * For this, we are ordering the cursor by eiid and line_num.
364 * Now we have fetched n number of CDLs.
366 * then there should be a fresh CDL which we are missing. So,
367 * get that and append it to the current pl/sql table. And ensure
368 * that we dont get any cdl of this ei during the next fetch.
369 * Assumption#1:- In-case of reversing CDL, Line_num for the fresh CDL
370 * is greater than the line_num of he reversing CDL.
371 */
372 IF (l_line_num_reversed_tab(l_this_fetch) IS NOT NULL )
373 THEN
374 /*
375 * Get the Fresh line.
376 */
377 /*=========================================================+
378 | Burdening Enhancements |
379 | o Funds Check both R and I lines. |
380 | o Transfer Status Code P for R lines and G for I lines. |
381 +=========================================================*/
382 PA_DEBUG.g_err_stage := TO_CHAR(l_stage) || ':Fresh line Missing. Selecting Fresh line.';
383 IF P_DEBUG_MODE = 'Y' THEN
384 PA_DEBUG.write_file('costing_fc_proc: ' || PA_DEBUG.g_err_stage);
385 END IF;
386
387 l_stage := 400;
388 SELECT cdl.expenditure_item_id
389 ,cdl.line_num
390 ,cdl.line_type
391 ,cdl.line_num_reversed
392 --,cdl.acct_raw_cost
393 ,DECODE(cdl.line_type, 'R', cdl.acct_raw_cost, cdl.acct_burdened_cost)
394 --,cdl.denom_raw_cost
395 ,DECODE(cdl.line_type, 'R', cdl.denom_raw_cost, cdl.denom_burdened_cost)
396 ,cdl.acct_burdened_cost
397 ,cdl.denom_burdened_cost
398 ,cdl.project_id
399 ,cdl.pa_date
400 ,cdl.gl_date
401 ,cdl.burden_sum_rejection_code
402 ,cdl.burden_sum_source_run_id
403 ,cdl.ind_compiled_set_id
404 ,cdl.dr_code_combination_id
405 ,glp.period_name
406 ,l_expenditure_item_date_tab(l_this_fetch)
407 ,l_expenditure_type_tab(l_this_fetch)
408 ,l_task_id_tab(l_this_fetch)
409 ,l_exp_organization_id_tab(l_this_fetch)
410 ,l_org_id_tab(l_this_fetch)
411 ,NVL(pt.burden_amt_display_method, 'S')
412 ,NVL(pt.burden_cost_flag, 'N')
413 ,bv.budget_version_id
414 ,DECODE(l_system_linkage_function_tab(l_this_fetch), 'BTC', -7777, DECODE(cdl.line_type, 'I', NULL, DECODE(cdl.line_num_reversed, NULL, -1, NULL)))
415 -- ,cdl.system_reference3 po_line_id -- R12 change
416 ,(select ei.po_line_id
417 from pa_expenditure_items_all ei
418 where ei.expenditure_item_id = cdl.expenditure_item_id
419 ) po_line_id -- R12 change
420 ,'EXP'
421 ,cdl.expenditure_item_id
422 ,cdl.line_num
423 INTO l_expenditure_item_id_tab(l_this_fetch+1)
424 ,l_line_num_tab(l_this_fetch+1)
425 ,l_line_type_tab(l_this_fetch+1)
426 ,l_line_num_reversed_tab(l_this_fetch+1)
427 ,l_acct_raw_cost_tab(l_this_fetch+1)
428 ,l_denom_raw_cost_tab(l_this_fetch+1)
429 ,l_acct_burdened_cost_tab(l_this_fetch+1)
430 ,l_denom_burdened_cost_tab(l_this_fetch+1)
431 ,l_project_id_tab(l_this_fetch+1)
432 ,l_pa_date_tab(l_this_fetch+1)
433 ,l_gl_date_tab(l_this_fetch+1)
434 ,l_burden_sum_rej_code_tab(l_this_fetch+1)
435 ,l_burden_sum_source_run_id_tab(l_this_fetch+1)
436 ,l_ind_compiled_set_id_tab(l_this_fetch+1)
437 ,l_dr_code_combination_id_tab(l_this_fetch+1)
438 ,l_gl_period_name_tab(l_this_fetch+1)
439 ,l_expenditure_item_date_tab(l_this_fetch+1)
440 ,l_expenditure_type_tab(l_this_fetch+1)
441 ,l_task_id_tab(l_this_fetch+1)
442 ,l_exp_organization_id_tab(l_this_fetch+1)
443 ,l_org_id_tab(l_this_fetch+1)
444 ,l_burden_amt_disp_method_tab(l_this_fetch+1)
445 ,l_burden_cost_flag_tab(l_this_fetch+1)
446 ,l_budget_version_id_tab(l_this_fetch+1)
447 ,l_parent_bc_packet_id_tab(l_this_fetch+1)
448 ,l_document_line_id_tab(l_this_fetch+1)
449 ,l_pkt_reference1_tab(l_this_fetch+1)
450 ,l_pkt_reference2_tab(l_this_fetch+1)
451 ,l_pkt_reference3_tab(l_this_fetch+1)
452 FROM pa_cost_distribution_lines_all cdl
453 ,pa_project_types_all pt
454 ,pa_projects_all p
455 ,pa_budget_versions bv
456 ,pa_budgetary_control_options pbct
457 ,gl_period_statuses glp
458 WHERE
459 /*
460 * With I lines, this check is no longer valid.
461 * transfer_status_code check is not needed.
462 * cdl.transfer_status_code = decode(cdl.line_type, 'R', 'P', 'G')
463 */
464 cdl.line_num_reversed IS NULL -- ensures fresh line.
465 AND cdl.reversed_flag IS NULL -- ensures fresh line.
466 AND cdl.request_id = g_request_id
470 AND p.project_type = pt.project_type
467 AND cdl.line_type in ('R', 'I')
468 AND cdl.expenditure_item_id = l_expenditure_item_id_tab(l_this_fetch)
469 AND p.project_id = cdl.project_id
471 -- R12 AND NVL(pt.org_Id, -99) = NVL(p.org_Id, -99)
472 AND pt.org_Id = p.org_Id
473 AND glp.application_id = 101
474 AND glp.set_of_books_id = g_sob_id
475 /* AND TRUNC(glp.END_DATE) = TRUNC(cdl.gl_date) Commented for 2843753, 2961161*/
476 /* Added for 2843753,2961161 */
477 AND TRUNC(cdl.gl_date) between TRUNC(glp.START_DATE) and TRUNC(glp.END_DATE)
478 AND pbct.project_id = bv.project_id
479 AND pbct.BDGT_CNTRL_FLAG = 'Y'
480 AND pbct.BUDGET_TYPE_CODE = bv.budget_type_code
481 AND (pbct.EXTERNAL_BUDGET_CODE = 'GL'
482 OR
483 pbct.EXTERNAL_BUDGET_CODE is NULL)
484 AND bv.project_id = cdl.project_id
485 AND bv.current_flag = 'Y'
486 AND bv.budget_status_code = 'B'
487 --FP M changes
488 And adjustment_period_flag = 'N'
489 ;
490
491 l_totally_processed := l_totally_processed + 1;
492 l_ei_to_process_from := l_expenditure_item_id_tab(l_this_fetch);
493
494 IF (l_debug_mode = 'Y')
495 THEN
496 pa_debug.g_err_stage := ' l_ei_to_process_from is [' || to_char(l_ei_to_process_from) || ']';
497 pa_debug.write_file('costing_fc_proc: ' || pa_debug.g_err_stage);
498 END IF;
499
500 IF (l_debug_mode = 'Y')
501 THEN
502 pa_debug.g_err_stage := 'Fresh cdl [' || l_expenditure_item_id_tab(l_this_fetch+1) ||
503 '] line_num [' || l_line_num_tab(l_this_fetch+1) ||
504 '] line_type [' || l_line_type_tab(l_this_fetch+1) ||
505 '] line_num_reversed [' || l_line_num_reversed_tab(l_this_fetch+1) ||
506 '] p_id [' || l_project_id_tab(l_this_fetch+1) ||
507 '] pa_date [' || l_pa_date_tab(l_this_fetch+1) ||
508 '] gl_date [' || l_gl_date_tab(l_this_fetch+1) ||
509 '] acct_rc [' || l_acct_raw_cost_tab(l_this_fetch+1) ||
510 '] denom_rc [' || l_denom_raw_cost_tab(l_this_fetch+1) ||
511 '] acct_bc [' || l_acct_burdened_cost_tab(l_this_fetch+1) ||
512 '] burden_sum_rej_code [' || l_burden_sum_rej_code_tab(l_this_fetch+1) ||
513 '] bssrid [' || l_burden_sum_source_run_id_tab(l_this_fetch+1) ||
514 '] comp_set_id [' || l_ind_compiled_set_id_tab(l_this_fetch+1) ||
515 '] parent [' || l_parent_bc_packet_id_tab(l_this_fetch+1) ||
516 ']';
517 IF P_DEBUG_MODE = 'Y' THEN
518 pa_debug.write_file('costing_fc_proc: ' || pa_debug.g_err_stage);
519 END IF;
520 pa_debug.g_err_stage := 'dr_ccid [' || l_dr_code_combination_id_tab(l_this_fetch+1) ||
521 '] gl_p_name [' || l_gl_period_name_tab(l_this_fetch+1) ||
522 '] etype [' || l_expenditure_type_tab(l_this_fetch+1) ||
523 '] task_id [' || l_task_id_tab(l_this_fetch+1) ||
524 '] eorg_id [' || l_exp_organization_id_tab(l_this_fetch+1) ||
525 '] org_id [' || l_org_id_tab(l_this_fetch+1) ||
526 '] b_dsp_meth [' || l_burden_amt_disp_method_tab(l_this_fetch+1) ||
527 '] b_version_id [' || l_budget_version_id_tab(l_this_fetch+1) ||
528 '] burdened [' || l_burden_cost_flag_tab(l_this_fetch+1) ||
529 '] doc_line_id [' || to_char(l_document_line_id_tab(l_this_fetch+1)) ||
530 ']';
531 IF P_DEBUG_MODE = 'Y' THEN
532 pa_debug.write_file('costing_fc_proc: ' || pa_debug.g_err_stage);
533 END IF;
534 END IF; -- debug mode?
535
536
537 END IF; -- is the nth cdl a reversing one?
538
539 /*
540 * Printing fetched values.
541 */
542 IF (l_debug_mode = 'Y')
543 THEN
544 FOR i IN l_expenditure_item_id_tab.FIRST .. l_expenditure_item_id_tab.LAST
545 LOOP
546 pa_debug.g_err_stage := 'eiid [' || l_expenditure_item_id_tab(i) ||
547 '] sys_link [' || l_system_linkage_function_tab(i) ||
548 '] line_num [' || l_line_num_tab(i) ||
549 '] line_type [' || l_line_type_tab(i) ||
550 '] line_num_reversed [' || l_line_num_reversed_tab(i) ||
551 '] p_id [' || l_project_id_tab(i) ||
552 '] pa_date [' || l_pa_date_tab(i) ||
553 '] gl_date [' || l_gl_date_tab(i) ||
554 '] acct_rc [' || l_acct_raw_cost_tab(i) ||
555 '] denom_rc [' || l_denom_raw_cost_tab(i) ||
556 '] acct_bc [' || l_acct_burdened_cost_tab(i) ||
557 '] burden_sum_rej_code [' || l_burden_sum_rej_code_tab(i) ||
558 '] bssrid [' || l_burden_sum_source_run_id_tab(i) ||
559 '] comp_set_id [' || l_ind_compiled_set_id_tab(i) ||
563 '] task_id [' || l_task_id_tab(i) ||
560 '] dr_ccid [' || l_dr_code_combination_id_tab(i) ||
561 '] gl_p_name [' || l_gl_period_name_tab(i) ||
562 '] etype [' || l_expenditure_type_tab(i) ||
564 '] eorg_id [' || l_exp_organization_id_tab(i) ||
565 '] org_id [' || l_org_id_tab(i) ||
566 '] sys_link [' || l_system_linkage_function_tab(i) ||
567 '] b_dsp_meth [' || l_burden_amt_disp_method_tab(i) ||
568 '] b_version_id [' || l_budget_version_id_tab(i) ||
569 '] parent_pkt_id [' || l_parent_bc_packet_id_tab(i) ||
570 '] doc_line_id [' || to_char(l_document_line_id_tab(i)) ||
571 '] burdened [' || l_burden_cost_flag_tab(i) || ']';
572 IF P_DEBUG_MODE = 'Y' THEN
573 pa_debug.write_file('costing_fc_proc: ' || pa_debug.g_err_stage);
574 END IF;
575 END LOOP;
576 END IF; -- debug mode?
577
578 /*
579 * Get the Packet_id
580 */
581 PA_DEBUG.g_err_stage := TO_CHAR(l_stage) || ': Getting the packet_id.';
582 IF P_DEBUG_MODE = 'Y' THEN
583 PA_DEBUG.write_file('costing_fc_proc: ' || PA_DEBUG.g_err_stage);
584 END IF;
585
586 l_stage := 500;
587 SELECT gl_bc_packets_s.NEXTVAL
588 INTO g_packet_id
589 FROM dual;
590
591 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':Packet_id is [' || TO_CHAR(g_packet_id) || ']' ;
592 IF P_DEBUG_MODE = 'Y' THEN
593 pa_debug.write_file('costing_fc_proc: ' || pa_debug.g_err_stage);
594 END IF;
595
596 /*
597 * Call Autonomous Procedure to insert the pl/sql tables into pa_bc_packets.
598 */
599 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':Before Calling populate_pa_bc_packets.';
600 IF P_DEBUG_MODE = 'Y' THEN
601 pa_debug.write_file('costing_fc_proc: ' || pa_debug.g_err_stage);
602 END IF;
603
604 l_stage := 600;
605 populate_pa_bc_packets( l_return_status
606 ,l_error_code
607 ,l_error_stage
608 );
609
610
611 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':After calling populate_pa_bc_packets l_return_status = [' ||
612 l_return_status || '] l_error_stage = [' || l_error_stage ||
613 '] l_error_code = [' || l_error_code || ']' ;
614 IF P_DEBUG_MODE = 'Y' THEN
615 pa_debug.write_file('costing_fc_proc: ' || pa_debug.g_err_stage);
616 END IF;
617
618 /*
619 * Check l_return_status,l_error_code,l_error_stage and take appropriate action.
620 */
621 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
622 THEN
623 pa_debug.g_err_stage := 'Error occurred while call to populate_pa_bc_packets. x_return_status [' ||
624 l_return_status || ']';
625 IF P_DEBUG_MODE = 'Y' THEN
626 pa_debug.write_file('costing_fc_proc: ' || pa_debug.g_err_stage);
627 END IF;
628 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
629 END IF;
630
631 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':After Inserting Records into PA_BC_PACKETS.';
632 IF P_DEBUG_MODE = 'Y' THEN
633 pa_debug.write_file('costing_fc_proc: ' || pa_debug.g_err_stage);
634 END IF;
635
636 /*
637 * Call FC API here.
638 */
639 pa_debug.g_err_stage := TO_CHAR(l_stage) || ': Calling FC API';
640 IF P_DEBUG_MODE = 'Y' THEN
641 pa_debug.write_file('costing_fc_proc: ' || pa_debug.g_err_stage);
642 END IF;
643
644 pa_debug.g_err_stage := 'Error Occurred during call to pa_funds_check.';
645 /*
646 * Call the FC API.
647 */
648 l_stage := 700;
649 IF( NOT pa_funds_control_pkg.pa_funds_check( l_calling_module -- p_calling_module
650 ,'Y' -- p_conc_flag
651 ,g_sob_id -- p_set_of_book_id
652 ,g_packet_id -- p_packet_id
653 ,'R' -- p_mode
654 ,'Y' -- p_partial_flag
655 ,NULL -- p_reference1
656 ,NULL -- p_reference2
657 ,NULL -- p_reference3
658 ,l_return_status -- x_return_status
659 ,l_error_stage -- x_error_stage
660 ,l_error_code -- x_error_msg
661 ) )
662 THEN
663 pa_debug.g_err_stage := 'pa_funds_check returned FALSE.';
664 IF P_DEBUG_MODE = 'Y' THEN
665 pa_debug.write_file('costing_fc_proc: ' || pa_debug.g_err_stage);
666 END IF;
667
668 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
669 END IF;
670
671 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':After calling FC API l_return_status =[' || l_return_status ||
672 '] l_error_stage = [' || l_error_stage ||
673 '] l_error_code = [' || l_error_code || ']' ;
674 IF P_DEBUG_MODE = 'Y' THEN
678 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':After calling FC API' ;
675 pa_debug.write_file('costing_fc_proc: ' || pa_debug.g_err_stage);
676 END IF;
677
679 IF P_DEBUG_MODE = 'Y' THEN
680 pa_debug.write_file('costing_fc_proc: ' || pa_debug.g_err_stage);
681 END IF;
682
683
684 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':Calling process_rejected_exp_items' ;
685 IF P_DEBUG_MODE = 'Y' THEN
686 pa_debug.write_file('costing_fc_proc: ' || pa_debug.g_err_stage);
687 END IF;
688
689 l_stage := 800;
690 process_rejected_exp_items ( x_return_status => l_return_status
691 ,x_error_code => l_error_code
692 ,x_error_stage => l_error_stage
693 );
694
695 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
696 THEN
697 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':Error occurred while call to process_rejected_exp_items. x_return_status [' || l_return_status || ']';
698 IF P_DEBUG_MODE = 'Y' THEN
699 pa_debug.write_file('costing_fc_proc: ' || pa_debug.g_err_stage);
700 END IF;
701 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
702 END IF;
703
704 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':After calling process_rejected_exp_items l_return_status =[' ||
705 l_return_status ||
706 '] l_error_stage = [' || l_error_stage ||
707 '] l_error_code = [' || l_error_code || ']' ;
708 IF P_DEBUG_MODE = 'Y' THEN
709 pa_debug.write_file('costing_fc_proc: ' || pa_debug.g_err_stage);
710 END IF;
711
712 /*
713 * Calling FC ends here.
714 */
715 IF (l_this_fetch < l_bunch_size) THEN
716 /*
717 * Indicates last fetch.
718 */
719 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':Finished Processing Last Fetch.';
720 IF P_DEBUG_MODE = 'Y' THEN
721 pa_debug.write_file('costing_fc_proc: ' || pa_debug.g_err_stage);
722 END IF;
723 EXIT;
724 END IF;
725 /** deleting plsql tables **/
726
727 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':Deleting Pl/Sql tables......';
728 IF P_DEBUG_MODE = 'Y' THEN
729 pa_debug.write_file('costing_fc_proc: ' || pa_debug.g_err_stage);
730 END IF;
731
732 l_stage := 900;
733 l_expenditure_item_id_tab.DELETE;
734 l_line_num_tab.DELETE;
735 l_line_type_tab.DELETE;
736 l_line_num_reversed_tab.DELETE;
737 l_acct_raw_cost_tab.DELETE;
738 l_denom_raw_cost_tab.DELETE;
739 l_acct_burdened_cost_tab.DELETE;
740 l_denom_burdened_cost_tab.DELETE;
741 l_project_id_tab.DELETE;
742 l_pa_date_tab.DELETE;
743 l_gl_date_tab.DELETE;
744 l_burden_sum_rej_code_tab.DELETE;
745 l_burden_sum_source_run_id_tab.DELETE;
746 l_ind_compiled_set_id_tab.DELETE;
747 l_dr_code_combination_id_tab.DELETE;
748 l_gl_period_name_tab.DELETE;
749 l_expenditure_item_date_tab.DELETE;
750 l_expenditure_type_tab.DELETE;
751 l_task_id_tab.DELETE;
752 l_exp_organization_id_tab.DELETE;
753 l_org_id_tab.DELETE;
754 l_burden_amt_disp_method_tab.DELETE;
755 l_burden_cost_flag_tab.DELETE;
756 l_budget_version_id_tab.DELETE;
757 l_pkt_reference1_Tab.DELETE;
758 l_pkt_reference2_Tab.DELETE;
759 l_pkt_reference3_Tab.DELETE;
760
761 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':After Deleting Pl/Sql tables......';
762 IF P_DEBUG_MODE = 'Y' THEN
763 pa_debug.write_file('costing_fc_proc: ' || pa_debug.g_err_stage);
764 END IF;
765
766 /** deleting plsql tables **/
767 /*=================================================================+
768 | If earlier fetch had a spill-over, close and reopen the cursor. |
769 +=================================================================*/
770
771 IF (l_ei_to_process_from > 0)
772 THEN
773 IF P_DEBUG_MODE = 'Y' THEN
774 pa_debug.g_err_stage := TO_CHAR(l_stage) || 'closing cursor';
775 pa_debug.write_file('costing_fc_proc: ' || pa_debug.g_err_stage);
776 END if;
777 CLOSE pa_bc_packet_cur;
778
779 l_this_fetch := 0;
780 l_totally_fetched := 0;
781 IF P_DEBUG_MODE = 'Y' THEN
782 pa_debug.g_err_stage := TO_CHAR(l_stage) || 'opening cursor - to process from [' || to_char(l_ei_to_process_from) || ']';
783 pa_debug.write_file('costing_fc_proc: ' || pa_debug.g_err_stage);
784 END IF;
785 OPEN pa_bc_packet_cur;
786 END IF;
787
788 END LOOP; -- End of loop to insert total number records.
789
790 pa_debug.g_err_stage := TO_CHAR(l_stage) || ': No. Of CDLs Totally fetched [' || TO_CHAR(l_totally_fetched) || ']' ;
791 IF P_DEBUG_MODE = 'Y' THEN
792 pa_debug.write_file('costing_fc_proc: ' || pa_debug.g_err_stage);
793 END IF;
794
795 IF ( l_calling_module = 'DISTBTC')
796 THEN
797 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':Calling map_btc_items' ;
798 IF P_DEBUG_MODE = 'Y' THEN
799 pa_debug.write_file('costing_fc_proc: ' || pa_debug.g_err_stage);
800 END IF;
801 pa_bc_costing.map_btc_items ( p_request_id => g_request_id
805 );
802 ,x_return_status => l_return_status
803 ,x_error_code => l_error_code
804 ,x_error_stage => l_error_stage
806
807 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
808 THEN
809 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':Error occurred while call to map_btc_items. x_return_status [' || l_return_status || ']';
810 IF P_DEBUG_MODE = 'Y' THEN
811 pa_debug.write_file('costing_fc_proc: ' || pa_debug.g_err_stage);
812 END IF;
813 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
814 END IF;
815
816 pa_debug.g_err_stage := TO_CHAR(l_stage) ||
817 ':After calling map_btc_items l_return_status =[' ||
818 l_return_status ||
819 '] l_error_stage = [' || l_error_stage ||
820 '] l_error_code = [' || l_error_code || ']' ;
821 IF P_DEBUG_MODE = 'Y' THEN
822 pa_debug.write_file('costing_fc_proc: ' || pa_debug.g_err_stage);
823 END IF;
824 END IF; -- DISTBTC
825
826 pa_debug.g_err_stage := TO_CHAR(l_stage) || ': Leaving costing_fc_proc' ;
827 IF P_DEBUG_MODE = 'Y' THEN
828 pa_debug.write_file('costing_fc_proc: ' || pa_debug.g_err_stage);
829 END IF;
830
831 x_return_status := 0;
832 pa_debug.reset_err_stack;
833
834 EXCEPTION
835 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
836 THEN
837 IF P_DEBUG_MODE = 'Y' THEN
838 pa_debug.write_file('costing_fc_proc: ' || pa_debug.g_err_stage);
839 END IF;
840 x_return_status := -1;
841 x_error_code := pa_debug.g_err_stage ;
842 x_error_stage := to_char(l_stage) ;
843 WHEN OTHERS
844 THEN
845 IF P_DEBUG_MODE = 'Y' THEN
846 pa_debug.write_file('costing_fc_proc: EXCEPTION ' || pa_debug.g_err_stage);
847 END IF;
848
849 pa_debug.g_err_stage := TO_CHAR(SQLCODE) || SQLERRM ;
850 IF P_DEBUG_MODE = 'Y' THEN
851 pa_debug.write_file('costing_fc_proc: EXCEPTION ' || pa_debug.g_err_stage);
852 END IF;
853
854 x_return_status := -1;
855 x_error_code := TO_CHAR(SQLCODE) || SQLERRM ;
856 x_error_stage := l_stage ;
857 --RAISE;
858 END costing_fc_proc;
859
860 --------------------------------------------------------------------------------------
861
862 /*
863 * The following procedure resource-maps the BTC items and stamps the
864 * budget_ccid and other FC related columns in CDL.
865 */
866 PROCEDURE map_btc_items ( p_request_id IN NUMBER
867 ,x_return_status OUT NOCOPY NUMBER
868 ,x_error_code OUT NOCOPY VARCHAR2
869 ,x_error_stage OUT NOCOPY VARCHAR2
870 )
871 IS
872 /*
873 * Table to store the rejection code - if mapping fails.
874 * Value will be NULL if the CDL was successfully mapped.
875 */
876 l_expenditure_item_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
877 l_budget_ccid_tab PA_PLSQL_DATATYPES.IdTabTyp;
878 l_cost_dist_rejection_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
879 l_line_num_tab PA_PLSQL_DATATYPES.NumTabTyp;
880 l_cdl_rowid_tab PA_PLSQL_DATATYPES.RowidTabTyp;
881 l_encum_type_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
882 --r12
883 l_budget_line_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
884 l_budget_ver_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
885
886 /* declare set of plsql tables required for New Resource Map api*/
887 l_resmap_exp_item_id PA_PLSQL_DATATYPES.IdTabTyp;
888 l_resmap_request_id PA_PLSQL_DATATYPES.IdTabTyp;
889 l_resmap_project_id PA_PLSQL_DATATYPES.IdTabTyp;
890 l_resmap_line_num PA_PLSQL_DATATYPES.IdTabTyp;
891 l_resmap_cdl_rowid PA_PLSQL_DATATYPES.RowidTabTyp;
892 l_resmap_task_id PA_PLSQL_DATATYPES.IdTabTyp;
893 l_resmap_top_task_id PA_PLSQL_DATATYPES.IdTabTyp;
894 l_resmap_person_id PA_PLSQL_DATATYPES.IdTabTyp;
895 l_resmap_organization_id PA_PLSQL_DATATYPES.IdTabTyp;
896 l_resmap_job_id PA_PLSQL_DATATYPES.IdTabTyp;
897 l_resmap_exp_type PA_PLSQL_DATATYPES.Char150TabTyp;
898 l_resmap_exp_category PA_PLSQL_DATATYPES.Char150TabTyp;
899 l_resmap_sys_link_func PA_PLSQL_DATATYPES.Char150TabTyp;
900 l_resmap_gl_start_date PA_PLSQL_DATATYPES.DateTabTyp;
901 l_resmap_encum_type_id PA_PLSQL_DATATYPES.IdTabTyp;
902 l_resmap_vendor_id PA_PLSQL_DATATYPES.IdTabTyp;
903 l_resmap_budget_version_id PA_PLSQL_DATATYPES.IdTabTyp;
904 l_resmap_resource_list_id PA_PLSQL_DATATYPES.IdTabTyp;
905 l_resmap_entry_level_code PA_PLSQL_DATATYPES.Char30TabTyp;
906
907 l_bdgt_resource_list_id PA_PLSQL_DATATYPES.IdTabTyp;
908
909 l_counter NUMBER := 0;
910 l_debug_mode VARCHAR2(1) := 'N';
911 l_records_processed NUMBER := 0;
912 g_request_id NUMBER := 0;
913 l_debug_stage VARCHAR2(2000);
914
915 l_prev_project_id pa_cost_distribution_lines.project_id%TYPE;
916 l_prev_flag VARCHAR2(1);
917 l_budget_version_id pa_bc_packets.budget_version_id%TYPE;
921 l_budget_ccid pa_cost_distribution_lines.budget_ccid%TYPE;
918 l_resource_list_id pa_budget_versions.resource_list_id%TYPE;
919 l_entry_level_code pa_budget_versions.budget_entry_method_code%TYPE;
920 l_resource_list_member_id pa_bc_packets.resource_list_member_id%TYPE;
922 l_budget_line_id pa_cost_distribution_lines.budget_line_id%TYPE;
923 l_prev_reslist_id Number;
924 l_fnd_reqd_flag VARCHAR2(1);
925
926 l_return_status VARCHAR2(1);
927 l_stage NUMBER;
928 l_error_code VARCHAR2(1000);
929 l_error_stage VARCHAR2(1000);
930 l_resRecCount NUMBER := 0;
931 l_reslistCount NUMBER := 0;
932
933
934 CURSOR btc_cdl_cur IS
935 SELECT btc_cdl.expenditure_item_id expenditure_item_id
936 ,btc_cdl.project_id project_id
937 ,btc_cdl.line_num line_num
938 ,btc_cdl.cdl_rowid cdl_rowid
939 ,btc_cdl.task_id task_id
940 ,btc_cdl.top_task_id top_task_id
941 ,btc_cdl.person_id person_id
942 ,btc_cdl.organization_id organization_id
943 ,btc_cdl.job_id job_id
944 ,btc_cdl.expenditure_type expenditure_type
945 ,btc_cdl.expenditure_category expenditure_category
946 ,btc_cdl.system_linkage_function system_linkage_function
947 ,btc_cdl.gl_start_date gl_start_date
948 ,btc_cdl.encum_type_id encum_type_id
949 ,btc_cdl.vendor_id vendor_id
950 ,resmap.system_reference4 budget_version_id
951 ,resmap.resource_list_id resource_list_id
952 ,resmap.resource_list_member_id resource_list_member_id
953 ,btc_cdl.entry_level_code entry_level_code
954 ,btc_cdl.po_line_id po_line_id
955 ,btc_cdl.system_reference2 po_header_id
956 ,decode(btc_cdl.burden_amt_disp_method,'D','BURDEN','RAW') pkt_line_type
957 --FP M changes
958 ,btc_cdl.dr_code_combination_id dr_ccid
959 FROM pa_res_map_btc_v btc_cdl
960 ,pa_mappable_txns_tmp resmap
961 WHERE btc_cdl.request_id = g_request_id
962 AND resmap.system_reference3 = btc_cdl.request_id
963 AND resmap.system_reference2 = btc_cdl.line_num
964 AND resmap.system_reference1 = btc_cdl.expenditure_item_id
965 ORDER BY btc_cdl.resource_list_id
966 ,btc_cdl.project_id
967 ,btc_cdl.budget_version_id;
968
969 CURSOR reslist_cur IS
970 SELECT distinct btc_rl.resource_list_id
971 FROM pa_res_map_btc_v btc_rl
972 WHERE btc_rl.request_id = g_request_id;
973
974 CURSOR btc_resList_cur
975 IS
976 SELECT btc_cdl.expenditure_item_id expenditure_item_id
977 ,btc_cdl.project_id project_id
978 ,btc_cdl.line_num line_num
979 ,btc_cdl.cdl_rowid cdl_rowid
980 ,btc_cdl.task_id task_id
981 ,btc_cdl.top_task_id top_task_id
982 ,btc_cdl.person_id person_id
983 ,btc_cdl.organization_id organization_id
984 ,btc_cdl.job_id job_id
985 ,btc_cdl.expenditure_type expenditure_type
986 ,btc_cdl.expenditure_category expenditure_category
987 ,btc_cdl.system_linkage_function system_linkage_function
988 ,btc_cdl.gl_start_date gl_start_date
989 ,btc_cdl.encum_type_id encum_type_id
990 ,btc_cdl.vendor_id vendor_id
991 /* added for Cwk changes */
992 ,btc_cdl.budget_version_id budget_version_id
993 ,btc_cdl.resource_list_id resource_list_id
994 ,btc_cdl.entry_level_code entry_level_code
995 ,g_request_id request_id
996 FROM pa_res_map_btc_v btc_cdl
997 WHERE btc_cdl.request_id = g_request_id
998 ORDER BY NVL(btc_cdl.resource_list_id,0)
999 ,btc_cdl.project_id
1000 ,btc_cdl.budget_version_id
1001
1002 ;
1003
1004 BEGIN
1005
1006 pa_debug.init_err_stack('pa_bc_costing.map_btc_items');
1007
1008 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
1009 l_debug_mode := NVL(l_debug_mode, 'N');
1010
1011 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
1012
1013 l_stage := 100;
1014 l_debug_stage := TO_CHAR(l_stage) || ':From map_btc_items';
1015 print_msg(l_debug_mode,l_debug_stage);
1016
1017 g_request_id := p_request_id;
1018 l_debug_stage := TO_CHAR(l_stage) || ':Request_id is [' || TO_CHAR(g_request_id) || ']';
1019 print_msg(l_debug_mode,l_debug_stage);
1020
1021 /* Resource Mapping Changes Starts Here */
1022 l_stage := 150;
1023 l_debug_stage := l_stage||':'||'Initialize plsql tables';
1024 print_msg(l_debug_mode,l_debug_stage);
1025 --Initialize the plsql tables
1026 l_resmap_exp_item_id.delete;
1027 l_resmap_project_id.delete;
1028 l_resmap_line_num.delete;
1029 l_resmap_cdl_rowid.delete;
1030 l_resmap_task_id.delete;
1031 l_resmap_top_task_id.delete;
1035 l_resmap_exp_type.delete;
1032 l_resmap_person_id.delete;
1033 l_resmap_organization_id.delete;
1034 l_resmap_job_id.delete;
1036 l_resmap_exp_category.delete;
1037 l_resmap_sys_link_func.delete;
1038 l_resmap_gl_start_date.delete;
1039 l_resmap_encum_type_id.delete;
1040 l_resmap_vendor_id.delete;
1041 l_resmap_budget_version_id.delete;
1042 l_resmap_resource_list_id.delete;
1043 l_resmap_entry_level_code.delete;
1044
1045 OPEN btc_ResList_cur;
1046 FETCH btc_ResList_cur BULK COLLECT INTO
1047 l_resmap_exp_item_id
1048 ,l_resmap_project_id
1049 ,l_resmap_line_num
1050 ,l_resmap_cdl_rowid
1051 ,l_resmap_task_id
1052 ,l_resmap_top_task_id
1053 ,l_resmap_person_id
1054 ,l_resmap_organization_id
1055 ,l_resmap_job_id
1056 ,l_resmap_exp_type
1057 ,l_resmap_exp_category
1058 ,l_resmap_sys_link_func
1059 ,l_resmap_gl_start_date
1060 ,l_resmap_encum_type_id
1061 ,l_resmap_vendor_id
1062 ,l_resmap_budget_version_id
1063 ,l_resmap_resource_list_id
1064 ,l_resmap_entry_level_code
1065 ,l_resmap_request_id
1066 ;
1067 CLOSE btc_ResList_cur;
1068
1069 l_stage := 160;
1070 l_resRecCount := l_resmap_exp_item_id.count;
1071 l_debug_stage := l_stage||':'||'Num of Rows Fetched into PlsqlBlocks['||l_resRecCount||']' ;
1072 print_msg(l_debug_mode,l_debug_stage);
1073
1074 If l_resRecCount > 0 Then
1075 FORALL i IN l_resmap_exp_item_id.FIRST .. l_resmap_exp_item_id.LAST
1076 Insert into PA_MAPPABLE_TXNS_TMP
1077 (txn_id,
1078 person_id,
1079 job_id,
1080 organization_id,
1081 vendor_id,
1082 expenditure_type,
1083 event_type,
1084 non_labor_resource,
1085 expenditure_category,
1086 revenue_category,
1087 non_labor_resource_org_id,
1088 event_type_classification,
1089 system_linkage_function,
1090 project_role_id,
1091 resource_list_id,
1092 system_reference1,
1093 system_reference2,
1094 system_reference3,
1095 system_reference4,
1096 system_reference5
1097 )
1098 SELECT
1099 pa_mappable_txns_tmp_s.NEXTVAL
1100 ,l_resmap_person_id(i)
1101 ,l_resmap_job_id(i)
1102 ,l_resmap_organization_id(i)
1103 ,l_resmap_vendor_id(i)
1104 ,l_resmap_exp_type(i)
1105 ,NULL
1106 ,NULL
1107 ,l_resmap_exp_category(i)
1108 ,NULL
1109 ,NULL
1110 ,NULL
1111 ,l_resmap_sys_link_func(i)
1112 ,NULL
1113 ,l_resmap_resource_list_id(i)
1114 ,l_resmap_exp_item_id(i)
1115 ,l_resmap_line_num(i)
1116 ,l_resmap_request_id(i)
1117 ,l_resmap_budget_version_id(i)
1118 ,NULL
1119 FROM DUAL ;
1120 l_stage := 170;
1121 l_debug_stage := l_stage||':'||'Num of Rows Inserted into ResTmpTable['||sql%Rowcount||']';
1122 print_msg(l_debug_mode,l_debug_stage);
1123
1124 --get the distinct resource list Ids to call the resource mapping api
1125 l_debug_stage := l_stage||':'||'Fetching distinct resource List ids to call Resource map Api';
1126 print_msg(l_debug_mode,l_debug_stage);
1127 OPEN reslist_cur ;
1128 FETCH reslist_cur BULK COLLECT INTO
1129 l_bdgt_resource_list_id;
1130 CLOSE reslist_cur;
1131
1132 -- Call resource mapping API only once for each resource list id
1133 l_reslistCount := l_bdgt_resource_list_id.count;
1134 l_debug_stage := l_stage||':'||'Numof Distinct Resource ListIdCount['||l_reslistCount||']';
1135 print_msg(l_debug_mode,l_debug_stage);
1136 l_prev_reslist_id := NULL;
1137 IF l_reslistCount > 0 Then
1138 FOR i IN l_bdgt_resource_list_id.FIRST .. l_bdgt_resource_list_id.LAST LOOP
1139 If l_bdgt_resource_list_id(i) is NOT NULL Then
1140 -- Call the resource map api.
1141 l_stage := 180;
1142 l_debug_stage := l_stage||':'||'Calling Resource new_map_txns API For['
1143 ||l_resmap_resource_list_id(i)||']';
1144 print_msg(l_debug_mode,l_debug_stage);
1145 l_error_code := null;
1146 l_error_stage := null;
1147 PA_RES_ACCUMS.new_map_txns
1148 (x_resource_list_id => l_resmap_resource_list_id(i)
1149 ,x_error_stage => l_error_stage
1150 ,x_error_code => l_error_code ) ;
1151 l_prev_reslist_id := NVL(l_resmap_resource_list_id(i),0);
1152 l_debug_stage := l_stage||':'||'End of new_map_txns ErrStage['
1153 ||l_error_stage||']ErrCode['||l_error_code||']' ;
1154 print_msg(l_debug_mode,l_debug_stage);
1155 End if;
1156 END LOOP;
1157 END IF;
1158
1159 End If; -- end of resRecCount > zero
1160
1161 l_stage := 200;
1162 --Reset the counter
1163 l_counter := 0;
1164 l_expenditure_item_id_tab.delete;
1168 l_cdl_rowid_tab.delete;
1165 l_budget_ccid_tab.delete;
1166 l_cost_dist_rejection_code_tab.delete;
1167 l_line_num_tab.delete;
1169 l_encum_type_id_tab.delete;
1170 --r12
1171 l_budget_line_id_tab.delete;
1172 l_budget_ver_id_tab.delete;
1173
1174 FOR c1_rec IN btc_cdl_cur LOOP
1175
1176 l_debug_stage := 'Processing Eiid [' || c1_rec.expenditure_item_id ||
1177 '] line_num [' || c1_rec.line_num ||']BdgtVer['||c1_rec.budget_version_id||
1178 '] project_id [' || c1_rec.project_id ||']PoHead['||c1_rec.po_header_id||
1179 '] cdl_rowid [' || c1_rec.cdl_rowid ||']EntryLevlCode['||c1_rec.entry_level_code||
1180 '] task_id [' || c1_rec.task_id ||'] gl_start_date [' || c1_rec.gl_start_date ||
1181 '] top_task_id [' || c1_rec.top_task_id ||
1182 '] person_id [' || c1_rec.person_id ||
1183 '] organization_id [' || c1_rec.organization_id ||
1184 '] job_id [' || c1_rec.job_id ||']ResList['||c1_rec.resource_list_id||
1185 '] expenditure_type [' || c1_rec.expenditure_type ||
1186 '] expenditure_category [' || c1_rec.expenditure_category ||
1187 '] system_linkage_function [' || c1_rec.system_linkage_function ||
1188 '] encum_type_id [' || c1_rec.encum_type_id ||
1189 ']Rlmi ['||c1_rec.resource_list_member_id ||']Poline['||c1_rec.po_line_id||
1190 ']pktLineType['||c1_rec.pkt_line_type||
1191 ']dr_ccid['||c1_rec.dr_ccid||']' ;
1192
1193 print_msg(l_debug_mode,l_debug_stage);
1194 l_resource_list_member_id := c1_rec.resource_list_member_id;
1195 /* derive the resource List memberId for Cwk records */
1196 If c1_rec.po_line_id is NOT NULL Then
1197 l_stage := 210;
1198 l_debug_stage := l_stage||':'||'Contingent Worker EI';
1199 If c1_rec.budget_version_id is NOT NULL
1200 and c1_rec.po_header_id is NOT NULL Then
1201 l_debug_stage := l_stage||':'||'Calling Get_CWK_RLMI API';
1202 print_msg(l_debug_mode,l_debug_stage);
1203 l_resource_list_member_id := pa_funds_control_utils2.get_Cwk_rlmi
1204 (p_project_id => c1_rec.project_id
1205 ,p_task_id => c1_rec.task_id
1206 ,p_budget_version_id => c1_rec.budget_version_id
1207 ,p_document_header_id => c1_rec.po_header_id
1208 ,p_document_dist_id => null
1209 ,p_document_line_id => c1_rec.po_line_id
1210 ,p_document_type => 'EXP'
1211 ,p_expenditure_type => c1_rec.expenditure_type
1212 ,p_line_type => c1_rec.pkt_line_type
1213 ,p_calling_module => 'FUNDS_CHECK' );
1214 If l_resource_list_member_id is Null Then
1215 l_resource_list_member_id := c1_rec.resource_list_member_id;
1216 End If;
1217 l_debug_stage := l_stage||':'||'CWK RLMI['||l_resource_list_member_id||']' ;
1218 print_msg(l_debug_mode,l_debug_stage);
1219 End If;
1220 End If;
1221 /*
1222 * Caching.
1223 */
1224
1225 l_counter := l_counter + 1;
1226 /*
1227 * The following tables will be used for BULK update later.
1228 */
1229 l_expenditure_item_id_tab(l_counter) := c1_rec.expenditure_item_id;
1230 l_line_num_tab(l_counter) := c1_rec.line_num;
1231 l_cdl_rowid_tab(l_counter) := c1_rec.cdl_rowid;
1232 l_encum_type_id_tab(l_counter) := c1_rec.encum_type_id;
1233 l_cost_dist_rejection_code_tab(l_counter) := NULL;
1234 l_budget_ccid_tab(l_counter) := NULL;
1235
1236 IF c1_rec.resource_list_id is NOT NULL Then
1237 IF l_resource_list_member_id is NOT NULL Then
1238 l_debug_stage := TO_CHAR(l_stage) || ':Calling get_budget_ccid.' ;
1239 print_msg(l_debug_mode,l_debug_stage);
1240 /*
1241 * Get budget_ccid
1242 */
1243
1244 l_stage := 500;
1245 pa_funds_control_utils.get_budget_ccid
1246 ( p_project_id => c1_rec.project_id
1247 ,p_task_id => c1_rec.task_id
1248 ,p_top_task_id => c1_rec.top_task_id
1249 ,p_res_list_mem_id => l_resource_list_member_id
1250 ,p_start_date => c1_rec.gl_start_date
1251 ,p_budget_version_id => c1_rec.budget_version_id
1252 ,p_entry_level_code => c1_rec.entry_level_code
1253 ,x_budget_ccid => l_budget_ccid
1254 --r12
1255 ,x_budget_line_id => l_budget_line_id
1256 ,x_return_status => l_return_status
1257 ,x_error_message_code => l_error_code
1258 );
1259
1260 l_debug_stage := TO_CHAR(l_stage) || ':l_budget_ccid is [' || l_budget_ccid || ']';
1261 print_msg(l_debug_mode,l_debug_stage);
1262 IF (l_budget_ccid IS NOT NULL)
1263 --FP M changes
1264 --R12 - commented the (l_budget_ccid = c1_rec.dr_ccid)
1268 l_budget_ccid_tab(l_counter) := l_budget_ccid;
1265 --AND
1266 --(l_budget_ccid = c1_rec.dr_ccid)
1267 THEN
1269 --r12
1270 l_budget_line_id_tab(l_counter) := l_budget_line_id;
1271 l_budget_ver_id_tab(l_counter) := c1_rec.budget_version_id;
1272 ELSE -- l_budget_ccid IS NULL
1273 If (l_budget_ccid is NULL) Then
1274 l_cost_dist_rejection_code_tab(l_counter) := 'F132';
1275 l_budget_ccid_tab(l_counter) := NULL;
1276 l_budget_line_id_tab(l_counter) := NULL;
1277 l_budget_ver_id_tab(l_counter) := NULL;
1278 --FP M changes
1279 --R12 - commented the (l_budget_ccid <> c1_rec.dr_ccid)
1280 --ElsIf (l_budget_ccid <> c1_rec.dr_ccid) Then
1281 --l_cost_dist_rejection_code_tab(l_counter) := 'F107';
1282 --l_budget_ccid_tab(l_counter) := NULL;
1283 End If;
1284 END IF; -- l_budget_ccid IS NOT NULL.
1285 ELSE --l_resource_list_member_id IS NULL
1286 l_cost_dist_rejection_code_tab(l_counter) := 'F128';
1287 l_budget_ccid_tab(l_counter) := NULL;
1288 --r12
1289 l_budget_line_id_tab(l_counter) := NULL;
1290 l_budget_ver_id_tab(l_counter) := NULL;
1291 END IF; -- l_resource_list_member_id NULL check.
1292 ELSE -- resource_list_id is NULL.
1293 l_cost_dist_rejection_code_tab(l_counter) := 'F121';
1294 l_budget_ccid_tab(l_counter) := NULL;
1295 --r12
1296 l_budget_line_id_tab(l_counter) := NULL;
1297 l_budget_ver_id_tab(l_counter) := NULL;
1298 END IF; -- resource_list_id NULL check.
1299 END LOOP; --end of btc_cdl_cur
1300
1301 l_records_processed := l_counter;
1302
1303 l_debug_stage := l_stage||':'||'NumOfRecords Processed['||l_records_processed||']' ;
1304 print_msg(l_debug_mode,l_debug_stage);
1305 l_debug_stage := TO_CHAR(l_stage)||':No.of CDLs processed for mapping['||TO_CHAR(l_records_processed)||']' ;
1306 print_msg(l_debug_mode,l_debug_stage);
1307
1308 /*
1309 * Update the FC related columns in the CDL.
1310 * If Mapping was successful.
1311 *
1312 * Should modify this update rowid based for performance.
1313 */
1314 l_debug_stage := TO_CHAR(l_stage) || ':ORACLE error occurred while Bulk updating BC columns in CDL.' ;
1315
1316 l_stage := 600;
1317 FORALL l_counter IN 1 .. l_records_processed
1318 UPDATE pa_cost_distribution_lines_all cdl
1319 SET cdl.budget_ccid = l_budget_ccid_tab(l_counter)
1320 --r12
1321 ,cdl.budget_version_id = l_budget_ver_id_tab(l_counter)
1322 ,cdl.budget_line_id = l_budget_line_id_tab(l_counter)
1323 ,cdl.encumbrance_amount = cdl.acct_burdened_cost
1324 ,cdl.liquidate_encum_flag = 'Y'
1325 ,cdl.ENCUMBRANCE_TYPE_ID = l_encum_type_id_tab(l_counter)
1326 WHERE cdl.rowid = l_cdl_rowid_tab(l_counter)
1327 AND l_budget_ccid_tab(l_counter) IS NOT NULL
1328 ;
1329
1330 l_debug_stage := TO_CHAR(l_stage) || ':No.of CDLs updated with FC columns['||TO_CHAR(SQL%ROWCOUNT)||']';
1331 print_msg(l_debug_mode,l_debug_stage);
1332
1333 /*
1334 * Update ei.cost_dist_rejection_code if Mapping
1335 * Failed.
1336 */
1337 l_stage := 700;
1338 FORALL l_counter IN 1 .. l_records_processed
1339 UPDATE pa_expenditure_items ei
1340 SET ei.cost_dist_rejection_code = l_cost_dist_rejection_code_tab(l_counter)
1341 WHERE ei.expenditure_item_id = l_expenditure_item_id_tab(l_counter)
1342 AND l_budget_ccid_tab(l_counter) IS NULL
1343 ;
1344
1345 l_debug_stage := TO_CHAR(l_stage)||':No.of CDLs updated with rej_code['||TO_CHAR(SQL%ROWCOUNT)||']';
1346 print_msg(l_debug_mode,l_debug_stage);
1347
1348 l_debug_stage := TO_CHAR(l_stage) || ':Deleting CDLs which failed mapping.' ;
1349 print_msg(l_debug_mode,l_debug_stage);
1350
1351 /*
1352 * Delete the CDLs which failed resource-mapping.
1353 */
1354 l_stage := 800;
1355 FORALL l_counter IN 1 .. l_records_processed
1356 DELETE FROM pa_cost_distribution_lines cdl
1357 WHERE cdl.rowid = l_cdl_rowid_tab(l_counter)
1358 AND l_budget_ccid_tab(l_counter) IS NULL
1359 ;
1360
1361 l_debug_stage := TO_CHAR(l_stage)||':No.of CDLs deleted for Mapping failure['||TO_CHAR(SQL%ROWCOUNT)||']';
1362 print_msg(l_debug_mode,l_debug_stage);
1363
1364 --reset the error stack
1365 pa_debug.reset_err_stack;
1366
1367 EXCEPTION
1368 WHEN OTHERS
1369 THEN
1370 IF P_DEBUG_MODE = 'Y' THEN
1371 pa_debug.write_file('ErrStage['||l_stage||']map_btc_items:'||l_debug_stage);
1372 END IF;
1373 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1374 x_error_code := TO_CHAR(SQLCODE) || SQLERRM ;
1375 x_error_stage := l_stage ;
1376 RAISE;
1377 END map_btc_items;
1378
1382 */
1379 ----------------------------------------------------------------------------
1380 /*
1381 * This procedure deletes the CDLs that failed Funds-Check.
1383
1384 PROCEDURE process_rejected_exp_items ( x_return_status OUT NOCOPY NUMBER
1385 ,x_error_code OUT NOCOPY VARCHAR2
1386 ,x_error_stage OUT NOCOPY VARCHAR2
1387 )
1388 IS
1389 l_rejected_eiid_tab PA_PLSQL_DATATYPES.IdTabTyp;
1390 l_debug_mode VARCHAR2(1);
1391 l_records_affected NUMBER := 0;
1392 l_records_deleted NUMBER := 0; /* Added for bug#3094341 */
1393 l_stage NUMBER ;
1394 l_del_cdl_eiid_tab PA_PLSQL_DATATYPES.IdTabTyp;
1395 l_del_cdl_line_num_tab PA_PLSQL_DATATYPES.NumTabTyp;
1396 l_del_cdl_line_type_tab PA_PLSQL_DATATYPES.Char1TabTyp;
1397 l_del_cdl_parent_tab PA_PLSQL_DATATYPES.NumTabTyp;
1398 l_del_cdl_dbc_tab PA_PLSQL_DATATYPES.NumTabTyp;
1399 l_del_cdl_abc_tab PA_PLSQL_DATATYPES.NumTabTyp;
1400 l_del_cdl_pfbc_tab PA_PLSQL_DATATYPES.NumTabTyp;
1401 l_del_cdl_pbc_tab PA_PLSQL_DATATYPES.NumTabTyp;
1402 BEGIN
1403 pa_debug.init_err_stack('pa_bc_costing.process_rejected_exp_items');
1404
1405 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
1406 l_debug_mode := NVL(l_debug_mode, 'Y');
1407
1408 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
1409
1410 l_stage := 100;
1411 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':From process_rejected_exp_items';
1412 pa_debug.write_file(pa_debug.g_err_stage);
1413
1414 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':ORACLE error occurred while selecting rejected EIs.';
1415
1416 /*
1417 * Bulk collect the expenditure Items that were
1418 * rejected during Funds Check.
1419 */
1420 /*
1421 | Modified to handle contingent labor straight time transactions.
1422 | Bug 4103495: Modified to handle BTC transactions with adjustment_type = 'BURDEN_RESUMMARIZE'.
1423 +===============================================*/
1424 l_stage := 200;
1425 SELECT ei.expenditure_item_id
1426 BULK COLLECT
1427 INTO l_rejected_eiid_tab
1428 FROM pa_expenditure_items ei
1429 WHERE ei.cost_dist_rejection_code IS NOT NULL
1430 AND ei.cost_distributed_flag = 'S'
1431 AND ei.request_id = g_request_id
1432 AND (ei.system_linkage_function IN ('VI')
1433 OR (ei.system_linkage_function in ('ST','OT') AND ei.po_line_id IS NOT NULL)
1434 OR (ei.system_linkage_function = 'BTC' AND ei.adjustment_type = 'BURDEN_RESUMMARIZE')
1435 );
1436
1437 l_records_affected := SQL%ROWCOUNT;
1438
1439 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':No. of EIs failed Funds-Check : [' ||
1440 TO_CHAR(l_records_affected) || ']';
1441 pa_debug.write_file(pa_debug.g_err_stage);
1442
1443 /*
1444 * Process rejected EIs if there are any.
1445 * May be we can put the rejected EI process in a seperate
1446 * procedure
1447 */
1448 IF ( l_records_affected > 0 )
1449 THEN
1450
1451 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':Deleting CDLs rejected during FC.' ;
1452 pa_debug.write_file(pa_debug.g_err_stage);
1453
1454 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':Some rejected CDLs with req_id [' ||
1455 TO_CHAR(g_request_id) || '] are being deleted';
1456 pa_debug.write_file(pa_debug.g_err_stage);
1457
1458 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':ORACLE error occurred while Deleting CDLs.' ;
1459
1460 /*
1461 * Delete CDLs that were rejected during FC.
1462 * These records will be marked with a NOT NULL value for ei.cost_dist_rejection_code.
1463 *
1464 * I think its enough to check for eiid and request_id to identify CDLs
1465 * that were created during this run. But if its needed to check against line_num
1466 * also, then line_num also has to be selected above from pa_bc_packets.
1467 */
1468 l_stage := 300;
1469 FORALL i IN l_rejected_eiid_tab.FIRST .. l_rejected_eiid_tab.LAST
1470 DELETE
1471 FROM pa_cost_distribution_lines cdl
1472 WHERE cdl.request_id = g_request_id
1473 AND cdl.expenditure_item_id = l_rejected_eiid_tab(i)
1474 AND NVL(cdl.reversed_flag, 'N') <> 'Y'
1475 AND cdl.transfer_status_code <> 'V'
1476 RETURNING cdl.expenditure_item_id, cdl.line_num, cdl.line_type, cdl.parent_line_num
1477 ,nvl(cdl.denom_burdened_cost,0), nvl(cdl.acct_burdened_cost,0), nvl(cdl.burdened_cost,0), nvl(cdl.project_burdened_cost,0)
1478 BULK COLLECT INTO l_del_cdl_eiid_tab, l_del_cdl_line_num_tab, l_del_cdl_line_type_tab, l_del_cdl_parent_tab
1479 ,l_del_cdl_dbc_tab, l_del_cdl_abc_tab, l_del_cdl_pfbc_tab, l_del_cdl_pbc_tab
1480 ;
1481 /* Added for Bug fix to get the no of rec's deleted. Bug 3094341 */
1482 l_records_deleted := SQL%ROWCOUNT;
1483
1484 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':No. of CDLs deleted [' || TO_CHAR(SQL%ROWCOUNT) || ']';
1485 pa_debug.write_file(pa_debug.g_err_stage);
1486
1487 /* Check added to check if no of rec's deleted > 0 then go to the updates.
1488 Added for Bug 3094341
1489 */
1490
1491 IF l_records_deleted > 0 THEN -----------------------------------------{
1492 IF (l_debug_mode = 'Y')
1493 THEN
1494 /*
1498 LOOP
1495 * Modified the Looping to go by l_del_cdl_eiid_tab instead of l_expenditure_item_id_tab.
1496 */
1497 FOR i IN l_del_cdl_eiid_tab.FIRST .. l_del_cdl_eiid_tab.LAST
1499 pa_debug.g_err_stage := 'deleted eiid [' || l_del_cdl_eiid_tab(i) ||
1500 '] line_num [' || l_del_cdl_line_num_tab(i) ||
1501 '] line_type [' || l_del_cdl_line_type_tab(i) ||
1502 '] parent line [' || l_del_cdl_parent_tab(i) ||
1503 '] dbc [' || l_del_cdl_dbc_tab(i) ||
1504 '] abc [' || l_del_cdl_abc_tab(i) ||
1505 '] pfbc [' || l_del_cdl_pfbc_tab(i) ||
1506 '] pbc [' || l_del_cdl_pbc_tab(i) ||
1507 ']';
1508 pa_debug.write_file('process_rejected_exp_items: ' || pa_debug.g_err_stage);
1509 END LOOP;
1510 END IF; -- debug mode?
1511
1512 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':Updating reversed_flag in CDLs.' ;
1513 pa_debug.write_file(pa_debug.g_err_stage);
1514
1515 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':ORACLE error occurred while Updating CDLs.' ;
1516
1517 /*
1518 * Update the reversed_flag of the original CDLs, the reversing and new
1519 * CDLs of whom where deleted above because of failed FC.
1520 * Because, since the reversing and new are deleted, the original's
1521 * reversing flag should be brought back to NULL.
1522 * The request_id of the original is updated with the current request_id
1523 * when setting reversed_flag to 'Y'. So, we can make use of that.
1524 */
1525 l_stage := 400;
1526 FORALL i IN l_rejected_eiid_tab.FIRST .. l_rejected_eiid_tab.LAST
1527 UPDATE pa_cost_distribution_lines cdl
1528 SET cdl.reversed_flag = NULL
1529 WHERE NVL(cdl.reversed_flag, 'N') = 'Y'
1530 AND cdl.request_id = g_request_id
1531 AND cdl.expenditure_item_id = l_rejected_eiid_tab(i)
1532 ;
1533
1534 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':Reversed Flag updated for [' || TO_CHAR(SQL%ROWCOUNT) || '] CDLs';
1535 pa_debug.write_file(pa_debug.g_err_stage);
1536
1537 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':Updating burden change buckets on the raw CDLs.' ;
1538 pa_debug.write_file(pa_debug.g_err_stage);
1539
1540 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':ORACLE error occurred while Updating Raw CDLs.' ;
1541
1542 /*=======================================================================+
1543 | For failed transactions, if the CDL being deleted is of line type I, |
1544 | then the corresponding burden change amount has to be deducted from |
1545 | the parent raw line. |
1546 +=======================================================================*/
1547 l_stage := 500;
1548 FORALL i IN l_del_cdl_eiid_tab.FIRST .. l_del_cdl_eiid_tab.LAST
1549 UPDATE pa_cost_distribution_lines cdl
1550 SET cdl.denom_burdened_change = cdl.denom_burdened_change - l_del_cdl_dbc_tab(i)
1551 ,cdl.acct_burdened_change = cdl.acct_burdened_change - l_del_cdl_abc_tab(i)
1552 ,cdl.projfunc_burdened_change = cdl.projfunc_burdened_change - l_del_cdl_pfbc_tab(i)
1553 ,cdl.project_burdened_change = cdl.project_burdened_change - l_del_cdl_pbc_tab(i)
1554 WHERE cdl.expenditure_item_id = l_del_cdl_eiid_tab(i)
1555 and cdl.line_num = l_del_cdl_parent_tab(i)
1556 and l_del_cdl_line_type_tab(i) = 'I'
1557 ;
1558 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':Burden Change Bucket updated for [' || TO_CHAR(SQL%ROWCOUNT) || '] CDLs';
1559 pa_debug.write_file(pa_debug.g_err_stage);
1560
1561 END IF; -------------------------------------------------------------} /* l_records_deleted? */
1562
1563
1564 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':Leaving process_rejected_exp_items.';
1565 pa_debug.write_file(pa_debug.g_err_stage);
1566
1567 END IF; -- Were any EI got rejected?
1568
1569 pa_debug.reset_err_stack;
1570
1571 EXCEPTION
1572 WHEN OTHERS
1573 THEN
1574 pa_debug.write_file('EXCEPTION:' || pa_debug.g_err_stage);
1575 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1576 x_error_code := TO_CHAR(SQLCODE) || SQLERRM ;
1577 x_error_stage := l_stage ;
1578 RAISE;
1579 END; -- process_rejected_exp_items
1580
1581 ------------------------------------------------------------------------
1582 PROCEDURE populate_pa_bc_packets( x_return_status OUT NOCOPY NUMBER
1583 ,x_error_code OUT NOCOPY VARCHAR2
1584 ,x_error_stage OUT NOCOPY VARCHAR2
1585 )
1586 IS
1587 PRAGMA AUTONOMOUS_TRANSACTION;
1588
1589 l_debug_mode VARCHAR2(1);
1590 l_records_affected NUMBER := 0;
1591 l_stage NUMBER ;
1592
1593 BEGIN
1594
1595 pa_debug.init_err_stack('pa_bc_costing.populate_pa_bc_packets');
1596
1597 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
1598 l_debug_mode := NVL(l_debug_mode, 'Y');
1599
1600 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
1601
1602 l_stage := 100;
1603 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':From populate_pa_bc_packets';
1604 pa_debug.write_file(pa_debug.g_err_stage);
1605
1606 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':Before Inserting Raw lines into pa_bc_packets.' ;
1607 pa_debug.write_file(pa_debug.g_err_stage);
1608
1612 * Insert Raw lines.
1609 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':ORACLE error occurred while Bulk inserting into pa_bc_packets.' ;
1610
1611 /*
1613 *
1614 * For New lines, the FC process will insert the debit burden lines.
1615 * To distinguish a new line from a reversal line, the parent_bc_packet_id
1616 * is inserted as -1 for a new line.
1617 * For a reversal line, the value will be NULL.
1618 *
1619 * The transfer_status_code Join is used to make use of PA_COST_DISTRIBUTION_LINES_N2
1620 * index.
1621 *
1622 * burden_cost_flag is populated 'N' for Raw line.
1623 */
1624 l_stage := 200;
1625 FORALL i IN l_expenditure_item_id_tab.FIRST .. l_expenditure_item_id_tab.LAST
1626 INSERT
1627 INTO pa_bc_packets( packet_id
1628 ,project_id
1629 ,task_id
1630 ,budget_version_id
1631 ,expenditure_type
1632 ,expenditure_item_date
1633 ,period_name
1634 ,pa_date
1635 ,gl_date
1636 ,set_of_books_id
1637 ,je_category_name
1638 ,je_source_name
1639 ,status_code
1640 ,document_type
1641 ,funds_process_mode
1642 ,burden_cost_flag
1643 ,expenditure_organization_id
1644 ,document_header_id
1645 ,document_distribution_id
1646 ,document_line_id
1647 ,txn_ccid
1648 ,accounted_dr
1649 ,entered_dr
1650 ,bc_packet_id
1651 ,parent_bc_packet_id
1652 ,org_id
1653 ,balance_posted_flag
1654 ,program_id
1655 ,program_application_id
1656 ,program_update_date
1657 ,last_update_date
1658 ,last_updated_by
1659 ,created_by
1660 ,creation_date
1661 ,last_update_login
1662 ,request_id
1663 ,reference1
1664 ,reference2
1665 ,reference3
1666 )
1667 SELECT g_packet_id -- packet_id
1668 ,l_project_id_tab(i) -- project_id
1669 ,l_task_id_tab(i) -- task_id
1670 ,l_budget_version_id_tab(i) -- budget_version_id
1671 ,l_expenditure_type_tab(i) -- expenditure_type
1672 ,l_expenditure_item_date_tab(i) -- expenditure_item_date
1673 ,l_gl_period_name_tab(i) -- period_name
1674 ,l_pa_date_tab(i) -- pa_date
1675 ,l_gl_date_tab(i) -- gl_date
1676 ,g_sob_id -- set_of_book_id
1677 ,'Project Accounting' -- je_category_name
1678 ,'Expenditures' -- je_source_name
1679 ,'P' -- status_code
1680 ,'EXP' -- document_type
1681 ,'T' -- funds_process_mode
1682 ,'N' -- burden_cost_flag
1683 ,l_exp_organization_id_tab(i) -- expenditure_organization_id
1684 ,l_expenditure_item_id_tab(i) -- document_header_id
1685 ,l_line_num_tab(i) -- document_distribution_id
1686 ,l_document_line_id_tab(i) -- document_line_id
1687 ,l_dr_code_combination_id_tab(i) -- txn_ccid
1688 ,l_acct_raw_cost_tab(i) -- accounted_dr
1689 ,l_acct_raw_cost_tab(i) -- entered_dr
1690 ,pa_bc_packets_s.NEXTVAL -- bc_packet_id
1691 ,l_parent_bc_packet_id_tab(i) -- parent_bc_packet_id
1692 ,l_org_id_tab(i) -- org_id
1693 ,'N' -- balance_posted_flag
1694 ,g_program_id -- program_id
1695 ,g_program_application_id -- program_application_id
1696 ,SYSDATE -- program_update_date
1697 ,SYSDATE -- last_update_date
1701 ,g_last_update_login -- last_update_login
1698 ,g_last_updated_by -- last_updated_by
1699 ,g_created_by -- created_by
1700 ,SYSDATE -- creation_date
1702 ,g_request_id
1703 ,l_pkt_reference1_Tab(i)
1704 ,l_pkt_reference2_Tab(i)
1705 ,l_pkt_reference3_Tab(i)
1706 FROM DUAL
1707 ;
1708
1709 l_records_affected := SQL%ROWCOUNT;
1710
1711 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':After Inserting Raw lines into pa_bc_packets.' ;
1712 pa_debug.write_file(pa_debug.g_err_stage);
1713
1714 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':Inserted [' || TO_CHAR(l_records_affected) ||
1715 '] Raw lines into pa_bc_packets.';
1716 pa_debug.write_file(pa_debug.g_err_stage);
1717
1718 /*
1719 * Insert Burden lines - if the project is burdened.
1720 * For burden_amt_display_method = 'S', Burdened amount is stored in the raw
1721 * cdl itself.
1722 * Entered_dr = Burdened_amount - raw_cost
1723 *
1724 * Since, both the reversed and the reversing lines have the same request_id
1725 * (though the reversed line could have been created in a previous run),
1726 * to identify CDLs that were created in this run, we select those records
1727 * with cdl.reversed_flag <> 'Y'.
1728 *
1729 * Burden lines are inserted in this level - only if this is a reversing line.
1730 * For NEW lines, the FC process creates the Burden lines.
1731 * For this, we go by the cdl.line_num_reversed.
1732 *
1733 * Identifying the raw line in bc_packets corresponding to the burden line
1734 * that we are inserting.
1735 *
1736 * cdl.eiid = bcpk.eiid
1737 * bcpk.parent_bc_packet_id is null
1738 * because, if parent_bc_packet_id is NOT NULL, it means its a fresh raw line.
1739 * for fresh line, we wouldnt' be inserting burden lines in the first place.
1740 * if its just another burden line, the parent_bc_packet_id will have the
1741 * bc_packet_id of the raw line.
1742 *
1743 * burden_cost_flag is populated 'O' for Burden lines.
1744 */
1745
1746 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':Inserting Burden lines into pa_bc_packets (display_method = S).' ;
1747 pa_debug.write_file(pa_debug.g_err_stage);
1748
1749 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':ORACLE error occurred while inserting into pa_bc_packets' ;
1750
1751 l_stage := 300;
1752 FORALL i IN l_expenditure_item_id_tab.FIRST .. l_expenditure_item_id_tab.LAST
1753 INSERT
1754 INTO pa_bc_packets( packet_id
1755 ,project_id
1756 ,task_id
1757 ,budget_version_id
1758 ,expenditure_type
1759 ,expenditure_item_date
1760 ,period_name
1761 ,pa_date
1762 ,gl_date
1763 ,set_of_books_id
1764 ,je_category_name
1765 ,je_source_name
1766 ,status_code
1767 ,document_type
1768 ,funds_process_mode
1769 ,burden_cost_flag
1770 ,expenditure_organization_id
1771 ,document_header_id
1772 ,document_distribution_id
1773 ,document_line_id
1774 ,txn_ccid
1775 ,accounted_dr
1776 ,entered_dr
1777 ,bc_packet_id
1778 ,parent_bc_packet_id
1779 ,org_id
1780 ,balance_posted_flag
1781 ,program_id
1782 ,program_application_id
1783 ,program_update_date
1784 ,last_update_date
1785 ,last_updated_by
1786 ,created_by
1787 ,creation_date
1788 ,last_update_login
1789 ,request_id
1790 ,reference1
1791 ,reference2
1792 ,reference3
1793 )
1794 SELECT g_packet_id -- packet_id
1795 ,l_project_id_tab(i) -- project_id
1796 ,l_task_id_tab(i) -- task_id
1797 ,l_budget_version_id_tab(i) -- budget_version_id
1798 ,l_expenditure_type_tab(i) -- expenditure_type
1799 ,l_expenditure_item_date_tab(i) -- expenditure_item_date
1800 ,l_gl_period_name_tab(i) -- period_name
1801 ,l_pa_date_tab(i) -- pa_date
1802 ,l_gl_date_tab(i) -- gl_date
1803 ,g_sob_id -- set_of_books_id
1807 ,'EXP' -- document_type
1804 ,'Project Accounting' -- je_category_name
1805 ,'Expendiures' -- je_source_name
1806 ,'P' -- status_code
1808 ,'T' -- funds_process_mode
1809 ,'O' -- funds_process_mode
1810 ,l_exp_organization_id_tab(i) -- expenditure_organization_id
1811 ,l_expenditure_item_id_tab(i) -- document_header_id
1812 ,l_line_num_tab(i) -- document_distribution_id
1813 ,l_document_line_id_tab(i) -- document_line_id
1814 ,l_dr_code_combination_id_tab(i) -- txn_ccid
1815 ,(l_acct_burdened_cost_tab(i) - l_acct_raw_cost_tab(i)) -- accounted_dr
1816 ,(l_acct_burdened_cost_tab(i) - l_acct_raw_cost_tab(i)) -- entered_dr
1817 ,pa_bc_packets_s.NEXTVAL -- pa_bc_packet_id
1818 --,DECODE(l_line_num_reversed_tab(i), NULL, NULL, bcpk.bc_packet_id) -- parent_bc_packet_id
1819 ,DECODE(l_line_type_tab(i), 'I', NULL, DECODE(l_line_num_reversed_tab(i), NULL, NULL, bcpk.bc_packet_id)) -- parent_bc_packet_id
1820 ,l_org_id_tab(i) -- org_id
1821 ,'N' -- balance_posted_flag
1822 ,g_program_id -- program_id
1823 ,g_program_application_id -- program_application_id
1824 ,SYSDATE -- program_update_date
1825 ,SYSDATE -- last_update_date
1826 ,g_last_updated_by -- last_updated_by
1827 ,g_created_by -- created_by
1828 -- ,100 -- created_by
1829 ,SYSDATE -- creation_date
1830 ,g_last_update_login -- last_update_login
1831 ,g_request_id
1832 ,l_pkt_reference1_Tab(i)
1833 ,l_pkt_reference2_Tab(i)
1834 ,l_pkt_reference3_Tab(i)
1835 FROM pa_bc_packets bcpk -- to get the raw line in bc_packets
1836 WHERE l_line_num_reversed_tab(i) IS NOT NULL
1837 AND l_burden_amt_disp_method_tab(i) = 'S'
1838 AND l_burden_cost_flag_tab(i) = 'Y'
1839 AND bcpk.document_header_id = l_expenditure_item_id_tab(i)
1840 AND bcpk.parent_bc_packet_id IS NULL
1841 AND bcpk.packet_id = g_packet_id
1842 ;
1843
1844 l_records_affected := TO_CHAR(SQL%ROWCOUNT) ;
1845
1846 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':After Inserting Burden lines into pa_bc_packets (display_method = S).' ;
1847 pa_debug.write_file(pa_debug.g_err_stage);
1848
1849 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':Inserted [' || TO_CHAR(l_records_affected) ||
1850 '] Burden lines into pa_bc_packets';
1851 pa_debug.write_file(pa_debug.g_err_stage);
1852
1853 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':Inserting Burden lines into pa_bc_packets (disp_method = D).' ;
1854 pa_debug.write_file(pa_debug.g_err_stage);
1855
1856 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':ORACLE error occurred while inserting Burden lines into bc_pk dis_meth = D' ;
1857 /*
1858 * For burden_amt_display_method = D, Burdened amount has to be derived.
1859 * The bc_packet_id in pa_bc_packets for the *R* line for this EI is populated as
1860 * parent_bc_packet_id in the burden line in pa_bc_packets.
1861 * *R* line in pa_bc_packets will be identified by a NULL in the parent_bc_packet_id
1862 * column.
1863 */
1864 l_stage := 400;
1865 FORALL i IN l_expenditure_item_id_tab.FIRST .. l_expenditure_item_id_tab.LAST
1866 INSERT
1867 INTO pa_bc_packets( packet_id
1868 ,project_id
1869 ,task_id
1870 ,budget_version_id
1871 ,expenditure_type
1872 ,expenditure_item_date
1873 ,period_name
1874 ,pa_date
1875 ,gl_date
1876 ,set_of_books_id
1877 ,je_category_name
1878 ,je_source_name
1879 ,status_code
1880 ,document_type
1881 ,funds_process_mode
1882 ,burden_cost_flag
1883 ,expenditure_organization_id
1884 ,document_header_id
1885 ,document_distribution_id
1886 ,document_line_id
1887 ,txn_ccid
1888 ,accounted_dr
1889 ,entered_dr
1890 ,bc_packet_id
1891 ,parent_bc_packet_id
1892 ,org_id
1893 ,balance_posted_flag
1894 ,program_id
1898 ,last_updated_by
1895 ,program_application_id
1896 ,program_update_date
1897 ,last_update_date
1899 ,created_by
1900 ,creation_date
1901 ,last_update_login
1902 ,request_id
1903 ,reference1
1904 ,reference2
1905 ,reference3
1906 )
1907 SELECT g_packet_id -- packet_id
1908 ,l_project_id_tab(i) -- project_id
1909 ,l_task_id_tab(i) -- task_id
1910 ,l_budget_version_id_tab(i) -- budget_version_id
1911 ,icc.expenditure_type -- expenditure_type
1912 ,l_expenditure_item_date_tab(i) -- expenditure_item_date
1913 ,l_gl_period_name_tab(i) -- period_name
1914 ,l_pa_date_tab(i) -- pa_date
1915 ,l_gl_date_tab(i) -- gl_date
1916 ,g_sob_id -- set_of_book_id
1917 ,'Project Accounting' -- je_category_name
1918 ,'Expenditures' -- je_source_name
1919 ,'P' -- status_code
1920 ,'EXP' -- document_type
1921 ,'T' -- funds_process_mode
1922 ,'O' -- funds_process_mode
1923 ,l_exp_organization_id_tab(i) -- expenditure_organization_id
1924 ,l_expenditure_item_id_tab(i) -- document_header_id
1925 ,l_line_num_tab(i) -- document_distribution_id
1926 ,l_document_line_id_tab(i) -- document_line_id
1927 ,l_dr_code_combination_id_tab(i) -- txn_ccid
1928 ,ROUND(l_acct_raw_cost_tab(i) * cm.compiled_multiplier,2) -- accounted_dr
1929 ,ROUND(l_acct_raw_cost_tab(i) * cm.compiled_multiplier,2) -- entered_dr
1930 ,pa_bc_packets_s.NEXTVAL -- bc_packet_id
1931 --,DECODE(l_line_num_reversed_tab(i), NULL, NULL, bcpk.bc_packet_id) -- parent_bc_packet_id
1932 ,DECODE(l_line_type_tab(i), 'I', NULL, DECODE(l_line_num_reversed_tab(i), NULL, NULL, bcpk.bc_packet_id)) -- parent_bc_packet_id
1933 ,l_org_id_tab(i) -- org_id
1934 ,'N' -- balance_posted_flag
1935 ,g_program_id -- program_id
1936 ,g_program_application_id -- program_application_id
1937 ,SYSDATE -- program_update_date
1938 ,SYSDATE -- last_update_date
1939 ,g_last_updated_by -- last_updated_by
1940 ,g_created_by -- created_by
1941 ,SYSDATE -- creation_date
1942 ,g_last_update_login -- last_update_login
1943 ,g_request_id
1944 ,l_pkt_reference1_tab(i)
1945 ,l_pkt_reference2_tab(i)
1946 ,l_pkt_reference3_tab(i)
1947 FROM PA_IND_COST_CODES ICC,
1948 PA_COMPILED_MULTIPLIERS CM,
1949 PA_IND_COMPILED_SETS ICS,
1950 PA_COST_BASE_EXP_TYPES CBET,
1951 PA_COST_BASES CB,
1952 PA_IND_RATE_SCH_REVISIONS IRSR,
1953 PA_IND_RATE_SCHEDULES_ALL_BG IRS
1954 ,PA_BC_PACKETS bcpk
1955 WHERE ics.ind_rate_sch_revision_id = irsr.ind_rate_sch_revision_id
1956 AND irs.ind_rate_sch_id = irsr.ind_rate_sch_id
1957 AND irsr.cost_plus_structure = cbet.cost_plus_structure
1958 AND cbet.cost_base = cm.cost_base
1959 AND cb.cost_base = cbet.cost_base
1960 AND cb.cost_base_type = cbet.cost_base_type
1961 AND cbet.cost_base_type = 'INDIRECT COST'
1962 AND cbet.expenditure_type = l_expenditure_type_tab(i)
1963 AND ics.organization_id = l_exp_organization_id_tab(i)
1964 AND ics.cost_base = cbet.cost_base
1965 AND ics.ind_compiled_set_id = l_ind_compiled_set_id_tab(i)
1966 AND icc.ind_cost_code = cm.ind_cost_code
1967 AND cm.ind_compiled_set_id = l_ind_compiled_set_id_tab(i)
1968 AND l_burden_sum_rej_code_tab(i) IS NULL
1969 AND l_burden_sum_source_run_id_tab(i) = -9999
1970 AND l_burden_amt_disp_method_tab(i) = 'D'
1971 AND l_burden_cost_flag_tab(i) = 'Y'
1972 AND l_line_num_reversed_tab(i) IS NOT NULL -- reversing line
1973 AND bcpk.document_header_id = l_expenditure_item_id_tab(i)
1974 AND bcpk.parent_bc_packet_id IS NULL
1975 AND bcpk.packet_id = g_packet_id
1976 ;
1977
1978 l_records_affected := SQL%ROWCOUNT ;
1979
1983 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':After Inserting Burden lines into pa_bc_packets (disp_method = D).' ;
1980 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':Inserted [' || TO_CHAR(l_records_affected) || '] Burden lines into pa_bc_packets';
1981 pa_debug.write_file(pa_debug.g_err_stage);
1982
1984 pa_debug.write_file(pa_debug.g_err_stage);
1985
1986 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':Committing work!!' ;
1987 pa_debug.write_file(pa_debug.g_err_stage);
1988
1989 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':Leaving populate_pa_bc_packets.' ;
1990 pa_debug.write_file(pa_debug.g_err_stage);
1991
1992 l_stage := 500;
1993 COMMIT;
1994 pa_debug.reset_err_stack;
1995 EXCEPTION
1996 WHEN OTHERS
1997 THEN
1998 pa_debug.write_file(pa_debug.g_err_stage);
1999 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2000 x_error_code := TO_CHAR(SQLCODE) || SQLERRM ;
2001 x_error_stage := l_stage ;
2002 RAISE;
2003 END; -- populate_pa_bc_packets
2004
2005 --------------------------------------------------------------------------------------
2006 /*
2007 *
2008 *
2009 */
2010 PROCEDURE validate_debit_lines ( p_request_id IN NUMBER
2011 ,x_return_status OUT NOCOPY NUMBER
2012 ,x_error_code OUT NOCOPY VARCHAR2
2013 ,x_error_stage OUT NOCOPY NUMBER
2014 )
2015 IS
2016 /*
2017 * Table to store the rejection code - if mapping fails.
2018 * Value will be NULL if the CDL was successfully mapped.
2019 */
2020 l_expenditure_item_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
2021 l_budget_ccid_tab PA_PLSQL_DATATYPES.IdTabTyp;
2022 l_cost_dist_rejection_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
2023 l_line_num_tab PA_PLSQL_DATATYPES.NumTabTyp;
2024 l_cdl_rowid_tab PA_PLSQL_DATATYPES.RowidTabTyp;
2025 l_encum_type_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
2026 l_deleted_eiids_tab PA_PLSQL_DATATYPES.IdTabTyp;
2027
2028 l_counter NUMBER := 0;
2029 l_debug_mode VARCHAR2(1) := 'N';
2030 l_records_processed NUMBER := 0;
2031 l_cdls_deleted NUMBER := 0;
2032
2033 l_prev_project_id pa_cost_distribution_lines.project_id%TYPE;
2034 l_prev_flag VARCHAR2(1);
2035 l_budget_version_id pa_bc_packets.budget_version_id%TYPE;
2036 l_resource_list_id pa_budget_versions.resource_list_id%TYPE;
2037 l_entry_level_code pa_budget_versions.budget_entry_method_code%TYPE; --???
2038 l_resource_list_member_id pa_bc_packets.resource_list_member_id%TYPE;
2039 l_budget_ccid pa_cost_distribution_lines.budget_ccid%TYPE;
2040
2041 l_fnd_reqd_flag VARCHAR2(1);
2042
2043 g_request_id NUMBER;
2044 l_return_status VARCHAR2(1);
2045 l_stage NUMBER;
2046 l_error_code VARCHAR2(30);
2047
2048 /*
2049 * The line_num_reversed and reversed_flag combination will get us
2050 * the latest 'R' CDL.
2051 */
2052 CURSOR c1
2053 IS
2054 SELECT cdl.expenditure_item_id expenditure_item_id
2055 ,cdl.budget_ccid budget_ccid
2056 ,cdl.line_num line_num
2057 FROM pa_cost_distribution_lines cdl
2058 ,pa_expenditure_items ei
2059 WHERE (ei.system_linkage_function IN ('VI')
2060 --FP M changes
2061 OR (ei.system_linkage_function in ('ST','OT') AND ei.po_line_id IS NOT NULL))
2062 AND ei.expenditure_item_id = cdl.expenditure_item_id
2063 AND ei.cost_burden_distributed_flag = 'S'
2064 AND ei.cost_distributed_flag = 'Y'
2065 AND ei.ind_cost_dist_rejection_code IS NULL
2066 AND cdl.line_type ='R'
2067 AND cdl.line_num_reversed IS NULL
2068 AND cdl.reversed_flag IS NULL
2069 AND pa_funds_control_utils.get_bdgt_link(NVL( cdl.project_id, -99)
2070 ,'STD'
2071 ) = 'Y'
2072 ;
2073
2074 /*
2075 * The following cursor will fetch the 'D' lines created in this
2076 * run for a given eiid.
2077 */
2078
2079 CURSOR c2( p_cur_eiid IN NUMBER
2080 ,p_cur_request_id IN NUMBER)
2081 IS
2082 SELECT cdl.dr_code_combination_id
2083 FROM pa_cost_distribution_lines cdl
2084 WHERE cdl.expenditure_item_id = p_cur_eiid
2085 AND cdl.request_id = p_cur_request_id
2086 AND cdl.line_type = 'D'
2087 ;
2088
2089 BEGIN
2090 /*
2091 * For a cursor of 'R' lines of the EIs fetched for distribution
2092 * map one-by-one and match it with its corresponding 'D' line
2093 * and accordingly upate the dist_rejection_code.
2094 * finally delete 'D' and 'C' lines which have ei.dist_rejection_code
2095 * as not null.
2096 */
2097 pa_debug.init_err_stack('pa_bc_costing.validate_debit_lines');
2098
2099 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
2100 l_debug_mode := NVL(l_debug_mode, 'N');
2101
2102 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
2103
2104 l_stage := 100;
2105 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':From validate_debit_lines';
2106 IF P_DEBUG_MODE = 'Y' THEN
2107 pa_debug.write_file('validate_debit_lines: ' || pa_debug.g_err_stage);
2108 END IF;
2109
2113 pa_debug.write_file('validate_debit_lines: ' || pa_debug.g_err_stage);
2110 g_request_id := p_request_id;
2111 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':Request_id is [' || TO_CHAR(g_request_id) || ']';
2112 IF P_DEBUG_MODE = 'Y' THEN
2114 END IF;
2115
2116 FOR c1_rec IN c1
2117 LOOP
2118 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':Processing cdl [' || c1_rec.expenditure_item_id ||
2119 '] line_num [' || c1_rec.line_num ||
2120 '] bccid [' || c1_rec.budget_ccid ||
2121 ']' ;
2122 IF P_DEBUG_MODE = 'Y' THEN
2123 pa_debug.write_file('validate_debit_lines: ' || pa_debug.g_err_stage);
2124 END IF;
2125
2126 l_stage := 1000;
2127 l_counter := l_counter + 1;
2128 l_expenditure_item_id_tab(l_counter) := c1_rec.expenditure_item_id;
2129 l_cost_dist_rejection_code_tab(l_counter) := NULL;
2130
2131 FOR c2_rec IN c2( l_expenditure_item_id_tab(l_counter)
2132 ,g_request_id)
2133 LOOP
2134 IF (l_cost_dist_rejection_code_tab(l_counter) IS NULL)
2135 THEN
2136 IF (c1_rec.budget_ccid IS NOT NULL)
2137 THEN
2138 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':Comparing dccid [' || TO_CHAR(c2_rec.dr_code_combination_id) ||
2139 '] and bccid [' || TO_CHAR(c1_rec.budget_ccid) || ']';
2140 IF P_DEBUG_MODE = 'Y' THEN
2141 pa_debug.write_file('validate_debit_lines: ' || pa_debug.g_err_stage);
2142 END IF;
2143
2144 --R12 - commented the budget ccid validation and moved the cost dist rejection code assignment to NULL here
2145 l_cost_dist_rejection_code_tab(l_counter) := NULL;
2146
2147 /* R12
2148 IF (c2_rec.dr_code_combination_id <> c1_rec.budget_ccid)
2149 THEN
2150 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':Decided to reject the EI.';
2151 IF P_DEBUG_MODE = 'Y' THEN
2152 pa_debug.write_file('validate_debit_lines: ' || pa_debug.g_err_stage);
2153 END IF;
2154 l_cost_dist_rejection_code_tab(l_counter) := 'F107';
2155 ELSE
2156 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':EI validation passed.';
2157 IF P_DEBUG_MODE = 'Y' THEN
2158 pa_debug.write_file('validate_debit_lines: ' || pa_debug.g_err_stage);
2159 END IF;
2160 l_cost_dist_rejection_code_tab(l_counter) := NULL;
2161 END IF;
2162 */
2163
2164 ELSE
2165 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':B_ccid in the raw line is null';
2166 IF P_DEBUG_MODE = 'Y' THEN
2167 pa_debug.write_file('validate_debit_lines: ' || pa_debug.g_err_stage);
2168 END IF;
2169 l_cost_dist_rejection_code_tab(l_counter) := 'F165';
2170 END IF;
2171 END IF;
2172 END LOOP; -- c2_rec
2173 END LOOP; -- c1_rec
2174
2175 l_records_processed := l_counter;
2179 END IF;
2176 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':No. of EIs validated [' || TO_CHAR(l_records_processed) || ']';
2177 IF P_DEBUG_MODE = 'Y' THEN
2178 pa_debug.write_file('validate_debit_lines: ' || pa_debug.g_err_stage);
2180
2181 IF (l_expenditure_item_id_tab.COUNT > 0)
2182 THEN
2183 FOR i IN 1 .. l_expenditure_item_id_tab.LAST
2184 LOOP
2185 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':Eiid [' ||
2186 TO_CHAR(l_expenditure_item_id_tab(i)) ||
2187 '] rej_code [' || l_cost_dist_rejection_code_tab(i) || ']';
2188 IF P_DEBUG_MODE = 'Y' THEN
2189 pa_debug.write_file('validate_debit_lines: ' || pa_debug.g_err_stage);
2190 END IF;
2191 END LOOP;
2192 END IF;
2193
2194 IF (l_expenditure_item_id_tab.COUNT > 0)
2195 THEN
2196 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':Updating cost_dist_rejection_code in EIs.';
2197 IF P_DEBUG_MODE = 'Y' THEN
2198 pa_debug.write_file('validate_debit_lines: ' || pa_debug.g_err_stage);
2199 END IF;
2200 /*
2201 * Update cost_dist_rejection_code.
2202 */
2203 FORALL i IN 1 .. l_expenditure_item_id_tab.LAST
2204 UPDATE pa_expenditure_items ei
2205 SET ei.ind_cost_dist_rejection_code = l_cost_dist_rejection_code_tab(i)
2206 WHERE ei.expenditure_item_id = l_expenditure_item_id_tab(i)
2207 AND l_cost_dist_rejection_code_tab(i) IS NOT NULL
2208 AND ei.ind_cost_dist_rejection_code IS NULL
2209 ;
2210
2211 l_stage := 1000;
2212 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':No. of EIs updated with cost_dist_rejection_code [' ||
2213 TO_CHAR(SQL%ROWCOUNT) || ']' ;
2214 IF P_DEBUG_MODE = 'Y' THEN
2215 pa_debug.write_file('validate_debit_lines: ' || pa_debug.g_err_stage);
2216 END IF;
2217
2218 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':Deleting C and D lines for rejected EIs.';
2219 IF P_DEBUG_MODE = 'Y' THEN
2220 pa_debug.write_file('validate_debit_lines: ' || pa_debug.g_err_stage);
2221 END IF;
2222
2223 /*
2224 * Delete errored CDLs.
2225 */
2226 FORALL i IN 1 .. l_expenditure_item_id_tab.LAST
2227 DELETE
2228 FROM pa_cost_distribution_lines cdl
2229 WHERE cdl.line_type IN ('C', 'D')
2230 AND cdl.request_id = g_request_id
2231 AND cdl.expenditure_item_id = l_expenditure_item_id_tab(i)
2232 AND l_cost_dist_rejection_code_tab(i) IS NOT NULL
2233 ;
2234
2235 l_stage := 1100;
2236 l_cdls_deleted := SQL%ROWCOUNT;
2237 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':No. of CDLs deleted should be an even number.';
2238 IF P_DEBUG_MODE = 'Y' THEN
2239 pa_debug.write_file('validate_debit_lines: ' || pa_debug.g_err_stage);
2240 END IF;
2241
2242 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':No. of CDLs deleted [' || TO_CHAR(l_cdls_deleted) || ']' ;
2243 IF P_DEBUG_MODE = 'Y' THEN
2244 pa_debug.write_file('validate_debit_lines: ' || pa_debug.g_err_stage);
2245 END IF;
2246 END IF; -- anything got mapped??
2247
2248 x_return_status := 0;
2249 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':Leaving validate_debit_lines.';
2250 IF P_DEBUG_MODE = 'Y' THEN
2251 pa_debug.write_file('validate_debit_lines: ' || pa_debug.g_err_stage);
2252 END IF;
2253
2254 pa_debug.reset_err_stack;
2255
2256 EXCEPTION
2257 WHEN OTHERS
2258 THEN
2259 IF P_DEBUG_MODE = 'Y' THEN
2260 pa_debug.write_file('validate_debit_lines: ' || pa_debug.g_err_stage);
2261 END IF;
2262 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2263 x_error_code := TO_CHAR(SQLCODE) || SQLERRM ;
2264 x_error_stage := l_stage ;
2265 RAISE;
2266
2267 END validate_debit_lines;
2271 +=============================================================================*/
2268 --------------------------------------------------------------------------------------
2269 /*=============================================================================+
2270 | CWK: This API does the funds-checking for Contingent Worker transactions. |
2272 PROCEDURE costing_fc_proc_cwk ( p_calling_module IN VARCHAR2
2273 ,p_request_id IN NUMBER
2274 ,x_return_status OUT NOCOPY NUMBER
2275 ,x_error_code OUT NOCOPY VARCHAR2
2276 ,x_error_stage OUT NOCOPY NUMBER
2277 )
2278 IS
2279
2280 /*
2281 * Processing related variables.
2282 */
2283 l_calling_module VARCHAR2(20) ;
2284 l_debug_mode VARCHAR2(1);
2285 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
2286 l_error_code VARCHAR2(30);
2287 l_error_stage VARCHAR2(30);
2288 l_proc_name VARCHAR2(50) := 'costing_fc_proc_cwk';
2289 l_records_affected NUMBER := 0;
2290 l_stage NUMBER ;
2291 l_bunch_size PLS_INTEGER := 100;
2292 l_this_fetch PLS_INTEGER := 0;
2293 l_totally_fetched PLS_INTEGER := 0;
2294 l_totally_processed PLS_INTEGER := 0;
2295 l_ei_to_process_from pa_expenditure_items_all.expenditure_item_id%TYPE := 0;
2296
2297 /*
2298 * Cursor Declaration.
2299 */
2300
2301 CURSOR pa_bc_packet_cwk_cur
2302 IS
2303 SELECT cdl.expenditure_item_id
2304 ,cdl.line_num
2305 ,cdl.line_type
2306 ,cdl.line_num_reversed
2307 ,decode(cdl.line_type, 'I', ei.acct_raw_cost, cdl.acct_raw_cost)
2308 ,decode(cdl.line_type, 'I', ei.denom_raw_cost, cdl.denom_raw_cost)
2309 ,cdl.acct_burdened_cost
2310 ,cdl.denom_burdened_cost
2311 ,cdl.project_id
2312 ,cdl.pa_date
2313 ,cdl.gl_date
2314 ,cdl.burden_sum_rejection_code
2315 ,cdl.burden_sum_source_run_id
2316 ,cdl.ind_compiled_set_id
2317 ,cdl.dr_code_combination_id
2318 ,TO_NUMBER(cdl.system_reference2) po_header_id
2319 ,glp.period_name
2320 ,ei.expenditure_item_date
2321 ,ei.expenditure_type
2322 ,cdl.task_id
2323 ,ei.po_line_id
2324 ,NVL(ei.override_to_organization_id, exp.incurred_by_organization_id)
2325 ,NVL(ei.org_id, -99)
2326 ,NVL(pt.burden_amt_display_method, 'S')
2327 ,NVL(pt.burden_cost_flag, 'N')
2328 ,bv.budget_version_id
2329 ,'EXP' reference1
2330 ,cdl.expenditure_item_id reference2
2331 ,cdl.line_num reference3
2332 FROM pa_expenditure_items_all ei
2333 ,pa_cost_distribution_lines_all cdl
2334 ,pa_project_types_all pt
2335 ,pa_projects_all p
2336 ,pa_expenditures exp
2337 ,pa_budget_versions bv
2338 ,pa_budgetary_control_options pbct
2339 ,gl_period_statuses glp
2340 ,po_distributions_all pod /* 6989758 */
2341 WHERE ei.cost_distributed_flag = 'S'
2342 AND ei.request_id = g_request_id
2343 AND ei.cost_dist_rejection_code IS NULL
2344 AND ei.denom_raw_cost IS NOT NULL
2345 AND ei.system_linkage_function IN ('ST')
2346 AND ei.expenditure_id = exp.expenditure_id
2347 AND ei.expenditure_item_id > l_ei_to_process_from
2348 AND cdl.request_id = g_request_id
2349 AND cdl.line_type in ('R', 'I')
2350 AND cdl.expenditure_item_id = ei.expenditure_item_id
2351 AND NVL(cdl.reversed_flag, 'N') <> 'Y'
2352 AND cdl.project_id = p.project_id
2353 AND p.project_type = pt.project_type
2354 -- R12 AND NVL(pt.org_Id, -99) = NVL(p.org_Id, -99)
2355 AND pt.org_Id = p.org_Id
2356 AND glp.application_id = 101
2357 AND glp.set_of_books_id = g_sob_id
2358 AND TRUNC(cdl.gl_date) BETWEEN TRUNC(glp.START_DATE) AND TRUNC(glp.END_DATE)
2359 AND pbct.project_id = bv.project_id
2360 AND pbct.BDGT_CNTRL_FLAG = 'Y'
2361 AND pbct.BUDGET_TYPE_CODE = bv.budget_type_code
2362 AND (pbct.EXTERNAL_BUDGET_CODE = 'GL'
2363 OR
2364 pbct.EXTERNAL_BUDGET_CODE IS NULL)
2365 AND bv.project_id = cdl.project_id
2366 AND bv.current_flag = 'Y'
2367 AND bv.budget_status_code = 'B'
2368 AND ei.po_line_id IS NOT NULL
2369 --FP M changes
2370 And adjustment_period_flag = 'N'
2371 AND to_char(pod.po_header_id) = cdl.system_reference2 /* 6989758 */
2372 AND ei.po_line_id = pod.po_line_id /* 6989758 */
2373 AND cdl.project_id = pod.project_id /* 6989758 */
2374 AND cdl.task_id = pod.task_id /* 6989758 */
2375 ORDER BY cdl.expenditure_item_id
2376 ,cdl.line_num
2377 ;
2378
2379
2380 BEGIN
2381 pa_debug.init_err_stack('pa_bc_costing.costing_fc_proc_cwk');
2382
2383 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
2384 l_debug_mode := NVL(l_debug_mode, 'Y');
2385
2386 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
2387
2388 l_stage := 100;
2389 pa_debug.g_err_stage := TO_CHAR(l_stage) || ': From costing_fc_proc_cwk R112';
2390 IF P_DEBUG_MODE = 'Y' THEN
2391 pa_debug.write_file(l_proc_name || ': ' || pa_debug.g_err_stage);
2392 END IF;
2393
2394 /*
2398 g_request_id := p_request_id ;
2395 * Copy incoming parameters into Local variables.
2396 */
2397 l_calling_module := p_calling_module ;
2399
2400 pa_debug.g_err_stage := to_char(l_stage) || ': Request Id is [' || to_char(g_request_id) || ']' ;
2401 IF P_DEBUG_MODE = 'Y' THEN
2402 pa_debug.write_file(l_proc_name || ': ' || pa_debug.g_err_stage);
2403 END IF;
2404
2405 /*
2406 * Get the sob_id.
2407 */
2408 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':ORACLE error occurred while selecting pa_implementations';
2409 SELECT set_of_books_id
2410 INTO g_sob_id
2411 FROM pa_implementations;
2412
2413 pa_debug.g_err_stage := TO_CHAR(l_stage) || ': Sob_id is [' || TO_CHAR(g_sob_id) || ']' ;
2414 IF P_DEBUG_MODE = 'Y' THEN
2415 pa_debug.write_file(l_proc_name|| ': ' || pa_debug.g_err_stage);
2416 END IF;
2417
2418 /*
2419 * Select Expenditure_item_ids to process.
2420 *
2421 * We should get rid of this sql - because the columns selected here
2422 * can be received from the pro*C process as arrays.
2423 */
2424 l_stage := 200;
2425 pa_debug.g_err_stage := TO_CHAR(l_stage) || ': ORACLE error occurred Opening pa_bc_packet_cwk_cur.';
2426 OPEN pa_bc_packet_cwk_cur;
2427 /*
2428 * Resetting fetch-related variables.
2429 */
2430 l_this_fetch := 0;
2431 l_totally_fetched := 0;
2432
2433 /*
2434 * Loop until all EIs are processed.
2435 */
2436 LOOP
2437
2438 PA_DEBUG.g_err_stage := TO_CHAR(l_stage) || ': Fetching a Set of CDLs to Process.';
2439 IF P_DEBUG_MODE = 'Y' THEN
2440 PA_DEBUG.write_file(l_proc_name || ': ' || PA_DEBUG.g_err_stage);
2441 END IF;
2442
2443 l_stage := 300;
2444 pa_debug.g_err_stage := TO_CHAR(l_stage) || ': ORACLE error occurred Fetching pa_bc_packet_cwk_cur.';
2445 FETCH pa_bc_packet_cwk_cur
2446 BULK COLLECT
2447 INTO l_expenditure_item_id_tab
2448 ,l_line_num_tab
2449 ,l_line_type_tab
2450 ,l_line_num_reversed_tab
2451 ,l_acct_raw_cost_tab
2452 ,l_denom_raw_cost_tab
2453 ,l_acct_burdened_cost_tab
2454 ,l_denom_burdened_cost_tab
2455 ,l_project_id_tab
2456 ,l_pa_date_tab
2457 ,l_gl_date_tab
2458 ,l_burden_sum_rej_code_tab
2459 ,l_burden_sum_source_run_id_tab
2460 ,l_ind_compiled_set_id_tab
2461 ,l_dr_code_combination_id_tab
2462 ,l_document_header_id_tab
2463 ,l_gl_period_name_tab
2464 ,l_expenditure_item_date_tab
2465 ,l_expenditure_type_tab
2466 ,l_task_id_tab
2467 ,l_document_line_id_tab
2468 ,l_exp_organization_id_tab
2469 ,l_org_id_tab
2470 ,l_burden_amt_disp_method_tab
2471 ,l_burden_cost_flag_tab
2472 ,l_budget_version_id_tab
2473 ,l_pkt_reference1_Tab
2474 ,l_pkt_reference2_Tab
2475 ,l_pkt_reference3_Tab
2476 LIMIT l_bunch_size;
2477
2478 /*==========================================+
2479 | Once fetched, reset l_ei_to_process_from |
2480 +==========================================*/
2481 l_ei_to_process_from := 0;
2482
2483 l_this_fetch := pa_bc_packet_cwk_cur%ROWCOUNT - l_totally_fetched;
2484 l_totally_fetched := pa_bc_packet_cwk_cur%ROWCOUNT;
2485 l_totally_processed := l_totally_processed + l_this_fetch;
2486
2487 PA_DEBUG.g_err_stage := TO_CHAR(l_stage) || ': Fetched [' || l_this_fetch || '] CDLs to process.';
2488 IF P_DEBUG_MODE = 'Y' THEN
2489 PA_DEBUG.write_file(l_proc_name || ': ' || PA_DEBUG.g_err_stage);
2490 END IF;
2491
2492 IF (l_this_fetch = 0) THEN
2493 PA_DEBUG.g_err_stage := TO_CHAR(l_stage) || ': No more CDLs to process. Exiting';
2494 IF P_DEBUG_MODE = 'Y' THEN
2495 PA_DEBUG.write_file(l_proc_name || ': ' || PA_DEBUG.g_err_stage);
2496 END IF;
2497 x_return_status := 0;
2498 x_error_code := FND_API.G_RET_STS_SUCCESS;
2499 x_error_stage := l_stage;
2500 EXIT;
2501 END IF;
2502 /*
2503 * We got to ensure that all cdls of an ei end-up in the same packet.
2504 * For this, we are ordering the cursor by eiid and line_num.
2505 * Now we have fetched n number of CDLs.
2506 * -- If the nth CDL is a reversing one, (line_num_reversed <> NULL)
2507 * then there should be a fresh CDL which we are missing. So,
2508 * get that and append it to the current pl/sql table. And ensure
2509 * that we dont get any cdl of this ei during the next fetch.
2510 * Assumption#1:- In-case of reversing CDL, Line_num for the fresh CDL
2511 * is greater than the line_num of he reversing CDL.
2512 */
2513 IF (l_line_num_reversed_tab(l_this_fetch) IS NOT NULL )
2514 THEN
2515 /*
2516 * Get the Fresh line.
2517 */
2518 /*=========================================================+
2519 | Burdening Enhancements |
2520 | o Funds Check both R and I lines. |
2521 | o Transfer Status Code P for R lines and G for I lines. |
2522 +=========================================================*/
2523 PA_DEBUG.g_err_stage := TO_CHAR(l_stage) || ': Fresh line Missing. Selecting Fresh line.';
2527
2524 IF P_DEBUG_MODE = 'Y' THEN
2525 PA_DEBUG.write_file(l_proc_name || ': ' || PA_DEBUG.g_err_stage);
2526 END IF;
2528 l_stage := 400;
2529 SELECT cdl.expenditure_item_id
2530 ,cdl.line_num
2531 ,cdl.line_type
2532 ,cdl.line_num_reversed
2533 ,DECODE(cdl.line_type, 'R', cdl.acct_raw_cost, cdl.acct_burdened_cost)
2534 ,DECODE(cdl.line_type, 'R', cdl.denom_raw_cost, cdl.denom_burdened_cost)
2535 ,cdl.acct_burdened_cost
2536 ,cdl.denom_burdened_cost
2537 ,cdl.project_id
2538 ,cdl.pa_date
2539 ,cdl.gl_date
2540 ,cdl.burden_sum_rejection_code
2541 ,cdl.burden_sum_source_run_id
2542 ,cdl.ind_compiled_set_id
2543 ,cdl.dr_code_combination_id
2544 ,TO_NUMBER(cdl.system_reference2)
2545 ,glp.period_name
2546 ,l_expenditure_item_date_tab(l_this_fetch)
2547 ,l_expenditure_type_tab(l_this_fetch)
2548 ,l_task_id_tab(l_this_fetch)
2549 ,l_document_line_id_tab(l_this_fetch)
2550 ,l_exp_organization_id_tab(l_this_fetch)
2551 ,l_org_id_tab(l_this_fetch)
2552 ,NVL(pt.burden_amt_display_method, 'S')
2553 ,NVL(pt.burden_cost_flag, 'N')
2554 ,bv.budget_version_id
2555 ,'EXP'
2556 ,cdl.expenditure_item_id
2557 ,cdl.line_num
2558 INTO l_expenditure_item_id_tab(l_this_fetch+1)
2559 ,l_line_num_tab(l_this_fetch+1)
2560 ,l_line_type_tab(l_this_fetch+1)
2561 ,l_line_num_reversed_tab(l_this_fetch+1)
2562 ,l_acct_raw_cost_tab(l_this_fetch+1)
2563 ,l_denom_raw_cost_tab(l_this_fetch+1)
2564 ,l_acct_burdened_cost_tab(l_this_fetch+1)
2565 ,l_denom_burdened_cost_tab(l_this_fetch+1)
2566 ,l_project_id_tab(l_this_fetch+1)
2567 ,l_pa_date_tab(l_this_fetch+1)
2568 ,l_gl_date_tab(l_this_fetch+1)
2569 ,l_burden_sum_rej_code_tab(l_this_fetch+1)
2570 ,l_burden_sum_source_run_id_tab(l_this_fetch+1)
2571 ,l_ind_compiled_set_id_tab(l_this_fetch+1)
2572 ,l_dr_code_combination_id_tab(l_this_fetch+1)
2573 ,l_document_header_id_tab(l_this_fetch+1)
2574 ,l_gl_period_name_tab(l_this_fetch+1)
2575 ,l_expenditure_item_date_tab(l_this_fetch+1)
2576 ,l_expenditure_type_tab(l_this_fetch+1)
2577 ,l_task_id_tab(l_this_fetch+1)
2578 ,l_document_line_id_tab(l_this_fetch+1)
2579 ,l_exp_organization_id_tab(l_this_fetch+1)
2580 ,l_org_id_tab(l_this_fetch+1)
2581 ,l_burden_amt_disp_method_tab(l_this_fetch+1)
2582 ,l_burden_cost_flag_tab(l_this_fetch+1)
2583 ,l_budget_version_id_tab(l_this_fetch+1)
2584 ,l_pkt_reference1_Tab(l_this_fetch+1)
2585 ,l_pkt_reference2_Tab(l_this_fetch+1)
2586 ,l_pkt_reference3_Tab(l_this_fetch+1)
2587 FROM pa_cost_distribution_lines_all cdl
2588 ,pa_project_types_all pt
2589 ,pa_projects_all p
2590 ,pa_budget_versions bv
2591 ,pa_budgetary_control_options pbct
2592 ,gl_period_statuses glp
2593 WHERE cdl.line_num_reversed IS NULL
2594 AND cdl.reversed_flag IS NULL
2595 AND cdl.request_id = g_request_id
2596 AND cdl.line_type in ('R', 'I')
2597 AND cdl.expenditure_item_id = l_expenditure_item_id_tab(l_this_fetch)
2598 AND p.project_id = cdl.project_id
2599 AND p.project_type = pt.project_type
2600 -- R12 AND NVL(pt.org_Id, -99) = NVL(p.org_Id, -99)
2601 AND pt.org_Id = p.org_Id
2602 AND glp.application_id = 101
2603 AND glp.set_of_books_id = g_sob_id
2604 AND TRUNC(cdl.gl_date) between TRUNC(glp.START_DATE) and TRUNC(glp.END_DATE)
2605 AND pbct.project_id = bv.project_id
2606 AND pbct.BDGT_CNTRL_FLAG = 'Y'
2607 AND pbct.BUDGET_TYPE_CODE = bv.budget_type_code
2608 AND (pbct.EXTERNAL_BUDGET_CODE = 'GL'
2609 OR
2610 pbct.EXTERNAL_BUDGET_CODE is NULL)
2611 AND bv.project_id = cdl.project_id
2612 AND bv.current_flag = 'Y'
2613 AND bv.budget_status_code = 'B'
2614 --FP M changes
2615 And adjustment_period_flag = 'N' ;
2616
2617 l_totally_processed := l_totally_processed + 1;
2618 /*================================================================+
2619 | l_ei_to_process_from is maintained to avoid fetching this EI |
2620 | again during the next fetch. |
2621 +================================================================*/
2622 l_ei_to_process_from := l_expenditure_item_id_tab(l_this_fetch);
2623
2624 IF (l_debug_mode = 'Y')
2625 THEN
2626 pa_debug.g_err_stage := ' l_ei_to_process_from is [' || to_char(l_ei_to_process_from) || ']';
2627 pa_debug.write_file(l_proc_name || ': ' || pa_debug.g_err_stage);
2628 END IF;
2629
2630 IF (l_debug_mode = 'Y')
2631 THEN
2632 pa_debug.g_err_stage := 'Fresh cdl [' || l_expenditure_item_id_tab(l_this_fetch+1) ||
2636 '] p_id [' || l_project_id_tab(l_this_fetch+1) ||
2633 '] line_num [' || l_line_num_tab(l_this_fetch+1) ||
2634 '] line_type [' || l_line_type_tab(l_this_fetch+1) ||
2635 '] line_num_reversed [' || l_line_num_reversed_tab(l_this_fetch+1) ||
2637 '] pa_date [' || l_pa_date_tab(l_this_fetch+1) ||
2638 '] gl_date [' || l_gl_date_tab(l_this_fetch+1) ||
2639 '] acct_rc [' || l_acct_raw_cost_tab(l_this_fetch+1) ||
2640 '] denom_rc [' || l_denom_raw_cost_tab(l_this_fetch+1) ||
2641 '] acct_bc [' || l_acct_burdened_cost_tab(l_this_fetch+1) ||
2642 '] burden_sum_rej_code [' || l_burden_sum_rej_code_tab(l_this_fetch+1) ||
2643 '] bssrid [' || l_burden_sum_source_run_id_tab(l_this_fetch+1) ||
2644 '] comp_set_id [' || l_ind_compiled_set_id_tab(l_this_fetch+1) ||
2645 ']';
2646 IF P_DEBUG_MODE = 'Y' THEN
2647 pa_debug.write_file(l_proc_name || ': ' || pa_debug.g_err_stage);
2648 END IF;
2649 pa_debug.g_err_stage := 'dr_ccid [' || l_dr_code_combination_id_tab(l_this_fetch+1) ||
2650 '] gl_p_name [' || l_gl_period_name_tab(l_this_fetch+1) ||
2651 '] etype [' || l_expenditure_type_tab(l_this_fetch+1) ||
2652 '] task_id [' || l_task_id_tab(l_this_fetch+1) ||
2653 '] po_line_id [' || l_document_line_id_tab(l_this_fetch+1) ||
2654 '] eorg_id [' || l_exp_organization_id_tab(l_this_fetch+1) ||
2655 '] org_id [' || l_org_id_tab(l_this_fetch+1) ||
2656 '] b_dsp_meth [' || l_burden_amt_disp_method_tab(l_this_fetch+1) ||
2657 '] b_version_id [' || l_budget_version_id_tab(l_this_fetch+1) ||
2658 '] burdened [' || l_burden_cost_flag_tab(l_this_fetch+1) ||
2659 ']';
2660 IF P_DEBUG_MODE = 'Y' THEN
2661 pa_debug.write_file(l_proc_name || ': ' || pa_debug.g_err_stage);
2662 END IF;
2663 END IF; -- debug mode?
2664
2665
2666 END IF; -- is the nth cdl a reversing one?
2667
2668 /*
2669 * Printing fetched values.
2670 */
2671 IF (l_debug_mode = 'Y')
2672 THEN
2673 FOR i IN l_expenditure_item_id_tab.FIRST .. l_expenditure_item_id_tab.LAST
2674 LOOP
2675 pa_debug.g_err_stage := 'eiid [' || l_expenditure_item_id_tab(i) ||
2676 '] line_num [' || l_line_num_tab(i) ||
2677 '] line_type [' || l_line_type_tab(i) ||
2678 '] line_num_reversed [' || l_line_num_reversed_tab(i) ||
2679 '] p_id [' || l_project_id_tab(i) ||
2680 '] pa_date [' || l_pa_date_tab(i) ||
2681 '] gl_date [' || l_gl_date_tab(i) ||
2682 '] acct_rc [' || l_acct_raw_cost_tab(i) ||
2683 '] denom_rc [' || l_denom_raw_cost_tab(i) ||
2684 '] acct_bc [' || l_acct_burdened_cost_tab(i) ||
2685 '] burden_sum_rej_code [' || l_burden_sum_rej_code_tab(i) ||
2686 '] bssrid [' || l_burden_sum_source_run_id_tab(i) ||
2687 '] comp_set_id [' || l_ind_compiled_set_id_tab(i) ||
2688 '] dr_ccid [' || l_dr_code_combination_id_tab(i) ||
2689 '] gl_p_name [' || l_gl_period_name_tab(i) ||
2690 '] etype [' || l_expenditure_type_tab(i) ||
2691 '] task_id [' || l_task_id_tab(i) ||
2692 '] po_line_id [' || l_document_line_id_tab(i) ||
2693 '] eorg_id [' || l_exp_organization_id_tab(i) ||
2694 '] org_id [' || l_org_id_tab(i) ||
2695 '] b_dsp_meth [' || l_burden_amt_disp_method_tab(i) ||
2696 '] b_version_id [' || l_budget_version_id_tab(i) ||
2697 '] burdened [' || l_burden_cost_flag_tab(i) ||
2698 '] header_id [' || l_document_header_id_tab(i) ||
2699 '] line_id [' || l_document_line_id_tab(i) ||
2700 ']';
2701 IF P_DEBUG_MODE = 'Y' THEN
2702 pa_debug.write_file(l_proc_name || ': ' || pa_debug.g_err_stage);
2703 END IF;
2704
2705 END LOOP;
2706 END IF; -- debug mode?
2707
2708 /*
2709 * Get the Packet_id
2710 */
2711 PA_DEBUG.g_err_stage := TO_CHAR(l_stage) || ':Getting the packet_id.';
2712 IF P_DEBUG_MODE = 'Y' THEN
2713 pa_debug.write_file(l_proc_name || ': ' || pa_debug.g_err_stage);
2714 END IF;
2715
2716 l_stage := 500;
2717 SELECT gl_bc_packets_s.NEXTVAL
2718 INTO g_packet_id
2719 FROM dual;
2720
2721 pa_debug.g_err_stage := TO_CHAR(l_stage) || ': Packet_id is [' || TO_CHAR(g_packet_id) || ']' ;
2722 IF P_DEBUG_MODE = 'Y' THEN
2723 pa_debug.write_file(l_proc_name || ': ' || pa_debug.g_err_stage);
2724 END IF;
2725
2729 pa_debug.g_err_stage := TO_CHAR(l_stage) || ': Before Calling populate_pa_bc_packets.';
2726 /*==========================================================================+
2727 | Call Autonomous Procedure to insert the pl/sql tables into pa_bc_packets.|
2728 +==========================================================================*/
2730 IF P_DEBUG_MODE = 'Y' THEN
2731 pa_debug.write_file(l_proc_name || ': ' || pa_debug.g_err_stage);
2732 END IF;
2733
2734 l_stage := 600;
2735 populate_pa_bc_packets_cwk( l_return_status
2736 ,l_error_code
2737 ,l_error_stage
2738 );
2739
2740 pa_debug.g_err_stage := TO_CHAR(l_stage) || ': After calling populate_pa_bc_packets l_return_status = [' ||
2741 l_return_status || '] l_error_stage = [' || l_error_stage ||
2742 '] l_error_code = [' || l_error_code || ']' ;
2743 IF P_DEBUG_MODE = 'Y' THEN
2744 pa_debug.write_file(l_proc_name || ':' || pa_debug.g_err_stage);
2745 END IF;
2746
2747 /*========================+
2748 | Exception Processing. |
2749 +========================*/
2750 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
2751 THEN
2752 pa_debug.g_err_stage := 'Error occurred while call to populate_pa_bc_packets. x_return_status [' ||
2753 l_return_status || ']';
2754 IF P_DEBUG_MODE = 'Y' THEN
2755 pa_debug.write_file(l_proc_name || ':' || pa_debug.g_err_stage);
2756 END IF;
2757 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2758 END IF;
2759
2760 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':After Inserting Records into PA_BC_PACKETS.';
2761 IF P_DEBUG_MODE = 'Y' THEN
2762 pa_debug.write_file(l_proc_name || ':' || pa_debug.g_err_stage);
2763 END IF;
2764
2765 /*=================================+
2766 | Reject EIs that have rejections |
2767 +=================================*/
2768 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':Rejecting EIs.' ;
2769 pa_debug.write_file(pa_debug.g_err_stage);
2770
2771 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':ORACLE error while rejecting EIs.' ;
2772 FORALL i IN l_expenditure_item_id_tab.FIRST .. l_expenditure_item_id_tab.LAST
2773 UPDATE pa_expenditure_items ei
2774 SET ei.cost_dist_rejection_code = l_rejn_code_tab(i)
2775 WHERE ei.expenditure_item_id = l_expenditure_item_id_tab(i)
2776 AND l_rejn_code_tab(i) IS NOT NULL;
2777
2778 /*
2779 * This count does not represent the number of EIs updated because,
2780 * this table is for CDLs and can have more than one record for the same EI.
2781 */
2782 l_records_affected := SQL%ROWCOUNT ;
2783
2784 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':Updated [' ||
2785 TO_CHAR(l_records_affected) ||
2786 '] records - this count is not right.';
2787 pa_debug.write_file(pa_debug.g_err_stage);
2788 /*===================+
2789 | Call FC API here. |
2790 +===================*/
2791 pa_debug.g_err_stage := TO_CHAR(l_stage) || ': Calling FC API';
2792 IF P_DEBUG_MODE = 'Y' THEN
2793 pa_debug.write_file(l_proc_name || ':' || pa_debug.g_err_stage);
2794 END IF;
2795
2796 l_stage := 700;
2797 pa_debug.g_err_stage := 'Error Occurred during call to pa_funds_check.';
2798 IF( NOT pa_funds_control_pkg.pa_funds_check( l_calling_module -- p_calling_module
2799 ,'Y' -- p_conc_flag
2800 ,g_sob_id -- p_set_of_book_id
2801 ,g_packet_id -- p_packet_id
2802 ,'R' -- p_mode
2803 ,'Y' -- p_partial_flag
2804 ,NULL -- p_reference1
2805 ,NULL -- p_reference2
2806 ,NULL -- p_reference3
2807 ,l_return_status -- x_return_status
2808 ,l_error_stage -- x_error_stage
2809 ,l_error_code -- x_error_msg
2810 ) )
2811 THEN
2812 pa_debug.g_err_stage := 'pa_funds_check returned FALSE.';
2813 IF P_DEBUG_MODE = 'Y' THEN
2814 pa_debug.write_file('costing_fc_proc: ' || pa_debug.g_err_stage);
2815 END IF;
2816
2817 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2818 END IF;
2819
2820 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':After calling FC API l_return_status =[' || l_return_status ||
2821 '] l_error_stage = [' || l_error_stage ||
2822 '] l_error_code = [' || l_error_code || ']' ;
2823 IF P_DEBUG_MODE = 'Y' THEN
2824 pa_debug.write_file(l_proc_name || ':' || pa_debug.g_err_stage);
2825 END IF;
2826
2827 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':Calling process_rejected_exp_items' ;
2828 IF P_DEBUG_MODE = 'Y' THEN
2829 pa_debug.write_file(l_proc_name || ':' || pa_debug.g_err_stage);
2830 END IF;
2831
2832 l_stage := 800;
2833 process_rejected_exp_items ( x_return_status => l_return_status
2834 ,x_error_code => l_error_code
2838 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
2835 ,x_error_stage => l_error_stage
2836 );
2837
2839 THEN
2840 pa_debug.g_err_stage := TO_CHAR(l_stage) || ':Error while call to process_rejected_exp_items. x_return_status ['
2841 || l_return_status || ']';
2842 IF P_DEBUG_MODE = 'Y' THEN
2843 pa_debug.write_file(l_proc_name || ':' || pa_debug.g_err_stage);
2844 END IF;
2845 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2846 END IF;
2847
2848 pa_debug.g_err_stage := 'After calling process_rejected_exp_items l_return_status =[' ||
2849 l_return_status ||
2850 '] l_error_stage = [' || l_error_stage ||
2851 '] l_error_code = [' || l_error_code || ']' ;
2852 IF P_DEBUG_MODE = 'Y' THEN
2853 pa_debug.write_file(l_proc_name || ': ' || TO_CHAR(l_stage) || ': ' || pa_debug.g_err_stage);
2854 END IF;
2855
2856 /*
2857 * Calling FC ends here.
2858 */
2859 IF (l_this_fetch < l_bunch_size) THEN
2860 /*
2861 * Indicates last fetch.
2862 */
2863 pa_debug.g_err_stage := 'Finished Processing Last Fetch.';
2864 IF P_DEBUG_MODE = 'Y' THEN
2865 pa_debug.write_file(l_proc_name || ': ' || TO_CHAR(l_stage) || ': ' || pa_debug.g_err_stage);
2866 END IF;
2867 EXIT;
2868 END IF;
2869 /*========================+
2870 | Deleting plsql tables. |
2871 +========================*/
2872
2873 pa_debug.g_err_stage := 'Deleting Pl/Sql tables......';
2874 IF P_DEBUG_MODE = 'Y' THEN
2875 pa_debug.write_file(l_proc_name || ': ' || TO_CHAR(l_stage) || ': ' || pa_debug.g_err_stage);
2876 END IF;
2877
2878 l_stage := 900;
2879 l_expenditure_item_id_tab.DELETE;
2880 l_line_num_tab.DELETE;
2881 l_line_type_tab.DELETE;
2882 l_line_num_reversed_tab.DELETE;
2883 l_acct_raw_cost_tab.DELETE;
2884 l_denom_raw_cost_tab.DELETE;
2885 l_acct_burdened_cost_tab.DELETE;
2886 l_denom_burdened_cost_tab.DELETE;
2887 l_project_id_tab.DELETE;
2888 l_pa_date_tab.DELETE;
2889 l_gl_date_tab.DELETE;
2890 l_burden_sum_rej_code_tab.DELETE;
2891 l_burden_sum_source_run_id_tab.DELETE;
2892 l_ind_compiled_set_id_tab.DELETE;
2893 l_dr_code_combination_id_tab.DELETE;
2894 l_gl_period_name_tab.DELETE;
2895 l_expenditure_item_date_tab.DELETE;
2896 l_expenditure_type_tab.DELETE;
2897 l_task_id_tab.DELETE;
2898 l_exp_organization_id_tab.DELETE;
2899 l_org_id_tab.DELETE;
2900 l_burden_amt_disp_method_tab.DELETE;
2901 l_burden_cost_flag_tab.DELETE;
2902 l_budget_version_id_tab.DELETE;
2903 l_pkt_reference1_Tab.DELETE;
2904 l_pkt_reference2_Tab.DELETE;
2905 l_pkt_reference3_Tab.DELETE;
2906
2907 pa_debug.g_err_stage := 'After Deleting Pl/Sql tables......';
2908 IF P_DEBUG_MODE = 'Y' THEN
2909 pa_debug.write_file(l_proc_name || ': ' || TO_CHAR(l_stage) || ': ' || pa_debug.g_err_stage);
2910 END IF;
2911
2912 /*=================================================================+
2913 | If earlier fetch had a spill-over, close and reopen the cursor. |
2914 +=================================================================*/
2915
2916 IF (l_ei_to_process_from > 0)
2917 THEN
2918 pa_debug.g_err_stage := TO_CHAR(l_stage) || 'closing cursor';
2919 IF P_DEBUG_MODE = 'Y' THEN
2920 pa_debug.write_file(l_proc_name || ': ' || TO_CHAR(l_stage) || ': ' || pa_debug.g_err_stage);
2921 END if;
2922 CLOSE pa_bc_packet_cwk_cur;
2923
2924 l_this_fetch := 0;
2925 l_totally_fetched := 0;
2926 pa_debug.g_err_stage := 'Opening cursor - to process from [' ||
2927 to_char(l_ei_to_process_from) || ']';
2928 IF P_DEBUG_MODE = 'Y' THEN
2929 pa_debug.write_file(l_proc_name || ': ' || TO_CHAR(l_stage) || ': ' || pa_debug.g_err_stage);
2930 END IF;
2931 OPEN pa_bc_packet_cwk_cur;
2932 END IF;
2933
2934 END LOOP; -- End of loop to insert total number records.
2935
2936 pa_debug.g_err_stage := 'No. Of CDLs Totally fetched [' || TO_CHAR(l_totally_fetched) ||']';
2937 IF P_DEBUG_MODE = 'Y' THEN
2938 pa_debug.write_file(l_proc_name || ': ' || TO_CHAR(l_stage) || ': ' || pa_debug.g_err_stage);
2939 END IF;
2940
2941 pa_debug.g_err_stage := 'Leaving costing_fc_proc_cwk';
2942 IF P_DEBUG_MODE = 'Y' THEN
2943 pa_debug.write_file(l_proc_name || ': ' || TO_CHAR(l_stage) || ': ' || pa_debug.g_err_stage);
2944 END IF;
2945
2946 x_return_status := 0;
2947 pa_debug.reset_err_stack;
2948
2949 EXCEPTION
2950 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
2951 THEN
2952 IF P_DEBUG_MODE = 'Y' THEN
2953 pa_debug.write_file(l_proc_name || ': ' || TO_CHAR(l_stage) || ': ' || pa_debug.g_err_stage);
2954 END IF;
2955 x_return_status := -1;
2956 x_error_code := pa_debug.g_err_stage ;
2957 x_error_stage := to_char(l_stage) ;
2958 WHEN OTHERS
2959 THEN
2960 IF P_DEBUG_MODE = 'Y' THEN
2961 pa_debug.write_file(l_proc_name || ': EXCEPTION ' || pa_debug.g_err_stage);
2962 END IF;
2963
2967 END IF;
2964 pa_debug.g_err_stage := TO_CHAR(SQLCODE) || SQLERRM ;
2965 IF P_DEBUG_MODE = 'Y' THEN
2966 pa_debug.write_file(l_proc_name || ': EXCEPTION ' || pa_debug.g_err_stage);
2968
2969 x_return_status := -1;
2970 x_error_code := TO_CHAR(SQLCODE) || SQLERRM ;
2971 x_error_stage := l_stage ;
2972 END costing_fc_proc_cwk;
2973 --------------------------------------------------------------------------------------
2974 PROCEDURE populate_pa_bc_packets_cwk( x_return_status OUT NOCOPY NUMBER
2975 ,x_error_code OUT NOCOPY VARCHAR2
2976 ,x_error_stage OUT NOCOPY VARCHAR2
2977 )
2978 IS
2979 PRAGMA AUTONOMOUS_TRANSACTION;
2980
2981 /*=============================+
2982 | PLSQL Table definitions |
2983 +=============================*/
2984 l_ins_packet_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
2985 l_ins_project_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
2986 l_ins_task_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
2987 l_ins_budget_version_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
2988 l_ins_expenditure_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
2989 l_ins_ei_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
2990 l_ins_period_name_tab PA_PLSQL_DATATYPES.Char15TabTyp;
2991 l_ins_pa_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
2992 l_ins_gl_date_tab PA_PLSQL_DATATYPES.DateTabTyp;
2993 l_ins_set_of_books_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
2994 l_ins_je_category_name_tab PA_PLSQL_DATATYPES.Char80TabTyp;
2995 l_ins_je_source_name_tab PA_PLSQL_DATATYPES.Char80TabTyp;
2996 l_ins_status_code_tab PA_PLSQL_DATATYPES.Char1TabTyp;
2997 l_ins_funds_process_mode_tab PA_PLSQL_DATATYPES.Char1TabTyp;
2998 l_ins_burden_cost_flag_tab PA_PLSQL_DATATYPES.Char1TabTyp;
2999 l_ins_expenditure_orgn_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
3000 l_ins_document_dist_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
3001 l_ins_txn_ccid_tab PA_PLSQL_DATATYPES.IdTabTyp;
3002 l_ins_bc_packet_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
3003 l_ins_org_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
3004 l_ins_balance_posted_flag_tab PA_PLSQL_DATATYPES.Char1TabTyp;
3005 l_ins_document_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
3006 l_ins_parent_bc_packet_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
3007 l_ins_document_header_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
3008 l_ins_document_line_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
3009 l_ins_entered_dr_tab PA_PLSQL_DATATYPES.NumTabTyp;
3010 l_ins_accounted_dr_tab PA_PLSQL_DATATYPES.NumTabTyp;
3011 l_ins_exp_item_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
3012 l_ins_rejn_code_tab PA_PLSQL_DATATYPES.Char80TabTyp;
3013
3014 l_temp_Tot_Raw_Amt_tab PA_PLSQL_DATATYPES.NumTabTyp;
3015 l_temp_Tot_Bd_Amt_tab PA_PLSQL_DATATYPES.NumTabTyp;
3016 l_temp_Raw_Amt_Relieved_tab PA_PLSQL_DATATYPES.NumTabTyp;
3017 l_temp_Bd_Amt_Relieved_tab PA_PLSQL_DATATYPES.NumTabTyp;
3018 l_temp_compiled_multiplier_tab PA_PLSQL_DATATYPES.NumTabTyp;
3019 l_temp_parent_bc_packet_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
3020 l_temp_expenditure_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
3021 l_temp_comm_source_tab PA_PLSQL_DATATYPES.Char30TabTyp;
3022
3023 l_summ_project_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
3024 l_summ_task_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
3025 l_summ_document_header_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
3026 l_summ_document_line_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
3027 l_summ_tot_raw_amt_tab PA_PLSQL_DATATYPES.NumTabTyp;
3028 l_summ_tot_bd_amt_tab PA_PLSQL_DATATYPES.NumTabTyp;
3029 l_summ_raw_amt_relieved_tab PA_PLSQL_DATATYPES.NumTabTyp;
3030 l_summ_bd_amt_relieved_tab PA_PLSQL_DATATYPES.NumTabTyp;
3031 l_summ_compiled_multiplier_tab PA_PLSQL_DATATYPES.NumTabTyp;
3032 l_summ_parent_bc_packet_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
3033 l_summ_expenditure_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
3034 l_summ_source_tab PA_PLSQL_DATATYPES.Char30TabTyp;
3035
3036 l_txn_burden_exp_type_tab PA_PLSQL_DATATYPES.Char30TabTyp;
3037 l_txn_burden_comp_mult_tab PA_PLSQL_DATATYPES.NumTabTyp;
3038
3039 /*==================+
3040 | Scalar variables |
3041 +==================*/
3042
3043 l_po_raw_bc_packet_id pa_bc_packets.bc_packet_id%TYPE;
3044 l_exp_raw_bc_packet_id pa_bc_packets.bc_packet_id%TYPE;
3045 l_cur_new_raw_amt_relieved pa_bc_packets.entered_dr%TYPE;
3046 l_cur_new_bd_amt_relieved pa_bc_packets.entered_dr%TYPE;
3047 l_debug_mode VARCHAR2(1);
3048 l_proc_name VARCHAR2(50) := 'populate_pa_bc_packets_cwk';
3049 l_i_raw_po_rec NUMBER;
3050 l_records_affected NUMBER := 0;
3051 l_stage VARCHAR2(300) ;
3052 i NUMBER := 0;
3056 temp_rec NUMBER := 0;
3053 j NUMBER := 0;
3054 ins_rec NUMBER := 0;
3055 i_summary NUMBER := 0;
3057 l_found BOOLEAN;
3058
3059 /* Exceptions */
3060
3061 USER_EXCEPTION EXCEPTION;
3062
3063
3064 /*=============================================+
3065 | This routine is private for this procedure. |
3066 +=============================================*/
3067 PROCEDURE copy_common_attributes( i_source IN NUMBER
3068 ,i_dest IN NUMBER
3069 )
3070 IS
3071 BEGIN
3072 l_ins_packet_id_tab(i_dest) := g_packet_id;
3073 l_ins_project_id_tab(i_dest) := l_project_id_tab(i_source);
3074 l_ins_task_id_tab(i_dest) := l_task_id_tab(i_source);
3075 l_ins_budget_version_id_tab(i_dest) := l_budget_version_id_tab(i_source);
3076 l_ins_expenditure_type_tab(i_dest) := l_expenditure_type_tab(i_source);
3077 l_ins_ei_date_tab(i_dest) := l_expenditure_item_date_tab(i_source);
3078 l_ins_period_name_tab(i_dest) := l_gl_period_name_tab(i_source);
3079 l_ins_pa_date_tab(i_dest) := l_pa_date_tab(i_source);
3080 l_ins_gl_date_tab(i_dest) := l_gl_date_tab(i_source);
3081 l_ins_set_of_books_id_tab(i_dest) := g_sob_id;
3082 l_ins_je_category_name_tab(i_dest) := 'Project Accounting';
3083 l_ins_je_source_name_tab(i_dest) := 'Expenditures';
3084 l_ins_status_code_tab(i_dest) := 'P';
3085 l_ins_funds_process_mode_tab(i_dest) := 'T' ;
3086 l_ins_burden_cost_flag_tab(i_dest) := 'R';
3087 l_ins_expenditure_orgn_id_tab(i_dest) := l_exp_organization_id_tab(i_source);
3088 l_ins_document_dist_id_tab(i_dest) := l_line_num_tab(i_source);
3089 l_ins_txn_ccid_tab(i_dest) := l_dr_code_combination_id_tab(i_source);
3090 l_ins_org_id_tab(i_dest) := l_org_id_tab(i_source);
3091 l_ins_balance_posted_flag_tab(i_dest) := 'N';
3092
3093 l_ins_document_header_id_tab(i_dest) := l_document_header_id_tab(i_source);
3094 l_ins_document_line_id_tab(i_dest) := l_document_line_id_tab(i_source);
3095 l_ins_accounted_dr_tab(i_dest) := NULL;
3096 l_ins_entered_dr_tab(i_dest) := NULL;
3097 l_ins_parent_bc_packet_id_tab(i_dest) := NULL;
3098 l_ins_bc_packet_id_tab(i_dest) := NULL;
3099 l_ins_rejn_code_tab(i_dest) := NULL;
3100 l_ins_exp_item_id_tab(i_dest) := l_expenditure_item_id_tab(i_source) ;
3101
3102 END ;
3103
3104 BEGIN
3105
3106 pa_debug.init_err_stack('pa_bc_costing.populate_pa_bc_packets_cwk');
3107
3108 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
3109 l_debug_mode := NVL(l_debug_mode, 'Y');
3110
3111 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
3112
3113 l_stage := l_proc_name || ': ' || to_char(100) || ': ';
3114 pa_debug.g_err_stage := 'From ' || l_proc_name ;
3115 pa_debug.write_file(l_stage || pa_debug.g_err_stage);
3116
3117 /*==============================================================+
3118 | Summary records are cached incrementally for a batch of EIs. |
3119 | j - is the index for the summary tables. |
3120 +==============================================================*/
3121 j := 0;
3122
3123 FOR i IN l_expenditure_item_id_tab.FIRST .. l_expenditure_item_id_tab.LAST
3124 LOOP
3125
3126 pa_debug.g_err_stage := 'Processing EI [' || to_char(l_expenditure_item_id_tab(i)) ||
3127 '] line [' || to_char(l_line_num_tab(i)) || ']';
3128 pa_debug.write_file(l_stage || pa_debug.g_err_stage);
3129 l_rejn_code_tab(i) := NULL;
3130 BEGIN
3131 /*========================================================+
3132 | Commitment amounts to be relieved only for first time |
3133 | distribution. |
3134 +========================================================*/
3135 IF ( l_line_num_tab(i) = 1 )
3136 THEN
3137 /*====================================================================+
3138 | Select summary information from either pa_bc_packets or |
3139 | pa_bc_commitments_all. Populate the summary information into |
3140 | host plsql tables. |
3141 | If the summary information of the current txn already exist in the |
3142 | plsql table, proceed with further processing. Otherwise get the |
3143 | summary record from db and populate the plsql table. |
3144 | o j holds the number of summary records in the plsql table. |
3145 +====================================================================*/
3146 l_found := FALSE;
3147 FOR k IN 1 .. j
3148 LOOP
3149 IF ( l_summ_project_id_tab(k) = l_project_id_tab(i) AND
3150 l_summ_task_id_tab(k) = l_task_id_tab(i) AND
3151 l_summ_document_header_id_tab(k) = l_document_header_id_tab(i) AND
3152 l_summ_document_line_id_tab(k) = l_document_line_id_tab(i)
3153 )
3154 THEN
3158 l_found := TRUE;
3155 pa_debug.g_err_stage := 'Summary record found in Summary Cache';
3156 pa_debug.write_file(l_stage || pa_debug.g_err_stage);
3157 i_summary := k;
3159 EXIT;
3160 END IF;
3161 END LOOP;
3162 /*============================================+
3163 | If the summary record is not available in |
3164 | the plsql table, hit the db. |
3165 +============================================*/
3166 IF (NOT l_found)
3167 THEN
3168 pa_debug.g_err_stage := 'Hitting PA_BC_COMMITMENTS for Summary record R12';
3169 pa_debug.write_file(l_stage || pa_debug.g_err_stage);
3170 BEGIN
3171 SELECT pabcc.Comm_Tot_Raw_Amt
3172 ,pabcc.Comm_Tot_Bd_Amt
3173 ,pabcc.Comm_Raw_Amt_Relieved
3174 ,pabcc.Comm_Bd_Amt_Relieved
3175 ,pabcc.compiled_multiplier
3176 ,pabcc.parent_bc_packet_id
3177 ,pabcc.expenditure_type
3178 ,'PA_BC_COMMITMENTS'
3179 BULK COLLECT
3180 INTO l_temp_Tot_Raw_Amt_tab
3181 ,l_temp_Tot_Bd_Amt_tab
3182 ,l_temp_Raw_Amt_Relieved_tab
3183 ,l_temp_Bd_Amt_Relieved_tab
3184 ,l_temp_compiled_multiplier_tab
3185 ,l_temp_parent_bc_packet_id_tab
3186 ,l_temp_expenditure_type_tab
3187 ,l_temp_comm_source_tab
3188 FROM pa_bc_commitments pabcc
3189 WHERE pabcc.document_header_id = l_document_header_id_tab(i)
3190 AND pabcc.document_line_id = l_document_line_id_tab(i)
3191 AND pabcc.project_id = l_project_id_tab(i)
3192 AND pabcc.task_id = l_task_id_tab(i)
3193 AND ( (pabcc.parent_bc_packet_id IS NOT NULL AND pabcc.Comm_Tot_Bd_Amt <> 0)
3194 OR pabcc.parent_bc_packet_id IS NULL)
3195 AND pabcc.summary_record_flag = 'Y';
3196 EXCEPTION
3197 WHEN OTHERS
3198 THEN
3199 RAISE;
3200 END; -- anonymous block
3201 /*=================================================+
3202 | If a record is not found in pa_bc_commitments, |
3203 | try pa_bc_packets. |
3204 +=================================================*/
3205 /*=========================================+
3206 | Bug 4230083 : Added 'C' to status_code. |
3207 +=========================================*/
3208 IF ( l_temp_Tot_Raw_Amt_tab.COUNT = 0 )
3209 THEN
3210 pa_debug.g_err_stage := 'Hitting PA_BC_PACKETS for Summary record';
3211 pa_debug.write_file(l_stage || pa_debug.g_err_stage);
3212 BEGIN
3213 SELECT pabc.Comm_Tot_Raw_Amt
3214 ,pabc.Comm_Tot_Bd_Amt
3215 ,pabc.Comm_Raw_Amt_Relieved
3216 ,pabc.Comm_Bd_Amt_Relieved
3217 ,pabc.compiled_multiplier
3218 ,pabc.parent_bc_packet_id
3219 ,pabc.expenditure_type
3220 ,'PA_BC_PACKETS'
3221 BULK COLLECT
3222 INTO l_temp_Tot_Raw_Amt_tab
3223 ,l_temp_Tot_Bd_Amt_tab
3224 ,l_temp_Raw_Amt_Relieved_tab
3225 ,l_temp_Bd_Amt_Relieved_tab
3226 ,l_temp_compiled_multiplier_tab
3227 ,l_temp_parent_bc_packet_id_tab
3228 ,l_temp_expenditure_type_tab
3229 ,l_temp_comm_source_tab
3230 FROM pa_bc_packets pabc
3231 WHERE pabc.document_header_id = l_document_header_id_tab(i)
3232 AND pabc.document_line_id = l_document_line_id_tab(i)
3233 AND pabc.project_id = l_project_id_tab(i)
3234 AND pabc.task_id = l_task_id_tab(i)
3235 AND ( (pabc.parent_bc_packet_id IS NOT NULL AND pabc.Comm_Tot_Bd_Amt <> 0)
3236 OR pabc.parent_bc_packet_id IS NULL)
3237 AND pabc.funds_process_mode = 'T'
3238 AND pabc.summary_record_flag = 'Y'
3239 AND pabc.status_code IN ('A', 'C');
3240 EXCEPTION
3241 WHEN OTHERS
3242 THEN
3243 RAISE;
3244 END; -- anonymous block
3245 END IF; -- record not found in pa_bc_commitments
3246
3250 pa_debug.log_message(p_message => PA_DEBUG.g_err_stage);
3247 IF ( l_temp_Tot_Raw_Amt_tab.COUNT = 0 )
3248 THEN
3249 pa_debug.g_err_stage := 'Summary record NOT found. Rejecting Transaction.';
3251 l_rejn_code_tab(i) := 'PA_FC_NDF';
3252 pa_debug.g_err_stage := 'B4 raising exception' ;
3253 pa_debug.log_message(p_message => PA_DEBUG.g_err_stage);
3254 RAISE USER_EXCEPTION;
3255 END IF;
3256
3257 FOR jj IN l_temp_Tot_Raw_Amt_tab.FIRST .. l_temp_Tot_Raw_Amt_tab.LAST
3258 LOOP
3259 pa_debug.g_err_stage := 'no [' || to_char(jj) || ']';
3260 pa_debug.write_file(l_stage || pa_debug.g_err_stage);
3261 pa_debug.g_err_stage := 'l_temp_Tot_Raw_Amt_tab [' || l_temp_Tot_Raw_Amt_tab(jj) || ']';
3262 pa_debug.write_file(l_stage || pa_debug.g_err_stage);
3263 pa_debug.g_err_stage := 'l_temp_Tot_Bd_Amt_tab [' || to_char(l_temp_Tot_Bd_Amt_tab(jj)) || ']';
3264 pa_debug.write_file(l_stage || pa_debug.g_err_stage);
3265 pa_debug.g_err_stage := 'l_temp_Raw_Amt_Relieved_tab [' || to_char(l_temp_Raw_Amt_Relieved_tab(jj)) || ']';
3266 pa_debug.write_file(l_stage || pa_debug.g_err_stage);
3267 pa_debug.g_err_stage := 'l_temp_Bd_Amt_Relieved_tab [' || to_char(l_temp_Bd_Amt_Relieved_tab(jj)) || ']';
3268 pa_debug.write_file(l_stage || pa_debug.g_err_stage);
3269 pa_debug.g_err_stage := 'l_temp_compiled_multiplier_tab [' || to_char(l_temp_compiled_multiplier_tab(jj)) || ']';
3270 pa_debug.write_file(l_stage || pa_debug.g_err_stage);
3271 pa_debug.g_err_stage := 'l_temp_parent_bc_packet_id_tab [' || to_char(l_temp_parent_bc_packet_id_tab(jj)) || ']';
3272 pa_debug.write_file(l_stage || pa_debug.g_err_stage);
3273 pa_debug.g_err_stage := 'l_temp_expenditure_type_tab [' || l_temp_expenditure_type_tab(jj) || ']';
3274 pa_debug.write_file(l_stage || pa_debug.g_err_stage);
3275 pa_debug.g_err_stage := 'l_temp_comm_source_tab [' || l_temp_comm_source_tab(jj) || ']';
3276 pa_debug.write_file(l_stage || pa_debug.g_err_stage);
3277 END LOOP;
3278
3279 /*=======================================+
3280 | Append the contents of l_temp_ to the |
3281 | main table l_summ_ |
3282 +=======================================*/
3283 FOR temp_rec IN l_temp_Tot_Raw_Amt_tab.FIRST .. l_temp_Tot_Raw_Amt_tab.LAST
3284 LOOP
3285
3286 /*======================================================+
3287 | Insert the new summary record at the end of the main |
3288 | summary plsql table |
3289 +======================================================*/
3290 j := j + 1;
3291
3292 l_summ_project_id_tab(j) := l_project_id_tab(i);
3293 l_summ_task_id_tab(j) := l_task_id_tab(i);
3294 l_summ_document_header_id_tab(j) := l_document_header_id_tab(i);
3295 l_summ_document_line_id_tab(j) := l_document_line_id_tab(i);
3296
3297 l_summ_tot_raw_amt_tab(j) := l_temp_Tot_Raw_Amt_tab(temp_rec);
3298 l_summ_tot_bd_amt_tab(j) := l_temp_Tot_Bd_Amt_tab(temp_rec);
3299 l_summ_raw_amt_relieved_tab(j) := l_temp_Raw_Amt_Relieved_tab(temp_rec);
3300 l_summ_bd_amt_relieved_tab(j) := l_temp_Bd_Amt_Relieved_tab(temp_rec);
3301 l_summ_compiled_multiplier_tab(j) := l_temp_compiled_multiplier_tab(temp_rec);
3302 l_summ_parent_bc_packet_id_tab(j) := l_temp_parent_bc_packet_id_tab(temp_rec);
3303 l_summ_expenditure_type_tab(j) := l_temp_expenditure_type_tab(temp_rec);
3304 l_summ_source_tab(j) := l_temp_comm_source_tab(temp_rec);
3305 END LOOP; -- temp records
3306 FOR jj IN l_summ_project_id_tab.FIRST .. l_summ_project_id_tab.LAST
3307 LOOP
3308 pa_debug.g_err_stage := 'no [' || to_char(jj) || ']';
3309 pa_debug.write_file(l_stage || pa_debug.g_err_stage);
3310
3311 pa_debug.g_err_stage := 'l_summ_project_id_tab [' || to_char(l_summ_project_id_tab(jj)) || ']';
3312 pa_debug.write_file(l_stage || pa_debug.g_err_stage);
3313 pa_debug.g_err_stage := 'l_summ_task_id_tab [' || to_char(l_summ_task_id_tab(jj)) || ']';
3314 pa_debug.write_file(l_stage || pa_debug.g_err_stage);
3315 pa_debug.g_err_stage := 'l_summ_document_header_id_tab [' || to_char(l_summ_document_header_id_tab(jj)) || ']';
3316 pa_debug.write_file(l_stage || pa_debug.g_err_stage);
3317 pa_debug.g_err_stage := 'l_summ_document_line_id_tab [' || l_summ_document_line_id_tab(jj) || ']';
3318 pa_debug.write_file(l_stage || pa_debug.g_err_stage);
3319
3320 ---------
3321 pa_debug.g_err_stage := 'l_summ_tot_raw_amt_tab [' || to_char(l_summ_tot_raw_amt_tab(jj)) || ']';
3322 pa_debug.write_file(l_stage || pa_debug.g_err_stage);
3323 pa_debug.g_err_stage := 'l_summ_tot_bd_amt_tab [' || to_char(l_summ_tot_bd_amt_tab(jj)) || ']';
3324 pa_debug.write_file(l_stage || pa_debug.g_err_stage);
3325 pa_debug.g_err_stage := 'l_summ_raw_amt_relieved_tab [' || to_char(l_summ_raw_amt_relieved_tab(jj)) || ']';
3329 pa_debug.g_err_stage := 'l_summ_compiled_multiplier_tab [' || to_char(l_summ_compiled_multiplier_tab(jj)) || ']';
3326 pa_debug.write_file(l_stage || pa_debug.g_err_stage);
3327 pa_debug.g_err_stage := 'l_summ_bd_amt_relieved_tab [' || to_char(l_summ_bd_amt_relieved_tab(jj)) || ']';
3328 pa_debug.write_file(l_stage || pa_debug.g_err_stage);
3330 pa_debug.write_file(l_stage || pa_debug.g_err_stage);
3331 pa_debug.g_err_stage := 'l_summ_parent_bc_packet_id_tab [' || to_char(l_summ_parent_bc_packet_id_tab(jj)) || ']';
3332 pa_debug.write_file(l_stage || pa_debug.g_err_stage);
3333 pa_debug.g_err_stage := 'l_summ_expenditure_type_tab [' || l_summ_expenditure_type_tab(jj) || ']';
3334 pa_debug.write_file(l_stage || pa_debug.g_err_stage);
3335 pa_debug.g_err_stage := 'l_summ_source_tab [' || l_summ_source_tab(jj) || ']';
3336 pa_debug.write_file(l_stage || pa_debug.g_err_stage);
3337 END LOOP;
3338 /*======================================================+
3339 | Note :- At this point all the needed summary records |
3340 | needed to process this transaction will be available |
3341 | in the l_summ_ group of tables. |
3342 | o l_summ_ is a repository of summary records. |
3343 +======================================================*/
3344
3345 /*=============================================+
3346 | Get the corresponding Raw summary record. |
3347 +=============================================*/
3348 /**??????????see how we can avoid hitting the cache if the summary record is available in the l_temp_.........*/
3349 pa_debug.write_file('Probing Summary Cache.');
3350 l_found := FALSE;
3351 FOR summ_rec IN l_summ_project_id_tab.FIRST .. l_summ_project_id_tab.LAST
3352 LOOP
3353 IF ( l_summ_project_id_tab(summ_rec) = l_project_id_tab(i) AND
3354 l_summ_task_id_tab(summ_rec) = l_task_id_tab(i) AND
3355 l_summ_document_header_id_tab(summ_rec) = l_document_header_id_tab(i) AND
3356 l_summ_document_line_id_tab(summ_rec) = l_document_line_id_tab(i) AND
3357 l_summ_parent_bc_packet_id_tab(summ_rec) IS NULL
3358 )
3359 THEN
3360 pa_debug.write_file('Found record in Summary Cache.');
3361 l_found := TRUE;
3362 i_summary := summ_rec;
3363 EXIT;
3364 END IF;
3365 END LOOP;
3366
3367 IF ( NOT l_found )
3368 THEN
3369 pa_debug.g_err_stage := 'I just copied the summ records from DB to the cache. But now they are missing.. WIERD !!';
3370 pa_debug.write_file(l_stage || pa_debug.g_err_stage);
3371 l_rejn_code_tab(i) := 'PA_FC_NDF';
3372 RAISE USER_EXCEPTION;
3373 END IF;
3374 END IF; -- summary record not found in cache.
3375
3376 /*=========================================================+
3377 | Step 1 : Populate Raw PO relieving record. |
3378 | |
3379 | Note :- ins_rec is the index for the plsql tables to be |
3380 | inserted into pa_bc_packets. |
3381 +=========================================================*/
3382 pa_debug.g_err_stage := 'Inserting Raw PO relieving record.';
3383 pa_debug.write_file(l_stage || pa_debug.g_err_stage);
3384 ins_rec := ins_rec + 1;
3385 copy_common_attributes( i ,ins_rec );
3386 /*===========================================================+
3387 | Overriding what copy_common_attributes sets the value to. |
3388 | on Prithi's advice. |
3389 +===========================================================*/
3390 l_ins_document_dist_id_tab(ins_rec) := -9999;
3391
3392 BEGIN
3393 pa_debug.g_err_stage := 'ORACLE error selecting sequence';
3394 SELECT pa_bc_packets_s.NEXTVAL
3395 INTO l_ins_bc_packet_id_tab(ins_rec)
3396 FROM DUAL;
3397 EXCEPTION
3398 WHEN OTHERS THEN
3399 RAISE;
3400 END;
3401
3402 l_ins_document_type_tab(ins_rec) := 'PO';
3403 l_ins_expenditure_type_tab(ins_rec) := l_expenditure_type_tab(i);
3404 l_ins_document_header_id_tab(ins_rec) := l_document_header_id_tab(i);
3405
3406 /*=====================================================================+
3407 | Store the bc_packet_id of this raw record so that it can be |
3408 | populated to the parent_bc_packet_id column of the burden records. |
3409 +=====================================================================*/
3410 l_po_raw_bc_packet_id := l_ins_bc_packet_id_tab(ins_rec);
3411 /*===============================================================+
3412 | l_i_raw_po_rec retains the position of the raw PO record. |
3413 | This is used to reject the raw record - if the burden record |
3417
3414 | is not getting inserted for some rejection. |
3415 +===============================================================*/
3416 l_i_raw_po_rec := ins_rec;
3418 /*==============================================+
3419 | Calculate the effective relieval amounts. |
3420 +==============================================*/
3421 l_cur_new_raw_amt_relieved := l_summ_raw_amt_relieved_tab(i_summary) +
3422 l_acct_raw_cost_tab(i);
3423 pa_debug.g_err_stage := 'commited raw [' ||
3424 to_char(l_summ_tot_raw_amt_tab(i_summary)) || '] old rlvd [' ||
3425 to_char(l_summ_raw_amt_relieved_tab(i_summary)) || '] arc [' ||
3426 to_char(l_acct_raw_cost_tab(i)) || '] new rlvd [' ||
3427 to_char(l_cur_new_raw_amt_relieved) || ']';
3428 pa_debug.write_file(l_stage || pa_debug.g_err_stage);
3429 /*====================================+
3430 | Do not relieve more than commited. |
3431 +====================================*/
3432 /*============================================================================+
3433 | Bug 3826077 : Adding the ABS() check. Please refer the bug for more info. |
3434 +============================================================================*/
3435 IF ( ABS(l_cur_new_raw_amt_relieved) > ABS(l_summ_tot_raw_amt_tab(i_summary)) )
3436 THEN
3437 l_ins_entered_dr_tab(ins_rec) := (l_summ_tot_raw_amt_tab(i_summary) -
3438 l_summ_raw_amt_relieved_tab(i_summary)) * -1;
3439 l_ins_accounted_dr_tab(ins_rec) := l_ins_entered_dr_tab(ins_rec);
3440 l_summ_raw_amt_relieved_tab(i_summary) := l_summ_tot_raw_amt_tab(i_summary);
3441 pa_debug.g_err_stage := 'amt being relieved [' ||
3442 to_char(l_ins_entered_dr_tab(ins_rec)) || ']';
3443 ELSE
3444 l_ins_entered_dr_tab(ins_rec) := l_acct_raw_cost_tab(i) * -1;
3445 l_ins_accounted_dr_tab(ins_rec) := l_ins_entered_dr_tab(ins_rec);
3446 l_summ_raw_amt_relieved_tab(i_summary) := l_cur_new_raw_amt_relieved;
3447 pa_debug.g_err_stage := 'amt being relieved [' ||
3448 to_char(l_ins_entered_dr_tab(ins_rec)) || ']';
3449 END IF;
3450
3451 /*===============================================================+
3452 | Step 2a : Populate Burden PO record. (Same line burdening) |
3453 | |
3454 | Note :- The burden records for PO type should be will always |
3455 | be inserted by the distribution process. |
3456 +===============================================================*/
3457 IF ( l_burden_cost_flag_tab(i) = 'Y' AND
3458 l_burden_amt_disp_method_tab(i) = 'S' AND
3459 l_line_type_tab(i) <> 'I'
3460 )
3461 THEN
3462 ins_rec := ins_rec + 1;
3463 pa_debug.g_err_stage := 'Inserting Burd PO rec. Same line burd. ins_rec is [' || to_char(ins_rec) || ']';
3464 pa_debug.write_file(l_stage || pa_debug.g_err_stage);
3465 copy_common_attributes( i ,ins_rec );
3466 /*===========================================================+
3467 | Overriding what copy_common_attributes sets the value to. |
3468 | on Prithi's advice. |
3469 +===========================================================*/
3470 l_ins_document_dist_id_tab(ins_rec) := -9999;
3471
3472 BEGIN
3473 SELECT pa_bc_packets_s.NEXTVAL
3474 INTO l_ins_bc_packet_id_tab(ins_rec)
3475 FROM DUAL;
3476 EXCEPTION
3477 WHEN OTHERS THEN RAISE;
3478 END;
3479
3480 l_ins_document_type_tab(ins_rec) := 'PO';
3481 l_ins_expenditure_type_tab(ins_rec) := l_expenditure_type_tab(i_summary);
3482 l_ins_parent_bc_packet_id_tab(ins_rec) := l_po_raw_bc_packet_id;
3483
3484 /*===========================================================+
3485 | Calculate the amount to be relieved and the amount used |
3486 | to updated on the column Comm_Raw_Amt_Relieved. |
3487 +===========================================================*/
3488 l_cur_new_bd_amt_relieved := l_summ_bd_amt_relieved_tab(i_summary) +
3489 (l_acct_raw_cost_tab(i) * l_summ_compiled_multiplier_tab(i_summary));
3490 pa_debug.g_err_stage := 'commited bd [' ||
3491 to_char(l_summ_tot_bd_amt_tab(i_summary)) || '] old rlvd [' ||
3492 to_char(l_summ_bd_amt_relieved_tab(i_summary)) || '] bc [' ||
3493 to_char(l_acct_raw_cost_tab(i) * l_summ_compiled_multiplier_tab(i_summary))
3494 || '] new rlvd [' || to_char(l_cur_new_bd_amt_relieved) || ']';
3495 pa_debug.write_file(l_stage || pa_debug.g_err_stage);
3496 /*====================================+
3497 | Do not relieve more than commited. |
3498 +====================================*/
3502 IF ( ABS(l_cur_new_bd_amt_relieved) > ABS(l_summ_tot_bd_amt_tab(i_summary)) )
3499 /*============================================================================+
3500 | Bug 3826077 : Adding the ABS() check. Please refer the bug for more info. |
3501 +============================================================================*/
3503 THEN
3504 l_ins_entered_dr_tab(ins_rec) := (l_summ_tot_bd_amt_tab(i_summary) -
3505 l_summ_bd_amt_relieved_tab(i_summary)) * -1;
3506 l_ins_accounted_dr_tab(ins_rec) := l_ins_entered_dr_tab(ins_rec);
3507 l_summ_bd_amt_relieved_tab(i_summary) := l_summ_tot_bd_amt_tab(i_summary);
3508 pa_debug.g_err_stage := 'amt rlvd [' ||
3509 to_char(l_ins_entered_dr_tab(ins_rec)) || ']';
3510 pa_debug.write_file(l_stage || pa_debug.g_err_stage);
3511 ELSE
3512 l_ins_entered_dr_tab(ins_rec) := (l_acct_raw_cost_tab(i) *
3513 l_summ_compiled_multiplier_tab(i_summary)) * -1;
3514 l_ins_accounted_dr_tab(ins_rec) := l_ins_entered_dr_tab(ins_rec);
3515 l_summ_bd_amt_relieved_tab(i_summary) := l_cur_new_bd_amt_relieved;
3516 pa_debug.g_err_stage := 'amt rlvd [' ||
3517 to_char(l_ins_entered_dr_tab(ins_rec)) || ']';
3518 pa_debug.write_file(l_stage || pa_debug.g_err_stage);
3519 END IF;
3520 END IF; -- Same line burdening
3521 END IF; -- line num 1
3522
3523 /*=====================================================================+
3524 | If Separate line burdening, get the burden cost codes. |
3525 | This segment of code has to be executed - irrespective relieving PO |
3526 | lines getting inserted. This is because, the burden expenditure |
3527 | types and multipliers are needed to insert funds-check lines. |
3528 +=====================================================================*/
3529 IF ( l_burden_cost_flag_tab(i) = 'Y' AND
3530 l_burden_amt_disp_method_tab(i) = 'D' AND
3531 l_line_type_tab(i) <> 'I'
3532 )
3533 THEN
3534 pa_debug.g_err_stage := 'Separate line burdening - hitting burdening datamodel';
3535 pa_debug.write_file(l_stage || pa_debug.g_err_stage);
3536 SELECT icc.expenditure_type
3537 ,cm.compiled_multiplier
3538 BULK COLLECT INTO
3539 l_txn_burden_exp_type_tab
3540 ,l_txn_burden_comp_mult_tab
3541 FROM PA_IND_COST_CODES ICC
3542 ,PA_COMPILED_MULTIPLIERS CM
3543 ,PA_IND_COMPILED_SETS ICS
3544 ,PA_COST_BASE_EXP_TYPES CBET
3545 ,PA_COST_BASES CB
3546 ,PA_IND_RATE_SCH_REVISIONS IRSR
3547 ,PA_IND_RATE_SCHEDULES_ALL_BG IRS
3548 WHERE ics.ind_rate_sch_revision_id = irsr.ind_rate_sch_revision_id
3549 AND irs.ind_rate_sch_id = irsr.ind_rate_sch_id
3550 AND irsr.cost_plus_structure = cbet.cost_plus_structure
3551 AND cbet.cost_base = cm.cost_base
3552 AND cb.cost_base = cbet.cost_base
3553 AND cb.cost_base_type = cbet.cost_base_type
3554 AND cbet.cost_base_type = 'INDIRECT COST'
3555 AND cbet.expenditure_type = l_expenditure_type_tab(i)
3556 AND ics.organization_id = l_exp_organization_id_tab(i)
3557 AND ics.cost_base = cbet.cost_base
3558 AND ics.ind_compiled_set_id = l_ind_compiled_set_id_tab(i)
3559 AND icc.ind_cost_code = cm.ind_cost_code
3560 AND cm.ind_compiled_set_id = l_ind_compiled_set_id_tab(i)
3561 AND l_burden_sum_rej_code_tab(i) IS NULL
3562 AND l_burden_sum_source_run_id_tab(i) = -9999
3563 AND l_burden_amt_disp_method_tab(i) = 'D'
3564 AND l_burden_cost_flag_tab(i) = 'Y'
3565 ;
3566 pa_debug.g_err_stage := 'This txn has [' || TO_CHAR(SQL%ROWCOUNT) || '] Burden Cost Code(s).';
3567 pa_debug.write_file(l_stage || pa_debug.g_err_stage);
3568 END IF; -- Separate line burdening
3569
3570 /*=========================================================+
3571 | Relieve the PO commitment if first time distribution. |
3572 +=========================================================*/
3573 IF (l_line_num_tab(i) = 1)
3574 THEN
3575 IF ( l_burden_cost_flag_tab(i) = 'Y' AND
3576 l_burden_amt_disp_method_tab(i) = 'D'
3577 )
3578 THEN
3579 /*==============================================================+
3580 | Make sure there are matching PA burden cost code records for |
3581 | all commitment summary lines. |
3582 +==============================================================*/
3583 FOR summ_line IN l_summ_project_id_tab.FIRST .. l_summ_project_id_tab.LAST
3584 LOOP
3585
3586 IF l_txn_burden_exp_type_tab.COUNT <> 0 THEN /* Bug 3974799 */
3587
3588 FOR sep_burden IN l_txn_burden_exp_type_tab.FIRST .. l_txn_burden_exp_type_tab.LAST
3592 END LOOP;
3589 LOOP
3590 pa_debug.g_err_stage := 'et [' || l_txn_burden_exp_type_tab(sep_burden) || ']';
3591 pa_debug.write_file(pa_debug.g_err_stage);
3593
3594 END IF; /* Bug 3974799 */
3595
3596 /*========================================+
3597 | Bug 3801932 : Check only burden lines. |
3598 +========================================*/
3599 IF ( l_summ_parent_bc_packet_id_tab(summ_line) IS NOT NULL )
3600 THEN
3601 l_found := FALSE;
3602
3603 IF l_txn_burden_exp_type_tab.COUNT <> 0 THEN /* Bug 3974799 */
3604 FOR sep_burden IN l_txn_burden_exp_type_tab.FIRST .. l_txn_burden_exp_type_tab.LAST
3605 LOOP
3606 pa_debug.g_err_stage := 'comparing summ et ['
3607 || l_summ_expenditure_type_tab(summ_line) || '] with txn et ['
3608 || l_txn_burden_exp_type_tab(sep_burden) || ']';
3609 pa_debug.write_file(l_stage || pa_debug.g_err_stage);
3610 IF ( l_summ_project_id_tab(summ_line) = l_project_id_tab(i) AND
3611 l_summ_task_id_tab(summ_line) = l_task_id_tab(i) AND
3612 l_summ_document_header_id_tab(summ_line) = l_document_header_id_tab(i) AND
3613 l_summ_document_line_id_tab(summ_line) = l_document_line_id_tab(i) AND
3614 l_summ_expenditure_type_tab(summ_line) = l_txn_burden_exp_type_tab(sep_burden) AND
3615 l_summ_tot_bd_amt_tab(summ_line) <> 0
3616 )
3617 THEN
3618 pa_debug.g_err_stage := 'match';
3619 pa_debug.write_file(l_stage || pa_debug.g_err_stage);
3620 l_found := TRUE;
3621 EXIT; -- PA Burden cost codes loop
3622 ELSE
3623 pa_debug.g_err_stage := 'no match';
3624 pa_debug.write_file(l_stage || pa_debug.g_err_stage);
3625 l_found := FALSE;
3626 END IF;
3627 END LOOP; -- PA burden cost code records
3628 END IF; -- l_summ_parent_bc_packet_id_tab(summ_line) IS NOT NULL
3629 END IF; /* Bug 3974799 */
3630 END LOOP; -- commitment summary lines
3631 /*==========================================================================+
3632 | At this point, if l_found is FALSE, then, one of the summary records |
3633 | does not have a matching PA record. Reject the transaction. |
3634 +==========================================================================*/
3635
3636 IF ( NOT l_found )
3637 THEN
3638 l_rejn_code_tab(i) := 'PA_TXN_COMM_BCC_NO_MATCH';
3639 /*stop the raw relieving line getting inserted*/
3640 l_ins_rejn_code_tab(l_i_raw_po_rec) := 'PA_TXN_COMM_BCC_NO_MATCH';
3641 pa_debug.g_err_stage := 'Matching BCC record missing. Rejecting';
3642 pa_debug.write_file(l_stage || pa_debug.g_err_stage);
3643 RAISE USER_EXCEPTION;
3644 END IF;
3645 /*=============================================+
3646 | Inserting Funds PO relieving Burden Record. |
3647 +=============================================*/
3648 FOR summ_line IN l_summ_project_id_tab.FIRST .. l_summ_project_id_tab.LAST
3649 LOOP
3650 IF ( l_summ_project_id_tab(summ_line) = l_project_id_tab(i) AND
3651 l_summ_task_id_tab(summ_line) = l_task_id_tab(i) AND
3652 l_summ_document_header_id_tab(summ_line) = l_document_header_id_tab(i) AND
3653 l_summ_document_line_id_tab(summ_line) = l_document_line_id_tab(i) AND
3654 l_summ_parent_bc_packet_id_tab(summ_line) IS NOT NULL AND
3655 l_summ_tot_bd_amt_tab(summ_line) <> 0
3656 )
3657 THEN
3658 ins_rec := ins_rec + 1;
3659 pa_debug.g_err_stage := 'Inserting sep line PO relieving. ins_rec is [' || to_char(ins_rec) || ']';
3660 pa_debug.write_file(l_stage || pa_debug.g_err_stage);
3661 copy_common_attributes( i ,ins_rec );
3662 /*===========================================================+
3663 | Overriding what copy_common_attributes sets the value to. |
3664 | on Prithi's advice. |
3665 +===========================================================*/
3666 l_ins_document_dist_id_tab(ins_rec) := -9999;
3667 BEGIN
3668 SELECT pa_bc_packets_s.NEXTVAL
3669 INTO l_ins_bc_packet_id_tab(ins_rec)
3670 FROM DUAL;
3671 EXCEPTION
3672 WHEN OTHERS THEN RAISE;
3673 END;
3674 l_ins_document_type_tab(ins_rec) := 'PO';
3675 l_ins_expenditure_type_tab(ins_rec) := l_summ_expenditure_type_tab(summ_line);
3676 l_ins_parent_bc_packet_id_tab(ins_rec) := l_po_raw_bc_packet_id;
3677
3681
3678 l_cur_new_bd_amt_relieved := l_summ_bd_amt_relieved_tab(summ_line) +
3679 ( l_acct_raw_cost_tab(i) *
3680 l_summ_compiled_multiplier_tab(summ_line) );
3682 /*====================================+
3683 | Do not relieve more than commited. |
3684 +====================================*/
3685 /*============================================================================+
3686 | Bug 3826077 : Adding the ABS() check. Please refer the bug for more info. |
3687 +============================================================================*/
3688 IF ( ABS(l_cur_new_bd_amt_relieved) > ABS(l_summ_tot_bd_amt_tab(summ_line)) )
3689 THEN
3690 l_ins_entered_dr_tab(ins_rec) := ( l_summ_tot_bd_amt_tab(summ_line) -
3691 l_temp_Bd_Amt_Relieved_tab(summ_line) ) * -1;
3692 l_ins_accounted_dr_tab(ins_rec) := l_ins_entered_dr_tab(ins_rec);
3693 l_summ_bd_amt_relieved_tab(i_summary) := l_summ_tot_bd_amt_tab(summ_line);
3694 ELSE
3695 l_ins_entered_dr_tab(ins_rec) := ( l_acct_raw_cost_tab(i) *
3696 l_summ_compiled_multiplier_tab(summ_line) ) * -1;
3697 l_ins_accounted_dr_tab(ins_rec) := l_ins_entered_dr_tab(ins_rec);
3698 l_summ_bd_amt_relieved_tab(i_summary) := l_cur_new_bd_amt_relieved;
3699 END IF; -- Do not relieve more than commited.
3700 END IF; -- check if the summary belongs to this txn.
3701 END LOOP; -- summary lines
3702 END IF; -- separate line burdening
3703 END IF; -- line_num = 1
3704
3705 /*==============================+
3706 | Populate Raw EXP record. |
3707 +==============================*/
3708 IF( l_line_type_tab(i) <> 'I')
3709 THEN
3710 ins_rec := ins_rec + 1;
3711 pa_debug.g_err_stage := 'Inserting raw EXP record ins_rec is [' || to_char(ins_rec) || ']';
3712 pa_debug.write_file(l_stage || pa_debug.g_err_stage);
3713 copy_common_attributes( i ,ins_rec );
3714 BEGIN
3715 SELECT pa_bc_packets_s.NEXTVAL
3716 INTO l_ins_bc_packet_id_tab(ins_rec)
3717 FROM DUAL;
3718 EXCEPTION
3719 WHEN OTHERS THEN RAISE;
3720 END;
3721 l_ins_document_type_tab(ins_rec) := 'EXP';
3722 IF ( l_line_num_reversed_tab(i) IS NOT NULL )
3723 THEN
3724 l_ins_parent_bc_packet_id_tab(ins_rec) := NULL;
3725 ELSE
3726 l_ins_parent_bc_packet_id_tab(ins_rec) := -1;
3727 END IF;
3728 l_ins_document_header_id_tab(ins_rec) := l_expenditure_item_id_tab(i);
3729
3730 l_exp_raw_bc_packet_id := l_ins_bc_packet_id_tab(ins_rec);
3731
3732 l_ins_entered_dr_tab(ins_rec) := l_acct_raw_cost_tab(i);
3733 l_ins_accounted_dr_tab(ins_rec) := l_ins_entered_dr_tab(ins_rec);
3734 END IF; -- line type I check
3735
3736 /*==========================================+
3737 | Populate Same line burden record. (EXP) |
3738 +==========================================*/
3739 IF ( l_burden_cost_flag_tab(i) = 'Y' AND
3740 l_burden_amt_disp_method_tab(i) = 'S' AND
3741 (l_line_num_reversed_tab(i) IS NOT NULL OR l_line_type_tab(i) = 'I')
3742 )
3743 THEN
3744 ins_rec := ins_rec + 1;
3745 pa_debug.g_err_stage := 'inserting burden same line exp record ins_rec is [' || to_char(ins_rec) || ']';
3746 pa_debug.write_file(l_stage || pa_debug.g_err_stage);
3747 copy_common_attributes( i ,ins_rec );
3748 BEGIN
3749 SELECT pa_bc_packets_s.NEXTVAL
3750 INTO l_ins_bc_packet_id_tab(ins_rec)
3751 FROM DUAL;
3752 EXCEPTION
3753 WHEN OTHERS THEN RAISE;
3754 END;
3755
3756 l_ins_document_type_tab(ins_rec) := 'EXP';
3757 IF ( l_line_type_tab(i) = 'I' )
3758 THEN
3759 l_ins_parent_bc_packet_id_tab(ins_rec) := NULL;
3760 l_ins_entered_dr_tab(ins_rec) := l_acct_burdened_cost_tab(i);
3761 ELSE
3762 l_ins_parent_bc_packet_id_tab(ins_rec) := l_exp_raw_bc_packet_id;
3763 l_ins_entered_dr_tab(ins_rec) := (l_acct_burdened_cost_tab(i) - l_acct_raw_cost_tab(i));
3764 END IF;
3765 l_ins_document_header_id_tab(ins_rec) := l_expenditure_item_id_tab(i);
3766
3767 l_ins_accounted_dr_tab(ins_rec) := l_ins_entered_dr_tab(ins_rec);
3768 END IF; -- Same line Burdening
3769
3770 /*==============================================+
3771 | Populate Separate line burden record. (EXP) |
3772 +==============================================*/
3773 IF ( l_burden_cost_flag_tab(i) = 'Y' AND
3774 l_burden_amt_disp_method_tab(i) = 'D' AND
3775 l_line_num_reversed_tab(i) IS NOT NULL
3776 )
3777 THEN
3778 IF l_txn_burden_exp_type_tab.COUNT <> 0 THEN /* Bug 3974799 */
3779
3780 FOR sep_burden IN l_txn_burden_exp_type_tab.FIRST .. l_txn_burden_exp_type_tab.LAST
3781 LOOP
3785 BEGIN
3782 ins_rec := ins_rec + 1;
3783 pa_debug.write_file('inserting burden sep line exp record ins_rec is [' || to_char(ins_rec) || ']');
3784 copy_common_attributes( i ,ins_rec );
3786 SELECT pa_bc_packets_s.NEXTVAL
3787 INTO l_ins_bc_packet_id_tab(ins_rec)
3788 FROM DUAL;
3789 EXCEPTION
3790 WHEN OTHERS THEN RAISE;
3791 END;
3792 l_ins_expenditure_type_tab(ins_rec) := l_txn_burden_exp_type_tab(sep_burden);
3793 l_ins_document_type_tab(ins_rec) := 'EXP';
3794 l_ins_parent_bc_packet_id_tab(ins_rec) := l_exp_raw_bc_packet_id;
3795 l_ins_document_header_id_tab(ins_rec) := l_expenditure_item_id_tab(i);
3796
3797 l_ins_entered_dr_tab(ins_rec) := l_acct_raw_cost_tab(i) *
3798 l_txn_burden_comp_mult_tab(sep_burden);
3799 l_ins_accounted_dr_tab(ins_rec) := l_ins_entered_dr_tab(ins_rec);
3800 END LOOP; -- sep_burden
3801 END IF; /* Bug 3974799 */
3802 END IF; -- separate line burdening
3803 /*==================================================+
3804 | Delete all plsql tables that are used per loop. |
3805 +==================================================*/
3806 l_temp_Tot_Raw_Amt_tab.DELETE;
3807 l_temp_Tot_Bd_Amt_tab.DELETE;
3808 l_temp_Raw_Amt_Relieved_tab.DELETE;
3809 l_temp_Bd_Amt_Relieved_tab.DELETE;
3810 l_temp_compiled_multiplier_tab.DELETE;
3811 l_temp_parent_bc_packet_id_tab.DELETE;
3812 l_temp_expenditure_type_tab.DELETE;
3813 l_temp_comm_source_tab.DELETE;
3814 l_txn_burden_exp_type_tab.DELETE;
3815 l_txn_burden_comp_mult_tab.DELETE;
3816
3817 EXCEPTION
3818 WHEN USER_EXCEPTION
3819 THEN
3820 pa_debug.g_err_stage := 'From User Exception handler' ;
3821 pa_debug.log_message(p_message => PA_DEBUG.g_err_stage);
3822 NULL;
3823 WHEN OTHERS
3824 THEN
3825 RAISE;
3826 END; -- anonymous block
3827 END LOOP; -- for all records
3828
3829 pa_debug.g_err_stage := 'Before debug for loop ' ;
3830 pa_debug.log_message(p_message => PA_DEBUG.g_err_stage);
3831
3832 IF ( l_ins_packet_id_tab.COUNT > 0 )
3833 THEN
3834
3835 FOR ins_rec IN l_ins_packet_id_tab.FIRST .. l_ins_packet_id_tab.LAST
3836 LOOP
3837 pa_debug.g_err_stage := 'Before inserting record [' || to_char(ins_rec)
3838 || '] l_ins_packet_id_tab [' || to_char(l_ins_packet_id_tab(ins_rec))
3839 || '] l_ins_project_id_tab [' || to_char(l_ins_project_id_tab(ins_rec))
3840 || '] l_ins_task_id_tab [' || to_char(l_ins_task_id_tab(ins_rec))
3841 || '] l_ins_budget_version_id_tab [' || to_char(l_ins_budget_version_id_tab(ins_rec))
3842 || '] l_ins_expenditure_type_tab [' || l_ins_expenditure_type_tab(ins_rec)
3843 || '] l_ins_ei_date_tab [' || to_char(l_ins_ei_date_tab(ins_rec))
3844 || '] l_ins_period_name_tab [' || l_ins_period_name_tab(ins_rec)
3845 || '] l_ins_pa_date_tab [' || to_char(l_ins_pa_date_tab(ins_rec))
3846 || '] l_ins_gl_date_tab [' || to_char(l_ins_gl_date_tab(ins_rec))
3847 || '] l_ins_set_of_books_id_tab [' || to_char(l_ins_set_of_books_id_tab(ins_rec))
3848 || '] l_ins_je_category_name_tab [' || l_ins_je_category_name_tab(ins_rec)
3849 || '] l_ins_je_source_name_tab [' || l_ins_je_source_name_tab(ins_rec)
3850 || '] l_ins_status_code_tab [' || l_ins_status_code_tab(ins_rec)
3851 || '] l_ins_document_type_tab [' || l_ins_document_type_tab(ins_rec)
3852 || ']';
3853 pa_debug.write_file(pa_debug.g_err_stage);
3854 pa_debug.g_err_stage := 'Before inserting record [' || to_char(ins_rec)
3855 || '] l_ins_funds_process_mode_tab [' || l_ins_funds_process_mode_tab(ins_rec)
3856 || '] l_ins_burden_cost_flag_tab [' || l_ins_burden_cost_flag_tab(ins_rec)
3857 || '] l_ins_expenditure_orgn_id_tab [' || to_char(l_ins_expenditure_orgn_id_tab(ins_rec))
3858 || '] l_ins_document_header_id_tab [' || to_char(l_ins_document_header_id_tab(ins_rec))
3859 || '] l_ins_document_line_id_tab [' || to_char(l_ins_document_line_id_tab(ins_rec))
3860 || '] l_ins_document_dist_id_tab [' || to_char(l_ins_document_dist_id_tab(ins_rec))
3861 || '] l_ins_txn_ccid_tab [' || to_char(l_ins_txn_ccid_tab(ins_rec))
3862 || '] l_ins_burden_cost_flag_tab [' || l_ins_burden_cost_flag_tab(ins_rec)
3863 || '] l_ins_balance_posted_flag_tab [' || l_ins_balance_posted_flag_tab(ins_rec)
3864 || ']';
3865 pa_debug.write_file(pa_debug.g_err_stage);
3866
3867 pa_debug.g_err_stage := 'l_ins_accounted_dr_tab [' || to_char(l_ins_accounted_dr_tab(ins_rec)) || ']';
3868 pa_debug.write_file(pa_debug.g_err_stage);
3869 pa_debug.g_err_stage := 'l_ins_entered_dr_tab [' || to_char(l_ins_entered_dr_tab(ins_rec)) || ']';
3870 pa_debug.write_file(pa_debug.g_err_stage);
3871 pa_debug.g_err_stage := 'l_ins_bc_packet_id_tab [' || to_char(l_ins_bc_packet_id_tab(ins_rec)) || ']';
3872 pa_debug.write_file(pa_debug.g_err_stage);
3873 pa_debug.g_err_stage := 'l_ins_parent_bc_packet_id_tab [' || to_char(l_ins_parent_bc_packet_id_tab(ins_rec)) || ']';
3874 pa_debug.write_file(pa_debug.g_err_stage);
3875 pa_debug.g_err_stage := 'l_ins_org_id_tab [' || to_char(l_ins_org_id_tab(ins_rec)) || ']';
3876 pa_debug.write_file(pa_debug.g_err_stage);
3877 pa_debug.g_err_stage := 'l_ins_exp_item_id_tab [' || to_char(l_ins_exp_item_id_tab(ins_rec)) || ']';
3878 pa_debug.write_file(pa_debug.g_err_stage);
3879 END LOOP;
3880 END IF;
3881 /*==================================+
3882 | Insert into pa_bc_packets. |
3883 +==================================*/
3884 /* ?????????? This IF is added because the update gives numeric/value error
3885 * when there are no records to insert. Ideally this should not happen. This
3886 * has to be figured out. ??????????????????
3887 */
3888 IF ( l_ins_packet_id_tab.COUNT > 0)
3889 THEN
3890 pa_debug.g_err_stage := 'Before inserting into pa_bc_pacets';
3891 pa_debug.write_file(l_stage || pa_debug.g_err_stage);
3892
3893 pa_debug.g_err_stage := 'ORACLE error while Inserting PA_BC_PACKETS.' ;
3894 FORALL ins_rec IN l_ins_packet_id_tab.FIRST .. l_ins_packet_id_tab.LAST
3895 INSERT
3896 INTO pa_bc_packets( packet_id
3897 ,project_id
3898 ,task_id
3899 ,budget_version_id
3900 ,expenditure_type
3901 ,expenditure_item_date
3902 ,period_name
3903 ,pa_date
3904 ,gl_date
3905 ,set_of_books_id
3906 ,je_category_name
3907 ,je_source_name
3908 ,status_code
3909 ,document_type
3910 ,funds_process_mode
3911 ,burden_cost_flag
3912 ,expenditure_organization_id
3913 ,document_header_id
3914 ,document_line_id
3915 ,document_distribution_id
3916 ,txn_ccid
3917 ,accounted_dr
3918 ,entered_dr
3919 ,bc_packet_id
3920 ,parent_bc_packet_id
3921 ,org_id
3922 ,balance_posted_flag
3923 ,exp_item_id
3924 ,program_id
3925 ,program_application_id
3926 ,program_update_date
3927 ,last_update_date
3928 ,last_updated_by
3929 ,created_by
3930 ,creation_date
3931 ,last_update_login
3932 ,request_id
3933 ,reference1
3934 ,reference2
3935 ,reference3
3936 ,actual_flag -- Bug 5494476
3937 )
3938 SELECT l_ins_packet_id_tab(ins_rec)
3939 ,l_ins_project_id_tab(ins_rec)
3940 ,l_ins_task_id_tab(ins_rec)
3941 ,l_ins_budget_version_id_tab(ins_rec)
3942 ,l_ins_expenditure_type_tab(ins_rec)
3943 ,l_ins_ei_date_tab(ins_rec)
3944 ,l_ins_period_name_tab(ins_rec)
3945 ,l_ins_pa_date_tab(ins_rec)
3949 ,l_ins_je_source_name_tab(ins_rec)
3946 ,l_ins_gl_date_tab(ins_rec)
3947 ,l_ins_set_of_books_id_tab(ins_rec)
3948 ,l_ins_je_category_name_tab(ins_rec)
3950 ,l_ins_status_code_tab(ins_rec)
3951 ,l_ins_document_type_tab(ins_rec)
3952 ,l_ins_funds_process_mode_tab(ins_rec)
3953 ,l_ins_burden_cost_flag_tab(ins_rec)
3954 ,l_ins_expenditure_orgn_id_tab(ins_rec)
3955 ,l_ins_document_header_id_tab(ins_rec)
3956 ,l_ins_document_line_id_tab(ins_rec)
3957 ,l_ins_document_dist_id_tab(ins_rec)
3958 ,l_ins_txn_ccid_tab(ins_rec)
3959 ,l_ins_accounted_dr_tab(ins_rec)
3960 ,l_ins_entered_dr_tab(ins_rec)
3961 ,l_ins_bc_packet_id_tab(ins_rec)
3962 ,l_ins_parent_bc_packet_id_tab(ins_rec)
3963 ,l_ins_org_id_tab(ins_rec)
3964 ,l_ins_balance_posted_flag_tab(ins_rec)
3965 ,l_ins_exp_item_id_tab(ins_rec)
3966 ,g_program_id -- program_id
3967 ,g_program_application_id -- program_application_id
3968 ,SYSDATE -- program_update_date
3969 ,SYSDATE -- last_update_date
3970 ,-99 -- last_updated_by
3971 ,-99 -- created_by
3972 ,SYSDATE -- creation_date
3973 ,g_last_update_login -- last_update_login
3974 ,g_request_id
3975 ,'EXP'
3976 ,l_ins_exp_item_id_tab(ins_rec)
3977 ,l_ins_document_dist_id_tab(ins_rec)
3978 -- Bug 5494476 : Actual flag should be 'A' for expenditures and 'E' for PO commitment relieving records.
3979 ,decode (l_ins_document_type_tab(ins_rec),'EXP','A','E')
3980 FROM DUAL
3981 WHERE l_ins_rejn_code_tab(ins_rec) IS NULL
3982 ;
3983 END IF; --l_ins_packet_id_tab.COUNT > 0
3984
3985 l_records_affected := SQL%ROWCOUNT;
3986
3987 pa_debug.g_err_stage := 'Inserted [' || TO_CHAR(l_records_affected) ||
3988 '] Records into pa_bc_packets.';
3989 pa_debug.write_file(l_stage || pa_debug.g_err_stage);
3990
3991 /*==========================+
3992 | Deleting plsql tables. |
3993 +==========================*/
3994 pa_debug.g_err_stage := 'Deleting ins plsql tables' ;
3995 pa_debug.write_file(l_stage || pa_debug.g_err_stage);
3996
3997 l_ins_packet_id_tab.DELETE;
3998 l_ins_project_id_tab.DELETE;
3999 l_ins_task_id_tab.DELETE;
4000 l_ins_budget_version_id_tab.DELETE;
4001 l_ins_expenditure_type_tab.DELETE;
4002 l_ins_ei_date_tab.DELETE;
4003 l_ins_period_name_tab.DELETE;
4004 l_ins_pa_date_tab.DELETE;
4005 l_ins_gl_date_tab.DELETE;
4006 l_ins_set_of_books_id_tab.DELETE;
4007 l_ins_je_category_name_tab.DELETE;
4008 l_ins_je_source_name_tab.DELETE;
4009 l_ins_status_code_tab.DELETE;
4010 l_ins_funds_process_mode_tab.DELETE;
4011 l_ins_burden_cost_flag_tab.DELETE;
4012 l_ins_expenditure_orgn_id_tab.DELETE;
4013 l_ins_document_dist_id_tab.DELETE;
4014 l_ins_txn_ccid_tab.DELETE;
4015 l_ins_bc_packet_id_tab.DELETE;
4016 l_ins_org_id_tab.DELETE;
4017 l_ins_balance_posted_flag_tab.DELETE;
4018 l_ins_document_type_tab.DELETE;
4019 l_ins_parent_bc_packet_id_tab.DELETE;
4020 l_ins_document_header_id_tab.DELETE;
4021 l_ins_document_line_id_tab.DELETE;
4022 l_ins_entered_dr_tab.DELETE;
4023 l_ins_accounted_dr_tab.DELETE;
4024
4025 /*=========================+
4026 | Deleting Summary Cache. |
4027 +=========================*/
4028 pa_debug.g_err_stage := 'Deleting summary cache plsql tables' ;
4029 pa_debug.write_file(l_stage || pa_debug.g_err_stage);
4030
4031 l_summ_project_id_tab.DELETE;
4032 l_summ_task_id_tab.DELETE;
4033 l_summ_document_header_id_tab.DELETE;
4034 l_summ_document_line_id_tab.DELETE;
4035 l_summ_tot_raw_amt_tab.DELETE;
4036 l_summ_tot_bd_amt_tab.DELETE;
4037 l_summ_raw_amt_relieved_tab.DELETE;
4038 l_summ_bd_amt_relieved_tab.DELETE;
4039 l_summ_compiled_multiplier_tab.DELETE;
4040 l_summ_parent_bc_packet_id_tab.DELETE;
4041 l_summ_expenditure_type_tab.DELETE;
4042 l_summ_source_tab.DELETE;
4043
4044 l_stage := l_proc_name || ': ' || to_char(500) || ': ';
4045 pa_debug.g_err_stage := 'Committing work!!' ;
4046 pa_debug.write_file(l_stage || pa_debug.g_err_stage);
4047
4048 COMMIT;
4049
4050 pa_debug.g_err_stage := 'Leaving populate_pa_bc_packets_cwk.' ;
4051 pa_debug.write_file(l_stage || pa_debug.g_err_stage);
4052
4053 pa_debug.reset_err_stack;
4054 EXCEPTION
4055 WHEN OTHERS
4056 THEN
4057 pa_debug.write_file(l_stage || pa_debug.g_err_stage);
4058 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4059 x_error_code := TO_CHAR(SQLCODE) || SQLERRM ;
4060 x_error_stage := l_stage ;
4061 RAISE;
4062 END; -- populate_pa_bc_packets_cwk
4063 --------------------------------------------------------------------------------------
4064
4065
4066 END pa_bc_costing;