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