[Home] [Help]
PACKAGE BODY: APPS.IGI_RPI_ITEMS_PKG
Source
1 PACKAGE BODY IGI_RPI_ITEMS_PKG AS
2 --- $Header: igiritmb.pls 120.3.12000000.1 2007/08/31 05:52:44 mbremkum ship $
3
4 l_debug_level number:=FND_LOG.G_CURRENT_RUNTIME_LEVEL;
5
6 l_state_level number:=FND_LOG.LEVEL_STATEMENT;
7 l_proc_level number:=FND_LOG.LEVEL_PROCEDURE;
8 l_event_level number:=FND_LOG.LEVEL_EVENT;
9 l_excep_level number:=FND_LOG.LEVEL_EXCEPTION;
10 l_error_level number:=FND_LOG.LEVEL_ERROR;
11 l_unexp_level number:=FND_LOG.LEVEL_UNEXPECTED;
12
13 PROCEDURE Insert_Row( X_Item_Id IN NUMBER
14 , X_Set_Of_Books_Id IN NUMBER
15 , X_Item_Code IN VARCHAR2
16 , X_Price IN NUMBER
17 , X_Price_Effective_Date IN DATE
18 , X_Unit_Of_Measure IN VARCHAR2
19 , X_Start_Effective_Date IN DATE
20 , X_Creation_Date IN DATE
21 , X_Created_By IN NUMBER
22 , X_Last_Update_Date IN DATE
23 , X_Last_Updated_By IN NUMBER
24 , X_Last_Update_Login IN NUMBER
25 , X_Revised_Price_Eff_Date IN DATE
26 , X_Revised_Price IN NUMBER
27 , X_Description IN VARCHAR2
28 , X_Vat_Tax_Id IN NUMBER
29 , X_Revenue_Code_Combination_Id IN NUMBER
30 , X_Inactive_Date IN DATE
31 , X_Enabled_Flag IN VARCHAR2
32 /*MOAC IMpact Bug No 5905216*/
33 , X_Org_id IN NUMBER )
34 IS
35 l_Item_Id NUMBER;
36 l_Row_ID VARCHAR(30);
37
38 CURSOR Cursor_Row_ID IS SELECT rowid
39 FROM igi_rpi_items
40 WHERE item_id = X_Item_Id;
41
42 CURSOR Cursor_Item_id IS SELECT igi_rpi_items_s.nextval FROM sys.dual;
43
44 BEGIN
45 IF (X_Item_Id is NULL)
46 THEN
47 Open Cursor_Item_id;
48 Fetch Cursor_Item_id INTO l_Item_Id;
49
50 IF (Cursor_Item_id%NOTFOUND)
51 THEN
52 Raise NO_DATA_FOUND;
53 END IF;
54
55 Close Cursor_Item_id;
56 END IF;
57
58 LOCK TABLE igi_rpi_items IN SHARE UPDATE MODE;
59 IF (X_Item_Id is NOT NULL)
60 THEN
61 INSERT INTO igi_rpi_items( item_id
62 , set_of_books_id
63 , item_code
64 , price
65 , price_effective_date
66 , unit_of_measure
67 , start_effective_date
68 , creation_date
69 , created_by
70 , last_update_date
71 , last_updated_by
72 , last_update_login
73 , revised_price_eff_date
74 , revised_price
75 , description
76 , vat_tax_id
77 , revenue_code_combination_id
78 , inactive_date
79 , enabled_flag
80 , org_id
81 )
82 VALUES ( X_Item_Id
83 , X_Set_Of_Books_Id
84 , X_Item_Code
85 , X_Price
86 , X_Price_Effective_Date
87 , X_Unit_Of_Measure
88 , X_Start_Effective_Date
89 , X_Creation_Date
90 , X_Created_By
91 , X_Last_Update_Date
92 , X_Last_Updated_By
93 , X_Last_Update_Login
94 , X_Revised_Price_Eff_Date
95 , X_Revised_Price
96 , X_Description
97 , X_Vat_Tax_Id
98 , X_Revenue_Code_Combination_Id
99 , X_Inactive_Date
100 , X_Enabled_Flag
101 , X_Org_Id
102 );
103 END IF;
104 Open Cursor_Row_ID;
105 Fetch Cursor_Row_ID INTO l_Row_ID;
106
107 IF (Cursor_Row_ID%NOTFOUND)
108 THEN
109 Raise NO_DATA_FOUND;
110 END IF;
111 Close Cursor_Row_ID;
112
113 EXCEPTION
114 WHEN NO_DATA_FOUND THEN RETURN;
115 WHEN app_exceptions.application_exception THEN RAISE;
116 WHEN OTHERS THEN
117 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
118 fnd_message.set_token('PROCEDURE',
119 'IGI_RPI_ITEMS_PKG.Insert_Row');
120 -- bug 3199481, start block
121 IF (l_unexp_level >= l_debug_level) THEN
122 FND_MESSAGE.SET_NAME('IGI', 'IGI_LOGGING_UNEXP_ERROR');
123 FND_MESSAGE.SET_TOKEN('CODE', sqlcode);
124 FND_MESSAGE.SET_TOKEN('MSG', sqlerrm);
125
126 FND_LOG.MESSAGE(l_unexp_level, 'igi.plsql.igi_rpi_items_pkg.insert_row.Msg1',TRUE);
127 END IF;
128 -- bug 3199481, end block
129 RAISE;
130
131 END Insert_Row;
132
133
134 PROCEDURE Update_Row( X_Item_Id IN NUMBER
135 , X_Set_Of_Books_Id IN NUMBER
136 , X_Item_Code IN VARCHAR2
137 , X_Price IN NUMBER
138 , X_Price_Effective_Date IN DATE
139 , X_Unit_Of_Measure IN VARCHAR2
140 , X_Start_Effective_Date IN DATE
141 , X_Last_Update_Date IN DATE
142 , X_Last_Updated_By IN NUMBER
143 , X_Last_Update_Login IN NUMBER
144 , X_Revised_Price_Eff_Date IN DATE
145 , X_Revised_Price IN NUMBER
146 , X_Description IN VARCHAR2
147 , X_Vat_Tax_Id IN NUMBER
148 , X_Revenue_Code_Combination_Id IN NUMBER
149 , X_Inactive_Date IN DATE
150 , X_Enabled_Flag IN VARCHAR2 )
151 IS
152 BEGIN
153
154 LOCK TABLE igi_rpi_items IN SHARE UPDATE MODE;
155
156 UPDATE igi_rpi_items
157 SET item_code = X_Item_Code
158 , price = X_Price
159 , price_effective_date = X_Price_Effective_Date
160 , unit_of_measure = X_Unit_Of_Measure
161 , start_effective_date = X_Start_Effective_Date
162 , last_update_date = X_Last_Update_Date
163 , last_updated_by = X_Last_Updated_By
164 , last_update_login = X_Last_Update_Login
165 , revised_price_eff_date = X_Revised_Price_Eff_Date
166 , revised_price = X_Revised_Price
167 , description = X_Description
168 , vat_tax_id = X_Vat_Tax_Id
169 , revenue_code_combination_id = X_Revenue_Code_Combination_Id
170 , inactive_date = X_Inactive_Date
171 , enabled_flag = X_Enabled_Flag
172 WHERE Item_Id = X_Item_Id
173 AND Set_Of_Books_Id = X_Set_Of_Books_Id;
174
175 EXCEPTION
176 WHEN NO_DATA_FOUND THEN RETURN;
177 WHEN app_exceptions.application_exception THEN RAISE;
178 WHEN OTHERS THEN
179 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
180 fnd_message.set_token('PROCEDURE',
181 'IGI_RPI_ITEMS_PKG.Update_Row');
182 -- bug 3199481, start block
183 IF (l_unexp_level >= l_debug_level) THEN
184 FND_MESSAGE.SET_NAME('IGI', 'IGI_LOGGING_UNEXP_ERROR');
185 FND_MESSAGE.SET_TOKEN('CODE', sqlcode);
186 FND_MESSAGE.SET_TOKEN('MSG', sqlerrm);
187
188 FND_LOG.MESSAGE(l_unexp_level, 'igi.plsql.igi_rpi_items_pkg.update_row.Msg1',TRUE);
189 END IF;
190 -- bug 3199481, end block
191 RAISE;
192
193 END Update_Row;
194
195
196 PROCEDURE Delete_Row(X_Item_Id NUMBER)
197 IS
198 BEGIN
199 DELETE FROM igi_rpi_items
200 WHERE Item_Id = X_Item_Id;
201
202 IF (SQL%NOTFOUND)
203 THEN
204 Raise NO_DATA_FOUND;
205 END IF;
206
207 END Delete_Row;
208
209 END IGI_RPI_ITEMS_PKG;