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