[Home] [Help]
PACKAGE BODY: APPS.CSI_TXN_TYPES_PKG
Source
1 PACKAGE BODY CSI_TXN_TYPES_PKG as
2 /* $Header: csittstb.pls 115.8 2002/11/12 00:25:58 rmamidip noship $ */
3 -- Start of Comments
4 -- Package name : CSI_TXN_TYPES_PKG
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSI_TXN_TYPES_PKG';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csittstb.pls';
13
14 PROCEDURE Insert_Row(
15 px_TRANSACTION_TYPE_ID IN OUT NOCOPY NUMBER,
16 p_SOURCE_APPLICATION_ID NUMBER,
17 p_SOURCE_TRANSACTION_TYPE VARCHAR2,
18 p_SOURCE_TXN_TYPE_NAME VARCHAR2,
19 p_DESCRIPTION VARCHAR2,
20 p_SOURCE_OBJECT_CODE VARCHAR2,
21 p_IN_OUT_FLAG VARCHAR2,
22 p_CREATED_BY NUMBER,
23 p_CREATION_DATE DATE,
24 p_LAST_UPDATED_BY NUMBER,
25 p_LAST_UPDATE_DATE DATE,
26 p_LAST_UPDATE_LOGIN NUMBER,
27 p_OBJECT_VERSION_NUMBER NUMBER,
28 p_SUB_TYPE_ID NUMBER,
29 p_DEFAULT_FLAG VARCHAR2,
30 p_UPDATE_IB_FLAG VARCHAR2,
31 p_SEEDED_FLAG VARCHAR2
32 )
33
34 IS
35 CURSOR C2 IS SELECT CSI_TXN_TYPES_S.nextval FROM sys.dual;
36 BEGIN
37 If (px_TRANSACTION_TYPE_ID IS NULL) OR (px_TRANSACTION_TYPE_ID = FND_API.G_MISS_NUM) then
38 OPEN C2;
39 FETCH C2 INTO px_TRANSACTION_TYPE_ID;
40 CLOSE C2;
41 End If;
42 INSERT INTO CSI_TXN_TYPES(
43 TRANSACTION_TYPE_ID,
44 SOURCE_APPLICATION_ID,
45 SOURCE_TRANSACTION_TYPE,
46 SOURCE_TXN_TYPE_NAME,
47 DESCRIPTION,
48 SOURCE_OBJECT_CODE,
49 IN_OUT_FLAG,
50 CREATED_BY,
51 CREATION_DATE,
52 LAST_UPDATED_BY,
53 LAST_UPDATE_DATE,
54 LAST_UPDATE_LOGIN,
55 OBJECT_VERSION_NUMBER,
56 SEEDED_FLAG
57 ) VALUES (
58 px_TRANSACTION_TYPE_ID,
59 decode( p_SOURCE_APPLICATION_ID, FND_API.G_MISS_NUM, NULL, p_SOURCE_APPLICATION_ID),
60 decode( p_SOURCE_TRANSACTION_TYPE, FND_API.G_MISS_CHAR, NULL, p_SOURCE_TRANSACTION_TYPE),
61 decode( p_SOURCE_TXN_TYPE_NAME, FND_API.G_MISS_CHAR, NULL, p_SOURCE_TXN_TYPE_NAME),
62 decode( p_DESCRIPTION, FND_API.G_MISS_CHAR, NULL, p_DESCRIPTION),
63 decode( p_SOURCE_OBJECT_CODE, FND_API.G_MISS_CHAR, NULL, p_SOURCE_OBJECT_CODE),
64 decode( p_IN_OUT_FLAG, FND_API.G_MISS_CHAR, NULL, p_IN_OUT_FLAG),
65 decode( p_CREATED_BY, FND_API.G_MISS_NUM, NULL, p_CREATED_BY),
66 decode( p_CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_CREATION_DATE),
67 decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATED_BY),
68 decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_LAST_UPDATE_DATE),
69 decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATE_LOGIN),
70 decode( p_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, p_OBJECT_VERSION_NUMBER),
71 'N'
72 );
73
74 INSERT INTO CSI_SOURCE_IB_TYPES(
75 TRANSACTION_TYPE_ID,
76 SUB_TYPE_ID,
77 DEFAULT_FLAG,
78 UPDATE_IB_FLAG,
79 CREATED_BY,
80 CREATION_DATE,
81 LAST_UPDATED_BY,
82 LAST_UPDATE_DATE,
83 LAST_UPDATE_LOGIN,
84 SEEDED_FLAG,
85 OBJECT_VERSION_NUMBER
86 ) VALUES (
87 px_TRANSACTION_TYPE_ID,
88 decode( p_SUB_TYPE_ID, FND_API.G_MISS_NUM, NULL, p_SUB_TYPE_ID),
89 decode( p_DEFAULT_FLAG, FND_API.G_MISS_CHAR, NULL, p_DEFAULT_FLAG),
90 decode( p_UPDATE_IB_FLAG, FND_API.G_MISS_CHAR, NULL, p_UPDATE_IB_FLAG),
91 decode( p_CREATED_BY, FND_API.G_MISS_NUM, NULL, p_CREATED_BY),
92 decode( p_CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_CREATION_DATE),
93 decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATED_BY),
94 decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_LAST_UPDATE_DATE),
95 decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATE_LOGIN),
96 'N',
97 1 );
98
99
100 End Insert_Row;
101
102 PROCEDURE Update_Row(
103 p_TRANSACTION_TYPE_ID NUMBER,
104 p_SOURCE_APPLICATION_ID NUMBER,
105 p_SOURCE_TRANSACTION_TYPE VARCHAR2,
106 p_SOURCE_TXN_TYPE_NAME VARCHAR2,
107 p_DESCRIPTION VARCHAR2,
108 p_SOURCE_OBJECT_CODE VARCHAR2,
109 p_IN_OUT_FLAG VARCHAR2,
110 p_CREATED_BY NUMBER,
111 p_CREATION_DATE DATE,
112 p_LAST_UPDATED_BY NUMBER,
113 p_LAST_UPDATE_DATE DATE,
114 p_LAST_UPDATE_LOGIN NUMBER,
115 p_OBJECT_VERSION_NUMBER NUMBER,
116 p_SUB_TYPE_ID NUMBER,
117 p_DEFAULT_FLAG VARCHAR2,
118 p_UPDATE_IB_FLAG VARCHAR2)
119
120
121 IS
122 BEGIN
123 Update CSI_TXN_TYPES
124 SET
125 SOURCE_APPLICATION_ID = decode( p_SOURCE_APPLICATION_ID, FND_API.G_MISS_NUM, SOURCE_APPLICATION_ID, p_SOURCE_APPLICATION_ID),
126 SOURCE_TRANSACTION_TYPE = decode( p_SOURCE_TRANSACTION_TYPE, FND_API.G_MISS_CHAR, SOURCE_TRANSACTION_TYPE, p_SOURCE_TRANSACTION_TYPE),
127 SOURCE_TXN_TYPE_NAME = decode( p_SOURCE_TXN_TYPE_NAME, FND_API.G_MISS_CHAR, SOURCE_TXN_TYPE_NAME, p_SOURCE_TXN_TYPE_NAME),
128 DESCRIPTION = decode( p_DESCRIPTION, FND_API.G_MISS_CHAR, DESCRIPTION, p_DESCRIPTION),
129 SOURCE_OBJECT_CODE = decode( p_SOURCE_OBJECT_CODE, FND_API.G_MISS_CHAR, SOURCE_OBJECT_CODE, p_SOURCE_OBJECT_CODE),
130 IN_OUT_FLAG = decode( p_IN_OUT_FLAG, FND_API.G_MISS_CHAR, IN_OUT_FLAG, p_IN_OUT_FLAG),
131 CREATED_BY = decode( p_CREATED_BY, FND_API.G_MISS_NUM, CREATED_BY, p_CREATED_BY),
132 CREATION_DATE = decode( p_CREATION_DATE, FND_API.G_MISS_DATE, CREATION_DATE, p_CREATION_DATE),
133 LAST_UPDATED_BY = decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, LAST_UPDATED_BY, p_LAST_UPDATED_BY),
134 LAST_UPDATE_DATE = decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, LAST_UPDATE_DATE, p_LAST_UPDATE_DATE),
135 LAST_UPDATE_LOGIN = decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, LAST_UPDATE_LOGIN, p_LAST_UPDATE_LOGIN),
136 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1
137 --OBJECT_VERSION_NUMBER = decode( p_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, OBJECT_VERSION_NUMBER, p_OBJECT_VERSION_NUMBER)
138 where TRANSACTION_TYPE_ID = p_TRANSACTION_TYPE_ID;
139
140 Update CSI_SOURCE_IB_TYPES
141 SET
142 DEFAULT_FLAG = decode( p_DEFAULT_FLAG, FND_API.G_MISS_CHAR, DEFAULT_FLAG, p_DEFAULT_FLAG),
143 UPDATE_IB_FLAG = decode( p_UPDATE_IB_FLAG, FND_API.G_MISS_CHAR, UPDATE_IB_FLAG, p_UPDATE_IB_FLAG),
144 CREATED_BY = decode( p_CREATED_BY, FND_API.G_MISS_NUM, CREATED_BY, p_CREATED_BY),
145 CREATION_DATE = decode( p_CREATION_DATE, FND_API.G_MISS_DATE, CREATION_DATE, p_CREATION_DATE),
146 LAST_UPDATED_BY = decode( p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, LAST_UPDATED_BY, p_LAST_UPDATED_BY),
147 LAST_UPDATE_DATE = decode( p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, LAST_UPDATE_DATE, p_LAST_UPDATE_DATE),
148 LAST_UPDATE_LOGIN = decode( p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, LAST_UPDATE_LOGIN, p_LAST_UPDATE_LOGIN),
149 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1
150 where TRANSACTION_TYPE_ID = p_TRANSACTION_TYPE_ID
151 and SUB_TYPE_ID = p_sub_type_id;
152
153 If (SQL%NOTFOUND) then
154 RAISE NO_DATA_FOUND;
155 End If;
156 END Update_Row;
157
158 PROCEDURE Delete_Row(
159 p_TRANSACTION_TYPE_ID NUMBER,
160 p_SUB_TYPE_ID NUMBER)
161 IS
162 BEGIN
163 /* Commented as the Transaction Type should not be deleted */
164
165 -- DELETE FROM CSI_TXN_TYPES
166 -- WHERE TRANSACTION_TYPE_ID = p_TRANSACTION_TYPE_ID;
167
168 DELETE FROM CSI_SOURCE_IB_TYPES
169 WHERE TRANSACTION_TYPE_ID = p_TRANSACTION_TYPE_ID
170 AND SUB_TYPE_ID = p_SUB_TYPE_ID;
171
172 If (SQL%NOTFOUND) then
173 RAISE NO_DATA_FOUND;
174 End If;
175 END Delete_Row;
176
177 PROCEDURE Lock_Row(
178 p_TRANSACTION_TYPE_ID NUMBER,
179 p_SOURCE_APPLICATION_ID NUMBER,
180 p_SOURCE_TRANSACTION_TYPE VARCHAR2,
181 p_SOURCE_TXN_TYPE_NAME VARCHAR2,
182 p_DESCRIPTION VARCHAR2,
183 p_SOURCE_OBJECT_CODE VARCHAR2,
184 p_IN_OUT_FLAG VARCHAR2,
185 p_CREATED_BY NUMBER,
186 p_CREATION_DATE DATE,
187 p_LAST_UPDATED_BY NUMBER,
188 p_LAST_UPDATE_DATE DATE,
189 p_LAST_UPDATE_LOGIN NUMBER,
190 p_OBJECT_VERSION_NUMBER NUMBER,
191 p_SUB_TYPE_ID NUMBER,
192 p_DEFAULT_FLAG VARCHAR2,
193 p_UPDATE_IB_FLAG VARCHAR2,
194 p_SEEDED_FLAG VARCHAR2)
195
196 IS
197 CURSOR C IS
198 SELECT *
199 FROM CSI_TXN_TYPES
200 WHERE TRANSACTION_TYPE_ID = p_TRANSACTION_TYPE_ID
201 FOR UPDATE of TRANSACTION_TYPE_ID NOWAIT;
202 Recinfo C%ROWTYPE;
203
204 CURSOR C1 IS
205 SELECT *
206 FROM CSI_SOURCE_IB_TYPES
207 WHERE TRANSACTION_TYPE_ID = p_TRANSACTION_TYPE_ID
208 AND SUB_TYPE_ID = p_SUB_TYPE_ID
209 FOR UPDATE NOWAIT;
210
211 BEGIN
212 OPEN C;
213 FETCH C INTO Recinfo;
214 If (C%NOTFOUND) then
215 CLOSE C;
216 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
217 APP_EXCEPTION.RAISE_EXCEPTION;
218 End If;
219 CLOSE C;
220 if (
221 ( Recinfo.TRANSACTION_TYPE_ID = p_TRANSACTION_TYPE_ID)
222 AND ( ( Recinfo.SOURCE_APPLICATION_ID = p_SOURCE_APPLICATION_ID)
223 OR ( ( Recinfo.SOURCE_APPLICATION_ID IS NULL )
224 AND ( p_SOURCE_APPLICATION_ID IS NULL )))
225 AND ( ( Recinfo.SOURCE_TRANSACTION_TYPE = p_SOURCE_TRANSACTION_TYPE)
226 OR ( ( Recinfo.SOURCE_TRANSACTION_TYPE IS NULL )
227 AND ( p_SOURCE_TRANSACTION_TYPE IS NULL )))
228 AND ( ( Recinfo.SOURCE_TXN_TYPE_NAME = p_SOURCE_TXN_TYPE_NAME)
229 OR ( ( Recinfo.SOURCE_TXN_TYPE_NAME IS NULL )
230 AND ( p_SOURCE_TXN_TYPE_NAME IS NULL )))
231 AND ( ( Recinfo.DESCRIPTION = p_DESCRIPTION)
232 OR ( ( Recinfo.DESCRIPTION IS NULL )
233 AND ( p_DESCRIPTION IS NULL )))
234 AND ( ( Recinfo.SOURCE_OBJECT_CODE = p_SOURCE_OBJECT_CODE)
235 OR ( ( Recinfo.SOURCE_OBJECT_CODE IS NULL )
236 AND ( p_SOURCE_OBJECT_CODE IS NULL )))
237 AND ( ( Recinfo.IN_OUT_FLAG = p_IN_OUT_FLAG)
238 OR ( ( Recinfo.IN_OUT_FLAG IS NULL )
239 AND ( p_IN_OUT_FLAG IS NULL )))
240 AND ( ( Recinfo.CREATED_BY = p_CREATED_BY)
241 OR ( ( Recinfo.CREATED_BY IS NULL )
242 AND ( p_CREATED_BY IS NULL )))
243 AND ( ( Recinfo.CREATION_DATE = p_CREATION_DATE)
244 OR ( ( Recinfo.CREATION_DATE IS NULL )
245 AND ( p_CREATION_DATE IS NULL )))
246 AND ( ( Recinfo.LAST_UPDATED_BY = p_LAST_UPDATED_BY)
247 OR ( ( Recinfo.LAST_UPDATED_BY IS NULL )
248 AND ( p_LAST_UPDATED_BY IS NULL )))
249 AND ( ( Recinfo.LAST_UPDATE_DATE = p_LAST_UPDATE_DATE)
250 OR ( ( Recinfo.LAST_UPDATE_DATE IS NULL )
251 AND ( p_LAST_UPDATE_DATE IS NULL )))
252 AND ( ( Recinfo.LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN)
253 OR ( ( Recinfo.LAST_UPDATE_LOGIN IS NULL )
254 AND ( p_LAST_UPDATE_LOGIN IS NULL )))
255 AND ( ( Recinfo.OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER)
256 OR ( ( Recinfo.OBJECT_VERSION_NUMBER IS NULL )
257 AND ( p_OBJECT_VERSION_NUMBER IS NULL )))
258 /* AND ( ( Recinfo.SEEDED_FLAG = p_SEEDED_FLAG)
259 OR ( ( Recinfo.SEEDED_FLAG IS NULL )
260 AND ( p_SEEDED_FLAG IS NULL ))) */ --Commented for Bug 2419385. Record updated . Pl. requery to check message.
261 ) then
262 return;
263 else
264 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
265 APP_EXCEPTION.RAISE_EXCEPTION;
266 End If;
267
268
269 for sibtyp in c1 loop
270 if(
271 ( sibtyp.transaction_type_id = p_transaction_type_id
275 AND ( p_default_flag IS NULL )))
272 AND sibtyp.sub_type_id = p_sub_type_id )
273 AND( ( sibtyp.default_flag = p_default_flag )
274 OR ( ( sibtyp.default_flag IS NULL )
276 AND( ( sibtyp.update_ib_flag = p_update_ib_flag )
277 OR ( ( sibtyp.update_ib_flag IS NULL )
278 AND ( p_update_ib_flag IS NULL )))
279 AND ( ( sibtyp.CREATED_BY = p_CREATED_BY)
280 OR ( ( sibtyp.CREATED_BY IS NULL )
281 AND ( p_CREATED_BY IS NULL )))
282 AND ( ( sibtyp.CREATION_DATE = p_CREATION_DATE)
283 OR ( ( sibtyp.CREATION_DATE IS NULL )
284 AND ( p_CREATION_DATE IS NULL )))
285 AND ( ( sibtyp.LAST_UPDATED_BY = p_LAST_UPDATED_BY)
286 OR ( ( sibtyp.LAST_UPDATED_BY IS NULL )
287 AND ( p_LAST_UPDATED_BY IS NULL )))
288 AND ( ( sibtyp.LAST_UPDATE_DATE = p_LAST_UPDATE_DATE)
289 OR ( ( sibtyp.LAST_UPDATE_DATE IS NULL )
290 AND ( p_LAST_UPDATE_DATE IS NULL )))
291 AND ( ( sibtyp.LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN)
292 OR ( ( sibtyp.LAST_UPDATE_LOGIN IS NULL )
293 AND ( p_LAST_UPDATE_LOGIN IS NULL )))
294 AND ( (sibtyp.SEEDED_FLAG = p_SEEDED_FLAG)
295 OR ( ( sibtyp.SEEDED_FLAG IS NULL )
296 AND ( p_SEEDED_FLAG IS NULL )))
297 AND ( ( sibtyp.OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER)
298 OR ( ( sibtyp.OBJECT_VERSION_NUMBER IS NULL )
299 AND ( p_OBJECT_VERSION_NUMBER IS NULL )))
300
301 ) then
302 return;
303 else
304 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
305 APP_EXCEPTION.RAISE_EXCEPTION;
306 End If;
307 End Loop;
308
309 END Lock_Row;
310
311 End CSI_TXN_TYPES_PKG;