[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select capt.organization_id,
capt.subinventory_code,
capt.required_item_id,
sum(nvl(supplied_quantity,0)) supplied_quantity,
crpt.quantity required_quantity
from csp_available_parts_temp capt,csp_required_parts_temp crpt
where crpt.inventory_item_id = capt.required_item_id
and item_type = 'BASE'
group by capt.organization_id,
capt.subinventory_code,
capt.required_item_id,
crpt.quantity;
delete from csp_available_parts_temp
where organization_id = cr.organization_id
and nvl(subinventory_code,'-1') = nvl(cr.subinventory_code,'-1');
log('ship_set','Records deleted:'||sql%rowcount);
SELECT organization_id,
subinventory_code,
source_type_code,
required_item_id,
required_item_rev,
required_quantity,
supplied_item_id,
supplied_item_rev,
supplied_quantity,
supplied_item_type,
shipping_date,
shipping_method,
shipping_cost,
arrival_date,
distance,
open_or_closed,
geometry
from csp_available_parts_temp;
delete from csp_available_parts_temp
where nvl(supplied_quantity,-1) <= 0;
log('clean_up','Records deleted:'||sql%rowcount);
procedure update_shipping_info as
l_shipping_method varchar2(60);
select distinct organization_id, subinventory_code,
decode(l_distance_uom_code,'MILE',distance,'KM',distance/1.609344) distance
from csp_available_parts_temp
where source_type_code not in ('DEDICATED','MYSELF','UNMANNED','TECHNICIAN');
select shipping_method,
shipping_cost,
arrival_date,
decode(distance_uom,'MILE',distance,'KM',distance/1.609344) distance
from csp_shipping_details_v
where organization_id = p_organization_id
and to_location_id = nvl(p_search_params.to_location_id,
p_search_params.to_hz_location_id)
and location_source = decode(p_search_params.to_location_id,null,'HZ','HR')
order by shipping_cost,arrival_date;
log('update_shipping_info','Begin');
log('update_shipping_info','p_search_params.need_by_date:'||
to_char(p_search_params.need_by_date,'ddmmyy hh24:MI'));
log('update_shipping_info','l_distance_uom_code:'||l_distance_uom_code);
insert into csp_available_parts_temp(
organization_id,
subinventory_code,
source_type_code,
required_item_id,
required_item_rev,
required_quantity,
supplied_item_id,
supplied_item_rev,
supplied_quantity,
supplied_item_type,
shipping_date,
shipping_method,
shipping_cost,
arrival_date,
distance,
open_or_closed,
geometry)
select capt.organization_id,
capt.subinventory_code,
capt.source_type_code,
capt.required_item_id,
capt.required_item_rev,
capt.required_quantity,
capt.supplied_item_id,
capt.supplied_item_rev,
capt.supplied_quantity,
capt.supplied_item_type,
capt.shipping_date,
csdv.shipping_method,
csdv.shipping_cost,
csdv.arrival_date,
capt.distance,
capt.open_or_closed,
capt.geometry
from csp_shipping_details_v csdv,
csp_available_parts_temp capt
where csdv.organization_id = capt.organization_id
and to_location_id = nvl(p_search_params.to_location_id,
p_search_params.to_hz_location_id)
and location_source = decode(p_search_params.to_location_id,
null,'HZ','HR')
and nvl(decode(l_distance_uom_code,'MILE',capt.distance,
'KM',capt.distance/1.609344),0) <=
nvl(decode(csdv.distance_uom,'MILE',csdv.distance,
'KM',csdv.distance/1.609344),
nvl(decode(l_distance_uom_code,'MILE',capt.distance,
'KM',capt.distance/1.609344),0))
order by csdv.shipping_cost,csdv.arrival_date;
log('update_shipping_info','Records inserted:'||sql%rowcount);
log('update_shipping_info','looping c_sources');
log('update_shipping_info','cr.organization_id:'||cr.organization_id);
log('update_shipping_info','cr.subinventory_code:'||
cr.subinventory_code);
log('update_shipping_info','p_search_params.to_location_id:'||
p_search_params.to_location_id);
log('update_shipping_info','p_search_params.to_hz_location_id:'||
p_search_params.to_hz_location_id);
log('update_shipping_info','csinfo.distance:'||csinfo.distance);
log('update_shipping_info','cr.distance:'||cr.distance);
log('update_shipping_info','p_search_params.called_from:'||
p_search_params.called_from);
log('update_shipping_info','looping csinfo');
log('update_shipping_info','l_shipping_method:'||l_shipping_method);
log('update_shipping_info','csinfo.arrival_date:'||
csinfo.arrival_date);
log('update_shipping_info','csinfo.shipping_cost:'||
csinfo.shipping_cost);
log('update_shipping_info','update csp_available_parts_temp');
update csp_available_parts_temp
set shipping_method = csinfo.shipping_method,
shipping_cost = csinfo.shipping_cost,
arrival_date = csinfo.arrival_date
where organization_id = cr.organization_id
and nvl(subinventory_code,'-1') = nvl(cr.subinventory_code,'-1');
log('update_shipping_info','Records updated:'||sql%rowcount);
log('update_shipping_info','l_shipping_method:'||l_shipping_method);
log('update_shipping_info','l_shipping_method:'||l_shipping_method);
log('update_shipping_info','delete from csp_available_parts_temp');
delete from csp_available_parts_temp
where organization_id = cr.organization_id
and nvl(subinventory_code,'-1') = nvl(cr.subinventory_code,'-1');
log('update_shipping_info','Records deleted:'||sql%rowcount);
log('update_shipping_info','search method SPARES deleting 1');
delete from csp_available_parts_temp capt
where not exists (select 'x' from mtl_interorg_parameters mip
where mip.from_organization_id = capt.organization_id
and mip.to_organization_id = l_organization_id)
and capt.organization_id <> l_organization_id
and source_type_code not in ('DEDICATED','MYSELF');
log('update_shipping_info','Records deleted:'||sql%rowcount);
log('update_shipping_info','search method SPARES deleting 2');
delete from csp_available_parts_temp
where shipping_method is null
and source_type_code not in ('DEDICATED','MYSELF','UNMANNED','TECHNICIAN');
log('update_shipping_info','Records deleted:'||sql%rowcount);
log('update_shipping_info','End');
select cpp.organization_id,
cpp.secondary_inventory,
hl.geometry
from csp_planning_parameters cpp,
csp_dedicated_sites cds,
jtf_tasks_b jtb,
csp_requirement_headers crh,
hz_party_sites hps,
hz_locations hl
where cds.planning_parameters_id = cpp.planning_parameters_id
and nvl(cpp.stocking_site_excl,'N') = 'N'
and cpp.stocking_site_type = 'DEDICATED'
and jtb.address_id = cds.party_site_id
and jtb.task_id = crh.task_id
and hps.party_site_id = jtb.address_id
and hl.location_id = hps.location_id
and crh.requirement_header_id = p_search_params.requirement_header_id;
select sum(capt.supplied_quantity)-min(crpt.quantity)
from csp_required_parts_temp crpt,
csp_available_parts_temp capt
where crpt.item_type = 'BASE'
and capt.source_type_code = 'DEDICATED'
and capt.required_item_id = crpt.inventory_item_id
order by 1 asc;
log('site_dedicated_spares','Inserting into csp_available_parts_temp');
insert into csp_available_parts_temp(
organization_id,
subinventory_code,
source_type_code,
required_item_id,
required_item_rev,
required_quantity,
supplied_item_id,
supplied_item_rev,
supplied_quantity,
supplied_item_type,
shipping_date,
shipping_method,
shipping_cost,
arrival_date,
distance,
geometry,
open_or_closed)
select
csites.organization_id,
csites.secondary_inventory,
'DEDICATED',
crpt.inventory_item_id,
crpt.revision,
crpt.quantity,
crpt.alternate_item_id,
null supplied_item_rev,
csp_part_search_pvt.get_avail_qty(csites.organization_id,
csites.secondary_inventory,
crpt.alternate_item_id,
crpt.revision,
p_search_params.quantity_type),
crpt.item_type,
null shipping_date,
null shipping_method,
0 shipping_cost,
sysdate arrival_date,
0 distance,
csites.geometry,
'OPEN'
from csp_required_parts_temp crpt;
log('site_dedicated_spares','Records inserted:'||sql%rowcount);
log('my_inventory','insert into csp_available_parts');
insert into csp_available_parts_temp(
organization_id,
subinventory_code,
source_type_code,
required_item_id,
required_item_rev,
required_quantity,
supplied_item_id,
supplied_item_rev,
supplied_quantity,
supplied_item_type,
shipping_date,
shipping_method,
shipping_cost,
arrival_date,
distance,
geometry,
open_or_closed)
select
l_organization_id,
l_subinventory_code,
'MYSELF',
crpt.inventory_item_id,
crpt.revision,
crpt.quantity,
crpt.alternate_item_id,
null supplied_item_rev,
csp_part_search_pvt.get_avail_qty(l_organization_id,
l_subinventory_code,
crpt.alternate_item_id,
crpt.revision,
p_search_params.quantity_type),
crpt.item_type,
null shipping_date,
null shipping_method,
0 shipping_cost,
sysdate arrival_date,
0 distance,
l_my_location,
'OPEN'
from csp_required_parts_temp crpt;
log('my_inventory','inserted records:'||sql%rowcount);
select hl.geometry geometry,
csf_gps_pub.get_location(
null,csi.owner_resource_id,csi.owner_resource_type,sysdate) point,
cpp.organization_id,
cpp.secondary_inventory,
csi.condition_type,
csi.owner_resource_type,
csi.owner_resource_id
from csp_sec_inventories csi,
csp_planning_parameters cpp,
hz_locations hl
where cpp.organization_id = csi.organization_id
and cpp.secondary_inventory = csi.secondary_inventory_name
and cpp.stocking_site_type = 'TECHNICIAN'
and hl.location_id(+) = cpp.hz_location_id
and (csi.organization_id <> l_organization_id
or nvl(csi.secondary_inventory_name,'-1') <> l_subinventory_code)
and csi.condition_type = 'G'
and exists
(select 'x'
from mtl_onhand_quantities moq,
csp_required_parts_temp crpt
where moq.organization_id = csi.organization_id
and moq.subinventory_code = csi.secondary_inventory_name
and moq.inventory_item_id = crpt.alternate_item_id);
select 1 from dual
where l_point <> nvl(p_point,l_point);
log('technicians','insert into CAPT');
insert into csp_available_parts_temp(
organization_id,
subinventory_code,
source_type_code,
required_item_id,
required_item_rev,
required_quantity,
supplied_item_id,
supplied_item_rev,
supplied_quantity,
supplied_item_type,
shipping_date,
shipping_method,
shipping_cost,
arrival_date,
distance,
geometry,
open_or_closed)
select
cr.organization_id,
cr.secondary_inventory,
'TECHNICIAN',
crpt.inventory_item_id,
crpt.revision,
crpt.quantity,
crpt.alternate_item_id,
null supplied_item_rev,
csp_part_search_pvt.get_avail_qty(cr.organization_id,
cr.secondary_inventory,
crpt.alternate_item_id,
crpt.revision,
p_search_params.quantity_type),
crpt.item_type,
null shipping_date,
null shipping_method,
0 shipping_cost,
sysdate arrival_date,
round(sdo_geom.sdo_distance(l_my_location,l_geometry,
1000,l_distance_uom),1) distance,
l_geometry,
case nvl(csoc.object_type,'CLOSED')
when 'CLOSED' then 'CLOSED'
else 'OPEN'
end
from
csp_required_parts_temp crpt,
cac_sr_object_capacity csoc,
csp_sec_inventories csi
where (cr.organization_id <> l_organization_id
or nvl(cr.secondary_inventory,'-1') <> l_subinventory_code)
and csi.organization_id = cr.organization_id
and csi.secondary_inventory_name = cr.secondary_inventory
and cr.condition_type = 'G'
and decode(p_search_params.distance,null,-1,
round(sdo_geom.sdo_distance(
l_my_location,
l_geometry,
1000,
l_distance_uom),1)) <= nvl(p_search_params.distance,-1)
and csoc.object_type(+) = csi.owner_resource_type
and csoc.object_id(+) = csi.owner_resource_id
and sysdate between csoc.start_date_time(+) and csoc.end_date_time(+)
and decode(csoc.object_type,null,'CLOSED','OPEN')
in ('OPEN',l_open_or_closed);
log('technicians','Records inserted:'||sql%rowcount);
log('unmanned_warehouses','insert into CAPT');
insert into csp_available_parts_temp(
organization_id,
subinventory_code,
source_type_code,
required_item_id,
required_item_rev,
required_quantity,
supplied_item_id,
supplied_item_rev,
supplied_quantity,
supplied_item_type,
shipping_date,
shipping_method,
shipping_cost,
arrival_date,
distance,
geometry,
open_or_closed)
select
cpp.organization_id,
cpp.secondary_inventory,
cpp.stocking_site_type,
crpt.inventory_item_id,
crpt.revision,
crpt.quantity,
crpt.alternate_item_id,
null supplied_item_rev,
csp_part_search_pvt.get_avail_qty(cpp.organization_id,
cpp.secondary_inventory,
crpt.alternate_item_id,
crpt.revision,
p_search_params.quantity_type),
crpt.item_type,
null shipping_date,
null shipping_method,
0 shipping_cost,
sysdate arrival_date,
round(sdo_geom.sdo_distance(l_my_location,hl.geometry,
1000,l_distance_uom),1) distance,
hl.geometry,
decode(sign(hz_timezone_pub.convert_datetime(
l_server_timezone_id,
cpp.timezone_id,
sysdate)-nvl(cocv.start_time,sysdate-1))+
sign(nvl(cocv.end_time,sysdate+1)-hz_timezone_pub.convert_datetime(
l_server_timezone_id,
cpp.timezone_id,
sysdate)),2,'OPEN','CLOSED')
from
csp_required_parts_temp crpt,
csp_planning_parameters cpp,
csp_open_closed_v cocv,
hz_locations hl
where cpp.stocking_site_type = 'UNMANNED'
and nvl(cpp.stocking_site_excl,'N') = 'N'
and decode(p_search_params.distance,null,-1,
round(sdo_geom.sdo_distance(
l_my_location,
hl.geometry,
1000,
l_distance_uom),1)) <= nvl(p_search_params.distance,-1)
and hl.location_id (+) = cpp.hz_location_id
and (cpp.organization_id <> l_organization_id
or nvl(cpp.secondary_inventory,'-1') <> l_subinventory_code)
and cocv.calendar_id(+) = cpp.calendar_id
and decode(sign(hz_timezone_pub.convert_datetime(
l_server_timezone_id,
cpp.timezone_id,
sysdate)-nvl(cocv.start_time,sysdate-1))+
sign(nvl(cocv.end_time,sysdate+1)-hz_timezone_pub.convert_datetime(
l_server_timezone_id,
cpp.timezone_id,
sysdate)),2,'OPEN','CLOSED')
in ('OPEN',l_open_or_closed);
log('unmanned_warehouses','Records inserted:'||sql%rowcount);
select distinct organization_id
from csp_shipping_details_v
where ((to_location_id = p_search_params.to_location_id
and location_source = 'HR')
or (to_location_id = p_search_params.to_hz_location_id
and location_source = 'HZ'));
log('manned_warehouses','insert into CAPT');
insert into csp_available_parts_temp(
organization_id,
subinventory_code,
source_type_code,
required_item_id,
required_item_rev,
required_quantity,
supplied_item_id,
supplied_item_rev,
supplied_quantity,
supplied_item_type,
shipping_date,
shipping_method,
shipping_cost,
arrival_date,
distance,
geometry,
open_or_closed)
select
cpp.organization_id,
cpp.secondary_inventory,
cpp.stocking_site_type,
crpt.inventory_item_id,
crpt.revision,
crpt.quantity,
crpt.alternate_item_id,
null supplied_item_rev,
csp_part_search_pvt.get_avail_qty(cpp.organization_id,
cpp.secondary_inventory,
crpt.alternate_item_id,
crpt.revision,
p_search_params.quantity_type),
crpt.item_type,
null shipping_date,
null shipping_method,
null shipping_cost,
null arrival_date,
round(sdo_geom.sdo_distance(l_my_location,hl.geometry,
1000,l_distance_uom),1),
hl.geometry,
decode(sign(hz_timezone_pub.convert_datetime(
l_server_timezone_id,
cpp.timezone_id,
sysdate)-nvl(cocv.start_time,sysdate-1))+
sign(nvl(cocv.end_time,sysdate+1)-hz_timezone_pub.convert_datetime(
l_server_timezone_id,
cpp.timezone_id,
sysdate)),2,'OPEN','CLOSED')
from
csp_required_parts_temp crpt,
csp_planning_parameters cpp,
csp_open_closed_v cocv,
hz_locations hl
where cpp.stocking_site_type = 'MANNED'
and cpp.organization_id = cr.organization_id
and nvl(cpp.stocking_site_excl,'N') = 'N'
and hl.location_id (+) = cpp.hz_location_id
and decode(p_search_params.distance,null,-1,
round(sdo_geom.sdo_distance(
l_my_location,
hl.geometry,
1000,
l_distance_uom),1)) <= nvl(p_search_params.distance,-1)
and (cpp.organization_id <> nvl(l_organization_id, -999)
or nvl(cpp.secondary_inventory,'-1') <> l_subinventory_code)
and cocv.calendar_id(+) = cpp.calendar_id
and exists (select 'x'
from mtl_onhand_quantities moq
where moq.organization_id = cpp.organization_id
and moq.subinventory_code = nvl(cpp.secondary_inventory,
moq.subinventory_code)
and moq.inventory_item_id = crpt.alternate_item_id)
and decode(sign(hz_timezone_pub.convert_datetime(
l_server_timezone_id,
cpp.timezone_id,
sysdate)-nvl(cocv.start_time,sysdate-1))+
sign(nvl(cocv.end_time,sysdate+1)-hz_timezone_pub.convert_datetime(
l_server_timezone_id,
cpp.timezone_id,
sysdate)),2,'OPEN','CLOSED')
in ('OPEN',l_open_or_closed);
log('manned_warehouses','Inserted records:'||sql%rowcount);
insert into csp_available_parts_temp(
organization_id,
subinventory_code,
source_type_code,
required_item_id,
required_item_rev,
required_quantity,
supplied_item_id,
supplied_item_rev,
supplied_quantity,
supplied_item_type,
shipping_date,
shipping_method,
shipping_cost,
arrival_date,
distance,
geometry,
open_or_closed)
select
cpp.organization_id,
cpp.secondary_inventory,
cpp.stocking_site_type,
crpt.inventory_item_id,
crpt.revision,
crpt.quantity,
crpt.alternate_item_id,
null supplied_item_rev,
csp_part_search_pvt.get_avail_qty(cpp.organization_id,
cpp.secondary_inventory,
crpt.alternate_item_id,
crpt.revision,
p_search_params.quantity_type),
crpt.item_type,
null shipping_date,
null shipping_method,
null shipping_cost,
null arrival_date,
round(sdo_geom.sdo_distance(l_my_location,hl.geometry,
1000,l_distance_uom),1),
hl.geometry,
decode(sign(hz_timezone_pub.convert_datetime(
l_server_timezone_id,
cpp.timezone_id,
sysdate)-nvl(cocv.start_time,sysdate-1))+
sign(nvl(cocv.end_time,sysdate+1)-hz_timezone_pub.convert_datetime(
l_server_timezone_id,
cpp.timezone_id,
sysdate)),2,'OPEN','CLOSED')
from
csp_required_parts_temp crpt,
csp_planning_parameters cpp,
csp_open_closed_v cocv,
hz_locations hl
where cpp.organization_id = p_search_params.source_organization_id
and nvl(cpp.stocking_site_excl,'N') = 'N'
and hl.location_id (+) = cpp.hz_location_id
and decode(p_search_params.distance,null,-1,
round(sdo_geom.sdo_distance(
l_my_location,
hl.geometry,
1000,
l_distance_uom),1)) <= nvl(p_search_params.distance,-1)
and nvl(cpp.secondary_inventory,'-1') =
nvl(p_search_params.source_subinventory,'-1')
and cocv.calendar_id(+) = cpp.calendar_id
and decode(sign(hz_timezone_pub.convert_datetime(
l_server_timezone_id,
cpp.timezone_id,
sysdate)-nvl(cocv.start_time,sysdate-1))+
sign(nvl(cocv.end_time,sysdate+1)-hz_timezone_pub.convert_datetime(
l_server_timezone_id,
cpp.timezone_id,
sysdate)),2,'OPEN','CLOSED')
in ('OPEN',l_open_or_closed);
log('specific_warehouse','Records inserted:'||sql%rowcount);
select crpt.inventory_item_id,
crpt.alternate_item_id,
crpt.item_type item_type,
crpt.revision,
crpt.quantity
from csp_required_parts_temp crpt;
log('replenishment_source','inserting into CAPT');
insert into csp_available_parts_temp(
organization_id,
subinventory_code,
source_type_code,
required_item_id,
required_item_rev,
required_quantity,
supplied_item_id,
supplied_item_rev,
supplied_quantity,
supplied_item_type,
shipping_date,
shipping_method,
shipping_cost,
arrival_date,
distance,
geometry,
open_or_closed)
select
l_repl_org,
l_repl_sub,
'MANNED',
cr.inventory_item_id,
cr.revision,
cr.quantity,
cr.alternate_item_id,
null supplied_item_rev,
csp_part_search_pvt.get_avail_qty(l_repl_org,
l_repl_sub,
cr.alternate_item_id,
cr.revision,
p_search_params.quantity_type),
cr.item_type,
null shipping_date,
null shipping_method,
null shipping_cost,
null arrival_date,
null distance,
null geometry,
'OPEN'
from dual;
log('replenishment_source','Inserted records:'||sql%rowcount);
select crl.requirement_line_id,
crl.inventory_item_id,
crl.revision,
crl.uom_code,
crl.required_quantity,
crl.ship_complete_flag,
crh.destination_organization_id,
crh.destination_subinventory,
crh.need_by_date,
crh.timezone_id,
crh.ship_to_location_id
from csp_requirement_lines crl,
csp_requirement_headers crh
where crh.requirement_header_id = p_search_params.requirement_header_id
and crl.requirement_header_id = crh.requirement_header_id;
log('atp','insert into CAPT');
insert into csp_available_parts_temp(
organization_id,
subinventory_code,
source_type_code,
required_item_id,
required_item_rev,
required_quantity,
supplied_item_id,
supplied_item_rev,
supplied_quantity,
supplied_item_type,
shipping_date,
shipping_method,
shipping_cost,
arrival_date,
distance,
open_or_closed,
geometry)
select
l_avail_list_tbl(i).source_org_id,
l_avail_list_tbl(i).sub_inventory_code,
'MANNED',
l_avail_list_tbl(i).item_id,
l_avail_list_tbl(i).revision,
l_avail_list_tbl(i).ordered_quantity, -- replaced required_quantity with ordered_quantity
l_avail_list_tbl(i).item_id,
l_avail_list_tbl(i).revision,
l_avail_list_tbl(i).available_quantity,
decode(l_avail_list_tbl(i).item_type, 2, 'SUBSTITUTE', 8, 'SUPERSEDED', 'BASE'), -- used decode as item_type here is a number
null,
l_avail_list_tbl(i).shipping_methode,
null,
l_avail_list_tbl(i).arraival_date,
null,
'OPEN',
null
from dual;
log('atp','Inserted records:'||sql%rowcount);
select hl.geometry
from hz_locations hl,
csp_planning_parameters cpp
where cpp.organization_id = l_organization_id
and cpp.secondary_inventory = l_subinventory_code
and hl.location_id = cpp.hz_location_id;
select geometry
from hz_locations
where location_id = l_to_hz_location_id;
select hl.address1,hl.address2,hl.address3,hl.address4,
hl.city,hl.postal_code,hl.state,ftt.territory_short_name,
hl.province,hl.county
from hz_locations hl, fnd_territories_tl ftt
where hl.location_id = l_to_hz_location_id
and ftt.territory_code = hl.country
and ftt.language = 'US';
select hps.location_id
from hz_cust_site_uses_all hcsua,
po_location_associations_all plaa,
hz_cust_acct_sites_all hcasa,
hz_party_sites hps
where plaa.location_id = p_search_params.to_location_id
and hcsua.site_use_id = plaa.site_use_id
and hcsua.site_use_code = 'SHIP_TO'
and hcsua.status = 'A'
and hcasa.cust_acct_site_id = hcsua.cust_acct_site_id
and hps.party_site_id = hcasa.party_site_id;
select organization_id, subinventory_code
from csp_inv_loc_assignments
where resource_type = p_search_params.resource_type
and resource_id = p_search_params.resource_id
and default_code = 'IN';
procedure insert_parts as
i number :=0;
select inventory_item_id,revision,required_quantity, requirement_line_id
from csp_requirement_lines crl
where requirement_header_id = p_search_params.requirement_header_id;
select source_type, source_id
from csp_req_line_details
where requirement_line_id = p_requirement_line_id;
select reservation_quantity
from mtl_reservations
where reservation_id = p_reservation_id;
select quantity
from mtl_txn_request_lines
where line_id = p_mo_line_id;
select ordered_quantity
from oe_order_lines_all
where line_id = p_io_line_id;
select quantity
from po_lines_all
where po_line_id = p_po_line_id;
select inventory_item_id, revision, quantity
from csp_required_parts_temp;
l_insert_rec_c number;
select master_organization_id
from mtl_parameters
where organization_id = l_organization_id;
SELECT related_item_id
FROM mtl_related_items
WHERE organization_id = v_organization_id
AND TRUNC(sysdate) BETWEEN TRUNC(NVL(start_date,sysdate)) AND TRUNC(NVL(end_date,sysdate))
AND relationship_type_id = v_rel_type
start with inventory_item_id = v_inventory_item_id
CONNECT BY nocycle prior related_item_id = inventory_item_id
UNION
SELECT inventory_item_id AS related_item_id
FROM mtl_related_items
WHERE organization_id = v_organization_id
AND relationship_type_id = v_rel_type
AND TRUNC(sysdate) BETWEEN TRUNC(NVL(start_date,sysdate)) AND TRUNC(NVL(end_date,sysdate))
START WITH related_item_id = v_inventory_item_id
and reciprocal_flag = 'Y'
CONNECT BY nocycle prior inventory_item_id||prior reciprocal_flag = related_item_id||reciprocal_flag;
log('insert_parts','Begin');
log('insert_parts',
'p_search_params.requirement_header_id:'||
p_search_params.requirement_header_id);
log('insert_parts','in c_requirement loop');
log('insert_parts','in c_req_line_details loop');
log('insert_parts','cr.requirement_line_id:'||cr.requirement_line_id);
log('insert_parts','RES l_quantity:'||l_quantity);
log('insert_parts','RES l_total_quantity:'||l_total_quantity);
log('insert_parts','MO l_quantity:'||l_quantity);
log('insert_parts','MO l_total_quantity:'||l_total_quantity);
log('insert_parts','IO l_quantity:'||l_quantity);
log('insert_parts','IO l_total_quantity:'||l_total_quantity);
log('insert_parts','POREQ l_quantity:'||l_quantity);
log('insert_parts','POREQ l_total_quantity:'||l_total_quantity);
log('insert_parts','cr.required_quantity:'||cr.required_quantity);
log('insert_parts',
'l_required_parts.inventory_item_id:'||
l_required_parts(i).inventory_item_id);
log('insert_parts',
'l_required_parts.revision:'||l_required_parts(i).revision);
log('insert_parts',
'l_required_parts.quantity:'||l_required_parts(i).quantity);
log('insert_parts','p_search_params.requirement_header_id is null');
log('insert_parts','In l_required_parts.count loop i:'||i);
log('insert_parts','Insert into csp_required_parts_temp');
insert into csp_required_parts_temp(inventory_item_id,
revision,
alternate_item_id,
quantity,item_type)
values (l_required_parts(i).inventory_item_id,
l_required_parts(i).revision,
l_required_parts(i).inventory_item_id,
l_required_parts(i).quantity,
'BASE');
log('insert_parts','Inserted records:'||sql%rowcount);
log('insert_parts','insert supersessions into CRPT');
l_insert_rec_c := 0;
insert into csp_required_parts_temp(inventory_item_id,
revision,
alternate_item_id,
quantity,
item_type)
values (r_req_items.inventory_item_id,
r_req_items.revision,
r_rel_item.related_item_id,
r_req_items.quantity,
'SUPERSEDED');
l_insert_rec_c := l_insert_rec_c + 1;
log('insert_parts','Inserted records:' || l_insert_rec_c);
log('insert_parts','insert substitutes into CRPT');
insert into csp_required_parts_temp(inventory_item_id,
revision,
alternate_item_id,
quantity,item_type)
select crpt.inventory_item_id,
crpt.revision,
mriv.related_item_id,
crpt.quantity,
'SUBSTITUTE'
from mtl_related_items_view mriv,
mtl_parameters mp,
csp_required_parts_temp crpt
where mp.organization_id = l_organization_id
and mriv.organization_id = mp.master_organization_id
and mriv.inventory_item_id = crpt.inventory_item_id
and mriv.relationship_type_id = 2
and crpt.item_type = 'BASE'
and trunc(sysdate) between trunc(nvl(mriv.start_date,sysdate))
and trunc(nvl(mriv.end_date,sysdate))
and not exists(
select 'x'
from csp_required_parts_temp
where alternate_item_id = mriv.related_item_id);
log('insert_parts','Inserted records:'||sql%rowcount);
l_insert_rec_c := 0;
insert into csp_required_parts_temp(inventory_item_id,
revision,
alternate_item_id,
quantity,
item_type)
values (r_req_items.inventory_item_id,
r_req_items.revision,
r_rel_item.related_item_id,
r_req_items.quantity,
'SUBSTITUTE');
l_insert_rec_c := l_insert_rec_c + 1;
log('insert_parts','Inserted records:'||l_insert_rec_c);
log('insert_parts','End');
delete from csp_required_parts_temp;
delete from csp_available_parts_temp;
log('main','calling insert_parts');
insert_parts;
log('main','calling insert_parts');
insert_parts;
log('main','calling update_shipping_info');
update_shipping_info;
log('main','calling insert_parts');
insert_parts;
log('main','calling update_shipping_info');
update_shipping_info;
select revision
from mtl_item_revisions
where organization_id = p_organization_id
and inventory_item_id = p_inventory_item_id
and revision = nvl(p_revision,revision);
select sum(nvl(excess_quantity,0) - nvl(returned_quantity,0))
from csp_excess_lists
where organization_id = p_organization_id
and nvl(subinventory_code,'-1') = nvl(p_subinventory_code,'-1')
and inventory_item_id = p_inventory_item_id;
select serial_number_control_code,
revision_qty_control_code
from mtl_system_items
where organization_id = p_organization_id
and inventory_item_id = p_inventory_item_id;
select secondary_inventory_name
from csp_sec_inventories
where organization_id = p_organization_id
and condition_type = 'B';
select nvl(MTL_TRANSACTIONS_ENABLED_FLAG, 'N')
into l_TRANSACTIONS_ENABLED
from mtl_system_items_b
where inventory_item_id = p_inventory_item_id
and organization_id = p_organization_id;
select trunc(sysdate+flvv.lookup_code) bd_arrival_date
FROM jtf_cal_shift_constructs jcsc,
jtf_cal_shifts_b jcsb,
jtf_cal_shift_assign jcsa,
jtf_calendars_b jcb,
fnd_lookup_values_vl flvv,
csp_planning_parameters cpp
WHERE jcsa.calendar_id = jcb.calendar_id
AND jcsb.shift_id = jcsa.shift_id
AND jcsc.shift_id = jcsa.shift_id
and flvv.lookup_type = 'NUMBERS'
AND to_number(flvv.lookup_code) BETWEEN 0 AND 15
and trunc(sysdate+flvv.lookup_code)
between trunc(nvl(jcb.start_date_active,sysdate+flvv.lookup_code))
AND TRUNC(NVL(jcb.end_date_active,sysdate+flvv.lookup_code))
and trunc(sysdate+flvv.lookup_code)
between trunc(nvl(jcsa.shift_start_date,sysdate+flvv.lookup_code))
AND TRUNC(NVL(jcsa.shift_end_date,sysdate+flvv.lookup_code))
and trunc(sysdate+flvv.lookup_code)
between trunc(nvl(jcsb.start_date_active,sysdate+flvv.lookup_code))
AND TRUNC(NVL(jcsb.end_date_active,sysdate+flvv.lookup_code))
and trunc(sysdate+flvv.lookup_code)
between trunc(nvl(jcsc.start_date_active,sysdate+flvv.lookup_code))
AND TRUNC(NVL(jcsc.end_date_active,sysdate+flvv.lookup_code))
and jcsc.unit_of_time_value = to_char(sysdate+flvv.lookup_code,'D')
and not exists
(SELECT 'x'
FROM jtf_cal_exception_assign jcea,
jtf_cal_exceptions_b jceb
where jcea.calendar_id = jcb.calendar_id
and sysdate+flvv.lookup_code
between trunc(jceb.start_date_time) and trunc(jceb.end_date_time+1)
and sysdate+flvv.lookup_code
BETWEEN trunc(jcea.start_date_active) AND trunc(jcea.end_date_active+1)
and jcea.exception_id = jceb.exception_id
)
and jcb.calendar_id = cpp.calendar_id
and cpp.organization_id = p_organization_id
and nvl(cpp.secondary_inventory,-1) = nvl(p_subinventory_code,-1)
order by to_number(flvv.lookup_code);
select postal_code,city,decode(COUNTRY, 'CA', nvl(PROVINCE, STATE), STATE),country,nvl(timezone_id, -9999)
from hz_locations
where location_id = p_location_id
and p_location_source = 'HZ'
union all
select postal_code,town_or_city,upper(region_1),country,-9999
from hr_locations
where location_id = p_location_id
and p_location_source = 'HR';
SELECT hl.postal_code,
hl.city,
decode(hl.COUNTRY, 'CA', nvl(hl.PROVINCE, hl.STATE), hl.STATE),
hl.country,
hl.location_id,
nvl(hl.timezone_id, -9999)
FROM hz_locations hl,
hz_party_sites hps,
HZ_CUST_ACCT_SITES_ALL hcas,
PO_LOCATION_ASSOCIATIONS_ALL pol
WHERE hl.location_id = hps.location_id
AND hcas.party_site_id = hps.party_site_id
AND pol.org_id = hcas.org_id
AND pol.address_id = hcas.cust_acct_site_id
AND pol.location_id = p_location_id
AND rownum = 1;
SELECT GEOMETRY
INTO l_src_geo
FROM HZ_LOCATIONS
WHERE location_id =
( SELECT DISTINCT hz_location_id
FROM csp_planning_parameters
WHERE organization_id = p_src_org_id
AND NVL(SECONDARY_INVENTORY, 'NULL') = NVL(p_src_subinv, 'NULL')
);
SELECT hloc.GEOMETRY
INTO l_dest_geo
FROM hz_locations hloc,
hz_party_sites hps
WHERE hloc.location_id = hps.location_id
AND hps.party_site_id =
( SELECT DISTINCT party_site_id
FROM po_location_associations_all ploc,
hz_cust_acct_sites_all hcsa,
csp_requirement_headers crh
WHERE crh.requirement_header_id = p_req_header_id
AND ploc.location_id = crh.ship_to_location_id
--AND ploc.customer_id = hcsa.cust_account_id
AND ploc.address_id = hcsa.cust_acct_site_id
AND ploc.org_id = hcsa.org_id
);
SELECT ROUND(sdo_geom.sdo_distance(l_src_geo, l_dest_geo, 1000, l_distance_uom),1)
INTO l_distance
FROM dual;
SELECT meaning
INTO l_distance_uom_meaning
FROM fnd_lookups
WHERE lookup_type='CSFW_DISTANCE_UNIT'
AND lookup_code = l_distance_uom_code;