1 PACKAGE BODY PJM_COMMON_PROJ_PKG AS
2 /* $Header: PJMCMPJB.pls 120.0.12010000.2 2008/09/17 13:23:53 ybabulal 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
47 Begin
48
49 For crec in c loop
50
51 update mtl_material_transactions mmt
52 set mmt.project_id =
53 nvl(mmt.project_id , crec.common_project_id)
54 , mmt.source_project_id =
55 nvl(mmt.source_project_id,
56 decode(transaction_source_type_id,
57 5, crec.common_project_id,
58 null))
59 where mmt.pm_cost_collected = 'N'
60 and organization_id = crec.organization_id
61 and ( mmt.project_id is null
62 or mmt.source_project_id is null
63 );
64
65 --
66 -- This update is for subinventory transfers and direct org
67 -- transfers only
68 --
69 update mtl_material_transactions mmt
70 set mmt.to_project_id = crec.common_project_id
71 where mmt.pm_cost_collected = 'N'
72 and transfer_organization_id = crec.organization_id
73 and transaction_action_id not in ( 12 , 21 )
74 and to_project_id is null;
75
76 --
77 -- This update is for intransit transfers only
78 --
79 update mtl_material_transactions mmt
80 set mmt.to_project_id = crec.common_project_id
81 where mmt.pm_cost_collected = 'N'
82 and transaction_action_id in ( 12 , 21 )
83 and to_project_id is null
84 and ( organization_id , transfer_organization_id ) in (
85 select from_organization_id , to_organization_id
86 from mtl_interorg_parameters
87 where crec.organization_id =
88 decode( fob_point
89 , 1 /* Shipment */ , to_organization_id
90 , 2 /* Receipt */ , from_organization_id )
91 and mmt.transaction_action_id = 21
92 union all
93 select to_organization_id , from_organization_id
94 from mtl_interorg_parameters
95 where crec.organization_id =
96 decode( fob_point
97 , 1 /* Shipment */ , to_organization_id
98 , 2 /* Receipt */ , from_organization_id )
99 and mmt.transaction_action_id = 12
100 );
101
102 update wip_transactions wt
103 set wt.project_id = crec.common_project_id
104 where wt.pm_cost_collected = 'N'
105 and wt.organization_id = crec.organization_id
106 and wt.project_id is null;
107
108 End loop;
109
110 Exception
111 when OTHERS then
112 raise;
113 END Set_Common_Project;
114
115 END PJM_COMMON_PROJ_PKG;
[Home] [Help]