[Home] [Help]
PACKAGE BODY: APPS.PO_AUTOCREATE_DOC
Source
1 PACKAGE BODY PO_AUTOCREATE_DOC AS
2 /* $Header: POXWATCB.pls 120.26 2008/02/19 03:49:27 adevadul ship $ */
3
4 -- Read the profile option that enables/disables the debug log
5 g_po_wf_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('PO_SET_DEBUG_WORKFLOW_ON'),'N');
6
7 /* Private Procedure/Function prototypes */
8
9 FUNCTION valid_contact(p_vendor_site_id number, p_vendor_contact_id number) RETURN BOOLEAN;
10 FUNCTION get_contact_id(p_contact_name varchar2, p_vendor_site_id number) RETURN NUMBER;
11
12 -- bug2821542
13 PROCEDURE validate_buyer (p_agent_id IN NUMBER,
14 x_result OUT NOCOPY VARCHAR2);
15
16 --<Shared Proc FPJ START>
17 PROCEDURE set_purchasing_org_id(
18 itemtype IN VARCHAR2,
19 itemkey IN VARCHAR2,
20 p_org_id IN NUMBER,
21 p_suggested_vendor_site_id IN NUMBER
22 );
23 --<Shared Proc FPJ END>
24
25 -- <SERVICES FPJ START>
26 PROCEDURE purge_expense_lines(itemtype IN VARCHAR2,
27 itemkey IN VARCHAR2);
28 -- <SERVICES FPJ END>
29
30 /* Start of procedure/function bodies */
31
32 /***************************************************************************
33 *
34 * Procedure: start_wf_process
35 *
36 * Description: Generates the itemkey, sets up the Item Attributes,
37 * then starts the Main workflow process.
38 *
39 **************************************************************************/
40 procedure start_wf_process ( ItemType VARCHAR2,
41 ItemKey VARCHAR2,
42 workflow_process VARCHAR2,
43 req_header_id NUMBER,
44 po_number VARCHAR2,
45 interface_source_code VARCHAR2,
46 org_id NUMBER) is
47
48 x_org_id number;
49 x_progress varchar2(300);
50
51 --< Bug 3636669 Start >
52 l_user_id NUMBER;
53 l_application_id NUMBER;
54 l_responsibility_id NUMBER;
55 --< Bug 3636669 End >
56
57 BEGIN
58
59 x_progress := '10: start_wf_process: Called with following parameters:' ||
60 'ItemType = ' || ItemType || '/ ' ||
61 'ItemKey = ' || ItemKey || '/ ' ||
62 'workflow_process = ' || workflow_process || '/ ' ||
63 'req_header_id = ' || to_char(req_header_id) || '/ ' ||
64 'po_number = ' || po_number || '/ ' ||
65 'interface_source_code = ' || interface_source_code;
66
67 IF (g_po_wf_debug = 'Y') THEN
68 po_wf_debug_pkg.insert_debug(Itemtype,Itemkey,x_progress);
69 END IF;
70
71
72 /* If a process is passed then it will be run
73 * If a process is not passed then the selector function defined in
74 * item type will be determine which process to run
75 */
76
77 IF ( ItemType is NOT NULL ) AND
78 ( ItemKey is NOT NULL) AND
79 ( req_header_id is NOT NULL ) THEN
80
81 --Bug 5490243. Removed the commit introduced in Bug 3293852
82
83 wf_engine.CreateProcess(itemtype => itemtype,
84 itemkey => itemkey,
85 process => workflow_process );
86
87 x_progress:= '20: start_wf_process: Just after CreateProcess';
88 IF (g_po_wf_debug = 'Y') THEN
89 po_wf_debug_pkg.insert_debug(Itemtype,Itemkey,x_progress);
90 END IF;
91
92
93 /* Initialize workflow item attributes */
94
95 po_wf_util_pkg.SetItemAttrNumber (itemtype => itemtype,
96 itemkey => itemkey,
97 aname => 'REQ_HEADER_ID',
98 avalue => req_header_id);
99
100 po_wf_util_pkg.SetItemAttrText (itemtype => itemtype,
101 itemkey => itemkey,
102 aname => 'PO_NUM_TO_CREATE',
103 avalue => po_number);
104
105 /* Interface source code can be:
106 * - FORM = 10sc Enter Req form
107 * - ICX = Web Reqs
108 * - SRS = Conc. program.
109 */
110
111 po_wf_util_pkg.SetItemAttrText (itemtype => itemtype,
112 itemkey => itemkey,
113 aname => 'INTERFACE_SOURCE_CODE',
114 avalue => interface_source_code);
115
116
117 /* Both web reqs and sc enter reqs should pass in both a req_header_id
118 * and an org_id tied to that header. Even so, I'm going to get the
119 * org_id again from the req_header_id just to be sure.
120 *
121 * Eventually if this workflow gets called for >1 req eg. thru srs then
122 * there the req_header_id maybe null so then we'll just take the
123 * org_id as passed in.
124 */
125
126 x_org_id := org_id;
127
128 /* The calling proc should pass in the right org_id associated with the
129 * the req, but get it again just in case.
130 */
131
132 if (req_header_id is NOT NULL) then
133
134 select org_id
135 into x_org_id
136 from po_requisition_headers
137 where requisition_header_id = req_header_id;
138
139 end if;
140
141 po_wf_util_pkg.SetItemAttrNumber (itemtype => itemtype,
142 itemkey => itemkey,
143 aname => 'ORG_ID',
144 avalue => x_org_id);
145
146 --< Bug 3636669 Start >
147 -- Retrieve the current application context values. This assumes that
148 -- the application context has been set prior to calling this procedure.
149 FND_PROFILE.get('USER_ID', l_user_id);
150 FND_PROFILE.get('RESP_ID', l_responsibility_id);
151 FND_PROFILE.get('RESP_APPL_ID', l_application_id);
152
153 -- Populate the application context workflow attributes
154 PO_WF_UTIL_PKG.SetItemAttrNumber( itemtype => itemtype
155 , itemkey => itemkey
156 , aname => 'USER_ID'
157 , avalue => l_user_id
158 );
159 PO_WF_UTIL_PKG.SetItemAttrNumber( itemtype => itemtype
160 , itemkey => itemkey
161 , aname => 'APPLICATION_ID'
162 , avalue => l_application_id
163 );
164 PO_WF_UTIL_PKG.SetItemAttrNumber( itemtype => itemtype
165 , itemkey => itemkey
166 , aname => 'RESPONSIBILITY_ID'
167 , avalue => l_responsibility_id
168 );
169 --< Bug 3636669 End >
170
171 /* Kick off the process */
172
173 x_progress := '30: start_wf_process: Kicking off StartProcess ';
174 IF (g_po_wf_debug = 'Y') THEN
175 po_wf_debug_pkg.insert_debug(Itemtype,Itemkey,x_progress);
176 END IF;
177
178 wf_engine.StartProcess(itemtype => itemtype,
179 itemkey => itemkey );
180
181 END IF;
182
183 exception
184 when others then
185 wf_core.context('po_autocreate_doc','start_wf_process',x_progress);
186 raise;
187 end start_wf_process;
188
189
190 /***************************************************************************
191 *
192 * Procedure: should_req_be_autocreated
193 *
194 * Description: Decides whether automatic autocreation should
195 * take place or not.
196 *
197 **************************************************************************/
198 procedure should_req_be_autocreated(itemtype IN VARCHAR2,
199 itemkey IN VARCHAR2,
200 actid IN NUMBER,
201 funcmode IN VARCHAR2,
202 resultout OUT NOCOPY VARCHAR2 ) is
203
204 x_autocreate_doc varchar2(1);
205 x_progress varchar2(300);
206
207 begin
208
209 /* This decision is made by simply looking at an item atrribute,
210 * which has a default value. All the user needs to do is change
211 * that attribute according to their needs.
212 */
213
214 x_autocreate_doc := po_wf_util_pkg.GetItemAttrText (itemtype => itemtype,
215 itemkey => itemkey,
216 aname => 'AUTOCREATE_DOC');
217
218 if (x_autocreate_doc = 'Y') then
219 resultout := wf_engine.eng_completed || ':' || 'Y';
220
221 x_progress:= '10: should_req_be_autocreated: result = Y';
222 IF (g_po_wf_debug = 'Y') THEN
223 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
224 END IF;
225
226 else
227 resultout := wf_engine.eng_completed || ':' || 'N';
228
229 x_progress:= '20: should_req_be_autocreated: result = N';
230 IF (g_po_wf_debug = 'Y') THEN
231 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
232 END IF;
233 end if;
234
235 exception
236 when others then
237 wf_core.context('po_autocreate_doc','should_req_be_autocreated',x_progress);
238 raise;
239 end should_req_be_autocreated;
240
241
242 /***************************************************************************
243 *
244 * Procedure: launch_req_line_processing
245 *
246 * Description: This process gets the req lines available for
247 * autocreate either belonging to the req or all
248 * the ones in the req pool. This depends on whether
249 * whether a req_header_id was passed into the
250 * workflow.
251 *
252 **************************************************************************/
253 procedure launch_req_line_processing(itemtype IN VARCHAR2,
254 itemkey IN VARCHAR2,
255 actid IN NUMBER,
256 funcmode IN VARCHAR2,
257 resultout OUT NOCOPY VARCHAR2 ) is
258
259
260
261 x_ItemType varchar2(20) := itemtype; /* Calling proc has same
262 * item type as called proc
263 */
264 x_ItemKey varchar2(60) := null;
265 x_workflow_process varchar2(40) := 'REQ_LINE_PROCESSING';
266 x_seq_for_item_key varchar2(6) := null;
267 x_req_header_id number;
268 x_req_line_id number;
269 x_group_id number;
270 x_org_id number;
271 x_progress varchar2(300);
272
273
274 /* Declare cursor to get all the req lines for the req
275 * passed into the workflow.
276 */
277
278 /* Bug# 1121317: kagarwal
279 ** Desc: The cursor c1 in launch_req_line_processing() of POXWATCB.pls
280 ** is fetching the requisition_line_id using the po_requisition_lines_inq_v
281 ** view which is a join of more than 20 tables. The cursor has been modified
282 ** to improve performance.
283 */
284
285 /* Bug1366981
286 The following query is still performance intensive and so commenting
287 the same and change the cursor c1 to increase the performance
288
289 cursor c1 is x_req_header_id is a parameter
290 select pol.requisition_line_id
291 from po_requisition_headers poh, po_requisition_lines pol
292 where line_location_id is null AND
293 nvl(pol.cancel_flag,'N') ='N' AND
294 nvl(pol.closed_code,'OPEN') <> 'FINALLY CLOSED' AND
295 nvl(modified_by_agent_flag,'N') ='N' AND
296 source_type_code = 'VENDOR' AND
297 authorization_status = 'APPROVED' AND
298 (poh.requisition_header_id = x_req_header_id
299 OR
300 x_req_header_id is null) AND
301 poh.requisition_header_id = pol.requisition_header_id
302 order by poh.requisition_header_id, line_num;
303 */
304 /*Bug 1366981
305 To handle the x_req_header_id null case and to ensure the
306 index on requisition_header_id is used,modified the cursor c1
307 to use a union all and thereby increase performance.
308 */
309
310 cursor c1 is /* x_req_header_id is a parameter*/
311 select pol.requisition_line_id
312 from po_requisition_headers_all poh, -- <R12 MOAC>
313 po_requisition_lines pol
314 where x_req_header_id is null AND
315 line_location_id is null AND
316 nvl(pol.cancel_flag,'N') ='N' AND
317 nvl(pol.closed_code,'OPEN') <> 'FINALLY CLOSED' AND
318 nvl(modified_by_agent_flag,'N') ='N' AND
319 source_type_code = 'VENDOR' AND
320 authorization_status = 'APPROVED' AND
321 poh.requisition_header_id = pol.requisition_header_id
322 union all
323 select pol.requisition_line_id
324 from po_requisition_headers_all poh, -- <R12 MOAC>
325 po_requisition_lines pol
326 where x_req_header_id is not null AND
327 poh.requisition_header_id = x_req_header_id AND
328 line_location_id is null AND
329 nvl(pol.cancel_flag,'N') ='N' AND
330 nvl(pol.closed_code,'OPEN') <> 'FINALLY CLOSED' AND
331 nvl(modified_by_agent_flag,'N') ='N' AND
332 source_type_code = 'VENDOR' AND
333 authorization_status = 'APPROVED' AND
334 poh.requisition_header_id = pol.requisition_header_id;
335
336 --<CONSUME REQ DEMAND FPI START>
337 l_consume_req_demand_doc_id po_headers.po_header_id%type;
338 --sql what: get all the req lines which have the same bid/negotiation info as
339 -- those on the lines of the current blanket po approval workflow
340 -- just approved.
341 --sql why : This cursor fetches all these eligible requisition lines and lauches
342 -- start_wf_line_process one by one. This would happen only if
343 -- l_consume_req_demand_doc_id is not null. l_consume_req_demand_doc_id
344 -- is the blanket document id passed into created document workflow
345 -- from PO approval workflow.
346 --sql join: find all the blanket lines of l_consume_req_demand_doc_id,
347 -- equate the bid_number,bid_line_number and auction_header_id
348 -- of these lines to requisition lines from po_requisition_lines.
349 -- Also ensures these lines are not placed on another PO, they are
350 -- still in approved status, not modified by the buyer, source type is
351 -- vendor and not finally closed
352
353 CURSOR C_ConsumeReqLines is
354 SELECT prl.requisition_line_id
355 FROM po_lines pol,
356 po_requisition_lines prl,
357 po_requisition_headers_all prh -- <R12 MOAC>
358 WHERE pol.po_header_id=l_consume_req_demand_doc_id
359 AND prl.auction_header_id = pol.auction_header_id
360 AND prl.bid_line_number = pol.bid_line_number
361 AND prl.bid_number = pol.bid_number
362 AND prl.line_location_id is null
363 AND nvl(prl.cancel_flag,'N') ='N'
364 AND nvl(prl.closed_code,'OPEN') <> 'FINALLY CLOSED'
365 AND nvl(prl.modified_by_agent_flag,'N') ='N'
366 AND prl.source_type_code = 'VENDOR'
367 AND prh.authorization_status = 'APPROVED'
368 AND prh.requisition_header_id = prl.requisition_header_id;
369 --<CONSUME REQ DEMAND FPI END>
370 BEGIN
371 /* Set org context */
372 x_org_id := po_wf_util_pkg.GetItemAttrNumber (itemtype => itemtype,
376 po_moac_utils_pvt.set_org_context(x_org_id); --<R12 MOAC>
373 itemkey => itemkey,
374 aname => 'ORG_ID');
375
377
378 --<CONSUME REQ DEMAND FPI START>
379 l_consume_req_demand_doc_id := PO_WF_UTIL_PKG.GetItemAttrNumber
380 (itemtype => itemtype,
381 itemkey => itemkey,
382 aname => 'CONSUME_REQ_DEMAND_DOC_ID');
383 IF l_consume_req_demand_doc_id is null then
384 --<CONSUME REQ DEMAND FPI END>
385
386 /* If this create doc workflow was called from either the
387 * 10sc form or web req form, they pass in a req_header_id
388 * If the workflow was called by the conc. prg then there
389 * may or may not be a req_header_id passed in.
390 */
391
392
393 x_req_header_id := po_wf_util_pkg.GetItemAttrText (itemtype => itemtype,
394 itemkey => itemkey,
395 aname => 'REQ_HEADER_ID');
396
397 /* Get the group_id for this set of requision lines. The
398 * group_id determines which lines should be processed together,
399 * which in this case are all the lines belonging to the one req.
400 */
401
402 select to_char(PO_WF_GROUP_S.NEXTVAL)
403 into x_group_id
404 from sys.dual;
405
406
407 /* Store the group_id so grouping (later) only considers
408 * records with this group_id.
409 */
410
411 po_wf_util_pkg.SetItemAttrNumber (itemtype => itemtype,
412 itemkey => itemkey,
413 aname => 'GROUP_ID',
414 avalue => x_group_id);
415
416
417
418 /* Open cursor and loop thru all the req lines, launching the
419 * the req line processing workflow for each line.
420 */
421
422 x_progress:= '10: launch_req_line_processing: Just before opening cursor c1 ' ||
423 'for req_header_id = ' || to_char(x_req_header_id);
424
425 IF (g_po_wf_debug = 'Y') THEN
426 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
427 END IF;
428
429 open c1; /* Based on x_req_header_id */
430
431 loop
432 fetch c1 into x_req_line_id;
433 exit when c1%NOTFOUND;
434
435 x_progress:= '20: launch_req_line_processing: In loop,fetched c1 req_line_id = '||
436 to_char(x_req_line_id);
437 IF (g_po_wf_debug = 'Y') THEN
438 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
439 END IF;
440
441 /* Get the unique sequence to make sure item key will be unique */
442
443 select to_char(PO_WF_ITEMKEY_S.NEXTVAL)
444 into x_seq_for_item_key
445 from sys.dual;
446
447 /* The item key is the req_line_id concatenated with the
448 * unique id from a seq.
449 */
450
451 x_ItemKey := to_char(x_req_line_id) || '-' || x_seq_for_item_key;
452
453 /* Launch the req line processing process
454 *
455 * Need to pass in the parent's itemtype and itemkey so as to
456 * all the parent child relationship to be setup in the called
457 * process.
458 */
459
460 x_progress:= '30: launch_req_line_processing: Just about to launch '||
461 ' start_wf_line_process with: called_item_type = ' || x_ItemType
462 || '/ ' || 'called_item_key = ' || x_ItemKey || '/ ' ||
463 'group_id = ' || to_char(x_group_id);
464
465 IF (g_po_wf_debug = 'Y') THEN
466 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
467 END IF;
468
469 po_autocreate_doc.start_wf_line_process (x_ItemType,
470 x_ItemKey,
471 x_workflow_process,
472 x_group_id,
473 x_req_header_id,
474 x_req_line_id,
475 itemtype,
476 itemkey);
477
478 end loop;
479 close c1;
480
481 resultout := wf_engine.eng_completed || ':' || 'ACTIVITY_PERFORMED';
482
483 x_progress:= '40:launch_req_line_processing: result = ACTIVITY_PERFORMED';
484 IF (g_po_wf_debug = 'Y') THEN
485 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
486 END IF;
487
488 --<CONSUME REQ DEMAND FPI START>
489 ELSE --if l_consume_req_demand_doc_id is not null
490
491 --Get the group_id for this set of requision lines. The
492 --group_id determines which lines should be processed together,
493 --which in this case are all the lines belonging to the one req.
494
495 SELECT to_char(PO_WF_GROUP_S.NEXTVAL)
496 INTO x_group_id
497 FROM sys.dual;
498
499 -- Store the group_id so grouping (later) only considers
500 -- records with this group_id.
504 avalue => x_group_id);
501 PO_WF_UTIL_PKG.SetItemAttrNumber (itemtype => itemtype,
502 itemkey => itemkey,
503 aname => 'GROUP_ID',
505
506 -- Open cursor and loop thru all the req lines, launching the
507 --the req line processing workflow for each line.
508
509 x_progress:= '10: launch_req_line_processing: Just before opening '
510 ||'cursor C_ConsumeReqLines '
511 || 'for l_consume_req_demand_doc_id='
512 || to_char(l_consume_req_demand_doc_id);
513
514 IF (g_po_wf_debug = 'Y') THEN
515 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
516 END IF;
517
518 OPEN C_ConsumeReqLines;
519
520 LOOP
521 FETCH C_ConsumeReqLines into x_req_line_id;
522 EXIT WHEN C_ConsumeReqLines%NOTFOUND;
523
524 x_progress:= '20: launch_req_line_processing: In loop,fetched'
525 ||' C_ConsumeReqLines req_line_id = '
526 || to_char(x_req_line_id);
527 IF (g_po_wf_debug = 'Y') THEN
528 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
529 END IF;
530
531 --Get the unique sequence to make sure item key will be unique
532
533 SELECT to_char(PO_WF_ITEMKEY_S.NEXTVAL)
534 INTO x_seq_for_item_key
535 FROM sys.dual;
536
537 --The item key is the req_line_id concatenated with the
538 --unique id from a seq.
539
540 x_ItemKey := to_char(x_req_line_id) || '-' || x_seq_for_item_key;
541
542 --Launch the req line processing process
543 --Need to pass in the parent's itemtype and itemkey so as to
544 --all the parent child relationship to be setup in the called
545 --process.
546
547 x_progress:= '30: launch_req_line_processing: Just about to launch '
548 || ' start_wf_line_process with: called_item_type = '
549 || x_ItemType || '/ ' || 'called_item_key = '
550 || x_ItemKey || '/ ' || 'group_id = '
551 || to_char(x_group_id);
552
553 IF (g_po_wf_debug = 'Y') THEN
554 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
555 END IF;
556
557 po_autocreate_doc.start_wf_line_process (x_ItemType,
558 x_ItemKey,
559 x_workflow_process,
560 x_group_id,
561 x_req_header_id,
562 x_req_line_id,
563 itemtype,
564 itemkey);
565
566 END LOOP;
567 CLOSE C_ConsumeReqLines;
568
569 resultout := wf_engine.eng_completed || ':' || 'ACTIVITY_PERFORMED';
570
571 x_progress:='40:launch_req_line_processing: result =ACTIVITY_PERFORMED';
572 IF (g_po_wf_debug = 'Y') THEN
573 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
574 END IF;
575 END IF; --l_consume_req_demand_doc_id is null--
576 --<CONSUME REQ DEMAND FPI END>
577
578 exception
579 when others then
580 if l_consume_req_demand_doc_id is not null then
581 close c1;
582 else
583 CLOSE C_ConsumeReqLines;
584 end if;
585 wf_core.context('po_autocreate_doc','launch_req_line_processing',x_progress);
586 raise;
587 end launch_req_line_processing;
588
589 /***************************************************************************
590 *
591 * Procedure: start_wf_line_process
592 *
593 * Description: Generates the itemkey, sets up the Item Attributes,
594 * then starts the workflow process.
595 *
596 **************************************************************************/
597 procedure start_wf_line_process ( ItemType VARCHAR2,
598 ItemKey VARCHAR2,
599 workflow_process VARCHAR2,
600 group_id NUMBER,
601 req_header_id NUMBER,
602 req_line_id NUMBER,
603 parent_itemtype VARCHAR2,
604 parent_itemkey VARCHAR2) is
605
606
607 x_progress varchar2(300);
608
609 begin
610
611 x_progress := '10: start_wf_line_process: Called with item_type = ' || ItemType ||
612 '/ '|| 'item_key = ' || ItemKey;
613 IF (g_po_wf_debug = 'Y') THEN
614 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
615 END IF;
616
617 /* If a process is passed then it will be run
618 * If a process is not passed then the selector function defined in
619 * item type will be determine which process to run
620 */
621
622 IF (ItemType is NOT NULL ) AND
623 (ItemKey is NOT NULL) AND
624 (req_line_id is NOT NULL ) then
625 wf_engine.CreateProcess(itemtype => itemtype,
626 itemkey => itemkey,
627 process => workflow_process );
628
629 x_progress := '20: start_wf_line_process: Just after CreateProcess';
630 IF (g_po_wf_debug = 'Y') THEN
631 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
632 END IF;
633
637 itemkey => itemkey,
634 /* Initialize workflow item attributes */
635
636 po_wf_util_pkg.SetItemAttrNumber (itemtype => itemtype,
638 aname => 'GROUP_ID',
639 avalue => group_id);
640
641 po_wf_util_pkg.SetItemAttrNumber (itemtype => itemtype,
642 itemkey => itemkey,
643 aname => 'REQ_LINE_ID',
644 avalue => req_line_id);
645
646 po_wf_util_pkg.SetItemAttrNumber (itemtype => itemtype,
647 itemkey => itemkey,
648 aname => 'REQ_HEADER_ID',
649 avalue => req_header_id);
650
651
652 /* Need to set the parent child relationship between processes */
653
654 wf_engine.SetItemParent (itemtype => itemtype,
655 itemkey => itemkey,
656 parent_itemtype => parent_itemtype,
657 parent_itemkey => parent_itemkey,
658 parent_context => NULL);
659
660
661 /* Kick off the process */
662
663 x_progress:= '30: start_wf_line_process: Kicking off StartProcess';
664 IF (g_po_wf_debug = 'Y') THEN
665 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
666 END IF;
667
668 wf_engine.StartProcess(itemtype => itemtype,
669 itemkey => itemkey );
670
671 end if;
672
673 exception
674 when others then
675 x_progress:= '40: start_wf_line_process: IN EXCEPTION';
676 IF (g_po_wf_debug = 'Y') THEN
677 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
678 END IF;
679 raise;
680 end start_wf_line_process;
681
682
683 /***************************************************************************
684 *
685 * Procedure: get_req_info
686 *
687 * Description: Gets all the necessary info from the req line
688 *
689 *
690 **************************************************************************/
691 procedure get_req_info (itemtype IN VARCHAR2,
692 itemkey IN VARCHAR2,
693 actid IN NUMBER,
694 funcmode IN VARCHAR2,
695 resultout OUT NOCOPY VARCHAR2 ) is
696
697 x_req_line_id number;
698 x_suggested_buyer_id number;
699 x_suggested_vendor_name po_requisition_lines_all.suggested_vendor_name%type;
700 x_suggested_vendor_location varchar2(240);
701
702 /* Bug 2577940 The vendor id and vendor site id should also be populated from Req line */
703 x_suggested_vendor_id po_requisition_lines_all.vendor_id%type;
704 x_suggested_vendor_site_id po_requisition_lines_all.vendor_site_id%type;
705 /* Bug 2577940 */
706
707 x_source_doc_type_code varchar2(25);
708 x_source_doc_po_header_id number;
709 x_source_doc_line_num number;
710 x_rfq_required_flag varchar2(1);
711 x_on_rfq_flag varchar2(1);
712 x_item_id number;
713 x_category_id number;
714 x_currency_code varchar2(15);
715 x_rate_type varchar2(30);
716 x_rate_date date;
717 x_rate number;
718 x_org_id number;
719 x_pcard_id number;
720 x_pcard_flag varchar2(1);
721 x_progress varchar2(300);
722 x_organization_id number;
723 x_catalog_type varchar2(40);
724 /* Supplier Pcard FPH */
725 x_vendor_id number;
726 x_vendor_site_id number;
727
728 x_ga_flag varchar2(1) := 'N'; -- FPI GA
729 l_job_id number := null; -- <SERVICES FPJ>
730 l_labor_req_line_id po_requisition_lines_all.labor_req_line_id%TYPE; -- <SERVICES FPJ>
731
732 begin
733
734 x_req_line_id := po_wf_util_pkg.GetItemAttrNumber (itemtype => itemtype,
735 itemkey => itemkey,
736 aname => 'REQ_LINE_ID');
737
738 /* Get the necessary info from the req line */
739
740 select pls.org_id,
741 pls.suggested_buyer_id,
742 pls.suggested_vendor_name,
743 pls.suggested_vendor_location,
744 pls.document_type_code,
745 pls.blanket_po_header_id,
746 pls.blanket_po_line_num,
747 pls.rfq_required_flag,
748 pls.on_rfq_flag,
749 pls.item_id,
750 pls.category_id,
751 pls.currency_code,
752 pls.rate_type,
753 pls.rate_date,
754 pls.rate,
755 pls.pcard_flag,
756 /* Supplier PCard FPH */
757 decode(pls.pcard_flag, 'Y', phs.pcard_id,'S',-99999,'N', null),
758 pls.destination_organization_id,
759 pls.catalog_type,
760 pls.vendor_id, /* Bug 2577940 */
761 pls.vendor_site_id,
762 pls.job_id, -- <SERVICES FPJ>
763 pls.labor_req_line_id -- <SERVICES FPJ>
764 into x_org_id,
765 x_suggested_buyer_id,
766 x_suggested_vendor_name,
767 x_suggested_vendor_location,
768 x_source_doc_type_code,
769 x_source_doc_po_header_id,
773 x_item_id,
770 x_source_doc_line_num,
771 x_rfq_required_flag,
772 x_on_rfq_flag,
774 x_category_id,
775 x_currency_code,
776 x_rate_type,
777 x_rate_date,
778 x_rate,
779 x_pcard_flag,
780 x_pcard_id,
781 x_organization_id,
782 x_catalog_type,
783 x_suggested_vendor_id,
784 x_suggested_vendor_site_id,
785 l_job_id, -- <SERVICES FPJ>
786 l_labor_req_line_id -- <SERVICES FPJ>
787 from po_requisition_headers_all phs, -- <R12 MOAC>
788 po_requisition_lines pls
789 where pls.requisition_line_id = x_req_line_id
790 and phs.requisition_header_id = pls.requisition_header_id;
791
792
793 x_progress:= '10: get_req_info: Just after executing sql stmt with req_line_id ' ||
794 to_char(x_req_line_id);
795 IF (g_po_wf_debug = 'Y') THEN
796 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
797 END IF;
798
799
800 /* Set the item attributes */
801
802 po_wf_util_pkg.SetItemAttrNumber (itemtype => itemtype,
803 itemkey => itemkey,
804 aname => 'ORG_ID',
805 avalue => x_org_id);
806
807 po_wf_util_pkg.SetItemAttrNumber (itemtype => itemtype,
808 itemkey => itemkey,
809 aname => 'SUGGESTED_BUYER_ID',
810 avalue => x_suggested_buyer_id);
811
812 po_wf_util_pkg.SetItemAttrText (itemtype => itemtype,
813 itemkey => itemkey,
814 aname => 'SUGGESTED_VENDOR_NAME',
815 avalue => x_suggested_vendor_name);
816
817 po_wf_util_pkg.SetItemAttrText (itemtype => itemtype,
818 itemkey => itemkey,
819 aname => 'SUGGESTED_VENDOR_LOCATION',
820 avalue => x_suggested_vendor_location);
821 /* Bug 2577940 */
822 po_wf_util_pkg.SetItemAttrNumber (itemtype => itemtype,
823 itemkey => itemkey,
824 aname => 'SUGGESTED_VENDOR_ID',
825 avalue => x_suggested_vendor_id);
826
827 po_wf_util_pkg.SetItemAttrNumber (itemtype => itemtype,
828 itemkey => itemkey,
829 aname => 'SUGGESTED_VENDOR_SITE_ID',
830 avalue => x_suggested_vendor_site_id);
831 /* Bug 2577940 */
832 po_wf_util_pkg.SetItemAttrText (itemtype => itemtype,
833 itemkey => itemkey,
834 aname => 'SOURCE_DOCUMENT_TYPE_CODE',
835 avalue => x_source_doc_type_code);
836
837 po_wf_util_pkg.SetItemAttrNumber (itemtype => itemtype,
838 itemkey => itemkey,
839 aname => 'SOURCE_DOCUMENT_ID',
840 avalue => x_source_doc_po_header_id);
841
842 /* FPI GA Start */
843 /* Get the global agreement flag */
844
845 if x_source_doc_po_header_id is not null then
846 select global_agreement_flag
847 into x_ga_flag
848 from po_headers_all
849 where po_header_id = x_source_doc_po_header_id;
850 end if;
851
852 po_wf_util_pkg.SetItemAttrText (itemtype => itemtype,
853 itemkey => itemkey,
854 aname => 'SOURCE_DOC_GA_FLAG',
855 avalue => x_ga_flag);
856
857
858 /* FPI GA End */
859
860 po_wf_util_pkg.SetItemAttrNumber (itemtype => itemtype,
861 itemkey => itemkey,
862 aname => 'SOURCE_DOCUMENT_LINE_NUM',
863 avalue => x_source_doc_line_num);
864
865 po_wf_util_pkg.SetItemAttrText (itemtype => itemtype,
866 itemkey => itemkey,
867 aname => 'RFQ_REQUIRED_FLAG',
868 avalue => x_rfq_required_flag);
869
870 po_wf_util_pkg.SetItemAttrText (itemtype => itemtype,
871 itemkey => itemkey,
872 aname => 'ON_RFQ_FLAG',
873 avalue => x_on_rfq_flag);
874
875 po_wf_util_pkg.SetItemAttrNumber (itemtype => itemtype,
876 itemkey => itemkey,
877 aname => 'ITEM_ID',
878 avalue => x_item_id);
879
880 po_wf_util_pkg.SetItemAttrNumber (itemtype => itemtype,
881 itemkey => itemkey,
882 aname => 'CATEGORY_ID',
883 avalue => x_category_id);
884
885 po_wf_util_pkg.SetItemAttrNumber (itemtype => itemtype,
886 itemkey => itemkey,
887 aname => 'ORGANIZATION_ID',
891 -- Added the follow 4 lines to populate the currency_code, rate_type, rate_date and rate.
888 avalue => x_organization_id);
889
890 -- Bug 587589, lpo, 12/11/97
892 po_wf_util_pkg.SetItemAttrText (itemtype => itemtype,
893 itemkey => itemkey,
894 aname => 'CURRENCY_CODE',
895 avalue => x_currency_code);
896
897 po_wf_util_pkg.SetItemAttrText (itemtype => itemtype,
898 itemkey => itemkey,
899 aname => 'RATE_TYPE',
900 avalue => x_rate_type);
901
902 po_wf_util_pkg.SetItemAttrDate (itemtype => itemtype,
903 itemkey => itemkey,
904 aname => 'RATE_DATE',
905 avalue => x_rate_date);
906
907 po_wf_util_pkg.SetItemAttrNumber (itemtype => itemtype,
908 itemkey => itemkey,
909 aname => 'RATE',
910 avalue => x_rate);
911
912 po_wf_util_pkg.SetItemAttrNumber (itemtype => itemtype,
913 itemkey => itemkey,
914 aname => 'PCARD_ID',
915 avalue => x_pcard_id);
916
917 po_wf_util_pkg.SetItemAttrText (itemtype => itemtype,
918 itemkey => itemkey,
919 aname => 'CATALOG_TYPE',
920 avalue => x_catalog_type);
921 -- Bug 587589, lpo, 12/11/97
922
923 --<Shared Proc FPJ START>
924 --Set the PURCHASING_ORG_ID workflow item attribute
925 set_purchasing_org_id(itemtype,
926 itemkey,
927 x_org_id,
928 x_suggested_vendor_site_id);
929 --<Shared Proc FPJ END>
930
931 -- <SERVICES FPJ START>
932 po_wf_util_pkg.SetItemAttrNumber(itemtype => itemtype,
933 itemkey => itemkey,
934 aname => 'JOB_ID',
935 avalue => l_job_id);
936
937 po_wf_util_pkg.SetItemAttrNumber(itemtype => itemtype,
938 itemkey => itemkey,
939 aname => 'LABOR_REQ_LINE_ID',
940 avalue => l_labor_req_line_id);
941 -- <SERVICES FPJ END>
942
943 resultout := wf_engine.eng_completed || ':' || 'ACTIVITY_PERFORMED';
944
945 x_progress:= '20: get_req_info: result = ACTIVITY_PERFORMED';
946 IF (g_po_wf_debug = 'Y') THEN
947 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
948 END IF;
949
950 exception
951 when others then
952 wf_core.context('po_autocreate_doc','get_req_info',x_progress);
953 raise;
954 end get_req_info;
955
956
957 /***************************************************************************
958 *
959 * Procedure: rfq_required_check
960 *
961 * Description: Checks if an this req line should be on an RFQ before
962 * it can be autocreated
963 *
964 **************************************************************************/
965 procedure rfq_required_check (itemtype IN VARCHAR2,
966 itemkey IN VARCHAR2,
967 actid IN NUMBER,
968 funcmode IN VARCHAR2,
969 resultout OUT NOCOPY VARCHAR2 ) is
970
971 x_org_id number;
972 x_rfq_required_flag varchar2(1);
973 x_on_rfq_flag varchar2(1);
974 x_warn_rfq_required varchar2(1);
975 x_progress varchar2(300);
976
977
978 begin
979
980 /* Set org context */
981 x_org_id := po_wf_util_pkg.GetItemAttrNumber (itemtype => itemtype,
982 itemkey => itemkey,
983 aname => 'ORG_ID');
984 po_moac_utils_pvt.set_org_context(x_org_id); --<R12 MOAC>
985
986 /* Get rfq check flags */
987
988 x_rfq_required_flag := po_wf_util_pkg.GetItemAttrText
989 (itemtype => itemtype,
990 itemkey => itemkey,
991 aname => 'RFQ_REQUIRED_FLAG');
992
993 x_on_rfq_flag := po_wf_util_pkg.GetItemAttrText
994 (itemtype => itemtype,
995 itemkey => itemkey,
996 aname => 'ON_RFQ_FLAG');
997
998 /* In 10sc this profile option will just warn the user not stop him
999 * from going ahead. We could potentially send a notification to the
1000 * requestor (preparer) here and wait for a response that its ok
1001 * to go ahead, but for now we'll just fail the req line.
1002 */
1003
1004 fnd_profile.get('PO_AUTOCREATE_WARN_RFQ_REQUIRED', x_warn_rfq_required);
1005
1006 if ((x_warn_rfq_required = 'Y') AND
1007 (x_rfq_required_flag = 'Y') AND
1008 ((x_on_rfq_flag is NULL) OR (x_on_rfq_flag = 'N'))) then
1009 resultout := wf_engine.eng_completed || ':' || 'Y';
1010
1011 x_progress:= '10: rfq_required_check: result = Y';
1012 IF (g_po_wf_debug = 'Y') THEN
1016 else
1013 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
1014 END IF;
1015
1017 resultout := wf_engine.eng_completed || ':' || 'N';
1018
1019 x_progress:= '20: rfq_required_check: result = N';
1020 IF (g_po_wf_debug = 'Y') THEN
1021 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
1022 END IF;
1023
1024 end if;
1025
1026
1027 exception
1028 when others then
1029 wf_core.context('po_autocreate_doc','rfq_required_check',x_progress);
1030 raise;
1031 end rfq_required_check;
1032
1033
1034 /***************************************************************************
1035 *
1036 * Procedure: get_supp_info_for_acrt
1037 *
1038 * Description: Gets the suggested supplier/site info from the req line
1039 * and makes sure they are valid.
1040 *
1041 **************************************************************************/
1042 procedure get_supp_info_for_acrt (itemtype IN VARCHAR2,
1043 itemkey IN VARCHAR2,
1044 actid IN NUMBER,
1045 funcmode IN VARCHAR2,
1046 resultout OUT NOCOPY VARCHAR2 ) is
1047
1048 x_suggested_vendor_name po_requisition_lines_all.suggested_vendor_name%type;
1049 x_suggested_vendor_site varchar2(240);
1050
1051 /* Bug 2577940 */
1052 x_suggested_vendor_id number;
1053 x_suggested_vendor_site_id number;
1054 x_vendor po_requisition_lines_all.suggested_vendor_name%type;
1055 x_vendor_site varchar2(240);
1056 /* Bug 2577940 */
1057
1058 x_valid_vendor varchar2(1);
1059 x_valid_vendor_site varchar2(1);
1060 x_vendor_id number;
1061 x_vendor_site_id number;
1062 x_progress varchar2(300);
1063 x_org_id number;
1064
1065 begin
1066
1067 /* Set the org context. */
1068
1069 x_org_id := po_wf_util_pkg.GetItemAttrNumber (itemtype => itemtype,
1070 itemkey => itemkey,
1071 aname => 'ORG_ID');
1072
1073 po_moac_utils_pvt.set_org_context(x_org_id); --<R12 MOAC>
1074
1075 x_suggested_vendor_name := po_wf_util_pkg.GetItemAttrText
1076 (itemtype => itemtype,
1077 itemkey => itemkey,
1078 aname => 'SUGGESTED_VENDOR_NAME');
1079
1080 x_suggested_vendor_site := po_wf_util_pkg.GetItemAttrText
1081 (itemtype => itemtype,
1082 itemkey => itemkey,
1083 aname => 'SUGGESTED_VENDOR_LOCATION');
1084 /* Bug 2577940 */
1085 x_suggested_vendor_id := po_wf_util_pkg.GetItemAttrNumber
1086 (itemtype => itemtype,
1087 itemkey => itemkey,
1088 aname => 'SUGGESTED_VENDOR_ID');
1089
1090 x_suggested_vendor_site_id := po_wf_util_pkg.GetItemAttrNumber
1091 (itemtype => itemtype,
1092 itemkey => itemkey,
1093 aname => 'SUGGESTED_VENDOR_SITE_ID');
1094 /* Bug 2577940 */
1095
1096 /* Here we should do some validation since the req form doesn't validate
1097 * the vendor or vendor site against an lov.
1098 * Actually the lov for the source doc on the req line does have the suggested
1099 * vendor as part of the where clause. So the source doc check would have failed
1100 * since it would have been null for an invalid supplier. But no harm double
1101 * checking here. We need to get the suggested vendor/vendor_site id's anyway
1102 * since the req line doesn't store them.
1103 */
1104
1105 /* Check to see if the vendor on the req line is a valid one */
1106
1107 x_vendor := x_suggested_vendor_name;
1108 x_vendor_site := x_suggested_vendor_site;
1109 x_vendor_id := x_suggested_vendor_id;
1110 x_vendor_site_id := x_suggested_vendor_site_id;
1111
1112
1113 if x_suggested_vendor_id is not null then
1114
1115 /* Bug 2577940 if the id is provided, then it should take the precedence */
1116
1117 begin
1118 select 'Y',
1119 vendor_name
1120 into x_valid_vendor,
1121 x_vendor
1122 from po_suppliers_val_v
1123 where vendor_id = x_suggested_vendor_id;
1124 exception
1125 when NO_DATA_FOUND then
1126 x_valid_vendor := 'N';
1127 end;
1128
1129 /* If the vendor is not valid then we exit right here. */
1130
1131 if (x_valid_vendor ='N') then
1132 resultout := wf_engine.eng_completed || ':' || 'N';
1133
1134 x_progress:= '10: get_supp_info_for_acrt: result = N ' ||
1135 'because the supplier id is invalid';
1136 IF (g_po_wf_debug = 'Y') THEN
1137 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
1138 END IF;
1139
1140 purge_expense_lines(itemtype, itemkey); -- <SERVICES FPJ>
1141
1142 return;
1143 end if;
1144
1145 else /* Bug 2577940 */
1146
1147 /* If the id is null the name will be used */
1151 vendor_id
1148
1149 begin
1150 select 'Y',
1152 into x_valid_vendor,
1153 x_vendor_id
1154 from po_suppliers_val_v
1155 where vendor_name = x_suggested_vendor_name;
1156 exception
1157 when NO_DATA_FOUND then
1158 x_valid_vendor := 'N';
1159 end;
1160
1161 /* If the vendor is not valid then we exit right here. */
1162
1163 if (x_valid_vendor ='N') then
1164 resultout := wf_engine.eng_completed || ':' || 'N';
1165
1166 x_progress:= '20: get_supp_info_for_acrt: result = N ' ||
1167 'because the supplier is null or invalid';
1168 IF (g_po_wf_debug = 'Y') THEN
1169 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
1170 END IF;
1171
1172 purge_expense_lines(itemtype, itemkey); -- <SERVICES FPJ>
1173
1174 return;
1175 end if;
1176
1177 end if; /* Bug 2577940 */
1178
1179 /* If we get to this point then the vendor is valid, now
1180 * lets check for vendor site.
1181 */
1182
1183 if x_suggested_vendor_site_id is not null then /* Bug 2577940 */
1184
1185 /* If the id is provided already then it should take the precedence */
1186
1187 begin
1188
1189 --<Shared Proc FPJ>
1190 --Changed the query to go against po_vendor_sites_all
1191 --instead of po_supplier_sites_val_v.
1192 select 'Y',
1193 vendor_site_code
1194 into x_valid_vendor_site,
1195 x_vendor_site
1196 from po_vendor_sites_all
1197 where vendor_id = x_vendor_id
1198 and vendor_site_id = x_suggested_vendor_site_id
1199 --<Shared Proc FPJ START>
1200 and purchasing_site_flag = 'Y'
1201 and NVL(rfq_only_site_flag, 'N') = 'N'
1202 and sysdate < NVL(inactive_date, sysdate + 1);
1203 --<Shared Proc FPJ END>
1204 exception
1205 when NO_DATA_FOUND then
1206 x_valid_vendor_site := 'N';
1207 end;
1208
1209 /* If the vendor site id is not valid then we exit right here. */
1210
1211 if (x_valid_vendor_site ='N') then
1212 resultout := wf_engine.eng_completed || ':' || 'N';
1213
1214 x_progress:= '30: get_supp_info_for_acrt: result = N ' ||
1215 'becuase the supplier site id is invalid';
1216 IF (g_po_wf_debug = 'Y') THEN
1217 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
1218 END IF;
1219
1220 purge_expense_lines(itemtype, itemkey); -- <SERVICES FPJ>
1221
1222 return;
1223 end if;
1224
1225 else /* Bug 2577940 */
1226
1227 begin
1228 select 'Y',
1229 vendor_site_id
1230 into x_valid_vendor_site,
1231 x_vendor_site_id
1232 from po_supplier_sites_val_v
1233 where vendor_id = x_vendor_id
1234 and vendor_site_code = x_suggested_vendor_site;
1235 exception
1236 when NO_DATA_FOUND then
1237 x_valid_vendor_site := 'N';
1238 end;
1239
1240 /* If the vendor site is not valid then we exit right here. */
1241
1242 if (x_valid_vendor_site ='N') then
1243 resultout := wf_engine.eng_completed || ':' || 'N';
1244
1245 x_progress:= '40: get_supp_info_for_acrt: result = N ' ||
1246 'becuase the supplier site is null or invalid';
1247 IF (g_po_wf_debug = 'Y') THEN
1248 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
1249 END IF;
1250
1251 purge_expense_lines(itemtype, itemkey); -- <SERVICES FPJ>
1252
1253 return;
1254 end if;
1255
1256 end if; /* Bug 2577940 */
1257
1258 /* If we get here then both the vendor and vendor site are valid. */
1259
1260 po_wf_util_pkg.SetItemAttrNumber (itemtype => itemtype,
1261 itemkey => itemkey,
1262 aname => 'SUGGESTED_VENDOR_ID',
1263 avalue => x_vendor_id);
1264
1265 po_wf_util_pkg.SetItemAttrNumber (itemtype => itemtype,
1266 itemkey => itemkey,
1267 aname => 'SUGGESTED_VENDOR_SITE_ID',
1268 avalue => x_vendor_site_id);
1269
1270 /* Bug 2577940 The correct names also should be set */
1271 po_wf_util_pkg.SetItemAttrText (itemtype => itemtype,
1272 itemkey => itemkey,
1273 aname => 'SUGGESTED_VENDOR_NAME',
1274 avalue => x_vendor);
1275
1276 po_wf_util_pkg.SetItemAttrText (itemtype => itemtype,
1277 itemkey => itemkey,
1278 aname => 'SUGGESTED_VENDOR_LOCATION',
1279 avalue => x_vendor_site);
1280 /* Bug 2577940 */
1281
1282 resultout := wf_engine.eng_completed || ':' || 'Y';
1283
1284 x_progress:= '50: get_supp_info_for_acrt: result = Y';
1285 IF (g_po_wf_debug = 'Y') THEN
1286 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
1287 END IF;
1288
1289 exception
1290 when others then
1291 wf_core.context('po_autocreate_doc','get_supp_info_for_acrt',x_progress);
1292 raise;
1296 /***************************************************************************
1293 end get_supp_info_for_acrt;
1294
1295
1297 *
1298 * Procedure: is_source_doc_info_ok
1299 *
1300 * Description: This checks to make sure we have source document
1301 * reference information
1302 *
1303 **************************************************************************/
1304 procedure is_source_doc_info_ok (itemtype IN VARCHAR2,
1305 itemkey IN VARCHAR2,
1306 actid IN NUMBER,
1307 funcmode IN VARCHAR2,
1308 resultout OUT NOCOPY VARCHAR2 ) is
1309
1310
1311 x_source_doc_type_code varchar2(25);
1312 x_source_doc_po_header_id number;
1313 x_source_doc_line_num number;
1314 x_progress varchar2(300);
1315
1316 --Bug 2745549
1317 l_source_doc_ok varchar2(1) := 'N';
1318
1319 begin
1320
1321 /* When the source doc and source line was put onto the req line
1322 * it was all validated to make sure it was ok.
1323 * Ie. docs were within effectivity dates, not canelled or closed etc.
1324 * So not doing the check here again.
1325 * We just need to make sure the source_doc_type, source_doc and
1326 * source_line have been populated.
1327 */
1328
1329 x_source_doc_type_code := po_wf_util_pkg.GetItemAttrText
1330 (itemtype => itemtype,
1331 itemkey => itemkey,
1332 aname => 'SOURCE_DOCUMENT_TYPE_CODE');
1333
1334 x_source_doc_po_header_id := po_wf_util_pkg.GetItemAttrNumber
1335 (itemtype => itemtype,
1336 itemkey => itemkey,
1337 aname => 'SOURCE_DOCUMENT_ID');
1338
1339 x_source_doc_line_num := po_wf_util_pkg.GetItemAttrNumber
1340 (itemtype => itemtype,
1341 itemkey => itemkey,
1342 aname => 'SOURCE_DOCUMENT_LINE_NUM');
1343
1344 if ((x_source_doc_type_code is NULL) or
1345 (x_source_doc_po_header_id is NULL) or
1346 (x_source_doc_line_num is NULL)) then
1347 resultout := wf_engine.eng_completed || ':' || 'N';
1348
1349 x_progress:= '10: is_source_doc_info_ok: result = N';
1350 IF (g_po_wf_debug = 'Y') THEN
1351 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
1352 END IF;
1353
1354 --<Bug 2745549 mbhargav START>
1355 --Do not create PO if the referenced GA/Blanket is not valid
1356 --i.e. is Cancelled or Finally Closed
1357 elsif x_source_doc_type_code = 'BLANKET' THEN
1358
1359 is_ga_still_valid(x_source_doc_po_header_id, l_source_doc_ok);
1360
1361 IF l_source_doc_ok = 'N' THEN
1362 resultout := wf_engine.eng_completed || ':' || 'N';
1363
1364 x_progress:= '20: is_source_doc_info_ok: result = N';
1365 ELSE
1366 resultout := wf_engine.eng_completed || ':' || 'Y';
1367
1368 x_progress:= '20: is_source_doc_info_ok: result = Y';
1369 END IF;
1370
1371 IF (g_po_wf_debug = 'Y') THEN
1372 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
1373 END IF;
1374
1375 --<Bug 2745549 mbhargav END>
1376
1377 else
1378 resultout := wf_engine.eng_completed || ':' || 'Y';
1379
1380 x_progress:= '30: is_source_doc_info_ok: result = Y';
1381 IF (g_po_wf_debug = 'Y') THEN
1382 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
1383 END IF;
1384
1385 end if;
1386
1387 exception
1388 when others then
1389 wf_core.context('po_autocreate_doc','is_source_doc_info_ok',x_progress);
1390 raise;
1391 end is_source_doc_info_ok;
1392
1393 /***************************************************************************
1394 *
1395 * Procedure: does_contract_exist
1396 *
1397 * Description: Check if use_contract_flag is true and if contract
1398 * for the vendor and vendor site exists. This procedure
1399 * is added for self service purchasing.
1400 * this is also validating the expiration of the contract.
1401 *
1402 **************************************************************************/
1403 procedure does_contract_exist(itemtype IN VARCHAR2,
1404 itemkey IN VARCHAR2,
1405 actid IN NUMBER,
1406 funcmode IN VARCHAR2,
1407 resultout OUT NOCOPY VARCHAR2 ) is
1408
1409 x_contract_id number;
1410 x_supplier_id number;
1411 x_supplier_site_id number;
1412
1413 x_use_contract_flag varchar2(1);
1414 x_item_currency PO_REQUISITION_LINES_ALL.currency_code%TYPE;
1415
1416 x_progress varchar2(300) := '000';
1417
1418 -- <GC FPJ START>
1419
1420 l_gc_flag PO_HEADERS_ALL.global_agreement_flag%TYPE;
1421 l_currency PO_HEADERS_ALL.currency_code%TYPE;
1422 l_base_currency PO_HEADERS_ALL.currency_code%TYPE;
1423 l_rate PO_HEADERS_ALL.rate%TYPE;
1424 l_rate_date PO_HEADERS_ALL.rate_date%TYPE;
1425 l_rate_type PO_HEADERS_ALL.rate_type%TYPE;
1426
1430
1427 -- bug4198095
1428 -- No need to match local contract anymore after R12
1429 l_current_org_id PO_HEADERS_ALL.org_id%TYPE;
1431 -- SQL What: Find available contract that is valid. First it
1432 -- finds local contracts; if none exists then find global contracts.
1433 -- Also, latest contract takes priority
1434 -- SQL Why: Need to see if any contract out there that can be attached to
1435 -- the req line, if the req line does not already have a source
1436 -- document
1437
1438 CURSOR c_contract_currency IS
1439 SELECT POH.po_header_id,
1440 POH.global_agreement_flag,
1441 POH.currency_code
1442 FROM po_headers_all POH
1443 WHERE POH.vendor_id = x_supplier_id
1444 AND POH.currency_code = nvl(x_item_currency, l_base_currency)
1445 AND POH.type_lookup_code = 'CONTRACT'
1446 /* R12 GCPA
1447 + If Profile ALLOW_REFERENCING_CPA_UNDER_AMENDMENT is Y, then we can refer any Contract Which is approved Once
1448 + Else Contract should be in APPROVED state */
1449 AND ( (NVL(FND_PROFILE.VALUE('ALLOW_REFERENCING_CPA_UNDER_AMENDMENT'),'N') = 'Y'
1450 AND POH.Approved_Date Is Not Null
1451 )
1452 or ( POH.authorization_status = 'APPROVED' )
1453 )
1454 AND NVL(POH.cancel_flag, 'N') = 'N'
1455 AND NVL(POH.frozen_flag, 'N') = 'N'
1456 AND NVL(POH.closed_code, 'OPEN') = 'OPEN'
1457 AND TRUNC(SYSDATE) BETWEEN NVL(TRUNC(POH.start_date), SYSDATE - 1)
1458 AND NVL(TRUNC(POH.end_date), SYSDATE + 1)
1459 AND POH.global_agreement_flag = 'Y'
1460 /* R12 GCPA
1461 + Vendor Site validation needs to be skipped if Enable All Sites on Contracts is Set to Y. */
1462 AND EXISTS (SELECT 1
1463 FROM po_ga_org_assignments PGOA
1464 WHERE PGOA.po_header_id = POH.po_header_id
1465 AND PGOA.vendor_site_id = decode(Nvl(poh.Enable_All_Sites,'N'),'Y',PGOA.vendor_site_id, x_supplier_site_id)
1466 AND PGOA.organization_id = l_current_org_id
1467 AND PGOA.enabled_flag = 'Y')
1468 ORDER BY POH.creation_date desc;
1469
1470 CURSOR c_contract IS
1471 SELECT POH.po_header_id,
1472 POH.global_agreement_flag,
1473 POH.currency_code
1474 FROM po_headers_all POH
1475 WHERE POH.vendor_id = x_supplier_id
1476 AND POH.type_lookup_code = 'CONTRACT'
1477 /* R12 GCPA
1478 + If Profile ALLOW_REFERENCING_CPA_UNDER_AMENDMENT is Y, then we can refer any Contract Which is approved Once
1479 + Else Contract should be in APPROVED state
1480 */
1481 AND ( (NVL(FND_PROFILE.VALUE('ALLOW_REFERENCING_CPA_UNDER_AMENDMENT'),'N') = 'Y'
1482 AND POH.Approved_Date Is Not Null
1483 )
1484 or ( POH.authorization_status = 'APPROVED' )
1485 )
1486 AND NVL(POH.cancel_flag, 'N') = 'N'
1487 AND NVL(POH.frozen_flag, 'N') = 'N'
1488 AND NVL(POH.closed_code, 'OPEN') = 'OPEN'
1489 AND TRUNC(SYSDATE) BETWEEN NVL(TRUNC(POH.start_date), SYSDATE - 1)
1490 AND NVL(TRUNC(POH.end_date), SYSDATE + 1)
1491 AND POH.global_agreement_flag = 'Y'
1492 /* R12 GCPA
1493 + Vendor Site validation needs to be skipped if Enable All Sites on Contracts is Set to Y. */
1494 AND EXISTS (SELECT 1
1495 FROM po_ga_org_assignments PGOA
1496 WHERE PGOA.po_header_id = POH.po_header_id
1497 AND PGOA.vendor_site_id = decode(Nvl(poh.Enable_All_Sites,'N'),'Y',PGOA.vendor_site_id, x_supplier_site_id)
1498 AND PGOA.organization_id = l_current_org_id
1499 AND PGOA.enabled_flag = 'Y')
1500 ORDER BY POH.creation_date desc;
1501
1502 -- <GC FPJ END>
1503
1504 begin
1505
1506 x_supplier_id := po_wf_util_pkg.GetItemAttrNumber
1507 (itemtype => itemtype,
1508 itemkey => itemkey,
1509 aname => 'SUGGESTED_VENDOR_ID');
1510
1511 x_supplier_site_id := po_wf_util_pkg.GetItemAttrNumber
1512 (itemtype => itemtype,
1513 itemkey => itemkey,
1514 aname => 'SUGGESTED_VENDOR_SITE_ID');
1515
1516 x_use_contract_flag := po_wf_util_pkg.GetItemAttrText
1517 (itemtype => itemtype,
1518 itemkey => itemkey,
1519 aname => 'USE_CONTRACT_FLAG');
1520
1521 x_item_currency := po_wf_util_pkg.GetItemAttrText
1522 (itemtype => itemtype,
1523 itemkey => itemkey,
1524 aname => 'CURRENCY_CODE');
1525
1526 if nvl(x_use_contract_flag, 'N') <> 'Y' then
1527
1528 resultout := wf_engine.eng_completed || ':' || 'N';
1529
1530 return;
1531
1532 end if;
1533
1534 -- <GC FPJ START>
1535
1536 x_progress := '001';
1537
1538 -- The original query has been moved to the declare section as a cursor.
1539 l_base_currency := PO_CORE_S2.get_base_currency;
1540 l_current_org_id := PO_GA_PVT.get_current_org;
1541
1542 OPEN c_contract_currency;
1543
1544 -- Only take the first contract
1545
1546
1547 FETCH c_contract_currency INTO x_contract_id,
1551 IF (c_contract_currency%NOTFOUND) THEN
1548 l_gc_flag,
1549 l_currency;
1550
1552 x_contract_id := NULL;
1553 END IF;
1554
1555 CLOSE c_contract_currency;
1556
1557 if (x_contract_id IS NULL) then
1558
1559 OPEN c_contract;
1560
1561 FETCH c_contract INTO x_contract_id,
1562 l_gc_flag,
1563 l_currency;
1564
1565 IF (c_contract%NOTFOUND) THEN
1566 x_contract_id := NULL;
1567 END IF;
1568
1569 CLOSE c_contract;
1570 end if;
1571
1572 x_progress := '002';
1573
1574 --<Bug 3079146>
1575 IF (x_contract_id IS NOT NULL AND l_gc_flag = 'Y') THEN
1576
1577 l_base_currency := PO_CORE_S2.get_base_currency;
1578
1579 IF (l_base_currency <> l_currency) THEN -- conversion is needed
1580
1581 -- Since a PO line referencing a gloabal contract must be in a PO
1582 -- having the same currency, we need to derive the rate information
1583 -- if the global contract is using a foreign currency. Contract
1584 -- reference is not allowed if currency rate is not defined.
1585
1586 PO_GA_PVT.get_currency_info
1587 ( p_po_header_id => x_contract_id,
1588 x_currency_code => l_currency,
1589 x_rate_type => l_rate_type,
1590 x_rate_date => l_rate_date,
1591 x_rate => l_rate
1592 );
1593
1594 IF (l_rate IS NULL) THEN
1595 x_contract_id := NULL;
1596 ELSE -- rate is defined
1597
1598 po_wf_util_pkg.SetItemAttrText (itemtype => itemtype,
1599 itemkey => itemkey,
1600 aname => 'CURRENCY_CODE',
1601 avalue => l_currency);
1602
1603 po_wf_util_pkg.SetItemAttrText (itemtype => itemtype,
1604 itemkey => itemkey,
1605 aname => 'RATE_TYPE',
1606 avalue => l_rate_type);
1607
1608 po_wf_util_pkg.SetItemAttrDate (itemtype => itemtype,
1609 itemkey => itemkey,
1610 aname => 'RATE_DATE',
1611 avalue => l_rate_date);
1612
1613 po_wf_util_pkg.SetItemAttrNumber (itemtype => itemtype,
1614 itemkey => itemkey,
1615 aname => 'RATE',
1616 avalue => l_rate);
1617 END IF; -- l_rate is null
1618 END IF; -- l_currency <> l_base_currency
1619
1620 IF (x_contract_id IS NOT NULL) THEN
1621 po_wf_util_pkg.SetItemAttrText (itemtype => itemtype,
1622 itemkey => itemkey,
1623 aname => 'SOURCE_DOC_GA_FLAG',
1624 avalue => l_gc_flag);
1625 END IF; -- x_contract_id is not null
1626 END IF; -- l_gc_flag = 'Y'
1627
1628 -- <GC FPJ END>
1629
1630 if x_contract_id is not null then
1631
1632 po_wf_util_pkg.SetItemAttrNumber (itemtype => itemtype,
1633 itemkey => itemkey,
1634 aname => 'CONTRACT_ID',
1635 avalue => x_contract_id);
1636 po_wf_util_pkg.SetItemAttrText( itemtype => itemtype,
1637 itemkey => itemkey,
1638 aname => 'SOURCE_DOCUMENT_TYPE_CODE',
1639 avalue => 'CONTRACT');
1640
1641 -- <GC FPJ START>
1642 -- Since the ref is a contract and is stored in attr CONTRACT_ID,
1643 -- null out the reference in SOURCE_DOCUMENT_ID
1644
1645 po_wf_util_pkg.SetItemAttrNumber (itemtype => itemtype,
1646 itemkey => itemkey,
1647 aname => 'SOURCE_DOCUMENT_ID',
1648 avalue => NULL);
1649
1650 -- <GC FPJ END>
1651
1652 resultout := wf_engine.eng_completed || ':' || 'Y';
1653
1654 else
1655
1656 resultout := wf_engine.eng_completed || ':' || 'N';
1657
1658 end if;
1659
1660 exception
1661
1662 when others then
1663
1664 -- <GC FPJ START>
1665 IF (c_contract%ISOPEN) THEN
1666 CLOSE c_contract;
1667 END IF;
1668 -- <GC FPJ END>
1669
1670 wf_core.context('po_autocreate_doc','does_contract_exist',x_progress);
1671 raise;
1672
1673 end does_contract_exist;
1674
1675 /***************************************************************************
1676 *
1677 * Procedure: is_req_pcard_line
1678 *
1679 * Description: For Pcard req line, it doen't need source doc.
1680 *
1681 **************************************************************************/
1682 procedure is_req_pcard_line (itemtype IN VARCHAR2,
1686 resultout OUT NOCOPY VARCHAR2 ) is
1683 itemkey IN VARCHAR2,
1684 actid IN NUMBER,
1685 funcmode IN VARCHAR2,
1687
1688
1689 x_pcard_id number;
1690
1691 x_progress varchar2(300);
1692
1693 begin
1694
1695 x_pcard_id := po_wf_util_pkg.GetItemAttrNumber
1696 (itemtype => itemtype,
1697 itemkey => itemkey,
1698 aname => 'PCARD_ID');
1699
1700
1701 if (x_pcard_id is NULL) then
1702 resultout := wf_engine.eng_completed || ':' || 'N';
1703
1704 x_progress:= '10: is_req_pcard_line: result = N';
1705 IF (g_po_wf_debug = 'Y') THEN
1706 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
1707 END IF;
1708
1709 purge_expense_lines(itemtype, itemkey); -- <SERVICES FPJ>
1710
1711 else
1712 resultout := wf_engine.eng_completed || ':' || 'Y';
1713
1714 x_progress:= '20: is_req_pcard_line: result = Y';
1715 IF (g_po_wf_debug = 'Y') THEN
1716 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
1717 END IF;
1718
1719 end if;
1720
1721 exception
1722 when others then
1723 wf_core.context('po_autocreate_doc','is_req_pcard_line',x_progress);
1724 raise;
1725 end is_req_pcard_line;
1726
1727 /***************************************************************************
1728 *
1729 * Procedure: get_buyer_from_req_line
1730 *
1731 * Description: Gets the suggested buyer on the req line
1732 *
1733 *
1734 **************************************************************************/
1735 procedure get_buyer_from_req_line (itemtype IN VARCHAR2,
1736 itemkey IN VARCHAR2,
1737 actid IN NUMBER,
1738 funcmode IN VARCHAR2,
1739 resultout OUT NOCOPY VARCHAR2 ) is
1740
1741 x_suggested_buyer_id number;
1742 x_progress varchar2(300);
1743
1744 -- bug2821542
1745 l_validate_result VARCHAR2(1) := FND_API.G_TRUE;
1746
1747 begin
1748
1749 x_suggested_buyer_id := po_wf_util_pkg.GetItemAttrNumber
1750 (itemtype => itemtype,
1751 itemkey => itemkey,
1752 aname => 'SUGGESTED_BUYER_ID');
1753
1754 -- bug2821542
1755 IF (x_suggested_buyer_id IS NOT NULL) THEN
1756 validate_buyer(p_agent_id => x_suggested_buyer_id,
1757 x_result => l_validate_result);
1758 END IF;
1759
1760 if (x_suggested_buyer_id is NULL OR
1761 l_validate_result = FND_API.G_FALSE) then -- bug2821542
1762
1763 resultout := wf_engine.eng_completed || ':' || 'ACTION_FAILED';
1764
1765 x_progress:= '10: get_buyer_from_req_line: result = ACTION_FAILED';
1766 IF (g_po_wf_debug = 'Y') THEN
1767 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
1768 END IF;
1769
1770 else
1771 resultout := wf_engine.eng_completed || ':' || 'ACTION_SUCCEEDED';
1772
1773 x_progress:= '20: get_buyer_from_req_line: result = ACTION_SUCCEEDED';
1774 IF (g_po_wf_debug = 'Y') THEN
1775 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
1776 END IF;
1777
1778 end if;
1779
1780 exception
1781 when others then
1782 wf_core.context('po_autocreate_doc','get_buyer_from_req_line',x_progress);
1783 raise;
1784 end get_buyer_from_req_line;
1785
1786
1787 /***************************************************************************
1788 *
1789 * Procedure: get_buyer_from_item
1790 *
1791 * Description: Gets the buyer from the item master based on the
1792 * item on the requisition line.
1793 *
1794 **************************************************************************/
1795 procedure get_buyer_from_item (itemtype IN VARCHAR2,
1796 itemkey IN VARCHAR2,
1797 actid IN NUMBER,
1798 funcmode IN VARCHAR2,
1799 resultout OUT NOCOPY VARCHAR2 ) is
1800
1801 x_item_id number;
1802 x_buyer_id number;
1803 x_inv_org_id number;
1804 x_org_id number;
1805 x_progress varchar2(300);
1806
1807 -- bug2821542
1808 l_validate_result VARCHAR2(1) := FND_API.G_TRUE;
1809 l_purchasing_org_id PO_HEADERS_ALL.org_id%TYPE; --<Shared Proc FPJ>
1810
1811 begin
1812
1813 x_org_id := po_wf_util_pkg.GetItemAttrNumber (itemtype => itemtype,
1814 itemkey => itemkey,
1815 aname => 'ORG_ID');
1816
1817 --<Shared Proc FPJ START>
1818 l_purchasing_org_id := po_wf_util_pkg.GetItemAttrNumber
1819 (itemtype => itemtype,
1820 itemkey => itemkey,
1821 aname => 'PURCHASING_ORG_ID');
1822 --<Shared Proc FPJ END>
1823 x_item_id := po_wf_util_pkg.GetItemAttrNumber
1824 (itemtype => itemtype,
1828 --<Shared Proc FPJ>
1825 itemkey => itemkey,
1826 aname => 'ITEM_ID');
1827
1829 --Get default inventory org of purchasing org from the fsp_all table.
1830
1831 select inventory_organization_id
1832 into x_inv_org_id
1833 from financials_system_params_all
1834 where org_id = l_purchasing_org_id;
1835
1836 /* Now get the buyer from the item master. There may/may not be one
1837 * assigned to the item. MTL_SYSTEM_ITEMS is a table that isn't striped
1838 * org.
1839 */
1840
1841 begin
1842 select buyer_id
1843 into x_buyer_id
1844 from mtl_system_items
1845 where inventory_item_id = x_item_id
1846 and organization_id = x_inv_org_id;
1847 exception
1848 /* For one time items this will not return anything */
1849 when NO_DATA_FOUND then
1850 x_buyer_id := null;
1851 end;
1852
1853 -- bug2821542
1854 IF (x_buyer_id IS NOT NULL) THEN
1855 validate_buyer(p_agent_id => x_buyer_id,
1856 x_result => l_validate_result);
1857 END IF;
1858
1859 if (x_buyer_id is NULL OR
1860 l_validate_result = FND_API.G_FALSE) then -- bug2821542
1861
1862 resultout := wf_engine.eng_completed || ':' || 'ACTION_FAILED';
1863
1864 x_progress:='10: get_buyer_from_item: result = ACTION_FAILED';
1865 IF (g_po_wf_debug = 'Y') THEN
1866 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
1867 END IF;
1868
1869 else
1870 po_wf_util_pkg.SetItemAttrNumber (itemtype => itemtype,
1871 itemkey => itemkey,
1872 aname => 'SUGGESTED_BUYER_ID',
1873 avalue => x_buyer_id);
1874
1875 resultout := wf_engine.eng_completed || ':' || 'ACTION_SUCCEEDED';
1876
1877 x_progress:='20: get_buyer_from_item: result = ACTION_SUCCEEDED';
1878 IF (g_po_wf_debug = 'Y') THEN
1879 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
1880 END IF;
1881
1882 end if;
1883
1884 exception
1885 when others then
1886 wf_core.context('po_autocreate_doc','get_buyer_from_item',x_progress);
1887 raise;
1888 end get_buyer_from_item;
1889
1890
1891 /***************************************************************************
1892 *
1893 * Procedure: get_buyer_from_category
1894 *
1895 * Description: Gets buyer from the category on the req line
1896 *
1897 *
1898 **************************************************************************/
1899 procedure get_buyer_from_category (itemtype IN VARCHAR2,
1900 itemkey IN VARCHAR2,
1901 actid IN NUMBER,
1902 funcmode IN VARCHAR2,
1903 resultout OUT NOCOPY VARCHAR2 ) is
1904
1905 x_category_id number;
1906 x_agent_id number := null;
1907 x_progress varchar2(300);
1908
1909 -- bug2821542
1910 l_validate_result VARCHAR2(1) := FND_API.G_TRUE;
1911
1912 begin
1913
1914 x_category_id := po_wf_util_pkg.GetItemAttrNumber
1915 (itemtype => itemtype,
1916 itemkey => itemkey,
1917 aname => 'CATEGORY_ID');
1918
1919 /* Bug - 1895237
1920
1921 /* Get the buyer from the buyers table.
1922 If the HR profile Cross Business Groups is set to 'Y' , get it from
1923 po_agents as po_agents is not striped. But if that profile is set to No,
1924 then get only buyers for that business group. */
1925
1926
1927 If (nvl(hr_general.get_xbg_profile, 'N') = 'Y') then
1928
1929 begin
1930 select agent_id
1931 into x_agent_id
1932 from po_agents
1933 where category_id = x_category_id
1934 and trunc(sysdate) between start_date_active
1935 and nvl(end_date_active, sysdate+1);
1936
1937 exception
1938
1939 /* It's possible that the same category is assigned to multiple buyers*/
1940
1941 when TOO_MANY_ROWS then
1942
1943 x_agent_id := NULL;
1944
1945 x_progress := '10: get_buyer_from_category: More than 1 buyer for this category ';
1946 IF (g_po_wf_debug = 'Y') THEN
1947 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
1948 END IF;
1949
1950 when NO_DATA_FOUND then
1951
1952 x_agent_id := NULL;
1953
1954 x_progress := '20:get_buyer_from_category: No buyer assinged to category';
1955 IF (g_po_wf_debug = 'Y') THEN
1956 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
1957 END IF;
1958
1959 end;
1960
1961 else
1962
1963 begin
1964
1965 /* Bug - 1915033 - Added the effectivity dates condition for
1966 per_people_f also and also introduced TRUNC function */
1967
1968 select agent_id
1969 into x_agent_id
1970 from po_agents poa, per_people_f ppf, financials_system_parameters fsp
1971 where poa.agent_id = ppf.person_id
1975 and poa.category_id = x_category_id
1972 and ppf.business_group_id = fsp.business_group_id
1973 and trunc(sysdate) between ppf.effective_start_date
1974 and nvl(ppf.effective_end_date, sysdate+1)
1976 and trunc(sysdate) between poa.start_date_active
1977 and nvl(poa.end_date_active, sysdate+1);
1978
1979 exception
1980
1981 /* It's possible that the same category is assigned to multiple buyers in the
1982 same business group */
1983
1984 when TOO_MANY_ROWS then
1985
1986 x_agent_id := NULL;
1987
1988 x_progress := '10: get_buyer_from_category: More than 1 buyer for this category ';
1989 IF (g_po_wf_debug = 'Y') THEN
1990 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
1991 END IF;
1992
1993 when NO_DATA_FOUND then
1994
1995 x_agent_id := NULL;
1996
1997 x_progress := '20:get_buyer_from_category: No buyer assinged to category';
1998 IF (g_po_wf_debug = 'Y') THEN
1999 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
2000 END IF;
2001
2002 end;
2003
2004 end if;
2005
2006 -- bug2821542
2007 IF (x_agent_id IS NOT NULL) THEN
2008 validate_buyer(p_agent_id => x_agent_id,
2009 x_result => l_validate_result);
2010 END IF;
2011
2012 if (x_agent_id is NULL OR
2013 l_validate_result = FND_API.G_FALSE) then -- bug2821542
2014
2015 resultout := wf_engine.eng_completed || ':' || 'ACTION_FAILED';
2016
2017 x_progress := '30:get_buyer_from_category: result = ACTION_FAILED';
2018 IF (g_po_wf_debug = 'Y') THEN
2019 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
2020 END IF;
2021
2022
2023 else
2024 po_wf_util_pkg.SetItemAttrNumber (itemtype => itemtype,
2025 itemkey => itemkey,
2026 aname => 'SUGGESTED_BUYER_ID',
2027 avalue => x_agent_id);
2028
2029 resultout := wf_engine.eng_completed || ':' || 'ACTION_SUCCEEDED';
2030
2031 x_progress := '40:get_buyer_from_category: result = ACTION_SUCCEEDED';
2032 IF (g_po_wf_debug = 'Y') THEN
2033 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
2034 END IF;
2035
2036 end if;
2037
2038 exception
2039
2040 when others then
2041 wf_core.context('po_autocreate_doc','get_buyer_from_category',x_progress);
2042 raise;
2043
2044 end get_buyer_from_category;
2045
2046 /***************************************************************************
2047 *
2048 * Procedure: get_buyer_from_source_doc
2049 *
2050 * Description: Gets buyer from the source doc
2051 *
2052 *
2053 **************************************************************************/
2054 procedure get_buyer_from_source_doc (itemtype IN VARCHAR2,
2055 itemkey IN VARCHAR2,
2056 actid IN NUMBER,
2057 funcmode IN VARCHAR2,
2058 resultout OUT NOCOPY VARCHAR2 ) is
2059
2060 x_source_doc_po_header_id number;
2061 x_source_doc_type_code varchar2(25);
2062 x_agent_id number := null;
2063 x_progress varchar2(300);
2064
2065 -- bug2821542
2066 l_validate_result VARCHAR2(1) := FND_API.G_TRUE;
2067
2068 begin
2069
2070 x_source_doc_type_code := po_wf_util_pkg.GetItemAttrText
2071 (itemtype => itemtype,
2072 itemkey => itemkey,
2073 aname => 'SOURCE_DOCUMENT_TYPE_CODE');
2074
2075 x_source_doc_po_header_id := po_wf_util_pkg.GetItemAttrNumber
2076 (itemtype => itemtype,
2077 itemkey => itemkey,
2078 aname => 'SOURCE_DOCUMENT_ID');
2079 if( x_source_doc_type_code = 'BLANKET' or
2080 x_source_doc_type_code = 'QUOTATION') then
2081 /* Get the buyer from the PO headers table.
2082 */
2083 begin
2084 /*Bug 928568-removed the _all reference and used the striped table
2085 table instead */
2086
2087 --<Shared Proc FPJ>
2088 -- Modified the query to select from po_headers_all instead of po_headers.
2089 select agent_id
2090 into x_agent_id
2091 from po_headers_all
2092 where po_header_id = x_source_doc_po_header_id;
2093 exception
2094 when NO_DATA_FOUND then
2095 x_agent_id := NULL;
2096 x_progress := '10:get_buyer_from_source_doc: Source Doc id is wrong';
2097 IF (g_po_wf_debug = 'Y') THEN
2098 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
2099 END IF;
2100 end;
2101 else
2102 /* Right now, it's for pcard one-time item po, need to figure out
2103 * how to get buyer*/
2104 null;
2105 end if;
2106
2107 -- bug2821542
2108 IF (x_agent_id IS NOT NULL) THEN
2109 validate_buyer(p_agent_id => x_agent_id,
2110 x_result => l_validate_result);
2111 END IF;
2112
2113 if (x_agent_id is NULL OR
2117
2114 l_validate_result = FND_API.G_FALSE) then -- bug2821542
2115
2116 resultout := wf_engine.eng_completed || ':' || 'ACTION_FAILED';
2118 x_progress := '30:get_buyer_from_source_doc: result = ACTION_FAILED';
2119 IF (g_po_wf_debug = 'Y') THEN
2120 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
2121 END IF;
2122
2123
2124 else
2125 po_wf_util_pkg.SetItemAttrNumber (itemtype => itemtype,
2126 itemkey => itemkey,
2127 aname => 'SUGGESTED_BUYER_ID',
2128 avalue => x_agent_id);
2129
2130 resultout := wf_engine.eng_completed || ':' || 'ACTION_SUCCEEDED';
2131
2132 x_progress := '40:get_buyer_from_source_doc: result = ACTION_SUCCEEDED';
2133 IF (g_po_wf_debug = 'Y') THEN
2134 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
2135 END IF;
2136
2137 end if;
2138
2139 exception
2140 when others then
2141 wf_core.context('po_autocreate_doc','get_buyer_from_source_doc',x_progress);
2142 raise;
2143 end get_buyer_from_source_doc;
2144
2145 /***************************************************************************
2146 *
2147 * Procedure: get_buyer_from_contract
2148 *
2149 * Description: Gets buyer from the contract
2150 *
2151 *
2152 **************************************************************************/
2153 procedure get_buyer_from_contract (itemtype IN VARCHAR2,
2154 itemkey IN VARCHAR2,
2155 actid IN NUMBER,
2156 funcmode IN VARCHAR2,
2157 resultout OUT NOCOPY VARCHAR2 ) is
2158
2159 x_contract_id number;
2160 x_agent_id number;
2161 x_progress VARCHAR2(300);
2162
2163 -- bug2821542
2164 l_validate_result VARCHAR2(1) := FND_API.G_TRUE;
2165
2166 begin
2167
2168 x_contract_id := po_wf_util_pkg.GetItemAttrNumber
2169 (itemtype => itemtype,
2170 itemkey => itemkey,
2171 aname => 'CONTRACT_ID');
2172
2173 x_progress := '001';
2174
2175 begin
2176
2177 --<Shared Proc FPJ>
2178 --Modified the query to select from po_headers_all instead of po_headers
2179 select agent_id
2180 into x_agent_id
2181 from po_headers_all
2182 where po_header_id = x_contract_id;
2183
2184 exception
2185
2186 when others then
2187 x_agent_id := null;
2188
2189 end;
2190
2191 x_progress := '002';
2192
2193 -- bug2821542
2194 IF (x_agent_id IS NOT NULL) THEN
2195 validate_buyer(p_agent_id => x_agent_id,
2196 x_result => l_validate_result);
2197 END IF;
2198
2199 if (x_agent_id is NULL OR
2200 l_validate_result = FND_API.G_FALSE) then -- bug2821542
2201
2202 resultout := wf_engine.eng_completed || ':' || 'ACTION_FAILED';
2203
2204 x_progress := '30:get_buyer_from_source_doc: result = ACTION_FAILED';
2205 IF (g_po_wf_debug = 'Y') THEN
2206 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
2207 END IF;
2208
2209 purge_expense_lines(itemtype, itemkey); -- <SERVICES FPJ>
2210
2211 else
2212 po_wf_util_pkg.SetItemAttrNumber (itemtype => itemtype,
2213 itemkey => itemkey,
2214 aname => 'SUGGESTED_BUYER_ID',
2215 avalue => x_agent_id);
2216
2217 resultout := wf_engine.eng_completed || ':' || 'ACTION_SUCCEEDED';
2218
2219 x_progress := '40:get_buyer_from_contract: result = ACTION_SUCCEEDED';
2220 IF (g_po_wf_debug = 'Y') THEN
2221 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
2222 END IF;
2223
2224 end if;
2225
2226 exception
2227
2228 when others then
2229 wf_core.context('po_autocreate_doc','get_buyer_from_contract',x_progress);
2230 raise;
2231
2232 end get_buyer_from_contract;
2233
2234 /***************************************************************************
2235 *
2236 * Procedure: get_source_doc_type
2237 *
2238 * Description: Gets the source document type from the req line.
2239 *
2240 *
2241 **************************************************************************/
2242 procedure get_source_doc_type (itemtype IN VARCHAR2,
2243 itemkey IN VARCHAR2,
2244 actid IN NUMBER,
2245 funcmode IN VARCHAR2,
2246 resultout OUT NOCOPY VARCHAR2 ) is
2247
2248 x_source_doc_type_code varchar2(25);
2249 x_progress varchar2(300);
2250 x_ga_flag varchar2(1);
2251 begin
2252
2253 x_source_doc_type_code := po_wf_util_pkg.GetItemAttrText
2254 (itemtype => itemtype,
2255 itemkey => itemkey,
2256 aname => 'SOURCE_DOCUMENT_TYPE_CODE');
2257
2258 /* The source doc must be a blanket or quote so return the
2259 * appropriate value.
2260 */
2261
2265 (itemtype => itemtype,
2262 /* FPI GA Start */
2263 /* Get the GA Flag */
2264 x_ga_flag := po_wf_util_pkg.GetItemAttrText
2266 itemkey => itemkey,
2267 aname => 'SOURCE_DOC_GA_FLAG');
2268
2269 /* FPI GA End */
2270 /* draising 2692119 */
2271 if (x_source_doc_type_code is null) then
2272 resultout := wf_engine.eng_completed || ':' || 'NONE';
2273
2274 x_progress := '10: get_source_doc_type: result = NONE';
2275 IF (g_po_wf_debug = 'Y') THEN
2276 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
2277 END IF;
2278
2279 elsif (x_source_doc_type_code = 'BLANKET') then
2280 if nvl(x_ga_flag,'N') = 'Y' then -- FPI GA
2281 x_progress := '10: get_source_doc_type: result = GLOBAL_PA';
2282 resultout := wf_engine.eng_completed || ':' || 'GLOBAL_PA'; -- FPI GA
2283 else
2284 x_progress := '10: get_source_doc_type: result = BLANKET_PO';
2285 resultout := wf_engine.eng_completed || ':' || 'BLANKET_PO';
2286 end if;
2287
2288 x_progress := '10: get_source_doc_type: result = BLANKET_PO';
2289 IF (g_po_wf_debug = 'Y') THEN
2290 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
2291 END IF;
2292
2293 elsif (x_source_doc_type_code = 'CONTRACT') then
2294 resultout := wf_engine.eng_completed || ':' || 'CONTRACT_PO';
2295
2296 x_progress := '10: get_source_doc_type: result = CONTRACT_PO';
2297 IF (g_po_wf_debug = 'Y') THEN
2298 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
2299 END IF;
2300
2301 else
2302 /* Must be QUOTATION */
2303 resultout := wf_engine.eng_completed || ':' || 'QUOTATION';
2304
2305 x_progress := '10: get_source_doc_type: result = QUOTATION';
2306 IF (g_po_wf_debug = 'Y') THEN
2307 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
2308 END IF;
2309
2310 end if;
2311
2312 exception
2313 when others then
2314 wf_core.context('po_autocreate_doc','get_source_doc_type',x_progress);
2315 raise;
2316 end get_source_doc_type;
2317
2318 /***************************************************************************
2319 *
2320 * Procedure: one_time_item_check
2321 *
2322 * Description: Checks if this is a one-time req line (ie. no
2323 * item num)
2324 *
2325 **************************************************************************/
2326 procedure one_time_item_check (itemtype IN VARCHAR2,
2327 itemkey IN VARCHAR2,
2328 actid IN NUMBER,
2329 funcmode IN VARCHAR2,
2330 resultout OUT NOCOPY VARCHAR2 ) is
2331
2332 x_item_id number;
2333 x_progress varchar2(300);
2334
2335 begin
2336
2337 x_item_id := po_wf_util_pkg.GetItemAttrNumber (itemtype => itemtype,
2338 itemkey => itemkey,
2339 aname => 'ITEM_ID');
2340
2341 /* If item_id is null then this is a one-time item. */
2342
2343 if (x_item_id is NULL) then
2344 resultout := wf_engine.eng_completed || ':' || 'Y';
2345
2346 x_progress:= '10: one_time_item_check: result = Y';
2347 IF (g_po_wf_debug = 'Y') THEN
2348 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
2349 END IF;
2350
2351 else
2352 resultout := wf_engine.eng_completed || ':' || 'N';
2353
2354 x_progress:= '10: one_time_item_check: result = N';
2355 IF (g_po_wf_debug = 'Y') THEN
2356 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
2357 END IF;
2358
2359 end if;
2360
2361 exception
2362 when others then
2363 wf_core.context('po_autocreate_doc','one_time_item_check',x_progress);
2364 raise;
2365 end one_time_item_check;
2366
2367
2368
2369
2370
2371 /***************************************************************************
2372 *
2373 * Procedure: get_rel_gen_method
2374 *
2375 * Description: Gets the release generation method from the asl
2376 * associated with the supplier/site/item combination
2377 * on the req line
2378 *
2379 **************************************************************************/
2380 procedure get_rel_gen_method (itemtype IN VARCHAR2,
2381 itemkey IN VARCHAR2,
2382 actid IN NUMBER,
2383 funcmode IN VARCHAR2,
2384 resultout OUT NOCOPY VARCHAR2 ) is
2385
2386 x_org_id number;
2387 x_inv_org_id number;
2388 x_item_id number;
2389 x_suggested_vendor_id number;
2390 x_suggested_vendor_site_id number;
2391 x_organization_id number;
2392 x_category_id NUMBER; -- bug No 5943024
2393 x_rel_gen_method varchar2(25);
2394 x_progress varchar2(300);
2395
2396 begin
2397
2398 /* Set the org context. Backend create_po process assumes it is in
2399 * an org.
2400 */
2401
2402 x_org_id := po_wf_util_pkg.GetItemAttrNumber
2406
2403 (itemtype => itemtype,
2404 itemkey => itemkey,
2405 aname => 'ORG_ID');
2407 po_moac_utils_pvt.set_org_context(x_org_id); --<R12 MOAC>
2408
2409 /* Retrieve required info from item attributes */
2410
2411 x_item_id := po_wf_util_pkg.GetItemAttrNumber
2412 (itemtype => itemtype,
2413 itemkey => itemkey,
2414 aname => 'ITEM_ID');
2415
2416 x_suggested_vendor_id := po_wf_util_pkg.GetItemAttrNumber
2417 (itemtype => itemtype,
2418 itemkey => itemkey,
2419 aname => 'SUGGESTED_VENDOR_ID');
2420
2421 x_suggested_vendor_site_id := po_wf_util_pkg.GetItemAttrNumber
2422 (itemtype => itemtype,
2423 itemkey => itemkey,
2424 aname => 'SUGGESTED_VENDOR_SITE_ID');
2425
2426 x_organization_id := po_wf_util_pkg.GetItemAttrNumber
2427 (itemtype => itemtype,
2428 itemkey => itemkey,
2429 aname => 'ORGANIZATION_ID');
2430 /* bug no:5943024*/
2431 x_category_id := po_wf_util_pkg.GetItemAttrNumber
2432 (itemtype => itemtype,
2433 itemkey => itemkey,
2434 aname => 'CATEGORY_ID');
2435 /* end of Bug No 5943024*/
2436 /* Get the release geneartion method for the item/vendor/vendor_site
2437 * combination from the asl attributes table.
2438 * We have a subquery in the following query. The purpose of the subquery
2439 * is to get the local asl if both a local and global asl exist for the
2440 * item/vendor/vendor_site combination. Ie. it is possible to have both
2441 * a local and global asl for the same item/vendor/vendor_site combination.
2442 * For local asl's the using_organization_id is set to the inv_org_id.
2443 * For global asl's it is set to -1. Thus the max on the subquery will
2444 * return the local over the global if both exist.
2445 */
2446
2447 /* The release gen method can be:
2448 * Automatic Release/Review (CREATE) (not allowed if encumbrance on)
2449 * Automatic Release (CREATE_AND_APPROVE)
2450 * Release Using Autocrate (MANUAL)
2451 *
2452 * For the first two cases we let the Auotmatic Release Generation conc. program
2453 * pick the req lines up. We will pick up the latter case.
2454 */
2455
2456 BEGIN
2457 /* Bug No :5943024
2458 This query has been modified in such a way that it could select a release generation method even for the asl at commodity level Earlier the query selects release generation method only for item.
2459 */
2460
2461
2462 select paa.release_generation_method
2463 into x_rel_gen_method
2464 from po_asl_attributes_val_v paa
2465 WHERE (Paa.Item_Id = x_item_id
2466 OR (Paa.Item_Id IS NULL
2467 AND x_category_id = Paa.Category_Id
2468 AND NOT EXISTS (SELECT 'commodity level ASL should be used only if there is no item level ASL'
2469 FROM po_Asl_Attributes_val_v Paa4
2470 WHERE Paa4.Item_Id = x_item_id
2471 AND Paa4.Vendor_Id = Paa.Vendor_Id
2472 AND Nvl(Paa4.Vendor_Site_Id,- 1) = Nvl(Paa.Vendor_Site_Id,- 1)
2473 AND Paa4.UsIng_Organization_Id IN (- 1,x_organization_id))))
2474 AND Paa.Vendor_Id = x_suggested_vendor_id
2475 AND (Paa.Vendor_Site_Id IS NULL
2476 OR ( x_suggested_vendor_site_id = Paa.Vendor_Site_Id
2477 AND NOT EXISTS (SELECT 'select supplier line with null supplier site'
2478 FROM po_Asl_Attributes_val_v Paa3
2479 WHERE Nvl(Paa.Item_Id,- 1) = Nvl(Paa3.Item_Id,- 1)
2480 AND Nvl(Paa.Category_Id,- 1) = Nvl(Paa3.Category_Id,- 1)
2481 AND Paa.Vendor_Id = Paa3.Vendor_Id
2482 AND Paa3.Vendor_Site_Id IS NULL
2483 AND Paa3.UsIng_Organization_Id IN (- 1,
2484 x_organization_id))))
2485 AND Paa.UsIng_Organization_Id = (SELECT MAX(Paa2.UsIng_Organization_Id)
2486 FROM po_Asl_Attributes_val_v Paa2
2487 WHERE Nvl(Paa.Item_Id,- 1) = Nvl(Paa2.Item_Id,- 1)
2488 AND Nvl(Paa.Category_Id,- 1) = Nvl(Paa2.Category_Id,- 1)
2489 AND Paa.Vendor_Id = Paa2.Vendor_Id
2490 AND Nvl(Paa.Vendor_Site_Id,- 1) = Nvl(Paa2.Vendor_Site_Id,- 1)
2491 AND Paa2.UsIng_Organization_Id IN (- 1,x_organization_id));
2492 /* end of Bug No 5943024 */
2493
2494 exception
2495 when NO_DATA_FOUND then
2496 x_rel_gen_method :=null;
2497 end;
2498
2499 /* Set item attribute so it can be used later */
2500
2501 po_wf_util_pkg.SetItemAttrText (itemtype => itemtype,
2505
2502 itemkey => itemkey,
2503 aname => 'REL_GEN_METHOD',
2504 avalue => x_rel_gen_method);
2506 if (x_rel_gen_method is NULL) then
2507 resultout := wf_engine.eng_completed || ':' || 'NO_METHOD_FOUND';
2508
2509 x_progress:= '10: get_rel_gen_method: result = NO_METHOD_FOUND';
2510 IF (g_po_wf_debug = 'Y') THEN
2511 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
2512 END IF;
2513
2514 else
2515 resultout := wf_engine.eng_completed || ':' || x_rel_gen_method;
2516
2517 x_progress:= '20: get_rel_gen_method: ' || 'result = ' || x_rel_gen_method;
2518 IF (g_po_wf_debug = 'Y') THEN
2519 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
2520 END IF;
2521 end if;
2522
2523 -- <SERVICES FPJ START>
2524 IF (x_rel_gen_method <> 'MANUAL') THEN
2525 -- Remove any associated expense line
2526 purge_expense_lines(itemtype, itemkey);
2527 END IF;
2528 -- <SERVICES FPJ END>
2529
2530 exception
2531 when others then
2532 wf_core.context('po_autocreate_doc','get_rel_gen_method',x_progress);
2533 raise;
2534 end get_rel_gen_method;
2535
2536
2537 /***************************************************************************
2538 *
2539 * Procedure: cont_wf_autocreate_rel_gen
2540 *
2541 * Description: Decides whether automatic autocreation should
2542 * take place (ie. continue with workflow) if
2543 * the release generation method is AutoCreate.
2544 *
2545 **************************************************************************/
2546 procedure cont_wf_autocreate_rel_gen (itemtype IN VARCHAR2,
2547 itemkey IN VARCHAR2,
2548 actid IN NUMBER,
2549 funcmode IN VARCHAR2,
2550 resultout OUT NOCOPY VARCHAR2 ) is
2551
2552 x_cont_wf_for_ac_rel_gen varchar2(1);
2553 x_progress varchar2(300);
2554
2555 begin
2556
2557 x_cont_wf_for_ac_rel_gen:= po_wf_util_pkg.GetItemAttrText (itemtype => itemtype,
2558 itemkey => itemkey,
2559 aname => 'CONT_WF_FOR_AC_REL_GEN');
2560
2561 if (x_cont_wf_for_ac_rel_gen = 'Y') then
2562 resultout := wf_engine.eng_completed || ':' || 'Y';
2563
2564 x_progress:= '10: cont_wf_autocreate_rel_gen: Y';
2565 IF (g_po_wf_debug = 'Y') THEN
2566 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
2567 END IF;
2568
2569 else
2570 resultout := wf_engine.eng_completed || ':' || 'N';
2571
2572 x_progress:= '20: cont_wf_autocreate_rel_gen: N';
2573 IF (g_po_wf_debug = 'Y') THEN
2574 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
2575 END IF;
2576
2577 -- Remove any associated expense line
2578 purge_expense_lines(itemtype, itemkey); -- <SERVICES FPJ>
2579 end if;
2580
2581 exception
2582 when others then
2583 wf_core.context('po_autocreate_doc','cont_wf_autocreate_rel_gen',x_progress);
2584 raise;
2585 end cont_wf_autocreate_rel_gen;
2586
2587
2588 /***************************************************************************
2589 *
2590 * Procedure: insert_cand_req_lines_into_tbl
2591 *
2592 * Description: Inserts a req line into the temp table.
2593 * This means its possible to try and autocreate this
2594 * line.
2595 *
2596 **************************************************************************/
2597 procedure insert_cand_req_lines_into_tbl (itemtype IN VARCHAR2,
2598 itemkey IN VARCHAR2,
2599 actid IN NUMBER,
2600 funcmode IN VARCHAR2,
2601 resultout OUT NOCOPY VARCHAR2 ) is
2602
2603
2604 x_group_id number;
2605 x_req_header_id number;
2606 x_req_line_id number;
2607 x_suggested_buyer_id number;
2608 x_source_doc_type_code varchar2(25);
2609 x_source_doc_id number;
2610 x_source_doc_line number;
2611 x_suggested_vendor_id number;
2612 x_suggested_vendor_site_id number;
2613 x_currency_code varchar2(15);
2614 x_rate_type varchar2(30);
2615 x_rate_date date;
2616 x_rate number;
2617 x_pcard_id number;
2618 x_rel_gen_method varchar2(25);
2619 x_item_id number;
2620 x_progress varchar2(300);
2621
2622 x_contract_id number;
2623 l_job_id number := null; -- <SERVICES FPJ>
2624
2625 begin
2626
2627 /* Get all the item attributes that we need to put into the
2628 * temp table.
2629 */
2630
2631 /* Not all the fields are needed in the temp table for our
2632 * processing (grouping) to work. But no harm having them there.
2633 * If in the future they are needed then they'll already be there.
2634 */
2635
2636 x_group_id:= po_wf_util_pkg.GetItemAttrNumber
2637 (itemtype => itemtype,
2638 itemkey => itemkey,
2639 aname => 'GROUP_ID');
2643 itemkey => itemkey,
2640
2641 x_req_header_id:= po_wf_util_pkg.GetItemAttrNumber
2642 (itemtype => itemtype,
2644 aname => 'REQ_HEADER_ID');
2645
2646 x_req_line_id:= po_wf_util_pkg.GetItemAttrNumber
2647 (itemtype => itemtype,
2648 itemkey => itemkey,
2649 aname => 'REQ_LINE_ID');
2650
2651 x_suggested_buyer_id := po_wf_util_pkg.GetItemAttrNumber
2652 (itemtype => itemtype,
2653 itemkey => itemkey,
2654 aname => 'SUGGESTED_BUYER_ID');
2655
2656 x_source_doc_type_code := po_wf_util_pkg.GetItemAttrText
2657 (itemtype => itemtype,
2658 itemkey => itemkey,
2659 aname => 'SOURCE_DOCUMENT_TYPE_CODE');
2660
2661 x_source_doc_id := po_wf_util_pkg.GetItemAttrNumber
2662 (itemtype => itemtype,
2663 itemkey => itemkey,
2664 aname => 'SOURCE_DOCUMENT_ID');
2665
2666 x_source_doc_line := po_wf_util_pkg.GetItemAttrNumber
2667 (itemtype => itemtype,
2668 itemkey => itemkey,
2669 aname => 'SOURCE_DOCUMENT_LINE_NUM');
2670
2671
2672 x_suggested_vendor_id := po_wf_util_pkg.GetItemAttrNumber
2673 (itemtype => itemtype,
2674 itemkey => itemkey,
2675 aname => 'SUGGESTED_VENDOR_ID');
2676
2677 x_suggested_vendor_site_id := po_wf_util_pkg.GetItemAttrNumber
2678 (itemtype => itemtype,
2679 itemkey => itemkey,
2680 aname => 'SUGGESTED_VENDOR_SITE_ID');
2681
2682
2683 x_contract_id := po_wf_util_pkg.GetItemAttrNumber
2684 (itemtype => itemtype,
2685 itemkey => itemkey,
2686 aname => 'CONTRACT_ID');
2687
2688
2689 x_currency_code := po_wf_util_pkg.GetItemAttrText
2690 (itemtype => itemtype,
2691 itemkey => itemkey,
2692 aname => 'CURRENCY_CODE');
2693
2694 x_rate_type := po_wf_util_pkg.GetItemAttrText
2695 (itemtype => itemtype,
2696 itemkey => itemkey,
2697 aname => 'RATE_TYPE');
2698
2699 x_rate_date := po_wf_util_pkg.GetItemAttrDate
2700 (itemtype => itemtype,
2701 itemkey => itemkey,
2702 aname => 'RATE_DATE');
2703
2704 x_rate := po_wf_util_pkg.GetItemAttrNumber
2705 (itemtype => itemtype,
2706 itemkey => itemkey,
2707 aname => 'RATE');
2708
2709 x_pcard_id := po_wf_util_pkg.GetItemAttrNumber
2710 (itemtype => itemtype,
2711 itemkey => itemkey,
2712 aname => 'PCARD_ID');
2713
2714 x_rel_gen_method := po_wf_util_pkg.GetItemAttrText
2715 (itemtype => itemtype,
2716 itemkey => itemkey,
2717 aname => 'REL_GEN_METHOD');
2718
2719 x_item_id := po_wf_util_pkg.GetItemAttrText
2720 (itemtype => itemtype,
2721 itemkey => itemkey,
2722 aname => 'ITEM_ID');
2723
2724 -- <SERVICES FPJ>
2725 l_job_id := po_wf_util_pkg.GetItemAttrNumber
2726 (itemtype => itemtype,
2727 itemkey => itemkey,
2728 aname => 'JOB_ID');
2729
2730 /* Insert the req line into the the temp table.
2731 * The req lines in this table will then be picked up
2732 * later to be autocreated.
2733 */
2734
2735 x_progress := '10:insert_cand_req_lines_into_tbl: inserting into temp table for ' ||
2736 'req line = ' || to_char(x_req_line_id);
2737
2738 IF (g_po_wf_debug = 'Y') THEN
2739 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
2740 END IF;
2741
2742 begin
2743 INSERT INTO po_wf_candidate_req_lines_temp
2744 (group_id,
2745 requisition_header_id,
2746 requisition_line_id,
2747 suggested_buyer_id,
2748 source_doc_type_code,
2749 source_doc_id,
2750 source_doc_line,
2751 suggested_vendor_id,
2752 suggested_vendor_site_id,
2753 contract_id,
2754 currency_code,
2755 rate_type,
2756 rate_date,
2757 rate,
2758 pcard_id,
2759 process_code,
2760 release_generation_method,
2761 item_id,
2762 job_id) -- <SERVICES FPJ>
2763 VALUES (x_group_id,
2764 x_req_header_id,
2765 x_req_line_id,
2766 x_suggested_buyer_id,
2767 x_source_doc_type_code,
2768 x_source_doc_id,
2772 x_contract_id,
2769 x_source_doc_line,
2770 x_suggested_vendor_id,
2771 x_suggested_vendor_site_id,
2773 x_currency_code,
2774 x_rate_type,
2775 x_rate_date,
2776 x_rate,
2777 x_pcard_id,
2778 'PENDING',
2779 x_rel_gen_method,
2780 x_item_id,
2781 l_job_id); -- <SERVICES FPJ>
2782 exception
2783 when others then
2784 x_progress := '15: insert_cand_req_lines_into_tbl: IN EXCEPTION when inserting' ||
2785 'into po_wf_candidate_req_lines_temp';
2786 IF (g_po_wf_debug = 'Y') THEN
2787 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
2788 END IF;
2789 raise;
2790 end;
2791
2792 /* Calling process should do the commit, so comment out here.
2793 * COMMIT;
2794 */
2795
2796 resultout := wf_engine.eng_completed || ':' || 'ACTIVITY_PERFORMED';
2797
2798 x_progress:= '20: insert_cand_req_lines_into_tbl: ACTIVITY_PERFORMED';
2799 IF (g_po_wf_debug = 'Y') THEN
2800 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
2801 END IF;
2802
2803 exception
2804 when others then
2805 wf_core.context('po_autocreate_doc','insert_cand_req_lines_into_tbl',x_progress);
2806 raise;
2807 end insert_cand_req_lines_into_tbl;
2808
2809
2810 /***************************************************************************
2811 *
2812 * Procedure: group_req_lines
2813 *
2814 * Description: Groups the requistion lines in the temp table into
2815 * header and line records which it then inserts into
2816 * the interface tables.
2817 *
2818 **************************************************************************/
2819 procedure group_req_lines (itemtype IN VARCHAR2,
2820 itemkey IN VARCHAR2,
2821 actid IN NUMBER,
2822 funcmode IN VARCHAR2,
2823 resultout OUT NOCOPY VARCHAR2 ) is
2824
2825
2826 c1_group_id number;
2827 c1_req_header_id number;
2828 c1_req_line_id number;
2829 c1_suggested_buyer_id number;
2830 c1_source_doc_type_code varchar2(25);
2831 c1_source_doc_id number;
2832 c1_source_doc_line number;
2833 c1_suggested_vendor_id number;
2834 c1_suggested_vendor_site_id number;
2835 c1_currency_code varchar2(15);
2836 c1_rate_type varchar2(30);
2837 c1_rate_date date;
2838 c1_rate number;
2839 c1_process_code varchar2(30);
2840 c1_rel_gen_method varchar2(25);
2841 c1_item_id number;
2842 c1_pcard_id number;
2843 c1_contract_id number;
2844 c1_deliver_to_location_code hr_locations_all.location_code%type;
2845 c1_dest_org_id number; -- Consigned FPI
2846 c1_dest_type_code po_requisition_lines.destination_type_code%TYPE; -- Consigned FPI
2847 c1_cons_from_supp_flag varchar2(1); -- Consigned FPI
2848 c1_labor_req_line_id number; -- <SERVICES FPJ>
2849
2850 c2_rowid rowid;
2851 c2_group_id number;
2852 c2_req_header_id number;
2853 c2_req_line_id number;
2854 c2_suggested_buyer_id number;
2855 c2_source_doc_type_code varchar2(25);
2856 c2_source_doc_id number;
2857 c2_source_doc_line number;
2858 c2_suggested_vendor_id number;
2859 c2_suggested_vendor_site_id number;
2860 c2_currency_code varchar2(15);
2861 c2_rate_type varchar2(30);
2862 c2_rate_date date;
2863 c2_rate number;
2864 c2_process_code varchar2(30);
2865 c2_rel_gen_method varchar2(25);
2866 c2_item_id number;
2867 c2_pcard_id number;
2868 c2_contract_id number;
2869 c2_labor_req_line_id number; -- <SERVICES FPJ>
2870 --<R12 STYLES PHASE II START>
2871 c2_line_type_id number;
2872 c2_purchase_basis varchar2(30);
2873 c1_line_type_id number;
2874 c1_purchase_basis varchar2(30);
2875 l_pcard_id number;
2876 --<R12 STYLES PHASE II END>
2877
2878
2879 l_enable_vmi_flag po_asl_attributes.enable_vmi_flag%TYPE; -- Consigned FPI
2880 l_last_billing_date po_asl_attributes.last_billing_date%TYPE; -- Consigned FPI
2881 l_cons_billing_cycle po_asl_attributes.consigned_billing_cycle%TYPE; -- Consigned FPI
2882
2883 c2_dest_org_id number; -- Consigned FPI
2884 c2_dest_type_code po_requisition_lines.destination_type_code%TYPE; -- Consigned FPI
2885 c2_cons_from_supp_flag varchar2(1); -- Consigned FPI
2886
2887 x_group_id number;
2888 x_first_time_for_this_comb varchar2(5);
2889 x_interface_header_id number;
2890 x_suggested_vendor_contact_id number;
2891 x_suggested_vendor_contact varchar2(240);
2892 c2_deliver_to_location_code hr_locations_all.location_code%type;
2893 x_prev_sug_vendor_contact_id number;
2894 x_carry_contact_to_po_flag varchar2(10);
2895
2896 /* x_grouping_allowed varchar2(1); Bug 2974129 */
2897 x_group_one_time_address varchar2(1);
2898
2899 x_progress varchar2(300);
2900 x_valid number;
2901
2902 c1_ga_flag varchar2(1); -- FPI GA
2906 l_ref_ga_is_valid varchar2(1) := 'N';
2903 c2_ga_flag varchar2(1); -- FPI GA
2904
2905 --Bug 2745549
2907
2908 l_return_status varchar2(1) := NULL;
2909 l_msg_count number := NULL;
2910 l_msg_data varchar2(2000) := NULL;
2911
2912 x_source_contact_id NUMBER :=NULL; -- Bug 3586181
2913 c2_found VARCHAR2(1); -- Bug 3586181
2914
2915
2916 /* Define the cursor which picks up records from the temp table.
2917 * We need the 'for update' since we are going to update the
2918 * process_code.
2919 */
2920 /* Bug # 1721991.
2921 The 'for update' clause was added to update the row which was processed
2922 in the Cursor c2 but this led to another problem in Oracle 8.1.6.3 or above
2923 where you can't have a commit inside a 'for update' Cursor loop.
2924 This let to the Runtime Error 'fetch out of sequence'
2925 The commit was actually issued in the procedure insert_into_header_interface.
2926 To solve this we removed the for update in the cursor and instead used rowid
2927 to update the row processed by the Cursor.
2928 */
2929 -- <SERVICES FPJ>
2930 -- Added labor_req_line_id to the select statement
2931 cursor c1 is /* x_group_id is a parameter */
2932 select prlt.group_id,
2933 prlt.requisition_header_id,
2934 prlt.requisition_line_id,
2935 prlt.suggested_buyer_id,
2936 prlt.source_doc_type_code,
2937 prlt.source_doc_id,
2938 prlt.source_doc_line,
2939 prlt.suggested_vendor_id,
2940 prlt.suggested_vendor_site_id,
2941 prlt.currency_code,
2942 prlt.rate_type,
2943 prlt.rate_date,
2944 prlt.rate,
2945 prlt.process_code,
2946 prlt.release_generation_method,
2947 prlt.item_id,
2948 prlt.pcard_id,
2949 prlt.contract_id,
2950 hrl.location_code,
2951 prl.destination_organization_id,
2952 prl.destination_type_code,
2953 prl.labor_req_line_id
2954 --<R12 STYLES PHASE II START>
2955 ,prl.line_type_id,
2956 prl.purchase_basis
2957 --<R12 STYLES PHASE II END>
2958 from po_wf_candidate_req_lines_temp prlt,
2959 po_requisition_lines prl,
2960 hr_locations_all hrl
2961 where prlt.process_code = 'PENDING'
2962 and prlt.group_id = x_group_id
2963 and prlt.requisition_line_id = prl.requisition_line_id
2964 and prl.deliver_to_location_id = hrl.location_id(+) -- bug 2709046
2965 and prl.reqs_in_pool_flag = 'Y' -- <REQINPOOL>
2966 for update; -- <BUG 5256593>
2967
2968
2969
2970 -- <SERVICES FPJ>
2971 -- Added labor_req_line_id to the select statement
2972 cursor c2 is /* x_group_id is a parameter */
2973 select prlt.rowid, -- Bug# 1721991 , Added rowid to update row processed
2974 prlt.group_id,
2975 prlt.requisition_header_id,
2976 prlt.requisition_line_id,
2977 prlt.suggested_buyer_id,
2978 prlt.source_doc_type_code,
2979 prlt.source_doc_id,
2980 prlt.source_doc_line,
2981 prlt.suggested_vendor_id,
2982 prlt.suggested_vendor_site_id,
2983 prlt.currency_code,
2984 prlt.rate_type,
2985 prlt.rate_date,
2986 prlt.rate,
2987 prlt.process_code,
2988 prlt.release_generation_method,
2989 prlt.item_id,
2990 prlt.pcard_id,
2991 prlt.contract_id,
2992 prl.suggested_vendor_contact,
2993 prl.vendor_contact_id,
2994 hrl.location_code,
2995 prl.destination_organization_id,
2996 prl.destination_type_code,
2997 prl.labor_req_line_id
2998 --<R12 STYLES PHASE II START>
2999 ,prl.line_type_id,
3000 prl.purchase_basis
3001 --<R12 STYLES PHASE II END>
3002
3003 from po_wf_candidate_req_lines_temp prlt,
3004 po_requisition_lines prl,
3005 hr_locations_all hrl
3006 where prlt.process_code = 'PENDING'
3007 and prlt.group_id = x_group_id
3008 and prlt.requisition_line_id = prl.requisition_line_id
3009 and prl.deliver_to_location_id = hrl.location_id(+) -- bug 2709046
3010 and prl.reqs_in_pool_flag = 'Y' -- <REQINPOOL>
3011 --Bug# 1721991, for update;
3012 for update; -- <BUG 5256593>
3013
3014 --<R12 STYLES PHASE II START>
3015 c1_style_id PO_DOC_STYLE_HEADERS.style_id%type;
3016 c2_style_id PO_DOC_STYLE_HEADERS.style_id%type;
3017 c1_valid_style BOOLEAN;
3018 c2_valid_style BOOLEAN;
3019 x_return_status VARCHAR2(1);
3020 x_msg_count NUMBER;
3021 x_msg_data VARCHAR2(2000);
3022 --<R12 STYLES PHASE II END>
3023
3024 begin
3025
3026
3027 /* Get the group_id since we only want to process lines belonging
3028 * to the same group. We need to get the group_id before opening
3029 * the cursor since it is a parameter to the cursor.
3030 */
3031
3032 x_group_id := po_wf_util_pkg.GetItemAttrNumber (itemtype => itemtype,
3033 itemkey => itemkey,
3034 aname => 'GROUP_ID');
3035
3036 /* Bug 2974129. This Grouping allowed flag should not decide the #of documents
3037 Instead this should be applied to group the lines.
3038
3039 x_grouping_allowed := PO_WF_UTIL_PKG.GetItemAttrText (itemtype => itemtype,
3043 x_group_one_time_address := PO_WF_UTIL_PKG.GetItemAttrText (itemtype => itemtype,
3040 itemkey => itemkey,
3041 aname => 'GROUPING_ALLOWED_FLAG'); */
3042
3044 itemkey => itemkey,
3045 aname => 'GROUP_ONE_ADDR_LINE_FLAG');
3046
3047 /* if x_grouping_allowed is NULL then
3048
3049 x_grouping_allowed := 'Y';
3050
3051 end if; Bug 2974129 */
3052
3053 if x_group_one_time_address is NULL then
3054
3055 x_group_one_time_address := 'Y';
3056
3057 end if;
3058
3059 /* Open the cursor with that group_id */
3060 open c1; /* Based on x_group_id */
3061
3062 loop
3063 fetch c1 into c1_group_id,
3064 c1_req_header_id,
3065 c1_req_line_id,
3066 c1_suggested_buyer_id,
3067 c1_source_doc_type_code,
3068 c1_source_doc_id,
3069 c1_source_doc_line,
3070 c1_suggested_vendor_id,
3071 c1_suggested_vendor_site_id,
3072 c1_currency_code,
3073 c1_rate_type,
3074 c1_rate_date,
3075 c1_rate,
3076 c1_process_code,
3077 c1_rel_gen_method,
3078 c1_item_id,
3079 c1_pcard_id,
3080 c1_contract_id,
3081 c1_deliver_to_location_code,
3082 c1_dest_org_id,
3083 c1_dest_type_code,
3084 c1_labor_req_line_id
3085 --<R12 STYLES PHASE II START>
3086 ,c1_line_type_id,
3087 c1_purchase_basis
3088 --<R12 STYLES PHASE II END>
3089 ;
3090 exit when c1%NOTFOUND;
3091
3092 --<R12 STYLES PHASE II START>
3093 c1_valid_style := TRUE;
3094
3095 x_progress := '01: group_req_lines : c1_req_line_id = '|| to_char(c1_req_line_id);
3096 IF (g_po_wf_debug = 'Y') THEN
3097 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
3098 END IF;
3099
3100 -- bug 4923134
3101
3102 x_progress := '01: group_req_lines : c1_source_doc_id = '|| to_char(c1_source_doc_id);
3103 IF (g_po_wf_debug = 'Y') THEN
3104 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
3105 END IF;
3106
3107 x_progress := '01: group_req_lines : c1_contract_id = '|| to_char(c1_contract_id);
3108 IF (g_po_wf_debug = 'Y') THEN
3109 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
3110 END IF;
3111
3112
3113 if (nvl(c1_source_doc_id,c1_contract_id) is not null) then
3114
3115 c1_style_id := PO_DOC_STYLE_PVT.get_doc_style_id(nvl(c1_source_doc_id,c1_contract_id)); -- bug 4923134
3116
3117 x_progress := '01: group_req_lines : c1_style_id = '|| to_char(c1_style_id);
3118 IF (g_po_wf_debug = 'Y') THEN
3119 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
3120 END IF;
3121
3122 PO_DOC_STYLE_PVT.style_validate_req_attrs(p_api_version => 1.0,
3123 p_init_msg_list => fnd_api.g_true,
3124 x_return_status => x_return_status,
3125 x_msg_count => x_msg_count,
3126 x_msg_data => x_msg_data,
3127 p_doc_style_id => c1_style_id,
3128 p_document_id => NULL,
3129 p_line_type_id => c1_line_type_id,
3130 p_purchase_basis => c1_purchase_basis,
3131 p_destination_type => c1_dest_type_code,
3132 p_source => 'AUTOCREATE');
3133
3134 if x_return_status <> FND_API.g_ret_sts_success THEN
3135 c1_valid_style := FALSE;
3136 end if;
3137 --<Bug#5695323 vmaduri START>
3138 --Set Style ID as 1 if P card is used and source doc or cotract are not there
3139 else
3140 c1_style_id:=1;
3141 end if;
3142 --<Bug#5695323 vmaduri END>
3143 --<R12 STYLES PHASE II END>
3144
3145 if c1_valid_style then --< R12 STYLES PHASE II>
3146
3147 x_progress := '01: group_req_lines : c1_req_line_id = '|| to_char(c1_req_line_id)||'valid style';
3148 IF (g_po_wf_debug = 'Y') THEN
3149 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
3150 END IF;
3151
3152 /* FPI GA start */
3153 if (c1_source_doc_id is not null) then
3154 select global_agreement_flag
3155 into c1_ga_flag
3156 from po_headers_all
3157 where po_header_id = c1_source_doc_id;
3158 end if;
3159
3160 /* FPI GA End */
3161
3162 /* Consigned FPI start */
3163 PO_THIRD_PARTY_STOCK_GRP.Get_Asl_Attributes
3164 ( p_api_version => 1.0
3165 , p_init_msg_list => NULL
3166 , x_return_status => l_return_status
3167 , x_msg_count => l_msg_count
3171 , p_vendor_site_id => c1_suggested_vendor_site_id
3168 , x_msg_data => l_msg_data
3169 , p_inventory_item_id => c1_item_id
3170 , p_vendor_id => c1_suggested_vendor_id
3172 , p_using_organization_id => c1_dest_org_id
3173 , x_consigned_from_supplier_flag => c1_cons_from_supp_flag
3174 , x_enable_vmi_flag => l_enable_vmi_flag
3175 , x_last_billing_date => l_last_billing_date
3176 , x_consigned_billing_cycle => l_cons_billing_cycle
3177 );
3178
3179 if c1_cons_from_supp_flag = 'Y' and
3180 nvl(c1_dest_type_code,'INVENTORY') <> 'EXPENSE' then
3181 c1_source_doc_id := null;
3182 c1_contract_id := NULL; -- <GC FPJ>
3183 else
3184 c1_cons_from_supp_flag := 'N';
3185 end if;
3186
3187 /* Consigned FPI end */
3188
3189 --<Bug 2745549 mbhargav START>
3190 --Null out GA information if GA is not valid
3191 if c1_source_doc_id is not null then
3192
3193 is_ga_still_valid(c1_source_doc_id, l_ref_ga_is_valid);
3194
3195 if l_ref_ga_is_valid = 'N' then
3196 c1_source_doc_id := null;
3197 end if;
3198 end if;
3199 --<Bug 2745549 mbhargav END>
3200
3201
3202 /* Supplier PCard FPH. Check whether c1_pcard_id is valid. The function
3203 * will return pcard_id if valid else will have value null if not.
3204 */
3205
3206 If (c1_pcard_id is not null) then
3207 c1_pcard_id := po_pcard_pkg.get_valid_pcard_id(c1_pcard_id,c1_suggested_vendor_id,c1_suggested_vendor_site_id);
3208 end if;
3209 /* Supplier PCard FPH */
3210 x_first_time_for_this_comb := 'TRUE';
3211 x_suggested_vendor_contact_id := NULL;
3212 x_carry_contact_to_po_flag := 'TRUE';
3213 x_prev_sug_vendor_contact_id := NULL;
3214 c2_found :='Y';
3215
3216 open c2;
3217
3218 loop
3219 fetch c2 into c2_rowid, -- Bug# 1721991, Added rowid
3220 c2_group_id,
3221 c2_req_header_id,
3222 c2_req_line_id,
3223 c2_suggested_buyer_id,
3224 c2_source_doc_type_code,
3225 c2_source_doc_id,
3226 c2_source_doc_line,
3227 c2_suggested_vendor_id,
3228 c2_suggested_vendor_site_id,
3229 c2_currency_code,
3230 c2_rate_type,
3231 c2_rate_date,
3232 c2_rate,
3233 c2_process_code,
3234 c2_rel_gen_method,
3235 c2_item_id,
3236 c2_pcard_id,
3237 c2_contract_id,
3238 x_suggested_vendor_contact,
3239 x_suggested_vendor_contact_id,
3240 c2_deliver_to_location_code,
3241 c2_dest_org_id,
3242 c2_dest_type_code,
3243 c2_labor_req_line_id
3244 --<R12 STYLES PHASE II START>
3245 ,c2_line_type_id,
3246 c2_purchase_basis
3247 --<R12 STYLES PHASE II END>
3248 ;
3249
3250
3251 if (c2%rowcount)= 0 then -- Bug 3586181
3252 c2_found:='N';
3253 end if;
3254 exit when c2%NOTFOUND;
3255
3256
3257
3258
3259 --<R12 STYLES PHASE II START>
3260 c2_valid_style := TRUE;
3261 x_progress := '02: group_req_lines : c2_req_line_id = '|| to_char(c2_req_line_id);
3262 IF (g_po_wf_debug = 'Y') THEN
3263 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
3264 END IF;
3265
3266 -- bug 4923134
3267 x_progress := '02: group_req_lines : c2_source_doc_id = '|| to_char(c2_source_doc_id);
3268 IF (g_po_wf_debug = 'Y') THEN
3269 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
3270 END IF;
3271
3272 x_progress := '02: group_req_lines : c2_contract_id = '|| to_char(c2_contract_id);
3273 IF (g_po_wf_debug = 'Y') THEN
3274 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
3275 END IF;
3276
3277 if (nvl(c2_source_doc_id,c2_contract_id) is not null) then
3278
3279 c2_style_id := PO_DOC_STYLE_PVT.get_doc_style_id(nvl(c2_source_doc_id,c2_contract_id)); -- bug 4923134
3280
3281 x_progress := '02: group_req_lines : c2_style_id = '|| to_char(c2_style_id);
3282 IF (g_po_wf_debug = 'Y') THEN
3283 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
3284 END IF;
3285
3286 PO_DOC_STYLE_PVT.style_validate_req_attrs(p_api_version => 1.0,
3287 p_init_msg_list => fnd_api.g_true,
3288 x_return_status => x_return_status,
3289 x_msg_count => x_msg_count,
3290 x_msg_data => x_msg_data,
3291 p_doc_style_id => c2_style_id,
3292 p_document_id => NULL,
3293 p_line_type_id => c2_line_type_id,
3297
3294 p_purchase_basis => c2_purchase_basis,
3295 p_destination_type => c2_dest_type_code,
3296 p_source => 'AUTOCREATE');
3298 if x_return_status <> FND_API.g_ret_sts_success THEN
3299 c2_valid_style := FALSE;
3300 end if;
3301 --<Bug#5695323 vmaduri START>
3302 --Set Style ID as 1 if P card is used and source doc or cotract are not there
3303 else
3304 c2_style_id:=1;
3305 end if;
3306 --<Bug#5695323 vmaduri END>
3307 --<R12 STYLES PHASE II END>
3308
3309 if c2_valid_style then --<R12 STYLES PHASE II>
3310
3311 x_progress := '02: group_req_lines : c2_req_line_id = '|| to_char(c2_req_line_id)||'valid style';
3312 IF (g_po_wf_debug = 'Y') THEN
3313 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
3314 END IF;
3315
3316 /* FPI GA start */
3317 if (c2_source_doc_id is not null) then
3318 select global_agreement_flag
3319 into c2_ga_flag
3320 from po_headers_all
3321 where po_header_id = c2_source_doc_id;
3322 end if;
3323
3324 /* FPI GA End */
3325 /* Consigned FPI start */
3326 PO_THIRD_PARTY_STOCK_GRP.Get_Asl_Attributes
3327 ( p_api_version => 1.0
3328 , p_init_msg_list => NULL
3329 , x_return_status => l_return_status
3330 , x_msg_count => l_msg_count
3331 , x_msg_data => l_msg_data
3332 , p_inventory_item_id => c2_item_id
3333 , p_vendor_id => c2_suggested_vendor_id
3334 , p_vendor_site_id => c2_suggested_vendor_site_id
3335 , p_using_organization_id => c2_dest_org_id
3336 , x_consigned_from_supplier_flag => c2_cons_from_supp_flag
3337 , x_enable_vmi_flag => l_enable_vmi_flag
3338 , x_last_billing_date => l_last_billing_date
3339 , x_consigned_billing_cycle => l_cons_billing_cycle
3340 );
3341
3342 if c2_cons_from_supp_flag = 'Y' and
3343 nvl(c2_dest_type_code,'INVENTORY') <> 'EXPENSE' then
3344 c2_source_doc_id := null;
3345 c2_contract_id := NULL; -- <GC FPJ>
3346 else
3347 c2_cons_from_supp_flag := 'N';
3348 end if;
3349
3350 /* Consigned FPI end */
3351
3352 --<Bug 2745549 mbhargav START>
3353 --Null out GA information if GA is not valid
3354 if c2_source_doc_id is not null then
3355
3356 is_ga_still_valid(c2_source_doc_id, l_ref_ga_is_valid);
3357
3358 if l_ref_ga_is_valid = 'N' then
3359 c2_source_doc_id := null;
3360 end if;
3361 end if;
3362 --<Bug 2745549 mbhargav END>
3363
3364 /* Supplier PCard FPH. Check whether c2_pcard_id is valid. The function
3365 * will return pcard_id if valid else will have value null if not.
3366 */
3367 If (c2_pcard_id is not null) then
3368 c2_pcard_id := po_pcard_pkg.get_valid_pcard_id(c2_pcard_id,c2_suggested_vendor_id,c2_suggested_vendor_site_id);
3369 end if;
3370 /* Supplier PCard FPH */
3371 /* Associate similiar lines with the same header. This is the core
3372 * grouping logic.
3373 */
3374
3375 x_progress := '10: group_req_lines : c1_req_line_id = '
3376 || to_char(c1_req_line_id) || ' c2_req_line_id = '
3377 || to_char(c2_req_line_id);
3378
3379 if (x_suggested_vendor_contact_id is null) then
3380 x_suggested_vendor_contact_id := get_contact_id(x_suggested_vendor_contact, c2_suggested_vendor_site_id);
3381 end if;
3382
3383 IF (g_po_wf_debug = 'Y') THEN
3384 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
3385 END IF;
3386
3387 /* Bug 1362315
3388 When you initiate the create doc workflow from requisition import
3389 for a batch of 5000 requisitions or more, the process
3390 failed to create the po for one or two requisitions bcos
3391 we were not truncating the sysdate before a comparison in the following
3392 if logic and thereby not creating records in the po_headers_interface
3393 table for the autocreate logic to process the req to a PO.
3394 */
3395
3396
3397 /* Add one time location grouping logic */
3398
3399 if (c1_req_line_id = c2_req_line_id) /* Always insert if c1 and c2 is the same line */
3400 OR
3401 ( /* (x_grouping_allowed = 'Y') AND Bug 2974129 */
3402 (x_group_one_time_address = 'Y' OR
3403 (x_group_one_time_address = 'N' AND
3404 c1_deliver_to_location_code <> nvl(fnd_profile.value('POR_ONE_TIME_LOCATION'),-99) AND --bug 4449781 : added nvl
3405 c2_deliver_to_location_code <> nvl(fnd_profile.value('POR_ONE_TIME_LOCATION'),-99))) AND
3406 (c1_suggested_buyer_id = c2_suggested_buyer_id) AND
3407 (c1_style_id = c2_style_id) AND --<R12 STYLES PHASE II>
3408 (c1_suggested_vendor_id = c2_suggested_vendor_id) AND
3409 (c1_suggested_vendor_site_id = c2_suggested_vendor_site_id) AND
3413 (nvl(c1_contract_id,-1) = nvl(c2_contract_id,-1)) AND
3410 (nvl(c1_source_doc_type_code ,'QUOTATION') =
3411 nvl(c2_source_doc_type_code,'QUOTATION')) AND
3412 (nvl(c1_ga_flag,'N') = nvl(c2_ga_flag,'N')) AND -- FPI GA
3414 (nvl(c1_currency_code,'ok') = nvl(c2_currency_code, 'ok')) AND
3415 (nvl(c1_rate_type, 'ok') = nvl(c2_rate_type, 'ok')) AND
3416 ((c1_rate is NULL AND c2_rate is NULL) --<Bug 3343855>
3417 OR
3418 (nvl(c1_rate_date, trunc(sysdate)) = nvl(c2_rate_date, trunc(sysdate)))) AND
3419 (nvl(c1_rate,-1) = nvl(c2_rate, -1)) AND
3420 (nvl(c1_pcard_id,-1) = nvl(c2_pcard_id,-1)) AND
3421 ((nvl(c1_source_doc_id,-1) = nvl(c2_source_doc_id,-1))
3422 OR
3423 (nvl(c1_source_doc_type_code ,'QUOTATION') = 'QUOTATION')
3424 OR
3425 ((nvl(c1_source_doc_type_code,'QUOTATION') = 'BLANKET') AND (nvl(c1_ga_flag,'N') = 'Y'))) -- FPI GA AND
3426 )
3427 -- <SERVICES FPJ START>
3428 OR
3429 (nvl(c1_req_line_id, -1) = nvl(c2_labor_req_line_id, -1))
3430 OR
3431 (nvl(c1_labor_req_line_id, -1) = nvl(c2_req_line_id, -1))
3432 -- <SERVICES FPJ END>
3433 THEN
3434
3435 x_progress := '20: group_req_lines: c1 and c2 match ';
3436 IF (g_po_wf_debug = 'Y') THEN
3437 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
3438 END IF;
3439
3440
3441 /* Update the process code of the current line in the temp table so
3442 * it doesn't get picked up again by the cursor for processing.
3443 */
3444
3445 update po_wf_candidate_req_lines_temp
3446 set process_code = 'PROCESSED'
3447 where rowid=c2_rowid;
3448 -- Bug# 1721991, where current of c2;
3449
3450 x_progress:= '30:group_req_lines: Updated process_code ';
3451 IF (g_po_wf_debug = 'Y') THEN
3452 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
3453 END IF;
3454
3455
3456 if (x_first_time_for_this_comb = 'TRUE') then
3457
3458 --<R12 STYLES PHASE II START>
3459 -- bug5731406
3460 -- The check for source doc id (added through bug4923134) has been
3461 -- removed as c2_style_id is always populated after bug5695323
3462
3463 x_progress:= '30:group_req_lines: is progress payments ';
3464 IF (g_po_wf_debug = 'Y') THEN
3465 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
3466 END IF;
3467
3468 if PO_DOC_STYLE_PVT.is_progress_payments_enabled(c2_style_id) then
3469 l_pcard_id := NULL;
3470 else
3471 l_pcard_id := c2_pcard_id;
3472 end if;
3473
3474 --<R12 STYLES PHASE II END>
3475
3476 if(po_autocreate_doc.insert_into_headers_interface
3477 (itemtype,
3478 itemkey,
3479 c2_group_id,
3480 c2_suggested_vendor_id,
3481 c2_suggested_vendor_site_id,
3482 c2_suggested_buyer_id,
3483 c2_source_doc_type_code,
3484 c2_source_doc_id,
3485 c2_currency_code,
3486 c2_rate_type,
3487 c2_rate_date,
3488 c2_rate,
3489 l_pcard_id, --<R12 STYLES PHASE II>
3490 c2_style_id, --<R12 STYLES PHASE II>
3491 x_interface_header_id) = FALSE) then
3492 exit; --bug 3401653: po creation failed, skip out of inner loop
3493 end if;
3494
3495
3496
3497 po_autocreate_doc.insert_into_lines_interface (itemtype,
3498 itemkey,
3499 x_interface_header_id,
3500 c2_req_line_id,
3501 c2_source_doc_line,
3502 c2_source_doc_type_code,
3503 c2_contract_id,
3504 c2_source_doc_id, -- GA FPI
3505 c2_cons_from_supp_flag); -- Consigned FPI
3506
3507 /* Bug 3586181 When the document is Contract or Global Aggrement
3508 get the vendor contact from them*/
3509
3510 Begin
3511 if ((NVL(c1_source_doc_type_code,'BLANKET')='CONTRACT') ) then
3512
3513 select vendor_contact_id
3514 into x_source_contact_id
3515 from po_headers_all
3516 where po_header_id=c2_contract_id;
3517
3518 elsif (NVL(c2_ga_flag,'N')='Y') then -- For Global Aggrement.
3519
3520 select vendor_contact_id
3521 into x_source_contact_id
3522 from po_headers_all -- To take care of GAs in Diff Operating unit
3523 where po_header_id=c2_source_doc_id;
3524 else
3525 x_source_contact_id := null;
3526 end if;
3527 Exception
3528 when no_data_found then x_source_contact_id := NULL;
3529 end;
3530
3531 /* End 3586181*/
3532
3536 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
3533 x_progress := '40: group_req_lines: inserted header'||
3534 ' and line for req line = ' || to_char(c2_req_line_id);
3535 IF (g_po_wf_debug = 'Y') THEN
3537 END IF;
3538 /* bug 2656323
3539 Added code to update vendor_contact_id when po_headers is inserted for first time. */
3540 if (x_carry_contact_to_po_flag = 'TRUE' and
3541 valid_contact(c2_suggested_vendor_site_id, x_suggested_vendor_contact_id)) then
3542 begin
3543 update po_headers_interface
3544 set vendor_contact_id = x_suggested_vendor_contact_id
3545 where interface_header_id = x_interface_header_id;
3546 exception
3547 when others then
3548 IF (g_po_wf_debug = 'Y') THEN
3549 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
3550 END IF;
3551 end;
3552 end if;
3553
3554
3555
3556 x_first_time_for_this_comb := 'FALSE';
3557 --bug#3586181
3558 if (x_suggested_vendor_contact_id is not NULL) then
3559 x_prev_sug_vendor_contact_id := x_suggested_vendor_contact_id;
3560 end if;
3561 --bug#3586181
3562
3563
3564 else /* ie. x_first_time_for_this_comb = FALSE */
3565
3566 /* The line we are checking now can put put onto the same header
3567 * as a previous one, so only insert a new line into the
3568 * po_lines_interface table.
3569 */
3570
3571 po_autocreate_doc.insert_into_lines_interface (itemtype,
3572 itemkey,
3573 x_interface_header_id,
3574 c2_req_line_id,
3575 c2_source_doc_line,
3576 c2_source_doc_type_code,
3577 c2_contract_id,
3578 c2_source_doc_id, -- GA FPI
3579 c2_cons_from_supp_flag); -- Consigned FPI
3580
3581 x_progress := '50: group_req_lines: inserted just line for '||
3582 'req line = ' || to_char(c2_req_line_id);
3583 IF (g_po_wf_debug = 'Y') THEN
3584 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
3585 END IF;
3586
3587 --bug#3586181
3588 if (x_carry_contact_to_po_flag = 'TRUE') then -- SS
3589 if ( x_suggested_vendor_contact_id is not null and x_prev_sug_vendor_contact_id is not null) and
3590 (x_suggested_vendor_contact_id <> x_prev_sug_vendor_contact_id) then -- SS
3591 x_carry_contact_to_po_flag := 'FALSE';
3592 end if;
3593 end if;
3594
3595 -- Start Bug 5250863
3596 if(x_suggested_vendor_contact_id is not null and
3597 x_prev_sug_vendor_contact_id is null )then
3598 x_prev_sug_vendor_contact_id := x_suggested_vendor_contact_id;
3599 end if;
3600 -- End Bug 5250863
3601 --bug#3586181
3602
3603 end if;
3604
3605 end if;
3606 /* Commented by Bug 5250863
3607 --bug#3586181
3608 if(x_suggested_vendor_contact_id is not null)then
3609 x_prev_sug_vendor_contact_id := x_suggested_vendor_contact_id;
3610 end if;
3611 --bug#3586181
3612 */
3613 else
3614 x_progress := '02: group_req_lines : c2_req_line_id = '|| to_char(c2_req_line_id)||'invalid style';
3615 IF (g_po_wf_debug = 'Y') THEN
3616 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
3617 END IF;
3618
3619 end if ; --if c2_valid_style --<R12 STYLES PHASE II>
3620
3621 end loop;
3622 /* Commented this code as we are updating vendor_contact_id when header is inserted first time.
3623 if (x_carry_contact_to_po_flag = 'TRUE' and
3624 valid_contact(c2_suggested_vendor_site_id, x_suggested_vendor_contact_id)) then
3625 begin
3626 x_progress := '55: group_req_lines: updating header with vendor contact :'||x_interface_header_id;
3627 update po_headers_interface
3628 set vendor_contact_id = x_suggested_vendor_contact_id
3629 where interface_header_id = x_interface_header_id;
3630 exception
3631 when others then
3632 IF (g_po_wf_debug = 'Y') THEN
3633 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
3634 END IF;
3635 end;
3636 end if;
3637 */
3638 close c2;
3639
3640
3641
3642 /* Bug 3586181 Update the contact id if the either Contract or GA has
3643 got a valid contact */
3644 if (c2_found='Y') then
3645 Begin
3646
3647 if ( x_source_contact_id is not null) then
3648 update po_headers_interface
3649 set vendor_contact_id = x_source_contact_id
3650 where interface_header_id = x_interface_header_id;
3651
3652 elsif (x_carry_contact_to_po_flag = 'FALSE') then -- Implies contacts in Req lines are different
3653 update po_headers_interface
3654 set vendor_contact_id = NULL
3655 where interface_header_id = x_interface_header_id;
3659 where interface_header_id = x_interface_header_id;
3656 elsif (x_carry_contact_to_po_flag = 'TRUE') and (x_prev_sug_vendor_contact_id is not null) then
3657 update po_headers_interface
3658 set vendor_contact_id = x_prev_sug_vendor_contact_id
3660
3661 end if;
3662 end;
3663 end if;
3664 /* End 3586181 */
3665 else
3666 x_progress := '01: group_req_lines : c1_req_line_id = '|| to_char(c1_req_line_id)||'invalid style';
3667 IF (g_po_wf_debug = 'Y') THEN
3668 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
3669 END IF;
3670 end if; --if c1_valid_style --<R12 STYLES PHASE II>
3671
3672
3673 end loop;
3674
3675 close c1;
3676
3677 /* Calling process should do the commit, so comment out here.
3678 * COMMIT;
3679 */
3680
3681 resultout := wf_engine.eng_completed || ':' || 'ACTIVITY_PERFORMED';
3682
3683 x_progress := '60: group_req_lines: result = ACTIVITY_PERFORMED ';
3684 IF (g_po_wf_debug = 'Y') THEN
3685 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
3686 END IF;
3687
3688 exception
3689 when others then
3690 close c1;
3691 close c2;
3692 wf_core.context('po_autocreate_doc','group_req_lines',x_progress);
3693 raise;
3694 end group_req_lines;
3695
3696
3697 /***************************************************************************
3698 *
3699 * Procedure: insert_into_headers_interface
3700 *
3701 * Description: Inserts a row into the po_headers_interface
3702 * Returns false if creating PO header fails, and true otherwise (bug 3401653)
3703 *
3704 **************************************************************************/
3705 function insert_into_headers_interface (itemtype IN VARCHAR2,
3706 itemkey IN VARCHAR2,
3707 x_group_id IN NUMBER,
3708 x_suggested_vendor_id IN NUMBER,
3709 x_suggested_vendor_site_id IN NUMBER,
3710 x_suggested_buyer_id IN NUMBER,
3711 x_source_doc_type_code IN VARCHAR2,
3712 x_source_doc_id IN NUMBER,
3713 x_currency_code IN VARCHAR2,
3714 x_rate_type IN VARCHAR2,
3715 x_rate_date IN DATE,
3716 x_rate IN NUMBER,
3717 x_pcard_id IN NUMBER,
3718 p_style_id IN NUMBER, --<R12 STYLES PHASE II>
3719 x_interface_header_id IN OUT NOCOPY NUMBER)
3720 RETURN boolean is --bug 3401653
3721
3722
3723 x_batch_id number;
3724 x_creation_date date := sysdate;
3725 x_last_update_date date := sysdate;
3726 x_created_by number;
3727 x_last_updated_by number;
3728 x_org_id number;
3729 x_doc_type_to_create varchar2(25);
3730 x_release_date date;
3731 x_document_num varchar2(25);
3732 x_release_num number;
3733 x_release_num1 number;
3734 x_currency_code_doc varchar2(15);
3735 x_found varchar2(30);
3736
3737 x_no_releases number;
3738 x_ga_flag varchar2(1); -- FPI GA
3739 x_progress varchar2(300);
3740
3741 x_grouping_allowed varchar2(1); /* Bug 2974129 */
3742 x_group_code po_headers_interface.group_code%TYPE; /* Bug 2974129 */
3743 l_purchasing_org_id po_headers_all.org_id%TYPE; --<Shared Proc FPJ>
3744
3745 --begin bug 3401653
3746 l_source_doc_currency_code po_headers_all.currency_code%TYPE := NULL;
3747 l_pou_currency_code po_headers_all.currency_code%TYPE;
3748 l_rou_currency_code po_headers_all.currency_code%TYPE;
3749 l_pou_sob_id gl_sets_of_books.set_of_books_id%TYPE;
3750 l_pou_default_rate_type po_headers_all.rate_type%TYPE;
3751 l_interface_rate po_headers_all.rate%TYPE := NULL;
3752 l_interface_rate_type po_headers_all.rate_type%TYPE := NULL;
3753 l_interface_rate_date po_headers_all.rate_date%TYPE := NULL;
3754 l_display_rate po_headers_all.rate%TYPE := NULL;
3755 --end bug 3401653
3756
3757 begin
3758
3759 /* Set the org context. Backend create_po process assumes it is in
3760 * an org.
3761 */
3762
3763 x_org_id := po_wf_util_pkg.GetItemAttrNumber
3764 (itemtype => itemtype,
3765 itemkey => itemkey,
3766 aname => 'ORG_ID');
3767
3768 --<Shared Proc FPJ START>
3769
3770 x_progress := '10:insert_into_headers_interface:' ||
3771 'just before set_purchasing_org_id';
3772
3773 set_purchasing_org_id(itemtype,
3774 itemkey,
3775 x_org_id,
3776 x_suggested_vendor_site_id);
3777
3778 l_purchasing_org_id := po_wf_util_pkg.GetItemAttrNumber
3779 (itemtype => itemtype,
3780 itemkey => itemkey,
3781 aname => 'PURCHASING_ORG_ID');
3782
3783 x_progress:= '20: insert_into_headers_interface: org_id = ' ||
3784 to_char(x_org_id) || ' purchasing_org_id = ' ||
3785 to_char(l_purchasing_org_id);
3786
3787 --<Shared Proc FPJ END>
3788
3789
3790 /* Bug 2974129.
3794
3791 This attribute should decide the grouping logic in Auto Create. If this is set Y,
3792 then the 'DEFAULT' will be populated as grope code else 'REQUISITION' will be
3793 populated as group code */
3795 x_grouping_allowed := PO_WF_UTIL_PKG.GetItemAttrText (itemtype => itemtype,
3796 itemkey => itemkey,
3797 aname => 'GROUPING_ALLOWED_FLAG');
3798
3799 if x_grouping_allowed = 'N' then
3800 x_group_code := 'REQUISITION';
3801 else
3802 x_group_code := 'DEFAULT';
3803 end if;
3804
3805
3806 po_moac_utils_pvt.set_org_context(x_org_id); --<R12 MOAC>
3807
3808 /* Get user values */
3809
3810 x_created_by := to_number(FND_PROFILE.VALUE('user_id'));
3811 x_last_updated_by := to_number(FND_PROFILE.VALUE('user_id'));
3812
3813
3814 /* Get the interface_header_id from the sequence */
3815
3816 select po_headers_interface_s.nextval
3817 into x_interface_header_id
3818 from sys.dual;
3819
3820 /* Set the batch id which can be the same as
3821 * the interface_header_id since we create only one
3822 * po at a time from workflow
3823 */
3824
3825 x_batch_id := x_interface_header_id;
3826
3827 /* If the source doc is a blanket then we are going to create a blanket release.
3828 * If the source doc is a quotation then we are going to create a standard po.
3829 */
3830
3831 /* FPI GA - If ga flag is Y then we create a standard PO */
3832
3833 -- Bug 2695074 getting the ga flag from the db as the attribute does not have any value
3834 -- in this process
3835
3836 if x_source_doc_id is not null then
3837 select global_agreement_flag, currency_code
3838 into x_ga_flag, l_source_doc_currency_code
3839 from po_headers_all
3840 where po_header_id = x_source_doc_id;
3841 end if;
3842
3843 /* Bug 2735730.
3844 * If x_source_doc_id is null, then it would be only in the case
3845 * when the supplier is set up as a consigned enabled and the
3846 * destination type is INVENTORY for the requisition. In this case,
3847 * we should still create a Standard PO. Hence x_doc_type_to_create
3848 * should be STANDARD in this case.
3849 */
3850 if (x_source_doc_id is null) then
3851 x_doc_type_to_create := 'STANDARD';
3852 else
3853 if (x_source_doc_type_code = 'BLANKET')
3854 and nvl(x_ga_flag,'N') = 'N' then -- FPI GA
3855 x_doc_type_to_create := 'RELEASE';
3856 else
3857 x_doc_type_to_create := 'STANDARD';
3858 end if;
3859 end if;
3860
3861
3862 if (x_doc_type_to_create = 'STANDARD') then
3863
3864 /* Whether automatic numbering is on our not, we are going to use
3865 * the automatic number from the unique identifier table. This is
3866 * as per req import. If however we have an po num (eg. emergency po)
3867 * passed into the workflow then we need to use that.
3868 *
3869 * The autocreate backend will take whatever doc num we give it and
3870 * will try and create that. If we weren't to pass in a doc num and
3871 * automatic numbering was on, it would get the next number.
3872 *
3873 * If we are not using automatic numbering but we get the po num
3874 * from the unique identifier table then we could get a number that
3875 * has been used (entered manually by the user). We need to make sure
3876 * that the doc number is unique here since the backend expects that
3877 * when using manual numbering.
3878 */
3879
3880 x_document_num := po_wf_util_pkg.GetItemAttrText
3881 (itemtype => itemtype,
3882 itemkey => itemkey,
3883 aname => 'PO_NUM_TO_CREATE');
3884
3885 if (x_document_num is NULL) then
3886
3887 x_progress := '30: insert_into_headers_interface: Just about to get doc' ||
3888 'num from po_unique_identifier_control';
3889
3890 IF (g_po_wf_debug = 'Y') THEN
3891 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
3892 END IF;
3893
3894 /*
3895 Bug# 1869409
3896 Created a function get_document_num to get the next doucument
3897 Number from the PO_UNIQUE_IDENTIFIER_CONTROL table. This was
3898 done as the Commit after the UPDATE of the PO_UNIQUE_IDENTIFIER_CONTROL
3899 table was also affecting the Workflow transactions.
3900 The function get_document_num is an autonomous transaction.
3901 */
3902 --<Shared Proc FPJ>
3903 --Get document num in purchasing org
3904 x_document_num := get_document_num(l_purchasing_org_id);
3905
3906 x_progress := '40: insert_into_headers_interface: Got doc' ||
3907 'num from po_unique_identifier_control';
3908 IF (g_po_wf_debug = 'Y') THEN
3909 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
3910 END IF;
3911
3912 end if;
3913
3914 /* Check to make sure the doc num is not a duplicate */
3915
3916 begin
3917 --<Shared Proc FPJ>
3918 --Modified the query to select from po_headers_all instead of po_headers.
3919 select 'PO EXISTS'
3920 into x_found
3921 from po_headers_all
3925
3922 where segment1 = x_document_num
3923 and org_id = l_purchasing_org_id -- <R12 MOAC>
3924 and type_lookup_code IN ('STANDARD', 'PLANNED', 'BLANKET', 'CONTRACT');
3926 exception
3927 when NO_DATA_FOUND then
3928 null;
3929 when others then
3930 /* We have found a duplicate so raise the exception */
3931
3932 x_progress := '45: insert_into_headers_interface: document_num is a ' ||
3933 'duplicate - not going to insert into po_headers_interface';
3934 IF (g_po_wf_debug = 'Y') THEN
3935 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
3936 END IF;
3937
3938 raise;
3939 end;
3940
3941 x_release_num := NULL;
3942 x_release_date := NULL;
3943
3944
3945 --begin bug 3401653
3946 select sob.currency_code, fsp.set_of_books_id
3947 into l_pou_currency_code, l_pou_sob_id
3948 from financials_system_params_all fsp,
3949 gl_sets_of_books sob
3950 where fsp.set_of_books_id = sob.set_of_books_id
3951 and fsp.org_id = l_purchasing_org_id; -- <R12 MOAC>
3952
3953 select default_rate_type
3954 into l_pou_default_rate_type
3955 from po_system_parameters_all psp --<Shared Proc FPJ>
3956 where psp.org_id = l_purchasing_org_id; -- <R12 MOAC> removed nvl --<Shared Proc FPJ>
3957
3958 select sob.currency_code
3959 into l_rou_currency_code
3960 from financials_system_params_all fsp,
3961 gl_sets_of_books sob
3962 where fsp.set_of_books_id = sob.set_of_books_id
3963 and fsp.org_id = x_org_id;
3964 --end bug 3401653
3965
3966 /* Bug:565623. gtummala. 10/17/97
3967 * The backend also needs the currency_code to be populated in the
3968 * the po_headers_interface table. Should use functional currency if
3969 * its null.
3970 */
3971 if (x_currency_code is NULL) then
3972 x_currency_code_doc := l_pou_currency_code;
3973 else
3974 x_currency_code_doc := x_currency_code;
3975 end if;
3976
3977
3978 --begin bug 3401653
3979
3980 IF(l_source_doc_currency_code is not null) THEN
3981 x_currency_code_doc := l_source_doc_currency_code;
3982 END IF;
3983
3984 l_interface_rate_date := x_rate_date;
3985 IF(l_purchasing_org_id = x_org_id) THEN --x_org_id is req_org_id
3986 IF(x_currency_code_doc <> l_rou_currency_code) THEN
3987 --rate from req can go to po because pou=rou
3988 l_interface_rate_type := x_rate_type;
3989 l_interface_rate := x_rate;
3990 END IF;
3991 ELSE
3992 IF(l_pou_currency_code <> x_currency_code_doc) THEN
3993 IF l_pou_default_rate_type IS NULL THEN
3994 IF (g_po_wf_debug = 'Y') THEN
3995 x_progress := '47: insert_into_headers_interface: Purchasing Operating unit' ||
3996 ' has no default rate type, cannot create PO';
3997 po_wf_debug_pkg.insert_debug (itemtype, itemkey, x_progress);
3998 END IF;
3999 return FALSE;
4000 END IF;
4001
4002 -- copy rate info for PO currency to pou_currency
4003 l_interface_rate_type := l_pou_default_rate_type;
4004 l_interface_rate_date := trunc(sysdate);
4005 PO_CURRENCY_SV.get_rate(x_set_of_books_id => l_pou_sob_id,
4006 x_currency_code => x_currency_code_doc,
4007 x_rate_type => l_pou_default_rate_type,
4008 x_rate_date => l_interface_rate_date,
4009 x_inverse_rate_display_flag => 'N',
4010 x_rate => l_interface_rate,
4011 x_display_rate => l_display_rate);
4012
4013 END IF;
4014 IF(l_rou_currency_code <> x_currency_code_doc) THEN
4015 IF l_pou_default_rate_type IS NULL THEN
4016 IF (g_po_wf_debug = 'Y') THEN
4017 x_progress := '47: insert_into_headers_interface: Purchasing Operating unit' ||
4018 ' has no default rate type, cannot create PO';
4019 po_wf_debug_pkg.insert_debug (itemtype, itemkey, x_progress);
4020 END IF;
4021
4022 return FALSE;
4023 END IF;
4024
4025 -- Fail creation of the PO if there is no rate to convert from
4026 -- ROU currency to PO currency
4027 IF(PO_CURRENCY_SV.rate_exists (
4028 p_from_currency => l_rou_currency_code,
4029 p_to_currency => x_currency_code_doc,
4030 p_conversion_date => trunc(sysdate),
4031 p_conversion_type => l_pou_default_rate_type) <> 'Y')
4032 THEN
4033 IF (g_po_wf_debug = 'Y') THEN
4034 x_progress := '48: insert_into_headers_interface: No rate defined to' ||
4035 ' convert from Requesting OU currency to PO currency, cannot create PO';
4036 po_wf_debug_pkg.insert_debug (itemtype, itemkey, x_progress);
4037 END IF;
4038 return FALSE;
4039 END IF;
4040 END IF;
4041 END IF;
4042 --end bug 3401653
4043
4047
4044
4045
4046 else
4048 -- Doc is RELEASE
4049 -- Bug 4471683
4050 -- If Currency code is null get the functional currency
4051 IF x_currency_code is not NULL THEN
4052 x_currency_code_doc := x_currency_code;
4053 ELSE
4054 x_currency_code_doc := PO_CORE_S2.get_base_currency;
4055 END IF;
4056
4057 l_interface_rate_type := x_rate_type; --bug 3401653
4058 l_interface_rate_date := x_rate_date; --bug 3401653
4059 l_interface_rate := x_rate; --bug 3401653
4060
4061 select segment1
4062 into x_document_num
4063 from po_headers
4064 where po_header_id = x_source_doc_id;
4065
4066 /* Get the release number as the next release in sequence */
4067
4068 select nvl(max(release_num),0)+1
4069 into x_release_num
4070 from po_releases_all por, -- <R12 MOAC>
4071 po_headers poh
4072 where poh.po_header_id = x_source_doc_id
4073 and poh.po_header_id = por.po_header_id;
4074
4075 /* Bug565530. gtummala. 10/23/97.
4076 * Even if the po_releases table gives us the next one in sequence,
4077 * this could conflict with a release_num that we have inserted into
4078 * the po_headers_interface table previously that has yet to converted
4079 * into a release eg. when we have two req lines that will be created
4080 * onto two diff. releases.
4081 */
4082
4083 -- Bug 722352, lpo, 08/26/98
4084 -- Commented out the release_num filters for the next 2 queries.
4085
4086 select count (*)
4087 into x_no_releases
4088 from po_headers_interface phi
4089 where phi.document_num = x_document_num;
4090 -- and phi.release_num = x_release_num;
4091
4092 if (x_no_releases <> 0) then
4093 select max(release_num)+1
4094 into x_release_num1
4095 from po_headers_interface phi
4096 where phi.document_num = x_document_num;
4097 -- and phi.release_num = x_release_num;
4098 end if;
4099
4100 -- End of fix. Bug 722352, lpo, 08/26/98
4101
4102
4103
4104 -- <Action Date TZ FPJ>
4105 /* Bug 638599, lpo, 03/26/98
4106 * Strip out time portion to be consistent with Enter Release form.
4107 * 10/22/2003: Action Date TZ FPJ Change
4108 * Since release_date on the Enter Release form is now
4109 * a datetime, the trunc is now removed.
4110 */
4111 /* Set release date to sysdate */
4112 x_release_date := SYSDATE;
4113
4114 -- <End Action Date TZ FPJ>
4115
4116
4117 end if;
4118
4119 /* dreddy : bug 1394312 */
4120 if (x_release_num1 >= x_release_num) then
4121 x_release_num := x_release_num1;
4122 end if;
4123
4124 /* Insert into po_headers_inteface */
4125
4126 x_progress := '50: insert_into_headers_interface: Just about to insert into ' ||
4127 'po_headers_interface';
4128 IF (g_po_wf_debug = 'Y') THEN
4129 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
4130 END IF;
4131
4132 x_progress := '11: the doc type to be created ' || x_doc_type_to_create ;
4133
4134 IF (g_po_wf_debug = 'Y') THEN
4135 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
4136 END IF;
4137
4138 begin
4139 insert into po_headers_interface
4140 (wf_group_id,
4141 interface_header_id,
4142 interface_source_code,
4143 batch_id,
4144 process_code,
4145 action,
4146 document_type_code,
4147 document_subtype,
4148 document_num,
4149 group_code,
4150 vendor_id,
4151 vendor_site_id,
4152 release_num,
4153 release_date,
4154 agent_id,
4155 currency_code,
4156 rate_type_code,
4157 rate_date,
4158 rate,
4159 creation_date,
4160 created_by,
4161 last_update_date,
4162 last_updated_by,
4163 pcard_id,
4164 style_id --<R12 STYLES PHASE II>
4165 )
4166 values
4167 (x_group_id,
4168 x_interface_header_id,
4169 'PO',
4170 x_batch_id,
4171 'NEW',
4172 'NEW',
4173 'PO', -- PO for both po's and releases
4174 x_doc_type_to_create,
4175 x_document_num,
4176 x_group_code, /* Bug 2974129 */
4177 x_suggested_vendor_id,
4178 x_suggested_vendor_site_id,
4179 x_release_num,
4180 x_release_date,
4181 x_suggested_buyer_id,
4182 x_currency_code_doc,
4183 l_interface_rate_type, --bug 3401653
4184 l_interface_rate_date, --bug 3401653
4185 l_interface_rate, --bug 3401653
4186 x_creation_date,
4187 x_created_by,
4188 x_last_update_date,
4189 x_last_updated_by,
4190 x_pcard_id,
4191 p_style_id --<R12 STYLES PHASE II>
4192 );
4193
4194 return TRUE; --bug 3401653
4195
4196 exception
4200 IF (g_po_wf_debug = 'Y') THEN
4197 when others then
4198 x_progress := '55: insert_into_headers_interface: IN EXCEPTION when '||
4199 'inserting into po_headers_interface';
4201 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
4202 END IF;
4203
4204 raise;
4205 end;
4206
4207 x_progress := '60: insert_into_headers_interface: Inserted into ' ||
4208 'po_headers_interface';
4209 IF (g_po_wf_debug = 'Y') THEN
4210 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
4211 END IF;
4212
4213 /* The interface_header_id is returned as an out parameter so that
4214 * subsequent lines can be tied to this same header if needed.
4215 */
4216
4217
4218 exception
4219 when others then
4220 wf_core.context('po_autoinsert_into_headers_interface','create_doc',x_progress);
4221 raise;
4222 end insert_into_headers_interface;
4223
4224
4225 /***************************************************************************
4226 *
4227 * Procedure: insert_into_lines_interface
4228 *
4229 * Description: Inserts a row in the po_lines_interface table
4230 *
4231 *
4232 **************************************************************************/
4233 procedure insert_into_lines_interface (itemtype IN VARCHAR2,
4234 itemkey IN VARCHAR2,
4235 x_interface_header_id IN NUMBER,
4236 x_req_line_id IN NUMBER,
4237 x_source_doc_line IN NUMBER,
4238 x_source_doc_type_code IN VARCHAR2,
4239 x_contract_id IN NUMBER,
4240 x_source_doc_id IN NUMBER, -- GA FPI
4241 x_cons_from_supp_flag IN VARCHAR2) is -- Consigned FPI
4242
4243 -- <GC FPJ> : removed variable x_contract_num
4244
4245 x_interface_line_id number;
4246 x_creation_date date := sysdate;
4247 x_last_update_date date := sysdate;
4248 x_created_by number;
4249 x_last_updated_by number;
4250 x_org_id number;
4251 x_doc_type_to_create varchar2(25);
4252 x_action_type_code_line varchar2(3);
4253 x_line_num number;
4254 x_progress varchar2(300);
4255 x_source_line_id number;
4256 x_ga_flag varchar2(1);
4257
4258 begin
4259
4260 /* Set the org context. Backend create_po process assumes it is in
4261 * an org.
4262 */
4263
4264 x_org_id := po_wf_util_pkg.GetItemAttrNumber
4265 (itemtype => itemtype,
4266 itemkey => itemkey,
4267 aname => 'ORG_ID');
4268
4269 po_moac_utils_pvt.set_org_context(x_org_id); --<R12 MOAC>
4270
4271 /* Get user values */
4272
4273 x_created_by := to_number(FND_PROFILE.VALUE('user_id'));
4274 x_last_updated_by := to_number(FND_PROFILE.VALUE('user_id'));
4275
4276 /* FPI GA - If ga flag is Y then we create a standard PO */
4277
4278 if x_source_doc_id is not null then
4279 select global_agreement_flag
4280 into x_ga_flag
4281 from po_headers_all
4282 where po_header_id = x_source_doc_id;
4283 end if;
4284
4285 /* Bug 2735730.
4286 * If x_source_doc_id is null, then it would be only in the case
4287 * when the supplier is set up as a consigned enabled and the
4288 * destination type is INVENTORY for the requisition. In this case,
4289 * we should still create a Standard PO. Hence x_doc_type_to_create
4290 * should be STANDARD in this case.
4291 */
4292 if (x_source_doc_id is null) then
4293 x_doc_type_to_create := 'STANDARD';
4294 else
4295 if (x_source_doc_type_code = 'BLANKET')
4296 and nvl(x_ga_flag,'N') = 'N' then -- FPI GA
4297 x_doc_type_to_create := 'RELEASE';
4298 else
4299 x_doc_type_to_create := 'STANDARD';
4300 end if;
4301 end if;
4302
4303 if (x_doc_type_to_create = 'STANDARD') then
4304 x_action_type_code_line := NULL;
4305 x_line_num := NULL;
4306
4307 -- <GC FPJ START>
4308 -- We can now insert contract_id into po_lines_interface directly
4309 -- and therefore no need to derive contract_num
4310
4311 --if (x_contract_id is not null) then
4312 --
4313 -- select max(segment1)
4314 -- into x_contract_num
4315 -- from po_headers
4316 -- where po_header_id = x_contract_id;
4317 --
4318 --end if;
4319
4320 -- <GC FPJ END>
4321
4322 else
4323 /* RELEASE */
4324 x_action_type_code_line := 'ADD';
4325 x_line_num := x_source_doc_line;
4326 end if;
4327
4328
4329 select po_lines_interface_s.nextval
4330 into x_interface_line_id
4331 from sys.dual;
4332
4333 /* GA FPI start */
4334
4335 if x_source_doc_id is not null and
4336 x_source_doc_line is not null then
4337
4338 -- SQL what Get the line id from the source doc line
4339 -- SQL why Requisition line does not store the line id
4340 -- Bug fix 2703592 - need to select from all table instead of po_lines
4341 Select po_line_id
4345 And line_num = x_source_doc_line;
4342 into x_source_line_id
4343 From po_lines_all
4344 Where po_header_id = x_source_doc_id
4346
4347 end if;
4348
4349 /* GA FPI end */
4350
4351 /* Insert into po_lines */
4352
4353 x_progress := '10: insert_into_lines_interface: Just about to insert into ' ||
4354 'po_lines_interface';
4355
4356 IF (g_po_wf_debug = 'Y') THEN
4357 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
4358 END IF;
4359
4360 x_progress := '11: the doc type to be created ' || x_doc_type_to_create ;
4361
4362 IF (g_po_wf_debug = 'Y') THEN
4363 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
4364 END IF;
4365
4366 begin
4367 insert into po_lines_interface
4368 (interface_header_id,
4369 interface_line_id,
4370 action,
4371 line_num,
4372 shipment_num,
4373 requisition_line_id,
4374 contract_id, -- <GC FPJ>
4375 from_header_id, -- GA FPI
4376 from_line_id, -- GA FPI
4377 consigned_flag, -- Bug 2798503
4378 creation_date,
4379 created_by,
4380 last_update_date,
4381 last_updated_by)
4382 values
4383 (x_interface_header_id,
4384 x_interface_line_id,
4385 x_action_type_code_line,
4386 x_line_num,
4387 null,
4388 x_req_line_id,
4389 x_contract_id, -- <GC FPJ>
4390 decode(x_cons_from_supp_flag,'Y',null,x_source_doc_id) , -- Consigned FPI
4391 decode(x_cons_from_supp_flag,'Y',null,x_source_line_id), -- Consigned FPI
4392 x_cons_from_supp_flag, -- Bug 2798503
4393 x_creation_date,
4394 x_created_by,
4395 x_last_update_date,
4396 x_last_updated_by);
4397
4398 exception
4399 when others then
4400 x_progress:= '15: insert_into_lines_interface: IN EXCEPTION when' ||
4401 'inserting into po_lines_interface';
4402 IF (g_po_wf_debug = 'Y') THEN
4403 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
4404 END IF;
4405 raise;
4406 end;
4407
4408 x_progress := '20: insert_into_lines_interface: Inserted into ' ||
4409 'po_lines_interface';
4410 IF (g_po_wf_debug = 'Y') THEN
4411 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
4412 END IF;
4413
4414
4415 exception
4416 when others then
4417 wf_core.context('po_autoinsert_into_lines_interface','insert_into_lines_interface',
4418 x_progress);
4419 raise;
4420 end insert_into_lines_interface;
4421
4422
4423 /***************************************************************************
4424 *
4425 * Procedure: launch_doc_creation_approval
4426 *
4427 * Description: Launches the child doc creation and approval process
4428 * per document.
4429 *
4430 **************************************************************************/
4431 procedure launch_doc_creation_approval (itemtype IN VARCHAR2,
4432 itemkey IN VARCHAR2,
4433 actid IN NUMBER,
4434 funcmode IN VARCHAR2,
4435 resultout OUT NOCOPY VARCHAR2 ) is
4436
4437 x_ItemType varchar2(20) := itemtype; /* Calling proc has same
4438 * item type as called proc
4439 */
4440 x_ItemKey varchar2(60) := null;
4441 x_workflow_process varchar2(40) := 'CREATE_AND_APPROVE_DOC';
4442 x_group_id number;
4443 x_interface_header_id number;
4444 x_doc_type_to_create varchar2(25);
4445 x_seq_for_item_key varchar2(6) := null;
4446 x_agent_id number;
4447 x_org_id number;
4448 l_purchasing_org_id number; --<Shared Proc FPJ>
4449 x_progress varchar2(300);
4450 l_vendor_site_id PO_HEADERS_ALL.vendor_site_id%TYPE := NULL; --<BUG 3538308>
4451
4452 cursor c1 is /* x_group_id is a parameter */
4453 select interface_header_id,
4454 document_subtype,
4455 agent_id,
4456 vendor_site_id --<BUG 3538308>
4457 from po_headers_interface
4458 where wf_group_id = x_group_id
4459 order by interface_header_id;
4460
4461
4462 begin
4463
4464 /* Set the org context. Backend create_po process assumes it is in
4465 * an org.
4466 */
4467
4468 x_org_id := po_wf_util_pkg.GetItemAttrNumber
4469 (itemtype => itemtype,
4470 itemkey => itemkey,
4471 aname => 'ORG_ID');
4472
4473 po_moac_utils_pvt.set_org_context(x_org_id); --<R12 MOAC>
4474
4475
4476 /* Get the group id so we can launch doc creation and approval
4477 * for this group.
4478 */
4479
4480 x_group_id := po_wf_util_pkg.GetItemAttrNumber (itemtype => itemtype,
4481 itemkey => itemkey,
4485
4482 aname => 'GROUP_ID');
4483
4484 open c1; /* x_group_id is a parameter */
4486 loop
4487 fetch c1 into x_interface_header_id,
4488 x_doc_type_to_create,
4489 x_agent_id,
4490 l_vendor_site_id; --<BUG 3538308>
4491 exit when c1%NOTFOUND;
4492
4493 --<BUG 3538308 START>
4494 --For every iteration of the for-loop, need to set purchasing_org_id for
4495 --the PO document to be created. The purchasing_org_id retrieved here
4496 --is passed into po_autocreate_doc.start_wf_create_apprv_process.
4497
4498 x_progress:= '5: launch_doc_creation_approval: before set_purchasing_org_id: ' ||
4499 ' x_org_id (from workflow)= '|| to_char(x_org_id) ||
4500 ' l_vendor_site_id (from interface table)='||to_char(l_vendor_site_id);
4501
4502 set_purchasing_org_id(itemtype => itemtype,
4503 itemkey => itemkey,
4504 p_org_id => x_org_id,
4505 p_suggested_vendor_site_id => l_vendor_site_id);
4506
4507 l_purchasing_org_id := PO_WF_UTIL_PKG.GetItemAttrNumber (itemtype => itemtype,
4508 itemkey => itemkey,
4509 aname => 'PURCHASING_ORG_ID');
4510
4511 x_progress:= '6: launch_doc_creation_approval: after set_purchasing_org_id: ' ||
4512 ' l_purchasing_org_id (from workflow)= '|| to_char(l_purchasing_org_id) ||
4513 ' x_org_id (from workflow)= '|| to_char(x_org_id) ||
4514 ' l_vendor_site_id (from interface table)='||to_char(l_vendor_site_id);
4515
4516 IF (g_po_wf_debug = 'Y') THEN
4517 PO_WF_DEBUG_PKG.insert_debug(itemtype => itemtype,
4518 itemkey => itemkey,
4519 x_progress => x_progress);
4520 END IF;
4521
4522 --<BUG 3538308 END>
4523
4524 /* Get the unique sequence to make sure item key will be unique */
4525
4526 select to_char(PO_WF_ITEMKEY_S.NEXTVAL)
4527 into x_seq_for_item_key
4528 from sys.dual;
4529
4530 /* The item key is the interface_header_id concatenated with the
4531 * unique id from a seq.
4532 */
4533
4534 x_ItemKey := to_char(x_interface_header_id) || '-' || x_seq_for_item_key;
4535
4536 /* Launch the req line processing process
4537 *
4538 * Need to pass in the parent's itemtype and itemkey so as to
4539 * all the parent child relationship to be setup in the called
4540 * process.
4541 */
4542
4543 x_progress:= '10: launch_doc_creation_approval: '||
4544 ' Called start_wf_create_apprv_process with itemkey = '||
4545 x_Itemkey;
4546 IF (g_po_wf_debug = 'Y') THEN
4547 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
4548 END IF;
4549
4550 po_autocreate_doc.start_wf_create_apprv_process (x_ItemType,
4551 x_ItemKey,
4552 x_workflow_process,
4553 x_interface_header_id,
4554 x_doc_type_to_create,
4555 x_agent_id,
4556 x_org_id,
4557 l_purchasing_org_id, --<Shared Proc FPJ>
4558 itemtype,
4559 itemkey);
4560
4561
4562
4563 end loop;
4564 close c1;
4565
4566 resultout := wf_engine.eng_completed || ':' || 'ACTIVITY_PERFORMED';
4567
4568 x_progress := '20: launch_doc_creation_approval: result = ACTIVITY_PERFORMED';
4569 IF (g_po_wf_debug = 'Y') THEN
4570 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
4571 END IF;
4572
4573 exception
4574 when others then
4575 wf_core.context('po_autocreate_doc.launch_doc_creation_approval','launch_doc_creation_approval',
4576 x_progress);
4577 raise;
4578 end launch_doc_creation_approval;
4579
4580
4581 /***************************************************************************
4582 *
4583 * Procedure: start_wf_create_apprv_process
4584 *
4585 * Description: Creates and starts the 'CREATE_AND_APPROVE_DOC'
4586 * workflow proccess.
4587 *
4588 **************************************************************************/
4589 procedure start_wf_create_apprv_process (ItemType VARCHAR2,
4590 ItemKey VARCHAR2,
4591 workflow_process VARCHAR2,
4592 interface_header_id NUMBER,
4593 doc_type_to_create VARCHAR2,
4594 agent_id NUMBER,
4595 org_id NUMBER,
4596 purchasing_org_id NUMBER, --<Shared Proc FPJ>
4597 parent_itemtype VARCHAR2,
4598 parent_itemkey VARCHAR2) is
4599
4600 x_progress varchar2(300);
4601
4602 begin
4603
4604 x_progress := '10: start_wf_create_apprv_process: This was called with' ||
4605 'ItemType = ' || ItemType || '/ ' || 'ItemKey = ' || ItemKey;
4606
4607 IF (g_po_wf_debug = 'Y') THEN
4611
4608 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
4609 END IF;
4610
4612 /* If a process is passed then it will be run
4613 * If a process is not passed then the selector function defined in
4614 * item type will be determine which process to run
4615 */
4616
4617 IF (ItemType is NOT NULL ) AND
4618 (ItemKey is NOT NULL) AND
4619 (interface_header_id is NOT NULL ) then
4620 wf_engine.CreateProcess(itemtype => itemtype,
4621 itemkey => itemkey,
4622 process => workflow_process );
4623
4624 x_progress:= '20. start_wf_create_apprv_process: Just after create process';
4625 IF (g_po_wf_debug = 'Y') THEN
4626 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
4627 END IF;
4628
4629 /* Set the item attributes */
4630
4631 po_wf_util_pkg.SetItemAttrNumber (itemtype => itemtype,
4632 itemkey => itemkey,
4633 aname => 'ORG_ID',
4634 avalue => org_id);
4635
4636 --<Shared Proc FPJ START>
4637 po_wf_util_pkg.SetItemAttrNumber (itemtype => itemtype,
4638 itemkey => itemkey,
4639 aname => 'PURCHASING_ORG_ID',
4640 avalue => purchasing_org_id);
4641 --<Shared Proc FPJ END>
4642
4643 po_wf_util_pkg.SetItemAttrNumber (itemtype => itemtype,
4644 itemkey => itemkey,
4645 aname => 'INTERFACE_HEADER_ID',
4646 avalue => interface_header_id);
4647
4648
4649 po_wf_util_pkg.SetItemAttrText (itemtype => itemtype,
4650 itemkey => itemkey,
4651 aname => 'DOC_TYPE_TO_CREATE',
4652 avalue => doc_type_to_create);
4653
4654
4655 po_wf_util_pkg.SetItemAttrNumber (itemtype => itemtype,
4656 itemkey => itemkey,
4657 aname => 'AGENT_ID',
4658 avalue => agent_id);
4659
4660
4661 /* Need to set the parent child relationship between processes */
4662
4663 wf_engine.SetItemParent (itemtype => itemtype,
4664 itemkey => itemkey,
4665 parent_itemtype => parent_itemtype,
4666 parent_itemkey => parent_itemkey,
4667 parent_context => NULL);
4668
4669
4670 /* Kick off the process */
4671
4672 x_progress:= '30. start_wf_create_apprv_process: Kicking off StartProcess ' ||
4673 'with item_type = ' || itemtype || '/ ' || 'item_key = ' ||
4674 itemkey;
4675 IF (g_po_wf_debug = 'Y') THEN
4676 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
4677 END IF;
4678
4679 wf_engine.StartProcess(itemtype => itemtype,
4680 itemkey => itemkey );
4681
4682 END IF;
4683
4684 exception
4685 when others then
4686 x_progress:= '40. start_wf_create_apprv_process: IN EXCEPTION';
4687 IF (g_po_wf_debug = 'Y') THEN
4688 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
4689 END IF;
4690 raise;
4691 end start_wf_create_apprv_process;
4692
4693
4694 /***************************************************************************
4695 *
4696 * Procedure: create_doc
4697 *
4698 * Description: Calls backend autocreate package to create the
4699 * standard po or blanket release
4700 *
4701 *
4702 **************************************************************************/
4703 procedure create_doc (itemtype IN VARCHAR2,
4704 itemkey IN VARCHAR2,
4705 actid IN NUMBER,
4706 funcmode IN VARCHAR2,
4707 resultout OUT NOCOPY VARCHAR2 ) is
4708
4709 x_interface_header_id number;
4710 x_num_lines_processed number;
4711 x_autocreated_doc_id number;
4712 x_org_id number;
4713 x_progress varchar2(300);
4714
4715 --<Shared Proc FPJ START>
4716 l_purchasing_org_id PO_HEADERS_ALL.org_id%TYPE;
4717 l_return_status VARCHAR2(1);
4718 l_msg_count NUMBER;
4719 l_msg_data FND_NEW_MESSAGES.message_text%TYPE;
4720 l_doc_number PO_HEADERS_ALL.segment1%TYPE;
4721 --<Shared Proc FPJ END>
4722
4723 begin
4724
4725 /* Set the org context. Backend create_po process assumes it is in
4726 * an org.
4727 */
4728
4729 x_org_id := po_wf_util_pkg.GetItemAttrNumber
4730 (itemtype => itemtype,
4731 itemkey => itemkey,
4732 aname => 'ORG_ID');
4733 --<Shared Proc FPJ START>
4734 l_purchasing_org_id := po_wf_util_pkg.GetItemAttrNumber
4735 (itemtype => itemtype,
4736 itemkey => itemkey,
4737 aname => 'PURCHASING_ORG_ID');
4738 --<Shared Proc FPJ END>
4739
4743 itemkey => itemkey,
4740 PO_MOAC_UTILS_PVT.set_org_context(x_org_id); --<R12 MOAC>
4741 x_interface_header_id := po_wf_util_pkg.GetItemAttrNumber
4742 (itemtype => itemtype,
4744 aname => 'INTERFACE_HEADER_ID');
4745
4746
4747
4748
4749 /* Call the main sever side routine to actually create
4750 * the documents, ie:
4751 * - default in values not populated
4752 * - group accordingly
4753 * - insert into the main tables from the
4754 * the interface tables.
4755 *
4756 * x_document_id is populated with po_header_id for pos
4757 * and po_release_id for releases
4758 */
4759
4760
4761 x_progress:= '10: create_doc: Kicking off backend with' ||
4762 'interface_header_id = '|| to_char(x_interface_header_id);
4763 IF (g_po_wf_debug = 'Y') THEN
4764 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
4765 END IF;
4766
4767 --<Shared Proc FPJ>
4768 --Call Autocreate Backend to create the document
4769 --in the purchasing org specified.
4770 po_interface_s.create_documents(p_api_version => 1.0,
4771 x_return_status => l_return_status,
4772 x_msg_count => l_msg_count,
4773 x_msg_data => l_msg_data,
4774 p_batch_id => x_interface_header_id,
4775 p_req_operating_unit_id => x_org_id,
4776 p_purch_operating_unit_id => l_purchasing_org_id,
4777 x_document_id => x_autocreated_doc_id,
4778 x_number_lines => x_num_lines_processed,
4779 x_document_number => l_doc_number,
4780 -- Bug 3648268. Using lookup code instead of hardcoded value
4781 p_document_creation_method => 'CREATEDOC',
4782 p_orig_org_id => x_org_id -- <R12 MOAC>
4783 );
4784
4785
4786 x_progress := '20: create_doc: Came back from the backend with ' ||
4787 'doc_id = ' || to_char(x_autocreated_doc_id) || '/ ' ||
4788 'num_lines_processed = ' || to_char(x_num_lines_processed);
4789
4790 IF (g_po_wf_debug = 'Y') THEN
4791 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
4792 END IF;
4793
4794
4795 /* If at least one req line got processed then we have succeeded in
4796 * creating the po or release
4797 */
4798
4799 if (x_num_lines_processed > 0) then
4800 po_wf_util_pkg.SetItemAttrNumber (itemtype => itemtype,
4801 itemkey => itemkey,
4802 aname => 'AUTOCREATED_DOC_ID',
4803 avalue => x_autocreated_doc_id);
4804
4805 /* Call procedure to setup notification data which will be used
4806 * in sending a notification to the buyer that the doc has been
4807 * created successfully.
4808 */
4809
4810 po_autocreate_doc.setup_notification_data (itemtype, itemkey);
4811
4812 resultout := wf_engine.eng_completed || ':' || 'CREATE_OK';
4813
4814 x_progress:= '30: create_doc: result = CREATE_OK';
4815 IF (g_po_wf_debug = 'Y') THEN
4816 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
4817 END IF;
4818
4819 else
4820 resultout := wf_engine.eng_completed || ':' || 'CREATE_FAILED';
4821
4822 x_progress:= '40: create_doc: result = CREATE_FAILED';
4823 IF (g_po_wf_debug = 'Y') THEN
4824 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
4825 END IF;
4826
4827 end if;
4828
4829 exception
4830 when others then
4831 wf_core.context('po_autocreate_doc','create_doc',x_progress);
4832 raise;
4833 end create_doc;
4834
4835
4836 /***************************************************************************
4837 *
4838 * Procedure: setup_notification_data
4839 *
4840 * Description: Setup all the data (item attributes etc) needed
4841 * for the notification.
4842 *
4843 **************************************************************************/
4844 procedure setup_notification_data (itemtype IN VARCHAR2,
4845 itemkey IN VARCHAR2) is
4846
4847
4848
4849 x_doc_type_to_create varchar2(25);
4850 x_doc_type_created_disp varchar2(80);
4851 x_segment1 varchar2(20);
4852 x_release_num number;
4853 x_agent_id number;
4854 x_username varchar2(100);
4855 x_user_display_name varchar2(240);
4856 x_autocreated_doc_id number;
4857 l_open_form varchar2(200); --Bug#2982867
4858 l_view_po_url varchar2(1000); -- HTML Orders R12
4859 l_edit_po_url varchar2(1000); -- HTML Orders R12
4860 l_style_id po_headers_all.style_id%TYPE; -- HTML Orders R12
4861
4862 x_progress varchar2(300);
4863
4864 begin
4865
4866 x_doc_type_to_create := po_wf_util_pkg.GetItemAttrText
4870
4867 (itemtype => itemtype,
4868 itemkey => itemkey,
4869 aname => 'DOC_TYPE_TO_CREATE');
4871 x_autocreated_doc_id := po_wf_util_pkg.GetItemAttrNumber
4872 (itemtype => itemtype,
4873 itemkey => itemkey,
4874 aname => 'AUTOCREATED_DOC_ID');
4875
4876
4877 x_agent_id := po_wf_util_pkg.GetItemAttrNumber
4878 (itemtype => itemtype,
4879 itemkey => itemkey,
4880 aname => 'AGENT_ID');
4881
4882
4883 /* Get the displayed value from po_lookup_codes so it will be translated
4884 * This will return either 'Standard PO' or 'Release'
4885 */
4886 --<R12 STYLES PHASE II START>
4887 if (x_doc_type_to_create = 'STANDARD') then
4888 x_doc_type_created_disp:= PO_DOC_STYLE_PVT.get_style_display_name(x_autocreated_doc_id);
4889 else --releases
4890
4891 select displayed_field
4892 into x_doc_type_created_disp
4893 from po_lookup_codes
4894 where lookup_type = 'NOTIFICATION TYPE'
4895 and lookup_code = x_doc_type_to_create;
4896 end if;
4897 --<R12 STYLES PHASE II END>
4898
4899
4900 po_wf_util_pkg.SetItemAttrText (itemtype => itemtype,
4901 itemkey => itemkey,
4902 aname => 'DOC_TYPE_CREATED_DISP',
4903 avalue => x_doc_type_created_disp);
4904
4905 /* Get the document number created */
4906
4907 if (x_doc_type_to_create = 'STANDARD') then
4908
4909 --<Shared Proc FPJ>
4910 --Modified the query to select from po_headers_all instead of
4911 --po_headers.
4912 --< HTML Orders R12>
4913 -- selected style id
4914 select segment1,
4915 style_id
4916 into x_segment1,
4917 l_style_id
4918 from po_headers_all
4919 where po_header_id = x_autocreated_doc_id;
4920
4921
4922 po_wf_util_pkg.SetItemAttrText (itemtype => itemtype,
4923 itemkey => itemkey,
4924 aname => 'DOC_NUM_CREATED',
4925 avalue => x_segment1);
4926
4927 /* Bug 2982867, Assigning the proper Command and setting the item
4928 attribute to open PO or Release Form depending on what
4929 document was created. */
4930 -- <HTML Orders R12 Start >
4931 -- Set the URL and form link attributes based on doc style and type
4932 l_view_po_url := PO_REQAPPROVAL_INIT1.get_po_url(
4933 p_po_header_id => x_autocreated_doc_id,
4934 p_doc_subtype => x_doc_type_to_create,
4935 p_mode => 'viewOnly');
4936
4937 l_edit_po_url := PO_REQAPPROVAL_INIT1.get_po_url(
4938 p_po_header_id => x_autocreated_doc_id,
4939 p_doc_subtype => x_doc_type_to_create,
4940 p_mode => 'update');
4941
4942 IF PO_DOC_STYLE_GRP.is_standard_doc_style(l_style_id) = 'Y' THEN
4943 l_open_form := 'PO_POXPOEPO:PO_HEADER_ID="' || '&' ||
4944 'AUTOCREATED_DOC_ID"' ||
4945 ' ACCESS_LEVEL_CODE="MODIFY"' ||
4946 ' POXPOEPO_CALLING_FORM="POXSTNOT"';
4947 ELSE
4948 l_open_form := null;
4949 END IF;
4950 -- <HTML Orders R12 End >
4951 else
4952 /* RELEASE */
4953
4954 select poh.segment1,
4955 por.release_num
4956 into x_segment1,
4957 x_release_num
4958 from po_headers_all poh, -- <R12 MOAC>
4959 po_releases por
4960 where por.po_release_id = x_autocreated_doc_id
4961 and por.po_header_id = poh.po_header_id;
4962
4963 /* Append the release num to blanket po num */
4964
4965 po_wf_util_pkg.SetItemAttrText (itemtype => itemtype,
4966 itemkey => itemkey,
4967 aname => 'DOC_NUM_CREATED',
4968 avalue => x_segment1 || ', ' ||
4969 to_char (x_release_num));
4970
4971 -- HTML Orders R12: HTML URLs not applicable for releases
4972 l_view_po_url := '';
4973 l_edit_po_url := '';
4974 -- Bug 2982867
4975 l_open_form := 'PO_POXPOERL:PO_RELEASE_ID="' || '&' ||
4976 'AUTOCREATED_DOC_ID"' ||
4977 ' ACCESS_LEVEL_CODE="MODIFY"' ||
4978 ' POXPOERL_CALLING_FORM="POXSTNOT"';
4979 end if;
4980
4981 PO_WF_UTIL_PKG.SetItemAttrText (itemtype => itemtype,
4982 itemkey => itemkey,
4983 aname => 'OPEN_FORM_COMMAND',
4984 avalue =>l_open_form );
4985
4986 -- HTML Orders R12
4987 -- Set the URL and form attributes
4988 PO_WF_UTIL_PKG.SetItemAttrText ( itemtype => itemtype,
4989 itemkey => itemkey,
4990 aname => 'VIEW_DOC_URL' ,
4994 itemkey => itemkey,
4991 avalue => l_view_po_url);
4992
4993 PO_WF_UTIL_PKG.SetItemAttrText ( itemtype => itemtype,
4995 aname => 'EDIT_DOC_URL' ,
4996 avalue => l_edit_po_url);
4997
4998 /* Need to get the username (webuser) of the person we want to send
4999 * to send the notification to.
5000 *
5001 * Call po_req_approval_init1.get_user_name which then calls
5002 * wf.GetUserName to get the info.
5003 *
5004 * The agent_id in the po_agents table has the same value as the employee_id
5005 * in the HR_EMPLOYEES view for the corresponding employee so we can pass in
5006 * agent_id
5007 */
5008
5009 x_progress := '10: setup_notification_data: Got the doc num created.' ||
5010 'Just before call to get_user_name';
5011 IF (g_po_wf_debug = 'Y') THEN
5012 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
5013 END IF;
5014
5015 po_reqapproval_init1.get_user_name(x_agent_id, x_username, x_user_display_name);
5016
5017 po_wf_util_pkg.SetItemAttrText (itemtype => itemtype,
5018 itemkey => itemkey,
5019 aname => 'BUYER_USERNAME',
5020 avalue => x_username);
5021
5022
5023 x_progress := '20: setup_notification_data: Username = ' || x_username ||
5024 'End of setup_notification_data';
5025 IF (g_po_wf_debug = 'Y') THEN
5026 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
5027 END IF;
5028
5029 exception
5030 when others then
5031 wf_core.context('po_autosetup_notification_data','setup_notification_data',x_progress);
5032 raise;
5033 end setup_notification_data;
5034
5035
5036 /***************************************************************************
5037 *
5038 * Procedure: should_doc_be_approved
5039 *
5040 * Description: Decides whether document approval process should
5041 * be kicked off or not.
5042 *
5043 **************************************************************************/
5044 procedure should_doc_be_approved (itemtype IN VARCHAR2,
5045 itemkey IN VARCHAR2,
5046 actid IN NUMBER,
5047 funcmode IN VARCHAR2,
5048 resultout OUT NOCOPY VARCHAR2 ) is
5049
5050 x_auto_approve_doc varchar2(1);
5051 x_progress varchar2(300);
5052
5053 --<R12 eTax Integration Start>
5054 l_doc_type_to_create po_headers_all.type_lookup_code%TYPE;
5055 l_doc_type po_document_types_all_b.document_type_code%TYPE;
5056 l_doc_subtype po_document_types_all_b.document_subtype%TYPE;
5057 l_po_header_id po_headers_all.po_header_id%TYPE;
5058 l_po_release_id po_releases_all.po_release_id%TYPE;
5059 l_return_status VARCHAR2(1);
5060 --<R12 eTax Integration End>
5061
5062 begin
5063
5064 /* This decision is made by simply looking at an item atrribute,
5065 * which has a default value. All the user needs to do is change
5066 * that attribute according to their needs.
5067 */
5068 --<R12 eTax Integration Start>
5069 --
5070 -- Calculate tax before launching approval process
5071 -- If tax calculation is success and the wf attribute AUTO_APPROVE_DOC
5072 -- is set to Y then this function would return Y else it would
5073 -- return N. So Approval depends on tax calculation being successful
5074 --
5075 --<R12 eTax Integration End>
5076 x_auto_approve_doc := po_wf_util_pkg.GetItemAttrText (itemtype => itemtype,
5077 itemkey => itemkey,
5078 aname => 'AUTO_APPROVE_DOC');
5079
5080
5081 --<R12 eTax Integration Start>
5082 l_po_release_id := null;
5083 l_po_header_id := null;
5084 l_doc_type_to_create := null;
5085 l_doc_type := null;
5086 l_doc_subtype := null;
5087 l_return_status := null;
5088 l_doc_type_to_create := po_wf_util_pkg.GetItemAttrText (itemtype => itemtype,
5089 itemkey => itemkey,
5090 aname => 'DOC_TYPE_TO_CREATE');
5091
5092 IF (l_doc_type_to_create = PO_CONSTANTS_SV.RELEASE) THEN
5093 l_doc_type := PO_CONSTANTS_SV.RELEASE;
5094 l_doc_subtype := PO_CONSTANTS_SV.BLANKET;
5095 l_po_release_id := po_wf_util_pkg.GetItemAttrNumber
5096 (itemtype => itemtype,
5097 itemkey => itemkey,
5098 aname => 'AUTOCREATED_DOC_ID');
5099 ELSE
5100 l_doc_type := PO_CONSTANTS_SV.PO;
5101 l_doc_subtype := PO_CONSTANTS_SV.STANDARD;
5102 l_po_header_id := po_wf_util_pkg.GetItemAttrNumber
5103 (itemtype => itemtype,
5104 itemkey => itemkey,
5105 aname => 'AUTOCREATED_DOC_ID');
5106 END IF;
5107
5108 po_tax_interface_pvt.calculate_tax(p_po_header_id => l_po_header_id,
5109 p_po_release_id => l_po_release_id,
5110 x_return_status => l_return_status,
5114 IF (g_po_wf_debug = 'Y') THEN
5111 p_calling_program => 'AUTOCREATED_DOC_WF');
5112
5113 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5115 po_wf_debug_pkg.insert_debug(itemtype,itemkey,'Tax Errored ');
5116 END IF;
5117 END IF;
5118
5119 --<R12 eTax Integration End>
5120
5121 if (x_auto_approve_doc = 'Y' AND l_return_status = FND_API.G_RET_STS_SUCCESS) then --<R12 eTax Integration>
5122 resultout := wf_engine.eng_completed || ':' || 'Y';
5123
5124 x_progress:= '10: should_doc_be_approved: result = Y';
5125 IF (g_po_wf_debug = 'Y') THEN
5126 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
5127 END IF;
5128
5129 else
5130 resultout := wf_engine.eng_completed || ':' || 'N';
5131
5132 x_progress:= '20: should_doc_be_approved: result = N';
5133 IF (g_po_wf_debug = 'Y') THEN
5134 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
5135 END IF;
5136
5137 end if;
5138
5139 exception
5140 when others then
5141 wf_core.context('po_autocreate_doc','should_doc_be_approved',x_progress);
5142 raise;
5143 end should_doc_be_approved;
5144
5145
5146
5147 /***************************************************************************
5148 *
5149 * Procedure: launch_po_approval
5150 *
5151 * Description: Kicks off the po approval workflow to approve the
5152 * po or the release
5153 *
5154 **************************************************************************/
5155 procedure launch_po_approval (itemtype IN VARCHAR2,
5156 itemkey IN VARCHAR2,
5157 actid IN NUMBER,
5158 funcmode IN VARCHAR2,
5159 resultout OUT NOCOPY VARCHAR2 ) is
5160
5161 x_ItemType varchar2(20) := null;
5162 x_ItemKey varchar2(60) := null;
5163 x_workflow_process varchar2(40) := null;
5164 x_action_orig_from varchar2(30) := null;
5165 x_doc_id number := null;
5166 x_doc_num po_headers.segment1%type := null; -- Bug 3152167
5167 x_preparer_id number := null;
5168 x_doc_type varchar2(25) := null;
5169 x_doc_subtype varchar2(25) := null;
5170 x_submitter_action varchar2(25) := null;
5171 x_forward_to_id number := null;
5172 x_forward_from_id number := null;
5173 x_def_approval_path_id number := null;
5174 x_note varchar2(240):= null;
5175 x_seq_for_item_key varchar2(6) := null;
5176 x_doc_type_to_create varchar2(25);
5177 x_org_id number;
5178 x_progress varchar2(300);
5179 x_printflag varchar2(1) := 'N';
5180 x_faxflag varchar2(1) := 'N';
5181 x_faxnum varchar2(30) := null;
5182 x_fax_area varchar2(10) := null; -- bug 2567900
5183 x_emailflag varchar2(1) := 'N';
5184 x_emailaddress varchar2(2000) := null;
5185 x_default_method varchar2(25) := null;
5186
5187
5188 /* RETROACTIVE FPI START */
5189 l_document_num po_headers.segment1%type;
5190 /* RETROACTIVE FPI END */
5191
5192 /* <SUP_CON FPI START> */
5193 l_consigned_consumption_flag po_headers_all.consigned_consumption_flag%TYPE;
5194 /* <SUP_CON FPI END> */
5195
5196 l_purchasing_org_id po_headers_all.org_id%TYPE; --<Shared Proc FPJ>
5197
5198 /* BUG 4638656 */
5199 l_tp_header_id ece_tp_details.tp_header_id%TYPE;
5200 l_edi_flag ece_tp_details.edi_flag%TYPE;
5201 l_transaction_subtype ece_tp_details.document_id%TYPE;
5202 /* BUG 4638656 */
5203
5204
5205
5206 begin
5207
5208 x_org_id := po_wf_util_pkg.GetItemAttrNumber
5209 (itemtype => itemtype,
5210 itemkey => itemkey,
5211 aname => 'ORG_ID');
5212
5213 --<Shared Proc FPJ START>
5214 l_purchasing_org_id := po_wf_util_pkg.GetItemAttrNumber
5215 (itemtype => itemtype,
5216 itemkey => itemkey,
5217 aname => 'PURCHASING_ORG_ID');
5218 --<Shared Proc FPJ END>
5219
5220
5221 /* Bug: 1479382 set the org contect */
5222
5223 IF x_org_id IS NOT NULL THEN
5224
5225 po_moac_utils_pvt.set_org_context(x_org_id); --<R12 MOAC>
5226
5227 --<Shared Proc FPJ START>
5228 IF x_org_id <> l_purchasing_org_id THEN
5229 --Set the org_id to be the purchasing org for PO Approval Process
5230 po_wf_util_pkg.SetItemAttrNumber(itemtype => itemtype,
5231 itemkey => itemkey,
5232 aname => 'ORG_ID',
5233 avalue => l_purchasing_org_id);
5234 --Set the context to be that of purchasing org
5235 po_moac_utils_pvt.set_org_context(l_purchasing_org_id); --<R12 MOAC>
5236
5237 END IF;
5238 --<Shared Proc FPJ END>
5239 END IF;
5240
5241 x_doc_type_to_create := po_wf_util_pkg.GetItemAttrText
5242 (itemtype => itemtype,
5243 itemkey => itemkey,
5247 x_doc_type := 'RELEASE';
5244 aname => 'DOC_TYPE_TO_CREATE');
5245
5246 if (x_doc_type_to_create = 'RELEASE') then
5248 x_doc_subtype := 'BLANKET';
5249 else
5250 /* STANDARD */
5251 x_doc_type := 'PO';
5252 x_doc_subtype := 'STANDARD';
5253 end if;
5254
5255 /* Need to get item_type and workflow process from po_document_types.
5256 * They may be different based on the doc/org.
5257 */
5258
5259
5260 /* Get the doc_id, doc_num and preparer_id */
5261
5262 /* The preparer is the same as the buyer */
5263
5264 x_preparer_id:= po_wf_util_pkg.GetItemAttrNumber
5265 (itemtype => itemtype,
5266 itemkey => itemkey,
5267 aname => 'SUGGESTED_BUYER_ID');
5268
5269 x_doc_id:= po_wf_util_pkg.GetItemAttrNumber
5270 (itemtype => itemtype,
5271 itemkey => itemkey,
5272 aname => 'AUTOCREATED_DOC_ID');
5273
5274
5275 /* Bug 712718 ecso 08/17/98
5276 ** PO Approval workflow requires a non-null preparer_id
5277 ** Since attributes from another child workflow is not
5278 ** passed to this child workflow, hit the table to get
5279 ** the agent id.
5280 */
5281
5282 /* RETROACTIVE FPI START.
5283 * Deleted the code which gets teh preparer_id if it is null since
5284 * x_preparer_id will be derived in get_transmission_defaults if
5285 * this is null. Also deleted the code which gets the default supplier
5286 * communication method from po_vendor_site and now call
5287 * PO_VENDOR_SITES_SV.Get_Transmission_Defaults which does this now.
5288 * Also deleted the code which defaults item_type, item_key from
5289 * po_document_types as this is done now in start_wf_process.
5290 */
5291
5292 /* Bug 1845764 :
5293 Check if the supplier site on the PO/Release has email as the default notification method.
5294 if so set the email flag and address from the site */
5295
5296 -- included bug fix 2342323. Modified changes are to retrieve segment1
5297 -- from po_headers into x_doc_num
5298 -- for release, PO or PA
5299 -- Bug 2567900 Included fax number in the select
5300
5301 /* bug 4638656 - start */
5302 /* We donot consider transaction subtype POCO, since a
5303 document cannot be in requires reapproval when
5304 launching approval from create doc */
5305
5306 --Bug4956479 Included agent_id/preparer_id in select/into clause
5307 --for doctype of both PO/RELEASE
5308
5309 l_transaction_subtype := 'POO';
5310
5311 IF x_doc_type = 'PO' THEN
5312 BEGIN
5313 select pvs.tp_header_id,
5314 nvl(etd.edi_flag,'N'),
5315 ph.agent_id
5316 into l_tp_header_id,
5317 l_edi_flag,
5318 x_preparer_id
5319 from ece_tp_details etd,
5320 po_vendor_sites pvs,
5321 po_vendors pv,
5322 po_headers ph
5323 where pv.vendor_id = pvs.vendor_id
5324 and pvs.tp_header_id = etd.tp_header_id
5325 and etd.document_id = l_transaction_subtype
5326 and ph.vendor_id = pv.vendor_id
5327 and ph.vendor_site_id = pvs.vendor_site_id
5328 and ph.po_header_id = x_doc_id
5329 and ph.type_lookup_code= x_doc_subtype
5330 and etd.document_type = ph.type_lookup_code;
5331
5332 EXCEPTION
5333 WHEN NO_DATA_FOUND THEN
5334 l_edi_flag := 'N';
5335 END;
5336
5337 ELSIF x_doc_type = 'RELEASE' THEN
5338
5339 BEGIN
5340
5341 select pvs.tp_header_id,
5342 nvl(etd.edi_flag,'N'),
5343 pr.agent_id
5344 into l_tp_header_id,
5345 l_edi_flag,
5346 x_preparer_id
5347 from ece_tp_details etd,
5348 po_vendor_sites pvs,
5349 po_vendors pv,
5350 po_headers ph ,
5351 po_releases pr
5352 where pv.vendor_id = pvs.vendor_id
5353 and pvs.tp_header_id = etd.tp_header_id
5354 and etd.document_id = l_transaction_subtype
5355 and ph.vendor_id = pv.vendor_id
5356 and ph.vendor_site_id = pvs.vendor_site_id
5357 and pr.po_header_id = ph.po_header_id
5358 and pr.po_release_id = x_doc_id
5359 and etd.document_type = 'RELEASE';
5360
5361 EXCEPTION
5362 WHEN NO_DATA_FOUND THEN
5363 l_edi_flag := 'N';
5364 END;
5365 END IF;
5366
5367 if (l_edi_flag ='Y') and (l_tp_header_id is not null) then
5368 x_printflag := 'N';
5369 x_faxflag := 'N';
5370 x_faxnum := null;
5374 --Bug4956479
5371 x_emailflag := 'N';
5372 x_emailaddress:= null;
5373
5375 po_wf_util_pkg.SetItemAttrNumber(itemtype => itemtype,
5376 itemkey => itemkey,
5377 aname => 'SUGGESTED_BUYER_ID',
5378 avalue => x_preparer_id);
5379
5380 else
5381 /* bug 4638656 - end */
5382
5383
5384 PO_VENDOR_SITES_SV.Get_Transmission_Defaults(
5385 p_document_id => x_doc_id,
5386 p_document_type => x_doc_type,
5387 p_document_subtype => x_doc_subtype,
5388 p_preparer_id => x_preparer_id,
5389 x_default_method => x_default_method,
5390 x_email_address => x_emailaddress,
5391 x_fax_number => x_faxnum,
5392 x_document_num => x_doc_num );
5393
5394 -- Bug 3152167 Get the document number and pass the same to start po approval.
5395
5396 po_wf_util_pkg.SetItemAttrNumber(itemtype => itemtype,
5397 itemkey => itemkey,
5398 aname => 'SUGGESTED_BUYER_ID',
5399 avalue => x_preparer_id);
5400
5401
5402 If (x_default_method = 'EMAIL' ) and (x_emailaddress is not null) then
5403 x_emailflag := 'Y';
5404 elsif x_default_method = 'FAX' and (x_faxnum is not null) then
5405 x_emailaddress := null;
5406 x_faxnum := x_fax_area || x_faxnum;
5407
5408 x_faxflag := 'Y';
5409 elsif x_default_method = 'PRINT' then
5410 x_emailaddress := null;
5411 x_faxnum := null;
5412
5413 x_printflag := 'Y';
5414 else
5415 x_emailaddress := null;
5416 x_faxnum := null;
5417 end if;
5418
5419 end if; -- if l_edi_flag..
5420
5421
5422 /* <SUP_CON FPI START> */
5423 /* Add code to check if document is Consumption PO/Release.
5424 * If it is, transmission method will be disabled.
5425 */
5426
5427 /* Kick off the po approval workflow */
5428
5429 x_progress:= '18: launch_po_approval: Kicking off start_wf_process with' ||
5430 'item_type = ' || x_ItemType || '/ ' || 'item_key = ' ||
5431 x_ItemKey || '/ ' || 'workflow_process = ' || x_workflow_process ||
5432 '/ ' || 'doc_type = ' || x_doc_type || '/ ' || 'x_doc_id = ' ||
5433 to_char(x_doc_id);
5434
5435 IF (g_po_wf_debug = 'Y') THEN
5436 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
5437 END IF;
5438
5439 po_reqapproval_init1.start_wf_process(
5440 x_ItemType,
5441 x_ItemKey,
5442 x_workflow_process,
5443 x_action_orig_from,
5444 x_doc_id,
5445 x_doc_num, -- pass in as null since id exists
5446 x_preparer_id,
5447 x_doc_type,
5448 x_doc_subtype,
5449 x_submitter_action,
5450 x_forward_to_id,
5451 x_forward_from_id,
5452 x_def_approval_path_id,
5453 x_note,
5454 x_printflag,
5455 x_faxflag,
5456 x_faxnum,
5457 x_emailflag,
5458 x_emailaddress);
5459
5460 resultout := wf_engine.eng_completed || ':' || 'ACTIVITY_PERFORMED';
5461
5462 x_progress:= '20: launch_po_approval: result = ACTIVITY_PERFORMED';
5463 IF (g_po_wf_debug = 'Y') THEN
5464 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
5465 END IF;
5466
5467 --<BUG 5044645 START> set the org context back to original one
5468 IF x_org_id <> l_purchasing_org_id THEN
5469 po_moac_utils_pvt.set_org_context(x_org_id);
5470 END IF;
5471 --<BUG 5044645 END>
5472
5473 exception
5474 when others then
5475 wf_core.context('po_autocreate_doc','launch_po_approval',x_progress);
5476 raise;
5477 end launch_po_approval;
5478
5479
5480 /***************************************************************************
5481 *
5482 * Procedure: purge_rows_from_temp_table
5483 *
5484 * Description: This purges the rows from the temp table for the
5485 * group id associated with this workflow run.
5486 *
5487 **************************************************************************/
5488 procedure purge_rows_from_temp_table (itemtype IN VARCHAR2,
5489 itemkey IN VARCHAR2,
5490 actid IN NUMBER,
5491 funcmode IN VARCHAR2,
5492 resultout OUT NOCOPY VARCHAR2 ) is
5493 x_group_id number;
5494 x_progress varchar2(300);
5495
5496 begin
5497
5501 aname => 'GROUP_ID');
5498 x_group_id:= po_wf_util_pkg.GetItemAttrNumber
5499 (itemtype => itemtype,
5500 itemkey => itemkey,
5502
5503
5504 /* Delete all rows belonging to this group_id */
5505
5506 delete from po_wf_candidate_req_lines_temp
5507 where group_id = x_group_id;
5508
5509 /* Calling process should do the commit, so comment out here.
5510 * COMMIT;
5511 */
5512
5513 resultout := wf_engine.eng_completed || ':' || 'ACTIVITY_PERFORMED';
5514
5515 x_progress:= '10: purge_rows_from_temp_table: result = ACTIVITY_PERFORMED';
5516 IF (g_po_wf_debug = 'Y') THEN
5517 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
5518 END IF;
5519
5520
5521
5522 exception
5523 when others then
5524 wf_core.context('po_autocreate_doc','purge_rows_from_temp_table',x_progress);
5525 raise;
5526 end purge_rows_from_temp_table;
5527
5528 /***************************************************************************
5529 *
5530 * Procedure: is_this_emergency_req
5531 *
5532 * Description: This check if there is a reserved PO number
5533 *
5534 **************************************************************************/
5535 procedure is_this_emergency_req(itemtype IN VARCHAR2,
5536 itemkey IN VARCHAR2,
5537 actid IN NUMBER,
5538 funcmode IN VARCHAR2,
5539 resultout OUT NOCOPY VARCHAR2 ) is
5540
5541 x_req_header_id number;
5542 x_emergency_po_num varchar2(20);
5543 x_progress varchar2(300);
5544
5545 begin
5546
5547 x_req_header_id := po_wf_util_pkg.GetItemAttrText (itemtype => itemtype,
5548 itemkey => itemkey,
5549 aname => 'REQ_HEADER_ID');
5550 BEGIN
5551 SELECT emergency_po_num
5552 INTO x_emergency_po_num
5553 FROM po_requisition_headers
5554 WHERE requisition_header_id=x_req_header_id;
5555 EXCEPTION
5556 WHEN OTHERS THEN
5557
5558 resultout := wf_engine.eng_completed || ':' || 'N';
5559 x_progress:= '10: is_this_emergency_req: result = N';
5560 IF (g_po_wf_debug = 'Y') THEN
5561 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
5562 END IF;
5563
5564 END;
5565
5566 IF x_emergency_po_num IS NOT NULL THEN
5567 resultout := wf_engine.eng_completed || ':' || 'Y';
5568 x_progress:= '20: is_this_emergency_req: result = Y';
5569 IF (g_po_wf_debug = 'Y') THEN
5570 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
5571 END IF;
5572
5573 ELSE
5574 resultout := wf_engine.eng_completed || ':' || 'N';
5575 x_progress:= '30: is_this_emergency_req: result = N';
5576 IF (g_po_wf_debug = 'Y') THEN
5577 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
5578 END IF;
5579
5580 END IF;
5581
5582 exception
5583 when others then
5584 wf_core.context('po_autocreate_doc','is_this_emergency_req',x_progress);
5585 raise;
5586 end is_this_emergency_req;
5587
5588 /***************************************************************************
5589 *
5590 * Procedure: put_on_one_po
5591 *
5592 * Description: Group all req lines into one po and
5593 * insert into the interface tables
5594 * Remark: This is for processing emergency requisitions
5595 * where only one PO number is reserved for each
5596 * requisition
5597 *
5598 **************************************************************************/
5599 procedure put_on_one_po(itemtype IN VARCHAR2,
5600 itemkey IN VARCHAR2,
5601 actid IN NUMBER,
5602 funcmode IN VARCHAR2,
5603 resultout OUT NOCOPY VARCHAR2 ) is
5604
5605 c_rowid rowid;
5606 c_group_id number;
5607 c_req_header_id number;
5608 c_req_line_id number;
5609 c_suggested_buyer_id number;
5610 c_source_doc_type_code varchar2(25);
5611 c_source_doc_id number;
5612 c_source_doc_line number;
5613 c_suggested_vendor_id number;
5614 c_suggested_vendor_site_id number;
5615 c_currency_code varchar2(15);
5616 c_rate_type varchar2(30);
5617 c_rate_date date;
5618 c_rate number;
5619 c_process_code varchar2(30);
5620 c_rel_gen_method varchar2(25);
5621 c_item_id number;
5622 c_pcard_id number;
5623 c_contract_id number;
5624
5625 l_enable_vmi_flag po_asl_attributes.enable_vmi_flag%TYPE; -- Consigned FPI
5626 l_last_billing_date po_asl_attributes.last_billing_date%TYPE; -- Consigned FPI
5627 l_cons_billing_cycle po_asl_attributes.consigned_billing_cycle%TYPE; -- Consigned FPI
5628
5629 c_dest_org_id number; -- Consigned FPI
5630 c_dest_type_code po_requisition_lines.destination_type_code%TYPE; -- Consigned FPI
5631 c_cons_from_supp_flag varchar2(1); -- Consigned FPI
5635 x_first_time_for_this_comb varchar2(5);
5632
5633 x_progress varchar2(300);
5634 x_group_id number;
5636 x_interface_header_id number;
5637 x_suggested_vendor_contact_id number;
5638 x_suggested_vendor_contact varchar2(240);
5639 x_prev_sug_vendor_contact_id number;
5640 x_carry_contact_to_po_flag varchar2(10);
5641
5642 l_return_status varchar2(1) := NULL;
5643 l_msg_count number := NULL;
5644 l_msg_data varchar2(2000) := NULL;
5645
5646 l_style_id PO_DOC_STYLE_HEADERS.style_id%TYPE; --<R12 STYLES PHASE II>
5647 /* Define the cursor which picks up records from the temp table.
5648 * We need the 'for update' since we are going to update the
5649 * process_code.
5650 */
5651
5652 /* Bug # 1721991.
5653 The 'for update' clause was added to update the row which was processed
5654 in the Cursor c1 but this led to another problem in Oracle 8.1.6.3 or above
5655 where you can't have a commit inside a 'for update' Cursor loop.
5656 This let to the Runtime Error 'fetch out of sequence'
5657 The commit was actually issued in the procedure insert_into_header_interface.
5658 To solve this we removed the for update in the cursor and instead used rowid
5659 to update the row processed by the Cursor.
5660 */
5661
5662 cursor c1 is /* x_group_id is a parameter */
5663 select prlt.rowid, -- Bug# 1721991, Added rowid to update row
5664 prlt.group_id,
5665 prlt.requisition_header_id,
5666 prlt.requisition_line_id,
5667 prlt.suggested_buyer_id,
5668 prlt.source_doc_type_code,
5669 prlt.source_doc_id,
5670 prlt.source_doc_line,
5671 prlt.suggested_vendor_id,
5672 prlt.suggested_vendor_site_id,
5673 prlt.currency_code,
5674 prlt.rate_type,
5675 prlt.rate_date,
5676 prlt.rate,
5677 prlt.process_code,
5678 prlt.release_generation_method,
5679 prlt.item_id,
5680 prlt.pcard_id,
5681 prlt.contract_id,
5682 prl.suggested_vendor_contact,
5683 prl.vendor_contact_id,
5684 prl.destination_organization_id,
5685 prl.destination_type_code
5686 from po_wf_candidate_req_lines_temp prlt,
5687 po_requisition_lines prl
5688 where prlt.process_code = 'PENDING'
5689 and prlt.group_id = x_group_id
5690 and prlt.requisition_header_id = prl.requisition_header_id
5691 and prlt.requisition_line_id = prl.requisition_line_id;
5692 --Bug # 1721991, for update;
5693
5694
5695 begin
5696
5697
5698 /* Get the group_id since we only want to process lines belonging
5699 * to the same group. We need to get the group_id before opening
5700 * the cursor since it is a parameter to the cursor.
5701 */
5702 l_style_id := PO_DOC_STYLE_GRP.get_standard_doc_style; --<R12 STYLES PHASE II>
5703
5704 x_group_id := po_wf_util_pkg.GetItemAttrNumber (itemtype => itemtype,
5705 itemkey => itemkey,
5706 aname => 'GROUP_ID');
5707
5708 x_progress := '10: put_on_one_po : group_id '||
5709 to_char(x_group_id);
5710 IF (g_po_wf_debug = 'Y') THEN
5711 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
5712 END IF;
5713
5714
5715 /* only true for the first line */
5716 x_first_time_for_this_comb := 'TRUE';
5717 x_suggested_vendor_contact_id := NULL;
5718 x_carry_contact_to_po_flag := 'TRUE';
5719
5720 /* Open the cursor with that group_id */
5721 open c1; /* Based on x_group_id */
5722 loop
5723 fetch c1 into c_rowid, --Bug# 1721991, Added rowid
5724 c_group_id,
5725 c_req_header_id,
5726 c_req_line_id,
5727 c_suggested_buyer_id,
5728 c_source_doc_type_code,
5729 c_source_doc_id,
5730 c_source_doc_line,
5731 c_suggested_vendor_id,
5732 c_suggested_vendor_site_id,
5733 c_currency_code,
5734 c_rate_type,
5735 c_rate_date,
5736 c_rate,
5737 c_process_code,
5738 c_rel_gen_method,
5739 c_item_id,
5740 c_pcard_id,
5741 c_contract_id,
5742 x_suggested_vendor_contact,
5743 x_suggested_vendor_contact_id,
5744 c_dest_org_id,
5745 c_dest_type_code;
5746 exit when c1%NOTFOUND;
5747
5748 update po_wf_candidate_req_lines_temp
5749 set process_code = 'PROCESSED'
5750 where rowid=c_rowid;
5751 --Bug# 1721991, where current of c1;
5752
5753 if (x_suggested_vendor_contact_id is null) then
5754 x_suggested_vendor_contact_id := get_contact_id(x_suggested_vendor_contact, c_suggested_vendor_site_id);
5755 end if;
5756
5757 /* Consigned FPI start */
5758 PO_THIRD_PARTY_STOCK_GRP.Get_Asl_Attributes
5759 ( p_api_version => 1.0
5760 , p_init_msg_list => NULL
5761 , x_return_status => l_return_status
5762 , x_msg_count => l_msg_count
5763 , x_msg_data => l_msg_data
5764 , p_inventory_item_id => c_item_id
5768 , x_consigned_from_supplier_flag => c_cons_from_supp_flag
5765 , p_vendor_id => c_suggested_vendor_id
5766 , p_vendor_site_id => c_suggested_vendor_site_id
5767 , p_using_organization_id => c_dest_org_id
5769 , x_enable_vmi_flag => l_enable_vmi_flag
5770 , x_last_billing_date => l_last_billing_date
5771 , x_consigned_billing_cycle => l_cons_billing_cycle
5772 );
5773
5774 if nvl(c_dest_type_code,'INVENTORY') = 'EXPENSE' then
5775 c_cons_from_supp_flag := 'N';
5776 end if;
5777
5778 /* Consigned FPI end */
5779
5780 /** Bug 956730
5781 * bgu, Aug. 11, 1999
5782 * For Emergency Requisition, don't need to populate sourcing
5783 * information.
5784 */
5785 if (x_first_time_for_this_comb = 'TRUE') then
5786
5787 if(po_autocreate_doc.insert_into_headers_interface
5788 (itemtype,
5789 itemkey,
5790 c_group_id,
5791 c_suggested_vendor_id,
5792 c_suggested_vendor_site_id,
5793 c_suggested_buyer_id,
5794 null,
5795 null,
5796 c_currency_code,
5797 c_rate_type,
5798 c_rate_date,
5799 c_rate,
5800 c_pcard_id,
5801 l_style_id, --<R12 STYLES PHASE II>
5802 x_interface_header_id) = FALSE) then
5803 return; --bug 3401653: po creation failed
5804 end if;
5805
5806
5807
5808
5809 po_autocreate_doc.insert_into_lines_interface (itemtype,
5810 itemkey,
5811 x_interface_header_id,
5812 c_req_line_id,
5813 null,
5814 null,
5815 null,
5816 null,
5817 c_cons_from_supp_flag); -- Consigned FPI
5818
5819 x_progress := '20: put_on_one_po: inserted header'||
5820 ' and line for req line = ' ||
5821 to_char(c_req_line_id);
5822 IF (g_po_wf_debug = 'Y') THEN
5823 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
5824 END IF;
5825
5826 x_first_time_for_this_comb := 'FALSE';
5827
5828 if (x_suggested_vendor_contact_id is NULL) then
5829 x_carry_contact_to_po_flag := 'FALSE';
5830 end if;
5831
5832 x_prev_sug_vendor_contact_id := x_suggested_vendor_contact_id;
5833
5834 else /* ie. x_first_time_for_this_comb = FALSE */
5835
5836 /* The line will be put onto the same header
5837 * as a previous one, so only insert a new line into the
5838 * po_lines_interface table.
5839 */
5840
5841 po_autocreate_doc.insert_into_lines_interface (itemtype,
5842 itemkey,
5843 x_interface_header_id,
5844 c_req_line_id,
5845 null,
5846 null,
5847 null,
5848 null,
5849 c_cons_from_supp_flag); -- Consigned FPI
5850
5851
5852 if (x_carry_contact_to_po_flag = 'TRUE' and x_suggested_vendor_contact_id is not null) then
5853 if (x_suggested_vendor_contact_id <> x_prev_sug_vendor_contact_id) then
5854 x_carry_contact_to_po_flag := 'FALSE';
5855 end if;
5856 else
5857 x_carry_contact_to_po_flag := 'FALSE';
5858 end if;
5859
5860 x_progress := '30: put_on_one_po: inserted just line for '||
5861 'req line = ' || to_char(c_req_line_id);
5862 IF (g_po_wf_debug = 'Y') THEN
5863 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
5864 END IF;
5865
5866 end if;
5867
5868 x_prev_sug_vendor_contact_id := x_suggested_vendor_contact_id;
5869
5870 end loop;
5871 close c1;
5872
5873 if (x_carry_contact_to_po_flag = 'TRUE' and
5874 valid_contact(c_suggested_vendor_site_id, x_suggested_vendor_contact_id)) then
5875 begin
5876 x_progress := '55: group_req_lines: updating header with vendor contact :'||x_interface_header_id;
5877
5878 update po_headers_interface
5879 set vendor_contact_id = x_suggested_vendor_contact_id
5880 where interface_header_id = x_interface_header_id;
5881
5882 exception
5883 when others then
5884 IF (g_po_wf_debug = 'Y') THEN
5885 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
5886 END IF;
5887 end;
5888 end if;
5889
5890
5891 resultout := wf_engine.eng_completed || ':' || 'ACTIVITY_PERFORMED';
5892
5893 x_progress := '40: put_on_one_po: result = ACTIVITY_PERFORMED ';
5894 IF (g_po_wf_debug = 'Y') THEN
5895 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
5896 END IF;
5897
5898 exception
5899 when others then
5900 close c1;
5901 wf_core.context('po_autocreate_doc','put_on_one_po',x_progress);
5902 raise;
5903 end put_on_one_po;
5904
5908 *
5905 /***************************************************************************
5906 *
5907 * Procedure: send_return_notif
5909 * Description: Send notification to preparer when the requisition
5910 * is returned.
5911 *
5912 **************************************************************************/
5913
5914 /* Bug# 1694064: kagarwal
5915 ** Desc: Calling Req Approval wf to send return Notification
5916 ** instead of the wf API.
5917 */
5918
5919 procedure send_return_notif(p_req_header_id IN number,
5920 p_agent_id IN number,
5921 p_reason IN VARCHAR2) is
5922
5923 l_doc_type varchar2(240); /* Bug# 2681512: kagarwal */
5924 --Bug# 3268971: sbgeorge
5925 l_doc_type_code varchar2(80);
5926 l_doc_subtype varchar2(80);
5927 doc_subtype varchar2(25);
5928
5929 l_req_num varchar2(20);
5930 l_agent_name varchar2(240);
5931 l_preparer_id number;
5932 l_preparer_disp_name varchar2(240);
5933 l_preparer_user_name varchar2(200);
5934
5935 l_nid number;
5936 l_seq varchar2(10);
5937 ItemType varchar2(8);
5938 ItemKey varchar2(240);
5939
5940 l_update_req_url varchar2(1000);
5941 l_open_req_url varchar2(1000);
5942 l_resubmit_req_url varchar2(1000);
5943
5944 l_org_id number;
5945
5946 x_progress varchar2(300);
5947
5948 -- bug 5657496 variable addition <START>
5949 l_responsibility_id NUMBER;
5950 l_user_id NUMBER;
5951 l_application_id NUMBER;
5952 -- bug 5657496 variable addition <END>
5953
5954 begin
5955
5956 x_progress := 'PO_AUTOCREATE_DOC.send_return_notif: 001';
5957
5958 /* Bug# 2681512: kagarwal
5959 ** We will get the document type display value from
5960 ** po document types for correct translation.
5961 */
5962 /*
5963 select st.DISPLAYED_FIELD,
5964 ty.DISPLAYED_FIELD,
5965 hd.SEGMENT1,
5966 hd.ORG_ID,
5967 hd.PREPARER_ID,
5968 hd.TYPE_LOOKUP_CODE
5969 into l_doc_subtype,
5970 l_doc_type,
5971 l_req_num,
5972 l_org_id,
5973 l_preparer_id,
5974 doc_subtype
5975 from po_requisition_headers hd,
5976 po_lookup_codes ty,
5977 po_lookup_codes st
5978 where hd.requisition_header_id = p_req_header_id
5979 and ty.lookup_type = 'DOCUMENT TYPE'
5980 and ty.lookup_code = 'REQUISITION'
5981 and st.lookup_type = 'REQUISITION TYPE'
5982 and st.lookup_code = hd.TYPE_LOOKUP_CODE;
5983 */
5984
5985 select ty.DOCUMENT_TYPE_CODE,
5986 ty.DOCUMENT_SUBTYPE,
5987 ty.type_name,
5988 hd.SEGMENT1,
5989 hd.ORG_ID,
5990 hd.PREPARER_ID,
5991 hd.TYPE_LOOKUP_CODE,
5992 ty.wf_approval_itemtype
5993 into l_doc_type_code,
5994 l_doc_subtype,
5995 l_doc_type,
5996 l_req_num,
5997 l_org_id,
5998 l_preparer_id,
5999 doc_subtype,
6000 ItemType
6001 from po_requisition_headers hd,
6002 po_document_types ty
6003 where hd.requisition_header_id = p_req_header_id
6004 and ty.document_type_code = 'REQUISITION'
6005 and ty.document_subtype = hd.TYPE_LOOKUP_CODE;
6006
6007 -- Get Req Approval process.
6008
6009 x_progress := 'PO_AUTOCREATE_DOC.send_return_notif: 010';
6010
6011 /* Bug# 2681512: kagarwal
6012 ** Getting the wf_item_type in the SQL above
6013
6014 begin
6015
6016 select wf_approval_itemtype
6017 into ItemType
6018 from PO_DOCUMENT_TYPES
6019 where DOCUMENT_TYPE_CODE = 'REQUISITION'
6020 and DOCUMENT_SUBTYPE = doc_subtype;
6021
6022 exception
6023 when others then
6024 null;
6025 end;
6026 */
6027
6028 IF ItemType IS NULL THEN
6029 x_progress := 'PO_AUTOCREATE_DOC.send_return_notif: 020';
6030 return;
6031 END IF;
6032
6033 -- Build the links.
6034
6035 l_open_req_url := por_util_pkg.jumpIntoFunction(
6036 p_application_id => 178,
6037 p_function_code => 'POR_OPEN_REQ',
6038 p_parameter1 => to_char(p_req_header_id),
6039 p_parameter11 => to_char(l_org_id) );
6040
6041 l_update_req_url := por_util_pkg.jumpIntoFunction(
6042 p_application_id=> 178,
6043 p_function_code => 'POR_UPDATE_REQ',
6044 p_parameter1 => to_char(p_req_header_id),
6045 p_parameter11 => to_char(l_org_id) );
6046
6047 l_resubmit_req_url := por_util_pkg.jumpIntoFunction(
6048 p_application_id=> 178,
6049 p_function_code => 'POR_RESUBMIT_URL',
6050 p_parameter1 => to_char(p_req_header_id),
6051 p_parameter11 => to_char(l_org_id) );
6052
6053 PO_REQAPPROVAL_INIT1.get_user_name(l_preparer_id, l_preparer_user_name,
6057 into l_agent_name
6054 l_preparer_disp_name);
6055
6056 select pr.FULL_NAME
6058 from per_people_f pr
6059 where pr.person_id = p_agent_id
6060 and trunc(sysdate) between pr.effective_start_date
6061 and pr.effective_end_date;
6062
6063 -- Create wf process.
6064
6065 x_progress := 'PO_AUTOCREATE_DOC.send_return_notif: 040';
6066
6067 select to_char(PO_WF_ITEMKEY_S.NEXTVAL) into l_seq from sys.dual;
6068 ItemKey := to_char(p_req_header_id) || '-' || l_seq;
6069
6070 wf_engine.CreateProcess( ItemType => ItemType,
6071 ItemKey => ItemKey,
6072 process => 'NOTIFY_RETURN_REQ');
6073
6074 x_progress := 'PO_AUTOCREATE_DOC.send_return_notif: 050 - '||
6075 'itemtype: ' || ItemType || 'itemkey: ' || ItemKey;
6076 IF (g_po_wf_debug = 'Y') THEN
6077 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
6078 END IF;
6079
6080 -- Set the attributes
6081 po_wf_util_pkg.SetItemAttrText ( itemtype => itemtype,
6082 itemkey => itemkey,
6083 aname => 'DOCUMENT_NUMBER',
6084 avalue => l_req_num);
6085 --
6086 po_wf_util_pkg.SetItemAttrNumber ( itemtype => itemtype,
6087 itemkey => itemkey,
6088 aname => 'DOCUMENT_ID',
6089 avalue => p_req_header_id);
6090 --
6091 po_wf_util_pkg.SetItemAttrText ( itemtype => itemtype,
6092 itemkey => itemkey,
6093 aname => 'DOCUMENT_TYPE_DISP',
6094 avalue => l_doc_type);
6095 --
6096 -- Bug 2942228. The org id was getting changed from 458 to 204 when requisition was
6097 -- returned in Vision Services because it is not set as Workflow Attribute here.
6098 po_wf_util_pkg.SetItemAttrNumber ( itemtype => itemtype,
6099 itemkey => itemkey,
6100 aname => 'ORG_ID',
6101 avalue => l_org_id);
6102
6103 /* Bug# 2681512: kagarwal
6104 ** Desc: We will only be using one display attribute for type and
6105 ** subtype - DOCUMENT_TYPE_DISP, hence commenting the code below
6106 */
6107 /*
6108 po_wf_util_pkg.SetItemAttrText ( itemtype => itemtype,
6109 itemkey => itemkey,
6110 aname => 'DOCUMENT_SUBTYPE_DISP',
6111 avalue => l_doc_subtype);
6112 --
6113 */
6114 /* Bug# 3268971: sbgeorge
6115 ** Need to call PO_REQAPPROVAL_INIT1.Get_Req_Attributes after start in
6116 ** NOTIFY_RETURN_REQ process, since the notification needs to be similar to
6117 ** other Req Approval Notifications.
6118 ** Setting the item attributes for DOCUMENT_TYPE and DOCUMENT_SUBTYPE with
6119 ** document_type_code and document_subtype from po_document_types, because
6120 ** these are used in PO_REQAPPROVAL_INIT1.SetReqHdrAttributes to set the item
6121 ** attribute for DOCUMENT_TYPE_DISP.
6122 */
6123 po_wf_util_pkg.SetItemAttrText ( itemtype => itemtype,
6124 itemkey => itemkey,
6125 aname => 'DOCUMENT_TYPE',
6126 avalue => l_doc_type_code);
6127 --
6128 po_wf_util_pkg.SetItemAttrText ( itemtype => itemtype,
6129 itemkey => itemkey,
6130 aname => 'DOCUMENT_SUBTYPE',
6131 avalue => l_doc_subtype);
6132 --
6133
6134 po_wf_util_pkg.SetItemAttrText ( itemtype => itemtype,
6135 itemkey => itemkey,
6136 aname => 'NOTE',
6137 avalue => p_reason);
6138 --
6139 po_wf_util_pkg.SetItemAttrText ( itemtype => itemType,
6140 itemkey => itemkey,
6141 aname => 'PREPARER_USER_NAME' ,
6142 avalue => l_preparer_user_name);
6143
6144 po_wf_util_pkg.SetItemAttrText ( itemtype => itemType,
6145 itemkey => itemkey,
6146 aname => 'PREPARER_DISPLAY_NAME' ,
6147 avalue => l_preparer_disp_name);
6148
6149 po_wf_util_pkg.SetItemAttrText ( itemtype => itemtype,
6150 itemkey => itemkey,
6151 aname => 'APPROVER_DISPLAY_NAME' ,
6152 avalue => l_agent_name);
6153
6154
6155 po_wf_util_pkg.SetItemAttrText ( itemtype => itemtype,
6156 itemkey => itemkey,
6157 aname => 'REQ_URL' ,
6158 avalue => l_open_req_url);
6159
6160 po_wf_util_pkg.SetItemAttrText ( itemtype => itemtype,
6161 itemkey => itemkey,
6165 po_wf_util_pkg.SetItemAttrText ( itemtype => itemtype,
6162 aname => 'REQ_UPDATE_URL' ,
6163 avalue => l_update_req_url);
6164
6166 itemkey => itemkey,
6167 aname => 'REQ_RESUBMIT_URL' ,
6168 avalue => l_resubmit_req_url);
6169
6170 -- bug 5657496 <START>
6171 -- Need to set the context variables also, else the selector function in
6172 -- the workflow will set a null context.
6173 l_user_id := FND_GLOBAL.user_id;
6174 l_responsibility_id := FND_GLOBAL.resp_id;
6175 l_application_id := FND_GLOBAL.resp_appl_id;
6176 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
6177 itemkey => itemkey,
6178 aname => 'USER_ID',
6179 avalue => l_user_id);
6180
6181 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
6182 itemkey => itemkey,
6183 aname => 'APPLICATION_ID',
6184 avalue => l_application_id);
6185
6186 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
6187 itemkey => itemkey,
6188 aname => 'RESPONSIBILITY_ID',
6189 avalue => l_responsibility_id);
6190 -- bug 5657496 <END>
6191
6192 -- Start Process
6193 x_progress := 'PO_AUTOCREATE_DOC.send_return_notif: 055';
6194 IF (g_po_wf_debug = 'Y') THEN
6195 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
6196 END IF;
6197
6198 wf_engine.StartProcess(itemtype => itemtype,
6199 itemkey => itemkey );
6200
6201
6202 EXCEPTION
6203 WHEN OTHERS THEN
6204 po_message_s.sql_error('Error: send_return_notif()', x_progress, sqlcode);
6205 RAISE;
6206
6207 end send_return_notif;
6208
6209 /***************************************************************************
6210 *
6211 * function: get_document_num
6212 *
6213 * Description: get the next document number in the OU specified
6214 * from po_unique_identifier_cont_all table
6215 *
6216 **************************************************************************/
6217
6218 FUNCTION get_document_num (
6219 p_purchasing_org_id IN NUMBER --<Shared Proc FPJ>
6220 ) RETURN VARCHAR2
6221 IS
6222
6223 -- Bug # 1869409
6224 -- Created a function get_document_num as an autonomous transaction
6225 -- to avoid the COMMIT for the Workflow transactions.
6226
6227 -- bug5176308
6228 -- No need to be an autonomous transaction as the number generation is now
6229 -- through an API which itself is an autonomous transaction.
6230
6231 -- pragma AUTONOMOUS_TRANSACTION;
6232
6233 x_document_num varchar2(25);
6234 x_progress varchar2(300);
6235
6236 begin
6237
6238 x_progress := '10: get_document_num: Just before get doc' ||
6239 'num from po_unique_identifier_control';
6240
6241 -- bug5176308
6242 -- Call API to get the next document num
6243 x_document_num :=
6244 PO_CORE_SV1.default_po_unique_identifier
6245 ( p_table_name => 'PO_HEADERS',
6246 p_org_id => p_purchasing_org_id
6247 );
6248
6249
6250 x_progress := '20: get_document_num: Just after get doc' ||
6251 'num from po_unique_identifier_control';
6252
6253 return x_document_num;
6254
6255 exception
6256 when others then
6257 wf_core.context('po_autocreate_doc','get_document_num',x_progress);
6258 raise;
6259
6260 end get_document_num;
6261
6262 /***************************************************************************
6263 *
6264 * function: is_contract_required_on_req_line
6265 *
6266 * Description: check the worlflow options for the autocreate po
6267 * document.
6268 *
6269 **************************************************************************/
6270
6271 procedure is_contract_required_on_req(itemtype IN VARCHAR2,
6272 itemkey IN VARCHAR2,
6273 actid IN NUMBER,
6274 funcmode IN VARCHAR2,
6275 resultout OUT NOCOPY VARCHAR2 ) is
6276
6277 x_contract_required_flag varchar2(1);
6278
6279 x_progress varchar2(300) := '000';
6280
6281 begin
6282
6283 x_contract_required_flag := po_wf_util_pkg.GetItemAttrText
6284 (itemtype => itemtype,
6285 itemkey => itemkey,
6286 aname => 'CONTRACT_REQUIRED_FLAG');
6287
6288 if nvl(x_contract_required_flag, 'N') <> 'Y' then
6289
6290 resultout := wf_engine.eng_completed || ':' || 'N';
6291
6292 return;
6293 else
6294 resultout:= wf_engine.eng_completed || ':' || 'Y';
6295
6296 return;
6297
6298 end if;
6299
6303 wf_core.context('po_autocreate_doc','is_contract_required_on_req',x_progress);
6300 exception
6301
6302 when others then
6304 raise;
6305
6306 end is_contract_required_on_req;
6307
6308 /***************************************************************************
6309 *
6310 * function: should_contract_be_used
6311 *
6312 * Description: check whether contract be used to create document
6313 *
6314 **************************************************************************/
6315 procedure should_contract_be_used(itemtype IN VARCHAR2,
6316 itemkey IN VARCHAR2,
6317 actid IN NUMBER,
6318 funcmode IN VARCHAR2,
6319 resultout OUT NOCOPY VARCHAR2 ) is
6320 x_use_contract_flag varchar2(1);
6321
6322 x_progress varchar2(300) := '000';
6323
6324 begin
6325
6326 x_use_contract_flag := po_wf_util_pkg.GetItemAttrText
6327 (itemtype => itemtype,
6328 itemkey => itemkey,
6329 aname => 'USE_CONTRACT_FLAG');
6330
6331 if nvl(x_use_contract_flag, 'N') <> 'Y' then
6332
6333 resultout := wf_engine.eng_completed || ':' || 'N';
6334
6335 return;
6336 else
6337 resultout:= wf_engine.eng_completed || ':' || 'Y';
6338
6339 return;
6340
6341 end if;
6342
6343 exception
6344
6345 when others then
6346 wf_core.context('po_autocreate_doc','should_contract_be_used',x_progress);
6347 raise;
6348
6349 end should_contract_be_used;
6350
6351 /***************************************************************************
6352 *
6353 * Procedure: non_catalog_item_check
6354 *
6355 * Description: Checks if this is a non_catalog_item req line
6356 * (ie. non_catalog item)
6357 *
6358 **************************************************************************/
6359
6360 procedure non_catalog_item_check (itemtype IN VARCHAR2,
6361 itemkey IN VARCHAR2,
6362 actid IN NUMBER,
6363 funcmode IN VARCHAR2,
6364 resultout OUT NOCOPY VARCHAR2 ) is
6365
6366 x_item_id number;
6367 x_progress varchar2(300);
6368 x_catalog_type varchar2(30);
6369
6370 begin
6371
6372 x_catalog_type := po_wf_util_pkg.GetItemAttrText (itemtype => itemtype,
6373 itemkey => itemkey,
6374 aname => 'CATALOG_TYPE');
6375
6376 if nvl(x_catalog_type, 'CATALOG') <> 'NONCATALOG' then
6377
6378 resultout := wf_engine.eng_completed || ':' || 'N';
6379
6380 else
6381 resultout := wf_engine.eng_completed || ':' || 'Y';
6382
6383 x_progress:= '10: non_catalog_item_check: result = '|| resultout;
6384 IF (g_po_wf_debug = 'Y') THEN
6385 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
6386 END IF;
6387
6388 end if;
6389
6390
6391 exception
6392 when others then
6393 wf_core.context('po_autocreate_doc','non_catalog_item_check',x_progress);
6394 raise;
6395 end non_catalog_item_check;
6396
6397
6398 /***************************************************************************
6399 *
6400 * function: is_contract_info_ok
6401 *
6402 * Description: check the source contract number is okay
6403 *
6404 *
6405 **************************************************************************/
6406 procedure is_contract_doc_info_ok(itemtype IN VARCHAR2,
6407 itemkey IN VARCHAR2,
6408 actid IN NUMBER,
6409 funcmode IN VARCHAR2,
6410 resultout OUT NOCOPY VARCHAR2 ) is
6411
6412 x_source_doc_type_code varchar2(25);
6413 x_source_doc_po_header_id number;
6414 x_source_doc_line_num number;
6415 x_progress varchar2(300) := '000';
6416 x_contract_id_valid number;
6417 x_contract_currency_code varchar2(25);
6418 x_source_currency_code PO_REQUISITION_LINES_ALL.currency_code%TYPE;
6419 l_vendor_site_id PO_HEADERS_ALL.vendor_site_id%TYPE; -- <GC FPJ>
6420 l_base_currency PO_HEADERS_ALL.currency_code%TYPE;
6421 begin
6422
6423 /* When the source doc and source line was put onto the req line
6424 * it was all validated to make sure it was ok.
6425 * Ie. docs were within effectivity dates, not canelled or closed etc.
6426 * So not doing the check here again.
6427 * We just need to make sure the source_doc_type, source_doc and
6428 * source_line have been populated.
6429 * here it will just validate the contract docuement type.
6430 */
6431
6432 x_source_doc_type_code := po_wf_util_pkg.GetItemAttrText
6433 (itemtype => itemtype,
6434 itemkey => itemkey,
6435 aname => 'SOURCE_DOCUMENT_TYPE_CODE');
6436
6437 x_source_doc_po_header_id := po_wf_util_pkg.GetItemAttrNumber
6438 (itemtype => itemtype,
6442 x_source_doc_line_num := po_wf_util_pkg.GetItemAttrNumber
6439 itemkey => itemkey,
6440 aname => 'SOURCE_DOCUMENT_ID');
6441
6443 (itemtype => itemtype,
6444 itemkey => itemkey,
6445 aname => 'SOURCE_DOCUMENT_LINE_NUM');
6446 x_source_currency_code := po_wf_util_pkg.GetItemAttrText
6447 (itemtype => itemtype,
6448 itemkey => itemkey,
6449 aname => 'CURRENCY_CODE');
6450
6451 -- <GC FPJ START>
6452 l_vendor_site_id := po_wf_util_pkg.GetItemAttrNumber
6453 (itemtype => itemtype,
6454 itemkey => itemkey,
6455 aname => 'SUGGESTED_VENDOR_SITE_ID');
6456 -- <GC FPJ END>
6457
6458 if ((x_source_doc_line_num is NULL) and (x_source_doc_type_code = 'CONTRACT') and x_source_doc_po_header_id is not NULL) then
6459
6460 -- validate the contract reference is active, bug 2076945
6461
6462 begin
6463
6464 x_progress := '001';
6465
6466 -- <GC FPJ START>
6467
6468 -- SQL What: Validate the contract ref. If it is a GC, make sure
6469 -- that it is still enabled for purchasing against the suggested
6470 -- vendor site
6471 -- SQL Why: The reference cannot be used if it is not valid
6472
6473 select POH.po_header_id, poh.currency_code
6474 into x_contract_id_valid, x_contract_currency_code
6475 from po_headers_all POH -- <GC FPJ>: Use ALL table
6476 where
6477 POH.po_header_id = x_source_doc_po_header_id
6478 and POH.type_lookup_code = 'CONTRACT'
6479 and nvl(POH.cancel_flag,'N') = 'N'
6480 and TRUNC(sysdate) between nvl(TRUNC(start_date), sysdate - 1)
6481 and nvl(TRUNC(end_date), sysdate + 1)
6482 and POH.authorization_status = 'APPROVED'
6483 and nvl(POH.closed_code,'OPEN') = 'OPEN'
6484 AND NVL(POH.frozen_flag, 'N') = 'N'
6485 AND (NVL(POH.global_agreement_flag, 'N') = 'N'
6486 OR EXISTS (SELECT 1
6487 FROM po_ga_org_assignments PGOA,
6488 po_system_parameters PSP
6489 WHERE PGOA.po_header_id = POH.po_header_id
6490 AND PGOA.organization_id = PSP.org_id
6491 AND PGOA.vendor_site_id = l_vendor_site_id
6492 AND PGOA.enabled_flag = 'Y'));
6493 -- <GC FPJ END>
6494
6495 exception
6496
6497 when others then
6498 x_contract_id_valid := -1;
6499
6500 end;
6501
6502 if (x_contract_id_valid = -1 ) then
6503
6504 resultout := wf_engine.eng_completed || ':' || 'N';
6505
6506 elsif (x_contract_id_valid = x_source_doc_po_header_id) then
6507 -- bug 3079146
6508 -- if currency didn't match, don't create po
6509 -- so set the flag to true so that po won't be created.
6510 l_base_currency := PO_CORE_S2.get_base_currency;
6511
6512 if ((x_source_currency_code is not null and x_contract_currency_code = x_source_currency_code) or (x_source_currency_code is null and x_contract_currency_code = l_base_currency)) then
6513
6514 resultout := wf_engine.eng_completed || ':' || 'Y';
6515
6516 else
6517 resultout := wf_engine.eng_completed || ':' || 'N';
6518 po_wf_util_pkg.SetItemAttrText (itemtype => itemtype,
6519 itemkey => itemkey,
6520 aname => 'CONTRACT_REQUIRED_FLAG',
6521 avalue => 'Y');
6522 end if;
6523
6524 else
6525
6526 resultout := wf_engine.eng_completed || ':' || 'N';
6527
6528 end if;
6529
6530 x_progress:= '10: is_source_doc_info_ok: result = ' || resultout;
6531 IF (g_po_wf_debug = 'Y') THEN
6532 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
6533 END IF;
6534
6535 po_wf_util_pkg.SetItemAttrNumber (itemtype => itemtype,
6536 itemkey => itemkey,
6537 aname => 'CONTRACT_ID',
6538 avalue => x_source_doc_po_header_id);
6539
6540 -- <GC FPJ START>
6541 -- Since the ref is a contract and is stored in attr CONTRACT_ID,
6542 -- null out the reference in SOURCE_DOCUMENT_ID
6543
6544 po_wf_util_pkg.SetItemAttrNumber (itemtype => itemtype,
6545 itemkey => itemkey,
6546 aname => 'SOURCE_DOCUMENT_ID',
6547 avalue => NULL);
6548
6549 -- <GC FPJ END>
6550 else
6551 resultout := wf_engine.eng_completed || ':' || 'N';
6552
6553 x_progress:= '20: is_contract_info_ok: result = N';
6554 IF (g_po_wf_debug = 'Y') THEN
6555 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
6556 END IF;
6557
6558 end if;
6559
6560 exception
6561 when others then
6562 wf_core.context('po_autocreate_doc','is_contract_doc_info_ok',x_progress);
6563 raise;
6564
6568 *
6565 end is_contract_doc_info_ok;
6566
6567 /***************************************************************************
6569 * function: should_nctlog_src_frm_contract
6570 *
6571 * Description: check the workflow options on whether to include the
6572 * non_catalog_request in the autosource
6573 *
6574 *
6575 **************************************************************************/
6576
6577 procedure should_nctlog_src_frm_contract(itemtype IN VARCHAR2,
6578 itemkey IN VARCHAR2,
6579 actid IN NUMBER,
6580 funcmode IN VARCHAR2,
6581 resultout OUT NOCOPY VARCHAR2 ) is
6582
6583 x_incl_non_ctlg_req_flag varchar2(1);
6584
6585 x_progress varchar2(300) := '000';
6586
6587 begin
6588
6589
6590 x_incl_non_ctlg_req_flag := po_wf_util_pkg.GetItemAttrText
6591 (itemtype => itemtype,
6592 itemkey => itemkey,
6593 aname => 'INCLUDE_NON_CATALOG_REQ_FLAG');
6594
6595
6596 if nvl(x_incl_non_ctlg_req_flag, 'N') <> 'Y' then
6597
6598 resultout := wf_engine.eng_completed || ':' || 'N';
6599
6600 return;
6601 else
6602 resultout:= wf_engine.eng_completed || ':' || 'Y';
6603
6604 return;
6605
6606 end if;
6607
6608 exception
6609
6610 when others then
6611 wf_core.context('po_autocreate_doc','should_nctlog_src_frm_cntrct',x_progress);
6612 raise;
6613
6614 end should_nctlog_src_frm_contract;
6615
6616 /* Private Procedure/Functions */
6617
6618 FUNCTION valid_contact(p_vendor_site_id number, p_vendor_contact_id number) RETURN BOOLEAN
6619 is
6620 x_count number;
6621 begin
6622 if (p_vendor_site_id is null or p_vendor_contact_id is null) then
6623 return false;
6624 else
6625 -- check if contact on req. lines is valid
6626 select count(*) into x_count
6627 from po_vendor_contacts
6628 where vendor_site_id = p_vendor_site_id
6629 and vendor_contact_id = p_vendor_contact_id
6630 and nvl(inactive_date, sysdate+1) > sysdate;
6631
6632 if (x_count > 0) then
6633 return true;
6634 else
6635 return false;
6636 end if;
6637 end if;
6638 end;
6639
6640 FUNCTION get_contact_id(p_contact_name varchar2, p_vendor_site_id number) RETURN NUMBER
6641 IS
6642 x_first_name varchar2(60);
6643 x_last_name varchar2(60);
6644 x_comma_pos number;
6645 x_contact_id number := null;
6646 BEGIN
6647
6648 begin
6649 select max(vendor_contact_id)
6650 into x_contact_id
6651 from po_supplier_contacts_val_v
6652 where vendor_site_id = p_vendor_site_id
6653 and contact = p_contact_name;
6654 exception
6655 when others then
6656 x_contact_id := null;
6657 end;
6658
6659 return x_contact_id;
6660 END;
6661
6662 /***************************************************************************
6663 *
6664 * Procedure: is_src_doc_ga_frm_other_ou
6665 *
6666 * Description: Checks if the source doc is a GA from another OU
6667 * Added for global Agreements project in FPI (FPI GA)
6668 *
6669 **************************************************************************/
6670 procedure is_src_doc_ga_frm_other_ou (itemtype IN VARCHAR2,
6671 itemkey IN VARCHAR2,
6672 actid IN NUMBER,
6673 funcmode IN VARCHAR2,
6674 resultout OUT NOCOPY VARCHAR2 ) is
6675
6676 x_org_id number;
6677 x_owning_org_id number;
6678 x_progress varchar2(300);
6679 x_ga_flag varchar2(1);
6680 x_source_doc_po_header_id number;
6681
6682 begin
6683
6684 /* Set org context */
6685 x_org_id := po_wf_util_pkg.GetItemAttrNumber (itemtype => itemtype,
6686 itemkey => itemkey,
6687 aname => 'ORG_ID');
6688
6689 po_moac_utils_pvt.set_org_context(x_org_id); --<R12 MOAC>
6690
6691 x_source_doc_po_header_id := po_wf_util_pkg.GetItemAttrNumber
6692 (itemtype => itemtype,
6693 itemkey => itemkey,
6694 aname => 'SOURCE_DOCUMENT_ID');
6695
6696 x_ga_flag := po_wf_util_pkg.GetItemAttrText
6697 (itemtype => itemtype,
6698 itemkey => itemkey,
6699 aname => 'SOURCE_DOC_GA_FLAG');
6700
6701 /* get the owning org of the source document */
6702
6703 if x_source_doc_po_header_id is not null then
6704 select org_id
6705 into x_owning_org_id
6706 from po_headers_all
6707 where po_header_id = x_source_doc_po_header_id;
6708 end if;
6709
6710 if nvl(x_ga_flag,'N') = 'Y' and
6711 x_owning_org_id <> x_org_id then
6712
6713 x_progress := '10: is_src_doc_ga_frm_other_ou: result = Y';
6714 resultout := wf_engine.eng_completed || ':' || 'Y';
6715 else
6719
6716 x_progress := '20: is_src_doc_ga_frm_other_ou: result = N';
6717 resultout := wf_engine.eng_completed || ':' || 'N';
6718 end if;
6720 IF (g_po_wf_debug = 'Y') THEN
6721 po_wf_debug_pkg.insert_debug(Itemtype,Itemkey,x_progress);
6722 END IF;
6723
6724 exception
6725 when others then
6726 wf_core.context('po_autocreate_doc','is_src_doc_ga_other_ou',x_progress);
6727 raise;
6728 end is_src_doc_ga_frm_other_ou;
6729
6730
6731 /*****************************************************************************/
6732
6733 --<Bug 2745549 mbhargav START>
6734 --Checks whether the referenced document is not cancelled or finally closed
6735 PROCEDURE is_ga_still_valid(p_ga_po_header_id IN NUMBER,
6736 x_ref_is_valid OUT NOCOPY VARCHAR2) IS
6737
6738 BEGIN
6739 x_ref_is_valid := 'N';
6740
6741 --Check the referenced GA for cancel/finally closed status
6742 select 'Y'
6743 into x_ref_is_valid
6744 from po_headers_all poh
6745 where poh.po_header_id = p_ga_po_header_id and
6746 nvl(poh.cancel_flag, 'N') = 'N' and
6747 nvl(poh.closed_code, 'OPEN') <> 'FINALLY CLOSED';
6748
6749 EXCEPTION
6750 WHEN OTHERS THEN
6751 x_ref_is_valid := 'N';
6752 END;
6753 --<Bug 2745549 mbhargav END>
6754
6755
6756 -- bug2821542
6757 -- validate_buyer is created to make sure that the derived buyer
6758 -- is a valid buyer
6759
6760 /**
6761 * Private Procedure: validate_buyer
6762 * Requires: N/A
6763 * Modifies: N/A
6764 * Effects: Validates that p_agent_id is a valid buyer
6765 * Retunrs:
6766 * x_result: FND_API.G_TRUE if validation suceeds
6767 * FND_API.G_FALSE if validation fails
6768 */
6769
6770 PROCEDURE validate_buyer (p_agent_id IN NUMBER,
6771 x_result OUT NOCOPY VARCHAR2) IS
6772
6773 l_result VARCHAR2(1);
6774
6775 l_progress VARCHAR2(300);
6776 BEGIN
6777
6778 l_progress := '10: validate buyer';
6779
6780 SELECT 'Y'
6781 INTO l_result
6782 FROM po_buyers_val_v
6783 WHERE employee_id = p_agent_id;
6784
6785 x_result := FND_API.G_TRUE;
6786 EXCEPTION
6787 WHEN NO_DATA_FOUND THEN
6788 x_result := FND_API.G_FALSE;
6789 WHEN OTHERS THEN
6790 x_result := FND_API.G_FALSE;
6791 WF_CORE.context('po_autocreate_doc','validate_buyer',l_progress);
6792 raise;
6793 END validate_buyer;
6794
6795 --<Shared Proc FPJ START>
6796
6797 ----------------------------------------------------------------
6798 --Start of Comments
6799 --Name: buyer_on_src_doc_ok
6800 --Pre-reqs:
6801 -- None
6802 --Modifies:
6803 -- None
6804 --Locks:
6805 -- None
6806 --Function:
6807 -- Checks the buyer on source document.
6808 --Parameters:
6809 --IN:
6810 --itemtype
6811 -- internal name for the item type
6812 --itemkey
6813 -- primary key generated by the workflow for the item type
6814 --actid
6815 -- id number of the activity from which this procedure is called
6816 --funcmode
6817 -- execution mode of the function activity (RUN or CANCEL)
6818 --OUT:
6819 --resultout
6820 -- result returned to the workflow
6821 -- YES if 1) The buyer on the global agreement is in the same
6822 -- business group as the requesting operating unit OR
6823 -- 2) The HR:Cross Business Group profile is set to 'Y'
6824 -- NO otherwise
6825 --Notes:
6826 -- Added for Shared Procurement Services Project in FPJ
6827 --Testing:
6828 -- None
6829 --End of Comments
6830 ----------------------------------------------------------------
6831
6832
6833 PROCEDURE buyer_on_src_doc_ok (
6834 itemtype IN VARCHAR2,
6835 itemkey IN VARCHAR2,
6836 actid IN NUMBER,
6837 funcmode IN VARCHAR2,
6838 resultout OUT NOCOPY VARCHAR2
6839 ) IS
6840 x_org_id PO_HEADERS_ALL.org_id%TYPE;
6841 x_progress VARCHAR2(300);
6842 x_source_doc_po_header_id PO_HEADERS_ALL.po_header_id%TYPE;
6843 x_source_doc_type_code PO_HEADERS_ALL.from_type_lookup_code%TYPE;
6844 x_ga_flag PO_HEADERS_ALL.global_agreement_flag%TYPE;
6845 l_return_status VARCHAR2(1) := 'N';
6846
6847 BEGIN
6848 --Set org context
6849 x_org_id := po_wf_util_pkg.GetItemAttrNumber (itemtype => itemtype,
6850 itemkey => itemkey,
6851 aname => 'ORG_ID');
6852
6853 IF x_org_id IS NOT NULL THEN
6854 po_moac_utils_pvt.set_org_context(x_org_id); --<R12 MOAC>
6855 END IF;
6856
6857 IF (NVL(hr_general.get_xbg_profile, 'N') = 'Y') THEN
6858 x_progress := '10: buyer_on_src_doc_ok: result = Y';
6859 resultout := wf_engine.eng_completed || ':' || 'Y';
6860 ELSE --cross business group profile is 'N'
6861 x_source_doc_po_header_id := po_wf_util_pkg.GetItemAttrNumber
6862 (itemtype => itemtype,
6863 itemkey => itemkey,
6864 aname => 'SOURCE_DOCUMENT_ID');
6868 itemkey => itemkey,
6865
6866 x_source_doc_type_code := po_wf_util_pkg.GetItemAttrText
6867 (itemtype => itemtype,
6869 aname => 'SOURCE_DOCUMENT_TYPE_CODE');
6870
6871 x_ga_flag := po_wf_util_pkg.GetItemAttrText
6872 (itemtype => itemtype,
6873 itemkey => itemkey,
6874 aname => 'SOURCE_DOC_GA_FLAG');
6875
6876 IF ((x_source_doc_type_code = 'QUOTATION') OR
6877 (x_source_doc_type_code = 'BLANKET' AND NVL(x_ga_flag, 'N') = 'N')) THEN
6878
6879 x_progress := '20: buyer_on_src_doc_ok: result = Y';
6880 resultout := wf_engine.eng_completed || ':' || 'Y';
6881
6882 ELSIF x_source_doc_type_code = 'BLANKET' AND NVL(x_ga_flag, 'N') = 'Y' THEN
6883
6884 BEGIN
6885
6886 SELECT 'Y'
6887 INTO l_return_status
6888 FROM po_headers_all poh,
6889 per_people_f ppf,
6890 financials_system_parameters fsp
6891 WHERE poh.agent_id = ppf.person_id
6892 AND ppf.business_group_id = fsp.business_group_id
6893 AND TRUNC(SYSDATE) BETWEEN ppf.effective_start_date
6894 AND NVL (ppf.effective_end_date, SYSDATE + 1)
6895 AND poh.po_header_id = x_source_doc_po_header_id;
6896
6897 EXCEPTION
6898 WHEN NO_DATA_FOUND THEN
6899 l_return_status := 'N';
6900 END;
6901
6902 IF l_return_status = 'Y' THEN
6903 x_progress := '30: buyer_on_src_doc_ok: result = Y';
6904 resultout := wf_engine.eng_completed || ':' || 'Y';
6905 ELSE
6906 x_progress := '40: buyer_on_src_doc_ok: result = N';
6907 resultout := wf_engine.eng_completed || ':' || 'N';
6908 END IF;
6909
6910 ELSE
6911 x_progress := '50: buyer_on_src_doc_ok: result = N';
6912 resultout := wf_engine.eng_completed || ':' || 'N';
6913
6914 END IF; --source doc check
6915 END IF; --check profile option
6916
6917 IF (g_po_wf_debug = 'Y') THEN
6918 po_wf_debug_pkg.insert_debug (itemtype, itemkey, x_progress);
6919 END IF;
6920
6921 EXCEPTION
6922 WHEN OTHERS THEN
6923 wf_core.CONTEXT ('po_autocreate_doc', 'buyer_on_src_doc_ok',
6924 x_progress);
6925 RAISE;
6926 END buyer_on_src_doc_ok;
6927
6928
6929 ----------------------------------------------------------------
6930 --Start of Comments
6931 --Name: buyer_on_contract_ok
6932 --Pre-reqs:
6933 -- None
6934 --Modifies:
6935 -- None
6936 --Locks:
6937 -- None
6938 --Function:
6939 -- Checks the buyer on contract.
6940 --Parameters:
6941 --IN:
6942 --itemtype
6943 -- internal name for the item type
6944 --itemkey
6945 -- primary key generated by the workflow for the item type
6946 --actid
6947 -- id number of the activity from which this procedure is called
6948 --funcmode
6949 -- execution mode of the function activity (RUN or CANCEL)
6950 --OUT:
6951 --resultout
6952 -- result returned to the workflow
6953 -- YES if 1) The buyer on the global contract is in the same
6954 -- business group as the requesting operating unit OR
6955 -- 2) The HR:Cross Business Group profile is set to 'Y'
6956 -- NO otherwise
6957 --Notes:
6958 -- Added for Shared Procurement Services Project in FPJ
6959 --Testing:
6960 -- None
6961 --End of Comments
6962 ----------------------------------------------------------------
6963
6964
6965 PROCEDURE buyer_on_contract_ok (
6966 itemtype IN VARCHAR2,
6967 itemkey IN VARCHAR2,
6968 actid IN NUMBER,
6969 funcmode IN VARCHAR2,
6970 resultout OUT NOCOPY VARCHAR2
6971 ) IS
6972 x_org_id PO_HEADERS_ALL.org_id%TYPE;
6973 x_progress VARCHAR2(300);
6974 x_contract_id NUMBER;
6975 l_return_status VARCHAR2(1) := 'N';
6976
6977 BEGIN
6978 --Set org context
6979 x_org_id := po_wf_util_pkg.GetItemAttrNumber (itemtype => itemtype,
6980 itemkey => itemkey,
6981 aname => 'ORG_ID');
6982
6983 IF x_org_id IS NOT NULL THEN
6984 po_moac_utils_pvt.set_org_context(x_org_id); --<R12 MOAC>
6985 END IF;
6986
6987 IF (NVL(hr_general.get_xbg_profile, 'N') = 'Y') THEN
6988 x_progress := '10: buyer_on_contract_ok: result = Y';
6989 resultout := wf_engine.eng_completed || ':' || 'Y';
6990 ELSE -- cross business group profile is 'N'
6991 x_contract_id := po_wf_util_pkg.GetItemAttrNumber
6992 (itemtype => itemtype,
6993 itemkey => itemkey,
6994 aname => 'CONTRACT_ID');
6995
6996 IF x_contract_id IS NOT NULL THEN
6997 BEGIN
6998 SELECT 'Y'
6999 INTO l_return_status
7000 FROM po_headers_all poh,
7001 per_people_f ppf,
7005 AND TRUNC(SYSDATE) BETWEEN ppf.effective_start_date
7002 financials_system_parameters fsp
7003 WHERE poh.agent_id = ppf.person_id
7004 AND ppf.business_group_id = fsp.business_group_id
7006 AND NVL(ppf.effective_end_date, SYSDATE + 1)
7007 AND poh.po_header_id = x_contract_id;
7008 EXCEPTION
7009 WHEN NO_DATA_FOUND THEN
7010 l_return_status := 'N';
7011 END;
7012
7013 IF l_return_status = 'Y' THEN
7014 x_progress := '20: buyer_on_contract_ok: result = Y';
7015 resultout := wf_engine.eng_completed || ':' || 'Y';
7016 ELSE
7017 x_progress := '30: buyer_on_contract_ok: result = N';
7018 resultout := wf_engine.eng_completed || ':' || 'N';
7019
7020 purge_expense_lines(itemtype, itemkey); -- <SERVICES FPJ>
7021
7022 END IF;
7023
7024 ELSE --contract id is null
7025
7026 x_progress := '40: buyer_on_contract_ok: result = N';
7027 resultout := wf_engine.eng_completed || ':' || 'N';
7028
7029 purge_expense_lines(itemtype, itemkey); -- <SERVICES FPJ>
7030
7031 END IF; --contract_id check
7032 END IF; --check profile option
7033
7034 IF (g_po_wf_debug = 'Y') THEN
7035 po_wf_debug_pkg.insert_debug (itemtype, itemkey, x_progress);
7036 END IF;
7037
7038 EXCEPTION
7039 WHEN OTHERS THEN
7040 wf_core.CONTEXT('po_autocreate_doc', 'buyer_on_contract_ok', x_progress);
7041 RAISE;
7042 END buyer_on_contract_ok;
7043
7044
7045 ----------------------------------------------------------------
7046 --Start of Comments
7047 --Name: purchasing_ou_check
7048 --Pre-reqs:
7049 -- None
7050 --Modifies:
7051 -- None
7052 --Locks:
7053 -- None
7054 --Function:
7055 -- Checks the purchasing org of the Requisition.
7056 --Parameters:
7057 --IN:
7058 --itemtype
7059 -- internal name for the item type
7060 --itemkey
7061 -- primary key generated by the workflow for the item type
7062 --actid
7063 -- id number of the activity from which this procedure is called
7064 --funcmode
7065 -- execution mode of the function activity (RUN or CANCEL)
7066 --OUT:
7067 --resultout
7068 -- result returned to the workflow
7069 -- YES if the operating unit associated with vendor_site_id
7070 -- is different from requesting operating unit.
7071 -- NO otherwise
7072 --Notes:
7073 -- Added for Shared Procurement Services Project in FPJ
7074 --Testing:
7075 -- None
7076 --End of Comments
7077 ----------------------------------------------------------------
7078
7079
7080 PROCEDURE purchasing_ou_check (
7081 itemtype IN VARCHAR2,
7082 itemkey IN VARCHAR2,
7083 actid IN NUMBER,
7084 funcmode IN VARCHAR2,
7085 resultout OUT NOCOPY VARCHAR2
7086 ) IS
7087
7088 l_org_id PO_HEADERS_ALL.org_id%TYPE;
7089 l_purchasing_org_id PO_HEADERS_ALL.org_id%TYPE;
7090 x_progress VARCHAR2(300);
7091
7092 BEGIN
7093
7094 l_org_id := po_wf_util_pkg.GetItemAttrNumber
7095 (itemtype => itemtype,
7096 itemkey => itemkey,
7097 aname => 'ORG_ID');
7098
7099 l_purchasing_org_id := po_wf_util_pkg.GetItemAttrNumber
7100 (itemtype => itemtype,
7101 itemkey => itemkey,
7102 aname => 'PURCHASING_ORG_ID');
7103
7104 IF l_org_id = l_purchasing_org_id THEN
7105 x_progress := '10: purchasing_ou_check: result = N';
7106 resultout := wf_engine.eng_completed || ':' || 'N';
7107 ELSE
7108 x_progress := '20: purchasing_ou_check: result = Y';
7109 resultout := wf_engine.eng_completed || ':' || 'Y';
7110 END IF;
7111
7112 IF (g_po_wf_debug = 'Y') THEN
7113 po_wf_debug_pkg.insert_debug (itemtype, itemkey, x_progress);
7114 END IF;
7115
7116 EXCEPTION
7117
7118 WHEN OTHERS THEN
7119 wf_core.CONTEXT ('po_autocreate_doc', 'purchasing_ou_check', x_progress);
7120 RAISE;
7121
7122 END purchasing_ou_check;
7123
7124
7125 ----------------------------------------------------------------
7126 --Start of Comments
7127 --Name: ok_to_create_in_diff_ou
7128 --Pre-reqs:
7129 -- None
7130 --Modifies:
7131 -- None
7132 --Locks:
7133 -- None
7134 --Function:
7135 -- This procedure calls an API which does the following checks:
7136 -- 1) Transaction Flow existance
7137 -- 2) PA Exclusion: Expense Dest with Project and Task Information
7138 -- 3) VMI Enabled Check
7139 -- 4) Consigned Relationship exists
7140 -- 5) Check if we need to do following checks:
7141 -- a) Item validation and Revision checks
7142 -- b) Encumbrance check
7143 -- c) Destination Inv Org OPM enabled check
7144 --Parameters:
7145 --IN:
7146 --itemtype
7147 -- internal name for the item type
7148 --itemkey
7149 -- primary key generated by the workflow for the item type
7150 --actid
7151 -- id number of the activity from which this procedure is called
7152 --funcmode
7156 -- result returned to the workflow
7153 -- execution mode of the function activity (RUN or CANCEL)
7154 --OUT:
7155 --resultout
7157 -- YES if it is ok to create document in different operating unit
7158 -- NO otherwise
7159 --Notes:
7160 -- Added for Shared Procurement Services Project in FPJ
7161 --Testing:
7162 -- None
7163 --End of Comments
7164 ----------------------------------------------------------------
7165
7166
7167 PROCEDURE ok_to_create_in_diff_ou (
7168 itemtype IN VARCHAR2,
7169 itemkey IN VARCHAR2,
7170 actid IN NUMBER,
7171 funcmode IN VARCHAR2,
7172 resultout OUT NOCOPY VARCHAR2
7173 )
7174 IS
7175 l_requesting_org_id NUMBER;
7176 l_purchasing_org_id NUMBER;
7177 l_dest_org_id NUMBER;
7178 l_req_line_id NUMBER;
7179 l_source_doc_id NUMBER;
7180 l_item_id NUMBER;
7181 l_cons_from_supp_flag VARCHAR2(1);
7182 x_progress VARCHAR2 (300);
7183 l_suggested_vendor_id PO_VENDORS.vendor_id%TYPE;
7184 l_suggested_vendor_site_id PO_VENDOR_SITES_ALL.vendor_site_id%TYPE;
7185
7186 l_return_status VARCHAR2(1);
7187 l_msg_count NUMBER;
7188 l_msg_data VARCHAR2(2000);
7189 l_error_msg_name VARCHAR2(30);
7190 l_vmi_flag po_asl_attributes.enable_vmi_flag%TYPE;
7191 l_cons_from_supplier_flag VARCHAR2(1);
7192 l_last_billing_date po_asl_attributes.last_billing_date%TYPE;
7193 l_cons_billing_cycle po_asl_attributes.consigned_billing_cycle%TYPE;
7194 BEGIN
7195
7196 l_req_line_id :=
7197 po_wf_util_pkg.getitemattrnumber (itemtype => itemtype,
7198 itemkey => itemkey,
7199 aname => 'REQ_LINE_ID'
7200 );
7201 l_item_id :=
7202 po_wf_util_pkg.getitemattrnumber (itemtype => itemtype,
7203 itemkey => itemkey,
7204 aname => 'ITEM_ID'
7205 );
7206
7207 l_source_doc_id :=
7208 po_wf_util_pkg.getitemattrnumber (itemtype => itemtype,
7209 itemkey => itemkey,
7210 aname => 'SOURCE_DOCUMENT_ID'
7211 );
7212
7213 l_requesting_org_id :=
7214 po_wf_util_pkg.getitemattrnumber (itemtype => itemtype,
7215 itemkey => itemkey,
7216 aname => 'ORG_ID'
7217 );
7218 l_purchasing_org_id :=
7219 po_wf_util_pkg.getitemattrnumber (itemtype => itemtype,
7220 itemkey => itemkey,
7221 aname => 'PURCHASING_ORG_ID'
7222 );
7223
7224 l_suggested_vendor_id :=
7225 po_wf_util_pkg.getitemattrnumber (itemtype => itemtype,
7226 itemkey => itemkey,
7227 aname => 'SUGGESTED_VENDOR_ID'
7228 );
7229
7230 l_suggested_vendor_site_id :=
7231 po_wf_util_pkg.getitemattrnumber (itemtype => itemtype,
7232 itemkey => itemkey,
7233 aname => 'SUGGESTED_VENDOR_SITE_ID'
7234 );
7235
7236
7237 --SQL WHAT:Get the destination inventory org from the req line
7238 --SQL WHY: This information is needed for passing dest inv org
7239 -- to get_asl_attributes
7240 SELECT prl.destination_organization_id
7241 INTO l_dest_org_id
7242 FROM po_requisition_lines_all prl
7243 WHERE requisition_line_id = l_req_line_id;
7244
7245 PO_THIRD_PARTY_STOCK_GRP.Get_Asl_Attributes(
7246 p_api_version => 1.0
7247 , p_init_msg_list => NULL
7248 , x_return_status => l_return_status
7249 , x_msg_count => l_msg_count
7250 , x_msg_data => l_msg_data
7251 , p_inventory_item_id => l_item_id
7252 , p_vendor_id => l_suggested_vendor_id
7253 , p_vendor_site_id => l_suggested_vendor_site_id
7254 , p_using_organization_id => l_dest_org_id
7255 , x_consigned_from_supplier_flag => l_cons_from_supp_flag
7256 , x_enable_vmi_flag => l_vmi_flag
7257 , x_last_billing_date => l_last_billing_date
7258 , x_consigned_billing_cycle => l_cons_billing_cycle
7259 );
7260
7261 PO_SHARED_PROC_PVT.validate_cross_ou_purchasing(
7262 p_api_version =>1.0,
7263 p_requisition_line_id =>l_req_line_id,
7264 p_requesting_org_id =>l_requesting_org_id,
7265 p_purchasing_org_id =>l_purchasing_org_id,
7266 p_item_id =>l_item_id,
7267 p_source_doc_id =>l_source_doc_id,
7268 p_vmi_flag =>l_vmi_flag,
7269 p_cons_from_supp_flag =>l_cons_from_supp_flag,
7270 x_return_status =>l_return_status,
7274 IF l_return_status = FND_API.G_RET_STS_SUCCESS
7271 x_error_msg_name =>l_error_msg_name
7272 );
7273
7275 THEN
7276 x_progress := '10:ok_to_create_in_diff_ou: result = Y';
7277 resultout := wf_engine.eng_completed || ':' || 'Y';
7278 ELSE
7279 x_progress := '20: ok_to_create_in_diff_ou: result = N'
7280 || ' error msg: ' || l_error_msg_name;
7281 resultout := wf_engine.eng_completed || ':' || 'N';
7282 END IF;
7283
7284 IF (g_po_wf_debug = 'Y')
7285 THEN
7286 po_wf_debug_pkg.insert_debug (itemtype, itemkey, x_progress);
7287 END IF;
7288 EXCEPTION
7289 WHEN OTHERS
7290 THEN
7291 wf_core.CONTEXT ('po_autocreate_doc',
7292 'ok_to_create_in_diff_ou',
7293 x_progress
7294 );
7295 RAISE;
7296 END ok_to_create_in_diff_ou;
7297
7298 ----------------------------------------------------------------
7299 --Start of Comments
7300 --Name: set_purchasing_org_id
7301 --Pre-reqs:
7302 -- None
7303 --Modifies:
7304 -- None
7305 --Locks:
7306 -- None
7307 --Function:
7308 -- Helper function to set the PURCHASING_ORG_ID workflow
7309 -- attribute.
7310 --Parameters:
7311 --IN:
7312 --itemtype
7313 -- internal name for the item type
7314 --itemkey
7315 -- primary key generated by the workflow for the item type
7316 --p_org_id
7317 -- org_id of the operating unit where the requisition in
7318 -- question was created
7319 --p_suggested_vendor_site_id
7320 -- id of the suggested vendor site for the requisition in
7321 -- question
7322 --Notes:
7323 -- Added for Shared Procurement Services Project in FPJ
7324 --Testing:
7325 -- None
7326 --End of Comments
7327 ----------------------------------------------------------------
7328
7329 PROCEDURE set_purchasing_org_id(
7330 itemtype IN VARCHAR2,
7331 itemkey IN VARCHAR2,
7332 p_org_id IN NUMBER,
7333 p_suggested_vendor_site_id IN NUMBER
7334 )
7335 IS
7336
7337 l_purchasing_org_id PO_HEADERS_ALL.org_id%TYPE;
7338 l_progress VARCHAR2(300);
7339
7340 BEGIN
7341
7342 --Get the purchasing_org_id
7343
7344 l_progress:= '10: set_purchasing_org_id: org_id = ' || to_char(p_org_id);
7345
7346 IF p_suggested_vendor_site_id IS NOT NULL THEN
7347 BEGIN
7348 SELECT org_id
7349 INTO l_purchasing_org_id
7350 FROM po_vendor_sites_all
7351 WHERE vendor_site_id = p_suggested_vendor_site_id;
7352 EXCEPTION
7353 WHEN NO_DATA_FOUND THEN
7354 l_purchasing_org_id := p_org_id;
7355 END;
7356 ELSE
7357 --suggested_vendor_site_id is null
7358 l_purchasing_org_id := p_org_id;
7359 END IF;
7360
7361
7362 l_progress:= '20: set_purchasing_org_id: org_id = ' || to_char(p_org_id)
7363 || ' purchasing_org_id = ' || to_char(l_purchasing_org_id);
7364
7365
7366 --Set purchasing_org_id workflow attribute
7367 po_wf_util_pkg.SetItemAttrNumber (itemtype => itemtype,
7368 itemkey => itemkey,
7369 aname => 'PURCHASING_ORG_ID',
7370 avalue => l_purchasing_org_id);
7371
7372 EXCEPTION
7373
7374 WHEN OTHERS THEN
7375 wf_core.context('po_autocreate_doc', 'set_purchasing_org_id', l_progress);
7376 RAISE;
7377
7378 END set_purchasing_org_id;
7379
7380
7381 --<Shared Proc FPJ END>
7382
7383
7384
7385 -- <SERVICES FPJ START>
7386
7387 -------------------------------------------------------------
7388 --Start of Comments
7389 --Name : is_expense_line
7390 --
7391 --Pre-reqs : IN parameters need to be passed in with valid values
7392 --
7393 --Modifies : None
7394 --
7395 --Locks : None
7396 --
7397 --Function : This procedure checks whether a given line is an
7398 -- expense line
7399 --
7400 --Parameter(s):
7401 --
7402 --IN : itemtype IN VARCHAR2,
7403 -- itemkey IN VARCHAR2,
7404 -- actid IN NUMBER,
7405 -- funcmode IN VARCHAR2,
7406 --
7407 --IN OUT: : None
7408 --
7409 --OUT : resultout OUT NOCOPY VARCHAR2
7410 --
7411 --Returns : resultout OUT NOCOPY VARCHAR2
7412 --
7413 --Notes : None
7414 --
7415 --Testing : None
7416 --
7417 --End of Comments
7418 -------------------------------------------------------------
7419
7420 PROCEDURE is_expense_line(itemtype IN VARCHAR2,
7421 itemkey IN VARCHAR2,
7422 actid IN NUMBER,
7423 funcmode IN VARCHAR2,
7424 resultout OUT NOCOPY VARCHAR2) IS
7425
7426 l_org_id number := null;
7427 l_labor_req_line_id number := null;
7428 l_progress varchar2(300) := null;
7429
7430 BEGIN
7431
7432 /* Set org context */
7433 l_progress := '000';
7434 l_org_id := po_wf_util_pkg.GetItemAttrNumber(itemtype => itemtype,
7435 itemkey => itemkey,
7439 /* Get the expense line grouping ID */
7436 aname => 'ORG_ID');
7437 po_moac_utils_pvt.set_org_context(l_org_id); --<R12 MOAC>
7438
7440 l_labor_req_line_id := po_wf_util_pkg.GetItemAttrNumber(
7441 itemtype => itemtype,
7442 itemkey => itemkey,
7443 aname => 'LABOR_REQ_LINE_ID');
7444
7445 IF (l_labor_req_line_id is not null) THEN
7446 resultout := wf_engine.eng_completed || ':' || 'Y';
7447
7448 l_progress:= '10: is_expense_line: result = Y';
7449
7450 IF (g_po_wf_debug = 'Y') THEN
7451 po_wf_debug_pkg.insert_debug(itemtype,
7452 itemkey,
7453 l_progress);
7454 END IF;
7455
7456 ELSE
7457 resultout := wf_engine.eng_completed || ':' || 'N';
7458
7459 l_progress:= '20: is_expense_line: result = N';
7460
7461 IF (g_po_wf_debug = 'Y') THEN
7462 po_wf_debug_pkg.insert_debug(itemtype,
7463 itemkey,
7464 l_progress);
7465 END IF;
7466 END IF;
7467
7468 EXCEPTION
7469 WHEN OTHERS THEN
7470 wf_core.context('po_autocreate_doc',
7471 'is_expense_line',
7472 l_progress);
7473 RAISE;
7474 END is_expense_line;
7475
7476
7477
7478 -------------------------------------------------------------
7479 --Start of Comments
7480 --Name : purge_expense_lines
7481 --
7482 --Pre-reqs : IN parameters need to be passed in with valid values
7483 --
7484 --Modifies : None
7485 --
7486 --Locks : None
7487 --
7488 --Function : This procedure deletes all expense lines associated with a
7489 -- Temp Labor Requisition line that fails validations.
7490 --
7491 --Parameter(s):
7492 --
7493 --IN : itemtype IN VARCHAR2,
7494 -- itemkey IN VARCHAR2
7495 --
7496 --IN OUT: : None
7497 --
7498 --OUT : None
7499 --
7500 --Returns : None
7501 --
7502 --Notes : None
7503 --
7504 --Testing : None
7505 --
7506 --End of Comments
7507 -------------------------------------------------------------
7508
7509 PROCEDURE purge_expense_lines(itemtype IN VARCHAR2,
7510 itemkey IN VARCHAR2) IS
7511
7512 l_req_line_id number := null;
7513 l_progress varchar2(300) := null;
7514
7515 BEGIN
7516
7517 l_progress := '000';
7518 -- Get the requisition line ID
7519 l_req_line_id := po_wf_util_pkg.GetItemAttrNumber(
7520 itemtype => itemtype,
7521 itemkey => itemkey,
7522 aname => 'REQ_LINE_ID');
7523
7524 l_progress := '010';
7525 -- Delete all expense lines from the temporary table
7526 DELETE FROM po_wf_candidate_req_lines_temp
7527 WHERE requisition_line_id = (
7528 SELECT requisition_line_id
7529 FROM po_requisition_lines
7530 WHERE labor_req_line_id = l_req_line_id);
7531
7532 EXCEPTION
7533 WHEN OTHERS THEN
7534 wf_core.context('po_autocreate_doc',
7535 'purge_expense_lines',
7536 l_progress);
7537 RAISE;
7538 END purge_expense_lines;
7539 -- <SERVICES FPJ END>
7540
7541 /***************************************************************************
7542 *
7543 * Procedure: temp_labor_item_check
7544 *
7545 * Description: Checks if this is a temp_labor req line (ie. no
7546 * item num)
7547 *
7548 **************************************************************************/
7549 procedure temp_labor_item_check (itemtype IN VARCHAR2,
7550 itemkey IN VARCHAR2,
7551 actid IN NUMBER,
7552 funcmode IN VARCHAR2,
7553 resultout OUT NOCOPY VARCHAR2 ) IS
7554 x_progress varchar2(300);
7555 x_catalog_type varchar2(30);
7556
7557 begin
7558
7559 x_catalog_type := po_wf_util_pkg.GetItemAttrText (itemtype => itemtype,
7560 itemkey => itemkey,
7561 aname => 'CATALOG_TYPE');
7562
7563 if nvl(x_catalog_type, 'CATALOG') <> 'TEMP_LABOR' then
7564
7565 resultout := wf_engine.eng_completed || ':' || 'N';
7566
7567 else
7568 resultout := wf_engine.eng_completed || ':' || 'Y';
7569
7570 x_progress:= '10: temp_labor_item_check: result = '|| resultout;
7571 IF (g_po_wf_debug = 'Y') THEN
7572 po_wf_debug_pkg.insert_debug(itemtype,itemkey,x_progress);
7573 END IF;
7574
7575 end if;
7576
7577
7578 exception
7579 when others then
7580 wf_core.context('po_autocreate_doc','temp_labor_item_check',x_progress);
7581 raise;
7582 end temp_labor_item_check;
7583
7587 *
7584 /***************************************************************************
7585 *
7586 * function: should_tmplbr_src_frm_contract
7588 * Description: check the workflow options on whether to include the
7589 * temp_labor_request in the autosource
7590 *
7591 *
7592 **************************************************************************/
7593
7594 procedure should_tmplbr_src_frm_contract(itemtype IN VARCHAR2,
7595 itemkey IN VARCHAR2,
7596 actid IN NUMBER,
7597 funcmode IN VARCHAR2,
7598 resultout OUT NOCOPY VARCHAR2 ) IS
7599 x_incl_temp_labor_flag varchar2(1);
7600 x_progress varchar2(300) := '000';
7601
7602 begin
7603
7604 x_incl_temp_labor_flag := po_wf_util_pkg.GetItemAttrText
7605 (itemtype => itemtype,
7606 itemkey => itemkey,
7607 aname => 'INCLUDE_TEMP_LABOR_FLAG');
7608
7609
7610 if nvl(x_incl_temp_labor_flag, 'N') <> 'Y' then
7611
7612 resultout := wf_engine.eng_completed || ':' || 'N';
7613
7614 return;
7615 else
7616 resultout:= wf_engine.eng_completed || ':' || 'Y';
7617
7618 return;
7619
7620 end if;
7621
7622 exception
7623
7624 when others then
7625 wf_core.context('po_autocreate_doc','should_tmplbr_src_frm_cntract',x_progress);
7626 raise;
7627
7628 end should_tmplbr_src_frm_contract;
7629
7630
7631
7632 END PO_AUTOCREATE_DOC;