[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT '1'
FROM wsh_delivery_details
WHERE source_header_id = p_header_id
AND source_code = 'PO'
AND (
(p_blanket_ref_id is NULL AND
source_blanket_reference_id IS NULL)
OR
(p_blanket_ref_id IS NOT NULL AND
source_blanket_reference_id = p_blanket_ref_id)
);
WSH_DEBUG_SV.logmsg(l_module_name,'calling bulk insert');
bulk_insert_details (
P_line_rec => P_line_rec,
p_index => l_index,
p_action_prms => l_action_prms,
p_additional_line_info_rec => l_additional_line_info_rec,
X_return_status => l_return_status
);
l_dff_update_flag VARCHAR2(1);
p_update_flag => l_dff_update_flag,
x_return_status => l_return_status);
IF (l_dff_update_flag = 'Y') AND ( WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_DEFAULT_FLEX_LVL) = 1)
THEN --{
IF p_action_prms.caller = 'OM' THEN --{
p_additional_line_info_rec.attribute_category(i) := l_dff_context ;
PROCEDURE bulk_insert_details (
P_line_rec IN OUT NOCOPY OE_WSH_BULK_GRP.line_rec_type,
p_index IN NUMBER,
p_action_prms IN WSH_BULK_TYPES_GRP.action_parameters_rectype,
p_additional_line_info_rec IN additional_line_info_rec_type ,
X_return_status OUT NOCOPY VARCHAR2
)
IS
l_debug_on BOOLEAN;
'.' || 'BULK_INSERT_DETAILS';
l_rec_inserted_count NUMBER;
WSH_DEBUG_SV.log(l_module_name,'number of rows inserting ',
l_line_rec_count);
WSH_DEBUG_SV.log(l_module_name,'requested_quantity of line being inserted is', p_line_rec.requested_quantity(p_index));
INSERT INTO wsh_delivery_details(
source_code,
source_header_id,
source_line_id,
customer_id,
sold_to_contact_id,
inventory_item_id,
item_description,
hazard_class_id,
country_of_origin,
ship_from_location_id,
ship_to_location_id,
ship_to_contact_id,
ship_to_site_use_id,
deliver_to_location_id,
deliver_to_contact_id,
deliver_to_site_use_id,
intmed_ship_to_location_id,
intmed_ship_to_contact_id,
ship_tolerance_above,
ship_tolerance_below,
requested_quantity,
requested_quantity_uom,
subinventory,
revision,
date_requested,
date_scheduled,
master_container_item_id,
detail_container_item_id,
ship_method_code,
carrier_id,
freight_terms_code,
shipment_priority_code,
fob_code,
customer_item_id,
dep_plan_required_flag,
customer_prod_seq,
customer_dock_code,
cust_model_serial_number,
customer_job,
customer_production_line,
net_weight,
weight_uom_code,
volume,
volume_uom_code,
tp_attribute_category,
tp_attribute1,
tp_attribute2,
tp_attribute3,
tp_attribute4,
tp_attribute5,
tp_attribute6,
tp_attribute7,
tp_attribute8,
tp_attribute9,
tp_attribute10,
tp_attribute11,
tp_attribute12,
tp_attribute13,
tp_attribute14,
tp_attribute15,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
created_by,
creation_date,
last_update_date,
last_update_login,
last_updated_by,
program_application_id,
program_id,
program_update_date,
mvt_stat_status,
organization_id,
ship_set_id,
arrival_set_id,
ship_model_complete_flag,
top_model_line_id,
source_header_number,
source_header_type_id,
source_header_type_name,
cust_po_number,
ato_line_id,
src_requested_quantity,
src_requested_quantity_uom,
cancelled_quantity,
tracking_number,
shipping_instructions,
packing_instructions,
project_id,
task_id,
org_id,
oe_interfaced_flag,
inv_interfaced_flag,
source_line_number,
inspection_flag,
released_status,
delivery_detail_id,
container_flag,
gross_weight,
seal_code,
unit_number,
unit_price,
currency_code,
--freight_class_cat_id,
--commodity_code_cat_id,
preferred_grade,
src_requested_quantity2,
src_requested_quantity_uom2,
requested_quantity2,
cancelled_quantity2,
requested_quantity_uom2,
pickable_flag,
original_subinventory,
received_quantity,
received_quantity2,
source_line_set_id,
line_direction,
ignore_for_planning,
earliest_pickup_date,
latest_pickup_date,
earliest_dropoff_date,
latest_dropoff_date,
source_document_type_id,
service_level,
mode_of_transport,
source_blanket_reference_id,
source_blanket_reference_num,
vendor_id,
party_id,
ship_from_site_id,
hold_code,
supplier_item_number,
po_shipment_line_id,
po_shipment_line_number,
shipping_control,
source_line_type_code,
po_revision_number,
release_revision_number,
WV_FROZEN_FLAG,
UNIT_WEIGHT,
UNIT_VOLUME
) VALUES (
p_additional_line_info_rec.source_code,
p_line_rec.header_id(i),
p_line_rec.line_id(i),
p_line_rec.sold_to_org_id(i),
p_line_rec.sold_to_contact_id(i),
p_line_rec.inventory_item_id(i),
p_line_rec.item_description(i),
p_line_rec.hazard_class_id(i),
p_line_rec.country_of_origin(i),
nvl(p_line_rec.ship_from_location_id(i),-1),
p_line_rec.ship_to_location_id(i),
p_line_rec.ship_to_contact_id(i),
p_line_rec.ship_to_org_id(i),
p_line_rec.deliver_to_location_id(i),
p_line_rec.deliver_to_contact_id(i),
p_line_rec.deliver_to_org_id(i),
p_line_rec.intmed_ship_to_location_id(i),
p_line_rec.intermed_ship_to_contact_id(i),
p_line_rec.ship_tolerance_above(i),
p_line_rec.ship_tolerance_below(i),
nvl(p_line_rec.requested_quantity(i),-1),
nvl ( p_line_rec.requested_quantity_uom(i), 'XX'),
p_line_rec.subinventory(i),
p_line_rec.revision(i),
p_line_rec.request_date(i),
p_line_rec.schedule_ship_date(i),
p_additional_line_info_rec.master_container_item_id(i),
p_additional_line_info_rec.detail_container_item_id(i),
p_line_rec.shipping_method_code(i),
p_line_rec.carrier_id(i),
p_line_rec.freight_terms_code(i),
p_line_rec.shipment_priority_code(i),
p_line_rec.fob_point_code(i),
p_line_rec.customer_item_id(i),
p_line_rec.dep_plan_required_flag(i),
p_line_rec.cust_production_seq_num(i),
p_line_rec.customer_dock_code(i),
p_line_rec.cust_model_serial_number(i),
p_line_rec.customer_job(i),
p_line_rec.customer_production_line(i),
p_line_rec.net_weight(i),
p_line_rec.weight_uom_code(i),
p_line_rec.volume(i),
p_line_rec.volume_uom_code(i),
p_line_rec.TP_CONTEXT(i),
p_line_rec.tp_attribute1(i),
p_line_rec.tp_attribute2(i),
p_line_rec.tp_attribute3(i),
p_line_rec.tp_attribute4(i),
p_line_rec.tp_attribute5(i),
p_line_rec.tp_attribute6(i),
p_line_rec.tp_attribute7(i),
p_line_rec.tp_attribute8(i),
p_line_rec.tp_attribute9(i),
p_line_rec.tp_attribute10(i),
p_line_rec.tp_attribute11(i),
p_line_rec.tp_attribute12(i),
p_line_rec.tp_attribute13(i),
p_line_rec.tp_attribute14(i),
p_line_rec.tp_attribute15(i),
p_additional_line_info_rec.attribute_category(i),
p_additional_line_info_rec.attribute1(i),
p_additional_line_info_rec.attribute2(i),
p_additional_line_info_rec.attribute3(i),
p_additional_line_info_rec.attribute4(i),
p_additional_line_info_rec.attribute5(i),
p_additional_line_info_rec.attribute6(i),
p_additional_line_info_rec.attribute7(i),
p_additional_line_info_rec.attribute8(i),
p_additional_line_info_rec.attribute9(i),
p_additional_line_info_rec.attribute10(i),
p_additional_line_info_rec.attribute11(i),
p_additional_line_info_rec.attribute12(i),
p_additional_line_info_rec.attribute13(i),
p_additional_line_info_rec.attribute14(i),
p_additional_line_info_rec.attribute15(i),
l_FND_GLOBAL_USER_ID,
l_SYSDATE,
l_SYSDATE,
l_FND_GLOBAL_USER_ID,
l_FND_GLOBAL_USER_ID,
NULL,
NULL,
NULL,
p_line_rec.mvt_stat_status(i),
p_line_rec.organization_id(i),
p_line_rec.ship_set_id(i),
p_line_rec.arrival_set_id(i),
p_line_rec.ship_model_complete_flag(i),
p_line_rec.top_model_line_id(i),
p_line_rec.source_header_number(i),
p_line_rec.source_header_type_id(i),
p_line_rec.source_header_type_name(i),
p_line_rec.cust_po_number(i),
p_line_rec.ato_line_id(i),
p_line_rec.ordered_quantity(i),
p_line_rec.order_quantity_uom(i),
p_additional_line_info_rec.cancelled_quantity(i),
p_line_rec.tracking_number(i),
p_line_rec.shipping_instructions(i),
p_line_rec.packing_instructions(i),
p_line_rec.project_id(i),
p_line_rec.task_id(i),
p_action_prms.org_id,
decode(p_additional_line_info_rec.source_code,'OE','N','X'),
nvl(p_additional_line_info_rec.inv_interfaced_flag(i),
decode(p_line_rec.pickable_flag(i),'Y','N','X')),
p_line_rec.source_line_number(i),
decode (p_additional_line_info_rec.source_code, 'OKE' ,
nvl (p_line_rec.inspection_flag(i), 'N') , 'N' ),
p_additional_line_info_rec.released_status(i),
wsh_delivery_details_s.nextval,
'N',
p_line_rec.gross_weight(i),
p_line_rec.seal_code(i),
p_line_rec.end_item_unit_number(i),
p_line_rec.unit_list_price(i),
p_line_rec.currency_code(i),
--p_line_rec.freight_class_cat_id(i),
--p_line_rec.commodity_code_cat_id(i),
p_line_rec.preferred_grade(i),
p_line_rec.ordered_quantity2(i),
p_line_rec.ordered_quantity_uom2(i),
p_line_rec.requested_quantity2(i),
p_additional_line_info_rec.cancelled_quantity2(i),
p_line_rec.requested_quantity_uom2(i),
p_line_rec.pickable_flag(i),
p_line_rec.original_subinventory(i),
p_line_rec.received_quantity(i),
p_line_rec.received_quantity2(i),
p_line_rec.line_set_id(i),
decode(p_additional_line_info_rec.source_code, 'OE', 'O', 'OKE', 'O',
'PO',decode(p_line_rec.drop_ship_flag(i),'Y','D','I'),'O'),
NVL(p_additional_line_info_rec.ignore_for_planning(i),'N'),
p_additional_line_info_rec.earliest_pickup_date(i),
p_additional_line_info_rec.latest_pickup_date(i),
p_additional_line_info_rec.earliest_dropoff_date(i),
p_additional_line_info_rec.latest_dropoff_date(i),
p_line_rec.source_document_type_id(i),
p_additional_line_info_rec.service_level(i),
p_additional_line_info_rec.mode_of_transport(i),
-- Added for Inbound Logistics
p_line_rec.source_blanket_reference_id(i),
p_line_rec.source_blanket_reference_num(i),
p_line_rec.vendor_id(i),
p_line_rec.vendor_party_id(i),
p_line_rec.ship_from_site_id(i),
p_line_rec.hold_code(i),
p_line_rec.supplier_item_num(i),
p_line_rec.po_shipment_line_id(i),
p_line_rec.po_shipment_line_number(i),
p_line_rec.shipping_control(i),
p_line_rec.source_line_type_code(i),
p_line_rec.po_revision(i),
p_line_rec.release_revision(i),
'N' , -- WV_FROZEN_FLAG
p_line_rec.mtl_unit_weight(i),
p_line_rec.mtl_unit_volume(i)
)
RETURNING DELIVERY_DETAIL_ID
BULK COLLECT INTO l_del_det_id_tab;
l_rec_inserted_count := l_del_det_id_tab.first;
l_rec_inserted_count := l_del_det_id_tab.COUNT;
WSH_DEBUG_SV.log(l_module_name,'number of rows inserted ',
l_rec_inserted_count);
IF l_rec_inserted_count <> l_line_rec_count THEN
RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
WSH_INTEGRATION.DBI_Update_Detail_Log
(p_delivery_detail_id_tab => l_detail_tab,
p_dml_type => 'INSERT',
x_return_status => l_dbi_rs);
l_rec_inserted_count := p_index;
FOR i in l_first..l_rec_inserted_count LOOP
IF l_debug_on THEN
WSH_DEBUG_SV.log(l_module_name,'p_line_rec.shipping_eligible_flag(i)', p_line_rec.shipping_eligible_flag(i));
DELETE FROM WSH_DELIVERY_DETAILS
WHERE delivery_detail_id = l_invalid_ids(i);
INSERT INTO wsh_delivery_assignments_v(
delivery_assignment_id,
delivery_detail_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
program_application_id,
program_id,
program_update_date
) values (
wsh_delivery_assignments_s.nextval,
l_valid_ids(i),
l_SYSDATE,
L_FND_GLOBAL_USER_ID,
L_SYSDATE,
L_FND_GLOBAL_USER_ID,
L_FND_GLOBAL_USER_ID,
NULL,
NULL,
NULL
);
WSH_DEBUG_SV.log(l_module_name,'After the insert');
wsh_util_core.default_handler('WSH_BULK_PROCESS_PVT.bulk_insert_details');
END bulk_insert_details;
FND_MSG_PUB.Delete_Msg(X_stack_size_start);