DBA Data[Home] [Help]

APPS.OKC_XPRT_IMPORT_PVT SQL Statements

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

Line: 43

SELECT 'X'
FROM cz_rules
WHERE rule_type = 200 -- Statement rule
--AND devl_project_id = p_devl_project_id
AND rule_id = persistent_rule_id
AND SUBSTR(orig_sys_ref,INSTR(orig_sys_ref,':',-1,1)+1) = to_char(p_rule_id) --For Bug# 8240959
AND deleted_flag = 0;
Line: 100

SELECT template_name,
       DECODE(parent_template_id, NULL, template_id, parent_template_id),
       intent,
       name,
       org_id
FROM okc_terms_templates_all,
     hr_operating_units
WHERE organization_id = org_id
  AND template_id = p_template_id ;
Line: 178

  UPDATE okc_terms_templates_all
	SET    xprt_request_id = FND_GLOBAL.CONC_REQUEST_ID,
         last_update_login = FND_GLOBAL.LOGIN_ID,
		     last_update_date = SYSDATE,
		     last_updated_by = FND_GLOBAL.USER_ID
	WHERE template_id = p_template_id;
Line: 285

SELECT DISTINCT to_char(r.template_id)
FROM   okc_terms_templates_all t,
       okc_xprt_template_rules r,
       okc_xprt_rule_hdrs_all h
WHERE r.template_id = t.template_id
AND   r.rule_id = h.rule_id
AND   t.status_code IN ('APPROVED','ON_HOLD')
AND   h.request_id = FND_GLOBAL.CONC_REQUEST_ID;
Line: 297

SELECT t.template_id
FROM   okc_terms_templates_all t
WHERE  t.org_id = p_org_id
AND    t.intent IN (SELECT DISTINCT intent
				            FROM okc_xprt_rule_hdrs_all
  				          WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
                   )
AND  t.contract_expert_enabled = 'Y'
AND  t.status_code IN ('APPROVED','ON_HOLD');
Line: 309

SELECT 'X'
FROM   okc_xprt_rule_hdrs_all
WHERE  request_id = FND_GLOBAL.CONC_REQUEST_ID
AND    NVL(org_wide_flag,'N') = 'Y';
Line: 316

SELECT org_id
FROM   okc_xprt_rule_hdrs_all
WHERE  request_id = FND_GLOBAL.CONC_REQUEST_ID;
Line: 500

SELECT SUBSTR(req_data,
                      1,
                      INSTR(req_data,':',1) -1
                    )  child_req_id,
       SUBSTR(req_data,
                      INSTR(req_data,':',1) + 1
                    ) run_id
FROM dual;
Line: 510

SELECT COUNT(*)
  FROM cz_imp_rules
 WHERE run_id = G_RUN_ID;
Line: 516

SELECT rule_id
  FROM okc_xprt_rule_hdrs_all
 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID;
Line: 529

SELECT rule_id
  FROM okc_xprt_rule_hdrs_all
 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID ;
Line: 536

SELECT fnd_profile.Value('OKC_USE_CONTRACTS_RULES_ENGINE') INTO l_okc_rules_engine FROM dual;
Line: 563

	        Step 1: Update current request Id for all rules to be published
	    */

         fnd_file.put_line(FND_FILE.LOG,'  ');
Line: 573

           UPDATE okc_xprt_rule_hdrs_all
              SET request_id = FND_GLOBAL.CONC_REQUEST_ID,
		        program_id = FND_GLOBAL.CONC_PROGRAM_ID,
			   program_application_id = FND_GLOBAL.PROG_APPL_ID,
			   program_update_date = SYSDATE,
			   last_update_login = FND_GLOBAL.LOGIN_ID,
			   last_update_date = SYSDATE,
			   last_updated_by = FND_GLOBAL.USER_ID
            WHERE org_id = p_org_id
		    AND intent = DECODE(NVL(fnd_profile.value('OKC_LIBRARY_ACCESS_INTENT'),'A'),'A',
		                                  intent,
								    fnd_profile.value('OKC_LIBRARY_ACCESS_INTENT')
						    )
              AND status_code = 'PENDINGPUB';
Line: 651

				UPDATE okc_xprt_rule_hdrs_all
				   SET status_code = DECODE(l_rule_exists_flag,'T','REVISION','F','DRAFT')
				 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
				   AND rule_id = csr_pub_rule_rec.rule_id;
Line: 702

		 	UPDATE okc_xprt_rule_hdrs_all
		 	   SET status_code = DECODE(l_rule_exists_flag,'T','REVISION','F','DRAFT')
		 	 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
		 	   AND rule_id = csr_pub_rule_rec.rule_id;
