[Home] [Help]
PACKAGE BODY: APPS.CS_BILLING_TYPE_CATEGORIES_PKG
Source
1 package body CS_BILLING_TYPE_CATEGORIES_PKG as
2 /* $Header: csxchbcb.pls 115.4 2003/02/11 19:21:38 cnemalik noship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID in out NOCOPY VARCHAR2,
6 X_BILLING_TYPE in VARCHAR2,
7 X_BILLING_CATEGORY in VARCHAR2,
8 X_ROLLUP_ITEM_ID in NUMBER,
9 X_START_DATE_ACTIVE in DATE,
10 X_END_DATE_ACTIVE in DATE,
11 X_SEEDED_FLAG in VARCHAR2,
12 X_CREATION_DATE in DATE,
13 X_CREATED_BY in NUMBER,
14 X_LAST_UPDATE_DATE in DATE,
15 X_LAST_UPDATED_BY in NUMBER,
16 X_LAST_UPDATE_LOGIN in NUMBER
17 ) is
18 cursor C is select ROWID from CS_BILLING_TYPE_CATEGORIES
19 where BILLING_TYPE = X_BILLING_TYPE
20 ;
21 begin
22 insert into CS_BILLING_TYPE_CATEGORIES (
23 BILLING_TYPE,
24 BILLING_CATEGORY,
25 ROLLUP_ITEM_ID,
26 START_DATE_ACTIVE,
27 END_DATE_ACTIVE,
28 SEEDED_FLAG,
29 CREATION_DATE,
30 CREATED_BY,
31 LAST_UPDATE_DATE,
32 LAST_UPDATED_BY,
33 LAST_UPDATE_LOGIN
34 ) values (
35 X_BILLING_TYPE,
36 X_BILLING_CATEGORY,
37 X_ROLLUP_ITEM_ID,
38 X_START_DATE_ACTIVE,
39 X_END_DATE_ACTIVE,
40 X_SEEDED_FLAG,
41 X_CREATION_DATE,
42 X_CREATED_BY,
43 X_LAST_UPDATE_DATE,
44 X_LAST_UPDATED_BY,
45 X_LAST_UPDATE_LOGIN
46 );
47
48 open c;
49 fetch c into X_ROWID;
50 if (c%notfound) then
51 close c;
52 raise no_data_found;
53 end if;
54 close c;
55
56 end INSERT_ROW;
57
58
59 procedure LOCK_ROW (
60 X_BILLING_TYPE in VARCHAR2,
61 X_BILLING_CATEGORY in VARCHAR2,
62 X_ROLLUP_ITEM_ID in NUMBER,
63 X_START_DATE_ACTIVE in DATE,
64 X_END_DATE_ACTIVE in DATE
65 ) is
66 cursor c is select
67 BILLING_CATEGORY,
68 ROLLUP_ITEM_ID,
69 START_DATE_ACTIVE,
70 END_DATE_ACTIVE
71 from CS_BILLING_TYPE_CATEGORIES
72 where BILLING_TYPE = X_BILLING_TYPE
73 for update of BILLING_TYPE nowait;
74 recinfo c%rowtype;
75
76 begin
77 open c;
78 fetch c into recinfo;
79 if (c%notfound) then
80 close c;
81 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
82 app_exception.raise_exception;
83 end if;
84 close c;
85 if ( (recinfo.BILLING_CATEGORY = X_BILLING_CATEGORY)
86 AND ((recinfo.ROLLUP_ITEM_ID = X_ROLLUP_ITEM_ID)
87 OR ((recinfo.ROLLUP_ITEM_ID is null) AND (X_ROLLUP_ITEM_ID is null)))
88 AND ((recinfo.START_DATE_ACTIVE = X_START_DATE_ACTIVE)
89 OR ((recinfo.START_DATE_ACTIVE is null) AND (X_START_DATE_ACTIVE is null)))
90 AND ((recinfo.END_DATE_ACTIVE = X_END_DATE_ACTIVE)
91 OR ((recinfo.END_DATE_ACTIVE is null) AND (X_END_DATE_ACTIVE is null)))
92 ) then
93 null;
94 else
95 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
96 app_exception.raise_exception;
97 end if;
98
99 return;
100 end LOCK_ROW;
101
102 procedure UPDATE_ROW (
103 X_BILLING_TYPE in VARCHAR2,
104 X_BILLING_CATEGORY in VARCHAR2,
105 X_ROLLUP_ITEM_ID in NUMBER,
106 X_START_DATE_ACTIVE in DATE,
107 X_END_DATE_ACTIVE in DATE,
108 X_SEEDED_FLAG in VARCHAR2,
109 X_LAST_UPDATE_DATE in DATE,
110 X_LAST_UPDATED_BY in NUMBER,
111 X_LAST_UPDATE_LOGIN in NUMBER
112 ) is
113 begin
114 update CS_BILLING_TYPE_CATEGORIES set
115 BILLING_CATEGORY = X_BILLING_CATEGORY,
116 ROLLUP_ITEM_ID = X_ROLLUP_ITEM_ID,
117 START_DATE_ACTIVE = X_START_DATE_ACTIVE,
118 END_DATE_ACTIVE = X_END_DATE_ACTIVE,
119 SEEDED_FLAG = x_SEEDED_FLAG,
120 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
121 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
122 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
123 where BILLING_TYPE = X_BILLING_TYPE;
124
125 if (sql%notfound) then
126 raise no_data_found;
127 end if;
128
129 end UPDATE_ROW;
130
131 procedure DELETE_ROW (
132 X_BILLING_TYPE in VARCHAR2
133 ) is
134 begin
135 delete from CS_BILLING_TYPE_CATEGORIES
136 where BILLING_TYPE = X_BILLING_TYPE;
137
138 if (sql%notfound) then
139 raise no_data_found;
140 end if;
141 end DELETE_ROW;
142
143 PROCEDURE LOAD_ROW(
144 x_billing_type in VARCHAR2,
145 x_billing_category in VARCHAR2,
146 x_start_date_active in VARCHAR2,
147 x_end_date_active in VARCHAR2,
148 x_rollup_item_id in NUMBER,
149 x_owner in VARCHAR2,
150 x_custom_mode in VARCHAR2,
151 x_seeded_flag in VARCHAR2,
152 x_last_update_date in VARCHAR2 ) is
153
154 l_user_id number;
155 l_rowid varchar(30);
156
157 row_id varchar2(64);
158 v_audit_enabled_flag varchar2(1);
159 f_luby number; -- entity owner in file
160 f_ludate date; -- entity update date in file
161 f_stdate date; -- entity start date active in file
162 f_enddate date; -- entity end date active in file
163 db_luby number; -- entity owner in db
164 db_ludate date; -- entity update date in db
165 db_billing_type varchar2(30);
166
167 begin
168
169 if (x_owner = 'SEED') then
170 l_user_id := 1;
171 else
172 l_user_id := 0;
173 end if;
174
175 -- Translate owner to file_last_updated_by
176 f_luby := fnd_load_util.owner_id(X_OWNER);
177
178 -- Translate char dates to dates
179 f_ludate := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
180 f_stdate := to_date(X_START_DATE_ACTIVE, 'YYYY/MM/DD');
181 f_enddate := to_date(X_END_DATE_ACTIVE, 'YYYY/MM/DD');
182
183 select billing_type, last_updated_by, last_update_date
184 into db_billing_type, db_luby, db_ludate
185 from cs_billing_type_categories
186 where billing_type = X_BILLING_TYPE;
187
188 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
189 db_ludate, X_CUSTOM_MODE)) then
190
191
192 CS_BILLING_TYPE_CATEGORIES_PKG.UPDATE_ROW (
193 X_BILLING_TYPE => x_billing_type,
194 X_BILLING_CATEGORY => x_billing_category,
195 X_ROLLUP_ITEM_ID => x_rollup_item_id,
196 X_START_DATE_ACTIVE => f_stdate,
197 X_END_DATE_ACTIVE => f_enddate,
198 X_SEEDED_FLAG => x_seeded_flag,
199 X_LAST_UPDATE_DATE => f_ludate,
200 X_LAST_UPDATED_BY => f_luby,
201 X_LAST_UPDATE_LOGIN => 0);
202 end if;
203
204 exception
205 when no_data_found then
206
207 CS_BILLING_TYPE_CATEGORIES_PKG.INSERT_ROW (
208 X_ROWID => l_rowid,
209 X_BILLING_TYPE => x_billing_type,
210 X_BILLING_CATEGORY => x_billing_category,
211 X_ROLLUP_ITEM_ID => x_rollup_item_id,
212 X_START_DATE_ACTIVE => f_stdate,
213 X_END_DATE_ACTIVE => f_enddate,
214 X_SEEDED_FLAG => x_seeded_flag,
215 X_CREATION_DATE => f_ludate,
216 X_CREATED_BY => f_luby,
217 X_LAST_UPDATE_DATE => f_ludate,
218 X_LAST_UPDATED_BY => f_luby,
219 X_LAST_UPDATE_LOGIN => 0);
220
221 end LOAD_ROW;
222
223 end CS_BILLING_TYPE_CATEGORIES_PKG;