[Home] [Help]
730: , param_name
731: , decode(class_code
732: ,'ROUTING_RULES',nvl(param_value, 'N'),param_value) param_value
733: , param_data_type
734: from wsh_shipping_parameter_values
735: where organization_id = p_organization_id ;
736:
737: type l_varchar2_tbl is table of wsh_shipping_parameter_values.param_value%TYPE index by binary_integer;
738: l_param_value l_varchar2_tbl;
733: , param_data_type
734: from wsh_shipping_parameter_values
735: where organization_id = p_organization_id ;
736:
737: type l_varchar2_tbl is table of wsh_shipping_parameter_values.param_value%TYPE index by binary_integer;
738: l_param_value l_varchar2_tbl;
739: l_parameter_name wsh_util_core.column_tab_type;
740: l_param_value_info parameter_value_rec_typ;
741: temp_param_value_info parameter_value_rec_typ;
803: End Loop;
804:
805: If l_found and l_debug_on then
806: print_debug_info(l_module_name
807: ,'WSH_SHIPPING_PARAMETER_VALUES'
808: ,null
809: ,x_param_value_info
810: ,l_return_status);
811: WSH_DEBUG_SV.pop(l_module_name);
891: l_user_id number:= fnd_global.user_id;
892: l_login_id number:= fnd_global.login_id;
893: Begin
894: forall i in p_ins_ship_par_val_rec.class_code.first..p_ins_ship_par_val_rec.class_code.last
895: insert into wsh_shipping_parameter_values(
896: organization_id,
897: class_code,
898: param_name,
899: param_value,
920: p_ship_par_rec IN parameter_rec_typ,
921: p_ship_par_val_rec IN parameter_value_rec_typ,
922: x_return_status OUT NOCOPY VARCHAR2) is
923:
924: type l_parameter_value_type is table of wsh_shipping_parameter_values.PARAM_VALUE%TYPE index by binary_integer;
925: type l_parameter_name_type is table of wsh_shipping_parameter_values.PARAM_NAME%TYPE index by binary_integer;
926:
927: l_freight_terms_value l_parameter_value_type;
928: l_param_name l_parameter_name_type;
921: p_ship_par_val_rec IN parameter_value_rec_typ,
922: x_return_status OUT NOCOPY VARCHAR2) is
923:
924: type l_parameter_value_type is table of wsh_shipping_parameter_values.PARAM_VALUE%TYPE index by binary_integer;
925: type l_parameter_name_type is table of wsh_shipping_parameter_values.PARAM_NAME%TYPE index by binary_integer;
926:
927: l_freight_terms_value l_parameter_value_type;
928: l_param_name l_parameter_name_type;
929: l_param_value_tbl l_parameter_value_type;
1187:
1188: If l_freight_terms_rec.class_code.count > 0 then
1189:
1190: Begin
1191: Delete from wsh_shipping_parameter_values
1192: Where organization_id = l_freight_terms_rec.organization_id
1193: and class_code = 'FREIGHT_TERMS'
1194: and param_name = 'FREIGHT_TERMS';
1195: If l_debug_on then
1192: Where organization_id = l_freight_terms_rec.organization_id
1193: and class_code = 'FREIGHT_TERMS'
1194: and param_name = 'FREIGHT_TERMS';
1195: If l_debug_on then
1196: wsh_debug_sv.log(l_module_name,'After delete of Freight Terms from wsh_shipping_parameter_values : ',sqlerrm);
1197: End If;
1198: Exception
1199: When others then
1200: If l_debug_on then
1197: End If;
1198: Exception
1199: When others then
1200: If l_debug_on then
1201: wsh_debug_sv.log(l_module_name,'After delete of Freight Terms from wsh_shipping_parameter_values : ',sqlerrm);
1202: End If;
1203: fnd_message.set_name('WSH','WSH_UNEXP_ERROR');
1204: fnd_message.set_token('PACKAGE',l_module_name);
1205: fnd_message.set_token('ORA_ERROR',to_char(sqlcode));
1221: End If;
1222: End If;
1223:
1224: If l_debug_on then
1225: wsh_debug_sv.log(l_module_name,'Values to be inserted into WSH_SHIPPING_PARAMETER_VALUES as follows');
1226: print_debug_info(l_module_name
1227: ,'WSH_SHIPPING_PARAMETER_VALUES'
1228: ,p_ship_par_rec
1229: ,l_ship_par_val_rec
1223:
1224: If l_debug_on then
1225: wsh_debug_sv.log(l_module_name,'Values to be inserted into WSH_SHIPPING_PARAMETER_VALUES as follows');
1226: print_debug_info(l_module_name
1227: ,'WSH_SHIPPING_PARAMETER_VALUES'
1228: ,p_ship_par_rec
1229: ,l_ship_par_val_rec
1230: ,l_return_status);
1231: End If;
1235: Begin
1236: insert_parameter_values (l_ship_par_val_rec);
1237: /*
1238: FORALL i in l_ship_par_val_rec.class_code.FIRST..l_ship_par_val_rec.class_code.LAST
1239: INSERT INTO wsh_shipping_parameter_values(
1240: organization_id,
1241: class_code,
1242: param_name,
1243: param_value,
1259: l_login_id);
1260: */
1261:
1262: If l_debug_on then
1263: wsh_debug_sv.log(l_module_name,'After insert of values into wsh_shipping_parameter_values table : ',sqlerrm);
1264: End If;
1265:
1266: Exception
1267: When Others then
1265:
1266: Exception
1267: When Others then
1268: If l_debug_on then
1269: wsh_debug_sv.log(l_module_name,'After insert of values into wsh_shipping_parameter_values table : ',sqlerrm);
1270: End If;
1271: fnd_message.set_name('WSH','WSH_UNEXP_ERROR');
1272: fnd_message.set_token('PACKAGE',l_module_name);
1273: fnd_message.set_token('ORA_ERROR',to_char(sqlcode));
1278:
1279: End If; /* End of Main If logic */
1280:
1281: If l_debug_on then
1282: wsh_debug_sv.log(l_module_name,'After insert into WSH_SHIPPING_PARAMETER_VALUES SQLERRM : ',sqlerrm);
1283: wsh_debug_sv.pop(l_module_name);
1284: End If;
1285:
1286: Exception
1314:
1315: cursor l_shipping_param_values_csr ( p_organization_id in number ) is
1316: select param_name
1317: ,count(*) record_count
1318: from wsh_shipping_parameter_values
1319: where organization_id = p_organization_id
1320: and param_name in (
1321: 'BOL_TEMPLATE'
1322: ,'EVAL_RULE_BASED_ON_SHIPTO_CUST'
1324: ,'PACKSLIP_TEMPLATE'
1325: ,'SKIP_RTNG_RULE_AC_TRIP')
1326: group by param_name;
1327:
1328: type l_param_value_tbl_type is table of wsh_shipping_parameter_values.param_value%TYPE index by binary_integer;
1329: type l_param_name_tbl_type is table of wsh_shipping_parameter_values.param_name%TYPE index by binary_integer;
1330: type l_param_class_code_tbl_type is table of wsh_shipping_parameter_values.class_code%TYPE index by binary_integer;
1331: type l_param_data_type_tbl_type is table of wsh_shipping_parameter_values.param_data_type%TYPE index by binary_integer;
1332: type l_number_tbl is table of number index by binary_integer;
1325: ,'SKIP_RTNG_RULE_AC_TRIP')
1326: group by param_name;
1327:
1328: type l_param_value_tbl_type is table of wsh_shipping_parameter_values.param_value%TYPE index by binary_integer;
1329: type l_param_name_tbl_type is table of wsh_shipping_parameter_values.param_name%TYPE index by binary_integer;
1330: type l_param_class_code_tbl_type is table of wsh_shipping_parameter_values.class_code%TYPE index by binary_integer;
1331: type l_param_data_type_tbl_type is table of wsh_shipping_parameter_values.param_data_type%TYPE index by binary_integer;
1332: type l_number_tbl is table of number index by binary_integer;
1333:
1326: group by param_name;
1327:
1328: type l_param_value_tbl_type is table of wsh_shipping_parameter_values.param_value%TYPE index by binary_integer;
1329: type l_param_name_tbl_type is table of wsh_shipping_parameter_values.param_name%TYPE index by binary_integer;
1330: type l_param_class_code_tbl_type is table of wsh_shipping_parameter_values.class_code%TYPE index by binary_integer;
1331: type l_param_data_type_tbl_type is table of wsh_shipping_parameter_values.param_data_type%TYPE index by binary_integer;
1332: type l_number_tbl is table of number index by binary_integer;
1333:
1334: l_param_name_tbl l_param_name_tbl_type;
1327:
1328: type l_param_value_tbl_type is table of wsh_shipping_parameter_values.param_value%TYPE index by binary_integer;
1329: type l_param_name_tbl_type is table of wsh_shipping_parameter_values.param_name%TYPE index by binary_integer;
1330: type l_param_class_code_tbl_type is table of wsh_shipping_parameter_values.class_code%TYPE index by binary_integer;
1331: type l_param_data_type_tbl_type is table of wsh_shipping_parameter_values.param_data_type%TYPE index by binary_integer;
1332: type l_number_tbl is table of number index by binary_integer;
1333:
1334: l_param_name_tbl l_param_name_tbl_type;
1335: l_record_count_tbl l_number_tbl;
1357: ,p_param_value in varchar2
1358: ,p_class_code in varchar2
1359: ,p_organization_id in number ) is
1360: Select param_value
1361: from wsh_shipping_parameter_values
1362: where param_name = p_param_name
1363: and class_code = p_class_code
1364: and param_value = p_param_value
1365: and organization_id = p_organization_id;
1564:
1565: If l_freight_terms_rec.class_code.count > 0 then
1566: l_freight_terms_rec.organization_id := p_ship_par_val_rec.organization_id;
1567: Begin
1568: Delete from wsh_shipping_parameter_values
1569: Where organization_id = l_freight_terms_rec.organization_id
1570: and class_code = 'FREIGHT_TERMS'
1571: and param_name = 'FREIGHT_TERMS';
1572: If l_debug_on then
1624: End If;
1625: Begin
1626: insert_parameter_values (l_ins_ship_par_val_rec);
1627: If l_debug_on then
1628: wsh_debug_sv.log(l_module_name,'After insert of values in wsh_shipping_parameter_values table : ',sqlerrm);
1629: End If;
1630: Exception
1631: When others then
1632: If l_debug_on then
1648: End If;
1649:
1650: Begin
1651: forall i in l_upd_ship_par_val_rec.class_code.first..l_upd_ship_par_val_rec.class_code.last
1652: update wsh_shipping_parameter_values
1653: set param_value = l_upd_ship_par_val_rec.param_value_chr(i)
1654: , creation_date = sysdate
1655: , created_by = l_user_id
1656: , last_update_date = sysdate
1660: and param_data_type = l_upd_ship_par_val_rec.param_data_type(i)
1661: and param_name = l_upd_ship_par_val_rec.param_name(i);
1662:
1663: If l_debug_on then
1664: wsh_debug_sv.log(l_module_name,'After update of values in wsh_shipping_parameter_values table : ',sqlerrm);
1665: End If;
1666: Exception
1667: When others then
1668: If l_debug_on then
1846: x_param_info OUT NOCOPY parameter_rec_typ,
1847: x_param_value_info OUT NOCOPY parameter_value_rec_typ,
1848: x_return_status OUT NOCOPY VARCHAR2) IS
1849:
1850: type l_varchar2_tb is table of wsh_shipping_parameter_values.param_value%TYPE index by binary_integer;
1851: l_param_value l_varchar2_tb;
1852:
1853: cursor get_paramter_values_csr(p_organization_id in number) is
1854: select class_code
1855: , param_name
1856: , decode(class_code
1857: ,'ROUTING_RULES',nvl(param_value, 'N'),param_value) param_value
1858: , param_data_type
1859: from wsh_shipping_parameter_values
1860: where organization_id = p_organization_id ;
1861:
1862: l_param_info parameter_Rec_Typ;
1863: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET';
1894: x_param_info := l_param_info;
1895: End If;
1896:
1897: ------------------------------------------------------------------------
1898: -- Get values from wsh_shipping_parameter_values table .Don't raise --
1899: -- error is records dosen't exists. --
1900: ------------------------------------------------------------------------
1901: l_found := FALSE;
1902: If g_parameter_values.count > 0 then
1909: End If;
1910: End Loop;
1911: If l_found and l_debug_on then
1912: print_debug_info(l_module_name
1913: ,'WSH_SHIPPING_PARAMETER_VALUES'
1914: ,x_param_info
1915: ,x_param_value_info
1916: ,l_return_status);
1917: WSH_DEBUG_SV.pop(l_module_name);
1928: ,l_param_value
1929: ,x_param_value_info.param_data_type;
1930: Close get_paramter_values_csr;
1931: If l_debug_on then
1932: wsh_debug_sv.log(l_module_name,'After select from wsh_shipping_parameter_values : ',sqlerrm);
1933: wsh_debug_sv.log(l_module_name,'Row_Count = : ',x_param_value_info.param_name.count);
1934: End If;
1935:
1936: If x_param_value_info.param_name.COUNT > 0 then
1953: End if;
1954:
1955: If l_found and l_debug_on then
1956: print_debug_info(l_module_name
1957: ,'WSH_SHIPPING_PARAMETER_VALUES'
1958: ,x_param_info
1959: ,x_param_value_info
1960: ,l_return_status);
1961: End If;
1992: Procedure create_freight_terms(
1993: p_freight_terms_rec IN parameter_value_rec_typ,
1994: x_return_status OUT NOCOPY VARCHAR2)is
1995:
1996: type l_parameter_value_type is table of wsh_shipping_parameter_values.PARAM_VALUE%TYPE index by binary_integer;
1997:
1998: l_freight_terms_value l_parameter_value_type;
1999: l_org_name varchar2(2000) := wsh_util_core.get_org_name(p_freight_terms_rec.organization_id);
2000: l_user_id number:= fnd_global.user_id;
2008: ,p_param_value in varchar2
2009: ,p_class_code in varchar2
2010: ,p_organization_id in number ) is
2011: Select param_value
2012: from wsh_shipping_parameter_values
2013: where param_name = p_param_name
2014: and class_code = p_class_code
2015: and param_value = p_param_value
2016: and organization_id = p_organization_id;
2028: End If;
2029: If l_debug_on then
2030: wsh_debug_sv.log(l_module_name, 'p_freight_terms_rec.class_code.count ', p_freight_terms_rec.class_code.count);
2031: print_debug_info(l_module_name
2032: ,'WSH_SHIPPING_PARAMETER_VALUES'
2033: ,null
2034: ,p_freight_terms_rec
2035: ,l_return_status);
2036: End If;
2064: close check_duplicate_csr;
2065:
2066: If l_freight_terms is not null then
2067: If l_debug_on then
2068: wsh_debug_sv.log(l_module_name, 'One of the inserted records matches existing record in wsh_shipping_parameter_values table');
2069: wsh_debug_sv.log(l_module_name, 'Duplicate Freight Terms is ',p_freight_terms_rec.param_value_chr(i));
2070: wsh_debug_sv.log(l_module_name, 'For Organization id ',p_freight_terms_rec.organization_id);
2071: End If;
2072: fnd_message.set_name('FND','FND_RECORD_CHANGED_ERROR');
2076: End Loop;
2077:
2078: If l_debug_on then
2079: wsh_debug_sv.log(l_module_name, 'p_freight_terms_rec.class_code.count ', p_freight_terms_rec.class_code.count);
2080: wsh_debug_sv.log(l_module_name,'Before insert of values into wsh_shipping_parameter_values table : ');
2081: End If;
2082:
2083: If p_freight_terms_rec.param_name.count > 0 then
2084: Begin
2082:
2083: If p_freight_terms_rec.param_name.count > 0 then
2084: Begin
2085: FORALL i in p_freight_terms_rec.class_code.FIRST..p_freight_terms_rec.class_code.LAST
2086: Insert into wsh_shipping_parameter_values(
2087: organization_id,
2088: class_code,
2089: param_name,
2090: param_value,
2105: l_user_id,
2106: l_login_id);
2107:
2108: If l_debug_on then
2109: wsh_debug_sv.log(l_module_name,'After insert of values in wsh_shipping_parameter_values table : ',sqlerrm);
2110: End If;
2111: Exception
2112: When others then
2113: If l_debug_on then
2110: End If;
2111: Exception
2112: When others then
2113: If l_debug_on then
2114: wsh_debug_sv.log(l_module_name,'After insert of values in wsh_shipping_parameter_values table : ',sqlerrm);
2115: End If;
2116: fnd_message.set_name('WSH','WSH_UNEXP_ERROR');
2117: fnd_message.set_token('PACKAGE',l_module_name);
2118: fnd_message.set_token('ORA_ERROR',to_char(sqlcode));
2122: End If;
2123: End If;
2124:
2125: If l_debug_on then
2126: wsh_debug_sv.log(l_module_name,'After insert into wsh_shipping_parameter_values SQLERRM : ',SQLERRM);
2127: wsh_debug_sv.pop(l_module_name);
2128: End If;
2129:
2130: Exception
2161: ,p_param_value in varchar2
2162: ,p_class_code in varchar2
2163: ,p_organization_id in number ) is
2164: Select param_value
2165: from wsh_shipping_parameter_values
2166: where rowid <> p_row_id
2167: and param_name = p_param_name
2168: and class_code = p_class_code
2169: and param_value = p_param_value
2188: End If;
2189: If l_debug_on then
2190: wsh_debug_sv.log(l_module_name, 'p_freight_terms_rec.class_code.count ', p_freight_terms_rec.class_code.count);
2191: print_debug_info(l_module_name
2192: ,'WSH_SHIPPING_PARAMETER_VALUES'
2193: ,null
2194: ,p_freight_terms_rec
2195: ,l_return_status);
2196: End If;
2218: End If;
2219: End Loop;
2220:
2221: If l_debug_on then
2222: wsh_debug_sv.log(l_module_name, 'Before update of wsh_shipping_parameter_values table');
2223: End If;
2224:
2225: If row_id.count > 0 then
2226: Begin
2224:
2225: If row_id.count > 0 then
2226: Begin
2227: Forall i in row_id.first..row_id.last
2228: Update wsh_shipping_parameter_values
2229: Set param_value = p_freight_terms_rec.param_value_chr(i),
2230: creation_date = sysdate,
2231: created_by = l_user_id,
2232: last_update_date = sysdate,
2235: Where rowid = row_id(i);
2236: Exception
2237: When others then
2238: If l_debug_on then
2239: wsh_debug_sv.log(l_module_name,'After insert of values in wsh_shipping_parameter_values table : ',sqlerrm);
2240: End If;
2241: fnd_message.set_name('WSH','WSH_UNEXP_ERROR');
2242: fnd_message.set_token('PACKAGE',l_module_name);
2243: fnd_message.set_token('ORA_ERROR',to_char(sqlcode));
2251: Raise handle_exception;
2252: End If;
2253:
2254: If l_debug_on then
2255: wsh_debug_sv.log(l_module_name, 'After update of wsh_shipping_parameter_values SQLERRM',SQLERRM);
2256: End If;
2257: End If;
2258:
2259: If l_debug_on then
2305: If l_debug_on then
2306: wsh_debug_sv.log(l_module_name, 'p_freight_terms_rec.class_code.count ', p_freight_terms_rec.class_code.count);
2307: wsh_debug_sv.log(l_module_name, 'row_id.count ', row_id.count);
2308: print_debug_info(l_module_name
2309: ,'WSH_SHIPPING_PARAMETER_VALUES'
2310: ,null
2311: ,p_freight_terms_rec
2312: ,l_return_status);
2313: End If;
2313: End If;
2314:
2315: If row_id.count > 0 then
2316: Forall i in row_id.first..row_id.last
2317: Delete from wsh_shipping_parameter_values
2318: Where rowid = row_id(i);
2319: End If;
2320:
2321: If l_debug_on then
2445: wsh_debug_sv.log(p_module_name,'x_param_info.volume_uom_code ',x_param_info.volume_uom_code);
2446: -- Bug 8446283
2447: End If;
2448:
2449: If p_print_info = 'WSH_SHIPPING_PARAMETER_VALUES' OR p_print_info = 'ALL' then
2450: wsh_debug_sv.log(p_module_name,'x_param_value_info.organization_id',x_param_value_info.organization_id);
2451: wsh_debug_sv.log(p_module_name,'x_param_value_info.param_name.COUNT',x_param_value_info.param_name.COUNT);
2452: If x_param_value_info.param_name.COUNT > 0 then
2453: For i in x_param_value_info.param_name.FIRST..x_param_value_info.param_name.LAST