[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT NULL
FROM PO_REQUISITION_LINES
WHERE source_type_code = 'INVENTORY'
and deliver_to_location_id in (select location_id
from po_location_associations
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
and racm.customer_id
<> racm.duplicate_id
and racm.customer_id in
(select distinct customer_id
from po_location_associations)))
FOR UPDATE NOWAIT;
be retrieved in select statement later. */
/*
-- bug3648471
-- Rewrote merged_records as the original one did not return the records
-- expected
-- SQL What: Get all the requisitions that has the deliver to location
-- associated with a customer site to be merged, and the target
-- site being already associated with some other location
-- SQL Why: We will update the deliver to location information of the req
-- line, as the associated will be deleted later on.
CURSOR merged_records IS
SELECT m.CUSTOMER_MERGE_HEADER_ID,
yt.REQUISITION_LINE_ID
FROM PO_REQUISITION_LINES yt,
ra_customer_merges m,
po_location_associations pla
WHERE yt.source_type_code = 'INVENTORY'
AND yt.deliver_to_location_id = pla.location_id
AND pla.site_use_id = m.duplicate_site_id
AND m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num
AND EXISTS (SELECT null
FROM po_location_associations pla1
WHERE m.customer_site_id = pla1.site_use_id);
SELECT m.CUSTOMER_MERGE_HEADER_ID,
yt.REQUISITION_LINE_ID,
pla1.location_id, --new
pla2.location_id --old
FROM PO_REQUISITION_LINES yt, ra_customer_merges m,
po_location_associations pla1,po_location_associations pla2
WHERE yt.source_type_code = 'INVENTORY'
and yt.deliver_to_location_id = pla2.location_id
and pla2.site_use_id = m.duplicate_site_id
and m.customer_site_id = pla1.site_use_id
and m.process_flag = 'N'
and m.request_id = req_id
and m.set_number = set_num;
select distinct pla1.location_id
into l_deliver_to_location_id
from po_location_associations pla1,
po_location_associations pla2,
ra_customer_merges racm,
po_requisition_lines yt
where yt.deliver_to_location_id = pla2.location_id
and pla2.site_use_id = racm.duplicate_site_id
and racm.customer_site_id = pla1.site_use_id
and racm.process_flag = 'N'
and racm.request_id = req_id
and racm.set_number = set_num;
Store the value of the column to be updated to local
variables.
*/
/* Bug 4009128 - comment below assignments */
FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
/* NUM_COL1_ORIG_LIST(I) := l_deliver_to_location_id;
If auditing has been enabled, insert the changed records
into the HZ_CUSTOMER_MERGE_LOG table.
*/
IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
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,
'PO_REQUISITION_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 all corresponding deliver_to_location_id of
the affected Requisitions due to merged/changed accounts
*/
/* Bug 4009128 - modified deliver_to_location assignment */
FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
UPDATE PO_REQUISITION_LINES yt
SET -- DELIVER_TO_LOCATION_ID = l_deliver_to_location_id,
DELIVER_TO_LOCATION_ID = NEW_DELIVER_TO_LOC_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 REQUISITION_LINE_ID = PRIMARY_KEY_ID_LIST(I);
/* Number of rows updates */
arp_message.set_name('AR', 'AR_ROWS_UPDATED');
SELECT NULL
FROM PO_LOCATION_ASSOCIATIONS
WHERE site_use_id in (select racm.duplicate_site_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;
CURSOR deleted_records IS
SELECT m.CUSTOMER_MERGE_HEADER_ID,
yt.LOCATION_ID,
m.CUSTOMER_ID,
yt.SITE_USE_ID,
yt.ADDRESS_ID,
yt.ORGANIZATION_ID,
yt.ORG_ID,
yt.VENDOR_ID,
yt.VENDOR_SITE_ID,
yt.SUBINVENTORY,
yt.ATTRIBUTE_CATEGORY,
yt.ATTRIBUTE1,
yt.ATTRIBUTE2,
yt.ATTRIBUTE3,
yt.ATTRIBUTE4,
yt.ATTRIBUTE5,
yt.ATTRIBUTE6,
yt.ATTRIBUTE7,
yt.ATTRIBUTE8,
yt.ATTRIBUTE9,
yt.ATTRIBUTE10,
yt.ATTRIBUTE11,
yt.ATTRIBUTE12,
yt.ATTRIBUTE13,
yt.ATTRIBUTE14,
yt.ATTRIBUTE15
FROM PO_LOCATION_ASSOCIATIONS yt,
ra_customer_merges m
WHERE yt.site_use_id = m.duplicate_site_id -- get the records that
-- need to be merged from
AND m.process_flag = 'N'
AND m.request_id = req_id
AND m.set_number = set_num
AND EXISTS (SELECT null
FROM po_location_associations lc
WHERE lc.site_use_id = m.customer_site_id);
SELECT distinct m.CUSTOMER_MERGE_HEADER_ID,
yt.LOCATION_ID
FROM PO_LOCATION_ASSOCIATIONS yt, ra_customer_merges m
WHERE 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;
SELECT distinct m.CUSTOMER_MERGE_HEADER_ID,
yt.LOCATION_ID,
m.customer_id,
m.customer_site_id
FROM PO_LOCATION_ASSOCIATIONS yt, ra_customer_merges m
where yt.customer_id = m.duplicate_id
and 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;
/* Open the deleted_records cursor */
open deleted_records;
FETCH deleted_records BULK COLLECT INTO
MERGE_HEADER_ID_LIST,
PRIMARY_KEY_ID_LIST,
NUM_COL1_ORIG_LIST,
NUM_COL2_ORIG_LIST,
NUM_COL3_ORIG_LIST,
NUM_COL4_ORIG_LIST,
NUM_COL5_ORIG_LIST,
NUM_COL6_ORIG_LIST,
NUM_COL7_ORIG_LIST,
VCHAR_COL1_ORIG_LIST,
VCHAR_COL2_ORIG_LIST,
VCHAR_COL3_ORIG_LIST,
VCHAR_COL4_ORIG_LIST,
VCHAR_COL5_ORIG_LIST,
VCHAR_COL6_ORIG_LIST,
VCHAR_COL7_ORIG_LIST,
VCHAR_COL8_ORIG_LIST,
VCHAR_COL9_ORIG_LIST,
VCHAR_COL10_ORIG_LIST,
VCHAR_COL11_ORIG_LIST,
VCHAR_COL12_ORIG_LIST,
VCHAR_COL13_ORIG_LIST,
VCHAR_COL14_ORIG_LIST,
VCHAR_COL15_ORIG_LIST,
VCHAR_COL16_ORIG_LIST,
VCHAR_COL17_ORIG_LIST;
IF deleted_records%NOTFOUND THEN
l_last_fetch := TRUE;
Store the value of the column to be updated to local
variables.
*/
FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
NUM_COL1_NEW_LIST(I) := NUM_COL1_ORIG_LIST(I);
If auditing has been enabled, insert the changed records
into the HZ_CUSTOMER_MERGE_LOG table.
*/
IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
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,
DEL_COL21,
DEL_COL22,
DEL_COL23,
DEL_COL24,
ACTION_FLAG,
REQUEST_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY)
VALUES(
HZ_CUSTOMER_MERGE_LOG_s.nextval,
'PO_LOCATION_ASSOCIATIONS',
MERGE_HEADER_ID_LIST(I),
PRIMARY_KEY_ID_LIST(I),
NUM_COL1_ORIG_LIST(I),
NUM_COL2_ORIG_LIST(I),
NUM_COL3_ORIG_LIST(I),
NUM_COL4_ORIG_LIST(I),
NUM_COL5_ORIG_LIST(I),
NUM_COL6_ORIG_LIST(I),
NUM_COL7_ORIG_LIST(I),
VCHAR_COL1_ORIG_LIST(I),
VCHAR_COL2_ORIG_LIST(I),
VCHAR_COL3_ORIG_LIST(I),
VCHAR_COL4_ORIG_LIST(I),
VCHAR_COL5_ORIG_LIST(I),
VCHAR_COL6_ORIG_LIST(I),
VCHAR_COL7_ORIG_LIST(I),
VCHAR_COL8_ORIG_LIST(I),
VCHAR_COL9_ORIG_LIST(I),
VCHAR_COL10_ORIG_LIST(I),
VCHAR_COL11_ORIG_LIST(I),
VCHAR_COL12_ORIG_LIST(I),
VCHAR_COL13_ORIG_LIST(I),
VCHAR_COL14_ORIG_LIST(I),
VCHAR_COL15_ORIG_LIST(I),
VCHAR_COL16_ORIG_LIST(I),
VCHAR_COL17_ORIG_LIST(I),
'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);
Delete all old or redundant records from the affected
Requisitions due to merged/changed accounts
*/
FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
DELETE FROM PO_LOCATION_ASSOCIATIONS yt
WHERE LOCATION_ID = PRIMARY_KEY_ID_LIST(I);
close deleted_records;
/* Number of rows updates */
arp_message.set_name('AR', 'AR_ROWS_UPDATED');
select distinct racm.customer_id,
racm.customer_site_id
into l_customer_id,
l_site_use_id
from ra_customer_merges racm,
PO_LOCATION_ASSOCIATIONS yt
where yt.customer_id = racm.duplicate_id
and yt.site_use_id = racm.duplicate_site_id
and racm.process_flag = 'N'
and racm.request_id = req_id
and racm.set_number = set_num;
Store the value of the column to be updated to local
variables.
*/
FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
/*Bug 4009128 - commented below assignment */
/* NUM_COL1_ORIG_LIST(I) := l_customer_id;
If auditing has been enabled, insert the changed records
into the HZ_CUSTOMER_MERGE_LOG table.
*/
IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
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,
'PO_LOCATION_ASSOCIATIONS',
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 all corresponding customer_id and site_use_id
due to merged/changed accounts
*/
FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
UPDATE PO_LOCATION_ASSOCIATIONS yt
SET CUSTOMER_ID=NEW_CUST_ID_LIST(I), --Bug 4009128
SITE_USE_ID=NEW_CUST_SITE_ID_LIST(I), --Bug 4009128
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 LOCATION_ID = PRIMARY_KEY_ID_LIST(I);
/* Number of rows updates */
arp_message.set_name('AR', 'AR_ROWS_UPDATED');