[Home] [Help]
PACKAGE BODY: APPS.AME_STRING_VALUES_API2
Source
1 PACKAGE BODY AME_STRING_VALUES_API2 AS
2 /* $Header: amesaapi.pkb 120.1 2005/10/14 04:14 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_CONDITION_KEY in VARCHAR2,
18 X_STRING_VALUE in VARCHAR2,
19 X_CONDITION_ID out nocopy NUMBER,
20 X_STRING_VALUE_ROW out nocopy VARCHAR2
21 ) is
22 cursor CSR_GET_CONDITION_ID
23 (
24 X_CONDITION_KEY in VARCHAR2
25 ) is
26 select CONDITION_ID
27 from AME_CONDITIONS
28 where CONDITION_KEY = X_CONDITION_KEY;
29
30 cursor CSR_GET_STRING_VALUE_ROW
31 (
32 X_CONDITION_ID in NUMBER,
33 X_STRING_VALUE in VARCHAR2
34 ) is
35 select 'FOUND'
36 from AME_STRING_VALUES
37 where CONDITION_ID = X_CONDITION_ID
38 and STRING_VALUE = X_STRING_VALUE;
39 begin
40 open CSR_GET_CONDITION_ID (
41 X_CONDITION_KEY
42 );
43 fetch CSR_GET_CONDITION_ID into X_CONDITION_ID;
44 if (CSR_GET_CONDITION_ID%notfound) then
45 X_CONDITION_ID := null;
46 end if;
47 close CSR_GET_CONDITION_ID;
48
49 if X_CONDITION_ID is not null then
50 open CSR_GET_STRING_VALUE_ROW (
51 X_CONDITION_ID, X_STRING_VALUE
52 );
53 fetch CSR_GET_STRING_VALUE_ROW into X_STRING_VALUE_ROW;
54 if (CSR_GET_STRING_VALUE_ROW%notfound) then
55 X_STRING_VALUE_ROW := 'NOTFOUND';
56 end if;
57 close CSR_GET_STRING_VALUE_ROW;
58 end if;
59 end KEY_TO_IDS;
60
61 procedure KEY_TO_IDS_2 (
62 X_CONDITION_ID in NUMBER,
63 X_STRING_VALUE in VARCHAR2,
64 X_STRING_VALUE_COUNT out nocopy NUMBER
65 ) is
66 cursor CSR_GET_STRING_VALUE_COUNT
67 (
68 X_CONDITION_ID in NUMBER,
69 X_STRING_VALUE in VARCHAR2
70 ) is
71 select COUNT(*)
72 from AME_STRING_VALUES
73 where CONDITION_ID = X_CONDITION_ID
74 and STRING_VALUE = X_STRING_VALUE;
75 begin
76 if X_CONDITION_ID is not null then
77 open CSR_GET_STRING_VALUE_COUNT (X_CONDITION_ID, X_STRING_VALUE);
78 fetch CSR_GET_STRING_VALUE_COUNT into X_STRING_VALUE_COUNT;
79 close CSR_GET_STRING_VALUE_COUNT;
80 end if;
81 end KEY_TO_IDS_2;
82
83 procedure INSERT_ROW (
84 X_CONDITION_ID in NUMBER,
85 X_STRING_VALUE in VARCHAR2,
86 X_CREATED_BY in NUMBER,
87 X_CREATION_DATE in DATE,
88 X_LAST_UPDATED_BY in NUMBER,
89 X_LAST_UPDATE_DATE in DATE,
90 X_LAST_UPDATE_LOGIN in NUMBER,
91 X_START_DATE in DATE,
92 X_OBJECT_VERSION_NUMBER in NUMBER)
93 is
94
95 begin
96
97 insert into AME_STRING_VALUES
98 (
99 CONDITION_ID,
100 STRING_VALUE,
101 CREATED_BY,
102 CREATION_DATE,
103 LAST_UPDATED_BY,
104 LAST_UPDATE_DATE,
105 LAST_UPDATE_LOGIN,
106 START_DATE,
107 END_DATE,
108 OBJECT_VERSION_NUMBER
109 ) values (
110 X_CONDITION_ID,
111 X_STRING_VALUE,
112 X_CREATED_BY,
113 X_CREATION_DATE,
114 X_LAST_UPDATED_BY,
115 X_LAST_UPDATE_DATE,
116 X_LAST_UPDATE_LOGIN,
117 X_START_DATE,
118 AME_SEED_UTILITY.GET_DEFAULT_END_DATE,
119 X_OBJECT_VERSION_NUMBER);
120
121 end INSERT_ROW;
122
123 procedure DELETE_ROW (
124 X_CONDITION_ID in NUMBER
125 ) is
126 begin
127 delete from AME_STRING_VALUES
128 where CONDITION_ID = X_CONDITION_ID;
129 if (sql%notfound) then
130 raise no_data_found;
131 end if;
132 end DELETE_ROW;
133
134 procedure LOAD_ROW (
135 X_CONDITION_KEY in VARCHAR2,
136 X_CONDITION_ID in VARCHAR2,
137 X_STRING_VALUE in VARCHAR2,
138 X_OWNER in VARCHAR2,
139 X_LAST_UPDATE_DATE in VARCHAR2,
140 X_CUSTOM_MODE in VARCHAR2
141 )
142 is
143 X_ATTRIBUTE_ID NUMBER;
144 X_CONDITION_ID_LOAD NUMBER := null;
145 X_CREATED_BY NUMBER;
146 X_LAST_UPDATED_BY NUMBER;
147 X_LAST_UPDATE_LOGIN NUMBER;
148 X_STRING_VALUE_COUNT NUMBER;
149 X_STRING_VALUE_ROW VARCHAR2(20);
150 begin
151 OWNER_TO_WHO (
152 X_OWNER,
153 X_CREATED_BY,
154 X_LAST_UPDATED_BY,
155 X_LAST_UPDATE_LOGIN
156 );
157 if X_CONDITION_ID > 0 then
158 -- drive off CONDITION_KEY
159 X_CONDITION_ID_LOAD := null;
160 KEY_TO_IDS (
161 X_CONDITION_KEY,
162 X_STRING_VALUE,
163 X_CONDITION_ID_LOAD,
164 X_STRING_VALUE_ROW
165 );
166 -- the current row was not found but there is a corresponding
167 -- condition insert a new row
168 if (X_STRING_VALUE_ROW = 'NOTFOUND') then
169 INSERT_ROW (
170 X_CONDITION_ID_LOAD,
171 X_STRING_VALUE,
172 X_CREATED_BY,
173 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
174 X_LAST_UPDATED_BY,
175 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
176 X_LAST_UPDATE_LOGIN,
177 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
178 1);
179 end if;
180 else
181 -- drive off CONDITION_ID
182 KEY_TO_IDS_2 (
183 X_CONDITION_ID,
184 X_STRING_VALUE,
185 X_STRING_VALUE_COUNT
186 );
187 -- the current row was not found but there is a corresponding
188 -- condition insert a new row
189 if (X_STRING_VALUE_COUNT = 0)
190 and (X_CONDITION_ID is not null) then
191 INSERT_ROW (
192 X_CONDITION_ID,
193 X_STRING_VALUE,
194 X_CREATED_BY,
195 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
196 X_LAST_UPDATED_BY,
197 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
198 X_LAST_UPDATE_LOGIN,
199 to_date(X_LAST_UPDATE_DATE,'YYYY/MM/DD HH24:MI:SS'),
200 1);
201 end if;
202 end if;
203 exception
204 when others then
205 ame_util.runtimeException('ame_string_values_api2',
206 'load_row',
207 sqlcode,
208 sqlerrm);
209 raise;
210 end LOAD_ROW;
211 --
212 END AME_STRING_VALUES_API2;