[Home] [Help]
PACKAGE BODY: APPS.MTL_IC_PARAMETERS_PKG
Source
1 PACKAGE BODY MTL_IC_PARAMETERS_PKG as
2 /* $Header: INVSDICB.pls 115.9 2003/11/24 19:23:42 sthamman ship $ */
3
4 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
5 X_Ship_Organization_Id NUMBER,
6 X_Sell_Organization_Id NUMBER,
7 X_Last_Update_Date DATE,
8 X_Last_Updated_By NUMBER,
9 X_Creation_Date DATE,
10 X_Created_By NUMBER,
11 X_Last_Update_Login NUMBER,
12 X_Vendor_Id NUMBER,
13 X_Vendor_Site_Id NUMBER,
14 X_Customer_Id NUMBER,
15 X_Address_Id NUMBER,
16 X_Customer_Site_Id NUMBER,
17 X_Cust_Trx_Type_Id NUMBER,
18 X_Attribute_Category VARCHAR2,
19 X_Attribute1 VARCHAR2,
20 X_Attribute2 VARCHAR2,
21 X_Attribute3 VARCHAR2,
22 X_Attribute4 VARCHAR2,
23 X_Attribute5 VARCHAR2,
24 X_Attribute6 VARCHAR2,
25 X_Attribute7 VARCHAR2,
26 X_Attribute8 VARCHAR2,
27 X_Attribute9 VARCHAR2,
28 X_Attribute10 VARCHAR2,
29 X_Attribute11 VARCHAR2,
30 X_Attribute12 VARCHAR2,
31 X_Attribute13 VARCHAR2,
32 X_Attribute14 VARCHAR2,
33 X_Attribute15 VARCHAR2,
34 X_Revalue_Average_Flag VARCHAR2,
35 X_Freight_Code_Combination_Id NUMBER,
36 X_Inv_Currency_Code NUMBER,
37 X_Flow_Type NUMBER DEFAULT NULL, -- added as part of patchset-j development
38 X_Intercompany_COGS_Account_Id NUMBER DEFAULT NULL,
39 X_Inventory_Accrual_Account_Id NUMBER DEFAULT NULL,
40 X_Expense_Accrual_Account_Id NUMBER DEFAULT NULL
41 ) IS
42
43 CURSOR C IS
44 SELECT rowid FROM MTL_INTERCOMPANY_PARAMETERS
45 WHERE ship_organization_id = X_Ship_Organization_Id
46 AND sell_organization_id = X_Sell_Organization_Id
47 AND ( (inv_control.get_current_release_level < inv_release.GET_J_RELEASE_LEVEL)
48 OR
49 (inv_control.get_current_release_level >= inv_release.GET_J_RELEASE_LEVEL AND flow_type = X_Flow_Type)
50 );
51
52 l_Flow_Type NUMBER;
53 l_Intercompany_COGS_Account_Id NUMBER;
54 l_Inventory_Accrual_Account_Id NUMBER;
55 l_Expense_Accrual_Account_Id NUMBER;
56
57 BEGIN
58
59 IF inv_control.get_current_release_level >= inv_release.GET_J_RELEASE_LEVEL THEN
60 l_Flow_Type := X_Flow_Type;
61 l_Intercompany_COGS_Account_Id := X_Intercompany_COGS_Account_Id;
62 l_Inventory_Accrual_Account_Id := X_Inventory_Accrual_Account_Id ;
63 l_Expense_Accrual_Account_Id := X_Expense_Accrual_Account_Id;
64 ELSE
65 l_Flow_Type := 1; -- Should be always 1 (Shipping) if INV-J is not installed. Bug# 3271622
66 l_Intercompany_COGS_Account_Id := NULL;
67 l_Inventory_Accrual_Account_Id := NULL;
68 l_Expense_Accrual_Account_Id := NULL;
69 END IF;
70
71 INSERT INTO MTL_INTERCOMPANY_PARAMETERS(
72 ship_organization_id,
73 sell_organization_id,
74 last_update_date,
75 last_updated_by,
76 creation_date,
77 created_by,
78 last_update_login,
79 vendor_id,
80 vendor_site_id,
81 customer_id,
82 address_id,
83 customer_site_id,
84 cust_trx_type_id,
85 attribute_category,
86 attribute1,
87 attribute2,
88 attribute3,
89 attribute4,
90 attribute5,
91 attribute6,
92 attribute7,
93 attribute8,
94 attribute9,
95 attribute10,
96 attribute11,
97 attribute12,
98 attribute13,
99 attribute14,
100 attribute15,
101 revalue_average_flag,
102 freight_code_combination_id,
103 inv_currency_code,
104 Flow_Type,
105 Intercompany_COGS_Account_Id,
106 Inventory_Accrual_Account_Id,
107 Expense_Accrual_Account_Id
108
109 ) VALUES (
110 X_Ship_Organization_Id,
111 X_Sell_Organization_Id,
112 X_Last_Update_Date,
113 X_Last_Updated_By,
114 X_Creation_Date,
115 X_Created_By,
116 X_Last_Update_Login,
117 X_Vendor_Id,
118 X_Vendor_Site_Id,
119 X_Customer_Id,
120 X_Address_Id,
121 X_Customer_Site_Id,
122 X_Cust_Trx_Type_Id,
123 X_Attribute_Category,
124 X_Attribute1,
125 X_Attribute2,
126 X_Attribute3,
127 X_Attribute4,
128 X_Attribute5,
129 X_Attribute6,
130 X_Attribute7,
131 X_Attribute8,
132 X_Attribute9,
133 X_Attribute10,
134 X_Attribute11,
135 X_Attribute12,
136 X_Attribute13,
137 X_Attribute14,
138 X_Attribute15,
139 X_Revalue_Average_Flag,
140 X_Freight_Code_Combination_Id,
141 X_Inv_Currency_Code,
142 l_Flow_Type,
143 l_Intercompany_COGS_Account_Id,
144 l_Inventory_Accrual_Account_Id,
145 l_Expense_Accrual_Account_Id
146 );
147
148
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
158 END Insert_Row;
159
160
161
162 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
163 X_Ship_Organization_Id NUMBER,
164 X_Sell_Organization_Id NUMBER,
165 X_Vendor_Id NUMBER,
166 X_Vendor_Site_Id NUMBER,
167 X_Customer_Id NUMBER,
168 X_Address_Id NUMBER,
169 X_Customer_Site_Id NUMBER,
170 X_Cust_Trx_Type_Id NUMBER,
171 X_Attribute_Category VARCHAR2,
172 X_Attribute1 VARCHAR2,
173 X_Attribute2 VARCHAR2,
174 X_Attribute3 VARCHAR2,
175 X_Attribute4 VARCHAR2,
176 X_Attribute5 VARCHAR2,
177 X_Attribute6 VARCHAR2,
178 X_Attribute7 VARCHAR2,
179 X_Attribute8 VARCHAR2,
180 X_Attribute9 VARCHAR2,
181 X_Attribute10 VARCHAR2,
182 X_Attribute11 VARCHAR2,
183 X_Attribute12 VARCHAR2,
184 X_Attribute13 VARCHAR2,
185 X_Attribute14 VARCHAR2,
186 X_Attribute15 VARCHAR2,
187 X_Revalue_Average_Flag VARCHAR2,
188 X_Freight_Code_Combination_Id NUMBER,
189 X_Inv_Currency_Code NUMBER,
190 X_Flow_Type NUMBER DEFAULT NULL, -- added as part of patchset-j development
191 X_Intercompany_COGS_Account_Id NUMBER DEFAULT NULL,
192 X_Inventory_Accrual_Account_Id NUMBER DEFAULT NULL,
193 X_Expense_Accrual_Account_Id NUMBER DEFAULT NULL
194 ) IS
195 CURSOR C IS
196 SELECT *
197 FROM MTL_INTERCOMPANY_PARAMETERS
198 WHERE rowid = X_Rowid
199 FOR UPDATE of Ship_Organization_Id NOWAIT;
200 Recinfo C%ROWTYPE;
201
202
203 BEGIN
204 OPEN C;
205 FETCH C INTO Recinfo;
206 if (C%NOTFOUND) then
207 CLOSE C;
208 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
209 APP_EXCEPTION.Raise_Exception;
210 end if;
211 CLOSE C;
212 if (
213 (Recinfo.ship_organization_id = X_Ship_Organization_Id)
214 AND (Recinfo.sell_organization_id = X_Sell_Organization_Id)
215 AND ( (Recinfo.vendor_id = X_Vendor_Id)
216 OR ( (Recinfo.vendor_id IS NULL)
217 AND (X_Vendor_Id IS NULL)))
218 AND ( (Recinfo.vendor_site_id = X_Vendor_Site_Id)
219 OR ( (Recinfo.vendor_site_id IS NULL)
220 AND (X_Vendor_Site_Id IS NULL)))
221 AND (Recinfo.customer_id = X_Customer_Id)
222 AND (Recinfo.address_id = X_Address_Id)
223 AND (Recinfo.customer_site_id = X_Customer_Site_Id)
224 AND (Recinfo.cust_trx_type_id = X_Cust_Trx_Type_Id)
225 AND ( (Recinfo.attribute_category = X_Attribute_Category)
226 OR ( (Recinfo.attribute_category IS NULL)
227 AND (X_Attribute_Category IS NULL)))
228 AND ( (Recinfo.attribute1 = X_Attribute1)
229 OR ( (Recinfo.attribute1 IS NULL)
230 AND (X_Attribute1 IS NULL)))
231 AND ( (Recinfo.attribute2 = X_Attribute2)
232 OR ( (Recinfo.attribute2 IS NULL)
233 AND (X_Attribute2 IS NULL)))
234 AND ( (Recinfo.attribute3 = X_Attribute3)
235 OR ( (Recinfo.attribute3 IS NULL)
236 AND (X_Attribute3 IS NULL)))
237 AND ( (Recinfo.attribute4 = X_Attribute4)
238 OR ( (Recinfo.attribute4 IS NULL)
239 AND (X_Attribute4 IS NULL)))
240 AND ( (Recinfo.attribute5 = X_Attribute5)
241 OR ( (Recinfo.attribute5 IS NULL)
242 AND (X_Attribute5 IS NULL)))
243 AND ( (Recinfo.attribute6 = X_Attribute6)
244 OR ( (Recinfo.attribute6 IS NULL)
245 AND (X_Attribute6 IS NULL)))
246 AND ( (Recinfo.attribute7 = X_Attribute7)
247 OR ( (Recinfo.attribute7 IS NULL)
248 AND (X_Attribute7 IS NULL)))
249 AND ( (Recinfo.attribute8 = X_Attribute8)
250 OR ( (Recinfo.attribute8 IS NULL)
251 AND (X_Attribute8 IS NULL)))
252 AND ( (Recinfo.attribute9 = X_Attribute9)
253 OR ( (Recinfo.attribute9 IS NULL)
254 AND (X_Attribute9 IS NULL)))
255 AND ( (Recinfo.attribute10 = X_Attribute10)
256 OR ( (Recinfo.attribute10 IS NULL)
257 AND (X_Attribute10 IS NULL)))
258 AND ( (Recinfo.attribute11 = X_Attribute11)
259 OR ( (Recinfo.attribute11 IS NULL)
260 AND (X_Attribute11 IS NULL)))
261 AND ( (Recinfo.attribute12 = X_Attribute12)
262 OR ( (Recinfo.attribute12 IS NULL)
263 AND (X_Attribute12 IS NULL)))
264 AND ( (Recinfo.attribute13 = X_Attribute13)
265 OR ( (Recinfo.attribute13 IS NULL)
266 AND (X_Attribute13 IS NULL)))
267 AND ( (Recinfo.attribute14 = X_Attribute14)
268 OR ( (Recinfo.attribute14 IS NULL)
269 AND (X_Attribute14 IS NULL)))
270 AND ( (Recinfo.attribute15 = X_Attribute15)
271 OR ( (Recinfo.attribute15 IS NULL)
272 AND (X_Attribute15 IS NULL)))
273 --Bug 2945914:since the revalue average flag is not supported thro this form
274 /* AND ( (Recinfo.revalue_average_flag = X_Revalue_Average_Flag)
275 OR ( (Recinfo.revalue_average_flag IS NULL)
276 AND (X_Revalue_Average_Flag IS NULL)))*/
277 AND ( (Recinfo.freight_code_combination_id = X_Freight_Code_Combination_Id)
278 OR ( (Recinfo.freight_code_combination_id IS NULL)
279 AND (X_Freight_Code_Combination_Id IS NULL)))
280 AND ( (Recinfo.inv_currency_code = X_Inv_Currency_Code)
281 OR ( (Recinfo.inv_currency_code IS NULL)
282 AND (X_Inv_Currency_Code IS NULL)))
283
284 ) then
285 return;
286 else
287 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
288 APP_EXCEPTION.Raise_Exception;
289 end if;
290
291 IF(inv_control.get_current_release_level >= inv_release.GET_J_RELEASE_LEVEL) Then
292
293 if (
294 (Recinfo.flow_type = X_Flow_Type) --added as part of patchset-j development
295 OR ( (Recinfo.flow_type IS NULL)
296 AND (X_Flow_Type IS NULL))
297 AND ( (Recinfo.intercompany_cogs_account_id = X_Intercompany_COGS_Account_id)
298 OR ( (Recinfo.intercompany_cogs_account_id IS NULL)
299 AND (X_Intercompany_COGS_Account_id IS NULL)))
300 AND ( (Recinfo.inventory_Accrual_account_id = X_Inventory_Accrual_Account_id)
301 OR ( (Recinfo.inventory_Accrual_account_id IS NULL)
302 AND (X_Inventory_Accrual_Account_id IS NULL)))
303 AND ( (Recinfo.expense_Accrual_account_id = X_Expense_Accrual_Account_id)
304 OR ( (Recinfo.expense_Accrual_account_id IS NULL)
305 AND (X_Expense_Accrual_Account_id IS NULL)))
306
307 ) then
308 return;
309 else
310 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
311 APP_EXCEPTION.Raise_Exception;
312 end if;
313 END IF;
314 END Lock_Row;
315
316
317
318 PROCEDURE Update_Row(X_Rowid VARCHAR2,
319 X_Ship_Organization_Id NUMBER,
320 X_Sell_Organization_Id NUMBER,
321 X_Last_Update_Date DATE,
322 X_Last_Updated_By NUMBER,
323 X_Last_Update_Login NUMBER,
324 X_Vendor_Id NUMBER,
325 X_Vendor_Site_Id NUMBER,
326 X_Customer_Id NUMBER,
327 X_Address_Id NUMBER,
328 X_Customer_Site_Id NUMBER,
329 X_Cust_Trx_Type_Id NUMBER,
330 X_Attribute_Category VARCHAR2,
331 X_Attribute1 VARCHAR2,
332 X_Attribute2 VARCHAR2,
333 X_Attribute3 VARCHAR2,
334 X_Attribute4 VARCHAR2,
335 X_Attribute5 VARCHAR2,
336 X_Attribute6 VARCHAR2,
337 X_Attribute7 VARCHAR2,
338 X_Attribute8 VARCHAR2,
339 X_Attribute9 VARCHAR2,
340 X_Attribute10 VARCHAR2,
341 X_Attribute11 VARCHAR2,
342 X_Attribute12 VARCHAR2,
343 X_Attribute13 VARCHAR2,
344 X_Attribute14 VARCHAR2,
345 X_Attribute15 VARCHAR2,
346 X_Revalue_Average_Flag VARCHAR2,
347 X_Freight_Code_Combination_Id NUMBER,
348 X_Inv_Currency_Code NUMBER,
349 X_Flow_Type NUMBER DEFAULT NULL, -- added as part of patchset-j development
350 X_Intercompany_COGS_Account_Id NUMBER DEFAULT NULL,
351 X_Inventory_Accrual_Account_Id NUMBER DEFAULT NULL,
352 X_Expense_Accrual_Account_Id NUMBER DEFAULT NULL
353 ) IS
354
355
356 BEGIN
357 UPDATE MTL_INTERCOMPANY_PARAMETERS
358 SET
359 last_update_date = X_Last_Update_Date,
360 last_updated_by = X_Last_Updated_By,
361 last_update_login = X_Last_Update_Login,
362 vendor_id = X_Vendor_Id,
363 vendor_site_id = X_Vendor_Site_Id,
364 customer_id = X_Customer_Id,
365 address_id = X_Address_Id,
366 customer_site_id = X_Customer_Site_Id,
367 cust_trx_type_id = X_Cust_Trx_Type_Id,
368 attribute_category = X_Attribute_Category,
369 attribute1 = X_Attribute1,
370 attribute2 = X_Attribute2,
371 attribute3 = X_Attribute3,
372 attribute4 = X_Attribute4,
373 attribute5 = X_Attribute5,
374 attribute6 = X_Attribute6,
375 attribute7 = X_Attribute7,
376 attribute8 = X_Attribute8,
377 attribute9 = X_Attribute9,
378 attribute10 = X_Attribute10,
379 attribute11 = X_Attribute11,
380 attribute12 = X_Attribute12,
381 attribute13 = X_Attribute13,
382 attribute14 = X_Attribute14,
383 attribute15 = X_Attribute15,
384 --revalue_average_flag = X_Revalue_Average_Flag, Bug 2745914
385 freight_code_combination_id = X_Freight_Code_Combination_Id,
386 inv_currency_code = X_Inv_Currency_Code
387 WHERE rowid = X_Rowid;
388
389 if (SQL%NOTFOUND) then
390 Raise NO_DATA_FOUND;
391 end if;
392
393 IF(inv_control.get_current_release_level >= inv_release.GET_J_RELEASE_LEVEL) Then
394 UPDATE MTL_INTERCOMPANY_PARAMETERS
395 SET
396 flow_type = X_Flow_Type,
397 intercompany_cogs_account_id = X_Intercompany_COGS_Account_Id,
398 inventory_Accrual_account_id = X_Inventory_Accrual_Account_Id,
399 expense_Accrual_account_id = X_Expense_Accrual_Account_Id
400 WHERE rowid = X_Rowid;
401 ELSE
402 UPDATE MTL_INTERCOMPANY_PARAMETERS
403 SET
404 flow_type = 1, -- Should be always 1 (Shipping) if INV-J is not installed. Bug# 3271622
405 intercompany_cogs_account_id = NULL,
406 inventory_Accrual_account_id = NULL,
407 expense_Accrual_account_id = NULL
408 WHERE rowid = X_Rowid;
409 if (SQL%NOTFOUND) then
410 Raise NO_DATA_FOUND;
411 end if;
412 END IF;
413 END Update_Row;
414
415
416
417 PROCEDURE Delete_Row(X_Ship_Organization_Id NUMBER,
418 X_Sell_Organization_Id NUMBER) IS
419 BEGIN
420 DELETE FROM MTL_INTERCOMPANY_PARAMETERS
421 WHERE ship_organization_id=X_Ship_Organization_Id
422 AND sell_organization_id=X_Sell_Organization_Id;
423
424 if (SQL%NOTFOUND) then
425 Raise NO_DATA_FOUND;
426 end if;
427 END Delete_Row;
428
429
430 END MTL_IC_PARAMETERS_PKG;