1 PACKAGE INV_CALCULATE_EXP_DATE AUTHID CURRENT_USER AS
2 /* $Header: INVCEDTS.pls 120.1.12020000.2 2012/07/09 08:04:11 asugandh ship $ */
3
4 g_mti_txn_id NUMBER := -1; -- stores the transaction_interface_id to identify row in MTI
5 g_mmtt_txn_id NUMBER := -1; -- stores the transaction_header_id to identify row in MMTT
6 g_mtli_txn_id ROWID := '-1'; -- stores the rowid to identify row in MTLI
7 g_mtlt_txn_id ROWID := '-1'; -- stores the rowid to identify row in MTLT
8
9 TYPE mmtt_tab IS TABLE OF MTL_MATERIAL_TRANSACTIONS_TEMP%ROWTYPE
10 INDEX BY BINARY_INTEGER;
11 g_mmtt_tbl mmtt_tab;
12
13 TYPE mtlt_tab IS TABLE OF MTL_TRANSACTION_LOTS_TEMP%ROWTYPE
14 INDEX BY BINARY_INTEGER;
15 g_mtlt_tbl mtlt_tab;
16
17 TYPE mti_tab IS TABLE OF MTL_TRANSACTIONS_INTERFACE%ROWTYPE
18 INDEX BY BINARY_INTEGER;
19 g_mti_tbl mti_tab;
20
21 TYPE mtli_tab IS TABLE OF MTL_TRANSACTION_LOTS_INTERFACE%ROWTYPE
22 INDEX BY BINARY_INTEGER;
23 g_mtli_tbl mtli_tab;
24
25 PROCEDURE assign_mti_rec (
26 p_inventory_item_id IN NUMBER
27 , p_revision IN VARCHAR2
28 , p_organization_id IN NUMBER
29 , p_transaction_action_id IN NUMBER
30 , p_subinventory_code IN VARCHAR2
31 , p_locator_id IN NUMBER
32 , p_transaction_type_id IN NUMBER
33 , p_trx_source_type_id IN NUMBER
34 , p_transaction_quantity IN NUMBER
35 , p_primary_quantity IN NUMBER
36 , p_transaction_uom IN VARCHAR2
37 , p_ship_to_location IN NUMBER
38 , p_reason_id IN NUMBER
39 , p_user_id IN NUMBER
40 , p_transfer_lpn_id IN NUMBER
41 , p_transaction_source_id IN NUMBER
42 , p_trx_source_line_id IN NUMBER
43 , p_project_id IN NUMBER
44 , p_task_id IN NUMBER
45 , p_planning_organization_id IN NUMBER
46 , p_planning_tp_type IN NUMBER
47 , p_owning_organization_id IN NUMBER
48 , p_owning_tp_type IN NUMBER
49 , p_distribution_account_id IN NUMBER
50 , p_sec_transaction_quantity IN NUMBER
51 , p_secondary_uom_code IN VARCHAR2
52 , x_return_status OUT NOCOPY VARCHAR2
53 );
54
55 FUNCTION get_mti_tbl RETURN mti_tab;
56
57 PROCEDURE purge_mti_tab;
58
59 PROCEDURE assign_mmtt_rec (
60 p_inventory_item_id IN NUMBER
61 , p_revision IN VARCHAR2
62 , p_organization_id IN NUMBER
63 , p_transaction_action_id IN NUMBER
64 , p_subinventory_code IN VARCHAR2
65 , p_locator_id IN NUMBER
66 , p_transaction_type_id IN NUMBER
67 , p_trx_source_type_id IN NUMBER
68 , p_transaction_quantity IN NUMBER
69 , p_primary_quantity IN NUMBER
70 , p_transaction_uom IN VARCHAR2
71 , p_ship_to_location IN NUMBER
72 , p_reason_id IN NUMBER
73 , p_user_id IN NUMBER
74 , p_transfer_lpn_id IN NUMBER
75 , p_transaction_source_id IN NUMBER
76 , p_transaction_cost IN NUMBER
77 , p_project_id IN NUMBER
78 , p_task_id IN NUMBER
79 , p_planning_organization_id IN NUMBER
80 , p_planning_tp_type IN NUMBER
81 , p_owning_organization_id IN NUMBER
82 , p_owning_tp_type IN NUMBER
83 , p_distribution_account_id IN NUMBER
84 , p_sec_transaction_quantity IN NUMBER
85 , p_secondary_uom_code IN VARCHAR2
86 , x_return_status OUT NOCOPY VARCHAR2
87 );
88
89 FUNCTION get_mmtt_tbl RETURN mmtt_tab;
90
91 PROCEDURE purge_mmtt_tab;
92
93 FUNCTION get_txn_id ( p_table IN NUMBER) RETURN NUMBER;
94
95 FUNCTION get_lot_txn_id ( p_table IN NUMBER) RETURN ROWID;
96
97 PROCEDURE set_txn_id ( p_table IN NUMBER,
98 p_header_id IN NUMBER) ;
99
100 PROCEDURE set_lot_txn_id ( p_table IN NUMBER,
101 p_header_id IN ROWID);
102
103 PROCEDURE reset_header_id;
104
105 PROCEDURE get_lot_primary_onhand
106 ( p_inventory_item_id IN NUMBER
107 ,p_organization_id IN NUMBER
108 ,p_lot_number IN VARCHAR2
109 ,x_onhand OUT NOCOPY NUMBER
110 ,x_return_status OUT NOCOPY VARCHAR2
111 ,x_msg_count OUT NOCOPY NUMBER
112 ,x_msg_data OUT NOCOPY VARCHAR2
113 ) ;
114
115 PROCEDURE get_origination_date
116 ( p_inventory_item_id IN NUMBER
117 ,p_organization_id IN NUMBER
118 ,p_lot_number IN VARCHAR2
119 ,x_orig_date OUT NOCOPY DATE
120 ,x_return_status OUT NOCOPY VARCHAR2
121 ) ;
122
123 --Bug 13992231,Get origination_date from session level.
124 PROCEDURE get_origination_date_session
125 ( p_inventory_item_id IN NUMBER
126 ,p_organization_id IN NUMBER
127 ,p_lot_number IN VARCHAR2
128 ,x_orig_date OUT NOCOPY DATE
129 ,x_return_status OUT NOCOPY VARCHAR2
130 ) ;
131
132 -- bug#6073680 Added this procedure.
133 PROCEDURE check_lot_exists
134 ( p_inventory_item_id IN NUMBER
135 ,p_organization_id IN NUMBER
136 ,p_lot_number IN VARCHAR2
137 ,x_lot_exist OUT NOCOPY VARCHAR2
138 ,x_return_status OUT NOCOPY VARCHAR2
139 ) ;
140
141 /*
142 PROCEDURE update_inv_lot_attr (
143 p_inventory_item_id IN NUMBER
144 , p_organization_id IN NUMBER
145 , p_lot_number IN VARCHAR2
146 , p_expiration_date IN DATE
147 , p_grade_code IN VARCHAR2
148 , p_status_id IN NUMBER
149 , p_origination_type IN NUMBER
150 , p_origination_date IN DATE
151 , p_retest_date IN DATE
152 , p_exp_action_dt IN DATE
153 , p_exp_action_code IN VARCHAR2
154 , p_hold_date IN DATE
155 , p_maturity_date IN DATE
156 , p_vendor_lot_num IN VARCHAR2
157 , x_return_status OUT NOCOPY VARCHAR2
158 ) ;
159 */
160 PROCEDURE update_inv_lot_attr(
161 x_return_status OUT NOCOPY VARCHAR2
162 , x_msg_count OUT NOCOPY NUMBER
163 , x_msg_data OUT NOCOPY VARCHAR2
164 , p_inventory_item_id IN NUMBER
165 , p_organization_id IN NUMBER
166 , p_lot_number IN VARCHAR2
167 , p_source IN NUMBER
168 , p_expiration_date IN DATE DEFAULT NULL
169 , p_grade_code IN VARCHAR2 DEFAULT NULL
170 , p_origination_date IN DATE DEFAULT NULL
171 , p_origination_type IN NUMBER DEFAULT NULL
172 , p_status_id IN NUMBER DEFAULT NULL
173 , p_retest_date IN DATE DEFAULT NULL
174 , p_maturity_date IN DATE DEFAULT NULL
175 , p_supplier_lot_number IN VARCHAR2 DEFAULT NULL
176 , p_expiration_action_code IN VARCHAR2 DEFAULT NULL
177 , p_expiration_action_date IN DATE DEFAULT NULL
178 , p_hold_date IN DATE DEFAULT NULL
179 , p_c_attribute1 IN VARCHAR2 := NULL
180 , p_c_attribute2 IN VARCHAR2 := NULL
181 , p_c_attribute3 IN VARCHAR2 := NULL
182 , p_c_attribute4 IN VARCHAR2 := NULL
183 , p_c_attribute5 IN VARCHAR2 := NULL
184 , p_c_attribute6 IN VARCHAR2 := NULL
185 , p_c_attribute7 IN VARCHAR2 := NULL
186 , p_c_attribute8 IN VARCHAR2 := NULL
187 , p_c_attribute9 IN VARCHAR2 := NULL
188 , p_c_attribute10 IN VARCHAR2 := NULL
189 , p_c_attribute11 IN VARCHAR2 := NULL
190 , p_c_attribute12 IN VARCHAR2 := NULL
191 , p_c_attribute13 IN VARCHAR2 := NULL
192 , p_c_attribute14 IN VARCHAR2 := NULL
193 , p_c_attribute15 IN VARCHAR2 := NULL
194 , p_c_attribute16 IN VARCHAR2 := NULL
195 , p_c_attribute17 IN VARCHAR2 := NULL
196 , p_c_attribute18 IN VARCHAR2 := NULL
197 , p_c_attribute19 IN VARCHAR2 := NULL
198 , p_c_attribute20 IN VARCHAR2 := NULL
199 , p_d_attribute1 IN DATE := NULL
200 , p_d_attribute2 IN DATE := NULL
201 , p_d_attribute3 IN DATE := NULL
202 , p_d_attribute4 IN DATE := NULL
203 , p_d_attribute5 IN DATE := NULL
204 , p_d_attribute6 IN DATE := NULL
205 , p_d_attribute7 IN DATE := NULL
206 , p_d_attribute8 IN DATE := NULL
207 , p_d_attribute9 IN DATE := NULL
208 , p_d_attribute10 IN DATE := NULL
209 , p_n_attribute1 IN NUMBER := NULL
210 , p_n_attribute2 IN NUMBER := NULL
211 , p_n_attribute3 IN NUMBER := NULL
212 , p_n_attribute4 IN NUMBER := NULL
213 , p_n_attribute5 IN NUMBER := NULL
214 , p_n_attribute6 IN NUMBER := NULL
215 , p_n_attribute7 IN NUMBER := NULL
216 , p_n_attribute8 IN NUMBER := NULL
217 , p_n_attribute9 IN NUMBER := NULL
218 , p_n_attribute10 IN NUMBER := NULL
219 -- bug#6073680 START. Added following parameters to handle WMS Attributes
220 , p_description IN VARCHAR2 := NULL
221 , p_vendor_name IN VARCHAR2 := NULL
222 , p_date_code IN VARCHAR2 := NULL
223 , p_change_date IN DATE := NULL
224 , p_age IN NUMBER := NULL
225 , p_item_size IN NUMBER := NULL
226 , p_color IN VARCHAR2 := NULL
227 , p_volume IN NUMBER := NULL
228 , p_volume_uom IN VARCHAR2 := NULL
229 , p_place_of_origin IN VARCHAR2 := NULL
230 , p_best_by_date IN DATE := NULL
231 , p_length IN NUMBER := NULL
232 , p_length_uom IN VARCHAR2 := NULL
233 , p_recycled_content IN NUMBER := NULL
234 , p_thickness IN NUMBER := NULL
235 , p_thickness_uom IN VARCHAR2 := NULL
236 , p_width IN NUMBER := NULL
237 , p_width_uom IN VARCHAR2 := NULL
238 , p_curl_wrinkle_fold IN VARCHAR2 := NULL
239 , p_lot_attribute_category IN VARCHAR2 := NULL
240 , p_territory_code IN VARCHAR2 := NULL
241 , p_vendor_id IN VARCHAR2 := NULL
242 , p_parent_lot_number IN VARCHAR2 := NULL
243 -- bug#6073680 END. Added following parameters to handle WMS Attributes
244 );
245
246 PROCEDURE log_transaction_rec(
247 p_mtli_lot_rec IN MTL_TRANSACTION_LOTS_INTERFACE%ROWTYPE
248 ,p_mti_trx_rec IN MTL_TRANSACTIONS_INTERFACE%ROWTYPE
249 ,p_mtlt_lot_rec IN MTL_TRANSACTION_LOTS_TEMP%ROWTYPE
250 ,p_mmtt_trx_rec IN MTL_MATERIAL_TRANSACTIONS_TEMP%ROWTYPE
251 ,p_table IN NUMBER
252 );
253
254 PROCEDURE get_lot_expiration_date
255 ( p_mtli_lot_rec IN MTL_TRANSACTION_LOTS_INTERFACE%ROWTYPE
256 ,p_mti_trx_rec IN MTL_TRANSACTIONS_INTERFACE%ROWTYPE
257 ,p_mtlt_lot_rec IN MTL_TRANSACTION_LOTS_TEMP%ROWTYPE
258 ,p_mmtt_trx_rec IN MTL_MATERIAL_TRANSACTIONS_TEMP%ROWTYPE
259 ,p_table IN NUMBER
260 ,x_lot_expiration_date OUT NOCOPY DATE
261 ,x_return_status OUT NOCOPY VARCHAR2
262 );
263 END INV_CALCULATE_EXP_DATE;