[Home] [Help]
PACKAGE BODY: APPS.PER_US_VISA_MIGR
Source
1 PACKAGE BODY PER_US_VISA_MIGR AS
2 /* $Header: peusvsmi.pkb 120.0 2005/05/31 22:45:57 appldev noship $ */
3 procedure convert_visa(p_bg_id number)
4 is
5 --
6 -- Local Variables
7 --
8 l_cnt number;
9 l_codes_len number;
10 l_comma_all number;
11 l_orig_pos number;
12 l_orig_num number;
13 l_new_start number;
14 l_new_end number;
15 l_new_num number;
16 l_new_code varchar2(20);
17 l_category varchar2(2);
18 --
19 l_counter number := 0;
20 l_person_id number := 0;
21 l_business_group_id number := 0;
22 l_full_name varchar2(240);
23 l_start_date date;
24 l_per_information4 varchar2(150);
25 --
26 l_orig_codes varchar2(300);
27 l_new_codes varchar2(300);
28 l_codes_left varchar2(300);
29 --
30 CURSOR per_visa_exists(cp_bgid number) IS
31 SELECT DISTINCT full_name,
32 person_id,
33 start_date,
34 per_information4,
35 business_group_id
36 FROM per_all_people_f
37 WHERE per_information4 IS NOT NULL
38 AND business_group_id = cp_bgid
39 AND effective_end_date = to_date('4712/12/31','YYYY/MM/DD')
40 ORDER BY full_name;
41 --
42
43 begin
44 l_orig_codes := 'A1,A2,A3,ASLM,B1,B2,C1,C2,C3,' ||
45 'CPO,CPR,D1,E1,E2,F1,F2,G1,G2,G3,G4,' ||
46 'H1,H1B,H2A,H2B,H3,H4,H1B3,H1B4,H1B5,' ||
47 'I,K1,L1,L2,PR,M1,M2,N8,N9,NATO,' ||
48 'O1,O2,O3,P1,P2,P3,Q,Q1,R1,R2,RFGE,' ||
49 'SK1,SK2,SK3,TC,VWB,VWT,';
50 l_new_codes := 'A-1,A-2,A-3,ASLM,B-1,B-2,C-1,C-2,C-3,' ||
51 'CPO,CPR,D-1,E-1,E-2,F-1,F-2,G-1,G-2,G-3,G-4,' ||
52 'H-1A,H-1B,H-2A,H-2B,H-3,H-4,P-2,P-1,P-1,' ||
53 'I,K-1,L-1,L-2,LPR,M-1,M-2,N-8,N-9,NATO,' ||
54 'O-1,O-2,O-3,P-1,P-2,P-3,Q-1,*Q1,R-1,R-2,RFGE,' ||
55 'SK-1,SK-2,SK-3,TN,VW,VW,';
56 --
57 open per_visa_exists(p_bg_id);
58 LOOP
59 FETCH per_visa_exists INTO l_full_name,
60 l_person_id,
61 l_start_date,
62 l_per_information4,
63 l_business_group_id;
64 IF per_visa_exists%NOTFOUND THEN
65 EXIT;
66 END IF;
67 --
68 -- J code processing
69 --
70 l_new_code := '';
71 l_category := '';
72 IF LENGTH(l_per_information4)>=2
73 AND SUBSTR(l_per_information4,1,1) = 'J' THEN
74 l_new_code := SUBSTR(l_per_information4,1,1) || '-' ||
75 SUBSTR(l_per_information4,2,1);
76 IF LENGTH(l_per_information4) = 4 THEN
77 l_category := SUBSTR(l_per_information4,3,2);
78 END IF;
79 END IF;
80 --
81 l_orig_pos := INSTR(l_orig_codes,RTRIM(l_per_information4),1);
82 IF l_orig_pos > 0 THEN
83 IF l_orig_pos = 1 THEN
84 l_orig_num := 1;
85 ELSE
86 l_codes_left := SUBSTR(l_orig_codes,1,l_orig_pos-1);
87 l_codes_len := LENGTH(l_codes_left);
88 l_cnt := 1;
89 l_comma_all := 0;
90 WHILE l_cnt <= l_codes_len
91 LOOP
92 IF SUBSTR(l_orig_codes,l_cnt,1) = ',' THEN
93 l_comma_all := l_comma_all + 1;
94 END IF;
95 l_cnt := l_cnt + 1;
96 END LOOP;
97 l_orig_num := l_comma_all + 1;
98 END IF;
99 --
100 l_new_num := l_orig_num;
101 l_new_code := '';
102 --
103 IF l_new_num = 1 THEN
104 l_new_start := 1;
105 l_new_end := INSTR(l_new_codes,',',1,1) - 1;
106 ELSE
107 l_new_start := INSTR(l_new_codes,',',1,l_new_num-1) + 1;
108 l_new_end := INSTR(l_new_codes,',',1,l_new_num) - 1;
109 END IF;
110 --
111 l_new_code := SUBSTR(l_new_codes,l_new_start,l_new_end-l_new_start+1);
112 --
113 END IF;
114 --
115 -- Insert statement here
116 --
117 IF l_new_code IS NOT NULL THEN
118 INSERT INTO per_people_extra_info
119 (person_extra_info_id,
120 person_id,
121 information_type,
122 pei_information_category,
123 pei_information5,
124 pei_information9,
125 object_version_number,
126 last_update_date,
127 creation_date)
128 SELECT
129 per_people_extra_info_s.nextval,
130 l_person_id,
131 'PER_US_VISA_DETAILS',
132 'PER_US_VISA_DETAILS',
133 l_new_code,
134 l_category,
135 1,
136 sysdate,
137 sysdate
138 FROM sys.dual
139 WHERE NOT EXISTS
140 (SELECT 1
141 FROM PER_PEOPLE_EXTRA_INFO
142 WHERE person_id = l_person_id
143 AND pei_information5 = l_new_code
144 AND information_type = 'PER_US_VISA_DETAILS'
145 AND pei_information_category = 'PER_US_VISA_DETAILS');
146 l_counter := l_counter+1;
147 if MOD(l_counter,50) = 0 then
148 commit;
149 end if;
150 END IF;
151 END LOOP;
152 CLOSE per_visa_exists;
153 COMMIT;
154 end convert_visa;
155 --
156 END PER_US_VISA_MIGR;