1 PACKAGE BODY PJM_COMMITMENT_UTILS AS
2 /* $Header: PJMCMTUB.pls 120.1 2006/07/10 23:13:51 yliou noship $ */
3
4 FUNCTION REQ_Type
5 ( X_Org_Id IN NUMBER
6 , X_Subtype IN VARCHAR2
7 ) return VARCHAR2 IS
8
9 L_Type_Name VARCHAR2(80);
10
11 BEGIN
12
13 SELECT T.TYPE_NAME into L_Type_Name
14 from PO_DOCUMENT_TYPES_ALL_TL T
15 WHERE T.LANGUAGE = userenv('LANG')
16 and T.DOCUMENT_TYPE_CODE = 'REQUISITION'
17 AND T.DOCUMENT_SUBTYPE = X_Subtype
18 and NVL(T.org_id, -99) = NVL(X_Org_Id, -99);
19
20 RETURN ( L_Type_Name );
21
22 EXCEPTION
23 WHEN OTHERS THEN
24 RETURN ( NULL );
25
26 END REQ_Type;
27
28
29 FUNCTION BOM_RESOURCE
30 ( X_resource_id IN NUMBER
31 ) return VARCHAR2 IS
32
33 L_Resource_Code VARCHAR2(10);
34
35 BEGIN
36
37 select resource_code into L_Resource_Code
38 from bom_resources
39 where resource_id = X_resource_id;
40
41 RETURN ( L_Resource_Code );
42
43 EXCEPTION
44 WHEN OTHERS THEN
45 RETURN ( NULL );
46
47 END BOM_RESOURCE;
48
49
50 FUNCTION Item_Number
51 ( X_Item_Id IN NUMBER
52 , X_Organization_Id IN NUMBER
53 ) return VARCHAR2 IS
54
55 L_Item_Number VARCHAR2(40);
56
57 BEGIN
58
59 SELECT segment1 into L_Item_Number
60 from mtl_system_items_b
61 where inventory_item_id = X_Item_Id
62 and organization_id = X_Organization_Id;
63
64 RETURN ( L_Item_Number );
65
66 EXCEPTION
67 WHEN OTHERS THEN
68 RETURN ( NULL );
69
70 END Item_Number;
71
72
73 FUNCTION PO_EXP_ORG
74 ( X_org IN NUMBER
75 , X_entity IN NUMBER
76 , X_seq IN NUMBER
77 , X_dest IN VARCHAR2
78 ) return NUMBER IS
79
80 L_Exp_Org NUMBER;
81
82 BEGIN
83
84 IF (X_dest = 'INVENTORY') THEN
85 L_Exp_Org := X_org;
86 ELSIF (X_dest = 'SHOP FLOOR') THEN
87 select BD.PA_EXPENDITURE_ORG_ID into L_Exp_Org
88 from WIP_OPERATIONS WO
89 , BOM_DEPARTMENTS BD
90 where WO.ORGANIZATION_ID = X_org
91 AND WO.WIP_ENTITY_ID = X_entity
92 AND WO.OPERATION_SEQ_NUM = X_seq
93 AND BD.DEPARTMENT_ID = WO.DEPARTMENT_ID;
94 END IF;
95
96 RETURN ( L_Exp_Org );
97
98 END PO_EXP_ORG;
99
100
101 FUNCTION PO_EXP_TYPE
102 ( X_org IN NUMBER
103 , X_project IN NUMBER
104 , X_item IN NUMBER
105 , X_res IN NUMBER
106 , X_dest IN VARCHAR2
107 ) return VARCHAR2 IS
108
109 L_Exp_Type VARCHAR2(30);
110
111 BEGIN
112
113 IF (X_dest = 'INVENTORY') THEN
114 L_Exp_Type := MTL_EXPENDITURE_TYPE(X_org, X_item);
115 ELSIF (X_dest = 'SHOP FLOOR') THEN
116 L_Exp_Type := OSP_EXPENDITURE_TYPE(X_org, X_project, X_res);
117 END IF;
118
119 RETURN ( L_Exp_Type );
120
121 END PO_EXP_TYPE;
122
123
124 FUNCTION PO_TASK_ID
125 ( X_org IN NUMBER
126 , X_project IN NUMBER
127 , X_dest IN VARCHAR2
128 , X_item IN NUMBER
129 , X_subinv IN VARCHAR2
130 , X_task IN NUMBER
131 , X_entity IN NUMBER
132 , X_seq IN NUMBER
133 ) return NUMBER IS
134
135 L_Task_Id NUMBER;
136 L_op NUMBER;
137 L_item NUMBER;
138 L_entity NUMBER;
139 L_dep NUMBER;
140
141 BEGIN
142
143 IF (X_task is not null) THEN L_Task_Id := X_task;
144 ELSE
145 IF (X_dest = 'INVENTORY') THEN
146 L_Task_Id := PJM_TASK_AUTO_ASSIGN.INV_TASK_WNPS
147 ( X_org
148 , X_project
149 , X_item
150 , NULL
151 , NULL
152 , X_subinv );
153 ELSIF (X_dest = 'SHOP FLOOR') THEN
154 select WO.STANDARD_OPERATION_ID
155 , WDJ.WIP_ENTITY_ID
156 , WDJ.PRIMARY_ITEM_ID
157 , WO.DEPARTMENT_ID
158 into L_op, L_entity, L_item, L_dep
159 from WIP_DISCRETE_JOBS WDJ
160 , WIP_OPERATIONS WO
161 , BOM_DEPARTMENTS BD
162 where WDJ.ORGANIZATION_ID = X_org
163 AND WDJ.WIP_ENTITY_ID = X_entity
164 AND WO.ORGANIZATION_ID = WDJ.ORGANIZATION_ID
165 AND WO.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
166 AND WO.OPERATION_SEQ_NUM = X_seq
167 AND BD.DEPARTMENT_ID = WO.DEPARTMENT_ID;
168
169 L_Task_Id := PJM_TASK_AUTO_ASSIGN.WIP_TASK_WNPS
170 ( X_org
171 , X_project
172 , L_op
173 , L_entity
174 , L_item
175 , L_dep );
176
177 END IF; -- destination type
178
179 END IF;
180 RETURN ( L_Task_Id );
181
182 END PO_TASK_ID;
183
184
185 FUNCTION Uom_Conversion_Rate
186 ( X_pollookup IN VARCHAR2
187 , X_dest IN VARCHAR2
188 , X_item IN NUMBER
189 , X_org IN NUMBER
190 ) return NUMBER IS
191
192 L_Uom_Conversion_Rate NUMBER;
193
194 BEGIN
195
196 IF (X_dest = 'INVENTORY') THEN
197 select DECODE( X_pollookup
198 , MSI.PRIMARY_UNIT_OF_MEASURE , 1
199 , INV_CONVERT.INV_UM_CONVERT
200 ( X_item, 5 , 1
201 , NULL , NULL
202 , X_pollookup
203 , MSI.PRIMARY_UNIT_OF_MEASURE ) )
204 into L_Uom_Conversion_Rate
205 from mtl_system_items_b MSI
206 where MSI.ORGANIZATION_ID = X_org
207 AND MSI.INVENTORY_ITEM_ID = X_item;
208
209 ELSIF (X_dest = 'SHOP FLOOR') THEN
210 L_Uom_Conversion_Rate := 1;
211 END IF;
212
213 RETURN ( L_Uom_Conversion_Rate );
214
215 END Uom_Conversion_Rate;
216
217
218 FUNCTION GET_UNIT
219 ( X_pollookup IN VARCHAR2
220 , X_dest IN VARCHAR2
221 , X_item IN NUMBER
222 , X_org IN NUMBER
223 ) return VARCHAR2 IS
224
225 L_Unit VARCHAR2(25);
226
227 BEGIN
228
229 IF (X_dest = 'INVENTORY') THEN
230
231 select primary_unit_of_measure
232 into L_Unit
233 from mtl_system_items_b MSI
234 where MSI.ORGANIZATION_ID = X_org
235 AND MSI.INVENTORY_ITEM_ID = X_item;
236
237 ELSIF (X_dest = 'SHOP FLOOR') THEN
238
239 L_Unit := X_pollookup;
240
241 END IF;
242
243 RETURN ( L_Unit );
244
245 END GET_UNIT;
246
247
248 FUNCTION GET_UOM_CODE
249 ( X_pollookup IN VARCHAR2
250 , X_dest IN VARCHAR2
251 , X_item IN NUMBER
252 , X_org IN NUMBER
253 ) return VARCHAR2 IS
254
255 L_Unit VARCHAR2(3);
256
257 BEGIN
258
259 IF (X_dest = 'INVENTORY') THEN
260
261 select primary_uom_code
262 into L_Unit
263 from mtl_system_items_b MSI
264 where MSI.ORGANIZATION_ID = X_org
265 AND MSI.INVENTORY_ITEM_ID = X_item;
266
267 ELSIF (X_dest = 'SHOP FLOOR') THEN
268
269 select uom_code into L_Unit
270 from MTL_UNITS_OF_MEASURE_VL
271 where unit_of_measure = X_pollookup;
272
273 END IF;
274
275 RETURN ( L_Unit );
276
277 END GET_UOM_CODE;
278
279
280 FUNCTION GET_UOM_TL
281 ( X_pollookup IN VARCHAR2
282 , X_dest IN VARCHAR2
283 , X_item IN NUMBER
284 , X_org IN NUMBER
285 ) return VARCHAR2 IS
286
287 L_Unit VARCHAR2(25);
288
289 BEGIN
290
291 IF (X_dest = 'INVENTORY') THEN
292
293 select UOM.UNIT_OF_MEASURE_TL
294 into L_Unit
295 from mtl_system_items_b MSI,
296 mtl_units_of_measure_vl UOM
297 where MSI.ORGANIZATION_ID = X_org
298 AND MSI.INVENTORY_ITEM_ID = X_item
299 AND UOM.UOM_CODE = MSI.PRIMARY_UOM_CODE;
300
301 ELSIF (X_dest = 'SHOP FLOOR') THEN
302
303 select UNIT_OF_MEASURE_TL into L_Unit
304 from MTL_UNITS_OF_MEASURE_VL
305 where unit_of_measure = X_pollookup;
306
307 END IF;
308
309 RETURN ( L_Unit );
310
311 END GET_UOM_TL;
312
313
314 FUNCTION Vendor_Name
315 ( X_Vendor_Id IN NUMBER
316 ) return VARCHAR2 IS
317
318 L_Vendor_Name VARCHAR2(360);
319
320 BEGIN
321
322 SELECT vendor_name into L_Vendor_Name
323 from po_vendors
324 where vendor_id = X_Vendor_id;
325
326 RETURN ( L_Vendor_Name );
327
328 EXCEPTION
329 WHEN OTHERS THEN
330 RETURN ( NULL );
331
332 END Vendor_Name;
333
334
335 FUNCTION People_Name
336 ( X_Person_Id IN NUMBER
337 ) return VARCHAR2 IS
338
339 L_Full_Name VARCHAR2(240);
340
341 BEGIN
342
343 SELECT full_name into L_Full_Name
344 from PER_ALL_PEOPLE_F
345 where person_id = X_Person_Id
346 AND TRUNC(SYSDATE) BETWEEN NVL(EFFECTIVE_START_DATE, SYSDATE - 1)
347 AND NVL(EFFECTIVE_END_DATE, SYSDATE + 1);
348
349 RETURN ( L_Full_Name );
350
351 EXCEPTION
352 WHEN OTHERS THEN
353 RETURN ( NULL );
354
355 END People_Name;
356
357
358 FUNCTION PO_Type
359 ( X_Org_Id IN NUMBER
360 , X_Subtype IN VARCHAR2
361 ) return VARCHAR2 IS
362
363 L_Type_Name VARCHAR2(80);
364
365 BEGIN
366
367 SELECT T.TYPE_NAME into L_Type_Name
368 from PO_DOCUMENT_TYPES_ALL_TL T
369 WHERE T.LANGUAGE = userenv('LANG')
370 and T.DOCUMENT_TYPE_CODE IN ('PO', 'PA')
371 AND T.DOCUMENT_SUBTYPE = X_Subtype
372 and NVL(T.org_id, -99) = NVL(X_Org_Id, -99);
373
374 RETURN ( L_Type_Name );
375
376 EXCEPTION
377 WHEN OTHERS THEN
378 RETURN ( NULL );
379
380 END PO_Type;
381
382
383 FUNCTION PO_PROJECT_ID
384 ( X_Org_ID IN NUMBER
385 , X_Project_ID IN NUMBER
386 ) RETURN NUMBER IS
387
388 L_PO_Project NUMBER;
389
390 BEGIN
391
392 if (X_Project_ID is not null) then
393 L_PO_Project := X_Project_ID;
394 else
395 select common_project_id into L_PO_Project
396 from pjm_org_parameters
397 where organization_id = X_Org_ID;
398 end if;
399
400 RETURN ( L_PO_Project );
401
402 EXCEPTION
403 WHEN OTHERS THEN
404 RETURN ( NULL );
405
406 END PO_PROJECT_ID;
407
408
409 FUNCTION MTL_EXPENDITURE_TYPE
410 ( X_Org_ID IN NUMBER
411 , X_Item_ID IN NUMBER
412 ) RETURN VARCHAR2 IS
413
414 L_Exp_Type VARCHAR2(30);
415
416 CURSOR c_org IS
417 SELECT br.expenditure_type
418 FROM bom_resources br
419 , cst_item_cost_details cicd
420 , mtl_parameters mp
421 WHERE mp.organization_id = X_Org_ID
422 AND cicd.organization_id (+) = mp.organization_id
423 AND cicd.inventory_item_id (+) = X_Item_ID
424 AND cicd.cost_type_id (+) = mp.primary_cost_method
425 AND cicd.cost_element_id (+) = 1
426 AND br.resource_id = nvl( cicd.resource_id , mp.default_material_cost_id )
427 AND br.organization_id = mp.organization_id
428 ORDER BY br.resource_id;
429
430 BEGIN
431 OPEN c_org;
432 FETCH c_org INTO L_Exp_Type;
433 CLOSE c_org;
434 RETURN ( L_Exp_Type );
435
436 EXCEPTION
437 WHEN OTHERS THEN
438 IF ( c_org%isopen ) THEN
439 CLOSE c_org;
440 END IF;
441 RETURN ( NULL );
442
443 END MTL_EXPENDITURE_TYPE;
444
445 FUNCTION RES_EXPENDITURE_TYPE
446 ( X_Resource_ID IN NUMBER
447 ) RETURN VARCHAR2 IS
448
449 CURSOR c_res IS
450 SELECT expenditure_type
451 FROM bom_resources
452 WHERE resource_id = X_Resource_ID;
453
454 L_Exp_Type VARCHAR2(30);
455
456 BEGIN
457
458 OPEN c_res;
459 FETCH c_res INTO L_Exp_Type;
460 CLOSE C_res;
461 RETURN ( L_Exp_Type );
462
463 EXCEPTION
464 WHEN OTHERS THEN
465 IF ( c_res%isopen ) THEN
466 CLOSE c_res;
467 END IF;
468 RETURN ( NULL );
469
470 END RES_EXPENDITURE_TYPE;
471
472 FUNCTION OSP_EXPENDITURE_TYPE
473 ( X_Org_ID IN NUMBER
474 , X_Project_ID IN NUMBER
475 , X_Resource_ID IN NUMBER
476 ) RETURN VARCHAR2 IS
477
478 CURSOR c_org IS
479 SELECT dir_item_expenditure_type
480 FROM pjm_project_parameters
481 WHERE organization_id = X_Org_ID
482 AND project_id = X_Project_ID;
483
484 CURSOR c_res IS
485 SELECT expenditure_type
486 FROM bom_resources
487 WHERE resource_id = X_Resource_ID;
488
489 L_Exp_Type VARCHAR2(30);
490
491 BEGIN
492
493 IF ( X_Resource_ID IS NULL ) THEN
494 OPEN c_org;
495 FETCH c_org INTO L_Exp_Type;
496 CLOSE c_org;
497 ELSE
498 OPEN c_res;
499 FETCH c_res INTO L_Exp_Type;
500 CLOSE C_res;
501 END IF;
502 RETURN ( L_Exp_Type );
503
504 EXCEPTION
505 WHEN OTHERS THEN
506 IF ( c_org%isopen ) THEN
507 CLOSE c_org;
508 ELSIF ( c_res%isopen ) THEN
509 CLOSE c_res;
510 END IF;
511 RETURN ( NULL );
512
513 END OSP_EXPENDITURE_TYPE;
514
515 PROCEDURE CREATE_SYNONYMS IS
516
517 CURSOR c IS
518 SELECT nvl(max(pp.project_reference_enabled) , 'N')
519 , decode(max(pp.common_project_id) , NULL , 'N' , 'Y')
520 FROM pjm_org_parameters pp;
521
522 CURSOR c2 IS
523 SELECT ou.Oracle_Username
524 FROM fnd_product_installations pi
525 , fnd_oracle_userid ou
526 WHERE ou.Oracle_ID = pi.Oracle_ID
527 AND Application_ID = 0;
528
529 pjm_implemented VARCHAR2(1);
530 common_project VARCHAR2(1);
531 applsys_schema VARCHAR2(30);
532 synonym_changed BOOLEAN := FALSE;
533
534 PROCEDURE DO_CREATE_SYNONYM
535 ( X_synonym_name IN VARCHAR2
536 , X_table_name IN VARCHAR2
537 ) IS
538
539 CURSOR s ( C_name VARCHAR2 ) IS
540 SELECT table_name
541 FROM user_synonyms
542 WHERE synonym_name = C_name;
543
544 sqlstmt VARCHAR2(240);
545 curr_table_name VARCHAR2(30);
546 create_flag VARCHAR2(1);
547
548 BEGIN
549 --
550 -- Check with existence of synonym first
551 --
552 OPEN s ( X_synonym_name );
553 FETCH s INTO curr_table_name;
554 IF ( s%notfound ) THEN
555 --
556 -- If synonym not found, we need to create it
557 --
558 CLOSE s;
559 --
560 -- Drop the view just in case
561 --
562 AD_DDL.DO_DDL( applsys_schema
563 , 'PJM'
564 , AD_DDL.DROP_VIEW
565 , 'DROP VIEW ' || X_synonym_name
566 , X_synonym_name );
567
568 create_flag := 'Y';
569 ELSE
570 CLOSE s;
571 IF ( curr_table_name <> X_table_name ) THEN
572 --
573 -- Synonym exists but points to a different object. We need to drop
574 -- the existing synonym first before recreating the new one
575 --
576 AD_DDL.DO_DDL( applsys_schema
577 , 'PJM'
578 , AD_DDL.DROP_SYNONYM
579 , 'DROP SYNONYM ' || X_synonym_name
580 , X_synonym_name );
581
582 create_flag := 'Y';
583 ELSE
584 --
585 -- Existing synonym is what we want, no need to do anything
586 --
587 create_flag := 'N';
588 END IF;
589 END IF;
590 IF ( create_flag = 'Y' ) THEN
591 sqlstmt := 'CREATE SYNONYM ' || X_synonym_name || ' FOR ' || X_table_name;
592 AD_DDL.DO_DDL( applsys_schema
593 , 'PJM'
594 , AD_DDL.CREATE_SYNONYM
595 , sqlstmt
596 , X_synonym_name );
597 synonym_changed := TRUE;
598 END IF;
599 END DO_CREATE_SYNONYM;
600
601 BEGIN
602 --
603 -- Retrieve the current PJM configuration
604 --
605 OPEN c;
606 FETCH c INTO pjm_implemented , common_project;
607 CLOSE c;
608
609 --
610 -- Getting the APPLSYS schema name
611 --
612 OPEN c2;
613 FETCH c2 INTO applsys_schema;
614 CLOSE c2;
615
616 IF ( pjm_implemented = 'N' ) THEN
617 --
618 -- PJM has not been implemented, use the stub views for commitments
619 --
620 do_create_synonym( 'PJM_PO_COMMITMENTS_V' , 'PJM_PO_COMMITMENTS_STUB_V' );
621 do_create_synonym( 'PJM_REQ_COMMITMENTS_V' , 'PJM_REQ_COMMITMENTS_STUB_V' );
622 do_create_synonym( 'CST_PROJMFG_CMT_VIEW' , 'CST_PROJMFG_CMT_STUB_V' );
623 ELSIF ( common_project = 'N' ) THEN
624 --
625 -- PJM has been implemented without common project, use the basic views for commitments
626 --
627 do_create_synonym( 'PJM_PO_COMMITMENTS_V' , 'PJM_PO_COMMITMENTS_BASIC_V' );
628 do_create_synonym( 'PJM_REQ_COMMITMENTS_V' , 'PJM_REQ_COMMITMENTS_BASIC_V' );
629 do_create_synonym( 'CST_PROJMFG_CMT_VIEW' , 'CST_PROJMFG_CMT_BASIC_V' );
630 ELSE
631 --
632 -- PJM has been implemented with common project, use the advance views for commitments
633 --
634 do_create_synonym( 'PJM_PO_COMMITMENTS_V' , 'PJM_PO_COMMITMENTS_CMPRJ_V' );
635 do_create_synonym( 'PJM_REQ_COMMITMENTS_V' , 'PJM_REQ_COMMITMENTS_CMPRJ_V' );
636 do_create_synonym( 'CST_PROJMFG_CMT_VIEW' , 'CST_PROJMFG_CMT_BASIC_V' );
637 END IF;
638
639 IF ( synonym_changed ) THEN
640 BEGIN
641 --
642 -- Recompile the PA commitment view just as a housekeeping step
643 -- Do not worry about the outcome
644 --
645 AD_DDL.DO_DDL( applsys_schema
646 , 'PA'
647 , AD_DDL.ALTER_VIEW
648 , 'ALTER VIEW PA_COMMITMENT_TXNS_V COMPILE'
649 , 'PA_COMMITMENT_TXNS_V' );
650 EXCEPTION
651 WHEN OTHERS THEN
652 NULL;
653 END;
654 END IF;
655
656 END CREATE_SYNONYMS;
657
658 END PJM_COMMITMENT_UTILS;
[Home] [Help]