[Home] [Help]
308: --Pre-reqs:
309: -- PO_ENCUMBRANCE_GT has been updated with the data that should
310: -- be inserted into PO_BC_DISTRIBUTIONS.
311: --Modifies:
312: -- GL_BC_PACKETS
313: --Locks:
314: -- None.
315: --Function:
316: -- This procedure inserts all of the entries in
516:
517: -- Get the next packet_id from the sequence. This is required as
518: -- we insert all the records with same packet ID.
519:
520: SELECT GL_BC_PACKETS_S.nextval
521: INTO x_packet_id
522: FROM DUAL;
523:
524: l_progress := '020';
783: --
784: --We need to set the value of APPLIED_TO_DIST_ID_2 this would be passed
785: --onto the allocation attributes so that a 1-1 mapping can be created
786: --between po_bc_distributions and xla_distribution_links. A 1-1 mapping
787: --already exists between xla_distribution_links and gl_bc_packets. This
788: --is the route to be followed to establish a 1-1 mapping between
789: --po_bc_distributions and xla_distribution_links
790: --
791: -- Performance fix : Also added packet id filter in the inner query to
1001: -------------------------------------------------------------------------------
1002: --Start of Comments
1003: --Name: execute_gl_call
1004: --Pre-reqs:
1005: -- The packet has already been inserted and committed to GL_BC_PACKETS.
1006: --Modifies:
1007: -- GL_BC_PACKETS
1008: --Locks:
1009: -- None.
1003: --Name: execute_gl_call
1004: --Pre-reqs:
1005: -- The packet has already been inserted and committed to GL_BC_PACKETS.
1006: --Modifies:
1007: -- GL_BC_PACKETS
1008: --Locks:
1009: -- None.
1010: --Function:
1011: -- This procedure calls the GL funds checker that operates on the
1008: --Locks:
1009: -- None.
1010: --Function:
1011: -- This procedure calls the GL funds checker that operates on the
1012: -- data in GL_BC_PACKETS and updates that table with the
1013: -- success/failure results.
1014: --Parameters:
1015: --IN:
1016: --p_set_of_books_id
1015: --IN:
1016: --p_set_of_books_id
1017: -- The set of books of this org.
1018: --p_packet_id
1019: -- The packet_id in GL_BC_PACKETS that should be operated on.
1020: --p_gl_mode
1021: -- Specifies whether to call GL in 'R'eserve, 'A'djust or
1022: -- 'F'orce mode
1023: --p_partial_resv_flag
1375: --Pre-reqs:
1376: -- PO_ENCUMBRANCE_GT is populated with all calculations.
1377: -- Call to GL Funds Checker already made on given packet ID.
1378: --Modifies:
1379: -- GL_BC_PACKETS
1380: --Locks:
1381: -- None.
1382: --Function:
1383: -- Copies result information from gl_bc_packets into PO_ENCUMBRANCE_GT
1379: -- GL_BC_PACKETS
1380: --Locks:
1381: -- None.
1382: --Function:
1383: -- Copies result information from gl_bc_packets into PO_ENCUMBRANCE_GT
1384: -- Further post-processing/error reporting relies on this information from GTT
1385: --Parameters:
1386: --IN:
1387: --p_packet_id
1384: -- Further post-processing/error reporting relies on this information from GTT
1385: --Parameters:
1386: --IN:
1387: --p_packet_id
1388: -- ID of the packet inserted into gl_bc_packets table.
1389: --p_gl_return_code
1390: -- The overall transaction return code provided by GL from the glxfck() call
1391: --Testing:
1392: --
1431: l_progress := '010';
1432:
1433: -- Bug#14593047 START: Revamped the query to removed the
1434: -- XLA_DISTRIBUTIONS_LINKS table as the join between po_bc_distributions
1435: -- and gl_bc_packets can be achieved with event id condition
1436: -- This is necessary to improve the performance of the query which will
1437: -- avoid FTS on the XLA table.
1438: --
1439: --Modified the query below to join with gl_bc_packets_hists
1435: -- and gl_bc_packets can be achieved with event id condition
1436: -- This is necessary to improve the performance of the query which will
1437: -- avoid FTS on the XLA table.
1438: --
1439: --Modified the query below to join with gl_bc_packets_hists
1440: --This is so that the error rows are always found. GL moves
1441: --all errored rows to gl_bc_packets_hist for performance reasons.
1442: --
1443: --We have to join with xla_distribution_links to get a mapping between
1437: -- avoid FTS on the XLA table.
1438: --
1439: --Modified the query below to join with gl_bc_packets_hists
1440: --This is so that the error rows are always found. GL moves
1441: --all errored rows to gl_bc_packets_hist for performance reasons.
1442: --
1443: --We have to join with xla_distribution_links to get a mapping between
1444: --po_bc_distributions and gl_bc_packets. We need a distinct to ensure that
1445: --for federal /customization cases where there could be multiple rows in
1440: --This is so that the error rows are always found. GL moves
1441: --all errored rows to gl_bc_packets_hist for performance reasons.
1442: --
1443: --We have to join with xla_distribution_links to get a mapping between
1444: --po_bc_distributions and gl_bc_packets. We need a distinct to ensure that
1445: --for federal /customization cases where there could be multiple rows in
1446: --gl_bc_packets for the same distribution po_bc_distributions we are returned
1447: --just one row.
1448: --
1442: --
1443: --We have to join with xla_distribution_links to get a mapping between
1444: --po_bc_distributions and gl_bc_packets. We need a distinct to ensure that
1445: --for federal /customization cases where there could be multiple rows in
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
1445: --for federal /customization cases where there could be multiple rows in
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.
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: --
1455: -- Performance Fix : Changed in Inner query based on psa_bc_xla_events_gt to
1456: -- change the Plan of the Overall SQL.
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: /*
1460: SELECT
1461: DISTINCT
1462: STATUS_CODE,
1488: PBD.accounted_amt TRANSACTION_AMOUNT,
1489: PBD.reference15 REFERENCE15
1490: FROM XLA_DISTRIBUTION_LINKS XLD,
1491: PO_BC_DISTRIBUTIONS PBD,
1492: GL_BC_PACKETS GLBC
1493: WHERE (GLBC.PACKET_ID, GLBC.CODE_COMBINATION_ID) IN (
1494: SELECT /*+ unnest */
1495: /* DISTINCT glbc1.packet_id,
1496: glbc1.code_combination_id
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
1502: AND xld.event_id = GLBC.event_id
1526: PBD.accounted_amt TRANSACTION_AMOUNT,
1527: PBD.reference15 REFERENCE15
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)
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
1537: AND xld.event_id = GLBCH.event_id
1589: * PBD.ACCOUNTED_AMT
1590: TRANSACTION_AMOUNT,
1591: PBD.REFERENCE15 REFERENCE15
1592: FROM PO_BC_DISTRIBUTIONS PBD,
1593: GL_BC_PACKETS GLBC
1594: WHERE pbd.packet_id = p_packet_id
1595: AND pbd.ae_EVENT_ID = GLBC.EVENT_ID
1596: AND GLBC.TEMPLATE_ID IS NULL
1597: AND GLBC.SOURCE_DISTRIBUTION_ID_NUM_1 = pbd.distribution_id --Bug 16437550
1614: * PBD.ACCOUNTED_AMT
1615: TRANSACTION_AMOUNT,
1616: PBD.REFERENCE15 REFERENCE15
1617: FROM PO_BC_DISTRIBUTIONS PBD,
1618: GL_BC_PACKETS_HISTS GLBCH
1619: WHERE pbd.packet_id = p_packet_id
1620: AND pbd.ae_EVENT_ID = GLBCH.EVENT_ID
1621: AND GLBCH.TEMPLATE_ID IS NULL
1622: AND GLBCH.SOURCE_DISTRIBUTION_ID_NUM_1 = pbd.distribution_id --Bug 16437550
2640: /* <
2641:
2642:
2643: -- Now the updation of po_distributions/po_req_distributions for all the
2644: -- approved GL_BC_PACKETS records is done. Just rollup the encumbered_flag
2645: -- to the PO line locations and Requisition lines
2646:
2647: If l_flip_enc_flag = 'Y' Then
2648:
4356: --Name: delete_packet_autonomous
4357: --Pre-reqs:
4358: -- None.
4359: --Modifies:
4360: -- GL_BC_PACKETS
4361: --Locks:
4362: -- None.
4363: --Function:
4364: -- This procedure takes a packet id and deletes and
4361: --Locks:
4362: -- None.
4363: --Function:
4364: -- This procedure takes a packet id and deletes and
4365: -- commits it to GL_BC_PACKETS in an autonomous transaction.
4366: --Parameters:
4367: --IN:
4368: --p_packet_id
4369: -- The GL_BC_PACKETS.packet_id for the packet that is to be deleted.
4365: -- commits it to GL_BC_PACKETS in an autonomous transaction.
4366: --Parameters:
4367: --IN:
4368: --p_packet_id
4369: -- The GL_BC_PACKETS.packet_id for the packet that is to be deleted.
4370: --Testing:
4371: --
4372: --End of Comments
4373: -------------------------------------------------------------------------------