[Home] [Help]
PACKAGE BODY: APPS.XTR_FORECAST_PERIODS_PKG
Source
1 PACKAGE BODY XTR_FORECAST_PERIODS_PKG AS
2 /* $Header: xtrfprdb.pls 115.1 99/07/17 00:30:48 porting ship $ */
3 --
4 -- Package
5 -- XTR_FORECAST_PERIODS_PKG
6 -- Purpose
7 -- To group all the procedures/functions for table handling of the
8 -- xtr_forecast_periods table.
9 -- History
10 -- 12/24/98 BHCHUNG Created
11 --
12
13
14 --
15 -- Procedure
16 -- Check_Unique
17 -- Purpose
18 -- Checks the uniqueness of the forecast period inserted.
19 -- History
20 -- 12/24/98 BHCHUNG Created
21 -- Example
22 -- XTR_FORECAST_PERIODS_PKG.Check_Unique(...)
23 -- Notes
24 --
25
26 PROCEDURE Check_Unique(
27 X_rowid VARCHAR2,
28 X_forecast_header_id NUMBER,
29 X_period_number NUMBER) IS
30 CURSOR chk_duplicates IS
31 SELECT 'Duplicate'
32 FROM xtr_forecast_periods cfc
33 WHERE cfc.forecast_header_id = X_forecast_header_id
34 AND cfc.period_number = X_period_number
35 AND (X_rowid IS NULL
36 OR cfc.rowid <> chartorowid(X_rowid));
37 dummy VARCHAR2(100);
38 BEGIN
39 OPEN chk_duplicates;
40 FETCH chk_duplicates INTO dummy;
41
42 IF chk_duplicates%FOUND THEN
43 FND_MESSAGE.Set_Name('CE', 'CE_DUPLICATE_COLUMN_NUMBER');
44 APP_EXCEPTION.Raise_exception;
45 END IF;
46 CLOSE chk_duplicates;
47
48 EXCEPTION
49 WHEN APP_EXCEPTIONS.application_exception THEN
50 RAISE;
51 WHEN OTHERS THEN
52 FND_MESSAGE.Set_Name('CE', 'CE_UNHANDLED_EXCEPTION');
53 FND_MESSAGE.Set_Token('PROCEDURE', 'ce_cf_periods_pkg.check_unique');
54 RAISE;
55 END Check_Unique;
56
57 --
58 -- Procedure
59 -- insert_row
60 -- Purpose
61 -- To insert new row to xtr_forecast_periods.
62 -- History
63 -- 12/24/98 BHCHUNG Created
64 -- Example
65 -- XTR_FORECAST_PERIODS_PKG.Insert_Row(...)
66 -- Notes
67 --
68
69 PROCEDURE Insert_Row(
70 X_rowid IN OUT VARCHAR2,
71 X_forecast_period_id IN OUT NUMBER,
72 X_forecast_header_id NUMBER,
73 X_period_number NUMBER,
74 X_level_of_summary VARCHAR2,
75 X_length_of_period NUMBER,
76 X_length_type VARCHAR2,
77 X_created_by NUMBER,
78 X_creation_date DATE,
79 X_last_updated_by NUMBER,
80 X_last_update_date DATE,
81 X_last_update_login NUMBER
82 ) IS
83 CURSOR C IS SELECT rowid FROM xtr_forecast_periods
84 WHERE forecast_period_id = TO_NUMBER(X_forecast_period_id);
85 CURSOR C2 IS SELECT ce_forecast_columns_s.nextval FROM sys.dual;
86
87
88 BEGIN
89 OPEN C2;
90 FETCH C2 INTO X_forecast_period_id;
91 CLOSE C2;
92
93 INSERT INTO xtr_forecast_periods(
94 forecast_period_id,
95 forecast_header_id,
96 period_number,
97 level_of_summary,
98 length_of_period,
99 length_type,
100 created_by,
101 creation_date,
102 last_updated_by,
103 last_update_date,
104 last_update_login
105 ) VALUES (
106 X_forecast_period_id,
107 X_forecast_header_id,
108 X_period_number,
109 X_level_of_summary,
110 X_length_of_period,
111 X_length_type,
112 X_created_by,
113 X_creation_date,
114 X_last_updated_by,
115 X_last_update_date,
116 X_last_update_login
117 );
118 OPEN C;
119 FETCH C INTO X_rowid;
120 if (C%NOTFOUND) then
121 CLOSE C;
122 Raise NO_DATA_FOUND;
123 end if;
124 CLOSE C;
125 END Insert_Row;
126 --
127 -- Procedure
128 -- lock_row
129 -- Purpose
130 -- To lock a row from xtr_forecast_periods.
131 -- History
132 -- 12/24/98 BHCHUNG Created
133 -- Example
134 -- XTR_FORECAST_PERIODS_PKG.Lock_Row(...)
135 -- Notes
136 --
137
138 PROCEDURE Lock_Row(
139 X_rowid VARCHAR2,
140 X_forecast_period_id NUMBER,
141 X_forecast_header_id NUMBER,
142 X_period_number NUMBER,
143 X_level_of_summary VARCHAR2,
144 X_length_of_period NUMBER,
145 X_length_type VARCHAR2,
146 X_created_by NUMBER,
147 X_creation_date DATE,
148 X_last_updated_by NUMBER,
149 X_last_update_date DATE,
150 X_last_update_login NUMBER
151 ) IS
152 CURSOR C IS
153 SELECT *
154 FROM xtr_forecast_periods
155 WHERE rowid = X_rowid
156 FOR UPDATE of forecast_period_id NOWAIT;
157 Recinfo C%ROWTYPE;
158 BEGIN
159 OPEN C;
160 FETCH C INTO recinfo;
161 if (C%NOTFOUND) then
162 CLOSE C;
163 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
164 APP_EXCEPTION.Raise_Exception;
165 end if;
166 CLOSE C;
167 if (
168 (Recinfo.forecast_period_id = X_forecast_period_id)
169 AND (Recinfo.forecast_header_id = X_forecast_header_id)
170 AND (Recinfo.period_number = X_period_number)
171 AND (Recinfo.level_of_summary = X_level_of_summary)
172 AND (Recinfo.length_of_period = X_length_of_period)
173 AND (Recinfo.length_type = X_length_type)
174 ) then
175 return;
176 else
177 FND_MESSAGE.Set_name('FND', 'FORM_RECORD_CHANGED');
178 APP_EXCEPTION.Raise_Exception;
179 end if;
180 END Lock_Row;
181
182 --
183 -- Procedure
184 -- Update_Row
185 -- Purpose
186 -- To update xtr_forecast_periods with changes made.
187 -- History
188 -- 12/24/98 BHCHUNG Created
189 -- Example
190 -- XTR_FORECAST_PERIODS_PKG.Update_Row(...);
191 -- Notes
192 --
193 PROCEDURE Update_Row(
194 X_rowid VARCHAR2,
195 X_forecast_period_id NUMBER,
196 X_forecast_header_id NUMBER,
197 X_period_number NUMBER,
198 X_level_of_summary VARCHAR2,
199 X_length_of_period NUMBER,
200 X_length_type VARCHAR2,
201 X_created_by NUMBER,
202 X_creation_date DATE,
203 X_last_updated_by NUMBER,
204 X_last_update_date DATE,
205 X_last_update_login NUMBER
206 ) IS
207 BEGIN
208 UPDATE xtr_forecast_periods
209 SET
210 forecast_period_id = X_forecast_period_id,
211 forecast_header_id = X_forecast_header_id,
212 period_number = X_period_number,
213 level_of_summary = X_level_of_summary,
214 length_of_period = X_length_of_period,
215 length_type = X_length_type
216 WHERE rowid = X_rowid;
217 if (SQL%NOTFOUND) then
218 Raise NO_DATA_FOUND;
219 end if;
220 END Update_Row;
221
222 --
223 -- Procedure
224 -- Delete_Row
225 -- Purpose
226 -- To delete a row from xtr_forecast_periods.
227 -- History
228 -- 12/24/98 BHCHUNG Created
229 -- Example
230 -- XTR_FORECAST_PERIODS_PKG.Delete_Row(...);
231 -- Notes
232 --
233 PROCEDURE Delete_Row(X_rowid VARCHAR2) IS
234 BEGIN
235 DELETE FROM xtr_forecast_periods
236 WHERE rowid = X_rowid;
237 if (SQL%NOTFOUND) then
238 Raise NO_DATA_FOUND;
239 end if;
240 END Delete_Row;
241
242 END XTR_FORECAST_PERIODS_PKG;
243