1 PACKAGE INV_PHY_INV_LOVS AUTHID CURRENT_USER AS
2 /* $Header: INVPINLS.pls 120.3.12020000.3 2013/01/21 11:36:27 ragudise ship $ */
3
4 TYPE t_genref IS REF CURSOR;
5
6 -- Name: GET_PHY_INV_LOV
7 --
8 -- Input parameters:
9 -- p_phy_inv Restricts LOV SQL to the user input text
10 -- p_organization_id Organization ID
11 --
12 -- Output parameters:
13 -- x_phy_inv_lov Returns LOV rows as a reference cursor
14 --
15 -- Functions: This API returns valid physical inventories
16 --
17
18 PROCEDURE get_phy_inv_lov
19 (x_phy_inv_lov OUT NOCOPY t_genref,
20 p_phy_inv IN VARCHAR2,
21 p_organization_id IN NUMBER);
22
23
24 -- Name: GET_SERIAL_COUNT_NUMBER
25 --
26 -- Input parameters:
27 -- p_physical_inventory_id Physical Inventory ID
28 -- p_organization_id Organization ID
29 -- p_serial_number Serial Number
30 -- p_inventory_item_id Inventory Item ID
31 --
32 -- Output parameters:
33 -- x_number Returns the serial count for the number
34 -- of physical tags with that particular
35 -- serial number that has already been counted.
36 -- x_serial_in_scope Returns 1 if the serial is within the scope
37 -- of the physical inventory. Otherwise it will
38 -- return 0.
39 --
40 -- Functions: This API returns the count of physical tag records
41 -- for the given serial number inputted.
42 -- It has also been overloaded so that it will also
43 -- check if the inputted serial is within the scope
44 -- of the physical inventory, i.e. exists in a subinventory
45 -- for which the physical inventory covers
46 --
47
48 PROCEDURE get_serial_count_number
49 (p_physical_inventory_id IN NUMBER ,
50 p_organization_id IN NUMBER ,
51 p_serial_number IN VARCHAR2 ,
52 p_inventory_item_id IN NUMBER ,
53 x_number OUT NOCOPY NUMBER ,
54 x_serial_in_scope OUT NOCOPY NUMBER);
55
56 PROCEDURE process_tag
57 (p_physical_inventory_id IN NUMBER,
58 p_organization_id IN NUMBER,
59 p_subinventory IN VARCHAR2,
60 p_locator_id IN NUMBER := NULL,
61 p_parent_lpn_id IN NUMBER := NULL,
62 p_inventory_item_id IN NUMBER,
63 p_revision IN VARCHAR2 := NULL,
64 p_lot_number IN VARCHAR2 := NULL,
65 p_from_serial_number IN VARCHAR2 := NULL,
66 p_to_serial_number IN VARCHAR2 := NULL,
67 p_tag_quantity IN NUMBER,
68 p_tag_uom IN VARCHAR2,
69 p_dynamic_tag_entry_flag IN NUMBER,
70 p_user_id IN NUMBER,
71 p_cost_group_id IN NUMBER := NULL
72 --INVCONV, NSRIVAST, START
73 ,p_tag_sec_uom IN VARCHAR2 := NULL
74 ,p_tag_sec_quantity IN NUMBER := NULL
75 --INVCONV, NSRIVAST, END
76 );
77
78 PROCEDURE insert_row
79 (p_physical_inventory_id IN NUMBER,
80 p_organization_id IN NUMBER,
81 p_subinventory IN VARCHAR2,
82 p_locator_id IN NUMBER,
83 p_parent_lpn_id IN NUMBER,
84 p_inventory_item_id IN NUMBER,
85 p_revision IN VARCHAR2,
86 p_lot_number IN VARCHAR2,
87 p_serial_number IN VARCHAR2,
88 p_tag_quantity IN NUMBER,
89 p_tag_uom IN VARCHAR2,
90 p_user_id IN NUMBER,
91 p_cost_group_id IN NUMBER,
92 p_adjustment_id IN NUMBER
93 --INVCONV, NSRIVAST, START
94 ,p_tag_sec_quantity IN NUMBER := NULL
95 ,p_tag_sec_uom IN VARCHAR2 := NULL
96 --INVCONV, NSRIVAST, END
97 );
98
99 PROCEDURE update_row
100 (p_tag_id IN NUMBER,
101 p_physical_inventory_id IN NUMBER,
102 p_organization_id IN NUMBER,
103 p_subinventory IN VARCHAR2,
104 p_locator_id IN NUMBER,
105 p_parent_lpn_id IN NUMBER,
106 p_inventory_item_id IN NUMBER,
107 p_revision IN VARCHAR2,
108 p_lot_number IN VARCHAR2,
109 p_serial_number IN VARCHAR2,
110 p_tag_quantity IN NUMBER,
111 p_tag_uom IN VARCHAR2,
112 p_user_id IN NUMBER,
113 p_cost_group_id IN NUMBER,
114 p_adjustment_id IN NUMBER
115 ,p_tag_sec_quantity IN NUMBER := NULL --INVCONV, NSRIVAST
116 );
117
118 PROCEDURE update_adjustment
119 (p_adjustment_id IN NUMBER,
120 p_physical_inventory_id IN NUMBER,
121 p_organization_id IN NUMBER,
122 p_user_id IN NUMBER
123 );
124
125 PROCEDURE find_existing_adjustment
126 (p_physical_inventory_id IN NUMBER,
127 p_organization_id IN NUMBER,
128 p_subinventory IN VARCHAR2,
129 p_locator_id IN NUMBER,
130 p_parent_lpn_id IN NUMBER,
131 p_inventory_item_id IN NUMBER,
132 p_revision IN VARCHAR2,
133 p_lot_number IN VARCHAR2,
134 p_serial_number IN VARCHAR2,
135 p_user_id IN NUMBER,
136 p_cost_group_id IN NUMBER,
137 x_adjustment_id OUT NOCOPY NUMBER
138 );
139
140 PROCEDURE process_summary
141 (p_physical_inventory_id IN NUMBER,
142 p_organization_id IN NUMBER,
143 p_subinventory IN VARCHAR2,
144 p_locator_id IN NUMBER := NULL,
145 p_parent_lpn_id IN NUMBER := NULL,
146 p_dynamic_tag_entry_flag IN NUMBER,
147 p_user_id IN NUMBER
148 );
149
150
151 --Fix for bug #4654210
152 PROCEDURE unmark_serials
153 (p_physical_inventory_id IN NUMBER,
154 p_organization_id IN NUMBER,
155 p_item_id IN NUMBER,
156 x_status OUT NOCOPY NUMBER
157 );
158
159 -- Fix for bug 5660272
160 PROCEDURE GET_SERIAL_NUMBER_TYPE
161 ( x_serial_number_type OUT NOCOPY NUMBER,
162 p_organization_id IN NUMBER
163 );
164
165 -- Fix for bug 5660272
166 PROCEDURE VALIDATE_SERIAL_STATUS
167 ( x_status OUT NOCOPY NUMBER,
168 x_organization_code OUT NOCOPY VARCHAR2,
169 x_current_status OUT NOCOPY VARCHAR2,
170 p_serial_num IN VARCHAR2,
171 p_organization_id IN NUMBER,
172 p_subinventory_code IN VARCHAR2,
173 p_locator_id IN NUMBER,
174 p_inventory_item_id IN NUMBER,
175 p_serial_number_type IN NUMBER
176 );
177
178 -- Changes for Phy Inv ER - bug 13865417 , starts here
179 PROCEDURE DELETE_DUPLICATE_ENTRIES(
180 p_physical_inventory_id IN NUMBER,
181 p_organization_id IN NUMBER,
182 p_parent_lpn_id IN NUMBER,
183 p_inventory_item_id IN NUMBER,
184 p_revision IN VARCHAR2,
185 p_lot_number IN VARCHAR2,
186 p_serial_number IN VARCHAR2,
187 p_adjustment_id IN NUMBER
188 );
189
190 PROCEDURE PROCESS_PHY_INV_SUBXFER(
191 p_organization_id IN NUMBER,
192 p_physical_inv_id IN NUMBER,
193 p_txn_header_id IN NUMBER,
194 p_gl_acct_id IN NUMBER,
195 p_request_id IN NUMBER,
196 p_program_app_id IN NUMBER,
197 p_program_id IN NUMBER,
198 x_proc_status OUT NOCOPY NUMBER,
199 x_record_count OUT NOCOPY NUMBER,
200 p_txn_date IN VARCHAR2 -- BUG 16188610
201 );
202 -- Changes for Phy Inv ER - bug 13865417 , ends here
203
204 END INV_PHY_INV_LOVS;