[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| Veeru Tarikere 07/18/2002 Rewrote Customer_merge, update_quote_lines |
| and update_shipments.Removed Globals |
| |
*----------------------------------------------------------------------------*/
/*----------------------------------------------------------------------------*
| PUBLIC PROCEDURE |
| CUSTOMER_MERGE |
| DESCRIPTION |
| This API should be called from TCA customer merge concurrent |
| program and will merge records in Order Capture tables for |
| customers that being merged. |
| REQUIRES |
| |
| |
| EXCEPTIONS RAISED |
| DIFFERENT_PARTIES -- Raises an exception when the owner |
| parties are different for the cust |
| accounts that are being merged. |
| removed (vtariker) |
| KNOWN BUGS |
| |
| NOTES |
| |
| HISTORY |
| Harish Ekkirala Created 03/27/2001. |
| Vtariker 07/18/2002 Rewrote Customer_Merge |
| |
*----------------------------------------------------------------------------*/
PROCEDURE CUSTOMER_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 /*+ leading(M) use_nl(M,YT) USE_CONCAT */
distinct CUSTOMER_MERGE_HEADER_ID
,QUOTE_HEADER_ID
,CUST_ACCOUNT_ID
,INVOICE_TO_CUST_ACCOUNT_ID
,END_CUSTOMER_CUST_ACCOUNT_ID
FROM ASO_QUOTE_HEADERS yt, ra_customer_merges m
WHERE (
yt.CUST_ACCOUNT_ID = m.DUPLICATE_ID
OR yt.INVOICE_TO_CUST_ACCOUNT_ID = m.DUPLICATE_ID
OR yt.END_CUSTOMER_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,
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,
'ASO_QUOTE_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),
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 ASO_QUOTE_HEADERS yt SET
CUST_ACCOUNT_ID=NUM_COL1_NEW_LIST(I)
,INVOICE_TO_CUST_ACCOUNT_ID=NUM_COL2_NEW_LIST(I)
,END_CUSTOMER_CUST_ACCOUNT_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
, 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 QUOTE_HEADER_ID=PRIMARY_KEY_ID_LIST(I)
;
arp_message.set_name('AR','AR_ROWS_UPDATED');
ASO_MERGE_PVT.UPDATE_QUOTE_LINES(
req_id => req_id,
set_num => set_num,
process_mode => process_mode
);
ASO_MERGE_PVT.UPDATE_SHIPMENTS(
req_id => req_id,
set_num => set_num,
process_mode => process_mode
);
| UPDATE_QUOTE_LINES |
| DESCRIPTION |
| This is a private procedure to update ASO_QUOTE_LINES_ALL |
| table with merged to cust account id. When two cust accounts |
| are merged. |
| REQUIRES |
| |
| |
| EXCEPTIONS RAISED |
| |
| KNOWN BUGS |
| |
| NOTES |
| |
| HISTORY |
| Harish Ekkirala Created 03/27/2001. |
| Vtariker 07/18/2002 Rewrote Update_Quote_lines |
| |
*----------------------------------------------------------------------------*/
PROCEDURE UPDATE_QUOTE_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 CUSTOMER_MERGE_HEADER_ID
,QUOTE_LINE_ID
,INVOICE_TO_CUST_ACCOUNT_ID
,END_CUSTOMER_CUST_ACCOUNT_ID
FROM ASO_QUOTE_LINES yt, ra_customer_merges m
WHERE (
yt.INVOICE_TO_CUST_ACCOUNT_ID = m.DUPLICATE_ID
OR yt.END_CUSTOMER_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,
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,
'ASO_QUOTE_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),
'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 ASO_QUOTE_LINES yt SET
INVOICE_TO_CUST_ACCOUNT_ID=NUM_COL1_NEW_LIST(I)
, END_CUSTOMER_CUST_ACCOUNT_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=req_id
, PROGRAM_APPLICATION_ID=arp_standard.profile.program_application_id
, PROGRAM_ID=arp_standard.profile.program_id
, PROGRAM_UPDATE_DATE=SYSDATE
WHERE QUOTE_LINE_ID=PRIMARY_KEY_ID_LIST(I)
;
arp_message.set_name('AR','AR_ROWS_UPDATED');
arp_message.set_line( 'UPDATE_QUOTE_LINES');
END UPDATE_QUOTE_LINES;
| UPDATE_SHIPMENTS |
| DESCRIPTION |
| This is a private procedure to update ASO_SHIPMENTS |
| table with merged to cust account id. When two cust accounts |
| are merged. |
| REQUIRES |
| |
| |
| EXCEPTIONS RAISED |
| |
| KNOWN BUGS |
| |
| NOTES |
| |
| HISTORY |
| Harish Ekkirala Created 03/27/2001. |
| Vtariker 07/18/2002 Rewrote Update_Shipments |
| |
*----------------------------------------------------------------------------*/
PROCEDURE UPDATE_SHIPMENTS(
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
,SHIPMENT_ID
,SHIP_TO_CUST_ACCOUNT_ID
FROM ASO_SHIPMENTS yt, ra_customer_merges m
WHERE (
yt.SHIP_TO_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,
'ASO_SHIPMENTS',
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 ASO_SHIPMENTS yt SET
SHIP_TO_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
, 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 SHIPMENT_ID=PRIMARY_KEY_ID_LIST(I)
;
arp_message.set_name('AR','AR_ROWS_UPDATED');
arp_message.set_line( 'UPDATE_SHIPMENTS');
END UPDATE_SHIPMENTS;
| should also be updated in iStore tables. |
| This procedure will update ASO_QUOTE_HEADERS_ALL table |
| and will be called from party Merge concurrent program. |
| DESCRIPTION |
| |
| REQUIRES |
| |
| |
| EXCEPTIONS RAISED |
| |
| KNOWN BUGS |
| |
| NOTES |
| |
| HISTORY |
| |
*----------------------------------------------------------------------------*/
PROCEDURE MERGE_QUOTE_HEADERS(
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);
UPDATE ASO_QUOTE_HEADERS_ALL SET
party_id = DECODE(party_id,p_from_fk_id,p_to_fk_id,party_id),
invoice_to_party_id = DECODE(invoice_to_party_id,p_from_fk_id,p_to_fk_id,invoice_to_party_id),
cust_party_id = DECODE(cust_party_id,p_from_fk_id,p_to_fk_id,cust_party_id),
invoice_to_cust_party_id = DECODE(invoice_to_cust_party_id,p_from_fk_id,p_to_fk_id,invoice_to_cust_party_id),
End_Customer_party_id = DECODE(End_Customer_party_id,p_from_fk_id,p_to_fk_id,End_Customer_party_id),
End_Customer_cust_party_id = DECODE(End_Customer_cust_party_id,p_from_fk_id,p_to_fk_id,End_Customer_cust_party_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
OR invoice_to_party_id = p_from_fk_id
OR cust_party_id = p_from_fk_id
OR invoice_to_cust_party_id = p_from_fk_id;
arp_message.set_name('AR', 'AR_ROWS_UPDATED');
UPDATE ASO_QUOTE_HEADERS_ALL SET
invoice_to_party_site_id = DECODE(invoice_to_party_site_id,p_from_fk_id,p_to_fk_id,invoice_to_party_site_id),
End_Customer_party_site_id = DECODE(End_Customer_party_site_id,p_from_fk_id,p_to_fk_id,End_Customer_party_site_id),
sold_to_party_site_id = DECODE(sold_to_party_site_id,p_from_fk_id,p_to_fk_id,sold_to_party_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,
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 invoice_to_party_site_id = p_from_fk_id
OR End_Customer_party_site_id = p_from_fk_id
OR sold_to_party_site_id = p_from_fk_id;
arp_message.set_name('AR', 'AR_ROWS_UPDATED');
UPDATE ASO_QUOTE_HEADERS_ALL SET
org_contact_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 org_contact_id = p_from_fk_id;
arp_message.set_name('AR', 'AR_ROWS_UPDATED');
| should also be updated in iStore tables. |
| This procedure will update ASO_QUOTE_LINES_ALL table |
| and will be called from party Merge concurrent program. |
| DESCRIPTION |
| |
| REQUIRES |
| |
| |
| EXCEPTIONS RAISED |
| |
| KNOWN BUGS |
| |
| NOTES |
| |
| HISTORY |
| |
*----------------------------------------------------------------------------*/
PROCEDURE MERGE_QUOTE_LINES(
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);
UPDATE ASO_QUOTE_LINES_ALL SET
invoice_to_party_id = DECODE(invoice_to_party_id,p_from_fk_id,p_to_fk_id,invoice_to_party_id),
invoice_to_cust_party_id = DECODE(invoice_to_cust_party_id,p_from_fk_id,p_to_fk_id,invoice_to_cust_party_id),
End_Customer_party_id = DECODE(End_Customer_party_id,p_from_fk_id,p_to_fk_id,End_Customer_party_id),
End_Customer_cust_party_id = DECODE(End_Customer_cust_party_id,p_from_fk_id,p_to_fk_id,End_Customer_cust_party_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 invoice_to_party_id = p_from_fk_id
OR invoice_to_cust_party_id = p_from_fk_id
OR End_Customer_cust_party_id = p_from_fk_id;
arp_message.set_name('AR', 'AR_ROWS_UPDATED');
UPDATE ASO_QUOTE_LINES_ALL SET
invoice_to_party_site_id = p_to_fk_id,
End_Customer_party_site_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 invoice_to_party_site_id = p_from_fk_id
OR End_Customer_party_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 ASO_SHIPMENTS table |
| and will be called from party Merge concurrent program. |
| DESCRIPTION |
| |
| REQUIRES |
| |
| |
| EXCEPTIONS RAISED |
| |
| KNOWN BUGS |
| |
| NOTES |
| |
| HISTORY |
| |
*----------------------------------------------------------------------------*/
PROCEDURE MERGE_SHIPMENTS(
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);
UPDATE ASO_SHIPMENTS SET
ship_to_party_id = DECODE(ship_to_party_id,p_from_fk_id,p_to_fk_id,ship_to_party_id),
ship_to_cust_party_id = DECODE(ship_to_cust_party_id,p_from_fk_id,p_to_fk_id,ship_to_cust_party_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 ship_to_party_id = p_from_fk_id
OR ship_to_cust_party_id = p_from_fk_id;
arp_message.set_name('AR', 'AR_ROWS_UPDATED');
UPDATE ASO_SHIPMENTS SET
ship_to_party_site_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 ship_to_party_site_id = p_from_fk_id;
arp_message.set_name('AR', 'AR_ROWS_UPDATED');