[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PO_DELETE_ERROR NUMBER :=6;
x_last_update_date pon_auction_headers_all.last_update_date%TYPE;
SELECT last_update_date
INTO x_last_update_date
FROM pon_auction_headers_all
WHERE auction_header_id = p_auction_header_id
FOR UPDATE;
SELECT nvl(source_reqs_flag,'N'), contract_type, nvl(auction_origination_code, 'NONE')
into x_source_reqs_flag, x_contract_type, x_origination_code
FROM pon_auction_headers_all
where auction_header_id = p_auction_header_id;
select nvl(allow_staggered_awards, 'N') into l_allow_stag_awards from pon_auction_headers_all where auction_header_id = p_auction_header_id;
select count(DISTINCT line_number) into l_unawarded_line_count from pon_auction_item_prices_all where auction_header_id = p_auction_header_id and nvl(award_status,'NO') = 'NO';
UPDATE PON_AUCTION_HEADERS_ALL
SET OUTCOME_STATUS = 'ALLOCATION_FAILED'
WHERE AUCTION_HEADER_ID = p_auction_header_id;
x_progress := ++l_progress || l_api_name || ' : update outcome_status for auction ' || p_auction_header_id;
UPDATE PON_AUCTION_HEADERS_ALL
SET OUTCOME_STATUS = 'ALLOCATION_FAILED'
WHERE AUCTION_HEADER_ID = p_auction_header_id;
SELECT distinct itm.line_number
FROM pon_auction_item_prices_all itm,
po_req_lines_in_pool_src_v prlv
WHERE itm.auction_header_id = p_auction_header_id AND
nvl(itm.line_origination_code, 'NONE') = 'REQUISITION' AND
nvl(itm.allocation_status, 'NO') <> 'ALLOCATED' AND
nvl(itm.award_status, 'NO') = 'COMPLETED' AND
(Nvl(itm.clm_info_flag,'N') = 'Y' OR nvl(itm.awarded_quantity, -99) > 0) AND
itm.auction_header_id = prlv.auction_header_id AND
itm.line_number = prlv.auction_line_number;
SELECT itm.document_disp_line_number, itm.item_number, itm.item_revision, itm.item_description,
itm.requisition_number, pjo.name
FROM pon_auction_item_prices_all itm,
per_jobs pjo
WHERE itm.auction_header_id = p_auction_header_id AND
itm.line_number = p_line_number AND
pjo.job_id (+) = itm.job_id;
log_message( l_api_name || 'update allocation status of item to allocated. Line num - ' || p_item_line_number);
UPDATE pon_auction_item_prices_all
SET allocation_status = 'ALLOCATED'
WHERE auction_header_id = p_auction_header_id and
line_number = p_item_line_number;
IF p_item_line_number IS NULL THEN -- -- it means the exception was thrown before line information is selected
p_failure_reason := PON_AUCTION_PKG.getMessage('PON_AUC_WF_SYS_ERROR') || ' - ' || SUBSTRB(SQLERRM, 1, 500) || PON_AUCTION_PKG.getMessage('PON_LINE_INFO_NOT_AVAIL');
SELECT paa.orig_req_line_id, prlsv.requisition_quantity
FROM pon_award_allocations paa, po_req_lines_in_pool_src_v prlsv,
pon_auction_item_prices_all paip
WHERE paa.allocated_qty > prlsv.requisition_quantity AND
paa.auction_header_id = p_auction_header_id AND
nvl(paa.split_req_line_id, -999)= -999 AND
nvl(paa.allocated_qty,0) > 0 AND
prlsv.requisition_line_id = paa.orig_req_line_id AND
prlsv.requisition_header_id = paa.orig_req_header_id AND
paip.auction_header_id = paa.auction_header_id AND
paip.line_number = paa.bid_line_number AND
paip.order_type_lookup_code IN ('AMOUNT', 'QUANTITY')
GROUP BY paa.orig_req_line_id, prlsv.requisition_quantity
HAVING COUNT(distinct bid_number) = 1;
SELECT paa.orig_req_line_id
FROM pon_award_allocations paa, po_req_lines_in_pool_src_v prlsv,
pon_auction_item_prices_all paip
WHERE paa.auction_header_id = p_auction_header_id AND
nvl(paa.split_req_line_id, -999)= -999 AND
nvl(paa.allocated_qty,0) > 0 AND
prlsv.requisition_line_id = paa.orig_req_line_id AND
prlsv.requisition_header_id = paa.orig_req_header_id AND
paip.auction_header_id = paa.auction_header_id AND
paip.line_number = paa.bid_line_number AND
paip.order_type_lookup_code IN ('AMOUNT', 'QUANTITY')
GROUP BY paa.orig_req_line_id
HAVING SUM (nvl(paa.allocated_qty,0)) > max(prlsv.requisition_quantity) -- Clin Slin- Will work for info lines. SELECT Max(NULL) FROM dual gives null and comparision of null will gives false
AND COUNT(distinct bid_number) > 1;
SELECT distinct paa.bid_line_number, itm.item_number, itm.item_revision, itm.item_description,
itm.requisition_number, pjo.name
FROM pon_auction_item_prices_all itm,
per_jobs pjo,
pon_award_allocations paa
WHERE paa.auction_header_id = itm.auction_header_id AND
paa.bid_line_number = itm.line_number AND
paa.orig_req_line_id = p_orig_req_line_id AND
itm.auction_header_id = p_auction_header_id AND
pjo.job_id (+) = itm.job_id;
SELECT requisition_line_id
BULK COLLECT INTO l_req_line_id_col
FROM po_requisition_lines_all
WHERE auction_header_id = p_auction_header_id
FOR UPDATE NOWAIT;
UPDATE PON_AWARD_ALLOCATIONS
SET allocated_qty = l_req_qty,
last_update_date = sysdate,
last_updated_by = l_user_id
WHERE orig_req_line_id = l_orig_req_line;
UPDATE po_requisition_lines_all prl
set bid_number = -1, bid_line_number = -1
where prl.requisition_line_id in
(select distinct orig_req_line_id
from pon_award_allocations alloc
where alloc.auction_header_id = p_auction_header_id);
x_progress := ++l_progress || l_api_name || ' : Federal document : Updated po_requisition_lines_all with -1 :';
INSERT INTO po_req_split_lines_GT (
auction_header_id,
bid_number,
bid_line_number,
requisition_header_id,
requisition_line_id,
allocated_qty
)
SELECT paa.auction_header_id,
paa.bid_number,
paa.bid_line_number,
paa.orig_req_header_id,
paa.orig_req_line_id,
paa.allocated_qty
FROM pon_award_allocations paa
WHERE paa.auction_header_id = p_auction_header_id AND
nvl(paa.split_req_line_id, -999)= -999 AND
nvl(paa.allocated_qty,0) > 0;
-- INSERT INTO po_req_split_lines_gt_debug (SELECT * FROM po_req_split_lines_gt WHERE auction_header_id = p_auction_header_id);
UPDATE PON_AWARD_ALLOCATIONS PAA
SET split_req_line_id=
(select new_req_line_id
from po_req_split_lines_gt prlst
where prlst.requisition_line_id = PAA.orig_req_line_id
and prlst.auction_header_id = PAA.auction_header_id
and prlst.bid_number = PAA.bid_number
and prlst.bid_line_number = PAA.bid_line_number
and prlst.record_status in ('S', 'E', 'T')),
-- status in s and e means newly split lines and lines
-- with equal allocation
last_update_date = sysdate,
last_updated_by = l_user_id
WHERE PAA.auction_header_id = p_auction_header_id AND
nvl(paa.split_req_line_id, -999)= -999 AND
nvl(paa.allocated_qty,0) > 0;
IF p_item_line_number IS NULL THEN -- -- it means the exception was thrown before line information is selected
p_split_failure_reason := PON_AUCTION_PKG.getMessage('PON_AUC_WF_SYS_ERROR') || ' - ' || SUBSTRB(SQLERRM, 1, 500) || PON_AUCTION_PKG.getMessage('PON_LINE_INFO_NOT_AVAIL');
l_insert_cursor NUMBER;
l_insert_result NUMBER;
l_insert_index NUMBER;
l_bid_number_insertcol dbms_sql.number_table;
l_bid_line_number_insertcol dbms_sql.number_table;
l_orig_req_line_insertcol dbms_sql.number_table;
l_orig_req_header_insertcol dbms_sql.number_table;
l_allocated_qty_insertcol dbms_sql.number_table;
l_auction_header_id_insertcol dbms_sql.number_table;
l_bid_number_insertcol := l_empty_table;
l_bid_line_number_insertcol := l_empty_table;
l_orig_req_line_insertcol := l_empty_table;
l_orig_req_header_insertcol := l_empty_table;
l_allocated_qty_insertcol := l_empty_table;
l_auction_header_id_insertcol := l_empty_table;
SELECT paip.order_type_lookup_code, paip.purchase_basis, pah.contract_type
INTO l_order_type_lookup_code, l_purchase_basis, l_contract_type
FROM pon_auction_item_prices_all paip, pon_auction_headers_all pah
WHERE paip.auction_header_id = pah.auction_header_id
AND paip.auction_header_id = p_auctionid
AND paip.line_number = p_line_number;
SELECT requisition_line_id, requisition_header_id, requisition_quantity
BULK COLLECT INTO l_req_line_id_col, l_req_header_id_col,
l_req_quantity_col
FROM po_req_lines_in_pool_src_v
WHERE auction_header_id = p_auctionID AND
auction_line_number = p_line_number AND
nvl(modified_by_agent_flag, 'N') <> 'Y'
ORDER BY need_by_date ASC, creation_date ASC;
SELECT decode(itm.order_type_lookup_code, 'AMOUNT',
bl.bid_currency_unit_price,
bl.award_quantity) quantity,
bh.bid_number,
bl.bid_currency_unit_price,
Decode(Nvl(itm.clm_info_flag,'N'), 'Y', 1, 0) AS clm_info_indicator
BULK COLLECT INTO l_award_col, l_bid_number_col, l_bid_price_col , l_clm_info_flag
FROM pon_auction_item_prices_all itm,
pon_bid_item_prices bl,
pon_bid_headers bh,
pon_auction_headers_all pah
WHERE itm.auction_header_id = p_auctionID AND
itm.line_number = p_line_number AND
bl.line_number = itm.line_number AND
bl.auction_header_id = itm.auction_header_id AND
nvl(bl.award_status,'NO') = 'AWARDED' AND
bh.bid_number = bl.bid_number AND
bh.auction_header_id = itm.auction_header_id AND
nvl(bh.bid_status,'NONE') = 'ACTIVE'AND
pah.auction_header_id = itm.auction_header_id
ORDER BY bl.promised_date ASC, decode(pah.contract_type, 'BLANKET', 1, bl.award_quantity) DESC,
bl.bid_currency_price ASC,
bl.publish_date ASC;
l_insert_index := 1;
l_bid_number_insertcol(l_insert_index) := l_bid_number_col(bidIdx);
l_bid_line_number_insertcol(l_insert_index) := p_line_number;
l_orig_req_line_insertcol(l_insert_index) := l_req_line_id_col(l_reqIdx);
l_orig_req_header_insertcol(l_insert_index) := l_req_header_id_col(l_reqIdx);
l_allocated_qty_insertcol(l_insert_index) := l_qty_allocated;
l_clm_info_flag(l_insert_index) := l_clm_info_flag(1);
x_progress := '30: Auto_Req_Allocation: ' || 'qty allocated: ' || l_allocated_qty_insertcol(l_insert_index) || ' index: ' || l_insert_index;
x_progress := '31: Auto_Req_Allocation: ' || 'bid number: ' || l_bid_number_insertcol(l_insert_index) || ' bid line number : ' || l_bid_line_number_insertcol(l_insert_index)
|| 'req line id: ' || l_orig_req_line_insertcol(l_insert_index) || 'clm_info ' || l_clm_info_flag(l_insert_index);
l_auction_header_id_insertcol(l_insert_index) := p_auctionID;
l_insert_index := l_insert_index + 1;
l_split_req_line_id_insertcol(l_insert_index) := l_req_line_id_col(l_reqIdx);
l_split_req_line_id_insertcol(l_insert_index) := null;
x_progress := '33: Right before Bulk Insert';
FORALL l_count IN 1..l_bid_number_insertcol.COUNT
INSERT INTO pon_award_allocations(bid_number, bid_line_number, orig_req_line_id, orig_req_header_id, allocated_qty, split_req_line_id, auction_header_id, created_by, last_update_date, last_updated_by, last_update_login, creation_date)
VALUES(l_bid_number_insertcol(l_count),
l_bid_line_number_insertcol(l_count),
l_orig_req_line_insertcol(l_count),
l_orig_req_header_insertcol(l_count),
l_allocated_qty_insertcol(l_count),
Decode(l_clm_info_flag(l_count), 1 , l_orig_req_line_insertcol(l_count), NULL),
--l_split_req_line_id_insertcol(l_count),
l_auction_header_id_insertcol(l_count),
l_user_id,
sysdate,
l_user_id,
l_login_id,
sysdate);
x_progress := '35: Auto_Req_Allocation: ' || 'qty allocated: ' || l_allocated_qty_insertcol(l_bid_number_insertcol.COUNT);
select open_bidding_date,
close_bidding_date,
trading_partner_contact_id,
doctype_id,
trading_partner_name,
trading_partner_contact_name,
has_items_flag
into p_open_bidding_date,
p_close_bidding_date,
p_trading_partner_contact_id,
p_doctype_id,
p_trading_partner_name,
p_trading_partner_contact_name,
x_has_items
from pon_auction_headers_all
where auction_header_id = p_auction_header_id;
SELECT auh.document_number,
dt.doctype_group_name, auh.contract_type,
nvl(auh.wf_poncompl_current_round, 0), auh.doctype_id
INTO x_doc_number_dsp, x_doctype_group_name, x_contract_type,
x_current_round, x_doctype_id
FROM pon_auction_headers_all auh, pon_auc_doctypes dt
WHERE auh.auction_header_id = p_auction_header_id and
auh.doctype_id = dt.doctype_id;
SELECT 'REQUISITION'
INTO x_requistion_based
FROM DUAL
WHERE EXISTS(
SELECT '1'
FROM pon_auction_item_prices_all
WHERE auction_header_id = p_auction_header_id
AND line_origination_code = 'REQUISITION'
);
log_error(x_itemtype || ' ' || x_progress || 'in select exception' || SUBSTRB(SQLERRM, 1, 500));
-- it means the exception was thrown before line information is selected
-- let buyer know no line information is available
wf_engine.SetItemAttrText (itemtype => x_itemtype,
itemkey => x_itemkey,
aname => 'LINE_NUMBER',
avalue => PON_AUCTION_PKG.getMessage('PON_LINE_INFO_NOT_AVAIL'));
This procedure inserts data from PON tables to PO interface tables
*/
PROCEDURE CREATE_PO_STRUCTURE(p_auction_header_id IN NUMBER,
p_bid_number IN NUMBER,
p_user_id IN NUMBER,
p_interface_header_id OUT NOCOPY NUMBER,
p_pdoi_header OUT NOCOPY PDOIheader,
p_error_code OUT NOCOPY VARCHAR2,
p_error_message OUT NOCOPY VARCHAR2) IS
x_user_id NUMBER;
CURSOR update_clin_num_cursor(l_interface_header_id NUMBER,
l_auction_header_id NUMBER ) IS
SELECT interface_line_id,
group_line_id,
clm_info_flag,
interface_header_id
FROM po_lines_interface
WHERE group_Line_id IS NULL
AND INTERFACE_HEADER_ID = l_interface_header_id
AND auction_header_id = l_auction_header_id
AND clm_exhibit_name IS NULL --ELINs project
ORDER BY interface_line_id;
CURSOR update_slin_num_cursor(l_interface_header_id NUMBER,
l_auction_header_id NUMBER ) IS
SELECT interface_line_id,
group_line_id,
line_num_display,
interface_header_id
FROM po_lines_interface
WHERE group_Line_id IS NULL
AND INTERFACE_HEADER_ID = l_interface_header_id
AND auction_header_id = l_auction_header_id
ORDER BY interface_line_id;
SELECT interface_header_id,
interface_line_id,
auction_header_id,
auction_line_number,
group_line_id,
requisition_line_id,
clm_base_line_num,
line_num_display
FROM PO_LINES_INTERFACE
WHERE INTERFACE_HEADER_ID = l_interface_header_id
ORDER BY interface_line_id;
/* Selects the data from Sourcing that will populate PO_HEADERS_INTERFACE */
/* Bug 9891996 - Added External IDV field to headerLevelInfo cursor */
CURSOR headerLevelInfo IS
SELECT pah.auction_header_id,
pah.document_number,
pah.org_id,
pah.contract_type,
pah.language_code,
pbh.po_start_date,
pbh.po_end_date,
pah.currency_code,
pah.fob_code,
pah.freight_terms_code,
pah.carrier_code,
pah.payment_terms_id,
pah.ship_to_location_id,
pah.bill_to_location_id,
pah.auction_origination_code,
pah.source_reqs_flag,
pbh.bid_number,
pbh.order_number,
pbh.vendor_id,
pbh.vendor_site_id,
PON_AUCTION_CREATE_PO_PKG.get_vendor_contact_id(pbh.trading_partner_contact_id,pbh.vendor_site_id,pbh.vendor_id) vendor_contact_id,
pbh.agent_id,
pah.global_agreement_flag,
round(pah.po_min_rel_amount* pbh.rate,fc.precision),
pbh.po_agreed_amount,
pbh.bid_currency_code,
pah.rate_type,
pah.rate_date,
pbh.rate_dsp,
pbh.create_sourcing_rules,
pbh.update_sourcing_rules,
pbh.release_method,
pbh.initiate_approval,
pbh.acceptance_required_flag,
pah.po_style_id,
pah.progress_payment_type,
pah.supplier_enterable_pymt_flag,
pbh.idv_header_id, -- CLM Order off IDV Project
pbh.external_idv,
pah.umbrella_program_id,
pah.fair_opp_notice_flag
FROM pon_auction_headers_all pah,
pon_bid_headers pbh,
fnd_currencies fc
WHERE pah.auction_header_id = p_auction_header_id and
pbh.auction_header_id = pah.auction_header_id and
pbh.bid_number = p_bid_number and
pbh.bid_currency_code = fc.currency_code;
/* Selects the data from Sourcing that will populate PO_LINES_INTERFACE */
/*
rrkulkar-large-auction-support - this cursor will bring in all the lines in the middle-tier :
hence, added the following condition in the where clause :-
paip.line_origination_code = 'REQUISITION';
SELECT paip.line_number,
paip.line_type_id,
paip.order_type_lookup_code,
paip.line_origination_code,
paip.item_id,
paip.item_revision,
paip.category_id,
paip.item_description,
mtluom.unit_of_measure,
paip.ship_to_location_id,
paip.clm_need_by_date,
/*Clm Bug : 10212430 : For clm,copy POP dates from offer. */
Decode(Nvl(l_is_fed,'N'),'Y',pbip.PROMISE_POP_START_DATE,paip.need_by_start_date),
Decode(Nvl(l_is_fed,'N'),'Y',pbip.PROMISE_POP_END_DATE,paip.need_by_date),
pbip.award_quantity,
nvl(pbip.po_bid_min_rel_amount, round(paip.po_min_rel_amount* pbh.rate,fc.precision)),
paip.has_price_elements_flag,
decode(paip.order_type_lookup_code, 'FIXED PRICE',
round(pbip.bid_currency_unit_price, fc.precision),
pbip.bid_currency_unit_price),
pbip.promised_date,
paip.job_id,
round(paip.po_agreed_amount*pbh.rate, fc.precision),
paip.purchase_basis
, pbip.bid_curr_advance_amount
, pbip.recoupment_rate_percent
, pbip.progress_pymt_rate_percent
, pbip.retainage_rate_percent
, pbip.bid_curr_max_retainage_amt
, decode(pbip.has_bid_payments_flag, 'Y', decode((select 1 from dual where exists
( select 1 from pon_bid_payments_shipments where
auction_header_id = pbip.auction_header_id and
bid_number= pbip.bid_number and bid_line_number=pbip.line_number
and bid_currency_price <> 0
)
),
1,'Y','N')
,'N') has_bid_payments_flag
, pbip.award_shipment_number
-- Clin Slin Changes
, paip.line_num_display
, paip.group_line_id
, paip.clm_info_flag
, paip.clm_option_indicator
, paip.clm_option_num
, paip.clm_option_from_date
, paip.clm_option_to_date
, paip.clm_funded_flag
, paip.clm_base_line_num
-- Complex Pricing Changes
, paip.CLM_CONTRACT_TYPE
, paip.CLM_COST_CONSTRAINT
, paip.CLM_IDC_TYPE
, pbip.idv_line_id -- CLM Order Off IDV Project.
, paip.exhibit_number --ELINs project
-- Event Based Delivery Project
,paip.CLM_DELIVERY_EVENT_CODE
,paip.CLM_DELIVERY_PERIOD
,paip.CLM_DELIVERY_PERIOD_UOM
,pbip.CLM_PROMISE_PERIOD
,pbip.CLM_PROMISE_PERIOD_UOM
,pbip.CLM_PROMISE_POP_DURATION
,pbip.CLM_PROMISE_POP_DURATION_UOM
FROM
pon_auction_item_prices_all paip,
pon_bid_item_prices pbip,
mtl_units_of_measure mtluom,
pon_bid_headers pbh,
fnd_currencies fc
WHERE pbip.bid_number = p_bid_number and
pbip.auction_header_id = p_auction_header_id and
nvl(pbip.award_status, 'NO') = 'AWARDED' and
paip.auction_header_id = pbip.auction_header_id and
paip.line_number = pbip.line_number and
paip.group_type NOT IN ('GROUP','LOT_LINE') and
paip.uom_code = mtluom.uom_code (+) and
pbh.bid_number = pbip.bid_number and
fc.currency_code = pbh.bid_currency_code and
paip.line_origination_code = 'REQUISITION';
SELECT Decode(Nvl(l_is_fed,'N'),'Y', paa.orig_req_line_id, paa.split_req_line_id), paa.allocated_qty
FROM pon_award_allocations paa, pon_auction_item_prices_all paip
WHERE paa.auction_header_id = p_auction_header_id and
paa.bid_number = p_bid_number AND
paa.auction_header_id = paip.auction_header_id and
paa.bid_line_number = paip.line_number AND
bid_line_number = x_line_number and
((Nvl(paip.clm_info_flag,'N') = 'Y') OR (Nvl(paip.clm_info_flag,'N') = 'N' AND nvl(paa.allocated_qty,0) <> 0)) and
((Nvl(paip.clm_info_flag,'N') = 'Y') OR
(Nvl(paip.clm_info_flag,'N') = 'N' AND nvl(paa.split_req_line_id, -999) <> -999) OR
(Nvl(l_is_fed,'N') = 'Y' AND nvl(paa.orig_req_line_id, -999) <> -999));
/* Clin Slin changes : We need to get the info lines selected in this cursor, since
later in the code we use the results of this cursor
to identify the lines which have to go into the interface tables.
The condition used is
sum_of_alloc_quantities.EXISTS(x_line_number) AND
sum_of_alloc_quantities(x_line_number) > 0
*/
CURSOR sumOfReqAllocQuantities is
SELECT PAIP.line_number, nvl(sum(nvl(PAA.allocated_qty,0)), 0)
FROM PON_AWARD_ALLOCATIONS PAA, PON_AUCTION_ITEM_PRICES_ALL PAIP
WHERE PAIP.auction_header_id = p_auction_header_id
AND PAIP.award_status = 'COMPLETED'
AND ((Nvl(clm_info_flag,'N')='Y') OR (Nvl(clm_info_flag,'N') = 'N' and nvl(PAIP.awarded_quantity,0) > 0))
AND PAA.auction_header_id(+) = PAIP.auction_header_id
AND PAA.bid_line_number(+) = PAIP.line_number
AND PAA.bid_number(+) = p_bid_number
AND nvl(PAA.split_req_line_id(+), -999) <> -999
GROUP BY PAIP.line_number;
SELECT PAIP.line_number, nvl(sum(nvl(PAA.allocated_qty,0)), 0)
FROM PON_AWARD_ALLOCATIONS PAA, PON_AUCTION_ITEM_PRICES_ALL PAIP
WHERE PAIP.auction_header_id = p_auction_header_id
AND PAIP.award_status = 'COMPLETED'
AND ((Nvl(clm_info_flag,'N')='Y') OR (Nvl(clm_info_flag,'N') = 'N' and nvl(PAIP.awarded_quantity,0) > 0))
AND PAA.auction_header_id(+) = PAIP.auction_header_id
AND PAA.bid_line_number(+) = PAIP.line_number
AND PAA.bid_number(+) = p_bid_number
AND nvl(PAA.orig_req_line_id(+), -999) <> -999
GROUP BY PAIP.line_number;
SELECT doctype_id, Nvl(ALLOW_UNSOL_OFFER_LINES,'N')
INTO l_doctype_id,l_allow_unsol_lines
FROM pon_auction_headers_all
WHERE auction_Header_id = p_auction_header_id;
SELECT
pon_auc_doctype_rules.DEFAULT_VALUE INTO l_is_fed
FROM PON_AUC_DOCTYPE_RULES pon_auc_doctype_rules
, PON_AUC_BIZRULES pon_auc_bizrules
WHERE pon_auc_doctype_rules.BIZRULE_ID = pon_auc_bizrules.BIZRULE_ID
AND pon_auc_doctype_rules.DOCTYPE_ID = l_doctype_id
AND pon_auc_bizrules.NAME = 'FEDERAL_NEGOTIATION';
SELECT po_headers_interface_s.nextval
INTO x_interface_header_id
FROM dual;
Insert into PO_HEADERS_INTERFACE the purchase order header information based on the negotiation and the award bid.
*/
/* Bug 9891996 - Populate External IDV also in PO_HEADERS_INTERFACE table */
INSERT into PO_HEADERS_INTERFACE (
interface_header_id,
interface_source_code,
batch_id,
action,
org_id,
document_type_code,
document_subtype,
created_language,
effective_date,
expiration_date,
document_num,
group_code,
vendor_id,
vendor_site_id,
vendor_contact_id,
agent_id,
currency_code,
rate_type_code,
rate_date,
rate,
fob,
freight_terms,
freight_carrier,
terms_id,
ship_to_location_id,
bill_to_location_id,
consume_req_demand_flag,
global_agreement_flag,
min_release_amount,
amount_agreed,
acceptance_required_flag,
style_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
clm_external_idv,
umbrella_program_id,
fon_ref_id)
values (
x_interface_header_id, -- interface_header_id
'SOURCING', -- interface_source_code
x_interface_header_id, -- batch_id
'NEW', -- action
x_pdoi_header.org_id, -- org_id
decode(x_pdoi_header.contract_type, 'BLANKET',
'PA','CONTRACT','PA','PO'), -- document_type_code
x_pdoi_header.contract_type, -- document_subtype
x_pdoi_header.language_code, -- created_language
x_pdoi_header.po_start_date, -- effective_date
x_pdoi_header.po_end_date, -- expiration_date
NULL, --x_pdoi_header.order_number, -- document_num
'DEFAULT', -- group_code
x_pdoi_header.vendor_id, -- vendor_id
x_pdoi_header.vendor_site_id, -- vendor_site_id
x_pdoi_header.vendor_contact_id, -- vendor_contact_id
x_pdoi_header.agent_id, -- agent_id
x_pdoi_header.bid_currency_code, -- currency_code
decode(x_pdoi_header.currency_code, x_pdoi_header.bid_currency_code, null, x_pdoi_header.rate_type), -- rate_type_code
decode(x_pdoi_header.currency_code, x_pdoi_header.bid_currency_code, null, x_pdoi_header.rate_date), -- rate_date
decode(x_pdoi_header.currency_code, x_pdoi_header.bid_currency_code, null, x_pdoi_header.rate_dsp), -- rate
x_pdoi_header.fob_code, -- fob
x_pdoi_header.freight_terms_code, -- freight_terms
x_pdoi_header.carrier_code, -- freight_carrier,
x_pdoi_header.payment_terms_id, -- terms_id
x_pdoi_header.ship_to_location_id, -- ship_to_location_id
x_pdoi_header.bill_to_location_id, -- bill_to_location_id
x_pdoi_header.source_reqs_flag, -- consume req demandflag
x_pdoi_header.global_agreement_flag, -- global_agreement_flag
x_pdoi_header.po_min_rel_amount, -- min_release_amount
x_pdoi_header.po_agreed_amount, -- amount_agreed
x_pdoi_header.acceptance_required_flag, -- accept req flag
x_pdoi_header.po_style_id, -- style_id
p_user_id, -- created_by
sysdate, -- creation_date
p_user_id, -- last_update_by
SYSDATE,
x_pdoi_header.clm_external_idv,
decode(x_pdoi_header.contract_type, 'STANDARD',NULL,x_pdoi_header.umbrella_program_id),
decode(Nvl(x_pdoi_header.fair_opp_notice_flag,'N'), 'Y',x_pdoi_header.auction_header_id,NULL)); -- last_update_date
x_progress := '15: CREATE_PO_STRUCTURE: INSERTING the following data into po_headers_interface: ' ||
'interface_header_id: ' || to_char(x_interface_header_id) || ', ' ||
'interface_source_code: ' || 'SOURCING' || ', ' ||
'batch_id: ' || to_char(x_interface_header_id) || ', ' ||
'action: ' || 'NEW' || ', ' ||
'org_id: ' || to_char(x_pdoi_header.org_id) || ', ' ;
'last_update_by: ' || to_char(p_user_id) || ', ' ||
'clm_external_idv: ' || x_pdoi_header.clm_external_idv;
SELECT BID_CURRENCY_UNIT_PRICE INTO x_price
FROM PON_BID_SHIPMENTS
WHERE LINE_NUMBER = x_pdoi_line.line_number
AND AUCTION_HEADER_ID = x_pdoi_header.auction_header_id
AND BID_NUMBER = p_bid_number
AND SHIPMENT_NUMBER = x_pdoi_line.award_shipment_number;
Insert a row into PO_LINES_INTERFACE with the
item information from the negotiation line, and
the requisiton_line_id and quantity
(price and quantity will be switched when
negotiation line is amount-based-
check pon_auction_item_prices_all.order_type_lookup_code)
from the backing requisition.
*/
INSERT into PO_LINES_INTERFACE (
interface_header_id,
interface_line_id,
requisition_line_id,
line_type_id,
item_id,
item_revision,
category_id,
item_description,
unit_of_measure,
quantity,
unit_price,
min_release_amount,
ship_to_location_id,
need_by_date,
clm_period_perf_start_date,
clm_period_perf_end_date,
promised_date,
last_updated_by,
last_update_date,
created_by,
creation_date,
auction_header_id,
auction_display_number,
auction_line_number,
bid_number,
bid_line_number,
orig_from_req_flag,
job_id,
amount
, advance_amount
, recoupment_rate
, progress_payment_rate
, retainage_rate
, max_retainage_amount
, line_loc_populated_flag,
-- Clin Slin Changes
line_num_display,
group_line_id,
clm_info_flag,
clm_option_indicator,
clm_option_num,
clm_option_from_date,
clm_option_to_date,
clm_funded_flag,
clm_base_line_num,
-- Complex Pricing Changes
CONTRACT_TYPE,
COST_CONSTRAINT,
CLM_IDC_TYPE,
-- CLM Order Off IDV Project
from_header_id,
from_line_id,
clm_exhibit_name, --ELINs project
-- Event Based Delivery Project
CLM_DELIVERY_EVENT_CODE,
CLM_DELIVERY_PERIOD,
CLM_DELIVERY_PERIOD_UOM,
CLM_PROMISE_PERIOD,
CLM_PROMISE_PERIOD_UOM,
CLM_POP_DURATION,
CLM_POP_DURATION_UOM
)
values (
x_interface_header_id, -- interface_header_id
po_lines_interface_s.nextval, -- interface_line_id
x_requisition_line_id, -- requisition_line_id
x_pdoi_line.line_type_id,
-- line_type_id
x_pdoi_line.item_id,
-- item_id
x_pdoi_line.item_revision,
-- item_revision
x_pdoi_line.category_id,
-- category_id
substrb(x_pdoi_line.item_description, 1, 240),
-- item_description
decode(x_pdoi_line.order_type_lookup_code, 'AMOUNT', null, x_pdoi_line.unit_of_measure),
-- unit_of_measure
decode(x_pdoi_line.order_type_lookup_code, 'RATE', NULL, 'FIXED PRICE', NULL, x_allocation_quantity), -- quantity
decode(x_pdoi_line.order_type_lookup_code,'AMOUNT', 1, x_price), -- unit_price
x_pdoi_line.po_min_rel_amount,
-- min_release_amount
x_pdoi_line.ship_to_location_id,
-- ship_to_location_id
/*Bug : 10212430 : For clm : need_by_date <-> clm_need_by_date and Promise Start Date <-> need_by_start_date */
Decode(Nvl(l_is_fed,'N'),'Y',x_pdoi_line.need_by_date,x_pdoi_line.pop_start_date),
-- need_by_date
Decode(Nvl(l_is_fed,'N'),'Y',x_pdoi_line.pop_start_date,null),
-- period_of_performance_start_date
Decode(Nvl(l_is_fed,'N'),'Y',x_pdoi_line.pop_end_date,null),
-- period_of_performance_end_date
x_pdoi_line.promised_date, -- promised_date
p_user_id, -- last_update_by
sysdate, -- last_update_date
p_user_id, -- created_by
sysdate, -- creation_date
x_pdoi_header.auction_header_id, -- auction_header_id
x_pdoi_header.document_number, -- document_number
x_pdoi_line.line_number, -- auction_line_number,
x_pdoi_header.bid_number, -- bid_number
x_pdoi_line.line_number, -- bid_line_number
'Y', -- orig_from_req_flag
x_pdoi_line.job_id, -- job_id
x_amount -- amount
, decode(x_pdoi_line.bid_curr_advance_amount,0,null,x_pdoi_line.bid_curr_advance_amount)
, x_pdoi_line.recoupment_rate_percent
, x_pdoi_line.progress_pymt_rate_percent
, x_pdoi_line.retainage_rate_percent
, x_pdoi_line.Bid_curr_max_retainage_amt
, x_pdoi_line.has_bid_payments_flag -- Line_loc_populated
--Clin Slin Changes
,x_pdoi_line.line_num_display
,x_pdoi_line.group_line_id
,x_pdoi_line.clm_info_flag
,x_pdoi_line.clm_option_indicator
,x_pdoi_line.clm_option_num
,x_pdoi_line.clm_option_from_date
,x_pdoi_line.clm_option_to_date
,x_pdoi_line.clm_funded_flag
,x_pdoi_line.clm_base_line_num
,x_pdoi_line.CONTRACT_TYPE
,x_pdoi_line.COST_CONSTRAINT
,x_pdoi_line.CLM_IDC_TYPE
-- CLM Order Off IDV Project
,x_pdoi_header.idv_header_id
,x_pdoi_line.idv_line_id
,x_pdoi_line.exhibit_number -- ELINs project
-- Event Based Delivery Project
,Decode(Nvl(l_is_fed,'N'),'Y',x_pdoi_line.CLM_DELIVERY_EVENT_CODE,null)
,Decode(Nvl(l_is_fed,'N'),'Y',x_pdoi_line.CLM_DELIVERY_PERIOD,null)
,Decode(Nvl(l_is_fed,'N'),'Y',x_pdoi_line.CLM_DELIVERY_PERIOD_UOM,null)
,Decode(Nvl(l_is_fed,'N'),'Y',x_pdoi_line.CLM_PROMISE_PERIOD,null)
,Decode(Nvl(l_is_fed,'N'),'Y',x_pdoi_line.CLM_PROMISE_PERIOD_UOM,null)
,Decode(Nvl(l_is_fed,'N'),'Y',x_pdoi_line.CLM_PROMISE_POP_DURATION,null)
,Decode(Nvl(l_is_fed,'N'),'Y',x_pdoi_line.CLM_PROMISE_POP_DURATION_UOM,null)
) return interface_line_id into x_interface_line_id;
'35: CREATE_PO_STRUCTURE: INSERTING the following data into PO_LINES_INTERFACE: ' ||
'interface_header_id: ' || to_char(x_interface_header_id) || ', ' ||
'interface_line_id: ' || to_char(x_interface_line_id) || ', ' ||
'requisition_line_id: ' || to_char(x_requisition_line_id) || ', ' ||
'line_type_id: ' || to_char(x_pdoi_line.line_type_id) || ', ' ||
'item_id: ' || to_char(x_pdoi_line.item_id) || ', ' ||
'item_revision: ' || x_pdoi_line.item_revision || ', ' ||
'category_id: ' || to_char(x_pdoi_line.category_id) || ', ' ||
'item_description: ' || substrb(x_pdoi_line.item_description, 1, 240) || ', ';
'last_update_by: ' || to_char(p_user_id) || ', ' ||
'created_by: ' || to_char(p_user_id) || ', ' ||
'auction_header_id: ' || to_char(x_pdoi_header.auction_header_id) || ', ' ||
'document_number: ' || x_pdoi_header.document_number || ', ' ||
'auction_line_number: ' || to_char(x_pdoi_line.line_number) || ', ' ||
'bid_number: ' || to_char(x_pdoi_header.bid_number) || ', ' ||
'bid_line_number: ' || to_char(x_pdoi_line.line_number) || ', ' ||
'orig_from_req_flag: ' || 'Y';
Once we have inserted all the lines having allocated quantities
with backing reqs, we need to take care of the following 3 more
conditions :-
case-1. No backing requisition for current line OR
case-2. Lines with backing requisitions have 0 allocation
award quantities
case-3. There is an excess award OR
In either of the aforementioned 3 cases, we need to
insert an additional row into PO_LINES_INTERFACE with the
item information from the negotiation line, a null
requisition_line_id, a quantity for the excess award,
and a value of 'N' in the orig_from_req_flag column.
*/
/*
rrkulkar-large-auction-support : In case of super-large auctions,
this case will be satisfied more often than not. Here's what we can do :-
1. split this insert into 2 cases -
1a. use cursor approach for lines with backing reqs. (case-2 and case-3 above)
1b. use batching for lines with no backing reqs
*/
x_progress := '25: before execess award';
INSERT into PO_LINES_INTERFACE (
interface_header_id,
interface_line_id,
requisition_line_id,
line_type_id,
item_id,
item_revision,
category_id,
item_description,
unit_of_measure,
quantity,
unit_price,
min_release_amount,
ship_to_location_id,
need_by_date,
clm_period_perf_start_date,
clm_period_perf_end_date,
promised_date,
last_updated_by,
last_update_date,
created_by,
creation_date,
auction_header_id,
auction_display_number,
auction_line_number,
bid_number,
bid_line_number,
orig_from_req_flag,
job_id,
amount
, advance_amount
, recoupment_rate
, progress_payment_rate
, retainage_rate
, max_retainage_amount
, Line_loc_populated_flag,
-- Clin Slin Changes
line_num_display,
group_line_id,
clm_info_flag,
clm_option_indicator,
clm_option_num,
clm_option_from_date,
clm_option_to_date,
clm_funded_flag,
clm_base_line_num,
-- Complex Pricing Changes
CONTRACT_TYPE,
COST_CONSTRAINT,
CLM_IDC_TYPE,
--CLM Order off idv project
from_header_id,
from_line_id,
clm_exhibit_name, --ELINs project
-- Event Based Delivery Project
CLM_DELIVERY_EVENT_CODE,
CLM_DELIVERY_PERIOD,
CLM_DELIVERY_PERIOD_UOM,
CLM_PROMISE_PERIOD,
CLM_PROMISE_PERIOD_UOM,
CLM_POP_DURATION,
CLM_POP_DURATION_UOM
)
values (
x_interface_header_id, -- interface_header_id
po_lines_interface_s.nextval, -- interface_line_id
NULL, -- requisition_line_id
x_pdoi_line.line_type_id,
-- line_type_id
x_pdoi_line.item_id,
-- item_id
x_pdoi_line.item_revision,
-- item_revision
x_pdoi_line.category_id,
-- category_id
substrb(x_pdoi_line.item_description, 1, 240),
-- item_description
decode(x_pdoi_line.order_type_lookup_code, 'AMOUNT', null, x_pdoi_line.unit_of_measure), -- unit_of_measure
x_quantity, -- quantity
decode(x_pdoi_line.order_type_lookup_code,'AMOUNT', 1, x_price), -- unit_price
x_pdoi_line.po_min_rel_amount, -- min_release_amount
x_pdoi_line.ship_to_location_id,
-- ship_to_location_id
/*Bug : 10212430 : For clm : need_by_date <-> clm_need_by_date and Promise Start Date <-> need_by_start_date */
Decode(Nvl(l_is_fed,'N'),'Y',x_pdoi_line.need_by_date,x_pdoi_line.pop_start_date),
-- need_by_date
Decode(Nvl(l_is_fed,'N'),'Y',x_pdoi_line.pop_start_date,null),
-- period_of_performance_start_date
Decode(Nvl(l_is_fed,'N'),'Y',x_pdoi_line.pop_end_date,null),
-- period_of_performance_end_date
x_pdoi_line.promised_date,
-- promised_date
p_user_id, -- last_update_by
sysdate, -- last_update_date
p_user_id, -- created_by
sysdate, -- creation_date
x_pdoi_header.auction_header_id, -- auction_header_id
x_pdoi_header.document_number, -- document_number
x_pdoi_line.line_number, -- auction_line_number,
x_pdoi_header.bid_number, -- bid_number
x_pdoi_line.line_number, -- bid_line_number
'N', -- orig_from_req_flag
x_pdoi_line.job_id, -- job_id
x_amount -- amount
, decode(x_pdoi_line.bid_curr_advance_amount,0,null,x_pdoi_line.bid_curr_advance_amount)
, x_pdoi_line.recoupment_rate_percent
, x_pdoi_line.progress_pymt_rate_percent
, x_pdoi_line.retainage_rate_percent
, x_pdoi_line.Bid_curr_max_retainage_amt
, x_pdoi_line.has_bid_payments_flag -- Line_loc_populated
--Clin Slin Changes
,x_pdoi_line.line_num_display
,x_pdoi_line.group_line_id
,x_pdoi_line.clm_info_flag
,x_pdoi_line.clm_option_indicator
,x_pdoi_line.clm_option_num
,x_pdoi_line.clm_option_from_date
,x_pdoi_line.clm_option_to_date
,x_pdoi_line.clm_funded_flag
,x_pdoi_line.clm_base_line_num
,x_pdoi_line.CONTRACT_TYPE
,x_pdoi_line.COST_CONSTRAINT
,x_pdoi_line.CLM_IDC_TYPE
--CLM Order off idv project
,x_pdoi_header.idv_header_id
,x_pdoi_line.idv_line_id
,x_pdoi_line.exhibit_number --ELINs project
-- Event Based Delivery Project
,Decode(Nvl(l_is_fed,'N'),'Y',x_pdoi_line.CLM_DELIVERY_EVENT_CODE,null)
,Decode(Nvl(l_is_fed,'N'),'Y',x_pdoi_line.CLM_DELIVERY_PERIOD,null)
,Decode(Nvl(l_is_fed,'N'),'Y',x_pdoi_line.CLM_DELIVERY_PERIOD_UOM,null)
,Decode(Nvl(l_is_fed,'N'),'Y',x_pdoi_line.CLM_PROMISE_PERIOD,null)
,Decode(Nvl(l_is_fed,'N'),'Y',x_pdoi_line.CLM_PROMISE_PERIOD_UOM,null)
,Decode(Nvl(l_is_fed,'N'),'Y',x_pdoi_line.CLM_PROMISE_POP_DURATION,null)
,Decode(Nvl(l_is_fed,'N'),'Y',x_pdoi_line.CLM_PROMISE_POP_DURATION_UOM,null)
) return interface_line_id into x_interface_line_id;
'35: CREATE_PO_STRUCTURE: INSERTING the following data into PO_LINES_INTERFACE: ' ||
'interface_header_id: ' || to_char(x_interface_header_id) || ', ' ||
'interface_line_id: ' || to_char(x_interface_line_id) || ', ' ||
'requisition_line_id: ' || null || ', ' ||
'line_type_id: ' || to_char(x_pdoi_line.line_type_id) || ', ' ||
'item_id: ' || to_char(x_pdoi_line.item_id) || ', ' ||
'item_revision: ' || x_pdoi_line.item_revision || ', ' ||
'category_id: ' || to_char(x_pdoi_line.category_id) || ', ' ||
'item_description: ' || substrb(x_pdoi_line.item_description, 1, 240) || ', ';
'last_update_by: ' || to_char(p_user_id) || ', ' ||
'created_by: ' || to_char(p_user_id) || ', ' ||
'auction_header_id: ' || to_char(x_pdoi_header.auction_header_id) || ', ' ||
'document_number: ' || x_pdoi_header.document_number || ', ' ||
'auction_line_number: ' || to_char(x_pdoi_line.line_number) || ', ' ||
'bid_number: ' || to_char(x_pdoi_header.bid_number) || ', ' ||
'bid_line_number: ' || to_char(x_pdoi_line.line_number) || ', ' ||
'orig_from_req_flag: ' || 'N' || ', ';
select nvl(max(line_number),0)
into l_max_line_number
from pon_bid_item_prices
where bid_number = x_pdoi_header.bid_number
AND auction_line_number <> -1; --Unsolicited Lines Project
INSERT into PO_LINES_INTERFACE (
interface_header_id,
interface_line_id,
requisition_line_id,
line_type_id,
item_id,
item_revision,
category_id,
item_description,
unit_of_measure,
quantity,
unit_price,
min_release_amount,
ship_to_location_id,
need_by_date,
clm_period_perf_start_date,
clm_period_perf_end_date,
promised_date,
last_updated_by,
last_update_date,
created_by,
creation_date,
auction_header_id,
auction_display_number,
auction_line_number,
bid_number,
bid_line_number,
orig_from_req_flag,
job_id,
amount
, advance_amount
, recoupment_rate
, progress_payment_rate
, retainage_rate
, max_retainage_amount
, Line_loc_populated_flag,
-- Clin Slin Changes
line_num_display,
group_line_id,
clm_info_flag,
clm_option_indicator,
clm_option_num,
clm_option_from_date,
clm_option_to_date,
clm_funded_flag,
clm_base_line_num,
-- Complex Pricing Changes
CONTRACT_TYPE,
COST_CONSTRAINT,
CLM_IDC_TYPE,
-- CLM Order Off IDV Project
from_header_id,
from_line_id,
clm_exhibit_name, --ELINs project
-- Event Based Delivery Project
CLM_DELIVERY_EVENT_CODE,
CLM_DELIVERY_PERIOD,
CLM_DELIVERY_PERIOD_UOM,
CLM_PROMISE_PERIOD,
CLM_PROMISE_PERIOD_UOM,
CLM_POP_DURATION,
CLM_POP_DURATION_UOM
)
SELECT
x_interface_header_id, -- interface_header_id
po_lines_interface_s.nextval, -- interface_line_id
NULL, -- requisition_line_id
paip.line_type_id, -- line_type_id
paip.item_id, -- item_id
paip.item_revision, -- item_revision
paip.category_id, -- category_id
substrb(paip.item_description, 1, 240), -- item_description
decode(paip.order_type_lookup_code, 'AMOUNT', null, mtluom.unit_of_measure), -- unit_of_measure
decode(paip.order_type_lookup_code, 'RATE', TO_NUMBER(null),
'FIXED PRICE', TO_NUMBER(null),
'AMOUNT', pbip.bid_currency_unit_price,
pbip.award_quantity), -- QUANTITY
decode(paip.order_type_lookup_code,'AMOUNT', 1,
'FIXED PRICE', TO_NUMBER(NULL)
,nvl2( pbip.award_shipment_number,pbs.bid_currency_unit_price
,pbip.bid_currency_unit_price)), --unit_price
nvl(pbip.po_bid_min_rel_amount, round(paip.po_min_rel_amount* pbh.rate,fc.precision)), -- min_release_amount
paip.ship_to_location_id, -- ship_to_location_id
/*Clm Bug : 10212430 : For clm,copy POP dates from offer. */
Decode(Nvl(l_is_fed,'N'),'Y',paip.clm_need_by_date,paip.need_by_start_date), -- need_by_date
Decode(Nvl(l_is_fed,'N'),'Y',pbip.promise_pop_start_date,null), -- period_of_performance_start_date
Decode(Nvl(l_is_fed,'N'),'Y',pbip.promise_pop_end_date,null), -- period_of_performance_end_date
pbip.promised_date, -- promised_date
p_user_id, -- last_update_by
sysdate, -- last_update_date
p_user_id, -- created_by
sysdate, -- creation_date
x_pdoi_header.auction_header_id, -- auction_header_id
x_pdoi_header.document_number, -- document_number
paip.line_number, -- auction_line_number,
pbip.bid_number, -- bid_number
pbip.line_number, -- bid_line_number
Decode('Y', (SELECT is_linked_pr_line_yn FROM pon_award_allocations paa
WHERE paa.bid_number = pbip.bid_number AND paa.bid_line_number = pbip.line_number AND paa.is_linked_pr_line_yn = 'Y' AND ROWNUM <2),
'S', 'N'), -- orig_from_req_flag
paip.job_id, -- job_id
decode(paip.order_type_lookup_code,'FIXED PRICE', pbip.bid_currency_unit_price, TO_NUMBER(NULL)) -- amount
, decode(pbip.bid_curr_advance_amount,0,null,pbip.bid_curr_advance_amount)
, pbip.recoupment_rate_percent
, pbip.progress_pymt_rate_percent
, pbip.retainage_rate_percent
, pbip.Bid_curr_max_retainage_amt
, decode(pbip.has_bid_payments_flag, 'Y', decode((select 1 from dual where exists
( select 1 from pon_bid_payments_shipments where
auction_header_id = pbip.auction_header_id and
bid_number= pbip.bid_number and bid_line_number=pbip.line_number
and bid_currency_price <> 0
)
),
1,'Y','N')
,'N') --Line_loc_populated
, paip.line_num_display
, paip.group_line_id
, paip.clm_info_flag
, paip.clm_option_indicator
, paip.clm_option_num
, paip.clm_option_from_date
, paip.clm_option_to_date
, paip.clm_funded_flag
, paip.clm_base_line_num
, paip.CLM_CONTRACT_TYPE
, paip.CLM_COST_CONSTRAINT
, paip.CLM_IDC_TYPE
--CLM Order Off IDV Project
, pbh.idv_header_id
, pbip.idv_line_id
, paip.exhibit_number --ELINs project
-- Event Based Delivery Project
,Decode(Nvl(l_is_fed,'N'),'Y',paip.CLM_DELIVERY_EVENT_CODE,null)
,Decode(Nvl(l_is_fed,'N'),'Y',paip.CLM_DELIVERY_PERIOD,null)
,Decode(Nvl(l_is_fed,'N'),'Y',paip.CLM_DELIVERY_PERIOD_UOM,null)
,Decode(Nvl(l_is_fed,'N'),'Y',pbip.CLM_PROMISE_PERIOD,null)
,Decode(Nvl(l_is_fed,'N'),'Y',pbip.CLM_PROMISE_PERIOD_UOM,null)
,Decode(Nvl(l_is_fed,'N'),'Y',pbip.CLM_PROMISE_POP_DURATION,null)
,Decode(Nvl(l_is_fed,'N'),'Y',pbip.CLM_PROMISE_POP_DURATION_UOM,null)
FROM
pon_auction_item_prices_all paip,
pon_bid_item_prices pbip,
mtl_units_of_measure mtluom,
pon_bid_headers pbh,
fnd_currencies fc,
pon_bid_shipments pbs
WHERE
pbip.bid_number = p_bid_number and
pbip.auction_header_id = p_auction_header_id and
nvl(pbip.award_status, 'NO') = 'AWARDED' and
Nvl(pbip.order_number, -1) = -1 and --Staggered Awards project
paip.auction_header_id = pbip.auction_header_id and
paip.line_number = pbip.line_number and
paip.group_type NOT IN ('GROUP','LOT_LINE') and
paip.uom_code = mtluom.uom_code (+) and
pbh.bid_number = pbip.bid_number and
fc.currency_code = pbh.bid_currency_code and
(nvl(paip.line_origination_code, 'NO') <> 'REQUISITION'
OR l_is_line_type_enabled = 'Y') and --Bug : 14134092 : If line type structure changes are enabled, insert autocreated lines as normal lines.
pbip.line_number >= l_batch_start and
pbip.line_number <= l_batch_end and
pbs.bid_number(+) = pbip.bid_number and
pbs.line_number(+) = pbip.line_number and
pbs.shipment_number(+) = pbip.award_shipment_number;
x_progress := '35.1: CREATE_PO_STRUCTURE: STANDARD CASE: END OF BULK INSERT';
-- Insert all Payments for all lines in one go, if any and complex work
IF (x_pdoi_header.progress_payment_type IN ('ACTUAL','FINANCE')) THEN
x_progress := '35.1.5: CREATE_PAYMENTS: STANDARD CASE: IT IS COMPLEX WORK';
INSERT INTO po_line_locations_interface (
interface_header_id,
interface_line_id,
interface_line_location_id,
payment_type,
shipment_num,
ship_to_location_id,
need_by_date,
promised_date,
quantity,
unit_of_measure,
price_override,
amount,
description,
work_approver_id,
project_id,
task_id,
award_id,
expenditure_type,
expenditure_organization_id,
expenditure_item_date,
auction_payment_id,
bid_payment_id,
last_update_date,
last_updated_by,
creation_date,
created_by )
SELECT
x_interface_header_id, -- interface_header_id
pli.interface_line_id, -- interface_line_id
po_line_locations_interface_s.NEXTVAL,
-- interface_line_location_id
bpys.payment_type_code, -- shipment_type
bpys.payment_display_number, -- shipment_num
nvl(apys.ship_to_location_id,
paip. ship_to_location_id), -- ship_to_location_id
decode(x_pdoi_header.supplier_enterable_pymt_flag,
'Y', paip.need_by_date , apys.need_by_date), -- need_by_date
bpys.promised_date, -- promised_date
nvl(bpys.quantity, decode(paip.order_type_lookup_code,
'QUANTITY',
pli.quantity, null
)
) , -- quantity. Populate this for RATE and Qty Milestone
nvl2(bpys.uom_code, mtluom.unit_of_measure,
decode(paip.order_type_lookup_code, 'QUANTITY',
(select unit_of_measure from
mtl_units_of_measure where uom_code=
paip.uom_code),
null
)
) , -- unit_of_measure.Populate this for RATE and Qty Milestone
nvl2(bpys.quantity, bpys.bid_currency_price,
decode(paip.order_type_lookup_code,'QUANTITY',
bpys.bid_currency_price, null)
), -- price_override. Populate this for RATE and Qty Milestone
nvl2(bpys.quantity, null,
decode(paip.order_type_lookup_code, 'QUANTITY',
null, bpys.bid_currency_price)
),-- amount.Populate this for LUMPSUM and Fixed Price Milestone
bpys.payment_description, -- item_description
decode(x_pdoi_header.supplier_enterable_pymt_flag,
'Y',paip.work_approver_user_id, apys.work_approver_user_id),
-- Work_approver_user_id
decode(x_pdoi_header.supplier_enterable_pymt_flag,
'Y', paip. project_id , apys.project_id), -- project_id
decode(x_pdoi_header.supplier_enterable_pymt_flag,
'Y', paip. project_task_id , apys.project_task_id),
-- project_task_id
decode(x_pdoi_header.supplier_enterable_pymt_flag,
'Y', paip.project_award_id,apys.project_award_id),
-- project_award_id
decode(x_pdoi_header.supplier_enterable_pymt_flag,
'Y', paip.project_expenditure_type,
apys.project_expenditure_type),
-- project_expenditure_type
decode(x_pdoi_header.supplier_enterable_pymt_flag,
'Y', paip. project_exp_organization_id,
apys.project_exp_organization_id),
-- project_exp_organization_id
decode(x_pdoi_header.supplier_enterable_pymt_flag, 'Y',
paip. project_expenditure_item_date,
apys.project_expenditure_item_date),
-- project_expenditure_date
bpys.auction_payment_id , -- auction_payment_id
bpys.bid_payment_id, -- bid_payment_id
sysdate, -- last_update_date
x_user_id, -- last_updated_by
sysdate, -- creation_date
x_user_id -- created_by
FROM pon_auction_item_prices_all paip,
pon_bid_item_prices pbip,
pon_bid_payments_shipments bpys,
pon_auc_payments_shipments apys,
po_lines_interface pli,
mtl_units_of_measure mtluom
WHERE pbip.bid_number = p_bid_number and
pbip.auction_header_id = p_auction_header_id and
nvl(pbip.award_status, 'NO') = 'AWARDED' and
paip.auction_header_id = pbip.auction_header_id and
paip.line_number = pbip.line_number and
bpys.bid_number = pbip.bid_number and
bpys.bid_line_number = pbip.line_number and
pli.interface_header_id = x_interface_header_id and
pli.auction_line_number = paip.line_number and
pli.auction_header_id = paip.auction_header_id and
bpys.auction_payment_id = apys.payment_id (+) and
bpys.uom_code = mtluom.uom_code (+) and
nvl(bpys.bid_currency_price,0) <> 0 and
pbip.line_number >= l_batch_start and
pbip.line_number <= l_batch_end;
x_progress := '35.1.10: CREATE_PAYMENTS: STANDARD CASE: END INSERTING PAYMENTS';
insert into po_lines_interface.
*/
IF l_allow_unsol_lines = 'Y' THEN
log_message('SPO : l_allow_unsol_lines '||l_allow_unsol_lines);
log_message('SPO : Before inserting unsolicited lines. ');
PON_UNSOL_CREATE_PO_PKG.insert_unsol_lines_spo(p_interface_header_id => x_interface_header_id,
p_auction_header_id => p_auction_header_id,
p_bid_number => p_bid_number,
p_document_number => x_pdoi_header.document_number,
p_is_fed => Nvl(l_is_fed,'N'),
p_user_id => p_user_id);
log_message('SPO : After inserting unsolicited lines succesfully. ');
/* Blanket Agreement case: will do bulk insert from one table to another */
IF (x_pdoi_header.contract_type = 'BLANKET') THEN --{ -- if outcome is BPA
/* rrkulkar-large-auction-support changes */
--------------------------------------------------------------------------------------------------------------
--BATCHING FOR OUTCOME = BLANKET PURCHASE AGREEMENT: STARTS HERE
--------------------------------------------------------------------------------------------------------------
--get the number of rows to be copied
select nvl(max(line_number),0)
into l_max_line_number
from pon_bid_item_prices
where bid_number = x_pdoi_header.bid_number;
INSERT into PO_LINES_INTERFACE (
interface_header_id,
interface_line_id,
requisition_line_id,
line_type_id,
line_num,
item_id,
item_revision,
category_id,
ip_category_id,
item_description,
unit_of_measure,
price_break_lookup_code,
quantity,
committed_amount,
unit_price,
min_release_amount,
ship_to_location_id,
need_by_date,
clm_period_perf_start_date,
clm_period_perf_end_date,
promised_date,
last_updated_by,
last_update_date,
created_by,
creation_date,
auction_header_id,
auction_display_number,
auction_line_number,
bid_number,
bid_line_number,
orig_from_req_flag,
job_id,
amount,
-- Clin Slin Changes
line_num_display,
group_line_id,
clm_info_flag,
clm_option_indicator,
clm_option_num,
clm_option_from_date,
clm_option_to_date,
clm_funded_flag,
clm_base_line_num,
-- Complex Pricing Changes
CONTRACT_TYPE,
COST_CONSTRAINT,
CLM_IDC_TYPE,
--CLM Order Off IDV Project
from_header_id,
from_line_id,
clm_exhibit_name --ELINs project
)
SELECT
x_interface_header_id, -- interface_header_id
po_lines_interface_s.nextval, -- interface_line_id
NULL, -- requisition_line_id
paip.line_type_id, -- line_type_id
l_rows_processed + rownum, -- line num
paip.item_id,
-- item_id
paip.item_revision,
-- item_revision
paip.category_id,
-- category_id
nvl(paip.ip_category_id, -2),
-- ip category id
substrb(paip.item_description, 1, 240),
-- item_description
decode(paip.order_type_lookup_code, 'AMOUNT', null, mtluom.unit_of_measure),
-- unit_of_measure
decode(pbip.price_break_type, 'NONE', null, 'NON-CUMULATIVE', 'NON CUMULATIVE', pbip.price_break_type),
-- price_break_type
decode(paip.order_type_lookup_code,
'AMOUNT', Decode(Nvl(l_is_fed,'N'),'Y',pbip.bid_currency_unit_price,NULL),
'RATE', NULL,
'FIXED PRICE', NULL,
pbip.award_quantity), -- quantity
decode(paip.order_type_lookup_code,
'AMOUNT', pbip.bid_currency_unit_price,
'RATE', round(paip.po_agreed_amount*pbh.rate, fc.precision),
'FIXED PRICE', round(paip.po_agreed_amount*pbh.rate, fc.precision),
null), -- committed_amount
decode(paip.order_type_lookup_code,
'AMOUNT', 1,
'FIXED PRICE', null,
nvl2( pbip.award_shipment_number,pbs.bid_currency_unit_price
,pbip.bid_currency_unit_price)), --unit_price
nvl(pbip.po_bid_min_rel_amount, round(paip.po_min_rel_amount* pbh.rate, fc.precision)), -- min_release_amount
paip.ship_to_location_id, -- ship_to_location_id
Decode(Nvl(l_is_fed,'N'),'Y',paip.clm_need_by_date,paip.need_by_start_date), -- need_by_date
Decode(Nvl(l_is_fed,'N'),'Y',paip.need_by_start_date,null), -- period_of_performance_start_date
Decode(Nvl(l_is_fed,'N'),'Y',paip.need_by_date,null), -- period_of_performance_end_date
pbip.promised_date, -- promised_date
p_user_id, -- last_update_by
sysdate, -- last_update_date
p_user_id, -- created_by
sysdate, -- creation_date
x_pdoi_header.auction_header_id, -- auction_header_id
x_pdoi_header.document_number, -- document_number
paip.line_number, -- auction_line_number,
x_pdoi_header.bid_number, -- bid_number
paip.line_number, -- bid_line_number
decode(paip.line_origination_code, 'REQUISITION', 'Y', 'N'), -- orig_from_req_flag
paip.job_id, -- job_id
decode(paip.order_type_lookup_code,
'FIXED PRICE', round(pbip.bid_currency_unit_price, fc.precision),
null) -- amount
-- Clin Slin Changes
, paip.line_num_display
, paip.group_line_id
, paip.clm_info_flag
, paip.clm_option_indicator
, paip.clm_option_num
, paip.clm_option_from_date
, paip.clm_option_to_date
, paip.clm_funded_flag
, paip.clm_base_line_num
-- Complex Pricing Changes
, paip.CLM_CONTRACT_TYPE
, paip.CLM_COST_CONSTRAINT
, paip.CLM_IDC_TYPE
--CLM Order Off IDV Project
, pbh.idv_header_id
, pbip.idv_line_id
, paip.exhibit_number --ELINs project
FROM pon_auction_item_prices_all paip,
pon_bid_item_prices pbip,
mtl_units_of_measure mtluom,
pon_bid_headers pbh,
fnd_currencies fc,
pon_bid_shipments pbs
WHERE pbip.bid_number = p_bid_number and
pbip.auction_header_id = p_auction_header_id and
nvl(pbip.award_status, 'NO') = 'AWARDED' and
paip.auction_header_id = pbip.auction_header_id and
paip.line_number = pbip.line_number and
paip.group_type NOT IN ('GROUP','LOT_LINE') and
paip.uom_code = mtluom.uom_code (+) and
pbh.bid_number = pbip.bid_number and
fc.currency_code = pbh.bid_currency_code and
pbip.line_number >= l_batch_start and
pbip.line_number <= l_batch_end and
pbs.bid_number(+) = pbip.bid_number and
pbs.line_number(+) = pbip.line_number and
pbs.shipment_number(+) = pbip.award_shipment_number;
log_message('blanket inserting info lines: l_rows_processed' || l_rows_processed );
UPDATE po_lines_interface PLI1
SET PLI1.group_line_id = (select PLI2.interface_line_id from po_lines_interface PLI2
where PLI2.interface_header_id = x_interface_header_id
and PLI2.auction_header_id = p_auction_header_id
and PLI2.auction_line_number = PLI1.group_line_id
AND PLI2.group_line_id IS NULL
AND ROWNUM < 2)
where PLI1.group_line_id is not null
and PLI1.interface_header_id = x_interface_header_id
and PLI1.auction_header_id = p_auction_header_id;
UPDATE po_lines_interface PLI1
SET PLI1.clm_base_line_num = (select PLI2.interface_line_id from po_lines_interface PLI2
where PLI2.interface_header_id = x_interface_header_id
and PLI2.auction_header_id = p_auction_header_id
and PLI2.auction_line_number = PLI1.clm_base_line_num
AND PLI2.clm_base_line_num IS NULL
AND ROWNUM < 2)
where PLI1.clm_base_line_num is not null
and PLI1.interface_header_id = x_interface_header_id
and PLI1.auction_header_id = p_auction_header_id;
log_message('Inserting iP Descriptors for lines: ' || l_batch_start || ' to ' || l_batch_end);
INSERT_IP_DESCRIPTORS(p_auction_header_id, p_bid_number, x_interface_header_id, p_user_id, fnd_global.login_id, l_batch_start, l_batch_end);
log_message('inserting blanket price break information');
-- Insert Price Break information
INSERT INTO po_lines_interface (
interface_header_id,
interface_line_id,
shipment_type,
line_type_id,
item_id,
item_revision,
quantity,
price_break_lookup_code,
unit_price,
price_discount,
ship_to_organization_id,
ship_to_location_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
line_num,
shipment_num,
effective_date,
expiration_date,
auction_header_id,
auction_line_number,
-- Clin Slin Changes
line_num_display,
group_line_id,
clm_info_flag,
clm_option_indicator,
clm_option_num,
clm_option_from_date,
clm_option_to_date,
clm_funded_flag,
clm_base_line_num,
-- Complex Pricing Changes
CONTRACT_TYPE,
COST_CONSTRAINT,
CLM_IDC_TYPE
)
SELECT
x_interface_header_id, -- interface_header_id
po_lines_interface_s.NEXTVAL, -- interface_line_id
pbs.shipment_type, -- shipment_type
paip.line_type_id, -- line_type_id
paip.item_id, -- item_id
paip.item_revision, -- item_revision
pbs.quantity, -- quantity
decode(pbip.price_break_type, 'NONE', null, 'NON-CUMULATIVE', 'NON CUMULATIVE', pbip.price_break_type),
-- price_break_type
pbs.bid_currency_unit_price, -- unit_price
pbs.price_discount, -- price_discount
pbs.ship_to_organization_id, -- ship_to_organization_id
pbs.ship_to_location_id, -- ship_to_location_id
sysdate, -- last_update_date
p_user_id, -- last_updated_by
sysdate, -- creation_date
p_user_id, -- created_by
pli.line_num, -- line num
pbs.shipment_number, -- shipment_number
pbs.effective_start_date, -- effective_date
pbs.effective_end_date, -- expiration_date
pbs.auction_header_id, -- auction_header_id
pbs.auction_line_number, -- auction_line_number
-- Clin Slin Changes
paip.line_num_display
, paip.group_line_id
, paip.clm_info_flag
, paip.clm_option_indicator
, paip.clm_option_num
, paip.clm_option_from_date
, paip.clm_option_to_date
, paip.clm_funded_flag
, paip.clm_base_line_num
-- Complex Pricing Changes
, paip.CLM_CONTRACT_TYPE
, paip.CLM_COST_CONSTRAINT
, paip.CLM_IDC_TYPE
FROM pon_auction_item_prices_all paip,
pon_bid_item_prices pbip,
pon_bid_shipments pbs,
po_lines_interface pli
WHERE pbip.bid_number = p_bid_number and
pbip.auction_header_id = p_auction_header_id and
nvl(pbip.award_status, 'NO') = 'AWARDED' and
paip.auction_header_id = pbip.auction_header_id and
paip.line_number = pbip.line_number and
pbs.bid_number = p_bid_number and
pli.interface_header_id = x_interface_header_id and
pli.auction_line_number = paip.line_number and
pli.auction_header_id = paip.auction_header_id and
pbs.shipment_type = 'PRICE BREAK' and
pbip.line_number = pbs.line_number and
pbip.line_number >= l_batch_start and
pbip.line_number <= l_batch_end ;
-- Insert Line Price Differentials
INSERT INTO po_price_diff_interface
(price_diff_interface_id,
price_differential_num,
entity_type,
interface_header_id,
interface_line_id,
price_type,
enabled_flag,
min_multiplier,
max_multiplier,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login)
SELECT
po_price_diff_interface_s.NEXTVAL, -- price_diff_interface_id
ppd.price_differential_number, -- price_differential_num
'BLANKET LINE', -- entity_type
x_interface_header_id, -- interface_line_id
pli.interface_line_id, -- interface_line_id
ppd.price_type, -- price_type
'Y', -- enabled_flag
ppd.multiplier, -- min_multiplier
pbpd.multiplier, -- max_multiplier
sysdate, -- last_update_date
p_user_id, -- last_updated_by
sysdate, -- creation_date,
p_user_id, -- created_by
fnd_global.login_id -- last_update_login
FROM pon_price_differentials ppd,
pon_bid_item_prices pbip,
pon_bid_price_differentials pbpd,
pon_auction_headers_all pah,
po_lines_interface pli
WHERE pbip.bid_number = p_bid_number
AND nvl(pbip.award_status, 'NO') = 'AWARDED'
AND pbip.auction_header_id = ppd.auction_header_id
AND pbip.line_number = ppd.line_number
AND ppd.shipment_number = -1
AND p_bid_number = pbpd.bid_number(+)
AND ppd.line_number = pbpd.line_number(+)
AND ppd.shipment_number = pbpd.shipment_number(+)
AND ppd.price_differential_number = pbpd.price_differential_number(+)
AND pah.auction_header_id = ppd.auction_header_id
AND pli.interface_header_id = x_interface_header_id
AND pli.auction_line_number = ppd.line_number
AND pli.auction_header_id = ppd.auction_header_id
AND pli.shipment_num IS NULL
AND pbip.line_number >= l_batch_start
AND pbip.line_number <= l_batch_end;
-- Insert Price Break Price Differentials
INSERT INTO po_price_diff_interface
(price_diff_interface_id,
price_differential_num,
entity_type,
interface_header_id,
interface_line_id,
price_type,
enabled_flag,
min_multiplier,
max_multiplier,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login)
SELECT
po_price_diff_interface_s.NEXTVAL, -- price_diff_interface_id
ppd.price_differential_number, -- price_differential_num
'PRICE BREAK', -- entity_type
x_interface_header_id, -- interface_header_id
pli.interface_line_id, -- interface_line_id
ppd.price_type, -- price_type
'Y', -- enabled_flag
ppd.multiplier, -- min_multiplier
pbpd.multiplier, -- max_multiplier
sysdate, -- last_update_date
p_user_id, -- last_updated_by
sysdate, -- creation_date,
p_user_id, -- created_by
fnd_global.login_id -- last_update_login
FROM pon_price_differentials ppd,
pon_bid_item_prices pbip,
(select pbpd.bid_number, pbpd.line_number,
pbpd.shipment_number, pbs.auction_shipment_number,
pbpd.price_differential_number, pbpd.price_type,
pbpd.multiplier, pbpd.auction_header_id
from pon_bid_price_differentials pbpd, pon_bid_shipments pbs
where pbs.bid_number = p_bid_number
and pbs.line_number = pbpd.line_number
and pbs.shipment_number = pbpd.shipment_number) pbpd,
pon_bid_shipments pbs,
pon_auction_headers_all pah,
po_lines_interface pli
WHERE pbip.bid_number = p_bid_number
AND nvl(pbip.award_status, 'NO') = 'AWARDED'
AND pbip.bid_number = pbs.bid_number
AND pbip.line_number = pbs.line_number
AND pbs.auction_header_id = ppd.auction_header_id
AND pbs.line_number = ppd.line_number
AND pbs.auction_shipment_number = ppd.shipment_number
AND pah.auction_header_id = ppd.auction_header_id
AND ppd.line_number = pbpd.line_number(+)
AND ppd.shipment_number = pbpd.auction_shipment_number(+)
AND ppd.price_differential_number = pbpd.price_differential_number(+)
AND p_bid_number = pbpd.bid_number(+)
AND pli.interface_header_id = x_interface_header_id
AND pli.auction_line_number = pbs.line_number
AND pli.auction_header_id = pbs.auction_header_id
AND pli.shipment_num = pbs.shipment_number
AND pbip.line_number >= l_batch_start
AND pbip.line_number <= l_batch_end;
INSERT INTO po_lines_interface_debug
(SELECT * FROM po_lines_interface WHERE interface_header_id = x_interface_header_id);
INSERT INTO po_price_diff_interface_debug
(SELECT * FROM po_price_diff_interface WHERE interface_header_id = x_interface_header_id);
x_progress := '38: CREATE_PO_STRUCTURE: BLANKET CASE: END OF BULK INSERT';
insert into po_lines_interface.
*/
log_message('BPA : l_allow_unsol_lines '||l_allow_unsol_lines);
log_message('BPA : Before inserting unsolicited lines. ');
PON_UNSOL_CREATE_PO_PKG.insert_unsol_lines_bpa(p_interface_header_id => x_interface_header_id,
p_auction_header_id => p_auction_header_id,
p_bid_number => p_bid_number,
p_document_number => x_pdoi_header.document_number,
p_is_fed => Nvl(l_is_fed,'N'),
p_user_id => p_user_id,
x_rows_processed => l_rows_processed);
log_message('BPA : After inserting unsolicited lines succesfully, l_rows_processed '||l_rows_processed);
/* Update all the slin numbers's group line id with interface line id of the Corresponding CLIN to maintain
CLIN - SLIN relationships .
The below update query updates the mappings only for records, which were backed up by requisitions or
those records, which were splitted because of excess awarding or those records which were added in the
solicitation in relation with Requisition. */
UPDATE po_lines_interface PLI1
SET PLI1.group_line_id = Nvl( (select PLI2.interface_line_id
from po_lines_interface PLI2
where PLI2.interface_header_id = x_interface_header_id
and PLI2.auction_header_id = p_auction_header_id
and PLI2.auction_line_number = PLI1.group_line_id
AND PLI2.group_line_id IS NULL
AND PLI2.requisition_line_id IS NOT NULL
AND ROWNUM < 2), PLI1.group_line_id)
where PLI1.group_line_id is not null
and PLI1.interface_header_id = x_interface_header_id
and PLI1.auction_header_id = p_auction_header_id;
/* Update all the slin numbers's group line id with interface line id of the Corresponding CLIN to maintain
CLIN - SLIN relationships .
The below update query updates the mappings only for records, which were added in the solicitions
and not updated in the above query*/
UPDATE po_lines_interface PLI1
SET PLI1.group_line_id = (select PLI2.interface_line_id from po_lines_interface PLI2
where PLI2.interface_header_id = x_interface_header_id
and PLI2.auction_header_id = p_auction_header_id
and PLI2.auction_line_number = PLI1.group_line_id
AND PLI2.group_line_id IS NULL
AND ROWNUM < 2)
where PLI1.group_line_id is not null
and PLI1.interface_header_id = x_interface_header_id
and PLI1.auction_header_id = p_auction_header_id
and PLI1.group_line_id <> Nvl ( (SELECT PLI3.interface_line_id
FROM po_lines_interface PLI3
WHERE PLI3.interface_header_id = x_interface_header_id
and PLI3.auction_header_id = p_auction_header_id
AND PLI3.interface_line_id = PLI1.group_line_id), -99999 );
/* Update all the slin numbers's clm base line num id with interface line id of the Corresponding
Base CLIN/SLIN to maintain BASE - OPTION relationships.
The below update query updates the mappings only for records, which were backed up by requisitions or
those records, which were splitted because of excess awarding or those records which were added in the
solicitation in relation with Requisition. */
UPDATE po_lines_interface PLI1
SET PLI1.clm_base_line_num = Nvl( (select PLI2.interface_line_id
from po_lines_interface PLI2
where PLI2.interface_header_id = x_interface_header_id
and PLI2.auction_header_id = p_auction_header_id
and PLI2.auction_line_number = PLI1.clm_base_line_num
AND PLI2.clm_base_line_num IS NULL
AND PLI2.requisition_line_id IS NOT NULL
AND ROWNUM < 2), PLI1.clm_base_line_num)
where PLI1.clm_base_line_num is not null
and PLI1.interface_header_id = x_interface_header_id
and PLI1.auction_header_id = p_auction_header_id;
/* Update all the slin numbers's clm base line num id with interface line id of the Corresponding
Base CLIN/SLIN to maintain BASE - OPTION relationships.
The below update query updates the mappings only for records, which were added in the solicitions
and not updated in the above query*/
UPDATE po_lines_interface PLI1
SET PLI1.clm_base_line_num = (select PLI2.interface_line_id from po_lines_interface PLI2
where PLI2.interface_header_id = x_interface_header_id
and PLI2.auction_header_id = p_auction_header_id
and PLI2.auction_line_number = PLI1.clm_base_line_num
AND PLI2.clm_base_line_num IS NULL
AND ROWNUM < 2)
where PLI1.clm_base_line_num is not null
and PLI1.interface_header_id = x_interface_header_id
and PLI1.auction_header_id = p_auction_header_id
and PLI1.clm_base_line_num <> Nvl ( (SELECT PLI3.interface_line_id
FROM po_lines_interface PLI3
WHERE PLI3.interface_header_id = x_interface_header_id
and PLI3.auction_header_id = p_auction_header_id
AND PLI3.interface_line_id = PLI1.clm_base_line_num), -99999 );
FOR upd_row IN update_clin_num_cursor(x_interface_header_id,p_auction_header_id) LOOP
next_clin_num := pon_clo_renumber_pkg.next_clin_num(clin_num_tbl);
UPDATE po_lines_interface
SET line_num_display = next_clin_num
WHERE interface_header_id = upd_row.interface_header_id
AND interface_line_id = upd_row.interface_line_id;
FOR upd_row IN update_slin_num_cursor(x_interface_header_id,p_auction_header_id) LOOP
log_message('interface_header_id : '||upd_row.interface_header_id);
PROCEDURE INSERT_IP_DESCRIPTORS(p_auction_header_id IN NUMBER,
p_bid_number IN NUMBER,
p_interface_header_id IN NUMBER,
p_user_id IN NUMBER,
p_login_id IN NUMBER,
p_batch_start IN NUMBER,
p_batch_end IN NUMBER) IS
l_cursorName NUMBER;
SELECT pbip.line_number,
pli.interface_line_id,
paip.item_description,
nvl(paip.ip_category_id, -2) ip_category_id,
nvl(paip.item_id, -2) item_id,
paip.org_id,
decode(icx.type, 0, 'TXT', 1, 'NUM', 2, 'TRANS') datatype,
icx.stored_in_table,
icx.stored_in_column,
pbav.value,
paa.attribute_name
FROM pon_bid_item_prices pbip,
pon_auction_item_prices_all paip,
po_lines_interface pli,
pon_bid_attribute_values pbav,
pon_auction_attributes paa,
icx_cat_agreement_attrs_v icx
WHERE pbip.auction_header_id = p_auction_header_id and
pbip.bid_number = p_bid_number and
nvl(pbip.award_status, 'NO') = 'AWARDED' and
pbip.line_number >= p_batch_start and
pbip.line_number <= p_batch_end and
pbip.auction_header_id = paip.auction_header_id and
pbip.line_number = paip.line_number and
pli.interface_header_id = p_interface_header_id and
pbip.auction_header_id = pli.auction_header_id and
pbip.line_number = pli.auction_line_number and
pbip.auction_header_id = pbav.auction_header_id (+) and
pbip.bid_number = pbav.bid_number (+) and
pbip.line_number = pbav.line_number (+) and
pbav.auction_header_id = paa.auction_header_id (+) and
pbav.line_number = paa.line_number (+) and
pbav.sequence_number = paa.sequence_number (+) and
paa.ip_category_id (+) is not null and
paa.ip_category_id = icx.rt_category_id (+) and
paa.ip_descriptor_id = icx.attribute_id (+) and
icx.language (+) = userenv('LANG')
ORDER BY interface_line_id asc, decode(datatype, 'NUM', 0, 'TXT', 1, 2) asc;
select language_code
into l_language_code
from pon_auction_headers_all
where auction_header_id = p_auction_header_id;
'insert into po_attr_values_interface(' ||
'interface_header_id, ' ||
'interface_line_id, ' ||
'interface_attr_values_id, ' ||
'ip_category_id, ' ||
'inventory_item_id, ' ||
'org_id, ' ||
'last_update_login, ' ||
'last_updated_by, ' ||
'last_update_date, ' ||
'created_by, ' ||
'creation_date' ||
l_po_attr_values_cols ||
') values('||
':1, ' ||
':2, ' ||
':3, ' ||
':4, ' ||
':5, ' ||
':6, ' ||
':7, ' ||
':8, ' ||
':9, ' ||
':10, ' ||
':11' ||
l_po_attr_values_vals ||
')';
'insert into po_attr_values_tlp_interface(' ||
'interface_header_id, ' ||
'interface_line_id, ' ||
'interface_attr_values_tlp_id, ' ||
'ip_category_id, ' ||
'inventory_item_id, ' ||
'org_id, ' ||
'language, ' ||
'description, ' ||
'long_description, ' ||
'last_update_login, ' ||
'last_updated_by, ' ||
'last_update_date, ' ||
'created_by, ' ||
'creation_date' ||
l_po_attr_values_tlp_cols ||
') values('||
':1, ' ||
':2, ' ||
':3, ' ||
':4, ' ||
':5, ' ||
':6, ' ||
':7, ' ||
':8, ' ||
':9, ' ||
':10, ' ||
':11, ' ||
':12, ' ||
':13, ' ||
':14' ||
l_po_attr_values_tlp_vals ||
')';
select po_attr_values_interface_s.nextval
into l_cur_attr_values_id
from dual;
select po_attr_values_tlp_interface_s.nextval
into l_cur_attr_values_tlp_id
from dual;
END INSERT_IP_DESCRIPTORS;
x_last_update_date pon_auction_headers_all.last_update_date%TYPE;
SELECT pbh.bid_number
FROM pon_bid_headers pbh
WHERE pbh.auction_header_id = p_auction_header_id and
nvl(pbh.bid_status, 'NONE') = 'ACTIVE' and
--pbh.po_header_id is NULL and
nvl(pbh.award_status, 'NO') IN ('AWARDED', 'PARTIAL') and
exists (select 1 from pon_bid_item_prices pbip
where pbip.bid_number = pbh.bid_number
and nvl(pbip.award_status,'NO') = 'AWARDED'
and pbip.order_number is null)
GROUP BY pbh.bid_number;
select open_bidding_date,
close_bidding_date,
view_by_date
into x_auction_start_date,
x_auction_end_date,
x_preview_date
from pon_auction_headers_all
where auction_header_id = p_auction_header_id;
SELECT last_update_date, doctype_id, conterms_exist_flag
INTO x_last_update_date, x_doctype_id, x_conterms_exist_flag
FROM pon_auction_headers_all
WHERE auction_header_id = p_auction_header_id
FOR UPDATE;
select clm_document_number into x_order_number
from po_headers_all
where
po_header_id = x_po_header_id;
x_progress := '68: GENERATE_POS: Before call to Delete interface header id '||x_interface_header_id;
/* PO_SOURCING_GRP.DELETE_INTERFACE_HEADER(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_return_status => x_return_status,
p_interface_header_id => x_interface_header_id
);
x_progress := '68.1: GENERATE_POS: Just after call to Delete interface header status:'||x_return_status;
x_error_code := PO_DELETE_ERROR;
x_progress := substrb('68.2: GENERATE_POS: delete interface header error:'||x_error_msg, 1,4000);
select clm_document_number into x_order_number
from po_headers_all
where
po_header_id = x_po_header_id;
UPDATE pon_bid_headers
SET po_header_id = decode(x_error_code, PO_SUCCESS, x_po_header_id, null),
order_number = x_order_number,
po_error_code = x_error_code,
po_error_msg = x_error_msg,
po_wf_creation_rnd = decode(x_error_code, PO_SUCCESS, x_round_number, po_wf_creation_rnd)
where auction_header_id = p_auction_header_id and
bid_number = x_bid_number;
--Added for Staggered Awards, update order_number for each bid line
UPDATE pon_bid_item_prices
SET po_header_id = decode(x_error_code, PO_SUCCESS, x_po_header_id, null),
order_number = x_order_number,
po_error_code = x_error_code,
po_error_msg = x_error_msg,
po_wf_creation_rnd = decode(x_error_code, PO_SUCCESS, x_round_number, po_wf_creation_rnd),
initiate_approval = x_pdoi_header.initiate_approval
WHERE auction_header_id = p_auction_header_id and
bid_number = x_bid_number AND
Nvl(order_number, '-1') = -1 AND
award_status = 'AWARDED';
INSERT
INTO pon_orca_cert_details (
ORCA_CERT_DETAILS_ID,
VENDOR_ID,
VENDOR_SITE_ID,
DUNS_NUMBER,
RESPONSE_CODE,
RESPONSE_MESSAGE,
TIME_RECEIVED,
REG_STATUS_CODE,
VALID_FROM,
VALID_TO,
XML_ORCA_RECORD,
DOCUMENT_TYPE,
DOCUMENT_NUMBER,
CREATED_BY,
ATTACH_PRI_KEY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
SELECT PON_ORCA_CERT_DETAIL_S.NEXTVAL,
pocd.vendor_id,
pocd.vendor_site_id,
pocd.duns_number,
pocd.response_code,
pocd.response_message,
pocd.time_received,
pocd.reg_status_code,
pocd.valid_from,
pocd.valid_to,
pocd.xml_orca_record,
'AWARD' document_type,
TO_CHAR(x_po_header_id) document_number,
pocd.created_by,
pocd.attach_pri_key,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id
FROM pon_orca_cert_details pocd, po_headers_all pha, pon_auction_headers_all paha
WHERE pocd.document_number = paha.document_number
AND paha.auction_header_id = p_auction_header_id
AND pha.po_header_id = x_po_header_id
AND pha.vendor_id = pocd.vendor_id
AND pha.vendor_site_id = pocd.vendor_site_id;
select wf_approval_itemtype,
wf_approval_process
into x_ItemType,
x_workflow_process
from po_document_types
where document_type_code = x_doc_type
and document_subtype = x_doc_subtype;
SELECT to_char(PO_WF_ITEMKEY_S.NEXTVAL)
INTO x_seq_for_item_key
FROM dual;
SELECT employee_id
INTO x_preparer_id
FROM fnd_user
WHERE user_id = p_user_id;
'updatesourcingrule: ' || p_pdoi_header.update_sourcing_rules;
p_pdoi_header.update_sourcing_rules
);
SELECT count(pbh.bid_number)
INTO x_number_of_failed_pos
FROM pon_bid_headers pbh
WHERE pbh.auction_header_id = x_auction_header_id and
nvl(pbh.bid_status, 'NONE') not in ('ARCHIVED', 'DISQUALIFIED') and
pbh.po_header_id is NULL and
nvl(pbh.award_status, 'NO') in ('AWARDED', 'PARTIAL');
UPDATE PON_AUCTION_HEADERS_ALL
SET OUTCOME_STATUS = 'OUTCOME_FAILED'
WHERE AUCTION_HEADER_ID = x_auction_header_id;
/* update auction outcome status to outcome_completed */
UPDATE PON_AUCTION_HEADERS_ALL
SET OUTCOME_STATUS = 'OUTCOME_COMPLETED'
WHERE AUCTION_HEADER_ID = x_auction_header_id;
/* Selects all relevant information for the first 10 successful POs of the current round*/
CURSOR successfulPOs is
SELECT *
FROM (
SELECT pbh.bid_number,
pov.vendor_name,
pbh.vendor_site_code,
papf.full_name,
pbh.order_number
FROM pon_bid_headers pbh,
po_vendors pov,
per_all_people_f papf
WHERE pbh.auction_header_id = x_auction_header_id
AND pbh.bid_status = 'ACTIVE'
AND pbh.po_header_id is NOT NULL
AND pbh.po_wf_creation_rnd = x_round_number
AND pbh.vendor_id = pov.vendor_id
AND pbh.agent_id = papf.person_id
AND papf.effective_start_date < sysdate
AND papf.effective_end_date = (select max(papf2.effective_end_date)
from per_all_people_f papf2
where papf2.person_id = pbh.agent_id)
GROUP BY
pbh.bid_number,
pov.vendor_name,
pbh.vendor_site_code,
papf.full_name,
pbh.order_number
)
WHERE rownum <= 10;
SELECT wf_poncompl_item_key, contract_type
INTO itemkey, x_contract_type
FROM pon_auction_headers_all
WHERE auction_header_id = x_auction_header_id;
SELECT pbh.bid_number,
pov.vendor_name,
povsa.vendor_site_code,
papf.full_name,
pbh.order_number,
pbh.po_error_code,
pbh.po_error_msg
FROM pon_bid_headers pbh,
po_vendors pov,
po_vendor_sites_all povsa,
per_all_people_f papf
WHERE pbh.auction_header_id = x_auction_header_id
AND pbh.bid_status = 'ACTIVE'
AND nvl(pbh.award_status, 'NO') in ('AWARDED', 'PARTIAL')
AND pbh.po_header_id is NULL
AND pbh.vendor_id = pov.vendor_id
AND pbh.vendor_id = povsa.vendor_id
AND pbh.agent_id = papf.person_id
AND papf.effective_start_date < sysdate
AND papf.effective_end_date = (select max(papf2.effective_end_date)
from per_all_people_f papf2
where papf2.person_id = pbh.agent_id)
GROUP BY
pbh.bid_number,
pov.vendor_name,
povsa.vendor_site_code,
papf.full_name,
pbh.order_number,
pbh.po_error_code,
pbh.po_error_msg;
SELECT wf_poncompl_item_key, contract_type
INTO itemkey, x_contract_type
FROM pon_auction_headers_all
WHERE auction_header_id = x_auction_header_id;
select open_bidding_date,
close_bidding_date,
trading_partner_contact_id,
doctype_id,
trading_partner_name,
trading_partner_contact_name,
has_items_flag,
nvl(wf_poncompl_current_round, 0)
into x_open_bidding_date,
x_close_bidding_date,
x_trading_partner_contact_id,
x_doctype_id,
x_trading_partner_name,
x_trading_partner_contact_name,
x_has_items,
x_current_round
from pon_auction_headers_all
where auction_header_id = p_auction_header_id;
SELECT 'REQUISITION'
INTO x_requistion_based
FROM DUAL
WHERE EXISTS(
SELECT '1'
FROM pon_auction_item_prices_all
WHERE auction_header_id = p_auction_header_id
AND line_origination_code = 'REQUISITION'
);
UPDATE pon_auction_headers_all set
outcome_status = decode(x_current_round, 0, 'OUTCOME_INITIATED', 'OUTCOME_REINITIATED'),
last_update_date = sysdate
WHERE auction_header_id = p_auction_header_id;
x_progress := ++l_progress || l_api_name || ' : updated the outcome_status with round number :' || x_current_round;
SELECT 'Y' INTO l_linked_pr_line_exists
FROM pon_bid_backing_requisitions
WHERE auction_header_id = p_auction_header_id
AND ROWNUM <2;
po_negotiations_sv1.update_sol_ref_delete_all(p_auction_header_id => p_auction_header_id,
p_delete_pbr_yn => 'N',
x_return_status => x_return_status,
x_error_msg => x_msg_data,
x_error_code => l_error_code);
SELECT count(pbh.bid_number)
INTO x_number_of_failed_pos
FROM pon_bid_headers pbh
WHERE pbh.auction_header_id = p_auction_header_id and
nvl(pbh.bid_status, 'NONE') = 'ACTIVE' and
pbh.po_header_id is NULL and
nvl(pbh.award_status, 'NO') in ('AWARDED', 'PARTIAL');
UPDATE PON_AUCTION_HEADERS_ALL
SET OUTCOME_STATUS = 'OUTCOME_FAILED'
WHERE AUCTION_HEADER_ID = p_auction_header_id;
/* update auction outcome status to outcome_completed */
UPDATE PON_AUCTION_HEADERS_ALL
SET OUTCOME_STATUS = 'OUTCOME_COMPLETED'
WHERE AUCTION_HEADER_ID = p_auction_header_id;
UPDATE PON_AUCTION_HEADERS_ALL
SET OUTCOME_STATUS = 'OUTCOME_FAILED'
WHERE AUCTION_HEADER_ID = p_auction_header_id;
if there was an exception, we have updated the status as well
*/
COMMIT;
SELECT pon_auction_wf_createpo_s.nextval
INTO x_sequence
FROM dual;
-- update pon_auction_headers_all.wf_poncompl_current_round by incrementing by one
-- update pon_bid_headers.po_wf_creation_rnd by incrementing by one
UPDATE pon_auction_headers_all set
wf_poncompl_item_key = x_itemkey,
wf_poncompl_current_round = x_current_round+1,
last_update_date = sysdate
WHERE auction_header_id = p_auction_header_id;
UPDATE pon_bid_headers set
po_wf_creation_rnd = x_current_round+1
WHERE auction_header_id = p_auction_header_id;
SELECT interface_line_id , auction_line_number
FROM po_lines_interface
WHERE interface_header_id = x_interface_id;
UPDATE po_lines_interface
SET group_line_id = oneline.interface_line_id
WHERE interface_header_id = x_interface_id
AND group_line_id = oneline.auction_line_number;
UPDATE po_lines_interface
SET clm_base_line_num = oneline.interface_line_id
WHERE interface_header_id = x_interface_id
AND clm_base_line_num = oneline.auction_line_number;
API to update PON_AWARD_ALLOCATION table - allocated funds.
This is to figure out how much of funds are available for each bid line from the req Line
Later this will be used by the po_auto_line_process_pvt.create_pon_back_req_dist to apportion
the funds based on the allocated_funds in the ratio of distributions for each bid line.
The formula here is funds_remaining on req line(R1) * Bid_line_amount(B1) /(Sum of all bid Line amounts where this req line is linked(B1+B2+B3)
allocated_funds := R1*B1/(B1+B2+B3) */
PROCEDURE create_pon_back_req_alloc(p_auction_header_id IN NUMBER, x_resultout in out nocopy varchar2)
IS
l_user_id NUMBER;
DELETE FROM pon_award_allocations WHERE bid_number IN (
SELECT bid_number FROM pon_bid_headers
WHERE auction_header_id = p_auction_header_id
AND po_header_id IS NULL)
AND IS_LINKED_PR_LINE_YN = 'Y';
x_progress := ++l_progress || l_api_name || ' : after delete : ' || l_user_id;
INSERT INTO pon_award_allocations(bid_number,
bid_line_number,
orig_req_line_id,
orig_req_header_id,
auction_header_id,
allocated_funds,
is_linked_pr_line_yn,
created_by,
last_update_date,
last_updated_by,
last_update_login,
creation_date
)
( SELECT pbip.bid_number,
pbip.line_number,
pbrv.requisition_line_id,
pbrv.requisition_header_id,
pbip.auction_header_id,
(pbip.award_quantity * pbip.award_price * prl.funds_remaining )/
(rec_bid_funds.tot_bid_line_amount),
'Y',
l_user_id,
SYSDATE,
l_user_id,
l_login_id,
SYSDATE
FROM pon_bid_item_prices pbip,
pon_bid_headers pbh,
po_clmreq_lines_v prl,
(SELECT pbip1.auction_header_id, pbip1.auction_line_number,pbip1.bid_number, pbip1.line_number AS bid_line_number,
pbr.requisition_line_id, pbr.requisition_header_id
FROM pon_bid_item_references pir, pon_bid_backing_requisitions pbr, pon_bid_item_prices pbip1
WHERE pir.auction_header_id = p_auction_header_id
AND pir.auction_header_id = pbr.auction_header_id
AND pir.auction_line_number = pbr.auction_line_number
AND pir.bid_number = pbip1.bid_number
AND pir.line_number = pbip1.line_number
AND pir.link_done_by = 'BOTH'
union
SELECT pbr.auction_header_id, pbr.auction_line_number, pbr.bid_number, pbr.bid_line_number,
pbr.requisition_line_id , pbr.requisition_header_id
FROM pon_bid_backing_requisitions pbr
WHERE pbr.auction_header_id = p_auction_header_id) pbrv,
(SELECT pbrv1.requisition_line_id, sum (pbip.award_quantity * pbip.award_price) AS tot_bid_line_amount
FROM pon_bid_item_prices pbip,
(SELECT pbip1.auction_header_id, pbip1.auction_line_number,pbip1.bid_number, pbip1.line_number AS bid_line_number,
pbr.requisition_line_id, pbr.requisition_header_id
FROM pon_bid_item_references pir, pon_bid_backing_requisitions pbr, pon_bid_item_prices pbip1
WHERE pir.auction_header_id = p_auction_header_id
AND pir.auction_header_id = pbr.auction_header_id
AND pir.auction_line_number = pbr.auction_line_number
AND pir.bid_number = pbip1.bid_number
AND pir.line_number = pbip1.line_number
AND pir.link_done_by = 'BOTH'
union
SELECT pbr.auction_header_id, pbr.auction_line_number, pbr.bid_number, pbr.bid_line_number,
pbr.requisition_line_id , pbr.requisition_header_id
FROM pon_bid_backing_requisitions pbr
WHERE pbr.auction_header_id = p_auction_header_id ) pbrv1
WHERE pbip.auction_header_id = p_auction_header_id
AND pbip.auction_header_id = pbrv1.auction_header_id
AND pbip.auction_line_number = pbrv1.auction_line_number
AND Decode(pbrv1.bid_number, -1, pbip.bid_number, pbrv1.bid_number) = pbip.bid_number
AND Decode(pbrv1.bid_line_number, -1, pbip.line_number, pbrv1.bid_line_number) = pbip.line_number
AND pbip.award_status = 'AWARDED'
GROUP BY (pbrv1.auction_header_id,pbrv1.requisition_line_id )) rec_bid_funds
WHERE pbh.auction_header_id = p_auction_header_id
AND pbh.bid_number = pbip.bid_number
AND pbh.po_header_id IS NULL
AND pbip.award_status = 'AWARDED'
AND Nvl(pbip.clm_info_flag, 'N') <> 'Y'
AND Nvl(pbip.clm_option_indicator, 'B') <> 'O'
AND pbip.auction_header_id = pbrv.auction_header_id
AND pbip.auction_line_number = pbrv.auction_line_number
AND Decode(pbrv.bid_number, -1,-1 , pbip.bid_number) = pbrv.bid_number
AND Decode(pbrv.bid_line_number, -1,-1 , pbip.line_number) = pbrv.bid_line_number
AND prl.requisition_line_id = pbrv.requisition_line_id
AND pbrv.requisition_line_id = rec_bid_funds.requisition_line_id
AND prl.funds_remaining > 0
--Bug : 14134092 :For unsol lines, skip fund allocations from inventory destination req lines.
AND (pbip.auction_line_number <> -1 OR (pbip.auction_line_number = -1 AND prl.destination_type_code = 'EXPENSE')));
/* AND NOT EXISTS (SELECT 1
FROM pon_auction_item_prices_all paip
WHERE paip.auction_header_id = pbrv.auction_header_id
AND paip.line_number = pbrv.auction_line_number
AND paip.line_origination_code = 'REQUISITION'));*/
x_progress := ++l_progress || l_api_name || ' : after insert : ' || l_count;
UPDATE pon_award_allocations paa
SET allocated_funds = (SELECT CASE
WHEN (tot_amt_per_bid.allocated_funds > (pbip.award_quantity * pbip.award_price) )
--equate allocated funds to bid_line_amount
THEN ((paa.allocated_funds * (pbip.award_quantity * pbip.award_price ))/tot_amt_per_bid.allocated_funds )
ELSE paa.allocated_funds
END
FROM pon_bid_item_prices pbip,
(SELECT paa1.bid_number, paa1.bid_line_number, Sum(paa1.allocated_funds) AS allocated_funds
FROM pon_award_allocations paa1
GROUP BY (paa1.bid_number, paa1.bid_line_number)) tot_amt_per_bid
WHERE paa.auction_header_id = p_auction_header_id
AND pbip.bid_number = paa.bid_number
AND pbip.line_number = paa.bid_line_number
AND tot_amt_per_bid.bid_number = paa.bid_number
AND tot_amt_per_bid.bid_line_number = paa.bid_line_number) ;
x_progress := ++l_progress || l_api_name || ' : after insert : ' || l_count;
SELECT
vendor_contact_id into l_vendor_contact_id
FROM po_vendor_contacts
WHERE per_party_id = p_trading_partner_contact_id
AND vendor_site_id = p_vendor_site_id
AND vendor_id = p_vendor_id
AND INACTIVE_DATE > sysdate;