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