[Home] [Help]
PACKAGE BODY: APPS.MTL_STAT_TYPE_USAGES_PKG
Source
1 PACKAGE BODY MTL_STAT_TYPE_USAGES_PKG AS
2 --$Header: INVGSTUB.pls 120.2.12000000.2 2007/04/17 06:26:13 nesoni ship $
3 --+=======================================================================+
4 --| Copyright (c) 1999, 2000 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| INVGSTUB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| Body of package MTL_STAT_TYPE_USAGES_PKG, table |
13 --| handler for table MTL_STAT_TYPE_USAGES |
14 --| |
15 --| PROCEDURE LIST |
16 --| Insert_Row |
17 --| Lock_Row |
18 --| Delete_Row |
19 --| Update_Row |
20 --| |
21 --| HISTORY |
22 --| 01/27/1999 Herman Poon Created |
23 --| 05/31/1999 Paolo Juvara Modified to reflect changes in |
24 --| MTL_STAT_TYPE_USAGES |
25 --| 06/15/2000 Paolo Juvara Added support for period_type |
26 --| 07/11/2000 Komal Saini Added 2 new columns for Rules Validation |
27 --| 09/19/2001 Yanping Wang Added support for triangulation_mode |
28 --| 01/11/2002 Yanping Wang Added support for reference period rule |
29 --| 3 new columns: reference_period_rule, |
30 --| pending_invoice_days,prior_invoice_days |
31 --| 11/26/02 Vivian Ma Added NOCOPY to IN OUT parameters to |
32 --| comply with new PL/SQL standard for |
33 --| better performance |
34 --| 09/16/2003 Yanping Wang Added support for returns_processing |
35 --| 03/08/2005 Yanping Wang Added support for kit |
36 --| 16/04/2007 Neelam Soni Bug 5920143. Added support for Include |
37 --| Establishments. |
38 --+=======================================================================+
39
40 --===================
41 -- CONSTANTS
42 --===================
43 G_PKG_NAME CONSTANT VARCHAR2(30) := 'MTL_STAT_TYPE_USAGES_PKG';
44 -- add your constants here if any
45
46 --===================
47 -- GLOBAL VARIABLES
48 --===================
49 -- add your private global variables here if any
50
51 --===================
52 -- PUBLIC PROCEDURES AND FUNCTIONS
53 --===================
54
55 --========================================================================
56 -- PROCEDURE : Insert_Row PUBLIC
57 -- PARAMETERS:
58 --
59 --
60 -- COMMENT :
61 -- backward compatibility:
62 -- the following columns have been added to the table after the initial
63 -- release (R11i)
64 -- - period_type
65 -- - attribute_rule_set_code 11/jul/00
66 -- - alt_uom_rule_set_code 11/jul/00
67 -- - include_establishments 06/mar/07 Bug:5920143.
68 -- to guarantee backward compatibility, this procedure has a default value
69 -- for the corresponding parameters
70 --
71 -- EXCEPTIONS:
72 --========================================================================
73 PROCEDURE Insert_Row
74 ( p_rowid IN OUT NOCOPY VARCHAR2
75 , p_legal_entity_id NUMBER
76 , p_zone_code VARCHAR2
77 , p_usage_type VARCHAR2
78 , p_stat_type VARCHAR2
79 , p_period_set_name VARCHAR2
80 , p_period_type VARCHAR2
81 , p_start_period_name VARCHAR2
82 , p_end_period_name VARCHAR2
83 , p_weight_uom_code VARCHAR2
84 , p_entity_branch_reference VARCHAR2
85 , p_conversion_type VARCHAR2
86 , p_conversion_option VARCHAR2
87 , p_category_set_id NUMBER
88 , p_tax_office_code VARCHAR2
89 , p_tax_office_name VARCHAR2
90 , p_tax_office_location_id NUMBER
91 , p_attribute_rule_set_code VARCHAR2
92 , p_alt_uom_rule_set_code VARCHAR2
93 , p_triangulation_mode VARCHAR2
94 , p_reference_period_rule VARCHAR2
95 , p_pending_invoice_days NUMBER
96 , p_prior_invoice_days NUMBER
97 , p_returns_processing VARCHAR2
98 , p_kit_method VARCHAR2
99 , p_weight_precision NUMBER
100 , p_reporting_rounding VARCHAR2
101 , p_include_establishments VARCHAR2
102 , p_last_update_date DATE
103 , p_last_updated_by NUMBER
104 , p_last_update_login NUMBER
105 , p_created_by NUMBER
106 , p_creation_date DATE
107 )
108 IS
109 l_period_type VARCHAR2(200);
110
111 CURSOR C IS
112 SELECT
113 rowid
114 FROM
115 MTL_STAT_TYPE_USAGES
116 WHERE Legal_Entity_ID = p_legal_entity_id
117 AND Zone_Code = p_zone_code
118 AND Usage_Type = p_usage_type
119 AND Stat_Type = p_stat_type;
120
121 BEGIN
122 IF p_period_type IS NULL
123 THEN
124 l_period_type := 'Month';
125 ELSE
126 l_period_type := p_period_type;
127 END IF;
128
129 -- Bug:5920143.
130 -- New column include_establishments has been added to insert clause.
131 INSERT INTO MTL_STAT_TYPE_USAGES(
132 Legal_Entity_ID
133 , Zone_Code
134 , Usage_Type
135 , Stat_Type
136 , Start_Period_Name
137 , Period_Type
138 , End_Period_Name
139 , Period_Set_Name
140 , Weight_UOM_Code
141 , Entity_Branch_Reference
142 , Conversion_Type
143 , Conversion_Option
144 , Category_Set_ID
145 , Tax_Office_Code
146 , Tax_Office_Name
147 , Tax_Office_Location_ID
148 , Attribute_Rule_Set_Code
149 , Alt_Uom_Rule_Set_Code
150 , Triangulation_Mode
151 , reference_period_rule
152 , pending_invoice_days
153 , prior_invoice_days
154 , returns_processing
155 , kit_method
156 , weight_precision
157 , reporting_rounding
158 , include_establishments
159 , Last_Update_Date
160 , Last_Updated_By
161 , Last_Update_Login
162 , Created_By
163 , Creation_Date
164 )
165 VALUES(
166 p_legal_entity_id
167 , p_zone_code
168 , p_usage_type
169 , p_stat_type
170 , p_start_period_name
171 , l_period_type
172 , p_end_period_name
173 , p_period_set_name
174 , p_weight_uom_code
175 , p_entity_branch_reference
176 , p_conversion_type
177 , p_conversion_option
178 , p_category_set_id
179 , p_tax_office_code
180 , p_tax_office_name
181 , p_tax_office_location_id
182 , p_attribute_rule_set_code
183 , p_alt_uom_rule_set_code
184 , p_triangulation_mode
185 , p_reference_period_rule
186 , p_pending_invoice_days
187 , p_prior_invoice_days
188 , p_returns_processing
189 , p_kit_method
190 , p_weight_precision
191 , p_reporting_rounding
192 , p_include_establishments
193 , p_last_update_date
194 , p_last_updated_by
195 , p_last_update_login
196 , p_created_by
197 , p_creation_date
198 );
199
200 OPEN C;
201 FETCH C INTO p_rowid;
202 IF (C%NOTFOUND) THEN
203 CLOSE C;
204 RAISE NO_DATA_FOUND;
205 END IF;
206 CLOSE C;
207
208 END Insert_Row;
209
210
211 --========================================================================
212 -- PROCEDURE : Lock_Row PUBLIC
213 -- PARAMETERS:
214 --
215 --
216 -- COMMENT :
217 -- backward compatibility:
218 -- the following columns have been added to the table after the initial
219 -- release (R11i)
220 -- - period_type
221 -- - attribute_rule_set_code 11/jul/00
222 -- - alt_uom_rule_set_code 11/jul/00
223 -- - include_establishments 06/mar/07 Bug:5920143.
224 -- to guarantee backward compatibility, this procedure has a default value
225 -- for the corresponding parameters
226 --
227 -- EXCEPTIONS:
228 --========================================================================
229 PROCEDURE Lock_Row
230 ( p_rowid IN OUT NOCOPY VARCHAR2
231 , p_legal_entity_id NUMBER
232 , p_zone_code VARCHAR2
233 , p_usage_type VARCHAR2
234 , p_stat_type VARCHAR2
235 , p_period_set_name VARCHAR2
236 , p_period_type VARCHAR2
237 , p_start_period_name VARCHAR2
238 , p_end_period_name VARCHAR2
239 , p_weight_uom_code VARCHAR2
240 , p_entity_branch_reference VARCHAR2
241 , p_conversion_type VARCHAR2
242 , p_conversion_option VARCHAR2
243 , p_category_set_id NUMBER
244 , p_tax_office_code VARCHAR2
245 , p_tax_office_name VARCHAR2
246 , p_tax_office_location_id NUMBER
247 , p_attribute_rule_set_code VARCHAR2
248 , p_alt_uom_rule_set_code VARCHAR2
249 , p_triangulation_mode VARCHAR2
250 , p_reference_period_rule VARCHAR2
251 , p_pending_invoice_days NUMBER
252 , p_prior_invoice_days NUMBER
253 , p_returns_processing VARCHAR2
254 , p_kit_method VARCHAR2
255 , p_weight_precision NUMBER
256 , p_reporting_rounding VARCHAR2
257 , p_include_establishments VARCHAR2
258 )
259 IS
260 l_period_type VARCHAR2(200);
261
262 CURSOR C IS
263 SELECT *
264 FROM MTL_STAT_TYPE_USAGES
265 WHERE ROWID = p_rowid
266 FOR UPDATE OF Legal_Entity_ID NOWAIT;
267 Recinfo C%ROWTYPE;
268
269 BEGIN
270 IF p_period_type IS NULL
271 THEN
272 l_period_type := 'Month';
273 ELSE
274 l_period_type := p_period_type;
275 END IF;
276
277 OPEN C;
278 FETCH C INTO Recinfo;
279 IF (C%NOTFOUND) THEN
280 CLOSE C;
281 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
282 APP_EXCEPTION.Raise_Exception;
283 END IF;
284 CLOSE C;
285
286 IF (
287 (Recinfo.Legal_Entity_ID = p_legal_entity_id)
288 AND
289 (Recinfo.zone_code = p_zone_code)
290 AND
291 (Recinfo.usage_type = p_usage_type)
292 AND
293 (Recinfo.stat_type = p_stat_type)
294 AND
295 (Recinfo.start_period_name = p_start_period_name)
296 AND
297 (
298 (Recinfo.end_period_name = p_end_period_name)
299 OR
300 (
301 (recinfo.end_period_name IS NULL)
302 AND
303 (p_end_period_name IS NULL)
304 )
305 )
306 AND
307 (Recinfo.period_set_name = p_period_set_name)
308 AND
309 (Recinfo.period_type = l_period_type)
310 AND
311 (Recinfo.weight_uom_code = p_weight_uom_code)
312 AND
313 ((Recinfo.entity_branch_reference = p_entity_branch_reference)
314 OR ((Recinfo.entity_branch_reference IS NULL)
315 AND (p_entity_branch_reference IS NULL))
316 )
317 AND
318 ((Recinfo.conversion_type = p_conversion_type)
319 OR (Recinfo.conversion_type IS NULL
320 AND p_conversion_type IS NULL))
321 AND
322 ((Recinfo.conversion_option = p_conversion_option)
323 OR (Recinfo.conversion_option IS NULL
324 AND p_conversion_option IS NULL))
325 AND
326 (Recinfo.category_set_id = p_category_set_id)
327 AND
328 ((Recinfo.tax_office_code = p_tax_office_code)
329 OR ((Recinfo.tax_office_code IS NULL)
330 AND (p_tax_office_code IS NULL))
331 )
332 AND
333 ((Recinfo.tax_office_name = p_tax_office_name)
334 OR ((Recinfo.tax_office_name IS NULL)
335 AND (p_tax_office_name IS NULL))
336 )
337 AND
338 (Recinfo.tax_office_location_id = p_tax_office_location_id)
339 AND
340 (
341 (Recinfo.attribute_rule_set_code = p_attribute_rule_set_code)
342 OR
343 (
344 (recinfo.attribute_rule_set_code IS NULL)
345 AND
346 (p_attribute_rule_set_code IS NULL)
347 )
348 )
349 AND
350 (
351 (Recinfo.alt_uom_rule_set_code = p_alt_uom_rule_set_code)
352 OR
353 (
354 (recinfo.alt_uom_rule_set_code IS NULL)
355 AND
356 (p_alt_uom_rule_set_code IS NULL)
357 )
358 )
359 AND ( (Recinfo.triangulation_mode = p_triangulation_mode)
360 OR ( (recinfo.triangulation_mode IS NULL) AND (p_triangulation_mode IS NULL)))
361 AND ( (Recinfo.reference_period_rule = p_reference_period_rule)
362 OR ( (recinfo.reference_period_rule IS NULL) AND (p_reference_period_rule IS NULL)))
363 AND ( (Recinfo.pending_invoice_days = p_pending_invoice_days)
364 OR ( (recinfo.pending_invoice_days IS NULL) AND (p_pending_invoice_days IS NULL)))
365 AND ( (Recinfo.prior_invoice_days = p_prior_invoice_days)
366 OR ( (recinfo.prior_invoice_days IS NULL) AND (p_prior_invoice_days IS NULL)))
367 AND ( (Recinfo.returns_processing = p_returns_processing)
368 OR ( (recinfo.returns_processing IS NULL) AND (p_returns_processing IS NULL)))
369 AND ( (Recinfo.kit_method = p_kit_method)
370 OR ( (recinfo.kit_method IS NULL) AND (p_kit_method IS NULL)))
371 AND ( (Recinfo.weight_precision = p_weight_precision)
372 OR ( (recinfo.weight_precision IS NULL) AND (p_weight_precision IS NULL)))
373 AND ( (Recinfo.reporting_rounding = p_reporting_rounding)
374 OR ( (recinfo.reporting_rounding IS NULL) AND (p_reporting_rounding IS NULL)))
375 AND ( (Recinfo.include_establishments = p_include_establishments)
376 OR ( (recinfo.include_establishments IS NULL) AND (p_include_establishments IS NULL)))
377 )
378 THEN
379 RETURN;
380 ELSE
381 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
382 APP_EXCEPTION.Raise_Exception;
383 END IF;
384
385 END Lock_Row;
386
387
388 --========================================================================
389 -- PROCEDURE : Update_Row PUBLIC
390 -- PARAMETERS:
391 --
392 --
393 -- COMMENT :
394 -- backward compatibility:
395 -- the following columns have been added to the table after the initial
396 -- release (R11i)
397 -- - period_type
398 -- - attribute_rule_set_code 11/jul/00
399 -- - alt_uom_rule_set_code 11/jul/00
400 -- - include_establishments 06/mar/07 Bug:5920143.
401 -- to guarantee backward compatibility, this procedure has a default value
402 -- for the corresponding parameters
403 --
404 -- EXCEPTIONS:
405 --========================================================================
406 PROCEDURE Update_Row
407 ( p_rowid IN OUT NOCOPY VARCHAR2
408 , p_legal_entity_id NUMBER
409 , p_zone_code VARCHAR2
410 , p_usage_type VARCHAR2
411 , p_stat_type VARCHAR2
412 , p_period_set_name VARCHAR2
413 , p_period_type VARCHAR2
414 , p_start_period_name VARCHAR2
415 , p_end_period_name VARCHAR2
416 , p_weight_uom_code VARCHAR2
417 , p_entity_branch_reference VARCHAR2
418 , p_conversion_type VARCHAR2
419 , p_conversion_option VARCHAR2
420 , p_category_set_id NUMBER
421 , p_tax_office_code VARCHAR2
422 , p_tax_office_name VARCHAR2
423 , p_tax_office_location_id NUMBER
424 , p_attribute_rule_set_code VARCHAR2
425 , p_alt_uom_rule_set_code VARCHAR2
426 , p_triangulation_mode VARCHAR2
427 , p_reference_period_rule VARCHAR2
428 , p_pending_invoice_days NUMBER
429 , p_prior_invoice_days NUMBER
430 , p_returns_processing VARCHAR2
431 , p_kit_method VARCHAR2
432 , p_weight_precision NUMBER
433 , p_reporting_rounding VARCHAR2
434 , p_include_establishments VARCHAR2
435 , p_last_update_date DATE
436 , p_last_updated_by NUMBER
437 , p_last_update_login NUMBER
438 , p_created_by NUMBER
439 , p_creation_date DATE
440 )
441 IS
442 l_period_type VARCHAR2(200);
443 BEGIN
444 IF p_period_type IS NULL
445 THEN
446 l_period_type := 'Month';
447 ELSE
448 l_period_type := p_period_type;
449 END IF;
450
451 -- Bug:5920143.
452 -- New column include_establishments has been added to update clause.
453 UPDATE MTL_STAT_TYPE_USAGES
454 SET
455 Legal_Entity_ID = p_legal_entity_id
456 , zone_code = p_zone_code
457 , usage_type = p_usage_type
458 , stat_type = p_stat_type
459 , start_period_name = p_start_period_name
460 , end_period_name = p_end_period_name
461 , period_set_name = p_period_set_name
462 , period_type = l_period_type
463 , weight_uom_code = p_weight_uom_code
464 , entity_branch_reference = p_entity_branch_reference
465 , conversion_type = p_conversion_type
466 , conversion_option = p_conversion_option
467 , category_set_id = p_category_set_id
468 , tax_office_code = p_tax_office_code
469 , tax_office_name = p_tax_office_name
470 , tax_office_location_id = p_tax_office_location_id
471 , attribute_rule_set_code = p_attribute_rule_set_code
472 , alt_uom_rule_set_code = p_alt_uom_rule_set_code
473 , triangulation_mode = p_triangulation_mode
474 , reference_period_rule = p_reference_period_rule
475 , pending_invoice_days = p_pending_invoice_days
476 , prior_invoice_days = p_prior_invoice_days
477 , returns_processing = p_returns_processing
478 , kit_method = p_kit_method
479 , weight_precision = p_weight_precision
480 , reporting_rounding = p_reporting_rounding
481 , include_establishments = p_include_establishments
482 , last_update_date = p_last_update_date
483 , last_updated_by = p_last_updated_by
484 , last_update_login = p_last_update_login
485 , created_by = p_created_by
486 , creation_date = p_creation_date
487 WHERE ROWID = p_rowid;
488
489 IF (SQL%NOTFOUND) THEN
490 RAISE NO_DATA_FOUND;
491 END IF;
492
493 END Update_Row;
494
495
496 --========================================================================
497 -- PROCEDURE : Delete_Row PUBLIC
498 -- PARAMETERS:
499 --
500 --
501 -- COMMENT :
502 --
503 -- EXCEPTIONS:
504 --========================================================================
505 PROCEDURE Delete_Row
506 ( p_rowid IN OUT NOCOPY VARCHAR2
507 )
508 IS
509 BEGIN
510
511 DELETE FROM MTL_STAT_TYPE_USAGES
512 WHERE ROWID = p_rowid;
513
514 IF (SQL%NOTFOUND) THEN
515 RAISE NO_DATA_FOUND;
516 END IF;
517
518 END Delete_Row;
519
520 END MTL_STAT_TYPE_USAGES_PKG;