[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE insert_unsol_lines_spo(p_interface_header_id IN NUMBER,
p_auction_header_id IN NUMBER,
p_bid_number IN NUMBER,
p_document_number IN VARCHAR2,
p_is_fed IN VARCHAR2,
p_user_id IN NUMBER) IS
BEGIN
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,
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,
CONTRACT_TYPE,
COST_CONSTRAINT,
CLM_IDC_TYPE,
from_header_id,
from_line_id,
-- 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
p_interface_header_id, -- interface_header_id
po_lines_interface_s.nextval, -- interface_line_id
NULL, -- requisition_line_id
pbip.line_type_id, -- line_type_id
NULL,--pbip.item_id, -- item_id
NULL,--pbip.item_revision, -- item_revision
pbip.category_id, -- category_id
substrb(pbip.item_description, 1, 240), -- item_description
decode(pbip.order_type_lookup_code, 'AMOUNT', null, mtluom.unit_of_measure), -- unit_of_measure
decode(pbip.order_type_lookup_code, 'RATE', TO_NUMBER(null),
'FIXED PRICE', TO_NUMBER(null),
'AMOUNT', pbip.bid_currency_unit_price,
pbip.award_quantity), -- QUANTITY
decode(pbip.order_type_lookup_code,'AMOUNT', 1,
'FIXED PRICE', TO_NUMBER(NULL),pbip.bid_currency_unit_price), --unit_price
pbip.po_bid_min_rel_amount, -- min_release_amount
pbip.ship_to_location_id, -- ship_to_location_id
null, -- need_by_date
Decode(Nvl(p_is_fed,'N'),'Y',pbip.promise_pop_start_date,null), -- period_of_performance_start_date
Decode(Nvl(p_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
p_auction_header_id, -- auction_header_id
p_document_number, -- document_number
pbip.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
--pbip.job_id, -- job_id
decode(pbip.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
, 'N' --Line_loc_populated
, pbip.line_num_display
, pbip.group_line_id
, pbip.clm_info_flag
, pbip.clm_option_indicator
, pbip.clm_option_num
, pbip.clm_option_from_date
, pbip.clm_option_to_date
, pbip.clm_funded_flag
, pbip.clm_base_line_num
, pbip.CLM_CONTRACT_TYPE
, pbip.CLM_COST_CONSTRAINT
, pbip.CLM_IDC_TYPE
--CLM Order Off IDV Project
, pbh.idv_header_id
, pbip.idv_line_id
-- Event Based Delivery Project
,Decode(Nvl(p_is_fed,'N'),'Y',pbip.CLM_DELIVERY_EVENT_CODE,null)
,Decode(Nvl(p_is_fed,'N'),'Y',pbip.CLM_PROMISE_PERIOD,null)
,Decode(Nvl(p_is_fed,'N'),'Y',pbip.CLM_PROMISE_PERIOD_UOM,null)
,Decode(Nvl(p_is_fed,'N'),'Y',pbip.CLM_PROMISE_PERIOD,null)
,Decode(Nvl(p_is_fed,'N'),'Y',pbip.CLM_PROMISE_PERIOD_UOM,null)
,Decode(Nvl(p_is_fed,'N'),'Y',pbip.CLM_PROMISE_POP_DURATION,null)
,Decode(Nvl(p_is_fed,'N'),'Y',pbip.CLM_PROMISE_POP_DURATION_UOM,null)
FROM
pon_bid_item_prices pbip,
mtl_units_of_measure mtluom,
pon_bid_headers pbh,
fnd_currencies fc
WHERE
pbh.bid_number = p_bid_number AND
pbh.auction_header_id = p_auction_header_id AND
pbip.bid_number = pbh.bid_number and
pbip.auction_line_number = -1 AND
nvl(pbip.award_status, 'NO') = 'AWARDED' and
pbip.uom = mtluom.uom_code (+) and
fc.currency_code = pbh.bid_currency_code;
END insert_unsol_lines_spo;
PROCEDURE INSERT_UNSOL_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) IS
l_cursorName NUMBER;
SELECT pbip.line_number,
pli.interface_line_id,
pbip.item_description,
--nvl(paip.ip_category_id, -2) ip_category_id,
-2 ip_category_id, --ip_category_id
-2 item_id,--nvl(paip.item_id, -2) item_id,
pah.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_headers_all pah,
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
pbip.auction_line_number = -1 AND
nvl(pbip.award_status, 'NO') = 'AWARDED' and
pah.auction_header_id = pbip.auction_header_id 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_UNSOL_IP_DESCRIPTORS;
PROCEDURE insert_unsol_lines_bpa(p_interface_header_id IN NUMBER,
p_auction_header_id IN NUMBER,
p_bid_number IN NUMBER,
p_document_number IN VARCHAR2,
p_is_fed IN VARCHAR2,
p_user_id IN NUMBER,
x_rows_processed IN OUT NOCOPY NUMBER) IS
BEGIN
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,
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,
from_header_id,
from_line_id
)
SELECT
p_interface_header_id, -- interface_header_id
po_lines_interface_s.nextval, -- interface_line_id
NULL, -- requisition_line_id
pbip.line_type_id, -- line_type_id
x_rows_processed + rownum, -- line num
null, -- item_id
null, -- item_revision
pbip.category_id, -- category_id
null, -- ip category id
substrb(pbip.item_description, 1, 240),
-- item_description
decode(pbip.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(pbip.order_type_lookup_code,
'AMOUNT', Decode(Nvl(p_is_fed,'N'),'Y',pbip.bid_currency_unit_price,NULL),
'RATE', NULL,
'FIXED PRICE', NULL,
pbip.award_quantity), -- quantity
decode(pbip.order_type_lookup_code,
'AMOUNT', pbip.bid_currency_unit_price,null), -- committed_amount
decode(pbip.order_type_lookup_code,
'AMOUNT', 1,
'FIXED PRICE', null, pbip.bid_currency_unit_price), --unit_price
decode(pbip.order_type_lookup_code,
'AMOUNT', 1,
'FIXED PRICE', null,
pbip.po_bid_min_rel_amount), -- min_release_amount
pbip.ship_to_location_id, -- ship_to_location_id
null, -- need_by_date
Decode(Nvl(p_is_fed,'N'),'Y',pbip.promise_pop_start_date,null), -- period_of_performance_start_date
Decode(Nvl(p_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
p_auction_header_id, -- auction_header_id
p_document_number, -- document_number
pbip.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(pbip.order_type_lookup_code,
'FIXED PRICE', round(pbip.bid_currency_unit_price, fc.precision),
null) -- amount
-- Clin Slin Changes
, pbip.line_num_display
, pbip.group_line_id
, pbip.clm_info_flag
, pbip.clm_option_indicator
, pbip.clm_option_num
, pbip.clm_option_from_date
, pbip.clm_option_to_date
, pbip.clm_funded_flag
, pbip.clm_base_line_num
-- Complex Pricing Changes
, pbip.CLM_CONTRACT_TYPE
, pbip.CLM_COST_CONSTRAINT
, pbip.CLM_IDC_TYPE
--CLM Order Off IDV Project
, pbh.idv_header_id
, pbip.idv_line_id
FROM pon_bid_headers pbh,
pon_bid_item_prices pbip,
mtl_units_of_measure mtluom,
fnd_currencies fc
WHERE
pbh.bid_number = p_bid_number AND
pbh.auction_header_id = p_auction_header_id AND
pbip.bid_number = pbh.bid_number AND
pbip.auction_line_number = -1 AND
nvl(pbip.award_status, 'NO') = 'AWARDED' and
pbip.uom = mtluom.uom_code (+) and
fc.currency_code = pbh.bid_currency_code;
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 = p_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 )
where PLI1.group_line_id is not null
and PLI1.interface_header_id = p_interface_header_id
and PLI1.auction_header_id = p_auction_header_id;
INSERT_UNSOL_IP_DESCRIPTORS(p_auction_header_id,
p_bid_number,
p_interface_header_id,
p_user_id,
fnd_global.login_id);
END insert_unsol_lines_bpa;