1 PACKAGE BODY RCV_DISTRIBUTIONS_S AS
2 /* $Header: RCVTXDIB.pls 120.9 2010/10/19 03:36:54 vthevark ship $*/
3
4
5
6
7 /*===========================================================================
8
9 PROCEDURE NAME: get_distributions_info
10
11 ===========================================================================*/
12
13
14 PROCEDURE get_distributions_info
15 (X_line_location_id IN NUMBER,
16 X_shipment_line_id IN NUMBER,
17 X_item_id IN NUMBER,
18 X_num_of_distributions IN OUT NOCOPY NUMBER,
19 X_po_distributions_id OUT NOCOPY NUMBER,
20 X_destination_type_code IN OUT NOCOPY VARCHAR2,
21 X_destination_type_dsp OUT NOCOPY VARCHAR2,
22 X_deliver_to_location_id IN OUT NOCOPY NUMBER,
23 X_deliver_to_location OUT NOCOPY VARCHAR2,
24 X_deliver_to_person_id IN OUT NOCOPY NUMBER,
25 X_deliver_to_person OUT NOCOPY VARCHAR2,
26 X_deliver_to_sub IN OUT NOCOPY VARCHAR2,
27 X_deliver_to_locator_id OUT NOCOPY NUMBER,
28 X_deliver_to_locator OUT NOCOPY VARCHAR2,
29 X_wip_entity_id IN OUT NOCOPY NUMBER,
30 X_wip_repetitive_schedule_id IN OUT NOCOPY NUMBER,
31 X_wip_line_id IN OUT NOCOPY NUMBER,
32 X_wip_operation_seq_num IN OUT NOCOPY NUMBER,
33 X_wip_resource_seq_num IN OUT NOCOPY NUMBER,
34 X_bom_resource_id IN OUT NOCOPY NUMBER,
35 X_to_organization_id IN OUT NOCOPY NUMBER,
36 X_job IN OUT NOCOPY VARCHAR2,
37 X_line_num IN OUT NOCOPY VARCHAR2,
38 X_sequence IN OUT NOCOPY NUMBER,
39 X_department IN OUT NOCOPY VARCHAR2,
40 X_rate IN OUT NOCOPY NUMBER,
41 X_rate_date IN OUT NOCOPY DATE,
42 -- <RCV ENH FPI START>
43 x_kanban_card_number OUT NOCOPY VARCHAR2,
44 x_project_number OUT NOCOPY VARCHAR2,
45 x_task_number OUT NOCOPY VARCHAR2,
46 x_charge_account OUT NOCOPY VARCHAR2
47 -- <RCV ENH FPI END>
48 ) IS
49
50 X_progress VARCHAR2(3) := NULL;
51
52 X_valid_ship_to_location BOOLEAN;
53 X_valid_deliver_to_location BOOLEAN;
54 X_valid_deliver_to_person BOOLEAN;
55 X_valid_subinventory BOOLEAN;
56
57 -- <RCV ENH FPI START>
58 l_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
59 l_code_combination_id PO_DISTRIBUTIONS.code_combination_id%TYPE;
60 -- <RCV ENH FPI END>
61
62 /* Bug 3816908 : Variable added */
63 l_lookup_type po_lookup_codes.lookup_type%TYPE;
64
65 x_project_id PO_DISTRIBUTIONS.project_id%type; -- bug 3867151
66 x_task_id PO_DISTRIBUTIONS.task_id%type; -- bug 3867151
67 l_clm_document VARCHAR2(1) := 'N'; -- Whether its a CLM document or Not
68
69 l_asn_line_flag rcv_shipment_lines.asn_line_flag%TYPE ; -- bug 9742420
70 l_po_distribution_id po_distributions_all.po_distribution_id%TYPE; --bug 9742420
71
72 BEGIN
73
74 X_progress := '010';
75
76 /* Get the number of distributions so as to know whether to return
77 one or to just show multiple as the return value
78 */
79 SELECT count(po_distribution_id)
80 INTO X_num_of_distributions
81 FROM po_distributions
82 WHERE line_location_id = X_line_location_id;
83
84 /* Bug 9742420 when asn receipt on multiple distribion,
85 each asn create one shipment line,
86 we should not regard the ASN shipment line as multiple lines.
87 */
88
89 IF X_num_of_distributions >1 AND X_SHIPMENT_LINE_ID IS NOT NULL THEN
90 SELECT Nvl(asn_line_flag,'N'),
91 po_distribution_id
92 INTO l_asn_line_flag,
93 l_po_distribution_id
94 FROM rcv_shipment_lines
95 WHERE shipment_line_id = X_SHIPMENT_LINE_ID ;
96
97 IF l_asn_line_flag = 'Y' THEN
98 IF X_po_distributions_id IS NULL THEN
99 X_po_distributions_id := l_po_distribution_id;
100 END IF;
101
102 IF X_po_distributions_id IS NOT NULL THEN
103 X_num_of_distributions :=1 ;
104 END IF;
105 END IF;
106 END IF ;
107
108 /*End Bug 9742420 */
109
110 -- CLM Changes start
111 Begin
112 SELECT 'Y' into l_clm_document
113 FROM po_headers_all h,
114 po_doc_style_headers s
115 WHERE h.po_header_id = ( SELECT MIN(PO_HEADER_ID)
116 FROM po_distributions
117 WHERE line_location_id = X_line_location_id
118 )
119 AND h.style_id = s.style_id
120 AND Nvl(s.clm_flag,'N') = 'Y';
121 Exception
122 When No_Data_Found Then
123 l_clm_document := 'N';
124 End;
125 -- CLM Changes End
126
127
128 /* If there are no distributions for this line_location_id then you
129 must have passed a bad value. We should raise an error for this
130 */
131 IF X_num_of_distributions = 0 THEN
132
133 X_progress := '011';
134
135 IF (l_clm_document='N') THEN
136 po_message_s.sql_error ('get_distribution_info', X_progress, SQLCODE);
137 RAISE NO_DATA_FOUND;
138 END IF;
139
140 /* If there is one distribution then go ahead and fetch the destination
141 from the po_distributions table
142 */
143 ELSIF X_num_of_distributions = 1 THEN
144
145 X_progress := '020';
146
147 /* Bug# 1808822 - HR_LOCATIONS view is non-mergable so it is replaced by
148 hr_locations_all_tl and the condition hl.language(+) = USERENV('LANG')
149 is added. Since only location_code is selected, there is no need of
150 joining with the tables hr_locations_all and hz_locations. */
151
152 SELECT pod.po_distribution_id,
153 pod.destination_type_code,
154 pod.deliver_to_location_id,
155 hl.location_code,
156 pod.deliver_to_person_id,
157 pod.destination_subinventory,
158 pod.wip_entity_id,
159 pod.WIP_REPETITIVE_SCHEDULE_ID,
160 pod.WIP_LINE_ID,
161 pod.WIP_OPERATION_SEQ_NUM,
162 pod.WIP_RESOURCE_SEQ_NUM,
163 pod.BOM_RESOURCE_ID,
164 pod.destination_organization_id,
165 round(pod.rate,28),
166 pod.rate_date,
167 mkc.kanban_card_number, -- <RCV ENH FPI>
168 pod.project_id, -- bug 3867151
169 pod.task_id, -- bug 3867151
170 pod.code_combination_id -- <RCV ENH FPI>
171 INTO X_po_distributions_id,
172 X_destination_type_code,
173 X_deliver_to_location_id,
174 X_deliver_to_location,
175 X_deliver_to_person_id,
176 X_deliver_to_sub,
177 X_wip_entity_id,
178 X_wip_repetitive_schedule_id,
179 X_wip_line_id,
180 X_wip_operation_seq_num,
181 X_wip_resource_seq_num,
182 X_bom_resource_id,
183 X_to_organization_id,
184 X_rate,
185 X_rate_date,
186 x_kanban_card_number, -- <RCV ENH FPI>
187 x_project_id, -- <RCV ENH FPI>Bug 4684017 The variable should be x_project_id and not x_project_number
188 x_task_id, -- <RCV ENH FPI>Bug 4684017 The variable should be x_project_id and not x_project_number
189 l_code_combination_id -- <RCV ENH FPI>
190 FROM po_distributions pod,
191 hr_locations_all_tl hl,
192 mtl_kanban_cards mkc -- <RCV ENH FPI>
193 WHERE pod.line_location_id = X_line_location_id
194 AND pod.po_distribution_id = Nvl(X_PO_DISTRIBUTIONS_ID,pod.po_distribution_id) -- bug 9742420
195 AND hl.location_id(+) = pod.deliver_to_location_id
196 AND hl.language(+) = USERENV('LANG')
197 AND pod.kanban_card_id = mkc.kanban_card_id (+); -- <RCV ENH FPI>
198
199
200 /* Bug 3867151 START
201 Due to performance problems because of outer joins on project_id and
202 task_id related conditions in the above sql, writing a separate select
203 to retrieve the project and task numbers. This sql will be executed
204 only when project/task references are there in the PO distribution.
205 */
206
207 x_progress := 21;
208
209 IF (x_project_id IS NOT NULL) THEN
210 BEGIN
211 /*Bugfix5217513 SQLID:17869796 Rewritten queries
212 SELECT ppa.project_number,
213 pte.task_number
214 INTO x_project_number,
215 x_task_number
216 FROM pjm_projects_all_v ppa,
217 pa_tasks_expend_v pte
218 WHERE ppa.project_id = x_project_id
219 AND pte.task_id = x_task_id
220 AND ppa.project_id = pte.project_id;*/
221
222 /* Bug 5290928: Added condition task_id not null.
223 If the organization is only project controlled,
224 task is not mandatory. */
225 IF x_task_id IS NOT NULL THEN
226 select P.SEGMENT1 PROJECT_NUMBER ,
227 T.TASK_NUMBER
228 into x_project_number,
229 x_task_number
230 from PA_PROJECTS_ALL p,
231 PA_TASKS T
232 where P.PROJECT_ID = T.PROJECT_ID
233 and p.project_id = x_project_id
234 and T.task_id = x_task_id;
235 ELSE
236 select SEGMENT1 PROJECT_NUMBER
237 into x_project_number
238 from PA_PROJECTS_ALL
239 where project_id = x_project_id;
240 END IF;
241
242 EXCEPTION
243 WHEN NO_DATA_FOUND THEN
244 BEGIN
245 select PROJECT_NUMBER
246 into x_project_number
247 from PJM_SEIBAN_NUMBERS
248 where project_id = x_project_id;
249 EXCEPTION
250 WHEN NO_DATA_FOUND THEN
251 x_project_number := NULL;
252 x_task_number := NULL;
253 END;
254 END;
255 ELSE
256 x_project_number := NULL;
257 x_task_number := NULL;
258 END IF;
259
260 /* Bug 3867151 END */
261
262 X_deliver_to_person :=
263 po_inq_sv.get_person_name(X_deliver_to_person_id);
264
265 -- <RCV ENH FPI START>
266 x_progress := '025';
267
268 x_charge_account :=
269 PO_COMPARE_REVISIONS.get_charge_account(l_code_combination_id);
270 -- <RCV ENH FPI END>
271
272 /*
273 ** Make sure this information is still valid
274 */
275 rcv_transactions_sv.val_destination_info (
276 X_to_organization_id,
277 X_item_id,
278 NULL,
279 X_deliver_to_location_id,
280 X_deliver_to_person_id,
281 X_deliver_to_sub,
282 X_valid_ship_to_location,
283 X_valid_deliver_to_location,
284 X_valid_deliver_to_person,
285 X_valid_subinventory);
286
287 IF (NOT X_valid_deliver_to_location) THEN
288
289 X_deliver_to_location_id := NULL;
290 X_deliver_to_location := NULL;
291
292 END IF;
293
294 IF (NOT X_valid_deliver_to_person) THEN
295
296 X_deliver_to_person_id := NULL;
297 X_deliver_to_person := NULL;
298
299 END IF;
300
301 IF (NOT X_valid_subinventory) THEN
302
303 X_deliver_to_sub := NULL;
304
305 END IF;
306
307 /*
308 ** Check to see if you have a wip entity id. If you do then do get the
309 ** wip info for that distribution
310 */
311 IF (x_wip_entity_id > 0) THEN
312
313 rcv_transactions_sv.get_wip_info
314 (X_wip_entity_id,
315 X_wip_repetitive_schedule_id,
316 X_wip_line_id,
317 X_wip_operation_seq_num,
318 X_wip_resource_seq_num,
319 X_to_organization_id,
320 X_job,
321 X_line_num,
322 X_sequence,
323 X_department);
324
325 END IF;
326
327 /* If there is more than one distribution for a given line then
328 just return the status that there are multiple distributions
329 */
330 ELSE
331
332 -- <RCV ENH FPI START>
333 x_progress := '028';
334
335 RCV_DISTRIBUTIONS_S.get_misc_distr_info (
336 x_return_status => l_status,
337 p_line_location_id => x_line_location_id,
338 p_po_distribution_id => NULL,
339 x_kanban_card_number => x_kanban_card_number,
340 x_project_number => x_project_number,
341 x_task_number => x_task_number,
342 x_charge_account => x_charge_account,
343 x_deliver_to_person => x_deliver_to_person,
344 x_job => x_job,
345 x_outside_line_num => x_line_num,
346 x_sequence => x_sequence,
347 x_department => x_department,
348 x_dest_subinv => x_deliver_to_sub,
349 x_rate => x_rate,
350 x_rate_date => x_rate_date);
351
352 IF (l_status <> FND_API.G_RET_STS_SUCCESS) THEN
353 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
354 END IF;
355 -- <RCV ENH FPI END>
356
357 X_destination_type_code := 'MULTIPLE';
358
359 END IF;
360
361 /* If you have at least one distribution then go get the display
362 value for that destination type from the lookups table
363 */
364 IF X_num_of_distributions > 0 THEN
365
366 X_progress := '030';
367 /* Bug 3816908: Replaced hardcoded literals for lookup_type with
368 * bind variables.
369 */
370
371 l_lookup_type := 'RCV DESTINATION TYPE';
372
373 SELECT displayed_field
374 INTO X_destination_type_dsp
375 FROM po_lookup_codes
376 WHERE lookup_type = l_lookup_type
377 AND lookup_code = X_destination_type_code;
378
379 END IF;
380
381
382 EXCEPTION
383 WHEN OTHERS THEN
384 po_message_s.sql_error ('get_distribution_info', X_progress, SQLCODE);
385 RAISE;
386
387 END get_distributions_info;
388
389 /*===========================================================================
390
391 PROCEDURE NAME: test_rcv_distributions_s
392
393 ===========================================================================*/
394
395 PROCEDURE test_rcv_distributions_s
396 (X_line_location_id IN NUMBER,
397 X_shipment_line_id IN NUMBER,
398 X_item_id IN NUMBER ) IS
399
400 X_num_of_distributions NUMBER;
401 X_po_distributions_id NUMBER;
402 X_destination_type_code VARCHAR2(30);
403 X_destination_type_dsp VARCHAR2(80);
404 X_deliver_to_location_id NUMBER;
405
406 /** PO UTF8 Column Expansion Project 9/23/2002 tpoon **/
407 /** Changed X_deliver_to_location to use %TYPE **/
408 -- X_deliver_to_location VARCHAR2(30);
409 X_deliver_to_location hr_locations_all.location_code%TYPE;
410
411 X_deliver_to_person_id NUMBER;
412 X_deliver_to_person VARCHAR2(30);
413 X_deliver_to_sub VARCHAR2(30);
414 X_deliver_to_locator_id NUMBER;
415 X_deliver_to_locator VARCHAR2(30);
416 X_wip_entity_id NUMBER;
417 X_wip_repetitive_schedule_id NUMBER;
418 X_wip_line_id NUMBER;
419 X_wip_operation_seq_num NUMBER;
420 X_wip_resource_seq_num NUMBER;
421 X_bom_resource_id NUMBER;
422 X_to_organization_id NUMBER;
423 X_job VARCHAR2(240);
424 X_line_num VARCHAR2(10);
425 X_sequence NUMBER;
426 X_department VARCHAR2(10);
427 X_rate NUMBER;
428 X_rate_date DATE;
429
430 BEGIN
431
432 /* Test the get_distributions_info procedure */
433
434 -- <RCV ENH FPI START>
435 -- Remove the get_distributions_info call because this procedure is not
436 -- used anywhere, and the package will not compile if we do not remove
437 -- the call.
438
439 NULL;
440 -- RCV_DISTRIBUTIONS_S.get_distributions_info
441 -- (X_line_location_id, X_shipment_line_id, X_item_id,
442 -- X_num_of_distributions,
443 -- X_po_distributions_id, X_destination_type_code, X_destination_type_dsp,
444 -- X_deliver_to_location_id, X_deliver_to_location,
445 -- X_deliver_to_person_id, X_deliver_to_person, X_deliver_to_sub,
446 -- X_deliver_to_locator_id, X_deliver_to_locator,X_wip_entity_id,
447 -- X_wip_repetitive_schedule_id,X_wip_line_id,X_wip_operation_seq_num ,
448 -- X_wip_resource_seq_num , X_bom_resource_id ,
449 -- X_to_organization_id , X_job , X_line_num , X_sequence ,
450 -- X_department, X_rate, X_rate_date
451 -- );
452
453 -- <RCV ENH FPI END>
454
455 /* Print the results of the test */
456 /*dbms_output.put_line('Line Location Id: ' ||
457 to_char(X_line_location_id));
458 dbms_output.put_line('Shipment Id: ' ||
459 to_char(X_shipment_line_id));
460 dbms_output.put_line('Num of Dist: ' ||
461 to_char(X_num_of_distributions));
462 dbms_output.put_line('Dist ID: ' ||
463 to_char(X_po_distributions_id));
464 dbms_output.put_line('Dest Type Code: ' ||
465 X_destination_type_code);
466 dbms_output.put_line('Dest Type Dsp: ' ||
467 X_destination_type_dsp);
468 dbms_output.put_line('Deliver To Location Id: ' ||
469 to_char(X_deliver_to_location_id));
470 dbms_output.put_line('Deliver To Loc: ' ||
471 X_deliver_to_location);
472 dbms_output.put_line('Deliver To Person Id: ' ||
473 to_char(X_deliver_to_person_id));
474 dbms_output.put_line('Deliver To Person: ' ||
475 X_deliver_to_person);
476 dbms_output.put_line('Deliver To Sub: ' ||
477 X_deliver_to_sub);
478 dbms_output.put_line('Deliver To Locator Id: ' ||
479 to_char(X_deliver_to_locator_id));
480 dbms_output.put_line('Deliver To Locator: ' ||
481 X_deliver_to_locator);
482 dbms_output.put_line('X_wip_entity_id: ' ||
483 to_char(X_wip_entity_id));
484 dbms_output.put_line('X_wip_repetitive_schedule_id: ' ||
485 to_char(X_wip_repetitive_schedule_id));
486 dbms_output.put_line('X_wip_line_id: ' ||
487 to_char(X_wip_line_id));
488 dbms_output.put_line('X_wip_operation_seq_num: ' ||
489 to_char(X_wip_operation_seq_num));
490 dbms_output.put_line('X_wip_resource_seq_num: ' ||
491 to_char(X_wip_resource_seq_num));
492 dbms_output.put_line('X_bom_resource_id: ' ||
493 to_char(X_bom_resource_id));
494 dbms_output.put_line('X_to_organization_id: ' ||
495 to_char(X_to_organization_id));
496 dbms_output.put_line('X_job: ' ||
497 X_job);
498 dbms_output.put_line('X_line_num: ' ||
499 X_line_num);
500 dbms_output.put_line('X_sequence: ' ||
501 to_char(X_sequence));
502 dbms_output.put_line('X_department: ' ||
503 X_department);
504 dbms_output.put_line('X_rate: ' || to_char(x_rate));
505 dbms_output.put_line('X_rate_date: ' || to_char(x_rate_date,'dd-mon-yyyy'));
506 */
507 END test_rcv_distributions_s;
508
509
510 -- <RCV ENH FPI START>
511
512 /**
513 * Public Procedure: get_misc_distr_info
514 * Requires: p_line_location_id and p_po_distribution_id should be valid
515 * values in PO_LINE_LOCATIONS and PO_DISTRIBUTIONS
516 * Modifies: None
517 * Effects: if p_line_location_id is given and po_distribution_id is NULL,
518 * then it returns distribution information (kanban_card_number,
519 * project_number, etc.) If there is only 1 distribution. If there
520 * are multiple distributions, return 'Multiple' if not all
521 * distributions have null value. If
522 * po_distribution_id is given, then return distribution information
523 * based on that distribution id.
524 * Returns:
525 * x_return_status:
526 * FND_API.G_RET_STS_SUCCESS if no error occurs
527 * FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs (e.g.,
528 * line_location_id does not exist in PO_LINE_LOCATIONS)
529 * x_kanban_card_number:
530 * Kanban Card Number. NULL if distribution does not contain kanban card info
531 * x_project_number:
532 * Project Number. NULL if distribution does not contain project info.
533 * x_task_number:
534 * Task Number. NULL if distribution does not contain task info.
535 * x_charge_account:
536 * Concatenated Charge Account Segments.
537 * x_deliver_to_person:
538 * Requestor. NULL if distribution does not contain requestor info.
539 * x_job
540 * Job/Schedule. Null if distribution does not contain such info.
541 * x_outside_line_num
542 * Outside Processing related Display Info
543 * x_sequence
544 * Outside Processing related Display Info
545 * x_department
546 * Outside Processing related Display Info
547 * x_rate
548 * Currency rate
549 * x_rate_date
550 * Currency rate date
551 */
552
553 PROCEDURE get_misc_distr_info
554 (x_return_status OUT NOCOPY VARCHAR2,
555 p_line_location_id IN NUMBER,
556 p_po_distribution_id IN NUMBER,
557 x_kanban_card_number OUT NOCOPY VARCHAR2,
558 x_project_number OUT NOCOPY VARCHAR2,
559 x_task_number OUT NOCOPY VARCHAR2,
560 x_charge_account OUT NOCOPY VARCHAR2,
561 x_deliver_to_person OUT NOCOPY VARCHAR2,
562 x_job OUT NOCOPY VARCHAR2,
563 x_outside_line_num OUT NOCOPY VARCHAR2,
564 x_sequence OUT NOCOPY NUMBER,
565 x_department OUT NOCOPY VARCHAR2,
566 x_dest_subinv OUT NOCOPY VARCHAR2,
567 x_rate OUT NOCOPY NUMBER,
568 x_rate_date OUT NOCOPY DATE) IS
569
570 l_api_name CONSTANT VARCHAR2(50) := 'get_misc_distr_info';
571
572 l_num_distributions NUMBER;
573 l_num_projs NUMBER;
574 l_num_tasks NUMBER;
575 l_num_kanban_cards NUMBER;
576 l_num_charge_accts NUMBER;
577 l_num_requestors NUMBER;
578 l_num_jobs NUMBER;
579 l_num_rates NUMBER;
580 l_num_rate_dates NUMBER;
581 l_num_dest_subinv NUMBER;
582
583 --Bugfix5217513: Introduced Variables.
584 l_project_id NUMBER;
585 l_task_id NUMBER;
586
587 l_wip_entity_id PO_DISTRIBUTIONS.wip_entity_id%TYPE;
588 l_wip_rep_schedule_id PO_DISTRIBUTIONS.wip_repetitive_schedule_id%TYPE;
589 l_wip_line_id PO_DISTRIBUTIONS.wip_line_id%TYPE;
590 l_wip_operation_seq_num PO_DISTRIBUTIONS.wip_operation_seq_num%TYPE;
591 l_wip_resource_seq_num PO_DISTRIBUTIONS.wip_resource_seq_num%TYPE;
592 l_to_organization_id PO_DISTRIBUTIONS.destination_organization_id%TYPE;
593
594 l_multi_distr VARCHAR2(1) := FND_API.G_FALSE;
595 l_multiple_msg VARCHAR2(2000);
596
597 l_progress VARCHAR2(3);
598
599 BEGIN
600
601 x_return_status := FND_API.G_RET_STS_SUCCESS;
602
603 l_progress := '000';
604
605 IF (p_line_location_id IS NULL) THEN
606 RETURN;
607 END IF;
608
609 IF (p_po_distribution_id IS NULL) THEN
610 SELECT COUNT(po_distribution_id),
611 COUNT(kanban_card_id),
612 COUNT(project_id),
613 COUNT(task_id),
614 COUNT(code_combination_id),
615 COUNT(deliver_to_person_id),
616 COUNT(wip_entity_id),
617 COUNT(rate),
618 COUNT(rate_date),
619 COUNT(destination_subinventory)
620 INTO l_num_distributions,
621 l_num_kanban_cards,
622 l_num_projs,
623 l_num_tasks,
624 l_num_charge_accts,
625 l_num_requestors,
626 l_num_jobs,
627 l_num_rates,
628 l_num_rate_dates,
629 l_num_dest_subinv
630 FROM po_distributions
631 WHERE line_location_id = p_line_location_id;
632
633 IF (l_num_distributions > 1) THEN
634
635 l_progress := '010';
636
637 l_multi_distr := FND_API.G_TRUE;
638
639 FND_MESSAGE.set_name('PO', 'PO_MULTI_DEST_INFO');
640 l_multiple_msg := FND_MESSAGE.get;
641
642 IF (l_num_projs > 0) THEN
643 x_project_number := l_multiple_msg;
644 END IF;
645
646 IF (l_num_tasks > 0) THEN
647 x_task_number := l_multiple_msg;
648 END IF;
649
650 IF (l_num_kanban_cards > 0) THEN
651 x_kanban_card_number := l_multiple_msg;
652 END IF;
653
654 IF (l_num_charge_accts > 0) THEN
655 x_charge_account := l_multiple_msg;
656 END IF;
657
658 IF (l_num_requestors > 0) THEN
659 x_deliver_to_person := l_multiple_msg;
660 END IF;
661
662 IF (l_num_jobs > 0) THEN
663 x_job := l_multiple_msg;
664 END IF;
665
666 IF (l_num_dest_subinv > 0) THEN
667 x_dest_subinv := l_multiple_msg;
668 END IF;
669 END IF;
670 END IF;
671
672 IF (l_multi_distr = FND_API.G_FALSE) THEN
673
674 l_progress := '020';
675
676 --SQL What: Retreive Distribution Information by distribution_id
677 --SQL or line_location_id having only 1 distribution
678 --SQL Why: These are the return values of this procedure
679 --SQL Join: POD and PPA: project_id
680 --SQL POD and PTE: task_id
681 --SQL POD and MKC: kanban_card_id
682 --Bugfix5217513 SQLID: 17869745
683 SELECT MKC.kanban_card_number,
684 --PPA.project_number,
685 --PTE.task_number,
686 POD.project_id,
687 POD.task_id,
688 PO_COMPARE_REVISIONS.get_charge_account(POD.code_combination_id),
689 PO_INQ_SV.get_person_name(POD.deliver_to_person_id),
690 POD.destination_subinventory,
691 POD.wip_entity_id,
692 POD.wip_repetitive_schedule_id,
693 POD.wip_line_id,
694 POD.wip_operation_seq_num,
695 POD.wip_resource_seq_num,
696 POD.destination_organization_id,
697 ROUND(POD.rate, 28),
698 POD.rate_date
699 INTO x_kanban_card_number,
700 --x_project_number,
701 --x_task_number,
702 l_project_id,
703 l_task_id,
704 x_charge_account,
705 x_deliver_to_person,
706 x_dest_subinv,
707 l_wip_entity_id,
708 l_wip_rep_schedule_id,
709 l_wip_line_id,
710 l_wip_operation_seq_num,
711 l_wip_resource_seq_num,
712 l_to_organization_id,
713 x_rate,
714 x_rate_date
715 FROM po_distributions_all POD, -- Bug 10203707
716 --pjm_projects_all_v PPA,
717 --pa_tasks_expend_v PTE,
718 mtl_kanban_cards MKC
719 WHERE POD.po_distribution_id = NVL(p_po_distribution_id,
720 POD.po_distribution_id)
721 AND POD.line_location_id = p_line_location_id
722 --AND POD.project_id = PPA.project_id (+)
723 --AND POD.task_id = PTE.task_id (+)
724 AND POD.kanban_card_id = MKC.kanban_card_id (+);
725
726 IF l_project_id IS NOT NULL THEN
727 BEGIN
728 /* Bug 5290928: Added condition task_id not null.*/
729 IF l_task_id IS NOT NULL THEN
730 select P.SEGMENT1 PROJECT_NUMBER ,
731 T.TASK_NUMBER
732 into x_project_number,
733 x_task_number
734 from PA_PROJECTS_ALL p,
735 PA_TASKS T
736 where P.PROJECT_ID = T.PROJECT_ID
737 and p.project_id = l_project_id
738 and T.task_id = l_task_id;
739 ELSE
740 select SEGMENT1 PROJECT_NUMBER
741 into x_project_number
742 from PA_PROJECTS_ALL
743 where project_id = l_project_id;
744 END IF;
745
746 EXCEPTION
747 WHEN NO_DATA_FOUND THEN
748 BEGIN
749 select PROJECT_NUMBER
750 into x_project_number
751 from PJM_SEIBAN_NUMBERS
752 where project_id = l_project_id;
753 EXCEPTION
754 WHEN NO_DATA_FOUND THEN
755 x_project_number := NULL;
756 x_task_number := NULL;
757 END;
758 END;
759 ELSE
760 x_project_number := NULL;
761 x_task_number := NULL;
762 END IF;
763
764 /*
765 ** Check to see if you have a wip entity id. If you do then do get the
766 ** wip info for that distribution
767 */
768 IF (l_wip_entity_id > 0) THEN
769 l_progress := '030';
770 rcv_transactions_sv.get_wip_info
771 (l_wip_entity_id,
772 l_wip_rep_schedule_id,
773 l_wip_line_id,
774 l_wip_operation_seq_num,
775 l_wip_resource_seq_num,
776 l_to_organization_id,
777 X_job,
778 X_outside_line_num,
779 X_sequence,
780 X_department);
781 END IF;
782
783 l_progress := '040';
784
785 END IF;
786
787 EXCEPTION
788 WHEN OTHERS THEN
789 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
790 PO_MESSAGE_S.sql_error (l_api_name, l_progress, SQLCODE);
791 END get_misc_distr_info;
792
793
794 /**
795 * Public Procedure: get_misc_req_distr_info
796 * Requires: p_requisition_line_id and p_req_distribution_id should be valid
797 * values in PO_REQUISITION_LINES and PO_REQ_DISTRIBUTIONS
798 * Modifies: None
799 * Effects: get kanban card information from req line, project, task and
800 * charge account information from req distribution
801 * Returns:
802 * x_return_status:
803 * FND_API.G_RET_STS_SUCCESS if no error occurs
804 * FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs (e.g.,
805 * line_location_id does not exist in PO_LINE_LOCATIONS)
806 * x_kanban_card_number:
807 * Kanban Card Number. NULL if distribution does not contain kanban card info
808 * x_project_number:
809 * Project Number. NULL if distribution does not contain project info.
810 * x_task_number:
811 * Task Number. NULL if distribution does not contain task info.
812 * x_charge_account:
813 * Concatenated Charge Account Segments.
814 * x_deliver_to_person:
815 * Requestor
816 * x_dest_subinv:
817 * Subinventory defined in Requisition
818 */
819
820 PROCEDURE get_misc_req_distr_info
821 (x_return_status OUT NOCOPY VARCHAR2,
822 p_requisition_line_id IN NUMBER,
823 p_req_distribution_id IN NUMBER,
824 x_kanban_card_number OUT NOCOPY VARCHAR2,
825 x_project_number OUT NOCOPY VARCHAR2,
826 x_task_number OUT NOCOPY VARCHAR2,
827 x_charge_account OUT NOCOPY VARCHAR2,
828 x_deliver_to_person OUT NOCOPY VARCHAR2,
829 x_dest_subinv OUT NOCOPY VARCHAR2) IS
830
831 l_api_name CONSTANT VARCHAR2(50) := 'get_misc_req_distr_info';
832 l_progress VARCHAR2(3) := '000';
833 x_project_id PO_REQ_DISTRIBUTIONS.project_id%type; -- bug 3867151
834 x_task_id PO_REQ_DISTRIBUTIONS.task_id%type; -- bug 3867151
835 BEGIN
836 x_return_status := FND_API.G_RET_STS_SUCCESS;
837
838 IF (p_requisition_line_id IS NOT NULL) THEN
839 l_progress := '010';
840
841 --SQL What: Retreive information from requisition line and distribution. If no
842 --SQL distribution id then use req_line_id only because there is
843 --SQL always one and only one distribution per req line for
844 --SQL an internal req.
845 --SQL Why: Return values of this procedure
846 --SQL Join: PRL and PRD: requisition_line_id
847 --SQL PRL and MKC: kanban_card_id
848 --SQL PRD and PPA: project_id
849 --SQL PRD and PTE: task_id
850
851 SELECT MKC.kanban_card_number,
852 PO_INQ_SV.get_person_name(PRL.to_person_id),
853 PRL.destination_subinventory,
854 PRD.project_id, -- bug 3867151
855 PRD.task_id, -- bug 3867151
856 PO_COMPARE_REVISIONS.get_charge_account(PRD.code_combination_id)
857 INTO x_kanban_card_number,
858 x_deliver_to_person,
859 x_dest_subinv,
860 x_project_id, -- bug 3867151
861 x_task_id, -- bug 3867151
862 x_charge_account
863 FROM po_requisition_lines PRL,
864 po_req_distributions PRD,
865 mtl_kanban_cards MKC
866 WHERE PRL.requisition_line_id = p_requisition_line_id
867 AND PRL.requisition_line_id = PRD.requisition_line_id
868 AND PRD.distribution_id = NVL(p_req_distribution_id,PRD.distribution_id)
869 AND PRL.kanban_card_id = MKC.kanban_card_id (+);
870 END IF;
871
872 l_progress := '020';
873
874 /* Bug 3867151 START
875 Due to performance problems because of outer joins on project_id and
876 task_id related conditions in the above sql, writing a separate select
877 to retrieve the project and task numbers. This sql will be executed
878 only when project/task references are there in the PO distribution.
879 */
880 IF (x_project_id IS NOT NULL) THEN
881 BEGIN
882 /*Bugfix 5217513: SQLID 17869671 Rewritten queries.
883 SELECT ppa.project_number,
884 pte.task_number
885 INTO x_project_number,
886 x_task_number
887 FROM pjm_projects_all_v ppa,
888 pa_tasks_expend_v pte
889 WHERE ppa.project_id = x_project_id
890 AND pte.task_id = x_task_id
891 AND ppa.project_id = pte.project_id;*/
892
893 /* Bug 5290928: Added condition task_id not null.*/
894 IF x_task_id IS NOT NULL THEN
895 select P.SEGMENT1 PROJECT_NUMBER ,
896 T.TASK_NUMBER
897 into x_project_number,
898 x_task_number
899 from PA_PROJECTS_ALL p,
900 PA_TASKS T
901 where P.PROJECT_ID = T.PROJECT_ID
902 and p.project_id = x_project_id
903 and T.task_id = x_task_id;
904 ELSE
905 select SEGMENT1 PROJECT_NUMBER
906 into x_project_number
907 from PA_PROJECTS_ALL
908 where project_id = x_project_id;
909 END IF;
910
911 EXCEPTION
912 WHEN NO_DATA_FOUND THEN
913 BEGIN
914 select PROJECT_NUMBER
915 into x_project_number
916 from PJM_SEIBAN_NUMBERS
917 where project_id = x_project_id;
918 EXCEPTION
919 WHEN NO_DATA_FOUND THEN
920 x_project_number := NULL;
921 x_task_number := NULL;
922 END;
923 END;
924 ELSE
925 x_project_number := NULL;
926 x_task_number := NULL;
927 END IF;
928 /* Bug 3867151 END */
929
930 EXCEPTION
931 WHEN OTHERS THEN
932 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
933 PO_MESSAGE_S.sql_error (l_api_name, l_progress, SQLCODE);
934 END get_misc_req_distr_info;
935
936 -- <RCV ENH FPI END>
937
938 END RCV_DISTRIBUTIONS_S;
[Home] [Help]