1 PACKAGE BODY por_view_reqs_pkg AS
2 /* $Header: PORVRQSB.pls 120.19.12000000.2 2007/04/27 11:51:58 kkram ship $ */
3
4 -- Logging Static Variables
5 G_CURRENT_RUNTIME_LEVEL NUMBER;
6 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
7 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
8 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
9 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
10 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
11 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
12 G_MODULE_NAME CONSTANT VARCHAR2(30) := 'PO.PLSQL.POR_UTIL_PKG';
13
14 /**************************************************************************
15 * liwang, 06/15/2004
16 * This function should be obsoleted in release 11.5.10. Due to the dependency
17 * from view POR_APPROVAL_STATUS_LINES_V, an empty function body is kept here.
18 * This together with the view should be obsoleted in the next release.
19 * ******************************************SELECT pap.full_name, pap.email_address, ph.phone_number, wn.begin_date
20 FROM
21 wf_notifications wn,
22 wf_user_roles wlur,
23 fnd_user fnd,
24 po_requisition_headers_all prh,
25 per_phones ph,
26 per_all_people_f pap
27 WHERE
28 prh.requisition_header_id = 140331 AND
29 ph.parent_table(+) = 'PER_ALL_PEOPLE_F' AND
30 ph.parent_id (+) = fnd.employee_id AND
31 ph.phone_type(+) = 'W1' AND
32 wlur.user_name = fnd.user_name AND
33 pap.person_id = fnd.employee_id AND
34 fnd.employee_id = 57 AND
35 wn.recipient_role = wlur.role_name AND
36 wn.status = 'OPEN' AND
37 wn.message_type = prh.wf_item_type AND
38 wn.message_name IN ('PO_REQ_APPROVE',
39 'PO_REQ_REMINDER1',
40 'PO_REQ_APPROVE_WEB_MSG',
41 'PO_REQ_REMINDER2',
42 'PO_REQ_REMINDER1_WEB',
43 'PO_REQ_REMINDER2_WEB',
44 'PO_REQ_APPROVE_JRAD',
45 'PO_REQ_APPROVE_SIMPLE',
46 'PO_REQ_APPROVE_SIMPLE_JRAD',
47 'PO_REQ_REMINDER1_JRAD',
48 'PO_REQ_REMINDER2_JRAD')
49 AND effective_start_date < sysdate and effective_end_date > sysdate;*******************************/
50
51 function is_PlacedOnNG(req_header_id NUMBER) RETURN VARCHAR2 is
52 begin
53 return 'N';
54 end is_PlacedOnNG;
55
56 /**************************************************************************
57 * This function returns multiple_value or the full name of the requester *
58 **************************************************************************/
59 function get_requester(req_header_id IN NUMBER) RETURN VARCHAR2 IS
60 no_of_values NUMBER := 0;
61 value VARCHAR2(1000) := '';
62 begin
63 select count(distinct nvl(to_person_id,0))
64 into no_of_values
65 from po_requisition_lines_all
66 where requisition_header_id = req_header_id;
67
68 if (no_of_values > 1) then
69 return 'MULTIPLE_VALUE';
70 else
71 select full_name
72 into value
73 from
74 per_all_people_f hre,
75 po_requisition_lines_all prl
76 where
77 sysdate between hre.effective_start_date AND hre.effective_end_date AND
78 prl.to_person_id = hre.person_id AND
79 prl.requisition_header_id = req_header_id AND
80 rownum = 1;
81 return value;
82 end if;
83 end get_requester;
84
85 /**************************************************************************
86 * This function returns empty string or the deliver to address *
87 **************************************************************************/
88 function get_deliver_to(req_header_id IN NUMBER) RETURN VARCHAR2 is
89 no_of_values NUMBER := 0;
90 value VARCHAR2(1000) := '';
91 l_location_id NUMBER;
92 l_address_line_1 VARCHAR2(240);
93 l_address_line_2 VARCHAR2(240);
94 l_address_line_3 VARCHAR2(240);
95 l_territory_short_name VARCHAR2(80);
96 l_address_info VARCHAR2(240);
97 begin
98 select count(distinct nvl(deliver_to_location_id,0))
99 into no_of_values
100 from po_requisition_lines_all
101 where requisition_header_id = req_header_id;
102
103 if (no_of_values > 1) then
104 return '';
105 else
106 select count(*)
107 into no_of_values
108 from por_item_attribute_values
109 where requisition_header_id = req_header_id;
110
111 if(no_of_values > 0) then
112
113 select decode(hrtl.description,null,'',hrtl.description)
114 into value
115 from
116 hr_locations_all_tl hrtl,
117 po_requisition_lines_all prl
118 where
119 hrtl.location_id = prl.deliver_to_location_id AND
120 prl.requisition_header_id = req_header_id AND
121 hrtl.language = userenv('LANG') AND
122 rownum = 1;
123 return value;
124 else
125
126 select hrl.location_id
127 into l_location_id
128 from
129 hr_locations hrl,
130 po_requisition_lines_all prl
131 where
132 hrl.location_id = prl.deliver_to_location_id AND
133 prl.requisition_header_id = req_header_id AND
134 rownum = 1;
135
136
137 po_hr_location.get_address(l_location_id,
138 l_address_line_1,
139 l_address_line_2,
140 l_address_line_3,
141 l_territory_short_name,
142 l_address_info);
143
144 select decode(l_address_line_1,null,'',l_address_line_1||' ')||
145 decode(l_address_line_2,null,'',l_address_line_2||' ')||
146 decode(l_address_line_3,null,'',l_address_line_3||' ')||
147 l_address_info
148 into value
149 from dual;
150
151 return value;
152
153 end if;
154 end if;
155 end get_deliver_to;
156
157 /****************************************************************************
158 * This function returns requisition total of a given requisition header id *
159 ****************************************************************************/
160 function get_req_total(req_header_id IN NUMBER) RETURN NUMBER is
161 value NUMBER := 0;
162 begin
163 select SUM(decode(prl.matching_basis, 'AMOUNT', prl.amount,
164 prl.unit_price * (prl.quantity - nvl(prl.quantity_cancelled,0))))
165 into value
166 from
167 po_requisition_lines_all prl
168 where
169 requisition_header_id = req_header_id
170 and nvl(modified_by_agent_flag, 'N') = 'N'
171 and nvl(cancel_flag, 'N') = 'N';
172
173 return value;
174 end get_req_total;
175
176 /****************************************************************************
177 * This function returns note to agent value of a given requisition *
178 * header id *
179 ****************************************************************************/
180 function get_note_to_agent(req_header_id IN NUMBER) RETURN VARCHAR2 is
181 value VARCHAR2(1000) := '';
182 begin
183 select note_to_agent
184 into value
185 from po_requisition_lines_all
186 where
187 requisition_header_id = req_header_id and
188 rownum = 1;
189
190 return value;
191
192 end get_note_to_agent;
193
194
195 /**************************************************************************
196 * This function returns multiple_value or account number of a given *
197 * requisition line id *
198 **************************************************************************/
199 function get_account_number(req_line_id NUMBER) RETURN VARCHAR2 is
200 no_of_values NUMBER := 0;
201 value VARCHAR2(1000) := '';
202 begin
203 select count(distinct nvl(code_combination_id,0))
204 into no_of_values
205 from po_req_distributions_all
206 where requisition_line_id = req_line_id;
207
208 if (no_of_values > 1) then
209 return 'MULTIPLE_VALUE';
210 else
211 select concatenated_segments
212 into value
213 from
214 po_req_distributions_all prd,
215 gl_code_combinations_kfv cc,
216 gl_sets_of_books sob,
217 financials_system_params_all fsp
218 where
219 prd.requisition_line_id = req_line_id and
220 cc.code_combination_id = prd.code_combination_id and
221 cc.chart_of_accounts_id = sob.chart_of_accounts_id and
222 sob.set_of_books_id = fsp.set_of_books_id and
223 rownum =1;
224 return value;
225 end if;
226
227 end get_account_number;
228
229
230 /**************************************************************************
231 * This function returns multiple_value or project number of a given *
232 * requisition line id *
233 **************************************************************************/
234 function get_project_number(req_line_id NUMBER) RETURN VARCHAR2 is
235 no_of_values NUMBER := 0;
236 value VARCHAR2(1000) := '';
237 begin
238 select count(distinct nvl(project_id,0))
239 into no_of_values
240 from po_req_distributions_all
241 where requisition_line_id = req_line_id;
242
243 if (no_of_values > 1) then
244 return 'MULTIPLE_VALUE';
245 else
246 select projects.segment1
247 into value
248 from
249 po_req_distributions_all prd,
250 pa_projects_all projects
251 where
252 prd.requisition_line_id = req_line_id and
253 projects.project_id = prd.project_id and
254 rownum = 1;
255 return value;
256 end if;
257
258 end get_project_number;
259
260 /**************************************************************************
261 * This function returns multiple_value or task name of a given *
262 * requisition line id *
263 **************************************************************************/
264 function get_task_name(req_line_id NUMBER) RETURN VARCHAR2 is
265 no_of_values NUMBER := 0;
266 value VARCHAR2(1000) := '';
267 begin
268 select count(distinct nvl(task_id,0))
269 into no_of_values
270 from po_req_distributions_all
271 where requisition_line_id = req_line_id;
272
273 if (no_of_values > 1) then
274 return 'MULTIPLE_VALUE';
275 else
276 select tasks.task_name
277 into value
278 from
279 po_req_distributions_all prd,
280 pa_tasks tasks
281 where
282 prd.requisition_line_id = req_line_id and
283 tasks.task_id = prd.task_id and
284 rownum = 1;
285 return value;
286 end if;
287
288 end get_task_name;
289
290 /**************************************************************************
291 * This function returns multiple_value or expenditure type of a given *
292 * requisition line id *
293 **************************************************************************/
294 function get_expenditure_type(req_line_id NUMBER) RETURN VARCHAR2 is
295 no_of_values NUMBER := 0;
296 value VARCHAR2(1000) := '';
297 begin
298 select count(distinct nvl(expenditure_type,''))
299 into no_of_values
300 from po_req_distributions_all
301 where requisition_line_id = req_line_id;
302
303 if (no_of_values > 1) then
304 return 'MULTIPLE_VALUE';
305 else
306 select expenditure_type
307 into value
308 from po_req_distributions_all
309 where
310 requisition_line_id = req_line_id and
311 rownum =1 ;
312
313 return value;
314 end if;
315
316 end get_expenditure_type;
317
318 /**************************************************************************
319 * This function returns sales order number and status of a given *
320 * requisition line id *
321 **************************************************************************/
322 function get_so_number(req_line_id NUMBER) RETURN VARCHAR2 is
323 l_status_code VARCHAR2(50);
324 l_flow_meaning VARCHAR2(50);
325 l_so_number VARCHAR2(50);
326 l_line_id NUMBER;
327 l_released_count NUMBER;
328 l_total_count NUMBER;
329 begin
330 select to_char(OOH.ORDER_NUMBER), OOL.FLOW_STATUS_CODE, OOL.LINE_ID
331 INTO l_so_number, l_status_code, l_line_id
332 from PO_REQUISITION_LINES PRL,
333 PO_REQUISITION_HEADERS PRH,
334 OE_ORDER_HEADERS_ALL OOH,
335 OE_ORDER_LINES_ALL OOL,
336 PO_SYSTEM_PARAMETERS PSP
337 WHERE PRL.REQUISITION_HEADER_ID = PRH.REQUISITION_HEADER_ID
338 AND PRL.REQUISITION_LINE_ID = req_line_id
339 AND PRH.SEGMENT1 = OOH.ORIG_SYS_DOCUMENT_REF
340 AND OOL.HEADER_ID = OOH.HEADER_ID
341 AND OOL.ORIG_SYS_LINE_REF = to_char(PRL.LINE_NUM)
342 AND PSP.ORDER_SOURCE_ID = OOH.ORDER_SOURCE_ID;
343
344 return l_so_number;
345
346 EXCEPTION
347 WHEN no_data_found THEN
348 RETURN null;
349 end get_so_number;
350 /**************************************************************************
351 * This function returns sales order number and status of a given *
352 * requisition line id *
353 **************************************************************************/
354 function get_so_number_status(req_line_id NUMBER,p_prefix_so_number VARCHAR2 DEFAULT 'Y') RETURN VARCHAR2 is
355 l_status_code VARCHAR2(50);
356 l_so_number VARCHAR2(50);
357 l_so_number_status VARCHAR2(50);
358 l_split_line_num NUMBER;
359 l_line_id NUMBER;
360 begin
361
362 l_split_line_num :=0;
363 select count(OOL.LINE_ID)
364 INTO l_split_line_num
365 from PO_REQUISITION_LINES PRL,
366 PO_REQUISITION_HEADERS PRH,
367 OE_ORDER_HEADERS_ALL OOH,
368 OE_ORDER_LINES_ALL OOL,
369 PO_SYSTEM_PARAMETERS PSP
370 WHERE PRL.REQUISITION_HEADER_ID = PRH.REQUISITION_HEADER_ID
371 AND PRL.REQUISITION_LINE_ID = req_line_id
372 AND PRH.SEGMENT1 = OOH.ORIG_SYS_DOCUMENT_REF
373 AND OOL.HEADER_ID = OOH.HEADER_ID
374 AND OOL.ORIG_SYS_LINE_REF = to_char(PRL.LINE_NUM)
375 AND PSP.ORDER_SOURCE_ID = OOH.ORDER_SOURCE_ID;
376
377 -- Added the new parameter to check if the LifecycleCO invokes this code indirectly.
378 -- In case, from LifecyclePG do not return Multiple_value.
379 -- if the internal order line is split, the return 'MULTIPLE_VALUE'
380 if ( l_split_line_num > 1 ) then
381 return 'MULTIPLE_VALUE';
382 end if;
383
384 select to_char(OOH.ORDER_NUMBER), OOL.FLOW_STATUS_CODE, OOL.LINE_ID
385 INTO l_so_number, l_status_code, l_line_id
386 from PO_REQUISITION_LINES PRL,
387 PO_REQUISITION_HEADERS PRH,
388 OE_ORDER_HEADERS_ALL OOH,
389 OE_ORDER_LINES_ALL OOL,
390 PO_SYSTEM_PARAMETERS PSP
391 WHERE PRL.REQUISITION_HEADER_ID = PRH.REQUISITION_HEADER_ID
392 AND PRL.REQUISITION_LINE_ID = req_line_id
393 AND PRH.SEGMENT1 = OOH.ORIG_SYS_DOCUMENT_REF
394 AND OOL.HEADER_ID = OOH.HEADER_ID
395 AND OOL.ORIG_SYS_LINE_REF = to_char(PRL.LINE_NUM)
396 AND PSP.ORDER_SOURCE_ID = OOH.ORDER_SOURCE_ID;
397
398 l_so_number_status := get_so_number_status_code(l_status_code, l_line_id, l_so_number,p_prefix_so_number);
399
400 return l_so_number_status;
401 EXCEPTION
402 WHEN no_data_found THEN
403 RETURN null;
404 end get_so_number_status;
405
406 --Invoked when coming from Lifecycle page
407 function get_so_number_status(req_line_id NUMBER,p_prefix_so_number VARCHAR2 DEFAULT 'Y', p_line_id IN NUMBER) RETURN VARCHAR2 is
408 l_status_code VARCHAR2(50);
409 l_so_number VARCHAR2(50);
410 l_so_number_status VARCHAR2(50);
411 l_line_id NUMBER;
412 begin
413 select to_char(OOH.ORDER_NUMBER), OOL.FLOW_STATUS_CODE, OOL.LINE_ID
414 INTO l_so_number, l_status_code, l_line_id
415 from PO_REQUISITION_LINES PRL,
416 PO_REQUISITION_HEADERS PRH,
417 OE_ORDER_HEADERS_ALL OOH,
418 OE_ORDER_LINES_ALL OOL,
419 PO_SYSTEM_PARAMETERS PSP
420 WHERE PRL.REQUISITION_HEADER_ID = PRH.REQUISITION_HEADER_ID
421 AND PRL.REQUISITION_LINE_ID = req_line_id
422 AND PRH.SEGMENT1 = OOH.ORIG_SYS_DOCUMENT_REF
423 AND OOL.HEADER_ID = OOH.HEADER_ID
424 AND OOL.ORIG_SYS_LINE_REF = to_char(PRL.LINE_NUM)
425 AND OOL.LINE_ID = p_line_id
426 AND PSP.ORDER_SOURCE_ID = OOH.ORDER_SOURCE_ID;
427
428 l_so_number_status := get_so_number_status_code(l_status_code, l_line_id, l_so_number,p_prefix_so_number);
429
430 return l_so_number_status;
431 EXCEPTION
432 WHEN no_data_found THEN
433 RETURN null;
434
435 end get_so_number_status;
436
437 --Code to return the status of a line
438 function get_so_number_status_code(p_status_code IN VARCHAR2, p_line_id IN NUMBER, p_so_number IN NUMBER,p_prefix_so_number VARCHAR2 DEFAULT 'Y') RETURN VARCHAR2 IS
439 l_released_count NUMBER;
440 l_total_count NUMBER;
441 l_flow_meaning VARCHAR2(50);
442 l_so_number_status VARCHAR2(50);
443 Begin
444 IF (p_status_code is not null) THEN
445 IF p_status_code <> 'AWAITING_SHIPPING' AND
446 p_status_code <> 'PRODUCTION_COMPLETE' AND
447 p_status_code <> 'PICKED' AND
448 p_status_code <> 'PICKED_PARTIAL'
449 THEN
450 SELECT meaning
451 INTO l_flow_meaning
452 FROM fnd_lookup_values lv
453 WHERE lookup_type = 'LINE_FLOW_STATUS'
454 AND lookup_code = p_status_code
455 AND LANGUAGE = userenv('LANG')
456 AND VIEW_APPLICATION_ID = 660
457 AND SECURITY_GROUP_ID =
458 fnd_global.Lookup_Security_Group(lv.lookup_type,
459 lv.view_application_id);
460
461 /* status is AWAITING_SHIPPING or PRODUCTION_COMPLETE etc.
462 get value from shipping table */
463 ELSE
464 SELECT sum(decode(released_status, 'Y', 1, 0)), sum(1)
465 INTO l_released_count, l_total_count
466 FROM wsh_delivery_details
467 WHERE source_line_id = p_line_id
468 AND source_code = 'OE'
469 AND released_status <> 'D';
470
471 IF l_released_count = l_total_count THEN
472 SELECT meaning
473 INTO l_flow_meaning
474 FROM fnd_lookup_values lv
475 WHERE lookup_type = 'LINE_FLOW_STATUS'
476 AND lookup_code = 'PICKED'
477 AND LANGUAGE = userenv('LANG')
478 AND VIEW_APPLICATION_ID = 660
479 AND SECURITY_GROUP_ID =
480 fnd_global.Lookup_Security_Group(lv.lookup_type,
481 lv.view_application_id);
482
483 ELSIF l_released_count < l_total_count and l_released_count <> 0 THEN
484 SELECT meaning
485 INTO l_flow_meaning
486 FROM fnd_lookup_values lv
487 WHERE lookup_type = 'LINE_FLOW_STATUS'
488 AND lookup_code = 'PICKED_PARTIAL'
489 AND LANGUAGE = userenv('LANG')
490 AND VIEW_APPLICATION_ID = 660
491 AND SECURITY_GROUP_ID =
492 fnd_global.Lookup_Security_Group(lv.lookup_type,
493 lv.view_application_id);
494 ELSE
495 SELECT meaning
496 INTO l_flow_meaning
497 FROM fnd_lookup_values lv
498 WHERE lookup_type = 'LINE_FLOW_STATUS'
499 AND lookup_code = p_status_code
500 AND LANGUAGE = userenv('LANG')
501 AND VIEW_APPLICATION_ID = 660
502 AND SECURITY_GROUP_ID =
503 fnd_global.Lookup_Security_Group(lv.lookup_type,
504 lv.view_application_id);
505 END IF;
506 END IF;
507
508 if(p_prefix_so_number = 'Y') then
509 l_so_number_status := p_so_number || ' (' || l_flow_meaning || ')';
510 else
511 l_so_number_status := l_flow_meaning;
512 end if;
513 END IF;
514 return l_so_number_status;
515
516 EXCEPTION
517 WHEN no_data_found THEN
518 RETURN null;
519 end get_so_number_status_code;
520
521 /**************************************************************************
522 * This function returns sales order status (header) of a given *
523 * requisition line id *
524 **************************************************************************/
525 function get_so_status(req_line_id NUMBER) RETURN VARCHAR2 is
526 l_status_code VARCHAR2(50);
527 l_flow_meaning VARCHAR2(50);
528 l_so_number VARCHAR2(50);
529 l_line_id NUMBER;
530 l_released_count NUMBER;
531 l_total_count NUMBER;
532 begin
533 select to_char(OOH.ORDER_NUMBER), OOH.FLOW_STATUS_CODE, OOL.LINE_ID
534 INTO l_so_number, l_status_code, l_line_id
535 from PO_REQUISITION_LINES_ALL PRL,
536 PO_REQUISITION_HEADERS_ALL PRH,
537 OE_ORDER_HEADERS_ALL OOH,
538 OE_ORDER_LINES_ALL OOL,
539 PO_SYSTEM_PARAMETERS_ALL PSP
540 WHERE PRL.REQUISITION_HEADER_ID = PRH.REQUISITION_HEADER_ID
541 AND PRL.REQUISITION_LINE_ID = req_line_id
542 AND NVL(OOH.ORG_ID, -99) = NVL(PSP.ORG_ID, -99)
543 AND PRH.SEGMENT1 = OOH.ORIG_SYS_DOCUMENT_REF
544 AND OOL.HEADER_ID = OOH.HEADER_ID
545 AND OOL.ORIG_SYS_LINE_REF = to_char(PRL.LINE_NUM)
546 AND PSP.ORDER_SOURCE_ID = OOH.ORDER_SOURCE_ID;
547
548 IF (l_status_code is not null) THEN
549 SELECT meaning
550 INTO l_flow_meaning
551 FROM fnd_lookup_values lv
552 WHERE lookup_type = 'LINE_FLOW_STATUS'
553 AND lookup_code = l_status_code
554 AND LANGUAGE = userenv('LANG')
555 AND VIEW_APPLICATION_ID = 660
556 AND SECURITY_GROUP_ID =
557 fnd_global.Lookup_Security_Group(lv.lookup_type,
558 lv.view_application_id);
559 END IF;
560 return l_flow_meaning;
561
562 EXCEPTION
563 WHEN no_data_found THEN
564 RETURN null;
565 end get_so_status;
566
567 /**************************************************************************
568 * This function returns multiple_value or shipment number of a given *
569 * requisition line id *
570 **************************************************************************/
571 function get_shipment_number(req_line_id NUMBER) RETURN VARCHAR2 is
572 p_values dbms_sql.VARCHAR2_TABLE;
573 begin
574 select RSH.SHIPMENT_NUM BULK COLLECT INTO p_values
575 from RCV_SHIPMENT_HEADERS RSH, RCV_SHIPMENT_LINES RSL
576 WHERE RSL.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
577 AND RSL.REQUISITION_LINE_ID = req_line_id;
578
579 if (p_values IS NULL or p_values.COUNT < 1) then
580 return null;
581 elsif (p_values.COUNT > 1) then
582 return FND_MESSAGE.GET_STRING('ICX', 'ICX_POR_MULTIPLE');
583 else
584 return p_values(1);
585 end if;
586
587 end get_shipment_number;
588
589 function get_supplier_name(req_header_id NUMBER) RETURN VARCHAR2 is
590
591 CURSOR req_supplier_info (c_req_header_id NUMBER) IS
592 SELECT PRL.suggested_vendor_name, PRL.vendor_id
593 FROM PO_REQUISITION_LINES_ALL PRL,
594 po_line_types plt
595 where prl.requisition_header_id = c_req_header_id
596 and prl.source_type_code = 'VENDOR'
597 and prl.line_type_id = plt.line_type_id
598 and plt.outside_operation_flag = 'N';
599
600
601 CURSOR contractor_req_supplier_info (c_req_header_id NUMBER) IS
602 SELECT prl.suggested_vendor_name, prl.requisition_line_id
603 FROM PO_REQUISITION_LINES_ALL PRL,
604 po_line_types plt
605 where prl.requisition_header_id = c_req_header_id
606 and prl.source_type_code = 'VENDOR'
607 and prl.line_type_id = plt.line_type_id
608 and plt.outside_operation_flag = 'N'
609 and prl.LABOR_REQ_LINE_ID is null; -- labor line only
610
611 x_counter number := 0;
612 x_vendor_name varchar2(1000);
613 x_vendor_id number;
614 x_current_vendor_name varchar2(1000);
615 x_current_vendor_id number;
616 x_req_line_id number;
617 l_contractor_requisition_flag VARCHAR2(1);
618
619 begin
620
621 select CONTRACTOR_REQUISITION_FLAG
622 into l_contractor_requisition_flag
623 from po_requisition_headers
624 where requisition_header_id = req_header_id;
625
626 if ( l_contractor_requisition_flag = 'Y') then
627 -- contrctor requisition. There might be multiple suppliers in
628 -- the requisition line.
629
630 OPEN contractor_req_supplier_info(req_header_id);
631 LOOP
632 FETCH contractor_req_supplier_info INTO x_vendor_name, x_req_line_id;
633 EXIT WHEN contractor_req_supplier_info %NOTFOUND;
634
635 if(x_vendor_name is null) then
636 x_vendor_name := get_labor_line_supplier_name(x_req_line_id);
637 end if;
638
639 if(x_vendor_name is not null) then
640 if(x_counter = 0) then
641 x_current_vendor_name := x_vendor_name;
642 x_counter := x_counter + 1;
643 elsif (x_current_vendor_name <> x_vendor_name) then
644 x_current_vendor_name := 'MULTIPLE_VALUE';
645 exit;
646 end if;
647 end if;
648 END LOOP;
649 CLOSE contractor_req_supplier_info;
650 else
651 -- There should be at most 1 supplier for each requisition line
652 OPEN req_supplier_info(req_header_id);
653 LOOP
654 FETCH req_supplier_info INTO x_vendor_name, x_vendor_id;
655 EXIT WHEN req_supplier_info %NOTFOUND;
656 if(x_counter = 0) then
657 x_current_vendor_name := x_vendor_name;
658 x_current_vendor_id := x_vendor_id;
659 x_counter := x_counter + 1;
660 elsif (x_current_vendor_name <> x_vendor_name
661 or x_current_vendor_id <> x_vendor_id) then
662 x_current_vendor_name := 'MULTIPLE_VALUE';
663 exit;
664 end if;
665 END LOOP;
666 CLOSE req_supplier_info;
667
668 end if;
669
670 return x_current_vendor_name;
671
672
673 exception
674 when others then
675 return null;
676
677 end get_supplier_name;
678
679
680 /**************************************************************************
681 * This function returns 'Y' if there is even one reqline on a PO *
682 * *************************************************************************/
683 function is_PlacedOnPO(req_header_id NUMBER) RETURN VARCHAR2 is
684
685 is_aPO VARCHAR2(1) := '';
686 no_of_linesOnPO NUMBER := 0;
687
688 begin
689 select decode(count(prl.line_location_id),0,'N','Y')
690 into is_aPO
691 from po_requisition_lines prl
692 where prl.requisition_header_id = req_header_id
693 and prl.line_location_id is not null;
694
695 return is_aPO;
696
697 exception
698 when others then
699 return null;
700
701 end is_PlacedOnPO;
702
703 /**************************************************************************
704 * This function returns 'Y' if there is even one reqline on a SO *
705 * *************************************************************************/
706 function is_PlacedOnSO(req_header_id NUMBER) RETURN VARCHAR2 is
707
708 is_aSO VARCHAR2(1) := '';
709
710 begin
711 select prh.TRANSFERRED_TO_OE_FLAG
712 into is_aSO
713 from po_requisition_headers prh
714 where prh.requisition_header_id = req_header_id;
715
716 return is_aSO;
717
718 exception
719 when others then
720 return null;
721
722 end is_PlacedOnSO;
723
724 /**************************************************************************
725 * This function returns the full name of the approver *
726 **************************************************************************/
727 function get_approver_name(approver_id IN NUMBER) RETURN VARCHAR2 IS
728 value VARCHAR2(1000) := '';
729 begin
730 /** bgu, Apr. 08, 1999
731 * (1) Even the approver is no longer with the org, still need to retieve his/her
732 * Full Name.
733 * (2) Suppose a person has multiple employing history with the org, there're
734 * multiple records for the person in per_all_people_f table.
735 */
736 select distinct full_name
737 into value
738 from per_all_people_f hre
739 where hre.person_id = approver_id
740 and trunc(sysdate) BETWEEN effective_start_date
741 and effective_end_date;
742
743 return value;
744 exception
745 when others then
746 return null;
747
748 end get_approver_name;
749
750 /**************************************************************************
751 * This function returns the email address of the approver *
752 **************************************************************************/
753 function get_approver_email(approver_id IN NUMBER) RETURN VARCHAR2 IS
754 value VARCHAR2(1000) := '';
755 begin
756 /** bgu, Apr. 08, 1999
757 * (1) Even the approver is no longer with the org, still need to retieve his/her
758 * Full Name.
759 * (2) Suppose a person has multiple employing history with the org, there're
760 * multiple records for the person in per_all_people_f table.
761 */
762 select distinct email_address
763 into value
764 from per_all_people_f hre
765 where hre.person_id = approver_id
766 and trunc(sysdate) BETWEEN effective_start_date
767 and effective_end_date;
768
769 return value;
770 exception
771 when others then
772 return null;
773
774 end get_approver_email;
775
776
777 /**************************************************************************
778 * This procedure is used to return requisition total in currency format, *
779 * supplier name and placed on po flag all together to improve the *
780 * performance *
781 **************************************************************************/
782 procedure getLineInfo( reqHeaderId IN NUMBER,
783 currencyFormat IN VARCHAR2,
784 reqTotal OUT NOCOPY varchar2,
785 supplierName OUT NOCOPY VARCHAR2,
786 placedOnPoFlag OUT NOCOPY VARCHAR2) IS
787 begin
788 reqTotal := to_char(get_req_total(reqHeaderId), currencyFormat);
789 supplierName := get_supplier_name(reqHeaderId);
790 placedOnPoFlag := is_PlacedOnPO(reqHeaderId);
791 end getLineInfo;
792
793 /**************************************************************************
794 * This procedure is same with the procedure getLineInfo except that *
795 * it returns reqTotal as a unformatted number instead of a formatted *
796 * String. *
797 **************************************************************************/
798 procedure getUnformattedLineInfo( reqHeaderId IN NUMBER,
799 currencyFormat IN VARCHAR2,
800 reqTotal OUT NOCOPY NUMBER,
801 supplierName OUT NOCOPY VARCHAR2,
802 placedOnPoFlag OUT NOCOPY VARCHAR2) IS
803 begin
804 reqTotal := get_req_total(reqHeaderId);
805 supplierName := get_supplier_name(reqHeaderId);
806 placedOnPoFlag := is_PlacedOnPO(reqHeaderId);
807 end getUnformattedLineInfo;
808
809 /****************************************************************************
810 * This function returns urgent flag value of a given requisition header id *
811 ****************************************************************************/
812 function get_urgent_flag(req_header_id IN NUMBER) RETURN VARCHAR2 is
813 value PO_LOOKUP_CODES.DISPLAYED_FIELD%TYPE := '';
814 begin
815 select plc_urg.displayed_field
816 into value
817 from
818 po_requisition_headers_all prh,
819 po_requisition_lines_all prl,
820 po_lookup_codes plc_urg
821 where
822 prh.requisition_header_id = req_header_id and
823 prl.requisition_header_id = prh.requisition_header_id and
824 plc_urg.lookup_code = nvl(prl.urgent_flag, 'N') and
825 plc_urg.lookup_type = 'YES/NO' and
826 rownum = 1;
827
828 return value;
829
830 end get_urgent_flag;
831
832
833 /**************************************************************************
834 * This procedure returns distribution related info of a given *
835 * requisition line. *
836 **************************************************************************/
837 procedure getDistributionInfo(req_line_id IN NUMBER,
838 date_format IN VARCHAR2,
839 account_number OUT NOCOPY VARCHAR2,
840 project_id OUT NOCOPY NUMBER,
841 project_number OUT NOCOPY VARCHAR2,
842 task_id OUT NOCOPY NUMBER,
843 task_number OUT NOCOPY VARCHAR2,
844 expenditure_type OUT NOCOPY VARCHAR2,
845 expenditure_org_id OUT NOCOPY NUMBER,
846 expenditure_org OUT NOCOPY VARCHAR2,
847 expenditure_item_date OUT NOCOPY VARCHAR2) IS
848 begin
849 account_number := get_account_number(req_line_id);
850
851 select prd.project_id, prd.task_id, prd.expenditure_type,
852 prd.expenditure_organization_id,
853 to_char(prd.expenditure_item_date, date_format)
854 into project_id, task_id, expenditure_type,
855 expenditure_org_id, expenditure_item_date
856 from
857 po_req_distributions prd
858 where prd.requisition_line_id = req_line_id and
859 rownum = 1;
860
861 if (project_id is not null) then
862 select projects.segment1
863 into project_number
864 from
865 po_req_distributions prd,
866 pa_projects projects
867 where
868 prd.requisition_line_id = req_line_id and
869 projects.project_id = prd.project_id and
870 rownum = 1;
871 end if;
872
873 if (task_id is not null) then
874 select tasks.task_number
875 into task_number
876 from
877 po_req_distributions prd,
878 pa_tasks tasks
879 where
880 prd.requisition_line_id = req_line_id and
881 tasks.task_id = prd.task_id and rownum = 1 ;
882 end if;
883
884 if (expenditure_org_id is not null) then
885 select orgs.name
886 into expenditure_org
887 from
888 po_req_distributions prd,
889 pa_organizations_expend_v orgs
890 where
891 prd.requisition_line_id = req_line_id and
892 prd.expenditure_organization_id = orgs.organization_id and
893 rownum = 1;
894 end if;
895
896 end getDistributionInfo;
897
898 /****************************************************************************
899 * This function returns requisition line total of a given requisition *
900 * line. If the requisition line is cancelled, returns zero *
901 ****************************************************************************/
902 function get_line_total(req_line_id IN NUMBER, currency_code IN VARCHAR2) RETURN VARCHAR2 is
903 value VARCHAR2(2000) := '';
904 cancelled VARCHAR2(1) := 'N';
905 total NUMBER := 0;
906 begin
907 select nvl(cancel_flag, 'N')
908 into cancelled
909 from po_requisition_lines_all
910 where requisition_line_id = req_line_id;
911
912 if (cancelled = 'Y') then
913 select to_char(0, fnd_currency.safe_get_format_mask(currency_code, 30))
914 into value
915 from sys.dual;
916 else
917 select prl.unit_price * (prl.quantity - nvl(prl.quantity_cancelled,0))
918 into total
919 from po_requisition_lines_all prl
920 where requisition_line_id = req_line_id;
921
922 select to_char(total, fnd_currency.safe_get_format_mask(currency_code, 30))
923 into value
924 from sys.dual;
925 end if;
926
927 return value;
928 end get_line_total;
929
930 /* This function returns whether the req is modified by buyer */
931
932 function is_req_modified_by_buyer(reqHeaderId IN NUMBER) return varchar2 is
933
934 num_line_modified number := 0;
935
936 begin
937
938 select count(*)
939 into num_line_modified
940 from po_requisition_lines_all
941 where requisition_header_id = reqHeaderId
942 and MODIFIED_BY_AGENT_FLAG = 'Y';
943
944 if num_line_modified > 0 then
945 return 'Y';
946 else
947 return 'N';
948 end if;
949
950 end is_req_modified_by_buyer;
951
952 function get_business_group_name(approver_id IN NUMBER) RETURN VARCHAR2 IS
953 value VARCHAR2(1000) := '';
954 begin
955 select distinct pb.name
956 into value
957 from PER_BUSINESS_GROUPS_PERF pb,
958 per_all_people_f hre
959 where hre.person_id = approver_id
960 and hre.business_group_id=pb.business_group_id
961 and trunc(sysdate) BETWEEN effective_start_date
962 and effective_end_date;
963
964 return value;
965 exception
966 when others then
967 return null;
968
969 end get_business_group_name;
970
971 /****************************************************************************
972 * This function returns non recoverable tax total for a given requisition *
973 * header id. *
974 ****************************************************************************/
975 FUNCTION get_nonrec_tax_total(ReqHeaderId IN NUMBER)
976 RETURN NUMBER IS
977 total NUMBER := 0;
978 BEGIN
979
980 SELECT sum(nvl(prd.nonrecoverable_tax,0))
981 INTO total
982 FROM
983 po_req_distributions prd,
984 po_requisition_lines prl
985 WHERE
986 prd.requisition_line_id = prl.requisition_line_id and
987 prl.requisition_header_id= ReqHeaderId and
988 NVL(prl.cancel_flag, 'N') = 'N' and
989 NVL(prl.modified_by_agent_flag, 'N') = 'N';
990
991 RETURN total;
992
993 END get_nonrec_tax_total;
994
995 /****************************************************************************
996 * This function returns non recoverable tax total for a given requisition *
997 * line id. *
998 ****************************************************************************/
999 FUNCTION get_line_nonrec_tax_total(ReqLineId IN NUMBER) RETURN NUMBER IS
1000 total NUMBER := 0;
1001
1002 BEGIN
1003 SELECT sum(nvl(prd.nonrecoverable_tax,0))
1004 INTO total
1005 FROM
1006 po_req_distributions prd,
1007 po_requisition_lines prl
1008 WHERE
1009 prd.requisition_line_id = ReqLineId AND
1010 prd.requisition_line_id = prl.requisition_line_id AND
1011 NVL(prl.cancel_flag, 'N') = 'N' AND
1012 NVL(prl.modified_by_agent_flag, 'N') = 'N';
1013
1014 RETURN total;
1015
1016 END get_line_nonrec_tax_total;
1017
1018
1019 /****************************************************************************
1020 * This function returns recoverable tax total for a given requisition *
1021 * line id. *
1022 ****************************************************************************/
1023 FUNCTION get_line_rec_tax_total(ReqLineId IN NUMBER) RETURN NUMBER is
1024 total NUMBER := 0;
1025
1026 BEGIN
1027 SELECT sum(nvl(prd.recoverable_tax,0))
1028 INTO total
1029 FROM
1030 po_req_distributions prd,
1031 po_requisition_lines prl
1032 WHERE
1033 prd.requisition_line_id = ReqLineId AND
1034 prd.requisition_line_id = prl.requisition_line_id AND
1035 NVL(prl.cancel_flag, 'N') = 'N' AND
1036 NVL(prl.modified_by_agent_flag, 'N') = 'N';
1037
1038 RETURN total;
1039 END get_line_rec_tax_total;
1040
1041 /**
1042 * Returns Y if there is cancelled lines for the given requisition;
1043 * Else returns N
1044 */
1045 FUNCTION GET_CANCEL_FLAG(p_req_header_id IN NUMBER)
1046 RETURN VARCHAR2 IS
1047 x_cancels NUMBER := 0;
1048 BEGIN
1049 SELECT COUNT(*)
1050 INTO X_CANCELS
1051 FROM PO_REQUISITION_LINES_ALL PRL
1052 WHERE PRL.REQUISITION_HEADER_ID = P_REQ_HEADER_ID
1053 AND NVL(CANCEL_FLAG, 'N') = 'N';
1054
1055 IF ( X_CANCELS > 0 ) THEN
1056 RETURN 'Y';
1057 ELSE
1058 RETURN 'N';
1059 END IF;
1060 END GET_CANCEL_FLAG;
1061
1062 /**
1063 * Returns Y if there is return transaction for the given parent trxn id;
1064 * Else returns N
1065 */
1066 FUNCTION GET_RETURN_FLAG(p_txn_id IN NUMBER)
1067 RETURN VARCHAR2 IS
1068 x_returns NUMBER := 0;
1069 BEGIN
1070 SELECT COUNT(*)
1071 INTO X_RETURNS
1072 FROM RCV_TRANSACTIONS RT
1073 WHERE RT.PARENT_TRANSACTION_ID = P_TXN_ID
1074 AND RT.TRANSACTION_TYPE = 'RETURN TO RECEIVING';
1075
1076 IF ( X_RETURNS > 0 ) THEN
1077 RETURN 'Y';
1078 ELSE
1079 RETURN 'N';
1080 END IF;
1081 END GET_RETURN_FLAG;
1082
1083 /**
1084 * This function returns the po release id
1085 * if there is only one purchase order associated with the requisition;
1086 * or returns null if there is more than one order
1087 * associated with the requisition
1088 */
1089 FUNCTION GET_PO_RELEASE_ID(p_req_header_id in number)
1090 RETURN number IS
1091
1092 x_po_header_id number;
1093 x_po_release_id number;
1094
1095 BEGIN
1096 get_po_info (p_req_header_id,x_po_header_id, x_po_release_id );
1097
1098 return x_po_release_id;
1099 END GET_PO_RELEASE_ID;
1100
1101
1102 /**
1103 * This function returns the po header id
1104 * if there is only one purchase order associated with the requisition;
1105 * or returns null if there is more than one order
1106 * associated with the requisition
1107 */
1108
1109 FUNCTION GET_PO_HEADER_ID(p_req_header_id in number)
1110 RETURN number IS
1111
1112 x_po_header_id number;
1113 x_po_release_id number;
1114
1115 BEGIN
1116 get_po_info (p_req_header_id,x_po_header_id, x_po_release_id );
1117
1118 return x_po_header_id;
1119 END GET_PO_HEADER_ID;
1120
1121
1122 PROCEDURE GET_PO_INFO(p_req_header_id in number,
1123 p_po_header_id out NOCOPY number,
1124 p_po_release_id out NOCOPY number) IS
1125
1126 CURSOR c_po_info (c_req_header_id NUMBER) IS
1127 SELECT
1128 PH.PO_HEADER_ID, PR.PO_RELEASE_ID
1129 FROM
1130 PO_REQUISITION_LINES_ALL PRL,
1131 PO_REQ_DISTRIBUTIONS_ALL PRD,
1132 PO_DISTRIBUTIONS_ALL PD,
1133 PO_RELEASES_ALL PR,
1134 PO_HEADERS_ALL PH
1135 WHERE
1136 PD.PO_HEADER_ID = PH.PO_HEADER_ID AND
1137 PD.PO_RELEASE_ID = PR.PO_RELEASE_ID(+) AND
1138 PRD.DISTRIBUTION_ID = PD.REQ_DISTRIBUTION_ID AND
1139 PRD.REQUISITION_LINE_ID = PRL.REQUISITION_LINE_ID AND
1140 PRL.REQUISITION_HEADER_ID = C_REQ_HEADER_ID;
1141
1142 x_counter number := 0;
1143 x_po_header_id number := null;
1144 x_old_po_header_id number := null;
1145 x_po_release_id number := null;
1146 x_old_po_release_id number := null;
1147
1148
1149 BEGIN
1150 OPEN c_po_info(p_req_header_id);
1151 LOOP
1152 FETCH c_po_info INTO x_po_header_id, x_po_release_id;
1153 EXIT WHEN c_po_info %NOTFOUND;
1154 if(x_counter = 0) then
1155 x_old_po_header_id := x_po_header_id;
1156 x_old_po_release_id := x_po_release_id;
1157 x_counter := x_counter + 1;
1158 elsif (x_old_po_header_id <> x_po_header_id
1159 or x_old_po_release_id <> x_po_release_id) then
1160 x_po_header_id := null;
1161 x_po_release_id := null;
1162 exit;
1163 end if;
1164 END LOOP;
1165 CLOSE c_po_info;
1166
1167 p_po_header_id := x_po_header_id;
1168 p_po_release_id := x_po_release_id;
1169
1170 -- Exception
1171
1172 end GET_PO_INFO;
1173
1174 PROCEDURE GET_ORDER_RELATED_INFO(p_req_header_id in number,
1175 order_number out NOCOPY varchar2,
1176 order_source_type out NOCOPY varchar2,
1177 header_id out NOCOPY number,
1178 po_release_id out NOCOPY number,
1179 purchasing_org out NOCOPY varchar2,
1180 placed_on_po_flag out NOCOPY varchar2,
1181 order_status out NOCOPY varchar2) IS
1182
1183 no_of_po number :=0;
1184 req_line_id_po number :=0;
1185
1186 no_of_so number :=0;
1187 req_line_id_so number :=0;
1188
1189 no_of_order number := 0;
1190
1191 BEGIN
1192
1193 order_number:= null;
1194 order_source_type := null;
1195 purchasing_org := null;
1196 header_id := null;
1197 po_release_id := null;
1198 placed_on_po_flag := 'N';
1199 order_status := null;
1200 SELECT COUNT(DISTINCT(
1201 PH.SEGMENT1 ||DECODE(PR.RELEASE_NUM, NULL,'','-'||PR.RELEASE_NUM))),
1202 min(PRL.REQUISITION_LINE_ID)
1203 into no_of_po, req_line_id_po
1204 FROM
1205 PO_REQUISITION_LINES_ALL PRL,
1206 PO_REQUISITION_HEADERS_ALL PRH,
1207 PO_LINE_LOCATIONS_ALL PLL,
1208 PO_RELEASES_ALL PR,
1209 PO_HEADERS_ALL PH
1210 WHERE
1211 PLL.PO_HEADER_ID = PH.PO_HEADER_ID AND
1212 PR.PO_RELEASE_ID(+) = PLL.PO_RELEASE_ID AND
1213 PLL.LINE_LOCATION_ID = PRL.LINE_LOCATION_ID AND
1214 PRL.REQUISITION_HEADER_ID = PRH.REQUISITION_HEADER_ID AND
1215 PRH.REQUISITION_HEADER_ID = p_req_header_id;
1216
1217 if (no_of_po > 1) then
1218 order_number:= 'MULTIPLE_VALUE';
1219 order_source_type := 'MULTIPLE_VALUE';
1220 purchasing_org := 'MULTIPLE_VALUE';
1221 header_id := null;
1222 po_release_id := null;
1223 placed_on_po_flag := 'Y';
1224 order_status := 'MULTIPLE_VALUE';
1225 else
1226 SELECT COUNT(DISTINCT(OOH.HEADER_ID)), min(PRL.REQUISITION_LINE_ID)
1227 into no_of_so, req_line_id_so
1228 FROM
1229 PO_REQUISITION_HEADERS PRH,
1230 PO_REQUISITION_LINES_ALL PRL,
1231 OE_ORDER_HEADERS_ALL OOH,
1232 OE_ORDER_LINES_ALL OOL,
1233 PO_SYSTEM_PARAMETERS PSP
1234 WHERE
1235 OOH.ORDER_SOURCE_ID = PSP.ORDER_SOURCE_ID AND
1236 OOH.ORIG_SYS_DOCUMENT_REF = PRH.SEGMENT1 AND
1237 OOH.SOURCE_DOCUMENT_ID = PRH.REQUISITION_HEADER_ID AND
1238 OOH.HEADER_ID = OOL.HEADER_ID AND
1239 OOL.SOURCE_DOCUMENT_LINE_ID = PRL.REQUISITION_LINE_ID AND
1240 PRH.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_ID AND
1241 PRH.REQUISITION_HEADER_ID = p_req_header_id;
1242
1243 no_of_order := no_of_po + no_of_so;
1244
1245 if (no_of_order > 1 ) then
1246 order_number:= 'MULTIPLE_VALUE';
1247 order_source_type := 'MULTIPLE_VALUE';
1248 purchasing_org := 'MULTIPLE_VALUE';
1249 header_id := null;
1250 po_release_id := null;
1251 order_status := 'MULTIPLE_VALUE';
1252
1253 if (no_of_po > 0) then
1254 placed_on_po_flag := 'Y';
1255 end if;
1256
1257 elsif (no_of_order = 0) then
1258 order_number:= null;
1259 order_source_type := null;
1260 purchasing_org := null;
1261 header_id := null;
1262 po_release_id := null;
1263 placed_on_po_flag := 'N';
1264 order_status := null;
1265
1266 else
1267 if(no_of_po = 1) then
1268 -- ONLY ONE PO
1269 SELECT
1270 PH.SEGMENT1 ||DECODE(PR.RELEASE_NUM, NULL,'','-'||PR.RELEASE_NUM),
1271 PH.PO_HEADER_ID,
1272 PRL.SOURCE_TYPE_CODE,
1273 PR.PO_RELEASE_ID,
1274 HOU.NAME,
1275 'Y',
1276 DECODE(PR.PO_RELEASE_ID,
1277 NULL, PH.AUTHORIZATION_STATUS,
1278 PR.AUTHORIZATION_STATUS)
1279 into
1280 order_number,
1281 header_id,
1282 order_source_type,
1283 po_release_id,
1284 purchasing_org,
1285 placed_on_po_flag,
1286 order_status
1287 FROM
1288 PO_REQUISITION_LINES_ALL PRL,
1289 PO_LINE_LOCATIONS_ALL PLL,
1290 PO_RELEASES_ALL PR,
1291 PO_HEADERS_ALL PH,
1292 HR_ALL_ORGANIZATION_UNITS_VL HOU
1293 WHERE
1294 PLL.PO_HEADER_ID = PH.PO_HEADER_ID(+) AND
1295 PR.PO_RELEASE_ID(+) = PLL.PO_RELEASE_ID AND
1296 PLL.LINE_LOCATION_ID(+) = PRL.LINE_LOCATION_ID AND
1297 PRL.REQUISITION_LINE_ID = req_line_id_po AND
1298 PH.ORG_ID = HOU.ORGANIZATION_ID (+);
1299
1300 else
1301 -- no_of_so = 1
1302 -- ONLY ONE SO
1303 SELECT
1304 TO_CHAR(OOH.ORDER_NUMBER),
1305 OOH.HEADER_ID,
1306 PRL.SOURCE_TYPE_CODE,
1307 null,
1308 null,
1309 'N',
1310 get_so_number_status(prl.requisition_line_id)
1311 into
1312 order_number,
1313 header_id,
1314 order_source_type,
1315 po_release_id,
1316 purchasing_org,
1317 placed_on_po_flag,
1318 order_status
1319 FROM
1320 PO_REQUISITION_HEADERS PRH,
1321 PO_REQUISITION_LINES_ALL PRL,
1322 OE_ORDER_HEADERS_ALL OOH,
1323 OE_ORDER_LINES_ALL OOL,
1324 PO_SYSTEM_PARAMETERS PSP
1325 WHERE
1326 OOH.ORDER_SOURCE_ID = PSP.ORDER_SOURCE_ID AND
1327 OOH.ORIG_SYS_DOCUMENT_REF = PRH.SEGMENT1 AND
1328 OOH.HEADER_ID = OOL.HEADER_ID AND
1329 OOL.SOURCE_DOCUMENT_LINE_ID = PRL.REQUISITION_LINE_ID AND
1330 PRL.REQUISITION_LINE_ID = req_line_id_so AND
1331 OOH.SOURCE_DOCUMENT_ID = PRH.REQUISITION_HEADER_ID AND
1332 PRH.REQUISITION_HEADER_ID = p_req_header_id AND
1333 rownum = 1;
1334
1335 end if;
1336 end if;
1337
1338 end if;
1339
1340 -- EXCEPTION
1341 END GET_ORDER_RELATED_INFO;
1342
1343 /**
1344 * This function returns the purchasing organization name
1345 * if there is only one org associated with the requisition;
1346 * or returns 'MULTIPLE' if there is more than one org
1347 * associated with the requisition
1348 */
1349 FUNCTION GET_PURCHASING_ORG(p_req_header_id in number)
1350 RETURN varchar2 IS
1351 CURSOR c_purchasing_org (c_req_header_id NUMBER) IS
1352 SELECT
1353 HOU.NAME ORG_NAME, PH.ORG_ID
1354 FROM
1355 PO_REQUISITION_LINES_ALL PRL,
1356 PO_REQ_DISTRIBUTIONS_ALL PRD,
1357 PO_DISTRIBUTIONS_ALL PD,
1358 PO_HEADERS_ALL PH,
1359 HR_ALL_ORGANIZATION_UNITS_VL HOU
1360 WHERE
1361 PD.PO_HEADER_ID = PH.PO_HEADER_ID AND
1362 PRD.DISTRIBUTION_ID = PD.REQ_DISTRIBUTION_ID AND
1363 PRD.REQUISITION_LINE_ID = PRL.REQUISITION_LINE_ID AND
1364 PRL.REQUISITION_HEADER_ID = C_REQ_HEADER_ID AND
1365 PH.ORG_ID = HOU.ORGANIZATION_ID (+);
1366
1367 x_org varchar2(80);
1368 x_counter number := 0;
1369 x_org_id number := 0;
1370 x_old_org_id number := 0;
1371 BEGIN
1372 -- retrieve purchasing org id
1373 OPEN c_purchasing_org(p_req_header_id);
1374 LOOP
1375 FETCH c_purchasing_org INTO x_org, x_org_id;
1376 EXIT WHEN c_purchasing_org %NOTFOUND;
1377 if(x_counter = 0) then
1378 x_old_org_id := x_org_id;
1379 x_counter := x_counter + 1;
1380 elsif (x_old_org_id <> x_org_id) then
1381 x_org := 'MULTIPLE_VALUE';
1382 exit;
1383 end if;
1384 END LOOP;
1385 CLOSE c_purchasing_org;
1386 RETURN x_org;
1387 END GET_PURCHASING_ORG;
1388
1389 /**
1390 * This function returns the purchasing organization name
1391 * if there is only one org associated with the requisition;
1392 * or returns 'MULTIPLE' if there is more than one org
1393 * associated with the requisition
1394 */
1395 FUNCTION GET_PURCH_ORG_FOR_LINE(p_req_line_id in number)
1396 RETURN varchar2 IS
1397 CURSOR c_purchasing_org (c_req_line_id NUMBER) IS
1398 SELECT
1399 HOU.NAME ORG_NAME, PH.ORG_ID
1400 FROM
1401 PO_REQ_DISTRIBUTIONS_ALL PRD,
1402 PO_DISTRIBUTIONS_ALL PD,
1403 PO_HEADERS_ALL PH,
1404 HR_ALL_ORGANIZATION_UNITS_VL HOU
1405 WHERE
1406 PD.PO_HEADER_ID = PH.PO_HEADER_ID AND
1407 PRD.DISTRIBUTION_ID = PD.REQ_DISTRIBUTION_ID AND
1408 PRD.REQUISITION_LINE_ID = c_req_line_id AND
1409 PH.ORG_ID = HOU.ORGANIZATION_ID (+);
1410
1411 x_org varchar2(80);
1412 x_counter number := 0;
1413 x_org_id number := 0;
1414 x_old_org_id number := 0;
1415 BEGIN
1416 -- retrieve purchasing org id
1417 OPEN c_purchasing_org(p_req_line_id);
1418 LOOP
1419 FETCH c_purchasing_org INTO x_org, x_org_id;
1420 EXIT WHEN c_purchasing_org %NOTFOUND;
1421 if(x_counter = 0) then
1422 x_old_org_id := x_org_id;
1423 x_counter := x_counter + 1;
1424 elsif (x_old_org_id <> x_org_id) then
1425 x_org := 'MULTIPLE_VALUE';
1426 exit;
1427 end if;
1428 END LOOP;
1429 CLOSE c_purchasing_org;
1430
1431 RETURN x_org;
1432 END GET_PURCH_ORG_FOR_LINE;
1433
1434 function get_labor_line_supplier_name(req_line_id IN number) return varchar2 is
1435 no_of_suppliers number :=0;
1436 x_vendor_id number;
1437 suppliername varchar2(1000);
1438
1439 begin
1440
1441 select count(distinct nvl(vendor_id,0)), min(vendor_id)
1442 into no_of_suppliers, x_vendor_id
1443 from po_requisition_suppliers
1444 where requisition_line_id = req_line_id;
1445
1446 if (no_of_suppliers > 1) then
1447 return 'MULTIPLE_VALUE';
1448 else
1449 if (x_vendor_id is not null) then
1450 select vendor_name
1451 into suppliername
1452 from po_vendors
1453 where vendor_id = x_vendor_id;
1454
1455 return suppliername;
1456 else
1457 return null;
1458 end if;
1459 end if;
1460 end get_labor_line_supplier_name;
1461
1462 /**************************************************************************
1463 * This procedure returns the given req's current approver's full name *
1464 * and email. *
1465 **************************************************************************/
1466 PROCEDURE getCurrentApproverInfo(req_header_id IN NUMBER,
1467 full_name OUT NOCOPY VARCHAR2,
1468 email_address OUT NOCOPY VARCHAR2,
1469 phone OUT NOCOPY VARCHAR2,
1470 date_notified OUT NOCOPY DATE)
1471 IS
1472 l_approver_id NUMBER;
1473 l_procedure_name CONSTANT VARCHAR2(30) := 'getCurrentApproverInfo';
1474 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
1475
1476 BEGIN
1477
1478 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1479
1480 l_approver_id := POR_UTIL_PKG.GET_CURRENT_APPROVER(req_header_id);
1481
1482 IF (nvl(l_approver_id, -1) = -1) THEN
1483 RETURN;
1484 END IF;
1485
1486 SELECT pap.full_name, pap.email_address, (select ph.phone_number from per_phones ph where
1487 ph.parent_table(+) = 'PER_ALL_PEOPLE_F' AND
1488 ph.parent_id (+) = fnd.employee_id AND
1489 ph.phone_type(+) = 'W1' AND
1490 trunc(SYSDATE) BETWEEN nvl(PH.DATE_FROM, trunc(SYSDATE)) AND
1491 nvl(PH.DATE_TO, trunc(SYSDATE)) ) phone_number,
1492 wn.begin_date
1493 INTO full_name, email_address, phone, date_notified
1494 FROM
1495 wf_notifications wn,
1496 wf_notification_attributes wna,
1497 wf_user_roles wlur,
1498 fnd_user fnd,
1499 po_requisition_headers_all prh,
1500 per_all_people_f pap
1501 WHERE
1502 prh.requisition_header_id = req_header_id AND
1503 wlur.user_name = fnd.user_name AND
1504 pap.person_id = fnd.employee_id AND
1505 fnd.employee_id = l_approver_id AND
1506 prh.requisition_header_id = wna.number_value AND
1507 wna.name = 'DOCUMENT_ID' AND
1508 wna.notification_id = wn.notification_id AND
1509 wn.recipient_role = wlur.role_name AND
1510 wn.status = 'OPEN' AND
1511 wn.message_type = prh.wf_item_type AND
1512 wn.message_name IN ('PO_REQ_APPROVE',
1513 'PO_REQ_REMINDER1',
1514 'PO_REQ_APPROVE_WEB_MSG',
1515 'PO_REQ_REMINDER2',
1516 'PO_REQ_REMINDER1_WEB',
1517 'PO_REQ_REMINDER2_WEB',
1518 'PO_REQ_APPROVE_JRAD',
1519 'PO_REQ_APPROVE_SIMPLE',
1520 'PO_REQ_APPROVE_SIMPLE_JRAD',
1521 'PO_REQ_REMINDER1_JRAD',
1522 'PO_REQ_REMINDER2_JRAD')
1523 AND TRUNC(sysdate) between pap.effective_start_date and pap.effective_end_date
1524 AND rownum = 1;
1525
1526 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1527 l_log_msg := 'Name: ' || full_name || ',Email: ' || email_address ||
1528 ',Phone: ' || phone || ',Date Notified: ' || date_notified ;
1529 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||l_procedure_name, l_log_msg);
1530 END IF;
1531
1532 EXCEPTION
1533 when others then
1534
1535 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
1536 l_log_msg := 'Error in getCurrentApproverInfo. SQLERRM= ' || SQLERRM;
1537 FND_LOG.STRING(G_LEVEL_EXCEPTION, G_MODULE_NAME||l_procedure_name, l_log_msg);
1538 END IF;
1539
1540 full_name := null;
1541 email_address := null;
1542 phone := null;
1543 date_notified := null;
1544
1545 END getCurrentApproverInfo;
1546
1547 end por_view_reqs_pkg;
[Home] [Help]