[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
SHIP_CONFIRM_RULE_ID,
AUTOPACK_LEVEL,
TASK_PLANNING_FLAG,
EXPORT_SCREENING_FLAG,
APPENDING_LIMIT,
IGNORE_INBOUND_TRIP,
PACK_SLIP_REQUIRED_FLAG,
PICK_SEQUENCE_RULE_ID,
PICK_GROUPING_RULE_ID,
PRINT_PICK_SLIP_MODE,
PICK_RELEASE_REPORT_SET_ID,
AUTOCREATE_DEL_ORDERS_FLAG,
DEFAULT_STAGE_SUBINVENTORY,
DEFAULT_STAGE_LOCATOR_ID,
AUTODETAIL_PR_FLAG,
ENFORCE_PACKING_FLAG,
GROUP_BY_CUSTOMER_FLAG,
GROUP_BY_FOB_FLAG,
GROUP_BY_FREIGHT_TERMS_FLAG,
GROUP_BY_INTMED_SHIP_TO_FLAG,
GROUP_BY_SHIP_METHOD_FLAG,
GROUP_BY_CARRIER_FLAG,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
REQUEST_ID,
PICK_SLIP_LINES,
AUTOCREATE_DELIVERIES_FLAG,
FREIGHT_CLASS_CAT_SET_ID,
COMMODITY_CODE_CAT_SET_ID,
ENFORCE_SHIP_SET_AND_SMC,
AUTO_SEND_DOC_FLAG,
ITM_ADDITIONAL_COUNTRY_CODE,
AUTO_SELECT_CARRIER,
GOODS_DISPATCHED_ACCOUNT,
LOCATION_ID,
ORGANIZATION_ID,
WEIGHT_UOM_CLASS,
VOLUME_UOM_CLASS,
WEIGHT_VOLUME_FLAG,
INV_CONTROLS_CONTAINER_FLAG,
PERCENT_FILL_BASIS_FLAG,
TRIP_REPORT_SET_ID,
DELIVERY_REPORT_SET_ID,
AUTOCREATE_DEL_ORDERS_PR_FLAG,
FPA_POSITIVE_TOL_AMT,
FPA_NEGATIVE_TOL_AMT,
FPA_POSITIVE_TOL_PERCENTAGE,
FPA_NEGATIVE_TOL_PERCENTAGE,
FPA_DEFAULT_FREIGHT_ACCOUNT,
AUTO_APPLY_ROUTING_RULES,
AUTO_CALC_FGT_RATE_CR_DEL,
AUTO_CALC_FGT_RATE_APPEND_DEL,
AUTO_CALC_FGT_RATE_SC_DEL,
RAISE_BUSINESS_EVENTS,
ENABLE_TRACKING_WFS,
ENABLE_SC_WF,
null PROCESS_FLAG,
-- HW OPMCON. get check_on_hand
CHECK_ON_HAND,
--OTM R12
MAX_NET_WEIGHT,
MAX_GROSS_WEIGHT,
nvl(OTM_ENABLED,'N'), --OTM R12 Org-Specific
nvl(DYNAMIC_REPLENISHMENT_FLAG,'N'), --bug# 6689448 (replenishment project)
nvl(DOCK_APPT_SCHEDULING_FLAG,'N'), --bug 6700792: OTM Dock Door Sched Proj
nvl(RETAIN_NONSTAGED_DET_FLAG,'N') --Bug 7131800
FROM WSH_SHIPPING_PARAMETERS
WHERE ORGANIZATION_ID = v_organization_id;
WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.LAST_UPDATE_DATE',X_PARAM_INFO.LAST_UPDATE_DATE);
WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.LAST_UPDATED_BY',X_PARAM_INFO.LAST_UPDATED_BY);
WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.LAST_UPDATE_LOGIN',X_PARAM_INFO.LAST_UPDATE_LOGIN);
WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.PROGRAM_UPDATE_DATE',X_PARAM_INFO.PROGRAM_UPDATE_DATE);
WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.AUTO_SELECT_CARRIER',X_PARAM_INFO.AUTO_SELECT_CARRIER);
WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.LAST_UPDATE_DATE',X_PARAM_INFO.LAST_UPDATE_DATE);
WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.LAST_UPDATED_BY',X_PARAM_INFO.LAST_UPDATED_BY);
WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.LAST_UPDATE_LOGIN',X_PARAM_INFO.LAST_UPDATE_LOGIN);
WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.PROGRAM_UPDATE_DATE',X_PARAM_INFO.PROGRAM_UPDATE_DATE);
WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.AUTO_SELECT_CARRIER',X_PARAM_INFO.AUTO_SELECT_CARRIER);
SELECT
AUTO_RATE_TP_REL_TRIPS,
TL_PRIN_COST_ALLOC_BASIS,
TL_DISTANCE_ALLOC_BASIS,
TL_STOP_COST_ALLOC_BASIS,
AUTOFIRM_LOAD_TENDERED_TRIPS,
CONSOLIDATE_BO_LINES,
GU_WEIGHT_CLASS,
GU_WEIGHT_UOM,
GU_VOLUME_UOM,
GU_VOLUME_CLASS,
GU_DISTANCE_CLASS,
GU_DISTANCE_UOM,
GU_DIMENSION_CLASS,
GU_DIMENSION_UOM,
GU_CURRENCY_COUNTRY,
GU_CURRENCY_UOM,
GU_TIME_CLASS,
GU_TIME_UOM,
DEF_MILE_CALC_ON_CUST_FAC,
DEF_MILE_CALC_ON_SUPP_FAC,
DEF_MILE_CALC_ON_ORG_FAC,
DEF_MILE_CALC_ON_CARR_FAC,
TL_HWAY_DIS_EMP_CONSTANT,
AVG_HWAY_SPEED,
DISTANCE_UOM,
TIME_UOM,
UOM_FOR_NUM_OF_UNITS,
PALLET_ITEM_TYPE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
DEFER_INTERFACE,
ENFORCE_SHIP_METHOD,
ALLOW_FUTURE_SHIP_DATE,
RATE_IB_DELS_FGT_TERM,
SKIP_RATE_OB_DELS_FGT_TERM,
DEL_DATE_CALC_METHOD,
RATE_DS_DELS_FGT_TERM_ID,
RAISE_BUSINESS_EVENTS,
ENABLE_TRACKING_WFS,
ENABLE_SC_WF,
EXPAND_CARRIER_RANKINGS,
DEFER_PLAN_SHIPMENT_INTERFACE --bug 7491598
FROM WSH_GLOBAL_PARAMETERS;
WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.LAST_UPDATE_DATE',X_PARAM_INFO.LAST_UPDATE_DATE);
WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.LAST_UPDATED_BY',X_PARAM_INFO.LAST_UPDATED_BY);
WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.LAST_UPDATE_LOGIN',X_PARAM_INFO.LAST_UPDATE_LOGIN);
WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.LAST_UPDATE_DATE',X_PARAM_INFO.LAST_UPDATE_DATE);
WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.LAST_UPDATED_BY',X_PARAM_INFO.LAST_UPDATED_BY);
WSH_DEBUG_SV.log(l_module_name,'X_PARAM_INFO.LAST_UPDATE_LOGIN',X_PARAM_INFO.LAST_UPDATE_LOGIN);
select class_code
, param_name
, decode(class_code
,'ROUTING_RULES',nvl(param_value, 'N'),param_value) param_value
, param_data_type
from wsh_shipping_parameter_values
where organization_id = p_organization_id ;
l_selective boolean;
l_selective := TRUE;
l_selective := FALSE;
x_param_value_info.class_code.delete;
x_param_value_info.param_name.delete;
x_param_value_info.param_data_type.delete;
x_param_value_info.param_value_num.delete;
x_param_value_info.param_value_chr.delete;
x_param_value_info.param_value_date.delete;
If g_parameter_values(i).organization_id = temp_param_value_info.organization_id and l_selective then
l_found := TRUE;
Elsif g_parameter_values(i).organization_id = x_param_value_info.organization_id and not l_selective then
x_param_value_info := g_parameter_values(i);
If l_selective then
l_row_count := 0;
Procedure insert_parameter_values (p_ins_ship_par_val_rec parameter_value_rec_typ ) is
l_user_id number:= fnd_global.user_id;
insert into wsh_shipping_parameter_values(
organization_id,
class_code,
param_name,
param_value,
param_data_type,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
values (p_ins_ship_par_val_rec.organization_id
,p_ins_ship_par_val_rec.class_code(i)
,p_ins_ship_par_val_rec.param_name(i)
,p_ins_ship_par_val_rec.param_value_chr(i)
,p_ins_ship_par_val_rec.param_data_type(i)
,sysdate
,l_user_id
,sysdate
,l_user_id
,l_login_id);
End insert_parameter_values;
Insert into wsh_shipping_parameters (
goods_dispatched_account,
location_id,
organization_id,
weight_uom_class,
volume_uom_class,
weight_volume_flag,
inv_controls_container_flag,
percent_fill_basis_flag,
trip_report_set_id,
delivery_report_set_id,
pack_slip_required_flag,
pick_sequence_rule_id,
pick_grouping_rule_id,
print_pick_slip_mode,
pick_release_report_set_id,
autocreate_del_orders_flag,
default_stage_subinventory,
default_stage_locator_id,
autodetail_pr_flag,
enforce_packing_flag,
group_by_customer_flag,
group_by_fob_flag,
group_by_freight_terms_flag,
group_by_intmed_ship_to_flag,
group_by_ship_method_flag,
group_by_carrier_flag,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
pick_slip_lines,
autocreate_deliveries_flag,
freight_class_cat_set_id,
commodity_code_cat_set_id,
enforce_ship_set_and_smc,
auto_send_doc_flag,
itm_additional_country_code,
auto_select_carrier,
ship_confirm_rule_id,
autopack_level,
task_planning_flag,
appending_limit,
export_screening_flag,
autocreate_del_orders_pr_flag,
fpa_positive_tol_amt,
fpa_negative_tol_amt,
fpa_positive_tol_percentage,
fpa_negative_tol_percentage,
fpa_default_freight_account,
auto_apply_routing_rules,
auto_calc_fgt_rate_cr_del,
auto_calc_fgt_rate_append_del,
auto_calc_fgt_rate_sc_del,
raise_business_events,
enable_tracking_wfs,
enable_sc_wf,
check_on_hand,
--OTM R12
max_net_weight,
max_gross_weight,
--OTM R12 Org-Specific
otm_enabled,
dynamic_replenishment_flag,
-- OTM Dock Door Appt Sched Proj
dock_appt_scheduling_flag,
retain_nonstaged_det_flag --Bug 7131800
)
Values ( p_ship_par_rec.goods_dispatched_account,
p_ship_par_rec.location_id,
p_ship_par_rec.organization_id,
p_ship_par_rec.weight_uom_class,
p_ship_par_rec.volume_uom_class,
p_ship_par_rec.weight_volume_flag,
p_ship_par_rec.inv_controls_container_flag,
p_ship_par_rec.percent_fill_basis_flag,
p_ship_par_rec.trip_report_set_id,
p_ship_par_rec.delivery_report_set_id,
p_ship_par_rec.pack_slip_required_flag,
p_ship_par_rec.pick_sequence_rule_id,
p_ship_par_rec.pick_grouping_rule_id,
p_ship_par_rec.print_pick_slip_mode,
p_ship_par_rec.pick_release_report_set_id,
p_ship_par_rec.autocreate_del_orders_flag,
p_ship_par_rec.default_stage_subinventory,
p_ship_par_rec.default_stage_locator_id,
p_ship_par_rec.autodetail_pr_flag,
p_ship_par_rec.enforce_packing_flag,
p_ship_par_rec.group_by_customer_flag,
p_ship_par_rec.group_by_fob_flag,
p_ship_par_rec.group_by_freight_terms_flag,
p_ship_par_rec.group_by_intmed_ship_to_flag,
p_ship_par_rec.group_by_ship_method_flag,
p_ship_par_rec.group_by_carrier_flag,
p_ship_par_rec.attribute_category,
p_ship_par_rec.attribute1,
p_ship_par_rec.attribute2,
p_ship_par_rec.attribute3,
p_ship_par_rec.attribute4,
p_ship_par_rec.attribute5,
p_ship_par_rec.attribute6,
p_ship_par_rec.attribute7,
p_ship_par_rec.attribute8,
p_ship_par_rec.attribute9,
p_ship_par_rec.attribute10,
p_ship_par_rec.attribute11,
p_ship_par_rec.attribute12,
p_ship_par_rec.attribute13,
p_ship_par_rec.attribute14,
p_ship_par_rec.attribute15,
nvl(p_ship_par_rec.creation_date,sysdate),
nvl(p_ship_par_rec.created_by,l_user_id),
nvl(p_ship_par_rec.last_update_date,sysdate),
nvl(p_ship_par_rec.last_updated_by,l_user_id),
nvl(p_ship_par_rec.last_update_login,l_login_id),
p_ship_par_rec.pick_slip_lines,
p_ship_par_rec.autocreate_deliveries_flag,
p_ship_par_rec.freight_class_cat_set_id,
p_ship_par_rec.commodity_code_cat_set_id,
p_ship_par_rec.enforce_ship_set_and_smc,
p_ship_par_rec.auto_send_doc_flag,
p_ship_par_rec.itm_additional_country_code,
p_ship_par_rec.auto_select_carrier,
p_ship_par_rec.ship_confirm_rule_id,
p_ship_par_rec.autopack_level,
p_ship_par_rec.task_planning_flag,
p_ship_par_rec.appending_limit,
p_ship_par_rec.export_screening_flag,
p_ship_par_rec.autocreate_del_orders_pr_flag,
p_ship_par_rec.fpa_positive_tol_amt,
p_ship_par_rec.fpa_negative_tol_amt,
p_ship_par_rec.fpa_positive_tol_percentage,
p_ship_par_rec.fpa_negative_tol_percentage,
p_ship_par_rec.fpa_default_freight_account,
p_ship_par_rec.auto_apply_routing_rules,
p_ship_par_rec.auto_calc_fgt_rate_cr_del,
p_ship_par_rec.auto_calc_fgt_rate_append_del,
p_ship_par_rec.auto_calc_fgt_rate_sc_del,
p_ship_par_rec.raise_business_events,
p_ship_par_rec.enable_tracking_wfs,
p_ship_par_rec.enable_sc_wf,
p_ship_par_rec.check_on_hand,
--OTM R12
p_ship_par_rec.max_net_weight,
p_ship_par_rec.max_gross_weight,
--OTM R12 Org-Specific
p_ship_par_rec.otm_enabled,
p_ship_par_rec.dynamic_replenishment_flag,
--OTM Dock Door App Sched Proj
p_ship_par_rec.dock_appt_scheduling_flag,
--Bug 7131800
p_ship_par_rec.retain_nonstaged_det_flag);
wsh_debug_sv.log(l_module_name,'After insert into WSH_SHIPPING_PARAMETERS SQLERRM : ',sqlerrm);
wsh_debug_sv.log(l_module_name,'After insert into WSH_SHIPPING_PARAMETERS SQLERRM : ',sqlerrm);
Delete from wsh_shipping_parameter_values
Where organization_id = l_freight_terms_rec.organization_id
and class_code = 'FREIGHT_TERMS'
and param_name = 'FREIGHT_TERMS';
wsh_debug_sv.log(l_module_name,'After delete of Freight Terms from wsh_shipping_parameter_values : ',sqlerrm);
wsh_debug_sv.log(l_module_name,'After delete of Freight Terms from wsh_shipping_parameter_values : ',sqlerrm);
wsh_debug_sv.log(l_module_name,'Values to be inserted into WSH_SHIPPING_PARAMETER_VALUES as follows');
insert_parameter_values (l_ship_par_val_rec);
INSERT INTO wsh_shipping_parameter_values(
organization_id,
class_code,
param_name,
param_value,
param_data_type,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
VALUES (l_ship_par_val_rec.organization_id,
l_ship_par_val_rec.class_code(i),
l_ship_par_val_rec.param_name(i),
l_ship_par_val_rec.param_value_chr(i),
p_ship_par_val_rec.param_data_type(i),
sysdate,
l_user_id,
sysdate,
l_user_id,
l_login_id);
wsh_debug_sv.log(l_module_name,'After insert of values into wsh_shipping_parameter_values table : ',sqlerrm);
wsh_debug_sv.log(l_module_name,'After insert of values into wsh_shipping_parameter_values table : ',sqlerrm);
wsh_debug_sv.log(l_module_name,'After insert into WSH_SHIPPING_PARAMETER_VALUES SQLERRM : ',sqlerrm);
Procedure update_parameter(
p_ship_par_rec IN parameter_rec_typ,
p_ship_par_val_rec IN parameter_value_rec_typ,
x_return_status OUT NOCOPY VARCHAR2) is
cursor l_shipping_param_values_csr ( p_organization_id in number ) is
select param_name
,count(*) record_count
from wsh_shipping_parameter_values
where organization_id = p_organization_id
and param_name in (
'BOL_TEMPLATE'
,'EVAL_RULE_BASED_ON_SHIPTO_CUST'
,'MBOL_TEMPLATE'
,'PACKSLIP_TEMPLATE'
,'SKIP_RTNG_RULE_AC_TRIP')
group by param_name;
l_all_update boolean := FALSE;
l_all_insert boolean := FALSE;
l_update_insert boolean := FALSE;
l_module_name constant varchar(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_PARAMETER';
Select param_value
from wsh_shipping_parameter_values
where param_name = p_param_name
and class_code = p_class_code
and param_value = p_param_value
and organization_id = p_organization_id;
UPDATE wsh_shipping_parameters set
goods_dispatched_account = p_ship_par_rec.goods_dispatched_account,
location_id = p_ship_par_rec.location_id,
organization_id = p_ship_par_rec.organization_id,
weight_uom_class = p_ship_par_rec.weight_uom_class,
volume_uom_class = p_ship_par_rec.volume_uom_class,
weight_volume_flag = p_ship_par_rec.weight_volume_flag,
inv_controls_container_flag = p_ship_par_rec.inv_controls_container_flag,
percent_fill_basis_flag = p_ship_par_rec.percent_fill_basis_flag,
trip_report_set_id = p_ship_par_rec.trip_report_set_id,
delivery_report_set_id = p_ship_par_rec.delivery_report_set_id,
pack_slip_required_flag = p_ship_par_rec.pack_slip_required_flag,
pick_sequence_rule_id = p_ship_par_rec.pick_sequence_rule_id,
pick_grouping_rule_id = p_ship_par_rec.pick_grouping_rule_id,
print_pick_slip_mode = p_ship_par_rec.print_pick_slip_mode,
pick_release_report_set_id = p_ship_par_rec.pick_release_report_set_id,
autocreate_del_orders_flag = p_ship_par_rec.autocreate_del_orders_flag,
default_stage_subinventory = p_ship_par_rec.default_stage_subinventory,
default_stage_locator_id = p_ship_par_rec.default_stage_locator_id,
autodetail_pr_flag = p_ship_par_rec.autodetail_pr_flag,
enforce_packing_flag = p_ship_par_rec.enforce_packing_flag,
group_by_customer_flag = p_ship_par_rec.group_by_customer_flag,
group_by_fob_flag = p_ship_par_rec.group_by_fob_flag,
group_by_freight_terms_flag = p_ship_par_rec.group_by_freight_terms_flag,
group_by_intmed_ship_to_flag = p_ship_par_rec.group_by_intmed_ship_to_flag,
group_by_ship_method_flag = p_ship_par_rec.group_by_ship_method_flag,
group_by_carrier_flag = p_ship_par_rec.group_by_carrier_flag,
attribute_category = p_ship_par_rec.attribute_category,
attribute1 = p_ship_par_rec.attribute1,
attribute2 = p_ship_par_rec.attribute2,
attribute3 = p_ship_par_rec.attribute3,
attribute4 = p_ship_par_rec.attribute4,
attribute5 = p_ship_par_rec.attribute5,
attribute6 = p_ship_par_rec.attribute6,
attribute7 = p_ship_par_rec.attribute7,
attribute8 = p_ship_par_rec.attribute8,
attribute9 = p_ship_par_rec.attribute9,
attribute10 = p_ship_par_rec.attribute10,
attribute11 = p_ship_par_rec.attribute11,
attribute12 = p_ship_par_rec.attribute12,
attribute13 = p_ship_par_rec.attribute13,
attribute14 = p_ship_par_rec.attribute14,
attribute15 = p_ship_par_rec.attribute15,
last_update_date = nvl(p_ship_par_rec.last_update_date,sysdate),
last_updated_by = nvl(p_ship_par_rec.last_updated_by,l_user_id),
last_update_login = nvl(p_ship_par_rec.last_update_login,l_login_id),
pick_slip_lines = p_ship_par_rec.pick_slip_lines,
autocreate_deliveries_flag = p_ship_par_rec.autocreate_deliveries_flag,
freight_class_cat_set_id = p_ship_par_rec.freight_class_cat_set_id,
commodity_code_cat_set_id = p_ship_par_rec.commodity_code_cat_set_id,
enforce_ship_set_and_smc = p_ship_par_rec.enforce_ship_set_and_smc,
auto_send_doc_flag = p_ship_par_rec.auto_send_doc_flag,
itm_additional_country_code = p_ship_par_rec.itm_additional_country_code,
auto_select_carrier = p_ship_par_rec.auto_select_carrier,
ship_confirm_rule_id = p_ship_par_rec.ship_confirm_rule_id,
autopack_level = p_ship_par_rec.autopack_level,
task_planning_flag = p_ship_par_rec.task_planning_flag,
appending_limit = p_ship_par_rec.appending_limit,
export_screening_flag = p_ship_par_rec.export_screening_flag,
autocreate_del_orders_pr_flag = p_ship_par_rec.autocreate_del_orders_pr_flag,
fpa_positive_tol_amt = p_ship_par_rec.fpa_positive_tol_amt,
fpa_negative_tol_amt = p_ship_par_rec.fpa_negative_tol_amt,
fpa_positive_tol_percentage = p_ship_par_rec.fpa_positive_tol_percentage,
fpa_negative_tol_percentage = p_ship_par_rec.fpa_negative_tol_percentage,
fpa_default_freight_account = p_ship_par_rec.fpa_default_freight_account,
auto_apply_routing_rules = p_ship_par_rec.auto_apply_routing_rules,
auto_calc_fgt_rate_cr_del = p_ship_par_rec.auto_calc_fgt_rate_cr_del,
auto_calc_fgt_rate_append_del = p_ship_par_rec.auto_calc_fgt_rate_append_del,
auto_calc_fgt_rate_sc_del = p_ship_par_rec.auto_calc_fgt_rate_sc_del,
raise_business_events = p_ship_par_rec.raise_business_events,
enable_tracking_wfs = p_ship_par_rec.enable_tracking_wfs,
enable_sc_wf = p_ship_par_rec.enable_sc_wf,
check_on_hand = p_ship_par_rec.check_on_hand,
--OTM R12
max_net_weight = p_ship_par_rec.max_net_weight,
max_gross_weight = p_ship_par_rec.max_gross_weight,
--OTM R12 Org-Specific
otm_enabled = p_ship_par_rec.otm_enabled,
dynamic_replenishment_flag = p_ship_par_rec.dynamic_replenishment_flag, --bug# 6689448 (replenishment project)
dock_appt_scheduling_flag = p_ship_par_rec.dock_appt_scheduling_flag, --bug 6700792: OTM Dock Door App Sched Proj
retain_nonstaged_det_flag = p_ship_par_rec.retain_nonstaged_det_flag --Bug 7131800
WHERE organization_id = p_ship_par_rec.organization_id;
wsh_debug_sv.log(l_module_name,'After update of values in wsh_shipping_parameters table : ',sqlerrm);
wsh_debug_sv.log(l_module_name,'After update of values in wsh_shipping_parameters table : ',sqlerrm);
l_all_insert := TRUE;
If l_all_insert then
ins_count := nvl(l_ins_ship_par_val_rec.class_code.count,0) + 1;
Delete from wsh_shipping_parameter_values
Where organization_id = l_freight_terms_rec.organization_id
and class_code = 'FREIGHT_TERMS'
and param_name = 'FREIGHT_TERMS';
wsh_debug_sv.log(l_module_name,'After delete of Freigth Terms from wsh_shipping_parameter_value : ',sqlerrm);
wsh_debug_sv.log(l_module_name,'After delete of Freigth Terms from wsh_shipping_parameter_value : ',sqlerrm);
If l_all_insert then
If l_ins_ship_par_val_rec.class_code.COUNT > 0 then
If l_debug_on then
wsh_debug_sv.log(l_module_name,'In l_all_insert condition of If statement');
insert_parameter_values (l_ins_ship_par_val_rec);
wsh_debug_sv.log(l_module_name,'After insert of values in wsh_shipping_parameter_value table : ',sqlerrm);
wsh_debug_sv.log(l_module_name,'After insert of values in wsh_shipping_parameter_value table : ',sqlerrm);
wsh_debug_sv.log(l_module_name,'Going for insert in ELSE part of l_all_insert condition If statement');
insert_parameter_values (l_ins_ship_par_val_rec);
wsh_debug_sv.log(l_module_name,'After insert of values in wsh_shipping_parameter_values table : ',sqlerrm);
wsh_debug_sv.log(l_module_name,'After insert of values in wsh_shipping_parameter_value table : ',sqlerrm);
wsh_debug_sv.log(l_module_name,'Going for update in ELSE part of l_all_insert condition If statement');
update wsh_shipping_parameter_values
set param_value = l_upd_ship_par_val_rec.param_value_chr(i)
, creation_date = sysdate
, created_by = l_user_id
, last_update_date = sysdate
, last_updated_by = l_user_id
, last_update_login= l_login_id
where organization_id = l_upd_ship_par_val_rec.organization_id
and param_data_type = l_upd_ship_par_val_rec.param_data_type(i)
and param_name = l_upd_ship_par_val_rec.param_name(i);
wsh_debug_sv.log(l_module_name,'After update of values in wsh_shipping_parameter_values table : ',sqlerrm);
wsh_debug_sv.log(l_module_name,'After update of values in wsh_shipping_parameter_value table : ',sqlerrm);
End update_parameter;
p_last_update_date IN DATE,
x_return_status OUT NOCOPY VARCHAR2) is
Cursor get_last_update_date_csr is
select last_update_date
from wsh_shipping_parameters
where organization_id = p_organization_id;
select 1
from wsh_shipping_parameters
where organization_id = p_organization_id
for update nowait;
Open get_last_update_date_csr;
Fetch get_last_update_date_csr into l_date;
Close get_last_update_date_csr;
If l_date <> p_last_update_date then
fnd_message.set_name('FND' , 'FND_RECORD_CHANGED_ERROR');
fnd_message.set_name('FND', 'FND_RECORD_DELETED_ERROR');
select template_name
from xdo_templates_vl
where data_source_code = p_concprg_name
and template_code = p_code_field;
select class_code
, param_name
, decode(class_code
,'ROUTING_RULES',nvl(param_value, 'N'),param_value) param_value
, param_data_type
from wsh_shipping_parameter_values
where organization_id = p_organization_id ;
wsh_debug_sv.log(l_module_name,'After select from wsh_shipping_parameter_values : ',sqlerrm);
Select param_value
from wsh_shipping_parameter_values
where param_name = p_param_name
and class_code = p_class_code
and param_value = p_param_value
and organization_id = p_organization_id;
wsh_debug_sv.log(l_module_name, 'One of the inserted records matches existing record in wsh_shipping_parameter_values table');
wsh_debug_sv.log(l_module_name,'Before insert of values into wsh_shipping_parameter_values table : ');
Insert into wsh_shipping_parameter_values(
organization_id,
class_code,
param_name,
param_value,
param_data_type,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
Values (p_freight_terms_rec.organization_id,
p_freight_terms_rec.class_code(i),
p_freight_terms_rec.param_name(i),
p_freight_terms_rec.param_value_chr(i),
p_freight_terms_rec.param_data_type(i),
sysdate,
l_user_id,
sysdate,
l_user_id,
l_login_id);
wsh_debug_sv.log(l_module_name,'After insert of values in wsh_shipping_parameter_values table : ',sqlerrm);
wsh_debug_sv.log(l_module_name,'After insert of values in wsh_shipping_parameter_values table : ',sqlerrm);
wsh_debug_sv.log(l_module_name,'After insert into wsh_shipping_parameter_values SQLERRM : ',SQLERRM);
Procedure update_freight_terms(
p_freight_terms_rec IN parameter_value_rec_typ,
row_id IN row_id_tbl,
x_return_status OUT NOCOPY VARCHAR2) is
Cursor check_duplicate_csr(p_row_id in varchar2
,p_param_name in varchar2
,p_param_value in varchar2
,p_class_code in varchar2
,p_organization_id in number ) is
Select param_value
from wsh_shipping_parameter_values
where rowid <> p_row_id
and param_name = p_param_name
and class_code = p_class_code
and param_value = p_param_value
and organization_id = p_organization_id;
l_module_name constant varchar(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_FREIGHT_TERMS';
wsh_debug_sv.log(l_module_name, 'Before update of wsh_shipping_parameter_values table');
Update wsh_shipping_parameter_values
Set param_value = p_freight_terms_rec.param_value_chr(i),
creation_date = sysdate,
created_by = l_user_id,
last_update_date = sysdate,
last_updated_by = l_user_id,
last_update_login = l_login_id
Where rowid = row_id(i);
wsh_debug_sv.log(l_module_name,'After insert of values in wsh_shipping_parameter_values table : ',sqlerrm);
wsh_debug_sv.log(l_module_name, 'After update of wsh_shipping_parameter_values SQLERRM',SQLERRM);
End update_freight_terms;
Procedure delete_freight_terms(
p_freight_terms_rec IN parameter_value_rec_typ,
row_id IN row_id_tbl,
x_return_status OUT NOCOPY VARCHAR2) is
l_debug_on boolean;
l_module_name constant varchar(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DELETE_FREIGHT_TERMS';
Delete from wsh_shipping_parameter_values
Where rowid = row_id(i);
End delete_freight_terms;
wsh_debug_sv.log(p_module_name,'x_param_info.last_update_date',x_param_info.last_update_date);
wsh_debug_sv.log(p_module_name,'x_param_info.last_updated_by',x_param_info.last_updated_by);
wsh_debug_sv.log(p_module_name,'x_param_info.last_update_login',x_param_info.last_update_login);
wsh_debug_sv.log(p_module_name,'x_param_info.program_update_date',x_param_info.program_update_date);
wsh_debug_sv.log(p_module_name,'x_param_info.auto_select_carrier',x_param_info.auto_select_carrier);