[Home] [Help]
PACKAGE BODY: APPS.OKL_AM_PARTIES_PVT
Source
1 PACKAGE BODY OKL_AM_PARTIES_PVT AS
2 /* $Header: OKLRAMPB.pls 120.9 2007/12/18 09:16:08 ansethur noship $ */
3
4 ---------------------------------------------------------------------------
5 -- GLOBAL DATASTRUCTURES
6 ---------------------------------------------------------------------------
7
8 -- GLOBAL VARIABLES
9 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
10 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
11 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
12 G_MODULE_NAME CONSTANT VARCHAR2(500) := 'okl.am.plsql.okl_am_parties_pvt.';
13
14 SUBTYPE taiv_rec_type IS okl_trx_ar_invoices_pub.taiv_rec_type;
15
16
17 -- Start of comments
18 --
19 -- Procedure Name : get_contract_party
20 -- Description : Return contract parties for a role
21 -- Business Rules :
22 -- Parameters : contract, role code
23 -- Version : 1.0
24 -- End of comments
25
26 PROCEDURE get_contract_party (
27 p_contract_id IN NUMBER,
28 p_role_code IN VARCHAR2,
29 x_qpyv_tbl OUT NOCOPY qpyv_tbl_type,
30 x_return_status OUT NOCOPY VARCHAR2) IS
31
32 l_qpyv_tbl qpyv_tbl_type;
33 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
34 l_cnt NUMBER := 0;
35
36 CURSOR l_k_party_role_csr (
37 cp_role_code VARCHAR2,
38 cp_contract_id NUMBER) IS
39 SELECT pr.id cpl_id,
40 pr.jtot_object1_code object1_code,
41 pr.object1_id1 object1_id1,
42 pr.object1_id2 object1_id2
43 FROM okc_k_party_roles_b pr
44 WHERE pr.rle_code = cp_role_code
45 AND pr.cle_id IS NULL
46 AND pr.chr_id = cp_contract_id
47 AND pr.dnz_chr_id = cp_contract_id
48 AND rownum = 1; -- Only return 1st party
49
50
51 -- for debug logging
52 L_MODULE_NAME VARCHAR2(500) := G_MODULE_NAME||'get_contract_party';
53 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
54 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
55 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
56
57 BEGIN
58
59 IF (is_debug_procedure_on) THEN
60 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'Begin(+)');
61 END IF;
62
63 IF (is_debug_statement_on) THEN
64 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_contract_id :'||p_contract_id);
65 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_role_code :'||p_role_code);
66 END IF;
67
68 FOR l_k_role_rec IN l_k_party_role_csr (p_role_code, p_contract_id)
69 LOOP
70 l_cnt := l_cnt + 1;
71 l_qpyv_tbl(l_cnt).cpl_id := l_k_role_rec.cpl_id;
72 l_qpyv_tbl(l_cnt).party_jtot_object1_code := l_k_role_rec.object1_code;
73 l_qpyv_tbl(l_cnt).party_object1_id1 := l_k_role_rec.object1_id1;
74 l_qpyv_tbl(l_cnt).party_object1_id2 := l_k_role_rec.object1_id2;
75 END LOOP;
76
77 x_qpyv_tbl := l_qpyv_tbl;
78 x_return_status := l_return_status;
79
80 IF (is_debug_procedure_on) THEN
81 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'End(-)');
82 END IF;
83
84 EXCEPTION
85
86 WHEN OTHERS THEN
87 IF (is_debug_exception_on) THEN
88 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,L_MODULE_NAME, 'EXCEPTION :'||'OTHERS, SQLCODE: '
89 || sqlcode || ' , SQLERRM : ' || sqlerrm);
90 END IF;
91 -- Close open cursors
92 IF l_k_party_role_csr%ISOPEN THEN
93 CLOSE l_k_party_role_csr;
94 END IF;
95
96 -- store SQL error message on message stack for caller
97 OKL_API.SET_MESSAGE (
98 p_app_name => G_APP_NAME
99 ,p_msg_name => G_UNEXPECTED_ERROR
100 ,p_token1 => G_SQLCODE_TOKEN
101 ,p_token1_value => sqlcode
102 ,p_token2 => G_SQLERRM_TOKEN
103 ,p_token2_value => sqlerrm);
104
105 -- notify caller of an UNEXPECTED error
106 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
107
108 END get_contract_party;
109
110
111 -- Start of comments
112 --
113 -- Procedure Name : get_quote_party
114 -- Description : Return quote party based on setup rules
115 -- Business Rules :
116 -- Parameters : contract, rule group, rule code, quote role
117 -- Version : 1.0
118 -- End of comments
119
120 PROCEDURE get_quote_party (
121 p_contract_id IN NUMBER,
122 p_rule_chr_id IN NUMBER,
123 p_rgd_code IN VARCHAR2,
124 p_rdf_code IN VARCHAR2,
125 p_qpt_code IN VARCHAR2,
126 px_qpyv_tbl IN OUT NOCOPY qpyv_tbl_type,
127 x_return_status OUT NOCOPY VARCHAR2) IS
128
129 l_rulv_rec okl_rule_pub.rulv_rec_type;
130 l_qpyv_tbl qpyv_tbl_type;
131
132 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
133 l_date_sent DATE := SYSDATE;
134 l_k_cnt NUMBER;
135 l_q_cnt NUMBER;
136
137 -- for debug logging
138 L_MODULE_NAME VARCHAR2(500) := G_MODULE_NAME||'get_quote_party';
139 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
140 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
141 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
142
143 BEGIN
144
145 IF (is_debug_procedure_on) THEN
146 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'Begin(+)');
147 END IF;
148 IF (is_debug_statement_on) THEN
149 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_contract_id :'||p_contract_id);
150 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_rule_chr_id :'||p_rule_chr_id);
151 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_rgd_code :'||p_rgd_code);
152 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_rdf_code :'||p_rdf_code);
153 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qpt_code :'||p_qpt_code);
154 END IF;
155
156 okl_am_util_pvt.get_rule_record (
157 p_rgd_code => p_rgd_code,
158 p_rdf_code => p_rdf_code,
159 p_chr_id => p_rule_chr_id,
160 p_cle_id => NULL,
161 x_rulv_rec => l_rulv_rec,
162 x_return_status => l_return_status,
163 p_message_yn => FALSE);
164
165 IF (is_debug_statement_on) THEN
166 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
167 'after call to okl_am_util_pvt.get_rule_record :'||l_return_status);
168 END IF;
169
170 IF l_return_status = OKL_API.G_RET_STS_SUCCESS THEN
171 IF l_rulv_rec.rule_information1 IS NOT NULL
172 AND l_rulv_rec.rule_information1 <> G_MISS_CHAR THEN
173 get_contract_party (
174 p_contract_id => p_contract_id,
175 p_role_code => l_rulv_rec.rule_information1,
176 x_qpyv_tbl => l_qpyv_tbl,
177 x_return_status => l_return_status);
178 ELSE
179 l_return_status := OKL_API.G_RET_STS_ERROR;
180 END IF;
181 END IF;
182
183 -- No parties found - create empty row
184 IF l_qpyv_tbl.COUNT = 0 THEN
185 l_qpyv_tbl(1).cpl_id := NULL;
186 l_return_status := OKL_API.G_RET_STS_ERROR;
187 END IF;
188
189 FOR l_k_cnt IN l_qpyv_tbl.FIRST..l_qpyv_tbl.LAST LOOP
190
191 l_qpyv_tbl(l_k_cnt).date_sent := l_date_sent;
192 l_qpyv_tbl(l_k_cnt).qpt_code := p_qpt_code;
193
194 IF l_rulv_rec.rule_information2 <> G_MISS_CHAR THEN
195 IF p_qpt_code = 'ADVANCE_NOTICE' THEN
196 l_qpyv_tbl(l_k_cnt).delay_days := l_rulv_rec.rule_information2;
197 ELSIF p_qpt_code = 'RECIPIENT_ADDITIONAL' THEN
198 l_qpyv_tbl(l_k_cnt).allocation_percentage := l_rulv_rec.rule_information2;
199 END IF;
200 END IF;
201
202 l_q_cnt := NVL (px_qpyv_tbl.COUNT, 0) + 1;
203 px_qpyv_tbl(l_q_cnt) := l_qpyv_tbl(l_k_cnt);
204
205 END LOOP;
206
207 x_return_status := l_return_status;
208
209 IF (is_debug_procedure_on) THEN
210 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'End(-)');
211 END IF;
212
213 EXCEPTION
214
215 WHEN OTHERS THEN
216
217 IF (is_debug_exception_on) THEN
218 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,L_MODULE_NAME, 'EXCEPTION :'||'OTHERS, SQLCODE: '
219 || sqlcode || ' , SQLERRM : ' || sqlerrm);
220 END IF;
221 -- store SQL error message on message stack for caller
222 OKL_API.SET_MESSAGE (
223 p_app_name => G_APP_NAME
224 ,p_msg_name => G_UNEXPECTED_ERROR
225 ,p_token1 => G_SQLCODE_TOKEN
226 ,p_token1_value => sqlcode
227 ,p_token2 => G_SQLERRM_TOKEN
228 ,p_token2_value => sqlerrm);
229
230 -- notify caller of an UNEXPECTED error
231 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
232
233 END get_quote_party;
234
235
236 -- Start of comments
237 --
238 -- Procedure Name : get_rule_quote_parties
239 -- Description : Determine all quote parties using setup rules
240 -- Business Rules :
241 -- Parameters : contract id, optional quote id
242 -- Version : 1.0
243 -- End of comments
244
245 PROCEDURE get_rule_quote_parties (
246 p_qtev_rec IN qtev_rec_type,
247 x_qpyv_tbl OUT NOCOPY qpyv_tbl_type,
248 x_return_status OUT NOCOPY VARCHAR2) IS
249
250 l_qpyv_tbl qpyv_tbl_type;
251 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
252 l_overall_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
253
254 l_contract_id NUMBER := p_qtev_rec.khr_id;
255 l_rule_chr_id NUMBER;
256
257 l_recipient_rg VARCHAR2(30);
258 l_approver_rg VARCHAR2(30);
259 l_cou_copy_rg VARCHAR2(30);
260
261 -- for debug logging
262 L_MODULE_NAME VARCHAR2(500) := G_MODULE_NAME||'get_rule_quote_parties';
263 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
264 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
265 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
266
267 BEGIN
268
269 IF (is_debug_procedure_on) THEN
270 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'Begin(+)');
271 END IF;
272
273 IF (is_debug_statement_on) THEN
274 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'after call to get_rule_quote_parties :'||l_return_status);
275 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.id : '||p_qtev_rec.id);
276 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.qrs_code : '||p_qtev_rec.qrs_code);
277 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.qst_code : '||p_qtev_rec.qst_code);
278 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.consolidated_qte_id : '||p_qtev_rec.consolidated_qte_id );
279 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.khr_id : '||p_qtev_rec.khr_id);
280 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.art_id : '||p_qtev_rec.art_id);
281 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.qtp_code : '||p_qtev_rec.qtp_code);
282 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.trn_code : '||p_qtev_rec.trn_code);
283 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.pdt_id : '||p_qtev_rec.pdt_id);
284 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.date_effective_from : '||p_qtev_rec.date_effective_from);
285 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.quote_number : '||p_qtev_rec.quote_number);
286 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.early_termination_yn : '||p_qtev_rec.early_termination_yn);
287 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.partial_yn : '||p_qtev_rec.partial_yn);
288 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.preproceeds_yn : '||p_qtev_rec.preproceeds_yn);
289 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.summary_format_yn : '||p_qtev_rec.summary_format_yn);
290 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.consolidated_yn : '||p_qtev_rec.consolidated_yn);
291 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.payment_received_yn : '||p_qtev_rec.payment_received_yn);
292 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.requested_by : '||p_qtev_rec.requested_by);
293 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.approved_yn : '||p_qtev_rec.approved_yn);
294 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.accepted_yn : '||p_qtev_rec.accepted_yn);
295 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.org_id : '||p_qtev_rec.org_id);
296 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.legal_entity_id : '||p_qtev_rec.legal_entity_id);
297 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.repo_quote_indicator_yn : '||p_qtev_rec.repo_quote_indicator_yn);
298 END IF;
299
300 IF l_contract_id IS NULL
301 OR l_contract_id = G_MISS_NUM THEN
302
303 l_overall_status := OKL_API.G_RET_STS_ERROR;
304
305 OKC_API.SET_MESSAGE (
306 p_app_name => OKC_API.G_APP_NAME,
307 p_msg_name => 'OKC_NO_PARAMS',
308 p_token1 => 'PARAM',
309 p_token1_value => 'CONTRACT_ID',
310 p_token2 => 'PROCESS',
311 p_token2_value => 'GET_RULE_QUOTE_PARTIES');
312
313 END IF;
314
315 l_rule_chr_id := okl_am_util_pvt.get_rule_chr_id (p_qtev_rec);
316
317 IF p_qtev_rec.qtp_code LIKE 'TER_RECOURSE%' THEN
318 l_recipient_rg := 'AVQR1R';
319 l_approver_rg := 'AVQR5A';
320 l_cou_copy_rg := 'AVQR9F';
321 ELSE
322 l_recipient_rg := 'AMQR1R';
323 l_approver_rg := 'AMQR5A';
324 l_cou_copy_rg := 'AMQR9F';
325 END IF;
326
327 IF l_overall_status = OKL_API.G_RET_STS_SUCCESS THEN
328
329 get_quote_party (
330 p_contract_id => l_contract_id,
331 p_rule_chr_id => l_rule_chr_id,
332 p_rgd_code => l_recipient_rg,
333 p_rdf_code => 'AMLCRO',
334 p_qpt_code => 'RECIPIENT',
335 px_qpyv_tbl => l_qpyv_tbl,
336 x_return_status => l_return_status);
337
338
339 IF (is_debug_statement_on) THEN
340 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
341 'after call to get_quote_party :'||l_return_status);
342 END IF;
343
344 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
345 IF l_overall_status <> OKL_API.G_RET_STS_UNEXP_ERROR THEN
346 l_overall_status := l_return_status;
347 END IF;
348 END IF;
349
350 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS
351 OR l_qpyv_tbl.COUNT = 0 THEN
352 okl_am_util_pvt.set_message (
353 p_app_name => G_APP_NAME
354 ,p_msg_name => 'OKL_AM_QTE_RECIPIENT_NOT_FOUND'
355 ,p_token1 => 'QUOTE_PARTY_TYPE'
356 ,p_token1_value => 'RECIPIENT');
357 END IF;
358
359 get_quote_party (
360 p_contract_id => l_contract_id,
361 p_rule_chr_id => l_rule_chr_id,
362 p_rgd_code => l_recipient_rg,
363 p_rdf_code => 'AMLCRP',
364 p_qpt_code => 'RECIPIENT_ADDITIONAL',
365 px_qpyv_tbl => l_qpyv_tbl,
366 x_return_status => l_return_status);
367
368 IF (is_debug_statement_on) THEN
369 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
370 'after call to get_quote_party 1 :'||l_return_status);
371 END IF;
372
373 get_quote_party (
374 p_contract_id => l_contract_id,
375 p_rule_chr_id => l_rule_chr_id,
376 p_rgd_code => l_approver_rg,
377 p_rdf_code => 'AMLCAP',
378 p_qpt_code => 'APPROVER',
379 px_qpyv_tbl => l_qpyv_tbl,
380 x_return_status => l_return_status);
381
382 IF (is_debug_statement_on) THEN
383 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
384 'after call to get_quote_party 2 :'||l_return_status);
385 END IF;
386
387 get_quote_party (
388 p_contract_id => l_contract_id,
389 p_rule_chr_id => l_rule_chr_id,
390 p_rgd_code => l_approver_rg,
391 p_rdf_code => 'AMLCAV',
392 p_qpt_code => 'ADVANCE_NOTICE',
393 px_qpyv_tbl => l_qpyv_tbl,
394 x_return_status => l_return_status);
395
396 IF (is_debug_statement_on) THEN
397 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
398 'after call to get_quote_party 3 :'||l_return_status);
399 END IF;
400
401 get_quote_party (
402 p_contract_id => l_contract_id,
403 p_rule_chr_id => l_rule_chr_id,
404 p_rgd_code => l_cou_copy_rg,
405 p_rdf_code => 'AMLCCO',
406 p_qpt_code => 'FYI',
407 px_qpyv_tbl => l_qpyv_tbl,
408 x_return_status => l_return_status);
409
410 IF (is_debug_statement_on) THEN
411 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
412 'after call to get_quote_party 4 :'||l_return_status);
413 END IF;
414
415 END IF;
416
417 x_qpyv_tbl := l_qpyv_tbl;
418 x_return_status := l_overall_status;
419
420 IF (is_debug_procedure_on) THEN
421 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'End(-)');
422 END IF;
423
424 EXCEPTION
425
426 WHEN OTHERS THEN
427
428 IF (is_debug_exception_on) THEN
429 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,L_MODULE_NAME, 'EXCEPTION :'||'OTHERS, SQLCODE: '
430 || sqlcode || ' , SQLERRM : ' || sqlerrm);
431 END IF;
432
433 -- store SQL error message on message stack for caller
434 OKL_API.SET_MESSAGE (
435 p_app_name => G_APP_NAME
436 ,p_msg_name => G_UNEXPECTED_ERROR
437 ,p_token1 => G_SQLCODE_TOKEN
438 ,p_token1_value => sqlcode
439 ,p_token2 => G_SQLERRM_TOKEN
440 ,p_token2_value => sqlerrm);
441
442 -- notify caller of an UNEXPECTED error
443 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
444
445 END get_rule_quote_parties;
446
447
448 -- Start of comments
449 --
450 -- Procedure Name : fetch_rule_quote_parties
451 -- Description : Return quote parties using setup rules
452 -- Business Rules :
453 -- Parameters : contract_id
454 -- Version : 1.0
455 -- End of comments
456
457 PROCEDURE fetch_rule_quote_parties (
458 p_api_version IN NUMBER,
459 p_init_msg_list IN VARCHAR2,
460 x_msg_count OUT NOCOPY NUMBER,
461 x_msg_data OUT NOCOPY VARCHAR2,
462 x_return_status OUT NOCOPY VARCHAR2,
463 p_qtev_rec IN qtev_rec_type,
464 x_qpyv_tbl OUT NOCOPY qpyv_tbl_type,
465 x_q_party_uv_tbl OUT NOCOPY q_party_uv_tbl_type,
466 x_record_count OUT NOCOPY NUMBER) IS
467
468 l_q_party_tbl q_party_uv_tbl_type;
469 l_object_tbl okl_am_util_pvt.jtf_object_tbl_type;
470 l_qpyv_tbl qpyv_tbl_type;
471 l_cnt NUMBER := 0;
472
473 CURSOR l_q_party_role_csr (
474 cp_cpl_id NUMBER,
475 cp_khr_id NUMBER,
476 cp_qpt_code VARCHAR2) IS
477 SELECT kh.buy_or_sell k_buy_or_sell,
478 lq.meaning qp_party_role,
479 pr.rle_code kp_role_code,
480 lk.meaning kp_party_role
481 FROM okc_k_party_roles_b pr,
482 okc_k_headers_b kh,
483 fnd_lookups lq,
484 fnd_lookups lk
485 WHERE pr.id = cp_cpl_id
486 AND kh.id = cp_khr_id
487 AND kh.id = pr.chr_id
488 AND lq.lookup_code = cp_qpt_code
489 AND lq.lookup_type = 'OKL_QUOTE_PARTY_TYPE'
490 AND lk.lookup_code = pr.rle_code
491 AND lk.lookup_type = 'OKC_ROLE';
492
493 CURSOR l_q_no_party_csr (
494 cp_khr_id NUMBER,
495 cp_qpt_code VARCHAR2) IS
496 SELECT kh.buy_or_sell k_buy_or_sell,
497 lq.meaning qp_party_role
498 FROM okc_k_headers_v kh,
499 fnd_lookups lq
500 WHERE kh.id = cp_khr_id
501 AND lq.lookup_code = cp_qpt_code
502 AND lq.lookup_type = 'OKL_QUOTE_PARTY_TYPE';
503
504 l_q_role_rec l_q_party_role_csr%ROWTYPE;
505 l_q_no_role_rec l_q_no_party_csr%ROWTYPE;
506
507 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
508 l_overall_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
509
510 l_api_name CONSTANT VARCHAR2(30) := 'fetch_rule_quote_parties';
511 l_api_version CONSTANT NUMBER := G_API_VERSION;
512 l_msg_count NUMBER := G_MISS_NUM;
513 l_msg_data VARCHAR2(2000);
514 l_contract_id NUMBER := p_qtev_rec.khr_id;
515
516 -- for debug logging
517 L_MODULE_NAME VARCHAR2(500) := G_MODULE_NAME||'fetch_rule_quote_parties';
518 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
519 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
520 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
521
522 BEGIN
523
524 IF (is_debug_procedure_on) THEN
525 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'Begin(+)');
526 END IF;
527 IF (is_debug_statement_on) THEN
528 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'after call to get_rule_quote_parties :'||l_return_status);
529 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.id : '||p_qtev_rec.id);
530 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.qrs_code : '||p_qtev_rec.qrs_code);
531 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.qst_code : '||p_qtev_rec.qst_code);
532 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.consolidated_qte_id : '||p_qtev_rec.consolidated_qte_id );
533 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.khr_id : '||p_qtev_rec.khr_id);
534 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.art_id : '||p_qtev_rec.art_id);
535 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.qtp_code : '||p_qtev_rec.qtp_code);
536 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.trn_code : '||p_qtev_rec.trn_code);
537 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.pdt_id : '||p_qtev_rec.pdt_id);
538 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.date_effective_from : '||p_qtev_rec.date_effective_from);
539 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.quote_number : '||p_qtev_rec.quote_number);
540 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.early_termination_yn : '||p_qtev_rec.early_termination_yn);
541 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.partial_yn : '||p_qtev_rec.partial_yn);
542 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.preproceeds_yn : '||p_qtev_rec.preproceeds_yn);
543 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.summary_format_yn : '||p_qtev_rec.summary_format_yn);
544 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.consolidated_yn : '||p_qtev_rec.consolidated_yn);
545 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.payment_received_yn : '||p_qtev_rec.payment_received_yn);
546 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.requested_by : '||p_qtev_rec.requested_by);
547 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.approved_yn : '||p_qtev_rec.approved_yn);
548 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.accepted_yn : '||p_qtev_rec.accepted_yn);
549 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.org_id : '||p_qtev_rec.org_id);
550 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.legal_entity_id : '||p_qtev_rec.legal_entity_id);
551 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.repo_quote_indicator_yn : '||p_qtev_rec.repo_quote_indicator_yn);
552 END IF;
553
554 -- ***************************************************************
555 -- Check API version, initialize message list and create savepoint
556 -- ***************************************************************
557
558 l_return_status := OKL_API.START_ACTIVITY (
559 l_api_name,
560 G_PKG_NAME,
561 p_init_msg_list,
562 l_api_version,
563 p_api_version,
564 '_PVT',
565 x_return_status);
566
567 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
568 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
569 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
570 RAISE OKL_API.G_EXCEPTION_ERROR;
571 END IF;
572
573 -- ***********************************
574 -- Extract parties from contract rules
575 -- ***********************************
576
577 get_rule_quote_parties (
578 p_qtev_rec => p_qtev_rec,
579 x_qpyv_tbl => l_qpyv_tbl,
580 x_return_status => l_return_status);
581
582
583 IF (is_debug_statement_on) THEN
584 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
585 'after call to get_rule_quote_parties :'||l_return_status);
586 END IF;
587
588 -- Errors are acceptable
589 -- IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
590 -- RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
591 -- ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
592 -- RAISE OKL_API.G_EXCEPTION_ERROR;
593 -- END IF;
594
595 -- ********************
596 -- Process every record
597 -- ********************
598
599 IF l_qpyv_tbl.COUNT > 0 THEN
600
601 FOR i IN l_qpyv_tbl.FIRST..l_qpyv_tbl.LAST LOOP
602
603 l_q_role_rec.kp_role_code := NULL;
604
605 OPEN l_q_party_role_csr (
606 l_qpyv_tbl(i).cpl_id, l_contract_id, l_qpyv_tbl(i).qpt_code);
607 FETCH l_q_party_role_csr INTO l_q_role_rec;
608 CLOSE l_q_party_role_csr;
609
610 IF l_q_role_rec.kp_role_code IS NOT NULL THEN
611
612 -- Get Party Object
613 okl_am_util_pvt.get_object_details (
614 p_object_code => l_qpyv_tbl(i).party_jtot_object1_code,
615 p_object_id1 => l_qpyv_tbl(i).party_object1_id1,
616 p_object_id2 => l_qpyv_tbl(i).party_object1_id2,
617 x_object_tbl => l_object_tbl,
618 x_return_status => l_return_status);
619
620 IF (is_debug_statement_on) THEN
621 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
622 'after call to okl_am_util_pvt.get_object_details :'||l_return_status);
623 END IF;
624
625 IF l_return_status = OKL_API.G_RET_STS_SUCCESS
626 AND l_object_tbl.COUNT > 0 THEN
627 FOR l_ind IN l_object_tbl.FIRST..l_object_tbl.LAST LOOP
628 -- Party Object is found
629 l_cnt := l_cnt + 1;
630 l_q_party_tbl(l_cnt).contract_id := l_contract_id;
631 l_q_party_tbl(l_cnt).kp_party_id := l_qpyv_tbl(i).cpl_id;
632 l_q_party_tbl(l_cnt).qp_role_code := l_qpyv_tbl(i).qpt_code;
633 l_q_party_tbl(l_cnt).k_buy_or_sell := l_q_role_rec.k_buy_or_sell;
634 l_q_party_tbl(l_cnt).qp_party_role := l_q_role_rec.qp_party_role;
635 l_q_party_tbl(l_cnt).kp_role_code := l_q_role_rec.kp_role_code;
636 l_q_party_tbl(l_cnt).kp_party_role := l_q_role_rec.kp_party_role;
637 l_q_party_tbl(l_cnt).po_party_id1 := l_object_tbl(l_ind).id1;
638 l_q_party_tbl(l_cnt).po_party_id2 := l_object_tbl(l_ind).id2;
639 l_q_party_tbl(l_cnt).po_party_object := l_object_tbl(l_ind).object_code;
640 l_q_party_tbl(l_cnt).po_party_name := l_object_tbl(l_ind).name;
641 l_q_party_tbl(l_cnt).po_party_desc := l_object_tbl(l_ind).description;
642 END LOOP;
643 ELSE
644 -- Party Object is not found
645 l_cnt := l_cnt + 1;
646 l_q_party_tbl(l_cnt).contract_id := l_contract_id;
647 l_q_party_tbl(l_cnt).kp_party_id := l_qpyv_tbl(i).cpl_id;
648 l_q_party_tbl(l_cnt).qp_role_code := l_qpyv_tbl(i).qpt_code;
649 l_q_party_tbl(l_cnt).k_buy_or_sell := l_q_role_rec.k_buy_or_sell;
650 l_q_party_tbl(l_cnt).qp_party_role := l_q_role_rec.qp_party_role;
651 l_q_party_tbl(l_cnt).kp_role_code := l_q_role_rec.kp_role_code;
652 l_q_party_tbl(l_cnt).kp_party_role := l_q_role_rec.kp_party_role;
653 l_q_party_tbl(l_cnt).po_party_id1 := l_qpyv_tbl(i).party_jtot_object1_code;
654 l_q_party_tbl(l_cnt).po_party_id2 := l_qpyv_tbl(i).party_object1_id1;
655 l_q_party_tbl(l_cnt).po_party_object := l_qpyv_tbl(i).party_object1_id2;
656 l_q_party_tbl(l_cnt).po_party_name := NULL;
657 l_q_party_tbl(l_cnt).po_party_desc := NULL;
658 END IF;
659
660 ELSE
661
662 OPEN l_q_no_party_csr (l_contract_id, l_qpyv_tbl(i).qpt_code);
663 FETCH l_q_no_party_csr INTO l_q_no_role_rec;
664 CLOSE l_q_no_party_csr;
665
666 -- Party Role is not found
667 l_cnt := l_cnt + 1;
668 l_q_party_tbl(l_cnt).contract_id := l_contract_id;
669 l_q_party_tbl(l_cnt).kp_party_id := l_qpyv_tbl(i).cpl_id;
670 l_q_party_tbl(l_cnt).qp_role_code := l_qpyv_tbl(i).qpt_code;
671 l_q_party_tbl(l_cnt).k_buy_or_sell := l_q_no_role_rec.k_buy_or_sell;
672 l_q_party_tbl(l_cnt).qp_party_role := l_q_no_role_rec.qp_party_role;
673 l_q_party_tbl(l_cnt).kp_role_code := NULL;
674 l_q_party_tbl(l_cnt).kp_party_role := NULL;
675 l_q_party_tbl(l_cnt).po_party_id1 := NULL;
676 l_q_party_tbl(l_cnt).po_party_id2 := NULL;
677 l_q_party_tbl(l_cnt).po_party_object := NULL;
678 l_q_party_tbl(l_cnt).po_party_name := NULL;
679 l_q_party_tbl(l_cnt).po_party_desc := NULL;
680
681 END IF;
682
683 END LOOP;
684
685 END IF;
686
687 -- **************
688 -- Return results
689 -- **************
690
691 x_record_count := l_q_party_tbl.COUNT;
692 x_q_party_uv_tbl := l_q_party_tbl;
693 x_qpyv_tbl := l_qpyv_tbl;
694 x_return_status := l_overall_status;
695
696 OKL_API.END_ACTIVITY (x_msg_count, x_msg_data);
697
698 IF (is_debug_procedure_on) THEN
699 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'End(-)');
700 END IF;
701
702 EXCEPTION
703
704 WHEN OKL_API.G_EXCEPTION_ERROR THEN
705
706 IF (is_debug_exception_on) THEN
707 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,L_MODULE_NAME, 'EXCEPTION :'||'USER DEFINED');
708 END IF;
709
710 x_return_status := OKL_API.HANDLE_EXCEPTIONS
711 (
712 l_api_name,
713 G_PKG_NAME,
714 'OKL_API.G_RET_STS_ERROR',
715 x_msg_count,
716 x_msg_data,
717 '_PVT'
718 );
719
720 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
721
722 IF (is_debug_exception_on) THEN
723 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,L_MODULE_NAME, 'EXCEPTION :'||'UNEXPECTED');
724 END IF;
725
726 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
727 (
728 l_api_name,
729 G_PKG_NAME,
730 'OKL_API.G_RET_STS_UNEXP_ERROR',
731 x_msg_count,
732 x_msg_data,
733 '_PVT'
734 );
735
736 WHEN OTHERS THEN
737
738 IF (is_debug_exception_on) THEN
739 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,L_MODULE_NAME, 'EXCEPTION :'||'OTHERS, SQLCODE: '
740 || sqlcode || ' , SQLERRM : ' || sqlerrm);
741 END IF;
742
743 -- Close open cursors
744 IF l_q_party_role_csr%ISOPEN THEN
745 CLOSE l_q_party_role_csr;
746 END IF;
747
748 IF l_q_no_party_csr%ISOPEN THEN
749 CLOSE l_q_no_party_csr;
750 END IF;
751
752 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
753 (
754 l_api_name,
755 G_PKG_NAME,
756 'OTHERS',
757 x_msg_count,
758 x_msg_data,
759 '_PVT'
760 );
761
762 END fetch_rule_quote_parties;
763
764
765 -- Start of comments
766 --
767 -- Procedure Name : create_partner_as_recipient
768 -- Description : Assign a vendor partner as a quote recipient
769 -- Business Rules :
770 -- Parameters : quote_id and contract_id
771 -- Version : 1.0
772 -- End of comments
773
774 PROCEDURE create_partner_as_recipient (
775 p_qtev_rec IN qtev_rec_type,
776 p_validate_only IN BOOLEAN,
777 x_qpyv_tbl OUT NOCOPY qpyv_tbl_type,
778 x_return_status OUT NOCOPY VARCHAR2) IS
779
780 lp_qpyv_tbl qpyv_tbl_type;
781 lx_qpyv_tbl qpyv_tbl_type;
782
783 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
784 l_overall_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
785 l_api_version CONSTANT NUMBER := G_API_VERSION;
786 l_msg_count NUMBER := G_MISS_NUM;
787 l_msg_data VARCHAR2(2000);
788
789 -- Get contract program partner
790 CURSOR l_partner_csr (cp_chr_id NUMBER) IS
791 SELECT khr.id khr_id,
792 par.id par_id,
793 kpr.id kpr_id,
794 kpr.jtot_object1_code object1_code,
795 kpr.object1_id1 object1_id1,
796 kpr.object1_id2 object1_id2
797 FROM okl_k_headers khr,
798 okc_k_headers_all_b par,
799 okc_k_party_roles_b kpr
800 WHERE khr.id = cp_chr_id
801 AND par.id (+) = khr.khr_id
802 AND par.scs_code (+) = 'PROGRAM'
803 AND kpr.dnz_chr_id (+) = par.id
804 AND kpr.rle_code (+) = 'OKL_VENDOR';
805
806 l_partner_rec l_partner_csr%ROWTYPE;
807
808 -- for debug logging
809 L_MODULE_NAME VARCHAR2(500) := G_MODULE_NAME||'create_partner_as_recipient';
810 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
811 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
812 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
813
814 BEGIN
815
816 IF (is_debug_procedure_on) THEN
817 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'Begin(+)');
818 END IF;
819 IF (is_debug_statement_on) THEN
820 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'after call to get_rule_quote_parties :'||l_return_status);
821 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.id : '||p_qtev_rec.id);
822 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.qrs_code : '||p_qtev_rec.qrs_code);
823 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.qst_code : '||p_qtev_rec.qst_code);
824 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.consolidated_qte_id : '||p_qtev_rec.consolidated_qte_id );
825 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.khr_id : '||p_qtev_rec.khr_id);
826 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.art_id : '||p_qtev_rec.art_id);
827 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.qtp_code : '||p_qtev_rec.qtp_code);
828 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.trn_code : '||p_qtev_rec.trn_code);
829 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.pdt_id : '||p_qtev_rec.pdt_id);
830 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.date_effective_from : '||p_qtev_rec.date_effective_from);
831 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.quote_number : '||p_qtev_rec.quote_number);
832 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.early_termination_yn : '||p_qtev_rec.early_termination_yn);
833 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.partial_yn : '||p_qtev_rec.partial_yn);
834 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.preproceeds_yn : '||p_qtev_rec.preproceeds_yn);
835 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.summary_format_yn : '||p_qtev_rec.summary_format_yn);
836 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.consolidated_yn : '||p_qtev_rec.consolidated_yn);
837 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.payment_received_yn : '||p_qtev_rec.payment_received_yn);
838 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.requested_by : '||p_qtev_rec.requested_by);
839 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.approved_yn : '||p_qtev_rec.approved_yn);
840 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.accepted_yn : '||p_qtev_rec.accepted_yn);
841 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.org_id : '||p_qtev_rec.org_id);
842 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.legal_entity_id : '||p_qtev_rec.legal_entity_id);
843 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.repo_quote_indicator_yn : '||p_qtev_rec.repo_quote_indicator_yn);
844 END IF;
845
846 -- *******************
847 -- Validate parameters
848 -- *******************
849
850 IF NOT p_validate_only THEN
851
852 IF p_qtev_rec.id IS NULL
853 OR p_qtev_rec.id = G_MISS_NUM THEN
854
855 l_overall_status := OKL_API.G_RET_STS_ERROR;
856 OKC_API.SET_MESSAGE (
857 p_app_name => OKC_API.G_APP_NAME,
858 p_msg_name => 'OKC_NO_PARAMS',
859 p_token1 => 'PARAM',
860 p_token1_value => 'QUOTE_ID',
861 p_token2 => 'PROCESS',
862 p_token2_value => 'CREATE_PARTNER_AS_RECIPIENT');
863
864 END IF;
865
866 END IF;
867
868 IF p_qtev_rec.khr_id IS NULL
869 OR p_qtev_rec.khr_id = G_MISS_NUM THEN
870
871 l_overall_status := OKL_API.G_RET_STS_ERROR;
872 OKC_API.SET_MESSAGE (
873 p_app_name => OKC_API.G_APP_NAME,
874 p_msg_name => 'OKC_NO_PARAMS',
875 p_token1 => 'PARAM',
876 p_token1_value => 'CONTRACT_ID',
877 p_token2 => 'PROCESS',
878 p_token2_value => 'CREATE_PARTNER_AS_RECIPIENT');
879
880 END IF;
881
882 -- ********************
883 -- Choose quote parties
884 -- ********************
885
886 IF l_overall_status = OKL_API.G_RET_STS_SUCCESS THEN
887
888 OPEN l_partner_csr (p_qtev_rec.khr_id);
889 FETCH l_partner_csr INTO l_partner_rec;
890 CLOSE l_partner_csr;
891
892 IF l_partner_rec.khr_id IS NULL THEN
893 l_return_status := OKL_API.G_RET_STS_ERROR;
894 OKC_API.SET_MESSAGE (
895 p_app_name => G_OKC_APP_NAME,
896 p_msg_name => G_INVALID_VALUE,
897 p_token1 => G_COL_NAME_TOKEN,
898 p_token1_value => 'Contract Id');
899 ELSIF l_partner_rec.par_id IS NULL THEN
900 l_return_status := OKL_API.G_RET_STS_ERROR;
901 okl_am_util_pvt.set_message(
902 p_app_name => G_APP_NAME
903 ,p_msg_name => 'OKL_VP_INVALID_PARENT_AGRMNT');
904 ELSIF l_partner_rec.kpr_id IS NULL THEN
905 l_overall_status := OKL_API.G_RET_STS_ERROR;
906 OKC_API.SET_MESSAGE (
907 p_app_name => G_OKC_APP_NAME,
908 p_msg_name => G_INVALID_VALUE,
909 p_token1 => G_COL_NAME_TOKEN,
910 p_token1_value => 'Vendor Program Party');
911 ELSE
912
913 lp_qpyv_tbl(1).party_jtot_object1_code := l_partner_rec.object1_code;
914 lp_qpyv_tbl(1).party_object1_id1 := l_partner_rec.object1_id1;
915 lp_qpyv_tbl(1).party_object1_id2 := l_partner_rec.object1_id2;
916 lp_qpyv_tbl(1).qte_id := p_qtev_rec.id;
917 lp_qpyv_tbl(1).date_sent := SYSDATE;
918 lp_qpyv_tbl(1).qpt_code := 'RECIPIENT';
919 lp_qpyv_tbl(1).allocation_percentage := 100;
920
921 END IF;
922
923 END IF;
924
925 -- ******************
926 -- Save quote parties
927 -- ******************
928
929 IF NOT p_validate_only THEN
930
931 IF l_overall_status = OKL_API.G_RET_STS_SUCCESS THEN
932
933 IF lp_qpyv_tbl.COUNT > 0 THEN
934
935 okl_quote_parties_pub.insert_quote_parties (
936 p_api_version => l_api_version,
937 p_init_msg_list => OKL_API.G_FALSE,
938 x_return_status => l_return_status,
939 x_msg_count => l_msg_count,
940 x_msg_data => l_msg_data,
941 p_qpyv_tbl => lp_qpyv_tbl,
942 x_qpyv_tbl => lx_qpyv_tbl);
943
944 IF (is_debug_statement_on) THEN
945 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
946 'after call to okl_quote_parties_pub.insert_quote_parties :'||l_return_status);
947 END IF;
948
949 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
950 IF l_overall_status <> OKL_API.G_RET_STS_UNEXP_ERROR THEN
951 l_overall_status := l_return_status;
952 END IF;
953 END IF;
954
955 END IF;
956 END IF;
957 END IF;
958
959 x_qpyv_tbl := lx_qpyv_tbl;
960 x_return_status := l_overall_status;
961
962 IF (is_debug_procedure_on) THEN
963 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'End(-)');
964 END IF;
965
966 EXCEPTION
967
968 WHEN OTHERS THEN
969
970 IF (is_debug_exception_on) THEN
971 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,L_MODULE_NAME, 'EXCEPTION :'||'OTHERS, SQLCODE: '
972 || sqlcode || ' , SQLERRM : ' || sqlerrm);
973 END IF;
974
975 -- Close open cursors
976 IF l_partner_csr%ISOPEN THEN
977 CLOSE l_partner_csr;
978 END IF;
979
980 -- store SQL error message on message stack for caller
981 OKL_API.SET_MESSAGE (
982 p_app_name => G_APP_NAME
983 ,p_msg_name => G_UNEXPECTED_ERROR
984 ,p_token1 => G_SQLCODE_TOKEN
985 ,p_token1_value => sqlcode
986 ,p_token2 => G_SQLERRM_TOKEN
987 ,p_token2_value => sqlerrm);
988
989 -- notify caller of an UNEXPECTED error
990 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
991
992 END create_partner_as_recipient;
993
994
995 -- Start of comments
996 --
997 -- Procedure Name : create_quote_parties
998 -- Description : Create all quote parties using setup rules
999 -- Business Rules :
1000 -- Parameters : quote id, list of parties or contract id
1001 -- History : PAGARG 29-SEP-04 Added a validation for receipient role
1002 -- : If quote type is rollover then recipient can be
1003 -- : LESSEE only. In case it is not LESSEE then throw
1004 -- : error
1005 -- : PAGARG 19-Nov-2004 Bug# 4021165
1006 -- : Set overall status and don't raise exception in
1007 -- : case of error in rollover quote party.
1008 -- : Use correct application name while setting message
1009 -- : rmunjulu 4131592 For rollover quotes additional receipient can only be LESSEE
1010 -- Version : 1.0
1011 -- End of comments
1012
1013 PROCEDURE create_quote_parties (
1014 p_qtev_rec IN qtev_rec_type,
1015 p_qpyv_tbl IN qpyv_tbl_type,
1016 p_validate_only IN BOOLEAN,
1017 x_qpyv_tbl OUT NOCOPY qpyv_tbl_type,
1018 x_return_status OUT NOCOPY VARCHAR2) IS
1019
1020 l_qpyv_tbl qpyv_tbl_type;
1021 lp_qpyv_tbl qpyv_tbl_type;
1022 lx_qpyv_tbl qpyv_tbl_type;
1023 l_taiv_rec taiv_rec_type;
1024 e_taiv_rec taiv_rec_type;
1025
1026 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1027 l_overall_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1028 l_api_version CONSTANT NUMBER := G_API_VERSION;
1029 l_msg_count NUMBER := G_MISS_NUM;
1030 l_msg_data VARCHAR2(2000);
1031 l_party_name VARCHAR2(1000);
1032
1033 l_seq NUMBER := 0;
1034 l_approver_found BOOLEAN := FALSE;
1035 l_recipient_found BOOLEAN := FALSE;
1036 l_email_missing BOOLEAN := FALSE;
1037
1038 l_allc_total NUMBER := 0; -- Total allocated
1039 l_no_allc NUMBER := 0; -- Recipients without allocation
1040 l_alloc_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1041
1042 CURSOR l_kpr_csr (cp_cpl_id IN NUMBER, cp_khr_id NUMBER) IS
1043 SELECT kpr.rle_code rle_code,
1044 kpr.jtot_object1_code object1_code,
1045 kpr.object1_id1 object1_id1,
1046 kpr.object1_id2 object1_id2
1047 FROM okc_k_party_roles_b kpr
1048 WHERE kpr.id = cp_cpl_id
1049 AND kpr.chr_id = cp_khr_id
1050 AND kpr.dnz_chr_id = cp_khr_id;
1051
1052 l_k_role_rec l_kpr_csr%ROWTYPE;
1053 e_k_role_rec l_kpr_csr%ROWTYPE;
1054
1055 -- for debug logging
1056 L_MODULE_NAME VARCHAR2(500) := G_MODULE_NAME||'create_quote_parties';
1057 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
1058 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
1059 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
1060
1061 BEGIN
1062
1063 IF (is_debug_procedure_on) THEN
1064 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'Begin(+)');
1065 END IF;
1066
1067 IF (is_debug_statement_on) THEN
1068 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'after call to get_rule_quote_parties :'||l_return_status);
1069 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.id : '||p_qtev_rec.id);
1070 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.qrs_code : '||p_qtev_rec.qrs_code);
1071 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.qst_code : '||p_qtev_rec.qst_code);
1072 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.consolidated_qte_id : '||p_qtev_rec.consolidated_qte_id );
1073 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.khr_id : '||p_qtev_rec.khr_id);
1074 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.art_id : '||p_qtev_rec.art_id);
1075 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.qtp_code : '||p_qtev_rec.qtp_code);
1076 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.trn_code : '||p_qtev_rec.trn_code);
1077 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.pdt_id : '||p_qtev_rec.pdt_id);
1078 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.date_effective_from : '||p_qtev_rec.date_effective_from);
1079 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.quote_number : '||p_qtev_rec.quote_number);
1080 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.early_termination_yn : '||p_qtev_rec.early_termination_yn);
1081 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.partial_yn : '||p_qtev_rec.partial_yn);
1082 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.preproceeds_yn : '||p_qtev_rec.preproceeds_yn);
1083 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.summary_format_yn : '||p_qtev_rec.summary_format_yn);
1084 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.consolidated_yn : '||p_qtev_rec.consolidated_yn);
1085 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.payment_received_yn : '||p_qtev_rec.payment_received_yn);
1086 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.requested_by : '||p_qtev_rec.requested_by);
1087 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.approved_yn : '||p_qtev_rec.approved_yn);
1088 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.accepted_yn : '||p_qtev_rec.accepted_yn);
1089 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.org_id : '||p_qtev_rec.org_id);
1090 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.legal_entity_id : '||p_qtev_rec.legal_entity_id);
1091 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_qtev_rec.repo_quote_indicator_yn : '||p_qtev_rec.repo_quote_indicator_yn);
1092 END IF;
1093 -- *******************
1094 -- Validate parameters
1095 -- *******************
1096
1097 IF NOT p_validate_only THEN
1098
1099 IF p_qtev_rec.id IS NULL
1100 OR p_qtev_rec.id = G_MISS_NUM THEN
1101
1102 l_overall_status := OKL_API.G_RET_STS_ERROR;
1103
1104 OKC_API.SET_MESSAGE (
1105 p_app_name => OKC_API.G_APP_NAME,
1106 p_msg_name => 'OKC_NO_PARAMS',
1107 p_token1 => 'PARAM',
1108 p_token1_value => 'QUOTE_ID',
1109 p_token2 => 'PROCESS',
1110 p_token2_value => 'CREATE_QUOTE_PARTIES');
1111
1112 END IF;
1113
1114 END IF;
1115
1116 -- ********************
1117 -- Choose quote parties
1118 -- ********************
1119
1120 IF l_overall_status = OKL_API.G_RET_STS_SUCCESS THEN
1121
1122 IF p_qpyv_tbl.COUNT = 0 THEN
1123
1124 -- Use contract rules to decide on quote parties
1125 get_rule_quote_parties (
1126 p_qtev_rec => p_qtev_rec,
1127 x_qpyv_tbl => l_qpyv_tbl,
1128 x_return_status => l_return_status);
1129
1130 IF (is_debug_statement_on) THEN
1131 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
1132 'after call to get_rule_quote_parties :'||l_return_status);
1133 END IF;
1134
1135 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
1136 IF l_overall_status <> OKL_API.G_RET_STS_UNEXP_ERROR THEN
1137 l_overall_status := l_return_status;
1138 END IF;
1139 END IF;
1140
1141 ELSE
1142 -- User-defined quote parties
1143 l_qpyv_tbl := p_qpyv_tbl;
1144 END IF;
1145
1146 END IF;
1147
1148 -- ******************************************************************
1149 -- Validate records, populate missing fields and remove empty records
1150 -- ******************************************************************
1151
1152 IF l_overall_status = OKL_API.G_RET_STS_SUCCESS THEN
1153
1154 FOR i IN l_qpyv_tbl.FIRST..l_qpyv_tbl.LAST LOOP
1155
1156 -- Every quote party has to point to contract party
1157 IF l_qpyv_tbl(i).cpl_id IS NOT NULL
1158 AND l_qpyv_tbl(i).cpl_id <> G_MISS_NUM THEN
1159
1160 -- Must have at least one recipient
1161 IF l_qpyv_tbl(i).qpt_code = 'RECIPIENT' THEN
1162 l_recipient_found := TRUE;
1163 END IF;
1164
1165 -- User-defined parties must have email addresses
1166 IF p_qpyv_tbl.COUNT > 0
1167 AND ( l_qpyv_tbl(i).email_address IS NULL
1168 OR l_qpyv_tbl(i).email_address = G_MISS_CHAR) THEN
1169 l_email_missing := TRUE;
1170 END IF;
1171
1172 -- Delay Days is required for Advance Notice Party
1173 IF l_qpyv_tbl(i).qpt_code = 'ADVANCE_NOTICE' THEN
1174
1175 IF l_qpyv_tbl(i).delay_days IS NULL
1176 OR l_qpyv_tbl(i).delay_days = G_MISS_NUM
1177 OR l_qpyv_tbl(i).delay_days < 0 THEN
1178
1179 l_overall_status := OKL_API.G_RET_STS_ERROR;
1180 OKC_API.SET_MESSAGE (
1181 p_app_name => G_OKC_APP_NAME,
1182 p_msg_name => 'OKC_DATA_REQUIRED',
1183 p_token1 => 'DATA_NAME',
1184 p_token1_value => 'Delay Days',
1185 p_token2 => 'OPERATION',
1186 p_token2_value => 'Advance Notice');
1187
1188 END IF;
1189
1190 ELSE
1191 l_qpyv_tbl(i).delay_days := NULL;
1192 END IF;
1193
1194 -- Allocation Percentage is required for Additional Recipient Party
1195 IF l_qpyv_tbl(i).qpt_code = 'RECIPIENT_ADDITIONAL' THEN
1196
1197 IF l_qpyv_tbl(i).allocation_percentage IS NULL
1198 OR l_qpyv_tbl(i).allocation_percentage = G_MISS_NUM THEN
1199
1200 l_overall_status := OKL_API.G_RET_STS_ERROR;
1201 OKC_API.SET_MESSAGE (
1202 p_app_name => G_OKC_APP_NAME,
1203 p_msg_name => 'OKC_DATA_REQUIRED',
1204 p_token1 => 'DATA_NAME',
1205 p_token1_value => 'Allocation Percentage',
1206 p_token2 => 'OPERATION',
1207 p_token2_value => 'Additional Recipient');
1208
1209 ELSIF l_qpyv_tbl(i).allocation_percentage NOT BETWEEN 0 AND 100 THEN
1210
1211 l_overall_status := OKL_API.G_RET_STS_ERROR;
1212 OKC_API.SET_MESSAGE (
1213 p_app_name => G_OKC_APP_NAME,
1214 p_msg_name => 'OKL_PERCENATGE_FORMAT',
1215 p_token1 => 'COL_NAME',
1216 p_token1_value => 'Additional Recipient Percentage');
1217
1218 END IF;
1219
1220 END IF;
1221
1222 -- Allocation Percentage is bounded for Recipient Party
1223 IF l_qpyv_tbl(i).qpt_code = 'RECIPIENT' THEN
1224
1225 IF l_qpyv_tbl(i).allocation_percentage IS NOT NULL
1226 AND l_qpyv_tbl(i).allocation_percentage <> G_MISS_NUM
1227 AND l_qpyv_tbl(i).allocation_percentage NOT BETWEEN 0 AND 100
1228 THEN
1229
1230 l_overall_status := OKL_API.G_RET_STS_ERROR;
1231 OKC_API.SET_MESSAGE (
1232 p_app_name => G_OKC_APP_NAME,
1233 p_msg_name => 'OKL_PERCENATGE_FORMAT',
1234 p_token1 => 'COL_NAME',
1235 p_token1_value => 'Recipient Percentage');
1236
1237 END IF;
1238
1239 END IF;
1240
1241 -- Calculate allocation_percentage
1242 IF l_qpyv_tbl(i).qpt_code IN ('RECIPIENT_ADDITIONAL','RECIPIENT') THEN
1243
1244 IF l_qpyv_tbl(i).allocation_percentage IS NOT NULL
1245 AND l_qpyv_tbl(i).allocation_percentage <> G_MISS_NUM THEN
1246
1247 IF l_qpyv_tbl(i).allocation_percentage BETWEEN 0 AND 100 THEN
1248 l_allc_total := l_allc_total + l_qpyv_tbl(i).allocation_percentage;
1249 ELSE
1250 l_alloc_status := OKL_API.G_RET_STS_ERROR;
1251 END IF;
1252
1253 ELSE
1254 l_no_allc := l_no_allc + 1;
1255 END IF;
1256
1257 ELSE
1258 l_qpyv_tbl(i).allocation_percentage := NULL;
1259 END IF;
1260
1261 -- Approver and Advance Copy are mutually exclusive
1262 IF l_qpyv_tbl(i).qpt_code IN ('APPROVER','ADVANCE_NOTICE') THEN
1263
1264 IF l_approver_found THEN
1265
1266 l_overall_status := OKL_API.G_RET_STS_ERROR;
1267 OKC_API.SET_MESSAGE (
1268 p_app_name => G_OKC_APP_NAME,
1269 p_msg_name => 'OKC_POPULATE_ONLY_ONE',
1270 p_token1 => 'COL_NAME1',
1271 p_token1_value => 'Approver',
1272 p_token2 => 'COL_NAME2',
1273 p_token2_value => 'Advance Notice');
1274
1275 ELSE
1276 l_approver_found := TRUE;
1277 END IF;
1278
1279 END IF;
1280
1281 -- Get Contract Party Role
1282 l_k_role_rec := e_k_role_rec;
1283 OPEN l_kpr_csr (l_qpyv_tbl(i).cpl_id, p_qtev_rec.khr_id);
1284 FETCH l_kpr_csr INTO l_k_role_rec;
1285 CLOSE l_kpr_csr;
1286
1287 -- Invalid contract party role
1288 IF l_k_role_rec.rle_code IS NULL THEN
1289
1290 l_overall_status := OKL_API.G_RET_STS_ERROR;
1291 OKC_API.SET_MESSAGE (
1292 p_app_name => G_OKC_APP_NAME,
1293 p_msg_name => G_INVALID_VALUE,
1294 p_token1 => G_COL_NAME_TOKEN,
1295 p_token1_value => 'Contract Party');
1296
1297 ELSIF l_qpyv_tbl(i).qpt_code IN ('RECIPIENT_ADDITIONAL','RECIPIENT') THEN
1298
1299 -- Only Lessee and Vendor have setup for billing rules
1300 IF l_k_role_rec.rle_code NOT IN ('OKL_VENDOR','LESSEE') THEN
1301
1302 l_overall_status := OKL_API.G_RET_STS_ERROR;
1303 l_party_name := okl_am_util_pvt.get_jtf_object_name (
1304 l_k_role_rec.object1_code,
1305 l_k_role_rec.object1_id1,
1306 l_k_role_rec.object1_id2);
1307 OKC_API.SET_MESSAGE (
1308 p_app_name => G_APP_NAME,
1309 p_msg_name => 'OKL_AM_NO_BILLING_INFO',
1310 p_token1 => 'PARTY',
1311 p_token1_value => l_party_name);
1312
1313 -- Check billing information exist for vendor
1314 ELSIF l_k_role_rec.rle_code = 'OKL_VENDOR' THEN
1315
1316 l_taiv_rec := e_taiv_rec;
1317 l_taiv_rec.khr_id := p_qtev_rec.khr_id;
1318
1319 Okl_Am_Invoices_Pvt.Get_Vendor_Billing_Info (
1320 p_cpl_id => l_qpyv_tbl(i).cpl_id,
1321 px_taiv_rec => l_taiv_rec,
1322 x_return_status => l_return_status);
1323
1324 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
1325 IF l_overall_status <> OKL_API.G_RET_STS_UNEXP_ERROR THEN
1326 l_overall_status := l_return_status;
1327 END IF;
1328 END IF;
1329
1330 END IF;
1331
1332 END IF;
1333
1334 --Bug #3921591: pagarg +++ Rollover +++++++ Start ++++++++++
1335 ----------------------
1336 -- Validate Recipient in case of rollover quote
1337 ----------------------
1338 -- In case of rollover quote recipient can only be LESSEE
1339 IF p_qtev_rec.qtp_code LIKE 'TER_ROLL%'
1340 AND l_qpyv_tbl(i).qpt_code = 'RECIPIENT'
1341 AND l_k_role_rec.rle_code <> 'LESSEE'
1342 THEN
1343 --19 Nov 2004 PAGARG Bug# 4021165
1344 --Set the overall status in case of error
1345 --Don't raise exception
1346 --Use correct application name while setting message
1347 l_overall_status := OKL_API.G_RET_STS_ERROR;
1348 OKL_API.set_message( p_app_name => OKL_API.G_APP_NAME,
1349 p_msg_name => 'OKL_QTE_RCPT_LESSEE_ONLY');
1350 END IF;
1351 --Bug #3921591: pagarg +++ Rollover +++++++ End ++++++++++
1352
1353 -- rmunjulu 4131592 For Rollover Quote Additional Receipient can only be lessee
1354 IF p_qtev_rec.qtp_code LIKE 'TER_ROLL%'
1355 AND l_qpyv_tbl(i).qpt_code = 'RECIPIENT_ADDITIONAL'
1356 AND l_k_role_rec.rle_code <> 'LESSEE'
1357 THEN
1358 --19 Nov 2004 PAGARG Bug# 4021165
1359 --Set the overall status in case of error
1360 --Don't raise exception
1361 --Use correct application name while setting message
1362 l_overall_status := OKL_API.G_RET_STS_ERROR;
1363 OKL_API.set_message( p_app_name => OKL_API.G_APP_NAME,
1364 p_msg_name => 'OKL_QTE_RCPT_LESSEE_ONLY');
1365 END IF;
1366
1367 -- Save party details
1368 IF l_qpyv_tbl(i).party_object1_id1 IS NULL
1369 OR l_qpyv_tbl(i).party_object1_id1 = G_MISS_CHAR THEN
1370 l_qpyv_tbl(i).party_jtot_object1_code := l_k_role_rec.object1_code;
1371 l_qpyv_tbl(i).party_object1_id1 := l_k_role_rec.object1_id1;
1372 l_qpyv_tbl(i).party_object1_id2 := l_k_role_rec.object1_id2;
1373 END IF;
1374
1375 -- Attach quote parties to a quote
1376 l_qpyv_tbl(i).qte_id := p_qtev_rec.id;
1377
1378 -- Save results
1379 l_seq := l_seq + 1;
1380 lp_qpyv_tbl(l_seq) := l_qpyv_tbl(i);
1381
1382 END IF;
1383
1384 END LOOP;
1385
1386 /* Bug 2554547 - Email is optional in the screen
1387 -- Users did not supply email
1388 IF l_email_missing THEN
1389 l_return_status := OKL_API.G_RET_STS_ERROR;
1390 OKC_API.SET_MESSAGE (
1391 p_app_name => G_OKC_APP_NAME,
1392 p_msg_name => G_REQUIRED_VALUE,
1393 p_token1 => G_COL_NAME_TOKEN,
1394 p_token1_value => 'Email Address');
1395 END IF;
1396 */
1397
1398 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
1399 IF l_overall_status <> OKL_API.G_RET_STS_UNEXP_ERROR THEN
1400 l_overall_status := l_return_status;
1401 END IF;
1402 END IF;
1403
1404 -- Recipient not found
1405 IF NOT l_recipient_found THEN
1406 l_return_status := OKL_API.G_RET_STS_ERROR;
1407 -- Only display message if user-defined parties
1408 -- Rule-defined parties have its own message
1409 IF p_qpyv_tbl.COUNT > 0 THEN
1410 okl_am_util_pvt.set_message (
1411 p_app_name => G_APP_NAME
1412 ,p_msg_name => 'OKL_AM_QTE_RECIPIENT_NOT_FOUND'
1413 ,p_token1 => 'QUOTE_PARTY_TYPE'
1414 ,p_token1_value => 'RECIPIENT');
1415 END IF;
1416 END IF;
1417
1418 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
1419 IF l_overall_status <> OKL_API.G_RET_STS_UNEXP_ERROR THEN
1420 l_overall_status := l_return_status;
1421 END IF;
1422 END IF;
1423
1424 IF l_allc_total > 100
1425 OR (l_allc_total = 100 AND l_no_allc > 0)
1426 OR (l_allc_total < 100 AND l_no_allc = 0) THEN
1427 l_alloc_status := OKL_API.G_RET_STS_ERROR;
1428 ELSIF (l_allc_total < 100 AND l_no_allc > 0) THEN
1429 -- Divide the rest equally
1430 FOR i IN lp_qpyv_tbl.FIRST..lp_qpyv_tbl.LAST LOOP
1431 IF lp_qpyv_tbl(i).qpt_code IN ('RECIPIENT_ADDITIONAL','RECIPIENT')
1432 AND ( lp_qpyv_tbl(i).allocation_percentage IS NULL
1433 OR lp_qpyv_tbl(i).allocation_percentage = G_MISS_NUM)
1434 THEN
1435 lp_qpyv_tbl(i).allocation_percentage := (100 - l_allc_total) / l_no_allc;
1436 END IF;
1437 END LOOP;
1438 END IF;
1439
1440 IF l_alloc_status <> OKL_API.G_RET_STS_SUCCESS THEN
1441 l_return_status := OKL_API.G_RET_STS_ERROR;
1442 -- Message Text: Invalid value for the column Allocation Percentage
1443 OKL_API.SET_MESSAGE (
1444 p_app_name => G_OKC_APP_NAME,
1445 p_msg_name => G_INVALID_VALUE,
1446 p_token1 => G_COL_NAME_TOKEN,
1447 p_token1_value => 'Allocation Percentage');
1448 END IF;
1449
1450 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
1451 IF l_overall_status <> OKL_API.G_RET_STS_UNEXP_ERROR THEN
1452 l_overall_status := l_return_status;
1453 END IF;
1454 END IF;
1455
1456 END IF;
1457
1458 -- ******************
1459 -- Save quote parties
1460 -- ******************
1461
1462 IF NOT p_validate_only THEN
1463
1464 IF l_overall_status = OKL_API.G_RET_STS_SUCCESS THEN
1465
1466 IF lp_qpyv_tbl.COUNT > 0 THEN
1467
1468 okl_quote_parties_pub.insert_quote_parties (
1469 p_api_version => l_api_version,
1470 p_init_msg_list => OKL_API.G_FALSE,
1471 x_return_status => l_return_status,
1472 x_msg_count => l_msg_count,
1473 x_msg_data => l_msg_data,
1474 p_qpyv_tbl => lp_qpyv_tbl,
1475 x_qpyv_tbl => lx_qpyv_tbl);
1476
1477 IF (is_debug_statement_on) THEN
1478 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
1479 'after call to okl_quote_parties_pub.insert_quote_parties :'||l_return_status);
1480 END IF;
1481
1482 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
1483 IF l_overall_status <> OKL_API.G_RET_STS_UNEXP_ERROR THEN
1484 l_overall_status := l_return_status;
1485 END IF;
1486 END IF;
1487
1488 END IF;
1489 END IF;
1490 END IF;
1491
1492 x_qpyv_tbl := lx_qpyv_tbl;
1493 x_return_status := l_overall_status;
1494
1495 IF (is_debug_procedure_on) THEN
1496 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'End(-)');
1497 END IF;
1498
1499 EXCEPTION
1500
1501 WHEN OTHERS THEN
1502
1503 IF (is_debug_exception_on) THEN
1504 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,L_MODULE_NAME, 'EXCEPTION :'||'OTHERS, SQLCODE: '
1505 || sqlcode || ' , SQLERRM : ' || sqlerrm);
1506 END IF;
1507
1508 -- Close open cursors
1509 IF l_kpr_csr%ISOPEN THEN
1510 CLOSE l_kpr_csr;
1511 END IF;
1512
1513 -- store SQL error message on message stack for caller
1514 OKL_API.SET_MESSAGE (
1515 p_app_name => G_APP_NAME
1516 ,p_msg_name => G_UNEXPECTED_ERROR
1517 ,p_token1 => G_SQLCODE_TOKEN
1518 ,p_token1_value => sqlcode
1519 ,p_token2 => G_SQLERRM_TOKEN
1520 ,p_token2_value => sqlerrm);
1521
1522 -- notify caller of an UNEXPECTED error
1523 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1524
1525 END create_quote_parties;
1526
1527
1528 -- Start of comments
1529 --
1530 -- Procedure Name : get_quote_parties
1531 -- Description : Return quote party information
1532 -- Note : View OKL_AM_QUOTE_PARTIES_UV
1533 -- returns the same results
1534 -- Business Rules :
1535 -- Parameters : quote_id
1536 -- Version : 1.0
1537 -- History : PAGARG 4299668 Modify quote party role cursor to return
1538 -- record for a given party if party id is passed.
1539 -- End of comments
1540
1541 PROCEDURE get_quote_parties (
1542 p_api_version IN NUMBER,
1543 p_init_msg_list IN VARCHAR2,
1544 x_msg_count OUT NOCOPY NUMBER,
1545 x_msg_data OUT NOCOPY VARCHAR2,
1546 x_return_status OUT NOCOPY VARCHAR2,
1547 p_q_party_uv_rec IN q_party_uv_rec_type,
1548 x_q_party_uv_tbl OUT NOCOPY q_party_uv_tbl_type,
1549 x_record_count OUT NOCOPY NUMBER) IS
1550
1551 l_q_party_tbl q_party_uv_tbl_type;
1552 l_object_tbl okl_am_util_pvt.jtf_object_tbl_type;
1553 l_cnt NUMBER := 0;
1554 l_quote_id NUMBER;
1555
1556 --PAGARG 4299668 Modify the curosr to restrict the records for a given party if
1557 --party id is passed
1558 CURSOR l_q_party_role_csr (cp_qte_id NUMBER, cp_qpt_code VARCHAR2, cp_party_id NUMBER ) IS
1559 SELECT qp.id qp_party_id,
1560 qp.qpt_code qp_role_code,
1561 qp.CREATED_BY qp_created_by,
1562 qp.CREATION_DATE qp_creation_date,
1563 qp.LAST_UPDATED_BY qp_last_updated_by,
1564 qp.LAST_UPDATE_DATE qp_last_update_date,
1565 qp.LAST_UPDATE_LOGIN qp_last_update_login,
1566 qp.PARTY_JTOT_OBJECT1_CODE qp_party_object,
1567 qp.PARTY_OBJECT1_ID1 qp_party_id1,
1568 qp.PARTY_OBJECT1_ID2 qp_party_id2,
1569 qp.CONTACT_JTOT_OBJECT1_CODE qp_contact_object,
1570 qp.CONTACT_OBJECT1_ID1 qp_contact_id1,
1571 qp.CONTACT_OBJECT1_ID2 qp_contact_id2,
1572 qp.EMAIL_ADDRESS qp_email_address,
1573 lq.meaning qp_party_role,
1574 qp.date_sent qp_date_sent,
1575 pr.id kp_party_id,
1576 pr.rle_code kp_role_code,
1577 lk.meaning kp_party_role,
1578 pr.jtot_object1_code po_party_object,
1579 pr.object1_id1 po_party_id1,
1580 pr.object1_id2 po_party_id2,
1581 kh.id contract_id,
1582 kh.buy_or_sell k_buy_or_sell
1583 FROM okl_quote_parties qp,
1584 okc_k_party_roles_b pr,
1585 okl_trx_quotes_b tq,
1586 okc_k_headers_all_b kh,
1587 fnd_lookups lq,
1588 fnd_lookups lk
1589 WHERE qp.qte_id = cp_qte_id
1590 AND qp.qpt_code = NVL (cp_qpt_code, qp.qpt_code)
1591 AND pr.id (+) = qp.cpl_id
1592 AND tq.id = qp.qte_id
1593 AND tq.id = cp_qte_id
1594 AND kh.id = tq.khr_id
1595 AND lq.lookup_code = qp.qpt_code
1596 AND lq.lookup_type = 'OKL_QUOTE_PARTY_TYPE'
1597 AND lk.lookup_code (+) = pr.rle_code
1598 AND lk.lookup_type (+) = 'OKC_ROLE'
1599 AND qp.id = nvl(cp_party_id, qp.id);
1600
1601 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1602 l_api_name CONSTANT VARCHAR2(30) := 'get_quote_parties';
1603 l_api_version CONSTANT NUMBER := G_API_VERSION;
1604 l_msg_count NUMBER := G_MISS_NUM;
1605 l_msg_data VARCHAR2(2000);
1606 l_qpt_code VARCHAR2(30);
1607 --pagarg 4299668 variable to hold party id value
1608 l_party_id NUMBER;
1609
1610 -- for debug logging
1611 L_MODULE_NAME VARCHAR2(500) := G_MODULE_NAME||'get_quote_parties';
1612 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
1613 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
1614 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
1615
1616 BEGIN
1617
1618 IF (is_debug_procedure_on) THEN
1619 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'Begin(+)');
1620 END IF;
1621 IF (is_debug_statement_on) THEN
1622 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.quote_id : '|| p_q_party_uv_rec.quote_id );
1623 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.contract_id : '||p_q_party_uv_rec.contract_id );
1624 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.k_buy_or_sell : '||p_q_party_uv_rec.k_buy_or_sell );
1625 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.qp_party_id : '||p_q_party_uv_rec.qp_party_id );
1626 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.qp_role_code : '||p_q_party_uv_rec.qp_role_code );
1627 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.qp_party_role : '||p_q_party_uv_rec.qp_party_role );
1628 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.qp_date_sent : '||p_q_party_uv_rec.qp_date_sent );
1629 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.qp_date_hold : '||p_q_party_uv_rec.qp_date_hold );
1630 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.qp_created_by : '||p_q_party_uv_rec.qp_created_by );
1631 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.qp_creation_date : '||p_q_party_uv_rec.qp_creation_date );
1632 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.qp_last_updated_by : '||p_q_party_uv_rec.qp_last_updated_by );
1633 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.qp_last_update_date : '||p_q_party_uv_rec.qp_last_update_date );
1634 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.qp_last_update_login : '||p_q_party_uv_rec.qp_last_update_login );
1635 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.kp_party_id : '||p_q_party_uv_rec.kp_party_id );
1636 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.kp_role_code : '|| p_q_party_uv_rec.kp_role_code );
1637 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.kp_party_role : '|| p_q_party_uv_rec.kp_party_role );
1638 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.po_party_id1 : '|| p_q_party_uv_rec.po_party_id1 );
1639 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.po_party_id2 : '|| p_q_party_uv_rec.po_party_id2 );
1640 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.po_party_object : '|| p_q_party_uv_rec.po_party_object );
1641 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.po_party_name : '|| p_q_party_uv_rec.po_party_name );
1642 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.po_party_desc : '|| p_q_party_uv_rec.po_party_desc );
1643 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.co_contact_id1 : '|| p_q_party_uv_rec.co_contact_id1 );
1644 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.co_contact_id2 : '|| p_q_party_uv_rec.co_contact_id2 );
1645 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.co_contact_object : '|| p_q_party_uv_rec.co_contact_object );
1646 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.co_contact_name : '|| p_q_party_uv_rec.co_contact_name );
1647 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.co_contact_desc : '|| p_q_party_uv_rec.co_contact_desc );
1648 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.co_email : '|| p_q_party_uv_rec.co_email );
1649 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.co_order_num : '|| p_q_party_uv_rec.co_order_num );
1650 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.co_date_sent : '|| p_q_party_uv_rec.co_date_sent );
1651 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.cp_point_id : '|| p_q_party_uv_rec.cp_point_id );
1652 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.cp_point_type : '|| p_q_party_uv_rec.cp_point_type );
1653 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.cp_primary_flag : '|| p_q_party_uv_rec.cp_primary_flag );
1654 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.cp_email : '|| p_q_party_uv_rec.cp_email );
1655 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.cp_details : '|| p_q_party_uv_rec.cp_details );
1656 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.cp_order_num : '|| p_q_party_uv_rec.cp_order_num );
1657 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.cp_date_sent : '|| p_q_party_uv_rec.cp_date_sent );
1658 END IF;
1659 -- ***************************************************************
1660 -- Check API version, initialize message list and create savepoint
1661 -- ***************************************************************
1662
1663 l_return_status := OKL_API.START_ACTIVITY (
1664 l_api_name,
1665 G_PKG_NAME,
1666 p_init_msg_list,
1667 l_api_version,
1668 p_api_version,
1669 '_PVT',
1670 x_return_status);
1671
1672 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
1673 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1674 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
1675 RAISE OKL_API.G_EXCEPTION_ERROR;
1676 END IF;
1677
1678 -- *******************
1679 -- Validate parameters
1680 -- *******************
1681
1682 IF p_q_party_uv_rec.quote_id IS NULL
1683 OR p_q_party_uv_rec.quote_id = G_MISS_NUM THEN
1684
1685 l_return_status := OKL_API.G_RET_STS_ERROR;
1686
1687 OKC_API.SET_MESSAGE (
1688 p_app_name => OKC_API.G_APP_NAME,
1689 p_msg_name => 'OKC_NO_PARAMS',
1690 p_token1 => 'PARAM',
1691 p_token1_value => 'QUOTE_ID',
1692 p_token2 => 'PROCESS',
1693 p_token2_value => 'GET_QUOTE_PARTIES');
1694
1695 ELSE
1696 l_quote_id := p_q_party_uv_rec.quote_id;
1697 l_qpt_code := p_q_party_uv_rec.qp_role_code;
1698 IF l_qpt_code = G_MISS_CHAR THEN
1699 l_qpt_code := NULL;
1700 END IF;
1701 --pagarg 4299668 Obtain value of party id and replace G_MISS_NUM with null
1702 l_party_id := p_q_party_uv_rec.qp_party_id;
1703 IF l_party_id = G_MISS_NUM THEN
1704 l_party_id := NULL;
1705 END IF;
1706 END IF;
1707
1708 -- ********************
1709 -- Process every record
1710 -- ********************
1711
1712 --pagarg 4299668 pass party id also to the quote party role cursor
1713 FOR l_q_role_rec IN l_q_party_role_csr (l_quote_id, l_qpt_code, l_party_id) LOOP
1714 IF l_q_role_rec.kp_party_id IS NOT NULL THEN
1715 -- Get Contract Party Object
1716 okl_am_util_pvt.get_object_details (
1717 p_object_code => l_q_role_rec.po_party_object,
1718 p_object_id1 => l_q_role_rec.po_party_id1,
1719 p_object_id2 => l_q_role_rec.po_party_id2,
1720 x_object_tbl => l_object_tbl,
1721 x_return_status => l_return_status);
1722
1723 IF (is_debug_statement_on) THEN
1724 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
1725 'after call to okl_am_util_pvt.get_object_details :'||l_return_status);
1726 END IF;
1727
1728 ELSE
1729 -- Get Quote Party Object
1730 okl_am_util_pvt.get_object_details (
1731 p_object_code => l_q_role_rec.qp_party_object,
1732 p_object_id1 => l_q_role_rec.qp_party_id1,
1733 p_object_id2 => l_q_role_rec.qp_party_id2,
1734 x_object_tbl => l_object_tbl,
1735 x_return_status => l_return_status);
1736
1737 IF (is_debug_statement_on) THEN
1738 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
1739 'after call to okl_am_util_pvt.get_object_details :'||l_return_status);
1740 END IF;
1741
1742 END IF;
1743
1744 IF l_return_status = OKL_API.G_RET_STS_SUCCESS
1745 AND l_object_tbl.COUNT > 0 THEN
1746 FOR l_ind IN l_object_tbl.FIRST..l_object_tbl.LAST LOOP
1747 -- Party Object is found
1748 l_cnt := l_cnt + 1;
1749 l_q_party_tbl(l_cnt).quote_id := l_quote_id;
1750 l_q_party_tbl(l_cnt).contract_id := l_q_role_rec.contract_id;
1751 l_q_party_tbl(l_cnt).k_buy_or_sell := l_q_role_rec.k_buy_or_sell;
1752 l_q_party_tbl(l_cnt).qp_party_id := l_q_role_rec.qp_party_id;
1753 l_q_party_tbl(l_cnt).qp_role_code := l_q_role_rec.qp_role_code;
1754 l_q_party_tbl(l_cnt).qp_party_role := l_q_role_rec.qp_party_role;
1755 l_q_party_tbl(l_cnt).qp_date_hold := NULL;
1756 l_q_party_tbl(l_cnt).qp_date_sent := l_q_role_rec.qp_date_sent;
1757 l_q_party_tbl(l_cnt).qp_created_by := l_q_role_rec.qp_created_by;
1758 l_q_party_tbl(l_cnt).qp_creation_date := l_q_role_rec.qp_creation_date;
1759 l_q_party_tbl(l_cnt).qp_last_updated_by := l_q_role_rec.qp_last_updated_by;
1760 l_q_party_tbl(l_cnt).qp_last_update_date := l_q_role_rec.qp_last_update_date;
1761 l_q_party_tbl(l_cnt).qp_last_update_login := l_q_role_rec.qp_last_update_login;
1762 l_q_party_tbl(l_cnt).kp_party_id := l_q_role_rec.kp_party_id;
1763 l_q_party_tbl(l_cnt).kp_role_code := l_q_role_rec.kp_role_code;
1764 l_q_party_tbl(l_cnt).kp_party_role := l_q_role_rec.kp_party_role;
1765 l_q_party_tbl(l_cnt).po_party_id1 := l_object_tbl(l_ind).id1;
1766 l_q_party_tbl(l_cnt).po_party_id2 := l_object_tbl(l_ind).id2;
1767 l_q_party_tbl(l_cnt).po_party_object := l_object_tbl(l_ind).object_code;
1768 l_q_party_tbl(l_cnt).po_party_name := l_object_tbl(l_ind).name;
1769 l_q_party_tbl(l_cnt).po_party_desc := l_object_tbl(l_ind).description;
1770 l_q_party_tbl(l_cnt).co_contact_id1 := l_q_role_rec.qp_contact_id1;
1771 l_q_party_tbl(l_cnt).co_contact_id2 := l_q_role_rec.qp_contact_id2;
1772 l_q_party_tbl(l_cnt).co_contact_object := l_q_role_rec.qp_contact_object;
1773 l_q_party_tbl(l_cnt).co_email := l_q_role_rec.qp_email_address;
1774 l_q_party_tbl(l_cnt).co_order_num := 1;
1775 END LOOP;
1776 ELSE
1777 -- Party Object is not found
1778 l_cnt := l_cnt + 1;
1779 l_q_party_tbl(l_cnt).quote_id := l_quote_id;
1780 l_q_party_tbl(l_cnt).contract_id := l_q_role_rec.contract_id;
1781 l_q_party_tbl(l_cnt).k_buy_or_sell := l_q_role_rec.k_buy_or_sell;
1782 l_q_party_tbl(l_cnt).qp_party_id := l_q_role_rec.qp_party_id;
1783 l_q_party_tbl(l_cnt).qp_role_code := l_q_role_rec.qp_role_code;
1784 l_q_party_tbl(l_cnt).qp_party_role := l_q_role_rec.qp_party_role;
1785 l_q_party_tbl(l_cnt).qp_date_hold := NULL;
1786 l_q_party_tbl(l_cnt).qp_date_sent := l_q_role_rec.qp_date_sent;
1787 l_q_party_tbl(l_cnt).qp_created_by := l_q_role_rec.qp_created_by;
1788 l_q_party_tbl(l_cnt).qp_creation_date := l_q_role_rec.qp_creation_date;
1789 l_q_party_tbl(l_cnt).qp_last_updated_by := l_q_role_rec.qp_last_updated_by;
1790 l_q_party_tbl(l_cnt).qp_last_update_date := l_q_role_rec.qp_last_update_date;
1791 l_q_party_tbl(l_cnt).qp_last_update_login := l_q_role_rec.qp_last_update_login;
1792 l_q_party_tbl(l_cnt).kp_party_id := l_q_role_rec.kp_party_id;
1793 l_q_party_tbl(l_cnt).kp_role_code := l_q_role_rec.kp_role_code;
1794 l_q_party_tbl(l_cnt).kp_party_role := l_q_role_rec.kp_party_role;
1795 l_q_party_tbl(l_cnt).po_party_name := NULL;
1796 l_q_party_tbl(l_cnt).po_party_desc := NULL;
1797 l_q_party_tbl(l_cnt).co_contact_id1 := l_q_role_rec.qp_contact_id1;
1798 l_q_party_tbl(l_cnt).co_contact_id2 := l_q_role_rec.qp_contact_id2;
1799 l_q_party_tbl(l_cnt).co_contact_object := l_q_role_rec.qp_contact_object;
1800 l_q_party_tbl(l_cnt).co_email := l_q_role_rec.qp_email_address;
1801 l_q_party_tbl(l_cnt).co_order_num := 1;
1802
1803 IF l_q_role_rec.kp_party_id IS NOT NULL THEN
1804 l_q_party_tbl(l_cnt).po_party_id1 := l_q_role_rec.po_party_id1;
1805 l_q_party_tbl(l_cnt).po_party_id2 := l_q_role_rec.po_party_id2;
1806 l_q_party_tbl(l_cnt).po_party_object := l_q_role_rec.po_party_object;
1807 ELSE
1808 l_q_party_tbl(l_cnt).po_party_id1 := l_q_role_rec.qp_party_id1;
1809 l_q_party_tbl(l_cnt).po_party_id2 := l_q_role_rec.qp_party_id2;
1810 l_q_party_tbl(l_cnt).po_party_object := l_q_role_rec.qp_party_object;
1811 END IF;
1812
1813 END IF;
1814
1815 END LOOP;
1816
1817 -- **************
1818 -- Return results
1819 -- **************
1820
1821 x_record_count := l_q_party_tbl.COUNT;
1822 x_q_party_uv_tbl := l_q_party_tbl;
1823 x_return_status := l_return_status;
1824
1825 OKL_API.END_ACTIVITY (x_msg_count, x_msg_data);
1826
1827 IF (is_debug_procedure_on) THEN
1828 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'End(-)');
1829 END IF;
1830
1831 EXCEPTION
1832
1833 WHEN OTHERS THEN
1834
1835 IF (is_debug_exception_on) THEN
1836 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,L_MODULE_NAME, 'EXCEPTION :'||'OTHERS, SQLCODE: '
1837 || sqlcode || ' , SQLERRM : ' || sqlerrm);
1838 END IF;
1839
1840 -- Close open cursors
1841 IF l_q_party_role_csr%ISOPEN THEN
1842 CLOSE l_q_party_role_csr;
1843 END IF;
1844
1845 -- store SQL error message on message stack for caller
1846 OKL_API.SET_MESSAGE (
1847 p_app_name => G_APP_NAME
1848 ,p_msg_name => G_UNEXPECTED_ERROR
1849 ,p_token1 => G_SQLCODE_TOKEN
1850 ,p_token1_value => sqlcode
1851 ,p_token2 => G_SQLERRM_TOKEN
1852 ,p_token2_value => sqlerrm);
1853
1854 -- notify caller of an UNEXPECTED error
1855 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1856
1857 END get_quote_parties;
1858
1859
1860 -- Start of comments
1861 --
1862 -- Procedure Name : get_quote_party_contacts
1863 -- Description : Return quote party contact information
1864 -- Business Rules :
1865 -- Parameters : quote_id, optional quote_party_id
1866 -- Version : 1.0
1867 -- : PAGARG 4299668 fix the logic to obtain party contacts when
1868 -- party id is available
1869 -- End of comments
1870
1871 PROCEDURE get_quote_party_contacts (
1872 p_api_version IN NUMBER,
1873 p_init_msg_list IN VARCHAR2,
1874 x_msg_count OUT NOCOPY NUMBER,
1875 x_msg_data OUT NOCOPY VARCHAR2,
1876 x_return_status OUT NOCOPY VARCHAR2,
1877 p_q_party_uv_rec IN q_party_uv_rec_type,
1878 x_q_party_uv_tbl OUT NOCOPY q_party_uv_tbl_type,
1879 x_record_count OUT NOCOPY NUMBER) IS
1880
1881 l_q_party_tbl q_party_uv_tbl_type;
1882 l_temp_q_party_tbl q_party_uv_tbl_type;
1883 l_curr_qp_rec q_party_uv_rec_type;
1884
1885 l_object_tbl okl_am_util_pvt.jtf_object_tbl_type;
1886 l_t_object_tbl okl_am_util_pvt.jtf_object_tbl_type;
1887 l_where_cond okl_am_util_pvt.where_tbl_type;
1888 l_other_cols okl_am_util_pvt.select_tbl_type;
1889
1890 l_cnt NUMBER := 0;
1891 l_obj NUMBER;
1892 l_quote_id NUMBER;
1893 l_quote_party_id NUMBER := NULL;
1894 l_contact_source_count NUMBER;
1895 l_party_count NUMBER;
1896 l_contact_object VARCHAR2(30);
1897
1898 CURSOR l_cntct_src_count_csr (cp_rle_code VARCHAR2, cp_buy_or_sell VARCHAR2) IS
1899 SELECT count(*)
1900 FROM okc_contact_sources cs
1901 WHERE cs.rle_code = cp_rle_code
1902 AND cs.start_date <= SYSDATE
1903 AND NVL (cs.end_date, SYSDATE) >= SYSDATE
1904 AND cs.buy_or_sell = cp_buy_or_sell;
1905
1906 CURSOR l_contacts_csr (
1907 cp_k_party_id NUMBER,
1908 cp_rle_code VARCHAR2,
1909 cp_buy_or_sell VARCHAR2,
1910 cp_contract_id NUMBER) IS
1911 SELECT cn.jtot_object1_code,
1912 cn.object1_id1,
1913 cn.object1_id2
1914 FROM okc_contacts cn,
1915 okc_contact_sources cs
1916 WHERE cn.cpl_id = cp_k_party_id
1917 AND cn.dnz_chr_id = cp_contract_id
1918 AND cn.cro_code = cs.cro_code
1919 AND cn.jtot_object1_code = cs.jtot_object_code
1920 AND cs.start_date <= SYSDATE
1921 AND NVL (cs.end_date, SYSDATE) >= SYSDATE
1922 AND cs.rle_code = cp_rle_code
1923 AND cs.buy_or_sell = cp_buy_or_sell;
1924
1925 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1926 l_api_name CONSTANT VARCHAR2(30) :=
1927 'get_quote_party_contacts';
1928 l_api_version CONSTANT NUMBER := G_API_VERSION;
1929 l_msg_count NUMBER := G_MISS_NUM;
1930 l_msg_data VARCHAR2(2000);
1931
1932 -- for debug logging
1933 L_MODULE_NAME VARCHAR2(500) := G_MODULE_NAME||'get_quote_party_contacts';
1934 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
1935 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
1936 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
1937
1938 BEGIN
1939
1940 IF (is_debug_procedure_on) THEN
1941 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'Begin(+)');
1942 END IF;
1943
1944 IF (is_debug_statement_on) THEN
1945 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.quote_id : '|| p_q_party_uv_rec.quote_id );
1946 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.contract_id : '||p_q_party_uv_rec.contract_id );
1947 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.k_buy_or_sell : '||p_q_party_uv_rec.k_buy_or_sell );
1948 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.qp_party_id : '||p_q_party_uv_rec.qp_party_id );
1949 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.qp_role_code : '||p_q_party_uv_rec.qp_role_code );
1950 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.qp_party_role : '||p_q_party_uv_rec.qp_party_role );
1951 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.qp_date_sent : '||p_q_party_uv_rec.qp_date_sent );
1952 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.qp_date_hold : '||p_q_party_uv_rec.qp_date_hold );
1953 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.qp_created_by : '||p_q_party_uv_rec.qp_created_by );
1954 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.qp_creation_date : '||p_q_party_uv_rec.qp_creation_date );
1955 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.qp_last_updated_by : '||p_q_party_uv_rec.qp_last_updated_by );
1956 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.qp_last_update_date : '||p_q_party_uv_rec.qp_last_update_date );
1957 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.qp_last_update_login : '||p_q_party_uv_rec.qp_last_update_login );
1958 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.kp_party_id : '||p_q_party_uv_rec.kp_party_id );
1959 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.kp_role_code : '|| p_q_party_uv_rec.kp_role_code );
1960 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.kp_party_role : '|| p_q_party_uv_rec.kp_party_role );
1961 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.po_party_id1 : '|| p_q_party_uv_rec.po_party_id1 );
1962 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.po_party_id2 : '|| p_q_party_uv_rec.po_party_id2 );
1963 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.po_party_object : '|| p_q_party_uv_rec.po_party_object );
1964 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.po_party_name : '|| p_q_party_uv_rec.po_party_name );
1965 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.po_party_desc : '|| p_q_party_uv_rec.po_party_desc );
1966 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.co_contact_id1 : '|| p_q_party_uv_rec.co_contact_id1 );
1967 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.co_contact_id2 : '|| p_q_party_uv_rec.co_contact_id2 );
1968 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.co_contact_object : '|| p_q_party_uv_rec.co_contact_object );
1969 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.co_contact_name : '|| p_q_party_uv_rec.co_contact_name );
1970 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.co_contact_desc : '|| p_q_party_uv_rec.co_contact_desc );
1971 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.co_email : '|| p_q_party_uv_rec.co_email );
1972 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.co_order_num : '|| p_q_party_uv_rec.co_order_num );
1973 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.co_date_sent : '|| p_q_party_uv_rec.co_date_sent );
1974 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.cp_point_id : '|| p_q_party_uv_rec.cp_point_id );
1975 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.cp_point_type : '|| p_q_party_uv_rec.cp_point_type );
1976 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.cp_primary_flag : '|| p_q_party_uv_rec.cp_primary_flag );
1977 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.cp_email : '|| p_q_party_uv_rec.cp_email );
1978 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.cp_details : '|| p_q_party_uv_rec.cp_details );
1979 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.cp_order_num : '|| p_q_party_uv_rec.cp_order_num );
1980 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.cp_date_sent : '|| p_q_party_uv_rec.cp_date_sent );
1981 END IF;
1982 -- ***************************************************************
1983 -- Check API version, initialize message list and create savepoint
1984 -- ***************************************************************
1985
1986 l_return_status := OKL_API.START_ACTIVITY (
1987 l_api_name,
1988 G_PKG_NAME,
1989 p_init_msg_list,
1990 l_api_version,
1991 p_api_version,
1992 '_PVT',
1993 x_return_status);
1994
1995 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
1996 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1997 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
1998 RAISE OKL_API.G_EXCEPTION_ERROR;
1999 END IF;
2000
2001 -- *******************
2002 -- Validate parameters
2003 -- *******************
2004
2005 IF p_q_party_uv_rec.quote_id IS NULL
2006 OR p_q_party_uv_rec.quote_id = G_MISS_NUM THEN
2007
2008 l_return_status := OKL_API.G_RET_STS_ERROR;
2009
2010 OKC_API.SET_MESSAGE (
2011 p_app_name => OKC_API.G_APP_NAME,
2012 p_msg_name => 'OKC_NO_PARAMS',
2013 p_token1 => 'PARAM',
2014 p_token1_value => 'QUOTE_ID',
2015 p_token2 => 'PROCESS',
2016 p_token2_value => 'GET_QUOTE_PARTY_CONTACTS');
2017
2018 ELSE
2019 l_quote_id := p_q_party_uv_rec.quote_id;
2020 l_quote_party_id := p_q_party_uv_rec.qp_party_id;
2021 IF l_quote_party_id = G_MISS_NUM THEN
2022 l_quote_party_id := NULL;
2023 END IF;
2024 END IF;
2025
2026 -- ********************
2027 -- Get quote party data
2028 -- ********************
2029 --PAGARG 4299668 call the procedure even when if party is not null so as to
2030 --populate other fields of the party record
2031 get_quote_parties (
2032 p_api_version => p_api_version,
2033 p_init_msg_list => p_init_msg_list,
2034 x_msg_count => l_msg_count,
2035 x_msg_data => l_msg_data,
2036 x_return_status => l_return_status,
2037 p_q_party_uv_rec => p_q_party_uv_rec,
2038 x_q_party_uv_tbl => l_temp_q_party_tbl,
2039 x_record_count => l_party_count);
2040
2041
2042 IF (is_debug_statement_on) THEN
2043 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2044 'after call to get_quote_parties :'||l_return_status);
2045 END IF;
2046
2047 -- ********************
2048 -- Process every record
2049 -- ********************
2050
2051 l_where_cond(1).column_name := 'PARTY_ID';
2052 l_where_cond(2).column_name := 'PARTY_ID2';
2053 l_other_cols(1) := 'EMAIL_ADDRESS';
2054
2055 --PAGARG 4299668 check the table count before looping the table
2056 IF l_temp_q_party_tbl.count > 0
2057 THEN
2058 FOR l_ind IN l_temp_q_party_tbl.FIRST..l_temp_q_party_tbl.LAST LOOP
2059 --PAGARG 4299668 Empty the object table for each quote party role
2060 l_object_tbl.delete;
2061 -- ****************************
2062 -- Detemine Contact Object Code
2063 -- ****************************
2064 l_curr_qp_rec := l_temp_q_party_tbl(l_ind);
2065 l_contact_source_count := 0;
2066 l_contact_object := NULL;
2067
2068 IF l_curr_qp_rec.co_contact_object IS NULL THEN
2069
2070 OPEN l_cntct_src_count_csr
2071 (l_curr_qp_rec.kp_role_code,
2072 l_curr_qp_rec.k_buy_or_sell);
2073 FETCH l_cntct_src_count_csr INTO l_contact_source_count;
2074 CLOSE l_cntct_src_count_csr;
2075
2076 IF l_contact_source_count = 0 THEN
2077 IF l_curr_qp_rec.po_party_object = 'OKX_PARTY' THEN
2078 l_contact_object := 'OKX_PCONTACT';
2079 ELSIF l_curr_qp_rec.po_party_object = 'OKX_VENDOR' THEN
2080 l_contact_object := 'OKX_VCONTACT';
2081 END IF;
2082 END IF;
2083
2084 END IF;
2085
2086 -- **************************
2087 -- Get Contact Name and Email
2088 -- **************************
2089
2090 IF l_curr_qp_rec.co_contact_object IS NULL
2091 AND l_contact_source_count = 0
2092 AND l_contact_object IS NULL THEN
2093 -- Contact Object Code is not found
2094 l_cnt := l_cnt + 1;
2095 l_q_party_tbl(l_cnt) := l_curr_qp_rec;
2096
2097 ELSE
2098
2099 IF l_curr_qp_rec.co_contact_object IS NOT NULL THEN
2100
2101 -- *******************************************
2102 -- Get Contact Object from TCA (Quote Contact)
2103 -- *******************************************
2104
2105 -- Get all contacts for a quote
2106 okl_am_util_pvt.get_object_details (
2107 p_object_code => l_curr_qp_rec.co_contact_object,
2108 p_object_id1 => l_curr_qp_rec.co_contact_id1,
2109 p_object_id2 => l_curr_qp_rec.co_contact_id2,
2110 p_other_select => l_other_cols,
2111 x_object_tbl => l_object_tbl,
2112 x_return_status => l_return_status);
2113
2114 IF (is_debug_statement_on) THEN
2115 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2116 'after call to okl_am_util_pvt.get_object_details :'||l_return_status);
2117 END IF;
2118
2119 ELSIF l_contact_source_count <> 0 THEN
2120
2121 -- ***************************
2122 -- Get Contact Object from OKC
2123 -- ***************************
2124
2125 l_obj := 0;
2126
2127 FOR l_cntct_rec IN l_contacts_csr
2128 (l_curr_qp_rec.kp_party_id,
2129 l_curr_qp_rec.kp_role_code,
2130 l_curr_qp_rec.k_buy_or_sell,
2131 l_curr_qp_rec.contract_id)
2132 LOOP
2133
2134 IF (is_debug_statement_on) THEN
2135 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2136 'before call to okl_am_util_pvt.get_object_details :'||l_return_status);
2137 END IF;
2138 -- Get Contact Object
2139 okl_am_util_pvt.get_object_details (
2140 p_object_code => l_cntct_rec.jtot_object1_code,
2141 p_other_select => l_other_cols,
2142 p_object_id1 => l_cntct_rec.object1_id1,
2143 p_object_id2 => l_cntct_rec.object1_id2,
2144 x_object_tbl => l_t_object_tbl,
2145 x_return_status => l_return_status);
2146
2147 IF (is_debug_statement_on) THEN
2148 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2149 'after call to okl_am_util_pvt.get_object_details :'||l_return_status);
2150 END IF;
2151
2152 FOR l_ind3 IN l_t_object_tbl.FIRST..l_t_object_tbl.LAST LOOP
2153 l_obj := l_obj + 1;
2154 l_object_tbl(l_obj) := l_t_object_tbl(l_ind3);
2155 END LOOP;
2156
2157 END LOOP;
2158
2159 ELSIF l_contact_object IS NOT NULL THEN
2160
2161 -- *******************************************
2162 -- Get Contact Object from TCA (Party Contact)
2163 -- *******************************************
2164
2165 l_where_cond(1).condition_value := l_curr_qp_rec.po_party_id1;
2166 l_where_cond(2).condition_value := l_curr_qp_rec.po_party_id2;
2167
2168 IF (is_debug_statement_on) THEN
2169 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2170 'before call to okl_am_util_pvt.get_object_details :'||l_return_status);
2171 END IF;
2172
2173 -- Get all contacts for a party
2174 okl_am_util_pvt.get_object_details (
2175 p_object_code => l_contact_object,
2176 p_other_select => l_other_cols,
2177 p_other_where => l_where_cond,
2178 p_check_status => 'Y',
2179 x_object_tbl => l_object_tbl,
2180 x_return_status => l_return_status);
2181
2182 IF (is_debug_statement_on) THEN
2183 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2184 'before call to okl_am_util_pvt.get_object_details :'||l_return_status);
2185 END IF;
2186
2187 END IF;
2188
2189 -- ************
2190 -- Save results
2191 -- ************
2192
2193 IF l_return_status = OKL_API.G_RET_STS_SUCCESS
2194 AND l_object_tbl.COUNT > 0 THEN
2195 FOR l_ind2 IN l_object_tbl.FIRST..l_object_tbl.LAST LOOP
2196 -- Contact Object is found
2197 l_cnt := l_cnt + 1;
2198 l_q_party_tbl(l_cnt) := l_curr_qp_rec;
2199 l_q_party_tbl(l_cnt).co_contact_id1 := l_object_tbl(l_ind2).id1;
2200 l_q_party_tbl(l_cnt).co_contact_id2 := l_object_tbl(l_ind2).id2;
2201 l_q_party_tbl(l_cnt).co_contact_object := l_object_tbl(l_ind2).object_code;
2202 l_q_party_tbl(l_cnt).co_contact_name := l_object_tbl(l_ind2).name;
2203 l_q_party_tbl(l_cnt).co_contact_desc := l_object_tbl(l_ind2).description;
2204 l_q_party_tbl(l_cnt).co_email := l_object_tbl(l_ind2).other_values;
2205 l_q_party_tbl(l_cnt).co_order_num := l_ind2;
2206 l_q_party_tbl(l_cnt).co_date_sent := NULL;
2207 END LOOP;
2208 --PAGARG 4299668 Removed else clause as there is no need to populate
2209 --back the record in result table if there is no contact
2210 END IF;
2211 END IF;
2212 END LOOP;
2213 END IF;
2214
2215 -- **************
2216 -- Return results
2217 -- **************
2218
2219 IF l_quote_party_id IS NOT NULL
2220 AND l_q_party_tbl.COUNT = 1
2221 AND ( l_q_party_tbl(1).co_contact_id1 IS NULL
2222 OR l_q_party_tbl(1).co_contact_id1 = G_MISS_CHAR) THEN
2223 -- No contact was found
2224 x_record_count := 0;
2225 ELSE
2226 x_record_count := l_q_party_tbl.COUNT;
2227 END IF;
2228
2229 x_q_party_uv_tbl := l_q_party_tbl;
2230 x_return_status := l_return_status;
2231
2232 OKL_API.END_ACTIVITY (x_msg_count, x_msg_data);
2233
2234 IF (is_debug_procedure_on) THEN
2235 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'End(-)');
2236 END IF;
2237
2238 EXCEPTION
2239
2240 WHEN OTHERS THEN
2241
2242 IF (is_debug_exception_on) THEN
2243 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,L_MODULE_NAME, 'EXCEPTION :'||'OTHERS, SQLCODE: '
2244 || sqlcode || ' , SQLERRM : ' || sqlerrm);
2245 END IF;
2246
2247 -- Close open cursors
2248
2249 IF l_cntct_src_count_csr%ISOPEN THEN
2250 CLOSE l_cntct_src_count_csr;
2251 END IF;
2252
2253 IF l_contacts_csr%ISOPEN THEN
2254 CLOSE l_contacts_csr;
2255 END IF;
2256
2257 -- store SQL error message on message stack for caller
2258 OKL_API.SET_MESSAGE (
2259 p_app_name => G_APP_NAME
2260 ,p_msg_name => G_UNEXPECTED_ERROR
2261 ,p_token1 => G_SQLCODE_TOKEN
2262 ,p_token1_value => sqlcode
2263 ,p_token2 => G_SQLERRM_TOKEN
2264 ,p_token2_value => sqlerrm);
2265
2266 -- notify caller of an UNEXPECTED error
2267 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
2268
2269 END get_quote_party_contacts;
2270
2271
2272 -- Start of comments
2273 --
2274 -- Procedure Name : get_quote_contact_points
2275 -- Description : Return quote party contact point information
2276 -- Business Rules :
2277 -- Parameters : quote_id, optional quote_party_id and contact_id
2278 -- Version : 1.0
2279 -- : PAGARG 4299668 fix the logic to obtain party contact points
2280 -- when party id is available
2281 -- End of comments
2282 PROCEDURE get_quote_contact_points (
2283 p_api_version IN NUMBER,
2284 p_init_msg_list IN VARCHAR2,
2285 x_msg_count OUT NOCOPY NUMBER,
2286 x_msg_data OUT NOCOPY VARCHAR2,
2287 x_return_status OUT NOCOPY VARCHAR2,
2288 p_q_party_uv_rec IN q_party_uv_rec_type,
2289 x_q_party_uv_tbl OUT NOCOPY q_party_uv_tbl_type,
2290 x_record_count OUT NOCOPY NUMBER) IS
2291
2292 l_co_point_tbl q_party_uv_tbl_type;
2293 l_temp_qp_tbl q_party_uv_tbl_type;
2294 l_temp_co_tbl q_party_uv_tbl_type;
2295 l_curr_qp_rec q_party_uv_rec_type;
2296 l_curr_co_rec q_party_uv_rec_type;
2297
2298 l_cnt1 NUMBER := 0;
2299 l_cnt2 NUMBER;
2300 l_quote_id NUMBER;
2301 l_quote_party_id NUMBER := NULL;
2302 l_contact_id VARCHAR2(40) := NULL;
2303 l_contact_count NUMBER;
2304 l_party_count NUMBER;
2305 l_table_id VARCHAR2(40);
2306 l_table_name VARCHAR2(30);
2307 l_added_yn2 BOOLEAN;
2308
2309 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2310 l_api_name CONSTANT VARCHAR2(30) :=
2311 'get_quote_contact_points';
2312 l_api_version CONSTANT NUMBER := G_API_VERSION;
2313 l_msg_count NUMBER := G_MISS_NUM;
2314 l_msg_data VARCHAR2(2000);
2315
2316 CURSOR l_c_points_csr
2317 (cp_owner_table_name VARCHAR2,
2318 cp_owner_table_id NUMBER) IS
2319 SELECT Initcap (
2320 Decode (c.contact_point_type,
2321 'PHONE', Decode (c.phone_line_type,
2322 'GEN', c.contact_point_type,
2323 c.phone_line_type),
2324 'EMAIL', c.contact_point_type))
2325 contact_point_type,
2326 Decode (c.contact_point_type,
2327 'PHONE', LTrim (RTrim (
2328 Decode (c.telephone_type, NULL, NULL,
2329 c.telephone_type || ' ') ||
2330 Decode (c.phone_country_code, NULL, NULL,
2331 c.phone_country_code || ' ') ||
2332 Decode (c.phone_area_code, NULL, NULL,
2333 c.phone_area_code || ' ') ||
2334 Decode (c.phone_number, NULL, NULL,
2335 c.phone_number || ' ') ||
2336 Decode (c.phone_extension, NULL, NULL,
2337 'Ext. ' || c.phone_extension))),
2338 'EMAIL', c.email_address)
2339 contact_point_details,
2340 c.contact_point_id contact_point_id,
2341 c.email_address email_address,
2342 c.primary_flag primary_flag
2343 FROM okx_contact_points_v c
2344 WHERE c.owner_table_name = cp_owner_table_name
2345 AND c.owner_table_id = cp_owner_table_id
2346 AND c.status = 'A'
2347 AND c.contact_point_type IN ('EMAIL','PHONE');
2348
2349 -- for debug logging
2350 L_MODULE_NAME VARCHAR2(500) := G_MODULE_NAME||'get_quote_contact_points';
2351 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
2352 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
2353 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
2354
2355 BEGIN
2356
2357 IF (is_debug_procedure_on) THEN
2358 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'Begin(+)');
2359 END IF;
2360 IF (is_debug_statement_on) THEN
2361 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.quote_id : '|| p_q_party_uv_rec.quote_id );
2362 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.contract_id : '||p_q_party_uv_rec.contract_id );
2363 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.k_buy_or_sell : '||p_q_party_uv_rec.k_buy_or_sell );
2364 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.qp_party_id : '||p_q_party_uv_rec.qp_party_id );
2365 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.qp_role_code : '||p_q_party_uv_rec.qp_role_code );
2366 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.qp_party_role : '||p_q_party_uv_rec.qp_party_role );
2367 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.qp_date_sent : '||p_q_party_uv_rec.qp_date_sent );
2368 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.qp_date_hold : '||p_q_party_uv_rec.qp_date_hold );
2369 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.qp_created_by : '||p_q_party_uv_rec.qp_created_by );
2370 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.qp_creation_date : '||p_q_party_uv_rec.qp_creation_date );
2371 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.qp_last_updated_by : '||p_q_party_uv_rec.qp_last_updated_by );
2372 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.qp_last_update_date : '||p_q_party_uv_rec.qp_last_update_date );
2373 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.qp_last_update_login : '||p_q_party_uv_rec.qp_last_update_login );
2374 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.kp_party_id : '||p_q_party_uv_rec.kp_party_id );
2375 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.kp_role_code : '|| p_q_party_uv_rec.kp_role_code );
2376 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.kp_party_role : '|| p_q_party_uv_rec.kp_party_role );
2377 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.po_party_id1 : '|| p_q_party_uv_rec.po_party_id1 );
2378 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.po_party_id2 : '|| p_q_party_uv_rec.po_party_id2 );
2379 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.po_party_object : '|| p_q_party_uv_rec.po_party_object );
2380 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.po_party_name : '|| p_q_party_uv_rec.po_party_name );
2381 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.po_party_desc : '|| p_q_party_uv_rec.po_party_desc );
2382 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.co_contact_id1 : '|| p_q_party_uv_rec.co_contact_id1 );
2383 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.co_contact_id2 : '|| p_q_party_uv_rec.co_contact_id2 );
2384 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.co_contact_object : '|| p_q_party_uv_rec.co_contact_object );
2385 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.co_contact_name : '|| p_q_party_uv_rec.co_contact_name );
2386 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.co_contact_desc : '|| p_q_party_uv_rec.co_contact_desc );
2387 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.co_email : '|| p_q_party_uv_rec.co_email );
2388 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.co_order_num : '|| p_q_party_uv_rec.co_order_num );
2389 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.co_date_sent : '|| p_q_party_uv_rec.co_date_sent );
2390 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.cp_point_id : '|| p_q_party_uv_rec.cp_point_id );
2391 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.cp_point_type : '|| p_q_party_uv_rec.cp_point_type );
2392 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.cp_primary_flag : '|| p_q_party_uv_rec.cp_primary_flag );
2393 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.cp_email : '|| p_q_party_uv_rec.cp_email );
2394 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.cp_details : '|| p_q_party_uv_rec.cp_details );
2395 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.cp_order_num : '|| p_q_party_uv_rec.cp_order_num );
2396 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_q_party_uv_rec.cp_date_sent : '|| p_q_party_uv_rec.cp_date_sent );
2397 END IF;
2398 -- ***************************************************************
2399 -- Check API version, initialize message list and create savepoint
2400 -- ***************************************************************
2401
2402 l_return_status := OKL_API.START_ACTIVITY (
2403 l_api_name,
2404 G_PKG_NAME,
2405 p_init_msg_list,
2406 l_api_version,
2407 p_api_version,
2408 '_PVT',
2409 x_return_status);
2410
2411 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
2412 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2413 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
2414 RAISE OKL_API.G_EXCEPTION_ERROR;
2415 END IF;
2416
2417 -- *******************
2418 -- Validate parameters
2419 -- *******************
2420
2421 IF p_q_party_uv_rec.quote_id IS NULL
2422 OR p_q_party_uv_rec.quote_id = G_MISS_NUM THEN
2423
2424 l_return_status := OKL_API.G_RET_STS_ERROR;
2425
2426 OKC_API.SET_MESSAGE (
2427 p_app_name => OKC_API.G_APP_NAME,
2428 p_msg_name => 'OKC_NO_PARAMS',
2429 p_token1 => 'PARAM',
2430 p_token1_value => 'QUOTE_ID',
2431 p_token2 => 'PROCESS',
2432 p_token2_value => 'GET_QUOTE_PARTY_CONTACT_POINTS');
2433
2434 ELSE
2435 l_quote_id := p_q_party_uv_rec.quote_id;
2436 l_quote_party_id := p_q_party_uv_rec.qp_party_id;
2437 l_contact_id := p_q_party_uv_rec.co_contact_id1;
2438 IF l_quote_party_id = G_MISS_NUM THEN
2439 l_quote_party_id := NULL;
2440 END IF;
2441 IF l_contact_id = G_MISS_CHAR THEN
2442 l_contact_id := NULL;
2443 END IF;
2444 END IF;
2445
2446 -- ********************
2447 -- Get quote party data
2448 -- ********************
2449 --PAGARG 4299668 call the procedure even when if party is not null so as to
2450 --populate other fields of the party record
2451 get_quote_parties (
2452 p_api_version => p_api_version,
2453 p_init_msg_list => p_init_msg_list,
2454 x_msg_count => l_msg_count,
2455 x_msg_data => l_msg_data,
2456 x_return_status => l_return_status,
2457 p_q_party_uv_rec => p_q_party_uv_rec,
2458 x_q_party_uv_tbl => l_temp_qp_tbl,
2459 x_record_count => l_party_count);
2460
2461 IF (is_debug_statement_on) THEN
2462 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2463 'after call to get_quote_parties :'||l_return_status);
2464 END IF;
2465
2466 -- **************************
2467 -- Process every party record
2468 -- **************************
2469 --PAGARG 4299668 check the table count before looping the table
2470 IF l_temp_qp_tbl.count > 0
2471 THEN
2472 FOR l_ind1 IN l_temp_qp_tbl.FIRST..l_temp_qp_tbl.LAST LOOP
2473
2474 l_curr_qp_rec := l_temp_qp_tbl(l_ind1);
2475
2476 -- ****************
2477 -- Get Party Points
2478 -- ****************
2479 IF l_contact_id IS NULL THEN
2480
2481 l_table_name := NULL;
2482 l_table_id := NULL;
2483
2484 IF l_curr_qp_rec.po_party_object = 'OKX_PARTY' THEN
2485 l_table_name := 'HZ_PARTIES';
2486 l_table_id := l_curr_qp_rec.po_party_id1;
2487 IF l_table_id = G_MISS_CHAR THEN
2488 l_table_id := NULL;
2489 END IF;
2490 END IF;
2491
2492 l_added_yn2 := FALSE;
2493
2494 IF l_table_name IS NOT NULL
2495 AND l_table_id IS NOT NULL THEN
2496
2497 l_cnt2 := 0;
2498 FOR l_c_points_rec IN l_c_points_csr (l_table_name, l_table_id) LOOP
2499 -- Contact Point is found for a Party
2500 l_added_yn2 := TRUE;
2501 l_cnt1 := l_cnt1 + 1;
2502 l_cnt2 := l_cnt2 + 1;
2503 l_co_point_tbl(l_cnt1) := l_curr_qp_rec;
2504 l_co_point_tbl(l_cnt1).cp_point_id := l_c_points_rec.contact_point_id;
2505 l_co_point_tbl(l_cnt1).cp_point_type := l_c_points_rec.contact_point_type;
2506 l_co_point_tbl(l_cnt1).cp_primary_flag := l_c_points_rec.primary_flag;
2507 l_co_point_tbl(l_cnt1).cp_email := l_c_points_rec.email_address;
2508 l_co_point_tbl(l_cnt1).cp_details := l_c_points_rec.contact_point_details;
2509 l_co_point_tbl(l_cnt1).cp_order_num := l_cnt2;
2510 l_co_point_tbl(l_cnt1).cp_date_sent := NULL;
2511
2512 END LOOP;
2513
2514 END IF;
2515
2516 END IF;
2517
2518 -- **********************
2519 -- Get party contact data
2520 -- **********************
2521
2522 IF l_curr_qp_rec.co_contact_id1 IS NULL
2523 OR l_curr_qp_rec.co_contact_id1 = G_MISS_CHAR THEN
2524
2525 get_quote_party_contacts (
2526 p_api_version => p_api_version,
2527 p_init_msg_list => p_init_msg_list,
2528 x_msg_count => l_msg_count,
2529 x_msg_data => l_msg_data,
2530 x_return_status => l_return_status,
2531 p_q_party_uv_rec => l_curr_qp_rec,
2532 x_q_party_uv_tbl => l_temp_co_tbl,
2533 x_record_count => l_contact_count);
2534
2535 IF (is_debug_statement_on) THEN
2536 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2537 'after call to get_quote_party_contacts :'||l_return_status);
2538 END IF;
2539
2540
2541 ELSE
2542 l_temp_co_tbl(1) := l_curr_qp_rec;
2543 END IF;
2544
2545 -- ****************************
2546 -- Process every contact record
2547 -- ****************************
2548 --PAGARG 4299668 check the table count before looping the table
2549 IF l_temp_co_tbl.count > 0
2550 THEN
2551 FOR l_ind2 IN l_temp_co_tbl.FIRST..l_temp_co_tbl.LAST LOOP
2552
2553 l_curr_co_rec := l_temp_co_tbl(l_ind2);
2554 l_table_name := NULL;
2555 l_table_id := NULL;
2556
2557 IF l_curr_co_rec.co_contact_object = 'OKX_PCONTACT' THEN
2558 l_table_name := 'HZ_PARTIES';
2559 l_table_id := l_curr_co_rec.co_contact_id1;
2560 IF l_table_id = G_MISS_CHAR THEN
2561 l_table_id := NULL;
2562 END IF;
2563 END IF;
2564
2565 l_added_yn2 := FALSE;
2566
2567 IF l_table_name IS NOT NULL
2568 AND l_table_id IS NOT NULL THEN
2569
2570 l_cnt2 := 0;
2571 FOR l_c_points_rec IN l_c_points_csr (l_table_name, l_table_id) LOOP
2572 -- Contact Point is found for a Contact
2573 l_added_yn2 := TRUE;
2574 l_cnt1 := l_cnt1 + 1;
2575 l_cnt2 := l_cnt2 + 1;
2576 l_co_point_tbl(l_cnt1) := l_curr_co_rec;
2577 l_co_point_tbl(l_cnt1).cp_point_id := l_c_points_rec.contact_point_id;
2578 l_co_point_tbl(l_cnt1).cp_point_type := l_c_points_rec.contact_point_type;
2579 l_co_point_tbl(l_cnt1).cp_primary_flag := l_c_points_rec.primary_flag;
2580 l_co_point_tbl(l_cnt1).cp_email := l_c_points_rec.email_address;
2581 l_co_point_tbl(l_cnt1).cp_details := l_c_points_rec.contact_point_details;
2582 l_co_point_tbl(l_cnt1).cp_order_num := l_cnt2;
2583 l_co_point_tbl(l_cnt1).cp_date_sent := NULL;
2584
2585 END LOOP;
2586 END IF;
2587 END LOOP;
2588 END IF;
2589 END LOOP;
2590 END IF;
2591
2592 -- **************
2593 -- Return results
2594 -- **************
2595
2596 IF l_contact_id IS NOT NULL
2597 AND l_co_point_tbl.COUNT = 1
2598 AND ( l_co_point_tbl(1).cp_point_id IS NULL
2599 OR l_co_point_tbl(1).cp_point_id = G_MISS_NUM) THEN
2600 -- No contact point was found
2601 x_record_count := 0;
2602 ELSE
2603 x_record_count := l_co_point_tbl.COUNT;
2604 END IF;
2605
2606 x_q_party_uv_tbl := l_co_point_tbl;
2607 x_return_status := l_return_status;
2608
2609 OKL_API.END_ACTIVITY (x_msg_count, x_msg_data);
2610
2611 IF (is_debug_procedure_on) THEN
2612 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'End(-)');
2613 END IF;
2614
2615
2616 EXCEPTION
2617
2618 WHEN OTHERS THEN
2619
2620 IF (is_debug_exception_on) THEN
2621 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,L_MODULE_NAME, 'EXCEPTION :'||'OTHERS, SQLCODE: '
2622 || sqlcode || ' , SQLERRM : ' || sqlerrm);
2623 END IF;
2624
2625 -- Close open cursors
2626 IF l_c_points_csr%ISOPEN THEN
2627 CLOSE l_c_points_csr;
2628 END IF;
2629
2630 -- store SQL error message on message stack for caller
2631 OKL_API.SET_MESSAGE (
2632 p_app_name => G_APP_NAME
2633 ,p_msg_name => G_UNEXPECTED_ERROR
2634 ,p_token1 => G_SQLCODE_TOKEN
2635 ,p_token1_value => sqlcode
2636 ,p_token2 => G_SQLERRM_TOKEN
2637 ,p_token2_value => sqlerrm);
2638
2639 -- notify caller of an UNEXPECTED error
2640 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
2641
2642 END get_quote_contact_points;
2643
2644
2645 -- Start of comments
2646 --
2647 -- Procedure Name : get_party_details
2648 -- Description : Return OKX party, vendor, site, contact information
2649 -- Business Rules :
2650 -- Parameters : quote_id, optional quote_party_id
2651 -- Version : 1.0
2652 -- End of comments
2653
2654 PROCEDURE get_party_details (
2655 p_id_code IN VARCHAR2,
2656 p_id_value IN VARCHAR2,
2657 x_party_object_tbl OUT NOCOPY party_object_tbl_type,
2658 x_return_status OUT NOCOPY VARCHAR2) IS
2659
2660 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2661 l_cnt NUMBER := 0;
2662
2663 l_id_code VARCHAR2(10);
2664 l_code1 VARCHAR2(30);
2665 l_code2 VARCHAR2(30);
2666 l_code3 VARCHAR2(30);
2667 l_added_yn1 BOOLEAN;
2668 l_added_yn2 BOOLEAN;
2669 l_pos NUMBER;
2670
2671 l_party_tbl party_object_tbl_type;
2672 l_obj1 okl_am_util_pvt.jtf_object_tbl_type;
2673 l_obj2 okl_am_util_pvt.jtf_object_tbl_type;
2674 l_obj3 okl_am_util_pvt.jtf_object_tbl_type;
2675 l_other_cols okl_am_util_pvt.select_tbl_type;
2676 l_where_cond okl_am_util_pvt.where_tbl_type;
2677
2678 CURSOR l_c_points_csr (cp_object_id VARCHAR2) IS
2679 SELECT c.contact_point_id,
2680 c.email_address,
2681 c.primary_flag
2682 FROM okx_contact_points_v c
2683 WHERE c.contact_point_type = 'EMAIL'
2684 AND c.owner_table_name = 'HZ_PARTIES'
2685 AND c.status = 'A'
2686 AND c.owner_table_id = cp_object_id;
2687
2688 CURSOR l_chr_contacts_csr (cp_k_party_id NUMBER) IS
2689 SELECT cn.jtot_object1_code,
2690 cn.object1_id1,
2691 cn.object1_id2
2692 FROM okc_contacts cn
2693 WHERE cn.cpl_id = cp_k_party_id
2694 AND NVL (cn.start_date, SYSDATE) <= SYSDATE
2695 AND NVL (cn.end_date, SYSDATE) >= SYSDATE;
2696
2697 -- for debug logging
2698 L_MODULE_NAME VARCHAR2(500) := G_MODULE_NAME||'get_party_details';
2699 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
2700 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
2701 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
2702
2703 BEGIN
2704
2705 IF (is_debug_procedure_on) THEN
2706 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'Begin(+)');
2707 END IF;
2708 IF (is_debug_statement_on) THEN
2709 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_id_code : '|| p_id_code );
2710 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,'p_id_value : '||p_id_value );
2711 END IF;
2712 IF p_id_code IS NULL
2713 OR p_id_code = G_MISS_CHAR
2714 OR p_id_code NOT IN ('P', 'PC', 'V', 'VS', 'VC', 'O')
2715 OR p_id_value IS NULL
2716 OR p_id_value = G_MISS_CHAR THEN
2717
2718 l_return_status := OKL_API.G_RET_STS_ERROR;
2719 l_id_code := NULL;
2720
2721 OKC_API.SET_MESSAGE (
2722 p_app_name => OKC_API.G_APP_NAME,
2723 p_msg_name => 'OKC_NO_PARAMS',
2724 p_token1 => 'PARAM',
2725 p_token1_value => 'ID_CODE or ID_VALUE',
2726 p_token2 => 'PROCESS',
2727 p_token2_value => 'GET_PARTY_DETAILS');
2728
2729 ELSE
2730
2731 l_id_code := p_id_code;
2732
2733 IF l_id_code = 'P' THEN
2734 l_code1 := 'OKX_PARTY';
2735 l_code2 := 'OKX_PCONTACT';
2736 ELSIF l_id_code = 'PC' THEN
2737 l_code1 := 'OKX_PCONTACT';
2738 ELSIF l_id_code = 'V' THEN
2739 l_code1 := 'OKX_VENDOR';
2740 l_code2 := 'OKX_VENDSITE';
2741 l_code3 := 'OKX_VCONTACT';
2742 ELSIF l_id_code = 'VS' THEN
2743 l_code1 := 'OKX_VENDSITE';
2744 l_code2 := 'OKX_VCONTACT';
2745 ELSIF l_id_code = 'VC' THEN
2746 l_code1 := 'OKX_VCONTACT';
2747 ELSIF l_id_code = 'O' THEN
2748 NULL;
2749 END IF;
2750
2751 END IF;
2752
2753 IF l_id_code = 'O' THEN
2754
2755 l_other_cols(1) := 'EMAIL_ADDRESS';
2756 l_other_cols(2) := 'PERSON_ID';
2757
2758 FOR l_chr_c_rec IN l_chr_contacts_csr (p_id_value) LOOP
2759
2760 okl_am_util_pvt.get_object_details (
2761 p_object_code => l_chr_c_rec.jtot_object1_code,
2762 p_object_id1 => l_chr_c_rec.object1_id1,
2763 p_object_id2 => l_chr_c_rec.object1_id2,
2764 p_other_select => l_other_cols,
2765 x_object_tbl => l_obj1,
2766 x_return_status => l_return_status);
2767
2768
2769 IF (is_debug_statement_on) THEN
2770 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2771 'after call to okl_am_util_pvt.get_object_details :'||l_return_status);
2772 END IF;
2773
2774 IF l_return_status = OKL_API.G_RET_STS_SUCCESS
2775 AND l_obj1.COUNT > 0 THEN
2776 FOR l_ind IN l_obj1.FIRST..l_obj1.LAST LOOP
2777 l_cnt := l_cnt + 1;
2778 l_pos := Instr (l_obj1(l_ind).other_values, okl_am_util_pvt.G_DELIM);
2779 l_party_tbl(l_cnt).c_code := l_obj1(l_ind).object_code;
2780 l_party_tbl(l_cnt).c_id1 := l_obj1(l_ind).id1;
2781 l_party_tbl(l_cnt).c_id2 := l_obj1(l_ind).id2;
2782 l_party_tbl(l_cnt).c_name := l_obj1(l_ind).name;
2783 l_party_tbl(l_cnt).c_desc := l_obj1(l_ind).description;
2784 l_party_tbl(l_cnt).c_email :=
2785 Substr (l_obj1(l_ind).other_values, 1, l_pos - 1);
2786 l_party_tbl(l_cnt).c_person_id :=
2787 Substr (l_obj1(l_ind).other_values, l_pos + 1);
2788 END LOOP;
2789 END IF;
2790
2791 END LOOP;
2792
2793 ELSIF l_id_code = 'PC' THEN
2794
2795 l_other_cols(1) := 'EMAIL_ADDRESS';
2796
2797 okl_am_util_pvt.get_object_details (
2798 p_object_code => l_code1,
2799 p_object_id1 => p_id_value,
2800 p_other_select => l_other_cols,
2801 x_object_tbl => l_obj1,
2802 x_return_status => l_return_status);
2803
2804 IF (is_debug_statement_on) THEN
2805 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2806 'after call to okl_am_util_pvt.get_object_details :'||l_return_status);
2807 END IF;
2808
2809 IF l_return_status = OKL_API.G_RET_STS_SUCCESS
2810 AND l_obj1.COUNT > 0 THEN
2811
2812 FOR l_ind IN l_obj1.FIRST..l_obj1.LAST LOOP
2813
2814 l_added_yn1 := FALSE;
2815
2816 FOR l_c_points_rec IN l_c_points_csr (l_obj1(l_ind).id1) LOOP
2817 l_added_yn1 := TRUE;
2818 l_cnt := l_cnt + 1;
2819 l_party_tbl(l_cnt).c_code := l_obj1(l_ind).object_code;
2820 l_party_tbl(l_cnt).c_id1 := l_obj1(l_ind).id1;
2821 l_party_tbl(l_cnt).c_id2 := l_obj1(l_ind).id2;
2822 l_party_tbl(l_cnt).c_name := l_obj1(l_ind).name;
2823 l_party_tbl(l_cnt).c_desc := l_obj1(l_ind).description;
2824 l_party_tbl(l_cnt).c_email := l_obj1(l_ind).other_values;
2825 l_party_tbl(l_cnt).pcp_id := l_c_points_rec.contact_point_id;
2826 l_party_tbl(l_cnt).pcp_email := l_c_points_rec.email_address;
2827 l_party_tbl(l_cnt).pcp_primary := l_c_points_rec.primary_flag;
2828 END LOOP;
2829
2830 IF (NOT l_added_yn1) THEN
2831 l_cnt := l_cnt + 1;
2832 l_party_tbl(l_cnt).c_code := l_obj1(l_ind).object_code;
2833 l_party_tbl(l_cnt).c_id1 := l_obj1(l_ind).id1;
2834 l_party_tbl(l_cnt).c_id2 := l_obj1(l_ind).id2;
2835 l_party_tbl(l_cnt).c_name := l_obj1(l_ind).name;
2836 l_party_tbl(l_cnt).c_desc := l_obj1(l_ind).description;
2837 l_party_tbl(l_cnt).c_email := l_obj1(l_ind).other_values;
2838 END IF;
2839
2840 END LOOP;
2841 END IF;
2842
2843 ELSIF l_id_code = 'P' THEN
2844
2845 okl_am_util_pvt.get_object_details (
2846 p_object_code => l_code1,
2847 p_object_id1 => p_id_value,
2848 x_object_tbl => l_obj1,
2849 x_return_status => l_return_status);
2850
2851 IF (is_debug_statement_on) THEN
2852 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2853 'after call to okl_am_util_pvt.get_object_details :'||l_return_status);
2854 END IF;
2855
2856 IF l_return_status = OKL_API.G_RET_STS_SUCCESS
2857 AND l_obj1.COUNT > 0 THEN
2858
2859 FOR l_ind1 IN l_obj1.FIRST..l_obj1.LAST LOOP
2860
2861 l_added_yn1 := FALSE;
2862 l_other_cols(1) := 'EMAIL_ADDRESS';
2863 l_where_cond(1).column_name := 'PARTY_ID';
2864 l_where_cond(2).column_name := 'PARTY_ID2';
2865 l_where_cond(1).condition_value := l_obj1(l_ind1).id1;
2866 l_where_cond(2).condition_value := l_obj1(l_ind1).id2;
2867
2868 okl_am_util_pvt.get_object_details (
2869 p_object_code => l_code2,
2870 p_other_select => l_other_cols,
2871 p_other_where => l_where_cond,
2872 p_check_status => 'Y',
2873 x_object_tbl => l_obj2,
2874 x_return_status => l_return_status);
2875
2876 IF (is_debug_statement_on) THEN
2877 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2878 'after call to okl_am_util_pvt.get_object_details :'||l_return_status);
2879 END IF;
2880
2881 IF l_return_status = OKL_API.G_RET_STS_SUCCESS
2882 AND l_obj2.COUNT > 0 THEN
2883
2884 FOR l_ind2 IN l_obj2.FIRST..l_obj2.LAST LOOP
2885
2886 l_added_yn2 := FALSE;
2887
2888 FOR l_c_points_rec IN l_c_points_csr (l_obj2(l_ind2).id1) LOOP
2889 l_added_yn1 := TRUE;
2890 l_added_yn2 := TRUE;
2891 l_cnt := l_cnt + 1;
2892 l_party_tbl(l_cnt).p_code := l_obj1(l_ind1).object_code;
2893 l_party_tbl(l_cnt).p_id1 := l_obj1(l_ind1).id1;
2894 l_party_tbl(l_cnt).p_id2 := l_obj1(l_ind1).id2;
2895 l_party_tbl(l_cnt).p_name := l_obj1(l_ind1).name;
2896 l_party_tbl(l_cnt).p_desc := l_obj1(l_ind1).description;
2897 l_party_tbl(l_cnt).c_code := l_obj2(l_ind2).object_code;
2898 l_party_tbl(l_cnt).c_id1 := l_obj2(l_ind2).id1;
2899 l_party_tbl(l_cnt).c_id2 := l_obj2(l_ind2).id2;
2900 l_party_tbl(l_cnt).c_name := l_obj2(l_ind2).name;
2901 l_party_tbl(l_cnt).c_desc := l_obj2(l_ind2).description;
2902 l_party_tbl(l_cnt).c_email := l_obj2(l_ind2).other_values;
2903 l_party_tbl(l_cnt).pcp_id := l_c_points_rec.contact_point_id;
2904 l_party_tbl(l_cnt).pcp_email := l_c_points_rec.email_address;
2905 l_party_tbl(l_cnt).pcp_primary := l_c_points_rec.primary_flag;
2906 END LOOP;
2907
2908 IF (NOT l_added_yn2) THEN
2909 l_added_yn1 := TRUE;
2910 l_cnt := l_cnt + 1;
2911 l_party_tbl(l_cnt).p_code := l_obj1(l_ind1).object_code;
2912 l_party_tbl(l_cnt).p_id1 := l_obj1(l_ind1).id1;
2913 l_party_tbl(l_cnt).p_id2 := l_obj1(l_ind1).id2;
2914 l_party_tbl(l_cnt).p_name := l_obj1(l_ind1).name;
2915 l_party_tbl(l_cnt).p_desc := l_obj1(l_ind1).description;
2916 l_party_tbl(l_cnt).c_code := l_obj2(l_ind2).object_code;
2917 l_party_tbl(l_cnt).c_id1 := l_obj2(l_ind2).id1;
2918 l_party_tbl(l_cnt).c_id2 := l_obj2(l_ind2).id2;
2919 l_party_tbl(l_cnt).c_name := l_obj2(l_ind2).name;
2920 l_party_tbl(l_cnt).c_desc := l_obj2(l_ind2).description;
2921 l_party_tbl(l_cnt).c_email := l_obj2(l_ind2).other_values;
2922 END IF;
2923
2924 END LOOP;
2925
2926 END IF;
2927
2928 FOR l_c_points_rec IN l_c_points_csr (l_obj1(l_ind1).id1) LOOP
2929 l_added_yn1 := TRUE;
2930 l_cnt := l_cnt + 1;
2931 l_party_tbl(l_cnt).p_code := l_obj1(l_ind1).object_code;
2932 l_party_tbl(l_cnt).p_id1 := l_obj1(l_ind1).id1;
2933 l_party_tbl(l_cnt).p_id2 := l_obj1(l_ind1).id2;
2934 l_party_tbl(l_cnt).p_name := l_obj1(l_ind1).name;
2935 l_party_tbl(l_cnt).p_desc := l_obj1(l_ind1).description;
2936 l_party_tbl(l_cnt).pcp_id := l_c_points_rec.contact_point_id;
2937 l_party_tbl(l_cnt).pcp_email := l_c_points_rec.email_address;
2938 l_party_tbl(l_cnt).pcp_primary := l_c_points_rec.primary_flag;
2939 END LOOP;
2940
2941 IF (NOT l_added_yn1) THEN
2942 l_cnt := l_cnt + 1;
2943 l_party_tbl(l_cnt).p_code := l_obj1(l_ind1).object_code;
2944 l_party_tbl(l_cnt).p_id1 := l_obj1(l_ind1).id1;
2945 l_party_tbl(l_cnt).p_id2 := l_obj1(l_ind1).id2;
2946 l_party_tbl(l_cnt).p_name := l_obj1(l_ind1).name;
2947 l_party_tbl(l_cnt).p_desc := l_obj1(l_ind1).description;
2948 END IF;
2949
2950 END LOOP;
2951
2952 END IF;
2953
2954 ELSIF l_id_code = 'VC' THEN
2955
2956 l_other_cols(1) := 'EMAIL_ADDRESS';
2957
2958 okl_am_util_pvt.get_object_details (
2959 p_object_code => l_code1,
2960 p_object_id1 => p_id_value,
2961 p_other_select => l_other_cols,
2962 x_object_tbl => l_obj1,
2963 x_return_status => l_return_status);
2964
2965 IF (is_debug_statement_on) THEN
2966 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2967 'after call to okl_am_util_pvt.get_object_details in VC :'||l_return_status);
2968 END IF;
2969
2970 IF l_return_status = OKL_API.G_RET_STS_SUCCESS
2971 AND l_obj1.COUNT > 0 THEN
2972 FOR l_ind IN l_obj1.FIRST..l_obj1.LAST LOOP
2973 l_cnt := l_cnt + 1;
2974 l_party_tbl(l_cnt).c_code := l_obj1(l_ind).object_code;
2975 l_party_tbl(l_cnt).c_id1 := l_obj1(l_ind).id1;
2976 l_party_tbl(l_cnt).c_id2 := l_obj1(l_ind).id2;
2977 l_party_tbl(l_cnt).c_name := l_obj1(l_ind).name;
2978 l_party_tbl(l_cnt).c_desc := l_obj1(l_ind).description;
2979 l_party_tbl(l_cnt).c_email := l_obj1(l_ind).other_values;
2980 END LOOP;
2981 END IF;
2982
2983 ELSIF l_id_code = 'VS' THEN
2984
2985 okl_am_util_pvt.get_object_details (
2986 p_object_code => l_code1,
2987 p_object_id1 => p_id_value,
2988 x_object_tbl => l_obj1,
2989 x_return_status => l_return_status);
2990
2991 IF (is_debug_statement_on) THEN
2992 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
2993 'after call to okl_am_util_pvt.get_object_details in VS :'||l_return_status);
2994 END IF;
2995
2996 IF l_return_status = OKL_API.G_RET_STS_SUCCESS
2997 AND l_obj1.COUNT > 0 THEN
2998
2999 FOR l_ind1 IN l_obj1.FIRST..l_obj1.LAST LOOP
3000
3001 l_other_cols(1) := 'EMAIL_ADDRESS';
3002 l_where_cond(1).column_name := 'VENDOR_SITE_ID';
3003 l_where_cond(1).condition_value := l_obj1(l_ind1).id1;
3004
3005 okl_am_util_pvt.get_object_details (
3006 p_object_code => l_code2,
3007 p_other_select => l_other_cols,
3008 p_other_where => l_where_cond,
3009 p_check_status => 'Y',
3010 x_object_tbl => l_obj2,
3011 x_return_status => l_return_status);
3012
3013 IF (is_debug_statement_on) THEN
3014 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3015 'after call to okl_am_util_pvt.get_object_details :'||l_return_status);
3016 END IF;
3017
3018
3019 IF l_return_status = OKL_API.G_RET_STS_SUCCESS
3020 AND l_obj2.COUNT > 0 THEN
3021 FOR l_ind2 IN l_obj2.FIRST..l_obj2.LAST LOOP
3022 l_cnt := l_cnt + 1;
3023 l_party_tbl(l_cnt).s_code := l_obj1(l_ind1).object_code;
3024 l_party_tbl(l_cnt).s_id1 := l_obj1(l_ind1).id1;
3025 l_party_tbl(l_cnt).s_id2 := l_obj1(l_ind1).id2;
3026 l_party_tbl(l_cnt).s_name := l_obj1(l_ind1).name;
3027 l_party_tbl(l_cnt).s_desc := l_obj1(l_ind1).description;
3028 l_party_tbl(l_cnt).c_code := l_obj2(l_ind2).object_code;
3029 l_party_tbl(l_cnt).c_id1 := l_obj2(l_ind2).id1;
3030 l_party_tbl(l_cnt).c_id2 := l_obj2(l_ind2).id2;
3031 l_party_tbl(l_cnt).c_name := l_obj2(l_ind2).name;
3032 l_party_tbl(l_cnt).c_desc := l_obj2(l_ind2).description;
3033 l_party_tbl(l_cnt).c_email := l_obj2(l_ind2).other_values;
3034 END LOOP;
3035 ELSE
3036 l_cnt := l_cnt + 1;
3037 l_party_tbl(l_cnt).s_code := l_obj1(l_ind1).object_code;
3038 l_party_tbl(l_cnt).s_id1 := l_obj1(l_ind1).id1;
3039 l_party_tbl(l_cnt).s_id2 := l_obj1(l_ind1).id2;
3040 l_party_tbl(l_cnt).s_name := l_obj1(l_ind1).name;
3041 l_party_tbl(l_cnt).s_desc := l_obj1(l_ind1).description;
3042 END IF;
3043
3044 END LOOP;
3045
3046 END IF;
3047
3048 ELSIF l_id_code = 'V' THEN
3049
3050 okl_am_util_pvt.get_object_details (
3051 p_object_code => l_code1,
3052 p_object_id1 => p_id_value,
3053 x_object_tbl => l_obj1,
3054 x_return_status => l_return_status);
3055
3056 IF (is_debug_statement_on) THEN
3057 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3058 'after call to okl_am_util_pvt.get_object_details :'||l_return_status);
3059 END IF;
3060
3061
3062 IF l_return_status = OKL_API.G_RET_STS_SUCCESS
3063 AND l_obj1.COUNT > 0 THEN
3064
3065 FOR l_ind1 IN l_obj1.FIRST..l_obj1.LAST LOOP
3066
3067 l_where_cond(1).column_name := 'VENDOR_ID';
3068 l_where_cond(1).condition_value := l_obj1(l_ind1).id1;
3069
3070 okl_am_util_pvt.get_object_details (
3071 p_object_code => l_code2,
3072 p_other_where => l_where_cond,
3073 p_check_status => 'Y',
3074 x_object_tbl => l_obj2,
3075 x_return_status => l_return_status);
3076
3077 IF (is_debug_statement_on) THEN
3078 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3079 'after call to okl_am_util_pvt.get_object_details :'||l_return_status);
3080 END IF;
3081
3082
3083 IF l_return_status = OKL_API.G_RET_STS_SUCCESS
3084 AND l_obj2.COUNT > 0 THEN
3085
3086 FOR l_ind2 IN l_obj2.FIRST..l_obj2.LAST LOOP
3087
3088 l_other_cols(1) := 'EMAIL_ADDRESS';
3089 l_where_cond(1).column_name := 'VENDOR_SITE_ID';
3090 l_where_cond(1).condition_value := l_obj2(l_ind2).id1;
3091
3092 okl_am_util_pvt.get_object_details (
3093 p_object_code => l_code3,
3094 p_other_select => l_other_cols,
3095 p_other_where => l_where_cond,
3096 p_check_status => 'Y',
3097 x_object_tbl => l_obj3,
3098 x_return_status => l_return_status);
3099
3100 IF (is_debug_statement_on) THEN
3101 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
3102 'after call to okl_am_util_pvt.get_object_details :'||l_return_status);
3103 END IF;
3104
3105 IF l_return_status = OKL_API.G_RET_STS_SUCCESS
3106 AND l_obj3.COUNT > 0 THEN
3107
3108 FOR l_ind3 IN l_obj3.FIRST..l_obj3.LAST LOOP
3109 l_cnt := l_cnt + 1;
3110 l_party_tbl(l_cnt).p_code := l_obj1(l_ind1).object_code;
3111 l_party_tbl(l_cnt).p_id1 := l_obj1(l_ind1).id1;
3112 l_party_tbl(l_cnt).p_id2 := l_obj1(l_ind1).id2;
3113 l_party_tbl(l_cnt).p_name := l_obj1(l_ind1).name;
3114 l_party_tbl(l_cnt).p_desc := l_obj1(l_ind1).description;
3115 l_party_tbl(l_cnt).s_code := l_obj2(l_ind2).object_code;
3116 l_party_tbl(l_cnt).s_id1 := l_obj2(l_ind2).id1;
3117 l_party_tbl(l_cnt).s_id2 := l_obj2(l_ind2).id2;
3118 l_party_tbl(l_cnt).s_name := l_obj2(l_ind2).name;
3119 l_party_tbl(l_cnt).s_desc := l_obj2(l_ind2).description;
3120 l_party_tbl(l_cnt).c_code := l_obj3(l_ind3).object_code;
3121 l_party_tbl(l_cnt).c_id1 := l_obj3(l_ind3).id1;
3122 l_party_tbl(l_cnt).c_id2 := l_obj3(l_ind3).id2;
3123 l_party_tbl(l_cnt).c_name := l_obj3(l_ind3).name;
3124 l_party_tbl(l_cnt).c_desc := l_obj3(l_ind3).description;
3125 l_party_tbl(l_cnt).c_email := l_obj3(l_ind3).other_values;
3126 END LOOP;
3127
3128 ELSE
3129 l_cnt := l_cnt + 1;
3130 l_party_tbl(l_cnt).p_code := l_obj1(l_ind1).object_code;
3131 l_party_tbl(l_cnt).p_id1 := l_obj1(l_ind1).id1;
3132 l_party_tbl(l_cnt).p_id2 := l_obj1(l_ind1).id2;
3133 l_party_tbl(l_cnt).p_name := l_obj1(l_ind1).name;
3134 l_party_tbl(l_cnt).p_desc := l_obj1(l_ind1).description;
3135 l_party_tbl(l_cnt).s_code := l_obj2(l_ind2).object_code;
3136 l_party_tbl(l_cnt).s_id1 := l_obj2(l_ind2).id1;
3137 l_party_tbl(l_cnt).s_id2 := l_obj2(l_ind2).id2;
3138 l_party_tbl(l_cnt).s_name := l_obj2(l_ind2).name;
3139 l_party_tbl(l_cnt).s_desc := l_obj2(l_ind2).description;
3140 END IF;
3141
3142 END LOOP;
3143
3144 ELSE
3145 l_cnt := l_cnt + 1;
3146 l_party_tbl(l_cnt).p_code := l_obj1(l_ind1).object_code;
3147 l_party_tbl(l_cnt).p_id1 := l_obj1(l_ind1).id1;
3148 l_party_tbl(l_cnt).p_id2 := l_obj1(l_ind1).id2;
3149 l_party_tbl(l_cnt).p_name := l_obj1(l_ind1).name;
3150 l_party_tbl(l_cnt).p_desc := l_obj1(l_ind1).description;
3151 END IF;
3152
3153 END LOOP;
3154
3155 END IF;
3156
3157 END IF;
3158
3159 x_party_object_tbl := l_party_tbl;
3160 x_return_status := l_return_status;
3161
3162 IF (is_debug_procedure_on) THEN
3163 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'End(-)');
3164 END IF;
3165
3166 EXCEPTION
3167
3168 WHEN OTHERS THEN
3169
3170 IF (is_debug_exception_on) THEN
3171 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_EXCEPTION,L_MODULE_NAME, 'EXCEPTION :'||'OTHERS, SQLCODE: '
3172 || sqlcode || ' , SQLERRM : ' || sqlerrm);
3173 END IF;
3174
3175 -- Close open cursors
3176 IF l_c_points_csr%ISOPEN THEN
3177 CLOSE l_c_points_csr;
3178 END IF;
3179 IF l_chr_contacts_csr%ISOPEN THEN
3180 CLOSE l_chr_contacts_csr;
3181 END IF;
3182
3183 -- store SQL error message on message stack for caller
3184 OKL_API.SET_MESSAGE (
3185 p_app_name => G_APP_NAME
3186 ,p_msg_name => G_UNEXPECTED_ERROR
3187 ,p_token1 => G_SQLCODE_TOKEN
3188 ,p_token1_value => sqlcode
3189 ,p_token2 => G_SQLERRM_TOKEN
3190 ,p_token2_value => sqlerrm);
3191
3192 -- notify caller of an UNEXPECTED error
3193 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
3194
3195 END get_party_details;
3196
3197
3198 END okl_am_parties_pvt;