[Home] [Help]
PACKAGE BODY: APPS.WIP_WS_PTPKPI_PK
Source
1 package body WIP_WS_PTPKPI_PK as
2 /* $Header: WIPWSPPB.pls 120.11.12020000.2 2013/01/23 12:07:30 sjallipa ship $ */
3
4 /*============================================================================+
5 | Copyright (c) 1993 Oracle Corporation Belmont, California, USA |
6 | All rights reserved. |
7 | Oracle Manufacturing |
8 +=============================================================================+
9 |
10 | FILE NAME : WIPWSPPB.sql
11 | DESCRIPTION :
12 | This package contains specification for all APIs related to
13 MES production to Plan module
14 |
15 | HISTORY : created 13-SEP-07
16 | Renga Kannan 13-Sep-2007 Creating Initial Version
17 |
18
19 *============================================================================*/
20
21 Procedure populate_plan_data(
22 p_org_id in number,
23 p_org_ptpkpi_rec IN org_ptpkpi_rec_type,
24 x_return_status out nocopy varchar2,
25 x_msg_count out nocopy number,
26 x_msg_data out nocopy varchar2);
27
28 Procedure populate_actual_data(
29 p_org_id in Number,
30 p_org_ptpkpi_rec IN org_ptpkpi_rec_type,
31 x_return_status out nocopy Varchar2,
32 x_msg_count out nocopy number,
33 x_msg_data out nocopy varchar2);
34
35 Procedure wip_ws_PTPKPI_CONC_PROG(
36 errbuf out nocopy varchar2,
37 retcode out nocopy varchar2,
38 p_org_id in number) is
39
40 l_return_status varchar2(1);
41 l_msg_data varchar2(1000);
42 l_msg_count number;
43 l_lock_status number;
44 l_params wip_logger.param_tbl_t;
45 l_pref_exists varchar2(1);
46
47 l_concurrent_count NUMBER;
48 l_conc_status boolean;
49 l_org_ptpkpi_rec org_ptpkpi_rec_type;
50
51 Begin
52 if (g_logLevel <= wip_constants.trace_logging) then
53
54 l_params(1).paramName := 'p_org_id';
55 l_params(1).paramValue := p_org_id;
56
57 wip_logger.entryPoint(p_procName => 'WIP_WS_SHORTAGE.get_org_comp_calc_param',
58 p_params => l_params,
59 x_returnStatus => l_return_Status);
60 if(l_return_Status <> fnd_api.g_ret_sts_success) then
61 raise fnd_api.g_exc_unexpected_error;
62 end if;
63 wip_logger.log(' Start Time : = '||to_char(sysdate),l_return_status);
64
65 end if;
66 wip_ws_util.trace_log('Org id = '||to_char(p_org_id));
67 wip_ws_util.trace_log('Start time = '||to_char(sysdate));
68
69
70 wip_logger.log(' Trying to get lock for this organization ',l_return_status);
71 savepoint wip_ws_ptpkpi_calc;
72
73 l_concurrent_count := wip_ws_util.get_no_of_running_concurrent(
74 p_program_application_id => fnd_global.prog_appl_id,
75 p_concurrent_program_id => fnd_global.conc_program_id,
76 p_org_id => p_org_id);
77
78 if l_concurrent_count > 1 then
79 wip_ws_util.log_for_duplicate_concurrent (
80 p_org_id => p_org_id,
81 p_program_name => 'Production to Plan KPI');
82 l_conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', 'Errors encountered in calculation program, please check the log file.');
83 return;
84 end if;
85
86 get_org_ptpkpi_param(p_org_id => p_org_id,
87 x_pref_exists => l_pref_exists,
88 x_org_ptpkpi_rec => l_org_ptpkpi_rec);
89
90
91 If l_pref_exists = 'N' then
92 wip_ws_util.trace_log(' No Preference exists for this organization');
93 fnd_message.set_name('WIP','NO_PTPKPI_PREF_EXISTS');
94 raise FND_API.G_EXC_ERROR;
95 End if;
96
97
98
99 -- Call populate plan data api
100 populate_plan_data(p_org_id => p_org_id,
101 p_org_ptpkpi_rec=> l_org_ptpkpi_rec,
102 x_return_status => l_return_status,
103 x_msg_count => l_msg_count,
104 x_msg_data => l_msg_data);
105
106 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
107 wip_ws_util.trace_log('Unexpected error occured in populate_plan_data API');
108 raise FND_API.G_EXC_UNEXPECTED_ERROR;
109 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
110 wip_ws_util.trace_log('Expected error occurred in populate_plan_data API');
111 raise FND_API.G_EXC_ERROR;
112 ELSE
113 wip_ws_util.trace_log('populate_plan_data_API is successfull');
114 END IF;
115
116 -- Call populate actual data api
117
118 populate_actual_data(
119 p_org_id => p_org_id,
120 p_org_ptpkpi_rec=> l_org_ptpkpi_rec,
121 x_return_status => l_return_status,
122 x_msg_count => l_msg_count,
123 x_msg_data => l_msg_data);
124
125 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
126 wip_ws_util.trace_log('Unexpected error occured in populate_actual_data API');
127 raise FND_API.G_EXC_UNEXPECTED_ERROR;
128 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
129 wip_ws_util.trace_log('Expected error occurred in populate_actual_data API');
130 raise FND_API.G_EXC_ERROR;
131 ELSE
132 wip_ws_util.trace_log('populate_actual_data is successfull');
133 END IF;
134
135
136 EXCEPTION
137 when FND_API.G_EXC_UNEXPECTED_ERROR then
138 wip_ws_util.trace_log('Came to unexpected error in wip_ws_PTPKPI_CONC_PROG');
139 rollback to wip_ws_ptpkpi_calc;
140 retcode := 2; -- End with error
141 when FND_API.G_EXC_ERROR then
142 retcode := 1;
143 wip_ws_util.trace_log('Came to expected error in wip_ws_PTPKPI_CONC_PROG');
144 rollback to wip_ws_ptpkpi_calc;
145 when others then
146 wip_ws_util.trace_log('Came to others error in wip_ws_PTPKPI_CONC_PROG');
147 rollback to wip_ws_ptpkpi_calc;
148 retcode := 2; -- End with error
149 End wip_ws_PTPKPI_CONC_PROG;
150
151
152
153
154
155
156 /**************************************************************************
157
158 Procedure Name : Populate_Plan_data
159 Description : This procedure will delete the existing rows for
160 the plan table and populate new data. This API
161 will be called from concurrent program API
162
163 ****************************************************************************/
164
165 Procedure populate_plan_data(p_org_id in number,
166 p_org_ptpkpi_rec IN org_ptpkpi_rec_type,
167 x_return_status out nocopy varchar2,
168 x_msg_count out nocopy number,
169 x_msg_data out nocopy varchar2) is
170
171 l_sql VARCHAR2(2048) := null;
172 l_job_status_clause varchar2(2048);
173 l_job_type_clause varchar2(2048);
174 l_cursor integer;
175 l_dummy integer;
176
177
178 Begin
179 x_return_status := FND_API.G_RET_STS_SUCCESS;
180
181
182 -- Delete the rows that are populated by last concurrent run
183 -- for the parameter org
184
185 wip_ws_util.trace_log('Entering Populate_plan_data API');
186
187 delete wip_ws_ptpkpi_plan
188 where organization_id = p_org_id;
189
190 wip_ws_util.trace_log(' Deleted old rows in the table. Rows deleted = '||sql%rowcount);
191
192 -- Populate data from wip data model to
193 -- ptp kpi plan table
194 l_job_status_clause := ' and wdj.status_type in ('|| get_pref_job_statuses(p_org_ptpkpi_rec) || ')';
195 l_job_type_clause := ' and wdj.job_type in ('||get_job_types(p_org_ptpkpi_rec) || ')';
196 l_sql :=
197 'insert into wip_ws_ptpkpi_plan(
198 organization_id,
199 department_id,
200 wip_entity_id,
201 operation_seq_num,
202 shift_id,
203 planned_qty,
204 primary_uom_code,
205 op_lead_time,
206 concurrent_request_id,
207 last_update_date,
208 last_update_by,
209 creation_date,
210 created_by,
211 shift_start_time
212 )
213 select
214 wo.organization_id,
215 wo.department_id,
216 wo.wip_entity_id,
217 wo.operation_seq_num,
218 WIP_WS_PTPKPI_UTIL.get_shift_id_for_date(
219 wo.organization_id, wo.department_id, null, wo.last_unit_completion_date
220 ) ,
221 wo.scheduled_quantity-wo.cumulative_scrap_quantity ,
222 msi.primary_uom_code,
223 WIP_WS_PTPKPI_UTIL.get_operation_lead_time(
224 wo.organization_id, wo.wip_entity_id, wo.operation_seq_num
225 ) ,
226 fnd_global.CONC_REQUEST_ID,
227 sysdate,
228 :guserid,
229 sysdate,
230 :guserid,
231 WIP_WS_PTPKPI_UTIL.get_datetime_for_shift(:p_org_id,WIP_WS_PTPKPI_UTIL.get_shift_id_for_date(
232 wo.organization_id, wo.department_id, null, wo.last_unit_completion_date
233 ),1)
234 from
235 wip_operations wo,
236 wip_discrete_jobs wdj,
237 mtl_system_items msi
238 where wo.organization_id = :p_org_id
239 and wo.repetitive_schedule_id is null
240 and wo.last_unit_completion_date >= trunc(sysdate) - 30
241 and wo.wip_entity_id = wdj.wip_entity_id
242 and wo.organization_id = wdj.organization_id
243 and wdj.primary_item_id = msi.inventory_item_id
244 and wdj.organization_id = msi.organization_id';
245 l_sql := l_sql || l_job_status_clause || l_job_type_clause;
246
247 wip_ws_util.trace_log('Constructed sql statement = ');
248 wip_ws_util.trace_log(l_sql);
249 l_cursor := dbms_sql.open_cursor;
250 dbms_sql.parse(l_cursor, l_sql, dbms_sql.native);
251 dbms_sql.bind_variable(l_cursor, ':p_org_id', p_org_id);
252 dbms_sql.bind_variable(l_cursor,':guserid',g_user_id);
253 l_dummy := dbms_sql.execute(l_cursor);
254 wip_ws_util.trace_log(' Number of rows inserted = '||sql%rowcount);
255 dbms_sql.close_cursor(l_cursor);
256
257 EXCEPTION
258 when FND_API.G_EXC_UNEXPECTED_ERROR then
259 wip_ws_util.trace_log('Error message = '||sqlerrm);
260 wip_ws_util.trace_log('Came to unexpected error in populate_plan_data');
261 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
262 when FND_API.G_EXC_ERROR then
263 wip_ws_util.trace_log('Came to expected error in populate_plan_data');
264 x_return_status := FND_API.G_RET_STS_ERROR;
265 when others then
266 wip_ws_util.trace_log('Error message = '||sqlerrm);
267 wip_ws_util.trace_log('Came to others error in populate_plan_data');
268 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
269 End populate_plan_data;
270
271
272 FUNCTION get_pref_job_statuses(
273 p_org_ptpkpi_rec IN org_ptpkpi_rec_type) RETURN VARCHAR2 IS
274 status_str VARCHAR2(240) := null;
275 BEGIN
276 if(p_org_ptpkpi_rec.inc_released_jobs = WIP_CONSTANTS.YES) then
277 status_str := to_char(wip_constants.RELEASED);
278 end if;
279
280 if(p_org_ptpkpi_rec.inc_unreleased_jobs = WIP_CONSTANTS.YES) then
281 if(status_str is not null) then
282 status_str := status_str||',';
283 end if;
284 status_str := status_str || to_char(wip_constants.UNRELEASED);
285 end if;
286
287 if(p_org_ptpkpi_rec.inc_onhold_jobs = WIP_CONSTANTS.YES) then
288 if(status_str is not null) then
289 status_str := status_str||',';
290 end if;
291 status_str := status_str || to_char(wip_constants.HOLD);
292 end if;
293
294 if (p_org_ptpkpi_rec.inc_completed_jobs = WIP_CONSTANTS.YES) then
295 if(status_str is not null) then
296 status_str := status_str||',';
297 end if;
298 status_str := status_str || to_char(wip_constants.COMPLETED);
299 end if;
300 if status_str is null then
301 status_str := 'null';
302 end if;
303 return status_str;
304 END get_pref_job_statuses ;
305
306 FUNCTION get_job_types(
307 p_org_ptpkpi_rec IN org_ptpkpi_rec_type) RETURN VARCHAR2 IS
308 job_type_str VARCHAR2(240) := null;
309 BEGIN
310 wip_ws_util.trace_log('include std jobs = '||p_org_ptpkpi_rec.inc_standard_jobs);
311 wip_ws_util.trace_log('include non std jobs = '||p_org_ptpkpi_rec.inc_nonstd_jobs);
312 if (p_org_ptpkpi_rec.inc_standard_jobs = WIP_CONSTANTS.YES) then
313 job_type_str := to_char(wip_constants.STANDARD);
314 end if;
315 if (p_org_ptpkpi_rec.inc_nonstd_jobs = WIP_CONSTANTS.YES) then
316 if (job_type_str is not null) then
317 job_type_str := job_type_str||',';
318 end if;
319 job_type_str := job_type_str||to_char(wip_constants.NONSTANDARD);
320 end if;
321
322 return job_type_str;
323 END get_job_types;
324
325 Procedure get_org_ptpkpi_param(
326 p_org_id IN NUMBER,
327 x_pref_exists out nocopy varchar2,
328 x_org_ptpkpi_rec OUT NOCOPY org_ptpkpi_rec_type) is
329 l_row_seq_num number;
330 Begin
331
332 l_row_seq_num := WIP_WS_UTIL.get_multival_pref_seq(p_pref_id => g_pref_id_ptp,
333 p_level_id => g_pref_level_id_site,
334 p_attribute_name => g_pref_val_mast_org_att,
335 p_attribute_val => p_org_id);
336
337 wip_ws_util.trace_log('Preferenc row seq num = '||l_row_seq_num);
338
339 If l_row_seq_num is not null then
340 x_pref_exists := 'Y';
341 x_org_ptpkpi_rec.org_id := p_org_id;
342 x_org_ptpkpi_rec.inc_released_jobs :=
343 WIP_WS_UTIL.get_multival_pref_val_code(g_pref_id_ptp, g_pref_level_id_site, l_row_seq_num, g_pref_val_inc_release_att);
344 x_org_ptpkpi_rec.inc_unreleased_jobs :=
345 WIP_WS_UTIL.GET_MULTIVAL_PREF_VAL_CODE(g_pref_id_ptp, g_pref_level_id_site, l_row_seq_num, g_pref_val_inc_unreleased_att);
346 x_org_ptpkpi_rec.inc_onhold_jobs :=
347 WIP_WS_UTIL.GET_MULTIVAL_PREF_VAL_CODE(g_pref_id_ptp, g_pref_level_id_site, l_row_seq_num, g_pref_val_inc_onhold_att);
348 x_org_ptpkpi_rec.inc_completed_jobs :=
349 WIP_WS_UTIL.GET_MULTIVAL_PREF_VAL_CODE(g_pref_id_ptp, g_pref_level_id_site, l_row_seq_num, g_pref_val_inc_completed_att);
350 x_org_ptpkpi_rec.inc_standard_jobs :=
351 WIP_WS_UTIL.GET_MULTIVAL_PREF_VAL_CODE(g_pref_id_ptp, g_pref_level_id_site, l_row_seq_num, g_pref_val_inc_standard_att);
352 x_org_ptpkpi_rec.inc_nonstd_jobs :=
353 WIP_WS_UTIL.GET_MULTIVAL_PREF_VAL_CODE(g_pref_id_ptp, g_pref_level_id_site, l_row_seq_num, g_pref_val_inc_nonstd_att);
354 else
355 x_pref_exists := 'N';
356 end if;
357
358 End;
359
360 Procedure populate_actual_data(
361 p_org_id in Number,
362 p_org_ptpkpi_rec IN org_ptpkpi_rec_type,
363 x_return_status out nocopy Varchar2,
364 x_msg_count out nocopy number,
365 x_msg_data out nocopy varchar2) is
366 /*
367 Cursor move_tran_cur(p_org_id number,recentshiftdate date) is
368 select
369 wo.organization_id ,
370 wo.department_id ,
371 wo.wip_entity_id ,
372 wo.operation_seq_num,
373 wmt1.PRIMARY_QUANTITY,
374 WIP_WS_PTPKPI_UTIL.get_shift_id_for_date(
375 wo.organization_id,
376 wo.department_id,
377 null,
378 wmt1.transaction_date
379 ) shift_id,
380 fm_operation_seq_num fm_op,
381 FM_INTRAOPERATION_STEP_TYPE fm_step,
382 to_operation_seq_num to_op,
383 TO_INTRAOPERATION_STEP_TYPE to_step,
384 wmt1.primary_uom primary_uom_code,
385 WIP_WS_PTPKPI_UTIL.get_operation_lead_time(
386 wmt1.organization_id,
387 wmt1.wip_entity_id,
388 wo.operation_seq_num) op_lead_time,
389 wo.operation_seq_num op
390 from
391 wip_move_transactions wmt1,
392 wip_operations wo,
393 wip_discrete_jobs wdj
394 where
395 wo.organization_id = p_org_id
396 and wo.organization_id = wmt1.organization_id
397 and wo.wip_entity_id = wdj.wip_entity_id
398 and wo.wip_entity_id = wmt1.wip_entity_id
399 and wo.repetitive_schedule_id is null
400 and wmt1.transaction_date >= recentShiftDate
401 and (
402 (
403 wo.operation_seq_num >=
404 wmt1.fm_operation_seq_num +
405 DECODE(SIGN(wmt1.FM_INTRAOPERATION_STEP_TYPE - 2),1,1,0)
406 and wo.operation_seq_num <
407 wmt1.to_operation_seq_num +
408 DECODE(SIGN(wmt1.TO_INTRAOPERATION_STEP_TYPE - 2),1,1,0)
409 and (
410 wmt1.to_operation_seq_num > wmt1.fm_operation_seq_num OR
411 (wmt1.to_operation_seq_num = wmt1.fm_operation_seq_num AND
412 wmt1.FM_INTRAOPERATION_STEP_TYPE <= 2 AND
413 wmt1.TO_INTRAOPERATION_STEP_TYPE > 2)
414 )
415 AND (
416 wo.count_point_type < 3 OR
417 wo.operation_seq_num = wmt1.fm_operation_seq_num OR
418 (wo.operation_seq_num = wmt1.to_operation_seq_num AND
419 wmt1.TO_INTRAOPERATION_STEP_TYPE > 2)
420 )
421 )
422 OR
423 (
424 wo.operation_seq_num <
425 wmt1.fm_operation_seq_num +
426 DECODE(SIGN(wmt1.FM_INTRAOPERATION_STEP_TYPE-2),1,1,0) AND
427 wo.operation_seq_num >=
428 wmt1.to_operation_seq_num +
429 DECODE(SIGN(wmt1.TO_INTRAOPERATION_STEP_TYPE-2),1,1,0) AND
430 (wmt1.fm_operation_seq_num > wmt1.to_operation_seq_num OR
431 (wmt1.fm_operation_seq_num = wmt1.to_operation_seq_num AND
432 wmt1.TO_INTRAOPERATION_STEP_TYPE <= 2 AND
433 wmt1.FM_INTRAOPERATION_STEP_TYPE > 2)
434 ) AND
435 (wo.count_point_type < 3 OR
436 (wo.operation_seq_num = wmt1.to_operation_seq_num AND wo.count_point_type < 3) OR
437 (wo.operation_seq_num = wmt1.fm_operation_seq_num AND wmt1.FM_INTRAOPERATION_STEP_TYPE > 2)
438 )
439 )
440 OR
441 (
442 -- pick up all the returns from scrap/reject for the source operation
443 wmt1.FM_INTRAOPERATION_STEP_TYPE in (4,5) AND
444 wo.operation_seq_num = wmt1.fm_operation_seq_num
445 )
446 );
447 */
448
449 l_sql VARCHAR2(4048);
450 move_qty number;
451 scrap_qty number;
452 reject_qty number;
453 -- RECENTSHIFTID number;
454 RecentShiftStartTime date;
455
456 l_stmt_no number;
457 l_job_status_clause varchar2(1000);
458 l_job_type_clause varchar2(1000);
459
460 TYPE mov_tran_Curtype IS REF CURSOR;
461 move_tran_cur mov_tran_curtype;
462
463 l_operation_seq_num number;
464 l_primary_quantity number;
465 l_fm_op number;
466 l_fm_step number;
467 l_to_op number;
468 l_to_step number;
469
470
471 l_organization_id number;
472 l_department_id number;
473 l_wip_entity_id number;
474 l_op number;
475 l_shift_id varchar2(100);
476 l_primary_uom_code varchar2(100);
477 l_op_lead_time number;
478 l_lead_time number;
479 RECENTSHIFTDATE date;
480
481
482 Begin
483
484 /*
485 QUEUE CONSTANT NUMBER := 1;
486 RUN CONSTANT NUMBER := 2;
487 TOMOVE CONSTANT NUMBER := 3;
488 REJECT CONSTANT NUMBER := 4;
489 SCRAP CONSTANT NUMBER := 5;
490 */
491
492 wip_ws_util.trace_log('Entering populate_actual_data API');
493 -- remove old data
494
495 l_stmt_no := 10;
496
497 delete
498 from wip_ws_ptpkpi_actual
499 where shift_start_time < trunc(sysdate) - 30
500 and organization_id = p_org_id;
501
502 wip_ws_util.trace_log('Deleted old records. Number of records deleted = '||sql%rowcount);
503
504 -- get the most recent shift information from existing aggregated actuals
505
506 l_stmt_no := 20;
507 Begin
508 -- Bug 16177462
509 /* select shift_id, shift_start_time
510 into RecentShiftid, RecentShiftStartTime
511 from wip_ws_ptpkpi_actual
512 where shift_start_time in
513 (select max(shift_start_Time)
514 from wip_ws_ptpkpi_actual)
515 and rownum = 1;*/
516
517 select shift_start_time
518 into RecentShiftStartTime
519 from wip_ws_ptpkpi_actual
520 where shift_start_time in
521 (select max(shift_start_Time)
522 from wip_ws_ptpkpi_actual)
523 and rownum = 1;
524
525 Exception When no_data_found then
526
527 -- RecentShiftid := null;
528 RecentShiftStartTime := null;
529
530 End;
531
532 l_stmt_no := 30;
533 --wip_ws_util.trace_log(' Most recent shift id = '||to_char(RecentShiftid));
534 wip_ws_util.trace_log(' Most recent shift id = '||to_char(RecentShiftStartTime));
535
536 /*
537 If RecentShiftid is not null then
538 -- remove possible partial aggregated data for the most recent shift
539 delete from wip_ws_ptpkpi_actual
540 where shift_start_time >= RecentShiftStartTime;
541 End if;
542 */
543
544 delete from wip_ws_ptpkpi_actual
545 where shift_start_time >= sysdate-7
546 and organization_id = p_org_id;
547 -- process each row and find out whether the quantity should be
548 -- counted as a positive/negative move, scrap, or reject.
549
550 wip_ws_util.trace_log('Before starting For loop');
551
552 l_stmt_no := 40;
553 l_sql := 'select
554 wo.organization_id ,
555 wo.department_id ,
556 wo.wip_entity_id ,
557 wo.operation_seq_num,
558 wmt1.PRIMARY_QUANTITY,
559 WIP_WS_PTPKPI_UTIL.get_shift_id_for_date(
560 wo.organization_id,
561 wo.department_id,
562 null,
563 wmt1.transaction_date
564 ) shift_id,
565 fm_operation_seq_num fm_op,
566 FM_INTRAOPERATION_STEP_TYPE fm_step,
567 to_operation_seq_num to_op,
568 TO_INTRAOPERATION_STEP_TYPE to_step,
569 wmt1.primary_uom primary_uom_code,
570 WIP_WS_PTPKPI_UTIL.get_operation_lead_time(
571 wdj.organization_id,
572 wdj.wip_entity_id,
573 wo.operation_seq_num) op_lead_time,
574 wo.operation_seq_num op
575 from
576 wip_move_transactions wmt1,
577 wip_operations wo,
578 wip_discrete_jobs wdj
579 where
580 wo.organization_id = :p_org_id
581 and wo.organization_id = wmt1.organization_id
582 and wo.wip_entity_id = wdj.wip_entity_id
583 and wo.wip_entity_id = wmt1.wip_entity_id
584 and wo.repetitive_schedule_id is null
585 and wmt1.transaction_date >= sysdate-7
586 and (
587 (
588 wo.operation_seq_num >=
589 wmt1.fm_operation_seq_num +
590 DECODE(SIGN(wmt1.FM_INTRAOPERATION_STEP_TYPE - 2),1,1,0)
591 and wo.operation_seq_num <
592 wmt1.to_operation_seq_num +
593 DECODE(SIGN(wmt1.TO_INTRAOPERATION_STEP_TYPE - 2),1,1,0)
594 and (
595 wmt1.to_operation_seq_num > wmt1.fm_operation_seq_num OR
596 (wmt1.to_operation_seq_num = wmt1.fm_operation_seq_num AND
597 wmt1.FM_INTRAOPERATION_STEP_TYPE <= 2 AND
598 wmt1.TO_INTRAOPERATION_STEP_TYPE > 2)
599 )
600 AND (
601 wo.count_point_type < 3 OR
602 wo.operation_seq_num = wmt1.fm_operation_seq_num OR
603 (wo.operation_seq_num = wmt1.to_operation_seq_num AND
604 wmt1.TO_INTRAOPERATION_STEP_TYPE > 2)
605 )
606 )
607 OR
608 (
609 wo.operation_seq_num <
610 wmt1.fm_operation_seq_num +
611 DECODE(SIGN(wmt1.FM_INTRAOPERATION_STEP_TYPE-2),1,1,0) AND
612 wo.operation_seq_num >=
613 wmt1.to_operation_seq_num +
614 DECODE(SIGN(wmt1.TO_INTRAOPERATION_STEP_TYPE-2),1,1,0) AND
615 (wmt1.fm_operation_seq_num > wmt1.to_operation_seq_num OR
616 (wmt1.fm_operation_seq_num = wmt1.to_operation_seq_num AND
617 wmt1.TO_INTRAOPERATION_STEP_TYPE <= 2 AND
618 wmt1.FM_INTRAOPERATION_STEP_TYPE > 2)
619 ) AND
620 (wo.count_point_type < 3 OR
621 (wo.operation_seq_num = wmt1.to_operation_seq_num AND wo.count_point_type < 3) OR
622 (wo.operation_seq_num = wmt1.fm_operation_seq_num AND wmt1.FM_INTRAOPERATION_STEP_TYPE > 2)
623 )
624 )
625 OR
626 (
627 -- pick up all the returns from scrap/reject for the source operation
628 wmt1.FM_INTRAOPERATION_STEP_TYPE in (4,5) AND
629 wo.operation_seq_num = wmt1.fm_operation_seq_num
630 )
631 )';
632 l_job_status_clause := ' and wdj.status_type in ('|| get_pref_job_statuses(p_org_ptpkpi_rec) || ')';
633 l_job_type_clause := ' and wdj.job_type in ('||get_job_types(p_org_ptpkpi_rec) || ')';
634
635 l_stmt_no := 42;
636 l_sql := l_sql||l_job_status_clause || l_job_type_clause;
637
638 wip_ws_util.trace_log('Sql Statement');
639 wip_ws_util.trace_log(l_sql);
640
641 l_stmt_no := 45;
642 RECENTSHIFTDATE := nvl(RecentShiftStartTime,sysdate-30);
643
644 open move_tran_cur for l_sql using p_org_id;
645 Loop
646 fetch move_tran_cur into l_organization_id,l_department_id,l_wip_entity_id,
647 l_operation_seq_num,l_primary_quantity,l_shift_id,
648 l_fm_op,l_fm_step,l_to_op,l_to_step,l_primary_uom_code,
649 l_lead_time,l_op;
650 wip_ws_util.trace_log(' Inside loop');
651 exit when move_tran_cur%notfound;
652 move_qty := 0;
653 scrap_qty := 0;
654 reject_qty := 0;
655
656 l_stmt_no := 50;
657 if (l_to_step in (1, 2)) then
658 l_stmt_no := 60;
659 if (l_to_op > l_op) then
660 move_qty := l_primary_quantity;
661 elsif (l_to_op < l_op) then
662 move_qty := -1 * l_primary_quantity;
663 elsif (
664 l_to_op <= l_fm_op and
665 l_to_op = l_op and
666 l_FM_step not in (4,5)
667 ) then
668 move_qty := -1 * l_primary_quantity;
669 end if;
670 elsif (l_to_step= 3) then
671 l_stmt_no := 70;
672 if (l_to_op >= l_op) then
673 move_qty := l_primary_quantity;
674 elsif (
675 l_to_op < l_op
676 and( l_FM_step not in (4,5)
677 or l_fm_op <> l_op
678 )
679 ) then
680 move_qty := -1 * l_primary_quantity;
681 end if;
682 elsif (l_to_step= 5) then
683 l_stmt_no := 80;
684 if (l_to_op> l_op) then
685 move_qty := l_primary_quantity ;
686 elsif (l_to_op < l_op) then
687 move_qty := -1 * l_primary_quantity;
688 elsif (l_to_op = l_op) then
689 move_qty := 0;
690 scrap_qty := l_primary_quantity;
691 end if;
692
693 elsif (l_to_step= 4) Then
694 l_stmt_no := 90;
695 if (l_to_op > l_op) Then
696 move_qty := l_primary_quantity ;
697 elsif (l_to_op < l_op) then
698 move_qty := -1 * l_primary_quantity;
699 elsif (l_to_op = l_op) then
700 move_qty := 0;
701 reject_qty := l_primary_quantity;
702 end if;
703 End if;
704 l_stmt_no := 100;
705 if (l_fm_step = 5) then
706 if ( l_fm_op = l_op
707 and l_op >= l_to_op) then
708 scrap_qty := -1 * l_primary_quantity;
709 end if;
710 end if;
711
712 if (l_fm_step = 4) then
713 if (l_fm_op = l_op and
714 l_op >= l_to_op) then
715 reject_qty := -1 * l_primary_quantity;
716 end if;
717 end if;
718
719 l_stmt_no := 110;
720
721
722 wip_ws_util.trace_log(' Org id = '||to_char(l_organization_id));
723 wip_ws_util.trace_log(' dept = '||to_char(l_department_id));
724 wip_ws_util.trace_log(' entity id = '||to_char(l_wip_entity_id));
725 wip_ws_util.trace_log(' op = '||to_char(l_op));
726 wip_ws_util.trace_log(' move qty = '||to_char(move_qty));
727 wip_ws_util.trace_log(' scrap qty = '||to_char(scrap_qty));
728 wip_ws_util.trace_log(' reject qty = '||to_char(reject_qty));
729 wip_ws_util.trace_log(' shift id = '||l_shift_id);
730 wip_ws_util.trace_log(' uom = '||l_primary_uom_code);
731 wip_ws_util.trace_log(' Lead time = '||to_char(l_lead_time));
732
733 If move_qty+scrap_qty+reject_qty <> 0 Then
734 insert into wip_ws_ptp_gt
735 (organization_id,
736 department_id,
737 wip_entity_id,
738 operation_seq_num,
739 move_qty,
740 scrap_qty,
741 reject_qty,
742 shift_id,
743 primary_uom_code,
744 op_lead_time
745 )
746 values(
747 l_organization_id,
748 l_department_id,
749 l_wip_entity_id,
750 l_op,
751 move_qty,
752 scrap_qty,
753 reject_qty,
754 l_shift_id,
755 l_primary_uom_code,
756 l_lead_time
757 );
758 End if;
759
760 End Loop;
761
762 /*
763 For move_tran_rec in move_tran_cur(p_org_id,nvl(RecentShiftStartTime,sysdate-30))
764 Loop
765 wip_ws_util.trace_log('Inside Loop');
766 move_qty := 0;
767 scrap_qty := 0;
768 reject_qty := 0;
769
770 l_stmt_no := 50;
771
772 if (move_tran_rec.to_step in (1, 2)) then
773 l_stmt_no := 60;
774 if (move_tran_rec.to_op > move_tran_rec.op) then
775 move_qty := move_tran_rec.primary_quantity;
776 elsif (move_tran_rec.to_op < move_tran_rec.op) then
777 move_qty := -1 * move_tran_rec.primary_quantity;
778 elsif (
779 move_tran_rec.to_op <= move_tran_rec.fm_op and
780 move_tran_rec.to_op = move_tran_rec.op and
781 move_tran_rec.FM_step not in (4,5)
782 ) then
783 move_qty := -1 * move_tran_rec.primary_quantity;
784 end if;
785 elsif (move_tran_rec.to_step= 3) then
786 l_stmt_no := 70;
787 if (move_tran_rec.to_op >= move_tran_rec.op) then
788 move_qty := move_tran_rec.primary_quantity;
789 elsif (
790 move_tran_rec.to_op < move_tran_rec.op
791 and( move_tran_rec.FM_step not in (4,5)
792 or move_tran_rec.fm_op <> move_tran_rec.op
793 )
794 ) then
795 move_qty := -1 * move_tran_rec.primary_quantity;
796 end if;
797 elsif (move_tran_rec.to_step= 5) then
798 l_stmt_no := 80;
799 if (move_tran_rec.to_op> move_tran_rec.op) then
800 move_qty := move_tran_rec.primary_quantity ;
801 elsif (move_tran_rec.to_op < move_tran_rec.op) then
802 move_qty := -1 * move_tran_rec.primary_quantity;
803 elsif (move_tran_rec.to_op = move_tran_rec.op) then
804 move_qty := 0;
805 scrap_qty := move_tran_rec.primary_quantity;
806 end if;
807
808 elsif (move_tran_rec.to_step= 4) Then
809 l_stmt_no := 90;
810 if (move_tran_rec.to_op > move_tran_rec.op) Then
811 move_qty := move_tran_rec.primary_quantity ;
812 elsif (move_tran_rec.to_op < move_tran_rec.op) then
813 move_qty := -1 * move_tran_rec.primary_quantity;
814 elsif (move_tran_rec.to_op = move_tran_rec.op) then
815 move_qty := 0;
816 reject_qty := move_tran_rec.primary_quantity;
817 end if;
818 End if;
819 l_stmt_no := 100;
820 if (move_tran_rec.fm_step = 5) then
821 if ( move_tran_rec.fm_op = move_tran_rec.op
822 and move_tran_rec.op >= move_tran_rec.to_op) then
823 scrap_qty := -1 * move_tran_rec.primary_quantity;
824 end if;
825 end if;
826
827 if (move_tran_rec.fm_step = 4) then
828 if (move_tran_rec.fm_op = move_tran_rec.op and
829 move_tran_rec.op >= move_tran_rec.to_op) then
830 reject_qty := -1 * move_tran_rec.primary_quantity;
831 end if;
832 end if;
833
834 l_stmt_no := 110;
835
836
837 wip_ws_util.trace_log(' Org id = '||to_char(move_tran_rec.organization_id));
838 wip_ws_util.trace_log(' dept = '||to_char(move_tran_rec.department_id));
839 wip_ws_util.trace_log(' entity id = '||to_char(move_tran_rec.wip_entity_id));
840 wip_ws_util.trace_log(' op = '||to_char(move_tran_rec.op));
841 wip_ws_util.trace_log(' move qty = '||to_char(move_qty));
842 wip_ws_util.trace_log(' scrap qty = '||to_char(scrap_qty));
843 wip_ws_util.trace_log(' reject qty = '||to_char(reject_qty));
844 wip_ws_util.trace_log(' shift id = '||move_tran_rec.shift_id);
845 wip_ws_util.trace_log(' uom = '||move_tran_rec.primary_uom_code);
846 wip_ws_util.trace_log(' Lead time = '||to_char(move_tran_rec.op_lead_time));
847
848 insert into wip_ws_ptp_gt
849 (organization_id,
850 department_id,
851 wip_entity_id,
852 operation_seq_num,
853 move_qty,
854 scrap_qty,
855 reject_qty,
856 shift_id,
857 primary_uom_code,
858 op_lead_time
859 )
860 values(
861 move_tran_rec.organization_id,
862 move_tran_rec.department_id,
863 move_tran_rec.wip_entity_id,
864 move_tran_rec.op,
865 move_qty,
866 scrap_qty,
867 reject_qty,
868 move_tran_rec.shift_id,
869 move_tran_rec.primary_uom_code,
870 move_tran_rec.op_lead_time
871 );
872
873
874 End loop;
875 */
876 wip_ws_util.trace_log(' After loop');
877 l_stmt_no := 120;
878
879 -- aggregate the temp data and insert to the actual table
880 insert into wip_ws_ptpkpi_actual(
881 organization_id,
882 department_id,
883 wip_entity_id,
884 shift_id,
885 operation_seq_num,
886 moved_qty,
887 scrapped_qty,
888 rejected_qty,
889 primary_uom_code,
890 op_lead_time,
891 shift_start_time,
892 concurrent_request_id,
893 last_update_date,
894 last_update_by,
895 creation_date,
896 created_by)
897 select
898 organization_id,
899 department_id,
900 wip_entity_id,
901 shift_id,
902 operation_seq_num,
903 sum(nvl(move_qty,0)) ,
904 sum(nvl(scrap_qty,0)) ,
905 sum(nvl(reject_qty,0)),
906 t.primary_uom_code,
907 t.op_lead_time,
908 WIP_WS_PTPKPI_UTIL.get_datetime_for_shift(p_org_id,shift_id,1),
909 fnd_global.CONC_REQUEST_ID,
910 sysdate,
911 g_user_id,
912 sysdate,
913 g_user_id
914 from wip_ws_ptp_gt t
915 group by
916 organization_id,department_id,wip_entity_id,operation_seq_num,shift_id,primary_uom_code,
917 op_lead_time;
918
919 EXCEPTION
920 when FND_API.G_EXC_UNEXPECTED_ERROR then
921 wip_ws_util.trace_log('Error message = '||sqlerrm);
922 wip_ws_util.trace_log(' Statement no '||l_stmt_no);
923 wip_ws_util.trace_log('Came to unexpected error in populate_actual_data');
924 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
925 when FND_API.G_EXC_ERROR then
926 wip_ws_util.trace_log('Came to expected error in populate_actual_data');
927 wip_ws_util.trace_log(' Statement no '||l_stmt_no);
928 x_return_status := FND_API.G_RET_STS_ERROR;
929 when others then
930 wip_ws_util.trace_log('Error message = '||sqlerrm);
931 wip_ws_util.trace_log('Came to others error in populate_actual_data');
932 wip_ws_util.trace_log(' Statement no '||l_stmt_no);
933 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
934 End populate_actual_data;
935
936
937
938
939
940 END WIP_WS_PTPKPI_PK;