1 PACKAGE GMS_FUNDS_CONTROL_PKG AS
2 -- $Header: gmsfcfcs.pls 120.11 2007/03/13 12:18:42 cmishra ship $
3 -- Everest Funds Checker Main Routine
4
5 -- R12 Funds Management Uptake : AP/PO/REQ will no longer be saving data before
6 -- firing fundscheck hence existing logic is modified such that code which needs access
7 -- to AP/PO/REQ tables gets fired from main session and insert/update code gets fired in
8 -- autonomous mode. Added below variables for new logic.
9
10 TYPE t_set_of_books_id_type IS TABLE OF gms_bc_packets.set_of_books_id%type INDEX BY BINARY_INTEGER;
11 TYPE t_je_source_name_type IS TABLE OF gms_bc_packets.je_source_name%type INDEX BY BINARY_INTEGER;
12 TYPE t_je_category_name_type IS TABLE OF gms_bc_packets.je_category_name%type INDEX BY BINARY_INTEGER;
13 TYPE t_actual_flag_type IS TABLE OF gms_bc_packets.actual_flag%type INDEX BY BINARY_INTEGER;
14 TYPE t_project_id_type IS TABLE OF gms_bc_packets.project_id%type INDEX BY BINARY_INTEGER;
15 TYPE t_task_id_type IS TABLE OF gms_bc_packets.task_id%type INDEX BY BINARY_INTEGER;
16 TYPE t_award_id_type IS TABLE OF gms_bc_packets.award_id%type INDEX BY BINARY_INTEGER;
17 TYPE t_result_code_type IS TABLE OF gms_bc_packets.result_code%type INDEX BY BINARY_INTEGER;
18 TYPE t_entered_dr_type IS TABLE OF gms_bc_packets.entered_dr%type INDEX BY BINARY_INTEGER;
19 TYPE t_entered_cr_type IS TABLE OF gms_bc_packets.entered_cr%type INDEX BY BINARY_INTEGER;
20 TYPE t_etype_type IS TABLE OF gms_bc_packets.expenditure_type%type INDEX BY BINARY_INTEGER;
21 TYPE t_exp_org_id_type IS TABLE OF gms_bc_packets.expenditure_organization_id%type INDEX BY BINARY_INTEGER;
22 TYPE t_exp_item_date_type IS TABLE OF gms_bc_packets.expenditure_item_date%type INDEX BY BINARY_INTEGER;
23 TYPE t_document_type_type IS TABLE OF gms_bc_packets.document_type%type INDEX BY BINARY_INTEGER;
24 TYPE t_doc_header_id_type IS TABLE OF gms_bc_packets.document_header_id%type INDEX BY BINARY_INTEGER;
25 TYPE t_doc_dist_id_type IS TABLE OF gms_bc_packets.document_distribution_id%type INDEX BY BINARY_INTEGER;
26 TYPE t_vendor_id_type IS TABLE OF gms_bc_packets.vendor_id%type INDEX BY BINARY_INTEGER;
27 TYPE t_exp_category_type IS TABLE OF gms_bc_packets.expenditure_category%type INDEX BY BINARY_INTEGER;
28 TYPE t_revenue_category_type IS TABLE OF gms_bc_packets.revenue_category%type INDEX BY BINARY_INTEGER;
29 TYPE t_ind_cmp_set_id_type IS TABLE OF gms_bc_packets.ind_compiled_set_id%type INDEX BY BINARY_INTEGER;
30 TYPE t_reference6_type IS TABLE OF po_bc_distributions.reference6%type INDEX BY BINARY_INTEGER;
31 TYPE t_reference13_type IS TABLE OF po_bc_distributions.reference13%type INDEX BY BINARY_INTEGER;
32 Type t_po_rate_type IS TABLE OF po_distributions_all.rate%type INDEX BY BINARY_INTEGER; -- Bug 5614467
33
34 --R12 AP Lines Uptake enhancement : Forward porting bug 4450291
35 TYPE t_brc_type IS TABLE OF gms_bc_packets.burdenable_raw_cost%type INDEX BY BINARY_INTEGER;
36 TYPE t_doc_dist_line_num_type IS TABLE OF ap_invoice_distributions_all.distribution_line_number%type INDEX BY BINARY_INTEGER;
37 TYPE t_invoice_type_code_type IS TABLE OF ap_invoices_all.invoice_type_lookup_code%type INDEX BY BINARY_INTEGER;
38 TYPE t_inv_source_type IS TABLE OF ap_invoices_all.source%type INDEX BY BINARY_INTEGER;
39 TYPE t_source_event_id_type IS TABLE OF gms_bc_packets.source_event_id%type INDEX BY BINARY_INTEGER;
40 TYPE t_event_type_code_type IS TABLE OF po_bc_distributions.event_type_code%type INDEX BY BINARY_INTEGER;
41 TYPE t_main_or_backing_type IS TABLE OF po_bc_distributions.main_or_backing_code%type INDEX BY BINARY_INTEGER;
42 TYPE t_parent_reversal_id_type IS TABLE OF ap_invoice_distributions_all.parent_reversal_id%type INDEX BY BINARY_INTEGER; -- Bug 5369296
43
44
45 /*-----------------------------------------------------------------------------------------------
46
47 Function : gms_fck
48 Purpose : Fundschecking transaction against award budget
49 Parameters and meaning.
50 -----------------------
51 x_sobid : Set of books id
52 x_packetid : The packet, sent for funds check
53 x_mode : R = Reserve,C= Check Funds,S= Sumbit(Budget),B = Baselining(Budget)
54 x_partial : Partial = Y, Full = N
55 x_user_id : Not used in grants
56 x_user_resp_id : Not used in grants
57 x_execute : Not used in grants
58
59
60 -----------------------------------------------------------------------------------------------*/
61 FUNCTION gms_fck(
62 x_sobid IN NUMBER,
63 x_packetid IN NUMBER,
64 x_mode IN VARCHAR2 DEFAULT 'C',
65 x_override IN VARCHAR2 DEFAULT 'N',
66 x_partial IN VARCHAR2 DEFAULT 'N',
67 x_user_id IN NUMBER DEFAULT NULL,
68 x_user_resp_id IN NUMBER DEFAULT NULL,
69 x_execute IN VARCHAR2 DEFAULT 'N',
70 x_return_code IN OUT NOCOPY varchar2,
71 x_e_code OUT NOCOPY VARCHAR2,
72 x_e_stage OUT NOCOPY VARCHAR2) RETURN BOOLEAN;
73
74 /*-----------------------------------------------------------------------------------------------
75
76 Procedure : gms_gl_return_code
77 Purpose : procedure to return the OGM fundscheck result to GL fundschecker glx_fck
78 Parameters and meaning.
79 ------------------------
80 x_packetid : The packet, sent for funds check
81 x_mode : R = Reserve,C= Check Funds,S= Sumbit(Budget),B = Baselining(Budget)
82 x_gl_return_code : gl return code which is used to generate the status code in Grants
83 x_gms_return_code : gms return code calculated after fund check.
84 x_gms_partial_flag : Partial = Y, Full = N
85
86 -----------------------------------------------------------------------------------------------*/
87 Procedure gms_gl_return_code (
88 x_packet_id IN number,
89 x_mode in varchar2,
90 x_gl_return_code IN OUT NOCOPY VARCHAR2,
91 x_gms_return_code IN VARCHAR2,
92 x_gms_partial_flag IN VARCHAR2,
93 x_er_code IN OUT NOCOPY VARCHAR2,
94 x_er_stage IN OUT NOCOPY VARCHAR2 );
95
96
97 /* -----------------------------------------------------------------------------------------------
98 This function is used to calculate resource list member id while copying adls when REQ ->PO
99 -> AP
100 -------------------------------------------------------------------------------------------------- */
101
102 Procedure setup_rlmi (
103 x_packet_id IN NUMBER,
104 x_mode IN VARCHAR2,
105 x_err_code OUT NOCOPY NUMBER,
106 x_err_buff OUT NOCOPY VARCHAR2 );
107
108 /* -----------------------------------------------------------------------------------------------
109
110 Procedure : delete_pending_txns
111 Purpose : This procedure will delete pending records in gms_bc_packets associated with a
112 request that has been terminated.
113 After deleting the records from gms_bc_packets, corresponding request_id entry will
114 be deleted from gms_concurrency_control table.
115
116 -------------------------------------------------------------------------------------------------- */
117
118 Procedure delete_pending_txns
119 (x_err_code OUT NOCOPY NUMBER,
120 x_err_buff OUT NOCOPY VARCHAR2 );
121
122 /* -----------------------------------------------------------------------------------------------
123 Function : sponsored_project
124 Purpose : Returns 'Y' if project parameter passed is sponsored, else returns 'N'
125 -------------------------------------------------------------------------------------------------- */
126 FUNCTION sponsored_project(p_project_id IN NUMBER)
127 RETURN VARCHAR2;
128
129 PRAGMA RESTRICT_REFERENCES(sponsored_project, WNDS);
130 -----------------------------------------------------------------------------------------------+
131
132 -- R12 Fundscheck Management uptake: AP/PO/REQ will no longer be saving data before
133 -- firing fundscheck hence existing logic is modified such that code which needs access
134 -- to AP/PO/REQ tables gets fired from main session and the insert/update code gets fired in
135 -- autonomous mode.
136 -- Introduced new procedure to insert data into gms_bc_packets based on data
137 -- in gl_bc_packets and AP,PO,REQ transaction tables.This procedure is fired from GL
138 -- main budgetory control API.
139
140 PROCEDURE COPY_GL_PKT_TO_GMS_PKT (p_application_id IN NUMBER,
141 p_mode IN VARCHAR2 DEFAULT 'C',
142 p_partial_flag IN VARCHAR2 DEFAULT 'N',
143 x_return_code OUT NOCOPY VARCHAR2 ) ;
144
145 -- R12 Funds Management Uptake : This tieback procedure is called from PSA_BC_XLA_PVT.Budgetary_control
146 -- if SLA accounting fails.This API will mark the gms_bc_packet records to failed status.
147
148 PROCEDURE TIEBACK_FAILED_ACCT_STATUS (p_bc_mode IN VARCHAR2 DEFAULT 'C');
149
150 END GMS_FUNDS_CONTROL_PKG;