1 PACKAGE AHL_PP_MATERIALS_PVT AS
2 /* $Header: AHLVPPMS.pls 120.1.12010000.3 2008/11/19 06:00:59 jkjain 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 );
72
73 TYPE Sch_Material_Rec_Type IS RECORD (
74 SCHEDULE_MATERIAL_ID NUMBER ,
75 OBJECT_VERSION_NUMBER NUMBER ,
76 INVENTORY_ITEM_ID NUMBER ,
77 CONCATENATED_SEGMENTS VARCHAR2(40) ,
78 ITEM_DESCRIPTION VARCHAR2(240) ,
79 RT_OPER_MATERIAL_ID NUMBER ,
80 REQUESTED_QUANTITY NUMBER ,
81 REQUEST_ID NUMBER ,
82 VISIT_ID NUMBER ,
83 VISIT_TASK_ID NUMBER ,
84 ORGANIZATION_ID NUMBER ,
85 REQUESTED_DATE DATE ,
86 UOM VARCHAR2(30) ,
87 SCHEDULED_QUANTITY NUMBER ,
88 SCHEDULED_DATE DATE ,
89 PROCESS_STATUS NUMBER ,
90 JOB_NUMBER VARCHAR2(30) ,
91 WORKORDER_ID NUMBER ,
92 OPERATION_SEQUENCE NUMBER ,
93 ITEM_GROUP_ID NUMBER ,
94 SERIAL_NUMBER NUMBER ,
95 SUB_INVENTORY VARCHAR2(10) ,
96 LOCATION NUMBER ,
97 LOCATION_DESC VARCHAR2(50)
98 );
99
100 ----------------------------------------------
101 -- Define Table Type for records structures --
102 ----------------------------------------------
103 TYPE Req_Material_Tbl_Type IS TABLE OF Req_Material_Rec_Type INDEX BY BINARY_INTEGER;
104 TYPE Sch_Material_Tbl_Type IS TABLE OF Sch_Material_Rec_Type INDEX BY BINARY_INTEGER;
105
106 ------------------------
107 -- Declare Procedures --
108 ------------------------
109
110 -- Start of Comments --
111 -- Procedure name : Create_Material_Reqst
112 -- Type : Private(Called from Material transactions API or intrenally
113 -- called from process material request
114 -- Function : Validates Material Information and inserts records into
115 -- Schedule Material table for non routine jobs and loads record
116 -- into MRP_SCHEDULE_INTERFACE table to Launche Concurrent Program to
117 -- initiate material reservation
118 --
119 -- Pre-reqs :
120 -- Parameters :
121 --
122 -- Standard IN Parameters :
123 -- p_api_version IN NUMBER Required
124 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
125 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
126 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
127 -- p_interface_flag IN VARCHAR2 Required
128 -- p_called_module IN VARCHAR2 Default Null.
129 --
130 -- Standard OUT Parameters :
131 -- x_return_status OUT VARCHAR2 Required
132 -- x_msg_count OUT NUMBER Required
133 -- x_msg_data OUT VARCHAR2 Required
134 --
135 -- Create Material Request Parameters:
136 -- p_x_req_material_tbl IN OUT NOCOPY Req_Material_Tbl_Type,
137 -- Contains item information to perform material reservation
138 --
139 -- Version :
140 -- Initial Version 1.0
141 --
142 -- End of Comments.
143
144 PROCEDURE Create_Material_Reqst (
145 p_api_version IN NUMBER,
146 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
147 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
148 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
149 p_interface_flag IN VARCHAR2 ,
150 p_x_req_material_tbl IN OUT NOCOPY Req_Material_Tbl_Type,
151 x_job_return_status OUT NOCOPY VARCHAR2,
152 x_return_status OUT NOCOPY VARCHAR2,
153 x_msg_count OUT NOCOPY NUMBER,
154 x_msg_data OUT NOCOPY VARCHAR2
155 );
156
157
158 -- Start of Comments --
159 -- Procedure name : Process_Material_Request
160 -- Type : Private
161 -- Function : Manages Create/Modify/Delete material requirements for routine and
162 -- non routine operations associated to a job.
163 -- Pre-reqs :
164 -- Parameters :
165 --
166 -- Standard IN Parameters :
167 -- p_api_version IN NUMBER Required
168 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
169 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
170 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
171 -- Based on this flag, the API will set the default attributes.
172 -- p_module_type In VARCHAR2 Default NULL
173 -- This will be null.
174 -- Standard OUT Parameters :
175 -- x_return_status OUT VARCHAR2 Required
176 -- x_msg_count OUT NUMBER Required
177 -- x_msg_data OUT VARCHAR2 Required
178 --
179 -- Process_Material Parameters :
180 -- p_x_req_material_tbl IN OUT Ahl_Pp_Material_Pvt.Req_Material_Tbl_Type,Required
181 -- List of Required materials for a job
182 --
183
184 PROCEDURE Process_Material_Request (
185 p_api_version IN NUMBER,
186 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
187 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
188 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
189 p_module_type IN VARCHAR2 := NULL,
190 p_x_req_material_tbl IN OUT NOCOPY AHL_PP_MATERIALS_PVT.Req_Material_Tbl_Type,
191 x_return_status OUT NOCOPY VARCHAR2,
192 x_msg_count OUT NOCOPY NUMBER,
193 x_msg_data OUT NOCOPY VARCHAR2
194
195 );
196 -- Start of Comments --
197 -- Procedure name : Process_Wo_Op_Materials
198 -- Type : Private
199 -- Function : Procedure to Process Requested materials defined at Route/Operation/Dispostion
200 --
201 -- Pre-reqs :
202 -- Parameters :
203 --
204 -- Standard IN Parameters :
205 -- p_api_version IN NUMBER Required
206 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
207 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
208 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
209 -- Based on this flag, the API will set the default attributes.
210 -- Standard OUT Parameters :
211 -- x_return_status OUT VARCHAR2 Required
212 -- x_msg_count OUT NUMBER Required
213 -- x_msg_data OUT VARCHAR2 Required
214 --
215 -- Process_Material Parameters :
216 -- p_prd_wooperation_tbl IN AHL_PRD_WORKORDER_PVT.Prd_Workoper_Tbl,
217 -- x_req_material_tbl OUT Ahl_Pp_Material_Pvt.Req_Material_Tbl_Type,Required
218 -- List of Required materials for a job
219 --
220
221 PROCEDURE Process_Wo_Op_Materials (
222 p_api_version IN NUMBER,
223 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
224 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
225 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
226 p_operation_flag IN VARCHAR2,
227 p_prd_wooperation_tbl IN AHL_PRD_OPERATIONS_PVT.Prd_Operation_Tbl,
228 x_req_material_tbl OUT NOCOPY Req_Material_Tbl_Type,
229 x_return_status OUT NOCOPY VARCHAR2,
230 x_msg_count OUT NOCOPY NUMBER,
231 x_msg_data OUT NOCOPY VARCHAR2
232 );
233
234 -- Function to get mrp net value
235 FUNCTION Get_Mrp_Net
236 (p_schedule_material_id IN NUMBER,
237 p_item_desc IN VARCHAR2) RETURN VARCHAR2;
238 --
239 FUNCTION GET_QTY_PER_ASBLY
240 (p_schedule_material_id IN NUMBER,
241 p_item_desc IN VARCHAR2 ) RETURN NUMBER;
242 --
243 -- Start of Comments --
244 -- Procedure name : Log_Transaction_Record
245 -- Type : Private
246 -- Function : Writes the details about a transaction in the Log Table
247 -- AHL_WO_OPERATION_TXNS
248 -- Pre-reqs :
249 -- Parameters :
250 --
251 -- Log_Transaction Parameters:
252 -- p_trans_type_code IN VARCHAR2 Required
253 -- p_load_type_code IN NUMBER Required
254 -- p_transaction_type_code IN NUMBER Required
255 -- p_workorder_operation_id IN NUMBER Default NULL,
256 -- p_operation_resource_id IN NUMBER Default NULL,
257 -- p_schedule_material_id IN NUMBER Default NULL,
258 -- p_bom_resource_id IN NUMBER Default NULL,
259 -- p_cost_basis_code IN NUMBER Default NULL,
260 -- p_total_required IN NUMBER Default NULL,
261 -- p_assigned_units IN NUMBER Default NULL,
262 -- p_autocharge_type_code IN NUMBER Default NULL,
263 -- p_standard_rate_flag_code IN NUMBER Default NULL,
264 -- p_applied_resource_units IN NUMBER Default NULL,
265 -- p_applied_resource_value IN NUMBER Default NULL,
266 -- p_inventory_item_id IN NUMBER Default NULL,
267 -- p_scheduled_quantity IN NUMBER Default NULL,
268 -- p_scheduled_date IN DATE Default NULL,
269 -- p_mrp_net_flag IN NUMBER Default NULL,
270 -- p_quantity_per_assembly IN NUMBER Default NULL,
271 -- p_required_quantity IN NUMBER Default NULL,
272 -- p_supply_locator_id IN NUMBER Default NULL,
273 -- p_supply_subinventory IN NUMBER Default NULL,
274 -- p_date_required IN DATE Default NULL,
275 -- p_operation_type_code IN VARCHAR2 Default NULL,
276 -- p_sched_start_date IN DATE Default NULL,
277 -- p_res_sched_end_date IN DATE Default NULL,
278 -- p_op_scheduled_start_date IN DATE Default NULL,
279 -- p_op_scheduled_end_date IN DATE Default NULL,
280 -- p_op_actual_start_date IN DATE Default NULL,
281 -- p_op_actual_end_date IN DATE Default NULL,
282 -- p_attribute_category IN VARCHAR2 Default NULL,
283 -- p_attribute1 IN VARCHAR2 Default NULL
284 -- p_attribute2 IN VARCHAR2 Default NULL
285 -- p_attribute3 IN VARCHAR2 Default NULL
286 -- p_attribute4 IN VARCHAR2 Default NULL
287 -- p_attribute5 IN VARCHAR2 Default NULL
288 -- p_attribute6 IN VARCHAR2 Default NULL
289 -- p_attribute7 IN VARCHAR2 Default NULL
290 -- p_attribute8 IN VARCHAR2 Default NULL
291 -- p_attribute9 IN VARCHAR2 Default NULL
292 -- p_attribute10 IN VARCHAR2 Default NULL
293 -- p_attribute11 IN VARCHAR2 Default NULL
294 -- p_attribute12 IN VARCHAR2 Default NULL
295 -- p_attribute13 IN VARCHAR2 Default NULL
296 -- p_attribute14 IN VARCHAR2 Default NULL
297 -- p_attribute15 IN VARCHAR2 Default NULL
298 --
299 -- Version :
300 -- Initial Version 1.0
301 --
302 -- End of Comments.
303 PROCEDURE Log_Transaction_Record
304 ( p_wo_operation_txn_id IN NUMBER,
305 p_object_version_number IN NUMBER,
306 p_last_update_date IN DATE,
307 p_last_updated_by IN NUMBER,
308 p_creation_date IN DATE,
309 p_created_by IN NUMBER,
310 p_last_update_login IN NUMBER,
311 p_load_type_code IN NUMBER,
312 p_transaction_type_code IN NUMBER,
313 p_workorder_operation_id IN NUMBER := NULL,
314 p_operation_resource_id IN NUMBER := NULL,
315 p_schedule_material_id IN NUMBER := NULL,
316 p_bom_resource_id IN NUMBER := NULL,
317 p_cost_basis_code IN NUMBER := NULL,
318 p_total_required IN NUMBER := NULL,
319 p_assigned_units IN NUMBER := NULL,
320 p_autocharge_type_code IN NUMBER := NULL,
321 p_standard_rate_flag_code IN NUMBER := NULL,
322 p_applied_resource_units IN NUMBER := NULL,
323 p_applied_resource_value IN NUMBER := NULL,
324 p_inventory_item_id IN NUMBER := NULL,
325 p_scheduled_quantity IN NUMBER := NULL,
326 p_scheduled_date IN DATE := NULL,
327 p_mrp_net_flag IN NUMBER := NULL,
328 p_quantity_per_assembly IN NUMBER := NULL,
329 p_required_quantity IN NUMBER := NULL,
330 p_supply_locator_id IN NUMBER := NULL,
331 p_supply_subinventory IN NUMBER := NULL,
332 p_date_required IN DATE := NULL,
333 p_operation_type_code IN VARCHAR2 := NULL,
334 p_res_sched_start_date IN DATE := NULL,
335 p_res_sched_end_date IN DATE := NULL,
336 p_op_scheduled_start_date IN DATE := NULL,
337 p_op_scheduled_end_date IN DATE := NULL,
338 p_op_actual_start_date IN DATE := NULL,
339 p_op_actual_end_date IN DATE := NULL,
340 p_attribute_category IN VARCHAR2 := NULL,
341 p_attribute1 IN VARCHAR2 := NULL,
342 p_attribute2 IN VARCHAR2 := NULL,
343 p_attribute3 IN VARCHAR2 := NULL,
344 p_attribute4 IN VARCHAR2 := NULL,
345 p_attribute5 IN VARCHAR2 := NULL,
346 p_attribute6 IN VARCHAR2 := NULL,
347 p_attribute7 IN VARCHAR2 := NULL,
348 p_attribute8 IN VARCHAR2 := NULL,
349 p_attribute9 IN VARCHAR2 := NULL,
350 p_attribute10 IN VARCHAR2 := NULL,
351 p_attribute11 IN VARCHAR2 := NULL,
352 p_attribute12 IN VARCHAR2 := NULL,
353 p_attribute13 IN VARCHAR2 := NULL,
354 p_attribute14 IN VARCHAR2 := NULL,
355 p_attribute15 IN VARCHAR2 := NULL);
356 --
357 FUNCTION GET_ISSUED_QTY(P_ORG_ID IN NUMBER, P_ITEM_ID IN NUMBER,
358 P_WORKORDER_OP_ID IN NUMBER) RETURN NUMBER;
359 ---JKJAIN FP ER # 6436303------------------------------------------------------------
360 -- Function for returning net quantity of material available with
361 -- a workorder.
362 -- Net Total Quantity = Total Quantity Issued - Total quantity returned
363 -- Balaji added this function for OGMA ER # 5948868.
364 --------------------------------------------------------------------------------------
365 FUNCTION GET_NET_QTY(
366 P_ORG_ID IN NUMBER,
367 P_ITEM_ID IN NUMBER,
368 P_WORKORDER_OP_ID IN NUMBER
369 )
370 RETURN NUMBER;
371
372 -- Start of Comments --
373 -- Procedure name : Material_Notification
374 -- Type : Private
375 -- Function : Procedure to send material Notification when new item has been added
376 -- or quantity has been changed.
377 --
378 -- Pre-reqs :
379 -- Parameters :
380 --
381 -- Standard IN Parameters :
382 -- p_api_version IN NUMBER Required
383 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
384 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
385 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
386 -- Based on this flag, the API will set the default attributes.
387 -- Standard OUT Parameters :
388 -- x_return_status OUT VARCHAR2 Required
389 -- x_msg_count OUT NUMBER Required
390 -- x_msg_data OUT VARCHAR2 Required
391 --
392 -- Process_Material Notification Parameters :
393 -- p_Req_Material_Tbl IN Req_Material_Tbl_Type,
394 --
395
396 PROCEDURE Material_Notification
397 (
398 p_api_version IN NUMBER:= 1.0,
399 p_init_msg_list IN VARCHAR2,
400 p_commit IN VARCHAR2,
401 p_validation_level IN NUMBER,
402 p_Req_Material_Tbl IN Req_Material_Tbl_Type,
403 x_return_status OUT NOCOPY VARCHAR2,
404 x_msg_count OUT NOCOPY NUMBER,
405 x_msg_data OUT NOCOPY VARCHAR2
406 );
407
408 --
409 END AHL_PP_MATERIALS_PVT;
[Home] [Help]