[Home] [Help]
PACKAGE BODY: APPS.WIP_JOBCLOSE_PRIV
Source
1 PACKAGE BODY wip_jobclose_priv AS
2 /* $Header: wipjclpb.pls 120.17.12010000.4 2008/12/05 07:06:18 shjindal ship $ */
3
4 procedure populate_close_temp
5 (
6 p_organization_id IN NUMBER ,
7 p_class_type IN VARCHAR2 ,
8 p_from_class IN VARCHAR2 ,
9 p_to_class IN VARCHAR2 ,
10 p_from_job IN VARCHAR2 ,
11 p_to_job IN VARCHAR2 ,
12 p_from_release_date IN DATE ,
13 p_to_release_date IN DATE ,
14 p_from_start_date IN DATE ,
15 p_to_start_date IN DATE ,
16 p_from_completion_date IN DATE ,
17 p_to_completion_date IN DATE ,
18 p_status IN NUMBER ,
19 p_exclude_reserved_jobs IN VARCHAR2 ,
20 p_exclude_pending_txn_jobs IN VARCHAR2 ,
21 p_report_type IN VARCHAR2 ,
22 p_act_close_date IN DATE ,
23 x_group_id OUT NOCOPY NUMBER ,
24 x_ReturnStatus OUT NOCOPY VARCHAR2
25 )
26 IS
27
28 l_params wip_logger.param_tbl_t;
29 l_return_Status VARCHAR2(1);
30 l_msg VARCHAR(240);
31 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
32 l_number_temp NUMBER ;
33
34 BEGIN
35 x_returnStatus := fnd_api.g_ret_sts_success;
36 fnd_file.put_line(FND_FILE.LOG,'Populate Close Temp');
37
38 IF (l_logLevel <= wip_constants.trace_logging) THEN
39 l_params(1).paramName := 'p_organization_id';
40 l_params(1).paramValue := p_organization_id ;
41 wip_logger.entryPoint(p_procName => 'wip_jobclose_priv.populate_close_temp',
42 p_params => l_params,
43 x_returnStatus => l_return_Status);
44 END IF;
45
46 SELECT WIP_DJ_CLOSE_TEMP_S.nextval
47 INTO x_group_id
48 FROM DUAL ;
49
50 INSERT INTO WIP_DJ_CLOSE_TEMP
51 ( wip_entity_id ,
52 organization_id ,
53 wip_entity_name ,
54 primary_item_id ,
55 status_type ,
56 group_id ,
57 actual_close_date )
58 SELECT DJ.WIP_ENTITY_ID,
59 DJ.ORGANIZATION_ID,
60 WE.WIP_ENTITY_NAME,
61 DJ.PRIMARY_ITEM_ID,
62 DJ.STATUS_TYPE,
63 x_group_id,
64 p_act_close_date
65 FROM ORG_ACCT_PERIODS AP,
66 WIP_DISCRETE_JOBS DJ,
67 WIP_ENTITIES WE
68 WHERE DJ.ORGANIZATION_ID = p_organization_id
69 AND WE.ORGANIZATION_ID = DJ.ORGANIZATION_ID
70 AND AP.ORGANIZATION_ID = DJ.ORGANIZATION_ID
71 AND AP.OPEN_FLAG = 'Y'
72 AND WE.WIP_ENTITY_ID = DJ.WIP_ENTITY_ID
73 AND NOT EXISTS
74 (SELECT 'X'
75 FROM WIP_DJ_CLOSE_TEMP WDCT
76 WHERE WDCT.WIP_ENTITY_ID = WE.WIP_ENTITY_ID)
77 AND DJ.STATUS_TYPE IN
78 -- (1,3,4,5,6,7,9,11,15)
79 (WIP_CONSTANTS.UNRELEASED,
80 WIP_CONSTANTS.RELEASED,
81 WIP_CONSTANTS.COMP_CHRG,
82 WIP_CONSTANTS.COMP_NOCHRG ,
83 WIP_CONSTANTS.HOLD ,
84 WIP_CONSTANTS.CANCELLED ,
85 WIP_CONSTANTS.FAIL_BOM,
86 WIP_CONSTANTS.FAIL_ROUT,
87 WIP_CONSTANTS.FAIL_CLOSE
88 )
89 AND ( p_class_type IS NULL OR
90 DJ.CLASS_CODE IN ( SELECT CLASS_CODE
91 FROM WIP_ACCOUNTING_CLASSES
92 WHERE CLASS_TYPE = p_class_type
93 AND ORGANIZATION_ID = p_organization_id ))
94 AND ( p_from_class IS NULL OR DJ.CLASS_CODE >= p_from_class )
95 AND ( p_to_class IS NULL OR DJ.CLASS_CODE <= p_to_class )
96 AND ( p_from_job IS NULL OR WE.WIP_ENTITY_NAME >= p_from_job )
97 AND ( p_to_job IS NULL OR WE.WIP_ENTITY_NAME <= p_to_job )
98 AND ( p_from_start_date IS NULL OR
99 DJ.SCHEDULED_START_DATE >= p_from_start_date )
100 AND ( p_to_start_date IS NULL OR
101 DJ.SCHEDULED_START_DATE <= p_to_start_date )
102 AND ( p_from_completion_date IS NULL OR
103 DJ.DATE_COMPLETED >= p_from_completion_date )
104 AND ( p_to_completion_date IS NULL OR
105 DJ.DATE_COMPLETED <= p_to_completion_date )
106 AND ( p_from_release_date IS NULL OR
107 DJ.DATE_RELEASED >= p_from_release_date )
108 AND ( p_to_release_date IS NULL OR
109 DJ.DATE_RELEASED <= p_to_release_date )
110 AND ( p_status IS NULL OR DJ.STATUS_TYPE = p_status)
111 -- AND ( DJ.DATE_RELEASED <= p_act_close_date) /* Bug 5007538 */
112 AND ( p_exclude_reserved_jobs <> '1' OR NOT EXISTS
113 (SELECT 'X'FROM WIP_RESERVATIONS_V WRV
114 WHERE WRV.WIP_ENTITY_ID = WE.WIP_ENTITY_ID ))
115 AND ( p_exclude_pending_txn_jobs <> '1' OR ( NOT EXISTS
116 (SELECT 'X' FROM WIP_MOVE_TXN_INTERFACE WMTI
117 WHERE WMTI.ORGANIZATION_ID = p_organization_id
118 AND WMTI.WIP_ENTITY_ID = WE.WIP_ENTITY_ID)
119 AND NOT EXISTS
120 (SELECT 'X'
121 FROM WIP_COST_TXN_INTERFACE WCTI
122 WHERE WCTI.ORGANIZATION_ID = p_organization_id
123 AND WCTI.WIP_ENTITY_ID = WE.WIP_ENTITY_ID)
124 AND NOT EXISTS
125 (SELECT 'X'
126 FROM MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
127 WHERE ORGANIZATION_ID = p_organization_id
128 AND MMTT.TRANSACTION_SOURCE_TYPE_ID = 5
129 AND MMTT.TRANSACTION_SOURCE_ID = WE.WIP_ENTITY_ID)
130 AND NOT EXISTS
131 (SELECT /*+ index(mmt MTL_MATERIAL_TRANSACTIONS_n2) */ 'X'
132 FROM MTL_MATERIAL_TRANSACTIONS MMT
133 WHERE MMT.COSTED_FLAG IN ('N','E')
134 AND MMT.TRANSACTION_SOURCE_TYPE_ID = 5
135 AND MMT.ORGANIZATION_ID = p_organization_id
136 AND MMT.TRANSACTION_SOURCE_ID = WE.WIP_ENTITY_ID)
137 AND NOT EXISTS
138 (SELECT 'X'
139 FROM WIP_OPERATION_YIELDS WOY
140 WHERE WOY.ORGANIZATION_ID = p_organization_id
141 AND WOY.STATUS IN (1, 3)
142 AND WOY.WIP_ENTITY_ID = WE.WIP_ENTITY_ID)
143 AND (WE.ENTITY_TYPE <> 5 OR
144 (WE.ENTITY_TYPE = 5 AND NOT EXISTS
145 (SELECT 'X'
146 FROM wsm_sm_starting_jobs sj,
147 wsm_split_merge_transactions wmt
148 WHERE sj.wip_entity_id = we.wip_entity_id
149 AND sj.transaction_id = wmt.transaction_id
150 AND (wmt.status <> 4 or nvl(wmt.costed,1) <> 4))))
151 AND (WE.ENTITY_TYPE <> 5 OR
152 (WE.ENTITY_TYPE = 5 AND NOT EXISTS
153 (SELECT 'X'
154 FROM wsm_sm_resulting_jobs rj,
155 wsm_split_merge_transactions wmt
156 WHERE rj.wip_entity_id = we.wip_entity_id
157 AND rj.transaction_id = wmt.transaction_id
158 AND (wmt.status <> 4 or nvl(wmt.costed,1) <> 4))))))
159 GROUP BY DJ.WIP_ENTITY_ID, DJ.ORGANIZATION_ID, WE.WIP_ENTITY_NAME,
160 DJ.PRIMARY_ITEM_ID, DJ.STATUS_TYPE ;
161
162 SELECT count(*)
163 INTO l_number_temp
164 FROM WIP_DJ_CLOSE_TEMP
165 WHERE group_id = x_group_id ;
166
167 fnd_file.put_line(FND_FILE.LOG,'Records inserted in close temp '||to_char(l_number_temp));
168
169 --
170 -- Bug 5345660 exitPoint for normal exit.
171 --
172 IF fnd_log.g_current_runtime_level <= wip_constants.trace_logging THEN
173 wip_logger.exitPoint(
174 p_procName => 'wip_close_priv.populate_close_temp',
175 p_procReturnStatus => x_returnStatus,
176 p_msg => 'procedure normal exit',
177 x_returnStatus => l_return_status);
178 END IF;
179
180 /* Handling Exceptions */
181
182 EXCEPTION
183 WHEN others THEN
184 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
185 l_msg := 'unexpected error: ' || SQLERRM || 'SQLCODE = ' || SQLCODE;
186
187 IF (l_logLevel <= wip_constants.trace_logging) THEN
188 wip_logger.exitPoint(p_procName=>'wip_close_priv.populate_close_temp',
189 p_procReturnStatus => x_returnStatus,
190 p_msg => l_msg,
191 x_returnStatus => l_return_Status);
192 END IF;
193 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
194 fnd_message.set_token('MESSAGE', l_msg);
195 fnd_msg_pub.add;
196
197 END populate_close_temp ;
198
199 procedure TIME_ZONE_CONVERSIONS
200 (
201 p_from_release_date IN VARCHAR2 ,
202 p_to_release_date IN VARCHAR2 ,
203 p_from_start_date IN VARCHAR2 ,
204 p_to_start_date IN VARCHAR2 ,
205 p_from_completion_date IN VARCHAR2 ,
206 p_to_completion_date IN VARCHAR2 ,
207 p_act_close_date IN VARCHAR2 ,
208 x_from_release_date OUT NOCOPY DATE ,
209 x_to_release_date OUT NOCOPY DATE ,
210 x_from_start_date OUT NOCOPY DATE ,
211 x_to_start_date OUT NOCOPY DATE ,
212 x_from_completion_date OUT NOCOPY DATE ,
213 x_to_completion_date OUT NOCOPY DATE ,
214 x_act_close_date OUT NOCOPY DATE ,
215 x_returnstatus OUT NOCOPY VARCHAR2
216 )IS
217 l_params wip_logger.param_tbl_t;
218 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
219 l_return_status VARCHAR2(1) ;
220 l_msg_count NUMBER ;
221 l_msg_data VARCHAR2(200);
222 l_msg VARCHAR(240);
223 BEGIN
224 IF (l_logLevel <= wip_constants.trace_logging) THEN
225 l_params(1).paramName := 'p_from_release_date';
226 l_params(1).paramValue := p_from_release_date ;
227 l_params(2).paramName := 'p_to_release_date';
228 l_params(2).paramValue := p_to_release_date ;
229 l_params(3).paramName := 'p_from_start_date';
230 l_params(3).paramValue := p_from_start_date ;
231 l_params(4).paramName := 'p_to_start_date';
232 l_params(4).paramValue := p_to_start_date ;
233 l_params(5).paramName := 'p_from_completion_date';
234 l_params(5).paramValue := p_from_completion_date ;
235 l_params(6).paramName := 'p_to_completion_date';
236 l_params(6).paramValue := p_to_completion_date ;
237 wip_logger.entryPoint(p_procName => 'wip_jobclose_priv.time_zone_conversion',
238 p_params => l_params,
239 x_returnStatus => l_return_Status);
240 END IF;
241 x_ReturnStatus := fnd_api.g_ret_sts_success ;
242 fnd_file.put_line(FND_FILE.LOG,'Time Zone Conversions');
243
244 IF(fnd_profile.value('ENABLE_TIMEZONE_CONVERSIONS') = 'Y') THEN
245
246 HZ_TIMEZONE_PUB.Get_Time(
247 p_api_version => 1.0,
248 p_init_msg_list => 'F',
249 p_source_tz_id => to_number(fnd_profile.value_specific('CLIENT_TIMEZONE_ID')),
250 p_dest_tz_id => to_number(fnd_profile.value_specific('SERVER_TIMEZONE_ID')),
251 p_source_day_time => fnd_date.canonical_to_date(p_from_start_date) ,
252 x_dest_day_time => x_from_start_date ,
253 x_return_status => l_return_status,
254 x_msg_count => l_msg_count,
255 x_msg_data => l_msg_data);
256
257 HZ_TIMEZONE_PUB.Get_Time(
258 p_api_version => 1.0,
259 p_init_msg_list => 'F',
260 p_source_tz_id => to_number(fnd_profile.value_specific('CLIENT_TIMEZONE_ID')),
261 p_dest_tz_id => to_number(fnd_profile.value_specific('SERVER_TIMEZONE_ID')),
262 p_source_day_time => fnd_date.canonical_to_date(p_to_start_date) ,
263 x_dest_day_time => x_to_start_date ,
264 x_return_status => l_return_status,
265 x_msg_count => l_msg_count,
266 x_msg_data => l_msg_data);
267
268 HZ_TIMEZONE_PUB.Get_Time(
269 p_api_version => 1.0,
270 p_init_msg_list => 'F',
271 p_source_tz_id => to_number(fnd_profile.value_specific('CLIENT_TIMEZONE_ID')),
272 p_dest_tz_id => to_number(fnd_profile.value_specific('SERVER_TIMEZONE_ID')),
273 p_source_day_time => fnd_date.canonical_to_date(p_from_release_date) ,
274 x_dest_day_time => x_from_release_date ,
275 x_return_status => l_return_status,
276 x_msg_count => l_msg_count,
277 x_msg_data => l_msg_data);
278
279 HZ_TIMEZONE_PUB.Get_Time(
280 p_api_version => 1.0,
281 p_init_msg_list => 'F',
282 p_source_tz_id => to_number(fnd_profile.value_specific('CLIENT_TIMEZONE_ID')),
283 p_dest_tz_id => to_number(fnd_profile.value_specific('SERVER_TIMEZONE_ID')),
284 p_source_day_time => fnd_date.canonical_to_date(p_to_release_date) ,
285 x_dest_day_time => x_to_release_date ,
286 x_return_status => l_return_status,
287 x_msg_count => l_msg_count,
288 x_msg_data => l_msg_data);
289
290 HZ_TIMEZONE_PUB.Get_Time(
291 p_api_version => 1.0,
292 p_init_msg_list => 'F',
293 p_source_tz_id => to_number(fnd_profile.value_specific('CLIENT_TIMEZONE_ID')),
294 p_dest_tz_id => to_number(fnd_profile.value_specific('SERVER_TIMEZONE_ID')),
295 p_source_day_time => fnd_date.canonical_to_date(p_from_completion_date) ,
296 x_dest_day_time => x_from_completion_date ,
297 x_return_status => l_return_status,
298 x_msg_count => l_msg_count,
299 x_msg_data => l_msg_data);
300
301 HZ_TIMEZONE_PUB.Get_Time(
302 p_api_version => 1.0,
303 p_init_msg_list => 'F',
304 p_source_tz_id => to_number(fnd_profile.value_specific('CLIENT_TIMEZONE_ID')),
305 p_dest_tz_id => to_number(fnd_profile.value_specific('SERVER_TIMEZONE_ID')),
306 p_source_day_time => fnd_date.canonical_to_date(p_to_completion_date) ,
307 x_dest_day_time => x_to_completion_date ,
308 x_return_status => l_return_status,
309 x_msg_count => l_msg_count,
310 x_msg_data => l_msg_data);
311
312 HZ_TIMEZONE_PUB.Get_Time(
313 p_api_version => 1.0,
314 p_init_msg_list => 'F',
315 p_source_tz_id => to_number(fnd_profile.value_specific('CLIENT_TIMEZONE_ID')),
316 p_dest_tz_id => to_number(fnd_profile.value_specific('SERVER_TIMEZONE_ID')),
317 p_source_day_time => fnd_date.canonical_to_date(p_act_close_date) ,
318 x_dest_day_time => x_act_close_date ,
319 x_return_status => l_return_status,
320 x_msg_count => l_msg_count,
321 x_msg_data => l_msg_data);
322
323 ELSE
324
325 x_from_release_date := fnd_date.canonical_to_date(p_from_release_date);
326 x_to_release_date := fnd_date.canonical_to_date(p_to_release_date);
327 x_from_start_date := fnd_date.canonical_to_date(p_from_start_date);
328 x_to_start_date := fnd_date.canonical_to_date(p_to_start_date);
329 x_from_completion_date := fnd_date.canonical_to_date(p_from_completion_date);
330 x_to_completion_date := fnd_date.canonical_to_date(p_to_completion_date);
331 x_act_close_date := fnd_date.canonical_to_date(p_act_close_date);
332
333
334 END IF;
335
336 fnd_file.put_line(FND_FILE.LOG,'x_from_release_date : '||to_char(x_from_release_date));
337 fnd_file.put_line(FND_FILE.LOG,'x_to_release_date : '||to_char(x_to_release_date));
338 fnd_file.put_line(FND_FILE.LOG,'x_from_start_date : '||to_char(x_from_start_date));
339 fnd_file.put_line(FND_FILE.LOG,'x_to_start_date : '||to_char(x_to_start_date));
340 fnd_file.put_line(FND_FILE.LOG,'x_from_completion_date : '||to_char(x_from_completion_date));
341 fnd_file.put_line(FND_FILE.LOG,'x_to_completion_date : '||to_char(x_to_completion_date));
342 fnd_file.put_line(FND_FILE.LOG,'x_act_close_date : '||to_char(x_act_close_date));
343
344 IF fnd_log.g_current_runtime_level <= wip_constants.trace_logging THEN
345 wip_logger.exitPoint(
346 p_procName => 'wip_close_priv.time_zone_conversions',
347 p_procReturnStatus => x_returnStatus,
348 p_msg => 'procedure normal exit',
349 x_returnStatus => l_return_status);
350 END IF;
351 EXCEPTION
352 WHEN others THEN
353 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
354 l_msg := 'unexpected error: ' || SQLERRM || 'SQLCODE = ' || SQLCODE;
355
356 IF fnd_log.g_current_runtime_level <= wip_constants.trace_logging THEN
357 wip_logger.exitPoint(
358 p_procName=>'wip_close_priv.time_zone_conversions',
359 p_procReturnStatus => x_returnStatus,
360 p_msg => l_msg,
361 x_returnStatus => l_return_Status);
362 END IF;
363 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
364 fnd_message.set_token('MESSAGE', l_msg);
365 fnd_msg_pub.add;
366
367 END TIME_ZONE_CONVERSIONS;
368
369 procedure PRIOR_DATE_RELEASE
370 (
371 x_returnstatus OUT NOCOPY VARCHAR2,
372 p_organization_id IN NUMBER ,
373 p_group_id IN NUMBER
374 )
375 IS
376
377 --
378 -- Bug 5148397
379 --
380 l_params wip_logger.param_tbl_t;
381 l_return_status VARCHAR2(1);
382 l_msg VARCHAR(2000);
383 l_failed_counter NUMBER;
384 l_failed_ids dbms_sql.number_table;
385
386 BEGIN
387
388 IF fnd_log.g_current_runtime_level <= wip_constants.trace_logging THEN
389 l_params(1).paramName := 'p_organization_id';
390 l_params(1).paramValue := p_organization_id;
391 l_params(2).paramName := 'p_group_id';
392 l_params(2).paramValue := p_group_id;
393 wip_logger.entryPoint(
394 p_procName => 'wip_jobclose_priv.prior_date_release',
395 p_params => l_params,
396 x_returnStatus => l_return_status);
397 END IF;
398
399 --
400 -- The most efficient algorithm is to start deleting all
401 -- invalid records from wip_dj_close_temp collecting the
402 -- wip_entity_ids at the same time. Then use BULK op
403 -- to update wip_discrete_jobs' status for those records.
404 -- bso Sat Jun 17 17:18:45 PDT 2006
405 --
406
407 DELETE FROM wip_dj_close_temp wdct
408 WHERE wdct.organization_id = p_organization_id AND
409 wdct.group_id = p_group_id AND
410 wdct.actual_close_date <
411 (SELECT wdj.date_released
412 FROM wip_discrete_jobs wdj
413 WHERE wdj.wip_entity_id = wdct.wip_entity_id AND
414 wdj.organization_id = p_organization_id)
415 RETURNING wdct.wip_entity_id
416 BULK COLLECT INTO l_failed_ids;
417
418 l_failed_counter := l_failed_ids.COUNT;
419
420 fnd_file.put_line(FND_FILE.LOG,'Number of jobs failed because release date before close date : '|| to_char(l_failed_counter));
421
422 IF l_failed_counter = 0 THEN
423 x_returnstatus := FND_API.G_RET_STS_SUCCESS;
424 ELSE
425 --
426 -- Some invalid jobs found. Set expected error flag and
427 -- update wip_discrete_jobs' status_type to fail_close.
428 --
429 x_returnstatus := FND_API.G_RET_STS_ERROR;
430
431 FORALL i IN l_failed_ids.FIRST .. l_failed_ids.LAST
432 UPDATE wip_discrete_jobs
433 SET status_type = WIP_CONSTANTS.FAIL_CLOSE
434 WHERE organization_id = p_organization_id AND
435 wip_entity_id = l_failed_ids(i);
436
437 l_failed_ids.DELETE;
438 END IF;
439
440 --
441 -- Bug 5345660 exitPoint for normal exit.
442 --
443 IF fnd_log.g_current_runtime_level <= wip_constants.trace_logging THEN
444 wip_logger.exitPoint(
445 p_procName => 'wip_close_priv.prior_date_release',
446 p_procReturnStatus => x_returnStatus,
447 p_msg => 'procedure normal exit',
448 x_returnStatus => l_return_status);
449 END IF;
450
451 EXCEPTION
452 WHEN others THEN
453 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
454 l_msg := 'unexpected error: ' || SQLERRM || 'SQLCODE = ' || SQLCODE;
455
456 IF fnd_log.g_current_runtime_level <= wip_constants.trace_logging THEN
457 wip_logger.exitPoint(
458 p_procName=>'wip_close_priv.prior_date_release',
459 p_procReturnStatus => x_returnStatus,
460 p_msg => l_msg,
461 x_returnStatus => l_return_Status);
462 END IF;
463 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
464 fnd_message.set_token('MESSAGE', l_msg);
465 fnd_msg_pub.add;
466
467 END PRIOR_DATE_RELEASE;
468
469
470 procedure PENDING_TXNS
471 (
472 x_Returnstatus OUT NOCOPY VARCHAR2 ,
473 p_organization_id IN NUMBER ,
474 p_group_id IN NUMBER
475 )
476 IS
477 l_params wip_logger.param_tbl_t;
478 l_return_Status VARCHAR2(1);
479 l_msg VARCHAR(240);
480 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
481
482 CURSOR c_pending_txns IS
483 SELECT WIP_ENTITY_NAME
484 FROM WIP_DJ_CLOSE_TEMP
485 WHERE GROUP_ID = p_group_id
486 AND ORGANIZATION_ID = p_organization_id
487 AND WIP_ENTITY_ID IN
488 (SELECT WIP_ENTITY_ID
489 FROM WIP_MOVE_TXN_INTERFACE
490 WHERE ORGANIZATION_ID = p_organization_id
491 UNION ALL
492 SELECT WIP_ENTITY_ID
493 FROM WIP_COST_TXN_INTERFACE
494 WHERE ORGANIZATION_ID = p_organization_id
495 UNION ALL
496 SELECT TRANSACTION_SOURCE_ID
497 FROM MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
498 WHERE ORGANIZATION_ID = p_organization_id
499 AND TRANSACTION_SOURCE_TYPE_ID = 5
500 AND TRANSACTION_SOURCE_ID NOT IN
501 (SELECT TXN_SOURCE_ID
502 FROM MTL_TXN_REQUEST_LINES
503 WHERE TXN_SOURCE_ID = MMTT.TRANSACTION_SOURCE_ID
504 AND ORGANIZATION_ID = MMTT.ORGANIZATION_ID
505 AND LINE_STATUS = 9)
506 UNION ALL
507 SELECT TRANSACTION_SOURCE_ID
508 FROM MTL_MATERIAL_TRANSACTIONS
509 WHERE COSTED_FLAG IN ('N','E')
510 AND TRANSACTION_SOURCE_TYPE_ID = 5
511 AND ORGANIZATION_ID = p_organization_id
512 UNION ALL
513 SELECT DISTINCT WIP_ENTITY_ID
514 FROM WIP_OPERATION_YIELDS
515 WHERE ORGANIZATION_ID = p_organization_id
516 AND STATUS IN (1, 3)
517 UNION ALL
518 SELECT WLC.WIP_ENTITY_ID
519 FROM WIP_LPN_COMPLETIONS WLC,
520 WMS_LICENSE_PLATE_NUMBERS LPN ,
521 MTL_TXN_REQUEST_LINES MTRL
522 WHERE WLC.ORGANIZATION_ID = p_organization_id
523 AND WLC.LPN_ID = LPN.LPN_ID
524 AND MTRL.LPN_ID = LPN.LPN_ID
525 AND MTRL.txn_source_id = WLC.wip_entity_id
526 AND MTRL.line_status = 7 /*Bugfix 6455522 added one condition for mtrl.line_status=7*/
527 AND LPN.LPN_CONTEXT = 2);
528
529 l_failed_jobs WIP_DJ_CLOSE_TEMP.WIP_ENTITY_NAME%TYPE ;
530 l_failed_counter NUMBER ;
531 BEGIN
532
533 IF (l_logLevel <= wip_constants.trace_logging) THEN
534 l_params(1).paramName := 'p_organization_id';
535 l_params(1).paramValue := p_organization_id ;
536 l_params(1).paramName := 'p_group_id';
537 l_params(1).paramValue := p_group_id ;
538 wip_logger.entryPoint(p_procName => 'wip_jobclose_priv.pending_txns',
539 p_params => l_params,
540 x_returnStatus => l_return_Status);
541 END IF;
542 x_returnStatus := fnd_api.g_ret_sts_success;
543 l_failed_counter := 0 ;
544 fnd_file.put_line(FND_FILE.LOG,'Pending Txns Check');
545
546 OPEN c_pending_txns ;
547 LOOP
548 FETCH c_pending_txns INTO l_failed_jobs ;
549 if (c_pending_txns%FOUND) then
550 l_failed_counter := l_failed_counter + 1 ;
551 x_returnStatus := FND_API.G_RET_STS_ERROR ;
552 fnd_file.put_line(FND_FILE.OUTPUT,to_char(l_failed_jobs));
553 end if ;
554
555 UPDATE WIP_DJ_CLOSE_TEMP
556 SET STATUS_TYPE = 99
557 WHERE WIP_ENTITY_NAME = l_failed_jobs ;
558
559 EXIT WHEN c_pending_txns%NOTFOUND ;
560 END LOOP ;
561
562 UPDATE WIP_DISCRETE_JOBS
563 SET STATUS_TYPE = WIP_CONSTANTS.FAIL_CLOSE
564 WHERE WIP_ENTITY_ID IN
565 (SELECT WIP_ENTITY_ID
566 FROM WIP_DJ_CLOSE_TEMP
567 WHERE GROUP_ID = p_group_id
568 AND ORGANIZATION_ID = p_organization_id
569 AND STATUS_TYPE = 99);
570
571 DELETE FROM WIP_DJ_CLOSE_TEMP
572 WHERE GROUP_ID = p_group_id
573 AND ORGANIZATION_ID = p_organization_id
574 AND STATUS_TYPE = 99;
575
576 fnd_file.put_line(FND_FILE.LOG,'Number of jobs failed due to Pending txns : '|| to_char(l_failed_counter));
577
578 IF (c_pending_txns%ISOPEN) THEN
579 CLOSE c_pending_txns ;
580 END IF;
581
582 --
583 -- Bug 5345660 exitPoint for normal exit.
584 --
585 IF fnd_log.g_current_runtime_level <= wip_constants.trace_logging THEN
586 wip_logger.exitPoint(
587 p_procName => 'wip_close_priv.pending_txns',
588 p_procReturnStatus => x_returnStatus,
589 p_msg => 'procedure normal exit',
590 x_returnStatus => l_return_status);
591 END IF;
592
593 EXCEPTION
594 WHEN others THEN
595 IF (c_pending_txns%ISOPEN) THEN
596 CLOSE c_pending_txns ;
597 END IF;
598 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
599 l_msg := 'unexpected error: ' || SQLERRM || 'SQLCODE = ' || SQLCODE;
600
601 IF (l_logLevel <= wip_constants.trace_logging) THEN
602 wip_logger.exitPoint(p_procName=>'wip_close_priv.pending_txns',
603 p_procReturnStatus => x_returnStatus,
604 p_msg => l_msg,
605 x_returnStatus => l_return_Status);
606 END IF;
607 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
608 fnd_message.set_token('MESSAGE', l_msg);
609 fnd_msg_pub.add;
610 END PENDING_TXNS ;
611
612
613 procedure CLOSE_JOB_EXCEPTIONS
614 (
615 x_returnstatus OUT NOCOPY VARCHAR2,
616 p_organization_id IN NUMBER ,
617 p_group_id IN NUMBER
618 )
619 IS
620 cursor c_jobs is
621 select wdct.wip_entity_id,
622 we.organization_id
623 from wip_dj_close_temp wdct,
624 wip_entities we
625 where we.wip_entity_id = wdct.wip_entity_id
626 and we.organization_id = wdct.organization_id
627 and wdct.group_id = p_group_id
628 and wdct.organization_id = p_organization_id;
629
630 l_ret_status VARCHAR2(30);
631 l_msg_data VARCHAR2(2000);
632 l_ret_exp_status boolean := true;
633
634 BEGIN
635 x_returnstatus := FND_API.G_RET_STS_SUCCESS;
636
637 for l_jobRec in c_jobs loop
638 l_ret_exp_status :=
639 wip_ws_exceptions.close_exception_job
640 (p_wip_entity_id => l_jobRec.wip_entity_id,
641 p_organization_id => l_jobRec.organization_id);
642
643 IF (l_ret_exp_status = false) then
644 UPDATE WIP_DJ_CLOSE_TEMP
645 SET STATUS_TYPE = 99
646 WHERE WIP_ENTITY_ID = l_jobRec.wip_entity_id;
647
648 x_returnstatus := FND_API.G_RET_STS_ERROR;
649 END IF;
650 end loop;
651
652 END CLOSE_JOB_EXCEPTIONS ;
653
654
655 procedure PENDING_CLOCKS
656 (
657 x_returnstatus OUT NOCOPY VARCHAR2,
658 p_organization_id IN NUMBER ,
659 p_group_id IN NUMBER
660 )
661 IS
662 cursor c_jobs is
663 select wdct.wip_entity_id,
664 we.wip_entity_name
665 from wip_dj_close_temp wdct,
666 wip_entities we
667 where we.wip_entity_id = wdct.wip_entity_id
668 and we.organization_id = wdct.organization_id
669 and wdct.group_id = p_group_id
670 and wdct.organization_id = p_organization_id;
671
672 l_ret_status VARCHAR2(30);
673 l_msg_data VARCHAR2(2000);
674 l_params wip_logger.param_tbl_t;
675 l_return_Status VARCHAR2(1);
676 l_msg VARCHAR(240);
677 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
678 l_failed_counter NUMBER ;
679 BEGIN
680 IF (l_logLevel <= wip_constants.trace_logging) THEN
681 l_params(1).paramName := 'p_organization_id';
682 l_params(1).paramValue := p_organization_id ;
683 l_params(1).paramName := 'p_group_id';
684 l_params(1).paramValue := p_group_id ;
685 wip_logger.entryPoint(p_procName => 'wip_jobclose_priv.pending_clocks',
686 p_params => l_params,
687 x_returnStatus => l_return_Status);
688 END IF;
689 x_returnstatus := FND_API.G_RET_STS_SUCCESS;
690 l_failed_counter := 0;
691
692 for l_jobRec in c_jobs loop
693 l_ret_status :=
694 WIP_WS_TIME_ENTRY.is_clock_pending
695 (p_wip_entity_id => l_jobRec.wip_entity_id,
696 p_operation_seq_num => NULL);
697
698 IF (l_ret_status <> 'N') then
699 UPDATE WIP_DJ_CLOSE_TEMP
700 SET STATUS_TYPE = 99
701 WHERE WIP_ENTITY_ID = l_jobRec.wip_entity_id;
702 l_failed_counter := l_failed_counter + 1 ;
703 fnd_file.put_line(FND_FILE.OUTPUT,to_char(l_jobRec.wip_entity_name));
704 x_returnstatus := FND_API.G_RET_STS_ERROR;
705 END IF;
706 end loop;
707
708 UPDATE WIP_DISCRETE_JOBS
709 SET STATUS_TYPE = WIP_CONSTANTS.FAIL_CLOSE
710 WHERE WIP_ENTITY_ID IN
711 (SELECT WIP_ENTITY_ID
712 FROM WIP_DJ_CLOSE_TEMP
713 WHERE GROUP_ID = p_group_id
714 AND ORGANIZATION_ID = p_organization_id
715 AND STATUS_TYPE = 99);
716
717 DELETE FROM WIP_DJ_CLOSE_TEMP
718 WHERE GROUP_ID = p_group_id
719 AND ORGANIZATION_ID = p_organization_id
720 AND STATUS_TYPE = 99;
721
722 fnd_file.put_line(FND_FILE.LOG,'Number of jobs failed due to Pending Clocks : '|| to_char(l_failed_counter));
723
724 --
725 -- Bug 5345660 exitPoint for normal exit.
726 --
727 IF fnd_log.g_current_runtime_level <= wip_constants.trace_logging THEN
728 wip_logger.exitPoint(
729 p_procName => 'wip_close_priv.pending_clocks',
730 p_procReturnStatus => x_returnStatus,
731 p_msg => 'procedure normal exit',
732 x_returnStatus => l_return_status);
733 END IF;
734
735 EXCEPTION
736 WHEN others THEN
737 IF (c_jobs%ISOPEN) THEN
738 CLOSE c_jobs ;
739 END IF;
740 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
741 l_msg := 'unexpected error: ' || SQLERRM || 'SQLCODE = ' || SQLCODE;
742
743 IF (l_logLevel <= wip_constants.trace_logging) THEN
744 wip_logger.exitPoint(p_procName=>'wip_close_priv.pending_clocks',
745 p_procReturnStatus => x_returnStatus,
746 p_msg => l_msg,
747 x_returnStatus => l_return_Status);
748 END IF;
749 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
750 fnd_message.set_token('MESSAGE', l_msg);
751 fnd_msg_pub.add;
752
753 END PENDING_CLOCKS ;
754
755 procedure CANCEL_MOVE_ORDERS
756 (
757 x_returnstatus OUT NOCOPY VARCHAR2,
758 p_organization_id IN NUMBER ,
759 p_group_id IN NUMBER
760 )
761 IS
762 cursor c_jobs is
763 select wdct.wip_entity_id,
764 we.entity_type,
765 we.wip_entity_name
766 from wip_dj_close_temp wdct,
767 wip_entities we
768 where we.wip_entity_id = wdct.wip_entity_id
769 and we.organization_id = wdct.organization_id
770 and wdct.group_id = p_group_id
771 and wdct.organization_id = p_organization_id;
772
773 l_ret_status VARCHAR2(30);
774 l_msg_data VARCHAR2(2000);
775 l_failed_counter NUMBER ;
776 BEGIN
777 l_failed_counter := 0 ;
778 fnd_file.put_line(FND_FILE.LOG,'Cancelling Move Orders if any exists ');
779 for l_jobRec in c_jobs loop
780 wip_picking_pvt.cancel_allocations
781 (p_wip_entity_id => l_jobRec.wip_entity_id,
782 p_wip_entity_type => l_jobRec.entity_type,
783 x_return_status => l_ret_status,
784 x_msg_data => l_msg_data);
785 fnd_file.put_line(FND_FILE.LOG,'return status '||l_ret_status);
786 IF (l_ret_status <> FND_API.G_RET_STS_SUCCESS ) then
787 UPDATE WIP_DJ_CLOSE_TEMP
788 SET STATUS_TYPE = 99
789 WHERE WIP_ENTITY_ID = l_jobRec.wip_entity_id;
790 l_failed_counter := l_failed_counter + 1 ;
791 fnd_file.put_line(FND_FILE.OUTPUT,to_char(l_jobRec.wip_entity_name));
792 x_returnstatus := FND_API.G_RET_STS_ERROR;
793 END IF;
794 end loop;
795
796 UPDATE WIP_DISCRETE_JOBS
797 SET STATUS_TYPE = WIP_CONSTANTS.FAIL_CLOSE
798 WHERE WIP_ENTITY_ID IN
799 (SELECT WIP_ENTITY_ID
800 FROM WIP_DJ_CLOSE_TEMP
801 WHERE GROUP_ID = p_group_id
802 AND ORGANIZATION_ID = p_organization_id
803 AND STATUS_TYPE = 99);
804
805 DELETE FROM WIP_DJ_CLOSE_TEMP
806 WHERE GROUP_ID = p_group_id
807 AND ORGANIZATION_ID = p_organization_id
808 AND STATUS_TYPE = 99;
809
810 END CANCEL_MOVE_ORDERS ;
811
812 procedure CANCEL_PO
813 (
814 x_returnstatus OUT NOCOPY VARCHAR2,
815 p_organization_id IN NUMBER ,
816 p_group_id IN NUMBER
817 )
818 IS
819 cursor c_jobs is
820 select wdct.wip_entity_id,
821 we.entity_type
822 from wip_dj_close_temp wdct,
823 wip_entities we
824 where we.wip_entity_id = wdct.wip_entity_id
825 and we.organization_id = wdct.organization_id
826 and wdct.group_id = p_group_id
827 and wdct.organization_id = p_organization_id;
828
829 l_ret_status VARCHAR2(30);
830 l_msg_data VARCHAR2(2000);
831 l_propagate_job_change_to_po NUMBER;
832 BEGIN
833 fnd_file.put_line(FND_FILE.LOG,'Cancel PO');
834
835 select propagate_job_change_to_po
836 into l_propagate_job_change_to_po
837 from wip_parameters
838 where organization_id = p_organization_id;
839
840 for l_jobRec in c_jobs loop
841 -- add code to cancel PO/requisitions if exists and applicable
842 IF(po_code_release_grp.Current_Release >=
843 po_code_release_grp.PRC_11i_Family_Pack_J AND
844 l_propagate_job_change_to_po = WIP_CONSTANTS.YES) THEN
845 -- try to cancel all PO/requisitions associated to the jobs.
846 wip_osp.cancelPOReq(p_job_id => l_jobRec.wip_entity_id,
847 p_org_id => p_organization_id,
848 p_clr_fnd_mes_flag => 'Y',
849 x_return_status => l_ret_status);
850 -- added parameter p_clr_fnd_mes_flag for bugfix 7415801.
851
852
853 END IF;
854 end loop;
855
856 END CANCEL_PO ;
857
858
859 procedure PAST_CLOSE_DATE
860 (
861 x_returnstatus OUT NOCOPY VARCHAR2,
862 p_organization_id IN NUMBER ,
863 p_group_id IN NUMBER
864
865 )
866 IS
867
868 l_params wip_logger.param_tbl_t;
869 l_return_Status VARCHAR2(1);
870 l_msg VARCHAR(240);
871 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
872
873 CURSOR c_pending_txns IS
874 SELECT WIP_ENTITY_NAME
875 FROM WIP_DJ_CLOSE_TEMP
876 WHERE WIP_ENTITY_ID IN
877 (SELECT wdct.WIP_ENTITY_ID
878 FROM WIP_TRANSACTIONS wt,
879 WIP_DJ_CLOSE_TEMP wdct
880 WHERE wdct.GROUP_ID = p_group_id
881 AND wdct.ORGANIZATION_ID = p_organization_id
882 AND wdct.WIP_ENTITY_ID = wt.WIP_ENTITY_ID
883 AND wt.ORGANIZATION_ID = p_organization_id
884 AND wt.TRANSACTION_DATE > wdct.ACTUAL_CLOSE_DATE
885 UNION
886 SELECT wdct.WIP_ENTITY_ID
887 FROM MTL_MATERIAL_TRANSACTIONS mmt,
888 WIP_DJ_CLOSE_TEMP wdct
889 WHERE wdct.GROUP_ID = p_group_id
890 AND wdct.ORGANIZATION_ID = p_organization_id
891 AND wdct.WIP_ENTITY_ID = mmt.TRANSACTION_SOURCE_ID
892 AND mmt.TRANSACTION_SOURCE_TYPE_ID = 5
893 AND mmt.ORGANIZATION_ID = p_organization_id
894 AND mmt.TRANSACTION_DATE > wdct.ACTUAL_CLOSE_DATE) ;
895 l_failed_jobs WIP_DJ_CLOSE_TEMP.WIP_ENTITY_NAME%TYPE ;
896 l_failed_counter NUMBER ;
897 BEGIN
898 fnd_file.put_line(FND_FILE.LOG,'Inside Procedure Close Date ');
899 x_returnStatus := fnd_api.g_ret_sts_success;
900 l_failed_counter := 0 ;
901
902 IF (l_logLevel <= wip_constants.trace_logging) THEN
903 l_params(1).paramName := 'p_organization_id';
904 l_params(1).paramValue := p_organization_id ;
905 l_params(1).paramName := 'p_group_id';
906 l_params(1).paramValue := p_group_id ;
907 wip_logger.entryPoint(p_procName => 'wip_jobclose_priv.past_close_date',
908 p_params => l_params,
909 x_returnStatus => l_return_Status);
910 END IF;
911
912 OPEN c_pending_txns ;
913 LOOP
914 FETCH c_pending_txns INTO l_failed_jobs ;
915 if (c_pending_txns%FOUND) then
916 fnd_file.put_line(FND_FILE.LOG,'Close date precedes the txn date for job '||l_failed_jobs);
917 l_failed_counter := l_failed_counter + 1 ;
918 x_returnstatus := FND_API.G_RET_STS_ERROR ;
919 fnd_file.put_line(FND_FILE.OUTPUT,to_char(l_failed_jobs));
920 end if ;
921
922 UPDATE WIP_DJ_CLOSE_TEMP
923 SET STATUS_TYPE = 99
924 WHERE WIP_ENTITY_NAME = l_failed_jobs ;
925
926 EXIT WHEN c_pending_txns%NOTFOUND ;
927 END LOOP ;
928 fnd_file.put_line(FND_FILE.LOG,'Number of failed jobs because of past close date : '||l_failed_counter);
929
930 UPDATE WIP_DISCRETE_JOBS
931 SET STATUS_TYPE = WIP_CONSTANTS.FAIL_CLOSE
932 WHERE WIP_ENTITY_ID IN
933 (SELECT WIP_ENTITY_ID
934 FROM WIP_DJ_CLOSE_TEMP
935 WHERE GROUP_ID = p_group_id
936 AND ORGANIZATION_ID = p_organization_id
937 AND STATUS_TYPE = 99);
938
939 DELETE FROM WIP_DJ_CLOSE_TEMP
940 WHERE GROUP_ID = p_group_id
941 AND ORGANIZATION_ID = p_organization_id
942 AND STATUS_TYPE = 99;
943
944
945 IF (c_pending_txns%ISOPEN) THEN
946 CLOSE c_pending_txns ;
947 END IF;
948
949 --
950 -- Bug 5345660 exitPoint for normal exit.
951 --
952 IF fnd_log.g_current_runtime_level <= wip_constants.trace_logging THEN
953 wip_logger.exitPoint(
954 p_procName => 'wip_close_priv.past_close_date',
955 p_procReturnStatus => x_returnStatus,
956 p_msg => 'procedure normal exit',
957 x_returnStatus => l_return_status);
958 END IF;
959
960 EXCEPTION
961 WHEN others THEN
962 IF (c_pending_txns%ISOPEN) THEN
963 CLOSE c_pending_txns ;
964 END IF;
965 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
966 l_msg := 'unexpected error: ' || SQLERRM || 'SQLCODE = ' || SQLCODE;
967 IF (l_logLevel <= wip_constants.trace_logging) THEN
968 wip_logger.exitPoint(p_procName=>'wip_close_priv.past_close_date',
969 p_procReturnStatus => x_returnStatus,
970 p_msg => l_msg,
971 x_returnStatus => l_return_Status);
972 END IF;
973 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
974 fnd_message.set_token('MESSAGE', l_msg);
975 fnd_msg_pub.add;
976 END PAST_CLOSE_DATE ;
977
978 procedure CHECK_OPEN_PO
979 (
980 x_returnstatus OUT NOCOPY VARCHAR2 ,
981 p_organization_id IN NUMBER ,
982 p_group_id IN NUMBER
983 )
984 IS
985
986 l_params wip_logger.param_tbl_t;
987 l_return_Status VARCHAR2(1);
988 l_msg VARCHAR(240);
989 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
990
991 CURSOR c_open_po IS
992 SELECT WIP_ENTITY_NAME
993 FROM WIP_DJ_CLOSE_TEMP WDCT
994 WHERE wdct.GROUP_ID = p_group_id
995 AND wdct.ORGANIZATION_ID = p_organization_id
996 AND EXISTS
997 (SELECT '1'
998 FROM PO_RELEASES_ALL PR,
999 PO_HEADERS_ALL PH,
1000 PO_DISTRIBUTIONS_ALL PD,
1001 PO_LINE_LOCATIONS_ALL PL
1002 WHERE PD.WIP_ENTITY_ID = wdct.WIP_ENTITY_ID
1003 AND PD.DESTINATION_ORGANIZATION_ID = p_organization_id
1004 AND pd.po_line_id IS NOT NULL
1005 AND pd.line_location_id IS NOT NULL
1006 AND PH.PO_HEADER_ID = PD.PO_HEADER_ID
1007 AND PL.PO_HEADER_ID = PD.PO_HEADER_ID
1008 AND PL.LINE_LOCATION_ID = PD.LINE_LOCATION_ID
1009 AND PR.PO_RELEASE_ID (+) = PD.PO_RELEASE_ID
1010 AND (PL.CANCEL_FLAG IS NULL OR
1011 PL.CANCEL_FLAG = 'N')
1012 AND (PL.QUANTITY_RECEIVED<(PL.QUANTITY-PL.QUANTITY_CANCELLED))
1013 AND NVL(PL.CLOSED_CODE,'OPEN') <> 'FINALLY CLOSED'
1014 )
1015 --
1016 OR EXISTS
1017 (SELECT '1'
1018 FROM PO_REQUISITION_LINES_ALL PRL
1019 WHERE PRL.WIP_ENTITY_ID = wdct.WIP_ENTITY_ID
1020 AND PRL.DESTINATION_ORGANIZATION_ID = p_organization_id
1021 AND nvl(PRL.cancel_flag, 'N') = 'N'
1022 AND PRL.LINE_LOCATION_ID is NULL
1023 )
1024 OR EXISTS
1025 (SELECT '1'
1026 FROM PO_REQUISITIONS_INTERFACE_ALL PRI
1027 WHERE PRI.WIP_ENTITY_ID = wdct.WIP_ENTITY_ID
1028 AND PRI.DESTINATION_ORGANIZATION_ID = p_organization_id
1029 ) ;
1030
1031 l_failed_jobs WIP_DJ_CLOSE_TEMP.WIP_ENTITY_NAME%TYPE ;
1032 l_failed_counter NUMBER ;
1033
1034 BEGIN
1035 fnd_file.put_line(FND_FILE.LOG,'Open PO Check');
1036
1037 IF (l_logLevel <= wip_constants.trace_logging) THEN
1038 l_params(1).paramName := 'p_organization_id';
1039 l_params(1).paramValue := p_organization_id ;
1040 l_params(1).paramName := 'p_group_id';
1041 l_params(1).paramValue := p_group_id ;
1042 wip_logger.entryPoint(p_procName => 'wip_jobclose_priv.check_open_po',
1043 p_params => l_params,
1044 x_returnStatus => l_return_Status);
1045 END IF;
1046 x_returnStatus := fnd_api.g_ret_sts_success;
1047 l_failed_counter := 0 ;
1048
1049 OPEN c_open_po ;
1050 LOOP
1051 FETCH c_open_po INTO l_failed_jobs ;
1052 if (c_open_po%FOUND) then
1053 fnd_file.put_line(FND_FILE.LOG,'Open PO Exists');
1054 l_failed_counter := l_failed_counter + 1 ;
1055 x_returnStatus := fnd_api.g_ret_sts_error;
1056 fnd_file.put_line(FND_FILE.OUTPUT,to_char(l_failed_jobs));
1057 fnd_message.set_name('WIP', 'WIP_CANCEL_JOB/SCHED_OPEN_PO');
1058 l_msg := fnd_message.get;
1059 fnd_file.put_line(FND_FILE.OUTPUT,l_msg);
1060 fnd_file.put_line(FND_FILE.OUTPUT,'*******************');
1061 end if ;
1062
1063 EXIT WHEN c_open_po%NOTFOUND ;
1064 END LOOP ;
1065
1066 fnd_file.put_line(FND_FILE.LOG,'Number of jobs failed in Open PO : '||to_char( l_failed_counter));
1067
1068 IF (c_open_po%ISOPEN) THEN
1069 CLOSE c_open_po ;
1070 END IF;
1071
1072 --
1073 -- Bug 5345660 exitPoint for normal exit.
1074 --
1075 IF fnd_log.g_current_runtime_level <= wip_constants.trace_logging THEN
1076 wip_logger.exitPoint(
1077 p_procName => 'wip_close_priv.check_open_po',
1078 p_procReturnStatus => x_returnStatus,
1079 p_msg => 'procedure normal exit',
1080 x_returnStatus => l_return_status);
1081 END IF;
1082
1083 EXCEPTION
1084 WHEN others THEN
1085 IF (c_open_po%ISOPEN) THEN
1086 CLOSE c_open_po ;
1087 END IF;
1088 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1089 l_msg := 'unexpected error: ' || SQLERRM || 'SQLCODE = ' || SQLCODE;
1090
1091 IF (l_logLevel <= wip_constants.trace_logging) THEN
1092 wip_logger.exitPoint(p_procName=>'wip_close_priv.check_open_po',
1093 p_procReturnStatus => x_returnStatus,
1094 p_msg => l_msg,
1095 x_returnStatus => l_return_Status);
1096 END IF;
1097 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
1098 fnd_message.set_token('MESSAGE', l_msg);
1099 fnd_msg_pub.add;
1100
1101 END CHECK_OPEN_PO ;
1102
1103 procedure LOT_VALIDATE
1104 (
1105 x_returnstatus OUT NOCOPY VARCHAR2 ,
1106 p_organization_id IN NUMBER ,
1107 p_group_id IN NUMBER
1108 )
1109 IS
1110
1111 l_error_code number;
1112 l_err_msg varchar2(1000);
1113 l_wsm_org number ;
1114 l_return_status Varchar2(1);
1115 l_params wip_logger.param_tbl_t;
1116 l_msg VARCHAR(240);
1117 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
1118
1119 BEGIN
1120 x_returnStatus := fnd_api.g_ret_sts_success;
1121 fnd_file.put_line(FND_FILE.LOG,'lot validate');
1122 IF (l_logLevel <= wip_constants.trace_logging) THEN
1123 l_params(1).paramName := 'p_organization_id';
1124 l_params(1).paramValue := p_organization_id ;
1125 l_params(1).paramName := 'p_group_id';
1126 l_params(1).paramValue := p_group_id ;
1127 wip_logger.entryPoint(p_procName => 'wip_jobclose_priv.lot_validate',
1128 p_params => l_params,
1129 x_returnStatus => l_return_Status);
1130 END IF;
1131
1132 l_wsm_org := WSMPUTIL.CHECK_WSM_ORG(p_organization_id, l_error_code,l_err_msg);
1133
1134 If (l_wsm_org = 1) then
1135 WSMPUTIL.validate_lbj_before_close( p_group_id,
1136 p_organization_id,
1137 l_error_code,
1138 l_err_msg,
1139 l_return_status
1140 );
1141 End if;
1142
1143 --
1144 -- Bug 5345660 exitPoint for normal exit.
1145 --
1146 IF fnd_log.g_current_runtime_level <= wip_constants.trace_logging THEN
1147 wip_logger.exitPoint(
1148 p_procName => 'wip_close_priv.lot_validate',
1149 p_procReturnStatus => x_returnStatus,
1150 p_msg => 'procedure normal exit',
1151 x_returnStatus => l_return_status);
1152 END IF;
1153
1154 EXCEPTION
1155 WHEN others THEN
1156 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1157 l_msg := 'unexpected error: ' || SQLERRM || 'SQLCODE = ' || SQLCODE;
1158 IF (l_logLevel <= wip_constants.trace_logging) THEN
1159 wip_logger.exitPoint(p_procName=>'wip_jobclose_priv.lot_validate',
1160 p_procReturnStatus => x_returnStatus,
1161 p_msg => l_msg,
1162 x_returnStatus => l_return_Status);
1163 END IF;
1164 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
1165 fnd_message.set_token('MESSAGE', l_msg);
1166 fnd_msg_pub.add;
1167
1168 END LOT_VALIDATE ;
1169
1170
1171 procedure DELETE_RESERVATIONS
1172 (
1173 x_Returnstatus OUT NOCOPY VARCHAR2 ,
1174 p_organization_id IN NUMBER ,
1175 p_group_id IN NUMBER
1176
1177 )
1178 IS
1179 l_params wip_logger.param_tbl_t;
1180 l_return_Status VARCHAR2(1);
1181 l_msg VARCHAR(240);
1182 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
1183 l_rsv inv_reservation_global.mtl_reservation_rec_type;
1184 l_serialnumber inv_reservation_global.serial_number_tbl_type;
1185 l_status VARCHAR2(1) ;
1186 l_msg_count NUMBER;
1187
1188 CURSOR C_del_reservation IS
1189 SELECT wrv.reservation_id
1190 FROM wip_reservations_v wrv,
1191 wip_dj_close_temp wdct
1192 WHERE wdct.organization_id = p_organization_id
1193 AND wdct.group_id = p_group_id
1194 AND wdct.wip_entity_id = wrv.wip_entity_id;
1195
1196 BEGIN
1197 x_returnStatus := fnd_api.g_ret_sts_success;
1198 fnd_file.put_line(FND_FILE.LOG,'delete Existing reservations');
1199
1200 IF (l_logLevel <= wip_constants.trace_logging) THEN
1201 l_params(1).paramName := 'p_organization_id';
1202 l_params(1).paramValue := p_organization_id ;
1203 l_params(1).paramName := 'p_group_id';
1204 l_params(1).paramValue := p_group_id ;
1205 wip_logger.entryPoint(p_procName => 'wip_jobclose_priv.delete_reservation',
1206 p_params => l_params,
1207 x_returnStatus => l_return_Status);
1208 END IF;
1209 OPEN C_del_reservation ;
1210 LOOP
1211 FETCH C_del_reservation
1212 INTO l_rsv.reservation_id ;
1213
1214 /* Inventory Call for deleting reservations */
1215 inv_reservation_pub.delete_reservation
1216 (
1217 p_api_version_number => 1.0
1218 , p_init_msg_lst => fnd_api.g_true
1219 , x_return_status => l_status
1220 , x_msg_count => l_msg_count
1221 , x_msg_data => l_msg
1222 , p_rsv_rec => l_rsv
1223 , p_serial_number => l_serialnumber -- no serial control
1224 );
1225 EXIT WHEN c_del_reservation%NOTFOUND ;
1226 END LOOP ;
1227
1228 IF (c_del_reservation%ISOPEN) THEN
1229 CLOSE c_del_reservation ;
1230 END IF;
1231
1232 --
1233 -- Bug 5345660 exitPoint for normal exit.
1234 --
1235 IF fnd_log.g_current_runtime_level <= wip_constants.trace_logging THEN
1236 wip_logger.exitPoint(
1237 p_procName => 'wip_close_priv.delete_reservation',
1238 p_procReturnStatus => x_returnStatus,
1239 p_msg => 'procedure normal exit',
1240 x_returnStatus => l_return_status);
1241 END IF;
1242
1243 EXCEPTION
1244 WHEN others THEN
1245 IF (c_del_reservation%ISOPEN) THEN
1246 CLOSE c_del_reservation ;
1247 END IF;
1248 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1249 l_msg := 'unexpected error: ' || SQLERRM || 'SQLCODE = ' || SQLCODE;
1250
1251 IF (l_logLevel <= wip_constants.trace_logging) THEN
1252 wip_logger.exitPoint(p_procName=>'wip_jobclose_priv.delete_reservation',
1253 p_procReturnStatus => x_returnStatus,
1254 p_msg => l_msg,
1255 x_returnStatus => l_return_Status);
1256 END IF;
1257 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
1258 fnd_message.set_token('MESSAGE', l_msg);
1259 fnd_msg_pub.add;
1260
1261 END DELETE_RESERVATIONS ;
1262
1263
1264 /**************************************************************************
1265 * PROCEDURE TO WAIT FOR CONC. PROGRAM.
1266 * IT WILL RETURN ONLY AFTER THE CONC. PROGRAM COMPLETES
1267 /**************************************************************************/
1268
1269 PROCEDURE WAIT_CONC_PROGRAM(p_request_id in number,
1270 errbuf out NOCOPY varchar2,
1271 retcode out NOCOPY number) is
1272 l_call_status boolean;
1273 l_phase varchar2(80);
1274 l_status varchar2(80);
1275 l_dev_phase varchar2(80);
1276 l_dev_status varchar2(80);
1277 l_message varchar2(240);
1278
1279 l_counter number := 0;
1280 BEGIN
1281 LOOP
1282 l_call_status:= FND_CONCURRENT.WAIT_FOR_REQUEST
1283 ( p_request_id,
1284 10,
1285 -1,
1286 l_phase,
1287 l_status,
1288 l_dev_phase,
1289 l_dev_status,
1290 l_message);
1291 exit when l_call_status=false;
1292
1293 if (l_dev_phase='COMPLETE') then
1294 if (l_dev_status = 'NORMAL') then
1295 retcode := -1;
1296 elsif (l_dev_status = 'WARNING') then
1297 retcode := 1;
1298 else
1299 retcode := 2;
1300 end if;
1301 errbuf := l_message;
1302 return;
1303 end if;
1304
1305 l_counter := l_counter + 1;
1306 exit when l_counter >= 2;
1307
1308 end loop;
1309
1310 retcode := 2;
1311 return ;
1312 END WAIT_CONC_PROGRAM;
1313
1314 PROCEDURE RUN_REPORTS ( x_Returnstatus OUT NOCOPY VARCHAR2 ,
1315 p_group_id IN NUMBER ,
1316 p_organization_id IN NUMBER ,
1317 p_report_type IN NUMBER,
1318 p_class_type IN VARCHAR2 ,
1319 p_from_class IN VARCHAR2 ,
1320 p_to_class IN VARCHAR2 ,
1321 p_from_job IN VARCHAR2 ,
1322 p_to_job IN VARCHAR2 ,
1323 p_status IN VARCHAR2
1324 )
1325 IS
1326 l_req_id NUMBER;
1327 l_acct_period NUMBER;
1328 l_chart_of_accounts_id NUMBER;
1329 l_std_asst_jobs NUMBER;
1330 l_expense_jobs NUMBER;
1331 l_std_org_count NUMBER;
1332 l_acct_period_id NUMBER;
1333 SORT_BY_JOB NUMBER;
1334 l_precision_profile NUMBER;
1335 l_report_type NUMBER ;
1336 l_per_str_date VARCHAR2(30);
1337 l_per_cls_date VARCHAR2(30);
1338 wait BOOLEAN;
1339 phase VARCHAR2(2000);
1340 status VARCHAR2(2000);
1341 devphase VARCHAR2(2000);
1342 devstatus VARCHAR2(2000);
1343 message VARCHAR2(2000);
1344 errbuf VARCHAR2(200);
1345 retcode NUMBER ;
1346
1347 BEGIN
1348 fnd_file.put_line(FND_FILE.LOG,'Running Reports.........');
1349
1350 x_ReturnStatus := fnd_api.g_ret_sts_success;
1351 SORT_BY_JOB := 1 ;
1352 l_report_type := p_report_type ;
1353 l_precision_profile := fnd_profile.value('REPORT_QUANTITY_PRECISION');
1354
1355 IF ( l_precision_profile = NULL ) then
1356 l_precision_profile := 2;
1357 END IF ;
1358
1359 fnd_file.put_line(FND_FILE.LOG,'Report Quantity Precision');
1360
1361 SELECT COUNT(*)
1362 INTO l_std_asst_jobs
1363 FROM WIP_DJ_CLOSE_TEMP TEMP,
1364 WIP_DISCRETE_JOBS WDJ,
1365 WIP_ACCOUNTING_CLASSES WAC
1366 WHERE WDJ.WIP_ENTITY_ID = TEMP.WIP_ENTITY_ID
1367 AND TEMP.ORGANIZATION_ID = p_organization_id
1368 AND WDJ.ORGANIZATION_ID = TEMP.ORGANIZATION_ID
1369 AND WAC.ORGANIZATION_ID = TEMP.ORGANIZATION_ID
1370 AND WDJ.CLASS_CODE = WAC.CLASS_CODE
1371 AND TEMP.GROUP_ID = p_group_id
1372 AND WAC.CLASS_TYPE IN
1373 --(1,3,5,6)
1374 (WIP_CONSTANTS.DISC_CLASS,
1375 WIP_CONSTANTS.NS_ASSET_CLASS,
1376 WIP_CONSTANTS.LOT_CLASS,
1377 WIP_CONSTANTS.EAM_CLASS ) ;
1378
1379 SELECT COUNT(*)
1380 INTO l_expense_jobs
1381 FROM WIP_DJ_CLOSE_TEMP TEMP,
1382 WIP_DISCRETE_JOBS WDJ,
1383 WIP_ACCOUNTING_CLASSES WAC
1384 WHERE WDJ.WIP_ENTITY_ID = TEMP.WIP_ENTITY_ID
1385 AND TEMP.ORGANIZATION_ID = p_organization_id
1386 AND WDJ.ORGANIZATION_ID = p_organization_id
1387 AND WAC.ORGANIZATION_ID = p_organization_id
1388 AND WDJ.CLASS_CODE = WAC.CLASS_CODE
1389 AND TEMP.GROUP_ID = p_group_id
1390 AND WAC.CLASS_TYPE = WIP_CONSTANTS.NS_EXPENSE_CLASS ;
1391
1392 -- Bug 4890159. Performance Fix
1393 -- saugupta 1-Jun-06
1394 /*
1395 SELECT CHART_OF_ACCOUNTS_ID
1396 INTO l_chart_of_accounts_id
1397 FROM ORG_ORGANIZATION_DEFINITIONS
1398 WHERE ORGANIZATION_ID = p_organization_id ;
1399 */
1400 SELECT lgr.chart_of_accounts_id chart_of_accounts_id
1401 INTO l_chart_of_accounts_id
1402 FROM hr_organization_information hoi,
1403 gl_ledgers lgr
1404 WHERE hoi.organization_id = p_organization_id
1405 and hoi.org_information_context = 'Accounting Information'
1406 and (ltrim(hoi.org_information1,'0123456789') is null
1407 and hoi.org_information1 = lgr.ledger_id )
1408 and lgr.object_type_code = 'L'
1409 AND nvl(complete_flag, 'Y') = 'Y';
1410
1411 SELECT ACCT_PERIOD_ID,
1412 to_char(PERIOD_START_DATE,'YYYY/MM/DD'),
1413 to_char(SCHEDULE_CLOSE_DATE,'YYYY/MM/DD')
1414 INTO l_acct_period_id ,
1415 l_per_str_date,
1416 l_per_cls_date
1417 FROM ORG_ACCT_PERIODS
1418 WHERE INV_LE_TIMEZONE_PUB.GET_LE_DAY_FOR_INV_ORG (SYSDATE, p_organization_id) >= TRUNC(PERIOD_START_DATE)
1419 AND INV_LE_TIMEZONE_PUB.GET_LE_DAY_FOR_INV_ORG (SYSDATE, p_organization_id) <= TRUNC(SCHEDULE_CLOSE_DATE)
1420 AND ORGANIZATION_ID = p_organization_id;
1421
1422 SELECT COUNT(*)
1423 INTO l_std_org_count
1424 FROM MTL_PARAMETERS
1425 WHERE ORGANIZATION_ID = p_organization_id
1426 AND PRIMARY_COST_METHOD = 1 ;
1427
1428 IF (l_std_asst_jobs >= 1) and ( l_report_type <> 4 ) THEN
1429 /* STANDARD AND ASSET JOBS */
1430
1431 IF (l_std_org_count = 1 ) THEN
1432 fnd_file.put_line(FND_FILE.LOG,'--------WIPRDJVR---------');
1433
1434 l_req_id := FND_REQUEST.SUBMIT_REQUEST('WIP','WIPRDJVR',NULL,NULL,
1435 NULL,
1436 to_char(p_organization_id), -- Organziation id Parameter 1
1437 to_char(l_chart_of_accounts_id), -- Parameter 2
1438 to_char(l_acct_period_id), -- Parameter 3
1439 to_char(l_precision_profile), -- Parameter 4
1440 'PLS', -- default SRS -- Parameter 5
1441 to_char(SORT_BY_JOB), -- Parameter 6
1442 to_char(l_report_type), -- Parameter 7
1443 NULL, -- 1 ,p_class_type Parameter 8
1444 NULL,--1 -- Parameter 9
1445 NULL,--1 -- Parameter 10
1446 NULL, -- p_from_class Parameter 11
1447 NULL, -- p_to_class Parameter 12
1448 NULL, -- p_from_job Parameter 13
1449 NULL , -- p_to_job Parameter 14
1450 NULL, -- Status type Parameter 15
1451 NULL, -- Parameter 16
1452 NULL, -- Parameter 17
1453 NULL, -- Currency Code Parameter 18
1454 NULL, --'N' Parameter 19
1455 NULL, --2 Exchange Rate type Parameter 20
1456 NULL, --1 Exchange Rate Parameter 21
1457 NULL,
1458 NULL,
1459 p_group_id , -- Group Id Parameter 24
1460 NULL, NULL, NULL, NULL, NULL, NULL,
1461 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1462 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1463 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1464 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1465 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1466 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1467 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
1468
1469 ELSE
1470 fnd_file.put_line(FND_FILE.LOG,'CSTRDJVA');
1471
1472 l_req_id := FND_REQUEST.SUBMIT_REQUEST('BOM','CSTRDJVA',NULL,NULL,
1473 FALSE,
1474 NULL , -- Parameter 1
1475 NULL , -- Parameter 2
1476 NULL , -- Parameter 3
1477 NULL , -- Parameter 4
1478 to_char(p_organization_id), -- Parameter 5
1479 to_char(l_chart_of_accounts_id), -- Parameter 6
1480 to_char(l_acct_period_id), -- Parameter 7
1481 to_char( l_precision_profile), -- Parameter 8
1482 'PLS',
1483 to_char(SORT_BY_JOB), -- Parameter 10
1484 to_char(l_report_type), -- Parameter 11
1485 NULL, -- Parameter 12
1486 NULL,
1487 NULL,
1488 NULL, -- Parameter 15
1489 NULL, -- Parameter 16
1490 NULL, -- Parameter 17
1491 NULL, -- Parameter 18
1492 NULL, -- Parameter 19
1493 NULL, -- Parameter 20
1494 NULL, NULL, NULL, NULL, NULL ,
1495 p_group_id , NULL, NULL , NULL, NULL,
1496 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1497 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1498 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1499 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1500 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1501 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1502 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
1503
1504 END IF ;
1505
1506 COMMIT ;
1507
1508 IF (l_req_id = 0) THEN
1509 RETCODE := 2;
1510 RETURN;
1511 END IF;
1512
1513 WAIT_CONC_PROGRAM(l_req_id,ERRBUF,RETCODE);
1514
1515 FND_FILE.PUT_LINE(FND_FILE.LOG,'Costing Report Concurrent Program return code : '||retcode);
1516
1517 if (retcode <> -1 ) then
1518 FND_FILE.PUT_LINE(FND_FILE.LOG,'Report has errored or has a warning');
1519 errbuf := fnd_message.get;
1520 raise FND_API.G_EXC_ERROR ;
1521 end if;
1522
1523 END IF ;
1524
1525 /* EXPENSE REPORTS */
1526
1527 IF (l_expense_jobs >= 1) and ( l_report_type <> 4 ) THEN
1528
1529 l_report_type := 1 ; -- Always detailed reports only
1530 fnd_file.put_line(FND_FILE.LOG,'WIPREJVR');
1531
1532 l_req_id := FND_REQUEST.SUBMIT_REQUEST('WIP','WIPREJVR',NULL,NULL,
1533 FALSE,
1534 to_char(p_organization_id), -- Parameter 1
1535 to_char(l_chart_of_accounts_id), -- Parameter 2
1536 to_char(l_precision_profile), -- Parameter 3
1537 'PLS', -- Parameter 4
1538 to_char(SORT_BY_JOB), -- Parameter 5
1539 to_char(l_report_type), -- Parameter 6
1540 l_per_str_date,
1541 l_per_cls_date,
1542 NULL, -- Parameter 9
1543 NULL, -- Parameter 10
1544 NULL, -- Parameter 11
1545 NULL, -- Parameter 12
1546 NULL, -- Parameter 13
1547 NULL, NULL, p_group_id ,
1548 NULL, NULL, NULL, NULL,
1549 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1550 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1551 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1552 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1553 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1554 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1555 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1556 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
1557
1558 COMMIT ;
1559
1560 IF (l_req_id = 0) THEN
1561 RETCODE := 2;
1562 RETURN;
1563 END IF;
1564
1565 WAIT_CONC_PROGRAM(l_req_id,ERRBUF,RETCODE);
1566
1567 FND_FILE.PUT_LINE(FND_FILE.LOG,'Expense report reurn code : '||retcode);
1568
1569 if (retcode <> -1 ) then
1570 FND_FILE.PUT_LINE(FND_FILE.LOG,'Report has errored or has a warning');
1571 errbuf := fnd_message.get;
1572 raise FND_API.G_EXC_ERROR ;
1573 end if;
1574
1575 END IF ; -- Expense Reports End
1576
1577 EXCEPTION
1578 WHEN OTHERS THEN
1579 x_ReturnStatus := FND_API.G_RET_STS_ERROR;
1580 END RUN_REPORTS;
1581
1582
1583
1584 /**************************************************************************
1585 * This is the main API . This is the equivalent of wicdcl.opp .This API *
1586 * closes discrete jobs .
1587 **************************************************************************/
1588
1589
1590 procedure WIP_CLOSE
1591 (
1592 p_organization_id IN NUMBER ,
1593 p_class_type IN VARCHAR2 ,
1594 p_from_class IN VARCHAR2 ,
1595 p_to_class IN VARCHAR2 ,
1596 p_from_job IN VARCHAR2 ,
1597 p_to_job IN VARCHAR2 ,
1598 p_from_release_date IN VARCHAR2 ,
1599 p_to_release_date IN VARCHAR2 ,
1600 p_from_start_date IN VARCHAR2 ,
1601 p_to_start_date IN VARCHAR2 ,
1602 p_from_completion_date IN VARCHAR2 ,
1603 p_to_completion_date IN VARCHAR2 ,
1604 p_status IN VARCHAR2 ,
1605 p_group_id IN NUMBER ,
1606 p_select_jobs IN NUMBER ,
1607 p_exclude_reserved_jobs IN VARCHAR2 ,
1608 p_uncompleted_jobs IN VARCHAR2,
1609 p_exclude_pending_txn_jobs IN VARCHAR2 ,
1610 p_report_type IN VARCHAR2 ,
1611 p_act_close_date IN VARCHAR2 ,
1612 x_warning OUT NOCOPY NUMBER ,
1613 x_returnStatus OUT NOCOPY VARCHAR2
1614 )
1615 IS
1616 l_group_id NUMBER ;
1617 l_from_release_date DATE ;
1618 l_to_release_date DATE ;
1619 l_from_start_date DATE ;
1620 l_to_start_date DATE ;
1621 l_from_completion_date DATE ;
1622 l_to_completion_date DATE ;
1623 l_act_close_date DATE ;
1624 l_ret_code NUMBER;
1625 l_at_submission_time NUMBER;
1626 l_immediate NUMBER;
1627 l_acct_period_id NUMBER;
1628 l_num_close NUMBER;
1629 l_dest_day_time DATE;
1630 l_per_str_date DATE;
1631 l_per_cls_date DATE;
1632 l_costing_group_id NUMBER;
1633 l_return_status VARCHAR2(1) ;
1634 l_msg_count NUMBER;
1635 l_msg_data VARCHAR2(200);
1636 l_msg VARCHAR2(2000);
1637 l_req_id NUMBER;
1638 l_params wip_logger.param_tbl_t;
1639 l_errMsg VARCHAR2(240);
1640 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
1641
1642 BEGIN
1643
1644 l_at_submission_time := 1 ; ---SRS
1645 l_immediate := 2 ; -- From Close Form
1646 x_returnStatus := FND_API.G_RET_STS_SUCCESS ;
1647
1648 fnd_file.put_line(FND_FILE.LOG,'WIP DISCRETE JOB CLOSE');
1649
1650 fnd_file.put_line(FND_FILE.OUTPUT,'*****************************');
1651 fnd_file.put_line(FND_FILE.OUTPUT,'WIP DISCRETE JOB CLOSE OUTPUT');
1652 fnd_file.put_line(FND_FILE.OUTPUT,'*****************************');
1653 -- write parameter value to log file
1654
1655 IF (l_logLevel <= wip_constants.trace_logging) THEN
1656 l_params(1).paramName := 'p_organization_id';
1657 l_params(1).paramValue := p_organization_id ;
1658 l_params(2).paramName := 'p_class_type';
1659 l_params(2).paramValue := p_class_type;
1660 l_params(3).paramName := 'p_from_class';
1661 l_params(3).paramValue := p_from_class;
1662 l_params(4).paramName := 'p_to_class';
1663 l_params(4).paramValue := p_to_class;
1664 l_params(5).paramName := 'p_from_job';
1665 l_params(5).paramValue := p_from_job;
1666 l_params(6).paramName := 'p_to_job';
1667 l_params(6).paramValue := p_to_job;
1668 l_params(7).paramName := 'p_from_release_date';
1669 l_params(7).paramValue := p_from_release_date;
1670 l_params(8).paramName := 'p_to_release_date';
1671 l_params(8).paramValue := p_to_release_date;
1672 wip_logger.entryPoint(p_procName => 'wip_jobclose_priv.wip_close',
1673 p_params => l_params,
1674 x_returnStatus => l_return_Status);
1675 END IF;
1676
1677
1678 IF ( p_select_jobs = l_at_submission_time) THEN
1679
1680 /***************************************************/
1681 /* TIME ZONE CONVERSION */
1682 /***************************************************/
1683
1684 TIME_ZONE_CONVERSIONS(
1685 p_from_release_date => p_from_release_date ,
1686 p_to_release_date => p_to_release_date ,
1687 p_from_start_date => p_from_start_date ,
1688 p_to_start_date => p_to_start_date ,
1689 p_from_completion_date => p_from_completion_date ,
1690 p_to_completion_date => p_to_completion_date ,
1691 p_act_close_date => p_act_close_date ,
1692 x_from_release_date => l_from_release_date ,
1693 x_to_release_date => l_to_release_date ,
1694 x_from_start_date => l_from_start_date ,
1695 x_to_start_date => l_to_start_date ,
1696 x_from_completion_date => l_from_completion_date ,
1697 x_to_completion_date => l_to_completion_date ,
1698 x_act_close_date => l_act_close_date ,
1699 x_returnstatus => l_return_status
1700 );
1701 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1702 IF fnd_log.g_current_runtime_level <= wip_constants.trace_logging THEN
1703 wip_logger.log(p_msg => 'time zone conversion failed',
1704 x_returnStatus => l_return_Status);
1705 END IF;
1706 RAISE FND_API.G_EXC_ERROR ;
1707 END IF;
1708
1709 if ( sysdate > l_act_close_date ) then
1710 fnd_file.put_line(FND_FILE.LOG,'WIP DISCRETE CLOSE');
1711 else
1712 fnd_message.set_name('WIP','CLOSE DATE');
1713 l_msg := fnd_message.get;
1714 l_msg := l_msg || ' ' || l_act_close_date ;
1715 fnd_message.set_name('WIP','WIP_LESS_OR_EQUAL');
1716 fnd_message.set_token('ENTITY1',l_msg);
1717 fnd_message.set_token('ENTITY2', sysdate);
1718 l_msg := fnd_message.get;
1719 fnd_file.put_line(FND_FILE.OUTPUT,l_msg);
1720 fnd_file.put_line(FND_FILE.OUTPUT,'*******************');
1721 fnd_file.put_line(FND_FILE.LOG,l_msg);
1722 RAISE FND_API.G_EXC_ERROR ;
1723 end if ;
1724
1725 /****************************************************************
1726 * *
1727 * Check for ACTUAL CLOSE DATE to be in an open accounting period*
1728 * *
1729 *****************************************************************/
1730 BEGIN
1731
1732 SELECT ACCT_PERIOD_ID
1733 INTO l_acct_period_id
1734 FROM ORG_ACCT_PERIODS
1735 WHERE ORGANIZATION_ID = p_organization_id
1736 AND INV_LE_TIMEZONE_PUB.GET_LE_DAY_FOR_INV_ORG (l_act_close_date,p_organization_id)
1737 BETWEEN PERIOD_START_DATE AND SCHEDULE_CLOSE_DATE
1738 AND PERIOD_CLOSE_DATE IS NULL;
1739
1740 EXCEPTION
1741 WHEN NO_DATA_FOUND THEN
1742 fnd_message.set_name('WIP','WIP_CLOSE_CLOSED_PERIOD');
1743 l_msg := fnd_message.get;
1744 fnd_file.put_line(FND_FILE.OUTPUT,l_msg);
1745 fnd_file.put_line(FND_FILE.OUTPUT,'*******************');
1746 fnd_file.put_line(FND_FILE.LOG,l_msg) ;
1747 RAISE FND_API.G_EXC_ERROR ;
1748 END ;
1749
1750 /**********************************************************
1751 * *
1752 * This procedure populates details into temp table before *
1753 * deletion . This is an equivalent API of wildct.ppc *
1754 * *
1755 **********************************************************/
1756
1757 populate_close_temp(
1758 p_organization_id => p_organization_id ,
1759 p_class_type => p_class_type ,
1760 p_from_class => p_from_class ,
1761 p_to_class => p_to_class ,
1762 p_from_job => p_from_job ,
1763 p_to_job => p_to_job ,
1764 p_from_release_date => l_from_release_date ,
1765 p_to_release_date => l_to_release_date ,
1766 p_from_start_date => l_from_start_date ,
1767 p_to_start_date => l_to_start_date ,
1768 p_from_completion_date => l_from_completion_date ,
1769 p_to_completion_date => l_to_completion_date ,
1770 p_status => to_number(p_status),
1771 p_exclude_reserved_jobs => p_exclude_reserved_jobs ,
1772 p_exclude_pending_txn_jobs => p_exclude_pending_txn_jobs ,
1773 p_report_type => p_report_type ,
1774 p_act_close_date => l_act_close_date ,
1775 x_group_id => l_group_id ,
1776 x_ReturnStatus => l_return_status
1777 );
1778 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1779 IF fnd_log.g_current_runtime_level <= wip_constants.trace_logging THEN
1780 wip_logger.log(p_msg => 'populate_close_temp',
1781 x_returnStatus => l_return_Status);
1782 END IF;
1783 RAISE FND_API.G_EXC_ERROR ;
1784 END IF;
1785
1786 /*Bug 6908428: Raise workflow notifications for eam workorders for status change to Pending close */
1787 EAM_WorkOrderTransactions_PUB.RAISE_WORKFLOW_STATUS_PEND_CLS(
1788 p_group_id => l_group_id ,
1789 p_new_status => WIP_CONSTANTS.PEND_CLOSE ,
1790 ERRBUF => l_errMsg ,
1791 RETCODE => l_return_status
1792 );
1793
1794 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1795 IF fnd_log.g_current_runtime_level <= wip_constants.trace_logging THEN
1796 wip_logger.log(p_msg => 'error during eam update workflow to pending close' || l_errMsg,
1797 x_returnStatus => l_return_Status);
1798 END IF;
1799 RAISE FND_API.G_EXC_ERROR ;
1800 END IF;
1801 /*Bug 6908428*/
1802
1803 UPDATE wip_discrete_jobs
1804 SET status_type = WIP_CONSTANTS.PEND_CLOSE ,
1805 request_id = fnd_global.conc_request_id ,
1806 last_update_date = sysdate,
1807 last_updated_by = fnd_global.user_id,
1808 last_update_login = fnd_global.login_id,
1809 program_application_id = fnd_global.prog_appl_id,
1810 program_id = fnd_global.conc_program_id
1811 WHERE organization_id = p_organization_id
1812 AND wip_entity_id in (SELECT wip_entity_id
1813 FROM wip_dj_close_temp
1814 WHERE group_id = l_group_id
1815 AND organization_id = p_organization_id);
1816
1817 /*Bug 6908428: Updating the status in eam_work_order_details pending close for eam workorders */
1818 EAM_WorkOrderTransactions_PUB.Update_EWOD(
1819 p_group_id => l_group_id,
1820 p_organization_id => p_organization_id,
1821 p_new_status => WIP_CONSTANTS.PEND_CLOSE,
1822 ERRBUF => l_errMsg,
1823 RETCODE => l_return_status
1824 );
1825
1826 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1827 IF fnd_log.g_current_runtime_level <= wip_constants.trace_logging THEN
1828 wip_logger.log(p_msg => 'eam update workoder error during pending close ' || l_errMsg,
1829 x_returnStatus => l_return_Status);
1830 END IF;
1831 RAISE FND_API.G_EXC_ERROR ;
1832 END IF;
1833
1834 /*Bug 6908428*/
1835
1836
1837 CANCEL_PO(
1838 x_returnstatus => l_return_status,
1839 p_organization_id => p_organization_id,
1840 p_group_id => l_group_id);
1841
1842 ELSE
1843 /* When the concurrent program is being called from Form */
1844 l_group_id := p_group_id ;
1845
1846 END IF ;
1847
1848 fnd_file.put_line(FND_FILE.LOG,'GROUP ID '||to_char(l_group_id));
1849
1850 /**********************************************************
1851 * *
1852 * Checks if Job Release date <= actual close date *
1853 * *
1854 **********************************************************/
1855
1856 PRIOR_DATE_RELEASE(
1857 x_returnstatus => l_return_status,
1858 p_organization_id => p_organization_id,
1859 p_group_id => l_group_id);
1860
1861 IF ( l_return_status = FND_API.G_RET_STS_ERROR ) THEN
1862 fnd_message.set_name('WIP', 'WIP_PRIOR_DATE_RELEASE');
1863 l_msg := fnd_message.get;
1864 fnd_file.put_line(FND_FILE.OUTPUT,l_msg);
1865 fnd_file.put_line(FND_FILE.OUTPUT,'*******************');
1866 x_warning := 1 ;
1867 --
1868 -- Bug 5345660 Added profile check before invoking wip_logger
1869 --
1870 IF fnd_log.g_current_runtime_level <= wip_constants.trace_logging THEN
1871 wip_logger.log(p_msg => l_msg,
1872 x_returnStatus => l_return_Status);
1873 END IF;
1874 END IF;
1875
1876 /**********************************************************
1877 * *
1878 * Close all exceptions for this Job *
1879 * *
1880 **********************************************************/
1881
1882 CLOSE_JOB_EXCEPTIONS(
1883 x_returnstatus => l_return_status,
1884 p_organization_id => p_organization_id,
1885 p_group_id => l_group_id);
1886
1887 IF ( l_return_status = FND_API.G_RET_STS_ERROR ) THEN
1888 x_warning := 1 ;
1889 END IF;
1890
1891 /**********************************************************
1892 * *
1893 * This call validates if any pending clocks exist *
1894 * and error out if there are any . *
1895 * *
1896 **********************************************************/
1897
1898 PENDING_CLOCKS(
1899 x_returnstatus => l_return_status,
1900 p_organization_id => p_organization_id,
1901 p_group_id => l_group_id);
1902
1903 IF ( l_return_status = FND_API.G_RET_STS_ERROR ) THEN
1904 fnd_message.set_name('WIP', 'WIP_PENDING_CLOCKS');
1905 l_msg := fnd_message.get;
1906 fnd_file.put_line(FND_FILE.OUTPUT,l_msg);
1907 fnd_file.put_line(FND_FILE.OUTPUT,'*******************');
1908 x_warning := 1 ;
1909 --
1910 -- Bug 5345660 Added profile check before invoking wip_logger
1911 --
1912 IF fnd_log.g_current_runtime_level <= wip_constants.trace_logging THEN
1913 wip_logger.log(p_msg => l_msg,
1914 x_returnStatus => l_return_Status);
1915 END IF;
1916 END IF;
1917 /**********************************************************
1918 * *
1919 * Try to cancel any move orders or tasks created by the *
1920 * component picking process related to this job. *
1921 * *
1922 * *
1923 **********************************************************/
1924
1925 CANCEL_MOVE_ORDERS(
1926 x_returnstatus => l_return_status,
1927 p_organization_id => p_organization_id,
1928 p_group_id => l_group_id);
1929 IF ( l_return_status = FND_API.G_RET_STS_ERROR ) THEN
1930 fnd_message.set_name('WIP', 'TRANSACTIONS PENDING');
1931 l_msg := fnd_message.get;
1932 fnd_file.put_line(FND_FILE.OUTPUT,l_msg);
1933 fnd_file.put_line(FND_FILE.OUTPUT,'*******************');
1934 x_warning := 1 ;
1935 --
1936 -- Bug 5345660 Added profile check before invoking wip_logger
1937 --
1938 IF fnd_log.g_current_runtime_level <= wip_constants.trace_logging THEN
1939 wip_logger.log(p_msg => l_msg,
1940 x_returnStatus => l_return_Status);
1941 END IF;
1942 END IF;
1943
1944 /**********************************************************
1945 * *
1946 * This call validates if any pending transactions exist *
1947 * and error out if there are any . *
1948 * *
1949 **********************************************************/
1950
1951 PENDING_TXNS(
1952 x_returnstatus => l_return_status,
1953 p_organization_id => p_organization_id,
1954 p_group_id => l_group_id);
1955
1956 IF ( l_return_status = FND_API.G_RET_STS_ERROR ) THEN
1957 fnd_message.set_name('WIP', 'TRANSACTIONS PENDING');
1958 l_msg := fnd_message.get;
1959 fnd_file.put_line(FND_FILE.OUTPUT,l_msg);
1960 fnd_file.put_line(FND_FILE.OUTPUT,'*******************');
1961 x_warning := 1 ;
1962 --
1963 -- Bug 5345660 Added profile check before invoking wip_logger
1964 --
1965 IF fnd_log.g_current_runtime_level <= wip_constants.trace_logging THEN
1966 wip_logger.log(p_msg => l_msg,
1967 x_returnStatus => l_return_Status);
1968 END IF;
1969 END IF;
1970
1971 /**********************************************************
1972 * *
1973 * This call validates if the close date is in past. *
1974 * *
1975 **********************************************************/
1976
1977 PAST_CLOSE_DATE(
1978 x_returnstatus => l_return_status,
1979 p_organization_id => p_organization_id,
1980 p_group_id => l_group_id);
1981
1982 IF ( l_return_status = FND_API.G_RET_STS_ERROR ) THEN
1983 fnd_message.set_name('WIP', 'CLOSE DATE IN PAST');
1984 l_msg := fnd_message.get;
1985 fnd_file.put_line(FND_FILE.OUTPUT,l_msg);
1986 fnd_file.put_line(FND_FILE.OUTPUT,'*******************');
1987 x_warning := 1 ;
1988 --
1989 -- Bug 5345660 Added profile check before invoking wip_logger
1990 --
1991 IF fnd_log.g_current_runtime_level <= wip_constants.trace_logging THEN
1992 wip_logger.log(p_msg => l_msg,
1993 x_returnStatus => l_return_Status);
1994 END IF;
1995 END IF;
1996
1997 /**********************************************************
1998 * *
1999 * This call validates if any open purchase orders exist *
2000 * and Warn if there are any . This check is done *
2001 * only from SRS *
2002 **********************************************************/
2003
2004 IF ( p_select_jobs = l_at_submission_time) THEN
2005
2006 CHECK_OPEN_PO(
2007 x_returnstatus => l_return_status,
2008 p_organization_id => p_organization_id,
2009 p_group_id => l_group_id);
2010
2011 IF ( l_return_status = FND_API.G_RET_STS_ERROR ) THEN
2012 x_warning := 1 ;
2013 fnd_message.set_name('WIP', 'WIP_CANCEL_JOB/SCHED_OPEN_PO');
2014 l_msg := fnd_message.get;
2015 --
2016 -- Bug 5345660 Added profile check before invoking wip_logger
2017 --
2018 IF fnd_log.g_current_runtime_level <= wip_constants.trace_logging THEN
2019 wip_logger.log(p_msg => l_msg,
2020 x_returnStatus => l_return_Status);
2021 END IF;
2022 END IF;
2023 END IF ;
2024
2025 /**********************************************************
2026 * *
2027 * This validation is for LOT Based Jobs . *
2028 * *
2029 **********************************************************/
2030
2031 LOT_VALIDATE(
2032 x_returnstatus => l_return_status,
2033 p_organization_id => p_organization_id,
2034 p_group_id => l_group_id );
2035 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2036 IF fnd_log.g_current_runtime_level <= wip_constants.trace_logging THEN
2037 wip_logger.log(p_msg => 'LOT_VALIDATE procedure failed',
2038 x_returnStatus => l_return_Status);
2039 END IF;
2040 RAISE FND_API.G_EXC_ERROR ;
2041 END IF;
2042
2043 /**********************************************************
2044 * *
2045 * Cover routine for the inventory API delete_reservation. *
2046 * *
2047 **********************************************************/
2048
2049
2050 DELETE_RESERVATIONS(
2051 x_returnstatus => l_return_status,
2052 p_organization_id => p_organization_id,
2053 p_group_id => l_group_id );
2054
2055 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2056 IF fnd_log.g_current_runtime_level <= wip_constants.trace_logging THEN
2057 wip_logger.log(p_msg => 'DELETE_RESERVATIONS procedure failed',
2058 x_returnStatus => l_return_Status);
2059 END IF;
2060 END IF;
2061
2062
2063 SELECT ACCT_PERIOD_ID
2064 INTO l_acct_period_id
2065 FROM ORG_ACCT_PERIODS
2066 WHERE TRUNC(SYSDATE) >= TRUNC(PERIOD_START_DATE)
2067 AND TRUNC(SYSDATE) <= TRUNC(SCHEDULE_CLOSE_DATE)
2068 AND ORGANIZATION_ID = p_organization_id;
2069
2070 /*********************************************************
2071 * This is a hook which returns success and can be used to*
2072 * call other procedures depending on client requirements *
2073 **********************************************************/
2074
2075 WIP_CLOSE_JOB_HOOK.WIP_CLOSE_JOB_HOOK_PRC
2076 (P_group_id => l_group_id,
2077 P_org_id => p_organization_id ,
2078 P_acct_per_id => l_acct_period_id ,
2079 P_ret_code => l_ret_code ,
2080 P_err_buf => l_errMsg );
2081
2082 IF (l_ret_code <> 0 ) THEN
2083 RAISE FND_API.G_EXC_ERROR ;
2084 END IF;
2085
2086
2087 /*****************************************
2088 * *
2089 * Costing Function updates *
2090 * *
2091 ******************************************/
2092
2093 SELECT WIP_TRANSACTIONS_S.nextval
2094 INTO l_costing_group_id
2095 FROM DUAL;
2096
2097 INSERT INTO WIP_COST_TXN_INTERFACE
2098 (LAST_UPDATE_DATE,
2099 LAST_UPDATED_BY,
2100 LAST_UPDATE_LOGIN,
2101 CREATION_DATE,
2102 CREATED_BY,
2103 REQUEST_ID,
2104 PROGRAM_APPLICATION_ID,
2105 PROGRAM_ID,
2106 PROGRAM_UPDATE_DATE,
2107 TRANSACTION_ID,
2108 ACCT_PERIOD_ID,
2109 GROUP_ID,
2110 PROCESS_STATUS,
2111 PROCESS_PHASE,
2112 TRANSACTION_TYPE,
2113 ORGANIZATION_ID,
2114 WIP_ENTITY_ID,
2115 WIP_ENTITY_NAME,
2116 ENTITY_TYPE,
2117 TRANSACTION_DATE)
2118 SELECT
2119 SYSDATE,
2120 fnd_global.user_id,
2121 fnd_global.login_id ,
2122 SYSDATE,
2123 fnd_global.user_id,
2124 fnd_global.conc_request_id ,
2125 fnd_global.prog_appl_id,
2126 fnd_global.conc_program_id ,
2127 SYSDATE,
2128 WIP_TRANSACTIONS_S.nextval,
2129 oap.ACCT_PERIOD_ID,
2130 l_costing_group_id,
2131 2, -- PROCESS_STATUS
2132 3, -- PROCESS_PHASE
2133 6, -- TRANSACTION_TYPE
2134 p_organization_id,
2135 wdct.WIP_ENTITY_ID,
2136 wdct.WIP_ENTITY_NAME,
2137 we.ENTITY_TYPE,
2138 wdct.ACTUAL_CLOSE_DATE
2139 FROM WIP_DJ_CLOSE_TEMP wdct,
2140 ORG_ACCT_PERIODS oap,
2141 WIP_ENTITIES we
2142 WHERE wdct.GROUP_ID = l_group_id
2143 AND we.wip_entity_id = wdct.wip_entity_id
2144 AND we.organization_id = p_organization_id
2145 AND wdct.ORGANIZATION_ID = p_organization_id
2146 AND oap.ORGANIZATION_ID = p_organization_id
2147 AND oap.PERIOD_CLOSE_DATE IS NULL
2148 AND INV_LE_TIMEZONE_PUB.GET_LE_DAY_FOR_INV_ORG (wdct.ACTUAL_CLOSE_DATE, wdct.ORGANIZATION_ID)
2149 BETWEEN oap.PERIOD_START_DATE and oap.SCHEDULE_CLOSE_DATE;
2150
2151 /*==============================================================+
2152 | CALL COSTING function to update variances |
2153 |===============================================================*/
2154
2155 CST_JobCloseVar_GRP.Calculate_Job_Variance
2156 (
2157 p_api_version => 1.0,
2158 p_init_msg_list => FND_API.G_FALSE,
2159 p_commit => FND_API.G_FALSE,
2160 p_validation_level => FND_API.G_VALID_LEVEL_FULL ,
2161 x_return_status => l_return_status,
2162 x_msg_count => l_msg_count,
2163 x_msg_data => l_msg_data,
2164 p_user_id => fnd_global.user_id,
2165 p_login_id => fnd_global.login_id,
2166 p_prg_appl_id => fnd_global.prog_appl_id,
2167 p_prg_id => fnd_global.conc_program_id,
2168 p_req_id => fnd_global.conc_request_id,
2169 p_wcti_group_id => l_costing_group_id,
2170 p_org_id => p_organization_id
2171 );
2172 -- Bug 5370550
2173 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2174 IF fnd_log.g_current_runtime_level <= wip_constants.trace_logging THEN
2175 wip_logger.log(p_msg => 'costing function error',
2176 x_returnStatus => l_return_Status);
2177 END IF;
2178 RAISE FND_API.G_EXC_ERROR ;
2179 END IF;
2180
2181
2182 /* Closing the jobs */
2183
2184 /*Bug 6908428: updating the status of eam workorders in eam_work_order_details to closed and workflow update*/
2185 EAM_WorkOrderTransactions_PUB.RAISE_WORKFLOW_STATUS_PEND_CLS(
2186 p_group_id => l_group_id,
2187 p_new_status => WIP_CONSTANTS.CLOSED,
2188 ERRBUF => l_errMsg,
2189 RETCODE => l_return_Status
2190 );
2191
2192 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2193 IF fnd_log.g_current_runtime_level <= wip_constants.trace_logging THEN
2194 wip_logger.log(p_msg => 'error during eam update workflow to closed' || l_errMsg,
2195 x_returnStatus => l_return_Status);
2196 END IF;
2197 RAISE FND_API.G_EXC_ERROR ;
2198 END IF;
2199
2200 EAM_WorkOrderTransactions_PUB.Update_EWOD(
2201 p_group_id => l_group_id,
2202 p_organization_id => p_organization_id,
2203 p_new_status => WIP_CONSTANTS.CLOSED,
2204 ERRBUF => l_errMsg,
2205 RETCODE => l_return_status
2206 );
2207
2208 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2209 IF fnd_log.g_current_runtime_level <= wip_constants.trace_logging THEN
2210 wip_logger.log(p_msg => 'eam update workoder error while job close' || l_errMsg,
2211 x_returnStatus => l_return_Status);
2212 END IF;
2213 RAISE FND_API.G_EXC_ERROR ;
2214 END IF;
2215
2216 /*Bug 6908428*/
2217
2218
2219 UPDATE WIP_DISCRETE_JOBS wdj
2220 SET DATE_CLOSED = (SELECT wdct.ACTUAL_CLOSE_DATE
2221 FROM WIP_DJ_CLOSE_TEMP wdct
2222 WHERE wdct.ORGANIZATION_ID = p_organization_id
2223 AND wdj.ORGANIZATION_ID = p_organization_id
2224 AND wdj.WIP_ENTITY_ID = wdct.WIP_ENTITY_ID
2225 AND wdct.GROUP_ID = l_group_id),
2226 LAST_UPDATE_DATE = SYSDATE,
2227 last_updated_by = fnd_global.user_id,
2228 last_update_login = fnd_global.login_id,
2229 STATUS_TYPE = WIP_CONSTANTS.CLOSED
2230 WHERE ORGANIZATION_ID = p_organization_id
2231 AND WIP_ENTITY_ID IN (SELECT WIP_ENTITY_ID
2232 FROM WIP_DJ_CLOSE_TEMP
2233 WHERE ORGANIZATION_ID = p_organization_id
2234 AND GROUP_ID = l_group_id);
2235
2236
2237 UPDATE WIP_ENTITIES
2238 SET ENTITY_TYPE = --DECODE(entity_type,6,7,5,8,3),
2239 DECODE(entity_type,
2240 WIP_CONSTANTS.EAM,
2241 WIP_CONSTANTS.CLOSED_EAM,
2242 WIP_CONSTANTS.LOTBASED ,
2243 WIP_CONSTANTS.CLOSED_OSFM ,
2244 WIP_CONSTANTS.CLOSED_DISC),
2245 LAST_UPDATE_DATE = SYSDATE,
2246 last_updated_by = fnd_global.user_id,
2247 last_update_login = fnd_global.login_id
2248 WHERE ORGANIZATION_ID = p_organization_id
2249 AND WIP_ENTITY_ID IN (SELECT wdct.WIP_ENTITY_ID
2250 FROM WIP_DJ_CLOSE_TEMP wdct
2251 WHERE wdct.ORGANIZATION_ID = p_organization_id
2252 AND wdct.GROUP_ID = l_group_id);
2253
2254
2255 /*****************************************************/
2256 /* CALLING REPORTS */
2257 /*****************************************************/
2258
2259 fnd_file.get_names(l_msg,l_msg_data);
2260 fnd_file.put_line( FND_FILE.LOG,l_msg);
2261 fnd_file.put_line( FND_FILE.LOG,l_msg_data);
2262
2263 Run_Reports(
2264 x_returnstatus => l_return_status,
2265 p_group_id => l_group_id ,
2266 p_organization_id => p_organization_id,
2267 p_report_type => p_report_type,
2268 p_class_type => p_class_type ,
2269 p_from_class => p_from_class ,
2270 p_to_class => p_to_class ,
2271 p_from_job => p_from_job ,
2272 p_to_job => p_to_job ,
2273 p_status => p_status);
2274
2275 IF(l_return_status <> fnd_api.g_ret_sts_success) THEN
2276 x_warning := 1 ;
2277 END IF;
2278
2279
2280 /*****************************************************/
2281 /* END OF CALLING REPORTS */
2282 /****************************************************/
2283
2284
2285 SELECT COUNT(*)
2286 INTO l_num_close
2287 FROM WIP_DJ_CLOSE_TEMP
2288 WHERE ORGANIZATION_ID = p_organization_id
2289 AND GROUP_ID = l_group_id;
2290
2291 fnd_file.put_line( FND_FILE.LOG,'Number of jobs Closed '||to_char(l_num_close));
2292 fnd_file.put(FND_FILE.OUTPUT,to_char(l_num_close)||' ');
2293 fnd_message.set_name('WIP','WIP_NUM_CLOSED');
2294 l_msg := fnd_message.get;
2295 fnd_file.put_line(FND_FILE.OUTPUT,l_msg);
2296 fnd_file.put_line(FND_FILE.OUTPUT,'*******************');
2297
2298
2299 IF ( l_num_close > 0 ) THEN
2300 DELETE FROM WIP_DJ_CLOSE_TEMP
2301 WHERE ORGANIZATION_ID = p_organization_id
2302 AND GROUP_ID = l_group_id;
2303 END IF;
2304
2305 --
2306 -- Bug 5345660 exitPoint for normal exit.
2307 --
2308 IF fnd_log.g_current_runtime_level <= wip_constants.trace_logging THEN
2309 wip_logger.exitPoint(
2310 p_procName => 'wip_close_priv.wip_close',
2311 p_procReturnStatus => x_returnStatus,
2312 p_msg => 'procedure normal exit',
2313 x_returnStatus => l_return_status);
2314 END IF;
2315
2316 COMMIT ;
2317
2318 EXCEPTION
2319 WHEN others THEN
2320 rollback ;
2321 fnd_file.put_line( FND_FILE.LOG,'Exception has occured');
2322 x_returnStatus := FND_API.G_RET_STS_ERROR ;
2323 /* Update jobs to Failed Close status */
2324
2325 /*Bug 6908428: Update the status of eam_work_order_details to failed close and proceed workflow notification*/
2326 EAM_WorkOrderTransactions_PUB.RAISE_WORKFLOW_STATUS_PEND_CLS(
2327 p_group_id => l_group_id,
2328 p_new_status => WIP_CONSTANTS.FAIL_CLOSE,
2329 ERRBUF => l_errMsg,
2330 RETCODE => l_return_Status
2331 );
2332
2333 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2334 IF fnd_log.g_current_runtime_level <= wip_constants.trace_logging THEN
2335 wip_logger.log(p_msg => 'error during eam update workflow to fail closed' || l_errMsg,
2336 x_returnStatus => l_return_Status);
2337 END IF;
2338 RAISE FND_API.G_EXC_ERROR ;
2339 END IF;
2340
2341 EAM_WorkOrderTransactions_PUB.Update_EWOD(
2342 p_group_id => l_group_id,
2343 p_organization_id => p_organization_id,
2344 p_new_status => WIP_CONSTANTS.FAIL_CLOSE,
2345 ERRBUF => l_errMsg,
2346 RETCODE => l_return_status
2347 );
2348
2349 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2350 IF fnd_log.g_current_runtime_level <= wip_constants.trace_logging THEN
2351 wip_logger.log(p_msg => 'eam update workoder error during fail close' || l_errMsg,
2352 x_returnStatus => l_return_Status);
2353 END IF;
2354 RAISE FND_API.G_EXC_ERROR ;
2355 END IF;
2356
2357 /*Bug 6908428*/
2358
2359
2360 UPDATE WIP_DISCRETE_JOBS wdj
2361 SET LAST_UPDATE_DATE = SYSDATE,
2362 last_updated_by = fnd_global.user_id,
2363 last_update_login = fnd_global.login_id,
2364 STATUS_TYPE = WIP_CONSTANTS.FAIL_CLOSE
2365 WHERE ORGANIZATION_ID = p_organization_id
2366 AND WIP_ENTITY_ID IN (SELECT WIP_ENTITY_ID
2367 FROM WIP_DJ_CLOSE_TEMP
2368 WHERE ORGANIZATION_ID = p_organization_id
2369 AND GROUP_ID = l_group_id);
2370
2371 /* Clean up Temp Table */
2372
2373 DELETE FROM WIP_DJ_CLOSE_TEMP
2374 WHERE ORGANIZATION_ID = p_organization_id
2375 AND GROUP_ID = l_group_id;
2376
2377 --
2378 -- Bug 5345660 exitPoint for exception exit.
2379 --
2380 l_msg := 'unexpected error: ' || SQLERRM || 'SQLCODE = ' || SQLCODE;
2381 IF fnd_log.g_current_runtime_level <= wip_constants.trace_logging THEN
2382 wip_logger.exitPoint(
2383 p_procName => 'wip_close_priv.wip_close',
2384 p_procReturnStatus => x_returnStatus,
2385 p_msg => l_msg,
2386 x_returnStatus => l_return_status);
2387 END IF;
2388
2389 COMMIT ;
2390
2391 END WIP_CLOSE ;
2392
2393 /* Wrapper function which will be called by the concurrent manager */
2394
2395 procedure WIP_CLOSE_MGR
2396 (
2397 ERRBUF OUT NOCOPY VARCHAR2 ,
2398 RETCODE OUT NOCOPY VARCHAR2 ,
2399 p_organization_id IN NUMBER ,
2400 p_class_type IN VARCHAR2 ,
2401 p_from_class IN VARCHAR2 ,
2402 p_to_class IN VARCHAR2 ,
2403 p_from_job IN VARCHAR2 ,
2404 p_to_job IN VARCHAR2 ,
2405 p_from_release_date IN VARCHAR2 ,
2406 p_to_release_date IN VARCHAR2 ,
2407 p_from_start_date IN VARCHAR2 ,
2408 p_to_start_date IN VARCHAR2 ,
2409 p_from_completion_date IN VARCHAR2 ,
2410 p_to_completion_date IN VARCHAR2 ,
2411 p_status IN VARCHAR2 ,
2412 p_group_id IN NUMBER ,
2413 p_select_jobs IN NUMBER ,
2414 p_exclude_reserved_jobs IN VARCHAR2 ,
2415 p_uncompleted_jobs IN VARCHAR2,
2416 p_exclude_pending_txn_jobs IN VARCHAR2 ,
2417 p_report_type IN VARCHAR2 ,
2418 p_act_close_date IN VARCHAR2
2419 )
2420 IS
2421 l_returnstatus VARCHAR2(1) ;
2422 l_msg_count NUMBER;
2423 l_msg_data VARCHAR2(200);
2424 l_warning NUMBER;
2425 BEGIN
2426 RETCODE := 0 ; -- success
2427
2428 WIP_CLOSE
2429 (
2430 p_organization_id => p_organization_id ,
2431 p_class_type => p_class_type ,
2432 p_from_class => p_from_class ,
2433 p_to_class => p_to_class ,
2434 p_from_job => p_from_job ,
2435 p_to_job => p_to_job ,
2436 p_from_release_date => p_from_release_date ,
2437 p_to_release_date => p_to_release_date ,
2438 p_from_start_date => p_from_start_date ,
2439 p_to_start_date => p_to_start_date ,
2440 p_from_completion_date => p_from_completion_date ,
2441 p_to_completion_date => p_to_completion_date ,
2442 p_status => p_status ,
2443 p_group_id => p_group_id ,
2444 p_select_jobs => p_select_jobs ,
2445 p_exclude_reserved_jobs => p_exclude_reserved_jobs ,
2446 p_uncompleted_jobs => p_uncompleted_jobs ,
2447 p_exclude_pending_txn_jobs => p_exclude_pending_txn_jobs ,
2448 p_report_type => p_report_type ,
2449 p_act_close_date => p_act_close_date ,
2450 x_warning => l_warning ,
2451 x_returnStatus => l_returnstatus
2452 );
2453
2454 IF l_warning = 1 THEN
2455 retcode := 1 ; -- warning ;
2456 wip_utilities.get_message_stack(p_msg =>errbuf);
2457 END IF ;
2458
2459 IF(l_returnStatus <> fnd_api.g_ret_sts_success) THEN
2460 retcode := 2; -- error
2461 wip_utilities.get_message_stack(p_msg =>errbuf);
2462 END IF;
2463
2464 EXCEPTION
2465 WHEN others THEN
2466 retcode := 2; -- error
2467 errbuf := SQLERRM;
2468
2469 END WIP_CLOSE_MGR ;
2470
2471 END wip_jobclose_priv ;