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