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