[Home] [Help]
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,
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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
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: /*
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;
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;
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;
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
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,
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,
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,
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
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);
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;
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');
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;
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,
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,
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,
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,
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,
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,
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,
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,
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,
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);