The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT lease_id
FROM pn_leases
WHERE customer_id IN (SELECT racm.duplicate_id
FROM ra_customer_merges racm
WHERE racm.process_flag = 'N'
AND racm.request_id = req_id
AND racm.set_number = set_num
)
FOR UPDATE NOWAIT;
SELECT tenancy_id
FROM pn_tenancies
WHERE customer_id IN (SELECT racm.duplicate_id
FROM ra_customer_merges racm
WHERE racm.process_flag = 'N'
AND racm.request_id = req_id
AND racm.set_number = set_num
)
FOR UPDATE NOWAIT;
SELECT tenancy_history_id
FROM pn_tenancies_history
WHERE customer_id IN (SELECT racm.duplicate_id
FROM ra_customer_merges racm
WHERE racm.process_flag = 'N'
AND racm.request_id = req_id
AND racm.set_number = set_num
)
FOR UPDATE NOWAIT;
SELECT term_template_id
FROM pn_term_templates
WHERE customer_id IN (SELECT racm.duplicate_id
FROM ra_customer_merges racm
WHERE racm.process_flag = 'N'
AND racm.request_id = req_id
AND racm.set_number = set_num
)
FOR UPDATE NOWAIT;
SELECT payment_term_id
FROM pn_payment_terms
WHERE customer_id IN (SELECT racm.duplicate_id
FROM ra_customer_merges racm
WHERE racm.process_flag = 'N'
AND racm.request_id = req_id
AND racm.set_number = set_num
)
FOR UPDATE NOWAIT;
SELECT payment_item_id
FROM pn_payment_items
WHERE customer_id IN (SELECT racm.duplicate_id
FROM ra_customer_merges racm
WHERE racm.process_flag = 'N'
AND racm.request_id = req_id
AND racm.set_number = set_num
)
FOR UPDATE NOWAIT;
SELECT rec_agreement_id
FROM pn_rec_agreements
WHERE customer_id IN (SELECT racm.duplicate_id
FROM ra_customer_merges racm
WHERE racm.process_flag = 'N'
AND racm.request_id = req_id
AND racm.set_number = set_num
)
FOR UPDATE NOWAIT;
SELECT area_class_dtl_id
FROM pn_rec_arcl_dtlln
WHERE cust_account_id IN
(SELECT racm.duplicate_id
FROM ra_customer_merges racm
WHERE racm.process_flag = 'N'
AND racm.request_id = req_id
AND racm.set_number = set_num
)
FOR UPDATE NOWAIT;
SELECT expense_class_dtl_id
FROM pn_rec_expcl_dtlln
WHERE cust_account_id IN
(SELECT racm.duplicate_id
FROM ra_customer_merges racm
WHERE racm.process_flag = 'N'
AND racm.request_id = req_id
AND racm.set_number = set_num
)
FOR UPDATE NOWAIT;
SELECT rec_period_lines_id
FROM pn_rec_period_lines
WHERE cust_account_id IN
(SELECT racm.duplicate_id
FROM ra_customer_merges racm
WHERE racm.process_flag = 'N'
AND racm.request_id = req_id
AND racm.set_number = set_num
)
FOR UPDATE NOWAIT;
SELECT cust_space_assign_id
FROM pn_space_assign_cust
WHERE cust_account_id IN
(SELECT racm.duplicate_id
FROM ra_customer_merges racm
WHERE racm.process_flag = 'N'
AND racm.request_id = req_id
AND racm.set_number = set_num
)
FOR UPDATE NOWAIT;
update_leases ( req_id => req_id,
set_num => set_num,
process_mode => process_mode
);
update_tenancies ( req_id => req_id,
set_num => set_num,
process_mode => process_mode
);
update_tenancies_history ( req_id => req_id,
set_num => set_num,
process_mode => process_mode
);
update_term_templates ( req_id => req_id,
set_num => set_num,
process_mode => process_mode
);
update_payment_terms ( req_id => req_id,
set_num => set_num,
process_mode => process_mode
);
update_payment_items ( req_id => req_id,
set_num => set_num,
process_mode => process_mode
);
update_rec_agreements ( req_id => req_id,
set_num => set_num,
process_mode => process_mode
);
update_rec_arcl_dtln ( req_id => req_id,
set_num => set_num,
process_mode => process_mode
);
update_rec_expcl_dtln ( req_id => req_id,
set_num => set_num,
process_mode => process_mode
);
update_rec_period_lines ( req_id => req_id,
set_num => set_num,
process_mode => process_mode
);
update_space_assign_cust ( req_id => req_id,
set_num => set_num,
process_mode => process_mode
);
| update_leases
|
| DESCRIPTION
| Account merge procedure for the table, pn_leases
| Column updated Corresponding HZ table.column
| -------------- --------------------------------
| customer_id HZ_CUST_ACCOUNTS.cust_account_id
|
| SCOPE - PRIVATE
|
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN: req_id, set_num, process_mode
|
| NOTES :
|
| MODIFICATION HISTORY
|
| 29-apr-2004 Perl Script Created
| 29-apr-2004 Kiran Finalised
+===========================================================================*/
PROCEDURE update_leases (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 m.CUSTOMER_MERGE_HEADER_ID
,yt.LEASE_ID
,yt.CUSTOMER_ID
FROM PN_LEASES 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_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,
'PN_LEASES',
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 PN_LEASES 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 LEASE_ID=PRIMARY_KEY_ID_LIST(I);
arp_message.set_name('AR','AR_ROWS_UPDATED');
arp_message.set_line( 'update_leases');
END update_leases;
| update_tenancies
|
| DESCRIPTION
| Account merge procedure for the table, pn_tenencies
| Column updated Corresponding HZ table.column
| -------------------- -----------------------------
| customer_id HZ_CUST_ACCOUNTS.cust_account_id
| customer_site_use_id HZ_CUST_SITE_USES.site_use_id
|
| SCOPE - PRIVATE
|
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN: req_id, set_num, process_mode
|
| NOTES :
|
| MODIFICATION HISTORY
|
| 29-apr-2004 Perl Script Created
| 29-apr-2004 Kiran Finalised
+===========================================================================*/
PROCEDURE update_tenancies (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 m.CUSTOMER_MERGE_HEADER_ID
,yt.TENANCY_ID
,yt.CUSTOMER_ID
,yt.CUSTOMER_SITE_USE_ID
FROM PN_TENANCIES yt
,RA_CUSTOMER_MERGES m
WHERE ( yt.CUSTOMER_ID = m.DUPLICATE_ID
OR yt.CUSTOMER_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,
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,
'PN_TENANCIES',
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 PN_TENANCIES yt SET
CUSTOMER_ID = NUM_COL1_NEW_LIST(I)
, CUSTOMER_SITE_USE_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 TENANCY_ID=PRIMARY_KEY_ID_LIST(I);
arp_message.set_name('AR','AR_ROWS_UPDATED');
arp_message.set_line( 'update_tenancies');
END update_tenancies;
| update_tenancies_history
|
| DESCRIPTION
| Account merge procedure for the table, pn_tenancies_history
| Column updated Corresponding HZ table.column
| -------------------- -----------------------------
| customer_id HZ_CUST_ACCOUNTS.cust_account_id
| customer_site_use_id HZ_CUST_SITE_USES.site_use_id
|
| SCOPE - PRIVATE
|
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN: req_id, set_num, process_mode
|
| NOTES :
|
| MODIFICATION HISTORY
|
| 29-apr-2004 Perl Script Created
| 29-apr-2004 Kiran Finalised
+===========================================================================*/
PROCEDURE update_tenancies_history (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 m.CUSTOMER_MERGE_HEADER_ID
,yt.TENANCY_HISTORY_ID
,yt.CUSTOMER_ID
,yt.CUSTOMER_SITE_USE_ID
FROM PN_TENANCIES_HISTORY yt,
RA_CUSTOMER_MERGES m
WHERE ( yt.CUSTOMER_ID = m.DUPLICATE_ID
OR yt.CUSTOMER_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,
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,
'PN_TENANCIES_HISTORY',
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 PN_TENANCIES_HISTORY yt SET
CUSTOMER_ID = NUM_COL1_NEW_LIST(I)
, CUSTOMER_SITE_USE_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 TENANCY_HISTORY_ID = PRIMARY_KEY_ID_LIST(I);
arp_message.set_name('AR','AR_ROWS_UPDATED');
arp_message.set_line( 'update_tenancies_history');
END update_tenancies_history;
| update_term_templates
|
| DESCRIPTION
| Account merge procedure for the table, pn_term_templates
| Column updated Corresponding HZ table.column
| -------------------- -----------------------------
| customer_id HZ_CUST_ACCOUNTS.cust_account_id
| customer_site_use_id HZ_CUST_SITE_USES.site_use_id
| cust_ship_site_id HZ_CUST_SITE_USES.site_use_id
|
| SCOPE - PRIVATE
|
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN: req_id, set_num, process_mode
|
| NOTES :
|
| MODIFICATION HISTORY
|
| 29-apr-2004 Perl Script Created
| 29-apr-2004 Kiran Finalised
+===========================================================================*/
PROCEDURE update_term_templates (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 m.CUSTOMER_MERGE_HEADER_ID
,yt.TERM_TEMPLATE_ID
,yt.CUSTOMER_ID
,yt.CUSTOMER_SITE_USE_ID
,yt.CUST_SHIP_SITE_ID
FROM PN_TERM_TEMPLATES yt,
RA_CUSTOMER_MERGES m
WHERE ( yt.CUSTOMER_ID = m.DUPLICATE_ID
OR yt.CUSTOMER_SITE_USE_ID = m.DUPLICATE_SITE_ID
OR yt.CUST_SHIP_SITE_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,
NUM_COL2_ORIG,
NUM_COL2_NEW,
NUM_COL3_ORIG,
NUM_COL3_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,
'PN_TERM_TEMPLATES',
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),
'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 PN_TERM_TEMPLATES yt SET
CUSTOMER_ID = NUM_COL1_NEW_LIST(I)
, CUSTOMER_SITE_USE_ID= NUM_COL2_NEW_LIST(I)
, CUST_SHIP_SITE_ID = NUM_COL3_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 TERM_TEMPLATE_ID = PRIMARY_KEY_ID_LIST(I);
arp_message.set_name('AR','AR_ROWS_UPDATED');
arp_message.set_line( 'update_term_templates');
END update_term_templates;
| update_payment_terms
|
| DESCRIPTION
| Account merge procedure for the table, pn_payment_terms_all
| Column updated Corresponding HZ table.column
| -------------------- -----------------------------
| customer_id HZ_CUST_ACCOUNTS.cust_account_id
| customer_site_use_id HZ_CUST_SITE_USES.site_use_id
| cust_ship_site_id HZ_CUST_SITE_USES.site_use_id
|
| SCOPE - PRIVATE
|
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN: req_id, set_num, process_mode
|
| OUT: none
|
| NOTES :
|
| MODIFICATION HISTORY
|
| 18-FEB-2003 Perl Script Created
| 18-FEB-2003 Kiran Finalised
| 29-apr-2004 Kiran Added code to update ship_site_id
+===========================================================================*/
PROCEDURE update_payment_terms (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 m.CUSTOMER_MERGE_HEADER_ID
,yt.PAYMENT_TERM_ID
,yt.CUSTOMER_ID
,yt.CUSTOMER_SITE_USE_ID
,yt.CUST_SHIP_SITE_ID
FROM PN_PAYMENT_TERMS yt,
RA_CUSTOMER_MERGES m
WHERE ( yt.CUSTOMER_ID = m.DUPLICATE_ID
OR yt.CUSTOMER_SITE_USE_ID = m.DUPLICATE_SITE_ID
OR yt.CUST_SHIP_SITE_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,
NUM_COL2_ORIG,
NUM_COL2_NEW,
NUM_COL3_ORIG,
NUM_COL3_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,
'PN_PAYMENT_TERMS',
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),
'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 PN_PAYMENT_TERMS yt SET
CUSTOMER_ID = NUM_COL1_NEW_LIST(I)
, CUSTOMER_SITE_USE_ID= NUM_COL2_NEW_LIST(I)
, CUST_SHIP_SITE_ID = NUM_COL3_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 PAYMENT_TERM_ID=PRIMARY_KEY_ID_LIST(I);
arp_message.set_name('AR','AR_ROWS_UPDATED');
arp_message.set_line( 'update_payment_terms');
END update_payment_terms;
| update_payment_items
|
| DESCRIPTION
| Account merge procedure for the table, pn_payment_items_all
| Column updated Corresponding HZ table.column
| -------------------- -----------------------------
| customer_id HZ_CUST_ACCOUNTS.cust_account_id
| customer_site_use_id HZ_CUST_SITE_USES.site_use_id
| cust_ship_site_id HZ_CUST_SITE_USES.site_use_id
|
| SCOPE - PRIVATE
|
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN: req_id, set_num, process_mode
|
| NOTES :
|
| MODIFICATION HISTORY
|
| 18-feb-2003 Perl Script Created
| 18-feb-2003 Kiran Hegde Finalised
| 29-apr-2004 Kiran Added code to update ship_site_id
+===========================================================================*/
PROCEDURE update_payment_items (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 m.CUSTOMER_MERGE_HEADER_ID
,yt.PAYMENT_ITEM_ID
,yt.CUSTOMER_ID
,yt.CUSTOMER_SITE_USE_ID
,yt.CUST_SHIP_SITE_ID
FROM PN_PAYMENT_ITEMS yt
,RA_CUSTOMER_MERGES m
WHERE ( yt.CUSTOMER_ID = m.DUPLICATE_ID
OR yt.CUSTOMER_SITE_USE_ID = m.DUPLICATE_SITE_ID
OR yt.CUST_SHIP_SITE_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,
NUM_COL2_ORIG,
NUM_COL2_NEW,
NUM_COL3_ORIG,
NUM_COL3_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,
'PN_PAYMENT_ITEMS',
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),
'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 PN_PAYMENT_ITEMS yt SET
CUSTOMER_ID=NUM_COL1_NEW_LIST(I)
, CUSTOMER_SITE_USE_ID=NUM_COL2_NEW_LIST(I)
, CUST_SHIP_SITE_ID=NUM_COL3_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 PAYMENT_ITEM_ID=PRIMARY_KEY_ID_LIST(I);
arp_message.set_name('AR','AR_ROWS_UPDATED');
arp_message.set_line( 'update_payment_items');
END update_payment_items;
| update_rec_agreements
|
| DESCRIPTION
| Account merge procedure for the table, pn_rec_agreements
| Column updated Corresponding HZ table.column
| -------------------- -----------------------------
| customer_id HZ_CUST_ACCOUNTS.cust_account_id
| cust_site_id HZ_CUST_SITE_USES.site_use_id
|
| SCOPE - PRIVATE
|
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN: req_id, set_num, process_mode
|
| NOTES :
|
| MODIFICATION HISTORY
|
| 29-apr-2004 Perl Script Created
| 29-apr-2004 Kiran Finalised
+===========================================================================*/
PROCEDURE update_rec_agreements (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 m.CUSTOMER_MERGE_HEADER_ID
,yt.REC_AGREEMENT_ID
,yt.CUSTOMER_ID
,yt.CUST_SITE_ID
FROM PN_REC_AGREEMENTS yt
,RA_CUSTOMER_MERGES m
WHERE ( yt.CUSTOMER_ID = m.DUPLICATE_ID
OR yt.CUST_SITE_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,
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,
'PN_REC_AGREEMENTS',
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 PN_REC_AGREEMENTS yt SET
CUSTOMER_ID=NUM_COL1_NEW_LIST(I)
, CUST_SITE_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 REC_AGREEMENT_ID=PRIMARY_KEY_ID_LIST(I);
arp_message.set_name('AR','AR_ROWS_UPDATED');
arp_message.set_line( 'update_rec_agreements');
END update_rec_agreements;
| update_rec_arcl_dtln
|
| DESCRIPTION
| Account merge procedure for the table, pn_rec_arcl_dtln
| Column updated Corresponding HZ table.column
| -------------------- -----------------------------
| cust_account_id HZ_CUST_ACCOUNTS.cust_account_id
|
| SCOPE - PRIVATE
|
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN: req_id, set_num, process_mode
|
| NOTES :
|
| MODIFICATION HISTORY
|
| 29-apr-2004 Perl Script Created
| 29-apr-2004 Kiran Finalised
+===========================================================================*/
PROCEDURE update_rec_arcl_dtln (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 m.CUSTOMER_MERGE_HEADER_ID
,yt.AREA_CLASS_DTL_ID
,yt.CUST_ACCOUNT_ID
FROM PN_REC_ARCL_DTLLN yt
,RA_CUSTOMER_MERGES m
WHERE yt.CUST_ACCOUNT_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,
ACTION_FLAG,
REQUEST_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
) VALUES (
HZ_CUSTOMER_MERGE_LOG_s.nextval,
'PN_REC_ARCL_DTLLN',
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 PN_REC_ARCL_DTLLN yt SET
CUST_ACCOUNT_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 AREA_CLASS_DTL_ID=PRIMARY_KEY_ID_LIST(I);
arp_message.set_name('AR','AR_ROWS_UPDATED');
arp_message.set_line( 'update_rec_arcl_dtln');
END update_rec_arcl_dtln;
| update_rec_expcl_dtln
|
| DESCRIPTION
| Account merge procedure for the table, pn_rec_expcl_dtln
| Column updated Corresponding HZ table.column
| -------------------- -----------------------------
| cust_account_id HZ_CUST_ACCOUNTS.cust_account_id
|
| SCOPE - PRIVATE
|
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN: req_id, set_num, process_mode
|
| NOTES :
|
| MODIFICATION HISTORY
|
| 29-apr-2004 Perl Script Created
| 29-apr-2004 Kiran Finalised
+===========================================================================*/
PROCEDURE update_rec_expcl_dtln (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 m.CUSTOMER_MERGE_HEADER_ID
,yt.EXPENSE_CLASS_LINE_ID
,yt.CUST_ACCOUNT_ID
FROM PN_REC_EXPCL_DTLLN yt
,RA_CUSTOMER_MERGES M
WHERE yt.CUST_ACCOUNT_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,
ACTION_FLAG,
REQUEST_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
) VALUES (
HZ_CUSTOMER_MERGE_LOG_s.nextval,
'PN_REC_EXPCL_DTLLN',
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 PN_REC_EXPCL_DTLLN yt SET
CUST_ACCOUNT_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 EXPENSE_CLASS_LINE_ID=PRIMARY_KEY_ID_LIST(I);
arp_message.set_name('AR','AR_ROWS_UPDATED');
arp_message.set_line( 'update_rec_expcl_dtln');
END update_rec_expcl_dtln;
| update_rec_period_lines
|
| DESCRIPTION
| Account merge procedure for the table, pn_rec_period_lines
| Column updated Corresponding HZ table.column
| -------------------- -----------------------------
| cust_account_id HZ_CUST_ACCOUNTS.cust_account_id
|
| SCOPE - PRIVATE
|
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN: req_id, set_num, process_mode
|
| NOTES :
|
| MODIFICATION HISTORY
|
| 29-apr-2004 Perl Script Created
| 29-apr-2004 Kiran Finalised
+===========================================================================*/
PROCEDURE update_rec_period_lines (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 m.CUSTOMER_MERGE_HEADER_ID
,yt.REC_PERIOD_LINES_ID
,yt.CUST_ACCOUNT_ID
FROM PN_REC_PERIOD_LINES yt
,RA_CUSTOMER_MERGES m
WHERE yt.CUST_ACCOUNT_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,
ACTION_FLAG,
REQUEST_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
) VALUES (
HZ_CUSTOMER_MERGE_LOG_s.nextval,
'PN_REC_PERIOD_LINES',
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 PN_REC_PERIOD_LINES yt SET
CUST_ACCOUNT_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 REC_PERIOD_LINES_ID=PRIMARY_KEY_ID_LIST(I);
arp_message.set_name('AR','AR_ROWS_UPDATED');
arp_message.set_line( 'update_rec_period_lines');
END update_rec_period_lines;
| update_space_assign_cust
|
| DESCRIPTION
| Account merge procedure for the table, pn_space_assign_cust_all
| Column updated Corresponding HZ table.column
| -------------------- -----------------------------
| cust_account_id HZ_CUST_ACCOUNTS.cust_account_id
| site_use_id HZ_CUST_SITE_USES.site_use_id
|
| SCOPE - PRIVATE
|
| EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN: req_id, set_num, process_mode
|
| NOTES :
|
| MODIFICATION HISTORY
|
| 18-feb-2003 Perl Script Created
| 18-feb-2003 Kiran Hegde Finalised
+===========================================================================*/
PROCEDURE update_space_assign_cust (
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
m.CUSTOMER_MERGE_HEADER_ID
,yt.cust_space_assign_id
,yt.CUST_ACCOUNT_ID
,yt.SITE_USE_ID
FROM PN_SPACE_ASSIGN_CUST yt
,RA_CUSTOMER_MERGES m
WHERE ( yt.CUST_ACCOUNT_ID = m.DUPLICATE_ID
OR 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,
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,
'PN_SPACE_ASSIGN_CUST',
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 PN_SPACE_ASSIGN_CUST yt SET
CUST_ACCOUNT_ID=NUM_COL1_NEW_LIST(I)
,SITE_USE_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 cust_space_assign_id=PRIMARY_KEY_ID_LIST(I);
arp_message.set_name('AR','AR_ROWS_UPDATED');
arp_message.set_line( 'update_space_assign_cust');
END update_space_assign_cust;