[Home] [Help]
PACKAGE BODY: APPS.MRP_SOURCING_RULE_HANDLERS
Source
1 PACKAGE BODY MRP_Sourcing_Rule_Handlers AS
2 /* $Header: MRPHSRLB.pls 115.1 99/07/16 12:23:27 porting ship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'MRP_Sourcing_Rule_Handlers';
7
8 -- Procedure Update_Row
9
10 PROCEDURE Update_Row
11 ( p_Sourcing_Rule_rec IN MRP_Sourcing_Rule_PUB.Sourcing_Rule_Rec_Type
12 )
13 IS
14 BEGIN
15
16 UPDATE MRP_SOURCING_RULES
17 SET SOURCING_RULE_ID = p_Sourcing_Rule_rec.Sourcing_Rule_Id
18 , ATTRIBUTE1 = p_Sourcing_Rule_rec.Attribute1
19 , ATTRIBUTE10 = p_Sourcing_Rule_rec.Attribute10
20 , ATTRIBUTE11 = p_Sourcing_Rule_rec.Attribute11
21 , ATTRIBUTE12 = p_Sourcing_Rule_rec.Attribute12
22 , ATTRIBUTE13 = p_Sourcing_Rule_rec.Attribute13
23 , ATTRIBUTE14 = p_Sourcing_Rule_rec.Attribute14
24 , ATTRIBUTE15 = p_Sourcing_Rule_rec.Attribute15
25 , ATTRIBUTE2 = p_Sourcing_Rule_rec.Attribute2
26 , ATTRIBUTE3 = p_Sourcing_Rule_rec.Attribute3
27 , ATTRIBUTE4 = p_Sourcing_Rule_rec.Attribute4
28 , ATTRIBUTE5 = p_Sourcing_Rule_rec.Attribute5
29 , ATTRIBUTE6 = p_Sourcing_Rule_rec.Attribute6
30 , ATTRIBUTE7 = p_Sourcing_Rule_rec.Attribute7
31 , ATTRIBUTE8 = p_Sourcing_Rule_rec.Attribute8
32 , ATTRIBUTE9 = p_Sourcing_Rule_rec.Attribute9
33 , ATTRIBUTE_CATEGORY = p_Sourcing_Rule_rec.Attribute_Category
34 , CREATED_BY = p_Sourcing_Rule_rec.Created_By
35 , CREATION_DATE = p_Sourcing_Rule_rec.Creation_Date
36 , DESCRIPTION = p_Sourcing_Rule_rec.Description
37 , LAST_UPDATED_BY = p_Sourcing_Rule_rec.Last_Updated_By
38 , LAST_UPDATE_DATE = p_Sourcing_Rule_rec.Last_Update_Date
39 , LAST_UPDATE_LOGIN = p_Sourcing_Rule_rec.Last_Update_Login
40 , ORGANIZATION_ID = p_Sourcing_Rule_rec.Organization_Id
41 , PLANNING_ACTIVE = p_Sourcing_Rule_rec.Planning_Active
42 , PROGRAM_APPLICATION_ID = p_Sourcing_Rule_rec.Program_Application_Id
43 , PROGRAM_ID = p_Sourcing_Rule_rec.Program_Id
44 , PROGRAM_UPDATE_DATE = p_Sourcing_Rule_rec.Program_Update_Date
45 , REQUEST_ID = p_Sourcing_Rule_rec.Request_Id
46 , SOURCING_RULE_NAME = p_Sourcing_Rule_rec.Sourcing_Rule_Name
47 , SOURCING_RULE_TYPE = p_Sourcing_Rule_rec.Sourcing_Rule_Type
48 , STATUS = p_Sourcing_Rule_rec.Status
49 WHERE SOURCING_RULE_ID = p_Sourcing_Rule_rec.Sourcing_Rule_Id
50 ;
51
52 EXCEPTION
53
54 WHEN OTHERS THEN
55
56 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
57 THEN
58 FND_MSG_PUB.Add_Exc_Msg
59 ( G_PKG_NAME
60 , 'Update_Row'
61 );
62 END IF;
63
64 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
65
66 END Update_Row;
67
68 -- Procedure Insert_Row
69
70 PROCEDURE Insert_Row
71 ( p_Sourcing_Rule_rec IN MRP_Sourcing_Rule_PUB.Sourcing_Rule_Rec_Type
72 )
73 IS
74 BEGIN
75
76 INSERT INTO MRP_SOURCING_RULES
77 ( SOURCING_RULE_ID
78 , ATTRIBUTE1
79 , ATTRIBUTE10
80 , ATTRIBUTE11
81 , ATTRIBUTE12
82 , ATTRIBUTE13
83 , ATTRIBUTE14
84 , ATTRIBUTE15
85 , ATTRIBUTE2
86 , ATTRIBUTE3
87 , ATTRIBUTE4
88 , ATTRIBUTE5
89 , ATTRIBUTE6
90 , ATTRIBUTE7
91 , ATTRIBUTE8
92 , ATTRIBUTE9
93 , ATTRIBUTE_CATEGORY
94 , CREATED_BY
95 , CREATION_DATE
96 , DESCRIPTION
97 , LAST_UPDATED_BY
98 , LAST_UPDATE_DATE
99 , LAST_UPDATE_LOGIN
100 , ORGANIZATION_ID
101 , PLANNING_ACTIVE
102 , PROGRAM_APPLICATION_ID
103 , PROGRAM_ID
104 , PROGRAM_UPDATE_DATE
105 , REQUEST_ID
106 , SOURCING_RULE_NAME
107 , SOURCING_RULE_TYPE
108 , STATUS
109 )
110 VALUES
111 ( p_Sourcing_Rule_rec.Sourcing_Rule_Id
112 , p_Sourcing_Rule_rec.Attribute1
113 , p_Sourcing_Rule_rec.Attribute10
114 , p_Sourcing_Rule_rec.Attribute11
115 , p_Sourcing_Rule_rec.Attribute12
116 , p_Sourcing_Rule_rec.Attribute13
117 , p_Sourcing_Rule_rec.Attribute14
118 , p_Sourcing_Rule_rec.Attribute15
119 , p_Sourcing_Rule_rec.Attribute2
120 , p_Sourcing_Rule_rec.Attribute3
121 , p_Sourcing_Rule_rec.Attribute4
122 , p_Sourcing_Rule_rec.Attribute5
123 , p_Sourcing_Rule_rec.Attribute6
124 , p_Sourcing_Rule_rec.Attribute7
125 , p_Sourcing_Rule_rec.Attribute8
126 , p_Sourcing_Rule_rec.Attribute9
127 , p_Sourcing_Rule_rec.Attribute_Category
128 , p_Sourcing_Rule_rec.Created_By
129 , p_Sourcing_Rule_rec.Creation_Date
130 , p_Sourcing_Rule_rec.Description
131 , p_Sourcing_Rule_rec.Last_Updated_By
132 , p_Sourcing_Rule_rec.Last_Update_Date
133 , p_Sourcing_Rule_rec.Last_Update_Login
134 , p_Sourcing_Rule_rec.Organization_Id
135 , p_Sourcing_Rule_rec.Planning_Active
136 , p_Sourcing_Rule_rec.Program_Application_Id
137 , p_Sourcing_Rule_rec.Program_Id
138 , p_Sourcing_Rule_rec.Program_Update_Date
139 , p_Sourcing_Rule_rec.Request_Id
140 , p_Sourcing_Rule_rec.Sourcing_Rule_Name
141 , p_Sourcing_Rule_rec.Sourcing_Rule_Type
142 , p_Sourcing_Rule_rec.Status
143 );
144
145 EXCEPTION
146
147 WHEN OTHERS THEN
148
149 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
150 THEN
151 FND_MSG_PUB.Add_Exc_Msg
152 ( G_PKG_NAME
153 , 'Insert_Row'
154 );
155 END IF;
156
157 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
158
159 END Insert_Row;
160
161 -- Procedure Delete_Row
162
163 PROCEDURE Delete_Row
164 ( p_Sourcing_Rule_Id IN NUMBER
165 )
166 IS
167
168 l_sr_receipt_id NUMBER := FND_API.G_MISS_NUM;
169
170 CURSOR cur_Receiving_Org IS
171 SELECT SR_RECEIPT_ID
172 FROM MRP_SR_RECEIPT_ORG
173 WHERE SOURCING_RULE_ID = p_Sourcing_Rule_Id;
174
175 BEGIN
176
177 -- Before we delete a sourcing Rule/BOD, we need to
178 -- delete all the associated receiving orgs and
179 -- shipping orgs (Note that deleting a shipping org
180 -- is taken care of by the delete handler for rec. org
181
182 OPEN cur_Receiving_Org;
183
184 WHILE TRUE LOOP
185 FETCH cur_Receiving_Org
186 INTO l_sr_receipt_id;
187
188 EXIT WHEN cur_Receiving_Org%NOTFOUND;
189
190 MRP_Receiving_Org_Handlers.Delete_Row (
191 p_Sr_Receipt_Id => l_sr_receipt_id );
192
193 END LOOP;
194
195 CLOSE cur_Receiving_Org;
196
197 DELETE FROM MRP_SOURCING_RULES
198 WHERE SOURCING_RULE_ID = p_Sourcing_Rule_Id;
199
200 EXCEPTION
201
202 WHEN OTHERS THEN
203
204 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
205 THEN
206 FND_MSG_PUB.Add_Exc_Msg
207 ( G_PKG_NAME
208 , 'Delete_Row'
209 );
210 END IF;
211
212 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
213
214 END Delete_Row;
215
216 -- Procedure lock_Row
217 --
218
219 PROCEDURE Lock_Row
220 ( x_return_status OUT VARCHAR2
221 , p_Sourcing_Rule_rec IN MRP_Sourcing_Rule_PUB.Sourcing_Rule_Rec_Type
222 , x_Sourcing_Rule_rec OUT MRP_Sourcing_Rule_PUB.Sourcing_Rule_Rec_Type
223 )
224 IS
225 l_Sourcing_Rule_rec MRP_Sourcing_Rule_PUB.Sourcing_Rule_Rec_Type;
226 BEGIN
227
228 SELECT SOURCING_RULE_ID
229 , ATTRIBUTE1
230 , ATTRIBUTE10
231 , ATTRIBUTE11
232 , ATTRIBUTE12
233 , ATTRIBUTE13
234 , ATTRIBUTE14
235 , ATTRIBUTE15
236 , ATTRIBUTE2
237 , ATTRIBUTE3
238 , ATTRIBUTE4
239 , ATTRIBUTE5
240 , ATTRIBUTE6
241 , ATTRIBUTE7
242 , ATTRIBUTE8
243 , ATTRIBUTE9
244 , ATTRIBUTE_CATEGORY
245 , CREATED_BY
246 , CREATION_DATE
247 , DESCRIPTION
248 , LAST_UPDATED_BY
249 , LAST_UPDATE_DATE
250 , LAST_UPDATE_LOGIN
251 , ORGANIZATION_ID
252 , PLANNING_ACTIVE
253 , PROGRAM_APPLICATION_ID
254 , PROGRAM_ID
255 , PROGRAM_UPDATE_DATE
256 , REQUEST_ID
257 , SOURCING_RULE_NAME
258 , SOURCING_RULE_TYPE
259 , STATUS
260 INTO l_Sourcing_Rule_rec.Sourcing_Rule_Id
261 , l_Sourcing_Rule_rec.Attribute1
262 , l_Sourcing_Rule_rec.Attribute10
263 , l_Sourcing_Rule_rec.Attribute11
264 , l_Sourcing_Rule_rec.Attribute12
265 , l_Sourcing_Rule_rec.Attribute13
266 , l_Sourcing_Rule_rec.Attribute14
267 , l_Sourcing_Rule_rec.Attribute15
268 , l_Sourcing_Rule_rec.Attribute2
269 , l_Sourcing_Rule_rec.Attribute3
270 , l_Sourcing_Rule_rec.Attribute4
271 , l_Sourcing_Rule_rec.Attribute5
272 , l_Sourcing_Rule_rec.Attribute6
273 , l_Sourcing_Rule_rec.Attribute7
274 , l_Sourcing_Rule_rec.Attribute8
275 , l_Sourcing_Rule_rec.Attribute9
276 , l_Sourcing_Rule_rec.Attribute_Category
277 , l_Sourcing_Rule_rec.Created_By
278 , l_Sourcing_Rule_rec.Creation_Date
279 , l_Sourcing_Rule_rec.Description
280 , l_Sourcing_Rule_rec.Last_Updated_By
281 , l_Sourcing_Rule_rec.Last_Update_Date
282 , l_Sourcing_Rule_rec.Last_Update_Login
283 , l_Sourcing_Rule_rec.Organization_Id
284 , l_Sourcing_Rule_rec.Planning_Active
285 , l_Sourcing_Rule_rec.Program_Application_Id
286 , l_Sourcing_Rule_rec.Program_Id
287 , l_Sourcing_Rule_rec.Program_Update_Date
288 , l_Sourcing_Rule_rec.Request_Id
289 , l_Sourcing_Rule_rec.Sourcing_Rule_Name
290 , l_Sourcing_Rule_rec.Sourcing_Rule_Type
291 , l_Sourcing_Rule_rec.Status
292 FROM MRP_SOURCING_RULES
293 WHERE SOURCING_RULE_ID = p_Sourcing_Rule_rec.Sourcing_Rule_Id
294 FOR UPDATE NOWAIT;
295
296 -- Row locked. Compare IN attributes to DB attributes.
297
298 IF ( (l_Sourcing_Rule_rec.Sourcing_Rule_Id =
299 p_Sourcing_Rule_rec.Sourcing_Rule_Id) OR
300 ((p_Sourcing_Rule_rec.Sourcing_Rule_Id = FND_API.G_MISS_NUM) OR
301 ( (l_Sourcing_Rule_rec.Sourcing_Rule_Id IS NULL) AND
302 (p_Sourcing_Rule_rec.Sourcing_Rule_Id IS NULL))))
303 AND ( (l_Sourcing_Rule_rec.Attribute1 =
304 p_Sourcing_Rule_rec.Attribute1) OR
305 ((p_Sourcing_Rule_rec.Attribute1 = FND_API.G_MISS_CHAR) OR
306 ( (l_Sourcing_Rule_rec.Attribute1 IS NULL) AND
307 (p_Sourcing_Rule_rec.Attribute1 IS NULL))))
308 AND ( (l_Sourcing_Rule_rec.Attribute10 =
309 p_Sourcing_Rule_rec.Attribute10) OR
310 ((p_Sourcing_Rule_rec.Attribute10 = FND_API.G_MISS_CHAR) OR
311 ( (l_Sourcing_Rule_rec.Attribute10 IS NULL) AND
312 (p_Sourcing_Rule_rec.Attribute10 IS NULL))))
313 AND ( (l_Sourcing_Rule_rec.Attribute11 =
314 p_Sourcing_Rule_rec.Attribute11) OR
315 ((p_Sourcing_Rule_rec.Attribute11 = FND_API.G_MISS_CHAR) OR
316 ( (l_Sourcing_Rule_rec.Attribute11 IS NULL) AND
317 (p_Sourcing_Rule_rec.Attribute11 IS NULL))))
318 AND ( (l_Sourcing_Rule_rec.Attribute12 =
319 p_Sourcing_Rule_rec.Attribute12) OR
320 ((p_Sourcing_Rule_rec.Attribute12 = FND_API.G_MISS_CHAR) OR
321 ( (l_Sourcing_Rule_rec.Attribute12 IS NULL) AND
322 (p_Sourcing_Rule_rec.Attribute12 IS NULL))))
323 AND ( (l_Sourcing_Rule_rec.Attribute13 =
324 p_Sourcing_Rule_rec.Attribute13) OR
325 ((p_Sourcing_Rule_rec.Attribute13 = FND_API.G_MISS_CHAR) OR
326 ( (l_Sourcing_Rule_rec.Attribute13 IS NULL) AND
327 (p_Sourcing_Rule_rec.Attribute13 IS NULL))))
328 AND ( (l_Sourcing_Rule_rec.Attribute14 =
329 p_Sourcing_Rule_rec.Attribute14) OR
330 ((p_Sourcing_Rule_rec.Attribute14 = FND_API.G_MISS_CHAR) OR
331 ( (l_Sourcing_Rule_rec.Attribute14 IS NULL) AND
332 (p_Sourcing_Rule_rec.Attribute14 IS NULL))))
333 AND ( (l_Sourcing_Rule_rec.Attribute15 =
334 p_Sourcing_Rule_rec.Attribute15) OR
335 ((p_Sourcing_Rule_rec.Attribute15 = FND_API.G_MISS_CHAR) OR
336 ( (l_Sourcing_Rule_rec.Attribute15 IS NULL) AND
337 (p_Sourcing_Rule_rec.Attribute15 IS NULL))))
338 AND ( (l_Sourcing_Rule_rec.Attribute2 =
339 p_Sourcing_Rule_rec.Attribute2) OR
340 ((p_Sourcing_Rule_rec.Attribute2 = FND_API.G_MISS_CHAR) OR
341 ( (l_Sourcing_Rule_rec.Attribute2 IS NULL) AND
342 (p_Sourcing_Rule_rec.Attribute2 IS NULL))))
343 AND ( (l_Sourcing_Rule_rec.Attribute3 =
344 p_Sourcing_Rule_rec.Attribute3) OR
345 ((p_Sourcing_Rule_rec.Attribute3 = FND_API.G_MISS_CHAR) OR
346 ( (l_Sourcing_Rule_rec.Attribute3 IS NULL) AND
347 (p_Sourcing_Rule_rec.Attribute3 IS NULL))))
348 AND ( (l_Sourcing_Rule_rec.Attribute4 =
349 p_Sourcing_Rule_rec.Attribute4) OR
350 ((p_Sourcing_Rule_rec.Attribute4 = FND_API.G_MISS_CHAR) OR
351 ( (l_Sourcing_Rule_rec.Attribute4 IS NULL) AND
352 (p_Sourcing_Rule_rec.Attribute4 IS NULL))))
353 AND ( (l_Sourcing_Rule_rec.Attribute5 =
354 p_Sourcing_Rule_rec.Attribute5) OR
355 ((p_Sourcing_Rule_rec.Attribute5 = FND_API.G_MISS_CHAR) OR
356 ( (l_Sourcing_Rule_rec.Attribute5 IS NULL) AND
357 (p_Sourcing_Rule_rec.Attribute5 IS NULL))))
358 AND ( (l_Sourcing_Rule_rec.Attribute6 =
359 p_Sourcing_Rule_rec.Attribute6) OR
360 ((p_Sourcing_Rule_rec.Attribute6 = FND_API.G_MISS_CHAR) OR
361 ( (l_Sourcing_Rule_rec.Attribute6 IS NULL) AND
362 (p_Sourcing_Rule_rec.Attribute6 IS NULL))))
363 AND ( (l_Sourcing_Rule_rec.Attribute7 =
364 p_Sourcing_Rule_rec.Attribute7) OR
365 ((p_Sourcing_Rule_rec.Attribute7 = FND_API.G_MISS_CHAR) OR
366 ( (l_Sourcing_Rule_rec.Attribute7 IS NULL) AND
367 (p_Sourcing_Rule_rec.Attribute7 IS NULL))))
368 AND ( (l_Sourcing_Rule_rec.Attribute8 =
369 p_Sourcing_Rule_rec.Attribute8) OR
370 ((p_Sourcing_Rule_rec.Attribute8 = FND_API.G_MISS_CHAR) OR
371 ( (l_Sourcing_Rule_rec.Attribute8 IS NULL) AND
372 (p_Sourcing_Rule_rec.Attribute8 IS NULL))))
373 AND ( (l_Sourcing_Rule_rec.Attribute9 =
374 p_Sourcing_Rule_rec.Attribute9) OR
375 ((p_Sourcing_Rule_rec.Attribute9 = FND_API.G_MISS_CHAR) OR
376 ( (l_Sourcing_Rule_rec.Attribute9 IS NULL) AND
377 (p_Sourcing_Rule_rec.Attribute9 IS NULL))))
378 AND ( (l_Sourcing_Rule_rec.Attribute_Category =
379 p_Sourcing_Rule_rec.Attribute_Category) OR
380 ((p_Sourcing_Rule_rec.Attribute_Category = FND_API.G_MISS_CHAR) OR
381 ( (l_Sourcing_Rule_rec.Attribute_Category IS NULL) AND
382 (p_Sourcing_Rule_rec.Attribute_Category IS NULL))))
383 AND ( (l_Sourcing_Rule_rec.Created_By =
384 p_Sourcing_Rule_rec.Created_By) OR
385 ((p_Sourcing_Rule_rec.Created_By = FND_API.G_MISS_NUM) OR
386 ( (l_Sourcing_Rule_rec.Created_By IS NULL) AND
387 (p_Sourcing_Rule_rec.Created_By IS NULL))))
388 AND ( (l_Sourcing_Rule_rec.Creation_Date =
389 p_Sourcing_Rule_rec.Creation_Date) OR
390 ((p_Sourcing_Rule_rec.Creation_Date = FND_API.G_MISS_DATE) OR
391 ( (l_Sourcing_Rule_rec.Creation_Date IS NULL) AND
392 (p_Sourcing_Rule_rec.Creation_Date IS NULL))))
393 AND ( (l_Sourcing_Rule_rec.Description =
394 p_Sourcing_Rule_rec.Description) OR
395 ((p_Sourcing_Rule_rec.Description = FND_API.G_MISS_CHAR) OR
396 ( (l_Sourcing_Rule_rec.Description IS NULL) AND
397 (p_Sourcing_Rule_rec.Description IS NULL))))
398 AND ( (l_Sourcing_Rule_rec.Last_Updated_By =
399 p_Sourcing_Rule_rec.Last_Updated_By) OR
400 ((p_Sourcing_Rule_rec.Last_Updated_By = FND_API.G_MISS_NUM) OR
401 ( (l_Sourcing_Rule_rec.Last_Updated_By IS NULL) AND
402 (p_Sourcing_Rule_rec.Last_Updated_By IS NULL))))
403 AND ( (l_Sourcing_Rule_rec.Last_Update_Date =
404 p_Sourcing_Rule_rec.Last_Update_Date) OR
405 ((p_Sourcing_Rule_rec.Last_Update_Date = FND_API.G_MISS_DATE) OR
406 ( (l_Sourcing_Rule_rec.Last_Update_Date IS NULL) AND
407 (p_Sourcing_Rule_rec.Last_Update_Date IS NULL))))
408 AND ( (l_Sourcing_Rule_rec.Last_Update_Login =
409 p_Sourcing_Rule_rec.Last_Update_Login) OR
410 ((p_Sourcing_Rule_rec.Last_Update_Login = FND_API.G_MISS_NUM) OR
411 ( (l_Sourcing_Rule_rec.Last_Update_Login IS NULL) AND
412 (p_Sourcing_Rule_rec.Last_Update_Login IS NULL))))
413 AND ( (l_Sourcing_Rule_rec.Organization_Id =
414 p_Sourcing_Rule_rec.Organization_Id) OR
415 ((p_Sourcing_Rule_rec.Organization_Id = FND_API.G_MISS_NUM) OR
416 ( (l_Sourcing_Rule_rec.Organization_Id IS NULL) AND
417 (p_Sourcing_Rule_rec.Organization_Id IS NULL))))
418 AND ( (l_Sourcing_Rule_rec.Planning_Active =
419 p_Sourcing_Rule_rec.Planning_Active) OR
420 ((p_Sourcing_Rule_rec.Planning_Active = FND_API.G_MISS_NUM) OR
421 ( (l_Sourcing_Rule_rec.Planning_Active IS NULL) AND
422 (p_Sourcing_Rule_rec.Planning_Active IS NULL))))
423 AND ( (l_Sourcing_Rule_rec.Program_Application_Id =
424 p_Sourcing_Rule_rec.Program_Application_Id) OR
425 ((p_Sourcing_Rule_rec.Program_Application_Id = FND_API.G_MISS_NUM) OR
426 ( (l_Sourcing_Rule_rec.Program_Application_Id IS NULL) AND
427 (p_Sourcing_Rule_rec.Program_Application_Id IS NULL))))
428 AND ( (l_Sourcing_Rule_rec.Program_Id =
429 p_Sourcing_Rule_rec.Program_Id) OR
430 ((p_Sourcing_Rule_rec.Program_Id = FND_API.G_MISS_NUM) OR
431 ( (l_Sourcing_Rule_rec.Program_Id IS NULL) AND
432 (p_Sourcing_Rule_rec.Program_Id IS NULL))))
433 AND ( (l_Sourcing_Rule_rec.Program_Update_Date =
434 p_Sourcing_Rule_rec.Program_Update_Date) OR
435 ((p_Sourcing_Rule_rec.Program_Update_Date = FND_API.G_MISS_DATE) OR
436 ( (l_Sourcing_Rule_rec.Program_Update_Date IS NULL) AND
437 (p_Sourcing_Rule_rec.Program_Update_Date IS NULL))))
438 AND ( (l_Sourcing_Rule_rec.Request_Id =
439 p_Sourcing_Rule_rec.Request_Id) OR
440 ((p_Sourcing_Rule_rec.Request_Id = FND_API.G_MISS_NUM) OR
441 ( (l_Sourcing_Rule_rec.Request_Id IS NULL) AND
442 (p_Sourcing_Rule_rec.Request_Id IS NULL))))
443 AND ( (l_Sourcing_Rule_rec.Sourcing_Rule_Name =
444 p_Sourcing_Rule_rec.Sourcing_Rule_Name) OR
445 ((p_Sourcing_Rule_rec.Sourcing_Rule_Name = FND_API.G_MISS_CHAR) OR
446 ( (l_Sourcing_Rule_rec.Sourcing_Rule_Name IS NULL) AND
447 (p_Sourcing_Rule_rec.Sourcing_Rule_Name IS NULL))))
448 AND ( (l_Sourcing_Rule_rec.Sourcing_Rule_Type =
449 p_Sourcing_Rule_rec.Sourcing_Rule_Type) OR
450 ((p_Sourcing_Rule_rec.Sourcing_Rule_Type = FND_API.G_MISS_NUM) OR
451 ( (l_Sourcing_Rule_rec.Sourcing_Rule_Type IS NULL) AND
452 (p_Sourcing_Rule_rec.Sourcing_Rule_Type IS NULL))))
453 AND ( (l_Sourcing_Rule_rec.Status =
454 p_Sourcing_Rule_rec.Status) OR
455 ((p_Sourcing_Rule_rec.Status = FND_API.G_MISS_NUM) OR
456 ( (l_Sourcing_Rule_rec.Status IS NULL) AND
457 (p_Sourcing_Rule_rec.Status IS NULL))))
458 THEN
459
460 -- Row has not changed. Set out parameter.
461
462 x_Sourcing_Rule_rec := l_Sourcing_Rule_rec;
463
464 -- Set return status
465
466 x_return_status := FND_API.G_RET_STS_SUCCESS;
467 x_Sourcing_Rule_rec.return_status := FND_API.G_RET_STS_SUCCESS;
468
469 ELSE
470
471 -- Row has changed by another user.
472
473 x_return_status := FND_API.G_RET_STS_ERROR;
474 x_Sourcing_Rule_rec.return_status := FND_API.G_RET_STS_ERROR;
475
476 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
477 THEN
478
479 FND_MESSAGE.SET_NAME('MRP','BOI_LOCK_ROW_CHANGED');
480 FND_MSG_PUB.Add;
481
482 END IF;
483
484 END IF;
485
486 EXCEPTION
487
488 WHEN NO_DATA_FOUND THEN
489
490 x_return_status := FND_API.G_RET_STS_ERROR;
491 x_Sourcing_Rule_rec.return_status := FND_API.G_RET_STS_ERROR;
492
493 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
494 THEN
495
496 FND_MESSAGE.SET_NAME('MRP','BOI_LOCK_ROW_DELETED');
497 FND_MSG_PUB.Add;
498
499 END IF;
500 WHEN APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION THEN
501
502 x_return_status := FND_API.G_RET_STS_ERROR;
503 x_Sourcing_Rule_rec.return_status := FND_API.G_RET_STS_ERROR;
504
505 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
506 THEN
507
508 FND_MESSAGE.SET_NAME('MRP','BOI_LOCK_ROW_ALREADY_LOCKED');
509 FND_MSG_PUB.Add;
510
511 END IF;
512 WHEN OTHERS THEN
513
514 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
515 x_Sourcing_Rule_rec.return_status := FND_API.G_RET_STS_UNEXP_ERROR;
516
517 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
518 THEN
519 FND_MSG_PUB.Add_Exc_Msg
520 ( G_PKG_NAME
521 , 'Lock_Row'
522 );
523 END IF;
524
525 END Lock_Row;
526
527 -- Function Query_Row
528
529 FUNCTION Query_Row
530 ( p_Sourcing_Rule_Id IN NUMBER
531 ) RETURN MRP_Sourcing_Rule_PUB.Sourcing_Rule_Rec_Type
532 IS
533 l_Sourcing_Rule_rec MRP_Sourcing_Rule_PUB.Sourcing_Rule_Rec_Type;
534 BEGIN
535
536 SELECT SOURCING_RULE_ID
537 , ATTRIBUTE1
538 , ATTRIBUTE10
539 , ATTRIBUTE11
540 , ATTRIBUTE12
541 , ATTRIBUTE13
542 , ATTRIBUTE14
543 , ATTRIBUTE15
544 , ATTRIBUTE2
545 , ATTRIBUTE3
546 , ATTRIBUTE4
547 , ATTRIBUTE5
548 , ATTRIBUTE6
549 , ATTRIBUTE7
550 , ATTRIBUTE8
551 , ATTRIBUTE9
552 , ATTRIBUTE_CATEGORY
553 , CREATED_BY
554 , CREATION_DATE
555 , DESCRIPTION
556 , LAST_UPDATED_BY
557 , LAST_UPDATE_DATE
558 , LAST_UPDATE_LOGIN
559 , ORGANIZATION_ID
560 , PLANNING_ACTIVE
561 , PROGRAM_APPLICATION_ID
562 , PROGRAM_ID
563 , PROGRAM_UPDATE_DATE
564 , REQUEST_ID
565 , SOURCING_RULE_NAME
566 , SOURCING_RULE_TYPE
567 , STATUS
568 INTO l_Sourcing_Rule_rec.Sourcing_Rule_Id
569 , l_Sourcing_Rule_rec.Attribute1
570 , l_Sourcing_Rule_rec.Attribute10
571 , l_Sourcing_Rule_rec.Attribute11
572 , l_Sourcing_Rule_rec.Attribute12
573 , l_Sourcing_Rule_rec.Attribute13
574 , l_Sourcing_Rule_rec.Attribute14
575 , l_Sourcing_Rule_rec.Attribute15
576 , l_Sourcing_Rule_rec.Attribute2
577 , l_Sourcing_Rule_rec.Attribute3
578 , l_Sourcing_Rule_rec.Attribute4
579 , l_Sourcing_Rule_rec.Attribute5
580 , l_Sourcing_Rule_rec.Attribute6
581 , l_Sourcing_Rule_rec.Attribute7
582 , l_Sourcing_Rule_rec.Attribute8
583 , l_Sourcing_Rule_rec.Attribute9
584 , l_Sourcing_Rule_rec.Attribute_Category
585 , l_Sourcing_Rule_rec.Created_By
586 , l_Sourcing_Rule_rec.Creation_Date
587 , l_Sourcing_Rule_rec.Description
588 , l_Sourcing_Rule_rec.Last_Updated_By
589 , l_Sourcing_Rule_rec.Last_Update_Date
590 , l_Sourcing_Rule_rec.Last_Update_Login
591 , l_Sourcing_Rule_rec.Organization_Id
592 , l_Sourcing_Rule_rec.Planning_Active
593 , l_Sourcing_Rule_rec.Program_Application_Id
594 , l_Sourcing_Rule_rec.Program_Id
595 , l_Sourcing_Rule_rec.Program_Update_Date
596 , l_Sourcing_Rule_rec.Request_Id
597 , l_Sourcing_Rule_rec.Sourcing_Rule_Name
598 , l_Sourcing_Rule_rec.Sourcing_Rule_Type
599 , l_Sourcing_Rule_rec.Status
600 FROM MRP_SOURCING_RULES
601 WHERE SOURCING_RULE_ID = p_Sourcing_Rule_Id
602 ;
603
604 RETURN l_Sourcing_Rule_rec;
605
606 EXCEPTION
607
608 WHEN OTHERS THEN
609
610 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
611 THEN
612 FND_MSG_PUB.Add_Exc_Msg
613 ( G_PKG_NAME
614 , 'Query_Row'
615 );
616 END IF;
617
618 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
619
620 END Query_Row;
621
622 -- Procedure Query_Entity
623
624 PROCEDURE Query_Entity
625 ( p_Sourcing_Rule_Id IN NUMBER
626 , x_Sourcing_Rule_rec OUT MRP_Sourcing_Rule_PUB.Sourcing_Rule_Rec_Type
627 , x_Sourcing_Rule_val_rec OUT MRP_Sourcing_Rule_PUB.Sourcing_Rule_Val_Rec_Type
628 )
629 IS
630 BEGIN
631
632 SELECT SOURCING_RULE_ID
633 , ATTRIBUTE1
634 , ATTRIBUTE10
635 , ATTRIBUTE11
636 , ATTRIBUTE12
637 , ATTRIBUTE13
638 , ATTRIBUTE14
639 , ATTRIBUTE15
640 , ATTRIBUTE2
641 , ATTRIBUTE3
642 , ATTRIBUTE4
643 , ATTRIBUTE5
644 , ATTRIBUTE6
645 , ATTRIBUTE7
646 , ATTRIBUTE8
647 , ATTRIBUTE9
648 , ATTRIBUTE_CATEGORY
649 , CREATED_BY
650 , CREATION_DATE
651 , DESCRIPTION
652 , LAST_UPDATED_BY
653 , LAST_UPDATE_DATE
654 , LAST_UPDATE_LOGIN
655 , ORGANIZATION_ID
656 , PLANNING_ACTIVE
657 , PROGRAM_APPLICATION_ID
658 , PROGRAM_ID
659 , PROGRAM_UPDATE_DATE
660 , REQUEST_ID
661 , SOURCING_RULE_NAME
662 , SOURCING_RULE_TYPE
663 , STATUS
664 INTO x_Sourcing_Rule_rec.Sourcing_Rule_Id
665 , x_Sourcing_Rule_rec.Attribute1
666 , x_Sourcing_Rule_rec.Attribute10
667 , x_Sourcing_Rule_rec.Attribute11
668 , x_Sourcing_Rule_rec.Attribute12
669 , x_Sourcing_Rule_rec.Attribute13
670 , x_Sourcing_Rule_rec.Attribute14
671 , x_Sourcing_Rule_rec.Attribute15
672 , x_Sourcing_Rule_rec.Attribute2
673 , x_Sourcing_Rule_rec.Attribute3
674 , x_Sourcing_Rule_rec.Attribute4
675 , x_Sourcing_Rule_rec.Attribute5
676 , x_Sourcing_Rule_rec.Attribute6
677 , x_Sourcing_Rule_rec.Attribute7
678 , x_Sourcing_Rule_rec.Attribute8
679 , x_Sourcing_Rule_rec.Attribute9
680 , x_Sourcing_Rule_rec.Attribute_Category
681 , x_Sourcing_Rule_rec.Created_By
682 , x_Sourcing_Rule_rec.Creation_Date
683 , x_Sourcing_Rule_rec.Description
684 , x_Sourcing_Rule_rec.Last_Updated_By
685 , x_Sourcing_Rule_rec.Last_Update_Date
686 , x_Sourcing_Rule_rec.Last_Update_Login
687 , x_Sourcing_Rule_rec.Organization_Id
688 , x_Sourcing_Rule_rec.Planning_Active
689 , x_Sourcing_Rule_rec.Program_Application_Id
690 , x_Sourcing_Rule_rec.Program_Id
691 , x_Sourcing_Rule_rec.Program_Update_Date
692 , x_Sourcing_Rule_rec.Request_Id
693 , x_Sourcing_Rule_rec.Sourcing_Rule_Name
694 , x_Sourcing_Rule_rec.Sourcing_Rule_Type
695 , x_Sourcing_Rule_rec.Status
696 FROM MRP_SOURCING_RULES
697 WHERE SOURCING_RULE_ID = p_Sourcing_Rule_Id
698 ;
699
700
701 EXCEPTION
702
703 WHEN OTHERS THEN
704
705 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
706 THEN
707 FND_MSG_PUB.Add_Exc_Msg
708 ( G_PKG_NAME
709 , 'Query_Entity'
710 );
711 END IF;
712
713 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
714
715 END Query_Entity;
716
717 END MRP_Sourcing_Rule_Handlers;