[Home] [Help]
3:
4:
5: -- Global Variable : Used in create_burden_adjustments and function calc_exp_burden
6: g_set_of_books_id pa_implementations_all.set_of_books_id%type;
7: g_request_id gms_bc_packets.request_id%type;
8:
9: -- Used in update_bc_pkt_burden_raw_cost
10: g_actual_flag varchar2(1);
11:
23:
24:
25:
26: -- variable used in update_bc_pkt_burden_raw_cost and UPDATE_BC_PACKET
27: l_calc_sequence gms_bc_packets.burden_calculation_seq%type;
28:
29: -- This cursor and variable will be used to lock the summary table ...
30: -- Used in update_bc_pkt_burden_raw_cost and Maximize_burden
31: Cursor c_lock_burden_summary(p_award_id in number,p_exp_type in varchar2) is
101: nvl(entered_dr,0) entered_dr,
102: award_set_id,
103: transaction_source,
104: request_id
105: from gms_bc_packets
106: where packet_id = p_packet_id
107: and expenditure_type = p_expenditure_type
108: and award_id = p_award_id
109: and nvl(entered_cr,0) + nvl(entered_dr,0) <> 0
126: nvl(entered_dr,0) entered_dr,
127: gbp.award_set_id,
128: transaction_source,
129: gbp.request_id
130: from gms_bc_packets gbp,
131: gms_award_distributions adl
132: where gbp.packet_id = p_packet_id
133: and gbp.expenditure_type = p_expenditure_type
134: and gbp.award_id = p_award_id
828: SUM( decode(pkt.document_type, 'EXP',nvl(pkt.burdenable_raw_cost,0), 0 ) ) exp_brc,
829: SUM( decode(pkt.document_type, 'PO', nvl(pkt.burdenable_raw_cost,0), 0 ) ) po_brc,
830: SUM( decode(pkt.document_type, 'REQ',nvl(pkt.burdenable_raw_cost,0), 0 ) ) req_brc,
831: SUM( decode(pkt.document_type, 'AP', nvl(pkt.burdenable_raw_cost,0), 0 ) ) ap_brc
832: FROM GMS_BC_PACKETS PKT,
833: gms_budget_versions gbv
834: WHERE pkt.award_id = p_award_id
835: and pkt.expenditure_type = p_exp_type
836: and pkt.status_code in ('A', 'P','I' )
899: END IF;
900:
901: close c_award_exp_total;
902: -- ---------------------------------------------------
903: -- Find out NOCOPY the unposted balances from GMS_BC_PACKETS
904: -- The gms_award_exp_type_act_cost is updated in
905: -- gms_gl_return_code process and burden_posted_flag
906: -- is updated to 'Y'. It is possible that there exists
907: -- some records for which funds_check has approved and
921:
922: l_arrival_seq := NVL(l_arrival_seq,0) ;
923:
924: -- ----------------------------------------
925: -- fetch pending cost from GMS_BC_PACKETS
926: -- ----------------------------------------
927: -- Start of comment
928: -- bug : 3092603
929: -- Desc : POOR PERFORMANCE FOR APXAPRVL ( INVOICE VALIDATION )
1044: -- ============= END OF PROC_GET_AWARD_EXP_TOTAL ==========================
1045:
1046:
1047: -- ------------------------------------------------------------------------------------------------
1048: -- Update expenditure_category and revenue category on gms_bc_packets because of change in RLMI API.
1049: -- Update person_id,job_id,vendor_id columns on gms_bc_packets.
1050: -- This is done before setup_rlmi is called Bug 2143160
1051: -- ------------------------------------------------------------------------------------------------
1052: PROCEDURE update_exp_rev_cat (x_packet_id IN NUMBER) IS
1045:
1046:
1047: -- ------------------------------------------------------------------------------------------------
1048: -- Update expenditure_category and revenue category on gms_bc_packets because of change in RLMI API.
1049: -- Update person_id,job_id,vendor_id columns on gms_bc_packets.
1050: -- This is done before setup_rlmi is called Bug 2143160
1051: -- ------------------------------------------------------------------------------------------------
1052: PROCEDURE update_exp_rev_cat (x_packet_id IN NUMBER) IS
1053: BEGIN
1052: PROCEDURE update_exp_rev_cat (x_packet_id IN NUMBER) IS
1053: BEGIN
1054: --
1055: -- To update expenditure_category and revenue category
1056: UPDATE gms_bc_packets pkt
1057: SET (pkt.expenditure_category,pkt.revenue_category) =
1058: (select pe.expenditure_category,pe.revenue_category_code
1059: from pa_expenditure_types pe
1060: where pe.expenditure_type = pkt.expenditure_type)
1061: WHERE pkt.packet_id = x_packet_id;
1062: END update_exp_rev_cat;
1063: -- ------------------------------------------------------------------------------------------------
1064: -- ------------------------------------------------------------------------------------------------
1065: -- This procedure updates the gms_bc_packets top_task_id and parent_resource_id for a packet
1066: -- This is mainly used during interface process.
1067: -- Bug 2143160
1068: -- ------------------------------------------------------------------------------------------------
1069: PROCEDURE update_top_tsk_par_res (x_packet_id IN NUMBER) IS
1067: -- Bug 2143160
1068: -- ------------------------------------------------------------------------------------------------
1069: PROCEDURE update_top_tsk_par_res (x_packet_id IN NUMBER) IS
1070: BEGIN
1071: UPDATE gms_bc_packets pkt
1072: SET pkt.top_task_id = (SELECT top_task_id
1073: FROM pa_tasks
1074: WHERE task_id = pkt.task_id)
1075: WHERE pkt.packet_id = x_packet_id
1074: WHERE task_id = pkt.task_id)
1075: WHERE pkt.packet_id = x_packet_id
1076: AND pkt.top_task_id IS NULL;
1077:
1078: UPDATE gms_bc_packets pkt
1079: SET pkt.parent_resource_id = (SELECT parent_member_id
1080: FROM pa_resource_list_members
1081: WHERE resource_list_member_id = pkt.resource_list_member_id)
1082: WHERE pkt.packet_id = x_packet_id
1085: END update_top_tsk_par_res;
1086:
1087: -- ----------------------------------------------------------------------------------------------------
1088: -- -------------------------------------------------------------------------
1089: -- create_burden_adjustments : Function creates burden adjusmtent entry in gms_bc_packets
1090: -- -------------------------------------------------------------------------
1091: PROCEDURE create_burden_adjustments(p_rec_log gms_burden_adjustments_log%ROWTYPE,
1092: p_project_id pa_projects_all.project_id%type,
1093: p_task_id pa_tasks.task_id%type,
1090: -- -------------------------------------------------------------------------
1091: PROCEDURE create_burden_adjustments(p_rec_log gms_burden_adjustments_log%ROWTYPE,
1092: p_project_id pa_projects_all.project_id%type,
1093: p_task_id pa_tasks.task_id%type,
1094: p_expenditure_item_date gms_bc_packets.expenditure_item_date%type,
1095: p_expenditure_org_id gms_bc_packets.expenditure_organization_id%type,
1096: p_rlmi gms_bc_packets.resource_list_member_id%type,
1097: p_bud_task_id gms_bc_packets.bud_task_id%type,
1098: p_ind_compiled_set_id gms_bc_packets.ind_compiled_set_id%type
1091: PROCEDURE create_burden_adjustments(p_rec_log gms_burden_adjustments_log%ROWTYPE,
1092: p_project_id pa_projects_all.project_id%type,
1093: p_task_id pa_tasks.task_id%type,
1094: p_expenditure_item_date gms_bc_packets.expenditure_item_date%type,
1095: p_expenditure_org_id gms_bc_packets.expenditure_organization_id%type,
1096: p_rlmi gms_bc_packets.resource_list_member_id%type,
1097: p_bud_task_id gms_bc_packets.bud_task_id%type,
1098: p_ind_compiled_set_id gms_bc_packets.ind_compiled_set_id%type
1099: ) IS
1092: p_project_id pa_projects_all.project_id%type,
1093: p_task_id pa_tasks.task_id%type,
1094: p_expenditure_item_date gms_bc_packets.expenditure_item_date%type,
1095: p_expenditure_org_id gms_bc_packets.expenditure_organization_id%type,
1096: p_rlmi gms_bc_packets.resource_list_member_id%type,
1097: p_bud_task_id gms_bc_packets.bud_task_id%type,
1098: p_ind_compiled_set_id gms_bc_packets.ind_compiled_set_id%type
1099: ) IS
1100: PRAGMA AUTONOMOUS_TRANSACTION; -- R12 Funds Management Uptake : Made this an autonomous procedure.
1093: p_task_id pa_tasks.task_id%type,
1094: p_expenditure_item_date gms_bc_packets.expenditure_item_date%type,
1095: p_expenditure_org_id gms_bc_packets.expenditure_organization_id%type,
1096: p_rlmi gms_bc_packets.resource_list_member_id%type,
1097: p_bud_task_id gms_bc_packets.bud_task_id%type,
1098: p_ind_compiled_set_id gms_bc_packets.ind_compiled_set_id%type
1099: ) IS
1100: PRAGMA AUTONOMOUS_TRANSACTION; -- R12 Funds Management Uptake : Made this an autonomous procedure.
1101:
1094: p_expenditure_item_date gms_bc_packets.expenditure_item_date%type,
1095: p_expenditure_org_id gms_bc_packets.expenditure_organization_id%type,
1096: p_rlmi gms_bc_packets.resource_list_member_id%type,
1097: p_bud_task_id gms_bc_packets.bud_task_id%type,
1098: p_ind_compiled_set_id gms_bc_packets.ind_compiled_set_id%type
1099: ) IS
1100: PRAGMA AUTONOMOUS_TRANSACTION; -- R12 Funds Management Uptake : Made this an autonomous procedure.
1101:
1102: x_rec_log gms_burden_adjustments_log%ROWTYPE ;
1099: ) IS
1100: PRAGMA AUTONOMOUS_TRANSACTION; -- R12 Funds Management Uptake : Made this an autonomous procedure.
1101:
1102: x_rec_log gms_burden_adjustments_log%ROWTYPE ;
1103: l_top_task_id gms_bc_packets.top_task_id%type;
1104: l_parent_resource_id gms_bc_packets.parent_resource_id%type;
1105: l_budget_version_id gms_bc_packets.budget_version_id%type;
1106:
1107: l_stage varchar2(25);
1100: PRAGMA AUTONOMOUS_TRANSACTION; -- R12 Funds Management Uptake : Made this an autonomous procedure.
1101:
1102: x_rec_log gms_burden_adjustments_log%ROWTYPE ;
1103: l_top_task_id gms_bc_packets.top_task_id%type;
1104: l_parent_resource_id gms_bc_packets.parent_resource_id%type;
1105: l_budget_version_id gms_bc_packets.budget_version_id%type;
1106:
1107: l_stage varchar2(25);
1108:
1101:
1102: x_rec_log gms_burden_adjustments_log%ROWTYPE ;
1103: l_top_task_id gms_bc_packets.top_task_id%type;
1104: l_parent_resource_id gms_bc_packets.parent_resource_id%type;
1105: l_budget_version_id gms_bc_packets.budget_version_id%type;
1106:
1107: l_stage varchar2(25);
1108:
1109: BEGIN
1159:
1160: -- Create burden adjustment entry ..
1161: l_stage := 'Create Burden Entry';
1162:
1163: insert into gms_bc_packets(
1164: packet_id,
1165: project_id,
1166: award_id,
1167: task_id,
1227: x_rec_log.document_header_id,
1228: x_rec_log.document_distribution_id,
1229: 0, --entered_dr,
1230: 0, --entered_cr
1231: gms_bc_packets_s.nextval,
1232: g_request_id,
1233: x_rec_log.bc_packet_id,
1234: x_rec_log.bc_packet_id,
1235: 'Y', -- burden_adjustment_flag,
1240: l_top_task_id,
1241: l_parent_resource_id,
1242: x_rec_log.adjustment_id,
1243: (select source_event_id
1244: from gms_bc_packets
1245: where bc_packet_id = x_rec_log.bc_packet_id)); /* Added for Bug 5645290 */
1246:
1247:
1248: -- ---------------------------------------------------------+
1301: X_prev_adj NUMBER ;
1302:
1303: Cursor get_prev_adj is
1304: SELECT SUM(burdenable_raw_cost)
1305: FROM gms_bc_packets
1306: WHERE document_header_id = p_header_id
1307: AND document_distribution_id = p_dist_id
1308: AND document_type = p_doc_type
1309: AND burden_adjustment_flag = 'Y'
1669: -- document type criteria was added to remove the FTS.
1670: --
1671: cursor c1 is
1672: select sum(burdenable_raw_cost)
1673: from gms_bc_packets
1674: where document_header_id = x_hdr_id
1675: and document_distribution_id = x_doc_dist_id
1676: and burden_adjustment_flag = 'Y'
1677: and nvl(burden_posted_flag,'N') <> 'X'
2230: x_tot_burden:= x_tot_burden + p_burden ;
2231:
2232: select gms_adjustments_id_s.NEXTVAL into l_calc_sequence from dual;
2233:
2234: UPDATE gms_bc_packets
2235: set burdenable_raw_cost = p_burden,
2236: burden_calculation_seq = l_calc_sequence
2237: where packet_id = p_record.packet_id
2238: and bc_packet_id = p_record.bc_packet_id ;
2422: /* =================================================================
2423: -- Bug : 2557041 - Added for IP check funds Enhancement
2424:
2425: As burden adjustment will not be carried out NOCOPY in check funds mode,
2426: reutrn after updating the burdenable_raw_cost on gms_bc_packets
2427: ================================================================== */
2428:
2429: IF p_mode = 'C' THEN
2430: update_bc_packet('REQ', burden_raw_cost, pkt_amount, p_record ) ;
2575: /* =================================================================
2576: -- Bug : 2557041 - Added for IP check funds Enhancement
2577:
2578: As burden adjustment will not be carried out NOCOPY in check funds mode,
2579: reutrn after updating the burdenable_raw_cost on gms_bc_packets
2580: ================================================================== */
2581:
2582: IF p_mode = 'C' THEN
2583: update_bc_packet('PO', burden_raw_cost, pkt_amount, p_record ) ;
3174: /* =================================================================
3175: -- Bug : 2557041 - Added for IP check funds Enhancement
3176:
3177: As burden adjustment will not be carried out NOCOPY in check funds mode,
3178: reutrn after updating the burdenable_raw_cost on gms_bc_packets
3179: ================================================================== */
3180:
3181: IF p_mode = 'C' THEN
3182: update_bc_packet('AP', burden_raw_cost, pkt_amount, p_record ) ;
3529: /* =================================================================
3530: -- Bug : 2557041 - Added for IP check funds Enhancement
3531:
3532: As burden adjustment will not be carried out NOCOPY in check funds mode,
3533: reutrn after updating the burdenable_raw_cost on gms_bc_packets
3534: ================================================================== */
3535:
3536: IF p_mode = 'C' THEN
3537: update_bc_packet('EXP', burden_raw_cost, pkt_amount, p_record ) ;
3612: -- * limit (non-zero)
3613: -- * Only check original transaction being FC'ed (parent_bc_packet_id is null)
3614: Cursor c_awd_exp is
3615: select distinct gbp.award_id,gbp.expenditure_type
3616: from gms_bc_packets gbp,
3617: gms_awards_all ga,
3618: gms_allowable_expenditures gae
3619: where gbp.packet_id = p_packet_id
3620: and ga.award_id = gbp.award_id
3631: Cursor c_bcpkts(x_award_id in number, x_expenditure_type in varchar2) is
3632: select rowid,
3633: entered_dr ,
3634: nvl(burdenable_raw_cost,0) burden
3635: from gms_bc_packets gbp
3636: where gbp.packet_id = p_packet_id
3637: and gbp.award_id = x_award_id
3638: and gbp.expenditure_type = x_expenditure_type
3639: and nvl(gbp.entered_dr,0) > 0
3643: order by decode(gbp.document_type,'EXP',1,'AP',2,'ENC',3,'PO',4,'REQ',5,6) asc,
3644: nvl(gbp.entered_dr,0) desc;
3645:
3646: -- Variable holds burden that can be maximized
3647: x_avail_burden_amt gms_bc_packets.burdenable_raw_cost%type;
3648:
3649: -- Variable to hold stage
3650: x_stage number(2);
3651:
3688: -- Cursor to pick expenditure items that has lead to maximizing AP ..
3689: Cursor c_bcpkts_max(p_award_id in number, p_expenditure_type in varchar2) is
3690: select bc_packet_id,
3691: abs(nvl(burdenable_raw_cost,0)) burdenable_raw_cost
3692: from gms_bc_packets gbp
3693: where gbp.packet_id = p_packet_id
3694: and gbp.award_id = p_award_id
3695: and gbp.expenditure_type = p_expenditure_type
3696: and nvl(gbp.burdenable_raw_cost,0) < 0
3696: and nvl(gbp.burdenable_raw_cost,0) < 0
3697: and gbp.parent_bc_packet_id is null
3698: order by nvl(gbp.burdenable_raw_cost,0) desc;
3699:
3700: x_bc_packet_id gms_bc_packets.bc_packet_id%type;
3701: x_burdenable_raw_cost gms_bc_packets.burdenable_raw_cost%type;
3702:
3703: Begin
3704: g_error_procedure_name := 'Maximize_burden';
3697: and gbp.parent_bc_packet_id is null
3698: order by nvl(gbp.burdenable_raw_cost,0) desc;
3699:
3700: x_bc_packet_id gms_bc_packets.bc_packet_id%type;
3701: x_burdenable_raw_cost gms_bc_packets.burdenable_raw_cost%type;
3702:
3703: Begin
3704: g_error_procedure_name := 'Maximize_burden';
3705: IF g_debug = 'Y' THEN
3776: for y in c_bcpkts(x.award_id,x.expenditure_type)
3777: loop -- Bcpkt record loop
3778: If ((y.entered_dr - y.burden) >= x_avail_burden_amt) then
3779:
3780: Update gms_bc_packets
3781: set burdenable_raw_cost = nvl(burdenable_raw_cost,0) + x_avail_burden_amt
3782: where rowid = y.rowid;
3783:
3784: x_avail_burden_amt := 0;
3783:
3784: x_avail_burden_amt := 0;
3785:
3786: Else
3787: Update gms_bc_packets
3788: set burdenable_raw_cost = y.entered_dr
3789: where rowid = y.rowid;
3790:
3791: x_avail_burden_amt := x_avail_burden_amt - (y.entered_dr - y.burden);
4020: -- which were in function update_bc_pkt_burden_raw_cost
4021:
4022: -- --------------------------------------------------------------
4023: -- Function to update the burdenable raw cost,budget version Id and status
4024: -- in GMS_BC_PACKETS.All the records for a packet is updated.
4025: -- Parameters :
4026: -- ==============
4027: -- p_action : This parameter defines action to be performed on gms_bc_packets
4028: -- Values :
4023: -- Function to update the burdenable raw cost,budget version Id and status
4024: -- in GMS_BC_PACKETS.All the records for a packet is updated.
4025: -- Parameters :
4026: -- ==============
4027: -- p_action : This parameter defines action to be performed on gms_bc_packets
4028: -- Values :
4029: -- 'UPDATE-STATUS': Update result_code and status_code on gms_bc_packets
4030: -- 'UPDATE-BVID' : Update budget_version_id on gms_bc_packets
4031: -- 'UPDATE-BRC' : Update burdenable Raw cost on gms_bc_packets
4025: -- Parameters :
4026: -- ==============
4027: -- p_action : This parameter defines action to be performed on gms_bc_packets
4028: -- Values :
4029: -- 'UPDATE-STATUS': Update result_code and status_code on gms_bc_packets
4030: -- 'UPDATE-BVID' : Update budget_version_id on gms_bc_packets
4031: -- 'UPDATE-BRC' : Update burdenable Raw cost on gms_bc_packets
4032: -- p_packet_id : Packets associated with this packet_id in gms_bc_packets will be updated
4033: -- p_award_id : Packets associated with this award_id in gms_bc_packets will be updated
4026: -- ==============
4027: -- p_action : This parameter defines action to be performed on gms_bc_packets
4028: -- Values :
4029: -- 'UPDATE-STATUS': Update result_code and status_code on gms_bc_packets
4030: -- 'UPDATE-BVID' : Update budget_version_id on gms_bc_packets
4031: -- 'UPDATE-BRC' : Update burdenable Raw cost on gms_bc_packets
4032: -- p_packet_id : Packets associated with this packet_id in gms_bc_packets will be updated
4033: -- p_award_id : Packets associated with this award_id in gms_bc_packets will be updated
4034: -- p_expenditure_type : Packets associated with this EXP type in gms_bc_packets will be updated
4027: -- p_action : This parameter defines action to be performed on gms_bc_packets
4028: -- Values :
4029: -- 'UPDATE-STATUS': Update result_code and status_code on gms_bc_packets
4030: -- 'UPDATE-BVID' : Update budget_version_id on gms_bc_packets
4031: -- 'UPDATE-BRC' : Update burdenable Raw cost on gms_bc_packets
4032: -- p_packet_id : Packets associated with this packet_id in gms_bc_packets will be updated
4033: -- p_award_id : Packets associated with this award_id in gms_bc_packets will be updated
4034: -- p_expenditure_type : Packets associated with this EXP type in gms_bc_packets will be updated
4035: -- p_full_mode_failure : If 'Y' update all the records in packet to failed status
4028: -- Values :
4029: -- 'UPDATE-STATUS': Update result_code and status_code on gms_bc_packets
4030: -- 'UPDATE-BVID' : Update budget_version_id on gms_bc_packets
4031: -- 'UPDATE-BRC' : Update burdenable Raw cost on gms_bc_packets
4032: -- p_packet_id : Packets associated with this packet_id in gms_bc_packets will be updated
4033: -- p_award_id : Packets associated with this award_id in gms_bc_packets will be updated
4034: -- p_expenditure_type : Packets associated with this EXP type in gms_bc_packets will be updated
4035: -- p_full_mode_failure : If 'Y' update all the records in packet to failed status
4036: -- p_result_code : Failed result code
4029: -- 'UPDATE-STATUS': Update result_code and status_code on gms_bc_packets
4030: -- 'UPDATE-BVID' : Update budget_version_id on gms_bc_packets
4031: -- 'UPDATE-BRC' : Update burdenable Raw cost on gms_bc_packets
4032: -- p_packet_id : Packets associated with this packet_id in gms_bc_packets will be updated
4033: -- p_award_id : Packets associated with this award_id in gms_bc_packets will be updated
4034: -- p_expenditure_type : Packets associated with this EXP type in gms_bc_packets will be updated
4035: -- p_full_mode_failure : If 'Y' update all the records in packet to failed status
4036: -- p_result_code : Failed result code
4037:
4030: -- 'UPDATE-BVID' : Update budget_version_id on gms_bc_packets
4031: -- 'UPDATE-BRC' : Update burdenable Raw cost on gms_bc_packets
4032: -- p_packet_id : Packets associated with this packet_id in gms_bc_packets will be updated
4033: -- p_award_id : Packets associated with this award_id in gms_bc_packets will be updated
4034: -- p_expenditure_type : Packets associated with this EXP type in gms_bc_packets will be updated
4035: -- p_full_mode_failure : If 'Y' update all the records in packet to failed status
4036: -- p_result_code : Failed result code
4037:
4038: -- --------------------------------------------------------------
4081: /*bug 11845832 Adding the update statement for burden calculation seq*/
4082:
4083: select gms_adjustments_id_s.NEXTVAL into l_burden_calc_seq from dual;
4084:
4085: UPDATE gms_bc_packets gbc
4086: SET burden_calculation_seq = l_burden_calc_seq
4087: WHERE
4088: packet_id = p_packet_id
4089: AND status_code in ('P','I');
4100: then burdenable raw cost should be 0
4101: else if burden_allowed returns 'N' (i.e burden is calculated in projects )
4102: then calculate the burdenable raw cost. */
4103:
4104: Update gms_bc_packets gbc
4105: Set gbc.burdenable_raw_cost =
4106: (select decode(gbc.result_code,'P82',0,
4107: decode(gbc.transaction_source,
4108: null,decode(gae.burden_cost_limit,
4139: IF g_debug = 'Y' THEN
4140: gms_error_pkg.gms_debug ('UPDATE_BC_PKT_BRC_BVID_STATUS'||': Result Code update :','C');
4141: END IF;
4142:
4143: update gms_bc_packets
4144: set result_code = x_result_code
4145: where packet_id = p_packet_id
4146: and award_id = NVL(p_award_id,award_id)
4147: and expenditure_type = NVL(p_expenditure_type,expenditure_type) ;
4153: END IF;
4154:
4155:
4156: If p_full_mode_failure = 'Y' then -- Encumbrance : PO/AP/REQ
4157: Update gms_bc_packets
4158: set status_code = 'R',
4159: result_code = decode(substr(result_code,1,1),'P','F65',result_code)
4160: where packet_id = p_packet_id;
4161:
4169:
4170:
4171: ELSIF p_action = g_update_bvid THEN
4172:
4173: -- Update budget_verison_id on gms_bc_packets. This is required as cursor c_Act
4174: -- checks for gms_bc_packet trasnactions that has a baselined budget only
4175: -- during summarization ...
4176:
4177: IF g_debug = 'Y' THEN
4177: IF g_debug = 'Y' THEN
4178: gms_error_pkg.gms_debug ('UPDATE_BC_PKT_BRC_BVID_STATUS'||': Budget version Id update :','C');
4179: END IF;
4180:
4181: Update gms_bc_packets bcp
4182: set bcp.budget_version_id = (select gbv.budget_version_id
4183: from gms_budget_versions gbv
4184: where gbv.award_id = bcp.award_id
4185: and gbv.project_id = bcp.project_id
4193: Begin
4194:
4195: Select 1 into l_count
4196: from dual where exists
4197: (select 1 from gms_bc_packets bcp
4198: where bcp.packet_id = p_packet_id
4199: and bcp.award_id = p_award_id
4200: and bcp.expenditure_type = p_expenditure_type
4201: and bcp.budget_version_id is null);
4205: END IF;
4206:
4207: x_result_code := 'F';
4208:
4209: Update gms_bc_packets
4210: set status_code = decode(p_mode,'C','F','R'),
4211: result_code = 'F12',
4212: fc_error_message = 'Could not derive budget version during burden calculation'
4213: where packet_id = p_packet_id
4220: IF g_debug = 'Y' THEN
4221: gms_error_pkg.gms_debug ('UPDATE_BC_PKT_BRC_BVID_STATUS'||':Budget version id failure: Full mode failure','C');
4222: END IF;
4223:
4224: Update gms_bc_packets
4225: set status_code = decode(p_mode,'C','F','R'),
4226: result_code = decode(substr(result_code,1,1),'P','F65',result_code)
4227: where packet_id = p_packet_id;
4228:
4247: END update_bc_pkt_brc_bvid_status;
4248:
4249:
4250: -- --------------------------------------------------------------
4251: -- Function to calculate and update the burdenable raw cost in GMS_BC_PACKETS
4252: -- All the records for a packet is updated.
4253: -- R12 Funds Managment Uptake : Modified below code to
4254: -- a. shift updates to new autonomous procedure update_bc_pkt_brc_bvid_status
4255: -- b. Added p_partial_flag parameter to fail records in bc packets based on FULL/PARTIAL MODE.
4262: IS
4263:
4264: stat boolean;
4265: X_total NUMBER ;
4266: l_expenditure_type gms_bc_packets.expenditure_type%TYPE ;
4267: l_award_id gms_bc_packets.award_id%TYPE ;
4268: l_header_id NUMBER ;
4269: X_result_code VARCHAR2(3) ;
4270:
4263:
4264: stat boolean;
4265: X_total NUMBER ;
4266: l_expenditure_type gms_bc_packets.expenditure_type%TYPE ;
4267: l_award_id gms_bc_packets.award_id%TYPE ;
4268: l_header_id NUMBER ;
4269: X_result_code VARCHAR2(3) ;
4270:
4271: cursor C_award_exp is
4269: X_result_code VARCHAR2(3) ;
4270:
4271: cursor C_award_exp is
4272: select distinct bcp.award_id, bcp.expenditure_type
4273: from gms_bc_packets bcp,
4274: gms_awards_all ga,
4275: gms_allowable_expenditures gae
4276: where bcp.packet_id = x_packet_id
4277: and status_code IN ('P','I') -- fix for bug : 2927485 ,to reject the transactions that may have already failed a setup step
4281: and gae.burden_cost_limit is not null;
4282:
4283: l_dummy number;
4284: l_full_mode_failure varchar2(1) := 'N';
4285: l_result_code gms_bc_packets.result_code%TYPE;
4286:
4287: BEGIN
4288:
4289: -------------------------------------------------------------------------------+
4318: g_actual_flag := 'E';
4319: Elsif p_mode in ('R') then
4320: Begin
4321: select 'A' into g_actual_flag from dual where exists
4322: (select 1 from gms_bc_packets where packet_id = x_packet_id
4323: and document_type = 'EXP');
4324: Exception
4325: When no_data_found then
4326: g_actual_flag := 'E';
4615: Cursor c_packet is
4616: select rowid,document_header_id, document_distribution_id,
4617: expenditure_type,burdenable_raw_cost,document_type,
4618: burden_adjustment_flag,ind_compiled_set_id
4619: from gms_bc_packets
4620: where packet_id = x_packet_id
4621: and status_code IN ('P','I')
4622: and substr(result_code,1,1) = 'P'
4623: and ((nvl(burden_adjustment_flag,'N') = 'N' and parent_bc_packet_id is null)
4696:
4697: End If;
4698:
4699: IF SQL%FOUND THEN
4700: Update gms_bc_packets
4701: set burden_posted_flag = 'X'
4702: where rowid = bc_records.rowid;
4703: ELSE
4704: ROLLBACK TO POST_BURDEN;
4702: where rowid = bc_records.rowid;
4703: ELSE
4704: ROLLBACK TO POST_BURDEN;
4705: l_stage := 'Failure';
4706: Update gms_bc_packets
4707: set status_code = 'R',
4708: result_code = 'F52'
4709: where rowid = bc_records.rowid;
4710:
4707: set status_code = 'R',
4708: result_code = 'F52'
4709: where rowid = bc_records.rowid;
4710:
4711: -- R12 Funds Management Uptake : Update fail status on gms_bc_packets based on Partial/Full mode
4712: IF p_partial_flag = 'N' THEN
4713: EXIT;
4714: END IF;
4715: END IF ;
4727: gms_error_pkg.gms_debug ('***********'||g_error_procedure_name||':FAILURE:'||l_error,'C');
4728:
4729: ROLLBACK TO POST_BURDEN;
4730:
4731: Update gms_bc_packets
4732: set status_code = 'T',
4733: result_code = 'F54',
4734: fc_error_message = l_stage||';'||l_error
4735: where packet_id = x_packet_id;