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