[Home] [Help]
PACKAGE BODY: APPS.MRP_SHIPPING_ORG_HANDLERS
Source
1 PACKAGE BODY MRP_Shipping_Org_Handlers AS
2 /* $Header: MRPHSHOB.pls 115.4 99/07/16 12:23:04 porting ship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'MRP_Shipping_Org_Handlers';
7
8 -- Procedure Update_Row
9
10 PROCEDURE Update_Row
11 ( p_Shipping_Org_rec IN MRP_Sourcing_Rule_PUB.Shipping_Org_Rec_Type
12 )
13 IS
14 BEGIN
15
16 UPDATE MRP_SR_SOURCE_ORG
17 SET SR_SOURCE_ID = p_Shipping_Org_rec.Sr_Source_Id
18 , ALLOCATION_PERCENT = p_Shipping_Org_rec.Allocation_Percent
19 , ATTRIBUTE1 = p_Shipping_Org_rec.Attribute1
20 , ATTRIBUTE10 = p_Shipping_Org_rec.Attribute10
21 , ATTRIBUTE11 = p_Shipping_Org_rec.Attribute11
22 , ATTRIBUTE12 = p_Shipping_Org_rec.Attribute12
23 , ATTRIBUTE13 = p_Shipping_Org_rec.Attribute13
24 , ATTRIBUTE14 = p_Shipping_Org_rec.Attribute14
25 , ATTRIBUTE15 = p_Shipping_Org_rec.Attribute15
26 , ATTRIBUTE2 = p_Shipping_Org_rec.Attribute2
27 , ATTRIBUTE3 = p_Shipping_Org_rec.Attribute3
28 , ATTRIBUTE4 = p_Shipping_Org_rec.Attribute4
29 , ATTRIBUTE5 = p_Shipping_Org_rec.Attribute5
30 , ATTRIBUTE6 = p_Shipping_Org_rec.Attribute6
31 , ATTRIBUTE7 = p_Shipping_Org_rec.Attribute7
32 , ATTRIBUTE8 = p_Shipping_Org_rec.Attribute8
33 , ATTRIBUTE9 = p_Shipping_Org_rec.Attribute9
34 , ATTRIBUTE_CATEGORY = p_Shipping_Org_rec.Attribute_Category
35 , CREATED_BY = p_Shipping_Org_rec.Created_By
36 , CREATION_DATE = p_Shipping_Org_rec.Creation_Date
37 , LAST_UPDATED_BY = p_Shipping_Org_rec.Last_Updated_By
38 , LAST_UPDATE_DATE = p_Shipping_Org_rec.Last_Update_Date
39 , LAST_UPDATE_LOGIN = p_Shipping_Org_rec.Last_Update_Login
40 , PROGRAM_APPLICATION_ID = p_Shipping_Org_rec.Program_Application_Id
41 , PROGRAM_ID = p_Shipping_Org_rec.Program_Id
42 , PROGRAM_UPDATE_DATE = p_Shipping_Org_rec.Program_Update_Date
43 , RANK = p_Shipping_Org_rec.Rank
44 , REQUEST_ID = p_Shipping_Org_rec.Request_Id
45 , SECONDARY_INVENTORY = p_Shipping_Org_rec.Secondary_Inventory
46 , SHIP_METHOD = p_Shipping_Org_rec.Ship_Method
47 , SOURCE_ORGANIZATION_ID = p_Shipping_Org_rec.Source_Organization_Id
48 , SOURCE_TYPE = p_Shipping_Org_rec.Source_Type
49 , SR_RECEIPT_ID = p_Shipping_Org_rec.Sr_Receipt_Id
50 , VENDOR_ID = p_Shipping_Org_rec.Vendor_Id
51 , VENDOR_SITE_ID = p_Shipping_Org_rec.Vendor_Site_Id
52 WHERE SR_SOURCE_ID = p_Shipping_Org_rec.Sr_Source_Id
53 ;
54
55 EXCEPTION
56
57 WHEN OTHERS THEN
58
59 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
60 THEN
61 FND_MSG_PUB.Add_Exc_Msg
62 ( G_PKG_NAME
63 , 'Update_Row'
64 );
65 END IF;
66
67 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
68
69 END Update_Row;
70
71 -- Procedure Insert_Row
72
73 PROCEDURE Insert_Row
74 ( p_Shipping_Org_rec IN MRP_Sourcing_Rule_PUB.Shipping_Org_Rec_Type
75 )
76 IS
77 BEGIN
78
79 INSERT INTO MRP_SR_SOURCE_ORG
80 ( SR_SOURCE_ID
81 , ALLOCATION_PERCENT
82 , ATTRIBUTE1
83 , ATTRIBUTE10
84 , ATTRIBUTE11
85 , ATTRIBUTE12
86 , ATTRIBUTE13
87 , ATTRIBUTE14
88 , ATTRIBUTE15
89 , ATTRIBUTE2
90 , ATTRIBUTE3
91 , ATTRIBUTE4
92 , ATTRIBUTE5
93 , ATTRIBUTE6
94 , ATTRIBUTE7
95 , ATTRIBUTE8
96 , ATTRIBUTE9
97 , ATTRIBUTE_CATEGORY
98 , CREATED_BY
99 , CREATION_DATE
100 , LAST_UPDATED_BY
101 , LAST_UPDATE_DATE
102 , LAST_UPDATE_LOGIN
103 , PROGRAM_APPLICATION_ID
104 , PROGRAM_ID
105 , PROGRAM_UPDATE_DATE
106 , RANK
107 , REQUEST_ID
108 , SECONDARY_INVENTORY
109 , SHIP_METHOD
110 , SOURCE_ORGANIZATION_ID
111 , SOURCE_TYPE
112 , SR_RECEIPT_ID
113 , VENDOR_ID
114 , VENDOR_SITE_ID
115 )
116 VALUES
117 ( p_Shipping_Org_rec.Sr_Source_Id
118 , p_Shipping_Org_rec.Allocation_Percent
119 , p_Shipping_Org_rec.Attribute1
120 , p_Shipping_Org_rec.Attribute10
121 , p_Shipping_Org_rec.Attribute11
122 , p_Shipping_Org_rec.Attribute12
123 , p_Shipping_Org_rec.Attribute13
124 , p_Shipping_Org_rec.Attribute14
125 , p_Shipping_Org_rec.Attribute15
126 , p_Shipping_Org_rec.Attribute2
127 , p_Shipping_Org_rec.Attribute3
128 , p_Shipping_Org_rec.Attribute4
129 , p_Shipping_Org_rec.Attribute5
130 , p_Shipping_Org_rec.Attribute6
131 , p_Shipping_Org_rec.Attribute7
132 , p_Shipping_Org_rec.Attribute8
133 , p_Shipping_Org_rec.Attribute9
134 , p_Shipping_Org_rec.Attribute_Category
135 , p_Shipping_Org_rec.Created_By
136 , p_Shipping_Org_rec.Creation_Date
137 , p_Shipping_Org_rec.Last_Updated_By
138 , p_Shipping_Org_rec.Last_Update_Date
139 , p_Shipping_Org_rec.Last_Update_Login
140 , p_Shipping_Org_rec.Program_Application_Id
141 , p_Shipping_Org_rec.Program_Id
142 , p_Shipping_Org_rec.Program_Update_Date
143 , p_Shipping_Org_rec.Rank
144 , p_Shipping_Org_rec.Request_Id
145 , p_Shipping_Org_rec.Secondary_Inventory
146 , p_Shipping_Org_rec.Ship_Method
147 , p_Shipping_Org_rec.Source_Organization_Id
148 , p_Shipping_Org_rec.Source_Type
149 , p_Shipping_Org_rec.Sr_Receipt_Id
150 , p_Shipping_Org_rec.Vendor_Id
151 , p_Shipping_Org_rec.Vendor_Site_Id
152 );
153
154 EXCEPTION
155
156 WHEN OTHERS THEN
157
158 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
159 THEN
160 FND_MSG_PUB.Add_Exc_Msg
161 ( G_PKG_NAME
162 , 'Insert_Row'
163 );
164 END IF;
165
166 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
167
168 END Insert_Row;
169
170 -- Procedure Delete_Row
171
172 PROCEDURE Delete_Row
173 ( p_Sr_Source_Id IN NUMBER
174 )
175 IS
176 BEGIN
177
178 DELETE FROM MRP_SR_SOURCE_ORG
179 WHERE SR_SOURCE_ID = p_Sr_Source_Id
180 ;
181
182 EXCEPTION
183
184 WHEN OTHERS THEN
185
186 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
187 THEN
188 FND_MSG_PUB.Add_Exc_Msg
189 ( G_PKG_NAME
190 , 'Delete_Row'
191 );
192 END IF;
193
194 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
195
196 END Delete_Row;
197
198 -- Procedure lock_Row
199 --
200
201 PROCEDURE Lock_Row
202 ( x_return_status OUT VARCHAR2
203 , p_Shipping_Org_rec IN MRP_Sourcing_Rule_PUB.Shipping_Org_Rec_Type
204 , x_Shipping_Org_rec OUT MRP_Sourcing_Rule_PUB.Shipping_Org_Rec_Type
205 )
206 IS
207 l_Shipping_Org_rec MRP_Sourcing_Rule_PUB.Shipping_Org_Rec_Type;
208 BEGIN
209
210 SELECT SR_SOURCE_ID
211 , ALLOCATION_PERCENT
212 , ATTRIBUTE1
213 , ATTRIBUTE10
214 , ATTRIBUTE11
215 , ATTRIBUTE12
216 , ATTRIBUTE13
217 , ATTRIBUTE14
218 , ATTRIBUTE15
219 , ATTRIBUTE2
220 , ATTRIBUTE3
221 , ATTRIBUTE4
222 , ATTRIBUTE5
223 , ATTRIBUTE6
224 , ATTRIBUTE7
225 , ATTRIBUTE8
226 , ATTRIBUTE9
227 , ATTRIBUTE_CATEGORY
228 , CREATED_BY
229 , CREATION_DATE
230 , LAST_UPDATED_BY
231 , LAST_UPDATE_DATE
232 , LAST_UPDATE_LOGIN
233 , PROGRAM_APPLICATION_ID
234 , PROGRAM_ID
235 , PROGRAM_UPDATE_DATE
236 , RANK
237 , REQUEST_ID
238 , SECONDARY_INVENTORY
239 , SHIP_METHOD
240 , SOURCE_ORGANIZATION_ID
241 , SOURCE_TYPE
242 , SR_RECEIPT_ID
243 , VENDOR_ID
244 , VENDOR_SITE_ID
245 INTO l_Shipping_Org_rec.Sr_Source_Id
246 , l_Shipping_Org_rec.Allocation_Percent
247 , l_Shipping_Org_rec.Attribute1
248 , l_Shipping_Org_rec.Attribute10
249 , l_Shipping_Org_rec.Attribute11
250 , l_Shipping_Org_rec.Attribute12
251 , l_Shipping_Org_rec.Attribute13
252 , l_Shipping_Org_rec.Attribute14
253 , l_Shipping_Org_rec.Attribute15
254 , l_Shipping_Org_rec.Attribute2
255 , l_Shipping_Org_rec.Attribute3
256 , l_Shipping_Org_rec.Attribute4
257 , l_Shipping_Org_rec.Attribute5
258 , l_Shipping_Org_rec.Attribute6
259 , l_Shipping_Org_rec.Attribute7
260 , l_Shipping_Org_rec.Attribute8
261 , l_Shipping_Org_rec.Attribute9
262 , l_Shipping_Org_rec.Attribute_Category
263 , l_Shipping_Org_rec.Created_By
264 , l_Shipping_Org_rec.Creation_Date
265 , l_Shipping_Org_rec.Last_Updated_By
266 , l_Shipping_Org_rec.Last_Update_Date
267 , l_Shipping_Org_rec.Last_Update_Login
268 , l_Shipping_Org_rec.Program_Application_Id
269 , l_Shipping_Org_rec.Program_Id
270 , l_Shipping_Org_rec.Program_Update_Date
271 , l_Shipping_Org_rec.Rank
272 , l_Shipping_Org_rec.Request_Id
273 , l_Shipping_Org_rec.Secondary_Inventory
274 , l_Shipping_Org_rec.Ship_Method
275 , l_Shipping_Org_rec.Source_Organization_Id
276 , l_Shipping_Org_rec.Source_Type
277 , l_Shipping_Org_rec.Sr_Receipt_Id
278 , l_Shipping_Org_rec.Vendor_Id
279 , l_Shipping_Org_rec.Vendor_Site_Id
280 FROM MRP_SR_SOURCE_ORG
281 WHERE SR_SOURCE_ID = p_Shipping_Org_rec.Sr_Source_Id
282 FOR UPDATE NOWAIT;
283
284 -- Row locked. Compare IN attributes to DB attributes.
285
286 IF ( (l_Shipping_Org_rec.Sr_Source_Id =
287 p_Shipping_Org_rec.Sr_Source_Id) OR
288 ((p_Shipping_Org_rec.Sr_Source_Id = FND_API.G_MISS_NUM) OR
289 ( (l_Shipping_Org_rec.Sr_Source_Id IS NULL) AND
290 (p_Shipping_Org_rec.Sr_Source_Id IS NULL))))
291 AND ( (l_Shipping_Org_rec.Allocation_Percent =
292 p_Shipping_Org_rec.Allocation_Percent) OR
293 ((p_Shipping_Org_rec.Allocation_Percent = FND_API.G_MISS_NUM) OR
294 ( (l_Shipping_Org_rec.Allocation_Percent IS NULL) AND
295 (p_Shipping_Org_rec.Allocation_Percent IS NULL))))
296 AND ( (l_Shipping_Org_rec.Attribute1 =
297 p_Shipping_Org_rec.Attribute1) OR
298 ((p_Shipping_Org_rec.Attribute1 = FND_API.G_MISS_CHAR) OR
299 ( (l_Shipping_Org_rec.Attribute1 IS NULL) AND
300 (p_Shipping_Org_rec.Attribute1 IS NULL))))
301 AND ( (l_Shipping_Org_rec.Attribute10 =
302 p_Shipping_Org_rec.Attribute10) OR
303 ((p_Shipping_Org_rec.Attribute10 = FND_API.G_MISS_CHAR) OR
304 ( (l_Shipping_Org_rec.Attribute10 IS NULL) AND
305 (p_Shipping_Org_rec.Attribute10 IS NULL))))
306 AND ( (l_Shipping_Org_rec.Attribute11 =
307 p_Shipping_Org_rec.Attribute11) OR
308 ((p_Shipping_Org_rec.Attribute11 = FND_API.G_MISS_CHAR) OR
309 ( (l_Shipping_Org_rec.Attribute11 IS NULL) AND
310 (p_Shipping_Org_rec.Attribute11 IS NULL))))
311 AND ( (l_Shipping_Org_rec.Attribute12 =
312 p_Shipping_Org_rec.Attribute12) OR
313 ((p_Shipping_Org_rec.Attribute12 = FND_API.G_MISS_CHAR) OR
314 ( (l_Shipping_Org_rec.Attribute12 IS NULL) AND
315 (p_Shipping_Org_rec.Attribute12 IS NULL))))
316 AND ( (l_Shipping_Org_rec.Attribute13 =
317 p_Shipping_Org_rec.Attribute13) OR
318 ((p_Shipping_Org_rec.Attribute13 = FND_API.G_MISS_CHAR) OR
319 ( (l_Shipping_Org_rec.Attribute13 IS NULL) AND
320 (p_Shipping_Org_rec.Attribute13 IS NULL))))
321 AND ( (l_Shipping_Org_rec.Attribute14 =
322 p_Shipping_Org_rec.Attribute14) OR
323 ((p_Shipping_Org_rec.Attribute14 = FND_API.G_MISS_CHAR) OR
324 ( (l_Shipping_Org_rec.Attribute14 IS NULL) AND
325 (p_Shipping_Org_rec.Attribute14 IS NULL))))
326 AND ( (l_Shipping_Org_rec.Attribute15 =
327 p_Shipping_Org_rec.Attribute15) OR
328 ((p_Shipping_Org_rec.Attribute15 = FND_API.G_MISS_CHAR) OR
329 ( (l_Shipping_Org_rec.Attribute15 IS NULL) AND
330 (p_Shipping_Org_rec.Attribute15 IS NULL))))
331 AND ( (l_Shipping_Org_rec.Attribute2 =
332 p_Shipping_Org_rec.Attribute2) OR
333 ((p_Shipping_Org_rec.Attribute2 = FND_API.G_MISS_CHAR) OR
334 ( (l_Shipping_Org_rec.Attribute2 IS NULL) AND
335 (p_Shipping_Org_rec.Attribute2 IS NULL))))
336 AND ( (l_Shipping_Org_rec.Attribute3 =
337 p_Shipping_Org_rec.Attribute3) OR
338 ((p_Shipping_Org_rec.Attribute3 = FND_API.G_MISS_CHAR) OR
339 ( (l_Shipping_Org_rec.Attribute3 IS NULL) AND
340 (p_Shipping_Org_rec.Attribute3 IS NULL))))
341 AND ( (l_Shipping_Org_rec.Attribute4 =
342 p_Shipping_Org_rec.Attribute4) OR
343 ((p_Shipping_Org_rec.Attribute4 = FND_API.G_MISS_CHAR) OR
344 ( (l_Shipping_Org_rec.Attribute4 IS NULL) AND
345 (p_Shipping_Org_rec.Attribute4 IS NULL))))
346 AND ( (l_Shipping_Org_rec.Attribute5 =
347 p_Shipping_Org_rec.Attribute5) OR
348 ((p_Shipping_Org_rec.Attribute5 = FND_API.G_MISS_CHAR) OR
349 ( (l_Shipping_Org_rec.Attribute5 IS NULL) AND
350 (p_Shipping_Org_rec.Attribute5 IS NULL))))
351 AND ( (l_Shipping_Org_rec.Attribute6 =
352 p_Shipping_Org_rec.Attribute6) OR
353 ((p_Shipping_Org_rec.Attribute6 = FND_API.G_MISS_CHAR) OR
354 ( (l_Shipping_Org_rec.Attribute6 IS NULL) AND
355 (p_Shipping_Org_rec.Attribute6 IS NULL))))
356 AND ( (l_Shipping_Org_rec.Attribute7 =
357 p_Shipping_Org_rec.Attribute7) OR
358 ((p_Shipping_Org_rec.Attribute7 = FND_API.G_MISS_CHAR) OR
359 ( (l_Shipping_Org_rec.Attribute7 IS NULL) AND
360 (p_Shipping_Org_rec.Attribute7 IS NULL))))
361 AND ( (l_Shipping_Org_rec.Attribute8 =
362 p_Shipping_Org_rec.Attribute8) OR
363 ((p_Shipping_Org_rec.Attribute8 = FND_API.G_MISS_CHAR) OR
364 ( (l_Shipping_Org_rec.Attribute8 IS NULL) AND
365 (p_Shipping_Org_rec.Attribute8 IS NULL))))
366 AND ( (l_Shipping_Org_rec.Attribute9 =
367 p_Shipping_Org_rec.Attribute9) OR
368 ((p_Shipping_Org_rec.Attribute9 = FND_API.G_MISS_CHAR) OR
369 ( (l_Shipping_Org_rec.Attribute9 IS NULL) AND
370 (p_Shipping_Org_rec.Attribute9 IS NULL))))
371 AND ( (l_Shipping_Org_rec.Attribute_Category =
375 (p_Shipping_Org_rec.Attribute_Category IS NULL))))
372 p_Shipping_Org_rec.Attribute_Category) OR
373 ((p_Shipping_Org_rec.Attribute_Category = FND_API.G_MISS_CHAR) OR
374 ( (l_Shipping_Org_rec.Attribute_Category IS NULL) AND
376 AND ( (l_Shipping_Org_rec.Created_By =
377 p_Shipping_Org_rec.Created_By) OR
378 ((p_Shipping_Org_rec.Created_By = FND_API.G_MISS_NUM) OR
379 ( (l_Shipping_Org_rec.Created_By IS NULL) AND
380 (p_Shipping_Org_rec.Created_By IS NULL))))
381 AND ( (l_Shipping_Org_rec.Creation_Date =
382 p_Shipping_Org_rec.Creation_Date) OR
383 ((p_Shipping_Org_rec.Creation_Date = FND_API.G_MISS_DATE) OR
384 ( (l_Shipping_Org_rec.Creation_Date IS NULL) AND
385 (p_Shipping_Org_rec.Creation_Date IS NULL))))
386 AND ( (l_Shipping_Org_rec.Last_Updated_By =
387 p_Shipping_Org_rec.Last_Updated_By) OR
388 ((p_Shipping_Org_rec.Last_Updated_By = FND_API.G_MISS_NUM) OR
389 ( (l_Shipping_Org_rec.Last_Updated_By IS NULL) AND
390 (p_Shipping_Org_rec.Last_Updated_By IS NULL))))
391 AND ( (l_Shipping_Org_rec.Last_Update_Date =
392 p_Shipping_Org_rec.Last_Update_Date) OR
393 ((p_Shipping_Org_rec.Last_Update_Date = FND_API.G_MISS_DATE) OR
394 ( (l_Shipping_Org_rec.Last_Update_Date IS NULL) AND
395 (p_Shipping_Org_rec.Last_Update_Date IS NULL))))
396 AND ( (l_Shipping_Org_rec.Last_Update_Login =
397 p_Shipping_Org_rec.Last_Update_Login) OR
398 ((p_Shipping_Org_rec.Last_Update_Login = FND_API.G_MISS_NUM) OR
399 ( (l_Shipping_Org_rec.Last_Update_Login IS NULL) AND
400 (p_Shipping_Org_rec.Last_Update_Login IS NULL))))
401 AND ( (l_Shipping_Org_rec.Program_Application_Id =
402 p_Shipping_Org_rec.Program_Application_Id) OR
403 ((p_Shipping_Org_rec.Program_Application_Id = FND_API.G_MISS_NUM) OR
404 ( (l_Shipping_Org_rec.Program_Application_Id IS NULL) AND
405 (p_Shipping_Org_rec.Program_Application_Id IS NULL))))
406 AND ( (l_Shipping_Org_rec.Program_Id =
407 p_Shipping_Org_rec.Program_Id) OR
408 ((p_Shipping_Org_rec.Program_Id = FND_API.G_MISS_NUM) OR
409 ( (l_Shipping_Org_rec.Program_Id IS NULL) AND
410 (p_Shipping_Org_rec.Program_Id IS NULL))))
411 AND ( (l_Shipping_Org_rec.Program_Update_Date =
412 p_Shipping_Org_rec.Program_Update_Date) OR
413 ((p_Shipping_Org_rec.Program_Update_Date = FND_API.G_MISS_DATE) OR
414 ( (l_Shipping_Org_rec.Program_Update_Date IS NULL) AND
415 (p_Shipping_Org_rec.Program_Update_Date IS NULL))))
416 AND ( (l_Shipping_Org_rec.Rank =
417 p_Shipping_Org_rec.Rank) OR
418 ((p_Shipping_Org_rec.Rank = FND_API.G_MISS_NUM) OR
419 ( (l_Shipping_Org_rec.Rank IS NULL) AND
420 (p_Shipping_Org_rec.Rank IS NULL))))
421 AND ( (l_Shipping_Org_rec.Request_Id =
422 p_Shipping_Org_rec.Request_Id) OR
423 ((p_Shipping_Org_rec.Request_Id = FND_API.G_MISS_NUM) OR
424 ( (l_Shipping_Org_rec.Request_Id IS NULL) AND
425 (p_Shipping_Org_rec.Request_Id IS NULL))))
426 AND ( (l_Shipping_Org_rec.Secondary_Inventory =
427 p_Shipping_Org_rec.Secondary_Inventory) OR
428 ((p_Shipping_Org_rec.Secondary_Inventory = FND_API.G_MISS_CHAR) OR
429 ( (l_Shipping_Org_rec.Secondary_Inventory IS NULL) AND
430 (p_Shipping_Org_rec.Secondary_Inventory IS NULL))))
431 AND ( (l_Shipping_Org_rec.Ship_Method =
432 p_Shipping_Org_rec.Ship_Method) OR
433 ((p_Shipping_Org_rec.Ship_Method = FND_API.G_MISS_CHAR) OR
434 ( (l_Shipping_Org_rec.Ship_Method IS NULL) AND
435 (p_Shipping_Org_rec.Ship_Method IS NULL))))
436 AND ( (l_Shipping_Org_rec.Source_Organization_Id =
437 p_Shipping_Org_rec.Source_Organization_Id) OR
438 ((p_Shipping_Org_rec.Source_Organization_Id = FND_API.G_MISS_NUM) OR
439 ( (l_Shipping_Org_rec.Source_Organization_Id IS NULL) AND
440 (p_Shipping_Org_rec.Source_Organization_Id IS NULL))))
441 AND ( (l_Shipping_Org_rec.Source_Type =
442 p_Shipping_Org_rec.Source_Type) OR
443 ((p_Shipping_Org_rec.Source_Type = FND_API.G_MISS_NUM) OR
444 ( (l_Shipping_Org_rec.Source_Type IS NULL) AND
445 (p_Shipping_Org_rec.Source_Type IS NULL))))
446 AND ( (l_Shipping_Org_rec.Sr_Receipt_Id =
447 p_Shipping_Org_rec.Sr_Receipt_Id) OR
448 ((p_Shipping_Org_rec.Sr_Receipt_Id = FND_API.G_MISS_NUM) OR
449 ( (l_Shipping_Org_rec.Sr_Receipt_Id IS NULL) AND
450 (p_Shipping_Org_rec.Sr_Receipt_Id IS NULL))))
451 AND ( (l_Shipping_Org_rec.Vendor_Id =
452 p_Shipping_Org_rec.Vendor_Id) OR
453 ((p_Shipping_Org_rec.Vendor_Id = FND_API.G_MISS_NUM) OR
454 ( (l_Shipping_Org_rec.Vendor_Id IS NULL) AND
455 (p_Shipping_Org_rec.Vendor_Id IS NULL))))
456 AND ( (l_Shipping_Org_rec.Vendor_Site_Id =
457 p_Shipping_Org_rec.Vendor_Site_Id) OR
458 ((p_Shipping_Org_rec.Vendor_Site_Id = FND_API.G_MISS_NUM) OR
459 ( (l_Shipping_Org_rec.Vendor_Site_Id IS NULL) AND
460 (p_Shipping_Org_rec.Vendor_Site_Id IS NULL))))
461 THEN
462
466
463 -- Row has not changed. Set out parameter.
464
465 x_Shipping_Org_rec := l_Shipping_Org_rec;
467 -- Set return status
468
469 x_return_status := FND_API.G_RET_STS_SUCCESS;
470 x_Shipping_Org_rec.return_status := FND_API.G_RET_STS_SUCCESS;
471
472 ELSE
473
474 -- Row has changed by another user.
475
476 x_return_status := FND_API.G_RET_STS_ERROR;
477 x_Shipping_Org_rec.return_status := FND_API.G_RET_STS_ERROR;
478
479 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
480 THEN
481
482 FND_MESSAGE.SET_NAME('MRP','BOI_LOCK_ROW_CHANGED');
483 FND_MSG_PUB.Add;
484
485 END IF;
486
487 END IF;
488
489 EXCEPTION
490
491 WHEN NO_DATA_FOUND THEN
492
493 x_return_status := FND_API.G_RET_STS_ERROR;
494 x_Shipping_Org_rec.return_status := FND_API.G_RET_STS_ERROR;
495
496 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
497 THEN
498
499 FND_MESSAGE.SET_NAME('MRP','BOI_LOCK_ROW_DELETED');
500 FND_MSG_PUB.Add;
501
502 END IF;
503 WHEN APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION THEN
504
505 x_return_status := FND_API.G_RET_STS_ERROR;
506 x_Shipping_Org_rec.return_status := FND_API.G_RET_STS_ERROR;
507
508 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
509 THEN
510
511 FND_MESSAGE.SET_NAME('MRP','BOI_LOCK_ROW_ALREADY_LOCKED');
512 FND_MSG_PUB.Add;
513
514 END IF;
515 WHEN OTHERS THEN
516
517 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
518 x_Shipping_Org_rec.return_status := FND_API.G_RET_STS_UNEXP_ERROR;
519
520 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
521 THEN
522 FND_MSG_PUB.Add_Exc_Msg
523 ( G_PKG_NAME
524 , 'Lock_Row'
525 );
526 END IF;
527
528 END Lock_Row;
529
530 -- Function Query_Row
531
532 FUNCTION Query_Row
533 ( p_Sr_Source_Id IN NUMBER
534 ) RETURN MRP_Sourcing_Rule_PUB.Shipping_Org_Rec_Type
535 IS
536 BEGIN
537
538 RETURN Query_Rows
539 ( p_Sr_Source_Id => p_Sr_Source_Id
540 )(1);
541
542
543 EXCEPTION
544
545 WHEN OTHERS THEN
546
547 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
548 THEN
549 FND_MSG_PUB.Add_Exc_Msg
550 ( G_PKG_NAME
551 , 'Query_Row'
552 );
553 END IF;
554
555 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
556
557 END Query_Row;
558
559 -- Procedure Query_Entity
560
561 PROCEDURE Query_Entity
562 ( p_Sr_Source_Id IN NUMBER
563 , x_Shipping_Org_rec OUT MRP_Sourcing_Rule_PUB.Shipping_Org_Rec_Type
564 , x_Shipping_Org_val_rec OUT MRP_Sourcing_Rule_PUB.Shipping_Org_Val_Rec_Type
565 )
566 IS
567 l_Shipping_Org_tbl MRP_Sourcing_Rule_PUB.Shipping_Org_Tbl_Type;
568 l_Shipping_Org_val_tbl MRP_Sourcing_Rule_PUB.Shipping_Org_Val_Tbl_Type;
569 BEGIN
570
571 Query_Entities
572 ( p_Sr_Source_Id => p_Sr_Source_Id
573 , x_Shipping_Org_tbl => l_Shipping_Org_tbl
574 , x_Shipping_Org_val_tbl => l_Shipping_Org_val_tbl
575 );
576
577 -- Assign out records
578
579 x_Shipping_Org_rec := l_Shipping_Org_tbl(1);
580 x_Shipping_Org_val_rec := l_Shipping_Org_val_tbl(1);
581
582
583 EXCEPTION
584
585 WHEN OTHERS THEN
586
587 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
588 THEN
589 FND_MSG_PUB.Add_Exc_Msg
590 ( G_PKG_NAME
591 , 'Query_Entity'
592 );
593 END IF;
594
595 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
596
597 END Query_Entity;
598
599 -- Function Query_Rows
600
601 --
602
603 FUNCTION Query_Rows
604 ( p_Sr_Source_Id IN NUMBER :=
605 FND_API.G_MISS_NUM
606 , p_Sr_Receipt_Id IN NUMBER :=
607 FND_API.G_MISS_NUM
608 ) RETURN MRP_Sourcing_Rule_PUB.Shipping_Org_Tbl_Type
609 IS
610 l_Shipping_Org_rec MRP_Sourcing_Rule_PUB.Shipping_Org_Rec_Type;
611 l_Shipping_Org_tbl MRP_Sourcing_Rule_PUB.Shipping_Org_Tbl_Type;
612
613 CURSOR l_Shipping_Org_csr IS
614 SELECT SR_SOURCE_ID
615 , ALLOCATION_PERCENT
616 , ATTRIBUTE1
617 , ATTRIBUTE10
618 , ATTRIBUTE11
619 , ATTRIBUTE12
620 , ATTRIBUTE13
621 , ATTRIBUTE14
622 , ATTRIBUTE15
623 , ATTRIBUTE2
624 , ATTRIBUTE3
625 , ATTRIBUTE4
626 , ATTRIBUTE5
627 , ATTRIBUTE6
628 , ATTRIBUTE7
629 , ATTRIBUTE8
630 , ATTRIBUTE9
631 , ATTRIBUTE_CATEGORY
632 , CREATED_BY
633 , CREATION_DATE
634 , LAST_UPDATED_BY
635 , LAST_UPDATE_DATE
636 , LAST_UPDATE_LOGIN
637 , PROGRAM_APPLICATION_ID
641 , REQUEST_ID
638 , PROGRAM_ID
639 , PROGRAM_UPDATE_DATE
640 , RANK
642 , SECONDARY_INVENTORY
643 , SHIP_METHOD
644 , SOURCE_ORGANIZATION_ID
645 , SOURCE_TYPE
646 , SR_RECEIPT_ID
647 , VENDOR_ID
648 , VENDOR_SITE_ID
649 FROM MRP_SR_SOURCE_ORG
650 WHERE ( SR_SOURCE_ID = p_Sr_Source_Id
651 )
652 OR ( SR_RECEIPT_ID = p_Sr_Receipt_Id
653 );
654
655 BEGIN
656
657 IF
658 (p_Sr_Source_Id IS NOT NULL
659 AND
660 p_Sr_Source_Id <> FND_API.G_MISS_NUM)
661 AND
662 (p_Sr_Receipt_Id IS NOT NULL
663 AND
664 p_Sr_Receipt_Id <> FND_API.G_MISS_NUM)
665 THEN
666 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
667 THEN
668 FND_MSG_PUB.Add_Exc_Msg
669 ( G_PKG_NAME
670 , 'Query Rows'
671 , 'Keys are mutually exclusive: Sr_Source_Id = '|| p_Sr_Source_Id || ', Sr_Receipt_Id = '|| p_Sr_Receipt_Id
672 );
673 END IF;
674
675 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
676
677 END IF;
678
679
680 -- Loop over fetched records
681
682 FOR l_implicit_rec IN l_Shipping_Org_csr LOOP
683
684 l_Shipping_Org_rec.Sr_Source_Id := l_implicit_rec.SR_SOURCE_ID;
685 l_Shipping_Org_rec.Allocation_Percent := l_implicit_rec.ALLOCATION_PERCENT;
686 l_Shipping_Org_rec.Attribute1 := l_implicit_rec.ATTRIBUTE1;
687 l_Shipping_Org_rec.Attribute10 := l_implicit_rec.ATTRIBUTE10;
688 l_Shipping_Org_rec.Attribute11 := l_implicit_rec.ATTRIBUTE11;
689 l_Shipping_Org_rec.Attribute12 := l_implicit_rec.ATTRIBUTE12;
690 l_Shipping_Org_rec.Attribute13 := l_implicit_rec.ATTRIBUTE13;
691 l_Shipping_Org_rec.Attribute14 := l_implicit_rec.ATTRIBUTE14;
692 l_Shipping_Org_rec.Attribute15 := l_implicit_rec.ATTRIBUTE15;
693 l_Shipping_Org_rec.Attribute2 := l_implicit_rec.ATTRIBUTE2;
694 l_Shipping_Org_rec.Attribute3 := l_implicit_rec.ATTRIBUTE3;
695 l_Shipping_Org_rec.Attribute4 := l_implicit_rec.ATTRIBUTE4;
696 l_Shipping_Org_rec.Attribute5 := l_implicit_rec.ATTRIBUTE5;
697 l_Shipping_Org_rec.Attribute6 := l_implicit_rec.ATTRIBUTE6;
698 l_Shipping_Org_rec.Attribute7 := l_implicit_rec.ATTRIBUTE7;
699 l_Shipping_Org_rec.Attribute8 := l_implicit_rec.ATTRIBUTE8;
700 l_Shipping_Org_rec.Attribute9 := l_implicit_rec.ATTRIBUTE9;
701 l_Shipping_Org_rec.Attribute_Category := l_implicit_rec.ATTRIBUTE_CATEGORY;
702 l_Shipping_Org_rec.Created_By := l_implicit_rec.CREATED_BY;
703 l_Shipping_Org_rec.Creation_Date := l_implicit_rec.CREATION_DATE;
704 l_Shipping_Org_rec.Last_Updated_By := l_implicit_rec.LAST_UPDATED_BY;
705 l_Shipping_Org_rec.Last_Update_Date := l_implicit_rec.LAST_UPDATE_DATE;
706 l_Shipping_Org_rec.Last_Update_Login := l_implicit_rec.LAST_UPDATE_LOGIN;
707 l_Shipping_Org_rec.Program_Application_Id := l_implicit_rec.PROGRAM_APPLICATION_ID;
708 l_Shipping_Org_rec.Program_Id := l_implicit_rec.PROGRAM_ID;
709 l_Shipping_Org_rec.Program_Update_Date := l_implicit_rec.PROGRAM_UPDATE_DATE;
710 l_Shipping_Org_rec.Rank := l_implicit_rec.RANK;
711 l_Shipping_Org_rec.Request_Id := l_implicit_rec.REQUEST_ID;
712 l_Shipping_Org_rec.Secondary_Inventory := l_implicit_rec.SECONDARY_INVENTORY;
713 l_Shipping_Org_rec.Ship_Method := l_implicit_rec.SHIP_METHOD;
714 l_Shipping_Org_rec.Source_Organization_Id := l_implicit_rec.SOURCE_ORGANIZATION_ID;
715 l_Shipping_Org_rec.Source_Type := l_implicit_rec.SOURCE_TYPE;
716 l_Shipping_Org_rec.Sr_Receipt_Id := l_implicit_rec.SR_RECEIPT_ID;
717 l_Shipping_Org_rec.Vendor_Id := l_implicit_rec.VENDOR_ID;
718 l_Shipping_Org_rec.Vendor_Site_Id := l_implicit_rec.VENDOR_SITE_ID;
719
720 l_Shipping_Org_tbl(l_Shipping_Org_tbl.COUNT + 1) := l_Shipping_Org_rec;
721
722 END LOOP;
723
724
725 -- PK sent and no rows found
726
727 IF
728 (p_Sr_Source_Id IS NOT NULL
729 AND
730 p_Sr_Source_Id <> FND_API.G_MISS_NUM)
731 AND
732 (l_Shipping_Org_tbl.COUNT = 0)
733 THEN
734 RAISE NO_DATA_FOUND;
735 END IF;
736
737
738 -- Return fetched table
739
740 RETURN l_Shipping_Org_tbl;
741
742 EXCEPTION
743
744 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
745
746 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
747
748 WHEN OTHERS THEN
749
750 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
751 THEN
752 FND_MSG_PUB.Add_Exc_Msg
753 ( G_PKG_NAME
754 , 'Query_Rows'
755 );
756 END IF;
757
758 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
759
760 END Query_Rows;
761
762 -- Procedure Query_Entities
763
764 --
765
766 PROCEDURE Query_Entities
767 ( p_Sr_Source_Id IN NUMBER :=
768 FND_API.G_MISS_NUM
769 , p_Sr_Receipt_Id IN NUMBER :=
770 FND_API.G_MISS_NUM
771 , x_Shipping_Org_tbl OUT MRP_Sourcing_Rule_PUB.Shipping_Org_Tbl_Type
772 , x_Shipping_Org_val_tbl OUT MRP_Sourcing_Rule_PUB.Shipping_Org_Val_Tbl_Type
773 )
774 IS
775 l_Shipping_Org_rec MRP_Sourcing_Rule_PUB.Shipping_Org_Rec_Type;
776 l_Shipping_Org_val_rec MRP_Sourcing_Rule_PUB.Shipping_Org_Val_Rec_Type;
777 l_rows_fetched BOOLEAN := FALSE;
778
782 , ATTRIBUTE1
779 CURSOR l_Shipping_Org_csr IS
780 SELECT SR_SOURCE_ID
781 , ALLOCATION_PERCENT
783 , ATTRIBUTE10
784 , ATTRIBUTE11
785 , ATTRIBUTE12
786 , ATTRIBUTE13
787 , ATTRIBUTE14
788 , ATTRIBUTE15
789 , ATTRIBUTE2
790 , ATTRIBUTE3
791 , ATTRIBUTE4
792 , ATTRIBUTE5
793 , ATTRIBUTE6
794 , ATTRIBUTE7
795 , ATTRIBUTE8
796 , ATTRIBUTE9
797 , ATTRIBUTE_CATEGORY
798 , CREATED_BY
799 , CREATION_DATE
800 , LAST_UPDATED_BY
801 , LAST_UPDATE_DATE
802 , LAST_UPDATE_LOGIN
803 , PROGRAM_APPLICATION_ID
804 , PROGRAM_ID
805 , PROGRAM_UPDATE_DATE
806 , RANK
807 , REQUEST_ID
808 , SECONDARY_INVENTORY
809 , SHIP_METHOD
810 , SOURCE_ORGANIZATION_ID
811 , SOURCE_TYPE
812 , SR_RECEIPT_ID
813 , VENDOR_ID
814 , VENDOR_SITE_ID
815 FROM MRP_SR_SOURCE_ORG
816 WHERE ( SR_SOURCE_ID = p_Sr_Source_Id
817 )
818 OR ( SR_RECEIPT_ID = p_Sr_Receipt_Id
819 );
820
821 BEGIN
822
823 IF
824 (p_Sr_Source_Id IS NOT NULL
825 AND
826 p_Sr_Source_Id <> FND_API.G_MISS_NUM)
827 AND
828 (p_Sr_Receipt_Id IS NOT NULL
829 AND
830 p_Sr_Receipt_Id <> FND_API.G_MISS_NUM)
831 THEN
832 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
833 THEN
834 FND_MSG_PUB.Add_Exc_Msg
835 ( G_PKG_NAME
836 , 'Query Entities'
837 , 'Keys are mutually exclusive: Sr_Source_Id = '|| p_Sr_Source_Id || ', Sr_Receipt_Id = '|| p_Sr_Receipt_Id
838 );
839 END IF;
840
841 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
842
843 END IF;
844
845
846 -- Loop over fetched records
847
848 FOR l_implicit_rec IN l_Shipping_Org_csr LOOP
849
850
851 l_Shipping_Org_rec.Sr_Source_Id := l_implicit_rec.SR_SOURCE_ID;
852 l_Shipping_Org_rec.Allocation_Percent := l_implicit_rec.ALLOCATION_PERCENT;
853 l_Shipping_Org_rec.Attribute1 := l_implicit_rec.ATTRIBUTE1;
854 l_Shipping_Org_rec.Attribute10 := l_implicit_rec.ATTRIBUTE10;
855 l_Shipping_Org_rec.Attribute11 := l_implicit_rec.ATTRIBUTE11;
856 l_Shipping_Org_rec.Attribute12 := l_implicit_rec.ATTRIBUTE12;
857 l_Shipping_Org_rec.Attribute13 := l_implicit_rec.ATTRIBUTE13;
858 l_Shipping_Org_rec.Attribute14 := l_implicit_rec.ATTRIBUTE14;
859 l_Shipping_Org_rec.Attribute15 := l_implicit_rec.ATTRIBUTE15;
860 l_Shipping_Org_rec.Attribute2 := l_implicit_rec.ATTRIBUTE2;
861 l_Shipping_Org_rec.Attribute3 := l_implicit_rec.ATTRIBUTE3;
862 l_Shipping_Org_rec.Attribute4 := l_implicit_rec.ATTRIBUTE4;
863 l_Shipping_Org_rec.Attribute5 := l_implicit_rec.ATTRIBUTE5;
864 l_Shipping_Org_rec.Attribute6 := l_implicit_rec.ATTRIBUTE6;
865 l_Shipping_Org_rec.Attribute7 := l_implicit_rec.ATTRIBUTE7;
866 l_Shipping_Org_rec.Attribute8 := l_implicit_rec.ATTRIBUTE8;
867 l_Shipping_Org_rec.Attribute9 := l_implicit_rec.ATTRIBUTE9;
868 l_Shipping_Org_rec.Attribute_Category := l_implicit_rec.ATTRIBUTE_CATEGORY;
869 l_Shipping_Org_rec.Created_By := l_implicit_rec.CREATED_BY;
870 l_Shipping_Org_rec.Creation_Date := l_implicit_rec.CREATION_DATE;
871 l_Shipping_Org_rec.Last_Updated_By := l_implicit_rec.LAST_UPDATED_BY;
872 l_Shipping_Org_rec.Last_Update_Date := l_implicit_rec.LAST_UPDATE_DATE;
873 l_Shipping_Org_rec.Last_Update_Login := l_implicit_rec.LAST_UPDATE_LOGIN;
874 l_Shipping_Org_rec.Program_Application_Id := l_implicit_rec.PROGRAM_APPLICATION_ID;
875 l_Shipping_Org_rec.Program_Id := l_implicit_rec.PROGRAM_ID;
876 l_Shipping_Org_rec.Program_Update_Date := l_implicit_rec.PROGRAM_UPDATE_DATE;
877 l_Shipping_Org_rec.Rank := l_implicit_rec.RANK;
878 l_Shipping_Org_rec.Request_Id := l_implicit_rec.REQUEST_ID;
879 l_Shipping_Org_rec.Secondary_Inventory := l_implicit_rec.SECONDARY_INVENTORY;
880 l_Shipping_Org_rec.Ship_Method := l_implicit_rec.SHIP_METHOD;
881 l_Shipping_Org_rec.Source_Organization_Id := l_implicit_rec.SOURCE_ORGANIZATION_ID;
882 l_Shipping_Org_rec.Source_Type := l_implicit_rec.SOURCE_TYPE;
883 l_Shipping_Org_rec.Sr_Receipt_Id := l_implicit_rec.SR_RECEIPT_ID;
884 l_Shipping_Org_rec.Vendor_Id := l_implicit_rec.VENDOR_ID;
885 l_Shipping_Org_rec.Vendor_Site_Id := l_implicit_rec.VENDOR_SITE_ID;
886
887 l_rows_fetched := l_Shipping_Org_csr%FOUND;
888
889 x_Shipping_Org_tbl(l_Shipping_Org_csr%ROWCOUNT) := l_Shipping_Org_rec;
890 x_Shipping_Org_val_tbl(l_Shipping_Org_csr%ROWCOUNT) := l_Shipping_Org_val_rec;
891
892 END LOOP;
893
894
895 -- PK sent and no rows found
896
897 IF
898 (p_Sr_Source_Id IS NOT NULL
899 AND
900 p_Sr_Source_Id <> FND_API.G_MISS_NUM)
901 AND
902 (NOT l_rows_fetched)
903 THEN
904 RAISE NO_DATA_FOUND;
905 END IF;
906
907 EXCEPTION
908
909 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
910
911 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
912
913 WHEN OTHERS THEN
914
915 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
916 THEN
917 FND_MSG_PUB.Add_Exc_Msg
918 ( G_PKG_NAME
919 , 'Query_Entities'
920 );
921 END IF;
922
923 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
924
925 END Query_Entities;
926
927 END MRP_Shipping_Org_Handlers;