[Home] [Help]
PACKAGE BODY: APPS.GMO_VALIDATE_BATCH_GRP
Source
1 PACKAGE BODY GMO_VALIDATE_BATCH_GRP
2 /* $Header: GMOBAVAB.pls 120.9 2011/05/06 10:21:59 rborpatl ship $ */
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 l_batch_id number;
96 l_batchstep_id number;
97 l_resources varchar2(32);
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 batch_id, 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
121 batchstep_id = l_batchstep_id
122 AND batch_id = l_batch_id;
123
124 cursor c_get_resources is
125 select batchstep_resource_id
126 from gme_batch_step_resources gbsr
127 where batchstep_activity_id = l_batchstep_activity_id
128 AND batch_id = l_batch_id AND batchstep_id = l_batchstep_id
129 ;
130
131 cursor c_get_materials is
132 select material_detail_id
133 from gme_material_details
134 where batch_id = p_entity_key;
135
136 cursor c_get_step_materials is
137 select material_detail_id
138 from gme_batch_step_items
139 where batchstep_id = l_batchstep_id
140 AND batch_id = l_batch_id;
141
142 CURSOR C_GET_DISPENSES IS
143 SELECT Dispense_id
144 from gmo_material_dispenses
145 WHERE material_status = 'DISPENSD'
146 and material_detail_id = l_material_detail_id;
147
148 CURSOR C_GET_unDISPENSES IS
149 SELECT unDispense_id
150 from gmo_material_undispenses
151 WHERE material_status = 'DISPENSD'
152 and material_detail_id = l_material_detail_id;
153
154 BEGIN
155
156 -- Process Batch Steps
157 open c_get_steps;
158 LOOP
159 FETCH c_get_steps into l_batch_id,l_batchstep_id;
160 EXIT WHEN c_get_steps%NOTFOUND;
161
162 -- Check for pending mandatory instructions
163
164 IF NOT IS_PIS_PENDING (P_ENTITY_NAME => GMO_CONSTANTS_GRP.ENTITY_OPERATION,
165 P_ENTITY_KEY => l_batchstep_id,
166 P_INSTRUCTION_TYPE => GMO_CONSTANTS_GRP.VBATCH_INSTRUCTION_TYPE,
167 x_msg_data => x_msg_data )
168 THEN
169 close c_get_steps;
170 RAISE INSTRUCTIONS_PENDING;
171 END IF;
172
173 -- Check for NCM
174
175 -- Process Activities
176
177 open c_get_activities;
178 LOOP
179 FETCH c_get_activities into l_batchstep_activity_id;
180 EXIT WHEN c_get_activities%NOTFOUND;
181
182 -- Check for pending mandatory instructions
183
184 IF NOT IS_PIS_PENDING (P_ENTITY_NAME => GMO_CONSTANTS_GRP.ENTITY_ACTIVITY,
185 P_ENTITY_KEY => l_batchstep_activity_id,
186 P_INSTRUCTION_TYPE => GMO_CONSTANTS_GRP.VBATCH_INSTRUCTION_TYPE,
187 x_msg_data => x_msg_data)
188 THEN
189 close c_get_activities;
190 RAISE INSTRUCTIONS_PENDING;
191 END IF;
192 -- Check for NCM
193
194
195 -- Process resources
196
197 open c_get_resources;
198 LOOP
199 FETCH c_get_resources into l_batchstep_resource_id;
200 EXIT WHEN c_get_resources%NOTFOUND;
201
202 -- Check for pending mandatory instructions
203
204 IF NOT IS_PIS_PENDING(P_ENTITY_NAME => GMO_CONSTANTS_GRP.ENTITY_RESOURCE,
205 P_ENTITY_KEY => l_batchstep_resource_id,
206 P_INSTRUCTION_TYPE => GMO_CONSTANTS_GRP.VBATCH_INSTRUCTION_TYPE,
207 x_msg_data => x_msg_data)
208 THEN
209 close c_get_resources;
210 RAISE INSTRUCTIONS_PENDING;
211 END IF;
212
213 -- Check for NCM
214 END LOOP;
215 close c_get_resources;
216 END LOOP;
217 close c_get_activities;
218 END LOOP;
219 close c_get_steps;
220
221 -- Process material lines
222 IF GMO_CONSTANTS_GRP.ENTITY_BATCH = P_ENTITY_NAME THEN
223 open c_get_materials;
224 LOOP
225 FETCH c_get_materials into l_material_detail_id;
226 EXIT WHEN c_get_materials%NOTFOUND;
227
228 -- Check for pending mandatory instructions
229 IF NOT IS_PIS_PENDING (P_ENTITY_NAME => GMO_CONSTANTS_GRP.ENTITY_MATERIAL,
230 P_ENTITY_KEY => l_material_detail_id,
231 P_INSTRUCTION_TYPE => GMO_CONSTANTS_GRP.VBATCH_INSTRUCTION_TYPE,
232 x_msg_data => x_msg_data)
233 THEN
234 close c_get_materials;
235 RAISE INSTRUCTIONS_PENDING;
236 END IF;
237 -- Check for pending PIs of dispense rows
238 open C_GET_DISPENSES;
239 LOOP
240 FETCH C_GET_DISPENSES into l_dispense_id;
241 EXIT WHEN C_GET_DISPENSES%NOTFOUND;
242 -- Check for pending mandatory instructions
243 IF NOT IS_PIS_PENDING (P_ENTITY_NAME => l_entity_name,
244 P_ENTITY_KEY => l_dispense_id,
245 P_INSTRUCTION_TYPE => l_entity_type,
246 x_msg_data => x_msg_data)
247 THEN
248 close C_GET_DISPENSES;
249 RAISE INSTRUCTIONS_PENDING;
250 END IF;
251 END LOOP;
252 close C_GET_DISPENSES;
253
254 -- Check for pending PIs of undispense rows
255 --Bug 5120934: start
256 open C_GET_unDISPENSES;
257 --Bug 5120934: end
258 LOOP
259 FETCH C_GET_unDISPENSES into l_undispense_id;
260 EXIT WHEN C_GET_unDISPENSES%NOTFOUND;
261 -- Check for pending mandatory instructions
262 IF NOT IS_PIS_PENDING (P_ENTITY_NAME => l_entity_name,
263 P_ENTITY_KEY => l_undispense_id,
264 P_INSTRUCTION_TYPE => l_rev_disp_type,
265 x_msg_data => x_msg_data)
266 THEN
267 close C_GET_UNDISPENSES;
268 RAISE INSTRUCTIONS_PENDING;
269 END IF;
270 END LOOP;
271 close C_GET_UNDISPENSES;
272
273 END LOOP;
274 close c_get_materials;
275
276 -- Chenck for NCM
277 select count(*) into l_ncm_count
278 from qa_results_v
279 where PROCESS_BATCH_ID = p_entity_key
280 and NONCONFORMANCE_STATUS <> 'CLOSED';
281 IF l_ncm_count > 0 THEN
282 raise NCM_NOT_CLOSED;
283 END IF;
284
285 ELSIF GMO_CONSTANTS_GRP.ENTITY_OPERATION = P_ENTITY_NAME THEN
286 l_batchstep_id := p_entity_key;
287 open c_get_step_materials;
288 LOOP
289 FETCH c_get_step_materials into l_material_detail_id;
290 EXIT WHEN c_get_step_materials%NOTFOUND;
291
292 -- Check for pending mandatory instructions
293 IF NOT IS_PIS_PENDING (P_ENTITY_NAME => GMO_CONSTANTS_GRP.ENTITY_MATERIAL,
294 P_ENTITY_KEY => l_material_detail_id,
295 P_INSTRUCTION_TYPE => GMO_CONSTANTS_GRP.VBATCH_INSTRUCTION_TYPE,
296 x_msg_data => x_msg_data)
297 THEN
298 close c_get_step_materials;
299 RAISE INSTRUCTIONS_PENDING;
300 END IF;
301
302 -- Check for pending PIs of dispense rows
303 open C_GET_DISPENSES;
304 LOOP
305 FETCH C_GET_DISPENSES into l_dispense_id;
306 EXIT WHEN C_GET_DISPENSES%NOTFOUND;
307 -- Check for pending mandatory instructions
308 IF NOT IS_PIS_PENDING (P_ENTITY_NAME => l_entity_name,
309 P_ENTITY_KEY => l_dispense_id,
310 P_INSTRUCTION_TYPE => l_entity_type,
311 x_msg_data => x_msg_data)
312 THEN
313 close C_GET_DISPENSES;
314 RAISE INSTRUCTIONS_PENDING;
315 END IF;
316 END LOOP;
317 close C_GET_DISPENSES;
318 -- Check for pending PIs of undispense rows
319 --Bug 5120934: start
320 open C_GET_unDISPENSES;
321 --Bug 5120934: end
322 LOOP
323 FETCH C_GET_unDISPENSES into l_undispense_id;
324 EXIT WHEN C_GET_unDISPENSES%NOTFOUND;
325 -- Check for pending mandatory instructions
326 IF IS_PIS_PENDING (P_ENTITY_NAME => l_entity_name,
327 P_ENTITY_KEY => l_undispense_id,
328 P_INSTRUCTION_TYPE => l_rev_disp_type,
329 x_msg_data => x_msg_data)
330 THEN
331 close C_GET_UNDISPENSES;
332 RAISE INSTRUCTIONS_PENDING;
333 END IF;
334 END LOOP;
335 close C_GET_UNDISPENSES;
336
337 END LOOP;
338 close c_get_step_materials;
339
340 -- Chenck for NCM
341 select count(*) into l_ncm_count
342 from qa_results_v
343 where PROCESS_BATCHSTEP_ID = p_entity_key
344 and NONCONFORMANCE_STATUS <> 'CLOSED';
345 IF l_ncm_count > 0 THEN
346 raise NCM_NOT_CLOSED;
347 END IF;
348
349 END IF;
350
351
352
353 X_RETURN_STATUS := 'S';
354 X_VALIDATION_STATUS := 'S';
355 X_VALIDATION_ID := 1000;
356 EXCEPTION WHEN INSTRUCTIONS_PENDING THEN
357 FND_MESSAGE.SET_NAME('GMO','GMO_INSTRUCTION_PENDING');
358 FND_MSG_PUB.ADD;
359 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
360 X_RETURN_STATUS := 'E';
361 X_VALIDATION_STATUS := 'E';
362 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
363 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
364 'gmo.plsql.GMO_VALIDATE_BATCH_GRP.VALIDATE_BATCH_COMPLIANCE',
365 FALSE
366 );
367 end if;
368 WHEN NCM_NOT_CLOSED THEN
369 FND_MESSAGE.SET_NAME('GMO','GMO_NOT_ALL_NCMS_CLOSED');
370 FND_MSG_PUB.ADD;
371 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
372 X_RETURN_STATUS := 'E';
373 X_VALIDATION_STATUS := 'E';
374 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
375 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
376 'gmo.plsql.GMO_VALIDATE_BATCH_GRP.VALIDATE_BATCH_COMPLIANCE',
377 FALSE
378 );
379 end if;
380 WHEN INSTRUNCTION_EXCEPTION THEN
381 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
382 X_RETURN_STATUS := 'E';
383 X_VALIDATION_STATUS := 'E';
384 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
385 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
386 'gmo.plsql.GMO_VALIDATE_BATCH_GRP.VALIDATE_BATCH_COMPLIANCE',
387 FALSE
388 );
389 end if;
390 END VALIDATE_BATCH_COMPLIANCE;
391
392 END GMO_VALIDATE_BATCH_GRP;