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