[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT multi_org_flag
INTO v_multi_org
FROM fnd_product_groups;
SELECT doctype_id, transaction_type
INTO v_doctype_id, v_transaction_type
FROM pon_auc_doctypes
WHERE internal_name = P_DOCUMENT_TYPE;
SELECT
LINE_ATTRIBUTE_ENABLED_FLAG, LINE_MAS_ENABLED_FLAG, PRICE_ELEMENT_ENABLED_FLAG,
RFI_LINE_ENABLED_FLAG, LOT_ENABLED_FLAG, GROUP_ENABLED_FLAG, LARGE_NEG_ENABLED_FLAG,
HDR_ATTRIBUTE_ENABLED_FLAG, NEG_TEAM_ENABLED_FLAG, PROXY_BIDDING_ENABLED_FLAG,
POWER_BIDDING_ENABLED_FLAG, AUTO_EXTEND_ENABLED_FLAG, TEAM_SCORING_ENABLED_FLAG , QTY_PRICE_TIERS_ENABLED_FLAG
INTO
l_line_attribute_enabled_flag, l_line_mas_enabled_flag, l_price_element_enabled_flag,
l_rfi_line_enabled_flag, l_lot_enabled_flag, l_group_enabled_flag, l_large_neg_enabled_flag,
l_hdr_attribute_enabled_flag, l_neg_team_enabled_flag, l_proxy_bidding_enabled_flag,
l_power_bidding_enabled_flag, l_auto_extend_enabled_flag, l_team_scoring_enabled_flag, l_qty_price_tier_enabled_flag
FROM
PON_NEGOTIATION_STYLES WHERE STYLE_ID = P_NEG_STYLE_ID;
v_debug_status := 'INSERT-PAH';
INSERT INTO PON_AUCTION_HEADERS_ALL (
AUCTION_HEADER_ID,
DOCUMENT_NUMBER,
AUCTION_HEADER_ID_ORIG_AMEND,
AUCTION_HEADER_ID_ORIG_ROUND,
AMENDMENT_NUMBER,
AUCTION_TITLE,
AUCTION_STATUS,
AWARD_STATUS,
AUCTION_TYPE,
CONTRACT_TYPE,
TRADING_PARTNER_NAME,
TRADING_PARTNER_NAME_UPPER,
TRADING_PARTNER_ID,
LANGUAGE_CODE,
BID_VISIBILITY_CODE,
ATTACHMENT_FLAG,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
AUCTION_ORIGINATION_CODE,
DOCTYPE_ID,
ORG_ID,
BUYER_ID,
MANUAL_EDIT_FLAG,
SHARE_AWARD_DECISION,
APPROVAL_STATUS,
GLOBAL_AGREEMENT_FLAG,
ATTRIBUTE_LINE_NUMBER,
HAS_HDR_ATTR_FLAG,
HAS_ITEMS_FLAG,
STYLE_ID,
PO_STYLE_ID,
PRICE_BREAK_RESPONSE,
NUMBER_OF_LINES,
ADVANCE_NEGOTIABLE_FLAG,
RECOUPMENT_NEGOTIABLE_FLAG,
PROGRESS_PYMT_NEGOTIABLE_FLAG,
RETAINAGE_NEGOTIABLE_FLAG,
MAX_RETAINAGE_NEGOTIABLE_FLAG,
SUPPLIER_ENTERABLE_PYMT_FLAG,
PROGRESS_PAYMENT_TYPE,
LINE_ATTRIBUTE_ENABLED_FLAG,
LINE_MAS_ENABLED_FLAG,
PRICE_ELEMENT_ENABLED_FLAG,
RFI_LINE_ENABLED_FLAG,
LOT_ENABLED_FLAG,
GROUP_ENABLED_FLAG,
LARGE_NEG_ENABLED_FLAG,
HDR_ATTRIBUTE_ENABLED_FLAG,
NEG_TEAM_ENABLED_FLAG,
PROXY_BIDDING_ENABLED_FLAG,
POWER_BIDDING_ENABLED_FLAG,
AUTO_EXTEND_ENABLED_FLAG,
TEAM_SCORING_ENABLED_FLAG,
PRICE_TIERS_INDICATOR,
QTY_PRICE_TIERS_ENABLED_FLAG
) VALUES (
pon_auction_headers_all_s.nextval, -- AUCTION_HEADER_ID
pon_auction_headers_all_s.currval, -- DOCUMENT_NUMBER
pon_auction_headers_all_s.currval, -- AUCTION_HEADER_ID_ORIG_AMEND,
pon_auction_headers_all_s.currval, -- AUCTION_HEADER_ID_ORIG_ROUND,
0, -- AMENDMENT_NUMBER
P_DOCUMENT_TITLE, -- AUCTION_TITLE
'DRAFT', -- AUCTION_STATUS
'NO', -- AWARD_STATUS
v_transaction_type, -- AUCTION_TYPE
P_CONTRACT_TYPE, -- CONTRACT_TYPE
v_site_name, -- TRADING_PARTNER_NAME
upper(v_site_name), -- TRADING_PARTNER_NAME_UPPER
v_site_id, -- TRADING_PARTNER_ID
userenv('LANG'), -- LANGUAGE_CODE
'OPEN_BIDDING', -- BID_VISIBILITY_CODE
'N', -- ATTACHMENT_FLAG
sysdate, -- CREATION_DATE
P_BUYER_ID, -- CREATED_BY
sysdate, -- LAST_UPDATE_DATE
P_BUYER_ID, -- LAST_UPDATED_BY
P_ORIGINATION_CODE, -- AUCTION_ORIGINATION_CODE
v_doctype_id, -- DOCTYPE_ID
P_ORG_ID, -- ORG_ID
P_BUYER_ID, -- BUYER_ID
'N', -- MANUAL_EDIT_FLAG
'N', -- SHARE_AWARD_DECISION
'NOT_REQUIRED', -- APPROVAL_STATUS
'N', -- GLOBAL_AGREEMENT_FLAG
-1, -- ATTRIBUTE_LINE_NUMBER
'N', -- HAS_HDR_ATTR_FLAG
'Y', -- HAS_ITEMS_FLAG
P_NEG_STYLE_ID, -- STYLE_ID
P_PO_STYLE_ID, -- PO_STYLE_ID
l_price_break_response, -- PRICE_BREAK_RESPONSE,
0, -- NUMBER_OF_LINES
'N', --ADVANCE_NEGOTIABLE_FLAG
'N', --RECOUPMENT_NEGOTIABLE_FLAG
'N', --PROGRESS_PYMT_NEGOTIABLE_FLAG
'N', --RETAINAGE_NEGOTIABLE_FLAG
'N', --MAX_RETAINAGE_NEGOTIABLE_FLAG
'N', --SUPPLIER_ENTERABLE_PYMT_FLAG
g_progress_payment_type, --PROGRESS_PAYMENT_TYPE
l_line_attribute_enabled_flag,
l_line_mas_enabled_flag,
l_price_element_enabled_flag,
l_rfi_line_enabled_flag,
l_lot_enabled_flag,
l_group_enabled_flag,
l_large_neg_enabled_flag,
l_hdr_attribute_enabled_flag,
l_neg_team_enabled_flag,
l_proxy_bidding_enabled_flag,
l_power_bidding_enabled_flag,
l_auto_extend_enabled_flag,
l_team_scoring_enabled_flag,
v_price_tiers_indicator,
l_qty_price_tier_enabled_flag
)
RETURNING auction_header_id INTO P_DOCUMENT_NUMBER;
SELECT order_type_lookup_code
INTO v_order_type_lookup_code
FROM po_line_types_b
WHERE P_LINE_TYPE_ID = line_type_id;
SELECT purchase_basis
INTO v_purchase_basis
FROM po_line_types_b
WHERE P_LINE_TYPE_ID = line_type_id;
SELECT multi_org_flag
INTO v_multi_org
FROM fnd_product_groups;
SELECT org_id
INTO v_org_id
FROM pon_auction_headers_all
WHERE auction_header_id = P_DOCUMENT_NUMBER
AND nvl(org_id, -9999) = nvl(P_ORG_ID, -9999);
SELECT category_id
INTO v_att_category_id
FROM fnd_document_categories
WHERE upper(name) = 'VENDOR';
SELECT count(*)
INTO v_header_attach_count
FROM fnd_attached_documents ad, fnd_documents doc
WHERE ad.entity_name = 'REQ_HEADERS'
AND ad.pk1_value = to_char(p_requisition_header_id)
AND ad.document_id = doc.document_id
AND doc.category_id = v_att_category_id;
SELECT count(*)
INTO v_item_attach_count
FROM fnd_attached_documents ad, fnd_documents doc
WHERE ad.entity_name = 'REQ_LINES'
AND ad.pk1_value = to_char(p_requisition_line_id)
AND ad.document_id = doc.document_id
AND doc.category_id = v_att_category_id;
UPDATE pon_auction_headers_all
SET global_agreement_flag = 'Y'
WHERE auction_header_id = p_document_number;
SELECT blanket_po_header_id, blanket_po_line_num
INTO v_blanket_po_header_id, v_blanket_po_line_num
FROM po_requisition_lines_all
WHERE requisition_header_id = p_requisition_header_id and
requisition_line_id = p_requisition_line_id;
SELECT ip_category_id
INTO v_ip_category_id
FROM po_lines_all
WHERE po_header_id = v_blanket_po_header_id and
line_num = v_blanket_po_line_num;
SELECT max(line_number)
INTO P_LINE_NUMBER
FROM pon_auction_item_prices_all
WHERE auction_header_id = P_DOCUMENT_NUMBER
AND line_type_id = P_LINE_TYPE_ID
AND nvl(p_item_id, -1) = nvl(item_id, -1)
AND nvl(p_item_revision, -1) = nvl(item_revision, -1)
AND nvl(p_item_description, 'NULL') = nvl(item_description, 'NULL')
AND p_category_id = category_id
AND p_ship_to_location_id = ship_to_location_id
-- Ignore UOM code for amount based lines
AND decode(v_order_type_lookup_code, 'AMOUNT', '1', p_uom_code) = decode(v_order_type_lookup_code, 'AMOUNT', '1', uom_code);
SELECT max(paip.line_number)
INTO P_LINE_NUMBER
FROM pon_auction_item_prices_all paip,
pon_backing_requisitions pbr,
po_requisition_lines_all prl
WHERE paip.auction_header_id = P_DOCUMENT_NUMBER
AND paip.line_type_id = P_LINE_TYPE_ID
AND nvl(p_item_id, -1) = nvl(paip.item_id, -1)
AND nvl(p_item_revision, -1) = nvl(paip.item_revision, -1)
AND nvl(p_item_description, 'NULL') = nvl(paip.item_description, 'NULL')
AND p_category_id = paip.category_id
AND nvl(v_ip_category_id, -1) = nvl(paip.ip_category_id, -1)
-- Ignore UOM code for amount based lines
AND decode(v_order_type_lookup_code, 'AMOUNT', '1', p_uom_code) = decode(v_order_type_lookup_code, 'AMOUNT', '1', paip.uom_code)
AND paip.auction_header_id = pbr.auction_header_id
AND paip.line_number = pbr.line_number
AND pbr.requisition_header_id = prl.requisition_header_id
AND pbr.requisition_line_id = prl.requisition_line_id
AND nvl(prl.blanket_po_header_id, -1) = nvl(v_blanket_po_header_id, -1)
AND nvl(prl.blanket_po_line_num, -1) = nvl(v_blanket_po_line_num, -1);
v_debug_status := 'UPDATE_PAIP_1';
UPDATE pon_auction_item_prices_all
SET requisition_number = 'MULTIPLE',
-- problem: least() and greater() return NULL if any argument is NULL
-- need_by_start := NULL if P_NEED_BY_DATE and need_by_start == NULL
need_by_start_date = decode(P_CONTRACT_TYPE, 'BLANKET', to_date(NULL), least(nvl(need_by_start_date,P_NEED_BY_DATE), nvl(P_NEED_BY_DATE,need_by_start_date))),
-- if P_NEED_BY_DATE is NULL, keep existing need_by_date
need_by_date = decode(P_CONTRACT_TYPE, 'BLANKET', to_date(NULL), greatest(nvl(need_by_date,P_NEED_BY_DATE), nvl(P_NEED_BY_DATE,need_by_date))),
attachment_flag = decode(attachment_flag, 'Y', 'Y', v_has_attachments),
-- if P_PRICE is NULL, keep existing current_price
current_price = nvl(current_price,0) + P_QUANTITY
WHERE auction_header_id = P_DOCUMENT_NUMBER
AND line_number = P_LINE_NUMBER;
v_debug_status := 'UPDATE_PAIP_2';
UPDATE pon_auction_item_prices_all
SET quantity = quantity + P_QUANTITY,
residual_quantity = residual_quantity + P_QUANTITY,
requisition_number = 'MULTIPLE',
-- problem: least() and greater() return NULL if any argument is NULL
-- set need_by_start to NULL if P_NEED_BY_DATE and need_by_start == NULL
need_by_start_date = decode(P_CONTRACT_TYPE, 'BLANKET', to_date(NULL), least(nvl(need_by_start_date,P_NEED_BY_DATE), nvl(P_NEED_BY_DATE,need_by_start_date))),
-- if P_NEED_BY_DATE is NULL, keep existing need_by_date
need_by_date = decode(P_CONTRACT_TYPE, 'BLANKET', to_date(NULL), greatest(nvl(need_by_date,P_NEED_BY_DATE), nvl(P_NEED_BY_DATE,need_by_date))),
attachment_flag = decode(attachment_flag, 'Y', 'Y', v_has_attachments),
-- if P_PRICE is NULL, keep existing current_price
current_price = decode(P_PRICE, 0, to_number(NULL), decode(current_price, NULL, NULL, least(current_price, P_PRICE)))
WHERE auction_header_id = P_DOCUMENT_NUMBER
AND line_number = P_LINE_NUMBER;
v_debug_status := 'UOM_SELECT';
SELECT preference_value
INTO v_uom_code
FROM pon_party_preferences
WHERE preference_name = 'AMOUNT_BASED_UOM'
AND app_short_name = 'PON'
AND party_id = v_site_id;
v_debug_status := 'UOM_SELECT_EACH';
SELECT uom_code
INTO v_uom_code
FROM mtl_units_of_measure
WHERE unit_of_measure = 'Each';
SELECT FND_FLEX_EXT.get_segs('INV', 'MCAT', STRUCTURE_ID, CATEGORY_ID)
INTO v_category_name
FROM mtl_categories_kfv
WHERE category_id = P_CATEGORY_ID;
SELECT nvl(max(line_number),0)+1
INTO P_LINE_NUMBER
FROM pon_auction_item_prices_all
WHERE auction_header_id = P_DOCUMENT_NUMBER;
v_debug_status := 'INSERT_PAIP';
INSERT INTO PON_AUCTION_ITEM_PRICES_ALL (
AUCTION_HEADER_ID,
LINE_NUMBER,
DISP_LINE_NUMBER,
LAST_AMENDMENT_UPDATE,
MODIFIED_DATE,
ITEM_DESCRIPTION,
CATEGORY_ID,
CATEGORY_NAME,
IP_CATEGORY_ID,
UOM_CODE,
QUANTITY,
RESIDUAL_QUANTITY,
NEED_BY_START_DATE,
NEED_BY_DATE,
SHIP_TO_LOCATION_ID,
NUMBER_OF_BIDS,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CURRENT_PRICE,
NOTE_TO_BIDDERS,
ATTACHMENT_FLAG,
HAS_ATTRIBUTES_FLAG,
ORG_ID,
LINE_TYPE_ID,
ORDER_TYPE_LOOKUP_CODE,
PURCHASE_BASIS,
ITEM_ID,
ITEM_NUMBER,
ITEM_REVISION,
LINE_ORIGINATION_CODE,
REQUISITION_NUMBER,
PRICE_BREAK_TYPE,
PRICE_BREAK_NEG_FLAG,
HAS_SHIPMENTS_FLAG,
HAS_QUANTITY_TIERS,
PRICE_DISABLED_FLAG,
quantity_disabled_flag,
JOB_ID,
ADDITIONAL_JOB_DETAILS,
PO_AGREED_AMOUNT,
HAS_PRICE_DIFFERENTIALS_FLAG,
PRICE_DIFF_SHIPMENT_NUMBER,
DIFFERENTIAL_RESPONSE_TYPE,
GROUP_TYPE,
DOCUMENT_DISP_LINE_NUMBER,
SUB_LINE_SEQUENCE_NUMBER,
HAS_PAYMENTS_FLAG,
PROGRESS_PYMT_RATE_PERCENT
) VALUES (
P_DOCUMENT_NUMBER, -- AUCTION_HEADER_ID
P_LINE_NUMBER,
P_LINE_NUMBER, -- DISP_LINE_NUMBER
0, -- LAST_AMENDMENT_UPDATE
sysdate, -- MODIFIED_DATE
P_ITEM_DESCRIPTION,
P_CATEGORY_ID,
v_category_name, -- CATEGORY_NAME
v_ip_category_id, -- IP_CATEGORY_ID
v_uom_code, -- UOM_CODE
v_quantity, -- QUANTITY
v_quantity, -- RESIDUAL_QUANTITY,
decode(P_CONTRACT_TYPE, 'BLANKET', to_date(NULL), P_NEED_BY_DATE), -- NEED_BY_START_DATE
decode(P_CONTRACT_TYPE, 'BLANKET', to_date(NULL), P_NEED_BY_DATE), -- NEED_BY_DATE
decode(P_CONTRACT_TYPE, 'BLANKET', NULL, p_ship_to_location_id), -- SHIP_TO_LOCATION_ID
0, -- NUMBER_OF_BIDS
sysdate, -- CREATION_DATE
P_BUYER_ID, -- CREATED_BY
sysdate, -- LAST_UPDATE_DATE
P_BUYER_ID, -- LAST_UPDATED_BY
v_price, -- CURRENT_PRICE
P_NOTE_TO_VENDOR, -- NOTE_TO_BIDDERS
v_has_attachments, -- ATTACHMENT_FLAG
'N', -- HAS_ATTRIBUTE_FLAG
P_ORG_ID, -- ORG_ID
P_LINE_TYPE_ID,
v_order_type_lookup_code, -- ORDER_TYPE_LOOKUP_CODE
v_purchase_basis, -- Purchase Basis
P_ITEM_ID,
P_ITEM_NUMBER,
P_ITEM_REVISION,
P_ORIGINATION_CODE, -- LINE_ORIGINATION_CODE
P_REQUISITION_NUMBER,
decode(v_order_type_lookup_code,'AMOUNT', 'NONE', 'FIXED PRICE', 'NONE', g_price_break_type), -- PRICE_BREAK_TYPE
g_price_break_neg_flag, -- PRICE_BREAK_NEG_FLAG
'N', -- HAS_SHIPMENTS_FLAG
'N', -- HAS_QUANTITY_TIERS
'N', -- PRICE_DISABLED_FLAG
'N', -- QUANTITY_DISABLED_FLAG
P_JOB_ID, -- JOB ID - ADDED FOR SERVICES PROCUREMENT PROJECT
P_JOB_DETAILS, -- ADDITIONAL JOB DETAILS -ADDED FOR SERVICES PROCUREMENT PROJECT
P_PO_AGREED_AMOUNT, -- PO AGREED AMOUNT -ADDED FOR SERVICES PROCUREMENT PROJECT
p_has_price_diff_flag, -- LINE HAS PRICE DIFFERENTIALS ADDED FOR SERVICES PROCUREMENT PROJECT-
-1, --price diff shipment number is -1 by default
Decode(p_has_price_diff_flag,'Y','OPTIONAL', NULL),
'LINE', -- GROUP_TYPE
P_LINE_NUMBER, -- DOCUMENT_DISP_LINE_NUMBER
P_LINE_NUMBER, -- SUB_LINE_SEQUENCE_NUMBER
'N', --has_payments_flag
decode(g_progress_payment_type, 'FINANCE', 100,null) --PROGRESS_PYMT_RATE_PERCENT
);
UPDATE PON_AUCTION_HEADERS_ALL
SET
NUMBER_OF_LINES = P_LINE_NUMBER,
LAST_LINE_NUMBER = P_LINE_NUMBER
WHERE
AUCTION_HEADER_ID = P_DOCUMENT_NUMBER;
v_debug_status := 'INSERT_PBR_YUMMY';
INSERT INTO PON_BACKING_REQUISITIONS (
AUCTION_HEADER_ID,
LINE_NUMBER,
REQUISITION_HEADER_ID,
REQUISITION_LINE_ID,
REQUISITION_QUANTITY,
REQUISITION_NUMBER
) VALUES (
P_DOCUMENT_NUMBER,
P_LINE_NUMBER,
P_REQUISITION_HEADER_ID,
P_REQUISITION_LINE_ID,
P_QUANTITY,
P_REQUISITION_NUMBER
);
v_debug_status := 'INSERT_HEADER_ATTACHMENT';
p_buyer_id, -- LAST_UPDATE_LOGIN
NULL, -- program_application_id
NULL, -- program_id
NULL, -- request_id
NULL, -- automatically_added_flag
33, -- from_category_id (Vendor)
33); -- to_category_id (Vendor)
v_debug_status := 'INSERT_LINE_ATTACHMENT';
p_buyer_id, -- LAST_UPDATE_LOGIN
NULL, -- program_application_id
NULL, -- program_id
NULL, -- request_id
NULL, -- automatically_added_flag
33, -- from_category_id (Vendor)
33); -- to_category_id (Vendor)
SELECT distinct interface_line_number
FROM pon_attributes_interface
WHERE interface_auction_header_id = p_document_number;
SELECT distinct paip.line_number, paip.ip_category_id
FROM pon_auction_item_prices_all paip,
pon_backing_requisitions pbr,
po_requisition_lines_all prl
WHERE paip.auction_header_id = p_document_number and
paip.auction_header_id = pbr.auction_header_id and
paip.line_number = pbr.line_number and
pbr.requisition_header_id = prl.requisition_header_id and
pbr.requisition_line_id = prl.requisition_line_id and
prl.blanket_po_header_id is null and
prl.blanket_po_line_num is null;
SELECT contract_type, created_by
INTO v_contract_type, v_buyer_id
FROM pon_auction_headers_all
WHERE auction_header_id = p_document_number;
select nvl(ppp.preference_value,'GENERAL'),
flv.meaning
into v_default_attr_group,
v_attr_group_name
from pon_party_preferences ppp,
fnd_lookup_values flv
where ppp.app_short_name = 'PON' and
ppp.preference_name = 'LINE_ATTR_DEFAULT_GROUP' and
ppp.party_id = (select trading_partner_id from pon_auction_headers_all where auction_header_id = p_document_number) and
flv.lookup_type = 'PON_LINE_ATTRIBUTE_GROUPS' and
nvl(ppp.preference_value,'GENERAL') = flv.lookup_code and
flv.view_application_id = 0 and
flv.security_group_id = 0 and
flv.language = userenv('LANG');
PO_NEGOTIATIONS4_GRP.insert_attributes(
p_api_version => 1.0,
p_commit => fnd_api.g_false,
p_init_msg_list => fnd_api.g_false,
p_validation_level => fnd_api.g_valid_level_full,
p_auction_header_id => p_document_number,
x_return_status => v_return_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data);
INSERT INTO PON_AUCTION_ATTRIBUTES (
AUCTION_HEADER_ID,
LINE_NUMBER,
ATTRIBUTE_NAME,
DESCRIPTION,
DATATYPE,
MANDATORY_FLAG,
VALUE,
DISPLAY_PROMPT,
HELP_TEXT,
DISPLAY_TARGET_FLAG,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
ATTRIBUTE_LIST_ID,
DISPLAY_ONLY_FLAG,
SEQUENCE_NUMBER,
COPIED_FROM_CAT_FLAG,
WEIGHT,
SCORING_TYPE,
ATTR_LEVEL,
ATTR_GROUP,
SECTION_NAME,
ATTR_MAX_SCORE,
INTERNAL_ATTR_FLAG,
ATTR_GROUP_SEQ_NUMBER,
ATTR_DISP_SEQ_NUMBER,
MODIFIED_FLAG,
MODIFIED_DATE,
LAST_AMENDMENT_UPDATE,
IP_CATEGORY_ID,
IP_DESCRIPTOR_ID
)
SELECT
P_DOCUMENT_NUMBER, -- AUCTION_HEADER_ID
INTERFACE_LINE_NUMBER, -- LINE_NUMBER
ATTRIBUTE_NAME, -- ATTRIBUTE_NAME
null, -- DESCRIPTION
DATATYPE, -- DATATYPE
'N', -- MANDATORY_FLAG
VALUE, -- VALUE
null, -- DISPLAY_PROMPT
null, -- HELP_TEXT
'N', -- DISPLAY_TARGET_FLAG
SYSDATE, -- CREATION_DATE
v_buyer_id, -- CREATED_BY
SYSDATE, -- LAST_UPDATE_DATE
v_buyer_id, -- LAST_UPDATED_BY
-1, -- ATTRIBUTE_LIST_ID
'N', -- DISPLAY_ONLY_FLAG
(ROWNUM*10), -- SEQUENCE_NUMBER
null, -- COPIED_FROM_CAT_FLAG
null, -- WEIGHT
null, -- SCORING_TYPE
'LINE', -- ATTR_LEVEL
v_default_attr_group, -- ATTR_GROUP
v_attr_group_name, -- SECTION_NAME
null, -- ATTR_MAX_SCORE
'N', -- INTERNAL_ATTR_FLAG
10, -- ATTR_GROUP_SEQ_NUMBER
(ROWNUM*10), -- ATTR_DISP_SEQ_NUMBER
null, -- MODIFIED_FLAG
null, -- MODIFIED_DATE
null, -- LAST_AMENDMENT_UPDATE
IP_CATEGORY_ID, -- IP_CATEGORY_ID
IP_DESCRIPTOR_ID -- IP_DESCRIPTOR_ID
FROM
(SELECT interface_line_number, attribute_name, datatype,
value, ip_category_id, ip_descriptor_id
FROM pon_attributes_interface
WHERE interface_auction_header_id = P_DOCUMENT_NUMBER AND
interface_line_number = v_line_number AND
((ip_category_id = 0 and v_ip_attr_default_option in ('ALL', 'BASE')) or
(ip_category_id <> 0 and v_ip_attr_default_option in ('ALL', 'CATEGORY')))
ORDER BY nvl(interface_sequence_number, v_max_seq_number) asc);
DELETE FROM PON_ATTRIBUTES_INTERFACE
WHERE interface_auction_header_id = P_DOCUMENT_NUMBER;
INSERT INTO PON_AUCTION_ATTRIBUTES (
AUCTION_HEADER_ID,
LINE_NUMBER,
ATTRIBUTE_NAME,
DESCRIPTION,
DATATYPE,
MANDATORY_FLAG,
VALUE,
DISPLAY_PROMPT,
HELP_TEXT,
DISPLAY_TARGET_FLAG,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
ATTRIBUTE_LIST_ID,
DISPLAY_ONLY_FLAG,
SEQUENCE_NUMBER,
COPIED_FROM_CAT_FLAG,
WEIGHT,
SCORING_TYPE,
ATTR_LEVEL,
ATTR_GROUP,
SECTION_NAME,
ATTR_MAX_SCORE,
INTERNAL_ATTR_FLAG,
ATTR_GROUP_SEQ_NUMBER,
ATTR_DISP_SEQ_NUMBER,
MODIFIED_FLAG,
MODIFIED_DATE,
LAST_AMENDMENT_UPDATE,
IP_CATEGORY_ID,
IP_DESCRIPTOR_ID
)
SELECT
P_DOCUMENT_NUMBER, -- AUCTION_HEADER_ID
v_line_number, -- LINE_NUMBER
DESCRIPTOR_NAME, -- ATTRIBUTE_NAME
null, -- DESCRIPTION
DATATYPE, -- DATATYPE
'N', -- MANDATORY_FLAG
null, -- VALUE
null, -- DISPLAY_PROMPT
null, -- HELP_TEXT
'N', -- DISPLAY_TARGET_FLAG
SYSDATE, -- CREATION_DATE
v_buyer_id, -- CREATED_BY
SYSDATE, -- LAST_UPDATE_DATE
v_buyer_id, -- LAST_UPDATED_BY
-1, -- ATTRIBUTE_LIST_ID
'N', -- DISPLAY_ONLY_FLAG
(ROWNUM*10), -- SEQUENCE_NUMBER
null, -- COPIED_FROM_CAT_FLAG
null, -- WEIGHT
null, -- SCORING_TYPE
'LINE', -- ATTR_LEVEL
v_default_attr_group, -- ATTR_GROUP
v_attr_group_name, -- SECTION_NAME
null, -- ATTR_MAX_SCORE
'N', -- INTERNAL_ATTR_FLAG
10, -- ATTR_GROUP_SEQ_NUMBER
(ROWNUM*10), -- ATTR_DISP_SEQ_NUMBER
null, -- MODIFIED_FLAG
null, -- MODIFIED_DATE
null, -- LAST_AMENDMENT_UPDATE
IP_CATEGORY_ID, -- IP_CATEGORY_ID
IP_DESCRIPTOR_ID -- IP_DESCRIPTOR_ID
FROM
(SELECT attribute_name descriptor_name, decode(type, 1, 'NUM', 'TXT') datatype,
rt_category_id ip_category_id, attribute_id ip_descriptor_id
FROM icx_cat_agreement_attrs_v
WHERE ((rt_category_id = 0 and v_ip_attr_default_option in ('ALL', 'BASE')) or
(rt_category_id = v_ip_category_id and v_ip_attr_default_option in ('ALL', 'CATEGORY'))) and language = userenv('LANG')
ORDER BY nvl(sequence, v_max_seq_number) asc);
SELECT nvl(max(price_differential_number),0)+1
INTO p_price_differential_number
FROM pon_price_differentials
WHERE auction_header_id = p_document_number AND
line_number = p_line_number AND
shipment_number = p_shipment_number;
v_debug_status := 'INSERT_PRICE_DIFFERENTIALS';
INSERT INTO pon_price_differentials
(
auction_header_id,
line_number,
shipment_number,
price_differential_number,
price_type,
multiplier,
creation_date,
created_by,
last_update_date,
last_updated_by
)VALUES(
p_document_number, -- Auction Header Id
p_line_number, -- Line Number
p_shipment_number, -- Shipment Number
p_price_differential_number, -- Price Differentials Number
p_price_type, -- Price Type
p_multiplier, -- Multiplier
Sysdate, -- creation date
p_buyer_id, -- created by
Sysdate, -- last update date
p_buyer_id -- last updated by
);
SELECT u1.user_name, u2.user_name
INTO P_OWNER_NAME, v_buyer_name
FROM fnd_user u1, fnd_user u2, pon_auction_headers_all ah
WHERE ah.auction_header_id = P_DOCUMENT_NUMBER
AND ah.trading_partner_contact_id = u1.person_party_id(+)
AND ah.buyer_id = u2.user_id(+);
FPJ: As we migrated to OA, this API is also updated. Because we cannot
encrypt id at pl/sql, we return id as an out parameter. The caller
needs to encrypt the id, and append to the url.
======================================================================*/
PROCEDURE Get_PO_Negotiation_Link(
P_PO_HEADER_ID IN NUMBER,
P_DOCUMENT_ID OUT NOCOPY NUMBER,
P_DOCUMENT_NUMBER OUT NOCOPY VARCHAR2,
P_DOCUMENT_URL OUT NOCOPY VARCHAR2,
P_RESULT OUT NOCOPY NUMBER,
P_ERROR_CODE OUT NOCOPY VARCHAR2,
P_ERROR_MESSAGE OUT NOCOPY VARCHAR2) IS
v_debug_status VARCHAR2(60);
SELECT ah.auction_header_id, ah.document_number
INTO P_DOCUMENT_ID, P_DOCUMENT_NUMBER
FROM pon_bid_headers bh, pon_auction_headers_all ah
WHERE bh.po_header_id = P_PO_HEADER_ID
AND bh.auction_header_id = ah.auction_header_id;
negotiation and adds (bulk inserts ) them as invitees. We do not check
for inactive suppliers/ sites in the autocreate process; these
INSERT INTO PON_BIDDING_PARTIES
(
AUCTION_HEADER_ID,
List_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
SEQUENCE,
TRADING_PARTNER_NAME,
TRADING_PARTNER_ID,
TRADING_PARTNER_CONTACT_NAME,
TRADING_PARTNER_CONTACT_ID,
CREATION_DATE,
CREATED_BY,
NUMBER_PRICE_DECIMALS,
ROUND_NUMBER,
LAST_AMENDMENT_UPDATE,
VENDOR_SITE_ID,
VENDOR_SITE_CODE,
ACCESS_TYPE
)
SELECT
P_DOCUMENT_NUMBER, --AUCTION_HEADER_ID
-1, -- List_ID
sysdate, -- LAST_UPDATE_DATE
p_buyer_id, -- LAST_UPDATED_BY
rownum * 10, -- SEQUENCE
vendor_name, -- TRADING_PARTNER_NAME
party_id, -- TRADING_PARTNER_ID
null, -- TRADING_PARTNER_CONTACT_NAME
null, -- TRADING_PARTNER_CONTACT_ID
sysdate, -- CREATION_DATE
p_buyer_id, -- CREATED_BY
NUMBER_PRICE_DECIMALS, -- NUMBER_PRICE_DECIMALS
1, -- ROUND_NUMBER
0, -- LAST_AMENDMENT_UPDATE
vendor_site_id, -- VENDOR_SITE_ID
vendor_site_code, -- VENDOR_SITE_CODE
'FULL' -- ACCESS_TYPE
FROM
(SELECT DISTINCT
pv.vendor_name vendor_name,
pv.party_id party_id,
ponah.number_price_decimals number_price_decimals,
nvl(prl.vendor_site_id, -1) vendor_site_id,
nvl(ps.vendor_site_code, -1) vendor_site_code
FROM po_requisition_lines_all prl,
pon_backing_requisitions ponbr,
pon_auction_headers_all ponah,
po_vendors pv,
po_vendor_sites_all ps
WHERE ponah.auction_header_id = p_document_number
and ponbr.auction_header_id = ponah.auction_header_id
and ponbr.requisition_header_id = prl.requisition_header_id
and ponbr.requisition_line_id = prl.requisition_line_id
and prl.vendor_id is not null
and prl.vendor_id = pv.vendor_id
and nvl(pv.start_date_active, sysdate) <= sysdate
and nvl(pv.end_date_active, sysdate) >= sysdate
and ps.vendor_id(+) = prl.vendor_id
and ps.vendor_site_id(+) = prl.vendor_site_id )
ORDER BY vendor_name;
select style_id, style_name
into x_style_id, x_style_name
from pon_negotiation_styles_tl
where style_id = 1
and language = userenv('LANG');