[Home] [Help]
PACKAGE BODY: APPS.EAM_PROCESS_WO_UTIL_PVT
Source
1 PACKAGE BODY EAM_PROCESS_WO_UTIL_PVT AS
2 /* $Header: EAMVPWUB.pls 120.16 2008/05/16 07:59:11 vchidura ship $ */
3 /***************************************************************************
4 --
5 -- Copyright (c) 2002 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME
9 --
10 -- EAMVPWUB.pls
11 --
12 -- DESCRIPTION
13 --
14 -- Body of package EAM_PROCESS_WO_UTIL_PVT
15 --
16 -- NOTES
17 --
18 -- HISTORY
19 --
20 -- 12-OCT-2003 Basanth Roy Initial Creation
21 -- 15-Jul-05 Anju Gupta Changes for MOAC
22 ***************************************************************************/
23
24 G_PKG_NAME CONSTANT VARCHAR2(30) := 'EAM_PROCESS_WO_UTIL_PVT';
25
26
27
28
29 procedure create_requisition
30 ( p_api_version IN NUMBER := 1.0
31 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
32 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
33 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
34 ,x_return_status OUT NOCOPY VARCHAR2
35 ,x_msg_count OUT NOCOPY NUMBER
36 ,x_msg_data OUT NOCOPY VARCHAR2
37 ,p_wip_entity_id IN NUMBER -- data
38 ,p_operation_seq_num IN NUMBER
39 ,p_organization_id IN NUMBER
40 ,p_user_id IN NUMBER
41 ,p_responsibility_id IN NUMBER
42 ,p_quantity IN NUMBER
43 ,p_unit_price IN NUMBER
44 ,p_category_id IN NUMBER
45 ,p_item_description IN VARCHAR2
46 ,p_uom_code IN VARCHAR2
47 ,p_need_by_date IN DATE
48 ,p_inventory_item_id IN NUMBER
49 ,p_direct_item_id IN NUMBER
50 ,p_suggested_vendor_id IN NUMBER
51 ,p_suggested_vendor_name IN VARCHAR2
52 ,p_suggested_vendor_site IN VARCHAR2
53 ,p_suggested_vendor_phone IN VARCHAR2
54 ,p_suggested_vendor_item_num IN VARCHAR2
55 ) IS
56
57 l_api_name CONSTANT VARCHAR2(30) := 'create_requisition';
58 l_api_version CONSTANT NUMBER := 1.0;
59 l_request_id NUMBER;
60 l_person_id NUMBER;
61 l_material_account NUMBER;
62 l_material_variance_account NUMBER;
63 l_currency VARCHAR2(30);
64 l_project_id NUMBER;
65 l_task_id NUMBER;
66 l_location_id NUMBER;
67 l_stmt_num NUMBER;
68 l_str_application_id VARCHAR2(30);
69 l_project_acc_context VARCHAR2(1);
70 l_ou_id number;
71 l_wip_entity_name VARCHAR2(240);
72 l_req_import VARCHAR2(50);
73
74 -- Added for NF2008 - Copy Asset Number to Notes to Approver on Purchase Requisitions
75 l_asset_number VARCHAR2(30);
76 l_priority NUMBER;
77 l_priority_meaning VARCHAR2(80) := '';
78 l_asset_criticality VARCHAR2(30);
79 l_descriptive_text VARCHAR2(240);
80 l_serial_number VARCHAR2(30);
81 l_asset_group_id NUMBER; --Added for the bug 6928769 and 7037630
82 l_organization_id NUMBER;--Added for the bug 6928769 and 7037630
83 BEGIN
84
85 -- Standard Start of API savepoint
86 l_stmt_num := 10;
87 SAVEPOINT create_requisition_pvt;
88
89 l_stmt_num := 20;
90 -- Standard call to check for call compatibility.
91 IF NOT fnd_api.compatible_api_call(
92 l_api_version
93 ,p_api_version
94 ,l_api_name
95 ,g_pkg_name) THEN
96 RAISE fnd_api.g_exc_unexpected_error;
97 END IF;
98
99 l_stmt_num := 30;
100 -- Initialize message list if p_init_msg_list is set to TRUE.
101 IF fnd_api.to_boolean(p_init_msg_list) THEN
102 fnd_msg_pub.initialize;
103 END IF;
104
105 l_stmt_num := 40;
106 -- Initialize API return status to success
107 x_return_status := fnd_api.g_ret_sts_success;
108
109 l_stmt_num := 50;
110
111 -- API body
112
113 -- If PO_DIRECT_DELIVERY_TO_SHOPFLOOR profile option is not set then return immediately with out error status
114 IF ( NVL(fnd_profile.value('PO_DIRECT_DELIVERY_TO_SHOPFLOOR'), 'N') = 'N' ) THEN
115 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('PO_DIRECT_DELIVERY_TO_SHOPFLOOR profile option is not set. So not creating requisitions.'); END IF;
116 return;
117 END IF;
118
119 --bug# 3691325 If requested quantity is less than or equal to zero then return immediately with out error status
120 IF ( NVL(p_quantity,0) <= 0) then
121 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Requested Quantity is less than or equal to zero. So not creating requisitions.'); END IF;
122 return;
123 END IF;
124
125 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Start of create_requisition'); END IF;
126
127 if (p_user_id is not null) then
128
129 select employee_id
130 into l_person_id
131 from fnd_user
132 where user_id = p_user_id;
133
134 end if;
135
136 if((p_wip_entity_id is not null) and (p_organization_id is not null)) then
137
138
139 SELECT wdj.material_account, wdj.material_variance_account, wdj.project_id,
140 wdj.task_id, we.wip_entity_name, wdj.asset_number,wdj.asset_group_id, wdj.organization_id, wdj.priority
141 INTO l_material_account, l_material_variance_account, l_project_id,
142 l_task_id, l_wip_entity_name, l_serial_number,l_asset_group_id, l_organization_id, l_priority
143 FROM wip_discrete_jobs wdj, wip_entities we
144 WHERE wdj.wip_entity_id = p_wip_entity_id
145 AND wdj.organization_id = p_organization_id
146 AND wdj.wip_entity_id = we.wip_entity_id;
147
148 if l_priority is not null then
149 select meaning into l_priority_meaning from mfg_lookups where
150 lookup_code=l_priority
151 AND lookup_type='WIP_EAM_ACTIVITY_PRIORITY';
152 end if;
153 -- Added for NF2008 - Copy Asset Number to Notes to Approver on Purchase Requisitions
154 SELECT meanv.descriptive_text, meanv.asset_criticality,instance_number
155 INTO l_descriptive_text, l_asset_criticality,l_asset_number
156 FROM mtl_eam_asset_numbers_v meanv
157 WHERE meanv.serial_number = l_serial_number
158 AND meanv.inventory_item_id = l_asset_group_id
159 AND meanv.CURRENT_ORGANIZATION_ID = l_organization_id;
160 if l_project_id is not null then
161 l_project_acc_context := 'Y';
162 end if;
163
164
165
166
167 select gb.currency_code, to_number(ho.ORG_INFORMATION3)
168 into l_currency, l_ou_id
169 from hr_organization_information ho, gl_sets_of_books gb
170 where gb.set_of_books_id = ho.ORG_INFORMATION1
171 and ho.organization_id = p_organization_id
172 and ho.ORG_INFORMATION_CONTEXT = 'Accounting Information';
173
174 end if;
175
176 if((p_wip_entity_id is not null) and (p_organization_id is not null) and (p_operation_seq_num is not null)) then
177
178 begin
179 select bd.location_id
180 into l_location_id
181 from bom_departments bd, wip_operations wo
182 where bd.department_id = wo.department_id
183 and bd.organization_id = wo.organization_id
184 and wo.wip_entity_id = p_wip_entity_id
185 and wo.operation_seq_num = p_operation_seq_num
186 and wo.organization_id = p_organization_id;
187 exception
188 when no_data_found then
189 l_location_id := 0;
190 end;
191
192 end if;
193
194
195 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Inside create_requisitions : inserting into po_requisitions_interface_all ..'); END IF;
196
197 /* Changed for MOAC: Insert org_id as well */
198 insert into po_requisitions_interface_all (
199 interface_source_code,
200 destination_type_code,
201 authorization_status,
202 preparer_id, -- person id of the user name
203 quantity,
204 destination_organization_id,
205 deliver_to_location_id,
206 deliver_to_requestor_id,
207 source_type_code,
208 category_id,
209 item_description,
210 uom_code,
211 unit_price,
212 need_by_date,
213 wip_entity_id,
214 wip_operation_seq_num,
215 charge_account_id,
216 variance_account_id,
217 item_id,
218 wip_resource_seq_num,
219 suggested_vendor_id,
220 suggested_vendor_name,
221 suggested_vendor_site,
222 suggested_vendor_phone,
223 suggested_vendor_item_num,
224 currency_code,
225 project_id,
226 task_id,
227 project_accounting_context,
228 last_updated_by,
229 last_update_date,
230 created_by,
231 creation_date,
232 org_id,
233 reference_num,
234 NOTE_TO_APPROVER )
235 values (
236 'EAM',
237 'SHOP FLOOR',
238 'INCOMPLETE',
239 l_person_id,
240 p_quantity,
241 p_organization_id,
242 l_location_id,
243 l_person_id,
244 'VENDOR',
245 p_category_id,
246 p_item_description,
247 p_uom_code,
248 nvl(p_unit_price,0),
249 p_need_by_date,
250 p_wip_entity_id,
251 p_operation_seq_num,
252 l_material_account,
253 l_material_variance_account,
254 p_inventory_item_id,
255 p_direct_item_id,
256 p_suggested_vendor_id,
257 p_suggested_vendor_name,
258 p_suggested_vendor_site,
259 p_suggested_vendor_phone,
260 p_suggested_vendor_item_num,
261 l_currency,
262 l_project_id,
263 l_task_id,
264 l_project_acc_context,
265 p_user_id,
266 sysdate,
267 p_user_id,
268 sysdate,
269 l_ou_id,
270 substrb(l_wip_entity_name, 1, 25) ,
271 l_asset_number||':'||l_DESCRIPTIVE_TEXT||':'||l_ASSET_CRITICALITY||':'||l_priority_meaning
272 );
273 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Asset Number Debug:- '||l_asset_number||':'||l_DESCRIPTIVE_TEXT||':'||l_ASSET_CRITICALITY||':'||l_priority); END IF;
274
275
276 l_str_application_id := fnd_profile.value('RESP_APPL_ID');
277
278 -- This call to fnd_global.apps_initialize is needed because this is
279 -- part of the WO API which can also be used as a standalone API
280 -- and there needs to be a call to APPS_INITIALIZE before
281 -- concurrent programs are called
282
283 if (p_user_id is not null and p_responsibility_id is not null and l_str_application_id is not null) then
284 FND_GLOBAL.APPS_INITIALIZE(p_user_id, p_responsibility_id, to_number(l_str_application_id),0);
285 end if;
286
287 /* Changes for MOAC */
288 fnd_request.set_org_id (l_ou_id);
289
290 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Inside create_requisitions. Calling the concurrent program ...'); END IF;
291 SELECT REQIMPORT_GROUP_BY_CODE into l_req_import
292 FROM PO_SYSTEM_PARAMETERS_ALL where ORG_ID=l_ou_id; -- Changed for bug 6837105
293 l_request_id := fnd_request.submit_request(
294 'PO', 'REQIMPORT', NULL, NULL, FALSE,'EAM', NULL, l_req_import,
295 NULL ,'N', 'Y' , chr(0), NULL, NULL, NULL,
296 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
297 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
298 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
299 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
300 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
301 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
302 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
303 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
304 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
305 ) ;
306
307
308
309 -- End of API body.
310 -- Standard check of p_commit.
311 IF fnd_api.to_boolean(p_commit) THEN
312 COMMIT WORK;
313 END IF;
314
315 l_stmt_num := 999;
316 -- Standard call to get message count and if count is 1, get message info.
317 fnd_msg_pub.count_and_get(
318 p_encoded => fnd_api.g_false
319 ,p_count => x_msg_count
320 ,p_data => x_msg_data);
321 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Inside create_requisitions. concurrent program finished ...'); END IF;
322
323 return;
324
325
326 EXCEPTION
327
328
329 WHEN fnd_api.g_exc_error THEN
330 ROLLBACK TO create_requisition_pvt;
331 x_return_status := fnd_api.g_ret_sts_error;
332 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, SQLERRM);
333 fnd_msg_pub.count_and_get(
334 p_encoded => fnd_api.g_false
335 ,p_count => x_msg_count
336 ,p_data => x_msg_data);
337 WHEN fnd_api.g_exc_unexpected_error THEN
338 ROLLBACK TO create_requisition_pvt;
339 x_return_status := fnd_api.g_ret_sts_unexp_error;
340 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, SQLERRM);
341 fnd_msg_pub.count_and_get(
342 p_encoded => fnd_api.g_false
343 ,p_count => x_msg_count
344 ,p_data => x_msg_data);
345 WHEN OTHERS THEN
346 ROLLBACK TO create_requisition_pvt;
347 x_return_status := fnd_api.g_ret_sts_unexp_error;
348 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, SQLERRM);
349 IF fnd_msg_pub.check_msg_level(
350 fnd_msg_pub.g_msg_lvl_unexp_error) THEN
351 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
352 END IF;
353
354 fnd_msg_pub.count_and_get(
355 p_encoded => fnd_api.g_false
356 ,p_count => x_msg_count
357 ,p_data => x_msg_data);
358
359
360
361 END create_requisition;
362
363
364
365
366
367
368 PROCEDURE create_reqs_at_wo_rel
369 ( p_api_version IN NUMBER := 1.0
370 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
371 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
372 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
373 ,x_return_status OUT NOCOPY VARCHAR2
374 ,x_msg_count OUT NOCOPY NUMBER
375 ,x_msg_data OUT NOCOPY VARCHAR2
376 ,p_user_id IN NUMBER
377 ,p_responsibility_id IN NUMBER
378 ,p_wip_entity_id IN NUMBER -- data
379 ,p_organization_id IN NUMBER)
380 IS
381 l_api_name CONSTANT VARCHAR2(30) := 'create_reqs_at_wo_rel';
382 l_api_version CONSTANT NUMBER := 1.0;
383 l_request_id NUMBER;
384 l_person_id NUMBER;
385 l_material_account NUMBER;
386 l_material_variance_account NUMBER;
387 l_currency VARCHAR2(30);
388 l_project_id NUMBER;
389 l_task_id NUMBER;
390 l_location_id NUMBER;
391 l_description VARCHAR2(240);
392 l_stmt_num NUMBER;
393 l_api_return_status VARCHAR2(1);
394 l_api_msg_count NUMBER;
395 l_api_msg_data VARCHAR2(2000);
396 l_total_req_qty NUMBER;
397
398 CURSOR l_di_recs IS
399 (
400 SELECT wip_entity_id,
401 organization_id,
402 operation_seq_num as task_number,
403 to_number(null) as inventory_item_id,
404 direct_item_sequence_id,
405 1 as direct_item_type_id,
406 description,
407 required_quantity,
408 unit_price,
409 uom as uom_code,
410 purchasing_category_id,
411 need_by_date as date_required,
412 auto_request_material,
413 suggested_vendor_id,
414 suggested_vendor_name,
415 suggested_vendor_site,
416 suggested_vendor_phone,
417 suggested_vendor_item_num
418 FROM wip_eam_direct_items
419 WHERE wip_entity_id = p_wip_entity_id
420 AND organization_id = p_organization_id
421 UNION ALL
422 SELECT wro.wip_entity_id,
423 wro.organization_id,
424 wro.operation_seq_num as task_number,
425 wro.inventory_item_id,
426 to_number(null) as direct_item_sequence_id,
427 2 as direct_item_type_id,
428 msi.description,
429 wro.required_quantity,
430 wro.unit_price,
431 msi.primary_uom_code as uom_code,
432 mic.category_id as purchasing_category_id,
433 wro.date_required,
434 wro.auto_request_material,
435 vendor_id,
436 wro.suggested_vendor_name,
437 to_char(null) as suggested_vendor_site,
438 to_char(null) as suggested_vendor_phone,
439 to_char(null) as suggested_vendor_item_num
440 FROM wip_requirement_operations wro,
441 mtl_system_items_kfv msi,
442 mtl_item_categories mic ,
443 mtl_default_category_sets mdcs
444 WHERE msi.inventory_item_id = wro.inventory_item_id
445 AND msi.organization_id = wro.organization_id
446 AND nvl(msi.stock_enabled_flag, 'N') = 'N'
447 AND wro.inventory_item_id = mic.inventory_item_id
448 AND wro.organization_id = mic.organization_id
449 AND mic.category_set_id = mdcs.category_set_id
450 AND mdcs.functional_area_id = 2
451 AND wro.wip_entity_id = p_wip_entity_id
452 AND wro.organization_id = p_organization_id
453 );
454
455 BEGIN
456
457 -- Standard Start of API savepoint
458 l_stmt_num := 10;
459 SAVEPOINT create_requisition_pvt;
460
461 l_stmt_num := 20;
462 -- Standard call to check for call compatibility.
463 IF NOT fnd_api.compatible_api_call(
464 l_api_version
465 ,p_api_version
466 ,l_api_name
467 ,g_pkg_name) THEN
468 RAISE fnd_api.g_exc_unexpected_error;
469 END IF;
470
471 l_stmt_num := 30;
472 -- Initialize message list if p_init_msg_list is set to TRUE.
473 IF fnd_api.to_boolean(p_init_msg_list) THEN
474 fnd_msg_pub.initialize;
475 END IF;
476
477 l_stmt_num := 40;
478 -- Initialize API return status to success
479 x_return_status := fnd_api.g_ret_sts_success;
480
481 l_stmt_num := 50;
482
483 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Start of create_reqs_at_wo_rel'); END IF;
484
485 FOR l_di_record in l_di_recs
486 LOOP
487
488 if l_di_record.wip_entity_id is not null
489 and l_di_record.auto_request_material = 'Y' then
490
491
492 IF l_di_record.direct_item_type_id = 1 THEN -- description based direct item . fix for 3421830
493 BEGIN
494 /*Querying table po_requisitions_interface_all also to avoid duplication of requisitions, added for bug #6112450*/
495 SELECT SUM(nvl(req_qty,0)) INTO l_total_req_qty
496 FROM
497 (SELECT SUM(nvl(quantity,0)) req_qty
498 FROM po_requisitions_interface_all pria
499 WHERE pria.wip_entity_id =l_di_record.wip_entity_id
500 AND pria.destination_organization_id = l_di_record.organization_id
501 AND pria.wip_operation_seq_num = l_di_record.task_number
502 AND pria.item_id is null
503 AND ( pria.wip_resource_seq_num = l_di_record.direct_item_sequence_id OR pria.wip_resource_seq_num IS NULL)
504 AND pria.item_description(+) = l_di_record.description
505 AND ((process_flag is null) or (Upper(Trim(process_flag)) = 'IN PROCESS'))
506 UNION ALL
507 SELECT SUM(nvl(quantity,0)) req_qty
508 FROM po_requisition_lines_all prla , po_requisition_headers_all prha
509 WHERE prla.requisition_header_id = prha.requisition_header_id(+)
510 AND upper(NVL(prha.authorization_status, 'APPROVED') ) not in ('CANCELLED', 'REJECTED','SYSTEM_SAVED')
511 AND prla.wip_entity_id =l_di_record.wip_entity_id
512 AND prla.destination_organization_id = l_di_record.organization_id
513 AND prla.wip_operation_seq_num = l_di_record.task_number
514 AND prla.item_id is null
515 AND ( prla.wip_resource_seq_num = l_di_record.direct_item_sequence_id OR prla.wip_resource_seq_num IS NULL)
516 AND prla.item_description(+) = l_di_record.description
517 );
518 EXCEPTION
519 WHEN NO_DATA_FOUND THEN
520 l_total_req_qty := 0;
521 END;
522
523
524 l_description := l_di_record.description;
525
526 ELSE
527
528
529 BEGIN
530 /*Querying table po_requisitions_interface_all also to avoid duplication of requisitions, added for bug #6112450*/
531 SELECT SUM(nvl(req_qty,0)) INTO l_total_req_qty
532 FROM
533 (SELECT SUM(nvl(quantity,0)) req_qty
534 FROM po_requisitions_interface_all pria
535 WHERE pria.wip_entity_id = l_di_record.wip_entity_id
536 AND pria.destination_organization_id = l_di_record.organization_id
537 AND pria.wip_operation_seq_num = l_di_record.task_number
538 AND pria.item_id = l_di_record.inventory_item_id
539 AND ((process_flag is null) or (Upper(Trim(process_flag)) = 'IN PROCESS'))
540 UNION ALL
541 SELECT SUM(nvl(quantity,0)) req_qty
542 FROM po_requisition_lines_all prla , po_requisition_headers_all prha
543 WHERE prla.requisition_header_id = prha.requisition_header_id(+)
544 AND upper(NVL(prha.authorization_status, 'APPROVED') ) not in ('CANCELLED', 'REJECTED','SYSTEM_SAVED')
545 AND prla.wip_entity_id = l_di_record.wip_entity_id
546 AND prla.destination_organization_id = l_di_record.organization_id
547 AND prla.wip_operation_seq_num = l_di_record.task_number
548 AND prla.item_id = l_di_record.inventory_item_id
549 );
550
551
552 EXCEPTION
553 WHEN NO_DATA_FOUND THEN
554 l_total_req_qty := 0 ;
555 END;
556 --Bug4188160:pass description of item instead of item name
557 l_description := l_di_record.description;
558
559 END IF;
560
561
562 create_requisition
563 ( p_api_version => 1.0
564 ,p_init_msg_list => FND_API.G_FALSE
565 ,p_commit => FND_API.G_FALSE
566 ,p_validate_only => FND_API.G_TRUE
567 ,x_return_status => l_api_return_status
568 ,x_msg_count => l_api_msg_count
569 ,x_msg_data => l_api_msg_data
570 ,p_wip_entity_id => l_di_record.wip_entity_id
571 ,p_operation_seq_num => l_di_record.task_number
572 ,p_organization_id => l_di_record.organization_id
573 ,p_user_id => p_user_id
574 ,p_responsibility_id => p_responsibility_id
575 ,p_quantity => (l_di_record.required_quantity-nvl(l_total_req_qty,0)) -- fix for 3421830
576 ,p_unit_price => l_di_record.unit_price
577 ,p_category_id => l_di_record.purchasing_category_id
578 ,p_item_description => l_description
579 ,p_uom_code => l_di_record.uom_code
580 ,p_need_by_date => l_di_record.date_required
581 ,p_inventory_item_id => l_di_record.inventory_item_id
582 ,p_direct_item_id => l_di_record.direct_item_sequence_id
583 ,p_suggested_vendor_id => l_di_record.suggested_vendor_id
584 ,p_suggested_vendor_name => l_di_record.suggested_vendor_name
585 ,p_suggested_vendor_site => l_di_record.suggested_vendor_site
586 ,p_suggested_vendor_phone => l_di_record.suggested_vendor_phone
587 ,p_suggested_vendor_item_num => l_di_record.suggested_vendor_item_num);
588
589
590 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('creating reqs for di seq finished with status '||l_api_return_status); END IF;
591
592 if nvl(l_api_return_status,'Q') <> 'S' then
593 x_return_status := fnd_api.g_ret_sts_error;
594 end if;
595
596 end if;
597
598 END LOOP;
599
600 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('End of create_reqs_at_wo_rel'); END IF;
601
602
603 EXCEPTION
604
605 when others then
606
607 x_return_status := fnd_api.g_ret_sts_error;
608
609 declare
610 l_text varchar2(1000);
611 begin
612 l_text := sqlerrm;
613 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('creating reqs for di seq , sqlerrm='||substrb(l_text,1,200)); END IF;
614 end;
615
616 END create_reqs_at_wo_rel;
617
618
619
620 PROCEDURE create_reqs_at_di_upd
621 ( p_api_version IN NUMBER := 1.0
622 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
623 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
624 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
625 ,x_return_status OUT NOCOPY VARCHAR2
626 ,x_msg_count OUT NOCOPY NUMBER
627 ,x_msg_data OUT NOCOPY VARCHAR2
628 ,p_user_id IN NUMBER
629 ,p_responsibility_id IN NUMBER
630 ,p_wip_entity_id IN NUMBER -- data
631 ,p_organization_id IN NUMBER
632 ,p_direct_item_sequence_id IN NUMBER
633 ,p_inventory_item_id IN NUMBER
634 ,p_required_quantity IN NUMBER)
635 IS
636 l_api_name CONSTANT VARCHAR2(30) := 'create_reqs_at_di_upd';
637 l_api_version CONSTANT NUMBER := 1.0;
638 l_request_id NUMBER;
639 l_person_id NUMBER;
640 l_material_account NUMBER;
641 l_material_variance_account NUMBER;
642 l_currency VARCHAR2(30);
643 l_project_id NUMBER;
644 l_task_id NUMBER;
645 l_location_id NUMBER;
646 l_stmt_num NUMBER;
647 l_api_return_status NUMBER;
648 l_api_msg_count NUMBER;
649 l_api_msg_data VARCHAR2(2000);
650 TYPE DirectItemRec IS RECORD (
651 wip_entity_id eam_direct_item_recs_v.wip_entity_id%TYPE,
652 organization_id eam_direct_item_recs_v.organization_id%TYPE,
653 task_number eam_direct_item_recs_v.task_number%TYPE,
654 inventory_item_id eam_direct_item_recs_v.inventory_item_id%TYPE,
655 direct_item_sequence_id eam_direct_item_recs_v.direct_item_sequence_id%TYPE,
656 direct_item_type_id eam_direct_item_recs_v.direct_item_type_id%TYPE,
657 description eam_direct_item_recs_v.description%TYPE,
658 required_quantity eam_direct_item_recs_v.required_quantity%TYPE,
659 unit_price eam_direct_item_recs_v.unit_price%TYPE,
660 uom_code eam_direct_item_recs_v.uom_code%TYPE,
661 purchasing_category_id eam_direct_item_recs_v.purchasing_category_id%TYPE,
662 date_required eam_direct_item_recs_v.date_required%TYPE,
663 auto_request_material eam_direct_item_recs_v.auto_request_material%TYPE,
664 suggested_vendor_id eam_direct_item_recs_v.suggested_vendor_id%TYPE,
665 suggested_vendor_name eam_direct_item_recs_v.suggested_vendor_name%TYPE,
666 suggested_vendor_site eam_direct_item_recs_v.suggested_vendor_site%TYPE,
667 suggested_vendor_phone eam_direct_item_recs_v.suggested_vendor_phone%TYPE,
668 suggested_vendor_item_num eam_direct_item_recs_v.suggested_vendor_item_num%TYPE );
669
670 TYPE l_di_recs_type IS REF CURSOR RETURN DirectItemRec;
671 l_di_recs l_di_recs_type;
672 l_di_record l_di_recs%ROWTYPE;
673
674 BEGIN
675
676 -- Standard Start of API savepoint
677 l_stmt_num := 10;
678 SAVEPOINT create_requisition_pvt;
679
680 l_stmt_num := 20;
681 -- Standard call to check for call compatibility.
682 IF NOT fnd_api.compatible_api_call(
683 l_api_version
684 ,p_api_version
685 ,l_api_name
686 ,g_pkg_name) THEN
687 RAISE fnd_api.g_exc_unexpected_error;
688 END IF;
689
690 l_stmt_num := 30;
691 -- Initialize message list if p_init_msg_list is set to TRUE.
692 IF fnd_api.to_boolean(p_init_msg_list) THEN
693 fnd_msg_pub.initialize;
694 END IF;
695
696 l_stmt_num := 40;
697 -- Initialize API return status to success
698 x_return_status := fnd_api.g_ret_sts_success;
699
700 l_stmt_num := 50;
701
702 -- API body
703
704 IF NOT l_di_recs%ISOPEN THEN
705
706 if p_direct_item_sequence_id is not null then
707 OPEN l_di_recs FOR
708 SELECT wip_entity_id,
709 organization_id,
710 operation_seq_num as task_number,
711 to_number(null) as inventory_item_id,
712 direct_item_sequence_id,
713 1 as direct_item_type_id,
714 description,
715 required_quantity,
716 unit_price,
717 uom as uom_code,
718 purchasing_category_id,
719 need_by_date as date_required,
720 auto_request_material,
721 SUGGESTED_VENDOR_ID,
722 suggested_vendor_name,
723 suggested_vendor_site,
724 suggested_vendor_phone,
725 suggested_vendor_item_num
726 FROM wip_eam_direct_items
727 WHERE wip_entity_id = p_wip_entity_id
728 AND organization_id = p_organization_id
729 AND direct_item_sequence_id = p_direct_item_sequence_id;
730 elsif p_inventory_item_id is not null then
731 OPEN l_di_recs FOR
732 SELECT wro.wip_entity_id,
733 wro.organization_id,
734 wro.operation_seq_num as task_number,
735 wro.inventory_item_id,
736 to_number(null) as direct_item_sequence_id,
737 2 as direct_item_type_id,
738 msi.description,
739 wro.required_quantity,
740 wro.unit_price,
741 msi.primary_uom_code as uom_code,
742 mic.category_id as purchasing_category_id,
743 wro.date_required,
744 wro.auto_request_material,
745 vendor_id,
746 wro.suggested_vendor_name,
747 to_char(null) as suggested_vendor_site,
748 to_char(null) as suggested_vendor_phone,
749 to_char(null) as suggested_vendor_item_num
750 FROM wip_requirement_operations wro,
751 mtl_system_items_kfv msi,
752 mtl_item_categories mic ,
753 mtl_default_category_sets mdcs
754 WHERE msi.inventory_item_id = wro.inventory_item_id
755 AND msi.organization_id = wro.organization_id
756 AND nvl(msi.stock_enabled_flag, 'N') = 'N'
757 AND wro.inventory_item_id = mic.inventory_item_id
758 AND wro.organization_id = mic.organization_id
759 AND mic.category_set_id = mdcs.category_set_id
760 AND mdcs.functional_area_id = 2
761 AND wro.wip_entity_id = p_wip_entity_id
762 AND wro.organization_id = p_organization_id
763 and wro.inventory_item_id = p_inventory_item_id;
764 end if;
765
766 END IF;
767
768 LOOP
769
770 FETCH l_di_recs INTO l_di_record;
771 EXIT WHEN l_di_recs%NOTFOUND;
772
773 if l_di_record.wip_entity_id is not null
774 and l_di_record.auto_request_material = 'Y' then
775 --Bug4188160:pass description of item instead of item name
776 create_requisition
777 ( p_api_version => 1.0
778 ,p_init_msg_list => FND_API.G_FALSE
779 ,p_commit => FND_API.G_FALSE
780 ,p_validate_only => FND_API.G_TRUE
781 ,x_return_status => l_api_return_status
782 ,x_msg_count => l_api_msg_count
783 ,x_msg_data => l_api_msg_data
784 ,p_wip_entity_id => l_di_record.wip_entity_id
785 ,p_operation_seq_num => l_di_record.task_number
786 ,p_organization_id => l_di_record.organization_id
787 ,p_user_id => p_user_id
788 ,p_responsibility_id => p_responsibility_id
789 ,p_quantity => p_required_quantity
790 ,p_unit_price => l_di_record.unit_price
791 ,p_category_id => l_di_record.purchasing_category_id
792 ,p_item_description => l_di_record.description
793 ,p_uom_code => l_di_record.uom_code
794 ,p_need_by_date => l_di_record.date_required
795 ,p_inventory_item_id => l_di_record.inventory_item_id
796 ,p_direct_item_id => l_di_record.direct_item_sequence_id
797 ,p_suggested_vendor_id => l_di_record.suggested_vendor_id
798 ,p_suggested_vendor_name => l_di_record.suggested_vendor_name
799 ,p_suggested_vendor_site => l_di_record.suggested_vendor_site
800 ,p_suggested_vendor_phone => l_di_record.suggested_vendor_phone
801 ,p_suggested_vendor_item_num => l_di_record.suggested_vendor_item_num);
802
803 if nvl(l_api_return_status,'Q') <> 'S' then
804 x_return_status := fnd_api.g_ret_sts_error;
805 end if;
806
807 end if;
808
809 END LOOP;
810 CLOSE l_di_recs;
811
812 EXCEPTION
813
814 when others then
815
816 x_return_status := fnd_api.g_ret_sts_error;
817
818 END create_reqs_at_di_upd;
819
820
821
822 END EAM_PROCESS_WO_UTIL_PVT;