[Home] [Help]
PACKAGE BODY: APPS.CN_REVENUE_CLASS_PKG
Source
1 PACKAGE BODY CN_REVENUE_CLASS_PKG AS
2 /* $Header: cntrclsb.pls 120.2 2005/08/07 23:02:48 vensrini noship $ */
3 --
4 -- Package Name
5 -- CN_REVENUE_CLASS_PKG
6 -- Purpose
7 -- Table Handler for CN_REVENUE_CLASS
8 --
9 -- History
10 -- 02-feb-01 Kumar Sivasankaran
11 -- ==========================================================================
12 -- |
13 -- | PRIVATE VARIABLES
14 -- |
15 -- ==========================================================================
16 g_program_type VARCHAR2(30) := NULL;
17 -- ==========================================================================
18 -- |
19 -- | PRIVATE ROUTINES
20 -- |
21 -- ==========================================================================
22
23 -- ==========================================================================
24 -- | Custom Validation
25 -- ==========================================================================
26
27 -- ==========================================================================
28 -- Procedure Name
29 -- Get_UID
30 -- Purpose
31 -- Get the Sequence Number to Create a new revenue Class
32 -- ==========================================================================
33 PROCEDURE Get_UID( x_revenue_class_id IN OUT NOCOPY NUMBER) IS
34
35 BEGIN
36
37 SELECT cn_revenue_classes_s.nextval
38 INTO X_revenue_class_id
39 FROM dual;
40
41 END Get_UID;
42
43 -- ==========================================================================
44 -- Procedure Name
45 -- Insert_row
46 -- Purpose
47 -- Main insert procedure
48 -- ==========================================================================
49 PROCEDURE insert_row
50 (x_revenue_class_id IN OUT NOCOPY NUMBER
51 ,p_name VARCHAR2 := NULL
52 ,p_description VARCHAR2 := NULL
53 ,p_liability_account_id NUMBER := NULL
54 ,p_expense_account_id NUMBER := NULL
55 ,p_Created_By NUMBER
56 ,p_Creation_Date DATE
57 ,p_Last_Updated_By NUMBER
58 ,p_Last_Update_Date DATE
59 ,p_Last_Update_Login NUMBER
60 ,p_org_id IN NUMBER)
61 IS
62 l_dummy NUMBER;
63
64 BEGIN
65
66 Get_UID( x_revenue_class_id );
67
68 INSERT INTO cn_revenue_Classes
69 ( ORG_ID
70 ,revenue_class_id
71 ,name
72 ,DESCRIPTION
73 ,liability_account_id
74 ,expense_account_id
75 ,object_version_number
76 ,Created_By
77 ,Creation_Date
78 ,Last_Updated_By
79 ,Last_Update_Date
80 ,Last_Update_Login)
81 VALUES
82 ( p_org_id
83 ,x_revenue_class_id
84 ,p_name
85 ,p_DESCRIPTION
86 ,p_liability_account_id
87 ,p_expense_account_id
88 ,1
89 ,p_Created_By
90 ,p_Creation_Date
91 ,p_Last_Updated_By
92 ,p_Last_Update_Date
93 ,p_Last_Update_Login
94 );
95
96 END Insert_row;
97
98 -- ==========================================================================
99 -- Procedure Name
100 -- Update Record
101 -- Purpose
102 --
103 -- ==========================================================================
104 PROCEDURE update_row
105 (p_revenue_class_id NUMBER
106 ,p_name VARCHAR2
107 ,p_description VARCHAR2
108 ,p_liability_account_id NUMBER
109 ,p_expense_account_id NUMBER
110 ,p_object_version_number NUMBER
111 ,p_Last_Updated_By NUMBER
112 ,p_Last_Update_Date DATE
113 ,p_Last_Update_Login NUMBER ) IS
114
115
116 l_revenue_class_id cn_revenue_classes.revenue_class_id%TYPE;
117 l_name cn_revenue_classes.name%TYPE;
118 l_description cn_revenue_classes.description%TYPE;
119 l_liability_account_Id cn_revenue_classes.liability_account_id%TYPE;
120 l_expense_account_Id cn_revenue_classes.expense_account_id%TYPE;
121
122 CURSOR C IS
123 SELECT *
124 FROM cn_revenue_classes
125 WHERE revenue_class_id = p_revenue_class_id
126 FOR UPDATE of revenue_class_id NOWAIT;
127 oldrow C%ROWTYPE;
128
129 BEGIN
130 OPEN C;
131 FETCH C INTO oldrow;
132
133 IF (C%NOTFOUND) then
134 CLOSE C;
135 fnd_message.Set_Name('FND', 'FORM_RECORD_DELETED');
136 app_exception.raise_exception;
137 END IF;
138 CLOSE C;
139
140
141 SELECT decode(p_revenue_class_id,
142 fnd_api.g_miss_num, oldrow.revenue_class_id,
143 p_revenue_class_id),
144 decode(p_name,
145 fnd_api.g_miss_char, oldrow.name,
146 p_name),
147 decode(p_description,
148 fnd_api.g_miss_char, oldrow.description,
149 p_description),
150 decode(p_liability_account_id,
151 fnd_api.g_miss_num, oldrow.liability_account_id,
152 p_liability_account_id),
153 decode(p_expense_account_id,
154 fnd_api.g_miss_num, oldrow.expense_account_id,
155 p_expense_account_id)
156 INTO
157 l_revenue_class_id,
158 l_name ,
159 l_description ,
160 l_liability_account_id,
161 l_expense_account_id
162 FROM dual;
163
164 IF (SQL%NOTFOUND) THEN
165 RAISE NO_DATA_FOUND;
166 END IF;
167
168
169 UPDATE cn_revenue_classes
170 SET
171 revenue_class_id = l_revenue_class_id,
172 object_version_number = nvl(p_object_version_number,0) + 1,
173 name = l_name,
174 description = l_description,
175 liability_account_id = l_liability_account_id,
176 expense_account_id = l_expense_account_id,
177 last_update_date = p_Last_Update_Date,
178 last_updated_by = p_Last_Updated_By,
179 last_update_login = p_Last_Update_Login
180 WHERE revenue_class_id = p_revenue_class_id;
181
182 IF oldrow.name <> l_name THEN
183
184 UPDATE cn_hierarchy_nodes chn
185 SET chn.name = l_name
186 WHERE chn.external_id = l_revenue_class_id
187 AND chn.dim_hierarchy_id IN (
188 select dh.dim_hierarchy_id
189 from cn_dimensions d,
190 cn_obj_tables_v t,
191 cn_head_hierarchies h,
192 cn_dim_hierarchies dh
193 where d.source_table_id = t.table_id
194 and d.dimension_id = h.dimension_id
195 and h.head_hierarchy_id = dh.header_dim_hierarchy_id
196 and t.name = 'CN_REVENUE_CLASSES');
197
198 END IF;
199
200 END Update_row;
201
202 -- ==========================================================================
203 -- Procedure Name
204 -- Delete_row
205 -- Purpose
206 -- ==========================================================================
207
208 PROCEDURE Delete_row( p_revenue_class_id NUMBER ) IS
209 BEGIN
210
211 DELETE FROM cn_revenue_classes
212 WHERE revenue_class_id = p_revenue_class_id ;
213 IF (SQL%NOTFOUND) THEN
214 RAISE NO_DATA_FOUND;
215 END IF;
216
217 END Delete_row;
218
219 END CN_REVENUE_CLASS_PKG;