[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DISTINCT RACM.CUSTOMER_ID, RACM.DUPLICATE_ID INTO
var_new_customer_id, var_old_customer_id
FROM RA_CUSTOMER_MERGES RACM
WHERE RACM.DUPLICATE_ID = var_customer_id
AND RACM.PROCESS_FLAG = 'N'
AND RACM.SET_NUMBER = set_no;
SELECT DISTINCT PROJECT_ID, CUSTOMER_ID, CONTACT_ID,
PROJECT_CONTACT_TYPE_CODE
INTO dummy_project_id, dummy_customer_id, dummy_contact_id,
dummy_proj_con_type_code
FROM PA_PROJECT_CONTACTS PC
WHERE PC.PROJECT_ID = var_project_id
AND PC.CUSTOMER_ID = var_new_customer_id
AND PC.CONTACT_ID >= 0
AND PC.PROJECT_CONTACT_TYPE_CODE = 'BILLING';
FUNCTION DELETE_BILLING_CONTACTS(
var_project_id IN pa_project_contacts.project_id%TYPE,
var_old_customer_id IN pa_project_contacts.customer_id%TYPE,
var_new_customer_id IN pa_project_contacts.customer_id%TYPE,
var_cust_merge_head_id IN ra_customer_merges.customer_merge_header_id%TYPE,
request_id IN NUMBER)
--
--
RETURN BOOLEAN IS
--
BEGIN
--
/* Added for Tca audit*/
IF pap_cmerge.g_audit_profile = 'Y' THEN
--
-- Inserting the data into HZ_CUSTOMER_MERGE_LOG table for
-- PA_PROJECT_CONTACTS table. All rows are inserted that are going
-- to be deleted for old customer.
--
/*DEL_COL11 added for customer account relation enhancement*/
INSERT INTO hz_customer_merge_log
( MERGE_LOG_ID,
MERGE_HEADER_ID ,
REQUEST_ID,
TABLE_NAME,
PRIMARY_KEY1,
PRIMARY_KEY2,
PRIMARY_KEY3,
PRIMARY_KEY4,
ACTION_FLAG,
DEL_COL1,
DEL_COL2,
DEL_COL3,
DEL_COL4,
DEL_COL5,
DEL_COL6,
DEL_COL7,
DEL_COL8,
DEL_COL9,
DEL_COL10,
DEL_COL11,
CREATED_BY,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN )
(select
HZ_CUSTOMER_MERGE_LOG_S.nextval,
var_cust_merge_head_id,
request_id,
'PA_PROJECT_CONTACTS',
var_project_id,
var_new_customer_id,
PC.contact_id,
PC.project_contact_type_code,
'D',
PC.PROJECT_ID,
PC.CUSTOMER_ID,
PC.CONTACT_ID,
PC.PROJECT_CONTACT_TYPE_CODE,
PC.LAST_UPDATE_DATE ,
PC.LAST_UPDATED_BY,
PC.CREATION_DATE,
PC.CREATED_BY,
PC.LAST_UPDATE_LOGIN ,
PC.RECORD_VERSION_NUMBER ,
PC.BILL_SHIP_CUSTOMER_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
FROM PA_PROJECT_CONTACTS PC
WHERE PC.PROJECT_ID = var_project_id
AND PC.CUSTOMER_ID = var_old_customer_id
AND PC.CONTACT_ID >= 0
AND PC.PROJECT_CONTACT_TYPE_CODE = 'BILLING');
DELETE PA_PROJECT_CONTACTS PC
WHERE PC.PROJECT_ID = var_project_id
AND PC.CUSTOMER_ID = var_old_customer_id
AND PC.CONTACT_ID >= 0
AND PC.PROJECT_CONTACT_TYPE_CODE = 'BILLING';
END DELETE_BILLING_CONTACTS;
/*CURSOR cursor_3 IS SELECT PROJECT_ID, CUSTOMER_ID FROM pa_project_contacts;*/
CURSOR cursor_3 IS SELECT DISTINCT pc.project_id,
PC.CUSTOMER_ID,
PC.CONTACT_ID,
PC.PROJECT_CONTACT_TYPE_CODE,
RACM.CUSTOMER_ID,
RACM.CUSTOMER_MERGE_HEADER_ID,
PC.BILL_SHIP_CUSTOMER_ID /*For customer account relationship*/
FROM pa_project_contacts pc,ra_customer_merges RACM
WHERE RACM.DUPLICATE_ID = PC.CUSTOMER_ID
AND RACM.PROCESS_FLAG = 'N'
AND RACM.SET_NUMBER = set_no
AND RACM.CUSTOMER_ID <> RACM.DUPLICATE_ID
/* Bug 3891382. Added the condition so that the cursor picks projects specific to the
org_id where customer merge has taken place. */
AND EXISTS ( SELECT NULL FROM PA_PROJECTS
WHERE PROJECT_ID = PC.PROJECT_ID );
CURSOR cursor_4 IS SELECT DISTINCT pc.project_id,
PC.CUSTOMER_ID,
PC.CONTACT_ID,
PC.PROJECT_CONTACT_TYPE_CODE,
RACM.CUSTOMER_ID,
RACM.CUSTOMER_MERGE_HEADER_ID,
PC.BILL_SHIP_CUSTOMER_ID /*For customer account relationship*/
FROM pa_project_contacts pc,ra_customer_merges RACM
WHERE RACM.DUPLICATE_ID = PC.BILL_SHIP_CUSTOMER_ID
AND RACM.PROCESS_FLAG = 'N'
AND RACM.SET_NUMBER = set_no
AND RACM.CUSTOMER_ID <> RACM.DUPLICATE_ID
/* Bug 3891382. Added the condition so that the cursor picks projects specific to the
org_id where customer merge has taken place. */
AND EXISTS ( SELECT NULL FROM PA_PROJECTS
WHERE PROJECT_ID = PC.PROJECT_ID );
data_found := DELETE_BILLING_CONTACTS( var_project_id,
var_customer_id,
var_racm_customer_id,
var_cust_merge_header_id,
req_id);
INSERT INTO hz_customer_merge_log
(
MERGE_LOG_ID,
MERGE_HEADER_ID,
REQUEST_ID,
TABLE_NAME,
PRIMARY_KEY1,
PRIMARY_KEY2,
PRIMARY_KEY3,
PRIMARY_KEY4,
NUM_COL1_ORIG,
NUM_COL1_NEW ,
ACTION_FLAG,
CREATED_BY,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN )
(SELECT
HZ_CUSTOMER_MERGE_LOG_S.nextval,
var_cust_merge_header_id,
req_id,
'PA_PROJECT_CONTACTS',
var_project_id,
var_racm_customer_id,
PC.contact_id,
PC.project_contact_type_code,
var_customer_id,
var_racm_customer_id,
'U',
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
FROM pa_project_contacts PC
WHERE PC.PROJECT_ID = var_project_id
AND PC.CUSTOMER_ID = var_customer_id);
UPDATE pa_project_contacts PC
SET CUSTOMER_ID = var_racm_customer_id,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = ARP_STANDARD.PROFILE.USER_ID,
LAST_UPDATE_LOGIN = ARP_STANDARD.PROFILE.LAST_UPDATE_LOGIN
WHERE
PC.PROJECT_ID = var_project_id
AND PC.CUSTOMER_ID = var_customer_id;
/*Done with the update of primary customer.The code to follow will take care
of the cases where the bill_ship_customer has been merged */
OPEN cursor_4;
INSERT INTO hz_customer_merge_log
(
MERGE_LOG_ID,
MERGE_HEADER_ID,
REQUEST_ID,
TABLE_NAME,
PRIMARY_KEY1,
PRIMARY_KEY2,
PRIMARY_KEY3,
PRIMARY_KEY4,
NUM_COL2_ORIG,
NUM_COL2_NEW ,
ACTION_FLAG,
CREATED_BY,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN )
(SELECT
HZ_CUSTOMER_MERGE_LOG_S.nextval,
var_cust_merge_header_id,
req_id,
'PA_PROJECT_CONTACTS',
var_project_id,
var_customer_id,
PC.contact_id,
PC.project_contact_type_code,
var_bill_ship_customer_id,
var_racm_customer_id,
'U',
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
FROM pa_project_contacts PC
WHERE PC.PROJECT_ID = var_project_id
AND PC.CUSTOMER_ID = var_customer_id
AND PC.BILL_SHIP_CUSTOMER_ID=var_bill_ship_customer_id);
UPDATE pa_project_contacts PC
SET BILL_SHIP_CUSTOMER_ID = var_racm_customer_id,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = ARP_STANDARD.PROFILE.USER_ID,
LAST_UPDATE_LOGIN = ARP_STANDARD.PROFILE.LAST_UPDATE_LOGIN
WHERE
PC.PROJECT_ID = var_project_id
AND PC.CUSTOMER_ID = var_customer_id
AND PC.BILL_SHIP_CUSTOMER_ID=var_bill_ship_customer_id;
ARP_MESSAGE.SET_NAME( 'AR', 'AR_ROWS_UPDATED' );
ARP_MESSAGE.SET_NAME( 'AR', 'AR_ROWS_DELETED' );