1 PACKAGE BODY EAM_PROCESS_WO_UTIL_PVT AS
2 /* $Header: EAMVPWUB.pls 120.21.12020000.6 2013/03/26 13:17:09 vboddapa 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 l_available NUMBER;
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(240);
79 l_descriptive_text VARCHAR2(240);
80 l_serial_number VARCHAR2(30);
81
82 l_asset_group_id NUMBER; --Added for the bug 6928769 and 7037630
83 l_organization_id NUMBER;--Added for the bug 6928769 and 7037630
84 l_maintenance_object_id NUMBER; -- Added for the bug 8363544, the fix of the previous bug fixing
85 l_maintenance_object_type NUMBER; -- Added for the bug 8363544, the fix of the previous bug fixing
86 l_user_id NUMBER; --Added for bug 13638082
87 l_status_options BOOLEAN ; --Added for bug 13638082
88 l_asset_cur_org_id NUMBER; --Added for bug 14101354
89
90 BEGIN
91 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN
92 EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||' EAM_PROCESS_WO_UTIL_PVT.create_requisition : Start===============================================================');
93 END IF;
94
95 -- Standard Start of API savepoint
96 l_stmt_num := 10;
97 SAVEPOINT create_requisition_pvt;
98
99 l_stmt_num := 20;
100 -- Standard call to check for call compatibility.
101 IF NOT fnd_api.compatible_api_call(
102 l_api_version
103 ,p_api_version
104 ,l_api_name
105 ,g_pkg_name) THEN
106 RAISE fnd_api.g_exc_unexpected_error;
107 END IF;
108
109 l_stmt_num := 30;
110 -- Initialize message list if p_init_msg_list is set to TRUE.
111 IF fnd_api.to_boolean(p_init_msg_list) THEN
112 fnd_msg_pub.initialize;
113 END IF;
114
115 l_stmt_num := 40;
116 -- Initialize API return status to success
117 x_return_status := fnd_api.g_ret_sts_success;
118
119 l_stmt_num := 50;
120
121 -- API body
122
123 -- If PO_DIRECT_DELIVERY_TO_SHOPFLOOR profile option is not set then return immediately with out error status
124 IF ( NVL(fnd_profile.value('PO_DIRECT_DELIVERY_TO_SHOPFLOOR'), 'N') = 'N' ) THEN
125 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN
126 EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||' EAM_PROCESS_WO_UTIL_PVT.create_requisition : PO_DIRECT_DELIVERY_TO_SHOPFLOOR profile option is not set. So not creating requisitions.');
127 END IF;
128 return;
129 END IF;
130
131 --bug# 3691325 If requested quantity is less than or equal to zero then return immediately with out error status
132 IF ( NVL(p_quantity,0) <= 0) then
133 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN
134 EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||' EAM_PROCESS_WO_UTIL_PVT.create_requisition : Requested Quantity is less than or equal to zero. So not creating requisitions.');
135 END IF;
136 return;
137 END IF;
138
139
140 if (nvl(p_user_id,fnd_global.user_id) is not null) then
141
142 select employee_id
143 into l_person_id
144 from fnd_user
145 where user_id = nvl(p_user_id,fnd_global.user_id);
146
147 end if;
148
149 if((p_wip_entity_id is not null) and (p_organization_id is not null)) then
150
151 --bug# 8363544, using wip_discrete_jobs.maintenance_object_id instead of wip_discrete_jobs.asset_number which is no more used after R12
152 -- then find serial number, inventory_item_id in csi_item_instances table with wip_discrete_jobs.maintenance_object_id for serialized item
153 -- there is no asset number for non-serialized item
154 -- Use wdj.rebuild_item_id as group id for non-serialized items; otherwise, find group id in table "csi_item_instances" as well.
155 SELECT wdj.project_id, wdj.task_id, we.wip_entity_name, wdj.maintenance_object_id,
156 wdj.maintenance_object_type, wdj.organization_id, wdj.priority
157 INTO l_project_id,l_task_id, l_wip_entity_name, l_maintenance_object_id,
158 l_maintenance_object_type, l_organization_id, l_priority
159 FROM wip_discrete_jobs wdj, wip_entities we
160 WHERE wdj.wip_entity_id = p_wip_entity_id
161 AND wdj.organization_id = p_organization_id
162 AND wdj.wip_entity_id = we.wip_entity_id;
163
164 /* Added for bug 9216810 */
165 select count(category_id) into l_available from cst_cat_ele_exp_assocs_v cceav
166 where category_id = p_category_id and
167 nvl(cceav.start_date,sysdate-1) <= sysdate and
168 nvl(cceav.end_date,sysdate+1) >= sysdate;
169
170 if l_available > 0 then
171 select decode(cceav.mfg_cost_element_id,
172 1,wac.material_account,
173 2,wac.material_overhead_account,
174 3,wac.resource_account,
175 4,wac.outside_processing_account,
176 5,wac.overhead_account,wac.material_account) account_id,
177
178 decode(cceav.mfg_cost_element_id,
179 1,wac.material_variance_account,
180 2,wac.material_overhead_account,
181 3,wac.resource_variance_account,
182 4,wac.outside_proc_variance_account,
183 5,wac.overhead_variance_account,wac.material_variance_account) variance_account_id
184
185 into l_material_account,l_material_variance_account
186 from cst_cat_ele_exp_assocs_v cceav, wip_accounting_classes wac,wip_discrete_jobs wdj
187 where wdj.organization_id = p_organization_id and
188 wdj.wip_entity_id = p_wip_entity_id and
189 wac.class_code = wdj.class_code and
190 wac.organization_id= wdj.organization_id and
191 cceav.category_id =p_category_id and
192 nvl(cceav.start_date,sysdate-1) <= sysdate and
193 nvl(cceav.end_date,sysdate+1) >= sysdate;
194 else
195 select wdj.material_account, wdj.material_variance_account
196 into l_material_account,l_material_variance_account
197 from wip_discrete_jobs wdj
198 where wdj.wip_entity_id = p_wip_entity_id and
199 wdj.organization_id = p_organization_id;
200 end if;
201
202 if l_priority is not null then
203 select meaning into l_priority_meaning from mfg_lookups where
204 lookup_code=l_priority
205 AND lookup_type='WIP_EAM_ACTIVITY_PRIORITY';
206 end if;
207
208 -- Added for the bug 8363544, the fix of the previous bug fixing
209 -- To get the correct asset number from csi_item_instances for serialized items
210 IF l_maintenance_object_type = 3 THEN
211
212 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN
213 EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||' EAM_PROCESS_WO_UTIL_PVT.create_requisition : Serialized Asset Retrieving info from csi_item_instances');
214 END IF;
215
216 SELECT serial_number, inventory_item_id, last_vld_organization_id
217 INTO l_serial_number, l_asset_group_id, l_asset_cur_org_id
218 FROM csi_item_instances
219 WHERE instance_id =l_maintenance_object_id;
220 --changed for the 14101354..Retrieving New org Id if the asset is moved out of current org
221 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN
222 EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||' EAM_PROCESS_WO_UTIL_PVT.create_requisition : Checking query variables are null or not : Serial Number:'|| l_serial_number);
223 EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||' EAM_PROCESS_WO_UTIL_PVT.create_requisition : Asset Group Id :' || l_asset_group_id ||' , Organization Id:' || l_organization_id);
224 END IF;
225
226 -- Added for NF2008 - Copy Asset Number to Notes to Approver on Purchase Requisitions
227 SELECT meanv.descriptive_text, meanv.asset_criticality,instance_number
228 INTO l_descriptive_text, l_asset_criticality,l_asset_number
229 FROM mtl_eam_asset_numbers_v meanv
230 WHERE meanv.serial_number = l_serial_number
231 AND meanv.inventory_item_id = l_asset_group_id
232 AND meanv.CURRENT_ORGANIZATION_ID = nvl(l_asset_cur_org_id, l_organization_id);
233 --changed for the 14101354
234 --for non serialized items
235 ELSIF l_maintenance_object_type = 2 THEN
236 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN
237 EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||' EAM_PROCESS_WO_UTIL_PVT.create_requisition : Non-serialized Asset-Retrieving info from mtl_system_items_b: l_serial_number:'||l_serial_number);
238 EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||' EAM_PROCESS_WO_UTIL_PVT.create_requisition : Asset Group Id:' || l_asset_group_id ||' , Organization Id:' || l_organization_id);
239 END IF;
240
241 SELECT msib.description
242 INTO l_descriptive_text
243 FROM mtl_system_items_b msib
244 WHERE msib.inventory_item_id= l_maintenance_object_id
245 AND msib.organization_id = l_organization_id;
246 END IF;
247
248 if l_project_id is not null then
249 l_project_acc_context := 'Y';
250 end if;
251
252
253
254
255 select gb.currency_code, to_number(ho.ORG_INFORMATION3)
256 into l_currency, l_ou_id
257 from hr_organization_information ho, gl_sets_of_books gb
258 where gb.set_of_books_id = ho.ORG_INFORMATION1
259 and ho.organization_id = p_organization_id
260 and ho.ORG_INFORMATION_CONTEXT = 'Accounting Information';
261
262 end if;
263
264 if((p_wip_entity_id is not null) and (p_organization_id is not null) and (p_operation_seq_num is not null)) then
265
266 begin
267 select bd.location_id
268 into l_location_id
269 from bom_departments bd, wip_operations wo
270 where bd.department_id = wo.department_id
271 and bd.organization_id = wo.organization_id
272 and wo.wip_entity_id = p_wip_entity_id
273 and wo.operation_seq_num = p_operation_seq_num
274 and wo.organization_id = p_organization_id;
275 exception
276 when no_data_found then
277 l_location_id := 0;
278 end;
279
280 end if;
281
282
283 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||' EAM_PROCESS_WO_UTIL_PVT.create_requisition : inserting into po_requisitions_interface_all ..'); END IF;
284
285 /* Changed for MOAC: Insert org_id as well */
286 insert into po_requisitions_interface_all (
287 interface_source_code,
288 destination_type_code,
289 authorization_status,
290 preparer_id, -- person id of the user name
291 quantity,
292 destination_organization_id,
293 deliver_to_location_id,
294 deliver_to_requestor_id,
295 source_type_code,
296 category_id,
297 item_description,
298 uom_code,
299 unit_price,
300 need_by_date,
301 wip_entity_id,
302 wip_operation_seq_num,
303 charge_account_id,
304 -- For bug# 14163019, Variance Account would be defaulted from INV params when not inserted
305 -- variance_account_id,
306 item_id,
307 wip_resource_seq_num,
308 suggested_vendor_id,
309 suggested_vendor_name,
310 suggested_vendor_site,
311 suggested_vendor_phone,
312 suggested_vendor_item_num,
313 currency_code,
314 project_id,
315 task_id,
316 project_accounting_context,
317 last_updated_by,
318 last_update_date,
319 created_by,
320 creation_date,
321 org_id,
322 reference_num,
323 NOTE_TO_APPROVER )
324 values (
325 'EAM',
326 'SHOP FLOOR',
327 'INCOMPLETE',
328 l_person_id,
329 p_quantity,
330 p_organization_id,
331 l_location_id,
332 l_person_id,
333 'VENDOR',
334 p_category_id,
335 p_item_description,
336 p_uom_code,
337 nvl(p_unit_price,0),
338 p_need_by_date,
339 p_wip_entity_id,
340 p_operation_seq_num,
341 l_material_account,
342 -- For bug# 14163019
343 -- l_material_variance_account,
344 p_inventory_item_id,
345 p_direct_item_id,
346 p_suggested_vendor_id,
347 p_suggested_vendor_name,
348 p_suggested_vendor_site,
349 p_suggested_vendor_phone,
350 p_suggested_vendor_item_num,
351 l_currency,
352 l_project_id,
353 l_task_id,
354 l_project_acc_context,
355 nvl(p_user_id,fnd_global.user_id),
356 sysdate,
357 nvl(p_user_id,fnd_global.user_id),
358 sysdate,
359 l_ou_id,
360 substrb(l_wip_entity_name, 1, 25) ,
361 l_asset_number||':'||l_DESCRIPTIVE_TEXT||':'||l_ASSET_CRITICALITY||':'||l_priority_meaning
362 );
363 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN
364 EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||' EAM_PROCESS_WO_UTIL_PVT.create_requisition : Asset Number Debug: Asset Number: '||l_asset_number||' , Description:'||l_DESCRIPTIVE_TEXT);
365 EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||' EAM_PROCESS_WO_UTIL_PVT.create_requisition : Criticality:'||l_ASSET_CRITICALITY||' , Priority:'||l_priority);
366 END IF;
367
368 l_str_application_id := fnd_profile.value('RESP_APPL_ID');
369
370 -- This call to fnd_global.apps_initialize is needed because this is
371 -- part of the WO API which can also be used as a standalone API
372 -- and there needs to be a call to APPS_INITIALIZE before
373 -- concurrent programs are called
374
375 if (nvl(p_user_id,fnd_global.user_id) is not null and p_responsibility_id is not null and l_str_application_id is not null) then
376 FND_GLOBAL.APPS_INITIALIZE(nvl(p_user_id,fnd_global.user_id), p_responsibility_id, to_number(l_str_application_id),0);
377 end if;
378
379 /* Changes for MOAC */
380 fnd_request.set_org_id (l_ou_id);
381 l_status_options := fnd_request.set_options(datagroup => 'Standard');
382
383 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||' EAM_PROCESS_WO_UTIL_PVT.create_requisition : Calling the concurrent program ...'); END IF;
384 SELECT REQIMPORT_GROUP_BY_CODE into l_req_import
385 FROM PO_SYSTEM_PARAMETERS_ALL where ORG_ID=l_ou_id; -- Changed for bug 6837105
386 l_request_id := fnd_request.submit_request(
387 'PO', 'REQIMPORT', NULL, NULL, FALSE,'EAM', NULL, l_req_import,
388 NULL ,'N', 'Y' , chr(0), NULL, NULL, NULL,
389 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
390 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
391 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
392 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
393 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
394 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
395 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
396 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
397 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
398 ) ;
399
400
401
402 -- End of API body.
403 -- Standard check of p_commit.
404 IF fnd_api.to_boolean(p_commit) THEN
405 COMMIT WORK;
406 END IF;
407
408 l_stmt_num := 999;
409 -- Standard call to get message count and if count is 1, get message info.
410 fnd_msg_pub.count_and_get(
411 p_encoded => fnd_api.g_false
412 ,p_count => x_msg_count
413 ,p_data => x_msg_data);
414
415 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN
416 EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||' EAM_PROCESS_WO_UTIL_PVT.create_requisition : Concurrent program finished Status : '||x_return_status||' End =========================');
417 END IF;
418 return;
419
420
421 EXCEPTION
422
423
424 WHEN fnd_api.g_exc_error THEN
425 ROLLBACK TO create_requisition_pvt;
426 x_return_status := fnd_api.g_ret_sts_error;
427 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, SQLERRM);
428 fnd_msg_pub.count_and_get(
429 p_encoded => fnd_api.g_false
430 ,p_count => x_msg_count
431 ,p_data => x_msg_data);
432 WHEN fnd_api.g_exc_unexpected_error THEN
433 ROLLBACK TO create_requisition_pvt;
434 x_return_status := fnd_api.g_ret_sts_unexp_error;
435 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, SQLERRM);
436 fnd_msg_pub.count_and_get(
437 p_encoded => fnd_api.g_false
438 ,p_count => x_msg_count
439 ,p_data => x_msg_data);
440 WHEN OTHERS THEN
441 ROLLBACK TO create_requisition_pvt;
442 x_return_status := fnd_api.g_ret_sts_unexp_error;
443 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, SQLERRM);
444 IF fnd_msg_pub.check_msg_level(
445 fnd_msg_pub.g_msg_lvl_unexp_error) THEN
446 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
447 END IF;
448
449 fnd_msg_pub.count_and_get(
450 p_encoded => fnd_api.g_false
451 ,p_count => x_msg_count
452 ,p_data => x_msg_data);
453
454
455
456 END create_requisition;
457
458
459
460
461
462
463 PROCEDURE create_reqs_at_wo_rel
464 ( p_api_version IN NUMBER := 1.0
465 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
466 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
467 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
468 ,x_return_status OUT NOCOPY VARCHAR2
469 ,x_msg_count OUT NOCOPY NUMBER
470 ,x_msg_data OUT NOCOPY VARCHAR2
471 ,p_user_id IN NUMBER
472 ,p_responsibility_id IN NUMBER
473 ,p_wip_entity_id IN NUMBER -- data
474 ,p_organization_id IN NUMBER)
475 IS
476 l_api_name CONSTANT VARCHAR2(30) := 'create_reqs_at_wo_rel';
477 l_api_version CONSTANT NUMBER := 1.0;
478 l_request_id NUMBER;
479 l_person_id NUMBER;
480 l_material_account NUMBER;
481 l_material_variance_account NUMBER;
482 l_currency VARCHAR2(30);
483 l_project_id NUMBER;
484 l_task_id NUMBER;
485 l_location_id NUMBER;
486 l_description VARCHAR2(240);
487 l_stmt_num NUMBER;
488 l_api_return_status VARCHAR2(1);
489 l_api_msg_count NUMBER;
490 l_api_msg_data VARCHAR2(2000);
491 l_total_req_qty NUMBER;
492 l_req_for_cancel_qty_profile VARCHAR2(1);
493
494 CURSOR l_di_recs IS
495 (
496 SELECT wip_entity_id,
497 organization_id,
498 operation_seq_num as task_number,
499 to_number(null) as inventory_item_id,
500 direct_item_sequence_id,
501 1 as direct_item_type_id,
502 description,
503 required_quantity,
504 unit_price,
505 uom as uom_code,
506 purchasing_category_id,
507 need_by_date as date_required,
508 auto_request_material,
509 suggested_vendor_id,
510 suggested_vendor_name,
511 suggested_vendor_site,
512 suggested_vendor_phone,
513 suggested_vendor_item_num
514 FROM wip_eam_direct_items
515 WHERE wip_entity_id = p_wip_entity_id
516 AND organization_id = p_organization_id
517 UNION ALL
518 SELECT wro.wip_entity_id,
519 wro.organization_id,
520 wro.operation_seq_num as task_number,
521 wro.inventory_item_id,
522 to_number(null) as direct_item_sequence_id,
523 2 as direct_item_type_id,
524 msi.description,
525 wro.required_quantity,
526 wro.unit_price,
527 msi.primary_uom_code as uom_code,
528 mic.category_id as purchasing_category_id,
529 wro.date_required,
530 wro.auto_request_material,
531 vendor_id,
532 wro.suggested_vendor_name,
533 to_char(null) as suggested_vendor_site,
534 to_char(null) as suggested_vendor_phone,
535 to_char(null) as suggested_vendor_item_num
536 FROM wip_requirement_operations wro,
537 mtl_system_items_kfv msi,
538 mtl_item_categories mic ,
539 mtl_default_category_sets mdcs
540 WHERE msi.inventory_item_id = wro.inventory_item_id
541 AND msi.organization_id = wro.organization_id
542 AND nvl(msi.stock_enabled_flag, 'N') = 'N'
543 AND wro.inventory_item_id = mic.inventory_item_id
544 AND wro.organization_id = mic.organization_id
545 AND mic.category_set_id = mdcs.category_set_id
546 AND mdcs.functional_area_id = 2
547 AND wro.wip_entity_id = p_wip_entity_id
548 AND wro.organization_id = p_organization_id
549 );
550
551 BEGIN
552
553 -- Standard Start of API savepoint
554 l_stmt_num := 10;
555 SAVEPOINT create_requisition_pvt;
556
557 l_stmt_num := 20;
558 -- Standard call to check for call compatibility.
559 IF NOT fnd_api.compatible_api_call(
560 l_api_version
561 ,p_api_version
562 ,l_api_name
563 ,g_pkg_name) THEN
564 RAISE fnd_api.g_exc_unexpected_error;
565 END IF;
566
567 l_stmt_num := 30;
568 -- Initialize message list if p_init_msg_list is set to TRUE.
569 IF fnd_api.to_boolean(p_init_msg_list) THEN
570 fnd_msg_pub.initialize;
571 END IF;
572
573 l_stmt_num := 40;
574 -- Initialize API return status to success
575 x_return_status := fnd_api.g_ret_sts_success;
576
577 l_stmt_num := 50;
578
579 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||' EAM_PROCESS_WO_UTIL_PVT.create_reqs_at_wo_rel : Start ==========================================================='); END IF;
580
581 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(' EAM_PROCESS_WO_UTIL_PVT.create_reqs_at_wo_rel:PROFILE : EAM: Trigger requisition for cancelled quantity : '||FND_PROFILE.VALUE('EAM_TRIGGER_REQ_CANCEL_QTY')); END IF;
582
583 l_req_for_cancel_qty_profile := NVL(FND_PROFILE.VALUE('EAM_TRIGGER_REQ_CANCEL_QTY'),'Y'); --bug 13102446
584
585 FOR l_di_record in l_di_recs
586 LOOP
587
588 if l_di_record.wip_entity_id is not null
589 and l_di_record.auto_request_material = 'Y' then
590
591
592 IF l_di_record.direct_item_type_id = 1 THEN -- description based direct item . fix for 3421830
593
594 l_description := l_di_record.description;
595
596 IF(l_req_for_cancel_qty_profile = 'Y') then
597 --13102446 trigger requisition again for the cancelled quantity where the earlier Req/PO was cancelled
598
599 BEGIN
600 /*Querying table po_requisitions_interface_all also to avoid duplication of requisitions, added for bug #6112450*/
601 SELECT SUM(nvl(req_qty,0)) INTO l_total_req_qty
602 FROM
603 (SELECT SUM(nvl(pria.quantity,0)) req_qty
604 FROM po_requisitions_interface_all pria
605 WHERE pria.wip_entity_id =l_di_record.wip_entity_id
606 AND pria.destination_organization_id = l_di_record.organization_id
607 AND pria.wip_operation_seq_num = l_di_record.task_number
608 AND pria.item_id is null
609 AND pria.wip_resource_seq_num = l_di_record.direct_item_sequence_id
610 AND ((process_flag is null) or (Upper(Trim(process_flag)) = 'IN PROCESS'))
611
612 UNION ALL
613 SELECT SUM(nvl(prla.quantity,0)) req_qty
614 FROM po_requisition_lines_all prla , po_requisition_headers_all prha
615 WHERE prla.requisition_header_id = prha.requisition_header_id(+)
616 AND upper(NVL(prha.authorization_status, 'APPROVED') ) not in ('CANCELLED', 'REJECTED','SYSTEM_SAVED')
617 AND UPPER(NVL(prla.cancel_flag,'N')) <> 'Y'
618 AND prla.wip_entity_id =l_di_record.wip_entity_id
619 AND prla.destination_organization_id = l_di_record.organization_id
620 AND prla.wip_operation_seq_num = l_di_record.task_number
621 AND prla.item_id is null
622 AND prla.wip_resource_seq_num = l_di_record.direct_item_sequence_id
623
624 UNION ALL
625 SELECT SUM(nvl(pd.quantity_ordered,0)) req_qty
626 FROM po_distributions_all pd , po_headers_all ph,po_lines_all pl
627 WHERE pd.po_header_id = ph.po_header_id(+)
628 AND upper(NVL(ph.authorization_status, 'APPROVED') ) not in ('CANCELLED', 'REJECTED','SYSTEM_SAVED')
629 AND pd.po_line_id = pl.po_line_id(+)
630 AND UPPER(NVL(pl.cancel_flag,'N')) <> 'Y'
631 AND pd.wip_entity_id = l_di_record.wip_entity_id
632 AND pd.destination_organization_id = l_di_record.organization_id
633 AND pd.wip_operation_seq_num = l_di_record.task_number
634 AND pl.item_id is null
635 AND pd.wip_resource_seq_num = l_di_record.direct_item_sequence_id
636 AND pd.line_location_id not in(
637 SELECT nvl(prla.line_location_id,0)
638 FROM po_requisition_lines_all prla , po_requisition_headers_all prha
639 WHERE prla.requisition_header_id = prha.requisition_header_id(+)
640 AND upper(NVL(prha.authorization_status, 'APPROVED') ) not in ('CANCELLED', 'REJECTED','SYSTEM_SAVED')
641 AND UPPER(NVL(prla.cancel_flag,'N')) <> 'Y'
642 AND prla.wip_entity_id =l_di_record.wip_entity_id
643 AND prla.destination_organization_id = l_di_record.organization_id
644 AND prla.wip_operation_seq_num = l_di_record.task_number
645 AND prla.item_id is null
646 AND prla.wip_resource_seq_num = l_di_record.direct_item_sequence_id)
647 );
648 EXCEPTION
649 WHEN NO_DATA_FOUND THEN
650 l_total_req_qty := 0;
651 END;
652
653 ELSE -- Don't trigger requisition again for the cancelled quantity where the earlier Req/PO was cancelled
654
655 BEGIN
656 /*Querying table po_requisitions_interface_all also to avoid duplication of requisitions, added for bug #6112450*/
657 SELECT SUM(nvl(req_qty,0)) INTO l_total_req_qty
658 FROM
659 (SELECT SUM(nvl(pria.quantity,0)) req_qty
660 FROM po_requisitions_interface_all pria
661 WHERE pria.wip_entity_id =l_di_record.wip_entity_id
662 AND pria.destination_organization_id = l_di_record.organization_id
663 AND pria.wip_operation_seq_num = l_di_record.task_number
664 AND pria.item_id is null
665 AND pria.wip_resource_seq_num = l_di_record.direct_item_sequence_id
666 AND ((process_flag is null) or (Upper(Trim(process_flag)) = 'IN PROCESS'))
667
668 UNION ALL
669 SELECT SUM(nvl(prla.quantity,0)) req_qty
670 FROM po_requisition_lines_all prla , po_requisition_headers_all prha
671 WHERE prla.requisition_header_id = prha.requisition_header_id(+)
672 AND upper(NVL(prha.authorization_status, 'APPROVED') ) not in ('SYSTEM_SAVED')
673 AND prla.wip_entity_id =l_di_record.wip_entity_id
674 AND prla.destination_organization_id = l_di_record.organization_id
675 AND prla.wip_operation_seq_num = l_di_record.task_number
676 AND prla.item_id is null
677 AND prla.wip_resource_seq_num = l_di_record.direct_item_sequence_id
678
679 UNION ALL
680 SELECT SUM(nvl(pd.quantity_ordered,0)) req_qty
681 FROM po_distributions_all pd , po_headers_all ph,po_lines_all pl
682 WHERE pd.po_header_id = ph.po_header_id(+)
683 AND upper(NVL(ph.authorization_status, 'APPROVED') ) not in ('SYSTEM_SAVED')
684 AND pd.po_line_id = pl.po_line_id(+)
685 AND pd.wip_entity_id = l_di_record.wip_entity_id
686 AND pd.destination_organization_id = l_di_record.organization_id
687 AND pd.wip_operation_seq_num = l_di_record.task_number
688 AND pl.item_id is null
689 AND pd.wip_resource_seq_num = l_di_record.direct_item_sequence_id
690 AND pd.line_location_id not in(
691 SELECT nvl(prla.line_location_id,0)
692 FROM po_requisition_lines_all prla , po_requisition_headers_all prha
693 WHERE prla.requisition_header_id = prha.requisition_header_id(+)
694 AND upper(NVL(prha.authorization_status, 'APPROVED') ) not in ('SYSTEM_SAVED')
695 AND prla.wip_entity_id =l_di_record.wip_entity_id
696 AND prla.destination_organization_id = l_di_record.organization_id
697 AND prla.wip_operation_seq_num = l_di_record.task_number
698 AND prla.item_id is null
699 AND prla.wip_resource_seq_num = l_di_record.direct_item_sequence_id)
700 );
701 EXCEPTION
702 WHEN NO_DATA_FOUND THEN
703 l_total_req_qty := 0;
704 END;
705
706 END IF; --IF(l_req_for_cancel_qty_profile = 'Y') then
707
708 ELSE
709 --Bug4188160:pass description of item instead of item name
710 l_description := l_di_record.description;
711
712 IF(l_req_for_cancel_qty_profile = 'Y') then
713 -- trigger requisition again for the cancelled quantity where the earlier Req/PO was cancelled
714
715 BEGIN
716
717 /*Querying table po_requisitions_interface_all also to avoid duplication of requisitions, added for bug #6112450*/
718
719 SELECT SUM(nvl(req_qty,0)) INTO l_total_req_qty
720 FROM
721 (SELECT SUM(nvl(pria.quantity,0)) req_qty
722 FROM po_requisitions_interface_all pria
723 WHERE pria.wip_entity_id = l_di_record.wip_entity_id
724 AND pria.destination_organization_id = l_di_record.organization_id
725 AND pria.wip_operation_seq_num = l_di_record.task_number
726 AND pria.item_id = l_di_record.inventory_item_id
727 AND pria.wip_resource_seq_num is null
728 AND ((process_flag is null) or (Upper(Trim(process_flag)) = 'IN PROCESS'))
729
730 UNION ALL
731 SELECT SUM(nvl(prla.quantity,0)) req_qty
732 FROM po_requisition_lines_all prla , po_requisition_headers_all prha
733 WHERE prla.requisition_header_id = prha.requisition_header_id(+)
734 AND upper(NVL(prha.authorization_status, 'APPROVED') ) not in ('CANCELLED', 'REJECTED','SYSTEM_SAVED')
735 AND UPPER(NVL(prla.cancel_flag,'N')) <> 'Y'
736 AND prla.wip_entity_id = l_di_record.wip_entity_id
737 AND prla.destination_organization_id = l_di_record.organization_id
738 AND prla.wip_operation_seq_num = l_di_record.task_number
739 AND prla.item_id = l_di_record.inventory_item_id
740 AND prla.wip_resource_seq_num is null
741
742 UNION ALL
743 SELECT SUM(nvl(pd.quantity_ordered,0)) req_qty
744 FROM po_distributions_all pd , po_headers_all ph,po_lines_all pl
745 WHERE pd.po_header_id = ph.po_header_id(+)
746 AND upper(NVL(ph.authorization_status, 'APPROVED') ) not in ('CANCELLED', 'REJECTED','SYSTEM_SAVED')
747 AND UPPER(NVL(pl.cancel_flag,'N')) <> 'Y'
748 AND pd.po_line_id = pl.po_line_id(+)
749 AND pd.wip_entity_id = l_di_record.wip_entity_id
750 AND pd.destination_organization_id = l_di_record.organization_id
751 AND pd.wip_operation_seq_num = l_di_record.task_number
752 AND pl.item_id = l_di_record.inventory_item_id
753 AND pd.wip_resource_seq_num is null
754 AND pd.line_location_id not in(
755 SELECT nvl(prla.line_location_id,0)
756 FROM po_requisition_lines_all prla , po_requisition_headers_all prha
757 WHERE prla.requisition_header_id = prha.requisition_header_id(+)
758 AND upper(NVL(prha.authorization_status, 'APPROVED') ) not in ('CANCELLED', 'REJECTED','SYSTEM_SAVED')
759 AND UPPER(NVL(prla.cancel_flag,'N')) <> 'Y'
760 AND prla.wip_entity_id =l_di_record.wip_entity_id
761 AND prla.destination_organization_id = l_di_record.organization_id
762 AND prla.wip_operation_seq_num = l_di_record.task_number
763 AND pl.item_id = l_di_record.inventory_item_id
764 AND prla.wip_resource_seq_num is null)
765 );
766
767
768 EXCEPTION
769 WHEN NO_DATA_FOUND THEN
770 l_total_req_qty := 0 ;
771 END;
772
773 ELSE -- Don't trigger requisition again for the cancelled quantity where the earlier Req/PO was cancelled
774
775 BEGIN
776 /*Querying table po_requisitions_interface_all also to avoid duplication of requisitions, added for bug #6112450*/
777
778 SELECT SUM(nvl(req_qty,0)) INTO l_total_req_qty
779 FROM
780 (SELECT SUM(nvl(pria.quantity,0)) req_qty
781 FROM po_requisitions_interface_all pria
782 WHERE pria.wip_entity_id = l_di_record.wip_entity_id
783 AND pria.destination_organization_id = l_di_record.organization_id
784 AND pria.wip_operation_seq_num = l_di_record.task_number
785 AND pria.item_id = l_di_record.inventory_item_id
786 AND pria.wip_resource_seq_num is null
787 AND ((process_flag is null) or (Upper(Trim(process_flag)) = 'IN PROCESS'))
788
789 UNION ALL
790 SELECT SUM(nvl(prla.quantity,0)) req_qty
791 FROM po_requisition_lines_all prla , po_requisition_headers_all prha
792 WHERE prla.requisition_header_id = prha.requisition_header_id(+)
793 AND upper(NVL(prha.authorization_status, 'APPROVED') ) not in ('SYSTEM_SAVED')
794 AND prla.wip_entity_id = l_di_record.wip_entity_id
795 AND prla.destination_organization_id = l_di_record.organization_id
796 AND prla.wip_operation_seq_num = l_di_record.task_number
797 AND prla.item_id = l_di_record.inventory_item_id
798 AND prla.wip_resource_seq_num is null
799
800 UNION ALL
801 SELECT SUM(nvl(pd.quantity_ordered,0)) req_qty
802 FROM po_distributions_all pd , po_headers_all ph,po_lines_all pl
803 WHERE pd.po_header_id = ph.po_header_id(+)
804 AND upper(NVL(ph.authorization_status, 'APPROVED') ) not in ('SYSTEM_SAVED')
805 AND pd.po_line_id = pl.po_line_id(+)
806 AND pd.wip_entity_id = l_di_record.wip_entity_id
807 AND pd.destination_organization_id = l_di_record.organization_id
808 AND pd.wip_operation_seq_num = l_di_record.task_number
809 AND pl.item_id = l_di_record.inventory_item_id
810 AND pd.wip_resource_seq_num is null
811 AND pd.line_location_id not in(
812 SELECT nvl(prla.line_location_id,0)
813 FROM po_requisition_lines_all prla , po_requisition_headers_all prha
814 WHERE prla.requisition_header_id = prha.requisition_header_id(+)
815 AND upper(NVL(prha.authorization_status, 'APPROVED') ) not in ('SYSTEM_SAVED')
816 AND prla.wip_entity_id =l_di_record.wip_entity_id
817 AND prla.destination_organization_id = l_di_record.organization_id
818 AND prla.wip_operation_seq_num = l_di_record.task_number
819 AND pl.item_id = l_di_record.inventory_item_id
820 AND prla.wip_resource_seq_num is null)
821 );
822
823
824 EXCEPTION
825 WHEN NO_DATA_FOUND THEN
826 l_total_req_qty := 0 ;
827 END;
828
829 END IF; --IF(l_req_for_cancel_qty_profile = 'Y') then
830
831 END IF; -- IF l_di_record.direct_item_type_id = 1
832
833 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN
834 EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||' EAM_PROCESS_WO_UTIL_PVT.create_reqs_at_wo_rel : DirectItemType(1:Description/2:Non-Stock) : '||l_di_record.direct_item_type_id||', Description: '||l_description);
835 EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||' EAM_PROCESS_WO_UTIL_PVT.create_reqs_at_wo_rel : WipentityId : '||l_di_record.wip_entity_id||', Operation : '||l_di_record.task_number);
836 EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||' EAM_PROCESS_WO_UTIL_PVT.create_reqs_at_wo_rel : Inv Item Id : '||l_di_record.inventory_item_id||', Direct Item Seq Id: '||l_di_record.direct_item_sequence_id);
837 EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||' EAM_PROCESS_WO_UTIL_PVT.create_reqs_at_wo_rel : UOM : '||l_di_record.uom_code||', Unit Price : '||l_di_record.unit_price);
838 EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||' EAM_PROCESS_WO_UTIL_PVT.create_reqs_at_wo_rel : Required Quantity : '||l_di_record.required_quantity||', Available Quantity : '||nvl(l_total_req_qty,0));
839 EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||' EAM_PROCESS_WO_UTIL_PVT.create_reqs_at_wo_rel : Creating requisition for quantity : '||(l_di_record.required_quantity-nvl(l_total_req_qty,0)));
840 END IF;
841
842 create_requisition
843 ( p_api_version => 1.0
844 ,p_init_msg_list => FND_API.G_FALSE
845 ,p_commit => FND_API.G_FALSE
846 ,p_validate_only => FND_API.G_TRUE
847 ,x_return_status => l_api_return_status
848 ,x_msg_count => l_api_msg_count
849 ,x_msg_data => l_api_msg_data
850 ,p_wip_entity_id => l_di_record.wip_entity_id
851 ,p_operation_seq_num => l_di_record.task_number
852 ,p_organization_id => l_di_record.organization_id
853 ,p_user_id => p_user_id
854 ,p_responsibility_id => p_responsibility_id
855 ,p_quantity => (l_di_record.required_quantity-nvl(l_total_req_qty,0)) -- fix for 3421830
856 ,p_unit_price => l_di_record.unit_price
857 ,p_category_id => l_di_record.purchasing_category_id
858 ,p_item_description => l_description
859 ,p_uom_code => l_di_record.uom_code
860 ,p_need_by_date => l_di_record.date_required
861 ,p_inventory_item_id => l_di_record.inventory_item_id
862 ,p_direct_item_id => l_di_record.direct_item_sequence_id
863 ,p_suggested_vendor_id => l_di_record.suggested_vendor_id
864 ,p_suggested_vendor_name => l_di_record.suggested_vendor_name
865 ,p_suggested_vendor_site => l_di_record.suggested_vendor_site
866 ,p_suggested_vendor_phone => l_di_record.suggested_vendor_phone
867 ,p_suggested_vendor_item_num => l_di_record.suggested_vendor_item_num);
868
869
870
871 if nvl(l_api_return_status,'Q') <> 'S' then
872 x_return_status := fnd_api.g_ret_sts_error;
873 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN
874 EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||' EAM_PROCESS_WO_UTIL_PVT.create_reqs_at_wo_rel : Creating reqs for the above direct item finished with status '||l_api_return_status);
875 END IF;
876
877 end if;
878
879 end if;
880
881 END LOOP;
882
883 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||' EAM_PROCESS_WO_UTIL_PVT.create_reqs_at_wo_rel : End=============================================================='); END IF;
884
885
886 EXCEPTION
887
888 when others then
889
890 x_return_status := fnd_api.g_ret_sts_error;
891
892 declare
893 l_text varchar2(1000);
894 begin
895 l_text := sqlerrm;
896 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN
897 EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||' EAM_PROCESS_WO_UTIL_PVT.create_reqs_at_wo_rel : Creating reqs for direct items Error: , sqlerrm='||substrb(l_text,1,200));
898 END IF;
899 end;
900
901 END create_reqs_at_wo_rel;
902
903
904
905 PROCEDURE create_reqs_at_di_upd
906 ( p_api_version IN NUMBER := 1.0
907 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
908 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
909 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
910 ,x_return_status OUT NOCOPY VARCHAR2
911 ,x_msg_count OUT NOCOPY NUMBER
912 ,x_msg_data OUT NOCOPY VARCHAR2
913 ,p_user_id IN NUMBER
914 ,p_responsibility_id IN NUMBER
915 ,p_wip_entity_id IN NUMBER -- data
916 ,p_organization_id IN NUMBER
917 ,p_direct_item_sequence_id IN NUMBER
918 ,p_inventory_item_id IN NUMBER
919 ,p_required_quantity IN NUMBER)
920 IS
921 l_api_name CONSTANT VARCHAR2(30) := 'create_reqs_at_di_upd';
922 l_api_version CONSTANT NUMBER := 1.0;
923 l_request_id NUMBER;
924 l_person_id NUMBER;
925 l_material_account NUMBER;
926 l_material_variance_account NUMBER;
927 l_currency VARCHAR2(30);
928 l_project_id NUMBER;
929 l_task_id NUMBER;
930 l_location_id NUMBER;
931 l_stmt_num NUMBER;
932 l_api_return_status NUMBER;
933 l_api_msg_count NUMBER;
934 l_api_msg_data VARCHAR2(2000);
935 TYPE DirectItemRec IS RECORD (
936 wip_entity_id eam_direct_item_recs_v.wip_entity_id%TYPE,
937 organization_id eam_direct_item_recs_v.organization_id%TYPE,
938 task_number eam_direct_item_recs_v.task_number%TYPE,
939 inventory_item_id eam_direct_item_recs_v.inventory_item_id%TYPE,
940 direct_item_sequence_id eam_direct_item_recs_v.direct_item_sequence_id%TYPE,
941 direct_item_type_id eam_direct_item_recs_v.direct_item_type_id%TYPE,
942 description eam_direct_item_recs_v.description%TYPE,
943 required_quantity eam_direct_item_recs_v.required_quantity%TYPE,
944 unit_price eam_direct_item_recs_v.unit_price%TYPE,
945 uom_code eam_direct_item_recs_v.uom_code%TYPE,
946 purchasing_category_id eam_direct_item_recs_v.purchasing_category_id%TYPE,
947 date_required eam_direct_item_recs_v.date_required%TYPE,
948 auto_request_material eam_direct_item_recs_v.auto_request_material%TYPE,
949 suggested_vendor_id eam_direct_item_recs_v.suggested_vendor_id%TYPE,
950 suggested_vendor_name eam_direct_item_recs_v.suggested_vendor_name%TYPE,
951 suggested_vendor_site eam_direct_item_recs_v.suggested_vendor_site%TYPE,
952 suggested_vendor_phone eam_direct_item_recs_v.suggested_vendor_phone%TYPE,
953 suggested_vendor_item_num eam_direct_item_recs_v.suggested_vendor_item_num%TYPE );
954
955 TYPE l_di_recs_type IS REF CURSOR RETURN DirectItemRec;
956 l_di_recs l_di_recs_type;
957 l_di_record l_di_recs%ROWTYPE;
958
959 BEGIN
960 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||' EAM_PROCESS_WO_UTIL_PVT.create_reqs_at_di_upd : Start========================================'); END IF;
961 -- Standard Start of API savepoint
962 l_stmt_num := 10;
963 SAVEPOINT create_requisition_pvt;
964
965 l_stmt_num := 20;
966 -- Standard call to check for call compatibility.
967 IF NOT fnd_api.compatible_api_call(
968 l_api_version
969 ,p_api_version
970 ,l_api_name
971 ,g_pkg_name) THEN
972 RAISE fnd_api.g_exc_unexpected_error;
973 END IF;
974
975 l_stmt_num := 30;
976 -- Initialize message list if p_init_msg_list is set to TRUE.
977 IF fnd_api.to_boolean(p_init_msg_list) THEN
978 fnd_msg_pub.initialize;
979 END IF;
980
981 l_stmt_num := 40;
982 -- Initialize API return status to success
983 x_return_status := fnd_api.g_ret_sts_success;
984
985 l_stmt_num := 50;
986
987 -- API body
988
989 IF NOT l_di_recs%ISOPEN THEN
990
991 IF p_direct_item_sequence_id is not null then
992
993 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||' EAM_PROCESS_WO_UTIL_PVT.create_reqs_at_di_upd : Description Direct Item Update'); END IF;
994
995 OPEN l_di_recs FOR
996 SELECT wip_entity_id,
997 organization_id,
998 operation_seq_num as task_number,
999 to_number(null) as inventory_item_id,
1000 direct_item_sequence_id,
1001 1 as direct_item_type_id,
1002 description,
1003 required_quantity,
1004 unit_price,
1005 uom as uom_code,
1006 purchasing_category_id,
1007 need_by_date as date_required,
1008 auto_request_material,
1009 SUGGESTED_VENDOR_ID,
1010 suggested_vendor_name,
1011 suggested_vendor_site,
1012 suggested_vendor_phone,
1013 suggested_vendor_item_num
1014 FROM wip_eam_direct_items
1015 WHERE wip_entity_id = p_wip_entity_id
1016 AND organization_id = p_organization_id
1017 AND direct_item_sequence_id = p_direct_item_sequence_id;
1018
1019 ELSIF p_inventory_item_id is not null then
1020
1021 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||' EAM_PROCESS_WO_UTIL_PVT.create_reqs_at_di_upd : Non-Stock Direct Item Update'); END IF;
1022
1023 OPEN l_di_recs FOR
1024 SELECT wro.wip_entity_id,
1025 wro.organization_id,
1026 wro.operation_seq_num as task_number,
1027 wro.inventory_item_id,
1028 to_number(null) as direct_item_sequence_id,
1029 2 as direct_item_type_id,
1030 msi.description,
1031 wro.required_quantity,
1032 wro.unit_price,
1033 msi.primary_uom_code as uom_code,
1034 mic.category_id as purchasing_category_id,
1035 wro.date_required,
1036 wro.auto_request_material,
1037 vendor_id,
1038 wro.suggested_vendor_name,
1039 to_char(null) as suggested_vendor_site,
1040 to_char(null) as suggested_vendor_phone,
1041 to_char(null) as suggested_vendor_item_num
1042 FROM wip_requirement_operations wro,
1043 mtl_system_items_kfv msi,
1044 mtl_item_categories mic ,
1045 mtl_default_category_sets mdcs
1046 WHERE msi.inventory_item_id = wro.inventory_item_id
1047 AND msi.organization_id = wro.organization_id
1048 AND nvl(msi.stock_enabled_flag, 'N') = 'N'
1049 AND wro.inventory_item_id = mic.inventory_item_id
1050 AND wro.organization_id = mic.organization_id
1051 AND mic.category_set_id = mdcs.category_set_id
1052 AND mdcs.functional_area_id = 2
1053 AND wro.wip_entity_id = p_wip_entity_id
1054 AND wro.organization_id = p_organization_id
1055 and wro.inventory_item_id = p_inventory_item_id;
1056 end if;
1057
1058 END IF;
1059
1060 LOOP
1061
1062 FETCH l_di_recs INTO l_di_record;
1063 EXIT WHEN l_di_recs%NOTFOUND;
1064
1065 if l_di_record.wip_entity_id is not null
1066 and l_di_record.auto_request_material = 'Y' then
1067
1068 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN
1069 EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||' EAM_PROCESS_WO_UTIL_PVT.create_reqs_at_di_upd : Direct Item Type(1:Description Direct /2:Non-Stock Direct ) : '||l_di_record.direct_item_type_id);
1070 EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||' EAM_PROCESS_WO_UTIL_PVT.create_reqs_at_di_upd : WipentityId: '||l_di_record.wip_entity_id||' Operation : '||l_di_record.task_number);
1071 EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||' EAM_PROCESS_WO_UTIL_PVT.create_reqs_at_di_upd : Inv Item Id: '||l_di_record.inventory_item_id||' Dir Item Seq id: '||l_di_record.direct_item_sequence_id);
1072 EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||' EAM_PROCESS_WO_UTIL_PVT.create_reqs_at_di_upd : UOM : '||l_di_record.uom_code||' Unit Price : '||l_di_record.unit_price);
1073 EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||' EAM_PROCESS_WO_UTIL_PVT.create_reqs_at_di_upd : To be Requested Quantity : '||p_required_quantity||' , Description : '||l_di_record.description);
1074 END IF;
1075
1076 --Bug4188160:pass description of item instead of item name
1077 create_requisition
1078 ( p_api_version => 1.0
1079 ,p_init_msg_list => FND_API.G_FALSE
1080 ,p_commit => FND_API.G_FALSE
1081 ,p_validate_only => FND_API.G_TRUE
1082 ,x_return_status => l_api_return_status
1083 ,x_msg_count => l_api_msg_count
1084 ,x_msg_data => l_api_msg_data
1085 ,p_wip_entity_id => l_di_record.wip_entity_id
1086 ,p_operation_seq_num => l_di_record.task_number
1087 ,p_organization_id => l_di_record.organization_id
1088 ,p_user_id => p_user_id
1089 ,p_responsibility_id => p_responsibility_id
1090 ,p_quantity => p_required_quantity
1091 ,p_unit_price => l_di_record.unit_price
1092 ,p_category_id => l_di_record.purchasing_category_id
1093 ,p_item_description => l_di_record.description
1094 ,p_uom_code => l_di_record.uom_code
1095 ,p_need_by_date => l_di_record.date_required
1096 ,p_inventory_item_id => l_di_record.inventory_item_id
1097 ,p_direct_item_id => l_di_record.direct_item_sequence_id
1098 ,p_suggested_vendor_id => l_di_record.suggested_vendor_id
1099 ,p_suggested_vendor_name => l_di_record.suggested_vendor_name
1100 ,p_suggested_vendor_site => l_di_record.suggested_vendor_site
1101 ,p_suggested_vendor_phone => l_di_record.suggested_vendor_phone
1102 ,p_suggested_vendor_item_num => l_di_record.suggested_vendor_item_num);
1103
1104
1105 if nvl(l_api_return_status,'Q') <> 'S' then
1106 x_return_status := fnd_api.g_ret_sts_error;
1107 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||' EAM_PROCESS_WO_UTIL_PVT.create_reqs_at_di_upd : create_requisition return status : '||l_api_return_status); END IF;
1108
1109 end if;
1110
1111 end if;
1112
1113 END LOOP;
1114 CLOSE l_di_recs;
1115
1116 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||' EAM_PROCESS_WO_UTIL_PVT.create_reqs_at_di_upd : End================================================='); END IF;
1117
1118 EXCEPTION
1119
1120 when others then
1121
1122 x_return_status := fnd_api.g_ret_sts_error;
1123
1124 declare
1125 l_text varchar2(1000);
1126 begin
1127 l_text := sqlerrm;
1128 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN
1129 EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||' EAM_PROCESS_WO_UTIL_PVT.create_reqs_at_di_upd : Creating reqs for direct items Error: , sqlerrm='||substrb(l_text,1,200));
1130 END IF;
1131 end;
1132 END create_reqs_at_di_upd;
1133
1134
1135
1136 END EAM_PROCESS_WO_UTIL_PVT;
[Home] [Help]