[Home] [Help]
PACKAGE BODY: APPS.AMS_EXP_TEMPLATE_PKG
Source
1 package body AMS_EXP_TEMPLATE_PKG as
2 /* $Header: amsextmb.pls 115.3 2002/11/14 21:55:44 jieli noship $ */
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_EXP_TEMPLATE_ID in NUMBER,
10 X_SET_CLAUSE in VARCHAR2,
11 X_EXPORT_TYPE in VARCHAR2,
12 X_OBJECT_VERSION_NUMBER in NUMBER,
13 X_TEMPLATE_NAME in VARCHAR2,
14 X_SEEDED_SELECT_CLAUSE in LONG,
15 X_FROM_CLAUSE in VARCHAR2,
16 X_JOIN_CONDITION in VARCHAR2,
17 X_CREATION_DATE in DATE,
18 X_CREATED_BY in NUMBER,
19 X_LAST_UPDATE_DATE in DATE,
20 X_LAST_UPDATED_BY in NUMBER,
21 X_LAST_UPDATE_LOGIN in NUMBER
22 ) is
23 begin
24 insert into AMS_EXP_TEMPLATE (
25 SET_CLAUSE,
26 EXPORT_TYPE,
27 EXP_TEMPLATE_ID,
28 LAST_UPDATED_BY,
29 OBJECT_VERSION_NUMBER,
30 CREATED_BY,
31 LAST_UPDATE_LOGIN,
32 LAST_UPDATE_DATE,
33 CREATION_DATE,
34 TEMPLATE_NAME,
35 SEEDED_SELECT_CLAUSE,
36 FROM_CLAUSE,
37 JOIN_CONDITION
38 ) values
39 (
40 X_SET_CLAUSE,
41 X_EXPORT_TYPE,
42 X_EXP_TEMPLATE_ID,
43 X_LAST_UPDATED_BY,
44 X_OBJECT_VERSION_NUMBER,
45 X_CREATED_BY,
46 X_LAST_UPDATE_LOGIN,
47 X_LAST_UPDATE_DATE,
48 X_CREATION_DATE,
49 X_TEMPLATE_NAME,
50 X_SEEDED_SELECT_CLAUSE,
51 X_FROM_CLAUSE,
52 X_JOIN_CONDITION);
53
54 end INSERT_ROW;
55
56 procedure LOCK_ROW (
57 X_EXP_TEMPLATE_ID in NUMBER,
58 X_SET_CLAUSE in VARCHAR2,
59 X_EXPORT_TYPE in VARCHAR2,
60 X_OBJECT_VERSION_NUMBER in NUMBER,
61 X_TEMPLATE_NAME in VARCHAR2,
62 X_SEEDED_SELECT_CLAUSE in LONG,
63 X_FROM_CLAUSE in VARCHAR2,
64 X_JOIN_CONDITION in VARCHAR2
65 ) is
66 cursor c1 is select
67 SET_CLAUSE,
68 EXPORT_TYPE,
69 OBJECT_VERSION_NUMBER,
70 TEMPLATE_NAME,
71 SEEDED_SELECT_CLAUSE,
72 FROM_CLAUSE,
73 JOIN_CONDITION,
74 EXP_TEMPLATE_ID
75 from AMS_EXP_TEMPLATE
76 where EXP_TEMPLATE_ID = X_EXP_TEMPLATE_ID
77 for update of EXP_TEMPLATE_ID nowait;
78 begin
79 for tlinfo in c1 loop
80 if ( (tlinfo.EXP_TEMPLATE_ID = X_EXP_TEMPLATE_ID)
81 AND (tlinfo.SET_CLAUSE = X_SET_CLAUSE)
82 AND (tlinfo.EXPORT_TYPE = X_EXPORT_TYPE)
83 AND (tlinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
84 AND (tlinfo.TEMPLATE_NAME = X_TEMPLATE_NAME)
85 AND (tlinfo.SEEDED_SELECT_CLAUSE = X_SEEDED_SELECT_CLAUSE)
86 AND (tlinfo.FROM_CLAUSE = X_FROM_CLAUSE)
87 AND (tlinfo.JOIN_CONDITION = X_JOIN_CONDITION)
88 ) then
89 null;
90 else
91 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
92 app_exception.raise_exception;
93 end if;
94 end loop;
95 return;
96 end LOCK_ROW;
97
98 procedure UPDATE_ROW (
99 X_EXP_TEMPLATE_ID in NUMBER,
100 X_SET_CLAUSE in VARCHAR2,
101 X_EXPORT_TYPE in VARCHAR2,
102 X_OBJECT_VERSION_NUMBER in NUMBER,
103 X_TEMPLATE_NAME in VARCHAR2,
104 X_SEEDED_SELECT_CLAUSE in LONG,
105 X_FROM_CLAUSE in VARCHAR2,
106 X_JOIN_CONDITION in VARCHAR2,
107 X_LAST_UPDATE_DATE in DATE,
108 X_LAST_UPDATED_BY in NUMBER,
109 X_LAST_UPDATE_LOGIN in NUMBER
110 ) is
111 begin
112 update AMS_EXP_TEMPLATE set
113 SET_CLAUSE = X_SET_CLAUSE,
114 EXPORT_TYPE = X_EXPORT_TYPE,
115 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
116 TEMPLATE_NAME = X_TEMPLATE_NAME,
117 SEEDED_SELECT_CLAUSE = X_SEEDED_SELECT_CLAUSE,
118 FROM_CLAUSE = X_FROM_CLAUSE,
119 JOIN_CONDITION = X_JOIN_CONDITION,
120 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
121 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
122 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
123 where EXP_TEMPLATE_ID = X_EXP_TEMPLATE_ID;
124
125 if (sql%notfound) then
126 raise no_data_found;
127 end if;
128 end UPDATE_ROW;
129
130 procedure DELETE_ROW (
131 X_EXP_TEMPLATE_ID in NUMBER
132 ) is
133 begin
134 delete from AMS_EXP_TEMPLATE
135 where EXP_TEMPLATE_ID = X_EXP_TEMPLATE_ID;
136
137 if (sql%notfound) then
138 raise no_data_found;
139 end if;
140
141 end DELETE_ROW;
142
143 procedure LOAD_ROW (
144 X_EXP_TEMPLATE_ID in NUMBER,
145 X_SET_CLAUSE in VARCHAR2,
146 X_EXPORT_TYPE in VARCHAR2,
147 X_TEMPLATE_NAME in VARCHAR2,
148 X_SEEDED_SELECT_CLAUSE in LONG,
149 X_FROM_CLAUSE in VARCHAR2,
150 X_JOIN_CONDITION in VARCHAR2,
151 X_OWNER in VARCHAR2
152 ) is
153
154 l_user_id number := 0;
155 l_concom_id number;
156 l_obj_verno number := 1;
157 l_dummy_char varchar2(1);
158 l_row_id varchar2(100);
159
160 cursor c_chk_col_exists is
161 select 'x'
162 from AMS_EXP_TEMPLATE
163 where EXP_TEMPLATE_ID = X_EXP_TEMPLATE_ID;
164
165 cursor c_get_con_com_id is
166 select AMS_EXP_TEMPLATE_S.nextval
167 from dual;
168
169
170 begin
171
172 if X_OWNER = 'SEED' then
173 l_user_id := 1;
174 end if;
175 open c_chk_col_exists;
176 fetch c_chk_col_exists into l_dummy_char;
177
178 if c_chk_col_exists%notfound
179 then
180 close c_chk_col_exists;
181 if X_EXP_TEMPLATE_ID is null
182 then
183 open c_get_con_com_id;
184 fetch c_get_con_com_id into l_concom_id;
185 close c_get_con_com_id;
186 else
187 l_concom_id := X_EXP_TEMPLATE_ID;
188 end if;
189 AMS_EXP_TEMPLATE_PKG.INSERT_ROW (
190 X_ROWID => l_row_id,
191 X_EXP_TEMPLATE_ID => X_EXP_TEMPLATE_ID,
192 X_SET_CLAUSE => X_SET_CLAUSE,
193 X_EXPORT_TYPE => X_EXPORT_TYPE,
194 X_OBJECT_VERSION_NUMBER => l_obj_verno,
195 X_TEMPLATE_NAME => X_TEMPLATE_NAME,
196 X_SEEDED_SELECT_CLAUSE => X_SEEDED_SELECT_CLAUSE,
197 X_FROM_CLAUSE => X_FROM_CLAUSE,
198 X_JOIN_CONDITION => X_JOIN_CONDITION,
199 X_CREATION_DATE => sysdate,
200 X_CREATED_BY => l_user_id,
201 X_LAST_UPDATE_DATE => sysdate,
202 X_LAST_UPDATED_BY => l_user_id,
203 X_LAST_UPDATE_LOGIN => 0);
204 else
205 close c_chk_col_exists;
206 l_concom_id := X_EXP_TEMPLATE_ID;
207
208 AMS_EXP_TEMPLATE_PKG.UPDATE_ROW(
209 X_EXP_TEMPLATE_ID => X_EXP_TEMPLATE_ID,
210 X_SET_CLAUSE => X_SET_CLAUSE,
211 X_EXPORT_TYPE => X_EXPORT_TYPE,
212 X_OBJECT_VERSION_NUMBER => l_obj_verno,
213 X_TEMPLATE_NAME => X_TEMPLATE_NAME,
214 X_SEEDED_SELECT_CLAUSE => X_SEEDED_SELECT_CLAUSE,
215 X_FROM_CLAUSE => X_FROM_CLAUSE,
216 X_JOIN_CONDITION => X_JOIN_CONDITION,
217 X_LAST_UPDATE_DATE => sysdate,
218 X_LAST_UPDATED_BY => l_user_id,
219 X_LAST_UPDATE_LOGIN => 0);
220 end if;
221 end LOAD_ROW;
222
223 end AMS_EXP_TEMPLATE_PKG;