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