[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT distinct
TX9||' '||
TX10||' '||
TX11||' '||
TX12||' '||
TX13||' '||
TX14||' '||
TX15||' '||
TX20||' '||
TX21||' '||
TX22||' '||
' '
FROM APPS.HZ_STAGED_PARTY_SITES
WHERE party_id = cp_party_id;
SELECT distinct
TX22||' '||
' '
FROM APPS.HZ_STAGED_CONTACTS
WHERE party_id = cp_party_id;
SELECT distinct
TX3||' '||
TX4||' '||
' '
FROM APPS.HZ_STAGED_CONTACT_POINTS
WHERE party_id = cp_party_id;
SELECT ACCOUNT_NUMBER
FROM APPS.hz_cust_accounts
WHERE PARTY_ID = p_party_id
ORDER BY STATUS,CREATION_DATE;
PROCEDURE insert_dqm_sync_error_rec (
p_party_id IN NUMBER,
p_record_id IN NUMBER,
p_party_site_id IN NUMBER,
p_org_contact_id IN NUMBER,
p_entity IN VARCHAR2,
p_operation IN VARCHAR2,
p_staged_flag IN VARCHAR2 DEFAULT 'E',
p_realtime_sync_flag IN VARCHAR2 DEFAULT 'Y',
p_error_data IN VARCHAR2
) IS
BEGIN
INSERT INTO hz_dqm_sync_interface (
PARTY_ID,
RECORD_ID,
PARTY_SITE_ID,
ORG_CONTACT_ID,
ENTITY,
OPERATION,
STAGED_FLAG,
REALTIME_SYNC_FLAG,
ERROR_DATA,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
SYNC_INTERFACE_NUM
) VALUES (
p_party_id,
p_record_id,
p_party_site_id,
p_org_contact_id,
p_entity,
p_operation,
p_staged_flag,
p_realtime_sync_flag,
p_error_data,
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,
HZ_DQM_SYNC_INTERFACE_S.nextval
);
END insert_dqm_sync_error_rec;
p_select_type IN VARCHAR2,
p_party_type IN VARCHAR2,
p_worker_number IN NUMBER,
p_num_workers IN NUMBER,
p_party_id IN NUMBER,
p_continue IN VARCHAR2,
p_start_rowid IN ROWID,
p_end_rowid IN ROWID,
x_party_cur IN OUT HZ_PARTY_STAGE.StageCurTyp) IS
l_party_type VARCHAR2(255);
IF p_select_type = 'SINGLE_PARTY' THEN
NULL;
ELSIF p_select_type = 'ALL_PARTIES' THEN
IF p_continue IS NULL OR p_continue<>'Y' THEN
IF p_party_type = 'ORGANIZATION' THEN
open x_party_cur FOR
SELECT p.PARTY_ID, p.STATUS
,p.PARTY_NAME
,p.PARTY_NUMBER
,p.PARTY_TYPE
,p.PARTY_NAME || ' ' || p.KNOWN_AS || ' ' || p.KNOWN_AS2 || ' ' || p.KNOWN_AS3 || ' '|| p.KNOWN_AS4 || ' '|| p.KNOWN_AS5
,op.DUNS_NUMBER_C
,op.TAX_NAME
,op.TAX_REFERENCE
,op.JGZZ_FISCAL_CODE
,op.SIC_CODE
,op.SIC_CODE_TYPE
,p.CATEGORY_CODE
,p.REFERENCE_USE_FLAG
,op.CORPORATION_CLASS
FROM HZ_PARTIES p, HZ_ORGANIZATION_PROFILES op
WHERE p.rowid BETWEEN p_start_rowid and p_end_rowid
AND p.party_id = op.party_id
AND op.effective_end_date is NULL
AND p.PARTY_TYPE ='ORGANIZATION';
SELECT p.PARTY_ID, p.STATUS
,p.PARTY_NAME
,p.PARTY_NUMBER
,p.PARTY_TYPE
,p.PARTY_NAME || ' ' || p.KNOWN_AS || ' ' || p.KNOWN_AS2 || ' ' || p.KNOWN_AS3 || ' '|| p.KNOWN_AS4 || ' '|| p.KNOWN_AS5
,NULL
,pe.TAX_NAME
,pe.TAX_REFERENCE
,pe.JGZZ_FISCAL_CODE
,NULL
,NULL
,p.CATEGORY_CODE
,p.REFERENCE_USE_FLAG
,NULL
FROM HZ_PARTIES p, HZ_PERSON_PROFILES pe
WHERE p.rowid BETWEEN p_start_rowid and p_end_rowid
AND p.party_id = pe.party_id
AND pe.effective_end_date is NULL
AND p.PARTY_TYPE ='PERSON';
SELECT p.PARTY_ID, p.STATUS
,p.PARTY_NAME
,p.PARTY_NUMBER
,p.PARTY_TYPE
,p.PARTY_NAME || ' ' || p.KNOWN_AS || ' ' || p.KNOWN_AS2 || ' ' || p.KNOWN_AS3 || ' '|| p.KNOWN_AS4 || ' '|| p.KNOWN_AS5
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,p.CATEGORY_CODE
,p.REFERENCE_USE_FLAG
,NULL
FROM HZ_PARTIES p
WHERE p.rowid BETWEEN p_start_rowid and p_end_rowid
AND p.party_type <> 'PERSON'
AND p.party_type <> 'ORGANIZATION'
AND p.party_type <> 'PARTY_RELATIONSHIP';
SELECT p.PARTY_ID, p.STATUS
,p.PARTY_NAME
,p.PARTY_NUMBER
,p.PARTY_TYPE
,p.PARTY_NAME || ' ' || p.KNOWN_AS || ' ' || p.KNOWN_AS2 || ' ' || p.KNOWN_AS3 || ' '|| p.KNOWN_AS4 || ' '|| p.KNOWN_AS5
,op.DUNS_NUMBER_C
,op.TAX_NAME
,op.TAX_REFERENCE
,op.JGZZ_FISCAL_CODE
,op.SIC_CODE
,op.SIC_CODE_TYPE
,p.CATEGORY_CODE
,p.REFERENCE_USE_FLAG
,op.CORPORATION_CLASS
FROM HZ_PARTIES p, HZ_ORGANIZATION_PROFILES op
WHERE p.rowid BETWEEN p_start_rowid and p_end_rowid
AND NOT EXISTS (select 1 FROM HZ_STAGED_PARTIES sp
WHERE sp.party_id = p.party_id)
AND p.party_id = op.party_id
AND op.effective_end_date is NULL
AND p.PARTY_TYPE ='ORGANIZATION';
SELECT p.PARTY_ID, p.STATUS
,p.PARTY_NAME
,p.PARTY_NUMBER
,p.PARTY_TYPE
,p.PARTY_NAME || ' ' || p.KNOWN_AS || ' ' || p.KNOWN_AS2 || ' ' || p.KNOWN_AS3 || ' '|| p.KNOWN_AS4 || ' '|| p.KNOWN_AS5
,NULL
,pe.TAX_NAME
,pe.TAX_REFERENCE
,pe.JGZZ_FISCAL_CODE
,NULL
,NULL
,p.CATEGORY_CODE
,p.REFERENCE_USE_FLAG
,NULL
FROM HZ_PARTIES p, HZ_PERSON_PROFILES pe
WHERE p.rowid BETWEEN p_start_rowid and p_end_rowid
AND NOT EXISTS (select 1 FROM HZ_STAGED_PARTIES sp
WHERE sp.party_id = p.party_id)
AND p.party_id = pe.party_id
AND pe.effective_end_date is NULL
AND p.PARTY_TYPE ='PERSON';
SELECT p.PARTY_ID, p.STATUS
,p.PARTY_NAME
,p.PARTY_NUMBER
,p.PARTY_TYPE
,p.PARTY_NAME || ' ' || p.KNOWN_AS || ' ' || p.KNOWN_AS2 || ' ' || p.KNOWN_AS3 || ' '|| p.KNOWN_AS4 || ' '|| p.KNOWN_AS5
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,p.CATEGORY_CODE
,p.REFERENCE_USE_FLAG
,NULL
FROM HZ_PARTIES p
WHERE p.rowid BETWEEN p_start_rowid and p_end_rowid
AND NOT EXISTS (select 1 FROM HZ_STAGED_PARTIES sp
WHERE sp.party_id = p.party_id)
AND p.party_type <> 'PERSON'
AND p.party_type <> 'ORGANIZATION'
AND p.party_type <> 'PARTY_RELATIONSHIP';
PROCEDURE insert_stage_parties (
p_continue IN VARCHAR2,
p_party_cur IN HZ_PARTY_STAGE.StageCurTyp) IS
l_limit NUMBER := 200;
INSERT INTO HZ_STAGED_PARTIES (
PARTY_ID
,STATUS
, TX2
, TX4
, TX8
, TX19
, TX32
, TX33
, TX34
, TX35
, TX36
, TX39
, TX40
, TX41
, TX42
, TX43
, TX44
, TX45
, TX46
, TX47
, TX48
, TX59
, TX60
, TX61
, TX62
, TX63
, TX156
, TX157
, TX158
) VALUES (
H_P_PARTY_ID(I)
,H_STATUS(I)
, decode(H_TX2(I),null,H_TX2(I),H_TX2(I)||' ')
, decode(H_TX4(I),null,H_TX4(I),H_TX4(I)||' ')
, decode(H_TX8(I),null,H_TX8(I),H_TX8(I)||' ')
, decode(H_TX19(I),null,H_TX19(I),H_TX19(I)||' ')
, decode(H_TX32(I),null,H_TX32(I),H_TX32(I)||' ')
, decode(H_TX33(I),null,H_TX33(I),H_TX33(I)||' ')
, decode(H_TX34(I),null,H_TX34(I),H_TX34(I)||' ')
, decode(H_TX35(I),null,H_TX35(I),H_TX35(I)||' ')
, decode(H_TX36(I),null,H_TX36(I),H_TX36(I)||' ')
, decode(H_TX39(I),null,H_TX39(I),H_TX39(I)||' ')
, decode(H_TX40(I),null,H_TX40(I),H_TX40(I)||' ')
, decode(H_TX41(I),null,H_TX41(I),H_TX41(I)||' ')
, decode(H_TX42(I),null,H_TX42(I),H_TX42(I)||' ')
, decode(H_TX43(I),null,H_TX43(I),H_TX43(I)||' ')
, decode(H_TX44(I),null,H_TX44(I),H_TX44(I)||' ')
, decode(H_TX45(I),null,H_TX45(I),H_TX45(I)||' ')
, decode(H_TX46(I),null,H_TX46(I),H_TX46(I)||' ')
, decode(H_TX47(I),null,H_TX47(I),H_TX47(I)||' ')
, decode(H_TX48(I),null,H_TX48(I),H_TX48(I)||' ')
, decode(H_TX59(I),null,H_TX59(I),H_TX59(I)||' ')
, decode(H_TX60(I),null,H_TX60(I),H_TX60(I)||' ')
, decode(H_TX61(I),null,H_TX61(I),H_TX61(I)||' ')
, decode(H_TX62(I),null,H_TX62(I),H_TX62(I)||' ')
, decode(H_TX63(I),null,H_TX63(I),H_TX63(I)||' ')
, decode(H_TX156(I),null,H_TX156(I),H_TX156(I)||' ')
, decode(H_TX157(I),null,H_TX157(I),H_TX157(I)||' ')
, decode(H_TX158(I),null,H_TX158(I),H_TX158(I)||' ')
);
INSERT INTO HZ_DQM_STAGE_GT ( PARTY_ID, OWNER_ID, PARTY_INDEX) VALUES (
H_P_PARTY_ID(I),H_P_PARTY_ID(I),H_PARTY_INDEX(I));
insert_stage_contacts;
insert_stage_party_sites;
insert_stage_contact_pts;
UPDATE HZ_STAGED_PARTIES SET
D_PS = H_PS_DEN(I),
D_CT = H_CT_DEN(I),
D_CPT = H_CPT_DEN(I)
WHERE PARTY_ID = H_P_PARTY_ID(I);
SELECT p.PARTY_ID, p.STATUS
,p.PARTY_NAME
,p.PARTY_NUMBER
,p.PARTY_TYPE
,p.PARTY_NAME || ' ' || p.KNOWN_AS || ' ' || p.KNOWN_AS2 || ' ' || p.KNOWN_AS3 || ' '|| p.KNOWN_AS4 || ' '|| p.KNOWN_AS5
,op.DUNS_NUMBER_C
,op.TAX_NAME
,op.TAX_REFERENCE
,op.JGZZ_FISCAL_CODE
,op.SIC_CODE
,op.SIC_CODE_TYPE
,p.CATEGORY_CODE
,p.REFERENCE_USE_FLAG
,op.CORPORATION_CLASS
INTO H_P_PARTY_ID(1), H_STATUS(1)
, H_TX2(1)
, H_TX34(1)
, H_TX36(1)
, H_TX39(1)
, H_TX41(1)
, H_TX42(1)
, H_TX44(1)
, H_TX45(1)
, H_TX46(1)
, H_TX47(1)
, H_TX48(1)
, H_TX156(1)
, H_TX157(1)
FROM HZ_PARTIES p, HZ_ORGANIZATION_PROFILES op
WHERE p.party_id = p_party_id
AND p.party_id = op.party_id
AND (p.status = 'M' or op.effective_end_date is NULL) AND ROWNUM=1;
SELECT p.PARTY_ID, p.STATUS
,p.PARTY_NAME
,p.PARTY_NUMBER
,p.PARTY_TYPE
,p.PARTY_NAME || ' ' || p.KNOWN_AS || ' ' || p.KNOWN_AS2 || ' ' || p.KNOWN_AS3 || ' '|| p.KNOWN_AS4 || ' '|| p.KNOWN_AS5
,NULL
,pe.TAX_NAME
,pe.TAX_REFERENCE
,pe.JGZZ_FISCAL_CODE
,NULL
,NULL
,p.CATEGORY_CODE
,p.REFERENCE_USE_FLAG
,NULL
INTO H_P_PARTY_ID(1), H_STATUS(1)
, H_TX2(1)
, H_TX34(1)
, H_TX36(1)
, H_TX39(1)
, H_TX41(1)
, H_TX42(1)
, H_TX44(1)
, H_TX45(1)
, H_TX46(1)
, H_TX47(1)
, H_TX48(1)
, H_TX156(1)
, H_TX157(1)
FROM HZ_PARTIES p, HZ_PERSON_PROFILES pe
WHERE p.party_id = p_party_id
AND p.party_id = pe.party_id
AND (p.status = 'M' or pe.effective_end_date is NULL) AND ROWNUM=1;
SELECT p.PARTY_ID, p.STATUS
,p.PARTY_NAME
,p.PARTY_NUMBER
,p.PARTY_TYPE
,p.PARTY_NAME || ' ' || p.KNOWN_AS || ' ' || p.KNOWN_AS2 || ' ' || p.KNOWN_AS3 || ' '|| p.KNOWN_AS4 || ' '|| p.KNOWN_AS5
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,p.CATEGORY_CODE
,p.REFERENCE_USE_FLAG
,NULL
INTO H_P_PARTY_ID(1), H_STATUS(1)
, H_TX2(1)
, H_TX34(1)
, H_TX36(1)
, H_TX39(1)
, H_TX41(1)
, H_TX42(1)
, H_TX44(1)
, H_TX45(1)
, H_TX46(1)
, H_TX47(1)
, H_TX48(1)
, H_TX156(1)
, H_TX157(1)
FROM HZ_PARTIES p
WHERE p.party_id = p_party_id;
INSERT INTO HZ_STAGED_PARTIES (
PARTY_ID
,STATUS
,D_PS
,D_CT
,D_CPT
, TX2
, TX4
, TX8
, TX19
, TX32
, TX33
, TX34
, TX35
, TX36
, TX39
, TX40
, TX41
, TX42
, TX43
, TX44
, TX45
, TX46
, TX47
, TX48
, TX59
, TX60
, TX61
, TX62
, TX63
, TX156
, TX157
, TX158
) VALUES (
H_P_PARTY_ID(1)
,H_STATUS(1)
,'SYNC'
,'SYNC'
,'SYNC'
, decode(H_TX2(1),null,H_TX2(1),H_TX2(1)||' ')
, decode(H_TX4(1),null,H_TX4(1),H_TX4(1)||' ')
, decode(H_TX8(1),null,H_TX8(1),H_TX8(1)||' ')
, decode(H_TX19(1),null,H_TX19(1),H_TX19(1)||' ')
, decode(H_TX32(1),null,H_TX32(1),H_TX32(1)||' ')
, decode(H_TX33(1),null,H_TX33(1),H_TX33(1)||' ')
, decode(H_TX34(1),null,H_TX34(1),H_TX34(1)||' ')
, decode(H_TX35(1),null,H_TX35(1),H_TX35(1)||' ')
, decode(H_TX36(1),null,H_TX36(1),H_TX36(1)||' ')
, decode(H_TX39(1),null,H_TX39(1),H_TX39(1)||' ')
, decode(H_TX40(1),null,H_TX40(1),H_TX40(1)||' ')
, decode(H_TX41(1),null,H_TX41(1),H_TX41(1)||' ')
, decode(H_TX42(1),null,H_TX42(1),H_TX42(1)||' ')
, decode(H_TX43(1),null,H_TX43(1),H_TX43(1)||' ')
, decode(H_TX44(1),null,H_TX44(1),H_TX44(1)||' ')
, decode(H_TX45(1),null,H_TX45(1),H_TX45(1)||' ')
, decode(H_TX46(1),null,H_TX46(1),H_TX46(1)||' ')
, decode(H_TX47(1),null,H_TX47(1),H_TX47(1)||' ')
, decode(H_TX48(1),null,H_TX48(1),H_TX48(1)||' ')
, decode(H_TX59(1),null,H_TX59(1),H_TX59(1)||' ')
, decode(H_TX60(1),null,H_TX60(1),H_TX60(1)||' ')
, decode(H_TX61(1),null,H_TX61(1),H_TX61(1)||' ')
, decode(H_TX62(1),null,H_TX62(1),H_TX62(1)||' ')
, decode(H_TX63(1),null,H_TX63(1),H_TX63(1)||' ')
, decode(H_TX156(1),null,H_TX156(1),H_TX156(1)||' ')
, decode(H_TX157(1),null,H_TX157(1),H_TX157(1)||' ')
, decode(H_TX158(1),null,H_TX158(1),H_TX158(1)||' ')
);
UPDATE HZ_STAGED_PARTIES SET
status =H_STATUS(1)
,concat_col = concat_col
,TX2=decode(H_TX2(1),null,H_TX2(1),H_TX2(1)||' ')
,TX4=decode(H_TX4(1),null,H_TX4(1),H_TX4(1)||' ')
,TX8=decode(H_TX8(1),null,H_TX8(1),H_TX8(1)||' ')
,TX19=decode(H_TX19(1),null,H_TX19(1),H_TX19(1)||' ')
,TX32=decode(H_TX32(1),null,H_TX32(1),H_TX32(1)||' ')
,TX33=decode(H_TX33(1),null,H_TX33(1),H_TX33(1)||' ')
,TX34=decode(H_TX34(1),null,H_TX34(1),H_TX34(1)||' ')
,TX35=decode(H_TX35(1),null,H_TX35(1),H_TX35(1)||' ')
,TX36=decode(H_TX36(1),null,H_TX36(1),H_TX36(1)||' ')
,TX39=decode(H_TX39(1),null,H_TX39(1),H_TX39(1)||' ')
,TX40=decode(H_TX40(1),null,H_TX40(1),H_TX40(1)||' ')
,TX41=decode(H_TX41(1),null,H_TX41(1),H_TX41(1)||' ')
,TX42=decode(H_TX42(1),null,H_TX42(1),H_TX42(1)||' ')
,TX43=decode(H_TX43(1),null,H_TX43(1),H_TX43(1)||' ')
,TX44=decode(H_TX44(1),null,H_TX44(1),H_TX44(1)||' ')
,TX45=decode(H_TX45(1),null,H_TX45(1),H_TX45(1)||' ')
,TX46=decode(H_TX46(1),null,H_TX46(1),H_TX46(1)||' ')
,TX47=decode(H_TX47(1),null,H_TX47(1),H_TX47(1)||' ')
,TX48=decode(H_TX48(1),null,H_TX48(1),H_TX48(1)||' ')
,TX59=decode(H_TX59(1),null,H_TX59(1),H_TX59(1)||' ')
,TX60=decode(H_TX60(1),null,H_TX60(1),H_TX60(1)||' ')
,TX61=decode(H_TX61(1),null,H_TX61(1),H_TX61(1)||' ')
,TX62=decode(H_TX62(1),null,H_TX62(1),H_TX62(1)||' ')
,TX63=decode(H_TX63(1),null,H_TX63(1),H_TX63(1)||' ')
,TX156=decode(H_TX156(1),null,H_TX156(1),H_TX156(1)||' ')
,TX157=decode(H_TX157(1),null,H_TX157(1),H_TX157(1)||' ')
,TX158=decode(H_TX158(1),null,H_TX158(1),H_TX158(1)||' ')
WHERE PARTY_ID=H_P_PARTY_ID(1);
SELECT oc.org_contact_id
FROM HZ_RELATIONSHIPS pr, HZ_ORG_CONTACTS oc
WHERE pr.relationship_id = oc.party_relationship_id
AND pr.subject_id = p_party_id
AND pr.subject_table_name = 'HZ_PARTIES'
AND pr.object_table_name = 'HZ_PARTIES'
AND pr.directional_flag = 'F';
SELECT party_type INTO l_party_type
FROM hz_parties WHERE party_id = p_party_id;
insert_dqm_sync_error_rec(p_party_id,l_org_contact_id,null,null,'CONTACTS','U','E','Y', l_sql_err_message);
SELECT p.PARTY_ID, p.STATUS
,p.PARTY_NAME
,p.PARTY_NUMBER
,p.PARTY_TYPE
,p.PARTY_NAME || ' ' || p.KNOWN_AS || ' ' || p.KNOWN_AS2 || ' ' || p.KNOWN_AS3 || ' '|| p.KNOWN_AS4 || ' '|| p.KNOWN_AS5
,op.DUNS_NUMBER_C
,op.TAX_NAME
,op.TAX_REFERENCE
,op.JGZZ_FISCAL_CODE
,op.SIC_CODE
,op.SIC_CODE_TYPE
,p.CATEGORY_CODE
,p.REFERENCE_USE_FLAG
,op.CORPORATION_CLASS
INTO H_P_PARTY_ID(1), H_STATUS(1)
, H_TX2(1)
, H_TX34(1)
, H_TX36(1)
, H_TX39(1)
, H_TX41(1)
, H_TX42(1)
, H_TX44(1)
, H_TX45(1)
, H_TX46(1)
, H_TX47(1)
, H_TX48(1)
, H_TX156(1)
, H_TX157(1)
FROM HZ_PARTIES p, HZ_ORGANIZATION_PROFILES op
WHERE p.party_id = p_party_id
AND p.party_id = op.party_id
AND (p.status = 'M' or op.effective_end_date is NULL) AND ROWNUM=1;
SELECT p.PARTY_ID, p.STATUS
,p.PARTY_NAME
,p.PARTY_NUMBER
,p.PARTY_TYPE
,p.PARTY_NAME || ' ' || p.KNOWN_AS || ' ' || p.KNOWN_AS2 || ' ' || p.KNOWN_AS3 || ' '|| p.KNOWN_AS4 || ' '|| p.KNOWN_AS5
,NULL
,pe.TAX_NAME
,pe.TAX_REFERENCE
,pe.JGZZ_FISCAL_CODE
,NULL
,NULL
,p.CATEGORY_CODE
,p.REFERENCE_USE_FLAG
,NULL
INTO H_P_PARTY_ID(1), H_STATUS(1)
, H_TX2(1)
, H_TX34(1)
, H_TX36(1)
, H_TX39(1)
, H_TX41(1)
, H_TX42(1)
, H_TX44(1)
, H_TX45(1)
, H_TX46(1)
, H_TX47(1)
, H_TX48(1)
, H_TX156(1)
, H_TX157(1)
FROM HZ_PARTIES p, HZ_PERSON_PROFILES pe
WHERE p.party_id = p_party_id
AND p.party_id = pe.party_id
AND (p.status = 'M' or pe.effective_end_date is NULL) AND ROWNUM=1;
SELECT p.PARTY_ID, p.STATUS
,p.PARTY_NAME
,p.PARTY_NUMBER
,p.PARTY_TYPE
,p.PARTY_NAME || ' ' || p.KNOWN_AS || ' ' || p.KNOWN_AS2 || ' ' || p.KNOWN_AS3 || ' '|| p.KNOWN_AS4 || ' '|| p.KNOWN_AS5
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,p.CATEGORY_CODE
,p.REFERENCE_USE_FLAG
,NULL
INTO H_P_PARTY_ID(1), H_STATUS(1)
, H_TX2(1)
, H_TX34(1)
, H_TX36(1)
, H_TX39(1)
, H_TX41(1)
, H_TX42(1)
, H_TX44(1)
, H_TX45(1)
, H_TX46(1)
, H_TX47(1)
, H_TX48(1)
, H_TX156(1)
, H_TX157(1)
FROM HZ_PARTIES p
WHERE p.party_id = p_party_id;
INSERT INTO HZ_STAGED_PARTIES (
PARTY_ID
,STATUS
,D_PS
,D_CT
,D_CPT
,TX2
,TX4
,TX8
,TX19
,TX32
,TX33
,TX34
,TX35
,TX36
,TX39
,TX40
,TX41
,TX42
,TX43
,TX44
,TX45
,TX46
,TX47
,TX48
,TX59
,TX60
,TX61
,TX62
,TX63
,TX156
,TX157
,TX158
) VALUES (
H_P_PARTY_ID(1)
,H_STATUS(1)
,'SYNC'
,'SYNC'
,'SYNC'
,decode(H_TX2(1),null,H_TX2(1),H_TX2(1)||' ')
,decode(H_TX4(1),null,H_TX4(1),H_TX4(1)||' ')
,decode(H_TX8(1),null,H_TX8(1),H_TX8(1)||' ')
,decode(H_TX19(1),null,H_TX19(1),H_TX19(1)||' ')
,decode(H_TX32(1),null,H_TX32(1),H_TX32(1)||' ')
,decode(H_TX33(1),null,H_TX33(1),H_TX33(1)||' ')
,decode(H_TX34(1),null,H_TX34(1),H_TX34(1)||' ')
,decode(H_TX35(1),null,H_TX35(1),H_TX35(1)||' ')
,decode(H_TX36(1),null,H_TX36(1),H_TX36(1)||' ')
,decode(H_TX39(1),null,H_TX39(1),H_TX39(1)||' ')
,decode(H_TX40(1),null,H_TX40(1),H_TX40(1)||' ')
,decode(H_TX41(1),null,H_TX41(1),H_TX41(1)||' ')
,decode(H_TX42(1),null,H_TX42(1),H_TX42(1)||' ')
,decode(H_TX43(1),null,H_TX43(1),H_TX43(1)||' ')
,decode(H_TX44(1),null,H_TX44(1),H_TX44(1)||' ')
,decode(H_TX45(1),null,H_TX45(1),H_TX45(1)||' ')
,decode(H_TX46(1),null,H_TX46(1),H_TX46(1)||' ')
,decode(H_TX47(1),null,H_TX47(1),H_TX47(1)||' ')
,decode(H_TX48(1),null,H_TX48(1),H_TX48(1)||' ')
,decode(H_TX59(1),null,H_TX59(1),H_TX59(1)||' ')
,decode(H_TX60(1),null,H_TX60(1),H_TX60(1)||' ')
,decode(H_TX61(1),null,H_TX61(1),H_TX61(1)||' ')
,decode(H_TX62(1),null,H_TX62(1),H_TX62(1)||' ')
,decode(H_TX63(1),null,H_TX63(1),H_TX63(1)||' ')
,decode(H_TX156(1),null,H_TX156(1),H_TX156(1)||' ')
,decode(H_TX157(1),null,H_TX157(1),H_TX157(1)||' ')
,decode(H_TX158(1),null,H_TX158(1),H_TX158(1)||' ')
);
UPDATE HZ_STAGED_PARTIES SET
concat_col = concat_col
,status =H_STATUS(1)
,TX2=decode(H_TX2(1),null,H_TX2(1),H_TX2(1)||' ')
,TX4=decode(H_TX4(1),null,H_TX4(1),H_TX4(1)||' ')
,TX8=decode(H_TX8(1),null,H_TX8(1),H_TX8(1)||' ')
,TX19=decode(H_TX19(1),null,H_TX19(1),H_TX19(1)||' ')
,TX32=decode(H_TX32(1),null,H_TX32(1),H_TX32(1)||' ')
,TX33=decode(H_TX33(1),null,H_TX33(1),H_TX33(1)||' ')
,TX34=decode(H_TX34(1),null,H_TX34(1),H_TX34(1)||' ')
,TX35=decode(H_TX35(1),null,H_TX35(1),H_TX35(1)||' ')
,TX36=decode(H_TX36(1),null,H_TX36(1),H_TX36(1)||' ')
,TX39=decode(H_TX39(1),null,H_TX39(1),H_TX39(1)||' ')
,TX40=decode(H_TX40(1),null,H_TX40(1),H_TX40(1)||' ')
,TX41=decode(H_TX41(1),null,H_TX41(1),H_TX41(1)||' ')
,TX42=decode(H_TX42(1),null,H_TX42(1),H_TX42(1)||' ')
,TX43=decode(H_TX43(1),null,H_TX43(1),H_TX43(1)||' ')
,TX44=decode(H_TX44(1),null,H_TX44(1),H_TX44(1)||' ')
,TX45=decode(H_TX45(1),null,H_TX45(1),H_TX45(1)||' ')
,TX46=decode(H_TX46(1),null,H_TX46(1),H_TX46(1)||' ')
,TX47=decode(H_TX47(1),null,H_TX47(1),H_TX47(1)||' ')
,TX48=decode(H_TX48(1),null,H_TX48(1),H_TX48(1)||' ')
,TX59=decode(H_TX59(1),null,H_TX59(1),H_TX59(1)||' ')
,TX60=decode(H_TX60(1),null,H_TX60(1),H_TX60(1)||' ')
,TX61=decode(H_TX61(1),null,H_TX61(1),H_TX61(1)||' ')
,TX62=decode(H_TX62(1),null,H_TX62(1),H_TX62(1)||' ')
,TX63=decode(H_TX63(1),null,H_TX63(1),H_TX63(1)||' ')
,TX156=decode(H_TX156(1),null,H_TX156(1),H_TX156(1)||' ')
,TX157=decode(H_TX157(1),null,H_TX157(1),H_TX157(1)||' ')
,TX158=decode(H_TX158(1),null,H_TX158(1),H_TX158(1)||' ')
WHERE PARTY_ID=H_P_PARTY_ID(1);
HZ_DQM_SYNC.insert_sh_interface_rec(p_party_id,null,null,null,'PARTY',p_operation);
insert_dqm_sync_error_rec(p_party_id, NULL, NULL, NULL, 'PARTY', p_operation, 'E', 'Y', l_sql_err_message);
PROCEDURE insert_stage_contacts IS
l_limit NUMBER := 200;
SELECT
/*+ ORDERED USE_NL(R OC PP)*/
oc.ORG_CONTACT_ID , r.OBJECT_ID, r.PARTY_ID, g.PARTY_INDEX, r.STATUS
,rtrim(pp.person_first_name || ' ' || pp.person_last_name)
,oc.CONTACT_NUMBER
,oc.JOB_TITLE
FROM HZ_DQM_STAGE_GT g, HZ_RELATIONSHIPS r,
HZ_ORG_CONTACTS oc, HZ_PERSON_PROFILES pp
WHERE oc.party_relationship_id = r.relationship_id
AND r.object_id = g.party_id
AND r.subject_id = pp.party_id
AND r.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND r.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND DIRECTIONAL_FLAG= 'F'
AND pp.effective_end_date is NULL
AND (oc.status is null OR oc.status = 'A' or oc.status = 'I')
AND (r.status is null OR r.status = 'A' or r.status = 'I');
INSERT INTO HZ_STAGED_CONTACTS (
ORG_CONTACT_ID
,PARTY_ID
,STATUS_FLAG
, TX2
, TX5
, TX6
, TX11
, TX22
, TX23
, TX24
, TX25
, TX156
) VALUES (
H_ORG_CONTACT_ID(I)
,H_C_PARTY_ID(I)
,H_STATUS(I)
, decode(H_TX2(I),null,H_TX2(I),H_TX2(I)||' ')
, decode(H_TX5(I),null,H_TX5(I),H_TX5(I)||' ')
, decode(H_TX6(I),null,H_TX6(I),H_TX6(I)||' ')
, decode(H_TX11(I),null,H_TX11(I),H_TX11(I)||' ')
, decode(H_TX22(I),null,H_TX22(I),H_TX22(I)||' ')
, decode(H_TX23(I),null,H_TX23(I),H_TX23(I)||' ')
, decode(H_TX24(I),null,H_TX24(I),H_TX24(I)||' ')
, decode(H_TX25(I),null,H_TX25(I),H_TX25(I)||' ')
, decode(H_TX156(I),null,H_TX156(I),H_TX156(I)||' ')
);
INSERT INTO HZ_DQM_STAGE_GT(PARTY_ID,OWNER_ID,ORG_CONTACT_ID,PARTY_INDEX)
SELECT H_C_PARTY_ID(I), H_R_PARTY_ID(I), H_ORG_CONTACT_ID(I), H_PARTY_INDEX(I)
FROM DUAL WHERE H_R_PARTY_ID(I) IS NOT NULL;
SELECT oc.ORG_CONTACT_ID, d.PARTY_ID, r.STATUS
,rtrim(pp.person_first_name || ' ' || pp.person_last_name)
,oc.CONTACT_NUMBER
,oc.JOB_TITLE
INTO H_ORG_CONTACT_ID(1), H_PARTY_ID(1), H_STATUS(1)
, H_TX2(1)
, H_TX11(1)
, H_TX22(1)
FROM HZ_ORG_CONTACTS oc, HZ_DQM_SYNC_INTERFACE d,
HZ_RELATIONSHIPS r, HZ_PERSON_PROFILES pp
WHERE d.ENTITY = 'CONTACTS'
AND oc.org_contact_id = p_org_contact_id
AND oc.org_contact_id = d.RECORD_ID
AND oc.party_relationship_id = r.relationship_id
AND r.subject_id = pp.party_id
AND r.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND r.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND DIRECTIONAL_FLAG= 'F'
AND pp.effective_end_date is NULL
AND (oc.status is null OR oc.status = 'A' or oc.status = 'I')
AND (r.status is null OR r.status = 'A' or r.status = 'I')
AND ROWNUM=1;
INSERT INTO HZ_STAGED_CONTACTS (
ORG_CONTACT_ID
,PARTY_ID
,STATUS_FLAG
, TX2
, TX5
, TX6
, TX11
, TX22
, TX23
, TX24
, TX25
, TX156
) VALUES (
H_ORG_CONTACT_ID(1)
, H_PARTY_ID(1)
, H_STATUS(1)
, decode(H_TX2(1),null,H_TX2(1),H_TX2(1)||' ')
, decode(H_TX5(1),null,H_TX5(1),H_TX5(1)||' ')
, decode(H_TX6(1),null,H_TX6(1),H_TX6(1)||' ')
, decode(H_TX11(1),null,H_TX11(1),H_TX11(1)||' ')
, decode(H_TX22(1),null,H_TX22(1),H_TX22(1)||' ')
, decode(H_TX23(1),null,H_TX23(1),H_TX23(1)||' ')
, decode(H_TX24(1),null,H_TX24(1),H_TX24(1)||' ')
, decode(H_TX25(1),null,H_TX25(1),H_TX25(1)||' ')
, decode(H_TX156(1),null,H_TX156(1),H_TX156(1)||' ')
);
UPDATE HZ_STAGED_CONTACTS SET
concat_col = concat_col
,status_flag = H_STATUS(1)
,TX2=decode(H_TX2(1),null,H_TX2(1),H_TX2(1)||' ')
,TX5=decode(H_TX5(1),null,H_TX5(1),H_TX5(1)||' ')
,TX6=decode(H_TX6(1),null,H_TX6(1),H_TX6(1)||' ')
,TX11=decode(H_TX11(1),null,H_TX11(1),H_TX11(1)||' ')
,TX22=decode(H_TX22(1),null,H_TX22(1),H_TX22(1)||' ')
,TX23=decode(H_TX23(1),null,H_TX23(1),H_TX23(1)||' ')
,TX24=decode(H_TX24(1),null,H_TX24(1),H_TX24(1)||' ')
,TX25=decode(H_TX25(1),null,H_TX25(1),H_TX25(1)||' ')
,TX156=decode(H_TX156(1),null,H_TX156(1),H_TX156(1)||' ')
WHERE ORG_CONTACT_ID=H_ORG_CONTACT_ID(1);
UPDATE HZ_STAGED_PARTIES set
D_CT = 'SYNC'
,CONCAT_COL = CONCAT_COL
WHERE PARTY_ID = H_PARTY_ID(1);
SELECT r.object_id INTO l_party_id
FROM HZ_ORG_CONTACTS oc, HZ_RELATIONSHIPS r
WHERE oc.org_contact_id = p_org_contact_id
AND oc.party_relationship_id = r.relationship_id
AND r.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND r.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND subject_type = 'PERSON'
AND DIRECTIONAL_FLAG = 'F'
AND (oc.status is null OR oc.status = 'A' or oc.status = 'I')
AND (r.status is null OR r.status = 'A' or r.status = 'I') ;
SELECT oc.ORG_CONTACT_ID, l_party_id, r.status
,rtrim(pp.person_first_name || ' ' || pp.person_last_name)
,oc.CONTACT_NUMBER
,oc.JOB_TITLE
INTO H_ORG_CONTACT_ID(1), H_PARTY_ID(1), H_STATUS(1)
,H_TX2(1)
,H_TX11(1)
,H_TX22(1)
FROM HZ_ORG_CONTACTS oc,
HZ_RELATIONSHIPS r, HZ_PERSON_PROFILES pp
WHERE
oc.org_contact_id = p_org_contact_id
AND oc.party_relationship_id = r.relationship_id
AND r.subject_id = pp.party_id
AND r.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND r.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND DIRECTIONAL_FLAG = 'F'
AND pp.effective_end_date is NULL
AND (oc.status is null OR oc.status = 'A' or oc.status = 'I')
AND (r.status is null OR r.status = 'A' or r.status = 'I')
AND ROWNUM=1;
INSERT INTO HZ_STAGED_CONTACTS (
ORG_CONTACT_ID
,PARTY_ID
,STATUS_FLAG
,TX2
,TX5
,TX6
,TX11
,TX22
,TX23
,TX24
,TX25
,TX156
) VALUES (
H_ORG_CONTACT_ID(1)
,H_PARTY_ID(1)
,H_STATUS(1)
,decode(H_TX2(1),null,H_TX2(1),H_TX2(1)||' ')
,decode(H_TX5(1),null,H_TX5(1),H_TX5(1)||' ')
,decode(H_TX6(1),null,H_TX6(1),H_TX6(1)||' ')
,decode(H_TX11(1),null,H_TX11(1),H_TX11(1)||' ')
,decode(H_TX22(1),null,H_TX22(1),H_TX22(1)||' ')
,decode(H_TX23(1),null,H_TX23(1),H_TX23(1)||' ')
,decode(H_TX24(1),null,H_TX24(1),H_TX24(1)||' ')
,decode(H_TX25(1),null,H_TX25(1),H_TX25(1)||' ')
,decode(H_TX156(1),null,H_TX156(1),H_TX156(1)||' ')
);
UPDATE HZ_STAGED_CONTACTS SET
concat_col = concat_col
,status_flag = H_STATUS(1)
,TX2=decode(H_TX2(1),null,H_TX2(1),H_TX2(1)||' ')
,TX5=decode(H_TX5(1),null,H_TX5(1),H_TX5(1)||' ')
,TX6=decode(H_TX6(1),null,H_TX6(1),H_TX6(1)||' ')
,TX11=decode(H_TX11(1),null,H_TX11(1),H_TX11(1)||' ')
,TX22=decode(H_TX22(1),null,H_TX22(1),H_TX22(1)||' ')
,TX23=decode(H_TX23(1),null,H_TX23(1),H_TX23(1)||' ')
,TX24=decode(H_TX24(1),null,H_TX24(1),H_TX24(1)||' ')
,TX25=decode(H_TX25(1),null,H_TX25(1),H_TX25(1)||' ')
,TX156=decode(H_TX156(1),null,H_TX156(1),H_TX156(1)||' ')
WHERE ORG_CONTACT_ID=H_ORG_CONTACT_ID(1);
UPDATE HZ_STAGED_PARTIES set
D_CT = 'SYNC'
,CONCAT_COL = CONCAT_COL
WHERE PARTY_ID = H_PARTY_ID(1);
HZ_DQM_SYNC.insert_sh_interface_rec(l_party_id,p_org_contact_id,null,null,'CONTACTS',p_operation);
insert_dqm_sync_error_rec(l_party_id, p_org_contact_id, NULL, NULL, 'CONTACTS', p_operation, 'E', 'Y', l_sql_err_message);
PROCEDURE insert_stage_contact_pts IS
l_limit NUMBER := 200;
SELECT /*+ ORDERED USE_NL(cp) */ cp.CONTACT_POINT_ID, g.party_id, g.party_site_id, g.org_contact_id, cp.CONTACT_POINT_TYPE, PARTY_INDEX, cp.STATUS
,translate(phone_number,'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz!"#$%&()''*+,-./:;<=>?@[\]^_`{|}~ ','0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ') || ' ' || translate(phone_area_code||' ' || phone_number,'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz!"#$%&()''*+,-./:;<=>?@[\]^_`{|}~ ','0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ') || ' ' || translate(phone_country_code|| ' ' || phone_area_code||' ' || phone_number,'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz!"#$%&()''*+,-./:;<=>?@[\]^_`{|}~ ','0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ')
INSERT INTO HZ_STAGED_CONTACT_POINTS (
CONTACT_POINT_ID
,PARTY_ID
,PARTY_SITE_ID
,ORG_CONTACT_ID
,CONTACT_POINT_TYPE
,STATUS_FLAG
, TX1
, TX2
, TX3
, TX4
, TX5
, TX6
, TX7
, TX8
, TX9
, TX10
, TX11
, TX12
, TX13
, TX14
, TX158
) VALUES (
H_CONTACT_POINT_ID(I)
,H_CPT_PARTY_ID(I)
,H_CPT_PARTY_SITE_ID(I)
,H_CPT_ORG_CONTACT_ID(I)
,H_CONTACT_POINT_TYPE(I)
,H_STATUS(I)
, decode(H_TX1(I),null,H_TX1(I),H_TX1(I)||' ')
, decode(H_TX2(I),null,H_TX2(I),H_TX2(I)||' ')
, decode(H_TX3(I),null,H_TX3(I),H_TX3(I)||' ')
, decode(H_TX4(I),null,H_TX4(I),H_TX4(I)||' ')
, decode(H_TX5(I),null,H_TX5(I),H_TX5(I)||' ')
, decode(H_TX6(I),null,H_TX6(I),H_TX6(I)||' ')
, decode(H_TX7(I),null,H_TX7(I),H_TX7(I)||' ')
, decode(H_TX8(I),null,H_TX8(I),H_TX8(I)||' ')
, decode(H_TX9(I),null,H_TX9(I),H_TX9(I)||' ')
, decode(H_TX10(I),null,H_TX10(I),H_TX10(I)||' ')
, decode(H_TX11(I),null,H_TX11(I),H_TX11(I)||' ')
, decode(H_TX12(I),null,H_TX12(I),H_TX12(I)||' ')
, decode(H_TX13(I),null,H_TX13(I),H_TX13(I)||' ')
, decode(H_TX14(I),null,H_TX14(I),H_TX14(I)||' ')
, decode(H_TX158(I),null,H_TX158(I),H_TX158(I)||' ')
);
SELECT cp.CONTACT_POINT_ID, d.PARTY_ID, d.PARTY_SITE_ID, d.ORG_CONTACT_ID, cp.CONTACT_POINT_TYPE, cp.STATUS
,translate(phone_number,'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz!"#$%&()''*+,-./:;<=>?@[\]^_`{|}~ ','0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ') || ' ' || translate(phone_area_code||' ' || phone_number,'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz!"#$%&()''*+,-./:;<=>?@[\]^_`{|}~ ','0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ') || ' ' || translate(phone_country_code|| ' ' || phone_area_code||' ' || phone_number,'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz!"#$%&()''*+,-./:;<=>?@[\]^_`{|}~ ','0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ')
INSERT INTO HZ_STAGED_CONTACT_POINTS (
CONTACT_POINT_ID
,PARTY_ID
,PARTY_SITE_ID
,ORG_CONTACT_ID
,CONTACT_POINT_TYPE
,STATUS_FLAG
, TX1
, TX2
, TX3
, TX4
, TX5
, TX6
, TX7
, TX8
, TX9
, TX10
, TX11
, TX12
, TX13
, TX14
, TX158
) VALUES (
H_CONTACT_POINT_ID(1)
,H_PARTY_ID(1)
,H_PARTY_SITE_ID(1)
,H_ORG_CONTACT_ID(1)
,H_CONTACT_POINT_TYPE(1)
,H_STATUS(1)
, decode(H_TX1(1),null,H_TX1(1),H_TX1(1)||' ')
, decode(H_TX2(1),null,H_TX2(1),H_TX2(1)||' ')
, decode(H_TX3(1),null,H_TX3(1),H_TX3(1)||' ')
, decode(H_TX4(1),null,H_TX4(1),H_TX4(1)||' ')
, decode(H_TX5(1),null,H_TX5(1),H_TX5(1)||' ')
, decode(H_TX6(1),null,H_TX6(1),H_TX6(1)||' ')
, decode(H_TX7(1),null,H_TX7(1),H_TX7(1)||' ')
, decode(H_TX8(1),null,H_TX8(1),H_TX8(1)||' ')
, decode(H_TX9(1),null,H_TX9(1),H_TX9(1)||' ')
, decode(H_TX10(1),null,H_TX10(1),H_TX10(1)||' ')
, decode(H_TX11(1),null,H_TX11(1),H_TX11(1)||' ')
, decode(H_TX12(1),null,H_TX12(1),H_TX12(1)||' ')
, decode(H_TX13(1),null,H_TX13(1),H_TX13(1)||' ')
, decode(H_TX14(1),null,H_TX14(1),H_TX14(1)||' ')
, decode(H_TX158(1),null,H_TX158(1),H_TX158(1)||' ')
);
UPDATE HZ_STAGED_CONTACT_POINTS SET
concat_col = concat_col
,status_flag = H_STATUS(1)
,TX1=decode(H_TX1(1),null,H_TX1(1),H_TX1(1)||' ')
,TX2=decode(H_TX2(1),null,H_TX2(1),H_TX2(1)||' ')
,TX3=decode(H_TX3(1),null,H_TX3(1),H_TX3(1)||' ')
,TX4=decode(H_TX4(1),null,H_TX4(1),H_TX4(1)||' ')
,TX5=decode(H_TX5(1),null,H_TX5(1),H_TX5(1)||' ')
,TX6=decode(H_TX6(1),null,H_TX6(1),H_TX6(1)||' ')
,TX7=decode(H_TX7(1),null,H_TX7(1),H_TX7(1)||' ')
,TX8=decode(H_TX8(1),null,H_TX8(1),H_TX8(1)||' ')
,TX9=decode(H_TX9(1),null,H_TX9(1),H_TX9(1)||' ')
,TX10=decode(H_TX10(1),null,H_TX10(1),H_TX10(1)||' ')
,TX11=decode(H_TX11(1),null,H_TX11(1),H_TX11(1)||' ')
,TX12=decode(H_TX12(1),null,H_TX12(1),H_TX12(1)||' ')
,TX13=decode(H_TX13(1),null,H_TX13(1),H_TX13(1)||' ')
,TX14=decode(H_TX14(1),null,H_TX14(1),H_TX14(1)||' ')
,TX158=decode(H_TX158(1),null,H_TX158(1),H_TX158(1)||' ')
WHERE CONTACT_POINT_ID=H_CONTACT_POINT_ID(1);
UPDATE HZ_STAGED_PARTIES set
D_CPT = 'SYNC'
,CONCAT_COL = CONCAT_COL
WHERE PARTY_ID = H_PARTY_ID(1);
SELECT owner_table_name,owner_table_id INTO l_ot_table, l_ot_id
FROM hz_contact_points
WHERE contact_point_id = p_contact_point_id;
SELECT p.party_id, ps.party_site_id, party_type
INTO l_party_id1, l_party_site_id, l_party_type
FROM HZ_PARTY_SITES ps, HZ_PARTIES p
WHERE party_site_id = l_ot_id
AND p.party_id = ps.party_id;
SELECT r.object_id, org_contact_id INTO l_party_id,l_org_contact_id
FROM HZ_ORG_CONTACTS oc, HZ_RELATIONSHIPS r
WHERE r.party_id = l_party_id1
AND r.relationship_id = oc.party_relationship_id
AND r.directional_flag = 'F'
AND r.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND r.OBJECT_TABLE_NAME = 'HZ_PARTIES';
SELECT party_type INTO l_party_type
FROM hz_parties
WHERE party_id = l_ot_id;
SELECT r.object_id, org_contact_id INTO l_party_id,l_org_contact_id
FROM HZ_ORG_CONTACTS oc, HZ_RELATIONSHIPS r
WHERE r.party_id = l_ot_id
AND r.relationship_id = oc.party_relationship_id
AND r.directional_flag = 'F'
AND r.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND r.OBJECT_TABLE_NAME = 'HZ_PARTIES';
SELECT cp.CONTACT_POINT_ID, l_party_id, l_party_site_id, l_org_contact_id, cp.CONTACT_POINT_TYPE, cp.STATUS
,translate(phone_number,'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz!"#$%&()''*+,-./:;<=>?@[\]^_`{|}~ ','0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ') || ' ' || translate(phone_area_code||' ' || phone_number,'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz!"#$%&()''*+,-./:;<=>?@[\]^_`{|}~ ','0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ') || ' ' || translate(phone_country_code|| ' ' || phone_area_code||' ' || phone_number,'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz!"#$%&()''*+,-./:;<=>?@[\]^_`{|}~ ','0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ')
INSERT INTO HZ_STAGED_CONTACT_POINTS (
CONTACT_POINT_ID
,PARTY_ID
,PARTY_SITE_ID
,ORG_CONTACT_ID
,CONTACT_POINT_TYPE
,STATUS_FLAG
,TX1
,TX2
,TX3
,TX4
,TX5
,TX6
,TX7
,TX8
,TX9
,TX10
,TX11
,TX12
,TX13
,TX14
,TX158
) VALUES (
H_CONTACT_POINT_ID(1)
,H_PARTY_ID(1)
,H_PARTY_SITE_ID(1)
,H_ORG_CONTACT_ID(1)
,H_CONTACT_POINT_TYPE(1)
,H_STATUS(1)
,decode(H_TX1(1),null,H_TX1(1),H_TX1(1)||' ')
,decode(H_TX2(1),null,H_TX2(1),H_TX2(1)||' ')
,decode(H_TX3(1),null,H_TX3(1),H_TX3(1)||' ')
,decode(H_TX4(1),null,H_TX4(1),H_TX4(1)||' ')
,decode(H_TX5(1),null,H_TX5(1),H_TX5(1)||' ')
,decode(H_TX6(1),null,H_TX6(1),H_TX6(1)||' ')
,decode(H_TX7(1),null,H_TX7(1),H_TX7(1)||' ')
,decode(H_TX8(1),null,H_TX8(1),H_TX8(1)||' ')
,decode(H_TX9(1),null,H_TX9(1),H_TX9(1)||' ')
,decode(H_TX10(1),null,H_TX10(1),H_TX10(1)||' ')
,decode(H_TX11(1),null,H_TX11(1),H_TX11(1)||' ')
,decode(H_TX12(1),null,H_TX12(1),H_TX12(1)||' ')
,decode(H_TX13(1),null,H_TX13(1),H_TX13(1)||' ')
,decode(H_TX14(1),null,H_TX14(1),H_TX14(1)||' ')
,decode(H_TX158(1),null,H_TX158(1),H_TX158(1)||' ')
);
UPDATE HZ_STAGED_CONTACT_POINTS SET
concat_col = concat_col
,status_flag = H_STATUS(1)
,TX1=decode(H_TX1(1),null,H_TX1(1),H_TX1(1)||' ')
,TX2=decode(H_TX2(1),null,H_TX2(1),H_TX2(1)||' ')
,TX3=decode(H_TX3(1),null,H_TX3(1),H_TX3(1)||' ')
,TX4=decode(H_TX4(1),null,H_TX4(1),H_TX4(1)||' ')
,TX5=decode(H_TX5(1),null,H_TX5(1),H_TX5(1)||' ')
,TX6=decode(H_TX6(1),null,H_TX6(1),H_TX6(1)||' ')
,TX7=decode(H_TX7(1),null,H_TX7(1),H_TX7(1)||' ')
,TX8=decode(H_TX8(1),null,H_TX8(1),H_TX8(1)||' ')
,TX9=decode(H_TX9(1),null,H_TX9(1),H_TX9(1)||' ')
,TX10=decode(H_TX10(1),null,H_TX10(1),H_TX10(1)||' ')
,TX11=decode(H_TX11(1),null,H_TX11(1),H_TX11(1)||' ')
,TX12=decode(H_TX12(1),null,H_TX12(1),H_TX12(1)||' ')
,TX13=decode(H_TX13(1),null,H_TX13(1),H_TX13(1)||' ')
,TX14=decode(H_TX14(1),null,H_TX14(1),H_TX14(1)||' ')
,TX158=decode(H_TX158(1),null,H_TX158(1),H_TX158(1)||' ')
WHERE CONTACT_POINT_ID=H_CONTACT_POINT_ID(1);
UPDATE HZ_STAGED_PARTIES set
D_CPT = 'SYNC'
,CONCAT_COL = CONCAT_COL
WHERE PARTY_ID = H_PARTY_ID(1);
HZ_DQM_SYNC.insert_sh_interface_rec(l_party_id,p_contact_point_id,l_party_site_id, l_org_contact_id, 'CONTACT_POINTS',p_operation);
insert_dqm_sync_error_rec(l_party_id, p_contact_point_id, l_party_site_id, l_org_contact_id, 'CONTACT_POINTS', p_operation, 'E', 'Y', l_sql_err_message);
PROCEDURE insert_stage_party_sites IS
l_limit NUMBER := 200;
SELECT /*+ ORDERED USE_NL(ps l) */ ps.PARTY_SITE_ID, g.party_id, g.org_contact_id, g.PARTY_INDEX, ps.status
,rtrim(l.address1 || ' ' || l.address2 || ' ' || l.address3 || ' ' || l.address4)
,l.CITY
,l.POSTAL_CODE
,l.PROVINCE
,l.STATE
,ps.PARTY_SITE_NUMBER
,ps.PARTY_SITE_NAME
,l.COUNTY
,l.COUNTRY
,ps.IDENTIFYING_ADDRESS_FLAG
,ps.STATUS
,l.ADDRESS1
FROM HZ_DQM_STAGE_GT g, HZ_PARTY_SITES ps, HZ_LOCATIONS l
WHERE ps.PARTY_ID = g.owner_id
AND (ps.status is null OR ps.status = 'A' OR ps.status = 'I')
AND ps.location_id = l.location_id;
INSERT INTO HZ_STAGED_PARTY_SITES (
PARTY_SITE_ID
,PARTY_ID
,ORG_CONTACT_ID
,STATUS_FLAG
, TX3
, TX4
, TX9
, TX10
, TX11
, TX12
, TX13
, TX14
, TX15
, TX17
, TX18
, TX19
, TX20
, TX21
, TX22
, TX24
, TX25
, TX26
, TX27
, TX28
, TX29
, TX30
) VALUES (
H_PARTY_SITE_ID(I)
,H_PS_PARTY_ID(I)
,H_PS_ORG_CONTACT_ID(I)
,H_STATUS(I)
, decode(H_TX3(I),null,H_TX3(I),H_TX3(I)||' ')
, decode(H_TX4(I),null,H_TX4(I),H_TX4(I)||' ')
, decode(H_TX9(I),null,H_TX9(I),H_TX9(I)||' ')
, decode(H_TX10(I),null,H_TX10(I),H_TX10(I)||' ')
, decode(H_TX11(I),null,H_TX11(I),H_TX11(I)||' ')
, decode(H_TX12(I),null,H_TX12(I),H_TX12(I)||' ')
, decode(H_TX13(I),null,H_TX13(I),H_TX13(I)||' ')
, decode(H_TX14(I),null,H_TX14(I),H_TX14(I)||' ')
, decode(H_TX15(I),null,H_TX15(I),H_TX15(I)||' ')
, decode(H_TX17(I),null,H_TX17(I),H_TX17(I)||' ')
, decode(H_TX18(I),null,H_TX18(I),H_TX18(I)||' ')
, decode(H_TX19(I),null,H_TX19(I),H_TX19(I)||' ')
, decode(H_TX20(I),null,H_TX20(I),H_TX20(I)||' ')
, decode(H_TX21(I),null,H_TX21(I),H_TX21(I)||' ')
, decode(H_TX22(I),null,H_TX22(I),H_TX22(I)||' ')
, decode(H_TX24(I),null,H_TX24(I),H_TX24(I)||' ')
, decode(H_TX25(I),null,H_TX25(I),H_TX25(I)||' ')
, decode(H_TX26(I),null,H_TX26(I),H_TX26(I)||' ')
, decode(H_TX27(I),null,H_TX27(I),H_TX27(I)||' ')
, decode(H_TX28(I),null,H_TX28(I),H_TX28(I)||' ')
, decode(H_TX29(I),null,H_TX29(I),H_TX29(I)||' ')
, decode(H_TX30(I),null,H_TX30(I),H_TX30(I)||' ')
);
INSERT INTO HZ_DQM_STAGE_GT (PARTY_ID, OWNER_ID, OWNER_TABLE, PARTY_SITE_ID,
ORG_CONTACT_ID,PARTY_INDEX) VALUES (
H_PS_PARTY_ID(I),H_PARTY_SITE_ID(I),'HZ_PARTY_SITES',H_PARTY_SITE_ID(I),
H_PS_ORG_CONTACT_ID(I),H_PARTY_INDEX(I));
SELECT ps.PARTY_SITE_ID, d.party_id, d.org_contact_id, ps.STATUS
,rtrim(l.address1 || ' ' || l.address2 || ' ' || l.address3 || ' ' || l.address4)
,l.CITY
,l.POSTAL_CODE
,l.PROVINCE
,l.STATE
,ps.PARTY_SITE_NUMBER
,ps.PARTY_SITE_NAME
,l.COUNTY
,l.COUNTRY
,ps.IDENTIFYING_ADDRESS_FLAG
,ps.STATUS
,l.ADDRESS1
INTO H_PARTY_SITE_ID(1), H_PARTY_ID(1), H_ORG_CONTACT_ID(1), H_STATUS(1)
, H_TX3(1)
, H_TX9(1)
, H_TX11(1)
, H_TX12(1)
, H_TX14(1)
, H_TX17(1)
, H_TX18(1)
, H_TX20(1)
, H_TX22(1)
, H_TX24(1)
, H_TX25(1)
, H_TX28(1)
FROM HZ_PARTY_SITES ps, HZ_DQM_SYNC_INTERFACE d, HZ_LOCATIONS l
WHERE d.ENTITY='PARTY_SITES'
AND ps.party_site_id = p_party_site_id
AND d.record_id = ps.party_site_id
AND ps.location_id = l.location_id
AND (ps.status is null OR ps.status = 'A' OR ps.status = 'I')
AND ROWNUM=1;
INSERT INTO HZ_STAGED_PARTY_SITES (
PARTY_SITE_ID
,PARTY_ID
,ORG_CONTACT_ID
,STATUS_FLAG
, TX3
, TX4
, TX9
, TX10
, TX11
, TX12
, TX13
, TX14
, TX15
, TX17
, TX18
, TX19
, TX20
, TX21
, TX22
, TX24
, TX25
, TX26
, TX27
, TX28
, TX29
, TX30
) VALUES (
H_PARTY_SITE_ID(1)
,H_PARTY_ID(1)
,H_ORG_CONTACT_ID(1)
,H_STATUS(1)
, decode(H_TX3(1),null,H_TX3(1),H_TX3(1)||' ')
, decode(H_TX4(1),null,H_TX4(1),H_TX4(1)||' ')
, decode(H_TX9(1),null,H_TX9(1),H_TX9(1)||' ')
, decode(H_TX10(1),null,H_TX10(1),H_TX10(1)||' ')
, decode(H_TX11(1),null,H_TX11(1),H_TX11(1)||' ')
, decode(H_TX12(1),null,H_TX12(1),H_TX12(1)||' ')
, decode(H_TX13(1),null,H_TX13(1),H_TX13(1)||' ')
, decode(H_TX14(1),null,H_TX14(1),H_TX14(1)||' ')
, decode(H_TX15(1),null,H_TX15(1),H_TX15(1)||' ')
, decode(H_TX17(1),null,H_TX17(1),H_TX17(1)||' ')
, decode(H_TX18(1),null,H_TX18(1),H_TX18(1)||' ')
, decode(H_TX19(1),null,H_TX19(1),H_TX19(1)||' ')
, decode(H_TX20(1),null,H_TX20(1),H_TX20(1)||' ')
, decode(H_TX21(1),null,H_TX21(1),H_TX21(1)||' ')
, decode(H_TX22(1),null,H_TX22(1),H_TX22(1)||' ')
, decode(H_TX24(1),null,H_TX24(1),H_TX24(1)||' ')
, decode(H_TX25(1),null,H_TX25(1),H_TX25(1)||' ')
, decode(H_TX26(1),null,H_TX26(1),H_TX26(1)||' ')
, decode(H_TX27(1),null,H_TX27(1),H_TX27(1)||' ')
, decode(H_TX28(1),null,H_TX28(1),H_TX28(1)||' ')
, decode(H_TX29(1),null,H_TX29(1),H_TX29(1)||' ')
, decode(H_TX30(1),null,H_TX30(1),H_TX30(1)||' ')
);
UPDATE HZ_STAGED_PARTY_SITES SET
concat_col = concat_col
,status_flag = H_STATUS(1)
,TX3=decode(H_TX3(1),null,H_TX3(1),H_TX3(1)||' ')
,TX4=decode(H_TX4(1),null,H_TX4(1),H_TX4(1)||' ')
,TX9=decode(H_TX9(1),null,H_TX9(1),H_TX9(1)||' ')
,TX10=decode(H_TX10(1),null,H_TX10(1),H_TX10(1)||' ')
,TX11=decode(H_TX11(1),null,H_TX11(1),H_TX11(1)||' ')
,TX12=decode(H_TX12(1),null,H_TX12(1),H_TX12(1)||' ')
,TX13=decode(H_TX13(1),null,H_TX13(1),H_TX13(1)||' ')
,TX14=decode(H_TX14(1),null,H_TX14(1),H_TX14(1)||' ')
,TX15=decode(H_TX15(1),null,H_TX15(1),H_TX15(1)||' ')
,TX17=decode(H_TX17(1),null,H_TX17(1),H_TX17(1)||' ')
,TX18=decode(H_TX18(1),null,H_TX18(1),H_TX18(1)||' ')
,TX19=decode(H_TX19(1),null,H_TX19(1),H_TX19(1)||' ')
,TX20=decode(H_TX20(1),null,H_TX20(1),H_TX20(1)||' ')
,TX21=decode(H_TX21(1),null,H_TX21(1),H_TX21(1)||' ')
,TX22=decode(H_TX22(1),null,H_TX22(1),H_TX22(1)||' ')
,TX24=decode(H_TX24(1),null,H_TX24(1),H_TX24(1)||' ')
,TX25=decode(H_TX25(1),null,H_TX25(1),H_TX25(1)||' ')
,TX26=decode(H_TX26(1),null,H_TX26(1),H_TX26(1)||' ')
,TX27=decode(H_TX27(1),null,H_TX27(1),H_TX27(1)||' ')
,TX28=decode(H_TX28(1),null,H_TX28(1),H_TX28(1)||' ')
,TX29=decode(H_TX29(1),null,H_TX29(1),H_TX29(1)||' ')
,TX30=decode(H_TX30(1),null,H_TX30(1),H_TX30(1)||' ')
WHERE PARTY_SITE_ID=H_PARTY_SITE_ID(1);
UPDATE HZ_STAGED_PARTIES set
D_PS = 'SYNC'
,CONCAT_COL = CONCAT_COL
WHERE PARTY_ID = H_PARTY_ID(1);
SELECT ps.party_id,p.party_type INTO l_party_id1, l_party_type
FROM HZ_PARTY_SITES ps, HZ_PARTIES p
WHERE party_site_id = p_party_site_id
AND p.PARTY_ID = ps.PARTY_ID;
SELECT r.object_id, org_contact_id INTO l_party_id,l_org_contact_id
FROM HZ_ORG_CONTACTS oc, HZ_RELATIONSHIPS r
WHERE r.party_id = l_party_id1
AND r.relationship_id = oc.party_relationship_id
AND r.directional_flag = 'F'
AND r.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND r.OBJECT_TABLE_NAME = 'HZ_PARTIES';
SELECT ps.PARTY_SITE_ID, l_party_id, l_org_contact_id, ps.STATUS
,rtrim(l.address1 || ' ' || l.address2 || ' ' || l.address3 || ' ' || l.address4)
,l.CITY
,l.POSTAL_CODE
,l.PROVINCE
,l.STATE
,ps.PARTY_SITE_NUMBER
,ps.PARTY_SITE_NAME
,l.COUNTY
,l.COUNTRY
,ps.IDENTIFYING_ADDRESS_FLAG
,ps.STATUS
,l.ADDRESS1
INTO H_PARTY_SITE_ID(1), H_PARTY_ID(1), H_ORG_CONTACT_ID(1), H_STATUS(1)
,H_TX3(1)
,H_TX9(1)
,H_TX11(1)
,H_TX12(1)
,H_TX14(1)
,H_TX17(1)
,H_TX18(1)
,H_TX20(1)
,H_TX22(1)
,H_TX24(1)
,H_TX25(1)
,H_TX28(1)
FROM HZ_PARTY_SITES ps, HZ_LOCATIONS l
WHERE
ps.party_site_id = p_party_site_id
AND ps.location_id = l.location_id
AND (ps.status is null OR ps.status = 'A' OR ps.status = 'I')
AND ROWNUM=1;
INSERT INTO HZ_STAGED_PARTY_SITES (
PARTY_SITE_ID
,PARTY_ID
,ORG_CONTACT_ID
,STATUS_FLAG
,TX3
,TX4
,TX9
,TX10
,TX11
,TX12
,TX13
,TX14
,TX15
,TX17
,TX18
,TX19
,TX20
,TX21
,TX22
,TX24
,TX25
,TX26
,TX27
,TX28
,TX29
,TX30
) VALUES (
H_PARTY_SITE_ID(1)
,H_PARTY_ID(1)
,H_ORG_CONTACT_ID(1)
,H_STATUS(1)
,decode(H_TX3(1),null,H_TX3(1),H_TX3(1)||' ')
,decode(H_TX4(1),null,H_TX4(1),H_TX4(1)||' ')
,decode(H_TX9(1),null,H_TX9(1),H_TX9(1)||' ')
,decode(H_TX10(1),null,H_TX10(1),H_TX10(1)||' ')
,decode(H_TX11(1),null,H_TX11(1),H_TX11(1)||' ')
,decode(H_TX12(1),null,H_TX12(1),H_TX12(1)||' ')
,decode(H_TX13(1),null,H_TX13(1),H_TX13(1)||' ')
,decode(H_TX14(1),null,H_TX14(1),H_TX14(1)||' ')
,decode(H_TX15(1),null,H_TX15(1),H_TX15(1)||' ')
,decode(H_TX17(1),null,H_TX17(1),H_TX17(1)||' ')
,decode(H_TX18(1),null,H_TX18(1),H_TX18(1)||' ')
,decode(H_TX19(1),null,H_TX19(1),H_TX19(1)||' ')
,decode(H_TX20(1),null,H_TX20(1),H_TX20(1)||' ')
,decode(H_TX21(1),null,H_TX21(1),H_TX21(1)||' ')
,decode(H_TX22(1),null,H_TX22(1),H_TX22(1)||' ')
,decode(H_TX24(1),null,H_TX24(1),H_TX24(1)||' ')
,decode(H_TX25(1),null,H_TX25(1),H_TX25(1)||' ')
,decode(H_TX26(1),null,H_TX26(1),H_TX26(1)||' ')
,decode(H_TX27(1),null,H_TX27(1),H_TX27(1)||' ')
,decode(H_TX28(1),null,H_TX28(1),H_TX28(1)||' ')
,decode(H_TX29(1),null,H_TX29(1),H_TX29(1)||' ')
,decode(H_TX30(1),null,H_TX30(1),H_TX30(1)||' ')
);
UPDATE HZ_STAGED_PARTY_SITES SET
concat_col = concat_col
,status_flag = H_STATUS(1)
,TX3=decode(H_TX3(1),null,H_TX3(1),H_TX3(1)||' ')
,TX4=decode(H_TX4(1),null,H_TX4(1),H_TX4(1)||' ')
,TX9=decode(H_TX9(1),null,H_TX9(1),H_TX9(1)||' ')
,TX10=decode(H_TX10(1),null,H_TX10(1),H_TX10(1)||' ')
,TX11=decode(H_TX11(1),null,H_TX11(1),H_TX11(1)||' ')
,TX12=decode(H_TX12(1),null,H_TX12(1),H_TX12(1)||' ')
,TX13=decode(H_TX13(1),null,H_TX13(1),H_TX13(1)||' ')
,TX14=decode(H_TX14(1),null,H_TX14(1),H_TX14(1)||' ')
,TX15=decode(H_TX15(1),null,H_TX15(1),H_TX15(1)||' ')
,TX17=decode(H_TX17(1),null,H_TX17(1),H_TX17(1)||' ')
,TX18=decode(H_TX18(1),null,H_TX18(1),H_TX18(1)||' ')
,TX19=decode(H_TX19(1),null,H_TX19(1),H_TX19(1)||' ')
,TX20=decode(H_TX20(1),null,H_TX20(1),H_TX20(1)||' ')
,TX21=decode(H_TX21(1),null,H_TX21(1),H_TX21(1)||' ')
,TX22=decode(H_TX22(1),null,H_TX22(1),H_TX22(1)||' ')
,TX24=decode(H_TX24(1),null,H_TX24(1),H_TX24(1)||' ')
,TX25=decode(H_TX25(1),null,H_TX25(1),H_TX25(1)||' ')
,TX26=decode(H_TX26(1),null,H_TX26(1),H_TX26(1)||' ')
,TX27=decode(H_TX27(1),null,H_TX27(1),H_TX27(1)||' ')
,TX28=decode(H_TX28(1),null,H_TX28(1),H_TX28(1)||' ')
,TX29=decode(H_TX29(1),null,H_TX29(1),H_TX29(1)||' ')
,TX30=decode(H_TX30(1),null,H_TX30(1),H_TX30(1)||' ')
WHERE PARTY_SITE_ID=H_PARTY_SITE_ID(1);
UPDATE HZ_STAGED_PARTIES set
D_PS = 'SYNC'
,CONCAT_COL = CONCAT_COL
WHERE PARTY_ID = H_PARTY_ID(1);
HZ_DQM_SYNC.insert_sh_interface_rec(l_party_id,p_party_site_id,null,l_org_contact_id,'PARTY_SITES',p_operation);
insert_dqm_sync_error_rec(l_party_id, p_party_site_id, NULL, l_org_contact_id, 'PARTY_SITES', p_operation, 'E', 'Y', l_sql_err_message);
SELECT p.PARTY_ID, p.STATUS, dsi.ROWID
,p.PARTY_NAME
,p.PARTY_NUMBER
,p.PARTY_TYPE
,p.PARTY_NAME || ' ' || p.KNOWN_AS || ' ' || p.KNOWN_AS2 || ' ' || p.KNOWN_AS3 || ' '|| p.KNOWN_AS4 || ' '|| p.KNOWN_AS5
,op.DUNS_NUMBER_C
,op.TAX_NAME
,op.TAX_REFERENCE
,op.JGZZ_FISCAL_CODE
,op.SIC_CODE
,op.SIC_CODE_TYPE
,p.CATEGORY_CODE
,p.REFERENCE_USE_FLAG
,op.CORPORATION_CLASS
FROM HZ_PARTIES p, HZ_ORGANIZATION_PROFILES op, HZ_DQM_SYNC_INTERFACE dsi
WHERE p.party_id = op.party_id
AND p.party_id = dsi.party_id
AND p.PARTY_TYPE = 'ORGANIZATION'
AND dsi.entity = 'PARTY'
AND dsi.staged_flag = 'N'
AND dsi.operation = p_operation
AND dsi.sync_interface_num >= p_from_rec
AND dsi.sync_interface_num <= p_to_rec
AND (p.status = 'M' or op.effective_end_date is NULL);
SELECT p.PARTY_ID, p.STATUS, dsi.ROWID
,p.PARTY_NAME
,p.PARTY_NUMBER
,p.PARTY_TYPE
,p.PARTY_NAME || ' ' || p.KNOWN_AS || ' ' || p.KNOWN_AS2 || ' ' || p.KNOWN_AS3 || ' '|| p.KNOWN_AS4 || ' '|| p.KNOWN_AS5
,NULL
,pe.TAX_NAME
,pe.TAX_REFERENCE
,pe.JGZZ_FISCAL_CODE
,NULL
,NULL
,p.CATEGORY_CODE
,p.REFERENCE_USE_FLAG
,NULL
FROM HZ_PARTIES p, HZ_PERSON_PROFILES pe, HZ_DQM_SYNC_INTERFACE dsi
WHERE p.party_id = pe.party_id
AND p.party_id = dsi.party_id
AND p.PARTY_TYPE = 'PERSON'
AND dsi.entity = 'PARTY'
AND dsi.staged_flag = 'N'
AND dsi.operation = p_operation
AND dsi.sync_interface_num >= p_from_rec
AND dsi.sync_interface_num <= p_to_rec
AND (p.status = 'M' or pe.effective_end_date is NULL);
SELECT p.PARTY_ID, p.STATUS, dsi.ROWID
,p.PARTY_NAME
,p.PARTY_NUMBER
,p.PARTY_TYPE
,p.PARTY_NAME || ' ' || p.KNOWN_AS || ' ' || p.KNOWN_AS2 || ' ' || p.KNOWN_AS3 || ' '|| p.KNOWN_AS4 || ' '|| p.KNOWN_AS5
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,p.CATEGORY_CODE
,p.REFERENCE_USE_FLAG
,NULL
FROM HZ_PARTIES p, HZ_DQM_SYNC_INTERFACE dsi
WHERE p.party_id = dsi.party_id
AND dsi.entity = 'PARTY'
AND dsi.staged_flag = 'N'
AND dsi.operation = p_operation
AND dsi.sync_interface_num >= p_from_rec
AND dsi.sync_interface_num <= p_to_rec
AND p.party_type <> 'PERSON'
AND p.party_type <> 'ORGANIZATION'
AND p.party_type <> 'PARTY_RELATIONSHIP';
log ('Inserting Data into HZ_STAGED_PARTIES...',FALSE);
INSERT INTO HZ_STAGED_PARTIES (
PARTY_ID
,STATUS
,D_PS
,D_CT
,D_CPT
,TX2
,TX4
,TX8
,TX19
,TX32
,TX33
,TX34
,TX35
,TX36
,TX39
,TX40
,TX41
,TX42
,TX43
,TX44
,TX45
,TX46
,TX47
,TX48
,TX59
,TX60
,TX61
,TX62
,TX63
,TX156
,TX157
,TX158
) VALUES (
H_P_PARTY_ID(I)
,H_STATUS(I)
,'SYNC'
,'SYNC'
,'SYNC'
,decode(H_TX2(I),null,H_TX2(I),H_TX2(I)||' ')
,decode(H_TX4(I),null,H_TX4(I),H_TX4(I)||' ')
,decode(H_TX8(I),null,H_TX8(I),H_TX8(I)||' ')
,decode(H_TX19(I),null,H_TX19(I),H_TX19(I)||' ')
,decode(H_TX32(I),null,H_TX32(I),H_TX32(I)||' ')
,decode(H_TX33(I),null,H_TX33(I),H_TX33(I)||' ')
,decode(H_TX34(I),null,H_TX34(I),H_TX34(I)||' ')
,decode(H_TX35(I),null,H_TX35(I),H_TX35(I)||' ')
,decode(H_TX36(I),null,H_TX36(I),H_TX36(I)||' ')
,decode(H_TX39(I),null,H_TX39(I),H_TX39(I)||' ')
,decode(H_TX40(I),null,H_TX40(I),H_TX40(I)||' ')
,decode(H_TX41(I),null,H_TX41(I),H_TX41(I)||' ')
,decode(H_TX42(I),null,H_TX42(I),H_TX42(I)||' ')
,decode(H_TX43(I),null,H_TX43(I),H_TX43(I)||' ')
,decode(H_TX44(I),null,H_TX44(I),H_TX44(I)||' ')
,decode(H_TX45(I),null,H_TX45(I),H_TX45(I)||' ')
,decode(H_TX46(I),null,H_TX46(I),H_TX46(I)||' ')
,decode(H_TX47(I),null,H_TX47(I),H_TX47(I)||' ')
,decode(H_TX48(I),null,H_TX48(I),H_TX48(I)||' ')
,decode(H_TX59(I),null,H_TX59(I),H_TX59(I)||' ')
,decode(H_TX60(I),null,H_TX60(I),H_TX60(I)||' ')
,decode(H_TX61(I),null,H_TX61(I),H_TX61(I)||' ')
,decode(H_TX62(I),null,H_TX62(I),H_TX62(I)||' ')
,decode(H_TX63(I),null,H_TX63(I),H_TX63(I)||' ')
,decode(H_TX156(I),null,H_TX156(I),H_TX156(I)||' ')
,decode(H_TX157(I),null,H_TX157(I),H_TX157(I)||' ')
,decode(H_TX158(I),null,H_TX158(I),H_TX158(I)||' ')
);
log ('Exception DUP_VAL_ON_INDEX occured while inserting Party with PARTY_ID - '||H_P_PARTY_ID(l_err_index));
DELETE FROM HZ_DQM_SYNC_INTERFACE WHERE ENTITY='PARTY' AND OPERATION='C' AND PARTY_ID=H_P_PARTY_ID(l_err_index);
UPDATE hz_dqm_sync_interface
SET error_data = l_sql_errm
,staged_flag = decode (error_data, NULL, 'N', 'E')
WHERE rowid = H_ROWID(l_err_index);
insert_dqm_sync_error_rec(H_P_PARTY_ID(l_err_index), NULL, NULL, NULL, 'PARTY', p_operation, 'E', 'N', l_sql_errm);
UPDATE HZ_STAGED_PARTIES SET
status =H_STATUS(I)
,concat_col = concat_col
,TX2=decode(H_TX2(I),null,H_TX2(I),H_TX2(I)||' ')
,TX4=decode(H_TX4(I),null,H_TX4(I),H_TX4(I)||' ')
,TX8=decode(H_TX8(I),null,H_TX8(I),H_TX8(I)||' ')
,TX19=decode(H_TX19(I),null,H_TX19(I),H_TX19(I)||' ')
,TX32=decode(H_TX32(I),null,H_TX32(I),H_TX32(I)||' ')
,TX33=decode(H_TX33(I),null,H_TX33(I),H_TX33(I)||' ')
,TX34=decode(H_TX34(I),null,H_TX34(I),H_TX34(I)||' ')
,TX35=decode(H_TX35(I),null,H_TX35(I),H_TX35(I)||' ')
,TX36=decode(H_TX36(I),null,H_TX36(I),H_TX36(I)||' ')
,TX39=decode(H_TX39(I),null,H_TX39(I),H_TX39(I)||' ')
,TX40=decode(H_TX40(I),null,H_TX40(I),H_TX40(I)||' ')
,TX41=decode(H_TX41(I),null,H_TX41(I),H_TX41(I)||' ')
,TX42=decode(H_TX42(I),null,H_TX42(I),H_TX42(I)||' ')
,TX43=decode(H_TX43(I),null,H_TX43(I),H_TX43(I)||' ')
,TX44=decode(H_TX44(I),null,H_TX44(I),H_TX44(I)||' ')
,TX45=decode(H_TX45(I),null,H_TX45(I),H_TX45(I)||' ')
,TX46=decode(H_TX46(I),null,H_TX46(I),H_TX46(I)||' ')
,TX47=decode(H_TX47(I),null,H_TX47(I),H_TX47(I)||' ')
,TX48=decode(H_TX48(I),null,H_TX48(I),H_TX48(I)||' ')
,TX59=decode(H_TX59(I),null,H_TX59(I),H_TX59(I)||' ')
,TX60=decode(H_TX60(I),null,H_TX60(I),H_TX60(I)||' ')
,TX61=decode(H_TX61(I),null,H_TX61(I),H_TX61(I)||' ')
,TX62=decode(H_TX62(I),null,H_TX62(I),H_TX62(I)||' ')
,TX63=decode(H_TX63(I),null,H_TX63(I),H_TX63(I)||' ')
,TX156=decode(H_TX156(I),null,H_TX156(I),H_TX156(I)||' ')
,TX157=decode(H_TX157(I),null,H_TX157(I),H_TX157(I)||' ')
,TX158=decode(H_TX158(I),null,H_TX158(I),H_TX158(I)||' ')
WHERE PARTY_ID = H_P_PARTY_ID(I);
log ('Exception DUP_VAL_ON_INDEX occured while inserting Party with PARTY_ID - '||H_P_PARTY_ID(l_err_index));
DELETE FROM HZ_DQM_SYNC_INTERFACE WHERE ENTITY='PARTY' AND OPERATION='U' AND PARTY_ID=H_P_PARTY_ID(l_err_index);
UPDATE hz_dqm_sync_interface
SET error_data = l_sql_errm
,staged_flag = decode (error_data, NULL, 'N', 'E')
WHERE rowid = H_ROWID(l_err_index);
insert_dqm_sync_error_rec(H_P_PARTY_ID(l_err_index), NULL, NULL, NULL, 'PARTY', p_operation, 'E', 'N', l_sql_errm);
SELECT
H_P_PARTY_ID(I) AS party_id
FROM dual ) T
ON (S.entity = 'PARTY' AND
S.party_id = T.party_id AND
S.staged_flag <> 'E')
WHEN NOT MATCHED THEN
INSERT (
PARTY_ID,
RECORD_ID,
PARTY_SITE_ID,
ORG_CONTACT_ID,
ENTITY,
OPERATION,
STAGED_FLAG,
REALTIME_SYNC_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
SYNC_INTERFACE_NUM
) VALUES (
H_P_PARTY_ID(I),
NULL,
NULL,
NULL,
'PARTY',
p_operation,
'N',
'N',
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,
HZ_DQM_SH_SYNC_INTERFACE_S.nextval
);
log ('Exception occured while inserting data into HZ_DQM_SH_SYNC_INTERFACE Table');
SELECT /*+ ORDERED USE_NL(ps l) */
ps.PARTY_SITE_ID
,dsi.party_id
,dsi.org_contact_id
,ps.status
,dsi.ROWID
,rtrim(l.address1 || ' ' || l.address2 || ' ' || l.address3 || ' ' || l.address4)
,l.CITY
,l.POSTAL_CODE
,l.PROVINCE
,l.STATE
,ps.PARTY_SITE_NUMBER
,ps.PARTY_SITE_NAME
,l.COUNTY
,l.COUNTRY
,ps.IDENTIFYING_ADDRESS_FLAG
,ps.STATUS
,l.ADDRESS1
FROM HZ_DQM_SYNC_INTERFACE dsi, HZ_PARTY_SITES ps, HZ_LOCATIONS l
WHERE dsi.record_id = ps.party_site_id
AND dsi.entity = 'PARTY_SITES'
AND dsi.operation = p_operation
AND dsi.staged_flag = 'N'
AND dsi.sync_interface_num >= p_from_rec
AND dsi.sync_interface_num <= p_to_rec
AND (ps.status is null OR ps.status = 'A' OR ps.status = 'I')
AND ps.location_id = l.location_id;
log ('Inserting Data into HZ_STAGED_PARTY_SITES...',FALSE);
INSERT INTO HZ_STAGED_PARTY_SITES (
PARTY_SITE_ID
,PARTY_ID
,ORG_CONTACT_ID
,STATUS_FLAG
,TX3
,TX4
,TX9
,TX10
,TX11
,TX12
,TX13
,TX14
,TX15
,TX17
,TX18
,TX19
,TX20
,TX21
,TX22
,TX24
,TX25
,TX26
,TX27
,TX28
,TX29
,TX30
) VALUES (
H_PARTY_SITE_ID(I)
,H_PS_PARTY_ID(I)
,H_PS_ORG_CONTACT_ID(I)
,H_STATUS(I)
,decode(H_TX3(I),null,H_TX3(I),H_TX3(I)||' ')
,decode(H_TX4(I),null,H_TX4(I),H_TX4(I)||' ')
,decode(H_TX9(I),null,H_TX9(I),H_TX9(I)||' ')
,decode(H_TX10(I),null,H_TX10(I),H_TX10(I)||' ')
,decode(H_TX11(I),null,H_TX11(I),H_TX11(I)||' ')
,decode(H_TX12(I),null,H_TX12(I),H_TX12(I)||' ')
,decode(H_TX13(I),null,H_TX13(I),H_TX13(I)||' ')
,decode(H_TX14(I),null,H_TX14(I),H_TX14(I)||' ')
,decode(H_TX15(I),null,H_TX15(I),H_TX15(I)||' ')
,decode(H_TX17(I),null,H_TX17(I),H_TX17(I)||' ')
,decode(H_TX18(I),null,H_TX18(I),H_TX18(I)||' ')
,decode(H_TX19(I),null,H_TX19(I),H_TX19(I)||' ')
,decode(H_TX20(I),null,H_TX20(I),H_TX20(I)||' ')
,decode(H_TX21(I),null,H_TX21(I),H_TX21(I)||' ')
,decode(H_TX22(I),null,H_TX22(I),H_TX22(I)||' ')
,decode(H_TX24(I),null,H_TX24(I),H_TX24(I)||' ')
,decode(H_TX25(I),null,H_TX25(I),H_TX25(I)||' ')
,decode(H_TX26(I),null,H_TX26(I),H_TX26(I)||' ')
,decode(H_TX27(I),null,H_TX27(I),H_TX27(I)||' ')
,decode(H_TX28(I),null,H_TX28(I),H_TX28(I)||' ')
,decode(H_TX29(I),null,H_TX29(I),H_TX29(I)||' ')
,decode(H_TX30(I),null,H_TX30(I),H_TX30(I)||' ')
);
log ('Exception DUP_VAL_ON_INDEX occured while inserting Party Site with PARTY_SITE_ID - '||H_PARTY_SITE_ID(l_err_index));
DELETE FROM HZ_DQM_SYNC_INTERFACE WHERE ENTITY='PARTY_SITES' AND OPERATION='C' AND RECORD_ID=H_PARTY_SITE_ID(l_err_index);
UPDATE hz_dqm_sync_interface
SET error_data = l_sql_errm
,staged_flag = decode (error_data, NULL, 'N', 'E')
WHERE rowid = H_ROWID(l_err_index);
insert_dqm_sync_error_rec(H_PS_PARTY_ID(l_err_index), H_PARTY_SITE_ID(l_err_index), NULL, H_PS_ORG_CONTACT_ID(l_err_index), 'PARTY_SITES', p_operation, 'E', 'N', l_sql_errm);
UPDATE HZ_STAGED_PARTY_SITES SET
concat_col = concat_col
,status_flag = H_STATUS(I)
,TX3=decode(H_TX3(I),null,H_TX3(I),H_TX3(I)||' ')
,TX4=decode(H_TX4(I),null,H_TX4(I),H_TX4(I)||' ')
,TX9=decode(H_TX9(I),null,H_TX9(I),H_TX9(I)||' ')
,TX10=decode(H_TX10(I),null,H_TX10(I),H_TX10(I)||' ')
,TX11=decode(H_TX11(I),null,H_TX11(I),H_TX11(I)||' ')
,TX12=decode(H_TX12(I),null,H_TX12(I),H_TX12(I)||' ')
,TX13=decode(H_TX13(I),null,H_TX13(I),H_TX13(I)||' ')
,TX14=decode(H_TX14(I),null,H_TX14(I),H_TX14(I)||' ')
,TX15=decode(H_TX15(I),null,H_TX15(I),H_TX15(I)||' ')
,TX17=decode(H_TX17(I),null,H_TX17(I),H_TX17(I)||' ')
,TX18=decode(H_TX18(I),null,H_TX18(I),H_TX18(I)||' ')
,TX19=decode(H_TX19(I),null,H_TX19(I),H_TX19(I)||' ')
,TX20=decode(H_TX20(I),null,H_TX20(I),H_TX20(I)||' ')
,TX21=decode(H_TX21(I),null,H_TX21(I),H_TX21(I)||' ')
,TX22=decode(H_TX22(I),null,H_TX22(I),H_TX22(I)||' ')
,TX24=decode(H_TX24(I),null,H_TX24(I),H_TX24(I)||' ')
,TX25=decode(H_TX25(I),null,H_TX25(I),H_TX25(I)||' ')
,TX26=decode(H_TX26(I),null,H_TX26(I),H_TX26(I)||' ')
,TX27=decode(H_TX27(I),null,H_TX27(I),H_TX27(I)||' ')
,TX28=decode(H_TX28(I),null,H_TX28(I),H_TX28(I)||' ')
,TX29=decode(H_TX29(I),null,H_TX29(I),H_TX29(I)||' ')
,TX30=decode(H_TX30(I),null,H_TX30(I),H_TX30(I)||' ')
WHERE PARTY_SITE_ID=H_PARTY_SITE_ID(I);
log ('Exception DUP_VAL_ON_INDEX occured while inserting Party Site with PARTY_SITE_ID - '||H_PARTY_SITE_ID(l_err_index));
DELETE FROM HZ_DQM_SYNC_INTERFACE WHERE ENTITY='PARTY_SITES' AND OPERATION='U' AND RECORD_ID=H_PARTY_SITE_ID(l_err_index);
UPDATE hz_dqm_sync_interface
SET error_data = l_sql_errm
,staged_flag = decode (error_data, NULL, 'N', 'E')
WHERE rowid = H_ROWID(l_err_index);
insert_dqm_sync_error_rec(H_PS_PARTY_ID(l_err_index), H_PARTY_SITE_ID(l_err_index), NULL, H_PS_ORG_CONTACT_ID(l_err_index), 'PARTY_SITES', p_operation, 'E', 'N', l_sql_errm);
UPDATE HZ_STAGED_PARTIES set
D_PS = 'SYNC'
,CONCAT_COL = CONCAT_COL
WHERE PARTY_ID = H_PS_PARTY_ID(I);
SELECT
H_PS_PARTY_ID(I) AS party_id
,H_PARTY_SITE_ID(I) AS record_id
,H_PS_ORG_CONTACT_ID(I) AS org_contact_id
FROM dual ) T
ON (S.entity = 'PARTY_SITES' AND
S.party_id = T.party_id AND
S.record_id = T.record_id AND
NVL(S.org_contact_id, -99) = NVL(T.org_contact_id, -99) AND
S.staged_flag <> 'E')
WHEN NOT MATCHED THEN
INSERT (
PARTY_ID,
RECORD_ID,
PARTY_SITE_ID,
ORG_CONTACT_ID,
ENTITY,
OPERATION,
STAGED_FLAG,
REALTIME_SYNC_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
SYNC_INTERFACE_NUM
) VALUES (
H_PS_PARTY_ID(I),
H_PARTY_SITE_ID(I),
NULL,
H_PS_ORG_CONTACT_ID(I),
'PARTY_SITES',
p_operation,
'N',
'N',
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,
HZ_DQM_SH_SYNC_INTERFACE_S.nextval
);
log ('Exception occured while inserting data into HZ_DQM_SH_SYNC_INTERFACE Table');
SELECT
/*+ leading(dsi) USE_NL(OC R PP) */
oc.ORG_CONTACT_ID
,r.OBJECT_ID
,r.PARTY_ID
,r.STATUS
,dsi.ROWID
,rtrim(pp.person_first_name || ' ' || pp.person_last_name)
,oc.CONTACT_NUMBER
,oc.JOB_TITLE
FROM HZ_DQM_SYNC_INTERFACE dsi, HZ_RELATIONSHIPS r,
HZ_ORG_CONTACTS oc, HZ_PERSON_PROFILES pp
WHERE oc.party_relationship_id = r.relationship_id
AND dsi.record_id = oc.org_contact_id
AND r.subject_id = pp.party_id
AND r.subject_type = 'PERSON'
AND r.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND r.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND DIRECTIONAL_FLAG = 'F'
AND pp.effective_end_date is NULL
AND dsi.entity = 'CONTACTS'
AND dsi.operation = p_operation
AND dsi.staged_flag = 'N'
AND dsi.sync_interface_num >= p_from_rec
AND dsi.sync_interface_num <= p_to_rec
AND (oc.status is null OR oc.status = 'A' or oc.status = 'I')
AND (r.status is null OR r.status = 'A' or r.status = 'I');
log ('Inserting Data into HZ_STAGED_CONTACTS...',FALSE);
INSERT INTO HZ_STAGED_CONTACTS (
ORG_CONTACT_ID
,PARTY_ID
,STATUS_FLAG
,TX2
,TX5
,TX6
,TX11
,TX22
,TX23
,TX24
,TX25
,TX156
) VALUES (
H_ORG_CONTACT_ID(I)
,H_C_PARTY_ID(I)
,H_STATUS(I)
,decode(H_TX2(I),null,H_TX2(I),H_TX2(I)||' ')
,decode(H_TX5(I),null,H_TX5(I),H_TX5(I)||' ')
,decode(H_TX6(I),null,H_TX6(I),H_TX6(I)||' ')
,decode(H_TX11(I),null,H_TX11(I),H_TX11(I)||' ')
,decode(H_TX22(I),null,H_TX22(I),H_TX22(I)||' ')
,decode(H_TX23(I),null,H_TX23(I),H_TX23(I)||' ')
,decode(H_TX24(I),null,H_TX24(I),H_TX24(I)||' ')
,decode(H_TX25(I),null,H_TX25(I),H_TX25(I)||' ')
,decode(H_TX156(I),null,H_TX156(I),H_TX156(I)||' ')
);
log ('Exception DUP_VAL_ON_INDEX occured while inserting a Contact with ORG_CONTACT_ID - '||H_ORG_CONTACT_ID(l_err_index));
DELETE FROM HZ_DQM_SYNC_INTERFACE WHERE ENTITY='CONTACTS' AND OPERATION='C' AND RECORD_ID=H_ORG_CONTACT_ID(l_err_index);
UPDATE hz_dqm_sync_interface
SET error_data = l_sql_errm
,staged_flag = decode (error_data, NULL, 'N', 'E')
WHERE rowid = H_ROWID(l_err_index);
insert_dqm_sync_error_rec(H_C_PARTY_ID(l_err_index), H_ORG_CONTACT_ID(l_err_index), NULL, NULL, 'CONTACTS', p_operation, 'E', 'N', l_sql_errm);
UPDATE HZ_STAGED_CONTACTS SET
concat_col = concat_col
,status_flag = H_STATUS(I)
,TX2=decode(H_TX2(I),null,H_TX2(I),H_TX2(I)||' ')
,TX5=decode(H_TX5(I),null,H_TX5(I),H_TX5(I)||' ')
,TX6=decode(H_TX6(I),null,H_TX6(I),H_TX6(I)||' ')
,TX11=decode(H_TX11(I),null,H_TX11(I),H_TX11(I)||' ')
,TX22=decode(H_TX22(I),null,H_TX22(I),H_TX22(I)||' ')
,TX23=decode(H_TX23(I),null,H_TX23(I),H_TX23(I)||' ')
,TX24=decode(H_TX24(I),null,H_TX24(I),H_TX24(I)||' ')
,TX25=decode(H_TX25(I),null,H_TX25(I),H_TX25(I)||' ')
,TX156=decode(H_TX156(I),null,H_TX156(I),H_TX156(I)||' ')
WHERE ORG_CONTACT_ID=H_ORG_CONTACT_ID(I);
log ('Exception DUP_VAL_ON_INDEX occured while inserting a Contact with ORG_CONTACT_ID - '||H_ORG_CONTACT_ID(l_err_index));
DELETE FROM HZ_DQM_SYNC_INTERFACE WHERE ENTITY='CONTACTS' AND OPERATION='U' AND RECORD_ID=H_ORG_CONTACT_ID(l_err_index);
UPDATE hz_dqm_sync_interface
SET error_data = l_sql_errm
,staged_flag = decode (error_data, NULL, 'N', 'E')
WHERE rowid = H_ROWID(l_err_index);
insert_dqm_sync_error_rec(H_C_PARTY_ID(l_err_index), H_ORG_CONTACT_ID(l_err_index), NULL, NULL, 'CONTACTS', p_operation, 'E', 'N', l_sql_errm);
UPDATE HZ_STAGED_PARTIES set
D_CT = 'SYNC'
,CONCAT_COL = CONCAT_COL
WHERE PARTY_ID = H_C_PARTY_ID(I);
SELECT
H_C_PARTY_ID(I) AS party_id
,H_ORG_CONTACT_ID(I) AS record_id
FROM dual ) T
ON (S.entity = 'CONTACTS' AND
S.party_id = T.party_id AND
S.record_id = T.record_id AND
S.staged_flag <> 'E')
WHEN NOT MATCHED THEN
INSERT (
PARTY_ID,
RECORD_ID,
PARTY_SITE_ID,
ORG_CONTACT_ID,
ENTITY,
OPERATION,
STAGED_FLAG,
REALTIME_SYNC_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
SYNC_INTERFACE_NUM
) VALUES (
H_C_PARTY_ID(I),
H_ORG_CONTACT_ID(I),
NULL,
NULL,
'CONTACTS',
p_operation,
'N',
'N',
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,
HZ_DQM_SH_SYNC_INTERFACE_S.nextval
);
log ('Exception occured while inserting data into HZ_DQM_SH_SYNC_INTERFACE Table');
SELECT
/*+ ORDERED USE_NL(cp) */
cp.CONTACT_POINT_ID
,dsi.party_id
,dsi.party_site_id
,dsi.org_contact_id
,cp.CONTACT_POINT_TYPE
,cp.STATUS
,dsi.ROWID
,translate(phone_number,'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz!"#$%&()''*+,-./:;<=>?@[\]^_`{|}~ ','0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ') || ' ' || translate(phone_area_code||' ' || phone_number,'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz!"#$%&()''*+,-./:;<=>?@[\]^_`{|}~ ','0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ') || ' ' || translate(phone_country_code|| ' ' || phone_area_code||' ' || phone_number,'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz!"#$%&()''*+,-./:;<=>?@[\]^_`{|}~ ','0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ')
log ('Inserting Data into HZ_STAGED_CONTACT_POINTS...',FALSE);
INSERT INTO HZ_STAGED_CONTACT_POINTS (
CONTACT_POINT_ID
,PARTY_ID
,PARTY_SITE_ID
,ORG_CONTACT_ID
,CONTACT_POINT_TYPE
,STATUS_FLAG
,TX1
,TX2
,TX3
,TX4
,TX5
,TX6
,TX7
,TX8
,TX9
,TX10
,TX11
,TX12
,TX13
,TX14
,TX158
) VALUES (
H_CONTACT_POINT_ID(I)
,H_CPT_PARTY_ID(I)
,H_CPT_PARTY_SITE_ID(I)
,H_CPT_ORG_CONTACT_ID(I)
,H_CONTACT_POINT_TYPE(I)
,H_STATUS(I)
,decode(H_TX1(I),null,H_TX1(I),H_TX1(I)||' ')
,decode(H_TX2(I),null,H_TX2(I),H_TX2(I)||' ')
,decode(H_TX3(I),null,H_TX3(I),H_TX3(I)||' ')
,decode(H_TX4(I),null,H_TX4(I),H_TX4(I)||' ')
,decode(H_TX5(I),null,H_TX5(I),H_TX5(I)||' ')
,decode(H_TX6(I),null,H_TX6(I),H_TX6(I)||' ')
,decode(H_TX7(I),null,H_TX7(I),H_TX7(I)||' ')
,decode(H_TX8(I),null,H_TX8(I),H_TX8(I)||' ')
,decode(H_TX9(I),null,H_TX9(I),H_TX9(I)||' ')
,decode(H_TX10(I),null,H_TX10(I),H_TX10(I)||' ')
,decode(H_TX11(I),null,H_TX11(I),H_TX11(I)||' ')
,decode(H_TX12(I),null,H_TX12(I),H_TX12(I)||' ')
,decode(H_TX13(I),null,H_TX13(I),H_TX13(I)||' ')
,decode(H_TX14(I),null,H_TX14(I),H_TX14(I)||' ')
,decode(H_TX158(I),null,H_TX158(I),H_TX158(I)||' ')
);
log ('Exception DUP_VAL_ON_INDEX occured while inserting a Contact Point with CONTACT_POINT_ID - '||H_CONTACT_POINT_ID(l_err_index));
DELETE FROM HZ_DQM_SYNC_INTERFACE WHERE ENTITY='CONTACT_POINTS' AND OPERATION='C' AND RECORD_ID=H_CONTACT_POINT_ID(l_err_index);
UPDATE hz_dqm_sync_interface
SET error_data = l_sql_errm
,staged_flag = decode (error_data, NULL, 'N', 'E')
WHERE rowid = H_ROWID(l_err_index);
insert_dqm_sync_error_rec(H_CPT_PARTY_ID(l_err_index), H_CONTACT_POINT_ID(l_err_index), H_CPT_PARTY_SITE_ID(l_err_index), H_CPT_ORG_CONTACT_ID(l_err_index), 'CONTACT_POINTS', p_operation, 'E', 'N', l_sql_errm);
UPDATE HZ_STAGED_CONTACT_POINTS SET
concat_col = concat_col
,status_flag = H_STATUS(I)
,TX1=decode(H_TX1(I),null,H_TX1(I),H_TX1(I)||' ')
,TX2=decode(H_TX2(I),null,H_TX2(I),H_TX2(I)||' ')
,TX3=decode(H_TX3(I),null,H_TX3(I),H_TX3(I)||' ')
,TX4=decode(H_TX4(I),null,H_TX4(I),H_TX4(I)||' ')
,TX5=decode(H_TX5(I),null,H_TX5(I),H_TX5(I)||' ')
,TX6=decode(H_TX6(I),null,H_TX6(I),H_TX6(I)||' ')
,TX7=decode(H_TX7(I),null,H_TX7(I),H_TX7(I)||' ')
,TX8=decode(H_TX8(I),null,H_TX8(I),H_TX8(I)||' ')
,TX9=decode(H_TX9(I),null,H_TX9(I),H_TX9(I)||' ')
,TX10=decode(H_TX10(I),null,H_TX10(I),H_TX10(I)||' ')
,TX11=decode(H_TX11(I),null,H_TX11(I),H_TX11(I)||' ')
,TX12=decode(H_TX12(I),null,H_TX12(I),H_TX12(I)||' ')
,TX13=decode(H_TX13(I),null,H_TX13(I),H_TX13(I)||' ')
,TX14=decode(H_TX14(I),null,H_TX14(I),H_TX14(I)||' ')
,TX158=decode(H_TX158(I),null,H_TX158(I),H_TX158(I)||' ')
WHERE CONTACT_POINT_ID=H_CONTACT_POINT_ID(I);
log ('Exception DUP_VAL_ON_INDEX occured while inserting a Contact Point with CONTACT_POINT_ID - '||H_CONTACT_POINT_ID(l_err_index));
DELETE FROM HZ_DQM_SYNC_INTERFACE WHERE ENTITY='CONTACT_POINTS' AND OPERATION='U' AND RECORD_ID=H_CONTACT_POINT_ID(l_err_index);
UPDATE hz_dqm_sync_interface
SET error_data = l_sql_errm
,staged_flag = decode (error_data, NULL, 'N', 'E')
WHERE rowid = H_ROWID(l_err_index);
insert_dqm_sync_error_rec(H_CPT_PARTY_ID(l_err_index), H_CONTACT_POINT_ID(l_err_index), H_CPT_PARTY_SITE_ID(l_err_index), H_CPT_ORG_CONTACT_ID(l_err_index), 'CONTACT_POINTS', p_operation, 'E', 'N', l_sql_errm);
UPDATE HZ_STAGED_PARTIES set
D_CPT = 'SYNC'
,CONCAT_COL = CONCAT_COL
WHERE PARTY_ID = H_CPT_PARTY_ID(I);
SELECT
H_CPT_PARTY_ID(I) AS party_id
,H_CONTACT_POINT_ID(I) AS record_id
,H_CPT_PARTY_SITE_ID(I) AS party_site_id
,H_CPT_ORG_CONTACT_ID(I) AS org_contact_id
FROM dual ) T
ON (S.entity = 'CONTACT_POINTS' AND
S.party_id = T.party_id AND
S.record_id = T.record_id AND
NVL(S.party_site_id, -99) = NVL(T.party_site_id, -99) AND
NVL(S.org_contact_id, -99) = NVL(T.org_contact_id, -99) AND
S.staged_flag <> 'E')
WHEN NOT MATCHED THEN
INSERT (
PARTY_ID,
RECORD_ID,
PARTY_SITE_ID,
ORG_CONTACT_ID,
ENTITY,
OPERATION,
STAGED_FLAG,
REALTIME_SYNC_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
SYNC_INTERFACE_NUM
) VALUES (
H_CPT_PARTY_ID(I),
H_CONTACT_POINT_ID(I),
H_CPT_PARTY_SITE_ID(I),
H_CPT_ORG_CONTACT_ID(I),
'CONTACT_POINTS',
p_operation,
'N',
'N',
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,
HZ_DQM_SH_SYNC_INTERFACE_S.nextval
);
log ('Exception occured while inserting data into HZ_DQM_SH_SYNC_INTERFACE Table');
SELECT p.PARTY_ID, p.STATUS, p.ROWID
,p.PARTY_NAME
,p.PARTY_NUMBER
,p.PARTY_TYPE
,p.PARTY_NAME || ' ' || p.KNOWN_AS || ' ' || p.KNOWN_AS2 || ' ' || p.KNOWN_AS3 || ' '|| p.KNOWN_AS4 || ' '|| p.KNOWN_AS5
,op.DUNS_NUMBER_C
,op.TAX_NAME
,op.TAX_REFERENCE
,op.JGZZ_FISCAL_CODE
,op.SIC_CODE
,op.SIC_CODE_TYPE
,p.CATEGORY_CODE
,p.REFERENCE_USE_FLAG
,op.CORPORATION_CLASS
FROM HZ_PARTIES p, HZ_IMP_PARTIES_SG ps, HZ_IMP_BATCH_DETAILS bd
,HZ_ORGANIZATION_PROFILES op
WHERE p.request_id = bd.main_conc_req_id
AND bd.batch_id = ps.batch_id
AND p.PARTY_TYPE = 'ORGANIZATION'
AND p.party_id = ps.party_id
AND ps.batch_id = p_batch_id
AND ps.party_orig_system = p_os
AND ps.batch_mode_flag = p_batch_mode_flag
AND ps.action_flag = p_operation
AND p.party_id = op.party_id
AND ps.party_orig_system_reference BETWEEN p_from_osr AND p_to_osr
AND (p.status = 'M' OR op.effective_end_date IS NULL);
SELECT p.PARTY_ID, p.STATUS, p.ROWID
,p.PARTY_NAME
,p.PARTY_NUMBER
,p.PARTY_TYPE
,p.PARTY_NAME || ' ' || p.KNOWN_AS || ' ' || p.KNOWN_AS2 || ' ' || p.KNOWN_AS3 || ' '|| p.KNOWN_AS4 || ' '|| p.KNOWN_AS5
,NULL
,pe.TAX_NAME
,pe.TAX_REFERENCE
,pe.JGZZ_FISCAL_CODE
,NULL
,NULL
,p.CATEGORY_CODE
,p.REFERENCE_USE_FLAG
,NULL
FROM HZ_PARTIES p, HZ_IMP_PARTIES_SG ps, HZ_IMP_BATCH_DETAILS bd
,HZ_PERSON_PROFILES pe
WHERE p.request_id = bd.main_conc_req_id
AND bd.batch_id = ps.batch_id
AND p.PARTY_TYPE = 'PERSON'
AND p.party_id = ps.party_id
AND ps.batch_id = p_batch_id
AND ps.party_orig_system = p_os
AND ps.batch_mode_flag = p_batch_mode_flag
AND ps.action_flag = p_operation
AND p.party_id = pe.party_id
AND ps.party_orig_system_reference BETWEEN p_from_osr AND p_to_osr
AND (p.status = 'M' OR pe.effective_end_date IS NULL);
SELECT p.PARTY_ID, p.STATUS, p.ROWID
,p.PARTY_NAME
,p.PARTY_NUMBER
,p.PARTY_TYPE
,p.PARTY_NAME || ' ' || p.KNOWN_AS || ' ' || p.KNOWN_AS2 || ' ' || p.KNOWN_AS3 || ' '|| p.KNOWN_AS4 || ' '|| p.KNOWN_AS5
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,p.CATEGORY_CODE
,p.REFERENCE_USE_FLAG
,NULL
FROM HZ_PARTIES p, HZ_IMP_PARTIES_SG ps, HZ_IMP_BATCH_DETAILS bd
WHERE p.request_id = bd.main_conc_req_id
AND bd.batch_id = ps.batch_id
AND p.party_id = ps.party_id
AND ps.batch_id = p_batch_id
AND ps.party_orig_system = p_os
AND ps.batch_mode_flag = p_batch_mode_flag
AND ps.action_flag = p_operation
AND p.party_type <> 'PERSON'
AND p.party_type <> 'ORGANIZATION'
AND p.party_type <> 'PARTY_RELATIONSHIP'
AND ps.party_orig_system_reference between p_from_osr and p_to_osr;
SELECT /*+ ORDERED USE_NL(ps l) */
ps.PARTY_SITE_ID
,ps.PARTY_ID
,NULL
,ps.STATUS
,ps.ROWID
,rtrim(l.address1 || ' ' || l.address2 || ' ' || l.address3 || ' ' || l.address4)
,l.CITY
,l.POSTAL_CODE
,l.PROVINCE
,l.STATE
,ps.PARTY_SITE_NUMBER
,ps.PARTY_SITE_NAME
,l.COUNTY
,l.COUNTRY
,ps.IDENTIFYING_ADDRESS_FLAG
,ps.STATUS
,l.ADDRESS1
FROM hz_locations l, hz_party_sites ps,
hz_imp_addresses_sg addr_sg, hz_imp_batch_details bd
WHERE l.request_id = bd.main_conc_req_id
AND bd.batch_id = addr_sg.batch_id
AND l.location_id = ps.location_id
AND addr_sg.batch_id = p_batch_id
AND addr_sg.batch_mode_flag = p_batch_mode_flag
AND addr_sg.party_orig_system = p_os
AND addr_sg.party_site_id = ps.party_site_id
AND addr_sg.action_flag = p_operation
AND addr_sg.party_orig_system_reference BETWEEN p_from_osr AND p_to_osr
AND (ps.status IS NULL OR ps.status = 'A' OR ps.status = 'I');
SELECT
/*+ ORDERED USE_NL(R OC PP)*/
oc.ORG_CONTACT_ID
,r.OBJECT_ID
,r.PARTY_ID
,r.STATUS
,oc.ROWID
,rtrim(pp.person_first_name || ' ' || pp.person_last_name)
,oc.CONTACT_NUMBER
,oc.JOB_TITLE
FROM hz_org_contacts oc, hz_imp_contacts_sg ocsg, hz_imp_batch_details bd,
hz_relationships r, hz_person_profiles pp
WHERE ocsg.batch_mode_flag = p_batch_mode_flag
AND oc.party_relationship_id = r.relationship_id
AND ocsg.batch_id = p_batch_id
AND ocsg.sub_orig_system = p_os
AND ocsg.contact_id = oc.org_contact_id
AND oc.request_id = bd.main_conc_req_id
AND bd.batch_id = ocsg.batch_id
AND r.subject_id = pp.party_id
AND r.subject_type = 'PERSON'
AND r.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND r.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND DIRECTIONAL_FLAG = 'F'
AND ocsg.action_flag = p_operation
AND pp.effective_end_date IS NULL
AND ocsg.sub_orig_system_reference BETWEEN p_from_osr AND p_to_osr
AND (oc.status IS NULL OR oc.status = 'A' OR oc.status = 'I')
AND (r.status IS NULL OR r.status = 'A' OR r.status = 'I')
UNION
SELECT
/*+ ORDERED USE_NL(R OC PP)*/
oc.ORG_CONTACT_ID
,r.OBJECT_ID
,r.PARTY_ID
,r.STATUS
,oc.ROWID
,rtrim(pp.person_first_name || ' ' || pp.person_last_name)
,oc.CONTACT_NUMBER
,oc.JOB_TITLE
FROM hz_org_contacts oc, hz_imp_relships_sg rsg, hz_imp_batch_details bd
,hz_relationships r, hz_person_profiles pp
WHERE rsg.batch_mode_flag = p_batch_mode_flag
AND rsg.batch_id = p_batch_id
AND rsg.sub_orig_system = p_os
AND rsg.relationship_id = oc.party_relationship_id
AND oc.request_id = bd.main_conc_req_id
AND bd.batch_id = rsg.batch_id
AND rsg.relationship_id = r.relationship_id
AND r.directional_flag = 'F'
AND r.subject_id = pp.party_id
AND r.subject_type = 'PERSON'
AND r.object_type = 'ORGANIZATION'
AND r.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND r.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND rsg.action_flag = p_operation
AND pp.effective_end_date IS NULL
AND rsg.sub_orig_system_reference BETWEEN p_from_osr AND p_to_osr
AND (oc.status IS NULL OR oc.status = 'A' OR oc.status = 'I')
AND (r.status IS NULL OR r.status = 'A' OR r.status = 'I');
SELECT
/*+ ORDERED USE_NL(cp) */
cp.CONTACT_POINT_ID
,cps.party_id
,decode (cp.owner_table_name, 'HZ_PARTY_SITES', cp.owner_table_id, NULL) party_site_id
,NULL
,cp.CONTACT_POINT_TYPE
,cp.STATUS
,cp.ROWID
,translate(phone_number,'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz!"#$%&()''*+,-./:;<=>?@[\]^_`{|}~ ','0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ') || ' ' || translate(phone_area_code||' ' || phone_number,'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz!"#$%&()''*+,-./:;<=>?@[\]^_`{|}~ ','0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ') || ' ' || translate(phone_country_code|| ' ' || phone_area_code||' ' || phone_number,'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz!"#$%&()''*+,-./:;<=>?@[\]^_`{|}~ ','0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ')