[Home] [Help]
PACKAGE BODY: APPS.PA_ONESTEP_STREAMLINE_PKG
Source
1 PACKAGE BODY PA_ONESTEP_STREAMLINE_PKG AS
2 /* $Header: PAOSTRMB.pls 120.5 2007/12/07 08:51:43 rdegala ship $ */
3 PROCEDURE update_request_state(tbl IN OUT NOCOPY strm_request_id_table)
4
5 IS
6
7 r_phase VARCHAR2(30);
8 r_status VARCHAR2(30);
9 v_phase VARCHAR2(30);
10 v_status VARCHAR2(30);
11 message VARCHAR2(240);
12 retval BOOLEAN;
13
14 BEGIN
15
16 pa_debug.set_err_stack('update_request_table');
17 pa_debug.g_err_stage := 'Entering update_request_table()';
18 pa_debug.write_file('LOG',pa_debug.g_err_stage);
19
20 FOR i IN 1..tbl.count LOOP
21
22 IF (tbl(i).request_id <> 0) THEN
23
24 retval := fnd_concurrent.get_request_status(tbl(i).request_id, '','', r_phase, r_status, v_phase, v_status, message);
25
26 IF retval THEN
27
28 tbl(i).phase := v_phase;
29 tbl(i).status := v_status;
30 tbl(i).u_phase := r_phase;
31 tbl(i).u_status := r_status;
32
33 ELSE
34
35 pa_debug.g_err_stage := 'fnd_concurrent.get_request_status return FALSE for Request ID ' ||to_char(tbl(i).request_id);
36 pa_debug.write_file('LOG',pa_debug.g_err_stage);
37
38 END IF;
39
40 END IF;
41
42 END LOOP;
43
44 pa_debug.g_err_stage := 'Leaving update_request_table()';
45 pa_debug.write_file('LOG',pa_debug.g_err_stage);
46 pa_debug.reset_err_stack;
47
48 EXCEPTION
49 WHEN OTHERS THEN
50 pa_debug.write_file('LOG','update_request_table() exception: Others');
51 pa_debug.write_file('LOG',pa_debug.g_err_stack);
52 pa_debug.write_file('LOG',pa_debug.g_err_stage);
53 RAISE;
54
55 END update_request_state;
56
57 /* Added Acct_date for bug 6655250*/
58 PROCEDURE submit_strmln_request(tbl IN OUT NOCOPY strm_request_id_table,
59 strm_option IN VARCHAR2,
60 acct_date IN DATE)
61
62 IS
63
64 -- Cursor for selecting Interface Streamline Options
65 cursor c_strmln_opt (p_strm_opt IN VARCHAR2) IS
66 SELECT LOOKUP_code
67 FROM (SELECT LOOKUP_code
68 FROM pa_lookups
69 WHERE lookup_type = 'INTERFACE STREAMLINE OPTIONS'
70 AND lookup_code IN ('ETBC-ATBC', 'EBTC-ABTC', 'EBL-ABL', 'EINV-AINV', 'ELAB-ALAB', 'EPJ-APJ',
71 'EPC-APC', 'EDR-ADR', 'ESC-ASC', 'EUSG-AUSG', 'EWIP-AWIP'))
72 WHERE LOOKUP_CODE =
73 decode(p_strm_opt,'ALL-STRMLN',LOOKUP_CODE,p_strm_opt);
74
75 v_cnt NUMBER := 0;
76 v_request_id NUMBER := -1;
77 v_phase VARCHAR(30) := 'PENDING';
78 v_status VARCHAR2(30) := 'NORMAL';
79 L_org_id NUMBER := PA_MOAC_UTILS.GET_CURRENT_ORG_ID ;
80
81 BEGIN
82
83 pa_debug.set_err_stack('submit_strmln_request');
84 pa_debug.g_err_stage := 'Entering submit_strmln_request()';
85 pa_debug.write_file('LOG',pa_debug.g_err_stage);
86
87 FOR strmln_opt IN c_strmln_opt(strm_option) LOOP
88
89 /* Setting print options bug 2816916 */
90 l_result_print := FND_REQUEST.SET_PRINT_OPTIONS(l_printer,l_print_style,l_number_of_copies, l_save_op_flag_bool,'Y');
91
92 -- MOAC changes for R12
93 fnd_request.set_org_id(l_org_id);
94
95 -- Call fnd_request.submit_request
96 /* Modified for bug 6655250*/
97 v_request_id := fnd_request.submit_request('PA','PASTRM',NULL,NULL,
98 FALSE,strmln_opt.lookup_code,'','','','','','',g_debug_mode,FND_DATE.DATE_TO_CANONICAL(acct_date),
99 '','','','','','','','','','','','','','','','','','','','',
100 '','','','','','','','','','','','','','','','','','','','',
101 '','','','','','','','','','','','','','','','','','','','',
102 '','','','','','','','','','','','','','','','','','','','',
103 '','','','','','','','','','');
104 -- Insert request ID into PL/SQL table
105 commit;
106
107 IF (v_request_id = 0) THEN
108 v_phase:= 'COMPLETE';
109 v_status := 'ERROR';
110 END IF;
111
112 v_cnt := tbl.count+1;
113
114 SELECT v_request_id,strmln_opt.lookup_code,v_phase,v_status
115 INTO tbl(v_cnt).request_id,tbl(v_cnt).lookup_code,
116 tbl(v_cnt).phase,tbl(v_cnt).status
117 FROM dual;
118
119 pa_debug.g_err_stage := 'Request ID='||to_char(tbl(v_cnt).request_id)||
120 ', Streamline Option='||tbl(v_cnt).lookup_code;
121 pa_debug.write_file('LOG',pa_debug.g_err_stage);
122 pa_debug.g_err_stage := ' SOB ID='||to_char(tbl(v_cnt).sob_id)||
123 ', Phase='||tbl(v_cnt).phase||', Status='||tbl(v_cnt).status;
124 pa_debug.write_file('LOG',pa_debug.g_err_stage);
125
126 END LOOP;
127 pa_debug.g_err_stage := 'Leaving submit_strmln_request() with success';
128 pa_debug.write_file('LOG',pa_debug.g_err_stage);
129 pa_debug.reset_err_stack;
130
131 EXCEPTION
132 WHEN OTHERS THEN
133 pa_debug.write_file('LOG','submit_strmln_request() exception: Others');
134 pa_debug.write_file('LOG',pa_debug.g_err_stack);
135 pa_debug.write_file('LOG',pa_debug.g_err_stage);
136 RAISE;
137
138 END submit_strmln_request;
139
140 PROCEDURE show_final_status(tbl IN strm_request_id_table,
141 v_opt IN VARCHAR2,
142 errbuf IN OUT NOCOPY VARCHAR2)
143
144 IS
145
146 cursor c_strmln_opt (p_strm_opt IN VARCHAR2) IS
147 SELECT LOOKUP_code, meaning
148 FROM (SELECT LOOKUP_code, meaning
149 FROM pa_lookups
150 WHERE lookup_type = 'INTERFACE STREAMLINE OPTIONS'
151 AND lookup_code IN ('ETBC-ATBC', 'EBTC-ABTC', 'EBL-ABL', 'EINV-AINV', 'ELAB-ALAB', 'EPJ-APJ',
152 'EPC-APC', 'EDR-ADR', 'ESC-ASC', 'EUSG-AUSG', 'EWIP-AWIP'))
153 WHERE LOOKUP_CODE =
154 decode(p_strm_opt,'ALL-STRMLN',LOOKUP_CODE,p_strm_opt);
155
156 v_length NUMBER;
157
158 BEGIN
159
160 pa_debug.set_err_stack('show_final_status');
161 pa_debug.g_err_stage := 'Entering show_final_status()';
162 pa_debug.write_file('LOG',pa_debug.g_err_stage);
163
164 FOR strmln_opt IN c_strmln_opt(v_opt) LOOP
165 v_length := trunc((130-length(strmln_opt.meaning))/2+
166 length(strmln_opt.meaning));
167
168 FOR i IN 1..tbl.count LOOP
169 IF (strmln_opt.lookup_code = tbl(i).lookup_code) THEN
170 pa_debug.g_err_stage := 'Request='||tbl(i).request_id||
171 ', lookup-code='||tbl(i).lookup_code||', SOB ID='||
172 tbl(i).sob_id;
173 pa_debug.write_file('LOG',pa_debug.g_err_stage);
174
175 pa_debug.g_err_stage := ' u_phase='||tbl(i).u_phase||
176 ', u_status='||tbl(i).u_status||', PHASE='||tbl(i).phase||
177 ', STATUS='||tbl(i).status;
178 pa_debug.write_file('LOG',pa_debug.g_err_stage);
179
180 IF (tbl(i).status <> 'NORMAL') THEN
181 errbuf := errbuf||'request id '||to_char(tbl(i).request_id)||
182 ': '||tbl(i).phase||' '||tbl(i).status;
183 END IF;
184
185 END IF;
186 END LOOP;
187 END LOOP;
188
189 pa_debug.g_err_stage := 'Leaving show_final_status() with success';
190 pa_debug.write_file('LOG',pa_debug.g_err_stage);
191 pa_debug.reset_err_stack;
192
193 EXCEPTION
194 WHEN OTHERS THEN
195 pa_debug.write_file('LOG','show_final_status() exception: Others');
196 pa_debug.write_file('LOG',pa_debug.g_err_stack);
197 pa_debug.write_file('LOG',pa_debug.g_err_stage);
198 RAISE;
199
200 END show_final_status;
201
202 PROCEDURE PAOSTRM(
203 errbuf OUT NOCOPY VARCHAR2,
204 retcode OUT NOCOPY VARCHAR2,
205 debug_mode IN VARCHAR2 ,
206 strm_opt IN VARCHAR2,
207 acct_date IN VARCHAR2) IS
208
209 /* Added acct_date for bug 6655250*/
210 -- PL/SQL Record and Table to keep track of streamline process
211 strm_request_table strm_request_id_table;
212 v_user_id NUMBER;
213 v_application_id NUMBER;
214 v_psob_id NUMBEr(15);
215 v_org_id NUMBER(15);
216 v_responsibility_id fnd_user_resp_groups.responsibility_id%TYPE;
217 v_completed_r BOOLEAN := true; -- for checking completed primary requests
218 v_execute_flag BOOLEAN;
219 v_temp VARCHAR2(80);
220 /*******/
221 v_sleep_interval NUMBER := 60;
222 /*******/
223 v_acct_date date; /* Bug 6655250*/
224
225 BEGIN
226
227 retcode := '0';
228 g_debug_mode := debug_mode;
229 pa_debug.init_err_stack('PAOSTRM');
230 pa_debug.set_process('PLSQL', 'LOG', debug_mode);
231 pa_debug.g_err_stage := 'Entering PAOSTRM()';
232 pa_debug.write_file('LOG',pa_debug.g_err_stage);
233 pa_debug.g_err_stage := ' Current system time is '||
234 to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS');
235 pa_debug.write_file('LOG',pa_debug.g_err_stage);
236
237 pa_debug.g_err_stage := 'Current Acct Date is '||acct_date;
238 pa_debug.write_file('LOG',pa_debug.g_err_stage);
239 v_user_id := fnd_global.user_id;
240 v_application_id := fnd_global.resp_appl_id;
241
242 /* added for bug 2816916 */
243 l_request_id := FND_GLOBAL.CONC_REQUEST_ID();
244 l_result_print := FND_CONCURRENT.GET_REQUEST_PRINT_OPTIONS(l_request_id,l_number_of_copies, l_print_style,l_printer, l_save_output_flag);
245
246 IF upper(l_save_output_flag) = 'N' THEN
247 l_save_op_flag_bool := FALSE;
248 ELSE
249 l_save_op_flag_bool := TRUE;
250 END IF;
251
252 /* end of addition for bug 2816916 */
253 v_acct_date := NVL(FND_DATE.CANONICAL_TO_DATE(acct_date),sysdate); /* Bug 6655250*/
254
255 SELECT set_of_books_id, org_id
256 INTO v_psob_id, v_org_id
257 FROM pa_implementations;
258
259 -- Get Primary Responsibility ID
260 v_responsibility_id := fnd_global.resp_id;
261 -- Get Sleep Interval
262 v_sleep_interval :=
263 nvl(3*to_number(fnd_profile.value('PA_STRMLN_SLEEP_INTERVAL')), 180);
264
265 pa_debug.g_err_stage := 'User ID: '||to_char(v_user_id)||', PSOB ID: '||
266 to_char(v_psob_id)||', ORG ID: '||to_char(v_org_id);
267 pa_debug.write_file('LOG',pa_debug.g_err_stage);
268 pa_debug.g_err_stage := 'Strmln Opt: '||strm_opt||', Resp ID: '||
269 v_responsibility_id||', Sleep Interval: '||v_sleep_interval||' Date:'||v_acct_date;
270 pa_debug.write_file('LOG',pa_debug.g_err_stage);
271 -- Start process for primary set of books
272 submit_strmln_request(strm_request_table, strm_opt,v_acct_date);
273
274
275 pa_debug.g_err_stage := 'All ' ||strm_request_table.count||
276 ' processes submitted; check completion status.';
277 pa_debug.write_file('LOG',pa_debug.g_err_stage);
278
279
280 LOOP -- check if all primary and reporting processes have completed
281 v_completed_r := true;
282 dbms_lock.sleep(v_sleep_interval); -- sleep
283 update_request_state(strm_request_table);
284 FOR i IN 1..strm_request_table.count LOOP
285 IF (strm_request_table(i).phase <> 'COMPLETE') THEN
286 v_completed_r := false;
287 ELSIF (strm_request_table(i).phase = 'COMPLETE') AND
288 (strm_request_table(i).status = 'WARNING') AND
289 (retcode <> '-1') THEN
290
291 pa_debug.g_err_stage := strm_request_table(i).status||' '||
292 strm_request_table(i).phase;
293 pa_debug.write_file('LOG',pa_debug.g_err_stage);
294
295 retcode := '1';
296 ELSIF (strm_request_table(i).phase = 'COMPLETE') AND
297 (strm_request_table(i).status = 'DELETED') AND
298 (retcode <> '-1') THEN
299
300 pa_debug.g_err_stage := strm_request_table(i).status||' '||
301 strm_request_table(i).phase;
302 pa_debug.write_file('LOG',pa_debug.g_err_stage);
303
304 retcode := '1';
305 ELSIF (strm_request_table(i).phase = 'COMPLETE') AND
306 (strm_request_table(i).status <> 'NORMAL') THEN
307
308 pa_debug.g_err_stage := strm_request_table(i).status||' '||
309 strm_request_table(i).phase;
310 pa_debug.write_file('LOG',pa_debug.g_err_stage);
311
312 retcode := '-1';
313 END IF;
314 END LOOP;
315 EXIT WHEN (v_completed_r);
316 END LOOP;
317 show_final_status(strm_request_table, strm_opt, errbuf);
318 pa_debug.g_err_stage := 'Leaving PAOSTRM() with success';
319 pa_debug.write_file('LOG',pa_debug.g_err_stage);
320 pa_debug.reset_err_stack;
321
322 EXCEPTION
323 WHEN NO_DATA_FOUND THEN
324 pa_debug.write_file('LOG','PAOSTRM() exception: No data found');
325 pa_debug.write_file('LOG',pa_debug.g_err_stack);
326 pa_debug.write_file('LOG',pa_debug.g_err_stage);
327
328 WHEN OTHERS THEN
329 show_final_status(strm_request_table, strm_opt, errbuf);
330 errbuf := errbuf||'errbuf: '||sqlerrm;
331 pa_debug.write_file('LOG','PAOSTRM() exception: Others');
332 pa_debug.write_file('LOG',pa_debug.g_err_stack);
333 pa_debug.write_file('LOG',pa_debug.g_err_stage);
334
335 END PAOSTRM;
336
337 END PA_ONESTEP_STREAMLINE_PKG;