[Home] [Help]
732: t_award_id(i));
733: END LOOP;
734:
735: g_error_stage := 'UPD FC SEQ: FOR ALL';
736: /* Bug#7034365 :Modified this update to pick up ind_compiled_set_id from gms_award_distributions
737: so as to ensure that we relieve burden component same as it was reserved for a PO/AP/REQ transaction
738: */
739:
740: FORALL j IN t_project_id.FIRST .. t_project_id.LAST
738: */
739:
740: FORALL j IN t_project_id.FIRST .. t_project_id.LAST
741: Update /*+ index(gbc GMS_BC_PACKETS_N1) */ gms_bc_packets gbc /*Added hint for bug 5683910 */
742: set ind_compiled_Set_id = (nvl((select ind_compiled_set_id from gms_award_distributions
743: where document_type = gbc.document_type
744: and ((document_type = 'AP' and
745: invoice_id = gbc.document_header_id and
746: --distribution_line_number = gbc.document_distribution_id) OR -- Bug 10082739: Joins were not proper
801: -- was funds checked earlier
802: Update gms_bc_packets gbc
803: set gbc.ind_compiled_Set_id = (Select nvl(gei.ind_compiled_set_id, adl.ind_compiled_set_id) --Bug 5122879
804: from gms_encumbrance_items gei,
805: gms_award_distributions adl
806: where adl.expenditure_item_id =gbc.adjusted_document_header_id
807: and adl.adl_status = 'A'
808: and adl.fc_status = 'A'
809: and nvl(adl.reversed_flag, 'N') = 'N'
827: /*--------------------------------------------------------------------------------------------------------
828: -- This procedure updates table values
829: -- TABLE Columns
830: -- GMS_BC_PACKETS STATUS_CODE
831: -- GMS_AWARD_DISTRIBUTIONS FC_STATUS
832: -- RESOURCE_LIST_MEMBER_ID
833: -- BUD_TASK_ID(Budgeted Task)
834: -- BUD_RES_LIST_MEMBER_ID(Budgeted rlmi)
835: -- RAW_COST
948: gbp.document_distribution_id,
949: adl.ind_compiled_set_id,
950: gbp.packet_id
951: FROM gms_bc_packets gbp,
952: gms_award_distributions adl
953: WHERE gbp.document_header_id = adl.expenditure_item_id
954: and gbp.document_distribution_id = adl.adl_line_num
955: and gbp.packet_id = p_packet_id
956: AND gbp.parent_bc_packet_id IS NULL
1055:
1056: IF tpacket_id.COUNT > 0 THEN
1057:
1058: FORALL I in tpacket_id.FIRST..tpacket_id.LAST
1059: UPDATE gms_award_distributions
1060: SET resource_list_member_id = tresource_list_member_id(i),
1061: bud_task_id = tbud_task_id(i),
1062: fc_status = DECODE(p_mode,'B',fc_status,
1063: DECODE (SUBSTR (tresult_code(i), 1, 1), 'P', 'A', 'R'))
1117:
1118: IF tpacket_id.COUNT > 0 THEN
1119:
1120: FORALL I in tpacket_id.FIRST..tpacket_id.LAST
1121: UPDATE gms_award_distributions
1122: SET cost_distributed_flag =
1123: DECODE(P_MODE,'B',cost_distributed_flag,DECODE (SUBSTR (tresult_code(i), 1, 1), 'P', 'Y', 'N')),
1124: fc_status = DECODE(P_MODE,'B',FC_STATUS,DECODE (SUBSTR (tresult_code(i), 1, 1), 'P', 'A', 'R')),
1125: raw_cost = DECODE(P_MODE,'B',RAW_COST,NVL (tentered_dr(i), 0) - NVL (tentered_cr(i), 0)),
1146: --Bug 5845974
1147: IF p_mode <> 'B' THEN
1148: g_error_stage := 'UPDATE_ADL_WITH_GL_DATE';
1149: FORALL I in tpacket_id.FIRST..tpacket_id.LAST
1150: update gms_award_distributions
1151: set gl_date = pa_utils2.get_prvdr_gl_date(texpenditure_item_date(i), 101, tset_of_books_id(i))
1152: where document_type = 'ENC'
1153: and adl_status = 'A'
1154: and expenditure_item_id = tdocument_header_id(i)
1312:
1313: x_stage NUMBER;
1314: x_award_set_id NUMBER;
1315: x_flip_adl_status varchar2(1); -- Bug 2155774
1316: l_adl_status gms_award_distributions.adl_status%TYPE;
1317: l_adl_document_type gms_award_distributions.document_type%TYPE;
1318: l_adl_invoice_id NUMBER;
1319: l_adl_dist_id gms_award_distributions.invoice_distribution_id%TYPE;
1320: l_award_set_id gms_award_distributions.award_set_id%TYPE;
1313: x_stage NUMBER;
1314: x_award_set_id NUMBER;
1315: x_flip_adl_status varchar2(1); -- Bug 2155774
1316: l_adl_status gms_award_distributions.adl_status%TYPE;
1317: l_adl_document_type gms_award_distributions.document_type%TYPE;
1318: l_adl_invoice_id NUMBER;
1319: l_adl_dist_id gms_award_distributions.invoice_distribution_id%TYPE;
1320: l_award_set_id gms_award_distributions.award_set_id%TYPE;
1321:
1315: x_flip_adl_status varchar2(1); -- Bug 2155774
1316: l_adl_status gms_award_distributions.adl_status%TYPE;
1317: l_adl_document_type gms_award_distributions.document_type%TYPE;
1318: l_adl_invoice_id NUMBER;
1319: l_adl_dist_id gms_award_distributions.invoice_distribution_id%TYPE;
1320: l_award_set_id gms_award_distributions.award_set_id%TYPE;
1321:
1322:
1323: CURSOR c_ap (p_inv_dist_id NUMBER) IS
1316: l_adl_status gms_award_distributions.adl_status%TYPE;
1317: l_adl_document_type gms_award_distributions.document_type%TYPE;
1318: l_adl_invoice_id NUMBER;
1319: l_adl_dist_id gms_award_distributions.invoice_distribution_id%TYPE;
1320: l_award_set_id gms_award_distributions.award_set_id%TYPE;
1321:
1322:
1323: CURSOR c_ap (p_inv_dist_id NUMBER) IS
1324: SELECT DISTINCT adl.award_set_id,
1325: adl.document_type, -- Bug 2433889
1326: adl.invoice_id, -- Bug 2433889
1327: adl.invoice_distribution_id -- Bug 2433889
1328: FROM ap_invoice_distributions_all ap,
1329: gms_award_distributions adl
1330: WHERE ap.invoice_distribution_id = p_inv_dist_id
1331: AND ap.award_id IS NOT NULL
1332: AND ap.award_id = adl.award_set_id
1333: AND adl.adl_line_num = 1
1336: adl.document_type,
1337: adl.invoice_id,
1338: adl.invoice_distribution_id
1339: FROM AP_SELF_ASSESSED_TAX_DIST_ALL apsat,
1340: GMS_AWARD_DISTRIBUTIONS ADL
1341: WHERE APSAT.INVOICE_DISTRIBUTION_ID = P_INV_DIST_ID
1342: AND APSAT.AWARD_ID IS NOT NULL
1343: AND APSAT.AWARD_ID = ADL.AWARD_SET_ID
1344: AND adl.adl_line_num = 1;
1346:
1347: CURSOR c_exp_adl (p_expenditure_item_id NUMBER,
1348: p_cdl_line_num NUMBER) IS
1349: SELECT *
1350: FROM gms_award_distributions
1351: WHERE document_type = 'EXP'
1352: AND adl_status = 'A'
1353: AND expenditure_item_id = NVL (p_expenditure_item_id, -1)
1354: AND cdl_line_num = NVL (p_cdl_line_num, -1);
1357:
1358: -- Bug 2155774, Cursor to pick all the REQ distributions not having adls
1359: CURSOR c_REQ_miss_adls (p_distribution_id NUMBER) IS
1360: SELECT adl.award_set_id, adl.adl_status
1361: FROM gms_award_distributions adl,
1362: po_req_distributions pd
1363: WHERE pd.distribution_id = p_distribution_id
1364: AND pd.award_id = adl.award_set_id
1365: AND adl.adl_line_num = 1
1362: po_req_distributions pd
1363: WHERE pd.distribution_id = p_distribution_id
1364: AND pd.award_id = adl.award_set_id
1365: AND adl.adl_line_num = 1
1366: AND not exists (select 1 from gms_award_distributions gad
1367: where gad.award_set_id = pd.award_id
1368: and gad.document_type = 'REQ'
1369: and gad.distribution_id = pd.distribution_id
1370: and gad.adl_status = 'A');
1372: CURSOR c_po_miss_adls (p_po_distribution_id NUMBER) IS
1373: SELECT pod.award_id award_set_id,
1374: adl.adl_status -- Bug 2155774
1375: FROM po_distributions_all pod,
1376: gms_award_distributions adl
1377: WHERE pod.po_distribution_id = p_po_distribution_id
1378: AND pod.award_id IS NOT NULL
1379: AND pod.award_id = adl.award_set_id
1380: AND adl.adl_line_num = 1 -- Bug 2155774
1379: AND pod.award_id = adl.award_set_id
1380: AND adl.adl_line_num = 1 -- Bug 2155774
1381: AND (adl.document_type = 'REQ'
1382: OR NOT EXISTS (SELECT 1
1383: FROM gms_award_distributions gad
1384: WHERE gad.award_set_id = pod.award_id
1385: AND gad.po_distribution_id = pod.po_distribution_id
1386: AND gad.adl_status = 'A')); -- Bug 2155774, added to pick distribution lines
1387: -- created by copy of PO and distribution lines
1409: -- due to data entry combinations.
1410: -- ========================================================
1411: -- bug : 2308005
1412:
1413: UPDATE gms_award_distributions adl
1414: SET adl_status = 'I'
1415: WHERE adl.adl_status = 'A'
1416: AND (adl.award_set_id,adl.document_type) IN ( SELECT adl2.award_set_id,adl2.document_type
1417: FROM gms_award_distributions adl2,
1413: UPDATE gms_award_distributions adl
1414: SET adl_status = 'I'
1415: WHERE adl.adl_status = 'A'
1416: AND (adl.award_set_id,adl.document_type) IN ( SELECT adl2.award_set_id,adl2.document_type
1417: FROM gms_award_distributions adl2,
1418: po_req_distributions_all pd
1419: WHERE g_document_type_tab(i) = 'REQ'
1420: AND adl2.document_type = 'REQ'
1421: AND adl2.adl_line_num = 1
1423: AND pd.distribution_id = g_doc_dist_id_tab(i)
1424: AND pd.award_id <> adl2.award_set_id
1425: UNION ALL
1426: SELECT adl2.award_set_id,adl2.document_type
1427: FROM gms_award_distributions adl2,
1428: po_distributions_all pd
1429: WHERE g_document_type_tab(i) = 'PO'
1430: AND adl2.document_type = 'PO'
1431: AND adl2.adl_line_num = 1
1484:
1485: IF NVL(l_award_set_id ,0) <>0 THEN
1486:
1487: If l_adl_status = 'I' Then
1488: update gms_award_distributions
1489: set adl_status = 'A'
1490: where award_set_id = l_award_set_id ;
1491: x_flip_adl_status := 'Y';
1492: End If;
1498: p_called_from => 'MISC_SYNCH_ADLS'
1499: );
1500:
1501: If (x_flip_adl_status = 'Y') Then
1502: update gms_award_distributions
1503: set adl_status = 'I'
1504: where award_set_id = l_award_set_id ;
1505: x_flip_adl_status := 'N';
1506: End If;
1521: -- ===================
1522:
1523: FOR i in 1..g_set_of_books_id_tab.count LOOP
1524:
1525: update gms_award_distributions adl
1526: set adl.adl_status = 'A'
1527: where adl.document_type = 'AP'
1528: and adl.adl_status = 'I'
1529: and adl.award_set_id in ( select adl2.award_set_id
1526: set adl.adl_status = 'A'
1527: where adl.document_type = 'AP'
1528: and adl.adl_status = 'I'
1529: and adl.award_set_id in ( select adl2.award_set_id
1530: from gms_award_distributions adl2,
1531: ap_invoice_distributions_all apd
1532: where apd.invoice_id = g_doc_header_id_tab(i)
1533: AND apd.invoice_distribution_id = g_doc_dist_id_tab(i)
1534: and apd.award_id is not null
1538: and adl2.invoice_distribution_id = apd.invoice_distribution_id
1539: and adl2.adl_status = 'I'
1540: union /* BUG 14216205 : Added the union for SAT */
1541: select ADL3.AWARD_SET_ID
1542: from GMS_AWARD_DISTRIBUTIONS ADL3,
1543: AP_SELF_ASSESSED_TAX_DIST_ALL APSAT
1544: where APSAT.INVOICE_ID = g_doc_header_id_tab(i)
1545: AND apsat.invoice_distribution_id = g_doc_dist_id_tab(i)
1546: AND apsat.award_id IS NOT NULL
2144: /* Bug 5369296 : If the AP distribution is a reversing distribution (i.e parent_reversal_id is not null) ,
2145: then the burdenable raw cost on the amount/quantity variance records in gms_bc_packets is stamped as 0.
2146: This is because after cancelling an invoice matched to a PO with quantity/amount variance the
2147: burdenable raw cost for the reversing distribution is populated on the basis of that populated in
2148: gms_award_distributions for the original distribution . The burdenable raw cost populated in
2149: gms_award_distributions for the original distribution includes the burdenable raw cost for both the normal
2150: distribution amount and the quantity/amount variance amount. So during invoice cancel we should not
2151: create the burden for the amount/quantity variance record in gms_bc_packets i.e the burdenable raw cost on the
2152: quantity/amount variance record in gms_bc_packets for the reversing distribution should be zero. */
2145: then the burdenable raw cost on the amount/quantity variance records in gms_bc_packets is stamped as 0.
2146: This is because after cancelling an invoice matched to a PO with quantity/amount variance the
2147: burdenable raw cost for the reversing distribution is populated on the basis of that populated in
2148: gms_award_distributions for the original distribution . The burdenable raw cost populated in
2149: gms_award_distributions for the original distribution includes the burdenable raw cost for both the normal
2150: distribution amount and the quantity/amount variance amount. So during invoice cancel we should not
2151: create the burden for the amount/quantity variance record in gms_bc_packets i.e the burdenable raw cost on the
2152: quantity/amount variance record in gms_bc_packets for the reversing distribution should be zero. */
2153:
2238: -- the packet should fail with the correct failure result code.
2239: -- ----------------------------------------------------------------------------------
2240: l_dist_award_id NUMBER;
2241: l_award_dist_option VARCHAR2 (1);
2242: x_adl_rec gms_award_distributions%ROWTYPE;
2243: l_packet_id gms_bc_packets.packet_id%TYPE;
2244:
2245:
2246: -- IP records are fetched by c_req_po_pkt_rec cursor as IP records will
2647: l_dist_award_id, 'F21',
2648: DECODE (adl.award_id, NULL, 'F62', NULL)), --Bug Fix 1599750(2)-- RESULT CODE for missing ADLS F62
2649: pov.vendor_id,
2650: adl.ind_compiled_set_id
2651: FROM gms_award_distributions adl,
2652: po_requisition_lines_all porl,
2653: po_req_distributions_all pord,
2654: po_vendors pov
2655: WHERE pord.distribution_id = p_req_dist_id
2677: FROM po_distributions_all pod,
2678: po_headers_all poh,
2679: po_lines_all pol, --BUG 3022249
2680: po_line_locations_all pll, -- BUG 3022249
2681: gms_award_distributions adl
2682: WHERE pod.po_distribution_id = p_po_dist_id
2683: AND pod.project_id IS NOT NULL
2684: AND pod.po_header_id = poh.po_header_id
2685: and pol.po_header_id = poh.po_header_id
2716: distribution is interfaced to Grants then calculate the BRC else the burdenable raw cost for the reversing
2717: distribution is the negative of the BRC for the parent distribution. */
2718: ( SELECT decode(ap1.pa_addition_flag,'Y',NULL,-1 * nvl(adl1.burdenable_raw_cost,0)) --R12 AP Lines Uptake enhancement : Forward porting bug 4450291
2719: -- Reversing AP distributions should copy the BRC from reversed Distribution
2720: FROM gms_award_distributions adl1,
2721: ap_invoice_distributions ap1
2722: WHERE adl1.document_type = 'AP'
2723: AND adl1.adl_status = 'A' -- Bug 5654186
2724: AND adl1.fc_status = 'A' -- Bug 5654186
2731: AND adl1.adl_line_num =1 ) burdenable_raw_cost ,
2732: apd.parent_reversal_id parent_reversal_id -- Bug 5369296
2733: FROM ap_invoice_distributions_all apd,
2734: ap_invoices_all api,
2735: gms_award_distributions adl,
2736: pa_expenditure_types et
2737: WHERE apd.invoice_distribution_id = p_ap_dist_id
2738: AND apd.project_id IS NOT NULL
2739: AND (NVL (apd.pa_addition_flag, 'X') <> 'T')
2764: DECODE ( adl.award_id,l_dist_award_id, 'F21', DECODE (adl.award_id, NULL, 'F62', NULL)),
2765: api.vendor_id,
2766: ADL.IND_COMPILED_SET_ID ,
2767: (SELECT DECODE(apt.pa_addition_flag,'Y',NULL,-1 * NVL(adl1.burdenable_raw_cost,0))
2768: from GMS_AWARD_DISTRIBUTIONS ADL1,
2769: AP_SELF_ASSESSED_TAX_DIST apt
2770: WHERE adl1.document_type = 'AP'
2771: AND adl1.adl_status = 'A'
2772: and ADL1.FC_STATUS = 'A'
2779: AND adl1.adl_line_num =1 ) burdenable_raw_cost ,
2780: apsat.PARENT_REVERSAL_ID PARENT_REVERSAL_ID
2781: FROM AP_SELF_ASSESSED_TAX_DIST_ALL apsat,
2782: ap_invoices_all api,
2783: gms_award_distributions adl,
2784: PA_EXPENDITURE_TYPES ET
2785: WHERE apsat.invoice_distribution_id = p_ap_dist_id
2786: AND apsat.project_id IS NOT NULL
2787: and (NVL (APSAT.PA_ADDITION_FLAG, 'X') <> 'T')
2808: l_debug_start_counter NUMBER;
2809: l_po_award_id po_distributions_all.award_id%TYPE;
2810: l_po_result_code gms_bc_packets.result_code%TYPE;
2811: l_po_vendor_id po_headers_all.vendor_id%TYPE;
2812: l_po_ind_com_set_id gms_award_distributions.ind_compiled_set_id%TYPE;
2813:
2814: -- Procedure to intialize PLSQL type variables
2815: PROCEDURE Intialize_tabs IS
2816: BEGIN
3197:
3198: IF l_award_dist_option = 'Y' THEN
3199:
3200: -- --------------------------------------------------------------------------+
3201: -- Insert a dummy record into gms_award_distributions for the default award id
3202: -- to remove the outer joints on gms_award_distributions.
3203: -- --------------------------------------------------------------------------+
3204: x_adl_rec.award_set_id := l_dist_award_id;
3205: x_adl_rec.adl_line_num := 1;
3198: IF l_award_dist_option = 'Y' THEN
3199:
3200: -- --------------------------------------------------------------------------+
3201: -- Insert a dummy record into gms_award_distributions for the default award id
3202: -- to remove the outer joints on gms_award_distributions.
3203: -- --------------------------------------------------------------------------+
3204: x_adl_rec.award_set_id := l_dist_award_id;
3205: x_adl_rec.adl_line_num := 1;
3206: x_adl_rec.document_type := 'DST';
3417: IF g_debug = 'Y' THEN
3418: gms_error_pkg.gms_debug (g_error_procedure_name||':'||'Deleting dummy ADL','C');
3419: END IF;
3420:
3421: DELETE gms_award_distributions
3422: WHERE award_set_id = NVL (l_dist_award_id, 0)
3423: AND document_type = 'DST'
3424: AND adl_line_num = 1
3425: AND adl_status = 'A'
3485:
3486: EXCEPTION
3487: WHEN OTHERS THEN
3488: IF l_award_dist_option = 'Y' THEN
3489: DELETE gms_award_distributions
3490: WHERE award_set_id = l_dist_award_id
3491: AND document_type = 'DST'
3492: AND adl_line_num = 1
3493: AND adl_status = 'A'
3560: trying to relieve funds from AP bucket*/
3561:
3562: UPDATE gms_bc_packets gbc
3563: SET ind_compiled_set_id =
3564: (select adl.ind_compiled_set_id from gms_award_distributions adl
3565: where adl.invoice_id = gbc.document_header_id
3566: and adl.invoice_distribution_id = gbc.document_distribution_id
3567: and adl.adl_status = 'A'
3568: and adl.fc_status = 'A'
3571: AND gbc.status_code = 'P'
3572: AND gbc.document_type = 'AP'
3573: and gbc.actual_flag = 'E'
3574: AND nvl(gbc.burden_adjustment_flag,'N') = 'N'
3575: AND exists (select 1 from gms_award_distributions adl2
3576: where adl2.invoice_id = gbc.document_header_id
3577: and adl2.invoice_distribution_id = gbc.document_distribution_id
3578: and adl2.adl_status = 'A'
3579: and adl2.fc_status = 'A'
3942: TYPE t_rowid IS TABLE OF VARCHAR2 (50);
3943:
3944: TYPE t_rlmi IS TABLE OF gms_bc_packets.resource_list_member_id%TYPE;
3945:
3946: -- TYPE t_upg_err IS TABLE OF gms_award_distributions.upg_error%TYPE; -- Bug 2178694
3947:
3948: TYPE t_fc_err IS TABLE OF gms_bc_packets.fc_error_message%TYPE;
3949:
3950: t_doc_type t_doctype;
8319: FROM gms_bc_packets pkt,
8320: gl_bc_packets gl,
8321: ap_invoice_distributions_all ap,
8322: po_distributions_all pod,
8323: gms_award_distributions adl
8324: WHERE pkt.packet_id = x_packet_id
8325: AND pkt.document_type = 'AP'
8326: AND gl.packet_id = pkt.packet_id
8327: AND ROWIDTOCHAR (gl.ROWID) = pkt.gl_bc_packets_rowid