[Home] [Help]
PACKAGE BODY: APPS.GMS_UTILITY
Source
1 PACKAGE BODY GMS_UTILITY AS
2 /* $Header: gmsutilb.pls 120.3 2006/02/09 00:07:39 rshaik noship $ */
3
4 Function GET_AWARD_NUMBER(P_Award_Id IN NUMBER) RETURN VARCHAR2 IS
5
6 x_sql VARCHAR2(2000);
7 cur_select INTEGER;
8 X_Award_Number VARCHAR2(30);
9 X_Rows_Processed NUMBER;
10
11 Begin
12
13 X_sql := 'Select award_number '
14 ||' from gms_awards where '
15 ||' award_id = :Award_Id ';
16
17 cur_select := DBMS_SQL.OPEN_CURSOR;
18
19
20
21 DBMS_SQL.PARSE(cur_select,X_sql,dbms_sql.native);
22
23 DBMS_SQL.BIND_VARIABLE(cur_select,':Award_Id', P_Award_Id);
24
25 DBMS_SQL.DEFINE_COLUMN(cur_select, 1 , X_Award_Number, 15);
26
27 X_Rows_Processed := DBMS_SQL.EXECUTE(cur_select);
28
29
30 If DBMS_SQL.FETCH_ROWS(cur_select) > 0 then
31
32 DBMS_SQL.COLUMN_VALUE(cur_select,1 , X_Award_Number);
33 End If;
34
35
36 RETURN X_Award_Number;
37
38
39 End GET_AWARD_NUMBER;
40
41 -------------------------------------------------------------------------------
42
43 procedure gms_util_fck (x_sob_id IN NUMBER,
44 x_packet_id IN NUMBER,
45 x_fcmode IN VARCHAR2 DEFAULT 'C',
46 x_override IN BOOLEAN,
47 x_partial IN VARCHAR2 DEFAULT 'N',
48 x_user_id IN NUMBER DEFAULT NULL,
49 x_user_resp_id IN NUMBER DEFAULT NULL,
50 x_execute IN VARCHAR2 DEFAULT 'N',
51 x_gms_return_code IN OUT NOCOPY VARCHAR2,
52 x_gl_return_code IN OUT NOCOPY VARCHAR2
53 ) IS
54
55 x_gms_user_id varchar2(20) ;
56 x_uid varchar2(20) ;
57 x_uir_id varchar2(20) ;
58 x_gms_user_resp_id varchar2(20) ;
59 x_gms_packet_id varchar2(20) ;
60 x_gms_sob_id varchar2(20) ;
61 x_flag varchar2(1) := 'N' ;
62 x_override_flag VARCHAR2(1);
63
64 x_gms_e_code VARCHAR2(1);
65 x_gms_e_stage VARCHAR2(2000);
66
67 l_gms_return_code varchar2(30) ;
68 l_gl_return_code varchar2(30) ;
69
70 cursor_name INTEGER;
71 fck_processed INTEGER;
72 proc_stat VARCHAR2(1000);
73 ret boolean;
74 status varchar2(30);
75 industry varchar2(30);
76
77 x_error_message VARCHAR2(240);
78
79 BEGIN
80
81 ret := fnd_installation.get(8402, 8402, status, industry);
82 if (status = 'I' ) then
83 if (x_override) then
84 x_override_flag := 'Y';
85 else
86 x_override_flag := 'N';
87 end if;
88 l_gms_return_code := x_gms_return_code ;
89 l_gl_return_code := x_gl_return_code ;
90
91 cursor_name := dbms_sql.open_cursor;
92
93
94 proc_stat := 'declare gms_code_local VARCHAR2(1); '||
95 ' gms_e_code VARCHAR2(1); gl_code_local VARCHAR2(1); gms_e_stage VARCHAR2(1000); '||
96 ' begin if not gms_funds_control_pkg.gms_fck(' ||
97 ':GMS_SOB_ID, :GMS_PACKET_ID, :GMS_MODE,'||
98 ':GMS_OVERRIDE_FLAG, :GMS_PARTIAL,'||
99 ':GMS_USER_ID, :GMS_USER_RESP_ID,:GMS_FLAG,'||
100 ':GMS_CODE_LOCAL'||','||
101 ':GMS_E_CODE'||','||
102 ':GMS_E_STAGE'||') then '||
103 'update gl_bc_packets '||
104 'set status_code = ''T'''||
105 ' where packet_id = :GMS_PACKET_ID '||'; '||
106 'commit; '||
107 'end if; end;';
108
109
110 x_error_message := 'Error in PARSE ';
111 dbms_sql.parse(cursor_name,proc_stat,dbms_sql.native);
112
113
114 x_error_message := 'Error in BIND ';
115 DBMS_SQL.BIND_VARIABLE(cursor_name,':GMS_CODE_LOCAL', 'X', 1);
116 DBMS_SQL.BIND_VARIABLE(cursor_name,':GMS_E_CODE', 'E_X' ,10 );
117 DBMS_SQL.BIND_VARIABLE(cursor_name,':GMS_E_STAGE', 'E_STG', 1000);
118 x_gms_sob_id := to_char(x_sob_id) ;
119 x_gms_packet_id := to_char(x_packet_id) ;
120 x_uid := to_char(x_user_id) ;
121 x_uir_id := to_char(x_user_resp_id) ;
122 DBMS_SQL.BIND_VARIABLE(cursor_name,':GMS_SOB_ID',x_gms_sob_id,20) ;
123 DBMS_SQL.BIND_VARIABLE(cursor_name,':GMS_PACKET_ID',x_gms_packet_id,20) ;
124 DBMS_SQL.BIND_VARIABLE(cursor_name,':GMS_MODE',x_fcmode,2) ;
125 DBMS_SQL.BIND_VARIABLE(cursor_name,':GMS_OVERRIDE_FLAG',x_override_flag,2) ;
126 DBMS_SQL.BIND_VARIABLE(cursor_name,':GMS_FLAG',x_flag,2) ;
127 DBMS_SQL.BIND_VARIABLE(cursor_name,':GMS_PARTIAL',x_partial,2) ;
128 DBMS_SQL.BIND_VARIABLE(cursor_name,':GMS_USER_ID',x_uid,20) ;
129 DBMS_SQL.BIND_VARIABLE(cursor_name,':GMS_USER_RESP_ID',x_uir_id,20) ;
130
131
132 fck_processed := dbms_sql.execute(cursor_name);
133
134 DBMS_SQL.VARIABLE_VALUE(cursor_name,':GMS_CODE_LOCAL', L_gms_return_code);
135 DBMS_SQL.VARIABLE_VALUE(cursor_name,':GMS_E_CODE', X_gms_e_code);
136 DBMS_SQL.VARIABLE_VALUE(cursor_name,':GMS_E_STAGE',X_gms_e_stage);
137
138 dbms_sql.close_cursor(cursor_name);
139 if l_gms_return_code in ('F','T') then
140 if l_gms_return_code in ('F') then
141 update gl_bc_packets gl
142 set gl.status_code = 'R'
143 where gl.packet_id = x_packet_id;
144 end if;
145 if l_gms_return_code in ('T') then
146 update gl_bc_packets gl
147 set gl.status_code = 'T'
148 where gl.packet_id = x_packet_id;
149 end if;
150 l_gl_return_code := 'F';
151 commit;
152 end if;
153 end if; -- status
154
155 X_gms_return_code := L_gms_return_code ;
156 X_gl_return_code := l_gl_return_code ;
157
158 EXCEPTION
159 WHEN OTHERS THEN
160 dbms_sql.close_cursor(cursor_name);
161 X_gms_return_code := L_gms_return_code ;
162 X_gl_return_code := l_gl_return_code ;
163
164 end gms_util_fck;
165
166 --------------------------------------------------------------------------------------
167 --
168 -- BUG: 3523587 GMS funds checking integrations with AP autonomus funds checking.
169 -- ISSUE : gms funds control package synchronize the award distribution lines.
170 -- with autonomus funds checking ap distribution lines are not available or
171 -- not available to update award set id back to ap distribution lines.
172 --
173 -- RESOLUTION :
174 -- Create award distribution lines with the same award set id but higher
175 -- distribution line number. This is done before gms funds checking.
176 -- After gms funds checking award distribution lines with hight line
177 -- numbers are deleted.
178 --
179 -- R12 Fundscheck Management uptake: In R12, AP/PO/REQ will no longer be saving
180 -- data before calling fundscheck and fundscheck logic of accessing AP/PO/REQ
181 -- is modified.With new architecture REMOVE_DUPLICATE_ADLS and
182 -- CREATE_DUPLICATE_ADLS are obsolete as these scenarios will no more be reproducible.
183 --------------------------------------------------------------------------------------
184 -- BUG: 3517362 forward port funds check related changes.
185 -- Obsoleted PROCEDURE REMOVE_DUPLICATE_ADLS( p_packet_id IN NUMBER )
186 -- Obsoleted PROCEDURE CREATE_DUPLICATE_ADLS( p_packet_id IN NUMBER )
187 --------------------------------------------------------------------------------------
188 procedure gms_util_pc_fck (x_sob_id IN NUMBER,
189 x_packet_id IN NUMBER,
190 x_fcmode IN VARCHAR2 DEFAULT 'C',
191 x_override IN VARCHAR2 DEFAULT 'N',
192 x_partial IN VARCHAR2 DEFAULT 'N',
193 x_user_id IN NUMBER DEFAULT NULL,
194 x_user_resp_id IN NUMBER DEFAULT NULL,
195 x_execute IN VARCHAR2 DEFAULT 'N',
196 x_gms_return_code IN OUT NOCOPY VARCHAR2
197 ) IS
198
199
200 x_gms_e_code VARCHAR2(1);
201 x_gms_e_stage VARCHAR2(2000);
202 cursor_name INTEGER;
203 fck_processed INTEGER;
204 proc_stat VARCHAR2(1000);
205
206 x_flag varchar2(1) := 'N' ;
207 x_gms_sob_id varchar2(20) ;
208 x_gms_packet_id varchar2(20) ;
209 x_uid varchar2(20) ;
210 x_uir_id varchar2(20) ;
211
212
213 ret boolean;
214 status varchar2(30);
215 industry varchar2(30);
216
217 x_error_message VARCHAR2(240);
218 l_mode varchar2(1) ;
219 l_gms_return_code varchar2(3) ;
220
221 BEGIN
222
223 l_gms_return_code := x_gms_return_code ;
224
225 ret := fnd_installation.get(8402, 8402, status, industry);
226 if (status = 'I' ) then
227
228 ------------------------------------------------------ --1472633
229 --For mode coming from PO,REQ
230 ------------------------------------------------------
231 if x_fcmode in ('A','F') then
232 l_mode := 'R';
233 else
234 l_mode := x_fcmode;
235 end if;
236 ------------------------------------------------------
237 cursor_name := dbms_sql.open_cursor;
238
239
240 proc_stat := 'declare gms_code_local VARCHAR2(1); '||
241 ' gms_e_code VARCHAR2(1); gms_e_stage VARCHAR2(1000); '||
242 ' begin if not gms_funds_control_pkg.gms_fck(' ||
243 ':GMS_SOB_ID, :GMS_PACKET_ID, :GMS_FCMODE,'||
244 ':GMS_OVERRIDE,:GMS_PARTIAL,'||
245 ':GMS_USER_ID, :GMS_USER_RESP_ID, :GMS_FLAG,'||
246 ':GMS_CODE_LOCAL'||','||
247 ':GMS_E_CODE'||','||
248 ':GMS_E_STAGE'||') then '||
249 ':gms_code_local := ''T''; '||
250 'end if; end;';
251
252
253 x_error_message := 'Error in PARSE ';
254 dbms_sql.parse(cursor_name,proc_stat,dbms_sql.native);
255
256 --
257 -- BUG 3523587
258 -- GMS funds checking integrations with ap autonomous funds checking code.
259 --
260 -- BUG: 3517362 forward port funds check related changes.
261 -- R12 Fundscheck Management uptake: obsoleted procedure call
262 -- create_duplicate_adls(x_packet_id) ;
263
264 x_error_message := 'Error in BIND ';
265 DBMS_SQL.BIND_VARIABLE(cursor_name,':GMS_CODE_LOCAL', 'X', 1);
266 DBMS_SQL.BIND_VARIABLE(cursor_name,':GMS_E_CODE', 'E_X' ,10 );
267 DBMS_SQL.BIND_VARIABLE(cursor_name,':GMS_E_STAGE', 'E_STG', 1000);
268 x_gms_sob_id := to_char(x_sob_id) ;
269 x_gms_packet_id := to_char(x_packet_id) ;
270 x_uid := to_char(x_user_id) ;
271 x_uir_id := to_char(x_user_resp_id) ;
272 DBMS_SQL.BIND_VARIABLE(cursor_name,':GMS_SOB_ID',x_gms_sob_id,20);
273 DBMS_SQL.BIND_VARIABLE(cursor_name,':GMS_PACKET_ID',x_gms_packet_id,20) ;
274 DBMS_SQL.BIND_VARIABLE(cursor_name,':GMS_FCMODE',l_mode,2) ;
275 DBMS_SQL.BIND_VARIABLE(cursor_name,':GMS_OVERRIDE',x_OVERRIDE,2) ;
276 DBMS_SQL.BIND_VARIABLE(cursor_name,':GMS_FLAG',x_flag,2) ;
277 DBMS_SQL.BIND_VARIABLE(cursor_name,':GMS_PARTIAL',x_partial,2) ;
278 DBMS_SQL.BIND_VARIABLE(cursor_name,':GMS_USER_ID',x_uid,20) ;
279 DBMS_SQL.BIND_VARIABLE(cursor_name,':GMS_USER_RESP_ID',x_uir_id,20) ;
280
281 fck_processed := dbms_sql.execute(cursor_name);
282
283 DBMS_SQL.VARIABLE_VALUE(cursor_name,':GMS_CODE_LOCAL', l_gms_return_code);
284 DBMS_SQL.VARIABLE_VALUE(cursor_name,':GMS_E_CODE', X_gms_e_code);
285 DBMS_SQL.VARIABLE_VALUE(cursor_name,':GMS_E_STAGE',X_gms_e_stage);
286
287
288
289 dbms_sql.close_cursor(cursor_name);
290 --
291 -- BUG 3523587
292 -- GMS funds checking integrations with ap autonomous funds checking code.
293 --
294 -- BUG: 3517362 forward port funds check related changes.
295 -- R12 Fundscheck Management uptake: obsoleted procedure call
296 --remove_duplicate_adls(x_packet_id) ;
297 COMMIT ;
298 end if; -- status
299
300 x_gms_return_code := l_gms_return_code ;
301
302 EXCEPTION
303 WHEN OTHERS THEN
304 l_gms_return_code := 'T';
305 dbms_sql.close_cursor(cursor_name);
306
307 l_gms_return_code := 'T';
308
309 x_gms_e_stage := substr('gms_utility.gms_util_pc_fck:'||SQLCODE||':'||SQLERRM,1,2000);
310
311 -- Bug 3416571
312 -- Comment out ,fc_error_message to resolve 1153 compatibility.
313 -- bug 3425948 uncomment out fc_error_message
314 update gms_bc_packets
315 set status_code = 'T' ,
316 result_code = 'F89' , fc_error_message = x_gms_e_stage
317 where packet_id = x_packet_id;
318
319 If l_mode = 'C' then
320
321 delete gms_bc_packet_arrival_order
322 where packet_id = x_packet_id;
323
324 End if;
325 x_gms_return_code := l_gms_return_code ;
326 UPDATE gl_bc_packets SET
327 result_code = DECODE (NVL (SUBSTR (result_code, 1, 1), 'P'),'P', 'F71',result_code)
328 WHERE packet_id = x_packet_id;
329
330 --
331 -- BUG 3523587
332 -- GMS funds checking integrations with ap autonomous funds checking code.
333 --
334 -- R12 Fundscheck Management uptake: obsoleted procedure call
335 --remove_duplicate_adls(x_packet_id) ;
336 COMMIT ;
337 end gms_util_pc_fck;
338 ------------------------------------------------------------------------------------------------
339
340 procedure gms_util_gl_return_code(x_packet_id IN NUMBER,
341 x_mode IN VARCHAR2,
342 x_gl_return_code IN OUT NOCOPY VARCHAR2,
343 x_gms_return_code IN VARCHAR2,
344 x_partial_resv_flag IN VARCHAR2
345 ) IS
346
347
348 gms_e_code VARCHAR2(1);
349 gms_e_stage VARCHAR2(2000);
350 cursor_name INTEGER;
351 stat_processed INTEGER;
352 proc_stat VARCHAR2(1000);
353 x_gms_packet_id varchar2(20) ;
354 ret boolean;
355 status varchar2(30);
356 industry varchar2(30);
357 l_mode varchar2(1) ;
358 l_gl_return_code varchar2(1) ;
359 l_new_api varchar2(1) ;
360
361 CURSOR c_new_api IS
362 SELECT 'Y'
363 FROM dual
364 WHERE EXISTS ( SELECT 1
365 FROM gl_bc_packets gl_pkt
366 WHERE gl_pkt.packet_id = x_packet_id
367 AND gl_pkt.template_id is NULL
368 AND exists (select 1
369 from gms_bc_packets gms_pkt
370 where gms_pkt.packet_id = x_packet_id
371 AND gms_pkt.document_type IN ('AP','PO','REQ')
372 AND gms_pkt.source_event_id = gl_pkt.event_id )
373 ) ;
374
375 BEGIN
376
377 l_gl_return_code := x_gl_return_code ; -- Bug 3017422 : Passed the correct parameter
378 ret := fnd_installation.get(8402, 8402, status, industry);
379
380 IF (status = 'I' ) then
381
382 -- =====================================================
383 -- BUG: 3416573
384 -- GMS_GL_RETURN_CODE API for autonomous funds checking.
385 -- =====================================================
386 l_new_api := 'N' ;
387 OPEN c_new_api ;
388 FETCH c_new_api into l_new_api ;
389 CLOSE c_new_api ;
390
391 ------------------------------------------------------
392 --For mode coming from PO,REQ
393 ------------------------------------------------------
394 l_mode := x_mode;
395 if x_mode in ('A','F') then
396 l_mode := 'R';
397 end if;
398
399 -- ========================================================
400 -- BUG: 3416573
401 -- GMS_GL_RETURN_CODE API for autonomous funds checking.
402 -- Call gms_funds_posting_pkg.gms_gl_return_code for
403 -- Requisition,PO and AP. The new api was added to resolve
404 -- the commit issue in gms_gl_return_code.
405 -- =========================================================
406 -- BUG: 3517362 forward port funds check related changes.
407 IF NVL(l_new_api,'N') = 'Y' THEN
408 proc_stat := ' declare x_gl_return_code_local varchar2(1);
409 gms_e_code varchar2(1);
410 gms_e_stage varchar2(1000); '||
411 'begin
412 gms_funds_posting_pkg.gms_gl_return_code('||
413 ':gms_e_code,:gms_e_stage,:x_gl_return_code_local,
414 :GMS_PACKET_ID,:GMS_mode,
415 :GMS_RETURN_CODE,'||
416 ':GMS_PARTIAL_RESV_FLAG);
417 end;';
418 ELSE
419 proc_stat := ' declare x_gl_return_code_local varchar2(1);
420 gms_e_code varchar2(1);
421 gms_e_stage varchar2(1000); '||
422 'begin
423 gms_funds_control_pkg.gms_gl_return_code('||
424 ':GMS_PACKET_ID,:GMS_mode, :x_gl_return_code_local, :GMS_RETURN_CODE,'||
425 ':GMS_PARTIAL_RESV_FLAG,'||
426 ':gms_e_code,:gms_e_stage);
427 end;';
428
429 END IF ;
430
431 cursor_name := dbms_sql.open_cursor;
432 dbms_sql.parse(cursor_name,proc_stat,dbms_sql.native);
433 x_gms_packet_id := to_char(x_packet_id) ;
434 DBMS_SQL.BIND_VARIABLE(cursor_name,':x_gl_return_code_local', l_gl_return_code, 1);
435 DBMS_SQL.BIND_VARIABLE(cursor_name,':GMS_PACKET_ID', x_gms_packet_id, 20);
436 DBMS_SQL.BIND_VARIABLE(cursor_name,':GMS_MODE', l_MODE, 2);
437 DBMS_SQL.BIND_VARIABLE(cursor_name,':GMS_RETURN_CODE', x_gms_return_code, 2);
438 DBMS_SQL.BIND_VARIABLE(cursor_name,':GMS_PARTIAL_RESV_FLAG', x_partial_resv_flag, 2);
439 DBMS_SQL.BIND_VARIABLE(cursor_name,':GMS_E_CODE', 'E_X' ,10 );
440 DBMS_SQL.BIND_VARIABLE(cursor_name,':GMS_E_STAGE', 'E_STG', 1000);
441 stat_processed := dbms_sql.execute(cursor_name);
442 DBMS_SQL.VARIABLE_VALUE(cursor_name,':x_gl_return_code_local', l_gl_return_code);
443 dbms_sql.close_cursor(cursor_name);
444 end if;
445 x_gl_return_code := l_gl_return_code ;
446
447 EXCEPTION
448 WHEN OTHERS THEN
449 dbms_sql.close_cursor(cursor_name);
450
451 l_gl_return_code := 'Z';
452
453 gms_e_stage := substr('gms_utility.gms_util_gl_return_code:'||SQLCODE||':'||SQLERRM,1,2000);
454
455 -- Bug : 2557041 - Changed F00 to F71 , F00 was a generic code, F71 result code is for unexpected error
456
457 UPDATE gl_bc_packets gl
458 SET gl.result_code = DECODE ( NVL (SUBSTR (result_code, 1, 1), 'P'),
459 'P', 'F71',result_code
460 )
461 WHERE gl.packet_id = x_packet_id;
462
463 -- Bug 3416571
464 -- Comment out ,fc_error_message to resolve 1153 compatibility.
465 --
466 UPDATE gms_bc_packets gms
467 SET gms.status_code = 'T',
468 gms.result_code = DECODE (NVL (SUBSTR (result_code, 1, 1), 'P'),
469 'P', 'F68',result_code)
470 ,fc_error_message = gms_e_stage
471 WHERE gms.packet_id = x_packet_id;
472
473 x_gl_return_code := l_gl_return_code ;
474
475 END gms_util_gl_return_code;
476
477 END GMS_UTILITY ;