1 PACKAGE EGO_IMPORT_PVT AUTHID DEFINER AS
2 /* $Header: EGOVIMPS.pls 120.42 2007/10/05 15:12:06 dsakalle ship $ */
3
4 SUBTYPE FLAG IS VARCHAR2( 1 );
5
6 G_TEXT_DATA_TYPE CONSTANT FLAG := 'T';
7 G_NUMBER_DATA_TYPE CONSTANT FLAG := 'N';
8 G_DATE_DATA_TYPE CONSTANT FLAG := 'D';
9
10 G_UNPROC_PROC_STATUS CONSTANT FLAG := 'U';
11 G_READY_PROC_STATUS CONSTANT FLAG := 'R';
12 G_IMPORTED_PROC_STATUS CONSTANT FLAG := 'I';
13
14 G_PDH_SOURCE_SYSTEM_ID CONSTANT NUMBER := 7;
15
16 G_CONF_XREF CONSTANT MTL_SYSTEM_ITEMS_INTERFACE.CONFIRM_STATUS%TYPE := 'CC';
17 G_CONF_XREF_READY CONSTANT MTL_SYSTEM_ITEMS_INTERFACE.CONFIRM_STATUS%TYPE := 'CCR';
18 G_CONF_XREF_NOT_READY CONSTANT MTL_SYSTEM_ITEMS_INTERFACE.CONFIRM_STATUS%TYPE := 'CCN';
19 G_CONF_XREF_FAKE CONSTANT MTL_SYSTEM_ITEMS_INTERFACE.CONFIRM_STATUS%TYPE := 'CFC';
20
21 G_CONF_MATCH CONSTANT MTL_SYSTEM_ITEMS_INTERFACE.CONFIRM_STATUS%TYPE := 'CM';
22 G_CONF_MATCH_READY CONSTANT MTL_SYSTEM_ITEMS_INTERFACE.CONFIRM_STATUS%TYPE := 'CMR';
23 G_CONF_MATCH_NOT_READY CONSTANT MTL_SYSTEM_ITEMS_INTERFACE.CONFIRM_STATUS%TYPE := 'CMN';
24 G_CONF_MATCH_FAKE CONSTANT MTL_SYSTEM_ITEMS_INTERFACE.CONFIRM_STATUS%TYPE := 'CFM';
25 G_FAKE_MATCH_READY CONSTANT MTL_SYSTEM_ITEMS_INTERFACE.CONFIRM_STATUS%TYPE := 'FMR';
26
27 G_CONF_NEW CONSTANT MTL_SYSTEM_ITEMS_INTERFACE.CONFIRM_STATUS%TYPE := 'CN';
28 G_CONF_NEW_READY CONSTANT MTL_SYSTEM_ITEMS_INTERFACE.CONFIRM_STATUS%TYPE := 'CNR';
29 G_CONF_NEW_NOT_READY CONSTANT MTL_SYSTEM_ITEMS_INTERFACE.CONFIRM_STATUS%TYPE := 'CNN';
30
31 G_UNCONF_NONE_MATCH CONSTANT MTL_SYSTEM_ITEMS_INTERFACE.CONFIRM_STATUS%TYPE := 'UN';
32 G_UNCONF_SIGL_MATCH CONSTANT MTL_SYSTEM_ITEMS_INTERFACE.CONFIRM_STATUS%TYPE := 'US';
33 G_UNCONF_MULT_MATCH CONSTANT MTL_SYSTEM_ITEMS_INTERFACE.CONFIRM_STATUS%TYPE := 'UM';
34
35 G_UNCONF_NO_MATCH_FAKE CONSTANT MTL_SYSTEM_ITEMS_INTERFACE.CONFIRM_STATUS%TYPE := 'UFN';
36 G_UNCONF_SINGLE_MATCH_FAKE CONSTANT MTL_SYSTEM_ITEMS_INTERFACE.CONFIRM_STATUS%TYPE := 'UFS';
37 G_UNCONF_MULTI_MATCH_FAKE CONSTANT MTL_SYSTEM_ITEMS_INTERFACE.CONFIRM_STATUS%TYPE := 'UFM';
38 G_FAKE_CONF_STATUS_FLAG CONSTANT MTL_SYSTEM_ITEMS_INTERFACE.CONFIRM_STATUS%TYPE := 'FK';
39
40 G_EXCLUDED CONSTANT MTL_SYSTEM_ITEMS_INTERFACE.CONFIRM_STATUS%TYPE := 'EX';
41 G_FAKE_EXCLUDED CONSTANT MTL_SYSTEM_ITEMS_INTERFACE.CONFIRM_STATUS%TYPE := 'FEX';
42
43 G_UNCONF_ACTION CONSTANT VARCHAR2(2) := 'UA';
44 G_UNEXCLUDE_ACTION CONSTANT VARCHAR2(2) := 'UX';
45
46
47 /*
48 * This function returns the next item number for a sequence generated item number
49 */
50 FUNCTION GET_NEXT_ITEM_NUMBER(p_catalog_group_id NUMBER) RETURN VARCHAR2;
51
52 /*
53 ** This procedure Bulk loads the GTIN
54 */
55 PROCEDURE Process_Gtin_Intf_Rows(ERRBUF OUT NOCOPY VARCHAR2,
56 RETCODE OUT NOCOPY VARCHAR2,
57 p_data_set_id IN NUMBER);
58
59 /*
60 * API to Bulk Load Source system cross references
61 */
62 PROCEDURE Process_SSXref_Intf_Rows(ERRBUF OUT NOCOPY VARCHAR2,
63 RETCODE OUT NOCOPY VARCHAR2,
64 p_data_set_id IN NUMBER);
65 /*
66 * "Phase 2 API
67 * This method resolves source system item cross references
68 * immediately after MTL_SYSTEM_ITEMS_INTERFACE is populated and
69 * tries to detect discrepencies between user entered CONFIRM_STATUS
70 * (if any) and other data.
71 */
72 PROCEDURE Resolve_SSXref_On_Data_load( p_data_set_id IN NUMBER
73 , p_commit IN FLAG DEFAULT FND_API.G_TRUE
74 );
75
76 /*
77 * This method sets the confirm_status for an unprocessed row (process_flag = 0) in the master org
78 * for a source system item identified by p_source_system_id and p_source_system_reference.
79 * p_status can have the following values:
80 * G_CONF_XREF
81 * G_CONF_MATCH
82 * G_CONF_NEW
83 * G_UNCONF_NONE_MATCH
84 * G_UNCONF_SIGL_MATCH
85 * G_UNCONF_MULT_MATCH
86 */
87 PROCEDURE Set_Confirm_Status(p_data_set_id IN NUMBER,
88 p_source_system_id IN VARCHAR2,
89 p_source_system_reference IN VARCHAR2,
90 p_status IN VARCHAR2,
91 p_inventory_item_id IN NUMBER DEFAULT NULL,
92 p_organization_id IN NUMBER DEFAULT NULL);
93
94
95 /* R12C: Introduced
96 * This method calls Get_Confirm_Status and returns String 'ImportReady'
97 * or 'NotImportReady'. This is added fro R12C enhancement where we have
98 * concept of bundles and all items should be ready for import for the bundle
99 * to be ready for import.
100 */
101 FUNCTION Get_Import_Ready_Status ( p_data_set_id IN NUMBER,
102 p_source_system_id IN VARCHAR2,
103 p_source_system_reference IN VARCHAR2,
104 p_bundle_id IN NUMBER
105 )
106 RETURN VARCHAR2;
107
108
109 /* R12C: Changed signature
110 * This method gets the confirm status plus the extra letter to indicate
111 * whether an item is ready to be passed on to IOI
112 */
113 FUNCTION Get_Confirm_Status(p_data_set_id IN NUMBER,
114 p_source_system_id IN VARCHAR2,
115 p_source_system_reference IN VARCHAR2,
116 p_bundle_id IN NUMBER)
117 RETURN VARCHAR2;
118
119 PROCEDURE Get_Item_Num_Desc_Gen_Method(p_item_catalog_group_id IN NUMBER,
120 x_item_num_gen_method OUT NOCOPY VARCHAR2,
121 x_item_desc_gen_method OUT NOCOPY VARCHAR2);
122
123 /*
124 * This method returns the seeded SOURCE_SYSTEM_ID for PDH
125 */
126 FUNCTION Get_PDH_Source_System_Id RETURN NUMBER;
127
128
129 /*
130 * "Phase 2 API
131 * This method populates the child entities with PK values.
132 * This method populates the other interface tables like MTL_ITEM_REVISION_INTERFACE,
133 * EGO_ITEM_PEOPLE_INTF, MTL_ITEM_CATEGORIES_INTERFACE, EGO_ITM_USR_ATTR_INTRFC etc.
134 * with the inventory item id/number and organization id/code.
135 */
136 PROCEDURE Resolve_Child_Entities( p_data_set_id IN NUMBER
137 , p_commit IN FLAG DEFAULT FND_API.G_TRUE
138 );
139
140 PROCEDURE Stamp_RequestId_For_ReImport( p_request_id IN MTL_SYSTEM_ITEMS_INTERFACE.REQUEST_ID%TYPE );
141
142 PROCEDURE Stamp_Row_RequestId( p_request_id IN MTL_SYSTEM_ITEMS_INTERFACE.REQUEST_ID%TYPE
143 , p_target_rowid IN UROWID
144 );
145
146 PROCEDURE Log_Error_For_ReImport(p_request_id IN MTL_SYSTEM_ITEMS_INTERFACE.REQUEST_ID%TYPE
147 , p_target_rowid IN UROWID
148 , p_err_msg IN VARCHAR2
149 );
150
151 PROCEDURE Prepare_Row_For_ReImport
152 ( p_batch_id IN MTL_SYSTEM_ITEMS_INTERFACE.SET_PROCESS_ID%TYPE
153 , p_organization_id IN MTL_SYSTEM_ITEMS_INTERFACE.ORGANIZATION_ID%TYPE
154 , p_target_rowid IN UROWID
155 , x_return_code OUT NOCOPY NUMBER
156 , x_err_msg OUT NOCOPY VARCHAR2
157 );
158
159 --=================================================================================================================--
160 --------------------------------------- Start of Merging Section ----------------------------------------------------
161 --=================================================================================================================--
162 /*
163 * The procedures in this section, both public and private, relate to the task of identifying the rows in various
164 * item interface tables that have the same keys and need to be merged - i.e. collapsed into one row. The result of
165 * the merging operation on a subset of the table should be that there is at most one row for any set of keys.
166 *
167 *
168 * All the MERGE_* procedures take the following arguments:
169 * p_batch_id IN NUMBER =>
170 * The batch identifier (MANDATORY).
171 * p_is_pdh_batch IN FLAG DEFAULT NULL =>
172 * Used to determine the set of keys to use for merging.
173 * - Pass FND_API.G_TRUE to indicate that the batch is a PIMDH batch
174 * - Pass FND_API.G_FALSE to indicate that the batch is a non-PIMDH batch
175 * - If not passed, the batch header will be used to determine whether or not
176 * the batch is a PIMDH batch (absence of a header implies that it is).
177 * p_master_org_id IN NUMBER DEFAULT NULL =>
178 * The ID of the default batch organization, to be used for rows for which neither
179 * ORGANIZATION_ID nor ORGANIZATION_CODE are provided.
180 * If not passed, the ORGANIZATION_ID in the batch header will be used.
181 *
182 * p_commit IN FLAG DEFAULT FND_API.G_FALSE =>
183 * Pass FND_API.G_TRUE to have a COMMIT issued at the end of the procedure.
184 */
185
186
187 --------------------------------------------------------------------------------------------
188 -- Procedure MERGE_ATTRS
189 -- Merges item- and revision-level single row attributes found in the extension
190 -- attributes interface table (EGO_ITM_USR_ATTR_INTRFC)
191 --------------------------------------------------------------------------------------------
192 PROCEDURE merge_attrs ( p_batch_id IN NUMBER
193 , p_is_pdh_batch IN FLAG DEFAULT NULL
194 , p_ss_id IN NUMBER DEFAULT NULL
195 , p_master_org_id IN NUMBER DEFAULT NULL
196 , p_commit IN FLAG DEFAULT FND_API.G_FALSE
197 );
198 --------------------------------------------------------------------------------------------
199 -- Procedure MERGE_REVS
200 -- Merges unprocessed revisions found in the revisions interface table
201 -- (MTL_ITEM_REVISIONS_INTERFACE)
202 --------------------------------------------------------------------------------------------
203 PROCEDURE merge_revs( p_batch_id IN NUMBER
204 , p_is_pdh_batch IN FLAG DEFAULT NULL
205 , p_ss_id IN NUMBER DEFAULT NULL
206 , p_master_org_id IN NUMBER DEFAULT NULL
207 , p_commit IN FLAG DEFAULT FND_API.G_FALSE
208 );
209
210 --------------------------------------------------------------------------------------------
211 -- Procedure MERGE_ITEMS
212 -- Merges unprocessed items found in the items interface table
213 -- (MTL_SYSTEM_ITEMS_INTERFACE)
214 --------------------------------------------------------------------------------------------
215 PROCEDURE merge_items ( p_batch_id IN NUMBER
216 , p_is_pdh_batch IN FLAG DEFAULT NULL
217 , p_ss_id IN NUMBER DEFAULT NULL
218 , p_master_org_id IN NUMBER DEFAULT NULL
219 , p_commit IN FLAG DEFAULT FND_API.G_FALSE
220 );
221
222 --------------------------------------------------------------------------------------------
223 -- Procedure MERGE_BATCH
224 -- Wrapper procedure that calls the other MERGE_* procedures
225 --------------------------------------------------------------------------------------------
226 PROCEDURE merge_batch ( p_batch_id IN NUMBER
227 , p_is_pdh_batch IN FLAG DEFAULT NULL
228 , p_ss_id IN NUMBER DEFAULT NULL
229 , p_master_org_id IN NUMBER DEFAULT NULL
230 , p_commit IN FLAG DEFAULT FND_API.G_FALSE
231 );
232
233 --=================================================================================================================--
234 --------------------------------------- End of Merging Section ----------------------------------------------------
235 --=================================================================================================================--
236
237 ------------------------------------------------------------------------------------------
238 -- This function returns the batch status of a batch --
239 ------------------------------------------------------------------------------------------
240 FUNCTION GET_BATCH_STATUS(p_batch_id NUMBER) RETURN VARCHAR2;
241
242
243 --------------------------------------------------------------------------------------------
244 -- Function WRAPPED_TO_NUMBER --
245 -- Wraps the to_number built-in to return null in case of conversion failure --
246 --------------------------------------------------------------------------------------------
247 FUNCTION WRAPPED_TO_NUMBER( p_val VARCHAR2 )
248 RETURN NUMBER
249 DETERMINISTIC;
250
251 --------------------------------------------------------------------------------------------
252 -- Function WRAPPED_TO_UOM --
253 -- Wraps inv_convert.uom_conversion to return null in case of conversion failure. --
254 -- If both of the from_uom params are null, no attempt to make the conversion is --
255 -- performed. --
256 --------------------------------------------------------------------------------------------
257 FUNCTION WRAPPED_TO_UOM( p_val NUMBER
258 , p_to_uom_code EGO_ITM_USR_ATTR_INTRFC.ATTR_VALUE_UOM%TYPE
259 , p_from_uom_code EGO_ITM_USR_ATTR_INTRFC.ATTR_VALUE_UOM%TYPE
260 , p_from_uom_value EGO_ITM_USR_ATTR_INTRFC.ATTR_UOM_DISP_VALUE%TYPE
261 , p_inventory_item_id EGO_ITM_USR_ATTR_INTRFC.INVENTORY_ITEM_ID%TYPE DEFAULT NULL
262 )
263 RETURN NUMBER;
264
265 --------------------------------------------------------------------------------------------
266 -- Function WRAPPED_TO_NUMBER --
267 -- Wraps the to_date built-in to return null in case of conversion failure --
268 --------------------------------------------------------------------------------------------
269 FUNCTION WRAPPED_TO_DATE( p_val VARCHAR2 )
270 RETURN DATE
271 DETERMINISTIC;
272
273 --------------------------------------------------------------------------------------------
274 -- Function GET_REV_USR_ATTR --
275 -- Returns the display value of the specified revision attribute; if there is no --
276 -- display value, it returns the appropriate value column, based on the --
277 -- p_attr_value_type parameter --
278 -- Used by matching program --
279 --------------------------------------------------------------------------------------------
280 FUNCTION GET_REV_USR_ATTR
281 (
282 p_batch_id IN EGO_ITM_USR_ATTR_INTRFC.DATA_SET_ID%TYPE
283 , p_source_system_id IN EGO_ITM_USR_ATTR_INTRFC.SOURCE_SYSTEM_ID%TYPE
284 , p_source_system_reference IN EGO_ITM_USR_ATTR_INTRFC.SOURCE_SYSTEM_REFERENCE%TYPE
285 , p_organization_id IN EGO_ITM_USR_ATTR_INTRFC.ORGANIZATION_ID%TYPE
289 , p_attr_name IN EGO_ITM_USR_ATTR_INTRFC.ATTR_INT_NAME%TYPE
286 , p_revision_code IN EGO_ITM_USR_ATTR_INTRFC.REVISION%TYPE
287 , p_attr_group_type IN EGO_ITM_USR_ATTR_INTRFC.ATTR_GROUP_TYPE%TYPE
288 , p_attr_group_name IN EGO_ITM_USR_ATTR_INTRFC.ATTR_GROUP_INT_NAME%TYPE
290 , p_attr_value_type IN FLAG
291 )
292 RETURN EGO_ITM_USR_ATTR_INTRFC.ATTR_DISP_VALUE%TYPE;
293
294 ----------------------------------------------------------------------------------------------
295 -- Function GET_REV_USR_ATTR_DISP
296 -- Returns the display value of the specified revision attribute, if the attribute is present
297 -- in the interface table, or the internal value, interpreted as a display value.
298 -- The assumption is that this will be called from a value set context ...
299 ----------------------------------------------------------------------------------------------
300 FUNCTION GET_REV_USR_ATTR_SS_DISP
301 (
302 p_batch_id IN EGO_ITM_USR_ATTR_INTRFC.DATA_SET_ID%TYPE
303 , p_source_system_id IN EGO_ITM_USR_ATTR_INTRFC.SOURCE_SYSTEM_ID%TYPE
304 , p_source_system_reference IN EGO_ITM_USR_ATTR_INTRFC.SOURCE_SYSTEM_REFERENCE%TYPE
305 , p_organization_id IN EGO_ITM_USR_ATTR_INTRFC.ORGANIZATION_ID%TYPE
306 , p_revision_code IN EGO_ITM_USR_ATTR_INTRFC.REVISION%TYPE
307 , p_attr_group_type IN EGO_ITM_USR_ATTR_INTRFC.ATTR_GROUP_TYPE%TYPE
308 , p_attr_group_name IN EGO_ITM_USR_ATTR_INTRFC.ATTR_GROUP_INT_NAME%TYPE
309 , p_attr_name IN EGO_ITM_USR_ATTR_INTRFC.ATTR_INT_NAME%TYPE
310 , p_attr_type IN FLAG
311 , p_from_internal_column IN FLAG
312 , p_do_processed_rows_flag IN FLAG DEFAULT FND_API.G_FALSE
313 , p_request_id IN EGO_ITM_USR_ATTR_INTRFC.REQUEST_ID%TYPE DEFAULT NULL
314 )
315 RETURN EGO_ITM_USR_ATTR_INTRFC.ATTR_DISP_VALUE%TYPE;
316
317 FUNCTION GET_REV_USR_ATTR_PDH_DISP
318 (
319 p_batch_id IN EGO_ITM_USR_ATTR_INTRFC.DATA_SET_ID%TYPE
320 , p_inventory_item_id IN EGO_ITM_USR_ATTR_INTRFC.INVENTORY_ITEM_ID%TYPE
321 , p_item_number IN EGO_ITM_USR_ATTR_INTRFC.ITEM_NUMBER%TYPE
322 , p_organization_id IN EGO_ITM_USR_ATTR_INTRFC.ORGANIZATION_ID%TYPE
323 , p_revision_code IN EGO_ITM_USR_ATTR_INTRFC.REVISION%TYPE
324 , p_attr_group_type IN EGO_ITM_USR_ATTR_INTRFC.ATTR_GROUP_TYPE%TYPE
325 , p_attr_group_name IN EGO_ITM_USR_ATTR_INTRFC.ATTR_GROUP_INT_NAME%TYPE
326 , p_attr_name IN EGO_ITM_USR_ATTR_INTRFC.ATTR_INT_NAME%TYPE
327 , p_attr_type IN FLAG
328 , p_from_internal_column IN FLAG
329 , p_do_processed_rows_flag IN FLAG DEFAULT FND_API.G_FALSE
330 , p_request_id IN EGO_ITM_USR_ATTR_INTRFC.REQUEST_ID%TYPE DEFAULT NULL
331 )
332 RETURN EGO_ITM_USR_ATTR_INTRFC.ATTR_DISP_VALUE%TYPE;
333
334 FUNCTION GET_REV_USR_ATTR_TO_CHAR
335 (
336 p_batch_id IN EGO_ITM_USR_ATTR_INTRFC.DATA_SET_ID%TYPE
337 , p_source_system_id IN EGO_ITM_USR_ATTR_INTRFC.SOURCE_SYSTEM_ID%TYPE DEFAULT NULL
338 , p_source_system_reference IN EGO_ITM_USR_ATTR_INTRFC.SOURCE_SYSTEM_REFERENCE%TYPE DEFAULT NULL
339 , p_organization_id IN EGO_ITM_USR_ATTR_INTRFC.ORGANIZATION_ID%TYPE
340 , p_revision_code IN EGO_ITM_USR_ATTR_INTRFC.REVISION%TYPE
341 , p_attr_group_type IN EGO_ITM_USR_ATTR_INTRFC.ATTR_GROUP_TYPE%TYPE
342 , p_attr_group_name IN EGO_ITM_USR_ATTR_INTRFC.ATTR_GROUP_INT_NAME%TYPE
343 , p_attr_name IN EGO_ITM_USR_ATTR_INTRFC.ATTR_INT_NAME%TYPE
344 , p_do_processed_rows_flag IN FLAG DEFAULT FND_API.G_FALSE
345 , p_request_id IN EGO_ITM_USR_ATTR_INTRFC.REQUEST_ID%TYPE DEFAULT NULL
346 , p_inventory_item_id IN EGO_ITM_USR_ATTR_INTRFC.INVENTORY_ITEM_ID%TYPE DEFAULT NULL
347 , p_item_number IN EGO_ITM_USR_ATTR_INTRFC.ITEM_NUMBER%TYPE DEFAULT NULL
348 , p_use_pdh_keys_to_join IN BOOLEAN
349 , p_get_value_col IN BOOLEAN
350 , p_attr_type IN FLAG
351 , p_attr_miss_value IN BOOLEAN DEFAULT TRUE
352 )
353 RETURN EGO_ITM_USR_ATTR_INTRFC.ATTR_DISP_VALUE%TYPE;
354
355 ------------------------------------------------------------------------------------------------
356 -- Function GET_REV_USR_ATTR_STR --
357 -- Returns the string value of the specified revision attribute, if the attribute is present --
358 -- in the interface table. --
359 ------------------------------------------------------------------------------------------------
360 FUNCTION GET_REV_USR_ATTR_SS_VSTR
361 (
362 p_batch_id IN EGO_ITM_USR_ATTR_INTRFC.DATA_SET_ID%TYPE
363 , p_source_system_id IN EGO_ITM_USR_ATTR_INTRFC.SOURCE_SYSTEM_ID%TYPE
364 , p_source_system_reference IN EGO_ITM_USR_ATTR_INTRFC.SOURCE_SYSTEM_REFERENCE%TYPE
365 , p_organization_id IN EGO_ITM_USR_ATTR_INTRFC.ORGANIZATION_ID%TYPE
369 , p_attr_name IN EGO_ITM_USR_ATTR_INTRFC.ATTR_INT_NAME%TYPE
366 , p_revision_code IN EGO_ITM_USR_ATTR_INTRFC.REVISION%TYPE
367 , p_attr_group_type IN EGO_ITM_USR_ATTR_INTRFC.ATTR_GROUP_TYPE%TYPE
368 , p_attr_group_name IN EGO_ITM_USR_ATTR_INTRFC.ATTR_GROUP_INT_NAME%TYPE
370 , p_do_processed_rows_flag IN FLAG DEFAULT FND_API.G_FALSE
371 , p_request_id IN EGO_ITM_USR_ATTR_INTRFC.REQUEST_ID%TYPE DEFAULT NULL
372 )
373 RETURN EGO_ITM_USR_ATTR_INTRFC.ATTR_VALUE_STR%TYPE;
374
375 FUNCTION GET_REV_USR_ATTR_PDH_VSTR
376 (
377 p_batch_id IN EGO_ITM_USR_ATTR_INTRFC.DATA_SET_ID%TYPE
378 , p_inventory_item_id IN EGO_ITM_USR_ATTR_INTRFC.INVENTORY_ITEM_ID%TYPE
379 , p_item_number IN EGO_ITM_USR_ATTR_INTRFC.ITEM_NUMBER%TYPE
380 , p_organization_id IN EGO_ITM_USR_ATTR_INTRFC.ORGANIZATION_ID%TYPE
381 , p_revision_code IN EGO_ITM_USR_ATTR_INTRFC.REVISION%TYPE
382 , p_attr_group_type IN EGO_ITM_USR_ATTR_INTRFC.ATTR_GROUP_TYPE%TYPE
383 , p_attr_group_name IN EGO_ITM_USR_ATTR_INTRFC.ATTR_GROUP_INT_NAME%TYPE
384 , p_attr_name IN EGO_ITM_USR_ATTR_INTRFC.ATTR_INT_NAME%TYPE
385 , p_do_processed_rows_flag IN FLAG DEFAULT FND_API.G_FALSE
386 , p_request_id IN EGO_ITM_USR_ATTR_INTRFC.REQUEST_ID%TYPE DEFAULT NULL
387 )
388 RETURN EGO_ITM_USR_ATTR_INTRFC.ATTR_VALUE_STR%TYPE;
389
390 ------------------------------------------------------------------------------------------------
391 -- Function GET_REV_USR_ATTR_DATE --
392 -- Returns the date value of the specified revision attribute, if the attribute is present --
393 -- in the interface table. --
394 ------------------------------------------------------------------------------------------------
395 FUNCTION GET_REV_USR_ATTR_SS_VDATE
396 (
397 p_batch_id IN EGO_ITM_USR_ATTR_INTRFC.DATA_SET_ID%TYPE
398 , p_source_system_id IN EGO_ITM_USR_ATTR_INTRFC.SOURCE_SYSTEM_ID%TYPE
399 , p_source_system_reference IN EGO_ITM_USR_ATTR_INTRFC.SOURCE_SYSTEM_REFERENCE%TYPE
400 , p_organization_id IN EGO_ITM_USR_ATTR_INTRFC.ORGANIZATION_ID%TYPE
401 , p_revision_code IN EGO_ITM_USR_ATTR_INTRFC.REVISION%TYPE
402 , p_attr_group_type IN EGO_ITM_USR_ATTR_INTRFC.ATTR_GROUP_TYPE%TYPE
403 , p_attr_group_name IN EGO_ITM_USR_ATTR_INTRFC.ATTR_GROUP_INT_NAME%TYPE
404 , p_attr_name IN EGO_ITM_USR_ATTR_INTRFC.ATTR_INT_NAME%TYPE
405 , p_do_processed_rows_flag IN FLAG DEFAULT FND_API.G_FALSE
406 , p_request_id IN EGO_ITM_USR_ATTR_INTRFC.REQUEST_ID%TYPE DEFAULT NULL
407 )
408 RETURN EGO_ITM_USR_ATTR_INTRFC.ATTR_VALUE_DATE%TYPE;
409
410 FUNCTION GET_REV_USR_ATTR_PDH_VDATE
411 (
412 p_batch_id IN EGO_ITM_USR_ATTR_INTRFC.DATA_SET_ID%TYPE
413 , p_inventory_item_id IN EGO_ITM_USR_ATTR_INTRFC.INVENTORY_ITEM_ID%TYPE
414 , p_item_number IN EGO_ITM_USR_ATTR_INTRFC.ITEM_NUMBER%TYPE
415 , p_organization_id IN EGO_ITM_USR_ATTR_INTRFC.ORGANIZATION_ID%TYPE
416 , p_revision_code IN EGO_ITM_USR_ATTR_INTRFC.REVISION%TYPE
417 , p_attr_group_type IN EGO_ITM_USR_ATTR_INTRFC.ATTR_GROUP_TYPE%TYPE
418 , p_attr_group_name IN EGO_ITM_USR_ATTR_INTRFC.ATTR_GROUP_INT_NAME%TYPE
419 , p_attr_name IN EGO_ITM_USR_ATTR_INTRFC.ATTR_INT_NAME%TYPE
420 , p_do_processed_rows_flag IN FLAG DEFAULT FND_API.G_FALSE
421 , p_request_id IN EGO_ITM_USR_ATTR_INTRFC.REQUEST_ID%TYPE DEFAULT NULL
422 )
423 RETURN EGO_ITM_USR_ATTR_INTRFC.ATTR_VALUE_DATE%TYPE;
424
425 ------------------------------------------------------------------------------------------------
426 -- Function GET_REV_USR_ATTR_NUM --
427 -- Returns the number value of the specified revision attribute, if the attribute is present --
428 -- in the interface table. --
429 -- p_output_uom_code parameter is ignored (no uom conversions performed) --
430 ------------------------------------------------------------------------------------------------
431 FUNCTION GET_REV_USR_ATTR_SS_VNUM
432 (
433 p_batch_id IN EGO_ITM_USR_ATTR_INTRFC.DATA_SET_ID%TYPE
434 , p_source_system_id IN EGO_ITM_USR_ATTR_INTRFC.SOURCE_SYSTEM_ID%TYPE
435 , p_source_system_reference IN EGO_ITM_USR_ATTR_INTRFC.SOURCE_SYSTEM_REFERENCE%TYPE
436 , p_organization_id IN EGO_ITM_USR_ATTR_INTRFC.ORGANIZATION_ID%TYPE
437 , p_revision_code IN EGO_ITM_USR_ATTR_INTRFC.REVISION%TYPE
438 , p_attr_group_type IN EGO_ITM_USR_ATTR_INTRFC.ATTR_GROUP_TYPE%TYPE
439 , p_attr_group_name IN EGO_ITM_USR_ATTR_INTRFC.ATTR_GROUP_INT_NAME%TYPE
440 , p_attr_name IN EGO_ITM_USR_ATTR_INTRFC.ATTR_INT_NAME%TYPE
441 , p_output_uom_code IN EGO_ITM_USR_ATTR_INTRFC.ATTR_VALUE_UOM%TYPE
442 , p_do_processed_rows_flag IN FLAG DEFAULT FND_API.G_FALSE
446
443 , p_request_id IN EGO_ITM_USR_ATTR_INTRFC.REQUEST_ID%TYPE DEFAULT NULL
444 )
445 RETURN EGO_ITM_USR_ATTR_INTRFC.ATTR_VALUE_NUM%TYPE;
447 FUNCTION GET_REV_USR_ATTR_PDH_VNUM
448 (
449 p_batch_id IN EGO_ITM_USR_ATTR_INTRFC.DATA_SET_ID%TYPE
450 , p_inventory_item_id IN EGO_ITM_USR_ATTR_INTRFC.INVENTORY_ITEM_ID%TYPE
451 , p_item_number IN EGO_ITM_USR_ATTR_INTRFC.ITEM_NUMBER%TYPE
452 , p_organization_id IN EGO_ITM_USR_ATTR_INTRFC.ORGANIZATION_ID%TYPE
453 , p_revision_code IN EGO_ITM_USR_ATTR_INTRFC.REVISION%TYPE
454 , p_attr_group_type IN EGO_ITM_USR_ATTR_INTRFC.ATTR_GROUP_TYPE%TYPE
455 , p_attr_group_name IN EGO_ITM_USR_ATTR_INTRFC.ATTR_GROUP_INT_NAME%TYPE
456 , p_attr_name IN EGO_ITM_USR_ATTR_INTRFC.ATTR_INT_NAME%TYPE
457 , p_output_uom_code IN EGO_ITM_USR_ATTR_INTRFC.ATTR_VALUE_UOM%TYPE
458 , p_do_processed_rows_flag IN FLAG DEFAULT FND_API.G_FALSE
459 , p_request_id IN EGO_ITM_USR_ATTR_INTRFC.REQUEST_ID%TYPE DEFAULT NULL
460 )
461 RETURN EGO_ITM_USR_ATTR_INTRFC.ATTR_VALUE_NUM%TYPE;
462
463 ------------------------------------------------------------------------------------------------
464 -- Function GET_REV_USR_ATTR_DISP_STR --
465 -- Returns the string value of the specified revision attribute, if the attribute is present --
466 -- in the interface table, merging in an attempted conversion of the display column content. --
467 ------------------------------------------------------------------------------------------------
468 FUNCTION GET_REV_USR_ATTR_SS_DSTR
469 (
470 p_batch_id IN EGO_ITM_USR_ATTR_INTRFC.DATA_SET_ID%TYPE
471 , p_source_system_id IN EGO_ITM_USR_ATTR_INTRFC.SOURCE_SYSTEM_ID%TYPE
472 , p_source_system_reference IN EGO_ITM_USR_ATTR_INTRFC.SOURCE_SYSTEM_REFERENCE%TYPE
473 , p_organization_id IN EGO_ITM_USR_ATTR_INTRFC.ORGANIZATION_ID%TYPE
474 , p_revision_code IN EGO_ITM_USR_ATTR_INTRFC.REVISION%TYPE
475 , p_attr_group_type IN EGO_ITM_USR_ATTR_INTRFC.ATTR_GROUP_TYPE%TYPE
476 , p_attr_group_name IN EGO_ITM_USR_ATTR_INTRFC.ATTR_GROUP_INT_NAME%TYPE
477 , p_attr_name IN EGO_ITM_USR_ATTR_INTRFC.ATTR_INT_NAME%TYPE
478 , p_do_processed_rows_flag IN FLAG DEFAULT FND_API.G_FALSE
479 , p_request_id IN EGO_ITM_USR_ATTR_INTRFC.REQUEST_ID%TYPE DEFAULT NULL
480 )
481 RETURN EGO_ITM_USR_ATTR_INTRFC.ATTR_VALUE_STR%TYPE;
482
483 FUNCTION GET_REV_USR_ATTR_PDH_DSTR
484 (
485 p_batch_id IN EGO_ITM_USR_ATTR_INTRFC.DATA_SET_ID%TYPE
486 , p_inventory_item_id IN EGO_ITM_USR_ATTR_INTRFC.INVENTORY_ITEM_ID%TYPE
487 , p_item_number IN EGO_ITM_USR_ATTR_INTRFC.ITEM_NUMBER%TYPE
488 , p_organization_id IN EGO_ITM_USR_ATTR_INTRFC.ORGANIZATION_ID%TYPE
489 , p_revision_code IN EGO_ITM_USR_ATTR_INTRFC.REVISION%TYPE
490 , p_attr_group_type IN EGO_ITM_USR_ATTR_INTRFC.ATTR_GROUP_TYPE%TYPE
491 , p_attr_group_name IN EGO_ITM_USR_ATTR_INTRFC.ATTR_GROUP_INT_NAME%TYPE
492 , p_attr_name IN EGO_ITM_USR_ATTR_INTRFC.ATTR_INT_NAME%TYPE
493 , p_do_processed_rows_flag IN FLAG DEFAULT FND_API.G_FALSE
494 , p_request_id IN EGO_ITM_USR_ATTR_INTRFC.REQUEST_ID%TYPE DEFAULT NULL
495 )
496 RETURN EGO_ITM_USR_ATTR_INTRFC.ATTR_VALUE_STR%TYPE;
497
498 ------------------------------------------------------------------------------------------------
499 -- Function GET_REV_USR_ATTR_DISP_DATE --
500 -- Returns the date value of the specified revision attribute, if the attribute is present --
501 -- in the interface table, merging in an attempted conversion of the display column content. --
502 ------------------------------------------------------------------------------------------------
503 FUNCTION GET_REV_USR_ATTR_SS_DDATE
504 (
505 p_batch_id IN EGO_ITM_USR_ATTR_INTRFC.DATA_SET_ID%TYPE
506 , p_source_system_id IN EGO_ITM_USR_ATTR_INTRFC.SOURCE_SYSTEM_ID%TYPE
507 , p_source_system_reference IN EGO_ITM_USR_ATTR_INTRFC.SOURCE_SYSTEM_REFERENCE%TYPE
508 , p_organization_id IN EGO_ITM_USR_ATTR_INTRFC.ORGANIZATION_ID%TYPE
509 , p_revision_code IN EGO_ITM_USR_ATTR_INTRFC.REVISION%TYPE
510 , p_attr_group_type IN EGO_ITM_USR_ATTR_INTRFC.ATTR_GROUP_TYPE%TYPE
511 , p_attr_group_name IN EGO_ITM_USR_ATTR_INTRFC.ATTR_GROUP_INT_NAME%TYPE
512 , p_attr_name IN EGO_ITM_USR_ATTR_INTRFC.ATTR_INT_NAME%TYPE
513 , p_do_processed_rows_flag IN FLAG DEFAULT FND_API.G_FALSE
514 , p_request_id IN EGO_ITM_USR_ATTR_INTRFC.REQUEST_ID%TYPE DEFAULT NULL
515 )
516 RETURN EGO_ITM_USR_ATTR_INTRFC.ATTR_VALUE_DATE%TYPE;
517
518 FUNCTION GET_REV_USR_ATTR_PDH_DDATE
519 (
520 p_batch_id IN EGO_ITM_USR_ATTR_INTRFC.DATA_SET_ID%TYPE
521 , p_inventory_item_id IN EGO_ITM_USR_ATTR_INTRFC.INVENTORY_ITEM_ID%TYPE
522 , p_item_number IN EGO_ITM_USR_ATTR_INTRFC.ITEM_NUMBER%TYPE
526 , p_attr_group_name IN EGO_ITM_USR_ATTR_INTRFC.ATTR_GROUP_INT_NAME%TYPE
523 , p_organization_id IN EGO_ITM_USR_ATTR_INTRFC.ORGANIZATION_ID%TYPE
524 , p_revision_code IN EGO_ITM_USR_ATTR_INTRFC.REVISION%TYPE
525 , p_attr_group_type IN EGO_ITM_USR_ATTR_INTRFC.ATTR_GROUP_TYPE%TYPE
527 , p_attr_name IN EGO_ITM_USR_ATTR_INTRFC.ATTR_INT_NAME%TYPE
528 , p_do_processed_rows_flag IN FLAG DEFAULT FND_API.G_FALSE
529 , p_request_id IN EGO_ITM_USR_ATTR_INTRFC.REQUEST_ID%TYPE DEFAULT NULL
530 )
531 RETURN EGO_ITM_USR_ATTR_INTRFC.ATTR_VALUE_DATE%TYPE;
532
533 ------------------------------------------------------------------------------------------------
534 -- Function GET_REV_USR_ATTR_DISP_NUM --
535 -- Returns the number value of the specified revision attribute, if the attribute is present --
536 -- in the interface table, merging in an attempted conversion of the display column content. --
537 ------------------------------------------------------------------------------------------------
538 FUNCTION GET_REV_USR_ATTR_SS_DNUM
539 (
540 p_batch_id IN EGO_ITM_USR_ATTR_INTRFC.DATA_SET_ID%TYPE
541 , p_source_system_id IN EGO_ITM_USR_ATTR_INTRFC.SOURCE_SYSTEM_ID%TYPE
542 , p_source_system_reference IN EGO_ITM_USR_ATTR_INTRFC.SOURCE_SYSTEM_REFERENCE%TYPE
543 , p_organization_id IN EGO_ITM_USR_ATTR_INTRFC.ORGANIZATION_ID%TYPE
544 , p_revision_code IN EGO_ITM_USR_ATTR_INTRFC.REVISION%TYPE
545 , p_attr_group_type IN EGO_ITM_USR_ATTR_INTRFC.ATTR_GROUP_TYPE%TYPE
546 , p_attr_group_name IN EGO_ITM_USR_ATTR_INTRFC.ATTR_GROUP_INT_NAME%TYPE
547 , p_attr_name IN EGO_ITM_USR_ATTR_INTRFC.ATTR_INT_NAME%TYPE
548 , p_output_uom_code IN EGO_ITM_USR_ATTR_INTRFC.ATTR_VALUE_UOM%TYPE
549 , p_do_processed_rows_flag IN FLAG DEFAULT FND_API.G_FALSE
550 , p_request_id IN EGO_ITM_USR_ATTR_INTRFC.REQUEST_ID%TYPE DEFAULT NULL
551 )
552 RETURN EGO_ITM_USR_ATTR_INTRFC.ATTR_VALUE_NUM%TYPE;
553
554 FUNCTION GET_REV_USR_ATTR_PDH_DNUM
555 (
556 p_batch_id IN EGO_ITM_USR_ATTR_INTRFC.DATA_SET_ID%TYPE
557 , p_inventory_item_id IN EGO_ITM_USR_ATTR_INTRFC.INVENTORY_ITEM_ID%TYPE
558 , p_item_number IN EGO_ITM_USR_ATTR_INTRFC.ITEM_NUMBER%TYPE
559 , p_organization_id IN EGO_ITM_USR_ATTR_INTRFC.ORGANIZATION_ID%TYPE
560 , p_revision_code IN EGO_ITM_USR_ATTR_INTRFC.REVISION%TYPE
561 , p_attr_group_type IN EGO_ITM_USR_ATTR_INTRFC.ATTR_GROUP_TYPE%TYPE
562 , p_attr_group_name IN EGO_ITM_USR_ATTR_INTRFC.ATTR_GROUP_INT_NAME%TYPE
563 , p_attr_name IN EGO_ITM_USR_ATTR_INTRFC.ATTR_INT_NAME%TYPE
564 , p_output_uom_code IN EGO_ITM_USR_ATTR_INTRFC.ATTR_VALUE_UOM%TYPE
565 , p_do_processed_rows_flag IN FLAG DEFAULT FND_API.G_FALSE
566 , p_request_id IN EGO_ITM_USR_ATTR_INTRFC.REQUEST_ID%TYPE DEFAULT NULL
567
568 )
569 RETURN EGO_ITM_USR_ATTR_INTRFC.ATTR_VALUE_NUM%TYPE;
570
571 ------------------------------------------------------------------------------------------------
572 -- Functions GET_LATEST_EIUAI_REV_[SS/PDH] --
573 -- Returns the the code of the latest LOGICAL revision row loaded for the item into the --
574 -- user defined attribute interface table --
575 -- Note the lack of attribute-specific parameters - this is to ensure that contexts in --
576 -- which this proc gets called will only attempt to go after a single logical revision --
577 -- row, regardless of the possible absence of the required attributes in that row and --
578 -- their possible presence in other logical rows of the interface table --
579 ------------------------------------------------------------------------------------------------
580 FUNCTION GET_LATEST_EIUAI_REV_SS
581 (
582 p_batch_id IN EGO_ITM_USR_ATTR_INTRFC.DATA_SET_ID%TYPE
583 , p_source_system_id IN EGO_ITM_USR_ATTR_INTRFC.SOURCE_SYSTEM_ID%TYPE
584 , p_source_system_reference IN EGO_ITM_USR_ATTR_INTRFC.SOURCE_SYSTEM_REFERENCE%TYPE
585 , p_organization_id IN EGO_ITM_USR_ATTR_INTRFC.ORGANIZATION_ID%TYPE
586 , p_do_processed_rows_flag IN FLAG DEFAULT FND_API.G_FALSE
587 , p_request_id IN EGO_ITM_USR_ATTR_INTRFC.REQUEST_ID%TYPE DEFAULT NULL
588 )
589 RETURN EGO_ITM_USR_ATTR_INTRFC.REVISION%TYPE;
590
591 FUNCTION GET_LATEST_EIUAI_REV_PDH
592 (
593 p_batch_id IN EGO_ITM_USR_ATTR_INTRFC.DATA_SET_ID%TYPE
594 , p_inventory_item_id IN EGO_ITM_USR_ATTR_INTRFC.INVENTORY_ITEM_ID%TYPE
595 , p_item_number IN EGO_ITM_USR_ATTR_INTRFC.ITEM_NUMBER%TYPE
596 , p_organization_id IN EGO_ITM_USR_ATTR_INTRFC.ORGANIZATION_ID%TYPE
597 , p_do_processed_rows_flag IN FLAG DEFAULT FND_API.G_FALSE
598 , p_request_id IN EGO_ITM_USR_ATTR_INTRFC.REQUEST_ID%TYPE DEFAULT NULL
599 )
600 RETURN EGO_ITM_USR_ATTR_INTRFC.REVISION%TYPE;
601
602 FUNCTION GET_LATEST_MIRI_REV_SS
606 , p_source_system_reference IN MTL_ITEM_REVISIONS_INTERFACE.SOURCE_SYSTEM_REFERENCE%TYPE
603 (
604 p_batch_id IN MTL_ITEM_REVISIONS_INTERFACE.SET_PROCESS_ID%TYPE
605 , p_source_system_id IN MTL_ITEM_REVISIONS_INTERFACE.SOURCE_SYSTEM_ID%TYPE
607 , p_organization_id IN MTL_ITEM_REVISIONS_INTERFACE.ORGANIZATION_ID%TYPE
608 , p_do_processed_rows_flag IN FLAG DEFAULT FND_API.G_FALSE
609 , p_request_id IN MTL_ITEM_REVISIONS_INTERFACE.REQUEST_ID%TYPE DEFAULT NULL
610 )
611 RETURN MTL_ITEM_REVISIONS_INTERFACE.REVISION%TYPE;
612
613 FUNCTION GET_LATEST_MIRI_REV_PDH
614 (
615 p_batch_id IN MTL_ITEM_REVISIONS_INTERFACE.SET_PROCESS_ID%TYPE
616 , p_inventory_item_id IN MTL_ITEM_REVISIONS_INTERFACE.INVENTORY_ITEM_ID%TYPE
617 , p_item_number IN MTL_ITEM_REVISIONS_INTERFACE.ITEM_NUMBER%TYPE
618 , p_organization_id IN MTL_ITEM_REVISIONS_INTERFACE.ORGANIZATION_ID%TYPE
619 , p_do_processed_rows_flag IN FLAG DEFAULT FND_API.G_FALSE
620 , p_request_id IN MTL_ITEM_REVISIONS_INTERFACE.REQUEST_ID%TYPE DEFAULT NULL
621 )
622 RETURN MTL_ITEM_REVISIONS_INTERFACE.REVISION%TYPE;
623
624 -----------------------------------------------------------------------------------------
625 -- Get_Tokens --
626 -- Takes a string and breaks it into tokens, returning them in another space-delimited --
627 -- string; the tokens are determined according to the attributes/preferences of the --
628 -- intermedia text index on items, using its stoplist, lexer, etc. --
629 -----------------------------------------------------------------------------------------
630 PROCEDURE GET_TOKENS
631 (
632 p_string_val IN VARCHAR2
633 ,x_tokens OUT NOCOPY VARCHAR2
634 );
635
636 ------------------------------------------------------------------------------------------
637 -- Convert_Org_And_Cat_Grp --
638 -- This procedure converts a specified interface table row's organization code to an ID --
639 -- and converts the item catalog category name to its corresponding ID; these --
640 -- conversions only occur if the org code/category name exactly match an existing name --
641 -- in PDH. --
642 ------------------------------------------------------------------------------------------
643 PROCEDURE CONVERT_ORG_AND_CAT_GRP
644 (
645 p_batch_id IN NUMBER
646 ,p_src_system_id IN NUMBER
647 ,p_src_system_ref IN VARCHAR2
648 ,p_commit IN BOOLEAN
649 );
650
651 ------------------------------------------------------------------------------------------
652 -- Convert_Org_Cat_Grp_For_Batch --
653 -- This is a wrapper procedure for the previous conversion procedure; this one accepts --
654 -- a batch ID and converts all unprocessed rows belonging to that batch. --
655 ------------------------------------------------------------------------------------------
656 PROCEDURE CONVERT_ORG_CAT_GRP_FOR_BATCH
657 (
658 p_batch_id IN NUMBER
659 ,p_commit IN BOOLEAN
660 );
661
662 ------------------------------------------------------------------------------------------
663 -- Confirm_Matches --
664 -- This procedure takes care of setting the confirm status for a particular match in --
665 -- the item match table, depending on how many matches were found for a row in the --
666 -- interface table. --
667 ------------------------------------------------------------------------------------------
668 PROCEDURE CONFIRM_MATCHES
669 (
670 p_batch_id IN NUMBER
671 ,p_src_system_id IN NUMBER
672 ,p_src_system_ref IN VARCHAR2
673 ,p_match_count IN NUMBER
674 ,p_inventory_item_id IN NUMBER
675 ,p_organization_id IN NUMBER
676 );
677
678 ------------------------------------------------------------------------------------------
679 -- Get_Latest_Revision_Func --
680 -- This function is a wrapper to Get_Latest_Revision, formed as a function instead of a --
684 (
681 -- procedure. --
682 ------------------------------------------------------------------------------------------
683 FUNCTION GET_LATEST_REVISION_FUNC
685 p_batch_id IN EGO_ITM_USR_ATTR_INTRFC.DATA_SET_ID%TYPE
686 , p_source_system_id IN EGO_ITM_USR_ATTR_INTRFC.SOURCE_SYSTEM_ID%TYPE
687 , p_source_system_reference IN EGO_ITM_USR_ATTR_INTRFC.SOURCE_SYSTEM_REFERENCE%TYPE
688 , p_organization_id IN EGO_ITM_USR_ATTR_INTRFC.ORGANIZATION_ID%TYPE
689 )
690 RETURN VARCHAR2;
691
692 PROCEDURE UPDATE_ITEM_SYNC_RECORDS
693 (p_set_id IN NUMBER
694 ,p_org_id IN NUMBER
695 );
696
697
698 PROCEDURE SET_CONFIRM_STATUS
699 (p_batch_id IN MTL_SYSTEM_ITEMS_INTERFACE.SET_PROCESS_ID%TYPE
700 ,p_source_system_id IN MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_ID%TYPE
701 ,p_source_system_reference IN MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_REFERENCE%TYPE
702 ,p_new_status IN MTL_SYSTEM_ITEMS_INTERFACE.CONFIRM_STATUS%TYPE
703 ,p_inventory_item_id IN MTL_SYSTEM_ITEMS_INTERFACE.INVENTORY_ITEM_ID%TYPE DEFAULT NULL
704 ,p_organization_id IN MTL_SYSTEM_ITEMS_INTERFACE.ORGANIZATION_ID%TYPE DEFAULT NULL
708 );
705 ,p_check_matching_table IN FLAG DEFAULT FND_API.G_FALSE
706 ,errmsg OUT NOCOPY VARCHAR2
707 ,retcode OUT NOCOPY VARCHAR2
709
710 ------------------------------------------------------------------
711 -- Function for returning the change order flag for the batch
712 -- Bug#4631349 (RSOUNDAR)
713 ------------------------------------------------------------------
714
715 FUNCTION getAddAllToChangeFlag (p_batch_id IN NUMBER)
716 RETURN VARCHAR2;
717
718 FUNCTION Get_Lookup_Meaning(p_lookup_type IN VARCHAR2, p_lookup_code IN VARCHAR2) RETURN VARCHAR2;
719
720 ------------------------------------------------------------------
721 --Function for returning batch details before import
722 --Bug.:4933193
723 ------------------------------------------------------------------
724 FUNCTION GET_IMPORT_DETAILS_DATA
725 ( p_set_process_id NUMBER,
726 p_organization_id NUMBER ,
727 p_organization_code VARCHAR2
728 )
729 RETURN SYSTEM.EGO_IMPORT_CNT_TABLE;
730
731 /*
732 * This method updates the request_ids to ego_import_batches_b table.
733 */
734 PROCEDURE Update_Request_Id_To_Batch (
735 p_import_request_id IN NUMBER,
736 p_match_request_id IN NUMBER,
737 p_batch_id IN NUMBER);
738
739 /*
740 * This method is called after all the import is completed
741 * this will update the process flag of rows with process flag = 111
742 */
743 PROCEDURE Demerge_Batch_After_Import(
744 ERRBUF OUT NOCOPY VARCHAR2
745 , RETCODE OUT NOCOPY VARCHAR2
746 , p_batch_id IN NUMBER
747 );
748
749 /*
750 * This mehtod is for merging the associations.
751 * This method also defaults the values for primary_flag and status_code in associations table.
752 * primary_flag null is defaulted with 'N'
753 * status_code null is defaulted with 1
754 */
755 PROCEDURE merge_associations ( p_batch_id IN NUMBER
756 , p_is_pdh_batch IN FLAG DEFAULT NULL
757 , p_commit IN FLAG DEFAULT FND_API.G_FALSE );
758
759
760 /*
761 * This method cleans up UDA row identifiers, ensuring that all single attr groups
762 * are represented by only one row identifier in EGO_ITM_USR_ATTR_INTRFC
763 */
764 PROCEDURE CLEAN_UP_UDA_ROW_IDENTS( p_batch_id IN NUMBER,
765 p_process_status IN NUMBER,
766 p_ignore_item_num_upd IN VARCHAR2, --FND_API.G_TRUE
767 p_commit IN VARCHAR2 DEFAULT FND_API.G_TRUE
768 );
769
770 END EGO_IMPORT_PVT;
[Home] [Help]