[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
1308: is
1309: select gbv.budget_version_id
1310: , gbl.start_date
1311: , gbl.end_date
1312: from gms_budget_versions gbv
1313: , gms_resource_assignments gra
1314: , gms_budget_lines gbl
1315: where gbv.budget_version_id = gra.budget_version_id
1316: and gra.resource_assignment_id = gbl.resource_assignment_id
1327: is
1328: select gbv.budget_version_id
1329: , gbl.start_date
1330: , gbl.end_date
1331: from gms_budget_versions gbv
1332: , gms_resource_assignments gra
1333: , gms_budget_lines gbl
1334: where gbv.budget_version_id = gra.budget_version_id
1335: and gra.resource_assignment_id = gbl.resource_assignment_id
1341:
1342: cursor time_phased_type_csr ( p_budget_version_id in NUMBER)
1343: is
1344: select time_phased_type_code
1345: from gms_budget_versions gbv,
1346: pa_budget_entry_methods pbem
1347: where gbv.budget_entry_method_code = pbem.budget_entry_method_code
1348: and gbv.budget_version_id = p_budget_version_id;
1349:
1424: cursor multifunding_csr(p_project_id IN NUMBER,
1425: p_award_id IN NUMBER)
1426: IS
1427: select budget_version_id,budget_entry_method_code
1428: from gms_budget_versions
1429: where project_id = p_project_id
1430: and award_id <> p_award_id
1431: and budget_status_code = 'B'
1432: and current_flag = 'Y';
1434: cursor entry_method_csr (p_project_id IN NUMBER,
1435: p_award_id IN NUMBER)
1436: IS
1437: select budget_entry_method_code
1438: from gms_budget_versions
1439: where project_id = p_project_id
1440: and award_id = p_award_id
1441: and budget_status_code in ('W','S');
1442:
1449: cursor resource_list_csr (p_project_id IN NUMBER,
1450: p_award_id IN NUMBER)
1451: IS
1452: select resource_list_id
1453: from gms_budget_versions
1454: where project_id = p_project_id
1455: and award_id = p_award_id
1456: and budget_status_code in ('W','S');
1457:
1466: cursor baselined_budg_csr (p_project_id IN NUMBER,
1467: p_award_id IN NUMBER)
1468: IS
1469: select budget_version_id
1470: from gms_budget_versions
1471: where budget_status_code = 'B'
1472: and project_id = p_project_id
1473: and award_id = p_award_id;
1474:
1634: , p_budget_type_code VARCHAR2 )
1635: IS
1636: SELECT budget_version_id
1637: , budget_status_code
1638: FROM gms_budget_versions
1639: WHERE project_id = p_project_id
1640: AND award_id = p_award_id
1641: AND budget_type_code = p_budget_type_code
1642: AND budget_status_code IN ('W','S');
1643:
1644: CURSOR l_lock_old_budget_csr( p_budget_version_id NUMBER )
1645: IS
1646: SELECT 'x'
1647: FROM gms_budget_versions bv
1648: , gms_resource_assignments ra
1649: , gms_budget_lines bl
1650: WHERE bv.budget_version_id = p_budget_version_id
1651: AND bv.budget_version_id = ra.budget_version_id (+)
2252: -- resource list used in accumulation
2253:
2254: select resource_list_id
2255: into l_baselined_resource_list_id
2256: from gms_budget_versions
2257: where budget_version_id = l_baselined_version_id;
2258:
2259: if (l_resource_list_id <> l_baselined_resource_list_id) then
2260: gms_error_pkg.gms_message(x_err_name => 'GMS_BU_BASE_RES_LIST_EXISTS',
2310: -- if oracle error, return
2311: return;
2312: end if;
2313:
2314: insert into gms_budget_versions(
2315: budget_version_id,
2316: project_id,
2317: award_id,
2318: budget_type_code,
2358: pm_budget_reference,
2359: wf_status_code,
2360: first_budget_period ) -- Bug 3104308
2361: select
2362: gms_budget_versions_s.nextval,
2363: l_project_id,
2364: l_award_id,
2365: p_budget_type_code,
2366: 1,
2633: , p_budget_type_code VARCHAR2 )
2634:
2635: IS
2636: SELECT budget_version_id, budget_status_code
2637: FROM gms_budget_versions
2638: WHERE project_id = p_project_id
2639: AND award_id = p_award_id
2640: AND budget_type_code = p_budget_type_code
2641: AND budget_status_code in ('W','S');
2648: , p_budget_type_code VARCHAR2 )
2649:
2650: IS
2651: SELECT 1
2652: FROM gms_budget_versions
2653: WHERE project_id = p_project_id
2654: AND award_id = p_award_id
2655: AND budget_type_code = p_budget_type_code
2656: AND budget_status_code = 'B';
2665: SELECT v.resource_list_id,
2666: t.project_type_class_code
2667: FROM pa_project_types t,
2668: pa_projects p,
2669: gms_budget_versions v
2670: WHERE v.budget_version_id = p_draft_version_id
2671: AND v.project_id = p.project_id
2672: AND p.project_type = t.project_type;
2673:
2680: prl.group_resource_type_id
2681: FROM pa_project_types t,
2682: pa_projects p,
2683: pa_resource_lists prl,
2684: gms_budget_versions v
2685: WHERE v.budget_version_id = p_draft_version_id
2686: AND v.project_id = p.project_id
2687: AND prl.resource_list_id = v.resource_list_id
2688: AND p.project_type = t.project_type;
2689:
2690: CURSOR l_time_phased_type_csr(p_budget_version_id IN NUMBER)
2691: IS
2692: SELECT pbem.time_phased_type_code, pbem.entry_level_code
2693: FROM gms_budget_versions gbv,
2694: pa_budget_entry_methods pbem
2695: WHERE gbv.budget_version_id = p_budget_version_id
2696: AND gbv.budget_entry_method_code = pbem.budget_entry_method_code;
2697:
2728:
2729: CURSOR l_lock_budget_csr (p_budget_version_id NUMBER)
2730: IS
2731: SELECT 'x'
2732: FROM gms_budget_versions
2733: WHERE budget_version_id = p_budget_version_id
2734: FOR UPDATE NOWAIT;
2735:
2736: ------------------------------------------------------------------------------
2747: l_baselined_version_id NUMBER;
2748: l_prev_baselined_version_id NUMBER;
2749: l_budget_status_code VARCHAR2(30);
2750: l_time_phased_type_code VARCHAR2(1);
2751: l_mark_as_original gms_budget_versions.current_original_flag%TYPE;
2752: i NUMBER;
2753: l_row_found NUMBER;
2754: l_function_allowed VARCHAR2(1);
2755: l_resp_id NUMBER := 0;
3163: ----------------------------------------------------------------------------
3164: ----------------------------------------------------------------------------
3165: -- Conditional Funds Checking.
3166: -- if is_fc_required returns TRUE then
3167: -- update GMS_BUDGET_VERSIONS table and set FC_REQUIRED_FLAG = 'Y'
3168: -- The Funds Checking process is run only if this flag is 'Y'
3169: ----------------------------------------------------------------------------
3170:
3171: if is_fc_required (p_project_id => l_project_id,
3174: IF L_DEBUG = 'Y' THEN
3175: gms_error_pkg.gms_debug('GMS_BUDGET_PUB.BASELINE_BUDGET - Call to is_fc_required returned TRUE ', 'C');
3176: END IF;
3177:
3178: UPDATE gms_budget_versions
3179: SET fc_required_flag = 'Y'
3180: WHERE award_id = l_award_id
3181: AND project_id = l_project_id
3182: AND budget_type_code = p_budget_type_code
3185: IF L_DEBUG = 'Y' THEN
3186: gms_error_pkg.gms_debug('GMS_BUDGET_PUB.BASELINE_BUDGET - Call to is_fc_required returned FALSE ', 'C');
3187: END IF;
3188:
3189: UPDATE gms_budget_versions
3190: SET fc_required_flag = 'N'
3191: WHERE award_id = l_award_id
3192: AND project_id = l_project_id
3193: AND budget_type_code = p_budget_type_code
3330: -- when the client extension returns 'T',
3331: -- the baseline action will be skipped here, since the baselining is done later
3332: -- by the baseliner as part of the workflow process.
3333:
3334: UPDATE gms_budget_versions
3335: SET --budget_status_code = 'S',
3336: WF_status_code = 'IN_ROUTE',
3337: conc_request_id = l_conc_request_id
3338: WHERE budget_version_id = l_budget_version_id;
3421: IF L_DEBUG = 'Y' THEN
3422: gms_error_pkg.gms_debug('GMS_BUDGET_PUB.BASELINE_BUDGET - Updating Budget to Submitted status', 'C');
3423: END IF;
3424:
3425: UPDATE gms_budget_versions
3426: SET budget_status_code = 'S',
3427: WF_status_code = NULL,
3428: conc_request_id = l_conc_request_id,
3429: current_original_flag = l_mark_as_original -- Added for Bug:1578992
3514: select bv.budget_version_id,
3515: bem.entry_level_code
3516: into l_prev_baselined_version_id,
3517: l_prev_entry_level_code
3518: from gms_budget_versions bv,
3519: pa_budget_entry_methods bem
3520: where bv.award_id = l_award_id
3521: and bv.project_id = l_project_id
3522: and bv.budget_type_code = p_budget_type_code
3611: IF L_DEBUG = 'Y' THEN
3612: gms_error_pkg.gms_debug('GMS_BUDGET_PUB.BASELINE_BUDGET - Updating Budget to Working status', 'C');
3613: END IF;
3614:
3615: UPDATE gms_budget_versions
3616: SET budget_status_code = 'W', conc_request_id = l_conc_request_id
3617: WHERE budget_version_id = l_budget_version_id;
3618:
3619: -- 29-May-2000------------------------------------------------------------------------------------
3631: IF L_DEBUG = 'Y' THEN
3632: gms_error_pkg.gms_debug('GMS_BUDGET_PUB.BASELINE_BUDGET - Updating current flag to N on previous baselined budget version', 'C');
3633: END IF;
3634:
3635: update gms_budget_versions
3636: set current_flag = 'N'
3637: where award_id = l_award_id
3638: and project_id = l_project_id
3639: and budget_type_code = p_budget_type_code
3647: IF L_DEBUG = 'Y' THEN
3648: gms_error_pkg.gms_debug('GMS_BUDGET_PUB.BASELINE_BUDGET - Updating current flag to Y on newly created budget version', 'C');
3649: END IF;
3650:
3651: update gms_budget_versions
3652: set current_flag = 'Y'
3653: where budget_version_id = ( select max(budget_version_id)
3654: from gms_budget_versions
3655: where award_id = l_award_id
3650:
3651: update gms_budget_versions
3652: set current_flag = 'Y'
3653: where budget_version_id = ( select max(budget_version_id)
3654: from gms_budget_versions
3655: where award_id = l_award_id
3656: and project_id = l_project_id
3657: and budget_type_code = p_budget_type_code);
3658:
3664:
3665: begin
3666: select budget_version_id
3667: into l_baselined_version_id
3668: from gms_budget_versions
3669: where award_id = l_award_id
3670: and project_id = l_project_id
3671: and budget_type_code = p_budget_type_code
3672: and budget_status_code = 'B'
3740: gms_error_pkg.gms_debug('GMS_BUDGET_PUB.BASELINE_BUDGET - Updating current flag to Y on previous baselined budget version', 'C');
3741: END IF;
3742:
3743: -- Bug 2386041
3744: update gms_budget_versions
3745: set current_flag = 'Y'
3746: where budget_version_id = l_prev_baselined_version_id;
3747:
3748: IF L_DEBUG = 'Y' THEN
3748: IF L_DEBUG = 'Y' THEN
3749: gms_error_pkg.gms_debug('GMS_BUDGET_PUB.BASELINE_BUDGET - Updating current flag to N on newly baselined budget version id', 'C');
3750: END IF;
3751:
3752: update gms_budget_versions
3753: set current_flag = 'N'
3754: where budget_version_id = l_baselined_version_id;
3755:
3756: IF L_DEBUG = 'Y' THEN
3756: IF L_DEBUG = 'Y' THEN
3757: gms_error_pkg.gms_debug('GMS_BUDGET_PUB.BASELINE_BUDGET - Updating budget to submitted status', 'C');
3758: END IF;
3759:
3760: UPDATE gms_budget_versions
3761: SET budget_status_code = 'S'
3762: WHERE budget_version_id = l_budget_version_id;
3763:
3764: commit;
3871: gms_error_pkg.gms_debug('GMS_BUDGET_PUB.BASELINE_BUDGET - Updating current flag to Y on previous baselined budget version', 'C');
3872: END IF;
3873:
3874: -- Bug 2386041
3875: update gms_budget_versions
3876: set current_flag = 'Y'
3877: where budget_version_id = l_prev_baselined_version_id;
3878:
3879: IF L_DEBUG = 'Y' THEN
3879: IF L_DEBUG = 'Y' THEN
3880: gms_error_pkg.gms_debug('GMS_BUDGET_PUB.BASELINE_BUDGET - Updating current flag to N on newly baselined budget version id', 'C');
3881: END IF;
3882:
3883: update gms_budget_versions
3884: set current_flag = 'N'
3885: where budget_version_id = l_baselined_version_id;
3886:
3887: IF L_DEBUG = 'Y' THEN
3887: IF L_DEBUG = 'Y' THEN
3888: gms_error_pkg.gms_debug('GMS_BUDGET_PUB.BASELINE_BUDGET - Updating budget to submitted status', 'C');
3889: END IF;
3890:
3891: UPDATE gms_budget_versions
3892: SET budget_status_code = 'S'
3893: WHERE budget_version_id = l_budget_version_id;
3894:
3895: commit;
3988: IF L_DEBUG = 'Y' THEN
3989: gms_error_pkg.gms_debug('GMS_BUDGET_PUB.BASELINE_BUDGET - Updating current_flag to Y on previously baselined budget', 'C');
3990: END IF;
3991:
3992: update gms_budget_versions
3993: set current_flag = 'Y'
3994: where award_id = l_award_id
3995: and project_id = l_project_id
3996: and budget_type_code = p_budget_type_code
4034: END IF;
4035:
4036: -- since Funds check failed the previously baselined budget (whose current_flag was set to 'R' earlier) has to be restored
4037:
4038: update gms_budget_versions
4039: set current_flag = 'Y'
4040: where award_id = l_award_id
4041: and project_id = l_project_id
4042: and budget_type_code = p_budget_type_code
4173: ,p_award_id NUMBER
4174: ,p_budget_type_code VARCHAR2)
4175: IS
4176: SELECT budget_version_id, budget_entry_method_code, resource_list_id
4177: FROM gms_budget_versions
4178: WHERE project_id = p_project_id
4179: AND award_id = p_award_id
4180: AND budget_status_code = 'W';
4181:
4441: END IF;
4442:
4443: -- dbms_output.put_line('GMS_BUDGET_PUB.ADD_BUDGET_LINE - after budget_amount_code_csr');
4444:
4445: -- Get the budget_version, budget_entry_method_code and resource_list_id from table gms_budget_versions
4446:
4447: OPEN l_budget_version_csr(l_project_id, l_award_id, p_budget_type_code);
4448: FETCH l_budget_version_csr
4449: INTO l_budget_version_id
4463: CLOSE l_budget_version_csr;
4464:
4465: -- dbms_output.put_line('GMS_BUDGET_PUB.ADD_BUDGET_LINE - after budget_version_csr');
4466:
4467: -- entry method code is mandatory (and a nullible field in table gms_budget_versions)
4468:
4469: IF l_budget_entry_method_code IS NULL
4470: THEN
4471: gms_error_pkg.gms_message(x_err_name => 'GMS_ENTRY_METHOD_IS_MISSING',
5034: END IF;
5035:
5036: end if; -- Time Phased type code
5037:
5038: -- summarizing the totals in the table gms_budget_versions
5039:
5040: GMS_BUDGET_PUB.summerize_project_totals( x_budget_version_id => l_budget_version_id
5041: , x_err_code => x_err_code
5042: , x_err_stage => x_err_stage
5144: , p_award_id NUMBER
5145: , p_budget_type_code VARCHAR2 )
5146: IS
5147: SELECT budget_version_id
5148: FROM gms_budget_versions
5149: WHERE project_id = p_project_id
5150: AND award_id = p_award_id
5151: AND budget_type_code = p_budget_type_code
5152: AND budget_status_code in ('W','S');-- Bug 1831122
5161:
5162: CURSOR l_lock_budget_csr( p_budget_version_id NUMBER )
5163: IS
5164: SELECT 'x'
5165: FROM gms_budget_versions bv
5166: , gms_resource_assignments ra
5167: , gms_budget_lines bl
5168: WHERE bv.budget_version_id = p_budget_version_id
5169: AND bv.budget_version_id = ra.budget_version_id (+)
5447:
5448: end loop;
5449:
5450: begin
5451: delete gms_budget_versions
5452: where budget_version_id = l_budget_version_id;
5453: exception
5454: when NO_DATA_FOUND
5455: then
5525: IS
5526: SELECT budget_version_id
5527: , resource_list_id
5528: , budget_entry_method_code
5529: FROM gms_budget_versions
5530: WHERE project_id = p_project_id
5531: AND award_id = p_award_id
5532: AND budget_type_code = p_budget_type_code
5533: AND budget_status_code in ('W','S');--Bug 1831122
6056: (select 1
6057: from gms_budget_lines
6058: where resource_assignment_id = l_resource_assignment_id);
6059:
6060: -- Update gms_budget_versions only if the denormalized totals are
6061: -- not being maintained in the form. Example the Copy Actual
6062: -- process.
6063:
6064: update gms_budget_versions
6060: -- Update gms_budget_versions only if the denormalized totals are
6061: -- not being maintained in the form. Example the Copy Actual
6062: -- process.
6063:
6064: update gms_budget_versions
6065: set raw_cost = pa_currency.round_currency_amt(nvl(raw_cost,0) - nvl(l_raw_cost,0) ),
6066: burdened_cost = pa_currency.round_currency_amt(nvl(burdened_cost,0) - nvl(l_burdened_cost,0) ),
6067: labor_quantity = (to_number(
6068: decode(l_track_as_labor_flag,
6109:
6110: CLOSE l_budget_line_rowid_csr;
6111:
6112:
6113: --summarizing the totals in the table gms_budget_versions
6114:
6115: GMS_BUDGET_PUB.summerize_project_totals( x_budget_version_id => l_budget_version_id
6116: , x_err_code => x_err_code
6117: , x_err_stage => x_err_stage
6269: , budget_entry_method_code
6270: , resource_list_id
6271: , change_reason_code
6272: , description
6273: FROM gms_budget_versions
6274: WHERE project_id = p_project_id
6275: AND award_id = p_award_id
6276: AND budget_type_code = p_budget_type_code
6277: AND budget_status_code in ('W','S');
6337:
6338: CURSOR l_lock_budget_csr( p_budget_version_id NUMBER )
6339: IS
6340: SELECT 'x'
6341: FROM gms_budget_versions bv
6342: , gms_resource_assignments ra
6343: , gms_budget_lines bl
6344: WHERE bv.budget_version_id = p_budget_version_id
6345: AND bv.budget_version_id = ra.budget_version_id (+)
6679: p_resource_list_id => p_resource_list_id);
6680:
6681: END IF;
6682:
6683: -- Once the above validations have passed update the GMS_BUDGET_VERSIONS table
6684: -- with the appropriate values.
6685:
6686: -- Bug 3104308 : Validation for p_first_budget_period .This will be fired only for
6687: -- GL/PA budget periods.
6702: END IF;
6703:
6704: OPEN l_lock_budget_csr( l_budget_version_id );
6705:
6706: UPDATE GMS_BUDGET_VERSIONS
6707: SET change_reason_code = decode(p_change_reason_code,
6708: GMS_BUDGET_PUB.G_MISS_CHAR,
6709: change_reason_code,
6710: p_change_reason_code),
6828: END IF;
6829:
6830: end if; -- l_time_phased_type_code
6831:
6832: --summarizing the totals in the table gms_budget_versions
6833:
6834: gms_budget_pub.summerize_project_totals( x_budget_version_id => l_budget_version_id
6835: , x_err_code => x_err_code
6836: , x_err_stage => x_err_stage
6987: IS
6988: SELECT budget_version_id
6989: , budget_entry_method_code
6990: , resource_list_id
6991: FROM gms_budget_versions
6992: WHERE project_id = p_project_id
6993: AND award_id = p_award_id
6994: AND budget_type_code = p_budget_type_code
6995: AND budget_status_code IN ('W','S');