[Home] [Help]
PACKAGE BODY: APPS.AMS_IMP_COL_MAPPING_PKG
Source
1 package body AMS_IMP_COL_MAPPING_PKG as
2 /* $Header: amslccmb.pls 115.7 2004/04/07 21:22:10 usingh ship $ */
3 AMS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
4 AMS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
5 AMS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
6
7 procedure INSERT_ROW (
8 X_ROWID in OUT NOCOPY VARCHAR2,
9 X_COL_MAPPING_ID in NUMBER,
10 X_TABLE_NAME in VARCHAR2,
11 X_COLUMN_NAME in VARCHAR2,
12 X_MEANING in VARCHAR2,
13 X_REQUIRED_FLAG in VARCHAR2,
14 X_TARGET_TABLE_NAME in VARCHAR2,
15 X_CREATION_DATE in DATE,
16 X_CREATED_BY in NUMBER,
17 X_LAST_UPDATE_DATE in DATE,
18 X_LAST_UPDATED_BY in NUMBER,
19 X_LAST_UPDATE_LOGIN in NUMBER
20 ) is
21 begin
22 insert into AMS_IMP_COL_MAPPING (
23 TABLE_NAME,
24 COLUMN_NAME,
25 MEANING,
26 REQUIRED_FLAG,
27 TARGET_TABLE_NAME,
28 CREATION_DATE,
29 CREATED_BY,
30 LAST_UPDATE_LOGIN,
31 COL_MAPPING_ID,
32 LAST_UPDATE_DATE,
33 LAST_UPDATED_BY
34 )
35 values
36 (
37 X_TABLE_NAME,
38 X_COLUMN_NAME,
39 X_MEANING,
40 X_REQUIRED_FLAG,
41 X_TARGET_TABLE_NAME,
42 X_CREATION_DATE,
43 X_CREATED_BY,
44 X_LAST_UPDATE_LOGIN,
45 X_COL_MAPPING_ID,
46 X_LAST_UPDATE_DATE,
47 X_LAST_UPDATED_BY
48 );
49
50 end INSERT_ROW;
51
52 procedure LOCK_ROW (
53 X_COL_MAPPING_ID in NUMBER,
54 X_TABLE_NAME in VARCHAR2,
55 X_COLUMN_NAME in VARCHAR2,
56 X_MEANING in VARCHAR2,
57 X_REQUIRED_FLAG in VARCHAR2,
58 X_TARGET_TABLE_NAME in VARCHAR2
59 ) is
60 cursor c1 is select
61 TABLE_NAME,
62 COLUMN_NAME,
63 MEANING,
64 REQUIRED_FLAG,
65 TARGET_TABLE_NAME,
66 COL_MAPPING_ID
67 from AMS_IMP_COL_MAPPING
68 where COL_MAPPING_ID = X_COL_MAPPING_ID
69 for update of COL_MAPPING_ID nowait;
70 begin
71 for tlinfo in c1 loop
72 if ( (tlinfo.COL_MAPPING_ID = X_COL_MAPPING_ID)
73 AND (tlinfo.TABLE_NAME = X_TABLE_NAME)
74 AND (tlinfo.COLUMN_NAME = X_COLUMN_NAME)
75 AND (tlinfo.MEANING = X_MEANING)
76 AND ((tlinfo.REQUIRED_FLAG = X_REQUIRED_FLAG)
77 OR ((tlinfo.REQUIRED_FLAG is null) AND (X_REQUIRED_FLAG is null)))
78 AND (tlinfo.TARGET_TABLE_NAME = X_TARGET_TABLE_NAME)
79 ) then
80 null;
81 else
82 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
83 app_exception.raise_exception;
84 end if;
85 end loop;
86 return;
87 end LOCK_ROW;
88
89 procedure UPDATE_ROW (
90 X_COL_MAPPING_ID in NUMBER,
91 X_TABLE_NAME in VARCHAR2,
92 X_COLUMN_NAME in VARCHAR2,
93 X_MEANING in VARCHAR2,
94 X_REQUIRED_FLAG in VARCHAR2,
95 X_TARGET_TABLE_NAME in VARCHAR2,
96 X_LAST_UPDATE_DATE in DATE,
97 X_LAST_UPDATED_BY in NUMBER,
98 X_LAST_UPDATE_LOGIN in NUMBER
99 ) is
100 begin
101 update AMS_IMP_COL_MAPPING set
102 TABLE_NAME = X_TABLE_NAME,
103 COLUMN_NAME = X_COLUMN_NAME,
104 MEANING = X_MEANING,
105 REQUIRED_FLAG = X_REQUIRED_FLAG,
106 TARGET_TABLE_NAME = X_TARGET_TABLE_NAME,
107 COL_MAPPING_ID = X_COL_MAPPING_ID,
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 COL_MAPPING_ID = X_COL_MAPPING_ID;
112
113 if (sql%notfound) then
114 raise no_data_found;
115 end if;
116 end UPDATE_ROW;
117
118 procedure DELETE_ROW (
119 X_COL_MAPPING_ID in NUMBER
120 ) is
121 begin
122 delete from AMS_IMP_COL_MAPPING
123 where COL_MAPPING_ID = X_COL_MAPPING_ID;
124
125 if (sql%notfound) then
126 raise no_data_found;
127 end if;
128
129 end DELETE_ROW;
130
131 procedure LOAD_ROW (
132 X_COL_MAPPING_ID in NUMBER,
133 X_TABLE_NAME in VARCHAR2,
134 X_COLUMN_NAME in VARCHAR2,
135 X_MEANING in VARCHAR2,
136 X_REQUIRED_FLAG in VARCHAR2,
137 X_TARGET_TABLE_NAME in VARCHAR2,
138 X_OWNER in VARCHAR2,
139 X_CREATION_DATE in DATE,
140 X_CREATED_BY in NUMBER,
141 X_LAST_UPDATE_DATE in DATE,
142 X_LAST_UPDATED_BY in NUMBER,
143 X_LAST_UPDATE_LOGIN in NUMBER
144 ) IS
145
146 l_user_id number := 0;
147 l_colmap_id number;
148 l_obj_verno number;
149 l_dummy_char varchar2(1);
150 l_row_id varchar2(100);
151
152 cursor c_chk_col_exists is
153 select 'x'
154 from AMS_IMP_COL_MAPPING
155 where COL_MAPPING_ID = X_COL_MAPPING_ID;
156
157 cursor c_get_col_mapping_id is
158 select AMS_IMP_COL_MAPPING_s.nextval
159 from dual;
160
161 BEGIN
162 if X_OWNER = 'SEED' then
163 l_user_id := 1;
164 end if;
165 open c_chk_col_exists;
166 fetch c_chk_col_exists into l_dummy_char;
167 if c_chk_col_exists%notfound
168 then
169 close c_chk_col_exists;
170 if X_COL_MAPPING_ID is null
171 then
172 open c_get_col_mapping_id;
173 fetch c_get_col_mapping_id into l_colmap_id;
174 close c_get_col_mapping_id;
175 else
176 l_colmap_id := X_COL_MAPPING_ID;
177 end if;
178 AMS_IMP_COL_MAPPING_PKG.INSERT_ROW (
179 X_ROWID => l_row_id,
180 X_COL_MAPPING_ID => X_COL_MAPPING_ID,
181 X_TABLE_NAME => X_TABLE_NAME,
182 X_COLUMN_NAME => X_COLUMN_NAME,
183 X_MEANING => X_MEANING,
184 X_REQUIRED_FLAG => X_REQUIRED_FLAG,
185 X_TARGET_TABLE_NAME => X_TARGET_TABLE_NAME,
186 X_CREATION_DATE => X_CREATION_DATE , -- sysdate,
187 X_CREATED_BY => l_user_id,
188 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE, -- sysdate,
189 X_LAST_UPDATED_BY => l_user_id,
190 X_LAST_UPDATE_LOGIN => 1);
191
192 else
193 close c_chk_col_exists;
194 l_colmap_id := X_COL_MAPPING_ID ;
195
196 AMS_IMP_COL_MAPPING_PKG.UPDATE_ROW (
197 X_COL_MAPPING_ID => X_COL_MAPPING_ID,
198 X_TABLE_NAME => X_TABLE_NAME,
199 X_COLUMN_NAME => X_COLUMN_NAME,
200 X_MEANING => X_MEANING ,
201 X_REQUIRED_FLAG => X_REQUIRED_FLAG,
202 X_TARGET_TABLE_NAME => X_TARGET_TABLE_NAME,
203 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE, -- sysdate,
204 X_LAST_UPDATED_BY => l_user_id,
205 X_LAST_UPDATE_LOGIN => 1
206 );
207 end if;
208
209 end LOAD_ROW;
210
211 end AMS_IMP_COL_MAPPING_PKG;