[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 draft_locked,
draft_locked_by,
draft_locked_by_contact_id
INTO l_draft_locked,
l_draft_locked_by,
l_draft_locked_by_contact_id
FROM pon_auction_headers_all
WHERE auction_header_id = p_document_number;
SELECT user_parties.party_id user_party_id,
company_parties.party_id company_party_id
INTO i_draft_locked_by,
i_draft_locked_by_contact_id
FROM fnd_user,
hz_parties user_parties,
hz_parties company_parties,
hz_relationships,
hz_code_assignments
WHERE fnd_user.person_party_id = user_parties.party_id
AND fnd_user.user_id = p_buyer_id
AND hz_relationships.object_id = company_parties.party_id
AND hz_relationships.subject_id = user_parties.party_id
AND hz_relationships.relationship_type = 'POS_EMPLOYMENT'
AND hz_relationships.relationship_code = 'EMPLOYEE_OF'
AND hz_relationships.start_date <= SYSDATE
AND hz_relationships.end_date >= SYSDATE
AND hz_code_assignments.owner_table_id = company_parties.party_id
AND hz_code_assignments.owner_table_name = 'HZ_PARTIES'
AND hz_code_assignments.class_category = 'POS_PARTICIPANT_TYPE'
AND hz_code_assignments.class_code = 'ENTERPRISE';
UPDATE PON_AUCTION_HEADERS_ALL
SET DRAFT_LOCKED = 'N',
DRAFT_LOCKED_BY = NULL,
DRAFT_LOCKED_BY_CONTACT_ID = NULL,
DRAFT_LOCKED_DATE = NULL,
DRAFT_UNLOCKED_BY = i_draft_locked_by,
DRAFT_UNLOCKED_BY_CONTACT_ID = i_draft_locked_by_contact_id,
DRAFT_UNLOCKED_DATE = SYSDATE,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_buyer_id
WHERE AUCTION_HEADER_ID = p_document_number;
UPDATE pon_auction_headers_all
SET AUCTION_ORIGINATION_CODE = 'REQUISITION', BUYER_ID = p_buyer_id -- bug 13640015
WHERE AUCTION_HEADER_ID = p_document_number; -- bug 13640015
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,
-- Begin Supplier Management: Bug 14087712
SUPP_REG_QUAL_FLAG,
SUPP_EVAL_FLAG,
HIDE_TERMS_FLAG,
HIDE_ABSTRACT_FORMS_FLAG,
HIDE_ATTACHMENTS_FLAG,
INTERNAL_EVAL_FLAG,
HDR_SUPP_ATTR_ENABLED_FLAG,
INTGR_HDR_ATTR_FLAG,
INTGR_HDR_ATTACH_FLAG,
LINE_SUPP_ATTR_ENABLED_FLAG,
ITEM_SUPP_ATTR_ENABLED_FLAG,
INTGR_CAT_LINE_ATTR_FLAG,
INTGR_ITEM_LINE_ATTR_FLAG,
INTGR_CAT_LINE_ASL_FLAG
-- End Supplier Management: Bug 14087712
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,
-- Begin Supplier Management: Bug 14087712
l_supp_reg_qual_flag,
l_supp_eval_flag,
l_hide_terms_flag,
l_hide_abstract_forms_flag,
l_hide_attachments_flag,
l_internal_eval_flag,
l_hdr_supp_attr_enabled_flag,
l_intgr_hdr_attr_flag,
l_intgr_hdr_attach_flag,
l_line_supp_attr_enabled_flag,
l_item_supp_attr_enabled_flag,
l_intgr_cat_line_attr_flag,
l_intgr_item_line_attr_flag,
l_intgr_cat_line_asl_flag
-- End Supplier Management: Bug 14087712
FROM PON_NEGOTIATION_STYLES
WHERE STYLE_ID = P_NEG_STYLE_ID;
SELECT STANDARD_FORM,
DOCUMENT_FORMAT
INTO l_standard_form,
l_document_format
FROM PO_PRINT_FORM_FORMATS
WHERE FORM_SOURCE='PON'
AND DEFAULT_FLAG ='Y'
AND DOCUMENT_TYPE='PO_SOL_STD_FORM'
AND SYSDATE < NVL(INACTIVE_DATE,SYSDATE + 1);
'Inserting into pon_auction_headers_all');
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,
-- Begin Supplier Management: Bug 14087712
SUPP_REG_QUAL_FLAG,
SUPP_EVAL_FLAG,
HIDE_TERMS_FLAG,
HIDE_ABSTRACT_FORMS_FLAG,
HIDE_ATTACHMENTS_FLAG,
INTERNAL_EVAL_FLAG,
HDR_SUPP_ATTR_ENABLED_FLAG,
INTGR_HDR_ATTR_FLAG,
INTGR_HDR_ATTACH_FLAG,
LINE_SUPP_ATTR_ENABLED_FLAG,
ITEM_SUPP_ATTR_ENABLED_FLAG,
INTGR_CAT_LINE_ATTR_FLAG,
INTGR_ITEM_LINE_ATTR_FLAG,
INTGR_CAT_LINE_ASL_FLAG,
INTERNAL_ONLY_FLAG,
-- End Supplier Management: Bug 14087712
UDA_TEMPLATE_ID, -- uda_template_id
UDA_TEMPLATE_DATE,
SOLICITATION_TYPE,
REVISION,
STANDARD_FORM,
DOCUMENT_FORMAT,
FAIR_OPP_NOTICE_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,
-- Begin Supplier Management: Bug 14087712
l_supp_reg_qual_flag,
l_supp_eval_flag,
l_hide_terms_flag,
l_hide_abstract_forms_flag,
l_hide_attachments_flag,
l_internal_eval_flag,
l_hdr_supp_attr_enabled_flag,
l_intgr_hdr_attr_flag,
l_intgr_hdr_attach_flag,
l_line_supp_attr_enabled_flag,
l_item_supp_attr_enabled_flag,
l_intgr_cat_line_attr_flag,
l_intgr_item_line_attr_flag,
l_intgr_cat_line_asl_flag,
'N',
-- End Supplier Management: Bug 14087712
l_uda_template_id, -- uda_template_id
l_uda_template_date,
l_sol_type,
0,
l_standard_form,
l_document_format,
p_fair_opp_notice_flag
)
RETURNING auction_header_id
INTO P_DOCUMENT_NUMBER;
'Inserted in PAH : auction_header_id : '||P_DOCUMENT_NUMBER
)
;
/* Bug 9645160 - updated clm_document_number back in PON_AUCTION_HEADERS_ALL table. */
UPDATE pon_auction_headers_all
SET DOCUMENT_NUMBER = clm_doc_number
WHERE AUCTION_HEADER_ID = p_document_number;
SELECT progress_payment_type
INTO l_progress_payment_type
FROM pon_auction_headers_all
WHERE auction_header_id=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,
doctype_id, -- Added doctype_id
contract_type
INTO v_org_id,
v_doctype_id,
v_contract_type
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 'Y'
INTO l_line_exist
FROM pon_auction_item_prices_all
WHERE auction_header_id =p_document_number
AND line_number = p_line_number;
SELECT 'Y'
INTO l_line_valid
FROM pon_auction_item_prices_all
WHERE auction_header_id = P_DOCUMENT_NUMBER
AND line_number = P_LINE_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 NVL(p_category_id, -1) = NVL(category_id , -1)
AND NVL(p_ship_to_location_id, -1) = NVL(ship_to_location_id, -1)
-- Ignore UOM code for amount based lines
AND NVL(DECODE(v_order_type_lookup_code, 'AMOUNT', '1', p_uom_code),'NULL') =
NVL(DECODE(v_order_type_lookup_code, 'AMOUNT', '1', uom_code),'NULL')
--- adding info flag check form CLM : Clin Slin Changes
AND NVL(P_CLM_INFO_FLAG, 'N') = 'N'
AND NVL(clm_info_flag , 'N') = 'N'
--- It should match only lines which are NOT Slins and Options
AND NVL(clm_option_indicator, 'B') <> 'O'
AND group_line_id IS NULL
-- Bug 13701530
-- Added the below condition so that only Autocreated Requisition Lines can be grouped
AND line_origination_code = 'REQUISITION'
AND rownum < 2;
SELECT 'Y'
INTO l_line_valid
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_number = P_LINE_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 NVL(p_category_id, -1) = NVL(paip.category_id, -1)
AND NVL(v_ip_category_id, -1) = NVL(paip.ip_category_id, -1)
-- Ignore UOM code for amount based lines
AND NVL(DECODE(v_order_type_lookup_code, 'AMOUNT', '1', p_uom_code), 'NULL') =
NVL(DECODE(v_order_type_lookup_code, 'AMOUNT', '1', paip.uom_code), 'NULL')
--- adding info flag check form CLM : Clin Slin Changes
AND NVL(P_CLM_INFO_FLAG, 'N') = 'N'
AND NVL(paip.clm_info_flag , 'N') = 'N'
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)
--- It should match only lines which are NOT Slins and Options
AND NVL(paip.clm_option_indicator, 'B') <> 'O'
AND paip.group_line_id IS NULL
-- Bug 13701530
-- Added the below condition so that only Autocreated Requisition Lines can be grouped
AND paip.line_origination_code = 'REQUISITION'
AND rownum < 2;
logme(l_log_module, 'Grouping Line search select ');
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 NVL(p_category_id, -1) = NVL(category_id , -1)
AND NVL(p_ship_to_location_id, -1) = NVL(ship_to_location_id, -1)
-- Ignore UOM code for amount based lines
AND NVL(DECODE(v_order_type_lookup_code, 'AMOUNT', '1', p_uom_code),'NULL') =
NVL(DECODE(v_order_type_lookup_code, 'AMOUNT', '1', uom_code),'NULL')
--- adding info flag check form CLM : Clin Slin Changes
AND NVL(P_CLM_INFO_FLAG, 'N') = 'N'
AND NVL(clm_info_flag , 'N') = 'N'
--- It should match only lines which are NOT Slins and Options
AND NVL(clm_option_indicator, 'B') <> 'O'
AND group_line_id IS NULL
-- Bug 13701530
-- Added the below condition so that only Autocreated Requisition Lines can be grouped
AND line_origination_code = 'REQUISITION';
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 NVL(p_category_id, -1) = NVL(paip.category_id, -1)
AND NVL(v_ip_category_id, -1) = NVL(paip.ip_category_id, -1)
-- Ignore UOM code for amount based lines
AND NVL(DECODE(v_order_type_lookup_code, 'AMOUNT', '1', p_uom_code), 'NULL') =
NVL(DECODE(v_order_type_lookup_code, 'AMOUNT', '1', paip.uom_code), 'NULL')
--- adding info flag check form CLM : Clin Slin Changes
AND NVL(P_CLM_INFO_FLAG, 'N') = 'N'
AND NVL(paip.clm_info_flag , 'N') = 'N'
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)
--- It should match only lines which are NOT Slins and Options
AND NVL(paip.clm_option_indicator, 'B') <> 'O'
AND paip.group_line_id IS NULL
-- Bug 13701530
-- Added the below condition so that only Autocreated Requisition Lines can be grouped
AND paip.line_origination_code = 'REQUISITION';
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(V_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(V_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,
clm_cost_constraint = DECODE(clm_cost_constraint,
P_CLM_COST_CONSTRAINT, P_CLM_COST_CONSTRAINT, NULL),
clm_contract_type = DECODE(NVL(clm_contract_type, 'NULL'),
P_CLM_CONTRACT_TYPE, P_CLM_CONTRACT_TYPE, 'NULL', NULL, 'FP_FIRM')
WHERE auction_header_id = P_DOCUMENT_NUMBER
AND line_number = P_LINE_NUMBER;
UPDATE pon_auction_item_prices_all
SET requisition_number = 'MULTIPLE'
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(V_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(V_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,
-- added for 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,
CLM_CONTRACT_TYPE,
CLM_COST_CONSTRAINT,
CLM_IDC_TYPE,
UDA_TEMPLATE_ID
)
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(V_CONTRACT_TYPE, 'BLANKET', to_date(NULL), P_NEED_BY_DATE), -- NEED_BY_START_DATE
DECODE(V_CONTRACT_TYPE, 'BLANKET', to_date(NULL), P_NEED_BY_DATE), -- NEED_BY_DATE
DECODE(V_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
NVL(P_NEG_LINE_NUM_DISP, P_LINE_NUM_DISPLAY),
P_GROUP_LINE_ID,
P_CLM_INFO_FLAG,
P_CLM_OPTION_INDICATOR,
P_CLM_OPTION_NUM,
P_CLM_OPTION_FROM_DATE,
P_CLM_OPTION_TO_DATE,
P_CLM_FUNDED_FLAG,
P_CLM_BASE_LINE_NUM,
P_CLM_CONTRACT_TYPE,
DECODE(l_progress_payment_type,'ACTUAL','','FINANCE','',P_CLM_COST_CONSTRAINT), -- CLM QA Bug : 10096343
P_CLM_IDC_TYPE,
P_UDA_TEMPLATE_ID
);
SELECT need_by_date,
clm_period_perf_start_date,
clm_period_perf_end_date
INTO need_by_dt,
pop_start_dt,
pop_end_dt
FROM po_requisition_lines_all
WHERE requisition_header_id = p_requisition_header_id
AND requisition_line_id = p_requisition_line_id;
UPDATE pon_auction_item_prices_all
SET clm_need_by_date = DECODE(V_CONTRACT_TYPE, 'BLANKET', to_date(NULL), need_by_dt),
need_by_start_date = DECODE(V_CONTRACT_TYPE, 'BLANKET', to_date(NULL), pop_start_dt),
need_by_date = DECODE(V_CONTRACT_TYPE, 'BLANKET', to_date(NULL), pop_end_dt)
WHERE auction_header_id = P_DOCUMENT_NUMBER
AND line_number = P_LINE_NUMBER;
logme(l_log_module, 'in add neg lines just inserted');
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 ATTRIBUTE_GROUP_ID INTO l_comp_pricing_grp_id
FROM po_uda_ag_template_usages
WHERE template_id = P_REQ_LINE_TEMP_ID
AND ATTRIBUTE1 = P_CLM_IDC_TYPE
AND ATTRIBUTE2 = P_CLM_CONTRACT_TYPE;
UPDATE pon_auction_item_prices_all
SET clm_amount = clm_amount
+ PO_UDA_PUB.get_single_attr_value(
p_entity_code => 'PO_REQ_LINE_EXT_ATTRS',
pk1_value => P_REQUISITION_LINE_ID,
p_attr_grp_id => l_comp_pricing_grp_id,
p_attr_int_name => 'TOTAL_AMOUNT') -- bug 13571062
WHERE auction_header_id = P_DOCUMENT_NUMBER
AND line_number = P_LINE_NUMBER;
delete pon_auction_item_prices_ext_b
where auction_header_id = p_document_number
and line_number=p_line_number
and attr_group_id in
(select attribute_group_id from po_uda_ag_template_usages
where template_id=p_uda_template_id
and nvl(attribute1,'X')=nvl(p_clm_idc_type,'X')
and nvl(attribute2,'X')=nvl(p_clm_contract_type,'X'));
'Delete complex pricing UDA ');
SELECT ATTRIBUTE_GROUP_ID INTO l_comp_pricing_grp_id
FROM po_uda_ag_template_usages
WHERE template_id = P_UDA_TEMPLATE_ID
AND ATTRIBUTE1 = P_CLM_IDC_TYPE
AND ATTRIBUTE2 = P_CLM_CONTRACT_TYPE;
UPDATE pon_auction_item_prices_all
SET clm_amount =
PO_UDA_PUB.get_single_attr_value(
p_entity_code => 'PON_AUC_PRICES_EXT_ATTRS',
pk1_value => P_DOCUMENT_NUMBER,
pk2_value => P_LINE_NUMBER,
p_attr_grp_id => l_comp_pricing_grp_id,
p_attr_int_name => 'TOTAL_AMOUNT') -- bug 13571062
WHERE auction_header_id = P_DOCUMENT_NUMBER
AND line_number = P_LINE_NUMBER;
UPDATE pon_auction_item_prices_all
SET clm_unit_price = current_price,
clm_amount = (NVL(quantity,0)*NVL(current_price,0))
WHERE auction_header_id = P_DOCUMENT_NUMBER
AND line_number = P_LINE_NUMBER;
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
SELECT NVL(fair_opp_notice_flag, 'N'),
umbrella_program_id
INTO l_fair_opp_notice_flag,
l_umbrella_program_id
FROM pon_auction_headers_all
WHERE auction_header_id = p_document_number;
SELECT MAX(sequence)
INTO l_max_sequence
FROM PON_BIDDING_PARTIES
WHERE AUCTION_HEADER_ID = P_DOCUMENT_NUMBER;
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
Nvl(l_max_sequence,0) + 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');
SELECT requisition_line_id ,
line_number
FROM pon_backing_requisitions
WHERE auction_header_id = P_DOCUMENT_NUMBER;
UPDATE pon_auction_item_prices_all
SET group_line_id = oneline.line_number
WHERE auction_header_id = P_DOCUMENT_NUMBER
AND group_line_id = oneline.requisition_line_id;
UPDATE pon_auction_item_prices_all
SET clm_base_line_num = oneline.line_number
WHERE auction_header_id = P_DOCUMENT_NUMBER
AND clm_base_line_num = oneline.requisition_line_id;
SELECT line_number
INTO l_line_number
FROM pon_backing_requisitions
WHERE auction_header_id = p_document_number
AND requisition_line_id = p_req_line_clin;
UPDATE pon_auction_item_prices_all
SET group_line_id = l_line_number
WHERE auction_header_id = p_document_number
AND line_number = p_line_number;
SELECT line_number
INTO l_line_number
FROM pon_backing_requisitions
WHERE auction_header_id = p_document_number
AND requisition_line_id = p_req_line_opt;
UPDATE pon_auction_item_prices_all
SET clm_base_line_num = l_line_number
WHERE auction_header_id = p_document_number
AND line_number = p_line_number;