APPS.OKC_OC_INT_SALESCDT_PVT SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 60

  SELECT contract_number
  FROM   okc_k_headers_b
  WHERE  id = c_k_id;

Line: 67

  SELECT TO_CHAR(order_number)
  FROM   okx_order_headers_v
  WHERE  id1 = c_o_id;

Line: 74

  SELECT TO_CHAR(quote_number)
  FROM   okx_quote_headers_v
  WHERE  id1 = c_q_id;

Line: 134

     g_sales_credit_tab.DELETE;

Line: 135

     lx_sales_credit_tab.DELETE;

Line: 514

      SELECT a.quote_header_id source_header_id,
       a.quote_line_id source_line_id,
       a.percent,
       b.id1 salesrep_id,
       a.resource_group_id,        --no matching column in OKC
       -----employee_person_id,  --obsolete column in ASO replace by resource id
       a.sales_credit_type_id,
       a.attribute_category_code,   --new column needed in OKC
       a.object_version_number
      FROM   okx_qte_sls_credits_v a
            ,okx_salesreps_v       b
      WHERE  cp_q_flag = OKC_API.G_TRUE
        AND  a.quote_header_id = cp_qhr_id
        AND   ((cp_qle_id = OKC_API.G_MISS_NUM AND a.quote_line_id IS NULL) OR
               (cp_qle_id <> OKC_API.G_MISS_NUM AND a.quote_line_id = cp_qle_id))
        AND  a.resource_id = b.resource_id
        AND  b.org_id = SYS_CONTEXT('OKC_CONTEXT', 'ORG_ID')

      UNION ALL

      -- second query to get okx_ord_sls_credits_v information
      SELECT header_id source_header_id,
       line_id source_line_id,
       percent,
       salesrep_id,
       -------sales_credit_type_id,
       TO_NUMBER(NULL),
       sales_credit_type_id,
       TO_CHAR(NULL),
       TO_NUMBER(NULL)    --object_version_number not present in order table
      FROM   okx_ord_sls_credits_v
      WHERE  cp_o_flag = OKC_API.G_TRUE
        AND  header_id = cp_ohr_id
        AND   ((cp_ole_id = OKC_API.G_MISS_NUM AND line_id IS NULL) OR
               (cp_ole_id <> OKC_API.G_MISS_NUM AND line_id = cp_ole_id));

Line: 552

      SELECT id
      FROM   okc_contacts
      WHERE  dnz_chr_id = b_chr_id
        AND  cro_code = b_salesrep_ctrol
        AND  object1_id1=b_object_id
        AND  rownum = 1;

Line: 632

                 SELECT id into  l_supplier_role_id FROM OKC_K_PARTY_ROLES_B
                                                    WHERE dnz_chr_id = p_chr_id
                                                    AND   rle_code = 'SUPPLIER';

Line: 770

SELECT
	qscdt.sales_credit_id SALES_CREDIT_ID  -- quote (header or line) sales credit ID
FROM
	OKX_QTE_SLS_CREDITS_V    qscdt
WHERE 	b_q_flag = OKC_API.g_true
AND 	qscdt.quote_header_id = b_qh_id
AND 	((b_ql_id IS NULL AND qscdt.quote_line_id IS NULL)
		OR (b_ql_id IS NOT NULL AND qscdt.quote_line_id = b_ql_id))
UNION

SELECT
	oscdt.sales_credit_id SALES_CREDIT_ID  -- order (header or line) sales credit ID
FROM
	OKX_ORD_SLS_CREDITS_V    oscdt
WHERE 	b_o_flag = OKC_API.g_true
AND 	oscdt.header_id = b_oh_id
AND 	((b_ol_id IS NULL AND oscdt.line_id IS NULL)
		OR (b_ol_id IS NOT NULL AND oscdt.line_id = b_ql_id));

Line: 793

    SELECT
        DECODE(qscdt.resource_id,NULL,g_aso_op_code_create,
                DECODE(qscdt.sales_credit_type_id,NULL,g_aso_op_code_create,
                        DECODE(qscdt.percent,NULL,g_aso_op_code_create,g_aso_op_code_update)
                       )
               ) OPERATION_CODE,
	qscdt.sales_credit_id   sales_credit_id,  -- quote(Header or line) sales credit ID
