[Home] [Help]
PACKAGE BODY: APPS.GMF_XLA_PKG
Source
1 PACKAGE BODY GMF_XLA_PKG AS
2 /* $Header: GMFXLAPB.pls 120.48.12020000.4 2012/12/24 16:14:08 pvkanetk ship $ */
3
4 G_CURRENT_RUNTIME_LEVEL NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
5 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
6 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
7 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
8 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
9 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
10 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
11 G_MODULE_NAME CONSTANT VARCHAR2(50) :='GMF.PLSQL.GMF_XLA_PKG.';
12
13
14 G_reference_no NUMBER;
15 G_legal_entity_id NUMBER;
16 G_ledger_id NUMBER;
17 G_accounting_mode VARCHAR2(1);
18
19 --
20 -- Process Categories defined in SLA
21 --
22 /*
23 G_inventory_transactions VARCHAR2(30) := 'INVENTORY_TRANSACTIONS';
24 G_production_transactions VARCHAR2(30) := 'PRODUCTION_TRANSACTIONS';
25 G_purchasing_transactions VARCHAR2(30) := 'PURCHASING_TRANSACTIONS';
26 G_order_management VARCHAR2(30) := 'ORDER_MANAGEMENT';
27 G_revaluation_transactions VARCHAR2(30) := 'REVALUATION_TRANSACTIONS';
28 */
29 G_inventory_transactions VARCHAR2(50) := 'Inventory Transactions';
30 G_production_transactions VARCHAR2(50) := 'Production Management Transactions';
31 G_purchasing_transactions VARCHAR2(50) := 'Purchasing Transactions';
32 G_order_management VARCHAR2(50) := 'Order Management Transactions';
33 G_revaluation_transactions VARCHAR2(50) := 'Inventory Revaluation';
34
35 --
36 -- Event Classes defined in SLA
37 --
38 G_batch_material VARCHAR2(30) := 'BATCH_MATERIAL';
39 G_batch_resource VARCHAR2(30) := 'BATCH_RESOURCE';
40 g_batch_close VARCHAR2(30) := 'BATCH_CLOSE';
41 G_costreval VARCHAR2(30) := 'COSTREVAL';
42 G_lotcostadj VARCHAR2(30) := 'LOTCOSTADJ';
43
44 --
45 -- SLA call back stages.
46 -- 1. Pre-Processing
47 -- 2. Extract
48 -- 3. Post-Processing
49 -- 4. Post-Accounting
50 --
51 G_pre_accounting VARCHAR2(30) := 'PRE_ACCOUNTING';
52 G_extract VARCHAR2(30) := 'EXTRACT';
53 G_post_processing VARCHAR2(30) := 'POST_PROCESSING';
54 G_post_accounting VARCHAR2(30) := 'POST_ACCOUNTING';
55
56 -- Initialize WHO columns
57 g_user_id NUMBER := FND_GLOBAL.USER_ID;
58 g_login_id NUMBER := FND_GLOBAL.LOGIN_ID;
59 g_prog_appl_id NUMBER := FND_GLOBAL.PROG_APPL_ID;
60 g_program_id NUMBER := FND_GLOBAL.CONC_PROGRAM_ID;
61 g_request_id NUMBER := FND_GLOBAL.CONC_REQUEST_ID;
62
63 g_log_msg FND_LOG_MESSAGES.message_text%TYPE;
64
65 g_version_printed BOOLEAN := FALSE;
66
67 /**
68 * Output log messages
69 */
70
71 PROCEDURE print_debug( pmsg IN VARCHAR2 )
72 IS
73 l_dt VARCHAR2(64);
74
75 BEGIN
76 l_dt := TO_CHAR(SYSDATE, 'yyyy-mm-dd hh24:mi:ss');
77 fnd_file.put_line(fnd_file.log,pmsg||' - '||l_dt);
78 END print_debug;
79
80
81 /*============================================================================
82 | PUNCTION - CREATE_EVENT
83 |
84 | DESCRIPTION
85 | Create accounting events for '<TRANSACTION>' type
86 |
87 | PRAMETERS
88 | p_event_type: Event type
89 | p_transaction_id: Unique Identifier
90 | P_event_date: Event date
91 | p_calling_sequence: Debug information
92 |
93 | RETURN TYPE: NUMBER
94 |
95 | KNOWN ISSUES:
96 |
97 | NOTES:
98 |
99 | MODIFICATION HISTORY
100 | Date Author Description of Change
101 |
102 *===========================================================================*/
103
104 FUNCTION create_event
105 (
106 p_reference_no IN NUMBER
107 , p_legal_entity_id IN NUMBER
108 , p_ledger_id IN NUMBER
109 , x_errbuf OUT NOCOPY VARCHAR2
110 )
111 RETURN NUMBER
112 IS
113
114 l_procedure_name CONSTANT VARCHAR2(100) := G_MODULE_NAME || 'CREATE_EVENT';
115 l_entity_type_code xla_events_int_gt.entity_code%TYPE;
116
117 n_rows_inserted NUMBER; /* into xla_events_int_gt table */
118
119 l_curr_calling_sequence VARCHAR2(4000);
120 n_hdrs number; -- xxxremove
121 n_lines number; -- xxxremove
122 n_events number; -- xxxremove
123
124 BEGIN
125
126
127 l_curr_calling_sequence := 'SLAPre-Processor' || '.GMF_XLA_PKG.CREATE_EVENT';
128 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
129
130 IF NOT g_version_printed THEN
131 g_log_msg := '$Header: GMFXLAPB.pls 120.48.12020000.4 2012/12/24 16:14:08 pvkanetk ship $';
132 print_debug(g_log_msg);
133 g_version_printed := TRUE;
134 END IF;
135
136 g_log_msg := 'Begin of procedure '|| l_procedure_name;
137 print_debug(g_log_msg);
138
139 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
140 THEN
141 FND_LOG.STRING(
142 log_level => g_level_procedure,
143 module => g_module_name || l_procedure_name,
144 message => g_log_msg
145 );
146 END IF;
147
148 -- umxla_extract_gt; /* xxxremove */
149
150 select count(*) into n_hdrs from gmf_xla_extract_headers_gt; -- xxxremove
151 select count(*) into n_lines from gmf_xla_extract_lines_gt; -- xxxremove
152
153 g_log_msg := n_hdrs || ' rows in hdrs_gt and ' || n_lines || ' rows in lines_gt';
154
155 print_debug(g_log_msg);
156 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
157 THEN
158 FND_LOG.STRING(
159 log_level => g_level_procedure,
160 module => g_module_name || l_procedure_name,
161 message => g_log_msg
162 );
163 END IF;
164
165 ---------------------------------------------------------------------
166 -- Set global variables
167 ---------------------------------------------------------------------
168 G_reference_no := p_reference_no;
169
170
171 ---------------------------------------------------------------------
172 -- Update extract hdr and lines global temp tables to set
173 -- entity code, event class and type.
174 ---------------------------------------------------------------------
175 g_log_msg := 'Calling proc GMF_XLA_PKG.update_extract_gt to set entity codes';
176
177 print_debug(g_log_msg);
178 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
179 THEN
180 FND_LOG.STRING(
181 log_level => g_level_procedure,
182 module => g_module_name || l_procedure_name,
183 message => g_log_msg
184 );
185 END IF;
186
187 update_extract_gt('SET_ENTITY_CODES', NULL);
188
189
190 ------------------------------------------------------------------------
191 -- Insert into xla_events_int_gt table. Create_Bulk_Events procedure
192 -- of SLA will pickup txn from here and create events. It will
193 -- also update event_id column in this table.
194 -- We'll stamp this event id on gmf_xla_extract_headers.event_id.
195 --
196 -- We will insert only transactions for which event needs to be created.
197 ------------------------------------------------------------------------
198
199 --
200 -- Bug 5668308: Added for loop to process multiple entity code. Multiple entity codes
201 -- will come in case of Purchasing and OM since all internal order txns are
202 -- mapped to Inventory Entity in SLA.
203 --
204 FOR i in (
205 SELECT distinct entity_code
206 FROM gmf_xla_extract_headers_gt
207 )
208 LOOP
209 g_log_msg := 'Calling function GMF_XLA_PKG.insert_into_xla_events_gt for entity ' || i.entity_code ||
210 ' (if entity is Inventory when process was submitted for PUR/OM, then we have some' ||
211 ' internal order transfers)';
212
213 print_debug(g_log_msg);
214 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
215 THEN
216 FND_LOG.STRING(
217 log_level => g_level_procedure,
218 module => g_module_name || l_procedure_name,
219 message => g_log_msg
220 );
221 END IF;
222
223 -- Bug 5668308: sending entity_code as parameter
224 n_rows_inserted := insert_into_xla_events_gt(i.entity_code);
225
226 ---------------------------------------------------------------------
227 -- Now create events by calling xla_events_pkg.create_bulk_events
228 -- only when there are any events to create.
229 ---------------------------------------------------------------------
230 IF n_rows_inserted = 0
231 THEN
232 g_log_msg := 'No events to create for entity ' || i.entity_code || '. User might be running the process more than once.' ||
233 ' We will still update extract headers and lines, since amounts might ' ||
234 ' differ from last run';
235
236 print_debug(g_log_msg);
237 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
238 THEN
239 FND_LOG.STRING(
240 log_level => g_level_procedure,
241 module => g_module_name || l_procedure_name,
242 message => g_log_msg
243 );
244 END IF;
245
246 ELSE
247 --
248 -- We've events to create
249 --
250
251 g_log_msg := 'Calling proc XLA_EVENTS_PKG.create_bulk_events for entity ' || i.entity_code;
252
253 print_debug(g_log_msg);
254 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
255 THEN
256 FND_LOG.STRING(
257 log_level => g_level_procedure,
258 module => g_module_name || l_procedure_name,
259 message => g_log_msg
260 );
261 END IF;
262
263
264 /* Bug 5668308: we cannot use this as we've more than one
265 * entity code for PUR and OM since they process internal
266 * orders, which are mapped to Inventory entity.
267 * Following SQL was causing incorrect entity being set on
268 * SLA event and hence unable to query the event.
269 SELECT entity_code
270 INTO l_entity_type_code
271 FROM gmf_xla_extract_headers_gt
272 WHERE rownum = 1
273 ;
274 */
275
276
277 xla_events_pub_pkg.create_bulk_events
278 (
279 p_source_application_id => NULL
280 , p_application_id => 555
281 , p_legal_entity_id => p_legal_entity_id
282 , p_ledger_id => p_ledger_id
283 -- , p_entity_type_code => l_entity_type_code Bug 5668308
284 , p_entity_type_code => i.entity_code
285 );
286
287 /*Bug 12931836 - Commented this (for performance improvement)
288 select count(*) into n_events from xla_events
289 where application_id = 555
290 and reference_num_1 = p_reference_no; -- xxxremove
291 */
292
293 g_log_msg := 'Completed bulk events creation. created ';
294
295 print_debug(g_log_msg);
296 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
297 THEN
298 FND_LOG.STRING(
299 log_level => g_level_procedure,
300 module => g_module_name || l_procedure_name,
301 message => g_log_msg
302 );
303 END IF;
304
305 END IF;
306
307
308 ---------------------------------------------------------------------
309 -- Update gmf_xla_extract_headers_gt to set event_id
310 -- generated by above call. No need to update if n_rows_inserted = 0
311 ---------------------------------------------------------------------
312 IF n_rows_inserted = 0
313 THEN
314 g_log_msg := 'No events were created for entity ' || i.entity_code ||'. So, no need to update extract_headers gt with event ids';
315
316 print_debug(g_log_msg);
317 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
318 THEN
319 FND_LOG.STRING(
320 log_level => g_level_procedure,
321 module => g_module_name || l_procedure_name,
322 message => g_log_msg
323 );
324 END IF;
325
326 ELSE
327 g_log_msg := 'Calling proc XLA_EVENTS_PKG.update_extract_gt to set event ids for entity ' || i.entity_code;
328
329 print_debug(g_log_msg);
330 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
331 THEN
332 FND_LOG.STRING(
333 log_level => g_level_procedure,
334 module => g_module_name || l_procedure_name,
335 message => g_log_msg
336 );
337 END IF;
338
339 -- Bug 5668308: sending entity_code as parameter
340 update_extract_gt('SET_EVENT_IDS', i.entity_code);
341
342 END IF;
343
344 DELETE FROM xla_events_int_gt; -- Cleanup for next run
345
346 END LOOP; -- Loop for each Entity (i.e., Source/Process Category)
347 /* Bug 5668308 */
348
349 ---------------------------------------------------------------------
350 -- Now insert/update gmf_xla_extract_headers to set event_id
351 ---------------------------------------------------------------------
352 g_log_msg := 'Calling proc update_extract_header';
353
354 print_debug(g_log_msg);
355 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
356 THEN
357 FND_LOG.STRING(
358 log_level => g_level_procedure,
359 module => g_module_name || l_procedure_name,
360 message => g_log_msg
361 );
362 END IF;
363
364 update_extract_headers_table;
365
366
367 ---------------------------------------------------------------------
368 -- Now insert/update gmf_xla_extract_headers to set event_id
369 ---------------------------------------------------------------------
370 g_log_msg := 'Calling proc XLA_EVENTS_PKG.update_extract_lines';
371
372 print_debug(g_log_msg);
373 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
374 THEN
375 FND_LOG.STRING(
376 log_level => g_level_procedure,
377 module => g_module_name || l_procedure_name,
378 message => g_log_msg
379 );
380 END IF;
381
382 update_extract_lines_table;
383
384
385 ---------------------------------------------------------------------
386 -- Now merge rows into gmf_transaction_valuation table.
387 ---------------------------------------------------------------------
388 g_log_msg := 'Calling proc XLA_EVENTS_PKG.merge_into_gtv';
389
390 print_debug(g_log_msg);
391 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
392 THEN
393 FND_LOG.STRING(
394 log_level => g_level_procedure,
395 module => g_module_name || l_procedure_name,
396 message => g_log_msg
397 );
398 END IF;
399
400 merge_into_gtv;
401
402
403 ---------------------------------------------------------------------
404 -- Clean-up...
405 --
406 -- Now delete from xla_events_int_gt table. This table should not contain
407 -- any rows for next time around. SLA expects this table to contain
408 -- only rows for which events has to be created.
409 -- Verify GT table definitions to decide whether to keep following
410 -- stmt or not.
411 ---------------------------------------------------------------------
412 g_log_msg := 'Events creation complete. Deleting ALL rows from table xla_events_int_gt';
413
414 print_debug(g_log_msg);
415 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
416 THEN
417 FND_LOG.STRING(
418 log_level => g_level_procedure,
419 module => g_module_name || l_procedure_name,
420 message => g_log_msg
421 );
422 END IF;
423
424 DELETE FROM xla_events_int_gt;
425
426
427 ---------------------------------------------------------------------
428 -- Clean-up...
429 --
430 -- gmf_xla_extract_headers/lines_gt tables too!
431 -- Verify GT table definitions to decide whether to keep following
432 -- stmts or not.
433 ---------------------------------------------------------------------
434 g_log_msg := 'Events creation complete. Deleting ALL rows from extract headers/lines gt tables';
435
436 print_debug(g_log_msg);
437 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
438 THEN
439 FND_LOG.STRING(
440 log_level => g_level_procedure,
441 module => g_module_name || l_procedure_name,
442 message => g_log_msg
443 );
444 END IF;
445
446 /* Bug 12647482. Following delete statements commented because these are transaction persistent tables. The delete
447 happens automatically when the data is commited after processing every 1000 records (profile: GL$COMMIT_COUNT). */
448
449 /*
450 DELETE FROM gmf_xla_extract_headers_gt;
451 DELETE FROM gmf_xla_extract_lines_gt;
452 */
453 ---------------------------------------------------------------------
454 -- All done!
455 ---------------------------------------------------------------------
456 g_log_msg := 'End of procedure '|| l_procedure_name;
457
458 print_debug(g_log_msg);
459 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
460 THEN
461 FND_LOG.STRING(
462 log_level => g_level_procedure,
463 module => g_module_name || l_procedure_name,
464 message => g_log_msg
465 );
466 END IF;
467
468 RETURN 0;
469
470 EXCEPTION
471 WHEN OTHERS
472 THEN
473 x_errbuf := substrb('gmf_xla_pkg.create_event. in when-others: ' ||
474 '; sqlcode/err: ' || to_char(sqlcode) || '-' || sqlerrm, 1, 240);
475
476 print_debug(x_errbuf);
477 IF (FND_LOG.LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL )
478 THEN
479 FND_LOG.STRING(
480 log_level => FND_LOG.LEVEL_EXCEPTION,
481 module => g_module_name || l_procedure_name,
482 message => x_errbuf
483 );
484 END IF;
485
486 g_log_msg := substrb('gmf_xla_pkg.create_event. in when-others (backtrace): ' ||
487 DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, 1, 240);
488
489 print_debug(g_log_msg);
490 IF (FND_LOG.LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL )
491 THEN
492 FND_LOG.STRING(
493 log_level => FND_LOG.LEVEL_EXCEPTION,
494 module => g_module_name || l_procedure_name,
495 message => g_log_msg
496 );
497 END IF;
498
499 RETURN -1;
500
501 END create_event;
502
503 PROCEDURE update_extract_gt (
504 p_what_to_update IN VARCHAR2,
505 p_entity_code IN VARCHAR2
506 )
507 IS
508
509 l_procedure_name CONSTANT VARCHAR2(100) := g_module_name || 'UPDATE_EXTRACT_GT';
510 l_entity_code VARCHAR2(100);
511 l_event_class_code VARCHAR2(100);
512 l_event_type_code VARCHAR2(100);
513
514 l_transaction_id BINARY_INTEGER;
515 l_transaction_source_type_id BINARY_INTEGER;
516 l_transaction_action_id BINARY_INTEGER;
517 l_source_document_id BINARY_INTEGER;
518 l_source_line_id BINARY_INTEGER;
519 l_transaction_type VARCHAR2(100);
520 l_ledger_id BINARY_INTEGER;
521 l_valuation_cost_type_id BINARY_INTEGER;
522 l_cnt BINARY_INTEGER;
523 l_cnt_hdr_msg BINARY_INTEGER := 1; /* Bug 12647482 */
524
525 BEGIN
526
527 g_log_msg := 'Begin of procedure '|| l_procedure_name;
528
529 print_debug(g_log_msg);
530 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
531 THEN
532 FND_LOG.STRING(
533 log_level => g_level_procedure,
534 module => g_module_name || l_procedure_name,
535 message => g_log_msg
536 );
537 END IF;
538
539 <<update_extract_hdr_gt>>
540 CASE p_what_to_update
541 WHEN 'SET_ENTITY_CODES'
542 THEN
543 --
544 -- setting entity_code, event_class_code, event_type_code
545 --
546 g_log_msg := 'Setting entity_code, event_class_code, and event_type_code now.';
547
548 print_debug(g_log_msg);
549 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
550 THEN
551 FND_LOG.STRING(
552 log_level => g_level_statement,
553 module => g_module_name || l_procedure_name,
554 message => g_log_msg
555 );
556 END IF;
557
558
559 update gmf_xla_extract_headers_gt ehgt
560 set (entity_code, event_class_code, event_type_code) =
561 (SELECT entity_code, event_class_code, event_type_code
562 FROM gmf_xla_event_model em
563 WHERE
564 ( em.transaction_source_type_id = ehgt.transaction_source_type_id
565 AND em.transaction_action_id = ehgt.transaction_action_id
566 AND nvl(em.organization, 'x') = nvl(ehgt.organization, 'x')
567 AND nvl(em.transfer_type, 'x') = nvl(ehgt.transfer_type, 'x')
568 AND nvl(em.transfer_price_flag, 'x') = nvl(ehgt.transfer_price_flag, 'x')
569 AND nvl(em.transaction_type, 'x') = nvl(ehgt.transaction_type, 'x')
570 AND nvl(em.fob_point, 99) = nvl(ehgt.fob_point, 99)
571 AND nvl(ehgt.transaction_type, 'x') <> 'RESOURCE_TRANSACTIONS'
572 )
573 OR
574 (
575 nvl(ehgt.transaction_type, 'x') = em.transaction_type -- 'RESOURCE_TRANSACTIONS'
576 )
577 )
578 ;
579
580 g_log_msg := sql%rowcount || ' rows updated';
581
582 print_debug(g_log_msg);
583 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
584 THEN
585 FND_LOG.STRING(
586 log_level => g_level_statement,
587 module => g_module_name || l_procedure_name,
588 message => g_log_msg
589 );
590 END IF;
591
592 -- /*
593 BEGIN
594
595 FOR i in (
596 SELECT transaction_id, transaction_source_type_id, transaction_action_id,
597 source_document_id, source_line_id,
598 nvl(organization, 'x') as organization,
599 nvl(transfer_type, 'x') as transfer_type,
600 nvl(transfer_price_flag, 'x') as transfer_price_flag,
601 nvl(fob_point, 99) as fob_point,
602 nvl(transaction_type, 'x') as transaction_type
603 FROM gmf_xla_extract_headers_gt
604 WHERE entity_code IS NULL or event_class_code IS NULL OR event_type_code IS NULL
605 )
606 LOOP
607
608 IF l_cnt_hdr_msg > 0 THEN /* Bug 12647482 */
609 g_log_msg := 'Could not set event type for ' ||
610 'TxnID/SrcTyp/Act/Org/XferType/TPflag/FOB/TxnType/SrcDoc/SrcLine'
611 ;
612 print_debug(g_log_msg);
613 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
614 THEN
615 FND_LOG.STRING(
616 log_level => g_level_statement,
617 module => g_module_name || l_procedure_name,
618 message => g_log_msg
619 );
620 END IF;
621 END IF;
622
623 l_cnt_hdr_msg := -1;
624
625 g_log_msg := i.transaction_id || '/' ||
626 i.transaction_source_type_id || '/' || i.transaction_action_id || '/' ||
627 i.organization || '/' || i.transfer_type || '/' ||
628 i.transfer_price_flag || '/' || i.fob_point || '/' ||
629 i.transaction_type || '/' ||
630 i.source_document_id || '/' ||
631 i.source_line_id
632 ;
633
634 print_debug(g_log_msg);
635 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
636 THEN
637 FND_LOG.STRING(
638 log_level => g_level_statement,
639 module => g_module_name || l_procedure_name,
640 message => g_log_msg
641 );
642 END IF;
643 END LOOP;
644
645 IF l_cnt_hdr_msg < 0 THEN /* Bug 12647482 */
646 DELETE FROM gmf_xla_extract_lines_gt
647 where header_id in (SELECT header_id
648 FROM gmf_xla_extract_headers_gt
649 WHERE entity_code IS NULL or event_class_code IS NULL OR event_type_code IS NULL)
650 ;
651 DELETE FROM gmf_xla_extract_headers_gt
652 WHERE entity_code IS NULL or event_class_code IS NULL OR event_type_code IS NULL
653 ;
654 END IF;
655
656 EXCEPTION
657 WHEN NO_DATA_FOUND
658 THEN
659 NULL;
660 END;
661 --*/
662
663
664
665 WHEN 'SET_EVENT_IDS'
666 THEN
667 --
668 -- setting event_id
669 --
670 g_log_msg := 'Setting event_id on extract_headers_gt for entity ' || p_entity_code;
671
672 print_debug(g_log_msg);
673 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
674 THEN
675 FND_LOG.STRING(
676 log_level => g_level_statement,
677 module => g_module_name || l_procedure_name,
678 message => g_log_msg
679 );
680 END IF;
681
682 UPDATE gmf_xla_extract_headers_gt ehgt
683 SET event_id = (SELECT event_id
684 FROM xla_events_int_gt egt
685 WHERE egt.source_id_int_1 = ehgt.transaction_id
686 AND egt.source_id_int_2 = ehgt.ledger_id
687 AND egt.source_id_int_3 = ehgt.valuation_cost_type_id
688 /* AND egt.source_id_int_4 = ehgt.transaction_source_type_id INVCONV */
689 AND egt.source_id_char_1 = ehgt.event_class_code
690 /* AND nvl(egt.source_id_char_2, 'x') = nvl(ehgt.lot_number, 'x') INVCONV */
691 )
692 WHERE entity_code = p_entity_code
693 ;
694
695
696
697 g_log_msg := sql%rowcount || ' rows updated';
698
699 print_debug(g_log_msg);
700 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
701 THEN
702 FND_LOG.STRING(
703 log_level => g_level_statement,
704 module => g_module_name || l_procedure_name,
705 message => g_log_msg
706 );
707 END IF;
708
709
710 END CASE update_extract_hdr_gt;
711
712
713 g_log_msg := 'End of procedure '|| l_procedure_name;
714
715 print_debug(g_log_msg);
716 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
717 THEN
718 FND_LOG.STRING(
719 log_level => g_level_procedure,
720 module => g_module_name || l_procedure_name,
721 message => g_log_msg
722 );
723 END IF;
724
725 END update_extract_gt;
726
727 /* Bug 5668308: Added p_entity_code parameter */
728 FUNCTION insert_into_xla_events_gt (p_entity_code IN VARCHAR2)
729 RETURN NUMBER
730 IS
731
732 l_procedure_name CONSTANT VARCHAR2(100) := g_module_name || 'INSERT_INTO_XLA_EVENTS_GT';
733 n_rows_inserted NUMBER;
734
735 BEGIN
736
737 g_log_msg := 'Begin of function '|| l_procedure_name || ' for entity ' || p_entity_code;
738
739 print_debug(g_log_msg);
740 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
741 THEN
742 FND_LOG.STRING(
743 log_level => g_level_procedure,
744 module => g_module_name || l_procedure_name,
745 message => g_log_msg
746 );
747 END IF;
748
749 --
750 -- Bug 5052850
751 -- SLA created new table xla_events_int_gt for performance improvements.
752 -- So, changed xla_events_int_gt table to xla_events_int_gt table.
753 -- Prereq SLA patch: 4777706
754 --
755 -- RS Bug 5059076 - line_number and event_created_by columns
756 -- are not there present in the new _int_gt table
757 -- Both columns were set to Null before
758 -- bug 11807826, added event type code in the where clause for Lc valuatuion transactions
759 --
760 -- Bug 13043283. Populating source_id_char_2 with source_line_id (rcv_transactions.transaction_id).
761 --
762 INSERT INTO xla_events_int_gt
763 (
764 entity_id
765 , application_id
766 , ledger_id
767 , legal_entity_id
768 , entity_code
769 , transaction_number
770 , source_id_int_1
771 , source_id_int_2
772 , source_id_int_3
773 , source_id_int_4
774 , source_id_char_1
775 , source_id_char_2
776 , source_id_char_3
777 , source_id_char_4
778 , event_id
779 , event_class_code
780 , event_type_code
781 , event_number
782 , event_date
783 , event_status_code
784 , process_status_code
785 , reference_num_1
786 , reference_num_2
787 , reference_num_3
788 , reference_num_4
789 , reference_char_1
790 , reference_char_2
791 , reference_char_3
792 , reference_char_4
793 , reference_date_1
794 , reference_date_2
795 , reference_date_3
796 , reference_date_4
797 , valuation_method
798 , security_id_int_1
799 , security_id_int_2
800 , security_id_int_3
801 , security_id_char_1
802 , security_id_char_2
803 , security_id_char_3
804 , on_hold_flag
805 , transaction_date
806 )
807 SELECT
808 DISTINCT
809 NULL -- entity_id
810 , 555 -- application_id
811 , ledger_id
812 , legal_entity_id
813 , entity_code
814 , transaction_id -- transaction_number
815 , transaction_id -- SOURCE_ID_INT_1
816 , ledger_id -- SOURCE_ID_INT_2
817 , valuation_cost_type_id -- SOURCE_ID_INT_3
818 /* , transaction_source_type_id -- SOURCE_ID_INT_4 INVCONV */
819 , NULL -- SOURCE_ID_INT_4
820 , event_class_code -- SOURCE_ID_CHAR_1
821 /* , lot_number -- SOURCE_ID_CHAR_2 INVCONV */
822 /*
823 , DECODE(event_class_code,
824 'RECEIVE', DECODE(event_type_code,
825 'RECEIVE', source_line_id,
826 NULL),
827 NULL) -- SOURCE_ID_CHAR_2 Bug 13043283
828 */
829 , NULL -- SOURCE_ID_CHAR_2 Bug 13637723
830 , NULL -- SOURCE_ID_CHAR_3
831 , NULL -- SOURCE_ID_CHAR_4
832 , NULL -- event_id
833 , event_class_code
834 , event_type_code
835 , NULL -- event_number
836 , transaction_date -- event_date
837 , xla_events_pub_pkg.C_EVENT_UNPROCESSED -- event_status_code
838 , NULL -- process_status_code
839 , reference_no -- REFERENCE_NUM_1
840 , NULL -- REFERENCE_NUM_2
841 , NULL -- REFERENCE_NUM_3
842 , NULL -- REFERENCE_NUM_4
843 , NULL -- REFERENCE_CHAR_1
844 , NULL -- REFERENCE_CHAR_2
845 , NULL -- REFERENCE_CHAR_3
846 , NULL -- REFERENCE_CHAR_4
847 , NULL -- REFERENCE_DATE_1
848 , NULL -- REFERENCE_DATE_2
849 , NULL -- REFERENCE_DATE_3
850 , NULL -- REFERENCE_DATE_4
851 , valuation_cost_type -- valuation_method
852 , ehgt.organization_id -- SECURITY_ID_INT_1
853 , ehgt.operating_unit -- SECURITY_ID_INT_2
854 , legal_entity_id -- SECURITY_ID_INT_3 Bug 6601963
855 , NULL -- SECURITY_ID_CHAR_1
856 , NULL -- SECURITY_ID_CHAR_2
857 , NULL -- SECURITY_ID_CHAR_3
858 , NULL
859 , transaction_date
860 FROM
861 gmf_xla_extract_headers_gt ehgt
862 WHERE
863 entity_code = p_entity_code
864 AND
865 not exists (SELECT 'txns for which events created'
866 FROM gmf_xla_extract_headers eh
867 WHERE eh.transaction_id = ehgt.transaction_id
868 AND eh.legal_entity_id = ehgt.legal_entity_id
869 AND eh.ledger_id = ehgt.ledger_id
870 AND eh.valuation_cost_type_id = ehgt.valuation_cost_type_id
871 /* AND eh.transaction_source_type_id = ehgt.transaction_source_type_id INVCONV */
872 AND eh.event_class_code = ehgt.event_class_code
873 /* AND nvl(eh.lot_number, 'x') = nvl(ehgt.lot_number, 'x') INVCONV */
874 AND eh.event_type_code = DECODE(ehgt.event_class_code,'LC_ADJUSTMENTS',ehgt.event_type_code, eh.event_type_code ) /*bug 11807826 */
875 ) ;
876
877
878 n_rows_inserted := sql%rowcount;
879
880 g_log_msg := n_rows_inserted || ' rows inserted into xla_events_int_gt';
881
882 print_debug(g_log_msg);
883 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
884 THEN
885 FND_LOG.STRING(
886 log_level => g_level_procedure,
887 module => g_module_name || l_procedure_name,
888 message => g_log_msg
889 );
890 END IF;
891
892 g_log_msg := 'End of procedure '|| l_procedure_name || ' for entity ' || p_entity_code;
893
894 print_debug(g_log_msg);
895 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
896 THEN
897 FND_LOG.STRING(
898 log_level => g_level_procedure,
899 module => g_module_name || l_procedure_name,
900 message => g_log_msg
901 );
902 END IF;
903
904 RETURN n_rows_inserted;
905
906 END insert_into_xla_events_gt;
907
908 PROCEDURE update_extract_headers_table
909 IS
910
911 l_procedure_name CONSTANT VARCHAR2(100) := g_module_name || 'UPDATE_EXTRACT_HEADERS_TABLE';
912
913 BEGIN
914
915 g_log_msg := 'Begin of procedure '|| l_procedure_name;
916
917 print_debug(g_log_msg);
918 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
919 THEN
920 FND_LOG.STRING(
921 log_level => g_level_procedure,
922 module => g_module_name || l_procedure_name,
923 message => g_log_msg
924 );
925 END IF;
926
927 g_log_msg := 'Merging rows in to gmf_xla_extract_headers table';
928
929 print_debug(g_log_msg);
930 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
931 THEN
932 FND_LOG.STRING(
933 log_level => g_level_procedure,
934 module => g_module_name || l_procedure_name,
935 message => g_log_msg
936 );
937 END IF;
938
939 MERGE INTO gmf_xla_extract_headers eh
940 USING (SELECT
941 DISTINCT
942 reference_no
943 , event_id
944 , entity_code
945 , event_class_code
946 , event_type_code
947 , legal_entity_id
948 , ledger_id
949 , xfer_legal_entity_id
950 , xfer_ledger_id
951 , operating_unit
952 , base_currency
953 , transaction_id
954 , transaction_date
955 , valuation_cost_type_id
956 , valuation_cost_type
957 , inventory_item_id
958 -- , item_revision
959 , organization_id
960 , lot_number
961 , transaction_quantity
962 , transaction_uom
963 , transaction_source_type_id
964 , transaction_action_id
965 , transaction_type_id
966 , transaction_value
967 , transaction_value_raw
968 , transaction_currency
969 , txn_source
970 , source_document_id
971 , source_line_id
972 , currency_code
973 , currency_conversion_date
974 , currency_conversion_type
975 , currency_conversion_rate -- Bug 6792803
976 , resources
977 -- , resource_class
978 , line_type
979 , ar_trx_type_id
980 , order_type
981 , reason_id
982 /*
983 , charge_id
984 , customer_id
985 , customer_site_id
986 , taxauth_id
987 , vendor_id
988 , vendor_site_id
989 , routing_id
990 , customer_gl_class
991 , itemcost_class
992 , vendor_gl_class
993 , cost_category_id
994 , gl_business_class_cat_id
995 , gl_product_line_cat_id
996 , jv_qty_ind
997 , quantity_um
998 */
999 , accounted_flag
1000 , actual_posting_date
1001 , invoiced_flag
1002 , shipment_costed
1003 FROM gmf_xla_extract_headers_gt) ehgt
1004 ON ( eh.transaction_id = ehgt.transaction_id
1005 AND eh.ledger_id = ehgt.ledger_id
1006 AND eh.valuation_cost_type_id = ehgt.valuation_cost_type_id
1007 /* AND eh.transaction_source_type_id = ehgt.transaction_source_type_id INVCONV */
1008 AND eh.event_class_code = ehgt.event_class_code
1009 /* AND nvl(eh.lot_number, 'x') = nvl(ehgt.lot_number, 'x') INVCONV */
1010 /* Bug 7620018. Added legal_entity_id so that unique index GMF_XLA_EXTRACT_HEADERS_U2 is used. */
1011 AND eh.legal_entity_id = ehgt.legal_entity_id
1012 AND eh.event_type_code = DECODE(ehgt.event_class_code,'LC_ADJUSTMENTS',ehgt.event_type_code, eh.event_type_code ) /*bug 11807826 */
1013 )
1014 WHEN MATCHED THEN
1015 UPDATE SET
1016 eh.transaction_quantity = ehgt.transaction_quantity
1017 , eh.transaction_uom = ehgt.transaction_uom /* B8617122 */
1018 , eh.transaction_value = ehgt.transaction_value
1019 , eh.transaction_value_raw = ehgt.transaction_value_raw
1020 , eh.reference_no = ehgt.reference_no
1021 , eh.shipment_costed = ehgt.shipment_costed
1022 , eh.invoiced_flag = ehgt.invoiced_flag
1023 , eh.last_update_date = sysdate
1024 , eh.last_updated_by = g_user_id
1025 , eh.last_update_login = g_login_id
1026 , eh.program_application_id = g_prog_appl_id
1027 , eh.program_id = g_program_id
1028 , eh.request_id = g_request_id
1029 , eh.currency_conversion_rate = ehgt.currency_conversion_rate
1030 , eh.transaction_date = ehgt.transaction_date /* Bug 8251052 */
1031 WHEN NOT MATCHED THEN
1032 INSERT
1033 (
1034 header_id
1035 , reference_no
1036 , event_id
1037 , entity_code
1038 , event_class_code
1039 , event_type_code
1040 , legal_entity_id
1041 , ledger_id
1042 , xfer_legal_entity_id
1043 , xfer_ledger_id
1044 , operating_unit
1045 , base_currency
1046 , transaction_id
1047 , transaction_date
1048 , valuation_cost_type_id
1049 , valuation_cost_type
1050 , inventory_item_id
1051 -- , item_revision
1052 , organization_id
1053 , lot_number
1054 , transaction_quantity
1055 , transaction_uom
1056 , transaction_source_type_id
1057 , transaction_action_id
1058 , transaction_type_id
1059 , transaction_value
1060 , transaction_value_raw
1061 , transaction_currency
1062 , txn_source
1063 , source_document_id
1064 , source_line_id
1065 , currency_code
1066 , currency_conversion_date
1067 , currency_conversion_type
1068 , currency_conversion_rate -- Bug 6792803
1069 , resources
1070 -- , resource_class
1071 , line_type
1072 , ar_trx_type_id
1073 , order_type
1074 , reason_id
1075 /*
1076 , charge_id
1077 , customer_id
1078 , customer_site_id
1079 , taxauth_id
1080 , vendor_id
1081 , vendor_site_id
1082 , routing_id
1083 , customer_gl_class
1084 , itemcost_class
1085 , vendor_gl_class
1086 , cost_category_id
1087 , gl_business_class_cat_id
1088 , gl_product_line_cat_id
1089 , jv_qty_ind
1090 , quantity_um
1091 */
1092 , accounted_flag
1093 , actual_posting_date
1094 , creation_date
1095 , created_by
1096 , last_update_date
1097 , last_updated_by
1098 , last_update_login
1099 , program_application_id
1100 , program_id
1101 , request_id
1102 )
1103 VALUES
1104 (
1105 gmf_xla_extract_headers_s.NEXTVAL -- header_id
1106 , ehgt.reference_no
1107 , ehgt.event_id
1108 , ehgt.entity_code
1109 , ehgt.event_class_code
1110 , ehgt.event_type_code
1111 , ehgt.legal_entity_id
1112 , ehgt.ledger_id
1113 , ehgt.xfer_legal_entity_id
1114 , ehgt.xfer_ledger_id
1115 , ehgt.operating_unit
1116 , ehgt.base_currency
1117 , ehgt.transaction_id
1118 , ehgt.transaction_date
1119 , ehgt.valuation_cost_type_id
1120 , ehgt.valuation_cost_type
1121 , ehgt.inventory_item_id
1122 -- , ehgt.item_revision
1123 , ehgt.organization_id
1124 , ehgt.lot_number
1125 , ehgt.transaction_quantity
1126 , ehgt.transaction_uom
1127 , ehgt.transaction_source_type_id
1128 , ehgt.transaction_action_id
1129 , ehgt.transaction_type_id
1130 , ehgt.transaction_value
1131 , ehgt.transaction_value_raw
1132 , ehgt.transaction_currency
1133 , ehgt.txn_source
1134 , ehgt.source_document_id
1135 , ehgt.source_line_id
1136 , ehgt.currency_code
1137 , ehgt.currency_conversion_date
1138 , ehgt.currency_conversion_type
1139 , ehgt.currency_conversion_rate
1140 , ehgt.resources
1141 -- , ehgt.resource_class
1142 , ehgt.line_type
1143 , ehgt.ar_trx_type_id
1144 , ehgt.order_type
1145 , ehgt.reason_id
1146 /*
1147 , ehgt.charge_id
1148 , ehgt.customer_id
1149 , ehgt.customer_site_id
1150 , ehgt.taxauth_id
1151 , ehgt.vendor_id
1152 , ehgt.vendor_site_id
1153 , ehgt.routing_id
1154 , ehgt.customer_gl_class
1155 , ehgt.itemcost_class
1156 , ehgt.vendor_gl_class
1157 , ehgt.cost_category_id
1158 , ehgt.gl_business_class_cat_id
1159 , ehgt.gl_product_line_cat_id
1160 , ehgt.jv_qty_ind
1161 , ehgt.quantity_um
1162 */
1163 , 'N' -- ehgt.accounted_flag
1164 , ehgt.actual_posting_date
1165 , sysdate
1166 , g_user_id
1167 , sysdate
1168 , g_user_id
1169 , g_login_id
1170 , g_prog_appl_id
1171 , g_program_id
1172 , g_request_id
1173 )
1174 ;
1175
1176
1177 g_log_msg := sql%rowcount || ' rows merged into extract headers';
1178
1179 print_debug(g_log_msg);
1180 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
1181 THEN
1182 FND_LOG.STRING(
1183 log_level => g_level_statement,
1184 module => g_module_name || l_procedure_name,
1185 message => g_log_msg
1186 );
1187 END IF;
1188
1189
1190
1191 g_log_msg := 'End of procedure '|| l_procedure_name;
1192
1193 print_debug(g_log_msg);
1194 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
1195 THEN
1196 FND_LOG.STRING(
1197 log_level => g_level_procedure,
1198 module => g_module_name || l_procedure_name,
1199 message => g_log_msg
1200 );
1201 END IF;
1202
1203
1204 END update_extract_headers_table;
1205
1206 PROCEDURE update_extract_lines_table
1207 IS
1208
1209 l_procedure_name CONSTANT VARCHAR2(100) := g_module_name || 'UPDATE_EXTRACT_LINES_TABLE';
1210 l_cnt number; -- xxxremove
1211 BEGIN
1212
1213 g_log_msg := 'Begin of procedure '|| l_procedure_name;
1214
1215 print_debug(g_log_msg);
1216 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
1217 THEN
1218 FND_LOG.STRING(
1219 log_level => g_level_procedure,
1220 module => g_module_name || l_procedure_name,
1221 message => g_log_msg
1222 );
1223 END IF;
1224
1225 --
1226 -- First, set the header_id and event_id in extract_lines_gt table
1227 --
1228 -- B 7147477 include legal entity id so that unique index GMF_XLA_EXTRACT_HEADERS_U2 is used.
1229 -- bug 11807826, added event type code in the where clause for Lc valuatuion transactions
1230 -- bug13428401, modified the decode condition in the where clause
1231 UPDATE gmf_xla_extract_lines_gt elgt
1232 SET (header_id, event_id) =
1233 (SELECT
1234 eh.header_id, eh.event_id
1235 FROM
1236 gmf_xla_extract_headers_gt ehgt,
1237 gmf_xla_extract_headers eh
1238 WHERE
1239 ehgt.header_id = elgt.header_id
1240 AND eh.legal_entity_id = ehgt.legal_entity_id
1241 AND eh.ledger_id = ehgt.ledger_id
1242 AND eh.valuation_cost_type_id = ehgt.valuation_cost_type_id
1243 AND eh.transaction_id = ehgt.transaction_id
1244 /* AND eh.transaction_source_type_id = ehgt.transaction_source_type_id INVCONV */
1245 AND eh.event_class_code = ehgt.event_class_code
1246 /* AND nvl(eh.lot_number, 'x') = nvl(ehgt.lot_number, 'x') INVCONV */
1247 AND eh.event_type_code = DECODE(ehgt.event_class_code,'LC_ADJUSTMENTS',ehgt.event_type_code, eh.event_type_code ) /*bug 11807826 */
1248 ) ;
1249
1250
1251 g_log_msg := sql%rowcount || ' rows updated with header_id and event_id in extract_lines_gt table';
1252
1253 print_debug(g_log_msg);
1254 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
1255 THEN
1256 FND_LOG.STRING(
1257 log_level => g_level_statement,
1258 module => g_module_name || l_procedure_name,
1259 message => g_log_msg
1260 );
1261 END IF;
1262
1263
1264
1265 --
1266 -- Now, delete any old rows
1267 --
1268 DELETE FROM gmf_xla_extract_lines el
1269 WHERE
1270 -- reference_no <> g_reference_no AND
1271 header_id in
1272 (
1273 SELECT header_id
1274 FROM gmf_xla_extract_lines_gt elgt
1275 -- WHERE ehgt.header_id = el.header_id
1276 )
1277 ;
1278
1279 g_log_msg := sql%rowcount || ' old rows deleted from extract_lines table';
1280
1281 print_debug(g_log_msg);
1282 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
1283 THEN
1284 FND_LOG.STRING(
1285 log_level => g_level_statement,
1286 module => g_module_name || l_procedure_name,
1287 message => g_log_msg
1288 );
1289 END IF;
1290
1291 --
1292 -- Now, merge with main extract_lines table
1293 --
1294 INSERT INTO gmf_xla_extract_lines
1295 (
1296 line_id
1297 , header_id
1298 , reference_no
1299 , event_id
1300 , ledger_id
1301 , line_number
1302 , journal_line_type
1303 , cost_cmpntcls_id
1304 , cost_analysis_code
1305 , component_cost
1306 , usage_ind
1307 , cost_level
1308 , aqui_cost_id
1309 , trans_amount_raw
1310 , base_amount_raw
1311 , trans_amount
1312 , base_amount
1313 , dr_cr_sign
1314 , organization_id
1315 , subinv_organization_id
1316 , subinventory_code
1317 , xfer_subinventory_code
1318 , lot_number
1319 , locator_id
1320 , transaction_account_id
1321 , entered_amount
1322 , accounted_amount
1323 , creation_date
1324 , created_by
1325 , last_update_date
1326 , last_updated_by
1327 , program_application_id
1328 , program_id
1329 , request_id
1330 )
1331 SELECT
1332 elgt.line_id
1333 -- gmf_xla_extract_lines_s.NEXTVAL -- line_id /* Bug 12576944 */
1334 , elgt.header_id
1335 , elgt.reference_no
1336 , elgt.event_id
1337 , elgt.ledger_id
1338 , row_number() over(partition by header_id order by header_id) -- line_number
1339 , elgt.journal_line_type
1340 , elgt.cost_cmpntcls_id
1341 , elgt.cost_analysis_code
1342 , elgt.component_cost
1343 , elgt.usage_ind
1344 , elgt.cost_level
1345 , elgt.aqui_cost_id
1346 , elgt.trans_amount_raw
1347 , elgt.base_amount_raw
1348 , elgt.trans_amount
1349 , elgt.base_amount
1350 , elgt.dr_cr_sign
1351 , elgt.organization_id
1352 , elgt.organization_id
1353 , elgt.subinventory_code
1354 , elgt.xfer_subinventory_code
1355 , elgt.lot_number
1356 , elgt.locator_id
1357 , elgt.transaction_account_id
1358 , elgt.entered_amount
1359 , elgt.accounted_amount
1360 , sysdate
1361 , g_user_id
1362 , sysdate
1363 , g_user_id
1364 , g_prog_appl_id
1365 , g_program_id
1366 , g_request_id
1367 FROM
1368 gmf_xla_extract_lines_gt elgt
1369 ;
1370
1371 g_log_msg := sql%rowcount || ' rows inserted into extract_lines table';
1372
1373 print_debug(g_log_msg);
1374 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
1375 THEN
1376 FND_LOG.STRING(
1377 log_level => g_level_statement,
1378 module => g_module_name || l_procedure_name,
1379 message => g_log_msg
1380 );
1381 END IF;
1382
1383
1384 g_log_msg := 'End of procedure '|| l_procedure_name;
1385
1386 print_debug(g_log_msg);
1387 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
1388 THEN
1389 FND_LOG.STRING(
1390 log_level => g_level_procedure,
1391 module => g_module_name || l_procedure_name,
1392 message => g_log_msg
1393 );
1394 END IF;
1395
1396
1397 END update_extract_lines_table;
1398
1399
1400 PROCEDURE merge_into_gtv
1401 IS
1402
1403 l_procedure_name CONSTANT VARCHAR2(100) := g_module_name || 'MERGE_INTO_GTV';
1404
1405 BEGIN
1406
1407 g_log_msg := 'Begin of procedure '|| l_procedure_name;
1408
1409 print_debug(g_log_msg);
1410 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
1411 THEN
1412 FND_LOG.STRING(
1413 log_level => g_level_procedure,
1414 module => g_module_name || l_procedure_name,
1415 message => g_log_msg
1416 );
1417 END IF;
1418
1419 /* Bug 12647482 Following delete statement commented and replaced with sebsequent one. */
1420 /*
1421 delete from gmf_transaction_valuation
1422 where (transaction_id, ledger_id, valuation_cost_type_id,
1423 -- transaction_source_type_id,
1424 event_class_code) IN
1425 (select eh.transaction_id, eh.ledger_id, eh.valuation_cost_type_id,
1426 -- eh.transaction_source_type_id,
1427 eh.event_class_code
1428 from gmf_xla_extract_headers eh,
1429 gmf_xla_extract_lines_gt elgt
1430 where eh.header_id = elgt.header_id
1431 and eh.event_id = elgt.event_id
1432 )
1433 ;
1434 */
1435
1436 /* Bug 12647482 */
1437 DELETE FROM gmf_transaction_valuation
1438 WHERE (transaction_id, ledger_id, valuation_cost_type_id, event_class_code)
1439 IN (
1440 select transaction_id, ledger_id, valuation_cost_type_id, event_class_code
1441 from gmf_xla_extract_headers_gt
1442 );
1443
1444 g_log_msg := sql%rowcount || ' previous rows deleted from GVT';
1445
1446 print_debug(g_log_msg);
1447 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
1448 THEN
1449 FND_LOG.STRING(
1450 log_level => G_LEVEL_STATEMENT,
1451 module => g_module_name || l_procedure_name,
1452 message => g_log_msg
1453 );
1454 END IF;
1455
1456 INSERT INTO gmf_transaction_valuation
1457 (
1458 valuation_id
1459 , header_id
1460 , event_id
1461 , ledger_id
1462 , legal_entity_id
1463 , ledger_currency
1464 , valuation_cost_type
1465 , valuation_cost_type_id
1466 , reference_no
1467 , transaction_source
1468 , transaction_id
1469 , doc_id
1470 , line_id
1471 , org_id
1472 , organization_id
1473 , inventory_item_id
1474 , item_number
1475 , lot_number
1476 , resources
1477 , transaction_date
1478 , transaction_source_type_id
1479 , transaction_action_id
1480 , transaction_type_id
1481 , entity_code
1482 , event_class_code
1483 , event_type_code
1484 , final_posting_date
1485 , accounted_flag
1486 , line_type
1487 , transaction_source_type
1488 , journal_line_type
1489 , subinventory_code
1490 , component_class_usage
1491 , component_class_usage_type
1492 , cost_level
1493 , txn_base_value_raw
1494 , txn_base_value
1495 , creation_date
1496 , created_by
1497 , last_update_date
1498 , last_updated_by
1499 , program_application_id
1500 , program_id
1501 , request_id
1502 )
1503 SELECT
1504 gmf_transaction_valuation_S.nextval
1505 , a.header_id
1506 , a.event_id
1507 , a.ledger_id
1508 , a.legal_entity_id
1509 , a.ledger_currency
1510 , a.valuation_cost_type
1511 , a.valuation_cost_type_id
1512 , a.reference_no
1513 , a.transaction_source
1514 , a.transaction_id
1515 , a.doc_id
1516 , a.line_id
1517 , a.org_id
1518 , a.organization_id
1519 , a.inventory_item_id
1520 , a.item_number
1521 , a.lot_number
1522 , a.resources
1523 , a.transaction_date
1524 , a.transaction_source_type_id
1525 , a.transaction_action_id
1526 , a.transaction_type_id
1527 , a.entity_code
1528 , a.event_class_code
1529 , a.event_type_code
1530 , a.final_posting_date
1531 , a.accounted_flag
1532 , a.line_type
1533 , a.transaction_source_type
1534 , a.journal_line_type
1535 , a.subinventory_code
1536 , a.component_class_usage
1537 , a.component_class_usage_type
1538 , a.cost_level
1539 , a.txn_base_value_raw
1540 , a.txn_base_value
1541 , sysdate
1542 , g_user_id
1543 , sysdate
1544 , g_user_id
1545 , g_prog_appl_id
1546 , g_program_id
1547 , g_request_id
1548 FROM
1549 (SELECT
1550 eh.header_id
1551 , eh.event_id
1552 , eh.ledger_id
1553 , eh.legal_entity_id
1554 , eh.base_currency as ledger_currency
1555 , eh.valuation_cost_type
1556 , eh.valuation_cost_type_id
1557 , eh.reference_no
1558 , decode(eh.txn_source, 'INV', 'INVENTORY',
1559 'PUR', 'PURCHASING',
1560 'OM', 'ORDERMANAGEMENT',
1561 'PM', 'PRODUCTION',
1562 'RVAL', 'COSTREVALUATION')
1563 as transaction_source
1564 , eh.transaction_id
1565 , eh.source_document_id as doc_id
1566 , eh.source_line_id as line_id
1567 , eh.operating_unit as org_id
1568 , eh.organization_id
1569 , eh.inventory_item_id
1570 , item.concatenated_segments as item_number
1571 , NULL as lot_number
1572 , eh.resources
1573 , eh.transaction_date
1574 , eh.transaction_source_type_id
1575 , eh.transaction_action_id
1576 , eh.transaction_type_id
1577 , eh.entity_code
1578 , eh.event_class_code
1579 , eh.event_type_code
1580 , eh.actual_posting_date as final_posting_date
1581 , eh.accounted_flag
1582 , eh.line_type
1583 , nvl(ts.transaction_source_type_name, ' ') as transaction_source_type
1584 , elgt.journal_line_type
1585 , elgt.subinventory_code
1586 , decode(elgt.usage_ind, 1, 'Material',
1587 2, 'Overhead',
1588 3, 'Resource',
1589 4, 'Expense Alloc',
1590 5, 'Std Cost Adj') as component_class_usage
1591 , elgt.usage_ind as component_class_usage_type
1592 , elgt.cost_level
1593 , sum(elgt.BASE_AMOUNT_RAW) as txn_base_value_raw
1594 , sum(elgt.BASE_AMOUNT) as txn_base_value
1595 FROM gmf_xla_extract_headers eh,
1596 gmf_xla_extract_lines_gt elgt,
1597 mtl_system_items_kfv item,
1598 mtl_txn_source_types ts
1599 WHERE eh.header_id = elgt.header_id
1600 AND eh.event_id = elgt.event_id
1601 --
1602 -- Need an outer join here since for batch close rows, item id is null
1603 --
1604 AND item.organization_id(+) = eh.organization_id
1605 AND item.inventory_item_id(+) = eh.inventory_item_id
1606 AND ts.transaction_source_type_id(+) = eh.transaction_source_type_id
1607 GROUP BY
1608 eh.header_id
1609 , eh.event_id
1610 , eh.ledger_id
1611 , eh.legal_entity_id
1612 , eh.base_currency
1613 , eh.valuation_cost_type
1614 , eh.valuation_cost_type_id
1615 , eh.reference_no
1616 , decode(eh.txn_source, 'INV', 'INVENTORY',
1617 'PUR', 'PURCHASING',
1618 'OM', 'ORDERMANAGEMENT',
1619 'PM', 'PRODUCTION',
1620 'RVAL', 'COSTREVALUATION')
1621 , eh.transaction_id
1622 , eh.source_document_id
1623 , eh.source_line_id
1624 , eh.operating_unit
1625 , eh.organization_id
1626 , eh.inventory_item_id
1627 , item.concatenated_segments
1628 , eh.lot_number
1629 , eh.resources
1630 , eh.transaction_date
1631 , eh.transaction_source_type_id
1632 , eh.transaction_action_id
1633 , eh.transaction_type_id
1634 , eh.entity_code
1635 , eh.event_class_code
1636 , eh.event_type_code
1637 , eh.actual_posting_date
1638 , eh.accounted_flag
1639 , eh.line_type
1640 , ts.transaction_source_type_name
1641 , elgt.journal_line_type
1642 , elgt.subinventory_code
1643 , decode(elgt.usage_ind, 1, 'Material',
1644 2, 'Overhead',
1645 3, 'Resource',
1646 4, 'Expense Alloc',
1647 5, 'Std Cost Adj')
1648 , elgt.usage_ind
1649 , elgt.cost_level
1650 ) a
1651 ;
1652
1653 g_log_msg := sql%rowcount || ' rows inserted into GVT';
1654
1655 print_debug(g_log_msg);
1656 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
1657 THEN
1658 FND_LOG.STRING(
1659 log_level => g_level_statement,
1660 module => g_module_name || l_procedure_name,
1661 message => g_log_msg
1662 );
1663 END IF;
1664
1665
1666
1667 g_log_msg := 'End of procedure '|| l_procedure_name;
1668
1669 print_debug(g_log_msg);
1670 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
1671 THEN
1672 FND_LOG.STRING(
1673 log_level => g_level_procedure,
1674 module => g_module_name || l_procedure_name,
1675 message => g_log_msg
1676 );
1677 END IF;
1678
1679
1680 END MERGE_INTO_GTV;
1681
1682
1683
1684 /*============================================================================
1685 | PROCEDURE - PREACCOUNTING(PUBLIC)
1686 |
1687 | DESCRIPTION
1688 | This procedure is the AP SLA preaccounting procedure. This procedure
1689 | will be called by SLA through an API.
1690 |
1691 | PRAMETERS
1692 | p_application_id:
1693 | This parameter is the application ID of the application that the SLA
1694 | workflow event is for. This procedure must exit without doing anything
1695 | if this parameter is not 200 to ensure that this procedure is only
1696 | executed when the workflow event is for AP. This parameter will never
1697 | be NULL.
1698 | p_ledger_id:
1699 | This parameter is the ledger ID of the ledger to account.This
1700 | parameter is purely informational. This procedure selects from the
1701 | XLA_ENTITY_EVENTS_V view, which does not include events incompatible
1702 | with this parameter. This parameter will never be NULL.
1703 | p_process_category:
1704 | This parameter is the "process category" of the events to account. This
1705 | parameter is purely informational. This procedure selects from the
1706 | XLA_ENTITY_EVENTS_V view, which does not include events incompatible
1707 | with this parameter.Possible values are as following:
1708 | +------------+------------------------------------------+
1709 | | Value | Meaning |
1710 | +------------+------------------------------------------+
1711 | | 'Invoices' | process invoices |
1712 | | 'Payments' | process payments and reconciled payments |
1713 | | 'All' | process everything |
1714 | +------------+------------------------------------------+
1715 | p_end_date
1716 | This parameter is the maximum event date of the events to be processed
1717 | in this run of the accounting. This procedure selects from the
1718 | XLA_ENTITY_EVENTS_V view, which does not include events incompatible
1719 | with this parameter. This parameter will never be NULL.
1720 | p_accounting_mode
1721 | This parameter is the "accounting mode" that the accounting is being
1722 | run in. This parameter will never be NULL.
1723 | +-------+------------------------------------------------------------+
1724 | | Value | Meaning |
1725 | +-------+------------------------------------------------------------+
1726 | | 'D' | The accounting is being run in "draft mode". Draft mode is |
1727 | | | used to examine what the accounting entries would look for |
1728 | | | an event without actually creating the accounting entries. |
1729 | | | without actually creating the accounting entries. |
1730 | | 'F' | The accounting is being run in "final mode". Final mode is |
1731 | | | used to create accounting entries. |
1732 | +-------+------------------------------------------------------------+
1733 | p_valuation_method
1734 | This parameter is unused by AP. This parameter is purely informational.
1735 | This procedure selects from the XLA_ENTITY_EVENTS_V view, which does
1736 | not include events incompatible with this parameter.
1737 | p_security_id_int_1
1738 | This parameter is unused by AP.
1739 | p_security_id_int_2
1740 | This parameter is unused by AP.
1741 | p_security_id_int_3
1742 | This parameter is unused by AP.
1743 | p_security_id_char_1
1744 | This parameter is unused by AP.
1745 | p_security_id_char_2
1746 | This parameter is unused by AP.
1747 | p_security_id_char_3
1748 | This parameter is unused by AP.
1749 | p_report_request_id
1750 | This parameter is the concurrent request ID of the concurrent request
1751 | that is this run of the accounting. This parameter is used to specify
1752 | which events in the XLA_ENTITY_EVENTS_V view are to be accounted in
1753 | this run of the accounting. This parameter will never be NULL.
1754 | KNOWN ISSUES:
1755 |
1756 | NOTES:
1757 | 1) This procedure is run in final mode and draft mode.
1758 | 2) This procedure is run in batch mode but not in document mode.
1759 | 3) This procedure is in its own commit cycle.
1760 |
1761 | MODIFICATION HISTORY
1762 | Date Author Description of Change
1763 | 23rd Feb, 2011 Saptagirish Pabolu Code changed against the Bug#11075192
1764 *==================================================================================*/
1765 PROCEDURE preaccounting
1766 ( p_application_id IN NUMBER
1767 , p_ledger_id IN NUMBER
1768 , p_process_category IN VARCHAR2
1769 , p_end_date IN DATE
1770 , p_accounting_mode IN VARCHAR2
1771 , p_valuation_method IN VARCHAR2
1772 , p_security_id_int_1 IN NUMBER
1773 , p_security_id_int_2 IN NUMBER
1774 , p_security_id_int_3 IN NUMBER
1775 , p_security_id_char_1 IN VARCHAR2
1776 , p_security_id_char_2 IN VARCHAR2
1777 , p_security_id_char_3 IN VARCHAR2
1778 , p_report_request_id IN NUMBER
1779 )
1780 IS
1781 l_profile varchar2(1);
1782 l_cost_frozen_flag NUMBER;
1783 x_cost_frozen_flag NUMBER;
1784 l_count Number;
1785 l_cost_type_id NUMBER;
1786 l_cost_mthd varchar2(10);
1787
1788
1789
1790
1791 CURSOR check_cost_not_frozen (l_security_id_int_3 NUMBER, l_valuation_method varchar2) IS
1792 Select 1 from dual
1793 where exists
1794 (SELECT mp.organization_id
1795 FROM mtl_parameters mp,
1796 gmf_fiscal_policies gfp,
1797 org_organization_definitions ood,
1798 gl_item_cst gic,
1799 gmf_period_statuses gps,
1800 cm_mthd_mst cmm
1801 WHERE mp.process_enabled_flag = 'Y'
1802 AND gfp.legal_entity_id = ood.legal_entity
1803 AND gfp.legal_entity_id = l_security_id_int_3
1804 AND mp.organization_id = ood.organization_id
1805 AND gic.organization_id = mp.organization_id
1806 AND gic.cost_type_id = gps.cost_type_id
1807 AND gic.final_flag = 0
1808 AND gic.end_date <= gps.end_date
1809 AND gps.legal_entity_id = gfp.legal_entity_id
1810 AND cmm.cost_type_id = gps.cost_type_id
1811 AND cmm.cost_mthd_code = l_valuation_method
1812 AND gps.start_date <= p_end_date
1813 AND gps.end_date >= p_end_date);
1814
1815 -- Cursor used if no records exist in gl_item_cost
1816
1817 CURSOR cost_update_run (l_security_id_int_3 NUMBER, l_valuation_method varchar2) IS
1818 SELECT count(mp.organization_id)
1819 FROM mtl_parameters mp,
1820 gmf_fiscal_policies gfp,
1821 org_organization_definitions ood,
1822 gl_item_cst gic,
1823 gmf_period_statuses gps,
1824 cm_mthd_mst cmm
1825 WHERE mp.process_enabled_flag = 'Y'
1826 AND gfp.legal_entity_id = ood.legal_entity
1827 AND gfp.legal_entity_id = l_security_id_int_3
1828 AND mp.organization_id = ood.organization_id
1829 AND gic.organization_id = mp.organization_id
1830 AND gic.cost_type_id = gps.cost_type_id
1831 AND gic.end_date <= gps.end_date
1832 AND gps.legal_entity_id = gfp.legal_entity_id
1833 AND cmm.cost_type_id = gps.cost_type_id
1834 AND cmm.cost_mthd_code = l_valuation_method
1835 AND gps.start_date <= p_end_date
1836 AND gps.end_date >= p_end_date;
1837
1838 -- Get cost type and legal entity from extract headers
1839 CURSOR cur_le_cost_mthd IS
1840 SELECT DISTINCT cost_mthd_code, eh.legal_entity_id
1841 FROM gmf_xla_extract_headers eh, cm_mthd_mst cmm, gmf_ledger_valuation_methods vm
1842 WHERE eh.valuation_cost_type_id = cmm.cost_type_id
1843 AND vm.ledger_id = eh.ledger_id
1844 AND vm.cost_type_id = eh.valuation_cost_type_id
1845 AND eh.ledger_id = p_ledger_id
1846 AND eh.accounted_flag IS NOT NULL
1847 AND eh.transaction_date <= p_end_date
1848 AND cmm.cost_mthd_code = NVL(p_valuation_method, cmm.cost_mthd_code)
1849 AND eh.legal_entity_id = NVL(p_security_id_char_3, eh.legal_entity_id);
1850
1851 BEGIN
1852
1853 g_log_msg := 'Begin of procedure GMF_XLA_PKG.preaccounting. Mode is '||p_accounting_mode;
1854 print_debug(g_log_msg);
1855
1856 IF NOT g_version_printed THEN
1857 g_log_msg := '$Header: GMFXLAPB.pls 120.48.12020000.4 2012/12/24 16:14:08 pvkanetk ship $';
1858 print_debug(g_log_msg);
1859 g_version_printed := TRUE;
1860 END IF;
1861
1862 l_profile := NVL(fnd_profile.VALUE ('GMF_CHECK_COSTS_FROZEN'),'N');
1863
1864 IF p_accounting_mode = 'F' THEN
1865 IF l_profile = 'Y' THEN
1866
1867 x_cost_frozen_flag := 0; -- OK to begin with
1868
1869 FOR rec IN cur_le_cost_mthd LOOP
1870
1871 l_cost_frozen_flag := 0;
1872
1873 OPEN cost_update_run( rec.legal_entity_id, rec.cost_mthd_code);
1874 FETCH cost_update_run INTO l_count;
1875 CLOSE cost_update_run;
1876
1877 If l_count >= 1 THEN -- cost update has been run
1878
1879 OPEN check_cost_not_frozen( rec.legal_entity_id, rec.cost_mthd_code);
1880 FETCH check_cost_not_frozen INTO l_cost_frozen_flag;
1881
1882 -- Code changes against the Bug#11075192 Starts
1883 IF check_cost_not_frozen%NOTFOUND THEN
1884 l_cost_frozen_flag := 0;
1885 END IF;
1886 -- Code changes against the Bug#11075192 Ends
1887
1888 CLOSE check_cost_not_frozen;
1889 Else
1890 l_cost_frozen_flag := 1;
1891
1892 End If;
1893
1894 IF l_cost_frozen_flag = 1 THEN
1895 x_cost_frozen_flag := 1;
1896
1897 -- Code changes against the Bug#11075192 Starts
1898 ELSE
1899 x_cost_frozen_flag := 0;
1900 -- Code changes against the Bug#11075192 Ends
1901
1902 END IF;
1903
1904 END LOOP;
1905
1906 IF x_cost_frozen_flag = 1 THEN
1907
1908 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
1909 THEN
1910
1911 FND_LOG.STRING(
1912 log_level => g_level_procedure,
1913 module => g_module_name ,
1914 message => g_log_msg
1915 );
1916 END IF;
1917
1918 -- g_log_msg := 'Raising application error. costs are not frozen and accounting mode is '||p_accounting_mode;
1919 FND_MESSAGE.SET_NAME('GMF', 'CM_NOT_FROZEN');
1920
1921 g_log_msg := FND_MESSAGE.GET;
1922
1923
1924 RAISE_APPLICATION_ERROR(-20101, g_log_msg);
1925 END IF;
1926
1927 END IF; -- l_profile = 'Y'
1928 END IF; -- p_accounting_mode = 'F'
1929
1930 /*
1931 CASE p_process_category
1932 WHEN G_inventory_transactions
1933 THEN
1934 process_inv_txns(g_pre_accounting);
1935 WHEN G_purchasing_transactions
1936 THEN
1937 process_pur_txns(g_pre_accounting);
1938 WHEN G_production_transactions
1939 THEN
1940 process_pm_txns(g_pre_accounting);
1941 WHEN G_order_management
1942 THEN
1943 process_om_txns(g_pre_accounting);
1944 WHEN G_revaluation_transactions
1945 THEN
1946 process_rval_txns(g_pre_accounting);
1947 END CASE;
1948 */
1949
1950 g_log_msg := 'End of procedure GMF_XLA_PKG.preaccounting. Mode is '||p_accounting_mode;
1951 print_debug(g_log_msg);
1952
1953 END preaccounting;
1954
1955
1956 /*============================================================================
1957 | PROCEDURE - EXTRACT (PUBLIC)
1958 |
1959 | DESCRIPTION
1960 | This procedure is the AP SLA extract procedure. This procedure
1961 | will be called by SLA thorugh an API.
1962 |
1963 | PRAMETERS
1964 | p_application_id
1965 | This parameter is the application ID of the application that the SLA
1966 | workflow event is for. This procedure must exit without doing anything
1967 | if this parameter is not 200 to ensure that this procedure is only
1968 | executed when the workflow event is for AP. This parameter will never
1969 | be NULL.
1970 | p_accounting_mode
1971 | This parameter is the "accounting mode" that the accounting is being
1972 | run in. This parameter will never be NULL.
1973 | +-------+-----------------------------------------------------------+
1974 | | Value | Meaning |
1975 | +-------+-----------------------------------------------------------+
1976 | | 'D' | The accounting is being run in "draft mode". Draft mode is|
1977 | | | used TO examine what the accounting entries would look for|
1978 | | | an event without actually creating the accounting entries |
1979 | | 'F' | The accounting is being run in "final mode". Final mode is|
1980 | | | used to create accounting entries. |
1981 | +-------+-----------------------------------------------------------+
1982 |
1983 | KNOWN ISSUES:
1984 |
1985 | NOTES:
1986 | 1) This procedure is run in final mode and draft mode.
1987 | 2) This procedure is run in batch mode and document mode.
1988 | 3) This procedure is part of the accounting commit cycle.
1989 |
1990 | MODIFICATION HISTORY
1991 | Date Author Description of Change
1992 | 04-May-2011 Uday Phadtare Bug 12419110. Disable formula security for the session.
1993 *===========================================================================*/
1994 PROCEDURE extract
1995 ( p_application_id IN NUMBER
1996 , p_accounting_mode IN VARCHAR2
1997 )
1998 IS
1999
2000 BEGIN
2001 --NULL;
2002
2003 g_log_msg := 'Begin of procedure GMF_XLA_PKG.extract';
2004 print_debug(g_log_msg);
2005
2006 /* Begin Bug 12419110 */
2007 BEGIN
2008 gmd_p_fs_context.set_additional_attr; --Disable formula security
2009 EXCEPTION
2010 WHEN OTHERS THEN
2011 RAISE_APPLICATION_ERROR(-20101, NULL);
2012 END;
2013 /* End Bug 12419110 */
2014
2015 /*
2016 CASE p_process_category
2017 WHEN G_inventory_transactions
2018 THEN
2019 process_inv_txns(G_extract);
2020 WHEN G_purchasing_transactions
2021 THEN
2022 process_pur_txns(G_extract);
2023 WHEN G_production_transactions
2024 THEN
2025 process_pm_txns(G_extract);
2026 WHEN G_order_management
2027 THEN
2028 process_om_txns(G_extract);
2029 WHEN G_revaluation_transactions
2030 THEN
2031 process_rval_txns(G_extract);
2032 END CASE;
2033 */
2034
2035 /* Begin Bug 12419110 */
2036 g_log_msg := 'End of procedure GMF_XLA_PKG.extract';
2037 print_debug(g_log_msg);
2038
2039 END extract;
2040
2041
2042 /*============================================================================
2043 | PROCEDURE - POSTACCOUNTING(PUBLIC)
2044 |
2045 | DESCRIPTION
2046 | This procedure is the AP SLA post-accounting procedure. This procedure
2047 | will be called by SLA through an API.
2048 |
2049 | PRAMETERS
2050 | p_application_id
2051 | This parameter is the application ID of the application that the SLA
2052 | workflow event is for. This procedure must exit without doing anything
2053 | if this parameter is not 200 to ensure that this procedure is only
2054 | executed when the workflow event is for AP. This parameter will never
2055 | be NULL.
2056 | p_ledger_id
2057 | This parameter is the ledger ID of the ledger to account. This
2058 | parameter is purely informational. This procedure selects from the
2059 | XLA_ENTITY_EVENTS_V view, which does not include events incompatible
2060 | with this parameter. This parameter will never be NULL.
2061 | p_process_category
2062 | This parameter is the "process category" of the events to account.
2063 | This parameter is purely informational. This procedure selects from
2064 | the XLA_ENTITY_EVENTS_V view, which does not include events
2065 | incompatible with this parameter.Possible values are as following:
2066 | +------------+-------------------------------+
2067 | | Value | Meaning |
2068 | +------------+-------------------------------+
2069 | | 'Invoices' | process invoices |
2070 | | 'Payments' | process payments and receipts |
2071 | | 'All' | process everything |
2072 | +------------+-------------------------------+
2073 | p_end_date
2074 | This parameter is the maximum event date of the events to be processed
2075 | in this run of the accounting. This procedure selects from the
2076 | XLA_ENTITY_EVENTS_V view, which does not include events incompatible
2077 | with this parameter. This parameter will never be NULL.
2078 | p_accounting_mode
2079 | This parameter is the "accounting mode" that the accounting is being
2080 | run in. This parameter will never be NULL.
2081 | +-------+-------------------------------------------------------------+
2082 | | Value | Meaning |
2083 | +-------+-------------------------------------------------------------+
2084 | | 'D' | The accounting is being run in "draft mode". Draft mode is |
2085 | | | used to examine what the accounting entries would look for |
2086 | | | an event without actually creating the accounting entries. |
2087 | | 'F' | The accounting is being run in "final mode". Final mode is |
2088 | | | used to create accounting entries. |
2089 | +-------+-------------------------------------------------------------+
2090 | p_valuation_method
2091 | This parameter is unused by AP. This parameter is purely informational
2092 | This procedure selects from the XLA_ENTITY_EVENTS_V view, which does
2093 | not include events incompatible with this parameter.
2094 | p_security_id_int_1
2095 | This parameter is unused by AP.
2096 | p_security_id_int_2
2097 | This parameter is unused by AP.
2098 | p_security_id_int_3
2099 | This parameter is unused by AP.
2100 | p_security_id_char_1
2101 | This parameter is unused by AP.
2102 | p_security_id_char_2
2103 | This parameter is unused by AP.
2104 | p_security_id_char_3
2105 | This parameter is unused by AP.
2106 | p_report_request_id
2107 | This parameter is the concurrent request ID of the concurrent request
2108 | that is this run of the accounting. This parameter is used to specify
2109 | which events in the XLA_ENTITY_EVENTS_V view are to be accounted in
2110 | this run of the accounting. This parameter will never be NULL.
2111 |
2112 | KNOWN ISSUES:
2113 |
2114 | NOTES:
2115 | 1) This procedure is run in final mode and draft mode.
2116 | 2) This procedure is run in batch mode but not in document mode.
2117 | 3) This procedure is in its own commit cycle.
2118 |
2119 | MODIFICATION HISTORY
2120 | Date Author Description of Change
2121 |
2122 *===========================================================================*/
2123 PROCEDURE postaccounting
2124 ( p_application_id IN NUMBER
2125 , p_ledger_id IN NUMBER
2126 , p_process_category IN VARCHAR2
2127 , p_end_date IN DATE
2128 , p_accounting_mode IN VARCHAR2
2129 , p_valuation_method IN VARCHAR2
2130 , p_security_id_int_1 IN NUMBER
2131 , p_security_id_int_2 IN NUMBER
2132 , p_security_id_int_3 IN NUMBER
2133 , p_security_id_char_1 IN VARCHAR2
2134 , p_security_id_char_2 IN VARCHAR2
2135 , p_security_id_char_3 IN VARCHAR2
2136 , p_report_request_id IN NUMBER
2137 )
2138 IS
2139
2140 BEGIN
2141 --NULL;
2142
2143 /* Begin Bug 12419110 */
2144 g_log_msg := 'Begin of procedure GMF_XLA_PKG.postaccounting';
2145 print_debug(g_log_msg);
2146
2147 /*
2148 CASE p_process_category
2149 WHEN G_inventory_transactions
2150 THEN
2151 process_inv_txns(G_post_accounting);
2152 WHEN G_purchasing_transactions
2153 THEN
2154 process_pur_txns(G_post_accounting);
2155 WHEN G_production_transactions
2156 THEN
2157 process_pm_txns(G_post_accounting);
2158 WHEN G_order_management
2159 THEN
2160 process_om_txns(G_post_accounting);
2161 WHEN G_revaluation_transactions
2162 THEN
2163 process_rval_txns(G_post_accounting);
2164 END CASE;
2165 */
2166
2167 /* Begin Bug 12419110 */
2168 g_log_msg := 'End of procedure GMF_XLA_PKG.postaccounting';
2169 print_debug(g_log_msg);
2170
2171 END postaccounting;
2172
2173 /*============================================================================
2174 | PROCEDURE - POSTPROCESSING (PUBLIC)
2175 |
2176 | DESCRIPTION
2177 | This procedure is the AP SLA post-processing procedure. This procedure
2178 | will be called by SLA thorugh an API.
2179 |
2180 | The XLA_POST_ACCTG_EVENTS_V view contains only the successfully accounted
2181 | events.
2182 |
2183 | PRAMETERS
2184 | p_application_id
2185 | This parameter is the application ID of the application that the SLA
2186 | workflow event is for. This procedure must exit without doing anything
2187 | if this parameter is not 200 to ensure that this procedure is only
2188 | executed when the workflow event is for AP. This parameter will never
2189 | be NULL.
2190 | p_accounting_mode
2191 | This parameter is the "accounting mode" that the accounting is being
2192 | run in. This parameter will never be NULL.
2193 | +-------+-----------------------------------------------------------+
2194 | | Value | Meaning |
2195 | +-------+-----------------------------------------------------------+
2196 | | 'D' | The accounting is being run in "draft mode". Draft mode is|
2197 | | | used TO examine what the accounting entries would look for|
2198 | | | an event without actually creating the accounting entries |
2199 | | 'F' | The accounting is being run in "final mode". Final mode is|
2200 | | | used to create accounting entries. |
2201 | +-------+-----------------------------------------------------------+
2202 |
2203 | KNOWN ISSUES:
2204 |
2205 | NOTES:
2206 | 1) This procedure is run in final mode and draft mode.
2207 | 2) This procedure is run in batch mode and document mode.
2208 | 3) This procedure is part of the accounting commit cycle.
2209 |
2210 | MODIFICATION HISTORY
2211 | Date Author Description of Change
2212 |
2213 *===========================================================================*/
2214 PROCEDURE postprocessing
2215 ( p_application_id IN NUMBER
2216 , p_accounting_mode IN VARCHAR2
2217 )
2218 IS
2219
2220 l_procedure_name CONSTANT VARCHAR2(100):= g_module_name || 'postprocessing';
2221
2222 BEGIN
2223
2224 g_log_msg := 'Begin of procedure '|| l_procedure_name;
2225
2226 print_debug(g_log_msg);
2227 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
2228 THEN
2229 FND_LOG.STRING(
2230 log_level => g_level_procedure,
2231 module => g_module_name || l_procedure_name,
2232 message => g_log_msg
2233 );
2234 END IF;
2235
2236
2237 g_log_msg := 'Parameters: application_id = ' || p_application_id ||
2238 ' p_accounting_mode = ' || p_accounting_mode;
2239
2240 print_debug(g_log_msg);
2241 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
2242 THEN
2243 FND_LOG.STRING(
2244 log_level => g_level_procedure,
2245 module => g_module_name || l_procedure_name,
2246 message => g_log_msg
2247 );
2248 END IF;
2249
2250 ---------------------------------------------------------------------
2251 -- This procedure should only called for GMF (application id = 555)
2252 -- Otherwise exit the procedure.
2253 ---------------------------------------------------------------------
2254 IF (p_application_id <> 555) THEN
2255
2256 g_log_msg := 'Invalid application id ' || p_application_id || ' passed. exiting';
2257
2258 print_debug(g_log_msg);
2259 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
2260 THEN
2261 FND_LOG.STRING(
2262 log_level => g_level_procedure,
2263 module => g_module_name || l_procedure_name,
2264 message => g_log_msg
2265 );
2266 END IF;
2267 RETURN;
2268 END IF;
2269
2270 ---------------------------------------------------------------------
2271 -- only accept 'D' (draft) or 'F' (final) mode
2272 ---------------------------------------------------------------------
2273 IF p_accounting_mode NOT IN ('D', 'F')
2274 THEN
2275
2276 g_log_msg := 'Invalid Accounting mode ' || p_accounting_mode || ' for this procedure';
2277
2278 print_debug(g_log_msg);
2279 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
2280 THEN
2281 FND_LOG.STRING(
2282 log_level => g_level_procedure,
2283 module => g_module_name || l_procedure_name,
2284 message => g_log_msg
2285 );
2286 END IF;
2287 RETURN;
2288 END IF;
2289
2290 G_accounting_mode := p_accounting_mode;
2291
2292 ---------------------------------------------------------------------
2293 -- Now update extract header, transaction valuation and mmt to set
2294 -- costed flag.
2295 -- We need to loop for each process category as this is not a mandatory
2296 -- parameter to Create Accounting program.
2297 ---------------------------------------------------------------------
2298 FOR i in (SELECT DISTINCT process_category
2299 FROM gmf_xla_event_model em
2300 WHERE exists (SELECT 'X'
2301 FROM xla_post_acctg_events_v ae
2302 WHERE ae.event_class_code = em.event_class_code)
2303 )
2304 LOOP
2305
2306 g_log_msg := 'Post-Processing for '|| i.process_category;
2307
2308 print_debug(g_log_msg);
2309 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
2310 THEN
2311 FND_LOG.STRING(
2312 log_level => g_level_procedure,
2313 module => g_module_name || l_procedure_name,
2314 message => g_log_msg
2315 );
2316 END IF;
2317
2318 CASE i.process_category
2319
2320 ---------------------------------------------------------------------
2321 -- Inventory Transactions
2322 ---------------------------------------------------------------------
2323 WHEN G_inventory_transactions
2324 THEN
2325 process_inv_txns(G_post_processing);
2326
2327 ---------------------------------------------------------------------
2328 -- OPM Production transactions
2329 ---------------------------------------------------------------------
2330 WHEN G_production_transactions
2331 THEN
2332 process_pm_txns(G_post_processing);
2333
2334 ---------------------------------------------------------------------
2335 -- Purchasing Transactions
2336 ---------------------------------------------------------------------
2337 WHEN G_purchasing_transactions
2338 THEN
2339 process_pur_txns(G_post_processing);
2340
2341 ---------------------------------------------------------------------
2342 -- Order Management Transactions
2343 ---------------------------------------------------------------------
2344 WHEN G_order_management
2345 THEN
2346 process_om_txns(G_post_processing);
2347
2348 ---------------------------------------------------------------------
2349 -- Cost Reval and Lot Cost Adjustment Transactions
2350 ---------------------------------------------------------------------
2351 WHEN G_revaluation_transactions
2352 THEN
2353 process_rval_txns(G_post_processing);
2354 END CASE;
2355
2356 END LOOP;
2357
2358 g_log_msg := 'End of procedure '|| l_procedure_name;
2359
2360 print_debug(g_log_msg);
2361 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
2362 THEN
2363 FND_LOG.STRING(
2364 log_level => g_level_procedure,
2365 module => g_module_name || l_procedure_name,
2366 message => g_log_msg
2367 );
2368 END IF;
2369
2370 END postprocessing;
2371
2372 /*============================================================================
2373 | PROCEDURE - process_inv_txns
2374 |
2375 | DESCRIPTION
2376 | This procedure processes all inventory transactions. Right now we are
2377 | using this procedure for post-processing, but we can use it for other
2378 | processing events (pre-processing, extract and post-accounting).
2379 |
2380 | PRAMETERS
2381 |
2382 | KNOWN ISSUES:
2383 |
2384 | NOTES:
2385 |
2386 | MODIFICATION HISTORY
2387 | Date Author Description of Change
2388 | 01-Oct-2005 umoogala Genesis
2389 *===========================================================================*/
2390 PROCEDURE process_inv_txns(p_event VARCHAR2)
2391 IS
2392 l_procedure_name CONSTANT VARCHAR2(100) := 'process_inv_txns';
2393
2394 BEGIN
2395
2396 g_log_msg := 'Begin of procedure ' || l_procedure_name || ' for event ' || p_event;
2397
2398 print_debug(g_log_msg);
2399
2400 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
2401 THEN
2402 FND_LOG.STRING(
2403 log_level => g_level_statement,
2404 module => g_module_name || l_procedure_name,
2405 message => g_log_msg
2406 );
2407 END IF;
2408
2409 CASE p_event
2410 WHEN G_pre_accounting
2411 THEN
2412 NULL;
2413 WHEN G_extract
2414 THEN
2415 NULL;
2416 WHEN G_post_accounting
2417 THEN
2418 NULL;
2419 WHEN G_post_processing
2420 THEN
2421
2422 g_log_msg := 'Updating gmf_xla_extract_headers table';
2423
2424 print_debug(g_log_msg);
2425 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
2426 THEN
2427 FND_LOG.STRING(
2428 log_level => g_level_statement,
2429 module => g_module_name || l_procedure_name,
2430 message => g_log_msg
2431 );
2432 END IF;
2433
2434 --
2435 -- Update extract Headers
2436 --
2437 UPDATE gmf_xla_extract_headers
2438 SET accounted_flag = DECODE(G_accounting_mode, 'D', 'D', 'F', NULL)
2439 , actual_posting_date = DECODE(G_accounting_mode, 'F', sysdate, NULL)
2440 , last_update_date = sysdate
2441 , last_updated_by = g_user_id
2442 , last_update_login = g_login_id
2443 , program_application_id= g_prog_appl_id
2444 , program_id = g_program_id
2445 , request_id = g_request_id
2446 WHERE (transaction_id, ledger_id, valuation_cost_type_id, event_class_code)
2447 in (SELECT
2448 xpae.SOURCE_ID_INT_1, xpae.SOURCE_ID_INT_2,
2449 xpae.SOURCE_ID_INT_3, xpae.SOURCE_ID_CHAR_1
2450 FROM
2451 xla_post_acctg_events_v xpae,
2452 gmf_xla_event_model gxem
2453 WHERE
2454 gxem.process_category = G_inventory_transactions
2455 and xpae.event_class_code = gxem.event_class_code
2456 )
2457 ;
2458 g_log_msg := sql%rowcount || ' rows updated in gmf_xla_extract_headers table';
2459
2460 print_debug(g_log_msg);
2461 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
2462 THEN
2463 FND_LOG.STRING(
2464 log_level => g_level_statement,
2465 module => g_module_name || l_procedure_name,
2466 message => g_log_msg
2467 );
2468 END IF;
2469
2470
2471 --
2472 -- Update transaction valuation table
2473 --
2474 g_log_msg := 'Updating gmf_transaction_valuation table';
2475
2476 print_debug(g_log_msg);
2477 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
2478 THEN
2479 FND_LOG.STRING(
2480 log_level => g_level_statement,
2481 module => g_module_name || l_procedure_name,
2482 message => g_log_msg
2483 );
2484 END IF;
2485
2486 UPDATE gmf_transaction_valuation
2487 SET accounted_flag = DECODE(G_accounting_mode, 'D', 'D', 'F', NULL)
2488 , final_posting_date = DECODE(G_accounting_mode, 'F', sysdate, NULL)
2489 , last_update_date = sysdate
2490 , last_updated_by = g_user_id
2491 , last_update_login = g_login_id
2492 , program_application_id= g_prog_appl_id
2493 , program_id = g_program_id
2494 , request_id = g_request_id
2495 WHERE
2496 (transaction_id, ledger_id, valuation_cost_type_id, event_class_code)
2497 in (SELECT
2498 xpae.SOURCE_ID_INT_1, xpae.SOURCE_ID_INT_2,
2499 xpae.SOURCE_ID_INT_3, xpae.SOURCE_ID_CHAR_1
2500 FROM
2501 xla_post_acctg_events_v xpae,
2502 gmf_xla_event_model gxem
2503 WHERE
2504 gxem.process_category = G_inventory_transactions
2505 and xpae.event_class_code = gxem.event_class_code
2506 )
2507 AND ACCOUNTED_FLAG IS NOT NULL -- B7395353 Rajesh Patangya
2508 ;
2509 g_log_msg := sql%rowcount || ' rows updated in gmf_transaction_valuation table';
2510 print_debug(g_log_msg);
2511 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
2512 THEN
2513 FND_LOG.STRING(
2514 log_level => g_level_statement,
2515 module => g_module_name || l_procedure_name,
2516 message => g_log_msg
2517 );
2518 END IF;
2519
2520
2521 --
2522 -- Update transaction valuation table
2523 --
2524 g_log_msg := 'Updating mtl_material_transactions table';
2525
2526 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
2527 THEN
2528 FND_LOG.STRING(
2529 log_level => g_level_statement,
2530 module => g_module_name || l_procedure_name,
2531 message => g_log_msg
2532 );
2533 END IF;
2534 -- We need to do a single update to mtl_material_transactions so that
2535 -- session will perform all updates together. If any other session has updated any of the records session will wait
2536 -- until lock is available eliminating dead lock.
2537 -- Old queries PK Bug 9066162. These queries are now used to insert data in gmf_inv_Txn_flags_gt
2538 -- Q1
2539 /* UPDATE mtl_material_transactions
2540 SET opm_costed_flag = DECODE(G_accounting_mode, 'D', 'D', 'F', NULL)
2541 , program_update_date = SYSDATE
2542 , last_updated_by = g_user_id
2543 , last_update_login = g_login_id
2544 , program_application_id= g_prog_appl_id
2545 , program_id = g_program_id
2546 , request_id = g_request_id
2547 WHERE transaction_id
2548 IN (SELECT
2549 xpae.SOURCE_ID_INT_1
2550 FROM
2551 xla_post_acctg_events_v xpae,
2552 gmf_xla_event_model gxem
2553 WHERE
2554 gxem.process_category = G_inventory_transactions
2555 AND xpae.event_class_code = gxem.event_class_code
2556 AND xpae.event_class_code NOT IN ('FOB_RCPT_SENDER_RCPT', 'FOB_SHIP_RECIPIENT_SHIP') ); */
2557 -- Q2 performed only when G_accounting_mode = 'F'
2558 /* UPDATE mtl_material_transactions
2559 SET opm_costed_flag = NULL
2560 , program_update_date = SYSDATE
2561 , last_updated_by = g_user_id
2562 , last_update_login = g_login_id
2563 , program_application_id= g_prog_appl_id
2564 , program_id = g_program_id
2565 , request_id = g_request_id
2566 WHERE transaction_source_type_id in (2, 4, 8, 9, 10, 13)
2567 AND transaction_action_id in (2, 28)
2568 AND transfer_transaction_id
2569 in (SELECT
2570 xpae.SOURCE_ID_INT_1
2571 FROM
2572 xla_post_acctg_events_v xpae,
2573 gmf_xla_event_model gxem
2574 WHERE
2575 gxem.process_category = G_inventory_transactions
2576 AND xpae.event_class_code = gxem.event_class_code
2577 AND xpae.event_class_code in ('SUBINV_XFER')); */
2578 -- Q3 performed only when G_accounting_mode = 'F'
2579 /* UPDATE mtl_material_transactions
2580 SET shipment_costed = 'Y'
2581 , program_update_date = SYSDATE
2582 , last_updated_by = g_user_id
2583 , last_update_login = g_login_id
2584 , program_application_id= g_prog_appl_id
2585 , program_id = g_program_id
2586 , request_id = g_request_id
2587 WHERE transaction_source_type_id in (7, 8, 13)
2588 AND transaction_action_id in (12, 21)
2589 AND transaction_id
2590 in (SELECT
2591 xpae.SOURCE_ID_INT_1
2592 FROM
2593 xla_post_acctg_events_v xpae,
2594 gmf_xla_event_model gxem
2595 WHERE
2596 gxem.process_category = G_inventory_transactions
2597 AND xpae.event_class_code = gxem.event_class_code
2598 AND xpae.event_class_code in ('FOB_RCPT_SENDER_RCPT', 'FOB_SHIP_RECIPIENT_SHIP')); */
2599
2600 /* There may be multiple sessions. Q1 is specific to current session. Q2 and Q3 are performed only when accouting mode is final.
2601 Q2 should not be a problem since there is only one event for Subinventory transfer. We do not have two separate events for
2602 Subinventory transfer. These multiple updates caused deadlock based on how events were distributed amongst workers.
2603 Old queries are now used to insert data in gmf_inv_Txn_flags_gt. And a single update is used to update all mmt rows.
2604 Bug 9066162 new logic follows. We need to sort duplicates. Union can not be used since hard coded values are used for
2605 opm_costed_flag and shipment_costed. Events for these may or may not be processed by the same worker.
2606 */
2607
2608
2609 Insert into gmf_inv_Txn_flags_gt (transaction_id, opm_costed_flag, shipment_costed)
2610 SELECT DISTINCT xpae.SOURCE_ID_INT_1 transaction_id,
2611 DECODE(G_accounting_mode, 'D', 'D', 'F', NULL) opm_costed_flag,
2612 NULL shipment_costed
2613 FROM xla_post_acctg_events_v xpae,
2614 gmf_xla_event_model gxem
2615 WHERE gxem.process_category = G_inventory_transactions
2616 AND xpae.event_class_code = gxem.event_class_code
2617 AND xpae.event_class_code NOT IN ('FOB_RCPT_SENDER_RCPT', 'FOB_SHIP_RECIPIENT_SHIP')
2618 UNION ALL
2619 SELECT DISTINCT mmt1.transfer_transaction_id, NULL, NULL
2620 FROM mtl_material_transactions mmt1
2621 WHERE G_accounting_mode = 'F'
2622 AND mmt1.transaction_source_type_id in (2, 4, 8, 9, 10, 13)
2623 AND mmt1.transaction_action_id in (2, 28)
2624 AND mmt1.transaction_id IN
2625 (SELECT xpae.SOURCE_ID_INT_1
2626 FROM xla_post_acctg_events_v xpae,
2627 gmf_xla_event_model gxem
2628 WHERE gxem.process_category = G_inventory_transactions
2629 AND xpae.event_class_code = gxem.event_class_code
2630 AND xpae.event_class_code in ('SUBINV_XFER')
2631 )
2632 UNION ALL
2633 SELECT DISTINCT mmt2.transaction_id, 'N', 'Y'
2634 FROM mtl_material_transactions mmt2
2635 WHERE G_accounting_mode = 'F'
2636 AND mmt2.transaction_source_type_id in (7, 8, 13)
2637 AND mmt2.transaction_action_id in (12, 21)
2638 AND mmt2.transaction_id IN
2639 (SELECT xpae.SOURCE_ID_INT_1
2640 FROM xla_post_acctg_events_v xpae,
2641 gmf_xla_event_model gxem
2642 WHERE gxem.process_category = G_inventory_transactions
2643 AND xpae.event_class_code = gxem.event_class_code
2644 AND xpae.event_class_code in ('FOB_RCPT_SENDER_RCPT', 'FOB_SHIP_RECIPIENT_SHIP'))
2645 ;
2646
2647 -- One session can get both events for shipment and Intransit. In such case there will be duplicate rows in
2648 -- gmf_inv_Txn_flags_gt. Find duplicates update and eliminate duplicates
2649
2650
2651 Update gmf_inv_Txn_flags_gt
2652 set opm_costed_flag = NULL,
2653 shipment_costed = 'Y'
2654 where transaction_id IN (select transaction_id
2655 from gmf_inv_Txn_flags_gt
2656 group by transaction_id
2657 having count(transaction_id) > 1);
2658
2659
2660 IF sql%rowcount > 0 THEN
2661
2662 delete from gmf_inv_Txn_flags_gt
2663 where rowid IN (select min(rowid) from gmf_inv_Txn_flags_gt
2664 group by transaction_id
2665 having count(transaction_id) > 1);
2666
2667 END IF;
2668
2669
2670 -- Bug 9066162 New logic. Uses sigle update for all transactions to eliminate deadlock.
2671 -- If any transaction/s are updated by another session this will wait, replacing possible deadlock with a wait event.
2672 -- Now Update mmt flags. We do not want to overwrite flags if written by another session Thus decode is used in select query.
2673 -- for both opm_costed_flag and shipment_costed.
2674
2675 update mtl_material_transactions mmt
2676 set (opm_costed_flag, shipment_costed) = (select decode(mmt.opm_costed_flag,NULL,NULL,'D',decode(txngt.opm_costed_flag, NULL, NULL,'D'),'N',txngt.opm_costed_flag)
2677 ,decode(mmt.shipment_costed,'Y','Y',decode(txngt.shipment_costed,NULL,NULL,txngt.shipment_costed))
2678 from gmf_inv_Txn_flags_gt txngt
2679 where mmt.transaction_id = txngt.transaction_id)
2680 , program_update_date = SYSDATE
2681 /* Vpedarla bug: 9292668 commenting last_updated_by and last_update_login update*/
2682 -- , last_updated_by = g_user_id
2683 -- , last_update_login = g_login_id
2684 , program_application_id= g_prog_appl_id
2685 , program_id = g_program_id
2686 , request_id = g_request_id
2687 where transaction_id IN (select txngt.transaction_id from gmf_inv_Txn_flags_gt txngt);
2688
2689
2690 g_log_msg := sql%rowcount || ' rows of Inv Transactions updated to set OPM_costed_flag and shipment_costed flag in mtl_material_transactions table';
2691
2692 print_debug(g_log_msg);
2693 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
2694 THEN
2695 FND_LOG.STRING(
2696 log_level => g_level_statement,
2697 module => g_module_name || l_procedure_name,
2698 message => g_log_msg
2699 );
2700 END IF;
2701
2702 ELSE
2703 g_log_msg := 'Invalid event passed';
2704
2705 print_debug(g_log_msg);
2706 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
2707 THEN
2708 FND_LOG.STRING(
2709 log_level => g_level_statement,
2710 module => g_module_name || l_procedure_name,
2711 message => g_log_msg
2712 );
2713 END IF;
2714 RETURN;
2715 END CASE;
2716
2717 g_log_msg := 'End of procedure ' || l_procedure_name || ' for event ' || p_event;
2718
2719 print_debug(g_log_msg);
2720 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
2721 THEN
2722 FND_LOG.STRING(
2723 log_level => g_level_statement,
2724 module => g_module_name || l_procedure_name,
2725 message => g_log_msg
2726 );
2727 END IF;
2728
2729 END process_inv_txns;
2730
2731
2732 /*============================================================================
2733 | PROCEDURE - process_pur_txns
2734 |
2735 | DESCRIPTION
2736 | This procedure processes all inventory transactions. Right now we are
2737 | using this procedure for post-processing, but we can use it for other
2738 | processing events (pre-processing, extract and post-accounting).
2739 |
2740 | PRAMETERS
2741 |
2742 | KNOWN ISSUES:
2743 |
2744 | NOTES:
2745 |
2746 | MODIFICATION HISTORY
2747 | Date Author Description of Change
2748 | 01-Oct-2005 umoogala Genesis
2749 | 27-Feb-2007 pmarada Bug 5436974, Invoices enhancement, added code to
2750 | update gmf_invoice_distributions table
2751 | 5-Aug-2009 pmarada bug 8642337 LCM-OPM Integration, updating LC adjustment
2752 | tables accounted flag and final accounted date.
2753 | 29-Sep-2011 phiriyan Bug 13033147. Modified not to update MMT row when
2754 | event class / transaction type is related to Receiving txn.
2755 *===========================================================================*/
2756 PROCEDURE process_pur_txns(p_event VARCHAR2)
2757 IS
2758 l_procedure_name CONSTANT VARCHAR2(100) := 'process_pur_txns';
2759
2760 record_count INTEGER ;
2761
2762 BEGIN
2763
2764 g_log_msg := 'Begin of procedure ' || l_procedure_name || ' for event ' || p_event;
2765
2766 print_debug(g_log_msg);
2767 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
2768 THEN
2769 FND_LOG.STRING(
2770 log_level => g_level_statement,
2771 module => g_module_name || l_procedure_name,
2772 message => g_log_msg
2773 );
2774 END IF;
2775
2776 CASE p_event
2777 WHEN G_pre_accounting
2778 THEN
2779 NULL;
2780 WHEN G_extract
2781 THEN
2782 NULL;
2783 WHEN G_post_accounting
2784 THEN
2785 NULL;
2786 WHEN G_post_processing
2787 THEN
2788 g_log_msg := 'Updating gmf_xla_extract_headers table';
2789
2790 /* Bug 15949925 No need to call this. Done in pre-processor.
2791
2792 g_log_msg := 'Deleting Duplicate IPV/ERV xla entries';
2793
2794 select count(*) into record_count from xla_events_int_gt ;
2795
2796 g_log_msg := g_log_msg || 'Record count of xla_events_int_gt is ' || record_count ;
2797
2798 print_debug(g_log_msg);
2799
2800 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
2801 THEN
2802 FND_LOG.STRING(
2803 log_level => g_level_statement,
2804 module => g_module_name || l_procedure_name,
2805 message => g_log_msg
2806 );
2807 END IF;
2808
2809 delete_IPV_ERV_DUP_RECORDS ;
2810
2811 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
2812 THEN
2813 FND_LOG.STRING(
2814 log_level => g_level_statement,
2815 module => g_module_name || l_procedure_name,
2816 message => g_log_msg
2817 );
2818 END IF;
2819 End Bug 15949925 end commenting code out. */
2820 --
2821 -- Update extract Headers
2822 --
2823 UPDATE gmf_xla_extract_headers
2824 SET accounted_flag = DECODE(G_accounting_mode, 'D', 'D', 'F', NULL)
2825 , actual_posting_date = DECODE(G_accounting_mode, 'F', sysdate, NULL)
2826 , last_update_date = sysdate
2827 , last_updated_by = g_user_id
2828 , last_update_login = g_login_id
2829 , program_application_id= g_prog_appl_id
2830 , program_id = g_program_id
2831 , request_id = g_request_id
2832 WHERE (transaction_id, ledger_id, valuation_cost_type_id, event_class_code)
2833 in (SELECT
2834 xpae.SOURCE_ID_INT_1, xpae.SOURCE_ID_INT_2,
2835 xpae.SOURCE_ID_INT_3, xpae.SOURCE_ID_CHAR_1
2836 FROM
2837 xla_post_acctg_events_v xpae,
2838 gmf_xla_event_model gxem
2839 WHERE
2840 gxem.process_category = G_purchasing_transactions
2841 and xpae.event_class_code = gxem.event_class_code
2842 )
2843 ;
2844 g_log_msg := sql%rowcount || ' rows updated in gmf_xla_extract_headers table';
2845
2846 print_debug(g_log_msg);
2847 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
2848 THEN
2849 FND_LOG.STRING(
2850 log_level => g_level_statement,
2851 module => g_module_name || l_procedure_name,
2852 message => g_log_msg
2853 );
2854 END IF;
2855
2856
2857 --
2858 -- Update transaction valuation table
2859 --
2860 g_log_msg := 'Updating gmf_transaction_valuation table';
2861
2862 print_debug(g_log_msg);
2863 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
2864 THEN
2865 FND_LOG.STRING(
2866 log_level => g_level_statement,
2867 module => g_module_name || l_procedure_name,
2868 message => g_log_msg
2869 );
2870 END IF;
2871
2872 UPDATE gmf_transaction_valuation
2873 SET accounted_flag = DECODE(G_accounting_mode, 'D', 'D', 'F', NULL)
2874 , final_posting_date = DECODE(G_accounting_mode, 'F', sysdate, NULL)
2875 , last_update_date = sysdate
2876 , last_updated_by = g_user_id
2877 , last_update_login = g_login_id
2878 , program_application_id= g_prog_appl_id
2879 , program_id = g_program_id
2880 , request_id = g_request_id
2881 WHERE
2882 (transaction_id, ledger_id, valuation_cost_type_id, event_class_code)
2883 in (SELECT
2884 xpae.SOURCE_ID_INT_1, xpae.SOURCE_ID_INT_2,
2885 xpae.SOURCE_ID_INT_3, xpae.SOURCE_ID_CHAR_1
2886 FROM
2887 xla_post_acctg_events_v xpae,
2888 gmf_xla_event_model gxem
2889 WHERE
2890 gxem.process_category = G_purchasing_transactions
2891 and xpae.event_class_code = gxem.event_class_code
2892 )
2893 AND ACCOUNTED_FLAG IS NOT NULL -- B7395353 Rajesh Patangya
2894 ;
2895 g_log_msg := sql%rowcount || ' rows updated in gmf_transaction_valuation table';
2896
2897 print_debug(g_log_msg);
2898 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
2899 THEN
2900 FND_LOG.STRING(
2901 log_level => g_level_statement,
2902 module => g_module_name || l_procedure_name,
2903 message => g_log_msg
2904 );
2905 END IF;
2906
2907
2908 --
2909 -- Update transaction valuation table
2910 --
2911 g_log_msg := 'Updating mtl_material_transactions table';
2912
2913 print_debug(g_log_msg);
2914 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
2915 THEN
2916 FND_LOG.STRING(
2917 log_level => g_level_statement,
2918 module => g_module_name || l_procedure_name,
2919 message => g_log_msg
2920 );
2921 END IF;
2922
2923 UPDATE mtl_material_transactions
2924 SET opm_costed_flag = DECODE(G_accounting_mode, 'D', 'D', 'F', NULL)
2925 , program_update_date = SYSDATE /* ANTHIYAG Updating Porgram_update_date instead of Last_update_date to avoid auditing issues */
2926 /* Vpedarla bug: 9292668 commenting last_updated_by and last_update_login update*/
2927 -- , last_updated_by = g_user_id
2928 -- , last_update_login = g_login_id
2929 , program_application_id= g_prog_appl_id
2930 , program_id = g_program_id
2931 , request_id = g_request_id
2932 WHERE transaction_id
2933 in (SELECT
2934 xpae.SOURCE_ID_INT_1
2935 FROM
2936 xla_post_acctg_events_v xpae,
2937 gmf_xla_event_model gxem
2938 WHERE
2939 gxem.process_category = G_purchasing_transactions
2940 and xpae.event_class_code = gxem.event_class_code
2941 /*Bug 13033147 - Added following condition to exclude Receiving txns from this update*/
2942 and gxem.transaction_source_type_id <> 999
2943 )
2944 ;
2945
2946 g_log_msg := sql%rowcount || ' rows updated in mtl_material_transactions table';
2947
2948 print_debug(g_log_msg);
2949 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
2950 THEN
2951 FND_LOG.STRING(
2952 log_level => g_level_statement,
2953 module => g_module_name || l_procedure_name,
2954 message => g_log_msg
2955 );
2956 END IF;
2957
2958 /* bug 4879803 start jboppana*/
2959
2960 g_log_msg := 'Updating gmf_rcv_accounting_txns table';
2961
2962 print_debug(g_log_msg);
2963 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
2964 THEN
2965 FND_LOG.STRING(
2966 log_level => g_level_statement,
2967 module => g_module_name || l_procedure_name,
2968 message => g_log_msg
2969 );
2970 END IF;
2971
2972 UPDATE gmf_rcv_accounting_txns
2973 SET accounted_flag = DECODE(G_accounting_mode, 'D', 'D', 'F', NULL)
2974 , last_update_date = sysdate
2975 , last_updated_by = g_user_id
2976 , last_update_login = g_login_id
2977 , program_application_id= g_prog_appl_id
2978 , program_id = g_program_id
2979 , request_id = g_request_id
2980 WHERE accounting_txn_id
2981 in (SELECT
2982 xpae.SOURCE_ID_INT_1
2983 FROM
2984 xla_post_acctg_events_v xpae,
2985 gmf_xla_event_model gxem
2986 WHERE
2987 gxem.process_category = G_purchasing_transactions
2988 and gxem.transaction_type IN ('RECEIVING_RECEIVE','RECEIVING_DELIVER_EXPENSE',
2989 'RECEIVING_RET_TO_VENDOR','DELIVER_EXP_RET_TO_RECEIVING',
2990 'RECEIVING_LOG_RET_TO_VENDOR', 'RECEIVING_LOG_RECEIVE',
2991 'RECEIVING_ADJUST_RECEIVE', 'RECEIVING_ADJUST_DELIVER')
2992 and xpae.event_class_code = gxem.event_class_code
2993 )
2994 ;
2995
2996 g_log_msg := sql%rowcount || ' rows updated in gmf_rcv_accounting_txns table';
2997
2998 print_debug(g_log_msg);
2999 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
3000 THEN
3001 FND_LOG.STRING(
3002 log_level => g_level_statement,
3003 module => g_module_name || l_procedure_name,
3004 message => g_log_msg
3005 );
3006 END IF;
3007 /* bug 4879803 end jboppana*/
3008 --
3009 -- Bug 5436974 Start Invoices enhancement pmarada
3010 --
3011 g_log_msg := 'Updating gmf_invoice_distributions table';
3012
3013 print_debug(g_log_msg);
3014 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
3015 THEN
3016 FND_LOG.STRING(
3017 log_level => g_level_statement,
3018 module => g_module_name || l_procedure_name,
3019 message => g_log_msg
3020 );
3021 END IF;
3022 /* update the accounting */
3023 UPDATE gmf_invoice_distributions SET
3024 Accounted_flag = DECODE(G_accounting_mode, 'D', 'D', 'F', NULL )
3025 ,final_posting_date = DECODE (G_accounting_mode, 'F', sysdate, NULL)
3026 ,last_update_date = sysdate
3027 ,last_updated_by = g_user_id
3028 ,last_update_login = g_login_id
3029 ,program_application_id= g_prog_appl_id
3030 ,program_id = g_program_id
3031 ,request_id = g_request_id
3032 WHERE distribution_id
3033 IN (SELECT xpae.SOURCE_ID_INT_1
3034 FROM xla_post_acctg_events_v xpae,
3035 gmf_xla_event_model gxem
3036 WHERE gxem.process_category = G_purchasing_transactions
3037 AND gxem.transaction_type IN ('PAYABLES_INVOICE_IPV_ADJ','PAYABLES_INVOICE_ERV_ADJ')
3038 AND xpae.event_class_code = gxem.event_class_code
3039 );
3040
3041 g_log_msg := sql%rowcount || ' rows updated in gmf_invoice_distributions table';
3042
3043 print_debug(g_log_msg);
3044 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
3045 THEN
3046 FND_LOG.STRING(
3047 log_level => g_level_statement,
3048 module => g_module_name || l_procedure_name,
3049 message => g_log_msg
3050 );
3051 END IF;
3052 --
3053 -- End bug 5436974 Invoice enhancement
3054 --
3055
3056 -- Start LCM-OPM Integration
3057 -- Update gmf_lc_adj_transactions table
3058 g_log_msg := 'Updating gmf_lc_adj_transactions table';
3059
3060 print_debug(g_log_msg);
3061 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
3062 FND_LOG.STRING(
3063 log_level => g_level_statement,
3064 module => g_module_name || l_procedure_name,
3065 message => g_log_msg
3066 );
3067 END IF;
3068
3069 UPDATE gmf_lc_adj_transactions SET
3070 Accounted_flag = DECODE(G_accounting_mode, 'D', 'D', 'F', NULL )
3071 ,final_posting_date = DECODE (G_accounting_mode, 'F', sysdate, NULL)
3072 ,last_update_date = sysdate
3073 ,last_updated_by = g_user_id
3074 ,last_update_login = g_login_id
3075 ,program_application_id= g_prog_appl_id
3076 ,program_id = g_program_id
3077 ,request_id = g_request_id
3078 WHERE adj_transaction_id
3079 IN (SELECT xpae.SOURCE_ID_INT_1
3080 FROM xla_post_acctg_events_v xpae,
3081 gmf_xla_event_model gxem
3082 WHERE gxem.process_category = G_purchasing_transactions
3083 AND gxem.transaction_type IN ('LC_ADJUSTMENT_EXP_DELIVER','LC_ADJUSTMENT_DELIVER','LC_ADJUSTMENT_RECEIVE')
3084 AND xpae.event_class_code = gxem.event_class_code
3085 );
3086
3087 g_log_msg := sql%rowcount || ' rows updated in gmf_lc_adj_transactions table';
3088
3089 print_debug(g_log_msg);
3090 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
3091 FND_LOG.STRING(
3092 log_level => g_level_statement,
3093 module => g_module_name || l_procedure_name,
3094 message => g_log_msg
3095 );
3096 END IF;
3097
3098 -- Update gmf_lc_actual_cost_adjs table
3099 g_log_msg := 'Updating gmf_lc_actual_cost_adjs table';
3100
3101 print_debug(g_log_msg);
3102 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
3103 FND_LOG.STRING(
3104 log_level => g_level_statement,
3105 module => g_module_name || l_procedure_name,
3106 message => g_log_msg
3107 );
3108 END IF;
3109 UPDATE gmf_lc_actual_cost_adjs SET
3110 accounted_flag = DECODE(G_accounting_mode, 'D', 'D', 'F', NULL )
3111 ,final_posting_date = DECODE (G_accounting_mode, 'F', sysdate, NULL)
3112 ,last_update_date = sysdate
3113 ,last_updated_by = g_user_id
3114 ,last_update_login = g_login_id
3115 ,program_application_id= g_prog_appl_id
3116 ,program_id = g_program_id
3117 ,request_id = g_request_id
3118 WHERE adj_transaction_id
3119 IN (SELECT xpae.SOURCE_ID_INT_1
3120 FROM xla_post_acctg_events_v xpae,
3121 gmf_xla_event_model gxem
3122 WHERE gxem.process_category = G_purchasing_transactions
3123 AND gxem.transaction_type IN ('LC_ADJUSTMENT_VALUATION')
3124 AND xpae.event_class_code = gxem.event_class_code
3125 );
3126
3127 g_log_msg := sql%rowcount || ' rows updated in gmf_lc_actual_cost_adjs table';
3128 print_debug(g_log_msg);
3129 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
3130 FND_LOG.STRING(
3131 log_level => g_level_statement,
3132 module => g_module_name || l_procedure_name,
3133 message => g_log_msg
3134 );
3135 END IF;
3136
3137 -- Update gmf_lc_lot_cost_adjs table
3138 g_log_msg := 'Updating gmf_lc_lot_cost_adjs table';
3139
3140 print_debug(g_log_msg);
3141 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
3142 FND_LOG.STRING(
3143 log_level => g_level_statement,
3144 module => g_module_name || l_procedure_name,
3145 message => g_log_msg
3146 );
3147 END IF;
3148 UPDATE gmf_lc_lot_cost_adjs SET
3149 accounted_flag = DECODE(G_accounting_mode, 'D', 'D', 'F', NULL )
3150 ,final_posting_date = DECODE (G_accounting_mode, 'F', sysdate, NULL)
3151 ,last_update_date = sysdate
3152 ,last_updated_by = g_user_id
3153 ,last_update_login = g_login_id
3154 ,program_application_id= g_prog_appl_id
3155 ,program_id = g_program_id
3156 ,request_id = g_request_id
3157 WHERE adj_transaction_id
3158 IN (SELECT xpae.SOURCE_ID_INT_1
3159 FROM xla_post_acctg_events_v xpae,
3160 gmf_xla_event_model gxem
3161 WHERE gxem.process_category = G_purchasing_transactions
3162 AND gxem.transaction_type IN ('LC_ADJUSTMENT_VALUATION')
3163 AND xpae.event_class_code = gxem.event_class_code
3164 );
3165
3166 g_log_msg := sql%rowcount || ' rows updated in gmf_lc_lot_cost_adjs table';
3167
3168 print_debug(g_log_msg);
3169 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
3170 FND_LOG.STRING(
3171 log_level => g_level_statement,
3172 module => g_module_name || l_procedure_name,
3173 message => g_log_msg
3174 );
3175 END IF;
3176 -- End LCM-OPM Integration
3177
3178 ELSE
3179 g_log_msg := 'Invalid event passed';
3180
3181 print_debug(g_log_msg);
3182 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
3183 THEN
3184 FND_LOG.STRING(
3185 log_level => g_level_statement,
3186 module => g_module_name || l_procedure_name,
3187 message => g_log_msg
3188 );
3189 END IF;
3190 RETURN;
3191 END CASE;
3192
3193 g_log_msg := 'End of procedure ' || l_procedure_name || ' for event ' || p_event;
3194
3195 print_debug(g_log_msg);
3196 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
3197 THEN
3198 FND_LOG.STRING(
3199 log_level => g_level_statement,
3200 module => g_module_name || l_procedure_name,
3201 message => g_log_msg
3202 );
3203 END IF;
3204 END process_pur_txns;
3205
3206 /*============================================================================
3207 | PROCEDURE - process_pm_txns
3208 |
3209 | DESCRIPTION
3210 | This procedure processes all inventory transactions. Right now we are
3211 | using this procedure for post-processing, but we can use it for other
3212 | processing events (pre-processing, extract and post-accounting).
3213 |
3214 | PRAMETERS
3215 |
3216 | KNOWN ISSUES:
3217 |
3218 | NOTES:
3219 |
3220 | MODIFICATION HISTORY
3221 | Date Author Description of Change
3222 | 01-Oct-2005 umoogala Genesis
3223 *===========================================================================*/
3224 PROCEDURE process_pm_txns(p_event VARCHAR2)
3225 IS
3226 l_procedure_name CONSTANT VARCHAR2(100) := 'process_pm_txns';
3227 BEGIN
3228
3229 g_log_msg := 'Begin of procedure ' || l_procedure_name || ' for event ' || p_event;
3230
3231 print_debug(g_log_msg);
3232 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
3233 THEN
3234 FND_LOG.STRING(
3235 log_level => g_level_statement,
3236 module => g_module_name || l_procedure_name,
3237 message => g_log_msg
3238 );
3239 END IF;
3240
3241 CASE p_event
3242 WHEN G_pre_accounting
3243 THEN
3244 NULL;
3245 WHEN G_extract
3246 THEN
3247 NULL;
3248 WHEN G_post_accounting
3249 THEN
3250 NULL;
3251 WHEN G_post_processing
3252 THEN
3253 g_log_msg := 'Updating gmf_xla_extract_headers table';
3254
3255 print_debug(g_log_msg);
3256 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
3257 THEN
3258 FND_LOG.STRING(
3259 log_level => g_level_statement,
3260 module => g_module_name || l_procedure_name,
3261 message => g_log_msg
3262 );
3263 END IF;
3264
3265 --
3266 -- Update extract Headers
3267 --
3268 UPDATE gmf_xla_extract_headers
3269 SET accounted_flag = DECODE(G_accounting_mode, 'D', 'D', 'F', NULL)
3270 , actual_posting_date = DECODE(G_accounting_mode, 'F', sysdate, NULL)
3271 , last_update_date = sysdate
3272 , last_updated_by = g_user_id
3273 , last_update_login = g_login_id
3274 , program_application_id= g_prog_appl_id
3275 , program_id = g_program_id
3276 , request_id = g_request_id
3277 WHERE (transaction_id, ledger_id, valuation_cost_type_id, event_class_code)
3278 in (SELECT
3279 xpae.SOURCE_ID_INT_1, xpae.SOURCE_ID_INT_2,
3280 xpae.SOURCE_ID_INT_3, xpae.SOURCE_ID_CHAR_1
3281 FROM
3282 xla_post_acctg_events_v xpae,
3283 gmf_xla_event_model gxem
3284 WHERE
3285 gxem.process_category = G_production_transactions
3286 and xpae.event_class_code = gxem.event_class_code
3287 )
3288 ;
3289 g_log_msg := sql%rowcount || ' rows updated in gmf_xla_extract_headers table';
3290
3291 print_debug(g_log_msg);
3292 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
3293 THEN
3294 FND_LOG.STRING(
3295 log_level => g_level_statement,
3296 module => g_module_name || l_procedure_name,
3297 message => g_log_msg
3298 );
3299 END IF;
3300
3301
3302 --
3303 -- Update transaction valuation table
3304 --
3305 g_log_msg := 'Updating gmf_transaction_valuation table';
3306
3307 print_debug(g_log_msg);
3308 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
3309 THEN
3310 FND_LOG.STRING(
3311 log_level => g_level_statement,
3312 module => g_module_name || l_procedure_name,
3313 message => g_log_msg
3314 );
3315 END IF;
3316
3317 UPDATE gmf_transaction_valuation
3318 SET accounted_flag = DECODE(G_accounting_mode, 'D', 'D', 'F', NULL)
3319 , final_posting_date = DECODE(G_accounting_mode, 'F', sysdate, NULL)
3320 , last_update_date = sysdate
3321 , last_updated_by = g_user_id
3322 , last_update_login = g_login_id
3323 , program_application_id= g_prog_appl_id
3324 , program_id = g_program_id
3325 , request_id = g_request_id
3326 WHERE
3327 (transaction_id, ledger_id, valuation_cost_type_id, event_class_code)
3328 in (SELECT
3329 xpae.SOURCE_ID_INT_1, xpae.SOURCE_ID_INT_2,
3330 xpae.SOURCE_ID_INT_3, xpae.SOURCE_ID_CHAR_1
3331 FROM
3332 xla_post_acctg_events_v xpae,
3333 gmf_xla_event_model gxem
3334 WHERE
3335 gxem.process_category = G_production_transactions
3336 and xpae.event_class_code = gxem.event_class_code
3337 )
3338 AND ACCOUNTED_FLAG IS NOT NULL -- B7395353 Rajesh Patangya
3339 ;
3340 g_log_msg := sql%rowcount || ' rows updated in gmf_transaction_valuation table';
3341
3342 print_debug(g_log_msg);
3343 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
3344 THEN
3345 FND_LOG.STRING(
3346 log_level => g_level_statement,
3347 module => g_module_name || l_procedure_name,
3348 message => g_log_msg
3349 );
3350 END IF;
3351
3352
3353 --
3354 -- Update material transaction table
3355 --
3356 g_log_msg := 'Updating mtl_material_transactions table for batch material transactions';
3357
3358 print_debug(g_log_msg);
3359 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
3360 THEN
3361 FND_LOG.STRING(
3362 log_level => g_level_statement,
3363 module => g_module_name || l_procedure_name,
3364 message => g_log_msg
3365 );
3366 END IF;
3367
3368 UPDATE mtl_material_transactions
3369 SET opm_costed_flag = DECODE(G_accounting_mode, 'D', 'D', 'F', NULL)
3370 , program_update_date = SYSDATE /* ANTHIYAG Updating Porgram_update_date instead of Last_update_date to avoid auditing issues */
3371 /* Vpedarla bug: 9292668 commenting last_updated_by and last_update_login update*/
3372 -- , last_updated_by = g_user_id
3373 -- , last_update_login = g_login_id
3374 , program_application_id= g_prog_appl_id
3375 , program_id = g_program_id
3376 , request_id = g_request_id
3377 WHERE transaction_id
3378 in (SELECT
3379 xpae.SOURCE_ID_INT_1
3380 FROM
3381 xla_post_acctg_events_v xpae,
3382 gmf_xla_event_model gxem
3383 WHERE
3384 gxem.process_category = G_production_transactions
3385 and gxem.event_class_code = G_batch_material
3386 and xpae.event_class_code = gxem.event_class_code
3387 )
3388 ;
3389
3390 g_log_msg := sql%rowcount || ' rows updated in mtl_material_transactions table';
3391
3392 print_debug(g_log_msg);
3393 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
3394 THEN
3395 FND_LOG.STRING(
3396 log_level => g_level_statement,
3397 module => g_module_name || l_procedure_name,
3398 message => g_log_msg
3399 );
3400 END IF;
3401
3402
3403 --
3404 -- BatchesXperiods Enh. 12.0.1. - umoogala - Feb 2007
3405 -- Update incoming layers table
3406 --
3407 g_log_msg := 'Updating gmf_incoming_material_layers table';
3408
3409 print_debug(g_log_msg);
3410 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
3411 THEN
3412 FND_LOG.STRING(
3413 log_level => g_level_statement,
3414 module => g_module_name || l_procedure_name,
3415 message => g_log_msg
3416 );
3417 END IF;
3418
3419
3420 UPDATE gmf_incoming_material_layers
3421 SET accounted_flag = DECODE(G_accounting_mode, 'D', 'D', 'F', NULL)
3422 , actual_posting_date = DECODE(G_accounting_mode, 'F', sysdate, NULL)
3423 , last_update_date = sysdate
3424 , last_updated_by = g_user_id
3425 , last_update_login = g_login_id
3426 WHERE (mmt_transaction_id)
3427 in (SELECT
3428 xpae.SOURCE_ID_INT_1
3429 FROM
3430 xla_post_acctg_events_v xpae,
3431 gmf_xla_event_model gxem
3432 WHERE
3433 gxem.process_category = G_production_transactions
3434 and xpae.event_class_code = gxem.event_class_code
3435 )
3436 ;
3437
3438 g_log_msg := sql%rowcount || ' rows updated in gmf_incoming_material_layers table';
3439
3440 print_debug(g_log_msg);
3441 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
3442 THEN
3443 FND_LOG.STRING(
3444 log_level => g_level_statement,
3445 module => g_module_name || l_procedure_name,
3446 message => g_log_msg
3447 );
3448 END IF;
3449 -- End BatchesXperiods Enh. 12.0.1. - umoogala - Feb 2007
3450
3451
3452 IF G_accounting_mode = 'F'
3453 THEN
3454
3455 --
3456 -- Update resource transaction table
3457 -- Decide what Flag to use when run in Draft Mode.
3458 --
3459 g_log_msg := 'Updating gme_resource_txns table for batch material transactions';
3460
3461 print_debug(g_log_msg);
3462 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
3463 THEN
3464 FND_LOG.STRING(
3465 log_level => g_level_statement,
3466 module => g_module_name || l_procedure_name,
3467 message => g_log_msg
3468 );
3469 END IF;
3470
3471 UPDATE gme_resource_txns
3472 SET posted_ind = DECODE(G_accounting_mode, 'D', posted_ind, 'F', 1)
3473 , last_update_date = sysdate
3474 , last_updated_by = g_user_id
3475 , last_update_login = g_login_id
3476 , program_application_id= g_prog_appl_id
3477 , program_id = g_program_id
3478 , request_id = g_request_id
3479 WHERE poc_trans_id
3480 in (SELECT
3481 xpae.SOURCE_ID_INT_1
3482 FROM
3483 xla_post_acctg_events_v xpae,
3484 gmf_xla_event_model gxem
3485 WHERE
3486 gxem.process_category = G_production_transactions
3487 and gxem.event_class_code = G_batch_resource
3488 and xpae.event_class_code = gxem.event_class_code
3489 )
3490 ;
3491
3492 g_log_msg := sql%rowcount || ' rows updated in gme_resource_txns table';
3493
3494 print_debug(g_log_msg);
3495 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
3496 THEN
3497 FND_LOG.STRING(
3498 log_level => g_level_statement,
3499 module => g_module_name || l_procedure_name,
3500 message => g_log_msg
3501 );
3502 END IF;
3503
3504
3505 --
3506 -- Update Batch Header table
3507 --
3508 g_log_msg := 'Updating gme_batch_header table for batch material transactions';
3509
3510 print_debug(g_log_msg);
3511 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
3512 THEN
3513 FND_LOG.STRING(
3514 log_level => g_level_statement,
3515 module => g_module_name || l_procedure_name,
3516 message => g_log_msg
3517 );
3518 END IF;
3519
3520 UPDATE gme_batch_header
3521 SET gl_posted_ind = DECODE(G_accounting_mode, 'D', gl_posted_ind, 'F', 1)
3522 , last_update_date = sysdate
3523 , last_updated_by = g_user_id
3524 , last_update_login = g_login_id
3525 WHERE batch_id
3526 in (SELECT
3527 xpae.SOURCE_ID_INT_1
3528 FROM
3529 xla_post_acctg_events_v xpae,
3530 gmf_xla_event_model gxem
3531 WHERE
3532 gxem.process_category = G_production_transactions
3533 and gxem.event_class_code = g_batch_close
3534 and xpae.event_class_code = gxem.event_class_code
3535 )
3536 ;
3537
3538 g_log_msg := sql%rowcount || ' rows updated in gme_batch_header table';
3539
3540 print_debug(g_log_msg);
3541 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
3542 THEN
3543 FND_LOG.STRING(
3544 log_level => g_level_statement,
3545 module => g_module_name || l_procedure_name,
3546 message => g_log_msg
3547 );
3548 END IF;
3549
3550 END IF;
3551
3552 ELSE
3553 g_log_msg := 'Invalid event passed';
3554
3555 print_debug(g_log_msg);
3556 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
3557 THEN
3558 FND_LOG.STRING(
3559 log_level => g_level_statement,
3560 module => g_module_name || l_procedure_name,
3561 message => g_log_msg
3562 );
3563 END IF;
3564 RETURN;
3565 END CASE;
3566
3567 g_log_msg := 'End of procedure ' || l_procedure_name || ' for event ' || p_event;
3568
3569 print_debug(g_log_msg);
3570 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
3571 THEN
3572 FND_LOG.STRING(
3573 log_level => g_level_statement,
3574 module => g_module_name || l_procedure_name,
3575 message => g_log_msg
3576 );
3577 END IF;
3578 END process_pm_txns;
3579
3580 /*============================================================================
3581 | PROCEDURE - process_om_txns
3582 |
3583 | DESCRIPTION
3584 | This procedure processes all inventory transactions. Right now we are
3585 | using this procedure for post-processing, but we can use it for other
3586 | processing events (pre-processing, extract and post-accounting).
3587 |
3588 | PRAMETERS
3589 |
3590 | KNOWN ISSUES:
3591 |
3592 | NOTES:
3593 |
3594 | MODIFICATION HISTORY
3595 | Date Author Description of Change
3596 | 01-Oct-2005 umoogala Genesis
3597 *===========================================================================*/
3598 PROCEDURE process_om_txns(p_event VARCHAR2)
3599 IS
3600 l_procedure_name CONSTANT VARCHAR2(100) := 'process_om_txns';
3601 BEGIN
3602
3603 g_log_msg := 'Begin of procedure ' || l_procedure_name || ' for event ' || p_event;
3604
3605 print_debug(g_log_msg);
3606 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
3607 THEN
3608 FND_LOG.STRING(
3609 log_level => g_level_statement,
3610 module => g_module_name || l_procedure_name,
3611 message => g_log_msg
3612 );
3613 END IF;
3614
3615 CASE p_event
3616 WHEN G_pre_accounting
3617 THEN
3618 NULL;
3619 WHEN G_extract
3620 THEN
3621 NULL;
3622 WHEN G_post_accounting
3623 THEN
3624 NULL;
3625 WHEN G_post_processing
3626 THEN
3627 g_log_msg := 'Updating gmf_xla_extract_headers table';
3628
3629 print_debug(g_log_msg);
3630 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
3631 THEN
3632 FND_LOG.STRING(
3633 log_level => g_level_statement,
3634 module => g_module_name || l_procedure_name,
3635 message => g_log_msg
3636 );
3637 END IF;
3638
3639 --
3640 -- Update extract Headers
3641 --
3642 UPDATE gmf_xla_extract_headers
3643 SET accounted_flag = DECODE(G_accounting_mode, 'D', 'D', 'F', NULL)
3644 , actual_posting_date = DECODE(G_accounting_mode, 'F', sysdate, NULL)
3645 , last_update_date = sysdate
3646 , last_updated_by = g_user_id
3647 , last_update_login = g_login_id
3648 , program_application_id= g_prog_appl_id
3649 , program_id = g_program_id
3650 , request_id = g_request_id
3651 WHERE (transaction_id, ledger_id, valuation_cost_type_id, event_class_code)
3652 in (SELECT
3653 xpae.SOURCE_ID_INT_1, xpae.SOURCE_ID_INT_2,
3654 xpae.SOURCE_ID_INT_3, xpae.SOURCE_ID_CHAR_1
3655 FROM
3656 xla_post_acctg_events_v xpae,
3657 gmf_xla_event_model gxem
3658 WHERE
3659 gxem.process_category = G_order_management
3660 and xpae.event_class_code = gxem.event_class_code
3661 )
3662 ;
3663 g_log_msg := sql%rowcount || ' rows updated in gmf_xla_extract_headers table';
3664
3665 print_debug(g_log_msg);
3666 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
3667 THEN
3668 FND_LOG.STRING(
3669 log_level => g_level_statement,
3670 module => g_module_name || l_procedure_name,
3671 message => g_log_msg
3672 );
3673 END IF;
3674
3675
3676 --
3677 -- Update transaction valuation table
3678 --
3679 g_log_msg := 'Updating gmf_transaction_valuation table';
3680
3681 print_debug(g_log_msg);
3682 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
3683 THEN
3684 FND_LOG.STRING(
3685 log_level => g_level_statement,
3686 module => g_module_name || l_procedure_name,
3687 message => g_log_msg
3688 );
3689 END IF;
3690
3691 UPDATE gmf_transaction_valuation
3692 SET accounted_flag = DECODE(G_accounting_mode, 'D', 'D', 'F', NULL)
3693 , final_posting_date = DECODE(G_accounting_mode, 'F', sysdate, NULL)
3694 , last_update_date = sysdate
3695 , last_updated_by = g_user_id
3696 , last_update_login = g_login_id
3697 , program_application_id= g_prog_appl_id
3698 , program_id = g_program_id
3699 , request_id = g_request_id
3700 WHERE
3701 (transaction_id, ledger_id, valuation_cost_type_id, event_class_code)
3702 in (SELECT
3703 xpae.SOURCE_ID_INT_1, xpae.SOURCE_ID_INT_2,
3704 xpae.SOURCE_ID_INT_3, xpae.SOURCE_ID_CHAR_1
3705 FROM
3706 xla_post_acctg_events_v xpae,
3707 gmf_xla_event_model gxem
3708 WHERE
3709 gxem.process_category = G_order_management
3710 and xpae.event_class_code = gxem.event_class_code
3711 )
3712 AND ACCOUNTED_FLAG IS NOT NULL -- B7395353 Rajesh Patangya
3713 ;
3714 g_log_msg := sql%rowcount || ' rows updated in gmf_transaction_valuation table';
3715
3716 print_debug(g_log_msg);
3717 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
3718 THEN
3719 FND_LOG.STRING(
3720 log_level => g_level_statement,
3721 module => g_module_name || l_procedure_name,
3722 message => g_log_msg
3723 );
3724 END IF;
3725
3726
3727 --
3728 -- Update transaction valuation table
3729 --
3730 g_log_msg := 'Updating mtl_material_transactions table';
3731
3732 print_debug(g_log_msg);
3733 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
3734 THEN
3735 FND_LOG.STRING(
3736 log_level => g_level_statement,
3737 module => g_module_name || l_procedure_name,
3738 message => g_log_msg
3739 );
3740 END IF;
3741
3742 UPDATE mtl_material_transactions
3743 SET opm_costed_flag = DECODE(G_accounting_mode, 'D', 'D', 'F', NULL)
3744 , program_update_date = SYSDATE /* ANTHIYAG Updating Porgram_update_date instead of Last_update_date to avoid auditing issues */
3745 /* Vpedarla bug: 9292668 commenting last_updated_by and last_update_login update*/
3746 -- , last_updated_by = g_user_id
3747 -- , last_update_login = g_login_id
3748 , program_application_id= g_prog_appl_id
3749 , program_id = g_program_id
3750 , request_id = g_request_id
3751 WHERE transaction_id
3752 in (SELECT
3753 xpae.SOURCE_ID_INT_1
3754 FROM
3755 xla_post_acctg_events_v xpae,
3756 gmf_xla_event_model gxem
3757 WHERE
3758 gxem.process_category = G_order_management
3759 and xpae.event_class_code = gxem.event_class_code
3760 )
3761 ;
3762
3763 g_log_msg := sql%rowcount || ' rows updated in mtl_material_transactions table';
3764
3765 print_debug(g_log_msg);
3766 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
3767 THEN
3768 FND_LOG.STRING(
3769 log_level => g_level_statement,
3770 module => g_module_name || l_procedure_name,
3771 message => g_log_msg
3772 );
3773 END IF;
3774
3775
3776 --
3777 -- Update cst_cogs_events table
3778 --
3779 g_log_msg := 'Updating cst_cogs_events table';
3780
3781 print_debug(g_log_msg);
3782 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
3783 THEN
3784 FND_LOG.STRING(
3785 log_level => g_level_statement,
3786 module => g_module_name || l_procedure_name,
3787 message => g_log_msg
3788 );
3789 END IF;
3790
3791 /* Set the costed flag in cst_cogs_events SO issues */
3792 /* xxx - REMOVE the comments. NOT code.
3793 UPDATE cst_cogs_events
3794 SET costed = DECODE(G_accounting_mode, 'D', 'D', 'F', NULL)
3795 , last_update_date = sysdate
3796 , last_updated_by = g_user_id
3797 , last_update_login = g_login_id
3798 , program_application_id = g_prog_appl_id
3799 , program_id = g_program_id
3800 , request_id = g_request_id
3801 WHERE
3802 exists (SELECT 'x'
3803 FROM
3804 xla_post_acctg_events_v xpae,
3805 gmf_xla_event_model gxem,
3806 gmf_xla_extract_headers eh
3807 WHERE
3808 eh.transaction_id = xpae.SOURCE_ID_INT_1
3809 AND eh.ledger_id = xpae.SOURCE_ID_INT_2
3810 AND eh.valuation_cost_type_id = xpae.SOURCE_ID_INT_3
3811 AND eh.event_class_code = xpae.SOURCE_ID_CHAR_1
3812 AND eh.transaction_action_id = 36
3813 AND gxem.process_category = G_order_management
3814 AND xpae.event_class_code = gxem.event_class_code
3815 AND cogs_om_line_id = eh.source_line_id
3816 AND cce.mmt_transaction_id = eh.transaction_id
3817 )
3818 ;
3819
3820 g_log_msg := sql%rowcount || ' rows updated in cst_cogs_events table';
3821
3822 print_debug(g_log_msg);
3823 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
3824 THEN
3825 FND_LOG.STRING(
3826 log_level => g_level_statement,
3827 module => g_module_name || l_procedure_name,
3828 message => g_log_msg
3829 );
3830 END IF;
3831 --
3832 -- End of updates
3833 --
3834 */
3835
3836 ELSE
3837 g_log_msg := 'Invalid event passed';
3838
3839 print_debug(g_log_msg);
3840 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
3841 THEN
3842 FND_LOG.STRING(
3843 log_level => g_level_statement,
3844 module => g_module_name || l_procedure_name,
3845 message => g_log_msg
3846 );
3847 END IF;
3848 RETURN;
3849 END CASE;
3850
3851 g_log_msg := 'End of procedure ' || l_procedure_name || ' for event ' || p_event;
3852
3853 print_debug(g_log_msg);
3854 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
3855 THEN
3856 FND_LOG.STRING(
3857 log_level => g_level_statement,
3858 module => g_module_name || l_procedure_name,
3859 message => g_log_msg
3860 );
3861 END IF;
3862
3863 END process_om_txns;
3864
3865 /*============================================================================
3866 | PROCEDURE - process_cm_txns
3867 |
3868 | DESCRIPTION
3869 | This procedure processes all inventory transactions. Right now we are
3870 | using this procedure for post-processing, but we can use it for other
3871 | processing events (pre-processing, extract and post-accounting).
3872 |
3873 | PRAMETERS
3874 |
3875 | KNOWN ISSUES:
3876 |
3877 | NOTES:
3878 |
3879 | MODIFICATION HISTORY
3880 | Date Author Description of Change
3881 | 01-Oct-2005 umoogala Genesis
3882 | 06-Nov-2006 ANTHIYAG Bug#5597804
3883 | Modified Code to Change the transaction_type to
3884 | "LOT_COST_ADJUSTMENTS" instead of "COST_REVALUATIONS"
3885 | to account for Lot Cost Adjustments in Final Mode.
3886 | 04-Mar-2008 Pramod B.H Bug 6646395 - GL Cost Allocations enhancement
3887 *===========================================================================*/
3888 PROCEDURE process_rval_txns(p_event VARCHAR2)
3889 IS
3890 l_procedure_name CONSTANT VARCHAR2(100) := 'process_rval_txns';
3891 l_cost_method_type cm_mthd_mst.cost_type%TYPE;
3892 BEGIN
3893
3894 g_log_msg := 'Begin of procedure ' || l_procedure_name || ' for event ' || p_event;
3895
3896 print_debug(g_log_msg);
3897 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
3898 THEN
3899 FND_LOG.STRING(
3900 log_level => g_level_statement,
3901 module => g_module_name || l_procedure_name,
3902 message => g_log_msg
3903 );
3904 END IF;
3905
3906 CASE p_event
3907 WHEN G_pre_accounting
3908 THEN
3909 NULL;
3910 WHEN G_extract
3911 THEN
3912 NULL;
3913 WHEN G_post_accounting
3914 THEN
3915 NULL;
3916 WHEN G_post_processing
3917 THEN
3918 g_log_msg := 'Updating gmf_xla_extract_headers table';
3919
3920 print_debug(g_log_msg);
3921 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
3922 THEN
3923 FND_LOG.STRING(
3924 log_level => g_level_statement,
3925 module => g_module_name || l_procedure_name,
3926 message => g_log_msg
3927 );
3928 END IF;
3929
3930 --
3931 -- Update extract Headers
3932 --
3933 UPDATE gmf_xla_extract_headers
3934 SET accounted_flag = DECODE(G_accounting_mode, 'D', 'D', 'F', NULL)
3935 , actual_posting_date = DECODE(G_accounting_mode, 'F', sysdate, NULL)
3936 , last_update_date = sysdate
3937 , last_updated_by = g_user_id
3938 , last_update_login = g_login_id
3939 , program_application_id= g_prog_appl_id
3940 , program_id = g_program_id
3941 , request_id = g_request_id
3942 WHERE (transaction_id, ledger_id, valuation_cost_type_id, event_class_code)
3943 in (SELECT
3944 xpae.SOURCE_ID_INT_1, xpae.SOURCE_ID_INT_2,
3945 xpae.SOURCE_ID_INT_3, xpae.SOURCE_ID_CHAR_1
3946 FROM
3947 xla_post_acctg_events_v xpae,
3948 gmf_xla_event_model gxem
3949 WHERE
3950 gxem.process_category = G_revaluation_transactions
3951 and xpae.event_class_code = gxem.event_class_code
3952 )
3953 ;
3954 g_log_msg := sql%rowcount || ' rows updated in gmf_xla_extract_headers table';
3955
3956 print_debug(g_log_msg);
3957 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
3958 THEN
3959 FND_LOG.STRING(
3960 log_level => g_level_statement,
3961 module => g_module_name || l_procedure_name,
3962 message => g_log_msg
3963 );
3964 END IF;
3965
3966
3967 --
3968 -- Update transaction valuation table
3969 --
3970 g_log_msg := 'Updating gmf_transaction_valuation table';
3971
3972 print_debug(g_log_msg);
3973 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
3974 THEN
3975 FND_LOG.STRING(
3976 log_level => g_level_statement,
3977 module => g_module_name || l_procedure_name,
3978 message => g_log_msg
3979 );
3980 END IF;
3981
3982 UPDATE gmf_transaction_valuation
3983 SET accounted_flag = DECODE(G_accounting_mode, 'D', 'D', 'F', NULL)
3984 , final_posting_date = DECODE(G_accounting_mode, 'F', sysdate, NULL)
3985 , last_update_date = sysdate
3986 , last_updated_by = g_user_id
3987 , last_update_login = g_login_id
3988 , program_application_id= g_prog_appl_id
3989 , program_id = g_program_id
3990 , request_id = g_request_id
3991 WHERE
3992 (transaction_id, ledger_id, valuation_cost_type_id, event_class_code)
3993 in (SELECT
3994 xpae.SOURCE_ID_INT_1, xpae.SOURCE_ID_INT_2,
3995 xpae.SOURCE_ID_INT_3, xpae.SOURCE_ID_CHAR_1
3996 FROM
3997 xla_post_acctg_events_v xpae,
3998 gmf_xla_event_model gxem
3999 WHERE
4000 gxem.process_category = G_revaluation_transactions
4001 and xpae.event_class_code = gxem.event_class_code
4002 )
4003 AND ACCOUNTED_FLAG IS NOT NULL -- B7395353 Rajesh Patangya
4004 ;
4005 g_log_msg := sql%rowcount || ' rows updated in gmf_transaction_valuation table';
4006
4007 print_debug(g_log_msg);
4008 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
4009 THEN
4010 FND_LOG.STRING(
4011 log_level => g_level_statement,
4012 module => g_module_name || l_procedure_name,
4013 message => g_log_msg
4014 );
4015 END IF;
4016
4017
4018 /*
4019 --
4020 -- Update transaction valuation table
4021 --
4022 g_log_msg := 'Updating mtl_material_transactions table';
4023
4024 print_debug(g_log_msg);
4025 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
4026 THEN
4027 FND_LOG.STRING(
4028 log_level => g_level_statement,
4029 module => g_module_name || l_procedure_name,
4030 message => g_log_msg
4031 );
4032 END IF;
4033
4034 UPDATE mtl_material_transactions
4035 SET opm_costed_flag = DECODE(G_accounting_mode, 'D', 'D', 'F', NULL)
4036 WHERE transaction_id
4037 in (SELECT
4038 xpae.SOURCE_ID_INT_1
4039 FROM
4040 xla_post_acctg_events_v xpae,
4041 gmf_xla_event_model gxem
4042 WHERE
4043 gxem.process_category = G_revaluation_transactions
4044 and xpae.event_class_code = gxem.event_class_code
4045 )
4046 ;
4047
4048 g_log_msg := sql%rowcount || ' rows updated in mtl_material_transactions table';
4049
4050 print_debug(g_log_msg);
4051 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
4052 THEN
4053 FND_LOG.STRING(
4054 log_level => g_level_statement,
4055 module => g_module_name || l_procedure_name,
4056 message => g_log_msg
4057 );
4058 END IF;
4059 */
4060
4061 SELECT cost_type
4062 INTO l_cost_method_type
4063 FROM cm_mthd_mst
4064 WHERE cost_type_id = (SELECT xpae.SOURCE_ID_INT_3
4065 FROM xla_post_acctg_events_v xpae
4066 WHERE rownum = 1)
4067 ;
4068
4069 --
4070 -- Now for lot cost method, update gmf_lot_cost_adjustmets table
4071 -- For Actual/Standard methods, update gmf_period_balances table.
4072 --
4073
4074 IF l_cost_method_type = 6
4075 -- Lot Cost Method
4076 THEN
4077 --
4078 -- Update transaction valuation table
4079 --
4080 g_log_msg := 'Updating gmf_lot_cost_adjustments table';
4081
4082 print_debug(g_log_msg);
4083 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
4084 THEN
4085 FND_LOG.STRING(
4086 log_level => g_level_statement,
4087 module => g_module_name || l_procedure_name,
4088 message => g_log_msg
4089 );
4090 END IF;
4091
4092 UPDATE gmf_lot_cost_adjustments
4093 SET gl_posted_ind = DECODE(G_accounting_mode, 'D', gl_posted_ind, 'F', 1)
4094 , last_update_date = sysdate
4095 , last_updated_by = g_user_id
4096 , last_update_login = g_login_id
4097 , program_application_id= g_prog_appl_id
4098 , program_id = g_program_id
4099 , request_id = g_request_id
4100 WHERE adjustment_id
4101 in (SELECT
4102 xpae.SOURCE_ID_INT_1
4103 FROM
4104 xla_post_acctg_events_v xpae,
4105 gmf_xla_event_model gxem
4106 WHERE
4107 gxem.process_category = G_revaluation_transactions
4108 and gxem.transaction_type = 'LOT_COST_ADJUSTMENTS' /* Bug#5597804 ANTHIYAG 06-Nov-2006 */
4109 and xpae.event_class_code = gxem.event_class_code
4110 )
4111 ;
4112
4113 g_log_msg := sql%rowcount || ' rows updated in gmf_lot_cost_adjustments table';
4114
4115 print_debug(g_log_msg);
4116 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
4117 THEN
4118 FND_LOG.STRING(
4119 log_level => g_level_statement,
4120 module => g_module_name || l_procedure_name,
4121 message => g_log_msg
4122 );
4123 END IF;
4124
4125 ELSE
4126 --
4127 -- Actual/Standard Cost Methods
4128 --
4129 -- Update transaction valuation table
4130 --
4131 g_log_msg := 'Updating gmf_period_balances table';
4132
4133 print_debug(g_log_msg);
4134 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
4135 THEN
4136 FND_LOG.STRING(
4137 log_level => g_level_statement,
4138 module => g_module_name || l_procedure_name,
4139 message => g_log_msg
4140 );
4141 END IF;
4142
4143 UPDATE gmf_period_balances
4144 SET costed_flag = DECODE(G_accounting_mode, 'D', 'D', 'F', NULL)
4145 , last_update_date = sysdate
4146 , last_updated_by = g_user_id
4147 , last_update_login = g_login_id
4148 , program_application_id= g_prog_appl_id
4149 , program_id = g_program_id
4150 , request_id = g_request_id
4151 WHERE period_balance_id
4152 in (SELECT
4153 xpae.SOURCE_ID_INT_1
4154 FROM
4155 xla_post_acctg_events_v xpae,
4156 gmf_xla_event_model gxem
4157 WHERE
4158 gxem.process_category = G_revaluation_transactions
4159 and gxem.transaction_type = 'COST_REVALUATIONS' /*changed LOT_COST_ADJUSTMENTS to COST_REVALUATIONS jboppana*/
4160 and xpae.event_class_code = gxem.event_class_code
4161 )
4162 ;
4163
4164 g_log_msg := sql%rowcount || ' rows updated in gmf_period_balances table';
4165
4166 print_debug(g_log_msg);
4167 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
4168 THEN
4169 FND_LOG.STRING(
4170 log_level => g_level_statement,
4171 module => g_module_name || l_procedure_name,
4172 message => g_log_msg
4173 );
4174 END IF;
4175
4176 /* for actual cost adjustments enhancement jboppana start*/
4177 IF l_cost_method_type = 1 THEN
4178 g_log_msg := 'Updating cm_adjs_dtl table';
4179
4180 print_debug(g_log_msg);
4181 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
4182 THEN
4183 FND_LOG.STRING(
4184 log_level => g_level_statement,
4185 module => g_module_name || l_procedure_name,
4186 message => g_log_msg
4187 );
4188 END IF;
4189
4190 UPDATE cm_adjs_dtl
4191 SET gl_posted_ind = DECODE(G_accounting_mode, 'D', gl_posted_ind, 'F', 1)
4192 , last_update_date = sysdate
4193 , last_updated_by = g_user_id
4194 , last_update_login = g_login_id
4195 , program_application_id= g_prog_appl_id
4196 , program_id = g_program_id
4197 , request_id = g_request_id
4198 WHERE cost_adjust_id
4199 in (SELECT
4200 xpae.SOURCE_ID_INT_1
4201 FROM
4202 xla_post_acctg_events_v xpae,
4203 gmf_xla_event_model gxem
4204 WHERE
4205 gxem.process_category = G_revaluation_transactions
4206 and gxem.transaction_type = 'ACTUAL_COST_ADJUSTMENTS'
4207 and xpae.event_class_code = gxem.event_class_code
4208 )
4209 ;
4210
4211 g_log_msg := sql%rowcount || ' rows updated in cm_adjs_dtl table';
4212
4213 print_debug(g_log_msg);
4214 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
4215 THEN
4216 FND_LOG.STRING(
4217 log_level => g_level_statement,
4218 module => g_module_name || l_procedure_name,
4219 message => g_log_msg
4220 );
4221 END IF;
4222
4223
4224 END IF;
4225 /* for actual cost adjustments enhancement jboppana end*/
4226
4227 /* Bug 6646395 - GL Cost Allocations enhancement phiriyan start*/
4228 IF l_cost_method_type = 1 THEN
4229 g_log_msg := 'Updating gl_aloc_dtl table';
4230
4231 print_debug(g_log_msg);
4232 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
4233 THEN
4234 FND_LOG.STRING(
4235 log_level => g_level_statement,
4236 module => g_module_name || l_procedure_name,
4237 message => g_log_msg
4238 );
4239 END IF;
4240
4241 UPDATE gl_aloc_dtl
4242 SET gl_posted_ind = DECODE(G_accounting_mode, 'D', gl_posted_ind, 'F', 1)
4243 , last_update_date = sysdate
4244 , last_updated_by = g_user_id
4245 , last_update_login = g_login_id
4246 , program_application_id= g_prog_appl_id
4247 , program_id = g_program_id
4248 , request_id = g_request_id
4249 WHERE allocdtl_id
4250 in (SELECT
4251 xpae.SOURCE_ID_INT_1
4252 FROM
4253 xla_post_acctg_events_v xpae,
4254 gmf_xla_event_model gxem
4255 WHERE
4256 gxem.process_category = G_revaluation_transactions
4257 and gxem.transaction_type = 'GL_COST_ALLOCATIONS'
4258 and xpae.event_class_code = gxem.event_class_code
4259 )
4260 ;
4261
4262 g_log_msg := sql%rowcount || ' rows updated in gl_aloc_dtl table';
4263
4264 print_debug(g_log_msg);
4265 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
4266 THEN
4267 FND_LOG.STRING(
4268 log_level => g_level_statement,
4269 module => g_module_name || l_procedure_name,
4270 message => g_log_msg
4271 );
4272 END IF;
4273
4274
4275 END IF;
4276 /* Bug 6646395 - GL Cost Allocations enhancement phiriyan end*/
4277 END IF; -- Actual/Standard Cost Methods
4278
4279 ELSE
4280
4281 g_log_msg := 'Invalid event passed';
4282
4283 print_debug(g_log_msg);
4284 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
4285 THEN
4286 FND_LOG.STRING(
4287 log_level => g_level_statement,
4288 module => g_module_name || l_procedure_name,
4289 message => g_log_msg
4290 );
4291 END IF;
4292 RETURN;
4293 END CASE;
4294
4295 g_log_msg := 'End of procedure ' || l_procedure_name || ' for event ' || p_event;
4296
4297 print_debug(g_log_msg);
4298 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
4299 THEN
4300 FND_LOG.STRING(
4301 log_level => g_level_statement,
4302 module => g_module_name || l_procedure_name,
4303 message => g_log_msg
4304 );
4305 END IF;
4306 END process_rval_txns;
4307
4308 ---------------------------------------------------------------------------
4309 -- PROCEDURE: DRILLDOWN
4310 -- COMMENT: DRILLDOWN procedure provides a public API for sla to return
4311 -- the appropriate information via OUT parameters to open the
4312 -- appropriate transaction form.
4313 -- PARAMETERS:
4314 -- p_application_id : Subledger application internal identifier
4315 -- p_ledger_id : Event ledger identifier
4316 -- p_legal_entity_id : Legal entity identifier
4317 -- p_entity_code : Event entity internal code
4318 -- p_event_class_code : Event class internal code
4319 -- p_event_type_code : Event type internal code
4320 -- p_source_id_int_1 : Generic system transaction identifiers
4321 -- p_source_id_int_2 : Generic system transaction identifiers
4322 -- p_source_id_int_3 : Generic system transaction identifiers
4323 -- p_source_id_int_4 : Generic system transaction identifiers
4324 -- p_source_id_char_1 : Generic system transaction identifiers
4325 -- p_source_id_char_2 : Generic system transaction identifiers
4326 -- p_source_id_char_3 : Generic system transaction identifiers
4327 -- p_source_id_char_4 : Generic system transaction identifiers
4328 -- p_security_id_int_1 : Generic system transaction identifiers
4329 -- p_security_id_int_2 : Generic system transaction identifiers
4330 -- p_security_id_int_3 : Generic system transaction identifiers
4331 -- p_security_id_char_1 : Generic system transaction identifiers
4332 -- p_security_id_char_2 : Generic system transaction identifiers
4333 -- p_security_id_char_3 : Generic system transaction identifiers
4334 -- p_valuation_method : Valuation Method internal identifier
4335 -- p_user_interface_type: This parameter determines the user interface type.
4336 -- The possible values are FORM, HTML, or NONE.
4337 -- p_function_name : The name of the Oracle Application Object
4338 -- Library function defined to open the transaction
4339 -- form. This parameter is used only if the page
4340 -- is a FORM page.
4341 -- p_parameters : An Oracle Application Object Library Function
4342 -- can have its own arguments/parameters. SLA
4343 -- expects developers to return these arguments via
4344 -- p_parameters.
4345 --
4346 ---------------------------------------------------------------------------
4347
4348 PROCEDURE DRILLDOWN
4349 (
4350 p_application_id IN INTEGER
4351 , p_ledger_id IN INTEGER
4352 , p_legal_entity_id IN INTEGER DEFAULT NULL
4353 , p_entity_code IN VARCHAR2
4354 , p_event_class_code IN VARCHAR2
4355 , p_event_type_code IN VARCHAR2
4356 , p_source_id_int_1 IN INTEGER DEFAULT NULL
4357 , p_source_id_int_2 IN INTEGER DEFAULT NULL
4358 , p_source_id_int_3 IN INTEGER DEFAULT NULL
4359 , p_source_id_int_4 IN INTEGER DEFAULT NULL
4360 , p_source_id_char_1 IN VARCHAR2 DEFAULT NULL
4361 , p_source_id_char_2 IN VARCHAR2 DEFAULT NULL
4362 , p_source_id_char_3 IN VARCHAR2 DEFAULT NULL
4363 , p_source_id_char_4 IN VARCHAR2 DEFAULT NULL
4364 , p_security_id_int_1 IN INTEGER DEFAULT NULL
4365 , p_security_id_int_2 IN INTEGER DEFAULT NULL
4366 , p_security_id_int_3 IN INTEGER DEFAULT NULL
4367 , p_security_id_char_1 IN VARCHAR2 DEFAULT NULL
4368 , p_security_id_char_2 IN VARCHAR2 DEFAULT NULL
4369 , p_security_id_char_3 IN VARCHAR2 DEFAULT NULL
4370 , p_valuation_method IN VARCHAR2 DEFAULT NULL
4371 , p_user_interface_type IN OUT NOCOPY VARCHAR2
4372 , p_function_name IN OUT NOCOPY VARCHAR2
4373 , p_parameters IN OUT NOCOPY VARCHAR2
4374 )
4375 IS
4376 l_security_id_int_1 BINARY_INTEGER;
4377 l_source_id_int_1 BINARY_INTEGER;
4378 l_form_usage_mode CONSTANT VARCHAR2(30) := 'SLA_DRILLDOWN';
4379 l_batch_id BINARY_INTEGER;
4380 l_batchstep_id BINARY_INTEGER;
4381 l_resource VARCHAR2(16);
4382 l_batchstep_activity_id BINARY_INTEGER;
4383 l_batchstep_resource_id BINARY_INTEGER;
4384 l_process_param_id BINARY_INTEGER;
4385 l_ship_header_id BINARY_INTEGER;
4386 l_ship_line_id BINARY_INTEGER;
4387
4388 BEGIN
4389 -- To check whether the application is GMF
4390 -- bug 12533484, added condition for transfer to regular event types
4391 IF (p_application_id = 555)
4392 THEN
4393 IF (p_entity_code in ('INVENTORY', 'ORDERMANAGEMENT') OR
4394 (p_entity_code = 'PRODUCTION' AND p_event_class_code = 'BATCH_MATERIAL') OR
4395 (p_entity_code = 'PURCHASING' AND p_event_class_code = 'DELIVER' AND p_event_type_code = 'XFER_TO_REGULAR')
4396 )
4397 THEN
4398 --
4399 -- Should open Material Transactions form
4400 -- p_source_id_int_1: mmt.transaction_id
4401 -- p_security_id_int_1: organization_id
4402 --
4403 p_user_interface_type := 'FORM';
4404 p_function_name := 'CST_INVTVTXN';
4405
4406 IF (p_event_class_code = 'FOB_SHIP_RECIPIENT_SHIP') OR
4407 (p_event_class_code = 'FOB_RCPT_SENDER_RCPT')
4408 THEN
4409 SELECT organization_id
4410 INTO l_security_id_int_1
4411 FROM mtl_material_transactions
4412 WHERE transaction_id = p_source_id_int_1;
4413 ELSE
4414 l_security_id_int_1 := p_security_id_int_1;
4415 END IF;
4416
4417 p_parameters := ' FORM_USAGE_MODE="'||l_form_usage_mode||'"'
4418 ||' INVTVTXN_GO_DETAIL="Y"'
4419 ||' INVTVTXN_TRXN_ID="' || to_char(p_source_id_int_1)||'"'
4420 ||' ORG_ID="'||to_char(l_security_id_int_1)||'"';
4421
4422 ELSIF (p_entity_code = 'PURCHASING')
4423 THEN
4424
4425 --
4426 -- Should open Receiving Transactions form
4427 -- p_source_id_int_1: mmt.transaction_id
4428 -- p_security_id_int_1: organization_id
4429 --
4430 IF p_event_class_code = 'DELIVER'
4431 THEN
4432 SELECT rcv_transaction_id
4433 INTO l_source_id_int_1
4434 FROM mtl_material_transactions
4435 WHERE transaction_id = p_source_id_int_1
4436 ;
4437 ELSIF p_event_class_code = 'RECEIVE'
4438 THEN
4439 --
4440 -- Bug 5668308: p_source_id_int_1 is grat id. So, get the
4441 -- rcv transaction id.
4442 --
4443 -- l_source_id_int_1 := p_source_id_int_1;
4444 --
4445 SELECT event_source_id
4446 INTO l_source_id_int_1
4447 FROM gmf_rcv_accounting_txns
4448 WHERE accounting_txn_id = p_source_id_int_1;
4449
4450 ELSIF p_event_class_code='PAYABLES_INVOICE' THEN
4451 p_user_interface_type := 'NONE';
4452 END IF;
4453
4454 IF p_event_class_code IN ('RECEIVE', 'DELIVER') THEN
4455 p_user_interface_type := 'FORM';
4456 p_function_name := 'RCV_RCVRCVRC';
4457 p_parameters := ' FORM_USAGE_MODE="'||l_form_usage_mode||'"'
4458 ||' TRANSACTION_ID="' || to_char(l_source_id_int_1)||'"'
4459 ||' MO_ORG_ID="'||to_char(p_security_id_int_2)||'"'
4460 ||' ORG_ID="'||to_char(p_security_id_int_1)||'"';
4461 /* LCM-OPM Integration pmarada */
4462 ELSIF p_event_class_code IN ('LC_ADJUST_RECEIVE','LC_ADJUST_DELIVER','LC_ADJUST_EXP_DELIVER','LC_ADJUST_VALUATION')
4463 THEN
4464 /* invoke View landed costs page for LCM shipment header and LCM shipment line */
4465 SELECT ship_header_id,
4466 ship_line_id
4467 INTO l_ship_header_id,
4468 l_ship_line_id
4469 FROM gmf_lc_adj_transactions
4470 WHERE adj_transaction_id = p_source_id_int_1;
4471
4472 /* invoke view landed costs page */
4473 p_user_interface_type := 'HTML';
4474 p_function_name := 'INL_VIEW_LC';
4475 p_parameters := ' shipHeaderId="'|| l_ship_header_id ||'"'
4476 ||' callerUrlGridPage="' || ''||'"'
4477 ||' returnLinkGridText="' || 'INL_RETURN_REF_SHIP'||'"'
4478 ||' fromPage="'||''||'"';
4479
4480 /* End LCM-OPM Integration pmarada */
4481 ELSE
4482 p_user_interface_type := 'NONE';
4483 END IF;
4484
4485 ELSIF (p_entity_code = 'PRODUCTION')
4486 THEN
4487
4488 IF p_event_class_code = 'BATCH_RESOURCE'
4489 THEN
4490 --
4491 -- Should open Resource Transactions window in Batch Steps form
4492 -- p_source_id_int_1: gme_resource_txns.poc_trans_id
4493 -- p_security_id_int_1: organization_id
4494 --
4495
4496 SELECT a.batch_id,
4497 a.batchstep_id,
4498 grt.resources,
4499 a.batchstep_activity_id,
4500 a.batchstep_resource_id
4501 INTO l_batch_id,
4502 l_batchstep_id,
4503 l_resource,
4504 l_batchstep_activity_id,
4505 l_batchstep_resource_id
4506 FROM gme_batch_step_resources a,
4507 gme_resource_txns grt
4508 WHERE grt.poc_trans_id = p_source_id_int_1
4509 AND grt.line_id = a.batchstep_resource_id;
4510
4511 p_user_interface_type := 'FORM';
4512 p_function_name := 'GMESTPED_F';
4513
4514 p_parameters := ' FORMS_USAGE_MODE="'||l_form_usage_mode||'"'
4515 ||' ORG_ID="'||to_char(p_security_id_int_1) ||'"'
4516 ||' BATCH_ID="'||to_char(l_batch_id)||'"'
4517 ||' BATCHSTEP_ID="'||to_char(l_batchstep_id)||'"'
4518 ||' BATCHSTEP_ACTIVITY_ID="'||to_char(l_batchstep_activity_id) ||'"'
4519 ||' BATCHSTEP_RESOURCE_ID="'||to_char(l_batchstep_resource_id)||'"'
4520 ||' RESOURCES="'||to_char(l_resource) ||'"'||' QUERY_ONLY="'||'YES'||'"';
4521
4522 ELSIF p_event_class_code = 'BATCH_CLOSE'
4523 THEN
4524 --
4525 -- Should open Batch Details form
4526 -- p_source_id_int_1: gme_batch_header.batch_id
4527 -- p_security_id_int_1: organization_id
4528 --
4529 p_user_interface_type := 'FORM';
4530 p_function_name := 'GMEBDTED_F';
4531
4532 p_parameters := ' QUERY_ONLY="'||'Y'||'"'||' BATCH_ID="' || to_char(p_source_id_int_1)||'"'
4533 ||' ORG_ID="'||to_char(p_security_id_int_1)||'"';
4534 END IF;
4535 ELSIF (p_entity_code = 'REVALUATION')
4536 THEN
4537
4538 --
4539 -- Should open Lot Cost Adjustment form
4540 -- p_source_id_int_1: adjustment_id
4541 -- p_security_id_int_1: organization_id
4542 --
4543 IF p_event_class_code = 'LOTCOSTADJ'
4544 THEN
4545
4546 p_user_interface_type := 'FORM';
4547 p_function_name := 'GMFLCADJ_F';
4548 p_parameters := ' QUERY_ONLY="'||'Y'||'"'
4549 ||' ADJUSTMENT_ID="' || to_char(p_source_id_int_1)||'"'
4550 ||' ORG_ID="'||to_char(p_security_id_int_1)||'"';
4551 ELSE
4552 p_user_interface_type := 'NONE';
4553 END IF;
4554
4555 ELSE
4556 p_user_interface_type := 'NONE';
4557 END IF;
4558 END IF;
4559 END DRILLDOWN;
4560
4561
4562 /*============================================================================
4563 | PROCEDURE - delete_IPV_ERV_DUP_RECORDS
4564 |
4565 | DESCRIPTION
4566 |
4567 | PRAMETERS
4568 |
4569 | MODIFICATION HISTORY
4570 | Date Author Description of Change
4571 | 08-Jun-2010 Vpedarla Bug: 9795216 Created the procedure
4572 | 16-Sep-2010 patangya Bug: 10121825 made changes to delete unwanted Cost Revaluation transactions.
4573 *===========================================================================*/
4574 PROCEDURE delete_IPV_ERV_DUP_RECORDS IS
4575
4576 l_procedure_name CONSTANT VARCHAR2(100) := 'delete_IPV_ERV_DUP_RECORDS';
4577
4578 record_count INTEGER ;
4579
4580 BEGIN
4581
4582 g_log_msg := 'Begin of procedure GMF_XLA_PKG.delete_IPV_ERV_DUP_RECORDS ' ;
4583 print_debug(g_log_msg);
4584
4585 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
4586 THEN
4587
4588 FND_LOG.STRING(
4589 log_level => g_level_procedure,
4590 module => g_module_name ,
4591 message => g_log_msg
4592 );
4593 END IF;
4594
4595 INSERT INTO xla_events_int_gt
4596 (
4597 entity_id
4598 ,application_id
4599 ,ledger_id
4600 ,entity_code
4601 ,event_status_code
4602 ,event_id
4603 )
4604 SELECT
4605 xe.entity_id
4606 ,xe.application_id
4607 ,xah.ledger_id
4608 ,'PURCHASING'
4609 ,xe.event_status_code
4610 ,xe.event_id
4611 FROM xla_events xe, xla_ae_headers xah
4612 WHERE xe.application_id = 555
4613 AND xah.application_id = 555
4614 AND xe.event_id not in ( select event_id from gmf_xla_extract_headers where EVENT_TYPE_CODE in ('INVOICE_IPV_ADJ' , 'INVOICE_ERV_ADJ' ) )
4615 AND xe.event_type_code in ('INVOICE_IPV_ADJ' , 'INVOICE_ERV_ADJ' )
4616 AND xe.event_status_code = 'N' /* Bug14325989*/
4617 AND xe.process_status_code = 'P' /* Bug14325989*/
4618 AND xe.event_id = xah.event_id
4619 -- Bug: 9681484 Vpedarla Added the below union to delete records of Actual Cost Adj., which are not valid.
4620 Union
4621 SELECT
4622 xe.entity_id
4623 ,xe.application_id
4624 ,xah.ledger_id
4625 ,'REVALUATION'
4626 ,xe.event_status_code
4627 ,xe.event_id
4628 FROM xla_events xe, xla_ae_headers xah
4629 WHERE xe.application_id = 555
4630 AND xah.application_id = 555
4631 AND xe.event_type_code = 'ACTCOSTADJ'
4632 AND xe.event_status_code = 'N'
4633 AND xe.process_status_code = 'P'
4634 AND xe.event_id = xah.event_id
4635 AND xe.event_id not in ( select event_id
4636 from gmf_xla_extract_headers
4637 where entity_code = 'REVALUATION'
4638 and EVENT_CLASS_CODE = 'ACTCOSTADJ' )
4639 -- Bug: 10121825 made changes to delete unwanted Cost Revaluation transactions.
4640 UNION
4641 SELECT
4642 xe.entity_id
4643 ,xe.application_id
4644 ,xah.ledger_id
4645 ,'REVALUATION'
4646 ,xe.event_status_code
4647 ,xe.event_id
4648 FROM xla_events xe, xla_ae_headers xah
4649 WHERE xe.application_id = 555
4650 AND xah.application_id = 555
4651 AND xe.event_type_code = 'COSTREVAL'
4652 AND xe.event_status_code = 'N'
4653 AND xe.process_status_code = 'P'
4654 AND xe.event_id = xah.event_id
4655 AND xe.event_id not in ( select event_id
4656 from gmf_xla_extract_headers
4657 where entity_code = 'REVALUATION'
4658 and EVENT_CLASS_CODE = 'COSTREVAL'
4659 and EVENT_TYPE_CODE = 'COSTREVAL' )
4660 ;
4661
4662
4663 select count(*) into record_count from xla_events_int_gt ;
4664
4665 g_log_msg := 'Record count of xla_events_int_gt is ' || record_count ;
4666
4667 print_debug(g_log_msg);
4668 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
4669 THEN
4670 FND_LOG.STRING(
4671 log_level => g_level_statement,
4672 module => g_module_name || l_procedure_name,
4673 message => g_log_msg
4674 );
4675 END IF;
4676
4677 IF record_count > 0 THEN
4678 xla_events_pkg.delete_bulk_events( p_application_id => 555);
4679 delete from xla_events_int_gt ;
4680 END IF;
4681
4682 g_log_msg := 'End of procedure GMF_XLA_PKG.delete_IPV_ERV_DUP_RECORDS ' ;
4683 print_debug(g_log_msg);
4684
4685 EXCEPTION
4686 WHEN OTHERS
4687 THEN
4688 g_log_msg := ' Encountered an exception ' ;
4689
4690 print_debug(g_log_msg);
4691 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
4692 THEN
4693 FND_LOG.STRING(
4694 log_level => g_level_statement,
4695 module => g_module_name || l_procedure_name,
4696 message => g_log_msg
4697 );
4698 END IF;
4699 END delete_IPV_ERV_DUP_RECORDS;
4700
4701 /*============================================================================
4702 | PROCEDURE - Del_Orphan_Lotcostadj_Events
4703 |
4704 | DESCRIPTION
4705 |
4706 | PRAMETERS
4707 |
4708 | MODIFICATION HISTORY
4709 | Date Author -- Parag Bug: 12889563 Description of Change
4710 | 13-Sep-2011 Pvkanetk Bug: 12889563 Created the procedure
4711 | Performance driving table? should we restrict by date range from preproc?
4712 | Pass in ledger_id, valuation_cost_type_id, transaction_date range from preprocessor
4713 | We can hard code transaction_source_type_id so that N1 index can be used on extract
4714 | headers and that can become driving table. What is date format in C code?
4715 | Convert this to function and print event count?
4716 *===========================================================================*/
4717
4718 PROCEDURE Del_Orphan_Lotcostadj_Events
4719 ( p_ledger_id IN NUMBER,
4720 p_valuation_cost_type_id IN NUMBER,
4721 p_date_start IN DATE,
4722 p_date_end IN DATE)
4723 IS
4724
4725 CURSOR chk_orphan_lot_cost_adj_events IS
4726 SELECT eh.event_id
4727 FROM gmf_xla_extract_headers eh,
4728 gmf_lot_cost_adjustments lc_adj
4729 WHERE eh.entity_code = 'REVALUATION'
4730 AND eh.EVENT_CLASS_CODE = 'LOTCOSTADJ'
4731 AND eh.EVENT_TYPE_CODE = 'LOTCOSTADJ'
4732 AND eh.transaction_id = lc_adj.adjustment_id
4733 AND eh.ledger_id = p_ledger_id
4734 AND eh.valuation_cost_type_id = p_valuation_cost_type_id
4735 AND eh.transaction_date >= p_date_start
4736 AND eh.transaction_date <= p_date_end
4737 AND eh.transaction_source_type_id = 0
4738 AND lc_adj.delete_mark = 1
4739 AND NVL(lc_adj.gl_posted_ind,0) <> 1;
4740
4741
4742 l_event_count NUMBER := 0;
4743
4744
4745 BEGIN
4746
4747 g_log_msg := 'Begin of procedure GMF_XLA_PKG.Del_Orphan_lot_cost_adj_events' ;
4748 print_debug(g_log_msg);
4749
4750 -- Delete batch Close events if they exist since batch is reopened.
4751
4752 FOR l_event IN chk_orphan_lot_cost_adj_events LOOP
4753
4754 l_event_count := l_event_count + 1;
4755
4756 INSERT INTO xla_events_int_gt
4757 (entity_id
4758 ,application_id
4759 ,ledger_id
4760 ,entity_code
4761 ,event_status_code
4762 ,event_id
4763 )
4764 SELECT
4765 xe.entity_id
4766 ,xe.application_id
4767 ,p_ledger_id
4768 ,'REVALUATION'
4769 ,xe.event_status_code
4770 ,xe.event_id
4771 FROM xla_events xe
4772 WHERE xe.application_id = 555
4773 AND xe.event_id = l_event.event_id
4774 AND xe.event_type_code = 'LOTCOSTADJ';
4775
4776 g_log_msg := 'Found ' || l_event.event_id ;
4777 print_debug(g_log_msg);
4778
4779 DELETE FROM gmf_xla_extract_lines
4780 WHERE Header_id = (SELECT header_id FROM gmf_xla_extract_headers
4781 WHERE entity_code = 'REVALUATION'
4782 AND event_class_code = 'LOTCOSTADJ'
4783 AND EVENT_TYPE_CODE = 'LOTCOSTADJ'
4784 AND event_id = l_event.event_id);
4785
4786 g_log_msg := 'Deleting extract headers for orphan events ';
4787 print_debug(g_log_msg);
4788
4789 DELETE FROM gmf_xla_extract_headers
4790 WHERE entity_code = 'REVALUATION'
4791 AND event_class_code = 'LOTCOSTADJ'
4792 AND EVENT_TYPE_CODE = 'LOTCOSTADJ'
4793 AND event_id = l_event.event_id;
4794
4795 END LOOP;
4796
4797 IF (l_event_count >= 1) THEN
4798
4799 g_log_msg := 'deleting orphan Lot cost adjustment events event count = '||l_event_count;
4800 print_debug(g_log_msg);
4801
4802 xla_events_pkg.delete_bulk_events( p_application_id => 555);
4803
4804 delete from xla_events_int_gt ;
4805
4806 END IF;
4807 EXCEPTION
4808 WHEN OTHERS THEN
4809
4810 NULL;
4811
4812 END Del_Orphan_Lotcostadj_Events;
4813
4814 END GMF_XLA_PKG;