[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.3.12000000.1 2007/01/21 17:34:47 appldev 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 insert into HR_ORG_INFO_TYPES_BY_CLASS (
41 ORG_CLASSIFICATION,
42 ORG_INFORMATION_TYPE,
43 MANDATORY_FLAG,
44 ENABLED_FLAG,
45 LAST_UPDATE_DATE,
46 LAST_UPDATED_BY,
47 LAST_UPDATE_LOGIN,
48 CREATED_BY,
49 CREATION_DATE
50 ) values(
51 X_ORG_CLASSIFICATION,
52 X_ORG_INFORMATION_TYPE,
53 X_MANDATORY_FLAG,
54 X_ENABLED_FLAG,
55 X_LAST_UPDATE_DATE,
56 X_LAST_UPDATED_BY,
57 X_LAST_UPDATE_LOGIN,
58 X_CREATED_BY,
59 X_CREATION_DATE);
60
61 open c;
62 fetch c into X_ROWID;
63 if (c%notfound) then
64 close c;
65 raise no_data_found;
66 end if;
67 close c;
68
69 end INSERT_ROW;
70
71 procedure LOCK_ROW (
72 X_ORG_CLASSIFICATION in VARCHAR2,
73 X_ORG_INFORMATION_TYPE in VARCHAR2,
74 X_MANDATORY_FLAG in VARCHAR2
75 ) is
76 cursor c1 is select
77 MANDATORY_FLAG
78 from HR_ORG_INFO_TYPES_BY_CLASS
79 where ORG_CLASSIFICATION = X_ORG_CLASSIFICATION
80 and ORG_INFORMATION_TYPE = X_ORG_INFORMATION_TYPE
81 for update of ORG_CLASSIFICATION nowait;
82 begin
83 for tlinfo in c1 loop
84 if (tlinfo.MANDATORY_FLAG = X_MANDATORY_FLAG)
85 then
86 null;
87 else
88 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
89 app_exception.raise_exception;
90 end if;
91 end loop;
92 return;
93 end LOCK_ROW;
94
95 procedure UPDATE_ROW (
96 X_ORG_CLASSIFICATION in VARCHAR2,
97 X_ORG_INFORMATION_TYPE in VARCHAR2,
98 X_MANDATORY_FLAG in VARCHAR2,
99 X_ENABLED_FLAG in VARCHAR2,
100 X_LAST_UPDATE_DATE in DATE,
101 X_LAST_UPDATED_BY in NUMBER,
102 X_LAST_UPDATE_LOGIN in NUMBER
103 ) is
104 begin
105 update HR_ORG_INFO_TYPES_BY_CLASS set
106 MANDATORY_FLAG = X_MANDATORY_FLAG,
107 ENABLED_FLAG = X_ENABLED_FLAG,
108 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
109 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
110 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
111 where ORG_CLASSIFICATION = X_ORG_CLASSIFICATION
112 and ORG_INFORMATION_TYPE = X_ORG_INFORMATION_TYPE;
113
114 if (sql%notfound) then
115 raise no_data_found;
116 end if;
117 end UPDATE_ROW;
118
119 procedure DELETE_ROW (
120 X_ORG_CLASSIFICATION in VARCHAR2,
121 X_ORG_INFORMATION_TYPE in VARCHAR2
122 ) is
123 begin
124 delete from HR_ORG_INFO_TYPES_BY_CLASS
125 where ORG_CLASSIFICATION = X_ORG_CLASSIFICATION
126 and ORG_INFORMATION_TYPE = X_ORG_INFORMATION_TYPE;
127
128 if (sql%notfound) then
129 raise no_data_found;
130 end if;
131
132 end DELETE_ROW;
133
134 procedure LOAD_ROW(
135 X_ORG_INFORMATION_TYPE IN VARCHAR2,
136 X_ORG_CLASSIFICATION IN VARCHAR2,
137 X_MANDATORY_FLAG IN VARCHAR2,
138 X_ENABLED_FLAG IN VARCHAR2 default 'Y',
139 X_OWNER IN VARCHAR2,
140 X_LAST_UPDATE_DATE IN VARCHAR2 default sysdate,
141 X_CUSTOM_MODE IN VARCHAR2 default null
142 ) is
143 X_ROWID ROWID;
144 X_CREATION_DATE DATE;
145 X_CREATED_BY NUMBER;
146 X_LAST_UPDATED_BY NUMBER;
147 X_LAST_UPDATE_LOGIN NUMBER;
148 l_enabled_flag varchar2(1):=nvl(X_ENABLED_FLAG,'Y');
149 f_luby number; -- entity owner in file
150 f_ludate date; -- entity update date in file
151 db_luby number; -- entity owner in db
152 db_ludate date; -- entity update date in db
153 begin
154 -- Translate owner to file_last_updated_by
155 f_luby := fnd_load_util.owner_id(X_OWNER);
156 -- Translate char last_update_date to date
157 f_ludate := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
158 select LAST_UPDATED_BY, LAST_UPDATE_DATE
159 into db_luby, db_ludate
160 from HR_ORG_INFO_TYPES_BY_CLASS
161 where ORG_INFORMATION_TYPE = X_ORG_INFORMATION_TYPE
162 and ORG_CLASSIFICATION=X_ORG_CLASSIFICATION;
163
164 -- Test for customization and version
165 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
166 db_ludate, X_CUSTOM_MODE)) then
167 -- Update existing row
168
169 UPDATE_ROW (
170 X_ORG_CLASSIFICATION ,
171 X_ORG_INFORMATION_TYPE ,
172 X_MANDATORY_FLAG ,
173 l_enabled_flag,
174 f_ludate ,
175 f_luby ,
176 0 );
177 END IF;
178 exception
179 when no_data_found then
180 INSERT_ROW (
181 X_ROWID ,
182 X_ORG_CLASSIFICATION ,
183 X_ORG_INFORMATION_TYPE ,
184 X_MANDATORY_FLAG ,
185 l_enabled_flag,
186 f_luby ,
187 f_ludate ,
188 f_ludate ,
189 f_luby ,
190 0 );
191 end LOAD_ROW;
192 end HR_ORG_INFO_TYPES_BY_CLASS_PKG;