[Home] [Help]
PACKAGE BODY: APPS.WIP_WS_EMBEDDED_ANALYTICS_PK
Source
1 PACKAGE BODY wip_ws_embedded_analytics_pk AS
2 /* $Header: wipwseab.pls 120.18 2008/05/29 23:59:37 ankohli noship $ */
3
4 /*============================================================================+
5 | Copyright (c) 2007 Oracle Corporation Redwood Shore, California, USA |
6 | All rights reserved. |
7 | Oracle Manufacturing |
8 +=============================================================================+
9 |
10 | FILE NAME : WIPWSEAB.sql
11 | DESCRIPTION :
12 | This package contains specification for all APIs related to
13 | MES First Pass Yield and Parts per Million Defects module
14 |
15 | HISTORY : created 11-DEC-07
16 | Nitikorn Tangjeerawong 11-DEC-2007 Creating Initial Version
17 |
18 *============================================================================*/
19
20 g_fpy_data_retention NUMBER := 33; -- 29 Days is for 30 days retention period. More than 29 days are buffer for date without shift.
21 g_ppmd_data_retention NUMBER := 33; -- 29 Days is for 30 days retention period. More than 29 days are buffer for date without shift.
22
23 FUNCTION set_calc_param(p_execution_date IN DATE,
24 p_cutoff_date IN DATE,
25 p_org_id IN NUMBER)
26 RETURN wip_logger.param_tbl_t IS
27 l_params wip_logger.param_tbl_t;
28 BEGIN
29 l_params (1).paramName := 'p_execution_date';
30 l_params (1).paramValue := p_execution_date;
31 l_params (2).paramName := 'p_cutoff_date';
32 l_params (2).paramValue := p_cutoff_date;
33 l_params (3).paramName := 'p_org_id';
34 l_params (3).paramValue := p_org_id;
35 RETURN l_params;
36 END set_calc_param;
37
38 PROCEDURE begin_log(p_params wip_logger.param_tbl_t,
39 p_proc_name VARCHAR2,
40 p_return_status OUT NOCOPY VARCHAR2
41 ) IS
42 BEGIN
43 IF (g_logLevel <= wip_constants.trace_logging) THEN
44 wip_logger.entryPoint(p_procName => p_proc_name,
45 p_params => p_params,
46 x_returnStatus => p_return_status);
47
48 IF (p_return_status <> fnd_api.g_ret_sts_success) THEN
49 RAISE fnd_api.g_exc_unexpected_error;
50 END IF;
51 END IF;
52
53 IF (g_logLevel <= wip_constants.trace_logging) then
54 wip_logger.exitPoint(p_procName => p_proc_name,
55 p_procReturnStatus => p_return_status,
56 p_msg => 'procedure success.',
57 x_returnStatus => p_return_Status);
58 END IF;
59 END begin_log;
60
61 PROCEDURE exception_log(p_proc_name VARCHAR2,
62 p_return_status OUT NOCOPY VARCHAR2) IS
63 l_return_status varchar2(1);
64 BEGIN
65 wip_ws_util.trace_log('Error in ' || p_proc_name);
66 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
67 IF (g_logLevel <= wip_constants.trace_logging) then
68 wip_logger.exitPoint(p_procName => p_proc_name,
69 p_procReturnStatus => l_return_status,
70 p_msg => 'unexpected error: ' || SQLERRM,
71 x_returnStatus => l_return_Status);
72 END IF;
73 END exception_log;
74
75 PROCEDURE populate_fpy_raw_data(
76 p_execution_date DATE,
77 p_cutoff_date DATE,
78 p_org_id IN NUMBER,
79 x_return_status OUT NOCOPY VARCHAR2) IS
80
81 l_return_status varchar2(1);
82 l_params wip_logger.param_tbl_t;
83
84 BEGIN
85
86 IF (g_logLevel <= wip_constants.trace_logging) THEN
87
88 l_params(1).paramName := 'p_execution_date';
89 l_params(1).paramValue := p_execution_date;
90 l_params(2).paramName := 'p_cutoff_date';
91 l_params(2).paramValue := p_cutoff_date;
92 l_params(3).paramName := 'p_org_id';
93 l_params(3).paramValue := p_org_id;
94
95 wip_logger.entryPoint(p_procName => 'wip_ws_embedded_analytics_pk.populate_fpy_raw_data',
96 p_params => l_params,
97 x_returnStatus => l_return_status);
98
99 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
100 RAISE fnd_api.g_exc_unexpected_error;
101 END IF;
102 END IF;
103
104 x_return_status := FND_API.G_RET_STS_SUCCESS;
105
106 /* Original Query before passing Department when calculating shift.
107 INSERT INTO wip_ws_fpy
108 (
109 ORGANIZATION_ID,
110 DEPARTMENT_ID,
111 WIP_ENTITY_ID,
112 OPERATION_SEQ_NUM,
113 INVENTORY_ITEM_ID,
114 SHIFT_NUM,
115 SHIFT_DATE,
116 QUANTITY_REJECTED,
117 QUANTITY_SCRAPPED,
118 QUANTITY_COMPLETED,
119 REQUEST_ID,
120 LAST_UPDATE_DATE,
121 LAST_UPDATED_BY,
122 CREATION_DATE,
123 CREATED_BY,
124 LAST_UPDATE_LOGIN,
125 PROGRAM_ID,
126 PROGRAM_APPLICATION_ID,
127 PROGRAM_UPDATE_DATE,
128 QUANTITY_FIRST_PASS
129 )
130 SELECT
131 wdj.organization_id,
132 completed_info.department_id,
133 completed_info.wip_entity_id as wip_entity_id,
134 completed_info.operation_seq_num as operation_seq_num,
135 wdj.primary_item_id,
136 completed_info.shift_num as shift_num,
137 completed_info.shift_start_date as shift_date,
138 0 as quantity_rejected,
139 0 as quantity_scraped,
140 completed_info.quantity_compelted,
141 g_request_id as REQUEST_ID,
142 p_execution_date as LAST_UPDATE_DATE,
143 g_user_id as LAST_UPDATED_BY,
144 p_execution_date as CREATION_DATE,
145 g_user_id as CREATED_BY,
146 g_login_id as LAST_UPDATE_LOGIN,
147 g_prog_id as PROGRAM_ID,
148 g_prog_appid as PROGRAM_APPLICATION_ID,
149 p_execution_date as PROGRAM_UPDATE_DATE,
150 0 as QUANTITY_FIRST_PASS
151 FROM
152 (
153 SELECT
154 wop.department_id,
155 wmt.shift_start_date,
156 wmt.shift_num,
157 wop.wip_entity_id,
158 wop.operation_seq_num,
159 NVL(SUM(wmt.primary_quantity *
160 DECODE(SIGN(wmt.to_operation_seq_num -wmt.fm_operation_seq_num),
161 0,DECODE(SIGN(wmt.fm_intraoperation_step_type -WIP_CONSTANTS.RUN),
162 0,DECODE(SIGN(wmt.to_intraoperation_step_type -WIP_CONSTANTS.RUN),1,1,-1),
163 -1,DECODE(SIGN(wmt.to_intraoperation_step_type -WIP_CONSTANTS.RUN),1,1,-1),
164 1,-1),
165 1, 1,
166 -1,-1)
167 ),0) quantity_compelted
168 FROM
169 (
170 SELECT
171 transaction_date+ mod(shift_info,1)*1000 shift_start_date,
172 abs(mod(trunc(shift_info),100)) as shift_num,
173 wip_entity_id,
174 primary_quantity,
175 to_operation_seq_num,
176 to_intraoperation_step_type,
177 fm_operation_seq_num,
178 fm_intraoperation_step_type
179 FROM
180 (
181 SELECT
182 wip_ws_embedded_analytics_pk.get_shift_info(wmt.organization_id, wmt.transaction_date) as shift_info,
183 wmt.*
184 FROM
185 wip_move_transactions wmt
186 WHERE
187 wmt.transaction_date > p_cutoff_date
188 AND wmt.organization_id = p_org_id
189 )
190 ) wmt,
191 wip_operations wop
192 WHERE
193 wop.organization_id = p_org_id
194 AND wop.wip_entity_id = wmt.wip_entity_id
195 AND ((wop.operation_seq_num >= wmt.fm_operation_seq_num + DECODE(SIGN(wmt.fm_intraoperation_step_type - WIP_CONSTANTS.RUN),1,1,0)
196 AND (wop.operation_seq_num < wmt.to_operation_seq_num + DECODE(SIGN(wmt.to_intraoperation_step_type - WIP_CONSTANTS.RUN),1,1,0))
197 AND (wmt.to_operation_seq_num > wmt.fm_operation_seq_num
198 OR (wmt.to_operation_seq_num = wmt.fm_operation_seq_num
199 AND wmt.fm_intraoperation_step_type <= WIP_CONSTANTS.RUN
200 AND wmt.to_intraoperation_step_type > WIP_CONSTANTS.RUN))
201 AND (wop.count_point_type < WIP_CONSTANTS.NO_MANUAL
202 OR wop.operation_seq_num = wmt.fm_operation_seq_num
203 OR (wop.operation_seq_num = wmt.to_operation_seq_num
204 AND wmt.to_intraoperation_step_type > WIP_CONSTANTS.RUN)))
205 OR
206 (wop.operation_seq_num < wmt.fm_operation_seq_num + DECODE(SIGN(wmt.fm_intraoperation_step_type - WIP_CONSTANTS.RUN),1,1,0)
207 AND (wop.operation_seq_num >= wmt.to_operation_seq_num + DECODE(SIGN(wmt.to_intraoperation_step_type - WIP_CONSTANTS.RUN),1,1,0))
208 AND (wmt.fm_operation_seq_num > wmt.to_operation_seq_num
209 OR (wmt.fm_operation_seq_num = wmt.to_operation_seq_num
210 AND wmt.to_intraoperation_step_type <= WIP_CONSTANTS.RUN
211 AND wmt.fm_intraoperation_step_type > WIP_CONSTANTS.RUN))
212 AND (wop.count_point_type < WIP_CONSTANTS.NO_MANUAL
213 OR (wop.operation_seq_num = wmt.to_operation_seq_num
214 AND wop.count_point_type < WIP_CONSTANTS.NO_MANUAL )
215 OR (wop.operation_seq_num = wmt.fm_operation_seq_num
216 AND wmt.fm_intraoperation_step_type > WIP_CONSTANTS.RUN))))
217 GROUP BY wop.department_id, wmt.shift_start_date, wmt.shift_num, wop.wip_entity_id, wop.operation_seq_num
218
219 ) completed_info,
220 WIP_DISCRETE_JOBS wdj
221
222 WHERE wdj.wip_entity_id = completed_info.wip_entity_id
223 AND wdj.organization_id = p_org_id;
224
225 */
226
227 INSERT INTO wip_ws_fpy
228 (
229 ORGANIZATION_ID,
230 DEPARTMENT_ID,
231 WIP_ENTITY_ID,
232 OPERATION_SEQ_NUM,
233 INVENTORY_ITEM_ID,
234 SHIFT_NUM,
235 SHIFT_DATE,
236 QUANTITY_REJECTED,
237 QUANTITY_SCRAPPED,
238 QUANTITY_COMPLETED,
239 REQUEST_ID,
240 LAST_UPDATE_DATE,
241 LAST_UPDATED_BY,
242 CREATION_DATE,
243 CREATED_BY,
244 LAST_UPDATE_LOGIN,
245 PROGRAM_ID,
246 PROGRAM_APPLICATION_ID,
247 PROGRAM_UPDATE_DATE,
248 QUANTITY_FIRST_PASS
249 )
250 SELECT
251 wdj.organization_id,
252 completed_info.department_id,
253 completed_info.wip_entity_id,
254 completed_info.operation_seq_num,
255 wdj.primary_item_id,
256 completed_info.shift_num,
257 completed_info.shift_start_date,
258 0 as quantity_rejected,
259 0 as quantity_scraped,
260 completed_info.quantity_compelted,
261 g_request_id as REQUEST_ID,
262 p_execution_date as LAST_UPDATE_DATE,
263 g_user_id as LAST_UPDATED_BY,
264 p_execution_date as CREATION_DATE,
265 g_user_id as CREATED_BY,
266 g_login_id as LAST_UPDATE_LOGIN,
267 g_prog_id as PROGRAM_ID,
268 g_prog_appid as PROGRAM_APPLICATION_ID,
269 p_execution_date as PROGRAM_UPDATE_DATE,
270 0 as QUANTITY_FIRST_PASS
271 FROM
272 (
273 SELECT
274 post_cal.department_id,
275 post_cal.shift_start_date,
276 post_cal.shift_num,
277 post_cal.wip_entity_id,
278 post_cal.operation_seq_num,
279 NVL(SUM(post_cal.primary_quantity *
280 DECODE(SIGN(post_cal.to_operation_seq_num -post_cal.fm_operation_seq_num),
281 0,DECODE(SIGN(post_cal.fm_intraoperation_step_type -WIP_CONSTANTS.RUN),
282 0,DECODE(SIGN(post_cal.to_intraoperation_step_type -WIP_CONSTANTS.RUN),1,1,-1),
283 -1,DECODE(SIGN(post_cal.to_intraoperation_step_type -WIP_CONSTANTS.RUN),1,1,-1),
284 1,-1),
285 1, 1,
286 -1,-1)
287 ),0) quantity_compelted
288 FROM
289 (
290 SELECT
291 pre_cal.department_id,
292 pre_cal.primary_quantity,
293 pre_cal.wip_entity_id,
294 pre_cal.operation_seq_num,
295 (case when (pre_cal.shift_info is not null) then pre_cal.transaction_date+ mod(pre_cal.shift_info,1)*1000 else trunc(pre_cal.transaction_date) end )as shift_start_date,
296 (case when (pre_cal.shift_info is not null) then abs(mod(trunc(pre_cal.shift_info),100)) else -1 end)as shift_num,
297 pre_cal.fm_operation_seq_num,
298 pre_cal.to_operation_seq_num,
299 pre_cal.fm_intraoperation_step_type,
300 pre_cal.to_intraoperation_step_type
301 FROM
302 (
303 SELECT
304 wop.department_id,
305 wmt.wip_entity_id,
306 wmt.transaction_id,
307 wop.operation_seq_num,
308 wmt.fm_operation_seq_num,
309 wmt.to_operation_seq_num,
310 wmt.fm_intraoperation_step_type,
311 wmt.to_intraoperation_step_type,
312 wmt.primary_quantity,
313 wmt.transaction_date,
314 wip_ws_embedded_analytics_pk.get_shift_info(wmt.organization_id,wop.department_id, wmt.transaction_date) as shift_info
315 FROM
316 wip_move_transactions wmt,
317 wip_operations wop
318 WHERE
319 wmt.transaction_date >= p_cutoff_date
320 AND wmt.organization_id = p_org_id
321 AND wop.organization_id = p_org_id
322 AND wop.wip_entity_id = wmt.wip_entity_id
323 AND ((wop.operation_seq_num >= wmt.fm_operation_seq_num + DECODE(SIGN(wmt.fm_intraoperation_step_type - WIP_CONSTANTS.RUN),1,1,0)
324 AND (wop.operation_seq_num < wmt.to_operation_seq_num + DECODE(SIGN(wmt.to_intraoperation_step_type - WIP_CONSTANTS.RUN),1,1,0))
325 AND (wmt.to_operation_seq_num > wmt.fm_operation_seq_num
326 OR (wmt.to_operation_seq_num = wmt.fm_operation_seq_num
327 AND wmt.fm_intraoperation_step_type <= WIP_CONSTANTS.RUN
328 AND wmt.to_intraoperation_step_type > WIP_CONSTANTS.RUN))
329 AND (wop.count_point_type < WIP_CONSTANTS.NO_MANUAL
330 OR wop.operation_seq_num = wmt.fm_operation_seq_num
331 OR (wop.operation_seq_num = wmt.to_operation_seq_num
332 AND wmt.to_intraoperation_step_type > WIP_CONSTANTS.RUN)))
333 OR
334 (wop.operation_seq_num < wmt.fm_operation_seq_num + DECODE(SIGN(wmt.fm_intraoperation_step_type - WIP_CONSTANTS.RUN),1,1,0)
335 AND (wop.operation_seq_num >= wmt.to_operation_seq_num + DECODE(SIGN(wmt.to_intraoperation_step_type - WIP_CONSTANTS.RUN),1,1,0))
336 AND (wmt.fm_operation_seq_num > wmt.to_operation_seq_num
337 OR (wmt.fm_operation_seq_num = wmt.to_operation_seq_num
338 AND wmt.to_intraoperation_step_type <= WIP_CONSTANTS.RUN
339 AND wmt.fm_intraoperation_step_type > WIP_CONSTANTS.RUN))
340 AND (wop.count_point_type < WIP_CONSTANTS.NO_MANUAL
341 OR (wop.operation_seq_num = wmt.to_operation_seq_num
342 AND wop.count_point_type < WIP_CONSTANTS.NO_MANUAL )
343 OR (wop.operation_seq_num = wmt.fm_operation_seq_num
344 AND wmt.fm_intraoperation_step_type > WIP_CONSTANTS.RUN))))
345 ) pre_cal
346 ) post_cal
347 GROUP BY post_cal.department_id, post_cal.shift_start_date, post_cal.shift_num, post_cal.wip_entity_id, post_cal.operation_seq_num
348 ) completed_info,
349 WIP_DISCRETE_JOBS wdj
350
351 WHERE wdj.wip_entity_id = completed_info.wip_entity_id
352 AND wdj.organization_id = p_org_id;
353
354 wip_ws_util.trace_log('Finish Inserting QUANTITY_COMPLETED');
355 --------------------------- Update QUANTITY_SCRAPPED ------------------------------------
356 UPDATE
357 wip_ws_fpy fpy
358 SET
359 QUANTITY_SCRAPPED =
360 nvl((SELECT
361 NVL(SUM(DECODE(wop.operation_seq_num,wmt.to_operation_seq_num,DECODE(wmt.to_intraoperation_step_type,WIP_CONSTANTS.SCRAP, wmt.primary_quantity,0),0)
362 - DECODE(wop.operation_seq_num, wmt.fm_operation_seq_num,DECODE(wmt.fm_intraoperation_step_type,WIP_CONSTANTS.SCRAP,wmt.primary_quantity,0),0)),0) as quantity_scrap
363 FROM
364 (
365 SELECT
366 (case when (shift_info is not null) then transaction_date+ mod(shift_info,1)*1000 else trunc(transaction_date) end )as shift_start_date,
367 (case when (shift_info is not null) then abs(mod(trunc(shift_info),100)) else -1 end)as shift_num,
368 wip_entity_id,
369 primary_quantity,
370 to_operation_seq_num,
371 to_intraoperation_step_type,
372 fm_operation_seq_num,
373 fm_intraoperation_step_type
374 FROM
375 (
376 SELECT
377 wip_ws_embedded_analytics_pk.get_shift_info(wmt.organization_id
378 ,(case when wmt.to_intraoperation_step_type = WIP_CONSTANTS.SCRAP then wmt.to_department_id else wmt.fm_department_id end)
379 ,wmt.transaction_date) as shift_info,
380 transaction_date,
381 wip_entity_id,
382 primary_quantity,
383 to_operation_seq_num,
384 to_intraoperation_step_type,
385 fm_operation_seq_num,
386 fm_intraoperation_step_type
387 FROM
388 wip_move_transactions wmt
389 WHERE
390 transaction_date >= p_cutoff_date
391 AND wmt.organization_id = p_org_id
392 AND (wmt.fm_intraoperation_step_type = WIP_CONSTANTS.SCRAP
393 OR wmt.to_intraoperation_step_type = WIP_CONSTANTS.SCRAP)
394 ) wmt_raw_shift_info
395 ) wmt,
396 wip_operations wop
397
398 WHERE
399 wop.wip_entity_id = wmt.wip_entity_id
400 AND wop.organization_id = p_org_id
401 AND ((wmt.fm_intraoperation_step_type = WIP_CONSTANTS.SCRAP AND wmt.fm_operation_seq_num = wop.operation_seq_num)
402 OR (wmt.to_intraoperation_step_type = WIP_CONSTANTS.SCRAP AND wmt.to_operation_seq_num = wop.operation_seq_num))
403
404 AND fpy.wip_entity_id = wop.wip_entity_id
405 AND fpy.operation_seq_num = wop.operation_seq_num
406 AND fpy.shift_num = wmt.shift_num
407 AND fpy.shift_date = wmt.shift_start_date
408 AND fpy.organization_id = p_org_id
409
410 GROUP BY wmt.shift_start_date, wmt.shift_num, wop.wip_entity_id, wop.operation_seq_num),0);
411
412 wip_ws_util.trace_log('Finish Updating QUANTITY_SCRAPPED');
413
414 /* This QUERY works too, if the above SQL turn bad in performance try this one
415 SELECT
416 NVL(SUM(DECODE(operation_seq_num,to_operation_seq_num,DECODE(to_intraoperation_step_type,WIP_CONSTANTS.SCRAP, primary_quantity,0),0)
417 - DECODE(operation_seq_num, fm_operation_seq_num,DECODE(fm_intraoperation_step_type,WIP_CONSTANTS.SCRAP,primary_quantity,0),0)),0) as quantity_scrap
418 FROM (
419 SELECT
420 transaction_date,primary_quantity,to_intraoperation_step_type,fm_intraoperation_step_type,fm_operation_seq_num,to_operation_seq_num,
421 transaction_date+ mod(shift_info,1)*1000 as shift_start_date,
422 abs(mod(trunc(shift_info),100)) as shift_num,
423 wip_entity_id,operation_seq_num
424 FROM (
425 SELECT
426 wip_ws_embedded_analytics_pk.get_shift_info(wmt.organization_id,(
427 case when (wmt.TO_DEPARTMENT_ID =WIP_CONSTANTS.SCRAP AND wop.operation_seq_num = wmt.fm_operation_seq_num) then wmt.to_department_id else wmt.fm_department_id end),wmt.transaction_date) as shift_info,
428 wop.wip_entity_id, wop.operation_seq_num,
429 wmt.transaction_date,wmt.primary_quantity,wmt.to_intraoperation_step_type,wmt.fm_intraoperation_step_type,fm_operation_seq_num,to_operation_seq_num
430 FROM
431 wip_move_transactions wmt,
432 wip_operations wop
433 WHERE
434 wop.wip_entity_id = wmt.wip_entity_id
435 AND wmt.organization_id = p_org_id
436 AND wop.organization_id = p_org_id
437 AND transaction_date >= p_cutoff_date
438 AND ((wmt.fm_intraoperation_step_type = 5 AND wmt.fm_operation_seq_num = wop.operation_seq_num)
439 OR (wmt.to_intraoperation_step_type = 5 AND wmt.to_operation_seq_num = wop.operation_seq_num))
440 )pre_cal
441 ) post_cal
442 GROUP BY shift_start_date, shift_num, wip_entity_id, operation_seq_num */
443
444
445
446
447 ------------------------------------ update QUANTITY_REJECTED ------------------------------------
448
449 UPDATE
450 wip_ws_fpy fpy
451 SET
452 QUANTITY_REJECTED =
453 -- Formula = Sum(IN) - Sum(OUT BW)
454 -- OUT BW = Move backword from REJECT except moving to the 'TOMOVE' within the same opeation
455
456 nvl((SELECT
457 NVL(SUM(DECODE(wop.operation_seq_num,wmt.to_operation_seq_num,
458 DECODE(wmt.to_intraoperation_step_type,WIP_CONSTANTS.REJECT, wmt.primary_quantity,0),0)
459 ),0)
460 - NVL(SUM(DECODE(wop.operation_seq_num,fm_operation_seq_num,
461 DECODE(wmt.fm_intraoperation_step_type,WIP_CONSTANTS.REJECT,
462 DECODE(SIGN(wmt.fm_operation_seq_num-wmt.to_operation_seq_num),
463 1,wmt.primary_quantity, -- Out Backward different operation seq
464 0,DECODE(wmt.to_intraoperation_step_type,WIP_CONSTANTS.TOMOVE,0,wmt.primary_quantity),0) -- Out Backward within same operation seq
465 ),0)
466 ),0) as quantity_reject
467 FROM
468 (
469 SELECT
470 (case when (shift_info is not null) then transaction_date+ mod(shift_info,1)*1000 else trunc(transaction_date) end )as shift_start_date,
471 (case when (shift_info is not null) then abs(mod(trunc(shift_info),100)) else -1 end)as shift_num,
472 wip_entity_id,
473 primary_quantity,
474 to_operation_seq_num,
475 to_intraoperation_step_type,
476 fm_operation_seq_num,
477 fm_intraoperation_step_type
478 FROM
479 (
480 SELECT
481 wip_ws_embedded_analytics_pk.get_shift_info(wmt.organization_id
482 ,(case when wmt.to_intraoperation_step_type = WIP_CONSTANTS.REJECT then wmt.to_department_id else wmt.fm_department_id end)
483 ,wmt.transaction_date) as shift_info ,
484 wmt.*
485 FROM
486 wip_move_transactions wmt
487 WHERE
488 transaction_date > p_cutoff_date
489 AND wmt.organization_id = p_org_id
490 AND (wmt.fm_intraoperation_step_type = WIP_CONSTANTS.REJECT
491 OR wmt.to_intraoperation_step_type = WIP_CONSTANTS.REJECT)
492 ) wmt_raw_shift_info
493 ) wmt,
494 wip_operations wop
495
496 WHERE
497 wop.wip_entity_id = wmt.wip_entity_id
498 AND wop.organization_id = p_org_id
499 AND ((wmt.fm_intraoperation_step_type = WIP_CONSTANTS.REJECT AND wmt.fm_operation_seq_num = wop.operation_seq_num )
500 OR (wmt.to_intraoperation_step_type = WIP_CONSTANTS.REJECT AND wmt.to_operation_seq_num = wop.operation_seq_num))
501 AND fpy.wip_entity_id = wop.wip_entity_id
502 AND fpy.operation_seq_num = wop.operation_seq_num
503 AND fpy.shift_num = wmt.shift_num
504 AND fpy.shift_date = wmt.shift_start_date
505 AND fpy.organization_id = p_org_id
506
507 GROUP BY wmt.shift_start_date, wmt.shift_num, wop.wip_entity_id, wop.operation_seq_num),0);
508
509 /*
510 Wrong SQL
511 nvl((SELECT
512 NVL(SUM(DECODE(wop.operation_seq_num,post_cal.to_operation_seq_num,
513 DECODE(post_cal.to_intraoperation_step_type,WIP_CONSTANTS.REJECT, post_cal.primary_quantity,0),0)
514 ),0)
515 - NVL(SUM(DECODE(wop.operation_seq_num,fm_operation_seq_num,
516 DECODE(post_cal.fm_intraoperation_step_type,WIP_CONSTANTS.REJECT,
517 DECODE(SIGN(post_cal.fm_operation_seq_num-post_cal.to_operation_seq_num),
518 1,post_cal.primary_quantity, -- Out Backward different operation seq
519 0,DECODE(post_cal.to_intraoperation_step_type,WIP_CONSTANTS.TOMOVE,0,post_cal.primary_quantity),0) -- Out Backward within same operation seq
520 ),0)
521 ),0) as quantity_reject
522
523 FROM
524 (
525 SELECT
526 pre_cal.department_id,
527 pre_cal.primary_quantity,
528 pre_cal.wip_entity_id,
529 pre_cal.operation_seq_num,
530 pre_cal.transaction_date+ mod(pre_cal.shift_info,1)*1000 as shift_start_date,
531 abs(mod(trunc(pre_cal.shift_info),100)) as shift_num,
532 pre_cal.fm_operation_seq_num,
533 pre_cal.to_operation_seq_num,
534 pre_cal.fm_intraoperation_step_type,
535 pre_cal.to_intraoperation_step_type
536 FROM
537 (
538 SELECT
539 wop.department_id,
540 wmt.wip_entity_id,
541 wmt.transaction_id,
542 wop.operation_seq_num,
543 wmt.fm_operation_seq_num,
544 wmt.to_operation_seq_num,
545 wmt.fm_intraoperation_step_type,
546 wmt.to_intraoperation_step_type,
547 wmt.primary_quantity,
548 wmt.transaction_date,
549 wip_ws_embedded_analytics_pk.get_shift_info(wmt.organization_id,null, wmt.transaction_date) as shift_info
550 FROM
551 wip_move_transactions wmt,
552 wip_operations wop
553 WHERE
554 wmt.transaction_date >= p_cutoff_date
555 AND wmt.organization_id = p_org_id
556 AND wop.organization_id = p_org_id
557 AND wop.wip_entity_id = wmt.wip_entity_id
558 AND (wmt.fm_intraoperation_step_type = WIP_CONSTANTS.REJECT
559 OR wmt.to_intraoperation_step_type = WIP_CONSTANTS.REJECT)
560 AND ((wop.operation_seq_num >= wmt.fm_operation_seq_num + DECODE(SIGN(wmt.fm_intraoperation_step_type - WIP_CONSTANTS.RUN),1,1,0)
561 AND (wop.operation_seq_num < wmt.to_operation_seq_num + DECODE(SIGN(wmt.to_intraoperation_step_type - WIP_CONSTANTS.RUN),1,1,0))
562 AND (wmt.to_operation_seq_num > wmt.fm_operation_seq_num
563 OR (wmt.to_operation_seq_num = wmt.fm_operation_seq_num
564 AND wmt.fm_intraoperation_step_type <= WIP_CONSTANTS.RUN
565 AND wmt.to_intraoperation_step_type > WIP_CONSTANTS.RUN))
566 AND (wop.count_point_type < WIP_CONSTANTS.NO_MANUAL
567 OR wop.operation_seq_num = wmt.fm_operation_seq_num
568 OR (wop.operation_seq_num = wmt.to_operation_seq_num
569 AND wmt.to_intraoperation_step_type > WIP_CONSTANTS.RUN)))
570 OR
571 (wop.operation_seq_num < wmt.fm_operation_seq_num + DECODE(SIGN(wmt.fm_intraoperation_step_type - WIP_CONSTANTS.RUN),1,1,0)
572 AND (wop.operation_seq_num >= wmt.to_operation_seq_num + DECODE(SIGN(wmt.to_intraoperation_step_type - WIP_CONSTANTS.RUN),1,1,0))
573 AND (wmt.fm_operation_seq_num > wmt.to_operation_seq_num
574 OR (wmt.fm_operation_seq_num = wmt.to_operation_seq_num
575 AND wmt.to_intraoperation_step_type <= WIP_CONSTANTS.RUN
576 AND wmt.fm_intraoperation_step_type > WIP_CONSTANTS.RUN))
577 AND (wop.count_point_type < WIP_CONSTANTS.NO_MANUAL
578 OR (wop.operation_seq_num = wmt.to_operation_seq_num
579 AND wop.count_point_type < WIP_CONSTANTS.NO_MANUAL )
580 OR (wop.operation_seq_num = wmt.fm_operation_seq_num
581 AND wmt.fm_intraoperation_step_type > WIP_CONSTANTS.RUN))))
582 ) pre_cal
583 ) post_cal,
584 wip_operations wop
585 WHERE
586 wop.wip_entity_id = post_cal.wip_entity_id
587 AND wop.organization_id = p_org_id
588 AND ((post_cal.fm_intraoperation_step_type = WIP_CONSTANTS.REJECT AND post_cal.fm_operation_seq_num = wop.operation_seq_num )
589 OR (post_cal.to_intraoperation_step_type = WIP_CONSTANTS.REJECT AND post_cal.to_operation_seq_num = wop.operation_seq_num))
590
591 AND fpy.wip_entity_id = wop.wip_entity_id
592 AND fpy.operation_seq_num = wop.operation_seq_num
593 AND fpy.shift_num = post_cal.shift_num
594 AND fpy.shift_date = post_cal.shift_start_date
595 AND fpy.organization_id = p_org_id
596 GROUP BY post_cal.shift_start_date, post_cal.shift_num, wop.wip_entity_id, wop.operation_seq_num),0);
597 */
598 wip_ws_util.trace_log('Finish Updating QUANTITY_REJECTED');
599 ------------------------------------ update QUANTITY_FIRST_PASS ------------------------------------
600 /*
601 UPDATE
602 wip_ws_fpy fpy
603 SET
604 QUANTITY_FIRST_PASS = QUANTITY_COMPLETED - QUANTITY_REJECTED - QUANTITY_SCRAPPED
605 WHERE
606 LAST_UPDATE_DATE = p_execution_date
607 AND organization_id = p_org_id;
608
609 wip_ws_util.trace_log('Finish Updating QUANTITY_FIRST_PASS');
610 */
611 -------------------------------------- end of population logic -------------------------------------------------------
612
613 IF (g_logLevel <= wip_constants.trace_logging) then
614 wip_logger.exitPoint(p_procName => 'wip_ws_embedded_analytics_pk.populate_fpy_raw_data',
615 p_procReturnStatus => l_return_status,
616 p_msg => 'procedure success.',
617 x_returnStatus => l_return_Status);
618 END IF;
619
620 EXCEPTION
621 WHEN others THEN
622 wip_ws_util.trace_log('Error in populate_fpy_raw_data');
623 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
624 IF (g_logLevel <= wip_constants.trace_logging) then
625 wip_logger.exitPoint(p_procName => 'wip_ws_embedded_analytics_pk.populate_fpy_raw_data',
626 p_procReturnStatus => l_return_status,
627 p_msg => 'unexpected error: ' || SQLERRM,
628 x_returnStatus => l_return_Status);
629 END IF;
630 END populate_fpy_raw_data;
631
632
633
634 --============================================ PER JOBOP ========
635
636
637 PROCEDURE calc_fpy_per_jobop_day_shift(
638 p_execution_date DATE,
639 p_cutoff_date DATE,
640 p_org_id NUMBER,
641 x_return_status OUT NOCOPY VARCHAR2) IS
642
643 l_return_status VARCHAR2(1);
644 l_params wip_logger.param_tbl_t;
645 l_proc_name VARCHAR2(60) :='wip_ws_embedded_analytics_pk.calc_fpy_per_jobop_day_shift';
646 BEGIN
647
648 begin_log (p_params => set_calc_param(p_execution_date => p_execution_date,p_cutoff_date => p_cutoff_date,p_org_id => p_org_id),
649 p_proc_name => l_proc_name,
650 p_return_status => l_return_status);
651
652 UPDATE wip_ws_fpy wwf
653 SET
654 SCRAP_PERCENT = (case when (wwf.QUANTITY_COMPLETED = 0 OR (wwf.QUANTITY_COMPLETED-wwf.QUANTITY_SCRAPPED-wwf.QUANTITY_REJECTED)/wwf.QUANTITY_COMPLETED < 0) then 0
655 else round(100*(wwf.QUANTITY_SCRAPPED/wwf.QUANTITY_COMPLETED),2) end),
656 REJECT_PERCENT = (case when (wwf.QUANTITY_COMPLETED = 0 OR (wwf.QUANTITY_COMPLETED-wwf.QUANTITY_SCRAPPED-wwf.QUANTITY_REJECTED)/wwf.QUANTITY_COMPLETED < 0) then 0
657 else round(100*(wwf.QUANTITY_REJECTED/wwf.QUANTITY_COMPLETED),2) end),
658 FPY_PERCENT = (case when (wwf.QUANTITY_COMPLETED = 0 OR (wwf.QUANTITY_COMPLETED-wwf.QUANTITY_SCRAPPED-wwf.QUANTITY_REJECTED)/wwf.QUANTITY_COMPLETED < 0) then 0
659 else round(100*(wwf.QUANTITY_COMPLETED-wwf.QUANTITY_SCRAPPED-wwf.QUANTITY_REJECTED)/wwf.QUANTITY_COMPLETED,2) end)
660 WHERE
661 LAST_UPDATE_DATE = p_execution_date
662 AND wwf.organization_id = p_org_id
663 AND wwf.SHIFT_NUM IS NOT NULL AND wwf.SHIFT_DATE IS NOT NULL
664 AND wwf.DEPARTMENT_ID IS NOT NULL AND wwf.INVENTORY_ITEM_ID IS NOT NULL
665 AND wwf.WIP_ENTITY_ID IS NOT NULL AND wwf.OPERATION_SEQ_NUM IS NOT NULL;
666
667 x_return_status := FND_API.G_RET_STS_SUCCESS;
668
669 EXCEPTION
670 WHEN others THEN
671 exception_log(l_proc_name,x_return_status);
672 END calc_fpy_per_jobop_day_shift;
673
674 PROCEDURE calc_fpy_per_jobop_day(
675 p_execution_date DATE,
676 p_cutoff_date DATE,
677 p_org_id NUMBER,
678 x_return_status OUT NOCOPY VARCHAR2) IS
679
680 l_return_status VARCHAR2(1);
681 l_proc_name VARCHAR2(60) :='wip_ws_embedded_analytics_pk.calc_fpy_per_jobop_day';
682 BEGIN
683
684 begin_log (p_params => set_calc_param(p_execution_date => p_execution_date,p_cutoff_date => p_cutoff_date,p_org_id => p_org_id),
685 p_proc_name => l_proc_name,
686 p_return_status => l_return_status);
687
688 INSERT INTO wip_ws_fpy (
689 ORGANIZATION_ID, DEPARTMENT_ID, INVENTORY_ITEM_ID,
690 WIP_ENTITY_ID, OPERATION_SEQ_NUM,
691 SHIFT_NUM, SHIFT_DATE,
692 QUANTITY_REJECTED, QUANTITY_SCRAPPED, QUANTITY_COMPLETED,
693 SCRAP_PERCENT, REJECT_PERCENT, FPY_PERCENT,
694 REQUEST_ID, PROGRAM_ID,
695 LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
696 CREATION_DATE, CREATED_BY,
697 PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE
698 )
699 SELECT
700 p_org_id AS ORGANIZATION_ID, pre_cal.DEPARTMENT_ID, pre_cal.INVENTORY_ITEM_ID,
701 pre_cal.WIP_ENTITY_ID, pre_cal.OPERATION_SEQ_NUM,
702 null as SHIFT_NUM, pre_cal.SHIFT_DATE,
703 pre_cal.QUANTITY_REJECTED AS QUANTITY_REJECTED, pre_cal.QUANTITY_SCRAPPED AS QUANTITY_SCRAPPED, pre_cal.QUANTITY_COMPLETED AS QUANTITY_COMPLETED,
704 (case when (pre_cal.FPY_PERCENT < 0) then 0 else pre_cal.SCRAP_PERCENT end) as SCRAP_PERCENT,
705 (case when (pre_cal.FPY_PERCENT < 0) then 0 else pre_cal.REJECT_PERCENT end) as REJECT_PERCENT,
706 (case when (pre_cal.FPY_PERCENT < 0) then 0 else pre_cal.FPY_PERCENT end) as FPY_PERCENT,
707 g_request_id as REQUEST_ID, g_prog_id as PROGRAM_ID,
708 p_execution_date as LAST_UPDATE_DATE, g_user_id as LAST_UPDATED_BY, g_login_id as LAST_UPDATE_LOGIN,
709 p_execution_date as CREATION_DATE, g_user_id as CREATED_BY,
710 g_prog_appid as PROGRAM_APPLICATION_ID, p_execution_date as PROGRAM_UPDATE_DATE
711 FROM
712 ( SELECT wwf.WIP_ENTITY_ID,wwf.OPERATION_SEQ_NUM,Trunc(wwf.SHIFT_DATE) as SHIFT_DATE,wwf.DEPARTMENT_ID,wwf.INVENTORY_ITEM_ID,
713 (case when (sum(wwf.QUANTITY_COMPLETED) = 0) then 0 else round(100*sum(wwf.QUANTITY_SCRAPPED)/sum(wwf.QUANTITY_COMPLETED),2) end) as SCRAP_PERCENT,
714 (case when (sum(wwf.QUANTITY_COMPLETED) = 0) then 0 else round(100*sum(wwf.QUANTITY_REJECTED)/sum(wwf.QUANTITY_COMPLETED),2) end) as REJECT_PERCENT,
715 (case when (sum(wwf.QUANTITY_COMPLETED) = 0) then 0 else round(100*(sum(wwf.QUANTITY_COMPLETED)-sum(QUANTITY_SCRAPPED+QUANTITY_REJECTED))/sum(wwf.QUANTITY_COMPLETED),2) end) as FPY_PERCENT,
716 sum(wwf.QUANTITY_COMPLETED) as QUANTITY_COMPLETED, sum(wwf.QUANTITY_SCRAPPED) as QUANTITY_SCRAPPED, sum (wwf.QUANTITY_REJECTED) as QUANTITY_REJECTED
717 FROM WIP_WS_FPY wwf
718 WHERE organization_id =p_org_id
719 AND TRUNC(wwf.shift_date) >= TRUNC(p_cutoff_date)
720 AND wwf.SHIFT_NUM IS NOT NULL AND wwf.SHIFT_DATE IS NOT NULL
721 AND wwf.DEPARTMENT_ID IS NOT NULL AND wwf.INVENTORY_ITEM_ID IS NOT NULL
722 AND wwf.WIP_ENTITY_ID IS NOT NULL AND wwf.OPERATION_SEQ_NUM IS NOT NULL
723 GROUP BY wwf.OPERATION_SEQ_NUM,trunc(wwf.SHIFT_DATE),wwf.WIP_ENTITY_ID,wwf.DEPARTMENT_ID,wwf.INVENTORY_ITEM_ID
724 ) pre_cal;
725
726 x_return_status := FND_API.G_RET_STS_SUCCESS;
727
728 EXCEPTION
729 WHEN others THEN
730 exception_log(l_proc_name,p_return_status => x_return_status);
731 END calc_fpy_per_jobop_day;
732
733
734
735
736 PROCEDURE calc_fpy_per_jobop_week(
737 p_execution_date DATE,
738 p_cutoff_date DATE,
739 p_org_id NUMBER,
740 x_return_status OUT NOCOPY VARCHAR2) IS
741
742 l_return_status VARCHAR2(1);
743 l_proc_name VARCHAR2(60) :='wip_ws_embedded_analytics_pk.calc_fpy_per_jobop_week';
744 BEGIN
745
746 begin_log (p_params => set_calc_param(p_execution_date => p_execution_date,p_cutoff_date => p_cutoff_date,p_org_id => p_org_id),
747 p_proc_name => l_proc_name,
748 p_return_status => l_return_status);
749
750 INSERT INTO wip_ws_fpy (
751 ORGANIZATION_ID, DEPARTMENT_ID, INVENTORY_ITEM_ID,
752 WIP_ENTITY_ID, OPERATION_SEQ_NUM,
753 SHIFT_NUM, SHIFT_DATE,
754 QUANTITY_REJECTED, QUANTITY_SCRAPPED, QUANTITY_COMPLETED,
755 SCRAP_PERCENT, REJECT_PERCENT, FPY_PERCENT,
756 REQUEST_ID, PROGRAM_ID,
757 LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
758 CREATION_DATE, CREATED_BY,
759 PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE
760 )
761 SELECT
762 p_org_id AS ORGANIZATION_ID, pre_cal.DEPARTMENT_ID, pre_cal.INVENTORY_ITEM_ID,
763 pre_cal.WIP_ENTITY_ID, pre_cal.OPERATION_SEQ_NUM,
764 null as SHIFT_NUM, null SHIFT_DATE,
765 pre_cal.QUANTITY_REJECTED AS QUANTITY_REJECTED, pre_cal.QUANTITY_SCRAPPED AS QUANTITY_SCRAPPED, pre_cal.QUANTITY_COMPLETED AS QUANTITY_COMPLETED,
766 (case when (pre_cal.FPY_PERCENT < 0) then 0 else pre_cal.SCRAP_PERCENT end) as SCRAP_PERCENT,
767 (case when (pre_cal.FPY_PERCENT < 0) then 0 else pre_cal.REJECT_PERCENT end) as REJECT_PERCENT,
768 (case when (pre_cal.FPY_PERCENT < 0) then 0 else pre_cal.FPY_PERCENT end) as FPY_PERCENT,
769 g_request_id as REQUEST_ID, g_prog_id as PROGRAM_ID,
770 p_execution_date as LAST_UPDATE_DATE, g_user_id as LAST_UPDATED_BY, g_login_id as LAST_UPDATE_LOGIN,
771 p_execution_date as CREATION_DATE, g_user_id as CREATED_BY,
772 g_prog_appid as PROGRAM_APPLICATION_ID, p_execution_date as PROGRAM_UPDATE_DATE
773 FROM
774 ( SELECT wwf.WIP_ENTITY_ID,wwf.OPERATION_SEQ_NUM,wwf.DEPARTMENT_ID,wwf.INVENTORY_ITEM_ID,
775 (case when (sum(wwf.QUANTITY_COMPLETED) = 0) then 0 else round(100*sum(wwf.QUANTITY_SCRAPPED)/sum(wwf.QUANTITY_COMPLETED),2) end) as SCRAP_PERCENT,
776 (case when (sum(wwf.QUANTITY_COMPLETED) = 0) then 0 else round(100*sum(wwf.QUANTITY_REJECTED)/sum(wwf.QUANTITY_COMPLETED),2) end) as REJECT_PERCENT,
777 (case when (sum(wwf.QUANTITY_COMPLETED) = 0) then 0 else round(100*(sum(wwf.QUANTITY_COMPLETED)-sum(QUANTITY_SCRAPPED+QUANTITY_REJECTED))/sum(wwf.QUANTITY_COMPLETED),2) end) as FPY_PERCENT,
778 sum(wwf.QUANTITY_COMPLETED) as QUANTITY_COMPLETED, sum(wwf.QUANTITY_SCRAPPED) as QUANTITY_SCRAPPED, sum (wwf.QUANTITY_REJECTED) as QUANTITY_REJECTED
779 FROM WIP_WS_FPY wwf
780 WHERE organization_id =p_org_id
781 AND wwf.SHIFT_DATE >= trunc(p_execution_date)-6
782 AND wwf.SHIFT_NUM IS NOT NULL AND wwf.SHIFT_DATE IS NOT NULL
783 AND wwf.DEPARTMENT_ID IS NOT NULL AND wwf.INVENTORY_ITEM_ID IS NOT NULL
784 AND wwf.WIP_ENTITY_ID IS NOT NULL AND wwf.OPERATION_SEQ_NUM IS NOT NULL
785 GROUP BY wwf.OPERATION_SEQ_NUM,wwf.WIP_ENTITY_ID,wwf.DEPARTMENT_ID,wwf.INVENTORY_ITEM_ID
786 ) pre_cal;
787
788 x_return_status := FND_API.G_RET_STS_SUCCESS;
789
790 EXCEPTION
791 WHEN others THEN
792 exception_log(l_proc_name,p_return_status => x_return_status);
793 END calc_fpy_per_jobop_week;
794
795
796
797
798 PROCEDURE calc_fpy_per_jobop_week_shift(
799 p_execution_date DATE,
800 p_cutoff_date DATE,
801 p_org_id NUMBER,
802 x_return_status OUT NOCOPY VARCHAR2) IS
803
804 l_return_status VARCHAR2(1);
805 l_proc_name VARCHAR2(60) :='wip_ws_embedded_analytics_pk.calc_fpy_per_jobop_week_shift';
806 BEGIN
807
808 begin_log (p_params => set_calc_param(p_execution_date => p_execution_date,p_cutoff_date => p_cutoff_date,p_org_id => p_org_id),
809 p_proc_name => l_proc_name,
810 p_return_status => l_return_status);
811
812 INSERT INTO wip_ws_fpy (
813 ORGANIZATION_ID, DEPARTMENT_ID, INVENTORY_ITEM_ID,
814 WIP_ENTITY_ID, OPERATION_SEQ_NUM,
815 SHIFT_NUM, SHIFT_DATE,
816 QUANTITY_REJECTED, QUANTITY_SCRAPPED, QUANTITY_COMPLETED,
817 SCRAP_PERCENT, REJECT_PERCENT, FPY_PERCENT,
818 REQUEST_ID, PROGRAM_ID,
819 LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
820 CREATION_DATE, CREATED_BY,
821 PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE
822 )
823 SELECT
824 p_org_id AS ORGANIZATION_ID, pre_cal.DEPARTMENT_ID, pre_cal.INVENTORY_ITEM_ID,
825 pre_cal.WIP_ENTITY_ID, pre_cal.OPERATION_SEQ_NUM,
826 pre_cal.SHIFT_NUM AS SHIFT_NUM, NULL SHIFT_DATE,
827 pre_cal.QUANTITY_REJECTED AS QUANTITY_REJECTED, pre_cal.QUANTITY_SCRAPPED AS QUANTITY_SCRAPPED, pre_cal.QUANTITY_COMPLETED AS QUANTITY_COMPLETED,
828 (case when (pre_cal.FPY_PERCENT < 0) then 0 else pre_cal.SCRAP_PERCENT end) as SCRAP_PERCENT,
829 (case when (pre_cal.FPY_PERCENT < 0) then 0 else pre_cal.REJECT_PERCENT end) as REJECT_PERCENT,
830 (case when (pre_cal.FPY_PERCENT < 0) then 0 else pre_cal.FPY_PERCENT end) as FPY_PERCENT,
831 g_request_id AS REQUEST_ID, g_prog_id AS PROGRAM_ID,
832 p_execution_date AS LAST_UPDATE_DATE, g_user_id AS LAST_UPDATED_BY, g_login_id as LAST_UPDATE_LOGIN,
833 p_execution_date AS CREATION_DATE, g_user_id AS CREATED_BY,
834 g_prog_appid AS PROGRAM_APPLICATION_ID, p_execution_date AS PROGRAM_UPDATE_DATE
835 FROM
836 ( SELECT wwf.WIP_ENTITY_ID,wwf.OPERATION_SEQ_NUM,wwf.DEPARTMENT_ID,wwf.INVENTORY_ITEM_ID,wwf.SHIFT_NUM,
837 (case when (sum(wwf.QUANTITY_COMPLETED) = 0) then 0 else round(100*sum(wwf.QUANTITY_SCRAPPED)/sum(wwf.QUANTITY_COMPLETED),2) end) AS SCRAP_PERCENT,
838 (case when (sum(wwf.QUANTITY_COMPLETED) = 0) then 0 else round(100*sum(wwf.QUANTITY_REJECTED)/sum(wwf.QUANTITY_COMPLETED),2) end) AS REJECT_PERCENT,
839 (case when (sum(wwf.QUANTITY_COMPLETED) = 0) then 0 else round(100*(sum(wwf.QUANTITY_COMPLETED)-sum(QUANTITY_SCRAPPED+QUANTITY_REJECTED))/sum(wwf.QUANTITY_COMPLETED),2) end) as FPY_PERCENT,
840 sum(wwf.QUANTITY_COMPLETED) as QUANTITY_COMPLETED, sum(wwf.QUANTITY_SCRAPPED) as QUANTITY_SCRAPPED, sum (wwf.QUANTITY_REJECTED) as QUANTITY_REJECTED
841 FROM WIP_WS_FPY wwf
842 WHERE organization_id =p_org_id
843 AND wwf.SHIFT_DATE >= trunc(p_execution_date-6)
844 AND wwf.SHIFT_NUM IS NOT NULL AND wwf.SHIFT_DATE IS NOT NULL
845 AND wwf.DEPARTMENT_ID IS NOT NULL AND wwf.INVENTORY_ITEM_ID IS NOT NULL
846 AND wwf.WIP_ENTITY_ID IS NOT NULL AND wwf.OPERATION_SEQ_NUM IS NOT NULL
847 GROUP BY wwf.OPERATION_SEQ_NUM,wwf.WIP_ENTITY_ID,wwf.DEPARTMENT_ID,wwf.INVENTORY_ITEM_ID,wwf.SHIFT_NUM
848 ) pre_cal;
849 x_return_status := FND_API.G_RET_STS_SUCCESS;
850
851 EXCEPTION
852 WHEN others THEN
853 exception_log(l_proc_name,p_return_status => x_return_status);
854 END calc_fpy_per_jobop_week_shift;
855
856
857
858
859 /*
860 PROCEDURE calc_fpy_per_job_day(
861 p_execution_date DATE,
862 p_cutoff_date DATE,
863 p_org_id NUMBER,
864 x_return_status OUT NOCOPY VARCHAR2) IS
865
866 l_return_status VARCHAR2(1);
867 l_proc_name VARCHAR2(60) :='wip_ws_embedded_analytics_pk.calc_fpy_per_job_day';
868 BEGIN
869
870 begin_log (p_params => set_calc_param(p_execution_date => p_execution_date,p_cutoff_date => p_cutoff_date,p_org_id => p_org_id),
871 p_proc_name => l_proc_name,
872 p_return_status => l_return_status);
873
874 x_return_status := FND_API.G_RET_STS_SUCCESS;
875
876
877 EXCEPTION
878 WHEN others THEN
879 exception_log(l_proc_name,p_return_status => x_return_status);
880 END calc_fpy_per_job_day;
881 */
882
883 --============================================ PER JOB ========
884
885 PROCEDURE calc_fpy_per_job_day(
886 p_execution_date DATE,
887 p_cutoff_date DATE,
888 p_org_id NUMBER,
889 x_return_status OUT NOCOPY VARCHAR2) IS
890
891 l_return_status VARCHAR2(1);
892 l_proc_name VARCHAR2(60) :='wip_ws_embedded_analytics_pk.calc_fpy_per_job_day';
893 BEGIN
894
895 begin_log (p_params => set_calc_param(p_execution_date => p_execution_date,p_cutoff_date => p_cutoff_date,p_org_id => p_org_id),
896 p_proc_name => l_proc_name,
897 p_return_status => l_return_status);
898
899 INSERT INTO wip_ws_fpy (
900 ORGANIZATION_ID, DEPARTMENT_ID, INVENTORY_ITEM_ID,
901 WIP_ENTITY_ID, OPERATION_SEQ_NUM,
902 SHIFT_NUM, SHIFT_DATE,
903 QUANTITY_REJECTED, QUANTITY_SCRAPPED, QUANTITY_COMPLETED,
904 SCRAP_PERCENT, REJECT_PERCENT, FPY_PERCENT,
905 REQUEST_ID, PROGRAM_ID,
906 LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
907 CREATION_DATE, CREATED_BY,
908 PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE
909 ) SELECT
910 p_org_id AS ORGANIZATION_ID, NULL as DEPARTMENT_ID, pre_calc.INVENTORY_ITEM_ID,
911 pre_calc.WIP_ENTITY_ID, NULL AS OPERATION_SEQ_NUM,
912 NULL AS SHIFT_NUM, pre_calc .SHIFT_DATE,
913 NULL AS QUANTITY_REJECTED, NULL AS QUANTITY_SCRAPPED, pre_calc.START_QUANTITY AS QUANTITY_COMPLETED,
914 pre_calc.SCRAP_PERCENT * 100 AS SCRAP_PERCENT,
915 pre_calc.REJECT_PERCENT * 100 AS REJECT_PERCENT, --Bug 7114765
916 pre_calc.FPY_PERCENT*100 AS FPY_PERCENT,
917 g_request_id AS REQUEST_ID, g_prog_id AS PROGRAM_ID,
918 p_execution_date AS LAST_UPDATE_DATE, g_user_id AS LAST_UPDATED_BY, g_login_id as LAST_UPDATE_LOGIN,
919 p_execution_date AS CREATION_DATE, g_user_id AS CREATED_BY,
920 g_prog_appid AS PROGRAM_APPLICATION_ID, p_execution_date AS PROGRAM_UPDATE_DATE
921 FROM
922 ( SELECT wdj.START_QUANTITY, day_sum.WIP_ENTITY_ID, day_sum.INVENTORY_ITEM_ID, day_sum.SHIFT_DATE,day_sum.FPY_PERCENT,
923 (CASE WHEN (wdj.START_QUANTITY = 0) THEN 0 ELSE round(day_sum.TOTAL_DAY_SCRAP/wdj.START_QUANTITY,4) END) as SCRAP_PERCENT,
924 (CASE WHEN (wdj.START_QUANTITY = 0) THEN 0 ELSE round(day_sum.TOTAL_DAY_REJECT/wdj.START_QUANTITY,4) END) as REJECT_PERCENT --Bug 7114765
925 FROM
926 ( SELECT
927 round(exp(sum(ln(DECODE(SIGN(FPY_PERCENT),1,FPY_PERCENT/100,1)))),4) as FPY_PERCENT,
928 SUM(wwf.QUANTITY_SCRAPPED) as TOTAL_DAY_SCRAP,
929 SUM(wwf.QUANTITY_REJECTED) as TOTAL_DAY_REJECT, --Bug 7114765
930 wwf.WIP_ENTITY_ID,
931 TRUNC(shift_date) as SHIFT_DATE, wwf.INVENTORY_ITEM_ID
932 FROM WIP_WS_FPY wwf
933 WHERE wwf.organization_id = p_org_id AND TRUNC(wwf.shift_date) >= TRUNC(p_cutoff_date)
934 AND wwf.SHIFT_DATE is NOT NULL AND wwf.SHIFT_NUM is NULL
935 AND wwf.WIP_ENTITY_ID is NOT NULL AND wwf.DEPARTMENT_ID is NOT NULL
936 AND wwf.INVENTORY_ITEM_ID is NOT NULL AND wwf.OPERATION_SEQ_NUM is NOT NULL
937 GROUP BY wwf.WIP_ENTITY_ID, TRUNC(shift_date),wwf.INVENTORY_ITEM_ID
938 HAVING MIN(FPY_PERCENT) > 0
939
940 UNION
941
942 SELECT 0 as FPY_PERCENT,
943 SUM(wwf.QUANTITY_SCRAPPED) as TOTAL_DAY_SCRAP,
944 SUM(wwf.QUANTITY_REJECTED) as TOTAL_DAY_REJECT, --BUG 7114765
945 wwf.WIP_ENTITY_ID,
946 TRUNC(shift_date) as SHIFT_DATE, wwf.INVENTORY_ITEM_ID
947 FROM WIP_WS_FPY wwf
948 WHERE wwf.organization_id = p_org_id AND TRUNC(wwf.shift_date) >= TRUNC(p_cutoff_date)
949 AND wwf.SHIFT_DATE is NOT NULL AND wwf.SHIFT_NUM is NULL
950 AND wwf.WIP_ENTITY_ID is NOT NULL AND wwf.DEPARTMENT_ID is NOT NULL
951 AND wwf.INVENTORY_ITEM_ID is NOT NULL AND wwf.OPERATION_SEQ_NUM is NOT NULL
952 GROUP BY wwf.WIP_ENTITY_ID, TRUNC(shift_date),wwf.INVENTORY_ITEM_ID
953 HAVING MIN(FPY_PERCENT) <= 0
954 ) day_sum,
955 WIP_DISCRETE_JOBS wdj
956 WHERE wdj.WIP_ENTITY_ID = day_sum.WIP_ENTITY_ID
957 AND wdj.organization_id = p_org_id) pre_calc;
958 x_return_status := FND_API.G_RET_STS_SUCCESS;
959
960 EXCEPTION
961 WHEN others THEN
962 exception_log(l_proc_name,p_return_status => x_return_status);
963 END calc_fpy_per_job_day;
964
965
966
967 PROCEDURE calc_fpy_per_job_day_shift(
968 p_execution_date DATE,
969 p_cutoff_date DATE,
970 p_org_id NUMBER,
971 x_return_status OUT NOCOPY VARCHAR2) IS
972
973 l_return_status VARCHAR2(1);
974 l_proc_name VARCHAR2(60) :='wip_ws_embedded_analytics_pk.calc_fpy_per_job_day_shift';
975 BEGIN
976
977 begin_log (p_params => set_calc_param(p_execution_date => p_execution_date,p_cutoff_date => p_cutoff_date,p_org_id => p_org_id),
978 p_proc_name => l_proc_name,
979 p_return_status => l_return_status);
980
981 INSERT INTO wip_ws_fpy (
982 ORGANIZATION_ID, DEPARTMENT_ID, INVENTORY_ITEM_ID,
983 WIP_ENTITY_ID, OPERATION_SEQ_NUM,
984 SHIFT_NUM, SHIFT_DATE,
985 QUANTITY_REJECTED, QUANTITY_SCRAPPED, QUANTITY_COMPLETED,
986 SCRAP_PERCENT, REJECT_PERCENT, FPY_PERCENT,
987 REQUEST_ID, PROGRAM_ID,
988 LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
989 CREATION_DATE, CREATED_BY,
990 PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE
991 ) SELECT
992 p_org_id AS ORGANIZATION_ID, NULL as DEPARTMENT_ID, pre_calc.INVENTORY_ITEM_ID,
993 pre_calc.WIP_ENTITY_ID, NULL AS OPERATION_SEQ_NUM,
994 pre_calc.SHIFT_NUM, pre_calc.SHIFT_DATE,
995 NULL AS QUANTITY_REJECTED, NULL AS QUANTITY_SCRAPPED, pre_calc.START_QUANTITY AS QUANTITY_COMPLETED,
996 pre_calc.SCRAP_PERCENT * 100 AS SCRAP_PERCENT,
997 pre_calc.REJECT_PERCENT * 100 AS REJECT_PERCENT, --Bug 7114765
998 pre_calc.FPY_PERCENT*100 AS FPY_PERCENT,
999 g_request_id AS REQUEST_ID, g_prog_id AS PROGRAM_ID,
1000 p_execution_date AS LAST_UPDATE_DATE, g_user_id AS LAST_UPDATED_BY, g_login_id as LAST_UPDATE_LOGIN,
1001 p_execution_date AS CREATION_DATE, g_user_id AS CREATED_BY,
1002 g_prog_appid AS PROGRAM_APPLICATION_ID, p_execution_date AS PROGRAM_UPDATE_DATE
1003 FROM
1004 ( SELECT wdj.START_QUANTITY, day_sum.WIP_ENTITY_ID, day_sum.INVENTORY_ITEM_ID, day_sum.SHIFT_DATE, day_sum.SHIFT_NUM, day_sum.FPY_PERCENT,
1005 (CASE WHEN (wdj.START_QUANTITY = 0) THEN 0 ELSE round(day_sum.TOTAL_DAY_SCRAP/wdj.START_QUANTITY,4) END) as SCRAP_PERCENT,
1006 (CASE WHEN (wdj.START_QUANTITY = 0) THEN 0 ELSE round(day_sum.TOTAL_DAY_REJECT/wdj.START_QUANTITY,4) END) as REJECT_PERCENT --Bug 7114765
1007 FROM
1008 ( SELECT
1009 round(exp(sum(ln(DECODE(SIGN(FPY_PERCENT),1,FPY_PERCENT/100,1)))),4) as FPY_PERCENT,
1010 SUM(wwf.QUANTITY_SCRAPPED) as TOTAL_DAY_SCRAP,
1011 SUM(wwf.QUANTITY_REJECTED) as TOTAL_DAY_REJECT, --Bug 7114765
1012 wwf.WIP_ENTITY_ID,
1013 TRUNC(shift_date) as SHIFT_DATE, wwf.SHIFT_NUM, wwf.INVENTORY_ITEM_ID
1014 FROM WIP_WS_FPY wwf
1015 WHERE wwf.organization_id = p_org_id AND TRUNC(wwf.shift_date) >= trunc(p_cutoff_date)
1016 AND wwf.SHIFT_DATE is NOT NULL AND wwf.SHIFT_NUM is NOT NULL
1017 AND wwf.WIP_ENTITY_ID is NOT NULL AND wwf.DEPARTMENT_ID is NOT NULL
1018 AND wwf.INVENTORY_ITEM_ID is NOT NULL AND wwf.OPERATION_SEQ_NUM is NOT NULL
1019 GROUP BY wwf.WIP_ENTITY_ID, TRUNC(shift_date),wwf.SHIFT_NUM ,wwf.INVENTORY_ITEM_ID
1020 HAVING MIN(FPY_PERCENT) > 0
1021
1022 UNION
1023
1024 SELECT
1025 0 as FPY_PERCENT,
1026 SUM(wwf.QUANTITY_SCRAPPED) as TOTAL_DAY_SCRAP,
1027 SUM(wwf.QUANTITY_REJECTED) as TOTAL_DAY_REJECT, --Bug 7114765
1028 wwf.WIP_ENTITY_ID,
1029 TRUNC(shift_date) as SHIFT_DATE, wwf.SHIFT_NUM, wwf.INVENTORY_ITEM_ID
1030 FROM WIP_WS_FPY wwf
1031 WHERE wwf.organization_id = p_org_id AND TRUNC(wwf.shift_date) >= trunc(p_cutoff_date)
1032 AND wwf.SHIFT_DATE is NOT NULL AND wwf.SHIFT_NUM is NOT NULL
1033 AND wwf.WIP_ENTITY_ID is NOT NULL AND wwf.DEPARTMENT_ID is NOT NULL
1034 AND wwf.INVENTORY_ITEM_ID is NOT NULL AND wwf.OPERATION_SEQ_NUM is NOT NULL
1035 GROUP BY wwf.WIP_ENTITY_ID, TRUNC(shift_date),wwf.SHIFT_NUM ,wwf.INVENTORY_ITEM_ID
1036 HAVING MIN(FPY_PERCENT) <= 0
1037 ) day_sum,
1038 WIP_DISCRETE_JOBS wdj
1039 WHERE wdj.WIP_ENTITY_ID = day_sum.WIP_ENTITY_ID
1040 AND wdj.organization_id = p_org_id) pre_calc;
1041 /*
1042 INSERT INTO wip_ws_fpy (
1043 ORGANIZATION_ID, DEPARTMENT_ID, INVENTORY_ITEM_ID,
1044 WIP_ENTITY_ID, OPERATION_SEQ_NUM,
1045 SHIFT_NUM, SHIFT_DATE,
1046 QUANTITY_REJECTED, QUANTITY_SCRAPPED, QUANTITY_COMPLETED,
1047 SCRAP_PERCENT, REJECT_PERCENT, FPY_PERCENT,
1048 REQUEST_ID, PROGRAM_ID,
1049 LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
1050 CREATION_DATE, CREATED_BY,
1051 PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE
1052 ) SELECT
1053 p_org_id AS ORGANIZATION_ID, NULL as DEPARTMENT_ID, pre_calc.INVENTORY_ITEM_ID,
1054 pre_calc.WIP_ENTITY_ID, NULL AS OPERATION_SEQ_NUM,
1055 pre_calc.SHIFT_NUM, pre_calc.SHIFT_DATE,
1056 NULL AS QUANTITY_REJECTED, NULL AS QUANTITY_SCRAPPED, pre_calc.START_QUANTITY AS QUANTITY_COMPLETED,
1057 pre_calc.SCRAP_PERCENT * 100 AS SCRAP_PERCENT,
1058 100*(1 - pre_calc.FPY_PERCENT- pre_calc.SCRAP_PERCENT) AS REJECT_PERCENT,
1059 pre_calc.FPY_PERCENT*100 AS FPY_PERCENT,
1060 g_request_id AS REQUEST_ID, g_prog_id AS PROGRAM_ID,
1061 p_execution_date AS LAST_UPDATE_DATE, g_user_id AS LAST_UPDATED_BY, g_login_id as LAST_UPDATE_LOGIN,
1062 p_execution_date AS CREATION_DATE, g_user_id AS CREATED_BY,
1063 g_prog_appid AS PROGRAM_APPLICATION_ID, p_execution_date AS PROGRAM_UPDATE_DATE
1064 FROM
1065 ( SELECT wdj.START_QUANTITY, day_sum.WIP_ENTITY_ID, day_sum.INVENTORY_ITEM_ID, day_sum.SHIFT_DATE, day_sum.SHIFT_NUM, day_sum.FPY_PERCENT,
1066 (CASE WHEN (wdj.START_QUANTITY = 0) THEN 0 ELSE round(day_sum.TOTAL_DAY_SCRAP/wdj.START_QUANTITY,4) END) as SCRAP_PERCENT FROM
1067 ( SELECT as FPY_PERCENT, sum(wwf.QUANTITY_SCRAPPED) as TOTAL_DAY_SCRAP, wwf.WIP_ENTITY_ID,
1068 TRUNC(shift_date) as SHIFT_DATE, wwf.SHIFT_NUM, wwf.INVENTORY_ITEM_ID
1069 FROM WIP_WS_FPY wwf
1070 WHERE wwf.organization_id = p_org_id AND TRUNC(wwf.shift_date) >= trunc(p_cutoff_date)
1071 AND wwf.SHIFT_DATE is NOT NULL AND wwf.SHIFT_NUM is NOT NULL
1072 AND wwf.WIP_ENTITY_ID is NOT NULL AND wwf.DEPARTMENT_ID is NOT NULL
1073 AND wwf.INVENTORY_ITEM_ID is NOT NULL AND wwf.OPERATION_SEQ_NUM is NOT NULL
1074 GROUP BY wwf.WIP_ENTITY_ID, TRUNC(shift_date),wwf.SHIFT_NUM ,wwf.INVENTORY_ITEM_ID
1075 HAVING MIN(FPY_PERCENT) = 0) day_sum,
1076 WIP_DISCRETE_JOBS wdj
1077 WHERE wdj.WIP_ENTITY_ID = day_sum.WIP_ENTITY_ID
1078 AND wdj.organization_id = p_org_id) pre_calc;
1079 */
1080 x_return_status := FND_API.G_RET_STS_SUCCESS;
1081
1082 EXCEPTION
1083 WHEN others THEN
1084 exception_log(l_proc_name,p_return_status => x_return_status);
1085 END calc_fpy_per_job_day_shift;
1086
1087
1088
1089 PROCEDURE calc_fpy_per_job_week(
1090 p_execution_date DATE,
1091 p_cutoff_date DATE,
1092 p_org_id NUMBER,
1093 x_return_status OUT NOCOPY VARCHAR2) IS
1094
1095 l_return_status VARCHAR2(1);
1096 l_proc_name VARCHAR2(60) :='wip_ws_embedded_analytics_pk.calc_fpy_per_job_week';
1097 BEGIN
1098
1099 begin_log (p_params => set_calc_param(p_execution_date => p_execution_date,p_cutoff_date => p_cutoff_date,p_org_id => p_org_id),
1100 p_proc_name => l_proc_name,
1101 p_return_status => l_return_status);
1102
1103 INSERT INTO wip_ws_fpy (
1104 ORGANIZATION_ID, DEPARTMENT_ID, INVENTORY_ITEM_ID,
1105 WIP_ENTITY_ID, OPERATION_SEQ_NUM,
1106 SHIFT_NUM, SHIFT_DATE,
1107 QUANTITY_REJECTED, QUANTITY_SCRAPPED, QUANTITY_COMPLETED,
1108 SCRAP_PERCENT, REJECT_PERCENT, FPY_PERCENT,
1109 REQUEST_ID, PROGRAM_ID,
1110 LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
1111 CREATION_DATE, CREATED_BY,
1112 PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE
1113 ) SELECT
1114 p_org_id AS ORGANIZATION_ID, NULL as DEPARTMENT_ID, pre_calc.INVENTORY_ITEM_ID,
1115 pre_calc.WIP_ENTITY_ID, NULL AS OPERATION_SEQ_NUM,
1116 NULL AS SHIFT_NUM, NULL AS SHIFT_DATE,
1117 NULL AS QUANTITY_REJECTED, NULL AS QUANTITY_SCRAPPED, pre_calc.START_QUANTITY AS QUANTITY_COMPLETED,
1118 pre_calc.SCRAP_PERCENT * 100 AS SCRAP_PERCENT,
1119 pre_calc.REJECT_PERCENT * 100 AS REJECT_PERCENT, --Bug 7114765
1120 pre_calc.FPY_PERCENT*100 AS FPY_PERCENT,
1121 g_request_id AS REQUEST_ID, g_prog_id AS PROGRAM_ID,
1122 p_execution_date AS LAST_UPDATE_DATE, g_user_id AS LAST_UPDATED_BY, g_login_id as LAST_UPDATE_LOGIN,
1123 p_execution_date AS CREATION_DATE, g_user_id AS CREATED_BY,
1124 g_prog_appid AS PROGRAM_APPLICATION_ID, p_execution_date AS PROGRAM_UPDATE_DATE
1125 FROM
1126 ( SELECT wdj.START_QUANTITY, day_sum.WIP_ENTITY_ID, day_sum.INVENTORY_ITEM_ID, day_sum.FPY_PERCENT,
1127 (CASE WHEN (wdj.START_QUANTITY = 0) THEN 0 ELSE round(day_sum.TOTAL_DAY_SCRAP/wdj.START_QUANTITY,4) END) as SCRAP_PERCENT,
1128 (CASE WHEN (wdj.START_QUANTITY = 0) THEN 0 ELSE round(day_sum.TOTAL_DAY_REJECT/wdj.START_QUANTITY,4) END) as REJECT_PERCENT --Bug 7114765
1129 FROM
1130 ( SELECT
1131 round(exp(sum(ln(DECODE(SIGN(FPY_PERCENT),1,FPY_PERCENT/100,1)))),4) as FPY_PERCENT,
1132 sum(wwf.QUANTITY_SCRAPPED) as TOTAL_DAY_SCRAP,
1133 sum(wwf.QUANTITY_REJECTED) as TOTAL_DAY_REJECT, --Bug 7114765
1134 wwf.WIP_ENTITY_ID, wwf.INVENTORY_ITEM_ID
1135 FROM WIP_WS_FPY wwf
1136 WHERE wwf.organization_id = p_org_id
1137 AND wwf.SHIFT_DATE is NULL AND wwf.SHIFT_NUM is NULL
1138 AND wwf.WIP_ENTITY_ID is NOT NULL AND wwf.DEPARTMENT_ID is NOT NULL
1139 AND wwf.INVENTORY_ITEM_ID is NOT NULL AND wwf.OPERATION_SEQ_NUM is NOT NULL
1140 GROUP BY wwf.WIP_ENTITY_ID, wwf.INVENTORY_ITEM_ID
1141 HAVING MIN(FPY_PERCENT) > 0
1142
1143 UNION
1144
1145 SELECT 0 as FPY_PERCENT,
1146 sum(wwf.QUANTITY_SCRAPPED) as TOTAL_DAY_SCRAP,
1147 sum(wwf.QUANTITY_REJECTED) as TOTAL_DAY_REJECT, --Bug 7114765
1148 wwf.WIP_ENTITY_ID, wwf.INVENTORY_ITEM_ID
1149 FROM WIP_WS_FPY wwf
1150 WHERE wwf.organization_id = p_org_id
1151 AND wwf.SHIFT_DATE is NULL AND wwf.SHIFT_NUM is NULL
1152 AND wwf.WIP_ENTITY_ID is NOT NULL AND wwf.DEPARTMENT_ID is NOT NULL
1153 AND wwf.INVENTORY_ITEM_ID is NOT NULL AND wwf.OPERATION_SEQ_NUM is NOT NULL
1154 GROUP BY wwf.WIP_ENTITY_ID, wwf.INVENTORY_ITEM_ID
1155 HAVING MIN(FPY_PERCENT) <= 0
1156 ) day_sum,
1157 WIP_DISCRETE_JOBS wdj
1158 WHERE wdj.WIP_ENTITY_ID = day_sum.WIP_ENTITY_ID
1159 AND wdj.organization_id = p_org_id) pre_calc;
1160 x_return_status := FND_API.G_RET_STS_SUCCESS;
1161
1162 EXCEPTION
1163 WHEN others THEN
1164 exception_log(l_proc_name,p_return_status => x_return_status);
1165 END calc_fpy_per_job_week;
1166
1167
1168
1169
1170 PROCEDURE calc_fpy_per_job_week_shift(
1171 p_execution_date DATE,
1172 p_cutoff_date DATE,
1173 p_org_id NUMBER,
1174 x_return_status OUT NOCOPY VARCHAR2) IS
1175
1176 l_return_status VARCHAR2(1);
1177 l_proc_name VARCHAR2(60) :='wip_ws_embedded_analytics_pk.calc_fpy_per_job_week_shift';
1178 BEGIN
1179
1180 begin_log (p_params => set_calc_param(p_execution_date => p_execution_date,p_cutoff_date => p_cutoff_date,p_org_id => p_org_id),
1181 p_proc_name => l_proc_name,
1182 p_return_status => l_return_status);
1183
1184 INSERT INTO wip_ws_fpy (
1185 ORGANIZATION_ID, DEPARTMENT_ID, INVENTORY_ITEM_ID,
1186 WIP_ENTITY_ID, OPERATION_SEQ_NUM,
1187 SHIFT_NUM, SHIFT_DATE,
1188 QUANTITY_REJECTED, QUANTITY_SCRAPPED, QUANTITY_COMPLETED,
1189 SCRAP_PERCENT, REJECT_PERCENT, FPY_PERCENT,
1190 REQUEST_ID, PROGRAM_ID,
1191 LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
1192 CREATION_DATE, CREATED_BY,
1193 PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE
1194 ) SELECT
1195 p_org_id AS ORGANIZATION_ID, NULL as DEPARTMENT_ID, pre_calc.INVENTORY_ITEM_ID,
1196 pre_calc.WIP_ENTITY_ID, NULL AS OPERATION_SEQ_NUM,
1197 pre_calc.SHIFT_NUM, NULL AS SHIFT_DATE,
1198 NULL AS QUANTITY_REJECTED, NULL AS QUANTITY_SCRAPPED, pre_calc.START_QUANTITY AS QUANTITY_COMPLETED,
1199 pre_calc.SCRAP_PERCENT * 100 AS SCRAP_PERCENT,
1200 pre_calc.REJECT_PERCENT * 100 AS REJECT_PERCENT, --Bug 7114765
1201 pre_calc.FPY_PERCENT*100 AS FPY_PERCENT,
1202 g_request_id AS REQUEST_ID, g_prog_id AS PROGRAM_ID,
1203 p_execution_date AS LAST_UPDATE_DATE, g_user_id AS LAST_UPDATED_BY, g_login_id as LAST_UPDATE_LOGIN,
1204 p_execution_date AS CREATION_DATE, g_user_id AS CREATED_BY,
1205 g_prog_appid AS PROGRAM_APPLICATION_ID, p_execution_date AS PROGRAM_UPDATE_DATE
1206 FROM
1207 ( SELECT wdj.START_QUANTITY, day_sum.WIP_ENTITY_ID, day_sum.INVENTORY_ITEM_ID, day_sum.FPY_PERCENT, day_sum.SHIFT_NUM,
1208 (CASE WHEN (wdj.START_QUANTITY = 0) THEN 0 ELSE round(day_sum.TOTAL_DAY_SCRAP/wdj.START_QUANTITY,4) END) as SCRAP_PERCENT,
1209 (CASE WHEN (wdj.START_QUANTITY = 0) THEN 0 ELSE round(day_sum.TOTAL_DAY_REJECT/wdj.START_QUANTITY,4) END) as REJECT_PERCENT --Bug 7114765
1210 FROM
1211 ( SELECT
1212 round(exp(sum(ln(DECODE(SIGN(FPY_PERCENT),1,FPY_PERCENT/100,1)))),4) as FPY_PERCENT,
1213 sum(wwf.QUANTITY_SCRAPPED) as TOTAL_DAY_SCRAP,
1214 sum(wwf.QUANTITY_REJECTED) as TOTAL_DAY_REJECT, --Bug 7114765
1215 wwf.WIP_ENTITY_ID, wwf.INVENTORY_ITEM_ID, wwf.SHIFT_NUM
1216 FROM WIP_WS_FPY wwf
1217 WHERE wwf.organization_id = p_org_id
1218 AND wwf.SHIFT_DATE is NULL AND wwf.SHIFT_NUM is NOT NULL
1219 AND wwf.WIP_ENTITY_ID is NOT NULL AND wwf.DEPARTMENT_ID is NOT NULL
1220 AND wwf.INVENTORY_ITEM_ID is NOT NULL AND wwf.OPERATION_SEQ_NUM is NOT NULL
1221 GROUP BY wwf.WIP_ENTITY_ID, wwf.INVENTORY_ITEM_ID, wwf.SHIFT_NUM
1222 HAVING MIN(FPY_PERCENT) > 0
1223
1224 UNION
1225
1226 SELECT 0 as FPY_PERCENT,
1227 sum(wwf.QUANTITY_SCRAPPED) as TOTAL_DAY_SCRAP,
1228 sum(wwf.QUANTITY_REJECTED) as TOTAL_DAY_REJECT, --Bug 7114765
1229 wwf.WIP_ENTITY_ID, wwf.INVENTORY_ITEM_ID, wwf.SHIFT_NUM
1230 FROM WIP_WS_FPY wwf
1231 WHERE wwf.organization_id = p_org_id
1232 AND wwf.SHIFT_DATE is NULL AND wwf.SHIFT_NUM is NOT NULL
1233 AND wwf.WIP_ENTITY_ID is NOT NULL AND wwf.DEPARTMENT_ID is NOT NULL
1234 AND wwf.INVENTORY_ITEM_ID is NOT NULL AND wwf.OPERATION_SEQ_NUM is NOT NULL
1235 GROUP BY wwf.WIP_ENTITY_ID, wwf.INVENTORY_ITEM_ID, wwf.SHIFT_NUM
1236 HAVING MIN(FPY_PERCENT) <= 0
1237 ) day_sum,
1238 WIP_DISCRETE_JOBS wdj
1239 WHERE wdj.WIP_ENTITY_ID = day_sum.WIP_ENTITY_ID
1240 AND wdj.organization_id = p_org_id) pre_calc;
1241 x_return_status := FND_API.G_RET_STS_SUCCESS;
1242
1243 EXCEPTION
1244 WHEN others THEN
1245 exception_log(l_proc_name,p_return_status => x_return_status);
1246 END calc_fpy_per_job_week_shift;
1247
1248
1249 --============================================ PER ASSEMBLY ========
1250
1251 PROCEDURE calc_fpy_per_assm_day_shift(
1252 p_execution_date DATE,
1253 p_cutoff_date DATE,
1254 p_org_id NUMBER,
1255 x_return_status OUT NOCOPY VARCHAR2) IS
1256
1257 l_return_status VARCHAR2(1);
1258 l_proc_name VARCHAR2(60) :='wip_ws_embedded_analytics_pk.calc_fpy_per_assm_day_shift';
1259 BEGIN
1260
1261 begin_log (p_params => set_calc_param(p_execution_date => p_execution_date,p_cutoff_date => p_cutoff_date,p_org_id => p_org_id),
1262 p_proc_name => l_proc_name,
1263 p_return_status => l_return_status);
1264
1265 INSERT INTO wip_ws_fpy (
1266 ORGANIZATION_ID, DEPARTMENT_ID, INVENTORY_ITEM_ID,
1267 WIP_ENTITY_ID, OPERATION_SEQ_NUM,
1268 SHIFT_NUM, SHIFT_DATE,
1269 QUANTITY_REJECTED, QUANTITY_SCRAPPED, QUANTITY_COMPLETED,
1270 SCRAP_PERCENT, REJECT_PERCENT, FPY_PERCENT,
1271 REQUEST_ID, PROGRAM_ID,
1272 LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
1273 CREATION_DATE, CREATED_BY,
1274 PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE
1275 ) SELECT
1276 p_org_id AS ORGANIZATION_ID, NULL as DEPARTMENT_ID, wwf.INVENTORY_ITEM_ID,
1277 NULL AS WIP_ENTITY_ID, NULL AS OPERATION_SEQ_NUM,
1278 wwf.SHIFT_NUM, wwf.SHIFT_DATE,
1279 NULL AS QUANTITY_REJECTED, NULL AS QUANTITY_SCRAPPED, NULL AS QUANTITY_COMPLETED,
1280 (CASE WHEN (SUM(wwf.QUANTITY_COMPLETED)=0) THEN 0 ELSE ROUND(SUM(wwf.SCRAP_PERCENT*wwf.QUANTITY_COMPLETED)/SUM(wwf.QUANTITY_COMPLETED),2) END) AS SCRAP_PERCENT,
1281 (CASE WHEN (SUM(wwf.QUANTITY_COMPLETED)=0) THEN 0 ELSE ROUND(SUM(wwf.REJECT_PERCENT*wwf.QUANTITY_COMPLETED)/SUM(wwf.QUANTITY_COMPLETED),2) END) AS REJECT_PERCENT,
1282 (CASE WHEN (SUM(wwf.QUANTITY_COMPLETED)=0) THEN 0 ELSE ROUND(SUM(wwf.FPY_PERCENT*wwf.QUANTITY_COMPLETED)/SUM(wwf.QUANTITY_COMPLETED),2) END) AS FPY_PERCENT,
1283 g_request_id AS REQUEST_ID, g_prog_id AS PROGRAM_ID,
1284 p_execution_date AS LAST_UPDATE_DATE, g_user_id AS LAST_UPDATED_BY, g_login_id as LAST_UPDATE_LOGIN,
1285 p_execution_date AS CREATION_DATE, g_user_id AS CREATED_BY,
1286 g_prog_appid AS PROGRAM_APPLICATION_ID, p_execution_date AS PROGRAM_UPDATE_DATE
1287 FROM wip_ws_fpy wwf
1288 WHERE wwf.WIP_ENTITY_ID is NOT NULL and wwf.OPERATION_SEQ_NUM is NULL
1289 and wwf.INVENTORY_ITEM_ID is NOT NULL and wwf.DEPARTMENT_ID is NULL
1290 and wwf.SHIFT_DATE is NOT NULL and wwf.SHIFT_NUM is NOT NULL
1291 and wwf.ORGANIZATION_ID = p_org_id
1292 and TRUNC(wwf.SHIFT_DATE) >= TRUNC(p_cutoff_date)
1293 GROUP BY wwf.Inventory_Item_id, wwf.SHIFT_DATE, wwf.SHIFT_NUM;
1294
1295 x_return_status := FND_API.G_RET_STS_SUCCESS;
1296
1297 EXCEPTION
1298 WHEN others THEN
1299 exception_log(l_proc_name,p_return_status => x_return_status);
1300 END calc_fpy_per_assm_day_shift;
1301
1302
1303
1304
1305 PROCEDURE calc_fpy_per_assm_day(
1306 p_execution_date DATE,
1307 p_cutoff_date DATE,
1308 p_org_id NUMBER,
1309 x_return_status OUT NOCOPY VARCHAR2) IS
1310
1311 l_return_status VARCHAR2(1);
1312 l_proc_name VARCHAR2(60) :='wip_ws_embedded_analytics_pk.calc_fpy_per_assm_day';
1313 BEGIN
1314
1315 begin_log (p_params => set_calc_param(p_execution_date => p_execution_date,p_cutoff_date => p_cutoff_date,p_org_id => p_org_id),
1316 p_proc_name => l_proc_name,
1317 p_return_status => l_return_status);
1318
1319 INSERT INTO wip_ws_fpy (
1320 ORGANIZATION_ID, DEPARTMENT_ID, INVENTORY_ITEM_ID,
1321 WIP_ENTITY_ID, OPERATION_SEQ_NUM,
1322 SHIFT_NUM, SHIFT_DATE,
1323 QUANTITY_REJECTED, QUANTITY_SCRAPPED, QUANTITY_COMPLETED,
1324 SCRAP_PERCENT, REJECT_PERCENT, FPY_PERCENT,
1325 REQUEST_ID, PROGRAM_ID,
1326 LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
1327 CREATION_DATE, CREATED_BY,
1328 PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE
1329 ) SELECT
1330 p_org_id AS ORGANIZATION_ID, NULL as DEPARTMENT_ID, wwf.INVENTORY_ITEM_ID,
1331 NULL AS WIP_ENTITY_ID, NULL AS OPERATION_SEQ_NUM,
1332 NULL AS SHIFT_NUM, wwf.SHIFT_DATE,
1333 NULL AS QUANTITY_REJECTED, NULL AS QUANTITY_SCRAPPED, NULL AS QUANTITY_COMPLETED,
1334 (CASE WHEN (SUM(wwf.QUANTITY_COMPLETED)=0) THEN 0 ELSE ROUND(SUM(wwf.SCRAP_PERCENT*wwf.QUANTITY_COMPLETED)/SUM(wwf.QUANTITY_COMPLETED),2) END) AS SCRAP_PERCENT,
1335 (CASE WHEN (SUM(wwf.QUANTITY_COMPLETED)=0) THEN 0 ELSE ROUND(SUM(wwf.REJECT_PERCENT*wwf.QUANTITY_COMPLETED)/SUM(wwf.QUANTITY_COMPLETED),2) END) AS REJECT_PERCENT,
1336 (CASE WHEN (SUM(wwf.QUANTITY_COMPLETED)=0) THEN 0 ELSE ROUND(SUM(wwf.FPY_PERCENT*wwf.QUANTITY_COMPLETED)/SUM(wwf.QUANTITY_COMPLETED),2) END) AS FPY_PERCENT,
1337 g_request_id AS REQUEST_ID, g_prog_id AS PROGRAM_ID,
1338 p_execution_date AS LAST_UPDATE_DATE, g_user_id AS LAST_UPDATED_BY, g_login_id as LAST_UPDATE_LOGIN,
1339 p_execution_date AS CREATION_DATE, g_user_id AS CREATED_BY,
1340 g_prog_appid AS PROGRAM_APPLICATION_ID, p_execution_date AS PROGRAM_UPDATE_DATE
1341 FROM wip_ws_fpy wwf
1342 WHERE wwf.WIP_ENTITY_ID is NOT NULL and wwf.OPERATION_SEQ_NUM is NULL
1343 and wwf.INVENTORY_ITEM_ID is NOT NULL and wwf.DEPARTMENT_ID is NULL
1344 and wwf.SHIFT_DATE is NOT NULL and wwf.SHIFT_NUM is NULL
1345 and wwf.ORGANIZATION_ID = p_org_id
1346 and TRUNC(wwf.SHIFT_DATE) >= TRUNC(p_cutoff_date)
1347 GROUP BY wwf.Inventory_Item_id, wwf.SHIFT_DATE;
1348
1349 x_return_status := FND_API.G_RET_STS_SUCCESS;
1350
1351 EXCEPTION
1352 WHEN others THEN
1353 exception_log(l_proc_name,p_return_status => x_return_status);
1354 END calc_fpy_per_assm_day;
1355
1356
1357
1358 PROCEDURE calc_fpy_per_assm_week_shift(
1359 p_execution_date DATE,
1360 p_cutoff_date DATE,
1361 p_org_id NUMBER,
1362 x_return_status OUT NOCOPY VARCHAR2) IS
1363
1364 l_return_status VARCHAR2(1);
1365 l_proc_name VARCHAR2(60) :='wip_ws_embedded_analytics_pk.calc_fpy_per_assm_week_shift';
1366 BEGIN
1367
1368 begin_log (p_params => set_calc_param(p_execution_date => p_execution_date,p_cutoff_date => p_cutoff_date,p_org_id => p_org_id),
1369 p_proc_name => l_proc_name,
1370 p_return_status => l_return_status);
1371
1372 INSERT INTO wip_ws_fpy (
1373 ORGANIZATION_ID, DEPARTMENT_ID, INVENTORY_ITEM_ID,
1374 WIP_ENTITY_ID, OPERATION_SEQ_NUM,
1375 SHIFT_NUM, SHIFT_DATE,
1376 QUANTITY_REJECTED, QUANTITY_SCRAPPED, QUANTITY_COMPLETED,
1377 SCRAP_PERCENT, REJECT_PERCENT, FPY_PERCENT,
1378 REQUEST_ID, PROGRAM_ID,
1379 LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
1380 CREATION_DATE, CREATED_BY,
1381 PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE
1382 ) SELECT
1383 p_org_id AS ORGANIZATION_ID, NULL as DEPARTMENT_ID, wwf.INVENTORY_ITEM_ID,
1384 NULL AS WIP_ENTITY_ID, NULL AS OPERATION_SEQ_NUM,
1385 wwf.SHIFT_NUM, NULL AS SHIFT_DATE,
1386 NULL AS QUANTITY_REJECTED, NULL AS QUANTITY_SCRAPPED, NULL AS QUANTITY_COMPLETED,
1387 (CASE WHEN (SUM(wwf.QUANTITY_COMPLETED)=0) THEN 0 ELSE ROUND(SUM(wwf.SCRAP_PERCENT*wwf.QUANTITY_COMPLETED)/SUM(wwf.QUANTITY_COMPLETED),2) END) AS SCRAP_PERCENT,
1388 (CASE WHEN (SUM(wwf.QUANTITY_COMPLETED)=0) THEN 0 ELSE ROUND(SUM(wwf.REJECT_PERCENT*wwf.QUANTITY_COMPLETED)/SUM(wwf.QUANTITY_COMPLETED),2) END) AS REJECT_PERCENT,
1389 (CASE WHEN (SUM(wwf.QUANTITY_COMPLETED)=0) THEN 0 ELSE ROUND(SUM(wwf.FPY_PERCENT*wwf.QUANTITY_COMPLETED)/SUM(wwf.QUANTITY_COMPLETED),2) END) AS FPY_PERCENT,
1390 g_request_id AS REQUEST_ID, g_prog_id AS PROGRAM_ID,
1391 p_execution_date AS LAST_UPDATE_DATE, g_user_id AS LAST_UPDATED_BY, g_login_id as LAST_UPDATE_LOGIN,
1392 p_execution_date AS CREATION_DATE, g_user_id AS CREATED_BY,
1393 g_prog_appid AS PROGRAM_APPLICATION_ID, p_execution_date AS PROGRAM_UPDATE_DATE
1394 FROM wip_ws_fpy wwf
1395 WHERE wwf.WIP_ENTITY_ID is NOT NULL and wwf.OPERATION_SEQ_NUM is NULL
1396 and wwf.INVENTORY_ITEM_ID is NOT NULL and wwf.DEPARTMENT_ID is NULL
1397 and wwf.SHIFT_DATE is NULL and wwf.SHIFT_NUM is NOT NULL
1398 and wwf.ORGANIZATION_ID = p_org_id
1399 GROUP BY wwf.Inventory_Item_id, wwf.SHIFT_NUM;
1400
1401 x_return_status := FND_API.G_RET_STS_SUCCESS;
1402
1403 EXCEPTION
1404 WHEN others THEN
1405 exception_log(l_proc_name,p_return_status => x_return_status);
1406 END calc_fpy_per_assm_week_shift;
1407
1408
1409
1410 PROCEDURE calc_fpy_per_assm_week(
1411 p_execution_date DATE,
1412 p_cutoff_date DATE,
1413 p_org_id NUMBER,
1414 x_return_status OUT NOCOPY VARCHAR2) IS
1415
1416 l_return_status VARCHAR2(1);
1417 l_proc_name VARCHAR2(60) :='wip_ws_embedded_analytics_pk.calc_fpy_per_assm_week';
1418 BEGIN
1419
1420 begin_log (p_params => set_calc_param(p_execution_date => p_execution_date,p_cutoff_date => p_cutoff_date,p_org_id => p_org_id),
1421 p_proc_name => l_proc_name,
1422 p_return_status => l_return_status);
1423
1424 INSERT INTO wip_ws_fpy (
1425 ORGANIZATION_ID, DEPARTMENT_ID, INVENTORY_ITEM_ID,
1426 WIP_ENTITY_ID, OPERATION_SEQ_NUM,
1427 SHIFT_NUM, SHIFT_DATE,
1428 QUANTITY_REJECTED, QUANTITY_SCRAPPED, QUANTITY_COMPLETED,
1429 SCRAP_PERCENT, REJECT_PERCENT, FPY_PERCENT,
1430 REQUEST_ID, PROGRAM_ID,
1431 LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
1432 CREATION_DATE, CREATED_BY,
1433 PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE
1434 ) SELECT
1435 p_org_id AS ORGANIZATION_ID, NULL as DEPARTMENT_ID, wwf.INVENTORY_ITEM_ID,
1436 NULL AS WIP_ENTITY_ID, NULL AS OPERATION_SEQ_NUM,
1437 NULL AS SHIFT_NUM, NULL AS SHIFT_DATE,
1438 NULL AS QUANTITY_REJECTED, NULL AS QUANTITY_SCRAPPED, NULL AS QUANTITY_COMPLETED,
1439 (CASE WHEN (SUM(wwf.QUANTITY_COMPLETED)=0) THEN 0 ELSE ROUND(SUM(wwf.SCRAP_PERCENT*wwf.QUANTITY_COMPLETED)/SUM(wwf.QUANTITY_COMPLETED),2) END) AS SCRAP_PERCENT,
1440 (CASE WHEN (SUM(wwf.QUANTITY_COMPLETED)=0) THEN 0 ELSE ROUND(SUM(wwf.REJECT_PERCENT*wwf.QUANTITY_COMPLETED)/SUM(wwf.QUANTITY_COMPLETED),2) END) AS REJECT_PERCENT,
1441 (CASE WHEN (SUM(wwf.QUANTITY_COMPLETED)=0) THEN 0 ELSE ROUND(SUM(wwf.FPY_PERCENT*wwf.QUANTITY_COMPLETED)/SUM(wwf.QUANTITY_COMPLETED),2) END) AS FPY_PERCENT,
1442 g_request_id AS REQUEST_ID, g_prog_id AS PROGRAM_ID,
1443 p_execution_date AS LAST_UPDATE_DATE, g_user_id AS LAST_UPDATED_BY, g_login_id as LAST_UPDATE_LOGIN,
1444 p_execution_date AS CREATION_DATE, g_user_id AS CREATED_BY,
1445 g_prog_appid AS PROGRAM_APPLICATION_ID, p_execution_date AS PROGRAM_UPDATE_DATE
1446 FROM wip_ws_fpy wwf
1447 WHERE wwf.WIP_ENTITY_ID is NOT NULL and wwf.OPERATION_SEQ_NUM is NULL
1448 and wwf.INVENTORY_ITEM_ID is NOT NULL and wwf.DEPARTMENT_ID is NULL
1449 and wwf.SHIFT_DATE is NULL and wwf.SHIFT_NUM is NULL
1450 and wwf.ORGANIZATION_ID = p_org_id
1451 GROUP BY wwf.Inventory_Item_id;
1452
1453 x_return_status := FND_API.G_RET_STS_SUCCESS;
1454
1455 EXCEPTION
1456 WHEN others THEN
1457 exception_log(l_proc_name,p_return_status => x_return_status);
1458 END calc_fpy_per_assm_week;
1459
1460
1461 --============================================ PER DEPARTMENT ========
1462
1463 PROCEDURE calc_fpy_per_dept_day_shift(
1464 p_execution_date DATE,
1465 p_cutoff_date DATE,
1466 p_org_id NUMBER,
1467 x_return_status OUT NOCOPY VARCHAR2) IS
1468
1469 l_return_status VARCHAR2(1);
1470 l_proc_name VARCHAR2(60) :='wip_ws_embedded_analytics_pk.calc_fpy_per_dept_day_shift';
1471 BEGIN
1472
1473 begin_log (p_params => set_calc_param(p_execution_date => p_execution_date,p_cutoff_date => p_cutoff_date,p_org_id => p_org_id),
1474 p_proc_name => l_proc_name,
1475 p_return_status => l_return_status);
1476
1477 INSERT INTO wip_ws_fpy (
1478 ORGANIZATION_ID, DEPARTMENT_ID, INVENTORY_ITEM_ID,
1479 WIP_ENTITY_ID, OPERATION_SEQ_NUM,
1480 SHIFT_NUM, SHIFT_DATE,
1481 QUANTITY_REJECTED, QUANTITY_SCRAPPED, QUANTITY_COMPLETED,
1482 SCRAP_PERCENT, REJECT_PERCENT, FPY_PERCENT,
1483 REQUEST_ID, PROGRAM_ID,
1484 LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
1485 CREATION_DATE, CREATED_BY,
1486 PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE
1487 )
1488 SELECT
1489 p_org_id AS ORGANIZATION_ID, pre_cal.DEPARTMENT_ID, NULL AS INVENTORY_ITEM_ID,
1490 NULL AS WIP_ENTITY_ID, NULL AS OPERATION_SEQ_NUM,
1491 pre_cal.SHIFT_NUM, pre_cal.SHIFT_DATE,
1492 NULL AS QUANTITY_REJECTED, NULL AS QUANTITY_SCRAPPED, NULL AS QUANTITY_COMPLETED,
1493 pre_cal.SCRAP_PERCENT, pre_cal.REJECT_PERCENT, pre_cal.FPY_PERCENT,
1494 g_request_id as REQUEST_ID, g_prog_id as PROGRAM_ID,
1495 p_execution_date as LAST_UPDATE_DATE, g_user_id as LAST_UPDATED_BY, g_login_id as LAST_UPDATE_LOGIN,
1496 p_execution_date as CREATION_DATE, g_user_id as CREATED_BY,
1497 g_prog_appid as PROGRAM_APPLICATION_ID, p_execution_date as PROGRAM_UPDATE_DATE
1498 FROM
1499 ( SELECT TRUNC(wwf.SHIFT_DATE) as SHIFT_DATE,wwf.DEPARTMENT_ID,wwf.SHIFT_NUM,
1500 (case when (sum(wwf.QUANTITY_COMPLETED) = 0) then 0 else round(100*sum(wwf.QUANTITY_SCRAPPED)/sum(wwf.QUANTITY_COMPLETED),2) end) as SCRAP_PERCENT,
1501 (case when (sum(wwf.QUANTITY_COMPLETED) = 0) then 0 else round(100*sum(wwf.QUANTITY_REJECTED)/sum(wwf.QUANTITY_COMPLETED),2) end) as REJECT_PERCENT,
1502 (case when (sum(wwf.QUANTITY_COMPLETED) = 0) then 0 else round(100*(sum(wwf.QUANTITY_COMPLETED)-sum(QUANTITY_SCRAPPED+QUANTITY_REJECTED))/sum(wwf.QUANTITY_COMPLETED),2) end) as FPY_PERCENT
1503 FROM WIP_WS_FPY wwf
1504 WHERE organization_id =p_org_id
1505 AND TRUNC(wwf.shift_date) >= TRUNC(p_cutoff_date)
1506 AND wwf.SHIFT_NUM IS NOT NULL AND wwf.SHIFT_DATE IS NOT NULL
1507 AND wwf.DEPARTMENT_ID IS NOT NULL AND wwf.INVENTORY_ITEM_ID IS NOT NULL
1508 AND wwf.WIP_ENTITY_ID IS NOT NULL AND wwf.OPERATION_SEQ_NUM IS NOT NULL
1509 GROUP BY TRUNC(wwf.SHIFT_DATE),wwf.DEPARTMENT_ID,wwf.SHIFT_NUM
1510 ) pre_cal;
1511
1512 x_return_status := FND_API.G_RET_STS_SUCCESS;
1513
1514 EXCEPTION
1515 WHEN others THEN
1516 exception_log(l_proc_name,p_return_status => x_return_status);
1517 END calc_fpy_per_dept_day_shift;
1518
1519
1520
1521 PROCEDURE calc_fpy_per_dept_day(
1522 p_execution_date DATE,
1523 p_cutoff_date DATE,
1524 p_org_id NUMBER,
1525 x_return_status OUT NOCOPY VARCHAR2) IS
1526
1527 l_return_status VARCHAR2(1);
1528 l_proc_name VARCHAR2(60) :='wip_ws_embedded_analytics_pk.calc_fpy_per_dept_day';
1529 BEGIN
1530
1531 begin_log (p_params => set_calc_param(p_execution_date => p_execution_date,p_cutoff_date => p_cutoff_date,p_org_id => p_org_id),
1532 p_proc_name => l_proc_name,
1533 p_return_status => l_return_status);
1534
1535 INSERT INTO wip_ws_fpy (
1536 ORGANIZATION_ID, DEPARTMENT_ID, INVENTORY_ITEM_ID,
1537 WIP_ENTITY_ID, OPERATION_SEQ_NUM,
1538 SHIFT_NUM, SHIFT_DATE,
1539 QUANTITY_REJECTED, QUANTITY_SCRAPPED, QUANTITY_COMPLETED,
1540 SCRAP_PERCENT, REJECT_PERCENT, FPY_PERCENT,
1541 REQUEST_ID, PROGRAM_ID,
1542 LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
1543 CREATION_DATE, CREATED_BY,
1544 PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE
1545 )
1546 SELECT
1547 p_org_id AS ORGANIZATION_ID, pre_cal.DEPARTMENT_ID, NULL AS INVENTORY_ITEM_ID,
1548 NULL AS WIP_ENTITY_ID, NULL AS OPERATION_SEQ_NUM,
1549 NULL AS SHIFT_NUM, pre_cal.SHIFT_DATE,
1550 NULL AS QUANTITY_REJECTED, NULL AS QUANTITY_SCRAPPED, NULL AS QUANTITY_COMPLETED,
1551 pre_cal.SCRAP_PERCENT, pre_cal.REJECT_PERCENT, pre_cal.FPY_PERCENT,
1552 g_request_id as REQUEST_ID, g_prog_id as PROGRAM_ID,
1553 p_execution_date as LAST_UPDATE_DATE, g_user_id as LAST_UPDATED_BY, g_login_id as LAST_UPDATE_LOGIN,
1554 p_execution_date as CREATION_DATE, g_user_id as CREATED_BY,
1555 g_prog_appid as PROGRAM_APPLICATION_ID, p_execution_date as PROGRAM_UPDATE_DATE
1556 FROM
1557 ( SELECT TRUNC(wwf.SHIFT_DATE) as SHIFT_DATE,wwf.DEPARTMENT_ID,
1558 (case when (sum(wwf.QUANTITY_COMPLETED) = 0) then 0 else round(100*sum(wwf.QUANTITY_SCRAPPED)/sum(wwf.QUANTITY_COMPLETED),2) end) as SCRAP_PERCENT,
1559 (case when (sum(wwf.QUANTITY_COMPLETED) = 0) then 0 else round(100*sum(wwf.QUANTITY_REJECTED)/sum(wwf.QUANTITY_COMPLETED),2) end) as REJECT_PERCENT,
1560 (case when (sum(wwf.QUANTITY_COMPLETED) = 0) then 0 else round(100*(sum(wwf.QUANTITY_COMPLETED)-sum(QUANTITY_SCRAPPED+QUANTITY_REJECTED))/sum(wwf.QUANTITY_COMPLETED),2) end) as FPY_PERCENT
1561 FROM WIP_WS_FPY wwf
1562 WHERE organization_id =p_org_id
1563 AND TRUNC(wwf.shift_date) >= TRUNC(p_cutoff_date)
1564 AND wwf.SHIFT_NUM IS NOT NULL AND wwf.SHIFT_DATE IS NOT NULL
1565 AND wwf.DEPARTMENT_ID IS NOT NULL AND wwf.INVENTORY_ITEM_ID IS NOT NULL
1566 AND wwf.WIP_ENTITY_ID IS NOT NULL AND wwf.OPERATION_SEQ_NUM IS NOT NULL
1567 GROUP BY TRUNC(wwf.SHIFT_DATE),wwf.DEPARTMENT_ID
1568 ) pre_cal;
1569
1570 x_return_status := FND_API.G_RET_STS_SUCCESS;
1571
1572 EXCEPTION
1573 WHEN others THEN
1574 exception_log(l_proc_name,p_return_status => x_return_status);
1575 END calc_fpy_per_dept_day;
1576
1577
1578
1579
1580 PROCEDURE calc_fpy_per_dept_week_shift(
1581 p_execution_date DATE,
1582 p_cutoff_date DATE,
1583 p_org_id NUMBER,
1584 x_return_status OUT NOCOPY VARCHAR2) IS
1585
1586 l_return_status VARCHAR2(1);
1587 l_proc_name VARCHAR2(60) :='wip_ws_embedded_analytics_pk.calc_fpy_per_dept_week_shift';
1588 BEGIN
1589
1590 begin_log (p_params => set_calc_param(p_execution_date => p_execution_date,p_cutoff_date => p_cutoff_date,p_org_id => p_org_id),
1591 p_proc_name => l_proc_name,
1592 p_return_status => l_return_status);
1593
1594 INSERT INTO wip_ws_fpy (
1595 ORGANIZATION_ID, DEPARTMENT_ID, INVENTORY_ITEM_ID,
1596 WIP_ENTITY_ID, OPERATION_SEQ_NUM,
1597 SHIFT_NUM, SHIFT_DATE,
1598 QUANTITY_REJECTED, QUANTITY_SCRAPPED, QUANTITY_COMPLETED,
1599 SCRAP_PERCENT, REJECT_PERCENT, FPY_PERCENT,
1600 REQUEST_ID, PROGRAM_ID,
1601 LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
1602 CREATION_DATE, CREATED_BY,
1603 PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE
1604 )
1605 SELECT
1606 p_org_id AS ORGANIZATION_ID, pre_cal.DEPARTMENT_ID, NULL AS INVENTORY_ITEM_ID,
1607 NULL AS WIP_ENTITY_ID, NULL AS OPERATION_SEQ_NUM,
1608 pre_cal.SHIFT_NUM, NULL AS SHIFT_DATE,
1609 NULL AS QUANTITY_REJECTED, NULL AS QUANTITY_SCRAPPED, NULL AS QUANTITY_COMPLETED,
1610 pre_cal.SCRAP_PERCENT, pre_cal.REJECT_PERCENT, pre_cal.FPY_PERCENT,
1611 g_request_id as REQUEST_ID, g_prog_id as PROGRAM_ID,
1612 p_execution_date as LAST_UPDATE_DATE, g_user_id as LAST_UPDATED_BY, g_login_id as LAST_UPDATE_LOGIN,
1613 p_execution_date as CREATION_DATE, g_user_id as CREATED_BY,
1614 g_prog_appid as PROGRAM_APPLICATION_ID, p_execution_date as PROGRAM_UPDATE_DATE
1615 FROM
1616 ( SELECT wwf.DEPARTMENT_ID,wwf.SHIFT_NUM,
1617 (case when (sum(wwf.QUANTITY_COMPLETED) = 0) then 0 else round(100*sum(wwf.QUANTITY_SCRAPPED)/sum(wwf.QUANTITY_COMPLETED),2) end) as SCRAP_PERCENT,
1618 (case when (sum(wwf.QUANTITY_COMPLETED) = 0) then 0 else round(100*sum(wwf.QUANTITY_REJECTED)/sum(wwf.QUANTITY_COMPLETED),2) end) as REJECT_PERCENT,
1619 (case when (sum(wwf.QUANTITY_COMPLETED) = 0) then 0 else round(100*(sum(wwf.QUANTITY_COMPLETED)-sum(QUANTITY_SCRAPPED+QUANTITY_REJECTED))/sum(wwf.QUANTITY_COMPLETED),2) end) as FPY_PERCENT
1620 FROM WIP_WS_FPY wwf
1621 WHERE organization_id =p_org_id
1622 AND wwf.shift_date >= TRUNC(p_execution_date)-6
1623 AND wwf.SHIFT_NUM IS NOT NULL AND wwf.SHIFT_DATE IS NOT NULL
1624 AND wwf.DEPARTMENT_ID IS NOT NULL AND wwf.INVENTORY_ITEM_ID IS NOT NULL
1625 AND wwf.WIP_ENTITY_ID IS NOT NULL AND wwf.OPERATION_SEQ_NUM IS NOT NULL
1626 GROUP BY wwf.DEPARTMENT_ID,wwf.SHIFT_NUM
1627 ) pre_cal;
1628
1629 x_return_status := FND_API.G_RET_STS_SUCCESS;
1630
1631 EXCEPTION
1632 WHEN others THEN
1633 exception_log(l_proc_name,p_return_status => x_return_status);
1634 END calc_fpy_per_dept_week_shift;
1635
1636
1637 PROCEDURE calc_fpy_per_dept_week(
1638 p_execution_date DATE,
1639 p_cutoff_date DATE,
1640 p_org_id NUMBER,
1641 x_return_status OUT NOCOPY VARCHAR2) IS
1642
1643 l_return_status VARCHAR2(1);
1644 l_proc_name VARCHAR2(60) :='wip_ws_embedded_analytics_pk.calc_fpy_per_dept_week';
1645 BEGIN
1646
1647 begin_log (p_params => set_calc_param(p_execution_date => p_execution_date,p_cutoff_date => p_cutoff_date,p_org_id => p_org_id),
1648 p_proc_name => l_proc_name,
1649 p_return_status => l_return_status);
1650
1651 INSERT INTO wip_ws_fpy (
1652 ORGANIZATION_ID, DEPARTMENT_ID, INVENTORY_ITEM_ID,
1653 WIP_ENTITY_ID, OPERATION_SEQ_NUM,
1654 SHIFT_NUM, SHIFT_DATE,
1655 QUANTITY_REJECTED, QUANTITY_SCRAPPED, QUANTITY_COMPLETED,
1656 SCRAP_PERCENT, REJECT_PERCENT, FPY_PERCENT,
1657 REQUEST_ID, PROGRAM_ID,
1658 LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
1659 CREATION_DATE, CREATED_BY,
1660 PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE
1661 )
1662 SELECT
1663 p_org_id AS ORGANIZATION_ID, pre_cal.DEPARTMENT_ID, NULL AS INVENTORY_ITEM_ID,
1664 NULL AS WIP_ENTITY_ID, NULL AS OPERATION_SEQ_NUM,
1665 NULL AS SHIFT_NUM, NULL AS SHIFT_DATE,
1666 NULL AS QUANTITY_REJECTED, NULL AS QUANTITY_SCRAPPED, NULL AS QUANTITY_COMPLETED,
1667 pre_cal.SCRAP_PERCENT, pre_cal.REJECT_PERCENT, pre_cal.FPY_PERCENT,
1668 g_request_id as REQUEST_ID, g_prog_id as PROGRAM_ID,
1669 p_execution_date as LAST_UPDATE_DATE, g_user_id as LAST_UPDATED_BY, g_login_id as LAST_UPDATE_LOGIN,
1670 p_execution_date as CREATION_DATE, g_user_id as CREATED_BY,
1671 g_prog_appid as PROGRAM_APPLICATION_ID, p_execution_date as PROGRAM_UPDATE_DATE
1672 FROM
1673 ( SELECT wwf.DEPARTMENT_ID,
1674 (case when (sum(wwf.QUANTITY_COMPLETED) = 0) then 0 else round(100*sum(wwf.QUANTITY_SCRAPPED)/sum(wwf.QUANTITY_COMPLETED),2) end) as SCRAP_PERCENT,
1675 (case when (sum(wwf.QUANTITY_COMPLETED) = 0) then 0 else round(100*sum(wwf.QUANTITY_REJECTED)/sum(wwf.QUANTITY_COMPLETED),2) end) as REJECT_PERCENT,
1676 (case when (sum(wwf.QUANTITY_COMPLETED) = 0) then 0 else round(100*(sum(wwf.QUANTITY_COMPLETED)-sum(QUANTITY_SCRAPPED+QUANTITY_REJECTED))/sum(wwf.QUANTITY_COMPLETED),2) end) as FPY_PERCENT
1677 FROM WIP_WS_FPY wwf
1678 WHERE organization_id =p_org_id
1679 AND wwf.shift_date >= TRUNC(p_execution_date)-6
1680 AND wwf.SHIFT_NUM IS NOT NULL AND wwf.SHIFT_DATE IS NOT NULL
1681 AND wwf.DEPARTMENT_ID IS NOT NULL AND wwf.INVENTORY_ITEM_ID IS NOT NULL
1682 AND wwf.WIP_ENTITY_ID IS NOT NULL AND wwf.OPERATION_SEQ_NUM IS NOT NULL
1683 GROUP BY wwf.DEPARTMENT_ID
1684 ) pre_cal;
1685
1686 x_return_status := FND_API.G_RET_STS_SUCCESS;
1687
1688 EXCEPTION
1689 WHEN others THEN
1690 exception_log(l_proc_name,p_return_status => x_return_status);
1691 END calc_fpy_per_dept_week;
1692
1693
1694 PROCEDURE calc_fpy_all_depts_day_shift(
1695 p_execution_date DATE,
1696 p_cutoff_date DATE,
1697 p_org_id NUMBER,
1698 x_return_status OUT NOCOPY VARCHAR2) IS
1699
1700 l_return_status VARCHAR2(1);
1701 l_proc_name VARCHAR2(60) :='wip_ws_embedded_analytics_pk.calc_fpy_all_depts_day_shift';
1702 BEGIN
1703
1704 begin_log (p_params => set_calc_param(p_execution_date => p_execution_date,p_cutoff_date => p_cutoff_date,p_org_id => p_org_id),
1705 p_proc_name => l_proc_name,
1706 p_return_status => l_return_status);
1707
1708 INSERT INTO wip_ws_fpy (
1709 ORGANIZATION_ID, DEPARTMENT_ID, INVENTORY_ITEM_ID,
1710 WIP_ENTITY_ID, OPERATION_SEQ_NUM,
1711 SHIFT_NUM, SHIFT_DATE,
1712 QUANTITY_REJECTED, QUANTITY_SCRAPPED, QUANTITY_COMPLETED,
1713 SCRAP_PERCENT, REJECT_PERCENT, FPY_PERCENT,
1714 REQUEST_ID, PROGRAM_ID,
1715 LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
1716 CREATION_DATE, CREATED_BY,
1717 PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE
1718 )
1719 SELECT
1720 p_org_id AS ORGANIZATION_ID, NULL as DEPARTMENT_ID, NULL AS INVENTORY_ITEM_ID,
1721 NULL AS WIP_ENTITY_ID, NULL AS OPERATION_SEQ_NUM,
1722 pre_cal.SHIFT_NUM, pre_cal.SHIFT_DATE,
1723 NULL AS QUANTITY_REJECTED, NULL AS QUANTITY_SCRAPPED, NULL AS QUANTITY_COMPLETED,
1724 (case when (pre_cal.FPY_PERCENT < 0) then 0 else pre_cal.SCRAP_PERCENT end) as SCRAP_PERCENT,
1725 (case when (pre_cal.FPY_PERCENT < 0) then 0 else pre_cal.REJECT_PERCENT end) as REJECT_PERCENT,
1726 (case when (pre_cal.FPY_PERCENT < 0) then 0 else pre_cal.FPY_PERCENT end) as FPY_PERCENT,
1727 g_request_id as REQUEST_ID, g_prog_id as PROGRAM_ID,
1728 p_execution_date as LAST_UPDATE_DATE, g_user_id as LAST_UPDATED_BY, g_login_id as LAST_UPDATE_LOGIN,
1729 p_execution_date as CREATION_DATE, g_user_id as CREATED_BY,
1730 g_prog_appid as PROGRAM_APPLICATION_ID, p_execution_date as PROGRAM_UPDATE_DATE
1731 FROM
1732 ( SELECT TRUNC(wwf.SHIFT_DATE) as SHIFT_DATE ,wwf.SHIFT_NUM,
1733 (case when (sum(wwf.QUANTITY_COMPLETED) = 0) then 0 else round(100*sum(wwf.QUANTITY_SCRAPPED)/sum(wwf.QUANTITY_COMPLETED),2) end) as SCRAP_PERCENT,
1734 (case when (sum(wwf.QUANTITY_COMPLETED) = 0) then 0 else round(100*sum(wwf.QUANTITY_REJECTED)/sum(wwf.QUANTITY_COMPLETED),2) end) as REJECT_PERCENT,
1735 (case when (sum(wwf.QUANTITY_COMPLETED) = 0) then 0 else round(100*(sum(wwf.QUANTITY_COMPLETED)-sum(QUANTITY_SCRAPPED+QUANTITY_REJECTED))/sum(wwf.QUANTITY_COMPLETED),2) end) as FPY_PERCENT
1736 FROM WIP_WS_FPY wwf
1737 WHERE organization_id =p_org_id
1738 AND TRUNC(wwf.shift_date) >= TRUNC(p_cutoff_date)
1739 AND wwf.SHIFT_NUM IS NOT NULL AND wwf.SHIFT_DATE IS NOT NULL
1740 AND wwf.DEPARTMENT_ID IS NOT NULL AND wwf.INVENTORY_ITEM_ID IS NOT NULL
1741 AND wwf.WIP_ENTITY_ID IS NOT NULL AND wwf.OPERATION_SEQ_NUM IS NOT NULL
1742 GROUP BY TRUNC(wwf.SHIFT_DATE),wwf.SHIFT_NUM
1743 ) pre_cal;
1744
1745 x_return_status := FND_API.G_RET_STS_SUCCESS;
1746
1747 EXCEPTION
1748 WHEN others THEN
1749 exception_log(l_proc_name,p_return_status => x_return_status);
1750 END calc_fpy_all_depts_day_shift;
1751
1752
1753 PROCEDURE calc_fpy_all_depts_day(
1754 p_execution_date DATE,
1755 p_cutoff_date DATE,
1756 p_org_id NUMBER,
1757 x_return_status OUT NOCOPY VARCHAR2) IS
1758
1759 l_return_status VARCHAR2(1);
1760 l_proc_name VARCHAR2(60) :='wip_ws_embedded_analytics_pk.calc_fpy_all_depts_day';
1761 BEGIN
1762
1763 begin_log (p_params => set_calc_param(p_execution_date => p_execution_date,p_cutoff_date => p_cutoff_date,p_org_id => p_org_id),
1764 p_proc_name => l_proc_name,
1765 p_return_status => l_return_status);
1766
1767 INSERT INTO wip_ws_fpy (
1768 ORGANIZATION_ID, DEPARTMENT_ID, INVENTORY_ITEM_ID,
1769 WIP_ENTITY_ID, OPERATION_SEQ_NUM,
1770 SHIFT_NUM, SHIFT_DATE,
1771 QUANTITY_REJECTED, QUANTITY_SCRAPPED, QUANTITY_COMPLETED,
1772 SCRAP_PERCENT, REJECT_PERCENT, FPY_PERCENT,
1773 REQUEST_ID, PROGRAM_ID,
1774 LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
1775 CREATION_DATE, CREATED_BY,
1776 PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE
1777 )
1778 SELECT
1779 p_org_id AS ORGANIZATION_ID, NULL as DEPARTMENT_ID, NULL AS INVENTORY_ITEM_ID,
1780 NULL AS WIP_ENTITY_ID, NULL AS OPERATION_SEQ_NUM,
1781 NULL AS SHIFT_NUM, pre_cal.SHIFT_DATE,
1782 NULL AS QUANTITY_REJECTED, NULL AS QUANTITY_SCRAPPED, NULL AS QUANTITY_COMPLETED,
1783 (case when (pre_cal.FPY_PERCENT < 0) then 0 else pre_cal.SCRAP_PERCENT end) as SCRAP_PERCENT,
1784 (case when (pre_cal.FPY_PERCENT < 0) then 0 else pre_cal.REJECT_PERCENT end) as REJECT_PERCENT,
1785 (case when (pre_cal.FPY_PERCENT < 0) then 0 else pre_cal.FPY_PERCENT end) as FPY_PERCENT,
1786 g_request_id as REQUEST_ID, g_prog_id as PROGRAM_ID,
1787 p_execution_date as LAST_UPDATE_DATE, g_user_id as LAST_UPDATED_BY, g_login_id as LAST_UPDATE_LOGIN,
1788 p_execution_date as CREATION_DATE, g_user_id as CREATED_BY,
1789 g_prog_appid as PROGRAM_APPLICATION_ID, p_execution_date as PROGRAM_UPDATE_DATE
1790 FROM
1791 ( SELECT TRUNC(wwf.SHIFT_DATE) as SHIFT_DATE,
1792 (case when (sum(wwf.QUANTITY_COMPLETED) = 0) then 0 else round(100*sum(wwf.QUANTITY_SCRAPPED)/sum(wwf.QUANTITY_COMPLETED),2) end) as SCRAP_PERCENT,
1793 (case when (sum(wwf.QUANTITY_COMPLETED) = 0) then 0 else round(100*sum(wwf.QUANTITY_REJECTED)/sum(wwf.QUANTITY_COMPLETED),2) end) as REJECT_PERCENT,
1794 (case when (sum(wwf.QUANTITY_COMPLETED) = 0) then 0 else round(100*(sum(wwf.QUANTITY_COMPLETED)-sum(QUANTITY_SCRAPPED+QUANTITY_REJECTED))/sum(wwf.QUANTITY_COMPLETED),2) end) as FPY_PERCENT
1795 FROM WIP_WS_FPY wwf
1796 WHERE organization_id =p_org_id
1797 AND TRUNC(wwf.shift_date) >= TRUNC(p_cutoff_date)
1798 AND wwf.SHIFT_NUM IS NOT NULL AND wwf.SHIFT_DATE IS NOT NULL
1799 AND wwf.DEPARTMENT_ID IS NOT NULL AND wwf.INVENTORY_ITEM_ID IS NOT NULL
1800 AND wwf.WIP_ENTITY_ID IS NOT NULL AND wwf.OPERATION_SEQ_NUM IS NOT NULL
1801 GROUP BY TRUNC(wwf.SHIFT_DATE)
1802 ) pre_cal;
1803
1804 x_return_status := FND_API.G_RET_STS_SUCCESS;
1805
1806 EXCEPTION
1807 WHEN others THEN
1808 exception_log(l_proc_name,p_return_status => x_return_status);
1809 END calc_fpy_all_depts_day;
1810
1811
1812
1813
1814 PROCEDURE calc_fpy_all_depts_week_shift(
1815 p_execution_date DATE,
1816 p_cutoff_date DATE,
1817 p_org_id NUMBER,
1818 x_return_status OUT NOCOPY VARCHAR2) IS
1819
1820 l_return_status VARCHAR2(1);
1821 l_proc_name VARCHAR2(60) :='wip_ws_embedded_analytics_pk.calc_fpy_all_depts_week_shift';
1822 BEGIN
1823
1824 begin_log (p_params => set_calc_param(p_execution_date => p_execution_date,p_cutoff_date => p_cutoff_date,p_org_id => p_org_id),
1825 p_proc_name => l_proc_name,
1826 p_return_status => l_return_status);
1827
1828 INSERT INTO wip_ws_fpy (
1829 ORGANIZATION_ID, DEPARTMENT_ID, INVENTORY_ITEM_ID,
1830 WIP_ENTITY_ID, OPERATION_SEQ_NUM,
1831 SHIFT_NUM, SHIFT_DATE,
1832 QUANTITY_REJECTED, QUANTITY_SCRAPPED, QUANTITY_COMPLETED,
1833 SCRAP_PERCENT, REJECT_PERCENT, FPY_PERCENT,
1834 REQUEST_ID, PROGRAM_ID,
1835 LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
1836 CREATION_DATE, CREATED_BY,
1837 PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE
1838 )
1839 SELECT
1840 p_org_id AS ORGANIZATION_ID, NULL as DEPARTMENT_ID, NULL AS INVENTORY_ITEM_ID,
1841 NULL AS WIP_ENTITY_ID, NULL AS OPERATION_SEQ_NUM,
1842 pre_cal.SHIFT_NUM, NULL AS SHIFT_DATE,
1843 NULL AS QUANTITY_REJECTED, NULL AS QUANTITY_SCRAPPED, NULL AS QUANTITY_COMPLETED,
1844 (case when (pre_cal.FPY_PERCENT < 0) then 0 else pre_cal.SCRAP_PERCENT end) as SCRAP_PERCENT,
1845 (case when (pre_cal.FPY_PERCENT < 0) then 0 else pre_cal.REJECT_PERCENT end) as REJECT_PERCENT,
1846 (case when (pre_cal.FPY_PERCENT < 0) then 0 else pre_cal.FPY_PERCENT end) as FPY_PERCENT,
1847 g_request_id as REQUEST_ID, g_prog_id as PROGRAM_ID,
1848 p_execution_date as LAST_UPDATE_DATE, g_user_id as LAST_UPDATED_BY, g_login_id as LAST_UPDATE_LOGIN,
1849 p_execution_date as CREATION_DATE, g_user_id as CREATED_BY,
1850 g_prog_appid as PROGRAM_APPLICATION_ID, p_execution_date as PROGRAM_UPDATE_DATE
1851 FROM
1852 ( SELECT wwf.SHIFT_NUM,
1853 (case when (sum(wwf.QUANTITY_COMPLETED) = 0) then 0 else round(100*sum(wwf.QUANTITY_SCRAPPED)/sum(wwf.QUANTITY_COMPLETED),2) end) as SCRAP_PERCENT,
1854 (case when (sum(wwf.QUANTITY_COMPLETED) = 0) then 0 else round(100*sum(wwf.QUANTITY_REJECTED)/sum(wwf.QUANTITY_COMPLETED),2) end) as REJECT_PERCENT,
1855 (case when (sum(wwf.QUANTITY_COMPLETED) = 0) then 0 else round(100*(sum(wwf.QUANTITY_COMPLETED)-sum(QUANTITY_SCRAPPED+QUANTITY_REJECTED))/sum(wwf.QUANTITY_COMPLETED),2) end) as FPY_PERCENT
1856 FROM WIP_WS_FPY wwf
1857 WHERE organization_id =p_org_id
1858 AND wwf.shift_date >= TRUNC(p_execution_date)-6
1859 AND wwf.SHIFT_NUM IS NOT NULL AND wwf.SHIFT_DATE IS NOT NULL
1860 AND wwf.DEPARTMENT_ID IS NOT NULL AND wwf.INVENTORY_ITEM_ID IS NOT NULL
1861 AND wwf.WIP_ENTITY_ID IS NOT NULL AND wwf.OPERATION_SEQ_NUM IS NOT NULL
1862 GROUP BY wwf.SHIFT_NUM
1863 ) pre_cal;
1864
1865 x_return_status := FND_API.G_RET_STS_SUCCESS;
1866
1867 EXCEPTION
1868 WHEN others THEN
1869 exception_log(l_proc_name,p_return_status => x_return_status);
1870 END calc_fpy_all_depts_week_shift;
1871
1872
1873 PROCEDURE calc_fpy_all_depts_week(
1874 p_execution_date DATE,
1875 p_cutoff_date DATE,
1876 p_org_id NUMBER,
1877 x_return_status OUT NOCOPY VARCHAR2) IS
1878
1879 l_return_status VARCHAR2(1);
1880 l_proc_name VARCHAR2(60) :='wip_ws_embedded_analytics_pk.calc_fpy_all_depts_week';
1881 BEGIN
1882
1883 begin_log (p_params => set_calc_param(p_execution_date => p_execution_date,p_cutoff_date => p_cutoff_date,p_org_id => p_org_id),
1884 p_proc_name => l_proc_name,
1885 p_return_status => l_return_status);
1886
1887 INSERT INTO wip_ws_fpy (
1888 ORGANIZATION_ID, DEPARTMENT_ID, INVENTORY_ITEM_ID,
1889 WIP_ENTITY_ID, OPERATION_SEQ_NUM,
1890 SHIFT_NUM, SHIFT_DATE,
1891 QUANTITY_REJECTED, QUANTITY_SCRAPPED, QUANTITY_COMPLETED,
1892 SCRAP_PERCENT, REJECT_PERCENT, FPY_PERCENT,
1893 REQUEST_ID, PROGRAM_ID,
1894 LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
1895 CREATION_DATE, CREATED_BY,
1896 PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE
1897 )
1898 SELECT
1899 p_org_id AS ORGANIZATION_ID, NULL as DEPARTMENT_ID, NULL AS INVENTORY_ITEM_ID,
1900 NULL AS WIP_ENTITY_ID, NULL AS OPERATION_SEQ_NUM,
1901 NULL AS SHIFT_NUM, NULL AS SHIFT_DATE,
1902 NULL AS QUANTITY_REJECTED, NULL AS QUANTITY_SCRAPPED, NULL AS QUANTITY_COMPLETED,
1903 (case when (pre_cal.FPY_PERCENT < 0) then 0 else pre_cal.SCRAP_PERCENT end) as SCRAP_PERCENT,
1904 (case when (pre_cal.FPY_PERCENT < 0) then 0 else pre_cal.REJECT_PERCENT end) as REJECT_PERCENT,
1905 (case when (pre_cal.FPY_PERCENT < 0) then 0 else pre_cal.FPY_PERCENT end) as FPY_PERCENT,
1906 g_request_id as REQUEST_ID, g_prog_id as PROGRAM_ID,
1907 p_execution_date as LAST_UPDATE_DATE, g_user_id as LAST_UPDATED_BY, g_login_id as LAST_UPDATE_LOGIN,
1908 p_execution_date as CREATION_DATE, g_user_id as CREATED_BY,
1909 g_prog_appid as PROGRAM_APPLICATION_ID, p_execution_date as PROGRAM_UPDATE_DATE
1910 FROM
1911 ( SELECT
1912 (case when (sum(wwf.QUANTITY_COMPLETED) = 0) then 0 else round(100*sum(wwf.QUANTITY_SCRAPPED)/sum(wwf.QUANTITY_COMPLETED),2) end) as SCRAP_PERCENT,
1913 (case when (sum(wwf.QUANTITY_COMPLETED) = 0) then 0 else round(100*sum(wwf.QUANTITY_REJECTED)/sum(wwf.QUANTITY_COMPLETED),2) end) as REJECT_PERCENT,
1914 (case when (sum(wwf.QUANTITY_COMPLETED) = 0) then 0 else round(100*(sum(wwf.QUANTITY_COMPLETED)-sum(QUANTITY_SCRAPPED+QUANTITY_REJECTED))/sum(wwf.QUANTITY_COMPLETED),2) end) as FPY_PERCENT
1915 FROM WIP_WS_FPY wwf
1916 WHERE organization_id =p_org_id
1917 AND wwf.shift_date >= TRUNC(p_execution_date)-6
1918 AND wwf.SHIFT_NUM IS NOT NULL AND wwf.SHIFT_DATE IS NOT NULL
1919 AND wwf.DEPARTMENT_ID IS NOT NULL AND wwf.INVENTORY_ITEM_ID IS NOT NULL
1920 AND wwf.WIP_ENTITY_ID IS NOT NULL AND wwf.OPERATION_SEQ_NUM IS NOT NULL
1921
1922 ) pre_cal;
1923
1924 x_return_status := FND_API.G_RET_STS_SUCCESS;
1925
1926 EXCEPTION
1927 WHEN others THEN
1928 exception_log(l_proc_name,p_return_status => x_return_status);
1929 END calc_fpy_all_depts_week;
1930
1931
1932 PROCEDURE calc_fpy_for_jobop_all(
1933 p_execution_date DATE,
1934 p_cutoff_date DATE,
1935 p_org_id NUMBER,
1936 x_return_status OUT NOCOPY VARCHAR2) IS
1937
1938 l_proc_name VARCHAR2(60) :='wip_ws_embedded_analytics_pk.calc_fpy_for_jobop_all';
1939 BEGIN
1940
1941 begin_log (p_params => set_calc_param(p_execution_date => p_execution_date,p_cutoff_date => p_cutoff_date,p_org_id => p_org_id),
1942 p_proc_name => l_proc_name,
1943 p_return_status => x_return_status);
1944
1945
1946 populate_fpy_raw_data(
1947 p_execution_date => p_execution_date,
1948 p_cutoff_date => p_cutoff_date,
1949 p_org_id => p_org_id,
1950 x_return_status => x_return_status);
1951
1952 calc_fpy_per_jobop_day_shift(
1953 p_execution_date => p_execution_date,
1954 p_cutoff_date => p_cutoff_date,
1955 p_org_id => p_org_id,
1956 x_return_status => x_return_status);
1957
1958 calc_fpy_per_jobop_day(
1959 p_execution_date => p_execution_date,
1960 p_cutoff_date => p_cutoff_date,
1961 p_org_id => p_org_id,
1962 x_return_status => x_return_status);
1963
1964 calc_fpy_per_jobop_week(
1965 p_execution_date => p_execution_date,
1966 p_cutoff_date => p_cutoff_date,
1967 p_org_id => p_org_id,
1968 x_return_status => x_return_status);
1969
1970 calc_fpy_per_jobop_week_shift(
1971 p_execution_date => p_execution_date,
1972 p_cutoff_date => p_cutoff_date,
1973 p_org_id => p_org_id,
1974 x_return_status => x_return_status);
1975
1976
1977 x_return_status := FND_API.G_RET_STS_SUCCESS;
1978
1979
1980 EXCEPTION
1981 WHEN others THEN
1982 exception_log(l_proc_name,p_return_status => x_return_status);
1983 END calc_fpy_for_jobop_all;
1984
1985
1986 PROCEDURE calc_fpy_for_job_all(
1987 p_execution_date DATE,
1988 p_cutoff_date DATE,
1989 p_org_id NUMBER,
1990 x_return_status OUT NOCOPY VARCHAR2) IS
1991
1992 l_proc_name VARCHAR2(60) :='wip_ws_embedded_analytics_pk.calc_fpy_for_job_all';
1993 BEGIN
1994
1995 begin_log (p_params => set_calc_param(p_execution_date => p_execution_date,p_cutoff_date => p_cutoff_date,p_org_id => p_org_id),
1996 p_proc_name => l_proc_name,
1997 p_return_status => x_return_status);
1998
1999
2000 calc_fpy_per_job_day(
2001 p_execution_date => p_execution_date,
2002 p_cutoff_date => p_cutoff_date,
2003 p_org_id => p_org_id,
2004 x_return_status => x_return_status);
2005
2006 calc_fpy_per_job_day_shift(
2007 p_execution_date => p_execution_date,
2008 p_cutoff_date => p_cutoff_date,
2009 p_org_id => p_org_id,
2010 x_return_status => x_return_status);
2011
2012 calc_fpy_per_job_week(
2013 p_execution_date => p_execution_date,
2014 p_cutoff_date => p_cutoff_date,
2015 p_org_id => p_org_id,
2016 x_return_status => x_return_status);
2017
2018 calc_fpy_per_job_week_shift(
2019 p_execution_date => p_execution_date,
2020 p_cutoff_date => p_cutoff_date,
2021 p_org_id => p_org_id,
2022 x_return_status => x_return_status);
2023
2024
2025 x_return_status := FND_API.G_RET_STS_SUCCESS;
2026
2027
2028 EXCEPTION
2029 WHEN others THEN
2030 exception_log(l_proc_name,p_return_status => x_return_status);
2031 END calc_fpy_for_job_all;
2032
2033
2034 PROCEDURE calc_fpy_for_assm_all(
2035 p_execution_date DATE,
2036 p_cutoff_date DATE,
2037 p_org_id NUMBER,
2038 x_return_status OUT NOCOPY VARCHAR2) IS
2039
2040 l_proc_name VARCHAR2(60) :='wip_ws_embedded_analytics_pk.calc_fpy_for_assm_all';
2041 BEGIN
2042
2043 begin_log (p_params => set_calc_param(p_execution_date => p_execution_date,p_cutoff_date => p_cutoff_date,p_org_id => p_org_id),
2044 p_proc_name => l_proc_name,
2045 p_return_status => x_return_status);
2046
2047
2048 calc_fpy_per_assm_day_shift(
2049 p_execution_date => p_execution_date,
2050 p_cutoff_date => p_cutoff_date,
2051 p_org_id => p_org_id,
2052 x_return_status => x_return_status);
2053
2054 calc_fpy_per_assm_day(
2055 p_execution_date => p_execution_date,
2056 p_cutoff_date => p_cutoff_date,
2057 p_org_id => p_org_id,
2058 x_return_status => x_return_status);
2059
2060 calc_fpy_per_assm_week_shift(
2061 p_execution_date => p_execution_date,
2062 p_cutoff_date => p_cutoff_date,
2063 p_org_id => p_org_id,
2064 x_return_status => x_return_status);
2065
2066 calc_fpy_per_assm_week(
2067 p_execution_date => p_execution_date,
2068 p_cutoff_date => p_cutoff_date,
2069 p_org_id => p_org_id,
2070 x_return_status => x_return_status);
2071
2072
2073 x_return_status := FND_API.G_RET_STS_SUCCESS;
2074
2075
2076 EXCEPTION
2077 WHEN others THEN
2078 exception_log(l_proc_name,p_return_status => x_return_status);
2079 END calc_fpy_for_assm_all;
2080
2081
2082 PROCEDURE calc_fpy_for_dept_all(
2083 p_execution_date DATE,
2084 p_cutoff_date DATE,
2085 p_org_id NUMBER,
2086 x_return_status OUT NOCOPY VARCHAR2) IS
2087
2088 l_proc_name VARCHAR2(60) :='wip_ws_embedded_analytics_pk.calc_fpy_for_dept_all';
2089
2090 BEGIN
2091
2092 begin_log (p_params => set_calc_param(p_execution_date => p_execution_date,p_cutoff_date => p_cutoff_date,p_org_id => p_org_id),
2093 p_proc_name => l_proc_name,
2094 p_return_status => x_return_status);
2095
2096 calc_fpy_per_dept_day_shift(
2097 p_execution_date => p_execution_date,
2098 p_cutoff_date => p_cutoff_date,
2099 p_org_id => p_org_id,
2100 x_return_status => x_return_status);
2101
2102 calc_fpy_per_dept_day(
2103 p_execution_date => p_execution_date,
2104 p_cutoff_date => p_cutoff_date,
2105 p_org_id => p_org_id,
2106 x_return_status => x_return_status);
2107
2108 calc_fpy_per_dept_week_shift(
2109 p_execution_date => p_execution_date,
2110 p_cutoff_date => p_cutoff_date,
2111 p_org_id => p_org_id,
2112 x_return_status => x_return_status);
2113
2114 calc_fpy_per_dept_week(
2115 p_execution_date => p_execution_date,
2116 p_cutoff_date => p_cutoff_date,
2117 p_org_id => p_org_id,
2118 x_return_status => x_return_status);
2119
2120
2121 x_return_status := FND_API.G_RET_STS_SUCCESS;
2122
2123
2124 EXCEPTION
2125 WHEN others THEN
2126 exception_log(l_proc_name,p_return_status => x_return_status);
2127 END calc_fpy_for_dept_all;
2128
2129
2130 PROCEDURE calc_fpy_for_all_depts_all(
2131 p_execution_date DATE,
2132 p_cutoff_date DATE,
2133 p_org_id NUMBER,
2134 x_return_status OUT NOCOPY VARCHAR2) IS
2135
2136 l_proc_name VARCHAR2(60) :='wip_ws_embedded_analytics_pk.calc_fpy_for_all_depts_all';
2137 BEGIN
2138
2139 begin_log (p_params => set_calc_param(p_execution_date => p_execution_date,p_cutoff_date => p_cutoff_date,p_org_id => p_org_id),
2140 p_proc_name => l_proc_name,
2141 p_return_status => x_return_status);
2142
2143
2144 calc_fpy_all_depts_day_shift(
2145 p_execution_date => p_execution_date,
2146 p_cutoff_date => p_cutoff_date,
2147 p_org_id => p_org_id,
2148 x_return_status => x_return_status);
2149
2150 calc_fpy_all_depts_day(
2151 p_execution_date => p_execution_date,
2152 p_cutoff_date => p_cutoff_date,
2153 p_org_id => p_org_id,
2154 x_return_status => x_return_status);
2155
2156 calc_fpy_all_depts_week_shift(
2157 p_execution_date => p_execution_date,
2158 p_cutoff_date => p_cutoff_date,
2159 p_org_id => p_org_id,
2160 x_return_status => x_return_status);
2161
2162 calc_fpy_all_depts_week(
2163 p_execution_date => p_execution_date,
2164 p_cutoff_date => p_cutoff_date,
2165 p_org_id => p_org_id,
2166 x_return_status => x_return_status);
2167
2168 x_return_status := FND_API.G_RET_STS_SUCCESS;
2169
2170
2171 EXCEPTION
2172 WHEN others THEN
2173 exception_log(l_proc_name,p_return_status => x_return_status);
2174 END calc_fpy_for_all_depts_all;
2175
2176
2177 FUNCTION get_start_shift_date_to_calc(p_org_id IN NUMBER,
2178 p_department_id IN NUMBER,
2179 p_execution_date IN DATE)
2180 RETURN DATE
2181 IS
2182 l_last_shift_date DATE;
2183 l_second_last_shift_date DATE;
2184 l_retention_boundary DATE;
2185 l_start_move_tran_date_to_calc DATE;
2186 l_start_shift_date_to_calc DATE;
2187 l_shift_seq NUMBER;
2188 l_shift_num NUMBER;
2189 l_shift_start_date DATE;
2190 l_shift_end_date DATE;
2191 l_shift_string VARCHAR2(100);
2192 l_calc_start_date DATE;
2193 l_last_calculation_date DATE;
2194 l_department_id NUMBER;
2195
2196 l_params wip_logger.param_tbl_t;
2197 l_return_status VARCHAR2(1);
2198 l_proc_name VARCHAR2(60) :='wip_ws_embedded_analytics_pk.get_start_shift_date_to_calc';
2199 BEGIN
2200
2201 l_params(1).paramName := 'p_org_id';
2202 l_params(1).paramValue := p_org_id;
2203 l_params(2).paramName := 'p_execution_date';
2204 l_params(2).paramValue := p_execution_date;
2205 begin_log (p_params => l_params,p_proc_name => l_proc_name,p_return_status => l_return_status);
2206
2207 l_retention_boundary := TRUNC(p_execution_date - g_fpy_data_retention);
2208
2209
2210 BEGIN
2211 SELECT max(shift_date) INTO l_last_shift_date
2212 FROM wip_ws_fpy wwf
2213 WHERE organization_id = p_org_id
2214 AND wwf.operation_seq_num is NOT NULL
2215 AND wwf.wip_entity_id is NOT NULL
2216 AND wwf.inventory_item_id is NOT NULL
2217 AND wwf.department_id is NOT NULL
2218 AND wwf.shift_date is NOT NULL
2219 AND wwf.shift_num is NOT NULL;
2220
2221 IF l_last_shift_date is NULL THEN
2222 l_second_last_shift_date := l_last_shift_date;
2223 ELSE
2224 SELECT max(shift_date) INTO l_second_last_shift_date
2225 FROM wip_ws_fpy wwf
2226 WHERE organization_id = p_org_id
2227 AND shift_date < l_last_shift_date
2228 AND wwf.operation_seq_num is NOT NULL
2229 AND wwf.wip_entity_id is NOT NULL
2230 AND wwf.inventory_item_id is NOT NULL
2231 AND wwf.department_id is NOT NULL
2232 AND wwf.shift_date is NOT NULL
2233 AND wwf.shift_num is NOT NULL;
2234 IF l_second_last_shift_date is NULL THEN
2235 l_second_last_shift_date := l_last_shift_date;
2236 END IF;
2237 END IF;
2238
2239 IF l_second_last_shift_date > l_retention_boundary THEN
2240 l_start_shift_date_to_calc := l_second_last_shift_date;
2241 ELSE
2242 l_start_shift_date_to_calc := l_retention_boundary;
2243 END IF;
2244 EXCEPTION
2245 WHEN NO_DATA_FOUND THEN
2246 l_start_shift_date_to_calc := l_retention_boundary;
2247 WHEN others THEN
2248 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2249 END;
2250
2251 SELECT MAX(CREATION_DATE) INTO l_last_calculation_date from WIP_WS_FPY;
2252 IF l_last_calculation_date is NULL THEN
2253 l_last_calculation_date := l_retention_boundary;
2254 ELSE
2255 IF l_last_calculation_date < l_retention_boundary THEN
2256 l_last_calculation_date := l_retention_boundary;
2257 END IF;
2258 END IF;
2259
2260 BEGIN
2261
2262
2263 l_department_id := NULL;
2264
2265 SELECT transaction_date INTO l_start_move_tran_date_to_calc
2266 FROM wip_move_transactions
2267 WHERE creation_date >= l_last_calculation_date
2268 AND organization_id = p_org_id
2269 AND ROWNUM = 1;
2270
2271 /*
2272 SELECT wmt.transaction_date, wop.department_id into l_start_move_tran_date_to_calc, l_department_id
2273 FROM wip_move_transactions wmt,
2274 wip_operations wop
2275 WHERE
2276 wmt.creation_date >= l_last_calculation_date
2277 AND wmt.organization_id = p_org_id
2278 AND wop.organization_id = p_org_id
2279 AND ROWNUM = 1
2280 AND wop.wip_entity_id = wmt.wip_entity_id
2281 AND ((wop.operation_seq_num >= wmt.fm_operation_seq_num + DECODE(SIGN(wmt.fm_intraoperation_step_type - WIP_CONSTANTS.RUN),1,1,0)
2282 AND (wop.operation_seq_num < wmt.to_operation_seq_num + DECODE(SIGN(wmt.to_intraoperation_step_type - WIP_CONSTANTS.RUN),1,1,0))
2283 AND (wmt.to_operation_seq_num > wmt.fm_operation_seq_num
2284 OR (wmt.to_operation_seq_num = wmt.fm_operation_seq_num
2285 AND wmt.fm_intraoperation_step_type <= WIP_CONSTANTS.RUN
2286 AND wmt.to_intraoperation_step_type > WIP_CONSTANTS.RUN))
2287 AND (wop.count_point_type < WIP_CONSTANTS.NO_MANUAL
2288 OR wop.operation_seq_num = wmt.fm_operation_seq_num
2289 OR (wop.operation_seq_num = wmt.to_operation_seq_num
2290 AND wmt.to_intraoperation_step_type > WIP_CONSTANTS.RUN)))
2291 OR
2292 (wop.operation_seq_num < wmt.fm_operation_seq_num + DECODE(SIGN(wmt.fm_intraoperation_step_type - WIP_CONSTANTS.RUN),1,1,0)
2293 AND (wop.operation_seq_num >= wmt.to_operation_seq_num + DECODE(SIGN(wmt.to_intraoperation_step_type - WIP_CONSTANTS.RUN),1,1,0))
2294 AND (wmt.fm_operation_seq_num > wmt.to_operation_seq_num
2295 OR (wmt.fm_operation_seq_num = wmt.to_operation_seq_num
2296 AND wmt.to_intraoperation_step_type <= WIP_CONSTANTS.RUN
2297 AND wmt.fm_intraoperation_step_type > WIP_CONSTANTS.RUN))
2298 AND (wop.count_point_type < WIP_CONSTANTS.NO_MANUAL
2299 OR (wop.operation_seq_num = wmt.to_operation_seq_num
2300 AND wop.count_point_type < WIP_CONSTANTS.NO_MANUAL )
2301 OR (wop.operation_seq_num = wmt.fm_operation_seq_num
2302 AND wmt.fm_intraoperation_step_type > WIP_CONSTANTS.RUN))));
2303 */
2304
2305
2306
2307
2308 IF l_start_move_tran_date_to_calc < l_retention_boundary THEN
2309 l_start_move_tran_date_to_calc := l_retention_boundary;
2310 l_department_id := NULL;
2311 END IF;
2312
2313 EXCEPTION
2314 WHEN NO_DATA_FOUND THEN
2315 l_start_move_tran_date_to_calc := l_retention_boundary;
2316 WHEN others THEN
2317 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2318 END;
2319
2320 wip_ws_util.retrieve_first_shift(
2321 p_org_id,
2322 p_department_id,
2323 null,
2324 l_start_move_tran_date_to_calc,
2325 l_shift_seq,
2326 l_shift_num,
2327 l_shift_start_date,
2328 l_shift_end_date,
2329 l_shift_string
2330 );
2331
2332 IF l_shift_start_date < l_start_shift_date_to_calc THEN
2333 l_calc_start_date := l_shift_start_date;
2334 ELSE
2335 l_calc_start_date := l_start_shift_date_to_calc;
2336 END IF;
2337
2338
2339 RETURN l_shift_start_date;
2340 EXCEPTION
2341 WHEN others THEN
2342 exception_log(l_proc_name,p_return_status => l_return_status);
2343 END get_start_shift_date_to_calc;
2344
2345
2346 PROCEDURE delete_old_and_replacing_data(
2347 p_calc_start_date DATE,
2348 p_retention_boundary DATE,
2349 p_org_id NUMBER,
2350 x_return_status OUT NOCOPY VARCHAR2) IS
2351
2352 l_return_status VARCHAR2(1);
2353 l_proc_name VARCHAR2(60) :='wip_ws_embedded_analytics_pk.delete_old_and_replacing_data';
2354 BEGIN
2355
2356 begin_log (p_params => set_calc_param(p_execution_date => p_calc_start_date,p_cutoff_date => p_retention_boundary,p_org_id => p_org_id),
2357 p_proc_name => l_proc_name,
2358 p_return_status => l_return_status);
2359
2360 DELETE FROM wip_ws_fpy
2361 WHERE shift_date < p_retention_boundary
2362 AND organization_id = p_org_id;
2363
2364 DELETE FROM wip_ws_fpy wwf
2365 WHERE organization_id = p_org_id
2366 AND shift_date is NULL;
2367
2368 DELETE FROM wip_ws_fpy
2369 WHERE shift_date >= p_calc_start_date
2370 AND organization_id = p_org_id;
2371
2372 DELETE FROM wip_ws_fpy
2373 WHERE TRUNC(shift_date) >= TRUNC(p_calc_start_date)
2374 AND organization_id = p_org_id
2375 AND (operation_seq_num is NULL
2376 OR wip_entity_id is NULL
2377 OR inventory_item_id is NULL
2378 OR department_id is NULL
2379 OR shift_num is NULL);
2380
2381 x_return_status := FND_API.G_RET_STS_SUCCESS;
2382
2383 EXCEPTION
2384 WHEN others THEN
2385 exception_log(l_proc_name,p_return_status => x_return_status);
2386 END delete_old_and_replacing_data;
2387
2388
2389 PROCEDURE wip_ws_fpykpi_conc_prog(
2390 errbuf OUT NOCOPY VARCHAR2,
2391 retcode OUT NOCOPY VARCHAR2,
2392 p_org_id IN NUMBER) IS
2393 /*
2394 l_msg_data VARCHAR2(1000);
2395 l_msg_count NUMBER;
2396 l_lock_status NUMBER;
2397 l_shift_start_time NUMBER;
2398 l_cal_code bom_shift_dates.calendar_code%TYPE;
2399 l_exception_set_id NUMBER;
2400 */
2401
2402 l_concurrent_execution_date DATE;
2403 l_retention_boundary DATE;
2404 l_calc_start_date DATE;
2405
2406 l_return_status VARCHAR2(1);
2407 l_params wip_logger.param_tbl_t;
2408 l_proc_name VARCHAR2(60) :='wip_ws_embedded_analytics_pk.wip_ws_fpykpi_conc_prog';
2409
2410 l_concurrent_count NUMBER;
2411 l_conc_status boolean;
2412
2413 BEGIN
2414
2415 l_concurrent_execution_date := SYSDATE;
2416 l_retention_boundary := TRUNC(l_concurrent_execution_date - g_fpy_data_retention);
2417
2418 l_params(1).paramName := 'p_org_id';
2419 l_params(1).paramValue := p_org_id;
2420 begin_log (p_params => l_params,
2421 p_proc_name => l_proc_name,
2422 p_return_status => l_return_status);
2423
2424
2425 wip_ws_util.trace_log('Org id = '||to_char(p_org_id));
2426 wip_ws_util.trace_log('Start time = '||to_char(l_concurrent_execution_date));
2427
2428 SAVEPOINT wip_ws_fpykpi_calc;
2429
2430
2431 l_concurrent_count := wip_ws_util.get_no_of_running_concurrent(
2432 p_program_application_id => fnd_global.prog_appl_id,
2433 p_concurrent_program_id => fnd_global.conc_program_id,
2434 p_org_id => p_org_id);
2435
2436 if l_concurrent_count > 1 then
2437 wip_ws_util.log_for_duplicate_concurrent (
2438 p_org_id => p_org_id,
2439 p_program_name => 'First Pass Yield KPI');
2440 l_conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', 'Errors encountered in calculation program, please check the log file.');
2441 return;
2442 end if;
2443
2444
2445 delete wip_ws_fpy where organization_id = p_org_id;
2446 l_calc_start_date := trunc(l_concurrent_execution_date-g_fpy_data_retention);
2447
2448 calc_fpy_for_jobop_all(
2449 p_execution_date => l_concurrent_execution_date,
2450 p_cutoff_date => l_calc_start_date,
2451 p_org_id => p_org_id,
2452 x_return_status => l_return_status);
2453
2454 calc_fpy_for_job_all(
2455 p_execution_date => l_concurrent_execution_date,
2456 p_cutoff_date => l_calc_start_date,
2457 p_org_id => p_org_id,
2458 x_return_status => l_return_status);
2459
2460 calc_fpy_for_assm_all(
2461 p_execution_date => l_concurrent_execution_date,
2462 p_cutoff_date => l_calc_start_date,
2463 p_org_id => p_org_id,
2464 x_return_status => l_return_status);
2465
2466 calc_fpy_for_dept_all(
2467 p_execution_date => l_concurrent_execution_date,
2468 p_cutoff_date => l_calc_start_date,
2469 p_org_id => p_org_id,
2470 x_return_status => l_return_status);
2471
2472 calc_fpy_for_all_depts_all(
2473 p_execution_date => l_concurrent_execution_date,
2474 p_cutoff_date => l_calc_start_date,
2475 p_org_id => p_org_id,
2476 x_return_status => l_return_status);
2477
2478
2479 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2480 wip_ws_util.trace_log('Unexpected error occured in populate_fpy_raw_data API');
2481 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2482 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2483 wip_ws_util.trace_log('Expected error occurred in populate_fpy_raw_data API');
2484 RAISE FND_API.G_EXC_ERROR;
2485 ELSE
2486 wip_ws_util.trace_log('populate_fpy_raw_data is successfull');
2487 END IF;
2488
2489 IF (g_logLevel <= wip_constants.trace_logging) then
2490 wip_logger.exitPoint(p_procName => 'wip_ws_embedded_analytics_pk.wip_ws_fpykpi_conc_prog',
2491 p_procReturnStatus => l_return_status,
2492 p_msg => 'processed successfully',
2493 x_returnStatus => l_return_status);
2494 END IF;
2495
2496 EXCEPTION
2497 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2498 l_return_status := 2;
2499 exception_log(l_proc_name,p_return_status => l_return_status);
2500
2501 ROLLBACK TO wip_ws_fpykpi_calc;
2502 retcode := 2; -- End with error
2503 WHEN FND_API.G_EXC_ERROR THEN
2504 retcode := 1;
2505 wip_ws_util.trace_log('Came to expected error in wip_ws_fpykpi_conc_prog');
2506 ROLLBACK TO wip_ws_fpykpi_calc;
2507 WHEN others THEN
2508 wip_ws_util.trace_log('Came to others error in wip_ws_fpykpi_conc_prog');
2509 ROLLBACK TO wip_ws_fpykpi_calc;
2510 retcode := 2; -- End with error
2511 END wip_ws_fpykpi_conc_prog;
2512
2513 FUNCTION get_shift_info(p_org_id IN NUMBER,
2514 p_department_id IN NUMBER,
2515 p_transaction_date IN DATE)
2516 RETURN NUMBER
2517 IS
2518 l_shift_seq NUMBER;
2519 l_shift_num NUMBER;
2520 l_shift_start_date DATE;
2521 l_shift_end_date DATE;
2522 l_shift_string VARCHAR2(100);
2523 l_date_diff NUMBER;
2524 l_return NUMBER;
2525 BEGIN
2526 wip_ws_util.retrieve_first_shift(
2527 p_org_id => p_org_id,
2528 p_dept_id => p_department_id,
2529 p_resource_id => null,
2530 p_date => p_transaction_date,
2531 x_shift_seq => l_shift_seq,
2532 x_shift_num => l_shift_num,
2533 x_shift_start_date => l_shift_start_date,
2534 x_shift_end_date => l_shift_end_date,
2535 x_shift_string => l_shift_string
2536 );
2537
2538 l_date_diff := l_shift_start_date - p_transaction_date;
2539 l_return := (nvl(l_shift_seq,1) * 100) + nvl(l_shift_num,0);
2540
2541 if l_date_diff < 0 then
2542 l_return := (l_return - (l_date_diff/1000))*-1;
2543 else
2544 l_return := l_return + (l_date_diff/1000);
2545 end if;
2546
2547 RETURN l_return;
2548 END get_shift_info;
2549
2550
2551
2552
2553 /*
2554 Description:
2555 Given the organization, department, resource, and a timestamp,
2556 find out which shift the timestamp belongs to. It uses the existing
2557 shift definition as defined in the wip_ws_util package.
2558 Parameters:
2559 p_org_id - the organization id
2560 p_dept_id - the department id
2561 p_resource_id - the resource id
2562 p_date - the timestamp
2563 */
2564 function get_shift_info_for_date
2565 (
2566 p_org_id in number,
2567 p_dept_id in number,
2568 p_resource_id in number,
2569 p_date in date
2570 ) return varchar2
2571 is
2572 l_cal_code varchar2(30);
2573
2574 l_c_start_date date;
2575 l_c_end_date date;
2576 l_c_from_time varchar2(60);
2577 l_c_to_time varchar2(60);
2578 l_24hr_resource number;
2579 x_shift_seq number;
2580 x_shift_num number;
2581 x_shift_start_date date;
2582 x_shift_end_date date;
2583 x_shift_string varchar2(100);
2584 begin
2585 wip_ws_util.retrieve_first_shift(
2586 p_org_id => p_org_id,
2587 p_dept_id => p_dept_id,
2588 p_resource_id => p_resource_id,
2589 p_date => p_date,
2590 x_shift_seq => x_shift_seq,
2591 x_shift_num => x_shift_num,
2592 x_shift_start_date => x_shift_start_date,
2593 x_shift_end_date => x_shift_end_date,
2594 x_shift_string => x_shift_string
2595 );
2596 return (x_shift_seq || '.' ||
2597 x_shift_num || '.' ||
2598 TO_CHAR(x_shift_start_date, 'MM-DD-YYYY HH24:MI') || '.' ||
2599 TO_CHAR(x_shift_end_date, 'MM-DD-YYYY HH24:MI') || '.' ||
2600 x_shift_string || '.');
2601 end get_shift_info_for_date;
2602
2603 FUNCTION extract_shift_info(p_shift_info VARCHAR2, p_index NUMBER)
2604 RETURN VARCHAR2
2605 IS
2606 l_start_position NUMBER;
2607 l_end_position NUMBER;
2608 BEGIN
2609 IF p_index = 1 THEN
2610 l_start_position := 1;
2611 ELSE
2612 l_start_position := instr(p_shift_info, '.', 1, p_index-1) + 1;
2613 END IF;
2614 l_end_position := instr(p_shift_info, '.', 1, p_index) - 1;
2615 RETURN substrb(p_shift_info, l_start_position, (l_end_position - l_start_position) + 1);
2616 END;
2617
2618 FUNCTION get_shift_seq(p_shift_info VARCHAR2) RETURN VARCHAR2 IS
2619 BEGIN
2620 RETURN to_number(extract_shift_info(p_shift_info, 1));
2621 END get_shift_seq;
2622
2623 FUNCTION get_shift_num(p_shift_info VARCHAR2) RETURN NUMBER IS
2624 BEGIN
2625 RETURN to_number(extract_shift_info(p_shift_info, 2));
2626 END get_shift_num;
2627
2628 FUNCTION get_shift_start_date(p_shift_info VARCHAR2) RETURN DATE IS
2629 BEGIN
2630 RETURN to_date(extract_shift_info(p_shift_info, 3),'MM-DD-YYYY HH24:MI');
2631 END get_shift_start_date;
2632
2633
2634 PROCEDURE wip_ws_ppmdkpi_conc_prog(
2635 errbuf OUT NOCOPY VARCHAR2,
2636 retcode OUT NOCOPY VARCHAR2,
2637 p_org_id IN NUMBER) IS
2638
2639 l_return_status VARCHAR2(1);
2640 l_concurrent_execution_date DATE;
2641 l_msg_data VARCHAR2(1000);
2642 l_msg_count NUMBER;
2643 l_lock_status NUMBER;
2644 l_params wip_logger.param_tbl_t;
2645 l_shift_seq NUMBER;
2646 l_shift_num NUMBER;
2647 l_shift_start_date DATE;
2648 l_shift_end_date DATE;
2649 l_shift_string VARCHAR2(100);
2650 l_shift_start_time NUMBER;
2651
2652 l_concurrent_count NUMBER;
2653 l_conc_status boolean;
2654
2655 BEGIN
2656
2657 l_concurrent_execution_date := SYSDATE;
2658
2659 IF (g_logLevel <= wip_constants.trace_logging) THEN
2660
2661 l_params(1).paramName := 'p_org_id';
2662 l_params(1).paramValue := p_org_id;
2663
2664 wip_logger.entryPoint(p_procName => 'wip_ws_embedded_analytics_pk.wip_ws_ppmdkpi_conc_prog',
2665 p_params => l_params,
2666 x_returnStatus => l_return_status);
2667
2668 IF(l_return_status <> fnd_api.g_ret_sts_success) THEN
2669 RAISE fnd_api.g_exc_unexpected_error;
2670 END IF;
2671
2672 wip_logger.log(' Start Time : = '||to_char(l_concurrent_execution_date),l_return_status);
2673
2674 END IF;
2675
2676 wip_ws_util.trace_log('Org id = '||to_char(p_org_id));
2677 wip_ws_util.trace_log('Start time = '||to_char(l_concurrent_execution_date));
2678
2679
2680 SAVEPOINT wip_ws_ppmdkpi_calc;
2681
2682
2683 l_concurrent_count := wip_ws_util.get_no_of_running_concurrent(
2684 p_program_application_id => fnd_global.prog_appl_id,
2685 p_concurrent_program_id => fnd_global.conc_program_id,
2686 p_org_id => p_org_id);
2687
2688 if l_concurrent_count > 1 then
2689 wip_ws_util.log_for_duplicate_concurrent (
2690 p_org_id => p_org_id,
2691 p_program_name => 'Parts Per Million Defects KPI');
2692 l_conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', 'Errors encountered in calculation program, please check the log file.');
2693 return;
2694 end if;
2695
2696
2697 DELETE FROM wip_ws_ppm_defects
2698 WHERE organization_id = p_org_id;
2699
2700 populate_ppm_defects_data(
2701 p_start_date => trunc(sysdate-g_ppmd_data_retention),
2702 p_org_id => p_org_id,
2703 x_return_status => l_return_status);
2704
2705 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2706 wip_ws_util.trace_log('Unexpected error occured in populate_fpy_raw_data API');
2707 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2708 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2709 wip_ws_util.trace_log('Expected error occurred in populate_fpy_raw_data API');
2710 RAISE FND_API.G_EXC_ERROR;
2711 ELSE
2712 wip_ws_util.trace_log('populate_fpy_raw_data is successful');
2713 END IF;
2714
2715 IF (g_logLevel <= wip_constants.trace_logging) then
2716 wip_logger.exitPoint(p_procName => 'wip_ws_embedded_analytics_pk.wip_ws_ppmdkpi_conc_prog',
2717 p_procReturnStatus => l_return_status,
2718 p_msg => 'processed successfully',
2719 x_returnStatus => l_return_status);
2720 END IF;
2721
2722 EXCEPTION
2723
2724
2725 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2726 wip_ws_util.trace_log('Came to unexpected error in wip_ws_ppmdkpi_conc_prog');
2727
2728 IF (g_logLevel <= wip_constants.trace_logging) then
2729 wip_logger.exitPoint(p_procName => 'wip_ws_embedded_analytics_pk.wip_ws_ppmdkpi_conc_prog',
2730 p_procReturnStatus => 2,
2731 p_msg => 'unexpected error FND_API.G_EXC_UNEXPECTED_ERROR' || SQLERRM,
2732 x_returnStatus => l_return_status);
2733 END IF;
2734 ROLLBACK TO wip_ws_ppmdkpi_calc;
2735 retcode := 2; -- End with error
2736 WHEN FND_API.G_EXC_ERROR THEN
2737 retcode := 1;
2738 wip_ws_util.trace_log('Came to expected error in wip_ws_ppmdkpi_conc_prog');
2739 IF (g_logLevel <= wip_constants.trace_logging) then
2740 wip_logger.exitPoint(p_procName => 'wip_ws_embedded_analytics_pk.wip_ws_ppmdkpi_conc_prog',
2741 p_procReturnStatus => 1,
2742 p_msg => 'unexpected error FND_API.G_EXC_ERROR' || SQLERRM,
2743 x_returnStatus => l_return_status);
2744 END IF;
2745
2746 ROLLBACK TO wip_ws_ppmdkpi_calc;
2747 WHEN others THEN
2748 wip_ws_util.trace_log('Came to others error in wip_ws_ppmdkpi_conc_prog');
2749
2750 IF (g_logLevel <= wip_constants.trace_logging) then
2751 wip_logger.exitPoint(p_procName => 'wip_ws_embedded_analytics_pk.wip_ws_ppmdkpi_conc_prog',
2752 p_procReturnStatus => 2,
2753 p_msg => 'unexpected error OTHERS' || SQLERRM,
2754 x_returnStatus => l_return_status);
2755 END IF;
2756 ROLLBACK TO wip_ws_ppmdkpi_calc;
2757 retcode := 2; -- End with error
2758 END wip_ws_ppmdkpi_conc_prog;
2759
2760 PROCEDURE populate_ppm_defects_data(
2761 p_start_date DATE,
2762 p_org_id NUMBER,
2763 x_return_status OUT NOCOPY VARCHAR2) IS
2764
2765 l_params wip_logger.param_tbl_t;
2766 l_return_status VARCHAR2(1);
2767
2768 BEGIN
2769
2770 IF (g_logLevel <= wip_constants.trace_logging) THEN
2771
2772 l_params(1).paramName := 'p_start_date';
2773 l_params(1).paramValue := p_start_date;
2774 l_params(2).paramName := 'p_org_id';
2775 l_params(2).paramValue := p_org_id;
2776
2777 wip_logger.entryPoint(p_procName => 'wip_ws_embedded_analytics_pk.populate_ppm_defects_data',
2778 p_params => l_params,
2779 x_returnStatus => l_return_status);
2780
2781 IF(l_return_status <> fnd_api.g_ret_sts_success) THEN
2782 RAISE fnd_api.g_exc_unexpected_error;
2783 END IF;
2784
2785 END IF;
2786
2787 x_return_status := FND_API.G_RET_STS_SUCCESS;
2788
2789 INSERT INTO wip_ws_ppm_defects(
2790 ORGANIZATION_ID,
2791 WIP_ENTITY_ID,
2792 INVENTORY_ITEM_ID,
2793 SHIFT_NUM,
2794 SHIFT_DATE,
2795 QUANTITY_DEFECTED,
2796 QUANTITY_PRODUCED,
2797 LAST_UPDATE_DATE,
2798 LAST_UPDATED_BY,
2799 LAST_UPDATE_LOGIN,
2800 CREATION_DATE,
2801 CREATED_BY,
2802 REQUEST_ID,
2803 PROGRAM_ID,
2804 PROGRAM_APPLICATION_ID,
2805 PROGRAM_UPDATE_DATE
2806 )
2807 select
2808 wdj.organization_id, -- ORGANIZATION_ID
2809 wdj.wip_entity_id, -- WIP_ENTITY_ID
2810 wdj.primary_item_id, -- INVENTORY_ITEM_ID
2811 WIP_WS_EMBEDDED_ANALYTICS_PK.get_shift_num(shift_info) shift_num, -- SHIFT_NUM
2812 WIP_WS_EMBEDDED_ANALYTICS_PK.get_shift_start_date(shift_info) shift_date, -- SHIFT_DATE
2813 wdj.quantity_scrapped, -- qty_defected
2814 wdj.quantity_completed + wdj.quantity_scrapped, -- qty_produced
2815 sysdate, --LAST_UPDATE_DATE,
2816 g_user_id, --LAST_UPDATED_BY,
2817 g_login_id, --LAST_UPDATE_LOGIN,
2818 sysdate, --CREATION_DATE,
2819 g_user_id, --CREATED_BY,
2820 g_request_id, --REQUEST_ID,
2821 g_prog_id, --PROGRAM_ID,
2822 g_prog_appid,--PROGRAM_APPLICATION_ID,
2823 sysdate --PROGRAM_UPDATE_DATE
2824 from
2825 (select
2826 WIP_WS_EMBEDDED_ANALYTICS_PK.get_shift_info_for_date(
2827 wdj1.organization_id, null, null,
2828 nvl(wdj1.date_completed, wdj1.date_closed)) shift_info,
2829 wdj1.*
2830 from wip_discrete_jobs wdj1
2831 where wdj1.date_completed > p_start_date
2832 and wdj1.organization_id = p_org_id
2833 and wdj1.status_type in (WIP_CONSTANTS.CLOSED, WIP_CONSTANTS.COMP_CHRG, WIP_CONSTANTS.COMP_NOCHRG)
2834 and wdj1.quantity_completed > 0) wdj;
2835
2836 IF (g_logLevel <= wip_constants.trace_logging) then
2837 wip_logger.exitPoint(p_procName => 'wip_ws_embedded_analytics_pk.populate_ppm_defects_data',
2838 p_procReturnStatus => l_return_status,
2839 p_msg => 'processed successfully',
2840 x_returnStatus => l_return_status);
2841 END IF;
2842
2843 EXCEPTION
2844
2845 WHEN others THEN
2846 wip_ws_util.trace_log('Error in populate_ppm_defects_data');
2847
2848 IF (g_logLevel <= wip_constants.trace_logging) then
2849 wip_logger.exitPoint(p_procName => 'wip_ws_embedded_analytics_pk.populate_ppm_defects_data',
2850 p_procReturnStatus => l_return_status,
2851 p_msg => 'processed error' || SQLERRM,
2852 x_returnStatus => l_return_status);
2853 END IF;
2854
2855 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2856 END populate_ppm_defects_data;
2857
2858 END wip_ws_embedded_analytics_pk;
2859