[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
561: SUM( decode(pkt.document_type, 'EXP',nvl(pkt.burdenable_raw_cost,0), 0 ) ) exp_brc,
562: SUM( decode(pkt.document_type, 'PO', nvl(pkt.burdenable_raw_cost,0), 0 ) ) po_brc,
563: SUM( decode(pkt.document_type, 'REQ',nvl(pkt.burdenable_raw_cost,0), 0 ) ) req_brc,
564: SUM( decode(pkt.document_type, 'AP', nvl(pkt.burdenable_raw_cost,0), 0 ) ) ap_brc
565: FROM GMS_BC_PACKETS PKT,
566: gms_budget_versions gbv
567: WHERE pkt.award_id = p_award_id
568: and pkt.expenditure_type = p_exp_type
569: and pkt.status_code in ('A', 'P','I' )
632: END IF;
633:
634: close c_award_exp_total;
635: -- ---------------------------------------------------
636: -- Find out NOCOPY the unposted balances from GMS_BC_PACKETS
637: -- The gms_award_exp_type_act_cost is updated in
638: -- gms_gl_return_code process and burden_posted_flag
639: -- is updated to 'Y'. It is possible that there exists
640: -- some records for which funds_check has approved and
654:
655: l_arrival_seq := NVL(l_arrival_seq,0) ;
656:
657: -- ----------------------------------------
658: -- fetch pending cost from GMS_BC_PACKETS
659: -- ----------------------------------------
660: -- Start of comment
661: -- bug : 3092603
662: -- Desc : POOR PERFORMANCE FOR APXAPRVL ( INVOICE VALIDATION )
777: -- ============= END OF PROC_GET_AWARD_EXP_TOTAL ==========================
778:
779:
780: -- ------------------------------------------------------------------------------------------------
781: -- Update expenditure_category and revenue category on gms_bc_packets because of change in RLMI API.
782: -- Update person_id,job_id,vendor_id columns on gms_bc_packets.
783: -- This is done before setup_rlmi is called Bug 2143160
784: -- ------------------------------------------------------------------------------------------------
785: PROCEDURE update_exp_rev_cat (x_packet_id IN NUMBER) IS
778:
779:
780: -- ------------------------------------------------------------------------------------------------
781: -- Update expenditure_category and revenue category on gms_bc_packets because of change in RLMI API.
782: -- Update person_id,job_id,vendor_id columns on gms_bc_packets.
783: -- This is done before setup_rlmi is called Bug 2143160
784: -- ------------------------------------------------------------------------------------------------
785: PROCEDURE update_exp_rev_cat (x_packet_id IN NUMBER) IS
786: BEGIN
785: PROCEDURE update_exp_rev_cat (x_packet_id IN NUMBER) IS
786: BEGIN
787: --
788: -- To update expenditure_category and revenue category
789: UPDATE gms_bc_packets pkt
790: SET (pkt.expenditure_category,pkt.revenue_category) =
791: (select pe.expenditure_category,pe.revenue_category_code
792: from pa_expenditure_types pe
793: where pe.expenditure_type = pkt.expenditure_type)
794: WHERE pkt.packet_id = x_packet_id;
795: END update_exp_rev_cat;
796: -- ------------------------------------------------------------------------------------------------
797: -- ------------------------------------------------------------------------------------------------
798: -- This procedure updates the gms_bc_packets top_task_id and parent_resource_id for a packet
799: -- This is mainly used during interface process.
800: -- Bug 2143160
801: -- ------------------------------------------------------------------------------------------------
802: PROCEDURE update_top_tsk_par_res (x_packet_id IN NUMBER) IS
800: -- Bug 2143160
801: -- ------------------------------------------------------------------------------------------------
802: PROCEDURE update_top_tsk_par_res (x_packet_id IN NUMBER) IS
803: BEGIN
804: UPDATE gms_bc_packets pkt
805: SET pkt.top_task_id = (SELECT top_task_id
806: FROM pa_tasks
807: WHERE task_id = pkt.task_id)
808: WHERE pkt.packet_id = x_packet_id
807: WHERE task_id = pkt.task_id)
808: WHERE pkt.packet_id = x_packet_id
809: AND pkt.top_task_id IS NULL;
810:
811: UPDATE gms_bc_packets pkt
812: SET pkt.parent_resource_id = (SELECT parent_member_id
813: FROM pa_resource_list_members
814: WHERE resource_list_member_id = pkt.resource_list_member_id)
815: WHERE pkt.packet_id = x_packet_id
818: END update_top_tsk_par_res;
819:
820: -- ----------------------------------------------------------------------------------------------------
821: -- -------------------------------------------------------------------------
822: -- create_burden_adjustments : Function creates burden adjusmtent entry in gms_bc_packets
823: -- -------------------------------------------------------------------------
824: PROCEDURE create_burden_adjustments(p_rec_log gms_burden_adjustments_log%ROWTYPE,
825: p_project_id pa_projects_all.project_id%type,
826: p_task_id pa_tasks.task_id%type,
823: -- -------------------------------------------------------------------------
824: PROCEDURE create_burden_adjustments(p_rec_log gms_burden_adjustments_log%ROWTYPE,
825: p_project_id pa_projects_all.project_id%type,
826: p_task_id pa_tasks.task_id%type,
827: p_expenditure_item_date gms_bc_packets.expenditure_item_date%type,
828: p_expenditure_org_id gms_bc_packets.expenditure_organization_id%type,
829: p_rlmi gms_bc_packets.resource_list_member_id%type,
830: p_bud_task_id gms_bc_packets.bud_task_id%type,
831: p_ind_compiled_set_id gms_bc_packets.ind_compiled_set_id%type
824: PROCEDURE create_burden_adjustments(p_rec_log gms_burden_adjustments_log%ROWTYPE,
825: p_project_id pa_projects_all.project_id%type,
826: p_task_id pa_tasks.task_id%type,
827: p_expenditure_item_date gms_bc_packets.expenditure_item_date%type,
828: p_expenditure_org_id gms_bc_packets.expenditure_organization_id%type,
829: p_rlmi gms_bc_packets.resource_list_member_id%type,
830: p_bud_task_id gms_bc_packets.bud_task_id%type,
831: p_ind_compiled_set_id gms_bc_packets.ind_compiled_set_id%type
832: ) IS
825: p_project_id pa_projects_all.project_id%type,
826: p_task_id pa_tasks.task_id%type,
827: p_expenditure_item_date gms_bc_packets.expenditure_item_date%type,
828: p_expenditure_org_id gms_bc_packets.expenditure_organization_id%type,
829: p_rlmi gms_bc_packets.resource_list_member_id%type,
830: p_bud_task_id gms_bc_packets.bud_task_id%type,
831: p_ind_compiled_set_id gms_bc_packets.ind_compiled_set_id%type
832: ) IS
833: PRAGMA AUTONOMOUS_TRANSACTION; -- R12 Funds Management Uptake : Made this an autonomous procedure.
826: p_task_id pa_tasks.task_id%type,
827: p_expenditure_item_date gms_bc_packets.expenditure_item_date%type,
828: p_expenditure_org_id gms_bc_packets.expenditure_organization_id%type,
829: p_rlmi gms_bc_packets.resource_list_member_id%type,
830: p_bud_task_id gms_bc_packets.bud_task_id%type,
831: p_ind_compiled_set_id gms_bc_packets.ind_compiled_set_id%type
832: ) IS
833: PRAGMA AUTONOMOUS_TRANSACTION; -- R12 Funds Management Uptake : Made this an autonomous procedure.
834:
827: p_expenditure_item_date gms_bc_packets.expenditure_item_date%type,
828: p_expenditure_org_id gms_bc_packets.expenditure_organization_id%type,
829: p_rlmi gms_bc_packets.resource_list_member_id%type,
830: p_bud_task_id gms_bc_packets.bud_task_id%type,
831: p_ind_compiled_set_id gms_bc_packets.ind_compiled_set_id%type
832: ) IS
833: PRAGMA AUTONOMOUS_TRANSACTION; -- R12 Funds Management Uptake : Made this an autonomous procedure.
834:
835: x_rec_log gms_burden_adjustments_log%ROWTYPE ;
832: ) IS
833: PRAGMA AUTONOMOUS_TRANSACTION; -- R12 Funds Management Uptake : Made this an autonomous procedure.
834:
835: x_rec_log gms_burden_adjustments_log%ROWTYPE ;
836: l_top_task_id gms_bc_packets.top_task_id%type;
837: l_parent_resource_id gms_bc_packets.parent_resource_id%type;
838: l_budget_version_id gms_bc_packets.budget_version_id%type;
839:
840: l_stage varchar2(25);
833: PRAGMA AUTONOMOUS_TRANSACTION; -- R12 Funds Management Uptake : Made this an autonomous procedure.
834:
835: x_rec_log gms_burden_adjustments_log%ROWTYPE ;
836: l_top_task_id gms_bc_packets.top_task_id%type;
837: l_parent_resource_id gms_bc_packets.parent_resource_id%type;
838: l_budget_version_id gms_bc_packets.budget_version_id%type;
839:
840: l_stage varchar2(25);
841:
834:
835: x_rec_log gms_burden_adjustments_log%ROWTYPE ;
836: l_top_task_id gms_bc_packets.top_task_id%type;
837: l_parent_resource_id gms_bc_packets.parent_resource_id%type;
838: l_budget_version_id gms_bc_packets.budget_version_id%type;
839:
840: l_stage varchar2(25);
841:
842: BEGIN
892:
893: -- Create burden adjustment entry ..
894: l_stage := 'Create Burden Entry';
895:
896: insert into gms_bc_packets(
897: packet_id,
898: project_id,
899: award_id,
900: task_id,
960: x_rec_log.document_header_id,
961: x_rec_log.document_distribution_id,
962: 0, --entered_dr,
963: 0, --entered_cr
964: gms_bc_packets_s.nextval,
965: g_request_id,
966: x_rec_log.bc_packet_id,
967: x_rec_log.bc_packet_id,
968: 'Y', -- burden_adjustment_flag,
973: l_top_task_id,
974: l_parent_resource_id,
975: x_rec_log.adjustment_id,
976: (select source_event_id
977: from gms_bc_packets
978: where bc_packet_id = x_rec_log.bc_packet_id)); /* Added for Bug 5645290 */
979:
980:
981: -- ---------------------------------------------------------+
1034: X_prev_adj NUMBER ;
1035:
1036: Cursor get_prev_adj is
1037: SELECT SUM(burdenable_raw_cost)
1038: FROM gms_bc_packets
1039: WHERE document_header_id = p_header_id
1040: AND document_distribution_id = p_dist_id
1041: AND document_type = p_doc_type
1042: AND burden_adjustment_flag = 'Y'
1402: -- document type criteria was added to remove the FTS.
1403: --
1404: cursor c1 is
1405: select sum(burdenable_raw_cost)
1406: from gms_bc_packets
1407: where document_header_id = x_hdr_id
1408: and document_distribution_id = x_doc_dist_id
1409: and burden_adjustment_flag = 'Y'
1410: and nvl(burden_posted_flag,'N') <> 'X'
1963: x_tot_burden:= x_tot_burden + p_burden ;
1964:
1965: select gms_adjustments_id_s.NEXTVAL into l_calc_sequence from dual;
1966:
1967: UPDATE gms_bc_packets
1968: set burdenable_raw_cost = p_burden,
1969: burden_calculation_seq = l_calc_sequence
1970: where packet_id = p_record.packet_id
1971: and bc_packet_id = p_record.bc_packet_id ;
2155: /* =================================================================
2156: -- Bug : 2557041 - Added for IP check funds Enhancement
2157:
2158: As burden adjustment will not be carried out NOCOPY in check funds mode,
2159: reutrn after updating the burdenable_raw_cost on gms_bc_packets
2160: ================================================================== */
2161:
2162: IF p_mode = 'C' THEN
2163: update_bc_packet('REQ', burden_raw_cost, pkt_amount, p_record ) ;
2308: /* =================================================================
2309: -- Bug : 2557041 - Added for IP check funds Enhancement
2310:
2311: As burden adjustment will not be carried out NOCOPY in check funds mode,
2312: reutrn after updating the burdenable_raw_cost on gms_bc_packets
2313: ================================================================== */
2314:
2315: IF p_mode = 'C' THEN
2316: update_bc_packet('PO', burden_raw_cost, pkt_amount, p_record ) ;
2810: /* =================================================================
2811: -- Bug : 2557041 - Added for IP check funds Enhancement
2812:
2813: As burden adjustment will not be carried out NOCOPY in check funds mode,
2814: reutrn after updating the burdenable_raw_cost on gms_bc_packets
2815: ================================================================== */
2816:
2817: IF p_mode = 'C' THEN
2818: update_bc_packet('AP', burden_raw_cost, pkt_amount, p_record ) ;
3165: /* =================================================================
3166: -- Bug : 2557041 - Added for IP check funds Enhancement
3167:
3168: As burden adjustment will not be carried out NOCOPY in check funds mode,
3169: reutrn after updating the burdenable_raw_cost on gms_bc_packets
3170: ================================================================== */
3171:
3172: IF p_mode = 'C' THEN
3173: update_bc_packet('EXP', burden_raw_cost, pkt_amount, p_record ) ;
3248: -- * limit (non-zero)
3249: -- * Only check original transaction being FC'ed (parent_bc_packet_id is null)
3250: Cursor c_awd_exp is
3251: select distinct gbp.award_id,gbp.expenditure_type
3252: from gms_bc_packets gbp,
3253: gms_awards_all ga,
3254: gms_allowable_expenditures gae
3255: where gbp.packet_id = p_packet_id
3256: and ga.award_id = gbp.award_id
3267: Cursor c_bcpkts(x_award_id in number, x_expenditure_type in varchar2) is
3268: select rowid,
3269: entered_dr ,
3270: nvl(burdenable_raw_cost,0) burden
3271: from gms_bc_packets gbp
3272: where gbp.packet_id = p_packet_id
3273: and gbp.award_id = x_award_id
3274: and gbp.expenditure_type = x_expenditure_type
3275: and nvl(gbp.entered_dr,0) > 0
3279: order by decode(gbp.document_type,'EXP',1,'AP',2,'ENC',3,'PO',4,'REQ',5,6) asc,
3280: nvl(gbp.entered_dr,0) desc;
3281:
3282: -- Variable holds burden that can be maximized
3283: x_avail_burden_amt gms_bc_packets.burdenable_raw_cost%type;
3284:
3285: -- Variable to hold stage
3286: x_stage number(2);
3287:
3324: -- Cursor to pick expenditure items that has lead to maximizing AP ..
3325: Cursor c_bcpkts_max(p_award_id in number, p_expenditure_type in varchar2) is
3326: select bc_packet_id,
3327: abs(nvl(burdenable_raw_cost,0)) burdenable_raw_cost
3328: from gms_bc_packets gbp
3329: where gbp.packet_id = p_packet_id
3330: and gbp.award_id = p_award_id
3331: and gbp.expenditure_type = p_expenditure_type
3332: and nvl(gbp.burdenable_raw_cost,0) < 0
3332: and nvl(gbp.burdenable_raw_cost,0) < 0
3333: and gbp.parent_bc_packet_id is null
3334: order by nvl(gbp.burdenable_raw_cost,0) desc;
3335:
3336: x_bc_packet_id gms_bc_packets.bc_packet_id%type;
3337: x_burdenable_raw_cost gms_bc_packets.burdenable_raw_cost%type;
3338:
3339: Begin
3340: g_error_procedure_name := 'Maximize_burden';
3333: and gbp.parent_bc_packet_id is null
3334: order by nvl(gbp.burdenable_raw_cost,0) desc;
3335:
3336: x_bc_packet_id gms_bc_packets.bc_packet_id%type;
3337: x_burdenable_raw_cost gms_bc_packets.burdenable_raw_cost%type;
3338:
3339: Begin
3340: g_error_procedure_name := 'Maximize_burden';
3341: IF g_debug = 'Y' THEN
3412: for y in c_bcpkts(x.award_id,x.expenditure_type)
3413: loop -- Bcpkt record loop
3414: If ((y.entered_dr - y.burden) >= x_avail_burden_amt) then
3415:
3416: Update gms_bc_packets
3417: set burdenable_raw_cost = nvl(burdenable_raw_cost,0) + x_avail_burden_amt
3418: where rowid = y.rowid;
3419:
3420: x_avail_burden_amt := 0;
3419:
3420: x_avail_burden_amt := 0;
3421:
3422: Else
3423: Update gms_bc_packets
3424: set burdenable_raw_cost = y.entered_dr
3425: where rowid = y.rowid;
3426:
3427: x_avail_burden_amt := x_avail_burden_amt - (y.entered_dr - y.burden);
3656: -- which were in function update_bc_pkt_burden_raw_cost
3657:
3658: -- --------------------------------------------------------------
3659: -- Function to update the burdenable raw cost,budget version Id and status
3660: -- in GMS_BC_PACKETS.All the records for a packet is updated.
3661: -- Parameters :
3662: -- ==============
3663: -- p_action : This parameter defines action to be performed on gms_bc_packets
3664: -- Values :
3659: -- Function to update the burdenable raw cost,budget version Id and status
3660: -- in GMS_BC_PACKETS.All the records for a packet is updated.
3661: -- Parameters :
3662: -- ==============
3663: -- p_action : This parameter defines action to be performed on gms_bc_packets
3664: -- Values :
3665: -- 'UPDATE-STATUS': Update result_code and status_code on gms_bc_packets
3666: -- 'UPDATE-BVID' : Update budget_version_id on gms_bc_packets
3667: -- 'UPDATE-BRC' : Update burdenable Raw cost on gms_bc_packets
3661: -- Parameters :
3662: -- ==============
3663: -- p_action : This parameter defines action to be performed on gms_bc_packets
3664: -- Values :
3665: -- 'UPDATE-STATUS': Update result_code and status_code on gms_bc_packets
3666: -- 'UPDATE-BVID' : Update budget_version_id on gms_bc_packets
3667: -- 'UPDATE-BRC' : Update burdenable Raw cost on gms_bc_packets
3668: -- p_packet_id : Packets associated with this packet_id in gms_bc_packets will be updated
3669: -- p_award_id : Packets associated with this award_id in gms_bc_packets will be updated
3662: -- ==============
3663: -- p_action : This parameter defines action to be performed on gms_bc_packets
3664: -- Values :
3665: -- 'UPDATE-STATUS': Update result_code and status_code on gms_bc_packets
3666: -- 'UPDATE-BVID' : Update budget_version_id on gms_bc_packets
3667: -- 'UPDATE-BRC' : Update burdenable Raw cost on gms_bc_packets
3668: -- p_packet_id : Packets associated with this packet_id in gms_bc_packets will be updated
3669: -- p_award_id : Packets associated with this award_id in gms_bc_packets will be updated
3670: -- p_expenditure_type : Packets associated with this EXP type in gms_bc_packets will be updated
3663: -- p_action : This parameter defines action to be performed on gms_bc_packets
3664: -- Values :
3665: -- 'UPDATE-STATUS': Update result_code and status_code on gms_bc_packets
3666: -- 'UPDATE-BVID' : Update budget_version_id on gms_bc_packets
3667: -- 'UPDATE-BRC' : Update burdenable Raw cost on gms_bc_packets
3668: -- p_packet_id : Packets associated with this packet_id in gms_bc_packets will be updated
3669: -- p_award_id : Packets associated with this award_id in gms_bc_packets will be updated
3670: -- p_expenditure_type : Packets associated with this EXP type in gms_bc_packets will be updated
3671: -- p_full_mode_failure : If 'Y' update all the records in packet to failed status
3664: -- Values :
3665: -- 'UPDATE-STATUS': Update result_code and status_code on gms_bc_packets
3666: -- 'UPDATE-BVID' : Update budget_version_id on gms_bc_packets
3667: -- 'UPDATE-BRC' : Update burdenable Raw cost on gms_bc_packets
3668: -- p_packet_id : Packets associated with this packet_id in gms_bc_packets will be updated
3669: -- p_award_id : Packets associated with this award_id in gms_bc_packets will be updated
3670: -- p_expenditure_type : Packets associated with this EXP type in gms_bc_packets will be updated
3671: -- p_full_mode_failure : If 'Y' update all the records in packet to failed status
3672: -- p_result_code : Failed result code
3665: -- 'UPDATE-STATUS': Update result_code and status_code on gms_bc_packets
3666: -- 'UPDATE-BVID' : Update budget_version_id on gms_bc_packets
3667: -- 'UPDATE-BRC' : Update burdenable Raw cost on gms_bc_packets
3668: -- p_packet_id : Packets associated with this packet_id in gms_bc_packets will be updated
3669: -- p_award_id : Packets associated with this award_id in gms_bc_packets will be updated
3670: -- p_expenditure_type : Packets associated with this EXP type in gms_bc_packets will be updated
3671: -- p_full_mode_failure : If 'Y' update all the records in packet to failed status
3672: -- p_result_code : Failed result code
3673:
3666: -- 'UPDATE-BVID' : Update budget_version_id on gms_bc_packets
3667: -- 'UPDATE-BRC' : Update burdenable Raw cost on gms_bc_packets
3668: -- p_packet_id : Packets associated with this packet_id in gms_bc_packets will be updated
3669: -- p_award_id : Packets associated with this award_id in gms_bc_packets will be updated
3670: -- p_expenditure_type : Packets associated with this EXP type in gms_bc_packets will be updated
3671: -- p_full_mode_failure : If 'Y' update all the records in packet to failed status
3672: -- p_result_code : Failed result code
3673:
3674: -- --------------------------------------------------------------
3718: then burdenable raw cost should be 0
3719: else if burden_allowed returns 'N' (i.e burden is calculated in projects )
3720: then calculate the burdenable raw cost. */
3721:
3722: Update gms_bc_packets gbc
3723: Set gbc.burdenable_raw_cost =
3724: (select decode(gbc.result_code,'P82',0,
3725: decode(gbc.transaction_source,
3726: null,decode(gae.burden_cost_limit,
3757: IF g_debug = 'Y' THEN
3758: gms_error_pkg.gms_debug ('UPDATE_BC_PKT_BRC_BVID_STATUS'||': Result Code update :','C');
3759: END IF;
3760:
3761: update gms_bc_packets
3762: set result_code = x_result_code
3763: where packet_id = p_packet_id
3764: and award_id = NVL(p_award_id,award_id)
3765: and expenditure_type = NVL(p_expenditure_type,expenditure_type) ;
3771: END IF;
3772:
3773:
3774: If p_full_mode_failure = 'Y' then -- Encumbrance : PO/AP/REQ
3775: Update gms_bc_packets
3776: set status_code = 'R',
3777: result_code = decode(substr(result_code,1,1),'P','F65',result_code)
3778: where packet_id = p_packet_id;
3779:
3787:
3788:
3789: ELSIF p_action = g_update_bvid THEN
3790:
3791: -- Update budget_verison_id on gms_bc_packets. This is required as cursor c_Act
3792: -- checks for gms_bc_packet trasnactions that has a baselined budget only
3793: -- during summarization ...
3794:
3795: IF g_debug = 'Y' THEN
3795: IF g_debug = 'Y' THEN
3796: gms_error_pkg.gms_debug ('UPDATE_BC_PKT_BRC_BVID_STATUS'||': Budget version Id update :','C');
3797: END IF;
3798:
3799: Update gms_bc_packets bcp
3800: set bcp.budget_version_id = (select gbv.budget_version_id
3801: from gms_budget_versions gbv
3802: where gbv.award_id = bcp.award_id
3803: and gbv.project_id = bcp.project_id
3811: Begin
3812:
3813: Select 1 into l_count
3814: from dual where exists
3815: (select 1 from gms_bc_packets bcp
3816: where bcp.packet_id = p_packet_id
3817: and bcp.award_id = p_award_id
3818: and bcp.expenditure_type = p_expenditure_type
3819: and bcp.budget_version_id is null);
3823: END IF;
3824:
3825: x_result_code := 'F';
3826:
3827: Update gms_bc_packets
3828: set status_code = decode(p_mode,'C','F','R'),
3829: result_code = 'F12',
3830: fc_error_message = 'Could not derive budget version during burden calculation'
3831: where packet_id = p_packet_id
3838: IF g_debug = 'Y' THEN
3839: gms_error_pkg.gms_debug ('UPDATE_BC_PKT_BRC_BVID_STATUS'||':Budget version id failure: Full mode failure','C');
3840: END IF;
3841:
3842: Update gms_bc_packets
3843: set status_code = decode(p_mode,'C','F','R'),
3844: result_code = decode(substr(result_code,1,1),'P','F65',result_code)
3845: where packet_id = p_packet_id;
3846:
3865: END update_bc_pkt_brc_bvid_status;
3866:
3867:
3868: -- --------------------------------------------------------------
3869: -- Function to calculate and update the burdenable raw cost in GMS_BC_PACKETS
3870: -- All the records for a packet is updated.
3871: -- R12 Funds Managment Uptake : Modified below code to
3872: -- a. shift updates to new autonomous procedure update_bc_pkt_brc_bvid_status
3873: -- b. Added p_partial_flag parameter to fail records in bc packets based on FULL/PARTIAL MODE.
3880: IS
3881:
3882: stat boolean;
3883: X_total NUMBER ;
3884: l_expenditure_type gms_bc_packets.expenditure_type%TYPE ;
3885: l_award_id gms_bc_packets.award_id%TYPE ;
3886: l_header_id NUMBER ;
3887: X_result_code VARCHAR2(3) ;
3888:
3881:
3882: stat boolean;
3883: X_total NUMBER ;
3884: l_expenditure_type gms_bc_packets.expenditure_type%TYPE ;
3885: l_award_id gms_bc_packets.award_id%TYPE ;
3886: l_header_id NUMBER ;
3887: X_result_code VARCHAR2(3) ;
3888:
3889: cursor C_award_exp is
3887: X_result_code VARCHAR2(3) ;
3888:
3889: cursor C_award_exp is
3890: select distinct bcp.award_id, bcp.expenditure_type
3891: from gms_bc_packets bcp,
3892: gms_awards_all ga,
3893: gms_allowable_expenditures gae
3894: where bcp.packet_id = x_packet_id
3895: and status_code IN ('P','I') -- fix for bug : 2927485 ,to reject the transactions that may have already failed a setup step
3899: and gae.burden_cost_limit is not null;
3900:
3901: l_dummy number;
3902: l_full_mode_failure varchar2(1) := 'N';
3903: l_result_code gms_bc_packets.result_code%TYPE;
3904:
3905: BEGIN
3906:
3907: -------------------------------------------------------------------------------+
3936: g_actual_flag := 'E';
3937: Elsif p_mode in ('R') then
3938: Begin
3939: select 'A' into g_actual_flag from dual where exists
3940: (select 1 from gms_bc_packets where packet_id = x_packet_id
3941: and document_type = 'EXP');
3942: Exception
3943: When no_data_found then
3944: g_actual_flag := 'E';
4233: Cursor c_packet is
4234: select rowid,document_header_id, document_distribution_id,
4235: expenditure_type,burdenable_raw_cost,document_type,
4236: burden_adjustment_flag,ind_compiled_set_id
4237: from gms_bc_packets
4238: where packet_id = x_packet_id
4239: and status_code IN ('P','I')
4240: and substr(result_code,1,1) = 'P'
4241: and ((nvl(burden_adjustment_flag,'N') = 'N' and parent_bc_packet_id is null)
4306:
4307: End If;
4308:
4309: IF SQL%FOUND THEN
4310: Update gms_bc_packets
4311: set burden_posted_flag = 'X'
4312: where rowid = bc_records.rowid;
4313: ELSE
4314: ROLLBACK TO POST_BURDEN;
4312: where rowid = bc_records.rowid;
4313: ELSE
4314: ROLLBACK TO POST_BURDEN;
4315: l_stage := 'Failure';
4316: Update gms_bc_packets
4317: set status_code = 'R',
4318: result_code = 'F52'
4319: where rowid = bc_records.rowid;
4320:
4317: set status_code = 'R',
4318: result_code = 'F52'
4319: where rowid = bc_records.rowid;
4320:
4321: -- R12 Funds Management Uptake : Update fail status on gms_bc_packets based on Partial/Full mode
4322: IF p_partial_flag = 'N' THEN
4323: EXIT;
4324: END IF;
4325: END IF ;
4337: gms_error_pkg.gms_debug ('***********'||g_error_procedure_name||':FAILURE:'||l_error,'C');
4338:
4339: ROLLBACK TO POST_BURDEN;
4340:
4341: Update gms_bc_packets
4342: set status_code = 'T',
4343: result_code = 'F54',
4344: fc_error_message = l_stage||';'||l_error
4345: where packet_id = x_packet_id;