--	kscdt.ctc_id,
	sr.resource_id,
	kscdt.sales_credit_type_id1,
	kscdt.percent,
	kscdt.id,				  -- contract (Header or line) sales credit ID
	kscdt.creation_date,
	kscdt.chr_id,
	kscdt.cle_id,
        kscdt.last_update_date
--	kscdt.object_version_number
     FROM
	OKC_K_SALES_CREDITS          kscdt,
	OKX_QTE_SLS_CREDITS_V        qscdt,
	OKX_SALESREPS_V		     sr
     WHERE
	    b_q_flag = OKC_API.G_TRUE
	AND kscdt.chr_id = b_kh_id
	AND  ((b_kl_id IS NULL AND kscdt.cle_id IS NULL)
			OR (b_kl_id IS NOT NULL AND kscdt.cle_id = b_kl_id))
	AND qscdt.quote_header_id(+)  = b_qh_id
	AND NVL(qscdt. Quote_line_id(+), 0) = NVL(b_ql_id, 0)
--	AND qscdt.resource_id(+)= kscdt.ctc_id
	AND qscdt.sales_credit_type_id (+)= kscdt.sales_credit_type_id1
	AND qscdt.percent(+)= kscdt.percent
	AND sr.id1 = kscdt.salesrep_id1
	AND sr.id2 = kscdt.salesrep_id2

    UNION

    SELECT
        DECODE(oscdt.salesrep_id,NULL,g_aso_op_code_create,
                DECODE(oscdt.sales_credit_type_id,NULL,g_aso_op_code_create,
                        DECODE(oscdt.percent,NULL,g_aso_op_code_create,g_aso_op_code_update)
                       )
               ) OPERATION_CODE,
	oscdt.sales_credit_id   sales_credit_id,  -- order(Header or line) sales credit ID
--	kscdt.ctc_id,
--	sr.resource_id,
	to_number(kscdt.salesrep_id1) resource_id,
	kscdt.sales_credit_type_id1,
	kscdt.percent,
	kscdt.id,				  -- contract (Header or line) sales credit ID
	kscdt.creation_date,
	kscdt.chr_id,
	kscdt.cle_id,
	kscdt.last_update_date
--	kscdt.object_version_number
     FROM
	OKC_K_SALES_CREDITS          kscdt,
	OKX_ORD_SLS_CREDITS_V        oscdt
--	OKX_SALESREPS_V		     sr
     WHERE
	    b_o_flag = OKC_API.G_TRUE
	AND kscdt.chr_id = b_kh_id
	AND  ((b_kl_id IS NULL AND kscdt.cle_id IS NULL)
			OR (b_kl_id IS NOT NULL AND kscdt.cle_id = b_kl_id))
	AND oscdt.header_id(+)  = b_oh_id
	AND NVL(oscdt.line_id(+), 0) = NVL(b_ol_id, 0)
--	AND oscdt.salesrep_id(+)= kscdt.ctc_id
	AND oscdt.sales_credit_type_id (+)= kscdt.sales_credit_type_id1
	AND oscdt.percent(+)= kscdt.percent
--	AND sr.id1 = kscdt.salesrep_id1
--	AND sr.id2 = kscdt.salesrep_id2
ORDER BY
	1,
	3,
	4,
	5,
	6,
	7 ;

Line: 875

l_sales_credit_insert		VARCHAR2(1) := OKC_API.G_TRUE;

Line: 895

 l_sales_credit_tab.DELETE;

Line: 896

 l_k_sales_credit_tab.DELETE;

Line: 898

 x_sales_credit_tab.DELETE;

Line: 899

 x_k_sales_credit_tab.DELETE;

Line: 920

l_sales_credit_insert := OKC_API.G_TRUE;

Line: 942

	l_sales_credit_rec.last_update_date		:= sales_credit_rec.last_update_date;

Line: 955

	IF sales_credit_rec.operation_code = g_aso_op_code_update THEN
		IF (l_debug = 'Y') THEN
   		okc_util.print_trace(1,'step 1-2 operation code = '||sales_credit_rec.operation_code);

Line: 963

	-- updated in the l_sales_credit_tab variable
	--
	   IF l_sales_credit_tab.first IS NOT NULL THEN
		FOR i IN l_sales_credit_tab.first..l_sales_credit_tab.last LOOP
		   IF l_sales_credit_tab(i).sales_credit_id = sales_credit_rec.sales_credit_id THEN

			IF (l_debug = 'Y') THEN
   			okc_util.print_trace(1,'step 1-3 related sales credit adjustment is already planned to be updated');

