[Home] [Help]
PACKAGE BODY: APPS.PAY_US_INS_ORG_INFO_PKG
Source
1 PACKAGE BODY pay_us_ins_org_info_pkg AS
2 /* $Header: pyusiorg.pkb 115.1 99/07/17 06:44:44 porting shi $ */
3 FUNCTION insert_org_information
4 ( P_ORGANIZATION_ID NUMBER
5 ,P_ORG_INFORMATION_CONTEXT VARCHAR2
6 ,P_ORG_INFORMATION1 VARCHAR2
7 ,P_ORG_INFORMATION2 VARCHAR2 DEFAULT null
8 ,P_ORG_INFORMATION3 VARCHAR2 DEFAULT null
9 ,P_ORG_INFORMATION4 VARCHAR2 DEFAULT null
10 ,P_ORG_INFORMATION5 VARCHAR2 DEFAULT null
11 ,P_ORG_INFORMATION6 VARCHAR2 DEFAULT null
12 ,P_ORG_INFORMATION7 VARCHAR2 DEFAULT null
13 ,P_ORG_INFORMATION8 VARCHAR2 DEFAULT null
14 ,P_ORG_INFORMATION9 VARCHAR2 DEFAULT null
15 ,P_ORG_INFORMATION10 VARCHAR2 DEFAULT null
16 ,P_ORG_INFORMATION11 VARCHAR2 DEFAULT null
17 ,P_ORG_INFORMATION12 VARCHAR2 DEFAULT null
18 ,P_ORG_INFORMATION13 VARCHAR2 DEFAULT null
19 ,P_ORG_INFORMATION14 VARCHAR2 DEFAULT null
20 ,P_ORG_INFORMATION15 VARCHAR2 DEFAULT null
21 ,P_ORG_INFORMATION16 VARCHAR2 DEFAULT null
22 ,P_ORG_INFORMATION17 VARCHAR2 DEFAULT null
23 ,P_ORG_INFORMATION18 VARCHAR2 DEFAULT null
24 ,P_ORG_INFORMATION19 VARCHAR2 DEFAULT null
25 ,P_ORG_INFORMATION20 VARCHAR2 DEFAULT null
26 ) return NUMBER
27 IS
28 --
29 l_org_information_id NUMBER;
30 l_organization_id NUMBER;
31 l_org_information1 VARCHAR2(150);
32 l_org_information_type VARCHAR2(40);
33 l_check VARCHAR2(1);
34 l_dummy varchar2(1);
35 --
36 begin
37 --
38 hr_utility.trace('Entered insert_org_information');
39 --
40 hr_utility.set_location('pay_us_ins_org_info_pkg.insert_org_information',1);
41 if p_organization_id IS NULL
42 OR p_org_information_context IS NULL
43 OR p_org_information1 IS NULL
44 then
45 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
46 hr_utility.set_message_token('PROCEDURE','insert_org_information');
47 hr_utility.set_message_token('STEP','1');
48 hr_utility.raise_error;
49 end if;
50 --
51 hr_utility.set_location('pay_us_ins_org_info_pkg.insert_org_information',5);
52 SELECT organization_id
53 INTO l_organization_id
54 FROM hr_all_organization_units
55 WHERE organization_id = p_organization_id;
56 --
57 hr_utility.set_location('pay_us_ins_org_info_pkg.insert_org_information',8);
58 if p_org_information_context = 'CLASS' then
59 SELECT lookup_code
60 INTO l_org_information1
61 FROM hr_lookups
62 WHERE lookup_type = 'ORG_CLASS'
63 AND lookup_code = p_org_information1;
64 --
65 l_org_information_type := p_org_information_context;
66 else
67 hr_utility.trace('Entered Else');
68 l_org_information1 := p_org_information1;
69 begin
70 --
71 --
72 hr_utility.trace('p_org_information1 '||p_org_information1);
73 hr_utility.trace('p_org_information_context '||p_org_information_context);
74 hr_utility.trace('l_organization_id '||to_char(l_organization_id));
75 --
76 --
77 hr_utility.set_location('pay_us_ins_org_info_pkg.insert_org_information',10);
78 SELECT 'x' INTO l_dummy from dual;
79 -- SELECT oit.org_information_type
80 -- INTO l_org_information_type
81 -- FROM hr_organization_information hoi
82 -- , hr_org_info_types_by_class tbc
83 -- , hr_org_information_types oit
84 -- WHERE oit.org_information_type = p_org_information_context
85 -- AND oit.org_information_type = tbc.org_information_type
86 -- AND tbc.org_classification = hoi.org_information1
87 -- AND hoi.org_information_context = 'CLASS'
88 -- AND hoi.organization_id = l_organization_id;
89 --
90 hr_utility.trace('l_org_information_type '||l_org_information_type);
91 exception when NO_DATA_FOUND then
92 hr_utility.trace('In No Data Found');
93 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
94 hr_utility.set_message_token('PROCEDURE','insert_org_information');
95 hr_utility.set_message_token('STEP','10');
96 hr_utility.raise_error;
97 end;
98 --
99 end if;
100 --
101 begin
102 hr_utility.set_location('pay_us_ins_org_info_pkg.insert_org_information',15);
103 SELECT 'Y'
104 INTO l_check
105 FROM hr_organization_information
106 WHERE organization_id = l_organization_id
107 AND org_information_context = l_org_information_type
108 AND org_information1 = l_org_information1;
109 --
110 if l_check = 'Y' then
111 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
112 hr_utility.set_message_token('PROCEDURE','insert_org_information');
113 hr_utility.set_message_token('STEP','15');
114 hr_utility.raise_error;
115 end if;
116 --
117 exception when NO_DATA_FOUND then null;
118 end;
119 --
120 hr_utility.set_location('pay_us_ins_org_info_pkg.insert_org_information',20);
121 SELECT hr_organization_information_s.nextval
122 INTO l_org_information_id
123 FROM sys.dual;
124 --
125 -- hr_utility.trace('l_org_information_id is ' || to_char(l_org_information_id));
126 -- hr_utility.trace('p_organization_id is ' || to_char(p_organization_id));
127 hr_utility.trace('l_organization_id is ' || to_char(l_organization_id));
128 hr_utility.trace('l_org_information1 is ' || l_org_information1);
129 hr_utility.trace('l_org_information_type is ' || l_org_information_type);
130 hr_utility.trace('l_check is ' || l_check);
131 hr_utility.set_location('pay_us_ins_org_info_pkg.insert_org_information',25);
132 INSERT INTO HR_ORGANIZATION_INFORMATION
133 (org_information_id
134 ,organization_id
135 ,attribute_category
136 ,org_information_context
137 ,org_information1
138 ,org_information2
139 ,org_information3
140 ,org_information4
141 ,org_information5
142 ,org_information6
143 ,org_information7
144 ,org_information8
145 ,org_information9
146 ,org_information10
147 ,org_information11
148 ,org_information12
149 ,org_information13
150 ,org_information14
151 ,org_information15
152 ,org_information16
153 ,org_information17
154 ,org_information18
155 ,org_information19
156 ,org_information20
157 ,last_update_date
158 ,last_updated_by
159 ,last_update_login
160 ,created_by
161 ,creation_date)
162 SELECT l_org_information_id
163 ,hou.organization_id
164 ,p_org_information_context
165 ,p_org_information_context
166 ,p_org_information1
167 ,p_org_information2
168 ,p_org_information3
169 ,p_org_information4
170 ,p_org_information5
171 ,p_org_information6
172 ,p_org_information7
173 ,p_org_information8
174 ,p_org_information9
175 ,p_org_information10
176 ,p_org_information11
177 ,p_org_information12
178 ,p_org_information13
179 ,p_org_information14
180 ,p_org_information15
181 ,p_org_information16
182 ,p_org_information17
183 ,p_org_information18
184 ,p_org_information19
185 ,p_org_information20
186 ,hou.last_update_date
187 ,hou.last_updated_by
188 ,hou.last_update_login
189 ,hou.created_by
190 ,hou.creation_date
191 FROM hr_organization_units hou
192 WHERE hou.organization_id = p_organization_id;
193 --
194 hr_utility.set_location('pay_us_ins_org_info_pkg.insert_org_information',30);
195 hr_utility.trace('before return');
196 return l_org_information_id;
197 hr_utility.trace('after return');
198 --
199 --
200 end insert_org_information;
201 --
202 --
203 end pay_us_ins_org_info_pkg;