[Home] [Help]
PACKAGE BODY: APPS.PO_REQ_DISTRIBUTIONS_PKG1
Source
1 PACKAGE BODY PO_REQ_DISTRIBUTIONS_PKG1 as
2 /* $Header: POXRID1B.pls 120.2 2005/06/09 23:56:58 sjadhav noship $ */
3
4 /**
5 * For now, nonrecoverable and recoverable tax are not inserted and updated.
6 * These values are set by the tax engine.
7 **/
8
9 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
10 X_Distribution_Id IN OUT NOCOPY NUMBER,
11 X_Last_Update_Date DATE,
12 X_Last_Updated_By NUMBER,
13 X_Requisition_Line_Id NUMBER,
14 X_Set_Of_Books_Id NUMBER,
15 X_Code_Combination_Id NUMBER,
16 X_Req_Line_Quantity NUMBER,
17 X_Req_Line_Amount NUMBER, -- <SERVICES FPJ>
18 X_Req_Line_Currency_Amount NUMBER, -- <SERVICES FPJ>
19 X_Last_Update_Login NUMBER,
20 X_Creation_Date DATE,
21 X_Created_By NUMBER,
22 X_Encumbered_Flag VARCHAR2,
23 X_Gl_Encumbered_Date DATE,
24 X_Gl_Encumbered_Period_Name VARCHAR2,
25 X_Gl_Cancelled_Date DATE,
26 X_Failed_Funds_Lookup_Code VARCHAR2,
27 X_Encumbered_Amount NUMBER,
28 X_Budget_Account_Id NUMBER,
29 X_Accrual_Account_Id NUMBER,
30 X_Variance_Account_Id NUMBER,
31 X_Prevent_Encumbrance_Flag VARCHAR2,
32 X_Attribute_Category VARCHAR2,
33 X_Attribute1 VARCHAR2,
34 X_Attribute2 VARCHAR2,
35 X_Attribute3 VARCHAR2,
36 X_Attribute4 VARCHAR2,
37 X_Attribute5 VARCHAR2,
38 X_Attribute6 VARCHAR2,
39 X_Attribute7 VARCHAR2,
40 X_Attribute8 VARCHAR2,
41 X_Attribute9 VARCHAR2,
42 X_Attribute10 VARCHAR2,
43 X_Attribute11 VARCHAR2,
44 X_Attribute12 VARCHAR2,
45 X_Attribute13 VARCHAR2,
46 X_Attribute14 VARCHAR2,
47 X_Attribute15 VARCHAR2,
48 X_Ussgl_Transaction_Code VARCHAR2,
49 X_Government_Context VARCHAR2,
50 X_Project_Id NUMBER,
51 X_Task_Id NUMBER,
52 X_Expenditure_Type VARCHAR2,
53 X_Project_Accounting_Context VARCHAR2,
54 X_Expenditure_Organization_Id NUMBER,
55 X_Gl_Closed_Date DATE,
56 X_Source_Req_Distribution_Id NUMBER,
57 X_Distribution_Num NUMBER,
58 X_Project_Related_Flag VARCHAR2,
59 X_Expenditure_Item_Date DATE,
60 X_End_Item_Unit_Number VARCHAR2 DEFAULT NULL,
61 X_Recovery_Rate NUMBER,
62 X_Recoverable_Tax NUMBER,
63 X_Nonrecoverable_Tax NUMBER,
64 X_Tax_Recovery_Override_Flag VARCHAR2,
65 -- OGM_0.0 change
66 X_award_id NUMBER DEFAULT NULL ,
67 --togeorge 10/03/2000
68 -- added oke columns
69 x_oke_contract_line_id NUMBER default null,
70 x_oke_contract_deliverable_id NUMBER default null,
71 p_org_id IN NUMBER default null -- <R12 MOAC>
72 )
73 IS
74
75 x_unique BOOLEAN := TRUE;
76
77 CURSOR C IS SELECT rowid FROM PO_REQ_DISTRIBUTIONS
78 WHERE distribution_id = X_Distribution_Id;
79
80
81
82
83
84 CURSOR C2 IS SELECT po_req_distributions_s.nextval FROM sys.dual;
85
86 BEGIN
87
88 /* Check if the distribution_number entered by user is unique */
89 po_req_dist_sv.check_unique_insert(x_rowid,x_distribution_num,x_requisition_line_id);
90
91
92 if (X_Distribution_Id is NULL) then
93 OPEN C2;
94 FETCH C2 INTO X_Distribution_Id;
95 CLOSE C2;
96 end if;
97
98 INSERT INTO PO_REQ_DISTRIBUTIONS(
99 distribution_id,
100 last_update_date,
101 last_updated_by,
102 requisition_line_id,
103 set_of_books_id,
104 code_combination_id,
105 req_line_quantity,
106 req_line_amount, -- <SERVICES FPJ>
107 req_line_currency_amount, -- <SERVICES FPJ>
108 last_update_login,
109 creation_date,
110 created_by,
111 encumbered_flag,
112 gl_encumbered_date,
113 gl_encumbered_period_name,
114 gl_cancelled_date,
115 failed_funds_lookup_code,
116 encumbered_amount,
117 budget_account_id,
118 accrual_account_id,
119 variance_account_id,
120 prevent_encumbrance_flag,
121 attribute_category,
122 attribute1,
123 attribute2,
124 attribute3,
125 attribute4,
126 attribute5,
127 attribute6,
128 attribute7,
129 attribute8,
130 attribute9,
131 attribute10,
132 attribute11,
133 attribute12,
134 attribute13,
135 attribute14,
136 attribute15,
137 government_context,
138 project_id,
139 task_id,
140 expenditure_type,
141 project_accounting_context,
142 expenditure_organization_id,
143 gl_closed_date,
144 source_req_distribution_id,
145 distribution_num,
146 project_related_flag,
147 expenditure_item_date,
148 end_item_unit_number,
149 recovery_rate,
150 -- JFMIP START
151 recoverable_tax,
152 nonrecoverable_tax,
153 -- JFMIP END
154 tax_recovery_override_flag,
155 award_id, -- OGM_0.0 Changes
156 --togeorge 10/03/2000
157 -- added oke columns
158 oke_contract_line_id,
159 oke_contract_deliverable_id,
160 Org_Id -- <R12 MOAC>
161 ) VALUES (
162 X_Distribution_Id,
163 X_Last_Update_Date,
164 X_Last_Updated_By,
165 X_Requisition_Line_Id,
166 X_Set_Of_Books_Id,
167 X_Code_Combination_Id,
168 X_Req_Line_Quantity,
169 X_Req_Line_Amount, -- <SERVICES FPJ>
170 X_Req_Line_Currency_Amount, -- <SERVICES FPJ>
171 X_Last_Update_Login,
172 X_Creation_Date,
173 X_Created_By,
174 X_Encumbered_Flag,
175 X_Gl_Encumbered_Date,
176 X_Gl_Encumbered_Period_Name,
177 X_Gl_Cancelled_Date,
178 X_Failed_Funds_Lookup_Code,
179 X_Encumbered_Amount,
180 X_Budget_Account_Id,
181 X_Accrual_Account_Id,
182 X_Variance_Account_Id,
183 X_Prevent_Encumbrance_Flag,
184 X_Attribute_Category,
185 X_Attribute1,
186 X_Attribute2,
187 X_Attribute3,
188 X_Attribute4,
189 X_Attribute5,
190 X_Attribute6,
191 X_Attribute7,
192 X_Attribute8,
193 X_Attribute9,
194 X_Attribute10,
195 X_Attribute11,
196 X_Attribute12,
197 X_Attribute13,
198 X_Attribute14,
199 X_Attribute15,
200 X_Government_Context,
201 X_Project_Id,
202 X_Task_Id,
203 X_Expenditure_Type,
204 X_Project_Accounting_Context,
205 X_Expenditure_Organization_Id,
206 X_Gl_Closed_Date,
207 X_Source_Req_Distribution_Id,
208 X_Distribution_Num,
209 X_Project_Related_Flag,
210 X_Expenditure_Item_Date,
211 X_End_Item_Unit_Number,
212 X_Recovery_Rate,
213 -- JFMIP START
214 X_Recoverable_Tax,
215 X_Nonrecoverable_Tax,
216 -- JFMIP END
217 X_Tax_Recovery_Override_Flag,
218 X_Award_id, -- OGM_0.0 Changes
219 --togeorge 10/03/2000
220 -- added oke columns
221 x_oke_contract_line_id,
222 x_oke_contract_deliverable_id,
223 p_org_id -- <R12 MOAC>
224 );
225
226 OPEN C;
227 FETCH C INTO X_Rowid;
228 if (C%NOTFOUND) then
229 CLOSE C;
230 Raise NO_DATA_FOUND;
231 end if;
232 CLOSE C;
233
234
235 END Insert_Row;
236
237
238 PROCEDURE Update_Row(X_Rowid VARCHAR2,
239 X_Distribution_Id NUMBER,
240 X_Last_Update_Date DATE,
241 X_Last_Updated_By NUMBER,
242 X_Requisition_Line_Id NUMBER,
243 X_Set_Of_Books_Id NUMBER,
244 X_Code_Combination_Id NUMBER,
245 X_Req_Line_Quantity NUMBER,
246 X_Req_Line_Amount NUMBER, -- <SERVICES FPJ>
247 X_Req_Line_Currency_Amount NUMBER, -- <SERVICES FPJ>
248 X_Last_Update_Login NUMBER,
249 X_Encumbered_Flag VARCHAR2,
250 X_Gl_Encumbered_Date DATE,
251 X_Gl_Encumbered_Period_Name VARCHAR2,
252 X_Gl_Cancelled_Date DATE,
253 X_Failed_Funds_Lookup_Code VARCHAR2,
254 X_Encumbered_Amount NUMBER,
255 X_Budget_Account_Id NUMBER,
256 X_Accrual_Account_Id NUMBER,
257 X_Variance_Account_Id NUMBER,
258 X_Prevent_Encumbrance_Flag VARCHAR2,
259 X_Attribute_Category VARCHAR2,
260 X_Attribute1 VARCHAR2,
261 X_Attribute2 VARCHAR2,
262 X_Attribute3 VARCHAR2,
263 X_Attribute4 VARCHAR2,
264 X_Attribute5 VARCHAR2,
265 X_Attribute6 VARCHAR2,
266 X_Attribute7 VARCHAR2,
267 X_Attribute8 VARCHAR2,
268 X_Attribute9 VARCHAR2,
269 X_Attribute10 VARCHAR2,
270 X_Attribute11 VARCHAR2,
271 X_Attribute12 VARCHAR2,
272 X_Attribute13 VARCHAR2,
273 X_Attribute14 VARCHAR2,
274 X_Attribute15 VARCHAR2,
275 X_Ussgl_Transaction_Code VARCHAR2,
276 X_Government_Context VARCHAR2,
277 X_Project_Id NUMBER,
278 X_Task_Id NUMBER,
279 X_Expenditure_Type VARCHAR2,
280 X_Project_Accounting_Context VARCHAR2,
281 X_Expenditure_Organization_Id NUMBER,
282 X_Gl_Closed_Date DATE,
283 X_Source_Req_Distribution_Id NUMBER,
284 X_Distribution_Num NUMBER,
285 X_Project_Related_Flag VARCHAR2,
286 X_Expenditure_Item_Date DATE,
287 X_End_Item_Unit_Number VARCHAR2 DEFAULT NULL,
288 X_Recovery_Rate NUMBER,
289 X_Recoverable_Tax NUMBER,
290 X_Nonrecoverable_Tax NUMBER,
291 X_Tax_Recovery_Override_Flag VARCHAR2,
292 -- OGM_0.0 changes..
293 X_Award_id NUMBER DEFAULT NULL,
294 --togeorge 10/03/2000
295 -- added oke columns
296 x_oke_contract_line_id NUMBER default null,
297 x_oke_contract_deliverable_id NUMBER default null) IS
298
299
300 BEGIN
301 /*sugupta 760675 To avoid insynchronity between the client and server variables,
302 which caused the encumbered_flag and encumbered_amount passed from the client
303 as a variable (but was different from the values in the database, we update
304 encumbered_flag and encumbered_amount using database values rather than client
305 varables.
306 */
307
308 UPDATE PO_REQ_DISTRIBUTIONS
309 SET
310 distribution_id = X_Distribution_Id,
311 last_update_date = X_Last_Update_Date,
312 last_updated_by = X_Last_Updated_By,
313 requisition_line_id = X_Requisition_Line_Id,
314 set_of_books_id = X_Set_Of_Books_Id,
315 code_combination_id = X_Code_Combination_Id,
316 req_line_quantity = X_Req_Line_Quantity,
317 req_line_amount = X_Req_Line_Amount, -- <SERVICES FPJ>
318 req_line_currency_amount = X_Req_Line_Currency_Amount, -- <SERVICES FPJ>
319 last_update_login = X_Last_Update_Login,
320 encumbered_flag = encumbered_Flag,
321 gl_encumbered_date = X_Gl_Encumbered_Date,
322 gl_encumbered_period_name = X_Gl_Encumbered_Period_Name,
323 gl_cancelled_date = X_Gl_Cancelled_Date,
324 failed_funds_lookup_code = X_Failed_Funds_Lookup_Code,
325 encumbered_amount = encumbered_Amount,
326 budget_account_id = X_Budget_Account_Id,
327 accrual_account_id = X_Accrual_Account_Id,
328 variance_account_id = X_Variance_Account_Id,
329 prevent_encumbrance_flag = X_Prevent_Encumbrance_Flag,
330 attribute_category = X_Attribute_Category,
331 attribute1 = X_Attribute1,
332 attribute2 = X_Attribute2,
333 attribute3 = X_Attribute3,
334 attribute4 = X_Attribute4,
335 attribute5 = X_Attribute5,
336 attribute6 = X_Attribute6,
337 attribute7 = X_Attribute7,
338 attribute8 = X_Attribute8,
339 attribute9 = X_Attribute9,
340 attribute10 = X_Attribute10,
341 attribute11 = X_Attribute11,
342 attribute12 = X_Attribute12,
343 attribute13 = X_Attribute13,
344 attribute14 = X_Attribute14,
345 attribute15 = X_Attribute15,
346 government_context = X_Government_Context,
347 project_id = X_Project_Id,
348 task_id = X_Task_Id,
349 expenditure_type = X_Expenditure_Type,
350 project_accounting_context = X_Project_Accounting_Context,
351 expenditure_organization_id = X_Expenditure_Organization_Id,
352 gl_closed_date = X_Gl_Closed_Date,
353 source_req_distribution_id = X_Source_Req_Distribution_Id,
354 distribution_num = X_Distribution_Num,
355 project_related_flag = X_Project_Related_Flag,
356 expenditure_item_date = X_Expenditure_Item_Date,
357 end_item_unit_number = X_End_Item_Unit_Number,
358 recovery_rate = X_Recovery_Rate,
359 tax_recovery_override_flag = X_Tax_Recovery_Override_Flag,
360 Award_id = X_Award_id,
361 --togeorge 10/03/2000
362 -- added oke columns
363 oke_contract_line_id = x_oke_contract_line_id,
364 oke_contract_deliverable_id = x_oke_contract_deliverable_id
365 WHERE rowid = X_rowid;
366
367 if (SQL%NOTFOUND) then
368 Raise NO_DATA_FOUND;
369 end if;
370
371
372 END Update_Row;
373
374 END PO_REQ_DISTRIBUTIONS_PKG1;