[Home] [Help]
280: , x_online_report_id OUT NOCOPY NUMBER
281: );
282:
283: --
284: PROCEDURE delete_po_bc_distributions
285: (
286: p_packet_id IN NUMBER
287: ) ;
288:
306: --Start of Comments
307: --Name: insert_packet
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.
313: --Locks:
314: -- None.
315: --Function:
316: -- This procedure inserts all of the entries in
317: -- PO_ENCUMBRANCE_GT into PO_BC_DISTRIBUTIONS. It does this through
318: -- an autonomous transaction helper procedure, as GL needs the data
319: -- committed, but we don't want to commit anything else.
320: --Parameters:
321: --IN:
328: --p_currency_code
329: -- The currency code for the current org.
330: --OUT:
331: --x_packet_id
332: -- The PO_BC_DISTRIBUTIONS.packet_id that was used for this packet.
333: -- If a packet was not created, this will be NULL.
334: --Testing:
335: --
336: --End of Comments
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: );
396:
397:
400: event_id,
401: result_code -- Bug #4637958
402: )
403: SELECT distinct ae_event_id,'XLA_ERROR'
404: FROM po_bc_distributions
405: WHERE packet_id = x_packet_id;
406: END IF;
407:
408: l_progress := '300';
425: --Name: INSERT_PACKET_CREATE_EVENT
426: --Pre-reqs:
427: -- None.
428: --Modifies:
429: -- PO_BC_DISTRIBUTIONS
430: --Locks:
431: -- None.
432: --Function:
433: -- This procedure takes its input parameters and inserts and
430: --Locks:
431: -- None.
432: --Function:
433: -- This procedure takes its input parameters and inserts and
434: -- transfers data from po_encumbrance_gt to po_bc_distributions.
435: --Parameters:
436: --IN:
437: -- p_status_code
438: -- Specifies whether this is a Funds Check (C) or a real (pending) action (P).
443: -- p_currency_code
444: -- The currency code for the current org.
445: --OUT:
446: --x_packet_id
447: -- The PO_BC_DISTRIBUTIONS.packet_id that was used for this packet.
448: --Testing:
449: --
450: --End of Comments
451: -------------------------------------------------------------------------------
532: l_login_id := FND_GLOBAL.login_id ;
533:
534:
535:
536: -- Insert the data into PO_BC_DISTRIBUTIONS from PO_ENCUMBRANCE_GT,
537: --bug#5523323 Modified the insert statement to fetch data directly
538: --from the po_encumbrance_gt table rather than collections. This
539: --is possible because the autonomous transaction no longer exists.
540: INSERT INTO PO_BC_DISTRIBUTIONS
536: -- Insert the data into PO_BC_DISTRIBUTIONS from PO_ENCUMBRANCE_GT,
537: --bug#5523323 Modified the insert statement to fetch data directly
538: --from the po_encumbrance_gt table rather than collections. This
539: --is possible because the autonomous transaction no longer exists.
540: INSERT INTO PO_BC_DISTRIBUTIONS
541: ( BC_DISTRIBUTION_ID,
542: PACKET_ID,
543: STATUS_CODE,
544: CREATION_DATE,
597: ORIGIN_SEQUENCE_NUM,
598: CLM_DOC_FLAG /* <
599: )
600: SELECT
601: PO_BC_DISTRIBUTIONS_S.nextval,
602: x_packet_id,
603: p_status_code,
604: SYSDATE,
605: p_user_id,
710:
711: l_progress := '110';
712:
713: -- Updating event_type_code for main record.
714: UPDATE po_bc_distributions pbd
715: SET event_type_code = (select get_event_type_code(pbd.distribution_type,p_action) from dual)
716: WHERE packet_id = x_packet_id
717: AND main_or_backing_code = 'M'
718: RETURNING sequence_number
722:
723: -- Updating event_type_code for backing record.
724:
725: FORALL i IN 1..l_id_tbl.count
726: UPDATE po_bc_distributions pbd
727: SET event_type_code = (SELECT event_type_code
728: FROM po_bc_distributions pbd1
729: WHERE pbd1.packet_id = x_packet_id
730: AND pbd1.sequence_number=l_id_tbl(i))
724:
725: FORALL i IN 1..l_id_tbl.count
726: UPDATE po_bc_distributions pbd
727: SET event_type_code = (SELECT event_type_code
728: FROM po_bc_distributions pbd1
729: WHERE pbd1.packet_id = x_packet_id
730: AND pbd1.sequence_number=l_id_tbl(i))
731: WHERE packet_id = x_packet_id
732: AND main_or_backing_code <> 'M'
751: IF p_action = 'CANCEL' THEN
752:
753: SELECT prd.distribution_id BULK COLLECT INTO l_id_tbl
754: FROM po_req_distributions_all prd,
755: po_bc_distributions pbd1, -- for Backing
756: po_bc_distributions pbd2 -- For Main
757: WHERE pbd1.packet_id = x_packet_id
758: AND prd.distribution_id = pbd1.distribution_id
759: AND prd.source_req_distribution_id IS NOT NULL
752:
753: SELECT prd.distribution_id BULK COLLECT INTO l_id_tbl
754: FROM po_req_distributions_all prd,
755: po_bc_distributions pbd1, -- for Backing
756: po_bc_distributions pbd2 -- For Main
757: WHERE pbd1.packet_id = x_packet_id
758: AND prd.distribution_id = pbd1.distribution_id
759: AND prd.source_req_distribution_id IS NOT NULL
760: AND pbd1.main_or_backing_code = 'B_REQ'
762: AND pbd2.event_type_code IN ('PO_PA_CANCELLED','RELEASE_CANCELLED');
763:
764:
765: FORALL i IN 1..l_id_tbl.COUNT
766: UPDATE po_bc_distributions pbd
767: SET main_or_backing_code = 'M' ,event_type_code = 'REQ_RESERVED' , origin_sequence_num = NULL ,
768: entered_amt = entered_amt * -1 ,accounted_amt = accounted_amt * -1
769: WHERE pbd.packet_id = x_packet_id
770: AND pbd.main_or_backing_code = 'B_REQ'
782:
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: --
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
792: -- avoid a full table scan and to use index based on packet id.
793: UPDATE PO_BC_DISTRIBUTIONS PBD
789: --po_bc_distributions and xla_distribution_links
790: --
791: -- Performance fix : Also added packet id filter in the inner query to
792: -- avoid a full table scan and to use index based on packet id.
793: UPDATE PO_BC_DISTRIBUTIONS PBD
794: SET PBD.line_number = PBD.bc_distribution_id,
795: PBD.APPLIED_TO_DIST_ID_2=
796: (
797: SELECT ORIG.distribution_id
794: SET PBD.line_number = PBD.bc_distribution_id,
795: PBD.APPLIED_TO_DIST_ID_2=
796: (
797: SELECT ORIG.distribution_id
798: FROM PO_BC_DISTRIBUTIONS ORIG
799: WHERE ORIG.sequence_number=PBD.origin_sequence_num
800: AND ORIG.packet_id = x_packet_id
801: )
802: WHERE PBD.packet_id = x_packet_id ;
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')
861: AND xe.event_id =pbd.ae_event_id
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:
871: l_event_count := l_event_count+1;
872: l_events_tab(l_event_count).entity_id := rec_events.entity_id;
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
900: WHERE applied_to_header_id_1 = l_events_tab(i).source_id_int_1
901: AND packet_id <> x_packet_id
902: AND ae_event_id = l_events_tab(i).event_id;
903:
911: END IF;
912:
913: l_progress := '170' ;
914:
915: -- Loop for all distributions in po_bc_distributions for generation of event ids
916: -- Event id is unique for distribution type and gl date combination
917:
918:
919: FOR rec_po_bc_dist IN (SELECT DISTINCT applied_to_header_id_1,segment1,distribution_type,gl_date,event_type_code,reference_number
916: -- Event id is unique for distribution type and gl date combination
917:
918:
919: FOR rec_po_bc_dist IN (SELECT DISTINCT applied_to_header_id_1,segment1,distribution_type,gl_date,event_type_code,reference_number
920: FROM po_bc_distributions
921: WHERE packet_id = x_packet_id
922: AND main_or_backing_code = 'M')
923: LOOP
924:
953: p_valuation_method => l_valuation_method,
954: p_security_context => l_security_context,
955: p_budgetary_control_flag => 'Y'
956: );
957: -- Update po_bc_distributions ae_event_id with the l_event_id.
958:
959: IF g_debug_stmt THEN
960:
961: PO_DEBUG.debug_stmt(l_log_head,l_progress,'l_event_id ' || l_event_id);
963:
964:
965: IF l_event_id IS NOT NULL then
966:
967: UPDATE po_bc_distributions
968: SET ae_event_id = l_event_id
969: WHERE packet_id = x_packet_id
970: AND applied_to_header_id_1 = rec_po_bc_dist.applied_to_header_id_1
971: AND gl_date = rec_po_bc_dist.gl_date
973: returning sequence_number
974: BULK COLLECT INTO l_seq_num_tbl;
975:
976: FORALL i IN 1..l_seq_num_tbl.count
977: UPDATE po_bc_distributions pobd
978: SET pobd.ae_event_id = l_event_id
979: WHERE pobd.packet_id = x_packet_id
980: AND pobd.origin_sequence_num = l_seq_num_tbl(i);
981:
1138: OR l_status_code IN ('FATAL', 'XLA_ERROR', 'XLA_NO_JOURNAL' ) -- Bug 5009730
1139: )
1140: THEN
1141: l_progress := '125';
1142: -- Delete records from po_bc_distributions
1143: --bug#5523323 once we remove the autonomous txn we don't have to explicity delete
1144: --data from po_bc_distributions.
1145: --Delete_PO_BC_Distributions(p_packet_id => p_packet_id); -- Bug #4637958
1146:
1140: THEN
1141: l_progress := '125';
1142: -- Delete records from po_bc_distributions
1143: --bug#5523323 once we remove the autonomous txn we don't have to explicity delete
1144: --data from po_bc_distributions.
1145: --Delete_PO_BC_Distributions(p_packet_id => p_packet_id); -- Bug #4637958
1146:
1147: l_progress := '130';
1148: RAISE g_GL_FUNDS_API_EXC;
1141: l_progress := '125';
1142: -- Delete records from po_bc_distributions
1143: --bug#5523323 once we remove the autonomous txn we don't have to explicity delete
1144: --data from po_bc_distributions.
1145: --Delete_PO_BC_Distributions(p_packet_id => p_packet_id); -- Bug #4637958
1146:
1147: l_progress := '130';
1148: RAISE g_GL_FUNDS_API_EXC;
1149: END IF;
1197: --Name: delete_unnecessary_eventss
1198: --Pre-reqs:
1199: --None
1200: --Modifies:
1201: -- PO_BC_DISTRIBUTIONS
1202: --Locks:
1203: -- None.
1204: --Function:
1205: --Based on records inserted into po_bc_distributions during insert_packet_create_event,
1201: -- PO_BC_DISTRIBUTIONS
1202: --Locks:
1203: -- None.
1204: --Function:
1205: --Based on records inserted into po_bc_distributions during insert_packet_create_event,
1206: --this function identifies the unprocessed events associated with current encumbrance action
1207: --which are in draft/invalid status. Records in PO_BC corresponding to such events are deleted and those
1208: --events are also deleted.
1209: --Parameters:
1208: --events are also deleted.
1209: --Parameters:
1210: --IN:
1211: --p_packet_id
1212: -- ID of the packet inserted into PO_BC_DISTRIBUTIONS table.
1213: --p_action
1214: -- The current encumbrance action
1215: --Testing:
1216: --
1223:
1224: )
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;
1238: pbd.segment1,
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
1256: pbd.segment1,
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
1327: p_valuation_method => NULL,
1328: p_security_context => l_security_context
1329: );
1330: END LOOP;
1331: DELETE FROM po_bc_distributions WHERE packet_id = p_packet_id;
1332: ELSE
1333:
1334: FOR rec_to_del IN to_delete_invalids loop
1335:
1354: p_security_context => l_security_context
1355: );
1356:
1357: END LOOP;
1358: DELETE FROM po_bc_distributions WHERE packet_id = p_packet_id ;
1359:
1360: END IF;
1361:
1362: EXCEPTION
1430: --
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: --
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
1487: pbd.distribution_type) *
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,
1525: pbd.distribution_type) *
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
1588: )
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
1613: )
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
4436: --
4437:
4438: -------------------------------------------------------------------------------
4439: --Start of Comments
4440: --Name: DELETE_PO_BC_DISTRIBUTIONS
4441: --Pre-reqs:
4442: -- None.
4443: --Modifies:
4444: -- PO_BC_DISTRIBUTIONS.
4440: --Name: DELETE_PO_BC_DISTRIBUTIONS
4441: --Pre-reqs:
4442: -- None.
4443: --Modifies:
4444: -- PO_BC_DISTRIBUTIONS.
4445: --Locks:
4446: -- None.
4447: --Function:
4448: -- This procedure deletes from po_bc_distributions all rows corresponding to
4444: -- PO_BC_DISTRIBUTIONS.
4445: --Locks:
4446: -- None.
4447: --Function:
4448: -- This procedure deletes from po_bc_distributions all rows corresponding to
4449: -- a specified packetid.
4450: --Parameters:
4451: --IN:
4452: -- p_packet_id.
4455: --OUT:
4456: -- None.
4457: --Notes:
4458: -- The algorithm of the procedure is as follows :
4459: -- This procedure deletes from po_bc_distributions all rows corresponding to
4460: -- a specified packetid.
4461: --Testing:
4462: --
4463: --End of Comments
4461: --Testing:
4462: --
4463: --End of Comments
4464: -------------------------------------------------------------------------------
4465: PROCEDURE Delete_PO_BC_Distributions (
4466: p_packet_id IN NUMBER
4467: )
4468: IS
4469: BEGIN
4467: )
4468: IS
4469: BEGIN
4470:
4471: -- Delete all records for the packet processed from po_bc_distributions
4472: DELETE FROM po_bc_distributions
4473: WHERE packet_id = p_packet_id;
4474:
4475: END Delete_PO_BC_Distributions;
4468: IS
4469: BEGIN
4470:
4471: -- Delete all records for the packet processed from po_bc_distributions
4472: DELETE FROM po_bc_distributions
4473: WHERE packet_id = p_packet_id;
4474:
4475: END Delete_PO_BC_Distributions;
4476:
4471: -- Delete all records for the packet processed from po_bc_distributions
4472: DELETE FROM po_bc_distributions
4473: WHERE packet_id = p_packet_id;
4474:
4475: END Delete_PO_BC_Distributions;
4476:
4477: --
4478: -------------------------------------------------------------------------------
4479: --Start of Comments
4480: --Name: populate_bc_report_id
4481: --Pre-reqs:
4482: -- None.
4483: --Modifies:
4484: -- PO_BC_DISTRIBUTIONS.
4485: --Locks:
4486: -- None.
4487: --Function:
4488: -- This procedure populates online_report_id on po_bc_distributions rows corresponding to
4484: -- PO_BC_DISTRIBUTIONS.
4485: --Locks:
4486: -- None.
4487: --Function:
4488: -- This procedure populates online_report_id on po_bc_distributions rows corresponding to
4489: -- a given encumbrance transaction.
4490: --Parameters:
4491: --IN:
4492: -- p_online_report_id.
4497: -- None.
4498: --Notes:
4499: -- The algorithm of the procedure is as follows :
4500: -- Get all distributions in the global temporary table and populate
4501: -- their counterparts in po_bc_distributions with online_report_id
4502: --Testing:
4503: --
4504: --End of Comments
4505: -------------------------------------------------------------------------------
4519: End If;
4520: --bug#5523323 Remove the call to populate_aut_bc_report_id as this is not necessary.
4521: --we have removed the autonomous transaction.
4522: UPDATE
4523: PO_BC_DISTRIBUTIONS
4524: SET online_report_id=p_online_report_id
4525: WHERE reference15 in
4526: (
4527: SELECT reference15
4581: --OUT:
4582: -- None.
4583: --Notes:
4584: -- The algorithm of the procedure is as follows :
4585: -- Populate all rows in po_bc_distributions matching the reference15 column with
4586: -- p_online_report_id
4587: --Testing:
4588: --
4589: --End of Comments