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