1 PACKAGE BODY AP_CONC_PROG_PKG AS
2 /* $Header: apcpreqb.pls 120.3 2004/10/27 01:29:56 pjena noship $ */
3
4 -----------------------------------------------------------------------
5 -- Procedure pay_batch_requests_finished checks whether any of the
6 -- concurrent requests submitted for a payment batch are pending,
7 -- currently running or inactive, and sets X_finished_flag to TRUE if no
8 -- payment batch requests could run now and FALSE otherwise.
9 --
10 -- Drives off records in AP_CHECKRUN_CONC_PROCESSES which are loaded up
11 -- as each request is submitted. If all payment batch requests have been
12 -- completed, they are deleted from the table.
13 --
14 PROCEDURE pay_batch_requests_finished(X_batch_name IN VARCHAR2,
15 X_calling_sequence IN VARCHAR2,
16 X_finished_flag OUT NOCOPY BOOLEAN)
17 IS
18 X_request_id NUMBER;
19 X_tmp_flag BOOLEAN;
20 X_call_status BOOLEAN;
21 X_translated_phase VARCHAR2(80);
22 X_translated_status VARCHAR2(80);
23 X_phase VARCHAR2(80);
24 X_status VARCHAR2(80);
25 X_message VARCHAR2(255);
26 X_debug_info VARCHAR2(255);
27 X_curr_calling_sequence VARCHAR2(2000);
28
29 -------------------------------------------------------------------
30 -- Declare cursor to check payment batch requests
31 --
32 CURSOR requests_cursor IS
33 SELECT request_id
34 FROM ap_checkrun_conc_processes
35 WHERE checkrun_name = X_batch_name;
36
37 BEGIN
38 X_curr_calling_sequence := 'AP_CONC_PROG_PKG.PAY_BATCH_REQUESTS_FINISHED<-' ||
39 X_calling_sequence;
40 X_tmp_flag := TRUE;
41
42 X_debug_info := 'Open requests_cursor';
43 OPEN requests_cursor;
44
45 LOOP
46 X_debug_info := 'Fetch requests_cursor';
47 FETCH requests_cursor INTO X_request_id;
48
49 EXIT WHEN requests_cursor%NOTFOUND;
50
51 ---------------------------------------------------------------
52 -- Get payment batch request status and set X_finished_flag to
53 -- FALSE if request is PENDING, RUNNING or INACTIVE
54 --
55 X_debug_info := 'Get payment batch concurrent request status';
56
57 X_call_status := FND_CONCURRENT.GET_REQUEST_STATUS(X_request_id,
58 '',
59 '',
60 X_translated_phase,
61 X_translated_status,
62 X_phase,
63 X_status,
64 X_message);
65 IF (X_call_status = FALSE) THEN
66 APP_EXCEPTION.RAISE_EXCEPTION;
67 END IF;
68
69 IF (X_phase IN ('PENDING','RUNNING','INACTIVE')) THEN
70 X_tmp_flag := FALSE;
71 EXIT;
72 END IF;
73
74 END LOOP;
75
76 X_debug_info := 'Close requests_cursor';
77 CLOSE requests_cursor;
78
79 -------------------------------------------------------------------
80 -- Delete payment batch requests from table if all have finished
81 --
82 IF (X_tmp_flag = TRUE) THEN
83 X_debug_info := 'Delete requests from ap_checkrun_conc_processes';
84
85 DELETE FROM ap_checkrun_conc_processes
86 WHERE checkrun_name = X_batch_name;
87
88 COMMIT;
89 END IF;
90
91 X_finished_flag := X_tmp_flag;
92
93 EXCEPTION
94 WHEN OTHERS THEN
95 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
96 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
97 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',X_curr_calling_sequence);
98 FND_MESSAGE.SET_TOKEN('PARAMETERS','CHECKRUN_NAME = '||X_batch_name);
99 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',X_debug_info);
100 APP_EXCEPTION.RAISE_EXCEPTION;
101
102 END pay_batch_requests_finished;
103
104
105 -----------------------------------------------------------------------
106 -- Procedure requests_finished checks if a particular concurrent program
107 -- has any pending or running requests and sets X_finished_flag to FALSE
108 -- if requests are outstanding and TRUE otherwise.
109 --
110 -- NOTE: Sets X_finished_flag to FALSE if program is not registered.
111 --
112 PROCEDURE requests_finished(X_program_name IN VARCHAR2,
113 X_application_name IN VARCHAR2,
114 X_calling_sequence IN VARCHAR2,
115 X_finished_flag OUT NOCOPY BOOLEAN)
116 IS
117 X_program_id NUMBER;
118 X_application_id NUMBER;
119 X_request_id NUMBER;
120 X_call_status BOOLEAN;
121 X_translated_phase VARCHAR2(30);
122 X_translated_status VARCHAR2(30);
123 X_phase VARCHAR2(30);
124 X_status VARCHAR2(30);
125 X_message VARCHAR2(240);
126 X_debug_info VARCHAR2(240);
127 X_curr_calling_sequence VARCHAR2(2000);
128
129 -------------------------------------------------------------------
130 -- Declare cursor to check concurrent program requests
131 --
132 CURSOR requests_cursor IS
133 SELECT request_id
134 FROM fnd_concurrent_requests
135 WHERE program_application_id = X_application_id
136 AND concurrent_program_id = X_program_id;
137
138 BEGIN
139 X_curr_calling_sequence := 'AP_CONC_PROG_PKG.REQUESTS_FINISHED<-' ||
140 X_calling_sequence;
141 X_finished_flag := TRUE;
142
143 X_debug_info := 'Get concurrent program_id and application_id';
144
145 SELECT FCP.concurrent_program_id,
146 FCP.application_id
147 INTO X_program_id,
148 X_application_id
149 FROM fnd_concurrent_programs FCP, fnd_application FA
150 WHERE FA.application_short_name = X_application_name
151 AND FA.application_id = FCP.application_id
152 AND FCP.concurrent_program_name = X_program_name;
153
154 X_debug_info := 'Open requests_cursor';
155 OPEN requests_cursor;
156
157 LOOP
158 X_debug_info := 'Fetch requests_cursor';
159 FETCH requests_cursor INTO X_request_id;
160
161 EXIT WHEN requests_cursor%NOTFOUND;
162
163 ---------------------------------------------------------------
164 -- Get concurrent request status and set X_finished_flag to FALSE
165 -- if request is PENDING or RUNNING
166 --
167 X_debug_info := 'Get concurrent request status';
168
169 X_call_status := FND_CONCURRENT.GET_REQUEST_STATUS(X_request_id,
170 '',
171 '',
172 X_translated_phase,
173 X_translated_status,
174 X_phase,
175 X_status,
176 X_message);
177 IF (X_call_status = FALSE) THEN
178 APP_EXCEPTION.RAISE_EXCEPTION;
179 END IF;
180
181 IF (X_phase IN ('PENDING','RUNNING')) THEN
182 X_finished_flag := FALSE;
183 EXIT;
184 END IF;
185
186 END LOOP;
187
188 X_debug_info := 'Close requests_cursor';
189 CLOSE requests_cursor;
190
191 EXCEPTION
192 WHEN NO_DATA_FOUND THEN
193 X_finished_flag := FALSE;
194 WHEN OTHERS THEN
195 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
196 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
197 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',X_curr_calling_sequence);
198 FND_MESSAGE.SET_TOKEN('PARAMETERS','PROGRAM_NAME = ' ||
199 X_program_name ||
200 ', APPLICATION_NAME = ' ||
201 X_application_name);
202 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',X_debug_info);
203 APP_EXCEPTION.RAISE_EXCEPTION;
204
205 END requests_finished;
206
207
208 -----------------------------------------------------------------------
209 -- Procedure execution_method returns the execution method code for a
210 -- particular concurrent program. This is used by formats that submit
211 -- the Format Payments program of the payment batch. Since users can
212 -- write their own programs in any language, we need to know what type
213 -- of program it is registered as in order to know how to submit the
214 -- concurrent request.
215 --
216 -- Sets X_execution_method to R for rpts, P for srws, A for C programs,
217 -- and NULL if program is not registered.
218 --
219 PROCEDURE execution_method(X_program_name IN VARCHAR2,
220 X_calling_sequence IN VARCHAR2,
221 X_execution_method OUT NOCOPY VARCHAR2)
222 IS
223 X_debug_info VARCHAR2(240);
224 X_curr_calling_sequence VARCHAR2(2000);
225 BEGIN
226 X_curr_calling_sequence := 'AP_CONC_PROG_PKG.EXECUTION_METHOD' ||
227 X_calling_sequence;
228
229 X_debug_info := 'Get execution method code';
230
231 SELECT execution_method_code
232 INTO X_execution_method
233 FROM fnd_concurrent_programs
234 WHERE application_id = 200
235 AND concurrent_program_name = X_program_name;
236
237 EXCEPTION
238 WHEN NO_DATA_FOUND THEN
239 X_execution_method := NULL;
240 WHEN OTHERS THEN
241 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
242 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
243 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',X_curr_calling_sequence);
244 FND_MESSAGE.SET_TOKEN('PARAMETERS','PROGRAM_NAME = ' || X_program_name);
245 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',X_debug_info);
246 APP_EXCEPTION.RAISE_EXCEPTION;
247
248 END execution_method;
249
250 -----------------------------------------------------------------------
251 -- Procedure is_program_srs returns the srs_flag for a particular
252 -- concurrent program. This is used prior submitting concurrent
253 -- programs to determine whether or not a programs srs flag is set to
254 -- 'N'. If it is set to 'N', then parameters passed to program must
255 -- include token and "" around parameters with imbedded spaces.
256 -- If this is not done, then problems can occur with NT.
257 --
258 ------------------------------------------------------------------------
259 PROCEDURE IS_PROGRAM_SRS(X_program_name IN VARCHAR2,
260 X_calling_sequence IN VARCHAR2,
261 X_srs_flag OUT NOCOPY VARCHAR2)
262 IS
263 X_debug_info VARCHAR2(240);
264 X_curr_calling_sequence VARCHAR2(2000);
265 BEGIN
266 X_curr_calling_sequence := 'AP_CONC_PROG_PKG.IS_PROGRAM_SRS' ||
267 X_calling_sequence;
268
269 X_debug_info := 'Get SRS flag value';
270
271 SELECT srs_flag
272 INTO X_srs_flag
273 FROM fnd_concurrent_programs_vl
274 WHERE application_id = 200
275 AND concurrent_program_name = X_program_name;
276
277 EXCEPTION
278 WHEN NO_DATA_FOUND THEN
279 X_srs_flag := NULL;
280 WHEN OTHERS THEN
281 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
282 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
283 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',X_curr_calling_sequence);
284 FND_MESSAGE.SET_TOKEN('PARAMETERS','PROGRAM_NAME = ' || X_program_name);
285 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',X_debug_info);
286 APP_EXCEPTION.RAISE_EXCEPTION;
287
288 END IS_PROGRAM_SRS;
289
290
291 END AP_CONC_PROG_PKG;