[Home] [Help]
371: x_packet_id => x_packet_id
372: );
373:
374: l_progress := '200';
375: -- Populate eventids in psa_bc_xla_events_gt for these events
376: -- to be considered by Budgetary Control API
377: IF g_debug_stmt THEN
378: PO_DEBUG.debug_var(l_log_head,l_progress,'x_packet_id',x_packet_id);
379: END IF;
387: -- in one transaction boundary, PSA is getting the event id of previous lines
388: -- also which it has allready processed in this tranaction.
389: -- So clearing the event GT table before we insert any new event.
390:
391: DELETE FROM psa_bc_xla_events_gt
392: WHERE event_id IN ( SELECT distinct ae_event_id
393: FROM po_bc_distributions
394: WHERE packet_id <> x_packet_id
395: );
394: WHERE packet_id <> x_packet_id
395: );
396:
397:
398: INSERT into psa_bc_xla_events_gt
399: (
400: event_id,
401: result_code -- Bug #4637958
402: )
478: l_appl_id NUMBER; --
479: l_login_id NUMBER; --
480: l_event_type_code VARCHAR2(50) ;
481:
482: l_event_source_info xla_events_pub_pkg.t_event_source_info;
483: l_security_context xla_events_pub_pkg.t_security;
484: l_reference_info xla_events_pub_pkg.t_event_reference_info;
485: l_event_date DATE;
486: l_event_status_code VARCHAR2(30);
479: l_login_id NUMBER; --
480: l_event_type_code VARCHAR2(50) ;
481:
482: l_event_source_info xla_events_pub_pkg.t_event_source_info;
483: l_security_context xla_events_pub_pkg.t_security;
484: l_reference_info xla_events_pub_pkg.t_event_reference_info;
485: l_event_date DATE;
486: l_event_status_code VARCHAR2(30);
487: l_event_number NUMBER;
480: l_event_type_code VARCHAR2(50) ;
481:
482: l_event_source_info xla_events_pub_pkg.t_event_source_info;
483: l_security_context xla_events_pub_pkg.t_security;
484: l_reference_info xla_events_pub_pkg.t_event_reference_info;
485: l_event_date DATE;
486: l_event_status_code VARCHAR2(30);
487: l_event_number NUMBER;
488: l_valuation_method VARCHAR2(30);
497: l_num_of_rows_inserted NUMBER;
498:
499: l_id_tbl po_tbl_number;
500:
501: TYPE Event_tab_type IS TABLE OF XLA_EVENTS_INT_GT%ROWTYPE INDEX BY BINARY_INTEGER;
502: l_events_Tab Event_tab_type;
503: l_event_count NUMBER;
504:
505: BEGIN
829: IF g_debug_stmt THEN
830: PO_DEBUG.debug_var(l_log_head,l_progress,'l_legal_entity_id ',l_legal_entity_id );
831: END IF;
832:
833: l_event_status_code := xla_events_pub_pkg.c_event_unprocessed;
834: IF g_debug_stmt THEN
835: PO_DEBUG.debug_var(l_log_head,l_progress,'l_event_status_code ',l_event_status_code );
836: END IF;
837:
852: xe.event_status_code,
853: xe.process_status_code,
854: pbd.applied_to_header_id_1,
855: pbd.applied_to_entity_code
856: FROM xla_events xe,
857: po_bc_distributions pbd
858: WHERE NVL(xe.budgetary_control_flag, 'N') ='Y'
859: AND xe.EVENT_STATUS_CODE in ('U' ,'I')
860: AND xe.PROCESS_STATUS_CODE IN ('I','D')
862: AND pbd.packet_id <> x_packet_id
863: AND pbd.ae_event_id IS NOT NULL
864: AND main_or_backing_code = 'M'
865: -- AND NVL(status_code,'I') <> 'P'
866: --Bug 16010392. If event status is U in xla_events and P in po_bc_dists,
867: --Such event has to be deleted.
868: AND (pbd.header_id,pbd.event_type_code) IN (SELECT DISTINCT header_id,event_type_code FROM po_bc_distributions WHERE packet_id = x_packet_id)
869: ) LOOP
870:
888: IF l_event_count > 0 THEN
889:
890: FORALL i IN 1..l_event_count
891:
892: INSERT INTO XLA_EVENTS_INT_GT
893: VALUES l_events_tab(i) ;
894:
895: XLA_EVENTS_PUB_PKG.DELETE_BULK_EVENTS(p_application_id => 201);
896:
891:
892: INSERT INTO XLA_EVENTS_INT_GT
893: VALUES l_events_tab(i) ;
894:
895: XLA_EVENTS_PUB_PKG.DELETE_BULK_EVENTS(p_application_id => 201);
896:
897: FOR i in 1..l_event_count LOOP
898:
899: DELETE FROM po_bc_distributions
940: l_event_source_info.transaction_number := rec_po_bc_dist.reference_number;
941: l_event_source_info.source_id_int_1 := rec_po_bc_dist.applied_to_header_id_1;
942: l_security_context.security_id_int_1 := l_current_org_id ;
943:
944: l_event_id := xla_events_pub_pkg.create_event
945: (
946: p_event_source_info => l_event_source_info,
947: p_event_type_code => rec_po_bc_dist.event_type_code,
948: p_event_date => rec_po_bc_dist.gl_date,
1225: IS
1226:
1227: TYPE t_event_id IS TABLE OF po_bc_distributions.ae_event_id%TYPE;
1228: l_event_id t_event_id := t_event_id();
1229: l_event_source_info xla_events_pub_pkg.t_event_source_info;
1230: l_delete_event NUMBER;
1231: l_security_context xla_events_pub_pkg.t_security;
1232: l_api_name CONSTANT varchar2(30) := 'delete_unnecessary_events';
1233: l_log_head CONSTANT varchar2(100) := g_log_head || l_api_name;
1227: TYPE t_event_id IS TABLE OF po_bc_distributions.ae_event_id%TYPE;
1228: l_event_id t_event_id := t_event_id();
1229: l_event_source_info xla_events_pub_pkg.t_event_source_info;
1230: l_delete_event NUMBER;
1231: l_security_context xla_events_pub_pkg.t_security;
1232: l_api_name CONSTANT varchar2(30) := 'delete_unnecessary_events';
1233: l_log_head CONSTANT varchar2(100) := g_log_head || l_api_name;
1234: l_progress VARCHAR2(3) := '000';
1235:
1239: pbd.applied_to_header_id_1,
1240: pbd.distribution_type
1241: FROM
1242: po_bc_distributions pbd,
1243: xla_events xe,
1244: xla_transaction_entities xte
1245: WHERE
1246: xe.event_id = pbd.ae_event_id
1247: AND xe.EVENT_STATUS_CODE = 'U'
1257: pbd.applied_to_header_id_1,
1258: pbd.distribution_type
1259: FROM
1260: po_bc_distributions pbd,
1261: xla_events xe,
1262: xla_transaction_entities xte
1263: WHERE
1264: xe.event_id = pbd.ae_event_id
1265: AND xe.EVENT_STATUS_CODE = 'P'
1319: PO_DEBUG.debug_var(l_log_head,l_progress,'l_event_source_info.transaction_number', l_event_source_info.transaction_number);
1320: PO_DEBUG.debug_var(l_log_head,l_progress,'l_event_source_info.source_id_int_1', l_event_source_info.source_id_int_1 );
1321: END IF;
1322:
1323: xla_events_pub_pkg.DELETE_EVENT
1324: (
1325: p_event_source_info => l_event_source_info,
1326: p_event_id => rec_to_del.ae_event_id,
1327: p_valuation_method => NULL,
1345: PO_DEBUG.debug_var(l_log_head,l_progress,'l_event_source_info.transaction_number', l_event_source_info.transaction_number);
1346: PO_DEBUG.debug_var(l_log_head,l_progress,'l_event_source_info.source_id_int_1', l_event_source_info.source_id_int_1 );
1347: END IF;
1348:
1349: xla_events_pub_pkg.DELETE_EVENT
1350: (
1351: p_event_source_info => l_event_source_info,
1352: p_event_id => rec_to_del.ae_event_id,
1353: p_valuation_method => NULL,
1446: --gl_bc_packets for the same distribution po_bc_distributions we are returned
1447: --just one row.
1448: --
1449: -- We will get all the packets id from gl_bc_packets for the events which we have populated in
1450: -- psa_bc_xla_events_gt table for the current packet Id. We won't use the packet id returned by
1451: -- Execute GL call as there can be mutiple packet id from which we need to take the result from
1452: -- GL BC PACKETS. In case of Cancel action with recreate demand we create two events, which belong
1453: -- to different entity. So two packets will be created by PSA.
1454: --
1451: -- Execute GL call as there can be mutiple packet id from which we need to take the result from
1452: -- GL BC PACKETS. In case of Cancel action with recreate demand we create two events, which belong
1453: -- to different entity. So two packets will be created by PSA.
1454: --
1455: -- Performance Fix : Changed in Inner query based on psa_bc_xla_events_gt to
1456: -- change the Plan of the Overall SQL.
1457: /* bug 13562823 - To improve performance added hint and column code_combination_id so that index on packet_id and code_combination_id
1458: on table GL_BC_PACKETS would be considered. */
1459: /*
1493: WHERE (GLBC.PACKET_ID, GLBC.CODE_COMBINATION_ID) IN (
1494: SELECT /*+ unnest */
1495: /* DISTINCT glbc1.packet_id,
1496: glbc1.code_combination_id
1497: FROM psa_bc_xla_events_gt ps_ev_Gt,
1498: GL_BC_PACKETS glbc1
1499: WHERE ps_ev_Gt.event_id = glbc1.event_id)
1500: AND XLD.AE_HEADER_ID = GLBC.ae_header_id
1501: AND xld.ae_line_num = GLBC.ae_line_num
1528: FROM XLA_DISTRIBUTION_LINKS XLD,
1529: PO_BC_DISTRIBUTIONS PBD,
1530: GL_BC_PACKETS_HISTS GLBCH
1531: WHERE GLBCH.PACKET_ID IN (SELECT DISTINCT glbch1.packet_id
1532: FROM psa_bc_xla_events_gt ps_ev_Gt ,
1533: GL_BC_PACKETS_HISTS glbch1
1534: WHERE ps_ev_Gt.event_id = glbch1.event_id)
1535: AND XLD.AE_HEADER_ID = GLBCH.ae_header_id
1536: AND xld.ae_line_num = GLBCH.ae_line_num
4380: l_proc_name CONSTANT VARCHAR2(30) := 'DELETE_PACKET_AUTONOMOUS';
4381: l_log_head CONSTANT VARCHAR2(100) := g_log_head || l_proc_name;
4382: l_progress VARCHAR2(3) := '000';
4383: --
4384: -- SQL What: Querying for events currently in PSA_BC_XLA_EVENTS_GT
4385: -- SQL Why : Need to delete all data related to these events from PSA tables
4386: -- SQL Join: None
4387: CURSOR cur_events IS
4388: SELECT event_id
4385: -- SQL Why : Need to delete all data related to these events from PSA tables
4386: -- SQL Join: None
4387: CURSOR cur_events IS
4388: SELECT event_id
4389: FROM PSA_BC_XLA_EVENTS_GT;
4390:
4391: BEGIN
4392:
4393: IF g_debug_stmt THEN
4399:
4400: --
4401: FOR REC_EVENTS IN cur_events LOOP
4402: -- call the XLA API to delete events
4403: xla_events_pub_pkg.DELETE_EVENT
4404: (
4405: p_event_source_info => NULL,
4406: p_event_id => REC_EVENTS.event_id,
4407: p_valuation_method => NULL,