eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.WIP_WS_DL_UTIL

Source


1 package body WIP_WS_DL_UTIL as
2 /* $Header: wipwsdlb.pls 120.26.12020000.3 2012/11/28 07:18:32 sjallipa ship $ */
3 
4   procedure get_first_calendar_date
5   (
6     l_cal_code varchar2,
7     p_date date,
8     x_seq out nocopy number,
9     x_start_date out nocopy date,
10     x_end_date out nocopy date
11   )
12   Is
13     l_next_seq number;
14   Begin
15     select bcd.next_seq_num, bcd.seq_num, bcd.calendar_date, bcd.calendar_date + 1
16     into l_next_seq, x_seq, x_start_date, x_end_date
17     from bom_calendar_dates bcd
18     where bcd.calendar_code = l_cal_code and
19           bcd.exception_set_id = -1 and
20           bcd.calendar_date = trunc(p_date);
21 
22     if( x_seq is null) then
23       select bcd.seq_num, bcd.calendar_date, bcd.calendar_date + 1
24       into x_seq, x_start_date, x_end_date
25       from bom_calendar_dates bcd
26       where bcd.calendar_code = l_cal_code and
27             bcd.exception_set_id = -1 and
28             bcd.seq_num = l_next_seq;
29     end if;
30   End get_first_calendar_date;
31 
32   procedure get_first_calendar_shift
33   (
34     p_cal_code varchar2,
35     p_date date,
36     x_shift_seq out nocopy number,
37     x_shift_num out nocopy number,
38     x_shift_start_date out nocopy date,
39     x_shift_end_date out nocopy date
40   )
41   Is
42     l_cur_date date;
43     l_prior_date date;
44     l_next_date date;
45   Begin
46 
47   /* initialize the out variables */
48   x_shift_seq := null;
49   x_shift_num := null;
50   x_shift_start_date := null;
51   x_shift_end_date := null;
52 
53   /* find out the day that is on */
54   select min(bsd.shift_date)
55   into l_cur_date
56   from bom_shift_dates bsd
57   where bsd.calendar_code = p_cal_code and
58     bsd.shift_date >= trunc( p_date )and
59     bsd.seq_num is not null;
60 
61   /* find out prior and next day in calendar */
62   select max(bsd.shift_date)
63   into l_prior_date
64   from bom_shift_dates bsd
65   where bsd.calendar_code = p_cal_code and
66         bsd.shift_date < l_cur_date and
67         bsd.seq_num is not null;
68 
69   select min(bsd.shift_date)
70   into l_next_date
71   from bom_shift_dates bsd
72   where bsd.calendar_code = p_cal_code and
73         bsd.shift_date > l_cur_date and
74         bsd.seq_num is not null;
75 
76   /* find out the closest shift that is running or going to run */
77   select
78     seq_num,
79     shift_num,
80     shift_date + from_time/(60*60*24),
81     shift_date + to_time/(60*60*24)
82   into
83     x_shift_seq,
84     x_shift_num,
85     x_shift_start_date,
86     x_shift_end_date
87   from
88   (
89     select bsd.shift_date,
90            bsd.shift_num,
91            bsd.seq_num,
92            st.from_time,
93            st.to_time
94     from bom_shift_dates bsd,
95          ( select bst.shift_num,
96                   min(bst.from_time) from_time,
97                   max (bst.to_time + decode(sign(bst.to_time - bst.from_time), -1, (24*60*60), 0) ) to_time
98            from bom_shift_times bst
99            where bst.calendar_code = p_cal_code
100            group by bst.shift_num
101          ) st
102      where bsd.calendar_code = p_cal_code and
103            bsd.shift_num = st.shift_num and
104            (bsd.shift_date + st.to_time / (60 * 60 * 24)) > p_date and
105            bsd.shift_date in (l_cur_date, l_prior_date, l_next_date)
106      order by bsd.shift_date + st.from_time / (60 * 60 * 24)
107    )
108    where rownum = 1;
109 
110 
111   Exception when others then
112     null;
113   End get_first_calendar_shift;
114 
115   procedure get_first_dept_resource_shift
116   (
117     p_cal_code varchar2,
118     p_dept_id number,
119     p_resource_id number,
120     p_date date,
121     x_shift_seq out nocopy number,
122     x_shift_num out nocopy number,
123     x_shift_start_date out nocopy date,
124     x_shift_end_date out nocopy date
125   )
126   Is
127     l_cur_date date;
128     l_prior_date date;
129     l_next_date date;
130   Begin
131 
132   /* initialize the out variables */
133   x_shift_seq := null;
134   x_shift_num := null;
135   x_shift_start_date := null;
136   x_shift_end_date := null;
137 
138   /* find out the day that is on */
139   select min(bsd.shift_date)
140   into l_cur_date
141   from bom_shift_dates bsd, bom_resource_shifts brs
142   where bsd.calendar_code = p_cal_code and
143     bsd.shift_date >= trunc( p_date )and
144     brs.department_id = p_dept_id and
145     brs.resource_id = nvl( p_resource_id, brs.resource_id) and
146     brs.shift_num = bsd.shift_num and
147     bsd.exception_set_id = -1 and
148     bsd.seq_num is not null;
149 
150   /* find out prior and next day in calendar */
151   select max(bsd.shift_date)
152   into l_prior_date
153   from bom_shift_dates bsd, bom_resource_shifts brs
154   where bsd.calendar_code = p_cal_code and
155         bsd.shift_date < l_cur_date and
156         brs.department_id = p_dept_id and
157         brs.resource_id = nvl( p_resource_id, brs.resource_id) and
158         brs.shift_num = bsd.shift_num and
159         bsd.exception_set_id = -1 and
160         bsd.seq_num is not null;
161 
162   select min(bsd.shift_date)
163   into l_next_date
164   from bom_shift_dates bsd, bom_resource_shifts brs
165   where bsd.calendar_code = p_cal_code and
166         bsd.shift_date > l_cur_date and
167         brs.department_id = p_dept_id and
168         brs.resource_id = nvl( p_resource_id, brs.resource_id) and
169         brs.shift_num = bsd.shift_num and
170         bsd.exception_set_id = -1 and
171         bsd.seq_num is not null;
172 
173   /* find out the closest shift that is running or going to run */
174   select
175     seq_num,
176     shift_num,
177     shift_date + from_time/(60*60*24),
178     shift_date + to_time/(60*60*24)
179   into
180     x_shift_seq,
181     x_shift_num,
182     x_shift_start_date,
183     x_shift_end_date
184   from
185   (
186     select bsd.shift_date,
187            bsd.shift_num,
188            bsd.seq_num,
189            st.from_time,
190            st.to_time
191     from bom_shift_dates bsd,
192          ( select bst.shift_num,
193                   min(bst.from_time) from_time,
194                   max (bst.to_time + decode(sign(bst.to_time - bst.from_time), -1, (24*60*60), 0) ) to_time
195            from bom_shift_times bst
196            where bst.calendar_code = p_cal_code
197            group by bst.shift_num
198          ) st ,
199          bom_resource_shifts brs
200      where bsd.calendar_code = p_cal_code and
201            bsd.shift_num = st.shift_num and
202            brs.department_id = p_dept_id and
203            brs.resource_id = nvl( p_resource_id, brs.resource_id) and
204            brs.shift_num = bsd.shift_num and
205            (bsd.shift_date + st.to_time / (60 * 60 * 24)) > p_date and
206            bsd.shift_date in (l_cur_date, l_prior_date, l_next_date)
207      order by bsd.shift_date + st.from_time / (60 * 60 * 24)
208    ) t
209    where rownum = 1;
210 
211 
212   Exception when others then
213     null;
214   End get_first_dept_resource_shift;
215 
216   procedure get_first_shift
217   (
218     p_cal_code varchar2,
219     p_dept_id number,
220     p_resource_id number,
221     p_date date,
222     x_shift_seq out nocopy number,
223     x_shift_num out nocopy number,
224     x_shift_start_date out nocopy date,
225     x_shift_end_date out nocopy date
226   )
227   Is
228   Begin
229      if( p_dept_id is null) then
230        get_first_calendar_shift(p_cal_code, p_date, x_shift_seq, x_shift_num, x_shift_start_date, x_shift_end_date);
231      else
232        get_first_dept_resource_shift(p_cal_code, p_dept_id, p_resource_id, p_date,
233              x_shift_seq, x_shift_num, x_shift_start_date, x_shift_end_date);
234      end if;
235   End get_first_shift;
236 
237   function get_col_job_on_name
238   (
239     p_employee_id number
240   ) return varchar2
241   is
242   begin
243       return wip_ws_util.get_employee_name(p_employee_id, null);
244   end get_col_job_on_name;
245 
246 
247   /* return the sum of qty in both place - direct previous op, the closest check point op
248      , if they are the same, only count it once */
249   function get_col_total_prior_qty
250   (
251     p_wip_entity_id number,
252     p_op_seq number
253   ) return number
254   is
255     l_qty number;
256   begin
257 
258     select sum( wo1.quantity_in_queue + wo1.quantity_running + wo1.quantity_waiting_to_move + wo1.quantity_rejected)
259     into l_qty
260     from wip_operations wo1
261     where wo1.wip_entity_id = p_wip_entity_id and
262           ( wo1.operation_seq_num =
263             ( select wo2.previous_operation_seq_num
264               from wip_operations wo2
265               where wo2.wip_entity_id = p_wip_entity_id and
266                     wo2.operation_seq_num = p_op_seq
267             )
268             or
269             wo1.operation_seq_num =
270             ( select max( wo3.operation_seq_num )
271               from wip_operations wo3
272               where wo3.wip_entity_id = p_wip_entity_id and
273                      wo3.operation_seq_num < p_op_seq and
274                      wo3.count_point_type = 1
275             )
276           );
277 
278     return l_qty;
279   exception when others then
280     return null;
281   end;
282 
283   function get_col_customer
284   (
285     p_org_id number,
286     p_wip_entity_id number
287   ) return varchar2
288   is
289     cursor c_num_customers(p_org_id number, p_wip_entity_id number)
290     Is
291      select count(distinct ool.sold_to_org_id)
292        from HZ_CUST_ACCOUNTS cust_accnt, mtl_reservations mr,
293             mtl_sales_orders mso, oe_order_lines_all ool
294             , wip_discrete_jobs wdj
295        where mso.sales_order_id = mr.demand_source_header_id
296          and mr.demand_source_line_id = ool.line_id
297          and mr.demand_source_type_id = 2
298          and mr.supply_source_type_id = 5
299          and ool.sold_to_org_id = cust_accnt.cust_account_id
300          and mr.supply_source_header_id = wdj.wip_entity_id
301          and mr.organization_id = wdj.organization_id
302          and wdj.organization_id = p_org_id
303          and wdj.wip_entity_id = p_wip_entity_id;
304     cursor c_ustomers(p_org_id number, p_wip_entity_id number)
305     Is
306      select cust_party.party_name
307        from HZ_CUST_ACCOUNTS cust_accnt, HZ_PARTIES cust_party,
308             mtl_reservations mr, mtl_sales_orders mso, oe_order_lines_all ool,
309             wip_discrete_jobs wdj
310        where mso.sales_order_id = mr.demand_source_header_id
311          and mr.demand_source_line_id = ool.line_id
312          and mr.demand_source_type_id = 2
313          and mr.supply_source_type_id = 5
314          and ool.sold_to_org_id = cust_accnt.cust_account_id
315          and cust_party.party_id = cust_accnt.party_id
316          and mr.supply_source_header_id = wdj.wip_entity_id
317          and mr.organization_id = wdj.organization_id
318          and wdj.organization_id = p_org_id
319          and wdj.wip_entity_id = p_wip_entity_id;
320 
321     l_count number;
322     l_name varchar2(256);
323   begin
324     l_name := '';
325     open c_num_customers(p_org_id, p_wip_entity_id);
326     fetch c_num_customers into l_count;
327     close c_num_customers;
328     if (l_count > 1 ) then
329       fnd_message.SET_NAME('WIP', 'WIP_WS_DL_MULTIPLE');
330       return fnd_message.GET;
331     elsif ( l_count = 1 ) then
332       open c_ustomers(p_org_id, p_wip_entity_id);
333       fetch c_ustomers into l_name;
334       close c_ustomers;
335     end if;
336 
337     return(l_name);
338   end;
339 
340   function get_col_sales_order
341   (
342     p_org_id number,
343     p_wip_entity_id number
344   ) return varchar2
345   is
346     cursor c_num_sales_orders(p_org_id number, p_wip_entity_id number)
347     Is
348      select count(distinct mso.segment1)
349      from mtl_reservations mr, mtl_sales_orders mso,
350        oe_order_lines_all ool, wip_discrete_jobs wdj
351      where mso.sales_order_id = mr.demand_source_header_id
352        and mr.demand_source_line_id = ool.line_id
353        and mr.demand_source_type_id = 2
354        and mr.supply_source_type_id = 5
355        and mr.supply_source_header_id = wdj.wip_entity_id
356        and mr.organization_id = wdj.organization_id
357        and wdj.organization_id = p_org_id
358        and wdj.wip_entity_id = p_wip_entity_id;
359 
360     cursor c_sales_orders(p_org_id number, p_wip_entity_id number)
361     Is
362      select mso.concatenated_segments
363      from mtl_reservations mr, mtl_sales_orders_kfv mso,
364        oe_order_lines_all ool, wip_discrete_jobs wdj
365      where mso.sales_order_id = mr.demand_source_header_id
366        and mr.demand_source_line_id = ool.line_id
367        and mr.demand_source_type_id = 2
368        and mr.supply_source_type_id = 5
369        and mr.supply_source_header_id = wdj.wip_entity_id
370        and mr.organization_id = wdj.organization_id
371        and wdj.organization_id = p_org_id
372        and wdj.wip_entity_id = p_wip_entity_id;
373 
374     l_count number;
375     l_name varchar2(256);
376   begin
377     l_name := '';
378     open c_num_sales_orders(p_org_id, p_wip_entity_id);
379     fetch c_num_sales_orders into l_count;
380     close c_num_sales_orders;
381     if (l_count > 1 ) then
382       fnd_message.SET_NAME('WIP', 'WIP_WS_DL_MULTIPLE');
383       return fnd_message.GET;
384     elsif ( l_count = 1 ) then
385       open c_sales_orders(p_org_id, p_wip_entity_id);
386       fetch c_sales_orders into l_name;
387       close c_sales_orders;
388     end if;
389 
390     return(l_name);
391   end;
392 
393   /* need to concatenate the shift seq and shift num to uniquely identify a shift */
394   function get_col_shift_id
395   (
396     p_org_id number,
397     p_dept_id number,
398     p_resource_id number,
399     p_op_date date,
400     p_expedited varchar2,
401     p_first_shift_id varchar2,
402     p_first_shift_end_date date
403   )
404   return varchar2
405   Is
406     l_cal_code varchar2(30);
407 
408     l_shift_seq number;
409     l_shift_num number;
410     l_shift_start_date date;
411     l_shift_end_date date;
412     l_24hr_resource number;
413     l_ret varchar2(30);
414   Begin
415 
416     if( p_expedited = 'Y' ) then
417       return p_first_shift_id;
418     end if;
419 
420     if( p_op_date <= p_first_shift_end_date ) then
421       return p_first_shift_id;
422     end if;
423 
424     select mp.calendar_code
425     into l_cal_code
426     from mtl_parameters mp
427     where mp.organization_id = p_org_id;
428 
429     if( p_resource_id is not null ) then
430       select bdr.available_24_hours_flag
431       into l_24hr_resource
432       from bom_department_resources bdr
433       where bdr.department_id = p_dept_id and
434             bdr.resource_id = p_resource_id;
435     else
436       l_24hr_resource := null;
437     end if;
438 
439     if( l_24hr_resource = 2 ) then
440       get_first_shift(l_cal_code, p_dept_id, p_resource_id, p_op_date,
441         l_shift_seq, l_shift_num, l_shift_start_date, l_shift_end_date);
442 
443       l_ret := l_shift_seq || '.' || l_shift_num;
444     else
445       /*
446       get_first_calendar_date(l_cal_code, p_op_date, l_shift_seq, l_shift_start_date, l_shift_end_date);
447 
448       l_ret := l_shift_seq; */
449       /* if it's 24 hour resource, we treat all jobops as if they are in one shift
450          and the capacity would be 0, since using an arbitary day boundary would be
451          misleading too - per barry's decision */
452       l_ret := 1;
453     end if;
454 
455     return l_ret;
456 
457   end get_col_shift_id;
458 
459   function get_col_exception
460   (
461     p_wip_entity_id number,
462     p_op_seq number
463   ) return varchar
464   is
465     cursor c_num_exceptions(p_wip_entity_id number, p_op_seq number)
466     Is
467       select count(we.exception_id)
468       from wip_exceptions we
469       where we.wip_entity_id = p_wip_entity_id and
470             we.operation_seq_num = p_op_seq and
471             we.status_type = 1;
472 
473     cursor c_exceptions(p_wip_entity_id number, p_op_seq number)
474     Is
475       select ml.MEANING
476       from wip_exceptions we, mfg_lookups ml
477       where we.wip_entity_id = p_wip_entity_id and
478             we.operation_seq_num = p_op_seq and
479             we.status_type = 1 and
480             ml.LOOKUP_CODE = we.exception_type and
481             ml.LOOKUP_TYPE = 'WIP_EXCEPTION_TYPE';
482 
483     l_count number;
484     l_name varchar2(80);
485   begin
486     l_name := '';
487     open c_num_exceptions(p_wip_entity_id, p_op_seq);
488     fetch c_num_exceptions into l_count;
489     close c_num_exceptions;
490     if (l_count > 1 ) then
491       fnd_message.SET_NAME('WIP', 'WIP_WS_DL_MULTIPLE');
492       return fnd_message.GET;
493     elsif ( l_count = 1 ) then
494       open c_exceptions(p_wip_entity_id, p_op_seq);
495       fetch c_exceptions into l_name;
496       close c_exceptions;
497     end if;
498 
499     return(l_name);
500   end;
501 
502   function get_col_project
503   (
504     p_wip_entity_id number
505   ) return varchar
506   is
507     l_name varchar2(100);
508   begin
509     select decode(wdj.project_id, null, null,
510       pjm_project.all_proj_idtonum(wdj.project_id))
511     into l_name
512     from  wip_discrete_jobs wdj
513     where wdj.wip_entity_id = p_wip_entity_id;
514 
515     return l_name;
516   Exception
517     when others then
518     return null;
519   end;
520 
521   function get_col_task
522   (
523     p_wip_entity_id number
524   ) return varchar
525   is
526     l_name varchar2(100);
527   begin
528     select decode(wdj.task_id, null, null,
529         pjm_project.all_task_idtonum(wdj.task_id))
530     into l_name
531     from wip_discrete_jobs wdj
532     where wdj.wip_entity_id = p_wip_entity_id;
533 
534     return l_name;
535   Exception
536     when others then
537     return null;
538   end;
539 
540   /* need to pass in resource id? */
541   function get_col_resource_setup
542   (
543     p_wip_entity_id number,
544     p_op_seq number
545   ) return varchar
546   is
547     cursor c_num_setups(p_wip_entity_id number, p_op_seq number)
548     Is
549       select count(distinct wor.setup_id)
550       from wip_operation_resources wor
551       where wor.wip_entity_id = p_wip_entity_id and
552             wor.operation_seq_num = p_op_seq;
553 
554     cursor c_setups(p_wip_entity_id number, p_op_seq number)
555     Is
556       select bst.setup_code
557       from wip_operation_resources wor, bom_setup_types bst
558       where wor.wip_entity_id = p_wip_entity_id and
559             wor.operation_seq_num = p_op_seq and
560             wor.setup_id = bst.setup_id;
561 
562     l_count number;
563     l_name varchar2(10);
564   begin
565     l_name := '';
566     open c_num_setups(p_wip_entity_id, p_op_seq);
567     fetch c_num_setups into l_count;
568     close c_num_setups;
569     if (l_count > 1 ) then
570       fnd_message.SET_NAME('WIP', 'WIP_WS_DL_MULTIPLE');
571       return fnd_message.GET;
572     elsif ( l_count = 1 ) then
573       open c_setups(p_wip_entity_id, p_op_seq);
574       fetch c_setups into l_name;
575       close c_setups;
576     end if;
577 
578     return(l_name);
579   end;
580 
581   function get_col_component_uom(p_org_id number, p_comp_id number) return varchar2
582   Is
583     l_uom varchar2(3);
584   Begin
585     if( p_comp_id is null) then
586       return null;
587     end if;
588 
589     select msi.primary_uom_code
590     into l_uom
591     from mtl_system_items_b msi
592     where msi.organization_id = p_org_id and
593           msi.inventory_item_id = p_comp_id;
594 
595     return l_uom;
596   End get_col_component_uom;
597 
598   function get_col_component_usage
599   (
600     p_org_id number,
601     p_wip_entity_id number,
602     p_op_seq number,
603     p_comp_id number
604   ) return number
605   is
606     l_qty_open_requirements number;
607     l_qty_required number;
608     l_qty_issued number;
609     l_qty_allocated number;
610     l_qty_per number;
611     l_op_qty number;
612     l_qty_completed number;
613     l_cumulative_scrap_qty number;
614     l_basis_type number;
615     l_yield number;
616 
617     l_qty_tmp number;
618     cursor c_requirements(p_org_id number, p_wip_entity_id number, p_op_seq number, p_com_id number)
619     Is
620       select nvl(wro.basis_type, 1),
621         wro.required_quantity, wro.quantity_issued, wro.quantity_per_assembly,
622         decode(mp.include_component_yield, 1, nvl(wro.component_yield_factor, 1), 1)
623       from wip_requirement_operations wro, wip_parameters mp
624       where wro.organization_id = p_org_id and
625           wro.wip_entity_id = p_wip_entity_id and
626           mp.organization_id = wro.organization_id and
627           wro.operation_seq_num = p_op_seq and
628           wro.inventory_item_id = p_comp_id;
629 
630   begin
631 
632     select  wo.scheduled_quantity, wo.quantity_completed, nvl(wo.cumulative_scrap_quantity, 0)
633     into l_op_qty, l_qty_completed, l_cumulative_scrap_qty
634     from wip_operations wo
635     where wo.organization_id = p_org_id and
636           wo.wip_entity_id = p_wip_entity_id and
637           wo.operation_seq_num = p_op_seq;
638 
639     l_qty_open_requirements := 0;
640     open c_requirements(p_org_id, p_wip_entity_id, p_op_seq, p_comp_id);
641     loop
642       fetch c_requirements into l_basis_type, l_qty_required, l_qty_issued, l_qty_per, l_yield;
643       exit when c_requirements%NOTFOUND;
644 
645       if( l_basis_Type = 1 ) then /* item */
646         l_qty_tmp :=  l_qty_required/l_yield - l_qty_issued - l_qty_per * l_cumulative_scrap_qty/l_yield;
647         if( l_qty_tmp > 0 ) then
648           l_qty_open_requirements := l_qty_open_requirements + l_qty_tmp;
649         end if;
650       else
651         l_qty_tmp := l_qty_required/l_yield - l_qty_issued;
652         if( l_qty_tmp > 0 ) then
653           l_qty_open_requirements := l_qty_open_requirements + l_qty_tmp;
654         end if;
655       end if;
656     end loop;
657     close c_requirements;
658 
659     begin
660       l_qty_allocated := wip_picking_pub.quantity_allocated(p_wip_entity_id => p_wip_entity_id,
661                                                 p_operation_seq_num => p_op_seq,
662                                                 p_organization_id => p_org_id,
663                                                 p_inventory_item_id => p_comp_id,
664                                                 p_repetitive_schedule_id => null,
665                                                 p_quantity_issued => null);
666     exception when others then
667       l_qty_allocated := 0;
668     end;
669 
670     return (l_qty_open_requirements - nvl(l_qty_allocated, 0));
671   Exception
672     when others then
673     return null;
674   end;
675 
676   /* suppose in an operation, no two resource with the same id */
677   function get_actual_work_time
678   (
679     p_wip_entity_id number,
680     p_op_seq_num number,
681     p_resource_seq_num number,
682     p_include_all varchar2/* only include the active time, or even the past time records */
683   ) return number
684   Is
685     l_used_usage number;
686   Begin
687 
688     /* use duration, so it works for machine also */
689     /* also it will reflect the charged resource usage */
690     select sum(wrat.duration)
691     into l_used_usage
692     from wip_resource_actual_times wrat
693     where wrat.wip_entity_id = p_wip_entity_id
694       and wrat.operation_seq_num = p_op_seq_num
695       and wrat.resource_seq_num = p_resource_seq_num
696       and wrat.duration is not null
697       and wrat.process_status <> 4
698       and (p_include_all = 'Y' or status_type = 1);
699 
700     return nvl(l_used_usage, 0);
701   End get_actual_work_time;
702 
703 
704   function get_col_res_usage_req
705   (
706     p_wip_entity_id number,
707     p_op_seq number,
708     p_dept_id number,
709     p_resource_id number,
710     p_resource_seq_num number
711   ) return number
712   is
713     cursor c_dept_resource_usage(p_wip_entity_id number, p_op_seq number, p_dept_id number, p_resource_id number, p_resource_seq_num number)
714     Is
715     select
716         wor.resource_seq_num,
717         wor.basis_type,
718         wdj.start_quantity,
719         wo.cumulative_scrap_quantity,
720         wo.quantity_completed,
721         decode( wip_ws_time_entry.is_time_uom(wor.uom_code), 'Y',
722                inv_convert.inv_um_convert(-1,
723                                   38,
724                                   wor.usage_rate_or_amount,
725                                   wor.uom_code,
726                                   fnd_profile.value('BOM:HOUR_UOM_CODE'),
727                                   NULL,
728                                   NULL),
729                null) usage,
730        decode(mp.include_resource_efficiency, 1, nvl(bdr.efficiency, 1), 1) efficiency,
731        wor.actual_start_date,
732        wor.assigned_units
733   from wip_discrete_jobs       wdj,
734        wip_operations          wo,
735        wip_operation_resources wor,
736        bom_resources           br,
737        bom_department_resources bdr,
738        wip_parameters mp
739  where wdj.wip_entity_id = wo.wip_entity_id and
740        wdj.organization_id = wo.organization_id and
741        mp.organization_id = wdj.organization_id and
742        wo.wip_entity_id = wor.wip_entity_id and
743        wo.organization_id = wor.organization_id and
744        wo.operation_seq_num = wor.operation_seq_num and
745        br.organization_id = wor.organization_id and
746        br.resource_id = wor.resource_id and
747        bdr.resource_id = wor.resource_id and
748        bdr.department_id = nvl(wor.department_id, wo.department_id) and
749        wor.scheduled_flag in (1,3,4) and
750        wdj.status_type in (1,3,6) and
751        wor.wip_entity_id = p_wip_entity_id and
752        wor.operation_seq_num = p_op_seq and
753        nvl(bdr.share_from_dept_id, bdr.department_id ) = p_dept_id and
754        wor.resource_id = p_resource_id and
755        wor.resource_seq_num = nvl(p_resource_seq_num, wor.resource_seq_num);
756 
757     l_job_qty number;
758     l_qty_cumulative_scrap number;
759     l_qty_completed number;
760 
761     l_resource_seq_num number;
762     l_basis_type number;
763     l_usage number;
764     l_efficiency number;
765     l_actual_start date;
766     l_assigned_units number;
767 
768     l_usage_p number;
769     l_ret number;
770   begin
771 
772     open c_dept_resource_usage(p_wip_entity_id, p_op_seq, p_dept_id, p_resource_id, p_resource_seq_num);
773 
774     l_ret := null;
775     loop
776       fetch c_dept_resource_usage
777       into l_resource_seq_num, l_basis_type, l_job_qty, l_qty_cumulative_scrap, l_qty_completed,
778            l_usage , l_efficiency, l_actual_start, l_assigned_units;
779       exit when c_dept_resource_usage%NOTFOUND;
780 
781       l_usage_p := 0;
782 
783       if( l_actual_start is null) then
784         if( l_job_qty <= l_qty_completed + l_qty_cumulative_scrap) then
785             l_usage_p := 0;
786         elsif( l_basis_type = 1 )  then /* item */
787             l_usage_p := (l_job_qty - l_qty_completed - l_qty_cumulative_scrap) * l_usage;
788         else /* lot */
789           if( l_qty_completed + l_qty_cumulative_scrap > 0 ) then
790             l_usage_p := 0;
791           else
792             l_usage_p := l_usage;
793           end if;
794         end if;
795       else
796         if( l_job_qty <= l_qty_completed + l_qty_cumulative_scrap) then
797             l_usage_p := 0;
798         elsif( l_basis_type = 1 )  then /* item */
799             l_usage_p := (l_job_qty - l_qty_cumulative_scrap) * l_usage;
800               /* don't adjust it - (sysdate - l_actual_start) * 24 * l_assigned_units; */
801         else /* lot */
802           if( l_qty_completed > 0 ) then
803             l_usage_p := 0;
804           else
805             l_usage_p := l_usage; /* - (sysdate - l_actual_start) * 24 * l_assigned_units; */
806           end if;
807         end if;
808 
809         if( l_usage_p > 0 ) then
810           if( l_qty_completed = 0 ) then
811             /* no qty has been completed, use actual time entries to adjust the usage */
812             l_usage_p := l_usage_p - get_actual_work_time(p_wip_entity_id, p_op_seq, l_resource_seq_num, 'Y');
813           else
814             /* use the time entry to adjust unless use qty is more accurate */
815             l_usage_p := l_usage_p - greatest( l_qty_completed * l_usage ,
816               get_actual_work_time(p_wip_entity_id, p_op_seq, l_resource_seq_num, 'Y'));
817           end if;
818         end if;
819 
820       end if;  /* end else actual_start_date */
821 
822       if( l_usage_p < 0 ) then
823         l_usage_p := 0;
824       end if;
825 
826       l_ret := nvl(l_ret, 0) + l_usage_p / l_efficiency;
827     end loop;
828 
829     return l_ret;
830 
831   end get_col_res_usage_req;
832 
833   function get_jobop_queue_run_qty
834   (
835     p_wip_entity_id number,
836     p_op_seq_num number
837   ) return number
838   Is
839     l_qty number;
840   Begin
841     select wo.quantity_in_queue + wo.quantity_running
842     into l_qty
843     from wip_operations wo
844     where wo.wip_entity_id = p_wip_entity_id and wo.operation_seq_num = p_op_seq_num;
845 
846     return l_qty;
847   End get_jobop_queue_run_qty;
848 
849   function get_job_released_status
850   (
851     p_wip_entity_id number
852   ) return varchar2
853   Is
854     l_status_type number;
855   Begin
856     select wdj.status_type
857     into l_status_type
858     from wip_discrete_jobs wdj
859     where wdj.wip_entity_id = p_wip_entity_id;
860 
861     if( l_status_type <> 3 ) then /* not released */
862       return 'N';
863     end if;
864     return 'Y';
865   End get_job_released_status;
866 
867   function get_jobop_num_exceptions
868   (
869     p_wip_entity_id number,
870     p_op_seq_num number
871   ) return number
872   Is
873     l_num_exceptions number;
874   Begin
875     select count(*)
876     into l_num_exceptions
877     from wip_exceptions we
878     where we.wip_entity_id = p_wip_entity_id and
879           we.operation_seq_num = p_op_seq_num and
880           we.status_type = 1;
881     return l_num_exceptions;
882   End get_jobop_num_exceptions;
883 
884   function get_jobop_shopfloor_status
885   (
886     p_wip_entity_id number,
887     p_op_seq_num number
888   ) return varchar2
889   Is
890     l_num_shop_status number;
891     l_nomove_step_min number;
892     l_nomove_step_max number;
893 
894   Begin
895     select count(*), min(s.intraoperation_step_type), max(s.intraoperation_step_type)
896     into l_num_shop_status, l_nomove_step_min, l_nomove_step_max
897     from wip_shop_floor_statuses s, wip_shop_floor_status_codes c
898     where s.wip_entity_id = p_wip_entity_id and
899           s.operation_seq_num = p_op_seq_num and
900           s.shop_floor_status_code = c.shop_floor_status_code and
901           s.organization_id = c.organization_id and
902           c.status_move_flag = 2 /* no move */ and
903           nvl(c.disable_date, sysdate+1) > sysdate;
904 
905     if( l_num_shop_status > 0 ) then
906       return 'N'; /* TODO, simplify for now */
907     end if;
908 
909     return 'Y';
910   End get_jobop_shopfloor_status;
911 
912 
913   function get_col_ready_status(
914     p_resp_key varchar2,
915     p_org_id number,
916     p_dept_id number,
917     p_wip_entity_id number,
918     p_op_seq_num number
919   ) return varchar2
920   Is
921       cursor c_pref_values(p_pref_id number, p_level_id number) IS
922         select wpv.attribute_name, wpv.attribute_value_code
923         from wip_preference_values wpv
924         where wpv.preference_id = p_pref_id and
925           wpv.level_id = p_level_id
926         order by wpv.sequence_number;
927 
928       l_c_resp_key varchar2(30) := null;
929       l_c_org_id number := null;
930       l_c_dept_id number := null;
931 
932       l_level_id number;
933       l_value varchar2(10);
934       l_attribute_name varchar2(30);
935 
936       l_c_job_released boolean := true;
937       l_c_no_exceptions boolean := false;
938       l_c_shop_status boolean := false;
939       l_c_qty_queue_run boolean := false;
940 
941       --hooks for flexibility
942       l_custom_ready_status varchar2(1) := 'Y';
943 
944     Begin
945 
946       -- custom ready status integration
947       l_custom_ready_status :=
948         wip_ws_custom.get_custom_ready_status(
949           wip_entity_id => p_wip_entity_id,
950           operation_seq_num => p_op_seq_num,
951           serial_number => null,
952           attribute1 => null,
953           attribute2 => null,
954           attribute3 => null
955         );
956       IF (l_custom_ready_status not in ('Y' , 'y')) THEN
957         RETURN 'N';
958       END IF;
959 
960 
961       if( p_resp_key <> l_c_resp_key or nvl(p_org_id, -1) <> nvl(l_c_org_id, -1) or
962           nvl(p_dept_id, -1) <> nvl(l_c_dept_id, -1) ) then
963       /* re calculate the preference */
964         l_level_id := wip_ws_util.get_preference_level_id(WP_READY_STATUS_CRITERIA, p_resp_key, p_org_id, p_dept_id);
965         open c_pref_values(WP_READY_STATUS_CRITERIA, l_level_id);
966         loop
967           fetch c_pref_values into l_attribute_name, l_value;
968           exit when c_pref_values%NOTFOUND;
969 
970           if( l_value = WP_VALUE_YES) then
971             if( l_attribute_name = 'jobStatus') then
972               l_c_job_released := true;
973 
974             elsif( l_attribute_name = 'exception' ) then
975               l_c_no_exceptions := true;
976 
977             elsif( l_attribute_name = 'compAvail' ) then
978               null;
979 
980             elsif( l_attribute_name = 'sfStatus' ) then
981               l_c_shop_status := true;
982 
983             elsif( l_attribute_name = 'qtyQueRun' ) then
984               l_c_qty_queue_run := true;
985 
986             else
987               null;
988 /*              dbms_output.put_line('Unknow ready status criteria ' || l_attribute_name); */
989 
990             end if;
991           end if;
992 
993         end loop;
994         close c_pref_values;
995       end if;
996 
997      /* check qty in queue and run */
998      if( l_c_qty_queue_run and get_jobop_queue_run_qty(p_wip_entity_id, p_op_seq_num) = 0 ) then
999        return 'N';
1000      end if;
1001 
1002      /* check job status */
1003      if( l_c_job_released and get_job_released_status(p_wip_entity_id) = 'N' ) then
1004        return 'N';
1005      end if;
1006 
1007      /* check exceptions */
1008      if( l_c_no_exceptions and get_jobop_num_exceptions(p_wip_entity_id, p_op_seq_num) > 0 ) then
1009        return 'N';
1010      end if;
1011 
1012      /* check shop status */
1013      /* TODO, check if the no move is after the qty? */
1014      if( l_c_shop_status ) then
1015        if( get_jobop_shopfloor_status(p_wip_entity_id, p_op_seq_num) = 'N') then
1016          return 'N';
1017        end if;
1018      end if;
1019 
1020      return 'Y';
1021 
1022     End get_col_ready_status;
1023 
1024   /* interal apis */
1025   procedure add_string(x_all in out nocopy varchar2, p_str varchar2)
1026   is
1027   Begin
1028       x_all := x_all || p_str;
1029   End add_string;
1030 
1031   procedure add_string
1032   (
1033     x_all in out nocopy varchar2,
1034     p_delim varchar2,
1035     p_str varchar2
1036   )
1037   is
1038   Begin
1039     if( x_all is null) then
1040       x_all := p_str;
1041     else
1042       x_all := x_all || p_delim || p_str;
1043     end if;
1044   End add_string;
1045 
1046   procedure add_bind
1047   (
1048     x_binds in out nocopy varchar2,
1049     p_var varchar2,
1050     x_num in out nocopy number
1051   )
1052   is
1053   Begin
1054     x_num := x_num + 1;
1055     add_string(x_binds, ',', p_var);
1056   End add_bind;
1057 
1058   procedure add_where
1059   (
1060     x_where in out nocopy varchar2,
1061     p_line varchar2
1062   )
1063   is
1064   Begin
1065     if( x_where is not null) then
1066       x_where := x_where || ' and ';
1067     end if;
1068 
1069     x_where := x_where || p_line;
1070   End add_where;
1071 
1072 
1073 
1074   procedure build_dispatch_list_sql
1075   (
1076     p_resp_key varchar2,
1077     p_org_id number,
1078     p_dept_id number,
1079     p_resource_id number,
1080     p_instance_option number,
1081     p_instance_id number,
1082     p_serial_number varchar2,
1083     p_list_mode number,
1084     p_from_date date,
1085     p_to_date date,
1086     p_job_type number,
1087     p_component_id number,
1088     p_bind_number number,
1089     x_where_clause in out nocopy varchar2,
1090     x_bind_variables in out nocopy varchar2,
1091     x_order_by_columns in out nocopy varchar2,
1092     x_order_by_clause in out nocopy varchar2,
1093     x_bind_var_num in out nocopy number,  --Bug 12800454
1094     x_required in varchar2 default null			--Bug -7364131
1095 
1096   )
1097   Is
1098   Begin
1099 
1100     build_dispatch_list_where
1101     (
1102       p_resp_key, p_org_id, p_dept_id, p_resource_id,
1103       p_instance_option, p_instance_id, p_serial_number,
1104       p_list_mode, p_from_date, p_to_date, p_job_type,
1105       p_component_id,
1106       p_bind_number,
1107       x_where_clause,
1108       x_bind_variables,
1109       x_bind_var_num,   --Bug 12800454
1110       x_required				--Bug -7364131
1111 
1112     );
1113 
1114     build_dispatch_list_order_by
1115     (
1116       p_resp_key,
1117       p_org_id,
1118       p_dept_id,
1119       x_order_by_columns,
1120       x_order_by_clause
1121     );
1122   End build_dispatch_list_sql;
1123 
1124 
1125   procedure build_dispatch_list_order_by
1126   (
1127     p_resp_key varchar2,
1128     p_org_id number,
1129     p_dept_id number,
1130     x_order_by_columns in out nocopy varchar2,
1131     x_order_by_clause in out nocopy varchar2
1132   )
1133   is
1134 
1135   cursor c_pref_order_by(p_pref_id number, p_level_id number) IS
1136   select
1137       v1.attribute_value_code,
1138       v2.attribute_value_code,
1139       v3.attribute_value_code,
1140       v4.attribute_value_code
1141     from wip_preference_values v1, wip_preference_values v2, wip_preference_values v3, wip_preference_values v4
1142     where
1143       v1.preference_id = p_pref_id and
1144       v2.preference_id = p_pref_id and
1145       v3.preference_id = p_pref_id and
1146       v4.preference_id = p_pref_id and
1147       v1.level_id = p_level_id and
1148       v2.level_id = p_level_id and
1149       v3.level_id = p_level_id and
1150       v4.level_id = p_level_id and
1151       v1.attribute_name = 'attribute' and
1152       v2.attribute_name = 'column' and
1153       v3.attribute_name = 'direction' and
1154       v4.attribute_name = 'ignoreTime' and
1155       v1.sequence_number = v2.sequence_number and
1156       v2.sequence_number = v3.sequence_number and
1157       v3.sequence_number = v4.sequence_number
1158     order by v1.sequence_number;
1159 
1160     l_columns varchar2(4096);
1161     l_orderby varchar2(4096);
1162     l_level_id number;
1163     l_attribute_code varchar2(256);
1164     l_column varchar2(256);
1165     l_direction varchar2(256);
1166     l_ignoreTime varchar2(1);
1167 
1168     l_tmp varchar2(100);
1169   Begin
1170     l_columns := 'expedited';
1171     l_orderby := 'expedited';
1172 
1173     l_level_id := wip_ws_util.get_preference_level_id(WP_DL_ORDERING_CRITERIA, p_resp_key, p_org_id, p_dept_id);
1174 
1175     open c_pref_order_by(WP_DL_ORDERING_CRITERIA, l_level_id);
1176 
1177     loop
1178       fetch c_pref_order_by
1179       into l_attribute_code, l_column, l_direction, l_ignoreTime;
1180 
1181       exit when c_pref_order_by%NOTFOUND;
1182 
1183       add_string(l_columns, ',', l_column);
1184 
1185       l_tmp := l_column;
1186       if( l_ignoreTime is not null and l_ignoreTime = WP_VALUE_YES ) then
1187         l_tmp := 'trunc(' || l_tmp || ')';
1188       end if;
1189 
1190       if( l_direction is not null and l_direction = WP_VALUE_DIRECTION_DOWN ) then
1191         add_string(l_tmp, ' desc');
1192       else
1193         add_string(l_tmp, ' asc');
1194       end if;
1195 
1196       add_string(l_orderby, ', ', l_tmp);
1197     end loop;
1198 
1199     close c_pref_order_by;
1200 
1201     x_order_by_columns := l_columns;
1202     x_order_by_clause := l_orderby;
1203 
1204   End build_dispatch_list_order_by;
1205 
1206   procedure build_dispatch_list_where
1207   (
1208     p_resp_key varchar2,
1209     p_org_id number,
1210     p_dept_id number,
1211     p_resource_id number,
1212     p_instance_assigned number,
1213     p_instance_id number,
1214     p_serial_number varchar2,
1215     p_list_mode number,
1216     p_from_date date,
1217     p_to_date date,
1218     p_job_type number,
1219     p_component_id number,
1220     p_bind_number number,
1221     x_where_clause in out nocopy varchar2,
1222     x_bind_variables in out nocopy varchar2,
1223     x_bind_var_num in out nocopy number,  --Bug 12800454
1224     x_required in varchar2			--Bug -7364131
1225 
1226   )
1227   is
1228 
1229   cursor c_pref_values(p_pref_id number, p_level_id number, p_attribute varchar2) IS
1230     select wpv.attribute_value_code
1231     from wip_preference_values wpv
1232     where wpv.preference_id = p_pref_id and
1233       wpv.level_id = p_level_id and
1234       ( p_attribute is null
1235         or wpv.attribute_name = p_attribute);
1236 
1237   l_where varchar2(4096);
1238   l_binds varchar2(2048);
1239   l_num number;
1240   l_level_id number;
1241 
1242   l_status varchar2(20) := '';
1243   l_value varchar2(10) := '';
1244   l_include_to_move boolean;
1245 
1246   l_tmp varchar2(2048) := '';
1247 
1248   Begin
1249 
1250     l_num := p_bind_number;
1251     l_where := '';
1252     l_binds := '';
1253 
1254     -- read the job type
1255     if( p_job_type is not null) then
1256       add_where(l_where, '  job_type = ' || p_job_type);
1257     end if;
1258 
1259     -- read preference, add job status type
1260     l_level_id := wip_ws_util.get_preference_level_id(WP_JOB_STATUS, p_resp_key, p_org_id, p_dept_id);
1261     open c_pref_values(WP_JOB_STATUS, l_level_id, null);
1262     loop
1263       fetch c_pref_values into l_value;
1264       exit when c_pref_values%NOTFOUND;
1265       add_string(l_status, ', ', l_value);
1266     end loop;
1267     add_where(l_where, '  status_type in (' || l_status || ')' );
1268 
1269     -- if org has set
1270     if( p_org_id is not null ) then
1271       add_where(l_where, '  organization_id = :' || l_num);
1272       add_bind(l_binds, 'organization_id', l_num);
1273     end if;
1274 
1275     -- if instance has set
1276     if( (p_instance_assigned = 3 or p_instance_id = 4) and p_instance_id is not null ) then
1277       l_tmp :=
1278         '  exists   ' ||
1279         '  (   ' ||
1280         '    select 1  ' ||
1281         '    from wip_operation_resources   wor1,  ' ||
1282         '         wip_op_resource_instances wori1  ' ||
1283         '    where qrslt.wip_entity_id = wor1.wip_entity_id  ' ||
1284         '      and qrslt.organization_id = wor1.organization_id  ' ||
1285         '      and qrslt.operation_seq_num = wor1.operation_seq_num  ' ||
1286         '      and wor1.wip_entity_id = wori1.wip_entity_id  ' ||
1287         '      and wor1.operation_seq_num = wori1.operation_seq_num  ' ||
1288         '      and wor1.resource_seq_num = wori1.resource_seq_num  ' ||
1289         '      and wori1.instance_id = :' || l_num || '  ';
1290 
1291       add_bind(l_binds, 'instance_id', l_num);
1292       if( p_serial_number is not null ) then
1293         add_string(l_tmp, '      and wori1.serial_number = :' || l_num || '  ');
1294         add_bind(l_binds, 'serial_number', l_num);
1295       end if;
1296 
1297       add_string(l_tmp, '  )  ');
1298       add_where(l_where, l_tmp);
1299 
1300     -- if the resource is set
1301     elsif ( p_resource_id is not null ) then
1302       add_where(l_where,
1303         '  exists  ' ||
1304         '  (  ' ||
1305         '    select 1  ' ||
1306         '    from wip_operation_resources wor1, bom_department_resources bdr  ' ||
1307         '     where wor1.wip_entity_id = qrslt.wip_entity_id  ' ||
1308         '       and wor1.organization_id = qrslt.organization_id  ' ||
1309         '       and wor1.operation_seq_num = qrslt.operation_seq_num  ' ||
1310         '       and bdr.department_id = qrslt.department_id   ' ||
1311         '       and nvl(wor1.department_id, nvl(bdr.share_from_dept_id, bdr.department_id)) = :' || l_num || '  ' ||
1312         '       and wor1.resource_id = :' || (l_num + 1) || '  ' ||
1313         '  )  '
1314       );
1315       add_bind(l_binds, 'department_id', l_num);
1316       add_bind(l_binds, 'resource_id', l_num);
1317 
1318     -- if dept has set
1319     elsif ( p_dept_id is not null ) then
1320       add_where(l_where, '  department_id = :' || l_num);
1321       add_bind(l_binds, 'department_id', l_num);
1322     end if;
1323 
1324     if( p_instance_assigned = 2) then -- not assigned
1325       if (p_instance_assigned = 2 ) then
1326         l_tmp := '  not exists  ';
1327       else
1328         l_tmp := '  exists  ';
1329       end if;
1330 
1331       add_string
1332       (l_tmp,
1333         '  (   ' ||
1334         '    select 1  ' ||
1335         '    from wip_operation_resources wor1,   ' ||
1336         '         wip_op_resource_instances wori1  ' ||
1337         '    where qrslt.wip_entity_id = wor1.wip_entity_id  ' ||
1338         '      and qrslt.organization_id = wor1.organization_id  ' ||
1339         '      and qrslt.operation_seq_num = wor1.operation_seq_num  ' ||
1340         '      and wor1.wip_entity_id = wori1.wip_entity_id  ' ||
1341         '      and wor1.operation_seq_num = wori1.operation_seq_num  ' ||
1342         '      and wor1.resource_seq_num = wori1.resource_seq_num ' ||
1343         '  )  '
1344       );
1345       add_where(l_where, l_tmp);
1346     end if;
1347 
1348     -- read preference, include complete or not
1349     l_level_id := wip_ws_util.get_preference_level_id(WP_INCLUDE_COMPLETE_QTY, p_resp_key, p_org_id, p_dept_id);
1350     if( wip_ws_util.get_preference_value_code(WP_INCLUDE_COMPLETE_QTY, l_level_id) = WP_VALUE_YES ) then
1351       l_include_to_move := true;
1352     else
1353       l_include_to_move := false;
1354     end if;
1355 
1356     if (p_list_mode = LIST_MODE_SCHEDULED) then
1357       l_tmp := '';
1358       if( p_to_date is not null) then
1359         add_string(l_tmp, '      first_unit_start_date < :' || l_num);
1360         add_bind(l_binds, 'to_date', l_num);
1361       end if;
1362 
1363       if ( p_from_date is not null ) then
1364         add_string(l_tmp, ' and ', '      first_unit_start_date >= :' || l_num);
1365         add_bind(l_binds, 'from_date', l_num);
1366       end if;
1367 
1368       if( l_tmp is not null ) then
1369         add_where(l_where, '  ( expedited = ''Y'' or (' || l_tmp || ' ) )' );
1370       end if;
1371 
1372       if( l_include_to_move ) then
1373 	-- Bug 15913860
1374 	    add_where(l_where, '     ( (quantity_in_queue > 0 or quantity_running > 0) or quantity_waiting_to_move > 0 or (start_quantity - quantity_completed - cumulative_scrap_quantity > 0))');
1375 	  --Fix bug 13826369
1376        -- add_where(l_where, '     ( quantity_in_queue > 0 or quantity_running > 0 or quantity_waiting_to_move > 0 )');
1377         --add_where(l_where, '     ( quantity_waiting_to_move > 0 or start_quantity - quantity_completed - cumulative_scrap_quantity > 0 )');
1378       else
1379 	  	-- Bug 15913860
1380 	    add_where(l_where, '     (( quantity_in_queue > 0 or quantity_running > 0 ) or (start_quantity - quantity_completed - cumulative_scrap_quantity > 0 ))');
1381         --Fix bug 13826369
1382        -- add_where(l_where, '     ( quantity_in_queue > 0 or quantity_running > 0 )');
1383         --add_where(l_where, '     (start_quantity - quantity_completed - cumulative_scrap_quantity > 0 )');
1384       end if;
1385 
1386     elsif ( p_list_mode = LIST_MODE_CURRENT) then
1387       l_tmp := '';
1388 
1389 
1390       if( p_to_date is not null ) then
1391         add_string(l_tmp, 'first_unit_start_date <:'|| l_num);
1392         add_bind(l_binds, 'to_date', l_num);
1393       end if;
1394 
1395       if( p_from_date is not null ) then
1396         add_string(l_tmp, ' and ', 'first_unit_start_date >= :' || l_num);
1397         add_bind(l_binds, 'from_date', l_num);
1398       end if;
1399 
1400       if( l_tmp is not null ) then
1401         l_tmp := '  (expedited = ''Y'' or ( ' || l_tmp || ' )) ';
1402       end if;
1403 
1404       if x_required is null then				--Bug -7364131
1405 	      if( l_include_to_move ) then
1406 		add_string(l_tmp, ' and ', '( quantity_in_queue > 0 or quantity_running > 0 or quantity_waiting_to_move > 0)');
1407 	      else
1408 		add_string(l_tmp, ' and ', '( quantity_in_queue > 0 or quantity_running > 0 )');
1409 	      end if ;
1410       end if ;							--Bug -7364131
1411 
1412       add_where(l_where, '  (      ' || l_tmp || '   )  ');
1413     else -- upstream
1414       l_tmp := '';
1415       if( p_to_date is not null ) then
1416         add_string(l_tmp, 'first_unit_start_date <:'|| l_num);
1417         add_bind(l_binds, 'to_date', l_num);
1418       end if;
1419 
1420       if( p_from_date is not null ) then
1421         add_string(l_tmp, ' and ', 'first_unit_start_date >= :' || l_num);
1422         add_bind(l_binds, 'from_date', l_num);
1423       end if;
1424 
1425       if( l_tmp is not null ) then
1426         l_tmp := '  (expedited = ''Y'' or ( ' || l_tmp || ' )) ';
1427       end if;
1428 
1429       add_string(l_tmp, ' and ', '( ');
1430       add_string(l_tmp, '   0 < (select (wo1.quantity_in_queue + wo1.quantity_running + wo1.quantity_waiting_to_move + wo1.quantity_rejected) ');
1431       add_string(l_tmp, '      from wip_operations wo1 where wo1.operation_seq_num = ');
1432       add_string(l_tmp, '        ( select max(wo2.operation_seq_num) from wip_operations wo2 ');
1433       add_string(l_tmp, '          where wo2.count_point_type = 1 and ');
1434       add_string(l_tmp, '            wo2.operation_seq_num < qrslt.operation_seq_num and ');
1435       add_string(l_tmp, '            wo2.wip_entity_id = wo1.wip_entity_id and ');
1436       add_string(l_tmp, '             wo2.organization_id = wo1.organization_id ');
1437       add_string(l_tmp, '        ) and  wo1.wip_entity_id = qrslt.wip_entity_id )' );
1438       add_string(l_tmp, '   or ');
1439       add_string(l_tmp, '   0 < (select (wo1.quantity_in_queue + wo1.quantity_running + wo1.quantity_waiting_to_move + wo1.quantity_rejected) ');
1440       add_string(l_tmp, '      from wip_operations wo1, wip_operations wo2 ');
1441       add_string(l_tmp, '        where wo2.previous_operation_seq_num = wo1.operation_seq_num and ');
1442       add_string(l_tmp, '              wo2.operation_seq_num = qrslt.operation_seq_num and ');
1443       add_string(l_tmp, '              wo2.wip_entity_id = qrslt.wip_entity_id and ');
1444       add_string(l_tmp, '              wo2.wip_entity_id = wo1.wip_entity_id )' );
1445       add_string(l_tmp, '   ) ');
1446       add_where(l_where, l_tmp);
1447     end if;
1448    null;
1449 
1450    x_where_clause := l_where;
1451    x_bind_variables := l_binds;
1452    x_bind_var_num := l_num;
1453    -- 12800454 : l_num represents the location in the array that the bind variables are stored in
1454    -- this is passed back to the function to be used when the MoreOptions are used.
1455 
1456   End build_dispatch_list_where;
1457 
1458 
1459   procedure expedite
1460   (
1461     p_wip_entity_id number,
1462     p_op_seq_num number,
1463     x_status in out nocopy varchar2,
1464     x_msg_count in out nocopy number,
1465     x_msg in out nocopy number
1466   )
1467   Is
1468    l_expedited varchar2(1);
1469   Begin
1470     fnd_msg_pub.Initialize;
1471 
1472     x_status := '';
1473     x_msg_count := 0;
1474     x_msg := '';
1475 
1476     select wdj.expedited
1477     into l_expedited
1478     from wip_discrete_jobs wdj
1479     where wdj.wip_entity_id = p_wip_entity_id;
1480 
1481     if( l_expedited is null or l_expedited = 'N') then
1482       update wip_discrete_jobs wdj
1483       set wdj.expedited = 'Y'
1484       where wdj.wip_entity_id = p_wip_entity_id;
1485       commit;
1486     else
1487       x_status := 'A';
1488       fnd_message.SET_NAME('WIP', 'WS_JOBOP_ALR_EXPEDITED');
1489       fnd_msg_pub.Add;
1490     end if;
1491 
1492   End;
1493 
1494   procedure unexpedite
1495   (
1496     p_wip_entity_id number,
1497     p_op_seq_num number,
1498     x_status in out nocopy varchar2,
1499     x_msg_count in out nocopy number,
1500     x_msg in out nocopy number
1501   )
1502   Is
1503    l_expedited varchar2(1);
1504   Begin
1505     fnd_msg_pub.Initialize;
1506 
1507     x_status := '';
1508     x_msg_count := 0;
1509     x_msg := '';
1510 
1511     select wdj.expedited
1512     into l_expedited
1513     from wip_discrete_jobs wdj
1514     where wdj.wip_entity_id = p_wip_entity_id;
1515 
1516     if( l_expedited = 'Y') then
1517       update wip_discrete_jobs wdj
1518       set wdj.expedited = null /* set to N dosn't help on order */
1519       where wdj.wip_entity_id = p_wip_entity_id;
1520       commit;
1521     else
1522       x_status := 'A';
1523       fnd_message.SET_NAME('WIP', 'WS_JOBOP_ALR_UNEXPEDITED');
1524       fnd_msg_pub.Add;
1525     end if;
1526 
1527   End;
1528 
1529 
1530   /* need to concatenate the shift seq and shift num to uniquely identify a shift */
1531   function get_first_shift_id(p_org_id number, p_dept_id number, p_resource_id number)
1532   return varchar2
1533   Is
1534     l_ret varchar2(60);
1535 
1536     l_date date;
1537     l_seq number;
1538     l_num number;
1539     l_shift_start_date date;
1540     l_shift_end_date date;
1541     l_str varchar2(60);
1542   Begin
1543 
1544     l_date := sysdate;
1545 
1546     wip_ws_util.retrieve_first_shift(p_org_id, p_dept_id, p_resource_id, l_date, l_seq, l_num, l_shift_start_date, l_shift_end_date, l_str);
1547 
1548     l_ret := l_seq || '.' || l_num;
1549 
1550     return l_ret;
1551 
1552   End get_first_shift_id;
1553 
1554   procedure batch_move_add(
1555     p_index number,
1556     p_wip_entity_id number,
1557     p_wip_entity_name varchar2,
1558     p_op_seq varchar2,
1559     p_move_qty number,
1560     p_scrap_qty number,
1561     p_assy_serial varchar2 default null,
1562     x_return_status out nocopy varchar2
1563   )
1564   Is
1565   Begin
1566     if( p_index = 1 ) then
1567       l_move_table.delete;
1568     end if;
1569 
1570     l_move_table(p_index).wip_entity_id := p_wip_entity_id;
1571     l_move_table(p_index).wip_entity_name := p_wip_entity_name;
1572     l_move_table(p_index).op_seq := p_op_seq;
1573     l_move_table(p_index).move_qty := p_move_qty;
1574     l_move_table(p_index).scrap_qty := p_scrap_qty;
1575     l_move_table(p_index).assy_serial := p_assy_serial;
1576 
1577     x_return_status := 'S';
1578   Exception when others then
1579     x_return_status := 'U';
1580   End batch_move_add;
1581 
1582   procedure batch_move_process
1583   (
1584     p_resp_key varchar2,
1585     p_org_id number,
1586     p_dept_id number,
1587     p_employee_id number,
1588     x_return_status out nocopy varchar2
1589   )
1590   Is
1591   Begin
1592     wip_batch_move.process(l_move_table, p_resp_key, p_org_id, p_dept_id, p_employee_id, x_return_status);
1593   End batch_move_process;
1594 
1595   function get_shift_capacity
1596   (
1597     p_org_id number,
1598     p_dept_id number,
1599     p_resource_id number,
1600     p_shift_seq number,
1601     p_shift_num number
1602   )  return number
1603   Is
1604     l_cal_code varchar2(30);
1605     l_cal_exception_id number;
1606     l_date date;
1607     l_shift_date date;
1608     l_total_time number;
1609     l_units number;
1610     l_utilizaiton number;
1611   Begin
1612 
1613     select mp.calendar_code, mp.calendar_exception_set_id
1614     into l_cal_code, l_cal_exception_id
1615     from mtl_parameters mp
1616     where mp.organization_id = p_org_id;
1617 
1618     if( p_shift_num is not null) then
1619       /* use bom_shift_dates*/
1620       l_date := sysdate;
1621 
1622       select shift_date
1623       into l_shift_date
1624       from bom_shift_dates bsd
1625       where bsd.calendar_code = l_cal_code and
1626             bsd.exception_set_id = l_cal_exception_id and
1627             bsd.seq_num = p_shift_seq and
1628             bsd.shift_num = p_shift_num;
1629 
1630       /* adjust the time with sysdate */
1631       select sum( 24* (to_date - from_date)) total_time
1632       into l_total_time
1633       from
1634              ( select GREATEST(l_date, l_shift_date + from_time/(24*60*60)) from_date,
1635                       l_shift_date + to_time/(24*60*60) + decode(sign(to_time - from_time), -1, 1, 0) to_date
1636                  from bom_shift_times bst
1637                  where bst.calendar_code = l_cal_code and
1638                        bst.shift_num = p_shift_num
1639              ) sd
1640       where sd.from_date <= sd.to_date;
1641 
1642       select brs.capacity_units
1643       into l_units
1644       from bom_resource_shifts brs
1645       where brs.department_id = p_dept_id and
1646           brs.resource_id = p_resource_id and
1647           brs.shift_num = p_shift_num;
1648     else
1649       /* 24 hour resource */
1650       /*
1651       select bcd.calendar_date
1652       into l_shift_date
1653       from bom_calendar_dates bcd
1654       where bcd.calendar_code = l_cal_code and
1655             bcd.seq_num = p_shift_seq;
1656 
1657       if( l_shift_date < trunc(sysdate) ) then
1658         l_total_time := 0;
1659       elsif (l_shift_date > trunc(sysdate) ) then
1660         l_total_time := 24;
1661       else
1662         l_total_time := 24 - (sysdate - l_shift_date)*24;
1663       end if;
1664       */
1665       /* per barry's decision, using 0 as the capacity for 24hr resource */
1666       l_total_time := 0;
1667 
1668       l_units := null;
1669     end if;
1670 
1671     select nvl(l_units, bdr.capacity_units),
1672            decode(wp.include_resource_utilization, wip_constants.yes, nvl(bdr.utilization, 1), 1)
1673     into l_units, l_utilizaiton
1674     from bom_department_resources bdr, wip_parameters wp
1675     where bdr.department_id = p_dept_id and
1676           bdr.resource_id = p_resource_id and
1677           wp.organization_id = p_org_id;
1678 
1679     if( l_units is null ) then
1680       l_units := 1;
1681     end if;
1682 
1683     return l_units * l_total_time * l_utilizaiton;
1684   Exception
1685     when others then
1686       return 0;
1687   End get_shift_capacity;
1688 
1689   /* for home page capacity table */
1690   function get_cap_num_ns_jobs
1691   (
1692     p_resp_key varchar2,
1693     p_org_id number,
1694     p_department_id number,
1695     p_resource_id number,
1696     p_shift_num number,
1697     p_from_date date,
1698     p_to_date date
1699   ) return number
1700   Is
1701     l_num number;
1702 
1703     l_bind_num number := 1;
1704     l_list_mode number;
1705 
1706     x_where_clause varchar2(4096);
1707     x_bind_variables varchar2(1024);
1708     x_order_by_columns varchar2(1024);
1709     x_order_by_clause varchar2(1024);
1710     x_bind_var_num number;
1711 
1712     l_index number;
1713     l_pos number;
1714     l_var varchar2(256);
1715     l_sql varchar(2048);
1716     l_cursor integer;
1717     l_dummy integer;
1718   Begin
1719 
1720     l_list_mode := wip_ws_util.get_preference_value_code(WIP_WS_DEFAULT_DL_TYPE, p_resp_key, p_org_id, p_department_id);
1721 
1722     l_bind_num := 3;
1723 
1724     wip_ws_dl_util.build_dispatch_list_sql(p_resp_key => p_resp_key,
1725                                          p_org_id => p_org_id,
1726                                          p_dept_id => p_department_id,
1727                                          p_resource_id => p_resource_id,
1728                                          p_instance_option => 1, /* all */
1729                                          p_instance_id => null,
1730                                          p_serial_number => null,
1731                                          p_list_mode => l_list_mode,
1732                                          p_from_date => p_from_date,
1733                                          p_to_date => p_to_date,
1734                                          p_job_type => 1,
1735                                           p_component_id => null,
1736                                          p_bind_number => l_bind_num,
1737                                          x_where_clause => x_where_clause,
1738                                          x_bind_variables => x_bind_variables,
1739                                          x_order_by_columns => x_order_by_columns,
1740                                          x_order_by_clause => x_order_by_clause,
1741                                          x_bind_var_num => x_bind_var_num);
1742 
1743     l_sql := 'select count(*) ';
1744     l_sql := l_sql || 'from ( ';
1745     l_sql := l_sql || 'select wo.organization_id, wo.wip_entity_id, wo.operation_seq_num, wo.department_id, ';
1746     l_sql := l_sql || '       wo.first_unit_start_date, wo.last_unit_completion_date, ';
1747     l_sql := l_sql || '       wo.quantity_in_queue, wo.quantity_running, wo.quantity_waiting_to_move, wo.cumulative_scrap_quantity, ';
1748     l_sql := l_sql || ' wdj.start_quantity, wdj.quantity_completed, wdj.expedited, wdj.job_type, wdj.status_type ';
1749     l_sql := l_sql || 'from wip_operations wo, wip_discrete_jobs wdj ';
1750     l_sql := l_sql || 'where wo.organization_id = wdj.organization_id and ';
1751     l_sql := l_sql || '      wo.wip_entity_id = wdj.wip_entity_id and ';
1752     l_sql := l_sql || '      wo.actual_start_date is null ';
1753     l_sql := l_sql || ' ) qrslt ';
1754     l_sql := l_sql || ' where ';
1755     l_sql := l_sql || x_where_clause;
1756 
1757     l_cursor := dbms_sql.open_cursor;
1758     dbms_sql.parse(l_cursor, l_sql, dbms_sql.native);
1759     dbms_sql.define_column(l_cursor, 1, l_num);
1760 
1761     l_pos := 1;
1762     loop
1763       l_index := instr(x_bind_variables, ',', l_pos, 1);
1764 
1765       if ( l_index = 0 ) then
1766         l_var := substr(x_bind_variables, l_pos, length(x_bind_variables) - l_pos + 1);
1767       else
1768         l_var := substr(x_bind_variables, l_pos, l_index - l_pos);
1769       end if;
1770 
1771       if( l_var = 'from_date' ) then
1772         dbms_sql.bind_variable(l_cursor, '' || l_bind_num, p_from_date);
1773       elsif (l_var = 'to_date' ) then
1774         dbms_sql.bind_variable(l_cursor, '' || l_bind_num, p_to_date);
1775       elsif ( l_var = 'organization_id' ) then
1776         dbms_sql.bind_variable(l_cursor, '' || l_bind_num, p_org_id );
1777       elsif ( l_var = 'department_id' ) then
1778         dbms_sql.bind_variable(l_cursor, '' || l_bind_num, p_department_id);
1779       elsif ( l_var = 'resource_id') then
1780         dbms_sql.bind_variable(l_cursor, '' || l_bind_num, p_resource_id);
1781       end if;
1782       -- bind the var
1783 
1784       exit when l_index = 0;
1785 
1786       l_pos := l_index + 1;
1787       l_bind_num := l_bind_num + 1;
1788     end loop;
1789 
1790     l_dummy := dbms_sql.execute(l_cursor);
1791 
1792     if( dbms_sql.fetch_rows(l_cursor) > 0 ) then
1793       dbms_sql.column_value(l_cursor, 1, l_num);
1794     else
1795       l_num := 0;
1796     end if;
1797 
1798     dbms_sql.close_cursor(l_cursor);
1799 
1800     return l_num;
1801   End get_cap_num_ns_jobs;
1802 
1803   function get_cap_resource_avail
1804   (
1805     p_org_id number,
1806     p_department_id number,
1807     p_resource_id number,
1808     p_shift_num number,
1809     p_from_date date
1810   ) return number
1811   Is
1812     l_avail number;
1813     l_shift_seq number;
1814   Begin
1815     Begin
1816       select bsd.seq_num
1817       into l_shift_seq
1818       from bom_shift_dates bsd, mtl_parameters mp, bom_resource_shifts brs
1819       where mp.organization_id = p_org_id and
1820             mp.calendar_code = bsd.calendar_code and
1821             brs.department_id = p_department_id and
1822             brs.resource_id = p_resource_id and
1823             brs.shift_num = bsd.shift_num and
1824             bsd.shift_num = p_shift_num and
1825             bsd.shift_date = trunc(p_from_date); -- Fix bug 9392379
1826     Exception when others then
1827       l_shift_seq := null;
1828     end;
1829 
1830     if( l_shift_seq is null) then
1831       l_avail := 0;
1832     else
1833       l_avail := get_shift_capacity(p_org_id, p_department_id, p_resource_id, l_shift_seq, p_shift_num);
1834     end if;
1835 
1836     return l_avail;
1837   end get_cap_resource_avail;
1838 
1839  function get_cap_resource_required
1840   (
1841     p_resp_key varchar2,
1842     p_org_id number,
1843     p_department_id number,
1844     p_resource_id number,
1845     p_shift_num number,
1846     p_from_date date,
1847     p_to_date date
1848   )
1849   return number
1850   Is
1851     l_req number;
1852 
1853     l_bind_num number := 1;
1854     l_list_mode number;
1855 
1856     x_where_clause varchar2(4096);
1857     x_bind_variables varchar2(1024);
1858     x_order_by_columns varchar2(1024);
1859     x_order_by_clause varchar2(1024);
1860     x_required varchar2(10) := 'required';		--Bug -7364131
1861     x_bind_var_num number;
1862     l_index number;
1863     l_pos number;
1864     l_var varchar2(256);
1865     l_sql varchar(2048);
1866     l_cursor integer;
1867     l_dummy integer;
1868   Begin
1869     l_list_mode := wip_ws_util.get_preference_value_code(WIP_WS_DEFAULT_DL_TYPE, p_resp_key, p_org_id, p_department_id);
1870 
1871     l_bind_num := 3;
1872 
1873     wip_ws_dl_util.build_dispatch_list_sql(p_resp_key => p_resp_key,
1874                                          p_org_id => p_org_id,
1875                                          p_dept_id => p_department_id,
1876                                          p_resource_id => p_resource_id,
1877                                          p_instance_option => 1, /* all */
1878                                          p_instance_id => null,
1879                                          p_serial_number => null,
1880                                          p_list_mode => l_list_mode,
1881                                          p_from_date => p_from_date,
1882                                          p_to_date => p_to_date,
1883                                          p_job_type => 1,
1884                                           p_component_id => null,
1885                                          p_bind_number => l_bind_num,
1886                                          x_where_clause => x_where_clause,
1887                                          x_bind_variables => x_bind_variables,
1888                                          x_order_by_columns => x_order_by_columns,
1889                                          x_order_by_clause => x_order_by_clause,
1890                                          x_bind_var_num => x_bind_var_num,
1891                                          x_required =>x_required		--Bug -7364131
1892 					 );
1893 
1894     l_sql := 'select sum( nvl(wip_ws_dl_util.get_col_res_usage_req(wip_entity_id, operation_seq_num, :1, :2, null), 0) ) ';
1895     l_sql := l_sql || 'from ( ';
1896     l_sql := l_sql || 'select wo.organization_id, wo.wip_entity_id, wo.operation_seq_num, wo.department_id, ';
1897     l_sql := l_sql || '       wo.first_unit_start_date, wo.last_unit_completion_date, ';
1898     l_sql := l_sql || '       wo.quantity_in_queue, wo.quantity_running, wo.quantity_waiting_to_move, wo.cumulative_scrap_quantity, ';
1899     l_sql := l_sql || ' wdj.start_quantity, wdj.quantity_completed, wdj.expedited, wdj.job_type, wdj.status_type ';
1900     l_sql := l_sql || 'from wip_operations wo, wip_discrete_jobs wdj ';
1901     l_sql := l_sql || 'where wo.organization_id = wdj.organization_id and ';
1902     l_sql := l_sql || '      wo.wip_entity_id = wdj.wip_entity_id ';
1903     l_sql := l_sql || ' ) qrslt ';
1904     l_sql := l_sql || ' where ';
1905     l_sql := l_sql || x_where_clause;
1906 
1907     l_cursor := dbms_sql.open_cursor;
1908     dbms_sql.parse(l_cursor, l_sql, dbms_sql.native);
1909     dbms_sql.define_column(l_cursor, 1, l_req);
1910 
1911     dbms_sql.bind_variable(l_cursor, '1', p_department_id);
1912     dbms_sql.bind_variable(l_cursor, '2', p_resource_id);
1913 
1914     l_pos := 1;
1915     loop
1916       l_index := instr(x_bind_variables, ',', l_pos, 1);
1917 
1918       if ( l_index = 0 ) then
1919         l_var := substr(x_bind_variables, l_pos, length(x_bind_variables) - l_pos + 1);
1920       else
1921         l_var := substr(x_bind_variables, l_pos, l_index - l_pos);
1922       end if;
1923 
1924       if( l_var = 'from_date' ) then
1925         dbms_sql.bind_variable(l_cursor, '' || l_bind_num, p_from_date);
1926       elsif (l_var = 'to_date' ) then
1927         dbms_sql.bind_variable(l_cursor, '' || l_bind_num, p_to_date);
1928       elsif ( l_var = 'organization_id' ) then
1929         dbms_sql.bind_variable(l_cursor, '' || l_bind_num, p_org_id );
1930       elsif ( l_var = 'department_id' ) then
1931         dbms_sql.bind_variable(l_cursor, '' || l_bind_num, p_department_id);
1932       elsif ( l_var = 'resource_id') then
1933         dbms_sql.bind_variable(l_cursor, '' || l_bind_num, p_resource_id);
1934       end if;
1935       -- bind the var
1936 
1937       exit when l_index = 0;
1938 
1939       l_pos := l_index + 1;
1940       l_bind_num := l_bind_num + 1;
1941     end loop;
1942 
1943     l_dummy := dbms_sql.execute(l_cursor);
1944 
1945     if( dbms_sql.fetch_rows(l_cursor) > 0 ) then
1946       dbms_sql.column_value(l_cursor, 1, l_req);
1947     else
1948       l_req := 0;
1949     end if;
1950 
1951     if l_req is null then		--Bug -7364131
1952 	l_req := 0;			--Bug -7364131
1953     end if;				--Bug -7364131
1954     dbms_sql.close_cursor(l_cursor);
1955 
1956     return l_req;
1957   End get_cap_resource_required;
1958 
1959   function is_jobop_completed
1960   (
1961     p_resp_key varchar2,
1962     p_wip_entity_id number,
1963     p_op_seq number
1964   ) return varchar2
1965   Is
1966     l_ret varchar2(1);
1967 
1968     l_dept_id number;
1969     l_org_id number;
1970     l_level_id number;
1971     l_qty_queue_run number;
1972     l_qty_to_move number;
1973     l_include_to_move boolean;
1974   Begin
1975 
1976     select wo.organization_id, wo.department_id,
1977       wo.quantity_in_queue + wo.quantity_running,
1978       wo.quantity_waiting_to_move
1979     into l_org_id, l_dept_id, l_qty_queue_run, l_qty_to_move
1980     from wip_operations wo
1981     where wo.wip_entity_id = p_wip_entity_id and
1982           wo.operation_seq_num = p_op_seq;
1983 
1984     l_level_id := wip_ws_util.get_preference_level_id(WP_INCLUDE_COMPLETE_QTY, p_resp_key, l_org_id, l_dept_id);
1985     if( wip_ws_util.get_preference_value_code(WP_INCLUDE_COMPLETE_QTY, l_level_id) = WP_VALUE_YES ) then
1986       l_include_to_move := true;
1987     else
1988       l_include_to_move := false;
1989     end if;
1990 
1991     if( (l_include_to_move and l_qty_queue_run + l_qty_to_move > 0)
1992           or (not l_include_to_move and l_qty_queue_run > 0) ) then
1993       l_ret := 'N';
1994     else
1995       l_ret := 'Y';
1996     end if;
1997 
1998     return l_ret;
1999   Exception when others then
2000     return null;
2001   End is_jobop_completed;
2002 
2003 begin
2004   null;
2005 end WIP_WS_DL_UTIL;