[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT duplicate_id, customer_id,
duplicate_address_id, customer_address_id,
duplicate_site_id, customer_site_id
BULK COLLECT INTO l_duplicateIdTab, l_customerIdTab,
l_duplicateAddressIdTab, l_customerAddressIdTab,
l_duplicateSiteIdTab, l_customerSiteIdTab
FROM ra_customer_merges
WHERE process_flag = 'N'
AND request_id = Req_Id
AND set_number = Set_Num;
SELECT ck.cum_key_id,
ck.cum_note_text,
m.duplicate_address_id,
m.customer_address_id
FROM RLM_CUST_ITEM_CUM_KEYS ck,
RA_CUSTOMER_MERGES m
WHERE m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num
AND (ck.intrmd_ship_to_id = m.duplicate_address_id
OR ck.ship_to_address_id = m.duplicate_address_id
OR ck.bill_to_address_id = m.duplicate_address_id)
FOR update of ck.intrmd_ship_to_id,
ck.ship_to_address_id,
ck.bill_to_address_id,
ck.cum_note_text
nowait;
setARMessageUpdateTable('RLM_CUST_ITEM_CUM_KEYS');
UPDATE RLM_CUST_ITEM_CUM_KEYS
SET intrmd_ship_to_id = DECODE(intrmd_ship_to_id,
l_duplicateAddressIdTab(i),
l_customerAddressIdTab(i),
intrmd_ship_to_id
),
ship_to_address_id = DECODE(ship_to_address_id,
l_duplicateAddressIdTab(i),
l_customerAddressIdTab(i),
ship_to_address_id
),
bill_to_address_id = DECODE(bill_to_address_id,
l_duplicateAddressIdTab(i),
l_customerAddressIdTab(i),
bill_to_address_id
),
inactive_flag = 'Y',
cum_note_text = l_cumNotesTab(i),
last_update_date = SYSDATE,
last_updated_by = arp_standard.profile.user_id,
last_update_login = arp_standard.profile.last_update_login,
request_id = req_id,
program_application_id = arp_standard.profile.program_application_id,
program_id = arp_standard.profile.program_id,
program_update_date = SYSDATE
WHERE cum_key_id = l_cumKeyIdTab(i);
SELECT ih.header_id,
ih.header_note_text,
m.duplicate_id,
m.customer_id,
m.duplicate_address_id,
m.customer_address_id,
--bug 2171856
m.customer_name
FROM RLM_INTERFACE_HEADERS ih,
RA_CUSTOMER_MERGES m
WHERE m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num
AND (ih.customer_id = m.duplicate_id
OR ih.ece_primary_address_id = m.duplicate_address_id)
FOR update of ih.header_id,
ih.customer_id,
ih.ece_primary_address_id,
ih.header_note_text
nowait;
setARMessageUpdateTable('RLM_INTERFACE_HEADERS');
UPDATE RLM_INTERFACE_HEADERS
SET customer_id = DECODE(customer_id,
l_duplicateIdTab(i),
l_customerIdTab(i),
customer_id
),
cust_name_ext = DECODE(customer_id,
l_duplicateIdTab(i),
l_customerNameTab(i),
cust_name_ext
),
ece_primary_address_id = DECODE(ece_primary_address_id,
l_duplicateAddressIdTab(i),
l_customerAddressIdTab(i),
ece_primary_address_id
),
header_note_text = l_headerNotesTab(i),
last_update_date = SYSDATE,
last_updated_by = arp_standard.profile.user_id,
last_update_login = arp_standard.profile.last_update_login,
request_id = req_id,
program_application_id = arp_standard.profile.program_application_id,
program_id = arp_standard.profile.program_id,
program_update_date = SYSDATE
WHERE header_id = l_headerIdTab(i);
SELECT il.line_id,
il.item_note_text,
m.duplicate_address_id,
m.customer_address_id,
m.duplicate_site_id,
m.customer_site_id,
m.duplicate_id,
m.customer_id
FROM RLM_INTERFACE_LINES il,
RA_CUSTOMER_MERGES m
WHERE m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num
AND (il.bill_to_address_id = m.duplicate_address_id
OR il.intrmd_ship_to_id = m.duplicate_address_id
OR il.ship_to_address_id = m.duplicate_address_id
OR il.ship_to_customer_id = m.duplicate_id)
FOR update of il.bill_to_address_id,
il.intrmd_ship_to_id,
il.ship_to_address_id,
il.item_note_text,
il.ship_to_customer_id
nowait;
setARMessageUpdateTable('RLM_INTERFACE_LINES');
UPDATE RLM_INTERFACE_LINES
SET
ship_to_customer_id = DECODE(ship_to_customer_id,
l_duplicateIdTab(i),
l_ShiptoCustomerIdTab(i),
ship_to_customer_id
),
invoice_to_org_id = DECODE(invoice_to_org_id,
l_duplicateSiteIdTab(i),
l_customerSiteIdTab(i),
invoice_to_org_id
),
bill_to_address_id = DECODE(bill_to_address_id,
l_duplicateAddressIdTab(i),
l_customerAddressIdTab(i),
bill_to_address_id
),
intmed_ship_to_org_id = DECODE(intmed_ship_to_org_id,
l_duplicateSiteIdTab(i),
l_customerSiteIdTab(i),
intmed_ship_to_org_id
),
intrmd_ship_to_id = DECODE(intrmd_ship_to_id,
l_duplicateAddressIdTab(i),
l_customerAddressIdTab(i),
intrmd_ship_to_id
),
ship_to_address_id = DECODE(ship_to_address_id,
l_duplicateAddressIdTab(i),
l_customerAddressIdTab(i),
ship_to_address_id
),
ship_to_org_id = DECODE(ship_to_org_id,
l_duplicateSiteIdTab(i),
l_customerSiteIdTab(i),
ship_to_org_id
),
item_note_text = l_lineNotesTab(i),
last_update_date = SYSDATE,
last_updated_by = arp_standard.profile.user_id,
last_update_login = arp_standard.profile.last_update_login,
request_id = req_id,
program_application_id= arp_standard.profile.program_application_id,
program_id = arp_standard.profile.program_id,
program_update_date = SYSDATE
WHERE line_id = l_lineIdTab(i);
SELECT sh.header_id,
sh.header_note_text,
m.duplicate_id,
m.customer_id,
m.duplicate_address_id,
m.customer_address_id,
m.customer_name
FROM RLM_SCHEDULE_HEADERS sh,
RA_CUSTOMER_MERGES m
WHERE m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num
AND (sh.customer_id = m.duplicate_id
OR sh.ece_primary_address_id = m.duplicate_address_id)
FOR update of sh.header_id,
sh.customer_id,
sh.ece_primary_address_id,
sh.header_note_text nowait;
setARMessageUpdateTable('RLM_SCHEDULE_HEADERS');
UPDATE RLM_SCHEDULE_HEADERS
SET customer_id = DECODE(customer_id,
l_duplicateIdTab(i),
l_customerIdTab(i),
customer_id
),
cust_name_ext = DECODE(customer_id,
l_duplicateIdTab(i),
l_customerNameTab(i),
cust_name_ext
),
ece_primary_address_id= DECODE(ece_primary_address_id,
l_duplicateAddressIdTab(i),
l_customerAddressIdTab(i),
ece_primary_address_id
),
header_note_text = l_headerNotesTab(i),
last_update_date = SYSDATE,
last_updated_by = arp_standard.profile.user_id,
last_update_login = arp_standard.profile.last_update_login,
request_id = req_id,
program_application_id= arp_standard.profile.program_application_id,
program_id = arp_standard.profile.program_id,
program_update_date = SYSDATE
WHERE header_id = l_headerIdTab(i);
SELECT il.line_id,
il.item_note_text,
m.duplicate_address_id,
m.customer_address_id,
m.duplicate_site_id,
m.customer_site_id,
m.duplicate_id,
m.customer_id
FROM RLM_SCHEDULE_LINES il,
RA_CUSTOMER_MERGES m
WHERE m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num
AND (il.bill_to_address_id = m.duplicate_address_id
OR il.intrmd_ship_to_id = m.duplicate_address_id
OR il.ship_to_address_id = m.duplicate_address_id
OR il.ship_to_customer_id = m.duplicate_id)
FOR update of il.bill_to_address_id,
il.intrmd_ship_to_id,
il.ship_to_address_id,
il.item_note_text,
il.ship_to_customer_id
nowait;
setARMessageUpdateTable('RLM_SCHEDULE_LINES');
UPDATE RLM_SCHEDULE_LINES
SET invoice_to_org_id = DECODE(invoice_to_org_id,
l_duplicateSiteIdTab(i),
l_customerSiteIdTab(i),
invoice_to_org_id
),
ship_to_customer_id = DECODE(ship_to_customer_id,
l_duplicateIdTab(i),
l_customerShiptoIdTab(i),
ship_to_customer_id
),
bill_to_address_id = DECODE(bill_to_address_id,
l_duplicateAddressIdTab(i),
l_customerAddressIdTab(i),
bill_to_address_id
),
intmed_ship_to_org_id = DECODE(intmed_ship_to_org_id,
l_duplicateSiteIdTab(i),
l_customerSiteIdTab(i),
intmed_ship_to_org_id
),
intrmd_ship_to_id = DECODE(intrmd_ship_to_id,
l_duplicateAddressIdTab(i),
l_customerAddressIdTab(i),
intrmd_ship_to_id
),
ship_to_address_id = DECODE(ship_to_address_id,
l_duplicateAddressIdTab(i),
l_customerAddressIdTab(i),
ship_to_address_id
),
ship_to_org_id = DECODE(ship_to_org_id,
l_duplicateSiteIdTab(i),
l_customerSiteIdTab(i),
ship_to_org_id
),
item_note_text = l_lineNotesTab(i),
last_update_date = SYSDATE,
last_updated_by = arp_standard.profile.user_id,
last_update_login = arp_standard.profile.last_update_login,
request_id = req_id,
program_application_id= arp_standard.profile.program_application_id,
program_id = arp_standard.profile.program_id,
program_update_date = SYSDATE
WHERE line_id = l_lineIdTab(i);
SELECT address_id,
customer_id
FROM RLM_CUST_SHIPTO_TERMS
WHERE customer_id in
(select m.duplicate_id
from ra_customer_merges m
where m.process_flag = 'N'
and m.request_id = req_id
and m.set_number = set_num)
OR address_id in
(select m.duplicate_address_id
from ra_customer_merges m
where m.process_flag = 'N'
and m.request_id = req_id
and m.set_number = set_num)
FOR update nowait;
SELECT distinct CUSTOMER_MERGE_HEADER_ID
FROM rlm_cust_shipto_terms 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;
SELECT * from rlm_cust_shipto_terms
where customer_id in
(select m.duplicate_id
from ra_customer_merges m
where m.process_flag = 'N'
and m.request_id = req_id
and m.set_number = set_num)
OR address_id in
(select m.duplicate_address_id
from ra_customer_merges m
where m.process_flag = 'N'
and m.request_id = req_id
and m.set_number = set_num)
FOR update nowait;
INSERT INTO HZ_CUSTOMER_MERGE_LOG (
MERGE_LOG_ID,
TABLE_NAME,
MERGE_HEADER_ID,
DEL_COL1,
DEL_COL2,
DEL_COL3 ,
DEL_COL4 ,
DEL_COL5,
DEL_COL6,
DEL_COL7,
DEL_COL8,
DEL_COL9,
DEL_COL10,
DEL_COL11,
DEL_COL12,
DEL_COL13,
DEL_COL14,
DEL_COL15,
DEL_COL16,
DEL_COL17,
DEL_COL18,
DEL_COL19,
DEL_COL20,
DEL_COL21,
DEL_COL22,
DEL_COL23,
DEL_COL24,
DEL_COL25,
DEL_COL26,
DEL_COL27,
DEL_COL28,
DEL_COL29,
DEL_COL30,
DEL_COL31,
DEL_COL32,
DEL_COL33,
DEL_COL34 ,
DEL_COL35,
DEL_COL36,
DEL_COL37,
DEL_COL38,
DEL_COL39,
DEL_COL40,
DEL_COL41,
DEL_COL42,
DEL_COL43,
DEL_COL44,
DEL_COL45,
DEL_COL46,
DEL_COL47,
DEL_COL48,
DEL_COL49,
DEL_COL50,
DEL_COL51,
DEL_COL52,
DEL_COL53,
DEL_COL54,
DEL_COL55,
DEL_COL56,
DEL_COL57,
DEL_COL58,
DEL_COL59,
DEL_COL60,
DEL_COL61,
DEL_COL62,
DEL_COL63,
DEL_COL64,
DEL_COL65,
DEL_COL66,
DEL_COL67,
DEL_COL68,
DEL_COL69,
DEL_COL70,
DEL_COL71,
DEL_COL72,
DEL_COL73,
DEL_COL74,
DEL_COL75,
DEL_COL76,
DEL_COL77,
DEL_COL78,
DEL_COL79,
DEL_COL80,
DEL_COL81,
DEL_COL82,
DEL_COL83,
DEL_COL84,
DEL_COL85,
DEL_COL86,
DEL_COL87,
DEL_COL88,
DEL_COL89,
DEL_COL90,
DEL_COL91,
DEL_COL92,
DEL_COL93,
DEL_COL94,
DEL_COL95,
DEL_COL96,
DEL_COL97,
DEL_COL98,
DEL_COL99,
DEL_COL100,
DEL_COL101,
DEL_COL102,
DEL_COL103,
DEL_COL104,
DEL_COL105,
DEL_COL106,
DEL_COL107,
DEL_COL108,
DEL_COL109,
DEL_COL110,
DEL_COL111,
DEL_COL112,
DEL_COL113,
DEL_COL114,
DEL_COL115,
DEL_COL116,
DEL_COL117,
DEL_COL118,
DEL_COL119,
ACTION_FLAG,
REQUEST_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY)
Values
( HZ_CUSTOMER_MERGE_LOG_s.nextval,
'RLM_CUST_SHIPTO_TERMS',
MERGE_HEADER_ID_LIST(I),
cust_shipto_terms.CUST_SHIPTO_TERMS_ID,
cust_shipto_terms.ADDRESS_ID,
cust_shipto_terms.CUSTOMER_ID,
cust_shipto_terms.SHIP_FROM_ORG_ID,
cust_shipto_terms.CUM_CONTROL_CODE,
cust_shipto_terms.SHIP_METHOD,
cust_shipto_terms.INTRANSIT_TIME,
cust_shipto_terms.TIME_UOM_CODE,
cust_shipto_terms.SHIP_DELIVERY_RULE_NAME,
cust_shipto_terms.CUM_CURRENT_RECORD_YEAR,
cust_shipto_terms.CUM_PREVIOUS_RECORD_YEAR,
cust_shipto_terms.CUM_CURRENT_START_DATE,
cust_shipto_terms.CUM_PREVIOUS_START_DATE,
cust_shipto_terms.CUM_ORG_LEVEL_CODE,
cust_shipto_terms.CUM_SHIPMENT_RULE_CODE,
cust_shipto_terms.CUM_YESTERD_TIME_CUTOFF,
cust_shipto_terms.CUST_ASSIGN_SUPPLIER_CD,
cust_shipto_terms.CUSTOMER_RCV_CALENDAR_CD,
cust_shipto_terms.FREIGHT_CODE,
cust_shipto_terms.SUPPLIER_SHP_CALENDAR_CD,
cust_shipto_terms.UNSHIP_FIRM_CUTOFF_DAYS,
cust_shipto_terms.UNSHIPPED_FIRM_DISP_CD,
cust_shipto_terms.USE_EDI_SDP_CODE_FLAG,
cust_shipto_terms.DEMAND_TOLERANCE_ABOVE,
cust_shipto_terms.DEMAND_TOLERANCE_BELOW,
cust_shipto_terms.INACTIVE_DATE,
cust_shipto_terms.HEADER_ID,
cust_shipto_terms.PRICE_LIST_ID,
cust_shipto_terms.CRITICAL_ATTRIBUTE_KEY,
cust_shipto_terms.CUSTOMER_CONTACT_ID,
cust_shipto_terms.PLN_FIRM_DAY_FROM,
cust_shipto_terms.PLN_FIRM_DAY_TO,
cust_shipto_terms.PLN_FORECAST_DAY_FROM,
cust_shipto_terms.PLN_FORECAST_DAY_TO,
cust_shipto_terms.PLN_FROZEN_DAY_TO,
cust_shipto_terms.PLN_FROZEN_DAY_FROM,
cust_shipto_terms.SEQ_FIRM_DAY_FROM,
cust_shipto_terms.SEQ_FIRM_DAY_TO,
cust_shipto_terms.SEQ_FORECAST_DAY_TO,
cust_shipto_terms.SEQ_FORECAST_DAY_FROM,
cust_shipto_terms.SEQ_FROZEN_DAY_FROM,
cust_shipto_terms.SEQ_FROZEN_DAY_TO,
cust_shipto_terms.SHP_FIRM_DAY_FROM,
cust_shipto_terms.SHP_FIRM_DAY_TO,
cust_shipto_terms.SHP_FROZEN_DAY_TO,
cust_shipto_terms.SHP_FROZEN_DAY_FROM,
cust_shipto_terms.SHP_FORECAST_DAY_TO,
cust_shipto_terms.ROUND_TO_STD_PACK_FLAG,
cust_shipto_terms.SUPPLIER_CONTACT_ID,
cust_shipto_terms.AGREEMENT_NAME,
cust_shipto_terms.STD_PACK_QTY,
cust_shipto_terms.FUTURE_AGREEMENT_NAME,
cust_shipto_terms.SHP_FORECAST_DAY_FROM,
cust_shipto_terms.SCHEDULE_HIERARCHY_CODE,
cust_shipto_terms.COMMENTS,
cust_shipto_terms.LAST_UPDATED_BY,
cust_shipto_terms.LAST_UPDATE_DATE,
cust_shipto_terms.CREATION_DATE,
cust_shipto_terms.CREATED_BY,
cust_shipto_terms.ATTRIBUTE_CATEGORY,
cust_shipto_terms.ATTRIBUTE1,
cust_shipto_terms.ATTRIBUTE2,
cust_shipto_terms.ATTRIBUTE3,
cust_shipto_terms.ATTRIBUTE4,
cust_shipto_terms.ATTRIBUTE5,
cust_shipto_terms.ATTRIBUTE6,
cust_shipto_terms.ATTRIBUTE7,
cust_shipto_terms.ATTRIBUTE8,
cust_shipto_terms.ATTRIBUTE9,
cust_shipto_terms.ATTRIBUTE10,
cust_shipto_terms.ATTRIBUTE11,
cust_shipto_terms.ATTRIBUTE12,
cust_shipto_terms.ATTRIBUTE13,
cust_shipto_terms.ATTRIBUTE14,
cust_shipto_terms.ATTRIBUTE15,
cust_shipto_terms.LAST_UPDATE_LOGIN,
cust_shipto_terms.REQUEST_ID,
cust_shipto_terms.PROGRAM_APPLICATION_ID,
cust_shipto_terms.PROGRAM_ID,
cust_shipto_terms.PROGRAM_UPDATE_DATE,
cust_shipto_terms.TP_ATTRIBUTE1,
cust_shipto_terms.TP_ATTRIBUTE2,
cust_shipto_terms.TP_ATTRIBUTE3,
cust_shipto_terms.TP_ATTRIBUTE4,
cust_shipto_terms.TP_ATTRIBUTE5,
cust_shipto_terms.TP_ATTRIBUTE6,
cust_shipto_terms.TP_ATTRIBUTE7,
cust_shipto_terms.TP_ATTRIBUTE8,
cust_shipto_terms.TP_ATTRIBUTE9,
cust_shipto_terms.TP_ATTRIBUTE10,
cust_shipto_terms.TP_ATTRIBUTE11,
cust_shipto_terms.TP_ATTRIBUTE12,
cust_shipto_terms.TP_ATTRIBUTE13,
cust_shipto_terms.TP_ATTRIBUTE14,
cust_shipto_terms.TP_ATTRIBUTE15,
cust_shipto_terms.TP_ATTRIBUTE_CATEGORY,
cust_shipto_terms.MATCH_ACROSS_KEY,
cust_shipto_terms.MATCH_WITHIN_KEY,
cust_shipto_terms.PLN_MRP_FORECAST_DAY_FROM,
cust_shipto_terms.PLN_MRP_FORECAST_DAY_TO,
cust_shipto_terms.SHP_MRP_FORECAST_DAY_FROM,
cust_shipto_terms.SHP_MRP_FORECAST_DAY_TO,
cust_shipto_terms.SEQ_MRP_FORECAST_DAY_FROM,
cust_shipto_terms.SEQ_MRP_FORECAST_DAY_TO,
cust_shipto_terms.INTRANSIT_CALC_BASIS,
cust_shipto_terms.DEFAULT_SHIP_FROM,
cust_shipto_terms.PLN_FROZEN_FLAG,
cust_shipto_terms.SHP_FROZEN_FLAG,
cust_shipto_terms.SEQ_FROZEN_FLAG,
cust_shipto_terms.ISSUE_WARNING_DROP_PARTS_FLAG,
cust_shipto_terms.ORG_ID,
cust_shipto_terms.BLANKET_NUMBER,
cust_shipto_terms.RELEASE_RULE,
cust_shipto_terms.RELEASE_TIME_FRAME,
cust_shipto_terms.RELEASE_TIME_FRAME_UOM,
cust_shipto_terms.AGREEMENT_ID,
cust_shipto_terms.FUTURE_AGREEMENT_ID,
cust_shipto_terms.EXCLUDE_NON_WORKDAYS_FLAG,
cust_shipto_terms.DISABLE_CREATE_CUM_KEY_FLAG,
'D',
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);
setARMessageDeleteTable('RLM_CUST_SHIPTO_TERMS');
DELETE RLM_CUST_SHIPTO_TERMS
WHERE (address_id = p_duplicateAddressIdTab(i)
OR customer_id = DECODE(p_duplicateIdTab(i),
p_customerIdTab(i),
-3.1428571,
p_duplicateIdTab(i)
));
SELECT address_id,
customer_id
FROM RLM_CUST_ITEM_TERMS
WHERE customer_id in
(select m.duplicate_id
from ra_customer_merges m
where m.process_flag = 'N'
and m.request_id = req_id
and m.set_number = set_num)
OR address_id in
(select m.duplicate_address_id
from ra_customer_merges m
where m.process_flag = 'N'
and m.request_id = req_id
and m.set_number = set_num)
FOR update nowait;
SELECT distinct CUSTOMER_MERGE_HEADER_ID
FROM rlm_cust_shipto_terms 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;
SELECT * from rlm_cust_item_terms
where customer_id in
(select m.duplicate_id
from ra_customer_merges m
where m.process_flag = 'N'
and m.request_id = req_id
and m.set_number = set_num)
OR address_id in
(select m.duplicate_address_id
from ra_customer_merges m
where m.process_flag = 'N'
and m.request_id = req_id
and m.set_number = set_num)
FOR update nowait;
INSERT INTO HZ_CUSTOMER_MERGE_LOG (
MERGE_LOG_ID,
TABLE_NAME,
MERGE_HEADER_ID,
DEL_COL1,
DEL_COL2,
DEL_COL3 ,
DEL_COL4 ,
DEL_COL5,
DEL_COL6,
DEL_COL7,
DEL_COL8,
DEL_COL9,
DEL_COL10,
DEL_COL11,
DEL_COL12,
DEL_COL13,
DEL_COL14,
DEL_COL15,
DEL_COL16,
DEL_COL17,
DEL_COL18,
DEL_COL19,
DEL_COL20,
DEL_COL21,
DEL_COL22,
DEL_COL23,
DEL_COL24,
DEL_COL25,
DEL_COL26,
DEL_COL27,
DEL_COL28,
DEL_COL29,
DEL_COL30,
DEL_COL31,
DEL_COL32,
DEL_COL33,
DEL_COL34 ,
DEL_COL35,
DEL_COL36,
DEL_COL37,
DEL_COL38,
DEL_COL39,
DEL_COL40,
DEL_COL41,
DEL_COL42,
DEL_COL43,
DEL_COL44,
DEL_COL45,
DEL_COL46,
DEL_COL47,
DEL_COL48,
DEL_COL49,
DEL_COL50,
DEL_COL51,
DEL_COL52,
DEL_COL53,
DEL_COL54,
DEL_COL55,
DEL_COL56,
DEL_COL57,
DEL_COL58,
DEL_COL59,
DEL_COL60,
DEL_COL61,
DEL_COL62,
DEL_COL63,
DEL_COL64,
DEL_COL65,
DEL_COL66,
DEL_COL67,
DEL_COL68,
DEL_COL69,
DEL_COL70,
DEL_COL71,
DEL_COL72,
DEL_COL73,
DEL_COL74,
DEL_COL75,
DEL_COL76,
DEL_COL77,
DEL_COL78,
DEL_COL79,
DEL_COL80,
DEL_COL81,
DEL_COL82,
DEL_COL83,
DEL_COL84,
DEL_COL85,
DEL_COL86,
DEL_COL87,
DEL_COL88,
DEL_COL89,
DEL_COL90,
DEL_COL91,
DEL_COL92,
DEL_COL93,
DEL_COL94,
DEL_COL95,
DEL_COL96,
DEL_COL97,
DEL_COL98,
DEL_COL99,
DEL_COL100,
DEL_COL101,
DEL_COL102,
DEL_COL103,
DEL_COL104,
DEL_COL105,
DEL_COL106,
ACTION_FLAG,
REQUEST_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY)
Values
( HZ_CUSTOMER_MERGE_LOG_s.nextval,
'RLM_CUST_ITEM_TERMS',
MERGE_HEADER_ID_LIST(I),
cust_item_terms.CUST_ITEM_TERMS_ID,
cust_item_terms.CUSTOMER_ITEM_ID,
cust_item_terms.SHIP_FROM_ORG_ID,
cust_item_terms.ADDRESS_ID,
cust_item_terms.HEADER_ID,
cust_item_terms.AGREEMENT_NAME,
cust_item_terms.FUTURE_AGREEMENT_NAME,
cust_item_terms.CALC_CUM_FLAG,
cust_item_terms.CUM_CURRENT_START_DATE,
cust_item_terms.CUM_PREVIOUS_START_DATE,
cust_item_terms.CUST_ITEM_STATUS_CODE,
cust_item_terms.ROUND_TO_STD_PACK_FLAG,
cust_item_terms.SHIP_DELIVERY_RULE_NAME,
cust_item_terms.SHIP_METHOD,
cust_item_terms.INTRANSIT_TIME,
cust_item_terms.STD_PACK_QTY,
cust_item_terms.TIME_UOM_CODE,
cust_item_terms.PRICE_LIST_ID,
cust_item_terms.DEMAND_TOLERANCE_ABOVE,
cust_item_terms.USE_EDI_SDP_CODE_FLAG,
cust_item_terms.LAST_UPDATE_DATE,
cust_item_terms.LAST_UPDATED_BY,
cust_item_terms.CREATION_DATE,
cust_item_terms.CREATED_BY,
cust_item_terms.PLN_FIRM_DAY_TO,
cust_item_terms.ATTRIBUTE_CATEGORY,
cust_item_terms.PLN_FORECAST_DAY_FROM,
cust_item_terms.PLN_FORECAST_DAY_TO,
cust_item_terms.PLN_FROZEN_DAY_TO,
cust_item_terms.PLN_FROZEN_DAY_FROM,
cust_item_terms.ATTRIBUTE1,
cust_item_terms.SEQ_FIRM_DAY_FROM,
cust_item_terms.SEQ_FIRM_DAY_TO,
cust_item_terms.ATTRIBUTE2,
cust_item_terms.SEQ_FORECAST_DAY_TO,
cust_item_terms.SEQ_FORECAST_DAY_FROM,
cust_item_terms.ATTRIBUTE3,
cust_item_terms.SEQ_FROZEN_DAY_FROM,
cust_item_terms.SEQ_FROZEN_DAY_TO,
cust_item_terms.ATTRIBUTE4,
cust_item_terms.SHP_FIRM_DAY_FROM,
cust_item_terms.SHP_FIRM_DAY_TO,
cust_item_terms.ATTRIBUTE5,
cust_item_terms.SHP_FROZEN_DAY_TO,
cust_item_terms.SHP_FROZEN_DAY_FROM,
cust_item_terms.ATTRIBUTE6,
cust_item_terms.SHP_FORECAST_DAY_TO,
cust_item_terms.SHP_FORECAST_DAY_FROM,
cust_item_terms.ATTRIBUTE7,
cust_item_terms.ATTRIBUTE8,
cust_item_terms.ATTRIBUTE9,
cust_item_terms.ATTRIBUTE10,
cust_item_terms.ATTRIBUTE11,
cust_item_terms.ATTRIBUTE12,
cust_item_terms.ATTRIBUTE13,
cust_item_terms.ATTRIBUTE14,
cust_item_terms.ATTRIBUTE15,
cust_item_terms.LAST_UPDATE_LOGIN,
cust_item_terms.REQUEST_ID,
cust_item_terms.PROGRAM_APPLICATION_ID,
cust_item_terms.PROGRAM_ID,
cust_item_terms.PROGRAM_UPDATE_DATE,
cust_item_terms.DEMAND_TOLERANCE_BELOW,
cust_item_terms.CUSTOMER_CONTACT_ID,
cust_item_terms.CUSTOMER_ID,
cust_item_terms.FREIGHT_CODE,
cust_item_terms.PLN_FIRM_DAY_FROM,
cust_item_terms.SUPPLIER_CONTACT_ID,
cust_item_terms.TP_ATTRIBUTE1,
cust_item_terms.TP_ATTRIBUTE2,
cust_item_terms.TP_ATTRIBUTE3,
cust_item_terms.TP_ATTRIBUTE4,
cust_item_terms.TP_ATTRIBUTE5,
cust_item_terms.TP_ATTRIBUTE6,
cust_item_terms.TP_ATTRIBUTE7,
cust_item_terms.TP_ATTRIBUTE8,
cust_item_terms.TP_ATTRIBUTE9,
cust_item_terms.TP_ATTRIBUTE10,
cust_item_terms.TP_ATTRIBUTE11,
cust_item_terms.TP_ATTRIBUTE12,
cust_item_terms.TP_ATTRIBUTE13,
cust_item_terms.TP_ATTRIBUTE14,
cust_item_terms.TP_ATTRIBUTE15,
cust_item_terms.TP_ATTRIBUTE_CATEGORY,
cust_item_terms.INACTIVE_DATE,
cust_item_terms.COMMENTS,
cust_item_terms.DEFAULT_SHIP_FROM,
cust_item_terms.PLN_MRP_FORECAST_DAY_FROM,
cust_item_terms.PLN_MRP_FORECAST_DAY_TO,
cust_item_terms.SHP_MRP_FORECAST_DAY_FROM,
cust_item_terms.SHP_MRP_FORECAST_DAY_TO,
cust_item_terms.SEQ_MRP_FORECAST_DAY_FROM,
cust_item_terms.SEQ_MRP_FORECAST_DAY_TO,
cust_item_terms.PLN_FROZEN_FLAG,
cust_item_terms.SHP_FROZEN_FLAG,
cust_item_terms.SEQ_FROZEN_FLAG,
cust_item_terms.ISSUE_WARNING_DROP_PARTS_FLAG,
cust_item_terms.ORG_ID,
cust_item_terms.BLANKET_NUMBER,
cust_item_terms.RELEASE_RULE,
cust_item_terms.RELEASE_TIME_FRAME,
cust_item_terms.RELEASE_TIME_FRAME_UOM,
cust_item_terms.AGREEMENT_ID,
cust_item_terms.FUTURE_AGREEMENT_ID,
cust_item_terms.EXCLUDE_NON_WORKDAYS_FLAG,
cust_item_terms.DISABLE_CREATE_CUM_KEY_FLAG,
'D',
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);
setARMessageDeleteTable('RLM_CUST_ITEM_TERMS');
DELETE RLM_CUST_ITEM_TERMS
WHERE (address_id = p_duplicateAddressIdTab(i)
OR customer_id = DECODE(p_duplicateIdTab(i),
p_customerIdTab(i),
-3.1428571,
p_duplicateIdTab(i)
));
PROCEDURE setARMessageUpdateTable
============================================================================*/
PROCEDURE setARMessageUpdateTable(p_tableName IN VARCHAR2)
--
IS
--
BEGIN
--
arp_message.set_name('AR','AR_UPDATING_TABLE');
END setARMessageUpdateTable;
PROCEDURE setARMessageDeleteTable
============================================================================*/
PROCEDURE setARMessageDeleteTable(p_tableName IN VARCHAR2)
--
IS
--
BEGIN
--
arp_message.set_name('AR','AR_DELETING_TABLE');
END setARMessageDeleteTable;
arp_message.set_name('AR','AR_ROWS_UPDATED');
SELECT distinct CUSTOMER_MERGE_HEADER_ID
,CUM_KEY_ID
,INTRMD_SHIP_TO_ID
,SHIP_TO_ADDRESS_ID
,BILL_TO_ADDRESS_ID
,CUM_NOTE_TEXT
,INACTIVE_FLAG
FROM RLM_CUST_ITEM_CUM_KEYS yt, ra_customer_merges m
WHERE (
yt.INTRMD_SHIP_TO_ID = m.DUPLICATE_ADDRESS_ID
OR yt.SHIP_TO_ADDRESS_ID = m.DUPLICATE_ADDRESS_ID
OR yt.BILL_TO_ADDRESS_ID = m.DUPLICATE_ADDRESS_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,
VCHAR_COL1_ORIG,
VCHAR_COL1_NEW,
VCHAR_COL2_ORIG,
VCHAR_COL2_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,
'RLM_CUST_ITEM_CUM_KEYS',
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),
VCHAR_COL1_ORIG_LIST(I),
VCHAR_COL1_NEW_LIST(I),
VCHAR_COL2_ORIG_LIST(I),
VCHAR_COL2_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
);
SELECT distinct CUSTOMER_MERGE_HEADER_ID
,HEADER_ID
,yt.CUSTOMER_ID
,ECE_PRIMARY_ADDRESS_ID
,CUST_NAME_EXT
,HEADER_NOTE_TEXT
FROM RLM_INTERFACE_HEADERS yt, ra_customer_merges m
WHERE (
yt.CUSTOMER_ID = m.DUPLICATE_ID
OR yt.ECE_PRIMARY_ADDRESS_ID = m.DUPLICATE_ADDRESS_ID
) AND m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num;
select distinct customer_name into cust_name
from ra_customer_merges
where customer_id = NUM_COL1_NEW_LIST(I)
and request_id = req_id;
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,
VCHAR_COL1_ORIG,
VCHAR_COL1_NEW,
VCHAR_COL2_ORIG,
VCHAR_COL2_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,
'RLM_INTERFACE_HEADERS',
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),
VCHAR_COL1_ORIG_LIST(I),
VCHAR_COL1_NEW_LIST(I),
VCHAR_COL2_ORIG_LIST(I),
VCHAR_COL2_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
);
SELECT distinct CUSTOMER_MERGE_HEADER_ID
,LINE_ID
,INTRMD_SHIP_TO_ID
,SHIP_TO_ADDRESS_ID
,BILL_TO_ADDRESS_ID
,SHIP_TO_ORG_ID
,INVOICE_TO_ORG_ID
,INTMED_SHIP_TO_ORG_ID
,ITEM_NOTE_TEXT
FROM RLM_INTERFACE_LINES yt, ra_customer_merges m
WHERE (
yt.INTRMD_SHIP_TO_ID = m.DUPLICATE_ADDRESS_ID
OR yt.SHIP_TO_ADDRESS_ID = m.DUPLICATE_ADDRESS_ID
OR yt.BILL_TO_ADDRESS_ID = m.DUPLICATE_ADDRESS_ID
OR yt.SHIP_TO_ORG_ID = m.DUPLICATE_ADDRESS_ID
OR yt.INVOICE_TO_ORG_ID = m.DUPLICATE_ADDRESS_ID
OR yt.INTMED_SHIP_TO_ORG_ID = m.DUPLICATE_ADDRESS_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,
VCHAR_COL1_ORIG,
VCHAR_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,
'RLM_INTERFACE_LINES',
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),
VCHAR_COL1_ORIG_LIST(I),
VCHAR_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
);
SELECT distinct CUSTOMER_MERGE_HEADER_ID
,HEADER_ID
,yt.CUSTOMER_ID
,ECE_PRIMARY_ADDRESS_ID
,CUST_NAME_EXT
,HEADER_NOTE_TEXT
FROM RLM_SCHEDULE_HEADERS yt, ra_customer_merges m
WHERE (
yt.CUSTOMER_ID = m.DUPLICATE_ID
OR yt.ECE_PRIMARY_ADDRESS_ID = m.DUPLICATE_ADDRESS_ID
) AND m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num;
select distinct customer_name into cust_name
from ra_customer_merges
where customer_id = NUM_COL1_NEW_LIST(I)
and request_id = req_id;
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,
VCHAR_COL1_ORIG,
VCHAR_COL1_NEW,
VCHAR_COL2_ORIG,
VCHAR_COL2_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,
'RLM_SCHEDULE_HEADERS',
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),
VCHAR_COL1_ORIG_LIST(I),
VCHAR_COL1_NEW_LIST(I),
VCHAR_COL2_ORIG_LIST(I),
VCHAR_COL2_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
);
SELECT distinct CUSTOMER_MERGE_HEADER_ID
,LINE_ID
,INTRMD_SHIP_TO_ID
,SHIP_TO_ADDRESS_ID
,BILL_TO_ADDRESS_ID
,SHIP_TO_ORG_ID
,INVOICE_TO_ORG_ID
,INTMED_SHIP_TO_ORG_ID
,ITEM_NOTE_TEXT
FROM RLM_SCHEDULE_LINES yt, ra_customer_merges m
WHERE (
yt.INTRMD_SHIP_TO_ID = m.DUPLICATE_ADDRESS_ID
OR yt.SHIP_TO_ADDRESS_ID = m.DUPLICATE_ADDRESS_ID
OR yt.BILL_TO_ADDRESS_ID = m.DUPLICATE_ADDRESS_ID
OR yt.SHIP_TO_ORG_ID = m.DUPLICATE_ADDRESS_ID
OR yt.INVOICE_TO_ORG_ID = m.DUPLICATE_ADDRESS_ID
OR yt.INTMED_SHIP_TO_ORG_ID = m.DUPLICATE_ADDRESS_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,
VCHAR_COL1_ORIG,
VCHAR_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,
'RLM_SCHEDULE_LINES',
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),
VCHAR_COL1_ORIG_LIST(I),
VCHAR_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
);