[Home] [Help]
PACKAGE BODY: APPS.PA_XLA_INTERFACE_PKG
Source
1 PACKAGE BODY PA_XLA_INTERFACE_PKG AS
2 -- $Header: PAXLAIFB.pls 120.79.12020000.3 2013/03/06 09:43:39 admarath ship $
3
4
5 Type TypeNum is table of number index by binary_integer;
6 Type TypeDate is table of date index by binary_integer;
7 Type TypeVarChar is table of varchar2(30) index by binary_integer;
8 Type TypeVarChar1 is table of varchar2(1) index by binary_integer;
9 Type TypeVarChar2 is table of varchar2(2) index by binary_integer;
10 Type TypeVarChar10 is table of varchar2(10) index by binary_integer;
11 Type TypeVarchar240 is table of varchar2(240) index by binary_integer;
12 Type TEventInfo35 is table of varchar2(35) index by varchar2(10);
13 Type TEventInfo10 is table of varchar2(10) index by varchar2(10);
14
15 -- Global variables for bulk collecting the data for accounting event creation.
16 t_event_id TypeNum;
17 t_event_type_code TypeVarChar;
18 t_gl_date TypeDate;
19
20 ---
21
22 t_entity_id TypeNum;
23 t_line_type TypeVarChar2;
24 t_line_num TypeNum;
25 t_parent_line_num TypeNum;
26 t_line_num_reversed TypeNum;
27 t_orig_dist_line_id TypeNum;
28 t_parent_dist_line_id TypeNum;
29 t_cc_dist_line_id TypeNum;
30 t_dist_line_id_reversed TypeNum;
31
32 t_adjusted_item TypeNum;
33 t_transferred_from_item TypeNum;
34 t_historical_flag TypeVarChar1;
35 t_transaction_source TypeVarChar;
36 t_txn_type TypeVarChar;
37 t_payment_id TypeNum;
38 t_header_id TypeNum;
39 t_distribution_id TypeNum;
40 t_reversed_flag TypeVarChar1;
41 t_event_date TypeDate;
42 t_sr5 TypeNum; -- System reference 5 from CDL
43 t_orig_historic TypeVarChar1;
44 t_orig_line_num TypeNum;
45 t_orig_acct_source TypeVarChar10;
46 t_orig_tsc TypeVarChar1; -- Transfer status code
47 t_acct_source TypeVarChar10;
48 t_pts_source TypeVarChar10; -- Transcation source
49 t_cr_ccid TypeNum;
50 t_orig_cr_ccid TypeNum;
51
52 -- following need initialization
53 t_tsc TypeVarChar1;
54 t_xfer_reject TypeVarChar;
55 t_get_data TypeVarChar10;
56 t_raise_event TypeVarChar1;
57 ---
58
59 g_org_id Number; -- Operating unit
60 g_ledger_id Number; -- Ledger id of oprating unit.
61 g_legal_entity_id Number; -- Legal entity id of operating unit
62 g_calling_module Varchar2(30);
63 g_data_set_id Number; -- Cincurrent request id, budget version id etc.
64 g_imp_cr_ccid Number; -- Default supplier cost credit account
65
66 g_entity_code Varchar2(30);
67 g_project_org_id pa_projects_all.org_id%type; -- Project operating unit.
68
69 -- static global pl/sql tables for holding event type info
70 g_tab_module TEventInfo10;
71 g_tab_event_type_code TEventInfo35;
72
73 -- global variables for debug purpose
74 g_acct_source VARCHAR2(30);
75 g_prev_txn_source VARCHAR2(30);
76 g_debug_module VARCHAR2(100);
77 g_debug_mode Varchar2(1);
78 g_debug_level3 CONSTANT NUMBER := 3;
79 g_debug_level5 CONSTANT NUMBER := 5;
80
81 Type CostCurType is REF CURSOR;
82
83 TYPE t_source_info is table of xla_events_pub_pkg.t_event_source_info index by binary_integer;
84 tt_source_info t_source_info;
85 TYPE t_security_info is table of xla_events_pub_pkg.t_security index by binary_integer;
86 tt_security_info t_security_info;
87
88 -- procedure to populate SLA's array with info required for raising
89 -- accounting events
90 PROCEDURE POPULATE_COST_EVENT_ARRAY(x_rows_found out nocopy number);
91
92 -- procedure for stamping event id returned from SLA on distribution
93 -- lines and update transfer status code to 'A'
94 PROCEDURE TIEBACK_COST_EVENT;
95
96 -- procedure with declarations of cursors
97 -- procedure will return a cursor based in calling module
98 PROCEDURE OPEN_COST_CUR(p_cost_cursor IN OUT NOCOPY CostCurType,
99 p_calling_module IN VARCHAR2);
100
101 -- ---------------------------------------------------------------------------------+
102 -- ENCUMBRANCE RELATED CHANGES (Initalization): Start
103
104 -- 1.0: Forward Declarations
105
106 -- This procedure will derive the budget version to reverse (if any)
107 -- It will also derive budget status code of the current budget version
108
109 --Uncommented as part of 13857862 to revert the changes
110
111 PROCEDURE GET_BVID_TO_REVERSE(p_budget_version_id IN NUMBER,
112 p_curr_budget_status_code OUT NOCOPY VARCHAR2,
113 p_old_budget_version_id OUT NOCOPY NUMBER);
114
115 -- This procedure will reset the event id information on the current budget
116 -- and the budget being reversed
117
118 PROCEDURE RESET_EVENT_ID (p_budget_version_id IN NUMBER,
119 p_curr_budget_status_code IN VARCHAR2,
120 p_old_budget_version_id IN NUMBER);
121
122
123 -- This procedure will call XLA public API to delete draft
124 -- events (if they were established earlier), called from "Reset_event_id"
125 -- for budgets and "populate_enc_event_array" for funds check.
126
127 PROCEDURE DELETE_XLA_EVENT_DATA(p_data_set_id1 IN NUMBER,
128 p_data_set_id2 IN NUMBER,
129 p_calling_module IN VARCHAR2,
130 p_events_to_delete IN OUT NOCOPY VARCHAR2);
131
132 -- This procedure builds the pl/sql array for event creation
133
134 PROCEDURE POPULATE_ENC_EVENT_ARRAY;
135
136 -- This procedure will populate xla_events_gt table
137
138 --PROCEDURE POPULATE_ENC_EVENTS_GT (p_Source_Id_Int1 IN TypeNum,
139 -- p_event_type_code IN TypeVarChar,
140 -- p_event_date IN TypeDate,
141 -- p_calling_module IN Varchar2);
142
143 -- This procedure will execute during commitment tieback processing
144
145 PROCEDURE TIEBACK_ENC_EVENT;
146
147 -- This procedure will execute during budget tieback processing
148
149 PROCEDURE TIEBACK_BUDGET_EVENT;
150
151 -- 2:0: Global variables
152 g_event_type_code xla_events.event_type_code%type;
153 g_bvid_to_reverse pa_budget_versions.budget_version_id%type;
154 g_enc_create_events_flag Varchar2(1);
155 l_events_to_delete Varchar2(1);
156
157 t_transaction_date TypeDate;
158 t_source_event_id TypeNum;
159 t_Source_Id_Int_1 TypeNum;
160 t_Source_Id_Int_2 TypeNum;
161 t_Source_Id_Int_3 TypeNum;
162 t_Source_Id_Int_4 TypeNum;
163 t_source_id_char_1 TypeVarChar;
164 t_source_id_char_2 TypeVarChar;
165 t_source_id_char_3 TypeVarChar;
166 t_source_id_char_4 TypeVarChar;
167 t_source_application_id TypeNum;
168 t_application_id TypeNum;
169 t_legal_entity_id TypeNum;
170 t_ledger_id TypeNum;
171 t_security_org_id TypeNum;
172 t_entity_type_code TypeVarChar;
173 t_transaction_number TypeVarChar240;
174 t_bc_rev_event_id TypeNum;
175
176 TYPE t_reference_info is table of xla_events_pub_pkg.t_event_reference_info index by binary_integer;
177 tt_reference_info t_reference_info;
178
179 t_reference_num_1 TypeVarChar;
180
181 -- ENCUMBRANCE RELATED CHANGES (Initalization): End
182
183 -- ---------------------------------------------------------------------------------+
184
185 FUNCTION GET_SOURCE(p_transaction_source varchar2,
186 p_payment_id number)
187 return varchar2 IS
188 l_acct_source varchar2(30);
189 BEGIN
190
191 if p_transaction_source is null then
192 return 'PA';
193 else
194 select decode(predefined_flag,
195 'N', decode(posted_flag,
196 'N', 'PA',
197 'EXT'),
198 decode(posted_flag,
199 'N', 'PA',
200 acct_source_code)) /* Added posted_flag to derive PA if it is 'N' for bug 13254338*/
201 into l_acct_source
202 from pa_transaction_sources
203 where transaction_source = p_transaction_source;
204
205 if l_acct_source like 'AP%' then
206 if p_payment_id is not null then
207 l_acct_source := 'AP_PAY';
208 end if;
209 /* Bug 5374040 if p_transaction_source = 'AP DISCOUNTS' then
210 l_acct_source := 'AP_INV';
211 end if;
212 */
213 end if;
214
215 return l_acct_source;
216 end if;
217 END GET_SOURCE;
218
219 -- procedure to initialize global variables and set debug context
220 PROCEDURE INIT(p_calling_module varchar2,
221 p_data_set_id number ) IS
222
223 BEGIN
224 g_debug_module := 'pa.plsql.PA_XLA_INTERFACE_PKG';
225 g_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
226
227 IF g_debug_mode = 'Y' THEN
228 pa_debug.set_curr_function(p_function => 'create_events',
229 p_debug_mode => g_debug_mode );
230 END IF;
231
232 IF g_debug_mode = 'Y' THEN
233 pa_debug.g_err_stage:= 'Create Events API Start';
234 pa_debug.write('create_events ' || g_debug_module, pa_debug.g_err_stage, g_debug_level5);
235 END IF;
236
237 select org_id, set_of_books_id, def_supplier_cost_cr_ccid
238 into g_org_id, g_ledger_id, g_imp_cr_ccid
239 from pa_implementations ;
240
241 select to_number(org_information2)
242 into g_legal_entity_id
243 from hr_organization_information
244 where organization_id = g_org_id
245 and org_information_context = 'Operating Unit Information';
246
247 g_calling_module := p_calling_module;
248 g_data_set_id := p_data_set_id;
249
250 IF g_debug_mode = 'Y' THEN
251 pa_debug.g_err_stage:= 'Parameter List: Calling Module: ' || g_calling_module || ', Dataset ID: ' ||
252 g_data_set_id;
253 pa_debug.write('create_events ' || g_debug_module, pa_debug.g_err_stage, g_debug_level5);
254 END IF;
255
256 IF g_calling_module in ('USG', 'PJ', 'INV', 'WIP', 'LAB', 'SUPP', 'BTC', 'TBC', 'CC', 'BL', 'PC', 'Cost') THEN
257 g_entity_code := 'EXPENDITURES';
258 END IF;
259
260 -- ----------------------------------------------------------------------- +
261 -- Encumbrance changes start.........................
262 -- ----------------------------------------------------------------------- +
263 -- If g_calling_module is 'CC_BUDGET' or 'CC_BUDGET_YEAR_END' , i.e. a CC
264 -- budget then we will need to get the secondary ledger id
265 -- When CC integration is carried out ..add code here ...
266
267 -- Derive entity code for encumbrances ..
268 IF g_calling_module in ('COST_BUDGET','CC_BUDGET','REVENUE_BUDGET',
269 'COST_BUDGET_YEAR_END','CC_BUDGET_YEAR_END') THEN
270 g_entity_code := 'BUDGETS';
271
272 END IF;
273 -- ----------------------------------------------------------------------- +
274 -- Encumbrance changes end.........................
275 -- ----------------------------------------------------------------------- +
276
277 -- Build array of possible calling modules based on system linkage
278 -- for Cost and by line type for Cross Charge.
279 g_tab_module('ST') := 'LAB';
280 g_tab_module('OT') := 'LAB';
281 g_tab_module('USG') := 'USG';
282 g_tab_module('INV') := 'INV';
283 g_tab_module('PJ') := 'PJ' ;
284 g_tab_module('WIP') := 'WIP';
285 g_tab_module('BTC') := 'BTC';
286 g_tab_module('TBC') := 'TBC';
287 g_tab_module('BL') := 'BL' ;
288 g_tab_module('PC') := 'PC' ;
289 g_tab_module('ER') := 'ER' ;
290 g_tab_module('VI') := 'VI' ;
291
292 -- Build array of event type codes based on calling modules possible
293 g_tab_event_type_code('LAB') := 'LABOR_COST_DIST';
294 g_tab_event_type_code('USG') := 'USG_COST_DIST';
295 g_tab_event_type_code('INV') := 'INVENTORY_COST_DIST';
296 g_tab_event_type_code('PJ') := 'MISC_COST_DIST';
297 g_tab_event_type_code('WIP') := 'WIP_COST_DIST';
298 g_tab_event_type_code('BTC') := 'BURDEN_COST_DIST';
299 g_tab_event_type_code('TBC') := 'TOT_BURDENED_COST_DIST';
300 g_tab_event_type_code('BL') := 'BL_DISTRIBUTION';
301 g_tab_event_type_code('PC') := 'PRVDR_RECVR_RECLASS';
302 g_tab_event_type_code('ER') := 'EXP_COST_DIST';
303 g_tab_event_type_code('VI') := 'SUPP_COST_DIST';
304
305 END INIT;
306
307 -- This is the main procedure which will be called by "Create Events for Cost/
308 -- Cross Charge" and cost side streamline processes for interface to GL to
309 -- raise accounting events in SLA and update the event id on the distributions.
310 -- Transfer status code is updated to 'A' if events are successfully raised.
311
312 PROCEDURE CREATE_EVENTS(p_calling_module IN VARCHAR2,
313 p_data_set_id IN NUMBER,
314 x_result_code OUT NOCOPY VARCHAR2) is
315
316 l_application_id NUMBER;
317 l_rows_found NUMBER;
318 l_event_status_code xla_events.event_status_code%TYPE;
319
320 BEGIN
321
322 IF g_debug_mode = 'Y' THEN
323 pa_debug.g_err_stage:= 'Create Events: Start';
324 pa_debug.write('create_events ' || g_debug_module, pa_debug.g_err_stage, g_debug_level5);
325 END IF;
326
327 -- initialize global variables. check debug mode, get org_id
328 -- and ledger_id. build pl/sql table of event type info.
329 init(p_calling_module, p_data_set_id);
330
331 l_application_id := 275;
332 l_event_status_code := xla_events_pub_pkg.c_event_unprocessed;
333
334
335
336 IF (g_entity_code = 'BUDGETS' or p_calling_module = 'FUNDS_CHECK') then
337 -- -------------------------------------------------------------+
338 -- ENCUMBRANCE ACCOUNTING
339 -- -------------------------------------------------------------+
340
341 -- -------------------------------------------------------------+
342 -- E1. Select data for processing
343 IF g_debug_mode = 'Y' THEN
344 pa_debug.g_err_stage:= 'Create Events: Calling procedure Populate_enc_event_array';
345 pa_debug.write('create_events ' || g_debug_module, pa_debug.g_err_stage, g_debug_level5);
346 END IF;
347 -- -------------------------------------------------------------+
348
349 POPULATE_ENC_EVENT_ARRAY;
350
351 -- -------------------------------------------------------------+
352 -- E2. Check if there are events to be RAISED ..
353 IF g_debug_mode = 'Y' THEN
354 pa_debug.g_err_stage:= 'Create Events: Check if events exist for processing ..';
355 pa_debug.write('create_events ' || g_debug_module,pa_debug.g_err_stage, g_debug_level5);
356 END IF;
357 -- -------------------------------------------------------------+
358 If NOT t_event_date.EXISTS(1) then
359
360 IF g_debug_mode = 'Y' THEN
361 pa_debug.g_err_stage:= 'Create Events: No event exist for processing ..';
362 pa_debug.write('create_events ' || g_debug_module,pa_debug.g_err_stage, g_debug_level5);
363 END IF;
364
365 goto noprocessing;
366
367 End if;
368
369 -- -------------------------------------------------------------+
370 -- E3. Event Creation ..
371 IF g_debug_mode = 'Y' THEN
372 pa_debug.g_err_stage:= 'Create Events: Event(s) exist for processing ..';
373 pa_debug.write('create_events ' || g_debug_module,pa_debug.g_err_stage, g_debug_level5);
374
375 pa_debug.g_err_stage:= 'Create Events: FC: Calling xla_events_pkg.create_event';
376 pa_debug.write('create_events ' || g_debug_module,pa_debug.g_err_stage, g_debug_level5);
377 END IF;
378 -- -------------------------------------------------------------+
379
380 FOR i in t_event_date.FIRST..t_event_date.LAST LOOP
381 t_event_id(i) := xla_events_pub_pkg.create_event
382 (p_event_source_info => tt_source_info(i)
383 ,p_event_type_code => t_event_type_code(i)
384 ,p_event_date => t_event_date(i)
385 ,p_event_status_code => l_event_status_code
386 ,p_event_number => NULL
387 ,p_transaction_date => t_transaction_date(i)
388 ,p_reference_info => NULL -- tt_reference_info(i)
389 ,p_valuation_method => NULL
390 ,p_security_context => tt_security_info(i)
391 ,p_budgetary_control_flag => 'Y');
392
393 END LOOP;
394
395 -- -------------------------------------------------------------+
396 -- E4. Stamp event_id on source table (pa_budget_lines for
397 -- Budgets, pa_bc_packets for FC)
398 IF g_debug_mode = 'Y' THEN
399 pa_debug.g_err_stage:= 'Create Events: Tieback Processing';
400 pa_debug.write('create_events ' || g_debug_module,pa_debug.g_err_stage, g_debug_level5);
401 END IF;
402 -- -------------------------------------------------------------+
403 IF g_entity_code = 'BUDGETS' then
404
405 TIEBACK_BUDGET_EVENT;
406
407 ELSIF p_calling_module = 'FUNDS_CHECK' then
408
409 TIEBACK_ENC_EVENT;
410
411 END IF;
412
413 -- -------------------------------------------------------------+
414 -- E5. Populate psa_bc_xla_events_gt with the new event_id's ...
415 IF g_debug_mode = 'Y' THEN
416 pa_debug.g_err_stage:= 'Create Events: FC: Populate psa_bc_xla_events_gt';
417 pa_debug.write('create_events ' || g_debug_module,pa_debug.g_err_stage, g_debug_level5);
418
419 -- Events that are being populated into psa_bc_xla_events_gt
420 for x in t_event_id.FIRST..t_event_id.LAST loop
421 pa_debug.write('create_events ' || g_debug_module,'Event id is:'||t_event_id(x), g_debug_level5);
422 end loop;
423
424 END IF;
425 -- -------------------------------------------------------------+
426 forall i in t_event_id.FIRST..t_event_id.LAST
427 Insert into psa_bc_xla_events_gt(event_id,result_code)
428 values(t_event_id(i),'XLA_ERROR');
429
430 -- -------------------------------------------------------------+
431 -- E6. Delete pl/sql table ..
432 IF g_debug_mode = 'Y' THEN
433 pa_debug.g_err_stage:= 'Create Events: Delete PL/sql table';
434 pa_debug.write('create_events ' || g_debug_module,pa_debug.g_err_stage, g_debug_level5);
435 END IF;
436 -- -------------------------------------------------------------+
437 tt_source_info.DELETE;
438 t_event_type_code.DELETE;
439 t_event_date.DELETE;
440 t_transaction_date.DELETE;
441 tt_security_info.DELETE;
442 t_event_id.DELETE;
443 t_source_id_int_1.DELETE;
444
445 Else
446 -- -------------------------------------------------------------+
447 -- Create ACTUALs Accounting events
448 -- -------------------------------------------------------------+
449
450
451
452 IF g_debug_mode = 'Y' THEN
453 pa_debug.g_err_stage:= 'Calling procedure populate_cost_event_array';
454 pa_debug.write('create_events ' || g_debug_module, pa_debug.g_err_stage, g_debug_level5);
455 END IF;
456
457 -- derive the entity and event type details.
458 -- populate SLA's array structure with required parameters
459
460
461
462 populate_cost_event_array(l_rows_found);
463
464
465
466 IF l_rows_found = 0 THEN
467
468 goto noprocessing;
469 END IF;
470
471 IF g_debug_mode = 'Y' THEN
472 pa_debug.g_err_stage:= 'Calling xla_events_pub_pkg to raise events';
473 pa_debug.write('create_events ' || g_debug_module, pa_debug.g_err_stage, g_debug_level5);
474 END IF;
475
476 FOR i in t_entity_id.first..t_entity_id.last LOOP
477 IF t_raise_event(i) = 'Y' THEN
478 t_event_id(i) := xla_events_pub_pkg.create_event
479 (p_event_source_info => tt_source_info(i)
480 ,p_event_type_code => t_event_type_code(i)
481 ,p_event_date => t_event_date(i)
482 ,p_event_status_code => 'U'
483 ,p_event_number => NULL
484 ,p_transaction_date => NULL
485 ,p_reference_info => NULL
486 ,p_valuation_method => NULL
487 ,p_security_context => tt_security_info(i));
488 END IF;
489 END LOOP;
490
491 -- stamp event_id on distribution lines and update transfer status code
492 tieback_cost_event;
493
494 -- clear the pl/sql tables
495 t_entity_id.delete;
496 t_event_date.delete;
497 t_txn_type.delete;
498 t_event_id.delete;
499 t_event_type_code.delete;
500 g_tab_module.delete;
501 g_tab_event_type_code.delete;
502
503 IF g_debug_mode = 'Y' THEN
504 pa_debug.g_err_stage:= 'Create Events API..End';
505 pa_debug.write('create_events ' || g_debug_module,
506 pa_debug.g_err_stage, g_debug_level5);
507 END IF;
508
509 End If; -- ENCUMBRANCE V/S ACTUAL ACCOUNTING Check
510
511 x_result_code := 'Success';
512 <<NOPROCESSING>>
513 x_result_code := 'Success';
514 null;
515 END CREATE_EVENTS;
516
517 -- procedure with declarations of cursors
518 -- procedure will return a cursor based in calling module
519 PROCEDURE OPEN_COST_CUR(p_cost_cursor IN OUT NOCOPY CostCurType,
520 p_calling_module IN VARCHAR2) IS
521 BEGIN
522 IF g_calling_module in ('CC', 'BL', 'PC') THEN
523 open p_cost_cursor for
524 select expenditure_item_id,
525 cc_dist_line_id,
526 adjusted_expenditure_item_id,
527 transferred_from_exp_item_id,
528 historical_flag,
529 parent_dist_line_id,
530 dist_line_id_reversed,
531 line_type,
532 gl_date,
533 orig_historic,
534 orig_dist_line_id,
535 orig_acct_source
536 from (
537 select cdl.expenditure_item_id,
538 cdl.cc_dist_line_id cc_dist_line_id,
539 exp.adjusted_expenditure_item_id,
540 exp.transferred_from_exp_item_id,
541 NVL(exp.historical_flag, 'Y') historical_flag,
542 NULL parent_dist_line_id,
543 NULL dist_line_id_reversed,
544 cdl.line_type,
545 trunc(cdl.gl_date) gl_date,
546 NULL orig_historic,
547 NULL orig_dist_line_id,
548 NULL orig_acct_source
549 from pa_cc_dist_lines_all cdl,
550 pa_expenditure_items_all exp
551 where exp.expenditure_item_id = cdl.expenditure_item_id
552 and cdl.request_id = g_data_set_id
553 and cdl.transfer_status_code = 'X'
554 and cdl.dist_line_id_reversed is null
555 and exp.adjusted_expenditure_item_id is null
556 UNION ALL
557 select cdl.expenditure_item_id,
558 cdl.cc_dist_line_id cc_dist_line_id,
559 exp.adjusted_expenditure_item_id,
560 exp.transferred_from_exp_item_id,
561 NVL(exp.historical_flag, 'Y') historical_flag,
562 cdl.dist_line_id_reversed parent_dist_line_id,
563 NULL dist_line_id_reversed,
564 cdl.line_type line_type,
565 trunc(cdl.gl_date) gl_date,
566 nvl(pe.historical_flag, 'Y') orig_historic,
567 cd.cc_dist_line_id orig_dist_line_id,
568 cd.acct_source_code orig_acct_source
569 from pa_cc_dist_lines_all cdl,
570 pa_expenditure_items_all exp,
571 pa_expenditure_items_all pe,
572 pa_cc_dist_lines_all cd
573 where exp.expenditure_item_id = cdl.expenditure_item_id
574 and cdl.request_id = g_data_set_id
575 and cdl.transfer_status_code = 'X'
576 and exp.adjusted_expenditure_item_id is not null
577 and exp.adjusted_expenditure_item_id = pe.expenditure_item_id
578 and pe.expenditure_item_id = cd.expenditure_item_id
579 and cdl.dist_line_id_reversed = cd.cc_dist_line_id
580 UNION ALL
581 select cdl.expenditure_item_id,
582 cdl.cc_dist_line_id cc_dist_line_id,
583 exp.adjusted_expenditure_item_id,
584 exp.transferred_from_exp_item_id,
585 NVL(exp.historical_flag, 'Y') historical_flag,
586 NULL parent_dist_id,
587 cdl.dist_line_id_reversed dist_line_id_reversed,
588 cdl.line_type line_type,
589 trunc(cdl.gl_date) gl_date,
590 NULL orig_historic,
591 cd.cc_dist_line_id orig_dist_line_id,
592 cd.acct_source_code orig_acct_source
593 from pa_cc_dist_lines_all cdl,
594 pa_expenditure_items_all exp,
595 pa_cc_dist_lines_all cd
596 where exp.expenditure_item_id = cdl.expenditure_item_id
597 and cdl.request_id = g_data_set_id
598 and cdl.transfer_status_code = 'X'
599 and cdl.dist_line_id_reversed is not null
600 and cdl.expenditure_item_id = cd.expenditure_item_id
601 and cdl.dist_line_id_reversed = cd.cc_dist_line_id)
602 order by expenditure_item_id, cc_dist_line_id;
603 ELSIF g_calling_module in ('Cost', 'LAB', 'SUPP', 'USG', 'BTC',
604 'PJ', 'WIP', 'INV', 'TBC') THEN
605 open p_cost_cursor for
606 select expenditure_item_id,
607 line_num,
608 adjusted_expenditure_item_id,
609 transferred_from_exp_item_id,
610 transaction_source,
611 historical_flag,
612 parent_line_num,
613 system_linkage_function,
614 line_num_reversed,
615 line_type,
616 gl_date,
617 document_payment_id,
618 document_header_id,
619 document_distribution_id,
620 orig_historic,
621 orig_line_num,
622 orig_acct_source,
623 orig_tsc,
624 system_reference5,
625 cr_code_combination_id,
626 pts_source,
627 orig_cr_ccid
628 from (
629 select cdl.expenditure_item_id,
630 cdl.line_num,
631 exp.adjusted_expenditure_item_id,
632 exp.transferred_from_exp_item_id,
633 exp.transaction_source,
634 NVL(exp.historical_flag, 'Y') historical_flag,
635 cdl.parent_line_num,
636 decode(cdl.line_type,
637 'C', 'TBC',
638 'D', 'TBC',
639 exp.system_linkage_function) system_linkage_function,
640 cdl.line_num_reversed,
641 decode(cdl.line_type, 'R', 'R', 'B') line_type,
642 trunc(cdl.gl_date) gl_date,
643 exp.document_payment_id,
644 exp.document_header_id,
645 exp.document_distribution_id,
646 NVL(exp.historical_flag, 'Y') orig_historic,
647 NULL orig_line_num,
648 NULL orig_acct_source,
649 NULL orig_tsc,
650 cdl.system_reference5,
651 cdl.cr_code_combination_id,
652 decode(pts.predefined_flag,
653 NULL, 'PA',
654 'N', decode(pts.posted_flag,
655 'N', 'PA', 'EXT'),
656 decode(pts.posted_flag,
657 'N', 'PA',pts.acct_source_code)) pts_source, /* Added posted_flag to derive PA if it is 'N' for bug 13254338*/
658 cdl.cr_code_combination_id orig_cr_ccid
659 from pa_cost_distribution_lines_all cdl,
660 pa_expenditure_items_all exp,
661 pa_transaction_sources pts
662 where exp.expenditure_item_id = cdl.expenditure_item_id
663 and exp.transaction_source = pts.transaction_source(+)
664 and cdl.request_id = g_data_set_id
665 and cdl.transfer_status_code = 'X'
666 and cdl.line_num_reversed is null
667 and ( exp.adjusted_expenditure_item_id is null or ( cdl.parent_line_num is null
668 and NVL(exp.historical_flag, 'Y') = 'Y'
669 )
670 )
671 UNION ALL
672 select cdl.expenditure_item_id,
673 cdl.line_num,
674 exp.adjusted_expenditure_item_id,
675 exp.transferred_from_exp_item_id,
676 exp.transaction_source,
677 NVL(exp.historical_flag, 'Y') historical_flag,
678 cdl.parent_line_num,
679 decode(cdl.line_type,
680 'C', 'TBC',
681 'D', 'TBC',
682 exp.system_linkage_function) system_linkage_function,
683 cdl.line_num_reversed,
684 decode(cdl.line_type, 'R', 'R', 'B') line_type,
685 trunc(cdl.gl_date) gl_date,
686 exp.document_payment_id ,
687 exp.document_header_id ,
688 exp.document_distribution_id ,
689 nvl(pe.historical_flag, 'Y') orig_historic,
690 cd.line_num orig_line_num,
691 cd.acct_source_code orig_acct_source,
692 cd.transfer_status_code orig_tsc,
693 cdl.system_reference5,
694 cdl.cr_code_combination_id,
695 decode(pts.predefined_flag,
696 NULL, 'PA',
697 'N', decode(pts.posted_flag,
698 'N', 'PA',
699 'EXT'),
700 decode(pts.posted_flag,
701 'N', 'PA',pts.acct_source_code)) pts_source, /* Added posted_flag to derive PA if it is 'N' for bug 13254338*/
702 cd.cr_code_combination_id orig_cr_ccid
703 from pa_cost_distribution_lines_all cdl,
704 pa_expenditure_items_all exp,
705 pa_expenditure_items_all pe,
706 pa_cost_distribution_lines_all cd,
707 pa_transaction_sources pts
708 where exp.expenditure_item_id = cdl.expenditure_item_id
709 and pe.transaction_source = pts.transaction_source(+)
710 and cdl.request_id = g_data_set_id
711 and cdl.transfer_status_code = 'X'
712 and exp.adjusted_expenditure_item_id is not null
713 and exp.adjusted_expenditure_item_id = pe.expenditure_item_id
714 and pe.expenditure_item_id = cd.expenditure_item_id
715 and cdl.parent_line_num = cd.line_num
716 UNION ALL
717 select cdl.expenditure_item_id,
718 cdl.line_num,
719 exp.adjusted_expenditure_item_id,
720 exp.transferred_from_exp_item_id,
721 exp.transaction_source,
722 NVL(exp.historical_flag, 'Y') historical_flag,
723 cdl.parent_line_num,
724 decode(cdl.line_type,
725 'C', 'TBC',
726 'D', 'TBC',
727 exp.system_linkage_function) system_linkage_function,
728 cdl.line_num_reversed,
729 decode(cdl.line_type, 'R', 'R', 'B') line_type,
730 trunc(cdl.gl_date) gl_date,
731 exp.document_payment_id ,
732 exp.document_header_id ,
733 exp.document_distribution_id ,
734 NULL orig_historic,
735 cd.line_num orig_line_num,
736 cd.acct_source_code orig_acct_source,
737 cd.transfer_status_code orig_tsc,
738 cdl.system_reference5,
739 cdl.cr_code_combination_id,
740 decode(pts.predefined_flag,
741 NULL, 'PA',
742 'N', decode(pts.posted_flag,
743 'N', 'PA',
744 'EXT'),
745 decode(pts.posted_flag,
746 'N', 'PA',pts.acct_source_code)) pts_source, /* Added posted_flag to derive PA if it is 'N' for bug 13254338*/
747 cd.cr_code_combination_id orig_cr_ccid
748 from pa_cost_distribution_lines_all cdl,
749 pa_expenditure_items_all exp,
750 pa_cost_distribution_lines_all cd,
751 pa_transaction_sources pts
752 where exp.expenditure_item_id = cdl.expenditure_item_id
753 and exp.transaction_source = pts.transaction_source(+)
754 and cdl.request_id = g_data_set_id
755 and cdl.transfer_status_code = 'X'
756 and cdl.line_num_reversed is not null
757 and cdl.expenditure_item_id = cd.expenditure_item_id
758 and cdl.line_num_reversed = cd.line_num)
759 order by expenditure_item_id, line_num;
760 END IF;
761 END OPEN_COST_CUR;
762
763 -- procedure to populate SLA's array with info required for raising
764 -- accounting events
765
766 PROCEDURE POPULATE_COST_EVENT_ARRAY(x_rows_found out nocopy number) is
767 l_cursor CostCurType;
768 l_application_id NUMBER;
769 l_event_status_code VARCHAR2(1);
770 l_security_id_char_1 VARCHAR2(30);
771 l_acct_source varchar2(10);
772 t_security_id_char_1 TypeVarChar;
773 l_no_match varchar2(1);
774 l_cr_ccid number;
775 l_dr_slid number;
776 l_transfer_status_code varchar2(1);
777 l_transaction_source varchar2(30);
778
779 BEGIN
780
781 l_application_id := 275;
782 l_event_status_code := xla_events_pub_pkg.C_EVENT_UNPROCESSED;
783
784 t_tsc := t_tsc;
785 t_cr_ccid := t_cr_ccid;
786 t_sr5 := t_sr5;
787 t_raise_event := t_raise_event;
788
789 -- Populate
790 populate_acct_source;
791
792 x_rows_found := t_entity_id.count;
793
794 if x_rows_found = 0 then
795 goto no_rows_found;
796 end if;
797 /* Bug 13596243 added condition and t_acct_source(i) <> 'NA' so that events will not be raised for NA lines */
798 FOR i in t_entity_id.first..t_entity_id.last LOOP
799 l_no_match := 'Y';
800 if t_raise_event(i) is null and t_tsc(i) <> 'G' THEN
801 FOR k in i..t_entity_id.last LOOP
802 IF t_entity_id(k) = t_entity_id(i) AND
803 t_line_type(k) = t_line_type(i) AND
804 t_event_date(k) = t_event_date(i) AND
805 t_tsc(k) <> 'G' and i <> k and t_acct_source(i) <> 'NA' THEN
806 t_raise_event(i) := 'Y';
807 t_raise_event(k) := 'N';
808 l_no_match := 'N';
809 ELSE
810 l_no_match := 'Y';
811 END IF;
812 END LOOP;
813 END IF;
814 IF t_tsc(i) <> 'G' AND l_no_match = 'Y' AND t_raise_event(i) is null and t_acct_source(i) <> 'NA' THEN
815 t_raise_event(i) := 'Y';
816 END IF;
817 END LOOP;
818
819 FOR i in t_entity_id.first..t_entity_id.last LOOP
820 IF t_raise_event(i) = 'Y' THEN
821
822 IF g_calling_module = 'CC' THEN
823 t_event_type_code(i) :=
824 g_tab_event_type_code(g_tab_module(t_line_type(i)));
825 ELSIF g_calling_module in ('Cost', 'LAB', 'CC', 'SUPP') THEN
826 t_event_type_code(i) :=
827 g_tab_event_type_code(g_tab_module(t_txn_type(i)));
828 ELSE
829 t_event_type_code(i) := g_tab_event_type_code(g_calling_module);
830 END IF;
831 IF t_adjusted_item(i) is not null THEN -- change for reversing items.
832 t_event_type_code(i) := t_event_type_code(i) ||'_ADJ';
833 END IF;
834
835
836 tt_source_info(i).source_application_id := 275;
837 tt_source_info(i).application_id := 275;
838 tt_source_info(i).ledger_id := g_ledger_id;
839 tt_source_info(i).legal_entity_id := g_legal_entity_id;
840 tt_source_info(i).entity_type_code := 'EXPENDITURES';
841 tt_source_info(i).transaction_number := t_entity_id(i);
842 tt_source_info(i).source_id_int_1 := t_entity_id(i);
843 tt_security_info(i).security_id_int_1 := g_org_id;
844 END IF;
845 END LOOP;
846
847 <<NO_ROWS_FOUND>>
848 null;
849 END POPULATE_COST_EVENT_ARRAY;
850
851 -- procedure for stamping event id returned from SLA on distribution
852 -- lines and update transfer status code to 'A'
853 PROCEDURE TIEBACK_COST_EVENT IS
854 BEGIN
855 FOR i in t_entity_id.first..t_entity_id.last LOOP
856 IF t_event_id(i) is not null then
857 FOR k in t_entity_id.first..t_entity_id.last LOOP
858 IF t_entity_id(k) = t_entity_id(i) AND
859 t_line_type(k) = t_line_type(i) AND
860 t_event_date(k) = t_event_date(i) AND
861 t_tsc(k) <> 'G' THEN
862 t_event_id(k) := t_event_id(i);
863 END IF;
864 END LOOP;
865 END IF;
866 END LOOP;
867
868 IF g_calling_module in ('Cost', 'LAB', 'USG', 'INV', 'WIP',
869 'BTC', 'TBC', 'PJ', 'SUPP') THEN
870
871 FORALL i in t_entity_id.first..t_entity_id.last
872 update pa_cost_distribution_lines_all
873 set acct_event_id = decode(t_tsc(i), 'R', NULL, t_event_id(i)),
874 acct_source_code = decode(t_tsc(i), 'R', NULL, t_acct_source(i)),
875 transfer_status_code = decode(t_tsc(i), 'G', 'G', 'R', 'R', 'A'),
876 transfer_rejection_reason = decode(t_tsc(i),
877 'R',( SELECT meaning --bug 6033420
878 FROM pa_lookups
879 WHERE lookup_code = 'PA_XLA_NOT_FINAL_ACCT'
880 AND lookup_type = 'TRANSFER REJECTION REASON'),
881 transfer_rejection_reason),
882 transferred_date = trunc(sysdate),
883 system_reference5 = decode(t_tsc(i), 'R', system_reference5,
884 nvl(t_sr5(i), system_reference5)),
885 cr_code_combination_id = decode(t_tsc(i), 'R', cr_code_combination_id,
886 nvl(t_cr_ccid(i), cr_code_combination_id))
887 where expenditure_item_id = t_entity_id(i)
888 and transfer_status_code = 'X'
889 and TRUNC(gl_date) = t_event_date(i) --Bug 5081153
890 and line_num = t_line_num(i)
891 and line_type = decode(t_line_type(i), 'B', line_type, t_line_type(i))
892 and request_id = g_data_set_id;
893 ELSIF g_calling_module in ('CC', 'PC', 'BL') THEN
894 FORALL i in t_entity_id.first..t_entity_id.last
895 update pa_cc_dist_lines_all
896 set acct_event_id = t_event_id(i),
897 acct_source_code = t_acct_source(i),
898 transferred_date = trunc(sysdate),
899 transfer_status_code = decode(t_tsc(i), 'G', 'G', 'A')
900 where expenditure_item_id = t_entity_id(i)
901 and cc_dist_line_id = t_cc_dist_line_id(i)
902 and transfer_status_code = 'X'
903 and request_id = g_data_set_id
904 and line_type = t_line_type(i)
905 and gl_date = t_event_date(i);
906 END IF;
907
908 -- this is to handle cases where lines are not picked up by create events
909 -- because of some data inconsistency.
910
911 if g_calling_module in ('Cost', 'LAB', 'USG', 'INV', 'WIP', 'BTC', 'TBC',
912 'PJ', 'SUPP') then
913 update pa_cost_distribution_lines_all
914 set transfer_status_code = 'R',
915 transfer_rejection_reason = 'Create Events API did not pick this line'
916 where transfer_status_code = 'X'
917 and request_id = g_data_set_id;
918 elsif g_calling_module in ('CC', 'PC', 'BL') then
919 update pa_cc_dist_lines_all
920 set transfer_status_code = 'R',
921 transfer_rejection_code = 'Create Events API did not pick this line'
922 where transfer_status_code = 'X'
923 and request_id = g_data_set_id;
924 end if;
925
926 end tieback_cost_event;
927
928 PROCEDURE POPULATE_ACCT_SOURCE IS
929
930 x_cr_ccid number;
931 x_dr_sl_id number;
932 l_cursor CostCurType;
933 l_source_table varchar2(3);
934 l_check_parent_acct boolean := FALSE;
935 l_ccid number;
936 l_application_id number;
937 l_distribution_id_1 number;
938 l_distribution_id_2 number;
939 l_distribution_type varchar2(30);
940
941 FUNCTION check_plsql_table(p_orig_dist_line_id number)
942 return varchar2 IS
943 l_acct_source varchar2(10);
944 BEGIN
945 l_acct_source := NULL;
946 for j in t_entity_id.first..t_entity_id.last loop
947 if t_cc_dist_line_id(j) = p_orig_dist_line_id then
948 -- l_acct_source := t_acct_source(j); -- bug8754630
949 l_acct_source := t_orig_acct_source(j);
950 exit;
951 end if;
952 end loop;
953 return l_acct_source;
954 END CHECK_PLSQL_TABLE;
955
956 FUNCTION CHECK_PLSQL_TABLE(p_item number, p_line number)
957 return varchar2 IS
958 l_acct_source varchar2(10);
959 BEGIN
960 l_acct_source := NULL;
961 for j in t_entity_id.first..t_entity_id.last loop
962 if t_entity_id(j) = p_item and
963 t_line_num(j) = p_line then
964 --l_acct_source := t_acct_source(j); -- bug8754630
965 l_acct_source := t_orig_acct_source(j);
966 exit;
967 end if;
968 end loop;
969 return l_acct_source;
970 END CHECK_PLSQL_TABLE;
971
972 BEGIN
973 t_event_type_code := t_event_type_code;
974
975 open_cost_cur(l_cursor, g_calling_module);
976
977 if g_calling_module in ('Cost', 'LAB', 'SUPP', 'USG', 'BTC',
978 'PJ', 'WIP', 'INV', 'TBC') then
979 fetch l_cursor bulk collect into
980 t_entity_id,
981 t_line_num,
982 t_adjusted_item,
983 t_transferred_from_item,
984 t_transaction_source,
985 t_historical_flag, t_parent_line_num, t_txn_type,
986 t_line_num_reversed, t_line_type, t_event_date, t_payment_id,
987 t_header_id, t_distribution_id,
988 t_orig_historic, t_orig_line_num,
989 t_orig_acct_source, t_orig_tsc, t_sr5, t_cr_ccid,
990 t_pts_source, t_orig_cr_ccid;
991
992 elsif g_calling_module in ('CC', 'BL', 'PC') then
993 fetch l_cursor bulk collect into t_entity_id, t_cc_dist_line_id,
994 t_adjusted_item, t_transferred_from_item, t_historical_flag,
995 t_parent_dist_line_id, t_dist_line_id_reversed, t_line_type,
996 t_event_date, t_orig_historic, t_orig_dist_line_id,
997 t_orig_acct_source;
998 end if;
999
1000 close l_cursor;
1001
1002 if t_entity_id.count = 0 then
1003 return;
1004 end if;
1005
1006 if g_calling_module in ('CC', 'BL', 'PC') then
1007 for i in t_entity_id.first..t_entity_id.last loop
1008 t_tsc(i) := 'A';
1009 t_raise_event(i) := NULL;
1010 t_event_id(i) := NULL;
1011 t_event_type_code(i) := NULL;
1012
1013 if t_adjusted_item(i) is not null then
1014 t_acct_source(i) := nvl(t_orig_acct_source(i),
1015 check_plsql_table(t_parent_dist_line_id(i)));
1016 if t_acct_source(i) is null then
1017 t_acct_source(i) := 'UPG';
1018 end if;
1019 elsif t_dist_line_id_reversed(i) is null then
1020 t_acct_source(i):= 'PA';
1021 elsif t_dist_line_id_reversed(i) is not null then
1022 t_acct_source(i) := nvl(t_orig_acct_source(i),
1023 check_plsql_table(t_dist_line_id_reversed(i)));
1024 if t_acct_source(i) is null then
1025 t_acct_source(i) := 'UPG';
1026 end if;
1027 end if;
1028 end loop;
1029 elsif g_calling_module in ('Cost', 'LAB', 'SUPP', 'USG', 'BTC',
1030 'PJ', 'WIP', 'INV', 'TBC') then
1031
1032 for i in t_entity_id.first..t_entity_id.last loop
1033 t_get_data(i) := NULL;
1034 t_tsc(i) := 'A';
1035 t_raise_event(i) := NULL;
1036 t_event_id(i) := NULL;
1037 t_event_type_code(i) := NULL;
1038 --t_original_accted(i) := NULL;
1039
1040 --
1041 -- this is applicable to new, transferred and adjusting lines.
1042 -- for new item, acct source is whatever is selected
1043
1044
1045
1046 if t_line_num(i) = 1 then
1047
1048
1049
1050 if t_transferred_from_item(i) is null and
1051 t_adjusted_item(i) is null then
1052
1053 t_acct_source(i) := t_pts_source(i);
1054
1055 elsif t_adjusted_item(i) is not null then
1056
1057 -- 1. adjustment happened pre-R12, interfacing in R12
1058 -- 2. both parent and adjusting items in the same run
1059 -- 3. adjustment of R12 txn
1060 -- 4. adjustment of pre-R12 txn
1061
1062 -- adjustment in R12 or both parent and adjusting items in the same run
1063 t_acct_source(i) := nvl(t_orig_acct_source(i),
1064 check_plsql_table(t_adjusted_item(i),
1065 t_parent_line_num(i)));
1066
1067
1068 if t_acct_source(i) is not null then
1069 if (t_pts_source(i) = 'RCV' or t_pts_source(i) like 'AP%') then
1070
1071 t_get_data(i) := t_pts_source(i);
1072
1073 end if;
1074 else
1075
1076
1077 if t_orig_acct_source(i) is null then
1078
1079 t_acct_source(i) := t_pts_source(i);
1080
1081 if (t_pts_source(i) = 'RCV' or t_pts_source(i) like 'AP%') then
1082 t_get_data(i) := t_pts_source(i);
1083 end if;
1084
1085 if t_orig_historic(i) = 'Y' then --and (t_acct_source(i) not like 'AP%'
1086 --and t_acct_source(i) <> 'RCV') then
1087 t_acct_source(i) := 'UPG';
1088 end if;
1089
1090
1091 if t_acct_source(i) like 'AP%' and t_payment_id(i) is not null then
1092 t_acct_source(i) := 'AP_PAY';
1093 /* Bug 5374040 if t_transaction_source(i) = 'AP DISCOUNTS' then
1094 t_acct_source(i) := 'AP_INV';
1095 end if; */
1096 end if;
1097
1098 end if;
1099 end if; -- acct source not null
1100
1101 elsif t_transferred_from_item(i) is not null then
1102
1103 /* Bug 5367462
1104 There should be check adjusted_expenditure_item_id and then check for
1105 transferred_from_exp_item_id as reversal of already adjusted EI will have both fields updated
1106 */
1107 -- New item as a result of adjustment interfacing now
1108 t_acct_source(i) := 'PA';
1109
1110 if (t_pts_source(i) = 'RCV' or t_pts_source(i) like 'AP%') and
1111 t_line_type(i) = 'R' then
1112 t_get_data(i) := t_pts_source(i);
1113 end if;
1114 end if; -- adjusted_item not null
1115 end if; -- line_num = 1
1116 --
1117 -- for adjustment and recosting cases, if original and new lines are being
1118 -- interfaced together we want data to be ordered. Otherwise acct source
1119 -- derived could be wrong.
1120 --
1121 if t_line_num(i) > 1 then
1122 if t_adjusted_item(i) is not null and
1123 t_parent_line_num(i) is not null then
1124 -- this is applicable to C and D lines for adjusting item
1125 t_acct_source(i) := nvl(t_orig_acct_source(i),
1126 check_plsql_table(t_adjusted_item(i),
1127 t_parent_line_num(i)));
1128 -- adjustment of unupgraded txn
1129 if t_acct_source(i) is null then
1130 t_acct_source(i) := 'UPG';
1131 end if;
1132 elsif t_line_num_reversed(i) is null then
1133 t_acct_source(i) := 'PA';
1134 if (t_pts_source(i) = 'RCV' or t_pts_source(i) like 'AP%') and
1135 t_line_type(i) = 'R' then
1136 t_get_data(i) := t_pts_source(i);
1137 end if;
1138 elsif t_line_num_reversed(i) is not null then
1139 t_acct_source(i) := nvl(t_orig_acct_source(i),
1140 check_plsql_table(t_entity_id(i),
1141 t_line_num_reversed(i)));
1142
1143 if t_acct_source(i) is null then
1144 t_acct_source(i) := t_pts_source(i);
1145
1146 if (t_pts_source(i) = 'RCV' or t_pts_source(i) like 'AP%') and
1147 t_line_type(i) = 'R' then
1148 t_get_data(i) := t_pts_source(i);
1149 end if;
1150
1151 if t_historical_flag(i) = 'Y' then --and (t_acct_source(i) not like 'AP%'
1152 --and t_acct_source(i) <> 'RCV') then
1153 t_acct_source(i) := 'UPG';
1154 end if;
1155
1156 if t_acct_source(i) like 'AP%' and t_payment_id(i) is not null then
1157 t_acct_source(i) := 'AP_PAY';
1158 /* Bug 5475269
1159 if t_transaction_source(i) = 'AP DISCOUNTS' then
1160 t_acct_source(i) := 'AP_INV';
1161 end if;
1162 */
1163 end if;
1164
1165 end if;
1166 end if; -- line num reversed not null
1167 end if; -- line_num > 1
1168
1169 end loop;
1170 end if; -- g_calling_module
1171 ------------------------------------------------------------------------+
1172 -- In re-costing scenario, if all the lines are interfacing together and
1173 -- have the same gl_date then mark the original and reversing lines with
1174 -- transfer_status_code of 'G'.
1175 ------------------------------------------------------------------------+
1176
1177 if g_calling_module in ('Cost', 'LAB', 'SUPP', 'USG', 'BTC',
1178 'PJ', 'WIP', 'INV', 'TBC') then
1179 for i in t_entity_id.first..t_entity_id.last loop
1180 if t_line_num_reversed(i) is not null and t_tsc(i) <> 'G' then
1181 for k in t_entity_id.first..i loop
1182 if t_entity_id(k) = t_entity_id(i) and
1183 t_line_num(k) = t_line_num_reversed(i) and
1184 t_event_date(k) = t_event_date(i) and
1185 t_line_type(k) = t_line_type(i) and
1186 i <> k then
1187 t_tsc(k) := 'G';
1188 t_tsc(i) := 'G';
1189 end if;
1190 end loop;
1191 end if;
1192 end loop;
1193
1194 for i in t_entity_id.first..t_entity_id.last loop
1195 if t_get_data(i) is not null then
1196 if t_get_data(i) like 'AP%' then
1197 t_cr_ccid(i) := g_imp_cr_ccid;
1198 elsif t_get_data(i) = 'RCV' then
1199 t_cr_ccid(i) := g_imp_cr_ccid;
1200 end if;
1201 end if;
1202 end loop;
1203
1204 -- In case of adjustments, if the original doc is not accounted,
1205 -- adjustment in Projects will not raise an event. CDLs will be
1206 -- marked with rejection status.
1207
1208 for i in t_entity_id.first..t_entity_id.last loop
1209 if t_adjusted_item(i) is not null then
1210 if t_acct_source(i) = 'PA' and t_line_type(i) = 'R' then
1211 l_application_id := 275;
1212 l_distribution_id_1 := t_adjusted_item(i);
1213 l_distribution_id_2 := t_parent_line_num(i);
1214 l_distribution_type := t_line_type(i);
1215 l_check_parent_acct := FALSE; -- Bug 5105237
1216 end if;
1217 end if;
1218 if (t_adjusted_item(i) is not null or
1219 t_line_num_reversed(i) is not null) then
1220 if t_acct_source(i) = 'AP_INV' and t_adjusted_item(i) is not null then
1221 l_application_id := 200;
1222 l_distribution_id_1 := t_distribution_id(i);
1223 l_distribution_id_2 := NULL;
1224 l_distribution_type := 'AP_INV_DIST';
1225 l_check_parent_acct := TRUE;
1226 elsif t_acct_source(i) = 'AP_PAY' and t_adjusted_item(i) is not null then
1227 l_application_id := 200;
1228 l_distribution_id_1 := t_sr5(i);
1229 l_distribution_id_2 := NULL;
1230 l_distribution_type := 'AP_PMT_DIST';
1231 l_check_parent_acct := TRUE;
1232 elsif t_acct_source(i) = 'AP_APP' and t_adjusted_item(i) is not null then
1233 l_application_id := 200;
1234 l_distribution_id_1 := t_sr5(i);
1235 l_distribution_id_2 := NULL;
1236 l_distribution_type := 'AP_PREPAY';
1237 l_check_parent_acct := TRUE;
1238 elsif t_acct_source(i) = 'RCV' then -- for both adjusting item and reversing line
1239 l_application_id := 707;
1240 l_distribution_id_1 := t_sr5(i);
1241 l_distribution_id_2 := NULL;
1242 l_distribution_type := 'RCV_RECEIVING_SUB_LEDGER';
1243 l_check_parent_acct := TRUE;
1244 elsif t_acct_source(i) = 'INV' and t_line_num_reversed(i) is not null then
1245 l_application_id := 707;
1246 l_distribution_id_1 := t_sr5(i);
1247 l_distribution_id_2 := NULL;
1248 l_distribution_type := 'MTL_TRANSACTION_ACCOUNTS';
1249 l_check_parent_acct := TRUE;
1250 elsif t_acct_source(i) = 'WIP' and t_line_num_reversed(i) is not null then
1251 l_application_id := 707;
1252 l_distribution_id_1 := t_sr5(i);
1253 l_distribution_id_2 := NULL;
1254 l_distribution_type := 'WIP_TRANSACTION_ACCOUNTS';
1255 l_check_parent_acct := TRUE;
1256 end if;
1257
1258 end if;
1259
1260 if l_check_parent_acct then
1261 begin
1262 l_ccid := pa_xla_interface_pkg.Get_Sla_Ccid( l_application_id
1263 ,l_distribution_id_1
1264 ,l_distribution_id_2
1265 ,l_distribution_type
1266 ,'DEBIT'
1267 ,g_ledger_id
1268 );
1269 if l_ccid is null then
1270 t_tsc(i) := 'R';
1271 t_raise_event(i) := 'N';
1272 end if;
1273 l_check_parent_acct := FALSE;
1274 exception
1275 when no_data_found then
1276 t_tsc(i) := 'R';
1277 t_raise_event(i) := 'N';
1278 l_check_parent_acct := FALSE;
1279 when too_many_rows then
1280 l_check_parent_acct := FALSE;
1281 end;
1282 end if;
1283 end loop;
1284
1285 elsif g_calling_module in ('CC', 'BL', 'PC') then
1286 for i in t_entity_id.first..t_entity_id.last loop
1287 if t_dist_line_id_reversed(i) is not null and t_tsc(i) <> 'G' then
1288 for k in t_entity_id.first..i loop
1289 if t_entity_id(k) = t_entity_id(i) and
1290 t_cc_dist_line_id(k) = t_dist_line_id_reversed(i) and
1291 t_event_date(k) = t_event_date(i) and
1292 t_line_type(k) = t_line_type(i) and
1293 i <> k then
1294 t_tsc(k) := 'G';
1295 t_tsc(i) := 'G';
1296 end if;
1297 end loop;
1298 end if;
1299 end loop;
1300 end if; -- g_calling_module
1301
1302 END POPULATE_ACCT_SOURCE;
1303
1304 -- ---------------------------------------------------------------------------------+
1305 -- ENCUMBRANCE RELATED CHANGES STARTS HERE ...
1306 -- ---------------------------------------------------------------------------------+
1307
1308 -- ---------------------------------------------------------------------------+
1309 -- This procedure will derive the budget version id that needs to
1310 -- be reversed. Case: Re-baseline, Year-end or Check funds
1311 -- Out paramter: p_old_budget_version_id will be populated with this value
1312 -- Procedure will also derive the budget status code of the current budget
1313 -- Out parameter: p_curr_budget_status_code will be populated with this value
1314 -- ---------------------------------------------------------------------------+
1315 Procedure Get_bvid_to_reverse(p_budget_version_id IN NUMBER,
1316 p_curr_budget_status_code OUT NOCOPY VARCHAR2,
1317 p_old_budget_version_id OUT NOCOPY NUMBER)
1318 Is
1319 l_budget_type_code pa_budget_versions.budget_type_code%TYPE;
1320 l_project_id pa_budget_versions.project_id%TYPE;
1321 Begin
1322
1323 IF g_debug_mode = 'Y' THEN
1324 pa_debug.g_err_stage:= 'Get_bvid_to_reverse';
1325 pa_debug.write('create_events ' || g_debug_module,pa_debug.g_err_stage, g_debug_level5);
1326 END IF;
1327
1328 Select budget_type_code,budget_status_code,project_id
1329 into l_budget_type_code,p_curr_budget_status_code,l_project_id
1330 from pa_budget_versions
1331 where budget_version_id = p_budget_version_id;
1332
1333 If p_curr_budget_status_code in ('S','W') then
1334
1335 -- Draft version is used during "check funds", so get the current
1336 -- baselined version for reversal
1337 -- S: for submitted budget and W: for working (before submitted)
1338
1339 Begin
1340 Select budget_version_id
1341 into p_old_budget_version_id
1342 from pa_budget_versions
1343 where project_id = l_project_id
1344 and budget_type_code = l_budget_type_code
1345 and budget_status_code = 'B'
1346 and current_flag = 'Y';
1347 Exception
1348 When no_data_found then
1349 p_old_budget_version_id := null;
1350 -- first time submit ...
1351 End;
1352
1353 ElsIf p_curr_budget_status_code = 'B' then
1354 -- Baselined version is used during "baseline", so get the last
1355 -- baselined version for reversal
1356
1357 Select MAX(budget_version_id)
1358 into p_old_budget_version_id
1359 from pa_budget_versions
1360 where project_id = l_project_id
1361 and budget_type_code = l_budget_type_code
1362 and budget_status_code = 'B'
1363 and current_flag = 'N'
1364 and budget_version_id <> p_budget_version_id;
1365
1366 -- Note: If p_old_budget_version_id is null means first time baseline ..
1367 End If;
1368
1369
1370 IF g_debug_mode = 'Y' THEN
1371 pa_debug.g_err_stage:= 'Get_bvid_to_reverse'||':l_budget_type_code:'||l_budget_type_code||
1372 ':p_curr_budget_status_code:'||p_curr_budget_status_code;
1373 pa_debug.write('create_events ' || g_debug_module,pa_debug.g_err_stage, g_debug_level5);
1374
1375 pa_debug.g_err_stage:= 'Get_bvid_to_reverse'||':Last baselined version:'||p_old_budget_version_id;
1376 pa_debug.write('create_events ' || g_debug_module,pa_debug.g_err_stage, g_debug_level5);
1377 END IF;
1378
1379 End Get_bvid_to_reverse;
1380
1381 -- ---------------------------------------------------------------------------+
1382 -- This procedure will call XLA public API to delete draft
1383 -- events (if they were established earlier), called from "Reset_event_id"
1384 -- for budgets and "populate_enc_event_array" for funds check.
1385 -- Parameters:
1386 -- p_data_set_id1 : Current packet_id for Funds check and
1387 -- Last baselined version for Budgets
1388 -- p_data_set_id2 : Draft budget version for budgets
1389 -- p_calling_module : 'BUDGETS' or 'FUNDS_CHECK'
1390 -- p_events_to_delete: 'Y' if there are events to delete ..
1391 -- ---------------------------------------------------------------------------+
1392 Procedure Delete_xla_event_data(p_data_set_id1 IN NUMBER,
1393 p_data_set_id2 IN NUMBER,
1394 p_calling_module IN VARCHAR2,
1395 p_events_to_delete IN OUT NOCOPY VARCHAR2)
1396 Is
1397 Begin
1398
1399 IF g_debug_mode = 'Y' THEN
1400 pa_debug.g_err_stage:= 'Delete_xla_event_data'||'Module:['||p_calling_module
1401 ||']packet_id or last baselined version['||p_data_set_id1
1402 ||']draft budget version['||p_data_set_id2||']';
1403 pa_debug.write('create_events ' || g_debug_module,pa_debug.g_err_stage, g_debug_level5);
1404
1405 pa_debug.g_err_stage:= 'Delete_xla_event_data'||':Collect data for deleting reversing events';
1406 pa_debug.write('create_events ' || g_debug_module,pa_debug.g_err_stage, g_debug_level5);
1407 END IF;
1408
1409 If p_calling_module = 'BUDGETS' then
1410
1411 -- A. Select budget event data for clean up ..
1412 select txn.source_application_id,
1413 txn.application_id,
1414 txn.legal_entity_id,
1415 txn.ledger_id,
1416 txn.entity_code entity_type_code,
1417 txn.transaction_number,
1418 txn.source_id_int_1,
1419 txn.source_id_int_2,
1420 txn.source_id_int_3,
1421 txn.source_id_int_4,
1422 txn.source_id_char_1,
1423 txn.source_id_char_2,
1424 txn.source_id_char_3,
1425 txn.source_id_char_4,
1426 txn.security_id_int_1,
1427 evt.event_id
1428 BULK COLLECT INTO
1429 t_source_application_id,
1430 t_application_id,
1431 t_legal_entity_id,
1432 t_ledger_id,
1433 t_entity_type_code,
1434 t_transaction_number,
1435 t_source_id_int_1,
1436 t_source_id_int_2,
1437 t_source_id_int_3,
1438 t_source_id_int_4,
1439 t_source_id_char_1,
1440 t_source_id_char_2,
1441 t_source_id_char_3,
1442 t_source_id_char_4,
1443 t_security_org_id,
1444 t_source_event_id
1445 from xla_events evt,
1446 xla_transaction_entities txn
1447 where evt.entity_id = txn.entity_id
1448 and evt.event_id in
1449 (Select distinct bc_rev_event_id
1450 from pa_budget_lines
1451 where budget_version_id = p_data_set_id1
1452 and bc_rev_event_id is not null
1453 and p_data_set_id1 is not null
1454 UNION ALL
1455 Select distinct bc_event_id
1456 from pa_budget_lines
1457 where budget_version_id = p_data_set_id2
1458 and bc_event_id is not null
1459 and p_data_set_id2 is not null
1460 );
1461 -- 1st select for last baselined and 2nd for draft budget
1462
1463 Elsif p_calling_module = 'FUNDS_CHECK' then
1464
1465 -- A. Select event data for clean up ..
1466 select txn.source_application_id,
1467 txn.application_id,
1468 txn.legal_entity_id,
1469 txn.ledger_id,
1470 txn.entity_code entity_type_code,
1471 txn.transaction_number,
1472 txn.source_id_int_1,
1473 txn.source_id_int_2,
1474 txn.source_id_int_3,
1475 txn.source_id_int_4,
1476 txn.source_id_char_1,
1477 txn.source_id_char_2,
1478 txn.source_id_char_3,
1479 txn.source_id_char_4,
1480 txn.security_id_int_1,
1481 evt.event_id
1482 BULK COLLECT INTO
1483 t_source_application_id,
1484 t_application_id,
1485 t_legal_entity_id,
1486 t_ledger_id,
1487 t_entity_type_code,
1488 t_transaction_number,
1489 t_source_id_int_1,
1490 t_source_id_int_2,
1491 t_source_id_int_3,
1492 t_source_id_int_4,
1493 t_source_id_char_1,
1494 t_source_id_char_2,
1495 t_source_id_char_3,
1496 t_source_id_char_4,
1497 t_security_org_id,
1498 t_source_event_id
1499 from xla_events evt,
1500 xla_transaction_entities txn
1501 where evt.entity_id = txn.entity_id
1502 and evt.event_id in
1503 (select distinct pbc1.bc_event_id
1504 from pa_bc_packets pbc1
1505 where pbc1.packet_id <> p_data_set_id1
1506 and (pbc1.document_header_id,
1507 pbc1.document_distribution_id,
1508 pbc1.document_type) in
1509 (select pbc2.document_header_id,
1510 pbc2.document_distribution_id,
1511 pbc2.document_type
1512 from pa_bc_packets pbc2
1513 where pbc2.packet_id = p_data_set_id1
1514 and pbc2.status_code = 'I'
1515 and pbc2.ext_bdgt_flag = 'Y')
1516 and pbc1.status_code in ('S','F','T','R')
1517 and pbc1.bc_event_id is not null)
1518 and evt.event_status_code <> 'P';
1519
1520 End If; --If p_calling_module = 'BUDGETS' then
1521
1522 If t_source_event_id.exists(1) then
1523
1524 -- B. Initalize out variable ..
1525 p_events_to_delete := 'Y';
1526
1527 IF g_debug_mode = 'Y' THEN
1528 pa_debug.g_err_stage:= 'Delete_xla_event_data:'||t_source_event_id.COUNT||' event(s) to be deleted';
1529 pa_debug.write('create_events ' || g_debug_module,pa_debug.g_err_stage, g_debug_level5);
1530 END IF;
1531
1532 -- C. Assign the values to pl/sql array
1533
1534 for i in t_source_event_id.FIRST..t_source_event_id.LAST loop
1535
1536 tt_source_info(i).source_application_id := t_source_application_id(i);
1537 tt_source_info(i).application_id := t_application_id(i);
1538 tt_source_info(i).legal_entity_id := t_legal_entity_id(i);
1539 tt_source_info(i).ledger_id := t_ledger_id(i);
1540 tt_source_info(i).entity_type_code := t_entity_type_code(i);
1541 tt_source_info(i).transaction_number := t_transaction_number(i);
1542 tt_source_info(i).source_id_int_1 := t_source_id_int_1(i);
1543 tt_source_info(i).source_id_int_2 := t_source_id_int_2(i);
1544 tt_source_info(i).source_id_int_3 := t_source_id_int_3(i);
1545 tt_source_info(i).source_id_int_4 := t_source_id_int_4(i);
1546 tt_source_info(i).source_id_char_1 := t_source_id_char_1(i);
1547 tt_source_info(i).source_id_char_2 := t_source_id_char_2(i);
1548 tt_source_info(i).source_id_char_3 := t_source_id_char_3(i);
1549 tt_source_info(i).source_id_char_4 := t_source_id_char_4(i);
1550 tt_security_info(i).security_id_int_1 := t_security_org_id(i);
1551
1552 end loop;
1553
1554 -- D. Call XLA delete API
1555
1556 IF g_debug_mode = 'Y' THEN
1557 pa_debug.g_err_stage:= 'Delete_xla_event_data:'||'Call xla_events_pub_pkg.delete_event';
1558 pa_debug.write('create_events ' || g_debug_module,pa_debug.g_err_stage, g_debug_level5);
1559 END IF;
1560
1561 for i in t_source_event_id.FIRST..t_source_event_id.LAST loop
1562 XLA_EVENTS_PUB_PKG.DELETE_EVENT(p_event_source_info => tt_source_info(i)
1563 ,p_event_id => t_source_event_id(i)
1564 ,p_valuation_method => NULL
1565 ,p_security_context => tt_security_info(i));
1566 end loop;
1567
1568 -- E. Initalize pl/sql table
1569 IF g_debug_mode = 'Y' THEN
1570 pa_debug.g_err_stage:= 'Delete_xla_event_data:'||'initalize pl/sql table';
1571 pa_debug.write('create_events ' || g_debug_module,pa_debug.g_err_stage, g_debug_level5);
1572 END IF;
1573
1574 t_source_application_id.DELETE;
1575 t_application_id.DELETE;
1576 t_legal_entity_id.DELETE;
1577 t_ledger_id.DELETE;
1578 t_entity_type_code.DELETE;
1579 t_transaction_number.DELETE;
1580 t_source_id_int_1.DELETE;
1581 t_source_id_int_2.DELETE;
1582 t_source_id_int_3.DELETE;
1583 t_source_id_int_4.DELETE;
1584 t_source_id_char_1.DELETE;
1585 t_source_id_char_2.DELETE;
1586 t_source_id_char_3.DELETE;
1587 t_source_id_char_4.DELETE;
1588 t_security_org_id.DELETE;
1589 t_source_event_id.DELETE;
1590
1591 Else
1592
1593 IF g_debug_mode = 'Y' THEN
1594 pa_debug.g_err_stage:= 'Delete_xla_event_data: No event to delete';
1595 pa_debug.write('create_events ' || g_debug_module,pa_debug.g_err_stage, g_debug_level5);
1596 END IF;
1597
1598 End If;
1599
1600 End Delete_xla_event_data;
1601
1602 -- ---------------------------------------------------------------------------+
1603 -- This procedure will reset the reversing event_id on pa_budget_versions.
1604 -- This is required as re-baseline/Year-End can fail
1605 -- it will also reset event_id on the draft budget as user can execute
1606 -- "check funds" multiple times.
1607 -- ---------------------------------------------------------------------------+
1608 Procedure Reset_event_id (p_budget_version_id IN NUMBER,
1609 p_curr_budget_status_code IN VARCHAR2,
1610 p_old_budget_version_id IN NUMBER)
1611 Is
1612 -- l_events_to_delete Varchar2(1);
1613 Begin
1614
1615 -- ----------------------------------------------------------------------------------------- +
1616 IF g_debug_mode = 'Y' THEN
1617 pa_debug.g_err_stage:= 'Reset_event_id'||':Current Budget Status:'||p_curr_budget_status_code||
1618 ':Last baselined version:'||p_old_budget_version_id||
1619 ':Current budget version:'||p_budget_version_id;
1620 pa_debug.write('create_events ' || g_debug_module,pa_debug.g_err_stage, g_debug_level5);
1621
1622 pa_debug.g_err_stage:= 'Reset_event_id'||':Calling DELETE_XLA_EVENT_DATA';
1623 pa_debug.write('create_events ' || g_debug_module,pa_debug.g_err_stage, g_debug_level5);
1624 END IF;
1625 -- ----------------------------------------------------------------------------------------- +
1626 l_events_to_delete := 'N';
1627
1628 If p_curr_budget_status_code in ('S','W') then
1629 -- CF, CF after baseline (p_old will be NULL ..before 1st baseline)
1630
1631 DELETE_XLA_EVENT_DATA(p_data_set_id1 => p_old_budget_version_id,
1632 p_data_set_id2 => p_budget_version_id,
1633 p_calling_module => 'BUDGETS',
1634 p_events_to_delete => l_events_to_delete);
1635 Else
1636
1637 -- Baseline, re-baseline ..
1638 DELETE_XLA_EVENT_DATA(p_data_set_id1 => p_old_budget_version_id,
1639 p_data_set_id2 => NULL,
1640 p_calling_module => 'BUDGETS',
1641 p_events_to_delete => l_events_to_delete);
1642
1643 End If; --If p_curr_budget_status_code in ('S','W') then
1644
1645 -- p_data_set_id1: last baselined version
1646 -- p_data_set_id2: Current draft or latest budget version (will not have events)
1647
1648 If l_events_to_delete = 'Y' then
1649
1650 If p_curr_budget_status_code in ('S','W') then
1651 -- ----------------------------------------------------------------------------------------- +
1652 -- Update draft budget's event_id to null
1653 IF g_debug_mode = 'Y' THEN
1654 pa_debug.g_err_stage:= 'Reset_event_id'||': Reset bc_event_id for p_budget_version_id:'||p_budget_version_id;
1655 pa_debug.write('create_events ' || g_debug_module,pa_debug.g_err_stage, g_debug_level5);
1656 END IF;
1657 -- ----------------------------------------------------------------------------------------- +
1658
1659 Update pa_budget_lines
1660 set bc_event_id = NULL
1661 where budget_version_id = p_budget_version_id;
1662
1663 End If;
1664
1665 If p_old_budget_version_id is not null then
1666 -- ----------------------------------------------------------------------------------------- +
1667 IF g_debug_mode = 'Y' THEN
1668 pa_debug.g_err_stage:= 'Reset_event_id'||':Reset bc_rev_event_id for p_old_budget_version_id:'||p_old_budget_version_id;
1669 pa_debug.write('create_events ' || g_debug_module,pa_debug.g_err_stage, g_debug_level5);
1670 END IF;
1671 -- ----------------------------------------------------------------------------------------- +
1672
1673 Update pa_budget_lines
1674 set bc_rev_event_id = NULL
1675 where budget_version_id = p_old_budget_version_id;
1676
1677 End If;
1678
1679 End If; -- If l_events_to_delete = 'Y' then
1680
1681 End Reset_event_id;
1682
1683 /*
1684 -- ---------------------------------------------------------------------------+
1685 -- This is the procedure that will get called to populate xla_events_gt
1686 -- ---------------------------------------------------------------------------+
1687 Procedure Populate_enc_events_gt (p_Source_Id_Int1 IN TypeNum,
1688 p_event_type_code IN TypeVarChar,
1689 p_event_date IN TypeDate,
1690 p_calling_module IN Varchar2)
1691 IS
1692 l_application_id NUMBER;
1693 l_event_status_code VARCHAR2(1);
1694 l_user_id NUMBER;
1695
1696 Begin
1697
1698 IF g_debug_mode = 'Y' THEN
1699 pa_debug.g_err_stage:= 'Populate_enc_events_gt'||':Calling Module:'||p_calling_module||
1700 ' :Event count being inserted'||p_Source_Id_Int1.COUNT;
1701 pa_debug.write('create_events ' || g_debug_module,pa_debug.g_err_stage, g_debug_level5);
1702 END IF;
1703
1704 -- 1. Set variables
1705 l_application_id := 275;
1706 l_event_status_code := xla_events_pub_pkg.c_event_unprocessed;
1707 l_user_id := fnd_global.user_id;
1708 g_enc_create_events_flag := 'Y'; -- accessed in create_events
1709
1710 -- 2. Insert into xla_events_gt
1711 If p_calling_module = 'BUDGET' then
1712
1713 forall i in p_Source_Id_Int1.first..p_Source_Id_Int1.last
1714 insert into xla_events_gt ( LINE_NUMBER,
1715 ENTITY_ID,
1716 APPLICATION_ID,
1717 LEDGER_ID,
1718 LEGAL_ENTITY_ID,
1719 ENTITY_CODE,
1720 TRANSACTION_NUMBER,
1721 SOURCE_ID_INT_1,
1722 SOURCE_ID_INT_2,
1723 SOURCE_ID_INT_3,
1724 SOURCE_ID_INT_4,
1725 SOURCE_ID_CHAR_1,
1726 SOURCE_ID_CHAR_2,
1727 SOURCE_ID_CHAR_3,
1728 SOURCE_ID_CHAR_4,
1729 EVENT_ID,
1730 EVENT_CLASS_CODE,
1731 EVENT_TYPE_CODE,
1732 EVENT_NUMBER,
1733 EVENT_DATE,
1734 EVENT_STATUS_CODE,
1735 PROCESS_STATUS_CODE,
1736 EVENT_CREATED_BY,
1737 REFERENCE_NUM_1,
1738 REFERENCE_NUM_2,
1739 REFERENCE_NUM_3,
1740 REFERENCE_NUM_4,
1741 REFERENCE_CHAR_1,
1742 REFERENCE_CHAR_2,
1743 REFERENCE_CHAR_3,
1744 REFERENCE_CHAR_4,
1745 REFERENCE_DATE_1,
1746 REFERENCE_DATE_2,
1747 REFERENCE_DATE_3,
1748 REFERENCE_DATE_4,
1749 VALUATION_METHOD,
1750 SECURITY_ID_INT_1,
1751 SECURITY_ID_INT_2,
1752 SECURITY_ID_INT_3,
1753 SECURITY_ID_CHAR_1,
1754 SECURITY_ID_CHAR_2,
1755 SECURITY_ID_CHAR_3,
1756 ON_HOLD_FLAG,
1757 TRANSACTION_DATE,
1758 BUDGETARY_CONTROL_FLAG)
1759 values (NULL, -- line number
1760 NULL, -- entity_id
1761 l_application_id, -- application id
1762 g_ledger_id, -- ledger id (set in init)
1763 NULL, -- legal entity id
1764 g_entity_code, -- entity_code (set in init)
1765 NULL, -- transaction num
1766 p_Source_Id_Int1(i), -- source_id_int_1
1767 NULL, -- source_id_int_2
1768 NULL, -- source_id_int_3
1769 NULL, -- source_id_int_4
1770 NULL, -- source_id_char_1
1771 NULL, -- source_id_char_2
1772 NULL, -- source_id_char_3
1773 NULL, -- source_id_char_4
1774 NULL, -- event_id
1775 NULL, -- EVENT_CLASS_CODE
1776 g_event_type_code, -- EVENT_TYPE_CODE
1777 NULL, -- EVENT_NUMBER
1778 p_event_date(i), -- EVENT_DATE
1779 l_event_status_code, -- EVENT_STATUS_CODE
1780 NULL, -- PROCESS_STATUS_CODE
1781 l_user_id, -- EVENT_CREATED_BY
1782 NULL, -- REFERENCE_NUM_1
1783 NULL, -- REFERENCE_NUM_2
1784 NULL, -- REFERENCE_NUM_3
1785 NULL, -- REFERENCE_NUM_4
1786 NULL, -- REFERENCE_CHAR_1
1787 NULL, -- REFERENCE_CHAR_2
1788 NULL, -- REFERENCE_CHAR_3
1789 NULL, -- REFERENCE_CHAR_4
1790 NULL, -- REFERENCE_DATE_1
1791 NULL, -- REFERENCE_DATE_2
1792 NULL, -- REFERENCE_DATE_3
1793 NULL, -- REFERENCE_DATE_4
1794 NULL, -- VALUATION_METHOD
1795 g_project_org_id, -- SECURITY_ID_INT_1
1796 NULL, -- SECURITY_ID_INT_2
1797 NULL, -- SECURITY_ID_INT_3
1798 NULL, -- SECURITY_ID_CHAR_1
1799 NULL, -- SECURITY_ID_CHAR_2
1800 NULL, -- SECURITY_ID_CHAR_3
1801 NULL, -- ON_HOLD_FLAG
1802 NULL, -- TRANSACTION_DATE
1803 'Y'); -- BUDGETARY_CONTROL_FLAG
1804
1805 End If; -- If p_calling_module = 'BUDGET' then
1806
1807 End Populate_enc_events_gt;
1808 */
1809 -- ---------------------------------------------------------------------------+
1810 -- This procedure will select/identify encumbrance data (for budget baseline,
1811 -- budget check funds,Year-end), Fund check - AP/PO/REQ, Interface - BTC/TBC
1812 -- and populate xla_events_gt
1813 -- ---------------------------------------------------------------------------+
1814 Procedure Populate_enc_event_array
1815 IS
1816
1817 l_limit number(3);
1818 l_counter number(3);
1819 l_bvid_to_reverse pa_budget_versions.budget_version_id%type;
1820 l_curr_budget_status_code pa_budget_versions.budget_status_code%type;
1821 l_burden_method VARCHAR2(10);
1822
1823 -- Following variables used to build transaction number for budget
1824 -- Transaction number: 'project number' - 'budget type' - 'budget version number'
1825
1826 l_budget_type pa_budget_types.budget_type%type;
1827 l_project_number pa_projects_all.segment1%type;
1828 l_budget_version_number pa_budget_versions.version_number%type;
1829 l_rev_budget_version_number pa_budget_versions.version_number%type;
1830
1831 Begin
1832
1833 IF g_debug_mode = 'Y' THEN
1834 pa_debug.g_err_stage:= 'Populate_enc_event_array';
1835 pa_debug.write('create_events ' || g_debug_module,pa_debug.g_err_stage, g_debug_level5);
1836 END IF;
1837
1838 -- -----------------------------------------------------------------------------------+
1839 -- 1. Set variables
1840 -- -----------------------------------------------------------------------------------+
1841 l_limit := 500; -- Set l_limit for bulk processing
1842 l_counter := 0;
1843 g_enc_create_events_flag := 'N';
1844
1845 -- -----------------------------------------------------------------------------------+
1846 -- 2. Derive Event type code (for budgets)
1847 -- -----------------------------------------------------------------------------------+
1848 If g_calling_module in ('COST_BUDGET','CC_BUDGET','REVENUE_BUDGET') then
1849 g_event_type_code := 'BUDGET_BASELINE';
1850 Elsif g_calling_module in ('COST_BUDGET_YEAR_END','CC_BUDGET_YEAR_END') then
1851 g_event_type_code := 'BGT_YR_END_ROLLOVER';
1852 End If;
1853
1854 -- -----------------------------------------------------------------------------------+
1855 -- 3. Budget specific processing
1856 -- -----------------------------------------------------------------------------------+
1857 If g_entity_code = 'BUDGETS' then
1858
1859 -- 3A. Get current budget status code and also the budget version to reverse
1860 IF g_debug_mode = 'Y' THEN
1861 pa_debug.g_err_stage:= 'Populate_enc_event_array'||':Budget - Get_bvid_to_reverse';
1862 pa_debug.write('create_events ' || g_debug_module,pa_debug.g_err_stage, g_debug_level5);
1863 END IF;
1864
1865 Get_bvid_to_reverse(p_budget_version_id => g_data_set_id,
1866 p_curr_budget_status_code => l_curr_budget_status_code,
1867 p_old_budget_version_id => l_bvid_to_reverse);
1868
1869 -- 3B. Re-set event id (check funds/re-baseline failure)
1870 IF g_debug_mode = 'Y' THEN
1871 pa_debug.g_err_stage:= 'Populate_enc_event_array'||':Budget - Reset_event_id';
1872 pa_debug.write('create_events ' || g_debug_module,pa_debug.g_err_stage, g_debug_level5);
1873 END IF;
1874
1875 Reset_event_id (p_budget_version_id => g_data_set_id,
1876 p_curr_budget_status_code => l_curr_budget_status_code,
1877 p_old_budget_version_id => l_bvid_to_reverse);
1878
1879 -- 3C. Get org_id for the project
1880 IF g_debug_mode = 'Y' THEN
1881 pa_debug.g_err_stage:= 'Populate_enc_event_array'||':Budget - Get org_id';
1882 pa_debug.write('create_events ' || g_debug_module,pa_debug.g_err_stage, g_debug_level5);
1883 END IF;
1884
1885 select pp.org_id,pp.segment1,
1886 pbv.version_number,pbt.budget_type
1887 into g_project_org_id,l_project_number,
1888 l_budget_version_number,l_budget_type
1889 from pa_projects_all pp,
1890 pa_budget_versions pbv,
1891 pa_budget_types pbt
1892 where pbv.budget_version_id = g_data_set_id
1893 and pp.project_id = pbv.project_id
1894 and pbt.budget_type_code = pbv.budget_type_code;
1895
1896 If l_bvid_to_reverse is not null then
1897 select pbv.version_number
1898 into l_rev_budget_version_number
1899 from pa_budget_versions pbv
1900 where pbv.budget_version_id = l_bvid_to_reverse;
1901 End If;
1902
1903 -- 3D. Process budgets
1904 IF g_debug_mode = 'Y' THEN
1905 pa_debug.g_err_stage:= 'Populate_enc_event_array'||':Budget - Processing events::g_data_set_id,l_bvid_to_reverse::'||
1906 g_data_set_id||':'||l_bvid_to_reverse;
1907 pa_debug.write('create_events ' || g_debug_module,pa_debug.g_err_stage, g_debug_level5);
1908 END IF;
1909
1910 -- Note: g_data_set_id is for current version and l_bvid_to_reverse for reversing prev. baselined
1911
1912 select distinct budget_version_id, start_date
1913 BULK COLLECT INTO t_source_id_int_1,t_event_date
1914 from pa_budget_lines
1915 where budget_version_id in (g_data_set_id,l_bvid_to_reverse);
1916
1917 If g_debug_mode = 'Y' THEN
1918
1919 pa_debug.g_err_stage:= 'Populate_enc_event_array'||':Distinct budget record count is:'||t_source_id_int_1.COUNT;
1920 pa_debug.write('create_events ' || g_debug_module,pa_debug.g_err_stage, g_debug_level5);
1921
1922 End If;
1923
1924 for x in 1..t_source_id_int_1.COUNT loop
1925
1926 t_source_application_id(x) := 275;
1927 t_application_id(x) := 275;
1928 t_legal_entity_id(x) := to_number(null);
1929 t_ledger_id(x) := g_ledger_id;
1930 t_entity_type_code(x) := g_entity_code;
1931 t_source_id_int_2(x) := to_number(null);
1932 t_source_id_int_3(x) := to_number(null);
1933 t_source_id_int_4(x) := to_number(null);
1934 t_source_id_char_1(x) := null;
1935 t_source_id_char_2(x) := null;
1936 t_source_id_char_3(x) := null;
1937 t_source_id_char_4(x) := null;
1938 t_transaction_date(x) := null;
1939 t_security_org_id(x) := g_project_org_id;
1940 t_event_type_code(x) := g_event_type_code;
1941
1942 t_transaction_number(x) := l_project_number||' - '||l_budget_type||' - ';
1943
1944 If t_source_id_int_1(x) = g_data_set_id then
1945 t_transaction_number(x) := t_transaction_number(x) || l_budget_version_number;
1946 Elsif t_source_id_int_1(x) = l_bvid_to_reverse then
1947 t_transaction_number(x) := t_transaction_number(x) || l_rev_budget_version_number;
1948 End If;
1949
1950 end loop;
1951
1952 -- 3E. Set global variable
1953 g_bvid_to_reverse := l_bvid_to_reverse;
1954
1955 End If; -- Budget specific processing
1956
1957 -- -----------------------------------------------------------------------------------+
1958 -- 4. Funds Check specific processing
1959 -- -----------------------------------------------------------------------------------+
1960 If g_calling_module = 'FUNDS_CHECK' then
1961
1962 -- 4A: Clean up draft events created for the same document 'cause
1963 -- of Check funds action on the doc ..
1964 -- ----------------------------------------------------------------------------------------- +
1965 IF g_debug_mode = 'Y' THEN
1966 pa_debug.g_err_stage:= 'Populate_enc_event_array'||':Calling DELETE_XLA_EVENT_DATA';
1967 pa_debug.write('create_events ' || g_debug_module,pa_debug.g_err_stage, g_debug_level5);
1968 END IF;
1969 -- ----------------------------------------------------------------------------------------- +
1970
1971 l_events_to_delete := 'N';
1972
1973 DELETE_XLA_EVENT_DATA(p_data_set_id1 => g_data_set_id,
1974 p_data_set_id2 => NULL,
1975 p_calling_module => 'FUNDS_CHECK',
1976 p_events_to_delete => l_events_to_delete);
1977
1978 -- 4B. Get event related data ..
1979 IF g_debug_mode = 'Y' THEN
1980 pa_debug.g_err_stage:= 'Populate_enc_event_array'||':FC: Derive required data';
1981 pa_debug.write('create_events ' || g_debug_module,pa_debug.g_err_stage, g_debug_level5);
1982 END IF;
1983
1984 select txn.source_application_id,
1985 txn.application_id,
1986 txn.legal_entity_id,
1987 txn.ledger_id,
1988 txn.entity_code entity_type_code,
1989 txn.transaction_number,
1990 txn.source_id_int_1,
1991 txn.source_id_int_2,
1992 txn.source_id_int_3,
1993 txn.source_id_int_4,
1994 txn.source_id_char_1,
1995 txn.source_id_char_2,
1996 txn.source_id_char_3,
1997 txn.source_id_char_4,
1998 evt.event_date,
1999 evt.transaction_date,
2000 txn.security_id_int_1,
2001 evt.event_id,
2002 decode(evt.event_type_code,
2003 'REQ_RESERVED','REQ_BURDEN_RESERVED',
2004 'REQ_ADJUSTED','REQ_BURDEN_ADJUSTED',
2005 'REQ_UNRESERVED','REQ_BURDEN_UNRESERVED',
2006 'REQ_CANCELLED','REQ_BURDEN_CANCELLED',
2007 'REQ_FINAL_CLOSED','REQ_BURDEN_FINAL_CLOSED',
2008 'REQ_REJECTED','REQ_BURDEN_REJECTED',
2009 'REQ_RETURNED','REQ_BURDEN_RETURNED',
2010 'PO_PA_RESERVED','PO_BURDEN_RESERVED',
2011 'PO_PA_ADJUSTED','PO_BURDEN_ADJUSTED',
2012 'PO_PA_UNRESERVED','PO_BURDEN_UNRESERVED',
2013 'PO_PA_CANCELLED','PO_BURDEN_CANCELLED',
2014 'PO_PA_FINAL_CLOSED','PO_BURDEN_FINAL_CLOSED',
2015 'PO_PA_REJECTED','PO_BURDEN_REJECTED',
2016 'PO_PA_REOPEN_FINAL_MATCH','PO_BURDEN_REOPEN_FINAL_MATCH',
2017 'PO_PA_INV_CANCELLED','PO_BURDEN_INV_CANCELLED',
2018 'PO_PA_CR_MEMO_CANCELLED','PO_BURDEN_CR_MEMO_CANCELLED',
2019 'RELEASE_RESERVED','REL_BURDEN_RESERVED',
2020 'RELEASE_ADJUSTED','REL_BURDEN_ADJUSTED',
2021 'RELEASE_UNRESERVED','REL_BURDEN_UNRESERVED',
2022 'RELEASE_CANCELLED','REL_BURDEN_CANCELLED',
2023 'RELEASE_FINAL_CLOSED','REL_BURDEN_FINAL_CLOSED',
2024 'RELEASE_REJECTED','REL_BURDEN_REJECTED',
2025 'RELEASE_REOPEN_FINAL_CLOSED','REL_BURDEN_REOPEN_FINAL_CLOSED',
2026 'RELEASE_INV_CANCELLED','REL_BURDEN_INV_CANCELLED',
2027 'RELEASE_CR_MEMO_CANCELLED','REL_BURDEN_CR_MEMO_CANCELLED',
2028 'INVOICE VALIDATED','INVOICE_BURDEN_VALIDATED',
2029 'INVOICE CANCELLED','INVOICE_BURDEN_CANCELLED',
2030 'INVOICE ADJUSTED','INVOICE_BURDEN_ADJUSTED',
2031 'CREDIT MEMO VALIDATED','INVOICE_BURDEN_VALIDATED',
2032 'CREDIT MEMO CANCELLED','INVOICE_BURDEN_CANCELLED',
2033 'CREDIT MEMO ADJUSTED','INVOICE_BURDEN_ADJUSTED',
2034 'DEBIT MEMO VALIDATED','INVOICE_BURDEN_VALIDATED',
2035 'DEBIT MEMO CANCELLED','INVOICE_BURDEN_CANCELLED',
2036 'DEBIT MEMO ADJUSTED','INVOICE_BURDEN_ADJUSTED',
2037 'PREPAYMENT VALIDATED','PREPAYMENT_VALIDATED_BURDEN',
2038 'PREPAYMENT ADJUSTED','PREPAYMENT_ADJUSTED_BURDEN',
2039 'PREPAYMENT CANCELLED','PREPAYMENT_CANCELLED_BURDEN',
2040 'PREPAYMENT APPLIED','PREPAYMENT_APPLIED_BURDEN',
2041 'PREPAYMENT UNAPPLIED','PREPAYMENT_UNAPPLIED_BURDEN',
2042 'PREPAYMENT APPLICATION ADJ','PREPAY_APPLICATION_ADJ_BURDEN'
2043 ) event_type_code,
2044 evt.event_id
2045 BULK COLLECT INTO
2046 t_source_application_id,
2047 t_application_id,
2048 t_legal_entity_id,
2049 t_ledger_id,
2050 t_entity_type_code,
2051 t_transaction_number,
2052 t_source_id_int_1,
2053 t_source_id_int_2,
2054 t_source_id_int_3,
2055 t_source_id_int_4,
2056 t_source_id_char_1,
2057 t_source_id_char_2,
2058 t_source_id_char_3,
2059 t_source_id_char_4,
2060 t_event_date,
2061 t_transaction_date,
2062 t_security_org_id,
2063 t_source_event_id,
2064 t_event_type_code,
2065 t_reference_num_1
2066 from xla_events evt,
2067 xla_transaction_entities txn
2068 where evt.entity_id = txn.entity_id
2069 and evt.event_id in
2070 (select distinct source_event_id
2071 from pa_bc_packets
2072 where packet_id = g_data_set_id
2073 and status_code = 'I'
2074 and ext_bdgt_flag = 'Y'
2075 --and burden_method_code in ('S','D')
2076 --and bc_event_id is null
2077 );
2078
2079 End If; -- Funds Check processing
2080
2081 -- -----------------------------------------------------------------------------------+
2082 -- 5. Common Processing for FC and Budgeting
2083 -- -----------------------------------------------------------------------------------+
2084
2085 -- A. Populate Source pl/sql table ..
2086 IF g_debug_mode = 'Y' THEN
2087 pa_debug.g_err_stage:= 'Populate_enc_event_array'||':FC: Populate source pl/sql table';
2088 pa_debug.write('create_events ' || g_debug_module,pa_debug.g_err_stage, g_debug_level5);
2089 END IF;
2090
2091 If t_event_type_code.exists(1) then
2092
2093 for i in t_event_type_code.FIRST..t_event_type_code.LAST loop
2094
2095 tt_source_info(i).source_application_id := t_source_application_id(i);
2096 tt_source_info(i).application_id := t_application_id(i);
2097 tt_source_info(i).legal_entity_id := t_legal_entity_id(i);
2098 tt_source_info(i).ledger_id := t_ledger_id(i);
2099 tt_source_info(i).entity_type_code := t_entity_type_code(i);
2100 tt_source_info(i).transaction_number := t_transaction_number(i);
2101 tt_source_info(i).source_id_int_1 := t_source_id_int_1(i);
2102 tt_source_info(i).source_id_int_2 := t_source_id_int_2(i);
2103 tt_source_info(i).source_id_int_3 := t_source_id_int_3(i);
2104 tt_source_info(i).source_id_int_4 := t_source_id_int_4(i);
2105 tt_source_info(i).source_id_char_1 := t_source_id_char_1(i);
2106 tt_source_info(i).source_id_char_2 := t_source_id_char_2(i);
2107 tt_source_info(i).source_id_char_3 := t_source_id_char_3(i);
2108 tt_source_info(i).source_id_char_4 := t_source_id_char_4(i);
2109 tt_security_info(i).security_id_int_1 := t_security_org_id(i);
2110
2111 --If g_calling_module = 'FUNDS_CHECK' then
2112 -- tt_reference_info(i).reference_num_1 := t_reference_num_1(i);
2113 --Else
2114 -- tt_reference_info(i).reference_num_1 := '';
2115 --End If;
2116
2117 end loop;
2118
2119
2120 -- B. Delete temp pl/sql table ..
2121 IF g_debug_mode = 'Y' THEN
2122 pa_debug.g_err_stage:= 'Populate_enc_event_array'||':FC: Delete temp. pl/sql table';
2123 pa_debug.write('create_events ' || g_debug_module,pa_debug.g_err_stage, g_debug_level5);
2124 END IF;
2125
2126 t_source_application_id.DELETE;
2127 t_application_id.DELETE;
2128 t_legal_entity_id.DELETE;
2129 t_ledger_id.DELETE;
2130 t_entity_type_code.DELETE;
2131 t_transaction_number.DELETE;
2132 --t_source_id_int_1.DELETE; -- after tieback as its reqd. for budget tieback
2133 t_source_id_int_2.DELETE;
2134 t_source_id_int_3.DELETE;
2135 t_source_id_int_4.DELETE;
2136 t_source_id_char_1.DELETE;
2137 t_source_id_char_2.DELETE;
2138 t_source_id_char_3.DELETE;
2139 t_source_id_char_4.DELETE;
2140 t_security_org_id.DELETE;
2141
2142 --If g_calling_module = 'FUNDS_CHECK' then
2143 t_reference_num_1.DELETE;
2144 --End If;
2145
2146 End If; -- If t_event_type_code.exists(1) then
2147
2148 End Populate_enc_event_array;
2149
2150 -- -------------------------------------------------------------------+
2151 -- This procedure will update event_id on the source tables
2152 -- Called during budget processing ...
2153 -- -------------------------------------------------------------------+
2154 Procedure Tieback_budget_event
2155 Is
2156 Begin
2157 If t_event_id.EXISTS(1) then
2158 IF g_debug_mode = 'Y' THEN
2159 pa_debug.g_err_stage:= 'Tieback_budget_event: Process Start';
2160 pa_debug.write('create_events ' || g_debug_module,pa_debug.g_err_stage, g_debug_level5);
2161 END IF;
2162
2163 forall i in t_source_id_int_1.FIRST..t_source_id_int_1.LAST
2164 Update pa_budget_lines
2165 set bc_event_id = t_event_id(i)
2166 where budget_version_id = t_source_id_int_1(i)
2167 and start_date = t_event_date(i)
2168 and t_source_id_int_1(i) = g_data_set_id;
2169
2170 If g_bvid_to_reverse is NOT NULL then
2171
2172 forall i in t_source_id_int_1.FIRST..t_source_id_int_1.LAST
2173 Update pa_budget_lines
2174 set bc_rev_event_id = t_event_id(i)
2175 where budget_version_id = t_source_id_int_1(i)
2176 and start_date = t_event_date(i)
2177 and t_source_id_int_1(i) = g_bvid_to_reverse;
2178
2179 End If;
2180
2181 End If; --If t_event_id.EXISTS(1) then
2182
2183 End Tieback_budget_event;
2184
2185 -- -------------------------------------------------------------------+
2186 -- This procedure will update event_id on the source tables
2187 -- Called during commitment processing ...
2188 -- -------------------------------------------------------------------+
2189 Procedure Tieback_enc_event
2190 is
2191 PRAGMA AUTONOMOUS_TRANSACTION;
2192 Begin
2193
2194 If t_event_id.EXISTS(1) then
2195 IF g_debug_mode = 'Y' THEN
2196 pa_debug.g_err_stage:= 'Tieback_enc_event: Process FC';
2197 pa_debug.write('create_events ' || g_debug_module,pa_debug.g_err_stage, g_debug_level5);
2198 END IF;
2199
2200
2201 forall i in t_event_id.FIRST..t_event_id.LAST
2202 Update pa_bc_packets pb
2203 set pb.bc_event_id = t_event_id(i)
2204 where pb.packet_id = g_data_set_id
2205 and pb.source_event_id = t_source_event_id(i)
2206 and pb.status_code = 'I'
2207 and pb.ext_bdgt_flag = 'Y';
2208 --and pb.burden_method_code in ('S','D')
2209 --and pb.bc_event_id is null;
2210
2211 End If; --If t_event_id.EXISTS(1) then
2212
2213 COMMIT;
2214
2215 End tieback_enc_event;
2216 -- ---------------------------------------------------------------------------------+
2217 -- ENCUMBRANCE RELATED CHANGES ENDS HERE ...
2218 -- ---------------------------------------------------------------------------------+
2219
2220 /*
2221 * The following API is to be used by Post Accounting Programs.
2222 */
2223
2224 FUNCTION Get_Post_Acc_Sla_Ccid(
2225 P_Acct_Event_Id IN PA_Cost_Distribution_Lines_All.Acct_Event_Id%TYPE
2226 ,P_Transfer_Status_Code IN PA_Cost_Distribution_Lines_All.Transfer_Status_Code%TYPE
2227 ,P_Transaction_Source IN PA_Expenditure_Items_All.Transaction_Source%TYPE
2228 ,P_Historical_Flag IN PA_Expenditure_Items_All.Historical_Flag%TYPE
2229 ,P_Distribution_Id_1 IN XLA_Distribution_Links.Source_Distribution_Id_Num_1%TYPE
2230 ,P_Distribution_Id_2 IN XLA_Distribution_Links.Source_Distribution_Id_Num_2%TYPE
2231 ,P_Distribution_Type IN VARCHAR2
2232 ,P_Ccid IN PA_Cost_Distribution_Lines_All.Dr_Code_Combination_Id%TYPE DEFAULT NULL
2233 ,P_Account_Type IN VARCHAR2 DEFAULT 'DEBIT'
2234 ,P_Ledger_Id IN PA_Implementations_All.Set_Of_Books_Id%TYPE
2235 )
2236 RETURN NUMBER
2237 IS
2238 l_application_id XLA_Distribution_Links.Application_Id%TYPE;
2239 l_ccid PA_Cost_Distribution_Lines_All.Dr_Code_Combination_Id%TYPE;
2240 l_Source_Distribution_Id_Num_1 XLA_Distribution_Links.Source_Distribution_Id_Num_1%TYPE;
2241 l_Source_Distribution_Id_Num_2 XLA_Distribution_Links.Source_Distribution_Id_Num_2%TYPE;
2242 l_predefined_flag PA_Transaction_Sources.Predefined_Flag%TYPE;
2243 l_acct_source_code PA_Transaction_Sources.Acct_Source_Code%TYPE;
2244 l_Program_Code Xla_Post_Acct_Progs_b.Program_Code%TYPE;
2245 l_acct_event_id XLA_Events.Event_ID%TYPE;
2246 l_transfer_status_code PA_Cost_Distribution_Lines_All.transfer_status_code%TYPE;
2247 l_sys_ref5 PA_Cost_Distribution_Lines_All.system_reference5%TYPE;
2248 l_document_distribution_id PA_Expenditure_Items_All.document_distribution_id%TYPE;
2249 l_document_payment_id PA_Expenditure_Items_All.document_payment_id%TYPE;
2250 l_distribution_type Xla_Distribution_Links.Source_Distribution_Type%TYPE;
2251 l_payment_dist_lookup_code varchar2(15);
2252 BEGIN
2253
2254 l_predefined_flag := NULL;
2255 l_acct_source_code := NULL;
2256 l_sys_ref5 := NULL;
2257 l_acct_event_id := P_Acct_Event_Id;
2258 l_transfer_status_code := P_Transfer_status_code;
2259 l_ccid := P_ccid;
2260 l_document_distribution_id := NULL;
2261 l_document_payment_id := NULL;
2262 l_distribution_type := p_distribution_type;
2263
2264 -- Asset Generation capitalizes on 'I' lines. Since 'I' lines do not get accounted
2265 -- get the attributes required for the parent line. Parent Line Number is passed to
2266 -- p_distribution_id_2.
2267
2268 if p_distribution_type = 'I' then
2269 select cd.acct_event_id,
2270 cd.transfer_status_code,
2271 cd.dr_code_combination_id,
2272 cd.system_reference5, -- for RCV this holds rcv_subledger_id
2273 pe.document_distribution_id,
2274 pe.document_payment_id
2275 into l_acct_event_id,
2276 l_transfer_status_code,
2277 l_ccid,
2278 l_sys_ref5,
2279 l_document_distribution_id,
2280 l_document_payment_id
2281 from pa_cost_distribution_lines_all cd,
2282 pa_expenditure_items_all pe
2283 where cd.expenditure_item_id = pe.expenditure_item_id
2284 and cd.expenditure_item_id = p_distribution_id_1
2285 and cd.line_num = p_distribution_id_2;
2286
2287 l_distribution_type := 'R';
2288 end if;
2289
2290 IF (P_Transaction_Source IS NOT NULL)
2291 THEN
2292 SELECT ts.Predefined_Flag, ts.Acct_Source_Code
2293 INTO l_predefined_flag
2294 ,l_acct_source_code
2295 FROM PA_Transaction_Sources ts
2296 WHERE Transaction_Source = P_Transaction_Source;
2297
2298 -- AP_PAY is determined based on payment_id field being populated on
2299 -- EI. This is true for Case Basis Accounting and "AP Discounts" in
2300 -- Accrual basis accounting.
2301 if l_predefined_flag = 'Y' and l_acct_source_code like 'AP%' then
2302 if p_distribution_id_2 is not null then
2303 l_acct_source_code := 'AP_PAY';
2304 end if;
2305 /* bug 5374040 if P_Transaction_Source = 'AP DISCOUNTS' then
2306 l_acct_source_code := 'AP_INV';
2307 end if;
2308 */
2309 end if;
2310 END IF;
2311
2312 /*
2313 * Determine Application_Id.
2314 */
2315 IF ( l_Transfer_Status_Code = 'A' AND l_Acct_Event_Id IS NOT NULL )
2316 THEN
2317 l_application_id := 275;
2318 END IF;
2319 IF ( l_Transfer_Status_Code = 'A' AND l_Acct_Event_Id IS NULL )
2320 THEN
2321 RETURN l_ccid;
2322 END IF;
2323 IF ( l_Transfer_Status_Code = 'V' AND l_predefined_flag = 'N' )
2324 THEN
2325 RETURN l_Ccid;
2326 END IF;
2327 IF ( l_Transfer_Status_Code = 'V' AND P_Historical_Flag = 'Y' AND l_predefined_flag = 'Y' )
2328 THEN
2329 RETURN l_Ccid;
2330 END IF;
2331 IF ( l_Transfer_Status_Code = 'V' AND P_Historical_Flag = 'N' AND l_predefined_flag = 'Y' )
2332 THEN
2333 l_application_id :=
2334 CASE l_acct_source_code
2335 WHEN 'AP_INV' THEN 200
2336 WHEN 'AP_PAY' THEN 200
2337 WHEN 'AP_APP' THEN 200
2338 WHEN 'INV' THEN 707
2339 WHEN 'WIP' THEN 707
2340 WHEN 'RCV' THEN 707
2341 ELSE 0
2342 END;
2343 -- Source Distribution Type is determined based on the acct_source_code value.
2344 -- These values are used in the join with xla_distribution_links. This is
2345 -- overriding the input parameter for distribution_type and is required only
2346 -- in case of 'V' lines.
2347 if l_acct_source_code = 'AP_PAY' then
2348 l_distribution_type := 'AP_PMT_DIST';
2349 elsif l_acct_source_code = 'AP_INV' then
2350 l_distribution_type := 'AP_INV_DIST';
2351 elsif l_acct_source_code = 'AP_APP' then
2352 l_distribution_type := 'AP_PREPAY';
2353 elsif l_acct_source_code = 'RCV' then
2354 l_distribution_type := 'RCV_RECEIVING_SUB_LEDGER';
2355 elsif l_acct_source_code = 'INV' then
2356 l_distribution_type := 'MTL_TRANSACTION_ACCOUNTS';
2357 elsif l_acct_source_code = 'WIP' then
2358 l_distribution_type := 'WIP_TRANSACTION_ACCOUNTS';
2359 end if;
2360
2361 END IF;
2362
2363 /*
2364 * Determine the Distribution Identifiers.
2365 */
2366 IF ( l_application_id = 275 )
2367 THEN
2368 l_Source_Distribution_Id_Num_1 := P_Distribution_Id_1;
2369 l_Source_Distribution_Id_Num_2 := P_Distribution_Id_2;
2370 END IF;
2371
2372 IF ( l_acct_source_code = 'AP_INV' AND l_application_id = 200 )
2373 THEN
2374 l_Source_Distribution_Id_Num_1 := nvl(l_document_distribution_id, P_Distribution_Id_1);
2375 END IF;
2376
2377 IF ( l_acct_source_code = 'AP_PAY' AND l_application_id = 200 )
2378 THEN
2379 l_Source_Distribution_Id_Num_1 := nvl(l_sys_ref5, P_Distribution_Id_1);
2380 END IF;
2381
2382 IF ( l_acct_source_code IN ('INV', 'WIP') AND l_application_id = 707 )
2383 THEN
2384 IF ( P_Account_Type = 'DEBIT' )
2385 THEN
2386 l_Source_Distribution_Id_Num_1 := nvl(l_sys_ref5, P_Distribution_Id_1);
2387 ELSE
2388 l_Source_Distribution_Id_Num_1 := nvl(l_sys_ref5, P_Distribution_Id_1);
2389 END IF;
2390 END IF;
2391
2392 IF ( l_acct_source_code = 'RCV' AND l_application_id = 707 )
2393 THEN
2394 IF ( P_Account_Type = 'DEBIT' )
2395 THEN
2396 l_Source_Distribution_Id_Num_1 := nvl(l_sys_ref5, P_Distribution_Id_1);
2397 ELSE
2398 RETURN l_Ccid;
2399 END IF;
2400 END IF;
2401
2402 IF ( P_Account_Type = 'DEBIT' )
2403 THEN
2404 l_program_code := 'PA_POSTACCOUNTING_DEBIT';
2405 ELSE
2406 l_program_code := 'PA_POSTACCOUNTING_CREDIT';
2407 END IF;
2408
2409 SELECT /*+ leading( gl, xdl, ael, aeh, xaca, xad, xpap ) use_nl(xdl) index(xdl XLA_DISTRIBUTION_LINKS_N1) */ code_combination_id
2410 into l_Ccid
2411 FROM xla_distribution_links xdl,
2412 xla_ae_headers aeh,
2413 xla_ae_lines ael,
2414 xla_acct_class_assgns xaca,
2415 xla_assignment_defns_b xad,
2416 xla_post_acct_progs_b xpap,
2417 gl_ledgers gl
2418 WHERE xdl.source_distribution_id_num_1 = l_Source_Distribution_Id_Num_1
2419 AND NVL(xdl.source_distribution_id_num_2, -99) = to_number(NVL(l_Source_Distribution_Id_Num_2, -99)) /*Added to_number for bug 9407402*/
2420 AND xdl.source_distribution_type = l_distribution_type
2421 AND xdl.application_id = l_application_id
2422 AND xdl.ae_header_id = aeh.ae_header_id
2423 AND xdl.ae_line_num = ael.ae_line_num
2424 AND xdl.ae_header_id = ael.ae_header_id
2425 AND aeh.application_id = ael.application_id
2426 AND ael.application_id = xdl.application_id
2427 AND aeh.balance_type_code = 'A'
2428 AND aeh.accounting_entry_status_code = 'F'
2429 AND aeh.ledger_id = P_Ledger_Id
2430 AND ael.accounting_class_code = xaca.accounting_class_code
2431 AND xaca.program_code = xad.program_code
2432 AND xaca.program_owner_code = xad.program_owner_code
2433 AND xad.program_code = xpap.program_code
2434 AND xpap.program_owner_code = 'S'
2435 AND xaca.assignment_code = xad.assignment_code
2436 AND xaca.assignment_owner_code = xad.assignment_owner_code
2437 AND (xad.ledger_id IS NULL OR xad.ledger_id = P_Ledger_Id)
2438 AND xad.enabled_flag = 'Y'
2439 AND gl.ledger_id = P_Ledger_Id
2440 AND xpap.program_code = DECODE ( xaca.accounting_class_code ,
2441 'DISCOUNT' ,
2442 DECODE( gl.sla_ledger_cash_basis_flag,
2443 'Y', DECODE ( P_Account_Type,
2444 'CREDIT', 'PA_POSTACCOUNTING_DEBIT',
2445 'DEBIT', ''
2446 )
2447 ,l_program_code )
2448 , 'ACCRUAL' , /*Added this ACCRUAL and decode for bug12366428*/
2449 DECODE ( P_Account_Type,
2450 'CREDIT', '',
2451 'DEBIT', 'PA_POSTACCOUNTING_CREDIT'
2452 )
2453 ,l_program_code)
2454 /*
2455 Bug 5039683 For Cash Basis : Hard coded acc class 'Discount'
2456 and fetched from Debit side of post acc program 'PA_POSTACCOUNTING_DEBIT
2457 For R12+, this need be reverted out and create seperate post acc program
2458 for cash basis include 'Discount' in credit side and remove from Debit.
2459 */
2460 AND xpap.application_id = 275
2461 -- and rownum = 1; -- Added for bug8496530
2462 and ((xdl.accounting_line_type_code = 'S' and rownum = 1)
2463 OR rownum = 1); -- for bug 8898427
2464
2465 RETURN l_ccid;
2466 EXCEPTION
2467 WHEN OTHERS THEN
2468 RAISE;
2469 END Get_Post_Acc_Sla_Ccid;
2470
2471 /*
2472 * This function is used by the Extract Object View.
2473 */
2474 FUNCTION Get_Sla_Ccid( P_Application_Id NUMBER
2475 ,P_Distribution_Id_1 NUMBER
2476 ,P_Distribution_Id_2 NUMBER
2477 ,P_Distribution_Type XLA_Distribution_Links.SOURCE_DISTRIBUTION_TYPE%TYPE
2478 ,P_Account_Type VARCHAR2
2479 ,P_Ledger_Id NUMBER
2480 )
2481 RETURN NUMBER
2482 IS
2483 l_Program_Code Xla_Post_Acct_Progs_b.Program_Code%TYPE;
2484 l_ccid PA_Cost_Distribution_Lines_All.Dr_Code_Combination_Id%TYPE;
2485 BEGIN
2486 IF ( P_Account_Type = 'DEBIT' )
2487 THEN
2488 l_program_code := 'PA_POSTACCOUNTING_DEBIT';
2489 ELSE
2490 l_program_code := 'PA_POSTACCOUNTING_CREDIT';
2491 END IF;
2492
2493 SELECT /*+ leading( gl, xdl, ael, aeh, xaca, xad, xpap ) */ code_combination_id
2494 into l_ccid
2495 FROM XLA_Distribution_Links xdl,
2496 XLA_Ae_Headers aeh,
2497 XLA_Ae_Lines ael,
2498 XLA_Acct_Class_Assgns xaca,
2499 XLA_Assignment_Defns_b xad,
2500 XLA_Post_acct_Progs_b xpap,
2501 gl_ledgers gl
2502 WHERE xdl.source_Distribution_id_num_1 = P_Distribution_Id_1
2503 AND NVL(xdl.source_Distribution_id_num_2, -99) = to_number(NVL(P_Distribution_Id_2 , -99)) /*Added to_number for bug 9407402*/
2504 AND xdl.Source_Distribution_Type = P_Distribution_Type
2505 AND xdl.application_id = P_Application_Id
2506 AND xdl.ae_header_id = aeh.ae_header_id
2507 AND xdl.ae_line_num = ael.ae_line_num
2508 AND xdl.ae_header_id = ael.ae_header_id
2509 AND aeh.application_id = ael.application_id
2510 AND ael.application_id = xdl.application_id
2511 AND aeh.balance_type_code = 'A'
2512 AND aeh.accounting_entry_status_code = 'F'
2513 AND aeh.ledger_id = P_Ledger_Id
2514 AND ael.accounting_class_code = xaca.accounting_class_code
2515 AND xaca.program_code = xad.program_code
2516 AND xaca.program_owner_code = xad.program_owner_code
2517 AND xad.program_code = xpap.program_code
2518 AND xpap.program_owner_code = 'S'
2519 AND xaca.assignment_code = xad.assignment_code
2520 AND xaca.assignment_owner_code = xad.assignment_owner_code
2521 AND (xad.ledger_id IS NULL OR xad.ledger_id = P_Ledger_Id)
2522 AND xad.enabled_flag = 'Y'
2523 AND gl.ledger_id = P_Ledger_Id
2524 AND xpap.program_code = DECODE ( xaca.accounting_class_code ,
2525 'DISCOUNT' ,
2526 DECODE( gl.sla_ledger_cash_basis_flag,
2527 'Y', DECODE ( P_Account_Type,
2528 'CREDIT', 'PA_POSTACCOUNTING_DEBIT',
2529 'DEBIT', ''
2530 )
2531 ,l_program_code ),
2532 'ACCRUAL' , /*Added this ACCRUAL and decode for bug12366428*/
2533 DECODE ( P_Account_Type,
2534 'CREDIT', '',
2535 'DEBIT', 'PA_POSTACCOUNTING_CREDIT'
2536 )
2537 ,l_program_code)
2538 /*
2539 Bug 5039683 For Cash Basis : Hard coded acc class 'Discount'
2540 and fetched from Debit side of post acc program 'PA_POSTACCOUNTING_DEBIT
2541 For R12+, this need be reverted out and create seperate post acc program
2542 for cash basis include 'Discount' in credit side and remove from Debit.
2543 */
2544 AND xpap.application_id = 275
2545 --and rownum = 1; -- Added for bug8496530 -- commented for bug 8898427
2546 AND ((xdl.accounting_line_type_code = 'S' and rownum = 1)
2547 OR rownum = 1); -- bug 8898427
2548
2549 RETURN l_ccid;
2550
2551 EXCEPTION
2552 WHEN TOO_MANY_ROWS THEN
2553 RETURN NULL; -- Process gracefully, create accounting will skip the transaction.
2554 WHEN NO_DATA_FOUND THEN
2555 RETURN NULL; -- Process gracefully, create accounting will skip the transaction.
2556 WHEN OTHERS THEN
2557 RAISE;
2558 END Get_Sla_Ccid;
2559
2560
2561 END PA_XLA_INTERFACE_PKG;