[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: )
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