1 Package body OKC_OC_INT_SALESCDT_PVT AS
2 /* $Header: OKCRSCTB.pls 120.2 2006/03/01 13:46:28 smallya noship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5 /**************************************************************
6 Processing:
7
8
9 For order to contract:
10 ...
11 Details for order to contract as follows:
12
13 For quote to contract:
14 OKC_OC_INT_QTK_PVT.create_k_from_quote calls this package through
15 a single call to OKC_OC_INT_SALESCDT_PVT.create_k_sales_credit passing
16 as parameters the contract header id, quote header id and
17 PL/SQL table p_rel_tab which has contract lines along
18 with related order lines.
19
20 Details for quote to contract as follows:
21
22 --<<GET SALES CREDIT INFORMATION>>
23 --call get_sales_credit() with header level parameters
24 --this does the following:-
25 --get sales credit information from ASO or ONT at the HEADER level
26 --and store it in global PL/SQL table g_sales_credit_tab
27
28 --call get_sales_credit () with line level parameters this time
29 --this does the following:-
30 --get sales credit information from ASO or ONT at the LINE level
31 --and append it to global PL/SQL table g_sales_credit_tab which will then
32 --contain BOTH header AND line level information
33
34 --call OKC_SALES_CREDIT_PVT.create_sales_credit()
35 --this puts sales credits information in OKC
36 --<<END OF GETTING SALES CREDIT INFORMATION>>
37
38
39
40 The OKX related quote pricing views concerned are:
41 okx_qte_sls_credits_v
42
43
44 Flow:
45 |---OKC_OC_INT_SALESCDT_PVT.create_k_sales_credit()
46 | |---get_sales_credit() called twice at header level AND line level
47 | | |---get_sales_credit_tab() at header level OR line level
48 | | (as called)
49 | |---OKC_SALES_CREDIT_PVT.create_sales_credit
50
51 **************************************************************/
52 --sales credits
53 g_sales_credit_tab OKC_SALES_CREDIT_PVT.scrv_tbl_type;
54 lx_sales_credit_tab OKC_SALES_CREDIT_PVT.scrv_tbl_type;
55
56
57 -- cursor to retrieve the contract number against a given contract id
58 -- this cursor is used to include the contract number in error messages
59 CURSOR c_knumber (c_k_id NUMBER) IS
60 SELECT contract_number
61 FROM okc_k_headers_b
62 WHERE id = c_k_id;
63
64 -- cursor to retrieve the order number against a given order id
65 -- this cursor is used to include the order number in error messages
66 CURSOR c_onumber (c_o_id NUMBER) IS
67 SELECT TO_CHAR(order_number)
68 FROM okx_order_headers_v
69 WHERE id1 = c_o_id;
70
71 -- cursor to retrieve the quote number against a given quote id
72 -- this cursor is used to include the quote number in error messages
73 CURSOR c_qnumber (c_q_id NUMBER) IS
74 SELECT TO_CHAR(quote_number)
75 FROM okx_quote_headers_v
76 WHERE id1 = c_q_id;
77
78 -- used for including in error messages
79 l_order_number VARCHAR2(120) := OKC_API.G_MISS_CHAR;
80 l_quote_number VARCHAR2(120) := OKC_API.G_MISS_CHAR;
81
82 -----------------------------------------------------------------------------
83 -- Procedure: print_error
84 -- Returns:
85 -- Purpose: Print the last error which occured
86 -- In Parameters: pos position on the line to print the message
87 -- Out Parameters:
88
89 PROCEDURE print_error(pos IN NUMBER) IS
90 x_msg_count NUMBER;
91 x_msg_data VARCHAR2(1000);
92 BEGIN
93 IF okc_util.l_trace_flag OR okc_util.l_log_flag THEN
94 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
95 p_data => x_msg_data
96 );
97 IF (l_debug = 'Y') THEN
98 okc_util.print_trace(pos, '==EXCEPTION=================');
99 END IF;
100 x_msg_data := fnd_msg_pub.get( p_msg_index => x_msg_count,
101 p_encoded => 'F'
102 );
103 IF (l_debug = 'Y') THEN
104 okc_util.print_trace(pos, 'Message : '||x_msg_data);
105 okc_util.print_trace(pos, '============================');
106 END IF;
107 END IF;
108 END print_error;
109
110
111
112 ----------------------------------------------------------------------------
113 -- Procedure: Cleanup()
114 -- Returns:
115 -- Purpose: Delete pl/sql tables, so that they are not reused
116 -- when a connection is used by another client
117 -- In Parameters: No Parameters
118 -- Out Parameters: x_return_status Varchar2
119 -----------------------------------------------------------------------------
120 PROCEDURE cleanup(x_return_status OUT NOCOPY varchar2 ) IS
121 BEGIN
122 IF (l_debug = 'Y') THEN
123 okc_util.print_trace(1, '>START - OKC_OC_INT_SALESCDT_PVT.CLEANUP - Initialize global PL/SQL Tables');
124 okc_util.print_trace(1, ' ');
125 END IF;
126
127 x_return_status := OKC_API.G_RET_STS_SUCCESS;
128
129 IF (l_debug = 'Y') THEN
130 okc_util.print_trace(2, 'Cleaning up plsql tables');
131 END IF;
132
133 --sales credits
134 g_sales_credit_tab.DELETE;
135 lx_sales_credit_tab.DELETE;
136
137 IF (l_debug = 'Y') THEN
138 okc_util.print_trace(2, 'Done Cleaning up');
139 okc_util.print_trace(1, '<END - OKC_OC_INT_SALESCDT_PVT.CLEANUP - Initialize global PL/SQL Tables');
140 END IF;
141 EXCEPTION
142 WHEN OTHERS THEN
143 OKC_API.set_message(G_APP_NAME,
144 G_UNEXPECTED_ERROR,
145 G_SQLCODE_TOKEN,
146 SQLCODE,
147 G_SQLERRM_TOKEN,
148 SQLERRM);
149 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
150 END cleanup;
151
152
153
154 ---------------------------------------------------------------------------
155 --Procedure to create contract sales credit information at the header and line levels
156 ----------------------------------------------------------------------------
157 -- Bug : 1686001 Changed references to ASO_QUOTE_HEADERS_ALL.QUOTE_HEADER_ID to OKX_QUOTE_HEADERS_V.ID1
158 PROCEDURE create_k_sales_credit (
159 x_return_status OUT NOCOPY VARCHAR2,
160
161 p_chr_id IN OKC_K_HEADERS_B.ID%TYPE,
162 p_o_flag IN VARCHAR2 ,
163 p_ohr_id IN NUMBER ,
164 p_q_flag IN VARCHAR2 ,
165 p_qhr_id IN OKX_QUOTE_HEADERS_V.ID1%TYPE
166 ,
167 p_line_inf_tab IN OKC_OC_INT_CONFIG_PVT.line_inf_tbl_type
168
169 -- this PL/SQL table has quote (or order) lines against contract lines
170
171 ) IS
172
173 i BINARY_INTEGER := 0;
174
175 l_api_version NUMBER := 1;
176 lx_msg_count NUMBER;
177 lx_msg_data VARCHAR2(2000);
178
179 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
180
181 BEGIN
182 x_return_status := l_return_status;
183
184
185 IF (l_debug = 'Y') THEN
186 OKC_UTIL.print_trace(1, ' ');
187 OKC_UTIL.print_trace(1, '================================================');
188 OKC_UTIL.print_trace(1, 'INITIALIZE GLOBAL PLSQL TABLES ');
189 OKC_UTIL.print_trace(1, '================================================');
190 OKC_UTIL.print_trace(1, ' ');
191 OKC_UTIL.print_trace(1, ' ');
192 END IF;
193
194 cleanup(x_return_status => l_return_status);
195 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
196 RAISE G_EXCEPTION_HALT_VALIDATION;
197 ELSE
198 x_return_status := l_return_status;
199 END IF;
200
201
202 IF (l_debug = 'Y') THEN
203 OKC_UTIL.print_trace(1, ' ');
204 OKC_UTIL.print_trace(1, 'Create Contract Sales Credit');
205 OKC_UTIL.print_trace(1, '-----------------------------------------------');
206 OKC_UTIL.print_trace(1, '>START - ******* OKC_OC_INT_SALESCDT_PVT.create_k_sales_credit -');
207 END IF;
208
209
210 --<<begin getting sales credit information>>
211
212 --get sales credit information from ASO or ONT at the HEADER level
213 --and store it in global PL/SQL table g_sales_credit_tab
214 IF (l_debug = 'Y') THEN
215 OKC_UTIL.print_trace(2, 'Calling get_sales_credit at HEADER level-');
216 OKC_UTIL.print_trace(2, 'Contract Id- '|| p_chr_id);
217 OKC_UTIL.print_trace(2, 'Quote Id - '|| p_qhr_id);
218 OKC_UTIL.print_trace(2, 'Order Id - '|| p_ohr_id);
219 END IF;
220 get_sales_credit (
221 p_chr_id => p_chr_id,
222 p_q_flag => p_q_flag,
223 p_qhr_id => p_qhr_id,
224 p_o_flag => p_o_flag,
225 p_ohr_id => p_ohr_id
226 );
227 IF (l_debug = 'Y') THEN
228 OKC_UTIL.print_trace(2, 'Header level call to get_sales_credit finished successfully');
229 OKC_UTIL.print_trace(2, 'Output in global PL/SQL table g_sales_credit_tab');
230 END IF;
231
232
233 --get sales credit information from ASO or ONT at the LINE level
234 --and append it to global PL/SQL table g_sales_credit_tab which will then
235 --contain BOTH header AND line level information
236 IF (l_debug = 'Y') THEN
237 OKC_UTIL.print_trace(2, 'Calling get_sales_credit at LINE level-');
238 OKC_UTIL.print_trace(2, 'Contract Id- '|| p_chr_id);
239 OKC_UTIL.print_trace(2, 'Quote Id - '|| p_qhr_id);
240 OKC_UTIL.print_trace(2, 'Order Id - '|| p_ohr_id);
241 OKC_UTIL.print_trace(2, 'PL/SQL table p_line_inf_tab- related quote or order lines and contract lines');
242 END IF;
243 get_sales_credit (
244 p_chr_id => p_chr_id,
245 p_q_flag => p_q_flag,
246 p_qhr_id => p_qhr_id,
247 p_o_flag => p_o_flag,
248 p_ohr_id => p_ohr_id,
249 p_line_inf_tab => p_line_inf_tab
250 );
251 IF (l_debug = 'Y') THEN
252 OKC_UTIL.print_trace(2, 'Line level call to get_sales_credit finished successfully');
253 OKC_UTIL.print_trace(2, 'Output in global PL/SQL table g_sale_credits_tab');
254 END IF;
255
256 --now put this sales credits information in OKC
257 IF (l_debug = 'Y') THEN
258 OKC_UTIL.print_trace(2, ' >Calling OKC_SALES_CREDIT_PVT.create_sales_credit');
259 OKC_UTIL.print_trace(2, 'input p_scrv_tbl => g_sales_credit_tab');
260 END IF;
261 IF g_sales_credit_tab.FIRST IS NOT NULL THEN
262
263 OKC_SALES_CREDIT_PVT.create_sales_credit(
264 p_api_version => l_api_version,
265 p_init_msg_list => OKC_API.G_FALSE,
266 x_return_status => l_return_status,
267 x_msg_count => lx_msg_count,
268 x_msg_data => lx_msg_data,
269 p_scrv_tbl => g_sales_credit_tab, --IN: ASO or ONT
270 x_scrv_tbl => lx_sales_credit_tab); --OUT: OKC
271
272
273 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
274 -- get quote or order number to display in error message
275 IF p_qhr_id IS NOT NULL THEN
276 BEGIN
277 OPEN c_qnumber(p_qhr_id);
278 FETCH c_qnumber INTO l_quote_number;
279 CLOSE c_qnumber;
280 EXCEPTION
281 WHEN OTHERS THEN
282 NULL;
283 END;
284 ELSIF p_ohr_id IS NOT NULL THEN
285 BEGIN
286 OPEN c_onumber(p_ohr_id);
287 FETCH c_onumber INTO l_order_number;
288 CLOSE c_onumber;
289 EXCEPTION
290 WHEN OTHERS THEN
291 NULL;
292 END;
293 END IF;
294
295 -- put error message on stack
296 --Sales Credit information from ASO or ONT table was not
297 -- created in OKC.
298 IF p_q_flag = OKC_API.G_TRUE THEN
299 OKC_API.set_message(p_app_name => G_APP_NAME,
300 p_msg_name => 'OKC_Q2K_NOSALESCDT',
301 p_token1 => 'QNUMBER',
302 p_token1_value => l_quote_number);
303 ELSIF p_o_flag = OKC_API.G_TRUE THEN
304 OKC_API.set_message(p_app_name => G_APP_NAME,
305 p_msg_name => 'OKC_Q2K_NOSALESCDT_ORD',
306 p_token1 => 'ONUMBER',
307 p_token1_value => l_order_number);
308 END IF;
309 print_error(2);
310
311
312 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
313 ----x_return_status := l_return_status;
314 ----RAISE G_EXCEPTION_HALT_VALIDATION;
315 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
316 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
317 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
318 ELSE
319 x_return_status := l_return_status;
320 END IF;
321 END IF;
322 END IF;
323
324 IF (l_debug = 'Y') THEN
325 OKC_UTIL.print_trace(2, ' >Call to OKC_SALES_CREDIT_PVT.create_sales_credit finished successfully');
326 END IF;
327 --<<end of getting sales credit information>>
328 -----------------------------------------------
329
330
331 IF (l_debug = 'Y') THEN
332 OKC_UTIL.print_trace(1, '>END - ******* OKC_OC_INT_SALESCDT_PVT.create_k_sales_credit -');
333 END IF;
334
335
336 EXCEPTION
337 WHEN OKC_API.G_EXCEPTION_ERROR THEN
338 -- store SQL error message on message stack for caller
339 OKC_API.set_message(G_APP_NAME, G_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
340 print_error(1);
341
342 -- notify caller of an error
343 x_return_status := OKC_API.G_RET_STS_ERROR;
344
345
346 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
347 -- store SQL error message on message stack for caller
348 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
349 print_error(1);
350
351 -- notify caller of an UNEXPECTED error
352 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
353
354
355 WHEN OTHERS THEN
356 -- store SQL error message on message stack for caller
357 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
358 print_error(1);
359
360 -- notify caller of an UNEXPECTED error
361 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
362
363 END create_k_sales_credit;
364
365
366
367 ----------------------------------------------------------------------------
368 -- Procedure creates sales credit information in OKC from
369 -- ASO or ONT sales credit
370 ----------------------------------------------------------------------------
371 -- Bug : 1686001 Changed references to ASO_QUOTE_HEADERS_ALL.QUOTE_HEADER_ID to OKX_QUOTE_HEADERS_V.ID1
372 PROCEDURE get_sales_credit(
373 p_chr_id IN NUMBER,
374 p_q_flag IN VARCHAR2 ,
375 p_qhr_id IN OKX_QUOTE_HEADERS_V.ID1%TYPE
376 ,
377 p_o_flag IN VARCHAR2 ,
378 p_ohr_id IN NUMBER ,
379 p_line_inf_tab IN OKC_OC_INT_CONFIG_PVT.line_inf_tbl_type
380
381 ) IS
382
383 i BINARY_INTEGER := 0;
384
385 BEGIN
386 IF (l_debug = 'Y') THEN
387 OKC_UTIL.print_trace(3, 'START --> get_sales_credit- ');
388 OKC_UTIL.print_trace(3, 'Contract Id - '|| p_chr_id);
389 OKC_UTIL.print_trace(3, 'Quote Id - '|| p_qhr_id);
390 OKC_UTIL.print_trace(3, 'Order Id - '|| p_ohr_id);
391 END IF;
392
393 --get all the sales credit for the quote header (or order header)
394 --for processing at the header level
395 --and store them in global PL/SQL table g_sales_credit_tab
396 IF p_q_flag = OKC_API.G_TRUE AND p_qhr_id IS NOT NULL AND p_qhr_id <> OKC_API.G_MISS_NUM AND
397 p_line_inf_tab.FIRST IS NULL
398 THEN
399 IF (l_debug = 'Y') THEN
400 OKC_UTIL.print_trace(3, 'Calling get_sales_credit_tab with p_chr_id and p_qhr_id/p_ohr_id for processing at header level');
401 END IF;
402 -- get_sales_credit_tab stores it's output in g_sales_credit_tab
403 get_sales_credit_tab(p_chr_id => p_chr_id,
404 p_q_flag => p_q_flag,
405 p_qhr_id => p_qhr_id
406 );
407 IF (l_debug = 'Y') THEN
408 OKC_UTIL.print_trace(3, 'Call to get_sales_credit_tab finished successfully');
409 END IF;
410
411 ELSIF p_o_flag = OKC_API.G_TRUE AND p_ohr_id IS NOT NULL AND p_ohr_id <> OKC_API.G_MISS_NUM AND
412 p_line_inf_tab.FIRST IS NULL
413 THEN
414 IF (l_debug = 'Y') THEN
415 OKC_UTIL.print_trace(3, 'Calling get_sales_credit_tab with p_chr_id and p_qhr_id/p_ohr_id for processing at header level');
416 END IF;
417 -- get_sales_credit_tab stores it's output in g_sales_credit_tab
418 get_sales_credit_tab(p_chr_id => p_chr_id,
419 p_o_flag => p_o_flag,
420 p_ohr_id => p_ohr_id
421 );
422 IF (l_debug = 'Y') THEN
423 OKC_UTIL.print_trace(3, 'Call to get_sales_credit_tab finished successfully');
424 END IF;
425 END IF;
426
427
428
429 --get all the sales credits for each quote line (or order line)
430 --and store them all in global PL/SQL table g_sales_credit_tab
431 --keeping intact the header level information that g_sales_credit_tab may
432 --already contain.
433 IF p_line_inf_tab.FIRST IS NOT NULL THEN
434 IF (l_debug = 'Y') THEN
435 OKC_UTIL.print_trace(3, 'Calling get_sales_credit_tab with p_chr_id and p_qhr_id/p_ohr_id and p_line_inf_tab for processing at line level');
436 END IF;
437
438 i := p_line_inf_tab.FIRST;
439 WHILE i IS NOT NULL LOOP
440 IF p_q_flag = OKC_API.G_TRUE AND p_qhr_id IS NOT NULL AND p_qhr_id <> OKC_API.G_MISS_NUM AND
441 p_line_inf_tab(i).line_type <> OKC_OC_INT_CONFIG_PVT.G_BASE_LINE THEN
442 -- get SALES CREDITS information from ASO tables
443 -- get_sales_credit_tab stores it's output in g_sales_credit_tab
444 get_sales_credit_tab(p_chr_id => p_chr_id,
445 p_q_flag => p_q_flag,
446 p_qhr_id => p_qhr_id,
447 p_cle_id => p_line_inf_tab(i).cle_id,
448 p_qle_id => p_line_inf_tab(i).object1_id1
449 );
450 ELSIF p_o_flag = OKC_API.G_TRUE AND p_ohr_id IS NOT NULL AND p_ohr_id <> OKC_API.G_MISS_NUM AND
451 p_line_inf_tab(i).line_type <> OKC_OC_INT_CONFIG_PVT.G_BASE_LINE THEN
452 -- get SALES CREDITS information from ONT tables
453 -- get_sales_credit_tab stores it's output in g_sales_credit_tab
454 get_sales_credit_tab(p_chr_id => p_chr_id,
455 p_o_flag => p_o_flag,
456 p_ohr_id => p_ohr_id,
457 p_cle_id => p_line_inf_tab(i).cle_id,
458 p_ole_id => p_line_inf_tab(i).object1_id1
459 );
460 END IF;
461
462 i := p_line_inf_tab.NEXT(i);
463 END LOOP;
464 END IF;
465
466 IF (l_debug = 'Y') THEN
467 OKC_UTIL.print_trace(3, 'Call to get_sales_credit_tab finished successfully');
468 OKC_UTIL.print_trace(3, 'END --> get_sales_credit ');
469 END IF;
470 EXCEPTION
471 WHEN OTHERS THEN
472 RAISE OKC_API.G_EXCEPTION_ERROR;
473 END get_sales_credit;
474
475
476
477 ----------------------------------------------------------------------
478 -- PROCEDURE to get SALES CREDITS information from ASO or ONT tables
479 -- get_sales_credit_tab stores it's output in global PL/SQL table g_sales_credit_tab
480 ----------------------------------------------------------------------
481 -- Bug : 1686001 Changed references to ASO_QUOTE_HEADERS_ALL.QUOTE_HEADER_ID to OKX_QUOTE_HEADERS_V.ID1
482 -- ASO_QUOTE_LINES_ALL.LINE_HEADER_ID to OKX_QUOTE_LINES_V.ID1
483 PROCEDURE get_sales_credit_tab(
484 p_chr_id IN OKC_K_HEADERS_B.ID%TYPE,
485 p_cle_id IN OKC_K_LINES_B.ID%TYPE ,
486
487 p_o_flag IN VARCHAR2 ,
488 p_ohr_id IN NUMBER ,
489 p_ole_id IN NUMBER ,
490
491 p_q_flag IN VARCHAR2 ,
492 p_qhr_id IN OKX_QUOTE_HEADERS_V.ID1%TYPE ,
493 p_qle_id IN OKX_QUOTE_LINES_V.ID1%TYPE ) IS
494
495 l_no_data_found BOOLEAN := TRUE;
496
497 i BINARY_INTEGER := 0;
498
499 -- cursor to get okx_qte_sls_credits_v or
500 -- okx_ord_sls_credits_v information
501 -- Bug : 1686001 Changed references to ASO_QUOTE_HEADERS_ALL.QUOTE_HEADER_ID to OKX_QUOTE_HEADERS_V.ID1
502 -- ASO_QUOTE_LINES_ALL.LINE_HEADER_ID to OKX_QUOTE_LINES_V.ID1
503 CURSOR c_source_sales_credit_rec (cp_q_flag IN VARCHAR2,
504 cp_qhr_id IN OKX_QUOTE_HEADERS_V.ID1%TYPE,
505 cp_qle_id IN OKX_QUOTE_LINES_V.ID1%TYPE,
506 cp_o_flag IN VARCHAR2,
507 cp_ohr_id IN NUMBER,
508 cp_ole_id IN NUMBER) IS
509 --could be either ASO or ONT
510 --only ONE of the following two queries in the union will be executed in
511 --a call depending on which flag (p_q_flag or p_o_flag) is true
512
513 -- first query to get okx_qte_sls_credits_v information
514 SELECT a.quote_header_id source_header_id,
515 a.quote_line_id source_line_id,
516 a.percent,
517 b.id1 salesrep_id,
518 a.resource_group_id, --no matching column in OKC
519 -----employee_person_id, --obsolete column in ASO replace by resource id
520 a.sales_credit_type_id,
521 a.attribute_category_code, --new column needed in OKC
522 a.object_version_number
523 FROM okx_qte_sls_credits_v a
524 ,okx_salesreps_v b
525 WHERE cp_q_flag = OKC_API.G_TRUE
526 AND a.quote_header_id = cp_qhr_id
527 AND ((cp_qle_id = OKC_API.G_MISS_NUM AND a.quote_line_id IS NULL) OR
528 (cp_qle_id <> OKC_API.G_MISS_NUM AND a.quote_line_id = cp_qle_id))
529 AND a.resource_id = b.resource_id
530 AND b.org_id = SYS_CONTEXT('OKC_CONTEXT', 'ORG_ID')
531
532 UNION ALL
533
534 -- second query to get okx_ord_sls_credits_v information
535 SELECT header_id source_header_id,
536 line_id source_line_id,
537 percent,
538 salesrep_id,
539 -------sales_credit_type_id,
540 TO_NUMBER(NULL),
541 sales_credit_type_id,
542 TO_CHAR(NULL),
543 TO_NUMBER(NULL) --object_version_number not present in order table
544 FROM okx_ord_sls_credits_v
545 WHERE cp_o_flag = OKC_API.G_TRUE
546 AND header_id = cp_ohr_id
547 AND ((cp_ole_id = OKC_API.G_MISS_NUM AND line_id IS NULL) OR
548 (cp_ole_id <> OKC_API.G_MISS_NUM AND line_id = cp_ole_id));
549
550
551 CURSOR c_get_contact_id(b_chr_id NUMBER,b_salesrep_ctrol VARCHAR2,b_object_id VARCHAR2) is
552 SELECT id
553 FROM okc_contacts
554 WHERE dnz_chr_id = b_chr_id
555 AND cro_code = b_salesrep_ctrol
556 AND object1_id1=b_object_id
557 AND rownum = 1;
558
559 l_supplier_role_id OKC_K_PARTY_ROLES_B.ID%TYPE;
560 l_source_sales_credit_rec c_source_sales_credit_rec%ROWTYPE;
561 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
562
563 BEGIN
564
565 IF (l_debug = 'Y') THEN
566 OKC_UTIL.print_trace(4, 'START --> get_sales_credit_tab- ');
567 OKC_UTIL.print_trace(4, 'Contract Id - '|| p_chr_id);
568 OKC_UTIL.print_trace(4, 'Contract Line Id - '|| p_cle_id);
569 OKC_UTIL.print_trace(4, 'Quote Id - '|| p_qhr_id);
570 OKC_UTIL.print_trace(4, 'Quote Line Id - '|| p_qle_id);
571 OKC_UTIL.print_trace(4, 'Order Id - '|| p_ohr_id);
572 OKC_UTIL.print_trace(4, 'Order Line Id - '|| p_ole_id);
573 END IF;
574
575
576 IF (p_q_flag = OKC_API.G_TRUE AND
577 p_qhr_id IS NOT NULL AND
578 p_qhr_id <> OKC_API.G_MISS_NUM) THEN
579 IF (l_debug = 'Y') THEN
580 OKC_UTIL.print_trace(4, 'Processing sales credit information for Quote Id - '|| p_qhr_id);
581 END IF;
582 ELSIF (p_o_flag = OKC_API.G_TRUE AND
583 p_ohr_id IS NOT NULL AND
584 p_ohr_id <> OKC_API.G_MISS_NUM) THEN
585 IF (l_debug = 'Y') THEN
586 OKC_UTIL.print_trace(4, 'Processing sales credit information for Order Id - '|| p_ohr_id);
587 END IF;
588 END IF;
589
590
591 OPEN c_source_sales_credit_rec (cp_q_flag => p_q_flag,
592 cp_qhr_id => p_qhr_id,
593 cp_qle_id => p_qle_id,
594 cp_o_flag => p_o_flag,
595 cp_ohr_id => p_ohr_id,
596 cp_ole_id => p_ole_id); --ASO or ONT
597 LOOP
598 --use COUNT to keep adding to existing records, if any, in g_sales_credit_tab
599 --otherwise if table empty, COUNT returns 0
600 i := g_sales_credit_tab.COUNT + 1;
601 FETCH c_source_sales_credit_rec INTO l_source_sales_credit_rec;
602 EXIT WHEN c_source_sales_credit_rec%NOTFOUND;
603
604 -- map okx_qte_sls_credits_v or
605 -- okx_ord_sls_credits_v to OKC_K_SALES_CREDITS
606
607 -- we don't need to enter the ID because it is automatically generated
608
609 IF l_source_sales_credit_rec.source_header_id IS NOT NULL THEN
610 --quote or order
611 g_sales_credit_tab(i).CHR_ID := p_chr_id;
612 g_sales_credit_tab(i).DNZ_CHR_ID := p_chr_id;
613 END IF;
614 IF l_source_sales_credit_rec.source_line_id IS NOT NULL THEN
615 --quote or order
616 g_sales_credit_tab(i).CLE_ID := p_cle_id;
617 END IF;
618
619 g_sales_credit_tab(i).percent := l_source_sales_credit_rec.percent;
620 IF (l_debug = 'Y') THEN
621 OKC_UTIL.print_trace(4, 'percent: '|| l_source_sales_credit_rec.percent);
622 END IF;
623
624 /*********** contact creation no longer needed ***********
625 --each SALESREP_ID from the quote (or order) is created as a contact
626 --in OKC_CONTACTS
627 --OKC_K_SALES_CREDITS.CTC_ID is a foreign key pointing to
628 --OKC_CONTACTS.ID
629 OPEN c_get_contact_id(p_chr_id,G_SALESREP_CTROL,l_source_sales_credit_rec.salesrep_id);
630 FETCH c_get_contact_id into g_sales_credit_tab(i).ctc_id;
631 IF c_get_contact_id%NOTFOUND then
632 SELECT id into l_supplier_role_id FROM OKC_K_PARTY_ROLES_B
633 WHERE dnz_chr_id = p_chr_id
634 AND rle_code = 'SUPPLIER';
635
636 OKO_OM_INT_OTK_PVT.create_contact_for_contract(
637 p_cro_code => G_SALESREP_CTROL
638 ,p_rle_code => 'SUPPLIER'
639 ,p_buy_or_sell => 'S'
640 ,p_chr_id => p_chr_id
641 ,p_object_id1 => l_source_sales_credit_rec.salesrep_id
642 ,p_object_id2 => '#'
643 ,p_contact_seq => NULL
644 ,p_cpl_id => l_supplier_role_id
645 ,x_return_status => l_return_status
646 );
647
648 --IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
649 -- x_return_status := l_return_status;
650 --END IF;
651 IF (l_debug = 'Y') THEN
652 okc_util.print_trace(4, 'OUTPUT RECORD - OKO_OM_INT_OTK_PVT.create_contact_for_contract :');
653 okc_util.print_trace(4, '===============================================');
654 END IF;
655 --okc_util.print_trace(4, 'Status = '||x_return_status);
656 IF (l_debug = 'Y') THEN
657 okc_util.print_trace(4, 'Status = '||l_return_status);
658 END IF;
659 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
660 IF (l_debug = 'Y') THEN
661 OKC_UTIL.print_trace(4,SQLERRM);
662 END IF;
663 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
664 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
665 IF (l_debug = 'Y') THEN
666 OKC_UTIL.print_trace(4,SQLERRM);
667 END IF;
668 RAISE OKC_API.G_EXCEPTION_ERROR;
669 END IF;
670
671 IF c_get_contact_id%ISOPEN then
672 CLOSE c_get_contact_id;
673 OPEN c_get_contact_id(p_chr_id,G_SALESREP_CTROL, l_source_sales_credit_rec.salesrep_id);
674 FETCH c_get_contact_id into g_sales_credit_tab(i).ctc_id;
675 END IF;
676
677 END IF;
678 CLOSE c_get_contact_id;
679 **************************************************************************/
680
681 ---obsolete column g_sales_credit_tab(i).ctc_id := l_source_sales_credit_rec.salesrep_id;
682 g_sales_credit_tab(i).salesrep_id1 := l_source_sales_credit_rec.salesrep_id;
683 g_sales_credit_tab(i).salesrep_id2 := '#';
684 IF (l_debug = 'Y') THEN
685 OKC_UTIL.print_trace(4, 'salesrep_id1: '|| l_source_sales_credit_rec.salesrep_id);
686 OKC_UTIL.print_trace(4, 'salesrep_id2: #');
687 END IF;
688
689
690 ----g_sales_credit_tab(i). := l_source_sales_credit_rec.resource_group_id;
691 --no matching column in OKC
692
693 g_sales_credit_tab(i).sales_credit_type_id1 := l_source_sales_credit_rec.sales_credit_type_id;
694 g_sales_credit_tab(i).sales_credit_type_id2 := '#';
695 -----g_sales_credit. := l_source_sales_credit_rec.attribute_category_code
696 --new column needed in OKC
697 IF (l_debug = 'Y') THEN
698 OKC_UTIL.print_trace(4, 'sales_credit_type_id1: '|| l_source_sales_credit_rec.sales_credit_type_id);
699 OKC_UTIL.print_trace(4, 'sales_credit_type_id2: #');
700 END IF;
701
702 g_sales_credit_tab(i).object_version_number := l_source_sales_credit_rec.object_version_number;
703 IF (l_debug = 'Y') THEN
704 OKC_UTIL.print_trace(4, 'object_version_number: '|| l_source_sales_credit_rec.object_version_number);
705 END IF;
706
707 END LOOP;
708 --IF c_source_sales_credit_rec%ROWCOUNT > 0 THEN
709 -- l_no_data_found := FALSE;
710 --END IF;
711 CLOSE c_source_sales_credit_rec;
712
713
714
715 --IF l_no_data_found THEN
716 -- OKC_UTIL.print_trace(4, 'END --> get_sales_credit_tab: returned error- ');
717 -- RAISE OKC_API.G_EXCEPTION_ERROR;
718 --ELSE
719 IF (l_debug = 'Y') THEN
720 OKC_UTIL.print_trace(4, 'Output: PL/SQL global table- g_sale_credits_tab');
721 OKC_UTIL.print_trace(4, 'END --> get_sales_credit_tab- ');
722 END IF;
723 --END IF;
724
725 EXCEPTION
726 WHEN OTHERS THEN
727 IF (l_debug = 'Y') THEN
728 OKC_UTIL.print_trace(4,SQLERRM);
729 END IF;
730 IF c_source_sales_credit_rec%ISOPEN THEN
731 CLOSE c_source_sales_credit_rec;
732 END IF;
733 RAISE OKC_API.G_EXCEPTION_ERROR;
734 END get_sales_credit_tab;
735
736
737
738 -- ========================================================================
739 -- START OF KTQ or KTO SALES CREDIT INFORMATION CREATION
740 -- or UPDATE
741 -- ========================================================================
742
743 PROCEDURE get_sales_credit(p_chr_id IN NUMBER,
744 p_cle_id IN NUMBER,
745 --
746 p_qhr_id IN NUMBER,
747 p_qle_id IN NUMBER,
748 p_q_flag IN VARCHAR2,
749 --
750 p_ohr_id IN NUMBER,
751 p_ole_id IN NUMBER,
752 p_o_flag IN VARCHAR2,
753 --
754 p_level IN VARCHAR2,
755 --
756 p_nqhr_id IN NUMBER,
757 p_nqle_idx IN NUMBER,
758 --
759 x_k_sales_credit_tab OUT NOCOPY k_sales_credit_tab_type,
760 x_sales_credit_tab OUT NOCOPY ASO_QUOTE_PUB.sales_credit_tbl_type) IS
761
762 --
763 -- CURSOR to identify the sales credits which have to be deleted taking
764 -- into account those marked as to be updated or created: any quote sales
765 -- credit not yet referenced in l_sales_credit_tab will be added as to be deleted.
766 --
767
768 CURSOR c_sales_credit (b_q_flag VARCHAR, b_qh_id NUMBER, b_ql_id NUMBER,
769 b_o_flag VARCHAR, b_oh_id NUMBER, b_ol_id NUMBER) IS
770 SELECT
771 qscdt.sales_credit_id SALES_CREDIT_ID -- quote (header or line) sales credit ID
772 FROM
773 OKX_QTE_SLS_CREDITS_V qscdt
774 WHERE b_q_flag = OKC_API.g_true
775 AND qscdt.quote_header_id = b_qh_id
776 AND ((b_ql_id IS NULL AND qscdt.quote_line_id IS NULL)
777 OR (b_ql_id IS NOT NULL AND qscdt.quote_line_id = b_ql_id))
778 UNION
779
780 SELECT
781 oscdt.sales_credit_id SALES_CREDIT_ID -- order (header or line) sales credit ID
782 FROM
783 OKX_ORD_SLS_CREDITS_V oscdt
784 WHERE b_o_flag = OKC_API.g_true
785 AND oscdt.header_id = b_oh_id
786 AND ((b_ol_id IS NULL AND oscdt.line_id IS NULL)
787 OR (b_ol_id IS NOT NULL AND oscdt.line_id = b_ql_id));
788 --
789 --
790 CURSOR c_k_sales_credit( b_kh_id NUMBER,b_kl_id NUMBER,
791 b_q_flag VARCHAR,b_qh_id NUMBER,b_ql_id NUMBER,
792 b_o_flag VARCHAR,b_oh_id NUMBER,b_ol_id NUMBER) IS
793 SELECT
794 DECODE(qscdt.resource_id,NULL,g_aso_op_code_create,
795 DECODE(qscdt.sales_credit_type_id,NULL,g_aso_op_code_create,
796 DECODE(qscdt.percent,NULL,g_aso_op_code_create,g_aso_op_code_update)
797 )
798 ) OPERATION_CODE,
799 qscdt.sales_credit_id sales_credit_id, -- quote(Header or line) sales credit ID
800 -- kscdt.ctc_id,
801 sr.resource_id,
802 kscdt.sales_credit_type_id1,
803 kscdt.percent,
804 kscdt.id, -- contract (Header or line) sales credit ID
805 kscdt.creation_date,
806 kscdt.chr_id,
807 kscdt.cle_id,
808 kscdt.last_update_date
809 -- kscdt.object_version_number
810 FROM
811 OKC_K_SALES_CREDITS kscdt,
812 OKX_QTE_SLS_CREDITS_V qscdt,
813 OKX_SALESREPS_V sr
814 WHERE
815 b_q_flag = OKC_API.G_TRUE
816 AND kscdt.chr_id = b_kh_id
817 AND ((b_kl_id IS NULL AND kscdt.cle_id IS NULL)
818 OR (b_kl_id IS NOT NULL AND kscdt.cle_id = b_kl_id))
819 AND qscdt.quote_header_id(+) = b_qh_id
820 AND NVL(qscdt. Quote_line_id(+), 0) = NVL(b_ql_id, 0)
821 -- AND qscdt.resource_id(+)= kscdt.ctc_id
822 AND qscdt.sales_credit_type_id (+)= kscdt.sales_credit_type_id1
823 AND qscdt.percent(+)= kscdt.percent
824 AND sr.id1 = kscdt.salesrep_id1
825 AND sr.id2 = kscdt.salesrep_id2
826
827 UNION
828
829 SELECT
830 DECODE(oscdt.salesrep_id,NULL,g_aso_op_code_create,
831 DECODE(oscdt.sales_credit_type_id,NULL,g_aso_op_code_create,
832 DECODE(oscdt.percent,NULL,g_aso_op_code_create,g_aso_op_code_update)
833 )
834 ) OPERATION_CODE,
835 oscdt.sales_credit_id sales_credit_id, -- order(Header or line) sales credit ID
836 -- kscdt.ctc_id,
837 -- sr.resource_id,
838 to_number(kscdt.salesrep_id1) resource_id,
839 kscdt.sales_credit_type_id1,
840 kscdt.percent,
841 kscdt.id, -- contract (Header or line) sales credit ID
842 kscdt.creation_date,
843 kscdt.chr_id,
844 kscdt.cle_id,
845 kscdt.last_update_date
846 -- kscdt.object_version_number
847 FROM
848 OKC_K_SALES_CREDITS kscdt,
849 OKX_ORD_SLS_CREDITS_V oscdt
850 -- OKX_SALESREPS_V sr
851 WHERE
852 b_o_flag = OKC_API.G_TRUE
853 AND kscdt.chr_id = b_kh_id
854 AND ((b_kl_id IS NULL AND kscdt.cle_id IS NULL)
855 OR (b_kl_id IS NOT NULL AND kscdt.cle_id = b_kl_id))
856 AND oscdt.header_id(+) = b_oh_id
857 AND NVL(oscdt.line_id(+), 0) = NVL(b_ol_id, 0)
858 -- AND oscdt.salesrep_id(+)= kscdt.ctc_id
859 AND oscdt.sales_credit_type_id (+)= kscdt.sales_credit_type_id1
860 AND oscdt.percent(+)= kscdt.percent
861 -- AND sr.id1 = kscdt.salesrep_id1
862 -- AND sr.id2 = kscdt.salesrep_id2
863 ORDER BY
864 1,
865 3,
866 4,
867 5,
868 6,
869 7 ;
870
871 l_prec_sls_crdt_id NUMBER := NULL;
872 l_prec_sls_crdt_procsd VARCHAR2(1) := OKC_API.G_FALSE;
873 g_miss_sls_crdt_rec c_k_sales_credit%ROWTYPE;
874 l_prec_sls_crdt_rec c_k_sales_credit%ROWTYPE := g_miss_sls_crdt_rec;
875 l_sales_credit_insert VARCHAR2(1) := OKC_API.G_TRUE;
876
877 l_sales_credit_rec ASO_QUOTE_PUB.sales_credit_rec_type;
878 l_sales_credit_tab ASO_QUOTE_PUB.sales_credit_tbl_type;
879 l_k_sales_credit_tab k_sales_credit_tab_type;
880
881 x BINARY_INTEGER;
882 y BINARY_INTEGER;
883
884 BEGIN
885
886 IF (l_debug = 'Y') THEN
887 okc_util.print_trace(1,'---------------------------------');
888 okc_util.print_trace(1,'>> start : Get sales credit ');
889 okc_util.print_trace(1,'---------------------------------');
890 END IF;
891
892 --
893 -- housekeeping
894 --
895 l_sales_credit_tab.DELETE;
896 l_k_sales_credit_tab.DELETE;
897
898 x_sales_credit_tab.DELETE;
899 x_k_sales_credit_tab.DELETE;
900
901 x := l_sales_credit_tab.COUNT;
902 y := l_k_sales_credit_tab.COUNT;
903
904 IF x = 0 THEN
905 x:=x+1;
906 END IF;
907
908 IF y = 0 THEN
909 y:=y+1;
910 END IF;
911
912 --
913 -- Fill in the l_sales_credit_tab variable with sales credit to be created or updated
914 --
915
916 FOR sales_credit_rec IN c_k_sales_credit (p_chr_id, p_cle_id,
917 p_q_flag, p_qhr_id, p_qle_id,
918 p_o_flag, p_ohr_id, p_ole_id ) LOOP
919
920 l_sales_credit_insert := OKC_API.G_TRUE;
921
922
923 IF sales_credit_rec.operation_code = g_aso_op_code_create THEN
924
925 IF (l_debug = 'Y') THEN
926 okc_util.print_trace(1,'step 1-1 operation code = '||sales_credit_rec.operation_code);
927 END IF;
928
929 -- Populate l_sales_credit_rec with infomation from sales_credit_rec;
930
931 l_sales_credit_rec.quote_header_id := p_qhr_id;
932 l_sales_credit_rec.quote_line_id := p_qle_id;
933
934 l_sales_credit_rec.operation_code := sales_credit_rec.operation_code;
935
936 l_sales_credit_rec.sales_credit_id := sales_credit_rec.sales_credit_id;
937 l_sales_credit_rec.percent := sales_credit_rec.percent;
938 -- l_sales_credit_rec.resource_id := sales_credit_rec.ctc_id;
939 l_sales_credit_rec.resource_id := sales_credit_rec.resource_id;
940 l_sales_credit_rec.sales_credit_type_id := sales_credit_rec.sales_credit_type_id1;
941 -- l_sales_credit_rec.object_version_number := sales_credit_rec.object_version_number;
942 l_sales_credit_rec.last_update_date := sales_credit_rec.last_update_date;
943
944
945 IF p_level = 'L' AND p_qhr_id IS NULL AND p_qle_id IS NULL THEN
946 -- related quote line has to be created
947 l_sales_credit_rec.quote_header_id := p_nqhr_id;
948 l_sales_credit_rec.qte_line_index := p_nqle_idx;
949 END IF;
950 l_sales_credit_rec.sales_credit_id := OKC_API.G_MISS_NUM;
951 END IF;
952
953
954
955 IF sales_credit_rec.operation_code = g_aso_op_code_update THEN
956 IF (l_debug = 'Y') THEN
957 okc_util.print_trace(1,'step 1-2 operation code = '||sales_credit_rec.operation_code);
958 END IF;
959 IF NVL(l_prec_sls_crdt_id,0) <> sales_credit_rec.id THEN
960
961 --
962 -- Need to check if the related quote sales credit is not already planned to be
963 -- updated in the l_sales_credit_tab variable
964 --
965 IF l_sales_credit_tab.first IS NOT NULL THEN
966 FOR i IN l_sales_credit_tab.first..l_sales_credit_tab.last LOOP
967 IF l_sales_credit_tab(i).sales_credit_id = sales_credit_rec.sales_credit_id THEN
968
969 IF (l_debug = 'Y') THEN
970 okc_util.print_trace(1,'step 1-3 related sales credit adjustment is already planned to be updated');
971 END IF;
972
973 l_sales_credit_insert := OKC_API.G_FALSE;
974 exit;
975 END IF;
976 END LOOP;
977 END IF;
978 ELSE
979 --
980 -- current contract sales credit matches with multiple quote sales credits
981 -- and will be disregarded if already processed or if related quote sales credit is
982 -- not already planned to be updated in the l_sales_credit_tab variable.
983 --
984 IF l_prec_sls_crdt_procsd = OKC_API.G_TRUE THEN
985 l_sales_credit_insert := OKC_API.G_FALSE;
986 l_prec_sls_crdt_procsd := OKC_API.G_FALSE;
987 ELSE
988 IF l_sales_credit_tab.first IS NOT NULL THEN
989 FOR i IN l_sales_credit_tab.first..l_sales_credit_tab.last LOOP
990 IF l_sales_credit_tab(i).sales_credit_id = sales_credit_rec.sales_credit_id THEN
991 l_sales_credit_insert := OKC_API.G_FALSE;
992 IF (l_debug = 'Y') THEN
993 okc_util.print_trace(1,'step 1-4 checking ctrct sls crdt with multiple qte sls crdt');
994 END IF;
995 exit;
996 END IF;
997 END LOOP;
998 END IF;
999 END IF; -- IF l_prec_sls_crdt_procsd := okc_api.g_true then..
1000 END IF; -- IF l_prec_sls_crdt_id <> sales_credit_rec.id and ..
1001
1002
1003 IF NVL(l_prec_sls_crdt_id,0) <> sales_credit_rec.id THEN
1004 IF l_prec_sls_crdt_id IS NOT NULL AND l_prec_sls_crdt_procsd = OKC_API.G_FALSE THEN
1005 -- Populate l_sales_credit_rec with information from l_prec_sls_crdt_rec;
1006
1007
1008 l_sales_credit_rec.sales_credit_id := l_prec_sls_crdt_rec.sales_credit_id;
1009 l_sales_credit_rec.percent := l_prec_sls_crdt_rec.percent;
1010 -- l_sales_credit_rec.resource_id := l_prec_sls_crdt_rec.ctc_id;
1011 l_sales_credit_rec.resource_id := l_prec_sls_crdt_rec.resource_id;
1012 l_sales_credit_rec.sales_credit_type_id := l_prec_sls_crdt_rec.sales_credit_type_id1;
1013 -- l_sales_credit_rec.object_version_number := l_prec_sls_crdt_rec.object_version_number;
1014 l_sales_credit_rec.last_update_date := l_prec_sls_crdt_rec.last_update_date;
1015
1016 l_sales_credit_rec.operation_code := g_aso_op_code_create;
1017 l_sales_credit_rec.quote_header_id := p_qhr_id;
1018 l_sales_credit_rec.quote_line_id := p_qle_id;
1019 l_sales_credit_rec.sales_credit_id := OKC_API.G_MISS_NUM;
1020
1021 l_sales_credit_tab(x) := l_sales_credit_rec;
1022 x := x + 1;
1023 --
1024 l_k_sales_credit_tab(y).id := l_prec_sls_crdt_rec.id;
1025 l_k_sales_credit_tab(y).level := p_level;
1026 y := y + 1;
1027
1028 l_prec_sls_crdt_procsd := okc_api.g_true;
1029
1030 END IF;
1031
1032 l_prec_sls_crdt_id := sales_credit_rec.id;
1033 l_prec_sls_crdt_procsd := OKC_API.G_FALSE;
1034 END IF;
1035
1036 IF l_sales_credit_insert = OKC_API.G_TRUE THEN
1037
1038 l_sales_credit_rec.quote_header_id := p_qhr_id;
1039 l_sales_credit_rec.quote_line_id := p_qle_id;
1040
1041 l_sales_credit_rec.sales_credit_id := sales_credit_rec.sales_credit_id;
1042 l_sales_credit_rec.percent := sales_credit_rec.percent;
1043 -- l_sales_credit_rec.resource_id := sales_credit_rec.ctc_id;
1044 l_sales_credit_rec.resource_id := sales_credit_rec.resource_id;
1045 l_sales_credit_rec.sales_credit_type_id := sales_credit_rec.sales_credit_type_id1;
1046 -- l_sales_credit_rec.object_version_number := sales_credit_rec.object_version_number;
1047 l_sales_credit_rec.last_update_date := sales_credit_rec.last_update_date;
1048
1049 l_sales_credit_rec.operation_code := sales_credit_rec.operation_code;
1050
1051 END IF;
1052 END IF; -- IF sales_credit_rec.operation_code = 'UPADTE' then...
1053
1054
1055 IF l_sales_credit_insert = OKC_API.G_TRUE THEN
1056
1057 l_sales_credit_tab(x) := l_sales_credit_rec;
1058
1059 IF (l_debug = 'Y') THEN
1060 okc_util.print_trace(1,'INDEX VALUE x = '||x);
1061 okc_util.print_trace(1,'=========================================================');
1062 okc_util.print_trace(1,' ');
1063 END IF;
1064
1065 IF (l_debug = 'Y') THEN
1066 okc_util.print_trace(1,'operation code = '||l_sales_credit_tab(x).operation_code);
1067 okc_util.print_trace(1,'qte hdr id = '||l_sales_credit_tab(x).quote_header_id);
1068 okc_util.print_trace(1,'qte line id = '||l_sales_credit_tab(x).quote_line_id);
1069 okc_util.print_trace(1,'sales credit id = '||l_sales_credit_tab(x).sales_credit_id);
1070 okc_util.print_trace(1,'percent = '||l_sales_credit_tab(x).percent);
1071 okc_util.print_trace(1,'resource(ctc)id = '||l_sales_credit_tab(x).resource_id);
1072 okc_util.print_trace(1,'sls crdt typ id = '||l_sales_credit_tab(x).sales_credit_type_id);
1073 END IF;
1074 -- okc_util.print_trace(1,'obj ver # = '||l_sales_credit_tab(x).object_version_number);
1075 IF (l_debug = 'Y') THEN
1076 okc_util.print_trace(1,'last updt date = '||l_sales_credit_tab(x).last_update_date);
1077 END IF;
1078
1079
1080 IF (l_debug = 'Y') THEN
1081 okc_util.print_trace(1,' ');
1082 END IF;
1083
1084 x := x + 1;
1085 --
1086 l_k_sales_credit_tab(y).id := sales_credit_rec.id;
1087 l_k_sales_credit_tab(y).level := p_level;
1088 y := y + 1;
1089 --
1090 l_prec_sls_crdt_procsd := OKC_API.G_TRUE;
1091 END IF;
1092
1093 l_prec_sls_crdt_rec := sales_credit_rec;
1094
1095 END LOOP; --- FOR sales_credit_rec IN c_k_sales_credit
1096
1097
1098 --
1099 -- Case of a new occurance of an existing sales credit, not processed before because
1100 -- the related quote sales credits have already been marked to be processed.
1101
1102 IF l_prec_sls_crdt_rec.operation_code = g_aso_op_code_update AND
1103 l_prec_sls_crdt_id IS NOT NULL AND l_prec_sls_crdt_procsd = OKC_API.G_FALSE THEN
1104 -- populate l_sales_credit_rec with infomation from l_prec_sls_crdt_rec;
1105
1106 l_sales_credit_rec.sales_credit_id := l_prec_sls_crdt_rec.sales_credit_id;
1107 l_sales_credit_rec.percent := l_prec_sls_crdt_rec.percent;
1108 -- l_sales_credit_rec.resource_id := l_prec_sls_crdt_rec.ctc_id;
1109 l_sales_credit_rec.resource_id := l_prec_sls_crdt_rec.resource_id;
1110 l_sales_credit_rec.sales_credit_type_id := l_prec_sls_crdt_rec.sales_credit_type_id1;
1111 -- l_sales_credit_rec.object_version_number := l_prec_sls_crdt_rec.object_version_number;
1112 l_sales_credit_rec.last_update_date := l_prec_sls_crdt_rec.last_update_date;
1113
1114 l_sales_credit_rec.operation_code := g_aso_op_code_create;
1115 l_sales_credit_rec.quote_header_id := p_qhr_id;
1116 l_sales_credit_rec.quote_line_id := p_qle_id;
1117 l_sales_credit_rec.sales_credit_id := OKC_API.G_MISS_NUM;
1118 --
1119 l_sales_credit_tab(x) := l_sales_credit_rec;
1120 x:= x+1;
1121 --
1122 l_k_sales_credit_tab(y).id := l_prec_sls_crdt_rec.id;
1123 l_k_sales_credit_tab(y).level := p_level;
1124 y:=y+1;
1125
1126 l_prec_sls_crdt_procsd := OKC_API.G_TRUE;
1127 END IF;
1128 --
1129 --
1130 -- Fill in the l_sales_credit_tab variable with sales credit to be deleted.
1131 --
1132
1133 FOR l_sales_credit IN c_sales_credit(p_q_flag,p_qhr_id, p_qle_id,
1134 p_o_flag,p_ohr_id,p_ole_id) LOOP
1135
1136 l_sales_credit_insert := OKC_API.G_TRUE;
1137
1138 --
1139 -- Need to check if the related quote sales credit is not already planned to be updated
1140 -- in the l_sales_credit_tab_variable
1141 --
1142 IF l_sales_credit_tab.FIRST IS NOT NULL THEN
1143 FOR i IN l_sales_credit_tab.first..l_sales_credit_tab.last LOOP
1144 IF l_sales_credit_tab(i).sales_credit_id = l_sales_credit.sales_credit_id THEN
1145 l_sales_credit_insert := OKC_API.G_FALSE;
1146 exit;
1147 END IF;
1148 END LOOP;
1149 END IF;
1150
1151 IF l_sales_credit_insert = OKC_API.G_TRUE THEN
1152 -- populate l_sales_credit_rec with information from l_sales_credit
1153
1154 l_sales_credit_rec.operation_code := g_aso_op_code_delete;
1155 l_sales_credit_rec.quote_header_id := p_qhr_id;
1156 l_sales_credit_rec.quote_line_id := p_qle_id;
1157
1158 l_sales_credit_rec.sales_credit_id := l_sales_credit.sales_credit_id;
1159 --
1160 l_sales_credit_tab(x) := l_sales_credit_rec;
1161 x:=x+1;
1162 END IF;
1163 END LOOP;
1164
1165
1166 IF (l_debug = 'Y') THEN
1167 okc_util.print_trace(1,' ');
1168 okc_util.print_trace(1,'-----------------------------------------');
1169 okc_util.print_trace(1,' values contained in the l_sales_credit_tab ');
1170 okc_util.print_trace(1,'-----------------------------------------');
1171 okc_util.print_trace(1,' ');
1172 END IF;
1173
1174 IF l_sales_credit_tab.first IS NOT NULL THEN
1175 FOR i IN l_sales_credit_tab.first..l_sales_credit_tab.last LOOP
1176 IF l_sales_credit_tab.EXISTS(i) THEN
1177
1178 IF (l_debug = 'Y') THEN
1179 okc_util.print_trace(1,' ');
1180 END IF;
1181
1182 IF (l_debug = 'Y') THEN
1183 okc_util.print_trace(1,'index value = '||i);
1184 okc_util.print_trace(1,'operation code = '||l_sales_credit_tab(i).operation_code);
1185 okc_util.print_trace(1,'qte hdr id = '||l_sales_credit_tab(i).quote_header_id);
1186 okc_util.print_trace(1,'qte line id = '||l_sales_credit_tab(i).quote_line_id);
1187 okc_util.print_trace(1,'sales credit id = '||l_sales_credit_tab(i).sales_credit_id);
1188 okc_util.print_trace(1,'percent = '||l_sales_credit_tab(i).percent);
1189 okc_util.print_trace(1,'resource(ctc)id = '||l_sales_credit_tab(i).resource_id);
1190 okc_util.print_trace(1,'sls crdt typ id = '||l_sales_credit_tab(i).sales_credit_type_id);
1191 END IF;
1192 -- okc_util.print_trace(1,'obj ver # = '||l_sales_credit_tab(i).object_version_number);
1193 IF (l_debug = 'Y') THEN
1194 okc_util.print_trace(1,'last updt date = '||l_sales_credit_tab(i).last_update_date);
1195 END IF;
1196
1197 IF (l_debug = 'Y') THEN
1198 okc_util.print_trace(1,' ');
1199 END IF;
1200 END IF;
1201 END LOOP;
1202 END IF;
1203
1204
1205
1206 IF l_k_sales_credit_tab.count > 0 THEN
1207 FOR i IN l_k_sales_credit_tab.FIRST..l_k_sales_credit_tab.LAST LOOP
1208 x_k_sales_credit_tab(x_k_sales_credit_tab.COUNT+1) := l_k_sales_credit_tab(i);
1209 END LOOP;
1210 END IF;
1211
1212 IF l_sales_credit_tab.COUNT > 0 THEN
1213 FOR i IN l_sales_credit_tab.FIRST..l_sales_credit_tab.LAST LOOP
1214 x_sales_credit_tab(x_sales_credit_tab.COUNT+1) := l_sales_credit_tab(i);
1215 END LOOP;
1216 END IF;
1217
1218
1219 IF (l_debug = 'Y') THEN
1220 okc_util.print_trace(1,' ');
1221 okc_util.print_trace(1,'====================================================');
1222 okc_util.print_trace(1,' FINAL VALUES CONTAINED IN THE X_SALES_CREDIT_TAB ');
1223 okc_util.print_trace(1,'====================================================');
1224 okc_util.print_trace(1,' ');
1225 END IF;
1226
1227 IF x_sales_credit_tab.first IS NOT NULL THEN
1228 FOR i IN x_sales_credit_tab.first..x_sales_credit_tab.last LOOP
1229 IF x_sales_credit_tab.EXISTS(i) THEN
1230
1231 IF (l_debug = 'Y') THEN
1232 okc_util.print_trace(1,' ');
1233 okc_util.print_trace(1,'index value = '||i);
1234 okc_util.print_trace(1,'operation code = '||l_sales_credit_tab(i).operation_code);
1235 okc_util.print_trace(1,'qte hdr id = '||l_sales_credit_tab(i).quote_header_id);
1236 okc_util.print_trace(1,'qte line id = '||l_sales_credit_tab(i).quote_line_id);
1237 okc_util.print_trace(1,'sales credit id = '||l_sales_credit_tab(i).sales_credit_id);
1238 okc_util.print_trace(1,'percent = '||l_sales_credit_tab(i).percent);
1239 okc_util.print_trace(1,'resource(ctc)id = '||l_sales_credit_tab(i).resource_id);
1240 okc_util.print_trace(1,'sls crdt typ id = '||l_sales_credit_tab(i).sales_credit_type_id);
1241 END IF;
1242 -- okc_util.print_trace(1,'obj ver # = '||l_sales_credit_tab(i).object_version_number);
1243 IF (l_debug = 'Y') THEN
1244 okc_util.print_trace(1,'last updt date = '||l_sales_credit_tab(i).last_update_date);
1245 okc_util.print_trace(1,' ');
1246 END IF;
1247
1248 END IF;
1249 END LOOP;
1250 END IF;
1251
1252 -- x_k_sales_credit_tab := l_k_sales_credit_tab;
1253 -- x_sales_credit_tab := l_sales_credit_tab;
1254
1255 IF (l_debug = 'Y') THEN
1256 okc_util.print_trace(1,' ');
1257 okc_util.print_trace(1,'====================================================');
1258 okc_util.print_trace(1,' FINAL VALUES CONTAINED IN THE X_K_SALES_CREDIT_TAB ');
1259 okc_util.print_trace(1,'====================================================');
1260 okc_util.print_trace(1,' ');
1261 END IF;
1262
1263 IF x_k_sales_credit_tab.first IS NOT NULL THEN
1264 FOR i IN x_k_sales_credit_tab.first..x_k_sales_credit_tab.last LOOP
1265 IF x_k_sales_credit_tab.EXISTS(i) THEN
1266
1267 IF (l_debug = 'Y') THEN
1268 okc_util.print_trace(1,'INDEX VALUE = '||i);
1269 okc_util.print_trace(1,'okc_k_sales_credits - id = '||x_k_sales_credit_tab(i).id);
1270 okc_util.print_trace(1,'Level = '||x_k_sales_credit_tab(i).level);
1271 END IF;
1272
1273 END IF;
1274
1275 END LOOP;
1276 END IF;
1277
1278 IF (l_debug = 'Y') THEN
1279 okc_util.print_trace(1,'------------------------------');
1280 okc_util.print_trace(1,'>>END : Get sales credits ');
1281 okc_util.print_trace(1,'------------------------------');
1282 END IF;
1283
1284 EXCEPTION
1285 WHEN OTHERS THEN
1286 IF (l_debug = 'Y') THEN
1287 okc_util.print_trace(1,'inside get sales credit : others exception');
1288 END IF;
1289
1290 IF c_k_sales_credit%ISOPEN THEN
1291 CLOSE c_k_sales_credit;
1292 END IF;
1293 IF c_sales_credit%ISOPEN THEN
1294 CLOSE c_sales_credit;
1295 END IF;
1296
1297 RAISE;
1298
1299 END; -- get_sales_credit
1300
1301
1302 -- ========================================
1303 --
1304 -- procedure build_sales_credit_from_k
1305 --
1306 -- ========================================
1307
1308 PROCEDURE build_sales_credit_from_k(
1309 p_chr_id IN OKC_K_HEADERS_B.id%TYPE,
1310 p_kl_rel_tab IN okc_oc_int_config_pvt.line_rel_tab_type
1311 ,
1312 --
1313 p_q_flag IN VARCHAR2 ,
1314 p_qhr_id IN OKX_QUOTE_HEADERS_V.id1%TYPE ,
1315 p_qle_tab IN ASO_QUOTE_PUB.qte_line_tbl_type ,
1316 --
1317 p_o_flag IN VARCHAR2 ,
1318 p_ohr_id IN OKX_ORDER_HEADERS_V.id1%TYPE ,
1319 p_ole_tab IN ASO_QUOTE_PUB.qte_line_tbl_type ,
1320 --
1321 x_hd_sales_credit_tab OUT NOCOPY ASO_QUOTE_PUB.sales_credit_tbl_type,
1322 x_ln_sales_credit_tab OUT NOCOPY ASO_QUOTE_PUB.sales_credit_tbl_type,
1323 --
1324 x_return_status OUT NOCOPY VARCHAR2 ) IS
1325
1326 k BINARY_INTEGER;
1327 x_ln_temp_sls_crdt_tab ASO_QUOTE_PUB.sales_credit_tbl_type;
1328 l_cle_id okc_k_lines_b.id%TYPE;
1329
1330 BEGIN
1331
1332 --
1333 -- housekeeping
1334 --
1335
1336 x_hd_sales_credit_tab.DELETE;
1337 x_ln_sales_credit_tab.DELETE;
1338
1339 x_ln_temp_sls_crdt_tab.DELETE;
1340 l_k_sales_credit_tab.DELETE;
1341 l_line_tab.DELETE;
1342
1343
1344 IF p_q_flag = OKC_API.g_true THEN
1345 l_line_tab:=p_qle_tab;
1346 ELSIF p_o_flag = OKC_API.g_true THEN
1347 l_line_tab:=p_ole_tab;
1348 END IF;
1349
1350 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1351
1352 ---------------------------------------------------------------
1353 -- Select sales credit information at the contract header level
1354 ---------------------------------------------------------------
1355
1356 --
1357 -- Get the sales credit information into the x_hd_sales_credit_tab variable
1358 --
1359 IF (l_debug = 'Y') THEN
1360 okc_util.print_trace(1,'=============================================');
1361 okc_util.print_trace(1,'START : GET SALES CREDITS AT HEADER LEVEL ');
1362 okc_util.print_trace(1,'=============================================');
1363 END IF;
1364
1365 get_sales_credit(p_chr_id => p_chr_id,
1366 p_cle_id => NULL,
1367 --
1368 p_qhr_id => p_qhr_id,
1369 p_qle_id => NULL,
1370 p_q_flag => p_q_flag,
1371 --
1372 p_ohr_id => p_ohr_id,
1373 p_ole_id => NULL,
1374 p_o_flag => p_o_flag,
1375 --
1376 p_level => 'H',
1377 --
1378 p_nqhr_id => NULL,
1379 p_nqle_idx => NULL,
1380 --
1381 x_k_sales_credit_tab => l_k_sales_credit_tab,
1382 x_sales_credit_tab => x_hd_sales_credit_tab );
1383
1384 IF (l_debug = 'Y') THEN
1385 okc_util.print_trace(1,'===========================================');
1386 okc_util.print_trace(1,' END : GET SALES CREDITS AT HEADER LEVEL ');
1387 okc_util.print_trace(1,'===========================================');
1388 END IF;
1389
1390
1391 --------------------------------------------------------------
1392 -- Select sales credit information at the contract Line level
1393 --------------------------------------------------------------
1394
1395 --
1396 -- Select sales credit information into the x_ln_sales_credit_tab variable
1397 --
1398
1399 IF (l_debug = 'Y') THEN
1400 okc_util.print_trace(1,'===========================================');
1401 okc_util.print_trace(1,'START : GET SALES CREDITS AT LINE LEVEL ');
1402 okc_util.print_trace(1,'===========================================');
1403 END IF;
1404
1405 IF l_line_tab.FIRST IS NOT NULL THEN
1406
1407 FOR i IN l_line_tab.FIRST..l_line_tab.LAST LOOP
1408
1409 --
1410 -- Need to ensure that the Top model line is processed for the configuration item,
1411 -- since the sales credit information is stored at the Top model line level
1412 --
1413 -- Also the index value i is the same in l_line_tab and p_kl_rel_tab because
1414 -- when the quote line table was populated px_k2q_line_id(l_ql).q_line_idx := l_ql
1415 -- the value of l_ql,q_line_idx are the same
1416 --
1417
1418 IF p_kl_rel_tab(i).q_item_type_code = g_aso_model_item THEN -- MDL
1419 l_cle_id := p_kl_rel_tab(i).k_parent_line_id;
1420 ELSE
1421 l_cle_id := p_kl_rel_tab(i).k_line_id;
1422 END IF;
1423
1424
1425 IF l_line_tab(i).operation_code= g_aso_op_code_create THEN
1426
1427 -- okc_util.print_trace(1,'operation code '||l_line_tab(i).operation_code);
1428
1429 get_sales_credit(p_chr_id => p_chr_id,
1430 p_cle_id => l_cle_id,
1431 --
1432 p_qhr_id => NULL,
1433 p_qle_id => NULL,
1434 p_q_flag => p_q_flag,
1435 --
1436 p_ohr_id => NULL,
1437 p_ole_id => NULL,
1438 p_o_flag => p_o_flag,
1439 --
1440 p_level =>'L',
1441 --
1442 p_nqhr_id => p_qhr_id,
1443 p_nqle_idx => i,
1444 --
1445 x_k_sales_credit_tab => l_k_sales_credit_tab,
1446 x_sales_credit_tab => x_ln_temp_sls_crdt_tab);
1447
1448 ELSIF l_line_tab(i).operation_code= g_aso_op_code_update THEN
1449
1450 -- okc_util.print_trace(1,'operation code '||l_line_tab(i).operation_code);
1451
1452 get_sales_credit(p_chr_id => p_chr_id,
1453 p_cle_id => l_cle_id,
1454 --
1455 p_qhr_id => p_qhr_id,
1456 p_qle_id => l_line_tab(i).quote_line_id,
1457 p_q_flag => p_q_flag,
1458 --
1459 p_ohr_id => p_ohr_id,
1460 p_ole_id => l_line_tab(i).quote_line_id,
1461 --Not valid now in case of an Order update from a contract
1462 --Will need to be modified when K -> O for update will be
1463 --required to be developed
1464
1465 p_o_flag => p_o_flag,
1466 --
1467 p_level =>'L',
1468 --
1469 p_nqhr_id => NULL,
1470 p_nqle_idx => NULL,
1471 --
1472 x_k_sales_credit_tab => l_k_sales_credit_tab,
1473 x_sales_credit_tab => x_ln_temp_sls_crdt_tab);
1474
1475 ELSIF l_line_tab(i).operation_code= g_aso_op_code_delete THEN
1476
1477 -- okc_util.print_trace(1,'operation code '||l_line_tab(i).operation_code);
1478
1479 get_sales_credit(p_chr_id => NULL,
1480 p_cle_id => NULL,
1481 --
1482 p_qhr_id => p_qhr_id,
1483 p_qle_id => l_line_tab(i).quote_line_id,
1484 p_q_flag => p_q_flag,
1485 --
1486 p_ohr_id => p_ohr_id,
1487 p_ole_id => l_line_tab(i).quote_line_id,
1488 --Not valid now in case of an Order update from a contract
1489 --Will need to be modified when K -> O for update will be
1490 --required to be developed
1491
1492 p_o_flag => p_o_flag,
1493 --
1494 p_level =>'L',
1495 --
1496 p_nqhr_id => NULL,
1497 p_nqle_idx => NULL,
1498 --
1499 x_k_sales_credit_tab => l_k_sales_credit_tab,
1500 x_sales_credit_tab => x_ln_temp_sls_crdt_tab);
1501 END IF;
1502
1503 IF x_ln_temp_sls_crdt_tab.COUNT > 0 THEN
1504 FOR k in x_ln_temp_sls_crdt_tab.first..x_ln_temp_sls_crdt_tab.last LOOP
1505 x_ln_sales_credit_tab(x_ln_sales_credit_tab.COUNT+1) := x_ln_temp_sls_crdt_tab(k);
1506 END LOOP;
1507 END IF;
1508
1509 END LOOP;
1510 END IF;
1511
1512 IF (l_debug = 'Y') THEN
1513 okc_util.print_trace(1,'===========================================');
1514 okc_util.print_trace(1,' END : GET SALES CREDITS AT LINE LEVEL ');
1515 okc_util.print_trace(1,'===========================================');
1516 END IF;
1517
1518 EXCEPTION
1519
1520 WHEN OTHERS THEN
1521 OKC_API.set_message(G_APP_NAME,
1522 G_UNEXPECTED_ERROR,
1523 G_SQLCODE_TOKEN,
1524 SQLCODE,
1525 G_SQLERRM_TOKEN,
1526 SQLERRM );
1527 print_error(2);
1528
1529 -- notify caller of an unexpected error
1530
1531 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1532
1533 END build_sales_credit_from_k;
1534
1535 -- ========================================================================
1536 -- END OF KTQ or KTO SALES CREDIT INFORMATION CREATION
1537 -- or UPDATE
1538 -- ========================================================================
1539
1540
1541
1542 ----------------------------------------------------------------------
1543 --
1544 -- Function get_party_name to retrieve a party name against -------
1545 -- the jtot_object1_code and object1_id1 -------
1546 --
1547 -- This is a general function that can be used anywhere to retrieve
1548 -- the party name for eg. from the OKCSLCRD.fmb sales credit form
1549 FUNCTION get_party_name (p_object1_id1 varchar2, p_jtot_object1_code varchar2) RETURN VARCHAR2 IS
1550 l_sql_stmt VARCHAR2(10000);
1551 l_from_clause varchar2(200);
1552 l_where_clause varchar2(2000);
1553 l_order_by_clause varchar2(200);
1554 l_party_name varchar2(500);
1555
1556 l_cursor_id INTEGER;
1557 l_dummy INTEGER;
1558
1559 BEGIN
1560
1561 l_cursor_id := DBMS_SQL.OPEN_CURSOR;
1562
1563 l_sql_stmt := 'SELECT FROM_TABLE, WHERE_CLAUSE, ORDER_BY_CLAUSE ';
1564 l_sql_stmt := l_sql_stmt || ' FROM jtf_objects_b';
1565 l_sql_stmt := l_sql_stmt || ' WHERE OBJECT_CODE = :jtot_object1_code';
1566
1567 DBMS_SQL.PARSE(l_cursor_id, l_sql_stmt, 2);
1568 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':jtot_object1_code', p_jtot_object1_code);
1569
1570 DBMS_SQL.DEFINE_COLUMN(l_cursor_id, 1, l_from_clause, 200);
1571 DBMS_SQL.DEFINE_COLUMN(l_cursor_id, 2, l_where_clause, 2000);
1572 DBMS_SQL.DEFINE_COLUMN(l_cursor_id, 3, l_order_by_clause, 200);
1573
1574 l_dummy := DBMS_SQL.EXECUTE(l_cursor_id);
1575 IF DBMS_SQL.FETCH_ROWS(l_cursor_id) = 0 THEN
1576 RAISE OKC_API.G_EXCEPTION_ERROR;
1577 END IF;
1578
1579 DBMS_SQL.COLUMN_VALUE(l_cursor_id, 1, l_from_clause);
1580 DBMS_SQL.COLUMN_VALUE(l_cursor_id, 2, l_where_clause);
1581 DBMS_SQL.COLUMN_VALUE(l_cursor_id, 3, l_order_by_clause);
1582
1583 DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
1584
1585
1586
1587
1588 l_cursor_id := DBMS_SQL.OPEN_CURSOR;
1589
1590 l_sql_stmt := 'SELECT NAME FROM ' || l_from_clause;
1591 l_sql_stmt := l_sql_stmt || ' WHERE ' || l_where_clause;
1592 l_sql_stmt := l_sql_stmt || ' AND ID1 = :object1_id';
1593 l_sql_stmt := l_sql_stmt || ' ORDER BY ' || l_order_by_clause;
1594
1595 DBMS_SQL.PARSE(l_cursor_id, l_sql_stmt, 2);
1596 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':object1_id', p_object1_id1);
1597
1598 DBMS_SQL.DEFINE_COLUMN(l_cursor_id, 1, l_party_name, 500);
1599
1600 l_dummy := DBMS_SQL.EXECUTE(l_cursor_id);
1601 IF DBMS_SQL.FETCH_ROWS(l_cursor_id) = 0 THEN
1602 RAISE OKC_API.G_EXCEPTION_ERROR;
1603 END IF;
1604
1605 DBMS_SQL.COLUMN_VALUE(l_cursor_id, 1, l_party_name);
1606 DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
1607
1608
1609 RETURN(l_party_name);
1610 EXCEPTION
1611 WHEN OTHERS THEN
1612 OKC_API.set_message(G_APP_NAME,
1613 G_UNEXPECTED_ERROR,
1614 G_SQLCODE_TOKEN,
1615 SQLCODE,
1616 G_SQLERRM_TOKEN,
1617 SQLERRM );
1618 print_error(2);
1619 END;
1620
1621
1622
1623
1624
1625 END OKC_OC_INT_SALESCDT_PVT ;
[Home] [Help]