[Home] [Help]
49: where budget_version_id = x_budget_version_id
50: for update nowait;
51: ---------------------------------------------------------------------------------------------
52: -- procedure to re-create the gms_balance records from GMS_BUDGET_LINES
53: -- and to clean up GMS_BC_PACKETs
54: -- Added x_project_id and x_award_id parameter : ported Bug 1703510
55:
56: procedure create_gms_balance( x_budget_version_id in number, x_set_of_books_id in number,
57: x_project_id in number, x_award_id in number) is
60: --Commented the above line for history purposes 11i change.
61:
62: -- Added x_project_id and x_award_id to delete so that it hits N2 index : ported Bug 1703510
63:
64: delete from gms_bc_packets
65: where project_id = x_project_id
66: and award_id = x_award_id
67: and budget_version_id = x_budget_version_id;
68:
121: RETCODE := 'E';
122: ERRBUF := 'NO_DRAFT_BUDGET_BUDGET';
123: end create_gms_balance;
124: -----------------------------------------------------------------------------
125: --Procedure to load all the raw transactions in GMS_BC_PACKETS for funds check
126: procedure create_direct_cost(x_packet_id IN number,
127: x_sob_id in number,
128: x_project_id in number,
129: x_award_id in number,
137: -- ---------------------------------------------------------------
138: --
139: -- Bug : 3362016 Grants integrations with CWK and PO Services.
140: -- sub select added in the from clause to use PO encumbered amount api.
141: insert into gms_bc_packets (
142: packet_id,
143: set_of_books_id,
144: je_source_name,
145: je_category_name,
202: vw.distribution_id,
203: 'Y',
204: 'E',
205: x_budget_version_id,
206: gms_bc_packets_s.nextval,
207: vw.burdenable_raw_cost,
208: vw.vendor_id,
209: vw.expenditure_category,
210: vw.revenue_category_code,
263: -- ---------------------------------------------------------------
264: --
265: -- Bug : 3362016 Grants integrations with CWK and PO Services.
266: -- sub select added in the from clause to use PO encumbered amount api.
267: insert into gms_bc_packets (
268: packet_id,
269: set_of_books_id,
270: je_source_name,
271: je_category_name,
328: vw.po_distribution_id,
329: 'Y',
330: 'E',
331: x_budget_version_id,
332: gms_bc_packets_s.nextval,
333: vw.burdenable_raw_cost,
334: vw.vendor_id, -- Bug 2069132 ( RLMI Change)
335: vw.expenditure_category, -- Bug 2069132 ( RLMI Change)
336: vw.revenue_category_code, -- Bug 2069132 ( RLMI Change)
398: -- ---------------------------------------------------------------
399: -- Bug Fix 2170878. Removed invoice_distribution_id join.In some
400: -- scenarios id is null on ad
401: -- ---------------------------------------------------------------
402: insert into gms_bc_packets (
403: packet_id,
404: set_of_books_id,
405: je_source_name,
406: je_category_name,
481: aid.invoice_distribution_id, -- AP Lines change
482: 'Y',
483: 'E',
484: x_budget_version_id,
485: gms_bc_packets_s.nextval,
486: adl.burdenable_raw_cost,
487: ap.vendor_id,
488: pet.expenditure_category,
489: pet.revenue_category_code,
543: begin
544: -- ---------------------------------------------------------------
545: -- TO INSERT Expenditures and Encumberances
546: -- ---------------------------------------------------------------
547: insert into gms_bc_packets (
548: PACKET_ID,
549: PROJECT_ID,
550: AWARD_ID,
551: TASK_ID,
618: 'E',
619: decode(sign(pc.amount),1,pc.amount,0),
620: decode(sign(pc.amount),-1,ABS(pc.amount),0),
621: x_budget_version_id,
622: gms_bc_packets_s.nextval,
623: adl.burdenable_raw_cost,
624: pa.incurred_by_person_id, -- Bug 2069132 ( RLMI Change)
625: pe.job_id, -- Bug 2069132 ( RLMI Change)
626: pc.system_reference1, -- Bug 2069132 ( RLMI Change)
721: -- ---------------------------------------------------------------------------------------------
722:
723: begin
724:
725: insert into gms_bc_packets (
726: PACKET_ID,
727: PROJECT_ID,
728: AWARD_ID,
729: TASK_ID,
789: -- Bug 1980810 PA Rounding function added
790: pa_currency.round_currency_amt(decode(sign(gei.amount),1,gei.amount,0)),
791: pa_currency.round_currency_amt(decode(sign(gei.amount),-1,-1*gei.amount,0)),
792: x_budget_version_id,
793: gms_bc_packets_s.nextval,
794: adl.burdenable_raw_cost,
795: ge.incurred_by_person_id, -- Bug 2069132 ( RLMI Change)
796: gei.job_id, -- Bug 2069132 ( RLMI Change)
797: pet.expenditure_category, -- Bug 2069132 ( RLMI Change)
840: raise;
841: end;
842: END create_direct_cost;
843: ---------------------------------------------------------------------------------------------
844: -- Procedure to create indirect cost lines in GMS_BC_PACKETS from the raw cost lines
845: -- in GMS_BC_PACKETS for a given packet;
846: procedure create_indirect_cost(x_packet_id IN number) IS
847: x_err_code number;
848: x_err_buff varchar2(2000);
841: end;
842: END create_direct_cost;
843: ---------------------------------------------------------------------------------------------
844: -- Procedure to create indirect cost lines in GMS_BC_PACKETS from the raw cost lines
845: -- in GMS_BC_PACKETS for a given packet;
846: procedure create_indirect_cost(x_packet_id IN number) IS
847: x_err_code number;
848: x_err_buff varchar2(2000);
849: begin
847: x_err_code number;
848: x_err_buff varchar2(2000);
849: begin
850: begin
851: Insert into gms_bc_packets
852: ( PACKET_ID,
853: PROJECT_ID,
854: AWARD_ID,
855: TASK_ID,
892: vendor_id, -- Bug 2069132 ( RLMI Change)
893: expenditure_category, -- Bug 2069132 ( RLMI Change)
894: revenue_category -- Bug 2069132 ( RLMI Change)
895: )
896: select /*+ index(gbc GMS_BC_PACKETS_N1) */ --Added the index hint for bug 5689194
897: gbc.PACKET_ID,
898: gbc.PROJECT_ID,
899: gbc.AWARD_ID,
900: gbc.TASK_ID,
930: gbc.OVERRIDE_AMOUNT,
931: gbc.EFFECT_ON_FUNDS_CODE ,
932: gbc.RESULT_CODE,
933: gbc.GL_BC_PACKETS_ROWID,
934: gms_bc_packets_s.nextval,
935: gbc.BC_PACKET_ID,
936: gbc.person_id, -- Bug 2069132 ( RLMI Change)
937: gbc.job_id, -- Bug 2069132 ( RLMI Change)
938: gbc.vendor_id, -- Bug 2069132 ( RLMI Change)
946: --pa_ind_rate_schedules_all_bg irs, --Bug 3630704 : Performance fix
947: pa_cost_base_cost_codes cbcc, /*for performance fix bug 5569067 */
948: /*pa_ind_compiled_sets ics, --for performance fix bug 5569067 */
949: pa_compiled_multipliers cm,
950: gms_bc_packets gbc
951: where gbc.document_type in ('REQ','PO','AP', 'ENC') -- perf bug 4005086. included 'ENC' here
952: and cbcc.cost_plus_structure = cbet.cost_plus_structure
953: /*and irsr.cost_plus_structure = cbet.cost_plus_structure bug 5569067 */
954: --and cb.cost_base = cbet.cost_base --Bug 3630704 : Performance fix
984: X_Err_Buff => X_Err_Buff);
985: raise;
986: end;
987: Begin
988: Insert into gms_bc_packets
989: ( PACKET_ID,
990: PROJECT_ID,
991: AWARD_ID,
992: TASK_ID,
1029: vendor_id, -- Bug 2069132 ( RLMI Change)
1030: expenditure_category, -- Bug 2069132 ( RLMI Change)
1031: revenue_category -- Bug 2069132 ( RLMI Change)
1032: )
1033: select /*+ index(gbc GMS_BC_PACKETS_N1) */ --Added the index hint for bug 5689194
1034: gbc.PACKET_ID,
1035: gbc.PROJECT_ID,
1036: gbc.AWARD_ID,
1037: gbc.TASK_ID,
1067: gbc.OVERRIDE_AMOUNT,
1068: gbc.EFFECT_ON_FUNDS_CODE ,
1069: gbc.RESULT_CODE,
1070: gbc.GL_BC_PACKETS_ROWID,
1071: gms_bc_packets_s.nextval,
1072: gbc.BC_PACKET_ID,
1073: gbc.person_id, -- Bug 2069132 ( RLMI Change)
1074: gbc.job_id, -- Bug 2069132 ( RLMI Change)
1075: gbc.vendor_id, -- Bug 2069132 ( RLMI Change)
1085: --pa_ind_compiled_sets ics, /* commented for bug 5689194 */
1086: pa_compiled_multipliers cm,
1087: pa_expenditure_items_all ei, --Bug Fix 1482377
1088: pa_transaction_sources pts, --Bug Fix 1482377
1089: gms_bc_packets gbc
1090: where gbc.document_type = 'EXP'
1091: and cbcc.cost_plus_structure = cbet.cost_plus_structure -- Bug 5689194
1092: --and cb.cost_base = cbet.cost_base --Bug 3630704 : Performance fix
1093: --and cb.cost_base_type = cbet.cost_base_type --Bug 3630704 : Performance fix
1132: -- ------------
1133: -- Encumbrances
1134: -- ------------
1135: begin
1136: Insert into gms_bc_packets
1137: ( PACKET_ID,
1138: PROJECT_ID,
1139: AWARD_ID,
1140: TASK_ID,
1215: gbc.OVERRIDE_AMOUNT,
1216: gbc.EFFECT_ON_FUNDS_CODE ,
1217: gbc.RESULT_CODE,
1218: gbc.GL_BC_PACKETS_ROWID,
1219: gms_bc_packets_s.nextval,
1220: gbc.BC_PACKET_ID,
1221: gbc.person_id, -- Bug 2069132 ( RLMI Change)
1222: gbc.job_id, -- Bug 2069132 ( RLMI Change)
1223: gbc.vendor_id, -- Bug 2069132 ( RLMI Change)
1230: pa_cost_base_exp_types cbet,
1231: --pa_ind_rate_schedules_all_bg irs, --Bug 3630704 : Performance fix
1232: pa_ind_compiled_sets ics,
1233: pa_compiled_multipliers cm,
1234: gms_bc_packets gbc
1235: where irsr.cost_plus_structure = cbet.cost_plus_structure
1236: --and cb.cost_base = cbet.cost_base --Bug 3630704 : Performance fix
1237: --and cb.cost_base_type = cbet.cost_base_type --Bug 3630704 : Performance fix
1238: and ics.cost_base = cbet.cost_base --Bug 3003584
1307: x_bc_packet_id number;
1308: x_fcl varchar2(1);
1309: x_bc_option_id number(15);
1310: x_categorization_code varchar2(1);
1311: l_budget_version_id gms_bc_packets.budget_version_id%TYPE;
1312: l_effect_on_funds_code varchar2(1);
1313: x_group_by_none varchar2(60) ;
1314:
1315: -- ------------------------------------------------------------------------
1333: AND NVL(a.migration_code,'M') ='M'; -- Bug 3626671;
1334:
1335:
1336: Cursor cur_update_col is
1337: select /*+ index(gms GMS_BC_PACKETS_N1) */ gms.bc_packet_id, -- added the index hint for bug 5689194
1338: gms.project_id,
1339: gms.award_id,
1340: gms.task_id,
1341: gms.expenditure_organization_id,
1347: gms.award_id,
1348: gms.parent_bc_packet_id,
1349: pm.categorization_code, -- to calculate the correct rlmi if budget without resource
1350: decode(sign(nvl(gms.entered_dr,0) - nvl(gms.entered_cr,0)),1,'D','I')
1351: from gms_bc_packets gms,
1352: gms_budget_versions bv,
1353: pa_budget_entry_methods pm,
1354: pa_expenditure_types TYPE,
1355: gms_encumbrance_items_all ei
1359: and bv.budget_entry_method_code = pm.budget_entry_method_code
1360: and gms.expenditure_type = TYPE.expenditure_type
1361: and gms.document_header_id = ei.encumbrance_item_id
1362: union all
1363: select /*+ index(gms GMS_BC_PACKETS_N1) */ gms.bc_packet_id, -- added the index hint for bug 5689194
1364: gms.project_id,
1365: gms.award_id,
1366: gms.task_id,
1367: gms.expenditure_organization_id,
1373: gms.award_id,
1374: gms.parent_bc_packet_id,
1375: pm.categorization_code, -- to calculate the correct rlmi if budget without resource
1376: decode(sign(nvl(gms.entered_dr,0) - nvl(gms.entered_cr,0)),1,'D','I')
1377: from gms_bc_packets gms,
1378: gms_budget_versions bv,
1379: pa_budget_entry_methods pm,
1380: pa_expenditure_types TYPE,
1381: pa_expenditure_items_all ei
1435: SELECT DISTINCT line.vendor_id
1436: INTO x_vendor_id
1437: FROM po_requisition_lines line,
1438: po_requisition_headers req,
1439: gms_bc_packets bc
1440: WHERE bc.packet_id = x_packet_id
1441: AND line.requisition_header_id = req.requisition_header_id
1442: AND bc.document_header_id = req.requisition_header_id
1443: AND bc.bc_packet_id = x_bc_packet_id;
1446: g_error_stage := 'VI-PO';
1447: SELECT DISTINCT head.vendor_id
1448: INTO x_vendor_id
1449: FROM po_headers_all head,
1450: gms_bc_packets bc
1451: WHERE bc.packet_id = x_packet_id
1452: AND bc.document_header_id = head.po_header_id
1453: AND bc.bc_packet_id = x_bc_packet_id;
1454: ELSIF x_system_linkage = 'VI'
1456: g_error_stage := 'VI-AP';
1457: SELECT DISTINCT head.vendor_id
1458: INTO x_vendor_id
1459: FROM ap_invoices_all head,
1460: gms_bc_packets bc
1461: WHERE bc.packet_id = x_packet_id
1462: AND bc.document_header_id = head.invoice_id
1463: AND bc.bc_packet_id = x_bc_packet_id;
1464: ELSIF ( x_system_linkage = 'ER'
1471: INTO x_person_id,
1472: x_job_id
1473: FROM pa_expenditures_all exp,
1474: pa_expenditure_items_all item,
1475: gms_bc_packets bc
1476: WHERE bc.packet_id = x_packet_id
1477: AND bc.bc_packet_id = x_bc_packet_id
1478: AND bc.document_header_id = item.expenditure_item_id
1479: AND item.expenditure_id = EXP.expenditure_id;
1484: INTO x_person_id,
1485: x_job_id
1486: FROM gms_encumbrances_all enc,
1487: gms_encumbrance_items_all item,
1488: gms_bc_packets bc
1489: WHERE bc.packet_id = x_packet_id
1490: AND bc.bc_packet_id = x_bc_packet_id
1491: AND bc.document_header_id = item.encumbrance_item_id
1492: AND item.encumbrance_id = enc.encumbrance_id;
1498: tp.attribute3
1499: INTO x_non_labor_resource,
1500: x_non_labor_resource_org_id
1501: FROM pa_expenditure_types tp,
1502: gms_bc_packets bc
1503: WHERE bc.packet_id = x_packet_id
1504: AND bc.bc_packet_id = x_bc_packet_id
1505: AND tp.expenditure_type = bc.expenditure_type;
1506: ELSIF x_document_type = 'EXP' THEN
1514: x_non_labor_resource,
1515: x_non_labor_resource_org_id
1516: FROM pa_expenditures_all exp,
1517: pa_expenditure_items_all item,
1518: gms_bc_packets bc
1519: WHERE bc.packet_id = x_packet_id
1520: AND bc.bc_packet_id = x_bc_packet_id
1521: AND bc.document_header_id = item.expenditure_item_id
1522: AND item.expenditure_id = EXP.expenditure_id;
1591: x_exec_type => 'C',
1592: x_err_code => x_err_code,
1593: x_err_buff => x_err_buff );
1594:
1595: UPDATE gms_bc_packets
1596: SET status_code = 'R',
1597: result_code = 'F94',
1598: res_result_code = 'F94',
1599: res_grp_result_code = 'F94',
1607: --To update effect on funds code,resource list member id
1608: -- for each record in a packet, if categorized by resource.
1609: ----------------------------------------------------------
1610:
1611: UPDATE gms_bc_packets
1612: SET resource_list_member_id = x_resource_list_member_id,
1613: effect_on_funds_code = l_effect_on_funds_code
1614: WHERE packet_id = x_packet_id
1615: AND bc_packet_id = x_bc_packet_id
1638: --To update effect on funds code,resource list member id
1639: -- for each record in a packet, if not categorized by resource.
1640: ----------------------------------------------------------
1641: IF x_resource_list_member_id IS NULL THEN
1642: UPDATE gms_bc_packets
1643: SET status_code = 'R',
1644: result_code = 'F94',
1645: res_result_code = 'F94',
1646: res_grp_result_code = 'F94',
1652:
1653: x_err_buff := x_er_stage;
1654: --('After Resource Mapping Process');
1655: ELSE
1656: UPDATE gms_bc_packets
1657: SET resource_list_member_id = x_resource_list_member_id,
1658: effect_on_funds_code = l_effect_on_funds_code
1659: WHERE packet_id = x_packet_id
1660: AND bc_packet_id = x_bc_packet_id
1673: x_exec_type => 'C',
1674: x_err_code => x_err_code,
1675: x_err_buff => x_err_buff );
1676:
1677: Update gms_bc_packets
1678: set status_code = 'T',
1679: result_code = 'F82',
1680: res_result_code = 'F82',
1681: res_grp_result_code = 'F82',
1705: x_exec_type => 'C',
1706: x_err_code => x_err_code,
1707: x_err_buff => x_err_buff );
1708:
1709: Update gms_bc_packets
1710: set status_code = 'T',
1711: result_code = 'F100',
1712: res_result_code = 'F100',
1713: res_grp_result_code = 'F100',
1727: END re_base_setup_rlmi;
1728: ---------------------------------------------------------------------------------------------
1729: procedure update_bc_packet_status(x_packet_id in number) is
1730: begin
1731: update gms_bc_packets
1732: set status_code = 'A'
1733: where packet_id = x_packet_id
1734: and status_code = 'P';
1735: exception
1746: PROCEDURE create_act_enc_gms_balances(x_budget_version_id number,
1747: x_base_budget_version_id number) IS
1748: BEGIN
1749: DELETE FROM gms_balances WHERE budget_version_id = x_budget_version_id;
1750: DELETE FROM gms_bc_packets WHERE budget_version_id = x_budget_version_id;
1751: --
1752: -- Insert Actuals and Encumbrance rows into GMS_BALANCES copied
1753: -- from the previous budget version in GMS_BALANCES
1754:
1804: PROCEDURE sweep_baselined_budget(x_base_budget_version_id number) IS
1805: --
1806: CURSOR get_pacid_cur(p_budget_version_id number) IS
1807: SELECT packet_id
1808: FROM gms_bc_packets
1809: WHERE budget_version_id = p_budget_version_id;
1810: --
1811: BEGIN
1812: FOR get_pacid_cur_var in get_pacid_cur(x_base_budget_version_id)
1986:
1987:
1988: -- If GMS:Update Actual and Encumbrance balance conc. process is
1989: -- not run then the actuals and encumbrances should be picked
1990: -- from GMS_BC_PACKETS otherwise the actuals and encumbrances should
1991: -- be picked from GMS_BALANCES since it will no longer be in
1992: -- GMS_BC_PACKETS.
1993:
1994: begin
1988: -- If GMS:Update Actual and Encumbrance balance conc. process is
1989: -- not run then the actuals and encumbrances should be picked
1990: -- from GMS_BC_PACKETS otherwise the actuals and encumbrances should
1991: -- be picked from GMS_BALANCES since it will no longer be in
1992: -- GMS_BC_PACKETS.
1993:
1994: begin
1995: SELECT 1 into x_dummy from dual
1996: where exists (select 'x'
1993:
1994: begin
1995: SELECT 1 into x_dummy from dual
1996: where exists (select 'x'
1997: FROM gms_bc_packets
1998: WHERE project_id = x_project_id
1999: AND award_id = x_award_id
2000: AND budget_version_id = x_base_bud_version_id
2001: AND status_code ='A');
2049: IF L_DEBUG = 'Y' THEN
2050: gms_error_pkg.gms_debug('gms_budget_balance -5','C');
2051: END IF;
2052: select gl_bc_packets_s.nextval into x_packet_id from dual;
2053: -- Create raw transactions in GMS_BC_PACKETS
2054: x_e_stage := '600';
2055: IF L_DEBUG = 'Y' THEN
2056: gms_error_pkg.gms_debug('gms_budget_balance -6','C');
2057: END IF;
2059: x_sob_id ,
2060: x_project_id,
2061: x_award_id,
2062: x_budget_version_id);
2063: -- Create burden transactions in GMS_BC_PACKETS
2064: x_e_stage := '700';
2065: create_indirect_cost(x_packet_id);
2066: -------------------------------------------------------------
2067: Begin
2070: END IF;
2071: x_e_stage := '800';
2072: begin
2073: select count(packet_id) into x_run
2074: from gms_bc_packets
2075: where packet_id = x_packet_id
2076: and rownum < 2;
2077: exception
2078: when no_data_found then