1 PACKAGE BODY inv_project AS
2 /* $Header: INVPRJIB.pls 120.10 2006/09/04 10:48:34 ramarava noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'INV_PROJECT';
5
6 G_DISPLAYED_SEGMENTS VARCHAR2(1000);
7 G_CONCATENATED_SEGMENTS VARCHAR2(1000);
8 G_PHYSICAL_LOCATOR VARCHAR2(1000);
9 G_PROJECT_COLUMN NUMBER;
10 G_TASK_COLUMN NUMBER;
11 G_PROJECT_NUMBER VARCHAR2(30);
12 G_PROJECT_ID NUMBER;
13 G_TASK_ID NUMBER;
14 G_TASK_NUMBER VARCHAR2(30);
15
16 G_LOC_CONC_QRY VARCHAR2(1000);
17 G_CONCATENATED_LOCATOR VARCHAR2(1000);
18 G_LOC_QRY_BLT VARCHAR2(1) := 'N';
19 /*For performace enhancements*/
20 g_delimiter VARCHAR2(1);
21 g_project_index NUMBER;
22 g_task_index NUMBER;
23 g_organization_id NUMBER;
24
25
26 PROCEDURE MYDEBUG(MSG IN VARCHAR2) IS
27
28 L_MSG VARCHAR2(5100);
29 l_len number;
30 l_count number;
31 l_start number;
32 l_subs varchar2(200);
33 BEGIN
34
35 L_MSG := MSG;
36
37 /* INV_MOBILE_HELPER_FUNCTIONS.TRACELOG(
38 P_ERR_MSG => L_MSG,
39 P_MODULE => G_PKG_NAME,
40 P_LEVEL => 4
41 );
42
43 l_len := length(l_msg);
44 l_count := floor(l_len/100) + 1;
45 l_start := 1;
46 for i in 1 .. l_count
47 loop
48 l_subs := substr(l_msg, l_start, (l_start +99));
49 dbms_output.put_line(l_subs);
50 l_start := l_start + 100;
51 end loop; */
52 exception
53 when others then
54 null;
55 END;
56
57
58
59 Procedure resolve_project_references(
60 source_project_id IN number,
61 source_project_number IN OUT NOCOPY varchar2,
62 source_task_id IN number,
63 source_task_number IN OUT NOCOPY varchar2,
64 p_project_id IN number,
65 p_project_number IN OUT NOCOPY varchar2,
66 t_task_id IN number,
67 t_task_number IN OUT NOCOPY varchar2,
68 to_project_id IN number,
69 to_project_number IN OUT NOCOPY varchar2,
70 to_task_id IN number,
71 to_task_number IN OUT NOCOPY varchar2,
72 pa_expenditure_org_id IN number,
73 pa_expenditure_org IN OUT NOCOPY varchar2,
74 success IN OUT NOCOPY boolean ) IS
75
76 l_cur_mfg_org_id NUMBER;
77 BEGIN
78
79 source_project_number := NULL;
80 source_task_number := NULL;
81 p_project_number := NULL;
82 t_task_number := NULL;
83 to_project_number := NULL;
84 to_task_number := NULL;
85 pa_expenditure_org := NULL;
86
87 -- bug 4662395 set the profile mfg_organization_id so
88 -- the call to PJM_PROJECTS_ALL_V will return data.
89 l_cur_mfg_org_id := TO_NUMBER(FND_PROFILE.VALUE('MFG_ORGANIZATION_ID'));
90
91 IF ( (pa_expenditure_org_id IS NOT NULL) AND
92 ( (l_cur_mfg_org_id IS NULL) OR
93 (l_cur_mfg_org_id IS NOT NULL AND l_cur_mfg_org_id <> FND_API.G_MISS_NUM)
94 )
95 ) THEN
96 FND_PROFILE.put('MFG_ORGANIZATION_ID',pa_expenditure_org_id);
97 END IF;
98
99
100 if ( source_project_id IS NOT NULL ) then
101 BEGIN
102 -- Modified For bug 1301035
103 /* Bug 2490166 */
104 /* SELECT m.segment1
105 INTO source_project_number
106 FROM pa_projects_all m
107 WHERE m.project_id = source_project_id ; */
108
109 SELECT m.project_number
110 INTO source_project_number
111 FROM pjm_projects_all_v m
112 WHERE m.project_id = source_project_id ;
113
114
115 EXCEPTION
116 WHEN NO_DATA_FOUND then
117 source_project_number := NULL;
118 END;
119 end if;
120
121
122
123 if ( p_project_id IS NOT NULL ) then
124 BEGIN
125 -- Modified For bug 1301035
126 /* Bug 2490166 */
127 /* SELECT my.segment1
128 INTO p_project_number
129 FROM pa_projects_all my
130 WHERE my.project_id = p_project_id ; */
131
132 SELECT my.project_number
133 INTO p_project_number
134 FROM pjm_projects_all_v my
135 WHERE my.project_id = p_project_id ;
136
137 success := TRUE ;
138 EXCEPTION
139 WHEN NO_DATA_FOUND then
140 p_project_number := NULL;
141 END;
142
143 end if;
144
145
146 if ( to_project_id IS NOT NULL ) then
147 BEGIN
148 -- Modified For bug 1301035
149 /* Bug 2490166 */
150 /* SELECT m.segment1
151 INTO to_project_number
152 FROM pa_projects_all m
153 WHERE m.project_id = to_project_id ; */
154
155 SELECT m.project_number
156 INTO to_project_number
157 FROM pjm_projects_all_v m
158 WHERE m.project_id = to_project_id ;
159 EXCEPTION
160 WHEN NO_DATA_FOUND then
161 to_project_number := NULL;
162
163 END;
164 end if;
165
166
167
168 if ( source_task_id IS NOT NULL ) then
169 BEGIN
170 SELECT m.task_number
171 INTO source_task_number
172 FROM pjm_tasks_v m
173 WHERE m.task_id = source_task_id
174 AND m.project_id = source_project_id;
175
176 EXCEPTION
177 WHEN NO_DATA_FOUND then
178 source_task_number := NULL;
179
180 END;
181 end if;
182
183
184
185 if ( t_task_id IS NOT NULL ) then
186 BEGIN
187 SELECT m.task_number
188 INTO t_task_number
189 FROM pjm_tasks_v m
190 WHERE m.task_id = t_task_id
191 AND m.project_id = p_project_id;
192
193 EXCEPTION
194 WHEN NO_DATA_FOUND then
195 t_task_number := NULL;
196
197 END;
198 end if;
199
200
201 if ( to_task_id IS NOT NULL ) then
202 BEGIN
203 SELECT m.task_number
204 INTO to_task_number
205 FROM pjm_tasks_v m
206 WHERE m.task_id = to_task_id
207 AND m.project_id = to_project_id;
208
209 EXCEPTION
210 WHEN NO_DATA_FOUND then
211 to_task_number := NULL;
212
213 END;
214 end if;
215
216
217 --Bug #5504073
218 --Added a ROWNUM = 1 while fetching expenditure org name
219 IF ( pa_expenditure_org_id IS NOT NULL ) then
220 BEGIN
221 SELECT m.name
222 INTO pa_expenditure_org
223 FROM pa_organizations_expend_v m
224 WHERE m.organization_id = pa_expenditure_org_id
225 AND active_flag='Y'
226 AND ROWNUM = 1;
227 EXCEPTION
228 WHEN NO_DATA_FOUND then
229 pa_expenditure_org := NULL;
230 END;
231 END IF;
232
233 success := TRUE;
234 EXCEPTION
235 WHEN OTHERS then
236 success := FALSE;
237
238 END resolve_project_references ;
239
240
241 Procedure org_project_parameters(
242 org_id IN number,
243 p_project_reference_enabled OUT NOCOPY number,
244 p_pm_cost_collection_enabled OUT NOCOPY number,
245 p_project_control_level OUT NOCOPY number,
246 success OUT NOCOPY boolean) IS
247 BEGIN
248 success := TRUE;
249 p_project_reference_enabled := 2;
250 p_pm_cost_collection_enabled := 2;
251 p_project_control_level := 1 ;
252 SELECT NVL(mp.project_reference_enabled, 2),
253 NVL(mp.pm_cost_collection_enabled,2),
254 NVL(mp.project_control_level,1)
255 INTO p_project_reference_enabled,
256 p_pm_cost_collection_enabled,
257 p_project_control_level
258 FROM mtl_parameters mp
259 WHERE organization_id = org_id ;
260
261 EXCEPTION
262 WHEN OTHERS then
263 success := FALSE ;
264
265 END org_project_parameters ;
266
267
268
269 Function onhand_qty(
270 org_id number,
271 sub_code varchar2,
272 loc_id number)return number IS
273
274 found number := -1;
275 qty_found number := 0 ;
276
277 BEGIN
278 SELECT NVL(SUM(primary_transaction_quantity),0)
279 INTO qty_found
280 FROM MTL_ONHAND_QUANTITIES_DETAIL
281 WHERE organization_id = org_id
282 AND subinventory_code = NVL(sub_code, subinventory_code)
283 AND NVL(locator_id,-9999) = NVL(loc_id, NVL(locator_id,-9999)) ;
284
285 return qty_found ;
286 EXCEPTION
287 WHEN NO_DATA_FOUND then
288 qty_found := 0 ;
289 return qty_found ;
290 WHEN OTHERS then
291 qty_found := -1 ;
292 return qty_found ;
293
294 END onhand_qty ;
295
296 Function pending_in_temp(
297 org_id number,
298 sub_code varchar2,
299 loc_id number) return number IS
300
301 found number := -1;
302 trx_found number := 0;
303
304 BEGIN
305 SELECT COUNT(transaction_temp_id)
306 INTO trx_found
307 FROM mtl_material_transactions_temp
308 WHERE organization_id = org_id
309 AND subinventory_code = NVL(sub_code, subinventory_code)
310 AND NVL(locator_id,-9999) = NVL(loc_id, NVL(locator_id,-9999)) ;
311
312 return trx_found ;
313 EXCEPTION
314 WHEN NO_DATA_FOUND then
315 trx_found := 0 ;
316 return trx_found ;
317 WHEN OTHERS then
318 trx_found := -1 ;
319 return trx_found ;
320 trx_found := NVL(trx_found, 0);
321
322 END pending_in_temp ;
323
324
325
326 Function pending_in_interface(
327 org_id number,
328 sub_code varchar2,
329 loc_id number) return number IS
330
331 found number := -1;
332 trx_found number := 0;
333
334 BEGIN
335 SELECT COUNT(transaction_interface_id)
336 INTO trx_found
337 FROM mtl_transactions_interface
338 WHERE organization_id = org_id
339 AND subinventory_code = NVL(sub_code, subinventory_code)
340 AND NVL(locator_id,-9999) = NVL(loc_id, NVL(locator_id,-9999)) ;
341
342 return trx_found ;
343 EXCEPTION
344 WHEN NO_DATA_FOUND then
345 trx_found := 0 ;
346 return trx_found ;
347 WHEN OTHERS then
348 trx_found := -1 ;
349 return trx_found ;
350
351
352 END pending_in_interface ;
353
354
355 Procedure onhand_pending_trx(
356 org_id IN number,
357 sub_code IN varchar2,
358 locator_id IN number,
359 onhand OUT NOCOPY boolean,
360 pending_trx OUT NOCOPY boolean,
361 success OUT NOCOPY boolean) IS
362
363 qty_found number := 0;
364 pending_trx_found number := 0;
365 onhand_return number;
366 pending_in_temp_return number;
367 pending_in_interface_return number ;
368
369
370
371 BEGIN
372 success := TRUE;
373
374 onhand_return := onhand_qty(org_id,sub_code,locator_id) ;
375 if ( onhand_return = 0 ) then
376 pending_in_temp_return := pending_in_temp(org_id,sub_code,locator_id) ;
377 if ( pending_in_temp_return = 0 ) then
378 pending_in_interface_return := pending_in_interface(org_id,sub_code,locator_id) ;
379 if ( pending_in_interface_return = 0 ) then
380 onhand := FALSE;
381 pending_trx := FALSE;
382 else
383 onhand := TRUE ;
384 pending_trx := TRUE ;
385 end if;
386 else
387 onhand := TRUE ;
388 pending_trx := TRUE ;
389 end if;
390 else
391 onhand := TRUE ;
392 pending_trx := TRUE ;
393 end if;
394 if ( (onhand_return < 0 ) OR (pending_in_temp_return < 0 ) OR
395 ( pending_in_interface_return < 0 ) ) then
396 success := FALSE;
397 end if;
398
399 END onhand_pending_trx;
400
401
402 Procedure populate_project_info(
403 FM_ORG_ID IN NUMBER,
404 TO_ORG_ID IN NUMBER,
405 FM_SUB IN VARCHAR2,
406 TO_SUB IN VARCHAR2,
407 FM_LOCATOR IN NUMBER,
408 TO_LOCATOR IN NUMBER,
409 F_PROJECT_ID IN OUT NOCOPY NUMBER,
410 F_TASK_ID IN OUT NOCOPY NUMBER,
411 T_PROJECT_ID IN OUT NOCOPY NUMBER,
412 T_TASK_ID IN OUT NOCOPY NUMBER,
413 ERROR_CODE OUT NOCOPY VARCHAR2,
414 ERROR_EXPL OUT NOCOPY VARCHAR2,
415 SRC_TYPE_ID IN NUMBER,
416 ACTION_ID IN NUMBER ,
417 SOURCE_ID IN NUMBER) IS
418 /*
419 Added source_id parameter to get the project and task of the source.
420 Currently the source_id field is used for passing the req_line_id.
421 This parameter could be used in future for other sources. For internal
422 order intransit shipment and internal req intransit receipt the source
423 id is the req_line_id. The project and task is selected from po_requisition_lines
424 table.
425 */
426 prj_ref_enabled number ;
427 prj_cntrl_level number;
428 to_org_prj_ref_enabled number;
429 to_org_prj_cntrl_level number;
430 translated_mesg varchar2(2000) := null;
431 v_buffer varchar2(241) := null;
432 x_return_status Varchar2(1);
433 BEGIN
434
435 error_code := null;
436 error_expl := null;
437 if ( (fm_org_id IS NULL ) OR (src_type_id IS NULL)
438 OR (action_id IS NULL ) ) then
439 fnd_message.set_name('INV','INV_DATA_ERROR');
440 fnd_message.set_token('ENTITY', 'populate_project_info');
441 translated_mesg := fnd_message.get ;
442 error_code := '';
443 error_expl := substr(translated_mesg,1,240) ;
444 return ;
445 end if;
446
447 SELECT NVL(project_reference_enabled,2), NVL(project_control_level,1)
448 INTO prj_ref_enabled, prj_cntrl_level
449 FROM mtl_parameters
450 WHERE organization_id = fm_org_id ;
451 /* We are going to get task ids no matter what the control level is
452 But are keeping reference for control level in case later we decide to
453 filter on this. So now, setting cntrl_level = 2 (task level)
454 */
455 prj_cntrl_level := 2 ;
456 if ( prj_ref_enabled = 2 ) then
457 if ( (action_id IN (3,21,12) )) then
458 goto handle_interorg ;
459 else
460 return ;
461 end if;
462 end if;
463
464 if ( f_project_id IS NULL and fm_locator is not NULL) then
465 SELECT project_id
466 INTO f_project_id
467 FROM mtl_item_locations
468 WHERE inventory_location_id = fm_locator
469 AND organization_id = fm_org_id ;
470 end if;
471
472 if ( f_task_id IS NULL AND f_project_id IS NOT NULL) then
473 if ( prj_cntrl_level = 2 AND prj_ref_enabled = 1 ) then
474 if ( fm_locator IS NOT NULL ) then
475 SELECT task_id
476 INTO f_task_id
477 FROM mtl_item_locations
478 WHERE NVL(project_id,-999) = NVL(f_project_id, -111)
479 AND inventory_location_id = fm_locator
480 AND organization_id = fm_org_id ;
481
482 end if;
483 end if;
484 end if;
485
486 if ( action_id in (2,28) ) then
487
488 if ( t_project_id IS NULL ) then
489 if ( to_locator IS NOT NULL ) then
490 SELECT project_id
491 INTO t_project_id
492 FROM mtl_item_locations
493 WHERE inventory_location_id = to_locator
494 AND organization_id = fm_org_id ;
495 end if;
496 end if;
497
498 if ( t_task_id IS NULL AND t_project_id IS NOT NULL) then
499 if ( to_locator IS NOT NULL ) then
500 SELECT task_id
501 INTO t_task_id
502 FROM mtl_item_locations
503 WHERE inventory_location_id = to_locator
504 AND organization_id = fm_org_id ;
505 end if;
506
507 end if;
508
509 end if;
510
511 <<handle_interorg>>
512 if ( (action_id = 3) OR (action_id = 21) OR (action_id = 12) ) then
513 if ( to_org_id IS NULL ) then
514 return;
515 end if;
516
517 SELECT NVL(project_reference_enabled,2), NVL(project_control_level,1)
518 INTO to_org_prj_ref_enabled, to_org_prj_cntrl_level
519 FROM mtl_parameters
520 WHERE organization_id = to_org_id ;
521
522 /* We are going to get task ids no matter what the control level is
523 But are keeping reference for control level in case later we decide to
524 filter on this. So now, setting cntrl_level = 2 (task level)
525 */
526
527 to_org_prj_cntrl_level := 2 ;
528 if ( to_org_prj_ref_enabled = 2 ) then
529 return;
530 end if;
531 If (action_id in (12,21) and source_id is not null) Then
532 If (src_type_id = 8 ) Then /* Intransit Shipment */
533 Get_project_info_from_Req(
534 x_return_status,
535 t_project_id,
536 t_task_id,
537 Source_Id);
538 Else /* for Intransit Receipt */
539 Get_project_info_for_RcvTrx(
540 x_return_status,
541 t_project_id,
542 t_task_id,
543 Source_Id);
544
545 End If;
546 If X_return_status <> FND_API.G_RET_STS_SUCCESS
547 Then
548 translated_mesg := fnd_message.get ;
549 error_code := '';
550 error_expl := substr(translated_mesg,1,240) ;
551 End If;
552 Return;
553 End If;
554
555 if ( t_project_id IS NULL ) then
556 if ( to_locator IS NOT NULL ) then
557
558 /* For an inventory intransit shipment,any locator entered on the form as
559 to_locator is not picked up by receiving as a default unless the transaction
560 default is set for that locator.
561 The same behaviour is true for project related locators and additionally
562 one cannot receive into a project related locator as well.
563 As MTL_SUPPLY does not support locators, the inventory moves from project
564 cost group to common and after receiving from common to common.*/
565
566 If (action_id = 21 and src_type_id = 13) Then
567 null;
568 Else
569 SELECT project_id
570 INTO t_project_id
571 FROM mtl_item_locations
572 WHERE inventory_location_id = to_locator
573 AND organization_id = to_org_id ;
574 End If;
575 end if;
576 end if;
577
578 if ( t_task_id IS NULL AND t_project_id IS NOT NULL) then
579 if ( to_locator IS NOT NULL ) then
580 SELECT task_id
581 INTO t_task_id
582 FROM mtl_item_locations
583 WHERE inventory_location_id = to_locator
584 AND organization_id = to_org_id ;
585 end if;
586
587 end if;
588
589 end if;
590
591 EXCEPTION
592 WHEN OTHERS then
593
594 fnd_message.set_name('INV','INV_UNHANDLED_ERR');
595 fnd_message.set_token('ENTITY1', 'populate_project_info');
596 v_buffer := to_char(SQLCODE) || ' '|| substr(SQLERRM,1,150);
597 fnd_message.set_token('ENTITY2', v_buffer);
598 translated_mesg := fnd_message.get ;
599 translated_mesg := substr(translated_mesg,1,230) ;
600 error_expl := translated_mesg ;
601
602 END populate_project_info;
603
604
605 Procedure call_cust_val(
606 V_item_id IN number
607 ,V_revision IN varchar2
608 ,V_org_id IN number
609 ,V_sub_code IN varchar2
610 ,V_locator_id IN number
611 ,V_xfr_org_id IN number
612 ,V_xfr_sub_code IN varchar2
613 ,V_xfr_locator_id IN number
614 ,V_quantity IN number
615 ,V_txn_type_id IN number
616 ,V_txn_action_id IN number
617 ,V_txn_source_type_id IN number
618 ,V_txn_source_id IN number
619 ,V_txn_source_name IN varchar2
620 ,V_project_id IN number
621 ,V_task_id IN OUT NOCOPY number
622 ,V_source_project_id IN number
623 ,V_source_task_id IN OUT NOCOPY number
624 ,V_to_project_id IN number
625 ,V_to_task_id IN OUT NOCOPY number
626 ,V_txn_date IN date
627 ,V_pa_expenditure_org_id IN number
628 ,V_expenditure_type IN varchar2
629 ,V_calling_module IN varchar2
630 ,V_user_id IN number
631 ,V_error_mesg OUT NOCOPY varchar2
632 ,V_warning_mesg OUT NOCOPY varchar2
633 ,V_success_flag OUT NOCOPY number
634 ,V_attribute_category IN varchar2
635 ,V_attribute1 IN varchar2
636 ,V_attribute2 IN varchar2
637 ,V_attribute3 IN varchar2
638 ,V_attribute4 IN varchar2
639 ,V_attribute5 IN varchar2
640 ,V_attribute6 IN varchar2
641 ,V_attribute7 IN varchar2
642 ,V_attribute8 IN varchar2
643 ,V_attribute9 IN varchar2
644 ,V_attribute10 IN varchar2
645 ,V_attribute11 IN varchar2
646 ,V_attribute12 IN varchar2
647 ,V_attribute13 IN varchar2
648 ,V_attribute14 IN varchar2
649 ,V_attribute15 IN varchar2
650 )
651 IS
652 x_fm_org_project_control_level number ;
653 x_to_org_project_control_level number;
654 original_task_id number;
655 original_source_task_id number;
656 original_to_task_id number;
657 translated_mesg varchar2(2000) := null;
658 Begin
659
660 v_error_mesg := null;
661 v_warning_mesg := null;
662
663 original_task_id := v_task_id ;
664 original_source_task_id := v_source_task_id ;
665 original_to_task_id := v_to_task_id ;
666 if ( v_org_id IS NULL ) then
667 fnd_message.set_name('INV','INV_DATA_ERROR');
668 fnd_message.set_token('ENTITY', 'call_customer_validation');
669 translated_mesg := fnd_message.get ;
670 v_error_mesg := substr(translated_mesg,1,240) ;
671 v_success_flag := -1 ;
672 return ;
673 end if;
674 if ( (v_txn_action_id = 3) AND v_xfr_org_id IS NULL ) then
675 fnd_message.set_name('INV','INV_DATA_ERROR');
676 fnd_message.set_token('ENTITY', 'call_customer_validation');
677 translated_mesg := fnd_message.get ;
678 v_error_mesg := substr(translated_mesg,1,240) ;
679 v_success_flag := -1 ;
680 return ;
681 end if;
682
683 SELECT NVL(project_control_level,1)
684 INTO x_fm_org_project_control_level
685 FROM mtl_parameters
686 WHERE organization_id = v_org_id ;
687
688 if ( v_txn_action_id = 3 ) then
689 SELECT NVL(project_control_level,1)
690 INTO x_to_org_project_control_level
691 FROM mtl_parameters
692 WHERE organization_id = v_xfr_org_id ;
693 end if;
694
695 /*
696 now call customers validation package
697 */
698
699 inv_prj_cust_val.validate(
700 V_item_id
701 ,V_revision
702 ,V_org_id
703 ,V_sub_code
704 ,V_locator_id
705 ,V_xfr_org_id
706 ,V_xfr_sub_code
707 ,V_xfr_locator_id
708 ,V_quantity
709 ,V_txn_type_id
710 ,V_txn_action_id
711 ,V_txn_source_type_id
712 ,V_txn_source_id
713 ,V_txn_source_name
714 ,V_project_id
715 ,V_task_id
716 ,V_source_project_id
717 ,V_source_task_id
718 ,V_to_project_id
719 ,V_to_task_id
720 ,V_txn_date
721 ,V_pa_expenditure_org_id
722 ,V_expenditure_type
723 ,V_calling_module
724 ,V_user_id
725 ,V_error_mesg
726 ,V_warning_mesg
727 ,V_success_flag
728 ,V_attribute_category
729 ,V_attribute1
730 ,V_attribute2
731 ,V_attribute3
732 ,V_attribute4
733 ,V_attribute5
734 ,V_attribute6
735 ,V_attribute7
736 ,V_attribute8
737 ,V_attribute9
738 ,V_attribute10
739 ,V_attribute11
740 ,V_attribute12
741 ,V_attribute13
742 ,V_attribute14
743 ,V_attribute15 );
744
745 /*
746 Overwrite returned task info if project control level was project,
747 otherwise, use task id info sent back by customer
748 */
749
750 if ( x_fm_org_project_control_level = 2 ) then
751 v_source_task_id := original_source_task_id ;
752 v_task_id := original_task_id ;
753 end if;
754
755 if ( x_to_org_project_control_level = 1 ) then
756 v_to_task_id := original_to_task_id ;
757 end if;
758
759 end call_cust_val ;
760
761 Procedure update_project_task(v_org_id number,
762 v_in_project_id number,
763 v_in_task_id number,
764 v_out_project_id in out NOCOPY number,
765 v_out_task_id in out NOCOPY number) is
766 v_project_reference_enabled number;
767 v_pm_cost_collection_enabled number;
768 v_project_control_level number;
769 v_success boolean;
770 begin
771 INV_PROJECT.org_project_parameters(v_org_id,
772 v_project_reference_enabled,
773 v_pm_cost_collection_enabled,
774 v_project_control_level,
775 v_success);
776 if v_success and v_project_reference_enabled = 1
777 then
778 if v_in_project_id is not null
779 then
780 v_out_project_id := v_in_project_id;
781 if v_in_task_id is not null
782 then
783 v_out_task_id := v_in_task_id;
784 end if;
785 end if;
786 end if;
787 end update_project_task;
788 Procedure update_project_task_number(v_org_id number,
789 v_in_project_id number,
790 v_in_task_id number,
791 v_out_project_id in out NOCOPY number,
792 v_out_task_id in out NOCOPY number,
793 v_out_project in out NOCOPY varchar2,
794 v_out_task in out NOCOPY varchar2) is
795 v_project_reference_enabled number;
796 v_pm_cost_collection_enabled number;
797 v_project_control_level number;
798 v_success boolean;
799 begin
800 INV_PROJECT.org_project_parameters(v_org_id,
801 v_project_reference_enabled,
802 v_pm_cost_collection_enabled,
803 v_project_control_level,
804 v_success);
805 if v_success and v_project_reference_enabled = 1
806 then
807 if v_in_project_id is not null
808 then
809 v_out_project_id := v_in_project_id;
810 begin
811
812 -- bug 4662395 set the profile mfg_organization_id so
813 -- the call to MTL_PROJECT_V will return data.
814
815 FND_PROFILE.put('MFG_ORGANIZATION_ID',v_org_id);
816
817 select project_number
818 into v_out_project
819 from mtl_project_v
820 where project_id = v_in_project_id;
821 exception
822 when others
823 then null;
824 end;
825 if v_in_task_id is not null
826 then
827 v_out_task_id := v_in_task_id;
828 begin
829 select task_number
830 into v_out_task
831 from mtl_task_v
832 where project_id = v_in_project_id
833 and task_id = v_in_task_id;
834 exception
835 when others
836 then null;
837 end;
838 end if;
839 end if;
840 end if;
841 end update_project_task_number;
842
843 /*
844 The Get_project_info_from_Req procedure is called in procedures
845 Get_project_loc_for_prj_Req and
846 populate_project_info procedure.
847
848 This procedure provides the project and task from
849 po_req_distributions_all via po_requisition_lines_all table
850 for the requisition_line_id in the
851 mtl_material_transactions_temp or the
852 transaction interface table.
853 */
854 Procedure Get_project_info_from_Req(
855 x_Return_Status Out NOCOPY Varchar2,
856 x_Project_Id Out NOCOPY Number,
857 x_Task_Id Out NOCOPY Number,
858 P_Req_Line_Id In Number) IS
859
860 l_req_project_id Number;
861 l_req_task_id Number;
862
863 Begin
864 x_return_status := FND_API.G_RET_STS_SUCCESS;
865
866 SELECT project_id,task_id
867 INTO l_req_project_id,l_req_task_id
868 FROM po_req_distributions_all
869 WHERE requisition_line_id = p_req_line_id;
870
871 x_project_id := l_req_project_id;
872 x_task_id := l_req_task_id;
873
874 Exception
875 When Others Then
876
877 X_return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
878 FND_MSG_PUB.Build_Exc_Msg(
879 p_pkg_name =>'Inv_Project',
880 p_procedure_name => 'Get_project_info_from_Req');
881
882 End Get_project_info_from_Req;
883
884 /*
885 The Get_project_info_for_RcvTrx procedure is called in procedures
886 populate_project_info procedure.
887
888 This procedure gets the project and task from po_req_distributions_all
889 via rcv_transactions table for the rcv_transaction_id in the
890 mtl_material_transactions_temp.
891 */
892 Procedure Get_project_info_for_RcvTrx(
893 x_Return_Status Out NOCOPY Varchar2,
894 x_Project_Id Out NOCOPY Number,
895 x_Task_Id Out NOCOPY Number,
896 P_Rcv_Trx_Id In Number) IS
897
898 l_req_project_id Number;
899 l_req_task_id Number;
900
901 Begin
902 x_return_status := FND_API.G_RET_STS_SUCCESS;
903
904 SELECT prd.project_id, prd.task_id
905 INTO l_req_project_id,l_req_task_id
906 FROM po_req_distributions_all prd,
907 rcv_transactions rcv
908 WHERE rcv.transaction_id = P_Rcv_Trx_Id
909 And prd.requisition_line_id = rcv.requisition_line_id;
910
911 x_project_id := l_req_project_id;
912 x_task_id := l_req_task_id;
913
914 Exception
915 When Others Then
916
917 X_return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
918 FND_MSG_PUB.Build_Exc_Msg(
919 p_pkg_name =>'Inv_Project',
920 p_procedure_name => 'Get_project_info_for_RcvTrx');
921
922 End Get_project_info_for_RcvTrx;
923
924 /*
925 /*
926 Procedure Get_project_loc_for_prj_Req appends the project and task to the
927 locator that is provided as input. This procedure takes care of creating a new
928 locator if all control levels are met. This procedure is called by inltev.ppc
929 */
930 Procedure Get_project_loc_for_prj_Req(
931 X_Return_Status Out NOCOPY Varchar2,
932 X_locator_Id In Out NOCOPY Number,
933 P_organization_id In Number,
934 P_Req_Line_Id In Number) IS
935
936 l_req_project_id Number;
937 l_req_task_id Number;
938 l_Project_locator_id Number;
939 l_return_status Varchar2(1);
940
941 Begin
942 x_return_status := FND_API.G_RET_STS_SUCCESS;
943 If P_Req_Line_Id is null Then
944 Return;
945 Else
946 Get_project_info_from_Req(
947 l_return_status,
948 l_req_project_id,
949 l_req_task_id,
950 P_Req_Line_Id);
951 If l_return_status <> FND_API.G_RET_STS_SUCCESS
952 Then
953 Return;
954 End If;
955 If l_req_project_id is null
956 Then
957 return;
958 Else
959 PJM_PROJECT_LOCATOR.Get_DefaultProjectLocator
960 (P_Organization_Id,
961 X_locator_Id,
962 l_req_project_id,
963 l_req_task_id,
964 l_project_locator_Id);
965 X_locator_Id := l_project_locator_Id;
966 End If;
967 End If;
968
969 Exception
970 When Others Then
971
972 X_return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
973 FND_MSG_PUB.Build_Exc_Msg(
974 p_pkg_name =>'Inv_Project',
975 p_procedure_name => 'Get_project_loc_for_prj_Req');
976
977 End Get_project_loc_for_prj_Req;
978
979 Procedure Set_Org_client_info(X_return_Status Out NOCOPY Varchar2,
980 P_Organization_Id In Number) Is
981
982 l_Org_Id Number;
983 l_conreq_id number :=0; --bugfix 4643461
984
985 Begin
986 x_return_status := FND_API.G_RET_STS_SUCCESS;
987
988 l_conreq_id := fnd_global.conc_request_id;
989
990 IF nvl(g_organization_id,-999) <> nvl(P_Organization_Id,-999) THEN
991
992 -- bugfix 4643461 cache the org_id for pickrelease and TM only. which has concurrent req id not null.
993 -- Bug 5304874 Added INV_CACHE.is_pickrelease so that caching happens for pick release online cases also
994 if (l_conreq_id > 0 OR INV_CACHE.is_pickrelease) then
995
996 g_organization_id := P_Organization_Id;
997
998 end if;
999
1000 --Commenting the Check Install for PJM, bug 3812559.
1001 /*IF PJM_INSTALL.CHECK_INSTALL
1002 THEN*/
1003 SELECT operating_unit
1004 INTO l_org_id
1005 FROM org_organization_definitions
1006 WHERE organization_id = P_Organization_Id;
1007
1008 if l_org_id is not null
1009 then
1010
1011 -- MOAC replace fnd_client_info.set_org_context
1012 -- with MO_GLOBAL.init
1013 MO_GLOBAL.init('INV');
1014 -- fnd_client_info.set_org_context(to_char(l_org_id));
1015 end if;
1016
1017 -- bugfix 4643461 added debug message
1018 if ( nvl(fnd_profile.value('INV_DEBUG_TRACE'), 0) = 1 ) then
1019 inv_log_util.trace('set client context : '||l_org_id, 'INV_PROJECT', 9);
1020 inv_log_util.trace('g_organization_id : '||g_organization_id, 'INV_PROJECT', 9);
1021 end if;
1022
1023
1024 /*END IF;*/
1025 END IF;
1026
1027 EXCEPTION
1028 WHEN OTHERS THEN
1029
1030 X_return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
1031 FND_MSG_PUB.Build_Exc_Msg(
1032 p_pkg_name =>'Inv_Project',
1033 p_procedure_name => 'Set_Org_client_info');
1034
1035 END Set_Org_client_info;
1036
1037
1038 PROCEDURE SET_SESSION_PARAMETERS(
1039 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1040 X_MSG_COUNT OUT NOCOPY NUMBER,
1041 X_MSG_DATA OUT NOCOPY VARCHAR2,
1042 P_ORGANIZATION_ID IN NUMBER
1043 ) IS
1044
1045 L_OPERATING_UNIT VARCHAR2(30);
1046
1047 BEGIN
1048
1049 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1050
1051 FND_PROFILE.PUT('MFG_ORGANIZATION_ID', TO_CHAR(P_ORGANIZATION_ID));
1052
1053 FND_PROFILE.GET('ORG_ID', L_OPERATING_UNIT);
1054
1055 -- MOAC replace fnd_client_info.set_org_context
1056 -- with MO_GLOBAL.init
1057 MO_GLOBAL.init('INV');
1058 -- FND_CLIENT_INFO.SET_ORG_CONTEXT(L_OPERATING_UNIT);
1059
1060 FND_MSG_PUB.COUNT_AND_GET( P_COUNT => X_MSG_COUNT, P_DATA => X_MSG_DATA );
1061
1062 EXCEPTION
1063
1064 WHEN FND_API.G_EXC_ERROR THEN
1065
1066 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1067 FND_MSG_PUB.COUNT_AND_GET( P_COUNT => X_MSG_COUNT, P_DATA => X_MSG_DATA );
1068
1069 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1070
1071 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
1072 FND_MSG_PUB.COUNT_AND_GET( P_COUNT => X_MSG_COUNT, P_DATA => X_MSG_DATA );
1073
1074 WHEN OTHERS THEN
1075
1076 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
1077 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1078 FND_MSG_PUB.ADD_EXC_MSG( g_pkg_name, 'SET_SESSION_PARAMETERS');
1079 END IF;
1080 FND_MSG_PUB.COUNT_AND_GET( P_COUNT => X_MSG_COUNT, P_DATA => X_MSG_DATA );
1081
1082 END SET_SESSION_PARAMETERS;
1083
1084 Procedure get_proj_task_from_lpn(
1085 p_organization_Id IN NUMBER,
1086 p_lpn_id IN NUMBER,
1087 x_project_id OUT NOCOPY NUMBER,
1088 x_task_id OUT NOCOPY NUMBER,
1089 x_return_status OUT NOCOPY VARCHAR2,
1090 x_msg_count OUT NOCOPY NUMBER,
1091 x_msg_data OUT NOCOPY VARCHAR2)
1092 IS
1093 l_project_id NUMBER := NULL;
1094 l_task_id NUMBER := NULL;
1095 BEGIN
1096 x_return_status := FND_API.G_RET_STS_SUCCESS;
1097
1098 SELECT mil.segment19, mil.segment20
1099 INTO l_project_id, l_task_id
1100 FROM mtl_item_locations mil, wms_license_plate_numbers wlpn
1101 WHERE wlpn.lpn_id = p_lpn_id
1102 AND wlpn.organization_id = p_organization_id
1103 AND wlpn.organization_id = mil.organization_id
1104 AND wlpn.locator_id = mil.inventory_location_id;
1105
1106 x_project_id := l_project_id;
1107 x_task_id := l_task_id;
1108
1109 EXCEPTION
1110 WHEN NO_DATA_FOUND THEN
1111 BEGIN
1112 select DISTINCT project_id, task_id
1113 INTO l_project_id, l_task_id
1114 FROM mtl_txn_request_lines
1115 WHERE organization_id = p_organization_id
1116 AND lpn_id = p_lpn_id;
1117
1118 x_project_id := l_project_id;
1119 x_task_id := l_task_id;
1120 EXCEPTION
1121 WHEN NO_DATA_FOUND THEN
1122 x_project_id := NULL;
1123 x_task_id := NULL;
1124 WHEN TOO_MANY_ROWS THEN
1125 x_project_id := NULL;
1126 x_task_id := NULL;
1127 WHEN OTHERS THEN
1128 x_project_id := NULL;
1129 x_task_id := NULL;
1130 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1131 FND_MSG_PUB.Count_And_Get
1132 (p_encoded => FND_API.G_FALSE,
1133 p_count => x_msg_count,
1134 p_data => x_msg_data);
1135 END;
1136
1137 WHEN FND_API.G_EXC_ERROR THEN
1138 x_project_id := NULL;
1139 x_task_id := NULL;
1140 x_return_status := FND_API.G_RET_STS_ERROR ;
1141 FND_MSG_PUB.Count_And_Get
1142 (p_encoded => FND_API.G_FALSE,
1143 p_count => x_msg_count,
1144 p_data => x_msg_data);
1145 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1146 x_project_id := NULL;
1147 x_task_id := NULL;
1148 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1149 FND_MSG_PUB.Count_And_Get
1150 (p_encoded => FND_API.G_FALSE,
1151 p_count => x_msg_count,
1152 p_data => x_msg_data);
1153 WHEN OTHERS THEN
1154 x_project_id := NULL;
1155 x_task_id := NULL;
1156 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1157 FND_MSG_PUB.Count_And_Get
1158 (p_encoded => FND_API.G_FALSE,
1159 p_count => x_msg_count,
1160 p_data => x_msg_data);
1161 END get_proj_task_from_lpn;
1162
1163
1164
1165 Function Is_Project_Enabled(
1166 p_org_id IN NUMBER
1167 ) return varchar2
1168 IS
1169
1170 l_project_reference_enabled number;
1171 success varchar2(6);
1172
1173 BEGIN
1174 success := 'TRUE';
1175 SELECT NVL(project_reference_enabled,2)
1176 INTO l_project_reference_enabled
1177 FROM mtl_parameters
1178 WHERE organization_id = p_org_id ;
1179
1180 IF l_project_reference_enabled <> 1 THEN
1181 success := 'FALSE';
1182 END IF;
1183
1184 RETURN success;
1185
1186 EXCEPTION
1187 WHEN OTHERS then
1188 success := 'FALSE' ;
1189 RETURN success;
1190
1191 END Is_Project_Enabled ;
1192
1193 -- Procedure to clear global variables for locator, project and task
1194 PROCEDURE CLEAR_GLOBAL_VARS IS
1195 BEGIN
1196 G_PHYSICAL_LOCATOR := NULL;
1197 G_PROJECT_NUMBER := NULL;
1198 G_PROJECT_ID := NULL;
1199 G_TASK_NUMBER := NULL;
1200 G_TASK_ID := NULL;
1201 END CLEAR_GLOBAL_VARS;
1202
1203 /* Procedure to obtain the Locator Metadata.
1204 * Processing Logic: Get all the segments (except 19 and 20) and
1205 * their corresponding validation types. If the validation type
1206 * of all the esgments is 'N' then form a string comprising
1207 * concatenated segments with the delimiter
1208 * (eg. SEGMENT1||'.'||SEGMENT2||'.'||SEGMENT3
1209 * If any of the segments has a validation type other than N'
1210 * form the display string as ALL\index of SEGMENT19\index of SEGMENT20
1211 */
1212 PROCEDURE GET_LOCATOR_METADATA(
1213 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1214 X_MSG_COUNT OUT NOCOPY NUMBER,
1215 X_MSG_DATA OUT NOCOPY VARCHAR2,
1216 X_DISPLAY OUT NOCOPY VARCHAR2,
1217 X_CONCATENATED OUT NOCOPY VARCHAR2,
1218 X_PROJECT_COLUMN OUT NOCOPY NUMBER,
1219 X_TASK_COLUMN OUT NOCOPY NUMBER
1220 ) IS
1221
1222 CURSOR CUR_SEG IS
1223 SELECT APPLICATION_COLUMN_NAME,
1224 ffs.FLEX_VALUE_SET_ID,
1225 ffv.VALIDATION_TYPE
1226 FROM FND_ID_FLEX_SEGMENTS ffs,
1227 FND_FLEX_VALUE_SETS ffv
1228 WHERE APPLICATION_ID = 401 -- 'INV'
1229 AND ID_FLEX_CODE = 'MTLL'
1230 AND ID_FLEX_NUM = 101 -- 'STOCK_LOCATORS'
1231 AND ENABLED_FLAG = 'Y'
1232 AND DISPLAY_FLAG = 'Y'
1233 AND ffv.FLEX_VALUE_SET_ID(+) = ffs.FLEX_VALUE_SET_ID
1234 ORDER BY SEGMENT_NUM;
1235
1236 L_RETURN VARCHAR2(90);
1237 L_ROWNUM NUMBER := 0;
1238 L_COLNAME VARCHAR2(15);
1239 L_VALUE_SET_ID NUMBER;
1240 L_VALIDATION_TYPE VARCHAR2(1);
1241 L_CONCAT VARCHAR2(1000);
1242 L_DELIM VARCHAR2(1);
1243 L_FIRST_TIME BOOLEAN := TRUE;
1244
1245 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1246 BEGIN
1247
1248 OPEN CUR_SEG;
1249 LOOP
1250 FETCH CUR_SEG INTO L_COLNAME, L_VALUE_SET_ID, L_VALIDATION_TYPE;
1251 EXIT WHEN CUR_SEG%NOTFOUND;
1252 L_ROWNUM := L_ROWNUM + 1;
1253 IF (L_COLNAME = 'SEGMENT19') OR (L_COLNAME = 'SEGMENT20') THEN
1254 L_RETURN := L_RETURN ||'\0'||TO_CHAR(L_ROWNUM);
1255 IF (L_COLNAME = 'SEGMENT19') THEN
1256 X_PROJECT_COLUMN := L_ROWNUM;
1257 ELSE
1258 X_TASK_COLUMN := L_ROWNUM;
1259 END IF;
1260 ELSE
1261 --Get the delimter if it is the first non-project and non-task segment
1262 IF (L_FIRST_TIME) THEN
1263 IF (L_VALIDATION_TYPE = 'N') OR (L_VALIDATION_TYPE IS NULL) THEN
1264
1265 SELECT CONCATENATED_SEGMENT_DELIMITER
1266 INTO L_DELIM
1267 FROM FND_ID_FLEX_STRUCTURES
1268 WHERE ID_FLEX_CODE = 'MTLL' AND ROWNUM =1;
1269
1270 L_CONCAT := L_COLNAME;
1271 ELSE
1272 L_CONCAT := NULL;
1273 END IF;
1274 L_FIRST_TIME := FALSE;
1275 --From the 2nd segement onwards, check if l_concat is not null and the validation
1276 --type for the current segment is 'N' or there is none. If so then append the
1277 --delimiter and segment name to the concatenated segments string
1278 ELSE
1279 IF (L_VALIDATION_TYPE = 'N' OR L_VALIDATION_TYPE IS NULL)
1280 AND (L_CONCAT IS NOT NULL) THEN
1281 L_CONCAT := L_CONCAT || '||' || '''' || L_DELIM || '''' || '||' || L_COLNAME;
1282 ELSE
1283 L_CONCAT := NULL;
1284 END IF; --END IF l_validation_type IS N
1285 END IF; --END IF L_FIRST_TIME
1286 END IF; --END IF COL_NAME is SEGMENT19 or SEGMENT20
1287 END LOOP;
1288 CLOSE CUR_SEG;
1289
1290 X_DISPLAY := 'ALL'||L_RETURN;
1291 X_CONCATENATED := L_CONCAT;
1292
1293 FND_MSG_PUB.COUNT_AND_GET( P_COUNT => X_MSG_COUNT, P_DATA => X_MSG_DATA );
1294 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1295
1296 EXCEPTION
1297 WHEN OTHERS THEN
1298
1299 IF (l_debug = 1) THEN
1300 MYDEBUG('EXCEPTION RAISED IN GET_LOCATOR_METADATA '||SQLERRM);
1301 END IF;
1302
1303 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
1304 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1305 FND_MSG_PUB.ADD_EXC_MSG( g_pkg_name, 'GET_LOCATOR_METADATA');
1306 END IF;
1307 FND_MSG_PUB.COUNT_AND_GET( P_COUNT => X_MSG_COUNT, P_DATA => X_MSG_DATA );
1308
1309 END GET_LOCATOR_METADATA;
1310
1311 /* Function to get the position of PROJECT column (SEGMENT19) */
1312 FUNCTION GET_PROJECT_COLUMN RETURN VARCHAR2 IS
1313 X_RETURN_STATUS VARCHAR2(1);
1314 X_MSG_DATA VARCHAR2(1000);
1315 X_MSG_COUNT NUMBER;
1316 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1317 BEGIN
1318 IF (l_debug = 1) THEN
1319 MYDEBUG('GET_PROJECT_COLUMN: G_DISPLAYED_SEGMENTS: '||G_DISPLAYED_SEGMENTS);
1320 END IF;
1321 IF G_DISPLAYED_SEGMENTS IS NULL THEN
1322 GET_LOCATOR_METADATA( X_RETURN_STATUS => X_RETURN_STATUS,
1323 X_MSG_COUNT => X_MSG_COUNT,
1324 X_MSG_DATA => X_MSG_DATA,
1325 X_DISPLAY => G_DISPLAYED_SEGMENTS,
1326 X_CONCATENATED => G_CONCATENATED_SEGMENTS,
1327 X_PROJECT_COLUMN => G_PROJECT_COLUMN,
1328 X_TASK_COLUMN => G_TASK_COLUMN);
1329
1330 IF (l_debug = 1) THEN
1331 MYDEBUG('GET_PROJECT_COLUMN: GET_LOCATOR_METADATA: '||X_RETURN_STATUS);
1332 END IF;
1333 END IF;
1334 IF (l_debug = 1) THEN
1335 MYDEBUG('GET_PROJECT_COLUMN: RETURNS: '||G_PROJECT_COLUMN);
1336 END IF;
1337 RETURN G_PROJECT_COLUMN;
1338 END;
1339
1340 /* Function to get the position of TASK column (SEGMENT20) */
1341 FUNCTION GET_TASK_COLUMN RETURN VARCHAR2 IS
1342 X_RETURN_STATUS VARCHAR2(1);
1343 X_MSG_DATA VARCHAR2(1000);
1344 X_MSG_COUNT NUMBER;
1345 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1346 BEGIN
1347 IF (l_debug = 1) THEN
1348 MYDEBUG('GET_TASK_COLUMN: G_DISPLAYED_SEGMENTS: '||G_DISPLAYED_SEGMENTS);
1349 END IF;
1350 IF G_DISPLAYED_SEGMENTS IS NULL THEN
1351 GET_LOCATOR_METADATA( X_RETURN_STATUS => X_RETURN_STATUS,
1352 X_MSG_COUNT => X_MSG_COUNT,
1353 X_MSG_DATA => X_MSG_DATA,
1354 X_DISPLAY => G_DISPLAYED_SEGMENTS,
1355 X_CONCATENATED => G_CONCATENATED_SEGMENTS,
1356 X_PROJECT_COLUMN => G_PROJECT_COLUMN,
1357 X_TASK_COLUMN => G_TASK_COLUMN);
1358 IF (l_debug = 1) THEN
1359 MYDEBUG('GET_TASK_COLUMN: GET_LOCATOR_METADATA: '||X_RETURN_STATUS);
1360 END IF;
1361 END IF;
1362 IF (l_debug = 1) THEN
1363 MYDEBUG('GET_TASK_COLUMN: RETURNS: '||G_TASK_COLUMN);
1364 END IF;
1365 RETURN G_TASK_COLUMN;
1366 END;
1367
1368 /* Function to fetch Physical Locator segments, project and task given locator and org
1369 * Processing Logic: Check if G_CONCATENATED_SEGMENTS is populated.
1370 * If yes then
1371 * Query MTL_ITEM_LOCATIONS to fetch concatenated physical locator segments, project Id
1372 * and Task Id for the passed locator Id/Org Id. Call get_project_number and get_task_number
1373 * to get Project Number and Task Number.
1374 * else
1375 * Use FND_FLEX_KEYVAL to fetch the Concatenated segment values, Project Id, Task Id,
1376 * Project Number and Task Number for the passed LocatorId/OrgId combination.
1377 * FND_FLEX_KEYVAL.CONCATENATED_SEGMENTS -- Concatenated display values
1378 * FND_FLEX_KEYVAL.SEGMENT_VALUES -- Table of Segment Values
1379 * The fetched values are stored in Package variables,
1380 */
1381 PROCEDURE FETCH_COMBINATION(P_LOCATOR_ID IN NUMBER,
1382 P_ORG_ID IN NUMBER) IS
1383 L_CC_ID_RET BOOLEAN;
1384 TYPE CUR_LOC IS REF CURSOR;
1385 c_locator CUR_LOC;
1386 l_loc_str VARCHAR2(1000);
1387 l_physical_locator VARCHAR2(1000);
1388 l_project_id NUMBER;
1389 l_task_id NUMBER;
1390 X_RETURN_STATUS VARCHAR2(1);
1391 X_MSG_DATA VARCHAR2(1000);
1392 X_MSG_COUNT NUMBER;
1393
1394 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1395 BEGIN
1396 IF (l_debug = 1) THEN
1397 MYDEBUG('FETCH_COMBINATION: P_LOCATOR_ID: P_ORG_ID: '||P_LOCATOR_ID||' : '||P_ORG_ID);
1398 END IF;
1399
1400 IF G_DISPLAYED_SEGMENTS IS NULL THEN
1401 GET_LOCATOR_METADATA( X_RETURN_STATUS => X_RETURN_STATUS,
1402 X_MSG_COUNT => X_MSG_COUNT,
1403 X_MSG_DATA => X_MSG_DATA,
1404 X_DISPLAY => G_DISPLAYED_SEGMENTS,
1405 X_CONCATENATED => G_CONCATENATED_SEGMENTS,
1406 X_PROJECT_COLUMN => G_PROJECT_COLUMN,
1407 X_TASK_COLUMN => G_TASK_COLUMN);
1408 IF (l_debug = 1) THEN
1409 MYDEBUG('FETCH_COMBINATION: GET_LOCATOR_METADATA: '||X_RETURN_STATUS);
1410 END IF;
1411 END IF;
1412
1413 IF G_CONCATENATED_SEGMENTS IS NOT NULL THEN
1414
1415 IF (l_debug = 1) THEN
1416 MYDEBUG('Using MTL_ITEM_LOCATIONS to get locator, project and task info');
1417 END IF;
1418 --Build the string to fetch locator id, project Id and
1419 --task Id from MTL_ITEM_LOCATIONS
1420 l_loc_str := 'SELECT ' || G_CONCATENATED_SEGMENTS || ', ';
1421 l_loc_str := l_loc_str || 'PROJECT_ID, TASK_ID FROM MTL_ITEM_LOCATIONS ';
1422 l_loc_str := l_loc_str || 'WHERE INVENTORY_LOCATION_ID = :1 AND ORGANIZATION_ID = :2';
1423
1424 IF (l_debug = 1) THEN
1425 MYDEBUG('l_loc_str:' || l_loc_str);
1426 END IF;
1427
1428 --Open the cursor and store the results in session variables
1429 OPEN c_locator FOR l_loc_str USING P_LOCATOR_ID, P_ORG_ID;
1430 FETCH c_locator INTO l_physical_locator, l_project_id, l_task_id;
1431
1432 IF l_physical_locator IS NOT NULL THEN
1433 G_PHYSICAL_LOCATOR := l_physical_locator;
1434 ELSE
1435 G_PHYSICAL_LOCATOR := NULL;
1436 END IF;
1437
1438 IF l_project_id IS NOT NULL THEN
1439 G_PROJECT_NUMBER := GET_PROJECT_NUMBER(l_project_id);
1440 G_PROJECT_ID := l_project_id;
1441 ELSE
1442 G_PROJECT_NUMBER := NULL;
1443 G_PROJECT_ID := NULL;
1444 END IF;
1445
1446 IF l_task_id IS NOT NULL THEN
1447 G_TASK_NUMBER := GET_TASK_NUMBER(l_task_id);
1448 G_TASK_ID := l_task_id;
1449 ELSE
1450 G_TASK_NUMBER := NULL;
1451 G_TASK_ID := NULL;
1452 END IF;
1453 CLOSE c_locator;
1454
1455 --If any of the segments has a validation type other than 'N'
1456 ELSE
1457 IF (l_debug = 1) THEN
1458 MYDEBUG('Using FND APIs to get locator, project and task info');
1459 END IF;
1460 L_CC_ID_RET := FND_FLEX_KEYVAL.VALIDATE_CCID(
1461 APPL_SHORT_NAME => 'INV',
1462 KEY_FLEX_CODE => 'MTLL',
1463 STRUCTURE_NUMBER => 101,
1464 COMBINATION_ID => P_LOCATOR_ID,
1465 DISPLAYABLE => G_DISPLAYED_SEGMENTS,
1466 DATA_SET => P_ORG_ID,
1467 VRULE => NULL,
1468 SECURITY => 'IGNORE',
1469 GET_COLUMNS => NULL,
1470 RESP_APPL_ID => 401,
1471 RESP_ID => NULL,
1472 USER_ID => NULL,
1473 SELECT_COMB_FROM_VIEW => NULL
1474 );
1475 IF NOT L_CC_ID_RET THEN
1476 IF (l_debug = 1) THEN
1477 MYDEBUG('FETCH_COMBINATION: VALIDATE_CCID: CALL FAILED');
1478 MYDEBUG('FETCH_COMBINATION: VALIDATE_CCID: '||FND_FLEX_KEYVAL.ERROR_MESSAGE);
1479 END IF;
1480 CLEAR_GLOBAL_VARS;
1481 ELSE
1482 G_PHYSICAL_LOCATOR := FND_FLEX_KEYVAL.CONCATENATED_VALUES;
1483 G_PROJECT_NUMBER := FND_FLEX_KEYVAL.SEGMENT_VALUE(GET_PROJECT_COLUMN);
1484 G_PROJECT_ID := FND_FLEX_KEYVAL.SEGMENT_ID(GET_PROJECT_COLUMN);
1485 G_TASK_NUMBER := FND_FLEX_KEYVAL.SEGMENT_VALUE(GET_TASK_COLUMN);
1486 G_TASK_ID := FND_FLEX_KEYVAL.SEGMENT_ID(GET_TASK_COLUMN);
1487 END IF;
1488
1489 END IF; --END If g_concated_segments IS NOT NULL
1490
1491 IF (l_debug = 1) THEN
1492 MYDEBUG('LOC SEGS: ' || G_PHYSICAL_LOCATOR);
1493 MYDEBUG('G_PROJECT_NUMBER: ' || G_PROJECT_NUMBER);
1494 MYDEBUG('G_PROJECT_ID: ' || G_PROJECT_ID);
1495 MYDEBUG('G_TASK_NUMBER: ' || G_TASK_NUMBER);
1496 MYDEBUG('G_TASK_ID: ' || G_TASK_ID);
1497 END IF;
1498
1499 EXCEPTION
1500 WHEN OTHERS THEN
1501 IF (l_debug = 1) THEN
1502 MYDEBUG('FETCH_COMBINATION: RAISED EXCEPTION: '||SQLERRM);
1503 END IF;
1504 END FETCH_COMBINATION;
1505
1506 /* Function to get the concatenated Locator Segments (except project and task)
1507 * given Locator Id and Organization Id
1508 */
1509 FUNCTION GET_LOCSEGS(P_LOCATOR_ID IN NUMBER,
1510 P_ORG_ID IN NUMBER) RETURN VARCHAR2 IS
1511 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1512 BEGIN
1513
1514 IF (l_debug = 1) THEN
1515 MYDEBUG('GET_LOCSEGS: P_LOCATOR_ID: P_ORG_ID: '||P_LOCATOR_ID||':'||P_ORG_ID);
1516 END IF;
1517
1518 /* If locator Id passed is not null then fetch the locator, project and
1519 * and task values. Else clear the session variables
1520 */
1521 IF P_LOCATOR_ID IS NOT NULL THEN
1522 FETCH_COMBINATION(P_LOCATOR_ID => P_LOCATOR_ID,
1523 P_ORG_ID => P_ORG_ID);
1524 ELSE
1525 IF (l_debug = 1) THEN
1526 mydebug('loc id is null');
1527 END IF;
1528 CLEAR_GLOBAL_VARS;
1529 END IF;
1530
1531 RETURN G_PHYSICAL_LOCATOR;
1532 END GET_LOCSEGS;
1533
1534 /*
1535 * This function returns the Project Number of the Project Id passed
1536 * Does not use FND API's.
1537 */
1538 FUNCTION GET_PROJECT_NUMBER(p_project_id IN NUMBER) RETURN VARCHAR2 IS
1539 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1540 BEGIN
1541
1542 /* The below code is commented because it uses pjm_projects_mtll_v
1543 which requrires mfg_organization_id profile value needs to be set
1544 whereas inv_projectlocator_pu.get_project_number do not require */
1545
1546 -- If this code is ever uncommneted then the profile mfg_organization_id
1547 -- needs to be set by the calling object otherwise PJM_PROJECTS_MTLL_V
1548 -- will not return a value. Bug 4662395
1549
1550 /*SELECT PROJECT_NUMBER
1551 INTO L_PROJECT_NUMBER
1552 FROM PJM_PROJECTS_MTLL_V
1553 WHERE PROJECT_ID = P_PROJECT_ID;
1554
1555 IF (l_debug = 1) THEN
1556 MYDEBUG('GET_PROJECT_NUMBER: RETURNS '||L_PROJECT_NUMBER);
1557 END IF;
1558 RETURN L_PROJECT_NUMBER;*/
1559 return INV_ProjectLocator_PUB.GET_PROJECT_NUMBER(p_project_id);
1560
1561 END GET_PROJECT_NUMBER;
1562
1563 FUNCTION GET_TASK_NUMBER(P_TASK_ID IN NUMBER) RETURN VARCHAR2 IS
1564 L_TASK_NUMBER VARCHAR2(100);
1565 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1566 BEGIN
1567
1568 /* SELECT TASK_NUMBER
1569 INTO L_TASK_NUMBER
1570 FROM PJM_TASKS_MTLL_V
1571 WHERE TASK_ID = P_TASK_ID;
1572
1573 IF (l_debug = 1) THEN
1574 MYDEBUG('GET_TASK_NUMBER: RETURNS '||L_TASK_NUMBER);
1575 END IF;
1576 RETURN L_TASK_NUMBER;*/
1577 return INV_ProjectLocator_PUB.GET_TASK_NUMBER(P_TASK_ID);
1578
1579 EXCEPTION
1580 WHEN OTHERS THEN
1581 IF (l_debug = 1) THEN
1582 MYDEBUG('GET_TASK_NUMBER: EXCEPTION '||SQLERRM);
1583 END IF;
1584 L_TASK_NUMBER := NULL;
1585 RETURN L_TASK_NUMBER;
1586 END GET_TASK_NUMBER;
1587
1588
1589 /*
1590 * These function are used to get the Project/Task Number of the Last Accessed
1591 * combination. They are for use in select statements e.g.
1592 * SELECT ..
1593 * ..
1594 * INV_PROJECT.GET_LOCSEGS(LOCATOR_ID,ORG_ID)
1595 * INV_PROJECT.GET_PROJECT_ID,
1596 * INV_PROJECT.GET_PROJECT_NUMBER,
1597 * INV_PROJECT.GET_TASK_ID,
1598 * INV_PROJECT.GET_TASK_NUMBER,
1599 * ..
1600 * FROM WMS_LICENSE_PLATE_NUMBERS
1601 *
1602 */
1603 FUNCTION GET_PROJECT_NUMBER RETURN VARCHAR2 IS
1604 BEGIN
1605 RETURN G_PROJECT_NUMBER;
1606 END GET_PROJECT_NUMBER;
1607
1608 FUNCTION GET_PROJECT_ID RETURN VARCHAR2 IS
1609 BEGIN
1610 RETURN G_PROJECT_ID;
1611 END GET_PROJECT_ID;
1612
1613 FUNCTION GET_TASK_NUMBER RETURN VARCHAR2 IS
1614 BEGIN
1615 RETURN G_TASK_NUMBER;
1616 END GET_TASK_NUMBER;
1617
1618 FUNCTION GET_TASK_ID RETURN VARCHAR2 IS
1619 BEGIN
1620 RETURN G_TASK_ID;
1621 END GET_TASK_ID;
1622
1623 /*
1624 * Procedure to obtain the Locator Metadata.
1625 * Processing Logic: Get all the segments and
1626 * their corresponding validation types. If the validation type
1627 * of all the esgments is 'N' then form a string comprising
1628 * concatenated segments with the delimiter
1629 * (eg. SEGMENT1||'.'||SEGMENT2||'.'||SEGMENT3
1630 */
1631 PROCEDURE CONC_LOC_QRY(
1632 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1633 X_MSG_COUNT OUT NOCOPY NUMBER,
1634 X_MSG_DATA OUT NOCOPY VARCHAR2,
1635 X_CONCATENATED OUT NOCOPY VARCHAR2
1636 ) IS
1637
1638 CURSOR CUR_SEG IS
1639 SELECT APPLICATION_COLUMN_NAME,
1640 ffs.FLEX_VALUE_SET_ID,
1641 ffv.VALIDATION_TYPE
1642 FROM FND_ID_FLEX_SEGMENTS ffs,
1643 FND_FLEX_VALUE_SETS ffv
1644 WHERE APPLICATION_ID = 401 -- 'INV'
1645 AND ID_FLEX_CODE = 'MTLL'
1646 AND ID_FLEX_NUM = 101 -- 'STOCK_LOCATORS'
1647 AND ENABLED_FLAG = 'Y'
1648 AND DISPLAY_FLAG = 'Y'
1649 AND ffv.FLEX_VALUE_SET_ID(+) = ffs.FLEX_VALUE_SET_ID
1650 ORDER BY SEGMENT_NUM;
1651
1652 L_RETURN VARCHAR2(90);
1653 L_ROWNUM NUMBER := 0;
1654 L_COLNAME VARCHAR2(15);
1655 L_VALUE_SET_ID NUMBER;
1656 L_VALIDATION_TYPE VARCHAR2(1);
1657 L_CONCAT VARCHAR2(1000);
1658 L_DELIM VARCHAR2(1);
1659 L_FIRST_TIME BOOLEAN := TRUE;
1660 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1661 BEGIN
1662
1663
1664 SELECT CONCATENATED_SEGMENT_DELIMITER
1665 INTO L_DELIM
1666 FROM FND_ID_FLEX_STRUCTURES
1667 WHERE ID_FLEX_CODE = 'MTLL' AND ROWNUM =1;
1668
1669
1670 OPEN CUR_SEG;
1671 LOOP
1672 FETCH CUR_SEG INTO L_COLNAME, L_VALUE_SET_ID, L_VALIDATION_TYPE;
1673 EXIT WHEN CUR_SEG%NOTFOUND;
1674 L_ROWNUM := L_ROWNUM + 1;
1675 IF (L_FIRST_TIME) THEN
1676 IF (L_COLNAME = 'SEGMENT19') THEN
1677 L_CONCAT := 'inv_project.GET_PROJECT_NUMBER('||L_COLNAME||')'; --Changed for bug 3073756
1678 ELSIF(L_COLNAME = 'SEGMENT20') THEN
1679 L_CONCAT := 'inv_project.GET_TASK_NUMBER('||L_COLNAME||')'; --Changed for bug 3073756
1680 ELSIF (L_VALIDATION_TYPE = 'N' OR L_VALIDATION_TYPE IS NULL) THEN
1681 L_CONCAT := L_COLNAME;
1682 ELSE
1683 L_CONCAT := NULL;
1684 END IF;
1685 L_FIRST_TIME := FALSE;
1686 --From the 2nd segement onwards, check if l_concat is not null and the validation
1687 --type for the current segment is 'N' or there is none. If so then append the
1688 --delimiter and segment name to the concatenated segments string
1689 ELSE
1690 IF (L_CONCAT IS NULL) THEN
1691 L_CONCAT := NULL;
1692 ELSIF (L_COLNAME = 'SEGMENT19') THEN
1693 L_CONCAT := L_CONCAT || '||' || '''' || L_DELIM || '''' || '||' || 'inv_project.GET_PROJECT_NUMBER('||L_COLNAME||')';
1694 ELSIF(L_COLNAME = 'SEGMENT20') THEN
1695 L_CONCAT := L_CONCAT || '||' || '''' || L_DELIM || '''' || '||' || 'inv_project.GET_TASK_NUMBER('||L_COLNAME||')';
1696 ELSIF (L_VALIDATION_TYPE = 'N' OR L_VALIDATION_TYPE IS NULL) AND L_CONCAT IS NOT NULL THEN
1697 L_CONCAT := L_CONCAT || '||' || '''' || L_DELIM || '''' || '||' || L_COLNAME;
1698 ELSE
1699 L_CONCAT := NULL;
1700 END IF;
1701 IF (l_debug = 1) THEN
1702 MYDEBUG('L_concat'||L_concat);
1703 END IF;
1704
1705 END IF; --END IF L_FIRST_TIME
1706
1707 END LOOP;
1708 CLOSE CUR_SEG;
1709
1710 IF (l_debug = 1) THEN
1711 MYDEBUG('L_OCNAT ' || L_CONCAT);
1712 END IF;
1713 X_CONCATENATED := L_CONCAT;
1714
1715
1716 FND_MSG_PUB.COUNT_AND_GET( P_COUNT => X_MSG_COUNT, P_DATA => X_MSG_DATA );
1717 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1718
1719 EXCEPTION
1720 WHEN OTHERS THEN
1721
1722 IF (l_debug = 1) THEN
1723 MYDEBUG('EXCEPTION RAISED IN conc_loc_qry '||SQLERRM);
1724 END IF;
1725 X_CONCATENATED := null;
1726
1727 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
1728 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1729 FND_MSG_PUB.ADD_EXC_MSG( g_pkg_name, 'conc_loc_qry');
1730 END IF;
1731 FND_MSG_PUB.COUNT_AND_GET( P_COUNT => X_MSG_COUNT, P_DATA => X_MSG_DATA );
1732
1733 END CONC_LOC_QRY;
1734
1735
1736 /*
1737 * Get the concatenated and resolved locator
1738 * currently used by material workbench
1739 */
1740
1741
1742 FUNCTION GET_LOCATOR(P_LOCATOR_ID IN NUMBER,
1743 P_ORG_ID IN NUMBER) RETURN VARCHAR2 IS
1744 L_CC_ID_RET BOOLEAN;
1745 TYPE CUR_LOC IS REF CURSOR;
1746 c_locator CUR_LOC;
1747 l_loc_str VARCHAR2(1000);
1748 X_RETURN_STATUS VARCHAR2(1);
1749 X_MSG_DATA VARCHAR2(1000);
1750 X_MSG_COUNT NUMBER;
1751 L_CONCATED_LOCATOR VARCHAR2(400);
1752
1753 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1754 BEGIN
1755
1756 IF (l_debug = 1) THEN
1757 MYDEBUG('GET_LOCATOR: P_LOCATOR_ID: P_ORG_ID: '||P_LOCATOR_ID||' : '||P_ORG_ID);
1758 END IF;
1759 IF (G_LOC_QRY_BLT <> 'Y') THEN
1760 G_LOC_QRY_BLT := 'Y';
1761 CONC_LOC_QRY(
1762 X_RETURN_STATUS => X_RETURN_STATUS,
1763 X_MSG_COUNT => X_MSG_COUNT,
1764 X_MSG_DATA => X_MSG_DATA,
1765 X_CONCATENATED => G_LOC_CONC_QRY
1766 );
1767 IF (l_debug = 1) THEN
1768 MYDEBUG('GET_LOCATOR: GET_LOCATOR_METADATA: '||X_RETURN_STATUS);
1769 END IF;
1770 END IF;
1771
1772 IF G_LOC_CONC_QRY IS NOT NULL THEN
1773
1774 IF (l_debug = 1) THEN
1775 MYDEBUG('Using MTL_ITEM_LOCATIONS to get locator, project and task info');
1776 END IF;
1777 --Build the string to fetch locator from MTL_ITEM_LOCATIONS
1778 l_loc_str := 'SELECT ' || G_LOC_CONC_QRY ;
1779 l_loc_str := l_loc_str || ' FROM MTL_ITEM_LOCATIONS ';
1780 l_loc_str := l_loc_str || ' WHERE INVENTORY_LOCATION_ID = :1 AND ORGANIZATION_ID = :2';
1781
1782 IF (l_debug = 1) THEN
1783 MYDEBUG('l_loc_str:' || l_loc_str);
1784 END IF;
1785
1786 --Open the cursor and store the results in session variables
1787 OPEN c_locator FOR l_loc_str USING P_LOCATOR_ID, P_ORG_ID;
1788 FETCH c_locator INTO l_concated_locator;
1789 CLOSE c_locator;
1790 G_CONCATENATED_LOCATOR := l_concated_locator;
1791
1792 --If any of the segments has a validation type other than 'N'
1793 ELSE
1794 IF (l_debug = 1) THEN
1795 MYDEBUG('Using FND APIs to get locator, project and task info');
1796 END IF;
1797 L_CC_ID_RET := FND_FLEX_KEYVAL.VALIDATE_CCID(
1798 APPL_SHORT_NAME => 'INV',
1799 KEY_FLEX_CODE => 'MTLL',
1800 STRUCTURE_NUMBER => 101,
1801 COMBINATION_ID => P_LOCATOR_ID,
1802 DISPLAYABLE => 'ALL',
1803 DATA_SET => P_ORG_ID,
1804 VRULE => NULL,
1805 SECURITY => 'IGNORE',
1806 GET_COLUMNS => NULL,
1807 RESP_APPL_ID => 401,
1808 RESP_ID => NULL,
1809 USER_ID => NULL,
1810 SELECT_COMB_FROM_VIEW => NULL
1811 );
1812 IF NOT L_CC_ID_RET THEN
1813 IF (l_debug = 1) THEN
1814 MYDEBUG('GET_LOCATOR: VALIDATE_CCID: CALL FAILED');
1815 MYDEBUG('GET_LOCATOR: VALIDATE_CCID: '||FND_FLEX_KEYVAL.ERROR_MESSAGE);
1816 END IF;
1817 ELSE
1818 G_CONCATENATED_LOCATOR := FND_FLEX_KEYVAL.CONCATENATED_VALUES;
1819 END IF;
1820
1821 END IF; --END If g_concated_segments IS NOT NULL
1822 IF (l_debug = 1) THEN
1823 MYDEBUG('LOCATOR: ' || G_CONCATENATED_LOCATOR);
1824 END IF;
1825 return G_CONCATENATED_LOCATOR;
1826
1827 EXCEPTION
1828 WHEN OTHERS THEN
1829 IF (l_debug = 1) THEN
1830 MYDEBUG('GET_LOCATOR: RAISED EXCEPTION: '||SQLERRM);
1831 END IF;
1832 RETURN NULL; --Added for bug 3073756
1833 END GET_LOCATOR;
1834
1835 /*Added overloaded function for performance enhancements...*/
1836 FUNCTION GET_LOCSEGS(p_concatenated_segments IN VARCHAR2)
1837 RETURN VARCHAR2 IS
1838 CURSOR cur_seg IS
1839 SELECT application_column_name
1840 FROM fnd_id_flex_segments ffs
1841 WHERE application_id = 401 -- 'INV'
1842 AND id_flex_code = 'MTLL'
1843 AND id_flex_num = 101 -- 'STOCK_LOCATORS'
1844 AND enabled_flag = 'Y'
1845 AND display_flag = 'Y'
1846 ORDER BY segment_num;
1847
1848 CURSOR c_delim IS
1849 SELECT concatenated_segment_delimiter
1850 FROM fnd_id_flex_structures
1851 WHERE id_flex_code = 'MTLL'
1852 AND ROWNUM = 1;
1853
1854 l_row_num NUMBER := 1;
1855 l_buf VARCHAR2(491);--BUG3306367
1856 l_new_segments VARCHAR2(491) := '';--BUG3306367
1857 i BINARY_INTEGER := 1;
1858 j BINARY_INTEGER := 1;
1859 k BINARY_INTEGER := 1;
1860 l_col_index BINARY_INTEGER := 1;
1861 BEGIN
1862 IF g_delimiter IS NULL THEN
1863 g_project_index := 0;
1864 g_task_index := 0;
1865 OPEN c_delim;
1866 FETCH c_delim INTO g_delimiter;
1867 CLOSE c_delim;
1868
1869 FOR rec1 IN cur_seg LOOP
1870 IF rec1.application_column_name = 'SEGMENT19' THEN
1871 g_project_index := l_row_num;
1872 END IF;
1873
1874 IF rec1.application_column_name = 'SEGMENT20' THEN
1875 g_task_index := l_row_num;
1876 END IF;
1877
1878 l_row_num := l_row_num + 1;
1879 END LOOP;
1880 END IF;
1881
1882 IF g_project_index = 0 THEN
1883 RETURN p_concatenated_segments;
1884 END IF;
1885
1886 LOOP
1887 <<skip_segment>>
1888 j := INSTR(p_concatenated_segments, g_delimiter, 1, i);
1889
1890 /*
1891 * j is initialized to 1, j is 0 only when the last
1892 * instance of the delimitor is already found.
1893 * In such a case fetch the rest of the string rather than
1894 * a substr..
1895 */
1896 IF j = 0 THEN
1897 l_buf := SUBSTR(p_concatenated_segments, k);
1898 ELSE
1899 IF SUBSTR(p_concatenated_segments, j - 1, 1) = '\' THEN
1900 i := i + 1;
1901 GOTO skip_segment;
1902 END IF;
1903
1904 l_buf := SUBSTR(p_concatenated_segments, k,(j - k));
1905 END IF;
1906
1907 IF l_col_index = g_project_index OR l_col_index = g_task_index THEN
1908 NULL;
1909 ELSE
1910 IF l_new_segments IS NULL THEN
1911 l_new_segments := l_buf;
1912 /*bug 3905395 */
1913 IF l_buf IS NULL THEN
1914 l_new_segments := fnd_api.g_miss_char;
1915 END IF;
1916 ELSE
1917 l_new_segments := l_new_segments || g_delimiter || l_buf;
1918 END IF;
1919 END IF;
1920
1921 k := j + 1;
1922 i := i + 1;
1923 l_col_index := l_col_index + 1;
1924 EXIT WHEN j = 0;
1925 END LOOP;
1926
1927 RETURN l_new_segments;
1928 END get_locsegs;
1929
1930 FUNCTION GET_PJM_LOCSEGS(p_concatenated_segments IN VARCHAR2)
1931 RETURN VARCHAR2 IS
1932 CURSOR cur_seg IS
1933 SELECT application_column_name
1934 FROM fnd_id_flex_segments ffs
1935 WHERE application_id = 401 -- 'INV'
1936 AND id_flex_code = 'MTLL'
1937 AND id_flex_num = 101 -- 'STOCK_LOCATORS'
1938 AND enabled_flag = 'Y'
1939 AND display_flag = 'Y'
1940 ORDER BY segment_num;
1941
1942 CURSOR c_delim IS
1943 SELECT concatenated_segment_delimiter
1944 FROM fnd_id_flex_structures
1945 WHERE id_flex_code = 'MTLL'
1946 AND ROWNUM = 1;
1947
1948 l_row_num NUMBER := 1;
1949 l_buf VARCHAR2(491);--BUG3306367
1950 l_new_segments VARCHAR2(1000) := '';--BUG4700952
1951 i BINARY_INTEGER := 1;
1952 j BINARY_INTEGER := 1;
1953 k BINARY_INTEGER := 1;
1954 l_col_index BINARY_INTEGER := 1;
1955 l_project_id NUMBER;
1956 l_task_id NUMBER;
1957 l_pt_buf VARCHAR2(30);
1958 BEGIN
1959 IF p_concatenated_segments is null THEN
1960 return(NULL);
1961 END IF;
1962 IF g_delimiter IS NULL THEN
1963 g_project_index := 0;
1964 g_task_index := 0;
1965 OPEN c_delim;
1966 FETCH c_delim INTO g_delimiter;
1967 CLOSE c_delim;
1968
1969 FOR rec1 IN cur_seg LOOP
1970 IF rec1.application_column_name = 'SEGMENT19' THEN
1971 g_project_index := l_row_num;
1972 END IF;
1973
1974 IF rec1.application_column_name = 'SEGMENT20' THEN
1975 g_task_index := l_row_num;
1976 END IF;
1977
1978 l_row_num := l_row_num + 1;
1979 END LOOP;
1980 END IF;
1981
1982 IF g_project_index = 0 THEN
1983 RETURN p_concatenated_segments;
1984 END IF;
1985
1986 LOOP
1987 <<skip_segment>>
1988 j := INSTR(p_concatenated_segments, g_delimiter, 1, i);
1989
1990 /*
1991 * j is initialized to 1, j is 0 only when the last
1992 * instance of the delimitor is already found.
1993 * In such a case fetch the rest of the string rather than
1994 * a substr..
1995 */
1996 IF j = 0 THEN
1997 l_buf := SUBSTR(p_concatenated_segments, k);
1998 ELSE
1999 IF SUBSTR(p_concatenated_segments, j - 1, 1) = '\' THEN
2000 i := i + 1;
2001 GOTO skip_segment;
2002 END IF;
2003
2004 l_buf := SUBSTR(p_concatenated_segments, k,(j - k));
2005 END IF;
2006
2007 IF l_col_index = g_project_index OR l_col_index = g_task_index THEN
2008
2009 -- 4662395 For this function
2010 -- the calling object will be required
2011 -- to set the profile mfg_organization_id.
2012 -- If this profile remains unset then
2013 -- no value will return from PJM_PROJECTS_ALL_V
2014
2015 if (l_col_index = g_project_index) then
2016 l_project_id := to_number(l_buf);
2017 BEGIN
2018 SELECT m.project_number
2019 INTO l_pt_buf
2020 FROM pjm_projects_all_v m
2021 WHERE m.project_id = l_project_id
2022 AND ROWNUM = 1;
2023 EXCEPTION
2024 WHEN OTHERS THEN l_pt_buf := '';
2025 END;
2026 else
2027 BEGIN
2028 l_task_id := to_number(l_buf);
2029 SELECT m.task_number
2030 INTO l_pt_buf
2031 FROM pjm_tasks_v m
2032 WHERE m.task_id = l_task_id
2033 AND m.project_id = l_project_id
2034 AND ROWNUM = 1;
2035 EXCEPTION
2036 WHEN OTHERS THEN l_pt_buf := '';
2037 END;
2038
2039 end if;
2040
2041 l_new_segments := l_new_segments || g_delimiter || l_pt_buf;
2042 ELSE
2043 IF l_new_segments IS NULL THEN
2044 l_new_segments := l_buf;
2045 /*bug 3905395 */
2046 IF l_buf IS NULL THEN
2047 l_new_segments := fnd_api.g_miss_char;
2048 END IF;
2049 ELSE
2050 l_new_segments := l_new_segments || g_delimiter || l_buf;
2051 END IF;
2052 END IF;
2053
2054 k := j + 1;
2055 i := i + 1;
2056 l_col_index := l_col_index + 1;
2057 EXIT WHEN j = 0;
2058 END LOOP;
2059
2060 RETURN l_new_segments;
2061 END get_pjm_locsegs;
2062
2063
2064
2065 END inv_project;
[Home] [Help]