[Home] [Help]
265:
266: g_date_format CONSTANT varchar2(25) := 'YYYY/MM/DD';
267:
268: -- result classifications
269: g_result_SUCCESS CONSTANT PO_ENCUMBRANCE_GT.result_type%TYPE
270: := PO_DOCUMENT_FUNDS_PVT.g_result_SUCCESS;
271:
272: g_result_WARNING CONSTANT PO_ENCUMBRANCE_GT.result_type%TYPE
273: := PO_DOCUMENT_FUNDS_PVT.g_result_WARNING;
268: -- result classifications
269: g_result_SUCCESS CONSTANT PO_ENCUMBRANCE_GT.result_type%TYPE
270: := PO_DOCUMENT_FUNDS_PVT.g_result_SUCCESS;
271:
272: g_result_WARNING CONSTANT PO_ENCUMBRANCE_GT.result_type%TYPE
273: := PO_DOCUMENT_FUNDS_PVT.g_result_WARNING;
274:
275: g_result_ERROR CONSTANT PO_ENCUMBRANCE_GT.result_type%TYPE
276: := PO_DOCUMENT_FUNDS_PVT.g_result_ERROR;
271:
272: g_result_WARNING CONSTANT PO_ENCUMBRANCE_GT.result_type%TYPE
273: := PO_DOCUMENT_FUNDS_PVT.g_result_WARNING;
274:
275: g_result_ERROR CONSTANT PO_ENCUMBRANCE_GT.result_type%TYPE
276: := PO_DOCUMENT_FUNDS_PVT.g_result_ERROR;
277:
278: --note: this classification currently maps to Warning, but is
279: --given a seperate label so as to easily identify this condition
276: := PO_DOCUMENT_FUNDS_PVT.g_result_ERROR;
277:
278: --note: this classification currently maps to Warning, but is
279: --given a seperate label so as to easily identify this condition
280: g_result_NOT_PROCESSED CONSTANT PO_ENCUMBRANCE_GT.result_type%TYPE
281: := PO_DOCUMENT_FUNDS_PVT.g_result_WARNING;
282:
283:
284: -- doc state check results
481: -- For POs with backing GAs, the org context of the PO must be set
482: -- in order to correctly determine whether or not there needs to
483: -- be a backing entry for the GA.
484: --Modifies:
485: -- PO_ENCUMBRANCE_GT
486: --Locks:
487: -- PO_REQUISITION_HEADERS_ALL
488: -- PO_REQ_DISTRIBUTIONS_ALL
489: -- PO_HEADERS_ALL
489: -- PO_HEADERS_ALL
490: -- PO_RELEASES_ALL
491: -- PO_DISTRIBUTIONS_ALL
492: --Function:
493: -- This procedure populates the global temp table PO_ENCUMBRANCE_GT
494: -- with all of the information required for each distribution that has any
495: -- encumbrance impact.
496: -- It will either retrieve the information from the document tables for
497: -- the given doc ids or use what has already been populated in the
626: --
627: -- populate_encumbrance_gt ( main doc ) ->
628: -- lock_headers
629: -- lock_distributions
630: --
632: -- remove_unnecessary_dists
633: --
634: -- update_encumbrance_gt ( main doc )
640: --
641: -- filter_backing_distributions ( backing Reqs )
642: --
643: -- populate_encumbrance_gt ( backing Reqs )
644: --
646: -- update_encumbrance_gt ( backing Reqs )
647: --
648: -- get_distributions ( backing GAs ) ->
652: --
653: -- filter_backing_distributions ( backing GAs )
654: --
655: -- populate_encumbrance_gt ( backing GAs )
656: --
658: -- update_encumbrance_gt ( backing GAs )
659: --
660: -- END get_all_distributions
851: --Name: initialize_encumbrance_gt
852: --Pre-reqs:
853: -- None.
854: --Modifies:
855: -- PO_ENCUMBRANCE_GT
856: --Locks:
857: -- None.
858: --Function:
859: -- Cleans the encumbrance table for use.
889: IF (p_use_enc_gt_flag = g_parameter_NO) THEN
890:
891: l_progress := '020';
892: IF g_debug_stmt THEN
893: PO_DEBUG.debug_stmt(l_log_head,l_progress,'Truncating PO_ENCUMBRANCE_GT');
894: END IF;
895:
896: -- If we shouldn't be using whatever was in the encumbrance table,
897: -- get rid of all of it.
903: ELSIF (p_use_enc_gt_flag = g_parameter_YES) THEN
904:
905: l_progress := '040';
906: IF g_debug_stmt THEN
907: PO_DEBUG.debug_table(l_log_head,l_progress,'PO_ENCUMBRANCE_GT',PO_DEBUG.g_all_rows,NULL,'PO');
908: PO_DEBUG.debug_stmt(l_log_head,l_progress,'Cleaning PO_ENCUMBRANCE_GT');
909: END IF;
910:
911: -- If we are trusting the data in the encumbrance table,
904:
905: l_progress := '040';
906: IF g_debug_stmt THEN
907: PO_DEBUG.debug_table(l_log_head,l_progress,'PO_ENCUMBRANCE_GT',PO_DEBUG.g_all_rows,NULL,'PO');
908: PO_DEBUG.debug_stmt(l_log_head,l_progress,'Cleaning PO_ENCUMBRANCE_GT');
909: END IF;
910:
911: -- If we are trusting the data in the encumbrance table,
912: -- we need to make sure that we have our own keys into it.
910:
911: -- If we are trusting the data in the encumbrance table,
912: -- we need to make sure that we have our own keys into it.
913:
914: UPDATE PO_ENCUMBRANCE_GT
915: SET
916: sequence_num = NULL
917: , origin_sequence_num = NULL
918: ;
962: -- For POs with backing GAs, the org context of the PO must be set
963: -- in order to correctly determine whether or not there needs to
964: -- be a backing entry for the GA.
965: --Modifies:
966: -- PO_ENCUMBRANCE_GT
967: --Locks:
968: -- PO_REQUISITION_HEADERS_ALL
969: -- PO_REQ_DISTRIBUTIONS_ALL
970: -- PO_HEADERS_ALL
1258: l_progress := '410';
1259:
1260: SELECT COUNT(*)
1261: INTO x_count
1262: FROM PO_ENCUMBRANCE_GT ENC
1263: WHERE ENC.sequence_num IS NULL
1264: ;
1265:
1266: l_progress := '420';
1325: --Name: remove_unnecessary_dists
1326: --Pre-reqs:
1327: -- The encumbrance table has been populated only with main doc data.
1328: --Modifies:
1329: -- PO_ENCUMBRANCE_GT
1330: --Locks:
1331: -- None.
1332: --Function:
1333: -- For the given action, removes any rows that aren't relevant to the
1367:
1368: -- Remove any entries that have no encumbrance impact.
1369: -- Keep the other prevent encumbrance rows, as they need to be reported.
1370:
1371: DELETE FROM PO_ENCUMBRANCE_GT ENC
1372: WHERE
1373: -- Delete already encumbered rows for Reserve, FC
1374: -- Bug 3364450: also delete enc rows for INV Cancel
1375: ( p_action IN ( g_action_RESERVE
1498: -- For POs with backing GAs, the org context of the PO must be set
1499: -- in order to correctly determine whether or not there needs to
1500: -- be a backing entry for the GA.
1501: --Modifies:
1502: -- PO_ENCUMBRANCE_GT
1503: --Locks:
1504: -- None.
1505: --Function:
1506: -- Polishes the data in the encumbrance table.
1570:
1571: -- Clean up the inputs columns to make future calculations easier,
1572: -- and derive other column values needed for calculations.
1573:
1574: UPDATE PO_ENCUMBRANCE_GT ENC
1575: SET
1576:
1577: -- input columns
1578: ENC.unencumbered_amount = NVL(ENC.unencumbered_amount,0)
1609:
1610: -- Fill in the minimum accountable unit and precision for foreign currencies.
1611: -- We probably don't need to do this for the functional currency rows.
1612:
1613: UPDATE PO_ENCUMBRANCE_GT ENC
1614: SET
1615: ( ENC.min_acct_unit_foreign
1616: , ENC.cur_precision_foreign
1617: )
1631:
1632: -- Fill in the sequence_num.
1633: -- Do this for prevent rows as well, for error reporting.
1634:
1635: UPDATE PO_ENCUMBRANCE_GT ENC
1636: SET ENC.sequence_num = PO_ENCUMBRANCE_GT_S.nextval
1637: WHERE ENC.sequence_num IS NULL
1638: ;
1639:
1632: -- Fill in the sequence_num.
1633: -- Do this for prevent rows as well, for error reporting.
1634:
1635: UPDATE PO_ENCUMBRANCE_GT ENC
1636: SET ENC.sequence_num = PO_ENCUMBRANCE_GT_S.nextval
1637: WHERE ENC.sequence_num IS NULL
1638: ;
1639:
1640: l_progress := '190';
1665: IF g_debug_stmt THEN
1666: PO_DEBUG.debug_stmt(l_log_head,l_progress,'updating GA dist_id');
1667: END IF;
1668:
1669: UPDATE PO_ENCUMBRANCE_GT PO_DIST
1670: SET PO_DIST.agreement_dist_id =
1671: ( SELECT GA_DIST.po_distribution_id
1672: FROM PO_DISTRIBUTIONS GA_DIST
1673: WHERE GA_DIST.po_header_id = PO_DIST.from_header_id
1698: IF g_debug_stmt THEN
1699: PO_DEBUG.debug_stmt(l_log_head,l_progress,'updating BPA dist ids');
1700: END IF;
1701:
1702: UPDATE PO_ENCUMBRANCE_GT REL_DIST
1703: SET REL_DIST.agreement_dist_id =
1704: ( SELECT BPA_DIST.po_distribution_id
1705: FROM PO_DISTRIBUTIONS_ALL BPA_DIST
1706: WHERE BPA_DIST.po_header_id = REL_DIST.header_id
1731: PO_DEBUG.debug_stmt(l_log_head,l_progress,'updating backing link');
1732: END IF;
1733:
1734: FORALL i IN 1 .. p_origin_seq_num_tbl.COUNT
1735: UPDATE PO_ENCUMBRANCE_GT BACKING
1736: SET BACKING.origin_sequence_num = p_origin_seq_num_tbl(i)
1737: WHERE BACKING.distribution_id = p_backing_dist_id_tbl(i)
1738: AND BACKING.distribution_type = p_distribution_type
1739: AND BACKING.origin_sequence_num IS NULL
1753: --bug 3568512: do not send Unreserved backing BPA/PPO lines to GL; we
1754: --only keep them in the GTT to maintain the unencumbered_amount column
1755: --Invoice Cancel is excluded b/c it has a different set of conditions
1756: --for backing docs in the filter_backing_distributions code
1757: UPDATE PO_ENCUMBRANCE_GT BACKING
1758: SET BACKING.send_to_gl_flag = 'N'
1759: , BACKING.update_encumbered_amount_flag = 'N'
1760: WHERE BACKING.origin_sequence_num IS NOT NULL --backing doc
1761: AND BACKING.encumbered_flag = 'N'
1780: -- passed in by AP for the main document
1781: IF (p_action IN (g_action_INVOICE_CANCEL, g_action_CR_MEMO_CANCEL)
1782: AND p_main_or_backing = g_MAIN) THEN
1783:
1784: UPDATE PO_ENCUMBRANCE_GT MAINDOC
1785: SET MAINDOC.budget_account_id = p_ap_budget_account_id
1786: WHERE MAINDOC.origin_sequence_num IS NULL;
1787:
1788: END IF;
1864: END IF;
1865:
1866: SELECT DISTS.req_distribution_id
1867: BULK COLLECT INTO l_distribution_id_tbl
1868: FROM PO_ENCUMBRANCE_GT DISTS
1869: WHERE DISTS.req_distribution_id IS NOT NULL
1870: AND DISTS.origin_sequence_num IS NULL
1871: AND DISTS.prevent_encumbrance_flag = 'N'
1872: ;
1881: END IF;
1882:
1883: SELECT DISTS.source_distribution_id
1884: BULK COLLECT INTO l_distribution_id_tbl
1885: FROM PO_ENCUMBRANCE_GT DISTS
1886: WHERE DISTS.source_distribution_id IS NOT NULL
1887: AND DISTS.origin_sequence_num IS NULL
1888: AND DISTS.prevent_encumbrance_flag = 'N'
1889: ;
1898: END IF;
1899:
1900: SELECT DISTS.agreement_dist_id
1901: BULK COLLECT INTO l_distribution_id_tbl
1902: FROM PO_ENCUMBRANCE_GT DISTS
1903: WHERE DISTS.agreement_dist_id IS NOT NULL
1904: AND DISTS.origin_sequence_num IS NULL
1905: AND DISTS.prevent_encumbrance_flag = 'N'
1906: ;
2047: l_backing_req_key
2048: , ENC.sequence_num
2049: , PRD.distribution_id
2050: FROM
2051: PO_ENCUMBRANCE_GT ENC
2052: , PO_REQUISITION_LINES_ALL PRL
2053: , PO_REQ_DISTRIBUTIONS_ALL PRD
2054: WHERE PRD.distribution_id = ENC.req_distribution_id --JOIN
2055: AND PRL.requisition_line_id = PRD.requisition_line_id --JOIN
2194: , x_origin_seq_num_tbl
2195: FROM
2196: PO_DISTRIBUTIONS_ALL POD
2197: , PO_LINE_LOCATIONS_ALL POLL
2198: , PO_ENCUMBRANCE_GT SR_DIST
2199: WHERE POLL.line_location_id = POD.line_location_id --JOIN
2200: AND POD.po_distribution_id = SR_DIST.source_distribution_id --JOIN
2201: AND NVL(POD.prevent_encumbrance_flag,'N') = 'N'
2202: AND NVL(POLL.closed_code,g_clsd_OPEN) <> g_clsd_FINALLY_CLOSED
2233: , x_origin_seq_num_tbl
2234: FROM
2235: PO_DISTRIBUTIONS_ALL POD
2236: , PO_HEADERS_ALL POH
2237: , PO_ENCUMBRANCE_GT REL_DIST
2238: WHERE POH.po_header_id = POD.po_header_id --JOIN
2239: AND POD.po_distribution_id = REL_DIST.agreement_dist_id --JOIN
2240: AND NVL(POD.prevent_encumbrance_flag,'N') = 'N'
2241: AND NVL(POH.closed_code,g_clsd_OPEN) <> g_clsd_FINALLY_CLOSED
2497: --Pre-reqs:
2498: -- The encumbrance table may need to be populated with
2499: -- the appropriate data.
2500: --Modifies:
2501: -- PO_ENCUMBRANCE_GT
2502: -- Submission check tables, online report table
2503: --Locks:
2504: -- PO_HEADERS_ALL
2505: -- PO_RELEASES_ALL
2508: -- Performs the validations necessary in order for an encumbrance
2509: -- action to be taken.
2510: -- Most validation failures will raise an exception from this procedure.
2511: --
2512: -- If PO_ENCUMBRANCE_GT contains ids for this encumbrance action
2513: -- (p_check_only_flag = NO and p_use_enc_gt_flag = YES),
2514: -- the table will be replaced with the data for these ids.
2515: --Parameters:
2516: --IN:
2771:
2772: l_progress := 'a20';
2773: IF g_debug_stmt THEN
2774: PO_DEBUG.debug_stmt(l_log_head,l_progress,'multiple id do action');
2775: PO_DEBUG.debug_table(l_log_head,l_progress,'PO_ENCUMBRANCE_GT'
2776: ,PO_DEBUG.g_all_rows
2777: ,po_tbl_varchar30('distribution_type','doc_level','doc_level_id')
2778: );
2779: END IF;
2787: BULK COLLECT INTO
2788: l_doc_level_tbl
2789: , l_dist_type_tbl
2790: , l_count_tbl
2791: FROM PO_ENCUMBRANCE_GT ENC
2792: GROUP BY ENC.doc_level, ENC.distribution_type
2793: ;
2794:
2795: l_progress := 'a30';
2812: -- Make sure we're not trying to act on the same entity twice.
2813:
2814: SELECT DISTINCT ENC.doc_level_id
2815: BULK COLLECT INTO l_doc_level_id_tbl
2816: FROM PO_ENCUMBRANCE_GT ENC
2817: ;
2818:
2819: l_progress := 'a50';
2820:
2831: -- Then we can proceed as normal.
2832:
2833: -- We've already gathered the ids from the table, so now
2834: -- we should clear it for the normal encumbrance flow.
2835: -- (i.e., DELETE FROM PO_ENCUMBRANCE_GT)
2836:
2837: delete_encumbrance_gt();
2838:
2839: -- Now fill the table up with all of the info.
2973: , g_doc_type_RELEASE, ENC.po_release_id
2974: , ENC.header_id
2975: )
2976: INTO l_doc_id
2977: FROM PO_ENCUMBRANCE_GT ENC
2978: WHERE rownum = 1
2979: ;
2980:
2981: l_progress := '220';
3000: END IF;
3001:
3002: SELECT 'Y'
3003: INTO l_multiple_docs_flag
3004: FROM PO_ENCUMBRANCE_GT ENC
3005: WHERE (ENC.po_release_id <> l_doc_id
3006: OR ENC.po_release_id IS NULL
3007: )
3008: AND rownum = 1;
3017: END IF;
3018:
3019: SELECT 'Y'
3020: INTO l_multiple_docs_flag
3021: FROM PO_ENCUMBRANCE_GT ENC
3022: WHERE (ENC.header_id <> l_doc_id
3023: OR ENC.header_id IS NULL
3024: )
3025: AND rownum = 1;
3753: -------------------------------------------------------------------------------
3754: --Start of Comments
3755: --Name: derive_packet_values
3756: --Pre-reqs:
3757: -- PO_ENCUMBRANCE_GT has been populated with the distribution
3758: -- information for both the original doc and backing docs.
3759: --Modifies:
3760: -- PO_ENCUMBRANCE_GT
3761: --Locks:
3756: --Pre-reqs:
3757: -- PO_ENCUMBRANCE_GT has been populated with the distribution
3758: -- information for both the original doc and backing docs.
3759: --Modifies:
3760: -- PO_ENCUMBRANCE_GT
3761: --Locks:
3762: -- None.
3763: --Function:
3764: -- This procedure updates all of the information required for each
3761: --Locks:
3762: -- None.
3763: --Function:
3764: -- This procedure updates all of the information required for each
3765: -- entry in PO_ENCUMBRANCE_GT that will be sent to GL for an
3766: -- encumbrance action. It prepares all of the columns of
3767: -- PO_ENCUMBRANCE_GT that map to columns of GL_BC_PACKETS.
3768: --Parameters:
3769: --IN:
3763: --Function:
3764: -- This procedure updates all of the information required for each
3765: -- entry in PO_ENCUMBRANCE_GT that will be sent to GL for an
3766: -- encumbrance action. It prepares all of the columns of
3767: -- PO_ENCUMBRANCE_GT that map to columns of GL_BC_PACKETS.
3768: --Parameters:
3769: --IN:
3770: --p_action
3771: -- Specifies the action that is being taken on the main doc.
3912: -------------------------------------------------------------------------------
3913: --Start of Comments
3914: --Name: get_period_info
3915: --Pre-reqs:
3916: -- PO_ENCUMBRANCE_GT has been populated with the date information
3917: -- for both the original doc and backing docs.
3918: --Modifies:
3919: -- PO_ENCUMBRANCE_GT
3920: --Locks:
3915: --Pre-reqs:
3916: -- PO_ENCUMBRANCE_GT has been populated with the date information
3917: -- for both the original doc and backing docs.
3918: --Modifies:
3919: -- PO_ENCUMBRANCE_GT
3920: --Locks:
3921: -- n/a
3922: --Function:
3923: -- This procedure updates the period information in PO_ENCUMBRANCE_GT
3919: -- PO_ENCUMBRANCE_GT
3920: --Locks:
3921: -- n/a
3922: --Function:
3923: -- This procedure updates the period information in PO_ENCUMBRANCE_GT
3924: -- for both the main doc and backing docs.
3925: -- For backing docs, it sets the period information equal to the
3926: -- period information of the main doc.
3927: --Parameters:
4123:
4124: -- By now, the period info has been set for the original
4125: -- document's distributions.
4126: -- Now update the backing distributions.
4127: UPDATE PO_ENCUMBRANCE_GT BACKING
4128: SET (
4129: BACKING.period_name
4130: , BACKING.period_year
4131: , BACKING.period_num
4140: , ORIG.period_num
4141: , ORIG.quarter_num
4142: , ORIG.gl_period_date --bug#5098665
4143: FROM
4144: PO_ENCUMBRANCE_GT ORIG
4145: WHERE
4146: ORIG.sequence_num = BACKING.origin_sequence_num
4147: AND ORIG.origin_sequence_num IS NULL
4148: AND ORIG.distribution_type <> g_dist_type_REQUISITION
4169: , 'PO_PDOI_INVALID_GL_ENC_PER'
4170: );
4171:
4172: -- Mark the missing date lines to not be sent to GL
4173: UPDATE PO_ENCUMBRANCE_GT DISTS
4174: SET DISTS.send_to_gl_flag = 'N' --bug 5413111 gl period failed rows shouldn't be sent to GL
4175: , DISTS.gl_result_code = 'F25' --GL error code for no period found
4176: , DISTS.result_type = g_result_ERROR
4177: , DISTS.result_text = l_period_error_text
4184: , 'PO_ENC_DIST_NOT_PROCESSED'
4185: );
4186:
4187: -- Mark the other lines as failures, since packet is hosed
4188: UPDATE PO_ENCUMBRANCE_GT DISTS
4189: SET DISTS.send_to_gl_flag = 'Y' --bug 3568512: new send_to_gl column
4190: , DISTS.result_text = l_not_processed_msg
4191: , DISTS.result_type = g_result_NOT_PROCESSED
4192: WHERE DISTS.period_name IS NOT NULL;
4233: -------------------------------------------------------------------------------
4234: --Start of Comments
4235: --Name: find_open_period
4236: --Pre-reqs:
4237: -- PO_ENCUMBRANCE_GT has been populated with the date information
4238: -- for both the original doc.
4239: --Modifies:
4240: -- PO_ENCUMBRANCE_GT
4241: -- PO_SESSION_GT
4236: --Pre-reqs:
4237: -- PO_ENCUMBRANCE_GT has been populated with the date information
4238: -- for both the original doc.
4239: --Modifies:
4240: -- PO_ENCUMBRANCE_GT
4241: -- PO_SESSION_GT
4242: --Locks:
4243: -- n/a
4244: --Function:
4241: -- PO_SESSION_GT
4242: --Locks:
4243: -- n/a
4244: --Function:
4245: -- This procedure updates the period information in PO_ENCUMBRANCE_GT
4246: -- for both the main doc, based on parameters which decide whether to
4247: -- use the distribution GL date, override date, roll-forward or
4248: -- roll-backward logic.
4249: --Parameters:
4338:
4339: )
4340: )
4341: )
4342: FROM PO_ENCUMBRANCE_GT DISTS
4343: WHERE
4344: DISTS.origin_sequence_num IS NULL --main doc
4345: AND DISTS.period_name IS NULL
4346: --bug 3568512: use send_to_gl_flag for this filter condition
4578:
4579: --bug#5098665 From now on we would populate the open period start date
4580: --instead of gl_encumbered_date. This is to ensure that the funds get
4581: --reserved/unreserved in the correct period. We would first populate
4582: --po_encumbrance_gt with gl_period_start_date that we derived earlier
4583: --and then this would be passed onto po_bc_distributions.
4584:
4585: UPDATE PO_ENCUMBRANCE_GT DISTS
4586: SET (
4581: --reserved/unreserved in the correct period. We would first populate
4582: --po_encumbrance_gt with gl_period_start_date that we derived earlier
4583: --and then this would be passed onto po_bc_distributions.
4584:
4585: UPDATE PO_ENCUMBRANCE_GT DISTS
4586: SET (
4587: DISTS.period_name
4588: , DISTS.period_year
4589: , DISTS.period_num
4616: l_progress := '080';
4617: --bug#5098665 From now on we would populate the open period start date
4618: --instead of gl_encumbered_date. This is to ensure that the funds get
4619: --reserved/unreserved in the correct period. We would first populate
4620: --po_encumbrance_gt with gl_period_start_date that we derived earlier
4621: --and then this would be passed onto po_bc_distributions.
4622:
4623: UPDATE PO_ENCUMBRANCE_GT DISTS
4624: SET (
4619: --reserved/unreserved in the correct period. We would first populate
4620: --po_encumbrance_gt with gl_period_start_date that we derived earlier
4621: --and then this would be passed onto po_bc_distributions.
4622:
4623: UPDATE PO_ENCUMBRANCE_GT DISTS
4624: SET (
4625: DISTS.period_name
4626: , DISTS.period_year
4627: , DISTS.period_num
4702: -------------------------------------------------------------------------------
4703: --Start of Comments
4704: --Name: get_amounts
4705: --Pre-reqs:
4706: -- The PO_ENCUMBRANCE_GT temp table has been populated with all
4707: -- required information about the main and backing documents; and
4708: -- Encumbrance is on for the doc type of the main doc.
4709: --Modifies:
4710: -- PO_ENCUMBRANCE_GT
4706: -- The PO_ENCUMBRANCE_GT temp table has been populated with all
4707: -- required information about the main and backing documents; and
4708: -- Encumbrance is on for the doc type of the main doc.
4709: --Modifies:
4710: -- PO_ENCUMBRANCE_GT
4711: --Locks:
4712: -- None.
4713: --Function:
4714: -- This procedure calculates the total amount being transacted by
4712: -- None.
4713: --Function:
4714: -- This procedure calculates the total amount being transacted by
4715: -- the encumbrance action and populates this value in the final_amt
4716: -- column for each row in the PO_ENCUMBRANCE_GT table
4717: --Parameters:
4718: --IN:
4719: --p_action
4720: -- Specifies the action that is being taken on the main doc.
4862: -- Requester Change Order is not supported for Complex Work POs.
4863:
4864: SELECT header_id
4865: INTO l_header_id
4866: FROM PO_ENCUMBRANCE_GT
4867: WHERE origin_sequence_num is null --main doc
4868: AND rownum = 1 --just get first record since all should have same ID
4869: ;
4870:
4915:
4916: l_progress := '070';
4917:
4918: IF g_debug_stmt THEN
4919: PO_DEBUG.debug_table(l_log_head,l_progress,'PO_ENCUMBRANCE_GT',PO_DEBUG.g_all_rows);
4920: PO_DEBUG.debug_end(l_log_head);
4921: END IF;
4922:
4923:
4940: -------------------------------------------------------------------------------
4941: --Start of Comments
4942: --Name: get_initial_amounts
4943: --Pre-reqs:
4944: -- The PO_ENCUMBRANCE_GT temp table has been populated with all
4945: -- required information about the main and backing documents; and
4946: -- Encumbrance is on for the doc type of the main doc.
4947: --Modifies:
4948: -- PO_ENCUMBRANCE_GT
4944: -- The PO_ENCUMBRANCE_GT temp table has been populated with all
4945: -- required information about the main and backing documents; and
4946: -- Encumbrance is on for the doc type of the main doc.
4947: --Modifies:
4948: -- PO_ENCUMBRANCE_GT
4949: --Locks:
4950: -- None.
4951: --Function:
4952: -- Performs the basic calculations for both main and backing docs:
5024: -- Now, calculate the intial amount_ordered for both the
5025: -- main and backing documents
5026:
5027: -- First, get the qty_ordered for quantity-based lines
5028: UPDATE PO_ENCUMBRANCE_GT DISTS
5029: SET DISTS.qty_ordered = nvl(DISTS.quantity_ordered, 0)
5030: WHERE DISTS.prevent_encumbrance_flag = 'N'
5031: AND DISTS.amount_based_flag = 'N'
5032: ;
5033:
5034: l_progress := '020';
5035:
5036: -- Now, get amt_ordered for both Service and non-Service lines
5037: UPDATE PO_ENCUMBRANCE_GT DISTS
5038: SET DISTS.amt_ordered =
5039: DECODE(DISTS.distribution_type
5040: -- Agreements
5041: , g_dist_type_AGREEMENT, amt_to_encumber_func
5055: l_progress := '030';
5056:
5057: IF g_debug_stmt THEN
5058: PO_DEBUG.debug_table(
5059: l_log_head, l_progress,'PO_ENCUMBRANCE_GT', PO_DEBUG.g_all_rows,
5060: po_tbl_varchar30( 'amount_based_flag', 'qty_ordered', 'amt_ordered' )
5061: );
5062: END IF;
5063:
5070:
5071: -- Note: the nonrecoverable_tax can be negative due
5072: -- to tax adjustments from Req Split.(bug 3428139, bug 3428600)
5073:
5074: UPDATE PO_ENCUMBRANCE_GT DISTS
5075: SET DISTS.nonrecoverable_tax_rate =
5076: DECODE( NVL(DISTS.amt_ordered,0)
5077:
5078: -- Bug 3410522: If amt_ordered is 0, we can ignore tax.
5087: l_progress := '040';
5088:
5089: IF g_debug_stmt THEN
5090: PO_DEBUG.debug_table(
5091: l_log_head, l_progress,'PO_ENCUMBRANCE_GT', PO_DEBUG.g_all_rows,
5092: po_tbl_varchar30( 'nonrecoverable_tax_rate')
5093: );
5094: PO_DEBUG.debug_end(l_log_head);
5095: END IF;
5114: -------------------------------------------------------------------------------
5115: --Start of Comments
5116: --Name: get_main_doc_amts
5117: --Pre-reqs:
5118: -- The PO_ENCUMBRANCE_GT temp table has been populated with all
5119: -- required information about the main and backing documents; and
5120: -- Encumbrance is on for the doc type of the main doc.
5121: --Modifies:
5122: -- PO_ENCUMBRANCE_GT
5118: -- The PO_ENCUMBRANCE_GT temp table has been populated with all
5119: -- required information about the main and backing documents; and
5120: -- Encumbrance is on for the doc type of the main doc.
5121: --Modifies:
5122: -- PO_ENCUMBRANCE_GT
5123: --Locks:
5124: -- None.
5125: --Function:
5126: -- Calculates the amount of encumbrance from the main document that
5180: IF (p_doc_type = g_doc_type_REQUISITION) THEN
5181: -- main doc is a Req
5182: l_progress := '020';
5183:
5184: UPDATE PO_ENCUMBRANCE_GT DISTS
5185: SET DISTS.amt_closed =
5186: DISTS.amt_ordered *
5187: DECODE( DISTS.amount_based_flag
5188:
5204: l_progress := '030';
5205:
5206: -- The closed amount for BPAs is the amount of encumbrance already
5207: -- relieved against the BPA
5208: UPDATE PO_ENCUMBRANCE_GT DISTS
5209: SET DISTS.amt_closed = DISTS.unencumbered_amount
5210: WHERE DISTS.prevent_encumbrance_flag = 'N'
5211: ;
5212:
5219: IF (p_doc_subtype = g_doc_subtype_PLANNED) THEN
5220: l_progress := '040';
5221:
5222: -- For PPO, qty closed = qty unencumbered by SR's against the PPO
5223: UPDATE PO_ENCUMBRANCE_GT DISTS
5224: SET DISTS.qty_closed = DISTS.unencumbered_quantity
5225: WHERE DISTS.origin_sequence_num IS NULL -- main doc
5226: AND DISTS.prevent_encumbrance_flag = 'N'
5227: AND DISTS.amount_based_flag = 'N'
5234:
5235: -- in this case, qty closed is the amount that is already
5236: -- moved to an actual (and not considered for future encumbrance
5237: -- actions)
5238: UPDATE PO_ENCUMBRANCE_GT DISTS
5239: SET DISTS.qty_closed =
5240: DECODE( DISTS.accrue_on_receipt_flag
5241:
5242: -- Online Accruals
5272: l_progress := '060';
5273:
5274: -- Now that we have the qty_closed for the qty-based main doc
5275: -- lines, we can get the amt_closed for all main doc lines.
5276: UPDATE PO_ENCUMBRANCE_GT DISTS
5277: SET DISTS.amt_closed =
5278: DECODE( DISTS.amount_based_flag
5279:
5280: -- quantity-based: use qty_closed calc from above
5335: -- will be used for backing doc calculations.
5336: -- qty_closed must be updated before amt_closed,
5337: -- since it is used in the calculation for amt_closed.
5338:
5339: UPDATE PO_ENCUMBRANCE_GT DISTS
5340: SET DISTS.qty_closed =
5341: DECODE( p_action
5342: , g_action_INVOICE_CANCEL,
5343: DISTS.quantity_billed + p_ap_cancelled_qty
5354:
5355: -- Bug 3480949: Use p_ap_reinstated_enc_amt instead of
5356: -- deleted variable l_ap_amt_billed_change
5357:
5358: UPDATE PO_ENCUMBRANCE_GT DISTS
5359: SET DISTS.amt_closed =
5360: DECODE( DISTS.amount_based_flag
5361:
5362: , 'N', DISTS.qty_closed * DISTS.price
5381: PO_DEBUG.debug_stmt(l_log_head,l_progress,
5382: 'Calculated amount_closed for main document'
5383: );
5384: PO_DEBUG.debug_table(
5385: l_log_head, l_progress,'PO_ENCUMBRANCE_GT', PO_DEBUG.g_all_rows,
5386: po_tbl_varchar30( 'amount_based_flag', 'qty_closed', 'amt_closed')
5387: );
5388: PO_DEBUG.debug_end(l_log_head);
5389: END IF;
5409: -------------------------------------------------------------------------------
5410: --Start of Comments
5411: --Name: get_backing_doc_amts
5412: --Pre-reqs:
5413: -- The PO_ENCUMBRANCE_GT temp table has been populated with all
5414: -- required information about the main and backing documents; and
5415: -- Encumbrance is on for the doc type of the main doc.
5416: --Modifies:
5417: -- PO_ENCUMBRANCE_GT
5413: -- The PO_ENCUMBRANCE_GT temp table has been populated with all
5414: -- required information about the main and backing documents; and
5415: -- Encumbrance is on for the doc type of the main doc.
5416: --Modifies:
5417: -- PO_ENCUMBRANCE_GT
5418: --Locks:
5419: -- None.
5420: --Function:
5421: -- Calculates the amount of encumbrance from the backing Req/PPO that
5515:
5516:
5517: -- Get unit of measure conversion rate, if backing Req UOM is
5518: -- different from main doc UOM
5519: UPDATE PO_ENCUMBRANCE_GT REQ_DISTS
5520: SET REQ_DISTS.uom_conversion_rate =
5521: (SELECT PO_UOM_S.PO_UOM_CONVERT_P( PO_DISTS.unit_meas_lookup_code
5522: , REQ_DISTS.unit_meas_lookup_code
5523: , REQ_DISTS.item_id
5521: (SELECT PO_UOM_S.PO_UOM_CONVERT_P( PO_DISTS.unit_meas_lookup_code
5522: , REQ_DISTS.unit_meas_lookup_code
5523: , REQ_DISTS.item_id
5524: )
5525: FROM PO_ENCUMBRANCE_GT PO_DISTS
5526: WHERE REQ_DISTS.origin_sequence_num = PO_DISTS.sequence_num
5527: AND PO_DISTS.distribution_type <> g_dist_type_REQUISITION
5528: AND REQ_DISTS.unit_meas_lookup_code <>
5529: PO_DISTS.unit_meas_lookup_code
5536: l_progress := '030';
5537:
5538: IF g_debug_stmt THEN
5539: PO_DEBUG.debug_stmt(l_log_head,l_progress,'did UoM conversion');
5540: PO_DEBUG.debug_table(l_log_head,l_progress,'PO_ENCUMBRANCE_GT',PO_DEBUG.g_all_rows
5541: ,po_tbl_varchar30('prevent_encumbrance_flag','amount_based_flag'
5542: ,'distribution_type','uom_conversion_rate','unit_meas_lookup_code'
5543: ,'item_id','sequence_num','origin_sequence_num'
5544: )
5549: -- Check to see if any of the rows returned error from the function
5550: BEGIN
5551: SELECT 'Y'
5552: INTO l_uom_conversion_error
5553: FROM PO_ENCUMBRANCE_GT REQ_DISTS
5554: WHERE REQ_DISTS.distribution_type = g_dist_type_REQUISITION
5555: AND REQ_DISTS.amount_based_flag = 'N'
5556: AND REQ_DISTS.prevent_encumbrance_flag = 'N'
5557: AND REQ_DISTS.uom_conversion_rate = -999
5597: -- Cancel w/ recreate demand uses new Req line (so not this calc)
5598: -- and backing Req for Invoice Cancel is calculated separately
5599: l_progress := '060';
5600:
5601: UPDATE PO_ENCUMBRANCE_GT REQ_DISTS
5602: SET REQ_DISTS.amt_closed =
5603: (SELECT
5604: DECODE( REQ_DISTS.amount_based_flag
5605:
5610:
5611: -- amount based
5612: , PO_DISTS.amt_closed_func --bug 3435571
5613: )
5614: FROM PO_ENCUMBRANCE_GT PO_DISTS
5615: WHERE REQ_DISTS.origin_sequence_num = PO_DISTS.sequence_num
5616: AND PO_DISTS.distribution_type <> g_dist_type_REQUISITION
5617: )
5618: WHERE REQ_DISTS.distribution_type = g_dist_type_REQUISITION
5622: ELSE
5623: -- p_action is Invoice/Credit Memo Cancel
5624: l_progress := '070';
5625:
5626: UPDATE PO_ENCUMBRANCE_GT REQ_DISTS
5627: SET REQ_DISTS.qty_open =
5628: (SELECT
5629: DECODE( greatest(0, REQ_DISTS.qty_ordered -
5630: (PO_DISTS.qty_closed *
5643: --put entire cancelled qty on Req
5644: , (p_ap_cancelled_qty
5645: * NVL(REQ_DISTS.uom_conversion_rate,1))
5646: )
5647: FROM PO_ENCUMBRANCE_GT PO_DISTS
5648: WHERE REQ_DISTS.origin_sequence_num = PO_DISTS.sequence_num
5649: AND PO_DISTS.distribution_type <> g_dist_type_REQUISITION
5650: ) --end select
5651: WHERE REQ_DISTS.distribution_type = g_dist_type_REQUISITION
5654: ;
5655:
5656: l_progress := '080';
5657:
5658: UPDATE PO_ENCUMBRANCE_GT REQ_DISTS
5659: SET REQ_DISTS.amt_open =
5660: (SELECT
5661: DECODE( REQ_DISTS.amount_based_flag
5662:
5677: --we will add tax to and round this amt
5678: , p_ap_amt_billed_change
5679: )
5680: )
5681: FROM PO_ENCUMBRANCE_GT PO_DISTS
5682: WHERE REQ_DISTS.origin_sequence_num = PO_DISTS.sequence_num
5683: AND PO_DISTS.distribution_type <> g_dist_type_REQUISITION
5684: )
5685: WHERE REQ_DISTS.distribution_type = g_dist_type_REQUISITION
5696: IF g_debug_stmt THEN
5697: PO_DEBUG.debug_stmt(l_log_head,l_progress,
5698: 'Calculated amount_closed for backing Reqs'
5699: );
5700: PO_DEBUG.debug_table(l_log_head,l_progress,'PO_ENCUMBRANCE_GT',PO_DEBUG.g_all_rows
5701: ,po_tbl_varchar30('prevent_encumbrance_flag','amount_based_flag'
5702: ,'qty_open','qty_ordered','qty_closed','uom_conversion_rate'
5703: ,'quantity_billed','distribution_type','sequence_num','origin_sequence_num'
5704: ,'amt_open','price','amt_ordered','amt_closed','amount_billed'
5733: -- Note: FC, Undo FC, Return do not have backing PPO scenarios,
5734: -- and backing PPO qty for Invoice Cancel is calculated separately
5735: l_progress := '110';
5736:
5737: UPDATE PO_ENCUMBRANCE_GT PPO_DISTS
5738: SET PPO_DISTS.qty_open =
5739: (SELECT SR_DISTS.qty_ordered - SR_DISTS.qty_closed
5740: FROM PO_ENCUMBRANCE_GT SR_DISTS
5741: WHERE SR_DISTS.sequence_num = PPO_DISTS.origin_sequence_num
5736:
5737: UPDATE PO_ENCUMBRANCE_GT PPO_DISTS
5738: SET PPO_DISTS.qty_open =
5739: (SELECT SR_DISTS.qty_ordered - SR_DISTS.qty_closed
5740: FROM PO_ENCUMBRANCE_GT SR_DISTS
5741: WHERE SR_DISTS.sequence_num = PPO_DISTS.origin_sequence_num
5742: )
5743: WHERE PPO_DISTS.origin_sequence_num IS NOT NULL
5744: AND PPO_DISTS.prevent_encumbrance_flag = 'N'
5748:
5749: -- p_action is Invoice/Credit Memo Cancel
5750: l_progress := '120';
5751:
5752: UPDATE PO_ENCUMBRANCE_GT PPO_DISTS
5753: SET PPO_DISTS.qty_open =
5754: (SELECT
5755: DECODE( greatest(0, SR_DISTS.qty_ordered -
5756: SR_DISTS.qty_closed
5770:
5771: --put entire cancelled qty on PPO
5772: , p_ap_cancelled_qty
5773: )
5774: FROM PO_ENCUMBRANCE_GT SR_DISTS
5775: WHERE PPO_DISTS.origin_sequence_num = SR_DISTS.sequence_num
5776: )
5777: WHERE PPO_DISTS.origin_sequence_num IS NOT NULL
5778: AND PPO_DISTS.prevent_encumbrance_flag = 'N'
5783: IF g_debug_stmt THEN
5784: PO_DEBUG.debug_stmt(l_log_head,l_progress,
5785: 'Updated qty_open for backing PPO'
5786: );
5787: PO_DEBUG.debug_table(l_log_head,l_progress,'PO_ENCUMBRANCE_GT',PO_DEBUG.g_all_rows
5788: ,po_tbl_varchar30('prevent_encumbrance_flag'
5789: ,'qty_open','qty_ordered','qty_closed','quantity_billed'
5790: ,'distribution_type','sequence_num','origin_sequence_num')
5791: );
5818: -------------------------------------------------------------------------------
5819: --Start of Comments
5820: --Name: get_final_amounts
5821: --Pre-reqs:
5822: -- The PO_ENCUMBRANCE_GT temp table has been populated with all
5823: -- required information about the main and backing documents; and
5824: -- Encumbrance is on for the doc type of the main doc.
5825: --Modifies:
5826: -- PO_ENCUMBRANCE_GT
5822: -- The PO_ENCUMBRANCE_GT temp table has been populated with all
5823: -- required information about the main and backing documents; and
5824: -- Encumbrance is on for the doc type of the main doc.
5825: --Modifies:
5826: -- PO_ENCUMBRANCE_GT
5827: --Locks:
5828: -- None.
5829: --Function:
5830: -- Based on the initial encumbrance amounts, and any amounts that are already
5901: IF (p_doc_subtype = g_doc_subtype_SCHEDULED) THEN
5902:
5903: l_progress := '010';
5904:
5905: UPDATE PO_ENCUMBRANCE_GT PPO_DISTS
5906: SET PPO_DISTS.amt_open =
5907: PPO_DISTS.qty_open * PPO_DISTS.price
5908: -- no Services lines on PPOs/SRs
5909: WHERE PPO_DISTS.origin_sequence_num IS NOT NULL --backing document
5919: -- the difference in the original amount of encumbrance and the
5920: -- amount of encumbrance already moved off to actuals.
5921: -- The greatest(0, <>) adjusts for over-delivered/billed case
5922:
5923: UPDATE PO_ENCUMBRANCE_GT DISTS
5924: SET DISTS.amt_open =
5925: DECODE( DISTS.amt_open
5926:
5927: -- if NULL, then it needs to be calculated still
5954: --you cannot cancel quantity/amount on the BPA itself. You can do
5955: --it only on releases. For BPA's the unencumbered amount accounts
5956: --for cancelled quantity of the relese as well.
5957:
5958: UPDATE PO_ENCUMBRANCE_GT DISTS
5959: SET DISTS.amt_open =
5960: GREATEST( 0,
5961: DISTS.amt_open -
5962: DECODE( DISTS.amount_based_flag
5986: -- amt_open to p_ap_reinstated_enc_amt, so that we add tax below.
5987:
5988: l_progress := '055';
5989:
5990: UPDATE PO_ENCUMBRANCE_GT DISTS
5991: SET DISTS.amt_open = p_ap_reinstated_enc_amt
5992: WHERE DISTS.prevent_encumbrance_flag = 'N'
5993: AND DISTS.origin_sequence_num IS NULL -- main doc
5994: ;
6002: IF g_debug_stmt THEN
6003: PO_DEBUG.debug_stmt(l_log_head,l_progress,
6004: 'Updated amt_open for all lines'
6005: );
6006: PO_DEBUG.debug_table(l_log_head,l_progress,'PO_ENCUMBRANCE_GT',PO_DEBUG.g_all_rows
6007: ,po_tbl_varchar30('prevent_encumbrance_flag' ,'amt_open',
6008: 'qty_ordered','qty_closed','quantity_billed', 'quantity_cancelled'
6009: ,'distribution_type','sequence_num','origin_sequence_num')
6010: );
6011: END IF;
6012:
6013:
6014: -- Before we do any rounding, we need to add in the tax (pro-rated)
6015: UPDATE PO_ENCUMBRANCE_GT DISTS
6016: SET DISTS.pre_round_amt = (DISTS.amt_open * DISTS.nonrecoverable_tax_rate)
6017: WHERE DISTS.prevent_encumbrance_flag = 'N'
6018: AND DISTS.amt_open IS NOT NULL
6019: ;
6022: IF g_debug_stmt THEN
6023: PO_DEBUG.debug_stmt(l_log_head,l_progress,
6024: 'Updated pre_round_amt for all lines'
6025: );
6026: PO_DEBUG.debug_table(l_log_head,l_progress,'PO_ENCUMBRANCE_GT',PO_DEBUG.g_all_rows
6027: ,po_tbl_varchar30('prevent_encumbrance_flag' ,'pre_round_amt')
6028: );
6029: END IF;
6030:
6110: -------------------------------------------------------------------------------
6111: --Start of Comments
6112: --Name: round_and_convert_amounts
6113: --Pre-reqs:
6114: -- The PO_ENCUMBRANCE_GT temp table has been populated with all
6115: -- required information about the main and backing documents; and
6116: -- Encumbrance is on for the doc type of the main doc.
6117: --Modifies:
6118: -- PO_ENCUMBRANCE_GT
6114: -- The PO_ENCUMBRANCE_GT temp table has been populated with all
6115: -- required information about the main and backing documents; and
6116: -- Encumbrance is on for the doc type of the main doc.
6117: --Modifies:
6118: -- PO_ENCUMBRANCE_GT
6119: --Locks:
6120: -- None.
6121: --Function:
6122: -- This procedure is a wrapper around the currency rounding procedure
6140: --p_cur_precision_func
6141: -- The precision (defined in FND_CURRENCIES) of the functional
6142: -- currency for the current Set of Books
6143: --p_column_to_use
6144: -- Specifies with column of PO_ENCUMBRANCE_GT to convert and round
6145: -- Valid Values:
6146: -- g_column_AMOUNT_TO_ENCUMBER,
6147: -- g_column_AMOUNT_CLOSED
6148: -- g_column_PRE_ROUND_AMT
6237: , l_cur_precision_func_tbl
6238: , l_min_acct_unit_func_tbl
6239: , l_round_only_flag_tbl --bug 3568671
6240: , l_origin_sequence_num_tbl -- bug 3480949
6241: FROM PO_ENCUMBRANCE_GT DISTS
6242: WHERE DISTS.prevent_encumbrance_flag = 'N'
6243: ORDER BY DISTS.sequence_num
6244: ;
6245:
6296: -- so that if we do not update those columns which do not
6297: -- correspond to the value of p_column_to_use
6298:
6299: FORALL i IN 1 .. l_sequence_num_tbl.COUNT
6300: UPDATE PO_ENCUMBRANCE_GT DISTS
6301: SET
6302: amt_to_encumber_func =
6303: DECODE( p_column_to_use
6304: , g_column_AMOUNT_TO_ENCUMBER, l_amount_result_tbl(i)
6342: -------------------------------------------------------------------------------
6343: --Start of Comments
6344: --Name: check_backing_pa_amounts
6345: --Pre-reqs:
6346: -- The final_amt column of PO_ENCUMBRANCE_GT has
6347: -- been populated correctly with the rounded functional amount for
6348: -- each of the distributions with backing agreements
6349: -- (PO against GA, Release against BPA)
6350: -- and the backing agreement distributions have also been loaded into
6347: -- been populated correctly with the rounded functional amount for
6348: -- each of the distributions with backing agreements
6349: -- (PO against GA, Release against BPA)
6350: -- and the backing agreement distributions have also been loaded into
6351: -- PO_ENCUMBRANCE_GT.
6352: -- It only makes sense to call this if both PO and Req encumbrance are on
6353: -- (i.e., blanket encumbrance is enabled).
6354: --Modifies:
6355: -- PO_ENCUMBRANCE_GT
6351: -- PO_ENCUMBRANCE_GT.
6352: -- It only makes sense to call this if both PO and Req encumbrance are on
6353: -- (i.e., blanket encumbrance is enabled).
6354: --Modifies:
6355: -- PO_ENCUMBRANCE_GT
6356: --Locks:
6357: -- n/a
6358: --Function:
6359: -- This procedure updates the final_amt column of backing agreement
6356: --Locks:
6357: -- n/a
6358: --Function:
6359: -- This procedure updates the final_amt column of backing agreement
6360: -- rows in PO_ENCUMBRANCE_GT, based on the final_amt for each
6361: -- of the PO/Release distributions.
6362: -- It prohibits the sum of the amounts against the backing agreement
6363: -- from exceeding the limits of the agreement's encumbrance, based on
6364: -- the action being taken.
6435: , l_amt_to_encumber_func_tbl
6436: , l_unencumbered_amount_tbl
6437: , l_amount_tbl
6438: FROM
6439: PO_ENCUMBRANCE_GT PA_DISTS
6440: , PO_ENCUMBRANCE_GT PO_DISTS
6441: WHERE PA_DISTS.origin_sequence_num = PO_DISTS.sequence_num
6442: AND PA_DISTS.distribution_id = PO_DISTS.agreement_dist_id
6443: AND PO_DISTS.prevent_encumbrance_flag = 'N'
6436: , l_unencumbered_amount_tbl
6437: , l_amount_tbl
6438: FROM
6439: PO_ENCUMBRANCE_GT PA_DISTS
6440: , PO_ENCUMBRANCE_GT PO_DISTS
6441: WHERE PA_DISTS.origin_sequence_num = PO_DISTS.sequence_num
6442: AND PA_DISTS.distribution_id = PO_DISTS.agreement_dist_id
6443: AND PO_DISTS.prevent_encumbrance_flag = 'N'
6444: AND PA_DISTS.distribution_type = g_dist_type_AGREEMENT
6545: -- Then, put the updated amounts back into the main GTT
6546: --IF (l_update_enc_gt_flag = 'Y') THEN
6547:
6548: FORALL i IN 1 .. l_amount_tbl.COUNT
6549: UPDATE PO_ENCUMBRANCE_GT PA_DISTS
6550: SET PA_DISTS.final_amt = l_amount_tbl(i)
6551: WHERE
6552: PA_DISTS.distribution_id = l_pa_dist_id_tbl(i)
6553: AND PA_DISTS.sequence_num = l_pa_sequence_num_tbl(i)
6595: -------------------------------------------------------------------------------
6596: --Start of Comments
6597: --Name: correct_backing_pa_amounts
6598: --Pre-reqs:
6599: -- PO_ENCUMBRANCE_GT is populated correctly with information about all
6600: -- the main doc and backing PA distributions
6601: --Modifies:
6602: -- PO_ENCUMBRANCE_GT
6603: --Locks:
6598: --Pre-reqs:
6599: -- PO_ENCUMBRANCE_GT is populated correctly with information about all
6600: -- the main doc and backing PA distributions
6601: --Modifies:
6602: -- PO_ENCUMBRANCE_GT
6603: --Locks:
6604: -- n/a
6605: --Function:
6606: -- This procedure updates the final_amt column of backing agreement
6603: --Locks:
6604: -- n/a
6605: --Function:
6606: -- This procedure updates the final_amt column of backing agreement
6607: -- rows in PO_ENCUMBRANCE_GT, based on the final_amt for each
6608: -- of the PO/Release distributions.
6609: -- It prohibits the sum of the amounts against the backing agreement
6610: -- from exceeding the limits of the agreement's encumbrance, based on
6611: -- the action being taken.
6626: --p_unencumbered_amt
6627: -- The unencumbered amount for the current PA, in functional currency
6628: --p_pa_sequence_num_tbl
6629: -- A colletion of sequence_nums corresponding to the sequence_nums in
6630: -- the PO_ENCUMBRANCE_GT of each backing PA row
6631: --p_pa_multiplier_tbl
6632: -- A collection where each element represents whether the corresponding
6633: -- element of the amount tbl is a DR or CR
6634: --IN OUT:
6633: -- element of the amount tbl is a DR or CR
6634: --IN OUT:
6635: --x_pa_amount_tbl
6636: -- A collection where each element represent the final_amt calculated
6637: -- in the PO_ENCUMBRANCE_GT table, for a given backing PA entry
6638: --Testing:
6639: --
6640: --End of Comments
6641: -------------------------------------------------------------------------------
6764: -------------------------------------------------------------------------------
6765: --Start of Comments
6766: --Name: set_complex_work_req_amounts
6767: --Pre-reqs:
6768: -- The final_amt column of PO_ENCUMBRANCE_GT has
6769: -- been populated correctly with the rounded functional amount for
6770: -- each of the Complex Work PO distributions
6771: --Modifies:
6772: -- PO_ENCUMBRANCE_GT
6768: -- The final_amt column of PO_ENCUMBRANCE_GT has
6769: -- been populated correctly with the rounded functional amount for
6770: -- each of the Complex Work PO distributions
6771: --Modifies:
6772: -- PO_ENCUMBRANCE_GT
6773: --Locks:
6774: -- n/a
6775: --Function:
6776: -- This procedure updates the final_amt column of backing Req rows in
6773: --Locks:
6774: -- n/a
6775: --Function:
6776: -- This procedure updates the final_amt column of backing Req rows in
6777: -- PO_ENCUMBRANCE_GT, based on the final_amt for each of the main doc
6778: -- (Complex Work PO doc) distributions
6779: -- It prohibits the sum of the amounts against the backing Requisition
6780: -- from exceeding the limits of the Requisition's encumbrance, if funds
6781: -- are being returned to the Requisition. If funds are being liquidated
6825: -- exceed this amount.
6826:
6827: -- First, set the backing Req dist final amount equal to the corresponding
6828: -- main doc final amount
6829: UPDATE PO_ENCUMBRANCE_GT BACKING_REQ
6830: SET BACKING_REQ.final_amt =
6831: (SELECT MAIN_DOC.final_amt
6832: FROM PO_ENCUMBRANCE_GT MAIN_DOC
6833: WHERE MAIN_DOC.sequence_num = BACKING_REQ.origin_sequence_num
6828: -- main doc final amount
6829: UPDATE PO_ENCUMBRANCE_GT BACKING_REQ
6830: SET BACKING_REQ.final_amt =
6831: (SELECT MAIN_DOC.final_amt
6832: FROM PO_ENCUMBRANCE_GT MAIN_DOC
6833: WHERE MAIN_DOC.sequence_num = BACKING_REQ.origin_sequence_num
6834: AND MAIN_DOC.origin_sequence_num IS NULL)
6835: WHERE BACKING_REQ.origin_sequence_num IS NOT NULL
6836: AND BACKING_REQ.distribution_type = g_dist_type_REQUISITION
6836: AND BACKING_REQ.distribution_type = g_dist_type_REQUISITION
6837: AND BACKING_REQ.prevent_encumbrance_flag = 'N'
6838: ;
6839: IF g_debug_stmt THEN
6840: PO_DEBUG.debug_table(l_log_head,l_progress,'PO_ENCUMBRANCE_GT',PO_DEBUG.g_all_rows,NULL,'PO');
6841: END IF;
6842: l_progress := '010';
6843:
6844: -- Next, extract the l_max_total_tbl for each distinct Req distribution: it
6857: BULK COLLECT INTO
6858: l_req_dist_id_tbl
6859: , l_max_total_tbl
6860: , l_req_dist_gtt_total_tbl
6861: FROM PO_ENCUMBRANCE_GT BACKING_REQ
6862: WHERE BACKING_REQ.origin_sequence_num IS NOT NULL
6863: AND BACKING_REQ.distribution_type = g_dist_type_REQUISITION
6864: AND BACKING_REQ.prevent_encumbrance_flag = 'N'
6865: GROUP BY distribution_id
6925: -------------------------------------------------------------------------------
6926: --Start of Comments
6927: --Name: correct_backing_req_amounts
6928: --Pre-reqs:
6929: -- PO_ENCUMBRANCE_GT is populated correctly with all information about all
6930: -- the main doc and backing Req distributions
6931: --Modifies:
6932: -- PO_ENCUMBRANCE_GT
6933: --Locks:
6928: --Pre-reqs:
6929: -- PO_ENCUMBRANCE_GT is populated correctly with all information about all
6930: -- the main doc and backing Req distributions
6931: --Modifies:
6932: -- PO_ENCUMBRANCE_GT
6933: --Locks:
6934: -- n/a
6935: --Function:
6936: -- This procedure adjusts the final_amt column of backing Req rows in
6933: --Locks:
6934: -- n/a
6935: --Function:
6936: -- This procedure adjusts the final_amt column of backing Req rows in
6937: -- PO_ENCUMBRANCE_GT for the Complex Work case.
6938: -- It prohibits the sum of the amounts against the backing Requisition
6939: -- from exceeding the limits of the Requisition's encumbrance, if funds
6940: -- are being returned to the Requisition. If funds are being liquidated
6941: -- from the Requisition, it ensures that no hanging balances are left on
6957: --p_unencumbered_amt
6958: -- The unencumbered amount for the current PA, in functional currency
6959: --p_pa_sequence_num_tbl
6960: -- A colletion of sequence_nums corresponding to the sequence_nums in
6961: -- the PO_ENCUMBRANCE_GT of each backing PA row
6962: --p_pa_multiplier_tbl
6963: -- A collection where each element represents whether the corresponding
6964: -- element of the amount tbl is a DR or CR
6965: --IN OUT:
6964: -- element of the amount tbl is a DR or CR
6965: --IN OUT:
6966: --x_pa_amount_tbl
6967: -- A collection where each element represent the final_amt calculated
6968: -- in the PO_ENCUMBRANCE_GT table, for a given backing PA entry
6969: --Testing:
6970: --
6971: --End of Comments
6972: -------------------------------------------------------------------------------
7003: , final_amt
7004: BULK COLLECT INTO
7005: l_sequence_num_tbl
7006: , l_gtt_amount_tbl
7007: FROM PO_ENCUMBRANCE_GT BACKING_REQ
7008: WHERE BACKING_REQ.origin_sequence_num IS NOT NULL
7009: AND BACKING_REQ.distribution_type = g_dist_type_REQUISITION
7010: AND BACKING_REQ.prevent_encumbrance_flag = 'N'
7011: AND BACKING_REQ.distribution_id = p_req_dist_id
7067: l_progress := '040';
7068:
7069: --Update the GTT with the adjusted amounts
7070: FORALL i IN l_start_row..l_end_row
7071: UPDATE PO_ENCUMBRANCE_GT BACKING_REQ
7072: SET BACKING_REQ.final_amt = l_gtt_amount_tbl(i)
7073: WHERE BACKING_REQ.sequence_num = l_sequence_num_tbl(i)
7074: ;
7075:
7092: -------------------------------------------------------------------------------
7093: --Start of Comments
7094: --Name: get_gl_references
7095: --Pre-reqs:
7096: -- PO_ENCUMBRANCE_GT has been populated with the information for
7097: -- each of the distributions that will have an entry in GL_BC_PACKETS.
7098: --Modifies:
7099: -- PO_ENCUMBRANCE_GT
7100: --Locks:
7095: --Pre-reqs:
7096: -- PO_ENCUMBRANCE_GT has been populated with the information for
7097: -- each of the distributions that will have an entry in GL_BC_PACKETS.
7098: --Modifies:
7099: -- PO_ENCUMBRANCE_GT
7100: --Locks:
7101: -- n/a
7102: --Function:
7103: -- This procedure updates the references and other non-complex columns
7100: --Locks:
7101: -- n/a
7102: --Function:
7103: -- This procedure updates the references and other non-complex columns
7104: -- in PO_ENCUMBRANCE_GT that map to columns of GL_BC_PACKETS.
7105: --Parameters:
7106: --IN:
7107: --p_action
7108: -- Specifies the action that is being taken on the main doc.
7137:
7138: l_cbc_action VARCHAR2(30) := NULL;
7139: l_cbc_line_description PO_LOOKUP_CODES.description%TYPE := NULL;
7140:
7141: l_source_doc_reference PO_ENCUMBRANCE_GT.reference10%TYPE;
7142:
7143: /* Start Bug 3292870 */
7144:
7145: TYPE g_rowid_char_tbl_type IS TABLE OF VARCHAR2(18);
7165: * it compatible with an 8i db.
7166: */
7167:
7168:
7169: UPDATE PO_ENCUMBRANCE_GT ALL_DISTS
7170: SET
7171: ALL_DISTS.je_category_name =
7172: DECODE( ALL_DISTS.distribution_type
7173: , g_dist_type_REQUISITION, g_je_category_Requisitions
7226:
7227:
7228: l_progress := '050';
7229:
7230: UPDATE PO_ENCUMBRANCE_GT ALL_DISTS
7231: SET ALL_DISTS.reference5 = ALL_DISTS.reference_num
7232: WHERE ALL_DISTS.send_to_gl_flag = 'Y' --bug 3568512: use new column
7233: and ALL_DISTS.distribution_type = g_dist_type_REQUISITION
7234: ;
7234: ;
7235:
7236: l_progress := '060';
7237:
7238: UPDATE PO_ENCUMBRANCE_GT ALL_DISTS
7239: SET ALL_DISTS.reference5 =
7240: ( SELECT PPO_DISTS.segment1
7241: FROM PO_ENCUMBRANCE_GT PPO_DISTS
7242: WHERE PPO_DISTS.origin_sequence_num
7237:
7238: UPDATE PO_ENCUMBRANCE_GT ALL_DISTS
7239: SET ALL_DISTS.reference5 =
7240: ( SELECT PPO_DISTS.segment1
7241: FROM PO_ENCUMBRANCE_GT PPO_DISTS
7242: WHERE PPO_DISTS.origin_sequence_num
7243: = ALL_DISTS.sequence_num
7244: AND PPO_DISTS.distribution_id
7245: = ALL_DISTS.source_distribution_id
7249: ;
7250:
7251: l_progress := '070';
7252:
7253: UPDATE PO_ENCUMBRANCE_GT ALL_DISTS
7254: SET ALL_DISTS.reference5 =
7255: ( SELECT PA_DISTS.segment1
7256: FROM PO_ENCUMBRANCE_GT PA_DISTS
7257: WHERE PA_DISTS.origin_sequence_num =
7252:
7253: UPDATE PO_ENCUMBRANCE_GT ALL_DISTS
7254: SET ALL_DISTS.reference5 =
7255: ( SELECT PA_DISTS.segment1
7256: FROM PO_ENCUMBRANCE_GT PA_DISTS
7257: WHERE PA_DISTS.origin_sequence_num =
7258: ALL_DISTS.sequence_num
7259: AND PA_DISTS.distribution_id =
7260: ALL_DISTS.agreement_dist_id
7266:
7267: l_progress := '080';
7268:
7269: FORALL i IN 1..l_rowid_char_tbl.COUNT
7270: UPDATE PO_ENCUMBRANCE_GT ALL_DISTS
7271: SET ALL_DISTS.reference5 =
7272: ( SELECT REQ_DISTS.segment1
7273: FROM PO_ENCUMBRANCE_GT REQ_DISTS
7274: WHERE REQ_DISTS.origin_sequence_num =
7269: FORALL i IN 1..l_rowid_char_tbl.COUNT
7270: UPDATE PO_ENCUMBRANCE_GT ALL_DISTS
7271: SET ALL_DISTS.reference5 =
7272: ( SELECT REQ_DISTS.segment1
7273: FROM PO_ENCUMBRANCE_GT REQ_DISTS
7274: WHERE REQ_DISTS.origin_sequence_num =
7275: ALL_DISTS.sequence_num
7276: AND REQ_DISTS.distribution_type =
7277: g_dist_type_REQUISITION
7291: -- Backing Reqs/PPOs/BPAs/GAs point to the {PO|Rel}/SR/BR/StdPO's po_header_id.
7292: -- During an invoice activity, the main doc (StdPO/BR/SR)
7293: -- points to the invoice_id.
7294:
7295: UPDATE PO_ENCUMBRANCE_GT BACKING
7296: SET
7297: BACKING.reference6 = g_reference6_SRCDOC
7298: , BACKING.reference10 =
7299: (
7297: BACKING.reference6 = g_reference6_SRCDOC
7298: , BACKING.reference10 =
7299: (
7300: SELECT TO_CHAR(MAIN.header_id)
7301: FROM PO_ENCUMBRANCE_GT MAIN
7302: WHERE MAIN.sequence_num = BACKING.origin_sequence_num
7303: )
7304: WHERE BACKING.origin_sequence_num IS NOT NULL --backing doc
7305: AND BACKING.send_to_gl_flag = 'Y' --bug 3568512: use new column
7315: -- it needs to be referenced by the main doc rows.
7316:
7317: l_source_doc_reference := TO_CHAR(p_invoice_id);
7318:
7319: UPDATE PO_ENCUMBRANCE_GT MAIN
7320: SET
7321: MAIN.reference6 = g_reference6_SRCDOC
7322: , MAIN.reference10 = l_source_doc_reference
7323: WHERE MAIN.send_to_gl_flag = 'Y' --bug 3568512: use new column
7346: -- or during an invoice transaction.
7347: -- In these cases, the Grants data must have been saved in the
7348: -- PO transaction tables anyway, so Grants can still figure it out.
7349:
7350: UPDATE PO_ENCUMBRANCE_GT MAIN
7351: SET
7352: MAIN.reference6 = g_reference6_GMSIP
7353:
7354: , MAIN.reference7 = to_char(project_id)
7385: IF p_action = g_action_CANCEL THEN
7386:
7387: l_progress := '160';
7388:
7389: UPDATE PO_ENCUMBRANCE_GT BACKING_REQ
7390: SET reference14 =
7391: (SELECT REQ_TABLE.source_req_distribution_id
7392: FROM PO_REQ_DISTRIBUTIONS_ALL REQ_TABLE
7393: WHERE BACKING_REQ.distribution_id = REQ_TABLE.distribution_id
7400: ELSIF p_action = g_action_ADJUST THEN
7401:
7402: l_progress := '180';
7403:
7404: UPDATE PO_ENCUMBRANCE_GT MAIN_REQ
7405: SET reference14 =
7406: (SELECT PARENT_DIST.distribution_id
7407: FROM PO_REQ_DISTRIBUTIONS_ALL PARENT_DIST
7408: , PO_REQUISITION_LINES_ALL PARENT_LINE
7451: PO_DEBUG.debug_var(l_log_head,l_progress,'l_cbc_action',l_cbc_action);
7452: PO_DEBUG.debug_var(l_log_head,l_progress,'l_cbc_line_description',l_cbc_line_description);
7453: END IF;
7454:
7455: UPDATE PO_ENCUMBRANCE_GT ALL_DISTS
7456: SET
7457: ALL_DISTS.je_line_description =
7458: SUBSTRB(ALL_DISTS.je_line_description,1,100)
7459: || '-'
7494: --Name: check_enc_action_possible
7495: --Pre-reqs:
7496: -- This check is meaningless if the appropriate encumbrance is not turned on.
7497: --Modifies:
7498: -- PO_ENCUMBRANCE_GT
7499: --Locks:
7500: -- None.
7501: --Function:
7502: -- This procedure determines whether there are any distributions below
7604: --Name: delete_encumbrance_gt
7605: --Pre-reqs:
7606: -- None.
7607: --Modifies:
7608: -- PO_ENCUMBRANCE_GT
7609: --Locks:
7610: -- None.
7611: --Function:
7612: -- Deletes all of the existing data from PO_ENCUMBRANCE_GT.
7608: -- PO_ENCUMBRANCE_GT
7609: --Locks:
7610: -- None.
7611: --Function:
7612: -- Deletes all of the existing data from PO_ENCUMBRANCE_GT.
7613: --Parameters:
7614: -- None.
7615: --Testing:
7616: --
7630: END IF;
7631:
7632: l_progress := '010';
7633:
7634: DELETE FROM PO_ENCUMBRANCE_GT ;
7635:
7636: l_progress := '900';
7637:
7638: IF g_debug_stmt THEN
7659: --Locks:
7660: -- None.
7661: --Function:
7662: -- This procedure will be used to update proper entered and accounted amount
7663: -- values in PO_ENCUMBRANCE_GT.
7664: --Parameters:
7665: --IN:
7666: -- p_action : specifies the action
7667: -- p_currency_code_func: currency code of the functional currency.
7670: --OUT:
7671: -- None.
7672: --Notes:
7673: -- The algorithm of the procedure is as follows :
7674: -- update proper entered and accounted amount values in PO_ENCUMBRANCE_GT.
7675: --Testing:
7676: --
7677: --End of Comments
7678: -------------------------------------------------------------------------------
7744: --foreign currency.
7745: --
7746: --For requisitions it is allright to have entered_amt=accounted_amt=final_amt.
7747: --This is because the requisitions are in functional currency all the time.
7748: UPDATE PO_ENCUMBRANCE_GT DISTS
7749: SET DISTS.entered_amount = decode(DISTS.distribution_type,g_dist_type_AGREEMENT,
7750: DISTS.final_amt,g_dist_type_REQUISITION,DISTS.final_amt,
7751: DISTS.pre_round_amt)
7752: ,DISTS.accounted_amount = DISTS.final_amt
7773: --foreign currency.
7774: --
7775: --For requisitions it is allright to have entered_amt=accounted_amt=final_amt.
7776: --This is because the requisitions are in functional currency all the time.
7777: UPDATE PO_ENCUMBRANCE_GT DISTS
7778: SET DISTS.entered_amount = -1 *decode(DISTS.distribution_type,g_dist_type_AGREEMENT,
7779: DISTS.final_amt,g_dist_type_REQUISITION,DISTS.final_amt,
7780: DISTS.pre_round_amt)
7781: ,DISTS.accounted_amount = -1 * DISTS.final_amt
7790: l_progress:= '060';
7791: --
7792: -- Get functional currency setup
7793: IF g_debug_stmt THEN
7794: PO_DEBUG.debug_stmt(l_log_head,l_progress,'Before starting currency conversions on entered_amount_fields in po_encumbrance_gt');
7795: PO_DEBUG.debug_table(l_log_head,l_progress,'PO_ENCUMBRANCE_GT',PO_DEBUG.g_all_rows,NULL,'PO');
7796: END IF;
7797:
7798: SELECT
7791: --
7792: -- Get functional currency setup
7793: IF g_debug_stmt THEN
7794: PO_DEBUG.debug_stmt(l_log_head,l_progress,'Before starting currency conversions on entered_amount_fields in po_encumbrance_gt');
7795: PO_DEBUG.debug_table(l_log_head,l_progress,'PO_ENCUMBRANCE_GT',PO_DEBUG.g_all_rows,NULL,'PO');
7796: END IF;
7797:
7798: SELECT
7799: FND_CUR.minimum_accountable_unit
7843: , l_cur_precision_to_tbl
7844: , l_min_acct_unit_to_tbl
7845: , l_round_only_flag_tbl
7846: , l_origin_sequence_num_tbl
7847: FROM PO_ENCUMBRANCE_GT DISTS
7848: WHERE DISTS.prevent_encumbrance_flag = 'N'
7849: AND DISTS.distribution_type <> g_dist_type_REQUISITION --
7850: ORDER BY DISTS.sequence_num
7851: ;
7890: END IF;
7891:
7892: l_progress:='100';
7893: FORALL i IN 1 .. l_sequence_num_tbl.COUNT
7894: UPDATE PO_ENCUMBRANCE_GT DISTS
7895: SET DISTS.entered_amount= l_amount_result_tbl(i)
7896: WHERE DISTS.prevent_encumbrance_flag = 'N'
7897: AND DISTS.sequence_num = l_sequence_num_tbl(i)
7898: ;
7896: WHERE DISTS.prevent_encumbrance_flag = 'N'
7897: AND DISTS.sequence_num = l_sequence_num_tbl(i)
7898: ;
7899: IF g_debug_stmt THEN
7900: PO_DEBUG.debug_stmt(l_log_head,l_progress,'After completing update on po_encumbrance_gt for distributions');
7901: PO_DEBUG.debug_var(l_log_head,l_progress,'sql%rowcount',sql%rowcount);
7902: PO_DEBUG.debug_table(l_log_head,l_progress,'PO_ENCUMBRANCE_GT',PO_DEBUG.g_all_rows,NULL,'PO');
7903: END IF;
7904: --
7898: ;
7899: IF g_debug_stmt THEN
7900: PO_DEBUG.debug_stmt(l_log_head,l_progress,'After completing update on po_encumbrance_gt for distributions');
7901: PO_DEBUG.debug_var(l_log_head,l_progress,'sql%rowcount',sql%rowcount);
7902: PO_DEBUG.debug_table(l_log_head,l_progress,'PO_ENCUMBRANCE_GT',PO_DEBUG.g_all_rows,NULL,'PO');
7903: END IF;
7904: --
7905: l_progress := '110';
7906: