1 PACKAGE BODY HZ_PARTY_PREFERENCES_PKG as
2 /*$Header: ARHPRFTB.pls 115.5 2003/02/04 06:51:00 ssmohan noship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID in out NOCOPY VARCHAR2,
6 X_PARTY_PREFERENCE_ID in NUMBER,
7 X_PARTY_ID in NUMBER,
8 X_MODULE in VARCHAR2,
9 X_CATEGORY in VARCHAR2,
10 X_PREFERENCE_CODE in VARCHAR2,
11 X_VALUE_VARCHAR2 in VARCHAR2,
12 X_VALUE_NUMBER in NUMBER,
13 X_VALUE_DATE in DATE,
14 X_VALUE_NAME in VARCHAR2,
15 X_ADDITIONAL_VALUE1 in VARCHAR2,
16 X_ADDITIONAL_VALUE2 in VARCHAR2,
17 X_ADDITIONAL_VALUE3 in VARCHAR2,
18 X_ADDITIONAL_VALUE4 in VARCHAR2,
19 X_ADDITIONAL_VALUE5 in VARCHAR2,
20 X_OBJECT_VERSION_NUMBER in NUMBER,
21 X_CREATED_BY in NUMBER,
22 X_CREATION_DATE in DATE,
23 X_LAST_UPDATED_BY in NUMBER,
24 X_LAST_UPDATE_DATE in DATE,
25 X_LAST_UPDATE_LOGIN in NUMBER
26 ) is
27 cursor C is select ROWID from HZ_PARTY_PREFERENCES
28 where PARTY_PREFERENCE_ID = X_PARTY_PREFERENCE_ID;
29 l_rowid ROWID;
30 begin
31 insert into HZ_PARTY_PREFERENCES (
32 PARTY_PREFERENCE_ID,
33 PARTY_ID,
34 MODULE,
35 CATEGORY,
36 PREFERENCE_CODE,
37 VALUE_VARCHAR2,
38 VALUE_NUMBER,
39 VALUE_DATE,
40 VALUE_NAME,
41 ADDITIONAL_VALUE1,
42 ADDITIONAL_VALUE2,
43 ADDITIONAL_VALUE3,
44 ADDITIONAL_VALUE4,
45 ADDITIONAL_VALUE5,
46 OBJECT_VERSION_NUMBER,
47 CREATED_BY,
48 CREATION_DATE,
49 LAST_UPDATED_BY,
50 LAST_UPDATE_DATE,
51 LAST_UPDATE_LOGIN
52 )
53 VALUES
54 ( X_PARTY_PREFERENCE_ID
55 , DECODE(X_PARTY_ID, FND_API.G_MISS_NUM, NULL, X_PARTY_ID)
56 , DECODE(X_MODULE, FND_API.G_MISS_CHAR, NULL, X_MODULE)
57 , DECODE(X_CATEGORY, FND_API.G_MISS_CHAR, NULL, X_CATEGORY)
58 , DECODE(X_PREFERENCE_CODE, FND_API.G_MISS_CHAR, NULL, X_PREFERENCE_CODE)
59 , DECODE(X_VALUE_VARCHAR2, FND_API.G_MISS_CHAR, NULL, X_VALUE_VARCHAR2)
60 , DECODE(X_VALUE_NUMBER, FND_API.G_MISS_NUM, NULL, X_VALUE_NUMBER)
61 , DECODE(X_VALUE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), X_VALUE_DATE)
62 , DECODE(X_VALUE_NAME, FND_API.G_MISS_CHAR, NULL, X_VALUE_NAME)
63 , DECODE(X_ADDITIONAL_VALUE1, FND_API.G_MISS_CHAR, NULL, X_ADDITIONAL_VALUE1)
64 , DECODE(X_ADDITIONAL_VALUE2, FND_API.G_MISS_CHAR, NULL, X_ADDITIONAL_VALUE2)
65 , DECODE(X_ADDITIONAL_VALUE3, FND_API.G_MISS_CHAR, NULL, X_ADDITIONAL_VALUE3)
66 , DECODE(X_ADDITIONAL_VALUE4, FND_API.G_MISS_CHAR, NULL, X_ADDITIONAL_VALUE4)
67 , DECODE(X_ADDITIONAL_VALUE5, FND_API.G_MISS_CHAR, NULL, X_ADDITIONAL_VALUE5)
68 , DECODE(X_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, X_OBJECT_VERSION_NUMBER)
69 , DECODE(X_CREATED_BY, FND_API.G_MISS_NUM, NULL, X_CREATED_BY)
70 , DECODE(x_CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), x_CREATION_DATE)
71 , DECODE(x_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, x_LAST_UPDATED_BY)
72 , DECODE(x_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), x_LAST_UPDATE_DATE)
73 , DECODE(x_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL, x_LAST_UPDATE_LOGIN)
74 ) returning ROWID into X_ROWID;
75
76 -- Following is an old standard we used to use.
77 -- Now we are trying to use returning rowid clause
78 -- for returning the rowid back.
79 /*
80 open c;
81 fetch c into X_ROWID;
82 if (c%notfound) then
83 close c;
84 raise no_data_found;
85 end if;
86 close c;
87 */
88
89 end INSERT_ROW;
90
91 procedure LOCK_ROW (
92 X_PARTY_PREFERENCE_ID in NUMBER,
93 X_PARTY_ID in NUMBER,
94 X_MODULE in VARCHAR2,
95 X_CATEGORY in VARCHAR2,
96 X_PREFERENCE_CODE in VARCHAR2,
97 X_VALUE_VARCHAR2 in VARCHAR2,
98 X_VALUE_NUMBER in NUMBER,
99 X_VALUE_DATE in DATE,
100 X_VALUE_NAME in VARCHAR2,
101 X_ADDITIONAL_VALUE1 in VARCHAR2,
102 X_ADDITIONAL_VALUE2 in VARCHAR2,
103 X_ADDITIONAL_VALUE3 in VARCHAR2,
104 X_ADDITIONAL_VALUE4 in VARCHAR2,
105 X_ADDITIONAL_VALUE5 in VARCHAR2,
106 X_OBJECT_VERSION_NUMBER in NUMBER
107 ) is
108 cursor c1 is select
109 PARTY_ID,
110 MODULE,
111 CATEGORY,
112 PREFERENCE_CODE,
113 VALUE_VARCHAR2,
114 VALUE_NUMBER,
115 VALUE_DATE,
116 VALUE_NAME,
117 ADDITIONAL_VALUE1,
118 ADDITIONAL_VALUE2,
119 ADDITIONAL_VALUE3,
120 ADDITIONAL_VALUE4,
121 ADDITIONAL_VALUE5,
122 OBJECT_VERSION_NUMBER
123 from HZ_PARTY_PREFERENCES
124 where PARTY_PREFERENCE_ID = X_PARTY_PREFERENCE_ID
125 for update of PARTY_PREFERENCE_ID nowait;
126 begin
127 for tlinfo in c1 loop
128 if ( ((tlinfo.PARTY_ID = X_PARTY_ID)
129 OR ((tlinfo.PARTY_ID is null) AND (X_PARTY_ID is null)))
130 AND ((tlinfo.MODULE = X_MODULE)
131 OR ((tlinfo.MODULE is null) AND (X_MODULE is null)))
132 AND ((tlinfo.CATEGORY = X_CATEGORY)
133 OR ((tlinfo.CATEGORY is null) AND (X_CATEGORY is null)))
134 AND ((tlinfo.PREFERENCE_CODE = X_PREFERENCE_CODE)
135 OR ((tlinfo.PREFERENCE_CODE is null) AND (X_PREFERENCE_CODE is null)))
136 AND ((tlinfo.VALUE_VARCHAR2 = X_VALUE_VARCHAR2)
137 OR ((tlinfo.VALUE_VARCHAR2 is null) AND (X_VALUE_VARCHAR2 is null)))
138 AND ((tlinfo.VALUE_NUMBER = X_VALUE_NUMBER)
139 OR ((tlinfo.VALUE_NUMBER is null) AND (X_VALUE_NUMBER is null)))
140 AND ((tlinfo.VALUE_DATE = X_VALUE_DATE)
141 OR ((tlinfo.VALUE_DATE is null) AND (X_VALUE_DATE is null)))
142 AND ((tlinfo.VALUE_NAME = X_VALUE_NAME)
143 OR ((tlinfo.VALUE_NAME is null) AND (X_VALUE_NAME is null)))
144 AND ((tlinfo.ADDITIONAL_VALUE1 = X_ADDITIONAL_VALUE1)
145 OR ((tlinfo.ADDITIONAL_VALUE1 is null) AND (X_ADDITIONAL_VALUE1 is null)))
146 AND ((tlinfo.ADDITIONAL_VALUE2 = X_ADDITIONAL_VALUE2)
147 OR ((tlinfo.ADDITIONAL_VALUE2 is null) AND (X_ADDITIONAL_VALUE2 is null)))
148 AND ((tlinfo.ADDITIONAL_VALUE3 = X_ADDITIONAL_VALUE3)
149 OR ((tlinfo.ADDITIONAL_VALUE3 is null) AND (X_ADDITIONAL_VALUE3 is null)))
150 AND ((tlinfo.ADDITIONAL_VALUE4 = X_ADDITIONAL_VALUE4)
151 OR ((tlinfo.ADDITIONAL_VALUE4 is null) AND (X_ADDITIONAL_VALUE4 is null)))
152 AND ((tlinfo.ADDITIONAL_VALUE5 = X_ADDITIONAL_VALUE5)
153 OR ((tlinfo.ADDITIONAL_VALUE5 is null) AND (X_ADDITIONAL_VALUE5 is null)))
154 AND ((tlinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
155 OR ((tlinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
156 ) then
157 null;
158 else
159 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
160 app_exception.raise_exception;
161 end if;
162 end loop;
163 return;
164 end LOCK_ROW;
165
166 procedure UPDATE_ROW (
167 X_PARTY_PREFERENCE_ID in NUMBER,
168 X_PARTY_ID in NUMBER,
169 X_MODULE in VARCHAR2,
170 X_CATEGORY in VARCHAR2,
171 X_PREFERENCE_CODE in VARCHAR2,
172 X_VALUE_VARCHAR2 in VARCHAR2,
173 X_VALUE_NUMBER in NUMBER,
174 X_VALUE_DATE in DATE,
175 X_VALUE_NAME in VARCHAR2,
176 X_ADDITIONAL_VALUE1 in VARCHAR2,
177 X_ADDITIONAL_VALUE2 in VARCHAR2,
178 X_ADDITIONAL_VALUE3 in VARCHAR2,
179 X_ADDITIONAL_VALUE4 in VARCHAR2,
180 X_ADDITIONAL_VALUE5 in VARCHAR2,
181 X_OBJECT_VERSION_NUMBER in NUMBER,
182 X_LAST_UPDATED_BY in NUMBER,
183 X_LAST_UPDATE_DATE in DATE,
184 X_LAST_UPDATE_LOGIN in NUMBER
185 ) is
186 begin
187 update HZ_PARTY_PREFERENCES set
188 PARTY_ID = DECODE(X_PARTY_ID, FND_API.G_MISS_NUM, PARTY_ID, X_PARTY_ID),
189 MODULE = DECODE(X_MODULE, FND_API.G_MISS_CHAR, MODULE, X_MODULE),
190 CATEGORY = DECODE(X_CATEGORY, FND_API.G_MISS_CHAR, CATEGORY, X_CATEGORY),
191 PREFERENCE_CODE = DECODE(X_PREFERENCE_CODE,FND_API.G_MISS_CHAR, PREFERENCE_CODE, X_PREFERENCE_CODE),
192 VALUE_VARCHAR2 = DECODE(X_VALUE_VARCHAR2, FND_API.G_MISS_CHAR, VALUE_VARCHAR2, X_VALUE_VARCHAR2),
193 VALUE_NUMBER = DECODE(X_VALUE_NUMBER, FND_API.G_MISS_NUM, VALUE_NUMBER, X_VALUE_NUMBER),
194 VALUE_DATE = DECODE(X_VALUE_DATE, FND_API.G_MISS_DATE, VALUE_DATE, X_VALUE_DATE),
195 VALUE_NAME = DECODE(X_VALUE_NAME, FND_API.G_MISS_CHAR, VALUE_NAME, X_VALUE_NAME),
196 ADDITIONAL_VALUE1 = DECODE(X_ADDITIONAL_VALUE1, FND_API.G_MISS_CHAR, ADDITIONAL_VALUE1, X_ADDITIONAL_VALUE1),
197 ADDITIONAL_VALUE2 = DECODE(X_ADDITIONAL_VALUE2, FND_API.G_MISS_CHAR, ADDITIONAL_VALUE2, X_ADDITIONAL_VALUE2),
198 ADDITIONAL_VALUE3 = DECODE(X_ADDITIONAL_VALUE3, FND_API.G_MISS_CHAR, ADDITIONAL_VALUE3, X_ADDITIONAL_VALUE3),
199 ADDITIONAL_VALUE4 = DECODE(X_ADDITIONAL_VALUE4, FND_API.G_MISS_CHAR, ADDITIONAL_VALUE4, X_ADDITIONAL_VALUE4),
200 ADDITIONAL_VALUE5 = DECODE(X_ADDITIONAL_VALUE5, FND_API.G_MISS_CHAR, ADDITIONAL_VALUE5, X_ADDITIONAL_VALUE5),
201 OBJECT_VERSION_NUMBER = DECODE(X_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, OBJECT_VERSION_NUMBER, X_OBJECT_VERSION_NUMBER),
202 LAST_UPDATE_DATE = DECODE(X_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, LAST_UPDATE_DATE, X_LAST_UPDATE_DATE),
203 LAST_UPDATED_BY = DECODE(X_LAST_UPDATED_BY, FND_API.G_MISS_NUM, LAST_UPDATED_BY, X_LAST_UPDATED_BY),
204 LAST_UPDATE_LOGIN = DECODE(X_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, LAST_UPDATE_LOGIN, X_LAST_UPDATE_LOGIN)
205 where PARTY_PREFERENCE_ID = X_PARTY_PREFERENCE_ID;
206
207 if (sql%notfound) then
208 raise no_data_found;
209 end if;
210 end UPDATE_ROW;
211
212 procedure DELETE_ROW (
213 X_PARTY_PREFERENCE_ID in NUMBER,
214 X_OBJECT_VERSION_NUMBER in NUMBER
215 ) is
216 begin
217 delete from HZ_PARTY_PREFERENCES
218 where PARTY_PREFERENCE_ID = X_PARTY_PREFERENCE_ID
219 and OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER;
220
221 if (sql%notfound) then
222 raise no_data_found;
223 end if;
224
225 end DELETE_ROW;
226
227 end HZ_PARTY_PREFERENCES_PKG;
[Home] [Help]