[Home] [Help]
PACKAGE BODY: APPS.ITG_ORGEFF_PVT
Source
1 PACKAGE BODY ITG_OrgEff_PVT AS
2 /* ARCS: $Header: itgeffb.pls 115.5 2003/12/03 23:01:42 klai noship $
3 * CVS: itgeffb.pls,v 1.13 2002/12/23 21:20:30 ecoe Exp
4 */
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ITG_OrgEff_PVT';
7
8 CURSOR orgind_csr(
9 p_doctyp VARCHAR2,
10 p_pubsub VARCHAR2
11 ) IS
12 SELECT org_controlled
13 FROM itg_org_indicator
14 WHERE cln_doc_type = p_doctyp
15 AND doc_direction = p_pubsub;
16
17 /* Check the effectivity. */
18 FUNCTION Check_Effective(
19 p_organization_id IN NUMBER,
20 p_cln_doc_type IN VARCHAR2,
21 p_doc_direction IN VARCHAR2 /* 'P'ublish or 'S'ubscribe */
22 ) RETURN BOOLEAN IS
23
24 /* This query was the key to the effectivity defaulting (wildcard) scheme,
25 of the sparse matrix scheme. (It's a cool trick, which is why I am not
26 deleting it). Note that cln_doc_type is no longer allowed to be NULL.
27
28 CURSOR orgeff_csr(
29 p_orgid NUMBER,
30 p_doctyp VARCHAR2,
31 p_pubsub VARCHAR2
32 ) IS
33 SELECT start_date, end_date, effectivity_enabled
34 FROM itg_org_effectivity
35 WHERE (organization_id = p_orgid OR organization_id IS NULL)
36 AND (cln_doc_type = p_doctyp OR cln_doc_type IS NULL)
37 AND doc_direction = p_pubsub
38 ORDER BY organization_id ASC,
39 cln_doc_type ASC;
40
41 It is now replaced with the more direct query:
42
43 */
44
45 CURSOR orgeff_csr(
46 p_orgid NUMBER,
47 p_doctyp VARCHAR2,
48 p_pubsub VARCHAR2
49 ) IS
50 SELECT start_date, end_date, effectivity_enabled
51 FROM itg_org_effectivity
52 WHERE NVL(organization_id, -1) = NVL(p_orgid, -1)
53 AND cln_doc_type = p_doctyp
54 AND doc_direction = p_pubsub;
55
56 l_found BOOLEAN;
57 l_orgid NUMBER;
58 l_orgind itg_org_indicator.org_controlled%TYPE;
59 l_orgeff orgeff_csr%ROWTYPE;
60 BEGIN
61 /* Check if effectivity restrictions are globally enabled. */
62 /* TBD: add the site profile item ITG_ORG_EFFECTIVE ('Y'/'N'). */
63 IF NVL(UPPER(FND_PROFILE.VALUE('ITG_ORG_EFFECTIVE')), 'N') <> 'Y' THEN
64 ITG_Debug.msg('Effective: not profile enabled.');
65 RETURN TRUE;
66 END IF;
67
68 /* Check if effectivity restrictions are enabled for this document type. */
69 OPEN orgind_csr(p_cln_doc_type, p_doc_direction);
70 FETCH orgind_csr INTO l_orgind;
71 l_found := orgind_csr%FOUND;
72 CLOSE orgind_csr;
73 IF NOT l_found THEN
74 /* No effectivity record means non-effectivity controlled. */
75 ITG_Debug.msg('Effective: non-effectivity controlled.');
76 RETURN TRUE;
77 END IF;
78
79 /* Check the nitty-gritty itty-bitty effectivity. :) */
80 l_orgid := p_organization_id;
81 IF l_orgind <> FND_API.G_TRUE THEN
82 l_orgid := NULL;
83 END IF;
84 OPEN orgeff_csr(l_orgid, p_cln_doc_type, p_doc_direction);
85 FETCH orgeff_csr INTO l_orgeff;
86 l_found := orgeff_csr%FOUND;
87 CLOSE orgeff_csr;
88 /* In the older wildcard scheme, we wouldn't care if more records are
89 available. There shouldn't be any more now with the new query. */
90
91 IF l_found THEN
92 IF l_orgeff.effectivity_enabled = FND_API.G_TRUE THEN
93 IF (l_orgeff.start_date IS NULL OR l_orgeff.start_date <= SYSDATE) AND
94 (l_orgeff.end_date IS NULL OR l_orgeff.end_date >= SYSDATE) THEN
95 ITG_Debug.msg('Effective: all tests passed.');
96 RETURN TRUE;
97 ELSE
98 ITG_Debug.msg('Not effective: date out of range');
99 END IF;
100 ELSE
101 ITG_Debug.msg('Not effective: not enabled');
102 END IF;
103 ELSE
104 ITG_Debug.msg('Not effective: no effectivity record.');
105 END IF;
106 RETURN FALSE;
107 END Check_Effective;
108
109 PROCEDURE Update_Effectivity(
110 p_api_version IN NUMBER,
111 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
112 p_commit IN VARCHAR2 := FND_API.G_FALSE,
113 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
114
115 x_return_status OUT NOCOPY VARCHAR2, /* VARCHAR2(1) */
116 x_msg_count OUT NOCOPY NUMBER,
117 x_msg_data OUT NOCOPY VARCHAR2, /* VARCHAR2(2000) */
118
119 p_organization_id IN NUMBER,
120 p_cln_doc_type IN VARCHAR2,
121 p_doc_direction IN VARCHAR2,
122 p_start_date IN DATE := NULL,
123 p_end_date IN DATE := NULL,
124 p_effective IN VARCHAR2 := NULL
125 ) IS
126 l_api_name CONSTANT VARCHAR2(30) := 'Update_Effectivity';
127 l_api_version CONSTANT NUMBER := 1.0;
128
129 l_found BOOLEAN;
130 l_eff_id NUMBER;
131 l_dummy itg_org_indicator.org_controlled%TYPE;
132 l_count NUMBER;
133 l_flag VARCHAR2(1);
134
135 CURSOR existing_row_csr IS
136 SELECT effectivity_id
137 FROM itg_org_effectivity
138 WHERE NVL(organization_id, -1) = NVL(p_organization_id, -1)
139 AND cln_doc_type = p_cln_doc_type
140 AND doc_direction = p_doc_direction;
141
142 BEGIN
143 x_return_status := FND_API.G_RET_STS_SUCCESS;
144
145 BEGIN
146 SAVEPOINT Update_Effectivity_PVT;
147 ITG_Debug.setup(
148 p_reset => TRUE,
149 p_pkg_name => G_PKG_NAME,
150 p_proc_name => l_api_name);
151 IF NOT FND_API.Compatible_API_Call(
152 l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
153 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
154 END IF;
155 IF FND_API.To_Boolean(p_init_msg_list) THEN
156 FND_MSG_PUB.Initialize;
157 END IF;
158
159 ITG_Debug.msg('UE', 'Top of procedure.');
160 ITG_Debug.msg('UE', 'p_organization_id', p_organization_id);
161 ITG_Debug.msg('UE', 'p_cln_doc_type', p_cln_doc_type);
162 ITG_Debug.msg('UE', 'p_doc_direction', p_doc_direction);
163 ITG_Debug.msg('UE', 'p_start_date', p_start_date);
164 ITG_Debug.msg('UE', 'p_end_date', p_end_date);
165 ITG_Debug.msg('UE', 'p_effective', p_effective);
166
167 IF p_validation_level > FND_API.G_VALID_LEVEL_NONE THEN
168 ITG_Debug.msg('UE', 'Validating input arguments.');
169
170 IF p_cln_doc_type IS NULL THEN
171 l_count := 0;
172 ELSE
173 SELECT count(1)
174 INTO l_count
175 FROM fnd_lookup_values
176 WHERE lookup_type = 'CLN_COLLABORATION_DOC_TYPE'
177 AND lookup_code = p_cln_doc_type
178 AND language = USERENV('LANG');
179 END IF;
180
181 IF l_count <> 1 THEN
182 ITG_MSG.invalid_argument('p_cln_doc_type', p_cln_doc_type);
183 RAISE FND_API.G_EXC_ERROR;
184 END IF;
185
186 IF p_doc_direction <> 'P' AND p_doc_direction <> 'S' THEN
187 ITG_MSG.invalid_doc_direction(p_doc_direction);
188 RAISE FND_API.G_EXC_ERROR;
189 END IF;
190
191 OPEN orgind_csr(p_cln_doc_type, p_doc_direction);
192 FETCH orgind_csr INTO l_dummy;
193 l_found := orgind_csr%FOUND;
194 CLOSE orgind_csr;
195 IF NOT l_found THEN
196 ITG_MSG.missing_orgind(p_cln_doc_type, p_doc_direction);
197 RAISE FND_API.G_EXC_ERROR;
198 END IF;
199 END IF;
200
201 IF p_effective = FND_API.G_TRUE OR
202 p_effective = FND_API.G_FALSE THEN
203 l_flag := p_effective;
204 ELSE
205 l_flag := NULL;
206 END IF;
207
208 ITG_Debug.msg('UE', 'Looking for existing effectivity row.');
209 OPEN existing_row_csr;
210 FETCH existing_row_csr INTO l_eff_id;
211 l_found := existing_row_csr%FOUND;
212 CLOSE existing_row_csr;
213
214 IF l_found THEN
215 ITG_Debug.msg('UE', 'Updating existing effectivity row.');
216 DECLARE
217 BEGIN
218 UPDATE itg_org_effectivity
219 SET start_date = p_start_date,
220 end_date = p_end_date,
221 effectivity_enabled = NVL(l_flag, effectivity_enabled),
222 last_updated_by = FND_GLOBAL.user_id,
223 last_update_date = SYSDATE,
224 last_update_login = FND_GLOBAL.login_id
225 WHERE effectivity_id = l_eff_id;
226 EXCEPTION
227 WHEN NO_DATA_FOUND THEN
228 ITG_MSG.effectivity_update_fail(
229 p_organization_id, p_cln_doc_type, p_doc_direction, l_eff_id);
230 RAISE FND_API.G_EXC_ERROR;
231 END;
232 ELSE
233 ITG_Debug.msg('UE', 'Inserting new effectivity row.');
234 BEGIN
235 INSERT INTO itg_org_effectivity (
236 effectivity_id,
237 organization_id,
238 cln_doc_type,
239 doc_direction,
240 start_date,
241 end_date,
242 effectivity_enabled,
243 created_by,
244 creation_date,
245 last_updated_by,
246 last_update_date,
247 last_update_login
248 ) VALUES (
249 itg_org_effectivity_s.nextval,
250 p_organization_id,
251 p_cln_doc_type,
252 p_doc_direction,
253 p_start_date,
254 p_end_date,
255 NVL(l_flag, FND_API.G_TRUE),
256 FND_GLOBAL.user_id,
257 SYSDATE,
258 FND_GLOBAL.user_id,
259 SYSDATE,
260 FND_GLOBAL.login_id
261 );
262 EXCEPTION
263 WHEN NO_DATA_FOUND THEN
264 ITG_MSG.effectivity_insert_fail(
265 p_organization_id, p_cln_doc_type, p_doc_direction);
266 RAISE FND_API.G_EXC_ERROR;
267 END;
268 END IF;
269
270 IF FND_API.To_Boolean(p_commit) THEN
271 ITG_Debug.msg('UE', 'Committing work.');
272 COMMIT WORK;
273 END IF;
274 ITG_Debug.msg('UE', 'Done.');
275
276 EXCEPTION
277 WHEN FND_API.G_EXC_ERROR THEN
278 ROLLBACK TO Update_Effectivity_PVT;
279 x_return_status := FND_API.G_RET_STS_ERROR;
280 ITG_Debug.add_error;
281 ITG_Debug.msg('UE', 'EXCEPTION, checked error.', TRUE);
282
283 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
284 ROLLBACK TO Update_Effectivity_PVT;
285 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
286 ITG_Debug.msg('UE', 'EXCEPTION, un-expected error.', TRUE);
287
288 WHEN OTHERS THEN
289 ROLLBACK TO Update_Effectivity_PVT;
290 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
291 ITG_Debug.add_exc_error(G_PKG_NAME, l_api_name);
292 ITG_Debug.msg('UE', 'EXCEPTION, other error.', TRUE);
293 END;
294
295 FND_MSG_PUB.Count_And_Get(
296 p_count => x_msg_count,
297 p_data => x_msg_data);
298
299 END Update_Effectivity;
300
301 END;