[Home] [Help]
PACKAGE BODY: APPS.IGI_CIS_UPGRADE_PKG
Source
1 PACKAGE BODY IGI_CIS_UPGRADE_PKG AS
2 -- $Header: igipupgb.pls 120.0.12000000.2 2007/07/24 10:07:55 vensubra noship $
3
4 Procedure MIGRATE_DATA(p_errbuff OUT NOCOPY VARCHAR2,p_retcode OUT NOCOPY NUMBER)
5 is
6
7 CURSOR C_UPDATE IS
8 SELECT
9 DISTINCT AATR.VENDOR_ID
10 FROM
11 AP_AWT_TAX_RATES AATR, IGI_CIS_CERT_NI_NUMBERS ICCNN
12 WHERE
13 --TRUNC(SYSDATE) BETWEEN TRUNC(NVL(START_DATE, SYSDATE)) bug 5620412
14 --AND TRUNC(NVL(END_DATE, SYSDATE)) bug 5620412
15 --AND bug 5620412
16 AATR.CERTIFICATE_TYPE IN ('CIS4P', 'CIS4PF', 'CIS4T', 'CIS4TF', 'CIS5', 'CIS5F', 'CIS6', 'CIS6F')
17 AND AATR.TAX_RATE_ID = ICCNN.TAX_RATE_ID (+);
18
19 C_REC_INFO C_UPDATE%ROWTYPE;
20
21 TEMP_NI_NUMBER VARCHAR2 (30);
22 TEMP_COUNT NUMBER;
23 TEMP_COUNT2 NUMBER;
24 TEMP_UTR_NO VARCHAR2 (10);
25 TEMP_VENDOR_NAME VARCHAR2 (240);
26 TEMP_VENDOR_TYPE VARCHAR2 (30);
27
28 BEGIN
29
30 For C_REC_INFO in C_UPDATE LOOP
31
32 TEMP_NI_NUMBER := NULL;
33 TEMP_UTR_NO := 0;
34 TEMP_COUNT := 0;
35 TEMP_COUNT2 := 0;
36 TEMP_VENDOR_NAME := NULL;
37 TEMP_VENDOR_TYPE := NULL;
38
39 SELECT
40 VENDOR_NAME,VENDOR_TYPE_LOOKUP_CODE
41 INTO TEMP_VENDOR_NAME,TEMP_VENDOR_TYPE
42 FROM
43 AP_SUPPLIERS
44 WHERE
45 VENDOR_ID = C_REC_INFO.VENDOR_ID;
46
47 FND_MESSAGE.SET_NAME('IGI','IGI_CIS2007_UPG_VENDOR_DET');
48 FND_MESSAGE.SET_TOKEN('VENDOR_ID', C_REC_INFO.VENDOR_ID);
49 FND_MESSAGE.SET_TOKEN('VENDOR_NAME', TEMP_VENDOR_NAME);
50 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, FND_MESSAGE.GET);
51
52 -- ===== UPDATING CIS ENABLED FLAG ====
53
54 UPDATE AP_SUPPLIERS
55 SET CIS_ENABLED_FLAG = 'Y'
56 WHERE
57 VENDOR_ID = C_REC_INFO.VENDOR_ID;
58 IF SQL%FOUND THEN
59 WRITE_REPORT('IGI_CIS2007_UPG_CIS_FLAG');
60 END IF;
61
62 -- ===== UPDATING NATIONAL INSURANCE NUMBER ====
63 BEGIN
64 SELECT
65 COUNT(DISTINCT(ICCNN.NI_NUMBER)) INTO TEMP_COUNT
66 FROM
67 AP_AWT_TAX_RATES AATR, IGI_CIS_CERT_NI_NUMBERS ICCNN
68 WHERE
69 AATR.VENDOR_ID = C_REC_INFO.VENDOR_ID
70 AND AATR.CERTIFICATE_TYPE IN ('CIS4P', 'CIS4PF', 'CIS6', 'CIS6F')
71 -- AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(START_DATE, SYSDATE))
72 -- AND TRUNC(NVL(END_DATE, SYSDATE))
73 -- Bug 5620412
74 AND ICCNN.NI_NUMBER IS NOT NULL
75 AND AATR.TAX_RATE_ID = ICCNN.TAX_RATE_ID (+);
76 EXCEPTION
77 When NO_DATA_FOUND THEN
78 TEMP_COUNT:=0;
79 END;
80
81 IF TEMP_COUNT = 0 THEN
82 WRITE_REPORT('IGI_CIS2007_UPG_NO_NINO');
83 ELSIF TEMP_COUNT > 1 THEN
84 -- If there are multiple certificates, then determine which
85 -- certificate is active and use the NI Number from that certificate
86 BEGIN
87 SELECT
88 COUNT(DISTINCT(ICCNN.NI_NUMBER)) INTO TEMP_COUNT2
89 FROM
90 AP_AWT_TAX_RATES AATR, IGI_CIS_CERT_NI_NUMBERS ICCNN
91 WHERE
92 AATR.VENDOR_ID = C_REC_INFO.VENDOR_ID
93 AND AATR.CERTIFICATE_TYPE IN ('CIS4P', 'CIS4PF', 'CIS6', 'CIS6F')
94 AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(AATR.START_DATE, SYSDATE))
95 AND TRUNC(NVL(AATR.END_DATE, SYSDATE))
96 AND ICCNN.NI_NUMBER IS NOT NULL
97 AND AATR.TAX_RATE_ID = ICCNN.TAX_RATE_ID (+);
98 EXCEPTION
99 When NO_DATA_FOUND THEN
100 TEMP_COUNT2:=0;
101 END;
102
103 IF TEMP_COUNT2 = 0 THEN
104 WRITE_REPORT('IGI_CIS2007_UPG_NO_NINO');
105 ELSIF TEMP_COUNT2 > 1 THEN
106 --There are multiple active certificates
107 WRITE_REPORT('IGI_CIS2007_UPG_MULTIPLE_NINO');
108 ELSE
109 --Use the NI Number present in the Active Certificate
110 SELECT
111 DISTINCT (ICCNN.NI_NUMBER) INTO TEMP_NI_NUMBER
112 FROM
113 AP_AWT_TAX_RATES AATR, IGI_CIS_CERT_NI_NUMBERS ICCNN
114 WHERE
115 AATR.VENDOR_ID = C_REC_INFO.VENDOR_ID
116 AND AATR.CERTIFICATE_TYPE IN ('CIS4P', 'CIS4PF', 'CIS6', 'CIS6F')
117 AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(AATR.START_DATE, SYSDATE))
118 AND TRUNC(NVL(AATR.END_DATE, SYSDATE))
119 AND ICCNN.NI_NUMBER IS NOT NULL
120 AND AATR.TAX_RATE_ID = ICCNN.TAX_RATE_ID (+);
121
122 IF IGI_CIS_UPGRADE_PKG.IGI_CIS_VALIDATE_NI_NUMBER (TEMP_NI_NUMBER) THEN
123 UPDATE AP_SUPPLIERS
124 SET NATIONAL_INSURANCE_NUMBER = TEMP_NI_NUMBER
125 WHERE
126 VENDOR_ID = C_REC_INFO.VENDOR_ID;
127
128 IF SQL%FOUND THEN
129 WRITE_REPORT('IGI_CIS2007_UPG_NINO');
130 END IF;
131 ELSE
132 WRITE_REPORT('IGI_CIS2007_UPG_INVALID_NINO');
133 END IF;
134 END IF;
135 ELSE
136 SELECT
137 DISTINCT (ICCNN.NI_NUMBER) INTO TEMP_NI_NUMBER
138 FROM
139 AP_AWT_TAX_RATES AATR, IGI_CIS_CERT_NI_NUMBERS ICCNN
140 WHERE
141 AATR.VENDOR_ID = C_REC_INFO.VENDOR_ID
142 AND AATR.CERTIFICATE_TYPE IN ('CIS4P', 'CIS4PF', 'CIS6', 'CIS6F')
143 -- AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(START_DATE, SYSDATE))
144 -- AND TRUNC(NVL(END_DATE, SYSDATE))
145 -- Bug 5620412
146 AND ICCNN.NI_NUMBER IS NOT NULL
147 AND AATR.TAX_RATE_ID = ICCNN.TAX_RATE_ID (+);
148
149 IF IGI_CIS_UPGRADE_PKG.IGI_CIS_VALIDATE_NI_NUMBER (TEMP_NI_NUMBER) THEN
150 UPDATE AP_SUPPLIERS
151 SET NATIONAL_INSURANCE_NUMBER = TEMP_NI_NUMBER
152 WHERE
153 VENDOR_ID = C_REC_INFO.VENDOR_ID;
154
155 IF SQL%FOUND THEN
156 WRITE_REPORT('IGI_CIS2007_UPG_NINO');
157 END IF;
158 ELSE
159 WRITE_REPORT('IGI_CIS2007_UPG_INVALID_NINO');
160 END IF;
161 END IF;
162
163 -- ==== UPDATING UNIQUE TAX REFERENCE NUMBER ===
164
165 TEMP_COUNT := 0;
166 TEMP_COUNT2 := 0;
167
168 BEGIN
169 SELECT
170 COUNT(DISTINCT(SUBSTR (AATR.CERTIFICATE_NUMBER, 1,10))) INTO TEMP_COUNT
171 FROM
172 AP_AWT_TAX_RATES AATR, IGI_CIS_CERT_NI_NUMBERS ICCNN
173 WHERE
174 AATR.VENDOR_ID = C_REC_INFO.VENDOR_ID
175 AND AATR.CERTIFICATE_TYPE IN ('CIS4P', 'CIS4PF', 'CIS4T', 'CIS4TF', 'CIS5', 'CIS5F', 'CIS6', 'CIS6F')
176 --AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(START_DATE, SYSDATE)) bug 5620412
177 --AND TRUNC(NVL(END_DATE, SYSDATE)) bug 5620412
178 AND AATR.CERTIFICATE_NUMBER IS NOT NULL
179 AND AATR.TAX_RATE_ID = ICCNN.TAX_RATE_ID (+);
180 EXCEPTION
181 When NO_DATA_FOUND THEN
182 TEMP_COUNT:=0;
183 END;
184
185 IF TEMP_COUNT = 0 THEN
186 WRITE_REPORT('IGI_CIS2007_UPG_NO_UTR');
187 ELSIF TEMP_COUNT > 1 THEN
188 -- If there are multiple certificates, then determine which
189 -- certificate is active and use the UTR from that certificate
190 BEGIN
191 SELECT
192 COUNT(DISTINCT(SUBSTR (AATR.CERTIFICATE_NUMBER, 1,10))) INTO TEMP_COUNT2
193 FROM
194 AP_AWT_TAX_RATES AATR, IGI_CIS_CERT_NI_NUMBERS ICCNN
195 WHERE
196 AATR.VENDOR_ID = C_REC_INFO.VENDOR_ID
197 AND AATR.CERTIFICATE_TYPE IN ('CIS4P', 'CIS4PF', 'CIS4T', 'CIS4TF', 'CIS5', 'CIS5F', 'CIS6', 'CIS6F')
198 AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(START_DATE, SYSDATE))
199 AND TRUNC(NVL(END_DATE, SYSDATE))
200 AND AATR.CERTIFICATE_NUMBER IS NOT NULL
201 AND AATR.TAX_RATE_ID = ICCNN.TAX_RATE_ID (+);
202 EXCEPTION
203 When NO_DATA_FOUND THEN
204 TEMP_COUNT2:=0;
205 END;
206 IF TEMP_COUNT2 = 0 THEN
207 WRITE_REPORT('IGI_CIS2007_UPG_NO_UTR');
208 ELSIF TEMP_COUNT2 > 1 THEN
209 --There are multiple active certificates
210 WRITE_REPORT('IGI_CIS2007_UPG_MULTIPLE_UTR');
211 ELSE
212 --Use the UTR present in the Active Certificate
213 SELECT
214 DISTINCT(SUBSTR (AATR.CERTIFICATE_NUMBER, 1,10)) INTO TEMP_UTR_NO
215 FROM
216 AP_AWT_TAX_RATES AATR, IGI_CIS_CERT_NI_NUMBERS ICCNN
217 WHERE
218 AATR.VENDOR_ID = C_REC_INFO.VENDOR_ID
219 AND AATR.CERTIFICATE_TYPE IN ('CIS4P', 'CIS4PF', 'CIS4T', 'CIS4TF', 'CIS5', 'CIS5F', 'CIS6', 'CIS6F')
220 AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(AATR.START_DATE, SYSDATE))
221 AND TRUNC(NVL(AATR.END_DATE, SYSDATE))
222 AND AATR.CERTIFICATE_NUMBER IS NOT NULL
223 AND AATR.TAX_RATE_ID = ICCNN.TAX_RATE_ID (+);
224
225 IF IGI_CIS_VALIDATE_UTR (TEMP_UTR_NO) THEN
226
227 IF TEMP_VENDOR_TYPE = 'PARTNERSHIP' THEN
228 UPDATE AP_SUPPLIERS
229 SET PARTNERSHIP_UTR = TO_NUMBER (TEMP_UTR_NO)
230 WHERE VENDOR_ID = C_REC_INFO.VENDOR_ID;
231 ELSE
232 UPDATE AP_SUPPLIERS
233 SET UNIQUE_TAX_REFERENCE_NUM = TO_NUMBER(TEMP_UTR_NO)
234 WHERE VENDOR_ID = C_REC_INFO.VENDOR_ID;
235 END IF;
236 IF SQL%FOUND THEN
237 WRITE_REPORT('IGI_CIS2007_UPG_UTR');
238 END IF;
239
240 ELSE
241 WRITE_REPORT('IGI_CIS2007_UPG_INVALID_UTR');
242 END IF;
243 END IF;
244 ELSE
245 SELECT
246 DISTINCT(SUBSTR (AATR.CERTIFICATE_NUMBER, 1,10)) INTO TEMP_UTR_NO
247 FROM
248 AP_AWT_TAX_RATES AATR, IGI_CIS_CERT_NI_NUMBERS ICCNN
249 WHERE
250 VENDOR_ID = C_REC_INFO.VENDOR_ID
251 AND AATR.CERTIFICATE_TYPE IN ('CIS4P', 'CIS4PF', 'CIS4T', 'CIS4TF', 'CIS5', 'CIS5F', 'CIS6', 'CIS6F')
252 -- AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(START_DATE, SYSDATE)) bug 5620412
253 -- AND TRUNC(NVL(END_DATE, SYSDATE)) bug 5620412
254 AND AATR.CERTIFICATE_NUMBER IS NOT NULL
255 AND AATR.TAX_RATE_ID = ICCNN.TAX_RATE_ID (+);
256
257 IF IGI_CIS_VALIDATE_UTR (TEMP_UTR_NO) THEN
258
259 IF TEMP_VENDOR_TYPE = 'PARTNERSHIP' THEN
260 UPDATE AP_SUPPLIERS
261 SET PARTNERSHIP_UTR = TO_NUMBER (TEMP_UTR_NO)
262 WHERE VENDOR_ID = C_REC_INFO.VENDOR_ID;
263 ELSE
264 UPDATE AP_SUPPLIERS
265 SET UNIQUE_TAX_REFERENCE_NUM = TO_NUMBER(TEMP_UTR_NO)
266 WHERE VENDOR_ID = C_REC_INFO.VENDOR_ID;
267 END IF;
268 IF SQL%FOUND THEN
269 WRITE_REPORT('IGI_CIS2007_UPG_UTR');
270 END IF;
271
272 ELSE
273 WRITE_REPORT('IGI_CIS2007_UPG_INVALID_UTR');
274 END IF;
275
276 END IF;
277 FND_FILE.NEW_LINE (FND_FILE.OUTPUT,2);
278 END LOOP;
279
280 EXCEPTION
281 WHEN OTHERS THEN
282 P_RETCODE := 2;
283 P_ERRBUFF := 'ERROR MESSAGE: ' || SQLERRM || ' ERROR CODE: ' || TO_CHAR(SQLCODE);
284
285 End MIGRATE_DATA;
286
287
288 Function IGI_CIS_VALIDATE_NI_NUMBER (P_NINO IN VARCHAR2)
289 Return Boolean
290 Is
291 Begin
292
293 -- =========== Validate the NI Number =================
294
295 IF SUBSTR (P_NINO, 1,1) >= 'A' AND
296 SUBSTR (P_NINO, 1,1) <= 'Z' AND
297 SUBSTR (P_NINO, 1,1) NOT IN ('D','F','I','Q','U','V') AND
298 SUBSTR (P_NINO, 2,1) >= 'A' AND
299 SUBSTR (P_NINO, 2,1) <= 'Z' AND
300 SUBSTR (P_NINO, 2,1) NOT IN ('D','F','I','O','Q','U','V') AND
301 SUBSTR (P_NINO, 3,1) >= '0' AND
302 SUBSTR (P_NINO, 3,1) <= '9' AND
303 SUBSTR (P_NINO, 4,1) >= '0' AND
304 SUBSTR (P_NINO, 4,1) <= '9' AND
305 SUBSTR (P_NINO, 5,1) >= '0' AND
306 SUBSTR (P_NINO, 5,1) <= '9' AND
307 SUBSTR (P_NINO, 6,1) >= '0' AND
308 SUBSTR (P_NINO, 6,1) <= '9' AND
309 SUBSTR (P_NINO, 7,1) >= '0' AND
310 SUBSTR (P_NINO, 7,1) <= '9' AND
311 SUBSTR (P_NINO, 8,1) >= '0' AND
312 SUBSTR (P_NINO, 8,1) <= '9'
313 THEN
314 IF LENGTH (P_NINO) = 8 THEN
315 Return TRUE;
316 ELSIF LENGTH (P_NINO) = 9 AND SUBSTR (P_NINO, 9,1) IN ('A','B','C','D') THEN
317 Return TRUE;
318 ELSE
319 Return FALSE;
320 End If;
321 ELSE
322 Return FALSE;
323 END IF;
324 END IGI_CIS_VALIDATE_NI_NUMBER;
325
326
327 Function IGI_CIS_VALIDATE_UTR (P_UTR IN VARCHAR2)
328 Return Boolean
329 Is
330 l_temp Number;
331 Begin
332
333 -- =========== Validate the UTR Number =================
334
335 FOR I in 1.. LENGTH (P_UTR)
336 LOOP
337 IF SUBSTR (P_UTR, I, 1) >= '0' AND SUBSTR (P_UTR, I, 1) <= '9' THEN
338 NULL;
339 ELSE
340 Return FALSE;
341 EXIT;
342 END IF;
343 END LOOP;
344
345 l_temp := MOD (
346 TO_NUMBER (SUBSTR (P_UTR, 2,1)) * 6 +
347 TO_NUMBER (SUBSTR (P_UTR, 3,1)) * 7 +
348 TO_NUMBER (SUBSTR (P_UTR, 4,1)) * 8 +
349 TO_NUMBER (SUBSTR (P_UTR, 5,1)) * 9 +
350 TO_NUMBER (SUBSTR (P_UTR, 6,1)) * 10 +
351 TO_NUMBER (SUBSTR (P_UTR, 7,1)) * 5 +
352 TO_NUMBER (SUBSTR (P_UTR, 8,1)) * 4 +
353 TO_NUMBER (SUBSTR (P_UTR, 9,1)) * 3 +
354 TO_NUMBER (SUBSTR (P_UTR, 10,1)) * 2
355 , 11);
356
357 l_temp := 11 - l_temp;
358
359 IF l_temp > 9 THEN
360 l_temp := l_temp - 9;
361 END IF;
362
363 IF l_temp = TO_NUMBER (SUBSTR (P_UTR, 1,1)) THEN
364 Return TRUE;
365 ELSE
366 Return FALSE;
367 END IF;
368
369 END IGI_CIS_VALIDATE_UTR;
370
371 PROCEDURE WRITE_REPORT(P_MSG_NAME IN VARCHAR2) IS
372 BEGIN
373 FND_MESSAGE.SET_NAME('IGI',P_MSG_NAME);
374 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,FND_MESSAGE.GET);
375 END WRITE_REPORT;
376
377 END IGI_CIS_UPGRADE_PKG;
378