[Home] [Help]
PACKAGE BODY: APPS.GMO_VALIDATE_BATCH_GRP
Source
1 PACKAGE BODY GMO_VALIDATE_BATCH_GRP
2 /* $Header: GMOBAVAB.pls 120.7 2006/03/27 23:30:17 rahugupt noship $ */
3 AS
4
5 --The package name
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'GMO_VALIDATE_BATCH_GRP';
7
8 FUNCTION IS_PIS_PENDING (P_ENTITY_NAME VARCHAR2,
9 P_ENTITY_KEY VARCHAR2,
10 P_INSTRUCTION_TYPE VARCHAR2,
11 x_msg_data IN OUT NOCOPY VARCHAR2) RETURN BOOLEAN IS
12
13 l_instruction_set_id number;
14 l_return_status varchar2(100);
15 l_msg_count number;
16 l_msg_data varchar2(4000);
17 L_TOTAL_INSTRUCTIONS NUMBER;
18 L_OPTIONAL_PENDING_INSTR NUMBER;
19 L_MANDATORY_PENDING_INSTR NUMBER;
20 L_INSTRUCTION_PENDING VARCHAR2(5);
21 INSTRUCTION_EXCEPTION EXCEPTION;
22
23 BEGIN
24 GMO_INSTRUCTION_PVT.HAS_PENDING_INSTRUCTIONS(P_ENTITY_NAME =>P_ENTITY_NAME,
25 P_ENTITY_KEY =>P_ENTITY_KEY,
26 P_INSTRUCTION_TYPE =>P_INSTRUCTION_TYPE,
27 X_INSTRUCTION_PENDING =>L_INSTRUCTION_PENDING,
28 X_TOTAL_INSTRUCTIONS => L_TOTAL_INSTRUCTIONS,
29 X_OPTIONAL_PENDING_INSTR =>L_OPTIONAL_PENDING_INSTR,
30 X_MANDATORY_PENDING_INSTR =>L_MANDATORY_PENDING_INSTR,
31 X_RETURN_STATUS =>l_return_status,
32 X_MSG_COUNT =>l_msg_count,
33 X_MSG_DATA =>x_msg_data);
34 IF(l_return_status<> FND_API.G_RET_STS_SUCCESS ) THEN
35 RAISE INSTRUCTION_EXCEPTION;
36 END IF;
37 IF (L_MANDATORY_PENDING_INSTR > 0) THEN
38 return FALSE;
39 ELSE
40 return TRUE;
41 END IF;
42
43 EXCEPTION
44 WHEN INSTRUCTION_EXCEPTION THEN
45 FND_MESSAGE.SET_ENCODED(x_msg_data);
46 RAISE INSTRUCTION_EXCEPTION;
47 END;
48
49
50
51
52 -- Start of comments
53 -- API name : VALIDATE_BATCH_COMPLIANCE
54 -- Type : Public.
55
56 -- Function : This procedure implements the following:
57 -- 1. Validates the batch ID or batch step ID. If validation fails it returns an error status.
58
59 -- 2. Navigates through the batch hierarchy to identify any pending instructions.
60 -- If pending instructions
61
62 -- do exist then an entry is made for the same in the Audit table.
63 -- 3. Navigates through the batch hierarchy to identify any pending deviations and audits the same.
64
65 -- 4. Returns a unique validation ID and a validation status back to the calling program.
66
67
68 -- Pre-reqs : None.
69 -- Parameters :
70 -- IN :P_API_VERSION(Required) - NUMBER - Specifies the API version.
71
72 -- P_INIT_MSG_LIST(Optional) - VARCHAR2 - Specifies if the message list should be initialized.
73
74 -- Default = FND_API.G_FALSE
75
76 -- P_ENTITY_NAME(Required) - VARCHAR2 - The entity to be validated. It takes only the following values.
77
78 -- - 1. GMO_CONSTANTS_GRP.ENTITY_BATCH for validating a batch
79
80 -- - 2. GMO_CONSTANTS_GRP.ENTITY_OPERATION for validating a batch step.
81
82 -- - If any other value is provided then the API will error out.
83
84
85 PROCEDURE VALIDATE_BATCH_COMPLIANCE
86 (P_API_VERSION IN NUMBER,
87 P_INIT_MSG_LIST IN VARCHAR2 DEFAULT FND_API.G_FALSE,
88 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
89 X_MSG_COUNT OUT NOCOPY NUMBER,
90 X_MSG_DATA OUT NOCOPY VARCHAR2,
91 P_ENTITY_NAME IN VARCHAR2,
92 P_ENTITY_KEY IN VARCHAR2,
93 X_VALIDATION_ID OUT NOCOPY NUMBER,
94 X_VALIDATION_STATUS OUT NOCOPY VARCHAR2) IS
95
96 l_batchstep_id number;
97 l_resources varchar2(16);
98 l_material_detail_id number;
99 l_batchstep_activity_id number;
100 l_batchstep_resource_id number;
101 l_dispense_id number;
102 l_undispense_id number;
103 l_ncm_count number;
104 l_entity_type CONSTANT VARCHAR2(30) :='DISPENSE';
105 l_entity_name CONSTANT VARCHAR2(30) :='DISPENSE_ITEM';
106 l_rev_disp_type CONSTANT VARCHAR2(30) :='REVERSE_DISPENSE';
107
108 INSTRUCTIONS_PENDING exception;
109 NCM_NOT_CLOSED exception;
110 INSTRUNCTION_EXCEPTION EXCEPTION;
111 cursor c_get_steps is
112 select batchstep_id
113 from gme_batch_steps
114 where ((GMO_CONSTANTS_GRP.ENTITY_BATCH = P_ENTITY_NAME AND batch_id = p_entity_key) OR
115 (GMO_CONSTANTS_GRP.ENTITY_OPERATION = P_ENTITY_NAME AND batchstep_id = p_entity_key));
116
117 cursor c_get_activities is
118 select batchstep_activity_id
119 from gme_batch_step_activities
120 where batchstep_id = l_batchstep_id;
121
122 cursor c_get_resources is
123 select batchstep_resource_id
124 from gme_batch_step_resources gbsr
125 where batchstep_activity_id = l_batchstep_activity_id;
126
127 cursor c_get_materials is
128 select material_detail_id
129 from gme_material_details
130 where batch_id = p_entity_key;
131
132 cursor c_get_step_materials is
133 select material_detail_id
134 from gme_batch_step_items
135 where batchstep_id = l_batchstep_id;
136
137 CURSOR C_GET_DISPENSES IS
138 SELECT Dispense_id
139 from gmo_material_dispenses
140 WHERE material_status = 'DISPENSD'
141 and material_detail_id = l_material_detail_id;
142
143 CURSOR C_GET_unDISPENSES IS
144 SELECT unDispense_id
145 from gmo_material_undispenses
146 WHERE material_status = 'DISPENSD'
147 and material_detail_id = l_material_detail_id;
148
149 BEGIN
150
151 -- Process Batch Steps
152 open c_get_steps;
153 LOOP
154 FETCH c_get_steps into l_batchstep_id;
155 EXIT WHEN c_get_steps%NOTFOUND;
156
157 -- Check for pending mandatory instructions
158
159 IF NOT IS_PIS_PENDING (P_ENTITY_NAME => GMO_CONSTANTS_GRP.ENTITY_OPERATION,
160 P_ENTITY_KEY => l_batchstep_id,
161 P_INSTRUCTION_TYPE => GMO_CONSTANTS_GRP.VBATCH_INSTRUCTION_TYPE,
162 x_msg_data => x_msg_data )
163 THEN
164 close c_get_steps;
165 RAISE INSTRUCTIONS_PENDING;
166 END IF;
167
168 -- Check for NCM
169
170 -- Process Activities
171
172 open c_get_activities;
173 LOOP
174 FETCH c_get_activities into l_batchstep_activity_id;
175 EXIT WHEN c_get_activities%NOTFOUND;
176
177 -- Check for pending mandatory instructions
178
179 IF NOT IS_PIS_PENDING (P_ENTITY_NAME => GMO_CONSTANTS_GRP.ENTITY_ACTIVITY,
180 P_ENTITY_KEY => l_batchstep_activity_id,
181 P_INSTRUCTION_TYPE => GMO_CONSTANTS_GRP.VBATCH_INSTRUCTION_TYPE,
182 x_msg_data => x_msg_data)
183 THEN
184 close c_get_activities;
185 RAISE INSTRUCTIONS_PENDING;
186 END IF;
187 -- Check for NCM
188
189
190 -- Process resources
191
192 open c_get_resources;
193 LOOP
194 FETCH c_get_resources into l_batchstep_resource_id;
195 EXIT WHEN c_get_resources%NOTFOUND;
196
197 -- Check for pending mandatory instructions
198
199 IF NOT IS_PIS_PENDING(P_ENTITY_NAME => GMO_CONSTANTS_GRP.ENTITY_RESOURCE,
200 P_ENTITY_KEY => l_batchstep_resource_id,
201 P_INSTRUCTION_TYPE => GMO_CONSTANTS_GRP.VBATCH_INSTRUCTION_TYPE,
202 x_msg_data => x_msg_data)
203 THEN
204 close c_get_resources;
205 RAISE INSTRUCTIONS_PENDING;
206 END IF;
207
208 -- Check for NCM
209 END LOOP;
210 close c_get_resources;
211 END LOOP;
212 close c_get_activities;
213 END LOOP;
214 close c_get_steps;
215
216 -- Process material lines
217 IF GMO_CONSTANTS_GRP.ENTITY_BATCH = P_ENTITY_NAME THEN
218 open c_get_materials;
219 LOOP
220 FETCH c_get_materials into l_material_detail_id;
221 EXIT WHEN c_get_materials%NOTFOUND;
222
223 -- Check for pending mandatory instructions
224 IF NOT IS_PIS_PENDING (P_ENTITY_NAME => GMO_CONSTANTS_GRP.ENTITY_MATERIAL,
225 P_ENTITY_KEY => l_material_detail_id,
226 P_INSTRUCTION_TYPE => GMO_CONSTANTS_GRP.VBATCH_INSTRUCTION_TYPE,
227 x_msg_data => x_msg_data)
228 THEN
229 close c_get_materials;
230 RAISE INSTRUCTIONS_PENDING;
231 END IF;
232 -- Check for pending PIs of dispense rows
233 open C_GET_DISPENSES;
234 LOOP
235 FETCH C_GET_DISPENSES into l_dispense_id;
236 EXIT WHEN C_GET_DISPENSES%NOTFOUND;
237 -- Check for pending mandatory instructions
238 IF NOT IS_PIS_PENDING (P_ENTITY_NAME => l_entity_name,
239 P_ENTITY_KEY => l_dispense_id,
240 P_INSTRUCTION_TYPE => l_entity_type,
241 x_msg_data => x_msg_data)
242 THEN
243 close C_GET_DISPENSES;
244 RAISE INSTRUCTIONS_PENDING;
245 END IF;
246 END LOOP;
247 close C_GET_DISPENSES;
248
249 -- Check for pending PIs of undispense rows
250 --Bug 5120934: start
251 open C_GET_unDISPENSES;
252 --Bug 5120934: end
253 LOOP
254 FETCH C_GET_unDISPENSES into l_undispense_id;
255 EXIT WHEN C_GET_unDISPENSES%NOTFOUND;
256 -- Check for pending mandatory instructions
257 IF NOT IS_PIS_PENDING (P_ENTITY_NAME => l_entity_name,
258 P_ENTITY_KEY => l_undispense_id,
259 P_INSTRUCTION_TYPE => l_rev_disp_type,
260 x_msg_data => x_msg_data)
261 THEN
262 close C_GET_UNDISPENSES;
263 RAISE INSTRUCTIONS_PENDING;
264 END IF;
265 END LOOP;
266 close C_GET_UNDISPENSES;
267
268 END LOOP;
269 close c_get_materials;
270
271 -- Chenck for NCM
272 select count(*) into l_ncm_count
273 from qa_results_v
274 where PROCESS_BATCH_ID = p_entity_key
275 and NONCONFORMANCE_STATUS <> 'CLOSED';
276 IF l_ncm_count > 0 THEN
277 raise NCM_NOT_CLOSED;
278 END IF;
279
280 ELSIF GMO_CONSTANTS_GRP.ENTITY_OPERATION = P_ENTITY_NAME THEN
281 l_batchstep_id := p_entity_key;
282 open c_get_step_materials;
283 LOOP
284 FETCH c_get_step_materials into l_material_detail_id;
285 EXIT WHEN c_get_step_materials%NOTFOUND;
286
287 -- Check for pending mandatory instructions
288 IF NOT IS_PIS_PENDING (P_ENTITY_NAME => GMO_CONSTANTS_GRP.ENTITY_MATERIAL,
289 P_ENTITY_KEY => l_material_detail_id,
290 P_INSTRUCTION_TYPE => GMO_CONSTANTS_GRP.VBATCH_INSTRUCTION_TYPE,
291 x_msg_data => x_msg_data)
292 THEN
293 close c_get_step_materials;
294 RAISE INSTRUCTIONS_PENDING;
295 END IF;
296
297 -- Check for pending PIs of dispense rows
298 open C_GET_DISPENSES;
299 LOOP
300 FETCH C_GET_DISPENSES into l_dispense_id;
301 EXIT WHEN C_GET_DISPENSES%NOTFOUND;
302 -- Check for pending mandatory instructions
303 IF NOT IS_PIS_PENDING (P_ENTITY_NAME => l_entity_name,
304 P_ENTITY_KEY => l_dispense_id,
305 P_INSTRUCTION_TYPE => l_entity_type,
306 x_msg_data => x_msg_data)
307 THEN
308 close C_GET_DISPENSES;
309 RAISE INSTRUCTIONS_PENDING;
310 END IF;
311 END LOOP;
312 close C_GET_DISPENSES;
313 -- Check for pending PIs of undispense rows
314 --Bug 5120934: start
315 open C_GET_unDISPENSES;
316 --Bug 5120934: end
317 LOOP
318 FETCH C_GET_unDISPENSES into l_undispense_id;
319 EXIT WHEN C_GET_unDISPENSES%NOTFOUND;
320 -- Check for pending mandatory instructions
321 IF IS_PIS_PENDING (P_ENTITY_NAME => l_entity_name,
322 P_ENTITY_KEY => l_undispense_id,
323 P_INSTRUCTION_TYPE => l_rev_disp_type,
324 x_msg_data => x_msg_data)
325 THEN
326 close C_GET_UNDISPENSES;
327 RAISE INSTRUCTIONS_PENDING;
328 END IF;
329 END LOOP;
330 close C_GET_UNDISPENSES;
331
332 END LOOP;
333 close c_get_step_materials;
334
335 -- Chenck for NCM
336 select count(*) into l_ncm_count
337 from qa_results_v
338 where PROCESS_BATCHSTEP_ID = p_entity_key
339 and NONCONFORMANCE_STATUS <> 'CLOSED';
340 IF l_ncm_count > 0 THEN
341 raise NCM_NOT_CLOSED;
342 END IF;
343
344 END IF;
345
346
347
348 X_RETURN_STATUS := 'S';
349 X_VALIDATION_STATUS := 'S';
350 X_VALIDATION_ID := 1000;
351 EXCEPTION WHEN INSTRUCTIONS_PENDING THEN
352 FND_MESSAGE.SET_NAME('GMO','GMO_INSTRUCTION_PENDING');
353 FND_MSG_PUB.ADD;
354 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
355 X_RETURN_STATUS := 'E';
356 X_VALIDATION_STATUS := 'E';
357 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
358 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
359 'gmo.plsql.GMO_VALIDATE_BATCH_GRP.VALIDATE_BATCH_COMPLIANCE',
360 FALSE
361 );
362 end if;
363 WHEN NCM_NOT_CLOSED THEN
364 FND_MESSAGE.SET_NAME('GMO','GMO_NOT_ALL_NCMS_CLOSED');
365 FND_MSG_PUB.ADD;
366 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
367 X_RETURN_STATUS := 'E';
368 X_VALIDATION_STATUS := 'E';
369 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
370 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
371 'gmo.plsql.GMO_VALIDATE_BATCH_GRP.VALIDATE_BATCH_COMPLIANCE',
372 FALSE
373 );
374 end if;
375 WHEN INSTRUNCTION_EXCEPTION THEN
376 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
377 X_RETURN_STATUS := 'E';
378 X_VALIDATION_STATUS := 'E';
379 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
380 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
381 'gmo.plsql.GMO_VALIDATE_BATCH_GRP.VALIDATE_BATCH_COMPLIANCE',
382 FALSE
383 );
384 end if;
385 END VALIDATE_BATCH_COMPLIANCE;
386
387 END GMO_VALIDATE_BATCH_GRP;