[Home] [Help]
PACKAGE BODY: APPS.HR_ORG_INFO_TYPES_BY_CLASS_PKG
Source
1 package body HR_ORG_INFO_TYPES_BY_CLASS_PKG as
2 /* $Header: hroiclct.pkb 120.5 2011/04/28 18:38:21 sidsaxen ship $ */
3 procedure OWNER_TO_WHO (
4 X_OWNER in VARCHAR2,
5 X_CREATION_DATE out nocopy DATE,
6 X_CREATED_BY out nocopy NUMBER,
7 X_LAST_UPDATE_DATE out nocopy DATE,
8 X_LAST_UPDATED_BY out nocopy NUMBER,
9 X_LAST_UPDATE_LOGIN out nocopy NUMBER
10 ) is
11 begin
12 if X_OWNER = 'SEED' then
13 X_CREATED_BY := 1;
14 X_LAST_UPDATED_BY := 1;
15 else
16 X_CREATED_BY := 0;
17 X_LAST_UPDATED_BY := 0;
18 end if;
19 X_CREATION_DATE := sysdate;
20 X_LAST_UPDATE_DATE := sysdate;
21 X_LAST_UPDATE_LOGIN := 0;
22 end OWNER_TO_WHO;
23 procedure INSERT_ROW (
24 X_ROWID in out nocopy VARCHAR2,
25 X_ORG_CLASSIFICATION in VARCHAR2,
26 X_ORG_INFORMATION_TYPE in VARCHAR2,
27 X_MANDATORY_FLAG in VARCHAR2,
28 X_ENABLED_FLAG in VARCHAR2,
29 X_CREATED_BY in NUMBER,
30 X_CREATION_DATE in DATE,
31 X_LAST_UPDATE_DATE in DATE,
32 X_LAST_UPDATED_BY in NUMBER,
33 X_LAST_UPDATE_LOGIN in NUMBER
34 ) is
35 cursor C is select ROWID from HR_ORG_INFO_TYPES_BY_CLASS
36 where ORG_CLASSIFICATION = X_ORG_CLASSIFICATION
37 and ORG_INFORMATION_TYPE = X_ORG_INFORMATION_TYPE
38 ;
39 begin
40
41 --
42 -- Added the following code as a part of Zero Downtime Patching Project.
43 -- Code Starts Here.
44 --
45 per_ric_pkg.Chk_integrity(
46 p_entity_name=>'HR_ORG_INFO_TYPES_BY_CLASS',
47 p_ref_entity=>'HR_ORG_INFORMATION_TYPES',
48 p_ref_column_name=>'ORG_INFORMATION_TYPE',
49 p_ref_col_value_varchar=> X_ORG_INFORMATION_TYPE,
50 p_ref_col_value_number=> NULL,
51 p_ref_col_value_date=> NULL,
52 p_ref_type=>'INS');
53 --
54 -- Code Ends Here
55 --
56
57 insert into HR_ORG_INFO_TYPES_BY_CLASS (
58 ORG_CLASSIFICATION,
59 ORG_INFORMATION_TYPE,
60 MANDATORY_FLAG,
61 ENABLED_FLAG,
62 LAST_UPDATE_DATE,
63 LAST_UPDATED_BY,
64 LAST_UPDATE_LOGIN,
65 CREATED_BY,
66 CREATION_DATE
67 ) values(
68 X_ORG_CLASSIFICATION,
69 X_ORG_INFORMATION_TYPE,
70 X_MANDATORY_FLAG,
71 X_ENABLED_FLAG,
72 X_LAST_UPDATE_DATE,
73 X_LAST_UPDATED_BY,
74 X_LAST_UPDATE_LOGIN,
75 X_CREATED_BY,
76 X_CREATION_DATE);
77
78 open c;
79 fetch c into X_ROWID;
80 if (c%notfound) then
81 close c;
82 raise no_data_found;
83 end if;
84 close c;
85
86 end INSERT_ROW;
87
88 procedure LOCK_ROW (
89 X_ORG_CLASSIFICATION in VARCHAR2,
90 X_ORG_INFORMATION_TYPE in VARCHAR2,
91 X_MANDATORY_FLAG in VARCHAR2
92 ) is
93 cursor c1 is select
94 MANDATORY_FLAG
95 from HR_ORG_INFO_TYPES_BY_CLASS
96 where ORG_CLASSIFICATION = X_ORG_CLASSIFICATION
97 and ORG_INFORMATION_TYPE = X_ORG_INFORMATION_TYPE
98 for update of ORG_CLASSIFICATION nowait;
99 begin
100 for tlinfo in c1 loop
101 if (tlinfo.MANDATORY_FLAG = X_MANDATORY_FLAG)
102 then
103 null;
104 else
105 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
106 app_exception.raise_exception;
107 end if;
108 end loop;
109 return;
110 end LOCK_ROW;
111
112 procedure UPDATE_ROW (
113 X_ORG_CLASSIFICATION in VARCHAR2,
114 X_ORG_INFORMATION_TYPE in VARCHAR2,
115 X_MANDATORY_FLAG in VARCHAR2,
116 X_ENABLED_FLAG in VARCHAR2,
117 X_LAST_UPDATE_DATE in DATE,
118 X_LAST_UPDATED_BY in NUMBER,
119 X_LAST_UPDATE_LOGIN in NUMBER
120 ) is
121 begin
122 update HR_ORG_INFO_TYPES_BY_CLASS set
123 MANDATORY_FLAG = X_MANDATORY_FLAG,
124 ENABLED_FLAG = X_ENABLED_FLAG,
125 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
126 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
127 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
128 where ORG_CLASSIFICATION = X_ORG_CLASSIFICATION
129 and ORG_INFORMATION_TYPE = X_ORG_INFORMATION_TYPE;
130
131 if (sql%notfound) then
132 raise no_data_found;
133 end if;
134 end UPDATE_ROW;
135
136 procedure DELETE_ROW (
137 X_ORG_CLASSIFICATION in VARCHAR2,
138 X_ORG_INFORMATION_TYPE in VARCHAR2
139 ) is
140 begin
141 delete from HR_ORG_INFO_TYPES_BY_CLASS
142 where ORG_CLASSIFICATION = X_ORG_CLASSIFICATION
143 and ORG_INFORMATION_TYPE = X_ORG_INFORMATION_TYPE;
144
145 if (sql%notfound) then
146 raise no_data_found;
147 end if;
148
149 end DELETE_ROW;
150
151 procedure LOAD_ROW(
152 X_ORG_INFORMATION_TYPE IN VARCHAR2,
153 X_ORG_CLASSIFICATION IN VARCHAR2,
154 X_MANDATORY_FLAG IN VARCHAR2,
155 X_ENABLED_FLAG IN VARCHAR2 default 'Y',
156 X_OWNER IN VARCHAR2,
157 X_LAST_UPDATE_DATE IN VARCHAR2 default sysdate,
158 X_CUSTOM_MODE IN VARCHAR2 default null
159 ) is
160 X_ROWID ROWID;
161 X_CREATION_DATE DATE;
162 X_CREATED_BY NUMBER;
163 X_LAST_UPDATED_BY NUMBER;
164 X_LAST_UPDATE_LOGIN NUMBER;
165 l_enabled_flag varchar2(1):=nvl(X_ENABLED_FLAG,'Y');
166 f_luby number; -- entity owner in file
167 f_ludate date; -- entity update date in file
168 db_luby number; -- entity owner in db
169 db_ludate date; -- entity update date in db
170 begin
171 -- Translate owner to file_last_updated_by
172 f_luby := fnd_load_util.owner_id(X_OWNER);
173 -- Translate char last_update_date to date
174 f_ludate := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
175 select LAST_UPDATED_BY, LAST_UPDATE_DATE
176 into db_luby, db_ludate
177 from HR_ORG_INFO_TYPES_BY_CLASS
178 where ORG_INFORMATION_TYPE = X_ORG_INFORMATION_TYPE
179 and ORG_CLASSIFICATION=X_ORG_CLASSIFICATION;
180
181 -- Test for customization and version
182 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
183 db_ludate, X_CUSTOM_MODE)) then
184 -- Update existing row
185
186 UPDATE_ROW (
187 X_ORG_CLASSIFICATION ,
188 X_ORG_INFORMATION_TYPE ,
189 X_MANDATORY_FLAG ,
190 l_enabled_flag,
191 f_ludate ,
192 f_luby ,
193 0 );
194 END IF;
195 exception
196 when no_data_found then
197 INSERT_ROW (
198 X_ROWID ,
199 X_ORG_CLASSIFICATION ,
200 X_ORG_INFORMATION_TYPE ,
201 X_MANDATORY_FLAG ,
202 l_enabled_flag,
203 f_luby ,
204 f_ludate ,
205 f_ludate ,
206 f_luby ,
207 0 );
208 end LOAD_ROW;
209 end HR_ORG_INFO_TYPES_BY_CLASS_PKG;