[Home] [Help]
PACKAGE: APPS.INV_LOT_APIS
Source
1 PACKAGE inv_lot_apis AUTHID CURRENT_USER AS
2 /* $Header: INVLOTAS.pls 120.3 2008/01/21 14:46:18 aambulka ship $ */
3
4 /**
5 * global variable for lots attributes
6 */
7
8
9
10 TYPE t_genref is REF CURSOR;
11
12 PROCEDURE EXPIRATION_ACTION_CODE( x_codes OUT NOCOPY t_genref,
13 p_code IN VARCHAR2 );
14
15 PROCEDURE get_grade_codes ( x_grades OUT NOCOPY t_genref
16 , p_grade_code IN VARCHAR2);
17 PROCEDURE get_grade_codes(
18 x_grade_codes OUT NOCOPY t_genref
19 );
20
21 PROCEDURE GET_YES_NO( x_option OUT NOCOPY t_genref);
22
23 PROCEDURE GET_YES_NO( x_option OUT NOCOPY t_genref
24 , p_option IN VARCHAR2);
25
26 PROCEDURE get_named_attributes ( x_lot_att OUT NOCOPY t_genref
27 , p_inventory_item_id IN NUMBER
28 , p_organization_id IN NUMBER
29 , p_lot_number IN VARCHAR2
30 , p_parent_lot_number IN VARCHAR2); -- get_opm_lot_attributes
31
32
33 PROCEDURE get_opm_item_attributes ( x_item_lot_att OUT NOCOPY t_genref
34 , p_inventory_item_id IN NUMBER
35 , p_organization_id IN NUMBER); -- get_opm_item_attributes
36
37
38 g_ret_sts_success CONSTANT VARCHAR2(1) := 'S';
39 g_ret_sts_error CONSTANT VARCHAR2(1) := 'E';
40 g_ret_sts_unexp_error CONSTANT VARCHAR2(1) := 'U';
41 g_miss_num CONSTANT NUMBER := 9.99e125;
42 g_miss_char CONSTANT VARCHAR2(1) := CHR(0);
43 g_miss_date CONSTANT DATE := TO_DATE('1', 'j');
44 /*Exception definitions */
45 g_exc_error EXCEPTION;
46 g_exc_unexpected_error EXCEPTION;
47 /*Local variable for stoRing the INV:DEBUG TRACE profile value */
48
49 g_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
50 osfm_form_no_validate CONSTANT NUMBER := 1;
51 osfm_open_interface CONSTANT NUMBER := 2;
52 osfm_form_validate CONSTANT NUMBER := 3;
53 inv CONSTANT NUMBER := 4;
54
55
56
57 /****** For Checking existence of Reservation against a lot ***/
58 PROCEDURE check_reservations(p_inventory_item_id IN NUMBER
59 , p_organization_id IN NUMBER
60 , p_lot_number IN VARCHAR2
61 , p_exists OUT NOCOPY VARCHAR2 ) ;
62
63 PROCEDURE GET_COPY_LOT_ATTR_FLAG( x_return_status OUT NOCOPY VARCHAR2
64 , x_msg_count OUT NOCOPY NUMBER
65 , x_msg_data OUT NOCOPY VARCHAR2
66 , x_copy_lot_attr_flag OUT NOCOPY VARCHAR2
67 , p_organization_id IN NUMBER
68 , p_inventory_item_id IN NUMBER
69 );
70
71
72
73
74
75 /****** Wrappers for validating Lot Attributes *****/
76
77 PROCEDURE validate_grade_code( p_grade_code IN VARCHAR
78 , p_org_id IN NUMBER
79 , p_inventory_item_id IN NUMBER
80 , p_grade_control_flag IN VARCHAR2
81 , x_return_status OUT NOCOPY VARCHAR2
82 , x_msg_count OUT NOCOPY NUMBER
83 , x_msg_data OUT NOCOPY VARCHAR2
84 , x_valid OUT NOCOPY VARCHAR2);
85
86
87 PROCEDURE validate_exp_action_code( p_expiration_action_code IN VARCHAR
88 , p_org_id IN NUMBER
89 , p_inventory_item_id IN NUMBER
90 , p_shelf_life_code IN VARCHAR2
91 , x_return_status OUT NOCOPY VARCHAR2
92 , x_msg_count OUT NOCOPY NUMBER
93 , x_msg_data OUT NOCOPY VARCHAR2
94 , x_valid OUT NOCOPY VARCHAR2);
95
96 PROCEDURE validate_exp_action_date(
97 p_expiration_action_date IN DATE
98 , p_expiration_date IN DATE
99 , x_return_status OUT NOCOPY VARCHAR2
100 , x_msg_count OUT NOCOPY NUMBER
101 , x_msg_data OUT NOCOPY VARCHAR2
102 , x_valid OUT NOCOPY VARCHAR2);
103
104
105 PROCEDURE validate_hold_date(
106 p_hold_date IN DATE
107 , p_origination_date IN DATE
108 , x_return_status OUT NOCOPY VARCHAR2
109 , x_msg_count OUT NOCOPY NUMBER
110 , x_msg_data OUT NOCOPY VARCHAR2
111 , x_valid OUT NOCOPY VARCHAR2);
112
113
114 PROCEDURE validate_retest_date(
115 p_retest_date IN DATE
116 , p_origination_date IN DATE
117 , x_return_status OUT NOCOPY VARCHAR2
118 , x_msg_count OUT NOCOPY NUMBER
119 , x_msg_data OUT NOCOPY VARCHAR2
120 , x_valid OUT NOCOPY VARCHAR2);
121
122
123 PROCEDURE validate_maturity_date(
124 p_maturity_date IN DATE
125 , p_origination_date IN DATE
126 , x_return_status OUT NOCOPY VARCHAR2
127 , x_msg_count OUT NOCOPY NUMBER
128 , x_msg_data OUT NOCOPY VARCHAR2
129 , x_valid OUT NOCOPY VARCHAR2);
130
131 PROCEDURE get_parent_lot_attributes
132 ( x_lot_att OUT nocopy t_genref
133 , p_inventory_item_id IN NUMBER
134 , p_organization_id IN NUMBER
135 , p_lot_number IN VARCHAR2) ;
136
137 PROCEDURE Set_Msi_Default_Attr(
138 x_lot_att OUT NOCOPY t_genref
139 , p_organization_id IN NUMBER
140 , p_inventory_item_id IN NUMBER
141 , p_lot_number IN VARCHAR2 DEFAULT NULL -- nsinghi bug#5209065 rework. Added this param.
142 ) ;
143
144 --Added p_subinventory_code , p_locator_id in below procedure for Onhand status support
145 PROCEDURE get_parent_lov(x_lot_num_lov OUT NOCOPY t_genref, p_wms_installed IN VARCHAR2, p_organization_id IN NUMBER, p_txn_type_id IN NUMBER, p_inventory_item_id IN VARCHAR2 , p_lot_number IN VARCHAR2,
146 p_project_id IN NUMBER, p_task_id IN NUMBER , p_subinventory_code IN VARCHAR2 DEFAULT NULL ,p_locator_id IN NUMBER DEFAULT NULL);
147
148 -- Procedure to validate the Parent-Child Lot naming convention
149 -- as defined in the org/item level parameters
150
151 PROCEDURE validate_child_lot (
152 p_org_id IN NUMBER
153 , p_inventory_item_id IN NUMBER
154 , p_parent_lot_number IN VARCHAR2
155 , p_lot_number IN VARCHAR2
156 , x_return_status OUT NOCOPY VARCHAR2
157 , x_msg_count OUT NOCOPY NUMBER
158 , x_msg_data OUT NOCOPY VARCHAR2
159 ) ;
160
161 PROCEDURE Save_Conversions ( p_org_id IN NUMBER,
162 p_frm_uom IN VARCHAR2,
163 p_to_uom IN VARCHAR2,
164 p_saveConv OUT NOCOPY VARCHAR2) ;
165
166 PROCEDURE Save_Lot_UOM_Conv(
167 p_inventory_item_id MTL_LOT_NUMBERS.inventory_item_id%TYPE,
168 p_org_id NUMBER,
169 P_TRANSACTION_QUANTITY IN NUMBER,
170 p_primary_quantity IN NUMBER ,
171 P_TRANSACTION_UOM IN VARCHAR2 ,
172 p_primary_uom IN VARCHAR2 ,
173 p_lot_number MTL_LOT_NUMBERS.lot_number%TYPE,
174 p_expiration_date MTL_LOT_NUMBERS.expiration_date%TYPE,
175 x_return_status OUT NOCOPY VARCHAR2,
176 x_msg_data OUT NOCOPY VARCHAR2,
177 x_msg_count OUT NOCOPY NUMBER,
178 P_SUPPLIER_LOT_NUMBER MTL_LOT_NUMBERS.SUPPLIER_LOT_NUMBER%TYPE,
179 p_grade_code MTL_LOT_NUMBERS.grade_code%TYPE,
180 p_ORIGINATION_DATE MTL_LOT_NUMBERS.ORIGINATION_DATE%TYPE,
181 P_STATUS_ID MTL_LOT_NUMBERS.STATUS_ID%TYPE,
182 p_RETEST_DATE MTL_LOT_NUMBERS.RETEST_DATE%TYPE,
183 P_MATURITY_DATE MTL_LOT_NUMBERS.MATURITY_DATE%TYPE,
184 P_LOT_ATTRIBUTE_CATEGORY MTL_LOT_NUMBERS.LOT_ATTRIBUTE_CATEGORY%TYPE,
185 P_C_ATTRIBUTE1 MTL_LOT_NUMBERS.C_ATTRIBUTE1%TYPE,
186 P_C_ATTRIBUTE2 MTL_LOT_NUMBERS.C_ATTRIBUTE2%TYPE,
187 P_C_ATTRIBUTE3 MTL_LOT_NUMBERS.C_ATTRIBUTE3%TYPE,
188 P_C_ATTRIBUTE4 MTL_LOT_NUMBERS.C_ATTRIBUTE4%TYPE,
189 P_C_ATTRIBUTE5 MTL_LOT_NUMBERS.C_ATTRIBUTE5%TYPE,
190 P_C_ATTRIBUTE6 MTL_LOT_NUMBERS.C_ATTRIBUTE6%TYPE,
191 P_C_ATTRIBUTE7 MTL_LOT_NUMBERS.C_ATTRIBUTE7%TYPE,
192 P_C_ATTRIBUTE8 MTL_LOT_NUMBERS.C_ATTRIBUTE8%TYPE,
193 P_C_ATTRIBUTE9 MTL_LOT_NUMBERS.C_ATTRIBUTE9%TYPE,
194 P_C_ATTRIBUTE10 MTL_LOT_NUMBERS.C_ATTRIBUTE10%TYPE,
195 P_C_ATTRIBUTE11 MTL_LOT_NUMBERS.C_ATTRIBUTE11%TYPE,
196 P_C_ATTRIBUTE12 MTL_LOT_NUMBERS.C_ATTRIBUTE12%TYPE,
197 P_C_ATTRIBUTE13 MTL_LOT_NUMBERS.C_ATTRIBUTE13%TYPE,
198 P_C_ATTRIBUTE14 MTL_LOT_NUMBERS.C_ATTRIBUTE14%TYPE,
199 P_C_ATTRIBUTE15 MTL_LOT_NUMBERS.C_ATTRIBUTE15%TYPE,
200 P_C_ATTRIBUTE16 MTL_LOT_NUMBERS.C_ATTRIBUTE16%TYPE,
201 P_C_ATTRIBUTE17 MTL_LOT_NUMBERS.C_ATTRIBUTE17%TYPE,
202 P_C_ATTRIBUTE18 MTL_LOT_NUMBERS.C_ATTRIBUTE18%TYPE,
203 P_C_ATTRIBUTE19 MTL_LOT_NUMBERS.C_ATTRIBUTE19%TYPE,
204 P_C_ATTRIBUTE20 MTL_LOT_NUMBERS.C_ATTRIBUTE20%TYPE,
205 P_D_ATTRIBUTE1 MTL_LOT_NUMBERS.D_ATTRIBUTE1%TYPE,
206 P_D_ATTRIBUTE2 MTL_LOT_NUMBERS.D_ATTRIBUTE2%TYPE,
207 P_D_ATTRIBUTE3 MTL_LOT_NUMBERS.D_ATTRIBUTE3%TYPE,
208 P_D_ATTRIBUTE4 MTL_LOT_NUMBERS.D_ATTRIBUTE4%TYPE,
209 P_D_ATTRIBUTE5 MTL_LOT_NUMBERS.D_ATTRIBUTE5%TYPE,
210 P_D_ATTRIBUTE6 MTL_LOT_NUMBERS.D_ATTRIBUTE6%TYPE,
211 P_D_ATTRIBUTE7 MTL_LOT_NUMBERS.D_ATTRIBUTE7%TYPE,
212 P_D_ATTRIBUTE8 MTL_LOT_NUMBERS.D_ATTRIBUTE8%TYPE,
213 P_D_ATTRIBUTE9 MTL_LOT_NUMBERS.D_ATTRIBUTE9%TYPE,
214 P_D_ATTRIBUTE10 MTL_LOT_NUMBERS.D_ATTRIBUTE10%TYPE,
215 P_N_ATTRIBUTE1 MTL_LOT_NUMBERS.N_ATTRIBUTE1%TYPE,
216 P_N_ATTRIBUTE2 MTL_LOT_NUMBERS.N_ATTRIBUTE2%TYPE,
217 P_N_ATTRIBUTE3 MTL_LOT_NUMBERS.N_ATTRIBUTE3%TYPE,
218 P_N_ATTRIBUTE4 MTL_LOT_NUMBERS.N_ATTRIBUTE4%TYPE,
219 P_N_ATTRIBUTE5 MTL_LOT_NUMBERS.N_ATTRIBUTE5%TYPE,
220 P_N_ATTRIBUTE6 MTL_LOT_NUMBERS.N_ATTRIBUTE6%TYPE,
221 P_N_ATTRIBUTE7 MTL_LOT_NUMBERS.N_ATTRIBUTE7%TYPE,
222 P_N_ATTRIBUTE8 MTL_LOT_NUMBERS.N_ATTRIBUTE8%TYPE,
223 P_N_ATTRIBUTE9 MTL_LOT_NUMBERS.N_ATTRIBUTE9%TYPE,
224 P_N_ATTRIBUTE10 MTL_LOT_NUMBERS.N_ATTRIBUTE10%TYPE,
225 P_SECONDARY_QUANTITY IN NUMBER,
226 P_SECONDARY_UOM_CODE IN VARCHAR2 ,
227 p_parent_lot_number MTL_LOT_NUMBERS.parent_lot_number%TYPE,
228 P_ORIGINATION_TYPE MTL_LOT_NUMBERS.ORIGINATION_TYPE%TYPE,
229 P_EXPIRATION_ACTION_DATE MTL_LOT_NUMBERS.EXPIRATION_ACTION_DATE%TYPE,
230 P_EXPIRATION_ACTION_CODE MTL_LOT_NUMBERS.EXPIRATION_ACTION_CODE%TYPE,
231 P_HOLD_DATE MTL_LOT_NUMBERS.HOLD_DATE%TYPE,
232 P_REASON_ID IN VARCHAR2 ,
233 p_response IN VARCHAR2 ,
234 P_ATTRIBUTE_CATEGORY MTL_LOT_NUMBERS.ATTRIBUTE_CATEGORY%TYPE,
235 P_ATTRIBUTE1 MTL_LOT_NUMBERS.ATTRIBUTE1%TYPE,
236 P_ATTRIBUTE2 MTL_LOT_NUMBERS.ATTRIBUTE2%TYPE,
237 P_ATTRIBUTE3 MTL_LOT_NUMBERS.ATTRIBUTE3%TYPE,
238 P_ATTRIBUTE4 MTL_LOT_NUMBERS.ATTRIBUTE4%TYPE,
239 P_ATTRIBUTE5 MTL_LOT_NUMBERS.ATTRIBUTE5%TYPE,
240 P_ATTRIBUTE6 MTL_LOT_NUMBERS.ATTRIBUTE6%TYPE,
241 P_ATTRIBUTE7 MTL_LOT_NUMBERS.ATTRIBUTE7%TYPE,
242 P_ATTRIBUTE8 MTL_LOT_NUMBERS.ATTRIBUTE8%TYPE,
243 P_ATTRIBUTE9 MTL_LOT_NUMBERS.ATTRIBUTE9%TYPE,
244 P_ATTRIBUTE10 MTL_LOT_NUMBERS.ATTRIBUTE10%TYPE,
245 P_ATTRIBUTE11 MTL_LOT_NUMBERS.ATTRIBUTE11%TYPE,
246 P_ATTRIBUTE12 MTL_LOT_NUMBERS.ATTRIBUTE12%TYPE,
247 P_ATTRIBUTE13 MTL_LOT_NUMBERS.ATTRIBUTE13%TYPE,
248 P_ATTRIBUTE14 MTL_LOT_NUMBERS.ATTRIBUTE14%TYPE,
249 P_ATTRIBUTE15 MTL_LOT_NUMBERS.ATTRIBUTE15%TYPE,
250 P_ITEM_DUAL_UOM_CONTROL IN VARCHAR2 , -- hold item's Tracking indicator
251 P_copy_pnt_lot_att_flag IN VARCHAR2 ,
252 p_secondary_default_ind IN VARCHAR2 ,
253 p_disable_flag IN MTL_LOT_NUMBERS.DISABLE_FLAG%TYPE DEFAULT NULL, -- 4239238 Start
254 p_territory_code IN MTL_LOT_NUMBERS.TERRITORY_CODE%TYPE DEFAULT NULL,
255 p_date_code IN MTL_LOT_NUMBERS.DATE_CODE%TYPE DEFAULT NULL,
256 p_change_date IN MTL_LOT_NUMBERS.CHANGE_DATE%TYPE DEFAULT NULL,
257 p_age IN MTL_LOT_NUMBERS.AGE%TYPE DEFAULT NULL,
258 p_item_size IN MTL_LOT_NUMBERS.ITEM_SIZE%TYPE DEFAULT NULL,
259 p_color IN MTL_LOT_NUMBERS.COLOR%TYPE DEFAULT NULL,
260 p_volume IN MTL_LOT_NUMBERS.VOLUME%TYPE DEFAULT NULL,
261 p_volume_uom IN MTL_LOT_NUMBERS.VOLUME_UOM%TYPE DEFAULT NULL,
262 p_place_of_origin IN MTL_LOT_NUMBERS.PLACE_OF_ORIGIN%TYPE DEFAULT NULL,
263 p_best_by_date IN MTL_LOT_NUMBERS.BEST_BY_DATE%TYPE DEFAULT NULL,
264 p_length IN MTL_LOT_NUMBERS.LENGTH%TYPE DEFAULT NULL,
265 p_length_uom IN MTL_LOT_NUMBERS.LENGTH_UOM%TYPE DEFAULT NULL,
266 p_recycled_content IN MTL_LOT_NUMBERS.RECYCLED_CONTENT%TYPE DEFAULT NULL,
267 p_thickness IN MTL_LOT_NUMBERS.THICKNESS%TYPE DEFAULT NULL,
268 p_thickness_uom IN MTL_LOT_NUMBERS.THICKNESS_UOM%TYPE DEFAULT NULL,
269 p_width IN MTL_LOT_NUMBERS.WIDTH%TYPE DEFAULT NULL,
270 p_width_uom IN MTL_LOT_NUMBERS.WIDTH_UOM%TYPE DEFAULT NULL,
271 p_curl_wrinkle_fold IN MTL_LOT_NUMBERS.CURL_WRINKLE_FOLD%TYPE DEFAULT NULL,
272 p_vendor_name IN MTL_LOT_NUMBERS.VENDOR_NAME%TYPE DEFAULT NULL, -- 4239238 End
273 p_source_lot IN VARCHAR2 DEFAULT NULL, --Bug#5349912
274 p_copy_other_conversions IN VARCHAR2 DEFAULT 'F' --Bug#5349912
275 );
276
277
278
279
280 END inv_lot_apis;