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