[Home] [Help]
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
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:
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;
139: okc_util.print_trace(1, '
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,
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:
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:
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;
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
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);
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);
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
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);
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;
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
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;
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;
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;
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:
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;
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
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:
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;
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
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;
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:
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');
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');
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,
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,
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:
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
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:
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
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:
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
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
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
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);
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
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);
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;
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 :');
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;
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;
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;
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;
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- ');
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:
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
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: --
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
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
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:
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;
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:
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:
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;
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
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
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
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;
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
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;
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;
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:
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:
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
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:
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;
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.
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;
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;
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;
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;
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: ---------------------------------------------------------------
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,
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: -- ========================================================================
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);
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);
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,