[Home] [Help]
PACKAGE BODY: APPS.AME_CONDITION_USAGES_API
Source
1 PACKAGE BODY AME_CONDITION_USAGES_API AS
2 /* $Header: amecsapi.pkb 120.1 2005/10/14 04:12:25 ubhat noship $ */
3
4 procedure OWNER_TO_WHO (
5 X_OWNER in VARCHAR2,
6 X_CREATED_BY out nocopy NUMBER,
7 X_LAST_UPDATED_BY out nocopy NUMBER,
8 X_LAST_UPDATE_LOGIN out nocopy NUMBER
9 ) is
10 begin
11 X_CREATED_BY := AME_SEED_UTILITY.OWNER_AS_INTEGER(X_OWNER);
12 X_LAST_UPDATED_BY := AME_SEED_UTILITY.OWNER_AS_INTEGER(X_OWNER);
13 X_LAST_UPDATE_LOGIN := 0;
14 end OWNER_TO_WHO;
15
16 procedure KEY_TO_IDS (
17 X_RULE_ID in NUMBER,
18 X_CONDITION_ID in NUMBER,
19 X_CONDITION_USAGE_COUNT out nocopy NUMBER
20 ) is
21 cursor CSR_GET_COND_USAGE_COUNT
22 (
23 X_CONDITION_ID in NUMBER,
24 X_RULE_ID in NUMBER
25 ) is
26 select COUNT(*)
27 from AME_CONDITION_USAGES
28 where CONDITION_ID = X_CONDITION_ID
29 and RULE_ID = X_RULE_ID;
30
31 X_ATTRIBUTE_ID NUMBER;
32
33 begin
34
35 if (X_CONDITION_ID is not null) and
36 (X_RULE_ID is not null) then
37 open CSR_GET_COND_USAGE_COUNT (
38 X_CONDITION_ID,
39 X_RULE_ID
40 );
41 fetch CSR_GET_COND_USAGE_COUNT into X_CONDITION_USAGE_COUNT;
42 close CSR_GET_COND_USAGE_COUNT;
43 end if;
44
45 end KEY_TO_IDS;
46
47 procedure KEY_TO_IDS_3 (
48 X_RULE_KEY in NUMBER,
49 X_CONDITION_ID in NUMBER,
50 X_RULE_ID out nocopy NUMBER,
51 X_CONDITION_USAGE_COUNT out nocopy NUMBER
52 ) is
53 cursor CSR_GET_COND_USAGE_COUNT
54 (
55 X_CONDITION_ID in NUMBER,
56 X_RULE_ID in NUMBER
57 ) is
58 select COUNT(*)
59 from AME_CONDITION_USAGES
60 where CONDITION_ID = X_CONDITION_ID
61 and RULE_ID = X_RULE_ID;
62 cursor CSR_GET_RULE_ID
63 (
64 X_RULE_KEY in VARCHAR2
65 ) is
66 select RULE_ID
67 from AME_RULES
68 where RULE_KEY = X_RULE_KEY;
69 X_ATTRIBUTE_ID NUMBER;
70 begin
71 open CSR_GET_RULE_ID (
72 X_RULE_KEY
73 );
74 fetch CSR_GET_RULE_ID into X_RULE_ID;
75 if (CSR_GET_RULE_ID%notfound) then
76 X_RULE_ID := null;
77 end if;
78 close CSR_GET_RULE_ID;
79 if (X_CONDITION_ID is not null) and
80 (X_RULE_ID is not null) then
81 open CSR_GET_COND_USAGE_COUNT (
82 X_CONDITION_ID,
83 X_RULE_ID
84 );
85 fetch CSR_GET_COND_USAGE_COUNT into X_CONDITION_USAGE_COUNT;
86 close CSR_GET_COND_USAGE_COUNT;
87 end if;
88 end KEY_TO_IDS_3;
89
90
91 procedure INSERT_ROW (
92 X_RULE_ID in NUMBER,
93 X_CONDITION_ID in NUMBER,
94 X_CREATED_BY in NUMBER,
95 X_CREATION_DATE in DATE,
96 X_LAST_UPDATED_BY in NUMBER,
97 X_LAST_UPDATE_DATE in DATE,
98 X_LAST_UPDATE_LOGIN in NUMBER,
99 X_START_DATE in DATE,
100 X_OBJECT_VERSION_NUMBER in NUMBER)
101 is
102
103 begin
104
105 insert into AME_CONDITION_USAGES
106 (
107 RULE_ID,
108 CONDITION_ID,
109 CREATED_BY,
110 CREATION_DATE,
111 LAST_UPDATED_BY,
112 LAST_UPDATE_DATE,
113 LAST_UPDATE_LOGIN,
114 START_DATE,
115 END_DATE,
116 OBJECT_VERSION_NUMBER
117 ) values (
118 X_RULE_ID,
119 X_CONDITION_ID,
120 X_CREATED_BY,
121 X_CREATION_DATE,
122 X_LAST_UPDATED_BY,
123 X_LAST_UPDATE_DATE,
124 X_LAST_UPDATE_LOGIN,
125 X_START_DATE,
126 AME_SEED_UTILITY.GET_DEFAULT_END_DATE,
127 X_OBJECT_VERSION_NUMBER);
128
129 end INSERT_ROW;
130
131 procedure DELETE_ROW (
132 X_RULE_ID in NUMBER,
133 X_CONDITION_ID in NUMBER)
134 is
135 begin
136 delete from AME_CONDITION_USAGES
137 where RULE_ID = X_RULE_ID
138 and CONDITION_ID = X_CONDITION_ID;
139
140 if (sql%notfound) then
141 raise no_data_found;
142 end if;
143 end DELETE_ROW;
144
145 procedure LOAD_ROW (
146 X_RULE_ID in VARCHAR2,
147 X_CONDITION_ID in VARCHAR2,
148 X_OWNER in VARCHAR2,
149 X_LAST_UPDATE_DATE in VARCHAR2
150 )
151 is
152 X_CONDITION_ID_LOAD NUMBER;
153 X_CONDITION_USAGE VARCHAR2(20);
154 X_CONDITION_USAGE_COUNT NUMBER := 0;
155 X_CREATED_BY NUMBER;
156 X_LAST_UPDATED_BY NUMBER;
157 X_LAST_UPDATE_LOGIN NUMBER;
158 X_RULE_ID_LOAD NUMBER;
159 begin
160 OWNER_TO_WHO (
161 X_OWNER,
162 X_CREATED_BY,
163 X_LAST_UPDATED_BY,
164 X_LAST_UPDATE_LOGIN
165 );
166 if X_RULE_ID < 0 then
167 -- Drive off CONDITION_ID and RULE_ID
168 KEY_TO_IDS (
169 X_RULE_ID,
170 X_CONDITION_ID,
171 X_CONDITION_USAGE_COUNT
172 );
173 -- the current row was not found insert a new row
174 if (X_CONDITION_USAGE_COUNT = 0) then
175 INSERT_ROW (
176 X_RULE_ID,
177 X_CONDITION_ID,
178 X_CREATED_BY,
179 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
180 X_LAST_UPDATED_BY,
181 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
182 X_LAST_UPDATE_LOGIN,
183 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
184 1);
185 end if;
186 end if;
187 exception
188 when others then
189 ame_util.runtimeException('ame_condition_usages_api',
190 'load_row',
191 sqlcode,
192 sqlerrm);
193 raise;
194 end LOAD_ROW;
195 --
196 END AME_CONDITION_USAGES_API;