1 PACKAGE AHL_PP_MATERIALS_PVT AUTHID CURRENT_USER AS
2 /* $Header: AHLVPPMS.pls 120.3.12020000.2 2012/12/07 11:39:41 sareepar ship $*/
3 --
4 ---------------------------------------------------------------------
5 -- Define Record Types for record structures needed by the APIs --
6 ---------------------------------------------------------------------
7 TYPE Req_Material_Rec_Type IS RECORD (
8 SCHEDULE_MATERIAL_ID NUMBER ,
9 OBJECT_VERSION_NUMBER NUMBER ,
10 INVENTORY_ITEM_ID NUMBER ,
11 SCHEDULE_DESIGNATOR VARCHAR2(30) ,
12 VISIT_ID NUMBER ,
13 VISIT_START_DATE DATE ,
14 VISIT_TASK_ID NUMBER ,
15 ORGANIZATION_ID NUMBER ,
16 SCHEDULED_DATE DATE ,
17 REQUEST_ID NUMBER ,
18 PROCESS_STATUS NUMBER ,
19 ERROR_MESSAGE VARCHAR2(120) ,
20 TRANSACTION_ID NUMBER ,
21 CONCATENATED_SEGMENTS VARCHAR2(40) ,
22 ITEM_DESCRIPTION VARCHAR2(240) ,
23 RT_OPER_MATERIAL_ID NUMBER ,
24 REQUESTED_QUANTITY NUMBER ,
25 REQUESTED_DATE DATE ,
26 UOM_CODE VARCHAR2(30) ,
27 UOM_MEANING VARCHAR2(30) ,
28 SCHEDULED_QUANTITY NUMBER ,
29 JOB_NUMBER VARCHAR2(80) ,
30 REQUIRED_QUANTITY NUMBER ,
31 QUANTITY_PER_ASSEMBLY NUMBER ,
32 WORKORDER_ID NUMBER ,
33 WIP_ENTITY_ID NUMBER ,
34 OPERATION_SEQUENCE NUMBER ,
35 OPERATION_CODE VARCHAR2(80) ,
36 ITEM_GROUP_ID NUMBER ,
37 SERIAL_NUMBER NUMBER ,
38 INSTANCE_ID NUMBER ,
39 SUPPLY_TYPE NUMBER ,
40 SUB_INVENTORY VARCHAR2(10) ,
41 LOCATION NUMBER ,
42 PROGRAM_ID NUMBER ,
43 PROGRAM_UPDATE_DATE DATE ,
44 LAST_UPDATED_DATE DATE ,
45 DESCRIPTION VARCHAR2(80) ,
46 DEPARTMENT_ID NUMBER ,
47 PROJECT_TASK_ID NUMBER ,
48 PROJECT_ID NUMBER ,
49 WORKORDER_OPERATION_ID NUMBER ,
50 STATUS VARCHAR2(30) ,
51 ATTRIBUTE_CATEGORY VARCHAR2(30) ,
52 ATTRIBUTE1 VARCHAR2(150) ,
53 ATTRIBUTE2 VARCHAR2(150) ,
54 ATTRIBUTE3 VARCHAR2(150) ,
55 ATTRIBUTE4 VARCHAR2(150) ,
56 ATTRIBUTE5 VARCHAR2(150) ,
57 ATTRIBUTE6 VARCHAR2(150) ,
58 ATTRIBUTE7 VARCHAR2(150) ,
59 ATTRIBUTE8 VARCHAR2(150) ,
60 ATTRIBUTE9 VARCHAR2(150) ,
61 ATTRIBUTE10 VARCHAR2(150) ,
62 ATTRIBUTE11 VARCHAR2(150) ,
63 ATTRIBUTE12 VARCHAR2(150) ,
64 ATTRIBUTE13 VARCHAR2(150) ,
65 ATTRIBUTE14 VARCHAR2(150) ,
66 ATTRIBUTE15 VARCHAR2(150) ,
67 MRP_NET_FLAG NUMBER,
68 NOTIFY_TEXT VARCHAR2(3000) ,
69 OPERATION_FLAG VARCHAR2(1) ,
70 REPAIR_ITEM VARCHAR2(1) ,
71 --sukhwsin: Added attributes for SB Effectivity - starts
72 MC_HEADER_ID NUMBER ,
73 MC_NAME VARCHAR2(80) ,
74 MC_POSITION_KEY NUMBER ,
75 MC_POSITION VARCHAR2(80) ,
76 ITEM_GROUP_NAME VARCHAR2(80) ,
77 --sukhwsin: Added attributes for SB Effectivity - ends
78 --debadey: Marshalling FDD section 5.15 -- added AOG flag -- 1/11/11
79 AOG_FLAG VARCHAR2(1)
80 );
81
82
83 TYPE Sch_Material_Rec_Type IS RECORD (
84 SCHEDULE_MATERIAL_ID NUMBER ,
85 OBJECT_VERSION_NUMBER NUMBER ,
86 INVENTORY_ITEM_ID NUMBER ,
87 CONCATENATED_SEGMENTS VARCHAR2(40) ,
88 ITEM_DESCRIPTION VARCHAR2(240) ,
89 RT_OPER_MATERIAL_ID NUMBER ,
90 REQUESTED_QUANTITY NUMBER ,
91 REQUEST_ID NUMBER ,
92 VISIT_ID NUMBER ,
93 VISIT_TASK_ID NUMBER ,
94 ORGANIZATION_ID NUMBER ,
95 REQUESTED_DATE DATE ,
96 UOM VARCHAR2(30) ,
97 SCHEDULED_QUANTITY NUMBER ,
98 SCHEDULED_DATE DATE ,
99 PROCESS_STATUS NUMBER ,
100 JOB_NUMBER VARCHAR2(30) ,
101 WORKORDER_ID NUMBER ,
102 OPERATION_SEQUENCE NUMBER ,
103 ITEM_GROUP_ID NUMBER ,
104 SERIAL_NUMBER NUMBER ,
105 SUB_INVENTORY VARCHAR2(10) ,
106 LOCATION NUMBER ,
107 LOCATION_DESC VARCHAR2(50)
108 );
109
110 ----------------------------------------------
111 -- Define Table Type for records structures --
112 ----------------------------------------------
113 TYPE Req_Material_Tbl_Type IS TABLE OF Req_Material_Rec_Type INDEX BY BINARY_INTEGER;
114 TYPE Sch_Material_Tbl_Type IS TABLE OF Sch_Material_Rec_Type INDEX BY BINARY_INTEGER;
115
116 ------------------------
117 -- Declare Procedures --
118 ------------------------
119
120 -- Start of Comments --
121 -- Procedure name : Create_Material_Reqst
122 -- Type : Private(Called from Material transactions API or internally
123 -- called from process material request
124 -- Function : Validates Material Information and inserts records into
125 -- Schedule Material table for non routine jobs and loads record
126 -- into MRP_SCHEDULE_INTERFACE table to Launche Concurrent Program to
127 -- initiate material reservation
128 --
129 -- Pre-reqs :
130 -- Parameters :
131 --
132 -- Standard IN Parameters :
133 -- p_api_version IN NUMBER Required
134 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
135 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
136 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
137 -- p_module_type IN VARCHAR2 Default NULL
138 -- p_interface_flag IN VARCHAR2 Required
139 --
140 -- Standard OUT Parameters :
141 -- x_return_status OUT VARCHAR2 Required
142 -- x_msg_count OUT NUMBER Required
143 -- x_msg_data OUT VARCHAR2 Required
144 --
145 -- Create Material Request Parameters:
146 -- p_x_req_material_tbl IN OUT NOCOPY Req_Material_Tbl_Type,
147 -- Contains item information to perform material reservation
148 --
149 -- Version :
150 -- Initial Version 1.0
151 -- MANESING::NR Analysis, 03-Jun-2011, modified API signature to include IN parameter p_module_type
152 --
153 -- End of Comments.
154
155 PROCEDURE Create_Material_Reqst (
156 p_api_version IN NUMBER,
157 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
158 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
159 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
160 p_module_type IN VARCHAR2 := NULL,
161 p_interface_flag IN VARCHAR2,
162 p_x_req_material_tbl IN OUT NOCOPY Req_Material_Tbl_Type,
163 x_job_return_status OUT NOCOPY VARCHAR2,
164 x_return_status OUT NOCOPY VARCHAR2,
165 x_msg_count OUT NOCOPY NUMBER,
166 x_msg_data OUT NOCOPY VARCHAR2
167 );
168
169
170 -- Start of Comments --
171 -- Procedure name : Process_Material_Request
172 -- Type : Private
173 -- Function : Manages Create/Modify/Delete material requirements for routine and
174 -- non routine operations associated to a job.
175 -- Pre-reqs :
176 -- Parameters :
177 --
178 -- Standard IN Parameters :
179 -- p_api_version IN NUMBER Required
180 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
181 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
182 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
183 -- Based on this flag, the API will set the default attributes.
184 -- p_module_type In VARCHAR2 Default NULL
185 -- This will be null.
186 -- Standard OUT Parameters :
187 -- x_return_status OUT VARCHAR2 Required
188 -- x_msg_count OUT NUMBER Required
189 -- x_msg_data OUT VARCHAR2 Required
190 --
191 -- Process_Material Parameters :
192 -- p_x_req_material_tbl IN OUT Ahl_Pp_Material_Pvt.Req_Material_Tbl_Type,Required
193 -- List of Required materials for a job
194 --
195
196 PROCEDURE Process_Material_Request (
197 p_api_version IN NUMBER,
198 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
199 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
200 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
201 p_module_type IN VARCHAR2 := NULL,
202 p_x_req_material_tbl IN OUT NOCOPY AHL_PP_MATERIALS_PVT.Req_Material_Tbl_Type,
203 x_return_status OUT NOCOPY VARCHAR2,
204 x_msg_count OUT NOCOPY NUMBER,
205 x_msg_data OUT NOCOPY VARCHAR2
206
207 );
208 -- Start of Comments --
209 -- Procedure name : Process_Wo_Op_Materials
210 -- Type : Private
211 -- Function : Procedure to Process Requested materials defined at Route/Operation/Dispostion
212 --
213 -- Pre-reqs :
214 -- Parameters :
215 --
216 -- Standard IN Parameters :
217 -- p_api_version IN NUMBER Required
218 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
219 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
220 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
221 -- Based on this flag, the API will set the default attributes.
222 -- Standard OUT Parameters :
223 -- x_return_status OUT VARCHAR2 Required
224 -- x_msg_count OUT NUMBER Required
225 -- x_msg_data OUT VARCHAR2 Required
226 --
227 -- Process_Material Parameters :
228 -- p_prd_wooperation_tbl IN AHL_PRD_WORKORDER_PVT.Prd_Workoper_Tbl,
229 -- x_req_material_tbl OUT Ahl_Pp_Material_Pvt.Req_Material_Tbl_Type,Required
230 -- List of Required materials for a job
231 --
232
233 PROCEDURE Process_Wo_Op_Materials (
234 p_api_version IN NUMBER,
235 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
236 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
237 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
238 p_operation_flag IN VARCHAR2,
239 p_prd_wooperation_tbl IN AHL_PRD_OPERATIONS_PVT.Prd_Operation_Tbl,
240 x_req_material_tbl OUT NOCOPY Req_Material_Tbl_Type,
241 x_return_status OUT NOCOPY VARCHAR2,
242 x_msg_count OUT NOCOPY NUMBER,
243 x_msg_data OUT NOCOPY VARCHAR2
244 );
245
246 -- Function to get mrp net value
247 FUNCTION Get_Mrp_Net
248 (p_schedule_material_id IN NUMBER,
249 p_item_desc IN VARCHAR2) RETURN VARCHAR2;
250 --
251 FUNCTION GET_QTY_PER_ASBLY
252 (p_schedule_material_id IN NUMBER,
253 p_item_desc IN VARCHAR2 ) RETURN NUMBER;
254 --
255 -- Start of Comments --
256 -- Procedure name : Log_Transaction_Record
257 -- Type : Private
258 -- Function : Writes the details about a transaction in the Log Table
259 -- AHL_WO_OPERATION_TXNS
260 -- Pre-reqs :
261 -- Parameters :
262 --
263 -- Log_Transaction Parameters:
264 -- p_trans_type_code IN VARCHAR2 Required
265 -- p_load_type_code IN NUMBER Required
266 -- p_transaction_type_code IN NUMBER Required
267 -- p_workorder_operation_id IN NUMBER Default NULL,
268 -- p_operation_resource_id IN NUMBER Default NULL,
269 -- p_schedule_material_id IN NUMBER Default NULL,
270 -- p_bom_resource_id IN NUMBER Default NULL,
271 -- p_cost_basis_code IN NUMBER Default NULL,
272 -- p_total_required IN NUMBER Default NULL,
273 -- p_assigned_units IN NUMBER Default NULL,
274 -- p_autocharge_type_code IN NUMBER Default NULL,
275 -- p_standard_rate_flag_code IN NUMBER Default NULL,
276 -- p_applied_resource_units IN NUMBER Default NULL,
277 -- p_applied_resource_value IN NUMBER Default NULL,
278 -- p_inventory_item_id IN NUMBER Default NULL,
279 -- p_scheduled_quantity IN NUMBER Default NULL,
280 -- p_scheduled_date IN DATE Default NULL,
281 -- p_mrp_net_flag IN NUMBER Default NULL,
282 -- p_quantity_per_assembly IN NUMBER Default NULL,
283 -- p_required_quantity IN NUMBER Default NULL,
284 -- p_supply_locator_id IN NUMBER Default NULL,
285 -- p_supply_subinventory IN NUMBER Default NULL,
286 -- p_date_required IN DATE Default NULL,
287 -- p_operation_type_code IN VARCHAR2 Default NULL,
288 -- p_sched_start_date IN DATE Default NULL,
289 -- p_res_sched_end_date IN DATE Default NULL,
290 -- p_op_scheduled_start_date IN DATE Default NULL,
291 -- p_op_scheduled_end_date IN DATE Default NULL,
292 -- p_op_actual_start_date IN DATE Default NULL,
293 -- p_op_actual_end_date IN DATE Default NULL,
294 -- p_attribute_category IN VARCHAR2 Default NULL,
295 -- p_attribute1 IN VARCHAR2 Default NULL
296 -- p_attribute2 IN VARCHAR2 Default NULL
300 -- p_attribute6 IN VARCHAR2 Default NULL
297 -- p_attribute3 IN VARCHAR2 Default NULL
298 -- p_attribute4 IN VARCHAR2 Default NULL
299 -- p_attribute5 IN VARCHAR2 Default NULL
301 -- p_attribute7 IN VARCHAR2 Default NULL
302 -- p_attribute8 IN VARCHAR2 Default NULL
303 -- p_attribute9 IN VARCHAR2 Default NULL
304 -- p_attribute10 IN VARCHAR2 Default NULL
305 -- p_attribute11 IN VARCHAR2 Default NULL
306 -- p_attribute12 IN VARCHAR2 Default NULL
307 -- p_attribute13 IN VARCHAR2 Default NULL
308 -- p_attribute14 IN VARCHAR2 Default NULL
309 -- p_attribute15 IN VARCHAR2 Default NULL
310 --
311 -- Version :
312 -- Initial Version 1.0
313 --
314 -- End of Comments.
315 PROCEDURE Log_Transaction_Record
316 ( p_wo_operation_txn_id IN NUMBER,
317 p_object_version_number IN NUMBER,
318 p_last_update_date IN DATE,
319 p_last_updated_by IN NUMBER,
320 p_creation_date IN DATE,
321 p_created_by IN NUMBER,
322 p_last_update_login IN NUMBER,
323 p_load_type_code IN NUMBER,
324 p_transaction_type_code IN NUMBER,
325 p_workorder_operation_id IN NUMBER := NULL,
326 p_operation_resource_id IN NUMBER := NULL,
327 p_schedule_material_id IN NUMBER := NULL,
328 p_bom_resource_id IN NUMBER := NULL,
329 p_cost_basis_code IN NUMBER := NULL,
330 p_total_required IN NUMBER := NULL,
331 p_assigned_units IN NUMBER := NULL,
332 p_autocharge_type_code IN NUMBER := NULL,
333 p_standard_rate_flag_code IN NUMBER := NULL,
334 p_applied_resource_units IN NUMBER := NULL,
335 p_applied_resource_value IN NUMBER := NULL,
336 p_inventory_item_id IN NUMBER := NULL,
337 p_scheduled_quantity IN NUMBER := NULL,
338 p_scheduled_date IN DATE := NULL,
339 p_mrp_net_flag IN NUMBER := NULL,
340 p_quantity_per_assembly IN NUMBER := NULL,
341 p_required_quantity IN NUMBER := NULL,
342 p_supply_locator_id IN NUMBER := NULL,
343 p_supply_subinventory IN NUMBER := NULL,
344 p_date_required IN DATE := NULL,
345 p_operation_type_code IN VARCHAR2 := NULL,
346 p_res_sched_start_date IN DATE := NULL,
347 p_res_sched_end_date IN DATE := NULL,
348 p_op_scheduled_start_date IN DATE := NULL,
349 p_op_scheduled_end_date IN DATE := NULL,
350 p_op_actual_start_date IN DATE := NULL,
351 p_op_actual_end_date IN DATE := NULL,
352 p_attribute_category IN VARCHAR2 := NULL,
353 p_attribute1 IN VARCHAR2 := NULL,
354 p_attribute2 IN VARCHAR2 := NULL,
355 p_attribute3 IN VARCHAR2 := NULL,
356 p_attribute4 IN VARCHAR2 := NULL,
357 p_attribute5 IN VARCHAR2 := NULL,
358 p_attribute6 IN VARCHAR2 := NULL,
359 p_attribute7 IN VARCHAR2 := NULL,
360 p_attribute8 IN VARCHAR2 := NULL,
361 p_attribute9 IN VARCHAR2 := NULL,
362 p_attribute10 IN VARCHAR2 := NULL,
363 p_attribute11 IN VARCHAR2 := NULL,
364 p_attribute12 IN VARCHAR2 := NULL,
365 p_attribute13 IN VARCHAR2 := NULL,
366 p_attribute14 IN VARCHAR2 := NULL,
367 p_attribute15 IN VARCHAR2 := NULL);
368 --
369 FUNCTION GET_ISSUED_QTY(P_ORG_ID IN NUMBER, P_ITEM_ID IN NUMBER,
370 P_WORKORDER_OP_ID IN NUMBER) RETURN NUMBER;
371 ---JKJAIN FP ER # 6436303------------------------------------------------------------
372 -- Function for returning net quantity of material available with
373 -- a workorder.
374 -- Net Total Quantity = Total Quantity Issued - Total quantity returned
375 -- Balaji added this function for OGMA ER # 5948868.
376 --------------------------------------------------------------------------------------
377 FUNCTION GET_NET_QTY(
378 P_ORG_ID IN NUMBER,
379 P_ITEM_ID IN NUMBER,
380 P_WORKORDER_OP_ID IN NUMBER
381 )
382 RETURN NUMBER;
383
384 -- Start of Comments --
385 -- Procedure name : Material_Notification
386 -- Type : Private
387 -- Function : Procedure to send material Notification when new item has been added
388 -- or quantity has been changed.
389 --
390 -- Pre-reqs :
391 -- Parameters :
392 --
393 -- Standard IN Parameters :
394 -- p_api_version IN NUMBER Required
395 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
396 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
397 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
398 -- Based on this flag, the API will set the default attributes.
399 -- Standard OUT Parameters :
400 -- x_return_status OUT VARCHAR2 Required
401 -- x_msg_count OUT NUMBER Required
402 -- x_msg_data OUT VARCHAR2 Required
403 --
407
404 -- Process_Material Notification Parameters :
405 -- p_Req_Material_Tbl IN Req_Material_Tbl_Type,
406 --
408 PROCEDURE Material_Notification
409 (
410 p_api_version IN NUMBER:= 1.0,
411 p_init_msg_list IN VARCHAR2,
412 p_commit IN VARCHAR2,
413 p_validation_level IN NUMBER,
414 p_Req_Material_Tbl IN Req_Material_Tbl_Type,
415 x_return_status OUT NOCOPY VARCHAR2,
416 x_msg_count OUT NOCOPY NUMBER,
417 x_msg_data OUT NOCOPY VARCHAR2
418 );
419
420 --
421 -- sukhwsin::Marshling enhancements - added following procedure for changing item on workorder material requirement.
422 -- Start of Comments --
423 -- Procedure name : Wo_Mat_Req_Item_Change
424 -- Type : Public
425 -- Usage : This procedure is used to change item for item group OR position based item requirements.
426 -- Parameters :
427 --
428 -- Standard IN Parameters :
429 -- p_api_version IN NUMBER Required
430 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
431 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
432 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
433 -- Standard OUT Parameters :
434 -- x_return_status OUT VARCHAR2 Required
435 -- x_msg_count OUT NUMBER Required
436 -- x_msg_data OUT VARCHAR2 Required
437 --
438 -- Process_Material Notification Parameters :
439 -- p_asm_id IN ahl_schedule_materials.schedule_material_id
440 -- p_alternate_item_id IN New Inventory_Item_Id which will replace old inventory item.
441
442 PROCEDURE Wo_Mat_Req_Item_Change(
443 p_api_version IN NUMBER,
444 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
445 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
446 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
447 p_module_type IN VARCHAR2 := NULL,
448 p_asm_id IN NUMBER,
449 p_alternate_item_id IN NUMBER,
450 x_return_status OUT NOCOPY VARCHAR2,
451 x_msg_count OUT NOCOPY NUMBER,
452 x_msg_data OUT NOCOPY VARCHAR2
453 );
454
455 END AHL_PP_MATERIALS_PVT;
[Home] [Help]