[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'BUYER' AS USER_TYPE
FROM
HZ_PARTIES USER_PARTIES,
HZ_RELATIONSHIPS,
HZ_CODE_ASSIGNMENTS
WHERE USER_PARTIES.PARTY_ID = p_user_party_id
AND HZ_RELATIONSHIPS.SUBJECT_ID = USER_PARTIES.PARTY_ID
AND HZ_RELATIONSHIPS.RELATIONSHIP_TYPE = 'POS_EMPLOYMENT'
AND HZ_RELATIONSHIPS.RELATIONSHIP_CODE = 'EMPLOYEE_OF'
AND HZ_RELATIONSHIPS.START_DATE <= SYSDATE
AND HZ_RELATIONSHIPS.END_DATE >= SYSDATE
AND HZ_CODE_ASSIGNMENTS.OWNER_TABLE_ID = HZ_RELATIONSHIPS.OBJECT_ID
AND HZ_CODE_ASSIGNMENTS.OWNER_TABLE_NAME = 'HZ_PARTIES'
AND HZ_CODE_ASSIGNMENTS.CLASS_CATEGORY = 'POS_PARTICIPANT_TYPE'
AND HZ_CODE_ASSIGNMENTS.CLASS_CODE = 'ENTERPRISE'
UNION
SELECT 'SELLER' AS USER_TYPE
FROM
POS_SUPPLIER_USERS_V
WHERE PERSON_PARTY_ID = p_user_party_id;
SELECT 'BUYER' AS COMPANY_TYPE
FROM
HZ_PARTIES USER_PARTIES,
HZ_CODE_ASSIGNMENTS
WHERE USER_PARTIES.PARTY_ID = p_party_id
AND USER_PARTIES.PARTY_TYPE = 'ORGANIZATION'
AND HZ_CODE_ASSIGNMENTS.OWNER_TABLE_ID = USER_PARTIES.PARTY_ID
AND HZ_CODE_ASSIGNMENTS.OWNER_TABLE_NAME = 'HZ_PARTIES'
AND HZ_CODE_ASSIGNMENTS.CLASS_CATEGORY = 'POS_PARTICIPANT_TYPE'
AND HZ_CODE_ASSIGNMENTS.CLASS_CODE = 'ENTERPRISE'
UNION
SELECT 'SELLER' AS COMPANY_TYPE
FROM
PO_VENDORS
WHERE PARTY_ID = p_party_id;
SELECT
fu.USER_NAME
INTO
l_user_name
FROM FND_USER fu,
HZ_PARTIES hz
WHERE fu.PERSON_PARTY_ID=hz.PARTY_ID
AND hz.PARTY_ID= p_user_party_id
AND ROWNUM = 1;
SELECT
fu.USER_ID
INTO
l_user_id
FROM FND_USER fu,
HZ_PARTIES hz
WHERE fu.PERSON_PARTY_ID=hz.PARTY_ID
AND hz.PARTY_ID= p_user_party_id
AND ROWNUM = 1;
SELECT
COUNT(fu.USER_NAME)
INTO
l_user_name_count
FROM FND_USER fu,
HZ_PARTIES hz
WHERE fu.PERSON_PARTY_ID=hz.PARTY_ID
AND hz.PARTY_ID= p_user_party_id;
UPDATE PON_NEG_TEAM_MEMBERS pntm
SET pntm.USER_ID = l_to_user_id
WHERE pntm.USER_ID = l_from_user_id
AND NOT EXISTS (SELECT 'DUPLICATE'
FROM PON_NEG_TEAM_MEMBERS pntm1
WHERE pntm.auction_header_id = pntm1.auction_header_id
AND pntm1.USER_ID = l_to_user_id);
fnd_file.put_line (fnd_file.log, '50 : Buyer Merge - Updated USER IDs in PON_NEG_TEAM_MEMBERS: '||sql%rowcount||' rows');
UPDATE PON_AUCTION_HEADERS_ALL
SET TRADING_PARTNER_CONTACT_ID = p_To_FK_id,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = -1,
TRADING_PARTNER_CONTACT_NAME = l_user_name
WHERE TRADING_PARTNER_CONTACT_ID = p_From_FK_id;
fnd_file.put_line (fnd_file.log, '50 : Buyer Merge - Updated TPC IDs in PON_AUCTION_HEADERS_ALL: '||sql%rowcount||' rows');
UPDATE PON_DISCUSSIONS
SET OWNER_PARTY_ID = p_To_FK_id,
LAST_UPDATE_DATE = SYSDATE
WHERE OWNER_PARTY_ID = p_From_FK_id;
fnd_file.put_line (fnd_file.log, '60 : Buyer Merge - Updated TPC IDs in PON_DISCUSSIONS');
UPDATE PON_AUCTION_HEADERS_ALL
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = -1,
DRAFT_LOCKED_BY_CONTACT_ID = p_To_FK_id
WHERE DRAFT_LOCKED_BY_CONTACT_ID = p_From_FK_id;
fnd_file.put_line (fnd_file.log, '40 : Buyer Merge - Updated DRAFT_LOCKED_BY_CONTACT_IDs in PON_AUCTION_HEADERS_ALL: '||sql%rowcount||' rows');
UPDATE PON_AUCTION_HEADERS_ALL
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = -1,
DRAFT_UNLOCKED_BY_CONTACT_ID = p_To_FK_id
WHERE DRAFT_UNLOCKED_BY_CONTACT_ID = p_From_FK_id;
fnd_file.put_line (fnd_file.log, '40 : Buyer Merge - Updated DRAFT_UNLOCKED_BY_CONTACT_IDs in PON_AUCTION_HEADERS_ALL: '||sql%rowcount||' rows');
UPDATE PON_AUCTION_HEADERS_ALL
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = -1,
SCORING_LOCK_TP_CONTACT_ID = p_To_FK_id
WHERE SCORING_LOCK_TP_CONTACT_ID = p_From_FK_id;
fnd_file.put_line (fnd_file.log, '40 : Buyer Merge - Updated SCORING_LOCK_TP_CONTACT_IDs in PON_AUCTION_HEADERS_ALL: '||sql%rowcount||' rows');
SELECT
hz.person_first_name ||' '|| hz.person_last_name as person_name
INTO
l_person_name
FROM HZ_PARTIES hz
WHERE hz.PARTY_ID= p_To_FK_id;
UPDATE PON_AUCTION_EVENTS
SET TRADING_PARTNER_CONTACT_ID = p_To_FK_id,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = -1,
TRADING_PARTNER_CONTACT_NAME = l_person_name
WHERE TRADING_PARTNER_CONTACT_ID = p_From_FK_id;
fnd_file.put_line (fnd_file.log, '50 : Buyer Merge - Updated TRADING_PARTNER_CONTACT_IDs in PON_AUCTION_EVENTS: '||sql%rowcount||' rows');
UPDATE PON_BIDDERS_LISTS
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = -1,
TRADING_PARTNER_CONTACT_ID = p_To_FK_id
WHERE TRADING_PARTNER_CONTACT_ID = p_From_FK_id;
fnd_file.put_line (fnd_file.log, '50 : Buyer Merge - Updated TPC IDs in PON_BIDDERS_LISTS: '||sql%rowcount||' rows');
UPDATE PON_ATTRIBUTE_LISTS
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = -1,
TRADING_PARTNER_CONTACT_ID = p_To_FK_id
WHERE TRADING_PARTNER_CONTACT_ID = p_From_FK_id;
fnd_file.put_line (fnd_file.log, '50 : Buyer Merge - Updated TPC IDs in PON_ATTRIBUTE_LISTS: '||sql%rowcount||' rows');
UPDATE PON_BID_HEADERS
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = -1,
SURROG_BID_CREATED_CONTACT_ID = p_To_FK_id
WHERE SURROG_BID_CREATED_CONTACT_ID = p_From_FK_id;
fnd_file.put_line (fnd_file.log, '50 : Buyer Merge - Updated SURROG_BID_CREATED_CONTACT_IDs in PON_BID_HEADERS: '||sql%rowcount||' rows');
UPDATE PON_BID_HEADERS
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = -1,
SCORE_OVERRIDE_TP_CONTACT_ID = p_To_FK_id
WHERE SCORE_OVERRIDE_TP_CONTACT_ID = p_From_FK_id;
fnd_file.put_line (fnd_file.log, '50 : Buyer Merge - Updated SCORE_OVERRIDE_TP_CONTACT_IDs in PON_BID_HEADERS: '||sql%rowcount||' rows');
UPDATE PON_BID_HEADERS
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = -1,
SHORTLIST_TPC_ID = p_To_FK_id
WHERE SHORTLIST_TPC_ID = p_From_FK_id;
fnd_file.put_line (fnd_file.log, '50 : Buyer Merge - Updated SHORTLIST_TPC_IDs in PON_BID_HEADERS: '||sql%rowcount||' rows');
UPDATE PON_CONTRACTS
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = -1,
AUTHORING_PARTY_CONTACT_ID = p_To_FK_id
WHERE AUTHORING_PARTY_CONTACT_ID = p_From_FK_id;
fnd_file.put_line (fnd_file.log, '50 : Buyer Merge - Updated AUTHORING_PARTY_CONTACT_IDs in PON_CONTRACTS: '||sql%rowcount||' rows');
UPDATE PON_THREADS
SET LAST_UPDATE_DATE = SYSDATE,
OWNER_PARTY_ID = p_To_FK_id
WHERE OWNER_PARTY_ID = p_From_FK_id;
fnd_file.put_line (fnd_file.log, '50 : Buyer Merge - Updated OWNER_PARTY_IDs in PON_THREADS: '||sql%rowcount||' rows');
SELECT
hz.PERSON_FIRST_NAME,
hz.PERSON_LAST_NAME
INTO
l_first_name,
l_last_name
FROM HZ_PARTIES hz
WHERE hz.PARTY_ID= p_To_FK_id;
UPDATE PON_THREAD_ENTRIES
SET FROM_ID = p_To_FK_id,
FROM_FIRST_NAME= l_first_name,
FROM_LAST_NAME=l_last_name
WHERE FROM_ID = p_From_FK_id;
fnd_file.put_line (fnd_file.log, '50 : Buyer Merge - Updated FROM_IDs in PON_THREAD_ENTRIES: '||sql%rowcount||' rows');
SELECT
hz.PERSON_FIRST_NAME,
hz.PERSON_LAST_NAME
INTO
l_first_name,
l_last_name
FROM HZ_PARTIES hz
WHERE hz.PARTY_ID= p_To_FK_id;
UPDATE PON_TE_RECIPIENTS te
SET te.TO_ID = p_To_FK_id,
te.TO_FIRST_NAME = l_first_name,
te.TO_LAST_NAME = l_last_name
WHERE te.TO_ID = p_From_FK_id
AND NOT EXISTS (SELECT 'duplicate'
FROM PON_TE_RECIPIENTS te1
WHERE te1.ENTRY_ID = te.ENTRY_ID
AND te1.TO_ID = p_To_FK_id);
fnd_file.put_line (fnd_file.log, '50 : Buyer Merge - Updated TO_IDs in PON_TE_RECIPIENTS: '||sql%rowcount||' rows');
UPDATE PON_ACKNOWLEDGEMENTS
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = -1,
SURROG_BID_ACK_CONTACT_ID = p_To_FK_id
WHERE SURROG_BID_ACK_CONTACT_ID = p_From_FK_id;
fnd_file.put_line (fnd_file.log, '50 : Buyer Merge - Updated SURROG_BID_ACK_CONTACT_IDs in PON_ACKNOWLEDGEMENTS: '||sql%rowcount||' rows');
UPDATE PON_SUPPLIER_ACCESS
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = -1,
BUYER_TP_CONTACT_ID = p_To_FK_id
WHERE BUYER_TP_CONTACT_ID = p_From_FK_id;
fnd_file.put_line (fnd_file.log, '50 : Buyer Merge - Updated BUYER_TP_CONTACT_IDs in PON_SUPPLIER_ACCESS: '||sql%rowcount||' rows');
SELECT
hz.person_last_name ||', '|| hz.person_first_name as person_name
INTO
l_person_name
FROM HZ_PARTIES hz
WHERE hz.PARTY_ID= p_To_FK_id;
UPDATE PON_BIDDING_PARTIES
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = -1,
TRADING_PARTNER_CONTACT_ID = p_To_FK_id,
TRADING_PARTNER_CONTACT_NAME = l_person_name
WHERE TRADING_PARTNER_CONTACT_ID = p_From_FK_id;
fnd_file.put_line (fnd_file.log, '50 : Seller Merge - Updated TRADING_PARTNER_CONTACT_IDs in PON_BIDDING_PARTIES: '||sql%rowcount||' rows');
SELECT
hz.person_last_name ||', '|| hz.person_first_name as person_name
INTO
l_person_name
FROM HZ_PARTIES hz
WHERE hz.PARTY_ID= p_To_FK_id;
UPDATE PON_BIDDING_PARTIES
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = -1,
ACK_PARTNER_CONTACT_ID = p_To_FK_id,
ACK_PARTNER_CONTACT_NAME = l_person_name
WHERE ACK_PARTNER_CONTACT_ID = p_From_FK_id;
fnd_file.put_line (fnd_file.log, '50 : Seller Merge - Updated ACK_PARTNER_CONTACT_IDs in PON_BIDDING_PARTIES: '||sql%rowcount||' rows');
UPDATE PON_BID_HEADERS
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = -1,
DRAFT_UNLOCKED_BY_CONTACT_ID = p_To_FK_id
WHERE DRAFT_UNLOCKED_BY_CONTACT_ID = p_From_FK_id;
fnd_file.put_line (fnd_file.log, '50 : Seller Merge - Updated DRAFT_UNLOCKED_BY_CONTACT_IDs in PON_BID_HEADERS: '||sql%rowcount||' rows');
UPDATE PON_BID_HEADERS
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = -1,
DRAFT_LOCKED_BY_CONTACT_ID = p_To_FK_id
WHERE DRAFT_LOCKED_BY_CONTACT_ID = p_From_FK_id;
fnd_file.put_line (fnd_file.log, '50 : Seller Merge - Updated DRAFT_LOCKED_BY_CONTACT_IDs in PON_BID_HEADERS: '||sql%rowcount||' rows');
UPDATE PON_BID_HEADERS pb
SET pb.LAST_UPDATE_DATE = SYSDATE,
pb.LAST_UPDATED_BY = -1,
pb.TRADING_PARTNER_CONTACT_ID = p_To_FK_id,
pb.TRADING_PARTNER_CONTACT_NAME = l_user_name
WHERE pb.TRADING_PARTNER_CONTACT_ID = p_From_FK_id
AND NOT EXISTS (SELECT 'DUPLICATE'
FROM PON_BID_HEADERS pb1
WHERE pb1.AUCTION_HEADER_ID = pb.AUCTION_HEADER_ID
AND pb1.TRADING_PARTNER_ID <> pb.TRADING_PARTNER_ID
AND pb1.TRADING_PARTNER_CONTACT_ID = p_To_FK_id
AND pb1.BID_STATUS = 'ACTIVE');
fnd_file.put_line (fnd_file.log, '50 : Seller Merge - Updated TRADING_PARTNER_CONTACT_IDs in PON_BID_HEADERS: '||sql%rowcount||' rows');
UPDATE PON_OPTIMIZE_CONSTRAINTS
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = -1,
TRADING_PARTNER_CONTACT_ID = p_To_FK_id
WHERE TRADING_PARTNER_CONTACT_ID = p_From_FK_id;
fnd_file.put_line (fnd_file.log, '50 : Seller Merge - Updated TRADING_PARTNER_CONTACT_IDs in PON_OPTIMIZE_CONSTRAINTS: '||sql%rowcount||' rows');
UPDATE PON_ACKNOWLEDGEMENTS pa
SET pa.LAST_UPDATE_DATE = SYSDATE,
pa.LAST_UPDATED_BY = -1,
pa.TRADING_PARTNER_CONTACT_ID = p_To_FK_id
WHERE pa.TRADING_PARTNER_CONTACT_ID = p_From_FK_id
AND NOT EXISTS ( SELECT 'DUPLICATE'
FROM PON_ACKNOWLEDGEMENTS pa1
WHERE pa1.AUCTION_HEADER_ID = pa.AUCTION_HEADER_ID
AND pa1.TRADING_PARTNER_CONTACT_ID = p_To_FK_id );
fnd_file.put_line (fnd_file.log, '50 : Seller Merge - Updated TRADING_PARTNER_CONTACT_IDs in PON_ACKNOWLEDGEMENTS: '||sql%rowcount||' rows');
UPDATE PON_AUCTION_SUMMARY
SET TRADING_PARTNER_CONTACT_ID = p_To_FK_id
WHERE TRADING_PARTNER_CONTACT_ID = p_From_FK_id;
fnd_file.put_line (fnd_file.log, '50 : Seller Merge - Updated TRADING_PARTNER_CONTACT_IDs in PON_AUCTION_SUMMARY: '||sql%rowcount||' rows');
UPDATE PON_SUPPLIER_ACTIVITIES psa1
SET psa1.TRADING_PARTNER_CONTACT_ID = p_To_FK_id,
psa1.LAST_UPDATED_BY = -1,
psa1.LAST_UPDATE_DATE = sysdate
WHERE psa1.TRADING_PARTNER_CONTACT_ID = p_From_FK_id
AND NOT EXISTS (SELECT 'DUPLICATE'
FROM PON_SUPPLIER_ACTIVITIES psa2
WHERE psa2.AUCTION_HEADER_ID_ORIG_AMEND = psa1.AUCTION_HEADER_ID_ORIG_AMEND
AND psa2.LAST_ACTIVITY_TIME = psa1.LAST_ACTIVITY_TIME
AND psa2.TRADING_PARTNER_ID = psa1.TRADING_PARTNER_ID
AND psa2.TRADING_PARTNER_CONTACT_ID = p_To_FK_id );
fnd_file.put_line (fnd_file.log, '50 : Seller Merge - Updated TRADING_PARTNER_CONTACT_IDs in PON_SUPPLIER_ACTIVITIES: '||sql%rowcount||' rows');