[Home] [Help]
146: END IF;
147:
148: -- umxla_extract_gt; /* xxxremove */
149:
150: select count(*) into n_hdrs from gmf_xla_extract_headers_gt; -- xxxremove
151: select count(*) into n_lines from gmf_xla_extract_lines_gt; -- xxxremove
152:
153: g_log_msg := n_hdrs || ' rows in hdrs_gt and ' || n_lines || ' rows in lines_gt';
154:
190: ------------------------------------------------------------------------
191: -- Insert into xla_events_int_gt table. Create_Bulk_Events procedure
192: -- of SLA will pickup txn from here and create events. It will
193: -- also update event_id column in this table.
194: -- We'll stamp this event id on gmf_xla_extract_headers.event_id.
195: --
196: -- We will insert only transactions for which event needs to be created.
197: ------------------------------------------------------------------------
198:
202: -- mapped to Inventory Entity in SLA.
203: --
204: FOR i in (
205: SELECT distinct entity_code
206: FROM gmf_xla_extract_headers_gt
207: )
208: LOOP
209: g_log_msg := 'Calling function GMF_XLA_PKG.insert_into_xla_events_gt for entity ' || i.entity_code ||
210: ' (if entity is Inventory when process was submitted for PUR/OM, then we have some' ||
267: * Following SQL was causing incorrect entity being set on
268: * SLA event and hence unable to query the event.
269: SELECT entity_code
270: INTO l_entity_type_code
271: FROM gmf_xla_extract_headers_gt
272: WHERE rownum = 1
273: ;
274: */
275:
305: END IF;
306:
307:
308: ---------------------------------------------------------------------
309: -- Update gmf_xla_extract_headers_gt to set event_id
310: -- generated by above call. No need to update if n_rows_inserted = 0
311: ---------------------------------------------------------------------
312: IF n_rows_inserted = 0
313: THEN
346: END LOOP; -- Loop for each Entity (i.e., Source/Process Category)
347: /* Bug 5668308 */
348:
349: ---------------------------------------------------------------------
350: -- Now insert/update gmf_xla_extract_headers to set event_id
351: ---------------------------------------------------------------------
352: g_log_msg := 'Calling proc update_extract_header';
353:
354: print_debug(g_log_msg);
364: update_extract_headers_table;
365:
366:
367: ---------------------------------------------------------------------
368: -- Now insert/update gmf_xla_extract_headers to set event_id
369: ---------------------------------------------------------------------
370: g_log_msg := 'Calling proc XLA_EVENTS_PKG.update_extract_lines';
371:
372: print_debug(g_log_msg);
426:
427: ---------------------------------------------------------------------
428: -- Clean-up...
429: --
430: -- gmf_xla_extract_headers/lines_gt tables too!
431: -- Verify GT table definitions to decide whether to keep following
432: -- stmts or not.
433: ---------------------------------------------------------------------
434: g_log_msg := 'Events creation complete. Deleting ALL rows from extract headers/lines gt tables';
446: /* Bug 12647482. Following delete statements commented because these are transaction persistent tables. The delete
447: happens automatically when the data is commited after processing every 1000 records (profile: GL$COMMIT_COUNT). */
448:
449: /*
450: DELETE FROM gmf_xla_extract_headers_gt;
451: DELETE FROM gmf_xla_extract_lines_gt;
452: */
453: ---------------------------------------------------------------------
454: -- All done!
555: );
556: END IF;
557:
558:
559: update gmf_xla_extract_headers_gt ehgt
560: set (entity_code, event_class_code, event_type_code) =
561: (SELECT entity_code, event_class_code, event_type_code
562: FROM gmf_xla_event_model em
563: WHERE
599: nvl(transfer_type, 'x') as transfer_type,
600: nvl(transfer_price_flag, 'x') as transfer_price_flag,
601: nvl(fob_point, 99) as fob_point,
602: nvl(transaction_type, 'x') as transaction_type
603: FROM gmf_xla_extract_headers_gt
604: WHERE entity_code IS NULL or event_class_code IS NULL OR event_type_code IS NULL
605: )
606: LOOP
607:
644:
645: IF l_cnt_hdr_msg < 0 THEN /* Bug 12647482 */
646: DELETE FROM gmf_xla_extract_lines_gt
647: where header_id in (SELECT header_id
648: FROM gmf_xla_extract_headers_gt
649: WHERE entity_code IS NULL or event_class_code IS NULL OR event_type_code IS NULL)
650: ;
651: DELETE FROM gmf_xla_extract_headers_gt
652: WHERE entity_code IS NULL or event_class_code IS NULL OR event_type_code IS NULL
647: where header_id in (SELECT header_id
648: FROM gmf_xla_extract_headers_gt
649: WHERE entity_code IS NULL or event_class_code IS NULL OR event_type_code IS NULL)
650: ;
651: DELETE FROM gmf_xla_extract_headers_gt
652: WHERE entity_code IS NULL or event_class_code IS NULL OR event_type_code IS NULL
653: ;
654: END IF;
655:
678: message => g_log_msg
679: );
680: END IF;
681:
682: UPDATE gmf_xla_extract_headers_gt ehgt
683: SET event_id = (SELECT event_id
684: FROM xla_events_int_gt egt
685: WHERE egt.source_id_int_1 = ehgt.transaction_id
686: AND egt.source_id_int_2 = ehgt.ledger_id
857: , NULL -- SECURITY_ID_CHAR_3
858: , NULL
859: , transaction_date
860: FROM
861: gmf_xla_extract_headers_gt ehgt
862: WHERE
863: entity_code = p_entity_code
864: AND
865: not exists (SELECT 'txns for which events created'
862: WHERE
863: entity_code = p_entity_code
864: AND
865: not exists (SELECT 'txns for which events created'
866: FROM gmf_xla_extract_headers eh
867: WHERE eh.transaction_id = ehgt.transaction_id
868: AND eh.legal_entity_id = ehgt.legal_entity_id
869: AND eh.ledger_id = ehgt.ledger_id
870: AND eh.valuation_cost_type_id = ehgt.valuation_cost_type_id
923: message => g_log_msg
924: );
925: END IF;
926:
927: g_log_msg := 'Merging rows in to gmf_xla_extract_headers table';
928:
929: print_debug(g_log_msg);
930: IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL )
931: THEN
935: message => g_log_msg
936: );
937: END IF;
938:
939: MERGE INTO gmf_xla_extract_headers eh
940: USING (SELECT
941: DISTINCT
942: reference_no
943: , event_id
999: , accounted_flag
1000: , actual_posting_date
1001: , invoiced_flag
1002: , shipment_costed
1003: FROM gmf_xla_extract_headers_gt) ehgt
1004: ON ( eh.transaction_id = ehgt.transaction_id
1005: AND eh.ledger_id = ehgt.ledger_id
1006: AND eh.valuation_cost_type_id = ehgt.valuation_cost_type_id
1007: /* AND eh.transaction_source_type_id = ehgt.transaction_source_type_id INVCONV */
1006: AND eh.valuation_cost_type_id = ehgt.valuation_cost_type_id
1007: /* AND eh.transaction_source_type_id = ehgt.transaction_source_type_id INVCONV */
1008: AND eh.event_class_code = ehgt.event_class_code
1009: /* AND nvl(eh.lot_number, 'x') = nvl(ehgt.lot_number, 'x') INVCONV */
1010: /* Bug 7620018. Added legal_entity_id so that unique index GMF_XLA_EXTRACT_HEADERS_U2 is used. */
1011: AND eh.legal_entity_id = ehgt.legal_entity_id
1012: AND eh.event_type_code = DECODE(ehgt.event_class_code,'LC_ADJUSTMENTS',ehgt.event_type_code, eh.event_type_code ) /*bug 11807826 */
1013: )
1014: WHEN MATCHED THEN
1101: , request_id
1102: )
1103: VALUES
1104: (
1105: gmf_xla_extract_headers_s.NEXTVAL -- header_id
1106: , ehgt.reference_no
1107: , ehgt.event_id
1108: , ehgt.entity_code
1109: , ehgt.event_class_code
1224:
1225: --
1226: -- First, set the header_id and event_id in extract_lines_gt table
1227: --
1228: -- B 7147477 include legal entity id so that unique index GMF_XLA_EXTRACT_HEADERS_U2 is used.
1229: -- bug 11807826, added event type code in the where clause for Lc valuatuion transactions
1230: -- bug13428401, modified the decode condition in the where clause
1231: UPDATE gmf_xla_extract_lines_gt elgt
1232: SET (header_id, event_id) =
1232: SET (header_id, event_id) =
1233: (SELECT
1234: eh.header_id, eh.event_id
1235: FROM
1236: gmf_xla_extract_headers_gt ehgt,
1237: gmf_xla_extract_headers eh
1238: WHERE
1239: ehgt.header_id = elgt.header_id
1240: AND eh.legal_entity_id = ehgt.legal_entity_id
1233: (SELECT
1234: eh.header_id, eh.event_id
1235: FROM
1236: gmf_xla_extract_headers_gt ehgt,
1237: gmf_xla_extract_headers eh
1238: WHERE
1239: ehgt.header_id = elgt.header_id
1240: AND eh.legal_entity_id = ehgt.legal_entity_id
1241: AND eh.ledger_id = ehgt.ledger_id
1424: event_class_code) IN
1425: (select eh.transaction_id, eh.ledger_id, eh.valuation_cost_type_id,
1426: -- eh.transaction_source_type_id,
1427: eh.event_class_code
1428: from gmf_xla_extract_headers eh,
1429: gmf_xla_extract_lines_gt elgt
1430: where eh.header_id = elgt.header_id
1431: and eh.event_id = elgt.event_id
1432: )
1437: DELETE FROM gmf_transaction_valuation
1438: WHERE (transaction_id, ledger_id, valuation_cost_type_id, event_class_code)
1439: IN (
1440: select transaction_id, ledger_id, valuation_cost_type_id, event_class_code
1441: from gmf_xla_extract_headers_gt
1442: );
1443:
1444: g_log_msg := sql%rowcount || ' previous rows deleted from GVT';
1445:
1591: , elgt.usage_ind as component_class_usage_type
1592: , elgt.cost_level
1593: , sum(elgt.BASE_AMOUNT_RAW) as txn_base_value_raw
1594: , sum(elgt.BASE_AMOUNT) as txn_base_value
1595: FROM gmf_xla_extract_headers eh,
1596: gmf_xla_extract_lines_gt elgt,
1597: mtl_system_items_kfv item,
1598: mtl_txn_source_types ts
1599: WHERE eh.header_id = elgt.header_id
1837:
1838: -- Get cost type and legal entity from extract headers
1839: CURSOR cur_le_cost_mthd IS
1840: SELECT DISTINCT cost_mthd_code, eh.legal_entity_id
1841: FROM gmf_xla_extract_headers eh, cm_mthd_mst cmm, gmf_ledger_valuation_methods vm
1842: WHERE eh.valuation_cost_type_id = cmm.cost_type_id
1843: AND vm.ledger_id = eh.ledger_id
1844: AND vm.cost_type_id = eh.valuation_cost_type_id
1845: AND eh.ledger_id = p_ledger_id
2418: NULL;
2419: WHEN G_post_processing
2420: THEN
2421:
2422: g_log_msg := 'Updating gmf_xla_extract_headers table';
2423:
2424: print_debug(g_log_msg);
2425: IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
2426: THEN
2433:
2434: --
2435: -- Update extract Headers
2436: --
2437: UPDATE gmf_xla_extract_headers
2438: SET accounted_flag = DECODE(G_accounting_mode, 'D', 'D', 'F', NULL)
2439: , actual_posting_date = DECODE(G_accounting_mode, 'F', sysdate, NULL)
2440: , last_update_date = sysdate
2441: , last_updated_by = g_user_id
2454: gxem.process_category = G_inventory_transactions
2455: and xpae.event_class_code = gxem.event_class_code
2456: )
2457: ;
2458: g_log_msg := sql%rowcount || ' rows updated in gmf_xla_extract_headers table';
2459:
2460: print_debug(g_log_msg);
2461: IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
2462: THEN
2784: THEN
2785: NULL;
2786: WHEN G_post_processing
2787: THEN
2788: g_log_msg := 'Updating gmf_xla_extract_headers table';
2789:
2790: /* Bug 15949925 No need to call this. Done in pre-processor.
2791:
2792: g_log_msg := 'Deleting Duplicate IPV/ERV xla entries';
2819: End Bug 15949925 end commenting code out. */
2820: --
2821: -- Update extract Headers
2822: --
2823: UPDATE gmf_xla_extract_headers
2824: SET accounted_flag = DECODE(G_accounting_mode, 'D', 'D', 'F', NULL)
2825: , actual_posting_date = DECODE(G_accounting_mode, 'F', sysdate, NULL)
2826: , last_update_date = sysdate
2827: , last_updated_by = g_user_id
2840: gxem.process_category = G_purchasing_transactions
2841: and xpae.event_class_code = gxem.event_class_code
2842: )
2843: ;
2844: g_log_msg := sql%rowcount || ' rows updated in gmf_xla_extract_headers table';
2845:
2846: print_debug(g_log_msg);
2847: IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
2848: THEN
3249: THEN
3250: NULL;
3251: WHEN G_post_processing
3252: THEN
3253: g_log_msg := 'Updating gmf_xla_extract_headers table';
3254:
3255: print_debug(g_log_msg);
3256: IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
3257: THEN
3264:
3265: --
3266: -- Update extract Headers
3267: --
3268: UPDATE gmf_xla_extract_headers
3269: SET accounted_flag = DECODE(G_accounting_mode, 'D', 'D', 'F', NULL)
3270: , actual_posting_date = DECODE(G_accounting_mode, 'F', sysdate, NULL)
3271: , last_update_date = sysdate
3272: , last_updated_by = g_user_id
3285: gxem.process_category = G_production_transactions
3286: and xpae.event_class_code = gxem.event_class_code
3287: )
3288: ;
3289: g_log_msg := sql%rowcount || ' rows updated in gmf_xla_extract_headers table';
3290:
3291: print_debug(g_log_msg);
3292: IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
3293: THEN
3623: THEN
3624: NULL;
3625: WHEN G_post_processing
3626: THEN
3627: g_log_msg := 'Updating gmf_xla_extract_headers table';
3628:
3629: print_debug(g_log_msg);
3630: IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
3631: THEN
3638:
3639: --
3640: -- Update extract Headers
3641: --
3642: UPDATE gmf_xla_extract_headers
3643: SET accounted_flag = DECODE(G_accounting_mode, 'D', 'D', 'F', NULL)
3644: , actual_posting_date = DECODE(G_accounting_mode, 'F', sysdate, NULL)
3645: , last_update_date = sysdate
3646: , last_updated_by = g_user_id
3659: gxem.process_category = G_order_management
3660: and xpae.event_class_code = gxem.event_class_code
3661: )
3662: ;
3663: g_log_msg := sql%rowcount || ' rows updated in gmf_xla_extract_headers table';
3664:
3665: print_debug(g_log_msg);
3666: IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
3667: THEN
3802: exists (SELECT 'x'
3803: FROM
3804: xla_post_acctg_events_v xpae,
3805: gmf_xla_event_model gxem,
3806: gmf_xla_extract_headers eh
3807: WHERE
3808: eh.transaction_id = xpae.SOURCE_ID_INT_1
3809: AND eh.ledger_id = xpae.SOURCE_ID_INT_2
3810: AND eh.valuation_cost_type_id = xpae.SOURCE_ID_INT_3
3914: THEN
3915: NULL;
3916: WHEN G_post_processing
3917: THEN
3918: g_log_msg := 'Updating gmf_xla_extract_headers table';
3919:
3920: print_debug(g_log_msg);
3921: IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
3922: THEN
3929:
3930: --
3931: -- Update extract Headers
3932: --
3933: UPDATE gmf_xla_extract_headers
3934: SET accounted_flag = DECODE(G_accounting_mode, 'D', 'D', 'F', NULL)
3935: , actual_posting_date = DECODE(G_accounting_mode, 'F', sysdate, NULL)
3936: , last_update_date = sysdate
3937: , last_updated_by = g_user_id
3950: gxem.process_category = G_revaluation_transactions
3951: and xpae.event_class_code = gxem.event_class_code
3952: )
3953: ;
3954: g_log_msg := sql%rowcount || ' rows updated in gmf_xla_extract_headers table';
3955:
3956: print_debug(g_log_msg);
3957: IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL )
3958: THEN
4610: ,xe.event_id
4611: FROM xla_events xe, xla_ae_headers xah
4612: WHERE xe.application_id = 555
4613: AND xah.application_id = 555
4614: AND xe.event_id not in ( select event_id from gmf_xla_extract_headers where EVENT_TYPE_CODE in ('INVOICE_IPV_ADJ' , 'INVOICE_ERV_ADJ' ) )
4615: AND xe.event_type_code in ('INVOICE_IPV_ADJ' , 'INVOICE_ERV_ADJ' )
4616: AND xe.event_status_code = 'N' /* Bug14325989*/
4617: AND xe.process_status_code = 'P' /* Bug14325989*/
4618: AND xe.event_id = xah.event_id
4632: AND xe.event_status_code = 'N'
4633: AND xe.process_status_code = 'P'
4634: AND xe.event_id = xah.event_id
4635: AND xe.event_id not in ( select event_id
4636: from gmf_xla_extract_headers
4637: where entity_code = 'REVALUATION'
4638: and EVENT_CLASS_CODE = 'ACTCOSTADJ' )
4639: -- Bug: 10121825 made changes to delete unwanted Cost Revaluation transactions.
4640: UNION
4652: AND xe.event_status_code = 'N'
4653: AND xe.process_status_code = 'P'
4654: AND xe.event_id = xah.event_id
4655: AND xe.event_id not in ( select event_id
4656: from gmf_xla_extract_headers
4657: where entity_code = 'REVALUATION'
4658: and EVENT_CLASS_CODE = 'COSTREVAL'
4659: and EVENT_TYPE_CODE = 'COSTREVAL' )
4660: ;
4723: IS
4724:
4725: CURSOR chk_orphan_lot_cost_adj_events IS
4726: SELECT eh.event_id
4727: FROM gmf_xla_extract_headers eh,
4728: gmf_lot_cost_adjustments lc_adj
4729: WHERE eh.entity_code = 'REVALUATION'
4730: AND eh.EVENT_CLASS_CODE = 'LOTCOSTADJ'
4731: AND eh.EVENT_TYPE_CODE = 'LOTCOSTADJ'
4776: g_log_msg := 'Found ' || l_event.event_id ;
4777: print_debug(g_log_msg);
4778:
4779: DELETE FROM gmf_xla_extract_lines
4780: WHERE Header_id = (SELECT header_id FROM gmf_xla_extract_headers
4781: WHERE entity_code = 'REVALUATION'
4782: AND event_class_code = 'LOTCOSTADJ'
4783: AND EVENT_TYPE_CODE = 'LOTCOSTADJ'
4784: AND event_id = l_event.event_id);
4785:
4786: g_log_msg := 'Deleting extract headers for orphan events ';
4787: print_debug(g_log_msg);
4788:
4789: DELETE FROM gmf_xla_extract_headers
4790: WHERE entity_code = 'REVALUATION'
4791: AND event_class_code = 'LOTCOSTADJ'
4792: AND EVENT_TYPE_CODE = 'LOTCOSTADJ'
4793: AND event_id = l_event.event_id;