1 PACKAGE BODY GL_FUNDS_CHECKER_PKG AS
2 /* $Header: glfbcfcb.pls 120.30 2005/07/08 14:58:14 tpradhan ship $ */
3
4 -- Types :
5 --
6
7 -- SegNamArray contains all Active Segments
8
9 TYPE SegNamArray IS TABLE OF VARCHAR2(9) INDEX BY BINARY_INTEGER;
10
11 -- TokNameArray contains names of all tokens
12
13 TYPE TokNameArray IS TABLE OF VARCHAR2(15) INDEX BY BINARY_INTEGER;
14
15 -- TokValArray contains values for all tokens
16
17 TYPE TokValArray IS TABLE OF VARCHAR2(1000) INDEX BY BINARY_INTEGER;
18
19
20 -- Constants :
21 -- This is used as a delimiter in the Debug Info String
22
23 g_delim CONSTANT VARCHAR2(1) := '[';
24
25
26 -- Private Global Variables :
27 --
28
29 -- Packet ID for the Packet being processed
30
31 g_packet_id gl_bc_packets.packet_id%TYPE;
32
33
34 -- Funds Check Return Code for the Packet processed. Valid Return Codes
35 -- are : 'S' for Success, 'A' for Advisory, 'F' for Failure, 'P' for Partial,
36 -- and 'T' for Fatal
37
38 g_return_code gl_bc_packets.result_code%TYPE;
39
40 -- Message Token Name
41
42 msg_tok_names TokNameArray;
43
44 -- Message Token Value
45
46 msg_tok_val TokValArray;
47
48 -- Number of Message Tokens
49
50 g_no_msg_tokens NUMBER;
51
52 -- Debug String
53
54 g_dbug VARCHAR2(2000);
55
56 /* ----------------------------------------------------------------------- */
57 /* */
58 /* Private Function Definition */
59 /* */
60 /* ----------------------------------------------------------------------- */
61
62 -- Bug 4481546, commented out the function glxfuf
63 /*
64 FUNCTION glxfuf(p_sobid IN NUMBER,
65 p_packetid IN NUMBER,
66 p_mode IN VARCHAR2,
67 p_partial_resv_flag IN VARCHAR2,
68 p_override IN VARCHAR2,
69 p_conc_flag IN VARCHAR2,
70 p_user_id IN NUMBER,
71 p_user_resp_id IN NUMBER) RETURN BOOLEAN;
72 */
73
74 PROCEDURE message_token(tokname IN VARCHAR2,
75 tokval IN VARCHAR2);
76
77
78 PROCEDURE add_message(appname IN VARCHAR2,
79 msgname IN VARCHAR2);
80
81
82 /* ------------------------------------------------------------------------- */
83 /* */
84 /* Funds Check API for any process that needs to perform Funds Check and/or */
85 /* Funds Reservation */
86 /* */
87 /* This routine returns TRUE if successful; otherwise, it returns FALSE */
88 /* */
89 /* In case of failure, this routine will populate the global Message Stack */
90 /* using FND_MESSAGE. The calling routine will retrieve the message from */
91 /* the Stack */
92 /* */
93 /* When invoked from a Concurrent Process, the calling process has to */
94 /* initialize values for User ID, User Responsibility ID, Calling */
95 /* Application ID and Login ID. These values should be initialized, in the */
96 /* Global Stack by invoking FND_GLOBAL, prior to calling Funds Checker */
97 /* */
98 /* External Packages which are being invoked include : */
99 /* */
100 /* FND_GLOBAL */
101 /* FND_PROFILE */
102 /* FND_INSTALLATION */
103 /* FND_MESSAGE */
104 /* FND_FLEX_EXT */
105 /* FND_FLEX_APIS */
106 /* */
107 /* GL Tables which are being used include : */
108 /* */
109 /* GL_BC_PACKETS */
110 /* GL_BC_PACKET_ARRIVAL_ORDER */
111 /* GL_BC_OPTIONS */
112 /* GL_BC_OPTION_DETAILS */
113 /* GL_BC_PERIOD_MAP */
114 /* GL_BC_DUAL */
115 /* GL_BC_DUAL2 */
116 /* GL_CONCURRENCY_CONTROL */
117 /* GL_PERIOD_STATUSES */
118 /* GL_LOOKUPS */
119 /* GL_USSGL_TRANSACTION_CODES */
120 /* GL_USSGL_ACCOUNT_PAIRS */
121 /* GL_BALANCES */
122 /* GL_BUDGETS */
123 /* GL_BUDGET_VERSIONS */
124 /* GL_BUDGET_ASSIGNMENTS */
125 /* GL_BUDGET_PERIOD_RANGES */
126 /* GL_JE_BATCHES */
127 /* GL_JE_HEADERS */
128 /* GL_JE_LINES */
129 /* GL_SETS_OF_BOOKS */
130 /* GL_CODE_COMBINATIONS */
131 /* GL_ACCOUNT_HIERARCHIES */
132 /* */
133 /* AOL Tables which are being used include : */
134 /* */
135 /* FND_USER */
136 /* FND_APPLICATION */
137 /* FND_RESPONSIBILITY */
138 /* FND_PROFILE_OPTION_VALUES */
139 /* FND_PRODUCT_INSTALLATIONS */
140 /* */
141 /* ------------------------------------------------------------------------- */
142
143 -- Parameters :
144
145 -- p_sobid : Set of Books ID
146
147 -- p_packetid : Packet ID
148
149 -- p_mode : Funds Checker Operation Mode. Defaults to 'C' (Checking)
150
151 -- p_partial_resv_flag : Whether Partial Reservation is allowed for the
152 -- Packet. Defaults to 'N' (No)
153
154 -- p_override : Whether to Override in case of Funds Reservation failure
155 -- because of lack of Funds. Defaults to 'N' (No)
156
157 -- p_conc_flag : Whether invoked from a Concurrent Process. Defaults to
158 -- 'N' (No)
159
160 -- p_user_id : User ID for Override (from AP AutoApproval)
161
162 -- p_user_resp_id : User Responsibility ID for Override (from AP AutoApproval)
163
164 -- p_return_code : Return Status for the Packet
165
166 FUNCTION glxfck(p_sobid IN NUMBER,
167 p_packetid IN NUMBER,
168 p_mode IN VARCHAR2 DEFAULT 'C',
169 p_partial_resv_flag IN VARCHAR2 DEFAULT 'N',
170 p_override IN VARCHAR2 DEFAULT 'N',
171 p_conc_flag IN VARCHAR2 DEFAULT 'N',
172 p_user_id IN NUMBER DEFAULT NULL,
173 p_user_resp_id IN NUMBER DEFAULT NULL,
174 p_return_code OUT NOCOPY VARCHAR2) RETURN BOOLEAN IS
175
176 others EXCEPTION;
177
178 BEGIN
179
180 g_packet_id := p_packetid;
181
182 -- Bug 4481546, added NOT to the condition below
183 IF NOT PSA_FUNDS_CHECKER_PKG.glxfck
184 (p_sobid ,
185 p_packetid ,
186 p_mode ,
187 p_partial_resv_flag ,
188 p_override ,
189 p_conc_flag ,
190 p_user_id ,
191 p_user_resp_id ,
192 p_return_code ) then
193
194 goto fatal_error;
195
196 END IF;
197
198 p_return_code := g_return_code;
199
200 return(TRUE);
201
202
203 <<fatal_error>>
204 g_dbug := g_dbug ||
205 'Fatal Error' || g_delim;
206 /*
207 if not glxfuf
208 (p_sobid ,
209 p_packetid ,
210 p_mode ,
211 p_partial_resv_flag ,
212 p_override ,
213 p_conc_flag ,
214 p_user_id ,
215 p_user_resp_id ) then
216 raise others;
217
218 end if;
219 */
220 return(FALSE);
221
222
223 EXCEPTION
224
225 WHEN OTHERS THEN
226
227 message_token('PROCEDURE', 'Funds Checker');
228 message_token('EVENT', SQLERRM);
229 add_message('SQLGL', 'GL_UNHANDLED_EXCEPTION');
230
231 return(FALSE);
232
233 END glxfck;
234
235 /* ------------------------------------------------------------------------- */
236
237 -- Purge Packets after Funds Check
238
239 -- This Module provides a way for any external Funds Check implementation
240 -- to rollback Funds Reserved after the Funds Checker call. This must be
241 -- called before any commit that would otherwise confirm the final Funds
242 -- Check Status of the packet
243
244 -- This Module deletes all transaction lines of a packet in gl_bc_packets and
245 -- the associated Arrival Order record in gl_bc_packet_arrival_order
246
247 -- This Module also deletes the corresponding records for a packet being
248 -- Unreserved
249
250 -- This Function is invoked by any Module that needs to purge all packet
251 -- related information after the Funds Checker call
252
253
254 -- Parameters :
255
256 -- p_packetid : Packet ID
257
258 -- p_packetid_ursvd : Unreservation Packet ID. Defaults to 0
259
260 PROCEDURE glxfpp(p_packetid IN NUMBER,
261 p_packetid_ursvd IN NUMBER DEFAULT 0) IS
262
263 BEGIN
264
265 -- Delete Packet Transactions
266
267 delete from gl_bc_packets bp
268 where bp.packet_id in (p_packetid, p_packetid_ursvd);
269
270
271 -- Delete Packet Arrival Order Record
272
273 delete from gl_bc_packet_arrival_order ao
274 where ao.packet_id in (p_packetid, p_packetid_ursvd);
275
276
277 EXCEPTION
278
279 WHEN OTHERS THEN
280
281 message_token('PROCEDURE', 'Funds Checker');
282 message_token('EVENT', SQLERRM);
283 add_message('SQLGL', 'GL_UNHANDLED_EXCEPTION');
284
285 END glxfpp;
286
287 /* ------------------------------------------------------------------------- */
288
289 -- Update Status Code for Transactions to Fatal
290
291 -- Updates Status Code for all transactions in the Packet to 'T'; it also
292 -- updates affect_funds_flag in gl_bc_packet_arrival_order to 'N' so that
293 -- the available Funds calculation of packets arriving later is not affected
294 -- in case an irrecoverable error halts Funds Check. SQLs for updating the
295 -- columns are not guaranteed to succeed in many drastic cases. However, this
296 -- step tries to ensure that the current packet does not affect the Funds
297 -- Available calculation for packets arriving later
298
299 -- The final cleanup is done by the Sweeper program, which deletes all packets
300 -- with Status 'T', as well as all packets with Status 'P' (Pending) which are
301 -- older than a specific (relatively long) time interval. This remedies for
302 -- cases where the update could not be done in this Module
303
304 -- Bug 4481546, commented out the function glxfuf since it should not be used.
305 /*
306 FUNCTION glxfuf(p_sobid IN NUMBER,
307 p_packetid IN NUMBER,
308 p_mode IN VARCHAR2,
309 p_partial_resv_flag IN VARCHAR2,
310 p_override IN VARCHAR2,
311 p_conc_flag IN VARCHAR2,
312 p_user_id IN NUMBER,
313 p_user_resp_id IN NUMBER) RETURN BOOLEAN IS
314
315 others EXCEPTION;
316 p_sql_err_msg VARCHAR2(200);
317
318 BEGIN
319
320 -- Update Status Code for the Packet Transactions
321
322 update gl_bc_packets bp
323 set bp.status_code = 'T'
324 where bp.packet_id = g_packet_id;
325
326 g_dbug := g_dbug ||
327 'Updated Status for ' || SQL%ROWCOUNT || ' Trans to Fatal' ||
328 g_delim;
329
330
331 -- Update Affect Funds Flag
332
333 update gl_bc_packet_arrival_order ao
334 set ao.affect_funds_flag = 'N'
335 where ao.packet_id = g_packet_id;
336
337 if PSA_FUNDS_CHECKER_PKG.glzfrs_public
338 ('Z' ,
339 p_sobid ,
340 p_packetid ,
341 p_mode ,
342 p_partial_resv_flag ,
343 p_override ,
344 p_conc_flag ,
345 p_user_id ,
346 p_user_resp_id ,
347 p_sql_err_msg ) then
348 raise others;
349 end if;
350
351 return(TRUE);
352
353
357
354 EXCEPTION
355
356 WHEN OTHERS THEN
358 message_token('PROCEDURE', 'Funds Checker');
359 message_token('EVENT', SQLERRM);
360 add_message('SQLGL', 'GL_UNHANDLED_EXCEPTION');
361
362 return(FALSE);
363
364 END glxfuf;
365 */
366 /* ------------------------------------------------------------------------- */
367
368 -- Add Token and Value to the Message Token array
369
370 PROCEDURE message_token(tokname IN VARCHAR2,
371 tokval IN VARCHAR2) IS
372
373 BEGIN
374
375 if g_no_msg_tokens is null then
376 g_no_msg_tokens := 1;
377 else
378 g_no_msg_tokens := g_no_msg_tokens + 1;
379 end if;
380
381 msg_tok_names(g_no_msg_tokens) := tokname;
382 msg_tok_val(g_no_msg_tokens) := tokval;
383
384 END message_token;
385
386 /* ----------------------------------------------------------------------- */
387
388 -- Sets the Message Stack
389
390 PROCEDURE add_message(appname IN VARCHAR2,
391 msgname IN VARCHAR2) IS
392
393 i BINARY_INTEGER;
394
395 BEGIN
396
397 if ((appname is not null) and
398 (msgname is not null)) then
399
400 FND_MESSAGE.SET_NAME(appname, msgname);
401
402 if g_no_msg_tokens is not null then
403
404 for i in 1..g_no_msg_tokens loop
405 FND_MESSAGE.SET_TOKEN(msg_tok_names(i), msg_tok_val(i));
406 end loop;
407
408 end if;
409
410 end if;
411
412 -- Clear Message Token stack
413
414 g_no_msg_tokens := 0;
415
416 END add_message;
417
418 /* ----------------------------------------------------------------------- */
419
420 -- Get Debug Information
421
422 -- This Module is used to retrieve Debug Information for Funds Checker. It
423 -- prints Debug Information when run as a Batch Process from SQL*Plus. For
424 -- the Debug Information to be printed on the Screen, the SQL*Plus parameter
425 -- 'Serveroutput' should be set to 'ON'
426
427 FUNCTION get_debug RETURN VARCHAR2 IS
428
429 BEGIN
430
431 return(g_dbug);
432
433 END get_debug;
434
435
436 /* ----------------------------------------------------------------------- */
437
438 END GL_FUNDS_CHECKER_PKG;
[Home] [Help]