[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
-- The profile option is updateable at all levels, and the current default is 6 months.
FUNCTION get_txn_history_range_profile
RETURN PLS_INTEGER IS
l_txn_history_range_in_months PLS_INTEGER ;
SELECT COUNT(DISTINCT pbp.auction_header_id ) total_invited
INTO l_tot_inv
FROM pon_bidding_parties pbp,
pon_auction_headers pah
WHERE pbp.list_id = -1
AND pbp.trading_partner_id = p_tp_id
AND pah.auction_header_id = pbp.auction_header_id
AND pah.auction_status IN ('ACTIVE', 'AUCTION_CLOSED')
AND NVL(pah.is_template_flag, 'N') = 'N'
AND pah.creation_date >= ADD_MONTHS( TRUNC(SYSDATE) ,-(l_txn_history_range_in_months));
SELECT COUNT( DISTINCT(pbh.auction_header_id)) total_invited
INTO l_tot_inv
FROM pon_bid_headers pbh,
pon_auction_headers pah,
pon_bidding_parties pbp
WHERE pbh.trading_partner_id = p_tp_id
AND pbh. bid_status IN ('ACTIVE')
AND pah.auction_header_id = pbh.auction_header_id
AND pah.auction_status IN ('ACTIVE', 'AUCTION_CLOSED')
AND NVL(pah.is_template_flag, 'N') = 'N'
AND pbh.auction_header_id = pbp.auction_header_id
AND pbh.trading_partner_id = pbp.trading_partner_id
AND pbp.list_id = -1
AND pbh.creation_date >= ADD_MONTHS(TRUNC(SYSDATE), -(l_txn_history_range_in_months));
SELECT COUNT( DISTINCT(pbh.auction_header_id)) total_uninvited
INTO l_tot_uninv
FROM pon_bid_headers pbh,
pon_auction_headers pah
WHERE pbh.trading_partner_id = p_tp_id
AND bid_status IN ('ACTIVE')
AND pah.auction_header_id = pbh.auction_header_id
AND pah.auction_status IN ('ACTIVE', 'AUCTION_CLOSED')
AND NVL(pah.is_template_flag, 'N') = 'N'
AND pbh.auction_header_id NOT IN -- invited parties list
( SELECT pbp.auction_header_id
FROM pon_bidding_parties pbp
WHERE pbh.trading_partner_id = pbp.trading_partner_id
AND pbp.list_id = -1)
AND pbh.creation_date >= ADD_MONTHS(TRUNC(SYSDATE), -(l_txn_history_range_in_months));
SELECT COUNT(DISTINCT pbh.auction_header_id) total_awarded
INTO l_tot_awd
FROM pon_bid_headers pbh,
pon_auction_headers pah
WHERE pbh.trading_partner_id = p_tp_id
AND pah.auction_header_id = pbh.auction_header_id
AND pah.auction_status IN ('ACTIVE', 'AUCTION_CLOSED')
AND NVL(pah.is_template_flag, 'N') = 'N'
AND NVL(pbh.award_status,'NA') IN ('AWARDED', 'PARTIAL')
AND bid_status IN ('ACTIVE')
AND pbh.creation_date >= ADD_MONTHS(TRUNC(SYSDATE), -(l_txn_history_range_in_months));
SELECT SUM(DECODE(type_lookup_code, 'STANDARD', 1,0)) total_spo,
SUM(DECODE(type_lookup_code, 'BLANKET', 1,0)) total_bpa,
SUM(DECODE(type_lookup_code, 'CONTRACT', 1,0)) total_cpa
INTO l_total_spo,
l_total_bpa,
l_total_cpa
FROM po_vendors pv,
hz_parties hp,
po_headers poh
WHERE hp.party_id = p_tp_id
AND poh.vendor_id = pv.vendor_id
AND poh.authorization_status NOT IN
( 'IN PROCESS','INCOMPLETE','REQUIRES REAPPROVAL')
AND pv.party_id = hp.party_id
AND poh.creation_date >= ADD_MONTHS(TRUNC(SYSDATE), -(l_txn_history_range_in_months));
SELECT SUM(DECODE(type_lookup_code, 'STANDARD', 1,0)) total_spo,
SUM(DECODE(type_lookup_code, 'BLANKET', 1,0)) total_bpa,
SUM(DECODE(type_lookup_code, 'CONTRACT', 1,0)) total_cpa
INTO l_total_spo,
l_total_bpa,
l_total_cpa
FROM po_vendors pv,
po_headers poh
WHERE pv.vendor_id = p_vendor_id
AND poh.vendor_id = pv.vendor_id
AND poh.authorization_status NOT IN
( 'IN PROCESS','INCOMPLETE','REQUIRES REAPPROVAL')
AND poh.creation_date >= ADD_MONTHS(TRUNC(SYSDATE), -(l_txn_history_range_in_months));
SELECT COUNT(*) total_po_releases
INTO l_tot_po_rel
FROM po_vendors pv,
hz_parties hp,
po_releases_all por,
po_headers poh
WHERE hp.party_id = p_tp_id
AND poh.PO_HEADER_ID = por.PO_HEADER_ID
AND por.release_type = 'BLANKET'
AND poh.authorization_status NOT IN
( 'IN PROCESS','INCOMPLETE','REQUIRES REAPPROVAL')
AND por.authorization_status NOT IN
( 'IN PROCESS','INCOMPLETE','REQUIRES REAPPROVAL')
AND poh.vendor_id = pv.vendor_id
AND pv.party_id = hp.party_id
AND por.creation_date >= ADD_MONTHS(TRUNC(SYSDATE), -(l_txn_history_range_in_months));
SELECT COUNT(*) total_po_releases
INTO l_tot_po_rel
FROM po_vendors pv,
po_releases_all por,
po_headers poh
WHERE pv.vendor_id = p_vendor_id
AND poh.vendor_id = pv.vendor_id
AND poh.PO_HEADER_ID = por.PO_HEADER_ID
AND por.release_type = 'BLANKET'
AND poh.authorization_status NOT IN
( 'IN PROCESS','INCOMPLETE','REQUIRES REAPPROVAL')
AND por.authorization_status NOT IN
( 'IN PROCESS','INCOMPLETE','REQUIRES REAPPROVAL')
AND por.creation_date >= ADD_MONTHS(TRUNC(SYSDATE), -(l_txn_history_range_in_months));