Line: 717

		 	UPDATE okc_xprt_rule_hdrs_all
		 	   SET status_code = DECODE(l_rule_exists_flag,'T','REVISION','F','DRAFT')
		 	 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
		 	   AND rule_id = csr_pub_rule_rec.rule_id;
Line: 761

		 	UPDATE okc_xprt_rule_hdrs_all
		 	   SET status_code = DECODE(l_rule_exists_flag,'T','REVISION','F','DRAFT')
		 	 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
		 	   AND rule_id = csr_pub_rule_rec.rule_id;
Line: 776

		 	UPDATE okc_xprt_rule_hdrs_all
		 	   SET status_code = DECODE(l_rule_exists_flag,'T','REVISION','F','DRAFT')
		 	 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
		 	   AND rule_id = csr_pub_rule_rec.rule_id;
Line: 815

		 	UPDATE okc_xprt_rule_hdrs_all
		 	   SET status_code = DECODE(l_rule_exists_flag,'T','REVISION','F','DRAFT')
		 	 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
		 	   AND rule_id = csr_pub_rule_rec.rule_id;
Line: 830

		 	UPDATE okc_xprt_rule_hdrs_all
		 	   SET status_code = DECODE(l_rule_exists_flag,'T','REVISION','F','DRAFT')
		 	 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
		 	   AND rule_id = csr_pub_rule_rec.rule_id;
Line: 872

		 	UPDATE okc_xprt_rule_hdrs_all
		 	   SET status_code = DECODE(l_rule_exists_flag,'T','REVISION','F','DRAFT')
		 	 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
		 	   AND rule_id = csr_pub_rule_rec.rule_id;
Line: 887

		 	UPDATE okc_xprt_rule_hdrs_all
		 	   SET status_code = DECODE(l_rule_exists_flag,'T','REVISION','F','DRAFT')
		 	 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
		 	   AND rule_id = csr_pub_rule_rec.rule_id;
Line: 941

		 	UPDATE okc_xprt_rule_hdrs_all
		 	   SET status_code = DECODE(l_rule_exists_flag,'T','REVISION','F','DRAFT')
		 	 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
		 	   AND rule_id = csr_pub_rule_rec.rule_id;
Line: 956

		 	UPDATE okc_xprt_rule_hdrs_all
		 	   SET status_code = DECODE(l_rule_exists_flag,'T','REVISION','F','DRAFT')
		 	 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
		 	   AND rule_id = csr_pub_rule_rec.rule_id;
Line: 979

		      Step 6: Insert Extension Rules
		   */

            fnd_file.put_line(FND_FILE.LOG,'  ');
Line: 984

            fnd_file.put_line(FND_FILE.LOG,'Step 6: Calling API to insert extension rule records');
Line: 999

            fnd_file.put_line(FND_FILE.LOG,'Step 6: After Calling API to insert extension rule records');
Line: 1042

		     		 	UPDATE okc_xprt_rule_hdrs_all
		     		 	   SET status_code = DECODE(l_rule_exists_flag,'T','REVISION','F','DRAFT')
		     		 	 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
		     		 	   AND rule_id = csr_pub_rule_rec.rule_id;
Line: 1111

	       		 	UPDATE okc_xprt_rule_hdrs_all
	       		 	   SET status_code = DECODE(l_rule_exists_flag,'T','REVISION','F','DRAFT')
	       		 	 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
	       		 	   AND rule_id = csr_pub_rule_rec.rule_id;
Line: 1154

		 	UPDATE okc_xprt_rule_hdrs_all
		 	   SET status_code = DECODE(l_rule_exists_flag,'T','REVISION','F','DRAFT')
		 	 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
		 	   AND rule_id = csr_pub_rule_rec.rule_id;
Line: 1169

		 	UPDATE okc_xprt_rule_hdrs_all
		 	   SET status_code = DECODE(l_rule_exists_flag,'T','REVISION','F','DRAFT')
		 	 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
		 	   AND rule_id = csr_pub_rule_rec.rule_id;
Line: 1209

		 	UPDATE okc_xprt_rule_hdrs_all
		 	   SET status_code = DECODE(l_rule_exists_flag,'T','REVISION','F','DRAFT')
		 	 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
		 	   AND rule_id = csr_pub_rule_rec.rule_id;
Line: 1224

		 	UPDATE okc_xprt_rule_hdrs_all
		 	   SET status_code = DECODE(l_rule_exists_flag,'T','REVISION','F','DRAFT')
		 	 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
		 	   AND rule_id = csr_pub_rule_rec.rule_id;
