[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| ******* Please delete these lines after modifications *******
| This account merge procedure was generated using a perl script.
|
| This is only suggested code. Please ensure that the code actually
| works for you.
|
| Please also address the additional notes inserted as comments in the
| code below.
| ******************************
|
|--------------------------------------------------------------*/
PROCEDURE Agreement_Merge (
req_id NUMBER,
set_num NUMBER,
process_mode VARCHAR2) IS
TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
INDEX BY BINARY_INTEGER;
SELECT distinct CUSTOMER_MERGE_HEADER_ID
,agreement_id
,invoice_to_org_id
,sold_to_org_id
FROM OE_AGREEMENTS_B yt, ra_customer_merges m
WHERE yt.invoice_to_org_id = m.DUPLICATE_SITE_ID
AND m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num
UNION
SELECT distinct CUSTOMER_MERGE_HEADER_ID
,agreement_id
,invoice_to_org_id
,sold_to_org_id
FROM OE_AGREEMENTS_B yt, ra_customer_merges m
WHERE yt.sold_to_org_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,
ACTION_FLAG,
REQUEST_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
'OE_AGREEMENTS_B',
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),
'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 OE_AGREEMENTS_B yt SET
invoice_to_org_id=NUM_COL1_NEW_LIST(I)
,sold_to_org_id=NUM_COL2_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 agreement_id=PRIMARY_KEY_ID_LIST(I)
;
arp_message.set_name('AR','AR_ROWS_UPDATED');
SELECT a.qualifier_id
INTO l_qualifier_id
FROM qp_qualifiers a
WHERE a.qualifier_attr_value = to_char(p_qualifier_attr_value)
AND trunc(l_temp_date) between nvl(trunc(start_date_active), trunc(l_temp_date)) and
nvl(trunc(end_date_active), trunc(l_temp_date))
AND (a.qualifier_context,
a.qualifier_attribute,
nvl(a.list_header_id, -1),
nvl(a.list_line_id, -1),
nvl(qualifier_rule_id, -1),
a.qualifier_grouping_no) IN
(SELECT b.qualifier_context, b.qualifier_attribute,
nvl(b.list_header_id, -1),
nvl(b.list_line_id, -1),
nvl(qualifier_rule_id, -1),
b.qualifier_grouping_no
FROM qp_qualifiers b
WHERE b.qualifier_id = p_qualifier_id
AND b.qualifier_id <> a.qualifier_id)
AND rownum = 1;
IF l_qualifier_id IS NOT NULL THEN /* Duplicate Exists. Therefore delete
the duplicate qualifier */
DELETE qp_qualifiers
WHERE qualifier_id = l_qualifier_id;
| ******* Please delete these lines after modifications *******
| This account merge procedure was generated using a perl script.
|
| This is only suggested code. Please ensure that the code actually
| works for you.
|
| Please also address the additional notes inserted as comments in the
| code below.
| ******************************
|
|--------------------------------------------------------------*/
PROCEDURE Qualifier_Merge (
req_id NUMBER,
set_num NUMBER,
process_mode VARCHAR2) IS
TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
INDEX BY BINARY_INTEGER;
SELECT distinct CUSTOMER_MERGE_HEADER_ID
,qualifier_id
,qualifier_attr_value
--Added following 3 select list columns for bug fix 3649761
,qualifier_attr_value
,qualifier_context
,qualifier_attribute
FROM QP_QUALIFIERS yt, ra_customer_merges m
WHERE
/* (
yt.qualifier_attr_value = to_char(m.DUPLICATE_SITE_ID)
)*/
-- above clause replaced by clause below for bug fix 3649761
(
yt.qualifier_attr_value = to_char(m.DUPLICATE_SITE_ID) AND
(yt.qualifier_context = 'CUSTOMER' AND
yt.qualifier_attribute = 'QUALIFIER_ATTRIBUTE11' --Ship To
OR
yt.qualifier_context = 'CUSTOMER' AND
yt.qualifier_attribute = 'QUALIFIER_ATTRIBUTE5' --Site Use
OR
yt.qualifier_context = 'CUSTOMER' AND
yt.qualifier_attribute = 'QUALIFIER_ATTRIBUTE14' --Bill To
)
OR
yt.qualifier_attr_value = to_char(m.DUPLICATE_ID) AND
(yt.qualifier_context = 'CUSTOMER' AND
yt.qualifier_attribute = 'QUALIFIER_ATTRIBUTE2' --Customer Name
)
)
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,
VCHAR_COL1_ORIG,
VCHAR_COL1_NEW,
--Added the following 2 columns for bug fix 3649761
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,
'QP_QUALIFIERS',
MERGE_HEADER_ID_LIST(I),
PRIMARY_KEY_ID_LIST(I),
VCHAR_COL1_ORIG_LIST(I),
VCHAR_COL1_NEW_LIST(I),
--Added the following 2 columns for bug fix 3649761
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
);
UPDATE QP_QUALIFIERS yt SET
--Modified code for bug fix 3649761
qualifier_attr_value=decode(nvl(VCHAR_COL1_NEW_LIST(I),'x'), 'x',
VCHAR_COL2_NEW_LIST(I),
VCHAR_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
, 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 qualifier_id=PRIMARY_KEY_ID_LIST(I);
arp_message.set_name('AR','AR_ROWS_UPDATED');