1 PACKAGE INV_QUANTITY_TREE_UE AUTHID CURRENT_USER AS
2 /* $Header: INVQTUES.pls 120.7.12020000.3 2012/11/01 12:07:52 ksivasa ship $*/
3
4
5 g_lot_control NUMBER := 2;
6 g_no_lot_control NUMBER := 1;
7
8 g_serial_control NUMBER := 2;
9 g_no_serial_control NUMBER := 1;
10
11 g_rev_control NUMBER := 2;
12 g_no_rev_control NUMBER := 1;
13
14 g_no_rev_ctrl_please NUMBER := 0;
15 g_want_rev_ctrl NUMBER := 1;
16 g_refer_rev_control NUMBER := 2;
17
18 g_no_lot_ctrl_please NUMBER := 0;
19 g_want_lot_ctrl NUMBER := 1;
20 g_refer_lot_control NUMBER := 2;
21
22 g_all_subinvs NUMBER := 0;
23 g_asset_subinvs NUMBER := 1;
24
25
26 g_ONHAND NUMBER := 1; /* Select from MTL_ONHAND_QUANTITES */
27 g_TRX_TEMP NUMBER := 2; /* MTL_MATERIAL_TRANSACTIONS_TEMP */
28 g_RESERVATION NUMBER := 3; /* From MTL_DEMAND */
29 g_qs_txn NUMBER := 5; /*Suggestion in MMTT */
30
31 -- bug 4104123 : replaced p_demand_header_type default NULL by 0
32 FUNCTION create_tree(p_organization_id IN NUMBER,
33 p_inventory_item_id IN NUMBER,
34 p_revision_control IN NUMBER DEFAULT 1,
35 p_lot_control IN NUMBER DEFAULT 1,
36 p_serial_control IN NUMBER DEFAULT 1,
37 p_lot_active IN NUMBER DEFAULT 2,
38 p_demand_header_id IN NUMBER DEFAULT NULL,
39 p_demand_header_type IN NUMBER,
40 p_tree_mode In NUMBER DEFAULT 3, --2 replaced by 3 for bug7038890
41 p_negative_inv_allowed in NUMBER DEFAULT 0,
42 p_lot_expiration_date in DATE DEFAULT NULL,
43 p_activate IN NUMBER DEFAULT 1,
44 p_uom_code IN VARCHAR2 DEFAULT NULL,
45 p_asset_subinventory_only IN NUMBER DEFAULT 0,
46 p_demand_source_name IN VARCHAR2 DEFAULT NULL,
47 p_demand_source_line_id IN NUMBER DEFAULT NULL,
48 p_demand_source_delivery IN NUMBER DEFAULT NULL,
49 p_rev_active IN NUMBER DEFAULT 2,
50 x_available_quantity OUT NOCOPY NUMBER,
51 x_onhand_quantity OUT NOCOPY NUMBER,
52 x_return_status OUT NOCOPY VARCHAR2,
53 x_message_count OUT NOCOPY NUMBER,
54 x_message_data OUT NOCOPY VARCHAR2,
55 p_lpn_id IN NUMBER DEFAULT NULL, --added for bug7038890
56 p_wms_task_type IN NUMBER DEFAULT NULL) --added for bug12831673
57 RETURN NUMBER;
58
59 -- invConv change begin : Overloaded version of create_tree :
60 -- bug 4104123 : replaced p_demand_header_type default NULL by 0
61 FUNCTION create_tree( p_organization_id IN NUMBER
62 , p_inventory_item_id IN NUMBER
63 , p_revision_control IN NUMBER DEFAULT 1
64 , p_lot_control IN NUMBER DEFAULT 1
65 , p_serial_control IN NUMBER DEFAULT 1
66 , p_grade_code IN VARCHAR2 DEFAULT NULL -- invConv change
67 , p_lot_active IN NUMBER DEFAULT 2
68 , p_demand_header_id IN NUMBER DEFAULT NULL
69 , p_demand_header_type IN NUMBER DEFAULT 0
70 , p_tree_mode IN NUMBER DEFAULT 3 --2 replaced by 3 for bug7038890
71 , p_negative_inv_allowed IN NUMBER DEFAULT 0
72 , p_lot_expiration_date IN DATE DEFAULT NULL
73 , p_activate IN NUMBER DEFAULT 1
74 , p_uom_code IN VARCHAR2 DEFAULT NULL
75 , p_asset_subinventory_only IN NUMBER DEFAULT 0
76 , p_demand_source_name IN VARCHAR2 DEFAULT NULL
77 , p_demand_source_line_id IN NUMBER DEFAULT NULL
78 , p_demand_source_delivery IN NUMBER DEFAULT NULL
79 , p_rev_active IN NUMBER DEFAULT 2
80 , x_available_quantity OUT NOCOPY NUMBER
81 , x_available_quantity2 OUT NOCOPY NUMBER -- invConv change
82 , x_onhand_quantity OUT NOCOPY NUMBER
83 , x_onhand_quantity2 OUT NOCOPY NUMBER -- invConv change
84 , x_return_status OUT NOCOPY VARCHAR2
85 , x_message_count OUT NOCOPY NUMBER
86 , x_message_data OUT NOCOPY VARCHAR2
87 , p_lpn_id IN NUMBER DEFAULT NULL --added for bug7038890
88 , p_wms_task_type IN NUMBER DEFAULT NULL) --added for bug12831673
89 RETURN NUMBER;
90 -- invConv changes end.
91
92 -- bug 4104123 : replaced p_demand_header_type default NULL by 0
93 FUNCTION query_tree(p_organization_id IN NUMBER,
94 p_inventory_item_id IN NUMBER,
95 p_revision_control IN NUMBER DEFAULT 1,
96 p_lot_control IN NUMBER DEFAULT 1,
97 p_serial_control IN NUMBER DEFAULT 1,
98 p_demand_header_id IN NUMBER default NULL,
99 p_demand_header_type IN NUMBER,
100 p_revision in varchar2 default NULL,
101 p_lot in varchar2 default NULL,
102 P_lot_expiration_date IN DATE default NULL,
103 P_subinventory IN varchar2 default NULL,
104 P_locator in NUMBER default NULL,
105 P_transfer_subinventory VARCHAR2 default NULL,
106 P_transaction_quantity in NUMBER default 0,
107 P_uom_code in varchar2 default NULL,
108 P_lot_active IN NUMBER default 2,
109 P_activate IN NUMBER default 1,
110 P_tree_mode In NUMBER Default 3, --2 replaced by 3 for bug7038890
111 P_demand_source_name IN varchar2 default NULL,
112 P_demand_source_line_id IN NUMBER default NULL,
113 P_demand_source_delivery in NUMBER default NULL,
114 P_rev_active in NUMBER default 2,
115 X_available_onhand out NOCOPY NUMBER,
116 X_available_quantity out NOCOPY NUMBER,
117 X_onhand_quantity out NOCOPY NUMBER,
118 X_return_status OUT NOCOPY VARCHAR2,
119 X_message_count OUT NOCOPY NUMBER,
120 X_message_data Out NOCOPY VARCHAR2,
121 P_lpn_id IN NUMBER DEFAULT NULL , --added for bug7038890
122 P_wms_task_type IN NUMBER DEFAULT NULL ,--added for bug12831673
123 P_transfer_locator IN VARCHAR2 DEFAULT NULL -- Bug 14516283
124 ) RETURN NUMBER;
125
126 -- invConv changes begin : overloaded version of query_tree :
127 -- bug 4104123 : replaced p_demand_header_type default NULL by 0
128 FUNCTION query_tree( p_organization_id IN NUMBER
129 , p_inventory_item_id IN NUMBER
130 , p_revision_control IN NUMBER DEFAULT 1
131 , p_lot_control IN NUMBER DEFAULT 1
132 , p_serial_control IN NUMBER DEFAULT 1
133 , p_demand_header_id IN NUMBER DEFAULT NULL
134 , p_demand_header_type IN NUMBER DEFAULT 0
135 , p_revision in VARCHAR2 DEFAULT NULL
136 , p_lot in VARCHAR2 DEFAULT NULL
137 , p_lot_expiration_date IN DATE DEFAULT NULL
138 , p_subinventory IN VARCHAR2 DEFAULT NULL
139 , p_locator in NUMBER DEFAULT NULL
140 , p_transfer_subinventory IN VARCHAR2 DEFAULT NULL
141 , p_transaction_quantity IN NUMBER DEFAULT 0
142 , p_uom_code IN VARCHAR2 DEFAULT NULL
143 , p_transaction_quantity2 IN NUMBER DEFAULT NULL -- invConv change
144 , p_lot_active IN NUMBER DEFAULT 2
145 , p_activate IN NUMBER DEFAULT 1
146 , p_tree_mode IN NUMBER DEFAULT 3 --2 replaced by 3 for bug7038890
147 , p_demand_source_name IN VARCHAR2 DEFAULT NULL
148 , p_demand_source_line_id IN NUMBER DEFAULT NULL
149 , p_demand_source_delivery IN NUMBER DEFAULT NULL
150 , p_rev_active IN NUMBER DEFAULT 2
151 , x_available_onhand OUT NOCOPY NUMBER
152 , x_available_quantity OUT NOCOPY NUMBER
153 , x_onhand_quantity OUT NOCOPY NUMBER
154 , x_available_onhand2 OUT NOCOPY NUMBER -- invConv change
155 , x_available_quantity2 OUT NOCOPY NUMBER -- invConv change
156 , x_onhand_quantity2 OUT NOCOPY NUMBER -- invConv change
157 , x_return_status OUT NOCOPY VARCHAR2
158 , x_message_count OUT NOCOPY NUMBER
159 , x_message_data OUT NOCOPY VARCHAR2
160 , P_lpn_id IN NUMBER DEFAULT NULL --added for bug7038890
161 , P_wms_task_type IN NUMBER DEFAULT NULL -- 12831673
162 , P_transfer_locator IN VARCHAR2 DEFAULT NULL -- Bug 14516283
163 ) RETURN NUMBER;
164 -- invConv changes end.
165
166 -- bug 4104123 : replaced p_demand_header_type default NULL by 0
167 FUNCTION xact_qty(P_organization_id IN NUMBER,
168 P_inventory_item_id IN NUMBER,
169 P_demand_header_id IN NUMBER default NULL,
170 P_demand_header_type IN NUMBER,
171 P_revision_control IN NUMBER default 1,
172 P_lot_control IN NUMBER default 1,
173 P_serial_control IN NUMBER default 1,
174 P_revision in varchar2 default NULL,
175 P_lot in varchar2 default NULL,
176 P_lot_expiration_date IN DATE default NULL,
177 P_subinventory IN varchar2 default NULL,
178 P_locator in NUMBER default NULL,
179 P_xact_mode In NUMBER Default 2,
180 P_transfer_subinventory IN VARCHAR2 default NULL,
181 P_transfer_locator in NUMBER default NULL,
182 P_transaction_quantity in NUMBER default NULL,
183 P_uom_code in varchar2 default NULL,
184 P_lot_active IN NUMBER default 2,
185 P_activate IN NUMBER default 1,
186 P_demand_source_name IN varchar2 default NULL,
187 P_demand_source_line_id IN NUMBER default NULL,
188 P_demand_source_delivery in NUMBER default NULL,
189 P_rev_active in NUMBER default 2,
190 X_available_onhand out NOCOPY NUMBER,
191 X_available_quantity out NOCOPY NUMBER,
192 X_onhand_quantity out NOCOPY NUMBER,
193 X_return_status OUT NOCOPY VARCHAR2,
194 X_message_count OUT NOCOPY NUMBER,
195 X_message_data Out NOCOPY VARCHAR2,
196 P_tree_mode IN NUMBER DEFAULT 3, --added for bug7038890
197 P_lpn_id IN NUMBER DEFAULT NULL, --added for bug7038890
198 P_wms_task_type IN NUMBER DEFAULT NULL -- 12831673
199 ) RETURN NUMBER;
200
201 -- invConv changes begin : overloaded version of xact_qty
202 -- bug 4104123 : replaced p_demand_header_type default NULL by 0
203 FUNCTION xact_qty( P_organization_id IN NUMBER
204 , P_inventory_item_id IN NUMBER
205 , P_demand_header_id IN NUMBER DEFAULT NULL
206 , P_demand_header_type IN NUMBER DEFAULT 0
207 , P_revision_control IN NUMBER DEFAULT 1
208 , P_lot_control IN NUMBER DEFAULT 1
209 , P_serial_control IN NUMBER DEFAULT 1
210 , P_revision IN VARCHAR2 DEFAULT NULL
211 , P_lot IN VARCHAR2 DEFAULT NULL
212 , P_lot_expiration_date IN DATE DEFAULT NULL
213 , P_subinventory IN VARCHAR2 DEFAULT NULL
214 , P_locator IN NUMBER DEFAULT NULL
215 , P_xact_mode IN NUMBER DEFAULT 2
216 , P_transfer_subinventory IN VARCHAR2 DEFAULT NULL
217 , P_transfer_locator IN NUMBER DEFAULT NULL
218 , P_transaction_quantity IN NUMBER DEFAULT NULL
219 , P_uom_code IN VARCHAR2 DEFAULT NULL
220 , P_transaction_quantity2 IN NUMBER DEFAULT NULL
221 , P_lot_active IN NUMBER DEFAULT 2
222 , P_activate IN NUMBER DEFAULT 1
223 , P_demand_source_name IN VARCHAR2 DEFAULT NULL
224 , P_demand_source_line_id IN NUMBER DEFAULT NULL
225 , P_demand_source_delivery IN NUMBER DEFAULT NULL
226 , P_rev_active IN NUMBER DEFAULT 2
227 , X_available_onhand OUT NOCOPY NUMBER
228 , X_available_quantity OUT NOCOPY NUMBER
229 , X_onhand_quantity OUT NOCOPY NUMBER
230 , X_available_onhand2 OUT NOCOPY NUMBER
231 , X_available_quantity2 OUT NOCOPY NUMBER
232 , X_onhand_quantity2 OUT NOCOPY NUMBER
233 , X_return_status OUT NOCOPY VARCHAR2
234 , X_message_count OUT NOCOPY NUMBER
235 , X_message_data OUT NOCOPY VARCHAR2
236 , P_tree_mode IN NUMBER DEFAULT 3 --added for bug7038890
237 , P_lpn_id IN NUMBER DEFAULT NULL --added for bug7038890
238 , P_wms_task_type IN NUMBER DEFAULT NULL) --added for bug12831673
239 RETURN NUMBER;
240 -- invConv changes end.
241
242 PROCEDURE print_debug(p_message IN VARCHAR2, p_level IN NUMBER DEFAULT 14);
243
244 END INV_QUANTITY_TREE_UE;
[Home] [Help]