1 PACKAGE BODY PJM_COMMON_PROJ_PKG AS
2 /* $Header: PJMCMPJB.pls 120.0.12010000.3 2009/07/17 21:18:44 exlin ship $ */
3
4
5 Function Get_Common_Project
6 ( X_Org_Id IN NUMBER
7 ) RETURN NUMBER IS
8 l_common_proj_id NUMBER;
9
10 Begin
11
12 Select Common_Project_Id
13 into l_common_proj_id
14 from pjm_org_parameters
15 where Organization_Id = X_Org_Id;
16
17 return (l_common_proj_id);
18
19 Exception
20 When NO_DATA_FOUND then
21 return(null);
22
23 End Get_Common_Project;
24
25
26 ---------------------------------------------------------------------------
27 -- PUBLIC PROCEDURE
28 -- Set_Common_Project
29 --
30 -- DESCRIPTION
31 -- This procedure will set the project/task to common project/task
32 -- for the transactions that there is no project/task reference.
33 --
34 ---------------------------------------------------------------------------
35
36 PROCEDURE Set_Common_Project
37 ( X_Org_Id IN NUMBER
38 ) IS
39
40 Cursor c is
41 select organization_id , common_project_id
42 from pjm_org_parameters
43 where organization_id = nvl(X_Org_Id , organization_id)
44 and common_project_id is not null
45 order by organization_id,common_project_id /*Bug 6972181 (FP of 6900015): Added order by clause*/
46 FOR UPDATE of organization_id; /*Bug 8668526 (one-off for 7198823) */
47
48
49 Begin
50
51 For crec in c loop
52
53 update mtl_material_transactions mmt
54 set mmt.project_id =
55 nvl(mmt.project_id , crec.common_project_id)
56 , mmt.source_project_id =
57 nvl(mmt.source_project_id,
58 decode(transaction_source_type_id,
59 5, crec.common_project_id,
60 null))
61 where mmt.pm_cost_collected = 'N'
62 and organization_id = crec.organization_id
63 and ( mmt.project_id is null or
64 (MMT.SOURCE_PROJECT_ID IS NULL and transaction_source_type_id = 5)
65 ); /* Modified where clause for Bug 8668526 (one-off for 7198823) */
66
67
68
69 --
70 -- This update is for subinventory transfers and direct org
71 -- transfers only
72 --
73 update mtl_material_transactions mmt
74 set mmt.to_project_id = crec.common_project_id
75 where mmt.pm_cost_collected = 'N'
76 and transfer_organization_id = crec.organization_id
77 and transaction_action_id not in ( 12 , 21 )
78 and to_project_id is null;
79
80 --
81 -- This update is for intransit transfers only
82 --
83 update mtl_material_transactions mmt
84 set mmt.to_project_id = crec.common_project_id
85 where mmt.pm_cost_collected = 'N'
86 and transaction_action_id in ( 12 , 21 )
87 and to_project_id is null
88 and ( organization_id , transfer_organization_id ) in (
89 select from_organization_id , to_organization_id
90 from mtl_interorg_parameters
91 where crec.organization_id =
92 decode( fob_point
93 , 1 /* Shipment */ , to_organization_id
94 , 2 /* Receipt */ , from_organization_id )
95 and mmt.transaction_action_id = 21
96 union all
97 select to_organization_id , from_organization_id
98 from mtl_interorg_parameters
99 where crec.organization_id =
100 decode( fob_point
101 , 1 /* Shipment */ , to_organization_id
102 , 2 /* Receipt */ , from_organization_id )
103 and mmt.transaction_action_id = 12
104 );
105
106 update wip_transactions wt
107 set wt.project_id = crec.common_project_id
108 where wt.pm_cost_collected = 'N'
109 and wt.organization_id = crec.organization_id
110 and wt.project_id is null;
111
112 End loop;
113
114 Exception
115 when OTHERS then
116 raise;
117 END Set_Common_Project;
118
119 END PJM_COMMON_PROJ_PKG;
[Home] [Help]