[Home] [Help]
PACKAGE: APPS.INV_KANBAN_PVT
Source
1 PACKAGE INV_Kanban_PVT as
2 /* $Header: INVVKBNS.pls 120.5 2006/08/18 13:09:39 sbitra noship $ */
3
4
5 /* **
6 List of Global constants used in kanban API's
7 ** */
8 G_Current_Plan Constant Number := -1;
9
10 G_Source_Type_InterOrg Constant Number := 1;
11 G_Source_Type_Supplier Constant Number := 2;
12 G_Source_Type_IntraOrg Constant Number := 3;
13 G_Source_Type_Production Constant Number := 4;
14
15 G_Supply_Status_New Constant Number := 1;
16 G_Supply_Status_Full Constant Number := 2;
17 G_Supply_Status_Wait Constant Number := 3;
18 G_Supply_Status_Empty Constant Number := 4;
19 G_Supply_Status_InProcess Constant Number := 5;
20 G_Supply_Status_InTransit Constant Number := 6;
21 G_Supply_Status_Exception Constant Number := 7;
22
23 G_Card_Type_Replenishable Constant Number := 1;
24 G_Card_Type_NonReplenishable Constant Number := 2;
25
26 G_Card_Status_Active Constant Number := 1;
27 G_Card_Status_Hold Constant Number := 2;
28 G_Card_Status_Cancel Constant Number := 3;
29
30 G_No_Pull_Sequence Constant Number := -1;
31
32 G_Doc_type_PO Constant Number := 1;
33 G_Doc_type_Release Constant Number := 2;
34 G_Doc_type_Internal_Req Constant Number := 3;
35 G_Doc_type_Transfer_Order Constant Number := 4;
36 G_Doc_type_Discrete_Job Constant Number := 5;
37 G_Doc_type_Rep_Schedule Constant Number := 6;
38 G_Doc_type_Flow_Schedule Constant Number := 7;
39 G_Doc_type_lot_job Constant Number := 8;
40
41 /* **
42 Defining a data type - table of kanban card Ids
43 ( Single column table - )
44 ** */
45 TYPE Kanban_Card_Id_Tbl_Type IS TABLE OF MTL_KANBAN_CARDS.KANBAN_CARD_ID%TYPE
46 INDEX BY BINARY_INTEGER;
47
48
49 /* **
50 Defining a structure of type Pull_sequence_Rec
51 ** */
52 TYPE Pull_Sequence_Rec_Type IS RECORD
53 ( pull_sequence_id NUMBER := FND_API.G_MISS_NUM
54 , inventory_item_id NUMBER := FND_API.G_MISS_NUM
55 , organization_id NUMBER := FND_API.G_MISS_NUM
56 , subinventory_name VARCHAR2(10) := FND_API.G_MISS_CHAR
57 , Kanban_plan_id NUMBER := FND_API.G_MISS_NUM
58 , source_type NUMBER := FND_API.G_MISS_NUM
59 , last_update_date DATE := FND_API.G_MISS_DATE
60 , last_updated_by NUMBER := FND_API.G_MISS_NUM
61 , creation_date DATE := FND_API.G_MISS_DATE
62 , created_by NUMBER := FND_API.G_MISS_NUM
63 , locator_id NUMBER := FND_API.G_MISS_NUM
64 , supplier_id NUMBER := FND_API.G_MISS_NUM
65 , supplier_site_id NUMBER := FND_API.G_MISS_NUM
66 , source_organization_id NUMBER := FND_API.G_MISS_NUM
67 , source_subinventory VARCHAR2(10) := FND_API.G_MISS_CHAR
68 , source_locator_id NUMBER := FND_API.G_MISS_NUM
69 , wip_line_id NUMBER := FND_API.G_MISS_NUM
70 , replenishment_lead_time NUMBER := FND_API.G_MISS_NUM
71 , calculate_kanban_flag NUMBER := FND_API.G_MISS_NUM
72 , kanban_size NUMBER := FND_API.G_MISS_NUM
73 , fixed_lot_multiplier NUMBER := FND_API.G_MISS_NUM
74 , safety_stock_days NUMBER := FND_API.G_MISS_NUM
75 , number_of_cards NUMBER := FND_API.G_MISS_NUM
76 , minimum_order_quantity NUMBER := FND_API.G_MISS_NUM
77 , aggregation_type NUMBER := FND_API.G_MISS_NUM
78 , allocation_percent NUMBER := FND_API.G_MISS_NUM
79 , last_update_login NUMBER := FND_API.G_MISS_NUM
80 , updated_flag NUMBER := FND_API.G_MISS_NUM
81 , attribute_category VARCHAR2(30) := FND_API.G_MISS_CHAR
82 , attribute1 VARCHAR2(150) := FND_API.G_MISS_CHAR
83 , attribute2 VARCHAR2(150) := FND_API.G_MISS_CHAR
84 , attribute3 VARCHAR2(150) := FND_API.G_MISS_CHAR
85 , attribute4 VARCHAR2(150) := FND_API.G_MISS_CHAR
86 , attribute5 VARCHAR2(150) := FND_API.G_MISS_CHAR
87 , attribute6 VARCHAR2(150) := FND_API.G_MISS_CHAR
88 , attribute7 VARCHAR2(150) := FND_API.G_MISS_CHAR
89 , attribute8 VARCHAR2(150) := FND_API.G_MISS_CHAR
90 , attribute9 VARCHAR2(150) := FND_API.G_MISS_CHAR
91 , attribute10 VARCHAR2(150) := FND_API.G_MISS_CHAR
92 , attribute11 VARCHAR2(150) := FND_API.G_MISS_CHAR
93 , attribute12 VARCHAR2(150) := FND_API.G_MISS_CHAR
94 , attribute13 VARCHAR2(150) := FND_API.G_MISS_CHAR
95 , attribute14 VARCHAR2(150) := FND_API.G_MISS_CHAR
96 , attribute15 VARCHAR2(150) := FND_API.G_MISS_CHAR
97 , request_id NUMBER := FND_API.G_MISS_NUM
98 , program_application_id NUMBER := FND_API.G_MISS_NUM
99 , program_id NUMBER := FND_API.G_MISS_NUM
100 , program_update_date DATE := FND_API.G_MISS_DATE
101 , release_kanban_flag NUMBER := FND_API.G_MISS_NUM
102 , point_of_use_x NUMBER := FND_API.G_MISS_NUM
103 , point_of_use_y NUMBER := FND_API.G_MISS_NUM
104 , point_of_supply_x NUMBER := FND_API.G_MISS_NUM
105 , point_of_supply_y NUMBER := FND_API.G_MISS_NUM
106 , planning_update_status NUMBER := FND_API.G_MISS_NUM
107 , auto_request VARCHAR2(1) := NULL
108 , kanban_card_type NUMBER := NULL
109 , auto_allocate_flag NUMBER := FND_API.G_MISS_NUM --Added for 3905884.
110 );
111
112
113
114 /* **
115 Defining a structure of type Kanban_Card_Rec_Type
116 ** */
117 TYPE Kanban_Card_Rec_Type IS RECORD
118 ( kanban_card_id NUMBER := FND_API.g_miss_num
119 , kanban_card_number VARCHAR2(30) := FND_API.g_miss_char
120 , pull_sequence_id NUMBER := FND_API.g_miss_num
121 , inventory_item_id NUMBER := FND_API.g_miss_num
122 , organization_id NUMBER := FND_API.g_miss_num
123 , subinventory_name VARCHAR2(10) := FND_API.g_miss_char
124 , supply_status NUMBER := FND_API.g_miss_num
125 , card_status NUMBER := FND_API.g_miss_num
126 , kanban_card_type NUMBER := FND_API.g_miss_num
127 , source_type NUMBER := FND_API.g_miss_num
128 , kanban_size NUMBER := FND_API.g_miss_num
129 , last_update_date DATE := FND_API.g_miss_date
130 , last_updated_by NUMBER := FND_API.g_miss_num
131 , creation_date DATE := FND_API.g_miss_date
132 , created_by NUMBER := FND_API.g_miss_num
133 , locator_id NUMBER := FND_API.g_miss_num
134 , supplier_id NUMBER := FND_API.g_miss_num
135 , supplier_site_id NUMBER := FND_API.g_miss_num
136 , source_organization_id NUMBER := FND_API.g_miss_num
137 , source_subinventory VARCHAR2(10) := FND_API.g_miss_char
138 , source_locator_id NUMBER := FND_API.g_miss_num
139 , wip_line_id NUMBER := FND_API.g_miss_num
140 , current_replnsh_cycle_id NUMBER := FND_API.g_miss_num
141 , document_type NUMBER := FND_API.G_MISS_NUM
142 , document_header_id NUMBER := FND_API.G_MISS_NUM
143 , document_detail_id NUMBER := FND_API.G_MISS_NUM
144 , error_code VARCHAR2(30) := FND_API.g_miss_char
145 , last_update_login NUMBER := FND_API.g_miss_num
146 , last_print_date DATE := FND_API.g_miss_date
147 , attribute_category VARCHAR2(30) := FND_API.g_miss_char
148 , attribute1 VARCHAR2(150) := FND_API.g_miss_char
149 , attribute2 VARCHAR2(150) := FND_API.g_miss_char
150 , attribute3 VARCHAR2(150) := FND_API.g_miss_char
151 , attribute4 VARCHAR2(150) := FND_API.g_miss_char
152 , attribute5 VARCHAR2(150) := FND_API.g_miss_char
153 , attribute6 VARCHAR2(150) := FND_API.g_miss_char
154 , attribute7 VARCHAR2(150) := FND_API.g_miss_char
155 , attribute8 VARCHAR2(150) := FND_API.g_miss_char
156 , attribute9 VARCHAR2(150) := FND_API.g_miss_char
157 , attribute10 VARCHAR2(150) := FND_API.g_miss_char
158 , attribute11 VARCHAR2(150) := FND_API.g_miss_char
159 , attribute12 VARCHAR2(150) := FND_API.g_miss_char
160 , attribute13 VARCHAR2(150) := FND_API.g_miss_char
161 , attribute14 VARCHAR2(150) := FND_API.g_miss_char
162 , attribute15 VARCHAR2(150) := FND_API.g_miss_char
163 , request_id NUMBER := FND_API.g_miss_num
164 , program_application_id NUMBER := FND_API.g_miss_num
165 , program_id NUMBER := FND_API.g_miss_num
166 , program_update_date DATE := FND_API.g_miss_date
167 , lot_item_id NUMBER DEFAULT NULL
168 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
169 , lot_number VARCHAR2(80) DEFAULT NULL
170 , lot_item_revision VARCHAR2(1) DEFAULT NULL
171 , lot_subinventory_code VARCHAR2(30) DEFAULT NULL
172 , lot_location_id NUMBER DEFAULT NULL
173 , lot_quantity NUMBER DEFAULT NULL
174 , replenish_quantity NUMBER DEFAULT NULL
175 , need_by_date DATE DEFAULT NULL
176 , source_wip_entity_id NUMBER DEFAULT NULL);
177
178
179 /* **
180 Defining a data type - table of pull_sequence_id
181 Single column table -
182 ** */
183 TYPE Pull_Sequence_Id_Tbl_Type IS TABLE
184 OF MTL_KANBAN_PULL_SEQUENCES.PULL_SEQUENCE_ID%TYPE
185 INDEX BY BINARY_INTEGER;
186
187
188 PullSeqTable Pull_Sequence_Id_Tbl_Type;
189
190 TYPE Operation_Tbl_Type IS TABLE
191 OF NUMBER INDEX BY BINARY_INTEGER;
192
193 G_Operation_Tbl Operation_Tbl_Type;
194
195 /* **
196 To update pull sequence table
197 ** */
198 PROCEDURE Update_Pull_sequence_tbl
199 (x_return_status Out NOCOPY Varchar2,
200 p_Pull_Sequence_tbl INV_Kanban_PVT.Pull_Sequence_Id_Tbl_Type,
201 x_update_flag IN Varchar2,
202 p_operation_tbl INV_Kanban_PVT.operation_tbl_type := G_Operation_Tbl);
203
204 /* **
205 API to get Kanban constants
206 ** */
207 Procedure Get_Constants
208 (X_Ret_Success Out NOCOPY Varchar2,
209 X_Ret_Error Out NOCOPY Varchar2 ,
210 X_Ret_Unexp_Error Out NOCOPY Varchar2 ,
211 X_Current_Plan Out NOCOPY Number,
212 X_Source_Type_InterOrg Out NOCOPY Number,
213 X_Source_Type_Supplier Out NOCOPY Number,
214 X_Source_Type_IntraOrg Out NOCOPY Number,
215 X_Source_Type_Production Out NOCOPY Number,
216 X_Card_Type_Replenishable Out NOCOPY Number,
217 X_Card_Type_NonReplenishable Out NOCOPY Number,
218 X_Card_Status_Active Out NOCOPY Number,
219 X_Card_Status_Hold Out NOCOPY Number,
220 X_Card_Status_Cancel Out NOCOPY Number,
221 X_No_Pull_sequence Out NOCOPY Number,
222 X_Doc_Type_Po Out NOCOPY Number,
223 X_Doc_Type_Release Out NOCOPY Number,
224 X_Doc_Type_Internal_Req Out NOCOPY Number);
225
226
227 /* **
228 API to Delete a pull sequence
229 ** */
230 PROCEDURE Delete_Pull_Sequence
231 (x_return_status Out NOCOPY Varchar2,
232 p_kanban_plan_id Number);
233
234 /* **
235 API to Create a new Pull sequence
236 ** */
237 PROCEDURE Insert_Pull_sequence
238 (x_return_status Out NOCOPY Varchar2,
239 p_Pull_Sequence_Rec INV_Kanban_PVT.Pull_sequence_Rec_Type);
240
241 /* **
242 API to update an existing Pull Sequence
243 ** */
244 PROCEDURE Update_Pull_sequence
245 (x_return_status Out NOCOPY Varchar2,
246 x_Pull_Sequence_Rec IN OUT NOCOPY INV_Kanban_PVT.Pull_sequence_Rec_Type);
247
248 /* **
249 Updating a Kanban Card supply Status is a Overloaded function
250 can be called with various inputs. Following are the various
251 forms of this API with the inputs required.
252 ** */
253 PROCEDURE Update_Card_Supply_Status(X_Return_Status Out NOCOPY Varchar2,
254 p_Kanban_Card_Id Number,
255 p_Supply_Status Number,
256 p_Document_type Number,
257 p_Document_Header_Id Number,
258 p_Document_detail_Id NUMBER,
259 p_replenish_quantity NUMBER DEFAULT NULL,
260 p_need_by_date DATE DEFAULT NULL,
261 p_source_wip_entity_id NUMBER DEFAULT NULL);
262
263 PROCEDURE Update_Card_Supply_Status(X_Return_Status Out NOCOPY Varchar2,
264 p_Kanban_Card_Id Number,
265 p_Supply_Status Number);
266
267 PROCEDURE Update_Card_Supply_Status
268 ( p_api_version_number IN NUMBER
269 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
270 , p_commit IN VARCHAR2 := FND_API.G_FALSE
271 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
272 , x_return_status OUT NOCOPY VARCHAR2
273 , x_msg_count OUT NOCOPY NUMBER
274 , x_msg_data OUT NOCOPY VARCHAR2
275 , p_Kanban_Card_Id Number
276 , p_Supply_Status NUMBER
277 , p_Document_type IN NUMBER DEFAULT NULL
278 , p_Document_Header_Id IN NUMBER DEFAULT NULL
279 , p_Document_detail_Id IN NUMBER DEFAULT NULL
280 , p_replenish_quantity IN NUMBER DEFAULT NULL
281 , p_need_by_date IN DATE DEFAULT NULL
282 , p_source_wip_entity_id IN NUMBER DEFAULT NULL);
283
284 PROCEDURE Update_Card_Supply_Status(X_Return_Status Out NOCOPY Varchar2,
285 p_Kanban_Card_Id Number,
286 p_Supply_Status Number,
287 p_Document_type Number,
288 p_Document_Header_Id Number);
289
290 /* **
291 API to check the existence of valid kanban cards for
292 a pull sequence.
293 ** */
294 FUNCTION Valid_Kanban_Cards_Exist(p_Pull_sequence_id number)
295 Return Boolean;
296
297
298 /* **
299 API to check the existence of valid kanban cards for
300 a pull sequence, which have the same Point of Supply
301 but different quantity
302 ** */
303 FUNCTION Diff_Qty_Kanban_Cards_Exist(
304 p_pull_sequence_id number,
305 p_source_type number,
306 p_supplier_id number,
307 p_supplier_site_id number,
308 p_source_organization_id number,
309 p_source_subinventory varchar2,
310 p_source_locator_id number,
311 p_wip_line_id number,
312 p_kanban_size number)
313 Return Number;
314
315
316 /* **
317 API to check if it is OK to create kanban cards for the given
318 pull sequence.
319 ** */
320 FUNCTION Ok_To_Create_Kanban_Cards(p_Pull_sequence_id number)
321 Return Boolean;
322
323
324 /* **
325 API to check if it is OK to delete a given
326 pull sequence.
327 ** */
331
328 FUNCTION Ok_To_Delete_Pull_Sequence(p_Pull_sequence_id number)
329 RETURN BOOLEAN;
330
332 /* **
333 API to check if the kanban card is a valid production card.
334 ** */
335 FUNCTION Valid_Production_Kanban_Card( p_wip_entity_id number,
336 p_org_id number,
337 p_kanban_id number,
338 p_inv_item_id number,
339 p_subinventory varchar2,
340 p_locator_id number )
341 RETURN BOOLEAN;
342
343
344 /* **
345 API to create kanban cards for
346 a pull sequence.
347 ** */
348 PROCEDURE Create_Kanban_Cards
349 (X_return_status OUT NOCOPY VARCHAR2,
350 X_Kanban_Card_Ids OUT NOCOPY INV_Kanban_PVT.Kanban_Card_Id_Tbl_Type,
351 P_Pull_Sequence_Rec INV_Kanban_PVT.Pull_Sequence_Rec_Type,
352 p_Supply_Status NUMBER);
353
354
355 /* **
356 API to validate and create replenishment order for a
357 Kanban card.
358 ** */
359 PROCEDURE Check_And_Create_Replenishment
360 (x_return_status Out NOCOPY Varchar2,
361 X_Supply_Status Out NOCOPY Number,
362 X_Current_Replenish_Cycle_Id Out NOCOPY Number,
363 P_Kanban_Card_Rec In Out NOCOPY INV_Kanban_PVT.Kanban_Card_Rec_Type);
364
365 PROCEDURE test;
366
367 PROCEDURE update_kanban_card_status
368 (p_Card_Status IN Number,
369 p_pull_sequence_id IN Number);
370
371 /*Bug 3740514: Controlling the Updation of Kanban Cards and its activity for
372 Cards with Supply Type as 'Supplier' and Document Type as 'Release' */
373 PROCEDURE update_card_and_card_status
374 (p_kanban_card_id IN NUMBER,
375 p_supply_status IN NUMBER,/*Bug# 4490269 */
376 p_update OUT NOCOPY BOOLEAN) ;
377
378
379 PROCEDURE return_att_quantity(p_org_id IN NUMBER,
380 p_item_id IN NUMBER,
381 p_rev IN VARCHAR2,
382 p_lot_no IN VARCHAR2,
383 p_subinv IN VARCHAR2,
384 p_locator_id IN NUMBER,
385 x_qoh OUT NOCOPY NUMBER,
386 x_atr OUT NOCOPY NUMBER,
387 x_att OUT NOCOPY NUMBER,
388 x_err_code OUT NOCOPY NUMBER,
389 x_err_msg OUT NOCOPY VARCHAR2);
390
391 PROCEDURE get_max_kanban_asmbly_qty( p_bill_seq_id IN NUMBER,
392 P_COMPONENT_ITEM_ID IN NUMBER,
393 P_BOM_REVISION_DATE IN DATE DEFAULT NULL,
394 P_START_SEQ_NUM IN NUMBER,
395 P_AVAILABLE_QTY IN NUMBER,
396 X_MAX_ASMBLY_QTY OUT NOCOPY NUMBER,
397 X_ERROR_CODE OUT NOCOPY NUMBER,
398 X_error_msg OUT NOCOPY VARCHAR2);
399
400 PROCEDURE GET_KANBAN_REC_GRP_INFO(p_organization_id IN NUMBER,
401 p_kanban_assembly_id IN NUMBER,
402 p_rtg_rev_date IN DATE DEFAULT Sysdate,
403 x_bom_seq_id OUT NOCOPY NUMBER,
404 x_start_seq_num OUT NOCOPY NUMBER,
405 X_error_code OUT NOCOPY NUMBER,
406 X_error_msg OUT NOCOPY VARCHAR2);
407
408 FUNCTION eligible_for_lbj
409 (p_organization_id IN NUMBER,
410 p_inventory_item_id IN NUMBER,
411 p_source_type_id IN NUMBER,
412 p_kanban_card_id IN NUMBER DEFAULT NULL) RETURN VARCHAR2;
413
414
415 /*The following procedure is added for 3905884. This procedure automatically
416 allocates the move order created for Kanaban replenishment if the
417 Auto_Allocate_Flag is set
418 */
419 PROCEDURE Auto_Allocate_Kanban (
420 p_mo_header_id IN NUMBER ,
421 x_return_status OUT NOCOPY VARCHAR2 ,
422 x_msg_count OUT NOCOPY NUMBER ,
423 x_msg_data OUT NOCOPY VARCHAR2 );
424
425
426 END INV_Kanban_PVT;