[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT PAR_VALUE INTO l_idx_mem
FROM CTX_PARAMETERS
WHERE PAR_NAME = 'MAX_INDEX_MEMORY';
SELECT PAR_VALUE INTO l_idx_mem
FROM CTX_PARAMETERS
WHERE PAR_NAME = 'DEFAULT_INDEX_MEMORY';
select tablespace, index_tablespace
into ctx_tbsp, ctx_index_tbsp
from fnd_product_installations
where application_id = '222';
PROCEDURE insert_into_thin_tables( p_entity IN VARCHAR2) IS
BEGIN
log ('-------------------------------------');
log ('Calling insert_into_thin_tables for ' || p_entity);
insert /*+ append */ into hz_thin_st_parties
(party_id, status, partition_id, parent_rowid, concat_col)
select party_id, status, decode(TX36, 'ORGANIZATION ',0,'PERSON ',1,1),ROWID,null
from hz_shadow_st_parties;
insert /*+ append */ into hz_thin_st_psites
(party_id, party_site_id, person_party_id, qkey, org_contact_id,
parent_rowid, concat_col,status_flag) -- Bug No: 4299785
select decode(party_id,person_party_id,NULL,party_id), party_site_id,person_party_id, qkey,org_contact_id,ROWID,null,status_flag -- Fix for bug 5155761
--select party_id, party_site_id,person_party_id, qkey,org_contact_id,ROWID,null,status_flag -- Bug No: 4299785
from hz_shadow_st_psites;
insert /*+ append */ into hz_thin_st_contacts
(party_id, person_party_id, qkey, org_contact_id, parent_rowid, concat_col,status_flag) -- Fix for bug 5155761
select decode(party_id,person_party_id,NULL,party_id),person_party_id, qkey,org_contact_id,ROWID,null,status_flag -- Fix for bug 5155761
--(party_id, qkey, org_contact_id, parent_rowid, concat_col,status_flag)-- Bug No: 4299785
--select party_id, qkey,org_contact_id,ROWID,null,status_flag -- Bug No: 4299785
from hz_shadow_st_contacts;
insert /*+ append */ into hz_thin_st_cpts
(party_id, partition_id, contact_point_id, party_site_id,
person_party_id, parent_rowid, org_contact_id, qkey, concat_col,status_flag) -- Bug No: 4299785
select decode(party_id,person_party_id,NULL,party_id), decode(contact_point_type,'PHONE',0,'EMAIL',1,2),contact_point_id,party_site_id, person_party_id,ROWID,org_contact_id, qkey, null,status_flag -- Fix for bug 5155761
--select party_id, decode(contact_point_type,'PHONE',0,'EMAIL',1,2),contact_point_id,party_site_id,person_party_id,ROWID,org_contact_id, qkey, null,status_flag -- Bug No: 4299785
from hz_shadow_st_cpts;
/* select owner into l_owner from sys.all_objects
where object_name = 'HZ_STAGED_PARTIES' and OBJECT_TYPE = 'TABLE' and owner = l_owner1;*/
l_sql := ' select owner from sys.all_tables where table_name = ''HZ_STAGED_PARTIES'' and owner = :1';
l_sql := ' select owner from sys.all_tables where table_name = ''HZ_THIN_ST_PARTIES'' and owner = :1';
/* select owner into l_owner from sys.all_objects
where object_name = 'HZ_THIN_ST_PARTIES' and OBJECT_TYPE = 'TABLE' and
owner = l_owner1;*/
SELECT staged_attribute_column
FROM HZ_TRANS_ATTRIBUTES_VL a, HZ_TRANS_FUNCTIONS_VL f
WHERE ENTITY_NAME = cp_entity
AND f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
AND nvl(f.ACTIVE_FLAG,'Y') = 'Y'
--AND nvl(f.PRIMARY_FLAG,'Y') = 'Y' --5044716
;
l(' SELECT ');
SELECT COUNT(1)
FROM HZ_TRANS_ATTRIBUTES_VL a, HZ_TRANS_FUNCTIONS_VL f
WHERE ENTITY_NAME = cp_entity
AND f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
AND nvl(f.ACTIVE_FLAG,'Y') = 'Y'
--AND nvl(f.PRIMARY_FLAG,'Y') = 'Y' --5044716
;
SELECT staged_attribute_column
FROM HZ_TRANS_ATTRIBUTES_VL a, HZ_TRANS_FUNCTIONS_VL f
WHERE ENTITY_NAME = cp_entity
AND f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
AND nvl(f.ACTIVE_FLAG,'Y') = 'Y'
--AND nvl(f.PRIMARY_FLAG,'Y') = 'Y' --5044716
;
UPDATE HZ_TRANS_ATTRIBUTES_B set TEMP_SECTION=NULL
WHERE ENTITY_NAME='PARTY';
UPDATE HZ_TRANS_ATTRIBUTES_B set TEMP_SECTION=NULL
WHERE ENTITY_NAME='PARTY_SITES';
UPDATE HZ_TRANS_ATTRIBUTES_B set TEMP_SECTION=NULL
WHERE ENTITY_NAME='CONTACTS';
UPDATE HZ_TRANS_ATTRIBUTES_B set TEMP_SECTION=NULL
WHERE ENTITY_NAME='CONTACT_POINTS';
log('Update of temp_section in HZ_TRANS_ATTRIBUTES_B successful for ' || p_entity);
SELECT OWNER||'.'||INDEX_NAME idx_name
FROM sys.all_indexes i, hz_trans_attributes_vl a, hz_trans_functions_vl f
WHERE f.attribute_id = a.attribute_id
AND i.owner = l_owner1
AND f.index_required_flag in ('Y','T')
AND i.INDEX_NAME = decode(a.entity_name,'PARTY','HZ_SHADOW_ST_PARTIES',
'PARTY_SITES','HZ_SHADOW_ST_PSITES','CONTACTS','HZ_SHADOW_ST_CONTACTS',
'CONTACT_POINTS','HZ_SHADOW_ST_CPTS')||'_N0'||f.function_id) LOOP
EXECUTE IMMEDIATE 'DROP INDEX '||IDX.idx_name;
UPDATE hz_trans_functions_b set index_required_flag='N' where index_required_flag='T';
INSERT INTO HZ_DQM_STAGE_LOG (
OPERATION,
NUMBER_OF_WORKERS,
WORKER_NUMBER,
STEP,
START_FLAG,
START_TIME,
END_FLAG,
END_TIME,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY)
VALUES (
substr(p_operation,1,30),
p_num_workers,
p_worker_number,
p_step,
NULL,
NULL,
NULL,
NULL,
hz_utility_pub.created_by,
hz_utility_pub.creation_date,
hz_utility_pub.last_update_login,
hz_utility_pub.last_update_date,
hz_utility_pub.user_id
);
SELECT f.TRANSFORMATION_NAME, f.STAGED_ATTRIBUTE_COLUMN
FROM HZ_TRANS_FUNCTIONS_VL f, HZ_TRANS_ATTRIBUTES_VL a
WHERE PRIMARY_FLAG = 'Y'
AND nvl(f.ACTIVE_FLAG,'Y') = 'Y'
AND f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
AND a.ENTITY_NAME = cp_entity_name;
DELETE FROM HZ_DQM_STAGE_LOG
WHERE operation = 'SHADOW_STAGING'
AND step = 'COMPLETE';
SELECT count(*) into l_is_wildchar from HZ_DQM_STAGE_LOG where operation = 'SHADOW_STAGE_FOR_WILDCHAR_SRCH' and rownum = 1 ;
INSERT INTO HZ_DQM_STAGE_LOG(operation, number_of_workers, worker_number, step,
last_update_date, creation_date, created_by, last_updated_by)
VALUES ('SHADOW_STAGE_FOR_WILDCHAR_SRCH', '-1', '-1', 'Y', SYSDATE, SYSDATE, 0, 0);
SELECT number_of_workers INTO l_last_num_workers
FROM HZ_DQM_STAGE_LOG
WHERE operation = l_command
AND STEP = 'SHADOW_INIT';
DELETE from HZ_DQM_STAGE_LOG where operation = l_command and
step like 'SHADOW%';
DELETE from HZ_DQM_STAGE_LOG where operation = 'SHADOW_POPULATE_THIN';
DELETE from HZ_DQM_STAGE_LOG where operation = 'SHADOW_CREATE_INDEXES';
UPDATE HZ_TRANS_FUNCTIONS_B SET STAGED_FLAG='N';
Select request_id BULK COLLECT into l_sub_requests
from Fnd_Concurrent_Requests R
Where Parent_Request_Id = FND_GLOBAL.conc_request_id
and (phase_code<>'C' or status_code<>'C');
SELECT start_flag, end_flag INTO l_start_flag, l_end_flag
FROM HZ_DQM_STAGE_LOG
WHERE OPERATION = 'SHADOW_POPULATE_THIN'
AND step = 'HZ_PARTIES';
UPDATE HZ_DQM_STAGE_LOG set START_FLAG = 'Y', END_TIME=SYSDATE
WHERE operation = 'SHADOW_POPULATE_THIN' AND step ='HZ_PARTIES';
insert_into_thin_tables('PARTIES');
UPDATE HZ_DQM_STAGE_LOG set END_FLAG = 'Y', END_TIME=SYSDATE
WHERE operation = 'SHADOW_POPULATE_THIN' AND step ='HZ_PARTIES';
SELECT start_flag, end_flag INTO l_start_flag, l_end_flag
FROM HZ_DQM_STAGE_LOG
WHERE OPERATION = 'SHADOW_POPULATE_THIN'
AND step = 'HZ_PARTY_SITES';
UPDATE HZ_DQM_STAGE_LOG set START_FLAG = 'Y', END_TIME=SYSDATE
WHERE operation = 'SHADOW_POPULATE_THIN' AND step ='HZ_PARTY_SITES';
insert_into_thin_tables('PARTY_SITES');
UPDATE HZ_DQM_STAGE_LOG set END_FLAG = 'Y', END_TIME=SYSDATE
WHERE operation = 'SHADOW_POPULATE_THIN' AND step ='HZ_PARTY_SITES';
SELECT start_flag, end_flag INTO l_start_flag, l_end_flag
FROM HZ_DQM_STAGE_LOG
WHERE OPERATION = 'SHADOW_POPULATE_THIN'
AND step = 'HZ_ORG_CONTACTS';
UPDATE HZ_DQM_STAGE_LOG set START_FLAG = 'Y', END_TIME=SYSDATE
WHERE operation = 'SHADOW_POPULATE_THIN' AND step ='HZ_ORG_CONTACTS';
insert_into_thin_tables('CONTACTS') ;
UPDATE HZ_DQM_STAGE_LOG set END_FLAG = 'Y', END_TIME=SYSDATE
WHERE operation = 'SHADOW_POPULATE_THIN' AND step ='HZ_ORG_CONTACTS';
SELECT start_flag, end_flag INTO l_start_flag, l_end_flag
FROM HZ_DQM_STAGE_LOG
WHERE OPERATION = 'SHADOW_POPULATE_THIN'
AND step = 'HZ_CONTACT_POINTS';
UPDATE HZ_DQM_STAGE_LOG set START_FLAG = 'Y', END_TIME=SYSDATE
WHERE operation = 'SHADOW_POPULATE_THIN' AND step ='HZ_CONTACT_POINTS';
insert_into_thin_tables('CONTACT_POINTS') ;
UPDATE HZ_DQM_STAGE_LOG set END_FLAG = 'Y', END_TIME=SYSDATE
WHERE operation = 'SHADOW_POPULATE_THIN' AND step ='HZ_CONTACT_POINTS';
log('inserted into thin tables');
SELECT 1 INTO T FROM HZ_THIN_ST_PARTIES
WHERE ROWNUM=1
AND CONTAINS (concat_col, 'dummy_string')>0;
UPDATE HZ_DQM_STAGE_LOG
SET START_FLAG ='Y', START_TIME=SYSDATE,END_FLAG = 'Y', END_TIME=SYSDATE
WHERE OPERATION = 'SHADOW_CREATE_INDEXES' AND step = 'HZ_PARTIES';
SELECT 1 INTO T FROM HZ_THIN_ST_PSITES
WHERE ROWNUM=1
AND CONTAINS (concat_col, 'dummy_string')>0;
UPDATE HZ_DQM_STAGE_LOG
SET START_FLAG ='Y', START_TIME=SYSDATE,END_FLAG = 'Y', END_TIME=SYSDATE
WHERE OPERATION = 'SHADOW_CREATE_INDEXES' AND step = 'HZ_PARTY_SITES';
SELECT 1 INTO T FROM HZ_THIN_ST_CONTACTS
WHERE ROWNUM=1
AND CONTAINS (concat_col, 'dummy_string')>0;
UPDATE HZ_DQM_STAGE_LOG
SET START_FLAG ='Y', START_TIME=SYSDATE,END_FLAG = 'Y', END_TIME=SYSDATE
WHERE OPERATION = 'SHADOW_CREATE_INDEXES' AND step = 'HZ_ORG_CONTACTS';
SELECT 1 INTO T FROM HZ_THIN_ST_CPTS
WHERE ROWNUM=1
AND CONTAINS (concat_col, 'dummy_string')>0;
UPDATE HZ_DQM_STAGE_LOG
SET START_FLAG ='Y', START_TIME=SYSDATE,END_FLAG = 'Y', END_TIME=SYSDATE
WHERE OPERATION = 'SHADOW_CREATE_INDEXES' AND step = 'HZ_CONTACT_POINTS';
UPDATE HZ_TRANS_FUNCTIONS_B
SET STAGED_FLAG='Y' WHERE nvl(ACTIVE_FLAG,'Y') = 'Y';
Select request_id BULK COLLECT into l_sub_requests
from Fnd_Concurrent_Requests R
Where Parent_Request_Id = FND_GLOBAL.conc_request_id
and (phase_code<>'C' or status_code<>'C');
UPDATE HZ_TRANS_FUNCTIONS_B
SET STAGED_FLAG='Y' WHERE nvl(ACTIVE_FLAG,'Y') = 'Y';
( SELECT start_flag, end_flag
FROM HZ_DQM_STAGE_LOG
WHERE OPERATION = p_command
AND WORKER_NUMBER = l_worker_number AND step = l_step);
SELECT SYSDATE INTO l_startdate FROM DUAL;
UPDATE HZ_DQM_STAGE_LOG set start_flag='Y', start_time = SYSDATE
WHERE OPERATION = p_command
AND WORKER_NUMBER = l_worker_number AND step = l_log_step;
HZ_STAGE_MAP_TRANSFORM_SHADOW.insert_stage_parties('N',l_party_cur);
HZ_STAGE_MAP_TRANSFORM_SHADOW.insert_stage_parties('Y',l_party_cur);
UPDATE HZ_DQM_STAGE_LOG set end_flag='Y', end_time = SYSDATE
WHERE OPERATION = p_command
AND WORKER_NUMBER = l_worker_number AND step = l_log_step;
UPDATE HZ_DQM_STAGE_LOG set start_flag='Y', start_time = SYSDATE
WHERE OPERATION = p_command
AND WORKER_NUMBER = l_worker_number AND step = l_log_step;
HZ_STAGE_MAP_TRANSFORM_SHADOW.insert_stage_parties('N',l_party_cur);
HZ_STAGE_MAP_TRANSFORM_SHADOW.insert_stage_parties('Y',l_party_cur);
UPDATE HZ_DQM_STAGE_LOG set end_flag='Y', end_time = SYSDATE
WHERE OPERATION = p_command
AND WORKER_NUMBER = l_worker_number AND step = l_log_step;
UPDATE HZ_DQM_STAGE_LOG set start_flag='Y', start_time = SYSDATE
WHERE OPERATION = p_command
AND WORKER_NUMBER = l_worker_number AND step = l_log_step;
HZ_STAGE_MAP_TRANSFORM_SHADOW.insert_stage_parties('N',l_party_cur);
HZ_STAGE_MAP_TRANSFORM_SHADOW.insert_stage_parties('Y',l_party_cur);
UPDATE HZ_DQM_STAGE_LOG set end_flag='Y', end_time = SYSDATE
WHERE OPERATION = p_command
AND WORKER_NUMBER = l_worker_number AND step = l_log_step;
DELETE FROM HZ_DQM_SYNC_INTERFACE WHERE mod(PARTY_ID,l_num_workers) = l_worker_number
AND creation_date<=l_startdate;
select 'Y'
from hz_trans_functions_vl t, hz_trans_attributes_b a
where a.attribute_id = t.attribute_id
and ENTITY_NAME = p_entity
and nvl(staged_flag, 'N') = 'N'
and primary_flag = 'Y'
and nvl(active_flag, 'Y') = 'Y'
and rownum = 1;
SELECT count(1) FROM
(SELECT distinct f.staged_attribute_column
FROM HZ_TRANS_FUNCTIONS_VL f, HZ_TRANS_ATTRIBUTES_VL a
WHERE
-- PRIMARY_FLAG = 'Y' --5044716
nvl(f.ACTIVE_FLAG,'Y') = 'Y'
and f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
AND a.entity_name = p_entity
);
SELECT start_flag, end_flag INTO l_start_flag, l_end_flag
FROM HZ_DQM_STAGE_LOG
WHERE OPERATION = 'SHADOW_CREATE_INDEXES'
AND step = 'HZ_PARTIES';
UPDATE HZ_DQM_STAGE_LOG set END_FLAG = 'Y', END_TIME=SYSDATE
WHERE operation = 'SHADOW_CREATE_INDEXES' AND step ='HZ_PARTIES';
UPDATE HZ_DQM_STAGE_LOG set START_FLAG = 'Y', START_TIME=SYSDATE
WHERE operation = 'SHADOW_CREATE_INDEXES' AND step ='HZ_PARTIES';
UPDATE HZ_DQM_STAGE_LOG set END_FLAG = 'Y', END_TIME=SYSDATE
WHERE operation = 'SHADOW_CREATE_INDEXES' AND step ='HZ_PARTIES';
SELECT start_flag, end_flag INTO l_start_flag, l_end_flag
FROM HZ_DQM_STAGE_LOG
WHERE OPERATION = 'SHADOW_CREATE_INDEXES'
AND step = 'HZ_PARTY_SITES';
UPDATE HZ_DQM_STAGE_LOG set END_FLAG = 'Y', END_TIME=SYSDATE
WHERE operation = 'SHADOW_CREATE_INDEXES' AND step ='HZ_PARTY_SITES';
UPDATE HZ_DQM_STAGE_LOG set START_FLAG = 'Y', START_TIME=SYSDATE
WHERE operation = 'SHADOW_CREATE_INDEXES' AND step ='HZ_PARTY_SITES';
UPDATE HZ_DQM_STAGE_LOG set END_FLAG = 'Y', END_TIME=SYSDATE
WHERE operation = 'SHADOW_CREATE_INDEXES' AND step ='HZ_PARTY_SITES';
SELECT start_flag, end_flag INTO l_start_flag, l_end_flag
FROM HZ_DQM_STAGE_LOG
WHERE OPERATION = 'SHADOW_CREATE_INDEXES'
AND step = 'HZ_ORG_CONTACTS';
UPDATE HZ_DQM_STAGE_LOG set END_FLAG = 'Y', END_TIME=SYSDATE
WHERE operation = 'SHADOW_CREATE_INDEXES' AND step ='HZ_ORG_CONTACTS';
UPDATE HZ_DQM_STAGE_LOG set START_FLAG = 'Y', START_TIME=SYSDATE
WHERE operation = 'SHADOW_CREATE_INDEXES' AND step ='HZ_ORG_CONTACTS';
UPDATE HZ_DQM_STAGE_LOG set END_FLAG = 'Y', END_TIME=SYSDATE
WHERE operation = 'SHADOW_CREATE_INDEXES' AND step ='HZ_ORG_CONTACTS';
SELECT start_flag, end_flag INTO l_start_flag, l_end_flag
FROM HZ_DQM_STAGE_LOG
WHERE OPERATION = 'SHADOW_CREATE_INDEXES'
AND step = 'HZ_CONTACT_POINTS';
UPDATE HZ_DQM_STAGE_LOG set END_FLAG = 'Y', END_TIME=SYSDATE
WHERE operation = 'SHADOW_CREATE_INDEXES' AND step ='HZ_CONTACT_POINTS';
UPDATE HZ_DQM_STAGE_LOG set START_FLAG = 'Y', START_TIME=SYSDATE
WHERE operation = 'SHADOW_CREATE_INDEXES' AND step ='HZ_CONTACT_POINTS';
UPDATE HZ_DQM_STAGE_LOG set END_FLAG = 'Y', END_TIME=SYSDATE
WHERE operation = 'SHADOW_CREATE_INDEXES' AND step ='HZ_CONTACT_POINTS';
l_update_str VARCHAR2(4000);
l_update_str := null;
for ATTRS IN (SELECT ATTRIBUTE_ID, ATTRIBUTE_NAME
FROM HZ_TRANS_ATTRIBUTES_VL
WHERE ENTITY_NAME = p_entity)
LOOP
for FUNCS IN (SELECT PROCEDURE_NAME, STAGED_ATTRIBUTE_COLUMN
FROM HZ_TRANS_FUNCTIONS_VL
WHERE ATTRIBUTE_ID = ATTRS.ATTRIBUTE_ID
AND nvl(ACTIVE_FLAG,'Y') = 'Y'
AND NVL(STAGED_FLAG,'N') <> 'Y')
LOOP
l(' l_stage_rec.'||FUNCS.STAGED_ATTRIBUTE_COLUMN || ' := ');
l_update_str := ' '|| FUNCS.STAGED_ATTRIBUTE_COLUMN || ' = ' ||
' l_stage_rec.'||FUNCS.STAGED_ATTRIBUTE_COLUMN || ' ';
l_update_str := l_update_str || ','|| FUNCS.STAGED_ATTRIBUTE_COLUMN || ' = ' ||
' l_stage_rec.'||FUNCS.STAGED_ATTRIBUTE_COLUMN || ' ';
IF l_update_str IS NOT NULL THEN
IF p_entity = 'PARTY' THEN
l(' UPDATE HZ_SHADOW_ST_PARTIES SET ');
l(' ' || l_update_str);
l(' UPDATE HZ_SHADOW_ST_PSITES SET ');
l(' ' || l_update_str);
l(' UPDATE HZ_SHADOW_ST_CONTACTS SET ');
l(' ' || l_update_str);
l(' UPDATE HZ_SHADOW_ST_CPTS SET ');
l(' ' || l_update_str);
for ATTRS IN (SELECT ATTRIBUTE_ID, ATTRIBUTE_NAME
FROM HZ_TRANS_ATTRIBUTES_VL
WHERE ENTITY_NAME = p_entity)
LOOP
for FUNCS IN (SELECT PROCEDURE_NAME, STAGED_ATTRIBUTE_COLUMN
FROM HZ_TRANS_FUNCTIONS_VL
WHERE ATTRIBUTE_ID = ATTRS.ATTRIBUTE_ID
AND nvl(ACTIVE_FLAG,'Y') = 'Y')
LOOP
l(' l_stage_rec.'||FUNCS.STAGED_ATTRIBUTE_COLUMN || ' := ');
l_org_select coltab;
l_per_select coltab;
l_oth_select coltab;
l(' p_select_type IN VARCHAR2,');
FOR ATTRS IN (SELECT a.ATTRIBUTE_ID,
a.ATTRIBUTE_NAME,
a.SOURCE_TABLE,
a.CUSTOM_ATTRIBUTE_PROCEDURE,
f.PROCEDURE_NAME,
f.STAGED_ATTRIBUTE_COLUMN,
to_number(substrb(STAGED_ATTRIBUTE_COLUMN, 3)) COLNUM,
nvl(TAG,'C') column_data_type --Bug No: 4954701
FROM HZ_TRANS_ATTRIBUTES_VL a, HZ_TRANS_FUNCTIONS_VL f,FND_LOOKUP_VALUES_VL lkp --Bug No: 4954701
WHERE ENTITY_NAME = 'PARTY'
AND nvl(f.ACTIVE_FLAG,'Y') = 'Y'
AND f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
AND lkp.LOOKUP_TYPE = 'PARTY_LOGICAL_ATTRIBUTE_LIST' --Bug No: 4954701
AND lkp.LOOKUP_CODE = a.ATTRIBUTE_NAME --Bug No: 4954701
ORDER BY COLNUM) LOOP
IF cur_col_num
SELECT min(to_number(substrb(STAGED_ATTRIBUTE_COLUMN, 3))) INTO l_min_colnum
FROM HZ_TRANS_ATTRIBUTES_VL a, HZ_TRANS_FUNCTIONS_VL f
WHERE ENTITY_NAME = 'PARTY'
AND nvl(f.ACTIVE_FLAG,'Y') = 'Y'
AND f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
AND a.ATTRIBUTE_NAME=ATTRS.ATTRIBUTE_NAME;
l_org_select(idx) := l_org_attr_name;
l_per_select(idx) := 'NULL';
l_oth_select(idx) := 'NULL';
l_per_select(idx) := l_per_attr_name;
l_org_select(idx) := 'NULL';
l_oth_select(idx) := 'NULL';
l_org_select(idx) := l_org_attr_name;
l_per_select(idx) := l_per_attr_name;
l_oth_select(idx) := 'NULL';
l_org_select(idx) := l_attr_name;
l_per_select(idx) := l_attr_name;
l_oth_select(idx) := l_attr_name;
l_org_select(idx):='N';
l_per_select(idx):='N';
l_oth_select(idx):='N';
l_org_select(idx):='N';
l_per_select(idx):='N';
l_oth_select(idx):='N';
l_org_select(idx) := 'p.PARTY_NAME || '' '' || p.KNOWN_AS || '' '' || p.KNOWN_AS2 || '' '' || p.KNOWN_AS3 || '' ''|| p.KNOWN_AS4 || '' ''|| p.KNOWN_AS5';
l_per_select(idx) := 'p.PARTY_NAME || '' '' || p.KNOWN_AS || '' '' || p.KNOWN_AS2 || '' '' || p.KNOWN_AS3 || '' ''|| p.KNOWN_AS4 || '' ''|| p.KNOWN_AS5';
l_oth_select(idx) := 'p.PARTY_NAME || '' '' || p.KNOWN_AS || '' '' || p.KNOWN_AS2 || '' '' || p.KNOWN_AS3 || '' ''|| p.KNOWN_AS4 || '' ''|| p.KNOWN_AS5';
l(' IF p_select_type = ''SINGLE_PARTY'' THEN');
l(' ELSIF p_select_type = ''ALL_PARTIES'' THEN');
l(' SELECT p.PARTY_ID, p.STATUS, NULL AS PERSON_PARTY_ID '); -- Fix for bug 5155761
FOR I in 1..l_org_select.COUNT LOOP
IF l_org_select(I) <> 'N' THEN
l(' ,' || l_org_select(I));
l(' SELECT p.PARTY_ID, p.STATUS, p.PARTY_ID AS PERSON_PARTY_ID '); -- Fix for bug 5155761
FOR I in 1..l_per_select.COUNT LOOP
IF l_per_select(I) <> 'N' THEN
l(' ,' || l_per_select(I));
l(' SELECT p.PARTY_ID, p.STATUS, NULL AS PERSON_PARTY_ID '); -- Fix for bug 5155761
FOR I in 1..l_oth_select.COUNT LOOP
IF l_oth_select(I) <> 'N' THEN
l(' ,' || l_oth_select(I));
l(' SELECT p.PARTY_ID, p.STATUS, NULL AS PERSON_PARTY_ID '); -- Fix for bug 5155761
FOR I in 1..l_org_select.COUNT LOOP
IF l_org_select(I) <> 'N' THEN
l(' ,' || l_org_select(I));
l(' AND NOT EXISTS (select 1 FROM HZ_SHADOW_ST_PARTIES sp ');
l(' SELECT p.PARTY_ID, p.STATUS, p.PARTY_ID AS PERSON_PARTY_ID '); -- Fix for bug 5155761
FOR I in 1..l_per_select.COUNT LOOP
IF l_per_select(I) <> 'N' THEN
l(' ,' || l_per_select(I));
l(' AND NOT EXISTS (select 1 FROM HZ_SHADOW_ST_PARTIES sp ');
l(' SELECT p.PARTY_ID, p.STATUS, NULL AS PERSON_PARTY_ID'); -- Fix for bug 5155761
FOR I in 1..l_oth_select.COUNT LOOP
IF l_oth_select(I) <> 'N' THEN
l(' ,' || l_oth_select(I));
l(' AND NOT EXISTS (select 1 FROM HZ_SHADOW_ST_PARTIES sp ');
l(' PROCEDURE insert_stage_parties ( ');
l(' INSERT INTO HZ_SHADOW_ST_PARTIES (');
l(' INSERT INTO HZ_DQM_STAGE_GT ( PARTY_ID, OWNER_ID, PARTY_INDEX, PERSON_PARTY_ID) VALUES ('); -- Fix for bug 5155761
l(' insert_stage_contacts;');
l(' insert_stage_party_sites;');
l(' insert_stage_contact_pts;');
l(' SELECT p.PARTY_ID, p.STATUS ');
FOR I in 1..l_org_select.COUNT LOOP
IF l_org_select(I) <> 'N' THEN
l(' ,' || l_org_select(I));
l(' SELECT p.PARTY_ID, p.STATUS ');
FOR I in 1..l_per_select.COUNT LOOP
IF l_per_select(I) <> 'N' THEN
l(' ,' || l_per_select(I));
l(' SELECT p.PARTY_ID, p.STATUS ');
FOR I in 1..l_oth_select.COUNT LOOP
IF l_per_select(I) <> 'N' THEN
l(' ,' || l_oth_select(I));
l(' INSERT INTO HZ_SHADOW_ST_PARTIES (');
l(' UPDATE HZ_SHADOW_ST_PARTIES SET ');
l_select coltab;
l(' PROCEDURE insert_stage_party_sites IS ');
FOR ATTRS IN (SELECT a.ATTRIBUTE_ID,
a.ATTRIBUTE_NAME,
a.SOURCE_TABLE,
a.CUSTOM_ATTRIBUTE_PROCEDURE,
f.PROCEDURE_NAME,
f.STAGED_ATTRIBUTE_COLUMN,
to_number(substrb(STAGED_ATTRIBUTE_COLUMN, 3)) COLNUM,
nvl(lkp.tag,'C') column_data_type
FROM HZ_TRANS_ATTRIBUTES_VL a, HZ_TRANS_FUNCTIONS_VL f,FND_LOOKUP_VALUES_VL lkp --Bug No: 4954701
WHERE ENTITY_NAME = 'PARTY_SITES'
AND nvl(f.ACTIVE_FLAG,'Y') = 'Y'
AND f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
AND lkp.lookup_type = 'PARTY_SITE_LOGICAL_ATTRIB_LIST'
and lkp.lookup_code = a.ATTRIBUTE_NAME
ORDER BY COLNUM) LOOP
IF cur_col_num
SELECT min(to_number(substrb(STAGED_ATTRIBUTE_COLUMN, 3))) INTO l_min_colnum
FROM HZ_TRANS_ATTRIBUTES_VL a, HZ_TRANS_FUNCTIONS_VL f
WHERE ENTITY_NAME = 'PARTY_SITES'
AND nvl(f.ACTIVE_FLAG,'Y') = 'Y'
AND f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
AND a.ATTRIBUTE_NAME=ATTRS.ATTRIBUTE_NAME;
l_select(idx) := l_loc_attr_name;
l_select(idx) := l_ps_attr_name;
l_select(idx) := 'N';
l_select(idx) := 'N';
l_select(idx) := 'rtrim(l.address1 || '' '' || l.address2 || '' '' || l.address3 || '' '' || l.address4)';
l(' SELECT /*+ cardinality(g 200) use_nl(g ps l) */ ps.PARTY_SITE_ID, g.party_id, g.org_contact_id, g.PARTY_INDEX, g.PERSON_PARTY_ID, ps.status '); -- Bug No: 4299785
FOR I in 1..l_select.COUNT LOOP
IF l_select(I) <> 'N' THEN
l(' ,' || l_select(I));
l(' INSERT INTO HZ_SHADOW_ST_PSITES (');
l(' INSERT INTO HZ_DQM_STAGE_GT (PARTY_ID, OWNER_ID, OWNER_TABLE, PARTY_SITE_ID,');
l(' SELECT ps.PARTY_SITE_ID, d.party_id, d.org_contact_id, ps.status '); -- Bug No: 4299785
FOR I in 1..l_select.COUNT LOOP
IF l_select(I) <> 'N' THEN
l(' ,' || l_select(I));
l(' INSERT INTO HZ_SHADOW_ST_PSITES (');
l(' UPDATE HZ_SHADOW_ST_PSITES SET ');
l(' UPDATE HZ_SHADOW_ST_PARTIES set');
l_select coltab;
l(' PROCEDURE insert_stage_contacts IS ');
FOR ATTRS IN (SELECT a.ATTRIBUTE_ID,
a.ATTRIBUTE_NAME,
a.SOURCE_TABLE,
a.CUSTOM_ATTRIBUTE_PROCEDURE,
f.PROCEDURE_NAME,
f.STAGED_ATTRIBUTE_COLUMN,
to_number(substrb(STAGED_ATTRIBUTE_COLUMN, 3)) COLNUM,
nvl(lkp.tag,'C') column_data_type
FROM HZ_TRANS_ATTRIBUTES_VL a, HZ_TRANS_FUNCTIONS_VL f,FND_LOOKUP_VALUES_VL lkp --Bug No: 4954701
WHERE ENTITY_NAME = 'CONTACTS'
AND nvl(f.ACTIVE_FLAG,'Y') = 'Y'
AND f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
AND lkp.LOOKUP_TYPE='CONTACT_LOGICAL_ATTRIB_LIST'
AND lkp.LOOKUP_CODE = a.ATTRIBUTE_NAME
ORDER BY COLNUM) LOOP
IF cur_col_num
SELECT min(to_number(substrb(STAGED_ATTRIBUTE_COLUMN, 3))) INTO l_min_colnum
FROM HZ_TRANS_ATTRIBUTES_VL a, HZ_TRANS_FUNCTIONS_VL f
WHERE ENTITY_NAME = 'CONTACTS'
AND nvl(f.ACTIVE_FLAG,'Y') = 'Y'
AND f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
AND a.ATTRIBUTE_NAME=ATTRS.ATTRIBUTE_NAME;
l_select(idx) := l_pp_attr_name;
l_select(idx) := l_oc_attr_name;
l_select(idx) := l_attr_name;
l_select(idx) := 'N';
l_select(idx) := 'N';
l_select(idx) := 'rtrim(pp.person_first_name || '' '' || pp.person_last_name)';
l(' SELECT ');
FOR I in 1..l_select.COUNT LOOP
IF l_select(I) <> 'N' THEN
l(' ,' || l_select(I));
l(' INSERT INTO HZ_SHADOW_ST_CONTACTS (');
l(' INSERT INTO HZ_DQM_STAGE_GT(PARTY_ID,OWNER_ID,ORG_CONTACT_ID,PARTY_INDEX, PERSON_PARTY_ID) ');
l(' SELECT H_C_PARTY_ID(I), H_R_PARTY_ID(I), H_ORG_CONTACT_ID(I), H_PARTY_INDEX(I), H_PERSON_PARTY_ID(I)');
l(' SELECT oc.ORG_CONTACT_ID , d.PARTY_ID, r.status '); -- Bug No: 4299785
FOR I in 1..l_select.COUNT LOOP
IF l_select(I) <> 'N' THEN
l(' ,' || l_select(I));
l(' INSERT INTO HZ_SHADOW_ST_CONTACTS (');
l(' UPDATE HZ_SHADOW_ST_CONTACTS SET ');
l(' UPDATE HZ_SHADOW_ST_PARTIES set');
l_select coltab;
l(' PROCEDURE insert_stage_contact_pts IS ');
FOR ATTRS IN (SELECT a.ATTRIBUTE_ID,
a.ATTRIBUTE_NAME,
a.SOURCE_TABLE,
a.CUSTOM_ATTRIBUTE_PROCEDURE,
f.PROCEDURE_NAME,
f.STAGED_ATTRIBUTE_COLUMN,
to_number(substrb(STAGED_ATTRIBUTE_COLUMN, 3)) COLNUM,
nvl(lkp.tag,'C') column_data_type
FROM HZ_TRANS_ATTRIBUTES_VL a, HZ_TRANS_FUNCTIONS_VL f,FND_LOOKUP_VALUES_VL lkp --Bug No: 4954701
WHERE ENTITY_NAME = 'CONTACT_POINTS'
AND nvl(f.ACTIVE_FLAG,'Y') = 'Y'
AND f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
AND lkp.LOOKUP_TYPE = 'CONTACT_PT_LOGICAL_ATTRIB_LIST'
AND lkp.lookup_code = a.ATTRIBUTE_NAME
ORDER BY COLNUM) LOOP
IF cur_col_num
SELECT min(to_number(substrb(STAGED_ATTRIBUTE_COLUMN, 3))) INTO l_min_colnum
FROM HZ_TRANS_ATTRIBUTES_VL a, HZ_TRANS_FUNCTIONS_VL f
WHERE ENTITY_NAME = 'CONTACT_POINTS'
AND nvl(f.ACTIVE_FLAG,'Y') = 'Y'
AND f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
AND a.ATTRIBUTE_NAME=ATTRS.ATTRIBUTE_NAME;
l_select(idx) := l_attr_name;
l_select(idx) := 'N';
l_select(idx) := 'N';
l_select(idx) := 'translate(phone_number,''0123456789ABCDEFGHIJKLMNOPQRSTUV'||
'WXYZabcdefghijklmnopqrstuvwxyz!"#$%&()''''*+,-./:;<=>?@[\]^'||
l_select(idx) := 'translate(phone_country_code|| '' '' || phone_area_code||'' '' || phone_number,''0123456789ABCDEFGHIJKLMNOPQRSTUV'||
'WXYZabcdefghijklmnopqrstuvwxyz!"#$%&()''''*+,-./:;<=>?@[\]^_'||
l(' SELECT /*+ cardinality(g 200) use_nl(g cp) */ cp.CONTACT_POINT_ID, g.party_id, g.party_site_id, g.org_contact_id, cp.CONTACT_POINT_TYPE, PARTY_INDEX, g.PERSON_PARTY_ID, cp.status'); -- Bug No: 4299785
FOR I in 1..l_select.COUNT LOOP
IF l_select(I) <> 'N' THEN
l(' ,' || l_select(I));
l(' INSERT INTO HZ_SHADOW_ST_CPTS (');
l(' SELECT cp.CONTACT_POINT_ID, d.PARTY_ID, d.PARTY_SITE_ID, d.ORG_CONTACT_ID, cp.CONTACT_POINT_TYPE, cp.STATUS '); -- Bug No: 4299785
FOR I in 1..l_select.COUNT LOOP
IF l_select(I) <> 'N' THEN
l(' ,' || l_select(I));
l(' INSERT INTO HZ_SHADOW_ST_CPTS (');
l(' UPDATE HZ_SHADOW_ST_CPTS SET ');
l(' UPDATE HZ_SHADOW_ST_PARTIES set');
SELECT decode(a.entity_name,'PARTY','HZ_SHADOW_ST_PARTIES',
'PARTY_SITES','HZ_SHADOW_ST_PSITES','CONTACTS','HZ_SHADOW_ST_CONTACTS',
'CONTACT_POINTS','HZ_SHADOW_ST_CPTS')||'_N0'||substrb(staged_attribute_column,3) index_name,
decode(a.entity_name,'PARTY','HZ_SHADOW_ST_PARTIES',
'PARTY_SITES','HZ_SHADOW_ST_PSITES','CONTACTS','HZ_SHADOW_ST_CONTACTS',
'CONTACT_POINTS','HZ_SHADOW_ST_CPTS') table_name,
decode(a.entity_name,'PARTY','HZ_SRCH_PARTIES',
'PARTY_SITES','HZ_SRCH_PSITES','CONTACTS','HZ_SRCH_CONTACTS',
'CONTACT_POINTS','HZ_SRCH_CPTS')||'_N0'||substrb(staged_attribute_column,3) srch_index_name,
decode(a.entity_name,'PARTY','HZ_SRCH_PARTIES',
'PARTY_SITES','HZ_SRCH_PSITES','CONTACTS','HZ_SRCH_CONTACTS',
'CONTACT_POINTS','HZ_SRCH_CPTS') srch_table_name,
f.staged_attribute_column column_name
FROM hz_trans_attributes_vl a, hz_trans_functions_vl f
WHERE f.attribute_id = a.attribute_id
AND f.index_required_flag = 'Y'
AND a.entity_name = p_entity;
SELECT 1 FROM sys.all_indexes
WHERE INDEX_NAME=cp_index_name
AND TABLE_NAME=cp_table_name and owner = l_index_owner;
select index_tablespace
into ar_index_tbsp
from fnd_product_installations
where application_id = '222';
l_sql := 'select ' || proc ||
'(:attrval,:lang,:attr,:entity,:ctx) from dual';
l_sql := 'select ' || proc ||
'(:record_id,:entity,:attr,:ctx) from dual';
FOR FUNCS IN (SELECT PROCEDURE_NAME, a.ENTITY_NAME, a.ATTRIBUTE_NAME, f.TRANSFORMATION_NAME
FROM HZ_TRANS_FUNCTIONS_VL f, HZ_TRANS_ATTRIBUTES_VL a
WHERE a.ATTRIBUTE_ID = f.ATTRIBUTE_ID
AND nvl(f.ACTIVE_FLAG,'Y') = 'Y' )
LOOP
BEGIN
l_custom := FALSE;
l_sql := 'select ' || FUNCS.PROCEDURE_NAME ||
'(:attrval,:lang,:attr,:entity) from dual';
FOR FUNCS IN (SELECT custom_attribute_procedure, ENTITY_NAME, ATTRIBUTE_NAME
FROM HZ_TRANS_ATTRIBUTES_VL a
WHERE source_table = 'CUSTOM' OR
custom_attribute_procedure is NOT NULL
AND EXISTS (select 1 from HZ_TRANS_FUNCTIONS_VL f
WHERE f.attribute_id = a.attribute_id
AND nvl(f.ACTIVE_FLAG,'Y') = 'Y'))
LOOP
BEGIN
l_custom := TRUE;
l_sql := 'select ' || FUNCS.custom_attribute_procedure ||
'(:record_id,:entity,:attr) from dual';