1 PACKAGE BODY AHL_PRD_OPERATIONS_PVT AS
2 /* $Header: AHLVPROB.pls 120.19.12010000.2 2008/12/17 00:48:16 sikumar ship $ */
3
4 G_PKG_NAME VARCHAR2(30) := 'AHL_PRD_OPERATIONS_PVT';
5 G_DEBUG VARCHAR2(1) := AHL_DEBUG_PUB.is_log_enabled;
6
7 FUNCTION get_date_and_time(p_date IN DATE,
8 p_date_hh24 IN VARCHAR2,
9 p_date_mi IN VARCHAR2,
10 p_date_ss IN VARCHAR2) RETURN DATE;
11
12 PROCEDURE default_attributes
13 (
14 p_x_prd_workoper_rec IN OUT NOCOPY AHL_PRD_OPERATIONS_PVT.prd_workoperation_rec
15 )
16 AS
17
18 CURSOR get_workorder_rec(c_workorder_id NUMBER)
19 IS
20 SELECT WO.route_id,
21 WO.wip_entity_id,
22 VST.organization_id,
23 WDJ.scheduled_start_date,
24 WDJ.scheduled_completion_date,
25 WO.actual_start_date,
26 WO.actual_end_date
27 FROM AHL_WORKORDERS WO,
28 AHL_VISITS_B VST,
29 WIP_DISCRETE_JOBS WDJ
30 WHERE WO.workorder_id =c_workorder_id
31 AND WO.status_code <> '22'
32 AND WO.visit_id =VST.visit_id
33 AND WO.wip_entity_id =WDJ.wip_entity_id (+);
34
35 l_job_organization_id NUMBER;
36 l_job_wip_entity_id NUMBER;
37 l_job_route_id NUMBER;
38 l_job_scheduled_start_date DATE;
39 l_job_scheduled_end_date DATE;
40 l_job_actual_start_date DATE;
41 l_job_actual_end_date DATE;
42
43 CURSOR get_operation_details(c_operation_code VARCHAR2)
44 IS
45 SELECT OP.operation_id,
46 OP.description,
47 OP.qa_inspection_type
48 FROM AHL_OPERATIONS_VL OP
49 WHERE OP.concatenated_segments=c_operation_code
50 AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(OP.start_date_active,SYSDATE))
51 AND TRUNC(NVL(OP.end_date_active,SYSDATE+1))
52 AND OP.revision_status_code='COMPLETE'
53 AND OP.revision_number IN
54 ( SELECT MAX(revision_number)
55 FROM AHL_OPERATIONS_B_KFV
56 WHERE concatenated_segments=OP.concatenated_segments
57 AND TRUNC(SYSDATE) BETWEEN TRUNC(start_date_active) AND
58 TRUNC(NVL(end_date_active,SYSDATE+1))
59 );
60 l_qa_inspection_type VARCHAR2(150);
61 l_description VARCHAR2(500);
62
63 l_msg_count NUMBER;
64 l_msg_data VARCHAR2(2000);
65 l_return_status VARCHAR2(1);
66
67 BEGIN
68
69 IF ( p_x_prd_workoper_rec.operation_code IS NOT NULL ) THEN
70
71 IF ( G_DEBUG = 'Y' ) THEN
72 AHL_DEBUG_PUB.debug( 'Job Operation is based on Route Management Operation' );
73 END IF;
74
75 OPEN get_operation_details(p_x_prd_workoper_rec.operation_code);
76 FETCH get_operation_details
77 INTO p_x_prd_workoper_rec.operation_id,
78 l_description,
79 l_qa_inspection_type;
80 CLOSE get_operation_details;
81
82 IF p_x_prd_workoper_rec.operation_description is NULL THEN
83 p_x_prd_workoper_rec.operation_description:=l_description;
84 END IF;
85 END IF;
86
87 SELECT ahl_workorder_operations_s.NEXTVAL
88 INTO p_x_prd_workoper_rec.workorder_operation_id
89 FROM DUAL;
90
91 p_x_prd_workoper_rec.LAST_UPDATE_DATE :=SYSDATE;
92 p_x_prd_workoper_rec.LAST_UPDATED_BY :=FND_GLOBAL.user_id;
93 p_x_prd_workoper_rec.CREATION_DATE :=SYSDATE;
94 p_x_prd_workoper_rec.CREATED_BY :=FND_GLOBAL.user_id;
95 p_x_prd_workoper_rec.LAST_UPDATE_LOGIN :=FND_GLOBAL.user_id;
96 p_x_prd_workoper_rec.OBJECT_VERSION_NUMBER:=1;
97 p_x_prd_workoper_rec.STATUS_CODE :='2';
98
99 OPEN get_workorder_rec (p_x_prd_workoper_rec.workorder_id);
100 FETCH get_workorder_rec
101 INTO l_job_route_id,
102 l_job_wip_entity_id,
103 l_job_organization_id,
104 l_job_scheduled_start_date,
105 l_job_scheduled_end_date,
106 l_job_actual_start_date,
107 l_job_actual_end_date;
108 CLOSE get_workorder_rec;
109
110 IF ( ( p_x_prd_workoper_rec.wip_entity_id IS NULL OR
111 p_x_prd_workoper_rec.wip_entity_id = FND_API.G_MISS_NUM ) AND
112 l_job_wip_entity_id IS NOT NULL ) THEN
113 p_x_prd_workoper_rec.wip_entity_id := l_job_wip_entity_id;
114 END IF;
115
116 IF ( p_x_prd_workoper_rec.organization_id IS NULL OR
117 p_x_prd_workoper_rec.organization_id = FND_API.G_MISS_NUM ) THEN
118 p_x_prd_workoper_rec.organization_id := l_job_organization_id;
119 END IF;
120
121 IF ( G_DEBUG = 'Y' ) THEN
122 AHL_DEBUG_PUB.debug( 'Job Route ID :'||l_job_route_id );
123 AHL_DEBUG_PUB.debug( 'Operation Code :'|| p_x_prd_workoper_rec.operation_code );
124 AHL_DEBUG_PUB.debug( 'Operation ID :'|| p_x_prd_workoper_rec.operation_id );
125 END IF;
126
127 IF ( l_job_route_id IS NULL AND
128 p_x_prd_workoper_rec.operation_id IS NULL ) THEN
129 l_qa_inspection_type:=fnd_profile.value('AHL_NR_WO_OP_PLAN_TYPE');
130
131 IF ( G_DEBUG = 'Y' ) THEN
132 AHL_DEBUG_PUB.debug( 'Defaulting QA Inspection Type for Operation from Profile' );
133 END IF;
134
135 END IF;
136
137 IF l_qa_inspection_type IS NOT NULL THEN
138 AHL_QA_RESULTS_PVT.get_qa_plan
139 (
140 p_api_version => 1.0,
141 p_init_msg_list => FND_API.G_FALSE,
142 p_commit => FND_API.G_FALSE,
143 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
144 p_default => FND_API.G_FALSE,
145 p_module_type => NULL,
146 x_return_status => l_return_status,
147 x_msg_count => l_msg_count,
148 x_msg_data => l_msg_data,
149 p_organization_id => p_x_prd_workoper_rec.organization_id,
150 p_transaction_number => 2002,
151 p_col_trigger_value => l_qa_inspection_type,
152 x_plan_id => p_x_prd_workoper_rec.plan_id
153 );
154 END IF;
155
156 IF ( p_x_prd_workoper_rec.scheduled_start_date IS NOT NULL AND
157 p_x_prd_workoper_rec.scheduled_start_date <> FND_API.G_MISS_DATE AND
158 l_job_scheduled_start_date IS NOT NULL AND
159 TRUNC( p_x_prd_workoper_rec.scheduled_start_date ) = TRUNC( l_job_scheduled_start_date ) ) THEN
160 p_x_prd_workoper_rec.scheduled_start_date := l_job_scheduled_start_date;
161 IF ( p_x_prd_workoper_rec.scheduled_start_hr IS NULL OR
162 p_x_prd_workoper_rec.scheduled_start_hr = FND_API.G_MISS_NUM)
163 THEN
164 p_x_prd_workoper_rec.scheduled_start_hr := TO_NUMBER( TO_CHAR( l_job_scheduled_start_date, 'HH24' ) );
165 END IF;
166
167 IF( p_x_prd_workoper_rec.scheduled_start_mi IS NULL OR
168 p_x_prd_workoper_rec.scheduled_start_mi = FND_API.G_MISS_NUM)
169 THEN
170 p_x_prd_workoper_rec.scheduled_start_mi := TO_NUMBER( TO_CHAR( l_job_scheduled_start_date, 'MI' ) );
171 END IF;
172 END IF;
173
174 IF ( p_x_prd_workoper_rec.scheduled_end_date IS NOT NULL AND
175 p_x_prd_workoper_rec.scheduled_end_date <> FND_API.G_MISS_DATE AND
176 l_job_scheduled_end_date IS NOT NULL AND
177 TRUNC( p_x_prd_workoper_rec.scheduled_end_date ) = TRUNC( l_job_scheduled_end_date ) ) THEN
178 p_x_prd_workoper_rec.scheduled_end_date := l_job_scheduled_end_date;
179 IF ( p_x_prd_workoper_rec.scheduled_end_hr IS NULL OR
180 p_x_prd_workoper_rec.scheduled_end_hr = FND_API.G_MISS_NUM)
181 THEN
182 p_x_prd_workoper_rec.scheduled_end_hr := TO_NUMBER( TO_CHAR( l_job_scheduled_end_date, 'HH24' ) );
183 END IF;
184
185 IF ( p_x_prd_workoper_rec.scheduled_end_mi IS NULL OR
186 p_x_prd_workoper_rec.scheduled_end_mi = FND_API.G_MISS_NUM)
187 THEN
188 p_x_prd_workoper_rec.scheduled_end_mi := TO_NUMBER( TO_CHAR( l_job_scheduled_end_date, 'MI' ) );
189 END IF;
190 ELSIF ( p_x_prd_workoper_rec.scheduled_end_date IS NOT NULL AND
191 p_x_prd_workoper_rec.scheduled_end_date <> FND_API.G_MISS_DATE AND
192 l_job_scheduled_start_date IS NOT NULL AND
193 TRUNC( p_x_prd_workoper_rec.scheduled_end_date ) = TRUNC( l_job_scheduled_start_date ) ) THEN
194 p_x_prd_workoper_rec.scheduled_end_date := l_job_scheduled_start_date;
195 IF ( p_x_prd_workoper_rec.scheduled_end_hr IS NULL OR
196 p_x_prd_workoper_rec.scheduled_end_hr = FND_API.G_MISS_NUM)
197 THEN
198 p_x_prd_workoper_rec.scheduled_end_hr := TO_NUMBER( TO_CHAR( l_job_scheduled_start_date, 'HH24' ) );
199 END IF;
200
201 IF ( p_x_prd_workoper_rec.scheduled_end_mi IS NULL OR
202 p_x_prd_workoper_rec.scheduled_end_mi = FND_API.G_MISS_NUM)
203 THEN
204 p_x_prd_workoper_rec.scheduled_end_mi := TO_NUMBER( TO_CHAR( l_job_scheduled_start_date, 'MI' ) );
205 END IF;
206 END IF;
207
208 IF ( p_x_prd_workoper_rec.actual_start_date IS NOT NULL AND
209 p_x_prd_workoper_rec.actual_start_date <> FND_API.G_MISS_DATE AND
210 l_job_actual_start_date IS NOT NULL AND
211 TRUNC( p_x_prd_workoper_rec.actual_start_date ) = TRUNC( l_job_actual_start_date ) ) THEN
212 p_x_prd_workoper_rec.actual_start_date := l_job_actual_start_date;
213 p_x_prd_workoper_rec.actual_start_hr := TO_NUMBER( TO_CHAR( l_job_actual_start_date, 'HH24' ) );
214 p_x_prd_workoper_rec.actual_start_mi := TO_NUMBER( TO_CHAR( l_job_actual_start_date, 'MI' ) );
215 END IF;
216
217 IF ( p_x_prd_workoper_rec.actual_end_date IS NOT NULL AND
218 p_x_prd_workoper_rec.actual_end_date <> FND_API.G_MISS_DATE AND
219 l_job_actual_end_date IS NOT NULL AND
220 TRUNC( p_x_prd_workoper_rec.actual_end_date ) = TRUNC( l_job_actual_end_date ) ) THEN
221 p_x_prd_workoper_rec.actual_end_date := l_job_actual_end_date;
222 p_x_prd_workoper_rec.actual_end_hr := TO_NUMBER( TO_CHAR( l_job_actual_end_date, 'HH24' ) );
223 p_x_prd_workoper_rec.actual_end_mi := TO_NUMBER( TO_CHAR( l_job_scheduled_end_date, 'MI' ) );
224 ELSIF ( p_x_prd_workoper_rec.actual_end_date IS NOT NULL AND
225 p_x_prd_workoper_rec.actual_end_date <> FND_API.G_MISS_DATE AND
226 l_job_actual_start_date IS NOT NULL AND
227 TRUNC( p_x_prd_workoper_rec.actual_end_date ) = TRUNC( l_job_actual_start_date ) ) THEN
228 p_x_prd_workoper_rec.actual_end_date := l_job_actual_start_date;
229 p_x_prd_workoper_rec.actual_end_hr := TO_NUMBER( TO_CHAR( l_job_actual_start_date, 'HH24' ) );
230 p_x_prd_workoper_rec.actual_end_mi := TO_NUMBER( TO_CHAR( l_job_actual_start_date, 'MI' ) );
231 END IF;
232
233 END default_attributes;
234
235 PROCEDURE default_missing_attributes
236 (
237 p_x_prd_workoper_rec IN OUT NOCOPY AHL_PRD_OPERATIONS_PVT.PRD_workoperation_rec,
238 p_module_type IN VARCHAR2
239 )
240 AS
241 cursor get_operation_rec(c_operation_id NUMBER)
242 is
243 SELECT *
244 FROM AHL_WORKORDER_OPERATIONS_V
245 WHERE workorder_operation_id=c_operation_id;
246
247 l_old_operation_rec get_operation_rec%ROWTYPE;
248
249 BEGIN
250 OPEN get_operation_rec(p_x_prd_workoper_rec.WORKORDER_OPERATION_ID);
251 FETCH get_operation_rec INTO l_old_operation_rec;
252 CLOSE get_operation_rec;
253 IF(p_module_type IS NULL OR p_module_type NOT IN ('OAF','JSP'))THEN
254 IF p_x_prd_workoper_rec.SCHEDULED_START_DATE=FND_API.G_MISS_DATE THEN
255 p_x_prd_workoper_rec.SCHEDULED_START_DATE:=NULL;
256 ELSIF p_x_prd_workoper_rec.SCHEDULED_START_DATE IS NULL THEN
257 p_x_prd_workoper_rec.SCHEDULED_START_DATE:=l_old_operation_rec.SCHEDULED_START_DATE;
258 END IF;
259 IF p_x_prd_workoper_rec.SCHEDULED_START_HR=FND_API.G_MISS_NUM THEN
260 p_x_prd_workoper_rec.SCHEDULED_START_HR:=NULL;
261 ELSIF p_x_prd_workoper_rec.SCHEDULED_START_HR IS NULL THEN
262 p_x_prd_workoper_rec.SCHEDULED_START_HR:=l_old_operation_rec.SCHEDULED_START_HR;
263 END IF;
264
265 IF p_x_prd_workoper_rec.SCHEDULED_START_MI=FND_API.G_MISS_NUM THEN
266 p_x_prd_workoper_rec.SCHEDULED_START_MI:=NULL;
267 ELSIF p_x_prd_workoper_rec.SCHEDULED_START_MI IS NULL THEN
268 p_x_prd_workoper_rec.SCHEDULED_START_MI:=l_old_operation_rec.SCHEDULED_START_MI;
269 END IF;
270
271 IF p_x_prd_workoper_rec.SCHEDULED_END_DATE=FND_API.G_MISS_DATE THEN
272 p_x_prd_workoper_rec.SCHEDULED_END_DATE:=NULL;
273 ELSIF p_x_prd_workoper_rec.SCHEDULED_END_DATE IS NULL THEN
274 p_x_prd_workoper_rec.SCHEDULED_END_DATE:=l_old_operation_rec.SCHEDULED_END_DATE;
275 END IF;
276
277 IF p_x_prd_workoper_rec.SCHEDULED_END_HR=FND_API.G_MISS_NUM THEN
278 p_x_prd_workoper_rec.SCHEDULED_END_HR:=NULL;
279 ELSIF p_x_prd_workoper_rec.SCHEDULED_END_HR IS NULL THEN
280 p_x_prd_workoper_rec.SCHEDULED_END_HR:=l_old_operation_rec.SCHEDULED_END_HR;
281 END IF;
282
283 IF p_x_prd_workoper_rec.SCHEDULED_END_MI=FND_API.G_MISS_NUM THEN
284 p_x_prd_workoper_rec.SCHEDULED_END_MI:=NULL;
285 ELSIF p_x_prd_workoper_rec.SCHEDULED_END_MI IS NULL THEN
286 p_x_prd_workoper_rec.SCHEDULED_END_MI:=l_old_operation_rec.SCHEDULED_END_MI;
287 END IF;
288
289 IF p_x_prd_workoper_rec.ACTUAL_START_DATE=FND_API.G_MISS_DATE THEN
290 p_x_prd_workoper_rec.ACTUAL_START_DATE:=NULL;
291 ELSIF p_x_prd_workoper_rec.ACTUAL_START_DATE IS NULL THEN
292 p_x_prd_workoper_rec.ACTUAL_START_DATE:=l_old_operation_rec.ACTUAL_START_DATE;
293 END IF;
294
295 IF p_x_prd_workoper_rec.ACTUAL_START_HR=FND_API.G_MISS_NUM THEN
296 p_x_prd_workoper_rec.ACTUAL_START_HR:=NULL;
297 ELSIF p_x_prd_workoper_rec.ACTUAL_START_HR IS NULL THEN
298 p_x_prd_workoper_rec.ACTUAL_START_HR:=l_old_operation_rec.ACTUAL_START_HR;
299 END IF;
300
301 IF p_x_prd_workoper_rec.ACTUAL_START_MI=FND_API.G_MISS_NUM THEN
302 p_x_prd_workoper_rec.ACTUAL_START_MI:=NULL;
303 ELSIF p_x_prd_workoper_rec.ACTUAL_START_MI IS NULL THEN
304 p_x_prd_workoper_rec.ACTUAL_START_MI:=l_old_operation_rec.ACTUAL_START_MI;
305 END IF;
306
307 IF p_x_prd_workoper_rec.ACTUAL_END_DATE=FND_API.G_MISS_DATE THEN
308 p_x_prd_workoper_rec.ACTUAL_END_DATE:=NULL;
309 ELSIF p_x_prd_workoper_rec.ACTUAL_END_DATE IS NULL THEN
310 p_x_prd_workoper_rec.ACTUAL_END_DATE:=l_old_operation_rec.ACTUAL_END_DATE;
311 END IF;
312
313 IF p_x_prd_workoper_rec.ACTUAL_END_HR=FND_API.G_MISS_NUM THEN
314 p_x_prd_workoper_rec.ACTUAL_END_HR:=NULL;
315 ELSIF p_x_prd_workoper_rec.ACTUAL_END_HR IS NULL THEN
316 p_x_prd_workoper_rec.ACTUAL_END_HR:=l_old_operation_rec.ACTUAL_END_HR;
317 END IF;
318
319 IF p_x_prd_workoper_rec.ACTUAL_END_MI=FND_API.G_MISS_NUM THEN
320 p_x_prd_workoper_rec.ACTUAL_END_MI:=NULL;
321 ELSIF p_x_prd_workoper_rec.ACTUAL_END_MI IS NULL THEN
322 p_x_prd_workoper_rec.ACTUAL_END_MI:=l_old_operation_rec.ACTUAL_END_MI;
323 END IF;
324 ELSIF p_module_type = 'JSP' THEN
325 IF p_x_prd_workoper_rec.SCHEDULED_START_DATE IS NOT NULL THEN
326 IF p_x_prd_workoper_rec.SCHEDULED_START_HR=FND_API.G_MISS_NUM THEN
327 p_x_prd_workoper_rec.SCHEDULED_START_HR:=NULL;
328 ELSIF p_x_prd_workoper_rec.SCHEDULED_START_HR IS NULL THEN
329 p_x_prd_workoper_rec.SCHEDULED_START_HR:=l_old_operation_rec.SCHEDULED_START_HR;
330 END IF;
331 IF p_x_prd_workoper_rec.SCHEDULED_START_MI=FND_API.G_MISS_NUM THEN
332 p_x_prd_workoper_rec.SCHEDULED_START_MI:=NULL;
333 ELSIF p_x_prd_workoper_rec.SCHEDULED_START_MI IS NULL THEN
334 p_x_prd_workoper_rec.SCHEDULED_START_MI:=l_old_operation_rec.SCHEDULED_START_MI;
335 END IF;
336 END IF;
337
338 IF p_x_prd_workoper_rec.SCHEDULED_END_DATE IS NOT NULL THEN
339 IF p_x_prd_workoper_rec.SCHEDULED_END_HR=FND_API.G_MISS_NUM THEN
340 p_x_prd_workoper_rec.SCHEDULED_END_HR:=NULL;
341 ELSIF p_x_prd_workoper_rec.SCHEDULED_END_HR IS NULL THEN
342 p_x_prd_workoper_rec.SCHEDULED_END_HR:=l_old_operation_rec.SCHEDULED_END_HR;
343 END IF;
344 IF p_x_prd_workoper_rec.SCHEDULED_END_MI=FND_API.G_MISS_NUM THEN
345 p_x_prd_workoper_rec.SCHEDULED_END_MI:=NULL;
346 ELSIF p_x_prd_workoper_rec.SCHEDULED_END_MI IS NULL THEN
347 p_x_prd_workoper_rec.SCHEDULED_END_MI:=l_old_operation_rec.SCHEDULED_END_MI;
348 END IF;
349 END IF;
350
351 IF p_x_prd_workoper_rec.ACTUAL_START_DATE IS NOT NULL THEN
352 IF p_x_prd_workoper_rec.ACTUAL_START_HR=FND_API.G_MISS_NUM THEN
353 p_x_prd_workoper_rec.ACTUAL_START_HR:=NULL;
354 ELSIF p_x_prd_workoper_rec.ACTUAL_START_HR IS NULL THEN
355 p_x_prd_workoper_rec.ACTUAL_START_HR:=l_old_operation_rec.ACTUAL_START_HR;
356 END IF;
357
358 IF p_x_prd_workoper_rec.ACTUAL_START_MI=FND_API.G_MISS_NUM THEN
359 p_x_prd_workoper_rec.ACTUAL_START_MI:=NULL;
360 ELSIF p_x_prd_workoper_rec.ACTUAL_START_MI IS NULL THEN
361 p_x_prd_workoper_rec.ACTUAL_START_MI:=l_old_operation_rec.ACTUAL_START_MI;
362 END IF;
363 END IF;
364
365 IF p_x_prd_workoper_rec.ACTUAL_END_DATE IS NOT NULL THEN
366 IF p_x_prd_workoper_rec.ACTUAL_END_HR=FND_API.G_MISS_NUM THEN
367 p_x_prd_workoper_rec.ACTUAL_END_HR:=NULL;
368 ELSIF p_x_prd_workoper_rec.ACTUAL_END_HR IS NULL THEN
369 p_x_prd_workoper_rec.ACTUAL_END_HR:=l_old_operation_rec.ACTUAL_END_HR;
370 END IF;
371 IF p_x_prd_workoper_rec.ACTUAL_END_MI=FND_API.G_MISS_NUM THEN
372 p_x_prd_workoper_rec.ACTUAL_END_MI:=NULL;
373 ELSIF p_x_prd_workoper_rec.ACTUAL_END_MI IS NULL THEN
374 p_x_prd_workoper_rec.ACTUAL_END_MI:=l_old_operation_rec.ACTUAL_END_MI;
375 END IF;
376 END IF;
377 END IF;
378
379 IF p_x_prd_workoper_rec.DEPARTMENT_ID= FND_API.G_MISS_NUM THEN
380 p_x_prd_workoper_rec.DEPARTMENT_ID:=NULL;
381 ELSIF p_x_prd_workoper_rec.DEPARTMENT_ID IS NULL THEN
382 p_x_prd_workoper_rec.DEPARTMENT_ID:=l_old_operation_rec.DEPARTMENT_ID;
383 END IF;
384
385 IF p_x_prd_workoper_rec.DEPARTMENT_NAME= FND_API.G_MISS_CHAR THEN
386 p_x_prd_workoper_rec.DEPARTMENT_NAME:=NULL;
387 ELSIF p_x_prd_workoper_rec.DEPARTMENT_NAME IS NULL THEN
388 p_x_prd_workoper_rec.DEPARTMENT_NAME:=l_old_operation_rec.DEPARTMENT_NAME;
389 END IF;
390
391 IF p_x_prd_workoper_rec.STATUS_CODE= FND_API.G_MISS_CHAR THEN
392 p_x_prd_workoper_rec.STATUS_CODE:=NULL;
393 ELSIF p_x_prd_workoper_rec.STATUS_CODE IS NULL THEN
394 p_x_prd_workoper_rec.STATUS_CODE:=l_old_operation_rec.STATUS_CODE;
395 END IF;
396
397 IF p_x_prd_workoper_rec.STATUS_MEANING= FND_API.G_MISS_CHAR THEN
398 p_x_prd_workoper_rec.STATUS_MEANING:=NULL;
399 ELSIF p_x_prd_workoper_rec.STATUS_MEANING IS NULL THEN
400 p_x_prd_workoper_rec.STATUS_MEANING:=l_old_operation_rec.STATUS;
401 END IF;
402
403 IF p_x_prd_workoper_rec.OPERATION_ID= FND_API.G_MISS_NUM THEN
404 p_x_prd_workoper_rec.OPERATION_ID:=NULL;
405 ELSIF p_x_prd_workoper_rec.OPERATION_ID IS NULL THEN
406 p_x_prd_workoper_rec.OPERATION_ID:=l_old_operation_rec.OPERATION_ID;
407 END IF;
408
409 IF p_x_prd_workoper_rec.OPERATION_CODE= FND_API.G_MISS_CHAR THEN
410 p_x_prd_workoper_rec.OPERATION_CODE:=NULL;
411 ELSIF p_x_prd_workoper_rec.OPERATION_CODE IS NULL THEN
412 p_x_prd_workoper_rec.OPERATION_CODE:=l_old_operation_rec.OPERATION_CODE;
413 END IF;
414
415 IF p_x_prd_workoper_rec.OPERATION_TYPE_CODE= FND_API.G_MISS_CHAR THEN
416 p_x_prd_workoper_rec.OPERATION_TYPE_CODE:=NULL;
417 ELSIF p_x_prd_workoper_rec.OPERATION_TYPE_CODE IS NULL THEN
418 p_x_prd_workoper_rec.OPERATION_TYPE_CODE:=l_old_operation_rec.OPERATION_TYPE_CODE;
419 END IF;
420
421 IF p_x_prd_workoper_rec.OPERATION_TYPE= FND_API.G_MISS_CHAR THEN
422 p_x_prd_workoper_rec.OPERATION_TYPE:=NULL;
423 ELSIF p_x_prd_workoper_rec.OPERATION_TYPE IS NULL THEN
424 p_x_prd_workoper_rec.OPERATION_TYPE:=l_old_operation_rec.OPERATION_TYPE;
425 END IF;
426
427 IF p_x_prd_workoper_rec.PLAN_ID= FND_API.G_MISS_NUM THEN
428 p_x_prd_workoper_rec.PLAN_ID:=NULL;
429 ELSIF p_x_prd_workoper_rec.PLAN_ID IS NULL THEN
430 p_x_prd_workoper_rec.PLAN_ID:=l_old_operation_rec.PLAN_ID;
431 END IF;
432
433 IF p_x_prd_workoper_rec.COLLECTION_ID= FND_API.G_MISS_NUM THEN
434 p_x_prd_workoper_rec.COLLECTION_ID:=NULL;
435 ELSIF p_x_prd_workoper_rec.COLLECTION_ID IS NULL THEN
436 p_x_prd_workoper_rec.COLLECTION_ID:=l_old_operation_rec.COLLECTION_ID;
437 END IF;
438
439 IF p_x_prd_workoper_rec.OPERATION_DESCRIPTION= FND_API.G_MISS_CHAR THEN
440 p_x_prd_workoper_rec.OPERATION_DESCRIPTION:=NULL;
441 ELSIF p_x_prd_workoper_rec.OPERATION_DESCRIPTION IS NULL THEN
442 p_x_prd_workoper_rec.OPERATION_DESCRIPTION:=l_old_operation_rec.DESCRIPTION;
443 END IF;
444
445 IF p_x_prd_workoper_rec.ATTRIBUTE_CATEGORY= FND_API.G_MISS_CHAR THEN
446 p_x_prd_workoper_rec.ATTRIBUTE_CATEGORY:=NULL;
447 ELSIF p_x_prd_workoper_rec.ATTRIBUTE_CATEGORY IS NULL THEN
448 p_x_prd_workoper_rec.ATTRIBUTE_CATEGORY:=l_old_operation_rec.ATTRIBUTE_CATEGORY;
449 END IF;
450
451 IF p_x_prd_workoper_rec.ATTRIBUTE1= FND_API.G_MISS_CHAR THEN
452 p_x_prd_workoper_rec.ATTRIBUTE1:=NULL;
453 ELSIF p_x_prd_workoper_rec.ATTRIBUTE1 IS NULL THEN
454 p_x_prd_workoper_rec.ATTRIBUTE1:=l_old_operation_rec.ATTRIBUTE1;
455 END IF;
456
457 IF p_x_prd_workoper_rec.ATTRIBUTE2= FND_API.G_MISS_CHAR THEN
458 p_x_prd_workoper_rec.ATTRIBUTE2:=NULL;
459 ELSIF p_x_prd_workoper_rec.ATTRIBUTE2 IS NULL THEN
460 p_x_prd_workoper_rec.ATTRIBUTE2:=l_old_operation_rec.ATTRIBUTE2;
461 END IF;
462
463 IF p_x_prd_workoper_rec.ATTRIBUTE3= FND_API.G_MISS_CHAR THEN
464 p_x_prd_workoper_rec.ATTRIBUTE3:=NULL;
465 ELSIF p_x_prd_workoper_rec.ATTRIBUTE3 IS NULL THEN
466 p_x_prd_workoper_rec.ATTRIBUTE3:=l_old_operation_rec.ATTRIBUTE3;
467 END IF;
468
469 IF p_x_prd_workoper_rec.ATTRIBUTE4= FND_API.G_MISS_CHAR THEN
470 p_x_prd_workoper_rec.ATTRIBUTE4:=NULL;
471 ELSIF p_x_prd_workoper_rec.ATTRIBUTE4 IS NULL THEN
472 p_x_prd_workoper_rec.ATTRIBUTE4:=l_old_operation_rec.ATTRIBUTE4;
473 END IF;
474
475 IF p_x_prd_workoper_rec.ATTRIBUTE5= FND_API.G_MISS_CHAR THEN
476 p_x_prd_workoper_rec.ATTRIBUTE5:=NULL;
477 ELSIF p_x_prd_workoper_rec.ATTRIBUTE5 IS NULL THEN
478 p_x_prd_workoper_rec.ATTRIBUTE5:=l_old_operation_rec.ATTRIBUTE5;
479 END IF;
480
481 IF p_x_prd_workoper_rec.ATTRIBUTE6= FND_API.G_MISS_CHAR THEN
482 p_x_prd_workoper_rec.ATTRIBUTE6:=NULL;
483 ELSIF p_x_prd_workoper_rec.ATTRIBUTE6 IS NULL THEN
484 p_x_prd_workoper_rec.ATTRIBUTE6:=l_old_operation_rec.ATTRIBUTE6;
485 END IF;
486
487 IF p_x_prd_workoper_rec.ATTRIBUTE7= FND_API.G_MISS_CHAR THEN
488 p_x_prd_workoper_rec.ATTRIBUTE7:=NULL;
489 ELSIF p_x_prd_workoper_rec.ATTRIBUTE7 IS NULL THEN
490 p_x_prd_workoper_rec.ATTRIBUTE7:=l_old_operation_rec.ATTRIBUTE7;
491 END IF;
492
493 IF p_x_prd_workoper_rec.ATTRIBUTE8= FND_API.G_MISS_CHAR THEN
494 p_x_prd_workoper_rec.ATTRIBUTE8:=NULL;
495 ELSIF p_x_prd_workoper_rec.ATTRIBUTE8 IS NULL THEN
496 p_x_prd_workoper_rec.ATTRIBUTE8:=l_old_operation_rec.ATTRIBUTE8;
497 END IF;
498
499 IF p_x_prd_workoper_rec.ATTRIBUTE9= FND_API.G_MISS_CHAR THEN
500 p_x_prd_workoper_rec.ATTRIBUTE9:=NULL;
501 ELSIF p_x_prd_workoper_rec.ATTRIBUTE9 IS NULL THEN
502 p_x_prd_workoper_rec.ATTRIBUTE9:=l_old_operation_rec.ATTRIBUTE9;
503 END IF;
504
505 IF p_x_prd_workoper_rec.ATTRIBUTE10= FND_API.G_MISS_CHAR THEN
506 p_x_prd_workoper_rec.ATTRIBUTE10:=NULL;
507 ELSIF p_x_prd_workoper_rec.ATTRIBUTE10 IS NULL THEN
508 p_x_prd_workoper_rec.ATTRIBUTE10:=l_old_operation_rec.ATTRIBUTE10;
509 END IF;
510
511 IF p_x_prd_workoper_rec.ATTRIBUTE11= FND_API.G_MISS_CHAR THEN
512 p_x_prd_workoper_rec.ATTRIBUTE11:=NULL;
513 ELSIF p_x_prd_workoper_rec.ATTRIBUTE11 IS NULL THEN
514 p_x_prd_workoper_rec.ATTRIBUTE11:=l_old_operation_rec.ATTRIBUTE11;
515 END IF;
516
517 IF p_x_prd_workoper_rec.ATTRIBUTE12= FND_API.G_MISS_CHAR THEN
518 p_x_prd_workoper_rec.ATTRIBUTE12:=NULL;
519 ELSIF p_x_prd_workoper_rec.ATTRIBUTE12 IS NULL THEN
520 p_x_prd_workoper_rec.ATTRIBUTE12:=l_old_operation_rec.ATTRIBUTE12;
521 END IF;
522
523 IF p_x_prd_workoper_rec.ATTRIBUTE13= FND_API.G_MISS_CHAR THEN
524 p_x_prd_workoper_rec.ATTRIBUTE13:=NULL;
525 ELSIF p_x_prd_workoper_rec.ATTRIBUTE13 IS NULL THEN
526 p_x_prd_workoper_rec.ATTRIBUTE13:=l_old_operation_rec.ATTRIBUTE13;
527 END IF;
528
529 IF p_x_prd_workoper_rec.ATTRIBUTE14= FND_API.G_MISS_CHAR THEN
530 p_x_prd_workoper_rec.ATTRIBUTE14:=NULL;
531 ELSIF p_x_prd_workoper_rec.ATTRIBUTE14 IS NULL THEN
532 p_x_prd_workoper_rec.ATTRIBUTE14:=l_old_operation_rec.ATTRIBUTE14;
533 END IF;
534
535 IF p_x_prd_workoper_rec.ATTRIBUTE15= FND_API.G_MISS_CHAR THEN
536 p_x_prd_workoper_rec.ATTRIBUTE15:=NULL;
537 ELSIF p_x_prd_workoper_rec.ATTRIBUTE15 IS NULL THEN
538 p_x_prd_workoper_rec.ATTRIBUTE15:=l_old_operation_rec.ATTRIBUTE15;
539 END IF;
540
541 IF p_x_prd_workoper_rec.WORKORDER_OPERATION_ID= FND_API.G_MISS_NUM THEN
542 p_x_prd_workoper_rec.WORKORDER_OPERATION_ID:=NULL;
543 ELSIF p_x_prd_workoper_rec.WORKORDER_OPERATION_ID IS NULL THEN
544 p_x_prd_workoper_rec.WORKORDER_OPERATION_ID:=l_old_operation_rec.WORKORDER_OPERATION_ID;
545 END IF;
546
547 IF p_x_prd_workoper_rec.ORGANIZATION_ID= FND_API.G_MISS_NUM THEN
548 p_x_prd_workoper_rec.ORGANIZATION_ID:=NULL;
549 ELSIF p_x_prd_workoper_rec.ORGANIZATION_ID IS NULL THEN
550 p_x_prd_workoper_rec.ORGANIZATION_ID:=l_old_operation_rec.ORGANIZATION_ID;
551 END IF;
552
553 IF p_x_prd_workoper_rec.OPERATION_SEQUENCE_NUM= FND_API.G_MISS_NUM THEN
554 p_x_prd_workoper_rec.OPERATION_SEQUENCE_NUM:=NULL;
555 ELSIF p_x_prd_workoper_rec.OPERATION_SEQUENCE_NUM IS NULL THEN
556 p_x_prd_workoper_rec.OPERATION_SEQUENCE_NUM:=l_old_operation_rec.OPERATION_SEQUENCE_NUM;
557 END IF;
558
559 IF p_x_prd_workoper_rec.WORKORDER_ID= FND_API.G_MISS_NUM THEN
560 p_x_prd_workoper_rec.WORKORDER_ID:=NULL;
561 ELSIF p_x_prd_workoper_rec.WORKORDER_ID IS NULL THEN
562 p_x_prd_workoper_rec.WORKORDER_ID:=l_old_operation_rec.WORKORDER_ID;
563 END IF;
564
565 IF p_x_prd_workoper_rec.WIP_ENTITY_ID= FND_API.G_MISS_NUM THEN
566 p_x_prd_workoper_rec.WIP_ENTITY_ID:=NULL;
567 ELSIF p_x_prd_workoper_rec.WIP_ENTITY_ID IS NULL THEN
568 p_x_prd_workoper_rec.WIP_ENTITY_ID:=l_old_operation_rec.WIP_ENTITY_ID;
569 END IF;
570
571 IF p_x_prd_workoper_rec.OBJECT_VERSION_NUMBER= FND_API.G_MISS_NUM THEN
572 p_x_prd_workoper_rec.OBJECT_VERSION_NUMBER:=NULL;
573 ELSIF p_x_prd_workoper_rec.OBJECT_VERSION_NUMBER IS NULL THEN
574 p_x_prd_workoper_rec.OBJECT_VERSION_NUMBER:=l_old_operation_rec.OBJECT_VERSION_NUMBER;
575 END IF;
576
577 IF p_x_prd_workoper_rec.LAST_UPDATE_DATE=FND_API.G_MISS_DATE THEN
578 p_x_prd_workoper_rec.LAST_UPDATE_DATE:=NULL;
579 ELSIF p_x_prd_workoper_rec.LAST_UPDATE_DATE IS NULL THEN
580 p_x_prd_workoper_rec.LAST_UPDATE_DATE:=l_old_operation_rec.LAST_UPDATE_DATE;
581 END IF;
582
583 IF p_x_prd_workoper_rec.LAST_UPDATED_BY= FND_API.G_MISS_NUM THEN
584 p_x_prd_workoper_rec.LAST_UPDATED_BY:=NULL;
585 ELSIF p_x_prd_workoper_rec.LAST_UPDATED_BY IS NULL THEN
586 p_x_prd_workoper_rec.LAST_UPDATED_BY:=l_old_operation_rec.LAST_UPDATED_BY;
587 END IF;
588
589 IF p_x_prd_workoper_rec.CREATION_DATE=FND_API.G_MISS_DATE THEN
590 p_x_prd_workoper_rec.CREATION_DATE:=NULL;
591 ELSIF p_x_prd_workoper_rec.CREATION_DATE IS NULL THEN
592 p_x_prd_workoper_rec.CREATION_DATE:=l_old_operation_rec.CREATION_DATE;
593 END IF;
594
595 IF p_x_prd_workoper_rec.CREATED_BY= FND_API.G_MISS_NUM THEN
596 p_x_prd_workoper_rec.CREATED_BY:=NULL;
597 ELSIF p_x_prd_workoper_rec.CREATED_BY IS NULL THEN
598 p_x_prd_workoper_rec.CREATED_BY:=l_old_operation_rec.CREATED_BY;
599 END IF;
600
601 IF p_x_prd_workoper_rec.LAST_UPDATE_LOGIN= FND_API.G_MISS_NUM THEN
602 p_x_prd_workoper_rec.LAST_UPDATE_LOGIN:=NULL;
603 ELSIF p_x_prd_workoper_rec.LAST_UPDATE_LOGIN IS NULL THEN
604 p_x_prd_workoper_rec.LAST_UPDATE_LOGIN:=l_old_operation_rec.LAST_UPDATE_LOGIN;
605 END IF;
606
607
608 END default_missing_attributes;
609
610 FUNCTION is_valid_operation_update(
611 p_operation_id IN NUMBER,
612 p_wo_op_id IN NUMBER,
613 p_operation_code IN VARCHAR2,
614 p_dml_operation IN VARCHAR2
615 )
616 RETURN NUMBER
617 IS
618
619 ---- declare cursors here----
620 -- cursor for getting operation code of existing work order operation
621 CURSOR c_get_wo_op(p_wo_op_id IN NUMBER)
622 IS
623 SELECT
624 rop.concatenated_segments
625 FROM
626 AHL_WORKORDER_OPERATIONS wop,
627 AHL_OPERATIONS_B_KFV rop
628 WHERE
629 rop.operation_id = wop.operation_id
630 AND wop.workorder_operation_id = p_wo_op_id;
631
632 -- cursor for getting latest revision of the operation
633 CURSOR get_operation(c_operation_code VARCHAR2)
634 IS
635 SELECT OP.operation_id
636 FROM AHL_OPERATIONS_B_KFV OP
637 WHERE OP.concatenated_segments=c_operation_code
638 AND OP.revision_number IN
639 ( SELECT MAX(OP1.revision_number)
640 FROM AHL_OPERATIONS_B_KFV OP1
641 WHERE OP1.concatenated_segments=OP.concatenated_segments
642 AND TRUNC(SYSDATE) BETWEEN TRUNC(OP1.start_date_active) AND
643 TRUNC(NVL(OP1.end_date_active,SYSDATE+1))
644 AND OP1.revision_status_code='COMPLETE'
645 );
646
647 ---- declare local variables here----
648 l_existing_op_code VARCHAR2(154);
649 l_operation_id NUMBER;
650
651 BEGIN
652
653 -- dump all the inputs
654
655 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
656 fnd_log.string
657 (
658 fnd_log.level_statement,
659 'ahl.plsql.AHL_PRD_OPERATIONS_PVT.is_valid_operation_update',
660 'p_operation_id : ' || p_operation_id
661 );
662 fnd_log.string
663 (
664 fnd_log.level_statement,
665 'ahl.plsql.AHL_PRD_OPERATIONS_PVT.is_valid_operation_update',
666 'p_wo_op_id : ' || p_wo_op_id
667 );
668 fnd_log.string
669 (
670 fnd_log.level_statement,
671 'ahl.plsql.AHL_PRD_OPERATIONS_PVT.is_valid_operation_update',
672 'p_operation_code : ' || p_operation_code
673 );
674 fnd_log.string
675 (
676 fnd_log.level_statement,
677 'ahl.plsql.AHL_PRD_OPERATIONS_PVT.is_valid_operation_update',
678 'p_dml_operation : ' || p_dml_operation
679 );
680 END IF;
681
682
683 l_operation_id := p_operation_id;
684
685 -- create case
686 IF (
687 p_dml_operation = 'C'
688 AND
689 p_operation_code IS NOT NULL
690 )
691 THEN
692
693 OPEN get_operation(p_operation_code);
694 FETCH get_operation INTO l_operation_id;
695 CLOSE get_operation;
696
697 -- update case
698 ELSIF (
699 p_dml_operation = 'U'
700 --AND p_operation_id IS NULL
701 AND
702 p_operation_code IS NOT NULL
703 AND
704 p_operation_code <> FND_API.G_MISS_CHAR
705 )
706 THEN
707 OPEN c_get_wo_op(p_wo_op_id);
708 FETCH c_get_wo_op INTO l_existing_op_code;
709 CLOSE c_get_wo_op;
710
711 IF nvl(l_existing_op_code, '-1') <> p_operation_code
712 THEN
713
714 OPEN get_operation(p_operation_code);
715 FETCH get_operation INTO l_operation_id;
716 CLOSE get_operation;
717
718 END IF;
719 END IF;
720
721 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
722 fnd_log.string
723 (
724 fnd_log.level_statement,
725 'ahl.plsql.AHL_PRD_OPERATIONS_PVT.is_valid_operation_update',
726 'Operation id returned -> l_operation_id : ' || l_operation_id
727 );
728 END IF;
729
730 RETURN l_operation_id;
731
732 END is_valid_operation_update;
733 PROCEDURE convert_values_to_ids
734 (
735 p_x_prd_workoper_rec IN OUT NOCOPY AHL_PRD_OPERATIONS_PVT.prd_workoperation_rec,
736 p_module_type IN VARCHAR2
737 )
738 As
739
740 CURSOR get_operation(c_operation_code VARCHAR2)
741 IS
742 SELECT operation_id
743 FROM AHL_OPERATIONS_B_KFV
744 WHERE concatenated_segments=c_operation_code
745 AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active,SYSDATE))
746 AND TRUNC(NVL(end_date_active,SYSDATE+1))
747 AND revision_status_code='COMPLETE';
748
749 CURSOR get_department(c_department_name VARCHAR2,c_org_id NUMBER)
750 IS
751 SELECT A.Department_id,
752 A.department_code,
753 A.description
754 FROM BOM_DEPARTMENTS A
755 WHERE UPPER(A.description) LIKE UPPER(c_department_name)
756 AND A.organization_id=c_org_id;
757
758 l_dept_rec get_department%ROWTYPE;
759
760 CURSOR get_operation_type(c_operation_type VARCHAR2)
761 IS
762 SELECT lookup_code
763 FROM FND_LOOKUP_VALUES_VL
764 WHERE lookup_type='AHL_OPERATION_TYPE'
765 AND UPPER(meaning)=UPPER(c_operation_type)
766 AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active,SYSDATE))
767 AND TRUNC(NVL(end_date_active,SYSDATE+1));
768
769 CURSOR get_completion_sub_inv(c_com_subinv VARCHAR2,c_inventory_item_id NUMBER)
770 IS
771 SELECT a.Secondary_inventory
772 FROM MTL_ITEM_SUB_INVENTORIES a,
773 MTL_PARAMETERS b
774 WHERE a.Secondary_inventory=c_com_subinv
775 AND a.organization_id=b.organization_id
776 AND a.inventory_item_id=c_inventory_item_id;
777
778 -- Balaji added for Release NR error
779 CURSOR get_op_sch_sec(c_wip_entity_id IN NUMBER, c_op_seq_no IN NUMBER)
780 IS
781 SELECT
782 TO_CHAR(FIRST_UNIT_START_DATE, 'ss') schedule_start_sec,
783 TO_CHAR(LAST_UNIT_COMPLETION_DATE, 'ss') schedule_end_sec
784 FROM
785 WIP_OPERATIONS
786 WHERE
787 WIP_ENTITY_ID = c_wip_entity_id AND
788 OPERATION_SEQ_NUM = c_op_seq_no;
789
790 -- Balaji added for Release NR error
791 CURSOR get_op_act_sec(c_wo_op_id IN NUMBER)
792 IS
793 SELECT
794 TO_CHAR(ACTUAL_START_DATE, 'ss') actual_start_sec,
795 TO_CHAR(ACTUAL_END_DATE, 'ss') actual_end_sec
796 FROM
797 AHL_WORKORDER_OPERATIONS
798 WHERE
799 workorder_operation_id = c_wo_op_id;
800
801 l_compl_subinv_rec get_completion_sub_inv%ROWTYPE;
802
803 l_ctr NUMBER:=0;
804 --l_hour VARCHAR2(30);
805 l_sec VARCHAR2(30);
806 --l_minutes VARCHAR2(30);
807 --l_date_time VARCHAR2(30);
808 l_sch_start_sec VARCHAR2(30);
809 l_sch_end_sec VARCHAR2(30);
810 l_act_start_sec VARCHAR2(30);
811 l_act_end_sec VARCHAR2(30);
812 l_operation_id NUMBER;
813
814 BEGIN
815 /*
816 -- Bug # 6717357 -- start
817 IF p_x_prd_workoper_rec.OPERATION_CODE IS NOT NULL AND
818 p_x_prd_workoper_rec.OPERATION_CODE<>FND_API.G_MISS_CHAR THEN
819 OPEN get_operation(p_x_prd_workoper_rec.OPERATION_CODE);
820 FETCH get_operation INTO p_x_prd_workoper_rec.OPERATION_ID;
821
822 IF get_operation%NOTFOUND THEN
823 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_OPERATION_CODE_INVALID');
824 FND_MESSAGE.SET_TOKEN('FIELD1',p_x_prd_workoper_rec.operation_sequence_num,false);
825 FND_MSG_PUB.ADD;
826 END IF;
827 CLOSE get_operation;
828 END IF;
829 -- Bug # 6717357 -- end
830 */
831
832 -- Added following code for Bug # 6717357 -- start
833 -- Operation code need to be converted to id in following cases
834 -- 1. DML operation is CREATE
835 -- 2. DML operation is UPDATE and Operation code is updated.
836 l_operation_id := is_valid_operation_update(
837 p_operation_id => p_x_prd_workoper_rec.operation_id,
838 p_wo_op_id => p_x_prd_workoper_rec.workorder_operation_id,
839 p_operation_code => p_x_prd_workoper_rec.operation_code,
840 p_dml_operation => p_x_prd_workoper_rec.dml_operation
841 );
842
843 IF p_x_prd_workoper_rec.operation_code IS NOT NULL AND
844 l_operation_id IS NULL
845 THEN
846 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_OPERATION_CODE_INVALID');
847 FND_MESSAGE.SET_TOKEN('FIELD1',p_x_prd_workoper_rec.operation_sequence_num,false);
848 FND_MSG_PUB.ADD;
849 ELSE
850 p_x_prd_workoper_rec.OPERATION_ID := l_operation_id;
851 END IF;
852 -- Added following code for Bug # 6717357 -- End
853 IF p_x_prd_workoper_rec.department_name IS NOT NULL AND
854 p_x_prd_workoper_rec.department_name<>FND_API.G_MISS_CHAR THEN
855 OPEN get_department(p_x_prd_workoper_rec.department_name,p_x_prd_workoper_rec.organization_id);
856 FETCH get_department INTO l_dept_rec;
857
858 IF get_department%NOTFOUND THEN
859 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_DEPT_NAME_INVALID');
860 FND_MESSAGE.SET_TOKEN('FIELD1',p_x_prd_workoper_rec.department_name,false);
861 FND_MESSAGE.SET_TOKEN('RECORD',p_x_prd_workoper_rec.operation_sequence_num,false);
862 FND_MSG_PUB.ADD;
863 ELSIF get_department%FOUND THEN
864 p_x_prd_workoper_rec.department_id:=l_dept_rec.department_id;
865 END IF;
866 CLOSE get_department;
867 END IF;
868
869 IF p_x_prd_workoper_rec.OPERATION_TYPE IS NOT NULL AND
870 p_x_prd_workoper_rec.OPERATION_TYPE<>FND_API.G_MISS_CHAR THEN
871 OPEN get_operation_type(p_x_prd_workoper_rec.OPERATION_TYPE);
872 FETCH get_operation_type INTO p_x_prd_workoper_rec.OPERATION_TYPE_CODE;
873
874 IF get_operation_type%NOTFOUND THEN
875 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_OPERATION_TYPE_INVALID');
876 FND_MESSAGE.SET_TOKEN('FIELD1',p_x_prd_workoper_rec.OPERATION_TYPE,false);
877 FND_MESSAGE.SET_TOKEN('RECORD',p_x_prd_workoper_rec.operation_sequence_num,false);
878 FND_MSG_PUB.ADD;
879 END IF;
880 CLOSE get_operation_type;
881 END IF;
882
883 OPEN get_op_sch_sec(p_x_prd_workoper_rec.wip_entity_id, p_x_prd_workoper_rec.operation_sequence_num);
884 FETCH get_op_sch_sec INTO l_sch_start_sec, l_sch_end_sec;
885 CLOSE get_op_sch_sec;
886
887 IF ( G_DEBUG = 'Y' ) THEN
888 AHL_DEBUG_PUB.debug( 'p_x_prd_workoper_rec.SCHEDULED_START_DATE : ' || to_char(p_x_prd_workoper_rec.SCHEDULED_START_DATE,'DD-MON-YY hh24:mi:ss') );
889 END IF;
890
891 IF p_x_prd_workoper_rec.SCHEDULED_START_DATE IS NOT NULL AND
892 p_x_prd_workoper_rec.SCHEDULED_START_DATE <> FND_API.G_MISS_DATE THEN
893
894 l_sec := TO_CHAR(p_x_prd_workoper_rec.SCHEDULED_START_DATE, 'ss');
895 IF(l_sec = '00' AND p_module_type <> 'OAF' ) THEN
896 l_sec := l_sch_start_sec;
897 END IF;
898 p_x_prd_workoper_rec.SCHEDULED_START_DATE :=
899 get_date_and_time
900 (p_date => p_x_prd_workoper_rec.SCHEDULED_START_DATE,
901 p_date_hh24 => p_x_prd_workoper_rec.SCHEDULED_START_HR,
902 p_date_mi => p_x_prd_workoper_rec.SCHEDULED_START_MI,
903 p_date_ss => l_sec);
904 IF ( G_DEBUG = 'Y' ) THEN
905 AHL_DEBUG_PUB.debug( 'p_x_prd_workoper_rec.SCHEDULED_START_DATE : ' || to_char(p_x_prd_workoper_rec.SCHEDULED_START_DATE,'DD-MON-YY hh24:mi:ss') );
906 END IF;
907 END IF;
908
909 IF p_x_prd_workoper_rec.SCHEDULED_END_DATE IS NOT NULL AND
910 p_x_prd_workoper_rec.SCHEDULED_END_DATE <> FND_API.G_MISS_DATE THEN
911
912 l_sec := TO_CHAR(p_x_prd_workoper_rec.SCHEDULED_END_DATE, 'ss');
913 IF(l_sec = '00' AND p_module_type <> 'OAF' ) THEN
914 l_sec := l_sch_end_sec;
915 END IF;
916 p_x_prd_workoper_rec.SCHEDULED_END_DATE :=
917 get_date_and_time
918 (p_date => p_x_prd_workoper_rec.SCHEDULED_END_DATE,
919 p_date_hh24 => p_x_prd_workoper_rec.SCHEDULED_END_HR,
920 p_date_mi => p_x_prd_workoper_rec.SCHEDULED_END_MI,
921 p_date_ss => l_sec);
922 IF ( G_DEBUG = 'Y' ) THEN
923 AHL_DEBUG_PUB.debug( 'p_x_prd_workoper_rec.SCHEDULED_END_DATE : ' || to_char(p_x_prd_workoper_rec.SCHEDULED_END_DATE,'DD-MON-YY hh24:mi:ss') );
924 END IF;
925 END IF;
926
927 -- Balaji added for Release NR error
928 OPEN get_op_act_sec(p_x_prd_workoper_rec.workorder_operation_id);
929 FETCH get_op_act_sec INTO l_act_start_sec, l_act_end_sec;
930 CLOSE get_op_act_sec;
931
932 IF p_x_prd_workoper_rec.ACTUAL_START_DATE IS NOT NULL AND
933 p_x_prd_workoper_rec.ACTUAL_START_DATE <> FND_API.G_MISS_DATE THEN
934
935 l_sec := TO_CHAR(p_x_prd_workoper_rec.ACTUAL_START_DATE, 'ss');
936 IF(l_sec = '00' AND p_module_type <> 'OAF' ) THEN
937 l_sec := l_act_start_sec;
938 END IF;
939 p_x_prd_workoper_rec.ACTUAL_START_DATE :=
940 get_date_and_time
941 (p_date => p_x_prd_workoper_rec.ACTUAL_START_DATE,
942 p_date_hh24 => p_x_prd_workoper_rec.ACTUAL_START_HR,
943 p_date_mi => p_x_prd_workoper_rec.ACTUAL_START_MI,
944 p_date_ss => l_sec);
945 IF ( G_DEBUG = 'Y' ) THEN
946 AHL_DEBUG_PUB.debug( 'p_x_prd_workoper_rec.ACTUAL_START_DATE : ' || to_char(p_x_prd_workoper_rec.ACTUAL_START_DATE,'DD-MON-YY hh24:mi:ss') );
947 END IF;
948 END IF;
949
950 IF p_x_prd_workoper_rec.ACTUAL_END_DATE IS NOT NULL AND
951 p_x_prd_workoper_rec.ACTUAL_END_DATE <> FND_API.G_MISS_DATE THEN
952
953 l_sec := TO_CHAR(p_x_prd_workoper_rec.ACTUAL_END_DATE, 'ss');
954 IF(l_sec = '00' AND p_module_type <> 'OAF' ) THEN
955 l_sec := l_act_end_sec;
956 END IF;
957 p_x_prd_workoper_rec.ACTUAL_END_DATE :=
958 get_date_and_time
959 (p_date => p_x_prd_workoper_rec.ACTUAL_END_DATE,
960 p_date_hh24 => p_x_prd_workoper_rec.ACTUAL_END_HR,
961 p_date_mi => p_x_prd_workoper_rec.ACTUAL_END_MI,
962 p_date_ss => l_sec);
963 IF ( G_DEBUG = 'Y' ) THEN
964 AHL_DEBUG_PUB.debug( 'p_x_prd_workoper_rec.ACTUAL_END_DATE : ' || to_char(p_x_prd_workoper_rec.ACTUAL_END_DATE,'DD-MON-YY hh24:mi:ss') );
965 END IF;
966 END IF;
967
968 END convert_values_to_ids;
969
970 PROCEDURE validate_operation
971 (
972 p_prd_workoper_rec IN AHL_PRD_OPERATIONS_PVT.PRD_workoperation_rec
973 )
974 AS
975 CURSOR validate_unique_operation(c_workorder_id NUMBER,c_operation_seq_num NUMBER)
976 IS
977 SELECT 'X'
978 FROM AHL_WORKORDER_OPERATIONS
979 WHERE workorder_id =c_workorder_id
980 AND operation_sequence_num =c_operation_seq_num;
981
982 l_unique_rec VARCHAR2(1);
983
984 CURSOR validate_department(c_dept_id NUMBER,c_org_id NUMBER)
985 IS
986 SELECT B.eam_enabled_flag
987 FROM BOM_DEPARTMENTS A,
988 MTL_PARAMETERS B
989 WHERE A.department_id=c_dept_id
990 AND A.organization_id=B.organization_id
991 AND A.organization_id=c_org_id;
992
993 CURSOR get_wo_status(c_wo_id NUMBER)
994 IS
995 SELECT AWOS.status_code,
996 FNDL.meaning
997 FROM AHL_WORKORDERS AWOS,
998 FND_LOOKUP_VALUES_VL FNDL
999 WHERE AWOS.workorder_id = c_wo_id
1000 AND FNDL.LOOKUP_CODE(+) = AWOS.STATUS_CODE
1001 AND FNDL.LOOKUP_TYPE(+) = 'AHL_JOB_STATUS';
1002 -- Cursor added by Balaji for actual dates check issue with operation.
1003 CURSOR get_wo_actual_dates(c_wo_id NUMBER)
1004 IS
1005 SELECT
1006 ACTUAL_START_DATE,
1007 ACTUAL_END_DATE
1008 FROM
1009 AHL_WORKORDERS
1010 WHERE
1011 WORKORDER_ID = c_wo_id;
1012 l_eam_enabled_flag VARCHAR2(1);
1013 l_return_status VARCHAR2(1);
1014 l_wo_status_code VARCHAR2(80);
1015 l_wo_status VARCHAR2(30);
1016 l_wo_actual_start_date DATE;
1017 l_wo_actual_end_date DATE;
1018
1019 -- fix for bug# 7555681
1020 CURSOR get_curr_operation_status(c_workorder_id NUMBER,c_operation_seq_num NUMBER)
1021 IS
1022 SELECT status_code
1023 FROM AHL_WORKORDER_OPERATIONS
1024 WHERE workorder_id =c_workorder_id
1025 AND operation_sequence_num =c_operation_seq_num;
1026
1027 l_curr_op_status VARCHAR2(1);
1028
1029 BEGIN
1030 IF p_prd_workoper_rec.dml_operation='U' THEN
1031 OPEN get_wo_status(p_prd_workoper_rec.workorder_id);
1032 FETCH get_wo_status INTO l_wo_status_code, l_wo_status;
1033 CLOSE get_wo_status;
1034
1035 IF l_wo_status_code IN ('4', '5', '7', '12') THEN
1036 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_UPDOP_WOSTS');
1037 FND_MESSAGE.SET_TOKEN('WO_STS', l_wo_status);
1038 FND_MSG_PUB.ADD;
1039 END IF;
1040
1041 -- fix for bug# 7555681
1042 /*IF p_prd_workoper_rec.status_code = '1' THEN
1043 -- Bug 4393092
1044 -- Cannot update an operation which is in status 'Complete'
1045 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_UPDOP_STS_COMP');
1046 FND_MESSAGE.SET_TOKEN('OP_SEQ', p_prd_workoper_rec.OPERATION_SEQUENCE_NUM);
1047 FND_MSG_PUB.ADD;
1048 END IF;*/
1049 IF (l_wo_status_code = '1' AND p_prd_workoper_rec.status_code = '1') THEN
1050 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_UPDOP_WOSTS');
1051 FND_MESSAGE.SET_TOKEN('WO_STS', l_wo_status);
1052 FND_MSG_PUB.ADD;
1053 ELSE
1054 OPEN get_curr_operation_status(p_prd_workoper_rec.workorder_id,p_prd_workoper_rec.operation_sequence_num);
1055 FETCH get_curr_operation_status INTO l_curr_op_status;
1056 IF(l_curr_op_status = '1')THEN
1057
1058 -- Bug 4393092
1059 -- Cannot update an operation which is in status 'Complete'
1060 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_UPDOP_STS_COMP');
1061 FND_MESSAGE.SET_TOKEN('OP_SEQ', p_prd_workoper_rec.OPERATION_SEQUENCE_NUM);
1062 FND_MSG_PUB.ADD;
1063 END IF;
1064 CLOSE get_curr_operation_status;
1065 END IF;
1066 END IF;
1067
1068 -- rroy
1069 -- ACL Changes
1070 /*l_return_status := AHL_PRD_UTIL_PKG.Is_Unit_Locked(p_workorder_id => p_prd_workoper_rec.workorder_id, p_ue_id => NULL,
1071 p_visit_id => NULL,
1072 p_item_instance_id => NULL);
1073 IF l_return_status = FND_API.G_TRUE THEN
1074 IF p_prd_workoper_rec.dml_operation='C' THEN
1075 FND_MESSAGE.Set_Name('AHL', 'AHL_PP_OP_CRT_UNTLCKD');
1076 ELSIF p_prd_workoper_rec.dml_operation='U' THEN
1077 FND_MESSAGE.Set_Name('AHL', 'AHL_PP_OP_UPD_UNTLCKD');
1078 END IF;
1079 FND_MSG_PUB.ADD;
1080 RAISE FND_API.G_EXC_ERROR;
1081 END IF;
1082 */
1083 -- rroy
1084 -- ACL Changes
1085
1086 IF p_prd_workoper_rec.DEPARTMENT_ID IS NULL OR
1087 p_prd_workoper_rec.DEPARTMENT_ID=FND_API.G_MISS_NUM THEN
1088 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_DEPT_ID_NULL');
1089 FND_MSG_PUB.ADD;
1090 END IF;
1091
1092 IF p_prd_workoper_rec.SCHEDULED_START_DATE IS NULL OR
1093 p_prd_workoper_rec.SCHEDULED_START_DATE=FND_API.G_MISS_DATE THEN
1094 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_OP_SCHEDSTART_DT_NULL');
1095 FND_MSG_PUB.ADD;
1096 ELSIF p_prd_workoper_rec.SCHEDULED_END_DATE IS NULL OR
1097 p_prd_workoper_rec.SCHEDULED_END_DATE=FND_API.G_MISS_DATE THEN
1098 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_OP_SCHEDEND_DT_NULL');
1099 FND_MSG_PUB.ADD;
1100 ELSIF NVL(p_prd_workoper_rec.SCHEDULED_START_DATE,SYSDATE) > NVL(p_prd_workoper_rec.SCHEDULED_END_DATE,SYSDATE) THEN
1101 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_OP_SCHEDDTS_INVALID_DT');
1102 FND_MSG_PUB.ADD;
1103 END IF;
1104
1105 IF p_prd_workoper_rec.ACTUAL_START_DATE IS NOT NULL AND
1106 p_prd_workoper_rec.ACTUAL_START_DATE <> FND_API.G_MISS_DATE AND
1107 p_prd_workoper_rec.ACTUAL_START_DATE > SYSDATE THEN
1108 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_OP_ACT_STRT_DT_INVALID');
1109 FND_MSG_PUB.ADD;
1110 END IF;
1111
1112 IF (p_prd_workoper_rec.ACTUAL_START_DATE IS NULL OR
1113 p_prd_workoper_rec.ACTUAL_START_DATE=FND_API.G_MISS_DATE) AND
1114 (p_prd_workoper_rec.ACTUAL_END_DATE<>FND_API.G_MISS_DATE AND
1115 p_prd_workoper_rec.ACTUAL_END_DATE IS NOT NULL) THEN
1116 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_OP_ACT_START_DT_NULL');
1117 FND_MESSAGE.SET_TOKEN('RECORD',p_prd_workoper_rec.operation_sequence_num,false);
1118 FND_MSG_PUB.ADD;
1119 END IF;
1120
1121 IF p_prd_workoper_rec.ACTUAL_START_DATE IS NOT NULL AND
1122 p_prd_workoper_rec.ACTUAL_START_DATE<>FND_API.G_MISS_DATE AND
1123 p_prd_workoper_rec.ACTUAL_END_DATE<>FND_API.G_MISS_DATE AND
1124 p_prd_workoper_rec.ACTUAL_END_DATE IS NOT NULL AND
1125 p_prd_workoper_rec.ACTUAL_START_DATE > p_prd_workoper_rec.ACTUAL_END_DATE THEN
1126 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_OP_ACT_SE_DT_INVALID');
1127 FND_MSG_PUB.ADD;
1128 END IF;
1129
1130 IF p_prd_workoper_rec.ACTUAL_END_DATE IS NOT NULL AND
1131 p_prd_workoper_rec.ACTUAL_END_DATE <> FND_API.G_MISS_DATE AND
1132 TRUNC( p_prd_workoper_rec.ACTUAL_END_DATE ) > TRUNC( SYSDATE ) THEN
1133 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_OP_ACT_END_DT_INVALID');
1134 FND_MSG_PUB.ADD;
1135 END IF;
1136
1137 -- Balaji added actual date validation against workorder actual dates.
1138 OPEN get_wo_actual_dates(p_prd_workoper_rec.workorder_id);
1139 FETCH get_wo_actual_dates INTO l_wo_actual_start_date, l_wo_actual_end_date;
1140 CLOSE get_wo_actual_dates;
1141
1142 IF ( p_prd_workoper_rec.actual_start_date IS NOT NULL AND
1143 p_prd_workoper_rec.actual_start_date <> FND_API.G_MISS_DATE AND
1144 l_wo_actual_start_date IS NOT NULL AND
1145 p_prd_workoper_rec.actual_start_date < l_wo_actual_start_date ) THEN
1146 FND_MESSAGE.set_name( 'AHL', 'AHL_PRD_OP_WO_ST_DT' );
1147 FND_MSG_PUB.add;
1148 END IF;
1149
1150 IF ( p_prd_workoper_rec.actual_end_date IS NOT NULL AND
1151 p_prd_workoper_rec.actual_end_date <> FND_API.G_MISS_DATE AND
1152 l_wo_actual_end_date IS NOT NULL AND
1153 p_prd_workoper_rec.actual_end_date > l_wo_actual_end_date ) THEN
1154 FND_MESSAGE.set_name( 'AHL', 'AHL_PRD_OP_WO_END_DT' );
1155 FND_MSG_PUB.add;
1156 END IF;
1157
1158 IF p_prd_workoper_rec.OPERATION_SEQUENCE_NUM IS NULL OR
1159 p_prd_workoper_rec.OPERATION_SEQUENCE_NUM=FND_API.G_MISS_NUM THEN
1160 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_OPERATION_SEQ_NULL');
1161 FND_MSG_PUB.ADD;
1162 ELSIF p_prd_workoper_rec.OPERATION_SEQUENCE_NUM <=0 THEN
1163 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_OPERATION_SEQ_NEGZERO');
1164 FND_MSG_PUB.ADD;
1165 ELSE
1166 IF p_prd_workoper_rec.dml_operation='C' THEN
1167 OPEN validate_unique_operation(p_prd_workoper_rec.workorder_id,p_prd_workoper_rec.operation_sequence_num);
1168 FETCH validate_unique_operation INTO l_unique_rec;
1169 IF validate_unique_operation%FOUND THEN
1170 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_OPER_SEQ_NOTUNIQ');
1171 FND_MESSAGE.SET_TOKEN('RECORD',p_prd_workoper_rec.operation_sequence_num,false);
1172 FND_MSG_PUB.ADD;
1173 END IF;
1174 CLOSE validate_unique_operation;
1175 END IF;
1176 END IF;
1177
1178 IF p_prd_workoper_rec.dml_operation='U' THEN
1179 IF p_prd_workoper_rec.WORKORDER_OPERATION_ID IS NULL OR
1180 p_prd_workoper_rec.WORKORDER_OPERATION_ID=FND_API.G_MISS_NUM THEN
1181 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_WO_OPERID_NULL');
1182 FND_MSG_PUB.ADD;
1183 END IF;
1184 END IF;
1185
1186 IF p_prd_workoper_rec.ORGANIZATION_ID IS NOT NULL AND
1187 p_prd_workoper_rec.ORGANIZATION_ID<>FND_API.G_MISS_NUM AND
1188 p_prd_workoper_rec.DEPARTMENT_ID IS NOT NULL AND
1189 p_prd_workoper_rec.DEPARTMENT_ID<>FND_API.G_MISS_NUM THEN
1190 OPEN validate_department(p_prd_workoper_rec.DEPARTMENT_ID,p_prd_workoper_rec.ORGANIZATION_ID);
1191 FETCH validate_department INTO l_eam_enabled_flag;
1192
1193 IF validate_department%NOTFOUND THEN
1194 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_DEPT_NAME_INVALID');
1195 FND_MSG_PUB.ADD;
1196 ELSIF validate_department%FOUND AND l_eam_enabled_flag='N' THEN
1197 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_DEPT_NOT_EAM_ENABLED');
1198 FND_MSG_PUB.ADD;
1199 END IF;
1200 CLOSE validate_department;
1201 END IF;
1202
1203 END validate_operation;
1204
1205 PROCEDURE process_operations
1206 (
1207 p_api_version IN NUMBER := 1.0,
1208 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
1209 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1210 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1211 p_default IN VARCHAR2 := FND_API.G_FALSE,
1212 p_module_type IN VARCHAR2,
1213 p_wip_mass_load_flag IN VARCHAR2,
1214 x_return_status OUT NOCOPY VARCHAR2,
1215 x_msg_count OUT NOCOPY NUMBER,
1216 x_msg_data OUT NOCOPY VARCHAR2,
1217 p_x_prd_operation_tbl IN OUT NOCOPY PRD_OPERATION_TBL
1218 )
1219 AS
1220
1221 l_api_name CONSTANT VARCHAR2(30) := 'process_operations';
1222 l_api_version CONSTANT NUMBER := 1.0;
1223 l_msg_count NUMBER;
1224 l_msg_data VARCHAR2(2000);
1225 l_return_status VARCHAR2(1);
1226
1227 l_empty_workorder_rec AHL_PRD_WORKORDER_PVT.prd_workorder_rec;
1228 l_resource_tbl AHL_PP_RESRC_REQUIRE_PVT.resrc_require_tbl_type;
1229 l_material_tbl AHL_PP_MATERIALS_PVT.req_material_tbl_type;
1230 l_op_status VARCHAR2(30);
1231 l_prd_operation_tbl PRD_OPERATION_TBL;
1232 idx NUMBER;
1233
1234 --cursor to retrieve the Operation Status Code
1235 CURSOR get_op_status(x_workorder_operation_id NUMBER)
1236 IS
1237 SELECT STATUS_CODE
1238 FROM AHL_WORKORDER_OPERATIONS
1239 WHERE WORKORDER_OPERATION_ID = x_workorder_operation_id;
1240
1241 BEGIN
1242 SAVEPOINT process_operations_PVT;
1243
1244 -- Initialize message list IF p_init_msg_list is set to TRUE.
1245 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
1246 p_api_version,
1247 l_api_name,G_PKG_NAME) THEN
1248 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1249 END IF;
1250
1251 IF FND_API.to_boolean(p_init_msg_list) THEN
1252 FND_MSG_PUB.initialize;
1253 END IF;
1254
1255 x_return_status:=FND_API.G_RET_STS_SUCCESS;
1256
1257 IF G_DEBUG='Y' THEN
1258 AHL_DEBUG_PUB.enable_debug;
1259 END IF;
1260
1261 IF ( G_DEBUG = 'Y' ) THEN
1262 AHL_DEBUG_PUB.debug( l_api_name || ' - Total number of Operations - ' || p_x_prd_operation_tbl.COUNT );
1263 END IF;
1264
1265 IF FND_API.to_boolean(p_default) THEN
1266 IF p_x_prd_operation_tbl.COUNT >0 THEN
1267 FOR i in p_x_prd_operation_tbl.FIRST..p_x_prd_operation_tbl.LAST
1268 LOOP
1269
1270 IF p_x_prd_operation_tbl(i).DML_OPERATION='C' THEN
1271 IF ( G_DEBUG = 'Y' ) THEN
1272 AHL_DEBUG_PUB.debug( l_api_name || ' - Before default_attributes' );
1273 END IF;
1274
1275 default_attributes
1276 (
1277 p_x_prd_workoper_rec => p_x_prd_operation_tbl(i)
1278 );
1279
1280 ELSIF p_x_prd_operation_tbl(i).DML_OPERATION='U' THEN
1281 IF ( G_DEBUG = 'Y' ) THEN
1282 AHL_DEBUG_PUB.debug( l_api_name || ' - Before default_missing_attributes' );
1283 END IF;
1284
1285 default_missing_attributes
1286 (
1287 p_x_prd_workoper_rec => p_x_prd_operation_tbl(i),
1288 p_module_type => p_module_type
1289 );
1290 END IF;
1291 END LOOP;
1292 END IF;
1293 END IF;
1294
1295 IF p_module_type='JSP' OR p_module_type = 'OAF' THEN
1296 IF p_x_prd_operation_tbl.COUNT >0 THEN
1297 FOR i in p_x_prd_operation_tbl.FIRST..p_x_prd_operation_tbl.LAST
1298 LOOP
1299 IF ( G_DEBUG = 'Y' ) THEN
1300 AHL_DEBUG_PUB.debug( l_api_name || ' - Before convert_values_to_ids' );
1301 END IF;
1302
1303 convert_values_to_ids
1304 (
1305 p_x_prd_workoper_rec =>p_x_prd_operation_tbl(i),
1306 p_module_type => p_module_type
1307 );
1308 END LOOP;
1309 END IF;
1310 END IF;
1311
1312 l_msg_count:=FND_MSG_PUB.count_msg;
1313
1314 IF l_msg_count > 0 THEN
1315 x_msg_count := l_msg_count;
1316 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1317 IF p_wip_mass_load_flag<>'Y' THEN
1318 RETURN;
1319 END IF;
1320 END IF;
1321
1322 idx := 1;
1323
1324 IF p_x_prd_operation_tbl.COUNT >0 THEN
1325 FOR i IN p_x_prd_operation_tbl.FIRST..p_x_prd_operation_tbl.LAST
1326 LOOP
1327 x_return_status:=FND_API.G_RET_STS_SUCCESS;
1328
1329 IF ( G_DEBUG = 'Y' ) THEN
1330 AHL_DEBUG_PUB.debug( l_api_name || ' - Before validate_operation' );
1331 END IF;
1332
1333 validate_operation
1334 (
1335 p_prd_workoper_rec =>p_x_prd_operation_tbl(i)
1336 );
1337
1338 l_msg_count:=FND_MSG_PUB.count_msg;
1339 IF l_msg_count > 0 THEN
1340 x_msg_count := l_msg_count;
1341 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1342 END IF;
1343
1344 IF x_return_status=FND_API.G_RET_STS_SUCCESS THEN
1345
1346 IF p_x_prd_operation_tbl(i).dml_operation='C' THEN
1347
1348 IF ( G_DEBUG = 'Y' ) THEN
1349 AHL_DEBUG_PUB.debug( l_api_name || ' - Before Inserting into AHL_WORKORDER_OPERATIONS' );
1350 END IF;
1351
1352 INSERT INTO AHL_WORKORDER_OPERATIONS
1353 (
1354 WORKORDER_OPERATION_ID,
1355 OBJECT_VERSION_NUMBER ,
1356 LAST_UPDATE_DATE ,
1357 LAST_UPDATED_BY ,
1358 CREATION_DATE ,
1359 CREATED_BY ,
1360 LAST_UPDATE_LOGIN ,
1361 OPERATION_SEQUENCE_NUM,
1362 WORKORDER_ID ,
1363 STATUS_CODE ,
1364 OPERATION_ID ,
1365 PLAN_ID ,
1366 COLLECTION_ID ,
1367 OPERATION_TYPE_CODE ,
1368 ACTUAL_START_DATE ,
1369 ACTUAL_END_DATE ,
1370 ATTRIBUTE_CATEGORY ,
1371 ATTRIBUTE1 ,
1372 ATTRIBUTE2 ,
1373 ATTRIBUTE3 ,
1374 ATTRIBUTE4 ,
1375 ATTRIBUTE5 ,
1376 ATTRIBUTE6 ,
1377 ATTRIBUTE7 ,
1378 ATTRIBUTE8 ,
1379 ATTRIBUTE9 ,
1380 ATTRIBUTE10 ,
1381 ATTRIBUTE11 ,
1382 ATTRIBUTE12 ,
1383 ATTRIBUTE13 ,
1384 ATTRIBUTE14 ,
1385 ATTRIBUTE15
1386 ) VALUES
1387 (
1388 p_x_prd_operation_tbl(I).WORKORDER_OPERATION_ID,
1389 p_x_prd_operation_tbl(I).OBJECT_VERSION_NUMBER ,
1390 p_x_prd_operation_tbl(I).LAST_UPDATE_DATE ,
1391 p_x_prd_operation_tbl(I).LAST_UPDATED_BY ,
1392 p_x_prd_operation_tbl(I).CREATION_DATE ,
1393 p_x_prd_operation_tbl(I).CREATED_BY ,
1394 p_x_prd_operation_tbl(I).LAST_UPDATE_LOGIN ,
1395 p_x_prd_operation_tbl(I).OPERATION_SEQUENCE_NUM,
1396 p_x_prd_operation_tbl(I).WORKORDER_ID ,
1397 NVL(p_x_prd_operation_tbl(I).STATUS_CODE,'2'),
1398 p_x_prd_operation_tbl(I).OPERATION_ID ,
1399 p_x_prd_operation_tbl(I).PLAN_ID ,
1400 p_x_prd_operation_tbl(I).COLLECTION_ID ,
1401 p_x_prd_operation_tbl(I).OPERATION_TYPE_CODE ,
1402 p_x_prd_operation_tbl(I).ACTUAL_START_DATE ,
1403 p_x_prd_operation_tbl(I).ACTUAL_END_DATE ,
1404 p_x_prd_operation_tbl(I).ATTRIBUTE_CATEGORY ,
1405 p_x_prd_operation_tbl(I).ATTRIBUTE1 ,
1406 p_x_prd_operation_tbl(I).ATTRIBUTE2 ,
1407 p_x_prd_operation_tbl(I).ATTRIBUTE3 ,
1408 p_x_prd_operation_tbl(I).ATTRIBUTE4 ,
1409 p_x_prd_operation_tbl(I).ATTRIBUTE5 ,
1410 p_x_prd_operation_tbl(I).ATTRIBUTE6 ,
1411 p_x_prd_operation_tbl(I).ATTRIBUTE7 ,
1412 p_x_prd_operation_tbl(I).ATTRIBUTE8 ,
1413 p_x_prd_operation_tbl(I).ATTRIBUTE9 ,
1414 p_x_prd_operation_tbl(I).ATTRIBUTE10 ,
1415 p_x_prd_operation_tbl(I).ATTRIBUTE11 ,
1416 p_x_prd_operation_tbl(I).ATTRIBUTE12 ,
1417 p_x_prd_operation_tbl(I).ATTRIBUTE13 ,
1418 p_x_prd_operation_tbl(I).ATTRIBUTE14 ,
1419 p_x_prd_operation_tbl(I).ATTRIBUTE15
1420 );
1421 ELSIF p_x_prd_operation_tbl(I).DML_OPERATION='U' THEN
1422
1423 IF ( G_DEBUG = 'Y' ) THEN
1424 AHL_DEBUG_PUB.debug( l_api_name || ' - Before Updating AHL_WORKORDER_OPERATIONS' );
1425 END IF;
1426
1427 -- R12
1428 -- Tech UIs
1429 OPEN get_op_status(p_x_prd_operation_tbl(I).WORKORDER_OPERATION_ID);
1430 FETCH get_op_status INTO l_op_status;
1431 CLOSE get_op_status;
1432
1433 IF p_x_prd_operation_tbl(I).status_code = '1' AND l_op_status <> '1' THEN
1434 -- If this is an operation completion
1435 l_prd_operation_tbl(idx).workorder_operation_id := p_x_prd_operation_tbl(i).workorder_operation_id;
1436 l_prd_operation_tbl(idx).object_version_number := p_x_prd_operation_tbl(i).object_version_number + 1;
1437 p_x_prd_operation_tbl(i).status_code := l_op_status;
1438 idx := idx + 1;
1439 END IF;
1440
1441 UPDATE AHL_WORKORDER_OPERATIONS SET
1442 OBJECT_VERSION_NUMBER =p_x_prd_operation_tbl(I).OBJECT_VERSION_NUMBER +1,
1443 LAST_UPDATE_DATE =NVL(p_x_prd_operation_tbl(I).LAST_UPDATE_DATE,SYSDATE),
1444 LAST_UPDATED_BY =NVL(p_x_prd_operation_tbl(I).LAST_UPDATED_BY,FND_GLOBAL.user_id),
1445 OPERATION_SEQUENCE_NUM =p_x_prd_operation_tbl(I).OPERATION_SEQUENCE_NUM,
1446 WORKORDER_ID =p_x_prd_operation_tbl(I).WORKORDER_ID ,
1447 STATUS_CODE =p_x_prd_operation_tbl(I).STATUS_CODE ,
1448 OPERATION_ID =p_x_prd_operation_tbl(I).OPERATION_ID ,
1449 PLAN_ID =p_x_prd_operation_tbl(I).PLAN_ID ,
1450 COLLECTION_ID =p_x_prd_operation_tbl(I).COLLECTION_ID ,
1451 OPERATION_TYPE_CODE =p_x_prd_operation_tbl(I).OPERATION_TYPE_CODE ,
1452 ACTUAL_START_DATE =p_x_prd_operation_tbl(I).ACTUAL_START_DATE ,
1453 ACTUAL_END_DATE =p_x_prd_operation_tbl(I).ACTUAL_END_DATE ,
1454 ATTRIBUTE_CATEGORY =p_x_prd_operation_tbl(I).ATTRIBUTE_CATEGORY ,
1455 ATTRIBUTE1 =p_x_prd_operation_tbl(I).ATTRIBUTE1 ,
1456 ATTRIBUTE2 =p_x_prd_operation_tbl(I).ATTRIBUTE2 ,
1457 ATTRIBUTE3 =p_x_prd_operation_tbl(I).ATTRIBUTE3 ,
1458 ATTRIBUTE4 =p_x_prd_operation_tbl(I).ATTRIBUTE4 ,
1459 ATTRIBUTE5 =p_x_prd_operation_tbl(I).ATTRIBUTE5 ,
1460 ATTRIBUTE6 =p_x_prd_operation_tbl(I).ATTRIBUTE6 ,
1461 ATTRIBUTE7 =p_x_prd_operation_tbl(I).ATTRIBUTE7 ,
1462 ATTRIBUTE8 =p_x_prd_operation_tbl(I).ATTRIBUTE8 ,
1463 ATTRIBUTE9 =p_x_prd_operation_tbl(I).ATTRIBUTE9 ,
1464 ATTRIBUTE10 =p_x_prd_operation_tbl(I).ATTRIBUTE10 ,
1465 ATTRIBUTE11 =p_x_prd_operation_tbl(I).ATTRIBUTE11 ,
1466 ATTRIBUTE12 =p_x_prd_operation_tbl(I).ATTRIBUTE12 ,
1467 ATTRIBUTE13 =p_x_prd_operation_tbl(I).ATTRIBUTE13 ,
1468 ATTRIBUTE14 =p_x_prd_operation_tbl(I).ATTRIBUTE14 ,
1469 ATTRIBUTE15 =p_x_prd_operation_tbl(I).ATTRIBUTE15
1470 WHERE WORKORDER_OPERATION_ID=p_x_prd_operation_tbl(I).WORKORDER_OPERATION_ID
1471 AND OBJECT_VERSION_NUMBER =p_x_prd_operation_tbl(I).OBJECT_VERSION_NUMBER;
1472
1473 p_x_prd_operation_tbl(i).OBJECT_VERSION_NUMBER := p_x_prd_operation_tbl(i).OBJECT_VERSION_NUMBER + 1;
1474
1475 END IF;
1476 END IF;
1477 END LOOP;
1478 END IF;
1479
1480 l_msg_count:=FND_MSG_PUB.count_msg;
1481
1482 IF l_msg_count > 0 THEN
1483 x_msg_count := l_msg_count;
1484 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1485
1486 IF p_wip_mass_load_flag='Y' THEN
1487 RAISE FND_API.G_EXC_ERROR;
1488 ELSE
1489 RETURN;
1490 END IF;
1491
1492 END IF;
1493
1494 IF p_wip_mass_load_flag='Y' THEN
1495
1496 IF ( G_DEBUG = 'Y' ) THEN
1497 AHL_DEBUG_PUB.debug( l_api_name || ' - Before AHL_EAM_JOB_PVT.update_job_operations' );
1498 END IF;
1499
1500 AHL_EAM_JOB_PVT.update_job_operations
1501 (
1502 p_api_version => 1.0 ,
1503 p_init_msg_list => FND_API.G_TRUE ,
1504 p_commit => FND_API.G_FALSE ,
1505 p_validation_level => FND_API.G_VALID_LEVEL_FULL ,
1506 p_default => FND_API.G_TRUE ,
1507 p_module_type => NULL ,
1508 x_return_status => l_return_status ,
1509 x_msg_count => l_msg_count ,
1510 x_msg_data => l_msg_data ,
1511 p_workorder_rec => l_empty_workorder_rec ,
1512 p_operation_tbl => p_x_prd_operation_tbl ,
1513 p_material_req_tbl => l_material_tbl ,
1514 p_resource_req_tbl => l_resource_tbl
1515 );
1516
1517 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1518 RAISE FND_API.G_EXC_ERROR;
1519 END IF;
1520 END IF; -- IF p_wip_mass_load_flag='Y' THEN
1521
1522 -- R12
1523 -- Tech UIs
1524 -- Check if operations need to be completed.
1525 IF l_prd_operation_tbl.COUNT > 0 THEN
1526 FOR j in l_prd_operation_tbl.FIRST..l_prd_operation_tbl.LAST LOOP
1527 AHL_COMPLETIONS_PVT.complete_operation
1528 (p_api_version => 1.0,
1529 p_init_msg_list => FND_API.G_TRUE,
1530 p_commit => FND_API.G_FALSE,
1531 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1532 p_default => FND_API.G_FALSE,
1533 x_return_status => l_return_status,
1534 x_msg_count => l_msg_count,
1535 x_msg_data => l_msg_data,
1536 p_workorder_operation_id => l_prd_operation_tbl(j).workorder_operation_id,
1537 p_object_version_no => l_prd_operation_tbl(j).object_version_number
1538 );
1539 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1540 RAISE FND_API.G_EXC_ERROR;
1541 END IF;
1542 END LOOP;
1543 END IF; -- IF l_prd_operation_tbl.COUNT > 0 THEN
1544
1545
1546 IF FND_API.TO_BOOLEAN(p_commit) THEN
1547 COMMIT;
1548 END IF;
1549
1550 IF ( G_DEBUG = 'Y' ) THEN
1551 AHL_DEBUG_PUB.debug( l_api_name || ' - Success' );
1552 END IF;
1553
1554 IF G_DEBUG='Y' THEN
1555 AHL_DEBUG_PUB.disable_debug;
1556 END IF;
1557
1558 EXCEPTION
1559 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1560 ROLLBACK TO process_operations_PVT;
1561 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1562 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1563 p_count => x_msg_count,
1564 p_data => x_msg_data);
1565 WHEN FND_API.G_EXC_ERROR THEN
1566 ROLLBACK TO process_operations_PVT;
1567 x_return_status := FND_API.G_RET_STS_ERROR;
1568 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1569 p_count => x_msg_count,
1570 p_data => x_msg_data);
1571
1572 WHEN OTHERS THEN
1573 ROLLBACK TO process_operations_PVT;
1574 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1575 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1576 FND_MSG_PUB.add_exc_msg(p_pkg_name =>g_pkg_name,
1577 p_procedure_name =>l_api_name,
1578 p_error_text => SUBSTR(SQLERRM,1,240));
1579
1580 END IF;
1581 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1582 p_count => x_msg_count,
1583 p_data => x_msg_data);
1584
1585 END;
1586
1587 FUNCTION get_date_and_time(p_date IN DATE,
1588 p_date_hh24 IN VARCHAR2,
1589 p_date_mi IN VARCHAR2,
1590 p_date_ss IN VARCHAR2) RETURN DATE IS
1591
1592 l_hour VARCHAR2(30);
1593 l_sec VARCHAR2(30);
1594 l_minutes VARCHAR2(30);
1595 l_date_time VARCHAR2(30);
1596 l_date DATE;
1597
1598 BEGIN
1599
1600 l_sec := TO_CHAR(p_date, 'ss');
1601 l_hour := TO_CHAR(p_date, 'hh24');
1602 l_minutes := TO_CHAR(p_date, 'mi');
1603 l_date := p_date;
1604
1605 IF ( p_date_hh24 IS NOT NULL AND
1606 p_date_hh24 <> FND_API.G_MISS_NUM ) THEN
1607 l_hour := p_date_hh24;
1608 END IF;
1609
1610 IF ( p_date_mi IS NOT NULL AND
1611 p_date_mi <> FND_API.G_MISS_NUM ) THEN
1612 l_minutes := p_date_mi;
1613 END IF;
1614
1615 IF(p_date_ss IS NOT NULL AND
1616 p_date_ss <> FND_API.G_MISS_NUM) THEN
1617 l_sec := p_date_ss;
1618 END IF;
1619
1620 IF ( l_hour <> '00' OR l_minutes <> '00' OR l_sec <> '00') THEN
1621 l_date_time := TO_CHAR(p_date, 'DD-MM-YYYY')||' :'|| l_hour ||':'|| l_minutes || ':'|| l_sec;
1622 l_date := TO_DATE(l_date_time , 'DD-MM-YYYY :HH24:MI:ss');
1623 END IF;
1624 RETURN l_date;
1625 END get_date_and_time;
1626
1627 END AHL_PRD_OPERATIONS_PVT;
1628
[Home] [Help]