[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT distinct CUSTOMER_MERGE_HEADER_ID
,AWARD_ID
,BILL_TO_ADDRESS_ID
,SHIP_TO_ADDRESS_ID
,LOC_BILL_TO_ADDRESS_ID
,LOC_SHIP_TO_ADDRESS_ID
,BILL_TO_CUSTOMER_ID
,FUNDING_SOURCE_ID
FROM GMS_AWARDS yt, ra_customer_merges m
WHERE (
yt.BILL_TO_ADDRESS_ID = m.DUPLICATE_ADDRESS_ID
OR yt.SHIP_TO_ADDRESS_ID = m.DUPLICATE_ADDRESS_ID
OR yt.LOC_BILL_TO_ADDRESS_ID = m.DUPLICATE_ADDRESS_ID
OR yt.LOC_SHIP_TO_ADDRESS_ID = m.DUPLICATE_ADDRESS_ID
OR yt.BILL_TO_CUSTOMER_ID = m.DUPLICATE_ID
OR yt.FUNDING_SOURCE_ID = m.DUPLICATE_ID
) AND m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num;
INSERT INTO HZ_CUSTOMER_MERGE_LOG (
MERGE_LOG_ID,
TABLE_NAME,
MERGE_HEADER_ID,
PRIMARY_KEY_ID,
NUM_COL1_ORIG,
NUM_COL1_NEW,
NUM_COL2_ORIG,
NUM_COL2_NEW,
NUM_COL3_ORIG,
NUM_COL3_NEW,
NUM_COL4_ORIG,
NUM_COL4_NEW,
NUM_COL5_ORIG,
NUM_COL5_NEW,
NUM_COL6_ORIG,
NUM_COL6_NEW,
ACTION_FLAG,
REQUEST_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
'GMS_AWARDS',
MERGE_HEADER_ID_LIST(I),
PRIMARY_KEY_ID_LIST(I),
NUM_COL1_ORIG_LIST(I),
NUM_COL1_NEW_LIST(I),
NUM_COL2_ORIG_LIST(I),
NUM_COL2_NEW_LIST(I),
NUM_COL3_ORIG_LIST(I),
NUM_COL3_NEW_LIST(I),
NUM_COL4_ORIG_LIST(I),
NUM_COL4_NEW_LIST(I),
NUM_COL5_ORIG_LIST(I),
NUM_COL5_NEW_LIST(I),
NUM_COL6_ORIG_LIST(I),
NUM_COL6_NEW_LIST(I),
'U',
req_id,
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.LAST_UPDATED_BY
);
UPDATE GMS_AWARDS yt SET
BILL_TO_ADDRESS_ID=NUM_COL1_NEW_LIST(I)
,SHIP_TO_ADDRESS_ID=NUM_COL2_NEW_LIST(I)
,LOC_BILL_TO_ADDRESS_ID=NUM_COL3_NEW_LIST(I)
,LOC_SHIP_TO_ADDRESS_ID=NUM_COL4_NEW_LIST(I)
,BILL_TO_CUSTOMER_ID=NUM_COL5_NEW_LIST(I)
,FUNDING_SOURCE_ID=NUM_COL6_NEW_LIST(I)
, LAST_UPDATE_DATE=SYSDATE
, last_updated_by=arp_standard.profile.user_id
, last_update_login=arp_standard.profile.last_update_login
WHERE AWARD_ID=PRIMARY_KEY_ID_LIST(I)
;
arp_message.set_name('AR','AR_ROWS_UPDATED');
SELECT distinct CUSTOMER_MERGE_HEADER_ID
,yt.AWARD_ID
,yt.CUSTOMER_ID
,yt.CONTACT_ID
,yt.USAGE_CODE
,yt.CUSTOMER_ID
FROM GMS_AWARDS_CONTACTS yt, ra_customer_merges m
WHERE (
yt.CUSTOMER_ID = m.DUPLICATE_ID
) AND m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num;
INSERT INTO HZ_CUSTOMER_MERGE_LOG (
MERGE_LOG_ID,
TABLE_NAME,
MERGE_HEADER_ID,
PRIMARY_KEY1,
PRIMARY_KEY2,
PRIMARY_KEY3,
PRIMARY_KEY4,
NUM_COL1_ORIG,
NUM_COL1_NEW,
ACTION_FLAG,
REQUEST_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
'GMS_AWARDS_CONTACTS',
MERGE_HEADER_ID_LIST(I),
PRIMARY_KEY1_LIST(I),
PRIMARY_KEY2_LIST(I),
PRIMARY_KEY3_LIST(I),
PRIMARY_KEY4_LIST(I),
NUM_COL1_ORIG_LIST(I),
NUM_COL1_NEW_LIST(I),
'U',
req_id,
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.LAST_UPDATED_BY
);
UPDATE GMS_AWARDS_CONTACTS yt SET
CUSTOMER_ID=NUM_COL1_NEW_LIST(I)
, LAST_UPDATE_DATE=SYSDATE
, last_updated_by=arp_standard.profile.user_id
, last_update_login=arp_standard.profile.last_update_login
WHERE AWARD_ID=PRIMARY_KEY1_LIST(I)
AND CUSTOMER_ID=PRIMARY_KEY2_LIST(I)
AND CONTACT_ID=PRIMARY_KEY3_LIST(I)
AND USAGE_CODE=PRIMARY_KEY4_LIST(I)
;
arp_message.set_name('AR','AR_ROWS_UPDATED');
SELECT distinct CUSTOMER_MERGE_HEADER_ID
,REPORT_ID
,SITE_USE_ID
FROM GMS_REPORTS yt, ra_customer_merges m
WHERE (
yt.SITE_USE_ID = m.DUPLICATE_SITE_ID
) AND m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num;
INSERT INTO HZ_CUSTOMER_MERGE_LOG (
MERGE_LOG_ID,
TABLE_NAME,
MERGE_HEADER_ID,
PRIMARY_KEY_ID,
NUM_COL1_ORIG,
NUM_COL1_NEW,
ACTION_FLAG,
REQUEST_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
'GMS_REPORTS',
MERGE_HEADER_ID_LIST(I),
PRIMARY_KEY_ID_LIST(I),
NUM_COL1_ORIG_LIST(I),
NUM_COL1_NEW_LIST(I),
'U',
req_id,
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.LAST_UPDATED_BY
);
UPDATE GMS_REPORTS yt SET
SITE_USE_ID=NUM_COL1_NEW_LIST(I)
, LAST_UPDATE_DATE=SYSDATE
, last_updated_by=arp_standard.profile.user_id
, last_update_login=arp_standard.profile.last_update_login
WHERE REPORT_ID=PRIMARY_KEY_ID_LIST(I)
;
arp_message.set_name('AR','AR_ROWS_UPDATED');
SELECT distinct CUSTOMER_MERGE_HEADER_ID
,DEFAULT_REPORT_ID
,SITE_USE_ID
FROM GMS_DEFAULT_REPORTS yt, ra_customer_merges m
WHERE (
yt.SITE_USE_ID = m.DUPLICATE_SITE_ID
) AND m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num;
INSERT INTO HZ_CUSTOMER_MERGE_LOG (
MERGE_LOG_ID,
TABLE_NAME,
MERGE_HEADER_ID,
PRIMARY_KEY_ID,
NUM_COL1_ORIG,
NUM_COL1_NEW,
ACTION_FLAG,
REQUEST_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
'GMS_DEFAULT_REPORTS',
MERGE_HEADER_ID_LIST(I),
PRIMARY_KEY_ID_LIST(I),
NUM_COL1_ORIG_LIST(I),
NUM_COL1_NEW_LIST(I),
'U',
req_id,
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.LAST_UPDATED_BY
);
UPDATE GMS_DEFAULT_REPORTS yt SET
SITE_USE_ID=NUM_COL1_NEW_LIST(I)
, LAST_UPDATE_DATE=SYSDATE
, last_updated_by=arp_standard.profile.user_id
, last_update_login=arp_standard.profile.last_update_login
WHERE DEFAULT_REPORT_ID=PRIMARY_KEY_ID_LIST(I)
;
arp_message.set_name('AR','AR_ROWS_UPDATED');