[Home] [Help]
PACKAGE BODY: APPS.PV_PARTNER_CONTRACTS_PVT
Source
1 PACKAGE BODY PV_Partner_Contracts_PVT as
2 /* $Header: pvxvpcob.pls 120.3 2005/09/07 10:25:44 appldev ship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- PV_Partner_Contracts_PVT
7 -- Purpose
8 --
9 -- History
10 -- 18-DEC-2002 Karen.Tsao Made a call to PV_Partner_Geo_Match_PVT.Get_Matched_Geo_Hierarchy_Id
11 -- to get the geography hierarchy id.
12 -- 28-DEC-2003 Karen.Tsao Modified the query in cursor c_get_contract_id.
13 -- 01-APR-2004 Karen.Tsao Fixed for bug 3540615. Added API Is_Contract_Exists.
14 -- 29-JUN-2004 Karen.Tsao Fixed for sql repository issue (8944997).
15 -- 09-DEC-2004 Karen.Tsao Modified for 11.5.11.
16 -- 02-MAY-2004 Karen.Tsao Took out Is_Contract_Exists() because it is not used.
17 -- 02-MAY-2004 Karen.Tsao Modified for language enhancement.
18 -- 06-SEP-2004 Karen.Tsao Added more debug messages.
19 --
20 -- NOTE
21 --
22 -- ===============================================================
23
24
25 G_PKG_NAME CONSTANT VARCHAR2(30):= 'PV_Partner_Contracts_PVT';
26 G_FILE_NAME CONSTANT VARCHAR2(12) := 'pvxvpcob.pls';
27
28 -- G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
29 -- G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
30 --
31 -- Foreward Procedure Declarations
32 --
33
34 -- ==============================================================================
35 -- Start of Comments
36 -- ==============================================================================
37 -- API Name
38 -- Get_Appropriate_Contract
39 -- Type
40 -- Private
41 -- Pre-Req
42 --
43 -- Parameters
44 --
45 -- IN
46 -- p_partner_party_id IN NUMBER
47 -- p_program_id IN NUMBER
48 --
49 -- OUT
50 -- x_contract_id OUT NUMBER
51 -- Version : Current version 1.0
52 -- Note: This automatic generated procedure definition, it includes standard IN/OUT parameters
53 -- and basic operation, developer must manually add parameters and business logic as necessary.
54 --
55 -- End of Comments
56 -- ==============================================================================
57
58 PV_DEBUG_HIGH_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
59 PV_DEBUG_LOW_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
60 PV_DEBUG_MEDIUM_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
61
62 function get_contract_response_options( p_geo_hierarchy_id in varchar2 )
63 return varchar2
64 is
65 l_str varchar2(2000) default null;
66 l_sep varchar2(2) default null;
67 begin
68 for x in ( select MEANING
69 from PV_PROGRAM_PAYMENT_MODE pm, PV_LOOKUPS lk
70 where pm.GEO_HIERARCHY_ID = p_geo_hierarchy_id
71 and pm.MODE_TYPE = 'CONTRACT'
72 and lk.lookup_type = 'PV_CONTRACT_RESPONSE'
73 and lk.lookup_code = pm.mode_of_payment
74 and lk.enabled_flag = 'Y'
75 and NVL(lk.start_date_active, SYSDATE) <= SYSDATE
76 and NVL(lk.end_date_active, SYSDATE) >= SYSDATE
77 order by meaning) loop
78 l_str := l_str || l_sep || x.MEANING;
79 l_sep := ', ';
80 end loop;
81 return l_str;
82 end;
83
84 PROCEDURE Is_Contract_Exist_Then_Create(
85 p_api_version_number IN NUMBER
86 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
87
88 ,x_return_status OUT NOCOPY VARCHAR2
89 ,x_msg_count OUT NOCOPY NUMBER
90 ,x_msg_data OUT NOCOPY VARCHAR2
91
92 ,p_program_id IN NUMBER
93 ,p_partner_id IN NUMBER
94 ,p_enrl_request_id IN NUMBER
95
96 ,x_exist OUT NOCOPY VARCHAR2
97
98 --,x_contract_status_tbl OUT NOCOPY JTF_VARCHAR2_TABLE_100
99 --,x_program_name_tbl OUT NOCOPY JTF_VARCHAR2_TABLE_100
100 --,x_program_id_tbl OUT NOCOPY JTF_NUMBER_TABLE
101 --,x_enrl_request_id_tbl OUT NOCOPY JTF_NUMBER_TABLE
102 )
103 IS
104 L_API_NAME CONSTANT VARCHAR2(30) := 'Is_Contract_Exist_Then_Create';
105 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
106
107 CURSOR c_get_partner_party_id IS
108 SELECT partner_party_id
109 FROM pv_partner_profiles
110 WHERE partner_id = p_partner_id;
111
112 CURSOR c_get_geo_hierarchy_id (cv_partner_id NUMBER, cv_program_id NUMBER) IS
113 SELECT ppc.geo_hierarchy_id
114 FROM pv_enty_attr_values eav, pv_program_contracts ppc, okc_terms_templates_all term
115 WHERE eav.entity_id = cv_partner_id
116 AND eav.attribute_id = 6
117 AND eav.entity = 'PARTNER'
118 AND eav.latest_flag = 'Y'
119 AND eav.enabled_flag = 'Y'
120 AND ppc.member_type_code = eav.attr_value
121 AND program_id = cv_program_id
122 AND ppc.contract_id = term.template_id
123 AND term.start_date <= sysdate
124 AND (term.end_date is null or term.end_date > sysdate);
125
126 CURSOR c_get_contract_id (cv_partner_id NUMBER, cv_program_id NUMBER, cv_geo_hierarchy_id NUMBER) IS
127 SELECT ppc.contract_id
128 FROM pv_enty_attr_values eav, pv_program_contracts ppc, okc_terms_templates_all term
129 WHERE eav.entity_id = cv_partner_id
130 AND eav.attribute_id = 6
131 AND eav.entity = 'PARTNER'
132 AND eav.latest_flag = 'Y'
133 AND eav.enabled_flag = 'Y'
134 AND ppc.member_type_code = eav.attr_value
135 AND program_id = cv_program_id
136 AND ppc.contract_id = term.template_id
137 AND term.start_date <= sysdate
138 AND (term.end_date is null or term.end_date > sysdate)
139 AND ppc.geo_hierarchy_id = cv_geo_hierarchy_id;
140
141
142 l_geo_hierarchy_id_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
143 l_contract_id NUMBER;
144 l_geo_hierarchy_id NUMBER;
145 l_geo_cnt NUMBER;
146 l_partner_party_id NUMBER;
147 l_template_id NUMBER;
148
149 BEGIN
150 -- Standard Start of API savepoint
151 SAVEPOINT Is_Contract_Exist_Then_Create;
152
153 -- Standard call to check for call compatibility.
154 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
155 p_api_version_number,
156 l_api_name,
157 G_PKG_NAME)
158 THEN
159 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
160 END IF;
161
162 -- Initialize message list if p_init_msg_list is set to TRUE.
163 IF FND_API.to_Boolean( p_init_msg_list )
164 THEN
165 FND_MSG_PUB.initialize;
166 END IF;
167
168 -- Debug Message
169 IF (PV_DEBUG_HIGH_ON) THEN
170 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
171 END IF;
172
173 -- Initialize API return status to SUCCESS
174 x_return_status := FND_API.G_RET_STS_SUCCESS;
175
176 IF (PV_DEBUG_HIGH_ON) THEN
177 PVX_UTILITY_PVT.debug_message('p_program_id = ' || p_program_id);
178 END IF;
179
180 l_geo_cnt := 1;
181 FOR x IN c_get_geo_hierarchy_id (p_partner_id, p_program_id) LOOP
182 IF (PV_DEBUG_HIGH_ON) THEN
183 PVX_UTILITY_PVT.debug_message('x.GEO_HIERARCHY_ID:' || x.GEO_HIERARCHY_ID);
184 END IF;
185
186 l_geo_hierarchy_id_tbl.extend;
187 l_geo_hierarchy_id_tbl(l_geo_cnt) := x.GEO_HIERARCHY_ID;
188 l_geo_cnt := l_geo_cnt + 1;
189 END LOOP;
190
191 FOR x IN c_get_partner_party_id LOOP
192 IF (PV_DEBUG_HIGH_ON) THEN
193 PVX_UTILITY_PVT.debug_message('x.partner_party_id:' || x.partner_party_id);
194 END IF;
195
196 l_partner_party_id := x.partner_party_id;
197 END LOOP;
198
199 PV_Partner_Geo_Match_PVT.Get_Matched_Geo_Hierarchy_Id(
200 p_api_version_number => 1.0
201 ,p_init_msg_list => FND_API.G_FALSE
202 ,x_return_status => x_return_status
203 ,x_msg_count => x_msg_count
204 ,x_msg_data => x_msg_data
205 ,p_partner_party_id => l_partner_party_id
206 ,p_geo_hierarchy_id => l_geo_hierarchy_id_tbl
207 ,x_geo_hierarchy_id => l_geo_hierarchy_id
208 );
209 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
210 FND_MESSAGE.SET_NAME('PV','PV_GET_MATCHED_GEO_HIER_ID');
211 FND_MSG_PUB.Add;
212 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
213 RAISE FND_API.G_EXC_ERROR;
214 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
215 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
216 END IF;
217 END IF;
218
219 IF l_geo_hierarchy_id is not null THEN
220 IF (PV_DEBUG_HIGH_ON) THEN
221 PVX_UTILITY_PVT.debug_message('l_geo_hierarchy_id is not null');
222 PVX_UTILITY_PVT.debug_message('l_geo_hierarchy_id = ' || l_geo_hierarchy_id);
223 END IF;
224
225 FOR y IN c_get_contract_id (p_partner_id, p_program_id, l_geo_hierarchy_id)
226 LOOP
227 IF (PV_DEBUG_HIGH_ON) THEN
228 PVX_UTILITY_PVT.debug_message('y.contract_id = ' || y.contract_id);
229 END IF;
230
231 OKC_TERMS_UTIL_GRP.get_translated_template(
232 p_api_version => 1.0
233 ,p_init_msg_list => FND_API.G_FALSE
234 ,p_template_id => y.contract_id
235 ,p_language => userenv('LANG')
236 ,p_document_type => 'PV_PARTNER_PROGRAM'
237 ,p_validity_date => SYSDATE
238 ,x_return_status => x_return_status
239 ,x_msg_data => x_msg_data
240 ,x_msg_count => x_msg_count
241 ,x_template_id => l_template_id
242 );
243
244 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
245 FND_MESSAGE.SET_NAME('PV','PV_GET_TRANS_TMPL_ERROR_OUT');
246 FND_MSG_PUB.Add;
247 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
248 RAISE FND_API.G_EXC_ERROR;
249 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
250 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
251 END IF;
252 END IF;
253
254 IF (PV_DEBUG_HIGH_ON) THEN
255 PVX_UTILITY_PVT.debug_message('l_template_id = ' || l_template_id);
256 END IF;
257
258 -- If there is an appropriate contract, instantiate the T's and C's here
259 OKC_TERMS_COPY_GRP.COPY_TERMS(
260 p_api_version => 1.0
261 ,p_init_msg_list => FND_API.G_FALSE
262 ,p_commit => FND_API.G_FALSE
263 ,p_template_id => l_template_id
264 ,p_target_doc_type => 'PV_PARTNER_PROGRAM'
265 ,p_target_doc_id => p_enrl_request_id
266 ,p_article_effective_date => null
267 ,p_retain_deliverable => null
268 ,p_target_contractual_doctype => null
269 ,p_target_response_doctype => null
270 ,p_internal_party_id => null
271 ,p_internal_contact_id => null
272 ,p_external_party_id => null
273 ,p_external_party_site_id => null
274 ,p_external_contact_id => null
275 ,p_validate_commit => null
276 ,p_validation_string => null
277 ,p_document_number => p_enrl_request_id
278 ,x_return_status => x_return_status
279 ,x_msg_data => x_msg_data
280 ,x_msg_count => x_msg_count
281 );
282
283 IF (PV_DEBUG_HIGH_ON) THEN
284 PVX_UTILITY_PVT.debug_message('x_return_status: ' || x_return_status);
285 END IF;
286
287 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
288 FND_MESSAGE.SET_NAME('PV','PV_COPY_TERMS_ERROR_OUT');
289 FND_MSG_PUB.Add;
290 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
291 RAISE FND_API.G_EXC_ERROR;
292 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
293 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
294 END IF;
295 END IF;
296 END LOOP;
297 x_exist := 'Y';
298 ELSE
299 IF (PV_DEBUG_HIGH_ON) THEN
300 PVX_UTILITY_PVT.debug_message('l_geo_hierarchy_id is not null');
301 PVX_UTILITY_PVT.debug_message('x_exist is N');
302 END IF;
303 x_exist := 'N';
304 END IF;
305
306 -- Debug Message
307 IF (PV_DEBUG_HIGH_ON) THEN
308 PVX_UTILITY_PVT.debug_message('Private API: ' || l_api_name || ' - end');
309 END IF;
310
311 -- Standard call to get message count and if count is 1, get message info.
312 FND_MSG_PUB.Count_And_Get
313 ( p_encoded => FND_API.G_FALSE,
314 p_count => x_msg_count,
315 p_data => x_msg_data
316 );
317
318 EXCEPTION
319
320 WHEN Fnd_Api.G_EXC_ERROR THEN
321 ROLLBACK TO Is_Contract_Exist_Then_Create;
322 x_return_status := Fnd_Api.G_RET_STS_ERROR;
323
324 -- Standard call to get message count and if count=1, get the message
325 Fnd_Msg_Pub.Count_And_Get (
326 p_encoded => Fnd_Api.G_FALSE
327 ,p_count => x_msg_count
328 ,p_data => x_msg_data
329 );
330
331 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
332 ROLLBACK TO Is_Contract_Exist_Then_Create;
333 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
334 -- Standard call to get message count and if count=1, get the message
335 Fnd_Msg_Pub.Count_And_Get (
336 p_encoded => Fnd_Api.G_FALSE
337 ,p_count => x_msg_count
338 ,p_data => x_msg_data
339 );
340
341 WHEN OTHERS THEN
342 ROLLBACK TO Is_Contract_Exist_Then_Create;
343 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
344 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR)
345 THEN
346 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME,l_api_name);
347 END IF;
348 -- Standard call to get message count and if count=1, get the message
349 Fnd_Msg_Pub.Count_And_Get (
350 p_encoded => Fnd_Api.G_FALSE
351 ,p_count => x_msg_count
352 ,p_data => x_msg_data
353 );
354
355 END Is_Contract_Exist_Then_Create;
356
357 PROCEDURE Get_Contract_Response_Options(
358 p_partner_party_id IN NUMBER
359 ,x_cntr_resp_opt_tbl OUT NOCOPY JTF_VARCHAR2_TABLE_200
360 )
361 IS
362 l_api_name CONSTANT VARCHAR2(45) := 'Get_Contract_Response_Options';
363 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
364
365
366 CURSOR c_cntr_geo_hierarchy_ids IS
367 SELECT geo_hierarchy_id
368 FROM PV_PROGRAM_PAYMENT_MODE
369 where program_id is null
370 and MODE_TYPE = 'CONTRACT'
371 group by geo_hierarchy_id;
372
373 CURSOR c_get_cntr_resp_opt(l_geo_hierarchy_Id NUMBER) IS
374 SELECT l.lookup_code, l.meaning
375 from pv_lookups l, PV_PROGRAM_PAYMENT_MODE p
376 where l.lookup_code = p.mode_of_payment
377 and l.lookup_type = 'PV_CONTRACT_RESPONSE'
378 and l.enabled_flag = 'Y'
379 and p.program_id is null
380 and p.geo_hierarchy_id = l_geo_hierarchy_Id
381 and p.MODE_TYPE = 'CONTRACT'
382 AND NVL(l.start_date_active, SYSDATE)<=SYSDATE
383 AND NVL(l.end_date_active, SYSDATE)>=SYSDATE
384 AND l.lookup_code <> 'REJECT'
385 union all
386 SELECT l.lookup_code, l.meaning
387 from pv_lookups l
388 where l.lookup_type = 'PV_CONTRACT_RESPONSE'
389 and l.enabled_flag = 'Y'
390 AND NVL(l.start_date_active, SYSDATE)<=SYSDATE
391 AND NVL(l.end_date_active, SYSDATE)>=SYSDATE
392 AND l.lookup_code = 'REJECT'
393 order by meaning;
394
395 CURSOR c_get_all_cntr_resp_opt IS
396 SELECT lookup_code, meaning
397 from pv_lookups l
398 where l.lookup_type = 'PV_CONTRACT_RESPONSE'
399 and l.enabled_flag = 'Y'
400 AND NVL(l.start_date_active, SYSDATE)<=SYSDATE
401 AND NVL(l.end_date_active, SYSDATE)>=SYSDATE
402 order by meaning;
403
404 l_geo_hierarchy_ids_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
405 l_geo_hierarchy_id NUMBER;
406 l_get_all_cntr_types boolean := false;
407 l_msg_count number;
408 l_msg_data varchar2(200);
409 l_return_status VARCHAR2(1);
410
411
412 BEGIN
413
414 x_cntr_resp_opt_tbl := JTF_VARCHAR2_TABLE_200();
415
416 for x in c_cntr_geo_hierarchy_ids loop
417 l_geo_hierarchy_ids_tbl.extend;
418 l_geo_hierarchy_ids_tbl(l_geo_hierarchy_ids_tbl.count) := x.geo_hierarchy_id;
419 end loop;
420
421
422 IF l_geo_hierarchy_ids_tbl.count > 0 THEN
423
424 PV_Partner_Geo_Match_PVT.get_Matched_Geo_Hierarchy_Id(
425 p_api_version_number => 1.0
426 ,p_init_msg_list => FND_API.G_TRUE
427 ,x_return_status => l_return_status
428 ,x_msg_count => l_msg_count
429 ,x_msg_data => l_msg_Data
430 ,p_partner_party_id => p_partner_party_id
431 ,p_geo_hierarchy_id => l_geo_hierarchy_ids_tbl
432 ,x_geo_hierarchy_id => l_geo_hierarchy_id
433 );
434
435 IF l_return_Status <> FND_API.G_RET_STS_SUCCESS or l_geo_hierarchy_id is null THEN
436 l_get_all_cntr_types := TRUE;
437 END IF;
438 ELSE
439 l_get_all_cntr_types := TRUE;
440 END IF;
441
442
443 IF l_get_all_cntr_types THEN
444 for x in c_get_all_cntr_resp_opt loop
445 x_cntr_resp_opt_tbl.extend;
446 x_cntr_resp_opt_tbl(x_cntr_resp_opt_tbl.count) := x.lookup_code||'%'||x.meaning;
447 END loop;
448 ELSE
449 for x in c_get_cntr_resp_opt(l_geo_hierarchy_Id) loop
450 x_cntr_resp_opt_tbl.extend;
451 x_cntr_resp_opt_tbl(x_cntr_resp_opt_tbl.count) := x.lookup_code||'%'||x.meaning;
452 END loop;
453 END IF;
454
455 END Get_Contract_Response_Options;
456
457 END PV_Partner_Contracts_PVT;