[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 1
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 1
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 1
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 1
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)
481: --If p_mode in ('X','E') then
482:
483: /* Bug 5250793 : Added code such that if an AP Invoice distribution fails fundscheck then the PO matched to that AP also fails
484: with full mode failure. */
485: Update gms_bc_packets bp
486: set bp.status_code = decode(p_mode,'C','F','R'),
487: bp.result_code = decode(substr(bp.result_code,1,1),'P','F65',null,'F65',bp.result_code),
488: 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)
489: where bp.document_type = 'PO'
489: where bp.document_type = 'PO'
490: and bp.packet_id = p_packet_id
491: and bp.status_code = 'P'
492: and bp.document_distribution_id in ( select distinct apid.po_distribution_id
493: from gms_bc_packets bp1,
494: ap_invoice_distributions_all apid
495: where bp1.packet_id = p_packet_id
496: and bp1.document_type = 'AP'
497: and bp1.document_distribution_id = apid.invoice_distribution_id
498: and substr(bp1.result_code,1,1) = 'F'
499: and apid.po_distribution_id IS NOT NULL ) ;
500:
501:
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, one of the burden failed) at stage:'||p_level,bp.fc_error_message)
506: where bp.packet_id = p_packet_id
507: and bp.status_code = 'P'
508: and bp.document_type in ('EXP','ENC','AP','PO','REQ')
509: and bp.parent_bc_packet_id is NOT NULL
510: and exists (select 1
511: from gms_bc_packets bp1
512: where bp1.packet_id = bp.packet_id /* Changed the order for Bug 6043224 */
513: and bp1.parent_bc_packet_id = bp.parent_bc_packet_id
514: and bp1.bc_packet_id <> bp.bc_packet_id /* Uncommented for Bug 6043224 */
515: and bp1.document_type = bp.document_type /* Uncommented for Bug 6043224 */
530: -- 2. Fail all cdls if one cdl failed for an EXP
531:
532: If p_mode = 'X' then
533:
534: Update gms_bc_packets bp
535: set bp.status_code = decode(p_mode,'C','F','R'),
536: bp.result_code = decode(substr(bp.result_code,1,1),'P','F65',null,'F65',bp.result_code),
537: 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)
538: where bp.packet_id = p_packet_id
538: where bp.packet_id = p_packet_id
539: and bp.status_code = 'P'
540: and bp.document_type = 'EXP'
541: and exists (select 1
542: from gms_bc_packets bp1
543: where bp1.packet_id = bp.packet_id
544: and bp1.document_header_id = bp.document_header_id
545: and bp1.document_distribution_id <> bp.document_distribution_id
546: and bp1.document_type = bp.document_type
584:
585: Cursor c_txn is
586: select adjusted_document_header_id,
587: nvl(ind_compiled_set_id,-1) ind_compiled_set_id
588: from gms_bc_packets
589: where packet_id = p_packetid
590: and document_type = 'ENC'
591: having sum(entered_dr-entered_cr) = 0
592: group by adjusted_document_header_id,
599: gms_error_pkg.gms_debug ( 'p_mode : '||p_mode,'C');
600:
601: If p_mode = 'Check_Adjusted' then
602: -- Fail adjusting txn. If adjusted has not been funds checked -F08
603: update gms_bc_packets gbc
604: set gbc.result_code = 'F08',
605: gbc.award_result_code = 'F08',
606: gbc.top_task_result_code = 'F08',
607: gbc.task_result_code = 'F08',
624: -- Adjusted and adjusting in same packet
625: for recs in c_txn
626: loop
627:
628: update gms_bc_packets gbc
629: set gbc.result_code = 'P82',
630: gbc.award_result_code = 'P82',
631: gbc.top_task_result_code = 'P82',
632: gbc.task_result_code = 'P82',
652:
653: -- R12 Funds management uptake
654: PRAGMA AUTONOMOUS_TRANSACTION;
655:
656: TYPE t_project_id_type IS TABLE OF gms_bc_packets.project_id%type;
657: TYPE t_award_id_type IS TABLE OF gms_bc_packets.award_id%type;
658: TYPE t_task_id_type IS TABLE OF gms_bc_packets.task_id%type;
659: TYPE t_exp_date_type IS TABLE OF gms_bc_packets.expenditure_item_date%type;
660: TYPE t_exp_type_type IS TABLE OF gms_bc_packets.expenditure_type%type;
653: -- R12 Funds management uptake
654: PRAGMA AUTONOMOUS_TRANSACTION;
655:
656: TYPE t_project_id_type IS TABLE OF gms_bc_packets.project_id%type;
657: TYPE t_award_id_type IS TABLE OF gms_bc_packets.award_id%type;
658: TYPE t_task_id_type IS TABLE OF gms_bc_packets.task_id%type;
659: TYPE t_exp_date_type IS TABLE OF gms_bc_packets.expenditure_item_date%type;
660: TYPE t_exp_type_type IS TABLE OF gms_bc_packets.expenditure_type%type;
661: TYPE t_exp_org_type IS TABLE OF gms_bc_packets.expenditure_organization_id%type;
654: PRAGMA AUTONOMOUS_TRANSACTION;
655:
656: TYPE t_project_id_type IS TABLE OF gms_bc_packets.project_id%type;
657: TYPE t_award_id_type IS TABLE OF gms_bc_packets.award_id%type;
658: TYPE t_task_id_type IS TABLE OF gms_bc_packets.task_id%type;
659: TYPE t_exp_date_type IS TABLE OF gms_bc_packets.expenditure_item_date%type;
660: TYPE t_exp_type_type IS TABLE OF gms_bc_packets.expenditure_type%type;
661: TYPE t_exp_org_type IS TABLE OF gms_bc_packets.expenditure_organization_id%type;
662: TYPE t_ind_set_type IS TABLE OF gms_bc_packets.ind_compiled_set_id%type;
655:
656: TYPE t_project_id_type IS TABLE OF gms_bc_packets.project_id%type;
657: TYPE t_award_id_type IS TABLE OF gms_bc_packets.award_id%type;
658: TYPE t_task_id_type IS TABLE OF gms_bc_packets.task_id%type;
659: TYPE t_exp_date_type IS TABLE OF gms_bc_packets.expenditure_item_date%type;
660: TYPE t_exp_type_type IS TABLE OF gms_bc_packets.expenditure_type%type;
661: TYPE t_exp_org_type IS TABLE OF gms_bc_packets.expenditure_organization_id%type;
662: TYPE t_ind_set_type IS TABLE OF gms_bc_packets.ind_compiled_set_id%type;
663:
656: TYPE t_project_id_type IS TABLE OF gms_bc_packets.project_id%type;
657: TYPE t_award_id_type IS TABLE OF gms_bc_packets.award_id%type;
658: TYPE t_task_id_type IS TABLE OF gms_bc_packets.task_id%type;
659: TYPE t_exp_date_type IS TABLE OF gms_bc_packets.expenditure_item_date%type;
660: TYPE t_exp_type_type IS TABLE OF gms_bc_packets.expenditure_type%type;
661: TYPE t_exp_org_type IS TABLE OF gms_bc_packets.expenditure_organization_id%type;
662: TYPE t_ind_set_type IS TABLE OF gms_bc_packets.ind_compiled_set_id%type;
663:
664: t_project_id t_project_id_type;
657: TYPE t_award_id_type IS TABLE OF gms_bc_packets.award_id%type;
658: TYPE t_task_id_type IS TABLE OF gms_bc_packets.task_id%type;
659: TYPE t_exp_date_type IS TABLE OF gms_bc_packets.expenditure_item_date%type;
660: TYPE t_exp_type_type IS TABLE OF gms_bc_packets.expenditure_type%type;
661: TYPE t_exp_org_type IS TABLE OF gms_bc_packets.expenditure_organization_id%type;
662: TYPE t_ind_set_type IS TABLE OF gms_bc_packets.ind_compiled_set_id%type;
663:
664: t_project_id t_project_id_type;
665: t_award_id t_award_id_type;
658: TYPE t_task_id_type IS TABLE OF gms_bc_packets.task_id%type;
659: TYPE t_exp_date_type IS TABLE OF gms_bc_packets.expenditure_item_date%type;
660: TYPE t_exp_type_type IS TABLE OF gms_bc_packets.expenditure_type%type;
661: TYPE t_exp_org_type IS TABLE OF gms_bc_packets.expenditure_organization_id%type;
662: TYPE t_ind_set_type IS TABLE OF gms_bc_packets.ind_compiled_set_id%type;
663:
664: t_project_id t_project_id_type;
665: t_award_id t_award_id_type;
666: t_task_id t_task_id_type;
691: t_exp_date,
692: t_exp_type,
693: t_exp_org,
694: t_ind_set
695: from gms_bc_packets gbc
696: where gbc.packet_id = p_packet_id
697: and gbc.status_code = 'P'
698: and gbc.ind_compiled_set_id is null
699: and nvl(gbc.burden_adjustment_flag,'N') = 'N' -- 3389292
720: so as to ensure that we relieve burden component same as it was reserved for a PO/AP/REQ transaction
721: */
722:
723: FORALL j IN t_project_id.FIRST .. t_project_id.LAST
724: Update /*+ index(gbc GMS_BC_PACKETS_N1) */ gms_bc_packets gbc /*Added hint for bug 5683910 */
725: set ind_compiled_Set_id = (nvl((select ind_compiled_set_id from gms_award_distributions
726: where document_type = gbc.document_type
727: and ((document_type = 'AP' and
728: invoice_id = gbc.document_header_id and
756:
757: -- Update 1 : For Adjusting (Reversing) transactions whose original transaction
758: -- is in the same packet
759:
760: Update gms_bc_packets gbc
761: set gbc.ind_compiled_Set_id = (Select gbc1.ind_compiled_set_id
762: from gms_bc_packets gbc1
763: where gbc1.packet_id = p_packet_id
764: and gbc1.document_header_id = gbc.adjusted_document_header_id
758: -- is in the same packet
759:
760: Update gms_bc_packets gbc
761: set gbc.ind_compiled_Set_id = (Select gbc1.ind_compiled_set_id
762: from gms_bc_packets gbc1
763: where gbc1.packet_id = p_packet_id
764: and gbc1.document_header_id = gbc.adjusted_document_header_id
765: /* bug 6414366 start */
766: and gbc1.document_distribution_id =
764: and gbc1.document_header_id = gbc.adjusted_document_header_id
765: /* bug 6414366 start */
766: and gbc1.document_distribution_id =
767: (select max(gbc2.document_distribution_id)
768: from gms_bc_packets gbc2
769: where gbc2.packet_id = p_packet_id
770: and gbc2.document_header_id = gbc.adjusted_document_header_id))
771: /* bug 6414366 end */
772: where gbc.packet_id = p_packet_id
780: gms_error_pkg.gms_debug ( 'After Update 1','C');
781: -- Bug#6075039 Modified the sub query for performance issue.
782: -- Update 2 : For Adjusting (Reversing) transactions whose original transaction
783: -- was funds checked earlier
784: Update gms_bc_packets gbc
785: set gbc.ind_compiled_Set_id = (Select nvl(gei.ind_compiled_set_id, adl.ind_compiled_set_id) --Bug 5122879
786: from gms_encumbrance_items gei,
787: gms_award_distributions adl
788: where adl.expenditure_item_id =gbc.adjusted_document_header_id
808:
809: /*--------------------------------------------------------------------------------------------------------
810: -- This procedure updates table values
811: -- TABLE Columns
812: -- GMS_BC_PACKETS STATUS_CODE
813: -- GMS_AWARD_DISTRIBUTIONS FC_STATUS
814: -- RESOURCE_LIST_MEMBER_ID
815: -- BUD_TASK_ID(Budgeted Task)
816: -- BUD_RES_LIST_MEMBER_ID(Budgeted rlmi)
839:
840: x_dummy NUMBER; -- Bug 2181546, Added
841: CURSOR c_failed_packet IS -- Bug 2181546, Added
842: SELECT 1
843: FROM gms_bc_packets
844: WHERE packet_id = p_packet_id
845: AND SUBSTR (nvl(result_code,'F65'), 1, 1) = 'F' ;
846:
847: /* Introduced for Bug# 4159238 (BaseBug#4292763)*/
845: AND SUBSTR (nvl(result_code,'F65'), 1, 1) = 'F' ;
846:
847: /* Introduced for Bug# 4159238 (BaseBug#4292763)*/
848:
849: TYPE tab_doc_head_id IS TABLE OF gms_bc_packets.document_header_id%TYPE;
850: TYPE tab_doc_type IS TABLE OF gms_bc_packets.document_type%TYPE;
851: TYPE tab_res_code IS TABLE OF gms_bc_packets.result_code%TYPE;
852: TYPE tab_sta_code IS TABLE OF gms_bc_packets.status_code%TYPE;
853: TYPE tab_dr_code IS TABLE OF gms_bc_packets.entered_dr%TYPE;
846:
847: /* Introduced for Bug# 4159238 (BaseBug#4292763)*/
848:
849: TYPE tab_doc_head_id IS TABLE OF gms_bc_packets.document_header_id%TYPE;
850: TYPE tab_doc_type IS TABLE OF gms_bc_packets.document_type%TYPE;
851: TYPE tab_res_code IS TABLE OF gms_bc_packets.result_code%TYPE;
852: TYPE tab_sta_code IS TABLE OF gms_bc_packets.status_code%TYPE;
853: TYPE tab_dr_code IS TABLE OF gms_bc_packets.entered_dr%TYPE;
854: TYPE tab_cr_code IS TABLE OF gms_bc_packets.entered_cr%TYPE;
847: /* Introduced for Bug# 4159238 (BaseBug#4292763)*/
848:
849: TYPE tab_doc_head_id IS TABLE OF gms_bc_packets.document_header_id%TYPE;
850: TYPE tab_doc_type IS TABLE OF gms_bc_packets.document_type%TYPE;
851: TYPE tab_res_code IS TABLE OF gms_bc_packets.result_code%TYPE;
852: TYPE tab_sta_code IS TABLE OF gms_bc_packets.status_code%TYPE;
853: TYPE tab_dr_code IS TABLE OF gms_bc_packets.entered_dr%TYPE;
854: TYPE tab_cr_code IS TABLE OF gms_bc_packets.entered_cr%TYPE;
855: TYPE tab_bud_task_id IS TABLE OF gms_bc_packets.bud_task_id%TYPE;
848:
849: TYPE tab_doc_head_id IS TABLE OF gms_bc_packets.document_header_id%TYPE;
850: TYPE tab_doc_type IS TABLE OF gms_bc_packets.document_type%TYPE;
851: TYPE tab_res_code IS TABLE OF gms_bc_packets.result_code%TYPE;
852: TYPE tab_sta_code IS TABLE OF gms_bc_packets.status_code%TYPE;
853: TYPE tab_dr_code IS TABLE OF gms_bc_packets.entered_dr%TYPE;
854: TYPE tab_cr_code IS TABLE OF gms_bc_packets.entered_cr%TYPE;
855: TYPE tab_bud_task_id IS TABLE OF gms_bc_packets.bud_task_id%TYPE;
856: TYPE tab_proj_id IS TABLE OF gms_bc_packets.project_id%TYPE;
849: TYPE tab_doc_head_id IS TABLE OF gms_bc_packets.document_header_id%TYPE;
850: TYPE tab_doc_type IS TABLE OF gms_bc_packets.document_type%TYPE;
851: TYPE tab_res_code IS TABLE OF gms_bc_packets.result_code%TYPE;
852: TYPE tab_sta_code IS TABLE OF gms_bc_packets.status_code%TYPE;
853: TYPE tab_dr_code IS TABLE OF gms_bc_packets.entered_dr%TYPE;
854: TYPE tab_cr_code IS TABLE OF gms_bc_packets.entered_cr%TYPE;
855: TYPE tab_bud_task_id IS TABLE OF gms_bc_packets.bud_task_id%TYPE;
856: TYPE tab_proj_id IS TABLE OF gms_bc_packets.project_id%TYPE;
857: TYPE tab_res_list_mem_id IS TABLE OF gms_bc_packets.resource_list_member_id%TYPE;
850: TYPE tab_doc_type IS TABLE OF gms_bc_packets.document_type%TYPE;
851: TYPE tab_res_code IS TABLE OF gms_bc_packets.result_code%TYPE;
852: TYPE tab_sta_code IS TABLE OF gms_bc_packets.status_code%TYPE;
853: TYPE tab_dr_code IS TABLE OF gms_bc_packets.entered_dr%TYPE;
854: TYPE tab_cr_code IS TABLE OF gms_bc_packets.entered_cr%TYPE;
855: TYPE tab_bud_task_id IS TABLE OF gms_bc_packets.bud_task_id%TYPE;
856: TYPE tab_proj_id IS TABLE OF gms_bc_packets.project_id%TYPE;
857: TYPE tab_res_list_mem_id IS TABLE OF gms_bc_packets.resource_list_member_id%TYPE;
858: TYPE tab_doc_dist_id IS TABLE OF gms_bc_packets.document_distribution_id%TYPE;
851: TYPE tab_res_code IS TABLE OF gms_bc_packets.result_code%TYPE;
852: TYPE tab_sta_code IS TABLE OF gms_bc_packets.status_code%TYPE;
853: TYPE tab_dr_code IS TABLE OF gms_bc_packets.entered_dr%TYPE;
854: TYPE tab_cr_code IS TABLE OF gms_bc_packets.entered_cr%TYPE;
855: TYPE tab_bud_task_id IS TABLE OF gms_bc_packets.bud_task_id%TYPE;
856: TYPE tab_proj_id IS TABLE OF gms_bc_packets.project_id%TYPE;
857: TYPE tab_res_list_mem_id IS TABLE OF gms_bc_packets.resource_list_member_id%TYPE;
858: TYPE tab_doc_dist_id IS TABLE OF gms_bc_packets.document_distribution_id%TYPE;
859: TYPE tab_task_id IS TABLE OF gms_bc_packets.task_id%TYPE;
852: TYPE tab_sta_code IS TABLE OF gms_bc_packets.status_code%TYPE;
853: TYPE tab_dr_code IS TABLE OF gms_bc_packets.entered_dr%TYPE;
854: TYPE tab_cr_code IS TABLE OF gms_bc_packets.entered_cr%TYPE;
855: TYPE tab_bud_task_id IS TABLE OF gms_bc_packets.bud_task_id%TYPE;
856: TYPE tab_proj_id IS TABLE OF gms_bc_packets.project_id%TYPE;
857: TYPE tab_res_list_mem_id IS TABLE OF gms_bc_packets.resource_list_member_id%TYPE;
858: TYPE tab_doc_dist_id IS TABLE OF gms_bc_packets.document_distribution_id%TYPE;
859: TYPE tab_task_id IS TABLE OF gms_bc_packets.task_id%TYPE;
860: TYPE tab_exp_item_date IS TABLE OF gms_bc_packets.expenditure_item_date%TYPE;
853: TYPE tab_dr_code IS TABLE OF gms_bc_packets.entered_dr%TYPE;
854: TYPE tab_cr_code IS TABLE OF gms_bc_packets.entered_cr%TYPE;
855: TYPE tab_bud_task_id IS TABLE OF gms_bc_packets.bud_task_id%TYPE;
856: TYPE tab_proj_id IS TABLE OF gms_bc_packets.project_id%TYPE;
857: TYPE tab_res_list_mem_id IS TABLE OF gms_bc_packets.resource_list_member_id%TYPE;
858: TYPE tab_doc_dist_id IS TABLE OF gms_bc_packets.document_distribution_id%TYPE;
859: TYPE tab_task_id IS TABLE OF gms_bc_packets.task_id%TYPE;
860: TYPE tab_exp_item_date IS TABLE OF gms_bc_packets.expenditure_item_date%TYPE;
861: TYPE tab_award_id IS TABLE OF gms_bc_packets.award_id%TYPE;
854: TYPE tab_cr_code IS TABLE OF gms_bc_packets.entered_cr%TYPE;
855: TYPE tab_bud_task_id IS TABLE OF gms_bc_packets.bud_task_id%TYPE;
856: TYPE tab_proj_id IS TABLE OF gms_bc_packets.project_id%TYPE;
857: TYPE tab_res_list_mem_id IS TABLE OF gms_bc_packets.resource_list_member_id%TYPE;
858: TYPE tab_doc_dist_id IS TABLE OF gms_bc_packets.document_distribution_id%TYPE;
859: TYPE tab_task_id IS TABLE OF gms_bc_packets.task_id%TYPE;
860: TYPE tab_exp_item_date IS TABLE OF gms_bc_packets.expenditure_item_date%TYPE;
861: TYPE tab_award_id IS TABLE OF gms_bc_packets.award_id%TYPE;
862: TYPE tab_exp_orgnzt_id IS TABLE OF gms_bc_packets.expenditure_organization_id%TYPE;
855: TYPE tab_bud_task_id IS TABLE OF gms_bc_packets.bud_task_id%TYPE;
856: TYPE tab_proj_id IS TABLE OF gms_bc_packets.project_id%TYPE;
857: TYPE tab_res_list_mem_id IS TABLE OF gms_bc_packets.resource_list_member_id%TYPE;
858: TYPE tab_doc_dist_id IS TABLE OF gms_bc_packets.document_distribution_id%TYPE;
859: TYPE tab_task_id IS TABLE OF gms_bc_packets.task_id%TYPE;
860: TYPE tab_exp_item_date IS TABLE OF gms_bc_packets.expenditure_item_date%TYPE;
861: TYPE tab_award_id IS TABLE OF gms_bc_packets.award_id%TYPE;
862: TYPE tab_exp_orgnzt_id IS TABLE OF gms_bc_packets.expenditure_organization_id%TYPE;
863: TYPE tab_packet_id IS TABLE OF gms_bc_packets.packet_id%TYPE;
856: TYPE tab_proj_id IS TABLE OF gms_bc_packets.project_id%TYPE;
857: TYPE tab_res_list_mem_id IS TABLE OF gms_bc_packets.resource_list_member_id%TYPE;
858: TYPE tab_doc_dist_id IS TABLE OF gms_bc_packets.document_distribution_id%TYPE;
859: TYPE tab_task_id IS TABLE OF gms_bc_packets.task_id%TYPE;
860: TYPE tab_exp_item_date IS TABLE OF gms_bc_packets.expenditure_item_date%TYPE;
861: TYPE tab_award_id IS TABLE OF gms_bc_packets.award_id%TYPE;
862: TYPE tab_exp_orgnzt_id IS TABLE OF gms_bc_packets.expenditure_organization_id%TYPE;
863: TYPE tab_packet_id IS TABLE OF gms_bc_packets.packet_id%TYPE;
864: TYPE tab_bc_packet_id IS TABLE OF gms_bc_packets.bc_packet_id%TYPE;
857: TYPE tab_res_list_mem_id IS TABLE OF gms_bc_packets.resource_list_member_id%TYPE;
858: TYPE tab_doc_dist_id IS TABLE OF gms_bc_packets.document_distribution_id%TYPE;
859: TYPE tab_task_id IS TABLE OF gms_bc_packets.task_id%TYPE;
860: TYPE tab_exp_item_date IS TABLE OF gms_bc_packets.expenditure_item_date%TYPE;
861: TYPE tab_award_id IS TABLE OF gms_bc_packets.award_id%TYPE;
862: TYPE tab_exp_orgnzt_id IS TABLE OF gms_bc_packets.expenditure_organization_id%TYPE;
863: TYPE tab_packet_id IS TABLE OF gms_bc_packets.packet_id%TYPE;
864: TYPE tab_bc_packet_id IS TABLE OF gms_bc_packets.bc_packet_id%TYPE;
865: TYPE tab_exp_type IS TABLE OF gms_bc_packets.expenditure_type%TYPE;
858: TYPE tab_doc_dist_id IS TABLE OF gms_bc_packets.document_distribution_id%TYPE;
859: TYPE tab_task_id IS TABLE OF gms_bc_packets.task_id%TYPE;
860: TYPE tab_exp_item_date IS TABLE OF gms_bc_packets.expenditure_item_date%TYPE;
861: TYPE tab_award_id IS TABLE OF gms_bc_packets.award_id%TYPE;
862: TYPE tab_exp_orgnzt_id IS TABLE OF gms_bc_packets.expenditure_organization_id%TYPE;
863: TYPE tab_packet_id IS TABLE OF gms_bc_packets.packet_id%TYPE;
864: TYPE tab_bc_packet_id IS TABLE OF gms_bc_packets.bc_packet_id%TYPE;
865: TYPE tab_exp_type IS TABLE OF gms_bc_packets.expenditure_type%TYPE;
866: TYPE tab_ind_comp_setid IS TABLE OF gms_bc_packets.ind_compiled_set_id%TYPE;
859: TYPE tab_task_id IS TABLE OF gms_bc_packets.task_id%TYPE;
860: TYPE tab_exp_item_date IS TABLE OF gms_bc_packets.expenditure_item_date%TYPE;
861: TYPE tab_award_id IS TABLE OF gms_bc_packets.award_id%TYPE;
862: TYPE tab_exp_orgnzt_id IS TABLE OF gms_bc_packets.expenditure_organization_id%TYPE;
863: TYPE tab_packet_id IS TABLE OF gms_bc_packets.packet_id%TYPE;
864: TYPE tab_bc_packet_id IS TABLE OF gms_bc_packets.bc_packet_id%TYPE;
865: TYPE tab_exp_type IS TABLE OF gms_bc_packets.expenditure_type%TYPE;
866: TYPE tab_ind_comp_setid IS TABLE OF gms_bc_packets.ind_compiled_set_id%TYPE;
867: TYPE tab_set_of_books_id IS TABLE OF gms_bc_packets.set_of_books_id%TYPE; --Bug 5845974
860: TYPE tab_exp_item_date IS TABLE OF gms_bc_packets.expenditure_item_date%TYPE;
861: TYPE tab_award_id IS TABLE OF gms_bc_packets.award_id%TYPE;
862: TYPE tab_exp_orgnzt_id IS TABLE OF gms_bc_packets.expenditure_organization_id%TYPE;
863: TYPE tab_packet_id IS TABLE OF gms_bc_packets.packet_id%TYPE;
864: TYPE tab_bc_packet_id IS TABLE OF gms_bc_packets.bc_packet_id%TYPE;
865: TYPE tab_exp_type IS TABLE OF gms_bc_packets.expenditure_type%TYPE;
866: TYPE tab_ind_comp_setid IS TABLE OF gms_bc_packets.ind_compiled_set_id%TYPE;
867: TYPE tab_set_of_books_id IS TABLE OF gms_bc_packets.set_of_books_id%TYPE; --Bug 5845974
868:
861: TYPE tab_award_id IS TABLE OF gms_bc_packets.award_id%TYPE;
862: TYPE tab_exp_orgnzt_id IS TABLE OF gms_bc_packets.expenditure_organization_id%TYPE;
863: TYPE tab_packet_id IS TABLE OF gms_bc_packets.packet_id%TYPE;
864: TYPE tab_bc_packet_id IS TABLE OF gms_bc_packets.bc_packet_id%TYPE;
865: TYPE tab_exp_type IS TABLE OF gms_bc_packets.expenditure_type%TYPE;
866: TYPE tab_ind_comp_setid IS TABLE OF gms_bc_packets.ind_compiled_set_id%TYPE;
867: TYPE tab_set_of_books_id IS TABLE OF gms_bc_packets.set_of_books_id%TYPE; --Bug 5845974
868:
869: tdocument_header_id tab_doc_head_id;
862: TYPE tab_exp_orgnzt_id IS TABLE OF gms_bc_packets.expenditure_organization_id%TYPE;
863: TYPE tab_packet_id IS TABLE OF gms_bc_packets.packet_id%TYPE;
864: TYPE tab_bc_packet_id IS TABLE OF gms_bc_packets.bc_packet_id%TYPE;
865: TYPE tab_exp_type IS TABLE OF gms_bc_packets.expenditure_type%TYPE;
866: TYPE tab_ind_comp_setid IS TABLE OF gms_bc_packets.ind_compiled_set_id%TYPE;
867: TYPE tab_set_of_books_id IS TABLE OF gms_bc_packets.set_of_books_id%TYPE; --Bug 5845974
868:
869: tdocument_header_id tab_doc_head_id;
870: tdocument_type tab_doc_type;
863: TYPE tab_packet_id IS TABLE OF gms_bc_packets.packet_id%TYPE;
864: TYPE tab_bc_packet_id IS TABLE OF gms_bc_packets.bc_packet_id%TYPE;
865: TYPE tab_exp_type IS TABLE OF gms_bc_packets.expenditure_type%TYPE;
866: TYPE tab_ind_comp_setid IS TABLE OF gms_bc_packets.ind_compiled_set_id%TYPE;
867: TYPE tab_set_of_books_id IS TABLE OF gms_bc_packets.set_of_books_id%TYPE; --Bug 5845974
868:
869: tdocument_header_id tab_doc_head_id;
870: tdocument_type tab_doc_type;
871: tresult_code tab_res_code;
898: document_distribution_id, task_id, expenditure_item_date,
899: expenditure_type , -- Bug 3003584
900: award_id, expenditure_organization_id, packet_id,
901: bc_packet_id, ind_compiled_set_id -- Added for bug : 2927485
902: FROM gms_bc_packets
903: WHERE packet_id = p_packet_id
904: AND parent_bc_packet_id IS NULL
905: AND nvl(burden_adjustment_flag,'N') = 'N'
906: AND status_code in ('A','B') --Added to fix bug 2138376 from 'B'*/
914: expenditure_type , -- Bug 3003584
915: award_id, expenditure_organization_id, packet_id,
916: bc_packet_id, ind_compiled_set_id, -- Added for bug : 2927485
917: set_of_books_id --Bug 5845974
918: FROM gms_bc_packets
919: WHERE packet_id = p_packet_id
920: AND parent_bc_packet_id IS NULL
921: AND nvl(burden_adjustment_flag,'N') = 'N'
922: AND status_code in ('A','B') --Added to fix bug 2138376 from 'B'*/
929: gbp.result_code,
930: gbp.document_distribution_id,
931: adl.ind_compiled_set_id,
932: gbp.packet_id
933: FROM gms_bc_packets gbp,
934: gms_award_distributions adl
935: WHERE gbp.document_header_id = adl.expenditure_item_id
936: and gbp.document_distribution_id = adl.adl_line_num
937: and gbp.packet_id = p_packet_id
967:
968: -- ---------------------------------------------+
969: -- FULL MODE: FAILURE
970: -- ---------------------------------------------+
971: UPDATE gms_bc_packets
972: SET status_code = decode(p_mode,'S','E','C','F','R'),
973: result_code =
974: DECODE (SUBSTR (NVL (result_code, 'F65'), 1, 1), 'P','F65', NVL(result_code,'F65')), --Bug 2092791 Added NVL Clause
975: fc_error_message = decode(fc_error_message,NULL,g_error_procedure_name,fc_error_message)
979:
980: -- ---------------------------------------------+
981: -- FULL MODE: PASS
982: -- ---------------------------------------------+
983: UPDATE gms_bc_packets
984: SET status_code = decode(p_mode,'S','S','B','B','C','C','A')
985: WHERE packet_id = p_packet_id;
986: IF g_debug = 'Y' THEN
987: gms_error_pkg.gms_debug ('STATUS_CODE_UPDATE - SUBMIT UPDATE FOR PASS TRANSACTIONS', 'C');
993:
994: -- ELSIF ( ( NVL(p_mode,'R') in ('R') and NVL(p_partial,'N') = 'Y' )
995: -- OR ( NVL(p_mode,'R') in ('E'))) THEN
996:
997: UPDATE gms_bc_packets
998: SET status_code = DECODE (SUBSTR (nvl(result_code,'F65'), 1, 1), 'P', 'A', 'R'),
999: fc_error_message = decode(fc_error_message,NULL,g_error_procedure_name,fc_error_message)
1000: WHERE packet_id = p_packet_id;
1001:
1224: RAISE; -- Bug 2181546, Added
1225: END status_code_update;
1226:
1227: ----------------------------------------------------------------------------------------------------------
1228: -- Procedure to update gms_bc_packets when there is a failure ..
1229: -- This Procedure updates
1230: -- status_code ,
1231: -- result_code at Award Level,Task Level,Resource Group Level
1232: -- fc_error_message
1250:
1251: IF p_bc_packet_id is NULL THEN
1252: g_error_stage := 'RESULT_CODE:PACK_ID';
1253:
1254: UPDATE gms_bc_packets
1255: SET status_code = decode(status_code,'P',p_status_code,'I',p_status_code,status_code),
1256: result_code = decode(substr(result_code,1,1),'F',result_code,p_result_code),
1257: fc_error_message = decode(fc_error_message,null,p_fc_error_message,fc_error_message)
1258: WHERE packet_id = p_packet_id;
1258: WHERE packet_id = p_packet_id;
1259: ELSE
1260: g_error_stage := 'RESULT_CODE:BC_PACK_ID';
1261:
1262: UPDATE gms_bc_packets
1263: SET status_code = decode(status_code,'P',p_status_code,'I',p_status_code,status_code),
1264: result_code = decode(substr(result_code,1,1),'F',result_code,p_result_code),
1265: fc_error_message = decode(fc_error_message,null,p_fc_error_message,fc_error_message)
1266: WHERE packet_id = p_packet_id
1638: END misc_synch_adls;
1639:
1640: -- R12 Funds Management Uptake : Obsolete data_transfer_failure procedure as this logic is handled in copy_gl_pkt_to_gms_pkt
1641: --============================================================================================
1642: -- Bug 2899151 : This procedure will fail gl_bc_packets and gms_bc_packets
1643: -- This procedure will be used when all records in gl_bc_packets (corresponding to sponsored)
1644: -- and not existing in gms_bc_packets.
1645: --============================================================================================
1646:
1640: -- R12 Funds Management Uptake : Obsolete data_transfer_failure procedure as this logic is handled in copy_gl_pkt_to_gms_pkt
1641: --============================================================================================
1642: -- Bug 2899151 : This procedure will fail gl_bc_packets and gms_bc_packets
1643: -- This procedure will be used when all records in gl_bc_packets (corresponding to sponsored)
1644: -- and not existing in gms_bc_packets.
1645: --============================================================================================
1646:
1647:
1648: -- =====================================================================================================
1662: l_count NUMBER;
1663:
1664: CURSOR C_count_rejected_rec IS
1665: SELECT count(*)
1666: FROM gms_bc_packets
1667: WHERE packet_id = p_packet_id
1668: AND status_code in ( 'I' ,'P')
1669: AND substr(result_code,1,1) = 'F' ;
1670:
1714: gms_error_pkg.gms_debug ('UPDATE_BC_PKT_BRC_STATUS'||':'|| 'Updating gms packets to Full mode failure','C');
1715: END IF;
1716:
1717: -- If failure, update result/status code
1718: Update gms_bc_packets
1719: set status_code = decode(p_mode,'C','F','R'),
1720: result_code =decode(result_code,null,'F65',
1721: decode(substr(result_code,1,1),'P','F65',result_code)),
1722: fc_error_message = decode(fc_error_message,NULL,
1731:
1732: END UPDATE_BC_PKT_BRC_STATUS;
1733:
1734: -- =====================================================================================================
1735: -- R12 Funds Managment Uptake : New autonomous procedure to insert records into gms_bc_packets and also
1736: -- updates failed result and status codes on gms packets in case of any failures during loding.
1737: -- This procedure will be fired from main session procedure copy_gl_pkt_to_gms_pkt which in turn is
1738: -- called from GL main budgetory control API.
1739: -- Input parameters : PLSQL tables storing data associated with gl pkts and AP/PO/REQ tables as the
1810:
1811: IF l_rec_count > 0 Then
1812:
1813: IF g_debug = 'Y' THEN
1814: gms_error_pkg.gms_debug ('Load_gms_pkts'||':'|| 'Starting loop to insert '||l_rec_count||'into gms_bc_packets','C');
1815: END IF;
1816:
1817: FORALL i IN 1 .. l_rec_count
1818: INSERT INTO gms_bc_packets
1814: gms_error_pkg.gms_debug ('Load_gms_pkts'||':'|| 'Starting loop to insert '||l_rec_count||'into gms_bc_packets','C');
1815: END IF;
1816:
1817: FORALL i IN 1 .. l_rec_count
1818: INSERT INTO gms_bc_packets
1819: (packet_id,
1820: set_of_books_id,
1821: je_source_name,
1822: je_category_name,
1876: ,p_doc_header_id_tab(i)
1877: ,p_doc_dist_id_tab(i)
1878: ,'N' -- For GMSIP 'N' should not cause an issue
1879: ,NULL
1880: ,gms_bc_packets_s.NEXTVAL
1881: ,p_vendor_id_tab(i)
1882: ,p_exp_category_tab(i)
1883: ,p_revenue_category_tab(i)
1884: ,l_request_id
1893: FOR i IN 1 .. l_rec_count LOOP
1894:
1895: IF SUBSTR(p_result_code_tab(i),1,1) = 'F' THEN
1896:
1897: UPDATE gms_bc_packets
1898: SET result_code = 'F65',
1899: fc_error_message = decode(fc_error_message,NULL,'Load_gms_pkts:Full mode failure',fc_error_message)
1900: WHERE packet_id = p_packet_id
1901: and SUBSTR(result_code,1,1) <> 'F' ;
2094: p_entered_dr => l_entered_dr,
2095: p_entered_cr => l_entered_cr);
2096:
2097: /* Bug 5369296 : If the AP distribution is a reversing distribution (i.e parent_reversal_id is not null) ,
2098: then the burdenable raw cost on the amount/quantity variance records in gms_bc_packets is stamped as 0.
2099: This is because after cancelling an invoice matched to a PO with quantity/amount variance the
2100: burdenable raw cost for the reversing distribution is populated on the basis of that populated in
2101: gms_award_distributions for the original distribution . The burdenable raw cost populated in
2102: gms_award_distributions for the original distribution includes the burdenable raw cost for both the normal
2100: burdenable raw cost for the reversing distribution is populated on the basis of that populated in
2101: gms_award_distributions for the original distribution . The burdenable raw cost populated in
2102: gms_award_distributions for the original distribution includes the burdenable raw cost for both the normal
2103: distribution amount and the quantity/amount variance amount. So during invoice cancel we should not
2104: create the burden for the amount/quantity variance record in gms_bc_packets i.e the burdenable raw cost on the
2105: quantity/amount variance record in gms_bc_packets for the reversing distribution should be zero. */
2106:
2107: IF g_parent_reversal_id_tab(p_copy_from_index) is NOT NULL then
2108: g_burdenable_raw_cost_tab(l_new_rec_index):= 0;
2101: gms_award_distributions for the original distribution . The burdenable raw cost populated in
2102: gms_award_distributions for the original distribution includes the burdenable raw cost for both the normal
2103: distribution amount and the quantity/amount variance amount. So during invoice cancel we should not
2104: create the burden for the amount/quantity variance record in gms_bc_packets i.e the burdenable raw cost on the
2105: quantity/amount variance record in gms_bc_packets for the reversing distribution should be zero. */
2106:
2107: IF g_parent_reversal_id_tab(p_copy_from_index) is NOT NULL then
2108: g_burdenable_raw_cost_tab(l_new_rec_index):= 0;
2109: END IF;
2173: -- to AP/PO/REQ tables uncommited gets fired in this main session procedure and insert/update code
2174: -- gets fired in new autonomous procedures.
2175: --
2176: -- This Function fetches required data from PO/AP/REQ and stores in PLSQL tables.Later fires autonomous
2177: -- procedure to insert into gms_bc_packets and update result codes.This is fired from main GL budgetory
2178: -- control API.
2179: -- for EXP and AP -Interface, insert is through gms_pa_costing_pkg
2180: -- for ENC insert into gms_bc_packets takes place through GMS_FC_SYS Package
2181: -- for Budget Submit/Baseline insert into gms_bc_packets takes place through GMS_BUDGET_BALANCE Package
2176: -- This Function fetches required data from PO/AP/REQ and stores in PLSQL tables.Later fires autonomous
2177: -- procedure to insert into gms_bc_packets and update result codes.This is fired from main GL budgetory
2178: -- control API.
2179: -- for EXP and AP -Interface, insert is through gms_pa_costing_pkg
2180: -- for ENC insert into gms_bc_packets takes place through GMS_FC_SYS Package
2181: -- for Budget Submit/Baseline insert into gms_bc_packets takes place through GMS_BUDGET_BALANCE Package
2182: -- =====================================================================================================
2183:
2184: PROCEDURE copy_gl_pkt_to_gms_pkt (p_application_id IN NUMBER,
2177: -- procedure to insert into gms_bc_packets and update result codes.This is fired from main GL budgetory
2178: -- control API.
2179: -- for EXP and AP -Interface, insert is through gms_pa_costing_pkg
2180: -- for ENC insert into gms_bc_packets takes place through GMS_FC_SYS Package
2181: -- for Budget Submit/Baseline insert into gms_bc_packets takes place through GMS_BUDGET_BALANCE Package
2182: -- =====================================================================================================
2183:
2184: PROCEDURE copy_gl_pkt_to_gms_pkt (p_application_id IN NUMBER,
2185: p_mode IN VARCHAR2 DEFAULT 'C',
2192: -- ----------------------------------------------------------------------------------
2193: l_dist_award_id NUMBER;
2194: l_award_dist_option VARCHAR2 (1);
2195: x_adl_rec gms_award_distributions%ROWTYPE;
2196: l_packet_id gms_bc_packets.packet_id%TYPE;
2197:
2198:
2199: -- IP records are fetched by c_req_po_pkt_rec cursor as IP records will
2200: -- also be stored in po_bc_distributions
2535: l_stdinvoice_exists VARCHAR2(1);
2536: l_counter NUMBER;
2537: l_debug_start_counter NUMBER;
2538: l_po_award_id po_distributions_all.award_id%TYPE;
2539: l_po_result_code gms_bc_packets.result_code%TYPE;
2540: l_po_vendor_id po_headers_all.vendor_id%TYPE;
2541: l_po_ind_com_set_id gms_award_distributions.ind_compiled_set_id%TYPE;
2542:
2543: -- Procedure to intialize PLSQL type variables
2674: -- Initializing OUT variables
2675: x_return_code := 'P';
2676: l_award_dist_option := 'N';
2677:
2678: SELECT gms_bc_packets_s.nextval
2679: INTO l_packet_id
2680: FROM dual;
2681:
2682: IF g_debug = 'Y' THEN
3206: l_mode);
3207: END IF;
3208:
3209: -- If code reaches this point means that data has been correctly transferred from
3210: -- gl_bc_packets to gms_bc_packets ..
3211:
3212: <
3213: NULL;
3214:
3241: BEGIN
3242: g_error_procedure_name := 'misc_gms_idc';
3243: SELECT document_type
3244: INTO doc_type
3245: FROM gms_bc_packets
3246: WHERE packet_id = x_packet_id
3247: AND nvl(burden_adjustment_flag,'N') = 'N'
3248: AND ROWNUM = 1;
3249:
3266: -- no need to get it for 'EXP'
3267: -- Fix for bug : 2927485 , Removed 'ENC'
3268: IF doc_type IN ('AP', 'PO', 'REQ', 'FAB' ) THEN
3269: -- populating compiled set id where ever it is null
3270: UPDATE gms_bc_packets gbc
3271: SET ind_compiled_set_id = gms_cost_plus_extn.get_award_cmt_compiled_set_id (
3272: gbc.task_id,
3273: gbc.expenditure_item_date,
3274: gbc.expenditure_type, --Bug 3003584
3304:
3305: END IF;
3306:
3307:
3308: INSERT INTO gms_bc_packets
3309: (packet_id,
3310: project_id,
3311: award_id,
3312: task_id,
3360: burden_adj_bc_packet_id,
3361: source_event_id,
3362: session_id,
3363: serial_id)
3364: SELECT /*+ index(gbc GMS_BC_PACKETS_N1) */ gbc.packet_id, /* Added the index hint for performance - Bug 5656276 */
3365: gbc.project_id,
3366: gbc.award_id,
3367: gbc.task_id,
3368: icc.expenditure_type,
3407: gbc.time_phased_type_code,
3408: gbc.categorization_code,
3409: gbc.request_id,
3410: gbc.gl_bc_packets_rowid,
3411: gms_bc_packets_s.NEXTVAL,
3412: decode(gbc.burden_adjustment_flag,'Y',gbc.parent_bc_packet_id,gbc.bc_packet_id),
3413: -- In case of burden adjustment flag, use parent_bc_packet_id on raw adjsutment line
3414: gbc.person_id,
3415: gbc.job_id,
3429: pa_cost_base_cost_codes cbcc, -- Bug 5656276
3430: pa_cost_base_exp_types cbet,
3431: /*pa_ind_compiled_sets ics, Bug 5656276 */
3432: pa_compiled_multipliers cm,
3433: gms_bc_packets gbc
3434: WHERE /*irsr.cost_plus_structure = cbet.cost_plus_structure Bug 5656276 */
3435: et.expenditure_type = icc.expenditure_type -- 2092791 ( RLMI Change)
3436: AND icc.ind_cost_code = cm.ind_cost_code
3437: AND cbet.cost_base = cm.cost_base
3459: RETURN TRUE;
3460: END misc_gms_idc;
3461:
3462: --===============================================================================================
3463: /* This Function updates following setup columns of gms_bc_packets
3464: budget_version_id
3465: amount_type
3466: boundary_code
3467: time_phased_type_code
3469: resource_list_id
3470: effect_on_funds_code
3471: Note : Budget Version Id is updated only if it is null , in case of
3472: Award budget submit/Baseline Process. Budget_version_id is inserted during
3473: insertion of records in gms_bc_packets. So if budget_version_id is
3474: alreay present this procedure will not update budget_version_id.
3475:
3476: The earlier logic of calculating budget_version_id in case of
3477: mode ('S'/'B') is removed, as budget_version_id logic is already
3474: alreay present this procedure will not update budget_version_id.
3475:
3476: The earlier logic of calculating budget_version_id in case of
3477: mode ('S'/'B') is removed, as budget_version_id logic is already
3478: present while inserting records in gms_bc_packets.
3479: */
3480: --===============================================================================================
3481:
3482: FUNCTION initialize_setup (x_packet_id IN NUMBER,
3481:
3482: FUNCTION initialize_setup (x_packet_id IN NUMBER,
3483: p_mode IN VARCHAR2)
3484: RETURN BOOLEAN IS
3485: x_budget_version_id gms_bc_packets.budget_version_id%TYPE;
3486: x_amount_type gms_bc_packets.amount_type%TYPE;
3487: x_boundary_code gms_bc_packets.boundary_code%TYPE;
3488: x_time_phased_type_code gms_bc_packets.time_phased_type_code%TYPE;
3489: x_categorization_code gms_bc_packets.categorization_code%TYPE;
3482: FUNCTION initialize_setup (x_packet_id IN NUMBER,
3483: p_mode IN VARCHAR2)
3484: RETURN BOOLEAN IS
3485: x_budget_version_id gms_bc_packets.budget_version_id%TYPE;
3486: x_amount_type gms_bc_packets.amount_type%TYPE;
3487: x_boundary_code gms_bc_packets.boundary_code%TYPE;
3488: x_time_phased_type_code gms_bc_packets.time_phased_type_code%TYPE;
3489: x_categorization_code gms_bc_packets.categorization_code%TYPE;
3490: x_project_id gms_bc_packets.project_id%TYPE;
3483: p_mode IN VARCHAR2)
3484: RETURN BOOLEAN IS
3485: x_budget_version_id gms_bc_packets.budget_version_id%TYPE;
3486: x_amount_type gms_bc_packets.amount_type%TYPE;
3487: x_boundary_code gms_bc_packets.boundary_code%TYPE;
3488: x_time_phased_type_code gms_bc_packets.time_phased_type_code%TYPE;
3489: x_categorization_code gms_bc_packets.categorization_code%TYPE;
3490: x_project_id gms_bc_packets.project_id%TYPE;
3491: x_award_id gms_bc_packets.award_id%TYPE;
3484: RETURN BOOLEAN IS
3485: x_budget_version_id gms_bc_packets.budget_version_id%TYPE;
3486: x_amount_type gms_bc_packets.amount_type%TYPE;
3487: x_boundary_code gms_bc_packets.boundary_code%TYPE;
3488: x_time_phased_type_code gms_bc_packets.time_phased_type_code%TYPE;
3489: x_categorization_code gms_bc_packets.categorization_code%TYPE;
3490: x_project_id gms_bc_packets.project_id%TYPE;
3491: x_award_id gms_bc_packets.award_id%TYPE;
3492: x_dist_award_id gms_bc_packets.award_id%TYPE;
3485: x_budget_version_id gms_bc_packets.budget_version_id%TYPE;
3486: x_amount_type gms_bc_packets.amount_type%TYPE;
3487: x_boundary_code gms_bc_packets.boundary_code%TYPE;
3488: x_time_phased_type_code gms_bc_packets.time_phased_type_code%TYPE;
3489: x_categorization_code gms_bc_packets.categorization_code%TYPE;
3490: x_project_id gms_bc_packets.project_id%TYPE;
3491: x_award_id gms_bc_packets.award_id%TYPE;
3492: x_dist_award_id gms_bc_packets.award_id%TYPE;
3493: x_resource_list_id gms_bc_packets.resource_list_id%TYPE;
3486: x_amount_type gms_bc_packets.amount_type%TYPE;
3487: x_boundary_code gms_bc_packets.boundary_code%TYPE;
3488: x_time_phased_type_code gms_bc_packets.time_phased_type_code%TYPE;
3489: x_categorization_code gms_bc_packets.categorization_code%TYPE;
3490: x_project_id gms_bc_packets.project_id%TYPE;
3491: x_award_id gms_bc_packets.award_id%TYPE;
3492: x_dist_award_id gms_bc_packets.award_id%TYPE;
3493: x_resource_list_id gms_bc_packets.resource_list_id%TYPE;
3494: x_award_distribution_option VARCHAR2 (10);
3487: x_boundary_code gms_bc_packets.boundary_code%TYPE;
3488: x_time_phased_type_code gms_bc_packets.time_phased_type_code%TYPE;
3489: x_categorization_code gms_bc_packets.categorization_code%TYPE;
3490: x_project_id gms_bc_packets.project_id%TYPE;
3491: x_award_id gms_bc_packets.award_id%TYPE;
3492: x_dist_award_id gms_bc_packets.award_id%TYPE;
3493: x_resource_list_id gms_bc_packets.resource_list_id%TYPE;
3494: x_award_distribution_option VARCHAR2 (10);
3495: CURSOR cur_init_setup IS
3488: x_time_phased_type_code gms_bc_packets.time_phased_type_code%TYPE;
3489: x_categorization_code gms_bc_packets.categorization_code%TYPE;
3490: x_project_id gms_bc_packets.project_id%TYPE;
3491: x_award_id gms_bc_packets.award_id%TYPE;
3492: x_dist_award_id gms_bc_packets.award_id%TYPE;
3493: x_resource_list_id gms_bc_packets.resource_list_id%TYPE;
3494: x_award_distribution_option VARCHAR2 (10);
3495: CURSOR cur_init_setup IS
3496: SELECT DISTINCT project_id,
3489: x_categorization_code gms_bc_packets.categorization_code%TYPE;
3490: x_project_id gms_bc_packets.project_id%TYPE;
3491: x_award_id gms_bc_packets.award_id%TYPE;
3492: x_dist_award_id gms_bc_packets.award_id%TYPE;
3493: x_resource_list_id gms_bc_packets.resource_list_id%TYPE;
3494: x_award_distribution_option VARCHAR2 (10);
3495: CURSOR cur_init_setup IS
3496: SELECT DISTINCT project_id,
3497: award_id,
3495: CURSOR cur_init_setup IS
3496: SELECT DISTINCT project_id,
3497: award_id,
3498: budget_version_id
3499: FROM gms_bc_packets
3500: WHERE packet_id = x_packet_id
3501: AND status_code in ('P','A') --Bug 2143160
3502: ;
3503: BEGIN
3539: DECODE (x_budget_version_id, NULL, pb.budget_version_id, x_budget_version_id)
3540: AND pb.current_flag = DECODE (x_budget_version_id, NULL, 'Y', pb.current_flag);
3541: -- AND pb.budget_status_code = 'B'; -- (This code is commented because in 11I Funds Check is done in
3542: -- Budget Submit mode also )
3543: UPDATE gms_bc_packets
3544: SET budget_version_id = x_budget_version_id,
3545: amount_type = x_amount_type,
3546: boundary_code = x_boundary_code,
3547: time_phased_type_code = x_time_phased_type_code,
3556: AND status_code in ('P','A') --Bug 2143160
3557: ;
3558: EXCEPTION
3559: WHEN NO_DATA_FOUND THEN
3560: UPDATE gms_bc_packets
3561: SET result_code = 'F12',
3562: status_code = decode(p_mode,'S','E','C','F','R')
3563: WHERE packet_id = x_packet_id
3564: AND project_id = x_project_id
3572: IF g_debug = 'Y' THEN
3573: gms_error_pkg.gms_debug ('Initilize_Setup - End ', 'C');
3574: END IF;
3575: IF x_award_id = NVL (x_dist_award_id, -1111) THEN
3576: UPDATE gms_bc_packets
3577: SET status_code = decode(p_mode,'S','E','C','F','R'),
3578: result_code = 'F21',
3579: res_result_code = 'F21',
3580: res_grp_result_code = 'F21',
3612: x_packet_id IN NUMBER,
3613: x_mode IN VARCHAR2,
3614: x_err_code OUT NOCOPY NUMBER,
3615: x_err_buff OUT NOCOPY VARCHAR2) IS
3616: TYPE t_doctype IS TABLE OF gms_bc_packets.document_type%TYPE;
3617:
3618: TYPE t_exptype IS TABLE OF gms_bc_packets.expenditure_type%TYPE;
3619:
3620: TYPE t_orgid IS TABLE OF gms_bc_packets.expenditure_organization_id%TYPE;
3614: x_err_code OUT NOCOPY NUMBER,
3615: x_err_buff OUT NOCOPY VARCHAR2) IS
3616: TYPE t_doctype IS TABLE OF gms_bc_packets.document_type%TYPE;
3617:
3618: TYPE t_exptype IS TABLE OF gms_bc_packets.expenditure_type%TYPE;
3619:
3620: TYPE t_orgid IS TABLE OF gms_bc_packets.expenditure_organization_id%TYPE;
3621:
3622: TYPE t_personid IS TABLE OF gms_bc_packets.person_id%TYPE;
3616: TYPE t_doctype IS TABLE OF gms_bc_packets.document_type%TYPE;
3617:
3618: TYPE t_exptype IS TABLE OF gms_bc_packets.expenditure_type%TYPE;
3619:
3620: TYPE t_orgid IS TABLE OF gms_bc_packets.expenditure_organization_id%TYPE;
3621:
3622: TYPE t_personid IS TABLE OF gms_bc_packets.person_id%TYPE;
3623:
3624: TYPE t_jobid IS TABLE OF gms_bc_packets.job_id%TYPE;
3618: TYPE t_exptype IS TABLE OF gms_bc_packets.expenditure_type%TYPE;
3619:
3620: TYPE t_orgid IS TABLE OF gms_bc_packets.expenditure_organization_id%TYPE;
3621:
3622: TYPE t_personid IS TABLE OF gms_bc_packets.person_id%TYPE;
3623:
3624: TYPE t_jobid IS TABLE OF gms_bc_packets.job_id%TYPE;
3625:
3626: TYPE t_vendorid IS TABLE OF gms_bc_packets.vendor_id%TYPE;
3620: TYPE t_orgid IS TABLE OF gms_bc_packets.expenditure_organization_id%TYPE;
3621:
3622: TYPE t_personid IS TABLE OF gms_bc_packets.person_id%TYPE;
3623:
3624: TYPE t_jobid IS TABLE OF gms_bc_packets.job_id%TYPE;
3625:
3626: TYPE t_vendorid IS TABLE OF gms_bc_packets.vendor_id%TYPE;
3627:
3628: TYPE t_expcat IS TABLE OF gms_bc_packets.expenditure_category%TYPE;
3622: TYPE t_personid IS TABLE OF gms_bc_packets.person_id%TYPE;
3623:
3624: TYPE t_jobid IS TABLE OF gms_bc_packets.job_id%TYPE;
3625:
3626: TYPE t_vendorid IS TABLE OF gms_bc_packets.vendor_id%TYPE;
3627:
3628: TYPE t_expcat IS TABLE OF gms_bc_packets.expenditure_category%TYPE;
3629:
3630: TYPE t_revcat IS TABLE OF gms_bc_packets.revenue_category%TYPE;
3624: TYPE t_jobid IS TABLE OF gms_bc_packets.job_id%TYPE;
3625:
3626: TYPE t_vendorid IS TABLE OF gms_bc_packets.vendor_id%TYPE;
3627:
3628: TYPE t_expcat IS TABLE OF gms_bc_packets.expenditure_category%TYPE;
3629:
3630: TYPE t_revcat IS TABLE OF gms_bc_packets.revenue_category%TYPE;
3631:
3632: TYPE t_catcode IS TABLE OF gms_bc_packets.categorization_code%TYPE;
3626: TYPE t_vendorid IS TABLE OF gms_bc_packets.vendor_id%TYPE;
3627:
3628: TYPE t_expcat IS TABLE OF gms_bc_packets.expenditure_category%TYPE;
3629:
3630: TYPE t_revcat IS TABLE OF gms_bc_packets.revenue_category%TYPE;
3631:
3632: TYPE t_catcode IS TABLE OF gms_bc_packets.categorization_code%TYPE;
3633:
3634: TYPE t_reslist IS TABLE OF gms_bc_packets.resource_list_id%TYPE;
3628: TYPE t_expcat IS TABLE OF gms_bc_packets.expenditure_category%TYPE;
3629:
3630: TYPE t_revcat IS TABLE OF gms_bc_packets.revenue_category%TYPE;
3631:
3632: TYPE t_catcode IS TABLE OF gms_bc_packets.categorization_code%TYPE;
3633:
3634: TYPE t_reslist IS TABLE OF gms_bc_packets.resource_list_id%TYPE;
3635:
3636: TYPE t_rowid IS TABLE OF VARCHAR2 (50);
3630: TYPE t_revcat IS TABLE OF gms_bc_packets.revenue_category%TYPE;
3631:
3632: TYPE t_catcode IS TABLE OF gms_bc_packets.categorization_code%TYPE;
3633:
3634: TYPE t_reslist IS TABLE OF gms_bc_packets.resource_list_id%TYPE;
3635:
3636: TYPE t_rowid IS TABLE OF VARCHAR2 (50);
3637:
3638: TYPE t_rlmi IS TABLE OF gms_bc_packets.resource_list_member_id%TYPE;
3634: TYPE t_reslist IS TABLE OF gms_bc_packets.resource_list_id%TYPE;
3635:
3636: TYPE t_rowid IS TABLE OF VARCHAR2 (50);
3637:
3638: TYPE t_rlmi IS TABLE OF gms_bc_packets.resource_list_member_id%TYPE;
3639:
3640: -- TYPE t_upg_err IS TABLE OF gms_award_distributions.upg_error%TYPE; -- Bug 2178694
3641:
3642: TYPE t_fc_err IS TABLE OF gms_bc_packets.fc_error_message%TYPE;
3638: TYPE t_rlmi IS TABLE OF gms_bc_packets.resource_list_member_id%TYPE;
3639:
3640: -- TYPE t_upg_err IS TABLE OF gms_award_distributions.upg_error%TYPE; -- Bug 2178694
3641:
3642: TYPE t_fc_err IS TABLE OF gms_bc_packets.fc_error_message%TYPE;
3643:
3644: t_doc_type t_doctype;
3645: t_exp_type t_exptype;
3646: t_person_id t_personid;
3711: t_vendor_id,
3712: t_row_id,
3713: t_rlmi_value,
3714: t_fc_error -- Bug 2178694
3715: FROM gms_bc_packets
3716: WHERE packet_id = x_packet_id
3717: AND status_code NOT IN ('F','R') -- Bug 2927485
3718: AND resource_list_member_id is NULL
3719: AND nvl(burden_adjustment_flag ,'N') = 'N' -- 3389292
3790: END LOOP;
3791: --4. Bulk Update
3792: g_error_stage := 'SETUP_RLMI : BULK COLLECT';
3793: FORALL bcpkt_txns IN t_row_id.FIRST .. t_row_id.LAST
3794: UPDATE gms_bc_packets
3795: SET status_code = decode(t_rlmi_value (bcpkt_txns),NULL,decode(x_mode,'S','E','C','F','R'),status_code),
3796: result_code = decode(t_rlmi_value (bcpkt_txns),NULL,'F94',result_code),
3797: resource_list_member_id = t_rlmi_value (bcpkt_txns),
3798: fc_error_message = t_fc_error (bcpkt_txns) -- Bug 2178694
3804:
3805:
3806: ----------------------------------------------------------------------------------------------------------
3807: --Procedure to calulate budgeted task id in packet for a budget version, entry level code and budget entry
3808: --method and update gms_bc_packets for the same set of records having the same combinations.
3809: --A single Update Statment will take care of Updating Budget task id for Following Budget Entry Methods
3810: -- Budget Entry Method
3811: -- P By Project
3812: -- T By Top Task
3818: BEGIN
3819: g_error_procedure_name := 'budget_task_id_update';
3820: -- if the budget entry level in 'L','T','P' -- update directly.
3821: g_error_stage := 'BUD_TASK_UPD :L,P,T';
3822: UPDATE gms_bc_packets bc
3823: SET (bc.bud_task_id, bc.top_task_id) =
3824: (SELECT DECODE (bem.entry_level_code, 'P', 0, 'L', bc.task_id, t.top_task_id),
3825: DECODE (bem.entry_level_code, 'P', 0, t.top_task_id)
3826: FROM pa_budget_entry_methods bem, gms_budget_versions bv,
3847: commit;
3848:
3849: -- if the budget entry level = 'M' and budget at LOWEST TASK
3850: g_error_stage := 'BUD_TASK_UPD :M';
3851: UPDATE gms_bc_packets bc
3852: SET (bc.bud_task_id, bc.top_task_id) =
3853: (SELECT t.task_id,
3854: t.top_task_id
3855: FROM pa_budget_entry_methods bem, gms_budget_versions bv,
3872: -- Added commit for the base bug 3848201
3873: commit;
3874:
3875: -- if the budget entry level = 'M' and budget at TOP TASK
3876: UPDATE gms_bc_packets bc
3877: SET (bc.bud_task_id, bc.top_task_id) =
3878: (SELECT t.task_id,
3879: t.top_task_id
3880: FROM pa_budget_entry_methods bem, gms_budget_versions bv,pa_tasks t
3904:
3905: -- If Bud Task Id is not updated till this point , then update bud_task_id with
3906: -- task_id of expenditure
3907: g_error_stage := 'BUD_TASK_UPD :ELSE';
3908: UPDATE gms_bc_packets bc
3909: SET (bc.bud_task_id, bc.top_task_id) =
3910: (SELECT t.task_id,
3911: t.top_task_id
3912: FROM pa_tasks t
3922: END budget_task_id_update;
3923:
3924: ----------------------------------------------------------------------------------------------------------
3925: -- Procedure to calulate budgeted resource list id in packet for a budget version, entry level code and
3926: -- budget entry method and update gms_bc_packets for the set of records having the same combinations.
3927: ----------------------------------------------------------------------------------------------------------
3928:
3929: PROCEDURE bud_res_list_id_update (
3930: x_packetid IN NUMBER) IS
3933: -- At Resource/Resource Group Level
3934: g_error_stage := 'BUD_RES_UPD :START';
3935:
3936: -- Bug 2605070, Only one stmt is needed to update the parent_resource_id
3937: UPDATE gms_bc_packets gms
3938: SET (parent_resource_id) =
3939: (SELECT pr.parent_member_id
3940: FROM pa_resource_list_members pr
3941: WHERE pr.resource_list_member_id = gms.resource_list_member_id
3948:
3949: ----------------------------------------------------------------------------------------------------------
3950: -- Procedure to update the funds control level code in a packet for a project, award, budget version,
3951: -- budget entry method.
3952: -- update gms_bc_packets for the set of records having the same combinations.
3953: ----------------------------------------------------------------------------------------------------------
3954: PROCEDURE funds_ctrl_level_code (
3955: x_packet_id IN NUMBER) IS
3956: BEGIN
3956: BEGIN
3957: g_error_procedure_name := 'funds_ctrl_level_code';
3958: g_error_stage := 'FUND_CTRL_LEVEL_CODE : A';
3959: -- Award Level
3960: UPDATE gms_bc_packets gms
3961: SET a_funds_control_level_code = (SELECT funds_control_level_code
3962: FROM gms_budgetary_controls gbc
3963: WHERE gbc.project_id = gms.project_id
3964: AND gbc.award_id = gms.award_id
3972: gms_error_pkg.gms_debug('FUNDS_CTRL_LEVEL_CODE - Update for Award Result code Complete ','C');
3973: END IF;
3974: g_error_stage := 'FUND_CTRL_LEVEL_CODE : TT';
3975: -- Top Task Level
3976: UPDATE gms_bc_packets gms
3977: SET tt_funds_control_level_code = (SELECT funds_control_level_code
3978: FROM gms_budgetary_controls gbc
3979: WHERE gbc.project_id = gms.project_id
3980: AND gbc.award_id = gms.award_id
3994: END IF;
3995: g_error_stage := 'FUND_CTRL_LEVEL_CODE : T';
3996:
3997: -- Task Level
3998: UPDATE gms_bc_packets gms
3999: SET t_funds_control_level_code = (SELECT funds_control_level_code
4000: FROM gms_budgetary_controls gbc
4001: WHERE gbc.project_id = gms.project_id
4002: AND gbc.award_id = gms.award_id
4017: --Task level funds control level code should set up only if budget entry method is by task
4018: --For project with resource level budget entry method task_id =0
4019: -- Resource Group Level
4020: g_error_stage := 'FUND_CTRL_LEVEL_CODE : RG';
4021: UPDATE gms_bc_packets gms
4022: SET rg_funds_control_level_code = (SELECT funds_control_level_code
4023: FROM gms_budgetary_controls gbc
4024: WHERE gbc.project_id = gms.project_id
4025: AND gbc.award_id = gms.award_id
4040: END IF;
4041:
4042: -- Resource Level
4043: g_error_stage := 'FUND_CTRL_LEVEL_CODE : R';
4044: UPDATE gms_bc_packets gms
4045: SET r_funds_control_level_code = (SELECT funds_control_level_code
4046: FROM gms_budgetary_controls gbc
4047: WHERE gbc.project_id = gms.project_id
4048: AND gbc.award_id = gms.award_id
4063:
4064: -- If Funds control level code at any level is null
4065: -- The update it to 'None'
4066: g_error_stage := 'FUND_CTRL_LEVEL_CODE : NONE';
4067: UPDATE gms_bc_packets gms
4068: SET r_funds_control_level_code =
4069: DECODE (r_funds_control_level_code, NULL, 'N', r_funds_control_level_code),
4070: rg_funds_control_level_code =
4071: DECODE (rg_funds_control_level_code, NULL, 'N', rg_funds_control_level_code),
4089: ----------------------------------------------------------------------------------------------------------
4090: PROCEDURE setup_start_end_date (
4091: x_packetid IN NUMBER,
4092: x_bc_packet_id IN NUMBER,
4093: x_project_id IN gms_bc_packets.project_id%TYPE,
4094: x_award_id IN gms_bc_packets.award_id%TYPE,
4095: x_budget_version_id IN gms_bc_packets.budget_version_id%TYPE,
4096: x_time_phased_type_code IN pa_budget_entry_methods.time_phased_type_code%TYPE,
4097: x_expenditure_item_date IN DATE,
4090: PROCEDURE setup_start_end_date (
4091: x_packetid IN NUMBER,
4092: x_bc_packet_id IN NUMBER,
4093: x_project_id IN gms_bc_packets.project_id%TYPE,
4094: x_award_id IN gms_bc_packets.award_id%TYPE,
4095: x_budget_version_id IN gms_bc_packets.budget_version_id%TYPE,
4096: x_time_phased_type_code IN pa_budget_entry_methods.time_phased_type_code%TYPE,
4097: x_expenditure_item_date IN DATE,
4098: x_amount_type IN gms_awards.amount_type%TYPE,
4091: x_packetid IN NUMBER,
4092: x_bc_packet_id IN NUMBER,
4093: x_project_id IN gms_bc_packets.project_id%TYPE,
4094: x_award_id IN gms_bc_packets.award_id%TYPE,
4095: x_budget_version_id IN gms_bc_packets.budget_version_id%TYPE,
4096: x_time_phased_type_code IN pa_budget_entry_methods.time_phased_type_code%TYPE,
4097: x_expenditure_item_date IN DATE,
4098: x_amount_type IN gms_awards.amount_type%TYPE,
4099: x_boundary_code IN gms_awards.boundary_code%TYPE,
4096: x_time_phased_type_code IN pa_budget_entry_methods.time_phased_type_code%TYPE,
4097: x_expenditure_item_date IN DATE,
4098: x_amount_type IN gms_awards.amount_type%TYPE,
4099: x_boundary_code IN gms_awards.boundary_code%TYPE,
4100: x_set_of_books_id IN gms_bc_packets.set_of_books_id%TYPE,
4101:
4102: -- x_budgeted_task_id IN gms_bc_packets.bud_task_id%TYPE,
4103: -- x_bud_res_list_member_id IN NUMBER,
4104: x_start_date OUT NOCOPY DATE,
4098: x_amount_type IN gms_awards.amount_type%TYPE,
4099: x_boundary_code IN gms_awards.boundary_code%TYPE,
4100: x_set_of_books_id IN gms_bc_packets.set_of_books_id%TYPE,
4101:
4102: -- x_budgeted_task_id IN gms_bc_packets.bud_task_id%TYPE,
4103: -- x_bud_res_list_member_id IN NUMBER,
4104: x_start_date OUT NOCOPY DATE,
4105: x_end_date OUT NOCOPY DATE) IS
4106:
4185: FROM gms_balances
4186: WHERE budget_version_id = x_budget_version_id;
4187: SELECT MAX (expenditure_item_date)
4188: INTO exp_date
4189: FROM gms_bc_packets
4190: WHERE budget_version_id = x_budget_version_id;
4191:
4192: g_budget_version_id := x_budget_version_id;
4193: g_gb_end_date := gb_end_date;
4634:
4635: PROCEDURE call_start_end_date_update (
4636: x_packetid IN NUMBER ,
4637: p_mode IN VARCHAR2) IS
4638: TYPE t_packetid IS TABLE OF gms_bc_packets.packet_id%TYPE;
4639: TYPE t_bcpktid IS TABLE OF gms_bc_packets.bc_packet_id%TYPE;
4640: TYPE t_projid IS TABLE OF gms_bc_packets.project_id%TYPE;
4641: TYPE t_awardid IS TABLE OF gms_bc_packets.award_id%TYPE;
4642: TYPE t_bvid IS TABLE OF gms_bc_packets.budget_version_id%TYPE;
4635: PROCEDURE call_start_end_date_update (
4636: x_packetid IN NUMBER ,
4637: p_mode IN VARCHAR2) IS
4638: TYPE t_packetid IS TABLE OF gms_bc_packets.packet_id%TYPE;
4639: TYPE t_bcpktid IS TABLE OF gms_bc_packets.bc_packet_id%TYPE;
4640: TYPE t_projid IS TABLE OF gms_bc_packets.project_id%TYPE;
4641: TYPE t_awardid IS TABLE OF gms_bc_packets.award_id%TYPE;
4642: TYPE t_bvid IS TABLE OF gms_bc_packets.budget_version_id%TYPE;
4643: TYPE t_tptypecd IS TABLE OF gms_bc_packets.time_phased_type_code%TYPE;
4636: x_packetid IN NUMBER ,
4637: p_mode IN VARCHAR2) IS
4638: TYPE t_packetid IS TABLE OF gms_bc_packets.packet_id%TYPE;
4639: TYPE t_bcpktid IS TABLE OF gms_bc_packets.bc_packet_id%TYPE;
4640: TYPE t_projid IS TABLE OF gms_bc_packets.project_id%TYPE;
4641: TYPE t_awardid IS TABLE OF gms_bc_packets.award_id%TYPE;
4642: TYPE t_bvid IS TABLE OF gms_bc_packets.budget_version_id%TYPE;
4643: TYPE t_tptypecd IS TABLE OF gms_bc_packets.time_phased_type_code%TYPE;
4644: TYPE t_expdate IS TABLE OF gms_bc_packets.expenditure_item_date%TYPE;
4637: p_mode IN VARCHAR2) IS
4638: TYPE t_packetid IS TABLE OF gms_bc_packets.packet_id%TYPE;
4639: TYPE t_bcpktid IS TABLE OF gms_bc_packets.bc_packet_id%TYPE;
4640: TYPE t_projid IS TABLE OF gms_bc_packets.project_id%TYPE;
4641: TYPE t_awardid IS TABLE OF gms_bc_packets.award_id%TYPE;
4642: TYPE t_bvid IS TABLE OF gms_bc_packets.budget_version_id%TYPE;
4643: TYPE t_tptypecd IS TABLE OF gms_bc_packets.time_phased_type_code%TYPE;
4644: TYPE t_expdate IS TABLE OF gms_bc_packets.expenditure_item_date%TYPE;
4645: TYPE t_amttype IS TABLE OF gms_bc_packets.amount_type%TYPE;
4638: TYPE t_packetid IS TABLE OF gms_bc_packets.packet_id%TYPE;
4639: TYPE t_bcpktid IS TABLE OF gms_bc_packets.bc_packet_id%TYPE;
4640: TYPE t_projid IS TABLE OF gms_bc_packets.project_id%TYPE;
4641: TYPE t_awardid IS TABLE OF gms_bc_packets.award_id%TYPE;
4642: TYPE t_bvid IS TABLE OF gms_bc_packets.budget_version_id%TYPE;
4643: TYPE t_tptypecd IS TABLE OF gms_bc_packets.time_phased_type_code%TYPE;
4644: TYPE t_expdate IS TABLE OF gms_bc_packets.expenditure_item_date%TYPE;
4645: TYPE t_amttype IS TABLE OF gms_bc_packets.amount_type%TYPE;
4646: TYPE t_boudrcd IS TABLE OF gms_bc_packets.boundary_code%TYPE;
4639: TYPE t_bcpktid IS TABLE OF gms_bc_packets.bc_packet_id%TYPE;
4640: TYPE t_projid IS TABLE OF gms_bc_packets.project_id%TYPE;
4641: TYPE t_awardid IS TABLE OF gms_bc_packets.award_id%TYPE;
4642: TYPE t_bvid IS TABLE OF gms_bc_packets.budget_version_id%TYPE;
4643: TYPE t_tptypecd IS TABLE OF gms_bc_packets.time_phased_type_code%TYPE;
4644: TYPE t_expdate IS TABLE OF gms_bc_packets.expenditure_item_date%TYPE;
4645: TYPE t_amttype IS TABLE OF gms_bc_packets.amount_type%TYPE;
4646: TYPE t_boudrcd IS TABLE OF gms_bc_packets.boundary_code%TYPE;
4647: TYPE t_sobid IS TABLE OF gms_bc_packets.set_of_books_id%TYPE;
4640: TYPE t_projid IS TABLE OF gms_bc_packets.project_id%TYPE;
4641: TYPE t_awardid IS TABLE OF gms_bc_packets.award_id%TYPE;
4642: TYPE t_bvid IS TABLE OF gms_bc_packets.budget_version_id%TYPE;
4643: TYPE t_tptypecd IS TABLE OF gms_bc_packets.time_phased_type_code%TYPE;
4644: TYPE t_expdate IS TABLE OF gms_bc_packets.expenditure_item_date%TYPE;
4645: TYPE t_amttype IS TABLE OF gms_bc_packets.amount_type%TYPE;
4646: TYPE t_boudrcd IS TABLE OF gms_bc_packets.boundary_code%TYPE;
4647: TYPE t_sobid IS TABLE OF gms_bc_packets.set_of_books_id%TYPE;
4648: TYPE t_startdt IS TABLE OF DATE;
4641: TYPE t_awardid IS TABLE OF gms_bc_packets.award_id%TYPE;
4642: TYPE t_bvid IS TABLE OF gms_bc_packets.budget_version_id%TYPE;
4643: TYPE t_tptypecd IS TABLE OF gms_bc_packets.time_phased_type_code%TYPE;
4644: TYPE t_expdate IS TABLE OF gms_bc_packets.expenditure_item_date%TYPE;
4645: TYPE t_amttype IS TABLE OF gms_bc_packets.amount_type%TYPE;
4646: TYPE t_boudrcd IS TABLE OF gms_bc_packets.boundary_code%TYPE;
4647: TYPE t_sobid IS TABLE OF gms_bc_packets.set_of_books_id%TYPE;
4648: TYPE t_startdt IS TABLE OF DATE;
4649: TYPE t_enddt IS TABLE OF DATE;
4642: TYPE t_bvid IS TABLE OF gms_bc_packets.budget_version_id%TYPE;
4643: TYPE t_tptypecd IS TABLE OF gms_bc_packets.time_phased_type_code%TYPE;
4644: TYPE t_expdate IS TABLE OF gms_bc_packets.expenditure_item_date%TYPE;
4645: TYPE t_amttype IS TABLE OF gms_bc_packets.amount_type%TYPE;
4646: TYPE t_boudrcd IS TABLE OF gms_bc_packets.boundary_code%TYPE;
4647: TYPE t_sobid IS TABLE OF gms_bc_packets.set_of_books_id%TYPE;
4648: TYPE t_startdt IS TABLE OF DATE;
4649: TYPE t_enddt IS TABLE OF DATE;
4650: TYPE t_errcode IS TABLE OF NUMBER;
4643: TYPE t_tptypecd IS TABLE OF gms_bc_packets.time_phased_type_code%TYPE;
4644: TYPE t_expdate IS TABLE OF gms_bc_packets.expenditure_item_date%TYPE;
4645: TYPE t_amttype IS TABLE OF gms_bc_packets.amount_type%TYPE;
4646: TYPE t_boudrcd IS TABLE OF gms_bc_packets.boundary_code%TYPE;
4647: TYPE t_sobid IS TABLE OF gms_bc_packets.set_of_books_id%TYPE;
4648: TYPE t_startdt IS TABLE OF DATE;
4649: TYPE t_enddt IS TABLE OF DATE;
4650: TYPE t_errcode IS TABLE OF NUMBER;
4651: TYPE t_errcbuff IS TABLE OF VARCHAR2(500);
4691: t_set_of_books_id,
4692: t_bc_packet_id,
4693: t_start_date,
4694: t_end_date
4695: FROM gms_bc_packets
4696: WHERE packet_id = x_packetid
4697: AND status_code = 'P'
4698: AND parent_bc_packet_id IS NULL ;
4699:
4731: -- END IF; -- Bug 2683607 : Commented , moved the END IF statement in the end
4732: -- so that all the FOR ALL statements are included in "IF t_bc_packet_id.COUNT > 0" check
4733: g_error_stage := 'CL_STEND_DATE:FORALL R';
4734: FORALL bcpkt_txns IN t_bc_packet_id.FIRST .. t_bc_packet_id.LAST
4735: UPDATE gms_bc_packets
4736: SET status_code = DECODE (
4737: t_start_date (bcpkt_txns),
4738: NULL, decode(p_mode,'S','E','C','F','R'),
4739: DECODE (t_end_date (bcpkt_txns), NULL, decode(p_mode,'S','E','C','F','R'), status_code)),
4766: -- budget_period_end_date as that of raw line . This will restrict the call of date calculation
4767: -- program once for each raw/burden transaction
4768: g_error_stage := 'CL_STEND_DATE:FORALL B';
4769: FORALL bcpkt_txns IN t_bc_packet_id.FIRST .. t_bc_packet_id.LAST
4770: UPDATE gms_bc_packets
4771: SET status_code = DECODE (
4772: t_start_date (bcpkt_txns),
4773: NULL, decode(p_mode,'S','E','C','F','R'),
4774: DECODE (t_end_date (bcpkt_txns), NULL, decode(p_mode,'S','E','C','F','R'), status_code)),
4925: END IF;
4926: END insert_arrival_order_seq;
4927:
4928: ----------------------------------------------------------------------------------------------------------
4929: -- This Procedure updated the burdened_cost column of gms_bc_packets .
4930: -- For EXP/PO/AP/REQ/ENC the Burdened cost = Raw Cost + Burdne Cost
4931: -- For Re-Costed Expenditures Burdened COst = Sum ( Raw Cost + Burden Cost ) of all the CDL's of that
4932: -- expenditure item id.
4933: ----------------------------------------------------------------------------------------------------------
4937: BEGIN
4938: g_error_procedure_name := 'update_burdened_cost';
4939: g_error_stage := 'UPD_BURDN_COST : START';
4940: -- --------------------------------------
4941: -- UPDATE BURDENED COST ON GMS_BC_PACKETS
4942: -- BURDENED COST = RAW COST + BURDEN COST.
4943: -- --------------------------------------
4944: -- Bug 2092791
4945: UPDATE gms_bc_packets a
4941: -- UPDATE BURDENED COST ON GMS_BC_PACKETS
4942: -- BURDENED COST = RAW COST + BURDEN COST.
4943: -- --------------------------------------
4944: -- Bug 2092791
4945: UPDATE gms_bc_packets a
4946: SET burdened_cost =
4947: (SELECT SUM ( NVL(entered_dr,0) - NVL(entered_cr,0) )
4948: FROM gms_bc_packets b
4949: WHERE b.packet_id + 0 = a.packet_id /* Bug 5689194 */
4944: -- Bug 2092791
4945: UPDATE gms_bc_packets a
4946: SET burdened_cost =
4947: (SELECT SUM ( NVL(entered_dr,0) - NVL(entered_cr,0) )
4948: FROM gms_bc_packets b
4949: WHERE b.packet_id + 0 = a.packet_id /* Bug 5689194 */
4950: AND b.document_type = a.document_type
4951: AND b.document_header_id = a.document_header_id
4952: AND ((b.document_type='EXP')
5008: x_err_count :=0;
5009:
5010: IF x_partial = 'Y' THEN
5011: g_error_stage := 'CHK SETUP FAIL : Y';
5012: UPDATE gms_bc_packets gms
5013: SET gms.status_code = 'T',
5014: gms.result_code = DECODE(budget_version_id,NULL,'F12',
5015: DECODE(resource_list_member_id,NULL,'F13',
5016: -- DECODE(bud_resource_list_member_id,NULL,'F14',
5087: -- Net zero txn.s failure
5088: If g_doc_type in ('EXP','ENC') then
5089: -- Handle net zero txn.s in the same packet
5090: -- A. Fail reversing line if original line has failed
5091: UPDATE gms_bc_packets bp
5092: SET bp.result_code = nvl(bp.result_code,'F65'),
5093: bp.status_code = 'R',
5094: bp.fc_error_message = decode(bp.fc_error_message,NULL,'CHECK_SETUP_FAILURE - net zero txn. - full mode failure',bp.fc_error_message)
5095: WHERE bp.packet_id = x_packetid
5098: AND bp.status_code = 'P'
5099: AND bp.document_header_id <> bp.adjusted_document_header_id
5100: AND bp.document_type in ('EXP','ENC')
5101: AND EXISTS (select 1
5102: from gms_bc_packets bp1
5103: where bp1.packet_id = bp.packet_id
5104: and bp1.document_header_id = bp.adjusted_document_header_id
5105: and SUBSTR (bp1.result_code, 1, 1) = 'F');
5106:
5107: -- Added commit for the base bug 3848201
5108: commit;
5109:
5110: -- B. Fail original line if reversing line has failed
5111: UPDATE gms_bc_packets bp
5112: SET bp.result_code = nvl(bp.result_code,'F65'),
5113: bp.status_code = 'R',
5114: 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)
5115: WHERE bp.packet_id = x_packetid
5118: AND bp.status_code = 'P'
5119: AND bp.document_header_id = bp.adjusted_document_header_id
5120: AND bp.document_type in ('EXP','ENC')
5121: AND EXISTS (select 1
5122: from gms_bc_packets bp1
5123: where bp1.packet_id = bp.packet_id
5124: and bp1.adjusted_document_header_id = bp.document_header_id
5125: and SUBSTR (bp1.result_code, 1, 1) = 'F');
5126:
5136: SELECT 1
5137: INTO x_err_count
5138: FROM dual
5139: WHERE EXISTS (SELECT 1
5140: FROM gms_bc_packets
5141: WHERE packet_id = x_packetid
5142: AND (
5143: budget_version_id IS NULL
5144: OR resource_list_member_id IS NULL
5155: -- Added commit for the base bug 3848201
5156: commit;
5157:
5158:
5159: UPDATE gms_bc_packets gms
5160: SET gms.status_code = 'T',
5161: gms.result_code = DECODE(result_code,NULL,DECODE(budget_version_id,NULL,'F12',
5162: DECODE(resource_list_member_id,NULL,'F13',
5163: -- DECODE(bud_resource_list_member_id,NULL,'F14',
5235:
5236: PROCEDURE update_fc_sequence (
5237: x_packetid IN NUMBER) IS
5238:
5239: TYPE t_fcseq IS TABLE OF gms_bc_packets.funds_check_seq%TYPE;
5240: TYPE t_rowid IS TABLE OF VARCHAR2 (50);
5241: t_row_id t_rowid;
5242: t_fc_seq t_fcseq;
5243: t_count number; -- fix for bug : 2927485
5265: funds_check_seq
5266: BULK COLLECT INTO
5267: t_row_id,
5268: t_fc_seq
5269: FROM gms_bc_packets
5270: WHERE packet_id = x_packetid
5271: AND nvl(result_code,'XX') <> 'P82'
5272: ORDER BY burdened_cost,
5273: document_type,
5291: funds_check_seq
5292: BULK COLLECT INTO
5293: t_row_id,
5294: t_fc_seq
5295: FROM gms_bc_packets
5296: WHERE packet_id = x_packetid
5297: AND nvl(result_code,'XX') = 'P82'
5298: ORDER BY adjusted_document_header_id,
5299: burdened_cost,
5314: END IF;
5315: END LOOP;
5316: g_error_stage := 'UPD FC SEQ: FOR ALL';
5317: FORALL bcpkt_txns IN t_row_id.FIRST .. t_row_id.LAST
5318: UPDATE gms_bc_packets
5319: SET funds_check_seq = t_fc_seq(bcpkt_txns)
5320: WHERE ROWID = t_row_id (bcpkt_txns);
5321: End if;
5322:
5329: /***********************************************************************************************/
5330: /********************************* FUNDS CHECK SETUP *************************************/
5331: /***********************************************************************************************/
5332: -------------------------------------------------------------------------------------------------
5333: /* This Function populates setup columns of GMS_BC_PACKETS Table for all the transactions
5334: This includes populating
5335: burdened_cost
5336: resource_list_member_id
5337: bud_resource_list_member_id
5370: COMMIT;
5371:
5372: -- ------------------------------------------------------------------------
5373: -- Update the budgeted task id
5374: -- in gms bc packets for a project, task, award, budget version
5375: -- ------------------------------------------------------------------------
5376: IF g_debug = 'Y' THEN
5377: gms_error_pkg.gms_debug ('GMS_SETUP - Before Budgeted Task Update -> packet_id'|| x_packetid, 'C');
5378: END IF;
5384: END IF;
5385:
5386: -- --------------------------------------------------------------------------
5387: -- Update the Budgeted resource list member id(rlmi)
5388: -- in gms bc packets for a project, task, award, budget version
5389: -- --------------------------------------------------------------------------
5390: IF g_debug = 'Y' THEN
5391: gms_error_pkg.gms_debug ('GMS_SETUP - Before Budgeted rlmi Update -> packet_id'|| x_packetid, 'C');
5392: END IF;
5398: END IF;
5399:
5400: -- ---------------------------------------------------------------------------
5401: -- Updating the Funds control level code
5402: -- in gms bc packets for a project, task, award, budget version
5403: -- ---------------------------------------------------------------------------
5404: IF g_debug = 'Y' THEN
5405: gms_error_pkg.gms_debug ('GMS_SETUP - Before Funds ctrl code -> packet_id'|| x_packetid, 'C');
5406: END IF;
5442: -- Bug 2176230
5443: -- ********************************************************************************
5444: -- NOTE :- Don't Put Any Commit after this point till gms_fc_process is complete,
5445: -- Reason being for funds checking in C Mode (check funds mode)
5446: -- transactions in gms_bc_packets should not be accounted by any other
5447: -- subsequent packet.
5448: -- ********************************************************************************
5449:
5450: insert_arrival_order_seq (x_packetid, x_mode);
5467: END gms_setup;
5468:
5469: /* ------------------------------------------------------------------------------------------------------
5470: This Procedure is called in case of Budget Submit and Re-Baseline prcoess of Award Budget.
5471: This procedure compares balances from gms_balances table and Records in gms_bc_packets
5472: for that budget_version_id. If any Record fails, control comes out of the loop and goes
5473: to the exception part where all the values of variable are dumped into the o/p file
5474:
5475: Bug 3681963 : Modified the code of procedure budget_fundscheck consider the previous consumed
5486: bud_task_id,
5487: resource_list_member_id, -- Bug 2605070, Replaced bud_resource_list_member_id with this column
5488: budget_period_start_date,
5489: budget_period_end_date
5490: FROM gms_bc_packets
5491: WHERE packet_id = x_packetid
5492: AND status_code = 'P'
5493: AND
5494: r_funds_control_level_code = 'B'
5509: bud_task_id,
5510: parent_resource_id,
5511: budget_period_start_date,
5512: budget_period_end_date
5513: FROM gms_bc_packets
5514: WHERE packet_id = x_packetid
5515: AND status_code = 'P'
5516: AND rg_funds_control_level_code = 'B'
5517: GROUP BY budget_version_id,
5530: budget_version_id,
5531: bud_task_id,
5532: budget_period_start_date,
5533: budget_period_end_date
5534: FROM gms_bc_packets
5535: WHERE packet_id = x_packetid
5536: AND status_code = 'P'
5537: AND t_funds_control_level_code = 'B'
5538: GROUP BY budget_version_id, bud_task_id, budget_period_start_date, budget_period_end_date
5543: budget_version_id,
5544: top_task_id,
5545: budget_period_start_date,
5546: budget_period_end_date
5547: FROM gms_bc_packets
5548: WHERE packet_id = x_packetid
5549: AND status_code = 'P'
5550: AND tt_funds_control_level_code = 'B'
5551: GROUP BY budget_version_id, top_task_id,
5557: SELECT SUM (NVL (entered_dr, 0) - NVL (entered_cr, 0)) a_bc_tot,
5558: budget_version_id,
5559: budget_period_start_date,
5560: budget_period_end_date
5561: FROM gms_bc_packets
5562: WHERE packet_id = x_packetid
5563: AND status_code = 'P'
5564: AND a_funds_control_level_code = 'B'
5565: GROUP BY budget_version_id, budget_period_start_date, budget_period_end_date
5570: x_r_bc_tot NUMBER (22, 5);
5571: x_rg_bc_tot NUMBER (22, 5);
5572: x_t_bc_tot NUMBER (22, 5);
5573: x_tt_bc_tot NUMBER (22, 5);
5574: x_res_list_member_id gms_bc_packets.resource_list_member_id%TYPE; -- Bug 2605070
5575: x_bud_task_id gms_bc_packets.bud_task_id%TYPE;
5576: x_budget_version_id NUMBER (22, 5);
5577: x_budget_period_start_date DATE;
5578: x_budget_period_end_date DATE;
5571: x_rg_bc_tot NUMBER (22, 5);
5572: x_t_bc_tot NUMBER (22, 5);
5573: x_tt_bc_tot NUMBER (22, 5);
5574: x_res_list_member_id gms_bc_packets.resource_list_member_id%TYPE; -- Bug 2605070
5575: x_bud_task_id gms_bc_packets.bud_task_id%TYPE;
5576: x_budget_version_id NUMBER (22, 5);
5577: x_budget_period_start_date DATE;
5578: x_budget_period_end_date DATE;
5579: temp NUMBER;
5583:
5584: l_balance_available NUMBER (22,5):=0;
5585: l_old_start_date DATE;
5586: l_old_end_date DATE;
5587: l_old_res_list_member_id gms_bc_packets.resource_list_member_id%TYPE;
5588: l_old_budget_version_id gms_bc_packets.budget_version_id%TYPE;
5589: l_old_bud_task_id gms_bc_packets.bud_task_id%TYPE;
5590: l_old_top_task_id gms_bc_packets.top_task_id%TYPE;
5591: l_old_parent_resource_id gms_bc_packets.parent_resource_id%TYPE;
5584: l_balance_available NUMBER (22,5):=0;
5585: l_old_start_date DATE;
5586: l_old_end_date DATE;
5587: l_old_res_list_member_id gms_bc_packets.resource_list_member_id%TYPE;
5588: l_old_budget_version_id gms_bc_packets.budget_version_id%TYPE;
5589: l_old_bud_task_id gms_bc_packets.bud_task_id%TYPE;
5590: l_old_top_task_id gms_bc_packets.top_task_id%TYPE;
5591: l_old_parent_resource_id gms_bc_packets.parent_resource_id%TYPE;
5592: l_previous_tot NUMBER (22,5):=0;
5585: l_old_start_date DATE;
5586: l_old_end_date DATE;
5587: l_old_res_list_member_id gms_bc_packets.resource_list_member_id%TYPE;
5588: l_old_budget_version_id gms_bc_packets.budget_version_id%TYPE;
5589: l_old_bud_task_id gms_bc_packets.bud_task_id%TYPE;
5590: l_old_top_task_id gms_bc_packets.top_task_id%TYPE;
5591: l_old_parent_resource_id gms_bc_packets.parent_resource_id%TYPE;
5592: l_previous_tot NUMBER (22,5):=0;
5593:
5586: l_old_end_date DATE;
5587: l_old_res_list_member_id gms_bc_packets.resource_list_member_id%TYPE;
5588: l_old_budget_version_id gms_bc_packets.budget_version_id%TYPE;
5589: l_old_bud_task_id gms_bc_packets.bud_task_id%TYPE;
5590: l_old_top_task_id gms_bc_packets.top_task_id%TYPE;
5591: l_old_parent_resource_id gms_bc_packets.parent_resource_id%TYPE;
5592: l_previous_tot NUMBER (22,5):=0;
5593:
5594:
5587: l_old_res_list_member_id gms_bc_packets.resource_list_member_id%TYPE;
5588: l_old_budget_version_id gms_bc_packets.budget_version_id%TYPE;
5589: l_old_bud_task_id gms_bc_packets.bud_task_id%TYPE;
5590: l_old_top_task_id gms_bc_packets.top_task_id%TYPE;
5591: l_old_parent_resource_id gms_bc_packets.parent_resource_id%TYPE;
5592: l_previous_tot NUMBER (22,5):=0;
5593:
5594:
5595: BEGIN
5851: CLOSE gms_bc_tot_a;
5852: IF g_debug = 'Y' THEN
5853: gms_error_pkg.gms_debug ('BUDGET_FUNDSCHECK BEFORE P50 UPDATE '|| x_packetid, 'C');
5854: END IF;
5855: UPDATE gms_bc_packets
5856: SET result_code = 'P50',
5857: award_result_code = 'P50',
5858: res_result_code = 'P50',
5859: res_grp_result_code = 'P50',
5883: x_token_val5=> x_budget_period_start_date,
5884: x_exec_type=> 'C',
5885: x_err_code=> x_err_code,
5886: x_err_buff=> x_err_buff);
5887: UPDATE gms_bc_packets
5888: SET result_code = 'F25',
5889: award_result_code = 'F25',
5890: res_result_code = 'F25',
5891: res_grp_result_code = 'F25',
5912: x_token_val5=> x_budget_period_start_date,
5913: x_exec_type=> 'C',
5914: x_err_code=> x_err_code,
5915: x_err_buff=> x_err_buff);
5916: UPDATE gms_bc_packets
5917: SET result_code = 'F26',
5918: award_result_code = 'F26',
5919: res_result_code = 'F26',
5920: res_grp_result_code = 'F26',
5939: x_token_val5=> x_budget_period_start_date,
5940: x_exec_type=> 'C',
5941: x_err_code=> x_err_code,
5942: x_err_buff=> x_err_buff);
5943: UPDATE gms_bc_packets
5944: SET result_code = 'F27',
5945: award_result_code = 'F27',
5946: res_result_code = 'F27',
5947: res_grp_result_code = 'F27',
5966: x_token_val5=> x_budget_period_start_date,
5967: x_exec_type=> 'C',
5968: x_err_code=> x_err_code,
5969: x_err_buff=> x_err_buff);
5970: UPDATE gms_bc_packets
5971: SET result_code = 'F28',
5972: award_result_code = 'F28',
5973: res_result_code = 'F28',
5974: res_grp_result_code = 'F28',
5991: x_token_val5=> x_budget_period_start_date,
5992: x_exec_type=> 'C',
5993: x_err_code=> x_err_code,
5994: x_err_buff=> x_err_buff);
5995: UPDATE gms_bc_packets
5996: SET result_code = 'F29',
5997: award_result_code = 'F29',
5998: res_result_code = 'F29',
5999: res_grp_result_code = 'F29',
6021: */
6022: -- ===========================================================================
6023:
6024: FUNCTION gms_fc_process (
6025: x_packetid IN gms_bc_packets.packet_id%TYPE,
6026: x_arrival_seq1 IN gms_bc_packet_arrival_order.packet_id%TYPE,
6027: x_mode IN Char -- Bug 2176230
6028: )
6029: RETURN BOOLEAN IS
6046: -- ==============================================================================
6047: -- ********************* RESOURCE LEVEL SUMMARY UPDATE ************************
6048: -- ==============================================================================
6049: -- Bug 2092791
6050: -- Following Insert statement inserts records in gms_bc_packets_summary.
6051: -- Records in this table will later be used to summarize amount at
6052: -- resource,task and Award Level for previous and currne packet.
6053: IF g_debug = 'Y' THEN
6054: gms_error_pkg.gms_debug ('RESOURCE LEVEL - SUMMARY INSERT ', 'C');
6054: gms_error_pkg.gms_debug ('RESOURCE LEVEL - SUMMARY INSERT ', 'C');
6055: END IF;
6056: x_date := sysdate;
6057: g_error_stage := 'FC PR : INSRT SUMM';
6058: INSERT INTO gms_bc_packets_bvid
6059: (packet_id,
6060: budget_version_id,
6061: creation_date)
6062: SELECT DISTINCT x_packetid,
6061: creation_date)
6062: SELECT DISTINCT x_packetid,
6063: budget_version_id,
6064: x_date
6065: FROM gms_bc_packets
6066: WHERE packet_id = x_packetid
6067: AND status_code = 'P' -- This is to ignore Transactions which failed during setup.
6068: ;
6069:
6066: WHERE packet_id = x_packetid
6067: AND status_code = 'P' -- This is to ignore Transactions which failed during setup.
6068: ;
6069:
6070: -- Bug 4053891 Do not change code flow ..as lock_budget_versions uses gms_bc_packets_bvid
6071: -- Costing and Funds check is incompatible to Sweeper. 'EXP' has been added to the list for
6072: -- interface (VI->EXP)
6073:
6074: If g_doc_type in ('REQ','PO','AP','FAB','EXP') then
6075: LOCK_BUDGET_VERSIONS(x_packetid);
6076: End If;
6077:
6078: -- Bug 2605070, Replaced bud_resource_list_member_id with resource_list_member_id
6079: INSERT INTO gms_bc_packets_summary
6080: (packet_id,
6081: creation_date,
6082: budget_version_id,
6083: top_task_id,
6103: nvl(sum(decode(bcpkt.status_code || bcpkt.actual_flag, 'AA',nvl(entered_dr,0) - nvl(entered_cr,0),0)),0),
6104: nvl(sum(decode(bcpkt.status_code || bcpkt.actual_flag, 'PA',nvl(entered_dr,0) - nvl(entered_cr,0),0)),0),
6105: nvl(sum(decode(bcpkt.status_code || bcpkt.actual_flag, 'AE',nvl(entered_dr,0) - nvl(entered_cr,0),0)),0),
6106: nvl(sum(decode(bcpkt.status_code || bcpkt.actual_flag, 'PE',nvl(entered_dr,0) - nvl(entered_cr,0),0)),0)
6107: FROM gms_bc_packets bcpkt,
6108: gms_bc_packet_arrival_order ao,
6109: gms_bc_packets_bvid a
6110: WHERE bcpkt.status_code IN ('A', 'P')
6111: AND bcpkt.budget_version_id = a.budget_version_id
6105: nvl(sum(decode(bcpkt.status_code || bcpkt.actual_flag, 'AE',nvl(entered_dr,0) - nvl(entered_cr,0),0)),0),
6106: nvl(sum(decode(bcpkt.status_code || bcpkt.actual_flag, 'PE',nvl(entered_dr,0) - nvl(entered_cr,0),0)),0)
6107: FROM gms_bc_packets bcpkt,
6108: gms_bc_packet_arrival_order ao,
6109: gms_bc_packets_bvid a
6110: WHERE bcpkt.status_code IN ('A', 'P')
6111: AND bcpkt.budget_version_id = a.budget_version_id
6112: AND bcpkt.packet_id = ao.packet_id
6113: AND a.packet_id = x_packetid
6134: IF g_debug = 'Y' THEN
6135: gms_error_pkg.gms_debug ('RESOURCE POSTED BALANCE UPDATE', 'C');
6136: END IF;
6137: g_error_stage := 'FC PR : RES P BAL';
6138: UPDATE gms_bc_packets bp
6139: SET (bp.res_budget_posted, bp.res_actual_posted, bp.res_enc_posted) =
6140: (SELECT SUM (NVL (budget_period_to_date, 0) * DECODE (balance_type, 'BGT', 1, 0)),
6141: SUM (NVL (actual_period_to_date, 0) * DECODE (balance_type, 'EXP', 1, 0)),
6142: SUM (NVL (encumb_period_to_date, 0) * DECODE (balance_type, 'REQ', 1, 'PO', 1, 'AP', 1, 'ENC', 1, 0))
6186: gms_error_pkg.gms_debug ( 'RESOURCE LEVEL - APPROVED/PENDING BALANCE FOR PREVIOUS AND CURRENT PACKETS','C' );
6187: END IF;
6188: -- Bug 2092791
6189: g_error_stage := 'FC PR : RES A BAL';
6190: UPDATE gms_bc_packets bp
6191: SET (bp.res_actual_approved, bp.res_actual_pending, bp.res_enc_approved,
6192: bp.res_enc_pending) =
6193: (SELECT
6194: SUM(actual_approved), --Bug 2490381 : Added SUM
6194: SUM(actual_approved), --Bug 2490381 : Added SUM
6195: SUM(actual_pending), --Bug 2490381 : Added SUM
6196: SUM(enc_approved), --Bug 2490381 : Added SUM
6197: SUM(enc_pending) --Bug 2490381 : Added SUM
6198: FROM gms_bc_packets_summary gmsbcs
6199: WHERE gmsbcs.packet_id = x_packetid
6200: AND gmsbcs.bud_task_id = bp.bud_task_id
6201: AND gmsbcs.budget_version_id = bp.budget_version_id
6202: AND gmsbcs.resource_list_member_id =
6231: effect_on_funds_code, -- Bug 2927485
6232: TRUNC ( budget_period_start_date ) budget_period_start_date,
6233: TRUNC ( budget_period_end_date ) budget_period_end_date,
6234: actual_flag
6235: FROM gms_bc_packets
6236: WHERE packet_id = x_packetid
6237: AND effect_on_funds_code in('D','I') --Bug 2092791
6238: AND status_code = 'P'
6239: AND r_funds_control_level_code <> 'N'
6280: x_budget_period_start_date_old := res_level.budget_period_start_date;
6281: x_budget_period_end_date_old := res_level.budget_period_end_date;
6282: ELSE
6283: IF nvl(res_level.entered_dr,0)>= 0 AND res_level.effect_on_funds_code = 'D' THEN --Bug 2092791 and 2927485 (Update pending balance
6284: UPDATE gms_bc_packets -- if funds are decreasing)
6285: SET res_actual_pending = NVL (res_actual_pending, 0)
6286: - DECODE (
6287: res_level.actual_flag,
6288: 'A', NVL (x_pending_actual, 0),
6318: IF g_debug = 'Y' THEN
6319: gms_error_pkg.gms_debug ('RESULT CODE UPDATE - RESOURCE', 'C');
6320: END IF;
6321: g_error_stage := 'FC PR : RES RESULT CODE';
6322: UPDATE gms_bc_packets bp
6323: SET bp.res_result_code = DECODE (
6324: bp.effect_on_funds_code,
6325: 'I', decode(bp.result_code,null,'P78',bp.result_code) ,
6326: 'D', DECODE (
6354:
6355:
6356: -- 7. Update all above levels with failure result code
6357: -- a. Propogate resource level to all other levels
6358: UPDATE gms_bc_packets bp
6359: SET bp.res_grp_result_code = res_result_code,
6360: bp.task_result_code = res_result_code,
6361: bp.top_task_result_code = res_result_code,
6362: bp.award_result_code = res_result_code,
6387: --===============================================================================
6388: -- RESOURCE LEVEL : INSERT NEGATIVE IN SUMMARY FOR FAILED TRANSACTIONS
6389: --===============================================================================
6390: g_error_stage := 'FC PR : RES INSERT NEG';
6391: INSERT INTO gms_bc_packets_summary
6392: (packet_id,
6393: creation_date,
6394: budget_version_id,
6395: top_task_id,
6408: budget_period_start_date,
6409: budget_period_end_date,
6410: -1 * nvl(sum(decode(status_code || actual_flag,'RA',nvl(entered_dr,0) - nvl(entered_cr,0),0)),0) ,
6411: -1 * nvl(sum(decode(status_code || actual_flag,'RE',nvl(entered_dr,0) - nvl(entered_cr,0),0)),0)
6412: from gms_bc_packets
6413: where packet_id = x_packetid
6414: and res_result_code in ('F92','F63','F75','F65')
6415: group by budget_version_id, top_task_id,bud_task_id, parent_resource_id,budget_period_start_date, budget_period_end_date;
6416:
6427: IF g_debug = 'Y' THEN
6428: gms_error_pkg.gms_debug ('RESOURCE GROUP POSTED BALANCE UPDATE', 'C');
6429: END IF;
6430: g_error_stage := 'FC PR : RESG P BAL';
6431: UPDATE gms_bc_packets bp
6432: SET (bp.res_grp_budget_posted, bp.res_grp_actual_posted, bp.res_grp_enc_posted) =
6433: (SELECT SUM (NVL (budget_period_to_date, 0) * DECODE (balance_type, 'BGT', 1, 0)),
6434: SUM (NVL (actual_period_to_date, 0) * DECODE (balance_type, 'EXP', 1, 0)),
6435: SUM (NVL (encumb_period_to_date, 0) * DECODE (balance_type, 'REQ', 1, 'PO', 1, 'AP', 1, 'ENC', 1, 0))
6483: gms_error_pkg.gms_debug ('RESOURCE GROUP LEVEL - APPROVED/PENDING BALANCE FOR CURRENT PACKET', 'C');
6484: END IF;
6485: -- Bug 2092791
6486: g_error_stage := 'FC PR : RESG A/P BAL';
6487: UPDATE gms_bc_packets bp
6488: SET (bp.res_grp_actual_approved, bp.res_grp_actual_pending, bp.res_grp_enc_approved,
6489: bp.res_grp_enc_pending) =
6490: (SELECT
6491: SUM(actual_approved),
6491: SUM(actual_approved),
6492: SUM(actual_pending),
6493: SUM(enc_approved),
6494: SUM(enc_pending)
6495: FROM gms_bc_packets_summary gmsbcs
6496: WHERE gmsbcs.packet_id = x_packetid
6497: AND gmsbcs.bud_task_id = bp.bud_task_id
6498: AND gmsbcs.budget_version_id = bp.budget_version_id
6499: AND gmsbcs.parent_resource_id = bp.parent_resource_id
6528: effect_on_funds_code, -- Bug 2927485
6529: TRUNC (budget_period_start_date) budget_period_start_date,
6530: TRUNC (budget_period_end_date) budget_period_end_date,
6531: actual_flag
6532: FROM gms_bc_packets
6533: WHERE packet_id = x_packetid
6534: AND effect_on_funds_code in ('D','I') -- Bug 2092791
6535: AND status_code = 'P'
6536: AND rg_funds_control_level_code <> 'N'
6575: x_budget_period_start_date_old := res_grp_level.budget_period_start_date;
6576: x_budget_period_end_date_old := res_grp_level.budget_period_end_date;
6577: ELSE
6578: 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
6579: UPDATE gms_bc_packets -- if funds are decreasing)
6580: SET res_grp_actual_pending = NVL (res_grp_actual_pending, 0)
6581: - DECODE (
6582: res_grp_level.actual_flag,
6583: 'A', NVL (x_pending_actual, 0),
6612: IF g_debug = 'Y' THEN
6613: gms_error_pkg.gms_debug ('RESULT CODE UPDATE - RESOURCE GROUP', 'C');
6614: END IF;
6615: g_error_stage := 'FC PR : RESG RESULT UPD';
6616: UPDATE gms_bc_packets bp
6617: SET bp.res_grp_result_code = DECODE (
6618: bp.effect_on_funds_code,
6619: 'I',decode(bp.result_code,null,'P78',bp.result_code) ,
6620: 'D', DECODE (
6648:
6649:
6650: -- 7. Update all above levels with failure result code
6651: -- b. Propogate resource group level to all other levels
6652: UPDATE gms_bc_packets bp
6653: SET bp.task_result_code = res_grp_result_code,
6654: bp.top_task_result_code = res_grp_result_code,
6655: bp.award_result_code = res_grp_result_code,
6656: bp.result_code = res_grp_result_code,
6681: --===============================================================================
6682: -- RESOURCE GROUP LEVEL : INSERT NEGATIVE IN SUMMARY FOR FAILED TRANSACTIONS
6683: --===============================================================================
6684: g_error_stage := 'FC PR : RESG IN NEG';
6685: INSERT INTO gms_bc_packets_summary
6686: (packet_id,
6687: creation_date,
6688: budget_version_id,
6689: top_task_id,
6700: budget_period_start_date,
6701: budget_period_end_date,
6702: -1 * nvl(sum(decode(status_code || actual_flag,'RA',nvl(entered_dr,0) - nvl(entered_cr,0),0)),0) ,
6703: -1 * nvl(sum(decode(status_code || actual_flag,'RE',nvl(entered_dr,0) - nvl(entered_cr,0),0)),0)
6704: from gms_bc_packets
6705: where packet_id = x_packetid
6706: and res_grp_result_code in ('F93','F63','F75','F65')
6707: and nvl(substr(res_result_code,1,1),'P') = 'P'
6708: group by budget_version_id, top_task_id, bud_task_id, budget_period_start_date, budget_period_end_date;
6719: IF g_debug = 'Y' THEN
6720: gms_error_pkg.gms_debug ('TASK :POSTED BALANCE ', 'C');
6721: END IF;
6722: g_error_stage := 'FC PR : TASK P BAL';
6723: UPDATE gms_bc_packets bp
6724: SET (bp.task_budget_posted, bp.task_actual_posted, bp.task_enc_posted) =
6725: (SELECT SUM (NVL (budget_period_to_date, 0) * DECODE (balance_type, 'BGT', 1, 0)),
6726: SUM (NVL (actual_period_to_date, 0) * DECODE (balance_type, 'EXP', 1, 0)),
6727: SUM (NVL (encumb_period_to_date, 0) * DECODE (balance_type, 'REQ', 1, 'PO', 1, 'AP', 1, 'ENC', 1, 0))
6760: gms_error_pkg.gms_debug ('TASK LEVEL - APPROVED/PENDING BALANCE FOR PREVIOUS PACKETS', 'C');
6761: END IF;
6762:
6763: g_error_stage := 'FC PR : TASK A/P BAL';
6764: UPDATE gms_bc_packets bp
6765: SET (bp.task_actual_approved, bp.task_actual_pending, bp.task_enc_approved,
6766: bp.task_enc_pending) =
6767: (SELECT
6768: SUM (actual_approved),
6768: SUM (actual_approved),
6769: SUM (actual_pending),
6770: SUM (enc_approved),
6771: SUM (enc_pending)
6772: FROM gms_bc_packets_summary gmsbcs
6773: WHERE gmsbcs.packet_id = x_packetid
6774: AND gmsbcs.budget_version_id = bp.budget_version_id
6775: AND gmsbcs.bud_task_id = bp.bud_task_id
6776: --Bug 2490381 : Changed "=" to "<=" to consider all the
6802: effect_on_funds_code, -- Bug 2927485
6803: TRUNC (budget_period_start_date) budget_period_start_date,
6804: TRUNC (budget_period_end_date) budget_period_end_date,
6805: actual_flag
6806: FROM gms_bc_packets
6807: WHERE packet_id = x_packetid
6808: AND effect_on_funds_code in ('D','I') -- Bug 2092791
6809: AND status_code = 'P'
6810: AND t_funds_control_level_code <> 'N'
6844: x_budget_period_start_date_old := task_level.budget_period_start_date;
6845: x_budget_period_end_date_old := task_level.budget_period_end_date;
6846: ELSE
6847: IF nvl(task_level.entered_dr,0) >=0 AND task_level.effect_on_funds_code = 'D' THEN --Bug 2092791 and 2927485 (Update pending balance
6848: UPDATE gms_bc_packets -- if funds are decreasing)
6849: SET task_actual_pending = NVL (task_actual_pending, 0)
6850: - DECODE (
6851: task_level.actual_flag,
6852: 'A', NVL (x_pending_actual, 0),
6882: IF g_debug = 'Y' THEN
6883: gms_error_pkg.gms_debug ('TASK : RESULT CODE ', 'C');
6884: END IF;
6885: g_error_stage := 'FC PR : TASK RESULT CD';
6886: UPDATE gms_bc_packets bp
6887: SET bp.task_result_code = DECODE (
6888: bp.effect_on_funds_code,
6889: 'I', decode(bp.result_code,null,'P78',bp.result_code) ,
6890: 'D', DECODE (
6918:
6919:
6920:
6921: -- 5. Update all above levels with failure result code
6922: UPDATE gms_bc_packets bp
6923: SET bp.result_code = bp.task_result_code,
6924: bp.top_task_result_code = bp.task_result_code,
6925: bp.award_result_code = bp.task_result_code,
6926: bp.status_code = DECODE(x_mode,'C','F','R')
6950: --==========================================================================
6951: -- TASK LEVEL : INSERT NEGATIVE IN SUMMARY FOR FAILED TRANSACTIONS
6952: --==========================================================================
6953: g_error_stage := 'FC PR : TASK IN NEG';
6954: INSERT INTO gms_bc_packets_summary
6955: (packet_id,
6956: creation_date,
6957: budget_version_id,
6958: top_task_id,
6967: budget_period_start_date,
6968: budget_period_end_date,
6969: -1 * nvl(sum(decode(status_code || actual_flag,'RA',nvl(entered_dr,0) - nvl(entered_cr,0),0)),0) ,
6970: -1 * nvl(sum(decode(status_code || actual_flag,'RE',nvl(entered_dr,0) - nvl(entered_cr,0),0)),0)
6971: from gms_bc_packets
6972: where packet_id = x_packetid
6973: and task_result_code in ('F91','F63','F75','F65')
6974: and substr(res_grp_result_code,1,1) = 'P'
6975: group by budget_version_id, top_task_id, budget_period_start_date, budget_period_end_date;
6986: IF g_debug = 'Y' THEN
6987: gms_error_pkg.gms_debug ('TOP TASK :POSTED BALANCE ', 'C');
6988: END IF;
6989: g_error_stage := 'FC PR : TTASK P BAL';
6990: UPDATE gms_bc_packets bp
6991: SET (bp.top_task_budget_posted, bp.top_task_actual_posted, bp.top_task_enc_posted) =
6992: (SELECT SUM (NVL (budget_period_to_date, 0) * DECODE (balance_type, 'BGT', 1, 0)),
6993: SUM (NVL (actual_period_to_date, 0) * DECODE (balance_type, 'EXP', 1, 0)),
6994: SUM (NVL (encumb_period_to_date, 0) * DECODE (balance_type, 'REQ', 1, 'PO', 1, 'AP', 1, 'ENC', 1, 0))
7029: END IF;
7030: g_error_stage := 'FC PR : TTASK A/P BAL';
7031: -- Bug 2092791
7032:
7033: UPDATE gms_bc_packets bp
7034: SET (bp.top_task_actual_approved, bp.top_task_actual_pending, bp.top_task_enc_approved,
7035: bp.top_task_enc_pending) =
7036: (SELECT
7037: SUM (actual_approved),
7037: SUM (actual_approved),
7038: SUM (actual_pending),
7039: SUM (enc_approved),
7040: SUM (enc_pending)
7041: FROM gms_bc_packets_summary gmsbcs
7042: WHERE gmsbcs.packet_id = x_packetid
7043: AND gmsbcs.budget_version_id = bp.budget_version_id
7044: AND gmsbcs.top_task_id = bp.top_task_id
7045: --Bug 2490381 : Changed "=" to "<=" to consider all the
7072: effect_on_funds_code, -- Bug 2927485
7073: TRUNC (budget_period_start_date) budget_period_start_date,
7074: TRUNC (budget_period_end_date) budget_period_end_date,
7075: actual_flag
7076: FROM gms_bc_packets
7077: WHERE packet_id = x_packetid
7078: AND effect_on_funds_code in ('D','I') -- Bug 2092791
7079: AND status_code = 'P'
7080: AND tt_funds_control_level_code <> 'N'
7117: x_budget_period_start_date_old := top_task_level.budget_period_start_date;
7118: x_budget_period_end_date_old := top_task_level.budget_period_end_date;
7119: ELSE
7120: 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
7121: UPDATE gms_bc_packets -- if funds are decreasing)
7122: SET top_task_actual_pending = NVL (top_task_actual_pending, 0)
7123: - DECODE (
7124: top_task_level.actual_flag,
7125: 'A', NVL (x_pending_actual, 0),
7155: IF g_debug = 'Y' THEN
7156: gms_error_pkg.gms_debug ('TOP TASK : RESULT CODE ', 'C');
7157: END IF;
7158: g_error_stage := 'FC PR : TTASK RESULT CODE';
7159: UPDATE gms_bc_packets bp
7160: SET bp.top_task_result_code = DECODE (
7161: bp.effect_on_funds_code,
7162: 'I', decode(bp.result_code,null,'P78',bp.result_code),
7163: 'D', DECODE (
7190: AND bp.status_code = 'P';
7191:
7192:
7193: -- 5. Update all above levels with failure result code
7194: UPDATE gms_bc_packets bp
7195: SET bp.result_code = bp.top_task_result_code,
7196: bp.award_result_code = bp.top_task_result_code,
7197: bp.status_code = DECODE(x_mode,'C','F','R')
7198: WHERE bp.packet_id = x_packetid
7218: End if;
7219: End If;
7220:
7221: g_error_stage := 'FC PR : TTASK IN NEG';
7222: INSERT INTO gms_bc_packets_summary
7223: (packet_id,
7224: creation_date,
7225: budget_version_id,
7226: budget_period_start_date,
7233: budget_period_start_date,
7234: budget_period_end_date,
7235: -1 * nvl(sum(decode(status_code || actual_flag, 'RA',nvl(entered_dr,0) - nvl(entered_cr,0),0)),0) ,
7236: -1 * nvl(sum(decode(status_code || actual_flag, 'RE',nvl(entered_dr,0) - nvl(entered_cr,0),0)),0)
7237: from gms_bc_packets
7238: where packet_id = x_packetid
7239: and top_task_result_code in ('F60','F63','F65','F75')
7240: and substr(task_result_code,1,1) = 'P'
7241: group by budget_version_id, budget_period_start_date, budget_period_end_date;
7253: IF g_debug = 'Y' THEN
7254: gms_error_pkg.gms_debug ('AWARD:POSTED BALANCE ', 'C');
7255: END IF;
7256: g_error_stage := 'FC PR : AWARD P B';
7257: UPDATE gms_bc_packets bp
7258: SET (bp.award_budget_posted, bp.award_actual_posted, bp.award_enc_posted) =
7259: (SELECT SUM (NVL (budget_period_to_date, 0) * DECODE (balance_type, 'BGT', 1, 0)),
7260: SUM (NVL (actual_period_to_date, 0) * DECODE (balance_type, 'EXP', 1, 0)),
7261: SUM (NVL (encumb_period_to_date, 0) * DECODE (balance_type, 'REQ', 1, 'PO', 1, 'AP', 1, 'ENC', 1, 0))
7290: gms_error_pkg.gms_debug ('AWARD LEVEL - APPROVED/PENDING BALANCE FOR PREVIOUS PACKETS', 'C');
7291: END IF;
7292: g_error_stage := 'FC PR : AWARD A/P B';
7293: -- Bug 2092791
7294: UPDATE gms_bc_packets bp
7295: SET (bp.award_actual_approved, bp.award_actual_pending, bp.award_enc_approved,
7296: bp.award_enc_pending) =
7297: (SELECT
7298: SUM (actual_approved),
7298: SUM (actual_approved),
7299: SUM (actual_pending),
7300: SUM (enc_approved),
7301: SUM (enc_pending)
7302: FROM gms_bc_packets_summary gmsbcs
7303: WHERE gmsbcs.packet_id = x_packetid
7304: AND gmsbcs.budget_version_id = bp.budget_version_id
7305: --Bug 2490381 : Changed "=" to "<=" to consider all the
7306: -- records from summary table which fall
7330: effect_on_funds_code, -- Bug 2927485
7331: TRUNC (budget_period_start_date) budget_period_start_date,
7332: TRUNC (budget_period_end_date) budget_period_end_date,
7333: actual_flag
7334: FROM gms_bc_packets
7335: WHERE packet_id = x_packetid
7336: AND effect_on_funds_code in ('D','I') -- 2092791
7337: AND status_code = 'P'
7338: AND a_funds_control_level_code <> 'N'
7371: x_budget_period_end_date_old := award_level.budget_period_end_date;
7372: ELSE
7373: IF nvl(award_level.entered_dr,0) >= 0 AND award_level.effect_on_funds_code = 'D' THEN --Bug 2092791 and 2927485(Update pending balance
7374: -- if funds are decreasing)
7375: UPDATE gms_bc_packets
7376: SET award_actual_pending = NVL (award_actual_pending, 0)
7377: - DECODE (
7378: award_level.actual_flag,
7379: 'A', NVL (x_pending_actual, 0),
7408: IF g_debug = 'Y' THEN
7409: gms_error_pkg.gms_debug ('AWARD: Result Code ', 'C');
7410: END IF;
7411: g_error_stage := 'FC PR : AWARD RESULT';
7412: UPDATE gms_bc_packets bp
7413: SET bp.award_result_code = DECODE (
7414: bp.effect_on_funds_code,
7415: 'I',decode(bp.result_code,null,'P78',bp.result_code ),
7416: 'D', DECODE (
7443: AND bp.status_code = 'P';
7444:
7445:
7446:
7447: UPDATE gms_bc_packets
7448: SET result_code = NVL (award_result_code, 'F53'),
7449: 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)
7450: WHERE packet_id = x_packetid
7451: AND effect_on_funds_code IN ('D', 'I')
7472: -- Bug 3426509 : Added following code to update the last advisory result code to result_code column
7473: -- e.g. if transaction passed funds check in advisory mode at Task and Resource Level , then the
7474: -- result_code will hold the result_code of Task Level funds check (i.e. 'P65').
7475:
7476: UPDATE gms_bc_packets
7477: SET result_code = DECODE (top_task_result_code,'P80', 'P80',
7478: DECODE (task_result_code,'P65', 'P65',
7479: DECODE (res_grp_result_code,'P73', 'P73',
7480: DECODE (res_result_code,'P69', 'P69',
7494: --================================================================================--
7495: -- Data clean up code from summary tables after Amount calculation
7496: --================================================================================--
7497: g_error_stage := 'FC PR : DELETE';
7498: DELETE gms_bc_packets_summary
7499: WHERE packet_id = x_packetid;
7500: DELETE gms_bc_packets_bvid
7501: WHERE packet_id = x_packetid;
7502:
7496: --================================================================================--
7497: g_error_stage := 'FC PR : DELETE';
7498: DELETE gms_bc_packets_summary
7499: WHERE packet_id = x_packetid;
7500: DELETE gms_bc_packets_bvid
7501: WHERE packet_id = x_packetid;
7502:
7503: -- Added commit for the base bug 3848201
7504: commit;
7506: -- Bug 2092791
7507:
7508: -- Bug 2176230
7509: -- Delete Record from gms_bc_packet_arrival_order able to ensure that transactions inserted into
7510: -- gms_bc_packets in check funds mode should not be accounted by any other subsequent packets.
7511:
7512: IF x_mode = 'C' THEN
7513: DELETE gms_bc_packet_arrival_order
7514: WHERE packet_id = x_packetid;
7528: -- This function calls all the procedures and functions for funds checker
7529: --------------------------------------------------------------------------------------------------------------------------------------------------
7530: -- Funds Check Processor
7531: FUNCTION gms_fcp (
7532: x_sobid IN gms_bc_packets.set_of_books_id%TYPE,
7533: x_packetid IN gms_bc_packets.packet_id%TYPE,
7534: x_mode IN VARCHAR2,
7535: x_partial IN VARCHAR2,
7536: x_arrival_seq IN gl_bc_packet_arrival_order.arrival_seq%TYPE,
7529: --------------------------------------------------------------------------------------------------------------------------------------------------
7530: -- Funds Check Processor
7531: FUNCTION gms_fcp (
7532: x_sobid IN gms_bc_packets.set_of_books_id%TYPE,
7533: x_packetid IN gms_bc_packets.packet_id%TYPE,
7534: x_mode IN VARCHAR2,
7535: x_partial IN VARCHAR2,
7536: x_arrival_seq IN gl_bc_packet_arrival_order.arrival_seq%TYPE,
7537: x_err_code OUT NOCOPY NUMBER,
7572: -- Bug 2176230
7573: -- ********************************************************************************
7574: -- NOTE :- Don't Put Any Commit after this point till gms_fc_process is complete,
7575: -- Reason being for funds checking in C Mode (check funds mode)
7576: -- transactions in gms_bc_packets should not be accounted by any other
7577: -- subsequent packet. (Applicable for x_mode = R,U,C,E)
7578: -- ********************************************************************************
7579:
7580:
7633: --
7634: -- ------------------------------------------------------------------------------------------------
7635: -- To Exit out NOCOPY of gms_fck in case of non_gms application calls.
7636: -- Bug 1966096. Funds check for non-sponsor projects and GL Transactions should happen base
7637: -- on the core functionality. Since their no entry in gms_bc_packets as those transactions
7638: -- gms funds checker should not return false which fail the above transactions.
7639: -- ------------------------------------------------------------------------------------------------
7640:
7641: FUNCTION gms_return_code (
7660: SELECT 0
7661: INTO x_err_code
7662: FROM DUAL
7663: WHERE EXISTS ( SELECT 'X'
7664: FROM gms_bc_packets
7665: WHERE packet_id = x_packetid
7666: AND status_code IN ('S', 'B')); --Bug Fix 1350100 Change status_code from 'A'
7667: --to 'B' to fix bug 2138376
7668: EXCEPTION
7681: SELECT 'F'
7682: INTO x_result_code
7683: FROM DUAL
7684: WHERE EXISTS ( SELECT result_code
7685: FROM gms_bc_packets
7686: WHERE packet_id = x_packetid
7687: AND SUBSTR (result_code, 1, 1) = 'F');
7688: EXCEPTION
7689: WHEN NO_DATA_FOUND THEN
7697:
7698: status_code_update (x_packetid, 'E');
7699: x_e_code := 'H';
7700:
7701: ELSE -- update gms_bc_packets with approved status
7702: IF g_debug = 'Y' THEN
7703: gms_error_pkg.gms_debug ( 'Calling status_code_update for encumbrances', 'C' );
7704: END IF;
7705: status_code_update (x_packetid, 'E');
7713: SELECT 1
7714: INTO x_err_code
7715: FROM DUAL
7716: WHERE EXISTS ( SELECT 'X'
7717: FROM gms_bc_packets
7718: WHERE packet_id = x_packetid
7719: AND SUBSTR (result_code, 1, 1) = 'F'
7720: AND status_code = 'T');
7721: x_return_code := 'T';
7728: SELECT 1
7729: INTO x_err_code
7730: FROM DUAL
7731: WHERE EXISTS ( SELECT 'X'
7732: FROM gms_bc_packets
7733: WHERE packet_id = x_packetid
7734: AND SUBSTR (result_code, 1, 1) = 'F');
7735: EXCEPTION
7736: WHEN NO_DATA_FOUND THEN
7796: -- in misc_gms_insert. Those will not be added here ..helps fail
7797: -- transactions in case of incorrect award number and expenditure type.
7798:
7799: -- R12 FundsCheck Management Uptake : Shifted IP/AP/PO/REQ/FAB cursor logic to procedure copy_gl_pkt_to_gms_pkt and
7800: -- merged with the selects which fetches IP/AP/PO/REQ/FAB record for inserting into gms_bc_packets.
7801: -- 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.
7802:
7803: CURSOR C_count_rec IS
7804: SELECT count(*) gms_txn_count,
7797: -- transactions in case of incorrect award number and expenditure type.
7798:
7799: -- R12 FundsCheck Management Uptake : Shifted IP/AP/PO/REQ/FAB cursor logic to procedure copy_gl_pkt_to_gms_pkt and
7800: -- merged with the selects which fetches IP/AP/PO/REQ/FAB record for inserting into gms_bc_packets.
7801: -- 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.
7802:
7803: CURSOR C_count_rec IS
7804: SELECT count(*) gms_txn_count,
7805: SUM(DECODE(gms.document_type,'REQ',1,0)) req_count,
7805: SUM(DECODE(gms.document_type,'REQ',1,0)) req_count,
7806: SUM(DECODE(gms.document_type,'PO',1,0)) po_count,
7807: SUM(DECODE(gms.document_type,'AP',1,0)) ap_count,
7808: SUM(DECODE(gms.document_type,'FAB',1,0)) fab_count
7809: FROM gms_bc_packets gms
7810: WHERE gms.packet_id= g_packet_id;
7811:
7812: -- R12 FundsCheck Management Uptake : New variables defined and used in deriving g_doc_type
7813: l_gms_txn_count NUMBER;
7859: -- R12 FundsCheck Management Uptake : Deleted existing logic which was deriving g_doc_type and
7860: -- g_non_gms_txn based on records in GL_bc_packets/AP/PO/REQ tables.
7861: -- With new architecture the gl_bc_packets/AP/PO/REQ validations will be performed during
7862: -- insertion of GMS packets in main session and current logic derives document type based on
7863: -- the data inserted into gms_bc_packets for current packet_id.
7864:
7865: OPEN c_count_rec;
7866: FETCH c_count_rec INTO l_gms_txn_count,l_req_count,l_po_count,l_ap_count,l_fab_count;
7867: CLOSE c_count_rec;
7886:
7887: End if; -- p_mode check - I
7888:
7889: IF g_debug = 'Y' THEN
7890: gms_error_pkg.gms_debug (g_error_procedure_name||':Total txns. in gms_bc_packets -'||l_gms_txn_count, 'C');
7891: gms_error_pkg.gms_debug (g_error_procedure_name||':Total REQ txns in gms_bc_packets -'||l_req_count,'C');
7892: gms_error_pkg.gms_debug (g_error_procedure_name||':Total PO txns in gms_bc_packets -'||l_po_count,'C');
7893: gms_error_pkg.gms_debug (g_error_procedure_name||':Total AP txns in gms_bc_packets -'||l_ap_count,'C');
7894: gms_error_pkg.gms_debug (g_error_procedure_name||':Total FAB txns in gms_bc_packets -'||l_fab_count,'C');
7887: End if; -- p_mode check - I
7888:
7889: IF g_debug = 'Y' THEN
7890: gms_error_pkg.gms_debug (g_error_procedure_name||':Total txns. in gms_bc_packets -'||l_gms_txn_count, 'C');
7891: gms_error_pkg.gms_debug (g_error_procedure_name||':Total REQ txns in gms_bc_packets -'||l_req_count,'C');
7892: gms_error_pkg.gms_debug (g_error_procedure_name||':Total PO txns in gms_bc_packets -'||l_po_count,'C');
7893: gms_error_pkg.gms_debug (g_error_procedure_name||':Total AP txns in gms_bc_packets -'||l_ap_count,'C');
7894: gms_error_pkg.gms_debug (g_error_procedure_name||':Total FAB txns in gms_bc_packets -'||l_fab_count,'C');
7895: gms_error_pkg.gms_debug (g_error_procedure_name||':Document type (ALL indicates Submit/Baseline)-'||g_doc_type,'C');
7888:
7889: IF g_debug = 'Y' THEN
7890: gms_error_pkg.gms_debug (g_error_procedure_name||':Total txns. in gms_bc_packets -'||l_gms_txn_count, 'C');
7891: gms_error_pkg.gms_debug (g_error_procedure_name||':Total REQ txns in gms_bc_packets -'||l_req_count,'C');
7892: gms_error_pkg.gms_debug (g_error_procedure_name||':Total PO txns in gms_bc_packets -'||l_po_count,'C');
7893: gms_error_pkg.gms_debug (g_error_procedure_name||':Total AP txns in gms_bc_packets -'||l_ap_count,'C');
7894: gms_error_pkg.gms_debug (g_error_procedure_name||':Total FAB txns in gms_bc_packets -'||l_fab_count,'C');
7895: gms_error_pkg.gms_debug (g_error_procedure_name||':Document type (ALL indicates Submit/Baseline)-'||g_doc_type,'C');
7896: END IF;
7889: IF g_debug = 'Y' THEN
7890: gms_error_pkg.gms_debug (g_error_procedure_name||':Total txns. in gms_bc_packets -'||l_gms_txn_count, 'C');
7891: gms_error_pkg.gms_debug (g_error_procedure_name||':Total REQ txns in gms_bc_packets -'||l_req_count,'C');
7892: gms_error_pkg.gms_debug (g_error_procedure_name||':Total PO txns in gms_bc_packets -'||l_po_count,'C');
7893: gms_error_pkg.gms_debug (g_error_procedure_name||':Total AP txns in gms_bc_packets -'||l_ap_count,'C');
7894: gms_error_pkg.gms_debug (g_error_procedure_name||':Total FAB txns in gms_bc_packets -'||l_fab_count,'C');
7895: gms_error_pkg.gms_debug (g_error_procedure_name||':Document type (ALL indicates Submit/Baseline)-'||g_doc_type,'C');
7896: END IF;
7897:
7890: gms_error_pkg.gms_debug (g_error_procedure_name||':Total txns. in gms_bc_packets -'||l_gms_txn_count, 'C');
7891: gms_error_pkg.gms_debug (g_error_procedure_name||':Total REQ txns in gms_bc_packets -'||l_req_count,'C');
7892: gms_error_pkg.gms_debug (g_error_procedure_name||':Total PO txns in gms_bc_packets -'||l_po_count,'C');
7893: gms_error_pkg.gms_debug (g_error_procedure_name||':Total AP txns in gms_bc_packets -'||l_ap_count,'C');
7894: gms_error_pkg.gms_debug (g_error_procedure_name||':Total FAB txns in gms_bc_packets -'||l_fab_count,'C');
7895: gms_error_pkg.gms_debug (g_error_procedure_name||':Document type (ALL indicates Submit/Baseline)-'||g_doc_type,'C');
7896: END IF;
7897:
7898: -- ======================================
7959: pod.po_header_id po_header_id,
7960: pod.project_id project_id,
7961: pod.task_id task_id,
7962: adl.award_id award_id
7963: FROM gms_bc_packets pkt,
7964: gl_bc_packets gl,
7965: ap_invoice_distributions_all ap,
7966: po_distributions_all pod,
7967: gms_award_distributions adl
7989: award_id,
7990: expenditure_type,
7991: document_type,
7992: SUM ( NVL (entered_dr, 0) - NVL (entered_cr, 0)) raw_cost
7993: FROM gms_bc_packets
7994: WHERE packet_id = x_packet_id
7995: and nvl(burden_adjustment_flag,'N') = 'N'
7996: AND EXISTS ( SELECT 1
7997: FROM gms_bc_packets
7993: FROM gms_bc_packets
7994: WHERE packet_id = x_packet_id
7995: and nvl(burden_adjustment_flag,'N') = 'N'
7996: AND EXISTS ( SELECT 1
7997: FROM gms_bc_packets
7998: WHERE packet_id = x_packet_id
7999: AND document_type = 'AP')
8000: GROUP BY packet_id,
8001: document_header_id,
8013: x_expenditure_type VARCHAR2
8014: )
8015: IS
8016: SELECT MIN (bc_packet_id)
8017: FROM gms_bc_packets
8018: WHERE packet_id = x_packet_id
8019: AND document_type = x_doc_type
8020: AND document_header_id = x_document_header_id
8021: AND document_distribution_id = x_document_distribution_id
8036: CLOSE financials_options;
8037:
8038: FOR bc_packets IN c_po_doc (l_inv_encumbrance_type_id)
8039: LOOP
8040: UPDATE gms_bc_packets
8041: SET document_type = 'PO',
8042: document_header_id = bc_packets.po_header_id,
8043: document_distribution_id = bc_packets.po_dist_id,
8044: project_id = bc_packets.project_id ,
8046: award_id = bc_packets.award_id
8047: WHERE ROWID = bc_packets.pkt_row_id;
8048: END LOOP;
8049:
8050: Delete from gms_bc_packets
8051: Where packet_id = x_packet_id
8052: And document_type = 'AP'
8053: And bc_packet_id in
8054: ( select a.bc_packet_id
8051: Where packet_id = x_packet_id
8052: And document_type = 'AP'
8053: And bc_packet_id in
8054: ( select a.bc_packet_id
8055: from gms_bc_packets a,
8056: ap_invoice_distributions_all apd
8057: where a.packet_id = x_packet_id
8058: and a.document_type = 'AP'
8059: and a.document_header_id = apd.invoice_id
8081: CLOSE min_bc_packet_id;
8082:
8083: IF bc_packets.raw_cost >= 0
8084: THEN
8085: UPDATE gms_bc_packets
8086: SET entered_dr = bc_packets.raw_cost,
8087: entered_cr = 0
8088: WHERE packet_id = x_packet_id
8089: AND document_type = bc_packets.document_type
8095: -- lines should be updated with 0. This is done for the same AP
8096: -- Distribution Line. BC packets gets data in multiple lines for
8097: -- the same distribution line.
8098: -- ---------------------------------------------------------------
8099: UPDATE gms_bc_packets
8100: SET entered_cr = 0,
8101: entered_dr = 0
8102: WHERE packet_id = x_packet_id
8103: AND bc_packet_id > l_bc_packet_id
8108: AND expenditure_type = bc_packets.expenditure_type;
8109:
8110: ELSIF bc_packets.raw_cost < 0
8111: THEN
8112: UPDATE gms_bc_packets
8113: SET entered_cr = bc_packets.raw_cost * -1,
8114: entered_dr = 0
8115: WHERE packet_id = x_packet_id
8116: AND document_type = bc_packets.document_type
8121: -- lines should be updated with 0. This is done for the same AP
8122: -- Distribution Line. BC packets gets data in multiple lines for
8123: -- the same distribution line.
8124: -- ---------------------------------------------------------------
8125: UPDATE gms_bc_packets
8126: SET entered_cr = 0,
8127: entered_dr = 0
8128: WHERE packet_id = x_packet_id
8129: AND bc_packet_id > l_bc_packet_id
8141:
8142: select 1
8143: into l_pkt_row
8144: from dual
8145: where exists ( select 1 from gms_bc_packets
8146: where packet_id = x_packet_id ) ;
8147:
8148: x_pkt_row := l_pkt_row ;
8149: EXCEPTION
8160:
8161: -- ------------------------------------ R12 Start ------------------------------------------------+
8162: -- R12 Changes: New procedure
8163: -- --------------------------------------------------------------------------------+
8164: -- This procedure will update the following columns in gms_bc_packets: serial_id,
8165: -- session_id,packet_id,period_name,period_year,period_num,account_type and status.
8166: -- Status will be upated from I to P. Called from gms_fck
8167: -- --------------------------------------------------------------------------------+
8168: PROCEDURE Synch_gms_gl_packets(p_packet_id IN Number)
8166: -- Status will be upated from I to P. Called from gms_fck
8167: -- --------------------------------------------------------------------------------+
8168: PROCEDURE Synch_gms_gl_packets(p_packet_id IN Number)
8169: IS
8170: l_gms_packet_id gms_bc_packets.packet_id%type;
8171:
8172: -- This cursor will fetch only if called for AP/PO/REQ
8173: CURSOR get_temp_packet_id Is
8174: Select gbc.packet_id
8171:
8172: -- This cursor will fetch only if called for AP/PO/REQ
8173: CURSOR get_temp_packet_id Is
8174: Select gbc.packet_id
8175: from gms_bc_packets gbc
8176: where gbc.source_event_id in
8177: (select glbc.event_id
8178: from gl_bc_packets glbc
8179: where glbc.packet_id = p_packet_id)
8218: IF g_debug = 'Y' THEN
8219: gms_error_pkg.gms_debug ('Synch_gms_gl_packets Strat : Previously establised packet is = '|| l_gms_packet_id,'C');
8220: END IF;
8221:
8222: /* Bug 5250793 : Added a join with gl_encumbrance_types so that the gl_bc_packets_rowid on gms_bc_packets is updated
8223: correctly for an invoice matched to a PO scenario. Before this change , the same gl_bc_packets_rowid was updated on
8224: gms_bc_packets for both the PO reversal and AP reserve records irrespective of the corresponding rowid on gl_bc_packets.*/
8225:
8226: -- Update gms_bc_packets data
8220: END IF;
8221:
8222: /* Bug 5250793 : Added a join with gl_encumbrance_types so that the gl_bc_packets_rowid on gms_bc_packets is updated
8223: correctly for an invoice matched to a PO scenario. Before this change , the same gl_bc_packets_rowid was updated on
8224: gms_bc_packets for both the PO reversal and AP reserve records irrespective of the corresponding rowid on gl_bc_packets.*/
8225:
8226: -- Update gms_bc_packets data
8227: /* Bug 5285217 : Changed the code to use "FOR" loop so that gl_bc_packets_rowid on gms_bc_packets is updated correctly
8228: for an invoice matched to a PO with Quantity Variance Scenario. Before this change , the same gl_bc_packets_rowid was
8222: /* Bug 5250793 : Added a join with gl_encumbrance_types so that the gl_bc_packets_rowid on gms_bc_packets is updated
8223: correctly for an invoice matched to a PO scenario. Before this change , the same gl_bc_packets_rowid was updated on
8224: gms_bc_packets for both the PO reversal and AP reserve records irrespective of the corresponding rowid on gl_bc_packets.*/
8225:
8226: -- Update gms_bc_packets data
8227: /* Bug 5285217 : Changed the code to use "FOR" loop so that gl_bc_packets_rowid on gms_bc_packets is updated correctly
8228: for an invoice matched to a PO with Quantity Variance Scenario. Before this change , the same gl_bc_packets_rowid was
8229: updated on gms_bc_packets for both the invoice reserve and the quantity variance reserve records irrespective of
8230: the corresponding rowid on gl_bc_packets.*/
8223: correctly for an invoice matched to a PO scenario. Before this change , the same gl_bc_packets_rowid was updated on
8224: gms_bc_packets for both the PO reversal and AP reserve records irrespective of the corresponding rowid on gl_bc_packets.*/
8225:
8226: -- Update gms_bc_packets data
8227: /* Bug 5285217 : Changed the code to use "FOR" loop so that gl_bc_packets_rowid on gms_bc_packets is updated correctly
8228: for an invoice matched to a PO with Quantity Variance Scenario. Before this change , the same gl_bc_packets_rowid was
8229: updated on gms_bc_packets for both the invoice reserve and the quantity variance reserve records irrespective of
8230: the corresponding rowid on gl_bc_packets.*/
8231:
8225:
8226: -- Update gms_bc_packets data
8227: /* Bug 5285217 : Changed the code to use "FOR" loop so that gl_bc_packets_rowid on gms_bc_packets is updated correctly
8228: for an invoice matched to a PO with Quantity Variance Scenario. Before this change , the same gl_bc_packets_rowid was
8229: updated on gms_bc_packets for both the invoice reserve and the quantity variance reserve records irrespective of
8230: the corresponding rowid on gl_bc_packets.*/
8231:
8232: FOR glbcrec in c_gl_bc_pkt LOOP
8233:
8263:
8264: End loop;
8265: End If; --If glbcrec.source_distribution_type = 'AP_PREPAY' then
8266:
8267: -- Update gms_bc_packets data
8268: Update gms_bc_packets gbc
8269: set (gbc.packet_id,gbc.status_code,
8270: gbc.session_id,gbc.serial_id,
8271: gbc.je_category_name,
8264: End loop;
8265: End If; --If glbcrec.source_distribution_type = 'AP_PREPAY' then
8266:
8267: -- Update gms_bc_packets data
8268: Update gms_bc_packets gbc
8269: set (gbc.packet_id,gbc.status_code,
8270: gbc.session_id,gbc.serial_id,
8271: gbc.je_category_name,
8272: gbc.je_source_name,gbc.period_name,
8302: So the 'gl_bc_packets_rowid IS NULL' and 'ROWNUM = 1' conditions are used to differentiate between
8303: the two records.
8304: For the first AP record (either invoice reserve record or the Quantity variance reserve record) in gl_bc_packets,
8305: the 'ROWNUM=1' and the (entered_dr-entered_cr) conditions are used to identify the corresponding record
8306: in gms_bc_packets. The (entered_dr-entered_cr) check is not suitable for the scenario in which the invoice amount and the quantity
8307: variance amount are same. But as the amounts are same for both the invoice reserve and quantity variance reserve
8308: records , only 'ROWNUM=1' check will suffice as we need not distinguish between the invoice reserve and the
8309: quantity variance record.
8310: For the second AP record (one among the invoice reserve record or the Quantity variance reserve record for which the
8307: variance amount are same. But as the amounts are same for both the invoice reserve and quantity variance reserve
8308: records , only 'ROWNUM=1' check will suffice as we need not distinguish between the invoice reserve and the
8309: quantity variance record.
8310: For the second AP record (one among the invoice reserve record or the Quantity variance reserve record for which the
8311: corresponding record is not yet updated in gms_bc_packets) in gl_bc_packets , the 'gl_bc_packets_rowid IS NULL'
8312: condition is used to identify the corresponding record in gms_bc_packets.*/
8313: and gbc.gl_bc_packets_rowid IS NULL
8314: and ((nvl(gbc.entered_dr,0) - nvl(gbc.entered_cr,0)) = (nvl(glbcrec.entered_dr,0) - nvl(glbcrec.entered_cr,0)))
8315: and ROWNUM = 1
8308: records , only 'ROWNUM=1' check will suffice as we need not distinguish between the invoice reserve and the
8309: quantity variance record.
8310: For the second AP record (one among the invoice reserve record or the Quantity variance reserve record for which the
8311: corresponding record is not yet updated in gms_bc_packets) in gl_bc_packets , the 'gl_bc_packets_rowid IS NULL'
8312: condition is used to identify the corresponding record in gms_bc_packets.*/
8313: and gbc.gl_bc_packets_rowid IS NULL
8314: and ((nvl(gbc.entered_dr,0) - nvl(gbc.entered_cr,0)) = (nvl(glbcrec.entered_dr,0) - nvl(glbcrec.entered_cr,0)))
8315: and ROWNUM = 1
8316: -- 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
8313: and gbc.gl_bc_packets_rowid IS NULL
8314: and ((nvl(gbc.entered_dr,0) - nvl(gbc.entered_cr,0)) = (nvl(glbcrec.entered_dr,0) - nvl(glbcrec.entered_cr,0)))
8315: and ROWNUM = 1
8316: -- 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
8317: -- whereas for the corresponding record gms_bc_packets will have source_distribution_id_num_1 as po_distribution_id
8318: -- Hence for this scenario we will check encumbrance_type_id to get PO record from GL.
8319: -- This update is NOT for the PO reversal record in an AP matched to a PO scenario.
8320: 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.
8321: from gl_bc_packets glbc1
8330: gms_error_pkg.gms_debug ('Synch_gms_gl_packets Strat : Updated bc packets except PO matched to an invoice '|| SQL%ROWCOUNT,'C');
8331: END IF;
8332:
8333: -- 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
8334: -- whereas for the corresponding record gms_bc_packets will have source_distribution_id_num_1 as po_distribution_id
8335: -- Hence for this scenario we will check encumbrance_type_id to get PO record from GL.
8336: -- 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.
8337:
8338: Update gms_bc_packets gbc
8334: -- whereas for the corresponding record gms_bc_packets will have source_distribution_id_num_1 as po_distribution_id
8335: -- Hence for this scenario we will check encumbrance_type_id to get PO record from GL.
8336: -- 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.
8337:
8338: Update gms_bc_packets gbc
8339: set (gbc.packet_id,gbc.status_code,
8340: gbc.session_id,gbc.serial_id,
8341: gbc.period_name,
8342: gbc.period_year,gbc.period_num,
8364: gms_error_pkg.gms_debug ('Synch_gms_gl_packets Strat : Updated bc packets for PO matched to an invoice '|| SQL%ROWCOUNT,'C');
8365: END IF;
8366:
8367: /* Bug 5645290 - Start */
8368: Update gms_bc_packets gbc
8369: set (gbc.packet_id,gbc.status_code,
8370: gbc.session_id,gbc.serial_id,
8371: gbc.je_category_name,
8372: gbc.je_source_name,gbc.period_name,
8379: gbcparent.je_source_name,gbcparent.period_name,
8380: gbcparent.period_year,gbcparent.period_num,
8381: gbcparent.account_type,
8382: gbcparent.gl_bc_packets_rowid
8383: from gms_bc_packets gbcparent
8384: where gbcparent.bc_packet_id = gbc.parent_bc_packet_id)
8385: where gbc.packet_id = l_gms_packet_id
8386: and gbc.status_code = 'I'
8387: and gbc.gl_bc_packets_rowid IS NULL;
8463: g_packet_id||','||g_mode||','||x_partial,'C');
8464: END IF;
8465:
8466: -- ---------------------------------------------------------------------------------------------------------+
8467: -- 101: R12 Funds Management uptake : This procedure will synch data in gl_bc_packets to gms_bc_packets ..
8468: -- ---------------------------------------------------------------------------------------------------------+
8469: SYNCH_GMS_GL_PACKETS(x_packetid);
8470:
8471: -------------------------------------------------------------------------------+
8502: pa_currency.set_currency_info;
8503:
8504: -------------------------------------------------------------------------------+
8505: -- 4. Call delete_pending_txns to delete bc pkt txns. left in 'P' status ..
8506: -- This procedure will delete pending records from gms_bc_packets
8507: -------------------------------------------------------------------------------+
8508: x_e_stage := 'delete_pending_txns';
8509: IF g_debug = 'Y' THEN
8510: gms_error_pkg.gms_debug (g_error_procedure_name||':'||x_e_stage,'C');
8591: -- copy_gl_pkt_to_gms_pkt of fundscheck process.
8592:
8593: --IF x_mode IN ('R', 'U', 'C', 'E','X') THEN
8594: IF x_mode IN ( 'E','X') THEN
8595: -- Calculate burdenable_cost and update on gms_bc_packets ..............
8596: -- Calling burden calculation for all except for mode : Submit,Baseline,Interface
8597: IF g_debug = 'Y' THEN
8598: x_e_stage := 'Burdenable Raw Cost calculation';
8599: g_error_procedure_name := 'Gms_fck';
8820:
8821: PROCEDURE TIEBACK_FAILED_ACCT_STATUS (p_bc_mode IN VARCHAR2 DEFAULT 'C') IS
8822: BEGIN
8823:
8824: UPDATE gms_bc_packets
8825: SET status_code = DECODE(p_bc_mode,'C','F','R'),
8826: result_code = 'F22'
8827: WHERE status_code in ('I','A','S')
8828: AND source_event_id IN