Line: 1264

		 	UPDATE okc_xprt_rule_hdrs_all
		 	   SET status_code = DECODE(l_rule_exists_flag,'T','REVISION','F','DRAFT')
		 	 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
		 	   AND rule_id = csr_pub_rule_rec.rule_id;
Line: 1279

		 	UPDATE okc_xprt_rule_hdrs_all
		 	   SET status_code = DECODE(l_rule_exists_flag,'T','REVISION','F','DRAFT')
		 	 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
		 	   AND rule_id = csr_pub_rule_rec.rule_id;
Line: 1319

	        Step 1: Update current request Id for all rules to be published
	    */

         fnd_file.put_line(FND_FILE.LOG,'  ');
Line: 1329

           UPDATE okc_xprt_rule_hdrs_all
              SET request_id = FND_GLOBAL.CONC_REQUEST_ID,
		        program_id = FND_GLOBAL.CONC_PROGRAM_ID,
			   program_application_id = FND_GLOBAL.PROG_APPL_ID,
			   program_update_date = SYSDATE,
			   last_update_login = FND_GLOBAL.LOGIN_ID,
			   last_update_date = SYSDATE,
			   last_updated_by = FND_GLOBAL.USER_ID
            WHERE org_id = p_org_id
		    AND intent = DECODE(NVL(fnd_profile.value('OKC_LIBRARY_ACCESS_INTENT'),'A'),'A',
		                                  intent,
								    fnd_profile.value('OKC_LIBRARY_ACCESS_INTENT')
						    )
              AND status_code = 'PENDINGPUB';
Line: 1410

				UPDATE okc_xprt_rule_hdrs_all
				   SET status_code = DECODE(l_rule_exists_flag,'T','REVISION','F','DRAFT')
				 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
				   AND rule_id = csr_pub_rule_rec.rule_id;
Line: 1445

	UPDATE Okc_Xprt_Question_Orders
	SET runtime_available_flag = 'Y',
	    question_rule_status  = 'ACTIVE',
	    last_updated_by = FND_GLOBAL.USER_ID,
	    last_update_date = SYSDATE,
	    last_update_login = FND_GLOBAL.LOGIN_ID
	WHERE question_rule_status = 'PENDINGPUB'
	AND   template_id IN ( SELECT template_id
		                     FROM okc_terms_templates_all
				                 WHERE xprt_request_id = FND_GLOBAL.CONC_REQUEST_ID
		                   );
Line: 1458

  DELETE FROM okc_xprt_template_rules
	WHERE NVL(deleted_flag,'N') = 'Y'
	AND   template_id IN ( SELECT template_id
		                     FROM okc_terms_templates_all
				                 WHERE xprt_request_id = FND_GLOBAL.CONC_REQUEST_ID
		                   );
Line: 1466

  UPDATE okc_xprt_template_rules
	SET    published_flag = 'Y'
	WHERE template_id IN ( SELECT template_id
		                     FROM okc_terms_templates_all
				                 WHERE xprt_request_id = FND_GLOBAL.CONC_REQUEST_ID
		                   );
Line: 1478

  	  SELECT okc_xprt_util_pvt.is_rule_line_level(l_rule_id) INTO l_line_level_flag FROM DUAL;
Line: 1480

  	  UPDATE okc_xprt_rule_hdrs_all
	    SET    status_code = 'ACTIVE',
		         published_flag = 'Y',
		         line_level_flag = l_line_level_flag, --is_rule_line_level(l_rule_id),
		         last_updated_by = FND_GLOBAL.USER_ID,
		         last_update_date = SYSDATE,
		         last_update_login = FND_GLOBAL.LOGIN_ID,
		         activation_date = SYSDATE
	    WHERE  rule_id = l_rule_id;
Line: 1490

      DELETE FROM okc_xprt_rule_outcomes_active WHERE rule_id = l_rule_id;
Line: 1492

      DELETE FROM okc_xprt_rule_cond_vals_active WHERE rule_condition_id IN (SELECT rule_condition_id FROM okc_xprt_rule_conditions WHERE rule_id = l_rule_id);
Line: 1494

      DELETE FROM okc_xprt_rule_cond_active WHERE rule_id = l_rule_id;
Line: 1496

      DELETE FROM okc_xprt_rule_hdrs_all_active WHERE rule_id = l_rule_id;
Line: 1578

SELECT SUBSTR(req_data,
                      1,
                      INSTR(req_data,':',1) -1
                    )  child_req_id,
       SUBSTR(req_data,
                      INSTR(req_data,':',1) + 1
                    ) run_id
