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