1 PACKAGE INV_CALCULATE_EXP_DATE AS
2 /* $Header: INVCEDTS.pls 120.1 2007/12/20 18:01:24 asatpute noship $ */
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#6073680 Added this procedure.
124 PROCEDURE check_lot_exists
125 ( p_inventory_item_id IN NUMBER
126 ,p_organization_id IN NUMBER
127 ,p_lot_number IN VARCHAR2
128 ,x_lot_exist OUT NOCOPY VARCHAR2
129 ,x_return_status OUT NOCOPY VARCHAR2
130 ) ;
131
132 /*
133 PROCEDURE update_inv_lot_attr (
134 p_inventory_item_id IN NUMBER
135 , p_organization_id IN NUMBER
136 , p_lot_number IN VARCHAR2
137 , p_expiration_date IN DATE
138 , p_grade_code IN VARCHAR2
139 , p_status_id IN NUMBER
140 , p_origination_type IN NUMBER
141 , p_origination_date IN DATE
142 , p_retest_date IN DATE
143 , p_exp_action_dt IN DATE
144 , p_exp_action_code IN VARCHAR2
145 , p_hold_date IN DATE
146 , p_maturity_date IN DATE
147 , p_vendor_lot_num IN VARCHAR2
148 , x_return_status OUT NOCOPY VARCHAR2
149 ) ;
150 */
151 PROCEDURE update_inv_lot_attr(
152 x_return_status OUT NOCOPY VARCHAR2
153 , x_msg_count OUT NOCOPY NUMBER
154 , x_msg_data OUT NOCOPY VARCHAR2
155 , p_inventory_item_id IN NUMBER
156 , p_organization_id IN NUMBER
157 , p_lot_number IN VARCHAR2
158 , p_source IN NUMBER
159 , p_expiration_date IN DATE DEFAULT NULL
160 , p_grade_code IN VARCHAR2 DEFAULT NULL
161 , p_origination_date IN DATE DEFAULT NULL
162 , p_origination_type IN NUMBER DEFAULT NULL
163 , p_status_id IN NUMBER DEFAULT NULL
164 , p_retest_date IN DATE DEFAULT NULL
165 , p_maturity_date IN DATE DEFAULT NULL
166 , p_supplier_lot_number IN VARCHAR2 DEFAULT NULL
167 , p_expiration_action_code IN VARCHAR2 DEFAULT NULL
168 , p_expiration_action_date IN DATE DEFAULT NULL
169 , p_hold_date IN DATE DEFAULT NULL
170 , p_c_attribute1 IN VARCHAR2 := NULL
171 , p_c_attribute2 IN VARCHAR2 := NULL
172 , p_c_attribute3 IN VARCHAR2 := NULL
173 , p_c_attribute4 IN VARCHAR2 := NULL
174 , p_c_attribute5 IN VARCHAR2 := NULL
175 , p_c_attribute6 IN VARCHAR2 := NULL
176 , p_c_attribute7 IN VARCHAR2 := NULL
177 , p_c_attribute8 IN VARCHAR2 := NULL
178 , p_c_attribute9 IN VARCHAR2 := NULL
179 , p_c_attribute10 IN VARCHAR2 := NULL
180 , p_c_attribute11 IN VARCHAR2 := NULL
181 , p_c_attribute12 IN VARCHAR2 := NULL
182 , p_c_attribute13 IN VARCHAR2 := NULL
183 , p_c_attribute14 IN VARCHAR2 := NULL
184 , p_c_attribute15 IN VARCHAR2 := NULL
185 , p_c_attribute16 IN VARCHAR2 := NULL
186 , p_c_attribute17 IN VARCHAR2 := NULL
187 , p_c_attribute18 IN VARCHAR2 := NULL
188 , p_c_attribute19 IN VARCHAR2 := NULL
189 , p_c_attribute20 IN VARCHAR2 := NULL
190 , p_d_attribute1 IN DATE := NULL
191 , p_d_attribute2 IN DATE := NULL
192 , p_d_attribute3 IN DATE := NULL
193 , p_d_attribute4 IN DATE := NULL
194 , p_d_attribute5 IN DATE := NULL
195 , p_d_attribute6 IN DATE := NULL
196 , p_d_attribute7 IN DATE := NULL
197 , p_d_attribute8 IN DATE := NULL
198 , p_d_attribute9 IN DATE := NULL
199 , p_d_attribute10 IN DATE := NULL
200 , p_n_attribute1 IN NUMBER := NULL
201 , p_n_attribute2 IN NUMBER := NULL
202 , p_n_attribute3 IN NUMBER := NULL
203 , p_n_attribute4 IN NUMBER := NULL
204 , p_n_attribute5 IN NUMBER := NULL
205 , p_n_attribute6 IN NUMBER := NULL
206 , p_n_attribute7 IN NUMBER := NULL
207 , p_n_attribute8 IN NUMBER := NULL
208 , p_n_attribute9 IN NUMBER := NULL
209 , p_n_attribute10 IN NUMBER := NULL
210 -- bug#6073680 START. Added following parameters to handle WMS Attributes
211 , p_description IN VARCHAR2 := NULL
212 , p_vendor_name IN VARCHAR2 := NULL
213 , p_date_code IN VARCHAR2 := NULL
214 , p_change_date IN DATE := NULL
215 , p_age IN NUMBER := NULL
216 , p_item_size IN NUMBER := NULL
217 , p_color IN VARCHAR2 := NULL
218 , p_volume IN NUMBER := NULL
219 , p_volume_uom IN VARCHAR2 := NULL
220 , p_place_of_origin IN VARCHAR2 := NULL
221 , p_best_by_date IN DATE := NULL
222 , p_length IN NUMBER := NULL
223 , p_length_uom IN VARCHAR2 := NULL
224 , p_recycled_content IN NUMBER := NULL
225 , p_thickness IN NUMBER := NULL
226 , p_thickness_uom IN VARCHAR2 := NULL
227 , p_width IN NUMBER := NULL
228 , p_width_uom IN VARCHAR2 := NULL
229 , p_curl_wrinkle_fold IN VARCHAR2 := NULL
230 , p_lot_attribute_category IN VARCHAR2 := NULL
231 , p_territory_code IN VARCHAR2 := NULL
232 , p_vendor_id IN VARCHAR2 := NULL
233 , p_parent_lot_number IN VARCHAR2 := NULL
234 -- bug#6073680 END. Added following parameters to handle WMS Attributes
235 );
236
237 PROCEDURE log_transaction_rec(
238 p_mtli_lot_rec IN MTL_TRANSACTION_LOTS_INTERFACE%ROWTYPE
239 ,p_mti_trx_rec IN MTL_TRANSACTIONS_INTERFACE%ROWTYPE
240 ,p_mtlt_lot_rec IN MTL_TRANSACTION_LOTS_TEMP%ROWTYPE
241 ,p_mmtt_trx_rec IN MTL_MATERIAL_TRANSACTIONS_TEMP%ROWTYPE
242 ,p_table IN NUMBER
243 );
244
245 PROCEDURE get_lot_expiration_date
246 ( p_mtli_lot_rec IN MTL_TRANSACTION_LOTS_INTERFACE%ROWTYPE
247 ,p_mti_trx_rec IN MTL_TRANSACTIONS_INTERFACE%ROWTYPE
248 ,p_mtlt_lot_rec IN MTL_TRANSACTION_LOTS_TEMP%ROWTYPE
249 ,p_mmtt_trx_rec IN MTL_MATERIAL_TRANSACTIONS_TEMP%ROWTYPE
250 ,p_table IN NUMBER
251 ,x_lot_expiration_date OUT NOCOPY DATE
252 ,x_return_status OUT NOCOPY VARCHAR2
253 );
254 END INV_CALCULATE_EXP_DATE;
[Home] [Help]