1 PACKAGE ego_pages_bulkload_pvt AUTHID CURRENT_USER AS
2 /* $Header: EGOVPGBS.pls 120.0.12010000.5 2010/04/27 06:10:22 jiabraha noship $ */
3 ----------------------
4 -- Global Constants --
5 ----------------------
6 /*Transaction Type variables*/
7 G_OPR_CREATE CONSTANT VARCHAR2(30) := 'CREATE';
8 G_OPR_UPDATE CONSTANT VARCHAR2(30) := 'UPDATE';
9 G_OPR_DELETE CONSTANT VARCHAR2(30) := 'DELETE';
10 G_OPR_SYNC CONSTANT VARCHAR2(30) := 'SYNC';
11
12 /*Error status variables*/
13 G_RET_STS_SUCCESS CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
14 G_RET_STS_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
15 G_RET_STS_UNEXP_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
16
17 /*Concurrent Program variables*/
18 G_PROG_APPL_ID CONSTANT NUMBER := FND_GLOBAL.PROG_APPL_ID;
19 G_PROGRAM_ID CONSTANT NUMBER := FND_GLOBAL.CONC_PROGRAM_ID;
20 G_REQUEST_ID CONSTANT NUMBER := FND_GLOBAL.CONC_REQUEST_ID;
21 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
22 G_LOGIN_ID NUMBER := FND_GLOBAL.LOGIN_ID;
23 G_SET_PROCESS_ID NUMBER;
24 G_EGO_APPLICATION_ID NUMBER;
25 G_COMMIT BOOLEAN := TRUE;
26 G_OBJECT_ID NUMBER;
27 G_PAGES_COUNT NUMBER :=0;
28 G_PAGE_ENTRIES_COUNT NUMBER :=0;
29
30 /*Process status variables*/
31 G_PROCESS_RECORD CONSTANT NUMBER := 1;
32 G_ERROR_RECORD CONSTANT NUMBER := 3;
33 G_SUCCESS_RECORD CONSTANT NUMBER := 7;
34
35 /*Flow Type variables*/
36 G_FLOW_TYPE NUMBER;
37 G_EGO_MD_API CONSTANT NUMBER := 1;
38 G_EGO_MD_INTF CONSTANT NUMBER := 2;
39
40 /*Error Handling variables*/
41 G_MESSAGE_NAME VARCHAR2(100);
42 G_MESSAGE_TEXT VARCHAR2(2000);
43 G_BO_IDENTIFIER_PG CONSTANT VARCHAR2(30) := 'ICC_BO';
44 G_ENTITY_PG CONSTANT VARCHAR2(30) := 'ICC_PG';
45 G_ENTITY_ENT CONSTANT VARCHAR2(30) := 'ICC_PG_ENTRIES';
46 G_ENTITY_PG_TAB CONSTANT VARCHAR2(30) := 'EGO_PAGES_INTERFACE';
47 G_ENTITY_ENT_TAB CONSTANT VARCHAR2(30) := 'EGO_PAGE_ENTRIES_INTERFACE';
48 G_TOKEN_TABLE ERROR_HANDLER.TOKEN_TBL_TYPE;
49
50 /*Null variables*/
51 G_NULL_NUM CONSTANT NUMBER := 9.99E125;
52 G_NULL_CHAR CONSTANT VARCHAR2(1) := Chr(0);
53 G_NULL_DATE CONSTANT DATE := To_date('1', 'j');
54
55 /*Data Level variables*/
56 G_DL_ITEM_LEVEL CONSTANT VARCHAR2(30) := 'ITEM_LEVEL';
57 G_DL_ITEM_REV_LEVEL CONSTANT VARCHAR2(30) := 'ITEM_REVISION_LEVEL';
58 G_DL_ITEM_ORG CONSTANT VARCHAR2(30) := 'ITEM_ORG';
59 G_DL_ITEM_SUP CONSTANT VARCHAR2(30) := 'ITEM_SUP';
60 G_DL_ITEM_SUP_SITE CONSTANT VARCHAR2(30) := 'ITEM_SUP_SITE';
61 G_DL_ITEM_SUP_SITE_ORG CONSTANT VARCHAR2(30) := 'ITEM_SUP_SITE_ORG';
62
63
64 /*This Procedure is used to initialize certain column values in the interface table.
65 Used in the interface flow.
66 x_return_status OUT NOCOPY parameter that returns the status
67 x_return_msg OUT NOCOPY parameter that returns the error message*/
68 PROCEDURE initialize(
69 x_return_status OUT NOCOPY VARCHAR2, x_return_msg OUT NOCOPY VARCHAR2);
70
71 /*This procedure is used to validate the transaction type for all the interface tables.
72 Used in the interface flow.
73 x_return_status OUT NOCOPY parameter that returns the status
74 x_return_msg OUT NOCOPY parameter that returns the error message*/
75 PROCEDURE validate_transaction_type(
76 x_return_status OUT NOCOPY VARCHAR2, x_return_msg OUT NOCOPY VARCHAR2);
77
78 /*This procedure is used for value to ID conversion for Pages.
79 Used in the interface flow.
80 x_return_status OUT NOCOPY parameter that returns the status
81 x_return_msg OUT NOCOPY parameter that returns the error message*/
82 PROCEDURE value_to_id_pages(
83 x_return_status OUT NOCOPY VARCHAR2, x_return_msg OUT NOCOPY VARCHAR2);
84
85 /*This procedure is used for the value to ID conversion for Page Entries.
86 Used in the interface flow.
87 x_return_status OUT NOCOPY parameter that returns the status
88 x_return_msg OUT NOCOPY parameter that returns the error message*/
89 PROCEDURE value_to_id_pg_entries(
90 x_return_status OUT NOCOPY VARCHAR2, x_return_msg OUT NOCOPY VARCHAR2);
91
92 /*This procedure is used for constructing the records for pages.
93 Used in the interface flow.
94 x_return_status OUT NOCOPY parameter that returns the status
95 x_return_msg OUT NOCOPY parameter that returns the error message*/
96 PROCEDURE construct_pages(
97 x_return_status OUT NOCOPY VARCHAR2, x_return_msg OUT NOCOPY VARCHAR2);
98
99 /*This procedure is used for constrcting the records for page entries.
100 Used in the interface flow.
101 x_return_status OUT NOCOPY parameter that returns the status
102 x_return_msg OUT NOCOPY parameter that returns the error message*/
103 PROCEDURE construct_pg_entries(
104 x_return_status OUT NOCOPY VARCHAR2, x_return_msg OUT NOCOPY VARCHAR2);
105
106 /*This procedure is used to construct the records for page pl/sql table.
107 p_pg_tbl IN OUT NOCOPY Pages table
108 x_return_status OUT NOCOPY parameter that returns the status
109 x_return_msg OUT NOCOPY parameter that returns the error message*/
110 PROCEDURE construct_page_tbl(
111 p_pg_tbl IN OUT NOCOPY ego_metadata_pub.ego_pg_tbl,
112 x_return_status OUT NOCOPY VARCHAR2, x_return_msg OUT NOCOPY VARCHAR2);
113
114 /*This procedure is used sed to construct the records for page entries pl/sql table.
115 Used in the API flow.
116 p_ent_tbl IN OUT NOCOPY Page Entries table
117 x_return_status OUT NOCOPY parameter that returns the status
118 x_return_msg OUT NOCOPY parameter that returns the error message*/
119 PROCEDURE construct_pg_entries_tbl(
120 p_ent_tbl IN OUT NOCOPY ego_metadata_pub.ego_ent_tbl,
121 x_return_status OUT NOCOPY VARCHAR2, x_return_msg OUT NOCOPY VARCHAR2);
122
123
124 /*This procedure is used for value to ID conversion for pages.
125 Used in the API flow.
126 p_pg_tbl IN OUT NOCOPY Pages table
127 x_return_status OUT NOCOPY parameter that returns the status
128 x_return_msg OUT NOCOPY parameter that returns the error message*/
129 PROCEDURE value_to_id_page_tbl(
130 p_pg_tbl IN OUT NOCOPY ego_metadata_pub.ego_pg_tbl,
131 x_return_status OUT NOCOPY VARCHAR2, x_return_msg OUT NOCOPY VARCHAR2);
132
133 /*This procedure is used for value to ID conversion for page entries.
134 Used in the API flow.
135 p_ent_tbl IN OUT NOCOPY Page Entries table
136 x_return_status OUT NOCOPY parameter that returns the status
137 x_return_msg OUT NOCOPY parameter that returns the error message*/
138 PROCEDURE value_to_id_pg_entries_tbl(
139 p_ent_tbl IN OUT NOCOPY ego_metadata_pub.ego_ent_tbl,
140 x_return_status OUT NOCOPY VARCHAR2, x_return_msg OUT NOCOPY VARCHAR2);
141
142 /*This procedure is used for bulk validation of the pages.
143 Used in the interface flow.
144 x_return_status OUT NOCOPY parameter that returns the status
145 x_return_msg OUT NOCOPY parameter that returns the error message*/
146 PROCEDURE bulk_validate_pages(
147 x_return_status OUT NOCOPY VARCHAR2, x_return_msg OUT NOCOPY VARCHAR2);
148
149 /*This procedure is used for the bulk validation for the page entries.
150 Used in the interface flow.
151 x_return_status OUT NOCOPY parameter that returns the status
152 x_return_msg OUT NOCOPY parameter that returns the error message*/
153 PROCEDURE bulk_validate_pg_entries(
154 x_return_status OUT NOCOPY VARCHAR2, x_return_msg OUT NOCOPY VARCHAR2);
155
156
157 /*This procedure is used to handle the additional validations for Pages.
158 Used in the API flow.
159 p_pg_tbl IN OUT NOCOPY Pages table
160 x_return_status OUT NOCOPY parameter that returns the status
161 x_return_msg OUT NOCOPY parameter that returns the error message*/
162 PROCEDURE additional_pg_validations(
163 p_pg_tbl IN OUT NOCOPY ego_metadata_pub.ego_pg_tbl,
164 x_return_status OUT NOCOPY VARCHAR2, x_return_msg OUT NOCOPY VARCHAR2);
165
166
167 /*This procedure is used to handle the additional validations for Page Entries
168 Used in the API flow.
169 p_ent_tbl IN OUT NOCOPY Page Entries table
170 x_return_status OUT NOCOPY parameter that returns the status
171 x_return_msg OUT NOCOPY parameter that returns the error message*/
172 PROCEDURE additional_ent_validations(
173 p_ent_tbl IN OUT NOCOPY ego_metadata_pub.ego_ent_tbl,
174 x_return_status OUT NOCOPY VARCHAR2, x_return_msg OUT NOCOPY VARCHAR2);
175
176
177 /*This procedure is used to handle the common validations pertaining to Pages.
178 Used in the both the flows.
179 p_pg_tbl IN OUT NOCOPY Pages table
180 x_return_status OUT NOCOPY parameter that returns the status
181 x_return_msg OUT NOCOPY parameter that returns the error message*/
182 PROCEDURE common_pg_validations(
183 p_pg_tbl IN OUT NOCOPY ego_metadata_pub.ego_pg_tbl,
184 x_return_status OUT NOCOPY VARCHAR2, x_return_msg OUT NOCOPY VARCHAR2);
185
186
187 /*This procedure is used to handle the common validations pertaining to page entries.
188 Used in both flows.
189 p_ent_tbl IN OUT NOCOPY Page Entries table
190 x_return_status OUT NOCOPY parameter that returns the status
191 x_return_msg OUT NOCOPY parameter that returns the error message*/
192 PROCEDURE common_ent_validations(
193 p_ent_tbl IN OUT NOCOPY ego_metadata_pub.ego_ent_tbl,
194 x_return_status OUT NOCOPY VARCHAR2, x_return_msg OUT NOCOPY VARCHAR2);
195
196
197 /*This is the main procedure that is called by the interface flow.
198 p_set_process_id IN set_process_id
199 x_return_status OUT NOCOPY parameter that returns the status
200 x_return_msg OUT NOCOPY parameter that returns the error message*/
201 PROCEDURE import_pg_intf(
202 p_set_process_id IN VARCHAR2,
203 x_return_status OUT NOCOPY VARCHAR2,
204 x_return_msg OUT NOCOPY VARCHAR2);
205
206
207 /*This the main procedure called by the public API to create pages.
208 p_pg_tbl IN OUT NOCOPY Pages table
209 p_commit IN controls whether commit to be executed or not
210 x_return_status OUT NOCOPY parameter that returns the status
211 x_return_msg OUT NOCOPY parameter that returns the error message*/
212 PROCEDURE Process_pages(
213 p_pg_tbl IN OUT NOCOPY ego_metadata_pub.ego_pg_tbl,
214 p_commit IN BOOLEAN DEFAULT true,
215 x_return_status OUT NOCOPY VARCHAR2, x_return_msg OUT NOCOPY VARCHAR2);
216
217
218 /*This the main procedure called by the public API to create pages.
219 p_ent_tbl IN OUT NOCOPY Pages Entries table
220 p_commit IN controls whether commit to be executed or not
221 x_return_status OUT NOCOPY parameter that returns the status
222 x_return_msg OUT NOCOPY parameter that returns the error message*/
223 PROCEDURE Process_pg_entries(
224 p_ent_tbl IN OUT NOCOPY ego_metadata_pub.ego_ent_tbl,
225 p_commit IN BOOLEAN DEFAULT true,
226 x_return_status OUT NOCOPY VARCHAR2, x_return_msg OUT NOCOPY VARCHAR2);
227
228
229 /*This procedure is used to process the Pages.
230 Used by both the flows.
231 p_pg_tbl IN OUT NOCOPY Pages table
232 x_return_status OUT NOCOPY parameter that returns the status
233 x_return_msg OUT NOCOPY parameter that returns the error message*/
234 PROCEDURE process_pg(
235 p_pg_tbl IN OUT NOCOPY ego_metadata_pub.ego_pg_tbl,
236 x_return_status OUT NOCOPY VARCHAR2, x_return_msg OUT NOCOPY VARCHAR2);
237
238
239 /*This procedure is used to process the page entries.
240 Used in both flows.
241 p_ent_tbl IN OUT NOCOPY Page Entries table
242 x_return_status OUT NOCOPY parameter that returns the status
243 x_return_msg OUT NOCOPY parameter that returns the error message*/
244 PROCEDURE process_ent(
245 p_ent_tbl IN OUT NOCOPY ego_metadata_pub.ego_ent_tbl,
246 x_return_status OUT NOCOPY VARCHAR2, x_return_msg OUT NOCOPY VARCHAR2);
247
248 /*This procedure is used to update the Pages interface table.
249 Used in the interface flow.
250 p_pg_tbl IN OUT NOCOPY Pages table
251 x_return_status OUT NOCOPY parameter that returns the status
252 x_return_msg OUT NOCOPY parameter that returns the error message*/
253 PROCEDURE update_intf_pages(
254 p_pg_tbl IN OUT NOCOPY ego_metadata_pub.ego_pg_tbl,
255 x_return_status OUT NOCOPY VARCHAR2, x_return_msg OUT NOCOPY VARCHAR2);
256
257
258 /*This procedure is used to update the page entries interface table.
259 Used in the interface flow.
260 p_ent_tbl IN OUT NOCOPY Page Entries table
261 x_return_status OUT NOCOPY parameter that returns the status
262 x_return_msg OUT NOCOPY parameter that returns the error message*/
263 PROCEDURE update_intf_pg_entries(
264 p_ent_tbl IN OUT NOCOPY ego_metadata_pub.ego_ent_tbl,
265 x_return_status OUT NOCOPY VARCHAR2, x_return_msg OUT NOCOPY VARCHAR2);
266
267 /*This procedure is used to delete processed records from the pages interface
268 Used in the interface flow.
269 x_set_process_id IN Set Process ID
270 x_return_status OUT NOCOPY parameter that returns the status
271 x_return_msg OUT NOCOPY parameter that returns the error message*/
272 PROCEDURE delete_processed_pages(
273 x_set_process_id IN NUMBER,
274 x_return_status OUT NOCOPY VARCHAR2, x_return_msg OUT NOCOPY VARCHAR2);
275
276
277 /*This procedure is used to deleted processed records from the page entries interface
278 Used in the interface flow.
279 x_set_process_id IN Set Process ID
280 x_return_status OUT NOCOPY parameter that returns the status
281 x_return_msg OUT NOCOPY parameter that returns the error message*/
282 PROCEDURE delete_processed_pg_entries(
283 x_set_process_id IN NUMBER,
284 x_return_status OUT NOCOPY VARCHAR2, x_return_msg OUT NOCOPY VARCHAR2);
285
286 /*This procedure is used in the update flow to handle null values for PG
287 Used in the interface and API flow.
288 p_pg_tbl IN OUT NOCOPY Pages plsql table
289 x_return_status OUT NOCOPY parameter that returns the status
290 x_return_msg OUT NOCOPY parameter that returns the error message*/
291 PROCEDURE handle_null_pg(
292 p_pg_tbl IN OUT NOCOPY ego_metadata_pub.ego_pg_tbl,
293 x_return_status OUT NOCOPY VARCHAR2, x_return_msg OUT NOCOPY VARCHAR2);
294
295 /*This procedure is used in the update flow to handle null values for PG Entries
296 Used in the interface and API flow.
297 p_ent_tbl IN OUT NOCOPY Page Entries plsql table
298 x_return_status OUT NOCOPY parameter that returns the status
299 x_return_msg OUT NOCOPY parameter that returns the error message*/
300 PROCEDURE handle_null_pg_entries(
301 p_ent_tbl IN OUT NOCOPY ego_metadata_pub.ego_ent_tbl,
302 x_return_status OUT NOCOPY VARCHAR2, x_return_msg OUT NOCOPY VARCHAR2);
303
304 /*This procedure will log debug messages
305 x_msg IN Input message name*/
306 Procedure write_debug(x_msg IN VARCHAR2);
307
308 END ego_pages_bulkload_pvt;
[Home] [Help]