1 PACKAGE BODY gme_close_step_pvt AS
2 /* $Header: GMEVCLSB.pls 120.3 2006/05/15 05:30:31 svgonugu noship $ */
3 g_debug VARCHAR2 (5) := fnd_profile.VALUE ('AFLOG_LEVEL');
4
5 /*======================================================================================
6 Procedure
7 close_step
8 Description
9 This particular procedure call close the batch steps.
10 Parameters
11 p_api_version For version specific processing - Default 1
12 p_validation_level Errors to skip before returning - Default 100
13 p_init_msg_list Signals wether the message stack should be initialised
14 p_commit Indicator to commit the changes made
15 p_batch_step_rec The batch step row to identify the step.
16 x_message_count The number of messages in the message stack
17 x_message_list message stack where the api writes its messages
18 x_return_status outcome of the API call
19 S - Success
20 E - Error
21 U - Unexpected error
22 ======================================================================================*/
23 PROCEDURE close_step (
24 p_batch_step_rec IN gme_batch_steps%ROWTYPE
25 ,p_delete_pending IN VARCHAR2 DEFAULT fnd_api.g_false
26 ,x_batch_step_rec OUT NOCOPY gme_batch_steps%ROWTYPE
27 ,x_return_status OUT NOCOPY VARCHAR2)
28 IS
29 /* Miscellaneous */
30 l_batch_header gme_batch_header%ROWTYPE;
31 l_in_batch_header gme_batch_header%ROWTYPE;
32 l_return_status VARCHAR2 (1);
33 preceding_step_closed VARCHAR2 (1) := 'Y';
34 l_pend_exists BOOLEAN;
35 l_mat_row_count NUMBER;
36 l_rsc_row_count NUMBER;
37 l_phantom_ids gme_common_pvt.number_tab;
38 l_material_ids gme_common_pvt.number_tab;
39 l_tran_row gme_inventory_txns_gtmp%ROWTYPE;
40 l_default_row gme_inventory_txns_gtmp%ROWTYPE;
41 /* Exception definations */
42 batch_step_fetch_error EXCEPTION;
43 invalid_step_status EXCEPTION;
44 close_phant_error EXCEPTION;
45 invalid_batch_status EXCEPTION;
46 batch_header_fetch_error EXCEPTION;
47 step_status_closed EXCEPTION;
48 dep_step_closed_error EXCEPTION;
49 batch_step_upd_err EXCEPTION;
50 gme_invalid_date_range EXCEPTION;
51 invalid_batch_type EXCEPTION;
52 pend_trans_err EXCEPTION;
53 fetch_trans_err EXCEPTION;
54 trans_delete_err EXCEPTION;
55 trans_update_err EXCEPTION;
56
57 /* This cursor fetches all the steps on which the given step
58 is dependent */
59 CURSOR cur_dep_steps
60 IS
61 SELECT d.dep_step_id, s.step_status
62 FROM gme_batch_step_dependencies d, gme_batch_steps s
63 WHERE d.batchstep_id = p_batch_step_rec.batchstep_id
64 AND s.batchstep_id = d.dep_step_id;
65
66 CURSOR cur_material_ids (v_batchstep_id IN NUMBER)
67 IS
68 SELECT m.material_detail_id
69 FROM gme_material_details m, gme_batch_step_items i
70 WHERE m.material_detail_id = i.material_detail_id
71 AND i.batchstep_id = v_batchstep_id;
72
73 l_dep_steps_rec cur_dep_steps%ROWTYPE;
74 --Bug#5109119
75 error_close_period EXCEPTION;
76 BEGIN
77 /* Set the return status to success initially */
78 x_return_status := fnd_api.g_ret_sts_success;
79
80 /* Initialize output batch step */
81 IF NOT (gme_batch_steps_dbl.fetch_row (p_batch_step_rec
82 ,x_batch_step_rec) ) THEN
83 RAISE batch_step_fetch_error;
84 END IF;
85
86 l_batch_header.batch_id := x_batch_step_rec.batch_id;
87
88 /* Initialize local batch header */
89 IF NOT (gme_batch_header_dbl.fetch_row (l_batch_header, l_batch_header) ) THEN
90 RAISE batch_header_fetch_error;
91 END IF;
92
93 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
94 gme_debug.put_line ( 'Closing step '
95 || l_batch_header.batch_no
96 || '/'
97 || x_batch_step_rec.batchstep_no
98 || ' at '
99 || TO_CHAR (p_batch_step_rec.step_close_date
100 ,'DD-MON-YYYY HH24:MI:SS') );
101 END IF;
102
103 /* Check that its a batch and not FPO */
104 IF l_batch_header.batch_type = 10 THEN
105 RAISE invalid_batch_type;
106 END IF;
107
108 /* Batch must be at least wip to close a batch step */
109 IF l_batch_header.batch_status = 1 THEN
110 RAISE invalid_batch_status;
111 END IF;
112
113 /* Return if step status is already closed */
114 /* This is not failure but the status will be return with success */
115 IF x_batch_step_rec.step_status = 4 THEN
116 RAISE step_status_closed;
117 /* Current step status must be certified to close step */
118 ELSIF x_batch_step_rec.step_status <> 3 THEN
119 RAISE invalid_step_status;
120 END IF;
121
122 IF l_batch_header.batch_status = 4 THEN
123 x_batch_step_rec.step_status := 4;
124 ELSE
125 -- coded add for closing the dependent steps only if ASQC is on
126 /* Bharati Satpute Bug2395188, Added check for the enforce step dependency*/
127 IF l_batch_header.automatic_step_calculation = 1
128 OR l_batch_header.enforce_step_dependency = 1 THEN
129 OPEN cur_dep_steps;
130
131 FETCH cur_dep_steps
132 INTO l_dep_steps_rec;
133
134 WHILE cur_dep_steps%FOUND LOOP
135 IF l_dep_steps_rec.step_status <> 4 THEN
136 CLOSE cur_dep_steps;
137
138 RAISE dep_step_closed_error;
139 END IF;
140
141 /* Bug 2395188 with hanging of close step API fixed */
142 FETCH cur_dep_steps
143 INTO l_dep_steps_rec;
144 END LOOP;
145
146 CLOSE cur_dep_steps;
147 END IF;
148
149 /* Update the Batch Step Status to Close */
150 x_batch_step_rec.step_status := 4;
151 END IF;
152
153 /* If a valid step_close_date is supplied by user, use it */
154 IF p_batch_step_rec.step_close_date IS NOT NULL THEN
155 /* Validate Date */
156 IF p_batch_step_rec.step_close_date >=
157 p_batch_step_rec.actual_cmplt_date THEN
158 x_batch_step_rec.step_close_date :=
159 p_batch_step_rec.step_close_date;
160 ELSE
161 gme_common_pvt.log_message ('GME_INVALID_DATE_RANGE'
162 ,'DATE1'
163 ,'Close Date'
164 ,'DATE2'
165 ,'Completion Date');
166 RAISE gme_invalid_date_range;
167 END IF; /* >= step completion date */
168 x_batch_step_rec.step_close_date := p_batch_step_rec.step_close_date;
169 ELSE
170 x_batch_step_rec.step_close_date := gme_common_pvt.g_timestamp;
171 END IF; /* step close date is not null */
172
173 --Bug#5109119 check for close period
174 IF NOT gme_common_pvt.check_close_period(p_org_id => l_batch_header.organization_id
175 ,p_trans_date => x_batch_step_rec.step_close_date) THEN
176 RAISE error_close_period;
177 END IF;
178
179 /* Update the batch step to the database */
180
181 IF NOT (gme_batch_steps_dbl.update_row (x_batch_step_rec) ) THEN
182 RAISE batch_step_upd_err;
183 END IF;
184
185 x_batch_step_rec.last_update_date := gme_common_pvt.g_timestamp;
186 x_batch_step_rec.last_updated_by := gme_common_pvt.g_user_ident;
187 x_batch_step_rec.last_update_login := gme_common_pvt.g_login_id;
188
189 /* For any ingredient lines attached with the step we have to */
190 /* close any phantom batches associated with it */
191 /* We need to do this only if close step is
192 called standalone. If this procedure is called from
193 close batch, then all the phantom batches are already closed */
194 IF l_batch_header.batch_status <> 4 THEN
195 gme_phantom_pvt.fetch_step_phantoms
196 (p_batch_id => x_batch_step_rec.batch_id
197 ,p_batchstep_id => x_batch_step_rec.batchstep_id
198 ,p_all_release_type_assoc => 0
199 ,x_phantom_ids => l_phantom_ids
200 ,x_return_status => l_return_status);
201
202 IF l_return_status <> x_return_status THEN
203 RAISE close_phant_error;
204 END IF;
205
206 FOR i IN 1 .. l_phantom_ids.COUNT LOOP
207 l_batch_header.batch_id := l_phantom_ids (i);
208 l_batch_header.batch_close_date :=
209 x_batch_step_rec.step_close_date;
210 l_in_batch_header := l_batch_header;
211 gme_close_batch_pvt.close_batch
212 (p_batch_header_rec => l_in_batch_header
213 ,x_batch_header_rec => l_batch_header
214 ,x_return_status => l_return_status);
215
216 IF l_return_status <> x_return_status THEN
217 RAISE close_phant_error;
218 END IF;
219 END LOOP;
220 END IF; /* l_batch_header.batch_status <> 4 */
221 EXCEPTION
222 WHEN batch_step_fetch_error OR batch_header_fetch_error OR error_close_period THEN
223 x_return_status := fnd_api.g_ret_sts_error;
224 WHEN invalid_batch_status THEN
225 x_return_status := fnd_api.g_ret_sts_error;
226 gme_common_pvt.log_message ('GME_API_INV_BATCH_CLOSE_STEP');
227 WHEN invalid_batch_type THEN
228 x_return_status := fnd_api.g_ret_sts_error;
229 gme_common_pvt.log_message ('INVALID_BATCH_TYPE_CLS_STEP');
230 WHEN step_status_closed THEN
231 x_return_status := fnd_api.g_ret_sts_error;
232 gme_common_pvt.log_message ('GME_API_CLOSE_STEP_STATUS');
233 WHEN invalid_step_status THEN
234 x_return_status := fnd_api.g_ret_sts_error;
235 gme_common_pvt.log_message ('GME_API_INV_STAT_STEP_CLS');
236 WHEN dep_step_closed_error THEN
237 x_return_status := fnd_api.g_ret_sts_error;
238 gme_common_pvt.log_message ('GME_API_DEP_STEP_N_CLS');
239 WHEN batch_step_upd_err THEN
240 gme_common_pvt.log_message ('GME_API_STEP_UPD_ERROR');
241 WHEN gme_invalid_date_range THEN
242 x_return_status := fnd_api.g_ret_sts_error;
243 WHEN close_phant_error OR trans_update_err OR trans_delete_err OR fetch_trans_err THEN
244 x_return_status := l_return_status;
245 WHEN pend_trans_err THEN
246 x_return_status := fnd_api.g_ret_sts_error;
247 gme_common_pvt.log_message ('GME_PENDING_TRANS_ERROR');
248 WHEN OTHERS THEN
249 x_return_status := fnd_api.g_ret_sts_unexp_error;
250 fnd_msg_pub.add_exc_msg ('GME_API_CLOSE_STEP', 'CLOSE_STEP');
251 END close_step;
252 END gme_close_step_pvt;
[Home] [Help]