1 package body WSH_CONSOL_BATCHES_PKG as
2 /* $Header: WSHTMCBB.pls 120.1 2005/09/08 14:03:10 wrudge noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_BATCH_ID in out nocopy NUMBER,
6 X_FREIGHT_TERMS_CODE in VARCHAR2,
7 X_CARRIER_ID in NUMBER,
8 X_MODE_OF_TRANSPORT in VARCHAR2,
9 X_SERVICE_LEVEL in VARCHAR2,
10 X_LOADING_SEQUENCE in NUMBER,
11 X_INTMED_SHIP_TO_LOCATION_ID in NUMBER,
12 X_ULTI_SHIP_TO_LOCATION_ID in NUMBER,
13 X_ULTI_SHIP_TO_REGION in NUMBER,
14 X_ULTI_SHIP_TO_ZIP_FROM in NUMBER,
15 X_ULTI_SHIP_TO_ZIP_TO in NUMBER,
16 X_ULTI_SHIP_TO_ZONE in NUMBER,
17 X_INCL_STAGED_DEL_FLAG in VARCHAR2,
18 X_INCL_DEL_ASG_TRIPS_FLAG in VARCHAR2,
19 X_CR_TRIP_TO_ULTM_SHIP_TO in VARCHAR2,
20 X_ROUTE_TRIPS_FLAG in VARCHAR2,
21 X_RATE_TRIPS_FLAG in VARCHAR2,
22 X_TRIP_NAME_PREFIX in VARCHAR2,
23 X_ORGANIZATION_ID in NUMBER,
24 X_CONSOL_GROUPING_RULE_ID in NUMBER,
25 X_CONSOL_SHIP_TO_LOCATION_ID in NUMBER,
26 X_DROPOFF_START_DAYS in NUMBER,
27 X_DELIVERY_NAME_TO in VARCHAR2,
28 X_PICKUP_START_DAYS in NUMBER,
29 X_PICKUP_END_DAYS in NUMBER,
30 X_CUSTOMER_ID in NUMBER,
31 X_FOB_CODE in VARCHAR2,
32 X_DELIVERY_NAME_FROM in VARCHAR2,
33 X_SHIP_TO_OVERIDE_FLAG in VARCHAR2,
34 X_DROPOFF_END_DAYS in NUMBER,
35 X_PR_BATCH_ID in NUMBER,
36 X_MAX_TRIP_WEIGHT in NUMBER,
37 X_MAX_TRIP_WEIGHT_UOM in VARCHAR2,
38 X_CREATION_DATE in DATE,
39 X_CREATED_BY in NUMBER,
40 X_LAST_UPDATE_DATE in DATE,
41 X_LAST_UPDATED_BY in NUMBER,
42 X_LAST_UPDATE_LOGIN in NUMBER
43 ) is
44 user_id NUMBER;
45 login_id NUMBER;
46 l_batch_id NUMBER;
47 CURSOR C IS SELECT rowid FROM WSH_CONSOL_BATCHES
48 WHERE batch_id = l_batch_id;
49 --
50 CURSOR NEXTID IS SELECT wsh_consol_batches_s.nextval FROM sys.dual;
51 begin
52 user_id := FND_GLOBAL.USER_ID;
53 login_id := FND_GLOBAL.LOGIN_ID;
54
55 IF (X_BATCH_ID is NULL) THEN
56 OPEN NEXTID;
57 FETCH NEXTID INTO l_batch_id;
58 CLOSE NEXTID;
59 END IF;
60 insert into WSH_CONSOL_BATCHES (
61 FREIGHT_TERMS_CODE,
62 CARRIER_ID,
63 MODE_OF_TRANSPORT,
64 SERVICE_LEVEL,
65 LOADING_SEQUENCE,
66 INTMED_SHIP_TO_LOCATION_ID,
67 ULTI_SHIP_TO_LOCATION_ID,
68 ULTI_SHIP_TO_REGION,
69 ULTI_SHIP_TO_ZIP_FROM,
70 ULTI_SHIP_TO_ZIP_TO,
71 ULTI_SHIP_TO_ZONE,
72 INCL_STAGED_DEL_FLAG,
73 INCL_DEL_ASG_TRIPS_FLAG,
74 CR_TRIP_TO_ULTM_SHIP_TO,
75 ROUTE_TRIPS_FLAG,
76 RATE_TRIPS_FLAG,
77 TRIP_NAME_PREFIX,
78 CREATION_DATE,
79 CREATED_BY,
80 LAST_UPDATE_DATE,
81 LAST_UPDATED_BY,
82 LAST_UPDATE_LOGIN,
83 BATCH_ID,
84 ORGANIZATION_ID,
85 CONSOL_GROUPING_RULE_ID,
86 CONSOL_SHIP_TO_LOCATION_ID,
87 DROPOFF_START_DAYS,
88 DELIVERY_NAME_TO,
89 PICKUP_START_DAYS,
90 PICKUP_END_DAYS,
91 CUSTOMER_ID,
92 FOB_CODE,
93 DELIVERY_NAME_FROM,
94 SHIP_TO_OVERIDE_FLAG,
95 DROPOFF_END_DAYS,
96 PR_BATCH_ID,
97 MAX_TRIP_WEIGHT,
98 MAX_TRIP_WEIGHT_UOM
99 ) values(
100 X_FREIGHT_TERMS_CODE,
101 X_CARRIER_ID,
102 X_MODE_OF_TRANSPORT,
103 X_SERVICE_LEVEL,
104 X_LOADING_SEQUENCE,
105 X_INTMED_SHIP_TO_LOCATION_ID,
106 X_ULTI_SHIP_TO_LOCATION_ID,
107 X_ULTI_SHIP_TO_REGION,
108 X_ULTI_SHIP_TO_ZIP_FROM,
109 X_ULTI_SHIP_TO_ZIP_TO,
110 X_ULTI_SHIP_TO_ZONE,
111 X_INCL_STAGED_DEL_FLAG,
112 X_INCL_DEL_ASG_TRIPS_FLAG,
113 X_CR_TRIP_TO_ULTM_SHIP_TO,
114 X_ROUTE_TRIPS_FLAG,
115 X_RATE_TRIPS_FLAG,
116 X_TRIP_NAME_PREFIX,
117 SYSDATE,
118 user_id,
119 SYSDATE,
120 user_id,
121 login_id,
122 l_batch_id,
123 X_ORGANIZATION_ID,
124 X_CONSOL_GROUPING_RULE_ID,
125 X_CONSOL_SHIP_TO_LOCATION_ID,
126 X_DROPOFF_START_DAYS,
127 X_DELIVERY_NAME_TO,
128 X_PICKUP_START_DAYS,
129 X_PICKUP_END_DAYS,
130 X_CUSTOMER_ID,
131 X_FOB_CODE,
132 X_DELIVERY_NAME_FROM,
133 X_SHIP_TO_OVERIDE_FLAG,
134 X_DROPOFF_END_DAYS,
135 X_PR_BATCH_ID,
136 X_MAX_TRIP_WEIGHT,
137 X_MAX_TRIP_WEIGHT_UOM);
138
139 x_batch_id := l_batch_id;
140 /*open c;
141 fetch c into X_ROWID;
142 if (c%notfound) then
143 close c;
144 raise no_data_found;
145 end if;
146 close c;*/
147
148 end INSERT_ROW;
149
150 /*procedure LOCK_ROW (
151 X_BATCH_ID in NUMBER,
152 X_FREIGHT_TERMS_CODE in VARCHAR2,
153 X_CARRIER_ID in NUMBER,
154 X_MODE_OF_TRANSPORT in VARCHAR2,
155 X_SERVICE_LEVEL in VARCHAR2,
156 X_LOADING_SEQUENCE in NUMBER,
157 X_INTMED_SHIP_TO_LOCATION_ID in NUMBER,
158 X_ULTI_SHIP_TO_LOCATION_ID in NUMBER,
159 X_ULTI_SHIP_TO_REGION in NUMBER,
160 X_ULTI_SHIP_TO_ZIP_FROM in NUMBER,
161 X_ULTI_SHIP_TO_ZIP_TO in NUMBER,
162 X_ULTI_SHIP_TO_ZONE in NUMBER,
163 X_INCL_STAGED_DEL_FLAG in VARCHAR2,
164 X_INCL_DEL_ASG_TRIPS_FLAG in VARCHAR2,
165 X_CR_TRIP_TO_ULTM_SHIP_TO in VARCHAR2,
166 X_ROUTE_TRIPS_FLAG in VARCHAR2,
167 X_RATE_TRIPS_FLAG in VARCHAR2,
168 X_TRIP_NAME_PREFIX in VARCHAR2,
169 X_ORGANIZATION_ID in NUMBER,
170 X_CONSOL_GROUPING_RULE_ID in NUMBER,
171 X_CONSOL_SHIP_TO_LOCATION_ID in NUMBER,
172 X_DROPOFF_START_DAYS in NUMBER,
173 X_DELIVERY_NAME_TO in VARCHAR2,
174 X_PICKUP_START_DAYS in NUMBER,
175 X_PICKUP_END_DAYS in NUMBER,
176 X_CUSTOMER_ID in NUMBER,
177 X_FOB_CODE in VARCHAR2,
178 X_DELIVERY_NAME_FROM in VARCHAR2,
179 X_SHIP_TO_OVERIDE_FLAG in VARCHAR2,
180 X_DROPOFF_END_DAYS in NUMBER,
181 X_PR_BATCH_ID in NUMBER,
182 X_MAX_TRIP_WEIGHT in NUMBER,
183 X_MAX_TRIP_WEIGHT_UOM in VARCHAR2
184 ) is
185 cursor c1 is select
186 FREIGHT_TERMS_CODE,
187 CARRIER_ID,
188 MODE_OF_TRANSPORT,
189 SERVICE_LEVEL,
190 LOADING_SEQUENCE,
191 INTMED_SHIP_TO_LOCATION_ID,
192 ULTI_SHIP_TO_LOCATION_ID,
193 ULTI_SHIP_TO_REGION,
194 ULTI_SHIP_TO_ZIP_FROM,
195 ULTI_SHIP_TO_ZIP_TO,
196 ULTI_SHIP_TO_ZONE,
197 INCL_STAGED_DEL_FLAG,
198 INCL_DEL_ASG_TRIPS_FLAG,
199 CR_TRIP_TO_ULTM_SHIP_TO,
200 ROUTE_TRIPS_FLAG,
201 RATE_TRIPS_FLAG,
202 TRIP_NAME_PREFIX,
203 ORGANIZATION_ID,
204 CONSOL_GROUPING_RULE_ID,
205 CONSOL_SHIP_TO_LOCATION_ID,
206 DROPOFF_START_DAYS,
207 DELIVERY_NAME_TO,
208 PICKUP_START_DAYS,
209 PICKUP_END_DAYS,
210 CUSTOMER_ID,
211 FOB_CODE,
212 DELIVERY_NAME_FROM,
213 SHIP_TO_OVERIDE_FLAG,
214 DROPOFF_END_DAYS,
215 PR_BATCH_ID,
216 BATCH_ID
217 from WSH_CONSOL_BATCHES
218 where BATCH_ID = X_BATCH_ID
219 for update of BATCH_ID nowait;
220 begin
221
222 OPEN C;
223 FETCH C INTO Recinfo;
224 --
225 if (C%NOTFOUND) then
226 --
227 CLOSE C;
228 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
229 APP_EXCEPTION.Raise_Exception;
230 --
231 end if;
232 --
233 IF C%ISOPEN THEN
234 CLOSE C;
235 END IF;
236
237 if ( ((recinfo.BATCH_ID = X_BATCH_ID)
238 AND ((recinfo.FREIGHT_TERMS_CODE = X_FREIGHT_TERMS_CODE)
239 OR ((recinfo.FREIGHT_TERMS_CODE is null) AND (X_FREIGHT_TERMS_CODE is null)))
240 AND ((recinfo.CARRIER_ID = X_CARRIER_ID)
241 OR ((recinfo.CARRIER_ID is null) AND (X_CARRIER_ID is null)))
242 AND ((recinfo.MODE_OF_TRANSPORT = X_MODE_OF_TRANSPORT)
243 OR ((recinfo.MODE_OF_TRANSPORT is null) AND (X_MODE_OF_TRANSPORT is null)))
244 AND ((recinfo.SERVICE_LEVEL = X_SERVICE_LEVEL)
245 OR ((recinfo.SERVICE_LEVEL is null) AND (X_SERVICE_LEVEL is null)))
246 AND ((recinfo.LOADING_SEQUENCE = X_LOADING_SEQUENCE)
247 OR ((recinfo.LOADING_SEQUENCE is null) AND (X_LOADING_SEQUENCE is null)))
248 AND ((recinfo.INTMED_SHIP_TO_LOCATION_ID = X_INTMED_SHIP_TO_LOCATION_ID)
249 OR ((recinfo.INTMED_SHIP_TO_LOCATION_ID is null) AND (X_INTMED_SHIP_TO_LOCATION_ID is null)))
250 AND ((recinfo.ULTI_SHIP_TO_LOCATION_ID = X_ULTI_SHIP_TO_LOCATION_ID)
251 OR ((recinfo.ULTI_SHIP_TO_LOCATION_ID is null) AND (X_ULTI_SHIP_TO_LOCATION_ID is null)))
252 AND ((recinfo.ULTI_SHIP_TO_REGION = X_ULTI_SHIP_TO_REGION)
253 OR ((recinfo.ULTI_SHIP_TO_REGION is null) AND (X_ULTI_SHIP_TO_REGION is null)))
254 AND ((recinfo.ULTI_SHIP_TO_ZIP_FROM = X_ULTI_SHIP_TO_ZIP_FROM)
255 OR ((recinfo.ULTI_SHIP_TO_ZIP_FROM is null) AND (X_ULTI_SHIP_TO_ZIP_FROM is null)))
256 AND ((recinfo.ULTI_SHIP_TO_ZIP_TO = X_ULTI_SHIP_TO_ZIP_TO)
257 OR ((recinfo.ULTI_SHIP_TO_ZIP_TO is null) AND (X_ULTI_SHIP_TO_ZIP_TO is null)))
258 AND ((recinfo.ULTI_SHIP_TO_ZONE = X_ULTI_SHIP_TO_ZONE)
259 OR ((recinfo.ULTI_SHIP_TO_ZONE is null) AND (X_ULTI_SHIP_TO_ZONE is null)))
260 AND ((recinfo.INCL_STAGED_DEL_FLAG = X_INCL_STAGED_DEL_FLAG)
261 OR ((recinfo.INCL_STAGED_DEL_FLAG is null) AND (X_INCL_STAGED_DEL_FLAG is null)))
262 AND ((recinfo.INCL_DEL_ASG_TRIPS_FLAG = X_INCL_DEL_ASG_TRIPS_FLAG)
263 OR ((recinfo.INCL_DEL_ASG_TRIPS_FLAG is null) AND (X_INCL_DEL_ASG_TRIPS_FLAG is null)))
264 AND ((recinfo.CR_TRIP_TO_ULTM_SHIP_TO = X_CR_TRIP_TO_ULTM_SHIP_TO)
265 OR ((recinfo.CR_TRIP_TO_ULTM_SHIP_TO is null) AND (X_CR_TRIP_TO_ULTM_SHIP_TO is null)))
266 AND ((recinfo.ROUTE_TRIPS_FLAG = X_ROUTE_TRIPS_FLAG)
267 OR ((recinfo.ROUTE_TRIPS_FLAG is null) AND (X_ROUTE_TRIPS_FLAG is null)))
268 AND ((recinfo.RATE_TRIPS_FLAG = X_RATE_TRIPS_FLAG)
269 OR ((recinfo.RATE_TRIPS_FLAG is null) AND (X_RATE_TRIPS_FLAG is null)))
270 AND ((recinfo.TRIP_NAME_PREFIX = X_TRIP_NAME_PREFIX)
271 OR ((recinfo.TRIP_NAME_PREFIX is null) AND (X_TRIP_NAME_PREFIX is null)))
272 AND ((recinfo.ORGANIZATION_ID = X_ORGANIZATION_ID)
273 OR ((recinfo.ORGANIZATION_ID is null) AND (X_ORGANIZATION_ID is null)))
274 AND ((recinfo.CONSOL_GROUPING_RULE_ID = X_CONSOL_GROUPING_RULE_ID)
275 OR ((recinfo.CONSOL_GROUPING_RULE_ID is null) AND (X_CONSOL_GROUPING_RULE_ID is null)))
276 AND ((recinfo.CONSOL_SHIP_TO_LOCATION_ID = X_CONSOL_SHIP_TO_LOCATION_ID)
277 OR ((recinfo.CONSOL_SHIP_TO_LOCATION_ID is null) AND (X_CONSOL_SHIP_TO_LOCATION_ID is null)))
278 AND ((recinfo.DROPOFF_START_DAYS = X_DROPOFF_START_DAYS)
279 OR ((recinfo.DROPOFF_START_DAYS is null) AND (X_DROPOFF_START_DAYS is null)))
280 AND ((recinfo.DELIVERY_NAME_TO = X_DELIVERY_NAME_TO)
281 OR ((recinfo.DELIVERY_NAME_TO is null) AND (X_DELIVERY_NAME_TO is null)))
282 AND ((recinfo.PICKUP_START_DAYS = X_PICKUP_START_DAYS)
283 OR ((recinfo.PICKUP_START_DAYS is null) AND (X_PICKUP_START_DAYS is null)))
284 AND ((recinfo.PICKUP_END_DAYS = X_PICKUP_END_DAYS)
285 OR ((recinfo.PICKUP_END_DAYS is null) AND (X_PICKUP_END_DAYS is null)))
286 AND ((recinfo.CUSTOMER_ID = X_CUSTOMER_ID)
287 OR ((recinfo.CUSTOMER_ID is null) AND (X_CUSTOMER_ID is null)))
288 AND ((recinfo.FOB_CODE = X_FOB_CODE)
289 OR ((recinfo.FOB_CODE is null) AND (X_FOB_CODE is null)))
290 AND ((recinfo.DELIVERY_NAME_FROM = X_DELIVERY_NAME_FROM)
291 OR ((recinfo.DELIVERY_NAME_FROM is null) AND (X_DELIVERY_NAME_FROM is null)))
292 AND ((recinfo.SHIP_TO_OVERIDE_FLAG = X_SHIP_TO_OVERIDE_FLAG)
293 OR ((recinfo.SHIP_TO_OVERIDE_FLAG is null) AND (X_SHIP_TO_OVERIDE_FLAG is null)))
294 AND ((recinfo.DROPOFF_END_DAYS = X_DROPOFF_END_DAYS)
295 OR ((recinfo.DROPOFF_END_DAYS is null) AND (X_DROPOFF_END_DAYS is null)))
296 AND ((recinfo.PR_BATCH_ID = X_PR_BATCH_ID)
297 OR ((recinfo.PR_BATCH_ID is null) AND (X_PR_BATCH_ID is null)))
298 AND ((recinfo.MAX_TRIP_WEIGHT = X_MAX_TRIP_WEIGHT)
299 OR ((recinfo.MAX_TRIP_WEIGHT is null) AND (X_MAX_TRIP_WEIGHT is null)))
300 AND ((recinfo.MAX_TRIP_WEIGHT_UOM = X_MAX_TRIP_WEIGHT_UOM)
301 OR ((recinfo.MAX_TRIP_WEIGHT_UOM is null) AND (X_MAX_TRIP_WEIGHT_UOM is null)))
302 ) then
303 null;
304 else
305 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
306 app_exception.raise_exception;
307 end if;
308 return;
309 end LOCK_ROW;*/
310 END WSH_CONSOL_BATCHES_PKG;
[Home] [Help]