1 PACKAGE BODY PJM_PROJECT_LOCATOR AS
2 /* $Header: PJMPLOCB.pls 120.9 2011/02/25 08:19:34 yaoli ship $ */
3
4 -- ---------------------------------------------------------------------
5 -- Global Variables
6 -- ---------------------------------------------------------------------
7 G_project_number VARCHAR2(25);
8 G_task_number VARCHAR2(25);
9
10 -- ---------------------------------------------------------------------
11 -- Private Functions and Procedures
12 -- ---------------------------------------------------------------------
13
14 -- Name : Get_Project_References
15 -- Pre-reqs : None.
16 -- Function : This local function resolves the displayed project
17 -- and task references for messaging purpose
18 --
19 --
20 -- Parameters :
21 -- IN : p_project_id IN NUMBER
22 -- : p_task_id IN NUMBER
23 --
24 -- Returns : The concatenated project_number and task_number
25 --
26 -- Version : 1.0 8/22/97 A Law
27 -- Notes :
28 --
29 FUNCTION Get_Project_References(
30 p_project_id IN NUMBER,
31 p_task_id IN NUMBER
32 ) RETURN VARCHAR2 IS
33
34 l_prj_ref varchar2(100);
35
36 BEGIN
37 if (p_project_id is null) then
38 l_prj_ref := 'NULL';
39 elsif (p_task_id is null) then
40 l_prj_ref := pjm_project.all_proj_idtonum( p_project_id );
41 else
42 l_prj_ref := pjm_project.all_proj_idtonum( p_project_id )
43 || ' , ' || pjm_project.all_task_idtonum( p_task_id );
44 end if;
45 return (l_prj_ref);
46 END Get_Project_References;
47
48
49
50 -- Name : Get_Component_ProjectSupply
51 -- Pre-reqs : None.
52 -- Function :
53 --
54 --
55 -- Parameters :
56 -- IN : p_organization_id IN NUMBER
57 -- : p_project_id IN NUMBER
58 -- : p_task_id IN NUMBER
59 -- : p_wip_entity_id IN NUMBER
60 -- : p_supply_sub IN NUMBER
61 -- : p_supply_loc_id IN OUT NUMBER
62 -- : p_item_id IN NUMBER
63 -- : p_org_loc_control IN NUMBER
64 --
65 -- Returns : BOOLEAN
66 -- The output of p_supply_loc_id will be the
67 -- proper locator with project and task
68 -- references based on the pegging attribute
69 -- of the component.
70 --
71 -- Version : 1.0 8/22/97 D Soosai
72 -- Notes :
73 --
74 FUNCTION Get_Component_ProjectSupply (
75 p_organization_id IN NUMBER,
76 p_project_id IN NUMBER,
77 p_task_id IN NUMBER,
78 p_wip_entity_id IN NUMBER,
79 p_supply_sub IN VARCHAR2,
80 p_supply_loc_id IN OUT NOCOPY NUMBER,
81 p_item_id IN NUMBER,
82 p_org_loc_control IN NUMBER
83 ) RETURN BOOLEAN IS
84
85 l_loc_id NUMBER := 0;
86 l_peg_flag VARCHAR2(1) := NULL;
87 subinv_mismatch EXCEPTION;
88
89 CURSOR c1 IS
90 SELECT end_assembly_pegging_flag
91 FROM mtl_system_items
92 WHERE inventory_item_id = p_item_id
93 AND organization_id = p_organization_id;
94
95 BEGIN
96
97 --
98 -- Bug 1218478
99 -- A. Law 03/01/2000
100 --
104 --
101 -- Originally, this procedure only process those items that are
102 -- hard pegged. We assume that the supply locator associated on
103 -- the BOM is always a common locator.
105 -- However, if a project locator is erroneously specified as the
106 -- supply locator for a soft-pegged item, original logic will
107 -- ignore this record and may result in an incorrect supply
108 -- locator with a different project/task reference.
109 --
110 -- Fortunately, the procedure Get_DefaultProjectLocator already
111 -- handles defaulting of project locator for "NULL" project, i.e.
112 -- common.
113 --
114 -- This procedure has been enhanced to handle both soft-pegged and
115 -- hard-pegged items
116 --
117
118 --
119 -- Bug 1402388
120 -- A. Law 09/17/2000
121 --
122 -- Split out branch logic for hard-pegged / soft-pegged from
123 -- locator control logic
124 --
125 OPEN c1;
126 FETCH c1 INTO l_peg_flag;
127 CLOSE c1;
128
129 IF ( l_peg_flag IN ( 'I' , 'X' ) ) THEN
130 IF (Check_ItemLocatorControl(p_organization_id,
131 p_supply_sub, p_supply_loc_id, p_item_id, 0)) THEN
132
133 Get_DefaultProjectLocator(p_organization_id,
134 p_supply_loc_id,
135 p_project_id,
136 p_task_id,
137 l_loc_id);
138 END IF;
139 ELSE
140 Get_DefaultProjectLocator(p_organization_id,
141 p_supply_loc_id,
142 NULL,
143 NULL,
144 l_loc_id);
145
146 END IF;
147
148 --
149 -- Call user procedure Get_User_Project_Supply
150 -- if user provides further specific business logic
151 -- for defaulting.
152 -- Updates Wip_Requirement_Operations with the
153 -- new supply subinventory and supply locator.
154 --
155 PJM_UserProjectLocator_Pub.Get_UserProjectSupply(
156 p_item_id,
157 p_organization_id,
158 p_wip_entity_id,
159 l_loc_id);
160
161 /* FP bugs 8744389/8754723/8744391 (refer to 8686512):
162 * When locator mismatch happens, locator id is set to -1
163 * Raise an exception to indicate mismatch
164 * This will affect the callers of Get_Component_ProjectSupply,
165 * Get_Job_ProjectSupply, and Get_Flow_ProjectSupply.
166 * Only WIP code calls these three procedures/functions.
167 * Tests are conducted from WIP, FLOW and EAM flows, which use
168 * WIP UI/functionality, to make sure this change doesn't break
169 * existing functionality in any other module
170 */
171 IF l_loc_id = -1 THEN
172 raise subinv_mismatch;
173 END IF;
174
175 IF l_loc_id <> 0 THEN
176 p_supply_loc_id := L_loc_id ;
177 END IF;
178 return(TRUE);
179 END Get_Component_ProjectSupply;
180
181
182 -- Name : Map_Locator
183 -- Pre-reqs : None.
184 -- Function : This local function tries to map a locator with
185 -- given physical locator segments, project and task.
186 -- If match not found, this function can optionally
187 -- creates one.
188 --
189 --
190 -- Parameters :
191 -- IN : X_organization_id IN NUMBER
192 -- : X_locator_id IN NUMBER
193 -- : X_project_id IN NUMBER
194 -- : X_task_id IN NUMBER
195 -- : X_create_locator IN BOOLEAN
196 --
197 -- Returns : Locator_ID of the matched locator
198 --
199 -- Version : 1.0 12/22/97 A Law
200 -- Notes :
201 --
202 FUNCTION map_locator ( X_organization_id IN NUMBER
203 , X_locator_id IN NUMBER
204 , X_project_id IN NUMBER
205 , X_task_id IN NUMBER
206 , X_create_locator IN BOOLEAN )
207 RETURN number
208 is
209 cursor C1 is
210 select application_column_name, required_flag
211 from fnd_id_flex_segments
212 where application_id = 401
213 and id_flex_code = 'MTLL'
214 and id_flex_num = 101
215 and application_column_name not in ('SEGMENT19','SEGMENT20')
216 and nvl(enabled_flag, 'N') = 'Y'
217 order by segment_num;
218 L_locator_id NUMBER;
219 L_locator_ctrl NUMBER;
220 L_subinv_match NUMBER;
221 L_subinv VARCHAR2(10);
222 L_stmt VARCHAR2(2000);
223 L_user_id NUMBER;
224 c INTEGER;
225 rows_processed INTEGER;
226 subinv_mismatch EXCEPTION;
227
228 begin
229 L_stmt := 'SELECT l2.inventory_location_id, ' ||
230 'l2.subinventory_code, ' ||
231 'DECODE(l1.subinventory_code, ' ||
232 'l2.subinventory_code, 1, ' ||
233 'NULL, NULL, 0) ' ||
234 'FROM mtl_item_locations l1 ' ||
235 ', mtl_item_locations l2 ' ||
236 'WHERE l1.organization_id = :org_id ' ||
237 'AND l2.organization_id = l1.organization_id ' ||
238 'AND l1.inventory_location_id = :locator_id ';
239
240 for c1rec in c1 loop
241 if (c1rec.application_column_name = 'SEGMENT1') then
242 if (c1rec.required_flag = 'Y') then
246 end if;
243 L_stmt := L_stmt || 'AND l2.segment1 = l1.segment1 ';
244 else
245 L_stmt := L_stmt || 'AND nvl(l2.segment1,'' '') = nvl(l1.segment1,'' '') ';
247 elsif (c1rec.application_column_name = 'SEGMENT2') then
248 if (c1rec.required_flag = 'Y') then
249 L_stmt := L_stmt || 'AND l2.segment2 = l1.segment2 ';
250 else
251 L_stmt := L_stmt || 'AND nvl(l2.segment2,'' '') = nvl(l1.segment2,'' '') ';
252 end if;
253 elsif (c1rec.application_column_name = 'SEGMENT3') then
254 if (c1rec.required_flag = 'Y') then
255 L_stmt := L_stmt || 'AND l2.segment3 = l1.segment3 ';
256 else
257 L_stmt := L_stmt || 'AND nvl(l2.segment3,'' '') = nvl(l1.segment3,'' '') ';
258 end if;
259 elsif (c1rec.application_column_name = 'SEGMENT4') then
260 if (c1rec.required_flag = 'Y') then
261 L_stmt := L_stmt || 'AND l2.segment4 = l1.segment4 ';
262 else
263 L_stmt := L_stmt || 'AND nvl(l2.segment4,'' '') = nvl(l1.segment4,'' '') ';
264 end if;
265 elsif (c1rec.application_column_name = 'SEGMENT5') then
266 if (c1rec.required_flag = 'Y') then
267 L_stmt := L_stmt || 'AND l2.segment5 = l1.segment5 ';
268 else
269 L_stmt := L_stmt || 'AND nvl(l2.segment5,'' '') = nvl(l1.segment5,'' '') ';
270 end if;
271 elsif (c1rec.application_column_name = 'SEGMENT6') then
272 if (c1rec.required_flag = 'Y') then
273 L_stmt := L_stmt || 'AND l2.segment6 = l1.segment6 ';
274 else
275 L_stmt := L_stmt || 'AND nvl(l2.segment6,'' '') = nvl(l1.segment6,'' '') ';
276 end if;
277 elsif (c1rec.application_column_name = 'SEGMENT7') then
278 if (c1rec.required_flag = 'Y') then
279 L_stmt := L_stmt || 'AND l2.segment7 = l1.segment7 ';
280 else
281 L_stmt := L_stmt || 'AND nvl(l2.segment7,'' '') = nvl(l1.segment7,'' '') ';
282 end if;
283 elsif (c1rec.application_column_name = 'SEGMENT8') then
284 if (c1rec.required_flag = 'Y') then
285 L_stmt := L_stmt || 'AND l2.segment8 = l1.segment8 ';
286 else
287 L_stmt := L_stmt || 'AND nvl(l2.segment8,'' '') = nvl(l1.segment8,'' '') ';
288 end if;
289 elsif (c1rec.application_column_name = 'SEGMENT9') then
290 if (c1rec.required_flag = 'Y') then
291 L_stmt := L_stmt || 'AND l2.segment9 = l1.segment9 ';
292 else
293 L_stmt := L_stmt || 'AND nvl(l2.segment9,'' '') = nvl(l1.segment9,'' '') ';
294 end if;
295 elsif (c1rec.application_column_name = 'SEGMENT10') then
296 if (c1rec.required_flag = 'Y') then
297 L_stmt := L_stmt || 'AND l2.segment10 = l1.segment10 ';
298 else
299 L_stmt := L_stmt || 'AND nvl(l2.segment10,'' '') = nvl(l1.segment10,'' '') ';
300 end if;
301 elsif (c1rec.application_column_name = 'SEGMENT11') then
302 if (c1rec.required_flag = 'Y') then
303 L_stmt := L_stmt || 'AND l2.segment11 = l1.segment11 ';
304 else
305 L_stmt := L_stmt || 'AND nvl(l2.segment11,'' '') = nvl(l1.segment11,'' '') ';
306 end if;
307 elsif (c1rec.application_column_name = 'SEGMENT12') then
308 if (c1rec.required_flag = 'Y') then
309 L_stmt := L_stmt || 'AND l2.segment12 = l1.segment12 ';
310 else
311 L_stmt := L_stmt || 'AND nvl(l2.segment12,'' '') = nvl(l1.segment12,'' '') ';
312 end if;
313 elsif (c1rec.application_column_name = 'SEGMENT13') then
314 if (c1rec.required_flag = 'Y') then
315 L_stmt := L_stmt || 'AND l2.segment13 = l1.segment13 ';
316 else
317 L_stmt := L_stmt || 'AND nvl(l2.segment13,'' '') = nvl(l1.segment13,'' '') ';
318 end if;
319 elsif (c1rec.application_column_name = 'SEGMENT14') then
320 if (c1rec.required_flag = 'Y') then
321 L_stmt := L_stmt || 'AND l2.segment14 = l1.segment14 ';
322 else
323 L_stmt := L_stmt || 'AND nvl(l2.segment14,'' '') = nvl(l1.segment14,'' '') ';
324 end if;
325 elsif (c1rec.application_column_name = 'SEGMENT15') then
326 if (c1rec.required_flag = 'Y') then
327 L_stmt := L_stmt || 'AND l2.segment15 = l1.segment15 ';
328 else
329 L_stmt := L_stmt || 'AND nvl(l2.segment15,'' '') = nvl(l1.segment15,'' '') ';
330 end if;
331 elsif (c1rec.application_column_name = 'SEGMENT16') then
332 if (c1rec.required_flag = 'Y') then
333 L_stmt := L_stmt || 'AND l2.segment16 = l1.segment16 ';
334 else
335 L_stmt := L_stmt || 'AND nvl(l2.segment16,'' '') = nvl(l1.segment16,'' '') ';
336 end if;
337 elsif (c1rec.application_column_name = 'SEGMENT17') then
338 if (c1rec.required_flag = 'Y') then
339 L_stmt := L_stmt || 'AND l2.segment17 = l1.segment17 ';
340 else
341 L_stmt := L_stmt || 'AND nvl(l2.segment17,'' '') = nvl(l1.segment17,'' '') ';
342 end if;
343 elsif (c1rec.application_column_name = 'SEGMENT18') then
344 if (c1rec.required_flag = 'Y') then
345 L_stmt := L_stmt || 'AND l2.segment18 = l1.segment18 ';
346 else
347 L_stmt := L_stmt || 'AND nvl(l2.segment18,'' '') = nvl(l1.segment18,'' '') ';
348 end if;
349 end if;
350 end loop;
351 if (X_project_id is null) then
352 L_stmt := L_stmt || 'AND l2.project_id is null';
353 else
354 if (X_task_id is null) then
355 L_stmt := L_stmt || 'AND l2.project_id = :project_id ' ||
356 'AND l2.task_id is null';
357 else
358 L_stmt := L_stmt || 'AND l2.project_id = :project_id ' ||
359 'AND l2.task_id = :task_id';
360 end if;
361 end if;
362 c := dbms_sql.open_cursor;
363 dbms_sql.parse(c, l_stmt, dbms_sql.native);
367 dbms_sql.bind_variable(c, 'project_id', X_project_id);
364 dbms_sql.bind_variable(c, 'org_id', X_organization_id);
365 dbms_sql.bind_variable(c, 'locator_id', X_locator_id);
366 if (X_project_id is not null) then
368 end if;
369 if (X_task_id is not null) then
370 dbms_sql.bind_variable(c, 'task_id', X_task_id);
371 end if;
372 dbms_sql.define_column(c,1,L_locator_id);
373 dbms_sql.define_column(c,2,L_subinv,10);
374 dbms_sql.define_column(c,3,L_subinv_match);
375 rows_processed := dbms_sql.execute(c);
376 if dbms_sql.fetch_rows(c) > 0 then
377 dbms_sql.column_value(c,1,L_locator_id);
378 dbms_sql.column_value(c,2,L_subinv);
379 dbms_sql.column_value(c,3,L_subinv_match);
380 else
381 L_locator_id := -1;
382 L_subinv_match := null;
383 end if;
384 dbms_sql.close_cursor(c);
385
386 if (L_locator_id > 0 and L_subinv_match = 0) then
387 raise subinv_mismatch;
388 end if;
389
390 if (L_locator_id = -1 and X_create_locator) then
391 select mtl_item_locations_s.nextval
392 into L_locator_id
393 from dual;
394
395 L_user_id := fnd_profile.value('USER_ID');
396
397 insert into mtl_item_locations
398 ( last_update_date
399 , last_updated_by
400 , creation_date
401 , created_by
402 , inventory_location_id
403 , organization_id
404 , segment1
405 , segment2
406 , segment3
407 , segment4
408 , segment5
409 , segment6
410 , segment7
411 , segment8
412 , segment9
413 , segment10
414 , segment11
415 , segment12
416 , segment13
417 , segment14
418 , segment15
419 , segment16
420 , segment17
421 , segment18
422 , segment19
423 , segment20
424 , summary_flag
425 , enabled_flag
426 , subinventory_code
427 , physical_location_id)
428 select sysdate
429 , L_user_id
430 , sysdate
431 , L_user_id
432 , L_locator_id
433 , X_organization_id
434 , segment1
435 , segment2
436 , segment3
437 , segment4
438 , segment5
439 , segment6
440 , segment7
441 , segment8
442 , segment9
443 , segment10
444 , segment11
445 , segment12
446 , segment13
447 , segment14
448 , segment15
449 , segment16
450 , segment17
451 , segment18
452 , X_project_id
453 , X_task_id
454 , 'N'
455 , 'Y'
456 , subinventory_code
457 , decode(X_project_id,
458 NULL, L_locator_id,
459 nvl(physical_location_id,inventory_location_id)
460 )
461 from mtl_item_locations
462 where organization_id = X_organization_id
463 and inventory_location_id = X_locator_id;
464
465 --
466 -- 11.09.1999
467 --
468 -- The following conditional statement has been added to check
469 -- locator control for the desired subinventory. We would only
470 -- propagate restricted locators if locator control is set at
471 -- the item level. This is to eliminate unnecessary inserts.
472 --
473 -- 05.16.2002
474 --
475 -- The following SQL has been modified slightly to account for
476 -- cases when subinventory code is NULL on mtl_item_locations.
477 --
478 select nvl(msi.locator_type , 0)
479 into L_locator_ctrl
480 from mtl_secondary_inventories msi
481 , mtl_item_locations mil
482 where mil.organization_id = X_organization_id
483 and mil.inventory_location_id = X_locator_id
484 and msi.organization_id (+) = mil.organization_id
485 and msi.secondary_inventory_name (+) = mil.subinventory_code;
486
487 if ( L_locator_ctrl = 5 ) then
488 --
489 -- Locator control is determined at the item level, we need to
490 -- propagate restricted locators for all items from the original
491 -- locator to the newly created locator
492 --
493 insert into mtl_secondary_locators
494 ( last_update_date
495 , last_updated_by
496 , creation_date
497 , created_by
498 , secondary_locator
499 , inventory_item_id
500 , organization_id
501 , subinventory_code)
502 select sysdate
503 , L_user_id
504 , sysdate
505 , L_user_id
506 , L_locator_id
507 , inventory_item_id
508 , X_organization_id
509 , subinventory_code
510 from mtl_secondary_locators msl
511 where organization_id = X_organization_id
512 and secondary_locator = X_locator_id
513 and exists (
514 select 'Locator restriction is on'
515 from mtl_system_items
516 where organization_id = msl.organization_id
517 and inventory_item_id = msl.inventory_item_id
518 and restrict_locators_code = 1);
519
520 end if; /* L_locator_ctrl = 5 */
521
522 end if;
523
524 return(L_locator_id);
525
526 exception
530 return(-1);
527 when subinv_mismatch then
528 fnd_message.set_name('PJM', 'LOC-DUPLICATE PHY LOC');
529 fnd_message.set_token('SUBINV', L_subinv);
531 when others then
532 raise;
533 end Map_Locator;
534
535
536 -- Name : Get_Physical_Location
537 -- Pre-reqs : None.
538 -- Function : This local function resolves the physical locator
539 -- for any given locator
540 --
541 --
542 -- Parameters :
543 -- IN : X_organization_id IN NUMBER
544 -- : X_locator_id IN NUMBER
545 --
546 -- Returns : TRUE is successful, FALSE otherwise
547 --
548 -- Version : 1.0 12/22/97 A Law
549 -- Notes :
550 --
551 FUNCTION get_physical_location ( X_organization_id IN NUMBER
552 , X_locator_id IN NUMBER )
553 RETURN BOOLEAN
554 IS
555 L_project_id number;
556 L_task_id number;
557 L_phy_loc_id number;
558 begin
559 select project_id
560 , task_id
561 , physical_location_id
562 into L_project_id
563 , L_task_id
564 , L_phy_loc_id
565 from mtl_item_locations
566 where organization_id = X_organization_id
567 and inventory_location_id = X_locator_id;
568
569 if (L_phy_loc_id is not null) then
570 return TRUE;
571 end if;
572
573 if (L_project_id is null) then
574 L_phy_loc_id := X_locator_id;
575 else
576 L_phy_loc_id := map_locator( X_organization_id
577 , X_locator_id
578 , NULL
579 , NULL
580 , TRUE );
581 if (L_phy_loc_id = -1) then
582 return FALSE;
583 end if;
584 end if;
585
586 update mtl_item_locations
587 set physical_location_id = L_phy_loc_id
588 where organization_id = X_organization_id
589 and inventory_location_id = X_locator_id;
590
591 return TRUE;
592
593 exception
594 when others then
595 return FALSE;
596
597 end Get_Physical_Location;
598
599
600 -- Name : Match_Proj_By_Plan_Grp
601 -- Pre-reqs : None.
602 -- Function : This local function matches any two given projects
603 -- by project or costing group and planning group
604 --
605 --
606 -- Parameters :
607 -- IN : X_organization_id IN NUMBER
608 -- : X_proj_ctrl_level IN NUMBER
609 -- : X_txn_project IN NUMBER
610 -- : X_txn_task IN NUMBER
611 -- : X_loc_project IN NUMBER
612 -- : X_loc_task IN NUMBER
613 -- : X_item_id IN NUMBER
614 --
615 -- Returns : TRUE if match is positive, FALSE otherwise
616 --
617 -- Version : 1.0 12/22/97 A Law
618 -- Notes :
619 --
620 FUNCTION match_proj_by_plan_grp ( X_organization_id IN NUMBER
621 , X_proj_ctrl_level IN NUMBER
622 , X_txn_project IN NUMBER
623 , X_txn_task IN NUMBER
624 , X_loc_project IN NUMBER
625 , X_loc_task IN NUMBER
626 , X_item_id IN NUMBER)
627 RETURN BOOLEAN
628 IS
629
630 L_allow_xprj_issue VARCHAR2(1);
631 L_cost_method NUMBER;
632 L_loc_cost_grp NUMBER;
633 L_loc_plan_grp VARCHAR2(30);
634 L_loc_prj_ref VARCHAR2(60);
635 L_txn_cost_grp NUMBER;
636 L_txn_plan_grp VARCHAR2(30);
637 L_txn_prj_ref VARCHAR2(60);
638 L_peg_flag VARCHAR2(1);
639
640 CURSOR Allow_XPrj_Issue ( C_organization_id NUMBER ) IS
641 SELECT nvl(allow_cross_proj_issues,'N')
642 FROM pjm_org_parameters
643 WHERE organization_id = C_organization_id;
644
645 CURSOR C_Cost_Method ( C_organization_id NUMBER) IS
646 SELECT primary_cost_method
647 FROM mtl_parameters
648 WHERE organization_id = C_organization_id;
649
650 CURSOR C_Proj_Param ( C_organization_id NUMBER
651 , C_project_id NUMBER) IS
652 SELECT planning_group
653 , nvl(costing_group_id,0)
654 FROM pjm_project_parameters
655 WHERE organization_id = C_organization_id
656 AND project_id = C_project_id;
657
658
659 CURSOR C_Peg_Flag ( C_organization_id NUMBER
660 , C_item_id NUMBER) IS
661 SELECT end_assembly_pegging_flag
662 FROM mtl_system_items
663 WHERE inventory_item_id = C_item_id
664 AND organization_id = C_organization_id;
665
666 BEGIN
667
668 /*
669 ** Retrieve org level parameters to see if we should check planning
670 ** group and cost group references
671 */
672
673 OPEN Allow_XPrj_Issue (X_organization_id);
674 FETCH Allow_XPrj_Issue INTO L_allow_xprj_issue;
675 if (Allow_XPrj_Issue%notfound) then
676 L_allow_xprj_issue := 'N';
677 end if;
678 CLOSE Allow_XPrj_Issue;
679
680 /*
684 (X_loc_project is not null))
681 ** Vanilla case - match project and task directly
682 */
683 if ((X_loc_project <> X_txn_project) and
685 or
686 /* Bug 2502968
687 ** Task is required (and must match) if transaction task is present even
688 ** in Project Controlled org, unless cross project is allowed
689 */
690 ((nvl(X_loc_task, nvl(X_txn_task,-1)) <> nvl(X_txn_task,-1)) and
691 (X_proj_ctrl_level = 1))
692 or
693 ((X_loc_task <> X_txn_task) and
694 (X_loc_task is not null) and
695 (X_proj_ctrl_level = 2)) then
696 -- do nothing
697 null;
698 else
699
700 /* Bug 11811757
701 ** If cross project issue is not allowed and item is hard-pegged and
702 ** locator is common, then return false
703 */
704
705 IF (X_item_id IS NOT NULL) THEN
706
707 OPEN C_Peg_Flag (X_organization_id, X_item_id);
708 FETCH C_Peg_Flag INTO L_peg_flag;
709 CLOSE C_Peg_Flag;
710
711 IF ((L_allow_xprj_issue = 'N') AND
712 (L_peg_flag IN ('I', 'X')) AND
713 (X_loc_project IS NULL)) THEN
714 FND_MESSAGE.SET_NAME('PJM', 'LOC-PRJ REF REQUIRED HARDPEG');
715 RETURN FALSE;
716 END IF;
717
718 END IF;
719
720 return TRUE;
721 end if;
722
723 if ( L_allow_xprj_issue = 'Y' ) then
724
725 /* Bug 2502968
726 ** If cross project issue is allowed and locator and transaction project
727 ** is the same, then the reference is valid (by the time it gets here,
728 ** the only such scenario is a task mismatch)
729 */
730 if ( X_txn_project = X_loc_project ) then
731 return TRUE;
732 end if;
733
734 OPEN C_Cost_Method (X_organization_id);
735 FETCH C_Cost_Method INTO L_cost_method;
736 if ( C_Cost_Method%notfound) then
737 L_cost_method := 0;
738 end if;
739 CLOSE C_Cost_Method;
740
741 OPEN C_Proj_Param (X_organization_id, X_txn_project);
742 FETCH C_Proj_Param INTO L_txn_plan_grp, L_txn_cost_grp;
743 if (C_Proj_Param%notfound) then
744 L_txn_plan_grp := NULL;
745 L_txn_cost_grp := NULL;
746 end if;
747 CLOSE C_Proj_Param;
748
749 OPEN C_Proj_Param (X_organization_id, X_loc_project);
750 FETCH C_Proj_Param INTO L_loc_plan_grp, L_loc_cost_grp;
751 if (C_Proj_Param%notfound) then
752 L_loc_plan_grp := NULL;
753 L_loc_cost_grp := NULL;
754 end if;
755 CLOSE C_Proj_Param;
756
757 /*
758 ** If either of the Planning Group is NULL, then match is
759 ** FALSE
760 */
761 /*Bug 8355068: If both planning group are null then this condition would fail.
762 If both planning group are null then we should not return false
763 and added one more condition to make sure that at least one of
764 of the planning group is not null.*/
765 if ( (L_txn_plan_grp is null or L_loc_plan_grp is null)
766 AND (L_txn_plan_grp is not null OR L_loc_plan_grp is not null) ) then
767 l_txn_prj_ref := Get_Project_References(X_txn_project, NULL);
768 l_loc_prj_ref := Get_Project_References(X_loc_project, NULL);
769 FND_MESSAGE.SET_NAME('PJM', 'LOC-PLAN GROUP MISMATCH');
770 FND_MESSAGE.SET_TOKEN('TXN_PRJ_REF',l_txn_prj_ref,FALSE);
771 FND_MESSAGE.SET_TOKEN('LOC_PRJ_REF',l_loc_prj_ref,FALSE);
772 return FALSE;
773 end if;
774
775 /*
776 ** If the Planning Groups do not match, then match is FALSE
777 */
778 if ( L_txn_plan_grp <> L_loc_plan_grp ) then
779 l_txn_prj_ref := Get_Project_References(X_txn_project, NULL);
780 l_loc_prj_ref := Get_Project_References(X_loc_project, NULL);
781 FND_MESSAGE.SET_NAME('PJM', 'LOC-PLAN GROUP MISMATCH');
782 FND_MESSAGE.SET_TOKEN('TXN_PRJ_REF',l_txn_prj_ref,FALSE);
783 FND_MESSAGE.SET_TOKEN('LOC_PRJ_REF',l_loc_prj_ref,FALSE);
784 return FALSE;
785 end if;
786
787 /*
788 ** If the Cost Groups do not match, then match is FALSE
789 **
790 ** This check is not applicable for standard costing
791 **
792 ** As of Family Pack H, this check has been completely removed
793 ** as the Cost Processor can handle cost group transfer in
794 ** WIP issues
795 **
796 if ( L_cost_method <> 1 ) then
797 if ( L_txn_cost_grp <> L_loc_cost_grp ) then
798 l_txn_prj_ref := Get_Project_References(X_txn_project, NULL);
799 l_loc_prj_ref := Get_Project_References(X_loc_project, NULL);
800 FND_MESSAGE.SET_NAME('PJM', 'LOC-COST GROUP MISMATCH');
801 FND_MESSAGE.SET_TOKEN('TXN_PRJ_REF',l_txn_prj_ref,FALSE);
802 FND_MESSAGE.SET_TOKEN('LOC_PRJ_REF',l_loc_prj_ref,FALSE);
803 return FALSE;
804 end if;
805 end if;
806 */
807 return TRUE;
808
809 else /* L_allow_xprj_issue = 'N' */
810
811 l_txn_prj_ref := Get_Project_References(X_txn_project, X_txn_task);
812 l_loc_prj_ref := Get_Project_References(X_loc_project, X_loc_task);
813 FND_MESSAGE.SET_NAME('PJM', 'LOC-PRJ REF MISMATCH');
814 FND_MESSAGE.SET_TOKEN('TXN_PRJ_REF',l_txn_prj_ref,FALSE);
815 FND_MESSAGE.SET_TOKEN('LOC_PRJ_REF',l_loc_prj_ref,FALSE);
816 return FALSE;
817 end if;
818
819 END match_proj_by_plan_grp;
820
821 -- ---------------------------------------------------------------------
822 -- Public Functions and Procedures
823 -- ---------------------------------------------------------------------
824
828 -- appropriate project and task references based on the
825 -- Name : Check_Project_References
826 -- Pre-reqs : None.
827 -- Function : This function checks whether a given locator has
829 -- current organization. Also if the validation mode is
830 -- specific, the function checks whether the parameters
831 -- project_id and task_id have appropriate values.
832 --
833 --
834 -- Parameters :
835 -- IN : p_organization_id IN NUMBER
836 -- : p_locator_id IN NUMBER
837 -- : p_validation_mode IN VARCHAR2
838 -- : p_required_flag IN VARCHAR2
839 -- : p_project_id IN NUMBER
840 -- : p_task_id IN NUMBER
841 --
842 -- Returns : TRUE if locator passes the validation for the
843 -- specific mode
844 -- FALSE if locator fails the validation for the
845 -- specific mode
846 --
847 -- Version : 1.0 5/27/97 S Bala
848 -- Notes :
849 --
850 FUNCTION Check_Project_References(
851 p_organization_id IN NUMBER,
852 p_locator_id IN NUMBER,
853 p_validation_mode IN VARCHAR2,
854 p_required_flag IN VARCHAR2,
855 p_project_id IN NUMBER,
856 p_task_id IN NUMBER,
857 p_item_id IN NUMBER
858 ) RETURN BOOLEAN IS
859
860 CURSOR C_Org_Controls(org_id NUMBER) IS
861 SELECT project_reference_enabled,
862 project_control_level,
863 organization_code
864 from mtl_parameters
865 where organization_id = org_id;
866
867 CURSOR C_Loc_Attrib(loc_id NUMBER, org_id NUMBER) IS
868 SELECT project_id,
869 task_id
870 FROM mtl_item_locations
871 WHERE inventory_location_id = loc_id
872 AND organization_id = org_id;
873
874 CURSOR C_Proj (p_id NUMBER) IS
875 SELECT project_id
876 FROM pjm_projects_v
877 WHERE project_id = p_id;
878
879 CURSOR C_Task (t_id NUMBER) IS
880 SELECT task_id
881 FROM pjm_tasks_v
882 WHERE task_id = t_id;
883
884 l_org_code VARCHAR2(3);
885 l_proj_ref_enabled NUMBER;
886 l_proj_control_level NUMBER;
887 l_loc_project_id NUMBER;
888 l_loc_task_id NUMBER;
889 l_txn_prj_ref VARCHAR2(60);
890 l_loc_prj_ref VARCHAR2(60);
891 l_dummy NUMBER;
892
893 BEGIN
894
895 /* If no locator is passed in , return TRUE */
896
897 if (p_locator_id is NULL) then
898 return TRUE;
899 end if;
900
901 /* If the locator argumnet passed is -1, it refers to a dynamically
902 ** created locator. In this case return TRUE, validation will be done
903 ** later for the locator
904 */
905
906 if (p_locator_id = -1) then
907 return TRUE;
908 end if;
909
910 /* Retrieve project control parameters for the organization
911 ** passed in
912 */
913
914 OPEN C_ORG_CONTROLS(p_organization_id);
915 FETCH C_ORG_CONTROLS INTO
916 l_proj_ref_enabled,
917 l_proj_control_level,
918 l_org_code;
919
920 if (C_ORG_CONTROLS%NOTFOUND) then
921 CLOSE C_ORG_CONTROLS;
922 RAISE NO_DATA_FOUND;
923 return FALSE;
924 end if;
925
926 CLOSE C_ORG_CONTROLS;
927
928 /* Retrieve locator project and task references */
929
930 OPEN C_LOC_ATTRIB(p_locator_id, p_organization_id);
931 FETCH C_LOC_ATTRIB INTO
932 l_loc_project_id,
933 l_loc_task_id;
934
935 if (C_LOC_ATTRIB%NOTFOUND) then
936 CLOSE C_LOC_ATTRIB;
937 RAISE NO_DATA_FOUND;
938 return FALSE;
939 end if;
940
941 CLOSE C_LOC_ATTRIB;
942
943 /* Check to see if project is chargeable; error out if not */
944
945 if ( l_loc_project_id is not null ) then
946
947 OPEN C_Proj ( l_loc_project_id );
948 FETCH C_Proj INTO l_dummy;
949
950 if ( C_Proj%NOTFOUND ) then
951 CLOSE C_Proj;
952 FND_MESSAGE.SET_NAME('PJM', 'LOC-PROJ NOT CHARGEABLE');
953 FND_MESSAGE.SET_TOKEN('PROJ' , Get_Project_References( l_loc_project_id , null ));
954 return(FALSE);
955 end if;
956
957 CLOSE C_Proj;
958
959 /* Check to see if project is enabled in current org */
960
961 if ( pjm_project.val_proj_idtonum( l_loc_project_id
962 , p_organization_id ) is null ) then
963
964 FND_MESSAGE.SET_NAME('PJM' , 'GEN-INVALID PROJ FOR ORG');
965 FND_MESSAGE.SET_TOKEN('PROJECT' , Get_Project_References( l_loc_project_id , null ));
966 FND_MESSAGE.SET_TOKEN('ORG' , l_org_code);
967 return(FALSE);
968
969 end if;
970
971 end if;
972
973 /* Check to see if task is chargeable; error out if not */
974
975 if ( l_loc_task_id is not null ) then
976
977 OPEN C_Task ( l_loc_task_id );
978 FETCH C_Task INTO l_dummy;
979
980 if ( C_Task%NOTFOUND ) then
981 CLOSE C_Task;
982 FND_MESSAGE.SET_NAME('PJM', 'LOC-TASK NOT CHARGEABLE');
986
983 FND_MESSAGE.SET_TOKEN('TASK' , Get_Project_References( l_loc_project_id , l_loc_task_id ));
984 return(FALSE);
985 end if;
987 CLOSE C_Task;
988
989 end if;
990
991 if (not get_physical_location(p_organization_id, p_locator_id)) then
992 return (FALSE);
993 end if;
994
995 /* Validations */
996
997 /* 1. If the organization is not project reference enabled, then
998 ** the locator cannot have project references.
999 */
1000
1001 if (l_proj_ref_enabled <> 1) then
1002 if ((l_loc_project_id is not null) or
1003 (l_loc_task_id is not null)) then
1004 FND_MESSAGE.SET_NAME('PJM', 'LOC-ORG NOT PRJ ENABLED');
1005 return (FALSE);
1006 end if;
1007 else
1008 /* If the project_id argument is NULL, then the locator
1009 ** cannot have project reference */
1010
1011 if ((p_project_id is null) and (p_validation_mode = 'SPECIFIC')) then
1012 if (l_loc_project_id is null) then
1013 return (TRUE);
1014 else
1015 FND_MESSAGE.SET_NAME('PJM', 'LOC-PRJ REF NOT ALLOWED');
1016 return (FALSE);
1017 end if;
1018 end if;
1019
1020 /* Control Level Validations */
1021
1022 /* If control level is project, and
1023 ** If Required Flag is set to 'Y', then
1024 ** verify project is set to not null */
1025
1026 if (l_proj_control_level = 1) then
1027
1028 if (p_required_flag = 'Y') then
1029 if (l_loc_project_id is null) then
1030 FND_MESSAGE.SET_NAME('PJM', 'LOC-PRJ REF REQUIRED');
1031 return (FALSE);
1032 end if;
1033 end if;
1034
1035 elsif (l_proj_control_level = 2) then
1036
1037 /* If control level is set to task, ensure both project and
1038 ** task are null or both are not null. if Required Flag is
1039 ** set to 'Y', verify both project and task are set to
1040 ** not null.
1041 */
1042
1043 if (((l_loc_project_id is not null) and (l_loc_task_id is null)) or
1044 ((l_loc_project_id is null) and (l_loc_task_id is not null))) then
1045
1046 FND_MESSAGE.SET_NAME('PJM', 'LOC-INVALID PRJ REF');
1047 return FALSE;
1048 end if;
1049
1050 if (p_required_flag = 'Y') then
1051 if ((l_loc_project_id is NULL) or (l_loc_task_id is NULL)) then
1052 FND_MESSAGE.SET_NAME('PJM', 'LOC-PRJ REF REQUIRED');
1053 return (FALSE);
1054 end if;
1055 end if;
1056 end if; /* l_proj_control_level = 2 */
1057
1058
1059 /* Project,Task Parameter Validations */
1060
1061 if (p_validation_mode = 'SPECIFIC') then
1062
1063 if (p_required_flag = 'Y') then
1064 /* If required flag is 'Y', then project references in the
1065 ** parameters must exactly match the project references for the
1066 ** locator
1067 */
1068 if (
1069 (nvl(l_loc_project_id, -1) <> nvl(p_project_id, -1))
1070 or
1071 /* Bug 2502968
1072 ** Task is required (and must match) if transaction task is present even
1073 ** in Project Controlled org
1074 */
1075 ((nvl(l_loc_task_id, -1) <> nvl(p_task_id, nvl(l_loc_task_id, -1))) and
1076 (l_proj_control_level = 1))
1077 or
1078 ((nvl(l_loc_task_id, -1) <> nvl(p_task_id, -1)) and
1079 (l_proj_control_level = 2))
1080 ) then
1081 l_txn_prj_ref := Get_Project_References(p_project_id, p_task_id);
1082 l_loc_prj_ref := Get_Project_References(l_loc_project_id, l_loc_task_id);
1083 FND_MESSAGE.SET_NAME('PJM', 'LOC-PRJ REF MISMATCH');
1084 FND_MESSAGE.SET_TOKEN('TXN_PRJ_REF',l_txn_prj_ref,FALSE);
1085 FND_MESSAGE.SET_TOKEN('LOC_PRJ_REF',l_loc_prj_ref,FALSE);
1086 return (FALSE);
1087 end if;
1088
1089 elsif (p_required_flag = 'N') then
1090
1091 /* If required flag is 'N', then call the function
1092 ** match_proj_by_plan_grp to determine if the locator
1093 ** passes validation
1094 */
1095 if ( not match_proj_by_plan_grp( p_organization_id
1096 , l_proj_control_level
1097 , p_project_id
1098 , p_task_id
1099 , l_loc_project_id
1100 , l_loc_task_id
1101 , p_item_id ) ) then
1102 return (FALSE);
1103 end if;
1104 end if; /* p_required_flag */
1105 end if; /* p_validation_mode */
1106 end if;
1107 return (TRUE);
1108
1109 END Check_Project_References;
1110
1111 FUNCTION Check_Project_References(
1112 p_organization_id IN NUMBER,
1113 p_locator_id IN NUMBER,
1114 p_validation_mode IN VARCHAR2,
1115 p_required_flag IN VARCHAR2,
1116 p_project_id IN NUMBER,
1120 return Check_Project_References(
1117 p_task_id IN NUMBER
1118 ) RETURN BOOLEAN IS
1119 BEGIN
1121 p_organization_id ,
1122 p_locator_id ,
1123 p_validation_mode ,
1124 p_required_flag ,
1125 p_project_id ,
1126 p_task_id ,
1127 NULL );
1128 END Check_Project_References;
1129
1130
1131 -- Name : Check_ItemLocatorControl
1132 -- Pre-reqs : None.
1133 -- Function : This function checks the locator control
1134 -- at three different levels: Org, Sub, Item.
1135 -- If parameter p_hardpeg_only is 1, it also
1136 -- checks if it is a hard-peg item.
1137 -- If the item has a restricted list of subinventory
1138 -- or locators, this function also checks if
1139 -- the parameter p_sub, p_loc are within its list.
1140 --
1141 -- Parameters :
1142 -- IN : p_organization_id IN NUMBER
1143 -- : p_sub IN VARCHAR2
1144 -- : p_loc IN NUMBER
1145 -- : p_item_id IN NUMBER
1146 -- : p_hardpeg_only IN NUMBER
1147 --
1148 -- Returns : True if all test are passed, else return False
1149 --
1150 -- Version : 1.0 10/02/97 W. Pacifia Chiang
1151 -- Notes :
1152 --
1153 FUNCTION Check_ItemLocatorControl (
1154 p_organization_id IN NUMBER,
1155 p_sub IN VARCHAR2,
1156 p_loc IN NUMBER,
1157 p_item_id IN NUMBER,
1158 p_hardpeg_only IN NUMBER
1159 ) RETURN BOOLEAN IS
1160
1161
1162 CURSOR C_ITEM_SUB IS
1163 SELECT 1
1164 FROM MTL_ITEM_SUB_INVENTORIES
1165 WHERE inventory_item_id = p_item_id
1166 AND organization_id = p_organization_id
1167 AND secondary_inventory = p_sub;
1168
1169 CURSOR C_ITEM_LOC IS
1170 SELECT 1
1171 FROM MTL_SECONDARY_LOCATORS
1172 WHERE inventory_item_id = p_item_id
1173 AND organization_id = p_organization_id
1174 AND secondary_locator = p_loc;
1175
1176
1177 dummy NUMBER := 0;
1178 l_org_loc_control NUMBER := 0;
1179 l_sub_loc_control NUMBER := 0;
1180 l_item_loc_control NUMBER := 0;
1181 l_item_loc_restrict NUMBER := 0;
1182 l_item_sub_restrict NUMBER := 0;
1183 l_peg_flag VARCHAR2(1) := NULL;
1184 l_loc_id NUMBER :=0;
1185
1186 BEGIN
1187
1188 BEGIN
1189 SELECT mtl.end_assembly_pegging_flag,
1190 mtl.location_control_code,
1191 NVL(mtl.restrict_locators_code,2),
1192 NVL(mtl.restrict_subinventories_code,2),
1193 mp.stock_locator_control_code
1194 INTO l_peg_flag,
1195 l_item_loc_control,
1196 l_item_loc_restrict,
1197 l_item_sub_restrict,
1198 l_org_loc_control
1199 FROM mtl_system_items mtl,
1200 mtl_parameters mp
1201 WHERE mtl.inventory_item_id = p_item_id
1202 AND mtl.organization_id = p_organization_id
1203 AND mtl.organization_id = mp.organization_id;
1204
1205 EXCEPTION
1206 WHEN OTHERS THEN
1207 return(FALSE);
1208 RAISE;
1209 END;
1210
1211 BEGIN
1212 SELECT sub.locator_type
1213 INTO l_sub_loc_control
1214 FROM mtl_secondary_inventories sub
1215 WHERE sub.secondary_inventory_name = p_sub
1216 AND sub.organization_id = p_organization_id;
1217
1218 EXCEPTION
1219 WHEN OTHERS THEN
1220 return(FALSE);
1221 RAISE;
1222 END;
1223
1224
1225 /* Only create project locator when
1226 the locator control allows dynamic insert
1227 without any restriction on locator or subinventory.
1228
1229 Note that there are three levels of locator
1230 control: Org, Sub, Item
1231 */
1232
1233 IF (l_org_loc_control = 3
1234 or
1235 (l_org_loc_control = 4 and
1236 l_sub_loc_control = 3)
1237 or
1238 (l_org_loc_control = 4 and
1239 l_sub_loc_control = 5 and
1240 l_item_loc_control = 3)) THEN
1241
1242
1243 /* If the item has a restricted list of subinventory,
1244 then check if the input subinventory is within the resticted list */
1245
1246 IF (l_item_sub_restrict = 1) THEN
1247 OPEN C_ITEM_SUB;
1248 FETCH C_ITEM_SUB INTO dummy;
1249 IF (C_ITEM_SUB%NOTFOUND) THEN
1250 CLOSE C_ITEM_SUB;
1251 return(FALSE);
1252 ELSE
1253 CLOSE C_ITEM_SUB;
1254 END IF;
1255
1256 /* If the item has a restricted list of locators,
1257 then check if the input locator is within the resticted list. */
1258
1259 IF (l_item_loc_restrict = 1) THEN
1260 OPEN C_ITEM_LOC;
1261 FETCH C_ITEM_LOC INTO dummy;
1262 IF (C_ITEM_LOC%NOTFOUND) THEN
1263 CLOSE C_ITEM_LOC;
1264 return(FALSE);
1265 ELSE
1266 CLOSE C_ITEM_LOC;
1267 END IF;
1268 END IF;
1269 END IF;
1270
1271
1275 ELSE
1272 IF (p_hardpeg_only = 1) THEN
1273 IF (l_peg_flag = 'I' or l_peg_flag = 'X') THEN
1274 return(TRUE);
1276 return (FALSE);
1277 END IF;
1278 ELSE
1279 return (TRUE);
1280 END IF;
1281 ELSE
1282 return (FALSE);
1283 END IF;
1284 END Check_ItemLocatorControl;
1285
1286
1287 -- Name : Get_DefaultProjectLocator
1288 -- Pre-reqs : None.
1289 -- Function : This procedure matches a project locator based on
1290 -- the parameters organization_id, locator_id, project_id
1291 -- and task_id. If no locator is found, a new locator is
1292 -- created with project reference in segment19 and
1293 -- task reference in segment20 into mtl_item_locations
1294 -- using same physical attributes (from segment1 upto
1295 -- segment18 depending locator flexfield setup).
1296 --
1297 --
1298 -- The assumption for this procedure is that
1299 -- the supply subinventory/locator value of those
1300 -- wip_requirement_operations should be common locators.
1301 --
1302 -- Parameters :
1303 -- IN : p_organization_id IN NUMBER
1304 -- : p_locator_id IN NUMBER
1305 -- : p_project_id IN NUMBER
1306 -- : p_task_id IN NUMBER
1307 -- : p_project_locator_id IN OUT NUMBER
1308 --
1309 -- RETURNS : NA
1310 --
1311 -- Version : 1.0 5/27/97 W. Pacifia Chiang
1312 -- Notes :
1313 --
1314 PROCEDURE Get_DefaultProjectLocator(
1315 p_organization_id IN NUMBER,
1316 p_locator_id IN NUMBER,
1317 p_project_id IN NUMBER,
1318 p_task_id IN NUMBER,
1319 p_project_locator_id IN OUT NOCOPY NUMBER
1320 ) IS
1321 CURSOR C3(v_loc_id NUMBER, v_org_id NUMBER,
1322 v_project_id NUMBER, v_task_id NUMBER)IS
1323 SELECT rowid
1324 from mtl_item_locations
1325 where inventory_location_id = v_loc_id
1326 and organization_id = v_org_id
1327 and nvl(project_id, -1) = nvl(v_project_id, -1)
1328 and nvl(task_id, -1) = nvl(v_task_id, -1);
1329
1330
1331 CURSOR C4 (v_loc_id NUMBER, v_org_id NUMBER) IS
1332 SELECT physical_location_id
1333 FROM mtl_item_locations
1334 WHERE inventory_location_id = v_loc_id
1335 and organization_id = v_org_id;
1336
1337 l_rowid VARCHAR2(400);
1338 l_subinv_code VARCHAR2(10);
1339 l_phy_loc_id NUMBER;
1340
1341 BEGIN
1342
1343 /* Comment this line because some callers to this API use the same variable
1344 * for p_locator_id and p_project_locator_id and this line will set them
1345 * both to null. Please refer to bug 8848853 for more details.
1346 */
1347 --p_project_locator_id := null;
1348
1349 /* If there is no input locator, there is no need to do anything
1350 */
1351 if (p_locator_id is null) then
1352 return;
1353 end if;
1354
1355 /* Bug 741594
1356 ** June 9, 1999
1357 **
1358 ** Check whether the locator itself is a valid locator.
1359 ** If yes proceed to other checks, if no error out.
1360 */
1361 if (not get_physical_location(p_organization_id, p_locator_id)) then
1362 fnd_message.set_name('INV','INV_INT_LOCCODE');
1363 raise_application_error(-20001, fnd_message.get);
1364 end if;
1365
1366 OPEN C4(p_locator_id, p_organization_id);
1367 FETCH C4 INTO l_phy_loc_id;
1368 CLOSE C4;
1369
1370 /* If project_id is null, we can simply return the physical
1371 ** locator
1372 */
1373 if (p_project_id is null) then
1374 p_project_locator_id := l_phy_loc_id;
1375 return;
1376 end if;
1377
1378 /* Check whether the passed arguments point to a
1379 ** valid existing locator. If yes return the locator
1380 ** id, if no proceed to other checks
1381 */
1382 OPEN C3(p_locator_id, p_organization_id, p_project_id, p_task_id);
1383 FETCH C3 INTO l_rowid;
1384
1385 if (C3%NOTFOUND) then
1386 close C3;
1387 else
1388 p_project_locator_id := p_locator_id;
1389 close C3;
1390 return;
1391 end if;
1392
1393 /* Check if another locator exist with matching flexfield segments
1394 ** as selected above for the organization_id, project_id and task_id
1395 ** passed in. Dynamic SQL is employed here for performance.
1396 */
1397
1398 p_project_locator_id := map_locator( p_organization_id
1399 , p_locator_id
1400 , p_project_id
1401 , p_task_id
1402 , TRUE );
1403
1404 return;
1405
1406 END Get_DefaultProjectLocator;
1407
1408
1409 /*==========================================================================
1410 -- Name : Get_Job_ProjectSupply
1411 -- Pre-reqs : None.
1412 -- Function : This procedure gets supply locators
1413 -- for materials that are backflushed
1414 -- for project jobs.
1415 --
1416 -- The assumption for this procedure is that
1417 -- the supply subinventory/locator value of those
1418 -- wip_requirement_operations should be common locators.
1419 --
1420 -- Parameters :
1421 -- IN : p_organization_id IN NUMBER
1422 -- : p_wip_entity_id IN NUMBER
1423 -- : p_project_id IN NUMBER
1424 -- : p_task_id IN NUMBER
1425 -- : p_success OUT NUMBER
1426 --
1427 -- RETURNS : NA
1428 --
1429 -- Version : 1.0 4/29/97 W. Pacifia Chiang
1430 -- Notes :
1431 +========================================================================== */
1432
1433 PROCEDURE Get_Job_ProjectSupply (
1434 p_organization_id IN NUMBER,
1435 p_wip_entity_id IN NUMBER,
1436 p_project_id IN NUMBER,
1437 p_task_id IN NUMBER,
1438 p_success OUT NOCOPY NUMBER
1439 ) IS
1440
1441 -- This procedure gets backflush material supply
1442 -- and supply locator for project job.
1443
1444 L_item_id NUMBER := 0;
1445 L_supply_type NUMBER := 0;
1446 L_supply_loc_id NUMBER := 0;
1447 L_proj_ref_enabled NUMBER := 2;
1448 l_org_loc_control NUMBER := 0;
1449
1450 L_supply_sub VARCHAR2(10) := NULL;
1451 L_ROW_ID ROWID;
1452 l_success BOOLEAN := TRUE;
1453 locator_exe exception;
1454
1455 CURSOR proj_job_curr (V_org_id NUMBER, V_wip_entity_id NUMBER) IS
1456 SELECT rop.inventory_item_id,
1457 rop.supply_subinventory,
1458 rop.supply_locator_id,
1459 rop.wip_supply_type, rop.rowid
1460 FROM wip_requirement_operations rop
1461 WHERE rop.organization_id = V_org_id
1462 AND rop.wip_entity_id = V_wip_entity_id
1463 AND rop.supply_locator_id is not null
1464 ORDER BY rop.operation_seq_num
1465 FOR UPDATE;
1466
1467 begin
1468 p_success := 1;
1469
1470 if (p_organization_id is not null) then
1471 begin
1472 SELECT NVL(mp.project_reference_enabled, 2),
1473 mp.stock_locator_control_code
1474 INTO L_proj_ref_enabled,
1475 l_org_loc_control
1476 FROM mtl_parameters mp
1477 WHERE mp.organization_id = p_organization_id;
1478
1479 exception
1480 when others then
1481 p_success := 0;
1482 raise;
1483 end;
1484 end if;
1485
1486 if (l_proj_ref_enabled = 1) then
1487
1488 -- only process if the org parameter has project reference enabled
1489 --
1490 OPEN proj_job_curr (p_organization_id, p_wip_entity_id) ;
1491 loop
1492 L_item_id := 0;
1493 L_supply_loc_id := 0;
1494 L_supply_type := 0;
1495
1496 FETCH proj_job_curr INTO
1497 L_item_id,
1498 L_supply_sub,
1499 L_supply_loc_id,
1500 L_supply_type,
1501 L_row_id;
1502 EXIT WHEN proj_job_curr%NOTFOUND;
1503
1504 -- The original supply locator specified for this project jobs
1505 -- from users should be a common locator. This is enforced
1506 -- by user procedure.
1507
1508 l_success := Get_Component_ProjectSupply(
1509 p_organization_id,
1510 p_project_id,
1511 p_task_id,
1512 p_wip_entity_id,
1513 L_supply_sub,
1514 L_supply_loc_id,
1515 L_item_id,
1516 l_org_loc_control);
1517 if l_success = false then
1518 p_success := 0;
1519 raise locator_exe;
1520 else
1521 if L_supply_loc_id <> 0 then
1522 begin
1523 UPDATE wip_requirement_operations rop
1524 SET rop.supply_locator_id = L_supply_loc_id
1525 WHERE rop.rowid = L_row_id;
1526 exception
1527 when others then
1528 p_success := 0;
1529 raise locator_exe;
1530 end;
1531 end if;
1532 end if;
1533 end loop;
1534 close proj_job_curr;
1535 end if;
1536 end Get_Job_ProjectSupply;
1537
1538
1539 -- Name : Get_Flow_ProjectSupply
1540 -- Pre-reqs : None.
1541 -- Function : This procedure gets supply locators
1542 -- for materials that are backflushed
1543 -- for project related flow schedules.
1544 --
1545 -- The assumption for this procedure is that
1546 -- the supply subinventory/locator value of those
1547 -- in mtl_transactions_interface should be common
1548 -- locators.
1549 --
1550 -- Parameters :
1551 -- IN : p_organization_id IN NUMBER
1552 -- : p_wip_entity_id IN NUMBER
1553 -- : p_project_id IN NUMBER
1554 -- : p_task_id IN NUMBER
1555 -- : p_success OUT NUMBER
1556 --
1557 -- RETURNS : NA
1558 --
1559 -- Version : 1.01 8/11/97 Daniel Soosai
1560 -- Notes :
1561 --
1562 PROCEDURE Get_Flow_ProjectSupply (
1563 p_organization_id IN NUMBER,
1564 p_wip_entity_id IN NUMBER,
1565 p_project_id IN NUMBER,
1566 p_task_id IN NUMBER,
1567 p_parent_id IN NUMBER,
1568 p_success OUT NOCOPY NUMBER
1569 ) IS
1570
1571 l_item_id NUMBER := 0;
1572 l_supply_type NUMBER := 0;
1573 l_supply_loc_id NUMBER := 0;
1574 l_proj_ref_enabled NUMBER := 2;
1575 l_org_loc_control NUMBER := 0;
1576 l_success BOOLEAN := TRUE;
1577 l_supply_sub VARCHAR2(10) := NULL;
1578 l_ROW_ID ROWID;
1579 locator_exe EXCEPTION;
1580
1581 CURSOR proj_flow_curr (V_org_id NUMBER,
1582 V_wip_entity_id NUMBER,
1583 V_parent_id NUMBER) IS
1584 SELECT mti.inventory_item_id,
1585 mti.subinventory_code,
1586 mti.locator_id,
1587 mti.rowid
1588 FROM mtl_transactions_interface mti
1589 WHERE mti.organization_id = V_org_id
1590 AND ( V_wip_entity_id is null
1591 OR mti.transaction_source_id = V_wip_entity_id )
1592 AND mti.locator_id is not null
1593 AND mti.parent_id = V_parent_id
1594 AND mti.transaction_action_id in (1, 27, 33, 34)
1595 AND mti.transaction_source_type_id = 5
1596 AND mti.flow_schedule = 'Y'
1597 ORDER BY mti.operation_seq_num
1598 FOR UPDATE;
1599
1600 begin
1601 p_success := 1;
1602
1603 IF (p_organization_id IS NOT NULL) THEN
1604 BEGIN
1605 SELECT NVL(mp.project_reference_enabled, 2),
1606 mp.stock_locator_control_code
1607 INTO L_proj_ref_enabled,
1608 l_org_loc_control
1609 FROM mtl_parameters mp
1610 WHERE mp.organization_id = p_organization_id;
1611
1612 EXCEPTION
1613 WHEN OTHERS THEN
1614 p_success := 0;
1615 RAISE;
1616 END;
1617 END IF;
1618
1619 IF l_proj_ref_enabled = 1 AND p_project_id IS NOT NULL THEN
1620 -- only process if the org parameter has project reference enabled
1621 -- and the project id is not null (only project jobs are processed)
1622
1623 OPEN proj_flow_curr (p_organization_id, p_wip_entity_id, p_parent_id) ;
1624 loop
1625 l_item_id := 0;
1626 l_supply_loc_id := 0;
1627 l_supply_type := 0;
1628
1629 FETCH proj_flow_curr INTO
1630 L_item_id,
1631 L_supply_sub,
1632 L_supply_loc_id,
1633 L_row_id;
1634 EXIT WHEN proj_flow_curr%NOTFOUND;
1635
1636
1637 /* The original supply locator specified for this project jobs
1638 from users should be a common locator. This is enforced
1639 by user procedure.
1640 */
1641
1642 l_success := Get_Component_ProjectSupply(
1643 p_organization_id,
1644 p_project_id,
1645 p_task_id,
1646 p_wip_entity_id,
1647 L_supply_sub,
1648 L_supply_loc_id,
1649 L_item_id,
1650 l_org_loc_control);
1651
1652 if l_success = false then
1653 p_success := 0;
1654 raise locator_exe;
1655 else
1656 if l_supply_loc_id <> 0 then
1657 begin
1658 update mtl_transactions_interface
1659 set(locator_id, project_id, task_id) =
1660 (select inventory_location_id, project_id, task_id
1661 from mtl_item_locations
1662 where inventory_location_id = l_supply_loc_id
1663 and organization_id = p_organization_id)
1664 where rowid = l_row_id;
1665
1666 exception
1667 when others then
1668 p_success := 0;
1669 raise locator_exe;
1670 end;
1671 end if;
1672 end if;
1673 end loop;
1674 close proj_flow_curr;
1675 end if;
1676 end Get_Flow_ProjectSupply;
1677
1678
1679 -- ---------------------------------------------------------------------
1680 -- Functions and Procedures used for locator segment defaulting
1681 -- ---------------------------------------------------------------------
1682 PROCEDURE Set_Segment_Default (
1683 p_project_id IN NUMBER,
1684 p_task_id IN NUMBER
1685 ) IS
1686
1687 begin
1688 if (p_project_id is not null) then
1689 G_project_number := pjm_project.all_proj_idtonum( p_project_id );
1690 if (p_task_id is not null) then
1691 G_task_number := pjm_project.all_task_idtonum( p_task_id );
1692 --BUG Fix 4590465
1693 else
1694 G_task_number := NULL;
1695
1696 -- END BUG 4590465
1697 end if;
1698 else
1699 G_project_number := NULL;
1700 G_task_number := NULL;
1701 end if;
1702 end Set_Segment_Default;
1703
1704 PROCEDURE Put_OrgProfile(name in varchar2, val in varchar2) is
1705 begin
1706 FND_PROFILE.put(name, val);
1707 end Put_OrgProfile;
1708
1709 PROCEDURE Get_OrgProfile(name in varchar2, val out nocopy varchar2) is
1710 begin
1711 FND_PROFILE.get(name, val);
1712 end Get_OrgProfile;
1713
1714 FUNCTION Proj_Seg_Default
1715 return VARCHAR2 IS
1716 begin
1717 return(G_Project_Number);
1718 end Proj_Seg_Default;
1719
1720 FUNCTION Task_Seg_Default
1721 return VARCHAR2 IS
1722 begin
1723 return(G_Task_Number);
1724 end Task_Seg_Default;
1725
1726 END PJM_PROJECT_LOCATOR;
[Home] [Help]