[Home] [Help]
PACKAGE BODY: APPS.CSP_FAILURE_RATES_PKG
Source
1 PACKAGE BODY CSP_FAILURE_RATES_PKG as
2 /* $Header: csptfrab.pls 115.3 2002/11/26 07:19:57 hhaugeru noship $ */
3 -- Start of Comments
4 -- Package name : CSP_FAILURE_RATES_PKG
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSP_FAILURE_RATES_PKG';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csptfrab.pls';
13
14 PROCEDURE Insert_Row(
15 px_FAILURE_RATE_ID IN OUT NOCOPY NUMBER
16 ,p_PRODUCT_ID NUMBER
17 ,p_INVENTORY_ITEM_ID NUMBER
18 ,p_CALCULATED_FAILURE_RATE NUMBER
19 ,p_MANUAL_FAILURE_RATE NUMBER
20 ,p_LAST_UPDATE_DATE DATE
21 ,p_LAST_UPDATED_BY NUMBER
22 ,p_CREATION_DATE DATE
23 ,p_CREATED_BY NUMBER
24 ,p_LAST_UPDATE_LOGIN NUMBER
25 ,p_PLANNING_PARAMETERS_ID NUMBER)
26 IS
27 CURSOR C2 IS SELECT CSP_FAILURE_RATES_S1.nextval FROM sys.dual;
28 BEGIN
29 If (px_FAILURE_RATE_ID IS NULL) OR (px_FAILURE_RATE_ID = FND_API.G_MISS_NUM) then
30 OPEN C2;
31 FETCH C2 INTO px_FAILURE_RATE_ID;
32 CLOSE C2;
33 End If;
34 INSERT INTO CSP_FAILURE_RATES(
35 FAILURE_RATE_ID
36 ,PRODUCT_ID
37 ,INVENTORY_ITEM_ID
38 ,CALCULATED_FAILURE_RATE
39 ,MANUAL_FAILURE_RATE
40 ,LAST_UPDATE_DATE
41 ,LAST_UPDATED_BY
42 ,CREATION_DATE
43 ,CREATED_BY
44 ,LAST_UPDATE_LOGIN
45 ,PLANNING_PARAMETERS_ID
46 ) VALUES (
47 px_FAILURE_RATE_ID
48 ,decode( p_PRODUCT_ID, FND_API.G_MISS_NUM, NULL, p_PRODUCT_ID)
49 ,decode( p_INVENTORY_ITEM_ID, FND_API.G_MISS_NUM, NULL, p_INVENTORY_ITEM_ID)
50 ,decode( p_CALCULATED_FAILURE_RATE, FND_API.G_MISS_NUM, NULL, p_CALCULATED_FAILURE_RATE)
51 ,decode( p_MANUAL_FAILURE_RATE, FND_API.G_MISS_NUM, NULL, p_MANUAL_FAILURE_RATE)
52 ,decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_LAST_UPDATE_DATE)
53 ,decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATED_BY)
54 ,decode( p_CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_CREATION_DATE)
55 ,decode( p_CREATED_BY, FND_API.G_MISS_NUM, NULL, p_CREATED_BY)
56 ,decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATE_LOGIN)
57 ,decode( p_PLANNING_PARAMETERS_ID, FND_API.G_MISS_NUM, NULL, p_PLANNING_PARAMETERS_ID));
58 End Insert_Row;
59 PROCEDURE Update_Row(
60 p_FAILURE_RATE_ID NUMBER
61 ,p_PRODUCT_ID NUMBER
62 ,p_INVENTORY_ITEM_ID NUMBER
63 ,p_CALCULATED_FAILURE_RATE NUMBER
64 ,p_MANUAL_FAILURE_RATE NUMBER
65 ,p_LAST_UPDATE_DATE DATE
66 ,p_LAST_UPDATED_BY NUMBER
67 ,p_CREATION_DATE DATE
68 ,p_CREATED_BY NUMBER
69 ,p_LAST_UPDATE_LOGIN NUMBER
70 ,p_PLANNING_PARAMETERS_ID NUMBER)
71 IS
72 BEGIN
73 Update CSP_FAILURE_RATES
74 SET
75 PRODUCT_ID = decode( p_PRODUCT_ID, FND_API.G_MISS_NUM, PRODUCT_ID, p_PRODUCT_ID)
76 ,INVENTORY_ITEM_ID = decode( p_INVENTORY_ITEM_ID, FND_API.G_MISS_NUM, INVENTORY_ITEM_ID, p_INVENTORY_ITEM_ID)
77 ,CALCULATED_FAILURE_RATE = decode( p_CALCULATED_FAILURE_RATE, FND_API.G_MISS_NUM, CALCULATED_FAILURE_RATE, p_CALCULATED_FAILURE_RATE)
78 ,MANUAL_FAILURE_RATE = decode( p_MANUAL_FAILURE_RATE, FND_API.G_MISS_NUM, MANUAL_FAILURE_RATE, p_MANUAL_FAILURE_RATE)
79 ,LAST_UPDATE_DATE = decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, LAST_UPDATE_DATE, p_LAST_UPDATE_DATE)
80 ,LAST_UPDATED_BY = decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, LAST_UPDATED_BY, p_LAST_UPDATED_BY)
81 ,CREATION_DATE = decode( p_CREATION_DATE, FND_API.G_MISS_DATE, CREATION_DATE, p_CREATION_DATE)
82 ,CREATED_BY = decode( p_CREATED_BY, FND_API.G_MISS_NUM, CREATED_BY, p_CREATED_BY)
83 ,LAST_UPDATE_LOGIN = decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, LAST_UPDATE_LOGIN, p_LAST_UPDATE_LOGIN)
84 ,PLANNING_PARAMETERS_ID = decode( p_PLANNING_PARAMETERS_ID, FND_API.G_MISS_NUM, PLANNING_PARAMETERS_ID, p_PLANNING_PARAMETERS_ID)
85 where FAILURE_RATE_ID = p_FAILURE_RATE_ID;
86 If (SQL%NOTFOUND) then
87 RAISE NO_DATA_FOUND;
88 End If;
89 END Update_Row;
90 PROCEDURE Delete_Row(
91 p_FAILURE_RATE_ID NUMBER)
92 IS
93 BEGIN
94 DELETE FROM CSP_FAILURE_RATES
95 WHERE FAILURE_RATE_ID = p_FAILURE_RATE_ID;
96 If (SQL%NOTFOUND) then
97 RAISE NO_DATA_FOUND;
98 End If;
99 END Delete_Row;
100 PROCEDURE Lock_Row(
101 p_FAILURE_RATE_ID NUMBER
102 ,p_PRODUCT_ID NUMBER
103 ,p_INVENTORY_ITEM_ID NUMBER
104 ,p_CALCULATED_FAILURE_RATE NUMBER
105 ,p_MANUAL_FAILURE_RATE NUMBER
106 ,p_LAST_UPDATE_DATE DATE
107 ,p_LAST_UPDATED_BY NUMBER
108 ,p_CREATION_DATE DATE
109 ,p_CREATED_BY NUMBER
110 ,p_LAST_UPDATE_LOGIN NUMBER
111 ,p_PLANNING_PARAMETERS_ID NUMBER)
112 IS
113 CURSOR C IS
114 SELECT *
115 FROM CSP_FAILURE_RATES
116 WHERE FAILURE_RATE_ID = p_FAILURE_RATE_ID
117 FOR UPDATE of FAILURE_RATE_ID NOWAIT;
118 Recinfo C%ROWTYPE;
119 BEGIN
120 OPEN C;
121 FETCH C INTO Recinfo;
122 If (C%NOTFOUND) then
123 CLOSE C;
124 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
125 APP_EXCEPTION.RAISE_EXCEPTION;
126 End If;
127 CLOSE C;
128 if (
129 ( Recinfo.FAILURE_RATE_ID = p_FAILURE_RATE_ID)
130 AND ( ( Recinfo.PRODUCT_ID = p_PRODUCT_ID)
131 OR ( ( Recinfo.PRODUCT_ID IS NULL )
132 AND ( p_PRODUCT_ID IS NULL )))
133 AND ( ( Recinfo.INVENTORY_ITEM_ID = p_INVENTORY_ITEM_ID)
134 OR ( ( Recinfo.INVENTORY_ITEM_ID IS NULL )
135 AND ( p_INVENTORY_ITEM_ID IS NULL )))
136 AND ( ( Recinfo.CALCULATED_FAILURE_RATE = p_CALCULATED_FAILURE_RATE)
137 OR ( ( Recinfo.CALCULATED_FAILURE_RATE IS NULL )
138 AND ( p_CALCULATED_FAILURE_RATE IS NULL )))
139 AND ( ( Recinfo.MANUAL_FAILURE_RATE = p_MANUAL_FAILURE_RATE)
140 OR ( ( Recinfo.MANUAL_FAILURE_RATE IS NULL )
141 AND ( p_MANUAL_FAILURE_RATE IS NULL )))
142 AND ( ( Recinfo.LAST_UPDATE_DATE = p_LAST_UPDATE_DATE)
143 OR ( ( Recinfo.LAST_UPDATE_DATE IS NULL )
144 AND ( p_LAST_UPDATE_DATE IS NULL )))
145 AND ( ( Recinfo.LAST_UPDATED_BY = p_LAST_UPDATED_BY)
146 OR ( ( Recinfo.LAST_UPDATED_BY IS NULL )
147 AND ( p_LAST_UPDATED_BY IS NULL )))
148 AND ( ( Recinfo.CREATION_DATE = p_CREATION_DATE)
149 OR ( ( Recinfo.CREATION_DATE IS NULL )
150 AND ( p_CREATION_DATE IS NULL )))
151 AND ( ( Recinfo.CREATED_BY = p_CREATED_BY)
152 OR ( ( Recinfo.CREATED_BY IS NULL )
153 AND ( p_CREATED_BY IS NULL )))
154 AND ( ( Recinfo.LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN)
155 OR ( ( Recinfo.LAST_UPDATE_LOGIN IS NULL )
156 AND ( p_LAST_UPDATE_LOGIN IS NULL )))
157 AND ( ( Recinfo.PLANNING_PARAMETERS_ID = p_PLANNING_PARAMETERS_ID)
158 OR ( ( Recinfo.PLANNING_PARAMETERS_ID IS NULL )
159 AND ( p_PLANNING_PARAMETERS_ID IS NULL )))
160 ) then
161 return;
162 else
163 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
164 APP_EXCEPTION.RAISE_EXCEPTION;
165 End If;
166 END Lock_Row;
167 End CSP_FAILURE_RATES_PKG;