eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.POS_BULKLOAD_ENTITIES

Source


1 PACKAGE BODY POS_BULKLOAD_ENTITIES
2 /* $Header: POSSBLKB.pls 120.6.12020000.2 2013/04/11 02:49:23 liawei ship $ */
3 AS
4   ----------------------------------------------------------------------------
5   -- Global constants
6   ----------------------------------------------------------------------------
7   G_PKG_NAME                          CONSTANT VARCHAR2(30) := 'EGO_USER_ATTRS_BULK_PVT';
8   G_REQUEST_ID                        NUMBER := FND_GLOBAL.CONC_REQUEST_ID;
9   G_PROGAM_APPLICATION_ID             NUMBER := FND_GLOBAL.PROG_APPL_ID;
10   G_PROGAM_ID                         NUMBER := FND_GLOBAL.CONC_PROGRAM_ID;
11   G_USER_NAME                         FND_USER.USER_NAME%TYPE := FND_GLOBAL.USER_NAME;
12   G_USER_ID                           NUMBER := FND_GLOBAL.USER_ID;
13   G_LOGIN_ID                          NUMBER := FND_GLOBAL.LOGIN_ID;
14   G_CURRENT_USER_ID                   NUMBER;
15   G_CURRENT_LOGIN_ID                  NUMBER;
16   G_API_VERSION                       NUMBER := 1.0;
17   G_HZ_PARTY_ID                       VARCHAR2(30);
18   G_NO_USER_NAME_TO_VALIDATE          EXCEPTION;
19     -- used for error handling.
20   G_ADD_ERRORS_TO_FND_STACK           VARCHAR2(1);
21   G_APPLICATION_CONTEXT               VARCHAR2(30);
22   G_ENTITY_ID                         NUMBER ;
23   G_ENTITY_CODE                       VARCHAR2(30) := 'HZ_PARTIES';
24 --    G_PK_COLS_TABLE                     PK_COL_TABLE;
25 --  G_SITE_NUMBER_EBI_COL               VARCHAR2(50) := 'C_INTF_ATTR240';
26   G_DATE_FORMAT                       CONSTANT VARCHAR2(30) := 'SYYYY-MM-DD HH24:MI:SS';
27 
28   G_APPLICATION_ID                    NUMBER(3) := 177;
29   G_DATA_ROWS_UPLOADED_NEW            CONSTANT NUMBER := 0;
30   G_PS_TO_BE_PROCESSED                CONSTANT NUMBER := 1;
31   G_PS_IN_PROCESS                     CONSTANT NUMBER := 2;
32   G_PS_GENERIC_ERROR                  CONSTANT NUMBER := 3;
33   G_PS_SUCCESS                        CONSTANT NUMBER := 4;
34   G_RETCODE_SUCCESS_WITH_WARNING      CONSTANT VARCHAR(1) := 'W';
35 
36    G_ERROR_TABLE_NAME      VARCHAR2(99) := 'EGO_BULKLOAD_INTF';
37    G_ERROR_ENTITY_CODE     VARCHAR2(99) := 'HZ_PARTIES';
38    G_ERROR_FILE_NAME       VARCHAR2(99);
39    G_BO_IDENTIFIER         VARCHAR2(99) := 'HZ_PARTIES';
40    G_DEBUG CONSTANT VARCHAR2(10) := NVL(FND_PROFILE.VALUE('POS_DEBUG_TRACE'),0);
41 
42    ---------------------------------------------------------------
43    -- Java Conc Program can continue writing to the same Error Log File.
44    -- using the below variable
45    ---------------------------------------------------------------
46    G_ERRFILE_PATH_AND_NAME VARCHAR2(10000);
47 
48    ---------------------------------------------------------------
49    -- API Return statuses.                                      --
50    ---------------------------------------------------------------
51    G_STATUS_SUCCESS    CONSTANT VARCHAR2(1)    := 'S';
52    G_STATUS_ERROR      CONSTANT VARCHAR2(1)    := 'E';
53 
54 
55    ---------------------------------------------------------------
56    -- UDA EGO WEBADI uptake
57    -- The following data level constants to be inserted into INTF tables
58    ---------------------------------------------------------------
59    G_POS_SUPP_LEVEL_ID NUMBER(5) := 17701;
60    G_POS_SUPP_ADDR_LEVEL_ID NUMBER(5) := 17702;
61    G_POS_SUPP_ADDR_SITE_LEVEL_ID NUMBER(5) := 17703;
62 
63 
64    G_POS_SUPP_LEVEL VARCHAR2(100) := 'SUPP_LEVEL';
65    G_POS_SUPP_ADDR_LEVEL VARCHAR2(100) := 'SUPP_ADDR_LEVEL';
66    G_POS_SUPP_ADDR_SITE_LEVEL VARCHAR2(100) := 'SUPP_ADDR_SITE_LEVEL';
67 
68 
69 
70 
71 PROCEDURE SETUP_BULKLOAD_INTF(ERRBUF  OUT NOCOPY VARCHAR2,
72             		      RETCODE OUT NOCOPY VARCHAR2,
73 			      ERROR_FILE OUT NOCOPY VARCHAR2,
74 			      p_result_format_usage_id IN Number) Is
75 l_process_status char(100);
76 BEGIN
77    DELETE FROM EGO_BULKLOAD_INTF
78    WHERE RESULTFMT_USAGE_ID = p_result_format_usage_id
79    AND PROCESS_STATUS <> G_DATA_ROWS_UPLOADED_NEW;
80    Open_Debug_Session;
81    ERROR_FILE := G_ERRFILE_PATH_AND_NAME;
82    Write_Conclog('Cleanup and Debug Session Setup Activities Completed');
83    RETCODE := 'S';
84 EXCEPTION
85     WHEN OTHERS THEN
86        ERRBUF  := SUBSTRB(SQLERRM, 1,240);
87        RETCODE := 'E';
88        Write_Conclog(' Error in SETUP_BULKLOAD_INTF Error API'||ERRBUF);
89 End SETUP_BULKLOAD_INTF;
90 
91 
92 PROCEDURE Open_Debug_Session IS
93 BEGIN
94   ----------------------------------------------------------------
95   -- Open the Debug Log Session, only if Profile is set to TRUE --
96   ----------------------------------------------------------------
97   IF (G_DEBUG = 1) THEN
98 
99    ----------------------------------------------------------------------------------
100    -- Opens Error_Handler debug session, only if Debug session is not already open.
101    ----------------------------------------------------------------------------------
102    IF (Error_Handler.Get_Debug <> 'Y') THEN
103      Open_Debug_Session_Internal;
104    END IF;
105  END IF;
106 END Open_Debug_Session;
107 
108  ----------------------------------------------------------
109  -- Internal procedure to open Debug Session.            --
110  ----------------------------------------------------------
111 PROCEDURE open_debug_session_internal IS
112   CURSOR c_get_utl_file_dir IS
113      SELECT VALUE
114       FROM V$PARAMETER
115       WHERE NAME = 'utl_file_dir';
116 
117   l_log_output_dir       VARCHAR2(512);
118   l_log_return_status    VARCHAR2(99);
119   l_errbuff              VARCHAR2(999);
120 BEGIN
121   Error_Handler.initialize();
122   Error_Handler.set_bo_identifier(G_BO_IDENTIFIER);
123 
124   OPEN c_get_utl_file_dir;
125   FETCH c_get_utl_file_dir INTO l_log_output_dir;
126   IF c_get_utl_file_dir%FOUND THEN
127     ------------------------------------------------------
128     -- Trim to get only the first directory in the list --
129     ------------------------------------------------------
130     IF INSTR(l_log_output_dir,',') <> 0 THEN
131       l_log_output_dir := SUBSTR(l_log_output_dir, 1, INSTR(l_log_output_dir, ',') - 1);
132     END IF;
133 
134     G_ERROR_FILE_NAME := G_ERROR_TABLE_NAME||'_'||to_char(sysdate, 'DDMONYYYY_HH24MISS')||'.err';
135 
136     -----------------------------------------------------------------------
137     -- To open the Debug Session to write the Debug Log.                 --
138     -- This sets Debug value so that Error_Handler.Get_Debug returns 'Y' --
139     -----------------------------------------------------------------------
140     Error_Handler.Open_Debug_Session(
141       p_debug_filename   => G_ERROR_FILE_NAME
142      ,p_output_dir       => l_log_output_dir
143      ,x_return_status    => l_log_return_status
144      ,x_error_mesg       => l_errbuff
145      );
146 
147     ---------------------------------------------------------------
148     -- The Java Conc Program Should be writing to the same Error Log File.
149     ---------------------------------------------------------------
150     G_ERRFILE_PATH_AND_NAME := l_log_output_dir||'/'||G_ERROR_FILE_NAME;
151 
152      Write_Conclog('Debug File name is => ' ||	G_ERRFILE_PATH_AND_NAME);
153 
154     IF (l_log_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
155        Write_Conclog('Unable to open error log file. Error => '||l_errbuff);
156     END IF;
157 
158   END IF; --IF c_get_utl_file_dir%FOUND THEN
159   CLOSE c_get_utl_file_dir;
160 END open_debug_session_internal;
161 
162 PROCEDURE Developer_Debug(p_msg  IN  VARCHAR2) IS
163  l_err_msg VARCHAR2(240);
164 BEGIN
165 	Error_Handler.Write_debug(p_msg);
166   EXCEPTION
167    WHEN OTHERS THEN
168     l_err_msg := SUBSTRB(SQLERRM, 1,240);
169     FND_FILE.put_line(FND_FILE.LOG, 'LOGGING SQL ERROR => '||l_err_msg);
170 END;
171 
172 -----------------------------------------------------------------
173  -- Close the Debug Session, only if Debug is already Turned ON --
174  -----------------------------------------------------------------
175 PROCEDURE Close_Debug_Session IS
176 
177 BEGIN
178    -----------------------------------------------------------------------------
179    -- Close Error_Handler debug session, only if Debug session is already open.
180    -----------------------------------------------------------------------------
181    IF (Error_Handler.Get_Debug = 'Y') THEN
182      Error_Handler.Close_Debug_Session;
183    END IF;
184 
185 END Close_Debug_Session;
186  -----------------------------------------------
187  -- Write Debug statements to Concurrent Log  --
188  -----------------------------------------------
189 PROCEDURE Write_Conclog (p_msg  IN  VARCHAR2) IS
190  l_err_msg VARCHAR2(240);
191 BEGIN
192     FND_FILE.put_line(FND_FILE.LOG, p_msg);
193 END Write_Conclog;
194 
195 -- Bug 16384042 Helper Function to resolve PK values for data level supplier site and party site
196 -- since ego_bulkload_intf already got AddressName and VendorSiteCode populated use them to find Ids
197 -- PKs in EBI table,  PK1 -- PartyID   PK2 -- IS_PROSPECT  PK3 -- PARTY_SITE_ID  PK4 -- VENDOR_SITE_ID
198 
199 PROCEDURE RESOLVE_PK_VALUES(
200           RETCODE                 OUT  NOCOPY   VARCHAR2,
201           p_result_format_usage_id  IN      NUMBER
202       ) Is
203 CURSOR c_ebi_col_mapping_addr(c_fmt_usage_id in number) is
204 SELECT INTF_COLUMN_NAME
205 FROM   ego_results_fmt_usages
206 WHERE  attribute_code = 'PS_PARTY_SITE_NAME'
207 AND    resultfmt_usage_id = c_fmt_usage_id
208 AND    CUSTOMIZATION_APPLICATION_ID = G_APPLICATION_ID
209 AND    rownum < 2;
210 
211 CURSOR c_ebi_col_mapping_site(c_fmt_usage_id in number) is
212 SELECT INTF_COLUMN_NAME
213 FROM   ego_results_fmt_usages
214 WHERE  attribute_code = 'SS_VENDOR_SITE_CODE'
215 AND    resultfmt_usage_id = c_fmt_usage_id
216 AND    CUSTOMIZATION_APPLICATION_ID = G_APPLICATION_ID
217 AND    rownum < 2;
218 
219 CURSOR c_resolve_PKs(c_fmt_usage_id in number) is
220 SELECT *
221 FROM   ego_bulkload_intf
222 WHERE  resultfmt_usage_id = c_fmt_usage_id;
223 --AND    process_status = G_PS_TO_BE_PROCESSED;
224 
225 CURSOR c_party_site (c_party_id IN NUMBER, c_address_name iN VARCHAR) IS
226 SELECT PS.Party_Site_ID
227 FROM   AP_Suppliers AP,
228        HZ_PARTY_SITES PS
229 WHERE  AP.Party_ID = PS.Party_ID
230 AND    AP.Party_ID = c_Party_ID
231 AND    PS.STATUS = 'A'
232 AND    PS.PARTY_SITE_NAME = c_address_name
233 AND    rownum < 2;
234 
235 CURSOR c_supplier_site (c_party_id IN NUMBER, c_party_site_id IN NUMBER, c_vendor_site_code IN VARCHAR) IS
236 SELECT  SS.Vendor_Site_ID
237 FROM    AP_Suppliers AP,
238         AP_SUPPLIER_SITES_ALL SS,
239         HZ_PARTY_SITES PS
240 WHERE   AP.Party_ID = PS.Party_ID
241 AND     AP.Party_ID = c_Party_ID
242 AND     PS.STATUS = 'A'
243 AND     PS.Party_Site_ID = c_party_site_id
244 AND     PS.Party_Site_ID = SS.Party_Site_ID
245 AND     SS.VENDOR_SITE_CODE = c_vendor_site_code
246 AND     SS.INACTIVE_DATE IS NULL
247 AND     rownum < 2;
248 
249 l_addr_col_name  VARCHAR2(20);
250 l_site_col_name  VARCHAR2(20);
251 --l_has_site_col   BOOLEAN;
252 l_address_name   VARCHAR2(150);
253 l_vendor_site_code VARCHAR2(150);
254 l_pk3_value      NUMBER;
255 l_pk4_value      NUMBER;
256 l_dyn_sql        VARCHAR2(100);
257 
258 Current_Error_Code      VARCHAR2(20) := NULL;
259 conc_status             BOOLEAN      ;
260 BEGIN
261    Write_Conclog('Executing RESOLVE_PK_VALUES API ');
262    --first get mapped column names from ego_result_fmt_usages
263    OPEN c_ebi_col_mapping_addr(p_result_format_usage_id);
264    FETCH c_ebi_col_mapping_addr INTO l_addr_col_name;
265     IF c_ebi_col_mapping_addr%NOTFOUND OR l_addr_col_name = NULL THEN -- no need to do anything in this case
266        CLOSE c_ebi_col_mapping_addr;
267        RETURN;
268     END IF;
269    CLOSE c_ebi_col_mapping_addr;
270    Write_Conclog('Executing RESOLVE_PK_VALUES -- get Address Column mapping colname =  '||l_addr_col_name );
271 
272    OPEN  c_ebi_col_mapping_site(p_result_format_usage_id);
273    FETCH c_ebi_col_mapping_site INTO l_site_col_name;
274      IF c_ebi_col_mapping_site %NOTFOUND THEN
275        l_site_col_name := NULL;
276      END IF;
277    CLOSE c_ebi_col_mapping_site;
278   Write_Conclog('Executing RESOLVE_PK_VALUES -- get Site Column mapping colname =  '||l_site_col_name );
279 
280    Write_Conclog('Executing RESOLVE_PK_VALUES -- before cursor c_ebi_rec');
281    FOR c_ebi_rec IN c_resolve_PKs(p_result_format_usage_id)
282    LOOP
283       l_dyn_sql := 'SELECT '||l_addr_col_name
284                   ||' FROM ego_bulkload_intf '
285                   ||' WHERE resultfmt_usage_id = :1 '
286                   ||' AND transaction_id = :2 ';
287       execute immediate l_dyn_sql into l_address_name using c_ebi_rec.resultfmt_usage_id, c_ebi_rec.transaction_id;
288          Write_Conclog('Executing RESOLVE_PK_VALUES --after 1st dyn sql l_address_name = '|| l_address_name);
289 
290       IF (l_site_col_name IS NOT NULL) THEN
291             l_dyn_sql := 'SELECT '||l_site_col_name
292                   ||' FROM ego_bulkload_intf '
293                   ||' WHERE resultfmt_usage_id = :1 '
294                   ||' AND transaction_id = :2 ';
295             execute immediate l_dyn_sql into l_vendor_site_code using c_ebi_rec.resultfmt_usage_id, c_ebi_rec.transaction_id;
296          Write_Conclog('Executing RESOLVE_PK_VALUES --after 2nd dyn sql l_vendor_site_code = '|| l_vendor_site_code);
297       END IF;
298    --setting PK3
299    IF l_address_name IS NOT NULL THEN
300       --getting PK3 value
301       OPEN c_party_site( c_ebi_rec.INSTANCE_PK1_VALUE, l_address_name);
302       FETCH c_party_site INTO l_pk3_value;
303       CLOSE c_party_site;
304       Write_Conclog('Executing RESOLVE_PK_VALUES 1st -- l_pk3_value = '||l_pk3_value);
305    END IF;
306    --setting PK4
307    IF  l_vendor_site_code IS NOT NULL AND NVL(l_pk3_value, 0) <> 0 THEN
308       OPEN c_supplier_site( c_ebi_rec.INSTANCE_PK1_VALUE, l_pk3_value, l_vendor_site_code);
309       FETCH c_supplier_site INTO l_pk4_value;
310       CLOSE c_supplier_site;
311       Write_Conclog('Executing RESOLVE_PK_VALUES 2nd -- l_pk4_value = '||l_pk4_value);
312    END IF;
313      Write_Conclog('Executing RESOLVE_PK_VALUES --Before final update');
314    --finally set PKs to EBI
315      UPDATE EGO_BULKLOAD_INTF
316      SET    INSTANCE_PK3_VALUE = l_pk3_value,
317             INSTANCE_PK4_VALUE = l_pk4_value
318      WHERE  resultfmt_usage_id = c_ebi_rec.resultfmt_usage_id
319      AND    transaction_id     =  c_ebi_rec.transaction_id;
320 
321    END LOOP; --  FOR c_ebi_rec IN c_resolve_PKs(c_fmt_usage_id)
322    Write_Conclog('Executing RESOLVE_PK_VALUES -- All done successfully');
323  EXCEPTION
324     WHEN OTHERS THEN
325        Write_Conclog('Error while calling Resolve_PK_values API  '||SQLCODE || ':'||SQLERRM);
326        RETCODE := 'E';
327        Current_Error_Code := To_Char(SQLCODE);
328        conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', Current_Error_Code);
329 END RESOLVE_PK_VALUES;
330 
331 /*
332 PROCEDURE RESOLVE_PK_VALUES(
333           p_Party_ID         IN          NUMBER,
334           p_Data_Level_ID    IN          NUMBER,
335           p_Attr_Group_ID    IN          NUMBER,
336           x_PK1_Value        OUT NOCOPY  NUMBER,
337           x_PK2_Value        OUT NOCOPY  NUMBER
338       ) Is
339 --Only existing UDAs have rows in extb
340 CURSOR c_existing_rows_in_extb(c_party_id IN NUMBER, c_data_level_id IN NUMBER, c_attr_group_id IN NUMBER)  IS
341 SELECT PK1_Value, PK2_Value
342 FROM   pos_supp_prof_ext_b
343 WHERE  party_id = c_party_id
344 AND    data_level_id = c_data_level_id
345 AND    attr_group_id = c_attr_group_id
346 AND    rownum < 2;
347 
348 CURSOR c_party_site (c_party_id IN NUMBER) IS
349 SELECT PS.Party_Site_ID
350 FROM   AP_Suppliers AP,
351        HZ_PARTY_SITES PS
352 WHERE  AP.Party_ID = PS.Party_ID
353 AND    AP.Party_ID = p_Party_ID
354 AND    PS.STATUS = 'A'
355 AND    rownum < 2;
356 
357 CURSOR c_supplier_site (c_party_id IN NUMBER) IS
358 SELECT  SS.Party_Site_ID,
359         SS.Vendor_Site_ID
360 FROM    AP_Suppliers AP,
361         AP_SUPPLIER_SITES_ALL SS,
362         HZ_PARTY_SITES PS
363 WHERE   AP.Party_ID = PS.Party_ID
364 AND     AP.Party_ID = p_Party_ID
365 AND     PS.STATUS = 'A'
366 AND     PS.Party_Site_ID = SS.Party_Site_ID
367 AND     SS.INACTIVE_DATE IS NULL
368 AND     rownum < 2;
369 
370 BEGIN
371   Write_Conclog('Entering RESOLVE_PK_VALUES  p_Party_ID = '||p_Party_ID);
372   Write_Conclog('                            p_Data_Level_ID = '||p_Data_Level_ID);
373   Write_Conclog('                            p_Attr_Group_ID = '||p_Attr_Group_ID);
374 
375   --shouldn't call this API when data level is Supplier Party still check in case
376   IF p_Data_Level_ID = G_POS_SUPP_LEVEL_ID THEN
377      Return;
378 
379   ELSIF p_Data_Level_ID = G_POS_SUPP_ADDR_LEVEL_ID THEN
380      OPEN c_existing_rows_in_extb(p_Party_ID, p_Data_Level_ID, p_Attr_Group_ID);
381      FETCH  c_existing_rows_in_extb INTO x_PK1_Value, x_PK2_Value;
382 
383      IF c_existing_rows_in_extb%NOTFOUND THEN
384      Write_Conclog('Cannot find rows in extb table use HZ_Party to resolve');
385      OPEN c_party_site(p_Party_ID);
386      FETCH c_party_site INTO  x_PK1_Value;
387      CLOSE c_party_site;
388      END IF;
389      ClOSE c_existing_rows_in_extb;
390 
391   ELSIF p_Data_Level_ID = G_POS_SUPP_ADDR_SITE_LEVEL_ID THEN
392      OPEN c_existing_rows_in_extb(p_Party_ID, p_Data_Level_ID, p_Attr_Group_ID);
393      FETCH  c_existing_rows_in_extb INTO x_PK1_Value, x_PK2_Value;
394 
395      IF c_existing_rows_in_extb%NOTFOUND THEN
396      Write_Conclog('Cannot find rows in extb table use HZ_Party to resolve');
397      OPEN c_supplier_site(p_Party_ID);
398      FETCH c_supplier_site INTO  x_PK1_Value, x_PK2_Value;
399      CLOSE c_supplier_site;
400      END IF;
401      ClOSE c_existing_rows_in_extb;
402 
403   END IF;
404 END RESOLVE_PK_VALUES;
405 */
406 ---------------------------------------------------------------------------------
407 --LOAD SUPPLIER USER DEFINED ATTRIBUTES
408 --------------------------------------------------------------------------------
409 PROCEDURE LOAD_USERATTR_INTF(
410 	   	  		 p_resultfmt_usage_id    IN         NUMBER,
411                  p_data_set_id           IN         NUMBER,
412                  x_errbuff               OUT NOCOPY VARCHAR2,
413                  x_retcode               OUT NOCOPY VARCHAR2,
414                  p_entity_name           IN       VARCHAR2,
415                  p_batch_id              IN       NUMBER
416                 ) Is
417     --Bug 16384042 modified cursor to include data_level_id column
418     CURSOR c_user_attr_group_codes (c_resultfmt_usage_id  IN  NUMBER,c_attr_group_type IN VARCHAR2) IS
419     SELECT DISTINCT To_Number(SUBSTR(attribute_code, 1, INSTR(attribute_code, '$$') - 1)) attr_group_id,classification_code, data_level_id
420     FROM   ego_results_fmt_usages
421     WHERE  resultfmt_usage_id = c_resultfmt_usage_id
422      AND   attribute_code LIKE '%$$%'
423      AND   To_Number(SUBSTR(attribute_code, 1, INSTR(attribute_code, '$$') - 1)) IN
424       (
425         SELECT attr_group_id
426         FROM   ego_attr_groups_v
427         WHERE  attr_group_type = c_attr_group_type
428         AND    application_id = G_APPLICATION_ID
429       );
430 
431      CURSOR c_attr_grp_n_attr_int_names(p_attr_id  IN NUMBER,c_attr_group_type IN VARCHAR2) IS
432      SELECT  attr_group_name, attr_name
433      FROM    ego_attrs_v
434      WHERE   attr_id = p_attr_id
435       AND    attr_group_type = c_attr_group_type
436       AND    application_id = G_APPLICATION_ID;
437 	    TYPE L_USER_ATTR_REC_TYPE IS RECORD
438 		(
439       DATA_SET_ID                          NUMBER(15),
440       TRANSACTION_ID                       NUMBER(15),
441       PK1_ID                    		        VARCHAR2(256),
442       PK2_ID                    		       VARCHAR2(256),
443       PK3_ID                    		       VARCHAR2(256),
444       PK4_ID                    		       VARCHAR2(256),
445       PK5_ID                    		       VARCHAR2(256),
446       CLASSIFICATION_CODE                  VARCHAR2(30),
447       ROW_IDENTIFIER                       NUMBER(15),
448       ATTR_GROUP_NAME                      VARCHAR2(30),
449       ATTR_NAME                            VARCHAR2(30),
450       ATTR_DATATYPE_CODE                   VARCHAR2(1), --Valid Vals: C / N / D
451       ATTR_VALUE_STR                       VARCHAR2(10000),
452       ATTR_VALUE_NUM                       NUMBER,
453       ATTR_VALUE_DATE                      DATE,
454       INTF_COLUMN_NAME                     VARCHAR2(30),
455 	  ATTR_GROUP_ID                        NUMBER(15),
456     BATCH_ID                             NUMBER(15)
457 	  );
458   TYPE L_USER_ATTR_TBL_TYPE IS TABLE OF L_USER_ATTR_REC_TYPE
459   INDEX BY BINARY_INTEGER;
460   TYPE VARCHAR_TBL_TYPE IS TABLE OF VARCHAR2(256)
461   INDEX BY BINARY_INTEGER;
462   l_prod_col_name_tbl               VARCHAR_TBL_TYPE;
463   l_intf_col_name_tbl               VARCHAR_TBL_TYPE;
464   l_attr_id_table                   DBMS_SQL.NUMBER_TABLE;
465   l_intf_col_name_table             DBMS_SQL.VARCHAR2_TABLE;
466   l_usr_attr_data_tbl               L_USER_ATTR_TBL_TYPE;
467   l_pk_id_char                    VARCHAR(15);
468   l_pk1_id_char                    VARCHAR(15);
469   l_pk2_id_char                    VARCHAR(15);
470   l_pk3_id_char                    VARCHAR(15);
471   l_pk4_id_char                    VARCHAR(15);
472   l_pk5_id_char                    VARCHAR(15);
473 
474   l_site_num_char                   VARCHAR(1000);
475   l_trade_area_num_char             VARCHAR(10);
476   l_count                           NUMBER(5);
477   l_data_type_code                  VARCHAR2(2);
478   l_transaction_id                  NUMBER(15);
479   l_attr_group_int_name    EGO_ATTRS_V.ATTR_GROUP_NAME%TYPE;
480   l_attr_int_name          EGO_ATTRS_V.ATTR_NAME%TYPE;
481 
482   l_attr_group_id NUMBER(15);
483   l_party_id       NUMBER(15);
484   l_attr_group_id_table DBMS_SQL.NUMBER_TABLE;
485   ---------------------------------------------------------
486   -- Example Data Types to be used in Bind Variable.
487   ---------------------------------------------------------
488   l_varchar_example        VARCHAR2(10000);
489   l_number_example         NUMBER;
490   l_date_example           DATE;
491   --------------------------------------------------------------------
492   -- Actual Data to store corresponding data type value.
493   --------------------------------------------------------------------
494   l_varchar_data           VARCHAR2(10000);
495   l_number_data            NUMBER;
496   l_date_data              DATE;
497   ---------------------------------------------------------
498   -- DBMS_SQL Open Cursor integers.
499   ---------------------------------------------------------
500   l_cursor_select          INTEGER;
501   l_cursor_execute         INTEGER;
502   l_cursor_attr_id_val     INTEGER;
503   ---------------------------------------------------------
504   -- Used for indexes.
505   ---------------------------------------------------------
506   l_temp                   NUMBER(10) := 1;
507   l_actual_userattr_indx   NUMBER(15);
508   l_indx                   NUMBER(15);
509   l_rows_per_attr_grp_indx NUMBER(15);
510   l_save_indx              NUMBER(15);
511   l_attr_grp_has_data      BOOLEAN;
512   l_attr_group_data_level  VARCHAR2(30);
513   ---------------------------------------------------------
514   -- Long Dynamic SQL Strings
515   ---------------------------------------------------------
516   l_dyn_sql                VARCHAR2(10000);
517   l_dyn_attr_id_val_sql    VARCHAR2(10000);
518   ---------------------------------------------------------
519   -- To Number the Attribute Group Data Rows Uniquely.
520   ---------------------------------------------------------
521   L_ATTR_GRP_ROW_IDENT     NUMBER(5) ;
522   ---------------------------------------------------------
523   -- Token tables to log errors, through Error_Handler
524   ---------------------------------------------------------
525   l_token_tbl_two         Error_Handler.Token_Tbl_Type;
526   l_token_tbl_one         Error_Handler.Token_Tbl_Type;
527   l_attr_group_type        VARCHAR2(30);
528 
529   l_userattr_indx_adj      NUMBER(15);  -- To adjust the index according to Data_levels
530 BEGIN
531     Write_Conclog('Loading the User Defined Attributes for Entity '||p_entity_name);
532 
533 	 IF (G_HZ_PARTY_ID IS NULL) THEN
534       IF (G_USER_NAME IS NOT NULL) THEN
535       SELECT 'HZ_PARTY:'||TO_CHAR(PERSON_ID)
536         INTO G_HZ_PARTY_ID
537         FROM EGO_PEOPLE_V
538        WHERE USER_NAME = G_USER_NAME;
539       ELSE
540         RAISE G_NO_USER_NAME_TO_VALIDATE;
541       END IF;
542    END IF;
543 
544 
545         l_attr_group_type := 'POS_SUPP_PROFMGMT_GROUP';
546         --Bug 16384042 added data_level_id for outerloop cursor
547 	 FOR c_attr_grp_rec IN c_user_attr_group_codes
548      (
549         p_resultfmt_usage_id,l_attr_group_type
550       )
551    LOOP
552 	    l_dyn_sql := '';
553 	    l_dyn_sql := ' SELECT To_Number(SUBSTR(attribute_code, INSTR(attribute_code, ''$$'')+2)) attr_id, intf_column_name, To_Number(SUBSTR(attribute_code, 1, INSTR(attribute_code, ''$$'') - 1)) attr_group_id ';
554 	    l_dyn_sql := l_dyn_sql || ' FROM   ego_results_fmt_usages ';
555 	    l_dyn_sql := l_dyn_sql || ' WHERE  resultfmt_usage_id = :RESULTFMT_USAGE_ID';
556 	    l_dyn_sql := l_dyn_sql || '  AND attribute_code LIKE :ATTRIBUTE_CODE ';
557 	    l_cursor_select := DBMS_SQL.OPEN_CURSOR;
558 	    DBMS_SQL.PARSE(l_cursor_select, l_dyn_sql, DBMS_SQL.NATIVE);
559 	    DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 1,l_attr_id_table,2500, l_temp);
560 	    DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 2,l_intf_col_name_table,2500, l_temp);
561             DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 3,l_attr_group_id_table,2500, l_temp);
562 
563 
564 	    DBMS_SQL.BIND_VARIABLE(l_cursor_select,':RESULTFMT_USAGE_ID', p_resultfmt_usage_id);
565 	    DBMS_SQL.BIND_VARIABLE(l_cursor_select,':ATTRIBUTE_CODE', c_attr_grp_rec.attr_group_id||'$$%');
566             l_cursor_execute := DBMS_SQL.EXECUTE(l_cursor_select);
567 
568             l_count := DBMS_SQL.FETCH_ROWS(l_cursor_select);
569 	    DBMS_SQL.COLUMN_VALUE(l_cursor_select, 1, l_attr_id_table);
570             DBMS_SQL.COLUMN_VALUE(l_cursor_select, 2, l_intf_col_name_table);
571             DBMS_SQL.COLUMN_VALUE(l_cursor_select, 3, l_attr_group_id_table);
572 
573 
574 
575 		DBMS_SQL.CLOSE_CURSOR(l_cursor_select);
576 		--------------------------------------------------------------------
577     -- New DBMS_SQL Cursor for Select Attr Values.
578     --------------------------------------------------------------------
579     l_cursor_attr_id_val := DBMS_SQL.OPEN_CURSOR;
580     l_dyn_attr_id_val_sql := '';
581     l_dyn_attr_id_val_sql := ' SELECT ';
582     l_dyn_attr_id_val_sql := l_dyn_attr_id_val_sql || ' TRANSACTION_ID , ';
583      --Bug 16384042 since always pass SUPP_ADDR_SITE as p_entity_name parameter, always get the most number of PKs whether used or not determined by datalevel
584       IF p_entity_name = 'SUPP' THEN
585           l_dyn_attr_id_val_sql := l_dyn_attr_id_val_sql || ' INSTANCE_PK1_VALUE, INSTANCE_PK2_VALUE,  ';
586       ELSIF p_entity_name = 'SUPP_ADDR' THEN
587           l_dyn_attr_id_val_sql := l_dyn_attr_id_val_sql || ' INSTANCE_PK1_VALUE, INSTANCE_PK2_VALUE , INSTANCE_PK3_VALUE ,  ';
588       ELSIF p_entity_name = 'SUPP_ADDR_SITE' THEN
589           l_dyn_attr_id_val_sql := l_dyn_attr_id_val_sql || ' INSTANCE_PK1_VALUE, INSTANCE_PK2_VALUE, INSTANCE_PK3_VALUE, INSTANCE_PK4_VALUE , ';
590       END IF;
591     --------------------------------------------------------------------
592     -- To fetch these cols also, as in case of New Item
593     -- Instance PK1 Value might not have been retrieved.
594     --------------------------------------------------------------------
595 
596      Write_Conclog('*l_attr_id_table.COUNT*'||l_attr_id_table.COUNT);
597     FOR i IN 1..l_attr_id_table.COUNT LOOP
598       IF (i <> l_attr_id_table.COUNT) THEN
599         l_dyn_attr_id_val_sql := l_dyn_attr_id_val_sql || l_intf_col_name_table(i) || ', ';
600       ELSE
601         l_dyn_attr_id_val_sql := l_dyn_attr_id_val_sql || l_intf_col_name_table(i) ;
602       END IF;
603     END LOOP; --end: FOR i IN 1..l_attr_id_table.COUNT LOOP
604     l_dyn_attr_id_val_sql := l_dyn_attr_id_val_sql || ' FROM EGO_BULKLOAD_INTF ' ;
605     l_dyn_attr_id_val_sql := l_dyn_attr_id_val_sql || ' WHERE RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID';
606     l_dyn_attr_id_val_sql := l_dyn_attr_id_val_sql || ' AND PROCESS_STATUS = :PROCESS_STATUS ';
607     DBMS_SQL.PARSE(l_cursor_attr_id_val, l_dyn_attr_id_val_sql, DBMS_SQL.NATIVE);
608     --------------------------------------------------------------------
609     --Setting Data Type for Trasaction ID
610     --------------------------------------------------------------------
611     DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, 1, l_number_example);
612     --------------------------------------------------------------------
613       --Setting Data Type for INSTANCE_PKx_VALUE
614       -- Also since TRANSACTION_ID, INSTANCE_PK1_VALUE, INSTANCE_PK2_VALUE,
615       -- INSTANCE_PK3_VALUE,INSTANCE_PK4_VALUE are added to the SELECT before the User-Defined
616       -- Attrs, we need to adjust the index as follows.
617       ------------------------------------------------------------------------
618 
619      IF p_entity_name = 'SUPP' THEN
620           DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, 2, l_varchar_example, 1000);
621           DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, 3, l_varchar_example, 1000);
622           l_userattr_indx_adj := 3;
623       ELSIF p_entity_name = 'SUPP_ADDR' THEN
624           DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, 2, l_varchar_example, 1000);
625           DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, 3, l_varchar_example, 1000);
626           DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, 4, l_varchar_example, 1000);
627            l_userattr_indx_adj := 4;
628       ELSIF p_entity_name = 'SUPP_ADDR_SITE' THEN
629           DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, 2, l_varchar_example, 1000);
630           DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, 3, l_varchar_example, 1000);
631           DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, 4, l_varchar_example, 1000);
632           DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, 5, l_varchar_example, 1000);
633           l_userattr_indx_adj := 5;
634       END IF;    -- p_entity_name
635 
636 
637     --------------------------------------------------------------------
638     Write_Conclog('Executing the l_attr_id table looping');
639     --------------------------------------------------------------------
640     -- Loop to Bind the Data Types for the SELECT Columns.
641     --------------------------------------------------------------------
642     FOR i IN 1..l_attr_id_table.COUNT LOOP
643 
644       l_actual_userattr_indx := i + l_userattr_indx_adj;
645 
646       l_data_type_code := SUBSTR (l_intf_col_name_table(i), 1, 1);
647       ------------------------------------------------------------------------
648       -- Based on the Data Type of the attribute, define the column
649       ------------------------------------------------------------------------
650       IF (l_data_type_code = 'C') THEN
651         DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, l_actual_userattr_indx, l_varchar_example, 1000);
652       ELSIF (l_data_type_code = 'N') THEN
653         DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, l_actual_userattr_indx, l_number_example);
654       ELSE --IF (l_data_type_code = 'D') THEN
655         DBMS_SQL.DEFINE_COLUMN(l_cursor_attr_id_val, l_actual_userattr_indx, l_date_example);
656       END IF; --IF (l_data_type_code = 'C') THEN
657     END LOOP; --FOR i IN 1..l_attr_id_table.COUNT LOOP
658     DBMS_SQL.BIND_VARIABLE(l_cursor_attr_id_val,':RESULTFMT_USAGE_ID',p_resultfmt_usage_id);
659     DBMS_SQL.BIND_VARIABLE(l_cursor_attr_id_val,':PROCESS_STATUS',G_PS_IN_PROCESS); -- 2
660 
661     ------------------------------------------------------------------------
662     --  Execute to get the Item User-Defined Attr values.
663     ------------------------------------------------------------------------
664     l_cursor_execute := DBMS_SQL.EXECUTE(l_cursor_attr_id_val);
665     l_rows_per_attr_grp_indx := 0;
666     L_ATTR_GRP_ROW_IDENT := 0;
667     ------------------------------------------------------------------------
668     --  Loop for each row found in EBI
669     ------------------------------------------------------------------------
670     Write_Conclog('Executing LOOP FOR CURSOR_ATTR_ID_VAL ');
671     LOOP --LOOP FOR CURSOR_ATTR_ID_VAL
672       IF DBMS_SQL.FETCH_ROWS(l_cursor_attr_id_val)>0 THEN
673         ------------------------------------------------------------------------
674         --Increment Row Identifier per (Attribute Group + Row) Combination.
675         ------------------------------------------------------------------------
676         L_ATTR_GRP_ROW_IDENT  := L_ATTR_GRP_ROW_IDENT + 1;
677         ------------------------------------------------------------------------
678         -- First column is Transaction ID.
679         ------------------------------------------------------------------------
680 
681       IF p_entity_name = 'SUPP' THEN
682        DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 1, l_transaction_id);
683        DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 2, l_pk1_id_char);
684        DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 3, l_pk2_id_char);
685 
686 
687       ELSIF p_entity_name = 'SUPP_ADDR' THEN
688        DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 1, l_transaction_id);
689        DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 2, l_pk1_id_char);
690        DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 3, l_pk2_id_char);
691        DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 4, l_pk3_id_char);
692      ELSIF p_entity_name = 'SUPP_ADDR_SITE' THEN
693        DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 1, l_transaction_id);
694        DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 2, l_pk1_id_char);
695        DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 3, l_pk2_id_char);
696        DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 4, l_pk3_id_char);
697        DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, 5, l_pk4_id_char);
698 
699       END IF;  -- p_entity_name
700 
701 
702 	   FOR i IN 1..l_attr_id_table.COUNT LOOP
703          OPEN c_attr_grp_n_attr_int_names(l_attr_id_table(i),l_attr_group_type);
704          FETCH c_attr_grp_n_attr_int_names INTO
705            l_attr_group_int_name, l_attr_int_name;
706 		Write_Conclog('Attribute group Internal Name '||l_attr_group_int_name);
707 	        Write_Conclog('Attribute Internal Name '||l_attr_int_name);
708          l_attr_grp_has_data := FALSE;
709          ------------------------------------------------------------------------
710          -- If one more Attribute found for the Attribute Group.
711          ------------------------------------------------------------------------
712          IF c_attr_grp_n_attr_int_names%FOUND THEN
713            l_rows_per_attr_grp_indx := l_rows_per_attr_grp_indx + 1;
714            l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).DATA_SET_ID := p_data_set_id;
715            l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).BATCH_ID := p_batch_id;
716            l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).TRANSACTION_ID := l_transaction_id;
717             Write_Conclog('after TRANSACTION_ID ');
718            --Bug 16384042 here we use actual data_level from outer cursor
719             IF c_attr_grp_rec.data_level_id = G_POS_SUPP_LEVEL_ID THEN
720             --IF p_entity_name = 'SUPP' THEN
721              if (l_pk1_id_char is NULL) THEN
722 
723                 l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).PK1_ID := null;
724                  Write_Conclog('after PK1_ID null');
725              else
726                 Write_Conclog('Before PK1_ID, PK1 = '||l_pk1_id_char);
727                 --FND_NUMBER.CANONICAL_TO_NUMBER
728                 l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).PK1_ID := l_pk1_id_char;
729                 Write_Conclog('after PK1_ID');
730              end if;
731              if (l_pk2_id_char is NULL) THEN
732                 l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).PK2_ID := null;
733                   Write_Conclog('after PK2_ID null');
734              else
735                Write_Conclog('Before PK2_ID, PK2 = '||l_pk2_id_char);
736                 l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).PK2_ID := l_pk2_id_char;
737               Write_Conclog('after PK2_ID');
738              end if;
739             ELSIF c_attr_grp_rec.data_level_id = G_POS_SUPP_ADDR_LEVEL_ID THEN
740             --ELSIF p_entity_name = 'SUPP_ADDR' THEN
741 
742          if (l_pk1_id_char is NULL) THEN
743                 l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).PK1_ID := null;
744              else
745                 l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).PK1_ID := l_pk1_id_char;
746              end if;
747              if (l_pk2_id_char is NULL) THEN
748                 l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).PK2_ID := null;
749              else
750                 l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).PK2_ID := l_pk2_id_char;
751              end if;
752               if (l_pk3_id_char is NULL) THEN
753                 l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).PK3_ID := null;
754              else
755              Write_Conclog('Before PK3_ID, PK3 = '||l_pk3_id_char);
756                 l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).PK3_ID := l_pk3_id_char;
757               Write_Conclog('after PK3_ID');
758              end if;
759             ELSIF c_attr_grp_rec.data_level_id = G_POS_SUPP_ADDR_SITE_LEVEL_ID THEN
760             --ELSIF p_entity_name = 'SUPP_ADDR_SITE' THEN
761               if (l_pk1_id_char is NULL) THEN
762                 l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).PK1_ID := null;
763              else
764                 l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).PK1_ID := l_pk1_id_char;
765              end if;
766              if (l_pk2_id_char is NULL) THEN
767                 l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).PK2_ID := null;
768              else
769                 l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).PK2_ID := l_pk2_id_char;
770              end if;
771               if (l_pk3_id_char is NULL) THEN
772                 l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).PK3_ID := null;
773              else
774                 l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).PK3_ID := l_pk3_id_char;
775              end if;
776               if (l_pk4_id_char is NULL) THEN
777                 l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).PK4_ID := null;
778              else
779                  Write_Conclog('Before PK4_ID, PK4 = '||l_pk4_id_char);
780                 l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).PK4_ID := l_pk4_id_char;
781                  Write_Conclog('After PK4_ID ');
782              end if;
783             END IF;
784              Write_Conclog('before CLASSIFICATION_CODE ');
785            l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).CLASSIFICATION_CODE := c_attr_grp_rec.classification_code;
786             Write_Conclog('before ROW_IDENTIFIER ');
787            l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).ROW_IDENTIFIER := L_ATTR_GRP_ROW_IDENT;
788 
789            l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).ATTR_DATATYPE_CODE := SUBSTR (l_intf_col_name_table(i), 1, 1);
790            l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).ATTR_GROUP_NAME := l_attr_group_int_name;
791            l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).ATTR_NAME := l_attr_int_name;
792            Write_Conclog('before attr_group_id ');
793            l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).ATTR_GROUP_ID := l_attr_group_id_table(i);
794 
795            l_actual_userattr_indx := i + l_userattr_indx_adj;
796            ------------------------------------------------------------------------
797             -- Depending upon the Data Type, populate corresponding field in the
798             -- User-Defined Attribute Data record.
799             ------------------------------------------------------------------------
800            IF (l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).ATTR_DATATYPE_CODE = 'C') THEN
801               DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, l_actual_userattr_indx, l_varchar_data);
802               l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).ATTR_VALUE_STR := l_varchar_data;
803            ELSIF (l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).ATTR_DATATYPE_CODE = 'N') THEN
804               DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, l_actual_userattr_indx, l_number_data);
805               l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).ATTR_VALUE_NUM := l_number_data;
806            ELSE --IF (l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).ATTR_DATATYPE_CODE = 'D') THEN
807               DBMS_SQL.COLUMN_VALUE(l_cursor_attr_id_val, l_actual_userattr_indx, l_date_data);
808               l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).ATTR_VALUE_DATE := l_date_data;
809            END IF; --end: IF (l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).ATTR_DATATYPE_CODE = 'C') THEN
810            l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).INTF_COLUMN_NAME := l_intf_col_name_table(i);
811            ------------------------------------------------------------------------
812            -- Donot populate NULL Attribute value in the User-Defined Attrs
813            -- Interface table.
814            ------------------------------------------------------------------------
815            IF ((l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).ATTR_VALUE_STR IS NULL) AND
816                (l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).ATTR_VALUE_NUM IS NULL) AND
817                (l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).ATTR_VALUE_DATE IS NULL)
818                ) THEN
819               ------------------------------------------------------------------------
820               -- If all attribute values are NULL value, then delete
821               -- the row from PLSQL table.
822               ------------------------------------------------------------------------
823 	       Write_Conclog('All the Attribute values are Null');
824               l_usr_attr_data_tbl.DELETE(l_rows_per_attr_grp_indx);
825               l_rows_per_attr_grp_indx := l_rows_per_attr_grp_indx - 1;
826            END IF; --end: IF ((l_usr_attr_data_tbl(l_rows_per_attr_grp_indx).ATTR_VALUE_STR...
827          END IF; --end: IF c_attr_grp_n_attr_int_names%FOUND THEN
828          CLOSE c_attr_grp_n_attr_int_names;
829        END LOOP; --end: FOR i IN 1..l_attr_id_table.COUNT LOOP
830     ELSE --end: IF DBMS_SQL.FETCH_ROWS(l_cursor_attr_id_val)>0 THEN
831         Write_Conclog('No Rows Found (or) All rows are Done.');
832         EXIT;
833    END IF; --IF DBMS_SQL.FETCH_ROWS(l_cursor_attr_id_val)>0 THEN
834   END LOOP; --END: LOOP FOR CURSOR_ATTR_ID_VAL
835       l_attr_id_table.DELETE;
836       l_intf_col_name_table.DELETE;
837     ------------------------------------------------------------------------
838       DBMS_SQL.CLOSE_CURSOR(l_cursor_attr_id_val);
839 	     -------------------------------------------------------------------
840       -- Loop for all the rows to be inserted per Attribute Group.
841       -------------------------------------------------------------------
842       FOR i IN 1..l_rows_per_attr_grp_indx LOOP
843         -------------------------------------------------------------------------
844         -- Fix for Bug# 3808455. To avoid the following error:
845         -- ORA-01401: inserted value too large for column
846         -- [This is done because ATTR_DISP_VALUE size is 1000 Chars]
847         -------------------------------------------------------------------------
848         IF ( LENGTH(l_usr_attr_data_tbl(i).ATTR_VALUE_STR) > 1000 ) THEN
849           l_token_tbl_one(1).token_name  := 'VALUE';
850           l_token_tbl_one(1).token_value := l_usr_attr_data_tbl(i).ATTR_VALUE_STR;
851            Write_Conclog('Inserted Attribute value too large....');
852         ELSE
853            l_varchar_data      := NULL;
854           IF (l_usr_attr_data_tbl(i).ATTR_DATATYPE_CODE = 'C') THEN
855              l_varchar_data := l_usr_attr_data_tbl(i).ATTR_VALUE_STR;
856           ELSIF (l_usr_attr_data_tbl(i).ATTR_DATATYPE_CODE = 'N') THEN
857              IF (l_usr_attr_data_tbl(i).ATTR_VALUE_NUM IS NOT NULL) THEN
858                l_varchar_data := To_char(l_usr_attr_data_tbl(i).ATTR_VALUE_NUM);
859              END IF;
860           ELSE
861             IF (l_usr_attr_data_tbl(i).ATTR_VALUE_DATE IS NOT NULL) THEN
862               l_varchar_data := To_Char(l_usr_attr_data_tbl(i).ATTR_VALUE_DATE , G_DATE_FORMAT);
863             END IF;
864           END IF; --end: IF (l_usr_attr_data_tbl(i).ATTR_DATATYPE_CODE = 'C') THEN
865     	  	Write_Conclog('DATA SET ID ' || l_usr_attr_data_tbl(i).DATA_SET_ID);
866           Write_Conclog('BATCH ID ' || l_usr_attr_data_tbl(i).BATCH_ID);
867 		Write_Conclog('TRANSACTION_ID ' || l_usr_attr_data_tbl(i).TRANSACTION_ID);
868 		Write_Conclog('PK1_ID ' || l_usr_attr_data_tbl(i).PK1_ID);
869                 Write_Conclog('PK2_ID ' || l_usr_attr_data_tbl(i).PK2_ID);
870                 Write_Conclog('PK3_ID ' || l_usr_attr_data_tbl(i).PK3_ID);
871                 Write_Conclog('PK4_ID ' || l_usr_attr_data_tbl(i).PK4_ID);
872                 Write_Conclog('PK5_ID ' || l_usr_attr_data_tbl(i).PK5_ID);
873 
874 		Write_Conclog('ROW_IDENT ' || l_usr_attr_data_tbl(i).ROW_IDENTIFIER);
875 		Write_Conclog('CLASS ' || l_usr_attr_data_tbl(i).CLASSIFICATION_CODE);
876 		Write_Conclog('ATTR_GROUP_INT_NAME ' || l_usr_attr_data_tbl(i).ATTR_GROUP_NAME);
877 		Write_Conclog('ATTR_INT_NAME ' || l_usr_attr_data_tbl(i).ATTR_NAME);
878 		Write_Conclog('ATTR_DISP_VALUE ' || l_varchar_data);
879 
880 		 ---------------------------------------------------------------
881 		 -- UDA EGO WEBADI
882 		 ----------------------------------------------------------------
883         --Bug 16384042 Here also need to use DataLevel from outer cursor
884         -- Here we call Resolve_PK_VALUES to get PK for party site and Supplier Site level
885         IF c_attr_grp_rec.data_level_id = G_POS_SUPP_LEVEL_ID THEN
886         --IF p_entity_name  = 'SUPP' THEN
887                 INSERT INTO POS_SUPP_PROF_EXT_INTF
888                  (
889                   DATA_SET_ID         ,
890                   TRANSACTION_ID      ,
891                   PARTY_ID    	      ,
892                   --PK1_VALUE           ,
893                   ROW_IDENTIFIER      ,
894                   PROCESS_STATUS      ,
895                   CLASSIFICATION_CODE ,
896                   ATTR_GROUP_INT_NAME ,
897                   ATTR_INT_NAME       ,
898                   ATTR_DISP_VALUE     ,
899                   DATA_LEVEL_ID       ,
900 		              DATA_LEVEL_NAME,
901                   BATCH_ID
902                   )
903                   VALUES
904                  (
905                  l_usr_attr_data_tbl(i).DATA_SET_ID,
906                  l_usr_attr_data_tbl(i).TRANSACTION_ID,
907                  to_number(l_usr_attr_data_tbl(i).PK1_ID),
908                  --l_usr_attr_data_tbl(i).PK2_ID,
909                  l_usr_attr_data_tbl(i).ROW_IDENTIFIER,
910                  G_PS_TO_BE_PROCESSED,
911                  l_usr_attr_data_tbl(i).CLASSIFICATION_CODE,
912                  l_usr_attr_data_tbl(i).ATTR_GROUP_NAME,
913                  l_usr_attr_data_tbl(i).ATTR_NAME,
914                  l_varchar_data,
915                  G_POS_SUPP_LEVEL_ID,
916                  G_POS_SUPP_LEVEL,
917                   l_usr_attr_data_tbl(i).BATCH_ID
918                  );
919 
920 		        Write_conclog('ATTR_ID: '||l_usr_attr_data_tbl(i).ATTR_GROUP_ID);
921                         Write_conclog('PARTY_ID: '||l_usr_attr_data_tbl(i).PK1_ID);
922                        -- Write_conclog('PK1_VALUE: '||l_usr_attr_data_tbl(i).PK2_ID);
923         ELSIF c_attr_grp_rec.data_level_id = G_POS_SUPP_ADDR_LEVEL_ID THEN
924          Write_Conclog('Before Inserting into Ext intf for Party Site level');
925 
926                 INSERT INTO POS_SUPP_PROF_EXT_INTF
927                  (
928                   DATA_SET_ID         ,
929                   TRANSACTION_ID      ,
930                   PARTY_ID    	      ,
931                   PK1_VALUE           , --Party_Site_ID
932                   --PK2_VALUE           ,
933                   ROW_IDENTIFIER      ,
934                   PROCESS_STATUS      ,
935                   CLASSIFICATION_CODE ,
936                   ATTR_GROUP_INT_NAME ,
937                   ATTR_INT_NAME       ,
938                   ATTR_DISP_VALUE     ,
939                   DATA_LEVEL_ID       ,
940                   DATA_LEVEL_NAME,
941                   BATCH_ID
942                   )
943                   VALUES
944                  (
945                  l_usr_attr_data_tbl(i).DATA_SET_ID,
946                  l_usr_attr_data_tbl(i).TRANSACTION_ID,
947                  to_number(l_usr_attr_data_tbl(i).PK1_ID),
948                  --l_PK1_Value,        -- Party_Site_ID
949                  l_usr_attr_data_tbl(i).PK3_ID, -- PK3 is Party_Site_ID
950                  l_usr_attr_data_tbl(i).ROW_IDENTIFIER,
951                  G_PS_TO_BE_PROCESSED,
952                  l_usr_attr_data_tbl(i).CLASSIFICATION_CODE,
953                  l_usr_attr_data_tbl(i).ATTR_GROUP_NAME,
954                  l_usr_attr_data_tbl(i).ATTR_NAME,
955                  l_varchar_data,
956                  G_POS_SUPP_ADDR_LEVEL_ID,
957 		 G_POS_SUPP_ADDR_LEVEL,
958         l_usr_attr_data_tbl(i).BATCH_ID
959                  );
960         ELSIF  c_attr_grp_rec.data_level_id = G_POS_SUPP_ADDR_SITE_LEVEL_ID THEN
961 
962          Write_Conclog('Before Insreting into Ext Intf for Supplier Site level' );
963 
964                 INSERT INTO POS_SUPP_PROF_EXT_INTF
965                  (
966                   DATA_SET_ID         ,
967                   TRANSACTION_ID      ,
968                   PARTY_ID    	      ,
969                   PK1_VALUE           , --Party_Site_ID
970                   PK2_VALUE           , --Vendor_Site_ID
971                   --PK3_VALUE           ,
972                   ROW_IDENTIFIER      ,
973                   PROCESS_STATUS      ,
974                   CLASSIFICATION_CODE ,
975                   ATTR_GROUP_INT_NAME ,
976                   ATTR_INT_NAME       ,
977                   ATTR_DISP_VALUE     ,
978 				  DATA_LEVEL_ID       ,
979 				  DATA_LEVEL_NAME,
980           BATCH_ID
981                   )
982                   VALUES
983                  (
984                  l_usr_attr_data_tbl(i).DATA_SET_ID,
985                  l_usr_attr_data_tbl(i).TRANSACTION_ID,
986                  to_number(l_usr_attr_data_tbl(i).PK1_ID),
987                  --l_PK1_Value,        -- Party_Site_ID
988                  --l_PK2_Value,        -- _Site_ID
989                  --l_usr_attr_data_tbl(i).PK2_ID,
990                  l_usr_attr_data_tbl(i).PK3_ID, -- Party_Site_ID
991                  l_usr_attr_data_tbl(i).PK4_ID,  -- Vendor_Site_ID
992                  l_usr_attr_data_tbl(i).ROW_IDENTIFIER,
993                  G_PS_TO_BE_PROCESSED,
994                  l_usr_attr_data_tbl(i).CLASSIFICATION_CODE,
995                  l_usr_attr_data_tbl(i).ATTR_GROUP_NAME,
996                  l_usr_attr_data_tbl(i).ATTR_NAME,
997                  l_varchar_data,
998 		 G_POS_SUPP_ADDR_SITE_LEVEL_ID,
999 		 G_POS_SUPP_ADDR_SITE_LEVEL,
1000         l_usr_attr_data_tbl(i).BATCH_ID
1001                  );
1002          END IF;
1003         END IF; --end: IF ( LENGTH(l_usr_attr_data_tbl(i)..
1004       END LOOP; --FOR i IN 1..l_usr_attr_data_tbl.COUNT LOOP
1005      Write_Conclog('Populated the User-Defined Attr Values for Attribute Group : '||l_attr_group_int_name);
1006    END LOOP; --FOR c_attr_grp_rec IN c_user_attr_group_codes
1007 
1008     x_retcode := G_STATUS_SUCCESS;
1009     x_errbuff := null;
1010  EXCEPTION
1011    WHEN OTHERS THEN
1012       x_retcode := G_STATUS_ERROR;
1013       x_errbuff := SUBSTRB(SQLERRM, 1,240);
1014        Write_Conclog('Error! While Loading User Defined Attributes into Interface tables' ) ;
1015       Write_Conclog('Error while processing Load User Attributes data API  '||SQLCODE || ':'||SQLERRM);
1016 END LOAD_USERATTR_INTF;
1017 -----------------------end of load_userattr_intf---------------------------
1018 -------------------------------------------------------------------------------
1019 --processing of Site Attributes from POS_SUPP_PROF_EXT_INTF
1020 --------------------------------------------------------------------------------
1021 PROCEDURE PROCESS_USER_ATTRS_DATA(
1022 			   	ERRBUF                          OUT NOCOPY VARCHAR2
1023 		       ,RETCODE                         OUT NOCOPY VARCHAR2
1024 		       ,p_data_set_id                   IN   NUMBER
1025 		       ,p_purge_successful_lines        IN   VARCHAR2 DEFAULT FND_API.G_FALSE) is
1026     l_entity_index_counter   NUMBER := 0;
1027     l_debug_level           NUMBER  := 0;
1028     l_user_attrs_return_status VARCHAR2(100);
1029     l_return_status          VARCHAR2(1);
1030     l_errorcode              NUMBER;
1031     l_msg_count              NUMBER;
1032     l_msg_data               VARCHAR2(1000);
1033     l_rel_sql 								 VARCHAR2(100);
1034     l_cnt                   NUMBER      := 0;
1035    CURSOR c_pos_supp_prof_ext_intf is
1036    select count(*) from POS_SUPP_PROF_EXT_INTF
1037    WHERE PROCESS_STATUS = G_PS_TO_BE_PROCESSED
1038    AND DATA_SET_ID      = p_data_set_id;
1039 BEGIN
1040 
1041  Write_Conclog('Processing the User Defined Attributes ' );
1042 
1043    IF (Error_Handler.Get_Debug = 'Y') THEN
1044      l_debug_level := 3; --continue writing to the Debug Log opened.
1045    ELSE
1046      l_debug_level := 0; --Since Debug log is not opened, donot open Debug log for User-Attrs also.
1047    END IF;
1048 
1049      l_user_attrs_return_status :=  FND_API.G_RET_STS_SUCCESS;
1050      Open c_pos_supp_prof_ext_intf;
1051      fetch c_pos_supp_prof_ext_intf  into  l_cnt;
1052      Close c_pos_supp_prof_ext_intf;
1053     IF l_cnt > 0  THEN
1054           UPDATE POS_SUPP_PROF_EXT_INTF
1055           SET PROCESS_STATUS = G_PS_IN_PROCESS
1056           ,REQUEST_ID = G_REQUEST_ID
1057           ,PROGRAM_APPLICATION_ID = G_PROGAM_APPLICATION_ID
1058           ,PROGRAM_ID = G_PROGAM_ID
1059           ,PROGRAM_UPDATE_DATE = SYSDATE
1060           ,CREATED_BY = DECODE(CREATED_BY, NULL, G_USER_ID, CREATED_BY)
1061           ,CREATION_DATE = DECODE(CREATION_DATE, NULL, SYSDATE, CREATION_DATE)
1062           ,LAST_UPDATED_BY = G_USER_ID
1063           ,LAST_UPDATE_DATE = SYSDATE
1064           ,LAST_UPDATE_LOGIN = G_LOGIN_ID
1065           ,TRANSACTION_TYPE = UPPER(NVL(TRANSACTION_TYPE,EGO_USER_ATTRS_DATA_PVT.G_SYNC_MODE))
1066 	  ,IS_PROSPECT = 'N'
1067           -- Bug16384042 We cannot set PK1_value to null irrespective of what DataLevel AG is using
1068 	  --,PK1_VALUE = null
1069           ,CLASSIFICATION_CODE = 'BS:BASE'
1070            WHERE DATA_SET_ID = p_data_set_id
1071            AND (PROCESS_STATUS IS NULL OR
1072             PROCESS_STATUS = G_PS_TO_BE_PROCESSED);
1073           l_rel_sql 		  := 'SELECT CODE FROM POS_SUPP_PROF_EXT_OCV';
1074 
1075            -- Bug 12747017 Adding gather stats to improve performance, only doing it when volume are large. Ref Implementation:RRSIMINB.pls
1076            IF l_cnt > 1000 THEN --hard-code threshold value 1000 here may need to use profile option in future
1077            Write_Conclog('Before Gathering Stats... l_cnt = '||l_cnt);
1078            fnd_stats.gather_table_stats('POS','POS_SUPP_PROF_EXT_INTF',cascade=>true,percent=>30);
1079            fnd_stats.gather_table_stats('POS','POS_SUPP_PROF_EXT_B',cascade=>true,percent=>30);
1080            fnd_stats.gather_table_stats('POS','POS_SUPP_PROF_EXT_TL',cascade=>true,percent=>30);
1081            Write_Conclog('Done Gathering Stats....');
1082            END IF;
1083 
1084 	   Write_Conclog('Executing EGO_USER_ATTRS_BULK_PVT.Bulk_Load_User_Attrs_Data -'||'HZ_PARTIES: Supplier Hub' );
1085 
1086             EGO_USER_ATTRS_BULK_PVT.Bulk_Load_User_Attrs_Data (
1087             p_api_version                   =>  G_API_VERSION                    --IN   NUMBER
1088            ,p_application_id                =>  177                             --IN   NUMBER
1089            ,p_attr_group_type               =>  'POS_SUPP_PROFMGMT_GROUP'             --IN   VARCHAR2
1090            ,p_object_name                   =>  'HZ_PARTIES'                      --IN   VARCHAR2
1091    	   ,p_hz_party_id                   =>   G_HZ_PARTY_ID
1092            ,p_interface_table_name          =>  'POS_SUPP_PROF_EXT_INTF'               --IN   VARCHAR2
1093            ,p_data_set_id                   =>  p_data_set_id                    --IN   NUMBER
1094            ,p_entity_id                     =>  0 --G_ENTITY_ID                      --IN   NUMBER
1095            ,p_entity_index                  =>  0 --l_entity_index_counter           --IN   NUMBER
1096            ,p_entity_code                   =>  'HZ_PARTIES'                   --IN   VARCHAR2
1097            ,p_debug_level                   =>  l_debug_level                    --IN   NUMBER
1098            ,p_init_error_handler            =>  FND_API.G_FALSE                  --IN   VARCHAR2
1099            ,p_init_fnd_msg_list             =>  FND_API.G_FALSE                  --IN   VARCHAR2
1100            ,p_log_errors                    =>  FND_API.G_TRUE                   --IN   VARCHAR2
1101            ,p_add_errors_to_fnd_stack       =>  FND_API.G_TRUE                   --IN   VARCHAR2
1102            ,p_commit                        =>  FND_API.G_FALSE                   --IN   VARCHAR2
1103            ,p_default_view_privilege        =>  NULL                              --IN   VARCHAR2
1104            ,p_default_edit_privilege        =>  NULL
1105            ,p_privilege_predicate_api_name  =>  NULL
1106            ,p_related_class_codes_query     =>  l_rel_sql                              --IN   VARCHAR2
1107 		   ,p_validate                      =>  TRUE
1108    	       ,p_do_dml                        =>  TRUE
1109            ,x_return_status                 =>  l_user_attrs_return_status       --OUT NOCOPY VARCHAR2
1110            ,x_errorcode                     =>  l_errorcode                      --OUT NOCOPY NUMBER
1111            ,x_msg_count                     =>  l_msg_count                      --OUT NOCOPY NUMBER
1112            ,x_msg_data                      =>  l_msg_data                       --OUT NOCOPY VARCHAR2
1113             );
1114 
1115 			Write_Conclog('Executed User Defined Attributes Upload API');
1116                         Write_Conclog('G_API_VERSION ' || G_API_VERSION);
1117 			Write_Conclog('G_HZ_PARTY_ID ' ||  G_HZ_PARTY_ID);
1118 			Write_Conclog('p_data_set_id ' || p_data_set_id);
1119 			Write_Conclog('G_ENTITY_ID ' || G_ENTITY_ID);
1120 			Write_Conclog('l_entity_index_counter ' || l_entity_index_counter);
1121 			Write_Conclog(' G_ENTITY_CODE ' || G_ENTITY_CODE );
1122 			Write_Conclog('l_debug_level ' || l_debug_level );
1123 			Write_Conclog('l_rel_sql ' ||  l_rel_sql );
1124 			Write_Conclog('Return Status '||l_user_attrs_return_status);
1125             Write_Conclog('Error Code '||l_errorcode);
1126             Write_Conclog('msg count '||l_msg_count);
1127             Write_Conclog('msg data '||l_msg_data);
1128 
1129 
1130                IF (FND_API.To_Boolean(p_purge_successful_lines)) THEN
1131                   -----------------------------------------------
1132                   -- Delete all successful rows from the table --
1133                   -- (they're the only rows still in process)  --
1134                    -----------------------------------------------
1135                   DELETE FROM POS_SUPP_PROF_EXT_INTF
1136                   WHERE DATA_SET_ID = p_data_set_id
1137                   AND PROCESS_STATUS = G_PS_IN_PROCESS;
1138             ELSE
1139                    ----------------------------------------------
1140                    -- Mark all rows we've processed as success --
1141                    -- if they weren't marked as failure above  --
1142                    ----------------------------------------------
1143                  UPDATE POS_SUPP_PROF_EXT_INTF
1144                  SET PROCESS_STATUS = G_PS_SUCCESS
1145                  WHERE DATA_SET_ID = p_data_set_id
1146                  AND PROCESS_STATUS = G_PS_IN_PROCESS;
1147             END IF;  -- p_purge_successful_lines
1148       END IF;  -- IF l_cnt > 0
1149 
1150 
1151 
1152 
1153       -------------------------------------------------------------------
1154       -- Finally, we log any errors that we've accumulated throughout  --
1155       -- our conversions and looping (including all errors encountered --
1156       -- within our Business Object's processing)                      --
1157       -------------------------------------------------------------------
1158       Write_Conclog('****Dumping the List of Error messages into the Concurrent Log***');
1159 
1160       ERROR_HANDLER.Log_Error(
1161         p_write_err_to_inttable         => 'N'
1162        ,p_write_err_to_conclog          => 'Y'
1163        ,p_write_err_to_debugfile        => 'Y'
1164       );
1165      Write_Conclog('****End of All Error messages***');
1166     -----------------------------------------------------------
1167     -- Let caller know whether any rows failed in processing --
1168     -----------------------------------------------------------
1169     IF (  l_user_attrs_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1170       RETCODE := G_RETCODE_SUCCESS_WITH_WARNING;
1171     ELSE
1172       RETCODE := FND_API.G_RET_STS_SUCCESS;
1173     END IF;
1174 EXCEPTION
1175 WHEN OTHERS THEN
1176       ----------------------------------------
1177       -- Mark all rows in process as errors --
1178       ----------------------------------------
1179      l_cnt := 0;
1180      Open c_pos_supp_prof_ext_intf;
1181      fetch c_pos_supp_prof_ext_intf  into  l_cnt;
1182      Close c_pos_supp_prof_ext_intf;
1183      IF l_cnt > 0  THEN
1184          UPDATE POS_SUPP_PROF_EXT_INTF
1185          SET PROCESS_STATUS = G_PS_GENERIC_ERROR
1186          WHERE DATA_SET_ID = p_data_set_id
1187          AND PROCESS_STATUS = G_PS_IN_PROCESS;
1188      END IF;
1189 
1190       Write_Conclog('Error! While Processing User Defined Attributes ' ) ;
1191       Write_Conclog('Error while processing Process User Attrs data API  '||SQLCODE || ':'||SQLERRM);
1192       RETCODE := FND_API.G_RET_STS_UNEXP_ERROR;
1193 END Process_User_Attrs_Data;
1194   ---------------------------------------------------------------------
1195   -- Main API called by Java Concurrent program
1196   ---------------------------------------------------------------------
1197 PROCEDURE BULKLOADENTITIES(
1198         ERRBUF                  OUT   NOCOPY  VARCHAR2,
1199         RETCODE                 OUT  NOCOPY   VARCHAR2,
1200         result_format_usage_id  IN      NUMBER,
1201         user_id                 IN      NUMBER,
1202         LANGAUGE                IN      VARCHAR2,
1203         resp_id                 IN      NUMBER,
1204         appl_id                 IN      NUMBER,
1205         batch_id                IN      NUMBER )
1206 IS
1207   l_region_application_id NUMBER := 0  ;
1208   l_customization_application_id NUMBER := 0  ;
1209   l_region_code VARCHAR2(30);
1210   Current_Error_Code      VARCHAR2(20) := NULL;
1211   conc_status             BOOLEAN      ;
1212   l_target_api_call NUMBER :=0;
1213   l_debug       VARCHAR2(80);
1214   l_errbuf varchar2(2000);
1215   l_retcode varchar2(2000);
1216   --l_rrs_set_process_id     NUMBER;
1217   l_process_status  char(100);
1218 BEGIN
1219    Write_Conclog('Executing the BulkLoadEntities API ');
1220    Write_Conclog('User Defined Attributes Import Program *STARTED*');
1221   UPDATE EGO_BULKLOAD_INTF
1222   SET
1223     PROCESS_STATUS = G_PS_IN_PROCESS,
1224     LAST_UPDATE_LOGIN = FND_GLOBAL.login_id,
1225     REQUEST_ID = FND_GLOBAL.conc_request_id,
1226     PROGRAM_APPLICATION_ID = FND_GLOBAL.prog_appl_id,
1227     PROGRAM_ID = FND_GLOBAL.conc_program_id
1228   WHERE RESULTFMT_USAGE_ID = result_format_usage_id
1229     AND process_status = G_PS_TO_BE_PROCESSED;
1230 
1231    --Bug 16384042 Calling Resolve PK Values to set PK3 and PK4 in EBI table
1232    Write_Conclog('Before calling Resolve_PK_values');
1233    RESOLVE_PK_VALUES(
1234                      RETCODE                  => l_retcode,
1235                      p_result_format_usage_id => result_format_usage_id
1236    );
1237 
1238    LOAD_USERATTR_INTF(
1239 	   	  		 p_resultfmt_usage_id    => result_format_usage_id,
1240                  p_data_set_id           => batch_id,
1241                  x_errbuff               => l_errbuf,
1242                  x_retcode               => l_retcode,
1243                  --Bug16384042 No need to call the same API three times,
1244                  --always pass SUPP_ADDR_SITE as this returns the most PKs, whether PK will be used is determined later
1245                  p_entity_name           => 'SUPP_ADDR_SITE',
1246                  p_batch_id              => batch_id
1247                 );
1248     --LOAD_USERATTR_INTF(
1249 	--   	  		 p_resultfmt_usage_id    => result_format_usage_id,
1250           --       p_data_set_id           => batch_id,
1251             --     x_errbuff               => l_errbuf,
1252               --   x_retcode               => l_retcode,
1253                 -- p_entity_name           => 'SUPP_ADDR',
1254               --   p_batch_id              => batch_id
1255      --           );
1256     --LOAD_USERATTR_INTF(
1257 	   	-- p_resultfmt_usage_id    => result_format_usage_id,
1258                 -- p_data_set_id           => batch_id,
1259                 -- x_errbuff               => l_errbuf,
1260                  --x_retcode               => l_retcode,
1261                  --p_entity_name           => 'SUPP_ADDR_SITE',
1262                  --p_batch_id              => batch_id
1263       --          );
1264    --PROCESS_USER_ATTRS_DATA(
1265 	--		   	ERRBUF                    =>   l_errbuf,
1266 	--	        RETCODE                   =>   l_retcode,
1267 	--	        p_data_set_id             =>   batch_id,
1268 	--	        p_purge_successful_lines  =>   FND_API.G_FALSE);
1269 
1270 Write_Conclog('Return Code After Executing LOAD and PROCESS APIs '||l_retcode);
1271 Write_Conclog('Error Code After Executing LOAD and PROCESS APIs '||l_errbuf);
1272 
1273   IF l_retcode = 'S' THEN
1274               UPDATE EGO_BULKLOAD_INTF
1275               SET PROCESS_STATUS = G_PS_SUCCESS,
1276               LAST_UPDATE_LOGIN = FND_GLOBAL.login_id,
1277               REQUEST_ID = FND_GLOBAL.conc_request_id,
1278               PROGRAM_APPLICATION_ID = FND_GLOBAL.prog_appl_id,
1279               PROGRAM_ID = FND_GLOBAL.conc_program_id
1280               WHERE RESULTFMT_USAGE_ID = result_format_usage_id
1281               AND process_status = G_PS_IN_PROCESS;
1282   END IF ;
1283   ERRBUF := l_errbuf;
1284   RETCODE := l_retcode;
1285   Write_Conclog('User Defined Attributes Import Program *COMPLETED* ');
1286  EXCEPTION
1287     WHEN OTHERS THEN
1288        Write_Conclog('Error while processing BulkloadEntities API  '||SQLCODE || ':'||SQLERRM);
1289        Write_Conclog('Error! While Running User Defined Attributes Import Program ');
1290        RETCODE := 'E';
1291        Current_Error_Code := To_Char(SQLCODE);
1292        conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', Current_Error_Code);
1293 END BULKLOADENTITIES;
1294 
1295 
1296 
1297 PROCEDURE LOAD_PARTY_INTF(
1298         ERRBUF                  OUT  NOCOPY   VARCHAR2,
1299         RETCODE                 OUT  NOCOPY   VARCHAR2,
1300         p_batch_id              IN      NUMBER,
1301         P_PARTY_ORIG_SYSTEM     IN      VARCHAR2,
1302         P_PARTY_ORIG_SYSTEM_REFERENCE IN VARCHAR2,
1303         P_INSERT_UPDATE_FLAG IN VARCHAR2,
1304         P_PARTY_TYPE IN VARCHAR2,
1305         P_ORGANIZATION_NAME IN VARCHAR2,
1306         P_PERSON_FIRST_NAME IN VARCHAR2,
1307         P_PERSON_LAST_NAME IN VARCHAR2
1308         )
1309 
1310 IS
1311 
1312  l_debug       VARCHAR2(80);
1313   l_errbuf varchar2(2000);
1314   l_retcode varchar2(2000);
1315 
1316 BEGIN
1317 
1318 DELETE FROM HZ_IMP_PARTIES_INT
1319    WHERE batch_id = p_batch_id;
1320    Write_Conclog('HZ_IMP_PARTIES_INT Completed');
1321 
1322    DELETE FROM Ap_Suppliers_Int
1323    WHERE sdh_BATCH_ID = P_BATCH_ID;
1324    Write_Conclog('Ap_Suppliers_Int Completed');
1325 
1326 INSERT
1327   INTO HZ_IMP_PARTIES_INT
1328     (
1329       BATCH_ID,
1330       PARTY_ORIG_SYSTEM,
1331       PARTY_ORIG_SYSTEM_REFERENCE,
1332       INSERT_UPDATE_FLAG,
1333       PARTY_TYPE,
1334       ORGANIZATION_NAME,
1335       PERSON_FIRST_NAME,
1336       PERSON_LAST_NAME,
1337       creation_date
1338     )
1339     VALUES
1340     (p_batch_id,
1341       P_PARTY_ORIG_SYSTEM,
1342       P_PARTY_ORIG_SYSTEM_REFERENCE,
1343       P_INSERT_UPDATE_FLAG,
1344       P_PARTY_TYPE,
1345        P_ORGANIZATION_NAME,
1346       P_PERSON_FIRST_NAME,
1347       P_PERSON_LAST_NAME,
1348       sysdate
1349     );
1350 
1351     INSERT INTO AP_SUPPLIERS_INT
1352    (VENDOR_INTERFACE_ID,
1353    VENDOR_NAME,
1354    SDH_BATCH_ID ,
1355    PARTY_ORIG_SYSTEM  ,
1356    PARTY_ORIG_SYSTEM_REFERENCE
1357    )
1358    VALUES
1359    (P_BATCH_ID,
1360    P_ORGANIZATION_NAME,
1361    P_BATCH_ID,
1362    P_PARTY_ORIG_SYSTEM,
1363    P_PARTY_ORIG_SYSTEM_REFERENCE
1364    );
1365 
1366   DELETE FROM HZ_IMP_PARTIES_INT
1367    WHERE batch_id = p_batch_id;
1368    Write_Conclog('HZ_IMP_PARTIES_INT Completed');
1369 
1370    DELETE FROM Ap_Suppliers_Int
1371    WHERE SDH_BATCH_ID = P_BATCH_ID;
1372    WRITE_CONCLOG('Ap_Suppliers_Int Completed');
1373 
1374   ERRBUF := l_errbuf;
1375   RETCODE := 'S';
1376   Write_Conclog('INSERT PARTY CHANGES ');
1377  EXCEPTION
1378     WHEN OTHERS THEN
1379        Write_Conclog('Error while processing LOAD_PARTY_INTF API  '||SQLCODE || ':'||SQLERRM);
1380        Write_Conclog('Error! While Running LOAD_PARTY_INTF ');
1381        RETCODE := 'E';
1382 
1383 
1384 END LOAD_PARTY_INTF;
1385 
1386 END;