[Home] [Help]
3:
4: -- Private Global Variables :
5:
6: -- Variables initialized in gms_fck:
7: g_packet_id gms_bc_packets.packet_id%type;
8: g_mode Varchar2(1);
9: g_debug Varchar2(1); -- To check on, whether to print debug messages in log file or not
10: g_error_program_name Varchar2 (30);
11: g_error_procedure_name Varchar2 (30);
13:
14: -- Variables initialized in gms_fck_init:
15: g_derived_mode Varchar2(1);
16: g_partial_flag Varchar2(1);
17: g_doc_type gms_bc_packets.document_type%type;
18: g_non_gms_txn BOOLEAN;
19: g_bc_packet_has_P82_records Varchar2(1); -- Used in handle_net_zero_txn/update_fc_sequence
20: g_ip_fc_flag VARCHAR2(1); -- If FC called for IP or not ..
21: g_gl_bc_pkt_sponsored_count NUMBER; -- Count of sponsored transactions in gl_bc_packets(AP/PO/REQ)
21: g_gl_bc_pkt_sponsored_count NUMBER; -- Count of sponsored transactions in gl_bc_packets(AP/PO/REQ)
22: g_pa_addition_flag_t_count NUMBER; -- Count of sponsored AP records with pa_addition_flag = 'T'
23:
24: -- Funds Check Return Code for the Packet processed.
25: g_return_code gms_bc_packets.result_code%TYPE;
26:
27: -- R12 Funds Management uptake : Defining global variables which are reffered by
28: -- copy_gl_pkt_to_gms_pkt and misc_sync_adls procedures
29: -- PLSQL type of variables for storing transaction data
117: Cursor c_lock_bvid is
118: select budget_version_id
119: from gms_budget_versions
120: where budget_version_id in
121: (select budget_version_id from gms_bc_packets_bvid)
122: for update;
123: Begin
124: for x in c_lock_bvid loop
125: null; -- Dummy code to lock gms_budget_versions
137: BEGIN
138:
139: g_error_procedure_name := 'update_status_on_failed_txns';
140:
141: UPDATE gms_bc_packets
142: SET status_code = 'R'
143: WHERE packet_id = p_packet_id
144: AND result_code like 'F%';
145:
146: END update_status_on_failed_txns;
147:
148: /* -----------------------------------------------------------------------------------------------
149: Procedure : delete_pending_txns
150: Purpose : This procedure will delete pending records in gms_bc_packets associated with a
151: request that has been terminated.
152: After deleting the records from gms_bc_packets, corresponding request_id entry will
153: be deleted from gms_concurrency_control table.
154: -------------------------------------------------------------------------------------------------- */
148: /* -----------------------------------------------------------------------------------------------
149: Procedure : delete_pending_txns
150: Purpose : This procedure will delete pending records in gms_bc_packets associated with a
151: request that has been terminated.
152: After deleting the records from gms_bc_packets, corresponding request_id entry will
153: be deleted from gms_concurrency_control table.
154: -------------------------------------------------------------------------------------------------- */
155:
156: Procedure delete_pending_txns
203: -- Phase Code 'C' indicates that the process has been completed
204: If l_phase_code = 'C' then
205:
206: -- C. Update gms_bc_packet status to 'T'
207: Update gms_bc_packets
208: set status_code = 'T',
209: fc_error_message = 'Packet had terminated,status updated to (T) by document_type,request_id:'||l_request_id||g_doc_type
210: where request_id = c_request_id.request_id
211: and status_code = 'P';
254: PROCEDURE Raw_burden_failure(p_packet_id IN NUMBER, p_mode in VARCHAR2,p_level in VARCHAR2) IS
255: BEGIN
256: g_error_procedure_name := 'RAW_BURDEN_FAILURE';
257: -- F75 : Transaction Failed because of Raw
258: /* Update gms_bc_packets bp Commented for bug 5726575; Moved below the update below next update
259: set bp.status_code = decode(p_mode,'C','F','R'),
260: bp.result_code = decode(substr(bp.result_code,1,1),'P','F75',null,'F75',bp.result_code),
261: bp.fc_error_message = decode(bp.fc_error_message,NULL,'RAW_BURDEN_FAILURE at stage:'||p_level,bp.fc_error_message)
262: where bp.packet_id = p_packet_id
270: )
271: and bp.parent_bc_packet_id IS NOT NULL
272: and exists
273: (select 1
274: from gms_bc_packets bp1
275: where bp1.packet_id = bp.packet_id
276: and bp1.bc_packet_id = bp.parent_bc_packet_id -- GMS_BC_PACKETS_U1
277: and ((p_level = 'RES' and substr(bp1.res_result_code,1,1) = 'F') or
278: (p_level = 'RESG' and substr(bp1.res_grp_result_code,1,1) = 'F') or
272: and exists
273: (select 1
274: from gms_bc_packets bp1
275: where bp1.packet_id = bp.packet_id
276: and bp1.bc_packet_id = bp.parent_bc_packet_id -- GMS_BC_PACKETS_U1
277: and ((p_level = 'RES' and substr(bp1.res_result_code,1,1) = 'F') or
278: (p_level = 'RESG' and substr(bp1.res_grp_result_code,1,1) = 'F') or
279: (p_level = 'TSK' and substr(bp1.task_result_code,1,1) = 'F') or
280: (p_level = 'TTSK' and substr(bp1.top_task_result_code,1,1)= 'F') or
285: -- Records that has failed will not have status_code update to 'R' at this stage ..
286: -- Only way to check in main cursor is result_code 'P' ..
287:
288: -- F63 : Transaction Failed because of Burden
289: Update gms_bc_packets bp
290: set bp.status_code = decode(p_mode,'C','F','R'),
291: bp.result_code = decode(substr(bp.result_code,1,1),'P','F63',null,'F63',bp.result_code),
292: bp.fc_error_message = decode(bp.fc_error_message,NULL,'RAW_BURDEN_FAILURE at stage:'||p_level,bp.fc_error_message)
293: where bp.packet_id = p_packet_id
301: )
302: and bp.parent_bc_packet_id IS NULL
303: and exists
304: (select /*+ push_subq no_unnest */ 1 /* Hint added for bug 14685623 */
305: from gms_bc_packets bp1
306: where bp1.packet_id = bp.packet_id
307: and bp1.parent_bc_packet_id = bp.bc_packet_id -- GMS_BC_PACKETS_N3
308: and ((p_level = 'RES' and substr(bp1.res_result_code,1,1) = 'F') or
309: (p_level = 'RESG' and substr(bp1.res_grp_result_code,1,1) = 'F') or
303: and exists
304: (select /*+ push_subq no_unnest */ 1 /* Hint added for bug 14685623 */
305: from gms_bc_packets bp1
306: where bp1.packet_id = bp.packet_id
307: and bp1.parent_bc_packet_id = bp.bc_packet_id -- GMS_BC_PACKETS_N3
308: and ((p_level = 'RES' and substr(bp1.res_result_code,1,1) = 'F') or
309: (p_level = 'RESG' and substr(bp1.res_grp_result_code,1,1) = 'F') or
310: (p_level = 'TSK' and substr(bp1.task_result_code,1,1) = 'F') or
311: (p_level = 'TTSK' and substr(bp1.top_task_result_code,1,1)= 'F') or
315: );
316: -- Records that has failed will not have status_code update to 'R' at this stage ..
317: -- Only way to check in main cursor is result_code 'P' ..
318: --Bug 5726575
319: Update gms_bc_packets bp
320: set bp.status_code = decode(p_mode,'C','F','R'),
321: bp.result_code = decode(substr(bp.result_code,1,1),'P','F75',null,'F75',bp.result_code),
322: bp.fc_error_message = decode(bp.fc_error_message,NULL,'RAW_BURDEN_FAILURE at stage:'||p_level,bp.fc_error_message)
323: where bp.packet_id = p_packet_id
331: )
332: and bp.parent_bc_packet_id IS NOT NULL
333: and exists
334: (select /*+ push_subq no_unnest */ 1 /* Hint added for bug 14685623 */
335: from gms_bc_packets bp1
336: where bp1.packet_id = bp.packet_id
337: and bp1.bc_packet_id = bp.parent_bc_packet_id -- GMS_BC_PACKETS_U1
338: and substr(bp1.result_code,1,1) = 'F'
339: /*and ((p_level = 'RES' and substr(bp1.res_result_code,1,1) = 'F') or
333: and exists
334: (select /*+ push_subq no_unnest */ 1 /* Hint added for bug 14685623 */
335: from gms_bc_packets bp1
336: where bp1.packet_id = bp.packet_id
337: and bp1.bc_packet_id = bp.parent_bc_packet_id -- GMS_BC_PACKETS_U1
338: and substr(bp1.result_code,1,1) = 'F'
339: /*and ((p_level = 'RES' and substr(bp1.res_result_code,1,1) = 'F') or
340: (p_level = 'RESG' and substr(bp1.res_grp_result_code,1,1) = 'F') or
341: (p_level = 'TSK' and substr(bp1.task_result_code,1,1) = 'F') or
377: Select 1
378: into l_dummy
379: from dual
380: where exists
381: (select 1 from gms_bc_packets bp1
382: where bp1.packet_id = p_packet_id
383: and (bp1.status_code in ('R','F') or
384: (p_level = 'RES' and substr(bp1.res_result_code,1,1) = 'F') or
385: (p_level = 'RESG' and substr(bp1.res_grp_result_code,1,1) = 'F') or
389: --(p_level = 'ALL' and substr(bp1.result_code,1,1) = 'F')
390: )
391: );
392:
393: Update gms_bc_packets bp
394: set bp.status_code = decode(p_mode,'C','F','R'),
395: bp.result_code = decode(substr(bp.result_code,1,1),'P','F65',null,'F65',bp.result_code),
396: bp.fc_error_message = decode(bp.fc_error_message,NULL,'FULL_MODE_FAILURE (R/U/C/I mode) at stage:'||p_level,bp.fc_error_message)
397: where packet_id = p_packet_id
420: select 1
421: into l_dummy
422: from dual
423: where exists (select 1
424: from gms_bc_packets
425: where packet_id = p_packet_id
426: and document_type = 'AP'
427: and substr(result_code,1,1) = 'F');
428: Exception
435: END IF;
436:
437:
438: If l_dummy = 1 then
439: update gms_bc_packets
440: set result_code = 'F65',
441: fc_error_message = decode(fc_error_message,NULL,'FULL_MODE_FAILURE (Related invoice distribution failed) '||p_level,fc_error_message)
442: where packet_id = p_packet_id
443: and substr(result_code,1,1) = 'P'
447: ap_invoice_distributions_all b
448: where (a.invoice_id,a.invoice_distribution_id) in
449: (select document_header_id,
450: document_distribution_id
451: from gms_bc_packets gbc
452: where gbc.packet_id = p_packet_id
453: and substr(gbc.result_code,1,1) = 'F'
454: and gbc.document_type = 'AP'
455: and gbc.parent_bc_packet_id is null)
462: AP_SELF_ASSESSED_TAX_DIST_ALL D
463: where (c.invoice_id,c.invoice_distribution_id) in
464: (select document_header_id,
465: document_distribution_id
466: from gms_bc_packets gbc
467: where gbc.packet_id = p_packet_id
468: and substr(gbc.result_code,1,1) = 'F'
469: and gbc.document_type = 'AP'
470: and GBC.PARENT_BC_PACKET_ID is null)
498: --If p_mode in ('X','E') then
499:
500: /* Bug 5250793 : Added code such that if an AP Invoice distribution fails fundscheck then the PO matched to that AP also fails
501: with full mode failure. */
502: Update gms_bc_packets bp
503: set bp.status_code = decode(p_mode,'C','F','R'),
504: bp.result_code = decode(substr(bp.result_code,1,1),'P','F65',null,'F65',bp.result_code),
505: bp.fc_error_message = decode(bp.fc_error_message,NULL,'FULL_MODE_FAILURE (X/E/R/U/C mode, INV matched to PO has failed) at stage:'||p_level,bp.fc_error_message)
506: where bp.document_type = 'PO'
506: where bp.document_type = 'PO'
507: and bp.packet_id = p_packet_id
508: and bp.status_code = 'P'
509: and bp.document_distribution_id in ( select distinct apid.po_distribution_id
510: from gms_bc_packets bp1,
511: ap_invoice_distributions_all apid
512: where bp1.packet_id = p_packet_id
513: and bp1.document_type = 'AP'
514: and bp1.document_distribution_id = apid.invoice_distribution_id
515: and substr(bp1.result_code,1,1) = 'F'
516: and apid.po_distribution_id IS NOT NULL ) ;
517:
518:
519: Update gms_bc_packets bp
520: set bp.status_code = decode(p_mode,'C','F','R'),
521: bp.result_code = decode(substr(bp.result_code,1,1),'P','F65',null,'F65',bp.result_code),
522: bp.fc_error_message = decode(bp.fc_error_message,NULL,'FULL_MODE_FAILURE (X/E/R/U/C mode, one of the burden failed) at stage:'||p_level,bp.fc_error_message)
523: where bp.packet_id = p_packet_id
524: and bp.status_code = 'P'
525: and bp.document_type in ('EXP','ENC','AP','PO','REQ')
526: and bp.parent_bc_packet_id is NOT NULL
527: and exists (select /*+ push_subq no_unnest */ 1 /* Hint added for bug 14685623 */
528: from gms_bc_packets bp1
529: where bp1.packet_id = bp.packet_id /* Changed the order for Bug 6043224 */
530: and bp1.parent_bc_packet_id = bp.parent_bc_packet_id
531: and bp1.bc_packet_id <> bp.bc_packet_id /* Uncommented for Bug 6043224 */
532: and bp1.document_type = bp.document_type /* Uncommented for Bug 6043224 */
547: -- 2. Fail all cdls if one cdl failed for an EXP
548:
549: If p_mode = 'X' then
550:
551: Update gms_bc_packets bp
552: set bp.status_code = decode(p_mode,'C','F','R'),
553: bp.result_code = decode(substr(bp.result_code,1,1),'P','F65',null,'F65',bp.result_code),
554: bp.fc_error_message = decode(bp.fc_error_message,NULL,'FULL_MODE_FAILURE (X mode - one of the CDL failed) at stage:'||p_level,bp.fc_error_message)
555: where bp.packet_id = p_packet_id
555: where bp.packet_id = p_packet_id
556: and bp.status_code = 'P'
557: and bp.document_type = 'EXP'
558: and exists (select /*+ push_subq no_unnest */ 1 /* Hint added for bug 14685623 */
559: from gms_bc_packets bp1
560: where bp1.packet_id = bp.packet_id
561: and bp1.document_header_id = bp.document_header_id
562: and bp1.document_distribution_id <> bp.document_distribution_id
563: and bp1.document_type = bp.document_type
601:
602: Cursor c_txn is
603: select adjusted_document_header_id,
604: nvl(ind_compiled_set_id,-1) ind_compiled_set_id
605: from gms_bc_packets
606: where packet_id = p_packetid
607: and document_type = 'ENC'
608: having sum(entered_dr-entered_cr) = 0
609: group by adjusted_document_header_id,
616: gms_error_pkg.gms_debug ( 'p_mode : '||p_mode,'C');
617:
618: If p_mode = 'Check_Adjusted' then
619: -- Fail adjusting txn. If adjusted has not been funds checked -F08
620: update gms_bc_packets gbc
621: set gbc.result_code = 'F08',
622: gbc.award_result_code = 'F08',
623: gbc.top_task_result_code = 'F08',
624: gbc.task_result_code = 'F08',
641: -- Adjusted and adjusting in same packet
642: for recs in c_txn
643: loop
644:
645: update gms_bc_packets gbc
646: set gbc.result_code = 'P82',
647: gbc.award_result_code = 'P82',
648: gbc.top_task_result_code = 'P82',
649: gbc.task_result_code = 'P82',
669:
670: -- R12 Funds management uptake
671: PRAGMA AUTONOMOUS_TRANSACTION;
672:
673: TYPE t_project_id_type IS TABLE OF gms_bc_packets.project_id%type;
674: TYPE t_award_id_type IS TABLE OF gms_bc_packets.award_id%type;
675: TYPE t_task_id_type IS TABLE OF gms_bc_packets.task_id%type;
676: TYPE t_exp_date_type IS TABLE OF gms_bc_packets.expenditure_item_date%type;
677: TYPE t_exp_type_type IS TABLE OF gms_bc_packets.expenditure_type%type;
670: -- R12 Funds management uptake
671: PRAGMA AUTONOMOUS_TRANSACTION;
672:
673: TYPE t_project_id_type IS TABLE OF gms_bc_packets.project_id%type;
674: TYPE t_award_id_type IS TABLE OF gms_bc_packets.award_id%type;
675: TYPE t_task_id_type IS TABLE OF gms_bc_packets.task_id%type;
676: TYPE t_exp_date_type IS TABLE OF gms_bc_packets.expenditure_item_date%type;
677: TYPE t_exp_type_type IS TABLE OF gms_bc_packets.expenditure_type%type;
678: TYPE t_exp_org_type IS TABLE OF gms_bc_packets.expenditure_organization_id%type;
671: PRAGMA AUTONOMOUS_TRANSACTION;
672:
673: TYPE t_project_id_type IS TABLE OF gms_bc_packets.project_id%type;
674: TYPE t_award_id_type IS TABLE OF gms_bc_packets.award_id%type;
675: TYPE t_task_id_type IS TABLE OF gms_bc_packets.task_id%type;
676: TYPE t_exp_date_type IS TABLE OF gms_bc_packets.expenditure_item_date%type;
677: TYPE t_exp_type_type IS TABLE OF gms_bc_packets.expenditure_type%type;
678: TYPE t_exp_org_type IS TABLE OF gms_bc_packets.expenditure_organization_id%type;
679: TYPE t_ind_set_type IS TABLE OF gms_bc_packets.ind_compiled_set_id%type;
672:
673: TYPE t_project_id_type IS TABLE OF gms_bc_packets.project_id%type;
674: TYPE t_award_id_type IS TABLE OF gms_bc_packets.award_id%type;
675: TYPE t_task_id_type IS TABLE OF gms_bc_packets.task_id%type;
676: TYPE t_exp_date_type IS TABLE OF gms_bc_packets.expenditure_item_date%type;
677: TYPE t_exp_type_type IS TABLE OF gms_bc_packets.expenditure_type%type;
678: TYPE t_exp_org_type IS TABLE OF gms_bc_packets.expenditure_organization_id%type;
679: TYPE t_ind_set_type IS TABLE OF gms_bc_packets.ind_compiled_set_id%type;
680:
673: TYPE t_project_id_type IS TABLE OF gms_bc_packets.project_id%type;
674: TYPE t_award_id_type IS TABLE OF gms_bc_packets.award_id%type;
675: TYPE t_task_id_type IS TABLE OF gms_bc_packets.task_id%type;
676: TYPE t_exp_date_type IS TABLE OF gms_bc_packets.expenditure_item_date%type;
677: TYPE t_exp_type_type IS TABLE OF gms_bc_packets.expenditure_type%type;
678: TYPE t_exp_org_type IS TABLE OF gms_bc_packets.expenditure_organization_id%type;
679: TYPE t_ind_set_type IS TABLE OF gms_bc_packets.ind_compiled_set_id%type;
680:
681: t_project_id t_project_id_type;
674: TYPE t_award_id_type IS TABLE OF gms_bc_packets.award_id%type;
675: TYPE t_task_id_type IS TABLE OF gms_bc_packets.task_id%type;
676: TYPE t_exp_date_type IS TABLE OF gms_bc_packets.expenditure_item_date%type;
677: TYPE t_exp_type_type IS TABLE OF gms_bc_packets.expenditure_type%type;
678: TYPE t_exp_org_type IS TABLE OF gms_bc_packets.expenditure_organization_id%type;
679: TYPE t_ind_set_type IS TABLE OF gms_bc_packets.ind_compiled_set_id%type;
680:
681: t_project_id t_project_id_type;
682: t_award_id t_award_id_type;
675: TYPE t_task_id_type IS TABLE OF gms_bc_packets.task_id%type;
676: TYPE t_exp_date_type IS TABLE OF gms_bc_packets.expenditure_item_date%type;
677: TYPE t_exp_type_type IS TABLE OF gms_bc_packets.expenditure_type%type;
678: TYPE t_exp_org_type IS TABLE OF gms_bc_packets.expenditure_organization_id%type;
679: TYPE t_ind_set_type IS TABLE OF gms_bc_packets.ind_compiled_set_id%type;
680:
681: t_project_id t_project_id_type;
682: t_award_id t_award_id_type;
683: t_task_id t_task_id_type;
708: t_exp_date,
709: t_exp_type,
710: t_exp_org,
711: t_ind_set
712: from gms_bc_packets gbc
713: where gbc.packet_id = p_packet_id
714: and gbc.status_code = 'P'
715: and gbc.ind_compiled_set_id is null
716: and nvl(gbc.burden_adjustment_flag,'N') = 'N' -- 3389292
737: so as to ensure that we relieve burden component same as it was reserved for a PO/AP/REQ transaction
738: */
739:
740: FORALL j IN t_project_id.FIRST .. t_project_id.LAST
741: Update /*+ index(gbc GMS_BC_PACKETS_N1) */ gms_bc_packets gbc /*Added hint for bug 5683910 */
742: set ind_compiled_Set_id = (nvl((select ind_compiled_set_id from gms_award_distributions
743: where document_type = gbc.document_type
744: and ((document_type = 'AP' and
745: invoice_id = gbc.document_header_id and
774:
775: -- Update 1 : For Adjusting (Reversing) transactions whose original transaction
776: -- is in the same packet
777:
778: Update gms_bc_packets gbc
779: set gbc.ind_compiled_Set_id = (Select gbc1.ind_compiled_set_id
780: from gms_bc_packets gbc1
781: where gbc1.packet_id = p_packet_id
782: and gbc1.document_header_id = gbc.adjusted_document_header_id
776: -- is in the same packet
777:
778: Update gms_bc_packets gbc
779: set gbc.ind_compiled_Set_id = (Select gbc1.ind_compiled_set_id
780: from gms_bc_packets gbc1
781: where gbc1.packet_id = p_packet_id
782: and gbc1.document_header_id = gbc.adjusted_document_header_id
783: /* bug 6414366 start */
784: and gbc1.document_distribution_id =
782: and gbc1.document_header_id = gbc.adjusted_document_header_id
783: /* bug 6414366 start */
784: and gbc1.document_distribution_id =
785: (select max(gbc2.document_distribution_id)
786: from gms_bc_packets gbc2
787: where gbc2.packet_id = p_packet_id
788: and gbc2.document_header_id = gbc.adjusted_document_header_id))
789: /* bug 6414366 end */
790: where gbc.packet_id = p_packet_id
798: gms_error_pkg.gms_debug ( 'After Update 1','C');
799: -- Bug#6075039 Modified the sub query for performance issue.
800: -- Update 2 : For Adjusting (Reversing) transactions whose original transaction
801: -- was funds checked earlier
802: Update gms_bc_packets gbc
803: set gbc.ind_compiled_Set_id = (Select nvl(gei.ind_compiled_set_id, adl.ind_compiled_set_id) --Bug 5122879
804: from gms_encumbrance_items gei,
805: gms_award_distributions adl
806: where adl.expenditure_item_id =gbc.adjusted_document_header_id
826:
827: /*--------------------------------------------------------------------------------------------------------
828: -- This procedure updates table values
829: -- TABLE Columns
830: -- GMS_BC_PACKETS STATUS_CODE
831: -- GMS_AWARD_DISTRIBUTIONS FC_STATUS
832: -- RESOURCE_LIST_MEMBER_ID
833: -- BUD_TASK_ID(Budgeted Task)
834: -- BUD_RES_LIST_MEMBER_ID(Budgeted rlmi)
857:
858: x_dummy NUMBER; -- Bug 2181546, Added
859: CURSOR c_failed_packet IS -- Bug 2181546, Added
860: SELECT 1
861: FROM gms_bc_packets
862: WHERE packet_id = p_packet_id
863: AND SUBSTR (nvl(result_code,'F65'), 1, 1) = 'F' ;
864:
865: /* Introduced for Bug# 4159238 (BaseBug#4292763)*/
863: AND SUBSTR (nvl(result_code,'F65'), 1, 1) = 'F' ;
864:
865: /* Introduced for Bug# 4159238 (BaseBug#4292763)*/
866:
867: TYPE tab_doc_head_id IS TABLE OF gms_bc_packets.document_header_id%TYPE;
868: TYPE tab_doc_type IS TABLE OF gms_bc_packets.document_type%TYPE;
869: TYPE tab_res_code IS TABLE OF gms_bc_packets.result_code%TYPE;
870: TYPE tab_sta_code IS TABLE OF gms_bc_packets.status_code%TYPE;
871: TYPE tab_dr_code IS TABLE OF gms_bc_packets.entered_dr%TYPE;
864:
865: /* Introduced for Bug# 4159238 (BaseBug#4292763)*/
866:
867: TYPE tab_doc_head_id IS TABLE OF gms_bc_packets.document_header_id%TYPE;
868: TYPE tab_doc_type IS TABLE OF gms_bc_packets.document_type%TYPE;
869: TYPE tab_res_code IS TABLE OF gms_bc_packets.result_code%TYPE;
870: TYPE tab_sta_code IS TABLE OF gms_bc_packets.status_code%TYPE;
871: TYPE tab_dr_code IS TABLE OF gms_bc_packets.entered_dr%TYPE;
872: TYPE tab_cr_code IS TABLE OF gms_bc_packets.entered_cr%TYPE;
865: /* Introduced for Bug# 4159238 (BaseBug#4292763)*/
866:
867: TYPE tab_doc_head_id IS TABLE OF gms_bc_packets.document_header_id%TYPE;
868: TYPE tab_doc_type IS TABLE OF gms_bc_packets.document_type%TYPE;
869: TYPE tab_res_code IS TABLE OF gms_bc_packets.result_code%TYPE;
870: TYPE tab_sta_code IS TABLE OF gms_bc_packets.status_code%TYPE;
871: TYPE tab_dr_code IS TABLE OF gms_bc_packets.entered_dr%TYPE;
872: TYPE tab_cr_code IS TABLE OF gms_bc_packets.entered_cr%TYPE;
873: TYPE tab_bud_task_id IS TABLE OF gms_bc_packets.bud_task_id%TYPE;
866:
867: TYPE tab_doc_head_id IS TABLE OF gms_bc_packets.document_header_id%TYPE;
868: TYPE tab_doc_type IS TABLE OF gms_bc_packets.document_type%TYPE;
869: TYPE tab_res_code IS TABLE OF gms_bc_packets.result_code%TYPE;
870: TYPE tab_sta_code IS TABLE OF gms_bc_packets.status_code%TYPE;
871: TYPE tab_dr_code IS TABLE OF gms_bc_packets.entered_dr%TYPE;
872: TYPE tab_cr_code IS TABLE OF gms_bc_packets.entered_cr%TYPE;
873: TYPE tab_bud_task_id IS TABLE OF gms_bc_packets.bud_task_id%TYPE;
874: TYPE tab_proj_id IS TABLE OF gms_bc_packets.project_id%TYPE;
867: TYPE tab_doc_head_id IS TABLE OF gms_bc_packets.document_header_id%TYPE;
868: TYPE tab_doc_type IS TABLE OF gms_bc_packets.document_type%TYPE;
869: TYPE tab_res_code IS TABLE OF gms_bc_packets.result_code%TYPE;
870: TYPE tab_sta_code IS TABLE OF gms_bc_packets.status_code%TYPE;
871: TYPE tab_dr_code IS TABLE OF gms_bc_packets.entered_dr%TYPE;
872: TYPE tab_cr_code IS TABLE OF gms_bc_packets.entered_cr%TYPE;
873: TYPE tab_bud_task_id IS TABLE OF gms_bc_packets.bud_task_id%TYPE;
874: TYPE tab_proj_id IS TABLE OF gms_bc_packets.project_id%TYPE;
875: TYPE tab_res_list_mem_id IS TABLE OF gms_bc_packets.resource_list_member_id%TYPE;
868: TYPE tab_doc_type IS TABLE OF gms_bc_packets.document_type%TYPE;
869: TYPE tab_res_code IS TABLE OF gms_bc_packets.result_code%TYPE;
870: TYPE tab_sta_code IS TABLE OF gms_bc_packets.status_code%TYPE;
871: TYPE tab_dr_code IS TABLE OF gms_bc_packets.entered_dr%TYPE;
872: TYPE tab_cr_code IS TABLE OF gms_bc_packets.entered_cr%TYPE;
873: TYPE tab_bud_task_id IS TABLE OF gms_bc_packets.bud_task_id%TYPE;
874: TYPE tab_proj_id IS TABLE OF gms_bc_packets.project_id%TYPE;
875: TYPE tab_res_list_mem_id IS TABLE OF gms_bc_packets.resource_list_member_id%TYPE;
876: TYPE tab_doc_dist_id IS TABLE OF gms_bc_packets.document_distribution_id%TYPE;
869: TYPE tab_res_code IS TABLE OF gms_bc_packets.result_code%TYPE;
870: TYPE tab_sta_code IS TABLE OF gms_bc_packets.status_code%TYPE;
871: TYPE tab_dr_code IS TABLE OF gms_bc_packets.entered_dr%TYPE;
872: TYPE tab_cr_code IS TABLE OF gms_bc_packets.entered_cr%TYPE;
873: TYPE tab_bud_task_id IS TABLE OF gms_bc_packets.bud_task_id%TYPE;
874: TYPE tab_proj_id IS TABLE OF gms_bc_packets.project_id%TYPE;
875: TYPE tab_res_list_mem_id IS TABLE OF gms_bc_packets.resource_list_member_id%TYPE;
876: TYPE tab_doc_dist_id IS TABLE OF gms_bc_packets.document_distribution_id%TYPE;
877: TYPE tab_task_id IS TABLE OF gms_bc_packets.task_id%TYPE;
870: TYPE tab_sta_code IS TABLE OF gms_bc_packets.status_code%TYPE;
871: TYPE tab_dr_code IS TABLE OF gms_bc_packets.entered_dr%TYPE;
872: TYPE tab_cr_code IS TABLE OF gms_bc_packets.entered_cr%TYPE;
873: TYPE tab_bud_task_id IS TABLE OF gms_bc_packets.bud_task_id%TYPE;
874: TYPE tab_proj_id IS TABLE OF gms_bc_packets.project_id%TYPE;
875: TYPE tab_res_list_mem_id IS TABLE OF gms_bc_packets.resource_list_member_id%TYPE;
876: TYPE tab_doc_dist_id IS TABLE OF gms_bc_packets.document_distribution_id%TYPE;
877: TYPE tab_task_id IS TABLE OF gms_bc_packets.task_id%TYPE;
878: TYPE tab_exp_item_date IS TABLE OF gms_bc_packets.expenditure_item_date%TYPE;
871: TYPE tab_dr_code IS TABLE OF gms_bc_packets.entered_dr%TYPE;
872: TYPE tab_cr_code IS TABLE OF gms_bc_packets.entered_cr%TYPE;
873: TYPE tab_bud_task_id IS TABLE OF gms_bc_packets.bud_task_id%TYPE;
874: TYPE tab_proj_id IS TABLE OF gms_bc_packets.project_id%TYPE;
875: TYPE tab_res_list_mem_id IS TABLE OF gms_bc_packets.resource_list_member_id%TYPE;
876: TYPE tab_doc_dist_id IS TABLE OF gms_bc_packets.document_distribution_id%TYPE;
877: TYPE tab_task_id IS TABLE OF gms_bc_packets.task_id%TYPE;
878: TYPE tab_exp_item_date IS TABLE OF gms_bc_packets.expenditure_item_date%TYPE;
879: TYPE tab_award_id IS TABLE OF gms_bc_packets.award_id%TYPE;
872: TYPE tab_cr_code IS TABLE OF gms_bc_packets.entered_cr%TYPE;
873: TYPE tab_bud_task_id IS TABLE OF gms_bc_packets.bud_task_id%TYPE;
874: TYPE tab_proj_id IS TABLE OF gms_bc_packets.project_id%TYPE;
875: TYPE tab_res_list_mem_id IS TABLE OF gms_bc_packets.resource_list_member_id%TYPE;
876: TYPE tab_doc_dist_id IS TABLE OF gms_bc_packets.document_distribution_id%TYPE;
877: TYPE tab_task_id IS TABLE OF gms_bc_packets.task_id%TYPE;
878: TYPE tab_exp_item_date IS TABLE OF gms_bc_packets.expenditure_item_date%TYPE;
879: TYPE tab_award_id IS TABLE OF gms_bc_packets.award_id%TYPE;
880: TYPE tab_exp_orgnzt_id IS TABLE OF gms_bc_packets.expenditure_organization_id%TYPE;
873: TYPE tab_bud_task_id IS TABLE OF gms_bc_packets.bud_task_id%TYPE;
874: TYPE tab_proj_id IS TABLE OF gms_bc_packets.project_id%TYPE;
875: TYPE tab_res_list_mem_id IS TABLE OF gms_bc_packets.resource_list_member_id%TYPE;
876: TYPE tab_doc_dist_id IS TABLE OF gms_bc_packets.document_distribution_id%TYPE;
877: TYPE tab_task_id IS TABLE OF gms_bc_packets.task_id%TYPE;
878: TYPE tab_exp_item_date IS TABLE OF gms_bc_packets.expenditure_item_date%TYPE;
879: TYPE tab_award_id IS TABLE OF gms_bc_packets.award_id%TYPE;
880: TYPE tab_exp_orgnzt_id IS TABLE OF gms_bc_packets.expenditure_organization_id%TYPE;
881: TYPE tab_packet_id IS TABLE OF gms_bc_packets.packet_id%TYPE;
874: TYPE tab_proj_id IS TABLE OF gms_bc_packets.project_id%TYPE;
875: TYPE tab_res_list_mem_id IS TABLE OF gms_bc_packets.resource_list_member_id%TYPE;
876: TYPE tab_doc_dist_id IS TABLE OF gms_bc_packets.document_distribution_id%TYPE;
877: TYPE tab_task_id IS TABLE OF gms_bc_packets.task_id%TYPE;
878: TYPE tab_exp_item_date IS TABLE OF gms_bc_packets.expenditure_item_date%TYPE;
879: TYPE tab_award_id IS TABLE OF gms_bc_packets.award_id%TYPE;
880: TYPE tab_exp_orgnzt_id IS TABLE OF gms_bc_packets.expenditure_organization_id%TYPE;
881: TYPE tab_packet_id IS TABLE OF gms_bc_packets.packet_id%TYPE;
882: TYPE tab_bc_packet_id IS TABLE OF gms_bc_packets.bc_packet_id%TYPE;
875: TYPE tab_res_list_mem_id IS TABLE OF gms_bc_packets.resource_list_member_id%TYPE;
876: TYPE tab_doc_dist_id IS TABLE OF gms_bc_packets.document_distribution_id%TYPE;
877: TYPE tab_task_id IS TABLE OF gms_bc_packets.task_id%TYPE;
878: TYPE tab_exp_item_date IS TABLE OF gms_bc_packets.expenditure_item_date%TYPE;
879: TYPE tab_award_id IS TABLE OF gms_bc_packets.award_id%TYPE;
880: TYPE tab_exp_orgnzt_id IS TABLE OF gms_bc_packets.expenditure_organization_id%TYPE;
881: TYPE tab_packet_id IS TABLE OF gms_bc_packets.packet_id%TYPE;
882: TYPE tab_bc_packet_id IS TABLE OF gms_bc_packets.bc_packet_id%TYPE;
883: TYPE tab_exp_type IS TABLE OF gms_bc_packets.expenditure_type%TYPE;
876: TYPE tab_doc_dist_id IS TABLE OF gms_bc_packets.document_distribution_id%TYPE;
877: TYPE tab_task_id IS TABLE OF gms_bc_packets.task_id%TYPE;
878: TYPE tab_exp_item_date IS TABLE OF gms_bc_packets.expenditure_item_date%TYPE;
879: TYPE tab_award_id IS TABLE OF gms_bc_packets.award_id%TYPE;
880: TYPE tab_exp_orgnzt_id IS TABLE OF gms_bc_packets.expenditure_organization_id%TYPE;
881: TYPE tab_packet_id IS TABLE OF gms_bc_packets.packet_id%TYPE;
882: TYPE tab_bc_packet_id IS TABLE OF gms_bc_packets.bc_packet_id%TYPE;
883: TYPE tab_exp_type IS TABLE OF gms_bc_packets.expenditure_type%TYPE;
884: TYPE tab_ind_comp_setid IS TABLE OF gms_bc_packets.ind_compiled_set_id%TYPE;
877: TYPE tab_task_id IS TABLE OF gms_bc_packets.task_id%TYPE;
878: TYPE tab_exp_item_date IS TABLE OF gms_bc_packets.expenditure_item_date%TYPE;
879: TYPE tab_award_id IS TABLE OF gms_bc_packets.award_id%TYPE;
880: TYPE tab_exp_orgnzt_id IS TABLE OF gms_bc_packets.expenditure_organization_id%TYPE;
881: TYPE tab_packet_id IS TABLE OF gms_bc_packets.packet_id%TYPE;
882: TYPE tab_bc_packet_id IS TABLE OF gms_bc_packets.bc_packet_id%TYPE;
883: TYPE tab_exp_type IS TABLE OF gms_bc_packets.expenditure_type%TYPE;
884: TYPE tab_ind_comp_setid IS TABLE OF gms_bc_packets.ind_compiled_set_id%TYPE;
885: TYPE tab_set_of_books_id IS TABLE OF gms_bc_packets.set_of_books_id%TYPE; --Bug 5845974
878: TYPE tab_exp_item_date IS TABLE OF gms_bc_packets.expenditure_item_date%TYPE;
879: TYPE tab_award_id IS TABLE OF gms_bc_packets.award_id%TYPE;
880: TYPE tab_exp_orgnzt_id IS TABLE OF gms_bc_packets.expenditure_organization_id%TYPE;
881: TYPE tab_packet_id IS TABLE OF gms_bc_packets.packet_id%TYPE;
882: TYPE tab_bc_packet_id IS TABLE OF gms_bc_packets.bc_packet_id%TYPE;
883: TYPE tab_exp_type IS TABLE OF gms_bc_packets.expenditure_type%TYPE;
884: TYPE tab_ind_comp_setid IS TABLE OF gms_bc_packets.ind_compiled_set_id%TYPE;
885: TYPE tab_set_of_books_id IS TABLE OF gms_bc_packets.set_of_books_id%TYPE; --Bug 5845974
886:
879: TYPE tab_award_id IS TABLE OF gms_bc_packets.award_id%TYPE;
880: TYPE tab_exp_orgnzt_id IS TABLE OF gms_bc_packets.expenditure_organization_id%TYPE;
881: TYPE tab_packet_id IS TABLE OF gms_bc_packets.packet_id%TYPE;
882: TYPE tab_bc_packet_id IS TABLE OF gms_bc_packets.bc_packet_id%TYPE;
883: TYPE tab_exp_type IS TABLE OF gms_bc_packets.expenditure_type%TYPE;
884: TYPE tab_ind_comp_setid IS TABLE OF gms_bc_packets.ind_compiled_set_id%TYPE;
885: TYPE tab_set_of_books_id IS TABLE OF gms_bc_packets.set_of_books_id%TYPE; --Bug 5845974
886:
887: tdocument_header_id tab_doc_head_id;
880: TYPE tab_exp_orgnzt_id IS TABLE OF gms_bc_packets.expenditure_organization_id%TYPE;
881: TYPE tab_packet_id IS TABLE OF gms_bc_packets.packet_id%TYPE;
882: TYPE tab_bc_packet_id IS TABLE OF gms_bc_packets.bc_packet_id%TYPE;
883: TYPE tab_exp_type IS TABLE OF gms_bc_packets.expenditure_type%TYPE;
884: TYPE tab_ind_comp_setid IS TABLE OF gms_bc_packets.ind_compiled_set_id%TYPE;
885: TYPE tab_set_of_books_id IS TABLE OF gms_bc_packets.set_of_books_id%TYPE; --Bug 5845974
886:
887: tdocument_header_id tab_doc_head_id;
888: tdocument_type tab_doc_type;
881: TYPE tab_packet_id IS TABLE OF gms_bc_packets.packet_id%TYPE;
882: TYPE tab_bc_packet_id IS TABLE OF gms_bc_packets.bc_packet_id%TYPE;
883: TYPE tab_exp_type IS TABLE OF gms_bc_packets.expenditure_type%TYPE;
884: TYPE tab_ind_comp_setid IS TABLE OF gms_bc_packets.ind_compiled_set_id%TYPE;
885: TYPE tab_set_of_books_id IS TABLE OF gms_bc_packets.set_of_books_id%TYPE; --Bug 5845974
886:
887: tdocument_header_id tab_doc_head_id;
888: tdocument_type tab_doc_type;
889: tresult_code tab_res_code;
916: document_distribution_id, task_id, expenditure_item_date,
917: expenditure_type , -- Bug 3003584
918: award_id, expenditure_organization_id, packet_id,
919: bc_packet_id, ind_compiled_set_id -- Added for bug : 2927485
920: FROM gms_bc_packets
921: WHERE packet_id = p_packet_id
922: AND parent_bc_packet_id IS NULL
923: AND nvl(burden_adjustment_flag,'N') = 'N'
924: AND status_code in ('A','B') --Added to fix bug 2138376 from 'B'*/
932: expenditure_type , -- Bug 3003584
933: award_id, expenditure_organization_id, packet_id,
934: bc_packet_id, ind_compiled_set_id, -- Added for bug : 2927485
935: set_of_books_id --Bug 5845974
936: FROM gms_bc_packets
937: WHERE packet_id = p_packet_id
938: AND parent_bc_packet_id IS NULL
939: AND nvl(burden_adjustment_flag,'N') = 'N'
940: AND status_code in ('A','B') --Added to fix bug 2138376 from 'B'*/
947: gbp.result_code,
948: gbp.document_distribution_id,
949: adl.ind_compiled_set_id,
950: gbp.packet_id
951: FROM gms_bc_packets gbp,
952: gms_award_distributions adl
953: WHERE gbp.document_header_id = adl.expenditure_item_id
954: and gbp.document_distribution_id = adl.adl_line_num
955: and gbp.packet_id = p_packet_id
985:
986: -- ---------------------------------------------+
987: -- FULL MODE: FAILURE
988: -- ---------------------------------------------+
989: UPDATE gms_bc_packets
990: SET status_code = decode(p_mode,'S','E','C','F','R'),
991: result_code =
992: DECODE (SUBSTR (NVL (result_code, 'F65'), 1, 1), 'P','F65', NVL(result_code,'F65')), --Bug 2092791 Added NVL Clause
993: fc_error_message = decode(fc_error_message,NULL,g_error_procedure_name,fc_error_message)
997:
998: -- ---------------------------------------------+
999: -- FULL MODE: PASS
1000: -- ---------------------------------------------+
1001: UPDATE gms_bc_packets
1002: SET status_code = decode(p_mode,'S','S','B','B','C','C','A')
1003: WHERE packet_id = p_packet_id;
1004: IF g_debug = 'Y' THEN
1005: gms_error_pkg.gms_debug ('STATUS_CODE_UPDATE - SUBMIT UPDATE FOR PASS TRANSACTIONS', 'C');
1011:
1012: -- ELSIF ( ( NVL(p_mode,'R') in ('R') and NVL(p_partial,'N') = 'Y' )
1013: -- OR ( NVL(p_mode,'R') in ('E'))) THEN
1014:
1015: UPDATE gms_bc_packets
1016: SET status_code = DECODE (SUBSTR (nvl(result_code,'F65'), 1, 1), 'P', 'A', 'R'),
1017: fc_error_message = decode(fc_error_message,NULL,g_error_procedure_name,fc_error_message)
1018: WHERE packet_id = p_packet_id;
1019:
1242: RAISE; -- Bug 2181546, Added
1243: END status_code_update;
1244:
1245: ----------------------------------------------------------------------------------------------------------
1246: -- Procedure to update gms_bc_packets when there is a failure ..
1247: -- This Procedure updates
1248: -- status_code ,
1249: -- result_code at Award Level,Task Level,Resource Group Level
1250: -- fc_error_message
1268:
1269: IF p_bc_packet_id is NULL THEN
1270: g_error_stage := 'RESULT_CODE:PACK_ID';
1271:
1272: UPDATE gms_bc_packets
1273: SET status_code = decode(status_code,'P',p_status_code,'I',p_status_code,status_code),
1274: result_code = decode(substr(result_code,1,1),'F',result_code,p_result_code),
1275: fc_error_message = decode(fc_error_message,null,p_fc_error_message,fc_error_message)
1276: WHERE packet_id = p_packet_id;
1276: WHERE packet_id = p_packet_id;
1277: ELSE
1278: g_error_stage := 'RESULT_CODE:BC_PACK_ID';
1279:
1280: UPDATE gms_bc_packets
1281: SET status_code = decode(status_code,'P',p_status_code,'I',p_status_code,status_code),
1282: result_code = decode(substr(result_code,1,1),'F',result_code,p_result_code),
1283: fc_error_message = decode(fc_error_message,null,p_fc_error_message,fc_error_message)
1284: WHERE packet_id = p_packet_id
1685: END misc_synch_adls;
1686:
1687: -- R12 Funds Management Uptake : Obsolete data_transfer_failure procedure as this logic is handled in copy_gl_pkt_to_gms_pkt
1688: --============================================================================================
1689: -- Bug 2899151 : This procedure will fail gl_bc_packets and gms_bc_packets
1690: -- This procedure will be used when all records in gl_bc_packets (corresponding to sponsored)
1691: -- and not existing in gms_bc_packets.
1692: --============================================================================================
1693:
1687: -- R12 Funds Management Uptake : Obsolete data_transfer_failure procedure as this logic is handled in copy_gl_pkt_to_gms_pkt
1688: --============================================================================================
1689: -- Bug 2899151 : This procedure will fail gl_bc_packets and gms_bc_packets
1690: -- This procedure will be used when all records in gl_bc_packets (corresponding to sponsored)
1691: -- and not existing in gms_bc_packets.
1692: --============================================================================================
1693:
1694:
1695: -- =====================================================================================================
1709: l_count NUMBER;
1710:
1711: CURSOR C_count_rejected_rec IS
1712: SELECT count(*)
1713: FROM gms_bc_packets
1714: WHERE packet_id = p_packet_id
1715: AND status_code in ( 'I' ,'P')
1716: AND substr(result_code,1,1) = 'F' ;
1717:
1761: gms_error_pkg.gms_debug ('UPDATE_BC_PKT_BRC_STATUS'||':'|| 'Updating gms packets to Full mode failure','C');
1762: END IF;
1763:
1764: -- If failure, update result/status code
1765: Update gms_bc_packets
1766: set status_code = decode(p_mode,'C','F','R'),
1767: result_code =decode(result_code,null,'F65',
1768: decode(substr(result_code,1,1),'P','F65',result_code)),
1769: fc_error_message = decode(fc_error_message,NULL,
1778:
1779: END UPDATE_BC_PKT_BRC_STATUS;
1780:
1781: -- =====================================================================================================
1782: -- R12 Funds Managment Uptake : New autonomous procedure to insert records into gms_bc_packets and also
1783: -- updates failed result and status codes on gms packets in case of any failures during loding.
1784: -- This procedure will be fired from main session procedure copy_gl_pkt_to_gms_pkt which in turn is
1785: -- called from GL main budgetory control API.
1786: -- Input parameters : PLSQL tables storing data associated with gl pkts and AP/PO/REQ tables as the
1857:
1858: IF l_rec_count > 0 Then
1859:
1860: IF g_debug = 'Y' THEN
1861: gms_error_pkg.gms_debug ('Load_gms_pkts'||':'|| 'Starting loop to insert '||l_rec_count||'into gms_bc_packets','C');
1862: END IF;
1863:
1864: FORALL i IN 1 .. l_rec_count
1865: INSERT INTO gms_bc_packets
1861: gms_error_pkg.gms_debug ('Load_gms_pkts'||':'|| 'Starting loop to insert '||l_rec_count||'into gms_bc_packets','C');
1862: END IF;
1863:
1864: FORALL i IN 1 .. l_rec_count
1865: INSERT INTO gms_bc_packets
1866: (packet_id,
1867: set_of_books_id,
1868: je_source_name,
1869: je_category_name,
1923: ,p_doc_header_id_tab(i)
1924: ,p_doc_dist_id_tab(i)
1925: ,'N' -- For GMSIP 'N' should not cause an issue
1926: ,NULL
1927: ,gms_bc_packets_s.NEXTVAL
1928: ,p_vendor_id_tab(i)
1929: ,p_exp_category_tab(i)
1930: ,p_revenue_category_tab(i)
1931: ,l_request_id
1940: FOR i IN 1 .. l_rec_count LOOP
1941:
1942: IF SUBSTR(p_result_code_tab(i),1,1) = 'F' THEN
1943:
1944: UPDATE gms_bc_packets
1945: SET result_code = 'F65',
1946: fc_error_message = decode(fc_error_message,NULL,'Load_gms_pkts:Full mode failure',fc_error_message)
1947: WHERE packet_id = p_packet_id
1948: and SUBSTR(result_code,1,1) <> 'F' ;
2141: p_entered_dr => l_entered_dr,
2142: p_entered_cr => l_entered_cr);
2143:
2144: /* Bug 5369296 : If the AP distribution is a reversing distribution (i.e parent_reversal_id is not null) ,
2145: then the burdenable raw cost on the amount/quantity variance records in gms_bc_packets is stamped as 0.
2146: This is because after cancelling an invoice matched to a PO with quantity/amount variance the
2147: burdenable raw cost for the reversing distribution is populated on the basis of that populated in
2148: gms_award_distributions for the original distribution . The burdenable raw cost populated in
2149: gms_award_distributions for the original distribution includes the burdenable raw cost for both the normal
2147: burdenable raw cost for the reversing distribution is populated on the basis of that populated in
2148: gms_award_distributions for the original distribution . The burdenable raw cost populated in
2149: gms_award_distributions for the original distribution includes the burdenable raw cost for both the normal
2150: distribution amount and the quantity/amount variance amount. So during invoice cancel we should not
2151: create the burden for the amount/quantity variance record in gms_bc_packets i.e the burdenable raw cost on the
2152: quantity/amount variance record in gms_bc_packets for the reversing distribution should be zero. */
2153:
2154: IF g_parent_reversal_id_tab(p_copy_from_index) is NOT NULL then
2155: g_burdenable_raw_cost_tab(l_new_rec_index):= 0;
2148: gms_award_distributions for the original distribution . The burdenable raw cost populated in
2149: gms_award_distributions for the original distribution includes the burdenable raw cost for both the normal
2150: distribution amount and the quantity/amount variance amount. So during invoice cancel we should not
2151: create the burden for the amount/quantity variance record in gms_bc_packets i.e the burdenable raw cost on the
2152: quantity/amount variance record in gms_bc_packets for the reversing distribution should be zero. */
2153:
2154: IF g_parent_reversal_id_tab(p_copy_from_index) is NOT NULL then
2155: g_burdenable_raw_cost_tab(l_new_rec_index):= 0;
2156: END IF;
2220: -- to AP/PO/REQ tables uncommited gets fired in this main session procedure and insert/update code
2221: -- gets fired in new autonomous procedures.
2222: --
2223: -- This Function fetches required data from PO/AP/REQ and stores in PLSQL tables.Later fires autonomous
2224: -- procedure to insert into gms_bc_packets and update result codes.This is fired from main GL budgetory
2225: -- control API.
2226: -- for EXP and AP -Interface, insert is through gms_pa_costing_pkg
2227: -- for ENC insert into gms_bc_packets takes place through GMS_FC_SYS Package
2228: -- for Budget Submit/Baseline insert into gms_bc_packets takes place through GMS_BUDGET_BALANCE Package
2223: -- This Function fetches required data from PO/AP/REQ and stores in PLSQL tables.Later fires autonomous
2224: -- procedure to insert into gms_bc_packets and update result codes.This is fired from main GL budgetory
2225: -- control API.
2226: -- for EXP and AP -Interface, insert is through gms_pa_costing_pkg
2227: -- for ENC insert into gms_bc_packets takes place through GMS_FC_SYS Package
2228: -- for Budget Submit/Baseline insert into gms_bc_packets takes place through GMS_BUDGET_BALANCE Package
2229: -- =====================================================================================================
2230:
2231: PROCEDURE copy_gl_pkt_to_gms_pkt (p_application_id IN NUMBER,
2224: -- procedure to insert into gms_bc_packets and update result codes.This is fired from main GL budgetory
2225: -- control API.
2226: -- for EXP and AP -Interface, insert is through gms_pa_costing_pkg
2227: -- for ENC insert into gms_bc_packets takes place through GMS_FC_SYS Package
2228: -- for Budget Submit/Baseline insert into gms_bc_packets takes place through GMS_BUDGET_BALANCE Package
2229: -- =====================================================================================================
2230:
2231: PROCEDURE copy_gl_pkt_to_gms_pkt (p_application_id IN NUMBER,
2232: p_mode IN VARCHAR2 DEFAULT 'C',
2239: -- ----------------------------------------------------------------------------------
2240: l_dist_award_id NUMBER;
2241: l_award_dist_option VARCHAR2 (1);
2242: x_adl_rec gms_award_distributions%ROWTYPE;
2243: l_packet_id gms_bc_packets.packet_id%TYPE;
2244:
2245:
2246: -- IP records are fetched by c_req_po_pkt_rec cursor as IP records will
2247: -- also be stored in po_bc_distributions
2503: ,'NONREC_TAX',apext.po_distribution_id
2504: ,NULL) ap_po_distribution_id,*/
2505: /* MAKING THE PO_DISTRIBUTION_ID TO NULL TO HANDLE THE ISSUE OF MISMATCH OF COMMITMENT AND ASI HEADER COMMITMENT BUG#15970881
2506: The po commitment relieveing logic depends upon the po distribution id.if the EI has PO distribution id stamped on it then the
2507: item should be relieved and it should creates the line in CR header in the gms_bc_packets table*/
2508: NULL ap_po_distribution_id,
2509: DECODE(apext.AID_LINE_TYPE_LOOKUP_CODE,'ITEM',DECODE(apext.po_distribution_id,NULL,NULL,apext.bus_flow_po_doc_id)
2510: ,'ACCRUAL',DECODE(apext.po_distribution_id,NULL,NULL,apext.bus_flow_po_doc_id)
2511: ,'NONREC_TAX',DECODE(apext.po_distribution_id,NULL,NULL,apext.bus_flow_po_doc_id)
2806: l_stdinvoice_exists VARCHAR2(1);
2807: l_counter NUMBER;
2808: l_debug_start_counter NUMBER;
2809: l_po_award_id po_distributions_all.award_id%TYPE;
2810: l_po_result_code gms_bc_packets.result_code%TYPE;
2811: l_po_vendor_id po_headers_all.vendor_id%TYPE;
2812: l_po_ind_com_set_id gms_award_distributions.ind_compiled_set_id%TYPE;
2813:
2814: -- Procedure to intialize PLSQL type variables
2945: -- Initializing OUT variables
2946: x_return_code := 'P';
2947: l_award_dist_option := 'N';
2948:
2949: SELECT gms_bc_packets_s.nextval
2950: INTO l_packet_id
2951: FROM dual;
2952:
2953: IF g_debug = 'Y' THEN
3477: l_mode);
3478: END IF;
3479:
3480: -- If code reaches this point means that data has been correctly transferred from
3481: -- gl_bc_packets to gms_bc_packets ..
3482:
3483: <
3484: NULL;
3485:
3512: BEGIN
3513: g_error_procedure_name := 'misc_gms_idc';
3514: SELECT document_type
3515: INTO doc_type
3516: FROM gms_bc_packets
3517: WHERE packet_id = x_packet_id
3518: AND nvl(burden_adjustment_flag,'N') = 'N'
3519: AND ROWNUM = 1;
3520:
3540:
3541: /* Bug 10082739: Reverted the fixes done in previous couple of versions to the below update statement */
3542:
3543: -- populating compiled set id where ever it is null
3544: UPDATE gms_bc_packets gbc
3545: SET ind_compiled_set_id = gms_cost_plus_extn.get_award_cmt_compiled_set_id (
3546: gbc.task_id,
3547: gbc.expenditure_item_date,
3548: gbc.expenditure_type, --Bug 3003584
3558: /* Bug 10082739: Added the below update statement to specifically handle the case of AP distributions where ind_compiled_set_id
3559: was derived as NULL during validation of the invoice, and so should again be derived as NULL when
3560: trying to relieve funds from AP bucket*/
3561:
3562: UPDATE gms_bc_packets gbc
3563: SET ind_compiled_set_id =
3564: (select adl.ind_compiled_set_id from gms_award_distributions adl
3565: where adl.invoice_id = gbc.document_header_id
3566: and adl.invoice_distribution_id = gbc.document_distribution_id
3606:
3607: END IF;
3608:
3609:
3610: INSERT INTO gms_bc_packets
3611: (packet_id,
3612: project_id,
3613: award_id,
3614: task_id,
3664: source_event_id,
3665: session_id,
3666: serial_id,
3667: txn_interface_id) -- Bug 9929155: Added txn_interface_id
3668: SELECT /*+ index(gbc GMS_BC_PACKETS_N1) */ gbc.packet_id, /* Added the index hint for performance - Bug 5656276 */
3669: gbc.project_id,
3670: gbc.award_id,
3671: gbc.task_id,
3672: icc.expenditure_type,
3711: gbc.time_phased_type_code,
3712: gbc.categorization_code,
3713: gbc.request_id,
3714: gbc.gl_bc_packets_rowid,
3715: gms_bc_packets_s.NEXTVAL,
3716: decode(gbc.burden_adjustment_flag,'Y',gbc.parent_bc_packet_id,gbc.bc_packet_id),
3717: -- In case of burden adjustment flag, use parent_bc_packet_id on raw adjsutment line
3718: gbc.vendor_id, --ADDED FOR BUG 16009601
3719: gbc.person_id,
3735: pa_cost_base_cost_codes cbcc, -- Bug 5656276
3736: pa_cost_base_exp_types cbet,
3737: /*pa_ind_compiled_sets ics, Bug 5656276 */
3738: pa_compiled_multipliers cm,
3739: gms_bc_packets gbc
3740: WHERE /*irsr.cost_plus_structure = cbet.cost_plus_structure Bug 5656276 */
3741: et.expenditure_type = icc.expenditure_type -- 2092791 ( RLMI Change)
3742: AND icc.ind_cost_code = cm.ind_cost_code
3743: AND cbet.cost_base = cm.cost_base
3765: RETURN TRUE;
3766: END misc_gms_idc;
3767:
3768: --===============================================================================================
3769: /* This Function updates following setup columns of gms_bc_packets
3770: budget_version_id
3771: amount_type
3772: boundary_code
3773: time_phased_type_code
3775: resource_list_id
3776: effect_on_funds_code
3777: Note : Budget Version Id is updated only if it is null , in case of
3778: Award budget submit/Baseline Process. Budget_version_id is inserted during
3779: insertion of records in gms_bc_packets. So if budget_version_id is
3780: alreay present this procedure will not update budget_version_id.
3781:
3782: The earlier logic of calculating budget_version_id in case of
3783: mode ('S'/'B') is removed, as budget_version_id logic is already
3780: alreay present this procedure will not update budget_version_id.
3781:
3782: The earlier logic of calculating budget_version_id in case of
3783: mode ('S'/'B') is removed, as budget_version_id logic is already
3784: present while inserting records in gms_bc_packets.
3785: */
3786: --===============================================================================================
3787:
3788: FUNCTION initialize_setup (x_packet_id IN NUMBER,
3787:
3788: FUNCTION initialize_setup (x_packet_id IN NUMBER,
3789: p_mode IN VARCHAR2)
3790: RETURN BOOLEAN IS
3791: x_budget_version_id gms_bc_packets.budget_version_id%TYPE;
3792: x_amount_type gms_bc_packets.amount_type%TYPE;
3793: x_boundary_code gms_bc_packets.boundary_code%TYPE;
3794: x_time_phased_type_code gms_bc_packets.time_phased_type_code%TYPE;
3795: x_categorization_code gms_bc_packets.categorization_code%TYPE;
3788: FUNCTION initialize_setup (x_packet_id IN NUMBER,
3789: p_mode IN VARCHAR2)
3790: RETURN BOOLEAN IS
3791: x_budget_version_id gms_bc_packets.budget_version_id%TYPE;
3792: x_amount_type gms_bc_packets.amount_type%TYPE;
3793: x_boundary_code gms_bc_packets.boundary_code%TYPE;
3794: x_time_phased_type_code gms_bc_packets.time_phased_type_code%TYPE;
3795: x_categorization_code gms_bc_packets.categorization_code%TYPE;
3796: x_project_id gms_bc_packets.project_id%TYPE;
3789: p_mode IN VARCHAR2)
3790: RETURN BOOLEAN IS
3791: x_budget_version_id gms_bc_packets.budget_version_id%TYPE;
3792: x_amount_type gms_bc_packets.amount_type%TYPE;
3793: x_boundary_code gms_bc_packets.boundary_code%TYPE;
3794: x_time_phased_type_code gms_bc_packets.time_phased_type_code%TYPE;
3795: x_categorization_code gms_bc_packets.categorization_code%TYPE;
3796: x_project_id gms_bc_packets.project_id%TYPE;
3797: x_award_id gms_bc_packets.award_id%TYPE;
3790: RETURN BOOLEAN IS
3791: x_budget_version_id gms_bc_packets.budget_version_id%TYPE;
3792: x_amount_type gms_bc_packets.amount_type%TYPE;
3793: x_boundary_code gms_bc_packets.boundary_code%TYPE;
3794: x_time_phased_type_code gms_bc_packets.time_phased_type_code%TYPE;
3795: x_categorization_code gms_bc_packets.categorization_code%TYPE;
3796: x_project_id gms_bc_packets.project_id%TYPE;
3797: x_award_id gms_bc_packets.award_id%TYPE;
3798: x_dist_award_id gms_bc_packets.award_id%TYPE;
3791: x_budget_version_id gms_bc_packets.budget_version_id%TYPE;
3792: x_amount_type gms_bc_packets.amount_type%TYPE;
3793: x_boundary_code gms_bc_packets.boundary_code%TYPE;
3794: x_time_phased_type_code gms_bc_packets.time_phased_type_code%TYPE;
3795: x_categorization_code gms_bc_packets.categorization_code%TYPE;
3796: x_project_id gms_bc_packets.project_id%TYPE;
3797: x_award_id gms_bc_packets.award_id%TYPE;
3798: x_dist_award_id gms_bc_packets.award_id%TYPE;
3799: x_resource_list_id gms_bc_packets.resource_list_id%TYPE;
3792: x_amount_type gms_bc_packets.amount_type%TYPE;
3793: x_boundary_code gms_bc_packets.boundary_code%TYPE;
3794: x_time_phased_type_code gms_bc_packets.time_phased_type_code%TYPE;
3795: x_categorization_code gms_bc_packets.categorization_code%TYPE;
3796: x_project_id gms_bc_packets.project_id%TYPE;
3797: x_award_id gms_bc_packets.award_id%TYPE;
3798: x_dist_award_id gms_bc_packets.award_id%TYPE;
3799: x_resource_list_id gms_bc_packets.resource_list_id%TYPE;
3800: x_award_distribution_option VARCHAR2 (10);
3793: x_boundary_code gms_bc_packets.boundary_code%TYPE;
3794: x_time_phased_type_code gms_bc_packets.time_phased_type_code%TYPE;
3795: x_categorization_code gms_bc_packets.categorization_code%TYPE;
3796: x_project_id gms_bc_packets.project_id%TYPE;
3797: x_award_id gms_bc_packets.award_id%TYPE;
3798: x_dist_award_id gms_bc_packets.award_id%TYPE;
3799: x_resource_list_id gms_bc_packets.resource_list_id%TYPE;
3800: x_award_distribution_option VARCHAR2 (10);
3801: CURSOR cur_init_setup IS
3794: x_time_phased_type_code gms_bc_packets.time_phased_type_code%TYPE;
3795: x_categorization_code gms_bc_packets.categorization_code%TYPE;
3796: x_project_id gms_bc_packets.project_id%TYPE;
3797: x_award_id gms_bc_packets.award_id%TYPE;
3798: x_dist_award_id gms_bc_packets.award_id%TYPE;
3799: x_resource_list_id gms_bc_packets.resource_list_id%TYPE;
3800: x_award_distribution_option VARCHAR2 (10);
3801: CURSOR cur_init_setup IS
3802: SELECT DISTINCT project_id,
3795: x_categorization_code gms_bc_packets.categorization_code%TYPE;
3796: x_project_id gms_bc_packets.project_id%TYPE;
3797: x_award_id gms_bc_packets.award_id%TYPE;
3798: x_dist_award_id gms_bc_packets.award_id%TYPE;
3799: x_resource_list_id gms_bc_packets.resource_list_id%TYPE;
3800: x_award_distribution_option VARCHAR2 (10);
3801: CURSOR cur_init_setup IS
3802: SELECT DISTINCT project_id,
3803: award_id,
3801: CURSOR cur_init_setup IS
3802: SELECT DISTINCT project_id,
3803: award_id,
3804: budget_version_id
3805: FROM gms_bc_packets
3806: WHERE packet_id = x_packet_id
3807: AND status_code in ('P','A') --Bug 2143160
3808: ;
3809: BEGIN
3845: DECODE (x_budget_version_id, NULL, pb.budget_version_id, x_budget_version_id)
3846: AND pb.current_flag = DECODE (x_budget_version_id, NULL, 'Y', pb.current_flag);
3847: -- AND pb.budget_status_code = 'B'; -- (This code is commented because in 11I Funds Check is done in
3848: -- Budget Submit mode also )
3849: UPDATE gms_bc_packets
3850: SET budget_version_id = x_budget_version_id,
3851: amount_type = x_amount_type,
3852: boundary_code = x_boundary_code,
3853: time_phased_type_code = x_time_phased_type_code,
3862: AND status_code in ('P','A') --Bug 2143160
3863: ;
3864: EXCEPTION
3865: WHEN NO_DATA_FOUND THEN
3866: UPDATE gms_bc_packets
3867: SET result_code = 'F12',
3868: status_code = decode(p_mode,'S','E','C','F','R')
3869: WHERE packet_id = x_packet_id
3870: AND project_id = x_project_id
3878: IF g_debug = 'Y' THEN
3879: gms_error_pkg.gms_debug ('Initilize_Setup - End ', 'C');
3880: END IF;
3881: IF x_award_id = NVL (x_dist_award_id, -1111) THEN
3882: UPDATE gms_bc_packets
3883: SET status_code = decode(p_mode,'S','E','C','F','R'),
3884: result_code = 'F21',
3885: res_result_code = 'F21',
3886: res_grp_result_code = 'F21',
3918: x_packet_id IN NUMBER,
3919: x_mode IN VARCHAR2,
3920: x_err_code OUT NOCOPY NUMBER,
3921: x_err_buff OUT NOCOPY VARCHAR2) IS
3922: TYPE t_doctype IS TABLE OF gms_bc_packets.document_type%TYPE;
3923:
3924: TYPE t_exptype IS TABLE OF gms_bc_packets.expenditure_type%TYPE;
3925:
3926: TYPE t_orgid IS TABLE OF gms_bc_packets.expenditure_organization_id%TYPE;
3920: x_err_code OUT NOCOPY NUMBER,
3921: x_err_buff OUT NOCOPY VARCHAR2) IS
3922: TYPE t_doctype IS TABLE OF gms_bc_packets.document_type%TYPE;
3923:
3924: TYPE t_exptype IS TABLE OF gms_bc_packets.expenditure_type%TYPE;
3925:
3926: TYPE t_orgid IS TABLE OF gms_bc_packets.expenditure_organization_id%TYPE;
3927:
3928: TYPE t_personid IS TABLE OF gms_bc_packets.person_id%TYPE;
3922: TYPE t_doctype IS TABLE OF gms_bc_packets.document_type%TYPE;
3923:
3924: TYPE t_exptype IS TABLE OF gms_bc_packets.expenditure_type%TYPE;
3925:
3926: TYPE t_orgid IS TABLE OF gms_bc_packets.expenditure_organization_id%TYPE;
3927:
3928: TYPE t_personid IS TABLE OF gms_bc_packets.person_id%TYPE;
3929:
3930: TYPE t_jobid IS TABLE OF gms_bc_packets.job_id%TYPE;
3924: TYPE t_exptype IS TABLE OF gms_bc_packets.expenditure_type%TYPE;
3925:
3926: TYPE t_orgid IS TABLE OF gms_bc_packets.expenditure_organization_id%TYPE;
3927:
3928: TYPE t_personid IS TABLE OF gms_bc_packets.person_id%TYPE;
3929:
3930: TYPE t_jobid IS TABLE OF gms_bc_packets.job_id%TYPE;
3931:
3932: TYPE t_vendorid IS TABLE OF gms_bc_packets.vendor_id%TYPE;
3926: TYPE t_orgid IS TABLE OF gms_bc_packets.expenditure_organization_id%TYPE;
3927:
3928: TYPE t_personid IS TABLE OF gms_bc_packets.person_id%TYPE;
3929:
3930: TYPE t_jobid IS TABLE OF gms_bc_packets.job_id%TYPE;
3931:
3932: TYPE t_vendorid IS TABLE OF gms_bc_packets.vendor_id%TYPE;
3933:
3934: TYPE t_expcat IS TABLE OF gms_bc_packets.expenditure_category%TYPE;
3928: TYPE t_personid IS TABLE OF gms_bc_packets.person_id%TYPE;
3929:
3930: TYPE t_jobid IS TABLE OF gms_bc_packets.job_id%TYPE;
3931:
3932: TYPE t_vendorid IS TABLE OF gms_bc_packets.vendor_id%TYPE;
3933:
3934: TYPE t_expcat IS TABLE OF gms_bc_packets.expenditure_category%TYPE;
3935:
3936: TYPE t_revcat IS TABLE OF gms_bc_packets.revenue_category%TYPE;
3930: TYPE t_jobid IS TABLE OF gms_bc_packets.job_id%TYPE;
3931:
3932: TYPE t_vendorid IS TABLE OF gms_bc_packets.vendor_id%TYPE;
3933:
3934: TYPE t_expcat IS TABLE OF gms_bc_packets.expenditure_category%TYPE;
3935:
3936: TYPE t_revcat IS TABLE OF gms_bc_packets.revenue_category%TYPE;
3937:
3938: TYPE t_catcode IS TABLE OF gms_bc_packets.categorization_code%TYPE;
3932: TYPE t_vendorid IS TABLE OF gms_bc_packets.vendor_id%TYPE;
3933:
3934: TYPE t_expcat IS TABLE OF gms_bc_packets.expenditure_category%TYPE;
3935:
3936: TYPE t_revcat IS TABLE OF gms_bc_packets.revenue_category%TYPE;
3937:
3938: TYPE t_catcode IS TABLE OF gms_bc_packets.categorization_code%TYPE;
3939:
3940: TYPE t_reslist IS TABLE OF gms_bc_packets.resource_list_id%TYPE;
3934: TYPE t_expcat IS TABLE OF gms_bc_packets.expenditure_category%TYPE;
3935:
3936: TYPE t_revcat IS TABLE OF gms_bc_packets.revenue_category%TYPE;
3937:
3938: TYPE t_catcode IS TABLE OF gms_bc_packets.categorization_code%TYPE;
3939:
3940: TYPE t_reslist IS TABLE OF gms_bc_packets.resource_list_id%TYPE;
3941:
3942: TYPE t_rowid IS TABLE OF VARCHAR2 (50);
3936: TYPE t_revcat IS TABLE OF gms_bc_packets.revenue_category%TYPE;
3937:
3938: TYPE t_catcode IS TABLE OF gms_bc_packets.categorization_code%TYPE;
3939:
3940: TYPE t_reslist IS TABLE OF gms_bc_packets.resource_list_id%TYPE;
3941:
3942: TYPE t_rowid IS TABLE OF VARCHAR2 (50);
3943:
3944: TYPE t_rlmi IS TABLE OF gms_bc_packets.resource_list_member_id%TYPE;
3940: TYPE t_reslist IS TABLE OF gms_bc_packets.resource_list_id%TYPE;
3941:
3942: TYPE t_rowid IS TABLE OF VARCHAR2 (50);
3943:
3944: TYPE t_rlmi IS TABLE OF gms_bc_packets.resource_list_member_id%TYPE;
3945:
3946: -- TYPE t_upg_err IS TABLE OF gms_award_distributions.upg_error%TYPE; -- Bug 2178694
3947:
3948: TYPE t_fc_err IS TABLE OF gms_bc_packets.fc_error_message%TYPE;
3944: TYPE t_rlmi IS TABLE OF gms_bc_packets.resource_list_member_id%TYPE;
3945:
3946: -- TYPE t_upg_err IS TABLE OF gms_award_distributions.upg_error%TYPE; -- Bug 2178694
3947:
3948: TYPE t_fc_err IS TABLE OF gms_bc_packets.fc_error_message%TYPE;
3949:
3950: t_doc_type t_doctype;
3951: t_exp_type t_exptype;
3952: t_person_id t_personid;
4017: t_vendor_id,
4018: t_row_id,
4019: t_rlmi_value,
4020: t_fc_error -- Bug 2178694
4021: FROM gms_bc_packets
4022: WHERE packet_id = x_packet_id
4023: AND status_code NOT IN ('F','R') -- Bug 2927485
4024: AND resource_list_member_id is NULL
4025: AND nvl(burden_adjustment_flag ,'N') = 'N' -- 3389292
4096: END LOOP;
4097: --4. Bulk Update
4098: g_error_stage := 'SETUP_RLMI : BULK COLLECT';
4099: FORALL bcpkt_txns IN t_row_id.FIRST .. t_row_id.LAST
4100: UPDATE gms_bc_packets
4101: SET status_code = decode(t_rlmi_value (bcpkt_txns),NULL,decode(x_mode,'S','E','C','F','R'),status_code),
4102: result_code = decode(t_rlmi_value (bcpkt_txns),NULL,'F94',result_code),
4103: resource_list_member_id = t_rlmi_value (bcpkt_txns),
4104: fc_error_message = t_fc_error (bcpkt_txns) -- Bug 2178694
4110:
4111:
4112: ----------------------------------------------------------------------------------------------------------
4113: --Procedure to calulate budgeted task id in packet for a budget version, entry level code and budget entry
4114: --method and update gms_bc_packets for the same set of records having the same combinations.
4115: --A single Update Statment will take care of Updating Budget task id for Following Budget Entry Methods
4116: -- Budget Entry Method
4117: -- P By Project
4118: -- T By Top Task
4124: BEGIN
4125: g_error_procedure_name := 'budget_task_id_update';
4126: -- if the budget entry level in 'L','T','P' -- update directly.
4127: g_error_stage := 'BUD_TASK_UPD :L,P,T';
4128: UPDATE gms_bc_packets bc
4129: SET (bc.bud_task_id, bc.top_task_id) =
4130: (SELECT DECODE (bem.entry_level_code, 'P', 0, 'L', bc.task_id, t.top_task_id),
4131: DECODE (bem.entry_level_code, 'P', 0, t.top_task_id)
4132: FROM pa_budget_entry_methods bem, gms_budget_versions bv,
4153: commit;
4154:
4155: -- if the budget entry level = 'M' and budget at LOWEST TASK
4156: g_error_stage := 'BUD_TASK_UPD :M';
4157: UPDATE gms_bc_packets bc
4158: SET (bc.bud_task_id, bc.top_task_id) =
4159: (SELECT t.task_id,
4160: t.top_task_id
4161: FROM pa_budget_entry_methods bem, gms_budget_versions bv,
4178: -- Added commit for the base bug 3848201
4179: commit;
4180:
4181: -- if the budget entry level = 'M' and budget at TOP TASK
4182: UPDATE gms_bc_packets bc
4183: SET (bc.bud_task_id, bc.top_task_id) =
4184: (SELECT t.task_id,
4185: t.top_task_id
4186: FROM pa_budget_entry_methods bem, gms_budget_versions bv,pa_tasks t
4210:
4211: -- If Bud Task Id is not updated till this point , then update bud_task_id with
4212: -- task_id of expenditure
4213: g_error_stage := 'BUD_TASK_UPD :ELSE';
4214: UPDATE gms_bc_packets bc
4215: SET (bc.bud_task_id, bc.top_task_id) =
4216: (SELECT t.task_id,
4217: t.top_task_id
4218: FROM pa_tasks t
4228: END budget_task_id_update;
4229:
4230: ----------------------------------------------------------------------------------------------------------
4231: -- Procedure to calulate budgeted resource list id in packet for a budget version, entry level code and
4232: -- budget entry method and update gms_bc_packets for the set of records having the same combinations.
4233: ----------------------------------------------------------------------------------------------------------
4234:
4235: PROCEDURE bud_res_list_id_update (
4236: x_packetid IN NUMBER) IS
4239: -- At Resource/Resource Group Level
4240: g_error_stage := 'BUD_RES_UPD :START';
4241:
4242: -- Bug 2605070, Only one stmt is needed to update the parent_resource_id
4243: UPDATE gms_bc_packets gms
4244: SET (parent_resource_id) =
4245: (SELECT pr.parent_member_id
4246: FROM pa_resource_list_members pr
4247: WHERE pr.resource_list_member_id = gms.resource_list_member_id
4254:
4255: ----------------------------------------------------------------------------------------------------------
4256: -- Procedure to update the funds control level code in a packet for a project, award, budget version,
4257: -- budget entry method.
4258: -- update gms_bc_packets for the set of records having the same combinations.
4259: ----------------------------------------------------------------------------------------------------------
4260: PROCEDURE funds_ctrl_level_code (
4261: x_packet_id IN NUMBER) IS
4262:
4268: /* 12649316 */
4269: /* 13410272 added distinct to the select query */
4270: CURSOR award_csr (p_packet_id number) is
4271: select distinct award_id
4272: from gms_bc_packets
4273: where packet_id = p_packet_id;
4274:
4275: BEGIN
4276: g_error_procedure_name := 'funds_ctrl_level_code';
4275: BEGIN
4276: g_error_procedure_name := 'funds_ctrl_level_code';
4277: g_error_stage := 'FUND_CTRL_LEVEL_CODE : A';
4278: -- Award Level
4279: UPDATE gms_bc_packets gms
4280: SET a_funds_control_level_code = (SELECT funds_control_level_code
4281: FROM gms_budgetary_controls gbc
4282: WHERE gbc.project_id = gms.project_id
4283: AND gbc.award_id = gms.award_id
4291: gms_error_pkg.gms_debug('FUNDS_CTRL_LEVEL_CODE - Update for Award Result code Complete ','C');
4292: END IF;
4293: g_error_stage := 'FUND_CTRL_LEVEL_CODE : TT';
4294: -- Top Task Level
4295: UPDATE gms_bc_packets gms
4296: SET tt_funds_control_level_code = (SELECT funds_control_level_code
4297: FROM gms_budgetary_controls gbc
4298: WHERE gbc.project_id = gms.project_id
4299: AND gbc.award_id = gms.award_id
4313: END IF;
4314: g_error_stage := 'FUND_CTRL_LEVEL_CODE : T';
4315:
4316: -- Task Level
4317: UPDATE gms_bc_packets gms
4318: SET t_funds_control_level_code = (SELECT funds_control_level_code
4319: FROM gms_budgetary_controls gbc
4320: WHERE gbc.project_id = gms.project_id
4321: AND gbc.award_id = gms.award_id
4336: --Task level funds control level code should set up only if budget entry method is by task
4337: --For project with resource level budget entry method task_id =0
4338: -- Resource Group Level
4339: g_error_stage := 'FUND_CTRL_LEVEL_CODE : RG';
4340: UPDATE gms_bc_packets gms
4341: SET rg_funds_control_level_code = (SELECT funds_control_level_code
4342: FROM gms_budgetary_controls gbc
4343: WHERE gbc.project_id = gms.project_id
4344: AND gbc.award_id = gms.award_id
4359: END IF;
4360:
4361: -- Resource Level
4362: g_error_stage := 'FUND_CTRL_LEVEL_CODE : R';
4363: UPDATE gms_bc_packets gms
4364: SET r_funds_control_level_code = (SELECT funds_control_level_code
4365: FROM gms_budgetary_controls gbc
4366: WHERE gbc.project_id = gms.project_id
4367: AND gbc.award_id = gms.award_id
4395: fcl_r
4396: from gms_awards_all
4397: where award_id = award_rec.award_id;
4398: /* 12649316 (select award_id
4399: from gms_bc_packets
4400: WHERE packet_id = x_packet_id ); */ /* added for bug 12432376 for debugging*/
4401:
4402: g_error_stage := 'FUND_CTRL_LEVEL_CODE : NONE';
4403: UPDATE gms_bc_packets gms
4399: from gms_bc_packets
4400: WHERE packet_id = x_packet_id ); */ /* added for bug 12432376 for debugging*/
4401:
4402: g_error_stage := 'FUND_CTRL_LEVEL_CODE : NONE';
4403: UPDATE gms_bc_packets gms
4404: SET r_funds_control_level_code =
4405: DECODE (r_funds_control_level_code, NULL, nvl(fcl_r,'N'), r_funds_control_level_code), /* added nvl(fcl_r,'N')for bug 12432376 for debugging*/
4406: rg_funds_control_level_code =
4407: DECODE (rg_funds_control_level_code, NULL, nvl(fcl_r_grp,'N'), rg_funds_control_level_code),/* added nvl(fcl_r_grp,'N') for bug 12432376 for debugging*/
4415: AND award_id = award_rec.award_id /* 12649316 */
4416: AND status_code = 'P'
4417: ;
4418: g_error_stage := 'FUND_CTRL_LEVEL_CODE : NONE';
4419: UPDATE gms_bc_packets gms
4420: SET r_funds_control_level_code =
4421: DECODE (r_funds_control_level_code, NULL, 'N', r_funds_control_level_code),
4422: rg_funds_control_level_code =
4423: DECODE (rg_funds_control_level_code, NULL, 'N', rg_funds_control_level_code),
4443: ----------------------------------------------------------------------------------------------------------
4444: PROCEDURE setup_start_end_date (
4445: x_packetid IN NUMBER,
4446: x_bc_packet_id IN NUMBER,
4447: x_project_id IN gms_bc_packets.project_id%TYPE,
4448: x_award_id IN gms_bc_packets.award_id%TYPE,
4449: x_budget_version_id IN gms_bc_packets.budget_version_id%TYPE,
4450: x_time_phased_type_code IN pa_budget_entry_methods.time_phased_type_code%TYPE,
4451: x_expenditure_item_date IN DATE,
4444: PROCEDURE setup_start_end_date (
4445: x_packetid IN NUMBER,
4446: x_bc_packet_id IN NUMBER,
4447: x_project_id IN gms_bc_packets.project_id%TYPE,
4448: x_award_id IN gms_bc_packets.award_id%TYPE,
4449: x_budget_version_id IN gms_bc_packets.budget_version_id%TYPE,
4450: x_time_phased_type_code IN pa_budget_entry_methods.time_phased_type_code%TYPE,
4451: x_expenditure_item_date IN DATE,
4452: x_amount_type IN gms_awards.amount_type%TYPE,
4445: x_packetid IN NUMBER,
4446: x_bc_packet_id IN NUMBER,
4447: x_project_id IN gms_bc_packets.project_id%TYPE,
4448: x_award_id IN gms_bc_packets.award_id%TYPE,
4449: x_budget_version_id IN gms_bc_packets.budget_version_id%TYPE,
4450: x_time_phased_type_code IN pa_budget_entry_methods.time_phased_type_code%TYPE,
4451: x_expenditure_item_date IN DATE,
4452: x_amount_type IN gms_awards.amount_type%TYPE,
4453: x_boundary_code IN gms_awards.boundary_code%TYPE,
4450: x_time_phased_type_code IN pa_budget_entry_methods.time_phased_type_code%TYPE,
4451: x_expenditure_item_date IN DATE,
4452: x_amount_type IN gms_awards.amount_type%TYPE,
4453: x_boundary_code IN gms_awards.boundary_code%TYPE,
4454: x_set_of_books_id IN gms_bc_packets.set_of_books_id%TYPE,
4455:
4456: -- x_budgeted_task_id IN gms_bc_packets.bud_task_id%TYPE,
4457: -- x_bud_res_list_member_id IN NUMBER,
4458: x_start_date OUT NOCOPY DATE,
4452: x_amount_type IN gms_awards.amount_type%TYPE,
4453: x_boundary_code IN gms_awards.boundary_code%TYPE,
4454: x_set_of_books_id IN gms_bc_packets.set_of_books_id%TYPE,
4455:
4456: -- x_budgeted_task_id IN gms_bc_packets.bud_task_id%TYPE,
4457: -- x_bud_res_list_member_id IN NUMBER,
4458: x_start_date OUT NOCOPY DATE,
4459: x_end_date OUT NOCOPY DATE) IS
4460:
4539: FROM gms_balances
4540: WHERE budget_version_id = x_budget_version_id;
4541: SELECT MAX (expenditure_item_date)
4542: INTO exp_date
4543: FROM gms_bc_packets
4544: WHERE budget_version_id = x_budget_version_id;
4545:
4546: g_budget_version_id := x_budget_version_id;
4547: g_gb_end_date := gb_end_date;
4988:
4989: PROCEDURE call_start_end_date_update (
4990: x_packetid IN NUMBER ,
4991: p_mode IN VARCHAR2) IS
4992: TYPE t_packetid IS TABLE OF gms_bc_packets.packet_id%TYPE;
4993: TYPE t_bcpktid IS TABLE OF gms_bc_packets.bc_packet_id%TYPE;
4994: TYPE t_projid IS TABLE OF gms_bc_packets.project_id%TYPE;
4995: TYPE t_awardid IS TABLE OF gms_bc_packets.award_id%TYPE;
4996: TYPE t_bvid IS TABLE OF gms_bc_packets.budget_version_id%TYPE;
4989: PROCEDURE call_start_end_date_update (
4990: x_packetid IN NUMBER ,
4991: p_mode IN VARCHAR2) IS
4992: TYPE t_packetid IS TABLE OF gms_bc_packets.packet_id%TYPE;
4993: TYPE t_bcpktid IS TABLE OF gms_bc_packets.bc_packet_id%TYPE;
4994: TYPE t_projid IS TABLE OF gms_bc_packets.project_id%TYPE;
4995: TYPE t_awardid IS TABLE OF gms_bc_packets.award_id%TYPE;
4996: TYPE t_bvid IS TABLE OF gms_bc_packets.budget_version_id%TYPE;
4997: TYPE t_tptypecd IS TABLE OF gms_bc_packets.time_phased_type_code%TYPE;
4990: x_packetid IN NUMBER ,
4991: p_mode IN VARCHAR2) IS
4992: TYPE t_packetid IS TABLE OF gms_bc_packets.packet_id%TYPE;
4993: TYPE t_bcpktid IS TABLE OF gms_bc_packets.bc_packet_id%TYPE;
4994: TYPE t_projid IS TABLE OF gms_bc_packets.project_id%TYPE;
4995: TYPE t_awardid IS TABLE OF gms_bc_packets.award_id%TYPE;
4996: TYPE t_bvid IS TABLE OF gms_bc_packets.budget_version_id%TYPE;
4997: TYPE t_tptypecd IS TABLE OF gms_bc_packets.time_phased_type_code%TYPE;
4998: TYPE t_expdate IS TABLE OF gms_bc_packets.expenditure_item_date%TYPE;
4991: p_mode IN VARCHAR2) IS
4992: TYPE t_packetid IS TABLE OF gms_bc_packets.packet_id%TYPE;
4993: TYPE t_bcpktid IS TABLE OF gms_bc_packets.bc_packet_id%TYPE;
4994: TYPE t_projid IS TABLE OF gms_bc_packets.project_id%TYPE;
4995: TYPE t_awardid IS TABLE OF gms_bc_packets.award_id%TYPE;
4996: TYPE t_bvid IS TABLE OF gms_bc_packets.budget_version_id%TYPE;
4997: TYPE t_tptypecd IS TABLE OF gms_bc_packets.time_phased_type_code%TYPE;
4998: TYPE t_expdate IS TABLE OF gms_bc_packets.expenditure_item_date%TYPE;
4999: TYPE t_amttype IS TABLE OF gms_bc_packets.amount_type%TYPE;
4992: TYPE t_packetid IS TABLE OF gms_bc_packets.packet_id%TYPE;
4993: TYPE t_bcpktid IS TABLE OF gms_bc_packets.bc_packet_id%TYPE;
4994: TYPE t_projid IS TABLE OF gms_bc_packets.project_id%TYPE;
4995: TYPE t_awardid IS TABLE OF gms_bc_packets.award_id%TYPE;
4996: TYPE t_bvid IS TABLE OF gms_bc_packets.budget_version_id%TYPE;
4997: TYPE t_tptypecd IS TABLE OF gms_bc_packets.time_phased_type_code%TYPE;
4998: TYPE t_expdate IS TABLE OF gms_bc_packets.expenditure_item_date%TYPE;
4999: TYPE t_amttype IS TABLE OF gms_bc_packets.amount_type%TYPE;
5000: TYPE t_boudrcd IS TABLE OF gms_bc_packets.boundary_code%TYPE;
4993: TYPE t_bcpktid IS TABLE OF gms_bc_packets.bc_packet_id%TYPE;
4994: TYPE t_projid IS TABLE OF gms_bc_packets.project_id%TYPE;
4995: TYPE t_awardid IS TABLE OF gms_bc_packets.award_id%TYPE;
4996: TYPE t_bvid IS TABLE OF gms_bc_packets.budget_version_id%TYPE;
4997: TYPE t_tptypecd IS TABLE OF gms_bc_packets.time_phased_type_code%TYPE;
4998: TYPE t_expdate IS TABLE OF gms_bc_packets.expenditure_item_date%TYPE;
4999: TYPE t_amttype IS TABLE OF gms_bc_packets.amount_type%TYPE;
5000: TYPE t_boudrcd IS TABLE OF gms_bc_packets.boundary_code%TYPE;
5001: TYPE t_sobid IS TABLE OF gms_bc_packets.set_of_books_id%TYPE;
4994: TYPE t_projid IS TABLE OF gms_bc_packets.project_id%TYPE;
4995: TYPE t_awardid IS TABLE OF gms_bc_packets.award_id%TYPE;
4996: TYPE t_bvid IS TABLE OF gms_bc_packets.budget_version_id%TYPE;
4997: TYPE t_tptypecd IS TABLE OF gms_bc_packets.time_phased_type_code%TYPE;
4998: TYPE t_expdate IS TABLE OF gms_bc_packets.expenditure_item_date%TYPE;
4999: TYPE t_amttype IS TABLE OF gms_bc_packets.amount_type%TYPE;
5000: TYPE t_boudrcd IS TABLE OF gms_bc_packets.boundary_code%TYPE;
5001: TYPE t_sobid IS TABLE OF gms_bc_packets.set_of_books_id%TYPE;
5002: TYPE t_startdt IS TABLE OF DATE;
4995: TYPE t_awardid IS TABLE OF gms_bc_packets.award_id%TYPE;
4996: TYPE t_bvid IS TABLE OF gms_bc_packets.budget_version_id%TYPE;
4997: TYPE t_tptypecd IS TABLE OF gms_bc_packets.time_phased_type_code%TYPE;
4998: TYPE t_expdate IS TABLE OF gms_bc_packets.expenditure_item_date%TYPE;
4999: TYPE t_amttype IS TABLE OF gms_bc_packets.amount_type%TYPE;
5000: TYPE t_boudrcd IS TABLE OF gms_bc_packets.boundary_code%TYPE;
5001: TYPE t_sobid IS TABLE OF gms_bc_packets.set_of_books_id%TYPE;
5002: TYPE t_startdt IS TABLE OF DATE;
5003: TYPE t_enddt IS TABLE OF DATE;
4996: TYPE t_bvid IS TABLE OF gms_bc_packets.budget_version_id%TYPE;
4997: TYPE t_tptypecd IS TABLE OF gms_bc_packets.time_phased_type_code%TYPE;
4998: TYPE t_expdate IS TABLE OF gms_bc_packets.expenditure_item_date%TYPE;
4999: TYPE t_amttype IS TABLE OF gms_bc_packets.amount_type%TYPE;
5000: TYPE t_boudrcd IS TABLE OF gms_bc_packets.boundary_code%TYPE;
5001: TYPE t_sobid IS TABLE OF gms_bc_packets.set_of_books_id%TYPE;
5002: TYPE t_startdt IS TABLE OF DATE;
5003: TYPE t_enddt IS TABLE OF DATE;
5004: TYPE t_errcode IS TABLE OF NUMBER;
4997: TYPE t_tptypecd IS TABLE OF gms_bc_packets.time_phased_type_code%TYPE;
4998: TYPE t_expdate IS TABLE OF gms_bc_packets.expenditure_item_date%TYPE;
4999: TYPE t_amttype IS TABLE OF gms_bc_packets.amount_type%TYPE;
5000: TYPE t_boudrcd IS TABLE OF gms_bc_packets.boundary_code%TYPE;
5001: TYPE t_sobid IS TABLE OF gms_bc_packets.set_of_books_id%TYPE;
5002: TYPE t_startdt IS TABLE OF DATE;
5003: TYPE t_enddt IS TABLE OF DATE;
5004: TYPE t_errcode IS TABLE OF NUMBER;
5005: TYPE t_errcbuff IS TABLE OF VARCHAR2(500);
5045: t_set_of_books_id,
5046: t_bc_packet_id,
5047: t_start_date,
5048: t_end_date
5049: FROM gms_bc_packets
5050: WHERE packet_id = x_packetid
5051: AND status_code = 'P'
5052: AND parent_bc_packet_id IS NULL ;
5053:
5085: -- END IF; -- Bug 2683607 : Commented , moved the END IF statement in the end
5086: -- so that all the FOR ALL statements are included in "IF t_bc_packet_id.COUNT > 0" check
5087: g_error_stage := 'CL_STEND_DATE:FORALL R';
5088: FORALL bcpkt_txns IN t_bc_packet_id.FIRST .. t_bc_packet_id.LAST
5089: UPDATE gms_bc_packets
5090: SET status_code = DECODE (
5091: t_start_date (bcpkt_txns),
5092: NULL, decode(p_mode,'S','E','C','F','R'),
5093: DECODE (t_end_date (bcpkt_txns), NULL, decode(p_mode,'S','E','C','F','R'), status_code)),
5120: -- budget_period_end_date as that of raw line . This will restrict the call of date calculation
5121: -- program once for each raw/burden transaction
5122: g_error_stage := 'CL_STEND_DATE:FORALL B';
5123: FORALL bcpkt_txns IN t_bc_packet_id.FIRST .. t_bc_packet_id.LAST
5124: UPDATE gms_bc_packets
5125: SET status_code = DECODE (
5126: t_start_date (bcpkt_txns),
5127: NULL, decode(p_mode,'S','E','C','F','R'),
5128: DECODE (t_end_date (bcpkt_txns), NULL, decode(p_mode,'S','E','C','F','R'), status_code)),
5279: END IF;
5280: END insert_arrival_order_seq;
5281:
5282: ----------------------------------------------------------------------------------------------------------
5283: -- This Procedure updated the burdened_cost column of gms_bc_packets .
5284: -- For EXP/PO/AP/REQ/ENC the Burdened cost = Raw Cost + Burdne Cost
5285: -- For Re-Costed Expenditures Burdened COst = Sum ( Raw Cost + Burden Cost ) of all the CDL's of that
5286: -- expenditure item id.
5287: ----------------------------------------------------------------------------------------------------------
5291: BEGIN
5292: g_error_procedure_name := 'update_burdened_cost';
5293: g_error_stage := 'UPD_BURDN_COST : START';
5294: -- --------------------------------------
5295: -- UPDATE BURDENED COST ON GMS_BC_PACKETS
5296: -- BURDENED COST = RAW COST + BURDEN COST.
5297: -- --------------------------------------
5298: -- Bug 2092791
5299: UPDATE gms_bc_packets a
5295: -- UPDATE BURDENED COST ON GMS_BC_PACKETS
5296: -- BURDENED COST = RAW COST + BURDEN COST.
5297: -- --------------------------------------
5298: -- Bug 2092791
5299: UPDATE gms_bc_packets a
5300: SET burdened_cost =
5301: (SELECT SUM ( NVL(entered_dr,0) - NVL(entered_cr,0) )
5302: FROM gms_bc_packets b
5303: WHERE b.packet_id + 0 = a.packet_id /* Bug 5689194 */
5298: -- Bug 2092791
5299: UPDATE gms_bc_packets a
5300: SET burdened_cost =
5301: (SELECT SUM ( NVL(entered_dr,0) - NVL(entered_cr,0) )
5302: FROM gms_bc_packets b
5303: WHERE b.packet_id + 0 = a.packet_id /* Bug 5689194 */
5304: AND b.document_type = a.document_type
5305: AND b.document_header_id = a.document_header_id
5306: AND ((b.document_type='EXP')
5362: x_err_count :=0;
5363:
5364: IF x_partial = 'Y' THEN
5365: g_error_stage := 'CHK SETUP FAIL : Y';
5366: UPDATE gms_bc_packets gms
5367: SET gms.status_code = 'T',
5368: gms.result_code = DECODE(budget_version_id,NULL,'F12',
5369: DECODE(resource_list_member_id,NULL,'F13',
5370: -- DECODE(bud_resource_list_member_id,NULL,'F14',
5441: -- Net zero txn.s failure
5442: If g_doc_type in ('EXP','ENC') then
5443: -- Handle net zero txn.s in the same packet
5444: -- A. Fail reversing line if original line has failed
5445: UPDATE gms_bc_packets bp
5446: SET bp.result_code = nvl(bp.result_code,'F65'),
5447: bp.status_code = 'R',
5448: bp.fc_error_message = decode(bp.fc_error_message,NULL,'CHECK_SETUP_FAILURE - net zero txn. - full mode failure',bp.fc_error_message)
5449: WHERE bp.packet_id = x_packetid
5452: AND bp.status_code = 'P'
5453: AND bp.document_header_id <> bp.adjusted_document_header_id
5454: AND bp.document_type in ('EXP','ENC')
5455: AND EXISTS (select 1
5456: from gms_bc_packets bp1
5457: where bp1.packet_id = bp.packet_id
5458: and bp1.document_header_id = bp.adjusted_document_header_id
5459: and SUBSTR (bp1.result_code, 1, 1) = 'F');
5460:
5461: -- Added commit for the base bug 3848201
5462: commit;
5463:
5464: -- B. Fail original line if reversing line has failed
5465: UPDATE gms_bc_packets bp
5466: SET bp.result_code = nvl(bp.result_code,'F65'),
5467: bp.status_code = 'R',
5468: bp.fc_error_message = decode(bp.fc_error_message,NULL,'CHECK_SETUP_FAILURE - original fail as reversing fail-full mode failure',bp.fc_error_message)
5469: WHERE bp.packet_id = x_packetid
5472: AND bp.status_code = 'P'
5473: AND bp.document_header_id = bp.adjusted_document_header_id
5474: AND bp.document_type in ('EXP','ENC')
5475: AND EXISTS (select 1
5476: from gms_bc_packets bp1
5477: where bp1.packet_id = bp.packet_id
5478: and bp1.adjusted_document_header_id = bp.document_header_id
5479: and SUBSTR (bp1.result_code, 1, 1) = 'F');
5480:
5490: SELECT 1
5491: INTO x_err_count
5492: FROM dual
5493: WHERE EXISTS (SELECT 1
5494: FROM gms_bc_packets
5495: WHERE packet_id = x_packetid
5496: AND (
5497: budget_version_id IS NULL
5498: OR resource_list_member_id IS NULL
5509: -- Added commit for the base bug 3848201
5510: commit;
5511:
5512:
5513: UPDATE gms_bc_packets gms
5514: SET gms.status_code = 'T',
5515: gms.result_code = DECODE(result_code,NULL,DECODE(budget_version_id,NULL,'F12',
5516: DECODE(resource_list_member_id,NULL,'F13',
5517: -- DECODE(bud_resource_list_member_id,NULL,'F14',
5589:
5590: PROCEDURE update_fc_sequence (
5591: x_packetid IN NUMBER) IS
5592:
5593: TYPE t_fcseq IS TABLE OF gms_bc_packets.funds_check_seq%TYPE;
5594: TYPE t_rowid IS TABLE OF VARCHAR2 (50);
5595: t_row_id t_rowid;
5596: t_fc_seq t_fcseq;
5597: t_count number; -- fix for bug : 2927485
5619: funds_check_seq
5620: BULK COLLECT INTO
5621: t_row_id,
5622: t_fc_seq
5623: FROM gms_bc_packets
5624: WHERE packet_id = x_packetid
5625: AND nvl(result_code,'XX') <> 'P82'
5626:
5627: ORDER BY ( NVL(entered_dr,0) - NVL(entered_cr,0) ), --added for 10418422
5647: funds_check_seq
5648: BULK COLLECT INTO
5649: t_row_id,
5650: t_fc_seq
5651: FROM gms_bc_packets
5652: WHERE packet_id = x_packetid
5653: AND nvl(result_code,'XX') = 'P82'
5654: ORDER BY adjusted_document_header_id,
5655: burdened_cost,
5670: END IF;
5671: END LOOP;
5672: g_error_stage := 'UPD FC SEQ: FOR ALL';
5673: FORALL bcpkt_txns IN t_row_id.FIRST .. t_row_id.LAST
5674: UPDATE gms_bc_packets
5675: SET funds_check_seq = t_fc_seq(bcpkt_txns)
5676: WHERE ROWID = t_row_id (bcpkt_txns);
5677: End if;
5678:
5685: /***********************************************************************************************/
5686: /********************************* FUNDS CHECK SETUP *************************************/
5687: /***********************************************************************************************/
5688: -------------------------------------------------------------------------------------------------
5689: /* This Function populates setup columns of GMS_BC_PACKETS Table for all the transactions
5690: This includes populating
5691: burdened_cost
5692: resource_list_member_id
5693: bud_resource_list_member_id
5726: COMMIT;
5727:
5728: -- ------------------------------------------------------------------------
5729: -- Update the budgeted task id
5730: -- in gms bc packets for a project, task, award, budget version
5731: -- ------------------------------------------------------------------------
5732: IF g_debug = 'Y' THEN
5733: gms_error_pkg.gms_debug ('GMS_SETUP - Before Budgeted Task Update -> packet_id'|| x_packetid, 'C');
5734: END IF;
5740: END IF;
5741:
5742: -- --------------------------------------------------------------------------
5743: -- Update the Budgeted resource list member id(rlmi)
5744: -- in gms bc packets for a project, task, award, budget version
5745: -- --------------------------------------------------------------------------
5746: IF g_debug = 'Y' THEN
5747: gms_error_pkg.gms_debug ('GMS_SETUP - Before Budgeted rlmi Update -> packet_id'|| x_packetid, 'C');
5748: END IF;
5754: END IF;
5755:
5756: -- ---------------------------------------------------------------------------
5757: -- Updating the Funds control level code
5758: -- in gms bc packets for a project, task, award, budget version
5759: -- ---------------------------------------------------------------------------
5760: IF g_debug = 'Y' THEN
5761: gms_error_pkg.gms_debug ('GMS_SETUP - Before Funds ctrl code -> packet_id'|| x_packetid, 'C');
5762: END IF;
5798: -- Bug 2176230
5799: -- ********************************************************************************
5800: -- NOTE :- Don't Put Any Commit after this point till gms_fc_process is complete,
5801: -- Reason being for funds checking in C Mode (check funds mode)
5802: -- transactions in gms_bc_packets should not be accounted by any other
5803: -- subsequent packet.
5804: -- ********************************************************************************
5805:
5806: insert_arrival_order_seq (x_packetid, x_mode);
5823: END gms_setup;
5824:
5825: /* ------------------------------------------------------------------------------------------------------
5826: This Procedure is called in case of Budget Submit and Re-Baseline prcoess of Award Budget.
5827: This procedure compares balances from gms_balances table and Records in gms_bc_packets
5828: for that budget_version_id. If any Record fails, control comes out of the loop and goes
5829: to the exception part where all the values of variable are dumped into the o/p file
5830:
5831: Bug 3681963 : Modified the code of procedure budget_fundscheck consider the previous consumed
5842: bud_task_id,
5843: resource_list_member_id, -- Bug 2605070, Replaced bud_resource_list_member_id with this column
5844: budget_period_start_date,
5845: budget_period_end_date
5846: FROM gms_bc_packets
5847: WHERE packet_id = x_packetid
5848: AND status_code = 'P'
5849: AND
5850: r_funds_control_level_code = 'B'
5865: bud_task_id,
5866: parent_resource_id,
5867: budget_period_start_date,
5868: budget_period_end_date
5869: FROM gms_bc_packets
5870: WHERE packet_id = x_packetid
5871: AND status_code = 'P'
5872: AND rg_funds_control_level_code = 'B'
5873: GROUP BY budget_version_id,
5886: budget_version_id,
5887: bud_task_id,
5888: budget_period_start_date,
5889: budget_period_end_date
5890: FROM gms_bc_packets
5891: WHERE packet_id = x_packetid
5892: AND status_code = 'P'
5893: AND t_funds_control_level_code = 'B'
5894: GROUP BY budget_version_id, bud_task_id, budget_period_start_date, budget_period_end_date
5899: budget_version_id,
5900: top_task_id,
5901: budget_period_start_date,
5902: budget_period_end_date
5903: FROM gms_bc_packets
5904: WHERE packet_id = x_packetid
5905: AND status_code = 'P'
5906: AND tt_funds_control_level_code = 'B'
5907: GROUP BY budget_version_id, top_task_id,
5913: SELECT SUM (NVL (entered_dr, 0) - NVL (entered_cr, 0)) a_bc_tot,
5914: budget_version_id,
5915: budget_period_start_date,
5916: budget_period_end_date
5917: FROM gms_bc_packets
5918: WHERE packet_id = x_packetid
5919: AND status_code = 'P'
5920: AND a_funds_control_level_code = 'B'
5921: GROUP BY budget_version_id, budget_period_start_date, budget_period_end_date
5926: x_r_bc_tot NUMBER (22, 5);
5927: x_rg_bc_tot NUMBER (22, 5);
5928: x_t_bc_tot NUMBER (22, 5);
5929: x_tt_bc_tot NUMBER (22, 5);
5930: x_res_list_member_id gms_bc_packets.resource_list_member_id%TYPE; -- Bug 2605070
5931: x_bud_task_id gms_bc_packets.bud_task_id%TYPE;
5932: x_budget_version_id NUMBER (22, 5);
5933: x_budget_period_start_date DATE;
5934: x_budget_period_end_date DATE;
5927: x_rg_bc_tot NUMBER (22, 5);
5928: x_t_bc_tot NUMBER (22, 5);
5929: x_tt_bc_tot NUMBER (22, 5);
5930: x_res_list_member_id gms_bc_packets.resource_list_member_id%TYPE; -- Bug 2605070
5931: x_bud_task_id gms_bc_packets.bud_task_id%TYPE;
5932: x_budget_version_id NUMBER (22, 5);
5933: x_budget_period_start_date DATE;
5934: x_budget_period_end_date DATE;
5935: temp NUMBER;
5939:
5940: l_balance_available NUMBER (22,5):=0;
5941: l_old_start_date DATE;
5942: l_old_end_date DATE;
5943: l_old_res_list_member_id gms_bc_packets.resource_list_member_id%TYPE;
5944: l_old_budget_version_id gms_bc_packets.budget_version_id%TYPE;
5945: l_old_bud_task_id gms_bc_packets.bud_task_id%TYPE;
5946: l_old_top_task_id gms_bc_packets.top_task_id%TYPE;
5947: l_old_parent_resource_id gms_bc_packets.parent_resource_id%TYPE;
5940: l_balance_available NUMBER (22,5):=0;
5941: l_old_start_date DATE;
5942: l_old_end_date DATE;
5943: l_old_res_list_member_id gms_bc_packets.resource_list_member_id%TYPE;
5944: l_old_budget_version_id gms_bc_packets.budget_version_id%TYPE;
5945: l_old_bud_task_id gms_bc_packets.bud_task_id%TYPE;
5946: l_old_top_task_id gms_bc_packets.top_task_id%TYPE;
5947: l_old_parent_resource_id gms_bc_packets.parent_resource_id%TYPE;
5948: l_previous_tot NUMBER (22,5):=0;
5941: l_old_start_date DATE;
5942: l_old_end_date DATE;
5943: l_old_res_list_member_id gms_bc_packets.resource_list_member_id%TYPE;
5944: l_old_budget_version_id gms_bc_packets.budget_version_id%TYPE;
5945: l_old_bud_task_id gms_bc_packets.bud_task_id%TYPE;
5946: l_old_top_task_id gms_bc_packets.top_task_id%TYPE;
5947: l_old_parent_resource_id gms_bc_packets.parent_resource_id%TYPE;
5948: l_previous_tot NUMBER (22,5):=0;
5949:
5942: l_old_end_date DATE;
5943: l_old_res_list_member_id gms_bc_packets.resource_list_member_id%TYPE;
5944: l_old_budget_version_id gms_bc_packets.budget_version_id%TYPE;
5945: l_old_bud_task_id gms_bc_packets.bud_task_id%TYPE;
5946: l_old_top_task_id gms_bc_packets.top_task_id%TYPE;
5947: l_old_parent_resource_id gms_bc_packets.parent_resource_id%TYPE;
5948: l_previous_tot NUMBER (22,5):=0;
5949:
5950:
5943: l_old_res_list_member_id gms_bc_packets.resource_list_member_id%TYPE;
5944: l_old_budget_version_id gms_bc_packets.budget_version_id%TYPE;
5945: l_old_bud_task_id gms_bc_packets.bud_task_id%TYPE;
5946: l_old_top_task_id gms_bc_packets.top_task_id%TYPE;
5947: l_old_parent_resource_id gms_bc_packets.parent_resource_id%TYPE;
5948: l_previous_tot NUMBER (22,5):=0;
5949:
5950:
5951: BEGIN
6207: CLOSE gms_bc_tot_a;
6208: IF g_debug = 'Y' THEN
6209: gms_error_pkg.gms_debug ('BUDGET_FUNDSCHECK BEFORE P50 UPDATE '|| x_packetid, 'C');
6210: END IF;
6211: UPDATE gms_bc_packets
6212: SET result_code = 'P50',
6213: award_result_code = 'P50',
6214: res_result_code = 'P50',
6215: res_grp_result_code = 'P50',
6239: x_token_val5=> x_budget_period_start_date,
6240: x_exec_type=> 'C',
6241: x_err_code=> x_err_code,
6242: x_err_buff=> x_err_buff);
6243: UPDATE gms_bc_packets
6244: SET result_code = 'F25',
6245: award_result_code = 'F25',
6246: res_result_code = 'F25',
6247: res_grp_result_code = 'F25',
6268: x_token_val5=> x_budget_period_start_date,
6269: x_exec_type=> 'C',
6270: x_err_code=> x_err_code,
6271: x_err_buff=> x_err_buff);
6272: UPDATE gms_bc_packets
6273: SET result_code = 'F26',
6274: award_result_code = 'F26',
6275: res_result_code = 'F26',
6276: res_grp_result_code = 'F26',
6295: x_token_val5=> x_budget_period_start_date,
6296: x_exec_type=> 'C',
6297: x_err_code=> x_err_code,
6298: x_err_buff=> x_err_buff);
6299: UPDATE gms_bc_packets
6300: SET result_code = 'F27',
6301: award_result_code = 'F27',
6302: res_result_code = 'F27',
6303: res_grp_result_code = 'F27',
6322: x_token_val5=> x_budget_period_start_date,
6323: x_exec_type=> 'C',
6324: x_err_code=> x_err_code,
6325: x_err_buff=> x_err_buff);
6326: UPDATE gms_bc_packets
6327: SET result_code = 'F28',
6328: award_result_code = 'F28',
6329: res_result_code = 'F28',
6330: res_grp_result_code = 'F28',
6347: x_token_val5=> x_budget_period_start_date,
6348: x_exec_type=> 'C',
6349: x_err_code=> x_err_code,
6350: x_err_buff=> x_err_buff);
6351: UPDATE gms_bc_packets
6352: SET result_code = 'F29',
6353: award_result_code = 'F29',
6354: res_result_code = 'F29',
6355: res_grp_result_code = 'F29',
6377: */
6378: -- ===========================================================================
6379:
6380: FUNCTION gms_fc_process (
6381: x_packetid IN gms_bc_packets.packet_id%TYPE,
6382: x_arrival_seq1 IN gms_bc_packet_arrival_order.packet_id%TYPE,
6383: x_mode IN Char -- Bug 2176230
6384: )
6385: RETURN BOOLEAN IS
6402: -- ==============================================================================
6403: -- ********************* RESOURCE LEVEL SUMMARY UPDATE ************************
6404: -- ==============================================================================
6405: -- Bug 2092791
6406: -- Following Insert statement inserts records in gms_bc_packets_summary.
6407: -- Records in this table will later be used to summarize amount at
6408: -- resource,task and Award Level for previous and currne packet.
6409: IF g_debug = 'Y' THEN
6410: gms_error_pkg.gms_debug ('RESOURCE LEVEL - SUMMARY INSERT ', 'C');
6410: gms_error_pkg.gms_debug ('RESOURCE LEVEL - SUMMARY INSERT ', 'C');
6411: END IF;
6412: x_date := sysdate;
6413: g_error_stage := 'FC PR : INSRT SUMM';
6414: INSERT INTO gms_bc_packets_bvid
6415: (packet_id,
6416: budget_version_id,
6417: creation_date)
6418: SELECT DISTINCT x_packetid,
6417: creation_date)
6418: SELECT DISTINCT x_packetid,
6419: budget_version_id,
6420: x_date
6421: FROM gms_bc_packets
6422: WHERE packet_id = x_packetid
6423: AND status_code = 'P' -- This is to ignore Transactions which failed during setup.
6424: ;
6425:
6422: WHERE packet_id = x_packetid
6423: AND status_code = 'P' -- This is to ignore Transactions which failed during setup.
6424: ;
6425:
6426: -- Bug 4053891 Do not change code flow ..as lock_budget_versions uses gms_bc_packets_bvid
6427: -- Costing and Funds check is incompatible to Sweeper. 'EXP' has been added to the list for
6428: -- interface (VI->EXP)
6429:
6430: If g_doc_type in ('REQ','PO','AP','FAB','EXP') then
6431: LOCK_BUDGET_VERSIONS(x_packetid);
6432: End If;
6433:
6434: -- Bug 2605070, Replaced bud_resource_list_member_id with resource_list_member_id
6435: INSERT INTO gms_bc_packets_summary
6436: (packet_id,
6437: creation_date,
6438: budget_version_id,
6439: top_task_id,
6459: nvl(sum(decode(bcpkt.status_code || bcpkt.actual_flag, 'AA',nvl(entered_dr,0) - nvl(entered_cr,0),0)),0),
6460: nvl(sum(decode(bcpkt.status_code || bcpkt.actual_flag, 'PA',nvl(entered_dr,0) - nvl(entered_cr,0),0)),0),
6461: nvl(sum(decode(bcpkt.status_code || bcpkt.actual_flag, 'AE',nvl(entered_dr,0) - nvl(entered_cr,0),0)),0),
6462: nvl(sum(decode(bcpkt.status_code || bcpkt.actual_flag, 'PE',nvl(entered_dr,0) - nvl(entered_cr,0),0)),0)
6463: FROM gms_bc_packets bcpkt,
6464: gms_bc_packet_arrival_order ao,
6465: gms_bc_packets_bvid a
6466: WHERE bcpkt.status_code IN ('A', 'P')
6467: AND bcpkt.budget_version_id = a.budget_version_id
6461: nvl(sum(decode(bcpkt.status_code || bcpkt.actual_flag, 'AE',nvl(entered_dr,0) - nvl(entered_cr,0),0)),0),
6462: nvl(sum(decode(bcpkt.status_code || bcpkt.actual_flag, 'PE',nvl(entered_dr,0) - nvl(entered_cr,0),0)),0)
6463: FROM gms_bc_packets bcpkt,
6464: gms_bc_packet_arrival_order ao,
6465: gms_bc_packets_bvid a
6466: WHERE bcpkt.status_code IN ('A', 'P')
6467: AND bcpkt.budget_version_id = a.budget_version_id
6468: AND bcpkt.packet_id = ao.packet_id
6469: AND a.packet_id = x_packetid
6490: IF g_debug = 'Y' THEN
6491: gms_error_pkg.gms_debug ('RESOURCE POSTED BALANCE UPDATE', 'C');
6492: END IF;
6493: g_error_stage := 'FC PR : RES P BAL';
6494: UPDATE gms_bc_packets bp
6495: SET (bp.res_budget_posted, bp.res_actual_posted, bp.res_enc_posted) =
6496: (SELECT SUM (NVL (budget_period_to_date, 0) * DECODE (balance_type, 'BGT', 1, 0)),
6497: SUM (NVL (actual_period_to_date, 0) * DECODE (balance_type, 'EXP', 1, 0)),
6498: SUM (NVL (encumb_period_to_date, 0) * DECODE (balance_type, 'REQ', 1, 'PO', 1, 'AP', 1, 'ENC', 1, 0))
6542: gms_error_pkg.gms_debug ( 'RESOURCE LEVEL - APPROVED/PENDING BALANCE FOR PREVIOUS AND CURRENT PACKETS','C' );
6543: END IF;
6544: -- Bug 2092791
6545: g_error_stage := 'FC PR : RES A BAL';
6546: UPDATE gms_bc_packets bp
6547: SET (bp.res_actual_approved, bp.res_actual_pending, bp.res_enc_approved,
6548: bp.res_enc_pending) =
6549: (SELECT
6550: SUM(actual_approved), --Bug 2490381 : Added SUM
6550: SUM(actual_approved), --Bug 2490381 : Added SUM
6551: SUM(actual_pending), --Bug 2490381 : Added SUM
6552: SUM(enc_approved), --Bug 2490381 : Added SUM
6553: SUM(enc_pending) --Bug 2490381 : Added SUM
6554: FROM gms_bc_packets_summary gmsbcs
6555: WHERE gmsbcs.packet_id = x_packetid
6556: AND gmsbcs.bud_task_id = bp.bud_task_id
6557: AND gmsbcs.budget_version_id = bp.budget_version_id
6558: AND gmsbcs.resource_list_member_id =
6587: effect_on_funds_code, -- Bug 2927485
6588: TRUNC ( budget_period_start_date ) budget_period_start_date,
6589: TRUNC ( budget_period_end_date ) budget_period_end_date,
6590: actual_flag
6591: FROM gms_bc_packets
6592: WHERE packet_id = x_packetid
6593: AND effect_on_funds_code in('D','I') --Bug 2092791
6594: AND status_code = 'P'
6595: AND r_funds_control_level_code <> 'N'
6636: x_budget_period_start_date_old := res_level.budget_period_start_date;
6637: x_budget_period_end_date_old := res_level.budget_period_end_date;
6638: ELSE
6639: IF nvl(res_level.entered_dr,0)>= 0 AND res_level.effect_on_funds_code = 'D' THEN --Bug 2092791 and 2927485 (Update pending balance
6640: UPDATE gms_bc_packets -- if funds are decreasing)
6641: SET res_actual_pending = NVL (res_actual_pending, 0)
6642: - DECODE (
6643: res_level.actual_flag,
6644: 'A', NVL (x_pending_actual, 0),
6674: IF g_debug = 'Y' THEN
6675: gms_error_pkg.gms_debug ('RESULT CODE UPDATE - RESOURCE', 'C');
6676: END IF;
6677: g_error_stage := 'FC PR : RES RESULT CODE';
6678: UPDATE gms_bc_packets bp
6679: SET bp.res_result_code = DECODE (
6680: bp.effect_on_funds_code,
6681: 'I', decode(bp.result_code,null,'P78',bp.result_code) ,
6682: 'D', DECODE (
6710:
6711:
6712: -- 7. Update all above levels with failure result code
6713: -- a. Propogate resource level to all other levels
6714: UPDATE gms_bc_packets bp
6715: SET bp.res_grp_result_code = res_result_code,
6716: bp.task_result_code = res_result_code,
6717: bp.top_task_result_code = res_result_code,
6718: bp.award_result_code = res_result_code,
6743: --===============================================================================
6744: -- RESOURCE LEVEL : INSERT NEGATIVE IN SUMMARY FOR FAILED TRANSACTIONS
6745: --===============================================================================
6746: g_error_stage := 'FC PR : RES INSERT NEG';
6747: INSERT INTO gms_bc_packets_summary
6748: (packet_id,
6749: creation_date,
6750: budget_version_id,
6751: top_task_id,
6764: budget_period_start_date,
6765: budget_period_end_date,
6766: -1 * nvl(sum(decode(status_code || actual_flag,'RA',nvl(entered_dr,0) - nvl(entered_cr,0),0)),0) ,
6767: -1 * nvl(sum(decode(status_code || actual_flag,'RE',nvl(entered_dr,0) - nvl(entered_cr,0),0)),0)
6768: from gms_bc_packets
6769: where packet_id = x_packetid
6770: and res_result_code in ('F92','F63','F75','F65')
6771: group by budget_version_id, top_task_id,bud_task_id, parent_resource_id,budget_period_start_date, budget_period_end_date;
6772:
6783: IF g_debug = 'Y' THEN
6784: gms_error_pkg.gms_debug ('RESOURCE GROUP POSTED BALANCE UPDATE', 'C');
6785: END IF;
6786: g_error_stage := 'FC PR : RESG P BAL';
6787: UPDATE gms_bc_packets bp
6788: SET (bp.res_grp_budget_posted, bp.res_grp_actual_posted, bp.res_grp_enc_posted) =
6789: (SELECT SUM (NVL (budget_period_to_date, 0) * DECODE (balance_type, 'BGT', 1, 0)),
6790: SUM (NVL (actual_period_to_date, 0) * DECODE (balance_type, 'EXP', 1, 0)),
6791: SUM (NVL (encumb_period_to_date, 0) * DECODE (balance_type, 'REQ', 1, 'PO', 1, 'AP', 1, 'ENC', 1, 0))
6839: gms_error_pkg.gms_debug ('RESOURCE GROUP LEVEL - APPROVED/PENDING BALANCE FOR CURRENT PACKET', 'C');
6840: END IF;
6841: -- Bug 2092791
6842: g_error_stage := 'FC PR : RESG A/P BAL';
6843: UPDATE gms_bc_packets bp
6844: SET (bp.res_grp_actual_approved, bp.res_grp_actual_pending, bp.res_grp_enc_approved,
6845: bp.res_grp_enc_pending) =
6846: (SELECT
6847: SUM(actual_approved),
6847: SUM(actual_approved),
6848: SUM(actual_pending),
6849: SUM(enc_approved),
6850: SUM(enc_pending)
6851: FROM gms_bc_packets_summary gmsbcs
6852: WHERE gmsbcs.packet_id = x_packetid
6853: AND gmsbcs.bud_task_id = bp.bud_task_id
6854: AND gmsbcs.budget_version_id = bp.budget_version_id
6855: AND gmsbcs.parent_resource_id = bp.parent_resource_id
6884: effect_on_funds_code, -- Bug 2927485
6885: TRUNC (budget_period_start_date) budget_period_start_date,
6886: TRUNC (budget_period_end_date) budget_period_end_date,
6887: actual_flag
6888: FROM gms_bc_packets
6889: WHERE packet_id = x_packetid
6890: AND effect_on_funds_code in ('D','I') -- Bug 2092791
6891: AND status_code = 'P'
6892: AND rg_funds_control_level_code <> 'N'
6931: x_budget_period_start_date_old := res_grp_level.budget_period_start_date;
6932: x_budget_period_end_date_old := res_grp_level.budget_period_end_date;
6933: ELSE
6934: IF nvl(res_grp_level.entered_dr,0) >= 0 AND res_grp_level.effect_on_funds_code = 'D' THEN --Bug 2092791 and 2927485 (Update pending balance
6935: UPDATE gms_bc_packets -- if funds are decreasing)
6936: SET res_grp_actual_pending = NVL (res_grp_actual_pending, 0)
6937: - DECODE (
6938: res_grp_level.actual_flag,
6939: 'A', NVL (x_pending_actual, 0),
6968: IF g_debug = 'Y' THEN
6969: gms_error_pkg.gms_debug ('RESULT CODE UPDATE - RESOURCE GROUP', 'C');
6970: END IF;
6971: g_error_stage := 'FC PR : RESG RESULT UPD';
6972: UPDATE gms_bc_packets bp
6973: SET bp.res_grp_result_code = DECODE (
6974: bp.effect_on_funds_code,
6975: 'I',decode(bp.result_code,null,'P78',bp.result_code) ,
6976: 'D', DECODE (
7004:
7005:
7006: -- 7. Update all above levels with failure result code
7007: -- b. Propogate resource group level to all other levels
7008: UPDATE gms_bc_packets bp
7009: SET bp.task_result_code = res_grp_result_code,
7010: bp.top_task_result_code = res_grp_result_code,
7011: bp.award_result_code = res_grp_result_code,
7012: bp.result_code = res_grp_result_code,
7037: --===============================================================================
7038: -- RESOURCE GROUP LEVEL : INSERT NEGATIVE IN SUMMARY FOR FAILED TRANSACTIONS
7039: --===============================================================================
7040: g_error_stage := 'FC PR : RESG IN NEG';
7041: INSERT INTO gms_bc_packets_summary
7042: (packet_id,
7043: creation_date,
7044: budget_version_id,
7045: top_task_id,
7056: budget_period_start_date,
7057: budget_period_end_date,
7058: -1 * nvl(sum(decode(status_code || actual_flag,'RA',nvl(entered_dr,0) - nvl(entered_cr,0),0)),0) ,
7059: -1 * nvl(sum(decode(status_code || actual_flag,'RE',nvl(entered_dr,0) - nvl(entered_cr,0),0)),0)
7060: from gms_bc_packets
7061: where packet_id = x_packetid
7062: and res_grp_result_code in ('F93','F63','F75','F65')
7063: and nvl(substr(res_result_code,1,1),'P') = 'P'
7064: group by budget_version_id, top_task_id, bud_task_id, budget_period_start_date, budget_period_end_date;
7075: IF g_debug = 'Y' THEN
7076: gms_error_pkg.gms_debug ('TASK :POSTED BALANCE ', 'C');
7077: END IF;
7078: g_error_stage := 'FC PR : TASK P BAL';
7079: UPDATE gms_bc_packets bp
7080: SET (bp.task_budget_posted, bp.task_actual_posted, bp.task_enc_posted) =
7081: (SELECT SUM (NVL (budget_period_to_date, 0) * DECODE (balance_type, 'BGT', 1, 0)),
7082: SUM (NVL (actual_period_to_date, 0) * DECODE (balance_type, 'EXP', 1, 0)),
7083: SUM (NVL (encumb_period_to_date, 0) * DECODE (balance_type, 'REQ', 1, 'PO', 1, 'AP', 1, 'ENC', 1, 0))
7116: gms_error_pkg.gms_debug ('TASK LEVEL - APPROVED/PENDING BALANCE FOR PREVIOUS PACKETS', 'C');
7117: END IF;
7118:
7119: g_error_stage := 'FC PR : TASK A/P BAL';
7120: UPDATE gms_bc_packets bp
7121: SET (bp.task_actual_approved, bp.task_actual_pending, bp.task_enc_approved,
7122: bp.task_enc_pending) =
7123: (SELECT
7124: SUM (actual_approved),
7124: SUM (actual_approved),
7125: SUM (actual_pending),
7126: SUM (enc_approved),
7127: SUM (enc_pending)
7128: FROM gms_bc_packets_summary gmsbcs
7129: WHERE gmsbcs.packet_id = x_packetid
7130: AND gmsbcs.budget_version_id = bp.budget_version_id
7131: AND gmsbcs.bud_task_id = bp.bud_task_id
7132: --Bug 2490381 : Changed "=" to "<=" to consider all the
7158: effect_on_funds_code, -- Bug 2927485
7159: TRUNC (budget_period_start_date) budget_period_start_date,
7160: TRUNC (budget_period_end_date) budget_period_end_date,
7161: actual_flag
7162: FROM gms_bc_packets
7163: WHERE packet_id = x_packetid
7164: AND effect_on_funds_code in ('D','I') -- Bug 2092791
7165: AND status_code = 'P'
7166: AND t_funds_control_level_code <> 'N'
7200: x_budget_period_start_date_old := task_level.budget_period_start_date;
7201: x_budget_period_end_date_old := task_level.budget_period_end_date;
7202: ELSE
7203: IF nvl(task_level.entered_dr,0) >=0 AND task_level.effect_on_funds_code = 'D' THEN --Bug 2092791 and 2927485 (Update pending balance
7204: UPDATE gms_bc_packets -- if funds are decreasing)
7205: SET task_actual_pending = NVL (task_actual_pending, 0)
7206: - DECODE (
7207: task_level.actual_flag,
7208: 'A', NVL (x_pending_actual, 0),
7238: IF g_debug = 'Y' THEN
7239: gms_error_pkg.gms_debug ('TASK : RESULT CODE ', 'C');
7240: END IF;
7241: g_error_stage := 'FC PR : TASK RESULT CD';
7242: UPDATE gms_bc_packets bp
7243: SET bp.task_result_code = DECODE (
7244: bp.effect_on_funds_code,
7245: 'I', decode(bp.result_code,null,'P78',bp.result_code) ,
7246: 'D', DECODE (
7274:
7275:
7276:
7277: -- 5. Update all above levels with failure result code
7278: UPDATE gms_bc_packets bp
7279: SET bp.result_code = bp.task_result_code,
7280: bp.top_task_result_code = bp.task_result_code,
7281: bp.award_result_code = bp.task_result_code,
7282: bp.status_code = DECODE(x_mode,'C','F','R')
7306: --==========================================================================
7307: -- TASK LEVEL : INSERT NEGATIVE IN SUMMARY FOR FAILED TRANSACTIONS
7308: --==========================================================================
7309: g_error_stage := 'FC PR : TASK IN NEG';
7310: INSERT INTO gms_bc_packets_summary
7311: (packet_id,
7312: creation_date,
7313: budget_version_id,
7314: top_task_id,
7323: budget_period_start_date,
7324: budget_period_end_date,
7325: -1 * nvl(sum(decode(status_code || actual_flag,'RA',nvl(entered_dr,0) - nvl(entered_cr,0),0)),0) ,
7326: -1 * nvl(sum(decode(status_code || actual_flag,'RE',nvl(entered_dr,0) - nvl(entered_cr,0),0)),0)
7327: from gms_bc_packets
7328: where packet_id = x_packetid
7329: and task_result_code in ('F91','F63','F75','F65')
7330: and substr(res_grp_result_code,1,1) = 'P'
7331: group by budget_version_id, top_task_id, budget_period_start_date, budget_period_end_date;
7342: IF g_debug = 'Y' THEN
7343: gms_error_pkg.gms_debug ('TOP TASK :POSTED BALANCE ', 'C');
7344: END IF;
7345: g_error_stage := 'FC PR : TTASK P BAL';
7346: UPDATE gms_bc_packets bp
7347: SET (bp.top_task_budget_posted, bp.top_task_actual_posted, bp.top_task_enc_posted) =
7348: (SELECT SUM (NVL (budget_period_to_date, 0) * DECODE (balance_type, 'BGT', 1, 0)),
7349: SUM (NVL (actual_period_to_date, 0) * DECODE (balance_type, 'EXP', 1, 0)),
7350: SUM (NVL (encumb_period_to_date, 0) * DECODE (balance_type, 'REQ', 1, 'PO', 1, 'AP', 1, 'ENC', 1, 0))
7385: END IF;
7386: g_error_stage := 'FC PR : TTASK A/P BAL';
7387: -- Bug 2092791
7388:
7389: UPDATE gms_bc_packets bp
7390: SET (bp.top_task_actual_approved, bp.top_task_actual_pending, bp.top_task_enc_approved,
7391: bp.top_task_enc_pending) =
7392: (SELECT
7393: SUM (actual_approved),
7393: SUM (actual_approved),
7394: SUM (actual_pending),
7395: SUM (enc_approved),
7396: SUM (enc_pending)
7397: FROM gms_bc_packets_summary gmsbcs
7398: WHERE gmsbcs.packet_id = x_packetid
7399: AND gmsbcs.budget_version_id = bp.budget_version_id
7400: AND gmsbcs.top_task_id = bp.top_task_id
7401: --Bug 2490381 : Changed "=" to "<=" to consider all the
7428: effect_on_funds_code, -- Bug 2927485
7429: TRUNC (budget_period_start_date) budget_period_start_date,
7430: TRUNC (budget_period_end_date) budget_period_end_date,
7431: actual_flag
7432: FROM gms_bc_packets
7433: WHERE packet_id = x_packetid
7434: AND effect_on_funds_code in ('D','I') -- Bug 2092791
7435: AND status_code = 'P'
7436: AND tt_funds_control_level_code <> 'N'
7473: x_budget_period_start_date_old := top_task_level.budget_period_start_date;
7474: x_budget_period_end_date_old := top_task_level.budget_period_end_date;
7475: ELSE
7476: IF nvl(top_task_level.entered_dr,0) >=0 AND top_task_level.effect_on_funds_code = 'D' THEN --Bug 2092791 and 2927485 (Update pending balance
7477: UPDATE gms_bc_packets -- if funds are decreasing)
7478: SET top_task_actual_pending = NVL (top_task_actual_pending, 0)
7479: - DECODE (
7480: top_task_level.actual_flag,
7481: 'A', NVL (x_pending_actual, 0),
7511: IF g_debug = 'Y' THEN
7512: gms_error_pkg.gms_debug ('TOP TASK : RESULT CODE ', 'C');
7513: END IF;
7514: g_error_stage := 'FC PR : TTASK RESULT CODE';
7515: UPDATE gms_bc_packets bp
7516: SET bp.top_task_result_code = DECODE (
7517: bp.effect_on_funds_code,
7518: 'I', decode(bp.result_code,null,'P78',bp.result_code),
7519: 'D', DECODE (
7546: AND bp.status_code = 'P';
7547:
7548:
7549: -- 5. Update all above levels with failure result code
7550: UPDATE gms_bc_packets bp
7551: SET bp.result_code = bp.top_task_result_code,
7552: bp.award_result_code = bp.top_task_result_code,
7553: bp.status_code = DECODE(x_mode,'C','F','R')
7554: WHERE bp.packet_id = x_packetid
7574: End if;
7575: End If;
7576:
7577: g_error_stage := 'FC PR : TTASK IN NEG';
7578: INSERT INTO gms_bc_packets_summary
7579: (packet_id,
7580: creation_date,
7581: budget_version_id,
7582: budget_period_start_date,
7589: budget_period_start_date,
7590: budget_period_end_date,
7591: -1 * nvl(sum(decode(status_code || actual_flag, 'RA',nvl(entered_dr,0) - nvl(entered_cr,0),0)),0) ,
7592: -1 * nvl(sum(decode(status_code || actual_flag, 'RE',nvl(entered_dr,0) - nvl(entered_cr,0),0)),0)
7593: from gms_bc_packets
7594: where packet_id = x_packetid
7595: and top_task_result_code in ('F60','F63','F65','F75')
7596: and substr(task_result_code,1,1) = 'P'
7597: group by budget_version_id, budget_period_start_date, budget_period_end_date;
7609: IF g_debug = 'Y' THEN
7610: gms_error_pkg.gms_debug ('AWARD:POSTED BALANCE ', 'C');
7611: END IF;
7612: g_error_stage := 'FC PR : AWARD P B';
7613: UPDATE gms_bc_packets bp
7614: SET (bp.award_budget_posted, bp.award_actual_posted, bp.award_enc_posted) =
7615: (SELECT SUM (NVL (budget_period_to_date, 0) * DECODE (balance_type, 'BGT', 1, 0)),
7616: SUM (NVL (actual_period_to_date, 0) * DECODE (balance_type, 'EXP', 1, 0)),
7617: SUM (NVL (encumb_period_to_date, 0) * DECODE (balance_type, 'REQ', 1, 'PO', 1, 'AP', 1, 'ENC', 1, 0))
7646: gms_error_pkg.gms_debug ('AWARD LEVEL - APPROVED/PENDING BALANCE FOR PREVIOUS PACKETS', 'C');
7647: END IF;
7648: g_error_stage := 'FC PR : AWARD A/P B';
7649: -- Bug 2092791
7650: UPDATE gms_bc_packets bp
7651: SET (bp.award_actual_approved, bp.award_actual_pending, bp.award_enc_approved,
7652: bp.award_enc_pending) =
7653: (SELECT
7654: SUM (actual_approved),
7654: SUM (actual_approved),
7655: SUM (actual_pending),
7656: SUM (enc_approved),
7657: SUM (enc_pending)
7658: FROM gms_bc_packets_summary gmsbcs
7659: WHERE gmsbcs.packet_id = x_packetid
7660: AND gmsbcs.budget_version_id = bp.budget_version_id
7661: --Bug 2490381 : Changed "=" to "<=" to consider all the
7662: -- records from summary table which fall
7686: effect_on_funds_code, -- Bug 2927485
7687: TRUNC (budget_period_start_date) budget_period_start_date,
7688: TRUNC (budget_period_end_date) budget_period_end_date,
7689: actual_flag
7690: FROM gms_bc_packets
7691: WHERE packet_id = x_packetid
7692: AND effect_on_funds_code in ('D','I') -- 2092791
7693: AND status_code = 'P'
7694: AND a_funds_control_level_code <> 'N'
7727: x_budget_period_end_date_old := award_level.budget_period_end_date;
7728: ELSE
7729: IF nvl(award_level.entered_dr,0) >= 0 AND award_level.effect_on_funds_code = 'D' THEN --Bug 2092791 and 2927485(Update pending balance
7730: -- if funds are decreasing)
7731: UPDATE gms_bc_packets
7732: SET award_actual_pending = NVL (award_actual_pending, 0)
7733: - DECODE (
7734: award_level.actual_flag,
7735: 'A', NVL (x_pending_actual, 0),
7764: IF g_debug = 'Y' THEN
7765: gms_error_pkg.gms_debug ('AWARD: Result Code ', 'C');
7766: END IF;
7767: g_error_stage := 'FC PR : AWARD RESULT';
7768: UPDATE gms_bc_packets bp
7769: SET bp.award_result_code = DECODE (
7770: bp.effect_on_funds_code,
7771: 'I',decode(bp.result_code,null,'P78',bp.result_code ),
7772: 'D', DECODE (
7799: AND bp.status_code = 'P';
7800:
7801:
7802:
7803: UPDATE gms_bc_packets
7804: SET result_code = NVL (award_result_code, 'F53'),
7805: status_code = DECODE (status_code,'P', DECODE (SUBSTR (NVL (award_result_code, 'F53'), 1, 1),'F', DECODE(x_mode,'C','F','R'),status_code),status_code)
7806: WHERE packet_id = x_packetid
7807: AND effect_on_funds_code IN ('D', 'I')
7828: -- Bug 3426509 : Added following code to update the last advisory result code to result_code column
7829: -- e.g. if transaction passed funds check in advisory mode at Task and Resource Level , then the
7830: -- result_code will hold the result_code of Task Level funds check (i.e. 'P65').
7831:
7832: UPDATE gms_bc_packets
7833: SET result_code = DECODE (top_task_result_code,'P80', 'P80',
7834: DECODE (task_result_code,'P65', 'P65',
7835: DECODE (res_grp_result_code,'P73', 'P73',
7836: DECODE (res_result_code,'P69', 'P69',
7850: --================================================================================--
7851: -- Data clean up code from summary tables after Amount calculation
7852: --================================================================================--
7853: g_error_stage := 'FC PR : DELETE';
7854: DELETE gms_bc_packets_summary
7855: WHERE packet_id = x_packetid;
7856: DELETE gms_bc_packets_bvid
7857: WHERE packet_id = x_packetid;
7858:
7852: --================================================================================--
7853: g_error_stage := 'FC PR : DELETE';
7854: DELETE gms_bc_packets_summary
7855: WHERE packet_id = x_packetid;
7856: DELETE gms_bc_packets_bvid
7857: WHERE packet_id = x_packetid;
7858:
7859: -- Added commit for the base bug 3848201
7860: commit;
7862: -- Bug 2092791
7863:
7864: -- Bug 2176230
7865: -- Delete Record from gms_bc_packet_arrival_order able to ensure that transactions inserted into
7866: -- gms_bc_packets in check funds mode should not be accounted by any other subsequent packets.
7867:
7868: IF x_mode = 'C' THEN
7869: DELETE gms_bc_packet_arrival_order
7870: WHERE packet_id = x_packetid;
7884: -- This function calls all the procedures and functions for funds checker
7885: --------------------------------------------------------------------------------------------------------------------------------------------------
7886: -- Funds Check Processor
7887: FUNCTION gms_fcp (
7888: x_sobid IN gms_bc_packets.set_of_books_id%TYPE,
7889: x_packetid IN gms_bc_packets.packet_id%TYPE,
7890: x_mode IN VARCHAR2,
7891: x_partial IN VARCHAR2,
7892: x_arrival_seq IN gl_bc_packet_arrival_order.arrival_seq%TYPE,
7885: --------------------------------------------------------------------------------------------------------------------------------------------------
7886: -- Funds Check Processor
7887: FUNCTION gms_fcp (
7888: x_sobid IN gms_bc_packets.set_of_books_id%TYPE,
7889: x_packetid IN gms_bc_packets.packet_id%TYPE,
7890: x_mode IN VARCHAR2,
7891: x_partial IN VARCHAR2,
7892: x_arrival_seq IN gl_bc_packet_arrival_order.arrival_seq%TYPE,
7893: x_err_code OUT NOCOPY NUMBER,
7928: -- Bug 2176230
7929: -- ********************************************************************************
7930: -- NOTE :- Don't Put Any Commit after this point till gms_fc_process is complete,
7931: -- Reason being for funds checking in C Mode (check funds mode)
7932: -- transactions in gms_bc_packets should not be accounted by any other
7933: -- subsequent packet. (Applicable for x_mode = R,U,C,E)
7934: -- ********************************************************************************
7935:
7936:
7989: --
7990: -- ------------------------------------------------------------------------------------------------
7991: -- To Exit out NOCOPY of gms_fck in case of non_gms application calls.
7992: -- Bug 1966096. Funds check for non-sponsor projects and GL Transactions should happen base
7993: -- on the core functionality. Since their no entry in gms_bc_packets as those transactions
7994: -- gms funds checker should not return false which fail the above transactions.
7995: -- ------------------------------------------------------------------------------------------------
7996:
7997: FUNCTION gms_return_code (
8016: SELECT 0
8017: INTO x_err_code
8018: FROM DUAL
8019: WHERE EXISTS ( SELECT 'X'
8020: FROM gms_bc_packets
8021: WHERE packet_id = x_packetid
8022: AND status_code IN ('S', 'B')); --Bug Fix 1350100 Change status_code from 'A'
8023: --to 'B' to fix bug 2138376
8024: EXCEPTION
8037: SELECT 'F'
8038: INTO x_result_code
8039: FROM DUAL
8040: WHERE EXISTS ( SELECT result_code
8041: FROM gms_bc_packets
8042: WHERE packet_id = x_packetid
8043: AND SUBSTR (result_code, 1, 1) = 'F');
8044: EXCEPTION
8045: WHEN NO_DATA_FOUND THEN
8053:
8054: status_code_update (x_packetid, 'E');
8055: x_e_code := 'H';
8056:
8057: ELSE -- update gms_bc_packets with approved status
8058: IF g_debug = 'Y' THEN
8059: gms_error_pkg.gms_debug ( 'Calling status_code_update for encumbrances', 'C' );
8060: END IF;
8061: status_code_update (x_packetid, 'E');
8069: SELECT 1
8070: INTO x_err_code
8071: FROM DUAL
8072: WHERE EXISTS ( SELECT 'X'
8073: FROM gms_bc_packets
8074: WHERE packet_id = x_packetid
8075: AND SUBSTR (result_code, 1, 1) = 'F'
8076: AND status_code = 'T');
8077: x_return_code := 'T';
8084: SELECT 1
8085: INTO x_err_code
8086: FROM DUAL
8087: WHERE EXISTS ( SELECT 'X'
8088: FROM gms_bc_packets
8089: WHERE packet_id = x_packetid
8090: AND SUBSTR (result_code, 1, 1) = 'F');
8091: EXCEPTION
8092: WHEN NO_DATA_FOUND THEN
8152: -- in misc_gms_insert. Those will not be added here ..helps fail
8153: -- transactions in case of incorrect award number and expenditure type.
8154:
8155: -- R12 FundsCheck Management Uptake : Shifted IP/AP/PO/REQ/FAB cursor logic to procedure copy_gl_pkt_to_gms_pkt and
8156: -- merged with the selects which fetches IP/AP/PO/REQ/FAB record for inserting into gms_bc_packets.
8157: -- Added below cursor to derive g_doc_type based on the data inserted into gms_bc_packets by procedure copy_gl_pkt_to_gms_pkt.
8158:
8159: CURSOR C_count_rec IS
8160: SELECT count(*) gms_txn_count,
8153: -- transactions in case of incorrect award number and expenditure type.
8154:
8155: -- R12 FundsCheck Management Uptake : Shifted IP/AP/PO/REQ/FAB cursor logic to procedure copy_gl_pkt_to_gms_pkt and
8156: -- merged with the selects which fetches IP/AP/PO/REQ/FAB record for inserting into gms_bc_packets.
8157: -- Added below cursor to derive g_doc_type based on the data inserted into gms_bc_packets by procedure copy_gl_pkt_to_gms_pkt.
8158:
8159: CURSOR C_count_rec IS
8160: SELECT count(*) gms_txn_count,
8161: SUM(DECODE(gms.document_type,'REQ',1,0)) req_count,
8161: SUM(DECODE(gms.document_type,'REQ',1,0)) req_count,
8162: SUM(DECODE(gms.document_type,'PO',1,0)) po_count,
8163: SUM(DECODE(gms.document_type,'AP',1,0)) ap_count,
8164: SUM(DECODE(gms.document_type,'FAB',1,0)) fab_count
8165: FROM gms_bc_packets gms
8166: WHERE gms.packet_id= g_packet_id;
8167:
8168: -- R12 FundsCheck Management Uptake : New variables defined and used in deriving g_doc_type
8169: l_gms_txn_count NUMBER;
8215: -- R12 FundsCheck Management Uptake : Deleted existing logic which was deriving g_doc_type and
8216: -- g_non_gms_txn based on records in GL_bc_packets/AP/PO/REQ tables.
8217: -- With new architecture the gl_bc_packets/AP/PO/REQ validations will be performed during
8218: -- insertion of GMS packets in main session and current logic derives document type based on
8219: -- the data inserted into gms_bc_packets for current packet_id.
8220:
8221: OPEN c_count_rec;
8222: FETCH c_count_rec INTO l_gms_txn_count,l_req_count,l_po_count,l_ap_count,l_fab_count;
8223: CLOSE c_count_rec;
8242:
8243: End if; -- p_mode check - I
8244:
8245: IF g_debug = 'Y' THEN
8246: gms_error_pkg.gms_debug (g_error_procedure_name||':Total txns. in gms_bc_packets -'||l_gms_txn_count, 'C');
8247: gms_error_pkg.gms_debug (g_error_procedure_name||':Total REQ txns in gms_bc_packets -'||l_req_count,'C');
8248: gms_error_pkg.gms_debug (g_error_procedure_name||':Total PO txns in gms_bc_packets -'||l_po_count,'C');
8249: gms_error_pkg.gms_debug (g_error_procedure_name||':Total AP txns in gms_bc_packets -'||l_ap_count,'C');
8250: gms_error_pkg.gms_debug (g_error_procedure_name||':Total FAB txns in gms_bc_packets -'||l_fab_count,'C');
8243: End if; -- p_mode check - I
8244:
8245: IF g_debug = 'Y' THEN
8246: gms_error_pkg.gms_debug (g_error_procedure_name||':Total txns. in gms_bc_packets -'||l_gms_txn_count, 'C');
8247: gms_error_pkg.gms_debug (g_error_procedure_name||':Total REQ txns in gms_bc_packets -'||l_req_count,'C');
8248: gms_error_pkg.gms_debug (g_error_procedure_name||':Total PO txns in gms_bc_packets -'||l_po_count,'C');
8249: gms_error_pkg.gms_debug (g_error_procedure_name||':Total AP txns in gms_bc_packets -'||l_ap_count,'C');
8250: gms_error_pkg.gms_debug (g_error_procedure_name||':Total FAB txns in gms_bc_packets -'||l_fab_count,'C');
8251: gms_error_pkg.gms_debug (g_error_procedure_name||':Document type (ALL indicates Submit/Baseline)-'||g_doc_type,'C');
8244:
8245: IF g_debug = 'Y' THEN
8246: gms_error_pkg.gms_debug (g_error_procedure_name||':Total txns. in gms_bc_packets -'||l_gms_txn_count, 'C');
8247: gms_error_pkg.gms_debug (g_error_procedure_name||':Total REQ txns in gms_bc_packets -'||l_req_count,'C');
8248: gms_error_pkg.gms_debug (g_error_procedure_name||':Total PO txns in gms_bc_packets -'||l_po_count,'C');
8249: gms_error_pkg.gms_debug (g_error_procedure_name||':Total AP txns in gms_bc_packets -'||l_ap_count,'C');
8250: gms_error_pkg.gms_debug (g_error_procedure_name||':Total FAB txns in gms_bc_packets -'||l_fab_count,'C');
8251: gms_error_pkg.gms_debug (g_error_procedure_name||':Document type (ALL indicates Submit/Baseline)-'||g_doc_type,'C');
8252: END IF;
8245: IF g_debug = 'Y' THEN
8246: gms_error_pkg.gms_debug (g_error_procedure_name||':Total txns. in gms_bc_packets -'||l_gms_txn_count, 'C');
8247: gms_error_pkg.gms_debug (g_error_procedure_name||':Total REQ txns in gms_bc_packets -'||l_req_count,'C');
8248: gms_error_pkg.gms_debug (g_error_procedure_name||':Total PO txns in gms_bc_packets -'||l_po_count,'C');
8249: gms_error_pkg.gms_debug (g_error_procedure_name||':Total AP txns in gms_bc_packets -'||l_ap_count,'C');
8250: gms_error_pkg.gms_debug (g_error_procedure_name||':Total FAB txns in gms_bc_packets -'||l_fab_count,'C');
8251: gms_error_pkg.gms_debug (g_error_procedure_name||':Document type (ALL indicates Submit/Baseline)-'||g_doc_type,'C');
8252: END IF;
8253:
8246: gms_error_pkg.gms_debug (g_error_procedure_name||':Total txns. in gms_bc_packets -'||l_gms_txn_count, 'C');
8247: gms_error_pkg.gms_debug (g_error_procedure_name||':Total REQ txns in gms_bc_packets -'||l_req_count,'C');
8248: gms_error_pkg.gms_debug (g_error_procedure_name||':Total PO txns in gms_bc_packets -'||l_po_count,'C');
8249: gms_error_pkg.gms_debug (g_error_procedure_name||':Total AP txns in gms_bc_packets -'||l_ap_count,'C');
8250: gms_error_pkg.gms_debug (g_error_procedure_name||':Total FAB txns in gms_bc_packets -'||l_fab_count,'C');
8251: gms_error_pkg.gms_debug (g_error_procedure_name||':Document type (ALL indicates Submit/Baseline)-'||g_doc_type,'C');
8252: END IF;
8253:
8254: -- ======================================
8315: pod.po_header_id po_header_id,
8316: pod.project_id project_id,
8317: pod.task_id task_id,
8318: adl.award_id award_id
8319: FROM gms_bc_packets pkt,
8320: gl_bc_packets gl,
8321: ap_invoice_distributions_all ap,
8322: po_distributions_all pod,
8323: gms_award_distributions adl
8345: award_id,
8346: expenditure_type,
8347: document_type,
8348: SUM ( NVL (entered_dr, 0) - NVL (entered_cr, 0)) raw_cost
8349: FROM gms_bc_packets
8350: WHERE packet_id = x_packet_id
8351: and nvl(burden_adjustment_flag,'N') = 'N'
8352: AND EXISTS ( SELECT 1
8353: FROM gms_bc_packets
8349: FROM gms_bc_packets
8350: WHERE packet_id = x_packet_id
8351: and nvl(burden_adjustment_flag,'N') = 'N'
8352: AND EXISTS ( SELECT 1
8353: FROM gms_bc_packets
8354: WHERE packet_id = x_packet_id
8355: AND document_type = 'AP')
8356: GROUP BY packet_id,
8357: document_header_id,
8369: x_expenditure_type VARCHAR2
8370: )
8371: IS
8372: SELECT MIN (bc_packet_id)
8373: FROM gms_bc_packets
8374: WHERE packet_id = x_packet_id
8375: AND document_type = x_doc_type
8376: AND document_header_id = x_document_header_id
8377: AND document_distribution_id = x_document_distribution_id
8392: CLOSE financials_options;
8393:
8394: FOR bc_packets IN c_po_doc (l_inv_encumbrance_type_id)
8395: LOOP
8396: UPDATE gms_bc_packets
8397: SET document_type = 'PO',
8398: document_header_id = bc_packets.po_header_id,
8399: document_distribution_id = bc_packets.po_dist_id,
8400: project_id = bc_packets.project_id ,
8402: award_id = bc_packets.award_id
8403: WHERE ROWID = bc_packets.pkt_row_id;
8404: END LOOP;
8405:
8406: Delete from gms_bc_packets
8407: Where packet_id = x_packet_id
8408: And document_type = 'AP'
8409: And bc_packet_id in
8410: ( select a.bc_packet_id
8407: Where packet_id = x_packet_id
8408: And document_type = 'AP'
8409: And bc_packet_id in
8410: ( select a.bc_packet_id
8411: from gms_bc_packets a,
8412: ap_invoice_distributions_all apd
8413: where a.packet_id = x_packet_id
8414: and a.document_type = 'AP'
8415: and a.document_header_id = apd.invoice_id
8416: and a.document_distribution_id = apd.distribution_line_number
8417: and NVL(apd.pa_addition_flag,'X') = 'T'
8418: union /* BUG 14216205 : Added the union for SAT */
8419: select a.bc_packet_id
8420: from GMS_BC_PACKETS a,
8421: AP_SELF_ASSESSED_TAX_DIST_ALL apsat
8422: where a.packet_id = x_packet_id
8423: and a.DOCUMENT_TYPE = 'AP'
8424: and a.DOCUMENT_HEADER_ID = APSAT.INVOICE_ID
8446: CLOSE min_bc_packet_id;
8447:
8448: IF bc_packets.raw_cost >= 0
8449: THEN
8450: UPDATE gms_bc_packets
8451: SET entered_dr = bc_packets.raw_cost,
8452: entered_cr = 0
8453: WHERE packet_id = x_packet_id
8454: AND document_type = bc_packets.document_type
8460: -- lines should be updated with 0. This is done for the same AP
8461: -- Distribution Line. BC packets gets data in multiple lines for
8462: -- the same distribution line.
8463: -- ---------------------------------------------------------------
8464: UPDATE gms_bc_packets
8465: SET entered_cr = 0,
8466: entered_dr = 0
8467: WHERE packet_id = x_packet_id
8468: AND bc_packet_id > l_bc_packet_id
8473: AND expenditure_type = bc_packets.expenditure_type;
8474:
8475: ELSIF bc_packets.raw_cost < 0
8476: THEN
8477: UPDATE gms_bc_packets
8478: SET entered_cr = bc_packets.raw_cost * -1,
8479: entered_dr = 0
8480: WHERE packet_id = x_packet_id
8481: AND document_type = bc_packets.document_type
8486: -- lines should be updated with 0. This is done for the same AP
8487: -- Distribution Line. BC packets gets data in multiple lines for
8488: -- the same distribution line.
8489: -- ---------------------------------------------------------------
8490: UPDATE gms_bc_packets
8491: SET entered_cr = 0,
8492: entered_dr = 0
8493: WHERE packet_id = x_packet_id
8494: AND bc_packet_id > l_bc_packet_id
8506:
8507: select 1
8508: into l_pkt_row
8509: from dual
8510: where exists ( select 1 from gms_bc_packets
8511: where packet_id = x_packet_id ) ;
8512:
8513: x_pkt_row := l_pkt_row ;
8514: EXCEPTION
8525:
8526: -- ------------------------------------ R12 Start ------------------------------------------------+
8527: -- R12 Changes: New procedure
8528: -- --------------------------------------------------------------------------------+
8529: -- This procedure will update the following columns in gms_bc_packets: serial_id,
8530: -- session_id,packet_id,period_name,period_year,period_num,account_type and status.
8531: -- Status will be upated from I to P. Called from gms_fck
8532: -- --------------------------------------------------------------------------------+
8533: PROCEDURE Synch_gms_gl_packets(p_packet_id IN Number)
8531: -- Status will be upated from I to P. Called from gms_fck
8532: -- --------------------------------------------------------------------------------+
8533: PROCEDURE Synch_gms_gl_packets(p_packet_id IN Number)
8534: IS
8535: l_gms_packet_id gms_bc_packets.packet_id%type;
8536:
8537: -- This cursor will fetch only if called for AP/PO/REQ
8538: CURSOR get_temp_packet_id Is
8539: Select gbc.packet_id
8536:
8537: -- This cursor will fetch only if called for AP/PO/REQ
8538: CURSOR get_temp_packet_id Is
8539: Select gbc.packet_id
8540: from gms_bc_packets gbc
8541: where gbc.source_event_id in
8542: (select glbc.event_id
8543: from gl_bc_packets glbc
8544: where glbc.packet_id = p_packet_id)
8583: IF g_debug = 'Y' THEN
8584: gms_error_pkg.gms_debug ('Synch_gms_gl_packets Strat : Previously establised packet is = '|| l_gms_packet_id,'C');
8585: END IF;
8586:
8587: /* Bug 5250793 : Added a join with gl_encumbrance_types so that the gl_bc_packets_rowid on gms_bc_packets is updated
8588: correctly for an invoice matched to a PO scenario. Before this change , the same gl_bc_packets_rowid was updated on
8589: gms_bc_packets for both the PO reversal and AP reserve records irrespective of the corresponding rowid on gl_bc_packets.*/
8590:
8591: -- Update gms_bc_packets data
8585: END IF;
8586:
8587: /* Bug 5250793 : Added a join with gl_encumbrance_types so that the gl_bc_packets_rowid on gms_bc_packets is updated
8588: correctly for an invoice matched to a PO scenario. Before this change , the same gl_bc_packets_rowid was updated on
8589: gms_bc_packets for both the PO reversal and AP reserve records irrespective of the corresponding rowid on gl_bc_packets.*/
8590:
8591: -- Update gms_bc_packets data
8592: /* Bug 5285217 : Changed the code to use "FOR" loop so that gl_bc_packets_rowid on gms_bc_packets is updated correctly
8593: for an invoice matched to a PO with Quantity Variance Scenario. Before this change , the same gl_bc_packets_rowid was
8587: /* Bug 5250793 : Added a join with gl_encumbrance_types so that the gl_bc_packets_rowid on gms_bc_packets is updated
8588: correctly for an invoice matched to a PO scenario. Before this change , the same gl_bc_packets_rowid was updated on
8589: gms_bc_packets for both the PO reversal and AP reserve records irrespective of the corresponding rowid on gl_bc_packets.*/
8590:
8591: -- Update gms_bc_packets data
8592: /* Bug 5285217 : Changed the code to use "FOR" loop so that gl_bc_packets_rowid on gms_bc_packets is updated correctly
8593: for an invoice matched to a PO with Quantity Variance Scenario. Before this change , the same gl_bc_packets_rowid was
8594: updated on gms_bc_packets for both the invoice reserve and the quantity variance reserve records irrespective of
8595: the corresponding rowid on gl_bc_packets.*/
8588: correctly for an invoice matched to a PO scenario. Before this change , the same gl_bc_packets_rowid was updated on
8589: gms_bc_packets for both the PO reversal and AP reserve records irrespective of the corresponding rowid on gl_bc_packets.*/
8590:
8591: -- Update gms_bc_packets data
8592: /* Bug 5285217 : Changed the code to use "FOR" loop so that gl_bc_packets_rowid on gms_bc_packets is updated correctly
8593: for an invoice matched to a PO with Quantity Variance Scenario. Before this change , the same gl_bc_packets_rowid was
8594: updated on gms_bc_packets for both the invoice reserve and the quantity variance reserve records irrespective of
8595: the corresponding rowid on gl_bc_packets.*/
8596:
8590:
8591: -- Update gms_bc_packets data
8592: /* Bug 5285217 : Changed the code to use "FOR" loop so that gl_bc_packets_rowid on gms_bc_packets is updated correctly
8593: for an invoice matched to a PO with Quantity Variance Scenario. Before this change , the same gl_bc_packets_rowid was
8594: updated on gms_bc_packets for both the invoice reserve and the quantity variance reserve records irrespective of
8595: the corresponding rowid on gl_bc_packets.*/
8596:
8597: FOR glbcrec in c_gl_bc_pkt LOOP
8598:
8628:
8629: End loop;
8630: End If; --If glbcrec.source_distribution_type = 'AP_PREPAY' then
8631:
8632: -- Update gms_bc_packets data
8633: Update gms_bc_packets gbc
8634: set (gbc.packet_id,gbc.status_code,
8635: gbc.session_id,gbc.serial_id,
8636: gbc.je_category_name,
8629: End loop;
8630: End If; --If glbcrec.source_distribution_type = 'AP_PREPAY' then
8631:
8632: -- Update gms_bc_packets data
8633: Update gms_bc_packets gbc
8634: set (gbc.packet_id,gbc.status_code,
8635: gbc.session_id,gbc.serial_id,
8636: gbc.je_category_name,
8637: gbc.je_source_name,gbc.period_name,
8667: So the 'gl_bc_packets_rowid IS NULL' and 'ROWNUM = 1' conditions are used to differentiate between
8668: the two records.
8669: For the first AP record (either invoice reserve record or the Quantity variance reserve record) in gl_bc_packets,
8670: the 'ROWNUM=1' and the (entered_dr-entered_cr) conditions are used to identify the corresponding record
8671: in gms_bc_packets. The (entered_dr-entered_cr) check is not suitable for the scenario in which the invoice amount and the quantity
8672: variance amount are same. But as the amounts are same for both the invoice reserve and quantity variance reserve
8673: records , only 'ROWNUM=1' check will suffice as we need not distinguish between the invoice reserve and the
8674: quantity variance record.
8675: For the second AP record (one among the invoice reserve record or the Quantity variance reserve record for which the
8672: variance amount are same. But as the amounts are same for both the invoice reserve and quantity variance reserve
8673: records , only 'ROWNUM=1' check will suffice as we need not distinguish between the invoice reserve and the
8674: quantity variance record.
8675: For the second AP record (one among the invoice reserve record or the Quantity variance reserve record for which the
8676: corresponding record is not yet updated in gms_bc_packets) in gl_bc_packets , the 'gl_bc_packets_rowid IS NULL'
8677: condition is used to identify the corresponding record in gms_bc_packets.*/
8678: and gbc.gl_bc_packets_rowid IS NULL
8679: and ((nvl(gbc.entered_dr,0) - nvl(gbc.entered_cr,0)) = (nvl(glbcrec.entered_dr,0) - nvl(glbcrec.entered_cr,0)))
8680: and ROWNUM = 1
8673: records , only 'ROWNUM=1' check will suffice as we need not distinguish between the invoice reserve and the
8674: quantity variance record.
8675: For the second AP record (one among the invoice reserve record or the Quantity variance reserve record for which the
8676: corresponding record is not yet updated in gms_bc_packets) in gl_bc_packets , the 'gl_bc_packets_rowid IS NULL'
8677: condition is used to identify the corresponding record in gms_bc_packets.*/
8678: and gbc.gl_bc_packets_rowid IS NULL
8679: and ((nvl(gbc.entered_dr,0) - nvl(gbc.entered_cr,0)) = (nvl(glbcrec.entered_dr,0) - nvl(glbcrec.entered_cr,0)))
8680: and ROWNUM = 1
8681: -- If it's PO mathed to an AP then for the PO reversal record in gl_bc_packets source_distribution_id_num_1 is populated as invoice_distribution_id
8678: and gbc.gl_bc_packets_rowid IS NULL
8679: and ((nvl(gbc.entered_dr,0) - nvl(gbc.entered_cr,0)) = (nvl(glbcrec.entered_dr,0) - nvl(glbcrec.entered_cr,0)))
8680: and ROWNUM = 1
8681: -- If it's PO mathed to an AP then for the PO reversal record in gl_bc_packets source_distribution_id_num_1 is populated as invoice_distribution_id
8682: -- whereas for the corresponding record gms_bc_packets will have source_distribution_id_num_1 as po_distribution_id
8683: -- Hence for this scenario we will check encumbrance_type_id to get PO record from GL.
8684: -- This update is NOT for the PO reversal record in an AP matched to a PO scenario.
8685: and 1 >= (select count(distinct glbc1.encumbrance_type_id) -- This will return more than one count for the PO reversal record in an AP matched to a PO scenario.
8686: from gl_bc_packets glbc1
8695: gms_error_pkg.gms_debug ('Synch_gms_gl_packets Strat : Updated bc packets except PO matched to an invoice '|| SQL%ROWCOUNT,'C');
8696: END IF;
8697:
8698: -- If it's PO mathed to an AP then for the PO reversal record in gl_bc_packets source_distribution_id_num_1 is populated as invoice_distribution_id
8699: -- whereas for the corresponding record gms_bc_packets will have source_distribution_id_num_1 as po_distribution_id
8700: -- Hence for this scenario we will check encumbrance_type_id to get PO record from GL.
8701: -- This update is for the PO reversal records , which will be only records left with gbc.packet_id = l_gms_packet_id , in an AP matched to a PO scenario.
8702:
8703: Update gms_bc_packets gbc
8699: -- whereas for the corresponding record gms_bc_packets will have source_distribution_id_num_1 as po_distribution_id
8700: -- Hence for this scenario we will check encumbrance_type_id to get PO record from GL.
8701: -- This update is for the PO reversal records , which will be only records left with gbc.packet_id = l_gms_packet_id , in an AP matched to a PO scenario.
8702:
8703: Update gms_bc_packets gbc
8704: set (gbc.packet_id,gbc.status_code,
8705: gbc.session_id,gbc.serial_id,
8706: gbc.period_name,
8707: gbc.period_year,gbc.period_num,
8736: gms_error_pkg.gms_debug ('Synch_gms_gl_packets Strat : Updated bc packets for PO matched to an invoice '|| SQL%ROWCOUNT,'C');
8737: END IF;
8738:
8739: /* Bug 5645290 - Start */
8740: Update gms_bc_packets gbc
8741: set (gbc.packet_id,gbc.status_code,
8742: gbc.session_id,gbc.serial_id,
8743: gbc.je_category_name,
8744: gbc.je_source_name,gbc.period_name,
8751: gbcparent.je_source_name,gbcparent.period_name,
8752: gbcparent.period_year,gbcparent.period_num,
8753: gbcparent.account_type,
8754: gbcparent.gl_bc_packets_rowid
8755: from gms_bc_packets gbcparent
8756: where gbcparent.bc_packet_id = gbc.parent_bc_packet_id)
8757: where gbc.packet_id = l_gms_packet_id
8758: and gbc.status_code = 'I'
8759: and gbc.gl_bc_packets_rowid IS NULL
8838: g_packet_id||','||g_mode||','||x_partial,'C');
8839: END IF;
8840:
8841: -- ---------------------------------------------------------------------------------------------------------+
8842: -- 101: R12 Funds Management uptake : This procedure will synch data in gl_bc_packets to gms_bc_packets ..
8843: -- ---------------------------------------------------------------------------------------------------------+
8844: SYNCH_GMS_GL_PACKETS(x_packetid);
8845:
8846: -------------------------------------------------------------------------------+
8877: pa_currency.set_currency_info;
8878:
8879: -------------------------------------------------------------------------------+
8880: -- 4. Call delete_pending_txns to delete bc pkt txns. left in 'P' status ..
8881: -- This procedure will delete pending records from gms_bc_packets
8882: -------------------------------------------------------------------------------+
8883: x_e_stage := 'delete_pending_txns';
8884: IF g_debug = 'Y' THEN
8885: gms_error_pkg.gms_debug (g_error_procedure_name||':'||x_e_stage,'C');
8966: -- copy_gl_pkt_to_gms_pkt of fundscheck process.
8967:
8968: --IF x_mode IN ('R', 'U', 'C', 'E','X') THEN
8969: IF x_mode IN ( 'E','X') THEN
8970: -- Calculate burdenable_cost and update on gms_bc_packets ..............
8971: -- Calling burden calculation for all except for mode : Submit,Baseline,Interface
8972: IF g_debug = 'Y' THEN
8973: x_e_stage := 'Burdenable Raw Cost calculation';
8974: g_error_procedure_name := 'Gms_fck';
9195:
9196: PROCEDURE TIEBACK_FAILED_ACCT_STATUS (p_bc_mode IN VARCHAR2 DEFAULT 'C') IS
9197: BEGIN
9198:
9199: UPDATE gms_bc_packets
9200: SET status_code = DECODE(p_bc_mode,'C','F','R'),
9201: result_code = 'F22'
9202: WHERE status_code in ('I','A','S')
9203: AND source_event_id IN