FROM dual;
Line: 1588

SELECT COUNT(*)
  FROM cz_imp_rules
 WHERE run_id = G_RUN_ID;
Line: 1594

SELECT rule_id
  FROM okc_xprt_rule_hdrs_all
 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID;
Line: 1607

SELECT rule_id
  FROM okc_xprt_rule_hdrs_all
 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID ;
Line: 1615

SELECT fnd_profile.Value('OKC_USE_CONTRACTS_RULES_ENGINE') INTO l_okc_rules_engine FROM dual;
Line: 1642

	        Step 1: Update current request Id for all rules to be published
	    */

         fnd_file.put_line(FND_FILE.LOG,'  ');
Line: 1652

           UPDATE okc_xprt_rule_hdrs_all
              SET request_id = FND_GLOBAL.CONC_REQUEST_ID,
		        program_id = FND_GLOBAL.CONC_PROGRAM_ID,
			   program_application_id = FND_GLOBAL.PROG_APPL_ID,
			   program_update_date = SYSDATE,
			   last_update_login = FND_GLOBAL.LOGIN_ID,
			   last_update_date = SYSDATE,
			   last_updated_by = FND_GLOBAL.USER_ID
            WHERE org_id = p_org_id
		    AND intent = DECODE(NVL(fnd_profile.value('OKC_LIBRARY_ACCESS_INTENT'),'A'),'A',
		                                  intent,
								    fnd_profile.value('OKC_LIBRARY_ACCESS_INTENT')
						    )
              AND status_code = 'PENDINGDISABLE';
Line: 1711

		 	UPDATE okc_xprt_rule_hdrs_all
		 	   SET status_code = DECODE(l_rule_exists_flag,'T','ACTIVE',status_code)
		 	 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
		 	   AND rule_id = csr_dis_rule_rec.rule_id;
Line: 1726

		 	UPDATE okc_xprt_rule_hdrs_all
		 	   SET status_code = DECODE(l_rule_exists_flag,'T','ACTIVE',status_code)
		 	 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
		 	   AND rule_id = csr_dis_rule_rec.rule_id;
Line: 1768

		 	UPDATE okc_xprt_rule_hdrs_all
		 	   SET status_code = DECODE(l_rule_exists_flag,'T','ACTIVE',status_code)
		 	 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
		 	   AND rule_id = csr_dis_rule_rec.rule_id;
Line: 1783

		 	UPDATE okc_xprt_rule_hdrs_all
		 	   SET status_code = DECODE(l_rule_exists_flag,'T','ACTIVE',status_code)
		 	 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
		 	   AND rule_id = csr_dis_rule_rec.rule_id;
Line: 1836

		 	UPDATE okc_xprt_rule_hdrs_all
		 	   SET status_code = DECODE(l_rule_exists_flag,'T','ACTIVE',status_code)
		 	 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
		 	   AND rule_id = csr_dis_rule_rec.rule_id;
Line: 1851

		 	UPDATE okc_xprt_rule_hdrs_all
		 	   SET status_code = DECODE(l_rule_exists_flag,'T','ACTIVE',status_code)
		 	 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
		 	   AND rule_id = csr_dis_rule_rec.rule_id;
Line: 1908

		 	UPDATE okc_xprt_rule_hdrs_all
		 	   SET status_code = DECODE(l_rule_exists_flag,'T','ACTIVE',status_code)
		 	 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
		 	   AND rule_id = csr_dis_rule_rec.rule_id;
Line: 1975

		 	UPDATE okc_xprt_rule_hdrs_all
		 	   SET status_code = DECODE(l_rule_exists_flag,'T','ACTIVE',status_code)
		 	 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
		 	   AND rule_id = csr_dis_rule_rec.rule_id;
Line: 2017

		 	UPDATE okc_xprt_rule_hdrs_all
		 	   SET status_code = DECODE(l_rule_exists_flag,'T','ACTIVE',status_code)
		 	 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
		 	   AND rule_id = csr_dis_rule_rec.rule_id;
Line: 2032

		 	UPDATE okc_xprt_rule_hdrs_all
		 	   SET status_code = DECODE(l_rule_exists_flag,'T','ACTIVE',status_code)
		 	 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
		 	   AND rule_id = csr_dis_rule_rec.rule_id;
Line: 2072

		 	UPDATE okc_xprt_rule_hdrs_all
		 	   SET status_code = DECODE(l_rule_exists_flag,'T','ACTIVE',status_code)
		 	 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
		 	   AND rule_id = csr_dis_rule_rec.rule_id;
