1 PACKAGE BODY PA_MC_BORRLENT AS
2 /* $Header: PAMRCBLB.pls 120.2 2005/10/18 01:27:11 avajain noship $ */
3
4 ------------------------------------------------------------
5 -- PRIVATE PACKAGE SPECIFICATIONS
6 ------------------------------------------------------------
7
8 PROCEDURE set_curr_function(p_function IN VARCHAR2);
9 PROCEDURE reset_curr_function;
10
11 PROCEDURE get_mrc_values (
12 p_primary_sob_id IN gl_Sets_of_books.set_of_books_id%TYPE
13 ,p_prvdr_org_id IN PA_PLSQL_DATATYPES.IDTabTyp
14 ,p_rsob_id IN PA_PLSQL_DATATYPES.IDTabTyp
15 ,p_rcurrency_code IN PA_PLSQL_DATATYPES.Char15TabTyp
16 ,p_cc_dist_line_id IN PA_PLSQL_DATATYPES.IDTabTyp
17 ,p_upd_type IN PA_PLSQL_DATATYPES.Char1TabTyp
18 DEFAULT PA_PLSQL_DATATYPES.EmptyChar1Tab
19 ,p_dist_line_id_reversed IN PA_PLSQL_DATATYPES.IDTabTyp
20 DEFAULT PA_PLSQL_DATATYPES.EmptyIDTab
21 ,p_expenditure_item_id IN PA_PLSQL_DATATYPES.IDTabTyp
22 DEFAULT PA_PLSQL_DATATYPES.EmptyIDTab
23 ,p_line_num IN PA_PLSQL_DATATYPES.IDTabTyp
24 DEFAULT PA_PLSQL_DATATYPES.EmptyIDTab
25 ,p_line_type IN PA_PLSQL_DATATYPES.Char2TabTyp
26 DEFAULT PA_PLSQL_DATATYPES.EmptyChar2Tab
27 ,p_denom_currency_code IN PA_PLSQL_DATATYPES.Char15TabTyp
28 ,p_acct_tp_rate_type IN PA_PLSQL_DATATYPES.Char30TabTyp
29 ,p_expenditure_item_date IN PA_PLSQL_DATATYPES.DateTabTyp
30 ,p_acct_tp_exchange_rate IN PA_PLSQL_DATATYPES.NumTabTyp
31 ,p_denom_amount IN PA_PLSQL_DATATYPES.NumTabTyp
32 ,p_cdl_line_num IN PA_PLSQL_DATATYPES.NumTabTyp
33 DEFAULT PA_PLSQL_DATATYPES.EmptyNumTab
34 ,p_prvdr_cost_reclass_code IN PA_PLSQL_DATATYPES.Char240TabTyp
35 DEFAULT PA_PLSQL_DATATYPES.EmptyChar240Tab
36 ,x_sob_id OUT NOCOPY PA_PLSQL_DATATYPES.IDTabTyp
37 ,x_cc_dist_line_id OUT NOCOPY PA_PLSQL_DATATYPES.IDTabTyp
38 ,x_expenditure_item_id OUT NOCOPY PA_PLSQL_DATATYPES.IDTabTyp
39 ,x_line_num OUT NOCOPY PA_PLSQL_DATATYPES.IDTabTyp
40 ,x_line_type OUT NOCOPY PA_PLSQL_DATATYPES.Char2TabTyp
41 ,x_exchange_rate OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
42 ,x_rate_type OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp
43 ,x_rate_date OUT NOCOPY PA_PLSQL_DATATYPES.DateTabTyp
44 ,x_currency_code OUT NOCOPY PA_PLSQL_DATATYPES.Char15TabTyp
45 ,x_amount OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
46 );
47
48 PROCEDURE log_message(p_message IN VARCHAR2);
49
50 ------------------------------------------------------------
51 -- PROCEDURE DEFINTIONS
52 ------------------------------------------------------------
53
54 -------------------------------------------------------------------------------
55 -- Procedure bl_mc_delete
56 -------------------------------------------------------------------------------
57
58 PROCEDURE bl_mc_delete
59 (
60 p_cc_dist_line_id IN PA_PLSQL_DATATYPES.IDTabTyp
61 ,p_debug_mode IN boolean
62 ) IS
63
64 BEGIN
65
66 /**set_curr_function('bl_mc_delete');
67 log_message('50: Entered bl_mc_delete');
68
69 -- Deleting records for each line id (irrespective of the set of
70 -- books)
71
72 FORALL j IN p_cc_dist_line_id.First..p_cc_dist_line_id.Last
73
74 DELETE
75 FROM PA_MC_CC_DIST_LINES_ALL
76 WHERE cc_dist_line_id = p_cc_dist_line_id(j);
77
78 log_message('100: -- Records deleted : ' || sql%rowcount);
79
80 log_message('150: Leaving bl_mc_delete');
81
82 reset_curr_function;
83
84 EXCEPTION
85 WHEN OTHERS
86 THEN
87 log_message('200: ERROR in bl_mc_delete');
88 raise;
89 **/
90 null;
91 END bl_mc_delete;
92
93
94 -------------------------------------------------------------------------------
95 -- bl_mc_update
96 -------------------------------------------------------------------------------
97
98 PROCEDURE bl_mc_update
99 (
100 p_primary_sob_id IN gl_sets_of_books.set_of_books_id%TYPE
101 ,p_prvdr_org_id IN PA_PLSQL_DATATYPES.IDTabTyp
102 ,p_rsob_id IN PA_PLSQL_DATATYPES.IDTabTyp
103 ,p_rcurrency_code IN PA_PLSQL_DATATYPES.Char15TabTyp
104 ,p_cc_dist_line_id IN PA_PLSQL_DATATYPES.IDTabTyp
105 ,p_line_type IN PA_PLSQL_DATATYPES.Char2TabTyp
106 ,p_upd_type IN PA_PLSQL_DATATYPES.Char1TabTyp
107 ,p_expenditure_item_date IN PA_PLSQL_DATATYPES.DateTabTyp
108 ,p_expenditure_item_id IN PA_PLSQL_DATATYPES.IDTabTyp
109 DEFAULT PA_PLSQL_DATATYPES.EmptyIDTab
110 ,p_denom_currency_code IN PA_PLSQL_DATATYPES.Char15TabTyp
111 ,p_acct_tp_rate_type IN PA_PLSQL_DATATYPES.Char30TabTyp
112 ,p_acct_tp_exchange_rate IN PA_PLSQL_DATATYPES.NumTabTyp
113 ,p_denom_transfer_price IN PA_PLSQL_DATATYPES.NumTabTyp
114 ,p_cdl_line_num IN PA_PLSQL_DATATYPES.NumTabTyp
115 DEFAULT PA_PLSQL_DATATYPES.EmptyNumTab
116 ,p_prvdr_cost_reclass_code IN PA_PLSQL_DATATYPES.Char240TabTyp
117 DEFAULT PA_PLSQL_DATATYPES.EmptyChar240Tab
118 ,p_login_id IN NUMBER
119 ,p_program_id IN NUMBER
120 ,p_program_application_id IN NUMBER
121 ,p_request_id IN NUMBER
122 ,p_debug_mode IN boolean
123 ) IS
124
125
126 x_sob_id PA_PLSQL_DATATYPES.IDTabTyp;
127 x_cc_dist_line_id PA_PLSQL_DATATYPES.IDTabTyp;
128 x_expenditure_item_id PA_PLSQL_DATATYPES.IDTabTyp;
129 x_line_num PA_PLSQL_DATATYPES.IDTabTyp;
130 x_line_type PA_PLSQL_DATATYPES.Char2TabTyp;
131 x_exchange_rate PA_PLSQL_DATATYPES.NumTabTyp;
132 x_rate_type PA_PLSQL_DATATYPES.Char30TabTyp;
133 x_rate_date PA_PLSQL_DATATYPES.DateTabTyp;
134 x_currency_code PA_PLSQL_DATATYPES.Char15TabTyp;
135 x_amount PA_PLSQL_DATATYPES.NumTabTyp;
136
137 i PLS_INTEGER:= 0;
138
139 BEGIN
140
141 /**
142 set_curr_function('bl_mc_update');
143
144 log_message('250: Entered bl_mc_update');
145
146 -- Obtain the converted values for each combination of set of books
147 -- and line id attributes
148
149 get_mrc_values(
150 p_primary_sob_id => p_primary_sob_id
151 ,p_prvdr_org_id => p_prvdr_org_id
152 ,p_rsob_id => p_rsob_id
153 ,p_rcurrency_code => p_rcurrency_code
154 ,p_cc_dist_line_id => p_cc_dist_line_id
155 ,p_upd_type => p_upd_type
156 ,p_denom_currency_code => p_denom_currency_code
157 ,p_acct_tp_rate_type => p_acct_tp_rate_type
158 ,p_expenditure_item_date => p_expenditure_item_date
159 ,p_acct_tp_exchange_rate => p_acct_tp_exchange_rate
160 ,p_denom_amount => p_denom_transfer_price
161 ,p_line_type => p_line_type
162 ,p_cdl_line_num => p_cdl_line_num
163 ,p_prvdr_cost_reclass_code => p_prvdr_cost_reclass_code
164 ,x_sob_id => x_sob_id
165 ,x_cc_dist_line_id => x_cc_dist_line_id
166 ,x_expenditure_item_id => x_expenditure_item_id
167 ,x_line_num => x_line_num
168 ,x_line_type => x_line_type
169 ,x_exchange_rate => x_exchange_rate
170 ,x_rate_type => x_rate_type
171 ,x_rate_date => x_rate_date
172 ,x_currency_code => x_currency_code
173 ,x_amount => x_amount
174 );
175
176 log_message('300: About to update pa_mc_cc_dist_lines_all');
177
178 -- If all the rows being updated are reversing lines then the
179 -- get_mrc_values will not return any rows in x_cc_dist_line_id. Then
180 -- no updates to the MRC table need to be performed.
181
182 IF x_cc_dist_line_id.exists(1)
183 THEN
184 FORALL j IN x_cc_dist_line_id.First..x_cc_dist_line_id.Last
185 UPDATE pa_mc_cc_dist_lines_all
186 SET
187 acct_tp_rate_type = x_rate_type(j)
188 ,acct_tp_rate_date = x_rate_date(j)
189 ,acct_tp_exchange_rate = x_exchange_rate(j)
190 ,amount = x_amount(j)
191 ,request_id = p_request_id
192 ,program_id = p_program_id
193 ,program_application_id = p_program_application_id
194 WHERE set_of_books_id = x_sob_id(j)
195 AND cc_dist_line_id = x_cc_dist_line_id(j)
196 AND prc_assignment_id = -99;
197
198 log_message('350: Rows updated : ' || sql%rowcount);
199
200 END IF;
201
202 -- Clean up before leaving
203
204 log_message('350: Cleaning up');
205
206 x_sob_id.delete;
207 x_cc_dist_line_id.delete;
208 x_expenditure_item_id.delete;
209 x_line_num.delete;
210 x_line_type.delete;
211 x_exchange_rate.delete;
212 x_rate_type.delete;
213 x_rate_date.delete;
214 x_currency_code.delete;
215 x_amount.delete;
216
217 log_message('400: Leaving bl_mc_update');
218
219 reset_curr_function;
220
221 EXCEPTION
222 WHEN OTHERS
223 THEN
224 log_message('450: ERROR in bl_mc_update');
225 raise;
226 **/
227 null;
228 END bl_mc_update;
229
230
231 -------------------------------------------------------------------------------
232 -- bl_mc_insert
233 -------------------------------------------------------------------------------
234
235 PROCEDURE bl_mc_insert
236 (
237 p_primary_sob_id IN gl_sets_of_books.set_of_books_id%TYPE
238 ,p_prvdr_org_id IN PA_PLSQL_DATATYPES.IDTabTyp
239 ,p_rsob_id IN PA_PLSQL_DATATYPES.IDTabTyp
240 ,p_rcurrency_code IN PA_PLSQL_DATATYPES.Char15TabTyp
241 ,p_cc_dist_line_id IN PA_PLSQL_DATATYPES.IDTabTyp
242 ,p_line_type IN PA_PLSQL_DATATYPES.Char2TabTyp
243 ,p_expenditure_item_id IN PA_PLSQL_DATATYPES.IDTabTyp
244 ,p_line_num IN PA_PLSQL_DATATYPES.IDTabTyp
245 ,p_denom_currency_code IN PA_PLSQL_DATATYPES.Char15TabTyp
246 ,p_acct_tp_rate_type IN PA_PLSQL_DATATYPES.Char30TabTyp
247 ,p_expenditure_item_date IN PA_PLSQL_DATATYPES.DateTabTyp
248 ,p_acct_tp_exchange_rate IN PA_PLSQL_DATATYPES.NumTabTyp
249 ,p_denom_transfer_price IN PA_PLSQL_DATATYPES.NumTabTyp
250 ,p_dist_line_id_reversed IN PA_PLSQL_DATATYPES.IDTabTyp
251 DEFAULT PA_PLSQL_DATATYPES.EmptyIDTab
252 ,p_cdl_line_num IN PA_PLSQL_DATATYPES.NumTabTyp
253 DEFAULT PA_PLSQL_DATATYPES.EmptyNumTab
254 ,p_prvdr_cost_reclass_code IN PA_PLSQL_DATATYPES.Char240TabTyp
255 DEFAULT PA_PLSQL_DATATYPES.EmptyChar240Tab
256 ,p_login_id IN NUMBER
257 ,p_program_id IN NUMBER
258 ,p_program_application_id IN NUMBER
259 ,p_request_id IN NUMBER
260 ,p_debug_mode IN boolean
261 ) IS
262
263 x_sob_id PA_PLSQL_DATATYPES.IDTabTyp;
264 x_cc_dist_line_id PA_PLSQL_DATATYPES.IDTabTyp;
265 x_expenditure_item_id PA_PLSQL_DATATYPES.IDTabTyp;
266 x_line_num PA_PLSQL_DATATYPES.IDTabTyp;
267 x_line_type PA_PLSQL_DATATYPES.Char2TabTyp;
268 x_exchange_rate PA_PLSQL_DATATYPES.NumTabTyp;
269 x_rate_type PA_PLSQL_DATATYPES.Char30TabTyp;
270 x_rate_date PA_PLSQL_DATATYPES.DateTabTyp;
271 x_currency_code PA_PLSQL_DATATYPES.Char15TabTyp;
272 x_amount PA_PLSQL_DATATYPES.NumTabTyp;
273
274 i PLS_INTEGER := 0;
275
276 BEGIN
277 /**
278 set_curr_function('bl_mc_insert');
279
280 log_message('500: Entered bl_mc_insert');
281
282 -- Obtain the converted values for each combination of set of books
283 -- and line id attributes
284
285 get_mrc_values(
286 p_primary_sob_id => p_primary_sob_id
287 ,p_prvdr_org_id => p_prvdr_org_id
288 ,p_rsob_id => p_rsob_id
289 ,p_rcurrency_code => p_rcurrency_code
290 ,p_cc_dist_line_id => p_cc_dist_line_id
291 ,p_dist_line_id_reversed => p_dist_line_id_reversed
292 ,p_expenditure_item_id => p_expenditure_item_id
293 ,p_line_num => p_line_num
294 ,p_line_type => p_line_type
295 ,p_denom_currency_code => p_denom_currency_code
296 ,p_acct_tp_rate_type => p_acct_tp_rate_type
297 ,p_expenditure_item_date => p_expenditure_item_date
298 ,p_acct_tp_exchange_rate => p_acct_tp_exchange_rate
299 ,p_denom_amount => p_denom_transfer_price
300 ,p_cdl_line_num => p_cdl_line_num
301 ,p_prvdr_cost_reclass_code => p_prvdr_cost_reclass_code
302 ,x_sob_id => x_sob_id
303 ,x_cc_dist_line_id => x_cc_dist_line_id
304 ,x_expenditure_item_id => x_expenditure_item_id
305 ,x_line_num => x_line_num
306 ,x_line_type => x_line_type
307 ,x_exchange_rate => x_exchange_rate
308 ,x_rate_type => x_rate_type
309 ,x_rate_date => x_rate_date
310 ,x_currency_code => x_currency_code
311 ,x_amount => x_amount
312 );
313
314 log_message('550: About to apply MRC inserts');
315
316 IF x_cc_dist_line_id.exists(1)
317 THEN
318 FORALL i in x_cc_dist_line_id.First..x_cc_dist_line_id.Last
319 INSERT INTO PA_MC_CC_DIST_LINES_ALL
320 (
321 set_of_books_id
322 ,prc_assignment_id
323 ,cc_dist_line_id
324 ,expenditure_item_id
325 ,line_num
326 ,line_type
327 ,acct_currency_code
328 ,amount
329 ,program_id
330 ,program_application_id
331 ,program_update_date
332 ,request_id
333 ,transfer_status_code
334 ,acct_tp_rate_type
335 ,acct_tp_rate_date
336 ,acct_tp_exchange_rate
337 ,gl_batch_name
338 ,transferred_date
339 ,transfer_rejection_code
340 )
341 VALUES
342 (
343 x_sob_id(i) -- set_of_books_id
344 ,-99 -- prc_assignment_id
345 ,x_cc_dist_line_id(i) -- cc_dist_line_id
346 ,x_expenditure_item_id(i) -- expenditure_item_id
347 ,x_line_num(i) -- line_num
348 ,x_line_type(i) -- line_type
349 ,x_currency_code(i) -- acct_currency_code
350 ,x_amount(i) -- amount
351 ,p_program_id -- program_id
355 ,'P' -- transfer_status_code
352 ,p_program_application_id -- program_application_id
353 ,sysdate -- program_update_date
354 ,p_request_id -- request_id
356 ,x_rate_type(i) -- acct_tp_rate_type
357 ,x_rate_date(i) -- acct_tp_rate_date
358 ,x_exchange_rate(i) -- acct_tp_exchange_rate
359 ,NULL -- gl_batch_name
360 ,NULL -- transferred_date
361 ,NULL -- transfer_rejection_code
362 );
363 END IF;
364
365 log_message('600: Rows inserted: ' || sql%rowcount);
366
367 -- Clean up before leaving
368
369 x_sob_id.delete;
370 x_cc_dist_line_id.delete;
371 x_expenditure_item_id.delete;
372 x_line_num.delete;
373 x_line_type.delete;
374 x_exchange_rate.delete;
375 x_rate_type.delete;
376 x_rate_date.delete;
377 x_currency_code.delete;
378 x_amount.delete;
379
380 log_message('650: Leaving bl_mc_insert');
381
382 reset_curr_function;
383
384 EXCEPTION
385 WHEN OTHERS
386 THEN
387 log_message('700: ERROR in bl_mc_insert');
388 raise;
389 **/
390 null;
391 END bl_mc_insert;
392
393 -------------------------------------------------------------------------------
394 -- get_mrc_values
395 --
396 -- This function is called by the bl_mc_insert and bl_mc_updated
397 -- procedures and computes the conversions for each set of books and
398 -- line ids passed in. The details of each Cross Charge Distribution
399 -- and the corresponding attributes are passed in (the IN parameters).
400 -- The output is a cartesian product of the set of books and line ids
401 -- passed in. For example if there are 2 sets of books (p_rsob_id
402 -- table will have two values) and three line ids (p_cc_dist_line_id
403 -- table will have 3 values), then the corresponding out parameters
404 -- will have 6 records (Sob1, Line1), (Sob2, Line1), (Sob1, Line2),
405 -- (Sob2, Line2), etc.
406 --
407 -- Depending on whether it is called from the bl_mc_update or
408 -- bl_mc_insert procedure, certain values may not be populated. For
409 -- example, upd_type is not relevant for inserts and p_cc_dist_line_id
410 -- is not relevant for updates.
411 -------------------------------------------------------------------------------
412
413 PROCEDURE get_mrc_values (
414 p_primary_sob_id IN gl_Sets_of_books.set_of_books_id%TYPE
415 ,p_prvdr_org_id IN PA_PLSQL_DATATYPES.IDTabTyp
416 ,p_rsob_id IN PA_PLSQL_DATATYPES.IDTabTyp
417 ,p_rcurrency_code IN PA_PLSQL_DATATYPES.Char15TabTyp
418 ,p_cc_dist_line_id IN PA_PLSQL_DATATYPES.IDTabTyp
419 ,p_upd_type IN PA_PLSQL_DATATYPES.Char1TabTyp
420 DEFAULT PA_PLSQL_DATATYPES.EmptyChar1Tab
421 ,p_dist_line_id_reversed IN PA_PLSQL_DATATYPES.IDTabTyp
422 DEFAULT PA_PLSQL_DATATYPES.EmptyIDTab
423 ,p_expenditure_item_id IN PA_PLSQL_DATATYPES.IDTabTyp
424 DEFAULT PA_PLSQL_DATATYPES.EmptyIDTab
425 ,p_line_num IN PA_PLSQL_DATATYPES.IDTabTyp
426 DEFAULT PA_PLSQL_DATATYPES.EmptyIDTab
427 ,p_line_type IN PA_PLSQL_DATATYPES.Char2TabTyp
428 ,p_denom_currency_code IN PA_PLSQL_DATATYPES.Char15TabTyp
429 ,p_acct_tp_rate_type IN PA_PLSQL_DATATYPES.Char30TabTyp
430 ,p_expenditure_item_date IN PA_PLSQL_DATATYPES.DateTabTyp
431 ,p_acct_tp_exchange_rate IN PA_PLSQL_DATATYPES.NumTabTyp
432 ,p_denom_amount IN PA_PLSQL_DATATYPES.NumTabTyp
433 ,p_cdl_line_num IN PA_PLSQL_DATATYPES.NumTabTyp
434 DEFAULT PA_PLSQL_DATATYPES.EmptyNumTab
435 ,p_prvdr_cost_reclass_code IN PA_PLSQL_DATATYPES.Char240TabTyp
436 DEFAULT PA_PLSQL_DATATYPES.EmptyChar240Tab
437 ,x_sob_id OUT NOCOPY PA_PLSQL_DATATYPES.IDTabTyp
438 ,x_cc_dist_line_id OUT NOCOPY PA_PLSQL_DATATYPES.IDTabTyp
439 ,x_expenditure_item_id OUT NOCOPY PA_PLSQL_DATATYPES.IDTabTyp
440 ,x_line_num OUT NOCOPY PA_PLSQL_DATATYPES.IDTabTyp
441 ,x_line_type OUT NOCOPY PA_PLSQL_DATATYPES.Char2TabTyp
442 ,x_exchange_rate OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
443 ,x_rate_type OUT NOCOPY PA_PLSQL_DATATYPES.Char30TabTyp
444 ,x_rate_date OUT NOCOPY PA_PLSQL_DATATYPES.DateTabTyp
445 ,x_currency_code OUT NOCOPY PA_PLSQL_DATATYPES.Char15TabTyp
446 ,x_amount OUT NOCOPY PA_PLSQL_DATATYPES.NumTabTyp
447 ) IS
448
449 l_sob_cnt PLS_INTEGER ; -- Count of set of books
450 l_id_cnt PLS_INTEGER ; -- Count of line ids
451 i PLS_INTEGER ; -- Output record counter
452
453 l_result_code VARCHAR2(15); -- Holds the result of the get_mc_rate package
454 l_denominator_rate NUMBER; -- Denominator rate from mc package
455 l_numerator_rate NUMBER; -- Numerator rate from mc package
456
457 l_cdl_line_num pa_cost_distribution_lines_all.line_num%TYPE;
458
459 -- Single element variables
460 l_acct_tp_rate_type pa_cc_dist_lines_all.acct_tp_rate_type%TYPE;
461 l_acct_tp_rate_date DATE;
465 lb_process_record BOOLEAN;
462 l_acct_tp_exchange_rate pa_cc_dist_lines_all.acct_tp_exchange_rate%TYPE;
463
464 -- Defines whether MC has to be performed on record or not
466
467 -- Defines whether rate has to be obtained from the MRC package or
468 -- whether it will be obtained from a reversed line
469 lb_get_new_rate BOOLEAN;
470
471 -- Defines whether this is a Provider Reclass entry
472 lb_provider_reclass_line BOOLEAN;
473
474 -- Defines whether this is a Borrowed and Lent entry
475 lb_borrowed_lent_line BOOLEAN;
476
477 -- Exception raised if line type is not valid
478 EXCP_INVALID_LINE_TYPE EXCEPTION;
479
480 BEGIN
481 /*
482 set_curr_function('get_mrc_values');
483
484 log_message('750: Entered get_mrc_values');
485
486
487 i := 0; -- Initialize output counter
488
489 -- Following are two loops so that the cartesian product of the
490 -- reporting set of books and the line_id records can be obtained.
491 -- Set of books id put in as inner loop since it is more likely that
492 -- all record related to a line_id will be physically together in the
493 -- database
494
495 FOR l_id_cnt IN p_cc_dist_line_id.First..p_cc_dist_line_id.Last
496 LOOP
497
498 log_message('800: Processing set of books count ' || l_id_cnt);
499
500 -- Determine the type of line passed in. The two types of lines
501 -- supported at present are: Provider Reclassification and Borrowed
502 -- and Lent
503
504 IF p_line_type(l_id_cnt) = 'BL'
505 THEN
506 log_message('850: Processing Borrowed and Lent line');
507 lb_borrowed_lent_line := TRUE;
508 lb_provider_reclass_line := FALSE;
509 ELSIF p_line_type(l_id_cnt) = 'PC'
510 THEN
511 log_message('900: Processing Provider Reclass line');
512 lb_provider_reclass_line := TRUE;
513 lb_borrowed_lent_line := FALSE;
514 ELSE
515 IF ( p_upd_type.exists(l_id_cnt) )
516 THEN
517 IF ( p_upd_type(l_id_cnt) <> 'R' )
518 THEN
519 log_message('940: Invalid line type');
520 raise EXCP_INVALID_LINE_TYPE;
521 END IF;
522 ELSE
523 log_message('970: Invalid line type');
524 raise EXCP_INVALID_LINE_TYPE;
525 END IF;
526 END IF;
527
528
529 FOR l_sob_cnt IN p_rsob_id.First..p_rsob_id.Last
530 LOOP
531
532 log_message('1000: Processing set of books id: ' || p_rsob_id(l_sob_cnt));
533
534 -- No updates required for reversed distribution lines (upd_type =
535 -- 'R'). For inserts, upd_type will not exist while for other updates,
536 -- upd_type will be 'U'. The logic below avoids a no_Data_found error
537 -- so that the value of upd_type is not checked when it is not
538 -- initialized
539
540 IF p_upd_type.exists(l_id_cnt)
541 THEN
542 IF p_upd_type(l_id_cnt) = 'R'
543 THEN
544 lb_process_record := FALSE;
545 ELSE
546 lb_process_record := TRUE;
547 END IF;
548 ELSE
549 lb_process_record := TRUE;
550 END IF;
551
552 IF lb_process_record
553 THEN
554
555 -- Set counter for output records
556 i := i + 1;
557
558 -- Copy line attributes for insert if applicable
559
560 x_sob_id(i) := p_rsob_id(l_sob_cnt);
561 x_currency_code(i) := p_rcurrency_code(l_sob_cnt);
562 x_cc_dist_line_id(i) := p_cc_dist_line_id(l_id_cnt);
563
564 -- Expenditure item id not required for updates. Populate in output
565 -- only if passed in
566
567 IF p_expenditure_item_id.exists(l_id_cnt)
568 THEN
569 x_expenditure_item_id(i) := p_expenditure_item_id(l_id_cnt);
570 x_line_num(i) := p_line_num(l_id_cnt);
571 x_line_type(i) := p_line_type(l_id_cnt);
572 END IF;
573
574 -- If there is a dist_line_id_reversed, this means that the current
575 -- distribution reverses an existing one. The attributes for this
576 -- distribution will be copied from the reversed distribution and the
577 -- amounts negated
578
579 IF p_dist_line_id_reversed.exists(l_id_cnt)
580 THEN
581 IF p_dist_line_id_reversed(l_id_cnt) IS NOT NULL
582 THEN
583 lb_get_new_rate := FALSE;
584 ELSE
585 lb_get_new_rate := TRUE;
586 END IF;
587 ELSE
588 lb_get_new_rate := TRUE;
589 END IF;
590
591 -- If this is not a reversing line, then the attributes have to be
592 -- derived. In the case of Borrowed and Lent lines, the derivation is
593 -- straightforward - take the input denom transfer price and convert
594 -- it to the corresponding amount in the reporting set of books. For
595 -- Provider Reclassification entries (line_type = 'PC'), since it is
596 -- supposed to reclassify the existing cost, the attributes should not
597 -- be got from the GL API (using current conversion attributes), but
598 -- instead, should be got from the corresponding CDL line
599
600 IF lb_get_new_rate --- If100
601 THEN
602
603 log_message('1050: New rates to be determined');
604
605 IF lb_borrowed_lent_line --- If200
606 THEN
607
611 -- conversion from the denom_transfer_price to the acct_transfer_price
608 log_message('1100: Processing Borrowed and Lent line');
609
610 -- The existing rate attributes have to be passed in. If the
612 -- has occurred using a 'User' rate type in the primary set of books,
613 -- then the MRC package will first convert the denom_Transfer_price to
614 -- the functional currency and then to the reporting currency (a two
615 -- stage conversion). Otherwise, it will convert directly from the
616 -- denom_transfer_price to the reporting currency
617 -- Note that for the reporting set of books, the date used is always
618 -- the expenditure item date
619
620 l_acct_tp_rate_type := p_acct_tp_rate_type(l_id_cnt);
621 l_acct_tp_exchange_rate := p_acct_tp_exchange_rate(l_id_cnt);
622 l_acct_tp_rate_date := p_expenditure_item_date(l_id_cnt);
623
624 -- Log rate attributes passed in for future debugging
625
626 log_message('1150: Parameters passed to MRC API');
627 log_message('1200: Primary sob id: ' || to_char(p_primary_sob_id));
628 log_message('1250: Reporting sob id: ' || to_char(p_rsob_id(l_sob_cnt)));
629 log_message('1300: Trans date : ' || p_expenditure_item_date(l_id_cnt));
630 log_message('1350: Conv type: ' || l_acct_tp_rate_type);
631 log_message('1400: Conv date: ' || l_acct_tp_rate_date);
632 log_message('1450: Conv rate: ' || l_acct_tp_exchange_rate);
633 log_message('1500: Org_id: ' || p_prvdr_org_id(l_id_cnt));
634
635 -- Call the MRC API for obtaining the converted values for this set of
636 -- books and line attributes
637
638
639 gl_mc_currency_pkg.get_rate
640 ( p_primary_set_of_books_id => p_primary_sob_id
641 ,p_reporting_set_of_books_id => p_rsob_id(l_sob_cnt)
642 ,p_trans_date => p_expenditure_item_date(l_id_cnt)
643 ,p_trans_currency_code => p_denom_currency_code(l_id_cnt)
644 ,p_trans_conversion_type => l_acct_tp_rate_type
645 ,p_trans_conversion_date => l_acct_tp_rate_date
646 ,p_trans_conversion_rate => l_acct_tp_exchange_rate
647 ,p_application_id => 275
648 ,p_org_id => p_prvdr_org_id(l_id_cnt)
649 ,p_fa_book_type_code => NULL
650 ,p_je_source_name => NULL
651 ,p_je_category_name => NULL
652 ,p_result_code => l_result_code
653 ,p_denominator_rate => l_denominator_rate
654 ,p_numerator_rate => l_numerator_rate
655 );
656
657 log_message('1550: Returned from get_rate with ' || l_result_code);
658
659 -- Save returned attributes
660
661 x_rate_type(i) := l_acct_tp_rate_type;
662 x_rate_date(i) := l_acct_tp_rate_date;
663 x_exchange_rate(i) := l_acct_tp_exchange_rate;
664
665 IF l_acct_tp_rate_type = 'User'
666 THEN
667 log_message('1600: Rate type returned is user');
668 x_amount(i) :=
669 pa_mc_currency_pkg.CurrRound ((p_denom_amount(l_id_cnt)*
670 p_acct_tp_exchange_rate(l_id_cnt)),
671 p_rcurrency_code(l_sob_cnt));
672 ELSE
673 x_amount(i) :=
674 pa_mc_currency_pkg.CurrRound(
675 ((p_denom_amount(l_id_cnt)/
676 l_denominator_rate)*l_numerator_rate),
677 p_rcurrency_code(l_sob_cnt));
678 END IF;
679
680 -- For the Provider Reclassification entries, the CDL line acts
681 -- as the source of the rates and amounts.
682
683 -- Following elsif borrowed and lent line If200
684
685 ELSIF lb_provider_reclass_line
686 THEN
687
688 log_message('1650: Selecting MRC Cost distribution line');
689
690 SELECT currency_code
691 ,decode(p_prvdr_cost_reclass_code(l_id_cnt), 'R',
692 amount, burdened_cost)
693 ,rate_type
694 ,conversion_date
695 ,exchange_rate
696 INTO
697 x_currency_code(i)
698 ,x_amount(i)
699 ,x_rate_type(i)
700 ,x_rate_date(i)
701 ,x_exchange_rate(i)
702 FROM pa_mc_cost_dist_lines_all
703 WHERE expenditure_item_id = p_expenditure_item_id(l_id_cnt)
704 AND line_num = p_cdl_line_num(l_id_cnt)
705 AND prc_assignment_id = -99
706 AND set_of_books_id = p_rsob_id(l_sob_cnt);
707
708 log_message('1700: Got MRC Cost distribution line');
709
710 x_sob_id(i) := p_rsob_id(l_sob_cnt);
711 x_cc_dist_line_id(i) := p_cc_dist_line_id(l_id_cnt);
712
713
714 END IF; -- If200
715
716 ELSE -- Else100 - reversing or not
717
718 -- Reversing line. Note that unlike for Cost Distribution lines, it
722 -- reversed line
719 -- does not matter whether the reversed line is in the same
720 -- Expenditure Item or a different Expenditure item. The two line_ids
721 -- are linked by the dist_line_id_reversed column. Getting values from
723
727 acct_currency_code,
724 log_message('1750: Getting values from reversed line');
725
726 SELECT
728 -amount,
729 acct_tp_rate_type,
730 acct_tp_rate_date,
731 acct_tp_exchange_rate
732 INTO
733 x_currency_code(i),
734 x_amount(i),
735 x_rate_type(i),
736 x_rate_date(i),
737 x_exchange_rate(i)
738 FROM pa_mc_cc_dist_lines_all
739 WHERE set_of_books_id = p_rsob_id(l_sob_cnt)
740 AND cc_dist_line_id = p_dist_line_id_reversed(l_id_cnt)
741 AND prc_assignment_id = -99;
742
743 log_message('1800: Got values for reversed line');
744
745 END IF; -- end get_new rate
746
747 END IF; -- end process record
748
749 END LOOP; -- end loop for processing line ids
750
751 END LOOP; -- end loop for processing set of books
752
753 log_message('1850: Leaving get_mrc_values. Filled up records: '|| to_char(i));
754
755 reset_curr_function;
756
757 EXCEPTION
758 WHEN OTHERS
759 THEN
760 x_sob_id.delete;
761 x_cc_dist_line_id.delete;
762 x_expenditure_item_id.delete;
763 x_line_num.delete;
764 x_line_type.delete;
765 x_exchange_rate.delete;
766 x_rate_type.delete;
767 x_rate_date.delete;
768 x_currency_code.delete;
769 x_amount.delete;
770 log_message('1900: Error in get_mrc_values');
771 raise;
772 */
773 null;
774 END get_mrc_values;
775
776 -------------------------------------------------------------------------------
777 -- log_message
778 -------------------------------------------------------------------------------
779
780 PROCEDURE log_message( p_message IN VARCHAR2) IS
781 BEGIN
782 /*
783 pa_cc_utils.log_message(p_message);
784 */
785 null;
786
787 END log_message;
788
789 -------------------------------------------------------------------------------
790 -- set_curr_function
791 -------------------------------------------------------------------------------
792
793 PROCEDURE set_curr_function(p_function IN VARCHAR2) IS
794 BEGIN
795 --pa_cc_utils.set_curr_function(p_function);
796 null;
797
798 END;
799
800 -------------------------------------------------------------------------------
801 -- reset_curr_function
802 -------------------------------------------------------------------------------
803
804 PROCEDURE reset_curr_function IS
805 BEGIN
806 --pa_cc_utils.reset_curr_function;
807 null;
808 END;
809
810 END PA_MC_BORRLENT;
[Home] [Help]