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