[Home] [Help]
PACKAGE BODY: APPS.AMS_DM_SOURCE_PKG
Source
1 PACKAGE BODY AMS_DM_SOURCE_PKG as
2 /* $Header: amstdsrb.pls 115.9 2003/09/03 19:26:46 nyostos ship $ */
3 -- Start of Comments
4 -- Package name : AMS_DM_SOURCE_PKG
5 -- Purpose :
6 -- History :
7 -- 26-Jan-2001 choang Removed object ver num increment and update criteria.
8 -- 30-jan-2001 choang Changed p_tree_node to p_rule_id.
9 -- 07-Jan-2002 choang Removed security group id
10 -- 28-Jul-2003 nyostos Added PERCENTILE column.
11 -- NOTE :
12 -- End of Comments
13
14
15 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AMS_DM_SOURCE_PKG';
16 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amstdsrb.pls';
17
18 AMS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
19 AMS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
20 AMS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
21
22 PROCEDURE Insert_Row(
23 px_SOURCE_ID IN OUT NOCOPY NUMBER,
24 p_LAST_UPDATE_DATE DATE,
25 p_LAST_UPDATED_BY NUMBER,
26 p_CREATION_DATE DATE,
27 p_CREATED_BY NUMBER,
28 p_LAST_UPDATE_LOGIN NUMBER,
29 px_OBJECT_VERSION_NUMBER IN OUT NOCOPY NUMBER,
30 p_MODEL_TYPE VARCHAR2,
31 p_ARC_USED_FOR_OBJECT VARCHAR2,
32 p_USED_FOR_OBJECT_ID NUMBER,
33 p_PARTY_ID NUMBER,
34 p_SCORE_RESULT VARCHAR2,
35 p_TARGET_VALUE VARCHAR2,
36 p_CONFIDENCE NUMBER,
37 p_CONTINUOUS_SCORE NUMBER,
38 p_decile NUMBER,
39 p_PERCENTILE NUMBER)
40 IS
41 X_ROWID VARCHAR2(30);
42
43 CURSOR C IS SELECT rowid FROM AMS_DM_SOURCE
44 WHERE SOURCE_ID = px_SOURCE_ID;
45
46 BEGIN
47
48
49 px_object_version_number := 1;
50
51
52 INSERT INTO AMS_DM_SOURCE(
53 SOURCE_ID,
54 LAST_UPDATE_DATE,
55 LAST_UPDATED_BY,
56 CREATION_DATE,
57 CREATED_BY,
58 LAST_UPDATE_LOGIN,
59 OBJECT_VERSION_NUMBER,
60 MODEL_TYPE,
61 ARC_USED_FOR_OBJECT,
62 USED_FOR_OBJECT_ID,
63 PARTY_ID,
64 SCORE_RESULT,
65 TARGET_VALUE,
66 CONFIDENCE,
67 CONTINUOUS_SCORE,
68 decile,
69 PERCENTILE
70 ) VALUES (
71 decode( px_SOURCE_ID, FND_API.g_miss_num, NULL, px_SOURCE_ID),
72 decode( p_LAST_UPDATE_DATE, FND_API.g_miss_date, NULL, p_LAST_UPDATE_DATE),
73 decode( p_LAST_UPDATED_BY, FND_API.g_miss_num, NULL, p_LAST_UPDATED_BY),
74 decode( p_CREATION_DATE, FND_API.g_miss_date, NULL, p_CREATION_DATE),
75 decode( p_CREATED_BY, FND_API.g_miss_num, NULL, p_CREATED_BY),
76 decode( p_LAST_UPDATE_LOGIN, FND_API.g_miss_num, NULL, p_LAST_UPDATE_LOGIN),
77 decode( px_OBJECT_VERSION_NUMBER, FND_API.g_miss_num, NULL, px_OBJECT_VERSION_NUMBER),
78 decode( p_MODEL_TYPE, FND_API.g_miss_char, NULL, p_MODEL_TYPE),
79 decode( p_ARC_USED_FOR_OBJECT, FND_API.g_miss_char, NULL, p_ARC_USED_FOR_OBJECT),
80 decode( p_USED_FOR_OBJECT_ID, FND_API.g_miss_num, NULL, p_USED_FOR_OBJECT_ID),
81 decode( p_PARTY_ID, FND_API.g_miss_num, NULL, p_PARTY_ID),
82 decode( p_SCORE_RESULT, FND_API.g_miss_char, NULL, p_SCORE_RESULT),
83 decode( p_TARGET_VALUE, FND_API.g_miss_char, NULL, p_TARGET_VALUE),
84 decode( p_CONFIDENCE, FND_API.g_miss_num, NULL, p_CONFIDENCE),
85 decode( p_CONTINUOUS_SCORE, FND_API.g_miss_num, NULL, p_CONTINUOUS_SCORE),
86 decode( p_decile, FND_API.g_miss_char, NULL, p_decile),
87 decode( p_PERCENTILE, FND_API.g_miss_char, NULL, p_PERCENTILE));
88 OPEN C;
89 FETCH C INTO x_rowid;
90 If (C%NOTFOUND) then
91 CLOSE C;
92 RAISE NO_DATA_FOUND;
93 End If;
94 End Insert_Row;
95
96 PROCEDURE Update_Row(
97 p_SOURCE_ID NUMBER,
98 p_LAST_UPDATE_DATE DATE,
99 p_LAST_UPDATED_BY NUMBER,
100 p_LAST_UPDATE_LOGIN NUMBER,
101 p_OBJECT_VERSION_NUMBER NUMBER,
102 p_MODEL_TYPE VARCHAR2,
103 p_ARC_USED_FOR_OBJECT VARCHAR2,
104 p_USED_FOR_OBJECT_ID NUMBER,
105 p_PARTY_ID NUMBER,
106 p_SCORE_RESULT VARCHAR2,
107 p_TARGET_VALUE VARCHAR2,
108 p_CONFIDENCE NUMBER,
109 p_CONTINUOUS_SCORE NUMBER,
110 p_decile NUMBER,
111 p_PERCENTILE NUMBER)
112 IS
113 BEGIN
114 Update AMS_DM_SOURCE
115 SET
116 SOURCE_ID = decode( p_SOURCE_ID, FND_API.g_miss_num, SOURCE_ID, p_SOURCE_ID),
117 LAST_UPDATE_DATE = decode( p_LAST_UPDATE_DATE, FND_API.g_miss_date, LAST_UPDATE_DATE, p_LAST_UPDATE_DATE),
118 LAST_UPDATED_BY = decode( p_LAST_UPDATED_BY, FND_API.g_miss_num, LAST_UPDATED_BY, p_LAST_UPDATED_BY),
119 LAST_UPDATE_LOGIN = decode( p_LAST_UPDATE_LOGIN, FND_API.g_miss_num, LAST_UPDATE_LOGIN, p_LAST_UPDATE_LOGIN),
120 OBJECT_VERSION_NUMBER = decode( p_OBJECT_VERSION_NUMBER, FND_API.g_miss_num, OBJECT_VERSION_NUMBER, p_OBJECT_VERSION_NUMBER),
121 MODEL_TYPE = decode( p_MODEL_TYPE, FND_API.g_miss_char, MODEL_TYPE, p_MODEL_TYPE),
122 ARC_USED_FOR_OBJECT = decode( p_ARC_USED_FOR_OBJECT, FND_API.g_miss_char, ARC_USED_FOR_OBJECT, p_ARC_USED_FOR_OBJECT),
123 USED_FOR_OBJECT_ID = decode( p_USED_FOR_OBJECT_ID, FND_API.g_miss_num, USED_FOR_OBJECT_ID, p_USED_FOR_OBJECT_ID),
124 PARTY_ID = decode( p_PARTY_ID, FND_API.g_miss_num, PARTY_ID, p_PARTY_ID),
125 SCORE_RESULT = decode( p_SCORE_RESULT, FND_API.g_miss_char, SCORE_RESULT, p_SCORE_RESULT),
126 TARGET_VALUE = decode( p_TARGET_VALUE, FND_API.g_miss_char, TARGET_VALUE, p_TARGET_VALUE),
127 CONFIDENCE = decode( p_CONFIDENCE, FND_API.g_miss_num, CONFIDENCE, p_CONFIDENCE),
128 CONTINUOUS_SCORE = decode( p_CONTINUOUS_SCORE, FND_API.g_miss_num, CONTINUOUS_SCORE, p_CONTINUOUS_SCORE),
129 decile = decode( p_decile, FND_API.g_miss_char, decile, p_decile),
130 PERCENTILE = decode( p_PERCENTILE, FND_API.g_miss_char, PERCENTILE, p_PERCENTILE)
131 WHERE SOURCE_ID = p_SOURCE_ID;
132
133 IF (SQL%NOTFOUND) THEN
134 RAISE NO_DATA_FOUND;
135 END IF;
136 END Update_Row;
137
138 PROCEDURE Delete_Row(
139 p_SOURCE_ID NUMBER)
140 IS
141 BEGIN
142 DELETE FROM AMS_DM_SOURCE
143 WHERE SOURCE_ID = p_SOURCE_ID;
144 If (SQL%NOTFOUND) then
145 RAISE NO_DATA_FOUND;
146 End If;
147 END Delete_Row;
148
149 PROCEDURE Lock_Row(
150 p_SOURCE_ID NUMBER,
151 p_LAST_UPDATE_DATE DATE,
152 p_LAST_UPDATED_BY NUMBER,
153 p_CREATION_DATE DATE,
154 p_CREATED_BY NUMBER,
155 p_LAST_UPDATE_LOGIN NUMBER,
156 p_OBJECT_VERSION_NUMBER NUMBER,
157 p_MODEL_TYPE VARCHAR2,
158 p_ARC_USED_FOR_OBJECT VARCHAR2,
159 p_USED_FOR_OBJECT_ID NUMBER,
160 p_PARTY_ID NUMBER,
161 p_SCORE_RESULT VARCHAR2,
162 p_TARGET_VALUE VARCHAR2,
163 p_CONFIDENCE NUMBER,
164 p_CONTINUOUS_SCORE NUMBER,
165 p_decile NUMBER,
166 p_PERCENTILE NUMBER)
167 IS
168 CURSOR C IS
169 SELECT *
170 FROM AMS_DM_SOURCE
171 WHERE SOURCE_ID = p_SOURCE_ID
172 FOR UPDATE of SOURCE_ID NOWAIT;
173 Recinfo C%ROWTYPE;
174 BEGIN
175 OPEN C;
176 FETCH C INTO Recinfo;
177 If (C%NOTFOUND) then
178 CLOSE C;
179 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
180 APP_EXCEPTION.RAISE_EXCEPTION;
181 End If;
182 CLOSE C;
183 if (
184 ( Recinfo.SOURCE_ID = p_SOURCE_ID)
185 AND ( ( Recinfo.LAST_UPDATE_DATE = p_LAST_UPDATE_DATE)
186 OR ( ( Recinfo.LAST_UPDATE_DATE IS NULL )
187 AND ( p_LAST_UPDATE_DATE IS NULL )))
188 AND ( ( Recinfo.LAST_UPDATED_BY = p_LAST_UPDATED_BY)
189 OR ( ( Recinfo.LAST_UPDATED_BY IS NULL )
190 AND ( p_LAST_UPDATED_BY IS NULL )))
191 AND ( ( Recinfo.CREATION_DATE = p_CREATION_DATE)
192 OR ( ( Recinfo.CREATION_DATE IS NULL )
193 AND ( p_CREATION_DATE IS NULL )))
194 AND ( ( Recinfo.CREATED_BY = p_CREATED_BY)
195 OR ( ( Recinfo.CREATED_BY IS NULL )
196 AND ( p_CREATED_BY IS NULL )))
197 AND ( ( Recinfo.LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN)
198 OR ( ( Recinfo.LAST_UPDATE_LOGIN IS NULL )
199 AND ( p_LAST_UPDATE_LOGIN IS NULL )))
200 AND ( ( Recinfo.OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER)
201 OR ( ( Recinfo.OBJECT_VERSION_NUMBER IS NULL )
202 AND ( p_OBJECT_VERSION_NUMBER IS NULL )))
203 AND ( ( Recinfo.MODEL_TYPE = p_MODEL_TYPE)
204 OR ( ( Recinfo.MODEL_TYPE IS NULL )
205 AND ( p_MODEL_TYPE IS NULL )))
206 AND ( ( Recinfo.ARC_USED_FOR_OBJECT = p_ARC_USED_FOR_OBJECT)
207 OR ( ( Recinfo.ARC_USED_FOR_OBJECT IS NULL )
208 AND ( p_ARC_USED_FOR_OBJECT IS NULL )))
209 AND ( ( Recinfo.USED_FOR_OBJECT_ID = p_USED_FOR_OBJECT_ID)
210 OR ( ( Recinfo.USED_FOR_OBJECT_ID IS NULL )
211 AND ( p_USED_FOR_OBJECT_ID IS NULL )))
212 AND ( ( Recinfo.PARTY_ID = p_PARTY_ID)
213 OR ( ( Recinfo.PARTY_ID IS NULL )
214 AND ( p_PARTY_ID IS NULL )))
215 AND ( ( Recinfo.SCORE_RESULT = p_SCORE_RESULT)
216 OR ( ( Recinfo.SCORE_RESULT IS NULL )
217 AND ( p_SCORE_RESULT IS NULL )))
218 AND ( ( Recinfo.TARGET_VALUE = p_TARGET_VALUE)
219 OR ( ( Recinfo.TARGET_VALUE IS NULL )
220 AND ( p_TARGET_VALUE IS NULL )))
221 AND ( ( Recinfo.CONFIDENCE = p_CONFIDENCE)
222 OR ( ( Recinfo.CONFIDENCE IS NULL )
223 AND ( p_CONFIDENCE IS NULL )))
224 AND ( ( Recinfo.CONTINUOUS_SCORE = p_CONTINUOUS_SCORE)
225 OR ( ( Recinfo.CONTINUOUS_SCORE IS NULL )
226 AND ( p_CONTINUOUS_SCORE IS NULL )))
227 ) then
228 return;
229 else
230 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
231 APP_EXCEPTION.RAISE_EXCEPTION;
232 End If;
233 END Lock_Row;
234
235 End AMS_DM_SOURCE_PKG;