DBA Data[Home] [Help]

PACKAGE BODY: APPS.PA_EXPENDITURE_TYPES_PKG

Source


1 PACKAGE BODY PA_EXPENDITURE_TYPES_PKG as
2 /* $Header: PAXTETSB.pls 120.3 2005/08/17 23:03:57 vthakkar noship $ */
3 
4   PROCEDURE Insert_Row(X_Rowid                   IN OUT NOCOPY VARCHAR2,
5                        X_Expenditure_Type           VARCHAR2,
6                        X_Last_Update_Date           DATE,
7                        X_Last_Updated_By            NUMBER,
8                        X_Creation_Date              DATE,
9                        X_Created_By                 NUMBER,
10                        X_Last_Update_Login          NUMBER,
11                        X_Expenditure_Category       VARCHAR2,
12                        X_Revenue_Category_Code      VARCHAR2,
13                        X_System_Linkage_Function    VARCHAR2,
14                        X_Unit_Of_Measure            VARCHAR2,
15                        X_Start_Date_Active          DATE,
16                        X_Cost_Rate_Flag             VARCHAR2,
17                        X_End_Date_Active            DATE,
18                        X_Description                VARCHAR2,
19                        X_Attribute_Category         VARCHAR2,
20                        X_Attribute1                 VARCHAR2,
21                        X_Attribute2                 VARCHAR2,
22                        X_Attribute3                 VARCHAR2,
23                        X_Attribute4                 VARCHAR2,
24                        X_Attribute5                 VARCHAR2,
25                        X_Attribute6                 VARCHAR2,
26                        X_Attribute7                 VARCHAR2,
27                        X_Attribute8                 VARCHAR2,
28                        X_Attribute9                 VARCHAR2,
29                        X_Attribute10                VARCHAR2,
30                        X_Attribute11                VARCHAR2,
31                        X_Attribute12                VARCHAR2,
32                        X_Attribute13                VARCHAR2,
33                        X_Attribute14                VARCHAR2,
34                        X_Attribute15                VARCHAR2
35                       -- MOAC changes
36               --        , X_output_tax_code            VARCHAR2
37 
38   ) IS
39     CURSOR C IS SELECT rowid FROM pa_expenditure_types
40                  WHERE expenditure_type = X_Expenditure_Type;
41 
42     L_output_vat_tax_id          NUMBER;
43 
44    BEGIN
45 
46 
47        INSERT INTO pa_expenditure_types(
48 	      expenditure_type_id,
49               expenditure_type,
50               last_update_date,
51               last_updated_by,
52               creation_date,
53               created_by,
54               last_update_login,
55               expenditure_category,
56               revenue_category_code,
57               system_linkage_function,
58               unit_of_measure,
59               start_date_active,
60               cost_rate_flag,
61               end_date_active,
62               description,
63               attribute_category,
64               attribute1,
65               attribute2,
66               attribute3,
67               attribute4,
68               attribute5,
69               attribute6,
70               attribute7,
71               attribute8,
72               attribute9,
73               attribute10,
74               attribute11,
75               attribute12,
76               attribute13,
77               attribute14,
78               attribute15
79              ) VALUES (
80 	      pa_exp_event_type_s.nextval ,
81               X_Expenditure_Type,
82               X_Last_Update_Date,
83               X_Last_Updated_By,
84               X_Creation_Date,
85               X_Created_By,
86               X_Last_Update_Login,
87               X_Expenditure_Category,
88               X_Revenue_Category_Code,
89               X_System_Linkage_Function,
90               X_Unit_Of_Measure,
91               X_Start_Date_Active,
92               X_Cost_Rate_Flag,
93               X_End_Date_Active,
94               X_Description,
95               X_ATTRIBUTE_CATEGORY,
96               X_ATTRIBUTE1,
97               X_ATTRIBUTE2,
98               X_ATTRIBUTE3,
99               X_ATTRIBUTE4,
100               X_ATTRIBUTE5,
101               X_ATTRIBUTE6,
102               X_ATTRIBUTE7,
103               X_ATTRIBUTE8,
104               X_ATTRIBUTE9,
105               X_ATTRIBUTE10,
106               X_ATTRIBUTE11,
107               X_ATTRIBUTE12,
108               X_ATTRIBUTE13,
109               X_ATTRIBUTE14,
110               X_ATTRIBUTE15
111              );
112 
113  -- Tax impact changes
114 --MOAC chagnes`
115 --       IF X_output_tax_code IS NOT NULL THEN
116 
117 --       BEGIN
118 
119 /*
120         SELECT vat_tax_id
121           INTO L_output_vat_tax_id
122           FROM pa_output_tax_code_setup_v
123          WHERE tax_code = x_output_tax_code ;
124 */ --for eTax changes
125 /*
126         INSERT INTO pa_expenditure_type_ous(
127               expenditure_type,
128               output_tax_classification_code,
129               last_update_date,
130               last_updated_by,
131               creation_date,
132               created_by,
133               last_update_login
134               )
135         VALUES
136              (
137  	     X_expenditure_type,
138 	     X_output_tax_code,
139 	     X_last_update_date,
140 	     X_last_updated_by,
141 	     X_creation_date,
142 	     X_created_by,
143 	     X_last_update_login
144              );
145 
146        END;
147 
148      END IF; */
149 
150     OPEN C;
151     FETCH C INTO X_Rowid;
152     if (C%NOTFOUND) then
153       CLOSE C;
154       Raise NO_DATA_FOUND;
155     end if;
156     CLOSE C;
157   END Insert_Row;
158 
159 
160   PROCEDURE Lock_Row(X_Rowid                      VARCHAR2,
161                      X_Expenditure_Type           VARCHAR2,
162                      X_Last_Update_Date           DATE,
163                      X_Last_Updated_By            NUMBER,
164                      X_Creation_Date              DATE,
165                      X_Created_By                 NUMBER,
166                      X_Last_Update_Login          NUMBER,
167                      X_Expenditure_Category       VARCHAR2,
168                      X_Revenue_Category_Code      VARCHAR2,
169                      X_System_Linkage_Function    VARCHAR2,
170                      X_Unit_Of_Measure            VARCHAR2,
171                      X_Start_Date_Active          DATE,
172                      X_Cost_Rate_Flag             VARCHAR2,
173                      X_End_Date_Active            DATE,
174                      X_Description                VARCHAR2,
175                      X_Attribute_Category         VARCHAR2,
176                      X_Attribute1                 VARCHAR2,
177                      X_Attribute2                 VARCHAR2,
178                      X_Attribute3                 VARCHAR2,
179                      X_Attribute4                 VARCHAR2,
180                      X_Attribute5                 VARCHAR2,
181                      X_Attribute6                 VARCHAR2,
182                      X_Attribute7                 VARCHAR2,
183                      X_Attribute8                 VARCHAR2,
184                      X_Attribute9                 VARCHAR2,
185                      X_Attribute10                VARCHAR2,
186                      X_Attribute11                VARCHAR2,
187                      X_Attribute12                VARCHAR2,
188                      X_Attribute13                VARCHAR2,
189                      X_Attribute14                VARCHAR2,
190                      X_Attribute15                VARCHAR2
191                    -- MOAC changes
192                     --, X_output_tax_code            VARCHAR2
193   ) IS
194     CURSOR C IS
195         SELECT *
196         FROM   pa_expenditure_types
197         WHERE  rowid = X_Rowid
198         FOR UPDATE of Expenditure_Type NOWAIT;
199     Recinfo C%ROWTYPE;
200 
201 
202   BEGIN
203     OPEN C;
204     FETCH C INTO Recinfo;
205     if (C%NOTFOUND) then
206       CLOSE C;
207       FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
208       APP_EXCEPTION.Raise_Exception;
209     end if;
210     CLOSE C;
211     if (
212               (    Recinfo.expenditure_type        = X_Expenditure_Type)
213          AND  (    Recinfo.expenditure_category    = X_Expenditure_Category)
214          AND  (    Recinfo.revenue_category_code   = X_Revenue_Category_Code)
215          AND  (    Recinfo.unit_of_measure         = X_Unit_Of_Measure)
216          AND  (    Recinfo.cost_rate_flag          = X_Cost_Rate_Flag)
217          AND  (   (Recinfo.start_date_active       = X_Start_Date_Active)
218                OR (    (Recinfo.start_date_active IS NULL)
219                    AND (X_Start_Date_Active IS NULL)))
220          AND  (   (Recinfo.end_date_active         = X_End_Date_Active)
221                OR (    (Recinfo.end_date_active IS NULL)
222                    AND (X_End_Date_Active IS NULL)))
223          AND  (   (Recinfo.description             = X_Description)
224                OR (    (Recinfo.description IS NULL)
225                    AND (X_Description IS NULL)))
226          AND  (   (Recinfo.attribute_category      = X_Attribute_Category)
227                OR (    (Recinfo.attribute_category IS NULL)
228                    AND (X_Attribute_Category IS NULL)))
229          AND  (   (Recinfo.attribute1              = X_Attribute1)
230                OR (    (Recinfo.attribute1 IS NULL)
231                    AND (X_Attribute1 IS NULL)))
232          AND  (   (Recinfo.attribute2              = X_Attribute2)
233                OR (    (Recinfo.attribute2 IS NULL)
234                    AND (X_Attribute2 IS NULL)))
235          AND  (   (Recinfo.attribute3              = X_Attribute3)
236                OR (    (Recinfo.attribute3 IS NULL)
237                    AND (X_Attribute3 IS NULL)))
238          AND  (   (Recinfo.attribute4              = X_Attribute4)
239                OR (    (Recinfo.attribute4 IS NULL)
240                    AND (X_Attribute4 IS NULL)))
241          AND  (   (Recinfo.attribute5              = X_Attribute5)
242                OR (    (Recinfo.attribute5 IS NULL)
243                    AND (X_Attribute5 IS NULL)))
244          AND  (   (Recinfo.attribute6              = X_Attribute6)
245                OR (    (Recinfo.attribute6 IS NULL)
246                    AND (X_Attribute6 IS NULL)))
247          AND  (   (Recinfo.attribute7              = X_Attribute7)
248                OR (    (Recinfo.attribute7 IS NULL)
249                    AND (X_Attribute7 IS NULL)))
250          AND  (   (Recinfo.attribute8              = X_Attribute8)
251                OR (    (Recinfo.attribute8 IS NULL)
252                    AND (X_Attribute8 IS NULL)))
253          AND  (   (Recinfo.attribute9              = X_Attribute9)
254                OR (    (Recinfo.attribute9 IS NULL)
255                    AND (X_Attribute9 IS NULL)))
256          AND  (   (Recinfo.attribute10             = X_Attribute10)
257                OR (    (Recinfo.attribute10 IS NULL)
258                    AND (X_Attribute10 IS NULL)))
259          AND  (   (Recinfo.attribute11             = X_Attribute11)
260                OR (    (Recinfo.attribute11 IS NULL)
261                    AND (X_Attribute11 IS NULL)))
262          AND  (   (Recinfo.attribute12             = X_Attribute12)
263                OR (    (Recinfo.attribute12 IS NULL)
264                    AND (X_Attribute12 IS NULL)))
265          AND  (   (Recinfo.attribute13             = X_Attribute13)
266                OR (    (Recinfo.attribute13 IS NULL)
267                    AND (X_Attribute13 IS NULL)))
268          AND  (   (Recinfo.attribute14             = X_Attribute14)
269                OR (    (Recinfo.attribute14 IS NULL)
270                    AND (X_Attribute14 IS NULL)))
271          AND  (   (Recinfo.attribute15             = X_Attribute15)
272                OR (    (Recinfo.attribute15 IS NULL)
273                    AND (X_Attribute15 IS NULL)))
274       ) then
275       return;
276     else
277       FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
278       APP_EXCEPTION.Raise_Exception;
279     end if;
280   END Lock_Row;
281 
282 
283 
284   PROCEDURE Update_Row(X_Rowid                      VARCHAR2,
285                        X_Expenditure_Type           VARCHAR2,
286                        X_Last_Update_Date           DATE,
287                        X_Last_Updated_By            NUMBER,
288                        X_Creation_Date              DATE,
289                        X_Created_By                 NUMBER,
290                        X_Last_Update_Login          NUMBER,
291                        X_Expenditure_Category       VARCHAR2,
292                        X_Revenue_Category_Code      VARCHAR2,
293                        X_System_Linkage_Function    VARCHAR2,
294                        X_Unit_Of_Measure            VARCHAR2,
295                        X_Start_Date_Active          DATE,
296                        X_Cost_Rate_Flag             VARCHAR2,
297                        X_End_Date_Active            DATE,
298                        X_Description                VARCHAR2,
299                        X_Attribute_Category         VARCHAR2,
300                        X_Attribute1                 VARCHAR2,
301                        X_Attribute2                 VARCHAR2,
302                        X_Attribute3                 VARCHAR2,
303                        X_Attribute4                 VARCHAR2,
304                        X_Attribute5                 VARCHAR2,
305                        X_Attribute6                 VARCHAR2,
306                        X_Attribute7                 VARCHAR2,
307                        X_Attribute8                 VARCHAR2,
308                        X_Attribute9                 VARCHAR2,
309                        X_Attribute10                VARCHAR2,
310                        X_Attribute11                VARCHAR2,
311                        X_Attribute12                VARCHAR2,
312                        X_Attribute13                VARCHAR2,
313                        X_Attribute14                VARCHAR2,
314                        X_Attribute15                VARCHAR2
315                    --MOAC changes
316                    -- , X_output_tax_code            VARCHAR2
317   ) IS
318 
319     L_output_vat_tax_id    NUMBER;
320 
321   BEGIN
322     UPDATE pa_expenditure_types
323     SET
324        expenditure_type         =  X_Expenditure_Type,
325        last_update_date         =  X_Last_Update_Date,
326        last_updated_by          =  X_Last_Updated_By,
327        creation_date            =  X_Creation_Date,
328        created_by               =  X_Created_By,
329        last_update_login        =  X_Last_Update_Login,
330        expenditure_category     =  X_Expenditure_Category,
331        revenue_category_code    =  X_Revenue_Category_Code,
332        system_linkage_function  =  X_System_Linkage_Function,
333        unit_of_measure          =  X_Unit_Of_Measure,
334        start_date_active        =  X_Start_Date_Active,
335        cost_rate_flag           =  X_Cost_Rate_Flag,
336        end_date_active          =  X_End_Date_Active,
337        description              =  X_Description,
338        attribute_category       =  X_Attribute_Category,
339        attribute1               =  X_Attribute1,
340        attribute2               =  X_Attribute2,
341        attribute3               =  X_Attribute3,
342        attribute4               =  X_Attribute4,
343        attribute5               =  X_Attribute5,
344        attribute6               =  X_Attribute6,
345        attribute7               =  X_Attribute7,
346        attribute8               =  X_Attribute8,
347        attribute9               =  X_Attribute9,
348        attribute10              =  X_Attribute10,
349        attribute11              =  X_Attribute11,
350        attribute12              =  X_Attribute12,
351        attribute13              =  X_Attribute13,
352        attribute14              =  X_Attribute14,
353        attribute15              =  X_Attribute15
354     WHERE rowid = X_Rowid;
355 
356     if (SQL%NOTFOUND) then
357       Raise NO_DATA_FOUND;
358     end if;
359 
360  -- Tax impact changes
361 -- MOAC changes
362 /*
363       IF X_output_tax_code IS NOT NULL THEN
364 
365        BEGIN
366 
367         SELECT vat_tax_id
368           INTO L_output_vat_tax_id
369           FROM pa_output_tax_code_setup_v
370          WHERE tax_code = x_output_tax_code ;
371 
372         UPDATE pa_expenditure_type_ous
373            SET output_tax_classification_code = X_output_tax_code,
374                last_update_date  = X_last_update_date,
375                last_updated_by   = X_last_updated_by
376          WHERE expenditure_type = X_expenditure_type;
377 
378  -- If no row exists then create new row
379  --
380         IF SQL%rowcount = 0 THEN
381          INSERT INTO pa_expenditure_type_ous(
382               expenditure_type,
383               output_tax_classification_code,
384               last_update_date,
385               last_updated_by,
386               creation_date,
387               created_by,
388               last_update_login
389               )
390          VALUES
391              (
392  	     X_expenditure_type,
393 	     X_output_tax_code,
394 	     X_last_update_date,
395 	     X_last_updated_by,
396 	     X_last_update_date,
397 	     X_last_updated_by,
398 	     X_last_update_login
399              );
400 
401        END IF;
402       END;
403 
404      ELSE -- If the X_output_tax_code is NULL. Added else part in 1782377.
405 
406 	 delete from pa_expenditure_type_ous
407 	 where expenditure_type = X_expenditure_type;
408 
409      END IF;
410 */
411   END Update_Row;
412 
413 END PA_EXPENDITURE_TYPES_PKG;