[Home] [Help]
PACKAGE BODY: APPS.CSP_PART_SEARCH_PVT
Source
1 package body csp_part_search_pvt as
2 /*$Header: cspvsrcb.pls 120.48.12020000.7 2013/03/15 06:38:53 htank ship $*/
3
4 l_return_status varchar2(30) := FND_API.G_RET_STS_SUCCESS;
5 l_msg_data varchar2(2000);
6 l_msg_count number;
7
8 procedure log(p_procedure in varchar2,p_message in varchar2) as
9 begin
10 dbms_output.put_line(p_procedure||' - '||p_message);
11 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
12 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
13 'csp.plsql.csp_part_search_pvt.'||p_procedure,
14 p_message);
15 end if;
16 end;
17
18 procedure search(p_required_parts IN required_parts_tbl,
19 p_search_params IN search_params_rec,
20 x_return_status OUT NOCOPY varchar2,
21 x_msg_data OUT NOCOPY varchar2,
22 x_msg_count OUT NOCOPY varchar2
23 ) AS
24 l_organization_id number;
25 l_subinventory_code varchar2(30);
26 l_enough number := -1;
27 l_open_or_closed varchar2(30) := 'OPEN';
28 l_my_location sdo_geometry;
29 l_distance_uom varchar2(30) := 'unit=km';
30 l_server_timezone_id number := fnd_profile.value('SERVER_TIMEZONE_ID');
31 l_search_method varchar2(30):= nvl(p_search_params.search_method,
32 fnd_profile.value('CSP_PART_SEARCH_METHOD'));
33 l_called_from_charges varchar2(1) := 'N';
34
35 procedure ship_set as
36
37 cursor c_parts is
38 select capt.organization_id,
39 capt.subinventory_code,
40 capt.required_item_id,
41 sum(nvl(supplied_quantity,0)) supplied_quantity,
42 crpt.quantity required_quantity
43 from csp_available_parts_temp capt,csp_required_parts_temp crpt
44 where crpt.inventory_item_id = capt.required_item_id
45 and item_type = 'BASE'
46 group by capt.organization_id,
47 capt.subinventory_code,
48 capt.required_item_id,
49 crpt.quantity;
50
51 begin
52 log('ship_set','Begin');
53 if p_search_params.ship_set then
54 for cr in c_parts loop
55 log('ship_set',
56 'cr.supplied_quantity:'||cr.supplied_quantity||
57 'cr.required_quantity:'||cr.required_quantity);
58 if cr.supplied_quantity < cr.required_quantity then
59 log('ship_set','deleting');
60 delete from csp_available_parts_temp
61 where organization_id = cr.organization_id
62 and nvl(subinventory_code,'-1') = nvl(cr.subinventory_code,'-1');
63 log('ship_set','Records deleted:'||sql%rowcount);
64 end if;
65 end loop;
66 end if;
67 log('ship_set','End');
68 end;
69
70 procedure print_capt_contents as
71 cursor capt is
72 SELECT organization_id,
73 subinventory_code,
74 source_type_code,
75 required_item_id,
76 required_item_rev,
77 required_quantity,
78 supplied_item_id,
79 supplied_item_rev,
80 supplied_quantity,
81 supplied_item_type,
82 shipping_date,
83 shipping_method,
84 shipping_cost,
85 arrival_date,
86 distance,
87 open_or_closed,
88 geometry
89 from csp_available_parts_temp;
90 org_id csp_available_parts_temp.organization_id%type;
91 sub_code csp_available_parts_temp.subinventory_code%type;
92 src_typ_cd csp_available_parts_temp.source_type_code%type;
93 req_item_id csp_available_parts_temp.required_item_id%type;
94 req_item_rev csp_available_parts_temp.required_item_rev%type;
95 req_qty csp_available_parts_temp.required_quantity%type;
96 sup_item_id csp_available_parts_temp.supplied_item_id%type;
97 sup_item_rev csp_available_parts_temp.supplied_item_rev%type;
98 sup_qty csp_available_parts_temp.supplied_quantity%type;
99 sup_item_typ csp_available_parts_temp.supplied_item_type%type;
100 ship_dt csp_available_parts_temp.shipping_date%type;
101 ship_meth csp_available_parts_temp.shipping_method%type;
102 ship_cost csp_available_parts_temp.shipping_cost%type;
103 arr_dt csp_available_parts_temp.arrival_date%type;
104 dist csp_available_parts_temp.distance%type;
105 op_or_c csp_available_parts_temp.open_or_closed%type;
106 geo csp_available_parts_temp.geometry%type;
107 begin
108 /*
109 open capt;
110 fetch capt into org_id, sub_code, src_typ_cd, req_item_id, req_item_rev, req_qty, sup_item_id, sup_item_rev, sup_qty, sup_item_typ, ship_dt, ship_meth, ship_cost, arr_dt, dist, op_or_c, geo;
111 close capt;
112
113 log('print_capt_contents','organization_id: '||org_id);
114 log('print_capt_contents','subinventory_code: '||sub_code);
115 log('print_capt_contents','source_type_code: '||src_typ_cd);
116 log('print_capt_contents','required_item_id: '||req_item_id);
117 log('print_capt_contents','required_item_rev: '||req_item_rev);
118 log('print_capt_contents','required_quantity: '||req_qty);
119 log('print_capt_contents','supplied_item_id: '||sup_item_id);
120 log('print_capt_contents','supplied_item_rev: '||sup_item_rev);
121 log('print_capt_contents','supplied_quantity: '||sup_qty);
122 log('print_capt_contents','supplied_item_type: '||sup_item_typ);
123 log('print_capt_contents','shipping_date: '||ship_dt);
124 log('print_capt_contents','shipping_method: '||ship_meth);
125 log('print_capt_contents','shipping_cost: '||ship_cost);
126 log('print_capt_contents','arrival_date: '||arr_dt);
127 log('print_capt_contents','distance: '||dist);
128 log('print_capt_contents','open_or_closed: '||op_or_c);
129 --log('print_capt_contents','geometry: '||geo);
130 */
131 log('print_capt_contents', 'Printing existing rows in CAPT...');
132 for capt_rec in capt loop
133 log('print_capt_contents','organization_id: '||capt_rec.organization_id);
134 log('print_capt_contents','subinventory_code: '||capt_rec.subinventory_code);
135 log('print_capt_contents','source_type_code: '||capt_rec.source_type_code);
136 log('print_capt_contents','required_item_id: '||capt_rec.required_item_id);
137 log('print_capt_contents','required_item_rev: '||capt_rec.required_item_rev);
138 log('print_capt_contents','required_quantity: '||capt_rec.required_quantity);
139 log('print_capt_contents','supplied_item_id: '||capt_rec.supplied_item_id);
140 log('print_capt_contents','supplied_item_rev: '||capt_rec.supplied_item_rev);
141 log('print_capt_contents','supplied_quantity: '||capt_rec.supplied_quantity);
142 log('print_capt_contents','supplied_item_type: '||capt_rec.supplied_item_type);
143 log('print_capt_contents','shipping_date: '||capt_rec.shipping_date);
144 log('print_capt_contents','shipping_method: '||capt_rec.shipping_method);
145 log('print_capt_contents','shipping_cost: '||capt_rec.shipping_cost);
146 log('print_capt_contents','arrival_date: '||capt_rec.arrival_date);
147 log('print_capt_contents','distance: '||capt_rec.distance);
148 log('print_capt_contents','open_or_closed: '||capt_rec.open_or_closed);
149 end loop;
150 end print_capt_contents;
151
152 procedure clean_up as
153 begin
154 log('clean_up','Begin');
155 delete from csp_available_parts_temp
156 where nvl(supplied_quantity,-1) <= 0;
157 log('clean_up','Records deleted:'||sql%rowcount);
158 log('clean_up','End');
159 end;
160
161 procedure update_shipping_info as
162
163 l_shipping_method varchar2(60);
164 l_shipping_cost number;
165 l_arrival_date date;
166 l_distance_uom_code varchar2(30);
167
168 cursor c_sources is
169 select distinct organization_id, subinventory_code,
170 decode(l_distance_uom_code,'MILE',distance,'KM',distance/1.609344) distance
171 from csp_available_parts_temp
172 where source_type_code not in ('DEDICATED','MYSELF','UNMANNED','TECHNICIAN');
173
174 cursor c_shipping_info(p_organization_id number,
175 p_subinventory_code varchar2) is
176 select shipping_method,
177 shipping_cost,
178 arrival_date,
179 decode(distance_uom,'MILE',distance,'KM',distance/1.609344) distance
180 from csp_shipping_details_v
181 where organization_id = p_organization_id
182 and to_location_id = nvl(p_search_params.to_location_id,
183 p_search_params.to_hz_location_id)
184 and location_source = decode(p_search_params.to_location_id,null,'HZ','HR')
185 order by shipping_cost,arrival_date;
186
187 begin
188 log('update_shipping_info','Begin');
189 log('update_shipping_info','p_search_params.need_by_date:'||
190 to_char(p_search_params.need_by_date,'ddmmyy hh24:MI'));
191 if l_distance_uom = 'unit=km' then
192 l_distance_uom_code := 'KM';
193 elsif l_distance_uom = 'unit=mile' then
194 l_distance_uom_code := 'MILE';
195 end if;
196 log('update_shipping_info','l_distance_uom_code:'||l_distance_uom_code);
197
198 if p_search_params.need_by_date is null then --Return all shipping methods
199 begin
200 insert into csp_available_parts_temp(
201 organization_id,
202 subinventory_code,
203 source_type_code,
204 required_item_id,
205 required_item_rev,
206 required_quantity,
207 supplied_item_id,
208 supplied_item_rev,
209 supplied_quantity,
210 supplied_item_type,
211 shipping_date,
212 shipping_method,
213 shipping_cost,
214 arrival_date,
215 distance,
216 open_or_closed,
217 geometry)
218 select capt.organization_id,
219 capt.subinventory_code,
220 capt.source_type_code,
221 capt.required_item_id,
222 capt.required_item_rev,
223 capt.required_quantity,
224 capt.supplied_item_id,
225 capt.supplied_item_rev,
226 capt.supplied_quantity,
227 capt.supplied_item_type,
228 capt.shipping_date,
229 csdv.shipping_method,
230 csdv.shipping_cost,
231 csdv.arrival_date,
232 capt.distance,
233 capt.open_or_closed,
234 capt.geometry
235 from csp_shipping_details_v csdv,
236 csp_available_parts_temp capt
237 where csdv.organization_id = capt.organization_id
238 and to_location_id = nvl(p_search_params.to_location_id,
239 p_search_params.to_hz_location_id)
240 and location_source = decode(p_search_params.to_location_id,
241 null,'HZ','HR')
242 and nvl(decode(l_distance_uom_code,'MILE',capt.distance,
243 'KM',capt.distance/1.609344),0) <=
244 nvl(decode(csdv.distance_uom,'MILE',csdv.distance,
245 'KM',csdv.distance/1.609344),
246 nvl(decode(l_distance_uom_code,'MILE',capt.distance,
247 'KM',capt.distance/1.609344),0))
248 order by csdv.shipping_cost,csdv.arrival_date;
249 log('update_shipping_info','Records inserted:'||sql%rowcount);
250
251 exception
252 when others then
253 log('unmanned_warehouses','when others exception');
254 log('unmanned_warehouses','sqlcode:'||sqlcode);
255 log('unmanned_warehouses','sqlerrm:'||sqlerrm);
256 end;
257 else
258 for cr in c_sources loop
259 log('update_shipping_info','looping c_sources');
260 log('update_shipping_info','cr.organization_id:'||cr.organization_id);
261 log('update_shipping_info','cr.subinventory_code:'||
262 cr.subinventory_code);
263 log('update_shipping_info','p_search_params.to_location_id:'||
264 p_search_params.to_location_id);
265 log('update_shipping_info','p_search_params.to_hz_location_id:'||
266 p_search_params.to_hz_location_id);
267 l_shipping_method := null;
268 for csinfo in c_shipping_info(cr.organization_id,
269 cr.subinventory_code) loop
270 log('update_shipping_info','csinfo.distance:'||csinfo.distance);
271 log('update_shipping_info','cr.distance:'||cr.distance);
272 log('update_shipping_info','p_search_params.called_from:'||
273 p_search_params.called_from);
274 if csinfo.distance is null or
275 cr.distance <= csinfo.distance or
276 (cr.distance is null and
277 csinfo.distance is not null and
278 p_search_params.called_from <> 'SCHEDULER') then
279 log('update_shipping_info','looping csinfo');
280 l_shipping_method := 'N';
281 log('update_shipping_info','l_shipping_method:'||l_shipping_method);
282 log('update_shipping_info','csinfo.arrival_date:'||
283 csinfo.arrival_date);
284 log('update_shipping_info','csinfo.shipping_cost:'||
285 csinfo.shipping_cost);
286
287 if csinfo.arrival_date <= nvl(p_search_params.need_by_date,
288 csinfo.arrival_date) then
289 log('update_shipping_info','update csp_available_parts_temp');
290
291 update csp_available_parts_temp
292 set shipping_method = csinfo.shipping_method,
293 shipping_cost = csinfo.shipping_cost,
294 arrival_date = csinfo.arrival_date
295 where organization_id = cr.organization_id
296 and nvl(subinventory_code,'-1') = nvl(cr.subinventory_code,'-1');
297 log('update_shipping_info','Records updated:'||sql%rowcount);
298 l_shipping_method := csinfo.shipping_method;
299 log('update_shipping_info','l_shipping_method:'||l_shipping_method);
300 exit;
301 end if;
302 end if;
303 end loop;
304 log('update_shipping_info','l_shipping_method:'||l_shipping_method);
305 if l_shipping_method = 'N' then --No shipping method could meet need by
306 log('update_shipping_info','delete from csp_available_parts_temp');
307 delete from csp_available_parts_temp
308 where organization_id = cr.organization_id
309 and nvl(subinventory_code,'-1') = nvl(cr.subinventory_code,'-1');
310 log('update_shipping_info','Records deleted:'||sql%rowcount);
311 end if;
312 end loop;
313 end if;
314 if l_search_method = 'SPARES' and
315 l_called_from_charges <> 'Y' then
316 log('update_shipping_info','search method SPARES deleting 1');
317 delete from csp_available_parts_temp capt
318 where not exists (select 'x' from mtl_interorg_parameters mip
319 where mip.from_organization_id = capt.organization_id
320 and mip.to_organization_id = l_organization_id)
321 and capt.organization_id <> l_organization_id
322 and source_type_code not in ('DEDICATED','MYSELF');
323 log('update_shipping_info','Records deleted:'||sql%rowcount);
324 log('update_shipping_info','search method SPARES deleting 2');
325 end if;
326 delete from csp_available_parts_temp
327 where shipping_method is null
328 and source_type_code not in ('DEDICATED','MYSELF','UNMANNED','TECHNICIAN');
329 log('update_shipping_info','Records deleted:'||sql%rowcount);
330 log('update_shipping_info','End');
331 end;
332
333 procedure site_dedicated_spares as
334 cursor c_sites is
335 select cpp.organization_id,
336 cpp.secondary_inventory,
337 hl.geometry
338 from csp_planning_parameters cpp,
339 csp_dedicated_sites cds,
340 jtf_tasks_b jtb,
341 csp_requirement_headers crh,
342 hz_party_sites hps,
343 hz_locations hl
344 where cds.planning_parameters_id = cpp.planning_parameters_id
345 and nvl(cpp.stocking_site_excl,'N') = 'N'
346 and cpp.stocking_site_type = 'DEDICATED'
347 and jtb.address_id = cds.party_site_id
348 and jtb.task_id = crh.task_id
349 and hps.party_site_id = jtb.address_id
350 and hl.location_id = hps.location_id
351 and crh.requirement_header_id = p_search_params.requirement_header_id;
352
353 cursor c_enough is
354 select sum(capt.supplied_quantity)-min(crpt.quantity)
355 from csp_required_parts_temp crpt,
356 csp_available_parts_temp capt
357 where crpt.item_type = 'BASE'
358 and capt.source_type_code = 'DEDICATED'
359 and capt.required_item_id = crpt.inventory_item_id
360 order by 1 asc;
361
362 begin
363 log('site_dedicated_spares','Begin');
364 for csites in c_sites loop
365 log('site_dedicated_spares','In c_sites loop');
366 log('site_dedicated_spares','Inserting into csp_available_parts_temp');
367 insert into csp_available_parts_temp(
368 organization_id,
369 subinventory_code,
370 source_type_code,
371 required_item_id,
372 required_item_rev,
373 required_quantity,
374 supplied_item_id,
375 supplied_item_rev,
376 supplied_quantity,
377 supplied_item_type,
378 shipping_date,
379 shipping_method,
380 shipping_cost,
381 arrival_date,
382 distance,
383 geometry,
384 open_or_closed)
385 select
386 csites.organization_id,
387 csites.secondary_inventory,
388 'DEDICATED',
389 crpt.inventory_item_id,
390 crpt.revision,
391 crpt.quantity,
392 crpt.alternate_item_id,
393 null supplied_item_rev,
394 csp_part_search_pvt.get_avail_qty(csites.organization_id,
395 csites.secondary_inventory,
396 crpt.alternate_item_id,
397 crpt.revision,
398 p_search_params.quantity_type),
399 crpt.item_type,
400 null shipping_date,
401 null shipping_method,
402 0 shipping_cost,
403 sysdate arrival_date,
404 0 distance,
405 csites.geometry,
406 'OPEN'
407 from csp_required_parts_temp crpt;
408 log('site_dedicated_spares','Records inserted:'||sql%rowcount);
409 end loop;
410 open c_enough;
411 fetch c_enough into l_enough;
412 log('site_dedicated_spares','l_enough_a:'||l_enough);
413 close c_enough;
414 l_enough := nvl(l_enough,-1);
415 log('site_dedicated_spares','l_enough_b:'||l_enough);
416 end;
417
418 procedure my_inventory as
419 begin
420 log('my_inventory','begin');
421 if p_search_params.my_inventory then
422 log('my_inventory','insert into csp_available_parts');
423 insert into csp_available_parts_temp(
424 organization_id,
425 subinventory_code,
426 source_type_code,
427 required_item_id,
428 required_item_rev,
429 required_quantity,
430 supplied_item_id,
431 supplied_item_rev,
432 supplied_quantity,
433 supplied_item_type,
434 shipping_date,
435 shipping_method,
436 shipping_cost,
437 arrival_date,
438 distance,
439 geometry,
440 open_or_closed)
441 select
442 l_organization_id,
443 l_subinventory_code,
444 'MYSELF',
445 crpt.inventory_item_id,
446 crpt.revision,
447 crpt.quantity,
448 crpt.alternate_item_id,
449 null supplied_item_rev,
450 csp_part_search_pvt.get_avail_qty(l_organization_id,
451 l_subinventory_code,
452 crpt.alternate_item_id,
453 crpt.revision,
454 p_search_params.quantity_type),
455 crpt.item_type,
456 null shipping_date,
457 null shipping_method,
458 0 shipping_cost,
459 sysdate arrival_date,
460 0 distance,
461 l_my_location,
462 'OPEN'
463 from csp_required_parts_temp crpt;
464 log('my_inventory','inserted records:'||sql%rowcount);
465 end if;
466 end;
467
468 procedure technicians as
469 cursor c_geocode is
470 select hl.geometry geometry,
471 csf_gps_pub.get_location(
472 null,csi.owner_resource_id,csi.owner_resource_type,sysdate) point,
473 cpp.organization_id,
474 cpp.secondary_inventory,
475 csi.condition_type,
476 csi.owner_resource_type,
477 csi.owner_resource_id
478 from csp_sec_inventories csi,
479 csp_planning_parameters cpp,
480 hz_locations hl
481 where cpp.organization_id = csi.organization_id
482 and cpp.secondary_inventory = csi.secondary_inventory_name
483 and cpp.stocking_site_type = 'TECHNICIAN'
484 and hl.location_id(+) = cpp.hz_location_id
485 and (csi.organization_id <> l_organization_id
486 or nvl(csi.secondary_inventory_name,'-1') <> l_subinventory_code)
487 and csi.condition_type = 'G'
488 and exists
489 (select 'x'
490 from mtl_onhand_quantities moq,
491 csp_required_parts_temp crpt
492 where moq.organization_id = csi.organization_id
493 and moq.subinventory_code = csi.secondary_inventory_name
494 and moq.inventory_item_id = crpt.alternate_item_id);
495
496 l_geometry mdsys.sdo_geometry;
497 l_point mdsys.sdo_point_type := mdsys.sdo_point_type(-9999,-9999,0);
498 l_test number := 0;
499 cursor c_compare(p_point mdsys.sdo_point_type) is
500 select 1 from dual
501 where l_point <> nvl(p_point,l_point);
502
503 begin
504 log('technicians','begin');
505 if p_search_params.technicians then
506 for cr in c_geocode loop
507 log('technicians','in c_geocode loop');
508 -- Check to see if GPS returns valid point
509 open c_compare(cr.point);
510 fetch c_compare into l_test;
511 close c_compare;
512 log('technicians','l_test:'||l_test);
513 if l_test = 1 then
514 l_geometry := MDSYS.SDO_GEOMETRY(2001,8307,cr.point,null,null);
515 else
516 l_geometry := cr.geometry;
517 end if;
518 l_test := 0;
519 log('technicians','insert into CAPT');
520 insert into csp_available_parts_temp(
521 organization_id,
522 subinventory_code,
523 source_type_code,
524 required_item_id,
525 required_item_rev,
526 required_quantity,
527 supplied_item_id,
528 supplied_item_rev,
529 supplied_quantity,
530 supplied_item_type,
531 shipping_date,
532 shipping_method,
533 shipping_cost,
534 arrival_date,
535 distance,
536 geometry,
537 open_or_closed)
538 select
539 cr.organization_id,
540 cr.secondary_inventory,
541 'TECHNICIAN',
542 crpt.inventory_item_id,
543 crpt.revision,
544 crpt.quantity,
545 crpt.alternate_item_id,
546 null supplied_item_rev,
547 csp_part_search_pvt.get_avail_qty(cr.organization_id,
548 cr.secondary_inventory,
549 crpt.alternate_item_id,
550 crpt.revision,
551 p_search_params.quantity_type),
552 crpt.item_type,
553 null shipping_date,
554 null shipping_method,
555 0 shipping_cost,
556 sysdate arrival_date,
557 round(sdo_geom.sdo_distance(l_my_location,l_geometry,
558 1000,l_distance_uom),1) distance,
559 l_geometry,
560 case nvl(csoc.object_type,'CLOSED')
561 when 'CLOSED' then 'CLOSED'
562 else 'OPEN'
563 end
564 from
565 csp_required_parts_temp crpt,
566 cac_sr_object_capacity csoc,
567 csp_sec_inventories csi
568 where (cr.organization_id <> l_organization_id
569 or nvl(cr.secondary_inventory,'-1') <> l_subinventory_code)
570 and csi.organization_id = cr.organization_id
571 and csi.secondary_inventory_name = cr.secondary_inventory
572 and cr.condition_type = 'G'
573 and decode(p_search_params.distance,null,-1,
574 round(sdo_geom.sdo_distance(
575 l_my_location,
576 l_geometry,
577 1000,
578 l_distance_uom),1)) <= nvl(p_search_params.distance,-1)
579 and csoc.object_type(+) = csi.owner_resource_type
580 and csoc.object_id(+) = csi.owner_resource_id
581 and sysdate between csoc.start_date_time(+) and csoc.end_date_time(+)
582 and decode(csoc.object_type,null,'CLOSED','OPEN')
583 in ('OPEN',l_open_or_closed);
584 log('technicians','Records inserted:'||sql%rowcount);
585 end loop;
586 end if;
587 log('technicians','end');
588 end;
589
590 procedure unmanned_warehouses as
591 begin
592 log('unmanned_warehouses','begin');
593 if p_search_params.unmanned_warehouses then
594 log('unmanned_warehouses','insert into CAPT');
595 insert into csp_available_parts_temp(
596 organization_id,
597 subinventory_code,
598 source_type_code,
599 required_item_id,
600 required_item_rev,
601 required_quantity,
602 supplied_item_id,
603 supplied_item_rev,
604 supplied_quantity,
605 supplied_item_type,
606 shipping_date,
607 shipping_method,
608 shipping_cost,
609 arrival_date,
610 distance,
611 geometry,
612 open_or_closed)
613 select
614 cpp.organization_id,
615 cpp.secondary_inventory,
616 cpp.stocking_site_type,
617 crpt.inventory_item_id,
618 crpt.revision,
619 crpt.quantity,
620 crpt.alternate_item_id,
621 null supplied_item_rev,
622 csp_part_search_pvt.get_avail_qty(cpp.organization_id,
623 cpp.secondary_inventory,
624 crpt.alternate_item_id,
625 crpt.revision,
626 p_search_params.quantity_type),
627 crpt.item_type,
628 null shipping_date,
629 null shipping_method,
630 0 shipping_cost,
631 sysdate arrival_date,
632 round(sdo_geom.sdo_distance(l_my_location,hl.geometry,
633 1000,l_distance_uom),1) distance,
634 hl.geometry,
635 decode(sign(hz_timezone_pub.convert_datetime(
636 l_server_timezone_id,
637 cpp.timezone_id,
638 sysdate)-nvl(cocv.start_time,sysdate-1))+
639 sign(nvl(cocv.end_time,sysdate+1)-hz_timezone_pub.convert_datetime(
640 l_server_timezone_id,
641 cpp.timezone_id,
642 sysdate)),2,'OPEN','CLOSED')
643 from
644 csp_required_parts_temp crpt,
645 csp_planning_parameters cpp,
646 csp_open_closed_v cocv,
647 hz_locations hl
648 where cpp.stocking_site_type = 'UNMANNED'
649 and nvl(cpp.stocking_site_excl,'N') = 'N'
650 and decode(p_search_params.distance,null,-1,
651 round(sdo_geom.sdo_distance(
652 l_my_location,
653 hl.geometry,
654 1000,
655 l_distance_uom),1)) <= nvl(p_search_params.distance,-1)
656 and hl.location_id (+) = cpp.hz_location_id
657 and (cpp.organization_id <> l_organization_id
658 or nvl(cpp.secondary_inventory,'-1') <> l_subinventory_code)
659 and cocv.calendar_id(+) = cpp.calendar_id
660 and decode(sign(hz_timezone_pub.convert_datetime(
661 l_server_timezone_id,
662 cpp.timezone_id,
663 sysdate)-nvl(cocv.start_time,sysdate-1))+
664 sign(nvl(cocv.end_time,sysdate+1)-hz_timezone_pub.convert_datetime(
665 l_server_timezone_id,
666 cpp.timezone_id,
667 sysdate)),2,'OPEN','CLOSED')
668 in ('OPEN',l_open_or_closed);
669 log('unmanned_warehouses','Records inserted:'||sql%rowcount);
670 end if;
671 log('unmanned_warehouses','end');
672 exception
673 when others then
674 log('unmanned_warehouses','when others exception');
675 log('unmanned_warehouses','sqlcode:'||sqlcode);
676 log('unmanned_warehouses','sqlerrm:'||sqlerrm);
677 end;
678
679 procedure manned_warehouses as
680 cursor c_shipping is
681 select distinct organization_id
682 from csp_shipping_details_v
683 where ((to_location_id = p_search_params.to_location_id
684 and location_source = 'HR')
685 or (to_location_id = p_search_params.to_hz_location_id
686 and location_source = 'HZ'));
687 begin
688 log('manned_warehouses','begin');
689 if p_search_params.manned_warehouses then
690 for cr in c_shipping loop
691 log('manned_warehouses','insert into CAPT');
692 insert into csp_available_parts_temp(
693 organization_id,
694 subinventory_code,
695 source_type_code,
696 required_item_id,
697 required_item_rev,
698 required_quantity,
699 supplied_item_id,
700 supplied_item_rev,
701 supplied_quantity,
702 supplied_item_type,
703 shipping_date,
704 shipping_method,
705 shipping_cost,
706 arrival_date,
707 distance,
708 geometry,
709 open_or_closed)
710 select
711 cpp.organization_id,
712 cpp.secondary_inventory,
713 cpp.stocking_site_type,
714 crpt.inventory_item_id,
715 crpt.revision,
716 crpt.quantity,
717 crpt.alternate_item_id,
718 null supplied_item_rev,
719 csp_part_search_pvt.get_avail_qty(cpp.organization_id,
720 cpp.secondary_inventory,
721 crpt.alternate_item_id,
722 crpt.revision,
723 p_search_params.quantity_type),
724 crpt.item_type,
725 null shipping_date,
726 null shipping_method,
727 null shipping_cost,
728 null arrival_date,
729 round(sdo_geom.sdo_distance(l_my_location,hl.geometry,
730 1000,l_distance_uom),1),
731 hl.geometry,
732 decode(sign(hz_timezone_pub.convert_datetime(
733 l_server_timezone_id,
734 cpp.timezone_id,
735 sysdate)-nvl(cocv.start_time,sysdate-1))+
736 sign(nvl(cocv.end_time,sysdate+1)-hz_timezone_pub.convert_datetime(
737 l_server_timezone_id,
738 cpp.timezone_id,
739 sysdate)),2,'OPEN','CLOSED')
740 from
741 csp_required_parts_temp crpt,
742 csp_planning_parameters cpp,
743 csp_open_closed_v cocv,
744 hz_locations hl
745 where cpp.stocking_site_type = 'MANNED'
746 and cpp.organization_id = cr.organization_id
747 and nvl(cpp.stocking_site_excl,'N') = 'N'
748 and hl.location_id (+) = cpp.hz_location_id
749 and decode(p_search_params.distance,null,-1,
750 round(sdo_geom.sdo_distance(
751 l_my_location,
752 hl.geometry,
753 1000,
754 l_distance_uom),1)) <= nvl(p_search_params.distance,-1)
755 and (cpp.organization_id <> nvl(l_organization_id, -999)
756 or nvl(cpp.secondary_inventory,'-1') <> l_subinventory_code)
757 and cocv.calendar_id(+) = cpp.calendar_id
758 and exists (select 'x'
759 from mtl_onhand_quantities moq
760 where moq.organization_id = cpp.organization_id
761 and moq.subinventory_code = nvl(cpp.secondary_inventory,
762 moq.subinventory_code)
763 and moq.inventory_item_id = crpt.alternate_item_id)
764 and decode(sign(hz_timezone_pub.convert_datetime(
765 l_server_timezone_id,
766 cpp.timezone_id,
767 sysdate)-nvl(cocv.start_time,sysdate-1))+
768 sign(nvl(cocv.end_time,sysdate+1)-hz_timezone_pub.convert_datetime(
769 l_server_timezone_id,
770 cpp.timezone_id,
771 sysdate)),2,'OPEN','CLOSED')
772 in ('OPEN',l_open_or_closed);
773 log('manned_warehouses','Inserted records:'||sql%rowcount);
774 end loop;
775 end if;
776 log('manned_warehouses','end');
777 exception
778 when others then
779 log('manned_warehouses','when others exception');
780 log('manned_warehouses','sqlcode:'||sqlcode);
781 log('manned_warehouses','sqlerrm:'||sqlerrm);
782 end;
783
784 procedure specific_warehouse as
785 begin
786 log('specific_warehouse','begin');
787 insert into csp_available_parts_temp(
788 organization_id,
789 subinventory_code,
790 source_type_code,
791 required_item_id,
792 required_item_rev,
793 required_quantity,
794 supplied_item_id,
795 supplied_item_rev,
796 supplied_quantity,
797 supplied_item_type,
798 shipping_date,
799 shipping_method,
800 shipping_cost,
801 arrival_date,
802 distance,
803 geometry,
804 open_or_closed)
805 select
806 cpp.organization_id,
807 cpp.secondary_inventory,
808 cpp.stocking_site_type,
809 crpt.inventory_item_id,
810 crpt.revision,
811 crpt.quantity,
812 crpt.alternate_item_id,
813 null supplied_item_rev,
814 csp_part_search_pvt.get_avail_qty(cpp.organization_id,
815 cpp.secondary_inventory,
816 crpt.alternate_item_id,
817 crpt.revision,
818 p_search_params.quantity_type),
819 crpt.item_type,
820 null shipping_date,
821 null shipping_method,
822 null shipping_cost,
823 null arrival_date,
824 round(sdo_geom.sdo_distance(l_my_location,hl.geometry,
825 1000,l_distance_uom),1),
826 hl.geometry,
827 decode(sign(hz_timezone_pub.convert_datetime(
828 l_server_timezone_id,
829 cpp.timezone_id,
830 sysdate)-nvl(cocv.start_time,sysdate-1))+
831 sign(nvl(cocv.end_time,sysdate+1)-hz_timezone_pub.convert_datetime(
832 l_server_timezone_id,
833 cpp.timezone_id,
834 sysdate)),2,'OPEN','CLOSED')
835 from
836 csp_required_parts_temp crpt,
837 csp_planning_parameters cpp,
838 csp_open_closed_v cocv,
839 hz_locations hl
840 where cpp.organization_id = p_search_params.source_organization_id
841 and nvl(cpp.stocking_site_excl,'N') = 'N'
842 and hl.location_id (+) = cpp.hz_location_id
843 and decode(p_search_params.distance,null,-1,
844 round(sdo_geom.sdo_distance(
845 l_my_location,
846 hl.geometry,
847 1000,
848 l_distance_uom),1)) <= nvl(p_search_params.distance,-1)
849 and nvl(cpp.secondary_inventory,'-1') =
850 nvl(p_search_params.source_subinventory,'-1')
851 and cocv.calendar_id(+) = cpp.calendar_id
852 and decode(sign(hz_timezone_pub.convert_datetime(
853 l_server_timezone_id,
854 cpp.timezone_id,
855 sysdate)-nvl(cocv.start_time,sysdate-1))+
856 sign(nvl(cocv.end_time,sysdate+1)-hz_timezone_pub.convert_datetime(
857 l_server_timezone_id,
858 cpp.timezone_id,
859 sysdate)),2,'OPEN','CLOSED')
860 in ('OPEN',l_open_or_closed);
861 log('specific_warehouse','Records inserted:'||sql%rowcount);
862 end;
863
864 procedure replenishment_source as
865 l_repl_org number;
866 l_repl_sub varchar2(30);
867 cursor c_required_parts is
868 select crpt.inventory_item_id,
869 crpt.alternate_item_id,
870 crpt.item_type item_type,
871 crpt.revision,
872 crpt.quantity
873 from csp_required_parts_temp crpt;
874
875 begin
876 log('replenishment_source','Begin');
877 if p_search_params.manned_warehouses then
878 for cr in c_required_parts loop
879 log('replenishment_source','In c_required_parts loop');
880 csp_parts_requirement.get_source_organization(
881 cr.alternate_item_id,
882 l_organization_id,
883 l_subinventory_code,
884 l_repl_org,
885 l_repl_sub);
886 log('replenishment_source','cr.alternate_item_id:'||
887 cr.alternate_item_id);
888 log('replenishment_source','l_organization_id:'||l_organization_id);
889 log('replenishment_source','l_subinventory_code:'||l_subinventory_code);
890 log('replenishment_source','l_repl_org:'||l_repl_org);
891 log('replenishment_source','l_repl_sub:'||l_repl_sub);
892 log('replenishment_source','inserting into CAPT');
893 insert into csp_available_parts_temp(
894 organization_id,
895 subinventory_code,
896 source_type_code,
897 required_item_id,
898 required_item_rev,
899 required_quantity,
900 supplied_item_id,
901 supplied_item_rev,
902 supplied_quantity,
903 supplied_item_type,
904 shipping_date,
905 shipping_method,
906 shipping_cost,
907 arrival_date,
908 distance,
909 geometry,
910 open_or_closed)
911 select
912 l_repl_org,
913 l_repl_sub,
914 'MANNED',
915 cr.inventory_item_id,
916 cr.revision,
917 cr.quantity,
918 cr.alternate_item_id,
919 null supplied_item_rev,
920 csp_part_search_pvt.get_avail_qty(l_repl_org,
921 l_repl_sub,
922 cr.alternate_item_id,
923 cr.revision,
924 p_search_params.quantity_type),
925 cr.item_type,
926 null shipping_date,
927 null shipping_method,
928 null shipping_cost,
929 null arrival_date,
930 null distance,
931 null geometry,
932 'OPEN'
933 from dual;
934 log('replenishment_source','Inserted records:'||sql%rowcount);
935 end loop;
936 end if;
937 log('replenishment_source','End');
938 end;
939
940 procedure atp as
941 cursor c_required_parts is
942 select crl.requirement_line_id,
943 crl.inventory_item_id,
944 crl.revision,
945 crl.uom_code,
946 crl.required_quantity,
947 crl.ship_complete_flag,
948 crh.destination_organization_id,
949 crh.destination_subinventory,
950 crh.need_by_date,
951 crh.timezone_id,
952 crh.ship_to_location_id
953 from csp_requirement_lines crl,
954 csp_requirement_headers crh
955 where crh.requirement_header_id = p_search_params.requirement_header_id
956 and crl.requirement_header_id = crh.requirement_header_id;
957
958 l_resource_rec csp_sch_int_pvt.csp_sch_resources_rec_typ;
959 l_destination_organization_id number;
960 l_timezone_id number;
961 l_ship_to_location_id number;
962 l_destination_subinventory varchar2(10);
963 l_need_by_date date;
964 l_parts_list_rec csp_sch_int_pvt.csp_parts_rec_type;
965 l_parts_list_tbl csp_sch_int_pvt.csp_parts_tbl_typ1;
966 l_avail_list_tbl csp_sch_int_pvt.available_parts_tbl_typ1;
967 i number := 0;
968 begin
969 log('atp','Begin');
970 for cr in c_required_parts loop
971 log('atp','in c_required_parts loop');
972 i := i + 1;
973 l_parts_list_Rec.line_id := cr.requirement_line_id;
974 l_parts_list_rec.item_id := cr.inventory_item_id;
975 l_parts_list_rec.revision := cr.revision;
976 l_parts_list_rec.item_uom := cr.uom_code;
977 l_parts_list_rec.quantity := cr.required_quantity;
978 l_parts_list_rec.ship_set_name := cr.ship_complete_flag;
979 l_parts_list_tbl(i) := l_parts_list_rec;
980 l_destination_organization_id := cr.destination_organization_id;
981 l_destination_subinventory := cr.destination_subinventory;
982 l_need_by_date := cr.need_by_date;
983 l_timezone_id := cr.timezone_id;
984 l_ship_to_location_id := cr.ship_to_location_id;
985 log('atp','l_parts_list_rec.line_id:'||l_parts_list_rec.line_id);
986 log('atp','l_parts_list_rec.item_id:'||l_parts_list_rec.item_id);
987 log('atp','l_parts_list_rec.revision:'||l_parts_list_rec.revision);
988 log('atp','l_parts_list_rec.item_uom:'||l_parts_list_rec.item_uom);
989 log('atp','l_parts_list_rec.quantity:'||l_parts_list_rec.quantity);
990 log('atp','l_parts_list_rec.ship_set_name:'||l_parts_list_rec.ship_set_name);
991 log('atp','l_destination_organization_id:'||l_destination_organization_id);
992 log('atp','l_destination_subinventory:'||l_destination_subinventory);
993 log('atp','l_need_by_date:'||l_need_by_date);
994 log('atp','l_timezone_id:'||l_timezone_id);
995 log('atp','l_ship_to_location_id:'||l_ship_to_location_id);
996 end loop;
997 l_resource_rec.resource_id := p_search_params.resource_id;
998 l_resource_rec.resource_type := p_search_params.resource_type;
999 log('atp','calling csp_sch_int_pvt.check_parts_availability');
1000 csp_sch_int_pvt.check_parts_availability(
1001 p_resource => l_resource_rec,
1002 p_organization_id => l_destination_organization_id,
1003 p_subinv_code => l_destination_subinventory,
1004 p_need_by_date => l_need_by_date,
1005 p_parts_list => l_parts_list_tbl,
1006 p_timezone_id => l_timezone_id,
1007 x_availability => l_avail_list_tbl,
1008 x_return_status => l_return_status,
1009 x_msg_data => l_msg_data,
1010 x_msg_count => l_msg_count,
1011 p_location_id => l_ship_to_location_id,
1012 p_include_alternates => p_search_params.include_alternates
1013 );
1014 log('atp','after csp_sch_int_pvt.check_parts_availability');
1015 log('atp','x_return_status:'||x_return_status);
1016 for i in 1..l_avail_list_tbl.count loop
1017 log('atp','insert into CAPT');
1018 insert into csp_available_parts_temp(
1019 organization_id,
1020 subinventory_code,
1021 source_type_code,
1022 required_item_id,
1023 required_item_rev,
1024 required_quantity,
1025 supplied_item_id,
1026 supplied_item_rev,
1027 supplied_quantity,
1028 supplied_item_type,
1029 shipping_date,
1030 shipping_method,
1031 shipping_cost,
1032 arrival_date,
1033 distance,
1034 open_or_closed,
1035 geometry)
1036 select
1037 l_avail_list_tbl(i).source_org_id,
1038 l_avail_list_tbl(i).sub_inventory_code,
1039 'MANNED',
1040 l_avail_list_tbl(i).item_id,
1041 l_avail_list_tbl(i).revision,
1042 l_avail_list_tbl(i).ordered_quantity, -- replaced required_quantity with ordered_quantity
1043 l_avail_list_tbl(i).item_id,
1044 l_avail_list_tbl(i).revision,
1045 l_avail_list_tbl(i).available_quantity,
1046 decode(l_avail_list_tbl(i).item_type, 2, 'SUBSTITUTE', 8, 'SUPERSEDED', 'BASE'), -- used decode as item_type here is a number
1047 null,
1048 l_avail_list_tbl(i).shipping_methode,
1049 null,
1050 l_avail_list_tbl(i).arraival_date,
1051 null,
1052 'OPEN',
1053 null
1054 from dual;
1055 log('atp','Inserted records:'||sql%rowcount);
1056 end loop;
1057 log('atp','End');
1058 end;
1059
1060 procedure get_my_location as
1061
1062 l_to_hz_location_id number := null;
1063 l_result_array csf_lf_pub.csf_lf_resultarray;
1064
1065 cursor c_my_location is
1066 select hl.geometry
1067 from hz_locations hl,
1068 csp_planning_parameters cpp
1069 where cpp.organization_id = l_organization_id
1070 and cpp.secondary_inventory = l_subinventory_code
1071 and hl.location_id = cpp.hz_location_id;
1072
1073 cursor c_to_location is
1074 select geometry
1075 from hz_locations
1076 where location_id = l_to_hz_location_id;
1077
1078 cursor c_address is
1079 select hl.address1,hl.address2,hl.address3,hl.address4,
1080 hl.city,hl.postal_code,hl.state,ftt.territory_short_name,
1081 hl.province,hl.county
1082 from hz_locations hl, fnd_territories_tl ftt
1083 where hl.location_id = l_to_hz_location_id
1084 and ftt.territory_code = hl.country
1085 and ftt.language = 'US';
1086
1087 cursor c_hz_address is
1088 select hps.location_id
1089 from hz_cust_site_uses_all hcsua,
1090 po_location_associations_all plaa,
1091 hz_cust_acct_sites_all hcasa,
1092 hz_party_sites hps
1093 where plaa.location_id = p_search_params.to_location_id
1094 and hcsua.site_use_id = plaa.site_use_id
1095 and hcsua.site_use_code = 'SHIP_TO'
1096 and hcsua.status = 'A'
1097 and hcasa.cust_acct_site_id = hcsua.cust_acct_site_id
1098 and hps.party_site_id = hcasa.party_site_id;
1099
1100 l_array hz_geocode_pkg.loc_array := hz_geocode_pkg.loc_array();
1101 l_rec hz_location_v2pub.location_rec_type;
1102 l_http_ad VARCHAR2(200);
1103 cpt number;
1104 l_distance_uom_code varchar2(30);
1105
1106
1107 begin
1108 l_to_hz_location_id := p_search_params.to_hz_location_id;
1109 if l_to_hz_location_id is null then
1110 open c_hz_address;
1111 fetch c_hz_address into l_to_hz_location_id;
1112 close c_hz_address;
1113 end if;
1114 log('get_my_location','Begin');
1115 log('get_my_location','l_return_status:'||l_return_status);
1116 -- Get center point
1117 if p_search_params.current_location then
1118 log('get_my_location','p_search_params.current_location = true');
1119 open c_my_location;
1120 fetch c_my_location into l_my_location;
1121 close c_my_location;
1122 elsif nvl(l_to_hz_location_id,0) > 0 then
1123 log('get_my_location','l_to_hz_location_id:'||l_to_hz_location_id);
1124 open c_to_location;
1125 fetch c_to_location into l_my_location;
1126 close c_to_location;
1127 -- Geocode address
1128 if l_my_location is null then
1129 log('get_my_location','l_my_location is null');
1130 cpt := 1;
1131 l_array.EXTEND;
1132 log('get_my_location','Fetch address');
1133 open c_address;
1134 fetch c_address into l_array(cpt).address1,
1135 l_array(cpt).address2,
1136 l_array(cpt).address3,
1137 l_array(cpt).address4,
1138 l_array(cpt).city,
1139 l_array(cpt).postal_code,
1140 l_array(cpt).state,
1141 l_array(cpt).country,
1142 l_array(cpt).province,
1143 l_array(cpt).county;
1144 close c_address;
1145 log('get_my_location','calling resolve_address');
1146 csf_resource_address_pvt.resolve_address (
1147 p_api_version => 1.0,
1148 p_init_msg_list => fnd_api.g_false,
1149 p_country => nvl (l_array(cpt).country, '_'),
1150 p_state => nvl (l_array(cpt).state,'_'),
1151 p_city => nvl (l_array(cpt).city, '_'),
1152 p_county => nvl (l_array(cpt).county,'_'),
1153 p_province => nvl (l_array(cpt).province, '_' ),
1154 p_postalcode => nvl (l_array(cpt).postal_code, '_' ),
1155 p_address1 => nvl (l_array(cpt).address1, '_' ),
1156 p_address2 => nvl (l_array(cpt).address2, '_' ),
1157 p_address3 => nvl (l_array(cpt).address3, '_' ),
1158 p_address4 => nvl (l_array(cpt).address4, '_' ),
1159 p_building_num => '_',
1160 p_alternate => '_',
1161 p_location_id => nvl (l_to_hz_location_id, -1 ),
1162 p_country_code => '_',
1163 x_return_status => l_return_status,
1164 x_msg_count => l_msg_count,
1165 x_msg_data => l_msg_data,
1166 x_geometry => l_my_location );
1167 /*
1168 if l_my_location is null then
1169 fnd_profile.get('HZ_GEOCODE_WEBSITE', l_http_ad);
1170 log('get_my_location','l_http_ad:'||l_http_ad);
1171 l_array(cpt).location_id := l_to_hz_location_id;
1172 log('get_my_location','calling hz_geocode_pkg.get_spatial_coords');
1173 hz_geocode_pkg.get_spatial_coords(
1174 p_loc_array => l_array,
1175 p_name => null,
1176 p_http_ad => l_http_ad,
1177 p_proxy => null,
1178 p_port => null,
1179 p_retry => 5,
1180 x_return_status => l_return_status,
1181 x_msg_count => l_msg_count,
1182 x_msg_data => l_msg_data);
1183 l_my_location := l_array(cpt).geometry;
1184 end if;
1185 */
1186 end if;
1187 end if;
1188 l_distance_uom_code := nvl(p_search_params.distance_uom,
1189 fnd_profile.value('CSFW_DEFAULT_DISTANCE_UNIT'));
1190 log('get_my_location','l_distance_uom_code:'||l_distance_uom_code);
1191 if l_distance_uom_code = 'KM' then
1192 l_distance_uom := 'unit=km';
1193 elsif l_distance_uom_code = 'METER' then
1194 l_distance_uom := 'unit=m';
1195 elsif l_distance_uom_code = 'MILE' then
1196 l_distance_uom := 'unit=mile';
1197 end if;
1198 log('get_my_location','l_distance_uom:'||l_distance_uom);
1199 log('get_my_location','l_return_status:'||l_return_status);
1200
1201 --ignore errors from geo-coding process
1202 l_return_status := fnd_api.g_ret_sts_success;
1203 FND_MSG_PUB.initialize;
1204 log('get_my_location','End');
1205 exception
1206 when others then
1207 log('get_my_location','when others exception');
1208 log('get_my_location','sqlcode:'||sqlcode);
1209 log('get_my_location','sqlerrm:'||sqlerrm);
1210 l_return_status := fnd_api.g_ret_sts_success;
1211 FND_MSG_PUB.initialize;
1212 end;
1213
1214 function get_organization_id return number as
1215 cursor c_organization_id is
1216 select organization_id, subinventory_code
1217 from csp_inv_loc_assignments
1218 where resource_type = p_search_params.resource_type
1219 and resource_id = p_search_params.resource_id
1220 and default_code = 'IN';
1221 begin
1222 log('get_organization_id','Begin');
1223 if p_search_params.resource_id is not null then
1224 log('get_organization_id',
1225 'p_search_params.resource_id:'||p_search_params.resource_id);
1226 open c_organization_id;
1227 fetch c_organization_id into l_organization_id,l_subinventory_code;
1228 close c_organization_id;
1229 log('get_organization_id','l_organization_id:'||l_organization_id);
1230 log('get_organization_id','l_subinventory_code:'||l_subinventory_code);
1231 else
1232 log('get_organization_id','p_search_params.resource_id is null');
1233 l_organization_id := fnd_profile.value('CS_INV_VALIDATION_ORG');
1234 l_subinventory_code := fnd_api.g_miss_char;
1235 l_called_from_charges := 'Y';
1236 end if;
1237 log('get_organization_id','l_organization_id:'||l_organization_id);
1238 log('get_organization_id','End');
1239 return l_organization_id;
1240 end;
1241
1242 procedure insert_parts as
1243 i number :=0;
1244 l_supersede_items csp_supersessions_pvt.number_arr;
1245 l_required_parts required_parts_tbl;
1246 l_quantity number := 0;
1247 l_total_quantity number := 0;
1248
1249 cursor c_requirement is
1250 select inventory_item_id,revision,required_quantity, requirement_line_id
1251 from csp_requirement_lines crl
1252 where requirement_header_id = p_search_params.requirement_header_id;
1253
1254 cursor c_req_line_details(p_requirement_line_id number) is
1255 select source_type, source_id
1256 from csp_req_line_details
1257 where requirement_line_id = p_requirement_line_id;
1258
1259 cursor c_reservations(p_reservation_id number) is
1260 select reservation_quantity
1261 from mtl_reservations
1262 where reservation_id = p_reservation_id;
1263
1264 cursor c_move_orders(p_mo_line_id number) is
1265 select quantity
1266 from mtl_txn_request_lines
1267 where line_id = p_mo_line_id;
1268
1269 cursor c_internal_orders(p_io_line_id number) is
1270 select ordered_quantity
1271 from oe_order_lines_all
1272 where line_id = p_io_line_id;
1273
1274 cursor c_purchase_reqs(p_po_line_id number) is
1275 select quantity
1276 from po_lines_all
1277 where po_line_id = p_po_line_id;
1278
1279 -- bug # 12554921
1280 cursor c_get_temp_items is
1281 select inventory_item_id, revision, quantity
1282 from csp_required_parts_temp;
1283
1284 l_master_org_id number;
1285 l_insert_rec_c number;
1286
1287 cursor c_get_master_org is
1288 select master_organization_id
1289 from mtl_parameters
1290 where organization_id = l_organization_id;
1291
1292 cursor c_get_sup_items (v_inventory_item_id number, v_organization_id number, v_rel_type number) is
1293 SELECT related_item_id
1294 FROM mtl_related_items
1295 WHERE organization_id = v_organization_id
1296 AND TRUNC(sysdate) BETWEEN TRUNC(NVL(start_date,sysdate)) AND TRUNC(NVL(end_date,sysdate))
1297 AND relationship_type_id = v_rel_type
1298 start with inventory_item_id = v_inventory_item_id
1299 CONNECT BY nocycle prior related_item_id = inventory_item_id
1300 UNION
1301 SELECT inventory_item_id AS related_item_id
1302 FROM mtl_related_items
1303 WHERE organization_id = v_organization_id
1304 AND relationship_type_id = v_rel_type
1305 AND TRUNC(sysdate) BETWEEN TRUNC(NVL(start_date,sysdate)) AND TRUNC(NVL(end_date,sysdate))
1306 START WITH related_item_id = v_inventory_item_id
1307 and reciprocal_flag = 'Y'
1308 CONNECT BY nocycle prior inventory_item_id||prior reciprocal_flag = related_item_id||reciprocal_flag;
1309
1310 begin
1311 log('insert_parts','Begin');
1312 if p_search_params.requirement_header_id is not null then
1313 log('insert_parts',
1314 'p_search_params.requirement_header_id:'||
1315 p_search_params.requirement_header_id);
1316 for cr in c_requirement loop
1317 log('insert_parts','in c_requirement loop');
1318 for cord in c_req_line_details(cr.requirement_line_id) loop
1319 log('insert_parts','in c_req_line_details loop');
1320 log('insert_parts','cr.requirement_line_id:'||cr.requirement_line_id);
1321 if cord.source_type = 'RES' then
1322 open c_reservations(cord.source_id);
1323 fetch c_reservations into l_quantity;
1324 close c_reservations;
1325 log('insert_parts','RES l_quantity:'||l_quantity);
1326 l_total_quantity := l_total_quantity + l_quantity;
1327 log('insert_parts','RES l_total_quantity:'||l_total_quantity);
1328 elsif cord.source_type = 'MO' then
1329 open c_move_orders(cord.source_id);
1330 fetch c_move_orders into l_quantity;
1331 close c_move_orders;
1332 log('insert_parts','MO l_quantity:'||l_quantity);
1333 l_total_quantity := l_total_quantity + l_quantity;
1334 log('insert_parts','MO l_total_quantity:'||l_total_quantity);
1335 elsif cord.source_type = 'IO' then
1336 open c_internal_orders(cord.source_id);
1337 fetch c_internal_orders into l_quantity;
1338 close c_internal_orders;
1339 log('insert_parts','IO l_quantity:'||l_quantity);
1340 l_total_quantity := l_total_quantity + l_quantity;
1341 log('insert_parts','IO l_total_quantity:'||l_total_quantity);
1342 elsif cord.source_type = 'POREQ' then
1343 open c_purchase_reqs(cord.source_id);
1344 fetch c_purchase_reqs into l_quantity;
1345 close c_purchase_reqs;
1346 log('insert_parts','POREQ l_quantity:'||l_quantity);
1347 l_total_quantity := l_total_quantity + l_quantity;
1348 log('insert_parts','POREQ l_total_quantity:'||l_total_quantity);
1349 end if;
1350 end loop;
1351
1352 log('insert_parts','cr.required_quantity:'||cr.required_quantity);
1353 if cr.required_quantity - l_total_quantity > 0 then
1354 i := i+1;
1355 l_required_parts(i).inventory_item_id := cr.inventory_item_id;
1356 l_required_parts(i).revision := cr.revision;
1357 l_required_parts(i).quantity := cr.required_quantity-l_total_quantity;
1358 log('insert_parts',
1359 'l_required_parts.inventory_item_id:'||
1360 l_required_parts(i).inventory_item_id);
1361 log('insert_parts',
1362 'l_required_parts.revision:'||l_required_parts(i).revision);
1363 log('insert_parts',
1364 'l_required_parts.quantity:'||l_required_parts(i).quantity);
1365 end if;
1366 l_quantity := 0;
1367 l_total_quantity := 0;
1368 end loop;
1369 else
1370 log('insert_parts','p_search_params.requirement_header_id is null');
1371 l_required_parts := p_required_parts;
1372 end if;
1373 for i in 1..l_required_parts.count loop
1374 log('insert_parts','In l_required_parts.count loop i:'||i);
1375 log('insert_parts','Insert into csp_required_parts_temp');
1376 insert into csp_required_parts_temp(inventory_item_id,
1377 revision,
1378 alternate_item_id,
1379 quantity,item_type)
1380 values (l_required_parts(i).inventory_item_id,
1381 l_required_parts(i).revision,
1382 l_required_parts(i).inventory_item_id,
1383 l_required_parts(i).quantity,
1384 'BASE');
1385 log('insert_parts','Inserted records:'||sql%rowcount);
1386 end loop;
1387 -- Supersessions
1388 if p_search_params.include_alternates then
1389 log('insert_parts','insert supersessions into CRPT');
1390 open c_get_master_org;
1391 fetch c_get_master_org into l_master_org_id;
1392 close c_get_master_org;
1393
1394 l_insert_rec_c := 0;
1395 for r_req_items in c_get_temp_items
1396 loop
1397 for r_rel_item in c_get_sup_items(r_req_items.inventory_item_id, l_master_org_id, 8)
1398 loop
1399 if r_rel_item.related_item_id <> r_req_items.inventory_item_id then
1400 insert into csp_required_parts_temp(inventory_item_id,
1401 revision,
1402 alternate_item_id,
1403 quantity,
1404 item_type)
1405 values (r_req_items.inventory_item_id,
1406 r_req_items.revision,
1407 r_rel_item.related_item_id,
1408 r_req_items.quantity,
1409 'SUPERSEDED');
1410 l_insert_rec_c := l_insert_rec_c + 1;
1411 end if;
1412 end loop;
1413 end loop;
1414
1415 log('insert_parts','Inserted records:' || l_insert_rec_c);
1416
1417 -- Substitutes
1418 log('insert_parts','insert substitutes into CRPT');
1419 insert into csp_required_parts_temp(inventory_item_id,
1420 revision,
1421 alternate_item_id,
1422 quantity,item_type)
1423 select crpt.inventory_item_id,
1424 crpt.revision,
1425 mriv.related_item_id,
1426 crpt.quantity,
1427 'SUBSTITUTE'
1428 from mtl_related_items_view mriv,
1429 mtl_parameters mp,
1430 csp_required_parts_temp crpt
1431 where mp.organization_id = l_organization_id
1432 and mriv.organization_id = mp.master_organization_id
1433 and mriv.inventory_item_id = crpt.inventory_item_id
1434 and mriv.relationship_type_id = 2
1435 and crpt.item_type = 'BASE'
1436 and trunc(sysdate) between trunc(nvl(mriv.start_date,sysdate))
1437 and trunc(nvl(mriv.end_date,sysdate))
1438 and not exists(
1439 select 'x'
1440 from csp_required_parts_temp
1441 where alternate_item_id = mriv.related_item_id);
1442
1443 log('insert_parts','Inserted records:'||sql%rowcount);
1444
1445 /*
1446 -- not sure if this is a valid case for SUBSTITUTE
1447
1448 l_insert_rec_c := 0;
1449 for r_req_items in c_get_temp_items
1450 loop
1451 for r_rel_item in c_get_sup_items(r_req_items.inventory_item_id, l_master_org_id, 2)
1452 loop
1453 if r_rel_item.related_item_id <> r_req_items.inventory_item_id then
1454 insert into csp_required_parts_temp(inventory_item_id,
1455 revision,
1456 alternate_item_id,
1457 quantity,
1458 item_type)
1459 values (r_req_items.inventory_item_id,
1460 r_req_items.revision,
1461 r_rel_item.related_item_id,
1462 r_req_items.quantity,
1463 'SUBSTITUTE');
1464 l_insert_rec_c := l_insert_rec_c + 1;
1465 end if;
1466 end loop;
1467 end loop;
1468
1469 log('insert_parts','Inserted records:'||l_insert_rec_c);
1470 */
1471 end if;
1472 log('insert_parts','End');
1473 end;
1474 begin
1475 if p_search_params.resource_id is null then
1476 l_called_from_charges := 'Y';
1477 delete from csp_required_parts_temp;
1478 delete from csp_available_parts_temp;
1479 end if;
1480 log('main','Begin');
1481 log('main','p_search_params.search_method:'||p_search_params.search_method);
1482 log('main','p_search_params.quantity_type:'||p_search_params.quantity_type);
1483 log('main','p_search_params.need_by_date:'||
1484 to_char(p_search_params.need_by_date,'dd-mon-yyyy hh24:mi:ss'));
1485 log('main','p_search_params.resource_type:'||p_search_params.resource_type);
1486 log('main','p_search_params.resource_id:'||p_search_params.resource_id);
1487 log('main','p_search_params.distance:'||p_search_params.distance);
1488 log('main','p_search_params.distance_uom:'||p_search_params.distance_uom);
1489 log('main','p_search_params.source_organization_id:'||
1490 p_search_params.source_organization_id);
1491 log('main','p_search_params.source_subinventory:'||
1492 p_search_params.source_subinventory);
1493 log('main','p_search_params.to_location_id:'||
1494 p_search_params.to_location_id);
1495 log('main','p_search_params.to_hz_location_id:'||
1496 p_search_params.to_hz_location_id);
1497 log('main','p_search_params.requirement_header_id:'||
1498 p_search_params.requirement_header_id);
1499 if p_search_params.my_inventory then
1500 log('main','my_inventory=true');
1501 else log('main','my_inventory=false');
1502 end if;
1503 if p_search_params.technicians then
1504 log('main','technicians=true');
1505 else log('main','technicians=false'); end if;
1506 if p_search_params.manned_warehouses then
1507 log('main','manned_warehouses=true');
1508 else log('main','manned_warehouses=false'); end if;
1509 if p_search_params.unmanned_warehouses then
1510 log('main','unmanned_warehouses=true');
1511 else log('main','unmanned_warehouses=false'); end if;
1512 if p_search_params.include_alternates then
1513 log('main','include_alternates=true');
1514 else log('main','include_alternates=false'); end if;
1515 if p_search_params.include_closed then log('main','include_closed=true');
1516 else log('main','include_closed=false'); end if;
1517 if p_search_params.ship_set then log('main','ship_set=true');
1518 else log('main','ship_set=false'); end if;
1519 if p_search_params.current_location then
1520 log('main','current_location=true');
1521 else log('main','current_location=false'); end if;
1522 log('main','l_search_method:'||l_search_method);
1523 if l_search_method = 'INVENTORY' then
1524 l_organization_id := get_organization_id;
1525 log('main','l_organization_id:'||l_organization_id);
1526 log('main','calling insert_parts');
1527 insert_parts;
1528 --log('main','calling site_dedicated_spares');
1529 --site_dedicated_spares;
1530 log('main','l_enough:'||l_enough);
1531 if l_enough < 0 then
1532 log('main','calling my_inventory');
1533 my_inventory;
1534 log('main','calling replenishment_source');
1535 replenishment_source;
1536 --log('main','calling ship_set');
1537 --ship_set;
1538 --log('main','calling clean_up');
1539 --clean_up;
1540 --log('main','calling update_shipping_info');
1541 --update_shipping_info;
1542 else
1543 log('main','calling clean_up');
1544 clean_up;
1545 end if;
1546 elsif l_search_method = 'ATP' then
1547 l_organization_id := get_organization_id;
1548 log('main','l_organization_id:'||l_organization_id);
1549 log('main','calling insert_parts');
1550 insert_parts;
1551 log('main','calling site_dedicated_spares');
1552 site_dedicated_spares;
1553 log('main','l_enough:'||l_enough);
1554 if l_enough < 0 then
1555 log('main','calling my_inventory');
1556 my_inventory;
1557 print_capt_contents;
1558 log('main','calling atp');
1559 atp;
1560 print_capt_contents;
1561 log('main','calling ship_set');
1562 ship_set;
1563 print_capt_contents;
1564 log('main','calling clean_up');
1565 clean_up;
1566 print_capt_contents;
1567 log('main','calling update_shipping_info');
1568 update_shipping_info;
1569 print_capt_contents;
1570 else
1571 log('main','calling clean_up');
1572 clean_up;
1573 end if;
1574 else
1575 l_organization_id := get_organization_id;
1576 log('main','l_organization_id:'||l_organization_id);
1577 log('main','calling get_my_location');
1578 get_my_location;
1579 if p_search_params.include_closed then
1580 log('main','p_search_params.include_closed=true');
1581 l_open_or_closed := 'CLOSED';
1582 log('main','l_open_or_closed:'||l_open_or_closed);
1583 end if;
1584 log('main','calling insert_parts');
1585 insert_parts;
1586
1587 log('main','p_search_params.source_organization_id'||
1588 p_search_params.source_organization_id);
1589 if p_search_params.source_organization_id is not null then
1590 log('main','calling specific_warehouse');
1591 specific_warehouse;
1592 else
1593 log('main','calling site_dedicated_spares');
1594 site_dedicated_spares;
1595 log('main','l_enough:'||l_enough);
1596 if l_enough < 0 then
1597 log('main','calling my_inventory');
1598 my_inventory;
1599 log('main','calling technicians');
1600 technicians;
1601 log('main','calling unmanned_warehouses');
1602 unmanned_warehouses;
1603 log('main','calling manned_warehouses');
1604 manned_warehouses;
1605 end if;
1606 end if;
1607 log('main','l_enough:'||l_enough);
1608 if l_enough < 0 then
1609 log('main','calling ship_set');
1610 ship_set;
1611 log('main','calling clean_up');
1612 clean_up;
1613 log('main','calling update_shipping_info');
1614 update_shipping_info;
1615 else
1616 log('main','calling clean_up');
1617 clean_up;
1618 end if;
1619 end if;
1620 x_return_status := l_return_status;
1621 x_msg_data := l_msg_data;
1622 x_msg_count := l_msg_count;
1623 log('main','x_return_status:'||x_return_status);
1624 log('main','x_msg_data:'||x_msg_data);
1625 log('main','x_msg_count:'||x_msg_count);
1626 log('main','End');
1627 END search;
1628
1629 function get_avail_qty (
1630 p_organization_id number,
1631 p_subinventory_code varchar2,
1632 p_inventory_item_id number,
1633 p_revision varchar2,
1634 p_quantity_type varchar2)
1635 return number is
1636 l_api_version constant number := 1.00;
1637 l_serial number;
1638 l_b_serial boolean;
1639 l_revision number;
1640 l_b_revision boolean;
1641 l_qoh number := 0;
1642 l_att number := 0;
1643 l_dummy number := 0;
1644 l_excess number := 0;
1645 d_att number := 0;
1646 v_att number := 0;
1647 d_qoh number := 0;
1648 v_qoh number := 0;
1649 l_r_qoh number := 0;
1650 l_r_att number := 0;
1651
1652 cursor c_revisions is
1653 select revision
1654 from mtl_item_revisions
1655 where organization_id = p_organization_id
1656 and inventory_item_id = p_inventory_item_id
1657 and revision = nvl(p_revision,revision);
1658
1659 cursor c_excess is
1660 select sum(nvl(excess_quantity,0) - nvl(returned_quantity,0))
1661 from csp_excess_lists
1662 where organization_id = p_organization_id
1663 and nvl(subinventory_code,'-1') = nvl(p_subinventory_code,'-1')
1664 and inventory_item_id = p_inventory_item_id;
1665
1666 cursor c_item_attributes is
1667 select serial_number_control_code,
1668 revision_qty_control_code
1669 from mtl_system_items
1670 where organization_id = p_organization_id
1671 and inventory_item_id = p_inventory_item_id;
1672
1673 cursor c_defective_sub is
1674 select secondary_inventory_name
1675 from csp_sec_inventories
1676 where organization_id = p_organization_id
1677 and condition_type = 'B';
1678
1679 -- bug 9724125
1680 l_TRANSACTIONS_ENABLED varchar2(1);
1681
1682 BEGIN
1683 log('get_avail_qty','Begin');
1684 log('get_avail_qty','p_organization_id:'||p_organization_id);
1685 log('get_avail_qty','p_subinventory_code:'||p_subinventory_code);
1686 log('get_avail_qty','p_inventory_item_id:'||p_inventory_item_id);
1687 log('get_avail_qty','p_revision:'||p_revision);
1688 log('get_avail_qty','p_quantity_type:'||p_quantity_type);
1689 -- bug 9724125
1690 -- if the MTL_TRANSACTIONS_ENABLED_FLAG is not Y no need to search for qty
1691 l_TRANSACTIONS_ENABLED := 'N'; -- closed world assumption
1692 select nvl(MTL_TRANSACTIONS_ENABLED_FLAG, 'N')
1693 into l_TRANSACTIONS_ENABLED
1694 from mtl_system_items_b
1695 where inventory_item_id = p_inventory_item_id
1696 and organization_id = p_organization_id;
1697 log('get_avail_qty','l_transactions_enabled:'||l_transactions_enabled);
1698 if l_TRANSACTIONS_ENABLED = 'N' then
1699 log('get_avail_qty','return = 0');
1700 return 0;
1701 end if;
1702
1703 if p_quantity_type = 'EXCESS' then
1704 open c_excess;
1705 fetch c_excess into l_excess;
1706 close c_excess;
1707 log('get_avail_qty','return ='||l_excess);
1708 return l_excess;
1709 else
1710 open c_item_attributes;
1711 fetch c_item_attributes into l_serial, l_revision;
1712 close c_item_attributes;
1713 log('get_avail_qty','l_serial:'||l_serial);
1714 log('get_avail_qty','l_revision:'||l_revision);
1715
1716 if l_serial <> 1 then
1717 l_b_serial := TRUE;
1718 else
1719 l_b_serial := FALSE;
1720 end if;
1721 if l_revision <> 1 then
1722 l_b_revision := TRUE;
1723 else
1724 l_b_revision := FALSE;
1725 end if;
1726 log('get_avail_qty','calling inv_quantity_tree_pub.clear_quantity_cache');
1727 inv_quantity_tree_pub.clear_quantity_cache;
1728 log('get_avail_qty','calling inv_quantity_tree_pub.query_quantities');
1729 if not l_b_revision then
1730 inv_quantity_tree_pub.query_quantities(
1731 p_api_version_number => l_api_version
1732 , p_init_msg_lst => fnd_api.g_false
1733 , x_return_status => l_return_status
1734 , x_msg_count => l_msg_count
1735 , x_msg_data => l_msg_data
1736 , p_organization_id => p_organization_id
1737 , p_inventory_item_id => p_inventory_item_id
1738 , p_tree_mode => inv_quantity_tree_pvt.g_reservation_mode
1739 , p_is_revision_control => l_b_revision
1740 , p_is_lot_control => false
1741 , p_is_serial_control => l_b_serial
1742 , p_onhand_source => inv_quantity_tree_pvt.g_all_subs
1743 , p_demand_source_type_id => null
1744 , p_demand_source_header_id => null
1745 , p_demand_source_line_id => null
1746 , p_demand_source_name => null
1747 , p_lot_expiration_date => null
1748 , p_revision => p_revision
1749 , p_lot_number => null
1750 , p_subinventory_code => p_subinventory_code
1751 , p_locator_id => null
1752 , x_qoh => l_qoh
1753 , x_rqoh => l_dummy
1754 , x_qr => l_dummy
1755 , x_qs => l_dummy
1756 , x_att => l_att
1757 , x_atr => l_dummy);
1758 else
1759 for crr in c_revisions loop
1760 log('get_avail_qty','crr.revision'||crr.revision);
1761 inv_quantity_tree_pub.query_quantities(
1762 p_api_version_number => l_api_version
1763 , p_init_msg_lst => fnd_api.g_false
1764 , x_return_status => l_return_status
1765 , x_msg_count => l_msg_count
1766 , x_msg_data => l_msg_data
1767 , p_organization_id => p_organization_id
1768 , p_inventory_item_id => p_inventory_item_id
1769 , p_tree_mode => inv_quantity_tree_pvt.g_reservation_mode
1770 , p_is_revision_control => l_b_revision
1771 , p_is_lot_control => false
1772 , p_is_serial_control => l_b_serial
1773 , p_onhand_source => inv_quantity_tree_pvt.g_all_subs
1774 , p_demand_source_type_id => null
1775 , p_demand_source_header_id => null
1776 , p_demand_source_line_id => null
1777 , p_demand_source_name => null
1778 , p_lot_expiration_date => null
1779 , p_revision => crr.revision
1780 , p_lot_number => null
1781 , p_subinventory_code => p_subinventory_code
1782 , p_locator_id => null
1783 , x_qoh => l_r_qoh
1784 , x_rqoh => l_dummy
1785 , x_qr => l_dummy
1786 , x_qs => l_dummy
1787 , x_att => l_r_att
1788 , x_atr => l_dummy);
1789 l_qoh := l_qoh + l_r_qoh;
1790 l_att := l_att + l_r_att;
1791 log('get_avail_qty','l_r_qoh'||l_r_qoh);
1792 log('get_avail_qty','l_r_att'||l_r_att);
1793 end loop;
1794 end if;
1795 log('get_avail_qty','l_qoh'||l_qoh);
1796 log('get_avail_qty','l_att'||l_att);
1797 -- Check for defective subinventories in inventory organization
1798 -- we should check defective subinv only if l_att or l_qoh are positive
1799 -- otherwise no need to deduct defective qty and we can avoid unnecessary queries
1800 if (p_subinventory_code is null)
1801 and ((nvl(p_quantity_type,'AVAILABLE') = 'AVAILABLE' and l_att > 0)
1802 or (nvl(p_quantity_type,'AVAILABLE') <> 'AVAILABLE' and l_qoh > 0)) then
1803 for cr in c_defective_sub loop
1804 log('get_avail_qty','in c_defective_sub loop');
1805 log('get_avail_qty','calling inv_quantity_tree_pub.query_quantities');
1806 inv_quantity_tree_pub.query_quantities(
1807 p_api_version_number => l_api_version
1808 , p_init_msg_lst => fnd_api.g_false
1809 , x_return_status => l_return_status
1810 , x_msg_count => l_msg_count
1811 , x_msg_data => l_msg_data
1812 , p_organization_id => p_organization_id
1813 , p_inventory_item_id => p_inventory_item_id
1814 , p_tree_mode => inv_quantity_tree_pvt.g_reservation_mode
1815 , p_is_revision_control => l_b_revision
1816 , p_is_lot_control => false
1817 , p_is_serial_control => l_b_serial
1818 , p_onhand_source => inv_quantity_tree_pvt.g_all_subs
1819 , p_demand_source_type_id => null
1820 , p_demand_source_header_id => null
1821 , p_demand_source_line_id => null
1822 , p_demand_source_name => null
1823 , p_lot_expiration_date => null
1824 , p_revision => p_revision
1825 , p_lot_number => null
1826 , p_subinventory_code => cr.secondary_inventory_name
1827 , p_locator_id => null
1828 , x_qoh => d_qoh
1829 , x_rqoh => l_dummy
1830 , x_qr => l_dummy
1831 , x_qs => l_dummy
1832 , x_att => d_att
1833 , x_atr => l_dummy);
1834 log('get_avail_qty','d_qoh'||d_qoh);
1835 log('get_avail_qty','d_att'||d_att);
1836 -- this is avoid case where l_qoh is 10 but defective qty is -12 which
1837 -- will again give 10-(-12) = 22 wrong qty
1838 if d_att < 0 then
1839 d_att := 0;
1840 end if;
1841 if d_qoh < 0 then
1842 d_qoh := 0;
1843 end if;
1844 log('get_avail_qty','d_qoh'||d_qoh);
1845 log('get_avail_qty','d_att'||d_att);
1846 v_qoh := v_qoh + d_qoh;
1847 v_att := v_att + d_att;
1848 log('get_avail_qty','v_qoh'||v_qoh);
1849 log('get_avail_qty','v_att'||v_att);
1850 end loop;
1851 end if;
1852
1853 if nvl(p_quantity_type,'AVAILABLE') = 'AVAILABLE' then
1854 log('get_avail_qty','return l_att - v_att'||l_att||'-'||v_att);
1855 return l_att - v_att;
1856 else
1857 log('get_avail_qty','return l_qoh - v_qoh'||l_qoh||'-'||v_qoh);
1858 return l_qoh - v_qoh;
1859 end if;
1860 end if;
1861 end get_avail_qty;
1862
1863 function get_arrival_time(
1864 p_cutoff date,
1865 p_cutoff_tz number,
1866 p_lead_time number,
1867 p_lead_time_uom varchar2,
1868 p_intransit_time number,
1869 p_delivery_time date,
1870 p_safety_zone number,
1871 p_location_id number,
1872 p_location_source varchar2,
1873 p_organization_id number,
1874 p_subinventory_code varchar2)
1875 return date is
1876 cursor business_days is
1877 select trunc(sysdate+flvv.lookup_code) bd_arrival_date
1878 FROM jtf_cal_shift_constructs jcsc,
1879 jtf_cal_shifts_b jcsb,
1880 jtf_cal_shift_assign jcsa,
1881 jtf_calendars_b jcb,
1882 fnd_lookup_values_vl flvv,
1883 csp_planning_parameters cpp
1884 WHERE jcsa.calendar_id = jcb.calendar_id
1885 AND jcsb.shift_id = jcsa.shift_id
1886 AND jcsc.shift_id = jcsa.shift_id
1887 and flvv.lookup_type = 'NUMBERS'
1888 AND to_number(flvv.lookup_code) BETWEEN 0 AND 15
1889 and trunc(sysdate+flvv.lookup_code)
1890 between trunc(nvl(jcb.start_date_active,sysdate+flvv.lookup_code))
1891 AND TRUNC(NVL(jcb.end_date_active,sysdate+flvv.lookup_code))
1892 and trunc(sysdate+flvv.lookup_code)
1893 between trunc(nvl(jcsa.shift_start_date,sysdate+flvv.lookup_code))
1894 AND TRUNC(NVL(jcsa.shift_end_date,sysdate+flvv.lookup_code))
1895 and trunc(sysdate+flvv.lookup_code)
1896 between trunc(nvl(jcsb.start_date_active,sysdate+flvv.lookup_code))
1897 AND TRUNC(NVL(jcsb.end_date_active,sysdate+flvv.lookup_code))
1898 and trunc(sysdate+flvv.lookup_code)
1899 between trunc(nvl(jcsc.start_date_active,sysdate+flvv.lookup_code))
1900 AND TRUNC(NVL(jcsc.end_date_active,sysdate+flvv.lookup_code))
1901 and jcsc.unit_of_time_value = to_char(sysdate+flvv.lookup_code,'D')
1902 and not exists
1903 (SELECT 'x'
1904 FROM jtf_cal_exception_assign jcea,
1905 jtf_cal_exceptions_b jceb
1906 where jcea.calendar_id = jcb.calendar_id
1907 and sysdate+flvv.lookup_code
1908 between trunc(jceb.start_date_time) and trunc(jceb.end_date_time+1)
1909 and sysdate+flvv.lookup_code
1910 BETWEEN trunc(jcea.start_date_active) AND trunc(jcea.end_date_active+1)
1911 and jcea.exception_id = jceb.exception_id
1912 )
1913 and jcb.calendar_id = cpp.calendar_id
1914 and cpp.organization_id = p_organization_id
1915 and nvl(cpp.secondary_inventory,-1) = nvl(p_subinventory_code,-1)
1916 order by to_number(flvv.lookup_code);
1917
1918 i number := 0;
1919 l_arrival_date date := sysdate;
1920 l_cutoff date;
1921 l_delivery_time date;
1922 p_hr_uom varchar2(60) := FND_PROFILE.VALUE('CSF_UOM_HOURS');
1923 p_client_timezone_id number := FND_PROFILE.VALUE('CLIENT_TIMEZONE_ID');
1924 p_server_timezone_id number := FND_PROFILE.VALUE('SERVER_TIMEZONE_ID');
1925 begin
1926 log('get_arrival_time',
1927 'p_cutoff:'||to_char(p_cutoff,'dd-mon-yyyy hh24:mi'));
1928 log('get_arrival_time','p_cutoff_tz:'||p_cutoff_tz);
1929 log('get_arrival_time','p_lead_time:'||p_lead_time);
1930 log('get_arrival_time','p_lead_time_uom:'||p_lead_time_uom);
1931 log('get_arrival_time','p_intransit_time:'||p_intransit_time);
1932 log('get_arrival_time',
1933 'p_delivery_time:'||to_char(p_delivery_time,'dd-mon-yyyy hh24:mi'));
1934 log('get_arrival_time','p_safety_zone:'||p_safety_zone);
1935 log('get_arrival_time',
1936 'l_arrival_date:'||to_char(l_arrival_date,'dd-mon-yyyy hh24:mi'));
1937
1938 if p_cutoff is not null then
1939 l_cutoff := hz_timezone_pub.convert_datetime( p_cutoff_tz,
1940 p_server_timezone_id,
1941 p_cutoff);
1942 if (sysdate-trunc(sysdate)) > (l_cutoff-trunc(l_cutoff)) then
1943 l_arrival_date := sysdate+1;
1944 end if;
1945 end if;
1946 if p_lead_time is not null then
1947 log('get_arrival_time','in p_lead_time');
1948 l_arrival_date := l_arrival_date +
1949 inv_convert.inv_um_convert(NULL,6,p_lead_time,
1950 p_lead_time_uom,p_hr_uom,NULL,NULL)*1/24;
1951 log('get_arrival_time',
1952 'l_arrival_date:'||to_char(l_arrival_date,'dd-mon-yyyy hh24:mi'));
1953 elsif p_intransit_time is not null then
1954 l_arrival_date := l_arrival_date + p_intransit_time;
1955 end if;
1956 if p_delivery_time is not null then
1957 log('get_arrival_time','in p_delivery_time');
1958 l_delivery_time := p_delivery_time;
1959 log('get_arrival_time',
1960 'l_delivery_time before:'||to_char(l_delivery_time,'dd-mon-yyyy hh24:mi'));
1961 log('get_arrival_time','p_location_id:'||p_location_id||
1962 'p_location_source:'||p_location_source);
1963 l_delivery_time := hz_timezone_pub.convert_datetime(
1964 nvl(get_ship_to_tz(p_location_id,p_location_source),
1965 p_client_timezone_id),
1966 p_server_timezone_id,
1967 p_delivery_time);
1968 log('get_arrival_time',
1969 'l_delivery_time after:'||to_char(l_delivery_time,'dd-mon-yyyy hh24:mi'));
1970 log('get_arrival_time',
1971 'trunc delivery time:'||to_char(l_delivery_time-trunc(l_delivery_time)));
1972 l_arrival_date := trunc(l_arrival_date) +
1973 (l_delivery_time-trunc(l_delivery_time));
1974 log('get_arrival_time',
1975 'l_arrival_date:'||to_char(l_arrival_date,'dd-mon-yyyy hh24:mi'));
1976 log('get_arrival_time',
1977 'sysdate:'||to_char(sysdate,'dd-mon-yyyy hh24:mi'));
1978 if l_arrival_date < sysdate then
1979 log('get_arrival_time','l_arrival_date < sysdate');
1980 l_arrival_date := l_arrival_date + 1;
1981 end if;
1982 log('get_arrival_time',
1983 'l_arrival_date:'||to_char(l_arrival_date,'dd-mon-yyyy hh24:mi'));
1984 end if;
1985 l_arrival_date := l_arrival_date + nvl(p_safety_zone,0);
1986 log('get_arrival_time',
1987 'l_arrival_date:'||to_char(l_arrival_date,'dd-mon-yyyy hh24:mi'));
1988
1989 i:= 0;
1990 for cr in business_days loop
1991 if i = trunc(l_arrival_date) - trunc(sysdate) then
1992 l_arrival_date := l_arrival_date + (trunc(cr.bd_arrival_date)
1993 - trunc(l_arrival_date));
1994 exit;
1995 end if;
1996 i := i + 1;
1997 end loop;
1998
1999 return l_arrival_date;
2000 end get_arrival_time;
2001
2002 function get_ship_to_tz(
2003 p_location_id number default null,
2004 p_location_source varchar2 default null)
2005 return number is
2006 l_ship_to_tz number;
2007 l_postal_code varchar2(150);
2008 l_city varchar2(150);
2009 l_state varchar2(150);
2010 l_country varchar2(150);
2011
2012 cursor c_ship_to is
2013 select postal_code,city,decode(COUNTRY, 'CA', nvl(PROVINCE, STATE), STATE),country,nvl(timezone_id, -9999)
2014 from hz_locations
2015 where location_id = p_location_id
2016 and p_location_source = 'HZ'
2017 union all
2018 select postal_code,town_or_city,upper(region_1),country,-9999
2019 from hr_locations
2020 where location_id = p_location_id
2021 and p_location_source = 'HR';
2022
2023 cursor c_get_hz_loc is
2024 SELECT hl.postal_code,
2025 hl.city,
2026 decode(hl.COUNTRY, 'CA', nvl(hl.PROVINCE, hl.STATE), hl.STATE),
2027 hl.country,
2028 hl.location_id,
2029 nvl(hl.timezone_id, -9999)
2030 FROM hz_locations hl,
2031 hz_party_sites hps,
2032 HZ_CUST_ACCT_SITES_ALL hcas,
2033 PO_LOCATION_ASSOCIATIONS_ALL pol
2034 WHERE hl.location_id = hps.location_id
2035 AND hcas.party_site_id = hps.party_site_id
2036 AND pol.org_id = hcas.org_id
2037 AND pol.address_id = hcas.cust_acct_site_id
2038 AND pol.location_id = p_location_id
2039 AND rownum = 1;
2040 l_hz_location_id number;
2041
2042 begin
2043
2044 log('get_ship_to_tz', 'p_location_id:'||p_location_id);
2045 log('get_ship_to_tz', 'p_location_source:'||p_location_source);
2046
2047 l_hz_location_id := -9999;
2048 l_ship_to_tz := -9999;
2049
2050 if p_location_source = 'HR' then
2051 -- try to get information from hz_location
2052 open c_get_hz_loc;
2053 fetch c_get_hz_loc into l_postal_code, l_city, l_state, l_country, l_hz_location_id, l_ship_to_tz;
2054 close c_get_hz_loc;
2055
2056 end if;
2057
2058 if l_hz_location_id = -9999 then
2059 open c_ship_to;
2060 fetch c_ship_to into l_postal_code,l_city,l_state,l_country,l_ship_to_tz;
2061 close c_ship_to;
2062 end if;
2063
2064 log('get_ship_to_tz', 'l_ship_to_tz:' || l_ship_to_tz);
2065
2066 if l_ship_to_tz = -9999 or l_ship_to_tz = fnd_api.g_miss_num then
2067 hz_timezone_pub.get_timezone_id (
2068 p_api_version => 1.0,
2069 p_init_msg_list => fnd_api.g_false,
2070 p_postal_code => l_postal_code,
2071 p_city => l_city,
2072 p_state => l_state,
2073 p_country => l_country,
2074 x_timezone_id => l_ship_to_tz,
2075 x_return_status => l_return_status,
2076 x_msg_count => l_msg_count,
2077 x_msg_data => l_msg_data);
2078 end if;
2079
2080 log('get_ship_to_tz', 'l_ship_to_tz:' || l_ship_to_tz);
2081
2082 return l_ship_to_tz;
2083 end;
2084
2085 function get_src_distance (
2086 p_req_header_id number,
2087 p_src_org_id number,
2088 p_src_subinv varchar2
2089 )
2090 return varchar2 is
2091 l_src_geo sdo_geometry;
2092 l_dest_geo sdo_geometry;
2093 l_distance_uom_code varchar2(10);
2094 l_distance_uom varchar2(100);
2095 l_distance varchar2(100) := '-';
2096 l_distance_uom_meaning varchar2(100);
2097 begin
2098 log('get_src_distance', 'p_req_header_id=' || p_req_header_id);
2099 log('get_src_distance', 'p_src_org_id=' || p_src_org_id);
2100 log('get_src_distance', 'p_src_subinv=' || p_src_subinv);
2101
2102 -- first find out source geocode
2103 SELECT GEOMETRY
2104 INTO l_src_geo
2105 FROM HZ_LOCATIONS
2106 WHERE location_id =
2107 ( SELECT DISTINCT hz_location_id
2108 FROM csp_planning_parameters
2109 WHERE organization_id = p_src_org_id
2110 AND NVL(SECONDARY_INVENTORY, 'NULL') = NVL(p_src_subinv, 'NULL')
2111 );
2112
2113 -- now get destination ship_to address's geocode
2114 SELECT hloc.GEOMETRY
2115 INTO l_dest_geo
2116 FROM hz_locations hloc,
2117 hz_party_sites hps
2118 WHERE hloc.location_id = hps.location_id
2119 AND hps.party_site_id =
2120 ( SELECT DISTINCT party_site_id
2121 FROM po_location_associations_all ploc,
2122 hz_cust_acct_sites_all hcsa,
2123 csp_requirement_headers crh
2124 WHERE crh.requirement_header_id = p_req_header_id
2125 AND ploc.location_id = crh.ship_to_location_id
2126 --AND ploc.customer_id = hcsa.cust_account_id
2127 AND ploc.address_id = hcsa.cust_acct_site_id
2128 AND ploc.org_id = hcsa.org_id
2129 );
2130
2131 l_distance_uom_code := fnd_profile.value('CSFW_DEFAULT_DISTANCE_UNIT');
2132 log('get_src_distance','l_distance_uom_code:'||l_distance_uom_code);
2133
2134 if l_distance_uom_code = 'KM' then
2135 l_distance_uom := 'unit=km';
2136 elsif l_distance_uom_code = 'METER' then
2137 l_distance_uom := 'unit=m';
2138 elsif l_distance_uom_code = 'MILE' then
2139 l_distance_uom := 'unit=mile';
2140 end if;
2141 log('get_src_distance','l_distance_uom:'||l_distance_uom);
2142
2143 SELECT ROUND(sdo_geom.sdo_distance(l_src_geo, l_dest_geo, 1000, l_distance_uom),1)
2144 INTO l_distance
2145 FROM dual;
2146 log('get_src_distance','l_distance:'||l_distance);
2147
2148 if l_distance <> '-' then
2149 SELECT meaning
2150 INTO l_distance_uom_meaning
2151 FROM fnd_lookups
2152 WHERE lookup_type='CSFW_DISTANCE_UNIT'
2153 AND lookup_code = l_distance_uom_code;
2154 log('get_src_distance','l_distance_uom_meaning:'||l_distance_uom_meaning);
2155
2156 return l_distance || ' ' || l_distance_uom_meaning;
2157 else
2158 return l_distance;
2159 end if;
2160
2161 end;
2162
2163 function get_cutoff_time(
2164 p_cutoff date,
2165 p_cutoff_tz number
2166 ) return date is
2167 l_cutoff date;
2168 l_cutoff_date date := sysdate;
2169 p_server_timezone_id number := FND_PROFILE.VALUE('SERVER_TIMEZONE_ID');
2170 begin
2171 if p_cutoff is null or p_cutoff_tz is null then
2172 return null;
2173 end if;
2174
2175 log('get_cutoff_time', 'p_cutoff=' || to_char(p_cutoff, 'DD-MON-YYYY HH24:MI:SS'));
2176 log('get_cutoff_time','p_cutoff_tz=' || p_cutoff_tz);
2177 log('get_cutoff_time','p_server_timezone_id=' || p_server_timezone_id);
2178
2179 l_cutoff := hz_timezone_pub.convert_datetime( p_cutoff_tz,
2180 p_server_timezone_id,
2181 p_cutoff);
2182
2183 if (sysdate-trunc(sysdate)) > (l_cutoff-trunc(l_cutoff)) then
2184 l_cutoff_date := sysdate+1;
2185 end if;
2186
2187 l_cutoff_date := trunc(l_cutoff_date) +
2188 (l_cutoff - trunc(l_cutoff));
2189 return l_cutoff_date;
2190 end;
2191 end;