1 PACKAGE BODY gme_reopen_step_pvt AS
2 /* $Header: GMEVROSB.pls 120.1 2005/06/03 14:22:02 appldev $ */
3 g_debug VARCHAR2 (5) := fnd_profile.VALUE ('AFLOG_LEVEL');
4
5 /*
6 REM *********************************************************************
7 REM * *
8 REM * FILE: GMEVROSB.pls *
9 REM * PURPOSE: Package Body for the GME step re-open api *
10 REM * AUTHOR: Navin Sinha, OPM Development *
11 REM * DATE: May 19 2005 *
12 REM * HISTORY: *
13 REM * ======== *
14 REM *********************************************************************
15 */
16
17 /*======================================================================================
18 Procedure
19 Reopen_All_Steps
20 Description
21 This particular procedure call re-open the batch steps.
22 Parameters
23 p_batch_header_rec The batch header row to identify the header.
24 p_validation_level Errors to skip before returning - Default 100
25 when p_validation_level=0, then called from Re-open batch PVT
26 x_message_count The number of messages in the message stack
27 x_message_list message stack where the api writes its messages
28 x_return_status outcome of the API call
29 S - Success
30 E - Error
31 U - Unexpected error
32 ======================================================================================*/
33 PROCEDURE reopen_all_steps (
34 p_batch_header_rec IN gme_batch_header%ROWTYPE
35 ,x_return_status OUT NOCOPY VARCHAR2)
36 IS
37 /* Local variables */
38 l_batch_steps_tab gme_close_batch_pvt.step_details_tab;
39 l_batch_header gme_batch_header%ROWTYPE;
40 l_in_batch_header gme_batch_header%ROWTYPE;
41 l_phantom_ids gme_common_pvt.number_tab;
42 l_return_status VARCHAR2 (1);
43 batch_step_fetch_err EXCEPTION;
44 batch_step_reopen_err EXCEPTION;
45 batch_step_upd_err EXCEPTION;
46 reopen_phant_error EXCEPTION;
47 BEGIN
48 /* Set the success staus to success inititally*/
49 x_return_status := fnd_api.g_ret_sts_success;
50 /* Get all the step into the tab */
51 gme_close_batch_pvt.fetch_batch_steps
52 (p_batch_id => p_batch_header_rec.batch_id
53 ,p_batchstep_id => NULL
54 ,x_step_tbl => l_batch_steps_tab
55 ,x_return_status => x_return_status);
56
57 IF x_return_status <> fnd_api.g_ret_sts_success THEN
58 RAISE batch_step_fetch_err;
59 END IF;
60
61 FOR i IN 1 .. l_batch_steps_tab.COUNT LOOP
62 -- Only reopen closed steps.
63 IF l_batch_steps_tab (i).step_status = 4 THEN
64 l_batch_steps_tab (i).step_close_date := NULL;
65 l_batch_steps_tab (i).step_status := 3;
66
67 -- Update Batch Step Record
68 IF NOT (gme_batch_steps_dbl.update_row
69 (p_batch_step => l_batch_steps_tab
70 (i) ) ) THEN
71 RAISE batch_step_upd_err;
72 END IF;
73
74 /* For any ingredient lines attached with the step we have to */
75 /* reopen any phantom batches associated with it */
76 gme_phantom_pvt.fetch_step_phantoms
77 (p_batch_id => l_batch_steps_tab
78 (i).batch_id
79 ,p_batchstep_id => l_batch_steps_tab
80 (i).batchstep_id
81 ,p_all_release_type_assoc => 0
82 ,x_phantom_ids => l_phantom_ids
83 ,x_return_status => l_return_status);
84
85 IF l_return_status <> x_return_status THEN
86 RAISE reopen_phant_error;
87 END IF;
88
89 FOR i IN 1 .. l_phantom_ids.COUNT LOOP
90 l_batch_header.batch_id := l_phantom_ids (i);
91 l_in_batch_header := l_batch_header;
92 gme_reopen_batch_pvt.reopen_batch
93 (p_batch_header_rec => l_in_batch_header
94 ,x_batch_header_rec => l_batch_header
95 ,p_reopen_steps => 'T'
96 ,x_return_status => l_return_status);
97
98 IF l_return_status <> x_return_status THEN
99 RAISE reopen_phant_error;
100 END IF;
101 END LOOP;
102 END IF; /* step_status = 4 */
103 END LOOP;
104 EXCEPTION
105 WHEN batch_step_fetch_err THEN
106 x_return_status := fnd_api.g_ret_sts_error;
107 WHEN batch_step_upd_err THEN
108 x_return_status := fnd_api.g_ret_sts_error;
109 WHEN batch_step_reopen_err THEN
110 x_return_status := fnd_api.g_ret_sts_error;
111 gme_common_pvt.log_message ('GME_API_BATCH_STEP_REOPEN_ERR');
112 WHEN reopen_phant_error THEN
113 x_return_status := l_return_status;
114 WHEN OTHERS THEN
115 x_return_status := fnd_api.g_ret_sts_unexp_error;
116 fnd_msg_pub.add_exc_msg ('GME_REOPEN_STEP_PVT', 'REOPEN_ALL_STEPS');
117 END reopen_all_steps;
118
119 /*======================================================================================
120 Procedure
121 Reopen_Step
122 Description
123 This particular procedure call re-open the batch steps.
124 Parameters
125 p_batch_step_rec The batch step row to identify the step.
126 x_return_status outcome of the API call
127 S - Success
128 E - Error
129 U - Unexpected error
130 ======================================================================================*/
131 PROCEDURE reopen_step (
132 p_batch_step_rec IN gme_batch_steps%ROWTYPE
133 ,x_batch_step_rec OUT NOCOPY gme_batch_steps%ROWTYPE
134 ,x_return_status OUT NOCOPY VARCHAR2)
135 IS
136 /* Miscellaneous */
137 l_batch_status NUMBER;
138 l_batch_header gme_batch_header%ROWTYPE;
139 l_in_batch_header gme_batch_header%ROWTYPE;
140 l_auto NUMBER := 0;
141 l_count NUMBER := 0;
142 l_enforce_step_dep NUMBER := -1;
143 /* Exception definitions */
144 batch_step_fetch_err EXCEPTION;
145 invalid_batch_status EXCEPTION;
146 invalid_step_status EXCEPTION;
147 batch_step_upd_err EXCEPTION;
148 reopen_phant_error EXCEPTION;
149 batch_depend_step EXCEPTION;
150
151 /* Database cursors for various tables*/
152 CURSOR cur_batch_status (l_batch_id IN NUMBER)
153 IS
154 SELECT batch_status, automatic_step_calculation
155 ,enforce_step_dependency
156 FROM gme_batch_header
157 WHERE batch_id = l_batch_id;
158
159 CURSOR cur_fetch_dep_steps (l_batchstep_id IN NUMBER, l_batch_id NUMBER)
160 IS
161 SELECT COUNT (*)
162 FROM gme_batch_step_dependencies gbsd, gme_batch_steps gbs
163 WHERE gbs.batchstep_id = gbsd.batchstep_id
164 AND gbsd.dep_step_id = l_batchstep_id
165 AND gbs.batch_id = l_batch_id
166 AND gbsd.batch_id = gbs.batch_id
167 AND gbsd.dep_type = 0
168 AND gbs.step_status = 4;
169
170 l_return_status VARCHAR2 (1);
171 l_phantom_ids gme_common_pvt.number_tab;
172 BEGIN
173 -- Set the return status to success initially
174 x_return_status := fnd_api.g_ret_sts_success;
175
176 IF NOT (gme_batch_steps_dbl.fetch_row (p_batch_step => p_batch_step_rec
177 ,x_batch_step => x_batch_step_rec) ) THEN
178 RAISE batch_step_fetch_err;
179 END IF;
180
181 -- Batch can be Certified(3) or WIP(2) to re-open its closed step
182 OPEN cur_batch_status (x_batch_step_rec.batch_id);
183
184 FETCH cur_batch_status
185 INTO l_batch_status, l_auto, l_enforce_step_dep;
186
187 CLOSE cur_batch_status;
188
189 IF l_batch_status NOT IN (2, 3) THEN
190 RAISE invalid_batch_status;
191 END IF;
192
193 -- Return if step status is already closed
194 -- Current step status must be closed to reopen step
195 IF x_batch_step_rec.step_status <> 4 THEN
196 RAISE invalid_step_status;
197 END IF;
198
199 -- Added check for enforce_step_dep also
200 IF (l_auto = 1) OR (l_enforce_step_dep = 1) THEN
201 OPEN cur_fetch_dep_steps (x_batch_step_rec.batchstep_id
202 ,x_batch_step_rec.batch_id);
203
204 FETCH cur_fetch_dep_steps
205 INTO l_count;
206
207 CLOSE cur_fetch_dep_steps;
208
209 IF l_count > 0 THEN
210 RAISE batch_depend_step;
211 END IF;
212 END IF;
213
214 -- Update the Batch Step Status to Certified and step close date to NULL
215 x_batch_step_rec.step_close_date := NULL;
216 x_batch_step_rec.step_status := 3;
217
218 -- Update Batch Step Record
219 IF NOT (gme_batch_steps_dbl.update_row (p_batch_step => x_batch_step_rec) ) THEN
220 RAISE batch_step_upd_err;
221 END IF;
222
223 x_batch_step_rec.last_update_date := gme_common_pvt.g_timestamp;
224 x_batch_step_rec.last_updated_by := gme_common_pvt.g_user_ident;
225 x_batch_step_rec.last_update_login := gme_common_pvt.g_login_id;
226 /* For any ingredient lines attached with the step we have to */
227 /* reopen any phantom batches associated with it */
228 gme_phantom_pvt.fetch_step_phantoms
229 (p_batch_id => x_batch_step_rec.batch_id
230 ,p_batchstep_id => x_batch_step_rec.batchstep_id
231 ,x_phantom_ids => l_phantom_ids
232 ,p_all_release_type_assoc => 0
233 ,x_return_status => l_return_status);
234
235 IF l_return_status <> x_return_status THEN
236 RAISE reopen_phant_error;
237 END IF;
238
239 FOR i IN 1 .. l_phantom_ids.COUNT LOOP
240 l_batch_header.batch_id := l_phantom_ids (i);
241 l_in_batch_header := l_batch_header;
242 gme_reopen_batch_pvt.reopen_batch
243 (p_batch_header_rec => l_in_batch_header
244 ,x_batch_header_rec => l_batch_header
245 ,p_reopen_steps => 'F'
246 ,x_return_status => l_return_status);
247
248 IF l_return_status <> x_return_status THEN
249 RAISE reopen_phant_error;
250 END IF;
251 END LOOP;
252 EXCEPTION
253 WHEN batch_step_fetch_err THEN
254 x_return_status := fnd_api.g_ret_sts_error;
255 WHEN invalid_batch_status THEN
256 x_return_status := fnd_api.g_ret_sts_error;
257 gme_common_pvt.log_message ('GME_API_INV_BATCH_STATUS_REOP');
258 WHEN invalid_step_status THEN
259 x_return_status := fnd_api.g_ret_sts_error;
260 gme_common_pvt.log_message ('GME_API_INV_STEP_STATUS_REOP');
261 WHEN batch_depend_step THEN
262 x_return_status := fnd_api.g_ret_sts_error;
263 gme_common_pvt.log_message ('GME_API_DEP_STEP_REOPEN');
264 WHEN batch_step_upd_err THEN
265 x_return_status := fnd_api.g_ret_sts_error;
266 WHEN reopen_phant_error THEN
267 x_return_status := l_return_status;
268 WHEN OTHERS THEN
269 x_return_status := fnd_api.g_ret_sts_unexp_error;
270 fnd_msg_pub.add_exc_msg ('GME_REOPEN_STEP_PVT', 'REOPEN_STEP');
271 END reopen_step;
272 END gme_reopen_step_pvt;
[Home] [Help]