[Home] [Help]
PACKAGE BODY: APPS.PA_EXPENDITURES_PKG
Source
1 package body pa_expenditures_pkg as
2 /* $Header: PAXTEXPB.pls 120.2 2005/08/09 04:53:34 avajain noship $ */
3
4 procedure insert_row (x_rowid in out NOCOPY VARCHAR2,
5 x_expenditure_id in out NOCOPY NUMBER,
6 x_last_update_date in DATE,
7 x_last_updated_by in NUMBER,
8 x_creation_date in DATE,
9 x_created_by in NUMBER,
10 x_expenditure_status_code in VARCHAR2,
11 x_expenditure_ending_date in DATE,
12 x_expenditure_class_code in VARCHAR2,
13 x_incurred_by_person_id in NUMBER,
14 x_incurred_by_organization_id in NUMBER,
15 x_expenditure_group in VARCHAR2,
16 x_control_total_amount in NUMBER,
17 x_entered_by_person_id in NUMBER,
18 x_description in VARCHAR2,
19 x_initial_submission_date in DATE,
20 x_last_update_login in NUMBER,
21 x_attribute_category in VARCHAR2,
22 x_attribute1 in VARCHAR2,
23 x_attribute2 in VARCHAR2,
24 x_attribute3 in VARCHAR2,
25 x_attribute4 in VARCHAR2,
26 x_attribute5 in VARCHAR2,
27 x_attribute6 in VARCHAR2,
28 x_attribute7 in VARCHAR2,
29 x_attribute8 in VARCHAR2,
30 x_attribute9 in VARCHAR2,
31 x_attribute10 in VARCHAR2,
32 x_denom_currency_code in VARCHAR2,
33 x_acct_currency_code in VARCHAR2,
34 x_acct_rate_type in VARCHAR2,
35 x_acct_rate_date in DATE,
36 x_acct_exchange_rate in NUMBER,
37 -- Trx_import enhancement:
38 -- These new parameters are needed to populate
39 -- PA_EXPENDITURES_ALL table's new columns
40 x_orig_exp_txn_reference1 in VARCHAR2,
41 x_orig_exp_txn_reference2 in VARCHAR2,
42 x_orig_exp_txn_reference3 in VARCHAR2,
43 x_orig_user_exp_txn_reference in VARCHAR2,
44 x_vendor_id in NUMBER,
45 x_person_type in VARCHAR2,
46 P_Org_ID IN NUMBER) -- 12i MOAC changes
47 is
48
49 cursor return_rowid is select rowid from pa_expenditures
50 where expenditure_id = x_expenditure_id;
51 cursor get_exp_id is select pa_expenditures_s.nextval from dual;
52
53 BEGIN
54
55 if (x_expenditure_id is null) then
56 open get_exp_id;
57 fetch get_exp_id into x_expenditure_id;
58 end if;
59
60 insert into pa_expenditures (
61 expenditure_id,
62 last_update_date,
63 last_updated_by,
64 creation_date,
65 created_by,
66 expenditure_status_code,
67 expenditure_ending_date,
68 expenditure_class_code,
69 incurred_by_person_id,
70 incurred_by_organization_id,
71 expenditure_group,
72 control_total_amount,
73 entered_by_person_id,
74 description,
75 initial_submission_date,
76 last_update_login,
77 attribute_category,
78 attribute1,
79 attribute2,
80 attribute3,
81 attribute4,
82 attribute5,
83 attribute6,
84 attribute7,
85 attribute8,
86 attribute9,
87 attribute10,
88 denom_currency_code,
89 acct_currency_code,
90 acct_rate_type,
91 acct_rate_date,
92 acct_exchange_rate,
93 orig_exp_txn_reference1,
94 orig_exp_txn_reference2,
95 orig_exp_txn_reference3,
96 orig_user_exp_txn_reference,
97 vendor_id,
98 person_type,
99 Org_Id) -- 12i MOAC changes
100 values (x_expenditure_id,
101 x_last_update_date,
102 x_last_updated_by,
103 x_creation_date,
104 x_created_by,
105 x_expenditure_status_code,
106 x_expenditure_ending_date,
107 x_expenditure_class_code,
108 x_incurred_by_person_id,
109 x_incurred_by_organization_id,
110 x_expenditure_group,
111 x_control_total_amount,
112 x_entered_by_person_id,
113 x_description,
114 x_initial_submission_date,
115 x_last_update_login,
116 x_attribute_category,
117 x_attribute1,
118 x_attribute2,
119 x_attribute3,
120 x_attribute4,
121 x_attribute5,
122 x_attribute6,
123 x_attribute7,
124 x_attribute8,
125 x_attribute9,
126 x_attribute10,
127 x_denom_currency_code,
128 x_acct_currency_code,
129 x_acct_rate_type,
130 x_acct_rate_date,
131 x_acct_exchange_rate,
132 x_orig_exp_txn_reference1,
133 x_orig_exp_txn_reference2,
134 x_orig_exp_txn_reference3,
135 x_orig_user_exp_txn_reference,
136 x_vendor_id,
137 x_person_type,
138 P_Org_Id); -- 12i MOAC changes
139
140 open return_rowid;
141 fetch return_rowid into x_rowid;
142 if (return_rowid%notfound) then
143 raise NO_DATA_FOUND;
144 end if;
145 close return_rowid;
146
147 END insert_row;
148
149 procedure update_row (x_rowid in VARCHAR2,
150 x_expenditure_id in NUMBER,
151 x_last_update_date in DATE,
152 x_last_updated_by in NUMBER,
153 x_expenditure_status_code in VARCHAR2,
154 x_expenditure_ending_date in DATE,
155 x_expenditure_class_code in VARCHAR2,
156 x_incurred_by_person_id in NUMBER,
157 x_incurred_by_organization_id in NUMBER,
158 x_expenditure_group in VARCHAR2,
159 x_control_total_amount in NUMBER,
160 x_entered_by_person_id in NUMBER,
161 x_description in VARCHAR2,
162 x_initial_submission_date in DATE,
163 x_last_update_login in NUMBER,
164 x_attribute_category in VARCHAR2,
165 x_attribute1 in VARCHAR2,
166 x_attribute2 in VARCHAR2,
167 x_attribute3 in VARCHAR2,
168 x_attribute4 in VARCHAR2,
169 x_attribute5 in VARCHAR2,
170 x_attribute6 in VARCHAR2,
171 x_attribute7 in VARCHAR2,
172 x_attribute8 in VARCHAR2,
173 x_attribute9 in VARCHAR2,
174 x_attribute10 in VARCHAR2,
175 x_denom_currency_code in VARCHAR2,
176 x_acct_currency_code in VARCHAR2,
177 x_acct_rate_type in VARCHAR2,
178 x_acct_rate_date in DATE,
179 x_acct_exchange_rate in NUMBER,
180 -- Trx_import enhancement:
181 -- These new parameters are needed to populate
182 -- PA_EXPENDITURES_ALL table's new columns
183 x_orig_exp_txn_reference1 in VARCHAR2,
184 x_orig_exp_txn_reference2 in VARCHAR2,
185 x_orig_exp_txn_reference3 in VARCHAR2,
186 x_orig_user_exp_txn_reference in VARCHAR2,
187 x_vendor_id in NUMBER,
188 x_person_type in VARCHAR2 ) is
189 BEGIN
190
191 update pa_expenditures
192 set expenditure_id = x_expenditure_id,
193 last_update_date = x_last_update_date,
194 last_updated_by = x_last_updated_by,
195 expenditure_status_code = x_expenditure_status_code,
196 expenditure_ending_date = x_expenditure_ending_date,
197 expenditure_class_code = x_expenditure_class_code,
198 incurred_by_person_id = x_incurred_by_person_id,
199 incurred_by_organization_id = x_incurred_by_organization_id,
200 expenditure_group = x_expenditure_group,
201 control_total_amount = x_control_total_amount,
202 entered_by_person_id = x_entered_by_person_id,
203 description = x_description,
204 initial_submission_date = x_initial_submission_date,
205 last_update_login = x_last_update_login,
206 attribute_category = x_attribute_category,
207 attribute1 = x_attribute1,
208 attribute2 = x_attribute2,
209 attribute3 = x_attribute3,
210 attribute4 = x_attribute4,
211 attribute5 = x_attribute5,
212 attribute6 = x_attribute6,
213 attribute7 = x_attribute7,
214 attribute8 = x_attribute8,
215 attribute9 = x_attribute9,
216 attribute10 = x_attribute10,
217 denom_currency_code = x_denom_currency_code,
218 acct_currency_code = x_acct_currency_code,
219 acct_rate_date = x_acct_rate_date,
220 acct_rate_type = x_acct_rate_type,
221 acct_exchange_rate = x_acct_exchange_rate,
222 orig_exp_txn_reference1 = x_orig_exp_txn_reference1,
223 orig_exp_txn_reference2 = x_orig_exp_txn_reference2,
224 orig_exp_txn_reference3 = x_orig_exp_txn_reference3,
225 orig_user_exp_txn_reference = x_orig_user_exp_txn_reference,
226 vendor_id = x_vendor_id,
227 person_type = x_person_type
228 where rowid = x_rowid;
229
230 END update_row;
231
232
233 procedure delete_row (x_expenditure_id in NUMBER) is
234 cursor items is select expenditure_item_id from pa_expenditure_items
235 where expenditure_id = x_expenditure_id
236 for update of expenditure_item_id nowait;
237 items_rec items%rowtype;
238 BEGIN
239 --
240 -- 3733123 - PJ.M:B5: QA:P11:OTH: MANUAL ENC/EXP FORM CREATING ORPHAN ADLS
241 -- delete award distribution lines..
242 --
243 gms_awards_dist_pkg.delete_adls(x_expenditure_id, NULL, 'EXP' ) ;
244
245 -- cascade delete the expenditure items.
246 open items;
247 LOOP
248 fetch items into items_rec;
249 if (items%notfound) then
250 exit;
251 else
252 pa_expenditure_items_pkg.delete_row (items_rec.expenditure_item_id);
253 end if;
254 END LOOP;
255
256 delete from pa_expenditures
257 where expenditure_id = x_expenditure_id;
258 EXCEPTION
259 when APP_EXCEPTION.RECORD_LOCK_EXCEPTION then
260 fnd_message.set_name ('FND', 'FORM_UNABLE_TO_RESERVE_RECORD');
261 app_exception.raise_exception;
262 END delete_row;
263
264
265 procedure delete_row (x_rowid in VARCHAR2) is
266 cursor get_exp_id is select expenditure_id from pa_expenditures
267 where rowid = x_rowid;
268 exp_rec get_exp_id%rowtype;
269 BEGIN
270 open get_exp_id;
271 fetch get_exp_id into exp_rec;
272
273 delete_row (exp_rec.expenditure_id);
274
275 END delete_row;
276
277
278 procedure lock_row (x_rowid in VARCHAR2) is
279 BEGIN
280 null;
281 END lock_row;
282
283 END pa_expenditures_pkg;