[Home] [Help]
PACKAGE BODY: APPS.CSF_PLAN_TERRS_PKG
Source
1 PACKAGE BODY CSF_PLAN_TERRS_PKG AS
2 /* $Header: CSFVPLTB.pls 120.3 2007/12/18 06:21:59 ipananil ship $ */
3
4 /*
5 +========================================================================+
6 | Copyright (c) 1999 Oracle Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +========================================================================+
10 Name
11 ----
12 CSF_PLAN_TERRS_PKG
13
14 Purpose
15 -------
16 Insert, lock and delete records in the CSF_PLAN_TERRS table.
17 Check uniqueness of columns PLAN_TERR_ID and TERR_ID/GROUP_ID combinations.
18 Check referential integrity of the TERR_ID and GROUP_ID columns.
19
20 History
21 -------
22 06-JAN-2000 ipels - First creation
23 26-SEP-2000 ipels - Fixed bug# 1413810
24 13-NOV-2002 jgrondel Bug 2663989.
25 Added NOCOPY hint to procedure
26 out-parameters.
27 13-NOV-2002 jgrondel Added dbdrv.
28 03-dec-2002 jgrondel Bug 2692082.
29 Added NOCOPY hint to procedure
30 out-parameters.
31 30-Nov-2004 vrajeev Bug Fixed 3224947
32 Changed code in Delete_Row
33 +========================================================================+
34 */
35
36
37 PROCEDURE Check_Unique
38 ( p_rowid IN VARCHAR2,
39 p_terr_id IN NUMBER,
40 p_group_id IN NUMBER
41 )
42 IS
43
44 CURSOR c_unique IS
45 SELECT NULL
46 FROM csf_plan_terrs
47 WHERE ( p_rowid IS NULL OR rowid <> p_rowid )
48 AND terr_id = p_terr_id
49 AND group_id = p_group_id;
50
51 r_unique c_unique%ROWTYPE;
52
53 BEGIN
54
55 OPEN c_unique;
56 FETCH c_unique INTO r_unique;
57 IF c_unique%FOUND THEN
58 CLOSE c_unique;
59 FND_MESSAGE.Set_Name('CSF', 'CSF_PLAN_TERRS_NOT_UNIQUE');
60 FND_MESSAGE.Set_Token('P_TERR_ID', p_terr_id);
61 FND_MESSAGE.Set_Token('P_GROUP_ID', p_group_id);
62 APP_EXCEPTION.Raise_Exception;
63 END IF;
64 CLOSE c_unique;
65
66 END Check_Unique;
67
68
69 PROCEDURE Check_References
70 ( p_terr_id IN NUMBER,
71 p_group_id IN NUMBER
72 )
73 IS
74
75 CURSOR c_terr IS
76 SELECT NULL
77 FROM jtf_terr_all
78 WHERE terr_id = p_terr_id;
79
80 CURSOR c_group IS
81 SELECT NULL
82 FROM jtf_rs_groups_b
83 WHERE group_id = p_group_id;
84
85 r_terr c_terr%ROWTYPE;
86 r_group c_group%ROWTYPE;
87
88 BEGIN
89
90 OPEN c_terr;
91 FETCH c_terr INTO r_terr;
92 IF c_terr%NOTFOUND THEN
93 CLOSE c_terr;
94 FND_MESSAGE.Set_Name('CSF', 'CSF_PLAN_TERRS_INVALID_TERR');
95 FND_MESSAGE.Set_Token('P_TERR_ID', p_terr_id);
96 APP_EXCEPTION.Raise_Exception;
97 END IF;
98 CLOSE c_terr;
99
100 OPEN c_group;
101 FETCH c_group INTO r_group;
102 IF c_group%NOTFOUND THEN
103 CLOSE c_group;
104 FND_MESSAGE.Set_Name('CSF', 'CSF_PLAN_TERRS_INVALID_GROUP');
105 FND_MESSAGE.Set_Token('P_GROUP_ID', p_group_id);
106 APP_EXCEPTION.Raise_Exception;
107 END IF;
108 CLOSE c_group;
109
110 END Check_References;
111
112
113 PROCEDURE Insert_Row
114 ( x_rowid IN OUT NOCOPY VARCHAR2,
115 p_terr_id IN NUMBER,
116 p_group_id IN NUMBER
117 )
118 IS
119
120 l_plan_terr_id NUMBER;
121 l_last_update_date DATE ;
122 l_last_updated_by NUMBER ;
123 l_creation_date DATE ;
124 l_created_by NUMBER ;
125 l_last_update_login NUMBER ;
126 l_object_version_number NUMBER := 1;
127
128 CURSOR c_plan_terrs IS
129 SELECT rowid
130 FROM csf_plan_terrs
131 WHERE plan_terr_id = l_plan_terr_id;
132
133 BEGIN
134
135 l_last_update_date := SYSDATE;
136 l_last_updated_by := FND_GLOBAL.User_Id;
137 l_creation_date := SYSDATE;
138 l_created_by := FND_GLOBAL.User_Id;
139 l_last_update_login := FND_GLOBAL.Login_Id;
140
141 SELECT CSF_PLAN_TERRS_S.NEXTVAL
142 INTO l_plan_terr_id
143 FROM dual;
144
145 INSERT INTO csf_plan_terrs
146 ( plan_terr_id ,
147 last_update_date ,
148 last_updated_by ,
149 creation_date ,
150 created_by ,
151 last_update_login ,
152 object_version_number ,
153 terr_id ,
154 group_id
155 )
156 VALUES
157 ( l_plan_terr_id ,
158 l_last_update_date ,
159 l_last_updated_by ,
160 l_creation_date ,
161 l_created_by ,
162 l_last_update_login ,
163 l_object_version_number ,
164 p_terr_id ,
165 p_group_id
166 );
167
168 OPEN c_plan_terrs;
169 FETCH c_plan_terrs INTO x_rowid;
170 IF (c_plan_terrs%NOTFOUND) THEN
171 CLOSE c_plan_terrs;
172 RAISE NO_DATA_FOUND;
173 END IF;
174 CLOSE c_plan_terrs;
175
176 END Insert_Row;
177
178
179 PROCEDURE Delete_Row
180 ( p_rowid IN VARCHAR2
181 )
182 IS
183 l_selected_terr varchar2(4000);
184 l_del_terr varchar2(4000);
185 l_dummy boolean;
186 CURSOR c_deleted_terr is
187 SELECT terr_id
188 FROM csf_plan_terrs
189 WHERE rowid = p_rowid;
190 BEGIN
191 -- BUG Fixed 3224947
192 -- After deleting a territory from the planner group
193 -- The territory should be removed from profile 'CSF: Selcted Territories'
194 -- To avoid resources belonging to that territory appearing on the Plan Board and Gantt.
195 OPEN c_deleted_terr;
196 FETCH c_deleted_terr INTO l_del_terr;
197 IF (c_deleted_terr%NOTFOUND) THEN
198 CLOSE c_deleted_terr;
199 RAISE NO_DATA_FOUND;
200 END IF;
201 CLOSE c_deleted_terr;
202 -- Get selected territories
203 l_selected_terr := csf_util_pvt.get_selected_terr(fnd_global.user_id);
204
205 if l_selected_terr is not null
206 then
207 l_selected_terr := replace(l_selected_terr, l_del_terr);
208 l_selected_terr := replace(l_selected_terr,',,',',');
209 l_selected_terr := ltrim(l_selected_terr,',');
210 l_selected_terr := rtrim(l_selected_terr,',');
211
212 -- Save the modified list
213 csf_util_pvt.set_selected_terr( l_selected_terr, fnd_global.user_id );
214 end if;
215 DELETE FROM csf_plan_terrs
216 WHERE rowid = p_rowid;
217 IF (SQL%NOTFOUND) THEN
218 RAISE NO_DATA_FOUND;
219 END IF;
220
221 END Delete_Row;
222
223
224 PROCEDURE Lock_Row
225 ( p_rowid IN VARCHAR2,
226 p_object_version_number IN NUMBER
227 )
228 IS
229
230 l_object_version_number NUMBER;
231
232 CURSOR c_plan_terrs IS
233 SELECT object_version_number
234 FROM csf_plan_terrs
235 WHERE rowid = p_rowid
236 FOR UPDATE NOWAIT;
237
238 BEGIN
239
240 OPEN c_plan_terrs;
241 FETCH c_plan_terrs INTO l_object_version_number;
242 IF (c_plan_terrs%NOTFOUND) THEN
243 CLOSE c_plan_terrs;
244 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
245 APP_EXCEPTION.Raise_Exception;
246 END IF;
247 CLOSE c_plan_terrs;
248
249 IF (l_object_version_number = p_object_version_number) THEN
250 NULL;
251 ELSE
252 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
253 APP_EXCEPTION.Raise_Exception;
254 END IF;
255
256 END Lock_Row;
257
258
259 END CSF_PLAN_TERRS_PKG;
260
261