DBA Data[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;