[Home] [Help]
PACKAGE BODY: APPS.WIP_WS_PTPKPI_PK
Source
1 package body WIP_WS_PTPKPI_PK as
2 /* $Header: WIPWSPPB.pls 120.10.12010000.3 2008/09/03 00:21:35 awongwai 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
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 Exception When no_data_found then
518
519 RecentShiftid := null;
520 RecentShiftStartTime := null;
521
522 End;
523
524 l_stmt_no := 30;
525 wip_ws_util.trace_log(' Most recent shift id = '||to_char(RecentShiftid));
526 wip_ws_util.trace_log(' Most recent shift id = '||to_char(RecentShiftStartTime));
527
528 /*
529 If RecentShiftid is not null then
530 -- remove possible partial aggregated data for the most recent shift
531 delete from wip_ws_ptpkpi_actual
532 where shift_start_time >= RecentShiftStartTime;
533 End if;
534 */
535
536 delete from wip_ws_ptpkpi_actual
537 where shift_start_time >= sysdate-7
538 and organization_id = p_org_id;
539 -- process each row and find out whether the quantity should be
540 -- counted as a positive/negative move, scrap, or reject.
541
542 wip_ws_util.trace_log('Before starting For loop');
543
544 l_stmt_no := 40;
545 l_sql := 'select
546 wo.organization_id ,
547 wo.department_id ,
548 wo.wip_entity_id ,
549 wo.operation_seq_num,
550 wmt1.PRIMARY_QUANTITY,
551 WIP_WS_PTPKPI_UTIL.get_shift_id_for_date(
552 wo.organization_id,
553 wo.department_id,
554 null,
555 wmt1.transaction_date
556 ) shift_id,
557 fm_operation_seq_num fm_op,
558 FM_INTRAOPERATION_STEP_TYPE fm_step,
559 to_operation_seq_num to_op,
560 TO_INTRAOPERATION_STEP_TYPE to_step,
561 wmt1.primary_uom primary_uom_code,
562 WIP_WS_PTPKPI_UTIL.get_operation_lead_time(
563 wdj.organization_id,
564 wdj.wip_entity_id,
565 wo.operation_seq_num) op_lead_time,
566 wo.operation_seq_num op
567 from
568 wip_move_transactions wmt1,
569 wip_operations wo,
570 wip_discrete_jobs wdj
571 where
572 wo.organization_id = :p_org_id
573 and wo.organization_id = wmt1.organization_id
574 and wo.wip_entity_id = wdj.wip_entity_id
575 and wo.wip_entity_id = wmt1.wip_entity_id
576 and wo.repetitive_schedule_id is null
577 and wmt1.transaction_date >= sysdate-7
578 and (
579 (
580 wo.operation_seq_num >=
581 wmt1.fm_operation_seq_num +
582 DECODE(SIGN(wmt1.FM_INTRAOPERATION_STEP_TYPE - 2),1,1,0)
583 and wo.operation_seq_num <
584 wmt1.to_operation_seq_num +
585 DECODE(SIGN(wmt1.TO_INTRAOPERATION_STEP_TYPE - 2),1,1,0)
586 and (
587 wmt1.to_operation_seq_num > wmt1.fm_operation_seq_num OR
588 (wmt1.to_operation_seq_num = wmt1.fm_operation_seq_num AND
589 wmt1.FM_INTRAOPERATION_STEP_TYPE <= 2 AND
590 wmt1.TO_INTRAOPERATION_STEP_TYPE > 2)
591 )
592 AND (
593 wo.count_point_type < 3 OR
594 wo.operation_seq_num = wmt1.fm_operation_seq_num OR
595 (wo.operation_seq_num = wmt1.to_operation_seq_num AND
596 wmt1.TO_INTRAOPERATION_STEP_TYPE > 2)
597 )
598 )
599 OR
600 (
601 wo.operation_seq_num <
602 wmt1.fm_operation_seq_num +
603 DECODE(SIGN(wmt1.FM_INTRAOPERATION_STEP_TYPE-2),1,1,0) AND
604 wo.operation_seq_num >=
605 wmt1.to_operation_seq_num +
606 DECODE(SIGN(wmt1.TO_INTRAOPERATION_STEP_TYPE-2),1,1,0) AND
607 (wmt1.fm_operation_seq_num > wmt1.to_operation_seq_num OR
608 (wmt1.fm_operation_seq_num = wmt1.to_operation_seq_num AND
609 wmt1.TO_INTRAOPERATION_STEP_TYPE <= 2 AND
610 wmt1.FM_INTRAOPERATION_STEP_TYPE > 2)
611 ) AND
612 (wo.count_point_type < 3 OR
613 (wo.operation_seq_num = wmt1.to_operation_seq_num AND wo.count_point_type < 3) OR
614 (wo.operation_seq_num = wmt1.fm_operation_seq_num AND wmt1.FM_INTRAOPERATION_STEP_TYPE > 2)
615 )
616 )
617 OR
618 (
619 -- pick up all the returns from scrap/reject for the source operation
620 wmt1.FM_INTRAOPERATION_STEP_TYPE in (4,5) AND
621 wo.operation_seq_num = wmt1.fm_operation_seq_num
622 )
623 )';
624 l_job_status_clause := ' and wdj.status_type in ('|| get_pref_job_statuses(p_org_ptpkpi_rec) || ')';
625 l_job_type_clause := ' and wdj.job_type in ('||get_job_types(p_org_ptpkpi_rec) || ')';
626
627 l_stmt_no := 42;
628 l_sql := l_sql||l_job_status_clause || l_job_type_clause;
629
630 wip_ws_util.trace_log('Sql Statement');
631 wip_ws_util.trace_log(l_sql);
632
633 l_stmt_no := 45;
634 RECENTSHIFTDATE := nvl(RecentShiftStartTime,sysdate-30);
635
636 open move_tran_cur for l_sql using p_org_id;
637 Loop
638 fetch move_tran_cur into l_organization_id,l_department_id,l_wip_entity_id,
639 l_operation_seq_num,l_primary_quantity,l_shift_id,
640 l_fm_op,l_fm_step,l_to_op,l_to_step,l_primary_uom_code,
641 l_lead_time,l_op;
642 wip_ws_util.trace_log(' Inside loop');
643 exit when move_tran_cur%notfound;
644 move_qty := 0;
645 scrap_qty := 0;
646 reject_qty := 0;
647
648 l_stmt_no := 50;
649 if (l_to_step in (1, 2)) then
650 l_stmt_no := 60;
651 if (l_to_op > l_op) then
652 move_qty := l_primary_quantity;
653 elsif (l_to_op < l_op) then
654 move_qty := -1 * l_primary_quantity;
655 elsif (
656 l_to_op <= l_fm_op and
657 l_to_op = l_op and
658 l_FM_step not in (4,5)
659 ) then
660 move_qty := -1 * l_primary_quantity;
661 end if;
662 elsif (l_to_step= 3) then
663 l_stmt_no := 70;
664 if (l_to_op >= l_op) then
665 move_qty := l_primary_quantity;
666 elsif (
667 l_to_op < l_op
668 and( l_FM_step not in (4,5)
669 or l_fm_op <> l_op
670 )
671 ) then
672 move_qty := -1 * l_primary_quantity;
673 end if;
674 elsif (l_to_step= 5) then
675 l_stmt_no := 80;
676 if (l_to_op> l_op) then
677 move_qty := l_primary_quantity ;
678 elsif (l_to_op < l_op) then
679 move_qty := -1 * l_primary_quantity;
680 elsif (l_to_op = l_op) then
681 move_qty := 0;
682 scrap_qty := l_primary_quantity;
683 end if;
684
685 elsif (l_to_step= 4) Then
686 l_stmt_no := 90;
687 if (l_to_op > l_op) Then
688 move_qty := l_primary_quantity ;
689 elsif (l_to_op < l_op) then
690 move_qty := -1 * l_primary_quantity;
691 elsif (l_to_op = l_op) then
692 move_qty := 0;
693 reject_qty := l_primary_quantity;
694 end if;
695 End if;
696 l_stmt_no := 100;
697 if (l_fm_step = 5) then
698 if ( l_fm_op = l_op
699 and l_op >= l_to_op) then
700 scrap_qty := -1 * l_primary_quantity;
701 end if;
702 end if;
703
704 if (l_fm_step = 4) then
705 if (l_fm_op = l_op and
706 l_op >= l_to_op) then
707 reject_qty := -1 * l_primary_quantity;
708 end if;
709 end if;
710
711 l_stmt_no := 110;
712
713
714 wip_ws_util.trace_log(' Org id = '||to_char(l_organization_id));
715 wip_ws_util.trace_log(' dept = '||to_char(l_department_id));
716 wip_ws_util.trace_log(' entity id = '||to_char(l_wip_entity_id));
717 wip_ws_util.trace_log(' op = '||to_char(l_op));
718 wip_ws_util.trace_log(' move qty = '||to_char(move_qty));
719 wip_ws_util.trace_log(' scrap qty = '||to_char(scrap_qty));
720 wip_ws_util.trace_log(' reject qty = '||to_char(reject_qty));
721 wip_ws_util.trace_log(' shift id = '||l_shift_id);
722 wip_ws_util.trace_log(' uom = '||l_primary_uom_code);
723 wip_ws_util.trace_log(' Lead time = '||to_char(l_lead_time));
724
725 If move_qty+scrap_qty+reject_qty <> 0 Then
726 insert into wip_ws_ptp_gt
727 (organization_id,
728 department_id,
729 wip_entity_id,
730 operation_seq_num,
731 move_qty,
732 scrap_qty,
733 reject_qty,
734 shift_id,
735 primary_uom_code,
736 op_lead_time
737 )
738 values(
739 l_organization_id,
740 l_department_id,
741 l_wip_entity_id,
742 l_op,
743 move_qty,
744 scrap_qty,
745 reject_qty,
746 l_shift_id,
747 l_primary_uom_code,
748 l_lead_time
749 );
750 End if;
751
752 End Loop;
753
754 /*
755 For move_tran_rec in move_tran_cur(p_org_id,nvl(RecentShiftStartTime,sysdate-30))
756 Loop
757 wip_ws_util.trace_log('Inside Loop');
758 move_qty := 0;
759 scrap_qty := 0;
760 reject_qty := 0;
761
762 l_stmt_no := 50;
763
764 if (move_tran_rec.to_step in (1, 2)) then
765 l_stmt_no := 60;
766 if (move_tran_rec.to_op > move_tran_rec.op) then
767 move_qty := move_tran_rec.primary_quantity;
768 elsif (move_tran_rec.to_op < move_tran_rec.op) then
769 move_qty := -1 * move_tran_rec.primary_quantity;
770 elsif (
771 move_tran_rec.to_op <= move_tran_rec.fm_op and
772 move_tran_rec.to_op = move_tran_rec.op and
773 move_tran_rec.FM_step not in (4,5)
774 ) then
775 move_qty := -1 * move_tran_rec.primary_quantity;
776 end if;
777 elsif (move_tran_rec.to_step= 3) then
778 l_stmt_no := 70;
779 if (move_tran_rec.to_op >= move_tran_rec.op) then
780 move_qty := move_tran_rec.primary_quantity;
781 elsif (
782 move_tran_rec.to_op < move_tran_rec.op
783 and( move_tran_rec.FM_step not in (4,5)
784 or move_tran_rec.fm_op <> move_tran_rec.op
785 )
786 ) then
787 move_qty := -1 * move_tran_rec.primary_quantity;
788 end if;
789 elsif (move_tran_rec.to_step= 5) then
790 l_stmt_no := 80;
791 if (move_tran_rec.to_op> move_tran_rec.op) then
792 move_qty := move_tran_rec.primary_quantity ;
793 elsif (move_tran_rec.to_op < move_tran_rec.op) then
794 move_qty := -1 * move_tran_rec.primary_quantity;
795 elsif (move_tran_rec.to_op = move_tran_rec.op) then
796 move_qty := 0;
797 scrap_qty := move_tran_rec.primary_quantity;
798 end if;
799
800 elsif (move_tran_rec.to_step= 4) Then
801 l_stmt_no := 90;
802 if (move_tran_rec.to_op > move_tran_rec.op) Then
803 move_qty := move_tran_rec.primary_quantity ;
804 elsif (move_tran_rec.to_op < move_tran_rec.op) then
805 move_qty := -1 * move_tran_rec.primary_quantity;
806 elsif (move_tran_rec.to_op = move_tran_rec.op) then
807 move_qty := 0;
808 reject_qty := move_tran_rec.primary_quantity;
809 end if;
810 End if;
811 l_stmt_no := 100;
812 if (move_tran_rec.fm_step = 5) then
813 if ( move_tran_rec.fm_op = move_tran_rec.op
814 and move_tran_rec.op >= move_tran_rec.to_op) then
815 scrap_qty := -1 * move_tran_rec.primary_quantity;
816 end if;
817 end if;
818
819 if (move_tran_rec.fm_step = 4) then
820 if (move_tran_rec.fm_op = move_tran_rec.op and
821 move_tran_rec.op >= move_tran_rec.to_op) then
822 reject_qty := -1 * move_tran_rec.primary_quantity;
823 end if;
824 end if;
825
826 l_stmt_no := 110;
827
828
829 wip_ws_util.trace_log(' Org id = '||to_char(move_tran_rec.organization_id));
830 wip_ws_util.trace_log(' dept = '||to_char(move_tran_rec.department_id));
831 wip_ws_util.trace_log(' entity id = '||to_char(move_tran_rec.wip_entity_id));
832 wip_ws_util.trace_log(' op = '||to_char(move_tran_rec.op));
833 wip_ws_util.trace_log(' move qty = '||to_char(move_qty));
834 wip_ws_util.trace_log(' scrap qty = '||to_char(scrap_qty));
835 wip_ws_util.trace_log(' reject qty = '||to_char(reject_qty));
836 wip_ws_util.trace_log(' shift id = '||move_tran_rec.shift_id);
837 wip_ws_util.trace_log(' uom = '||move_tran_rec.primary_uom_code);
838 wip_ws_util.trace_log(' Lead time = '||to_char(move_tran_rec.op_lead_time));
839
840 insert into wip_ws_ptp_gt
841 (organization_id,
842 department_id,
843 wip_entity_id,
844 operation_seq_num,
845 move_qty,
846 scrap_qty,
847 reject_qty,
848 shift_id,
849 primary_uom_code,
850 op_lead_time
851 )
852 values(
853 move_tran_rec.organization_id,
854 move_tran_rec.department_id,
855 move_tran_rec.wip_entity_id,
856 move_tran_rec.op,
857 move_qty,
858 scrap_qty,
859 reject_qty,
860 move_tran_rec.shift_id,
861 move_tran_rec.primary_uom_code,
862 move_tran_rec.op_lead_time
863 );
864
865
866 End loop;
867 */
868 wip_ws_util.trace_log(' After loop');
869 l_stmt_no := 120;
870
871 -- aggregate the temp data and insert to the actual table
872 insert into wip_ws_ptpkpi_actual(
873 organization_id,
874 department_id,
875 wip_entity_id,
876 shift_id,
877 operation_seq_num,
878 moved_qty,
879 scrapped_qty,
880 rejected_qty,
881 primary_uom_code,
882 op_lead_time,
883 shift_start_time,
884 concurrent_request_id,
885 last_update_date,
886 last_update_by,
887 creation_date,
888 created_by)
889 select
890 organization_id,
891 department_id,
892 wip_entity_id,
893 shift_id,
894 operation_seq_num,
895 sum(nvl(move_qty,0)) ,
896 sum(nvl(scrap_qty,0)) ,
897 sum(nvl(reject_qty,0)),
898 t.primary_uom_code,
899 t.op_lead_time,
900 WIP_WS_PTPKPI_UTIL.get_datetime_for_shift(p_org_id,shift_id,1),
901 fnd_global.CONC_REQUEST_ID,
902 sysdate,
903 g_user_id,
904 sysdate,
905 g_user_id
906 from wip_ws_ptp_gt t
907 group by
908 organization_id,department_id,wip_entity_id,operation_seq_num,shift_id,primary_uom_code,
909 op_lead_time;
910
911 EXCEPTION
912 when FND_API.G_EXC_UNEXPECTED_ERROR then
913 wip_ws_util.trace_log('Error message = '||sqlerrm);
914 wip_ws_util.trace_log(' Statement no '||l_stmt_no);
915 wip_ws_util.trace_log('Came to unexpected error in populate_actual_data');
916 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
917 when FND_API.G_EXC_ERROR then
918 wip_ws_util.trace_log('Came to expected error in populate_actual_data');
919 wip_ws_util.trace_log(' Statement no '||l_stmt_no);
920 x_return_status := FND_API.G_RET_STS_ERROR;
921 when others then
922 wip_ws_util.trace_log('Error message = '||sqlerrm);
923 wip_ws_util.trace_log('Came to others error in populate_actual_data');
924 wip_ws_util.trace_log(' Statement no '||l_stmt_no);
925 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
926 End populate_actual_data;
927
928
929
930
931
932 END WIP_WS_PTPKPI_PK;