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