[Home] [Help]
PACKAGE BODY: APPS.HZ_CLASS_CODE_RELATIONS_PKG
Source
1 PACKAGE BODY HZ_CLASS_CODE_RELATIONS_PKG AS
2 /*$Header: ARHCCRTB.pls 115.4 2002/11/21 19:38:06 sponnamb ship $ */
3
4 PROCEDURE Insert_Row (
5 X_CLASS_CATEGORY IN VARCHAR2,
6 X_CLASS_CODE IN VARCHAR2,
7 X_SUB_CLASS_CODE IN VARCHAR2,
8 X_START_DATE_ACTIVE IN DATE,
9 X_END_DATE_ACTIVE IN DATE,
10 X_OBJECT_VERSION_NUMBER IN NUMBER,
11 X_CREATED_BY_MODULE IN VARCHAR2,
12 X_APPLICATION_ID IN NUMBER
13 ) IS
14
15 BEGIN
16
17 INSERT INTO HZ_CLASS_CODE_RELATIONS (
18 CLASS_CATEGORY,
19 CLASS_CODE,
20 SUB_CLASS_CODE,
21 START_DATE_ACTIVE,
22 END_DATE_ACTIVE,
23 CREATED_BY,
24 CREATION_DATE,
25 LAST_UPDATE_LOGIN,
26 LAST_UPDATE_DATE,
27 LAST_UPDATED_BY,
28 OBJECT_VERSION_NUMBER,
29 CREATED_BY_MODULE,
30 APPLICATION_ID
31 )
32 VALUES (
33 DECODE( X_CLASS_CATEGORY, FND_API.G_MISS_CHAR, NULL, X_CLASS_CATEGORY ),
34 DECODE( X_CLASS_CODE, FND_API.G_MISS_CHAR, NULL, X_CLASS_CODE ),
35 DECODE( X_SUB_CLASS_CODE, FND_API.G_MISS_CHAR, NULL, X_SUB_CLASS_CODE ),
36 DECODE( X_START_DATE_ACTIVE, FND_API.G_MISS_DATE, TO_DATE( NULL ), X_START_DATE_ACTIVE ),
37 DECODE( X_END_DATE_ACTIVE, FND_API.G_MISS_DATE, TO_DATE( NULL ), X_END_DATE_ACTIVE ),
38 HZ_UTILITY_V2PUB.CREATED_BY,
39 HZ_UTILITY_V2PUB.CREATION_DATE,
40 HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
41 HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
42 HZ_UTILITY_V2PUB.LAST_UPDATED_BY,
43 DECODE( X_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, X_OBJECT_VERSION_NUMBER ),
44 DECODE( X_CREATED_BY_MODULE, FND_API.G_MISS_CHAR, NULL, X_CREATED_BY_MODULE ),
45 DECODE( X_APPLICATION_ID, FND_API.G_MISS_NUM, NULL, X_APPLICATION_ID )
46 );
47
48 END Insert_Row;
49
50 PROCEDURE Update_Row (
51 X_CLASS_CATEGORY IN VARCHAR2,
52 X_CLASS_CODE IN VARCHAR2,
53 X_SUB_CLASS_CODE IN VARCHAR2,
54 X_START_DATE_ACTIVE IN DATE,
55 X_END_DATE_ACTIVE IN DATE,
56 X_OBJECT_VERSION_NUMBER IN NUMBER,
57 X_CREATED_BY_MODULE IN VARCHAR2,
58 X_APPLICATION_ID IN NUMBER
59 ) IS
60
61 BEGIN
62
63 UPDATE HZ_CLASS_CODE_RELATIONS SET
64 END_DATE_ACTIVE = DECODE( X_END_DATE_ACTIVE, NULL, END_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL, X_END_DATE_ACTIVE ),
65 LAST_UPDATE_LOGIN = HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
66 LAST_UPDATE_DATE = HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
67 LAST_UPDATED_BY = HZ_UTILITY_V2PUB.LAST_UPDATED_BY,
68 OBJECT_VERSION_NUMBER = DECODE( X_OBJECT_VERSION_NUMBER, NULL, OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, X_OBJECT_VERSION_NUMBER ),
69 CREATED_BY_MODULE = DECODE( X_CREATED_BY_MODULE, NULL, CREATED_BY_MODULE, FND_API.G_MISS_CHAR, NULL, X_CREATED_BY_MODULE ),
70 APPLICATION_ID = DECODE( X_APPLICATION_ID, NULL, APPLICATION_ID, FND_API.G_MISS_NUM, NULL, X_APPLICATION_ID )
71 WHERE CLASS_CATEGORY = X_CLASS_CATEGORY
72 AND CLASS_CODE = X_CLASS_CODE
73 AND SUB_CLASS_CODE = X_SUB_CLASS_CODE
74 AND START_DATE_ACTIVE = X_START_DATE_ACTIVE;
75
76 IF ( SQL%NOTFOUND ) THEN
77 RAISE NO_DATA_FOUND;
78 END IF;
79
80 END Update_Row;
81
82 PROCEDURE Lock_Row (
83 X_Rowid IN OUT NOCOPY VARCHAR2,
84 X_CLASS_CATEGORY IN VARCHAR2,
85 X_CLASS_CODE IN VARCHAR2,
86 X_SUB_CLASS_CODE IN VARCHAR2,
87 X_START_DATE_ACTIVE IN DATE,
88 X_END_DATE_ACTIVE IN DATE,
89 X_CREATED_BY IN NUMBER,
90 X_CREATION_DATE IN DATE,
91 X_LAST_UPDATE_LOGIN IN NUMBER,
92 X_LAST_UPDATE_DATE IN DATE,
93 X_LAST_UPDATED_BY IN NUMBER,
94 X_OBJECT_VERSION_NUMBER IN NUMBER,
95 X_CREATED_BY_MODULE IN VARCHAR2,
96 X_APPLICATION_ID IN NUMBER
97 ) IS
98
99 CURSOR C IS
100 SELECT * FROM HZ_CLASS_CODE_RELATIONS
101 WHERE ROWID = x_Rowid
102 FOR UPDATE NOWAIT;
103 Recinfo C%ROWTYPE;
104
105 BEGIN
106
107 OPEN C;
108 FETCH C INTO Recinfo;
109 IF ( C%NOTFOUND ) THEN
110 CLOSE C;
111 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
112 APP_EXCEPTION.RAISE_EXCEPTION;
113 END IF;
114 CLOSE C;
115
116 IF (
117 ( ( Recinfo.CLASS_CATEGORY = X_CLASS_CATEGORY )
118 OR ( ( Recinfo.CLASS_CATEGORY IS NULL )
119 AND ( X_CLASS_CATEGORY IS NULL ) ) )
120 AND ( ( Recinfo.CLASS_CODE = X_CLASS_CODE )
121 OR ( ( Recinfo.CLASS_CODE IS NULL )
122 AND ( X_CLASS_CODE IS NULL ) ) )
123 AND ( ( Recinfo.SUB_CLASS_CODE = X_SUB_CLASS_CODE )
124 OR ( ( Recinfo.SUB_CLASS_CODE IS NULL )
125 AND ( X_SUB_CLASS_CODE IS NULL ) ) )
126 AND ( ( Recinfo.START_DATE_ACTIVE = X_START_DATE_ACTIVE )
127 OR ( ( Recinfo.START_DATE_ACTIVE IS NULL )
128 AND ( X_START_DATE_ACTIVE IS NULL ) ) )
129 AND ( ( Recinfo.END_DATE_ACTIVE = X_END_DATE_ACTIVE )
130 OR ( ( Recinfo.END_DATE_ACTIVE IS NULL )
131 AND ( X_END_DATE_ACTIVE IS NULL ) ) )
132 AND ( ( Recinfo.CREATED_BY = X_CREATED_BY )
133 OR ( ( Recinfo.CREATED_BY IS NULL )
134 AND ( X_CREATED_BY IS NULL ) ) )
135 AND ( ( Recinfo.CREATION_DATE = X_CREATION_DATE )
136 OR ( ( Recinfo.CREATION_DATE IS NULL )
137 AND ( X_CREATION_DATE IS NULL ) ) )
138 AND ( ( Recinfo.LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN )
139 OR ( ( Recinfo.LAST_UPDATE_LOGIN IS NULL )
140 AND ( X_LAST_UPDATE_LOGIN IS NULL ) ) )
141 AND ( ( Recinfo.LAST_UPDATE_DATE = X_LAST_UPDATE_DATE )
142 OR ( ( Recinfo.LAST_UPDATE_DATE IS NULL )
143 AND ( X_LAST_UPDATE_DATE IS NULL ) ) )
144 AND ( ( Recinfo.LAST_UPDATED_BY = X_LAST_UPDATED_BY )
145 OR ( ( Recinfo.LAST_UPDATED_BY IS NULL )
146 AND ( X_LAST_UPDATED_BY IS NULL ) ) )
147 AND ( ( Recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER )
148 OR ( ( Recinfo.OBJECT_VERSION_NUMBER IS NULL )
149 AND ( X_OBJECT_VERSION_NUMBER IS NULL ) ) )
150 AND ( ( Recinfo.CREATED_BY_MODULE = X_CREATED_BY_MODULE )
151 OR ( ( Recinfo.CREATED_BY_MODULE IS NULL )
152 AND ( X_CREATED_BY_MODULE IS NULL ) ) )
153 AND ( ( Recinfo.APPLICATION_ID = X_APPLICATION_ID )
154 OR ( ( Recinfo.APPLICATION_ID IS NULL )
155 AND ( X_APPLICATION_ID IS NULL ) ) )
156 ) THEN
157 RETURN;
158 ELSE
159 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
160 APP_EXCEPTION.RAISE_EXCEPTION;
161 END IF;
162
163 END Lock_Row;
164
165 PROCEDURE Select_Row (
166 X_CLASS_CATEGORY IN OUT NOCOPY VARCHAR2,
167 X_CLASS_CODE IN OUT NOCOPY VARCHAR2,
168 X_SUB_CLASS_CODE IN OUT NOCOPY VARCHAR2,
169 X_START_DATE_ACTIVE IN OUT NOCOPY DATE,
170 X_END_DATE_ACTIVE OUT NOCOPY DATE,
171 X_CREATED_BY_MODULE OUT NOCOPY VARCHAR2,
172 X_APPLICATION_ID OUT NOCOPY NUMBER
173 ) IS
174
175 BEGIN
176
177 SELECT
178 NVL( CLASS_CATEGORY, FND_API.G_MISS_CHAR ),
179 NVL( CLASS_CODE, FND_API.G_MISS_CHAR ),
180 NVL( SUB_CLASS_CODE, FND_API.G_MISS_CHAR ),
181 NVL( START_DATE_ACTIVE, FND_API.G_MISS_DATE ),
182 NVL( END_DATE_ACTIVE, FND_API.G_MISS_DATE ),
183 NVL( CREATED_BY_MODULE, FND_API.G_MISS_CHAR ),
184 NVL( APPLICATION_ID, FND_API.G_MISS_NUM )
185 INTO
186 X_CLASS_CATEGORY,
187 X_CLASS_CODE,
188 X_SUB_CLASS_CODE,
189 X_START_DATE_ACTIVE,
190 X_END_DATE_ACTIVE,
191 X_CREATED_BY_MODULE,
192 X_APPLICATION_ID
193 FROM HZ_CLASS_CODE_RELATIONS
194 WHERE CLASS_CATEGORY = X_CLASS_CATEGORY
195 AND CLASS_CODE = X_CLASS_CODE
196 AND SUB_CLASS_CODE = X_SUB_CLASS_CODE
197 AND START_DATE_ACTIVE = X_START_DATE_ACTIVE;
198
199 EXCEPTION
200 WHEN NO_DATA_FOUND THEN
201 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NO_RECORD' );
202 FND_MESSAGE.SET_TOKEN( 'RECORD', 'class_code_relation_rec');
203 FND_MESSAGE.SET_TOKEN( 'VALUE', X_CLASS_CATEGORY );
204 FND_MSG_PUB.ADD;
205 RAISE FND_API.G_EXC_ERROR;
206
207 END Select_Row;
208
209 PROCEDURE Delete_Row (
210 X_CLASS_CATEGORY IN VARCHAR2,
211 X_CLASS_CODE IN VARCHAR2,
212 X_SUB_CLASS_CODE IN VARCHAR2,
213 X_START_DATE_ACTIVE IN DATE
214
215 ) IS
216
217 BEGIN
218
219 DELETE FROM HZ_CLASS_CODE_RELATIONS
220 WHERE CLASS_CATEGORY = X_CLASS_CATEGORY
221 AND CLASS_CODE = X_CLASS_CODE
222 AND SUB_CLASS_CODE = X_SUB_CLASS_CODE
223 AND START_DATE_ACTIVE = X_START_DATE_ACTIVE;
224
225 IF ( SQL%NOTFOUND ) THEN
226 RAISE NO_DATA_FOUND;
227 END IF;
228
229 END Delete_Row;
230
231 END HZ_CLASS_CODE_RELATIONS_PKG;