[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;
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.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;
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)
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;
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
BULK COLLECT INTO l_award_col, l_bid_number_col, l_bid_price_col
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;
x_progress := '30: Auto_Req_Allocation: ' || 'qty allocated: ' || l_allocated_qty_insertcol(l_insert_index) || ' index: ' || l_insert_index;
l_auction_header_id_insertcol(l_insert_index) := p_auctionID;
l_insert_index := l_insert_index + 1;
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, 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),
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;
/* Selects the data from Sourcing that will populate PO_HEADERS_INTERFACE */
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,
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
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.need_by_start_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
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 split_req_line_id, allocated_qty
FROM pon_award_allocations
WHERE auction_header_id = p_auction_header_id and
bid_number = p_bid_number and
bid_line_number = x_line_number and
nvl(allocated_qty,0) <> 0 and
nvl(split_req_line_id, -999) <> -999;
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(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 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.
*/
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,
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)
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
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.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); -- 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);
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,
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
)
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
x_pdoi_line.need_by_start_date,
-- need_by_start_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
) 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,
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
)
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
x_pdoi_line.need_by_start_date,
-- need_by_start_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
) 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;
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,
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
)
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
paip.need_by_start_date, -- need_by_start_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
'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
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
nvl(paip.line_origination_code, 'NO') <> 'REQUISITION' 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;
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';
/* 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,
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
)
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', 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
paip.need_by_start_date, -- need_by_start_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
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('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)
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
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';
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, ' ||
'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' ||
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')
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;
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);
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;
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 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;