[Home] [Help]
547: p_resource_list_member_id in NUMBER,
548: p_start_date in DATE) is
549:
550: select (nvl(gbal.actual_period_to_date,0) + nvl(gbal.encumb_period_to_date,0))
551: from gms_budget_versions gbv,
552: gms_budget_lines gbl,
553: gms_resource_assignments gra,
554: gms_balances gbal
555: where gbv.budget_version_id = gra.budget_version_id
667: gbl_b.start_date start_date,
668: gbl_b.period_name period_name,
669: gbv_b.budget_entry_method_code budget_entry_method_code,
670: gbv_b.resource_list_id resource_list_id
671: from gms_budget_versions gbv_b,
672: gms_budget_lines gbl_b,
673: gms_resource_assignments gra_b
674: where gbv_b.budget_version_id = gra_b.budget_version_id
675: and gra_b.resource_assignment_id = gbl_b.resource_assignment_id
685: gbl_d.start_date start_date,
686: gbl_d.period_name period_name,
687: gbv_d.budget_entry_method_code budget_entry_method_code,
688: gbv_d.resource_list_id resource_list_id
689: from gms_budget_versions gbv_d,
690: gms_budget_lines gbl_d,
691: gms_resource_assignments gra_d
692: where gbv_d.budget_version_id = gra_d.budget_version_id
693: and gra_d.resource_assignment_id = gbl_d.resource_assignment_id
810: -- Get the project_totals
811: x_err_stage := 'get project totals <' || to_char(x_budget_version_id)
812: || '>';
813:
814: update gms_budget_versions v
815: set (labor_quantity,
816: labor_unit_of_measure,
817: raw_cost,
818: burdened_cost,
932: select trunc(gbl.start_date) start_date,
933: trunc(gbl.end_date) end_date,
934: sum(NVL(gbl.burdened_cost,0)) burdened_cost,
935: decode(sum(NVL(gbl.burdened_cost,0)), abs(sum(NVL(gbl.burdened_cost,0))), 1, 0) N
936: from gms_budget_versions gbv,
937: gms_resource_assignments gra,
938: gms_budget_lines gbl
939: where gbv.budget_version_id = gra.budget_version_id
940: and gra.resource_assignment_id = gbl.resource_assignment_id
958: IS
959: select trunc(gbl.start_date) start_date,
960: trunc(gbl.end_date) end_date,
961: sum(NVL(gbl.burdened_cost,0)) burdened_cost
962: from gms_budget_versions gbv,
963: gms_resource_assignments gra,
964: gms_budget_lines gbl
965: where gbv.budget_version_id = gra.budget_version_id
966: and gra.resource_assignment_id = gbl.resource_assignment_id
1005:
1006: Begin
1007: select nvl(burdened_cost,0)
1008: into l_total_budget_amount
1009: from gms_budget_versions
1010: where -- budget_status_code in ('W','S') commented for the bug 6860267 and added below condition
1011: budget_version_id = x_budget_version_id
1012: and award_id = x_award_id
1013: and project_id = x_project_id;
1054: end if;
1055:
1056: select decode(entry_level_code, 'P','P','T')
1057: into x_entry_level_code
1058: from gms_budget_versions gbv,
1059: pa_budget_entry_methods pbem
1060: where gbv.budget_Version_id = x_budget_version_id
1061: and pbem.budget_entry_method_code = gbv.budget_entry_method_code;
1062:
1088:
1089: select time_phased_type_code, categorization_code
1090: into x_period_type, x_categorization_code
1091: from pa_budget_entry_methods pbem,
1092: gms_budget_versions gbv
1093: where gbv.budget_entry_method_code = pbem.budget_entry_method_code
1094: and gbv.budget_version_id = x_budget_version_id;
1095:
1096: -- Added the IF condition for Bug: 1422606
1309: is
1310: select gbv.budget_version_id
1311: , gbl.start_date
1312: , gbl.end_date
1313: from gms_budget_versions gbv
1314: , gms_resource_assignments gra
1315: , gms_budget_lines gbl
1316: where gbv.budget_version_id = gra.budget_version_id
1317: and gra.resource_assignment_id = gbl.resource_assignment_id
1328: is
1329: select gbv.budget_version_id
1330: , gbl.start_date
1331: , gbl.end_date
1332: from gms_budget_versions gbv
1333: , gms_resource_assignments gra
1334: , gms_budget_lines gbl
1335: where gbv.budget_version_id = gra.budget_version_id
1336: and gra.resource_assignment_id = gbl.resource_assignment_id
1342:
1343: cursor time_phased_type_csr ( p_budget_version_id in NUMBER)
1344: is
1345: select time_phased_type_code
1346: from gms_budget_versions gbv,
1347: pa_budget_entry_methods pbem
1348: where gbv.budget_entry_method_code = pbem.budget_entry_method_code
1349: and gbv.budget_version_id = p_budget_version_id;
1350:
1425: cursor multifunding_csr(p_project_id IN NUMBER,
1426: p_award_id IN NUMBER)
1427: IS
1428: select budget_version_id,budget_entry_method_code
1429: from gms_budget_versions
1430: where project_id = p_project_id
1431: and award_id <> p_award_id
1432: and budget_status_code = 'B'
1433: and current_flag = 'Y';
1435: cursor entry_method_csr (p_project_id IN NUMBER,
1436: p_award_id IN NUMBER)
1437: IS
1438: select budget_entry_method_code
1439: from gms_budget_versions
1440: where project_id = p_project_id
1441: and award_id = p_award_id
1442: and budget_status_code in ('W','S');
1443:
1450: cursor resource_list_csr (p_project_id IN NUMBER,
1451: p_award_id IN NUMBER)
1452: IS
1453: select resource_list_id
1454: from gms_budget_versions
1455: where project_id = p_project_id
1456: and award_id = p_award_id
1457: and budget_status_code in ('W','S');
1458:
1467: cursor baselined_budg_csr (p_project_id IN NUMBER,
1468: p_award_id IN NUMBER)
1469: IS
1470: select budget_version_id
1471: from gms_budget_versions
1472: where budget_status_code = 'B'
1473: and project_id = p_project_id
1474: and award_id = p_award_id;
1475:
1635: , p_budget_type_code VARCHAR2 )
1636: IS
1637: SELECT budget_version_id
1638: , budget_status_code
1639: FROM gms_budget_versions
1640: WHERE project_id = p_project_id
1641: AND award_id = p_award_id
1642: AND budget_type_code = p_budget_type_code
1643: AND budget_status_code IN ('W','S');
1644:
1645: CURSOR l_lock_old_budget_csr( p_budget_version_id NUMBER )
1646: IS
1647: SELECT 'x'
1648: FROM gms_budget_versions bv
1649: , gms_resource_assignments ra
1650: , gms_budget_lines bl
1651: WHERE bv.budget_version_id = p_budget_version_id
1652: AND bv.budget_version_id = ra.budget_version_id (+)
2253: -- resource list used in accumulation
2254:
2255: select resource_list_id
2256: into l_baselined_resource_list_id
2257: from gms_budget_versions
2258: where budget_version_id = l_baselined_version_id;
2259:
2260: if (l_resource_list_id <> l_baselined_resource_list_id) then
2261: gms_error_pkg.gms_message(x_err_name => 'GMS_BU_BASE_RES_LIST_EXISTS',
2311: -- if oracle error, return
2312: return;
2313: end if;
2314:
2315: insert into gms_budget_versions(
2316: budget_version_id,
2317: project_id,
2318: award_id,
2319: budget_type_code,
2359: pm_budget_reference,
2360: wf_status_code,
2361: first_budget_period ) -- Bug 3104308
2362: select
2363: gms_budget_versions_s.nextval,
2364: l_project_id,
2365: l_award_id,
2366: p_budget_type_code,
2367: 1,
2634: , p_budget_type_code VARCHAR2 )
2635:
2636: IS
2637: SELECT budget_version_id, budget_status_code
2638: FROM gms_budget_versions
2639: WHERE project_id = p_project_id
2640: AND award_id = p_award_id
2641: AND budget_type_code = p_budget_type_code
2642: AND budget_status_code in ('W','S');
2649: , p_budget_type_code VARCHAR2 )
2650:
2651: IS
2652: SELECT 1
2653: FROM gms_budget_versions
2654: WHERE project_id = p_project_id
2655: AND award_id = p_award_id
2656: AND budget_type_code = p_budget_type_code
2657: AND budget_status_code = 'B';
2666: SELECT v.resource_list_id,
2667: t.project_type_class_code
2668: FROM pa_project_types t,
2669: pa_projects p,
2670: gms_budget_versions v
2671: WHERE v.budget_version_id = p_draft_version_id
2672: AND v.project_id = p.project_id
2673: AND p.project_type = t.project_type;
2674:
2681: prl.group_resource_type_id
2682: FROM pa_project_types t,
2683: pa_projects p,
2684: pa_resource_lists prl,
2685: gms_budget_versions v
2686: WHERE v.budget_version_id = p_draft_version_id
2687: AND v.project_id = p.project_id
2688: AND prl.resource_list_id = v.resource_list_id
2689: AND p.project_type = t.project_type;
2690:
2691: CURSOR l_time_phased_type_csr(p_budget_version_id IN NUMBER)
2692: IS
2693: SELECT pbem.time_phased_type_code, pbem.entry_level_code
2694: FROM gms_budget_versions gbv,
2695: pa_budget_entry_methods pbem
2696: WHERE gbv.budget_version_id = p_budget_version_id
2697: AND gbv.budget_entry_method_code = pbem.budget_entry_method_code;
2698:
2729:
2730: CURSOR l_lock_budget_csr (p_budget_version_id NUMBER)
2731: IS
2732: SELECT 'x'
2733: FROM gms_budget_versions
2734: WHERE budget_version_id = p_budget_version_id
2735: FOR UPDATE NOWAIT;
2736:
2737: ------------------------------------------------------------------------------
2748: l_baselined_version_id NUMBER;
2749: l_prev_baselined_version_id NUMBER;
2750: l_budget_status_code VARCHAR2(30);
2751: l_time_phased_type_code VARCHAR2(1);
2752: l_mark_as_original gms_budget_versions.current_original_flag%TYPE;
2753: i NUMBER;
2754: l_row_found NUMBER;
2755: l_function_allowed VARCHAR2(1);
2756: l_resp_id NUMBER := 0;
3164: ----------------------------------------------------------------------------
3165: ----------------------------------------------------------------------------
3166: -- Conditional Funds Checking.
3167: -- if is_fc_required returns TRUE then
3168: -- update GMS_BUDGET_VERSIONS table and set FC_REQUIRED_FLAG = 'Y'
3169: -- The Funds Checking process is run only if this flag is 'Y'
3170: ----------------------------------------------------------------------------
3171:
3172: if is_fc_required (p_project_id => l_project_id,
3175: IF L_DEBUG = 'Y' THEN
3176: gms_error_pkg.gms_debug('GMS_BUDGET_PUB.BASELINE_BUDGET - Call to is_fc_required returned TRUE ', 'C');
3177: END IF;
3178:
3179: UPDATE gms_budget_versions
3180: SET fc_required_flag = 'Y'
3181: WHERE award_id = l_award_id
3182: AND project_id = l_project_id
3183: AND budget_type_code = p_budget_type_code
3186: IF L_DEBUG = 'Y' THEN
3187: gms_error_pkg.gms_debug('GMS_BUDGET_PUB.BASELINE_BUDGET - Call to is_fc_required returned FALSE ', 'C');
3188: END IF;
3189:
3190: UPDATE gms_budget_versions
3191: SET fc_required_flag = 'N'
3192: WHERE award_id = l_award_id
3193: AND project_id = l_project_id
3194: AND budget_type_code = p_budget_type_code
3331: -- when the client extension returns 'T',
3332: -- the baseline action will be skipped here, since the baselining is done later
3333: -- by the baseliner as part of the workflow process.
3334:
3335: UPDATE gms_budget_versions
3336: SET --budget_status_code = 'S',
3337: WF_status_code = 'IN_ROUTE',
3338: conc_request_id = l_conc_request_id
3339: WHERE budget_version_id = l_budget_version_id;
3422: IF L_DEBUG = 'Y' THEN
3423: gms_error_pkg.gms_debug('GMS_BUDGET_PUB.BASELINE_BUDGET - Updating Budget to Submitted status', 'C');
3424: END IF;
3425:
3426: UPDATE gms_budget_versions
3427: SET budget_status_code = 'S',
3428: WF_status_code = NULL,
3429: conc_request_id = l_conc_request_id,
3430: current_original_flag = l_mark_as_original -- Added for Bug:1578992
3515: select bv.budget_version_id,
3516: bem.entry_level_code
3517: into l_prev_baselined_version_id,
3518: l_prev_entry_level_code
3519: from gms_budget_versions bv,
3520: pa_budget_entry_methods bem
3521: where bv.award_id = l_award_id
3522: and bv.project_id = l_project_id
3523: and bv.budget_type_code = p_budget_type_code
3612: IF L_DEBUG = 'Y' THEN
3613: gms_error_pkg.gms_debug('GMS_BUDGET_PUB.BASELINE_BUDGET - Updating Budget to Working status', 'C');
3614: END IF;
3615:
3616: UPDATE gms_budget_versions
3617: SET budget_status_code = 'W', conc_request_id = l_conc_request_id
3618: WHERE budget_version_id = l_budget_version_id;
3619:
3620: -- 29-May-2000------------------------------------------------------------------------------------
3632: IF L_DEBUG = 'Y' THEN
3633: gms_error_pkg.gms_debug('GMS_BUDGET_PUB.BASELINE_BUDGET - Updating current flag to N on previous baselined budget version', 'C');
3634: END IF;
3635:
3636: update gms_budget_versions
3637: set current_flag = 'N'
3638: where award_id = l_award_id
3639: and project_id = l_project_id
3640: and budget_type_code = p_budget_type_code
3648: IF L_DEBUG = 'Y' THEN
3649: gms_error_pkg.gms_debug('GMS_BUDGET_PUB.BASELINE_BUDGET - Updating current flag to Y on newly created budget version', 'C');
3650: END IF;
3651:
3652: update gms_budget_versions
3653: set current_flag = 'Y'
3654: where budget_version_id = ( select max(budget_version_id)
3655: from gms_budget_versions
3656: where award_id = l_award_id
3651:
3652: update gms_budget_versions
3653: set current_flag = 'Y'
3654: where budget_version_id = ( select max(budget_version_id)
3655: from gms_budget_versions
3656: where award_id = l_award_id
3657: and project_id = l_project_id
3658: and budget_type_code = p_budget_type_code);
3659:
3665:
3666: begin
3667: select budget_version_id
3668: into l_baselined_version_id
3669: from gms_budget_versions
3670: where award_id = l_award_id
3671: and project_id = l_project_id
3672: and budget_type_code = p_budget_type_code
3673: and budget_status_code = 'B'
3741: gms_error_pkg.gms_debug('GMS_BUDGET_PUB.BASELINE_BUDGET - Updating current flag to Y on previous baselined budget version', 'C');
3742: END IF;
3743:
3744: -- Bug 2386041
3745: update gms_budget_versions
3746: set current_flag = 'Y'
3747: where budget_version_id = l_prev_baselined_version_id;
3748:
3749: IF L_DEBUG = 'Y' THEN
3749: IF L_DEBUG = 'Y' THEN
3750: gms_error_pkg.gms_debug('GMS_BUDGET_PUB.BASELINE_BUDGET - Updating current flag to N on newly baselined budget version id', 'C');
3751: END IF;
3752:
3753: update gms_budget_versions
3754: set current_flag = 'N'
3755: where budget_version_id = l_baselined_version_id;
3756:
3757: IF L_DEBUG = 'Y' THEN
3757: IF L_DEBUG = 'Y' THEN
3758: gms_error_pkg.gms_debug('GMS_BUDGET_PUB.BASELINE_BUDGET - Updating budget to submitted status', 'C');
3759: END IF;
3760:
3761: UPDATE gms_budget_versions
3762: SET budget_status_code = 'S'
3763: WHERE budget_version_id = l_budget_version_id;
3764:
3765: commit;
3872: gms_error_pkg.gms_debug('GMS_BUDGET_PUB.BASELINE_BUDGET - Updating current flag to Y on previous baselined budget version', 'C');
3873: END IF;
3874:
3875: -- Bug 2386041
3876: update gms_budget_versions
3877: set current_flag = 'Y'
3878: where budget_version_id = l_prev_baselined_version_id;
3879:
3880: IF L_DEBUG = 'Y' THEN
3880: IF L_DEBUG = 'Y' THEN
3881: gms_error_pkg.gms_debug('GMS_BUDGET_PUB.BASELINE_BUDGET - Updating current flag to N on newly baselined budget version id', 'C');
3882: END IF;
3883:
3884: update gms_budget_versions
3885: set current_flag = 'N'
3886: where budget_version_id = l_baselined_version_id;
3887:
3888: IF L_DEBUG = 'Y' THEN
3888: IF L_DEBUG = 'Y' THEN
3889: gms_error_pkg.gms_debug('GMS_BUDGET_PUB.BASELINE_BUDGET - Updating budget to submitted status', 'C');
3890: END IF;
3891:
3892: UPDATE gms_budget_versions
3893: SET budget_status_code = 'S'
3894: WHERE budget_version_id = l_budget_version_id;
3895:
3896: commit;
3989: IF L_DEBUG = 'Y' THEN
3990: gms_error_pkg.gms_debug('GMS_BUDGET_PUB.BASELINE_BUDGET - Updating current_flag to Y on previously baselined budget', 'C');
3991: END IF;
3992:
3993: update gms_budget_versions
3994: set current_flag = 'Y'
3995: where award_id = l_award_id
3996: and project_id = l_project_id
3997: and budget_type_code = p_budget_type_code
4035: END IF;
4036:
4037: -- since Funds check failed the previously baselined budget (whose current_flag was set to 'R' earlier) has to be restored
4038:
4039: update gms_budget_versions
4040: set current_flag = 'Y'
4041: where award_id = l_award_id
4042: and project_id = l_project_id
4043: and budget_type_code = p_budget_type_code
4174: ,p_award_id NUMBER
4175: ,p_budget_type_code VARCHAR2)
4176: IS
4177: SELECT budget_version_id, budget_entry_method_code, resource_list_id
4178: FROM gms_budget_versions
4179: WHERE project_id = p_project_id
4180: AND award_id = p_award_id
4181: AND budget_status_code = 'W';
4182:
4442: END IF;
4443:
4444: -- dbms_output.put_line('GMS_BUDGET_PUB.ADD_BUDGET_LINE - after budget_amount_code_csr');
4445:
4446: -- Get the budget_version, budget_entry_method_code and resource_list_id from table gms_budget_versions
4447:
4448: OPEN l_budget_version_csr(l_project_id, l_award_id, p_budget_type_code);
4449: FETCH l_budget_version_csr
4450: INTO l_budget_version_id
4464: CLOSE l_budget_version_csr;
4465:
4466: -- dbms_output.put_line('GMS_BUDGET_PUB.ADD_BUDGET_LINE - after budget_version_csr');
4467:
4468: -- entry method code is mandatory (and a nullible field in table gms_budget_versions)
4469:
4470: IF l_budget_entry_method_code IS NULL
4471: THEN
4472: gms_error_pkg.gms_message(x_err_name => 'GMS_ENTRY_METHOD_IS_MISSING',
5035: END IF;
5036:
5037: end if; -- Time Phased type code
5038:
5039: -- summarizing the totals in the table gms_budget_versions
5040:
5041: GMS_BUDGET_PUB.summerize_project_totals( x_budget_version_id => l_budget_version_id
5042: , x_err_code => x_err_code
5043: , x_err_stage => x_err_stage
5145: , p_award_id NUMBER
5146: , p_budget_type_code VARCHAR2 )
5147: IS
5148: SELECT budget_version_id
5149: FROM gms_budget_versions
5150: WHERE project_id = p_project_id
5151: AND award_id = p_award_id
5152: AND budget_type_code = p_budget_type_code
5153: AND budget_status_code in ('W','S');-- Bug 1831122
5162:
5163: CURSOR l_lock_budget_csr( p_budget_version_id NUMBER )
5164: IS
5165: SELECT 'x'
5166: FROM gms_budget_versions bv
5167: , gms_resource_assignments ra
5168: , gms_budget_lines bl
5169: WHERE bv.budget_version_id = p_budget_version_id
5170: AND bv.budget_version_id = ra.budget_version_id (+)
5448:
5449: end loop;
5450:
5451: begin
5452: delete gms_budget_versions
5453: where budget_version_id = l_budget_version_id;
5454: exception
5455: when NO_DATA_FOUND
5456: then
5526: IS
5527: SELECT budget_version_id
5528: , resource_list_id
5529: , budget_entry_method_code
5530: FROM gms_budget_versions
5531: WHERE project_id = p_project_id
5532: AND award_id = p_award_id
5533: AND budget_type_code = p_budget_type_code
5534: AND budget_status_code in ('W','S');--Bug 1831122
6057: (select 1
6058: from gms_budget_lines
6059: where resource_assignment_id = l_resource_assignment_id);
6060:
6061: -- Update gms_budget_versions only if the denormalized totals are
6062: -- not being maintained in the form. Example the Copy Actual
6063: -- process.
6064:
6065: update gms_budget_versions
6061: -- Update gms_budget_versions only if the denormalized totals are
6062: -- not being maintained in the form. Example the Copy Actual
6063: -- process.
6064:
6065: update gms_budget_versions
6066: set raw_cost = pa_currency.round_currency_amt(nvl(raw_cost,0) - nvl(l_raw_cost,0) ),
6067: burdened_cost = pa_currency.round_currency_amt(nvl(burdened_cost,0) - nvl(l_burdened_cost,0) ),
6068: labor_quantity = (to_number(
6069: decode(l_track_as_labor_flag,
6110:
6111: CLOSE l_budget_line_rowid_csr;
6112:
6113:
6114: --summarizing the totals in the table gms_budget_versions
6115:
6116: GMS_BUDGET_PUB.summerize_project_totals( x_budget_version_id => l_budget_version_id
6117: , x_err_code => x_err_code
6118: , x_err_stage => x_err_stage
6270: , budget_entry_method_code
6271: , resource_list_id
6272: , change_reason_code
6273: , description
6274: FROM gms_budget_versions
6275: WHERE project_id = p_project_id
6276: AND award_id = p_award_id
6277: AND budget_type_code = p_budget_type_code
6278: AND budget_status_code in ('W','S');
6338:
6339: CURSOR l_lock_budget_csr( p_budget_version_id NUMBER )
6340: IS
6341: SELECT 'x'
6342: FROM gms_budget_versions bv
6343: , gms_resource_assignments ra
6344: , gms_budget_lines bl
6345: WHERE bv.budget_version_id = p_budget_version_id
6346: AND bv.budget_version_id = ra.budget_version_id (+)
6680: p_resource_list_id => p_resource_list_id);
6681:
6682: END IF;
6683:
6684: -- Once the above validations have passed update the GMS_BUDGET_VERSIONS table
6685: -- with the appropriate values.
6686:
6687: -- Bug 3104308 : Validation for p_first_budget_period .This will be fired only for
6688: -- GL/PA budget periods.
6703: END IF;
6704:
6705: OPEN l_lock_budget_csr( l_budget_version_id );
6706:
6707: UPDATE GMS_BUDGET_VERSIONS
6708: SET change_reason_code = decode(p_change_reason_code,
6709: GMS_BUDGET_PUB.G_MISS_CHAR,
6710: change_reason_code,
6711: p_change_reason_code),
6829: END IF;
6830:
6831: end if; -- l_time_phased_type_code
6832:
6833: --summarizing the totals in the table gms_budget_versions
6834:
6835: gms_budget_pub.summerize_project_totals( x_budget_version_id => l_budget_version_id
6836: , x_err_code => x_err_code
6837: , x_err_stage => x_err_stage
6988: IS
6989: SELECT budget_version_id
6990: , budget_entry_method_code
6991: , resource_list_id
6992: FROM gms_budget_versions
6993: WHERE project_id = p_project_id
6994: AND award_id = p_award_id
6995: AND budget_type_code = p_budget_type_code
6996: AND budget_status_code IN ('W','S');