Line: 973

			l_sales_credit_insert := OKC_API.G_FALSE;

Line: 982

	-- not already planned to be updated in the l_sales_credit_tab variable.
	--
	   IF l_prec_sls_crdt_procsd = OKC_API.G_TRUE THEN
		l_sales_credit_insert  := OKC_API.G_FALSE;

Line: 991

			 l_sales_credit_insert := OKC_API.G_FALSE;

Line: 1014

	l_sales_credit_rec.last_update_date		:= l_prec_sls_crdt_rec.last_update_date;

Line: 1036

   IF l_sales_credit_insert = OKC_API.G_TRUE THEN

	l_sales_credit_rec.quote_header_id := p_qhr_id;

Line: 1047

	l_sales_credit_rec.last_update_date		:= sales_credit_rec.last_update_date;

Line: 1055

   IF l_sales_credit_insert = OKC_API.G_TRUE THEN

	l_sales_credit_tab(x) := l_sales_credit_rec;

Line: 1076

   okc_util.print_trace(1,'last updt date  = '||l_sales_credit_tab(x).last_update_date);

Line: 1102

IF  l_prec_sls_crdt_rec.operation_code = g_aso_op_code_update AND
	l_prec_sls_crdt_id IS NOT NULL AND l_prec_sls_crdt_procsd = OKC_API.G_FALSE THEN
-- populate l_sales_credit_rec with infomation from l_prec_sls_crdt_rec;

Line: 1112

	l_sales_credit_rec.last_update_date		:= l_prec_sls_crdt_rec.last_update_date;

Line: 1136

 l_sales_credit_insert := OKC_API.G_TRUE;

Line: 1145

			l_sales_credit_insert := OKC_API.G_FALSE;

Line: 1151

	IF l_sales_credit_insert = OKC_API.G_TRUE THEN
-- populate l_sales_credit_rec with information from l_sales_credit

		l_sales_credit_rec.operation_code := g_aso_op_code_delete;

Line: 1194

   okc_util.print_trace(1,'last updt date  = '||l_sales_credit_tab(i).last_update_date);

Line: 1244

   okc_util.print_trace(1,'last updt date  = '||l_sales_credit_tab(i).last_update_date);

Line: 1336

  x_hd_sales_credit_tab.DELETE;

Line: 1337

  x_ln_sales_credit_tab.DELETE;

Line: 1339

  x_ln_temp_sls_crdt_tab.DELETE;

Line: 1340

  l_k_sales_credit_tab.DELETE;

Line: 1341

  l_line_tab.DELETE;

Line: 1448

	  ELSIF l_line_tab(i).operation_code= g_aso_op_code_update THEN

--		okc_util.print_trace(1,'operation code '||l_line_tab(i).operation_code);

Line: 1461

						--Not valid now in case of an Order update  from a contract
						--Will need to be modified when K -> O for update will be
						--required to be developed

			    	    p_o_flag => p_o_flag,
				--
				    p_level   =>'L',
				--
				    p_nqhr_id => NULL,
				    p_nqle_idx => NULL,
				--
				    x_k_sales_credit_tab   => l_k_sales_credit_tab,
				    x_sales_credit_tab     => x_ln_temp_sls_crdt_tab);

Line: 1475

	  ELSIF l_line_tab(i).operation_code= g_aso_op_code_delete THEN

--		okc_util.print_trace(1,'operation code '||l_line_tab(i).operation_code);

Line: 1488

						--Not valid now in case of an Order update  from a contract
						--Will need to be modified when K -> O for update will be
						--required to be developed

			    	    p_o_flag => p_o_flag,
				--
				    p_level   =>'L',
				--
				    p_nqhr_id => NULL,
				    p_nqle_idx => NULL,
				--
				    x_k_sales_credit_tab   => l_k_sales_credit_tab,
				    x_sales_credit_tab     => x_ln_temp_sls_crdt_tab);

Line: 1563

   l_sql_stmt := 'SELECT FROM_TABLE, WHERE_CLAUSE, ORDER_BY_CLAUSE ';

Line: 1590

   l_sql_stmt := 'SELECT NAME FROM ' || l_from_clause;