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.21.12010000.4 2008/11/21 22:30:42 ntangjee 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    ) t
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_required in varchar2 default null			--Bug -7364131
1094   )
1095   Is
1096   Begin
1097 
1098     build_dispatch_list_where
1099     (
1100       p_resp_key, p_org_id, p_dept_id, p_resource_id,
1101       p_instance_option, p_instance_id, p_serial_number,
1102       p_list_mode, p_from_date, p_to_date, p_job_type,
1103       p_component_id,
1104       p_bind_number,
1105       x_where_clause,
1106       x_bind_variables,
1107       x_required				--Bug -7364131
1108     );
1109 
1110     build_dispatch_list_order_by
1111     (
1112       p_resp_key,
1113       p_org_id,
1114       p_dept_id,
1115       x_order_by_columns,
1116       x_order_by_clause
1117     );
1118   End build_dispatch_list_sql;
1119 
1120 
1121   procedure build_dispatch_list_order_by
1122   (
1123     p_resp_key varchar2,
1124     p_org_id number,
1125     p_dept_id number,
1126     x_order_by_columns in out nocopy varchar2,
1127     x_order_by_clause in out nocopy varchar2
1128   )
1129   is
1130 
1131   cursor c_pref_order_by(p_pref_id number, p_level_id number) IS
1132   select
1133       v1.attribute_value_code,
1134       v2.attribute_value_code,
1135       v3.attribute_value_code,
1136       v4.attribute_value_code
1137     from wip_preference_values v1, wip_preference_values v2, wip_preference_values v3, wip_preference_values v4
1138     where
1139       v1.preference_id = p_pref_id and
1140       v2.preference_id = p_pref_id and
1141       v3.preference_id = p_pref_id and
1142       v4.preference_id = p_pref_id and
1143       v1.level_id = p_level_id and
1144       v2.level_id = p_level_id and
1145       v3.level_id = p_level_id and
1146       v4.level_id = p_level_id and
1147       v1.attribute_name = 'attribute' and
1148       v2.attribute_name = 'column' and
1149       v3.attribute_name = 'direction' and
1150       v4.attribute_name = 'ignoreTime' and
1151       v1.sequence_number = v2.sequence_number and
1152       v2.sequence_number = v3.sequence_number and
1153       v3.sequence_number = v4.sequence_number
1154     order by v1.sequence_number;
1155 
1156     l_columns varchar2(4096);
1157     l_orderby varchar2(4096);
1158     l_level_id number;
1159     l_attribute_code varchar2(256);
1160     l_column varchar2(256);
1161     l_direction varchar2(256);
1162     l_ignoreTime varchar2(1);
1163 
1164     l_tmp varchar2(100);
1165   Begin
1166     l_columns := 'expedited';
1167     l_orderby := 'expedited';
1168 
1169     l_level_id := wip_ws_util.get_preference_level_id(WP_DL_ORDERING_CRITERIA, p_resp_key, p_org_id, p_dept_id);
1170 
1171     open c_pref_order_by(WP_DL_ORDERING_CRITERIA, l_level_id);
1172 
1173     loop
1174       fetch c_pref_order_by
1175       into l_attribute_code, l_column, l_direction, l_ignoreTime;
1176 
1177       exit when c_pref_order_by%NOTFOUND;
1178 
1179       add_string(l_columns, ',', l_column);
1180 
1181       l_tmp := l_column;
1182       if( l_ignoreTime is not null and l_ignoreTime = WP_VALUE_YES ) then
1183         l_tmp := 'trunc(' || l_tmp || ')';
1184       end if;
1185 
1186       if( l_direction is not null and l_direction = WP_VALUE_DIRECTION_DOWN ) then
1187         add_string(l_tmp, ' desc');
1188       else
1189         add_string(l_tmp, ' asc');
1190       end if;
1191 
1192       add_string(l_orderby, ', ', l_tmp);
1193     end loop;
1194 
1195     close c_pref_order_by;
1196 
1197     x_order_by_columns := l_columns;
1198     x_order_by_clause := l_orderby;
1199 
1200   End build_dispatch_list_order_by;
1201 
1202   procedure build_dispatch_list_where
1203   (
1204     p_resp_key varchar2,
1205     p_org_id number,
1206     p_dept_id number,
1207     p_resource_id number,
1208     p_instance_assigned number,
1209     p_instance_id number,
1210     p_serial_number varchar2,
1211     p_list_mode number,
1212     p_from_date date,
1213     p_to_date date,
1214     p_job_type number,
1215     p_component_id number,
1216     p_bind_number number,
1217     x_where_clause in out nocopy varchar2,
1218     x_bind_variables in out nocopy varchar2,
1219     x_required in varchar2			--Bug -7364131
1220   )
1221   is
1222 
1223   cursor c_pref_values(p_pref_id number, p_level_id number, p_attribute varchar2) IS
1224     select wpv.attribute_value_code
1225     from wip_preference_values wpv
1226     where wpv.preference_id = p_pref_id and
1227       wpv.level_id = p_level_id and
1228       ( p_attribute is null
1229         or wpv.attribute_name = p_attribute);
1230 
1231   l_where varchar2(4096);
1232   l_binds varchar2(2048);
1233   l_num number;
1234   l_level_id number;
1235 
1236   l_status varchar2(20) := '';
1237   l_value varchar2(10) := '';
1238   l_include_to_move boolean;
1239 
1240   l_tmp varchar2(2048) := '';
1241 
1242   Begin
1243 
1244     l_num := p_bind_number;
1245     l_where := '';
1246     l_binds := '';
1247 
1248     -- read the job type
1249     if( p_job_type is not null) then
1250       add_where(l_where, '  job_type = ' || p_job_type);
1251     end if;
1252 
1253     -- read preference, add job status type
1254     l_level_id := wip_ws_util.get_preference_level_id(WP_JOB_STATUS, p_resp_key, p_org_id, p_dept_id);
1255     open c_pref_values(WP_JOB_STATUS, l_level_id, null);
1256     loop
1257       fetch c_pref_values into l_value;
1258       exit when c_pref_values%NOTFOUND;
1259       add_string(l_status, ', ', l_value);
1260     end loop;
1261     add_where(l_where, '  status_type in (' || l_status || ')' );
1262 
1263     -- if org has set
1264     if( p_org_id is not null ) then
1265       add_where(l_where, '  organization_id = :' || l_num);
1266       add_bind(l_binds, 'organization_id', l_num);
1267     end if;
1268 
1269     -- if instance has set
1270     if( (p_instance_assigned = 3 or p_instance_id = 4) and p_instance_id is not null ) then
1271       l_tmp :=
1272         '  exists   ' ||
1273         '  (   ' ||
1274         '    select 1  ' ||
1275         '    from wip_operation_resources   wor1,  ' ||
1276         '         wip_op_resource_instances wori1  ' ||
1277         '    where qrslt.wip_entity_id = wor1.wip_entity_id  ' ||
1278         '      and qrslt.organization_id = wor1.organization_id  ' ||
1279         '      and qrslt.operation_seq_num = wor1.operation_seq_num  ' ||
1280         '      and wor1.wip_entity_id = wori1.wip_entity_id  ' ||
1281         '      and wor1.operation_seq_num = wori1.operation_seq_num  ' ||
1282         '      and wor1.resource_seq_num = wori1.resource_seq_num  ' ||
1283         '      and wori1.instance_id = :' || l_num || '  ';
1284 
1285       add_bind(l_binds, 'instance_id', l_num);
1286       if( p_serial_number is not null ) then
1287         add_string(l_tmp, '      and wori1.serial_number = :' || l_num || '  ');
1288         add_bind(l_binds, 'serial_number', l_num);
1289       end if;
1290 
1291       add_string(l_tmp, '  )  ');
1292       add_where(l_where, l_tmp);
1293 
1294     -- if the resource is set
1295     elsif ( p_resource_id is not null ) then
1296       add_where(l_where,
1297         '  exists  ' ||
1298         '  (  ' ||
1299         '    select 1  ' ||
1300         '    from wip_operation_resources wor1, bom_department_resources bdr  ' ||
1301         '     where wor1.wip_entity_id = qrslt.wip_entity_id  ' ||
1302         '       and wor1.organization_id = qrslt.organization_id  ' ||
1303         '       and wor1.operation_seq_num = qrslt.operation_seq_num  ' ||
1304         '       and bdr.department_id = qrslt.department_id   ' ||
1305         '       and nvl(wor1.department_id, nvl(bdr.share_from_dept_id, bdr.department_id)) = :' || l_num || '  ' ||
1306         '       and wor1.resource_id = :' || (l_num + 1) || '  ' ||
1307         '  )  '
1308       );
1309       add_bind(l_binds, 'department_id', l_num);
1310       add_bind(l_binds, 'resource_id', l_num);
1311 
1312     -- if dept has set
1313     elsif ( p_dept_id is not null ) then
1314       add_where(l_where, '  department_id = :' || l_num);
1315       add_bind(l_binds, 'department_id', l_num);
1316     end if;
1317 
1318     if( p_instance_assigned = 2) then -- not assigned
1319       if (p_instance_assigned = 2 ) then
1320         l_tmp := '  not exists  ';
1321       else
1322         l_tmp := '  exists  ';
1323       end if;
1324 
1325       add_string
1326       (l_tmp,
1327         '  (   ' ||
1328         '    select 1  ' ||
1329         '    from wip_operation_resources wor1,   ' ||
1330         '         wip_op_resource_instances wori1  ' ||
1331         '    where qrslt.wip_entity_id = wor1.wip_entity_id  ' ||
1332         '      and qrslt.organization_id = wor1.organization_id  ' ||
1333         '      and qrslt.operation_seq_num = wor1.operation_seq_num  ' ||
1334         '      and wor1.wip_entity_id = wori1.wip_entity_id  ' ||
1335         '      and wor1.operation_seq_num = wori1.operation_seq_num  ' ||
1336         '      and wor1.resource_seq_num = wori1.resource_seq_num ' ||
1337         '  )  '
1338       );
1339       add_where(l_where, l_tmp);
1340     end if;
1341 
1342     -- read preference, include complete or not
1343     l_level_id := wip_ws_util.get_preference_level_id(WP_INCLUDE_COMPLETE_QTY, p_resp_key, p_org_id, p_dept_id);
1344     if( wip_ws_util.get_preference_value_code(WP_INCLUDE_COMPLETE_QTY, l_level_id) = WP_VALUE_YES ) then
1345       l_include_to_move := true;
1346     else
1347       l_include_to_move := false;
1348     end if;
1349 
1350     if (p_list_mode = LIST_MODE_SCHEDULED) then
1351       l_tmp := '';
1352       if( p_to_date is not null) then
1353         add_string(l_tmp, '      first_unit_start_date < :' || l_num);
1354         add_bind(l_binds, 'to_date', l_num);
1355       end if;
1356 
1357       if ( p_from_date is not null ) then
1358         add_string(l_tmp, ' and ', '      first_unit_start_date >= :' || l_num);
1359         add_bind(l_binds, 'from_date', l_num);
1360       end if;
1361 
1362       if( l_tmp is not null ) then
1363         add_where(l_where, '  ( expedited = ''Y'' or (' || l_tmp || ' ) )' );
1364       end if;
1365 
1366       if( l_include_to_move ) then
1367         add_where(l_where, '     ( quantity_waiting_to_move > 0 or start_quantity - quantity_completed - cumulative_scrap_quantity > 0 )');
1368       else
1369         add_where(l_where, '     (start_quantity - quantity_completed - cumulative_scrap_quantity > 0 )');
1370       end if;
1371 
1372     elsif ( p_list_mode = LIST_MODE_CURRENT) then
1373       l_tmp := '';
1374 
1375 
1376       if( p_to_date is not null ) then
1377         add_string(l_tmp, 'first_unit_start_date <:'|| l_num);
1378         add_bind(l_binds, 'to_date', l_num);
1379       end if;
1380 
1381       if( p_from_date is not null ) then
1382         add_string(l_tmp, ' and ', 'first_unit_start_date >= :' || l_num);
1383         add_bind(l_binds, 'from_date', l_num);
1384       end if;
1385 
1386       if( l_tmp is not null ) then
1387         l_tmp := '  (expedited = ''Y'' or ( ' || l_tmp || ' )) ';
1388       end if;
1389 
1390       if x_required is null then				--Bug -7364131
1391 	      if( l_include_to_move ) then
1392 		add_string(l_tmp, ' and ', '( quantity_in_queue > 0 or quantity_running > 0 or quantity_waiting_to_move > 0)');
1393 	      else
1394 		add_string(l_tmp, ' and ', '( quantity_in_queue > 0 or quantity_running > 0 )');
1395 	      end if ;
1396       end if ;							--Bug -7364131
1397 
1398       add_where(l_where, '  (      ' || l_tmp || '   )  ');
1399     else -- upstream
1400       l_tmp := '';
1401       if( p_to_date is not null ) then
1402         add_string(l_tmp, 'first_unit_start_date <:'|| l_num);
1403         add_bind(l_binds, 'to_date', l_num);
1404       end if;
1405 
1406       if( p_from_date is not null ) then
1407         add_string(l_tmp, ' and ', 'first_unit_start_date >= :' || l_num);
1408         add_bind(l_binds, 'from_date', l_num);
1409       end if;
1410 
1411       if( l_tmp is not null ) then
1412         l_tmp := '  (expedited = ''Y'' or ( ' || l_tmp || ' )) ';
1413       end if;
1414 
1415       add_string(l_tmp, ' and ', '( ');
1416       add_string(l_tmp, '   0 < (select (wo1.quantity_in_queue + wo1.quantity_running + wo1.quantity_waiting_to_move + wo1.quantity_rejected) ');
1417       add_string(l_tmp, '      from wip_operations wo1 where wo1.operation_seq_num = ');
1418       add_string(l_tmp, '        ( select max(wo2.operation_seq_num) from wip_operations wo2 ');
1419       add_string(l_tmp, '          where wo2.count_point_type = 1 and ');
1420       add_string(l_tmp, '            wo2.operation_seq_num < qrslt.operation_seq_num and ');
1421       add_string(l_tmp, '            wo2.wip_entity_id = wo1.wip_entity_id and ');
1422       add_string(l_tmp, '             wo2.organization_id = wo1.organization_id ');
1423       add_string(l_tmp, '        ) and  wo1.wip_entity_id = qrslt.wip_entity_id )' );
1424       add_string(l_tmp, '   or ');
1425       add_string(l_tmp, '   0 < (select (wo1.quantity_in_queue + wo1.quantity_running + wo1.quantity_waiting_to_move + wo1.quantity_rejected) ');
1426       add_string(l_tmp, '      from wip_operations wo1, wip_operations wo2 ');
1427       add_string(l_tmp, '        where wo2.previous_operation_seq_num = wo1.operation_seq_num and ');
1428       add_string(l_tmp, '              wo2.operation_seq_num = qrslt.operation_seq_num and ');
1429       add_string(l_tmp, '              wo2.wip_entity_id = qrslt.wip_entity_id and ');
1430       add_string(l_tmp, '              wo2.wip_entity_id = wo1.wip_entity_id )' );
1431       add_string(l_tmp, '   ) ');
1432       add_where(l_where, l_tmp);
1433     end if;
1434    null;
1435 
1436    x_where_clause := l_where;
1437    x_bind_variables := l_binds;
1438 
1439   End build_dispatch_list_where;
1440 
1441 
1442   procedure expedite
1443   (
1444     p_wip_entity_id number,
1445     p_op_seq_num number,
1446     x_status in out nocopy varchar2,
1447     x_msg_count in out nocopy number,
1448     x_msg in out nocopy number
1449   )
1450   Is
1451    l_expedited varchar2(1);
1452   Begin
1453     fnd_msg_pub.Initialize;
1454 
1455     x_status := '';
1456     x_msg_count := 0;
1457     x_msg := '';
1458 
1459     select wdj.expedited
1460     into l_expedited
1461     from wip_discrete_jobs wdj
1462     where wdj.wip_entity_id = p_wip_entity_id;
1463 
1464     if( l_expedited is null or l_expedited = 'N') then
1465       update wip_discrete_jobs wdj
1466       set wdj.expedited = 'Y'
1467       where wdj.wip_entity_id = p_wip_entity_id;
1468       commit;
1469     else
1470       x_status := 'A';
1471       fnd_message.SET_NAME('WIP', 'WS_JOBOP_ALR_EXPEDITED');
1472       fnd_msg_pub.Add;
1473     end if;
1474 
1475   End;
1476 
1477   procedure unexpedite
1478   (
1479     p_wip_entity_id number,
1480     p_op_seq_num number,
1481     x_status in out nocopy varchar2,
1482     x_msg_count in out nocopy number,
1483     x_msg in out nocopy number
1484   )
1485   Is
1486    l_expedited varchar2(1);
1487   Begin
1488     fnd_msg_pub.Initialize;
1489 
1490     x_status := '';
1491     x_msg_count := 0;
1492     x_msg := '';
1493 
1494     select wdj.expedited
1495     into l_expedited
1496     from wip_discrete_jobs wdj
1497     where wdj.wip_entity_id = p_wip_entity_id;
1498 
1499     if( l_expedited = 'Y') then
1500       update wip_discrete_jobs wdj
1501       set wdj.expedited = null /* set to N dosn't help on order */
1502       where wdj.wip_entity_id = p_wip_entity_id;
1503       commit;
1504     else
1505       x_status := 'A';
1506       fnd_message.SET_NAME('WIP', 'WS_JOBOP_ALR_UNEXPEDITED');
1507       fnd_msg_pub.Add;
1508     end if;
1509 
1510   End;
1511 
1512 
1513   /* need to concatenate the shift seq and shift num to uniquely identify a shift */
1514   function get_first_shift_id(p_org_id number, p_dept_id number, p_resource_id number)
1515   return varchar2
1516   Is
1517     l_ret varchar2(60);
1518 
1519     l_date date;
1520     l_seq number;
1521     l_num number;
1522     l_shift_start_date date;
1523     l_shift_end_date date;
1524     l_str varchar2(60);
1525   Begin
1526 
1527     l_date := sysdate;
1528 
1529     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);
1530 
1531     l_ret := l_seq || '.' || l_num;
1532 
1533     return l_ret;
1534 
1535   End get_first_shift_id;
1536 
1537   procedure batch_move_add(
1538     p_index number,
1539     p_wip_entity_id number,
1540     p_wip_entity_name varchar2,
1541     p_op_seq varchar2,
1542     p_move_qty number,
1543     p_scrap_qty number,
1544     p_assy_serial varchar2 default null,
1545     x_return_status out nocopy varchar2
1546   )
1547   Is
1548   Begin
1549     if( p_index = 1 ) then
1550       l_move_table.delete;
1551     end if;
1552 
1553     l_move_table(p_index).wip_entity_id := p_wip_entity_id;
1554     l_move_table(p_index).wip_entity_name := p_wip_entity_name;
1555     l_move_table(p_index).op_seq := p_op_seq;
1556     l_move_table(p_index).move_qty := p_move_qty;
1557     l_move_table(p_index).scrap_qty := p_scrap_qty;
1558     l_move_table(p_index).assy_serial := p_assy_serial;
1559 
1560     x_return_status := 'S';
1561   Exception when others then
1562     x_return_status := 'U';
1563   End batch_move_add;
1564 
1565   procedure batch_move_process
1566   (
1567     p_resp_key varchar2,
1568     p_org_id number,
1569     p_dept_id number,
1570     p_employee_id number,
1571     x_return_status out nocopy varchar2
1572   )
1573   Is
1574   Begin
1575     wip_batch_move.process(l_move_table, p_resp_key, p_org_id, p_dept_id, p_employee_id, x_return_status);
1576   End batch_move_process;
1577 
1578   function get_shift_capacity
1579   (
1580     p_org_id number,
1581     p_dept_id number,
1582     p_resource_id number,
1583     p_shift_seq number,
1584     p_shift_num number
1585   )  return number
1586   Is
1587     l_cal_code varchar2(30);
1588     l_cal_exception_id number;
1589     l_date date;
1590     l_shift_date date;
1591     l_total_time number;
1592     l_units number;
1593     l_utilizaiton number;
1594   Begin
1595 
1596     select mp.calendar_code, mp.calendar_exception_set_id
1597     into l_cal_code, l_cal_exception_id
1598     from mtl_parameters mp
1599     where mp.organization_id = p_org_id;
1600 
1601     if( p_shift_num is not null) then
1602       /* use bom_shift_dates*/
1603       l_date := sysdate;
1604 
1605       select shift_date
1606       into l_shift_date
1607       from bom_shift_dates bsd
1608       where bsd.calendar_code = l_cal_code and
1609             bsd.exception_set_id = l_cal_exception_id and
1610             bsd.seq_num = p_shift_seq and
1611             bsd.shift_num = p_shift_num;
1612 
1613       /* adjust the time with sysdate */
1614       select sum( 24* (to_date - from_date)) total_time
1615       into l_total_time
1616       from
1617              ( select GREATEST(l_date, l_shift_date + from_time/(24*60*60)) from_date,
1618                       l_shift_date + to_time/(24*60*60) + decode(sign(to_time - from_time), -1, 1, 0) to_date
1619                  from bom_shift_times bst
1620                  where bst.calendar_code = l_cal_code and
1621                        bst.shift_num = p_shift_num
1622              ) sd
1623       where sd.from_date <= sd.to_date;
1624 
1625       select brs.capacity_units
1626       into l_units
1627       from bom_resource_shifts brs
1628       where brs.department_id = p_dept_id and
1629           brs.resource_id = p_resource_id and
1630           brs.shift_num = p_shift_num;
1631     else
1632       /* 24 hour resource */
1633       /*
1634       select bcd.calendar_date
1635       into l_shift_date
1636       from bom_calendar_dates bcd
1637       where bcd.calendar_code = l_cal_code and
1638             bcd.seq_num = p_shift_seq;
1639 
1640       if( l_shift_date < trunc(sysdate) ) then
1641         l_total_time := 0;
1642       elsif (l_shift_date > trunc(sysdate) ) then
1643         l_total_time := 24;
1644       else
1645         l_total_time := 24 - (sysdate - l_shift_date)*24;
1646       end if;
1647       */
1648       /* per barry's decision, using 0 as the capacity for 24hr resource */
1649       l_total_time := 0;
1650 
1651       l_units := null;
1652     end if;
1653 
1654     select nvl(l_units, bdr.capacity_units),
1655            decode(wp.include_resource_utilization, wip_constants.yes, nvl(bdr.utilization, 1), 1)
1656     into l_units, l_utilizaiton
1657     from bom_department_resources bdr, wip_parameters wp
1658     where bdr.department_id = p_dept_id and
1659           bdr.resource_id = p_resource_id and
1660           wp.organization_id = p_org_id;
1661 
1662     if( l_units is null ) then
1663       l_units := 1;
1664     end if;
1665 
1666     return l_units * l_total_time * l_utilizaiton;
1667   Exception
1668     when others then
1669       return 0;
1670   End get_shift_capacity;
1671 
1672   /* for home page capacity table */
1673   function get_cap_num_ns_jobs
1674   (
1675     p_resp_key varchar2,
1676     p_org_id number,
1677     p_department_id number,
1678     p_resource_id number,
1679     p_shift_num number,
1680     p_from_date date,
1681     p_to_date date
1682   ) return number
1683   Is
1684     l_num number;
1685 
1686     l_bind_num number := 1;
1687     l_list_mode number;
1688 
1689     x_where_clause varchar2(4096);
1690     x_bind_variables varchar2(1024);
1691     x_order_by_columns varchar2(1024);
1692     x_order_by_clause varchar2(1024);
1693 
1694     l_index number;
1695     l_pos number;
1696     l_var varchar2(256);
1697     l_sql varchar(2048);
1698     l_cursor integer;
1699     l_dummy integer;
1700   Begin
1701 
1702     l_list_mode := wip_ws_util.get_preference_value_code(WIP_WS_DEFAULT_DL_TYPE, p_resp_key, p_org_id, p_department_id);
1703 
1704     l_bind_num := 3;
1705 
1706     wip_ws_dl_util.build_dispatch_list_sql(p_resp_key => p_resp_key,
1707                                          p_org_id => p_org_id,
1708                                          p_dept_id => p_department_id,
1709                                          p_resource_id => p_resource_id,
1710                                          p_instance_option => 1, /* all */
1711                                          p_instance_id => null,
1712                                          p_serial_number => null,
1713                                          p_list_mode => l_list_mode,
1714                                          p_from_date => p_from_date,
1715                                          p_to_date => p_to_date,
1716                                          p_job_type => 1,
1717                                           p_component_id => null,
1718                                          p_bind_number => l_bind_num,
1719                                          x_where_clause => x_where_clause,
1720                                          x_bind_variables => x_bind_variables,
1721                                          x_order_by_columns => x_order_by_columns,
1722                                          x_order_by_clause => x_order_by_clause);
1723 
1724     l_sql := 'select count(*) ';
1725     l_sql := l_sql || 'from ( ';
1726     l_sql := l_sql || 'select wo.organization_id, wo.wip_entity_id, wo.operation_seq_num, wo.department_id, ';
1727     l_sql := l_sql || '       wo.first_unit_start_date, wo.last_unit_completion_date, ';
1728     l_sql := l_sql || '       wo.quantity_in_queue, wo.quantity_running, wo.quantity_waiting_to_move, wo.cumulative_scrap_quantity, ';
1729     l_sql := l_sql || ' wdj.start_quantity, wdj.quantity_completed, wdj.expedited, wdj.job_type, wdj.status_type ';
1730     l_sql := l_sql || 'from wip_operations wo, wip_discrete_jobs wdj ';
1731     l_sql := l_sql || 'where wo.organization_id = wdj.organization_id and ';
1732     l_sql := l_sql || '      wo.wip_entity_id = wdj.wip_entity_id and ';
1733     l_sql := l_sql || '      wo.actual_start_date is null ';
1734     l_sql := l_sql || ' ) qrslt ';
1735     l_sql := l_sql || ' where ';
1736     l_sql := l_sql || x_where_clause;
1737 
1738     l_cursor := dbms_sql.open_cursor;
1739     dbms_sql.parse(l_cursor, l_sql, dbms_sql.native);
1740     dbms_sql.define_column(l_cursor, 1, l_num);
1741 
1742     l_pos := 1;
1743     loop
1744       l_index := instr(x_bind_variables, ',', l_pos, 1);
1745 
1746       if ( l_index = 0 ) then
1747         l_var := substr(x_bind_variables, l_pos, length(x_bind_variables) - l_pos + 1);
1748       else
1749         l_var := substr(x_bind_variables, l_pos, l_index - l_pos);
1750       end if;
1751 
1752       if( l_var = 'from_date' ) then
1753         dbms_sql.bind_variable(l_cursor, '' || l_bind_num, p_from_date);
1754       elsif (l_var = 'to_date' ) then
1755         dbms_sql.bind_variable(l_cursor, '' || l_bind_num, p_to_date);
1756       elsif ( l_var = 'organization_id' ) then
1757         dbms_sql.bind_variable(l_cursor, '' || l_bind_num, p_org_id );
1758       elsif ( l_var = 'department_id' ) then
1759         dbms_sql.bind_variable(l_cursor, '' || l_bind_num, p_department_id);
1760       elsif ( l_var = 'resource_id') then
1761         dbms_sql.bind_variable(l_cursor, '' || l_bind_num, p_resource_id);
1762       end if;
1763       -- bind the var
1764 
1765       exit when l_index = 0;
1766 
1767       l_pos := l_index + 1;
1768       l_bind_num := l_bind_num + 1;
1769     end loop;
1770 
1771     l_dummy := dbms_sql.execute(l_cursor);
1772 
1773     if( dbms_sql.fetch_rows(l_cursor) > 0 ) then
1774       dbms_sql.column_value(l_cursor, 1, l_num);
1775     else
1776       l_num := 0;
1777     end if;
1778 
1779     dbms_sql.close_cursor(l_cursor);
1780 
1781     return l_num;
1782   End get_cap_num_ns_jobs;
1783 
1784   function get_cap_resource_avail
1785   (
1786     p_org_id number,
1787     p_department_id number,
1788     p_resource_id number,
1789     p_shift_num number
1790   ) return number
1791   Is
1792     l_avail number;
1793     l_shift_seq number;
1794   Begin
1795     Begin
1796       select bsd.seq_num
1797       into l_shift_seq
1798       from bom_shift_dates bsd, mtl_parameters mp, bom_resource_shifts brs
1799       where mp.organization_id = p_org_id and
1800             mp.calendar_code = bsd.calendar_code and
1801             brs.department_id = p_department_id and
1802             brs.resource_id = p_resource_id and
1803             brs.shift_num = bsd.shift_num and
1804             bsd.shift_num = p_shift_num and
1805             bsd.shift_date = trunc(sysdate);
1806     Exception when others then
1807       l_shift_seq := null;
1808     end;
1809 
1810     if( l_shift_seq is null) then
1811       l_avail := 0;
1812     else
1813       l_avail := get_shift_capacity(p_org_id, p_department_id, p_resource_id, l_shift_seq, p_shift_num);
1814     end if;
1815 
1816     return l_avail;
1817   end get_cap_resource_avail;
1818 
1819  function get_cap_resource_required
1820   (
1821     p_resp_key varchar2,
1822     p_org_id number,
1823     p_department_id number,
1824     p_resource_id number,
1825     p_shift_num number,
1826     p_from_date date,
1827     p_to_date date
1828   )
1829   return number
1830   Is
1831     l_req number;
1832 
1833     l_bind_num number := 1;
1834     l_list_mode number;
1835 
1836     x_where_clause varchar2(4096);
1837     x_bind_variables varchar2(1024);
1838     x_order_by_columns varchar2(1024);
1839     x_order_by_clause varchar2(1024);
1840     x_required varchar2(10) := 'required';		--Bug -7364131
1841     l_index number;
1842     l_pos number;
1843     l_var varchar2(256);
1844     l_sql varchar(2048);
1845     l_cursor integer;
1846     l_dummy integer;
1847   Begin
1848     l_list_mode := wip_ws_util.get_preference_value_code(WIP_WS_DEFAULT_DL_TYPE, p_resp_key, p_org_id, p_department_id);
1849 
1850     l_bind_num := 3;
1851 
1852     wip_ws_dl_util.build_dispatch_list_sql(p_resp_key => p_resp_key,
1853                                          p_org_id => p_org_id,
1854                                          p_dept_id => p_department_id,
1855                                          p_resource_id => p_resource_id,
1856                                          p_instance_option => 1, /* all */
1857                                          p_instance_id => null,
1858                                          p_serial_number => null,
1859                                          p_list_mode => l_list_mode,
1860                                          p_from_date => p_from_date,
1861                                          p_to_date => p_to_date,
1862                                          p_job_type => 1,
1863                                           p_component_id => null,
1864                                          p_bind_number => l_bind_num,
1865                                          x_where_clause => x_where_clause,
1866                                          x_bind_variables => x_bind_variables,
1867                                          x_order_by_columns => x_order_by_columns,
1868                                          x_order_by_clause => x_order_by_clause,
1869 					  x_required =>x_required		--Bug -7364131
1870 					 );
1871 
1872     l_sql := 'select sum( nvl(wip_ws_dl_util.get_col_res_usage_req(wip_entity_id, operation_seq_num, :1, :2, null), 0) ) ';
1873     l_sql := l_sql || 'from ( ';
1874     l_sql := l_sql || 'select wo.organization_id, wo.wip_entity_id, wo.operation_seq_num, wo.department_id, ';
1875     l_sql := l_sql || '       wo.first_unit_start_date, wo.last_unit_completion_date, ';
1876     l_sql := l_sql || '       wo.quantity_in_queue, wo.quantity_running, wo.quantity_waiting_to_move, wo.cumulative_scrap_quantity, ';
1877     l_sql := l_sql || ' wdj.start_quantity, wdj.quantity_completed, wdj.expedited, wdj.job_type, wdj.status_type ';
1878     l_sql := l_sql || 'from wip_operations wo, wip_discrete_jobs wdj ';
1879     l_sql := l_sql || 'where wo.organization_id = wdj.organization_id and ';
1880     l_sql := l_sql || '      wo.wip_entity_id = wdj.wip_entity_id ';
1881     l_sql := l_sql || ' ) qrslt ';
1882     l_sql := l_sql || ' where ';
1883     l_sql := l_sql || x_where_clause;
1884 
1885     l_cursor := dbms_sql.open_cursor;
1886     dbms_sql.parse(l_cursor, l_sql, dbms_sql.native);
1887     dbms_sql.define_column(l_cursor, 1, l_req);
1888 
1889     dbms_sql.bind_variable(l_cursor, '1', p_department_id);
1890     dbms_sql.bind_variable(l_cursor, '2', p_resource_id);
1891 
1892     l_pos := 1;
1893     loop
1894       l_index := instr(x_bind_variables, ',', l_pos, 1);
1895 
1896       if ( l_index = 0 ) then
1897         l_var := substr(x_bind_variables, l_pos, length(x_bind_variables) - l_pos + 1);
1898       else
1899         l_var := substr(x_bind_variables, l_pos, l_index - l_pos);
1900       end if;
1901 
1902       if( l_var = 'from_date' ) then
1903         dbms_sql.bind_variable(l_cursor, '' || l_bind_num, p_from_date);
1904       elsif (l_var = 'to_date' ) then
1905         dbms_sql.bind_variable(l_cursor, '' || l_bind_num, p_to_date);
1906       elsif ( l_var = 'organization_id' ) then
1907         dbms_sql.bind_variable(l_cursor, '' || l_bind_num, p_org_id );
1908       elsif ( l_var = 'department_id' ) then
1909         dbms_sql.bind_variable(l_cursor, '' || l_bind_num, p_department_id);
1910       elsif ( l_var = 'resource_id') then
1911         dbms_sql.bind_variable(l_cursor, '' || l_bind_num, p_resource_id);
1912       end if;
1913       -- bind the var
1914 
1915       exit when l_index = 0;
1916 
1917       l_pos := l_index + 1;
1918       l_bind_num := l_bind_num + 1;
1919     end loop;
1920 
1921     l_dummy := dbms_sql.execute(l_cursor);
1922 
1923     if( dbms_sql.fetch_rows(l_cursor) > 0 ) then
1924       dbms_sql.column_value(l_cursor, 1, l_req);
1925     else
1926       l_req := 0;
1927     end if;
1928 
1929     if l_req is null then		--Bug -7364131
1930 	l_req := 0;			--Bug -7364131
1931     end if;				--Bug -7364131
1932     dbms_sql.close_cursor(l_cursor);
1933 
1934     return l_req;
1935   End get_cap_resource_required;
1936 
1937   function is_jobop_completed
1938   (
1939     p_resp_key varchar2,
1940     p_wip_entity_id number,
1941     p_op_seq number
1942   ) return varchar2
1943   Is
1944     l_ret varchar2(1);
1945 
1946     l_dept_id number;
1947     l_org_id number;
1948     l_level_id number;
1949     l_qty_queue_run number;
1950     l_qty_to_move number;
1951     l_include_to_move boolean;
1952   Begin
1953 
1954     select wo.organization_id, wo.department_id,
1955       wo.quantity_in_queue + wo.quantity_running,
1956       wo.quantity_waiting_to_move
1957     into l_org_id, l_dept_id, l_qty_queue_run, l_qty_to_move
1958     from wip_operations wo
1959     where wo.wip_entity_id = p_wip_entity_id and
1960           wo.operation_seq_num = p_op_seq;
1961 
1962     l_level_id := wip_ws_util.get_preference_level_id(WP_INCLUDE_COMPLETE_QTY, p_resp_key, l_org_id, l_dept_id);
1963     if( wip_ws_util.get_preference_value_code(WP_INCLUDE_COMPLETE_QTY, l_level_id) = WP_VALUE_YES ) then
1964       l_include_to_move := true;
1965     else
1966       l_include_to_move := false;
1967     end if;
1968 
1969     if( (l_include_to_move and l_qty_queue_run + l_qty_to_move > 0)
1970           or (not l_include_to_move and l_qty_queue_run > 0) ) then
1971       l_ret := 'N';
1972     else
1973       l_ret := 'Y';
1974     end if;
1975 
1976     return l_ret;
1977   Exception when others then
1978     return null;
1979   End is_jobop_completed;
1980 
1981 begin
1982   null;
1983 end WIP_WS_DL_UTIL;
1984