DBA Data[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;