[Home] [Help]
PACKAGE BODY: APPS.ASO_SHIPMENTS_PKG
Source
1 PACKAGE BODY ASO_SHIPMENTS_PKG as
2 /* $Header: asotshpb.pls 120.0 2005/05/31 11:54:05 appldev noship $ */
3 -- Start of Comments
4 -- Package name : ASO_SHIPMENTS_PKG
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):= 'ASO_SHIPMENTS_PKG';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'asotshpb.pls';
13
14 PROCEDURE Insert_Row(
15 px_SHIPMENT_ID IN OUT NOCOPY NUMBER,
16 p_CREATION_DATE DATE,
17 p_CREATED_BY NUMBER,
18 p_LAST_UPDATE_DATE DATE,
19 p_LAST_UPDATED_BY NUMBER,
20 p_LAST_UPDATE_LOGIN NUMBER,
21 p_REQUEST_ID NUMBER,
22 p_PROGRAM_APPLICATION_ID NUMBER,
23 p_PROGRAM_ID NUMBER,
24 p_PROGRAM_UPDATE_DATE DATE,
25 p_QUOTE_HEADER_ID NUMBER,
26 p_QUOTE_LINE_ID NUMBER,
27 p_PROMISE_DATE DATE,
28 p_REQUEST_DATE DATE,
29 p_SCHEDULE_SHIP_DATE DATE,
30 p_SHIP_TO_PARTY_SITE_ID NUMBER,
31 p_SHIP_TO_PARTY_ID NUMBER,
32 p_SHIP_TO_CUST_ACCOUNT_ID NUMBER,
33 p_SHIP_PARTIAL_FLAG VARCHAR2,
34 p_SHIP_SET_ID NUMBER,
35 p_SHIP_METHOD_CODE VARCHAR2,
36 p_FREIGHT_TERMS_CODE VARCHAR2,
37 p_FREIGHT_CARRIER_CODE VARCHAR2,
38 p_FOB_CODE VARCHAR2,
39 p_SHIPPING_INSTRUCTIONS VARCHAR2,
40 p_PACKING_INSTRUCTIONS VARCHAR2,
41 p_QUANTITY NUMBER,
42 p_RESERVED_QUANTITY VARCHAR2,
43 p_RESERVATION_ID NUMBER,
44 p_ORDER_LINE_ID NUMBER,
45 p_ATTRIBUTE_CATEGORY VARCHAR2,
46 p_ATTRIBUTE1 VARCHAR2,
47 p_ATTRIBUTE2 VARCHAR2,
48 p_ATTRIBUTE3 VARCHAR2,
49 p_ATTRIBUTE4 VARCHAR2,
50 p_ATTRIBUTE5 VARCHAR2,
51 p_ATTRIBUTE6 VARCHAR2,
52 p_ATTRIBUTE7 VARCHAR2,
53 p_ATTRIBUTE8 VARCHAR2,
54 p_ATTRIBUTE9 VARCHAR2,
55 p_ATTRIBUTE10 VARCHAR2,
56 p_ATTRIBUTE11 VARCHAR2,
57 p_ATTRIBUTE12 VARCHAR2,
58 p_ATTRIBUTE13 VARCHAR2,
59 p_ATTRIBUTE14 VARCHAR2,
60 p_ATTRIBUTE15 VARCHAR2,
61 p_ATTRIBUTE16 VARCHAR2,
62 p_ATTRIBUTE17 VARCHAR2,
63 p_ATTRIBUTE18 VARCHAR2,
64 p_ATTRIBUTE19 VARCHAR2,
65 p_ATTRIBUTE20 VARCHAR2,
66 p_SHIPMENT_PRIORITY_CODE VARCHAR2,
67 p_SHIP_QUOTE_PRICE NUMBER,
68 p_SHIP_FROM_ORG_ID NUMBER,
69 p_SHIP_TO_CUST_PARTY_ID NUMBER,
70 p_SHIP_METHOD_CODE_FROM VARCHAR2,
71 p_FREIGHT_TERMS_CODE_FROM VARCHAR2,
72 p_OBJECT_VERSION_NUMBER NUMBER,
73 p_REQUEST_DATE_TYPE VARCHAR2,
74 p_DEMAND_CLASS_CODE VARCHAR2
75 )
76
77 IS
78 CURSOR C2 IS SELECT ASO_SHIPMENTS_S.nextval FROM sys.dual;
79 BEGIN
80 If (px_SHIPMENT_ID IS NULL) OR (px_SHIPMENT_ID = FND_API.G_MISS_NUM) then
81 OPEN C2;
82 FETCH C2 INTO px_SHIPMENT_ID;
83 CLOSE C2;
84 End If;
85 INSERT INTO ASO_SHIPMENTS(
86 SHIPMENT_ID,
87 CREATION_DATE,
88 CREATED_BY,
89 LAST_UPDATE_DATE,
90 LAST_UPDATED_BY,
91 LAST_UPDATE_LOGIN,
92 REQUEST_ID,
93 PROGRAM_APPLICATION_ID,
94 PROGRAM_ID,
95 PROGRAM_UPDATE_DATE,
96 QUOTE_HEADER_ID,
97 QUOTE_LINE_ID,
98 PROMISE_DATE,
99 REQUEST_DATE,
100 SCHEDULE_SHIP_DATE,
101 SHIP_TO_PARTY_SITE_ID,
102 SHIP_TO_PARTY_ID,
103 SHIP_TO_CUST_ACCOUNT_ID,
104 SHIP_PARTIAL_FLAG,
105 SHIP_SET_ID,
106 SHIP_METHOD_CODE,
107 FREIGHT_TERMS_CODE,
108 FREIGHT_CARRIER_CODE,
109 FOB_CODE,
110 SHIPPING_INSTRUCTIONS,
111 PACKING_INSTRUCTIONS,
112 QUANTITY,
113 RESERVED_QUANTITY,
114 RESERVATION_ID,
115 ORDER_LINE_ID,
116 ATTRIBUTE_CATEGORY,
117 ATTRIBUTE1,
118 ATTRIBUTE2,
119 ATTRIBUTE3,
120 ATTRIBUTE4,
121 ATTRIBUTE5,
122 ATTRIBUTE6,
123 ATTRIBUTE7,
124 ATTRIBUTE8,
125 ATTRIBUTE9,
126 ATTRIBUTE10,
127 ATTRIBUTE11,
128 ATTRIBUTE12,
129 ATTRIBUTE13,
130 ATTRIBUTE14,
131 ATTRIBUTE15,
132 ATTRIBUTE16,
133 ATTRIBUTE17,
134 ATTRIBUTE18,
135 ATTRIBUTE19,
136 ATTRIBUTE20,
137 SHIPMENT_PRIORITY_CODE,
138 SHIP_QUOTE_PRICE,
139 SHIP_FROM_ORG_ID,
140 SHIP_TO_CUST_PARTY_ID,
141 SHIP_METHOD_CODE_FROM,
142 FREIGHT_TERMS_CODE_FROM,
143 OBJECT_VERSION_NUMBER,
144 REQUEST_DATE_TYPE,
145 DEMAND_CLASS_CODE
146 ) VALUES (
147 px_SHIPMENT_ID,
148 ASO_UTILITY_PVT.decode( p_CREATION_DATE, FND_API.G_MISS_DATE, NULL, p_CREATION_DATE),
149 decode( p_CREATED_BY, FND_API.G_MISS_NUM, NULL, p_CREATED_BY),
150 ASO_UTILITY_PVT.decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, NULL, p_LAST_UPDATE_DATE),
151 decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATED_BY),
152 decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATE_LOGIN),
153 decode( p_REQUEST_ID, FND_API.G_MISS_NUM, NULL, p_REQUEST_ID),
154 decode( p_PROGRAM_APPLICATION_ID, FND_API.G_MISS_NUM, NULL, p_PROGRAM_APPLICATION_ID),
155 decode( p_PROGRAM_ID, FND_API.G_MISS_NUM, NULL, p_PROGRAM_ID),
156 ASO_UTILITY_PVT.decode( p_PROGRAM_UPDATE_DATE, FND_API.G_MISS_DATE, NULL, p_PROGRAM_UPDATE_DATE),
157 decode( p_QUOTE_HEADER_ID, FND_API.G_MISS_NUM, NULL, p_QUOTE_HEADER_ID),
158 decode( p_QUOTE_LINE_ID, FND_API.G_MISS_NUM, NULL, p_QUOTE_LINE_ID),
159 ASO_UTILITY_PVT.decode( p_PROMISE_DATE, FND_API.G_MISS_DATE, NULL, p_PROMISE_DATE),
160 ASO_UTILITY_PVT.decode( p_REQUEST_DATE, FND_API.G_MISS_DATE, NULL, p_REQUEST_DATE),
161 ASO_UTILITY_PVT.decode( p_SCHEDULE_SHIP_DATE, FND_API.G_MISS_DATE, NULL, p_SCHEDULE_SHIP_DATE),
162 decode( p_SHIP_TO_PARTY_SITE_ID, FND_API.G_MISS_NUM, NULL, p_SHIP_TO_PARTY_SITE_ID),
163 decode( p_SHIP_TO_PARTY_ID, FND_API.G_MISS_NUM, NULL, p_SHIP_TO_PARTY_ID),
164 decode( p_SHIP_TO_CUST_ACCOUNT_ID, FND_API.G_MISS_NUM, NULL, p_SHIP_TO_CUST_ACCOUNT_ID),
165 decode( p_SHIP_PARTIAL_FLAG, FND_API.G_MISS_CHAR, NULL, p_SHIP_PARTIAL_FLAG),
166 decode( p_SHIP_SET_ID, FND_API.G_MISS_NUM, NULL, p_SHIP_SET_ID),
167 decode( p_SHIP_METHOD_CODE, FND_API.G_MISS_CHAR, NULL, p_SHIP_METHOD_CODE),
168 decode( p_FREIGHT_TERMS_CODE, FND_API.G_MISS_CHAR, NULL, p_FREIGHT_TERMS_CODE),
169 decode( p_FREIGHT_CARRIER_CODE, FND_API.G_MISS_CHAR, NULL, p_FREIGHT_CARRIER_CODE),
170 decode( p_FOB_CODE, FND_API.G_MISS_CHAR, NULL, p_FOB_CODE),
171 decode( p_SHIPPING_INSTRUCTIONS, FND_API.G_MISS_CHAR, NULL, p_SHIPPING_INSTRUCTIONS),
172 decode( p_PACKING_INSTRUCTIONS, FND_API.G_MISS_CHAR, NULL, p_PACKING_INSTRUCTIONS),
173 decode( p_QUANTITY, FND_API.G_MISS_NUM, NULL, p_QUANTITY),
174 decode( p_RESERVED_QUANTITY, FND_API.G_MISS_CHAR, NULL, p_RESERVED_QUANTITY),
175 decode( p_RESERVATION_ID, FND_API.G_MISS_NUM, NULL, p_RESERVATION_ID),
176 decode( p_ORDER_LINE_ID, FND_API.G_MISS_NUM, NULL, p_ORDER_LINE_ID),
177 decode( p_ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE_CATEGORY),
178 decode( p_ATTRIBUTE1, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE1),
179 decode( p_ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE2),
180 decode( p_ATTRIBUTE3, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE3),
181 decode( p_ATTRIBUTE4, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE4),
182 decode( p_ATTRIBUTE5, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE5),
183 decode( p_ATTRIBUTE6, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE6),
184 decode( p_ATTRIBUTE7, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE7),
185 decode( p_ATTRIBUTE8, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE8),
186 decode( p_ATTRIBUTE9, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE9),
187 decode( p_ATTRIBUTE10, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE10),
188 decode( p_ATTRIBUTE11, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE11),
189 decode( p_ATTRIBUTE12, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE12),
190 decode( p_ATTRIBUTE13, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE13),
191 decode( p_ATTRIBUTE14, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE14),
192 decode( p_ATTRIBUTE15, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE15),
193 decode( p_ATTRIBUTE16, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE16),
194 decode( p_ATTRIBUTE17, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE17),
195 decode( p_ATTRIBUTE18, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE18),
196 decode( p_ATTRIBUTE19, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE19),
197 decode( p_ATTRIBUTE20, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE20),
198 decode( p_SHIPMENT_PRIORITY_CODE, FND_API.G_MISS_CHAR, NULL, p_SHIPMENT_PRIORITY_CODE),
199 decode( p_SHIP_QUOTE_PRICE, FND_API.G_MISS_NUM, NULL, p_SHIP_QUOTE_PRICE),
200 decode( p_SHIP_FROM_ORG_ID, FND_API.G_MISS_NUM, NULL, p_SHIP_FROM_ORG_ID),
201 decode( p_SHIP_TO_CUST_PARTY_ID, FND_API.G_MISS_NUM, NULL, p_SHIP_TO_CUST_PARTY_ID),
202 decode( p_SHIP_METHOD_CODE_FROM, FND_API.G_MISS_CHAR, NULL, p_SHIP_METHOD_CODE_FROM),
203 decode( p_FREIGHT_TERMS_CODE_FROM, FND_API.G_MISS_CHAR, NULL, p_FREIGHT_TERMS_CODE_FROM),
204 decode ( p_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM,1,NULL,1, p_OBJECT_VERSION_NUMBER),
205 decode ( p_REQUEST_DATE_TYPE, FND_API.G_MISS_CHAR,NULL,p_REQUEST_DATE_TYPE),
206 decode ( p_DEMAND_CLASS_CODE, FND_API.G_MISS_CHAR,NULL, p_DEMAND_CLASS_CODE)
207 );
208 End Insert_Row;
209
210 PROCEDURE Update_Row(
211 p_SHIPMENT_ID NUMBER,
212 p_CREATION_DATE DATE,
213 p_CREATED_BY NUMBER,
214 p_LAST_UPDATE_DATE DATE,
215 p_LAST_UPDATED_BY NUMBER,
216 p_LAST_UPDATE_LOGIN NUMBER,
217 p_REQUEST_ID NUMBER,
218 p_PROGRAM_APPLICATION_ID NUMBER,
219 p_PROGRAM_ID NUMBER,
220 p_PROGRAM_UPDATE_DATE DATE,
221 p_QUOTE_HEADER_ID NUMBER,
222 p_QUOTE_LINE_ID NUMBER,
223 p_PROMISE_DATE DATE,
224 p_REQUEST_DATE DATE,
225 p_SCHEDULE_SHIP_DATE DATE,
226 p_SHIP_TO_PARTY_SITE_ID NUMBER,
227 p_SHIP_TO_PARTY_ID NUMBER,
228 p_SHIP_TO_CUST_ACCOUNT_ID NUMBER,
229 p_SHIP_PARTIAL_FLAG VARCHAR2,
230 p_SHIP_SET_ID NUMBER,
231 p_SHIP_METHOD_CODE VARCHAR2,
232 p_FREIGHT_TERMS_CODE VARCHAR2,
233 p_FREIGHT_CARRIER_CODE VARCHAR2,
234 p_FOB_CODE VARCHAR2,
235 p_SHIPPING_INSTRUCTIONS VARCHAR2,
236 p_PACKING_INSTRUCTIONS VARCHAR2,
237 p_QUANTITY NUMBER,
238 p_RESERVED_QUANTITY VARCHAR2,
239 p_RESERVATION_ID NUMBER,
240 p_ORDER_LINE_ID NUMBER,
241 p_ATTRIBUTE_CATEGORY VARCHAR2,
242 p_ATTRIBUTE1 VARCHAR2,
243 p_ATTRIBUTE2 VARCHAR2,
244 p_ATTRIBUTE3 VARCHAR2,
245 p_ATTRIBUTE4 VARCHAR2,
246 p_ATTRIBUTE5 VARCHAR2,
247 p_ATTRIBUTE6 VARCHAR2,
248 p_ATTRIBUTE7 VARCHAR2,
249 p_ATTRIBUTE8 VARCHAR2,
250 p_ATTRIBUTE9 VARCHAR2,
251 p_ATTRIBUTE10 VARCHAR2,
252 p_ATTRIBUTE11 VARCHAR2,
253 p_ATTRIBUTE12 VARCHAR2,
254 p_ATTRIBUTE13 VARCHAR2,
255 p_ATTRIBUTE14 VARCHAR2,
256 p_ATTRIBUTE15 VARCHAR2,
257 p_ATTRIBUTE16 VARCHAR2,
258 p_ATTRIBUTE17 VARCHAR2,
259 p_ATTRIBUTE18 VARCHAR2,
260 p_ATTRIBUTE19 VARCHAR2,
261 p_ATTRIBUTE20 VARCHAR2,
262 p_SHIPMENT_PRIORITY_CODE VARCHAR2,
263 p_SHIP_QUOTE_PRICE NUMBER,
264 p_SHIP_FROM_ORG_ID NUMBER,
265 p_SHIP_TO_CUST_PARTY_ID NUMBER,
266 p_SHIP_METHOD_CODE_FROM VARCHAR2,
267 p_FREIGHT_TERMS_CODE_FROM VARCHAR2,
268 p_OBJECT_VERSION_NUMBER NUMBER,
269 p_REQUEST_DATE_TYPE VARCHAR2,
270 p_DEMAND_CLASS_CODE VARCHAR2
271 )
272
273 IS
274 BEGIN
275 Update ASO_SHIPMENTS
276 SET
277 /* CREATION_DATE = ASO_UTILITY_PVT.decode( p_CREATION_DATE, FND_API.G_MISS_DATE, CREATION_DATE, p_CREATION_DATE),
278 CREATED_BY = decode( p_CREATED_BY, FND_API.G_MISS_NUM, CREATED_BY, p_CREATED_BY),*/
279 LAST_UPDATE_DATE = ASO_UTILITY_PVT.decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, LAST_UPDATE_DATE, p_LAST_UPDATE_DATE),
280 LAST_UPDATED_BY = decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, LAST_UPDATED_BY, p_LAST_UPDATED_BY),
281 LAST_UPDATE_LOGIN = decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, LAST_UPDATE_LOGIN, p_LAST_UPDATE_LOGIN),
282 REQUEST_ID = decode( p_REQUEST_ID, FND_API.G_MISS_NUM, REQUEST_ID, p_REQUEST_ID),
283 PROGRAM_APPLICATION_ID = decode( p_PROGRAM_APPLICATION_ID, FND_API.G_MISS_NUM, PROGRAM_APPLICATION_ID, p_PROGRAM_APPLICATION_ID),
284 PROGRAM_ID = decode( p_PROGRAM_ID, FND_API.G_MISS_NUM, PROGRAM_ID, p_PROGRAM_ID),
285 PROGRAM_UPDATE_DATE = ASO_UTILITY_PVT.decode( p_PROGRAM_UPDATE_DATE, FND_API.G_MISS_DATE, PROGRAM_UPDATE_DATE, p_PROGRAM_UPDATE_DATE),
286 QUOTE_HEADER_ID = decode( p_QUOTE_HEADER_ID, FND_API.G_MISS_NUM, QUOTE_HEADER_ID, p_QUOTE_HEADER_ID),
287 QUOTE_LINE_ID = decode( p_QUOTE_LINE_ID, FND_API.G_MISS_NUM, QUOTE_LINE_ID, p_QUOTE_LINE_ID),
291 SHIP_TO_PARTY_SITE_ID = decode( p_SHIP_TO_PARTY_SITE_ID, FND_API.G_MISS_NUM, SHIP_TO_PARTY_SITE_ID, p_SHIP_TO_PARTY_SITE_ID),
288 PROMISE_DATE = ASO_UTILITY_PVT.decode( p_PROMISE_DATE, FND_API.G_MISS_DATE, PROMISE_DATE, p_PROMISE_DATE),
289 REQUEST_DATE = ASO_UTILITY_PVT.decode( p_REQUEST_DATE, FND_API.G_MISS_DATE, REQUEST_DATE, p_REQUEST_DATE),
290 SCHEDULE_SHIP_DATE = ASO_UTILITY_PVT.decode( p_SCHEDULE_SHIP_DATE, FND_API.G_MISS_DATE, SCHEDULE_SHIP_DATE, p_SCHEDULE_SHIP_DATE),
292 SHIP_TO_PARTY_ID = decode( p_SHIP_TO_PARTY_ID, FND_API.G_MISS_NUM, SHIP_TO_PARTY_ID, p_SHIP_TO_PARTY_ID),
293 SHIP_TO_CUST_ACCOUNT_ID = decode( p_SHIP_TO_CUST_ACCOUNT_ID, FND_API.G_MISS_NUM, SHIP_TO_CUST_ACCOUNT_ID, p_SHIP_TO_CUST_ACCOUNT_ID),
294 SHIP_PARTIAL_FLAG = decode( p_SHIP_PARTIAL_FLAG, FND_API.G_MISS_CHAR, SHIP_PARTIAL_FLAG, p_SHIP_PARTIAL_FLAG),
295 SHIP_SET_ID = decode( p_SHIP_SET_ID, FND_API.G_MISS_NUM, SHIP_SET_ID, p_SHIP_SET_ID),
296 SHIP_METHOD_CODE = decode( p_SHIP_METHOD_CODE, FND_API.G_MISS_CHAR, SHIP_METHOD_CODE, p_SHIP_METHOD_CODE),
297 FREIGHT_TERMS_CODE = decode( p_FREIGHT_TERMS_CODE, FND_API.G_MISS_CHAR, FREIGHT_TERMS_CODE, p_FREIGHT_TERMS_CODE),
298 FREIGHT_CARRIER_CODE = decode( p_FREIGHT_CARRIER_CODE, FND_API.G_MISS_CHAR, FREIGHT_CARRIER_CODE, p_FREIGHT_CARRIER_CODE),
299 FOB_CODE = decode( p_FOB_CODE, FND_API.G_MISS_CHAR, FOB_CODE, p_FOB_CODE),
300 SHIPPING_INSTRUCTIONS = decode( p_SHIPPING_INSTRUCTIONS, FND_API.G_MISS_CHAR, SHIPPING_INSTRUCTIONS, p_SHIPPING_INSTRUCTIONS),
301 PACKING_INSTRUCTIONS = decode( p_PACKING_INSTRUCTIONS, FND_API.G_MISS_CHAR, PACKING_INSTRUCTIONS, p_PACKING_INSTRUCTIONS),
302 QUANTITY = decode( p_QUANTITY, FND_API.G_MISS_NUM, QUANTITY, p_QUANTITY),
303 RESERVED_QUANTITY = decode( p_RESERVED_QUANTITY, FND_API.G_MISS_CHAR, RESERVED_QUANTITY, p_RESERVED_QUANTITY),
304 RESERVATION_ID = decode( p_RESERVATION_ID, FND_API.G_MISS_NUM, RESERVATION_ID, p_RESERVATION_ID),
305 ORDER_LINE_ID = decode( p_ORDER_LINE_ID, FND_API.G_MISS_NUM, ORDER_LINE_ID, p_ORDER_LINE_ID),
306 ATTRIBUTE_CATEGORY = decode( p_ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR, ATTRIBUTE_CATEGORY, p_ATTRIBUTE_CATEGORY),
307 ATTRIBUTE1 = decode( p_ATTRIBUTE1, FND_API.G_MISS_CHAR, ATTRIBUTE1, p_ATTRIBUTE1),
308 ATTRIBUTE2 = decode( p_ATTRIBUTE2, FND_API.G_MISS_CHAR, ATTRIBUTE2, p_ATTRIBUTE2),
309 ATTRIBUTE3 = decode( p_ATTRIBUTE3, FND_API.G_MISS_CHAR, ATTRIBUTE3, p_ATTRIBUTE3),
310 ATTRIBUTE4 = decode( p_ATTRIBUTE4, FND_API.G_MISS_CHAR, ATTRIBUTE4, p_ATTRIBUTE4),
311 ATTRIBUTE5 = decode( p_ATTRIBUTE5, FND_API.G_MISS_CHAR, ATTRIBUTE5, p_ATTRIBUTE5),
312 ATTRIBUTE6 = decode( p_ATTRIBUTE6, FND_API.G_MISS_CHAR, ATTRIBUTE6, p_ATTRIBUTE6),
313 ATTRIBUTE7 = decode( p_ATTRIBUTE7, FND_API.G_MISS_CHAR, ATTRIBUTE7, p_ATTRIBUTE7),
314 ATTRIBUTE8 = decode( p_ATTRIBUTE8, FND_API.G_MISS_CHAR, ATTRIBUTE8, p_ATTRIBUTE8),
315 ATTRIBUTE9 = decode( p_ATTRIBUTE9, FND_API.G_MISS_CHAR, ATTRIBUTE9, p_ATTRIBUTE9),
316 ATTRIBUTE10 = decode( p_ATTRIBUTE10, FND_API.G_MISS_CHAR, ATTRIBUTE10, p_ATTRIBUTE10),
317 ATTRIBUTE11 = decode( p_ATTRIBUTE11, FND_API.G_MISS_CHAR, ATTRIBUTE11, p_ATTRIBUTE11),
318 ATTRIBUTE12 = decode( p_ATTRIBUTE12, FND_API.G_MISS_CHAR, ATTRIBUTE12, p_ATTRIBUTE12),
319 ATTRIBUTE13 = decode( p_ATTRIBUTE13, FND_API.G_MISS_CHAR, ATTRIBUTE13, p_ATTRIBUTE13),
320 ATTRIBUTE14 = decode( p_ATTRIBUTE14, FND_API.G_MISS_CHAR, ATTRIBUTE14, p_ATTRIBUTE14),
321 ATTRIBUTE15 = decode( p_ATTRIBUTE15, FND_API.G_MISS_CHAR, ATTRIBUTE15, p_ATTRIBUTE15),
322 ATTRIBUTE16 = decode( p_ATTRIBUTE16, FND_API.G_MISS_CHAR, ATTRIBUTE16, p_ATTRIBUTE16),
323 ATTRIBUTE17 = decode( p_ATTRIBUTE17, FND_API.G_MISS_CHAR, ATTRIBUTE17, p_ATTRIBUTE17),
324 ATTRIBUTE18 = decode( p_ATTRIBUTE18, FND_API.G_MISS_CHAR, ATTRIBUTE18, p_ATTRIBUTE18),
325 ATTRIBUTE19 = decode( p_ATTRIBUTE19, FND_API.G_MISS_CHAR, ATTRIBUTE19, p_ATTRIBUTE19),
326 ATTRIBUTE20 = decode( p_ATTRIBUTE20, FND_API.G_MISS_CHAR, ATTRIBUTE20, p_ATTRIBUTE20),
327 SHIPMENT_PRIORITY_CODE = decode( p_SHIPMENT_PRIORITY_CODE, FND_API.G_MISS_CHAR, SHIPMENT_PRIORITY_CODE, p_SHIPMENT_PRIORITY_CODE),
328 SHIP_QUOTE_PRICE = decode( p_SHIP_QUOTE_PRICE, FND_API.G_MISS_NUM, SHIP_QUOTE_PRICE, p_SHIP_QUOTE_PRICE),
329 SHIP_FROM_ORG_ID = decode( p_SHIP_FROM_ORG_ID, FND_API.G_MISS_NUM, SHIP_FROM_ORG_ID, p_SHIP_FROM_ORG_ID),
330 SHIP_TO_CUST_PARTY_ID = decode( p_SHIP_TO_CUST_PARTY_ID, FND_API.G_MISS_NUM, SHIP_TO_CUST_PARTY_ID, p_SHIP_TO_CUST_PARTY_ID),
331 SHIP_METHOD_CODE_FROM = decode( p_SHIP_METHOD_CODE_FROM, FND_API.G_MISS_CHAR, SHIP_METHOD_CODE_FROM, p_SHIP_METHOD_CODE_FROM),
332 FREIGHT_TERMS_CODE_FROM = decode( p_FREIGHT_TERMS_CODE_FROM, FND_API.G_MISS_CHAR, FREIGHT_TERMS_CODE_FROM, p_FREIGHT_TERMS_CODE_FROM),
333 OBJECT_VERSION_NUMBER = decode( p_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, nvl(OBJECT_VERSION_NUMBER,0)+1, nvl(p_OBJECT_VERSION_NUMBER, nvl(OBJECT_VERSION_NUMBER,0))+1),
334
335 REQUEST_DATE_TYPE = decode( p_REQUEST_DATE_TYPE, FND_API.G_MISS_CHAR, REQUEST_DATE_TYPE, p_REQUEST_DATE_TYPE),
336 DEMAND_CLASS_CODE = decode( p_DEMAND_CLASS_CODE, FND_API.G_MISS_CHAR, DEMAND_CLASS_CODE, p_DEMAND_CLASS_CODE)
337
338 where SHIPMENT_ID = p_SHIPMENT_ID;
339
340 If (SQL%NOTFOUND) then
341 RAISE NO_DATA_FOUND;
342 End If;
343 END Update_Row;
344
345 PROCEDURE Delete_Row(
346 p_SHIPMENT_ID NUMBER)
347 IS
348 BEGIN
349 DELETE FROM ASO_SHIPMENTS
350 WHERE SHIPMENT_ID = p_SHIPMENT_ID;
351 If (SQL%NOTFOUND) then
352 RAISE NO_DATA_FOUND;
356 PROCEDURE Lock_Row(
353 End If;
354 END Delete_Row;
355
357 --p_OBJECT_VERSION_NUMBER NUMBER,
358 p_SHIPMENT_ID NUMBER,
359 p_CREATION_DATE DATE,
360 p_CREATED_BY NUMBER,
361 p_LAST_UPDATE_DATE DATE,
362 p_LAST_UPDATED_BY NUMBER,
363 p_LAST_UPDATE_LOGIN NUMBER,
364 p_REQUEST_ID NUMBER,
365 p_PROGRAM_APPLICATION_ID NUMBER,
366 p_PROGRAM_ID NUMBER,
367 p_PROGRAM_UPDATE_DATE DATE,
368 p_QUOTE_HEADER_ID NUMBER,
369 p_QUOTE_LINE_ID NUMBER,
370 p_PROMISE_DATE DATE,
371 p_REQUEST_DATE DATE,
372 p_SCHEDULE_SHIP_DATE DATE,
373 p_SHIP_TO_PARTY_SITE_ID NUMBER,
374 p_SHIP_TO_PARTY_ID NUMBER,
375 p_SHIP_PARTIAL_FLAG VARCHAR2,
376 p_SHIP_SET_ID NUMBER,
377 p_SHIP_METHOD_CODE VARCHAR2,
378 p_FREIGHT_TERMS_CODE VARCHAR2,
379 p_FREIGHT_CARRIER_CODE VARCHAR2,
380 p_FOB_CODE VARCHAR2,
381 p_SHIPPING_INSTRUCTIONS VARCHAR2,
382 p_PACKING_INSTRUCTIONS VARCHAR2,
383 p_QUANTITY NUMBER,
384 p_RESERVED_QUANTITY VARCHAR2,
385 p_RESERVATION_ID NUMBER,
386 p_ORDER_LINE_ID NUMBER,
387 p_ATTRIBUTE_CATEGORY VARCHAR2,
388 p_ATTRIBUTE1 VARCHAR2,
389 p_ATTRIBUTE2 VARCHAR2,
390 p_ATTRIBUTE3 VARCHAR2,
391 p_ATTRIBUTE4 VARCHAR2,
392 p_ATTRIBUTE5 VARCHAR2,
393 p_ATTRIBUTE6 VARCHAR2,
394 p_ATTRIBUTE7 VARCHAR2,
395 p_ATTRIBUTE8 VARCHAR2,
396 p_ATTRIBUTE9 VARCHAR2,
397 p_ATTRIBUTE10 VARCHAR2,
398 p_ATTRIBUTE11 VARCHAR2,
399 p_ATTRIBUTE12 VARCHAR2,
400 p_ATTRIBUTE13 VARCHAR2,
401 p_ATTRIBUTE14 VARCHAR2,
402 p_ATTRIBUTE15 VARCHAR2,
403 p_SHIPMENT_PRIORITY_CODE VARCHAR2,
404 p_SHIP_QUOTE_PRICE NUMBER,
405 p_SHIP_FROM_ORG_ID NUMBER
406 )
407
408 IS
409 CURSOR C IS
410 SELECT SHIPMENT_PRIORITY_CODE,
411 --OBJECT_VERSION_NUMBER,
412 SHIPMENT_ID,
413 CREATION_DATE,
414 CREATED_BY,
415 LAST_UPDATE_DATE,
416 LAST_UPDATED_BY,
417 LAST_UPDATE_LOGIN,
418 REQUEST_ID,
419 PROGRAM_APPLICATION_ID,
420 PROGRAM_ID,
421 PROGRAM_UPDATE_DATE,
422 QUOTE_HEADER_ID,
423 QUOTE_LINE_ID,
424 PROMISE_DATE,
425 REQUEST_DATE,
426 SCHEDULE_SHIP_DATE,
427 SHIP_TO_PARTY_SITE_ID,
428 SHIP_TO_PARTY_ID,
429 SHIP_PARTIAL_FLAG,
430 SHIP_SET_ID,
431 SHIP_METHOD_CODE,
432 FREIGHT_TERMS_CODE,
433 FREIGHT_CARRIER_CODE,
434 FOB_CODE,
435 SHIPPING_INSTRUCTIONS,
436 PACKING_INSTRUCTIONS,
437 QUANTITY,
438 RESERVED_QUANTITY,
439 RESERVATION_ID,
440 ORDER_LINE_ID,
441 ATTRIBUTE_CATEGORY,
442 ATTRIBUTE1,
443 ATTRIBUTE2,
444 ATTRIBUTE3,
445 ATTRIBUTE4,
446 ATTRIBUTE5,
447 ATTRIBUTE6,
448 ATTRIBUTE7,
449 ATTRIBUTE8,
450 ATTRIBUTE9,
451 ATTRIBUTE10,
452 ATTRIBUTE11,
453 ATTRIBUTE12,
454 ATTRIBUTE13,
455 ATTRIBUTE14,
456 ATTRIBUTE15,
457 SHIP_QUOTE_PRICE,
458 SHIP_FROM_ORG_ID
459 FROM ASO_SHIPMENTS
460 WHERE SHIPMENT_ID = p_SHIPMENT_ID
461 FOR UPDATE of SHIPMENT_ID NOWAIT;
462 Recinfo C%ROWTYPE;
463 BEGIN
464 OPEN C;
465 FETCH C INTO Recinfo;
466 If (C%NOTFOUND) then
467 CLOSE C;
468 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
469 APP_EXCEPTION.RAISE_EXCEPTION;
470 End If;
471 CLOSE C;
472 if (
473 /*
474 ( Recinfo.SHIPMENT_ID = p_SHIPMENT_ID)
475 AND ( ( Recinfo.CREATION_DATE = p_CREATION_DATE)
476 OR ( ( Recinfo.CREATION_DATE IS NULL )
477 AND ( p_CREATION_DATE IS NULL )))
478 AND ( ( Recinfo.CREATED_BY = p_CREATED_BY)
479 OR ( ( Recinfo.CREATED_BY IS NULL )
480 AND ( p_CREATED_BY IS NULL )))
481 AND
482 */
483 ( ( Recinfo.LAST_UPDATE_DATE = p_LAST_UPDATE_DATE)
484 OR ( ( Recinfo.LAST_UPDATE_DATE IS NULL )
485 AND ( p_LAST_UPDATE_DATE IS NULL )))
486 /*
487 AND
488 ( ( Recinfo.OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER)
489 OR ( ( Recinfo.OBJECT_VERSION_NUMBER IS NULL )
490 AND ( p_OBJECT_VERSION_NUMBER IS NULL )))
491 AND ( ( Recinfo.LAST_UPDATED_BY = p_LAST_UPDATED_BY)
492 OR ( ( Recinfo.LAST_UPDATED_BY IS NULL )
493 AND ( p_LAST_UPDATED_BY IS NULL )))
494 AND ( ( Recinfo.LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN)
495 OR ( ( Recinfo.LAST_UPDATE_LOGIN IS NULL )
496 AND ( p_LAST_UPDATE_LOGIN IS NULL )))
497 AND ( ( Recinfo.REQUEST_ID = p_REQUEST_ID)
498 OR ( ( Recinfo.REQUEST_ID IS NULL )
499 AND ( p_REQUEST_ID IS NULL )))
500 AND ( ( Recinfo.PROGRAM_APPLICATION_ID = p_PROGRAM_APPLICATION_ID)
501 OR ( ( Recinfo.PROGRAM_APPLICATION_ID IS NULL )
502 AND ( p_PROGRAM_APPLICATION_ID IS NULL )))
503 AND ( ( Recinfo.PROGRAM_ID = p_PROGRAM_ID)
507 OR ( ( Recinfo.PROGRAM_UPDATE_DATE IS NULL )
504 OR ( ( Recinfo.PROGRAM_ID IS NULL )
505 AND ( p_PROGRAM_ID IS NULL )))
506 AND ( ( Recinfo.PROGRAM_UPDATE_DATE = p_PROGRAM_UPDATE_DATE)
508 AND ( p_PROGRAM_UPDATE_DATE IS NULL )))
509 AND ( ( Recinfo.QUOTE_HEADER_ID = p_QUOTE_HEADER_ID)
510 OR ( ( Recinfo.QUOTE_HEADER_ID IS NULL )
511 AND ( p_QUOTE_HEADER_ID IS NULL )))
512 AND ( ( Recinfo.QUOTE_LINE_ID = p_QUOTE_LINE_ID)
513 OR ( ( Recinfo.QUOTE_LINE_ID IS NULL )
514 AND ( p_QUOTE_LINE_ID IS NULL )))
515 AND ( ( Recinfo.PROMISE_DATE = p_PROMISE_DATE)
516 OR ( ( Recinfo.PROMISE_DATE IS NULL )
517 AND ( p_PROMISE_DATE IS NULL )))
518 AND ( ( Recinfo.REQUEST_DATE = p_REQUEST_DATE)
519 OR ( ( Recinfo.REQUEST_DATE IS NULL )
520 AND ( p_REQUEST_DATE IS NULL )))
521 AND ( ( Recinfo.SCHEDULE_SHIP_DATE = p_SCHEDULE_SHIP_DATE)
522 OR ( ( Recinfo.SCHEDULE_SHIP_DATE IS NULL )
523 AND ( p_SCHEDULE_SHIP_DATE IS NULL )))
524 AND ( ( Recinfo.SHIP_TO_PARTY_SITE_ID = p_SHIP_TO_PARTY_SITE_ID)
525 OR ( ( Recinfo.SHIP_TO_PARTY_SITE_ID IS NULL )
526 AND ( p_SHIP_TO_PARTY_SITE_ID IS NULL )))
527 AND ( ( Recinfo.SHIP_TO_PARTY_ID = p_SHIP_TO_PARTY_ID)
528 OR ( ( Recinfo.SHIP_TO_PARTY_ID IS NULL )
529 AND ( p_SHIP_TO_PARTY_ID IS NULL )))
530 AND ( ( Recinfo.SHIP_PARTIAL_FLAG = p_SHIP_PARTIAL_FLAG)
531 OR ( ( Recinfo.SHIP_PARTIAL_FLAG IS NULL )
532 AND ( p_SHIP_PARTIAL_FLAG IS NULL )))
533 AND ( ( Recinfo.SHIP_SET_ID = p_SHIP_SET_ID)
534 OR ( ( Recinfo.SHIP_SET_ID IS NULL )
535 AND ( p_SHIP_SET_ID IS NULL )))
536 AND ( ( Recinfo.SHIP_METHOD_CODE = p_SHIP_METHOD_CODE)
537 OR ( ( Recinfo.SHIP_METHOD_CODE IS NULL )
538 AND ( p_SHIP_METHOD_CODE IS NULL )))
539 AND ( ( Recinfo.FREIGHT_TERMS_CODE = p_FREIGHT_TERMS_CODE)
540 OR ( ( Recinfo.FREIGHT_TERMS_CODE IS NULL )
541 AND ( p_FREIGHT_TERMS_CODE IS NULL )))
542 AND ( ( Recinfo.FREIGHT_CARRIER_CODE = p_FREIGHT_CARRIER_CODE)
543 OR ( ( Recinfo.FREIGHT_CARRIER_CODE IS NULL )
544 AND ( p_FREIGHT_CARRIER_CODE IS NULL )))
545 AND ( ( Recinfo.FOB_CODE = p_FOB_CODE)
546 OR ( ( Recinfo.FOB_CODE IS NULL )
547 AND ( p_FOB_CODE IS NULL )))
548 AND ( ( Recinfo.SHIPPING_INSTRUCTIONS = p_SHIPPING_INSTRUCTIONS)
549 OR ( ( Recinfo.SHIPPING_INSTRUCTIONS IS NULL )
550 AND ( p_SHIPPING_INSTRUCTIONS IS NULL )))
551 AND ( ( Recinfo.PACKING_INSTRUCTIONS = p_PACKING_INSTRUCTIONS)
552 OR ( ( Recinfo.PACKING_INSTRUCTIONS IS NULL )
553 AND ( p_PACKING_INSTRUCTIONS IS NULL )))
554 AND ( ( Recinfo.QUANTITY = p_QUANTITY)
555 OR ( ( Recinfo.QUANTITY IS NULL )
556 AND ( p_QUANTITY IS NULL )))
557 AND ( ( Recinfo.RESERVED_QUANTITY = p_RESERVED_QUANTITY)
558 OR ( ( Recinfo.RESERVED_QUANTITY IS NULL )
559 AND ( p_RESERVED_QUANTITY IS NULL )))
560 AND ( ( Recinfo.RESERVATION_ID = p_RESERVATION_ID)
561 OR ( ( Recinfo.RESERVATION_ID IS NULL )
562 AND ( p_RESERVATION_ID IS NULL )))
563 AND ( ( Recinfo.ORDER_LINE_ID = p_ORDER_LINE_ID)
564 OR ( ( Recinfo.ORDER_LINE_ID IS NULL )
565 AND ( p_ORDER_LINE_ID IS NULL )))
566 AND ( ( Recinfo.ATTRIBUTE_CATEGORY = p_ATTRIBUTE_CATEGORY)
567 OR ( ( Recinfo.ATTRIBUTE_CATEGORY IS NULL )
568 AND ( p_ATTRIBUTE_CATEGORY IS NULL )))
569 AND ( ( Recinfo.ATTRIBUTE1 = p_ATTRIBUTE1)
570 OR ( ( Recinfo.ATTRIBUTE1 IS NULL )
571 AND ( p_ATTRIBUTE1 IS NULL )))
572 AND ( ( Recinfo.ATTRIBUTE2 = p_ATTRIBUTE2)
573 OR ( ( Recinfo.ATTRIBUTE2 IS NULL )
574 AND ( p_ATTRIBUTE2 IS NULL )))
575 AND ( ( Recinfo.ATTRIBUTE3 = p_ATTRIBUTE3)
576 OR ( ( Recinfo.ATTRIBUTE3 IS NULL )
577 AND ( p_ATTRIBUTE3 IS NULL )))
578 AND ( ( Recinfo.ATTRIBUTE4 = p_ATTRIBUTE4)
579 OR ( ( Recinfo.ATTRIBUTE4 IS NULL )
580 AND ( p_ATTRIBUTE4 IS NULL )))
581 AND ( ( Recinfo.ATTRIBUTE5 = p_ATTRIBUTE5)
582 OR ( ( Recinfo.ATTRIBUTE5 IS NULL )
583 AND ( p_ATTRIBUTE5 IS NULL )))
584 AND ( ( Recinfo.ATTRIBUTE6 = p_ATTRIBUTE6)
585 OR ( ( Recinfo.ATTRIBUTE6 IS NULL )
586 AND ( p_ATTRIBUTE6 IS NULL )))
587 AND ( ( Recinfo.ATTRIBUTE7 = p_ATTRIBUTE7)
588 OR ( ( Recinfo.ATTRIBUTE7 IS NULL )
589 AND ( p_ATTRIBUTE7 IS NULL )))
590 AND ( ( Recinfo.ATTRIBUTE8 = p_ATTRIBUTE8)
591 OR ( ( Recinfo.ATTRIBUTE8 IS NULL )
592 AND ( p_ATTRIBUTE8 IS NULL )))
593 AND ( ( Recinfo.ATTRIBUTE9 = p_ATTRIBUTE9)
594 OR ( ( Recinfo.ATTRIBUTE9 IS NULL )
595 AND ( p_ATTRIBUTE9 IS NULL )))
596 AND ( ( Recinfo.ATTRIBUTE10 = p_ATTRIBUTE10)
597 OR ( ( Recinfo.ATTRIBUTE10 IS NULL )
598 AND ( p_ATTRIBUTE10 IS NULL )))
599 AND ( ( Recinfo.ATTRIBUTE11 = p_ATTRIBUTE11)
600 OR ( ( Recinfo.ATTRIBUTE11 IS NULL )
601 AND ( p_ATTRIBUTE11 IS NULL )))
602 AND ( ( Recinfo.ATTRIBUTE12 = p_ATTRIBUTE12)
603 OR ( ( Recinfo.ATTRIBUTE12 IS NULL )
604 AND ( p_ATTRIBUTE12 IS NULL )))
605 AND ( ( Recinfo.ATTRIBUTE13 = p_ATTRIBUTE13)
606 OR ( ( Recinfo.ATTRIBUTE13 IS NULL )
607 AND ( p_ATTRIBUTE13 IS NULL )))
608 AND ( ( Recinfo.ATTRIBUTE14 = p_ATTRIBUTE14)
609 OR ( ( Recinfo.ATTRIBUTE14 IS NULL )
610 AND ( p_ATTRIBUTE14 IS NULL )))
611 AND ( ( Recinfo.ATTRIBUTE15 = p_ATTRIBUTE15)
612 OR ( ( Recinfo.ATTRIBUTE15 IS NULL )
613 AND ( p_ATTRIBUTE15 IS NULL )))
614 AND ( ( Recinfo.SHIPMENT_PRIORITY_CODE = p_SHIPMENT_PRIORITY_CODE)
615 OR ( ( Recinfo.SHIPMENT_PRIORITY_CODE IS NULL )
616 AND ( p_SHIPMENT_PRIORITY_CODE IS NULL )))
617 AND ( ( Recinfo.SHIP_QUOTE_PRICE = p_SHIP_QUOTE_PRICE)
618 OR ( ( Recinfo.SHIP_QUOTE_PRICE IS NULL )
619 AND ( p_SHIP_QUOTE_PRICE IS NULL )))
620 */
621 ) then
622 return;
623 else
624 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
625 APP_EXCEPTION.RAISE_EXCEPTION;
626 End If;
627 END Lock_Row;
628
629 End ASO_SHIPMENTS_PKG;