1 package EGO_PUB_WS_UTIL AUTHID CURRENT_USER AS
2 /* $Header: EGOPUTLS.pls 120.5 2011/05/06 05:26:58 trudave noship $ */
3
4 /*==========================================================================+
5 | Copyright (c) 1993 Oracle Corporation Belmont, California, USA |
6 | All rights reserved. |
7 +===========================================================================+
8 | |
9 | File Name : EGOPUTLS.pls |
10 | DESCRIPTION : This file contains the utility procedures needed for |
11 | all entities involved in Metadata Sync |
12 | Created By : TRUDAVE |
13 | |
14 +==========================================================================*/
15
16 ---------------------------------------------------------------
17 -- Global Variables and Constants --
18 ---------------------------------------------------------------
19
20 G_CURRENT_USER_ID NUMBER := FND_GLOBAL.User_Id;
21 G_CURRENT_LOGIN_ID NUMBER := FND_GLOBAL.Login_Id;
22 G_ICC_WEBSERVICE VARCHAR2(20) := 'EGO_SYNCICCDETAILS';
23 G_AG_WEBSERVICE VARCHAR2(20) := 'EGO_SYNCAGDETAILS';
24 G_VS_WEBSERVICE VARCHAR2(20) := 'EGO_SYNCVSDETAILS';
25 G_SYNC_PARAM VARCHAR2(40) := 'BATCH_PROCESS_TYPE';
26 G_TRIGGER_IMPORT_PARAM VARCHAR2(40) := 'Launch Sync Program';
27
28 --array to store single_value parameter names
29 TYPE parameter_name_array_type IS VARRAY(15) OF VARCHAR2(1000);
30
31 --array to store XML path expressions to retrieve single-value params
32 TYPE xpath_expr_array_type IS VARRAY(15) OF VARCHAR2(1000);
33
34 --for a session id returns the Numeric configuration parameter value
35 --for the provided parameter name
36 --from ego_pub_ws_config table - For List mode
37 --from ego_pub_bat_params_b table - For Batch mode
38
39 FUNCTION Get_Numeric_Param_Value( p_session_id IN NUMBER,
40 p_param_name IN VARCHAR2,
41 p_batch_id IN NUMBER DEFAULT NULL,
42 p_mode IN VARCHAR2 DEFAULT NULL) RETURN NUMBER;
43
44 --for a session id returns the char configuration parameter value
45 --for the provided parameter name
46 --from ego_pub_ws_config table - For List mode
47 --from ego_pub_bat_params_b table - For Batch mode
48
49 FUNCTION Get_Char_Param_Value( p_session_id IN NUMBER,
50 p_param_name IN VARCHAR2,
51 p_batch_id IN NUMBER DEFAULT NULL,
52 p_mode IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2;
53
54 --for a session id returns the date configuration parameter value
55 --for the provided parameter name
56 --from ego_pub_ws_config table - For List mode
57 --from ego_pub_bat_params_b table - For Batch mode
58
59 FUNCTION Get_Char_Date_Value( p_session_id IN NUMBER,
60 p_param_name IN VARCHAR2,
61 p_batch_id IN NUMBER DEFAULT NULL,
62 p_mode IN VARCHAR2 DEFAULT NULL) RETURN DATE;
63
64 --Function that returns the specified ODI input parameter from table
65 --EGO_PUB_WS_PARAMS using the session_id.
66 --INPUT:
67 --p_session_id: unique identifier of input XML stored in table EGO_PUB_WS_PARAMS
68 --p_search_str: x_path expression pointing to parameter to be recovered
69 -- (e.g. '/ValuesetQueryParam/BatchId')
70 --OUTPUT:
71 --parameter value in VARCHAR or NULL if parameter specified does not exist
72
73 FUNCTION Get_ODI_Input_Parameter(p_session_id IN NUMBER, p_search_str IN VARCHAR2) RETURN VARCHAR2;
74
75 --returns the batch search string for the entity
76 --ODI scenario. The Batch Search String is needed to find out the
77 --invocation mode.
78
79 FUNCTION Get_Batch_Search_Str(p_odi_entscename IN VARCHAR2) RETURN VARCHAR2;
80
81 --returns the system search string for the entity
82 --ODI scenario. The System Search String is needed to find out the
83 --systems in list mode.
84
85 FUNCTION Get_System_Search_Str(p_odi_entscename IN VARCHAR2) RETURN VARCHAR2;
86
87
88 --returns the language search string for the entity
89 --ODI scenario. The Language Search String is needed to find out the
90 --languages in list mode.
91
92 FUNCTION Get_Language_Search_Str(p_odi_entscename IN VARCHAR2) RETURN VARCHAR2;
93
94 -- returns the mode in which the web service is being
95 -- invoked. The procedure has two output parameter,
96 -- a string describing the invocation mode
97 -- batch id
98 -- mode as follows:
99 --
100 -- 'BATCH' - The web service is being invoked by PIM publication
101 -- code triggered by publication framework GUI
102 -- 'LIST' - The web service is being invoked by PIM publication
103 -- code and the parameters are passed as list of items
104 -- 'NONE' - If none of the above modes were found in the payload
105
106 PROCEDURE Invocation_Mode( p_session_id IN NUMBER,
107 p_search_str IN VARCHAR2,
108 x_mode OUT NOCOPY VARCHAR2,
109 x_batch_id OUT NOCOPY NUMBER );
110
111
112 -- creates FND_Security config parameters
113 PROCEDURE Create_Fnd_Security( p_session_id IN NUMBER,
114 p_mode IN VARCHAR2,
115 p_batch_id IN NUMBER DEFAULT NULL,
116 p_web_service_name IN VARCHAR2);
117
118 --Procedure that returns parameter names for the web service
119 PROCEDURE Get_Parameter_Names(p_web_service_name IN VARCHAR2,
120 p_param_names IN OUT NOCOPY parameter_name_array_type
121 );
122
123 --Procedure that returns Xpath of the parameters for the web service
124 PROCEDURE Get_Xpath_Expr(p_web_service_name IN VARCHAR2,
125 p_xpath_expr IN OUT NOCOPY xpath_expr_array_type
126 );
127
128 PROCEDURE Init_Security(p_session_id IN NUMBER,
129 p_web_service_name IN VARCHAR2);
130
131 --Inserts system options in EGO_PUB_WS_CONFIG table
132 PROCEDURE Config_Systems(p_session_id IN NUMBER,
133 p_system_search_str IN VARCHAR2,
134 p_web_service_name VARCHAR2 DEFAULT NULL);
135
136 --Inserts language options in EGO_PUB_WS_CONFIG table
137 PROCEDURE Config_Languages(p_session_id IN NUMBER,
138 p_lang_search_str IN VARCHAR2,
139 p_web_service_name IN VARCHAR2 DEFAULT NULL);
140
141 ---
142 --- This function returns the next input identifier to be used
143 --- while inserting error records in EGO_PUB_WS_INPUT_IDENTIFIERS
144 --- the id used as input_id should be unique per session
145 ---
146 FUNCTION Get_Max_Input_Identifier ( p_session_id IN NUMBER) RETURN NUMBER;
147
148 -- ============================================================================
149 -- Name : Populate_Input_Identifier
150 -- Description : This procedure will be used to insert record for Input Identifier
151 --
152 -- Parameters:
153 -- IN :
154 -- p_session_id IN NUMBER
155 -- An Unique DB sequence generated at Java wrapper.
156 --
157 -- p_input_id IN VARCHAR2
158 -- Id of the Input Identifier.
159 --
160 -- p_param_name IN VARCHAR2
161 -- Name of the Parameter in the Identifier
162 --
163 -- p_param_value IN VARCHAR2
164 -- Value of the Parameter in the Identifier
165 -- ============================================================================
166
167 PROCEDURE Populate_Input_Identifier(p_session_id IN NUMBER,
168 p_input_id IN NUMBER,
169 p_param_name IN VARCHAR2,
170 p_param_value IN VARCHAR2);
171
172 -- ============================================================================
173 -- Name : Log_Error
174 -- Description : This procedure will be used to insert error record
175 --
176 -- Parameters:
177 -- IN :
178 -- p_session_id IN NUMBER
179 -- An Unique DB sequence generated at Java wrapper.
180 --
181 -- p_input_id IN VARCHAR2
182 -- Id of the Input Identifier for which the error occured.
183 --
184 -- p_err_code IN VARCHAR2
185 -- Error Code
186 --
187 -- p_err_message IN VARCHAR2
188 -- Error Message
189 -- ============================================================================
190
191 PROCEDURE Log_Error(p_session_id IN NUMBER,
192 p_input_id IN NUMBER,
193 p_err_code IN VARCHAR2,
194 p_err_message IN VARCHAR2);
195
196 PROCEDURE Write_Errors_ToBatFwk(p_session_id IN NUMBER,
197 p_batch_id IN NUMBER);
198
199 FUNCTION Get_Application_id(p_appl_short_name IN VARCHAR2) RETURN NUMBER;
200
201 PROCEDURE Generate_XML(p_session_id IN NUMBER, p_web_service_name IN VARCHAR2);
202
203
204 END EGO_PUB_WS_UTIL;