Line: 2087

		 	UPDATE okc_xprt_rule_hdrs_all
		 	   SET status_code = DECODE(l_rule_exists_flag,'T','ACTIVE',status_code)
		 	 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
		 	   AND rule_id = csr_dis_rule_rec.rule_id;
Line: 2128

		 	UPDATE okc_xprt_rule_hdrs_all
		 	   SET status_code = DECODE(l_rule_exists_flag,'T','ACTIVE',status_code)
		 	 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
		 	   AND rule_id = csr_dis_rule_rec.rule_id;
Line: 2143

		 	UPDATE okc_xprt_rule_hdrs_all
		 	   SET status_code = DECODE(l_rule_exists_flag,'T','ACTIVE',status_code)
		 	 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID
		 	   AND rule_id = csr_dis_rule_rec.rule_id;
Line: 2199

	        Step 1: Update current request Id for all rules to be published
	    */

         fnd_file.put_line(FND_FILE.LOG,'  ');
Line: 2209

           UPDATE okc_xprt_rule_hdrs_all
              SET request_id = FND_GLOBAL.CONC_REQUEST_ID,
		        program_id = FND_GLOBAL.CONC_PROGRAM_ID,
			   program_application_id = FND_GLOBAL.PROG_APPL_ID,
			   program_update_date = SYSDATE,
			   last_update_login = FND_GLOBAL.LOGIN_ID,
			   last_update_date = SYSDATE,
			   last_updated_by = FND_GLOBAL.USER_ID
            WHERE org_id = p_org_id
		    AND intent = DECODE(NVL(fnd_profile.value('OKC_LIBRARY_ACCESS_INTENT'),'A'),'A',
		                                  intent,
								    fnd_profile.value('OKC_LIBRARY_ACCESS_INTENT')
						    )
              AND status_code = 'PENDINGDISABLE';
Line: 2263

  	  SELECT okc_xprt_util_pvt.is_rule_line_level(l_rule_id) INTO l_line_level_flag FROM DUAL;
Line: 2265

  	  UPDATE okc_xprt_rule_hdrs_all
	    SET    status_code = 'INACTIVE',
		         published_flag = 'Y',
		         line_level_flag = l_line_level_flag, --is_rule_line_level(l_rule_id),
		         last_updated_by = FND_GLOBAL.USER_ID,
		         last_update_date = SYSDATE,
		         last_update_login = FND_GLOBAL.LOGIN_ID
	    WHERE  rule_id = l_rule_id;
Line: 2274

      DELETE FROM okc_xprt_rule_outcomes_active WHERE rule_id = l_rule_id;
Line: 2276

      DELETE FROM okc_xprt_rule_cond_vals_active WHERE rule_condition_id IN (SELECT rule_condition_id FROM okc_xprt_rule_conditions WHERE rule_id = l_rule_id);
Line: 2278

      DELETE FROM okc_xprt_rule_cond_active WHERE rule_id = l_rule_id;
Line: 2280

      DELETE FROM okc_xprt_rule_hdrs_all_active WHERE rule_id = l_rule_id;
Line: 2350

SELECT 'x'
  FROM okc_terms_templates_all t
 WHERE  t.org_id = p_org_id
   AND  t.intent = p_intent
   AND  t.template_id = NVL(p_template_id, template_id);
Line: 2357

SELECT COUNT(*)
  FROM okc_xprt_rule_hdrs_all
 WHERE org_id = p_org_id
   AND intent = p_intent
   AND status_code = 'ACTIVE';
Line: 2613

SELECT SUBSTR(req_data,
                      1,
                      INSTR(req_data,':',1) -1
                    )  child_req_id,
       SUBSTR(req_data,
                      INSTR(req_data,':',1) + 1
                    ) run_id
FROM dual;
Line: 2623

SELECT COUNT(*)
  FROM cz_imp_rules
 WHERE run_id = G_RUN_ID;
Line: 2635

SELECT fnd_profile.Value('OKC_USE_CONTRACTS_RULES_ENGINE') INTO l_okc_rules_engine FROM dual;
Line: 2814

		      Step 2.2: Insert Extension Rules in Template Approval Flow
		   */

            fnd_file.put_line(FND_FILE.LOG,'  ');
Line: 2819

            fnd_file.put_line(FND_FILE.LOG,'Step 2.2: Calling API to insert extension rule records');
Line: 2835

            fnd_file.put_line(FND_FILE.LOG,'Step 2.2: After Calling API to insert extension rule records');