1 package PA_FUNDS_CONTROL_PKG as
2 -- $Header: PABCFCKS.pls 120.3 2006/06/27 06:07:36 cmishra noship $
3
4 -- Declare Global Variables
5 g_debug_mode VARCHAR2(10); -- Moved from body to spec so that PABCPKTB.pls can access ..
6
7 /*---------------------------------------------------------------------------------------------------------- +
8 --This is the Main funds check function which calls all the other functions and procedures.
9 --This API is called from the following places
10 -- GL - Funds check process
11 -- CBC - Funds check process
12 -- Costing - During Expenditure Cost Distribution process
13 -- Transaction Import Process
14 -- Baseline of Budget
15 --
16 -- Parameters :
17 -- p_set_of_books_id : Set of Books ID in GL accounts for the packet to funds checked.
18 -- p_calling_module : Identifier of the module from which the funds checker will be invoked
19 -- The valid values are
20 -- GL - General ledger
21 -- CBC - Contract Conmmitment
22 -- CHECK_BASELINE - Budget Baselining
23 -- RESERVE_BASELINE - Budget Baselining
24 -- TRXNIMPORT - Transaction Import
25 -- DISTVIADJ - Invoice Adjustments
26 -- DISTERADJ - Expense Report Adjustments
27 -- INTERFACVI - Interface VI to payables
28 -- INTERFACER - Interface ER to payables
29 -- EXPENDITURE - For actuals entering through Projects
30 --
31 -- P_packet_id : Packet ID of the packet to be funds checked.
32 -- P_mode : Funds Checker Operation Mode.
33 -- C - Check funds
34 -- R - Reserve funds.
35 -- U - Un-reserve (only for REQ,PO and AP)
36 -- B - Called from budget baseline process (Processed like check funds)
37 -- S - Called from Budget submission (Processed like check funds)
38 -- P_partial_flag : Indicates the packet can be fundschecked/reserverd partially or not
39 -- Y - Partial
40 -- N - Full mode, default is N
41 -- P_reference1 If the p_mode is 'R',U,C,F' and p_calling_module = 'CBC'or 'EXP' then
42 -- this parameter holds the document type info Document Type
43 -- EXP - Expenditures originating from project
44 -- CC - Contract Commitments
45 -- Elsif p_mode is B, S and p_calling_module = 'BASELINE' then
46 -- this parameter holds the ext_bdgt_link_flag
47 -- End if;
48 -- * This param is not null for EXP , CC document type and Base line mode
49 -- P_reference2 If the p_mode is 'R',U,C,F' and p_calling_module = 'CBC' then
50 -- this parameter holds the document header info for Contract Commitment
51 -- document Header Id from Contract Commitments
52 -- IGC_CC_INTERFACE.CC_HEADER_ID
53 -- Elsif p_mode is B, S and p_calling_module = 'BASELINE' then
54 -- this parameter holds the project_id
55 -- End if;
56 -- * This param is not null for CC document type and Base line mode
57 -- P_reference3 If p_mode is B, S and p_calling_module = 'BASELINE' then
58 -- this parameter holds the budget_version_id
59 -- End if;
60 -- * This param is not null for Base line mode and Contract commitments
61 --
62 -- p_conc_flag : identifies when funds check is invoked from concurrent program.
63 -- The valid values are
64 -- 'N' default
65 -- 'Y' - concurrent programm
66 --
67 -- x_return_status : Fudscheck return status
68 -- Valid Status are
69 -- S - Success
70 -- F - Failure
71 -- T - Fatal
72 -- x_error_stage :Identifies the place where funds check process failed
73 --
74 -- x_error_messagee :defines the type of error : SQLerror||sqlcode
75 -------------------------------------------------------------------------------------------------------------+ */
76 FUNCTION pa_funds_check
77 (p_calling_module IN VARCHAR2
78 ,p_conc_flag IN VARCHAR2 DEFAULT 'N'
79 ,p_set_of_book_id IN NUMBER
80 ,p_packet_id IN NUMBER
81 ,p_mode IN VARCHAR2 DEFAULT 'C'
82 ,p_partial_flag IN VARCHAR2 DEFAULT 'N'
83 ,p_reference1 IN VARCHAR2 DEFAULT NULL
84 ,p_reference2 IN VARCHAR2 DEFAULT NULL
85 ,p_reference3 IN VARCHAR2 DEFAULT NULL
86 ,x_return_status OUT NOCOPY VARCHAR2
87 ,x_error_msg OUT NOCOPY VARCHAR2
88 ,x_error_stage OUT NOCOPY VARCHAR2
89 ) RETURN BOOLEAN ;
90
91 -- This is an overloaded api in turn calls the main fund check function
92 PROCEDURE pa_funds_check
93 (p_calling_module IN VARCHAR2
94 ,p_set_of_book_id IN NUMBER
95 ,p_packet_id IN NUMBER
96 ,p_mode IN VARCHAR2 DEFAULT 'C'
97 ,p_partial_flag IN VARCHAR2 DEFAULT 'N'
98 ,p_reference1 IN VARCHAR2 DEFAULT NULL
99 ,p_reference2 IN VARCHAR2 DEFAULT NULL
100 ,x_return_status OUT NOCOPY VARCHAR2
101 ,x_error_msg OUT NOCOPY VARCHAR2
102 ,x_error_stage OUT NOCOPY VARCHAR2
103 ) ;
104 /*---------------------------------------------------------------------------------------------------------+
105 -- This is the Tie back api which updates the status of pa_bc_packets table after confirming the funds checking
106 -- status of GL / Contract Commitments
107 --Parameters:
108 -- P_packet_id : Packet Identifier of the funds check process
109 -- P_mode :Funds Checker Operation Mode
110 -- R - Reserve Default
111 -- B - Base line
112 -- P_calling_module :This holds the info of budget type
113 -- GL --- Standard Default
114 -- CBC --- Contract Commitments
115 -- P_reference1 :This Param is not null in case of Contract Commitment
116 -- If P_ext_bdgt_type = CBC
117 -- This param holds the information of document type
118 -- P_reference2 = Igc_cc_interface.document_type
119 -- elsif p_mode = B then
120 -- P_reference1 = project_id
121 -- Else
122 -- P_reference1 = NULL;
123 -- End if;
124 -- P_reference2 :This Param is not null in case of Contract Commitment
125 -- If P_ext_bdgt_type = CBC
126 -- This param holds the information of document Header Id
127 -- P_reference2 = Igc_cc_interface.CC_HEADER_ID
128 -- elsif p_mode = B then
129 -- P_reference2 = budget_version_id
130 -- Else
131 -- P_reference2 = NULL;
132 -- End if;
133 -- p_partial_flag :Partial reservation flag
134 -- Y - partial mode
135 -- N - full Mode default
136 -- P_gl_cbc_return_code :The return status of the GL /CBC funds check process
137 ---------------------------------------------------------------------------------------------------------+ */
138
139 PROCEDURE PA_GL_CBC_CONFIRMATION
140 (p_calling_module IN VARCHAR2
141 ,p_packet_id IN NUMBER
142 ,p_mode IN VARCHAR2 DEFAULT 'C'
143 ,p_partial_flag IN VARCHAR2 DEFAULT 'N'
144 ,p_reference1 IN VARCHAR2 DEFAULT NULL -- doc type = 'CC'
145 ,p_reference2 IN VARCHAR2 DEFAULT NULL ---- CC_HEADER_ID
146 ,p_gl_cbc_return_code IN OUT NOCOPY VARCHAR2
147 ,x_return_status OUT NOCOPY VARCHAR2
148 ) ;
149
150 ---------------------------------------------------------------------------------------------------+
151 --This api is used to update the encumbrance balances and budget account balances after the
152 -- funds check process is complete and result of the funds check process is successful
153 -- this api will be called from Base line process during tie back process
154 ---------------------------------------------------------------------------------------------------+
155 PROCEDURE upd_bdgt_encum_bal(
156 p_packet_id IN NUMBER,
157 p_calling_module IN VARCHAR2,
158 p_mode IN VARCHAR2,
159 p_packet_status IN VARCHAR2,
160 x_return_status OUT NOCOPY VARCHAR2);
161
162 --------------------------------------------------------------------------------------------+
163 -- This api updates the status of bc packets based on the result code
164 -- and calling mode and partial flag
165 -- The valid status code values are
166 -- A - Approved
167 -- B - Base lined -- Intermediate status
168 -- R - Rejected
169 -- C - Checked -- Intermediate status
170 -- F - Failed Check
171 -- S - Passed Check
172 -- E - Error
173 -- T - Fatal
174 -- V - Vendor Invoice - Intermediate status to avoid sweeper to pick
175 -- L - Intermediate status for Expense report to liquidate but avoid sweeper to pick
176 -- if the calling module is BASELINE then use BULK FETCH AND BULK
177 -- update logic since the volume of records is more.
178 -----------------------------------------------------------------------------------------------+
179 PROCEDURE status_code_update (
180 p_calling_module IN VARCHAR2,
181 p_packet_id IN NUMBER,
182 p_mode IN VARCHAR2,
183 p_partial IN VARCHAR2 DEFAULT 'N',
184 p_packet_status IN VARCHAR2 DEFAULT 'S',
185 x_return_status OUT NOCOPY varchar2 );
186
187 -----------------------------------------------------------------------------------------------+
188 -- This procedure is the autonomous version of status_code_udpate. Basically, this procedure
189 -- will call status_code_update
190 -- main procedure status_code_update is being made non-autonomous
191 -----------------------------------------------------------------------------------------------+
192 PROCEDURE status_code_update_autonomous (
193 p_calling_module IN VARCHAR2,
194 p_packet_id IN NUMBER,
195 p_mode IN VARCHAR2,
196 p_partial IN VARCHAR2 DEFAULT 'N',
197 p_packet_status IN VARCHAR2 DEFAULT 'S',
198 x_return_status OUT NOCOPY varchar2 );
199
200 ----------------------------------------------------------------+
201 -- This api writes message to log file / buffer / dummy table
202 -- and initalizes the final out NOCOPY params with values
203 ----------------------------------------------------------------+
204 PROCEDURE log_message(
205 p_stage IN VARCHAR2 default null,
206 p_error_msg IN VARCHAR2 default null,
207 p_return_status IN varchar2 default null,
208 p_msg_token1 IN VARCHAR2 default null,
209 p_msg_token2 IN VARCHAR2 default null
210 ) ;
211
212 ------------------------------------------------------------------+
213 -- This api updates the result and status code in pa bc packets
214 -- whenever there is error while processing
215 -----------------------------------------------------------------+
216 PROCEDURE result_status_code_update
217 ( p_status_code IN VARCHAR2 default null
218 ,p_result_code IN VARCHAR2 default null
219 ,p_res_result_code IN VARCHAR2 default null
220 ,p_res_grp_result_code IN VARCHAR2 default null
221 ,p_task_result_code IN VARCHAR2 default null
222 ,p_top_task_result_code IN VARCHAR2 default null
223 ,p_project_result_code IN VARCHAR2 default null
224 ,p_proj_acct_result_code IN VARCHAR2 default null
225 ,p_bc_packet_id IN NUMBER default null
226 ,p_packet_id IN NUMBER ) ;
227
228 -------------------------------------------------------------------------+
229 -- This api checks whether the Invoice is coming after the
230 -- interface from projects if the invoice is already interfaced
231 -- from projects then donot derive burden components
232 -- if the invoice system_linkage function is 'VI' then
233 -- derive budget ccid, encum type id, etc and DONOT do funds check
234 -- mark the invoice as approved and donot create encum liqd
235 -- if the invoice system linkage func is 'ER' then
236 -- derive budget ccid, encum type id, etc and DONOT funds check
237 -- mark the invoice as approved and create encum liqd for raw only
238 -------------------------------------------------------------------------+
239 PROCEDURE is_ap_from_project
240 (p_packet_id IN NUMBER,
241 p_calling_module IN VARCHAR2,
242 x_return_status OUT NOCOPY VARCHAR2) ;
243
244 ----------------------------------------------------------------------------+
245 -- This api checks whether the project is of burden on same or different
246 -- expenditure items
247 ---------------------------------------------------------------------------+
248 FUNCTION check_bdn_on_sep_item(p_project_id In number) return varchar2;
249 PRAGMA RESTRICT_REFERENCES (check_bdn_on_sep_item, WNDS);
250
251 /* The following API is added to tie back the status code of the
252 * bc packets during the distribute vendor invoice adjustments
253 * This API will be called from PABCCSTB.pls package and pro*c
254 */
255 PROCEDURE tieback_pkt_status
256 (p_calling_module in varchar2
257 ,p_packet_id in number
258 ,p_partial_flag in varchar2 default 'N'
259 ,p_mode in varchar2 default 'R'
260 ,p_tieback_status in varchar2 default 'T' --'S' for Success, 'T' -- fatal Error
261 ,p_request_id in number
262 ,x_return_status OUT NOCOPY varchar2);
263
264 /* This api derives the resource list member id for the given packet id
265 * this api should be used only for project funds check records which are
266 * inserted into pa_bc_packets */
267 PROCEDURE DERIVE_RLMI
268 ( p_packet_id IN pa_bc_packets.packet_id%type,
269 p_mode IN varchar2,
270 p_sob IN NUMBER,
271 p_reference1 IN varchar2 default null,
272 p_reference2 IN varchar2 default null,
273 p_calling_module IN varchar2 default 'GL'
274 ) ;
275
276 /* This Api derives all the required funds check setup params required for projects funds check
277 * if the mode is ForcePass then the transactions will be marked as Pass
278 */
279 FUNCTION funds_check_setup
280 ( p_packet_id IN pa_bc_packets.packet_id%type,
281 p_mode IN varchar2,
282 p_sob IN NUMBER,
283 p_reference1 IN varchar2 default null,
284 p_reference2 IN varchar2 default null,
285 p_calling_module IN varchar2
286 ) return boolean ;
287
288
289 /* Function is_baseline_progress being made public as it will be accessed in
290 * pa_trx_import.tieback_fc_records - Bug 3981458
291 */
292
293 FUNCTION is_baseline_progress(p_project_id number)
294 return varchar2;
295
296 -- Bug 5354715 : This function is made public so that it can be used in other packages to check if the project is installed in this OU.
297 FUNCTION IS_PA_INSTALL_IN_OU RETURN VARCHAR2 ;
298
299 END PA_FUNDS_CONTROL_PKG;
[Home] [Help]