1 PACKAGE AHL_MM_MTL_MGT_PVT AUTHID CURRENT_USER AS
2 /* $Header: AHLVMMGS.pls 120.0.12020000.2 2012/12/10 14:09:59 shnatu noship $*/
3 -------------------------------------
4 -- Common Package CONSTANTS --
5 -------------------------------------
6 G_APP_NAME CONSTANT VARCHAR2(3) := 'AHL'; -- Use for all FND_MESSAGE.SET_NAME calls
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AHL_MM_MTL_MGT_PVT';
8 ---------------------------------------------------------------------
9 -- Define Record Types for record structures needed by the APIs --
10 ---------------------------------------------------------------------
11
12 TYPE OAF_VARCHAR2_TABLE_100
13 IS
14 TABLE OF VARCHAR2 ( 100 );
15
16 TYPE OAF_NUMBER_TABLE
17 IS
18 TABLE OF NUMBER;
19
20 --AJPRASAN: Addition for FILTER_INSTANCES_MATCHING_BG() API
21 TYPE BG_Demand_Rec IS RECORD(
22 Scheduled_Material_ID NUMBER,
23 Include_HardLimit_MRs VARCHAR2(1),
24 Include_SoftLimit_MRs VARCHAR2(1),
25 Include_Alt_Items VARCHAR2(1),
26 Organization_Name VARCHAR2(240),
27 Serial_Number VARCHAR2(80),
28 Item_Owner VARCHAR2(360),
29 Reservation_Type VARCHAR2(30),
30 WO_Number VARCHAR2(80),
31 WO_Org VARCHAR2(240),
32 Filter_Reservations VARCHAR2(1),
33 Visit_Org VARCHAR2(240),
34 Visit_Number VARCHAR2(15),
35 Visit_Start_Date DATE,
36 Visit_Unit VARCHAR2(80)
37 );
38
39 TYPE Mtl_Avl_Schld_Rec IS RECORD(
40 SCHEDULE_MATERIAL_ID NUMBER ,
41 AVAILABLE_QUANTITY NUMBER ,
42 AVL_SCH_DATE DATE ,
43 ERROR_CODE NUMBER ,
44 ERROR_MESSAGE VARCHAR2(2000)
45 );
46
47 TYPE Supply_Rec_Type IS RECORD(
48 Supply_Org_Id NUMBER,
49 Supply_Subinv_Code VARCHAR2(10),
50 Supply_Locator_Id NUMBER,
51 Serial_Number VARCHAR2(30)
52 );
53
54 TYPE AOG_REC_TYPE IS RECORD(
55 SCHEDULE_MATERIAL_ID NUMBER ,
56 AOG_FLAG VARCHAR2(1),
57 ASM_OBJECT_VERSION_NUMBER NUMBER
58 );
59
60 TYPE Inv_Mtl_Avl_Rec IS RECORD(
61 AVAILABLE_QUANTITY NUMBER,
62 ERROR_CODE NUMBER,
63 error_message varchar2(2000)
64 );
65
66 -- Start of changes by debadey
67 -------------------------------
68 -- Define records and tables --
69 -------------------------------
70 -- Record Structure for Inventory Material Transfers
71 TYPE Ahl_Mtltfr_Rec_Type IS RECORD (
72 inventory_item_id NUMBER,
73 revision VARCHAR2(3),
74 source_org_id NUMBER,
75 source_subinv_name VARCHAR2(10),
76 source_locator_id NUMBER,
77 dest_org_id NUMBER,
78 dest_subinv_name VARCHAR2(10),
79 dest_locator_id NUMBER,
80 quantity NUMBER, -- quantity for serial item is always 1
81 uom VARCHAR2(3),
82 serial_number VARCHAR2(30),
83 lot_number VARCHAR2(80),
84 shipment_number VARCHAR2(30)
85 );
86
87 -- Table structure to be used in case multi select in UI
88 TYPE Ahl_Mtltfr_Tbl_Type IS TABLE OF Ahl_Mtltfr_Rec_Type;
89
90 ----------------------------------------------
91 -- Define Table Type for records structures --
92 ----------------------------------------------
93 TYPE Mtl_Avl_Schld_Tbl IS TABLE OF Mtl_Avl_Schld_Rec INDEX BY BINARY_INTEGER;
94 TYPE number_table_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
95 TYPE SUPPLY_TBL_TYPE IS TABLE OF SUPPLY_REC_TYPE INDEX BY BINARY_INTEGER;
96 type aog_tbl_type is table of aog_rec_type index by binary_integer;
97 type Inv_Mtl_Avl_Tbl is table of Inv_Mtl_Avl_Rec index by binary_integer;
98
99 ---------------------------------------------------------------------------------------------------------
100 -- Declare Procedures --
101 ---------------------------------------------------------------------------------------------------------
102 -- Start of Comments --
103 -- Procedure name : Check_Availability
104 -- Type : Private
105 -- Function : This procedure calls ATP to check if inventory item is available
106 -- for the cMRO Workoder/Operaion demand.
107 -- Pre-reqs :
108 -- Parameters :
109 --
110 -- Standard OUT Parameters :
111 -- x_return_status OUT VARCHAR2 Required
112 --
113 -- Check_Material_Aval Parameters :
114 -- p_schedule_material_id_tbl : Table containing Schedule material requirement id of the demand.
115 -- x_mtl_avl_schld_tbl : AHL_MM_MTL_MGT_PVT.Mtl_Avl_Schld_Rec
116 --
117 PROCEDURE CHECK_AVAILABILITY(
118 p_api_version IN NUMBER,
119 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
120 p_commit IN VARCHAR2 := FND_API.g_false,
121 p_module_type IN VARCHAR2 := NULL,
122 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
123 p_schedule_material_id_tbl IN number_table_type,
124 X_MTL_AVL_SCHLD_TBL OUT NOCOPY AHL_MM_MTL_MGT_PVT.MTL_AVL_SCHLD_TBL,
125 x_return_status OUT NOCOPY VARCHAR2,
126 X_MSG_COUNT OUT NOCOPY NUMBER,
127 x_msg_data OUT NOCOPY VARCHAR2 );
128 ---------------------------------------------------------------------------------------------------------
129 -- Declare Procedures --
130 ---------------------------------------------------------------------------------------------------------
131 -- Start of Comments --
132 -- Procedure name : Schedule_Materials
133 -- Type : Private
134 -- Function : This procedure calls ATP to schedule the material requirement
135 -- for the cMRO Workoder/Operaion demand.
136 -- Pre-reqs :
137 -- Parameters :
138 --
139 -- Standard OUT Parameters :
140 -- x_return_status OUT VARCHAR2 Required
141 --
142 -- Check_Material_Aval Parameters :
143 -- p_schedule_material_id_tbl : Table containing Schedule material requirement id of the demand.
144 -- x_mtl_avl_schld_tbl : Table containing output against schedule material req id
145 --
146 PROCEDURE SCHEDULE_MATERIALS(
147 p_api_version IN NUMBER,
148 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
149 p_commit IN VARCHAR2 := FND_API.g_false,
150 P_MODULE_TYPE IN VARCHAR2 := NULL,
151 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
152 p_schedule_material_id_tbl IN number_table_type,
153 X_MTL_AVL_SCHLD_TBL OUT NOCOPY AHL_MM_MTL_MGT_PVT.MTL_AVL_SCHLD_TBL,
154 x_return_status OUT NOCOPY VARCHAR2,
155 X_MSG_COUNT OUT NOCOPY NUMBER,
156 x_msg_data OUT NOCOPY VARCHAR2 );
157 ---------------------------------------------------------------------------------------------------------
158 -- Declare Procedures --
159 ---------------------------------------------------------------------------------------------------------
160 -- Start of Comments --
161 -- Procedure name : Schedule_Visit_Materials
162 -- Type : Public
163 -- Function : This procedure calls ATP to schedule all material requirements in a visit.
164 --
165 -- Pre-reqs :
166 -- Parameters :
167 --
168 -- Standard IN Parameters :
169 -- p_api_version IN NUMBER Required
170 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
171 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
172 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
173 -- Based on this flag, the API will set the default attributes.
174 -- p_module_type In VARCHAR2 Default NULL
175 -- This will be null.
176 -- Standard OUT Parameters :
177 -- x_return_status OUT VARCHAR2 Required
178 -- x_msg_count OUT NUMBER Required
179 -- x_msg_data OUT VARCHAR2 Required
180 --
181 -- Schedule_Visit_Materials Parameters :
182 -- p_visit_id IN : Number,Required : Visit ID
183 -- x_mtl_avl_schld_tbl OUT : Table containing output against schedule material req id
184 --
185 PROCEDURE SCHEDULE_VISIT_MATERIALS(
186 P_API_VERSION IN NUMBER,
187 P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
188 P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
189 P_MODULE_TYPE IN VARCHAR2 := NULL,
190 P_VALIDATION_LEVEL IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
191 P_VISIT_ID IN NUMBER,
192 X_MTL_AVL_SCHLD_TBL OUT NOCOPY AHL_MM_MTL_MGT_PVT.MTL_AVL_SCHLD_TBL,
193 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
194 X_MSG_COUNT OUT NOCOPY NUMBER,
195 X_MSG_DATA OUT NOCOPY VARCHAR2 );
196
197 ---------------------------------------------------------------------------------------------------------
198 -- AJPRASAN:: Adding the following function for Available Date calculation of a demand
199 ---------------------------------------------------------------------------------------------------------
200 -- Procedure name : Get_Available_Date
201 -- Type : Public
202 -- Usage : This function goes through all reservations for a particular demand
203 -- and if the reservations can satisfy the total quantity of the demand,
204 -- this returns the latest available date among all reserved quantities
205 --
206 -- Parameters :
207 -- p_scheduled_material_id IN NUMBER
208 --
209 -- Return:
210 -- l_available_date DATE
211 --
212 FUNCTION Get_Available_Date (
213 p_scheduled_material_id IN NUMBER
214 ) RETURN DATE;
215
216
217 ---------------------------------------------------------------------------------------------------------
218 -- AJPRASAN:: Adding the following function for creating Move Orders
219 ---------------------------------------------------------------------------------------------------------
220 -- Procedure name : Create_Move_Order
221 -- Type : Public
222 -- Usage : This function create a move order for the serial numbers passed in p_supply_tbl_type.
223 -- It creates and allocates the proper serial numbers & returns all the MO headers.
224 -- Later the user can go to the forms UI & perform the transaction.
225 --
226 -- IN Parameters :
227 -- p_scheduled_material_id NUMBER, Required
228 -- p_supply_tbl_type Serial_Tbl_Type, Required: The table containing the list of
229 -- supply serials and their location
230 --
231 -- OUT Parameters :
232 -- x_mo_hdr_id number, containing MO header ID with which the user can transact later
233 -- x_mo_lines_tbl number_table_type, containing the list of all line IDs for MOs
234 --
235
236 PROCEDURE CREATE_MOVE_ORDER(
237 p_api_version IN NUMBER,
238 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
239 p_commit IN VARCHAR2 := FND_API.G_FALSE,
240 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
241
242 p_scheduled_material_id IN NUMBER,
243 p_supply_tbl_type IN Supply_Tbl_Type,
244 x_mo_hdr_id OUT NOCOPY NUMBER,
245 x_mo_lines_tbl OUT NOCOPY number_table_type,
246
247 x_return_status OUT NOCOPY VARCHAR2,
248 x_msg_count OUT NOCOPY NUMBER,
249 x_msg_data OUT NOCOPY VARCHAR2
250 );
251
252 ---------------------------------------------------------------------------------------------------------
253 -- AJPRASAN:: Adding the following function for Flagging Requirements for ATP Collections
254 ---------------------------------------------------------------------------------------------------------
255 -- Procedure name : UPDATE_REQUIREMENTS_FOR_GOP
256 -- Type : Public
257 -- Usage : This procedure is utilized by the concurrent program (AHLATPFLG),
258 -- to flag the material requirements falling within the planning window
259 -- defined by the user. It will mark those demands so as to facilitate the
260 -- collection of the same by ATP. Later it will call schedule material API to
261 -- schedule those demands.
262 --
263 -- OUT Parameters :
264 -- Errbuf OUT NOCOPY VARCHAR2
265 -- Retcode OUT NOCOPY NUMBER
266 --
267
268 PROCEDURE UPDATE_REQUIREMENTS_FOR_GOP(
269 Errbuf OUT NOCOPY VARCHAR2,
270 Retcode OUT NOCOPY NUMBER
271 );
272
273 -------------------------------------------------------------------------------------------------------------
274 -- sukhwsin:: Added the following procedure for for sending workflow notification when AOG flag gets updated
275 -------------------------------------------------------------------------------------------------------------
276 -- Procedure name : Invoke_AOG_Upd_Notification
277 -- Type : Public
278 -- Usage : This procedure is called from variour places where AOG flag gets updated. It is used to
279 -- send workflow notifications upon updation of AOG flag.
280 -- IN Parameters :
281 -- p_api_version IN NUMBER Required
285 -- p_asm_id_tab IN NUMBER Required.
282 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
283 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
284 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
286 --
287 -- OUT Parameters :
288 -- x_return_status OUT VARCHAR2 Required
289 -- x_msg_count OUT NUMBER Required
290 -- x_msg_data OUT VARCHAR2 Required
291 --
292
293 PROCEDURE Invoke_AOG_Upd_Notification(
294 p_api_version IN NUMBER := 1.0,
295 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
296 p_commit IN VARCHAR2 := FND_API.G_FALSE,
297 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
298 p_asm_id_tab IN AHL_MM_MTL_MGT_PVT.number_table_type,
299 x_return_status OUT NOCOPY VARCHAR2,
300 x_msg_count OUT NOCOPY NUMBER,
301 x_msg_data OUT NOCOPY VARCHAR2
302 );
303
304 --changes by debadey starts
305 -----------------------
306 -- Define procedures --
307 -----------------------
308 -- Start of Comments --
309 --
310 -- Procedure name : Perform_Inter_Org_Transfer
311 -- Type : Private
312 -- Description : This procedure is called from the UI on selecting the option to do inter org transfer. It will perform reservation of the
313 -- item instances after transferring
314 -- Pre-reqs :
315 --
316 -- Standard IN Parameters :
317 -- p_api_version NUMBER := 1.0
318 -- p_init_msg_list VARCHAR2 := FND_API.G_FALSE
319 -- p_commit VARCHAR2 := FND_API.G_FALSE
320 -- p_validation_level NUMBER := FND_API.G_VALID_LEVEL_FULL
321 -- p_default VARCHAR2 := FND_API.G_FALSE
322 -- p_module_type VARCHAR2 := NULL
323 --
324 -- Standard OUT Parameters :
325 -- p_x_return_status VARCHAR2 Required
326 -- p_x_msg_count NUMBER Required
327 -- p_x_msg_data VARCHAR2 Required
328 --
329 --
330 -- Procedure IN, OUT, IN/OUT params :
331 -- p_x_ahl_mtltfr_rec Ahl_Mtltfr_Rec_Type IN Required
332 -- This record type variable will store all required information regarding the transfer
333 -- p_x_reservation_id NUMBER OUT
334 -- This variable will store the id for the reservation made, if any
335 -- p_x_sched_material_id NUMBER IN
336 -- This variable value needs to be fetched from the demand section and is used for reservation
337 -- Version :
338 -- Initial Version 1.0
339 --
340 -- End of Comments --
341 PROCEDURE Perform_Inter_Org_Transfer
342 (
343 -- Standard IN params
344 p_api_version IN NUMBER := 1.0,
345 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
346 p_commit IN VARCHAR2 := FND_API.G_FALSE,
347 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
348 p_default IN VARCHAR2 := FND_API.G_FALSE,
349 p_module_type IN VARCHAR2 := NULL,
350 -- Standard OUT params
351 x_return_status OUT NOCOPY VARCHAR2,
352 x_msg_count OUT NOCOPY NUMBER,
353 x_msg_data OUT NOCOPY VARCHAR2,
354 -- Procedure IN, OUT, IN/OUT params
355 p_x_mtltfr_tbl IN OUT NOCOPY Ahl_Mtltfr_Tbl_Type,
356 p_sched_material_id IN NUMBER,-- This is required for reservation API invocation
357 x_reservation_id OUT NOCOPY NUMBER -- Only one reservation will be made even for multiple item selection
358 -- Assumption is that the user will be allowed to select multiple serials/lots of only ONE item
359 -- and after inter org transfer all the item instances will reside in the same visit sub-inv and locator
360 );
361
362
363 -----------------------
364 -- Define procedures --
365 -----------------------
366 -- Start of Comments --
367 --
368 -- Procedure name : Perform_Inter_Org_Transfer_Inv
369 -- Type : Private
370 -- Description : This procedure is called for the actual transfer in inventory.
371 -- Pre-reqs :
372 --
373 -- Standard IN Parameters :
374 -- p_api_version NUMBER := 1.0
375 -- p_init_msg_list VARCHAR2 := FND_API.G_FALSE
376 -- p_commit VARCHAR2 := FND_API.G_FALSE
377 -- p_validation_level NUMBER := FND_API.G_VALID_LEVEL_FULL
378 -- p_default VARCHAR2 := FND_API.G_FALSE
379 -- p_module_type VARCHAR2 := NULL
380 --
381 -- Standard OUT Parameters :
382 -- x_return_status VARCHAR2 Required
383 -- x_msg_count NUMBER Required
384 -- x_msg_data VARCHAR2 Required
385 --
386 --
387 -- Procedure IN, OUT, IN/OUT params :
388 -- p_x_ahl_mtltfr_rec Ahl_Mtltfr_Rec_Type IN Required
389 -- This record type variable will store all required information regarding the transfer
390 -- Version :
391 -- Initial Version 1.0
392 --
393 -- End of Comments --
397 p_api_version IN NUMBER := 1.0,
394 PROCEDURE Perform_Inter_Org_Transfer_Inv
395 (
396 -- Standard IN params
398 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
399 p_commit IN VARCHAR2 := FND_API.G_FALSE,
400 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
401 p_default IN VARCHAR2 := FND_API.G_FALSE,
402 p_module_type IN VARCHAR2 := NULL,
403 -- Standard OUT params
404 x_return_status OUT NOCOPY VARCHAR2,
405 x_msg_count OUT NOCOPY NUMBER,
406 x_msg_data OUT NOCOPY VARCHAR2,
407 -- Procedure IN, OUT, IN/OUT params
408 p_x_mtltfr_tbl IN OUT NOCOPY Ahl_Mtltfr_Tbl_Type
409 );
410 -- end of changes by debadey
411
412 -- changes by sansatpa begins
413
414 G_LEVEL_PROCEDURE NUMBER := FND_LOG.LEVEL_PROCEDURE;
415 G_RUNTIME_LEVEL NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
416 G_RET_STS_SUCCESS VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
417 G_RET_STS_ERROR varchar2(1) := FND_API.G_RET_STS_ERROR;
418
419
420 ---------------------------------------------------------------------------------------------------------
421 -- SANSATPA:: Adding the following procedure for creating Internal Sales Orders
422 ---------------------------------------------------------------------------------------------------------
423 -----------------------
424 -- Define procedures --
425 -----------------------
426 -- Start of Comments --
427 --
428 -- Procedure name : CREATE_INTERNAL_SALES_ORDER
429 -- Type : Private
430 -- Description : This procedure is called for creating Internal Sales Orders.
431 -- Pre-reqs :
432 --
433 -- Standard IN Parameters :
434 -- p_api_version NUMBER := 1.0
435 -- p_init_msg_list VARCHAR2 := FND_API.G_FALSE
436 -- p_commit VARCHAR2 := FND_API.G_FALSE
437 -- p_validation_level NUMBER := FND_API.G_VALID_LEVEL_FULL
438 --
439 -- Standard OUT Parameters :
440 -- x_return_status VARCHAR2 Required
441 -- x_msg_count NUMBER Required
442 -- x_msg_data VARCHAR2 Required
443 --
444 --
445 -- Procedure -- IN Parameters :
446 -- P_SCH_MTL_ID NUMBER, Required
447 -- P_SOURCE_ORG_TBL OAF_NUMBER_TABLE, Required: The table containing the list of supply Org IDs
448 -- P_SOURCE_SUB_INV_TBL OAF_VARCHAR2_TABLE_100, Optional
449 -- P_SOURCE_LOCATORS_TBL OAF_NUMBER_TABLE, Optional
450 -- P_SERIAL_NUM_TBL OAF_VARCHAR2_TABLE_100, Required: The table containing the list of serial numbers
451 --
452 -- Procedure -- OUT Parameters :
453 -- X_REQUISITION_NUM VARCHAR2: The Internal Requisition Number
454 -- X_REQUISITION_HDR_ID NUMBER: The Internal Requisition Header Id
455 -- X_INT_ORDER_NUM NUMBER: The Internal Order Number
456 -- X_INT_ORDER_HDR_ID NUMBER: The Internal Order Header Id
457 -- X_RESERVATION_ID NUMBER: The Reservation Id
458 --
459 -- End of Comments --
460 PROCEDURE CREATE_INTERNAL_SALES_ORDER(
461 -- Standard IN params
462 P_API_VERSION IN NUMBER,
463 P_INIT_MSG_LIST IN VARCHAR2 DEFAULT FND_API.G_FALSE,
464 P_COMMIT IN VARCHAR2 DEFAULT FND_API.G_FALSE,
465 P_VALIDATION_LEVEL IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
466 -- Standard OUT params
467 X_MSG_COUNT OUT NOCOPY NUMBER,
468 X_MSG_DATA OUT NOCOPY VARCHAR2,
469 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
470 -- Procedure IN, OUT, IN/OUT params
471 P_SCH_MTL_ID IN NUMBER,
472 P_SOURCE_ORG_TBL IN OAF_NUMBER_TABLE,
473 P_SOURCE_SUB_INV_TBL IN OAF_VARCHAR2_TABLE_100,
474 P_SOURCE_LOCATORS_TBL IN OAF_NUMBER_TABLE,
475 P_SERIAL_NUM_TBL IN OAF_VARCHAR2_TABLE_100,
476 X_REQUISITION_NUM OUT NOCOPY VARCHAR2,
477 X_REQUISITION_HDR_ID OUT NOCOPY NUMBER,
478 X_INT_ORDER_NUM OUT NOCOPY NUMBER,
479 X_INT_ORDER_HDR_ID OUT NOCOPY NUMBER,
480 X_RESERVATION_ID OUT NOCOPY NUMBER );
481
482
483 -- changes by sansatpa ends
484
485
486 -------------------------------------------------------------------------------------------------------------
487 -- ARUNJK:: Added the following procedure for for sending workflow notification when AOG flag gets updated
488 -------------------------------------------------------------------------------------------------------------
489 -- Procedure name : Invoke_AOG_Upd_Notification
490 -- Type : Public
491 -- Usage : This procedure is called from variour places where AOG flag gets updated.
492 -- Invokes Invoke_AOG_Upd_Notification to send notification and AhlPrdWorkorderPvt.updateWoAogStatus
493 -- to update workorder status to AOG
494 -- IN Parameters :
495 -- p_api_version IN NUMBER Required
496 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
497 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
498 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
499 -- P_SCHEDULE_MATERIAL_ID_TBL IN Table of NUMBER containing ASM ID Required.
500 --
501 -- OUT Parameters :
502 -- x_return_status OUT VARCHAR2 Required
503 -- x_msg_count OUT NUMBER Required
504 -- x_msg_data OUT VARCHAR2 Required
505 --
506
507 PROCEDURE AOG_UPD_NOTIFICATION(
511 P_VALIDATION_LEVEL IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
508 P_API_VERSION IN NUMBER := 1.0,
509 P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
510 P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
512 P_SCHEDULE_MATERIAL_ID_TBL IN NUMBER_TABLE_TYPE,
513 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
514 X_MSG_COUNT OUT NOCOPY NUMBER,
515 X_MSG_DATA OUT NOCOPY VARCHAR2
516 );
517
518 -------------------------------------------------------------------------------------------------------------
519 -- ARUNJK:: Added the following procedure for for sending workflow notification when AOG flag gets updated
520 -------------------------------------------------------------------------------------------------------------
521 -- Procedure name : UPDATE_AOG
522 -- Type : Public
523 -- Usage : This procedure is called to update AOG flag
524 -- IN Parameters :
525 -- p_api_version IN NUMBER Required
526 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
527 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
528 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
529 -- P_AOG_TBL IN AOG_TBL_TYPE Required.
530 --
531 -- OUT Parameters :
532 -- x_return_status OUT VARCHAR2 Required
533 -- x_msg_count OUT NUMBER Required
534 -- x_msg_data OUT VARCHAR2 Required
535 --
536
537 PROCEDURE UPDATE_AOG(
538 P_API_VERSION IN NUMBER := 1.0,
539 P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
540 P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
541 P_VALIDATION_LEVEL IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
542 P_AOG_TBL IN AOG_TBL_TYPE,
543 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
544 X_MSG_COUNT OUT NOCOPY NUMBER,
545 X_MSG_DATA OUT NOCOPY VARCHAR2
546 );
547
548 /*PROCEDURE PICK_RELEASE(
549 P_API_VERSION IN NUMBER,
550 P_INIT_MSG_LIST IN VARCHAR2 DEFAULT FND_API.G_FALSE,
551 P_COMMIT IN VARCHAR2 DEFAULT FND_API.G_FALSE,
552 P_ORDER_HEADER_ID IN NUMBER, -- the ISO header is
553 P_ORDER_LINE_ID IN NUMBER, -- the iso line id
554 P_FM_SERIAL_NUM_TBL IN OAF_VARCHAR2_TABLE_100,
555 P_TO_SERIAL_NUM_TBL IN OAF_VARCHAR2_TABLE_100,
556 P_QUANTITY_TBL IN OAF_NUMBER_TABLE, -- The total quantity of serials present is serial tables passed
557 P_PICKING_RULE_ID IN NUMBER DEFAULT NULL, -- If not pass the required details
558 P_SOURCE_LOCATOR_TBL IN OAF_NUMBER_TABLE DEFAULT NULL, -- the supply Locator
559 P_SOURCE_SUB_INV_TBL IN OAF_VARCHAR2_TABLE_100 DEFAULT NULL, -- the Supply Sub inventory
560 P_PICK_FROM_ORG IN NUMBER DEFAULT NULL, -- the supply org
561 X_MSG_COUNT OUT NOCOPY NUMBER,
562 X_MSG_DATA OUT NOCOPY VARCHAR2,
563 X_RETURN_STATUS OUT NOCOPY VARCHAR2 );*/
564
565 ---------------------------------------------------------------------------------------------------------
566 -- AJPRASAN:: Adding the following function for filtering instances which satisfy build goal requirements
567 ---------------------------------------------------------------------------------------------------------
568 -- Procedure name : FILTER_INSTANCES_MATCHING_BG
569 -- Type : Public
570 -- Usage : This function does two things
571 -- 1. Call an API to populate a temporary table with the instances which are suitable
572 -- to be installed in the given position.
573 -- 2. Delete those instances from the table, which do not satisfy the
574 -- build goal requirements.
575 --
576 -- IN Parameters :
577 -- P_MODULE_TYPE IN VARCHAR2 -- Can be 'INVENTORY', 'ER', 'WO2WO', 'CNB'
578 -- p_demand_Record IN AHL_MM_MTL_MGT_PVT.BG_Demand_Rec
579 --
580
581 PROCEDURE FILTER_INSTANCES_MATCHING_BG(
582 p_api_version IN NUMBER,
583 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
584 p_commit IN VARCHAR2 := FND_API.G_FALSE,
585 p_module_type IN VARCHAR2,
586 p_demand_Record IN AHL_MM_MTL_MGT_PVT.BG_Demand_Rec,
587 x_return_status OUT NOCOPY VARCHAR2,
588 x_msg_count OUT NOCOPY NUMBER,
589 x_msg_data OUT NOCOPY VARCHAR2
590 );
591
592 ---------------------------------------------------------------------------------------------------------
593 -- ARUNJK:: Adding the following Procedure for Available Quantity based on Inventory Item Id and Organization Id
594 ---------------------------------------------------------------------------------------------------------
595 -- Function name : Get_Available_Quantity
596 -- Type : Public
597 -- Usage : This function accepts the Inventory Item Id and work order id and
598 -- returns the Availabile quantity of the given Item
599 --
600 -- Parameters :
601 -- P_INVENTORY_ITEM_ID IN NUMBER
602 -- P_ORG_ID IN NUMBER
603 --
604 -- Return:
605 -- X_Inv_Mtl_Avl_Tbl AHL_MM_MTL_MGT_PVT.Inv_Mtl_Avl_Tbl
606 --
607 PROCEDURE GET_AVAILABLE_QUANTITY(
608 P_API_VERSION IN NUMBER,
609 P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
610 P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
611 P_MODULE_TYPE IN VARCHAR2 := NULL,
612 P_VALIDATION_LEVEL IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
613 P_ORG_ID NUMBER,
614 P_INV_ITEM_ID NUMBER,
618 x_msg_data OUT NOCOPY VARCHAR2);
615 X_Inv_Mtl_Avl_Tbl OUT NOCOPY AHL_MM_MTL_MGT_PVT.Inv_Mtl_Avl_Tbl,
616 x_return_status OUT NOCOPY VARCHAR2,
617 X_MSG_COUNT OUT NOCOPY NUMBER,
619
620 -- Added by Kasridha : Passing table of visit IDs for check visit schedule materials API
621 TYPE visit_ids_Tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
622
623
624 -- Kasridha: Added for Marshalling Phase II Begins
625 ---------------------------------------------------------------------------------------------------------
626 -- Start of Comments --
627 -- Procedure name : Schedule_All_Visit_Materials
628 -- Type : Public
629 -- Function : This procedure calls ATP to schedule all material requirements for the selected visits.
630 --
631 -- Pre-reqs :
632 -- Parameters :
633 --
634 -- Standard IN Parameters :
635 -- p_api_version IN NUMBER Required
636 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
637 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
638 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
639 -- Based on this flag, the API will set the default attributes.
640 -- p_module_type In VARCHAR2 Default NULL
641 -- This will be null.
642 -- Standard OUT Parameters :
643 -- x_return_status OUT VARCHAR2 Required
644 -- x_msg_count OUT NUMBER Required
645 -- x_msg_data OUT VARCHAR2 Required
646 --
647 -- SCHED_ALL_VST_MTLS Parameters :
648 -- p_visit_ids IN : Number,Required : Visit ID
649 -- x_mtl_avl_schld_tbl OUT : Table containing output against schedule material req id
650 --
651 PROCEDURE SCHED_ALL_VST_MTLS(
652 P_API_VERSION IN NUMBER,
653 P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
654 P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
655 P_MODULE_TYPE IN VARCHAR2 := NULL,
656 P_VALIDATION_LEVEL IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
657 P_VISIT_IDS IN AHL_MM_MTL_MGT_PVT.visit_ids_Tbl,
658 X_MTL_AVL_SCHLD_TBL OUT NOCOPY AHL_MM_MTL_MGT_PVT.MTL_AVL_SCHLD_TBL,
659 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
660 X_MSG_COUNT OUT NOCOPY NUMBER,
661 X_MSG_DATA OUT NOCOPY VARCHAR2 );
662
663 -- Kasridha: Added for Marshalling Phase II Ends
664 END AHL_MM_MTL_MGT_PVT;
[Home] [Help]