[Home] [Help]
715: t_award_id(i));
716: END LOOP;
717:
718: g_error_stage := 'UPD FC SEQ: FOR ALL';
719: /* Bug#7034365 :Modified this update to pick up ind_compiled_set_id from gms_award_distributions
720: so as to ensure that we relieve burden component same as it was reserved for a PO/AP/REQ transaction
721: */
722:
723: FORALL j IN t_project_id.FIRST .. t_project_id.LAST
721: */
722:
723: FORALL j IN t_project_id.FIRST .. t_project_id.LAST
724: Update /*+ index(gbc GMS_BC_PACKETS_N1) */ gms_bc_packets gbc /*Added hint for bug 5683910 */
725: set ind_compiled_Set_id = (nvl((select ind_compiled_set_id from gms_award_distributions
726: where document_type = gbc.document_type
727: and ((document_type = 'AP' and
728: invoice_id = gbc.document_header_id and
729: distribution_line_number = gbc.document_distribution_id) OR
783: -- was funds checked earlier
784: Update gms_bc_packets gbc
785: set gbc.ind_compiled_Set_id = (Select nvl(gei.ind_compiled_set_id, adl.ind_compiled_set_id) --Bug 5122879
786: from gms_encumbrance_items gei,
787: gms_award_distributions adl
788: where adl.expenditure_item_id =gbc.adjusted_document_header_id
789: and adl.adl_status = 'A'
790: and adl.fc_status = 'A'
791: and nvl(adl.reversed_flag, 'N') = 'N'
809: /*--------------------------------------------------------------------------------------------------------
810: -- This procedure updates table values
811: -- TABLE Columns
812: -- GMS_BC_PACKETS STATUS_CODE
813: -- GMS_AWARD_DISTRIBUTIONS FC_STATUS
814: -- RESOURCE_LIST_MEMBER_ID
815: -- BUD_TASK_ID(Budgeted Task)
816: -- BUD_RES_LIST_MEMBER_ID(Budgeted rlmi)
817: -- RAW_COST
930: gbp.document_distribution_id,
931: adl.ind_compiled_set_id,
932: gbp.packet_id
933: FROM gms_bc_packets gbp,
934: gms_award_distributions adl
935: WHERE gbp.document_header_id = adl.expenditure_item_id
936: and gbp.document_distribution_id = adl.adl_line_num
937: and gbp.packet_id = p_packet_id
938: AND gbp.parent_bc_packet_id IS NULL
1037:
1038: IF tpacket_id.COUNT > 0 THEN
1039:
1040: FORALL I in tpacket_id.FIRST..tpacket_id.LAST
1041: UPDATE gms_award_distributions
1042: SET resource_list_member_id = tresource_list_member_id(i),
1043: bud_task_id = tbud_task_id(i),
1044: fc_status = DECODE(p_mode,'B',fc_status,
1045: DECODE (SUBSTR (tresult_code(i), 1, 1), 'P', 'A', 'R'))
1099:
1100: IF tpacket_id.COUNT > 0 THEN
1101:
1102: FORALL I in tpacket_id.FIRST..tpacket_id.LAST
1103: UPDATE gms_award_distributions
1104: SET cost_distributed_flag =
1105: DECODE(P_MODE,'B',cost_distributed_flag,DECODE (SUBSTR (tresult_code(i), 1, 1), 'P', 'Y', 'N')),
1106: fc_status = DECODE(P_MODE,'B',FC_STATUS,DECODE (SUBSTR (tresult_code(i), 1, 1), 'P', 'A', 'R')),
1107: raw_cost = DECODE(P_MODE,'B',RAW_COST,NVL (tentered_dr(i), 0) - NVL (tentered_cr(i), 0)),
1128: --Bug 5845974
1129: IF p_mode <> 'B' THEN
1130: g_error_stage := 'UPDATE_ADL_WITH_GL_DATE';
1131: FORALL I in tpacket_id.FIRST..tpacket_id.LAST
1132: update gms_award_distributions
1133: set gl_date = pa_utils2.get_prvdr_gl_date(texpenditure_item_date(i), 101, tset_of_books_id(i))
1134: where document_type = 'ENC'
1135: and adl_status = 'A'
1136: and expenditure_item_id = tdocument_header_id(i)
1294:
1295: x_stage NUMBER;
1296: x_award_set_id NUMBER;
1297: x_flip_adl_status varchar2(1); -- Bug 2155774
1298: l_adl_status gms_award_distributions.adl_status%TYPE;
1299: l_adl_document_type gms_award_distributions.document_type%TYPE;
1300: l_adl_invoice_id NUMBER;
1301: l_adl_dist_id gms_award_distributions.invoice_distribution_id%TYPE;
1302: l_award_set_id gms_award_distributions.award_set_id%TYPE;
1295: x_stage NUMBER;
1296: x_award_set_id NUMBER;
1297: x_flip_adl_status varchar2(1); -- Bug 2155774
1298: l_adl_status gms_award_distributions.adl_status%TYPE;
1299: l_adl_document_type gms_award_distributions.document_type%TYPE;
1300: l_adl_invoice_id NUMBER;
1301: l_adl_dist_id gms_award_distributions.invoice_distribution_id%TYPE;
1302: l_award_set_id gms_award_distributions.award_set_id%TYPE;
1303:
1297: x_flip_adl_status varchar2(1); -- Bug 2155774
1298: l_adl_status gms_award_distributions.adl_status%TYPE;
1299: l_adl_document_type gms_award_distributions.document_type%TYPE;
1300: l_adl_invoice_id NUMBER;
1301: l_adl_dist_id gms_award_distributions.invoice_distribution_id%TYPE;
1302: l_award_set_id gms_award_distributions.award_set_id%TYPE;
1303:
1304:
1305: CURSOR c_ap (p_inv_dist_id NUMBER) IS
1298: l_adl_status gms_award_distributions.adl_status%TYPE;
1299: l_adl_document_type gms_award_distributions.document_type%TYPE;
1300: l_adl_invoice_id NUMBER;
1301: l_adl_dist_id gms_award_distributions.invoice_distribution_id%TYPE;
1302: l_award_set_id gms_award_distributions.award_set_id%TYPE;
1303:
1304:
1305: CURSOR c_ap (p_inv_dist_id NUMBER) IS
1306: SELECT DISTINCT adl.award_set_id,
1307: adl.document_type, -- Bug 2433889
1308: adl.invoice_id, -- Bug 2433889
1309: adl.invoice_distribution_id -- Bug 2433889
1310: FROM ap_invoice_distributions_all ap,
1311: gms_award_distributions adl
1312: WHERE ap.invoice_distribution_id = p_inv_dist_id
1313: AND ap.award_id IS NOT NULL
1314: AND ap.award_id = adl.award_set_id
1315: AND adl.adl_line_num = 1;
1317:
1318: CURSOR c_exp_adl (p_expenditure_item_id NUMBER,
1319: p_cdl_line_num NUMBER) IS
1320: SELECT *
1321: FROM gms_award_distributions
1322: WHERE document_type = 'EXP'
1323: AND adl_status = 'A'
1324: AND expenditure_item_id = NVL (p_expenditure_item_id, -1)
1325: AND cdl_line_num = NVL (p_cdl_line_num, -1);
1328:
1329: -- Bug 2155774, Cursor to pick all the REQ distributions not having adls
1330: CURSOR c_REQ_miss_adls (p_distribution_id NUMBER) IS
1331: SELECT adl.award_set_id, adl.adl_status
1332: FROM gms_award_distributions adl,
1333: po_req_distributions pd
1334: WHERE pd.distribution_id = p_distribution_id
1335: AND pd.award_id = adl.award_set_id
1336: AND adl.adl_line_num = 1
1333: po_req_distributions pd
1334: WHERE pd.distribution_id = p_distribution_id
1335: AND pd.award_id = adl.award_set_id
1336: AND adl.adl_line_num = 1
1337: AND not exists (select 1 from gms_award_distributions gad
1338: where gad.award_set_id = pd.award_id
1339: and gad.document_type = 'REQ'
1340: and gad.distribution_id = pd.distribution_id
1341: and gad.adl_status = 'A');
1343: CURSOR c_po_miss_adls (p_po_distribution_id NUMBER) IS
1344: SELECT pod.award_id award_set_id,
1345: adl.adl_status -- Bug 2155774
1346: FROM po_distributions_all pod,
1347: gms_award_distributions adl
1348: WHERE pod.po_distribution_id = p_po_distribution_id
1349: AND pod.award_id IS NOT NULL
1350: AND pod.award_id = adl.award_set_id
1351: AND adl.adl_line_num = 1 -- Bug 2155774
1350: AND pod.award_id = adl.award_set_id
1351: AND adl.adl_line_num = 1 -- Bug 2155774
1352: AND (adl.document_type = 'REQ'
1353: OR NOT EXISTS (SELECT 1
1354: FROM gms_award_distributions gad
1355: WHERE gad.award_set_id = pod.award_id
1356: AND gad.po_distribution_id = pod.po_distribution_id
1357: AND gad.adl_status = 'A')); -- Bug 2155774, added to pick distribution lines
1358: -- created by copy of PO and distribution lines
1380: -- due to data entry combinations.
1381: -- ========================================================
1382: -- bug : 2308005
1383:
1384: UPDATE gms_award_distributions adl
1385: SET adl_status = 'I'
1386: WHERE adl.adl_status = 'A'
1387: AND (adl.award_set_id,adl.document_type) IN ( SELECT adl2.award_set_id,adl2.document_type
1388: FROM gms_award_distributions adl2,
1384: UPDATE gms_award_distributions adl
1385: SET adl_status = 'I'
1386: WHERE adl.adl_status = 'A'
1387: AND (adl.award_set_id,adl.document_type) IN ( SELECT adl2.award_set_id,adl2.document_type
1388: FROM gms_award_distributions adl2,
1389: po_req_distributions_all pd
1390: WHERE g_document_type_tab(i) = 'REQ'
1391: AND adl2.document_type = 'REQ'
1392: AND adl2.adl_line_num = 1
1394: AND pd.distribution_id = g_doc_dist_id_tab(i)
1395: AND pd.award_id <> adl2.award_set_id
1396: UNION ALL
1397: SELECT adl2.award_set_id,adl2.document_type
1398: FROM gms_award_distributions adl2,
1399: po_distributions_all pd
1400: WHERE g_document_type_tab(i) = 'PO'
1401: AND adl2.document_type = 'PO'
1402: AND adl2.adl_line_num = 1
1455:
1456: IF NVL(l_award_set_id ,0) <>0 THEN
1457:
1458: If l_adl_status = 'I' Then
1459: update gms_award_distributions
1460: set adl_status = 'A'
1461: where award_set_id = l_award_set_id ;
1462: x_flip_adl_status := 'Y';
1463: End If;
1469: p_called_from => 'MISC_SYNCH_ADLS'
1470: );
1471:
1472: If (x_flip_adl_status = 'Y') Then
1473: update gms_award_distributions
1474: set adl_status = 'I'
1475: where award_set_id = l_award_set_id ;
1476: x_flip_adl_status := 'N';
1477: End If;
1492: -- ===================
1493:
1494: FOR i in 1..g_set_of_books_id_tab.count LOOP
1495:
1496: update gms_award_distributions adl
1497: set adl.adl_status = 'A'
1498: where adl.document_type = 'AP'
1499: and adl.adl_status = 'I'
1500: and adl.award_set_id in ( select adl2.award_set_id
1497: set adl.adl_status = 'A'
1498: where adl.document_type = 'AP'
1499: and adl.adl_status = 'I'
1500: and adl.award_set_id in ( select adl2.award_set_id
1501: from gms_award_distributions adl2,
1502: ap_invoice_distributions_all apd
1503: where apd.invoice_id = g_doc_header_id_tab(i)
1504: AND apd.invoice_distribution_id = g_doc_dist_id_tab(i)
1505: and apd.award_id is not null
2097: /* Bug 5369296 : If the AP distribution is a reversing distribution (i.e parent_reversal_id is not null) ,
2098: then the burdenable raw cost on the amount/quantity variance records in gms_bc_packets is stamped as 0.
2099: This is because after cancelling an invoice matched to a PO with quantity/amount variance the
2100: burdenable raw cost for the reversing distribution is populated on the basis of that populated in
2101: gms_award_distributions for the original distribution . The burdenable raw cost populated in
2102: gms_award_distributions for the original distribution includes the burdenable raw cost for both the normal
2103: distribution amount and the quantity/amount variance amount. So during invoice cancel we should not
2104: create the burden for the amount/quantity variance record in gms_bc_packets i.e the burdenable raw cost on the
2105: quantity/amount variance record in gms_bc_packets for the reversing distribution should be zero. */
2098: then the burdenable raw cost on the amount/quantity variance records in gms_bc_packets is stamped as 0.
2099: This is because after cancelling an invoice matched to a PO with quantity/amount variance the
2100: burdenable raw cost for the reversing distribution is populated on the basis of that populated in
2101: gms_award_distributions for the original distribution . The burdenable raw cost populated in
2102: gms_award_distributions for the original distribution includes the burdenable raw cost for both the normal
2103: distribution amount and the quantity/amount variance amount. So during invoice cancel we should not
2104: create the burden for the amount/quantity variance record in gms_bc_packets i.e the burdenable raw cost on the
2105: quantity/amount variance record in gms_bc_packets for the reversing distribution should be zero. */
2106:
2191: -- the packet should fail with the correct failure result code.
2192: -- ----------------------------------------------------------------------------------
2193: l_dist_award_id NUMBER;
2194: l_award_dist_option VARCHAR2 (1);
2195: x_adl_rec gms_award_distributions%ROWTYPE;
2196: l_packet_id gms_bc_packets.packet_id%TYPE;
2197:
2198:
2199: -- IP records are fetched by c_req_po_pkt_rec cursor as IP records will
2421: l_dist_award_id, 'F21',
2422: DECODE (adl.award_id, NULL, 'F62', NULL)), --Bug Fix 1599750(2)-- RESULT CODE for missing ADLS F62
2423: pov.vendor_id,
2424: adl.ind_compiled_set_id
2425: FROM gms_award_distributions adl,
2426: po_requisition_lines_all porl,
2427: po_req_distributions_all pord,
2428: po_vendors pov
2429: WHERE pord.distribution_id = p_req_dist_id
2451: FROM po_distributions_all pod,
2452: po_headers_all poh,
2453: po_lines_all pol, --BUG 3022249
2454: po_line_locations_all pll, -- BUG 3022249
2455: gms_award_distributions adl
2456: WHERE pod.po_distribution_id = p_po_dist_id
2457: AND pod.project_id IS NOT NULL
2458: AND pod.po_header_id = poh.po_header_id
2459: and pol.po_header_id = poh.po_header_id
2490: distribution is interfaced to Grants then calculate the BRC else the burdenable raw cost for the reversing
2491: distribution is the negative of the BRC for the parent distribution. */
2492: ( SELECT decode(ap1.pa_addition_flag,'Y',NULL,-1 * nvl(adl1.burdenable_raw_cost,0)) --R12 AP Lines Uptake enhancement : Forward porting bug 4450291
2493: -- Reversing AP distributions should copy the BRC from reversed Distribution
2494: FROM gms_award_distributions adl1,
2495: ap_invoice_distributions ap1
2496: WHERE adl1.document_type = 'AP'
2497: AND adl1.adl_status = 'A' -- Bug 5654186
2498: AND adl1.fc_status = 'A' -- Bug 5654186
2505: AND adl1.adl_line_num =1 ) burdenable_raw_cost ,
2506: apd.parent_reversal_id parent_reversal_id -- Bug 5369296
2507: FROM ap_invoice_distributions_all apd,
2508: ap_invoices_all api,
2509: gms_award_distributions adl,
2510: pa_expenditure_types et
2511: WHERE apd.invoice_distribution_id = p_ap_dist_id
2512: AND apd.project_id IS NOT NULL
2513: AND (NVL (apd.pa_addition_flag, 'X') <> 'T')
2537: l_debug_start_counter NUMBER;
2538: l_po_award_id po_distributions_all.award_id%TYPE;
2539: l_po_result_code gms_bc_packets.result_code%TYPE;
2540: l_po_vendor_id po_headers_all.vendor_id%TYPE;
2541: l_po_ind_com_set_id gms_award_distributions.ind_compiled_set_id%TYPE;
2542:
2543: -- Procedure to intialize PLSQL type variables
2544: PROCEDURE Intialize_tabs IS
2545: BEGIN
2926:
2927: IF l_award_dist_option = 'Y' THEN
2928:
2929: -- --------------------------------------------------------------------------+
2930: -- Insert a dummy record into gms_award_distributions for the default award id
2931: -- to remove the outer joints on gms_award_distributions.
2932: -- --------------------------------------------------------------------------+
2933: x_adl_rec.award_set_id := l_dist_award_id;
2934: x_adl_rec.adl_line_num := 1;
2927: IF l_award_dist_option = 'Y' THEN
2928:
2929: -- --------------------------------------------------------------------------+
2930: -- Insert a dummy record into gms_award_distributions for the default award id
2931: -- to remove the outer joints on gms_award_distributions.
2932: -- --------------------------------------------------------------------------+
2933: x_adl_rec.award_set_id := l_dist_award_id;
2934: x_adl_rec.adl_line_num := 1;
2935: x_adl_rec.document_type := 'DST';
3146: IF g_debug = 'Y' THEN
3147: gms_error_pkg.gms_debug (g_error_procedure_name||':'||'Deleting dummy ADL','C');
3148: END IF;
3149:
3150: DELETE gms_award_distributions
3151: WHERE award_set_id = NVL (l_dist_award_id, 0)
3152: AND document_type = 'DST'
3153: AND adl_line_num = 1
3154: AND adl_status = 'A'
3214:
3215: EXCEPTION
3216: WHEN OTHERS THEN
3217: IF l_award_dist_option = 'Y' THEN
3218: DELETE gms_award_distributions
3219: WHERE award_set_id = l_dist_award_id
3220: AND document_type = 'DST'
3221: AND adl_line_num = 1
3222: AND adl_status = 'A'
3636: TYPE t_rowid IS TABLE OF VARCHAR2 (50);
3637:
3638: TYPE t_rlmi IS TABLE OF gms_bc_packets.resource_list_member_id%TYPE;
3639:
3640: -- TYPE t_upg_err IS TABLE OF gms_award_distributions.upg_error%TYPE; -- Bug 2178694
3641:
3642: TYPE t_fc_err IS TABLE OF gms_bc_packets.fc_error_message%TYPE;
3643:
3644: t_doc_type t_doctype;
7963: FROM gms_bc_packets pkt,
7964: gl_bc_packets gl,
7965: ap_invoice_distributions_all ap,
7966: po_distributions_all pod,
7967: gms_award_distributions adl
7968: WHERE pkt.packet_id = x_packet_id
7969: AND pkt.document_type = 'AP'
7970: AND gl.packet_id = pkt.packet_id
7971: AND ROWIDTOCHAR (gl.ROWID) = pkt.gl_bc_packets_rowid