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