[Home] [Help]
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);
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;
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,
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
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);
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);
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;
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: --------------------------------------------------------------------
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,
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,