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