1 PACKAGE AHL_UC_UTILIZATION_PVT AUTHID CURRENT_USER AS
2 /* $Header: AHLVUCUS.pls 115.3 2004/01/27 03:03:03 jeli noship $ */
3
4 -------------------------------------------------
5 -- Define Record Type for Utilization Details. --
6 -------------------------------------------------
7 TYPE utilization_rec_type IS RECORD (
8 ORGANIZATION_CODE VARCHAR2(3),
9 ITEM_NUMBER VARCHAR2(2000),
10 ORGANIZATION_ID NUMBER,
11 INVENTORY_ITEM_ID NUMBER,
12 SERIAL_NUMBER VARCHAR2(30),
13 CSI_ITEM_INSTANCE_ID NUMBER,
14 CSI_ITEM_INSTANCE_NUMBER VARCHAR2(30),
15 UOM_CODE VARCHAR2(3),
16 RULE_CODE VARCHAR2(30),
17 RULE_MEANING VARCHAR2(80),
18 READING_VALUE NUMBER,
19 READING_DATE DATE,
20 COUNTER_NAME VARCHAR2(30),
21 COUNTER_ID NUMBER,
22 CASCADE_FLAG VARCHAR2(1) := 'N',
23 DELTA_FLAG VARCHAR2(1) := 'N'
24 );
25
26 --G_MISS_Utilization_Rec Utilization_Rec_Type;
27
28 ----------------------------------------------
29 -- Define Table Types for record structures --
30 ----------------------------------------------
31 TYPE utilization_tbl_type IS TABLE OF utilization_rec_type INDEX BY BINARY_INTEGER;
32
33 -----------------------------------------
34 -- Declare Procedure for Utilization --
35 -----------------------------------------
36
37 PROCEDURE Update_Utilization(p_api_version IN NUMBER,
38 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
39 p_commit IN VARCHAR2 := FND_API.G_FALSE,
40 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
41 p_Utilization_tbl IN AHL_UC_Utilization_PVT.utilization_tbl_type,
42 x_return_status OUT NOCOPY VARCHAR2,
43 x_msg_count OUT NOCOPY NUMBER,
44 x_msg_data OUT NOCOPY VARCHAR2);
45 -- Start of Comments --
46 -- Procedure name : Update_Utilization
47 -- Type : Private
48 -- Function : Updates the utilization based on the counter rules defined in the master configuration
49 -- given the details of an item/counter id/counter name/uom_code.
50 -- Casacades the updates down to all the children if the p_cascade_flag is set to 'Y'.
51 -- Pre-reqs :
52 -- Parameters :
53 -- Standard IN Parameters :
54 -- p_api_version IN NUMBER Required
55 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
56 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
57 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
58 --
59 -- Standard OUT Parameters :
60 -- x_return_status OUT VARCHAR2 Required
61 -- x_msg_count OUT NUMBER Required
62 -- x_msg_data OUT VARCHAR2 Required
63 --
64 -- Update_Utilization Parameters:
65 --
66 -- p_Utilization_tbl IN Required.
67 -- For each record, at any given time only one of the following combinations is valid to identify the
68 -- item instance to be updated:
69 -- 1. Organization id and Inventory_item_id AND Serial Number.
70 -- This information will identify the part number and serial number of a configuration.
71 -- 2. Counter ID -- if this is passed a specific counter ONLY will be updated irrespective of the value
72 -- of p_cascade_flag.
73 -- 3. CSI_ITEM_INSTANCE_ID -- if this is passed, then this item instance and items down the hierarchy (depends on
74 -- the value p_cascade_flag) will be updated.
75 -- At any given time only one of the following combinations is valid to identify the type of item counters to be
76 -- updated:
77 -- 1. COUNTER_ID
78 -- 2. COUNTER_NAME
79 -- 3. UOM_CODE
80 --
81 -- Reading_Value IN Required.
82 -- This will be the value of the counter reading.
83 --
84 -- cascade_flag -- Can take values Y and N. Y indicates that the counter updates will cascade down the hierarchy
85 -- beginning at the item number passed. Ift its value is N then only the item counter will be updated.
86 -- delta_flag -- Can take values Y and N. Y indicates that the counter reading values refer to the delta and the
87 -- N means the reading value is current value
88 --
89 -- NOTES
90 -- 1. p_reading_value can be delta reading or net_reading depending on delta_flag. And the delta reading
91 -- can either be positive(ascending counter) or negative(descending counter). The net_reading can be
92 -- greater or less than its current reading value.
93 -- 2. For parameters counter_id, counter_name and UOM_code, one and only one can be provided each time.
94 -- 3. If counter_id is provided, then just update the specific counter_id, p_cascade_flag doesn't apply
95 -- at all.
96 -- 4. If counter_name is provided, then if the counter doesn't exist for the start instance, and reading
97 -- value is net reading then raise error and stop, but if the reading_value is delta reading and cascade_flag = 'Y',
98 -- then get all of the highest component instances of the start instance which have the counter associated but all their
99 -- ancestors don't. Loop through all of these component instances and the specific counter.
100 -- 5. If UOM_code is provided, then if delta_flag='Y', then get all of the distinct counters which associated with the start
101 -- instance and all of its component instance, otherwise just get all of the distinct counters which associated with the
102 -- the start instance only. Then loop through these counters like the above step.
103 -- End of Comments --
104
105 END AHL_UC_Utilization_PVT;