[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
DELETE FROM EGO_BULKLOAD_INTF
WHERE RESULTFMT_USAGE_ID = p_result_format_usage_id
AND PROCESS_STATUS <> G_DATA_ROWS_UPLOADED_NEW;
SELECT VALUE
FROM V$PARAMETER
WHERE NAME = 'utl_file_dir';
SELECT INTF_COLUMN_NAME
FROM ego_results_fmt_usages
WHERE attribute_code = 'PS_PARTY_SITE_NAME'
AND resultfmt_usage_id = c_fmt_usage_id
AND CUSTOMIZATION_APPLICATION_ID = G_APPLICATION_ID
AND rownum < 2;
SELECT INTF_COLUMN_NAME
FROM ego_results_fmt_usages
WHERE attribute_code = 'SS_VENDOR_SITE_CODE'
AND resultfmt_usage_id = c_fmt_usage_id
AND CUSTOMIZATION_APPLICATION_ID = G_APPLICATION_ID
AND rownum < 2;
SELECT *
FROM ego_bulkload_intf
WHERE resultfmt_usage_id = c_fmt_usage_id;
SELECT PS.Party_Site_ID
FROM AP_Suppliers AP,
HZ_PARTY_SITES PS
WHERE AP.Party_ID = PS.Party_ID
AND AP.Party_ID = c_Party_ID
AND PS.STATUS = 'A'
AND PS.PARTY_SITE_NAME = c_address_name
AND rownum < 2;
SELECT SS.Vendor_Site_ID
FROM AP_Suppliers AP,
AP_SUPPLIER_SITES_ALL SS,
HZ_PARTY_SITES PS
WHERE AP.Party_ID = PS.Party_ID
AND AP.Party_ID = c_Party_ID
AND PS.STATUS = 'A'
AND PS.Party_Site_ID = c_party_site_id
AND PS.Party_Site_ID = SS.Party_Site_ID
AND SS.VENDOR_SITE_CODE = c_vendor_site_code
AND SS.INACTIVE_DATE IS NULL
AND rownum < 2;
l_dyn_sql := 'SELECT '||l_addr_col_name
||' FROM ego_bulkload_intf '
||' WHERE resultfmt_usage_id = :1 '
||' AND transaction_id = :2 ';
l_dyn_sql := 'SELECT '||l_site_col_name
||' FROM ego_bulkload_intf '
||' WHERE resultfmt_usage_id = :1 '
||' AND transaction_id = :2 ';
Write_Conclog('Executing RESOLVE_PK_VALUES --Before final update');
UPDATE EGO_BULKLOAD_INTF
SET INSTANCE_PK3_VALUE = l_pk3_value,
INSTANCE_PK4_VALUE = l_pk4_value
WHERE resultfmt_usage_id = c_ebi_rec.resultfmt_usage_id
AND transaction_id = c_ebi_rec.transaction_id;
SELECT PK1_Value, PK2_Value
FROM pos_supp_prof_ext_b
WHERE party_id = c_party_id
AND data_level_id = c_data_level_id
AND attr_group_id = c_attr_group_id
AND rownum < 2;
SELECT PS.Party_Site_ID
FROM AP_Suppliers AP,
HZ_PARTY_SITES PS
WHERE AP.Party_ID = PS.Party_ID
AND AP.Party_ID = p_Party_ID
AND PS.STATUS = 'A'
AND rownum < 2;
SELECT SS.Party_Site_ID,
SS.Vendor_Site_ID
FROM AP_Suppliers AP,
AP_SUPPLIER_SITES_ALL SS,
HZ_PARTY_SITES PS
WHERE AP.Party_ID = PS.Party_ID
AND AP.Party_ID = p_Party_ID
AND PS.STATUS = 'A'
AND PS.Party_Site_ID = SS.Party_Site_ID
AND SS.INACTIVE_DATE IS NULL
AND rownum < 2;
SELECT DISTINCT To_Number(SUBSTR(attribute_code, 1, INSTR(attribute_code, '$$') - 1)) attr_group_id,classification_code, data_level_id
FROM ego_results_fmt_usages
WHERE resultfmt_usage_id = c_resultfmt_usage_id
AND attribute_code LIKE '%$$%'
AND To_Number(SUBSTR(attribute_code, 1, INSTR(attribute_code, '$$') - 1)) IN
(
SELECT attr_group_id
FROM ego_attr_groups_v
WHERE attr_group_type = c_attr_group_type
AND application_id = G_APPLICATION_ID
);
SELECT attr_group_name, attr_name
FROM ego_attrs_v
WHERE attr_id = p_attr_id
AND attr_group_type = c_attr_group_type
AND application_id = G_APPLICATION_ID;
l_cursor_select INTEGER;
SELECT 'HZ_PARTY:'||TO_CHAR(PERSON_ID)
INTO G_HZ_PARTY_ID
FROM EGO_PEOPLE_V
WHERE USER_NAME = G_USER_NAME;
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 ';
l_cursor_select := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(l_cursor_select, l_dyn_sql, DBMS_SQL.NATIVE);
DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 1,l_attr_id_table,2500, l_temp);
DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 2,l_intf_col_name_table,2500, l_temp);
DBMS_SQL.DEFINE_ARRAY(l_cursor_select, 3,l_attr_group_id_table,2500, l_temp);
DBMS_SQL.BIND_VARIABLE(l_cursor_select,':RESULTFMT_USAGE_ID', p_resultfmt_usage_id);
DBMS_SQL.BIND_VARIABLE(l_cursor_select,':ATTRIBUTE_CODE', c_attr_grp_rec.attr_group_id||'$$%');
l_cursor_execute := DBMS_SQL.EXECUTE(l_cursor_select);
l_count := DBMS_SQL.FETCH_ROWS(l_cursor_select);
DBMS_SQL.COLUMN_VALUE(l_cursor_select, 1, l_attr_id_table);
DBMS_SQL.COLUMN_VALUE(l_cursor_select, 2, l_intf_col_name_table);
DBMS_SQL.COLUMN_VALUE(l_cursor_select, 3, l_attr_group_id_table);
DBMS_SQL.CLOSE_CURSOR(l_cursor_select);
l_dyn_attr_id_val_sql := ' SELECT ';
l_usr_attr_data_tbl.DELETE(l_rows_per_attr_grp_indx);
l_attr_id_table.DELETE;
l_intf_col_name_table.DELETE;
Write_Conclog('Inserted Attribute value too large....');
INSERT INTO POS_SUPP_PROF_EXT_INTF
(
DATA_SET_ID ,
TRANSACTION_ID ,
PARTY_ID ,
--PK1_VALUE ,
ROW_IDENTIFIER ,
PROCESS_STATUS ,
CLASSIFICATION_CODE ,
ATTR_GROUP_INT_NAME ,
ATTR_INT_NAME ,
ATTR_DISP_VALUE ,
DATA_LEVEL_ID ,
DATA_LEVEL_NAME,
BATCH_ID
)
VALUES
(
l_usr_attr_data_tbl(i).DATA_SET_ID,
l_usr_attr_data_tbl(i).TRANSACTION_ID,
to_number(l_usr_attr_data_tbl(i).PK1_ID),
--l_usr_attr_data_tbl(i).PK2_ID,
l_usr_attr_data_tbl(i).ROW_IDENTIFIER,
G_PS_TO_BE_PROCESSED,
l_usr_attr_data_tbl(i).CLASSIFICATION_CODE,
l_usr_attr_data_tbl(i).ATTR_GROUP_NAME,
l_usr_attr_data_tbl(i).ATTR_NAME,
l_varchar_data,
G_POS_SUPP_LEVEL_ID,
G_POS_SUPP_LEVEL,
l_usr_attr_data_tbl(i).BATCH_ID
);
Write_Conclog('Before Inserting into Ext intf for Party Site level');
INSERT INTO POS_SUPP_PROF_EXT_INTF
(
DATA_SET_ID ,
TRANSACTION_ID ,
PARTY_ID ,
PK1_VALUE , --Party_Site_ID
--PK2_VALUE ,
ROW_IDENTIFIER ,
PROCESS_STATUS ,
CLASSIFICATION_CODE ,
ATTR_GROUP_INT_NAME ,
ATTR_INT_NAME ,
ATTR_DISP_VALUE ,
DATA_LEVEL_ID ,
DATA_LEVEL_NAME,
BATCH_ID
)
VALUES
(
l_usr_attr_data_tbl(i).DATA_SET_ID,
l_usr_attr_data_tbl(i).TRANSACTION_ID,
to_number(l_usr_attr_data_tbl(i).PK1_ID),
--l_PK1_Value, -- Party_Site_ID
l_usr_attr_data_tbl(i).PK3_ID, -- PK3 is Party_Site_ID
l_usr_attr_data_tbl(i).ROW_IDENTIFIER,
G_PS_TO_BE_PROCESSED,
l_usr_attr_data_tbl(i).CLASSIFICATION_CODE,
l_usr_attr_data_tbl(i).ATTR_GROUP_NAME,
l_usr_attr_data_tbl(i).ATTR_NAME,
l_varchar_data,
G_POS_SUPP_ADDR_LEVEL_ID,
G_POS_SUPP_ADDR_LEVEL,
l_usr_attr_data_tbl(i).BATCH_ID
);
INSERT INTO POS_SUPP_PROF_EXT_INTF
(
DATA_SET_ID ,
TRANSACTION_ID ,
PARTY_ID ,
PK1_VALUE , --Party_Site_ID
PK2_VALUE , --Vendor_Site_ID
--PK3_VALUE ,
ROW_IDENTIFIER ,
PROCESS_STATUS ,
CLASSIFICATION_CODE ,
ATTR_GROUP_INT_NAME ,
ATTR_INT_NAME ,
ATTR_DISP_VALUE ,
DATA_LEVEL_ID ,
DATA_LEVEL_NAME,
BATCH_ID
)
VALUES
(
l_usr_attr_data_tbl(i).DATA_SET_ID,
l_usr_attr_data_tbl(i).TRANSACTION_ID,
to_number(l_usr_attr_data_tbl(i).PK1_ID),
--l_PK1_Value, -- Party_Site_ID
--l_PK2_Value, -- _Site_ID
--l_usr_attr_data_tbl(i).PK2_ID,
l_usr_attr_data_tbl(i).PK3_ID, -- Party_Site_ID
l_usr_attr_data_tbl(i).PK4_ID, -- Vendor_Site_ID
l_usr_attr_data_tbl(i).ROW_IDENTIFIER,
G_PS_TO_BE_PROCESSED,
l_usr_attr_data_tbl(i).CLASSIFICATION_CODE,
l_usr_attr_data_tbl(i).ATTR_GROUP_NAME,
l_usr_attr_data_tbl(i).ATTR_NAME,
l_varchar_data,
G_POS_SUPP_ADDR_SITE_LEVEL_ID,
G_POS_SUPP_ADDR_SITE_LEVEL,
l_usr_attr_data_tbl(i).BATCH_ID
);
select count(*) from POS_SUPP_PROF_EXT_INTF
WHERE PROCESS_STATUS = G_PS_TO_BE_PROCESSED
AND DATA_SET_ID = p_data_set_id;
UPDATE POS_SUPP_PROF_EXT_INTF
SET PROCESS_STATUS = G_PS_IN_PROCESS
,REQUEST_ID = G_REQUEST_ID
,PROGRAM_APPLICATION_ID = G_PROGAM_APPLICATION_ID
,PROGRAM_ID = G_PROGAM_ID
,PROGRAM_UPDATE_DATE = SYSDATE
,CREATED_BY = DECODE(CREATED_BY, NULL, G_USER_ID, CREATED_BY)
,CREATION_DATE = DECODE(CREATION_DATE, NULL, SYSDATE, CREATION_DATE)
,LAST_UPDATED_BY = G_USER_ID
,LAST_UPDATE_DATE = SYSDATE
,LAST_UPDATE_LOGIN = G_LOGIN_ID
,TRANSACTION_TYPE = UPPER(NVL(TRANSACTION_TYPE,EGO_USER_ATTRS_DATA_PVT.G_SYNC_MODE))
,IS_PROSPECT = 'N'
-- Bug16384042 We cannot set PK1_value to null irrespective of what DataLevel AG is using
--,PK1_VALUE = null
,CLASSIFICATION_CODE = 'BS:BASE'
WHERE DATA_SET_ID = p_data_set_id
AND (PROCESS_STATUS IS NULL OR
PROCESS_STATUS = G_PS_TO_BE_PROCESSED);
l_rel_sql := 'SELECT CODE FROM POS_SUPP_PROF_EXT_OCV';
DELETE FROM POS_SUPP_PROF_EXT_INTF
WHERE DATA_SET_ID = p_data_set_id
AND PROCESS_STATUS = G_PS_IN_PROCESS;
UPDATE POS_SUPP_PROF_EXT_INTF
SET PROCESS_STATUS = G_PS_SUCCESS
WHERE DATA_SET_ID = p_data_set_id
AND PROCESS_STATUS = G_PS_IN_PROCESS;
UPDATE POS_SUPP_PROF_EXT_INTF
SET PROCESS_STATUS = G_PS_GENERIC_ERROR
WHERE DATA_SET_ID = p_data_set_id
AND PROCESS_STATUS = G_PS_IN_PROCESS;
UPDATE EGO_BULKLOAD_INTF
SET
PROCESS_STATUS = G_PS_IN_PROCESS,
LAST_UPDATE_LOGIN = FND_GLOBAL.login_id,
REQUEST_ID = FND_GLOBAL.conc_request_id,
PROGRAM_APPLICATION_ID = FND_GLOBAL.prog_appl_id,
PROGRAM_ID = FND_GLOBAL.conc_program_id
WHERE RESULTFMT_USAGE_ID = result_format_usage_id
AND process_status = G_PS_TO_BE_PROCESSED;
UPDATE EGO_BULKLOAD_INTF
SET PROCESS_STATUS = G_PS_SUCCESS,
LAST_UPDATE_LOGIN = FND_GLOBAL.login_id,
REQUEST_ID = FND_GLOBAL.conc_request_id,
PROGRAM_APPLICATION_ID = FND_GLOBAL.prog_appl_id,
PROGRAM_ID = FND_GLOBAL.conc_program_id
WHERE RESULTFMT_USAGE_ID = result_format_usage_id
AND process_status = G_PS_IN_PROCESS;
P_INSERT_UPDATE_FLAG IN VARCHAR2,
P_PARTY_TYPE IN VARCHAR2,
P_ORGANIZATION_NAME IN VARCHAR2,
P_PERSON_FIRST_NAME IN VARCHAR2,
P_PERSON_LAST_NAME IN VARCHAR2
)
IS
l_debug VARCHAR2(80);
DELETE FROM HZ_IMP_PARTIES_INT
WHERE batch_id = p_batch_id;
DELETE FROM Ap_Suppliers_Int
WHERE sdh_BATCH_ID = P_BATCH_ID;
INSERT
INTO HZ_IMP_PARTIES_INT
(
BATCH_ID,
PARTY_ORIG_SYSTEM,
PARTY_ORIG_SYSTEM_REFERENCE,
INSERT_UPDATE_FLAG,
PARTY_TYPE,
ORGANIZATION_NAME,
PERSON_FIRST_NAME,
PERSON_LAST_NAME,
creation_date
)
VALUES
(p_batch_id,
P_PARTY_ORIG_SYSTEM,
P_PARTY_ORIG_SYSTEM_REFERENCE,
P_INSERT_UPDATE_FLAG,
P_PARTY_TYPE,
P_ORGANIZATION_NAME,
P_PERSON_FIRST_NAME,
P_PERSON_LAST_NAME,
sysdate
);
INSERT INTO AP_SUPPLIERS_INT
(VENDOR_INTERFACE_ID,
VENDOR_NAME,
SDH_BATCH_ID ,
PARTY_ORIG_SYSTEM ,
PARTY_ORIG_SYSTEM_REFERENCE
)
VALUES
(P_BATCH_ID,
P_ORGANIZATION_NAME,
P_BATCH_ID,
P_PARTY_ORIG_SYSTEM,
P_PARTY_ORIG_SYSTEM_REFERENCE
);
DELETE FROM HZ_IMP_PARTIES_INT
WHERE batch_id = p_batch_id;
DELETE FROM Ap_Suppliers_Int
WHERE SDH_BATCH_ID = P_BATCH_ID;
Write_Conclog('INSERT PARTY CHANGES ');