1 PACKAGE EGO_FUNCTIONS_BULKLOAD_PVT AUTHID CURRENT_USER AS
2 /* $Header: EGOVFNBS.pls 120.0.12010000.4 2010/05/17 12:46:21 snandana noship $ */
3
4 /* Constants for process statuses. */
5 G_PROCESS_RECORD CONSTANT NUMBER := 1;
6 G_ERROR_RECORD CONSTANT NUMBER := 3;
7 G_SUCCESS_RECORD CONSTANT NUMBER := 7;
8
9 CURSOR ego_func_tbl(
10 c_set_process_id NUMBER) IS
11 SELECT *
12 FROM ego_functions_interface
13 WHERE ( (c_set_process_id IS NULL OR set_process_id = c_set_process_id)
14 AND process_status = G_PROCESS_RECORD );
15
16 CURSOR ego_func_param_tbl(
17 c_set_process_id NUMBER) IS
18 SELECT *
19 FROM ego_func_params_interface
20 WHERE ( (c_set_process_id IS NULL OR set_process_id = c_set_process_id)
21 AND process_status = G_PROCESS_RECORD );
22
23 /* Constants for transaction types. */
24 G_CREATE_TRANSACTION CONSTANT VARCHAR2(30) := 'CREATE';
25 G_UPDATE_TRANSACTION CONSTANT VARCHAR2(30) := 'UPDATE';
26 G_DELETE_TRANSACTION CONSTANT VARCHAR2(30) := 'DELETE';
27 G_SYNC_TRANSACTION CONSTANT VARCHAR2(30) := 'SYNC';
28
29 /* Constants for Who columns. Use the constants from ego_metadata_bulkload_pvt, as they will be
30 initialized in ego_metadata_bulkload_pvt.SetGlobals() while running Meata Data Import Concurrent Program. */
31 -- Initialize G_USER_ID and G_LOGIN_ID to some value, as constants in ego_metadata_bulkload_pvt will not be initialized,
32 -- while testing/debugging this package standalone.
33 G_USER_ID CONSTANT NUMBER := ego_metadata_bulkload_pvt.G_USER_ID;
34 G_LOGIN_ID CONSTANT NUMBER := ego_metadata_bulkload_pvt.G_LOGIN_ID;
35 G_REQUEST_ID CONSTANT NUMBER := ego_metadata_bulkload_pvt.G_REQUEST_ID;
36 G_PROGRAM_APPLICATION_ID CONSTANT NUMBER := ego_metadata_bulkload_pvt.G_PROGRAM_APPLICATION_ID;
37 G_PROGRAM_ID CONSTANT NUMBER := ego_metadata_bulkload_pvt.G_PROGRAM_ID;
38
39 /* Constants to null out the column values in UPDATE transactions. */
40 G_NULL_NUM CONSTANT NUMBER := fnd_api.G_NULL_NUM;
41 G_NULL_CHAR CONSTANT VARCHAR2(1) := fnd_api.G_NULL_CHAR;
42
43 /* Constants for error handling. */
44 G_FUNCTIONS_TAB CONSTANT VARCHAR2(50) := 'EGO_FUNCTIONS_INTERFACE';
45 G_FUNC_PARAMS_TAB CONSTANT VARCHAR2(50) := 'EGO_FUNC_PARAMS_INTERFACE';
46 G_BO_IDENTIFIER_ICC CONSTANT VARCHAR2(30) := 'ICC';
47 G_ENTITY_ICC_FN CONSTANT VARCHAR2(30) := 'ICC_FUNCTION';
48 G_ENTITY_ICC_FN_PARAM CONSTANT VARCHAR2(30) := 'ICC_FN_PARAM';
49
50 /* Constant to disply Package Name while logging debug messages. */
51 G_PCK_NAME CONSTANT VARCHAR2(30) := 'EGO_FUNCTIONS_BULKLOAD_PVT';
52
53 ---------------------------------------------------------------------------------------------
54 -- Procedure Name: import_functions_intf --
55 -- This is the main procedure that will be called while running Matadata Import Concurrent --
56 -- Program, to process Functions and Function Parameters. --
57 -- Parameters: --
58 -- IN --
59 -- p_set_process_id: ID to identify the rows (in ego_functions_interface table) to be --
60 -- processed in a batch. --
61 -- OUT --
62 -- x_return_status: Return status. Can be S or U (Unexpected Error). --
63 -- x_return_msg: Stores the error message, if unexpected error occurs. --
64 ---------------------------------------------------------------------------------------------
65 PROCEDURE import_functions_intf(p_set_process_id IN NUMBER,
66 x_return_status OUT NOCOPY VARCHAR2,
67 x_return_msg OUT NOCOPY VARCHAR2);
68
69 ---------------------------------------------------------------------------------------------
70 -- Procedure Name: delete_processed_functions --
71 -- This procedure will be called at end by Matadata Import Concurrent Program, --
72 -- to delete processed rows from Functions and Function Parameters interface tables. --
73 -- Parameters: --
74 -- IN --
75 -- p_set_process_id: ID to identify the rows (in ego_functions_interface table) that --
76 -- belongs to a particular batch. --
77 -- OUT --
78 -- x_return_status: Return status. Can be S or U (Unexpected Error). --
79 -- x_return_msg: Stores the error message, if unexpected error occurs. --
80 ---------------------------------------------------------------------------------------------
81 PROCEDURE delete_processed_functions(p_set_process_id IN NUMBER,
82 x_return_status OUT NOCOPY VARCHAR2,
83 x_return_msg OUT NOCOPY VARCHAR2);
84
85 ---------------------------------------------------------------------------------------------
86 -- Procedure Name: initialize_functions --
87 -- This procedure will intialize functions interface table with by updating the --
88 -- "WHO" columns, transaction_id and convering the transction_type to upper case. --
89 -- IN --
90 -- p_set_process_id: ID to identify the rows (in ego_functions_interface table) that --
91 -- belongs to a particular batch. --
92 ---------------------------------------------------------------------------------------------
93 PROCEDURE initialize_functions(p_set_process_id IN NUMBER);
94
95 ---------------------------------------------------------------------------------------------
96 -- Procedure Name: construct_function --
97 -- This procedure will validate transaction type and the key columns that can identify --
98 -- a function and also converts SYNC transaction to either CREATE or UPDATE, --
99 -- if the validation succeeds. --
100 -- Parameters: --
101 -- IN OUT --
102 -- func_header_rec - Represents a row of type ego_functions_interface%ROWTYPE. --
103 ---------------------------------------------------------------------------------------------
104 PROCEDURE construct_function(func_header_rec IN OUT NOCOPY ego_functions_interface%ROWTYPE);
105
106 ---------------------------------------------------------------------------------------------
107 -- Procedure Name: validate_function --
108 -- This procedure will perform the remaining validations (excluding the validations done --
109 -- on key columns in construct_function) based on the transaction type. --
110 -- Parameters: --
111 -- IN OUT --
112 -- func_header_rec - Represents a row of type ego_functions_interface%ROWTYPE. --
113 ---------------------------------------------------------------------------------------------
114 PROCEDURE validate_function(func_header_rec IN OUT NOCOPY ego_functions_interface%ROWTYPE);
115
116 ---------------------------------------------------------------------------------------------
117 -- Procedure Name: transact_function --
118 -- This procedure will update the base table, with the data in func_header_rec, only if --
119 -- there are no validation errors (process_status<>3), based on transaction type. --
120 -- Parameters: --
121 -- IN OUT --
122 -- func_header_rec - Represents a row of type ego_functions_interface%ROWTYPE. --
123 ---------------------------------------------------------------------------------------------
124 PROCEDURE transact_function(func_header_rec IN OUT NOCOPY ego_functions_interface%ROWTYPE);
125
126 ---------------------------------------------------------------------------------------------
127 -- Procedure Name: process_functions --
128 -- This procedure will process all the functions one by one. Technically, it will call the --
129 -- previous three functions. --
130 -- Parameters: --
134 -- has significance only in public API flow. --
131 -- IN OUT --
132 -- ego_func_tbl_values - Represents a table of type ego_functions_interface%ROWTYPE. --
133 -- p_commit - Indicates whether to commit the work or not. This parameter --
135 ---------------------------------------------------------------------------------------------
136 PROCEDURE process_functions(ego_func_tbl_values IN OUT NOCOPY ego_metadata_pub.ego_function_tbl_type,
137 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE);
138
139 ---------------------------------------------------------------------------------------------
140 -- Procedure Name: update_intfc_functions --
141 -- This will be invoked in Concurrent Request flow. --
142 -- This procedure will update the interface table back after processing the records. --
143 -- Parameters: --
144 -- IN OUT --
145 -- ego_func_tbl_values - Represents a table of type ego_functions_interface%ROWTYPE. --
146 ---------------------------------------------------------------------------------------------
147 PROCEDURE update_intfc_functions(ego_func_tbl_values IN OUT NOCOPY ego_metadata_pub.ego_function_tbl_type); /* Bug 9701271. */
148
149 ---------------------------------------------------------------------------------------------
150 -- Procedure Name: process_functions_conc_flow --
151 -- This will be invoked in Concurrent Request flow. --
152 -- This procedure will read the data in chunks from ego_functions_interface table, --
153 -- processes them by calling process_functions() and then updates interface table --
154 -- back by calling update_intfc_functions(). --
155 -- Parameters: --
156 -- IN --
157 -- p_set_process_id: ID to identify the rows (in ego_functions_interface table) to be --
158 -- processed in a batch. --
159 ---------------------------------------------------------------------------------------------
160 PROCEDURE process_functions_conc_flow(p_set_process_id IN NUMBER);
161
162 ----------------------------------------------------------------------------------------------
163 -- Procedure Name: process_functions_conc_flow --
164 -- This will be invoked in Concurrent Request flow. --
165 -- This procedure will do bulk validations. --
166 -- Parameters: --
167 -- IN --
168 -- p_set_process_id: ID to identify the rows (in ego_functions_interface table) to be --
169 -- processed in a batch. --
170 ----------------------------------------------------------------------------------------------
171 PROCEDURE bulk_validate_functions(p_set_process_id IN NUMBER);
172
173 ---------------------------------------------------------------------------------------------
174 -- Procedure Name: initialize_func_params --
175 -- This procedure will intialize function parameters interface table with by updating the --
176 -- "WHO" columns, transaction_id and convering the transction_type to upper case. --
177 -- IN --
178 -- p_set_process_id: ID to identify the rows (in ego_functions_interface table) that --
179 -- belongs to a particular batch. --
180 ---------------------------------------------------------------------------------------------
181 PROCEDURE initialize_func_params(p_set_process_id IN NUMBER);
182
183 ---------------------------------------------------------------------------------------------
184 -- Procedure Name: construct_func_param --
185 -- This procedure will validate transaction type and the key columns that can identify --
186 -- a function parameter and also converts SYNC transaction to either CREATE or UPDATE, --
187 -- if the validation succeeds. --
188 -- Parameters: --
189 -- IN OUT --
193
190 -- func_param_rec - Represents a row of type ego_func_params_interface%ROWTYPE. --
191 ---------------------------------------------------------------------------------------------
192 PROCEDURE construct_func_param(func_param_rec IN OUT NOCOPY ego_func_params_interface%ROWTYPE);
194 ---------------------------------------------------------------------------------------------
195 -- Procedure Name: validate_func_param --
196 -- This procedure will perform the remaining validations (excluding the validations done --
197 -- on key columns in construct_func_param) based on the transaction type. --
198 -- Parameters: --
202 PROCEDURE validate_func_param(func_param_rec IN OUT NOCOPY ego_func_params_interface%ROWTYPE);
199 -- IN OUT --
200 -- func_param_rec - Represents a row of type ego_func_params_interface%ROWTYPE. --
201 ---------------------------------------------------------------------------------------------
203
204 ---------------------------------------------------------------------------------------------
205 -- Procedure Name: transact_func_param --
206 -- This procedure will update the base table, with the data in func_param_rec, only if --
207 -- there are no validation errors (process_status<>3), based on transaction type. --
208 -- Parameters: --
209 -- IN OUT --
210 -- func_param_rec - Represents a row of type ego_func_params_interface%ROWTYPE. --
211 ---------------------------------------------------------------------------------------------
212 PROCEDURE transact_func_param(func_param_rec IN OUT NOCOPY ego_func_params_interface%ROWTYPE);
213
214 ---------------------------------------------------------------------------------------------
215 -- Procedure Name: process_func_params --
216 -- This procedure will process all the function parameters one by one. Technically, it --
217 -- will call the previous three functions. --
218 -- Parameters: --
219 -- IN OUT --
220 -- ego_func_param_tbl_values - Represents a table of type --
221 -- ego_func_params_interface%ROWTYPE. --
225 PROCEDURE process_func_params(ego_func_param_tbl_values IN OUT NOCOPY ego_metadata_pub.ego_func_param_tbl_type,
222 -- p_commit - Indicates whether to commit the work or not. This parameter --
223 -- has significance only in public API flow. --
224 ---------------------------------------------------------------------------------------------
226 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE);
227
228 ---------------------------------------------------------------------------------------------
229 -- Procedure Name: update_intfc_func_params --
230 -- This will be invoked in Concurrent Request flow. --
231 -- This procedure will update the interface table back after processing the records. --
232 -- Parameters: --
233 -- IN OUT --
234 -- ego_func_param_tbl_values - Represents a table of type --
235 -- ego_func_params_interface%ROWTYPE. --
236 ---------------------------------------------------------------------------------------------
237 PROCEDURE update_intfc_func_params(ego_func_param_tbl_values IN OUT NOCOPY ego_metadata_pub.ego_func_param_tbl_type); /* Bug 9701271. */
238
239 ---------------------------------------------------------------------------------------------
240 -- Procedure Name: process_func_params_conc_flow --
241 -- This will be invoked in Concurrent Request flow. --
242 -- This procedure will read the data in chunks from ego_func_params_interface table, --
243 -- processes them by calling process_func_params() and then updates interface table --
244 -- back by calling update_intfc_func_params(). --
245 -- Parameters: --
246 -- IN --
247 -- p_set_process_id: ID to identify the rows (in ego_func_params_interface table) to be --
248 -- processed in a batch. --
249 ---------------------------------------------------------------------------------------------
250 PROCEDURE process_func_params_conc_flow(p_set_process_id IN NUMBER);
251
255 -- This procedure will do bulk validations. --
252 ----------------------------------------------------------------------------------------------
253 -- Procedure Name: bulk_validate_func_params --
254 -- This will be invoked in Concurrent Request flow. --
256 -- Parameters: --
257 -- IN --
258 -- p_set_process_id: ID to identify the rows (in ego_func_params_interface table) to be --
259 -- processed in a batch. --
260 ----------------------------------------------------------------------------------------------
261 PROCEDURE bulk_validate_func_params(p_set_process_id IN NUMBER);
262 END EGO_FUNCTIONS_BULKLOAD_PVT;
[Home] [Help]