1 PACKAGE BODY IEX_SCORE_COMPONENTS_PKG AS
2 /* $Header: iextscpb.pls 120.0 2004/01/24 03:22:47 appldev noship $ */
3
4 /* Insert_Row procedure */
5 PG_DEBUG NUMBER(2) := TO_NUMBER(NVL(FND_PROFILE.value('IEX_DEBUG_LEVEL'), '20'));
6
7 PROCEDURE Insert_Row(x_rowid IN OUT NOCOPY VARCHAR2
8 ,p_SCORE_COMPONENT_ID NUMBER
9 ,p_SCORE_COMP_WEIGHT NUMBER
10 ,p_SCORE_ID NUMBER
11 ,p_ENABLED_FLAG VARCHAR2
12 ,p_LAST_UPDATE_DATE DATE
13 ,p_LAST_UPDATED_BY NUMBER
14 ,p_CREATION_DATE DATE
15 ,p_CREATED_BY NUMBER
16 ,p_LAST_UPDATE_LOGIN NUMBER
17 ,p_REQUEST_ID NUMBER
18 ,p_PROGRAM_APPLICATION_ID NUMBER
19 ,p_PROGRAM_ID NUMBER
20 ,p_PROGRAM_UPDATE_DATE DATE
21 ,p_SCORE_COMP_TYPE_ID NUMBER
22 ) IS
23 CURSOR C IS SELECT ROWID FROM IEX_SCORE_COMPONENTS
24 WHERE SCORE_COMPONENT_ID = p_SCORE_COMPONENT_ID;
25
26 BEGIN
27 INSERT INTO IEX_SCORE_COMPONENTS
28 (
29 SCORE_COMPONENT_ID
30 ,SCORE_COMP_WEIGHT
31 ,SCORE_ID
32 ,ENABLED_FLAG
33 ,LAST_UPDATE_DATE
34 ,LAST_UPDATED_BY
35 ,CREATION_DATE
36 ,CREATED_BY
37 ,LAST_UPDATE_LOGIN
38 ,REQUEST_ID
39 ,PROGRAM_APPLICATION_ID
40 ,PROGRAM_ID
41 ,PROGRAM_UPDATE_DATE
42 ,SCORE_COMP_TYPE_ID
43 ) VALUES (
44 p_SCORE_COMPONENT_ID
45 ,p_SCORE_COMP_WEIGHT
46 ,p_SCORE_ID
47 ,p_ENABLED_FLAG
48 ,p_LAST_UPDATE_DATE
49 ,p_LAST_UPDATED_BY
50 ,p_CREATION_DATE
51 ,p_CREATED_BY
52 ,p_LAST_UPDATE_LOGIN
53 ,p_REQUEST_ID
54 ,p_PROGRAM_APPLICATION_ID
55 ,p_PROGRAM_ID
56 ,p_PROGRAM_UPDATE_DATE
57 ,p_SCORE_COMP_TYPE_ID
58 );
59
60 OPEN C;
61 FETCH C INTO x_rowid;
62 IF (C%NOTFOUND) THEN
63 CLOSE C;
64 RAISE NO_DATA_FOUND;
65 END IF;
66 CLOSE C;
67 END Insert_Row;
68
69 /* Update_Row procedure */
70 PROCEDURE Update_Row(x_rowid VARCHAR2
71 ,p_SCORE_COMPONENT_ID NUMBER
72 ,p_SCORE_COMP_WEIGHT NUMBER
73 ,p_SCORE_ID NUMBER
74 ,p_ENABLED_FLAG VARCHAR2
75 ,p_LAST_UPDATE_DATE DATE
76 ,p_LAST_UPDATED_BY NUMBER
77 ,p_CREATION_DATE DATE
78 ,p_CREATED_BY NUMBER
79 ,p_LAST_UPDATE_LOGIN NUMBER
80 ,p_REQUEST_ID NUMBER
81 ,p_PROGRAM_APPLICATION_ID NUMBER
82 ,p_PROGRAM_ID NUMBER
83 ,p_PROGRAM_UPDATE_DATE DATE
84 ,p_SCORE_COMP_TYPE_ID NUMBER
85 ) IS
86 BEGIN
87 UPDATE IEX_SCORE_COMPONENTS SET
88 SCORE_COMPONENT_ID = p_SCORE_COMPONENT_ID
89 ,SCORE_COMP_WEIGHT = p_SCORE_COMP_WEIGHT
90 ,SCORE_ID = p_SCORE_ID
91 ,ENABLED_FLAG = p_ENABLED_FLAG
92 ,LAST_UPDATE_DATE = p_LAST_UPDATE_DATE
93 ,LAST_UPDATED_BY = p_LAST_UPDATED_BY
94 ,CREATION_DATE = p_CREATION_DATE
95 ,CREATED_BY = p_CREATED_BY
96 ,LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN
97 ,REQUEST_ID = p_REQUEST_ID
98 ,PROGRAM_APPLICATION_ID = p_PROGRAM_APPLICATION_ID
99 ,PROGRAM_ID = p_PROGRAM_ID
100 ,PROGRAM_UPDATE_DATE = p_PROGRAM_UPDATE_DATE
101 ,SCORE_COMP_TYPE_ID = p_SCORE_COMP_TYPE_ID
102 WHERE rowid = x_rowid;
103
104 if (sql%notfound) then
105 raise no_data_found;
106 end if;
107 END Update_Row;
108
109 /* Delete_Row procedure */
110 PROCEDURE Delete_Row(x_rowid VARCHAR2) IS
111 BEGIN
112 DELETE FROM IEX_SCORE_COMPONENTS
113 WHERE rowid = x_rowid;
114
115 if (sql%notfound) then
116 raise no_data_found;
117 end if;
118 END Delete_Row;
119
120 /* Lock_Row procedure */
121 PROCEDURE Lock_Row(x_rowid VARCHAR2
122 ,p_SCORE_COMPONENT_ID NUMBER
123 ,p_SCORE_COMP_WEIGHT NUMBER
124 ,p_SCORE_ID NUMBER
125 ,p_ENABLED_FLAG VARCHAR2
126 ,p_LAST_UPDATE_DATE DATE
127 ,p_LAST_UPDATED_BY NUMBER
128 ,p_CREATION_DATE DATE
129 ,p_CREATED_BY NUMBER
130 ,p_LAST_UPDATE_LOGIN NUMBER
131 ,p_REQUEST_ID NUMBER
132 ,p_PROGRAM_APPLICATION_ID NUMBER
133 ,p_PROGRAM_ID NUMBER
134 ,p_PROGRAM_UPDATE_DATE DATE
135 ,p_SCORE_COMP_TYPE_ID NUMBER
136 ) IS
137 CURSOR C IS SELECT * FROM IEX_SCORE_COMPONENTS
138 WHERE rowid = x_rowid
139 FOR UPDATE of SCORE_COMPONENT_ID NOWAIT;
140 Recinfo C%ROWTYPE;
141 BEGIN
142 OPEN C;
143 FETCH C INTO Recinfo;
144 IF (C%NOTFOUND) THEN
145 CLOSE C;
146 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
147 APP_EXCEPTION.Raise_Exception;
148 END IF;
149 CLOSE C;
150
151 IF (
152 (Recinfo.SCORE_COMPONENT_ID = p_SCORE_COMPONENT_ID)
153 AND ( (Recinfo.SCORE_COMP_WEIGHT = p_SCORE_COMP_WEIGHT)
154 OR ( (Recinfo.SCORE_COMP_WEIGHT IS NULL)
155 AND (p_SCORE_COMP_WEIGHT IS NULL)))
156 AND ( (Recinfo.SCORE_ID = p_SCORE_ID)
157 OR ( (Recinfo.SCORE_ID IS NULL)
158 AND (p_SCORE_ID IS NULL)))
159 AND ( (Recinfo.ENABLED_FLAG = p_ENABLED_FLAG)
160 OR ( (Recinfo.ENABLED_FLAG IS NULL)
161 AND (p_ENABLED_FLAG IS NULL)))
162 AND ( (Recinfo.LAST_UPDATE_DATE = p_LAST_UPDATE_DATE)
163 OR ( (Recinfo.LAST_UPDATE_DATE IS NULL)
164 AND (p_LAST_UPDATE_DATE IS NULL)))
165 AND ( (Recinfo.LAST_UPDATED_BY = p_LAST_UPDATED_BY)
166 OR ( (Recinfo.LAST_UPDATED_BY IS NULL)
167 AND (p_LAST_UPDATED_BY IS NULL)))
168 AND ( (Recinfo.CREATION_DATE = p_CREATION_DATE)
169 OR ( (Recinfo.CREATION_DATE IS NULL)
170 AND (p_CREATION_DATE IS NULL)))
171 AND ( (Recinfo.CREATED_BY = p_CREATED_BY)
172 OR ( (Recinfo.CREATED_BY IS NULL)
173 AND (p_CREATED_BY IS NULL)))
174 AND ( (Recinfo.LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN)
175 OR ( (Recinfo.LAST_UPDATE_LOGIN IS NULL)
176 AND (p_LAST_UPDATE_LOGIN IS NULL)))
177 AND ( (Recinfo.REQUEST_ID = p_REQUEST_ID)
178 OR ( (Recinfo.REQUEST_ID IS NULL)
179 AND (p_REQUEST_ID IS NULL)))
180 AND ( (Recinfo.PROGRAM_APPLICATION_ID = p_PROGRAM_APPLICATION_ID)
181 OR ( (Recinfo.PROGRAM_APPLICATION_ID IS NULL)
182 AND (p_PROGRAM_APPLICATION_ID IS NULL)))
183 AND ( (Recinfo.PROGRAM_ID = p_PROGRAM_ID)
184 OR ( (Recinfo.PROGRAM_ID IS NULL)
185 AND (p_PROGRAM_ID IS NULL)))
186 AND ( (Recinfo.PROGRAM_UPDATE_DATE = p_PROGRAM_UPDATE_DATE)
187 OR ( (Recinfo.PROGRAM_UPDATE_DATE IS NULL)
188 AND (p_PROGRAM_UPDATE_DATE IS NULL)))
189 AND ( (Recinfo.SCORE_COMP_TYPE_ID = p_SCORE_COMP_TYPE_ID)
190 OR ( (Recinfo.SCORE_COMP_TYPE_ID IS NULL)
191 AND (p_SCORE_COMP_TYPE_ID IS NULL)))
192 ) THEN
193 return;
194 ELSE
195 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
196 APP_EXCEPTION.Raise_Exception;
197 END IF;
198 END Lock_Row;
199 END;
200