[Home] [Help]
PACKAGE BODY: APPS.GCS_LEX_MAP_COLUMNS_PKG
Source
1 PACKAGE BODY GCS_LEX_MAP_COLUMNS_PKG AS
2 /* $Header: gcslxmcb.pls 115.2 2003/08/13 17:55:00 mikeward noship $ */
3 --
4 -- Package
5 -- gcs_lex_map_structs_pkg
6 -- Purpose
7 -- Package procedures for Lexical Mapping Structures
8 -- History
9 -- 23-JUN-03 M Ward Created
10 --
11
12 PROCEDURE Insert_Row( row_id IN OUT NOCOPY VARCHAR2,
13 structure_id NUMBER,
14 column_name VARCHAR2,
15 column_type_code VARCHAR2,
16 write_flag VARCHAR2,
17 error_code_column_flag VARCHAR2,
18 last_update_date DATE,
19 last_updated_by NUMBER,
20 last_update_login NUMBER,
21 creation_date DATE,
22 created_by NUMBER) IS
23 CURSOR column_row IS
24 SELECT rowid
25 FROM gcs_lex_map_columns mc
26 WHERE mc.structure_id = insert_row.structure_id
27 AND mc.column_name = insert_row.column_name;
28 BEGIN
29 IF structure_id IS NULL OR column_name IS NULL THEN
30 raise no_data_found;
31 END IF;
32
33 INSERT INTO gcs_lex_map_columns( column_id,
34 structure_id,
35 column_name,
36 column_type_code,
37 write_flag,
38 error_code_column_flag,
39 last_update_date,
40 last_updated_by,
41 last_update_login,
42 creation_date,
43 created_by)
44 SELECT gcs_lex_map_columns_s.nextval,
45 structure_id,
46 column_name,
47 column_type_code,
48 write_flag,
49 error_code_column_flag,
50 last_update_date,
51 last_updated_by,
52 last_update_login,
53 creation_date,
54 created_by
55 FROM dual
56 WHERE NOT EXISTS
57 (SELECT 1
58 FROM gcs_lex_map_columns mc
59 WHERE mc.structure_id = insert_row.structure_id
60 AND mc.column_name = insert_row.column_name);
61
62 OPEN column_row;
63 FETCH column_row INTO row_id;
64 IF column_row%NOTFOUND THEN
65 CLOSE column_row;
66 raise no_data_found;
67 END IF;
68 CLOSE column_row;
69 END Insert_Row;
70
71 PROCEDURE Update_Row( structure_id NUMBER,
72 column_name VARCHAR2,
73 column_type_code VARCHAR2,
74 write_flag VARCHAR2,
75 error_code_column_flag VARCHAR2,
76 last_update_date DATE,
77 last_updated_by NUMBER,
78 last_update_login NUMBER) IS
79 BEGIN
80 UPDATE gcs_lex_map_columns mc
81 SET column_type_code = update_row.column_type_code,
82 write_flag = update_row.write_flag,
83 error_code_column_flag = update_row.error_code_column_flag,
84 last_update_date = update_row.last_update_date,
85 last_updated_by = update_row.last_updated_by,
86 last_update_login = update_row.last_update_login
87 WHERE mc.structure_id = update_row.structure_id
88 AND mc.column_name = update_row.column_name;
89
90 IF SQL%NOTFOUND THEN
91 raise no_data_found;
92 END IF;
93 END Update_Row;
94
95 PROCEDURE Load_Row( structure_name VARCHAR2,
96 column_name VARCHAR2,
97 owner VARCHAR2,
98 last_update_date VARCHAR2,
99 column_type_code VARCHAR2,
100 write_flag VARCHAR2,
101 error_code_column_flag VARCHAR2,
102 custom_mode VARCHAR2) IS
103 row_id VARCHAR2(64);
104 struct_id NUMBER;
105 f_luby NUMBER; -- entity owner in file
106 f_ludate DATE; -- entity update date in file
107 db_luby NUMBER; -- entity owner in db
108 db_ludate DATE; -- entity update date in db
109 BEGIN
110 -- Get the structure ID given the structure name
111 SELECT structure_id
112 INTO struct_id
113 FROM gcs_lex_map_structs ms
114 WHERE ms.structure_name = load_row.structure_name;
115
116 -- Get last updated information from the loader data file
117 f_luby := fnd_load_util.owner_id(owner);
118 f_ludate := nvl(to_date(last_update_date, 'YYYY/MM/DD'), sysdate);
119
120 BEGIN
121 SELECT mc.last_updated_by, mc.last_update_date
122 INTO db_luby, db_ludate
123 FROM GCS_LEX_MAP_COLUMNS mc
124 WHERE mc.structure_id = struct_id
125 AND mc.column_name = load_row.column_name;
126
127 -- Test for customization information
128 IF fnd_load_util.upload_test(f_luby, f_ludate, db_luby, db_ludate,
129 custom_mode) THEN
130 update_row( structure_id => struct_id,
131 column_name => column_name,
132 column_type_code => column_type_code,
133 write_flag => write_flag,
134 error_code_column_flag => error_code_column_flag,
135 last_update_date => f_ludate,
136 last_updated_by => f_luby,
137 last_update_login => 0);
138 END IF;
139 EXCEPTION
140 WHEN NO_DATA_FOUND THEN
141 insert_row( row_id => row_id,
142 structure_id => struct_id,
143 column_name => column_name,
144 column_type_code => column_type_code,
145 write_flag => write_flag,
146 error_code_column_flag => error_code_column_flag,
147 last_update_date => f_ludate,
148 last_updated_by => f_luby,
149 last_update_login => 0,
150 creation_date => f_ludate,
151 created_by => f_luby);
152 END;
153 END Load_Row;
154
155 PROCEDURE Translate_Row( structure_name VARCHAR2,
156 column_name VARCHAR2,
157 owner VARCHAR2,
158 last_update_date VARCHAR2,
159 custom_mode VARCHAR2) IS
160 struct_id NUMBER;
161 f_luby NUMBER; -- entity owner in file
162 f_ludate DATE; -- entity update date in file
163 db_luby NUMBER; -- entity owner in db
164 db_ludate DATE; -- entity update date in db
165 BEGIN
166 -- Get the structure ID given the structure name
167 SELECT structure_id
168 INTO struct_id
169 FROM gcs_lex_map_structs ms
170 WHERE ms.structure_name = translate_row.structure_name;
171
172 -- Get last updated information from the loader data file
173 f_luby := fnd_load_util.owner_id(owner);
174 f_ludate := nvl(to_date(last_update_date, 'YYYY/MM/DD'), sysdate);
175
176 BEGIN
177 SELECT mc.last_updated_by, mc.last_update_date
178 INTO db_luby, db_ludate
179 FROM GCS_LEX_MAP_COLUMNS mc
180 WHERE mc.structure_id = struct_id
181 AND mc.column_name = translate_row.column_name;
182
183 -- Test for customization information
184 IF fnd_load_util.upload_test(f_luby, f_ludate, db_luby, db_ludate,
185 custom_mode) THEN
186 UPDATE gcs_lex_map_columns mc
187 SET last_update_date = f_ludate,
188 last_updated_by = f_luby,
189 last_update_login = 0
190 WHERE mc.structure_id = struct_id
191 AND mc.column_name = translate_row.column_name;
192 END IF;
193 EXCEPTION
194 WHEN NO_DATA_FOUND THEN
195 null;
196 END;
197 END Translate_Row;
198
199 END GCS_LEX_MAP_COLUMNS_PKG;