[Home] [Help]
PACKAGE BODY: APPS.FV_BUDGET_DISTRIBUTION_HDR_PKG
Source
1 PACKAGE BODY FV_BUDGET_DISTRIBUTION_HDR_PKG as
2 /* $Header: FVBEHDRB.pls 120.6 2010/08/04 13:28:55 amaddula ship $ */
3 g_module_name VARCHAR2(100) := 'fv.plsql.FV_BUDGET_DISTRIBUTION_HDR_PKG.';
4
5 PROCEDURE Insert_Row(X_ROWID IN OUT NOCOPY VARCHAR2,
6 X_DISTRIBUTION_ID NUMBER,
7 X_FUND_VALUE VARCHAR2,
8 X_SET_OF_BOOKS_ID NUMBER,
9 X_LAST_UPDATE_DATE DATE,
10 X_LAST_UPDATED_BY NUMBER,
11 X_CREATION_DATE DATE,
12 X_CREATED_BY NUMBER,
13 X_LAST_UPDATE_LOGIN NUMBER,
14 X_ATTRIBUTE1 VARCHAR2,
15 X_ATTRIBUTE2 VARCHAR2,
16 X_ATTRIBUTE3 VARCHAR2,
17 X_ATTRIBUTE4 VARCHAR2,
18 X_ATTRIBUTE5 VARCHAR2,
19 X_ATTRIBUTE6 VARCHAR2,
20 X_ATTRIBUTE7 VARCHAR2,
21 X_ATTRIBUTE8 VARCHAR2,
22 X_ATTRIBUTE9 VARCHAR2,
23 X_ATTRIBUTE10 VARCHAR2,
24 X_ATTRIBUTE11 VARCHAR2,
25 X_ATTRIBUTE12 VARCHAR2,
26 X_ATTRIBUTE13 VARCHAR2,
27 X_ATTRIBUTE14 VARCHAR2,
28 X_ATTRIBUTE15 VARCHAR2,
29 X_ATTRIBUTE_CATEGORY VARCHAR2,
30 X_ORG_ID NUMBER,
31 X_FACTS_PRGM_SEGMENT VARCHAR2,
32 X_TREASURY_SYMBOL_ID NUMBER,
33 X_FREEZE_DEFINITION_FLAG VARCHAR2,
34 X_AUTO_CREATE_FLAG VARCHAR2
35 ) IS
36 l_module_name VARCHAR2(200) := g_module_name || 'Insert_Row';
37 l_errbuf VARCHAR2(1024);
38
39 CURSOR C IS SELECT ROWID FROM FV_BUDGET_DISTRIBUTION_HDR
40 WHERE DISTRIBUTION_ID = X_DISTRIBUTION_ID;
41 BEGIN
42 INSERT INTO FV_BUDGET_DISTRIBUTION_HDR(
43 DISTRIBUTION_ID,
44 FUND_VALUE ,
45 SET_OF_BOOKS_ID ,
46 LAST_UPDATE_DATE,
47 LAST_UPDATED_BY ,
48 CREATION_DATE,
49 CREATED_BY ,
50 LAST_UPDATE_LOGIN ,
51 ATTRIBUTE1 ,
52 ATTRIBUTE2 ,
53 ATTRIBUTE3 ,
54 ATTRIBUTE4 ,
55 ATTRIBUTE5 ,
56 ATTRIBUTE6 ,
57 ATTRIBUTE7 ,
58 ATTRIBUTE8 ,
59 ATTRIBUTE9 ,
60 ATTRIBUTE10 ,
61 ATTRIBUTE11 ,
62 ATTRIBUTE12 ,
63 ATTRIBUTE13 ,
64 ATTRIBUTE14 ,
65 ATTRIBUTE15 ,
66 ATTRIBUTE_CATEGORY ,
67 ORG_ID ,
68 FACTS_PRGM_SEGMENT ,
69 TREASURY_SYMBOL_ID,
70 FREEZE_DEFINITION_FLAG,
71 AUTO_CREATE_FLAG)
72 VALUES(
73 X_DISTRIBUTION_ID ,
74 X_FUND_VALUE ,
75 X_SET_OF_BOOKS_ID ,
76 X_LAST_UPDATE_DATE ,
77 X_LAST_UPDATED_BY ,
78 X_CREATION_DATE ,
79 X_CREATED_BY ,
80 X_LAST_UPDATE_LOGIN ,
81 X_ATTRIBUTE1 ,
82 X_ATTRIBUTE2 ,
83 X_ATTRIBUTE3 ,
84 X_ATTRIBUTE4 ,
85 X_ATTRIBUTE5 ,
86 X_ATTRIBUTE6 ,
87 X_ATTRIBUTE7 ,
88 X_ATTRIBUTE8 ,
89 X_ATTRIBUTE9 ,
90 X_ATTRIBUTE10 ,
91 X_ATTRIBUTE11 ,
92 X_ATTRIBUTE12 ,
93 X_ATTRIBUTE13 ,
94 X_ATTRIBUTE14 ,
95 X_ATTRIBUTE15 ,
96 X_ATTRIBUTE_CATEGORY ,
97 X_ORG_ID ,
98 X_FACTS_PRGM_SEGMENT ,
99 X_TREASURY_SYMBOL_ID,
100 X_FREEZE_DEFINITION_FLAG,
101 X_AUTO_CREATE_FLAG);
102 OPEN C;
103 FETCH C INTO X_ROWID;
104 IF (C%NOTFOUND) THEN
105 CLOSE C;
106 Raise NO_DATA_FOUND;
107 END IF;
108 CLOSE C;
109 EXCEPTION
110 WHEN OTHERS THEN
111 l_errbuf := SQLERRM;
112 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',l_errbuf);
113 RAISE;
114 END Insert_Row;
115
116 PROCEDURE Update_Row(X_ROWID VARCHAR2,
117 X_DISTRIBUTION_ID NUMBER,
118 X_FUND_VALUE VARCHAR2,
119 X_SET_OF_BOOKS_ID NUMBER,
120 X_LAST_UPDATE_DATE DATE,
121 X_LAST_UPDATED_BY NUMBER,
122 X_CREATION_DATE DATE,
123 X_CREATED_BY NUMBER,
124 X_LAST_UPDATE_LOGIN NUMBER,
125 X_ATTRIBUTE1 VARCHAR2,
126 X_ATTRIBUTE2 VARCHAR2,
127 X_ATTRIBUTE3 VARCHAR2,
128 X_ATTRIBUTE4 VARCHAR2,
129 X_ATTRIBUTE5 VARCHAR2,
130 X_ATTRIBUTE6 VARCHAR2,
131 X_ATTRIBUTE7 VARCHAR2,
132 X_ATTRIBUTE8 VARCHAR2,
133 X_ATTRIBUTE9 VARCHAR2,
134 X_ATTRIBUTE10 VARCHAR2,
135 X_ATTRIBUTE11 VARCHAR2,
136 X_ATTRIBUTE12 VARCHAR2,
137 X_ATTRIBUTE13 VARCHAR2,
138 X_ATTRIBUTE14 VARCHAR2,
139 X_ATTRIBUTE15 VARCHAR2,
140 X_ATTRIBUTE_CATEGORY VARCHAR2,
141 X_ORG_ID NUMBER,
142 X_FACTS_PRGM_SEGMENT VARCHAR2,
143 X_TREASURY_SYMBOL_ID NUMBER,
144 X_FREEZE_DEFINITION_FLAG VARCHAR2,
145 X_AUTO_CREATE_FLAG VARCHAR2
146 ) IS
147 l_module_name VARCHAR2(200) := g_module_name || 'Update_Row';
148 l_errbuf VARCHAR2(1024);
149 BEGIN
150 UPDATE FV_BUDGET_DISTRIBUTION_HDR
151 SET
152 DISTRIBUTION_ID = X_DISTRIBUTION_ID,
153 FUND_VALUE = X_FUND_VALUE,
154 SET_OF_BOOKS_ID = X_SET_OF_BOOKS_ID,
155 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
156 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
157 CREATION_DATE = X_CREATION_DATE,
158 CREATED_BY = X_CREATED_BY ,
159 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN ,
160 ATTRIBUTE1 = X_ATTRIBUTE1 ,
161 ATTRIBUTE2 = X_ATTRIBUTE2,
162 ATTRIBUTE3 = X_ATTRIBUTE3,
163 ATTRIBUTE4 = X_ATTRIBUTE4 ,
164 ATTRIBUTE5 = X_ATTRIBUTE5 ,
165 ATTRIBUTE6 = X_ATTRIBUTE6 ,
166 ATTRIBUTE7 = X_ATTRIBUTE7 ,
167 ATTRIBUTE8 = X_ATTRIBUTE8 ,
168 ATTRIBUTE9 = X_ATTRIBUTE9 ,
169 ATTRIBUTE10 = X_ATTRIBUTE10 ,
170 ATTRIBUTE11 = X_ATTRIBUTE11 ,
171 ATTRIBUTE12 = X_ATTRIBUTE12 ,
172 ATTRIBUTE13 = X_ATTRIBUTE13 ,
173 ATTRIBUTE14 = X_ATTRIBUTE14 ,
174 ATTRIBUTE15 = X_ATTRIBUTE15,
175 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
176 ORG_ID = X_ORG_ID ,
177 FACTS_PRGM_SEGMENT = X_FACTS_PRGM_SEGMENT ,
178 TREASURY_SYMBOL_ID = X_TREASURY_SYMBOL_ID,
179 FREEZE_DEFINITION_FLAG = X_FREEZE_DEFINITION_FLAG,
180 AUTO_CREATE_FLAG = X_AUTO_CREATE_FLAG
181 WHERE ROWID = X_ROWID;
182
183 IF (SQL%NOTFOUND) THEN
184 RAISE NO_DATA_FOUND;
185 END IF;
186 EXCEPTION
187 WHEN OTHERS THEN
188 l_errbuf := SQLERRM;
189 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',l_errbuf);
190 RAISE;
191 END Update_Row;
192
193
194 PROCEDURE Lock_Row( X_ROWID VARCHAR2,
195 X_DISTRIBUTION_ID NUMBER,
196 X_FUND_VALUE VARCHAR2,
197 X_SET_OF_BOOKS_ID NUMBER,
198 X_ATTRIBUTE_CATEGORY VARCHAR2,
199 X_ORG_ID NUMBER,
200 X_FACTS_PRGM_SEGMENT VARCHAR2,
201 X_TREASURY_SYMBOL_ID NUMBER
202 ) IS
203 l_module_name VARCHAR2(200) := g_module_name || 'Lock_Row';
204 l_errbuf VARCHAR2(1024);
205 CURSOR C IS
206 SELECT distribution_id,
207 fund_value,
208 set_of_books_id,
209 attribute_category,
210 org_id,
211 facts_prgm_segment,
212 treasury_symbol_id
213 FROM FV_BUDGET_DISTRIBUTION_HDR
214 WHERE ROWID = X_ROWID
215 for UPDATE OF DISTRIBUTION_ID NOWAIT;
216 Recinfo C%ROWTYPE;
217
218 BEGIN
219 OPEN C;
220 FETCH C INTO Recinfo;
221 IF (C%NOTFOUND) THEN
222 CLOSE C;
223 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
224 APP_EXCEPTION.Raise_Exception;
225 END IF;
226 CLOSE C;
227 IF ((recinfo.DISTRIBUTION_ID = X_DISTRIBUTION_ID)
228 AND (recinfo.FUND_VALUE = X_FUND_VALUE)
229 AND (recinfo.SET_OF_BOOKS_ID = X_SET_OF_BOOKS_ID)
230 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
231 OR ((recinfo.ATTRIBUTE_CATEGORY IS NULL)
232 AND (X_ATTRIBUTE_CATEGORY IS NULL)))
233 AND ((recinfo.ORG_ID = X_ORG_ID)
234 OR ((recinfo.ORG_ID IS NULL)
235 AND (X_ORG_ID IS NULL)))
236 AND ((recinfo.FACTS_PRGM_SEGMENT = X_FACTS_PRGM_SEGMENT)
237 OR ((recinfo.FACTS_PRGM_SEGMENT IS NULL)
238 AND (X_FACTS_PRGM_SEGMENT IS NULL)))
239 AND (recinfo.TREASURY_SYMBOL_ID = X_TREASURY_SYMBOL_ID)
240 ) THEN
241 RETURN;
242 ELSE
243 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
244 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
245 FV_UTILITY.MESSAGE(FND_LOG.LEVEL_ERROR, l_module_name||'.message');
246 END IF;
247 APP_EXCEPTION.RAISE_EXCEPTION;
248 END IF;
249 EXCEPTION
250 WHEN OTHERS THEN
251 l_errbuf := SQLERRM;
252 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',l_errbuf);
253 RAISE;
254 END Lock_Row;
255
256
257
258 PROCEDURE Delete_Row(X_ROWID VARCHAR2) IS
259 l_module_name VARCHAR2(200) := g_module_name || 'Delete_Row';
260 l_errbuf VARCHAR2(1024);
261 x_distribution_id NUMBER;
262 x_fund_value VARCHAR2(25);
263 BEGIN
264 BEGIN
265 SELECT distribution_id
266 INTO x_distribution_id
267 FROM FV_BUDGET_DISTRIBUTION_HDR
268 WHERE rowid = x_rowid;
269
270 SELECT fund_value
271 INTO x_fund_value
272 FROM FV_BUDGET_DISTRIBUTION_HDR
273 WHERE rowid = x_rowid;
274
275 EXCEPTION
276 WHEN NO_DATA_FOUND THEN
277 RAISE;
278 END;
279
280
281 /* Delete FV_BUDGET_DISTRIBUTION_DTL - Master - Detail - Detail Record */
282 DELETE FROM FV_BUDGET_DISTRIBUTION_DTL
283 WHERE fund_value IN
284 (SELECT fund_value
285 FROM FV_BUDGET_DISTRIBUTION_HDR
286 WHERE fund_value = x_fund_value);
287
288 IF (SQL%NOTFOUND) THEN
289 NULL;
290 END IF;
291
292 /* Delete FV_BUDGET_DISTRIBUTION_HDR - Master Record */
293 DELETE FROM FV_BUDGET_DISTRIBUTION_HDR
294 WHERE DISTRIBUTION_ID = X_DISTRIBUTION_ID ;
295
296 IF (SQL%NOTFOUND) THEN
297 RAISE NO_DATA_FOUND;
298 END IF;
299 EXCEPTION
300 WHEN OTHERS THEN
301 l_errbuf := SQLERRM;
302 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',l_errbuf);
303 RAISE;
304 END DELETE_ROW;
305
306 END FV_BUDGET_DISTRIBUTION_HDR_PKG;