[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select party_type,party_id into l_party_type,l_party_id
From hz_parties
Where party_id in (Select party_id from hz_cust_accounts
Where cust_account_id = p_customer_id);
Select party_id into l_dup_party_id
From hz_parties
Where party_id in (Select party_id from hz_cust_accounts
Where cust_account_id = p_duplicate_id);
select 'N' into l_allow_merge_flag from dual
where exists (
select u.user_id from fnd_user u, fnd_user_resp_groups g, HZ_RELATIONSHIPS r
where u.user_id = g.user_id and g.responsibility_application_id=671
and u.customer_id= r.party_id and r.object_id = l_party_id
and r.subject_type='PERSON' and r.object_type='ORGANIZATION');
select PARTY_ID INTO l_party_id
from hz_cust_accounts
where cust_account_id=p_account_id;
SELECT distinct CUSTOMER_MERGE_HEADER_ID
,yt.ord_oneclick_id
,yt.CUST_ACCOUNT_ID
,yt.party_id
FROM IBE_ORD_ONECLICK_ALL 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,request_id,PRIMARY_KEY_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,ACTION_FLAG,CREATED_BY,CREATION_DATE,LAST_UPDATE_LOGIN,LAST_UPDATE_DATE,LAST_UPDATED_BY)
select HZ_CUSTOMER_MERGE_LOG_s.nextval,'IBE_ORD_ONECLICK_ALL',MERGE_HEADER_ID_LIST(I)
,req_id,ORD_ONECLICK_ID,OBJECT_VERSION_NUMBER, CUST_ACCOUNT_ID, PARTY_ID, CREATED_BY, CREATION_DATE,
LAST_UPDATED_BY,LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, ENABLED_FLAG, FREIGHT_CODE, PAYMENT_ID,
BILL_TO_PTY_SITE_ID, SHIP_TO_PTY_SITE_ID,ATTRIBUTE_CATEGORY, ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3,
ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11,
ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15, SECURITY_GROUP_ID, REQUEST_ID, PROGRAM_ID,
PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE, ORG_ID, 'D',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 IBE_ORD_ONECLICK_ALL where ORD_ONECLICK_ID=PRIMARY_KEY_ID_LIST(I);
delete IBE_ORD_ONECLICK_ALL
where ORD_ONECLICK_ID=PRIMARY_KEY_ID_LIST(I);
arp_message.set_name('AR','AR_ROWS_UPDATED');
SELECT distinct CUSTOMER_MERGE_HEADER_ID
,yt.SHP_LIST_ID
,yt.CUST_ACCOUNT_ID
,yt.party_id
FROM IBE_SH_SHP_LISTS_ALL 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;
UPDATE IBE_SH_SHP_LISTS_ALL yt SET
CUST_ACCOUNT_ID=NUM_COL1_NEW_LIST(I)
,PARTY_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
, REQUEST_ID=request_id
, PROGRAM_APPLICATION_ID=arp_standard.profile.program_application_id
, PROGRAM_ID=arp_standard.profile.program_id
, PROGRAM_UPDATE_DATE=SYSDATE
WHERE SHP_LIST_ID=PRIMARY_KEY_ID_LIST(I);
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,
'IBE_SH_SHP_LISTS_ALL',
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
);
arp_message.set_name('AR','AR_ROWS_UPDATED');
Select a.quote_header_id, a.cust_account_id, a.party_id, b.quote_name, racm.customer_merge_id
from IBE_ACTIVE_QUOTES_ALL a, ASO_QUOTE_HEADERS_ALL b, RA_CUSTOMER_MERGES RACM
Where a.quote_header_id = b.quote_header_id (+)
and a.party_id = b.party_id (+)
and a.cust_account_id = b.cust_account_id (+)
and a.cust_account_id = racm.duplicate_id
and a.record_type = 'CART'
and RACM.PROCESS_FLAG='N' AND RACM.REQUEST_ID = req_id
and RACM.SET_NUMBER = set_num;
update ASO_QUOTE_HEADERS_ALL
set QUOTE_NAME = 'IBE_PRMT_SC_DEFAULTNAMED'
where quote_header_id = l_from_quote_id
AND quote_name = 'IBE_PRMT_SC_UNNAMED';
INSERT INTO HZ_CUSTOMER_MERGE_LOG (
MERGE_LOG_ID,TABLE_NAME,MERGE_HEADER_ID,PRIMARY_KEY_ID,
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,'ASO_QUOTE_HEADERS_ALL',
l_customer_merge_id,l_from_quote_id,'IBE_PRMT_SC_UNNAMED','IBE_PRMT_SC_DEFAULTNAMED',
'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
);
insert into HZ_CUSTOMER_MERGE_LOG (MERGE_LOG_ID, TABLE_NAME,
MERGE_HEADER_ID,request_id,PRIMARY_KEY_ID,DEL_COL1,DEL_COL2,DEL_COL3,
DEL_COL4,DEL_COL5,DEL_COL6,DEL_COL7,DEL_COL8,DEL_COL9,DEL_COL10,DEL_COL11,ACTION_FLAG,
CREATED_BY,CREATION_DATE,LAST_UPDATE_LOGIN,LAST_UPDATE_DATE,LAST_UPDATED_BY)
select HZ_CUSTOMER_MERGE_LOG_s.nextval,'IBE_ACTIVE_QUOTES_ALL',l_customer_merge_id
,req_id,ACTIVE_QUOTE_ID,PARTY_ID,CUST_ACCOUNT_ID,ORG_ID,CREATED_BY,CREATION_DATE
,LAST_UPDATED_BY,LAST_UPDATE_DATE,OBJECT_VERSION_NUMBER,LAST_UPDATE_LOGIN
,SECURITY_GROUP_ID,QUOTE_HEADER_ID,'D',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 ibe_active_quotes_all where quote_header_id=l_from_quote_id
and cust_account_id = l_from_acct_id and party_id=l_from_party_id;
delete ibe_active_quotes_all
where quote_header_id = l_from_quote_id
and cust_account_id = l_from_acct_id
and party_id=l_from_party_id;
arp_message.set_name('AR','AR_ROWS_UPDATED');
l_program_update_date date;
l_last_updated_by number;
l_last_update_date date;
l_last_update_login number;
l_update_privilege_type_code varchar2(30);
l_delete_flag boolean:=TRUE;
Select distinct customer_merge_header_id,quote_header_id,
i.party_id, RACM.DUPLICATE_ID, RACM.CUSTOMER_ID,quote_sharee_id
from IBE_SH_QUOTE_ACCESS i, RA_CUSTOMER_MERGES RACM
Where i.cust_account_id = RACM.DUPLICATE_ID
AND RACM.PROCESS_FLAG='N'
AND RACM.REQUEST_ID = req_id
AND RACM.SET_NUMBER = set_num;
l_delete_flag:=TRUE;
Select quote_sharee_id, request_id,program_application_id,program_id,program_update_date,
object_version_number,created_by,creation_date,last_updated_by,last_update_date,
last_update_login,quote_header_id,quote_sharee_number,update_privilege_type_code,
security_group_id,party_id,cust_account_id,start_date_active,end_date_active,recipient_name,
contact_point_id
into l_to_quote_sharee_id,l_request_id,l_program_application_id,l_program_id,
l_program_update_date,l_object_version_number,l_created_by,l_creation_date,
l_last_updated_by,l_last_update_date,l_last_update_login,l_quote_header_id,
l_quote_sharee_number,l_update_privilege_type_code,l_security_group_id,l_party_id,
l_cust_account_id,l_start_date_active,l_end_date_active,l_recipient_name,l_contact_point_id
From IBE_SH_QUOTE_ACCESS
Where quote_header_id = l_from_quote_id
and cust_account_id = l_to_acct_id
--if multiple rows exist for with same quote header ID and account ID
and rownum=1
;
l_delete_flag:=FALSE;
/* Delete/end_date since it's a duplicate row in shared cart table
If both has same shared cart, delete row.
Log delete info.*/
--debug: need TCA profile for test
--actual delete
if l_delete_flag then
delete IBE_SH_QUOTE_ACCESS
where quote_header_id = l_from_quote_id
and cust_account_id = l_from_acct_id;
insert into HZ_CUSTOMER_MERGE_LOG (
MERGE_LOG_ID, TABLE_NAME,MERGE_HEADER_ID,PRIMARY_KEY_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,ACTION_FLAG,
REQUEST_ID,CREATED_BY,CREATION_DATE,LAST_UPDATE_LOGIN,LAST_UPDATE_DATE,
LAST_UPDATED_BY)
values(
HZ_CUSTOMER_MERGE_LOG_s.nextval,'IBE_SH_QUOTE_ACCESS',l_customer_merge_header_id,
l_from_quote_sharee_id,l_request_id,l_program_application_id,l_program_id,
l_program_update_date,l_object_version_number,l_created_by,l_creation_date,
l_last_updated_by,l_last_update_date,l_last_update_login,l_quote_header_id,
l_quote_sharee_number,l_update_privilege_type_code,l_security_group_id,l_party_id,
l_cust_account_id,l_start_date_active,l_end_date_active,l_recipient_name,
l_contact_point_id,'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 );
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,
'IBE_SH_QUOTE_ACCESS',l_customer_merge_header_id,l_from_quote_sharee_id,l_from_acct_id,
l_to_acct_id, l_from_party_id, l_to_party_id,'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 IBE_SH_QUOTE_ACCESS ISQ SET
party_id = l_to_party_id,
cust_account_id = l_to_acct_id,
last_update_date = hz_utility_pub.last_update_date,
last_updated_by = hz_utility_pub.user_id,
last_update_login = hz_utility_pub.last_update_login,
request_id = hz_utility_pub.request_id,
program_application_id = hz_utility_pub.program_application_id,
program_id = hz_utility_pub.program_id,
program_update_date = sysdate
Where cust_account_id = l_from_acct_id
And party_id = l_from_party_id
And quote_header_id = l_from_quote_id;
| The Foriegn keys to cust_account_id should also be updated|
| in iStore tables. This procedure will be invoked by |
| Customer Merge concurrent program. |
| DESCRIPTION |
| |
| REQUIRES |
| |
| |
| EXCEPTIONS RAISED |
| |
| KNOWN BUGS |
| |
| NOTES |
| |
| HISTORY |
| Harish Ekkirala Created 11/06/2000. |
| |
*----------------------------------------------------------------------------*/
PROCEDURE CUSTOMER_MERGE(
Request_id NUMBER,
Set_Number NUMBER,
Process_MODE VARCHAR2
)
IS
g_count NUMBER;
SELECT RACM.CUSTOMER_ID,RACM.DUPLICATE_ID,RACM.CUSTOMER_TYPE
FROM RA_CUSTOMER_MERGES RACM
WHERE RACM.PROCESS_FLAG='N'
AND RACM.REQUEST_ID = req_id
AND RACM.SET_NUMBER = set_number;
select profile_option_value from fnd_profile_option_values where profile_option_id in
(select profile_option_id from fnd_profile_options where profile_option_name ='AFLOG_ENABLED')
and ((level_id=10002 and level_value=671) or level_id=10001)
and profile_option_value='Y';
arp_message.set_name('AR','AR_ROWS_UPDATED');
arp_message.set_name('AR','AR_ROWS_UPDATED');
arp_message.set_name('AR','AR_ROWS_UPDATED');
| should also be updated in iStore tables. |
| This procedure will update IBE_SH_SHP_LISTS_ALL table |
| and will be called from party Merge concurrent program. |
| DESCRIPTION |
| |
| REQUIRES |
| |
| |
| EXCEPTIONS RAISED |
| |
| KNOWN BUGS |
| |
| NOTES |
| |
| HISTORY |
| Harish Ekkirala Created 02/12/2001. |
| |
*----------------------------------------------------------------------------*/
PROCEDURE MERGE_SHIP_LISTS(
P_entity_name IN VARCHAR2,
P_from_id IN NUMBER,
X_to_id OUT NOCOPY NUMBER,
P_from_fk_id IN NUMBER,
P_to_fk_id IN NUMBER,
P_parent_entity_name IN VARCHAR2,
P_batch_id IN NUMBER,
P_batch_party_id IN NUMBER,
X_return_status OUT NOCOPY VARCHAR2
)
IS
Cursor C1 is
Select 'X' from
IBE_SH_SHP_LISTS_ALL
Where party_id = p_from_fk_id
for update nowait;
Select merge_reason_code
Into l_merge_reason_code
From hz_merge_batch
Where batch_id = p_batch_id;
UPDATE IBE_SH_SHP_LISTS_ALL isl SET
party_id = p_to_fk_id,
last_update_date = hz_utility_pub.last_update_date,
last_updated_by = hz_utility_pub.user_id,
last_update_login = hz_utility_pub.last_update_login,
request_id = hz_utility_pub.request_id,
program_application_id = hz_utility_pub.program_application_id,
program_id = hz_utility_pub.program_id,
program_update_date = sysdate
Where party_id = p_from_fk_id;
arp_message.set_name('AR', 'AR_ROWS_UPDATED');
| should also be updated in iStore tables. |
| This procedure will update |
| IBE_ORD_ONECLICK_ALL table and will be called from party |
| Merge concurrent program. |
| DESCRIPTION |
| |
| REQUIRES |
| |
| |
| EXCEPTIONS RAISED |
| |
| KNOWN BUGS |
| |
| NOTES |
| |
| HISTORY |
| Harish Ekkirala Created 02/12/2001. |
| |
*----------------------------------------------------------------------------*/
PROCEDURE MERGE_ONECLICK(
P_entity_name IN VARCHAR2,
P_from_id IN NUMBER,
X_to_id OUT NOCOPY NUMBER,
P_from_fk_id IN NUMBER,
P_to_fk_id IN NUMBER,
P_parent_entity_name IN VARCHAR2,
P_batch_id IN NUMBER,
P_batch_party_id IN NUMBER,
X_return_status OUT NOCOPY VARCHAR2
)
IS
Cursor C1 is
Select 'X' from
IBE_ORD_ONECLICK_ALL
where party_id = p_from_fk_id
for update nowait;
Select 'X' from
IBE_ORD_ONECLICK_ALL
Where bill_to_pty_site_id = p_from_fk_id
Or ship_to_pty_site_id = p_from_fk_id
for update nowait;
Select merge_reason_code
Into l_merge_reason_code
From hz_merge_batch
Where batch_id = p_batch_id;
delete IBE_ORD_ONECLICK_ALL
where party_id = p_from_fk_id;
select ord_oneclick_id
into l_ord_oneclick_id
from ibe_ord_oneclick_all
where party_id = p_to_fk_id
and rownum = 1;
-- Lock the table and update the record(s).
arp_message.set_name('AR', 'AR_LOCKING_TABLE');
UPDATE IBE_ORD_ONECLICK_ALL SET
party_id = p_to_fk_id,
last_update_date = hz_utility_pub.last_update_date,
last_updated_by = hz_utility_pub.user_id,
last_update_login = hz_utility_pub.last_update_login
Where party_id = p_from_fk_id;
arp_message.set_name('AR', 'AR_ROWS_UPDATED');
delete IBE_ORD_ONECLICK_ALL
where party_id = p_from_fk_id;
-- Lock the table and update the record(s).
arp_message.set_name('AR', 'AR_LOCKING_TABLE');
UPDATE IBE_ORD_ONECLICK_ALL SET
bill_to_pty_site_id = decode(bill_to_pty_site_id,p_from_fk_id,p_to_fk_id,bill_to_pty_site_id),
ship_to_pty_site_id = decode(ship_to_pty_site_id,p_from_fk_id,p_to_fk_id,ship_to_pty_site_id),
last_update_date = hz_utility_pub.last_update_date,
last_updated_by = hz_utility_pub.user_id,
last_update_login = hz_utility_pub.last_update_login
Where bill_to_pty_site_id= p_from_fk_id
Or ship_to_pty_site_id = p_from_fk_id;
arp_message.set_name('AR', 'AR_ROWS_UPDATED');
| should also be updated in iStore tables. |
| This procedure will update |
| IBE_MSITE_PRTY_ACCSS table and will be called from party |
| Merge concurrent program. |
| DESCRIPTION |
| |
| REQUIRES |
| |
| |
| EXCEPTIONS RAISED |
| |
| KNOWN BUGS |
| |
| NOTES |
| |
| HISTORY |
| Harish Ekkirala Created 02/12/2001. |
| |
*----------------------------------------------------------------------------*/
PROCEDURE MERGE_MSITE_PARTY_ACCESS(
P_entity_name IN VARCHAR2,
P_from_id IN NUMBER,
X_to_id OUT NOCOPY NUMBER,
P_from_fk_id IN NUMBER,
P_to_fk_id IN NUMBER,
P_parent_entity_name IN VARCHAR2,
P_batch_id IN NUMBER,
P_batch_party_id IN NUMBER,
X_return_status OUT NOCOPY VARCHAR2
)
IS
Cursor C1 is
Select 'X' from
IBE_MSITE_PRTY_ACCSS
where party_id = p_from_fk_id
for update nowait;
Select a.msite_id, b.party_access_code
From ibe_msite_prty_accss a, ibe_msites_b b
Where party_id = p_party_id and a.msite_id=b.msite_id and b.site_type = 'I';
UPDATE IBE_MSITE_PRTY_ACCSS
SET party_id = p_to_fk_id,
last_update_date = hz_utility_pub.last_update_date,
last_updated_by = hz_utility_pub.user_id,
last_update_login = hz_utility_pub.last_update_login
Where party_id = p_from_fk_id and exists (
select 1 from IBE_MSITE_PRTY_ACCSS a, IBE_MSITES_B b
where party_id=p_to_fk_id and a.msite_id<>l_msite_id
and a.msite_id = b.msite_id and b.party_access_code = l_party_access_code
and b.site_type = 'I'
);
arp_message.set_name('AR', 'AR_ROWS_UPDATED');
update IBE_MSITE_PRTY_ACCSS
set END_DATE_ACTIVE = trunc(sysdate)
where party_id = p_from_fk_id and msite_id=l_msite_id;
| should also be updated in iStore tables. |
| This procedure will update IBE_SH_QUOTE table |
| and will be called from party Merge concurrent program. |
| DESCRIPTION |
| |
| REQUIRES |
| |
| |
| EXCEPTIONS RAISED |
| |
| KNOWN BUGS |
| |
| NOTES |
| |
| HISTORY |
| Adam Wu Created 12/05/2002. |
| |
*----------------------------------------------------------------------------*/
PROCEDURE MERGE_SHARED_QUOTE(
P_entity_name IN VARCHAR2,
P_from_id IN NUMBER,
X_to_id OUT NOCOPY NUMBER,
P_from_fk_id IN NUMBER,
P_to_fk_id IN NUMBER,
P_parent_entity_name IN VARCHAR2,
P_batch_id IN NUMBER,
P_batch_party_id IN NUMBER,
X_return_status OUT NOCOPY VARCHAR2
)
IS
l_dummy VARCHAR2(1);
Select quote_header_id, cust_account_id from
IBE_SH_QUOTE_ACCESS
Where party_id = p_from_fk_id
for update nowait;
select cust_account_id
from hz_cust_accounts
where party_id=p_party_id and rownum=1
for update nowait;
Select party_id into l_party_id
From IBE_SH_QUOTE_ACCESS
Where party_id = p_to_fk_id
and cust_account_id = l_to_cust_account_id
And quote_header_id = l_quote_header_id;
delete IBE_SH_QUOTE_ACCESS
where quote_header_id = l_quote_header_id
and party_id = p_from_fk_id
and cust_account_id = l_from_cust_account_id;
UPDATE IBE_SH_QUOTE_ACCESS SET
party_id = p_to_fk_id,
last_update_date = hz_utility_pub.last_update_date,
last_updated_by = hz_utility_pub.user_id,
last_update_login = hz_utility_pub.last_update_login,
request_id = hz_utility_pub.request_id,
program_application_id = hz_utility_pub.program_application_id,
program_id = hz_utility_pub.program_id,
program_update_date = sysdate
Where party_id = p_from_fk_id
And cust_account_id = l_from_cust_account_id
And quote_header_id = l_quote_header_id;
select 1
into l_dummy
from hz_contact_points
where contact_point_id=p_from_fk_id and owner_table_name<>'IBE_SH_QUOTE_ACCESS';
UPDATE IBE_SH_QUOTE_ACCESS SET
contact_point_id = p_to_fk_id,
last_update_date = hz_utility_pub.last_update_date,
last_updated_by = hz_utility_pub.user_id,
last_update_login = hz_utility_pub.last_update_login,
request_id = hz_utility_pub.request_id,
program_application_id = hz_utility_pub.program_application_id,
program_id = hz_utility_pub.program_id,
program_update_date = sysdate
Where contact_point_id = p_from_fk_id;
arp_message.set_name('AR', 'AR_ROWS_UPDATED');
| should also be updated in iStore tables. |
| This procedure will update IBE_ACTIVE_QUOTES_ALL table |
| and will be called from party Merge concurrent program. |
| DESCRIPTION |
| |
| REQUIRES |
| |
| |
| EXCEPTIONS RAISED |
| |
| KNOWN BUGS |
| |
| NOTES |
| |
| HISTORY |
| Adam Wu Created 12/05/2002. |
| |
*----------------------------------------------------------------------------*/
procedure MERGE_ACTIVE_QUOTE(
P_entity_name IN VARCHAR2,
P_from_id IN NUMBER,
X_to_id OUT NOCOPY NUMBER,
P_from_fk_id IN NUMBER,
P_to_fk_id IN NUMBER,
P_parent_entity_name IN VARCHAR2,
P_batch_id IN NUMBER,
P_batch_party_id IN NUMBER,
X_return_status OUT NOCOPY VARCHAR2
)
IS
l_merge_reason_code VARCHAR2(30);
Select a.quote_header_id, a.cust_account_id, b.quote_name
from IBE_ACTIVE_QUOTES_ALL a, ASO_QUOTE_HEADERS_ALL b
Where a.quote_header_id = b.quote_header_id (+) and a.party_id = b.party_id (+)
and a.cust_account_id=b.cust_account_id (+) and a.party_id=P_from_fk_id
for update nowait;
update ASO_QUOTE_HEADERS_ALL
set QUOTE_NAME = 'IBE_PRMT_SC_DEFAULTNAMED'
where quote_header_id = l_quote_header_id
AND quote_name = 'IBE_PRMT_SC_UNNAMED';
delete ibe_active_quotes_all
where quote_header_id = l_quote_header_id
and cust_account_id = l_cust_account_id
and party_id=P_from_fk_id;
arp_message.set_name('AR', 'AR_ROWS_UPDATED');