[Home] [Help]
PACKAGE BODY: APPS.PN_REC_EXP_LINE_DTL_PKG
Source
1 PACKAGE BODY pn_rec_exp_line_dtl_pkg AS
2 /* $Header: PNREXLDB.pls 120.2 2005/11/30 23:40:33 appldev noship $ */
3
4 -------------------------------------------------------------------------------
5 -- PROCDURE : INSERT_ROW
6 -- INVOKED FROM : insert_row procedure
7 -- PURPOSE : inserts the row
8 -- HISTORY :
9 -- 15-JUL-05 sdmahesh o Replaced base views with their _ALL table.
10 -- 28-NOV-05 pikhar o fetched org_id using cursor
11 -------------------------------------------------------------------------------
12 PROCEDURE insert_row(
13 x_org_id pn_rec_exp_line_dtl.org_id%TYPE,
14 x_expense_line_id pn_rec_exp_line_dtl.expense_line_id%TYPE,
15 x_expense_line_dtl_id IN OUT NOCOPY pn_rec_exp_line_dtl.expense_line_dtl_id%TYPE,
16 x_parent_expense_line_id pn_rec_exp_line_dtl.parent_expense_line_id%TYPE,
17 x_location_id pn_rec_exp_line_dtl.location_id%TYPE,
18 x_property_id pn_rec_exp_line_dtl.property_id%TYPE,
19 x_expense_type_code pn_rec_exp_line_dtl.expense_type_code%TYPE,
20 x_expense_account_id pn_rec_exp_line_dtl.expense_account_id%TYPE,
21 x_account_description pn_rec_exp_line_dtl.account_description%TYPE,
22 x_actual_amount pn_rec_exp_line_dtl.actual_amount%TYPE,
23 x_actual_amount_ovr pn_rec_exp_line_dtl.actual_amount_ovr%TYPE,
24 x_budgeted_amount pn_rec_exp_line_dtl.budgeted_amount%TYPE,
25 x_budgeted_amount_ovr pn_rec_exp_line_dtl.budgeted_amount_ovr%TYPE,
26 x_budgeted_pct pn_rec_exp_line_dtl.budgeted_pct%TYPE,
27 x_actual_pct pn_rec_exp_line_dtl.actual_pct%TYPE,
28 x_currency_code pn_rec_exp_line_dtl.currency_code%TYPE,
29 x_recoverable_flag pn_rec_exp_line_dtl.recoverable_flag%TYPE,
30 x_expense_line_indicator pn_rec_exp_line_dtl.expense_line_indicator%TYPE,
31 x_last_update_date pn_rec_exp_line_dtl.last_update_date%TYPE,
32 x_last_updated_by pn_rec_exp_line_dtl.last_updated_by%TYPE,
33 x_creation_date pn_rec_exp_line_dtl.creation_date%TYPE,
34 x_created_by pn_rec_exp_line_dtl.created_by%TYPE,
35 x_last_update_login pn_rec_exp_line_dtl.last_update_login%TYPE,
36 x_attribute_category pn_rec_exp_line_dtl.attribute_category%TYPE,
37 x_attribute1 pn_rec_exp_line_dtl.attribute1%TYPE,
38 x_attribute2 pn_rec_exp_line_dtl.attribute2%TYPE,
39 x_attribute3 pn_rec_exp_line_dtl.attribute3%TYPE,
40 x_attribute4 pn_rec_exp_line_dtl.attribute4%TYPE,
41 x_attribute5 pn_rec_exp_line_dtl.attribute5%TYPE,
42 x_attribute6 pn_rec_exp_line_dtl.attribute6%TYPE,
43 x_attribute7 pn_rec_exp_line_dtl.attribute7%TYPE,
44 x_attribute8 pn_rec_exp_line_dtl.attribute8%TYPE,
45 x_attribute9 pn_rec_exp_line_dtl.attribute9%TYPE,
46 x_attribute10 pn_rec_exp_line_dtl.attribute10%TYPE,
47 x_attribute11 pn_rec_exp_line_dtl.attribute11%TYPE,
48 x_attribute12 pn_rec_exp_line_dtl.attribute12%TYPE,
49 x_attribute13 pn_rec_exp_line_dtl.attribute13%TYPE,
50 x_attribute14 pn_rec_exp_line_dtl.attribute14%TYPE,
51 x_attribute15 pn_rec_exp_line_dtl.attribute15%TYPE)
52 IS
53 l_desc VARCHAR2(100) := 'pn_rec_exp_line_dtl_pkg.insert_row';
54
55 CURSOR org_cur IS
56 SELECT org_id
57 FROM pn_rec_exp_line_all
58 WHERE expense_line_id = x_expense_line_id;
59
60 l_org_id NUMBER;
61
62
63 BEGIN
64
65 pnp_debug_pkg.debug(l_desc ||' (+)');
66
67 IF x_org_id IS NULL THEN
68 FOR rec IN org_cur LOOP
69 l_org_id := rec.org_id;
70 END LOOP;
71 ELSE
72 l_org_id := x_org_id;
73 END IF;
74
75 INSERT INTO pn_rec_exp_line_dtl_all(
76 org_id,
77 expense_line_id,
78 expense_line_dtl_id,
79 parent_expense_line_id,
80 property_id,
81 location_id,
82 expense_type_code,
83 expense_account_id,
84 account_description,
85 actual_amount,
86 actual_amount_ovr,
87 budgeted_amount,
88 budgeted_amount_ovr,
89 budgeted_pct,
90 actual_pct,
91 currency_code,
92 recoverable_flag,
93 expense_line_indicator,
94 last_update_date,
95 last_updated_by,
96 creation_date,
97 created_by,
98 last_update_login,
99 attribute_category,
100 attribute1,
101 attribute2,
102 attribute3,
103 attribute4,
104 attribute5,
105 attribute6,
106 attribute7,
107 attribute8,
108 attribute9,
109 attribute10,
110 attribute11,
111 attribute12,
112 attribute13,
113 attribute14,
114 attribute15)
115 VALUES(
116 l_org_id,
117 x_expense_line_id,
118 pn_rec_exp_line_dtl_s.nextval,
119 x_parent_expense_line_id,
120 x_property_id,
121 x_location_id,
122 x_expense_type_code,
123 x_expense_account_id,
124 x_account_description,
125 x_actual_amount,
126 x_actual_amount_ovr,
127 x_budgeted_amount,
128 x_budgeted_amount_ovr,
129 x_budgeted_pct,
130 x_actual_pct,
131 x_currency_code,
132 x_recoverable_flag,
133 x_expense_line_indicator,
134 x_last_update_date,
135 x_last_updated_by,
136 x_creation_date,
137 x_created_by,
138 x_last_update_login,
139 x_attribute_category,
140 x_attribute1,
141 x_attribute2,
142 x_attribute3,
143 x_attribute4,
144 x_attribute5,
145 x_attribute6,
146 x_attribute7,
147 x_attribute8,
148 x_attribute9,
149 x_attribute10,
150 x_attribute11,
151 x_attribute12,
152 x_attribute13,
153 x_attribute14,
154 x_attribute15
155 ) RETURNING expense_line_dtl_id INTO x_expense_line_dtl_id;
156
157 pnp_debug_pkg.debug(l_desc ||' (-)');
158
159 EXCEPTION
160 WHEN OTHERS THEN
161 app_exception.raise_exception;
162 END insert_row;
163
164 -------------------------------------------------------------------------------
165 -- PROCDURE : UPDATE_ROW
166 -- INVOKED FROM : update_row procedure
167 -- PURPOSE : updates the row
168 -- HISTORY :
169 -- 15-JUL-05 sdmahesh o Replaced base views with their _ALL table.
170 -------------------------------------------------------------------------------
171 PROCEDURE update_row(
172 x_expense_line_dtl_id pn_rec_exp_line_dtl.expense_line_dtl_id%TYPE,
173 x_parent_expense_line_id pn_rec_exp_line_dtl.parent_expense_line_id%TYPE,
174 x_location_id pn_rec_exp_line_dtl.location_id%TYPE,
175 x_property_id pn_rec_exp_line_dtl.property_id%TYPE,
176 x_expense_type_code pn_rec_exp_line_dtl.expense_type_code%TYPE,
177 x_expense_account_id pn_rec_exp_line_dtl.expense_account_id%TYPE,
178 x_account_description pn_rec_exp_line_dtl.account_description%TYPE,
179 x_actual_amount pn_rec_exp_line_dtl.actual_amount%TYPE,
180 x_actual_amount_ovr pn_rec_exp_line_dtl.actual_amount_ovr%TYPE,
181 x_budgeted_amount pn_rec_exp_line_dtl.budgeted_amount%TYPE,
182 x_budgeted_amount_ovr pn_rec_exp_line_dtl.budgeted_amount_ovr%TYPE,
183 x_budgeted_pct pn_rec_exp_line_dtl.budgeted_pct%TYPE,
184 x_actual_pct pn_rec_exp_line_dtl.actual_pct%TYPE,
185 x_currency_code pn_rec_exp_line_dtl.currency_code%TYPE,
186 x_recoverable_flag pn_rec_exp_line_dtl.recoverable_flag%TYPE,
187 x_expense_line_indicator pn_rec_exp_line_dtl.expense_line_indicator%TYPE,
188 x_last_update_date pn_rec_exp_line_dtl.last_update_date%TYPE,
189 x_last_updated_by pn_rec_exp_line_dtl.last_updated_by%TYPE,
190 x_creation_date pn_rec_exp_line_dtl.creation_date%TYPE,
191 x_created_by pn_rec_exp_line_dtl.created_by%TYPE,
192 x_last_update_login pn_rec_exp_line_dtl.last_update_login%TYPE,
193 x_attribute_category pn_rec_exp_line_dtl.attribute_category%TYPE,
194 x_attribute1 pn_rec_exp_line_dtl.attribute1%TYPE,
195 x_attribute2 pn_rec_exp_line_dtl.attribute2%TYPE,
196 x_attribute3 pn_rec_exp_line_dtl.attribute3%TYPE,
197 x_attribute4 pn_rec_exp_line_dtl.attribute4%TYPE,
198 x_attribute5 pn_rec_exp_line_dtl.attribute5%TYPE,
199 x_attribute6 pn_rec_exp_line_dtl.attribute6%TYPE,
200 x_attribute7 pn_rec_exp_line_dtl.attribute7%TYPE,
201 x_attribute8 pn_rec_exp_line_dtl.attribute8%TYPE,
202 x_attribute9 pn_rec_exp_line_dtl.attribute9%TYPE,
203 x_attribute10 pn_rec_exp_line_dtl.attribute10%TYPE,
204 x_attribute11 pn_rec_exp_line_dtl.attribute11%TYPE,
205 x_attribute12 pn_rec_exp_line_dtl.attribute12%TYPE,
206 x_attribute13 pn_rec_exp_line_dtl.attribute13%TYPE,
207 x_attribute14 pn_rec_exp_line_dtl.attribute14%TYPE,
208 x_attribute15 pn_rec_exp_line_dtl.attribute15%TYPE)
209 IS
210 l_desc VARCHAR2(100) := 'pn_rec_exp_line_dtl_pkg.update_row';
211 BEGIN
212
213 pnp_debug_pkg.debug(l_desc ||' (+)');
214
215 UPDATE pn_rec_exp_line_dtl_all
216 SET parent_expense_line_id = x_parent_expense_line_id,
217 location_id = x_location_id,
218 property_id = x_property_id,
219 expense_type_code = x_expense_type_code,
220 expense_account_id = x_expense_account_id,
221 account_description = x_account_description,
222 actual_amount = x_actual_amount,
223 actual_amount_ovr = x_actual_amount_ovr,
224 budgeted_amount = x_budgeted_amount,
225 budgeted_amount_ovr = x_budgeted_amount_ovr,
226 budgeted_pct = x_budgeted_pct,
227 actual_pct = x_actual_pct,
228 currency_code = x_currency_code,
229 recoverable_flag = x_recoverable_flag,
230 expense_line_indicator = x_expense_line_indicator,
231 last_update_date = x_last_update_date,
232 last_updated_by = x_last_updated_by,
233 creation_date = x_creation_date,
234 created_by = x_created_by,
235 last_update_login = x_last_update_login,
236 attribute_category = x_attribute_category,
237 attribute1 = x_attribute1,
238 attribute2 = x_attribute2,
239 attribute3 = x_attribute3,
240 attribute4 = x_attribute4,
241 attribute5 = x_attribute5,
242 attribute6 = x_attribute6,
243 attribute7 = x_attribute7,
244 attribute8 = x_attribute8,
245 attribute9 = x_attribute9,
246 attribute10 = x_attribute10,
247 attribute11 = x_attribute11,
248 attribute12 = x_attribute12,
249 attribute13 = x_attribute13,
250 attribute14 = x_attribute14,
251 attribute15 = x_attribute15
252 WHERE expense_line_dtl_id = x_expense_line_dtl_id;
253
254 pnp_debug_pkg.debug(l_desc ||' (-)');
255
256 EXCEPTION
257 WHEN OTHERS THEN
258 RAISE_APPLICATION_ERROR(-20001, to_char(sqlcode));
259 app_exception.raise_exception;
260
261 END update_row;
262
263
264 -------------------------------------------------------------------------------
265 -- PROCDURE : DELETE_ROW
266 -- INVOKED FROM : delete_row procedure
267 -- PURPOSE : deletes the row
268 -- HISTORY :
269 -- 15-JUL-05 sdmahesh o Replaced base views with their _ALL table.
270 -------------------------------------------------------------------------------
271 PROCEDURE delete_row(x_expense_line_dtl_id pn_rec_exp_line_dtl.expense_line_dtl_id%TYPE) IS
272 l_desc VARCHAR2(100) := 'pn_rec_exp_line_dtl_pkg.delete_row';
273 BEGIN
274
275 pnp_debug_pkg.debug(l_desc ||' (+)');
276
277 DELETE pn_rec_exp_line_dtl_all
278 WHERE expense_line_dtl_id = x_expense_line_dtl_id;
279
280 pnp_debug_pkg.debug(l_desc ||' (-)');
281
282 EXCEPTION
283 WHEN OTHERS THEN
284 RAISE_APPLICATION_ERROR(-20001, to_char(sqlcode));
285 app_exception.raise_exception;
286
287 END delete_row;
288
289 END pn_rec_exp_line_dtl_pkg;