[Home] [Help]
PACKAGE BODY: APPS.ZX_SBSCR_OPTIONS_MIGRATE_PKG
Source
1 PACKAGE BODY ZX_SBSCR_OPTIONS_MIGRATE_PKG as
2 /* $Header: zxsbscrmigpkgb.pls 120.23 2006/05/12 12:33:23 asengupt ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(50) := 'ZX_SBSCR_OPTIONS_MIGRATE_PKG';
5 G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
6 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
7 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
8 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
9 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
10 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
11 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
12 G_MODULE_NAME CONSTANT VARCHAR2(250) := 'ZX.PLSQL.ZX_SBSCR_OPTIONS_MIGRATE_PKG.';
13
14
15 L_MULTI_ORG_FLAG FND_PRODUCT_GROUPS.MULTI_ORG_FLAG%TYPE;
16 L_ORG_ID NUMBER(15);
17
18 PROCEDURE SBSCRPTN_OPTIONS_MIGRATE (x_return_status OUT NOCOPY VARCHAR2) IS
19
20 l_api_name CONSTANT VARCHAR2(50) := 'SUBSCRIPTION_OPTIONS_MIGRATE';
21
22 BEGIN
23 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
24 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'()+');
25 END IF;
26
27 x_return_status := FND_API.G_RET_STS_SUCCESS;
28
29 INSERT INTO ZX_REGIMES_USAGES (
30 REGIME_USAGE_ID,
31 FIRST_PTY_ORG_ID,
32 TAX_REGIME_ID,
33 TAX_REGIME_CODE,
34 RECORD_TYPE_CODE,
35 OBJECT_VERSION_NUMBER,
36 CREATION_DATE,
37 CREATED_BY,
38 LAST_UPDATE_DATE,
39 LAST_UPDATED_BY,
40 LAST_UPDATE_LOGIN
41 )
42 SELECT
43 ZX_REGIMES_USAGES_S.NEXTVAL,
44 ptp.party_tax_profile_id CONTENT_OWNER_ID,
45 regime.tax_regime_id TAX_REGIME_ID,
46 regime.tax_regime_code TAX_REGIME_CODE,
47 'MIGRATED',
48 1,
49 SYSDATE ,
50 fnd_global.user_id ,
51 SYSDATE ,
52 fnd_global.user_id ,
53 fnd_global.conc_login_id
54 FROM ZX_REGIMES_B regime,
55 ZX_PARTY_TAX_PROFILE ptp,
56 ( SELECT decode(l_multi_org_flag,'N',l_org_id,org_id) org_id
57 FROM ap_tax_codes_all
58 UNION
59 SELECT decode(l_multi_org_flag,'N',l_org_id,org_id) org_id
60 FROM ar_vat_tax_all_b) codes
61 WHERE decode(l_multi_org_flag,'N',l_org_id,codes.org_id) = ptp.party_id
62 AND ptp.party_type_code = 'OU'
63 AND regime.record_type_code = 'MIGRATED'
64 AND ptp.record_type_code = 'MIGRATED'
65 AND NOT EXISTS ( SELECT 1
66 FROM zx_regimes_usages ru
67 WHERE (ru.FIRST_PTY_ORG_ID = ptp.party_tax_profile_id
68 AND ru.tax_regime_code = regime.tax_regime_code)
69 OR (ru.FIRST_PTY_ORG_ID = ptp.party_tax_profile_id
70 AND ru.tax_regime_id = regime.tax_regime_id)
71 );
72
73
74 INSERT INTO ZX_SUBSCRIPTION_OPTIONS (
75 SUBSCRIPTION_OPTION_ID,
76 REGIME_USAGE_ID,
77 SUBSCRIPTION_OPTION_CODE,
78 PARENT_FIRST_PTY_ORG_ID,
79 EFFECTIVE_FROM,
80 EFFECTIVE_TO,
81 ENABLED_FLAG,
82 ALLOW_SUBSCRIPTION_FLAG,
83 RECORD_TYPE_CODE,
84 EXCEPTION_OPTION_CODE,
85 CREATION_DATE,
86 CREATED_BY,
87 LAST_UPDATE_DATE,
88 LAST_UPDATED_BY,
89 LAST_UPDATE_LOGIN
90 )
91 SELECT ZX_SUBSCRIPTION_OPTIONS_S.nextval,
92 ru.regime_usage_id,
93 'OWN_GCO',
94 NULL,
95 regime.effective_from,
96 regime.effective_to,
97 'Y',
98 'N',
99 'MIGRATED',
100 'OWN_ONLY',
101 SYSDATE ,
102 fnd_global.user_id ,
103 SYSDATE ,
104 fnd_global.user_id ,
105 fnd_global.conc_login_id
106 FROM ZX_REGIMES_B regime,
107 ZX_REGIMES_USAGES ru
108 WHERE regime.tax_regime_code = ru.tax_regime_code
109 AND ru.record_type_code = 'MIGRATED'
110 AND NOT EXISTS (SELECT 1
111 FROM ZX_SUBSCRIPTION_OPTIONS opt
112 WHERE opt.regime_usage_id = ru.regime_usage_id
113 ) ;
114
115
116 INSERT INTO ZX_SUBSCRIPTION_DETAILS (
117 SUBSCRIPTION_DETAIL_ID,
118 SUBSCRIPTION_OPTION_ID,
119 FIRST_PTY_ORG_ID,
120 PARENT_FIRST_PTY_ORG_ID,
121 VIEW_OPTIONS_CODE,
122 TAX_REGIME_CODE,
123 EFFECTIVE_FROM,
124 EFFECTIVE_TO,
125 RECORD_TYPE_CODE,
126 CREATION_DATE,
127 CREATED_BY,
128 LAST_UPDATE_DATE,
129 LAST_UPDATED_BY,
130 LAST_UPDATE_LOGIN
131 )
132 SELECT ZX_SUBSCRIPTION_DETAILS_S.nextval,
133 opt.subscription_option_id,
134 ru.first_pty_org_id,
135 ru.first_pty_org_id,
136 'VFC',
137 ru.tax_regime_code,
138 opt.effective_from,
139 opt.effective_to,
140 'MIGRATED',
141 SYSDATE ,
142 fnd_global.user_id ,
143 SYSDATE ,
144 fnd_global.user_id ,
145 fnd_global.conc_login_id
146 FROM ZX_REGIMES_USAGES ru,
147 ZX_SUBSCRIPTION_OPTIONS opt
148 WHERE ru.regime_usage_id = opt.regime_usage_id
149 AND opt.record_type_code = 'MIGRATED'
150 AND NOT EXISTS (SELECT 1
151 FROM ZX_SUBSCRIPTION_DETAILS det
152 WHERE det.first_pty_org_id = ru.first_pty_org_id
153 AND det.parent_first_pty_org_id = ru.first_pty_org_id
154 AND det.tax_regime_code = ru.tax_regime_code
155 AND det.view_options_code = 'VFC'
156 AND det.effective_from = opt.effective_from
157 );
158
159 /* Insert a row for every regime */
160 INSERT INTO ZX_SUBSCRIPTION_DETAILS (
161 SUBSCRIPTION_DETAIL_ID,
162 SUBSCRIPTION_OPTION_ID,
163 FIRST_PTY_ORG_ID,
164 PARENT_FIRST_PTY_ORG_ID,
165 VIEW_OPTIONS_CODE,
166 TAX_REGIME_CODE,
167 EFFECTIVE_FROM,
168 EFFECTIVE_TO,
169 RECORD_TYPE_CODE,
170 CREATION_DATE,
171 CREATED_BY,
172 LAST_UPDATE_DATE,
173 LAST_UPDATED_BY,
174 LAST_UPDATE_LOGIN
175 )
176 SELECT ZX_SUBSCRIPTION_DETAILS_S.nextval,
177 0,
178 -99,
179 -99,
180 'VFC',
181 zrb.tax_regime_code,
182 zrb.effective_from,
183 zrb.effective_to,
184 'MIGRATED',
185 SYSDATE ,
186 fnd_global.user_id ,
187 SYSDATE ,
188 fnd_global.user_id ,
189 fnd_global.conc_login_id
190 FROM ZX_REGIMES_B zrb
191 WHERE NVL(zrb.has_sub_regime_flag,'N') = 'N'
192 AND zrb.record_type_code = 'MIGRATED'
193 AND NOT EXISTS (SELECT 1
194 FROM ZX_SUBSCRIPTION_DETAILS det
195 WHERE det.first_pty_org_id = -99
196 AND det.parent_first_pty_org_id = -99
197 AND det.tax_regime_code = zrb.tax_regime_code
198 AND det.view_options_code = 'VFC'
199 AND det.effective_from = zrb.effective_from
200 );
201
202 /** VFD row not insrted for OWN_GCO case now
203
204 INSERT INTO ZX_SUBSCRIPTION_DETAILS (
205 SUBSCRIPTION_DETAIL_ID,
206 SUBSCRIPTION_OPTION_ID,
207 FIRST_PTY_ORG_ID,
208 PARENT_FIRST_PTY_ORG_ID,
209 VIEW_OPTIONS_CODE,
210 TAX_REGIME_CODE,
211 EFFECTIVE_FROM,
212 EFFECTIVE_TO,
213 RECORD_TYPE_CODE,
214 CREATION_DATE,
215 CREATED_BY,
216 LAST_UPDATE_DATE,
217 LAST_UPDATED_BY,
218 LAST_UPDATE_LOGIN
219 )
220 SELECT ZX_SUBSCRIPTION_DETAILS_S.nextval,
221 opt.subscription_option_id,
222 ru.first_pty_org_id,
223 -99,
224 'VFD',
225 ru.tax_regime_code,
226 opt.effective_from,
227 opt.effective_to,
228 'MIGRATED',
229 ru.creation_date,
230 ru.created_by,
231 ru.last_update_date,
232 ru.last_updated_by,
233 ru.last_update_login
234 FROM ZX_REGIMES_USAGES ru,
235 ZX_SUBSCRIPTION_OPTIONS opt
236 WHERE ru.regime_usage_id = opt.regime_usage_id
237 AND NOT EXISTS (SELECT 1
238 FROM ZX_SUBSCRIPTION_DETAILS det
239 WHERE det.first_pty_org_id = ru.first_pty_org_id
240 AND det.parent_first_pty_org_id = -99
241 AND det.tax_regime_code = ru.tax_regime_code
242 AND det.view_options_code = 'VFD'
243 AND det.effective_from = opt.effective_from
244 );
245 **/
246
247 INSERT INTO ZX_SUBSCRIPTION_DETAILS (
248 SUBSCRIPTION_DETAIL_ID,
249 SUBSCRIPTION_OPTION_ID,
250 FIRST_PTY_ORG_ID,
251 PARENT_FIRST_PTY_ORG_ID,
252 VIEW_OPTIONS_CODE,
253 TAX_REGIME_CODE,
254 EFFECTIVE_FROM,
255 EFFECTIVE_TO,
256 RECORD_TYPE_CODE,
257 CREATION_DATE,
258 CREATED_BY,
259 LAST_UPDATE_DATE,
260 LAST_UPDATED_BY,
261 LAST_UPDATE_LOGIN
262 )
263 SELECT ZX_SUBSCRIPTION_DETAILS_S.nextval,
264 opt.subscription_option_id,
265 ru.first_pty_org_id,
266 -99,
267 'VFR',
268 ru.tax_regime_code,
269 opt.effective_from,
270 opt.effective_to,
271 'MIGRATED',
272 SYSDATE ,
273 fnd_global.user_id ,
274 SYSDATE ,
275 fnd_global.user_id ,
276 fnd_global.conc_login_id
277 FROM ZX_REGIMES_USAGES ru,
278 ZX_SUBSCRIPTION_OPTIONS opt
279 WHERE ru.regime_usage_id = opt.regime_usage_id
280 AND opt.record_type_code = 'MIGRATED'
281 AND NOT EXISTS (SELECT 1
282 FROM ZX_SUBSCRIPTION_DETAILS det
283 WHERE det.first_pty_org_id = ru.first_pty_org_id
284 AND det.parent_first_pty_org_id = -99
285 AND det.tax_regime_code = ru.tax_regime_code
286 AND det.view_options_code = 'VFR'
287 AND det.effective_from = opt.effective_from
288 );
289
290 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
291 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'()-');
292 END IF;
293
294 /*Bug 5204559*/
295 UPDATE ZX_REGIMES_B
296 SET ALLOW_EXEMPTIONS_FLAG = 'Y'
297 WHERE TAX_REGIME_ID IN (SELECT distinct RU.TAX_REGIME_ID
298 FROM ZX_REGIMES_USAGES RU,
299 ZX_PARTY_TAX_PROFILE PTP,
300 AR_SYSTEM_PARAMETERS_ALL ARSYS
301 WHERE PTP.PARTY_TAX_PROFILE_ID = RU.FIRST_PTY_ORG_ID
302 AND PTP.PARTY_TYPE_CODE = 'OU'
303 AND PTP.PARTY_ID = ARSYS.ORG_ID
304 AND nvl(ARSYS.TAX_USE_CUSTOMER_EXEMPT_FLAG,'N') = 'Y'
305 AND PTP.RECORD_TYPE_CODE = 'MIGRATED')
306 AND RECORD_TYPE_CODE = 'MIGRATED';
307
308 UPDATE ZX_REGIMES_B
309 SET ALLOW_EXCEPTIONS_FLAG = 'Y'
310 WHERE TAX_REGIME_ID IN (SELECT TAX_REGIME_ID
311 FROM ZX_REGIMES_USAGES RU,
312 ZX_PARTY_TAX_PROFILE PTP,
313 AR_SYSTEM_PARAMETERS_ALL ARSYS
314 WHERE PTP.PARTY_TAX_PROFILE_ID = RU.FIRST_PTY_ORG_ID
315 AND PTP.PARTY_TYPE_CODE = 'OU'
316 AND PTP.PARTY_ID = ARSYS.ORG_ID
317 AND (nvl(ARSYS.TAX_USE_PRODUCT_EXEMPT_FLAG,'N') = 'Y'
318 OR nvl(TAX_USE_LOC_EXC_RATE_FLAG,'N') = 'Y')
319 AND PTP.RECORD_TYPE_CODE = 'MIGRATED')
320
321 AND RECORD_TYPE_CODE = 'MIGRATED';
322
323 END SBSCRPTN_OPTIONS_MIGRATE;
324
325 BEGIN
326
327 SELECT NVL(MULTI_ORG_FLAG,'N') INTO L_MULTI_ORG_FLAG FROM
328 FND_PRODUCT_GROUPS;
329
330 IF L_MULTI_ORG_FLAG = 'N' THEN
331
332 FND_PROFILE.GET('ORG_ID',L_ORG_ID);
333
334 IF L_ORG_ID IS NULL THEN
335 arp_util_tax.debug('MO: Operating Units site level profile option value not set , resulted in Null Org Id');
336 END IF;
337 ELSE
341
338 L_ORG_ID := NULL;
339 END IF;
340
342 END ZX_SBSCR_OPTIONS_MIGRATE_PKG;