[Home] [Help]
PACKAGE BODY: APPS.IGS_PE_HZ_CONT_PREF_PKG
Source
1 PACKAGE BODY IGS_PE_HZ_CONT_PREF_PKG AS
2 /* $Header: IGSNIB2B.pls 115.1 2003/06/17 09:03:18 ssawhney noship $ */
3
4 g_error_msg VARCHAR2(2000);
5
6 PROCEDURE date_validate
7 (p_start_date IN hz_contact_preferences.PREFERENCE_START_DATE%TYPE,
8 p_end_date IN hz_contact_preferences.PREFERENCE_END_DATE%TYPE,
9 p_person_id IN hz_parties.party_id%TYPE,
10 x_msg_data OUT NOCOPY varchar2,
11 p_mode IN VARCHAR2)
12 AS
13 /*
14 || Created By : [email protected]
15 || Created On : 5-JUN-2003
16 || Purpose : checking for start date <= end date and start date >= birth date
17 || Known limitations, enhancements or remarks :
18 || Change History :
19 || Who When What
20 || (reverse chronological order - newest change first)
21 */
22 CURSOR c_person_db (cp_person_id HZ_PARTIES.PARTY_ID%TYPE)IS
23 SELECT birth_date FROM igs_pe_person_base_v
24 WHERE person_id = cp_person_id;
25 l_date DATE := null;
26
27 BEGIN
28
29 IF p_start_date > NVL(p_end_date,TO_DATE('4712/12/31','YYYY/MM/DD')) THEN
30 FND_MESSAGE.SET_NAME('IGS', 'IGS_PE_FROM_DT_GRT_TO_DATE');
31 x_msg_data := 'IGS_PE_FROM_DT_GRT_TO_DATE';
32 IGS_GE_MSG_STACK.ADD;
33
34 IF p_mode = 'R' THEN
35 APP_EXCEPTION.RAISE_EXCEPTION;
36 ELSE
37 RETURN;
38 END IF;
39
40 END IF;
41
42 OPEN c_person_db(p_person_id);
43 FETCH c_person_db INTO l_date;
44 CLOSE c_person_db;
45
46 IF p_start_date < NVL(l_date, p_start_date) THEN
47 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_STRT_DT_LESS_BIRTH_DT');
48 x_msg_data := 'IGS_AD_STRT_DT_LESS_BIRTH_DT';
49 IGS_GE_MSG_STACK.ADD;
50
51 IF p_mode = 'R' THEN
52 APP_EXCEPTION.RAISE_EXCEPTION;
53 ELSE
54 RETURN;
55 END IF;
56
57 END IF;
58
59 END date_validate;
60
61 --Local procedure to check for duplicate records
62
63
64 PROCEDURE val_overlap_api(
65 p_person_id hz_contact_preferences.CONTACT_LEVEL_TABLE_ID%TYPE,
66 p_start_date DATE,
67 p_end_date DATE,
68 p_cpid NUMBER,
69 x_msg_data OUT NOCOPY varchar2,
70 p_mode IN VARCHAR2
71 )
72 AS
73 /*
74 || Created By : [email protected]
75 || Created On : 10-JUN-2003
76 || Purpose : checking for duplicate office hours records
77 || Known limitations, enhancements or remarks :
78 || Change History :
79 || Who When What
80 || (reverse chronological order - newest change first) */
81
82 --l_nvl_end DATE := NVL(p_END_DATE,TO_DATE('4712/12/31','YYYY/MM/DD'));
83
84 CURSOR c_validate_overlap_dates ( cp_person_id hz_contact_preferences.CONTACT_LEVEL_TABLE_ID%TYPE,
85 cp_start_date DATE,
86 cp_end_date DATE,
87 cp_cpid NUMBER)
88 IS
89 SELECT 1
90 FROM hz_contact_preferences
91 WHERE contact_level_table_id = cp_person_id
92 AND contact_type = 'VISIT'
93 AND ( cp_cpid IS NULL OR contact_preference_id <> cp_cpid)
94 AND (NVL(preference_END_DATE,TO_DATE('4712/12/31','YYYY/MM/DD')) >= cp_start_date OR
95 NVL(preference_END_DATE,TO_DATE('4712/12/31','YYYY/MM/DD')) >= cp_end_date)
96 AND (preference_START_DATE <= cp_start_date OR preference_START_DATE <= cp_end_date);
97
98 l_count NUMBER := 0;
99
100 BEGIN
101
102 OPEN c_validate_overlap_dates ( p_person_id,p_start_date,p_end_date,p_cpid);
103 FETCH c_validate_overlap_dates INTO l_count;
104 IF c_validate_overlap_dates%FOUND THEN
105 --overlap exists
106 CLOSE c_validate_overlap_dates;
107 FND_MESSAGE.SET_NAME('IGS','IGS_GE_DATES_OVERLAP');
108 x_msg_data :='IGS_GE_DATES_OVERLAP';
109 IGS_GE_MSG_STACK.ADD;
110
111 IF p_mode = 'R' THEN
112 APP_EXCEPTION.RAISE_EXCEPTION;
113 ELSE
114 RETURN;
115 END IF;
116
117 END IF;
118
119 CLOSE c_validate_overlap_dates;
120
121 END val_overlap_api;
122
123 procedure INSERT_ROW (
124 X_MSG_COUNT OUT NOCOPY NUMBER,
125 X_MSG_DATA OUT NOCOPY VARCHAR2,
126 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
127 X_ROWID in out NOCOPY VARCHAR2,
128 x_CONTACT_PREFERENCE_ID IN OUT NOCOPY NUMBER,
129 x_OBJECT_VERSION_NUMBER IN OUT NOCOPY NUMBER,
130 X_CONTACT_LEVEL_TABLE IN VARCHAR2,
131 X_CONTACT_LEVEL_TABLE_ID IN NUMBER,
132 X_CONTACT_TYPE IN VARCHAR2,
133 X_PREFERENCE_CODE IN VARCHAR2,
134 X_PREFERENCE_START_DATE IN DATE,
135 X_PREFERENCE_END_DATE IN DATE,
136 X_REQUESTED_BY IN VARCHAR2,
137 X_REASON_CODE IN VARCHAR2,
138 X_STATUS IN VARCHAR2,
139 X_MODE in VARCHAR2 default 'R'
140 ) AS
141
142 l_cont_pref_rec_type HZ_CONTACT_PREFERENCE_V2PUB.contact_preference_rec_type;
143 l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
144
145 tmp_var VARCHAR2(2000);
146 tmp_var1 VARCHAR2(2000);
147 BEGIN
148
149
150 l_cont_pref_rec_type.contact_level_table := NVL(X_CONTACT_LEVEL_TABLE,'HZ_PARTIES');
151 l_cont_pref_rec_type.contact_level_table_id := X_CONTACT_LEVEL_TABLE_ID;
152 l_cont_pref_rec_type.contact_type := X_CONTACT_TYPE;
153 l_cont_pref_rec_type.preference_code := X_PREFERENCE_CODE;
154 l_cont_pref_rec_type.preference_start_date := X_PREFERENCE_START_DATE;
155 l_cont_pref_rec_type.preference_end_date := X_PREFERENCE_END_DATE;
156 l_cont_pref_rec_type.requested_by := NVL(X_REQUESTED_BY,'PARTY');
157 l_cont_pref_rec_type.reason_code := X_REASON_CODE;
158 l_cont_pref_rec_type.status := 'A';
159 l_cont_pref_rec_type.created_by_module := 'IGS';
160
161
162 -- perform validations
163 x_return_status := 'E';
164 x_msg_count :=1;
165
166 date_validate(
167 x_preference_start_date,
168 x_preference_end_date,
169 x_contact_level_table_id,
170 x_msg_data,
171 x_mode);
172
173 IF x_msg_data IS NOT NULL THEN
174 RETURN;
175 END IF;
176
177 -- check for date overlap.
178 x_return_status := 'E';
179 x_msg_count := 1;
180
181 val_overlap_api(
182 x_contact_level_table_id,
183 x_preference_start_date,
184 NVL(x_preference_end_date,TO_DATE('4712/12/31','YYYY/MM/DD')),
185 x_contact_preference_id,
186 x_msg_data,
187 x_mode);
188
189 IF x_msg_data IS NOT NULL THEN
190 RETURN;
191 END IF;
192
193
194 x_return_status := null;
195 x_msg_count := null;
196
197 HZ_CONTACT_PREFERENCE_V2PUB.create_contact_preference (
198 p_init_msg_list => l_init_msg_list,
199 p_contact_preference_rec => l_cont_pref_rec_type,
200 x_contact_preference_id => x_contact_preference_id,
201 x_return_status => x_return_status,
202 x_msg_count => x_msg_count ,
203 x_msg_data => x_msg_data );
204
205
206 IF x_return_status IN ('E','U') THEN
207
208 IF x_msg_count > 1 THEN
209 FOR i IN 1..x_msg_count LOOP
210 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
211 tmp_var1 := tmp_var1 || ' '|| tmp_var;
212 END LOOP;
213 x_msg_data := tmp_var1;
214 END IF;
215 RETURN;
216 END IF;
217
218
219 -- after successful insert, pass OVN out as 1.
220 x_OBJECT_VERSION_NUMBER :=1;
221
222 END INSERT_ROW;
223
224
225 procedure UPDATE_ROW (
226 X_MSG_COUNT OUT NOCOPY NUMBER,
227 X_MSG_DATA OUT NOCOPY VARCHAR2,
228 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
229 X_ROWID in out NOCOPY VARCHAR2,
230 x_CONTACT_PREFERENCE_ID IN OUT NOCOPY NUMBER,
231 x_OBJECT_VERSION_NUMBER IN OUT NOCOPY NUMBER,
232 X_CONTACT_LEVEL_TABLE IN VARCHAR2,
233 X_CONTACT_LEVEL_TABLE_ID IN NUMBER,
234 X_CONTACT_TYPE IN VARCHAR2,
235 X_PREFERENCE_CODE IN VARCHAR2,
236 X_PREFERENCE_START_DATE IN DATE,
237 X_PREFERENCE_END_DATE IN DATE,
238 X_REQUESTED_BY IN VARCHAR2,
239 X_REASON_CODE IN VARCHAR2,
240 X_STATUS IN VARCHAR2,
241 X_MODE in VARCHAR2 default 'R'
242 ) AS
243 l_cont_pref_rec_type HZ_CONTACT_PREFERENCE_V2PUB.contact_preference_rec_type;
244 l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
245 tmp_var VARCHAR2(2000);
246 tmp_var1 VARCHAR2(2000);
247
248 BEGIN
249
250
251 -- perform validations
252 -- perform this before setting the G_MISS_DATE.
253 x_return_status := 'E';
254 x_msg_count :=1;
255
256 date_validate(
257 x_preference_start_date,
258 x_preference_end_date,
259 x_contact_level_table_id,
260 x_msg_data,
261 x_mode);
262
263 IF x_msg_data IS NOT NULL THEN
264 RETURN;
265 END IF;
266 -- check for date overlap.
267 x_return_status := 'E';
268 x_msg_count := 1;
269
270 val_overlap_api(
271 x_contact_level_table_id,
272 x_preference_start_date,
273 NVL(x_preference_end_date,TO_DATE('4712/12/31','YYYY/MM/DD')) ,
274 x_contact_preference_id,
275 x_msg_data,
276 x_mode);
277
278 IF x_msg_data IS NOT NULL THEN
279 RETURN;
280 END IF;
281
282 x_return_status := null;
283 x_msg_data := null;
284 l_cont_pref_rec_type.contact_preference_id := x_CONTACT_PREFERENCE_ID;
285 l_cont_pref_rec_type.contact_level_table := NVL(X_CONTACT_LEVEL_TABLE,'HZ_PARTIES');
286 l_cont_pref_rec_type.contact_level_table_id := X_CONTACT_LEVEL_TABLE_ID;
287 l_cont_pref_rec_type.contact_type := X_CONTACT_TYPE;
288 l_cont_pref_rec_type.preference_code := X_PREFERENCE_CODE;
289 l_cont_pref_rec_type.preference_start_date := X_PREFERENCE_START_DATE;
290 l_cont_pref_rec_type.preference_end_date := NVL(X_PREFERENCE_END_DATE,FND_API.G_MISS_DATE);-- this is a nullable col
291 l_cont_pref_rec_type.requested_by := NVL(X_REQUESTED_BY,'PARTY');
292 l_cont_pref_rec_type.reason_code := NVL(X_REASON_CODE,FND_API.G_MISS_CHAR); -- this is a nullable col
293 l_cont_pref_rec_type.status := 'A';
294
295
296
297 HZ_CONTACT_PREFERENCE_V2PUB.update_contact_preference (
298 p_init_msg_list => l_init_msg_list,
299 p_contact_preference_rec => l_cont_pref_rec_type,
300 p_object_version_number => x_object_version_number,
301 x_return_status => x_return_status,
302 x_msg_count => x_msg_count ,
303 x_msg_data => x_msg_data );
304
305 IF x_return_status IN ('E','U') THEN
306
307 IF x_msg_count > 1 THEN
308 FOR i IN 1..x_msg_count LOOP
309 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
310 tmp_var1 := tmp_var1 || ' '|| tmp_var;
311 END LOOP;
312 x_msg_data := tmp_var1;
313 END IF;
314 RETURN;
315 END IF;
316
317
318
319 END UPDATE_ROW;
320 END IGS_PE_HZ_CONT_PREF_PKG;