[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE UPDATE_SUPPLIER_ACTIVITY(p_dup_trading_partner_id IN NUMBER,
x_return_status IN OUT NOCOPY VARCHAR2,
x_msg_count IN OUT NOCOPY NUMBER,
x_msg_data IN OUT NOCOPY VARCHAR2);
Also, there should not be an update for records that don't refer the 'from'
site being merged as the supplier is still active.
b) Site being merged is the last active site for supplier being merged.
In this case Supplier A will become inactive and all users associated with supplier A
will move to Supplier B.
Case 3 : From supplier(p_dup_vendor_id) and To supplier(p_vendor_id) are different
To supplier site(p_vendor_site_id) is null and copy flag is enabled, i.e. copy
From Supplier Site(p_dup_vendor_site_id) under To Supplier(p_vendor_id).
Implementation Approch :
=======================
Case 1: We will update vendor_site_id, vendor_site code for all tables that have reference
to vendor_site_id.
Case 2: The implementation will be as below for scenario a and b described above,
a) Update vendor_id, trading_partner_id, vendor_site_id
on all tables with reference to these cols
where vendor_id = p_dup_vendor_id
and trading_partner_id = p_dup_trading_partner_id
and (vendor_site_id = p_dup_vendor_site_id ).
The record should not be updated, if the vendor_site_id
is NOT populated.
b) Update vendor_id, trading_partner_id, vendor_site_id,
on all tables with reference to these cols
where vendor_id = p_dup_vendor_id
and trading_partner_id = p_dup_trading_partner_id
and vendor_site_id = p_dup_vendor_site_id
The record should be updated even if vendor_site_id is
not populated.
Case 3: Here new supplier site will be created with the site code same as of
From Supplier Site(p_dup_vendor_site_id) under To Supplier(p_vendor_id).
This is the same as Case 2 but with the same site code.
*/
-- Get the vendor information for the site we're merging to
GET_MERGE_TO_VENDOR_INFO(p_vendor_id => p_vendor_id,
p_vendor_site_id => p_vendor_site_id,
x_return_status => p_return_status,
x_msg_count => p_msg_count,
x_msg_data => p_msg_data,
x_trading_partner_name => x_trading_partner_name,
x_vendor_site_code => x_vendor_site_code);
does not have records for the bids that got updated
due to the merge and hence we might need to insert
extra rows here.
Same logic as in the upgrade
*/
UPDATE_SUPPLIER_ACTIVITY(p_dup_trading_partner_id => p_dup_party_id,
x_return_status => p_return_status,
x_msg_count => p_msg_count,
x_msg_data => p_msg_data);
fnd_file.put_line (fnd_file.log, l_progress|| 'After UPDATE_SUPPLIER_ACTIVITY ...');
fnd_file.put_line (fnd_file.log, l_progress|| ' Before PON_CONTERMS_UTL_PVT.updateDelivOnVendorMerge call.... ');
PON_CONTERMS_UTL_PVT.updateDelivOnVendorMerge(p_dup_vendor_id,
p_dup_vendor_site_id,
p_vendor_id,
p_vendor_site_id,
p_msg_data,
p_msg_count,
p_return_status);
fnd_file.put_line (fnd_file.log, l_progress|| ' After PON_CONTERMS_UTL_PVT.updateDelivOnVendorMerge call.... ');
SELECT count(*)
INTO l_num_active_sites
FROM po_vendor_sites_all
WHERE vendor_id = p_dup_vendor_id
AND vendor_site_id <> p_dup_vendor_site_id
AND nvl(inactive_date, sysdate+1) > sysdate;
-- select the vendor information for the site we're merging to
SELECT pv.vendor_name, pvs.vendor_site_code
INTO x_trading_partner_name, x_vendor_site_code
FROM po_vendors pv, po_vendor_sites_all pvs
WHERE pv.vendor_id = p_vendor_id
AND pv.vendor_id = pvs.vendor_id
AND pvs.vendor_site_id = p_vendor_site_id;
Followling table will be updated while merging at SITE Level.
1. pon_bid_headers
2. pon_bidding_parties
3. pon_party_line_exclusions
4. pon_pf_supplier_formula
*/
l_progress := 301;
in update query for pon_bid_headers.
*/
SELECT party_id
INTO l_trading_partner_id
FROM ap_suppliers
WHERE vendor_id=p_vendor_id;
UPDATE pon_bid_headers pbh1
SET pbh1.vendor_site_id = p_vendor_site_id,
pbh1.vendor_site_code = decode(pbh1.vendor_site_code,null,null,'-1','-1',p_vendor_site_code),
pbh1.last_updated_by = -1,
pbh1.last_update_date = sysdate
WHERE pbh1.trading_partner_id = l_trading_partner_id
AND pbh1.vendor_site_id = p_dup_vendor_site_id
AND NOT EXISTS (SELECT 'DUPLICATE'
FROM pon_bid_headers pbh2
WHERE pbh2.auction_header_id = pbh1.auction_header_id
AND pbh2.vendor_id = pbh1.vendor_id
AND pbh2.trading_partner_contact_id = pbh1.trading_partner_contact_id
AND pbh2.trading_partner_id = pbh1.trading_partner_id
AND pbh2.vendor_site_id = p_vendor_site_id);
SELECT pvs.vendor_site_code
INTO l_dup_vendor_site_code
FROM po_vendors pv, po_vendor_sites_all pvs
WHERE pv.vendor_id = p_dup_vendor_id
AND pv.vendor_id = pvs.vendor_id
AND pvs.vendor_site_id = p_dup_vendor_site_id;
UPDATE pon_bidding_parties pbp1
set pbp1.vendor_site_id = decode(pbp1.vendor_site_id , -1,-1, p_vendor_site_id),
pbp1.vendor_site_code = decode(pbp1.vendor_site_code,'-1','-1',p_vendor_site_code)
WHERE pbp1.trading_partner_id = l_trading_partner_id
AND ((pbp1.vendor_site_id = p_dup_vendor_site_id and pbp1.list_id = -1)
OR
(pbp1.vendor_site_code = l_dup_vendor_site_code and pbp1.list_id <> -1)
OR
( pbp1.vendor_site_code = l_dup_vendor_site_code
and pbp1.auction_header_id is not null
and exists( select 1 from pon_auction_headers_all pah
where pah.auction_header_id = pbp1.auction_header_id
and pah.global_template_flag='Y' )
)
)
AND NOT EXISTS (SELECT 'DUPLICATE'
FROM pon_bidding_parties pbp2
WHERE pbp2.auction_header_id = pbp1.auction_header_id
AND pbp2.list_id = pbp1.list_id
AND pbp2.trading_partner_id = pbp1.trading_partner_id
AND decode(pbp2.vendor_site_code,'-1',p_vendor_site_code,pbp2.vendor_site_code) = p_vendor_site_code
AND decode(pbp2.vendor_site_id,-1, p_vendor_site_id,pbp2.vendor_site_id) = p_vendor_site_id);
UPDATE pon_party_line_exclusions pple1
SET pple1.vendor_site_id = p_vendor_site_id,
pple1.last_updated_by = -1,
pple1.last_update_date = sysdate
WHERE pple1.vendor_site_id = p_dup_vendor_site_id
AND NOT EXISTS (SELECT 'DUPLICATE'
FROM pon_party_line_exclusions pple2
WHERE pple2.auction_header_id = pple1.auction_header_id
AND pple2.line_number = pple1.line_number
AND pple2.trading_partner_id = pple1.trading_partner_id
AND pple2.vendor_site_id = p_vendor_site_id);
UPDATE pon_pf_supplier_formula ppsf1
SET ppsf1.vendor_site_id = p_vendor_site_id
WHERE ppsf1.vendor_site_id = p_dup_vendor_site_id
AND NOT EXISTS (SELECT 'DUPLICATE'
FROM pon_pf_supplier_formula ppsf2
WHERE ppsf2.auction_header_id = ppsf1.auction_header_id
AND ppsf2.trading_partner_id = ppsf1.trading_partner_id
AND ppsf2.vendor_site_id = p_vendor_site_id);
Followling table will be updated while merging at Vendor level.
1. pon_bid_item_prices
2. pon_bid_headers
3. pon_bidding_parties
4. pon_party_line_exclusions
5. pon_pf_supplier_formula
*/
l_progress := 500;
UPDATE pon_bid_item_prices pbip1
SET pbip1.bid_trading_partner_id = p_trading_partner_id,
pbip1.last_updated_by = -1,
pbip1.last_update_date = sysdate
WHERE pbip1.bid_trading_partner_id = p_dup_trading_partner_id
AND pbip1.bid_number IN (SELECT bid_number
FROM pon_bid_headers pbh1
WHERE pbh1.trading_partner_id = p_dup_trading_partner_id
AND pbh1.vendor_id = p_dup_vendor_id
AND pbh1.vendor_site_id= p_dup_vendor_site_id
AND NOT EXISTS (SELECT 'DUPLICATE'
FROM pon_bid_headers pbh2
WHERE pbh2.auction_header_id = pbh1.auction_header_id
AND pbh2.vendor_id = p_vendor_id
AND pbh2.vendor_site_id = p_vendor_site_id
AND pbh2.trading_partner_id = p_trading_partner_id
AND pbh2.trading_partner_contact_id = pbh1.trading_partner_contact_id)
);
UPDATE pon_bid_headers pbh1
SET pbh1.trading_partner_id = p_trading_partner_id,
pbh1.trading_partner_name = p_trading_partner_name,
pbh1.vendor_id = p_vendor_id,
pbh1.vendor_site_id = p_vendor_site_id,
pbh1.vendor_site_code = p_vendor_site_code,
pbh1.last_updated_by = -1,
pbh1.last_update_date = sysdate
WHERE pbh1.trading_partner_id = p_dup_trading_partner_id
AND pbh1.vendor_id = p_dup_vendor_id
AND pbh1.vendor_site_id = p_dup_vendor_site_id
AND NOT EXISTS (SELECT 'DUPLICATE'
FROM pon_bid_headers pbh2
WHERE pbh2.auction_header_id = pbh1.auction_header_id
AND pbh2.vendor_id = p_vendor_id
AND pbh2.vendor_site_id = p_vendor_site_id
AND pbh2.trading_partner_id = p_trading_partner_id
AND pbh2.trading_partner_contact_id = pbh1.trading_partner_contact_id);
SELECT pvs.vendor_site_code
INTO l_dup_vendor_site_code
FROM po_vendors pv, po_vendor_sites_all pvs
WHERE pv.vendor_id = p_dup_vendor_id
AND pv.vendor_id = pvs.vendor_id
AND pvs.vendor_site_id = p_dup_vendor_site_id;
UPDATE pon_bidding_parties pbp1
SET pbp1.trading_partner_id = p_trading_partner_id,
pbp1.trading_partner_name = p_trading_partner_name,
pbp1.vendor_site_code = decode(pbp1.vendor_site_code,'-1','-1',p_vendor_site_code),
pbp1.vendor_site_id = decode(vendor_site_id , -1,-1, p_vendor_site_id),
pbp1.last_updated_by = -1,
pbp1.last_update_date = sysdate
WHERE pbp1.trading_partner_id = p_dup_trading_partner_id
AND ((pbp1.vendor_site_id = p_dup_vendor_site_id and pbp1.list_id = -1)
OR
(pbp1.vendor_site_code = l_dup_vendor_site_code and pbp1.list_id <> -1)
OR
( pbp1.vendor_site_code = l_dup_vendor_site_code
and pbp1.auction_header_id is not null
and exists( select 1 from pon_auction_headers_all pah
where pah.auction_header_id = pbp1.auction_header_id
and pah.global_template_flag='Y' )
)
)
AND NOT EXISTS (SELECT 'DUPLICATE'
FROM pon_bidding_parties pbp2
WHERE pbp2.auction_header_id = pbp1.auction_header_id
AND pbp2.list_id = pbp1.list_id
AND decode(pbp2.vendor_site_code,'-1',p_vendor_site_code,pbp2.vendor_site_code) = p_vendor_site_code
AND pbp2.trading_partner_id = p_trading_partner_id
AND decode(pbp2.vendor_site_id,-1, p_vendor_site_id,pbp2.vendor_site_id) = p_vendor_site_id);
UPDATE pon_party_line_exclusions pple1
SET pple1.trading_partner_id = p_trading_partner_id,
pple1.vendor_site_id = p_vendor_site_id,
pple1.last_updated_by = -1,
pple1.last_update_date = sysdate
WHERE pple1.trading_partner_id = p_dup_trading_partner_id
AND pple1.vendor_site_id = p_dup_vendor_site_id
AND NOT EXISTS (SELECT 'DUPLICATE'
FROM pon_party_line_exclusions pple2
WHERE pple2.auction_header_id = pple1.auction_header_id
AND pple2.trading_partner_id = p_trading_partner_id
AND pple2.vendor_site_id =p_vendor_site_id);
UPDATE pon_pf_supplier_formula ppsf1
SET ppsf1.trading_partner_id = p_trading_partner_id,
ppsf1.vendor_site_id = p_vendor_site_id,
ppsf1.last_updated_by = -1,
ppsf1.last_update_date = sysdate
WHERE ppsf1.trading_partner_id = p_dup_trading_partner_id
AND ppsf1.vendor_site_id = p_dup_vendor_site_id
AND NOT EXISTS (SELECT 'DUPLICATE'
FROM pon_pf_supplier_formula ppsf2
WHERE ppsf2.auction_header_id = ppsf1.auction_header_id
AND ppsf2.trading_partner_id = p_trading_partner_id
AND ppsf2.vendor_site_id = p_vendor_site_id);
Followling table will be updated while merging last site
1. pon_bid_item_prices
2. pon_bid_headers
3. pon_bidding_parties
4. pon_party_line_exclusions
5. pon_pf_supplier_formula
6. pon_supplier_access
7. pon_threads
8. pon_thread_entries
9. pon_te_recipients
10. pon_supplier_activities
11. pon_acknowledgements
*/
l_progress := 701;
UPDATE pon_bid_item_prices pbip1
SET pbip1.bid_trading_partner_id = p_trading_partner_id,
pbip1.last_updated_by = -1,
pbip1.last_update_date = sysdate
WHERE pbip1.bid_trading_partner_id = p_dup_trading_partner_id
AND pbip1.bid_number IN (SELECT bid_number
FROM pon_bid_headers pbh1
WHERE pbh1.trading_partner_id = p_dup_trading_partner_id
AND pbh1.vendor_id = p_dup_vendor_id
AND pbh1.vendor_site_id = decode(pbh1.vendor_site_id,-1,-1,p_dup_vendor_site_id)
AND NOT EXISTS (SELECT 'DUPLICATE'
FROM pon_bid_headers pbh2
WHERE pbh2.auction_header_id = pbh1.auction_header_id
AND pbh2.vendor_id = p_vendor_id
AND pbh2.vendor_site_id= decode(pbh2.vendor_site_id,-1,-1,p_vendor_site_id)
AND pbh2.trading_partner_id = p_trading_partner_id
AND pbh2.trading_partner_contact_id = pbh1.trading_partner_contact_id)
);
UPDATE pon_acknowledgements
SET trading_partner_id = p_trading_partner_id,
last_updated_by = -1,
last_update_date = sysdate
WHERE trading_partner_id = p_dup_trading_partner_id ;
UPDATE pon_bid_headers pbh1
SET pbh1.trading_partner_id = p_trading_partner_id,
pbh1.trading_partner_name = p_trading_partner_name,
pbh1.vendor_id = p_vendor_id,
pbh1.vendor_site_id = decode(pbh1.vendor_site_id,-1,-1,p_vendor_site_id),
pbh1.vendor_site_code = decode(pbh1.vendor_site_code,null,null,'-1','-1',p_vendor_site_code),
pbh1.last_updated_by = -1,
pbh1.last_update_date = sysdate
WHERE pbh1.trading_partner_id = p_dup_trading_partner_id
AND pbh1.vendor_id = p_dup_vendor_id
AND pbh1.vendor_site_id = decode(pbh1.vendor_site_id,-1,-1,p_dup_vendor_site_id)
AND NOT EXISTS (SELECT 'DUPLICATE'
FROM pon_bid_headers pbh2
WHERE pbh2.auction_header_id = pbh1.auction_header_id
AND pbh2.vendor_id = p_vendor_id
AND decode(pbh2.vendor_site_id,-1,p_vendor_site_id,pbh2.vendor_site_id) = p_vendor_site_id
AND decode(pbh2.vendor_site_code,'-1',p_vendor_site_code,null,p_vendor_site_code,pbh2.vendor_site_code) = p_vendor_site_code
AND pbh2.trading_partner_id = p_trading_partner_id
AND pbh2.trading_partner_contact_id = pbh1.trading_partner_contact_id);
SELECT pvs.vendor_site_code
INTO l_dup_vendor_site_code
FROM po_vendors pv, po_vendor_sites_all pvs
WHERE pv.vendor_id = p_dup_vendor_id
AND pv.vendor_id = pvs.vendor_id
AND pvs.vendor_site_id = p_dup_vendor_site_id;
UPDATE pon_bidding_parties pbp1
SET pbp1.trading_partner_id = p_trading_partner_id,
pbp1.trading_partner_name = p_trading_partner_name,
pbp1.vendor_site_id = decode(pbp1.vendor_site_id,-1,-1,p_vendor_site_id),
pbp1.vendor_site_code = decode(pbp1.vendor_site_code,'-1','-1',p_vendor_site_code),
pbp1.last_updated_by = -1,
pbp1.last_update_date = sysdate
WHERE pbp1.trading_partner_id = p_dup_trading_partner_id
AND ((pbp1.vendor_site_id=decode(pbp1.vendor_site_id,-1,-1,p_dup_vendor_site_id) and pbp1.list_id = -1)
OR
(pbp1.vendor_site_code = decode(pbp1.vendor_site_code,'-1','-1',l_dup_vendor_site_code) and pbp1.list_id <> -1)
OR
( pbp1.vendor_site_code = decode(pbp1.vendor_site_code,'-1','-1',l_dup_vendor_site_code)
and pbp1.auction_header_id is not null
and exists( select 1 from pon_auction_headers_all pah
where pah.auction_header_id = pbp1.auction_header_id
and pah.global_template_flag='Y' )
)
)
AND NOT EXISTS (SELECT 'DUPLICATE'
FROM pon_bidding_parties pbp2
WHERE pbp2.auction_header_id = pbp1.auction_header_id
AND pbp2.list_id = pbp1.list_id
AND pbp2.trading_partner_id = p_trading_partner_id
AND decode(pbp2.vendor_site_code,'-1',p_vendor_site_code,pbp2.vendor_site_code) = p_vendor_site_code
AND pbp2.vendor_site_id = decode(pbp1.vendor_site_id,-1,-1,p_vendor_site_id));
UPDATE pon_party_line_exclusions pple1
SET pple1.trading_partner_id = p_trading_partner_id,
pple1.vendor_site_id = decode(pple1.vendor_site_id,-1,-1,p_vendor_site_id)
WHERE pple1.trading_partner_id = p_dup_trading_partner_id
AND pple1.vendor_site_id = decode(pple1.vendor_site_id,-1,-1,p_dup_vendor_site_id)
AND NOT EXISTS (SELECT 'DUPLICATE'
FROM pon_party_line_exclusions pple2
WHERE pple2.auction_header_id = pple1.auction_header_id
AND pple2.trading_partner_id = p_trading_partner_id
AND pple2.vendor_site_id =decode(pple2.vendor_site_id,-1,-1,p_vendor_site_id));
UPDATE pon_pf_supplier_formula ppsf1
SET ppsf1.trading_partner_id = p_trading_partner_id,
ppsf1.vendor_site_id = decode(ppsf1.vendor_site_id,-1,-1,p_vendor_site_id),
ppsf1.last_updated_by = -1,
ppsf1.last_update_date = sysdate
WHERE ppsf1.trading_partner_id = p_dup_trading_partner_id
AND ppsf1.vendor_site_id = decode(ppsf1.vendor_site_id,-1,-1,p_dup_vendor_site_id)
AND NOT EXISTS (SELECT 'DUPLICATE'
FROM pon_pf_supplier_formula ppsf2
WHERE ppsf2.auction_header_id = ppsf1.auction_header_id
AND ppsf2.trading_partner_id = p_trading_partner_id
AND ppsf2.vendor_site_id = decode(ppsf1.vendor_site_id,-1,-1,p_vendor_site_id));
UPDATE pon_supplier_access psa1
SET psa1.supplier_trading_partner_id = p_trading_partner_id,
psa1.last_updated_by = -1,
psa1.last_update_date = sysdate
WHERE psa1.supplier_trading_partner_id = p_dup_trading_partner_id
AND NOT EXISTS (SELECT 'DUPLICATE'
FROM pon_supplier_access psa2
WHERE psa2.auction_header_id_orig_amend = psa1.auction_header_id_orig_amend
AND psa2.supplier_trading_partner_id = p_trading_partner_id);
UPDATE pon_supplier_activities psa1
SET psa1.trading_partner_id = p_trading_partner_id,
psa1.last_updated_by = -1,
psa1.last_update_date = sysdate
WHERE trading_partner_id =p_dup_trading_partner_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.trading_partner_contact_id = psa1.trading_partner_contact_id
AND psa2.last_activity_time = psa1.last_activity_time
AND psa2.trading_partner_id = p_trading_partner_id);
/* Who columns are not in update as we don't have it on table */
UPDATE pon_threads pt
SET pt.owner_party_id = p_trading_partner_id
WHERE pt.owner_party_id = p_dup_trading_partner_id;
/* Who columns are not in update as we don't have it on table */
UPDATE pon_thread_entries pte
SET pte.from_company_id = p_trading_partner_id,
pte.from_company_name = p_trading_partner_name,
pte.vendor_id= p_vendor_id
WHERE pte.vendor_id = p_dup_vendor_id
AND pte.from_company_id = p_dup_trading_partner_id;
/* Who columns are not in update as we don't have it on table */
UPDATE pon_te_recipients ptr
SET ptr.to_company_id = p_trading_partner_id,
ptr.to_company_name = p_trading_partner_name
WHERE ptr.to_company_id = p_dup_trading_partner_id;
PROCEDURE UPDATE_SUPPLIER_ACTIVITY(p_dup_trading_partner_id IN NUMBER,
x_return_status IN OUT NOCOPY VARCHAR2,
x_msg_count IN OUT NOCOPY NUMBER,
x_msg_data IN OUT NOCOPY VARCHAR2)
IS
l_procedure_name VARCHAR2(30) := 'UPDATE_SUPPLIER_ACTIVITY';
INSERT INTO PON_SUPPLIER_ACTIVITIES
(
auction_header_id_orig_amend,
trading_partner_contact_id,
last_activity_time,
auction_header_id,
trading_partner_id,
session_id,
last_activity_code,
last_action_flag,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
)
SELECT
ah.auction_header_id_orig_amend,
bh.trading_partner_contact_id,
bh.creation_date,
bh.auction_header_id,
bh.trading_partner_id,
-1, -- session id
DECODE(bh.bid_status, 'DRAFT', 'CRT_RESP', 'SUBMIT_BID'), -- activity code
'Y', -- last action flag
SYSDATE,
bh.created_by,
SYSDATE,
bh.last_updated_by,
0
FROM pon_auction_headers_all ah,
pon_bid_headers bh
WHERE bh.trading_partner_id = p_dup_trading_partner_id
AND ah.auction_header_id = bh.auction_header_id
AND NOT EXISTS (
SELECT NULL
FROM pon_supplier_activities psa
WHERE psa.auction_header_id_orig_amend = ah.auction_header_id_orig_amend
AND psa.trading_partner_id = bh.trading_partner_id
AND psa.trading_partner_contact_id = bh.trading_partner_contact_id
)
AND NOT EXISTS
(
SELECT NULL
FROM pon_bid_headers bh2,
pon_auction_headers_all ah2
WHERE ah2.auction_header_id = bh2.auction_header_id
AND ah.auction_header_id_orig_amend = ah2.auction_header_id_orig_amend
AND bh2.bid_number > bh.bid_number
AND bh2.trading_partner_contact_id = bh.trading_partner_contact_id
AND bh2.trading_partner_id = bh.trading_partner_id
);
fnd_file.put_line (fnd_file.log, 'In exception - UPDATE_SUPPLIER_ACTIVITY SQLERRM '||SQLERRM);
fnd_file.put_line (fnd_file.log, 'In exception - UPDATE_SUPPLIER_ACTIVITY SQLCODE'||SQLCODE);