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