[Home] [Help]
785: , param_name
786: , decode(class_code
787: ,'ROUTING_RULES',nvl(param_value, 'N'),param_value) param_value
788: , param_data_type
789: from wsh_shipping_parameter_values
790: where organization_id = p_organization_id ;
791:
792: type l_varchar2_tbl is table of wsh_shipping_parameter_values.param_value%TYPE index by binary_integer;
793: l_param_value l_varchar2_tbl;
788: , param_data_type
789: from wsh_shipping_parameter_values
790: where organization_id = p_organization_id ;
791:
792: type l_varchar2_tbl is table of wsh_shipping_parameter_values.param_value%TYPE index by binary_integer;
793: l_param_value l_varchar2_tbl;
794: l_parameter_name wsh_util_core.column_tab_type;
795: l_param_value_info parameter_value_rec_typ;
796: temp_param_value_info parameter_value_rec_typ;
858: End Loop;
859:
860: If l_found and l_debug_on then
861: print_debug_info(l_module_name
862: ,'WSH_SHIPPING_PARAMETER_VALUES'
863: ,null
864: ,x_param_value_info
865: ,l_return_status);
866: WSH_DEBUG_SV.pop(l_module_name);
946: l_user_id number:= fnd_global.user_id;
947: l_login_id number:= fnd_global.login_id;
948: Begin
949: forall i in p_ins_ship_par_val_rec.class_code.first..p_ins_ship_par_val_rec.class_code.last
950: insert into wsh_shipping_parameter_values(
951: organization_id,
952: class_code,
953: param_name,
954: param_value,
975: p_ship_par_rec IN parameter_rec_typ,
976: p_ship_par_val_rec IN parameter_value_rec_typ,
977: x_return_status OUT NOCOPY VARCHAR2) is
978:
979: type l_parameter_value_type is table of wsh_shipping_parameter_values.PARAM_VALUE%TYPE index by binary_integer;
980: type l_parameter_name_type is table of wsh_shipping_parameter_values.PARAM_NAME%TYPE index by binary_integer;
981:
982: l_freight_terms_value l_parameter_value_type;
983: l_param_name l_parameter_name_type;
976: p_ship_par_val_rec IN parameter_value_rec_typ,
977: x_return_status OUT NOCOPY VARCHAR2) is
978:
979: type l_parameter_value_type is table of wsh_shipping_parameter_values.PARAM_VALUE%TYPE index by binary_integer;
980: type l_parameter_name_type is table of wsh_shipping_parameter_values.PARAM_NAME%TYPE index by binary_integer;
981:
982: l_freight_terms_value l_parameter_value_type;
983: l_param_name l_parameter_name_type;
984: l_param_value_tbl l_parameter_value_type;
1233:
1234: If l_freight_terms_rec.class_code.count > 0 then
1235:
1236: Begin
1237: Delete from wsh_shipping_parameter_values
1238: Where organization_id = l_freight_terms_rec.organization_id
1239: and class_code = 'FREIGHT_TERMS'
1240: and param_name = 'FREIGHT_TERMS';
1241: If l_debug_on then
1238: Where organization_id = l_freight_terms_rec.organization_id
1239: and class_code = 'FREIGHT_TERMS'
1240: and param_name = 'FREIGHT_TERMS';
1241: If l_debug_on then
1242: wsh_debug_sv.log(l_module_name,'After delete of Freight Terms from wsh_shipping_parameter_values : ',sqlerrm);
1243: End If;
1244: Exception
1245: When others then
1246: If l_debug_on then
1243: End If;
1244: Exception
1245: When others then
1246: If l_debug_on then
1247: wsh_debug_sv.log(l_module_name,'After delete of Freight Terms from wsh_shipping_parameter_values : ',sqlerrm);
1248: End If;
1249: fnd_message.set_name('WSH','WSH_UNEXP_ERROR');
1250: fnd_message.set_token('PACKAGE',l_module_name);
1251: fnd_message.set_token('ORA_ERROR',to_char(sqlcode));
1267: End If;
1268: End If;
1269:
1270: If l_debug_on then
1271: wsh_debug_sv.log(l_module_name,'Values to be inserted into WSH_SHIPPING_PARAMETER_VALUES as follows');
1272: print_debug_info(l_module_name
1273: ,'WSH_SHIPPING_PARAMETER_VALUES'
1274: ,p_ship_par_rec
1275: ,l_ship_par_val_rec
1269:
1270: If l_debug_on then
1271: wsh_debug_sv.log(l_module_name,'Values to be inserted into WSH_SHIPPING_PARAMETER_VALUES as follows');
1272: print_debug_info(l_module_name
1273: ,'WSH_SHIPPING_PARAMETER_VALUES'
1274: ,p_ship_par_rec
1275: ,l_ship_par_val_rec
1276: ,l_return_status);
1277: End If;
1281: Begin
1282: insert_parameter_values (l_ship_par_val_rec);
1283: /*
1284: FORALL i in l_ship_par_val_rec.class_code.FIRST..l_ship_par_val_rec.class_code.LAST
1285: INSERT INTO wsh_shipping_parameter_values(
1286: organization_id,
1287: class_code,
1288: param_name,
1289: param_value,
1305: l_login_id);
1306: */
1307:
1308: If l_debug_on then
1309: wsh_debug_sv.log(l_module_name,'After insert of values into wsh_shipping_parameter_values table : ',sqlerrm);
1310: End If;
1311:
1312: Exception
1313: When Others then
1311:
1312: Exception
1313: When Others then
1314: If l_debug_on then
1315: wsh_debug_sv.log(l_module_name,'After insert of values into wsh_shipping_parameter_values table : ',sqlerrm);
1316: End If;
1317: fnd_message.set_name('WSH','WSH_UNEXP_ERROR');
1318: fnd_message.set_token('PACKAGE',l_module_name);
1319: fnd_message.set_token('ORA_ERROR',to_char(sqlcode));
1324:
1325: End If; /* End of Main If logic */
1326:
1327: If l_debug_on then
1328: wsh_debug_sv.log(l_module_name,'After insert into WSH_SHIPPING_PARAMETER_VALUES SQLERRM : ',sqlerrm);
1329: wsh_debug_sv.pop(l_module_name);
1330: End If;
1331:
1332: Exception
1360:
1361: cursor l_shipping_param_values_csr ( p_organization_id in number ) is
1362: select param_name
1363: ,count(*) record_count
1364: from wsh_shipping_parameter_values
1365: where organization_id = p_organization_id
1366: and param_name in (
1367: 'BOL_TEMPLATE'
1368: ,'EVAL_RULE_BASED_ON_SHIPTO_CUST'
1370: ,'PACKSLIP_TEMPLATE'
1371: ,'SKIP_RTNG_RULE_AC_TRIP')
1372: group by param_name;
1373:
1374: type l_param_value_tbl_type is table of wsh_shipping_parameter_values.param_value%TYPE index by binary_integer;
1375: type l_param_name_tbl_type is table of wsh_shipping_parameter_values.param_name%TYPE index by binary_integer;
1376: type l_param_class_code_tbl_type is table of wsh_shipping_parameter_values.class_code%TYPE index by binary_integer;
1377: type l_param_data_type_tbl_type is table of wsh_shipping_parameter_values.param_data_type%TYPE index by binary_integer;
1378: type l_number_tbl is table of number index by binary_integer;
1371: ,'SKIP_RTNG_RULE_AC_TRIP')
1372: group by param_name;
1373:
1374: type l_param_value_tbl_type is table of wsh_shipping_parameter_values.param_value%TYPE index by binary_integer;
1375: type l_param_name_tbl_type is table of wsh_shipping_parameter_values.param_name%TYPE index by binary_integer;
1376: type l_param_class_code_tbl_type is table of wsh_shipping_parameter_values.class_code%TYPE index by binary_integer;
1377: type l_param_data_type_tbl_type is table of wsh_shipping_parameter_values.param_data_type%TYPE index by binary_integer;
1378: type l_number_tbl is table of number index by binary_integer;
1379:
1372: group by param_name;
1373:
1374: type l_param_value_tbl_type is table of wsh_shipping_parameter_values.param_value%TYPE index by binary_integer;
1375: type l_param_name_tbl_type is table of wsh_shipping_parameter_values.param_name%TYPE index by binary_integer;
1376: type l_param_class_code_tbl_type is table of wsh_shipping_parameter_values.class_code%TYPE index by binary_integer;
1377: type l_param_data_type_tbl_type is table of wsh_shipping_parameter_values.param_data_type%TYPE index by binary_integer;
1378: type l_number_tbl is table of number index by binary_integer;
1379:
1380: l_param_name_tbl l_param_name_tbl_type;
1373:
1374: type l_param_value_tbl_type is table of wsh_shipping_parameter_values.param_value%TYPE index by binary_integer;
1375: type l_param_name_tbl_type is table of wsh_shipping_parameter_values.param_name%TYPE index by binary_integer;
1376: type l_param_class_code_tbl_type is table of wsh_shipping_parameter_values.class_code%TYPE index by binary_integer;
1377: type l_param_data_type_tbl_type is table of wsh_shipping_parameter_values.param_data_type%TYPE index by binary_integer;
1378: type l_number_tbl is table of number index by binary_integer;
1379:
1380: l_param_name_tbl l_param_name_tbl_type;
1381: l_record_count_tbl l_number_tbl;
1403: ,p_param_value in varchar2
1404: ,p_class_code in varchar2
1405: ,p_organization_id in number ) is
1406: Select param_value
1407: from wsh_shipping_parameter_values
1408: where param_name = p_param_name
1409: and class_code = p_class_code
1410: and param_value = p_param_value
1411: and organization_id = p_organization_id;
1606:
1607: If l_freight_terms_rec.class_code.count > 0 then
1608: l_freight_terms_rec.organization_id := p_ship_par_val_rec.organization_id;
1609: Begin
1610: Delete from wsh_shipping_parameter_values
1611: Where organization_id = l_freight_terms_rec.organization_id
1612: and class_code = 'FREIGHT_TERMS'
1613: and param_name = 'FREIGHT_TERMS';
1614: If l_debug_on then
1666: End If;
1667: Begin
1668: insert_parameter_values (l_ins_ship_par_val_rec);
1669: If l_debug_on then
1670: wsh_debug_sv.log(l_module_name,'After insert of values in wsh_shipping_parameter_values table : ',sqlerrm);
1671: End If;
1672: Exception
1673: When others then
1674: If l_debug_on then
1690: End If;
1691:
1692: Begin
1693: forall i in l_upd_ship_par_val_rec.class_code.first..l_upd_ship_par_val_rec.class_code.last
1694: update wsh_shipping_parameter_values
1695: set param_value = l_upd_ship_par_val_rec.param_value_chr(i)
1696: , creation_date = sysdate
1697: , created_by = l_user_id
1698: , last_update_date = sysdate
1702: and param_data_type = l_upd_ship_par_val_rec.param_data_type(i)
1703: and param_name = l_upd_ship_par_val_rec.param_name(i);
1704:
1705: If l_debug_on then
1706: wsh_debug_sv.log(l_module_name,'After update of values in wsh_shipping_parameter_values table : ',sqlerrm);
1707: End If;
1708: Exception
1709: When others then
1710: If l_debug_on then
1888: x_param_info OUT NOCOPY parameter_rec_typ,
1889: x_param_value_info OUT NOCOPY parameter_value_rec_typ,
1890: x_return_status OUT NOCOPY VARCHAR2) IS
1891:
1892: type l_varchar2_tb is table of wsh_shipping_parameter_values.param_value%TYPE index by binary_integer;
1893: l_param_value l_varchar2_tb;
1894:
1895: cursor get_paramter_values_csr(p_organization_id in number) is
1896: select class_code
1897: , param_name
1898: , decode(class_code
1899: ,'ROUTING_RULES',nvl(param_value, 'N'),param_value) param_value
1900: , param_data_type
1901: from wsh_shipping_parameter_values
1902: where organization_id = p_organization_id ;
1903:
1904: l_param_info parameter_Rec_Typ;
1905: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET';
1936: x_param_info := l_param_info;
1937: End If;
1938:
1939: ------------------------------------------------------------------------
1940: -- Get values from wsh_shipping_parameter_values table .Don't raise --
1941: -- error is records dosen't exists. --
1942: ------------------------------------------------------------------------
1943: l_found := FALSE;
1944: If g_parameter_values.count > 0 then
1951: End If;
1952: End Loop;
1953: If l_found and l_debug_on then
1954: print_debug_info(l_module_name
1955: ,'WSH_SHIPPING_PARAMETER_VALUES'
1956: ,x_param_info
1957: ,x_param_value_info
1958: ,l_return_status);
1959: WSH_DEBUG_SV.pop(l_module_name);
1970: ,l_param_value
1971: ,x_param_value_info.param_data_type;
1972: Close get_paramter_values_csr;
1973: If l_debug_on then
1974: wsh_debug_sv.log(l_module_name,'After select from wsh_shipping_parameter_values : ',sqlerrm);
1975: wsh_debug_sv.log(l_module_name,'Row_Count = : ',x_param_value_info.param_name.count);
1976: End If;
1977:
1978: If x_param_value_info.param_name.COUNT > 0 then
1995: End if;
1996:
1997: If l_found and l_debug_on then
1998: print_debug_info(l_module_name
1999: ,'WSH_SHIPPING_PARAMETER_VALUES'
2000: ,x_param_info
2001: ,x_param_value_info
2002: ,l_return_status);
2003: End If;
2034: Procedure create_freight_terms(
2035: p_freight_terms_rec IN parameter_value_rec_typ,
2036: x_return_status OUT NOCOPY VARCHAR2)is
2037:
2038: type l_parameter_value_type is table of wsh_shipping_parameter_values.PARAM_VALUE%TYPE index by binary_integer;
2039:
2040: l_freight_terms_value l_parameter_value_type;
2041: l_org_name varchar2(2000) := wsh_util_core.get_org_name(p_freight_terms_rec.organization_id);
2042: l_user_id number:= fnd_global.user_id;
2050: ,p_param_value in varchar2
2051: ,p_class_code in varchar2
2052: ,p_organization_id in number ) is
2053: Select param_value
2054: from wsh_shipping_parameter_values
2055: where param_name = p_param_name
2056: and class_code = p_class_code
2057: and param_value = p_param_value
2058: and organization_id = p_organization_id;
2070: End If;
2071: If l_debug_on then
2072: wsh_debug_sv.log(l_module_name, 'p_freight_terms_rec.class_code.count ', p_freight_terms_rec.class_code.count);
2073: print_debug_info(l_module_name
2074: ,'WSH_SHIPPING_PARAMETER_VALUES'
2075: ,null
2076: ,p_freight_terms_rec
2077: ,l_return_status);
2078: End If;
2106: close check_duplicate_csr;
2107:
2108: If l_freight_terms is not null then
2109: If l_debug_on then
2110: wsh_debug_sv.log(l_module_name, 'One of the inserted records matches existing record in wsh_shipping_parameter_values table');
2111: wsh_debug_sv.log(l_module_name, 'Duplicate Freight Terms is ',p_freight_terms_rec.param_value_chr(i));
2112: wsh_debug_sv.log(l_module_name, 'For Organization id ',p_freight_terms_rec.organization_id);
2113: End If;
2114: fnd_message.set_name('FND','FND_RECORD_CHANGED_ERROR');
2118: End Loop;
2119:
2120: If l_debug_on then
2121: wsh_debug_sv.log(l_module_name, 'p_freight_terms_rec.class_code.count ', p_freight_terms_rec.class_code.count);
2122: wsh_debug_sv.log(l_module_name,'Before insert of values into wsh_shipping_parameter_values table : ');
2123: End If;
2124:
2125: If p_freight_terms_rec.param_name.count > 0 then
2126: Begin
2124:
2125: If p_freight_terms_rec.param_name.count > 0 then
2126: Begin
2127: FORALL i in p_freight_terms_rec.class_code.FIRST..p_freight_terms_rec.class_code.LAST
2128: Insert into wsh_shipping_parameter_values(
2129: organization_id,
2130: class_code,
2131: param_name,
2132: param_value,
2147: l_user_id,
2148: l_login_id);
2149:
2150: If l_debug_on then
2151: wsh_debug_sv.log(l_module_name,'After insert of values in wsh_shipping_parameter_values table : ',sqlerrm);
2152: End If;
2153: Exception
2154: When others then
2155: If l_debug_on then
2152: End If;
2153: Exception
2154: When others then
2155: If l_debug_on then
2156: wsh_debug_sv.log(l_module_name,'After insert of values in wsh_shipping_parameter_values table : ',sqlerrm);
2157: End If;
2158: fnd_message.set_name('WSH','WSH_UNEXP_ERROR');
2159: fnd_message.set_token('PACKAGE',l_module_name);
2160: fnd_message.set_token('ORA_ERROR',to_char(sqlcode));
2164: End If;
2165: End If;
2166:
2167: If l_debug_on then
2168: wsh_debug_sv.log(l_module_name,'After insert into wsh_shipping_parameter_values SQLERRM : ',SQLERRM);
2169: wsh_debug_sv.pop(l_module_name);
2170: End If;
2171:
2172: Exception
2203: ,p_param_value in varchar2
2204: ,p_class_code in varchar2
2205: ,p_organization_id in number ) is
2206: Select param_value
2207: from wsh_shipping_parameter_values
2208: where rowid <> p_row_id
2209: and param_name = p_param_name
2210: and class_code = p_class_code
2211: and param_value = p_param_value
2230: End If;
2231: If l_debug_on then
2232: wsh_debug_sv.log(l_module_name, 'p_freight_terms_rec.class_code.count ', p_freight_terms_rec.class_code.count);
2233: print_debug_info(l_module_name
2234: ,'WSH_SHIPPING_PARAMETER_VALUES'
2235: ,null
2236: ,p_freight_terms_rec
2237: ,l_return_status);
2238: End If;
2260: End If;
2261: End Loop;
2262:
2263: If l_debug_on then
2264: wsh_debug_sv.log(l_module_name, 'Before update of wsh_shipping_parameter_values table');
2265: End If;
2266:
2267: If row_id.count > 0 then
2268: Begin
2266:
2267: If row_id.count > 0 then
2268: Begin
2269: Forall i in row_id.first..row_id.last
2270: Update wsh_shipping_parameter_values
2271: Set param_value = p_freight_terms_rec.param_value_chr(i),
2272: creation_date = sysdate,
2273: created_by = l_user_id,
2274: last_update_date = sysdate,
2277: Where rowid = row_id(i);
2278: Exception
2279: When others then
2280: If l_debug_on then
2281: wsh_debug_sv.log(l_module_name,'After insert of values in wsh_shipping_parameter_values table : ',sqlerrm);
2282: End If;
2283: fnd_message.set_name('WSH','WSH_UNEXP_ERROR');
2284: fnd_message.set_token('PACKAGE',l_module_name);
2285: fnd_message.set_token('ORA_ERROR',to_char(sqlcode));
2293: Raise handle_exception;
2294: End If;
2295:
2296: If l_debug_on then
2297: wsh_debug_sv.log(l_module_name, 'After update of wsh_shipping_parameter_values SQLERRM',SQLERRM);
2298: End If;
2299: End If;
2300:
2301: If l_debug_on then
2347: If l_debug_on then
2348: wsh_debug_sv.log(l_module_name, 'p_freight_terms_rec.class_code.count ', p_freight_terms_rec.class_code.count);
2349: wsh_debug_sv.log(l_module_name, 'row_id.count ', row_id.count);
2350: print_debug_info(l_module_name
2351: ,'WSH_SHIPPING_PARAMETER_VALUES'
2352: ,null
2353: ,p_freight_terms_rec
2354: ,l_return_status);
2355: End If;
2355: End If;
2356:
2357: If row_id.count > 0 then
2358: Forall i in row_id.first..row_id.last
2359: Delete from wsh_shipping_parameter_values
2360: Where rowid = row_id(i);
2361: End If;
2362:
2363: If l_debug_on then
2483: wsh_debug_sv.log(p_module_name,'x_param_info.dock_appt_scheduling_flag ',x_param_info.dock_appt_scheduling_flag ); --bug 6700792: OTM Dock Door App Sched Proj
2484: wsh_debug_sv.log(p_module_name,'x_param_info.retain_nonstaged_det_flag ',x_param_info.retain_nonstaged_det_flag ); --Bug 7131800
2485: End If;
2486:
2487: If p_print_info = 'WSH_SHIPPING_PARAMETER_VALUES' OR p_print_info = 'ALL' then
2488: wsh_debug_sv.log(p_module_name,'x_param_value_info.organization_id',x_param_value_info.organization_id);
2489: wsh_debug_sv.log(p_module_name,'x_param_value_info.param_name.COUNT',x_param_value_info.param_name.COUNT);
2490: If x_param_value_info.param_name.COUNT > 0 then
2491: For i in x_param_value_info.param_name.FIRST..x_param_value_info.param_name.LAST