[Home] [Help]
PACKAGE BODY: APPS.AS_SALES_LEAD_SCORECARDS_PKG
Source
1 PACKAGE BODY as_sales_lead_scorecards_pkg AS
2 /* $Header: asxtscdb.pls 115.8 2002/11/22 08:05:07 ckapoor ship $ */
3 AS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
4 AS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
5 AS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
6 AS_DEBUG_ERROR_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_ERROR);
7
8 PROCEDURE insert_row(
9 x_rowid IN OUT NOCOPY VARCHAR2
10 , x_scorecard_id NUMBER
11 , x_last_update_date DATE
12 , x_last_updated_by NUMBER
13 , x_creation_date DATE
14 , x_created_by NUMBER
15 , x_last_update_login NUMBER
16 , x_description VARCHAR2
17 , x_enabled_flag VARCHAR2
18 , x_start_date_active DATE
19 , x_end_date_active DATE
20 ) IS
21 CURSOR l_insert IS
22 SELECT ROWID
23 FROM as_sales_lead_scorecards
24 WHERE scorecard_id = x_scorecard_id;
25 BEGIN
26 INSERT INTO as_sales_lead_scorecards (
27 scorecard_id
28 , last_update_date
29 , last_updated_by
30 , creation_date
31 , created_by
32 , last_update_login
33 , description
34 , enabled_flag
35 , start_date_active
36 , end_date_active
37 ) VALUES (
38 x_scorecard_id
39 , DECODE(x_last_update_date,FND_API.G_MISS_DATE,TO_DATE(NULL),x_last_update_date)
40 , DECODE(x_last_updated_by,FND_API.G_MISS_NUM,NULL,x_last_updated_by)
41 , DECODE(x_creation_date,FND_API.G_MISS_DATE,TO_DATE(NULL),x_creation_date)
42 , DECODE(x_created_by,FND_API.G_MISS_NUM,NULL,x_created_by)
43 , DECODE(x_last_update_login,FND_API.G_MISS_NUM,NULL,x_last_update_login)
44 , DECODE(x_description,FND_API.G_MISS_CHAR,NULL,x_description)
45 , DECODE(x_enabled_flag,FND_API.G_MISS_CHAR,NULL,x_enabled_flag)
46 , DECODE(x_start_date_active,FND_API.G_MISS_DATE,TO_DATE(NULL),x_start_date_active)
47 , DECODE(x_end_date_active,FND_API.G_MISS_DATE,TO_DATE(NULL),x_end_date_active)
48 );
49
50 OPEN l_insert;
51 FETCH l_insert INTO x_rowid;
52 IF (l_insert%NOTFOUND) THEN
53 CLOSE l_insert;
54 RAISE NO_DATA_FOUND;
55 END IF;
56 END insert_row;
57
58 PROCEDURE delete_row(
59 x_scorecard_id NUMBER
60 ) IS
61 BEGIN
62 DELETE FROM as_sales_lead_scorecards
63 WHERE scorecard_id = x_scorecard_id;
64 IF (SQL%NOTFOUND) THEN
65 RAISE NO_DATA_FOUND;
66 END IF;
67 END delete_row;
68
69 PROCEDURE update_row(
70 x_rowid VARCHAR2
71 , x_scorecard_id NUMBER
72 , x_last_update_date DATE
73 , x_last_updated_by NUMBER
74 , x_creation_date DATE
75 , x_created_by NUMBER
76 , x_last_update_login NUMBER
77 , x_description VARCHAR2
78 , x_enabled_flag VARCHAR2
79 , x_start_date_active DATE
80 , x_end_date_active DATE
81 ) IS
82 BEGIN
83 UPDATE as_sales_lead_scorecards
84 SET
85 scorecard_id = DECODE(x_scorecard_id,FND_API.G_MISS_NUM,NULL,x_scorecard_id)
86 , last_update_date = DECODE(x_last_update_date,FND_API.G_MISS_DATE,TO_DATE(NULL),x_last_update_date)
87 , last_updated_by = DECODE(x_last_updated_by,FND_API.G_MISS_NUM,NULL,x_last_updated_by)
88 , creation_date = DECODE(x_creation_date,FND_API.G_MISS_DATE,TO_DATE(NULL),x_creation_date)
89 , created_by = DECODE(x_created_by,FND_API.G_MISS_NUM,NULL,x_created_by)
90 , last_update_login = DECODE(x_last_update_login,FND_API.G_MISS_NUM,NULL,x_last_update_login)
91 , description = DECODE(x_description,FND_API.G_MISS_CHAR,NULL,x_description)
92 , enabled_flag = DECODE(x_enabled_flag,FND_API.G_MISS_CHAR,NULL,x_enabled_flag)
93 , start_date_active = DECODE(x_start_date_active,FND_API.G_MISS_DATE,TO_DATE(NULL),x_start_date_active)
94 , end_date_active = DECODE(x_end_date_active,FND_API.G_MISS_DATE,TO_DATE(NULL),x_end_date_active)
95 WHERE ROWID = x_rowid;
96 IF (SQL%NOTFOUND) THEN
97 RAISE NO_DATA_FOUND;
98 END IF;
99 END update_row;
100
101 PROCEDURE update_row(
102 x_scorecard_id NUMBER
103 , x_last_update_date DATE
104 , x_last_updated_by NUMBER
105 , x_last_update_login NUMBER
106 , x_description VARCHAR2
107 , x_start_date_active DATE
108 , x_end_date_active DATE
109 ) IS
110 BEGIN
111 UPDATE as_sales_lead_scorecards
112 SET
113 scorecard_id = DECODE(x_scorecard_id,FND_API.G_MISS_NUM,NULL,x_scorecard_id)
114 , last_update_date = DECODE(x_last_update_date,FND_API.G_MISS_DATE,TO_DATE(NULL),x_last_update_date)
115 , last_updated_by = DECODE(x_last_updated_by,FND_API.G_MISS_NUM,NULL,x_last_updated_by)
116 , last_update_login = DECODE(x_last_update_login,FND_API.G_MISS_NUM,NULL,x_last_update_login)
117 , description = DECODE(x_description,FND_API.G_MISS_CHAR,NULL,x_description)
118 , start_date_active = DECODE(x_start_date_active,FND_API.G_MISS_DATE,TO_DATE(NULL),x_start_date_active)
119 , end_date_active = DECODE(x_end_date_active,FND_API.G_MISS_DATE,TO_DATE(NULL),x_end_date_active)
120 WHERE scorecard_id = x_scorecard_id;
121 IF (SQL%NOTFOUND) THEN
122 RAISE NO_DATA_FOUND;
123 END IF;
124 END update_row;
125
126 PROCEDURE lock_row(
127 x_rowid VARCHAR2
128 , x_scorecard_id NUMBER
129 , x_last_update_date DATE
130 , x_last_updated_by NUMBER
131 , x_creation_date DATE
132 , x_created_by NUMBER
133 , x_last_update_login NUMBER
134 , x_description VARCHAR2
135 , x_enabled_flag VARCHAR2
136 , x_start_date_active DATE
137 , x_end_date_active DATE
138 ) IS
139 CURSOR l_lock IS
140 SELECT *
141 FROM as_sales_lead_scorecards
142 WHERE rowid = x_rowid
143 FOR UPDATE OF scorecard_id NOWAIT;
144 l_table_rec l_lock%ROWTYPE;
145 BEGIN
146 OPEN l_lock;
147 FETCH l_lock INTO l_table_rec;
148 IF (l_lock%NOTFOUND) THEN
149 CLOSE l_lock;
150 FND_MESSAGE.SET_NAME('FND','FORM_RECORD_DELETED');
151 APP_EXCEPTION.RAISE_EXCEPTION;
152 END IF;
153 CLOSE l_lock;
154 IF (
155 ((l_table_rec.scorecard_id = x_scorecard_id)
156 OR ((l_table_rec.scorecard_id IS NULL)
157 AND ( x_scorecard_id IS NULL)))
158 AND ((l_table_rec.last_update_date = x_last_update_date)
159 OR ((l_table_rec.last_update_date IS NULL)
160 AND ( x_last_update_date IS NULL)))
161 AND ((l_table_rec.last_updated_by = x_last_updated_by)
162 OR ((l_table_rec.last_updated_by IS NULL)
163 AND ( x_last_updated_by IS NULL)))
164 AND ((l_table_rec.creation_date = x_creation_date)
165 OR ((l_table_rec.creation_date IS NULL)
166 AND ( x_creation_date IS NULL)))
167 AND ((l_table_rec.created_by = x_created_by)
168 OR ((l_table_rec.created_by IS NULL)
169 AND ( x_created_by IS NULL)))
170 AND ((l_table_rec.last_update_login = x_last_update_login)
171 OR ((l_table_rec.last_update_login IS NULL)
172 AND ( x_last_update_login IS NULL)))
173 AND ((l_table_rec.description = x_description)
174 OR ((l_table_rec.description IS NULL)
175 AND ( x_description IS NULL)))
176 AND ((l_table_rec.enabled_flag = x_enabled_flag)
177 OR ((l_table_rec.enabled_flag IS NULL)
178 AND ( x_enabled_flag IS NULL)))
179 AND ((l_table_rec.start_date_active = x_start_date_active)
180 OR ((l_table_rec.start_date_active IS NULL)
181 AND ( x_start_date_active IS NULL)))
182 AND ((l_table_rec.end_date_active = x_end_date_active)
183 OR ((l_table_rec.end_date_active IS NULL)
184 AND ( x_end_date_active IS NULL)))
185 ) THEN
186 RETURN;
187 ELSE
188 FND_MESSAGE.SET_NAME('FND','FORM_RECORD_DELETED');
189 APP_EXCEPTION.RAISE_EXCEPTION;
190 END IF;
191 END lock_row;
192
193
194 PROCEDURE load_row(
195 x_scorecard_id NUMBER
196 , x_description VARCHAR2
197 , x_start_date_active DATE
198 , x_end_date_active DATE
199 , x_owner VARCHAR2
200 )
201 IS
202 user_id number := 0;
203 row_id varchar2(64);
204
205
206 CURSOR c_get_last_updated (c_scorecard_id NUMBER) IS
207 SELECT last_updated_by
208 FROM as_sales_lead_scorecards
209 WHERE scorecard_id = c_scorecard_id;
210 l_last_updated_by NUMBER;
211 l_rowid varchar2(64);
212
213 BEGIN
214
215 -- If last_updated_by is not 1, means this record has been updated by
216 -- customer, we should not overwrite it.
217 OPEN c_get_last_updated (x_scorecard_id);
218 FETCH c_get_last_updated INTO l_last_updated_by;
219 CLOSE c_get_last_updated;
220
221 IF nvl(l_last_updated_by, 1) = 1
222 THEN
223 if (X_OWNER = 'SEED') then
224 user_id := 1;
225 end if;
226
227 Update_Row(
228 x_scorecard_id => x_scorecard_id
229 , x_last_update_date => SYSDATE
230 , x_last_updated_by => user_id
231 , x_last_update_login => 0
232 , x_description => x_description
233 , x_start_date_active => x_start_date_active
234 , x_end_date_active => x_end_date_active
235 );
236
237 END IF;
238
239 EXCEPTION
240 when no_data_found then
241 Insert_Row(
242 x_rowid => l_rowid
243 , x_scorecard_id => x_scorecard_id
244 , x_last_update_date => SYSDATE
245 , x_last_updated_by => user_id
246 , x_creation_date => sysdate
247 , x_created_by => 0
248 , x_last_update_login => 0
249 , x_description => x_description
250 , x_enabled_flag => NULL
251 , x_start_date_active => x_start_date_active
252 , x_end_date_active => x_end_date_active
253 );
254
255 END load_row;
256
257
258
259 END as_sales_lead_scorecards_pkg;