[Home] [Help]
PACKAGE BODY: APPS.WIP_OSP_SHP_I_WF
Source
1 PACKAGE BODY wip_osp_shp_i_wf AS
2 /*$Header: wiposhib.pls 120.6.12020000.2 2012/07/18 18:14:17 ankohli ship $ */
3
4 PROCEDURE SetStartupWFAttributes
5 ( p_itemtype in varchar2 default null
6 , p_itemkey in varchar2
7 , p_wip_entity_id in number
8 , p_rep_sched_id in number
9 , p_organization_id in number
10 , p_primary_qty in number
11 , p_primary_uom in varchar2
12 , p_op_seq_num in number
13 , p_user_id in number
14 , p_resp_id in number
15 , p_resp_appl_id in number
16 , p_security_group_id in number) is
17
18 l_wip_entity_name VARCHAR2(240);
19 l_wip_entity_type NUMBER;
20 l_line_name VARCHAR2(10);
21 l_primary_uom VARCHAR2(25);
22 l_primary_item_name VARCHAR2(81);
23 l_primary_item_desc VARCHAR2(240);
24 l_osp_item VARCHAR2(81);
25 l_osp_item_id NUMBER;
26 l_osp_item_desc VARCHAR2(240);
27 l_req_import VARCHAR2(20); --Fix for bug 8919025(Fp 8850950)
28 l_org_acct_ctxt VARCHAR2(30):= 'Accounting Information'; --Fix for bug 8919025 (FP 8850950)
29 l_ou_id number; --Fix for bug 8919025 (FP 8850950)
30 l_init_reqappr VARCHAR2(1);/*ER 4276433*/
31 begin
32 select we.wip_entity_name
33 , we.entity_type
34 , msik.concatenated_segments
35 , msik.description
36 , br.purchase_item_id
37 , msik2.concatenated_segments
38 , msik2.description
39 into l_wip_entity_name
40 , l_wip_entity_type
41 , l_primary_item_name
42 , l_primary_item_desc
43 , l_osp_item_id
44 , l_osp_item
45 , l_osp_item_desc
46 from wip_entities we
47 , wip_operation_resources wor
48 , bom_resources br
49 , mtl_system_items_kfv msik
50 , mtl_system_items_kfv msik2
51 where we.wip_entity_id = p_wip_entity_id
52 and we.organization_id = p_organization_id
53 and msik.inventory_item_id(+) = we.primary_item_id
54 and msik.organization_id(+) = we.organization_id
55 and wor.wip_entity_id = we.wip_entity_id
56 and wor.organization_id = we.organization_id
57 and nvl(wor.repetitive_schedule_id, -1) = nvl(p_rep_sched_id, -1)
58 and wor.operation_seq_num = p_op_seq_num
59 and wor.autocharge_type = WIP_CONSTANTS.PO_MOVE
60 and br.resource_id = wor.resource_id
61 and br.organization_id = wor.organization_id
62 and msik2.inventory_item_id = br.purchase_item_id
63 and msik2.organization_id = br.organization_id;
64 /*
65 select unit_of_measure
66 into l_primary_uom
67 from mtl_units_of_measure
68 where uom_code = p_primary_uom;
69 */
70 wf_engine.SetItemAttrNumber( itemtype => p_itemtype
71 , itemkey => p_itemkey
72 , aname => 'WIP_ENTITY_ID'
73 , avalue => p_wip_entity_id);
74
75 wf_engine.SetItemAttrNumber( itemtype => p_itemtype
76 , itemkey => p_itemkey
77 , aname => 'REP_SCHEDULE_ID'
78 , avalue => p_rep_sched_id);
79
80 wf_engine.SetItemAttrNumber( itemtype => p_itemtype
81 , itemkey => p_itemkey
82 , aname => 'ORGANIZATION_ID'
83 , avalue => p_organization_id);
84
85 IF l_wip_entity_type = WIP_CONSTANTS.DISCRETE or
86 l_wip_entity_type = WIP_CONSTANTS.CLOSED_DISC THEN
87
88 wf_engine.SetItemAttrText( itemtype => p_itemtype
89 , itemkey => p_itemkey
90 , aname => 'JOB_NAME'
91 , avalue => l_wip_entity_name);
92 elsif l_wip_entity_type = WIP_CONSTANTS.REPETITIVE THEN
93 select wl.line_code
94 into l_line_name
95 from wip_lines wl
96 , wip_repetitive_schedules wrs
97 where wrs.repetitive_schedule_id = p_rep_sched_id
98 and wrs.organization_id = p_organization_id
99 and wl.line_id = wrs.line_id
100 and wl.organization_id = wrs.organization_id;
101
102 wf_engine.SetItemAttrText( itemtype => p_itemtype
103 , itemkey => p_itemkey
104 , aname => 'LINE_NAME'
105 , avalue => l_line_name);
106 END IF;
107
108 wf_engine.SetItemAttrText( itemtype => p_itemtype
109 , itemkey => p_itemkey
110 , aname => 'ASSY'
111 , avalue => l_primary_item_name);
112
113 wf_engine.SetItemAttrText( itemtype => p_itemtype
114 , itemkey => p_itemkey
115 , aname => 'ASSY_DESC'
116 , avalue => l_primary_item_desc);
117
118 wf_engine.SetItemAttrNumber( itemtype => p_itemtype
119 , itemkey => p_itemkey
120 , aname => 'PRIMARY_QTY'
121 , avalue => p_primary_qty);
122
123 wf_engine.SetItemAttrText( itemtype => p_itemtype
124 , itemkey => p_itemkey
125 , aname => 'PRIMARY_UOM'
126 , avalue => p_primary_uom);
127
128 wf_engine.SetItemAttrNumber( itemtype => p_itemtype
129 , itemkey => p_itemkey
130 , aname => 'WIP_OP_SEQ'
131 , avalue => p_op_seq_num);
132
133 wf_engine.SetItemAttrNumber( itemtype => p_itemtype
134 , itemkey => p_itemkey
135 , aname => 'OSP_ITEM_ID'
136 , avalue => l_osp_item_id);
137
138 wf_engine.SetItemAttrText( itemtype => p_itemtype
139 , itemkey => p_itemkey
140 , aname => 'OSP_ITEM'
141 , avalue => l_osp_item);
142
143 wf_engine.SetItemAttrText( itemtype => p_itemtype
144 , itemkey => p_itemkey
145 , aname => 'OSP_ITEM_DESC'
146 , avalue => l_osp_item_desc);
147
148 wf_engine.SetItemAttrNumber( itemtype => p_itemtype
149 , itemkey => p_itemkey
150 , aname => 'USER_ID'
151 , avalue => p_user_id);
152
153 wf_engine.SetItemAttrNumber( itemtype => p_itemtype
154 , itemkey => p_itemkey
155 , aname => 'RESP_ID'
156 , avalue => p_resp_id);
157
158 wf_engine.SetItemAttrNumber( itemtype => p_itemtype
159 , itemkey => p_itemkey
160 , aname => 'RESP_APPL_ID'
161 , avalue => p_resp_appl_id);
162
163 wf_engine.SetItemAttrNumber( itemtype => p_itemtype
164 , itemkey => p_itemkey
165 , aname => 'SECURITY_GROUP_ID'
166 , avalue => p_security_group_id);
167
168 /*Start of Fix for bug 8919025 (FP 8850950)*/
169 BEGIN
170
171 /*retrieve the value of operating unit*/
172 select to_number(ORG_INFORMATION3) into l_ou_id
173 from HR_ORGANIZATION_INFORMATION
174 where ORGANIZATION_ID = p_organization_id
175 and ORG_INFORMATION_CONTEXT = l_org_acct_ctxt;
176
177 select reqimport_group_by_code
178 into l_req_import
179 from po_system_parameters_all
180 where org_id = l_ou_id;
181
182 EXCEPTION
183 WHEN NO_DATA_FOUND THEN
184 raise fnd_api.g_exc_unexpected_error;
185 END;
186
187 /*Set the REQ_GROUP_BY attribute which is the "Group By" parameter
188 of Requisition Import Concurrent Program*/
189 wf_engine.SetItemAttrText( itemtype => p_itemtype
190 , itemkey => p_itemkey
191 , aname => 'REQ_GROUP_BY'
192 , avalue => l_req_import);
193 /* End of Fix for bug 8919025 (FP 8850950)*/
194
195 /*ER 4276433*/
196 if(fnd_profile.value('WIP_OSP_INITIATE_REQAPPR') = WIP_CONSTANTS.NO) then
197 l_init_reqappr := 'N';
198 else
199 l_init_reqappr := 'Y';
200 end if;
201
202 wf_engine.SetItemAttrText( itemtype => p_itemtype
203 , itemkey => p_itemkey
204 , aname => 'INIT_REQAPPR'
205 , avalue => l_init_reqappr);
206 /*End of ER 4276433*/
207 exception
208 when others then
209 wf_core.context('WIP_OSP_SHP_I_WF', 'SetStartupWFAttributes', p_itemtype, p_itemkey);
210 raise;
211 END SetStartupWFAttributes;
212
213
214
215 PROCEDURE startWFProcess ( p_itemtype in varchar2
216 , p_itemkey in out nocopy varchar2
217 , p_workflow_process in varchar2
218 , p_wip_entity_id in number
219 , p_rep_sched_id in number
220 , p_organization_id in number
221 , p_primary_qty in number
222 , p_primary_uom in varchar2
223 , p_op_seq_num in number) is
224
225 l_user_id NUMBER;
226 l_resp_id NUMBER;
227 l_resp_appl_id NUMBER;
228 l_security_group_id NUMBER;
229
230 begin
231
232 if NOT wip_common_wf_pkg.OSPEnabled then
233 return;
234 end if;
235 /* Commented out for bug fix 6501679
236 l_user_id := fnd_profile.value('USER_ID');
237 l_resp_id := fnd_profile.value('RESP_ID');
238 l_resp_appl_id := fnd_profile.value('RESP_APPL_ID');
239 l_security_group_id := fnd_profile.value('SECURITY_GROUP_ID');
240 */
241
242 -- Added for bugfix 6501679 . Used fnd_global package to get the
243 -- user information.
244
245 l_user_id := fnd_global.user_id ;
246 l_resp_id := fnd_global.resp_id ;
247 l_resp_appl_id := fnd_global.resp_appl_id ;
248 l_security_group_id := fnd_global.security_group_id ;
249
250
251 if p_itemkey is null then
252 select to_char(wip_workflow_s.nextval)
253 into p_itemkey
254 from dual;
255 end if;
256
257 wf_engine.CreateProcess( itemtype => p_itemtype
258 , itemkey => p_itemkey
259 , process => p_workflow_process );
260
261 SetStartupWFAttributes ( p_itemtype => p_itemtype
262 , p_itemkey => p_itemkey
263 , p_wip_entity_id => p_wip_entity_id
264 , p_rep_sched_id => p_rep_sched_id
265 , p_organization_id => p_organization_id
266 , p_primary_qty => p_primary_qty
267 , p_primary_uom => p_primary_uom
268 , p_op_seq_num => p_op_seq_num
269 , p_user_id => l_user_id
270 , p_resp_id => l_resp_id
271 , p_resp_appl_id => l_resp_appl_id
272 , p_security_group_id => l_security_group_id);
273
274 wf_engine.StartProcess( itemtype => p_itemtype,
275 itemkey => p_itemkey );
276
277 /* May need to add the following line in, to prevent any bottleneck */
278 /* wf_engine.threshold := -1;*/
279 exception
280 when others then
281 wf_core.context('WIP_OSP_SHP_I_WF', 'startWFProcess', p_itemtype, p_itemkey);
282 raise;
283
284 END startWFProcess;
285
286
287 PROCEDURE GetStartupWFAttributes
288 ( p_itemtype in varchar2 default null
289 , p_itemkey in varchar2
290 , p_wip_entity_id out nocopy number
291 , p_rep_sched_id out nocopy number
292 , p_organization_id out nocopy number
293 , p_primary_qty out nocopy number
294 , p_primary_uom out nocopy varchar2
295 , p_osp_operation out nocopy number
296 , p_user_id out nocopy number
297 , p_resp_id out nocopy number
298 , p_resp_appl_id out nocopy number
299 , p_security_group_id out nocopy number) is
300 begin
301
302 p_wip_entity_id :=
303 wf_engine.GetItemAttrText( p_itemtype
304 , p_itemkey
305 , 'WIP_ENTITY_ID');
306 p_rep_sched_id :=
307 wf_engine.GetItemAttrNumber( p_itemtype
308 , p_itemkey
309 , 'REP_SCHEDULE_ID');
310 p_organization_id :=
311 wf_engine.GetItemAttrNumber( p_itemtype
312 , p_itemkey
313 , 'ORGANIZATION_ID');
314 p_primary_qty :=
315 wf_engine.GetItemAttrNumber( p_itemtype
316 , p_itemkey
317 , 'PRIMARY_QTY');
318 p_primary_uom :=
319 wf_engine.GetItemAttrText( p_itemtype
320 , p_itemkey
321 , 'PRIMARY_UOM');
322 p_osp_operation :=
323 wf_engine.GetItemAttrText( p_itemtype
324 , p_itemkey
325 , 'WIP_OP_SEQ');
326 p_user_id :=
327 wf_engine.GetItemAttrNumber( p_itemtype
328 , p_itemkey
329 , 'USER_ID');
330 p_resp_id :=
331 wf_engine.GetItemAttrNumber( p_itemtype
332 , p_itemkey
333 , 'RESP_ID');
334 p_resp_appl_id :=
335 wf_engine.GetItemAttrNumber( p_itemtype
336 , p_itemkey
337 , 'RESP_APPL_ID');
338 p_security_group_id :=
339 wf_engine.GetItemAttrNumber( p_itemtype
340 , p_itemkey
341 , 'SECURITY_GROUP_ID');
342 exception
343 when others then
344 wf_core.context('WIP_OSP_SHP_I_WF', 'GetStartupWFAttributes', p_itemtype, p_itemkey);
345 raise;
346
347 end GetStartupWFAttributes;
348
349
350 PROCEDURE GetReqImport
351 ( itemtype in varchar2
352 , itemkey in varchar2
353 , actid in number
354 , funcmode in varchar2
355 , resultout out nocopy varchar2) is
356
357 l_wip_entity_id NUMBER;
358 l_user_id NUMBER;
359 l_resp_id NUMBER;
360 l_resp_appl_id NUMBER;
361 l_security_group_id NUMBER;
362 l_result VARCHAR2(20);
363
364 cursor req_to_import (p_wip_entity_id number) IS
365 select 'REQ EXISTS'
366 from po_requisitions_interface_all
367 where wip_entity_id = p_wip_entity_id;
368
369 begin
370
371 l_wip_entity_id :=
372 wf_engine.GetItemAttrNumber ( itemtype
373 , itemkey
374 , 'WIP_ENTITY_ID');
375
376 open req_to_import(l_wip_entity_id);
377 fetch req_to_import into l_result;
378
379 IF (req_to_import%NOTFOUND) then
380 resultout := 'COMPLETE:N';
381 ELSE
382 l_user_id :=
383 wf_engine.GetItemAttrNumber( itemtype
384 , itemkey
385 , 'USER_ID');
386 l_resp_id :=
387 wf_engine.GetItemAttrNumber( itemtype
388 , itemkey
389 , 'RESP_ID');
390 l_resp_appl_id :=
391 wf_engine.GetItemAttrNumber( itemtype
392 , itemkey
393 , 'RESP_APPL_ID');
394 l_security_group_id :=
395 wf_engine.GetItemAttrNumber( itemtype
396 , itemkey
397 , 'SECURITY_GROUP_ID');
398
399 fnd_global.apps_initialize (
400 user_id => l_user_id,
401 resp_id => l_resp_id,
402 resp_appl_id => l_resp_appl_id ,
403 security_group_id => l_security_group_id);
404
405 resultout := 'COMPLETE:Y';
406 END IF;
407
408 exception
409 when others then
410 wf_core.context('WIP_OSP_SHP_I_WF', 'GetReqImport', itemtype, itemkey);
411 raise;
412
413 END GetReqImport;
414
415
416 PROCEDURE GetPOData
417 ( p_itemtype in varchar2
418 , p_itemkey in varchar2
419 , p_rec_num in number
420 , p_buyer out nocopy varchar2
421 , p_po_number out nocopy varchar2
422 , p_po_header_id out nocopy number
423 , p_po_distribution_id out nocopy number
424 , p_org_id out nocopy number
425 , p_po_line_qty out nocopy number
426 , p_po_line_uom out nocopy varchar2
427 , p_subcontractor out nocopy varchar2
428 , p_subcontractor_site out nocopy varchar2) is
429
430 l_record_number NUMBER := NULL;
431
432 begin
433
434 IF p_rec_num > 1 THEN
435 l_record_number := p_rec_num;
436 END IF;
437
438 p_buyer :=
439 wf_engine.GetItemAttrText ( p_itemtype
440 , p_itemkey
441 , 'BUYER' || l_record_number);
442 p_po_number :=
443 wf_engine.GetItemAttrText ( p_itemtype
444 , p_itemkey
445 , 'PO_NUM' || l_record_number);
446 p_po_header_id :=
447 wf_engine.GetItemAttrNumber ( p_itemtype
448 , p_itemkey
449 , 'PO_HEADER_ID' || l_record_number);
450 p_po_distribution_id :=
451 wf_engine.GetItemAttrNumber ( p_itemtype
452 , p_itemkey
453 , 'PO_DISTRIBUTION_ID' || l_record_number);
454 p_org_id :=
455 wf_engine.GetItemAttrNumber ( p_itemtype
456 , p_itemkey
457 , 'ORG_ID' || l_record_number);
458 p_po_line_qty :=
459 wf_engine.GetItemAttrNumber ( p_itemtype
460 , p_itemkey
461 , 'PO_LINE_QTY' || l_record_number);
462 p_po_line_uom :=
463 wf_engine.GetItemAttrText ( p_itemtype
464 , p_itemkey
465 , 'PO_UOM' || l_record_number);
466 p_subcontractor :=
467 wf_engine.GetItemAttrText ( p_itemtype
468 , p_itemkey
469 , 'SUBCONTRACTOR' || l_record_number);
470 p_subcontractor_site :=
471 wf_engine.GetItemAttrText ( p_itemtype
472 , p_itemkey
473 , 'SUBCONTRACTOR_SITE' || l_record_number);
474 exception
475 when others then
476 wf_core.context('WIP_OSP_SHP_I_WF', 'GetPOData', p_itemtype, p_itemkey);
477 raise;
478
479 end GetPOData;
480
481
482 PROCEDURE SetPOData
483 ( p_itemtype in varchar2
484 , p_itemkey in varchar2
485 , p_rec_num in number
486 , p_buyer in varchar2
487 , p_po_number in varchar2
488 , p_po_header_id in number
489 , p_po_distribution_id in number
490 , p_org_id in number
491 , p_po_line_qty in number
492 , p_po_line_uom in varchar2
493 , p_subcontractor in varchar2
494 , p_subcontractor_site in varchar2
495 , p_required_assy_qty in number default null
496 , p_create_new_attr in boolean default true) is
497
498 l_record_number NUMBER := NULL;
499
500 begin
501 IF p_rec_num > 1 THEN
502 l_record_number := p_rec_num;
503 END IF;
504
505 wf_engine.SetItemAttrNumber( itemtype => p_itemtype,
506 itemkey => p_itemkey,
507 aname => 'PO_HEADER_ID' || l_record_number,
508 avalue => p_po_header_id);
509
510 wf_engine.SetItemAttrNumber( itemtype => p_itemtype,
511 itemkey => p_itemkey,
512 aname => 'PO_DISTRIBUTION_ID'|| l_record_number,
513 avalue => p_po_distribution_id);
514
515 wf_engine.SetItemAttrNumber( itemtype => p_itemtype,
516 itemkey => p_itemkey,
517 aname => 'ORG_ID' || l_record_number,
518 avalue => p_org_id);
519
520 wf_engine.SetItemAttrText( itemtype => p_itemtype,
521 itemkey => p_itemkey,
522 aname => 'BUYER' || l_record_number,
523 avalue => p_buyer);
524
525 wf_engine.SetItemAttrText( itemtype => p_itemtype,
526 itemkey => p_itemkey,
527 aname => 'SUBCONTRACTOR_SITE' || l_record_number,
528 avalue => p_subcontractor_site);
529
530 wf_engine.SetItemAttrText( itemtype => p_itemtype,
531 itemkey => p_itemkey,
532 aname => 'PO_NUM' || l_record_number,
533 avalue => p_po_number);
534
535 wf_engine.SetItemAttrNumber( itemtype => p_itemtype,
536 itemkey => p_itemkey,
537 aname => 'PO_LINE_QTY' || l_record_number,
538 avalue => p_po_line_qty);
539
540 wf_engine.SetItemAttrText( itemtype => p_itemtype,
541 itemkey => p_itemkey,
542 aname => 'PO_UOM' || l_record_number,
543 avalue => p_po_line_uom);
544
545 wf_engine.SetItemAttrText( itemtype => p_itemtype,
546 itemkey => p_itemkey,
547 aname => 'SUBCONTRACTOR' || l_record_number,
548 avalue => p_subcontractor);
549 wf_engine.SetItemAttrText( itemtype => p_itemtype,
550 itemkey => p_itemkey,
551 aname => 'REQUIRED_ASSY_QTY' ||l_record_number,
552 avalue => p_required_assy_qty);
553
554 exception
555 when others then
556 wf_core.context('WIP_OSP_SHP_I_WF', 'SetPOData', p_itemtype, p_itemkey);
557 raise;
558
559 END SetPOData;
560
561 PROCEDURE MultiplePO ( itemtype in varchar2,
562 itemkey in varchar2,
563 actid in number,
564 funcmode in varchar2,
565 resultout out nocopy varchar2) is
566
567 cursor cget_approved_pos (l_wip_entity_id number,
568 l_rep_sched_id number,
569 l_osp_operation_num number
570 ) is
571 select ph.segment1 po_num,
572 ph.po_header_id po_header_id,
573 pd.po_distribution_id po_distribution_id,
574 pd.org_id org_id,
575 pd.quantity_ordered po_line_qty,
576 pl.unit_meas_lookup_code po_line_uom,
577 pv.vendor_name subcontractor,
578 pvs.vendor_site_code subcontractor_site,
579 ph.approved_flag approved_flag,
580 0 release_num,
581 decode(msi.outside_operation_uom_type,
582 'RESOURCE', decode(wor.basis_type,
583 WIP_CONSTANTS.PER_ITEM,
584 round(pd.quantity_ordered /wor.usage_rate_or_amount,
585 WIP_CONSTANTS.INV_MAX_PRECISION),
586 round(wo.scheduled_quantity,
587 WIP_CONSTANTS.INV_MAX_PRECISION)),
588 'ASSEMBLY', decode(wor.basis_type,
589 WIP_CONSTANTS.PER_ITEM,
590 round(pd.quantity_ordered,
591 WIP_CONSTANTS.INV_MAX_PRECISION),
592 round(wo.scheduled_quantity,
593 WIP_CONSTANTS.INV_MAX_PRECISION))) required_assy_qty
594 from po_headers_all ph,
595 po_lines_all pl,
596 po_distributions_all pd,
597 po_vendors pv,
598 po_vendor_sites_all pvs,
599 wip_operation_resources wor,
600 wip_operations wo,
601 mtl_system_items msi
602 where pd.wip_entity_id = l_wip_entity_id
603 and pd.wip_operation_seq_num = l_osp_operation_num
604 and nvl(pd.wip_repetitive_schedule_id, -1) = nvl(l_rep_sched_id, -1)
605 and pd.po_header_id = ph.po_header_id
606 --and ph.approved_flag = 'Y'
607 and ph.type_lookup_code = 'STANDARD'
608 and nvl(ph.cancel_flag, 'N') = 'N'
609 and pl.po_line_id = pd.po_line_id
610 and pl.po_header_id = pd.po_header_id
611 and pv.vendor_id = ph.vendor_id
612 and pvs.vendor_site_id = ph.vendor_site_id
613 and pvs.org_id = ph.org_id
614 and pd.wip_entity_id = wo.wip_entity_id
615 and pd.destination_organization_id = wo.organization_id
616 and pd.wip_operation_seq_num = wo.operation_seq_num
617 and (pd.wip_repetitive_schedule_id is null or
618 pd.wip_repetitive_schedule_id = wo.repetitive_schedule_id)
619 and pl.item_id = msi.inventory_item_id
620 -- Fixed bug 4411247. Join msi to pd.destination_organization_id instead
621 -- of pl.org_id because pl.org_id store operating unit organization, not
622 -- item organization.
623 and pd.destination_organization_id = msi.organization_id
624 and pd.wip_entity_id = wor.wip_entity_id
625 and pd.wip_operation_seq_num = wor.operation_seq_num
626 and pd.wip_resource_seq_num = wor.resource_seq_num
627 and pd.destination_organization_id = wor.organization_id
628 and (pd.wip_repetitive_schedule_id is null or
629 pd.wip_repetitive_schedule_id =wor.repetitive_schedule_id)
630 and wor.autocharge_type = WIP_CONSTANTS.PO_MOVE
631 union all
632 select ph.segment1||'-'||pr.RELEASE_NUM po_num,
633 ph.po_header_id po_header_id,
634 pd.po_distribution_id po_distribution_id,
635 pd.org_id org_id,
636 pd.quantity_ordered po_line_qty,
637 pl.unit_meas_lookup_code po_line_uom,
638 pv.vendor_name subcontractor,
639 pvs.vendor_site_code subcontractor_site,
640 pr.approved_flag approved_flag,
641 pr.release_num,
642 decode(msi.outside_operation_uom_type,
643 'RESOURCE', decode(wor.basis_type,
644 WIP_CONSTANTS.PER_ITEM,
645 round(pd.quantity_ordered /wor.usage_rate_or_amount,
646 WIP_CONSTANTS.INV_MAX_PRECISION),
647 round(wo.scheduled_quantity,
648 WIP_CONSTANTS.INV_MAX_PRECISION)),
649 'ASSEMBLY', decode(wor.basis_type,
650 WIP_CONSTANTS.PER_ITEM,
651 round(pd.quantity_ordered,
652 WIP_CONSTANTS.INV_MAX_PRECISION),
653 round(wo.scheduled_quantity,
654 WIP_CONSTANTS.INV_MAX_PRECISION))) required_assy_qty
655 from po_releases_all pr,
656 po_headers_all ph,
657 po_lines_all pl,
658 po_line_locations_all ps,
659 po_distributions_all pd,
660 po_vendors pv,
661 po_vendor_sites_all pvs,
662 wip_operation_resources wor,
663 wip_operations wo,
664 mtl_system_items msi
665 where ph.type_lookup_code = 'BLANKET'
666 and pd.wip_entity_id = l_wip_entity_id
667 and pd.wip_operation_seq_num = l_osp_operation_num
668 and nvl(pd.wip_repetitive_schedule_id, -1) = nvl(l_rep_sched_id, -1)
669 and pd.po_header_id = ph.po_header_id
670 and pr.po_release_id = pd.po_release_id
671 and pr.po_header_id = pd.po_header_id
672 --and pr.approved_flag = 'Y'
673 and nvl(pr.cancel_flag, 'N') = 'N'
674 and ps.line_location_id = pd.line_location_id
675 and pl.po_line_id = ps.po_line_id
676 and pv.vendor_id = ph.vendor_id
677 and pvs.vendor_site_id = ph.vendor_site_id
678 and pvs.org_id = ph.org_id
679 and pd.wip_entity_id = wo.wip_entity_id
680 and pd.destination_organization_id = wo.organization_id
681 and pd.wip_operation_seq_num = wo.operation_seq_num
682 and (pd.wip_repetitive_schedule_id is null or
683 pd.wip_repetitive_schedule_id = wo.repetitive_schedule_id)
684 and pl.item_id = msi.inventory_item_id
685 -- Fixed bug 4411247. Join msi to pd.destination_organization_id instead
686 -- of pl.org_id because pl.org_id store operating unit organization, not
687 -- item organization.
688 and pd.destination_organization_id = msi.organization_id
689 and pd.wip_entity_id = wor.wip_entity_id
690 and pd.wip_operation_seq_num = wor.operation_seq_num
691 and pd.wip_resource_seq_num = wor.resource_seq_num
692 and pd.destination_organization_id = wor.organization_id
693 and (pd.wip_repetitive_schedule_id is null or
694 pd.wip_repetitive_schedule_id =wor.repetitive_schedule_id)
695 and wor.autocharge_type = WIP_CONSTANTS.PO_MOVE;
696
697 cursor cget_reqs (l_wip_entity_id number,
698 l_rep_sched_id number,
699 l_osp_operation_num number
700 ) is
701 select prh.segment1 req_num
702 from po_requisition_headers_all prh,
703 po_requisition_lines_all prl
704 where prl.wip_entity_id = l_wip_entity_id
705 and prl.wip_operation_seq_num = l_osp_operation_num
706 and nvl(prl.wip_repetitive_schedule_id, -1) = nvl(l_rep_sched_id, -1)
707 and prl.line_location_id is null
708 and prh.requisition_header_id = prl.requisition_header_id;
709
710 l_num_of_po NUMBER ;
711 l_wip_entity_id NUMBER;
712 l_rep_sched_id NUMBER;
713 l_osp_operation_num NUMBER;
714 l_buyer VARCHAR2(80);
715 l_po_header_id NUMBER;
716 l_po_distribution_id NUMBER;
717 l_org_id NUMBER;
718 l_po_number VARCHAR2(80);
719 l_po_line_qty NUMBER;
720 l_po_line_uom VARCHAR2(25);
721 l_subcontractor_name PO_VENDORS.VENDOR_NAME%TYPE;
722 l_subcontractor_site VARCHAR2(80);
723 l_primary_qty NUMBER;
724 msg VARCHAR2(2000);
725 l_non_approved_pos VARCHAR2(2000) := '';
726 l_open_reqs VARCHAR2(2000);
727 l_release_num NUMBER ;
728 l_required_assy_qty NUMBER;
729 begin
730 l_wip_entity_id := wf_engine.GetItemAttrNumber ( itemtype,
731 itemkey,
732 'WIP_ENTITY_ID');
733 l_rep_sched_id := wf_engine.GetItemAttrNumber ( itemtype,
734 itemkey,
735 'REP_SCHEDULE_ID');
736 l_osp_operation_num := wf_engine.GetItemAttrNumber ( itemtype,
737 itemkey,
738 'WIP_OP_SEQ');
739 l_num_of_po := 0;
740
741 FOR c_pos_rec in cget_approved_pos(l_wip_entity_id, l_rep_sched_id ,l_osp_operation_num) LOOP
742 l_po_number := c_pos_rec.po_num;
743 l_po_header_id := c_pos_rec.po_header_id;
744 l_po_line_qty := c_pos_rec.po_line_qty;
745 l_po_line_uom := c_pos_rec.po_line_uom;
746 l_subcontractor_name := c_pos_rec.subcontractor;
747 l_subcontractor_site := c_pos_rec.subcontractor_site;
748 l_po_distribution_id := c_pos_rec.po_distribution_id;
749 l_org_id := c_pos_rec.org_id;
750 l_release_num := c_pos_rec.release_num ;
751 l_required_assy_qty := c_pos_rec.required_assy_qty;
752
753 /* Fix for Bug#6058918. Comment out following statement and
754 move it to next if */
755 /* l_buyer := wip_std_wf.GetBuyerLogin (l_po_header_id, l_release_num); */
756
757 if (c_pos_rec.approved_flag = 'Y') then
758 l_num_of_po := l_num_of_po + 1;
759 end if;
760
761 exit when l_num_of_po > 3;
762
763 if ( l_num_of_po <= 3 and c_pos_rec.approved_flag = 'Y') then
764 /* Fix for Bug#6058918. */
765 l_buyer := wip_std_wf.GetBuyerLogin (l_po_header_id, l_release_num);
766 SetPOData (p_itemtype => itemtype,
767 p_itemkey => itemkey,
768 p_rec_num => l_num_of_po,
769 p_buyer => l_buyer,
770 p_po_number => l_po_number,
771 p_po_header_id => l_po_header_id,
772 p_po_distribution_id => l_po_distribution_id,
773 p_org_id => l_org_id,
774 p_po_line_qty => l_po_line_qty,
775 p_po_line_uom => l_po_line_uom,
776 p_subcontractor => l_subcontractor_name,
777 p_subcontractor_site => l_subcontractor_site,
778 p_required_assy_qty => l_required_assy_qty);
779 else
780 l_non_approved_pos := l_po_number || ', ' || l_non_approved_pos ;
781 end if;
782 END LOOP;
783
784 FOR c_req_rec in cget_reqs(l_wip_entity_id, l_rep_sched_id ,l_osp_operation_num) LOOP
785
786 l_open_reqs := c_req_rec.req_num || ', ' || l_open_reqs ;
787 END LOOP;
788
789 wf_engine.SetItemAttrNumber( itemtype => itemtype,
790 itemkey => itemkey,
791 aname => 'NUM_OF_POS',
792 avalue => l_num_of_po);
793
794 wf_engine.SetItemAttrText ( itemtype => itemtype,
795 itemkey => itemkey,
796 aname => 'NON_APPROVED_POS',
797 avalue => substr(l_non_approved_pos,1,length(l_non_approved_pos)-2));
798
799 wf_engine.SetItemAttrText ( itemtype => itemtype,
800 itemkey => itemkey,
801 aname => 'OPEN_REQS',
802 avalue => substr(l_open_reqs,1,length(l_open_reqs)-2));
803
804 if l_num_of_po = 0 then
805 resultout:='COMPLETE:NONE';
806 elsif l_num_of_po = 1 then
807 l_primary_qty := wf_engine.GetItemAttrNumber ( itemtype,
808 itemkey,
809 'PRIMARY_QTY');
810
811 wf_engine.SetItemAttrNumber( itemtype => itemtype,
812 itemkey => itemkey,
813 aname => 'SHIP_QTY',
814 avalue => l_primary_qty);
815
816 if (l_po_line_qty < l_primary_qty) then
817 fnd_message.set_name ('WIP', 'WIP_SHP_GTR_THAN_PO_QTY');
818 msg := fnd_message.get;
819 end if;
820
821 wf_engine.SetItemAttrText( itemtype => itemtype,
822 itemkey => itemkey,
823 aname => 'NOT_VALID_MESG',
824 avalue => msg);
825
826
827 resultout:='COMPLETE:ONE';
828 elsif l_num_of_po > 1 and l_num_of_po <= 3 then
829 resultout:='COMPLETE:LT_THREE';
830 else
831 resultout:='COMPLETE:GT_THREE';
832 end if;
833
834 exception
835 when others then
836 wf_core.context('WIP_OSP_SHP_I_WF', 'MultiplePO', itemtype, itemkey);
837 raise;
838
839 END MultiplePO;
840
841 PROCEDURE Validate ( itemtype in varchar2,
842 itemkey in varchar2,
843 actid in number,
844 funcmode in varchar2,
845 resultout out nocopy varchar2) is
846 l_primary_qty NUMBER;
847 l_primary_uom VARCHAR2(20);
848 l_num_of_pos NUMBER;
849 l_total_qty NUMBER := 0;
850 l_qty NUMBER;
851 l_osp_item_id NUMBER;
852 l_rec_num NUMBER;
853 l_ship_qty NUMBER;
854 msg VARCHAR2(2000);
855
856 begin
857 l_primary_qty := wf_engine.GetItemAttrNumber ( itemtype,
858 itemkey,
859 'PRIMARY_QTY');
860
861 l_primary_uom := wf_engine.GetItemAttrText ( itemtype,
862 itemkey,
863 'PRIMARY_UOM');
864
865 l_num_of_pos := wf_engine.GetItemAttrText ( itemtype,
866 itemkey,
867 'NUM_OF_POS');
868
869 l_osp_item_id := wf_engine.GetItemAttrNumber ( itemtype,
870 itemkey,
871 'OSP_ITEM_ID');
872
873 for i in 1..l_num_of_pos loop
874
875
876 l_ship_qty := wf_engine.GetItemAttrNumber ( itemtype,
877 itemkey,
878 'PO_QTY' || i);
879
880 /* if the buyer has not entered in a number for PO Quantity or
881 if the buyer entered 0, then there is no need to add qty
882 */
883 if (l_ship_qty is not null and l_ship_qty <> 0) then
884 l_qty := l_ship_qty;
885 l_total_qty := l_total_qty + l_qty;
886 end if;
887
888 end loop;
889
890 if l_total_qty = l_primary_qty then
891 resultout := 'COMPLETE:Y';
892 else
893 -- Get Error message to be displayed in notifications
894 fnd_message.set_name ('WIP', 'WIP_QTY_NOT_VALID');
895 msg := fnd_message.get;
896
897 wf_engine.SetItemAttrText( itemtype => itemtype,
898 itemkey => itemkey,
899 aname => 'NOT_VALID_MESG',
900 avalue => msg);
901
902
903 resultout := 'COMPLETE:N';
904 end if;
905 exception
906 when others then
907 wf_core.context('WIP_OSP_SHP_I_WF', 'Validate', itemtype, itemkey);
908 raise;
909
910 END Validate;
911
912
913 PROCEDURE StartDetailProcesses ( itemtype in varchar2,
914 itemkey in varchar2,
915 actid in number,
916 funcmode in varchar2,
917 resultout out nocopy varchar2) is
918 i NUMBER;
919 num_of_proc NUMBER;
920 childkey VARCHAR2(80);
921 l_item_type VARCHAR2(8);
922 l_item_key VARCHAR2(80);
923 l_assy VARCHAR2(80);
924 l_assy_desc VARCHAR2(240);
925 l_buyer VARCHAR2(80);
926 l_job_name VARCHAR2(80);
927 l_line_name VARCHAR2(10);
928 l_organization_id NUMBER;
929 l_osp_item VARCHAR2(80);
930 l_osp_item_desc VARCHAR2(240);
931 l_osp_operation NUMBER;
932 l_po_header_id NUMBER;
933 l_po_distribution_id NUMBER;
934 l_org_id NUMBER;
935 l_po_line_qty NUMBER;
936 l_po_line_uom VARCHAR2(10);
937 l_po_num VARCHAR2(80);
938 l_primary_qty NUMBER;
939 l_primary_uom VARCHAR2(10);
940 l_rep_sched_id NUMBER;
941 l_ship_qty NUMBER;
942 l_subcontractor PO_VENDORS.VENDOR_NAME%TYPE;
943 l_subcontractor_site VARCHAR2(80);
944 l_wip_entity_id NUMBER;
945 l_user_id NUMBER;
946 l_resp_id NUMBER;
947 l_resp_appl_id NUMBER;
948 l_security_group_id NUMBER;
949 begin
950
951 num_of_proc := wf_engine.GetItemAttrNumber ( itemtype,
952 itemkey,
953 'NUM_OF_POS');
954
955
956 if num_of_proc > 1 and num_of_proc <= 3 then
957
958 GetStartupWFAttributes ( p_itemtype => itemtype
959 ,p_itemkey => itemkey
960 ,p_wip_entity_id => l_wip_entity_id
961 ,p_rep_sched_id => l_rep_sched_id
962 ,p_organization_id=> l_organization_id
963 ,p_primary_qty => l_primary_qty
964 ,p_primary_uom => l_primary_uom
965 ,p_osp_operation => l_osp_operation
966 ,p_user_id => l_user_id
967 ,p_resp_id => l_resp_id
968 ,p_resp_appl_id => l_resp_appl_id
969 ,p_security_group_id => l_security_group_id) ;
970
971
972 for i in 1..num_of_proc loop
973
974 select to_char(wip_workflow_s.nextval)
975 into childkey
976 from dual;
977
978 l_ship_qty := wf_engine.GetItemAttrNumber ( itemtype,
979 itemkey,
980 'PO_QTY' || i);
981
982 /* if ship_qty is null or = 0 then there is no need
983 to start a new process
984 */
985
986 if (l_ship_qty is not null and l_ship_qty <> 0) then
987
988
989 wf_engine.CreateProcess( itemtype => itemtype,
990 itemkey => childkey,
991 process => 'SHIP_INTERMEDIATE');
992
993
994 wf_engine.SetItemAttrText( itemtype => itemtype,
995 itemkey => childkey,
996 aname => 'PARENT_ITEMKEY',
997 avalue => itemkey);
998
999 SetStartupWFAttributes ( p_itemtype => itemtype
1000 ,p_itemkey => childkey
1001 ,p_wip_entity_id => l_wip_entity_id
1002 ,p_rep_sched_id => l_rep_sched_id
1003 ,p_organization_id => l_organization_id
1004 ,p_primary_qty => l_primary_qty
1005 ,p_primary_uom => l_primary_uom
1006 ,p_op_seq_num => l_osp_operation
1007 ,p_user_id => l_user_id
1008 ,p_resp_id => l_resp_id
1009 ,p_resp_appl_id => l_resp_appl_id
1010 ,p_security_group_id => l_security_group_id) ;
1011
1012 GetPOData ( p_itemtype => itemtype
1013 , p_itemkey => itemkey
1014 , p_rec_num => i
1015 , p_buyer => l_buyer
1016 , p_po_number => l_po_num
1017 , p_po_header_id => l_po_header_id
1018 , p_po_distribution_id => l_po_distribution_id
1019 , p_org_id => l_org_id
1020 , p_po_line_qty => l_po_line_qty
1021 , p_po_line_uom => l_po_line_uom
1022 , p_subcontractor => l_subcontractor
1023 , p_subcontractor_site => l_subcontractor_site);
1024
1025 SetPOData ( p_itemtype => itemtype
1026 , p_itemkey => childkey
1027 , p_rec_num => 1
1028 , p_buyer => l_buyer
1029 , p_po_number => l_po_num
1030 , p_po_header_id => l_po_header_id
1031 , p_po_distribution_id => l_po_distribution_id
1032 , p_org_id => l_org_id
1033 , p_po_line_qty => l_po_line_qty
1034 , p_po_line_uom => l_po_line_uom
1035 , p_subcontractor => l_subcontractor
1036 , p_subcontractor_site => l_subcontractor_site);
1037
1038 wf_engine.SetItemAttrText( itemtype => itemtype,
1039 itemkey => childkey,
1040 aname => 'SHIP_QTY',
1041 avalue => l_ship_qty);
1042
1043 wf_engine.StartProcess( itemtype => itemtype,
1044 itemkey => childkey );
1045 end if; /* ship_qty is not null or ship_qty != 0 */
1046
1047 end loop;
1048 end if;
1049 exception
1050 when others then
1051 wf_core.context('WIP_OSP_SHP_I_WF', 'StartDetailProcesses', itemtype, itemkey);
1052 raise;
1053
1054 END StartDetailProcesses;
1055
1056
1057 PROCEDURE SelectShippingManager( itemtype in varchar2,
1058 itemkey in varchar2,
1059 actid in number,
1060 funcmode in varchar2,
1061 resultout out nocopy varchar2) is
1062
1063 l_organization_id number :=
1064 wf_engine.GetItemAttrNumber( itemtype => itemtype,
1065 itemkey => itemkey,
1066 aname => 'ORGANIZATION_ID');
1067
1068 l_ship_from_2nd_sub varchar2(1) :=
1069 wf_engine.GetItemAttrText (itemtype => itemtype,
1070 itemkey => itemkey,
1071 aname => 'SHIPPED_FROM_2ND_SUB');
1072
1073 l_ship_manager varchar2(80) := NULL;
1074
1075 BEGIN
1076
1077 /* This check is required because, if the intermediates are being
1078 shipped from another supplier, there is no need to notify the shipper
1079 of the intermediates being shipped
1080 */
1081 if l_ship_from_2nd_sub = 'Y' then
1082 resultout := 'COMPLETE:WIP_NOT_FOUND';
1083 return;
1084 end if;
1085
1086 l_ship_manager :=
1087 wip_std_wf.GetShipManagerLogin(l_organization_id);
1088
1089 if (l_ship_manager is not null) then
1090 wf_engine.SetItemAttrText( itemtype => itemtype,
1091 itemkey => itemkey,
1092 aname => 'SHIPPING_MANAGER',
1093 avalue => l_ship_manager);
1094 resultout := 'COMPLETE:WIP_FOUND';
1095 else
1096 resultout := 'COMPLETE:WIP_NOT_FOUND';
1097 end if;
1098
1099 exception
1100 when others then
1101 wf_core.context('WIP_OSP_SHP_I_WF', 'SelectShippingManager', itemtype, itemkey);
1102 raise;
1103
1104 END SelectShippingManager;
1105
1106
1107
1108 PROCEDURE GetShipToAddress ( itemtype in varchar2,
1109 itemkey in varchar2,
1110 actid in number,
1111 funcmode in varchar2,
1112 resultout out nocopy varchar2) is
1113
1114 l_po_header_id NUMBER;
1115 l_address VARCHAR2(1000);
1116
1117 begin
1118 l_po_header_id := wf_engine.GetItemAttrNumber ( itemtype,
1119 itemkey,
1120 'PO_HEADER_ID');
1121
1122 select ap_vendor_sites_pkg.format_address (pvs.country,
1123 pvs.address_line1, pvs.address_line2, pvs.address_line3,
1124 pvs.address_line4, pvs.city,pvs.county,pvs.state,
1125 pvs.province,pvs.zip,null)
1126 into l_address
1127 from po_headers_all ph,
1128 po_vendor_sites_all pvs
1129 where ph.po_header_id = l_po_header_id
1130 and pvs.org_id = ph.org_id
1131 and pvs.vendor_site_id = ph.vendor_site_id;
1132
1133 wf_engine.SetItemAttrText( itemtype => itemtype,
1134 itemkey => itemkey,
1135 aname => 'ADDRESS',
1136 avalue => l_address);
1137
1138 exception
1139 when others then
1140 wf_core.context('WIP_OSP_SHP_I_WF', 'GetShipToAddress', itemtype, itemkey);
1141 raise;
1142 END GetShipToAddress;
1143
1144 PROCEDURE StartWFProcToAnotherSupplier
1145 ( p_po_distribution_id in NUMBER,
1146 p_shipped_qty in NUMBER,
1147 p_shipped_uom in VARCHAR2,
1148 p_shipped_date in DATE default null,
1149 p_expected_receipt_date in DATE default null,
1150 p_packing_slip in VARCHAR2 default null,
1151 p_airbill_waybill in VARCHAR2 default null,
1152 p_bill_of_lading in VARCHAR2 default null,
1153 p_packaging_code in VARCHAR2 default null,
1154 p_num_of_container in NUMBER default null,
1155 p_gross_weight in NUMBER default null,
1156 p_gross_weight_uom in VARCHAR2 default null,
1157 p_net_weight in NUMBER default null,
1158 p_net_weight_uom in VARCHAR2 default null,
1159 p_tar_weight in NUMBER default null,
1160 p_tar_weight_uom in VARCHAR2 default null,
1161 p_hazard_class in VARCHAR2 default null,
1162 p_hazard_code in VARCHAR2 default null,
1163 p_hazard_desc in VARCHAR2 default null,
1164 p_special_handling_code in VARCHAR2 default null,
1165 p_freight_carrier in VARCHAR2 default null,
1166 p_freight_carrier_terms in VARCHAR2 default null,
1167 p_carrier_equip in VARCHAR2 default null,
1168 p_carrier_method in VARCHAR2 default null,
1169 p_freight_bill_num in VARCHAR2 default null,
1170 p_receipt_num in VARCHAR2 default null,
1171 p_ussgl_txn_code in VARCHAR2 default null
1172 ) is
1173
1174 l_itemtype varchar2(8) := 'WIPISHPW';
1175 l_itemkey varchar2(240);
1176
1177 l_wip_entity_id number;
1178 l_rep_sched_id number;
1179 l_organization_id number;
1180 l_op_seq_num number;
1181
1182 l_ship_to_loc_id number;
1183
1184 l_buyer VARCHAR2(80);
1185 l_po_header_id NUMBER;
1186 l_po_distribution_id NUMBER;
1187 l_org_id NUMBER;
1188 l_po_number VARCHAR2(80);
1189 l_po_line_qty NUMBER;
1190 l_po_line_uom VARCHAR2(25);
1191 l_subcontractor_name PO_VENDORS.VENDOR_NAME%TYPE;
1192 l_subcontractor_site VARCHAR2(80);
1193 l_release_num NUMBER ;
1194
1195 cursor GetWIPData (p_po_distribution_id NUMBER) is
1196 select pd.wip_entity_id,
1197 pd.wip_repetitive_schedule_id,
1198 ps.ship_to_organization_id,
1199 pd.wip_operation_seq_num
1200 from po_distributions_all pd,
1201 po_line_locations_all ps
1202 where pd.po_distribution_id = p_po_distribution_id
1203 and ps.line_location_id = pd.line_location_id;
1204
1205 cursor GetPOData (p_po_distribution_id NUMBER) is
1206 select ph.segment1 ||
1207 decode (pr.release_num,
1208 NULL, NULL, '-' || pr.release_num) po_num,
1209 ph.po_header_id po_header_id,
1210 pd.quantity_ordered po_line_qty,
1211 pl.unit_meas_lookup_code po_line_uom,
1212 pv.vendor_name subcontractor,
1213 pvs.vendor_site_code subcontractor_site,
1214 ps.ship_to_location_id ship_to_location_id,
1215 pr.release_num
1216 from po_releases_all pr,
1217 po_vendor_sites_all pvs,
1218 po_vendors pv,
1219 po_headers_all ph,
1220 po_lines_all pl,
1221 po_line_locations_all ps,
1222 po_distributions_all pd
1223 where pd.po_distribution_id = p_po_distribution_id
1224 and ps.line_location_id = pd.line_location_id
1225 and pl.po_line_id = pd.po_line_id
1226 and ph.po_header_id = pd.po_header_id
1227 and pr.po_release_id (+) = pd.po_release_id
1228 and pv.vendor_id = ph.vendor_id
1229 and pvs.vendor_site_id = ph.vendor_site_id
1230 and pvs.org_id = ph.org_id;
1231
1232
1233 begin
1234
1235 if NOT wip_common_wf_pkg.OSPEnabled then
1236 return;
1237 end if;
1238
1239 select to_char(wip_workflow_s.nextval)
1240 into l_itemkey
1241 from dual;
1242
1243 wf_engine.CreateProcess( itemtype => l_itemtype
1244 , itemkey => l_itemkey
1245 , process => 'NOTIFY_2ND_BUYER_SUPPLIER' );
1246
1247 open GetWIPData (p_po_distribution_id);
1248 fetch GetWIPData into l_wip_entity_id, l_rep_sched_id, l_organization_id,
1249 l_op_seq_num;
1250 close GetWIPData;
1251
1252 SetStartupWFAttributes ( p_itemtype => l_itemtype
1253 , p_itemkey => l_itemkey
1254 , p_wip_entity_id => l_wip_entity_id
1255 , p_rep_sched_id => l_rep_sched_id
1256 , p_organization_id => l_organization_id
1257 , p_primary_qty => p_shipped_qty
1258 , p_primary_uom => p_shipped_uom
1259 , p_op_seq_num => l_op_seq_num
1260 , p_user_id => NULL
1261 , p_resp_id => NULL
1262 , p_resp_appl_id => NULL
1263 , p_security_group_id => NULL);
1264
1265 -- Gets the PO Data for the 1st PO (PO that is states the OSP item should
1266 -- be shipped to another vendor)
1267 open GetPOData (p_po_distribution_id);
1268 fetch GetPOData into l_po_number, l_po_header_id, l_po_line_qty,
1269 l_po_line_uom, l_subcontractor_name, l_subcontractor_site,
1270 l_ship_to_loc_id, l_release_num ;
1271 close GetPOData;
1272
1273 l_buyer := wip_std_wf.GetBuyerLogin (l_po_header_id, l_release_num);
1274
1275 SetPOData (p_itemtype => l_itemtype,
1276 p_itemkey => l_itemkey,
1277 p_rec_num => 1,
1278 p_buyer => l_buyer,
1279 p_po_number => l_po_number,
1280 p_po_header_id => l_po_header_id,
1281 p_po_distribution_id => l_po_distribution_id,
1282 p_org_id => l_org_id,
1283 p_po_line_qty => l_po_line_qty,
1284 p_po_line_uom => l_po_line_uom,
1285 p_subcontractor => l_subcontractor_name,
1286 p_subcontractor_site => l_subcontractor_site);
1287
1288 wf_engine.SetItemAttrNumber( itemtype => l_itemtype
1289 , itemkey => l_itemkey
1290 , aname => 'PO_DISTRIBUTION_ID'
1291 , avalue => p_po_distribution_id);
1292
1293 wf_engine.SetItemAttrText( itemtype => l_itemtype
1294 , itemkey => l_itemkey
1295 , aname => 'SHIPPED_FROM_2ND_SUB'
1296 , avalue => 'Y');
1297
1298 wf_engine.SetItemAttrNumber( itemtype => l_itemtype
1299 , itemkey => l_itemkey
1300 , aname => 'SHIP_QTY'
1301 , avalue => p_shipped_qty);
1302 wf_engine.SetItemAttrText( itemtype => l_itemtype
1303 , itemkey => l_itemkey
1304 , aname => 'PRIMARY_UOM'
1305 , avalue => p_shipped_uom);
1306
1307 /* Set up data for ASN */
1308
1309 wf_engine.SetItemAttrDate( itemtype => l_itemtype
1310 , itemkey => l_itemkey
1311 , aname => 'SHIPPED_DATE'
1312 , avalue => p_shipped_date);
1313 wf_engine.SetItemAttrDate( itemtype => l_itemtype
1314 , itemkey => l_itemkey
1315 , aname => 'EXPECTED_RECEIPT_DATE'
1316 , avalue => p_expected_receipt_date);
1317 wf_engine.SetItemAttrText( itemtype => l_itemtype
1318 , itemkey => l_itemkey
1319 , aname => 'PACKING_SLIP'
1320 , avalue => p_packing_slip);
1321 wf_engine.SetItemAttrText( itemtype => l_itemtype
1322 , itemkey => l_itemkey
1323 , aname => 'AIRBILL_WAYBILL_NUM'
1324 , avalue => p_airbill_waybill);
1325 wf_engine.SetItemAttrText( itemtype => l_itemtype
1326 , itemkey => l_itemkey
1327 , aname => 'BILL_OF_LADING'
1328 , avalue => p_bill_of_lading);
1329 wf_engine.SetItemAttrText( itemtype => l_itemtype
1330 , itemkey => l_itemkey
1331 , aname => 'PACKAGING_CODE'
1332 , avalue => p_packaging_code);
1333 wf_engine.SetItemAttrNumber( itemtype => l_itemtype
1334 , itemkey => l_itemkey
1335 , aname => 'NUM_OF_CONTAINER'
1336 , avalue => p_num_of_container);
1337 wf_engine.SetItemAttrNumber( itemtype => l_itemtype
1338 , itemkey => l_itemkey
1339 , aname => 'GROSS_WEIGHT'
1340 , avalue => p_gross_weight);
1341 wf_engine.SetItemAttrText( itemtype => l_itemtype
1342 , itemkey => l_itemkey
1343 , aname => 'GROSS_WEIGHT_UOM'
1344 , avalue => p_gross_weight_uom);
1345 wf_engine.SetItemAttrNumber( itemtype => l_itemtype
1346 , itemkey => l_itemkey
1347 , aname => 'NET_WEIGHT'
1348 , avalue => p_net_weight);
1349 wf_engine.SetItemAttrText( itemtype => l_itemtype
1350 , itemkey => l_itemkey
1351 , aname => 'NET_WEIGHT_UOM'
1352 , avalue => p_net_weight_uom);
1353 wf_engine.SetItemAttrNumber( itemtype => l_itemtype
1354 , itemkey => l_itemkey
1355 , aname => 'TAR_WEIGHT'
1356 , avalue => p_tar_weight);
1357 wf_engine.SetItemAttrText( itemtype => l_itemtype
1358 , itemkey => l_itemkey
1359 , aname => 'TAR_WEIGHT_UOM'
1360 , avalue => p_tar_weight_uom);
1361 wf_engine.SetItemAttrText( itemtype => l_itemtype
1362 , itemkey => l_itemkey
1363 , aname => 'HAZARD_CLASS'
1364 , avalue => p_hazard_class);
1365 wf_engine.SetItemAttrText( itemtype => l_itemtype
1366 , itemkey => l_itemkey
1367 , aname => 'HAZARD_CODE'
1368 , avalue => p_hazard_code);
1369 wf_engine.SetItemAttrText( itemtype => l_itemtype
1370 , itemkey => l_itemkey
1371 , aname => 'HAZARD_DESCRIPTION'
1372 , avalue => p_hazard_desc);
1373 wf_engine.SetItemAttrText( itemtype => l_itemtype
1374 , itemkey => l_itemkey
1375 , aname => 'SPECIAL_HANDLING_CODE'
1376 , avalue => p_special_handling_code);
1377 wf_engine.SetItemAttrText( itemtype => l_itemtype
1378 , itemkey => l_itemkey
1379 , aname => 'FREIGHT_CARRIER'
1380 , avalue => p_freight_carrier);
1381 wf_engine.SetItemAttrText( itemtype => l_itemtype
1382 , itemkey => l_itemkey
1383 , aname => 'FREIGHT_CARRIER_TERMS'
1384 , avalue => p_freight_carrier_terms);
1385 wf_engine.SetItemAttrText( itemtype => l_itemtype
1386 , itemkey => l_itemkey
1387 , aname => 'CARRIER_EQUIPMENT'
1388 , avalue => p_carrier_equip);
1389 wf_engine.SetItemAttrText( itemtype => l_itemtype
1390 , itemkey => l_itemkey
1391 , aname => 'CARRIER_METHOD'
1392 , avalue => p_carrier_method);
1393 wf_engine.SetItemAttrText( itemtype => l_itemtype
1394 , itemkey => l_itemkey
1395 , aname => 'FREIGHT_BILL_NUMBER'
1396 , avalue => p_freight_bill_num);
1397 wf_engine.SetItemAttrText( itemtype => l_itemtype
1398 , itemkey => l_itemkey
1399 , aname => 'RECEIPT_NUMBER'
1400 , avalue => p_receipt_num);
1401 wf_engine.SetItemAttrText( itemtype => l_itemtype
1402 , itemkey => l_itemkey
1403 , aname => 'USSGL_TRANSACTION_CODE'
1404 , avalue => p_ussgl_txn_code);
1405
1406 wf_engine.StartProcess( itemtype => l_itemtype,
1407 itemkey => l_itemkey );
1408
1409
1410 exception
1411 when others then
1412 wf_core.context('WIP_OSP_SHP_I_WF', 'StartWFProcToAnotherSupplier', l_itemtype, l_itemkey);
1413 raise;
1414
1415 end StartWFProcToAnotherSupplier;
1416
1417
1418 PROCEDURE GetApprovedPO ( itemtype in varchar2,
1419 itemkey in varchar2,
1420 actid in number,
1421 funcmode in varchar2,
1422 resultout out nocopy varchar2) is
1423
1424 cursor Get2ndPO (l_po_distribution_id number) is
1425 select ph2.segment1 || decode (pr.release_num,
1426 NULL, NULL, '-' || pr.release_num) po_num,
1427 ph2.po_header_id,
1428 pd2.po_distribution_id,
1429 pd2.org_id,
1430 pd2.quantity_ordered,
1431 pl2.unit_meas_lookup_code,
1432 pd2.wip_operation_seq_num,
1433 pl2.item_id,
1434 msik2.concatenated_segments,
1435 msik2.description,
1436 pr.release_num
1437 from po_releases_all pr,
1438 po_location_associations_all pla,
1439 mtl_system_items_kfv msik2,
1440 po_lines_all pl2,
1441 po_line_locations_all ps1,
1442 po_headers_all ph2,
1443 wip_operations wo,
1444 po_distributions_all pd2,
1445 po_distributions_all pd1
1446 where pd1.po_distribution_id = l_po_distribution_id
1447 and wo.wip_entity_id = pd1.wip_entity_id
1448 and wo.organization_id = pd1.destination_organization_id
1449 and wo.operation_seq_num = pd1.wip_operation_seq_num
1450 and nvl(wo.repetitive_schedule_id, -1)
1451 = nvl(pd1.wip_repetitive_schedule_id, - 1)
1452 and pd2.po_distribution_id <> l_po_distribution_id
1453 and pd2.wip_entity_id = pd1.wip_entity_id
1454 and nvl(pd2.wip_repetitive_schedule_id, -1)
1455 = nvl(pd1.wip_repetitive_schedule_id, -1)
1456 and pd2.wip_operation_seq_num in
1457 (pd1.wip_operation_seq_num, wo.next_operation_seq_num)
1458 and ph2.po_header_id = pd2.po_header_id
1459 and ph2.approved_flag = 'Y'
1460 and pl2.po_line_id = pd2.po_line_id
1461 and pl2.item_id = msik2.inventory_item_id
1462 and pl2.org_id = msik2.organization_id
1463 and ps1.line_location_id = pd1.line_location_id
1464 and pla.location_id = ps1.ship_to_location_id
1465 and pla.vendor_id = ph2.vendor_id
1466 and pla.vendor_site_id = ph2.vendor_site_id
1467 and pr.po_release_id (+) = pd2.po_release_id
1468 and ( (ph2.type_lookup_code = 'STANDARD'
1469 and nvl(ph2.cancel_flag, 'N') = 'N')
1470 OR (ph2.type_lookup_code = 'BLANKET'
1471 and pr.po_release_id = pd2.po_release_id
1472 and nvl(pr.cancel_flag, 'N') = 'N'))
1473 order by pd2.wip_operation_seq_num, pd2.wip_resource_seq_num ;
1474
1475 cursor GetShipToData (l_po_distribution_id NUMBER) is
1476 select pv.vendor_name,
1477 pvs.vendor_site_code
1478 from po_vendors pv,
1479 po_vendor_sites_all pvs,
1480 po_distributions_all pd,
1481 po_location_associations pla,
1482 po_line_locations_all ps
1483 where pd.po_distribution_id = l_po_distribution_id
1484 and ps.line_location_id = pd.line_location_id
1485 and pla.location_id = ps.ship_to_location_id
1486 and pv.vendor_id = pla.vendor_id
1487 and pvs.vendor_site_id = pla.vendor_site_id
1488 and pvs.org_id = ps.org_id;
1489
1490
1491 l_buyer VARCHAR2(80);
1492 l_po_header_id NUMBER;
1493 l_po_distribution_id NUMBER;
1494 l_org_id NUMBER;
1495 l_po_number VARCHAR2(80);
1496 l_po_line_qty NUMBER;
1497 l_po_line_uom VARCHAR2(25);
1498 l_subcontractor_name PO_VENDORS.VENDOR_NAME%TYPE;
1499 l_subcontractor_site VARCHAR2(80);
1500
1501 l_next_op_seq NUMBER;
1502 l_osp_item_id NUMBER;
1503 l_osp_item VARCHAR2(80);
1504 l_osp_item_desc VARCHAR2(240);
1505
1506 l_ship_to_loc_id NUMBER;
1507 l_vendor_id NUMBER;
1508 l_vendor_site_id NUMBER;
1509 l_vendor_name VARCHAR2(80);
1510 l_release_num NUMBER ;
1511
1512 begin
1513 l_po_distribution_id := wf_engine.GetItemAttrNumber ( itemtype,
1514 itemkey,
1515 'PO_DISTRIBUTION_ID');
1516
1517 open GetShipToData(l_po_distribution_id);
1518 fetch GetShipToData into l_subcontractor_name, l_subcontractor_site;
1519 close GetShipToData;
1520
1521 open Get2ndPO (l_po_distribution_id);
1522 fetch Get2ndPO into l_po_number, l_po_header_id, l_po_distribution_id,
1523 l_org_id, l_po_line_qty, l_po_line_uom, l_next_op_seq,
1524 l_osp_item_id,l_osp_item,l_osp_item_desc , l_release_num ;
1525 if (Get2ndPO%NOTFOUND) then
1526
1527 /**********************************************************************
1528 * We should not call SetPOData if there is no 2nd PO found, calling
1529 * this API causes duplicate attibute error because when we found 2nd PO
1530 * we also call SetPOData
1531 *********************************************************************/
1532 resultout := 'COMPLETE:N';
1533 else
1534 l_buyer := wip_std_wf.GetBuyerLogin (l_po_header_id, l_release_num);
1535
1536 SetPOData (p_itemtype => itemtype,
1537 p_itemkey => itemkey,
1538 p_rec_num => 2,
1539 p_buyer => l_buyer,
1540 p_po_number => l_po_number,
1541 p_po_header_id => l_po_header_id,
1542 p_po_distribution_id => l_po_distribution_id,
1543 p_org_id => l_org_id,
1544 p_po_line_qty => l_po_line_qty,
1545 p_po_line_uom => l_po_line_uom,
1546 p_subcontractor => l_subcontractor_name,
1547 p_subcontractor_site => l_subcontractor_site);
1548
1549 wf_engine.SetItemAttrNumber( itemtype => itemtype
1550 , itemkey => itemkey
1551 , aname => 'WIP_OP_SEQ'
1552 , avalue => l_next_op_seq);
1553
1554 wf_engine.SetItemAttrNumber( itemtype => itemtype
1555 , itemkey => itemkey
1556 , aname => 'OSP_ITEM_ID'
1557 , avalue => l_osp_item_id);
1558
1559 wf_engine.SetItemAttrText( itemtype => itemtype
1560 , itemkey => itemkey
1561 , aname => 'OSP_ITEM'
1562 , avalue => l_osp_item);
1563
1564 wf_engine.SetItemAttrText( itemtype => itemtype
1565 , itemkey => itemkey
1566 , aname => 'OSP_ITEM_DESC'
1567 , avalue => l_osp_item_desc);
1568
1569 resultout := 'COMPLETE:Y';
1570 end if;
1571 close Get2ndPO;
1572
1573 exception
1574 when others then
1575 wf_core.context('WIP_OSP_SHP_I_WF', 'GetApprovedPO', itemtype, itemkey);
1576 raise;
1577 end GetApprovedPO;
1578
1579 PROCEDURE CopyPOAttr ( itemtype in varchar2,
1580 itemkey in varchar2,
1581 actid in number,
1582 funcmode in varchar2,
1583 resultout out nocopy varchar2) is
1584
1585 l_buyer VARCHAR2(80);
1586 l_po_header_id NUMBER;
1587 l_po_distribution_id NUMBER;
1588 l_org_id NUMBER;
1589 l_po_line_qty NUMBER;
1590 l_po_line_uom VARCHAR2(10);
1591 l_po_num VARCHAR2(80);
1592 l_subcontractor VARCHAR2(80);
1593 l_subcontractor_site VARCHAR2(80);
1594 begin
1595
1596 GetPOData ( p_itemtype => itemtype
1597 , p_itemkey => itemkey
1598 , p_rec_num => 2
1599 , p_buyer => l_buyer
1600 , p_po_number => l_po_num
1601 , p_po_header_id => l_po_header_id
1602 , p_po_distribution_id => l_po_distribution_id
1603 , p_org_id => l_org_id
1604 , p_po_line_qty => l_po_line_qty
1605 , p_po_line_uom => l_po_line_uom
1606 , p_subcontractor => l_subcontractor
1607 , p_subcontractor_site => l_subcontractor_site);
1608
1609 SetPOData ( p_itemtype => itemtype
1610 , p_itemkey => itemkey
1611 , p_rec_num => 1
1612 , p_buyer => l_buyer
1613 , p_po_number => l_po_num
1614 , p_po_header_id => l_po_header_id
1615 , p_po_distribution_id => l_po_distribution_id
1616 , p_org_id => l_org_id
1617 , p_po_line_qty => l_po_line_qty
1618 , p_po_line_uom => l_po_line_uom
1619 , p_subcontractor => l_subcontractor
1620 , p_subcontractor_site => l_subcontractor_site);
1621
1622 SetPOData ( p_itemtype => itemtype
1623 , p_itemkey => itemkey
1624 , p_rec_num => 2
1625 , p_buyer => NULL
1626 , p_po_number => NULL
1627 , p_po_header_id => NULL
1628 , p_po_distribution_id => NULL
1629 , p_org_id => NULL
1630 , p_po_line_qty => NULL
1631 , p_po_line_uom => NULL
1632 , p_subcontractor => NULL
1633 , p_subcontractor_site => NULL);
1634
1635 exception
1636 when others then
1637 wf_core.context('WIP_OSP_SHP_I_WF', 'CopyPOAttr', itemtype, itemkey);
1638 raise;
1639
1640 END CopyPOAttr;
1641
1642 /*Add for Bug 13843792*/
1643 PROCEDURE StartReqImport ( itemtype in varchar2,
1644 itemkey in varchar2,
1645 actid in number,
1646 funcmode in varchar2,
1647 resultout out nocopy varchar2) is
1648 l_user_id NUMBER;
1649 l_resp_id NUMBER;
1650 l_resp_appl_id NUMBER;
1651 l_security_group_id NUMBER;
1652 l_req_group_by VARCHAR2(20);
1653 l_request_id Number;
1654 l_init_reqappr VARCHAR2(1);/*ER 4276433*/
1655
1656 begin
1657 /*Need to set user context so that we can get the correct profile value
1658 and submit concurrent program in the correct user context*/
1659
1660 l_user_id :=
1661 wf_engine.GetItemAttrNumber( itemtype
1662 , itemkey
1663 , 'USER_ID');
1664 l_resp_id :=
1665 wf_engine.GetItemAttrNumber( itemtype
1666 , itemkey
1667 , 'RESP_ID');
1668 l_resp_appl_id :=
1669 wf_engine.GetItemAttrNumber( itemtype
1670 , itemkey
1671 , 'RESP_APPL_ID');
1672 l_security_group_id :=
1673 wf_engine.GetItemAttrNumber( itemtype
1674 , itemkey
1675 , 'SECURITY_GROUP_ID');
1676
1677 fnd_global.apps_initialize (
1678 user_id => l_user_id,
1679 resp_id => l_resp_id,
1680 resp_appl_id => l_resp_appl_id ,
1681 security_group_id => l_security_group_id);
1682
1683 l_req_group_by := wf_engine.GetItemAttrNumber ( itemtype,
1684 itemkey,
1685 'REQ_GROUP_BY');
1686
1687 /*ER 4276433*/
1688 IF(fnd_profile.value('WIP_OSP_INITIATE_REQAPPR') = WIP_CONSTANTS.NO) then
1689 l_init_reqappr := 'N';
1690 ELSE
1691 l_init_reqappr := 'Y';
1692 END IF;
1693
1694 l_request_id := fnd_request.submit_request(
1695 'PO', 'REQIMPORT', NULL, NULL, FALSE,'WIP', NULL, l_req_group_by,
1696 NULL ,'N', l_init_reqappr , chr(0), NULL, NULL, NULL,
1697 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1698 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1699 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1700 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1701 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1702 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1703 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1704 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1705 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
1706 ) ;
1707
1708 --After launch the requistion import, add the request id as item attribute for WaitforReqImport to use
1709 wf_engine.AddItemAttr(itemtype => itemtype,
1710 itemkey => itemkey,
1711 aname => 'REQIMPORT_REQUEST_ID');
1712 wf_engine.SetItemAttrNumber(itemtype => itemtype
1713 , itemkey => itemkey
1714 , aname => 'REQIMPORT_REQUEST_ID'
1715 , avalue => l_request_id);
1716
1717 exception
1718 when others then
1719 wf_core.context('WIP_OSP_SHP_I_WF', 'StartDetailProcesses', itemtype, itemkey);
1720 raise;
1721
1722 END StartReqImport;
1723
1724 /*Add for Bug 13843792*/
1725 PROCEDURE WaitforReqImport ( itemtype in varchar2,
1726 itemkey in varchar2,
1727 actid in number,
1728 funcmode in varchar2,
1729 resultout out nocopy varchar2) is
1730 l_user_id NUMBER;
1731 l_resp_id NUMBER;
1732 l_resp_appl_id NUMBER;
1733 l_security_group_id NUMBER;
1734 l_parent_id Number;
1735 l_child_id Number;
1736 wait BOOLEAN;
1737 phase VARCHAR2(2000);
1738 status VARCHAR2(2000);
1739 devphase VARCHAR2(2000);
1740 devstatus VARCHAR2(2000);
1741 message VARCHAR2(2000);
1742 begin
1743 --Need to set user context so that we can get the correct profile value
1744 l_user_id :=
1745 wf_engine.GetItemAttrNumber( itemtype
1746 , itemkey
1747 , 'USER_ID');
1748 l_resp_id :=
1749 wf_engine.GetItemAttrNumber( itemtype
1750 , itemkey
1751 , 'RESP_ID');
1752 l_resp_appl_id :=
1753 wf_engine.GetItemAttrNumber( itemtype
1754 , itemkey
1755 , 'RESP_APPL_ID');
1756 l_security_group_id :=
1757 wf_engine.GetItemAttrNumber( itemtype
1758 , itemkey
1759 , 'SECURITY_GROUP_ID');
1760
1761 fnd_global.apps_initialize (
1762 user_id => l_user_id,
1763 resp_id => l_resp_id,
1764 resp_appl_id => l_resp_appl_id ,
1765 security_group_id => l_security_group_id);
1766
1767 --Retrieve the request id of requisition import and wait for it to finish
1768 l_parent_id := wf_engine.GetItemAttrNumber( itemtype
1769 , itemkey
1770 , 'REQIMPORT_REQUEST_ID');
1771
1772 IF l_parent_id <> 0 THEN
1773 wait := FND_CONCURRENT.WAIT_FOR_REQUEST
1774 (l_parent_id, 10, 3600, phase, status, devphase,
1775 devstatus, message);
1776
1777 --PO: Release During ReqImport is launched inside REQIMPORT, we need to wait for it to finish before we continue in the workflow
1778 IF(fnd_profile.value('REQIMP_AUTORELEASE') = 'Y') THEN
1779 BEGIN
1780 select request_id
1781 into l_child_id
1782 from fnd_concurrent_requests
1783 where parent_request_id = l_parent_id;
1784
1785 IF l_child_id <> 0 THEN
1786 wait := FND_CONCURRENT.WAIT_FOR_REQUEST
1787 (l_child_id, 10, 3600, phase, status, devphase,
1788 devstatus, message);
1789 END IF;
1790 EXCEPTION
1791 --cannot find Create Release concurrent program, skip wait and continue for the workflow
1792 WHEN no_data_found THEN NULL;
1793 END;
1794 END IF;
1795
1796 END IF;--l_parent_id <> 0
1797
1798 exception
1799 when others then
1800 wf_core.context('WIP_OSP_SHP_I_WF', 'StartDetailProcesses', itemtype, itemkey);
1801 raise;
1802 END WaitforReqImport;
1803
1804 END wip_osp_shp_i_wf;