PACKAGE BODY: APPS.CCT_INTERACTION_KEY_UPG

Source

1 PACKAGE BODY CCT_INTERACTION_KEY_UPG as
2 /* $Header: cctupgib.pls 120.0 2005/06/02 10:11:15 appldev noship $ */
3 
4 procedure Upgrade_Interaction_Keys
5 IS
6     l_upgraded        NUMERIC;
7     status NUMERIC;
8     l_operation varchar2(20) := 'EQUAL';
9     BEGIN
10       BEGIN
11         SELECT Count(*) INTO l_upgraded
12         FROM cct_interaction_keys
13         WHERE (interaction_key='AccountCode' and data_type='STRING') OR
14 			(interaction_key='ContractNum' and data_type='STRING') OR
15 			(interaction_key='AccountNum' and data_type='STRING') ;
16       EXCEPTION
17 	When Others Then
18 	  status := SQLCODE;
19 	  --dbms_output.put_line('In Upgrade_Interaction_Key Proc '||SQLERRM(STATUS));
20     return;
21       END;
22       if (l_upgraded > 0) THEN
23          --dbms_output.put_line('Interaction Keys are Already Upgraded');
24          return;
25       END IF;
26 
27 
28  FOR l_record IN (
29  	select route_param_id, value, operation from cct_route_params
30  		where nvl(f_deletedflag,'N') <> 'D' )
31  LOOP
32 	l_operation := 'EQUAL';
33     IF ((l_record.value = 'AccountCode') OR (l_record.value = 'ContractNum')
34 								OR (l_record.value = 'AccountNum')) THEN
35        IF (l_record.operation = '!=') THEN
36          l_operation := 'NOTEQUAL';
37        ELSIF (l_record.operation = 'DOESNOTEXISTSIN') THEN
38          l_operation := 'DONOTEXISTSIN';
39        ELSIF (l_record.operation = 'EXISTSIN') THEN
40          l_operation := 'EXISTSIN';
41     	  END IF;
42 
43     	  update cct_route_params set operation = l_operation
44 		where route_param_id = l_record.route_param_id AND nvl(f_deletedflag,'N')<>'D';
45 
46     END IF;
47  END LOOP;
48 
49 
50 FOR l_record IN (
51     select classification_rule_id, key, operation from CCT_Classification_Rules
52         where nvl(f_deletedflag,'N') <> 'D' )
53 LOOP
54    l_operation := 'EQUAL';
55 	 IF ((l_record.key = 'AccountCode') OR (l_record.key = 'ContractNum')
56 								OR (l_record.key = 'AccountNum')) THEN
57       IF (l_record.operation = '!=') THEN
58         l_operation := 'NOTEQUAL';
59       ELSIF (l_record.operation = 'DOESNOTEXISTSIN') THEN
60         l_operation := 'DONOTEXISTSIN';
61       ELSIF (l_record.operation = 'EXISTSIN') THEN
62         l_operation := 'EXISTSIN';
63       END IF;
64 
65       update CCT_Classification_Rules set operation = l_operation
66       where classification_rule_id = l_record.classification_rule_id
67 		AND nvl(f_deletedflag,'N')<>'D'  ;
68 
69    END IF;
70 END LOOP;
71 	update cct_interaction_keys set data_type='STRING'
72 		where (interaction_key='AccountCode' or interaction_key='ContractNum'
73 									  or interaction_key='AccountNum');
74 
75 
76   --dbms_output.put_line(' Populate cct_interaction_keys table ');
77 
78   END Upgrade_Interaction_Keys;
79 END CCT_INTERACTION_KEY_UPG;