[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_line_deleted VARCHAR2(1);
p_update_clause IN VARCHAR2,
p_retain_attachments IN VARCHAR2,
p_source_orig_round_id IN NUMBER,
p_source_prev_round_id IN NUMBER,
p_round_number IN NUMBER,
p_last_amendment_number IN NUMBER,
p_source_orig_amend_id IN NUMBER,
p_source_doctype_grp_name IN VARCHAR2,
p_source_auc_orig_code IN VARCHAR2,
x_document_number OUT NOCOPY VARCHAR2);
p_update_clause IN VARCHAR2,
p_retain_attachments IN VARCHAR2,
p_contracts_doctype IN VARCHAR2,
p_contract_type IN VARCHAR2,
p_document_number IN VARCHAR2);
p_update_clause IN VARCHAR2,
p_retain_attachments IN VARCHAR2,
p_tpc_name IN VARCHAR2,
p_tp_name IN VARCHAR2,
p_source_doctype_id IN NUMBER,
p_org_id IN NUMBER,
p_round_number IN NUMBER,
p_last_amendment_number IN NUMBER,
p_source_doc_num IN VARCHAR2,
p_style_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
);
p_update_clause IN VARCHAR2,
p_retain_attachments IN VARCHAR2,
p_large_auction_header_id IN NUMBER,
p_style_id IN NUMBER,
x_auction_header_id OUT NOCOPY NUMBER,
x_document_number OUT NOCOPY VARCHAR2,
x_request_id OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
--
-- Remember to change the l_api_version for change in the API
--
l_api_version CONSTANT NUMBER := 1.0;
l_error_code_update VARCHAR2(2000);
l_error_msg_update VARCHAR2(2000);
select style_id into l_style_id from pon_auction_headers_all
where auction_header_id = p_source_auction_header_id;
select approval_status
into l_approval_status
from pon_auction_headers_all
where
auction_header_id = p_source_auction_header_id;
SELECT USER_NAME
INTO l_tpc_name
FROM FND_USER
WHERE USER_ID = l_user_id;
SELECT PARTY_NAME
INTO l_tp_name
FROM HZ_PARTIES
WHERE PARTY_ID = l_tp_id;
SELECT auction_header_id
INTO l_locked_auction_header_id
FROM pon_auction_headers_all
WHERE auction_header_id = p_source_auction_header_id
FOR UPDATE;
select count(auction_header_id)
into l_newround_amendment_count
from pon_auction_headers_all
where auction_header_id_prev_round = p_source_auction_header_id;
select count(auction_header_id)
into l_newround_amendment_count
from pon_auction_headers_all
where auction_header_id_prev_amend = p_source_auction_header_id;
select auction_header_id_prev_amend into l_source_orig_amend_id from pon_auction_headers_all
where auction_header_id = p_source_auction_header_id;
select count(auction_header_id)
into l_newround_amendment_count
from pon_auction_headers_all
where auction_header_id_prev_amend = l_source_orig_amend_id and nvl(amendment_flag,'N') = 'N';
SELECT A.DOCTYPE_ID, A.AUCTION_HEADER_ID_ORIG_ROUND, A.AUCTION_HEADER_ID_PREV_ROUND,
nvl(A.AUCTION_ROUND_NUMBER,1), nvl( A.AMENDMENT_NUMBER,0), A.AUCTION_HEADER_ID_ORIG_AMEND,
A.ORG_ID, D.DOCTYPE_GROUP_NAME, D.MESSAGE_SUFFIX, A.AUCTION_ORIGINATION_CODE,A.DOCUMENT_NUMBER,
A.PRICE_BREAK_RESPONSE
INTO l_source_doctype_id, l_source_orig_round_id, l_source_prev_round_id, l_round_number,
l_last_amendment_number, l_source_orig_amend_id, l_org_id, l_source_doctype_grp_name,
g_message_suffix, l_source_auc_orig_code,l_source_doc_num, g_price_break_response
FROM PON_AUCTION_HEADERS_ALL A, PON_AUC_DOCTYPES D
WHERE auction_header_id = p_source_auction_header_id AND D.DOCTYPE_ID = A.DOCTYPE_ID ;
g_line_deleted := 'N';
SELECT PON_AUCTION_HEADERS_ALL_S.NEXTVAL
INTO l_auction_header_id
FROM DUAL;
p_update_clause => p_update_clause,
p_retain_attachments => l_retain_attachments,
p_source_orig_round_id => l_source_orig_round_id,
p_source_prev_round_id => l_source_prev_round_id,
p_round_number => l_round_number,
p_last_amendment_number => l_last_amendment_number,
p_source_orig_amend_id => l_source_orig_amend_id,
p_source_doctype_grp_name => l_source_doctype_grp_name,
p_source_auc_orig_code => l_source_auc_orig_code,
x_document_number => l_new_doc_number
);
argument14 => p_update_clause,
argument15 => p_retain_attachments,
argument16 => to_char(l_auction_header_id),
argument17 => to_char(l_style_id));
update pon_auction_headers_all set
request_id = l_request_id,
requested_by = p_user_id,
request_date = sysdate,
last_update_date = sysdate,
last_updated_by = p_user_id,
complete_flag = 'N'
where auction_header_id = l_auction_header_id;
-- Update the AUCTION_ORIGINATION_CODE with the source document
-- internal name if it is copy from RFI to Auction/RFQ. Set it to NULL
-- for the active negotiation copy. Carry it in all other cases
IF (p_copy_type = g_rfi_to_other_copy) THEN
SELECT INTERNAL_NAME INTO l_source_doc_internal_name
FROM PON_AUC_DOCTYPES P, PON_AUCTION_HEADERS_ALL A
WHERE P.DOCTYPE_ID = A.DOCTYPE_ID
AND AUCTION_HEADER_ID = p_source_auction_header_id;
update pon_auction_headers_all
set AUCTION_STATUS = 'APPLIED'
where auction_header_id = p_source_auction_header_id;
p_update_clause => p_update_clause,
p_retain_attachments => p_retain_attachments,
p_tpc_name => l_tpc_name,
p_tp_name => l_tp_name,
p_source_doctype_id => l_source_doctype_id,
p_org_id => l_org_id,
p_round_number => l_round_number,
p_last_amendment_number => l_last_amendment_number,
p_source_doc_num => l_source_doc_num,
p_style_id => l_style_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
update pon_auction_headers_all set
complete_flag = 'Y'
where auction_header_id = l_auction_header_id;
SELECT TRADING_PARTNER_ID,TRADING_PARTNER_CONTACT_ID,DOCTYPE_ID,
AWARD_APPROVAL_FLAG,STYLE_ID,CREATED_BY, DOCUMENT_NUMBER
INTO l_trading_partner_id,l_trading_partner_contact_id,
l_doctype_id,l_is_award_approval_reqd,l_style_id,l_user_id,
l_doc_number
FROM PON_AUCTION_HEADERS_ALL
WHERE AUCTION_HEADER_ID = p_source_auction_header_id;
SELECT USER_NAME INTO l_user_name
FROM FND_USER
WHERE USER_ID = l_user_id;
SELECT
SUP.USER_ID USER_ID
INTO l_mgr_id
FROM PER_ALL_ASSIGNMENTS_F ASS,
PER_ALL_ASSIGNMENTS_F SUPASS,
FND_USER SUP,
FND_USER EMP,
PER_ALL_PEOPLE_F PER,
PER_ALL_POSITIONS POS
WHERE ASS.PERSON_ID = EMP.EMPLOYEE_ID
AND ASS.SUPERVISOR_ID = SUP.EMPLOYEE_ID
AND ASS.PRIMARY_FLAG = 'Y'
AND ASS.ASSIGNMENT_TYPE in ('E', 'C')
AND TRUNC(SYSDATE) BETWEEN ASS.EFFECTIVE_START_DATE AND ASS.EFFECTIVE_END_DATE
AND SUPASS.PERSON_ID = ASS.SUPERVISOR_ID
AND SUPASS.POSITION_ID = POS.POSITION_ID(+)
AND SUPASS.PRIMARY_FLAG = 'Y'
AND ((SUPASS.ASSIGNMENT_TYPE = 'E' AND PER.CURRENT_EMPLOYEE_FLAG = 'Y') OR
(SUPASS.ASSIGNMENT_TYPE = 'C' AND PER.CURRENT_NPW_FLAG = 'Y'))
AND TRUNC(SYSDATE) BETWEEN PER.EFFECTIVE_START_DATE AND PER.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN SUPASS.EFFECTIVE_START_DATE AND SUPASS.EFFECTIVE_END_DATE
AND SUP.START_DATE <= SYSDATE
AND NVL(SUP.END_DATE, SYSDATE) >= SYSDATE
AND PER.PERSON_ID = SUP.EMPLOYEE_ID
AND EMP.USER_NAME = l_user_name
AND ROWNUM < 2;
-- selected explicitly
l_pdfFileName := PON_CLM_UTIL_PKG.get_clm_pdf_name(
l_doc_number,
l_src_rev_num,
USERENV('LANG'),
'BUYER'
);
p_update_clause IN VARCHAR2,
p_retain_attachments IN VARCHAR2,
p_source_orig_round_id IN NUMBER,
p_source_prev_round_id IN NUMBER,
p_round_number IN NUMBER,
p_last_amendment_number IN NUMBER,
p_source_orig_amend_id IN NUMBER,
p_source_doctype_grp_name IN VARCHAR2,
p_source_auc_orig_code IN VARCHAR2,
x_document_number OUT NOCOPY VARCHAR2
)
IS
l_contracts_doctype VARCHAR2(50);
l_return_value := PON_THREAD_DISC_PKG.insert_pon_discussions(
p_entity_name => 'PON_AUCTION_HEADERS_ALL',
p_entity_pk1 => p_auction_header_id,
p_entity_pk2 => '',
p_entity_pk3 => '',
p_entity_pk4 => '',
p_entity_pk5 => '',
p_subject => p_auction_header_id,
p_language_code => userenv('LANG'),
p_party_id => p_tp_contact_id,
p_validation_class => 'oracle.apps.pon.auctions.discussions.NegDiscussionValidation'
);
p_update_clause,
p_retain_attachments,
l_contracts_doctype,
l_contract_type,
l_document_number);
SELECT INTERNAL_NAME, MESSAGE_SUFFIX , DOCUMENT_NUMBER
INTO l_source_doc_internal_name, l_source_doc_msg_suffix,
l_source_doc_number
FROM PON_AUC_DOCTYPES P, PON_AUCTION_HEADERS_ALL A
WHERE P.DOCTYPE_ID = A.DOCTYPE_ID
AND AUCTION_HEADER_ID = p_source_auction_header_id;
SELECT INTERNAL_NAME, MESSAGE_SUFFIX , DOCUMENT_NUMBER
INTO l_source_doc_internal_name, l_source_doc_msg_suffix,
l_source_doc_number
FROM PON_AUC_DOCTYPES P, PON_AUCTION_HEADERS_ALL A
WHERE P.DOCTYPE_ID = A.DOCTYPE_ID
AND AUCTION_HEADER_ID = p_source_auction_header_id;
SELECT standard_form, document_format
INTO l_standard_form, l_document_format
FROM PO_PRINT_FORM_FORMATS
WHERE document_type = 'PO_SOL_AMEND_STD_FORM';
SELECT prev.standard_form, prev.document_format
INTO l_standard_form, l_document_format
FROM pon_auction_headers_all prev, pon_auction_headers_all pah
WHERE pah.auction_header_id = p_source_auction_header_id
AND prev.auction_header_id = pah.AUCTION_HEADER_ID_PREV_AMEND;
SELECT prev.standard_form, prev.document_format
INTO l_standard_form, l_document_format
FROM pon_auction_headers_all prev, pon_auction_headers_all pah
WHERE pah.auction_header_id = p_source_auction_header_id
AND Nvl(pah.amendment_flag, 'N') = 'Y'
AND prev.auction_header_id = pah.AUCTION_HEADER_ID_PREV_AMEND;
select document_number into l_orig_document_number from pon_auction_headers_all where auction_header_id = l_auction_header_id_orig_round;
select document_number into l_document_number from pon_auction_headers_all where auction_header_id = p_source_orig_amend_id;
SELECT DOCTYPE_GROUP_NAME
INTO l_destination_doctype_grp_name
FROM PON_AUC_DOCTYPES
WHERE DOCTYPE_ID = p_doctype_id;
g_err_loc := '1.3 Going to insert data into PON_AUCTION_HEADERS_ALL';
INSERT INTO PON_AUCTION_HEADERS_ALL
( AUCTION_HEADER_ID,
AUCTION_TITLE,
AUCTION_STATUS,
AUCTION_STATUS_NAME,
AWARD_STATUS,
AWARD_STATUS_NAME,
AUCTION_TYPE,
CONTRACT_TYPE,
TRADING_PARTNER_CONTACT_NAME,
TRADING_PARTNER_CONTACT_ID,
TRADING_PARTNER_NAME,
TRADING_PARTNER_NAME_UPPER,
TRADING_PARTNER_ID,
SHIP_TO_LOCATION_ID,
BILL_TO_LOCATION_ID,
OPEN_BIDDING_DATE,
CLOSE_BIDDING_DATE,
ORIGINAL_CLOSE_BIDDING_DATE,
VIEW_BY_DATE,
AWARD_BY_DATE,
PUBLISH_DATE,
CLOSE_DATE,
CANCEL_DATE,
TIME_ZONE,
BID_VISIBILITY_CODE,
BID_LIST_TYPE,
BID_FREQUENCY_CODE,
BID_SCOPE_CODE,
AUTO_EXTEND_FLAG,
AUTO_EXTEND_NUMBER,
NUMBER_OF_EXTENSIONS,
NUMBER_OF_BIDS,
MIN_BID_DECREMENT,
PRICE_DRIVEN_AUCTION_FLAG,
PAYMENT_TERMS_ID,
FREIGHT_TERMS_CODE,
FOB_CODE,
CARRIER_CODE,
CURRENCY_CODE,
RATE_TYPE,
RATE_DATE,
RATE,
NOTE_TO_BIDDERS,
ATTACHMENT_FLAG,
LANGUAGE_CODE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
WF_ITEM_KEY,
WF_ROLE_NAME,
AUTO_EXTEND_ALL_LINES_FLAG,
AUTO_EXTEND_MIN_TRIGGER_RANK,
MIN_BID_INCREMENT,
ALLOW_OTHER_BID_CURRENCY_FLAG,
SHIPPING_TERMS_CODE,
SHIPPING_TERMS,
AUTO_EXTEND_DURATION,
PROXY_BID_ALLOWED_FLAG,
PUBLISH_RATES_TO_BIDDERS_FLAG,
ATTRIBUTES_EXIST,
ORDER_NUMBER,
DOCUMENT_TRACKING_ID,
PO_TXN_FLAG,
EVENT_ID,
EVENT_TITLE,
SEALED_AUCTION_STATUS,
SEALED_ACTUAL_UNLOCK_DATE,
SEALED_ACTUAL_UNSEAL_DATE,
SEALED_UNLOCK_TP_CONTACT_ID,
SEALED_UNSEAL_TP_CONTACT_ID,
MODE_OF_TRANSPORT,
MODE_OF_TRANSPORT_CODE,
PO_START_DATE,
PO_END_DATE,
PO_AGREED_AMOUNT,
MIN_BID_CHANGE_TYPE,
FULL_QUANTITY_BID_CODE,
NUMBER_PRICE_DECIMALS,
AUTO_EXTEND_TYPE_FLAG,
AUCTION_ORIGINATION_CODE,
MULTIPLE_ROUNDS_FLAG,
AUCTION_HEADER_ID_ORIG_ROUND,
AUCTION_HEADER_ID_PREV_ROUND,
AUCTION_ROUND_NUMBER,
MANUAL_CLOSE_FLAG,
MANUAL_EXTEND_FLAG,
AUTOEXTEND_CHANGED_FLAG,
DOCTYPE_ID,
OFFER_TYPE,
MAX_RESPONSES,
RESPONSE_ALLOWED_FLAG,
FOB_NEG_FLAG,
CARRIER_NEG_FLAG,
FREIGHT_TERMS_NEG_FLAG,
MAX_RESPONSE_ITERATIONS,
PAYMENT_TERMS_NEG_FLAG,
MODE_OF_TRANSPORT_NEG_FLAG,
CONTRACT_ID,
CONTRACT_VERSION_NUM,
SHIPPING_TERMS_NEG_FLAG,
SHIPPING_METHOD_NEG_FLAG,
USE_REGIONAL_PRICING_FLAG,
SHOW_BIDDER_NOTES,
DERIVE_TYPE,
PRE_DELETE_AUCTION_STATUS,
DRAFT_LOCKED,
DRAFT_LOCKED_BY,
DRAFT_LOCKED_BY_CONTACT_ID,
DRAFT_LOCKED_DATE,
DRAFT_UNLOCKED_BY,
DRAFT_UNLOCKED_BY_CONTACT_ID,
DRAFT_UNLOCKED_DATE,
MAX_LINE_NUMBER,
BID_RANKING,
RANK_INDICATOR,
SHOW_BIDDER_SCORES,
OPEN_AUCTION_NOW_FLAG,
PUBLISH_AUCTION_NOW_FLAG,
TEMPLATE_ID,
REMINDER_DATE,
ORG_ID,
BUYER_ID,
MANUAL_EDIT_FLAG,
WF_PONCOMPL_ITEM_KEY,
HAS_PE_FOR_ALL_ITEMS,
HAS_PRICE_ELEMENTS,
PO_MIN_REL_AMOUNT,
GLOBAL_AGREEMENT_FLAG,
OUTCOME_STATUS,
SOURCE_REQS_FLAG,
AWARD_COMPLETE_DATE,
WF_PONCOMPL_CURRENT_ROUND,
SECURITY_LEVEL_CODE,
WF_APPROVAL_ITEM_KEY,
APPROVAL_STATUS,
SOURCE_DOC_ID,
SOURCE_DOC_NUMBER,
SOURCE_DOC_MSG,
SOURCE_DOC_LINE_MSG,
SOURCE_DOC_MSG_APP,
SHARE_AWARD_DECISION,
DESCRIPTION,
TEMPLATE_SCOPE,
TEMPLATE_STATUS,
IS_TEMPLATE_FLAG,
AWARD_APPROVAL_FLAG,
AWARD_APPROVAL_STATUS,
AWARD_APPR_AME_TRANS_ID,
AWARD_APPR_AME_TRANS_PREV_ID,
WF_AWARD_APPROVAL_ITEM_KEY,
AMENDMENT_NUMBER,
AMENDMENT_DESCRIPTION,
AUCTION_HEADER_ID_ORIG_AMEND,
AUCTION_HEADER_ID_PREV_AMEND,
DOCUMENT_NUMBER,
AWARD_APPR_AME_TXN_DATE,
HDR_ATTR_ENABLE_WEIGHTS,
-- HDR_ATTRIBUTE_DISPLAY_SCORE, I think somebody has dropped the column for new enhancement
HDR_ATTR_MAXIMUM_SCORE,
ATTRIBUTE_LINE_NUMBER,
HDR_ATTR_DISPLAY_SCORE,
CONTERMS_EXIST_FLAG,
CONTERMS_ARTICLES_UPD_DATE,
CONTERMS_DELIV_UPD_DATE,
AWARD_MODE,
HAS_HDR_ATTR_FLAG,
AWARD_DATE,
MAX_INTERNAL_LINE_NUM,
IS_PAUSED,
PAUSE_REMARKS,
LAST_PAUSE_DATE,
MAX_DOCUMENT_LINE_NUM,
PF_TYPE_ALLOWED, -- For Transformation bidding project
SUPPLIER_VIEW_TYPE, -- For Transformation bidding project
ABSTRACT_DETAILS, -- Abstract/Forms project
MAX_BID_COLOR_SEQUENCE_ID, -- CPA and Console related columns
HAS_ITEMS_FLAG,
SUPPLIER_ENTERABLE_PYMT_FLAG,
PROGRESS_PAYMENT_TYPE,
PROGRESS_PYMT_NEGOTIABLE_FLAG,
ADVANCE_NEGOTIABLE_FLAG,
RECOUPMENT_NEGOTIABLE_FLAG,
MAX_RETAINAGE_NEGOTIABLE_FLAG,
RETAINAGE_NEGOTIABLE_FLAG,
PROJECT_ID,
INT_ATTRIBUTE_CATEGORY ,
INT_ATTRIBUTE1,
INT_ATTRIBUTE2,
INT_ATTRIBUTE3,
INT_ATTRIBUTE4,
INT_ATTRIBUTE5,
INT_ATTRIBUTE6,
INT_ATTRIBUTE7,
INT_ATTRIBUTE8,
INT_ATTRIBUTE9,
INT_ATTRIBUTE10,
INT_ATTRIBUTE11,
INT_ATTRIBUTE12,
INT_ATTRIBUTE13,
INT_ATTRIBUTE14,
INT_ATTRIBUTE15,
EXT_ATTRIBUTE_CATEGORY,
EXT_ATTRIBUTE1,
EXT_ATTRIBUTE2,
EXT_ATTRIBUTE3,
EXT_ATTRIBUTE4,
EXT_ATTRIBUTE5,
EXT_ATTRIBUTE6,
EXT_ATTRIBUTE7,
EXT_ATTRIBUTE8,
EXT_ATTRIBUTE9,
EXT_ATTRIBUTE10,
EXT_ATTRIBUTE11,
EXT_ATTRIBUTE12,
EXT_ATTRIBUTE13,
EXT_ATTRIBUTE14,
EXT_ATTRIBUTE15,
STYLE_ID,
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,
HAS_SCORING_TEAMS_FLAG,
PO_STYLE_ID,
PRICE_BREAK_RESPONSE,
NUMBER_OF_LINES,
LAST_LINE_NUMBER,
BID_DECREMENT_METHOD,
STAGGERED_CLOSING_INTERVAL,
FIRST_LINE_CLOSE_DATE,
DISPLAY_BEST_PRICE_BLIND_FLAG,
ENFORCE_PREVRND_BID_PRICE_FLAG,
QTY_PRICE_TIERS_ENABLED_FLAG,
PRICE_TIERS_INDICATOR,
TWO_PART_FLAG ,
-- Added by Lion for EMD on 2008/12/12
-------------------------------------------------
EMD_ENABLE_FLAG ,
EMD_AMOUNT ,
EMD_DUE_DATE,
EMD_TYPE ,
EMD_ADDITIONAL_INFORMATION,
EMD_GUARANTEE_EXPIRY_DATE,
POST_EMD_TO_FINANCE,
-------------------------------------------------
SOLICITATION_TYPE, --
UDA_TEMPLATE_ID, --
UDA_TEMPLATE_DATE, --
NO_OF_COPIES, --
STANDARD_FORM,--
DOCUMENT_FORMAT, --
-- Begin Supplier Management: Bug 8993731
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 8993731
-- added as part of changes for bug 8771921
NEGOTIATION_REQUESTER_ID,
ALLOW_UNSOL_OFFER_LINES, --
ALLOW_WITHDRAW_FLAG, --
FAIR_OPP_NOTICE_FLAG,
UMBRELLA_PROGRAM_ID,
FEDERAL_PUBLICATION_EXCEPTION, -- FedBizOpps changes
ALLOW_MULTIPLE_ACTIVE_OFFERS, --
ALLOW_STAGGERED_AWARDS --
)
SELECT
p_auction_header_id,
AUCTION_TITLE,
'DRAFT', -- AUCTION_STATUS
NULL, -- AUCTION_STATUS_NAME
'NO', -- AWARD_STATUS
NULL, -- AWARD_STATUS_NAME
AUCTION_TYPE,
t_record.CONTRACT_TYPE ,
--
-- During amendment we should retain the same
-- trading_partner_contact_name
--
decode (p_copy_type,
g_amend_copy, TRADING_PARTNER_CONTACT_NAME,
g_conformed_doc_copy, TRADING_PARTNER_CONTACT_NAME,
p_tpc_name), -- TRADING_PARTNER_CONTACT_NAME
--
-- During amendment we should retain the same
-- trading_partner_contact_id
--
decode (p_copy_type,
g_amend_copy, TRADING_PARTNER_CONTACT_ID,
g_conformed_doc_copy, TRADING_PARTNER_CONTACT_ID,
p_tp_contact_id), -- TRADING_PARTNER_CONTACT_ID
p_tp_name,
UPPER(p_tp_name),
p_tp_id,
t_record.SHIP_TO_LOCATION_ID, -- SHIP_TO_LOCATION_ID
t_record.BILL_TO_LOCATION_ID, -- BILL_TO_LOCATION_ID
--
-- OPEN_BIDDING_DATE should get copied for Amendment.
-- It should be defaulted to NULL and then one hour after current date for
-- all other cases. This is the current Copy Reset logic in
-- AuctionHeadersAllVoImpl
--
decode(p_copy_type,
g_amend_copy, OPEN_BIDDING_DATE,
g_conformed_doc_copy, OPEN_BIDDING_DATE,
SYSDATE+1/24),
decode(p_copy_type,
g_amend_copy, CLOSE_BIDDING_DATE,
g_conformed_doc_copy, CLOSE_BIDDING_DATE,
NULL), -- CLOSE_BIDDING_DATE
NULL, -- ORIGINAL_CLOSE_BIDDING_DATE
decode(p_copy_type,
g_amend_copy, VIEW_BY_DATE,
g_conformed_doc_copy, VIEW_BY_DATE,
NULL), -- VIEW_BY_DATE
decode(p_copy_type,
g_amend_copy, AWARD_BY_DATE,
g_conformed_doc_copy, AWARD_BY_DATE,
NULL), -- AWARD_BY_DATE
NULL, -- PUBLISH_DATE
NULL, -- CLOSE_DATE
NULL, -- CANCEL_DATE
NULL, -- TIME_ZONE, it seems to be always null
t_record.BID_VISIBILITY_CODE, -- BID_VISIBILITY_CODE
t_record.BID_LIST_TYPE, -- BID_LIST_TYPE
t_record.BID_FREQUENCY_CODE, -- BID_FREQUENCY_CODE
t_record.BID_SCOPE_CODE, -- BID_SCOPE_CODE
t_record.AUTO_EXTEND_FLAG, -- AUTO_EXTEND_FLAG
decode(nvl(t_record.AUTO_EXTEND_FLAG,'N'),
'Y', AUTO_EXTEND_NUMBER,
NULL), -- AUTO_EXTEND_NUMBER,
NULL, -- NUMBER_OF_EXTENSIONS
0, -- NUMBER_OF_BIDS
decode(t_record.PRICE_DRIVEN_AUCTION_FLAG,
'N', NULL,
MIN_BID_DECREMENT), -- MIN_BID_DECREMENT
t_record.PRICE_DRIVEN_AUCTION_FLAG, -- PRICE_DRIVEN_AUCTION_FLAG
t_record.PAYMENT_TERMS_ID, -- PAYMENT_TERMS_ID
t_record.FREIGHT_TERMS_CODE , -- FREIGHT_TERMS_CODE
t_record.FOB_CODE, -- FOB_CODE
t_record.CARRIER_CODE, -- CARRIER_CODE
t_record.CURRENCY_CODE , -- CURRENCY_CODE
t_record.RATE_TYPE, -- RATE_TYPE
RATE_DATE,
RATE,
NOTE_TO_BIDDERS,
decode (p_retain_attachments,
'Y', ATTACHMENT_FLAG,
'N'), -- ATTACHMENT_FLAG
LANGUAGE_CODE,
SYSDATE, -- CREATION_DATE
p_user_id, -- CREATED_BY
SYSDATE, -- LAST_UPDATE_DATE
p_user_id, -- LAST_UPDATED_BY
NULL, -- WF_ITEM_KEY
NULL, -- WF_ROLE_NAME
t_record.AUTO_EXTEND_ALL_LINES_FLAG, -- AUTO_EXTEND_ALL_LINES_FLAG
t_record.AUTO_EXTEND_MIN_TRIGGER_RANK, -- AUTO_EXTEND_MIN_TRIGGER_RANK
decode(t_record.PRICE_DRIVEN_AUCTION_FLAG,
'N', NULL,
MIN_BID_INCREMENT), -- MIN_BID_INCREMENT
ALLOW_OTHER_BID_CURRENCY_FLAG,
SHIPPING_TERMS_CODE, -- Always NULL
SHIPPING_TERMS, -- Always NULL
t_record.AUTO_EXTEND_DURATION, -- AUTO_EXTEND_DURATION
PROXY_BID_ALLOWED_FLAG, -- Always NULL though bizrule is available
PUBLISH_RATES_TO_BIDDERS_FLAG,
ATTRIBUTES_EXIST,
NULL, -- ORDER_NUMBER
NULL, -- DOCUMENT_TRACKING_ID
NULL, -- PO_TXN_FLAG
--
-- for the new round and amendment cases, we do keep event information
-- (AuctionHeadersAllVOimpl logic)
--
decode(p_copy_type,
g_new_rnd_copy, t_record.EVENT_ID,
g_amend_copy, EVENT_ID,
g_conformed_doc_copy, EVENT_ID,
NULL), -- EVENT_ID
--
-- for the new round and amendment cases, we do keep event information
-- (AuctionHeadersAllVOimpl logic)
--
decode(p_copy_type,
g_new_rnd_copy, t_record.EVENT_TITLE,
g_amend_copy, EVENT_TITLE,
g_conformed_doc_copy, EVENT_TITLE,
NULL), -- EVENT_TITLE
--
-- Set sealed_auction_status to Locked if bidVisibilityCode = 'SEALED_AUCTION'
-- (AuctionHeadersAllVOimpl logic). But I will set it to NULL always and the publish
-- logic will change it if required
--
NULL, -- SEALED_AUCTION_STATUS
NULL, -- SEALED_ACTUAL_UNLOCK_DATE
NULL, -- SEALED_ACTUAL_UNSEAL_DATE
NULL, -- SEALED_UNLOCK_TP_CONTACT_ID
NULL, -- SEALED_UNSEAL_TP_CONTACT_ID
MODE_OF_TRANSPORT, -- Seems to be NULL always
MODE_OF_TRANSPORT_CODE, -- Seems to be NULL always
t_record.PO_START_DATE , -- PO_START_DATE
t_record.PO_END_DATE, -- PO_END_DATE,
t_record.PO_AGREED_AMOUNT , -- PO_AGREED_AMOUNT,
t_record.MIN_BID_CHANGE_TYPE, -- MIN_BID_CHANGE_TYPE
--
-- For blankets cannot restrict user to bid on full quantity
-- (AuctionHeadersALLEOImpl logic)
-- The same logic applies for the newly introduced CPA also
--
decode(t_record.CONTRACT_TYPE,
'BLANKET', 'PARTIAL_QTY_BIDS_ALLOWED',
'CONTRACT', 'PARTIAL_QTY_BIDS_ALLOWED',
t_record.FULL_QUANTITY_BID_CODE) , -- FULL_QUANTITY_BID_CODE
--
-- Even if the NUMBER_PRICE_DECIMALS is NULL (draft) we are not setting it to
-- 1000 as that will be done in the AuctionHeadersALLEOImpl
--
NUMBER_PRICE_DECIMALS,
t_record.AUTO_EXTEND_TYPE_FLAG, -- AUTO_EXTEND_TYPE_FLAG
--
-- Update the AUCTION_ORIGINATION_CODE with the source document
-- internal name if it is copy from RFI to Auction/RFQ. Set it to NULL
-- for the active negotiation copy. Carry it in all other cases
-- (AuctionHeaderaAllVOImpl logic)
--
g_auc_origination_code, -- AUCTION_ORIGINATION_CODE
t_record.MULTIPLE_ROUNDS_FLAG, -- MULTIPLE_ROUNDS_FLAG
l_auction_header_id_orig_round, -- AUCTION_HEADER_ID_ORIG_ROUND
l_auction_header_id_prev_round, -- AUCTION_HEADER_ID_PREV_ROUND
l_auction_round_number, -- AUCTION_ROUND_NUMBER
t_record.MANUAL_CLOSE_FLAG, -- MANUAL_CLOSE_FLAG
t_record.MANUAL_EXTEND_FLAG, -- MANUAL_EXTEND_FLAG
NULL, -- AUTOEXTEND_CHANGED_FLAG, Can not find any reason to carry it
p_doctype_id, -- DOCTYPE_ID
OFFER_TYPE,
--
-- Carry over the Sysadmin setting for Award Appr. flag except for copy
-- for Amendment
--
NULL, -- MAX_RESPONSES
--
-- Still carrying it though it is genrally Y in the DB and there is a bizrule
-- applicable for Offers (RESPONSE_ALLOWED_FLAG)
--
RESPONSE_ALLOWED_FLAG,
--
-- Still carrying it though it is genrally N in the DB
-- (FOB_NEG_FLAG)
--
FOB_NEG_FLAG,
CARRIER_NEG_FLAG, -- Still carrying it though it is genrally N in the DB
FREIGHT_TERMS_NEG_FLAG, -- Still carrying it though it is genrally N in the DB
NULL, -- MAX_RESPONSE_ITERATIONS
PAYMENT_TERMS_NEG_FLAG, -- Still carrying it though it is genrally N in the DB
MODE_OF_TRANSPORT_NEG_FLAG, -- Still carrying it though it is genrally N in the DB
decode (p_retain_clause,
'Y', CONTRACT_ID,
NULL), -- CONTRACT_ID
decode (p_retain_clause,
'Y', CONTRACT_VERSION_NUM,
NULL), -- CONTRACT_VERSION_NUM, will be updated later if reqd
SHIPPING_TERMS_NEG_FLAG, -- Still carrying it though it is genrally N in the DB
SHIPPING_METHOD_NEG_FLAG, -- Still carrying it though it is genrally N in the DB
USE_REGIONAL_PRICING_FLAG, -- Still carrying it though it is genrally NULL in the DB
--
-- Set SHOW_BIDDER_NOTES to N if bidVisibilityCode = 'SEALED_AUCTION'
-- (AuctionHeadersALLEOImpl logic)
--
decode(t_record.BID_VISIBILITY_CODE,
'SEALED_BIDDING', 'N',
t_record.SHOW_BIDDER_NOTES), -- SHOW_BIDDER_NOTES
DERIVE_TYPE,
NULL, -- PRE_DELETE_AUCTION_STATUS, it is always null in BOLC
decode(p_copy_type,
g_conformed_doc_copy, 'N',
'Y'), -- DRAFT_LOCKED
decode(p_copy_type,
g_conformed_doc_copy, NULL,
p_tp_id), -- DRAFT_LOCKED_BY
decode(p_copy_type,
g_conformed_doc_copy, NULL,
p_tp_contact_id), -- DRAFT_LOCKED_BY_CONTACT_ID, tp_contact_id
decode(p_copy_type,
g_conformed_doc_copy, NULL,
SYSDATE), -- DRAFT_LOCKED_DATE
NULL, -- DRAFT_UNLOCKED_BY
NULL, -- DRAFT_UNLOCKED_BY_CONTACT_ID
NULL, -- DRAFT_UNLOCKED_DATE
NULL, -- MAX_LINE_NUMBER (unused)
t_record.BID_RANKING, -- BID_RANKING
t_record.RANK_INDICATOR, -- RANK_INDICATOR
--
-- SHOW_BIDDER_SCORES will be changed to NONE if the BID_RANKING is PRICE_ONLY.
--
decode(t_record.BID_RANKING,
'PRICE_ONLY', 'NONE',
t_record.SHOW_BIDDER_SCORES), -- SHOW_BIDDER_SCORES
'N', -- OPEN_AUCTION_NOW_FLAG, always defaulted to N
'N', -- PUBLISH_AUCTION_NOW_FLAG, always defaulted to N, Copy Reset logic
NULL, -- TEMPLATE_ID
NULL, -- REMINDER_DATE, Copy Reset logic
ORG_ID,
--
-- Keep the buyer_id only for draft, amendment, same doctype new round
--
decode(l_copy_buyer_id,
'Y', BUYER_ID,
NULL), -- BUYER_ID
'Y', -- MANUAL_EDIT_FLAG , setting it to Y otherwise user can not edit a draft with REQ/BPA
NULL, -- WF_PONCOMPL_ITEM_KEY, to be set to NULL
--
-- It is set to N. Later on this will be set to Y or N while being published
-- (setChildrenExistFlags method logic in AuctionHeadersALLEOImpl)
--
'N', -- HAS_PE_FOR_ALL_ITEMS
--
-- It is also set to N. Later on this will be set to Y or N while being published
-- (setChildrenExistFlags method logic in AuctionHeadersALLEOImpl)
--
'N', -- HAS_PRICE_ELEMENTS
t_record.PO_MIN_REL_AMOUNT, -- PO_MIN_REL_AMOUNT
t_record.GLOBAL_AGREEMENT_FLAG, -- GLOBAL_AGREEMENT_FLAG
NULL, -- OUTCOME_STATUS
--
-- SOURCE_REQS_FLAG to be set to null as per the copyReset method logic in
-- AuctionHeadersAllVOImpl
--
NULL, -- SOURCE_REQS_FLAG
NULL, -- AWARD_COMPLETE_DATE
NULL, -- WF_PONCOMPL_CURRENT_ROUND, to be set to NULL
--
-- SECURITY_LEVEL_CODE can be set to PUBLIC if it is null
-- if neg team is disabled by style and old value is PRIVATE
-- reset to PUBLIC
--
decode(g_neg_style_control.neg_team_enabled_flag,
'N', decode(SECURITY_LEVEL_CODE, 'PRIVATE', 'PUBLIC',
nvl(SECURITY_LEVEL_CODE, 'PUBLIC')),
nvl(SECURITY_LEVEL_CODE, 'PUBLIC')), -- SECURITY_LEVEL_CODE
NULL, -- WF_APPROVAL_ITEM_KEY, to be set to NULL
--
-- If there are no value for APPROVAL_STATUS then set it to NOT_REQUIRED
-- Otherwise set it to REQUIRED. We are not setting it to the actual status
-- as setApprovalStatusValue method of AuctionHeadersALLEOImpl will
-- set it while publising the negotiation or saving the negotiation as draft
--
decode(p_copy_type,
g_conformed_doc_copy, 'NOT_REQUIRED',
decode(nvl(APPROVAL_STATUS, 'NOT_REQUIRED'),
'NOT_REQUIRED', 'NOT_REQUIRED',
'REQUIRED')), -- APPROVAL_STATUS
--
-- We are going to keep the SOURCE_DOC_XX fields for Copy To Auction,
-- Copy To RFQ as well as any cross doctype copy. This is a new behavior
--
decode(l_is_succession,
'Y', SOURCE_DOC_ID,
l_source_doc_id), -- SOURCE_DOC_ID
decode(l_is_succession,
'Y', SOURCE_DOC_NUMBER,
l_source_doc_number), -- SOURCE_DOC_NUMBER
decode(l_is_succession,
'Y', SOURCE_DOC_MSG,
l_source_doc_msg), -- SOURCE_DOC_MSG
decode(l_is_succession,
'Y', SOURCE_DOC_LINE_MSG,
l_source_doc_line_msg), -- SOURCE_DOC_LINE_MSG
decode(l_is_succession,
'Y', SOURCE_DOC_MSG_APP,
l_source_doc_msg_app ), -- SOURCE_DOC_MSG_APP
--
-- Set SHARE_AWARD_DECISION to N, setCommonDefaults method logic
-- in AuctionHeadersALLEOImpl
--
'N', -- SHARE_AWARD_DECISION
NULL, -- DESCRIPTION, The DESCRIPTION field is only for templates
NULL, -- TEMPLATE_SCOPE,
NULL, -- TEMPLATE_STATUS,
NULL, -- IS_TEMPLATE_FLAG,
--
-- Carry over the Sysadmin setting for Award Appr. flag except for copy
-- for Amendment
--
decode(p_copy_type,
g_amend_copy, AWARD_APPROVAL_FLAG,
g_conformed_doc_copy, AWARD_APPROVAL_FLAG,
l_is_award_approval_reqd), -- AWARD_APPROVAL_FLAG
decode(p_copy_type,
g_amend_copy, AWARD_APPROVAL_STATUS,
g_conformed_doc_copy, AWARD_APPROVAL_STATUS,
decode(l_is_award_approval_reqd,
'Y', 'REQUIRED',
'NOT_REQUIRED')), -- AWARD_APPROVAL_STATUS
NULL, -- AWARD_APPR_AME_TRANS_ID
NULL, -- AWARD_APPR_AME_TRANS_PREV_ID
NULL, -- WF_AWARD_APPROVAL_ITEM_KEY
decode(p_copy_type,
g_amend_copy, nvl(AMENDMENT_NUMBER,0)+1,
g_conformed_doc_copy, nvl(AMENDMENT_NUMBER,0),
0), -- AMENDMENT_NUMBER
NULL, -- AMENDMENT_DESCRIPTION
decode(p_copy_type,
g_amend_copy, AUCTION_HEADER_ID_ORIG_AMEND,
g_conformed_doc_copy, AUCTION_HEADER_ID_ORIG_AMEND,
p_auction_header_id), -- AUCTION_HEADER_ID_ORIG_AMEND
decode(p_copy_type,
'AMENDMENT', p_source_auction_header_id,
g_conformed_doc_copy, AUCTION_HEADER_ID_PREV_AMEND,
NULL), -- AUCTION_HEADER_ID_PREV_AMEND
l_document_number, -- DOCUMENT_NUMBER
NULL, -- AWARD_APPR_AME_TXN_DATE
--
-- AuctionHeadersALLEOImpl.setAdminPrefScoreSettings may override this later on
--
HDR_ATTR_ENABLE_WEIGHTS,
-- HDR_ATTRIBUTE_DISPLAY_SCORE, Some body has removed the column in prcdv10p
HDR_ATTR_MAXIMUM_SCORE,
ATTRIBUTE_LINE_NUMBER,
HDR_ATTR_DISPLAY_SCORE,
l_contract_terms_exisits, -- CONTERMS_EXIST_FLAG
--
-- No existing logic, hence keeping the CONTERMS_ARTICLES_UPD_DATE and
-- CONTERMS_DELIV_UPD_DATE
--
CONTERMS_ARTICLES_UPD_DATE,
CONTERMS_DELIV_UPD_DATE,
NULL, -- AWARD_MODE
HAS_HDR_ATTR_FLAG,
NULL, -- AWARD_DATE
--
-- AuctionHeadersAllVOImpl logic
--
decode(l_is_succession,
'Y', MAX_INTERNAL_LINE_NUM,
NULL), -- MAX_INTERNAL_LINE_NUM
NULL, -- IS_PAUSED
NULL, -- PAUSE_REMARKS
NULL, -- LAST_PAUSE_DATE
decode(p_copy_type, g_amend_copy, MAX_DOCUMENT_LINE_NUM,
g_conformed_doc_copy, MAX_DOCUMENT_LINE_NUM,
g_new_rnd_copy, MAX_DOCUMENT_LINE_NUM,
NULL), -- MAX_DOCUMENT_LINE_NUM
--
-- Transformation project:
--
t_record.PF_TYPE_ALLOWED, -- PF_TYPE_ALLOWED
--
-- Transformation project logic:
-- Since RFI has no price elements, set the column to TRANSFORMED
-- while copying to a RFI
--
decode(p_doctype_id,
g_rfi_doctype_id, 'TRANSFORMED',
decode(g_neg_style_control.price_element_enabled_flag,
'N', 'TRANSFORMED', SUPPLIER_VIEW_TYPE)), -- SUPPLIER_VIEW_TYPE
ABSTRACT_DETAILS, -- Abstract/Forms project related column
--
-- CPA and Console projects related columns
--
-- MAX_BID_COLOR_SEQUENCE_ID Field Logic
-- ---------------------------------------------------------
-- Copy MAX_BID_COLOR_SEQUENCE_ID field as is for New Round and
-- Amendments. In all other cases reset it to -1. Keeping it for cross copy.
-- Keep all other columns intact
--
decode(p_copy_type,
g_active_neg_copy, -1,
g_draft_neg_copy, -1,
MAX_BID_COLOR_SEQUENCE_ID), -- MAX_BID_COLOR_SEQUENCE_ID
HAS_ITEMS_FLAG,
decode(p_doctype_id, g_rfq_doctype_id, SUPPLIER_ENTERABLE_PYMT_FLAG, g_sol_doctype_id, SUPPLIER_ENTERABLE_PYMT_FLAG, 'N'),
decode(p_doctype_id, g_rfq_doctype_id, PROGRESS_PAYMENT_TYPE, g_sol_doctype_id, PROGRESS_PAYMENT_TYPE, 'NONE'),
decode(p_doctype_id, g_rfq_doctype_id, PROGRESS_PYMT_NEGOTIABLE_FLAG, g_sol_doctype_id, PROGRESS_PYMT_NEGOTIABLE_FLAG, 'N'),
decode(p_doctype_id, g_rfq_doctype_id, ADVANCE_NEGOTIABLE_FLAG, g_sol_doctype_id, ADVANCE_NEGOTIABLE_FLAG, 'N'),
decode(p_doctype_id, g_rfq_doctype_id, RECOUPMENT_NEGOTIABLE_FLAG, g_sol_doctype_id, RECOUPMENT_NEGOTIABLE_FLAG, 'N'),
decode(p_doctype_id, g_rfi_doctype_id, 'N', MAX_RETAINAGE_NEGOTIABLE_FLAG),
decode(p_doctype_id, g_rfi_doctype_id, 'N', RETAINAGE_NEGOTIABLE_FLAG),
PROJECT_ID,
INT_ATTRIBUTE_CATEGORY,
INT_ATTRIBUTE1,
INT_ATTRIBUTE2,
INT_ATTRIBUTE3,
INT_ATTRIBUTE4,
INT_ATTRIBUTE5,
INT_ATTRIBUTE6,
INT_ATTRIBUTE7,
INT_ATTRIBUTE8,
INT_ATTRIBUTE9,
INT_ATTRIBUTE10,
INT_ATTRIBUTE11,
INT_ATTRIBUTE12,
INT_ATTRIBUTE13,
INT_ATTRIBUTE14,
INT_ATTRIBUTE15,
EXT_ATTRIBUTE_CATEGORY,
EXT_ATTRIBUTE1,
EXT_ATTRIBUTE2,
EXT_ATTRIBUTE3,
EXT_ATTRIBUTE4,
EXT_ATTRIBUTE5,
EXT_ATTRIBUTE6,
EXT_ATTRIBUTE7,
EXT_ATTRIBUTE8,
EXT_ATTRIBUTE9,
EXT_ATTRIBUTE10,
EXT_ATTRIBUTE11,
EXT_ATTRIBUTE12,
EXT_ATTRIBUTE13,
EXT_ATTRIBUTE14,
EXT_ATTRIBUTE15,
g_neg_style_raw.STYLE_ID,
g_neg_style_raw.LINE_ATTRIBUTE_ENABLED_FLAG,
g_neg_style_raw.LINE_MAS_ENABLED_FLAG,
g_neg_style_raw.PRICE_ELEMENT_ENABLED_FLAG,
g_neg_style_raw.RFI_LINE_ENABLED_FLAG,
g_neg_style_raw.LOT_ENABLED_FLAG,
g_neg_style_raw.GROUP_ENABLED_FLAG,
g_neg_style_raw.LARGE_NEG_ENABLED_FLAG,
g_neg_style_raw.HDR_ATTRIBUTE_ENABLED_FLAG,
g_neg_style_raw.NEG_TEAM_ENABLED_FLAG,
g_neg_style_raw.PROXY_BIDDING_ENABLED_FLAG,
g_neg_style_raw.POWER_BIDDING_ENABLED_FLAG,
g_neg_style_raw.AUTO_EXTEND_ENABLED_FLAG,
g_neg_style_raw.TEAM_SCORING_ENABLED_FLAG,
DECODE(g_neg_style_raw.TEAM_SCORING_ENABLED_FLAG, 'N', 'N', HAS_SCORING_TEAMS_FLAG),
decode(p_doctype_id, g_rfi_doctype_id, NULL, PO_STYLE_ID),
g_price_break_response,
NUMBER_OF_LINES,
LAST_LINE_NUMBER,
decode(p_doctype_id, g_auction_doctype_id, BID_DECREMENT_METHOD, ''),
t_record.STAGGERED_CLOSING_INTERVAL,
t_record.FIRST_LINE_CLOSE_DATE,
decode(t_record.BEST_PRICE, 'Y',
decode(p_source_doctype_id, g_rfi_doctype_id,
l_disp_best_price_blind, DISPLAY_BEST_PRICE_BLIND_FLAG),
'N'), -- DISPLAY_BEST_PRICE_BLIND_FLAG
-- copy the flag to enforce previous round bid price as start price for amendment
-- else copy the configured value
DECODE(p_copy_type, g_amend_copy, ENFORCE_PREVRND_BID_PRICE_FLAG, g_conformed_doc_copy, ENFORCE_PREVRND_BID_PRICE_FLAG, l_MinBidPriceVal1),
g_neg_style_raw.QTY_PRICE_TIERS_ENABLED_FLAG,
t_record.PRICE_TIERS_INDICATOR,
DECODE(p_doctype_id, g_rfq_doctype_id, TWO_PART_FLAG, g_sol_doctype_id, TWO_PART_FLAG, NULL),
-- Added by Lion for EMD on 2008/12/12
-------------------------------------------------
EMD_ENABLE_FLAG ,
EMD_AMOUNT ,
EMD_DUE_DATE,
EMD_TYPE ,
EMD_ADDITIONAL_INFORMATION,
EMD_GUARANTEE_EXPIRY_DATE,
POST_EMD_TO_FINANCE,
-----------------------------------------------------
SOLICITATION_TYPE, --
UDA_TEMPLATE_ID, --
UDA_TEMPLATE_DATE, --
NO_OF_COPIES, --
Nvl(l_standard_form,standard_form),
Nvl(l_document_format,document_format),
-- Begin Supplier Management: Bug 8993731
g_neg_style_raw.SUPP_REG_QUAL_FLAG,
g_neg_style_raw.SUPP_EVAL_FLAG,
g_neg_style_raw.HIDE_TERMS_FLAG,
g_neg_style_raw.HIDE_ABSTRACT_FORMS_FLAG,
g_neg_style_raw.HIDE_ATTACHMENTS_FLAG,
g_neg_style_raw.INTERNAL_EVAL_FLAG,
g_neg_style_raw.HDR_SUPP_ATTR_ENABLED_FLAG,
g_neg_style_raw.INTGR_HDR_ATTR_FLAG,
g_neg_style_raw.INTGR_HDR_ATTACH_FLAG,
g_neg_style_raw.LINE_SUPP_ATTR_ENABLED_FLAG,
g_neg_style_raw.ITEM_SUPP_ATTR_ENABLED_FLAG,
g_neg_style_raw.INTGR_CAT_LINE_ATTR_FLAG,
g_neg_style_raw.INTGR_ITEM_LINE_ATTR_FLAG,
g_neg_style_raw.INTGR_CAT_LINE_ASL_FLAG,
decode(g_neg_style_raw.INTERNAL_EVAL_FLAG, 'Y', 'Y', INTERNAL_ONLY_FLAG),
-- End Supplier Management: Bug 8993731
NEGOTIATION_REQUESTER_ID, -- bug 8771921
t_record.ALLOW_UNSOL_OFFER_LINES, --Copy Allow unsolicited offer lines from base doc
t_record.ALLOW_WITHDRAW_FLAG, --Copy Allow Withdraw Flag from base doc
FAIR_OPP_NOTICE_FLAG,
Decode(Nvl(FAIR_OPP_NOTICE_FLAG,'N'),'Y',umbrella_program_id,NULL),
decode (p_copy_type,
g_amend_copy, FEDERAL_PUBLICATION_EXCEPTION,
g_conformed_doc_copy, FEDERAL_PUBLICATION_EXCEPTION,
g_new_rnd_copy, FEDERAL_PUBLICATION_EXCEPTION,
NULL), -- FBO bug 13445860, 13450752
t_record.ALLOW_MULTIPLE_ACTIVE_OFFERS, --Copy Allow multiple active offers flag from base doc
t_record.ALLOW_STAGGERED_AWARDS --Copy Allow staggered awards flag from base doc
FROM PON_AUCTION_HEADERS_ALL
WHERE AUCTION_HEADER_ID = p_source_auction_header_id;
update pon_auction_headers_all
set revision = l_Revision_Number
where auction_header_id = p_auction_header_id;
l_amendment_update PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
l_last_amendment_update PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
SELECT
A.DISP_LINE_NUMBER,
A.LAST_AMENDMENT_UPDATE,
A.LINE_NUMBER,
A.ITEM_DESCRIPTION,
A.CATEGORY_ID,
A.CATEGORY_NAME,
A.IP_CATEGORY_ID,
A.UOM_CODE,
A.QUANTITY,
A.NEED_BY_DATE, -- NEED TO CHANGE? No, carry it over
A.SHIP_TO_LOCATION_ID,
A.TARGET_PRICE,
A.THRESHOLD_PRICE,
-- Bug 4740593 decode added as part of enforcing previous round bid price project
-- Do not copy line start price for new round
DECODE(p_copy_type, 'NEW_ROUND', NULL, A.BID_START_PRICE),
A.NOTE_TO_BIDDERS,
A.ATTACHMENT_FLAG,
A.LANGUAGE_CODE,
A.RESERVE_PRICE,
A.DISPLAY_TARGET_PRICE_FLAG,
A.CURRENT_PRICE,
A.TYPE,
A.LOT_LINE_NUMBER,
A.MIN_BID_INCREMENT,
A.MIN_BID_DECREMENT,
A.UNIT_OF_MEASURE,
A.PO_MIN_REL_AMOUNT,
A.HAS_ATTRIBUTES_FLAG,
A.TRANSPORTATION_ORIGIN,
A.TRANSPORTATION_DEST,
A.MULTIPLE_PRICES_FLAG,
A.TBD_PRICING_FLAG,
A.NEED_BY_START_DATE,
A.FREIGHT_TERMS_CODE,
A.MODIFIED_FLAG,
A.ORG_ID,
A.HAS_PRICE_ELEMENTS_FLAG,
A.LINE_TYPE_ID,
A.ORDER_TYPE_LOOKUP_CODE,
A.LINE_ORIGINATION_CODE,
A.REQUISITION_NUMBER,
A.ITEM_REVISION,
A.ITEM_ID,
A.ITEM_NUMBER,
A.PRICE_BREAK_TYPE,
A.PRICE_BREAK_NEG_FLAG,
A.HAS_SHIPMENTS_FLAG,
A.SOURCE_DOC_NUMBER,
A.SOURCE_LINE_NUMBER,
A.SOURCE_DOC_ID,
A.SOURCE_LINE_ID,
A.PRICE_DISABLED_FLAG,
A.QUANTITY_DISABLED_FLAG,
A.JOB_ID,
A.ADDITIONAL_JOB_DETAILS,
A.PO_AGREED_AMOUNT,
A.HAS_PRICE_DIFFERENTIALS_FLAG,
A.DIFFERENTIAL_RESPONSE_TYPE,
A.PURCHASE_BASIS,
A.IS_QUANTITY_SCORED,
A.IS_NEED_BY_DATE_SCORED,
A.LAST_AMENDMENT_UPDATE,
A.MODIFIED_DATE,
A.PRICE_DIFF_SHIPMENT_NUMBER,
NVL(A.GROUP_TYPE, 'LINE'), -- Lot based project default logic
A.PARENT_LINE_NUMBER,
A.DOCUMENT_DISP_LINE_NUMBER,
A.MAX_SUB_LINE_SEQUENCE_NUMBER,
A.SUB_LINE_SEQUENCE_NUMBER,
A.HAS_BUYER_PFS_FLAG,
A.UNIT_TARGET_PRICE,
A.UNIT_DISPLAY_TARGET_FLAG,
DECODE(NVL(COUNTER.C1, 0), 0,'N','Y') HAD_OBSOLETED_PE,
DECODE(NVL(AA.C2,0),0,'N','Y') HAD_OBSOLETE_ATTR_GROUP,
DECODE(NVL(COUNTER.NUM_BUYER_PFS, 0), 0, 'N', 'Y') HAS_ACTIVE_BUYER_PE_FLAG,
DECODE(NVL(COUNTER.NUM_SUPPLIER_PFS, 0), 0, 'N', 'Y') HAS_ACTIVE_SUPPLIER_PE_FLAG,
A.HAS_PAYMENTS_FLAG,
A.ADVANCE_AMOUNT,
A.RECOUPMENT_RATE_PERCENT,
A.PROGRESS_PYMT_RATE_PERCENT,
A.RETAINAGE_RATE_PERCENT,
A.MAX_RETAINAGE_AMOUNT,
A.WORK_APPROVER_USER_ID,
A.PROJECT_ID,
A.PROJECT_TASK_ID,
A.PROJECT_AWARD_ID,
A.PROJECT_EXPENDITURE_TYPE,
A.PROJECT_EXP_ORGANIZATION_ID,
A.PROJECT_EXPENDITURE_ITEM_DATE,
decode(l_copy_close_bidding_date, 'Y', A.CLOSE_BIDDING_DATE,null),
A.HAS_QUANTITY_TIERS,
/***********************************************************
* CLM - Clin Slin project Changes Start.
*********************************************************/
A.LINE_NUM_DISPLAY ,
A.GROUP_LINE_ID,
A.CLM_INFO_FLAG,
A.CLM_OPTION_INDICATOR,
A.CLM_BASE_LINE_NUM,
A.CLM_OPTION_NUM,
A.CLM_OPTION_FROM_DATE,
A.CLM_OPTION_TO_DATE,
A.CLM_FUNDED_FLAG,
/***********************************************************
* CLM - Clin Slin project Changes End.
*********************************************************/
A.UDA_TEMPLATE_ID,
A.CLM_CONTRACT_TYPE, -- Fed Fields Project
A.CLM_IDC_TYPE, -- Fed Fields Project
A.CLM_COST_CONSTRAINT,
A.CLM_UNIT_PRICE,
A.CLM_AMOUNT,
A.CLM_NEED_BY_DATE,
-- CLM - Event Based Delivery Project
A.CLM_DELIVERY_EVENT_CODE,
A.CLM_DELIVERY_PERIOD,
A.CLM_DELIVERY_PERIOD_UOM,
A.CLM_POP_DURATION,
A.CLM_POP_DURATION_UOM,
A.EXHIBIT_NUMBER
BULK COLLECT
INTO
l_disp_line_number,
l_amendment_update,
l_line_number,
l_item_description,
l_category_id,
--
-- The category name is not revalidated from the mtl table so it will have the
-- older name set in the source doc
--
l_category_name,
l_ip_category_id,
l_uom_code,
l_quantity,
l_need_by_date, -- need to change?
l_ship_to_location_id,
l_target_price,
l_threshold_price,
l_bid_start_price,
l_note_to_bidders,
l_attachment_flag,
l_language_code,
l_reserve_price,
l_display_target_price_flag,
l_current_price,
l_type,
l_lot_line_number,
l_min_bid_increment,
l_min_bid_decrement,
l_unit_of_measure,
l_po_min_rel_amount,
l_has_attributes_flag,
l_transportation_origin,
l_transportation_dest,
l_multiple_prices_flag,
l_tbd_pricing_flag,
l_need_by_start_date,
l_freight_terms_code,
l_modified_flag,
l_org_id,
l_has_price_elements_flag,
l_line_type_id,
l_order_type_lookup_code,
l_line_origination_code,
l_requisition_number,
l_item_revision,
l_item_id,
l_item_number,
l_price_break_type,
l_price_break_neg_flag,
l_has_shipments_flag,
l_source_doc_number,
l_source_line_number,
l_souce_doc_id,
l_source_line_id,
l_price_disabled_flag,
l_quantity_disabled_flag,
l_job_id,
l_additional_job_details,
l_po_agreed_amount,
l_has_price_differentials_flag,
l_differential_response_type,
l_purchase_basis,
l_is_quantity_scored,
l_is_need_by_date_scored,
l_last_amendment_update,
l_modified_date,
l_price_diff_shipment_number,
l_group_type, -- Lot project related columns start
l_parent_line_number,
l_document_disp_line_number,
l_max_sub_line_sequence_number,
l_sub_line_sequence_number,
l_has_buyer_pfs_flag, -- Transformation project related columns start
l_unit_target_price,
l_unit_display_target_flag,
l_had_obsoleted_pe, -- It will return Y if there are any disabled Price Element
l_had_obsolete_attr_group, -- It will return Y if there are any disabled Attributes
l_has_active_buyer_pe_flag, -- It will return Y if there are any active buyer price elements
l_has_active_supplier_pe_flag, -- It will return Y if there are any active supplier price elements
l_has_payments_flag,
l_advance_amount,
l_recoupment_rate_percent,
l_progress_pymt_rate_percent,
l_retainage_rate_percent,
l_max_retainage_amount,
l_work_approver_user_id,
l_project_id,
l_project_task_id,
l_project_award_id,
l_project_expenditure_type,
l_project_exp_organization_id,
l_project_exp_item_date,
l_close_bidding_date,
l_has_quantity_tiers_flag,
--- CLM - Clin Slin project Changes Start.
l_line_num_display,
l_group_line_id,
l_clm_info_flag,
l_clm_option_indicator,
l_clm_base_line_num,
l_clm_option_num,
l_clm_option_from_date,
l_clm_option_to_date,
l_clm_funded_flag,
--- CLM - Clin Slin project Changes End.
--
l_uda_template_id,
l_clm_contract_type, -- Fed Fields Project
l_clm_idc_type, -- Fed Fields Project
l_clm_cost_constraint,
l_clm_unit_price,
l_clm_amount,
l_clm_need_by_date,
-- CLM - Event Based Delivery Project
l_clm_delivery_event_code,
l_clm_delivery_period,
l_clm_delivery_period_uom,
l_clm_pop_duration,
l_clm_pop_duration_uom,
l_exhibit_number
FROM PON_AUCTION_ITEM_PRICES_ALL A,
(SELECT sum(decode(VL.ENABLED_FLAG,'N',1,0)) C1,
sum(decode(VL.ENABLED_FLAG,'Y',decode(P.PF_TYPE,'BUYER',1,0),0)) NUM_BUYER_PFS,
sum(decode(VL.ENABLED_FLAG,'Y',decode(P.PF_TYPE,'SUPPLIER',decode(P.PRICE_ELEMENT_TYPE_ID,-10,0,1),0),0)) NUM_SUPPLIER_PFS,
AUCTION_HEADER_ID,
LINE_NUMBER
FROM PON_PRICE_ELEMENTS P, PON_PRICE_ELEMENT_TYPES VL
WHERE P.PRICE_ELEMENT_TYPE_ID = VL.PRICE_ELEMENT_TYPE_ID
GROUP BY AUCTION_HEADER_ID, LINE_NUMBER) COUNTER,
(SELECT COUNT(ATTRIBUTE_NAME) C2,
AUCTION_HEADER_ID,
LINE_NUMBER
FROM PON_AUCTION_ATTRIBUTES,
FND_LOOKUP_VALUES
WHERE
LOOKUP_TYPE = 'PON_LINE_ATTRIBUTE_GROUPS'
AND (ENABLED_FLAG = 'N'
OR
SYSDATE NOT BETWEEN NVL(START_DATE_ACTIVE,SYSDATE - 1 )
AND NVL(END_DATE_ACTIVE,SYSDATE + 1 ))
AND ATTR_GROUP = LOOKUP_CODE
AND ATTR_LEVEL = 'LINE'
AND VIEW_APPLICATION_ID = 0
AND SECURITY_GROUP_ID = 0
GROUP BY AUCTION_HEADER_ID, LINE_NUMBER)AA
WHERE A.AUCTION_HEADER_ID = p_source_auction_header_id
AND A.AUCTION_HEADER_ID = COUNTER.AUCTION_HEADER_ID (+)
AND A.LINE_NUMBER = COUNTER.LINE_NUMBER (+)
AND A.AUCTION_HEADER_ID = AA.AUCTION_HEADER_ID (+)
AND A.LINE_NUMBER = AA.LINE_NUMBER (+)
AND A.line_number >= p_from_line_number
AND A.line_number <= p_to_line_number
ORDER BY A.DISP_LINE_NUMBER;
l_last_amendment_update(x) := 0;
l_last_amendment_update(x) := p_last_amendment_number + 1;
l_last_amendment_update(x) := p_last_amendment_number;
select decode(count(attribute_name), 0, 'N', 'Y')
into l_has_descriptors
from pon_auction_attributes
where auction_header_id = p_source_auction_header_id and
line_number = l_line_number(x) and
ip_category_id is not null and
rownum = 1;
l_last_amendment_update(x) := p_last_amendment_number + 1;
l_last_amendment_update(x) := p_last_amendment_number;
select auction_header_id_prev_amend
into l_source_prev_amend_id
from pon_auction_headers_all
where auction_header_id = p_source_auction_header_id;
(SELECT ATTACHED_DOCUMENT_ID,PK1_VALUE,PK2_VALUE
FROM FND_ATTACHED_DOCUMENTS
WHERE pk1_value = to_char(p_source_auction_header_id)
AND pk2_value = to_char(l_line_number(x))
AND ENTITY_NAME = 'PON_AUCTION_ITEM_PRICES_ALL'
AND category_id <> (SELECT category_id FROM fnd_document_categories_vl
WHERE NAME = 'PonAmendmentSpecific')
UNION
SELECT ATTACHED_DOCUMENT_ID,PK1_VALUE,PK2_VALUE
FROM FND_ATTACHED_DOCUMENTS
WHERE pk1_value = To_Char(l_source_prev_amend_id)
AND pk2_value = to_char(l_line_number(x))
AND ENTITY_NAME = 'PON_AUCTION_ITEM_PRICES_ALL'
AND ATTACHED_DOCUMENT_ID NOT IN
(SELECT Nvl(ORIG_ATTACH_DOC_ID,-1) FROM FND_ATTACHED_DOCUMENTS
WHERE pk1_value = to_char(p_source_auction_header_id))) LOOP
FND_ATTACHED_DOCUMENTS2_PKG.COPY_ATTACHMENTS (
X_from_entity_name => 'PON_AUCTION_ITEM_PRICES_ALL',
X_from_pk1_value => base_attach_rec.pk1_value,
X_from_pk2_value => base_attach_rec.pk2_value,
X_to_entity_name => 'PON_AUCTION_ITEM_PRICES_ALL',
X_to_pk1_value => to_char(p_auction_header_id), -- PK1_VALUE
X_to_pk2_value => to_char(l_line_number(x)), -- PK1_VALUE
X_created_by => p_user_id, -- CREATED_BY
X_last_update_login => fnd_global.login_id, -- LAST_UPDATE_LOGIN
X_orig_attach_doc_id => base_attach_rec.ATTACHED_DOCUMENT_ID
);
X_last_update_login => fnd_global.login_id -- LAST_UPDATE_LOGIN
);
INSERT
INTO PON_AUCTION_ITEM_PRICES_ALL
(AUCTION_HEADER_ID,
AWARD_STATUS,
LINE_NUMBER,
ITEM_DESCRIPTION,
CATEGORY_ID,
CATEGORY_NAME,
IP_CATEGORY_ID,
UOM_CODE,
QUANTITY,
NEED_BY_DATE,
SHIP_TO_LOCATION_ID,
NUMBER_OF_BIDS,
LOWEST_BID_PRICE,
LOWEST_BID_QUANTITY,
LOWEST_BID_PROMISED_DATE,
LOWEST_BID_NUMBER,
CLOSEST_PROMISED_DATE,
CLOSEST_BID_PRICE,
CLOSEST_BID_QUANTITY,
CLOSEST_BID_NUMBER,
TARGET_PRICE,
THRESHOLD_PRICE,
BID_START_PRICE,
NOTE_TO_BIDDERS,
ATTACHMENT_FLAG,
LANGUAGE_CODE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
AUCTION_CREATION_DATE,
CLOSE_BIDDING_DATE,
NUMBER_OF_EXTENSIONS,
RESERVE_PRICE,
DISPLAY_TARGET_PRICE_FLAG,
CURRENT_PRICE,
BEST_BID_PRICE,
BEST_BID_QUANTITY,
BEST_BID_PROMISED_DATE,
BEST_BID_NUMBER,
TYPE,
LOT_LINE_NUMBER,
MIN_BID_INCREMENT,
MIN_BID_DECREMENT,
BEST_BID_PROXY_LIMIT_PRICE,
BEST_BID_CURRENCY_PRICE,
BEST_BID_CURRENCY_CODE,
PO_MIN_REL_AMOUNT,
BEST_BID_FIRST_BID_PRICE,
UNIT_OF_MEASURE,
HAS_ATTRIBUTES_FLAG,
TRANSPORTATION_ORIGIN,
TRANSPORTATION_DEST,
AUCTION_HEADER_ID_ORIG_ROUND,
AUCTION_HEADER_ID_PREV_ROUND,
LINE_NUMBER_ORIGINAL_ROUND,
LINE_NUMBER_PREV_ROUND,
MULTIPLE_PRICES_FLAG,
RESIDUAL_QUANTITY,
PENDING_QUANTITY,
CANCEL_QUANTITY,
NUMBER_OF_COMMITMENTS,
NUMBER_OF_PENDING_COMMITMENTS,
TBD_PRICING_FLAG,
NEED_BY_START_DATE,
PRICE,
FREIGHT_TERMS_CODE,
AWARDED_QUANTITY,
MODIFIED_FLAG,
BEST_BID_BID_PRICE,
BEST_BID_SCORE,
BEST_BID_BID_NUMBER,
BEST_BID_BID_CURRENCY_PRICE,
BEST_BID_BID_CURRENCY_CODE,
ORG_ID,
HAS_PRICE_ELEMENTS_FLAG,
LINE_TYPE_ID,
ORDER_TYPE_LOOKUP_CODE,
LINE_ORIGINATION_CODE,
REQUISITION_NUMBER,
ITEM_REVISION,
ITEM_ID,
ITEM_NUMBER,
PRICE_BREAK_TYPE,
PRICE_BREAK_NEG_FLAG,
HAS_SHIPMENTS_FLAG,
SOURCE_DOC_NUMBER,
SOURCE_LINE_NUMBER,
SOURCE_DOC_ID,
SOURCE_LINE_ID,
ALLOCATION_STATUS,
PRICE_DISABLED_FLAG,
QUANTITY_DISABLED_FLAG,
JOB_ID,
ADDITIONAL_JOB_DETAILS,
PO_AGREED_AMOUNT,
HAS_PRICE_DIFFERENTIALS_FLAG,
DIFFERENTIAL_RESPONSE_TYPE,
PURCHASE_BASIS,
IS_QUANTITY_SCORED,
IS_NEED_BY_DATE_SCORED,
DISP_LINE_NUMBER,
LAST_UPDATE_LOGIN,
LAST_AMENDMENT_UPDATE,
MODIFIED_DATE,
PRICE_DIFF_SHIPMENT_NUMBER,
GROUP_TYPE,
PARENT_LINE_NUMBER,
DOCUMENT_DISP_LINE_NUMBER,
MAX_SUB_LINE_SEQUENCE_NUMBER,
SUB_LINE_SEQUENCE_NUMBER,
HAS_BUYER_PFS_FLAG,
UNIT_TARGET_PRICE,
UNIT_DISPLAY_TARGET_FLAG,
HAS_PAYMENTS_FLAG,
ADVANCE_AMOUNT,
RECOUPMENT_RATE_PERCENT,
PROGRESS_PYMT_RATE_PERCENT,
RETAINAGE_RATE_PERCENT,
MAX_RETAINAGE_AMOUNT,
WORK_APPROVER_USER_ID,
PROJECT_ID,
PROJECT_TASK_ID,
PROJECT_AWARD_ID,
PROJECT_EXPENDITURE_TYPE,
PROJECT_EXP_ORGANIZATION_ID,
PROJECT_EXPENDITURE_ITEM_DATE,
HAS_QUANTITY_TIERS,
--- CLM - Clin Slin project Changes Start.
LINE_NUM_DISPLAY,
GROUP_LINE_ID,
CLM_INFO_FLAG,
CLM_OPTION_INDICATOR,
CLM_BASE_LINE_NUM,
CLM_OPTION_NUM,
CLM_OPTION_FROM_DATE,
CLM_OPTION_TO_DATE,
CLM_FUNDED_FLAG,
--- CLM - Clin Slin project Changes End.
--
UDA_TEMPLATE_ID,
-- Fed Fields Project : Copy contract_type, idc_type
CLM_CONTRACT_TYPE,
CLM_IDC_TYPE,
CLM_COST_CONSTRAINT,
CLM_UNIT_PRICE,
CLM_AMOUNT,
CLM_NEED_BY_DATE,
-- CLM - Event Based Delivery Project
CLM_DELIVERY_EVENT_CODE,
CLM_DELIVERY_PERIOD,
CLM_DELIVERY_PERIOD_UOM,
CLM_POP_DURATION,
CLM_POP_DURATION_UOM,
EXHIBIT_NUMBER )
VALUES (
p_auction_header_id,
NULL, -- AWARD_STATUS, defaulted to NULL
l_line_number(x),
l_item_description(x),
l_category_id(x),
l_category_name(x),
l_ip_category_id(x),
--
-- UOM_CODE is carried as we only create RFI lines with Price and
-- Qunatity for cross-copy
--
l_uom_code(x),
l_quantity(x),
l_need_by_date(x), -- NEED_BY_DATE is carried over
l_ship_to_location_id(x),
NULL, -- NUMBER_OF_BIDS, defaulted to NULL
NULL, -- LOWEST_BID_PRICE, defaulted to NULL
NULL, -- LOWEST_BID_QUANTITY, defaulted to NULL
NULL, -- LOWEST_BID_PROMISED_DATE, defaulted to NULL
NULL, -- LOWEST_BID_NUMBER, defaulted to NULL
NULL, -- CLOSEST_PROMISED_DATE, defaulted to NULL
NULL, -- CLOSEST_BID_PRICE, defaulted to NULL
NULL, -- CLOSEST_BID_QUANTITY, defaulted to NULL
NULL, -- CLOSEST_BID_NUMBER, defaulted to NULL
l_target_price(x),
l_threshold_price(x),
l_bid_start_price(x),
l_note_to_bidders(x),
l_attachment_flag(x),
l_language_code(x),
SYSDATE, -- CREATION_DATE
p_user_id , -- CREATED_BY
SYSDATE, -- LAST_UPDATE_DATE
p_user_id, -- LAST_UPDATED_BY
NULL, -- AUCTION_CREATION_DATE
l_close_bidding_date(x), -- CLOSE_BIDDING_DATE, defaulted to NULL
NULL, -- NUMBER_OF_EXTENSIONS, defaulted to NULL
l_reserve_price(x), -- RESERVE_PRICE
l_display_target_price_flag(x),
l_current_price(x),
NULL, -- BEST_BID_PRICE, defaulted to NULL
NULL, -- BEST_BID_QUANTITY , defaulted to NULL
NULL, -- BEST_BID_PROMISED_DATE , defaulted to NULL
NULL, -- BEST_BID_NUMBER , defaulted to NULL
l_type(x), -- TYPE, seems NULL always
l_lot_line_number(x),
l_min_bid_increment(x), -- MIN_BID_INCREMENT keeping it
l_min_bid_decrement(x), -- MIN_BID_DECREMENT keeping it
NULL, -- BEST_BID_PROXY_LIMIT_PRICE, defaulted to NULL
NULL, -- BEST_BID_CURRENCY_PRICE, defaulted to NULL
NULL, -- BEST_BID_CURRENCY_CODE, defaulted to NULL
l_po_min_rel_amount(x), -- PO_MIN_REL_AMOUNT, only reset for copy to RFI
NULL, -- BEST_BID_FIRST_BID_PRICE, defaulted to NULL
l_unit_of_measure(x),
l_has_attributes_flag(x),
l_transportation_origin(x),
l_transportation_dest(x),
NULL, -- AUCTION_HEADER_ID_ORIG_ROUND, No need to take care, they're not used ?
NULL, -- AUCTION_HEADER_ID_PREV_ROUND, No need to take care, they're not used ?
NULL, -- LINE_NUMBER_ORIGINAL_ROUND, No need to take care, they're not used ?
NULL, -- LINE_NUMBER_PREV_ROUND, No need to take care, they're not used ?
l_multiple_prices_flag(x),
NULL, -- RESIDUAL_QUANTITY
NULL, -- PENDING_QUANTITY, defaulted to NULL
NULL, -- CANCEL_QUANTITY, defaulted to NULL
NULL, -- NUMBER_OF_COMMITMENTS, defaulted to NULL
NULL, -- NUMBER_OF_PENDING_COMMITMENTS, defaulted to NULL
l_tbd_pricing_flag(x),
l_need_by_start_date(x), -- NEED_BY_START_DATE
NULL, -- PRICE
l_freight_terms_code(x),
NULL, -- AWARDED_QUANTITY, defaulted to NULL
l_modified_flag(x), -- MODIFIED_FLAG
NULL, -- BEST_BID_BID_PRICE, defaulted to NULL
NULL, -- BEST_BID_SCORE, defaulted to NULL
NULL, -- BEST_BID_BID_NUMBER, defaulted to NULL
NULL, -- BEST_BID_BID_CURRENCY_PRICE, defaulted to NULL
NULL, -- BEST_BID_BID_CURRENCY_CODE, defaulted to NULL
l_org_id(x),
l_has_active_supplier_pe_flag(x),
l_line_type_id(x),
l_order_type_lookup_code(x), -- ORDER_TYPE_LOOKUP_CODE
l_line_origination_code(x), -- LINE_ORIGINATION_CODE
l_requisition_number(x), -- REQUISITION_NUMBER
l_item_revision(x),
l_item_id(x),
l_item_number(x),
l_price_break_type(x),
l_price_break_neg_flag(x),
l_has_shipments_flag(x),
l_source_doc_number(x) , -- SOURCE_DOC_NUMBER, defaulted to NULL for active copy
l_source_line_number(x) , -- SOURCE_LINE_NUMBER, defaulted to NULL for active copy
l_souce_doc_id(x) , -- SOURCE_DOC_ID, defaulted to NULL for active copy
l_source_line_id(x) , -- SOURCE_LINE_ID, defaulted to NULL for active copy
NULL, -- ALLOCATION_STATUS, defaulted to NULL
l_price_disabled_flag(x),
l_quantity_disabled_flag(x),
l_job_id(x),
l_additional_job_details(x),
l_po_agreed_amount(x), -- PO_AGREED_AMOUNT, keeping this except for active copy
decode( g_auc_doctype_rule_data.ALLOW_PRICE_DIFFERENTIAL,
'Y', l_has_price_differentials_flag(x),
'N'), -- HAS_PRICE_DIFFERENTIALS_FLAG
l_differential_response_type(x),
l_purchase_basis(x),
l_is_quantity_scored(x),
l_is_need_by_date_scored(x),
l_disp_line_number(x),
p_user_id,
l_last_amendment_update(x),
l_modified_date(x),
l_price_diff_shipment_number(x),
l_group_type(x), -- Lot based project related columns
l_parent_line_number(x),
l_document_disp_line_number(x), -- Is it properly set?
--decode(l_is_succession, 'Y', l_max_sub_line_sequence_number(x), null),
/*Bug : 13934023 : Copy this field otherwise modified_flag is not updated for lines modified
when new round Auction/RFQ is created from RFQ/Auction */
l_max_sub_line_sequence_number(x),
l_sub_line_sequence_number(x),
l_has_active_buyer_pe_flag(x), -- Transformation project related columns
l_unit_target_price(x),
l_unit_display_target_flag(x),
l_has_payments_flag (x),
l_advance_amount (x),
l_recoupment_rate_percent(x),
l_progress_pymt_rate_percent(x),
l_retainage_rate_percent(x),
l_max_retainage_amount(x),
l_work_approver_user_id(x),
l_project_id(x),
l_project_task_id(x),
l_project_award_id(x),
l_project_expenditure_type(x),
l_project_exp_organization_id(x),
l_project_exp_item_date(x),
l_has_quantity_tiers_flag(x),
--- CLM - Clin Slin project Changes Start.
l_line_num_display(x),
l_group_line_id(x),
l_clm_info_flag(x),
l_clm_option_indicator(x),
l_clm_base_line_num(x),
l_clm_option_num(x),
l_clm_option_from_date(x),
l_clm_option_to_date(x),
l_clm_funded_flag(x),
--- CLM - Clin Slin project Changes End.
--
--Fed Fields Project
l_uda_template_id(x),
l_clm_contract_type(x),
l_clm_idc_type(x),
l_clm_cost_constraint(x),
l_clm_unit_price(x),
l_clm_amount(x),
l_clm_need_by_date(x),
-- CLM - Event Based Delivery Project
l_clm_delivery_event_code(x),
l_clm_delivery_period(x),
l_clm_delivery_period_uom(x),
l_clm_pop_duration(x),
l_clm_pop_duration_uom(x),
l_exhibit_number(x)
);
UPDATE PON_AUCTION_HEADERS_ALL
SET GLOBAL_AGREEMENT_FLAG = 'Y'
WHERE AUCTION_HEADER_ID = p_auction_header_id;
insert into pon_auction_sections
( AUCTION_HEADER_ID,
LINE_NUMBER,
ATTRIBUTE_LIST_ID,
SECTION_ID,
PREVIOUS_SECTION_ID,
ATTR_GROUP_SEQ_NUMBER,
SECTION_NAME,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
TWO_PART_SECTION_TYPE)
select
p_auction_header_id,
LINE_NUMBER,
ATTRIBUTE_LIST_ID,
PON_AUCTION_SECTIONS_S.NEXTVAL,
-- Team Scoring
-- Commenting the following line to allow copying of sections
-- even in case of cross copy or draft copy as previous section
-- id is needed while copying team assignments on the new document
-- decode(p_copy_type, g_new_rnd_copy,SECTION_ID, g_amend_copy, SECTION_ID,null),
--
SECTION_ID,
ATTR_GROUP_SEQ_NUMBER,
SECTION_NAME,
SYSDATE,
p_user_id,
SYSDATE,
p_user_id,
p_user_id,
DECODE(p_doctype_id, g_rfq_doctype_id, TWO_PART_SECTION_TYPE, g_sol_doctype_id, TWO_PART_SECTION_TYPE, NULL)
FROM PON_AUCTION_SECTIONS
WHERE AUCTION_HEADER_ID = p_source_auction_header_id;
l_last_amendment_update PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
SELECT
LINE_NUMBER,
ATTRIBUTE_NAME,
DESCRIPTION,
DATATYPE ,
MANDATORY_FLAG,
VALUE,
DISPLAY_PROMPT,
HELP_TEXT,
DISPLAY_TARGET_FLAG,
ATTRIBUTE_LIST_ID,
DISPLAY_ONLY_FLAG,
SEQUENCE_NUMBER,
COPIED_FROM_CAT_FLAG,
WEIGHT,
SCORING_TYPE,
ATTR_LEVEL ,
ATTR_GROUP,
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,
IS_OBSOLETE_ATTRIBUTE,
SECTION_NAME,
KNOCKOUT_SCORE,
SCORING_METHOD
BULK COLLECT
INTO
l_line_number,
l_attribute_name,
l_description,
l_datatype,
l_mandatory_flag,
l_value,
l_display_prompt,
l_help_text,
l_display_target_flag,
l_attribute_list_id,
l_display_only_flag,
l_sequence_number,
l_copied_from_cat_flag,
l_weight,
l_scoring_type,
l_attr_level,
l_attr_group,
l_attr_max_score,
l_internal_attr_flag,
l_attr_group_seq_number,
l_attr_disp_seq_number,
l_modified_flag,
l_modified_date,
l_last_amendment_update,
l_ip_category_id,
l_ip_descriptor_id,
l_is_obsolete_attribute,
l_section_name,
l_knockout_score,
l_scoring_method
FROM
(SELECT P.LINE_NUMBER,
P.ATTRIBUTE_NAME,
P.DESCRIPTION,
P.DATATYPE ,
P.MANDATORY_FLAG,
P.VALUE,
P.DISPLAY_PROMPT,
P.HELP_TEXT,
P.DISPLAY_TARGET_FLAG,
P.ATTRIBUTE_LIST_ID,
P.DISPLAY_ONLY_FLAG,
P.SEQUENCE_NUMBER,
P.COPIED_FROM_CAT_FLAG,
P.WEIGHT,
P.SCORING_TYPE,
P.ATTR_LEVEL ,
P.ATTR_GROUP,
P.ATTR_MAX_SCORE,
P.INTERNAL_ATTR_FLAG,
P.ATTR_GROUP_SEQ_NUMBER,
P.ATTR_DISP_SEQ_NUMBER ,
P.MODIFIED_FLAG,
P.MODIFIED_DATE,
P.LAST_AMENDMENT_UPDATE,
P.IP_CATEGORY_ID,
P.IP_DESCRIPTOR_ID,
'N' AS IS_OBSOLETE_ATTRIBUTE,
P.SECTION_NAME,
P.KNOCKOUT_SCORE,
P.SCORING_METHOD
FROM PON_AUCTION_ATTRIBUTES P
WHERE P.AUCTION_HEADER_ID = p_source_auction_header_id
AND P.ATTR_LEVEL = 'HEADER'
AND g_neg_style_control.hdr_attribute_enabled_flag = 'Y'
--copy the header attributes only once
--so we need the below where condition
--AND p_from_line_number = 1
GROUP BY P.LINE_NUMBER, P.ATTRIBUTE_NAME, P.DESCRIPTION, P.DATATYPE,
P.MANDATORY_FLAG, P.VALUE, P.DISPLAY_PROMPT, P.HELP_TEXT, P.DISPLAY_TARGET_FLAG,
P.ATTRIBUTE_LIST_ID, P.DISPLAY_ONLY_FLAG, P.SEQUENCE_NUMBER, P.COPIED_FROM_CAT_FLAG,
P.WEIGHT, P.SCORING_TYPE, P.ATTR_LEVEL, P.ATTR_GROUP, P.ATTR_MAX_SCORE, P.INTERNAL_ATTR_FLAG,
P.ATTR_GROUP_SEQ_NUMBER, P.ATTR_DISP_SEQ_NUMBER, P.MODIFIED_FLAG, P.MODIFIED_DATE,
P.LAST_AMENDMENT_UPDATE, P.IP_CATEGORY_ID, P.IP_DESCRIPTOR_ID,P.SECTION_NAME,P.KNOCKOUT_SCORE,P.SCORING_METHOD
);
l_last_amendment_update(x) := 0;
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,
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,
SECTION_NAME,
KNOCKOUT_SCORE,
SCORING_METHOD)
VALUES
(p_auction_header_id,
l_line_number(x),
l_attribute_name(x),
l_description(x),
l_datatype(x),
l_mandatory_flag(x),
l_value(x),
l_display_prompt(x),
l_help_text(x),
l_display_target_flag(x),
SYSDATE, -- CREATION_DATE
p_user_id, -- CREATED_BY
SYSDATE, -- LAST_UPDATE_DATE
p_user_id, -- LAST_UPDATED_BY
l_attribute_list_id(x),
l_display_only_flag(x),
l_sequence_number(x),
l_copied_from_cat_flag(x),
l_weight(x),
l_scoring_type(x),
l_attr_level(x),
l_attr_group(x),
l_attr_max_score(x),
l_internal_attr_flag(x),
l_attr_group_seq_number(x),
l_attr_disp_seq_number(x),
l_modified_flag(x),
l_modified_date(x),
l_last_amendment_update(x),
l_ip_category_id(x),
l_ip_descriptor_id(x),
l_section_name(x),
l_knockout_score(x),
l_scoring_method(x));
l_last_amendment_update PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
SELECT 'Y'
INTO l_temp
FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = 'PON_LINE_ATTRIBUTE_GROUPS'
AND LOOKUP_CODE = l_val1
AND (ENABLED_FLAG = 'N' OR
SYSDATE NOT BETWEEN
NVL(START_DATE_ACTIVE,SYSDATE-1) AND
NVL(END_DATE_ACTIVE,SYSDATE+1));
SELECT MEANING
INTO l_val2
FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = 'PON_LINE_ATTRIBUTE_GROUPS'
AND LOOKUP_CODE = l_val1;
SELECT
LINE_NUMBER,
ATTRIBUTE_NAME,
DESCRIPTION,
DATATYPE ,
MANDATORY_FLAG,
VALUE,
DISPLAY_PROMPT,
HELP_TEXT,
DISPLAY_TARGET_FLAG,
ATTRIBUTE_LIST_ID,
DISPLAY_ONLY_FLAG,
SEQUENCE_NUMBER,
COPIED_FROM_CAT_FLAG,
WEIGHT,
SCORING_TYPE,
ATTR_LEVEL ,
ATTR_GROUP,
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,
IS_OBSOLETE_ATTRIBUTE,
SECTION_NAME,
KNOCKOUT_SCORE,
SCORING_METHOD
BULK COLLECT
INTO
l_line_number,
l_attribute_name,
l_description,
l_datatype,
l_mandatory_flag,
l_value,
l_display_prompt,
l_help_text,
l_display_target_flag,
l_attribute_list_id,
l_display_only_flag,
l_sequence_number,
l_copied_from_cat_flag,
l_weight,
l_scoring_type,
l_attr_level,
l_attr_group,
l_attr_max_score,
l_internal_attr_flag,
l_attr_group_seq_number,
l_attr_disp_seq_number,
l_modified_flag,
l_modified_date,
l_last_amendment_update,
l_ip_category_id,
l_ip_descriptor_id,
l_is_obsolete_attribute,
l_section_name,
l_knockout_score,
l_scoring_method
FROM
(
SELECT P.LINE_NUMBER,
P.ATTRIBUTE_NAME,
P.DESCRIPTION,
P.DATATYPE ,
P.MANDATORY_FLAG,
P.VALUE,
P.DISPLAY_PROMPT,
P.HELP_TEXT,
P.DISPLAY_TARGET_FLAG,
P.ATTRIBUTE_LIST_ID,
P.DISPLAY_ONLY_FLAG,
P.SEQUENCE_NUMBER,
P.COPIED_FROM_CAT_FLAG,
P.WEIGHT,
P.SCORING_TYPE,
P.ATTR_LEVEL ,
P.ATTR_GROUP,
P.ATTR_MAX_SCORE,
P.INTERNAL_ATTR_FLAG,
P.ATTR_GROUP_SEQ_NUMBER,
P.ATTR_DISP_SEQ_NUMBER ,
P.MODIFIED_FLAG,
P.MODIFIED_DATE,
P.LAST_AMENDMENT_UPDATE,
P.IP_CATEGORY_ID,
P.IP_DESCRIPTOR_ID,
DECODE(NVL(COUNT(LOOKUP_CODE),0),0,'N','Y') IS_OBSOLETE_ATTRIBUTE,
P.SECTION_NAME,
P.KNOCKOUT_SCORE,
P.SCORING_METHOD
FROM PON_AUCTION_ATTRIBUTES P,
(SELECT LOOKUP_CODE
FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = 'PON_LINE_ATTRIBUTE_GROUPS'
AND (ENABLED_FLAG = 'N'
OR SYSDATE NOT BETWEEN NVL(START_DATE_ACTIVE,SYSDATE - 1 )
AND NVL(END_DATE_ACTIVE,SYSDATE + 1 ))
) A
WHERE P.AUCTION_HEADER_ID = p_source_auction_header_id
AND P.ATTR_GROUP = A.LOOKUP_CODE(+)
AND P.ATTR_LEVEL = 'LINE'
AND g_neg_style_control.line_attribute_enabled_flag = 'Y'
AND (P.SEQUENCE_NUMBER > -1 OR g_neg_style_control.line_mas_enabled_flag = 'Y')
AND (P.IP_CATEGORY_ID is null OR (P.IP_CATEGORY_ID is not null and p_doctype_id <> g_rfi_doctype_id))
AND P.line_number >= p_from_line_number
AND P.line_number <= p_to_line_number
GROUP BY P.LINE_NUMBER, P.ATTRIBUTE_NAME, P.DESCRIPTION, P.DATATYPE,
P.MANDATORY_FLAG, P.VALUE, P.DISPLAY_PROMPT, P.HELP_TEXT, P.DISPLAY_TARGET_FLAG,
P.ATTRIBUTE_LIST_ID, P.DISPLAY_ONLY_FLAG, P.SEQUENCE_NUMBER, P.COPIED_FROM_CAT_FLAG,
P.WEIGHT, P.SCORING_TYPE, P.ATTR_LEVEL, P.ATTR_GROUP, P.ATTR_MAX_SCORE, P.INTERNAL_ATTR_FLAG,
P.ATTR_GROUP_SEQ_NUMBER, P.ATTR_DISP_SEQ_NUMBER, P.MODIFIED_FLAG, P.MODIFIED_DATE,
P.LAST_AMENDMENT_UPDATE, P.IP_CATEGORY_ID, P.IP_DESCRIPTOR_ID,P.SECTION_NAME,P.KNOCKOUT_SCORE,P.SCORING_METHOD);
l_last_amendment_update(x) := 0;
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,
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,
SECTION_NAME,
KNOCKOUT_SCORE,
SCORING_METHOD)
VALUES
(p_auction_header_id,
l_line_number(x),
l_attribute_name(x),
l_description(x),
l_datatype(x),
l_mandatory_flag(x),
l_value(x),
l_display_prompt(x),
l_help_text(x),
l_display_target_flag(x),
SYSDATE, -- CREATION_DATE
p_user_id, -- CREATED_BY
SYSDATE, -- LAST_UPDATE_DATE
p_user_id, -- LAST_UPDATED_BY
l_attribute_list_id(x),
l_display_only_flag(x),
l_sequence_number(x),
l_copied_from_cat_flag(x),
l_weight(x),
l_scoring_type(x),
l_attr_level(x),
l_attr_group(x),
l_attr_max_score(x),
l_internal_attr_flag(x),
l_attr_group_seq_number(x),
l_attr_disp_seq_number(x),
l_modified_flag(x),
l_modified_date(x),
l_last_amendment_update(x),
l_ip_category_id(x),
l_ip_descriptor_id(x),
l_section_name(x),
l_knockout_score(x),
l_scoring_method(x));
SELECT contract_type
INTO l_contract_type
FROM pon_auction_headers_all
WHERE auction_header_id = p_auction_header_id;
DELETE FROM pon_auction_attributes paa
WHERE paa.auction_header_id = p_auction_header_id and
paa.attr_level = 'LINE' and
paa.ip_category_id is not null and
not exists (select null
from icx_cat_agreement_attrs_v
where rt_category_id = paa.ip_category_id and
attribute_id = paa.ip_descriptor_id and
language = userenv('LANG'));
UPDATE pon_auction_attributes paa
SET attribute_name = (select attribute_name
from icx_cat_agreement_attrs_v
where rt_category_id = paa.ip_category_id and
attribute_id = paa.ip_descriptor_id and
language = userenv('LANG'))
WHERE paa.auction_header_id = p_auction_header_id and
paa.attr_level = 'LINE' and
paa.ip_category_id is not null;
INSERT
INTO PON_ATTRIBUTE_SCORES
( AUCTION_HEADER_ID,
LINE_NUMBER,
ATTRIBUTE_SEQUENCE_NUMBER,
VALUE,
FROM_RANGE,
TO_RANGE,
SCORE,
ATTRIBUTE_LIST_ID,
SEQUENCE_NUMBER,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
)
(SELECT
p_auction_header_id,
pas.LINE_NUMBER,
pas.ATTRIBUTE_SEQUENCE_NUMBER,
pas.VALUE,
pas.FROM_RANGE,
pas.TO_RANGE,
pas.SCORE,
pas.ATTRIBUTE_LIST_ID,
pas.SEQUENCE_NUMBER,
SYSDATE, -- CREATION_DATE
p_user_id, -- CREATED_BY
SYSDATE, -- LAST_UPDATE_DATE
p_user_id -- LAST_UPDATED_BY
FROM PON_ATTRIBUTE_SCORES pas,
PON_AUCTION_ATTRIBUTES paa
WHERE pas.AUCTION_HEADER_ID = p_source_auction_header_id
AND pas.auction_header_id = paa.auction_header_id
AND pas.line_number = paa.line_number
AND paa.attr_level = 'HEADER'
AND pas.attribute_sequence_number = paa.sequence_number
AND g_neg_style_control.hdr_attribute_enabled_flag = 'Y'
) ;
INSERT
INTO PON_ATTRIBUTE_SCORES
( AUCTION_HEADER_ID,
LINE_NUMBER,
ATTRIBUTE_SEQUENCE_NUMBER,
VALUE,
FROM_RANGE,
TO_RANGE,
SCORE,
ATTRIBUTE_LIST_ID,
SEQUENCE_NUMBER,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
)
(SELECT
p_auction_header_id,
pas.LINE_NUMBER,
pas.ATTRIBUTE_SEQUENCE_NUMBER,
pas.VALUE,
pas.FROM_RANGE,
pas.TO_RANGE,
pas.SCORE,
pas.ATTRIBUTE_LIST_ID,
pas.SEQUENCE_NUMBER,
SYSDATE, -- CREATION_DATE
p_user_id, -- CREATED_BY
SYSDATE, -- LAST_UPDATE_DATE
p_user_id -- LAST_UPDATED_BY
FROM PON_ATTRIBUTE_SCORES pas,
PON_AUCTION_ATTRIBUTES paa
WHERE pas.AUCTION_HEADER_ID = p_source_auction_header_id
AND pas.auction_header_id = paa.auction_header_id
AND pas.line_number = paa.line_number
AND paa.attr_level = 'LINE'
AND pas.attribute_sequence_number = paa.sequence_number
AND g_neg_style_control.line_mas_enabled_flag = 'Y'
AND pas.line_number >= p_from_line_number
AND pas.line_number <= p_to_line_number
) ;
LOG_MESSAGE('copy_price_diff','inserting rows into PON_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,
LAST_UPDATE_LOGIN
)
(SELECT
p_auction_header_id,
LINE_NUMBER,
SHIPMENT_NUMBER,
PRICE_DIFFERENTIAL_NUMBER,
PRICE_TYPE,
MULTIPLIER,
SYSDATE, -- CREATION_DATE
p_user_id, -- CREATED_BY
SYSDATE, -- LAST_UPDATE_DATE
p_user_id, -- LAST_UPDATED_BY
p_user_id -- LAST_UPDATE_LOGIN
FROM PON_PRICE_DIFFERENTIALS
WHERE AUCTION_HEADER_ID = p_source_auction_header_id
AND LINE_NUMBER >= p_from_line_number
AND LINE_NUMBER <= p_to_line_number) ;
SELECT DISTINCT
psps.auction_header_id source_auc_id,
psps.line_number source_line_number,
psps.payment_id source_payment_id,
paps.auction_header_id dest_auc_id,
paps.line_number dest_line_number,
paps.payment_id dest_payment_id
FROM
PON_AUC_PAYMENTS_SHIPMENTS paps,
FND_ATTACHED_DOCUMENTS fnd,
PON_AUC_PAYMENTS_SHIPMENTS psps
WHERE psps.auction_header_id = p_source_auction_header_id
AND paps.auction_header_id = p_auction_header_id
AND paps.line_number = psps.line_number
AND paps.payment_display_number = psps.payment_display_number
AND fnd.pk1_value = to_char(psps.auction_header_id)
AND fnd.pk2_value = to_char(psps.line_number)
AND fnd.pk3_value = to_char(psps.payment_id)
AND fnd.entity_name like 'PON_AUC_PAYMENTS_SHIPMENTS%'
AND psps.line_number >= p_from_line_number
AND psps.line_number <= p_to_line_number;
g_err_loc := '1. Before inserting into pon_auc_payments_shipments';
LOG_MESSAGE('copy_negotiation','Before Insert into pon_auc_payments_shipments');
INSERT INTO
PON_AUC_PAYMENTS_SHIPMENTS
( AUCTION_HEADER_ID,
PAYMENT_ID,
LINE_NUMBER,
PAYMENT_DISPLAY_NUMBER,
PAYMENT_TYPE_CODE,
PAYMENT_DESCRIPTION,
SHIP_TO_LOCATION_ID,
QUANTITY,
UOM_CODE,
TARGET_PRICE,
NEED_BY_DATE,
WORK_APPROVER_USER_ID,
NOTE_TO_BIDDERS,
PROJECT_ID,
PROJECT_TASK_ID,
PROJECT_AWARD_ID,
PROJECT_EXPENDITURE_TYPE,
PROJECT_EXP_ORGANIZATION_ID,
PROJECT_EXPENDITURE_ITEM_DATE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
(SELECT
p_auction_header_id,
PON_AUC_PAYMENTS_SHIPMENTS_S1.NEXTVAL,
LINE_NUMBER,
PAYMENT_DISPLAY_NUMBER,
PAYMENT_TYPE_CODE,
PAYMENT_DESCRIPTION,
SHIP_TO_LOCATION_ID,
QUANTITY,
UOM_CODE,
TARGET_PRICE,
NEED_BY_DATE,
WORK_APPROVER_USER_ID,
NOTE_TO_BIDDERS,
PROJECT_ID,
PROJECT_TASK_ID,
PROJECT_AWARD_ID,
PROJECT_EXPENDITURE_TYPE,
PROJECT_EXP_ORGANIZATION_ID,
PROJECT_EXPENDITURE_ITEM_DATE,
SYSDATE,
p_user_id,
SYSDATE,
p_user_id,
fnd_global.login_id
FROM pon_auc_payments_shipments
WHERE auction_header_id = p_source_auction_header_id
AND line_number >= p_from_line_number
AND line_number <= p_to_line_number
);
g_err_loc := '2. After inserting into pon_auc_payments_shipments';
LOG_MESSAGE('copy_negotiation','After Insert into pon_auc_payments_shipments');
(SELECT ENTITY_NAME,ATTACHED_DOCUMENT_ID,PK1_VALUE,PK2_VALUE, PK3_VALUE
FROM FND_ATTACHED_DOCUMENTS
WHERE pk1_value = to_char(p_source_auction_header_id)
AND pk2_value = to_char(payment_line_rec.source_line_number)
AND pk3_value = to_char(payment_line_rec.source_payment_id)
AND ENTITY_NAME = 'PON_AUC_PAYMENTS_SHIPMENTS'
UNION
SELECT ENTITY_NAME,ATTACHED_DOCUMENT_ID,PK1_VALUE,PK2_VALUE,PK3_VALUE
FROM FND_ATTACHED_DOCUMENTS fad
WHERE pk1_value = To_Char(p_source_auction_header_id)
AND pk2_value = to_char(payment_line_rec.source_line_number)
AND pk3_value = to_char(payment_line_rec.source_payment_id)
AND ENTITY_NAME = 'PON_AUC_PAYMENTS_SHIPMENTS_AMEND'
AND NOT EXISTS
(SELECT 1 FROM FND_ATTACHED_DOCUMENTS
WHERE pk1_value = to_char(p_source_auction_header_id)
AND ENTITY_NAME
in ('PON_AUC_PAYMENTS_SHIPMENTS',
'PON_AUC_PAYMENTS_SHIPMENTS_DEL')
AND ORIG_ATTACH_DOC_ID =fad.ORIG_ATTACH_DOC_ID)) LOOP
FND_ATTACHED_DOCUMENTS2_PKG.COPY_ATTACHMENTS (
X_from_entity_name => base_attach_rec.ENTITY_NAME,
X_from_pk1_value => base_attach_rec.pk1_value,
X_from_pk2_value => base_attach_rec.pk2_value,
X_from_pk3_value => base_attach_rec.pk3_value,
X_to_entity_name => 'PON_AUC_PAYMENTS_SHIPMENTS',
X_to_pk1_value => to_char(p_auction_header_id), -- PK1_VALUE
X_to_pk2_value => to_char(payment_line_rec.dest_line_number), -- PK1_VALUE
X_to_pk3_value => to_char(payment_line_rec.dest_payment_id), -- PK1_VALUE
X_created_by => p_user_id, -- CREATED_BY
X_last_update_login => fnd_global.login_id, -- LAST_UPDATE_LOGIN
X_orig_attach_doc_id => base_attach_rec.ATTACHED_DOCUMENT_ID
);
(SELECT ATTACHED_DOCUMENT_ID,PK1_VALUE,PK2_VALUE, PK3_VALUE
FROM FND_ATTACHED_DOCUMENTS
WHERE pk1_value = to_char(payment_line_rec.source_auc_id)
AND pk2_value = to_char(payment_line_rec.source_line_number)
AND pk3_value = to_char(payment_line_rec.source_payment_id)
AND ENTITY_NAME = 'PON_AUC_PAYMENTS_SHIPMENTS'
) LOOP
FND_ATTACHED_DOCUMENTS2_PKG.COPY_ATTACHMENTS (
X_from_entity_name => 'PON_AUC_PAYMENTS_SHIPMENTS',
X_from_pk1_value => to_char(payment_line_rec.source_auc_id),
X_from_pk2_value => to_char(payment_line_rec.source_line_number),
X_from_pk3_value => to_char(payment_line_rec.source_payment_id),
X_to_entity_name => 'PON_AUC_PAYMENTS_SHIPMENTS_AMEND',
X_to_pk1_value => to_char(payment_line_rec.dest_auc_id), -- PK1_VALUE
X_to_pk2_value => to_char(payment_line_rec.dest_line_number), -- PK1_VALUE
X_to_pk3_value => to_char(payment_line_rec.dest_payment_id), -- PK1_VALUE
X_created_by => p_user_id, -- CREATED_BY
X_last_update_login => fnd_global.login_id, -- LAST_UPDATE_LOGIN
X_orig_attach_doc_id => base_attach_rec.ATTACHED_DOCUMENT_ID
);
X_last_update_login => fnd_global.login_id
);
SELECT price_tiers_indicator
INTO l_src_price_tiers_indicator
FROM pon_auction_headers_all
WHERE auction_header_id = p_source_auction_header_id;
INSERT INTO
PON_AUCTION_SHIPMENTS_ALL
( AUCTION_HEADER_ID,
LINE_NUMBER,
SHIPMENT_NUMBER,
SHIPMENT_TYPE,
SHIP_TO_ORGANIZATION_ID,
SHIP_TO_LOCATION_ID,
QUANTITY,
PRICE,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,
ORG_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
HAS_PRICE_DIFFERENTIALS_FLAG,
DIFFERENTIAL_RESPONSE_TYPE
)
(SELECT
p_auction_header_id,
LINE_NUMBER,
SHIPMENT_NUMBER,
SHIPMENT_TYPE,
SHIP_TO_ORGANIZATION_ID,
SHIP_TO_LOCATION_ID,
QUANTITY,
PRICE,
decode (l_keep_effective_start_date,
'Y', EFFECTIVE_START_DATE,
NULL), -- EFFECTIVE_START_DATE
decode (l_keep_effective_end_date,
'Y', EFFECTIVE_END_DATE,
NULL), -- EFFECTIVE_END_DATE
ORG_ID, -- Do we need to set thi OrgId to the current one
SYSDATE, -- CREATION_DATE
p_user_id, -- CREATED_BY
SYSDATE, -- LAST_UPDATE_DATE
p_user_id, -- LAST_UPDATED_BY
p_user_id, -- LAST_UPDATE_LOGIN
decode ( g_auc_doctype_rule_data.ALLOW_PRICE_DIFFERENTIAL,
'Y', HAS_PRICE_DIFFERENTIALS_FLAG,
'N'), -- HAS_PRICE_DIFFERENTIALS_FLAG
decode ( g_auc_doctype_rule_data.ALLOW_PRICE_DIFFERENTIAL,
'Y', DIFFERENTIAL_RESPONSE_TYPE ,
NULL) -- DIFFERENTIAL_RESPONSE_TYPE
FROM PON_AUCTION_SHIPMENTS_ALL
WHERE AUCTION_HEADER_ID = p_source_auction_header_id
AND SHIPMENT_TYPE = 'PRICE BREAK'
AND line_number >= p_from_line_number
AND line_number <= p_to_line_number) ;
INSERT INTO
PON_AUCTION_SHIPMENTS_ALL
( AUCTION_HEADER_ID,
LINE_NUMBER,
SHIPMENT_NUMBER,
SHIPMENT_TYPE,
SHIP_TO_ORGANIZATION_ID,
SHIP_TO_LOCATION_ID,
QUANTITY, -- This is the MIN Quantity field for quantity based Price tiers
PRICE,
ORG_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
MAX_QUANTITY,
HAS_PRICE_DIFFERENTIALS_FLAG-- This is for quantity price Tiers only
)
(SELECT
p_auction_header_id,
LINE_NUMBER,
SHIPMENT_NUMBER,
SHIPMENT_TYPE,
SHIP_TO_ORGANIZATION_ID,
SHIP_TO_LOCATION_ID,
QUANTITY,
PRICE,
ORG_ID, -- Do we need to set the orgId to the current one
SYSDATE, -- CREATION_DATE
p_user_id, -- CREATED_BY
SYSDATE, -- LAST_UPDATE_DATE
p_user_id, -- LAST_UPDATED_BY
p_user_id, -- LAST_UPDATE_LOGIN
MAX_QUANTITY, -- Max Quantity for qty based price tiers
'N' -- HAS_PRICE_DIFFERENTIALS_FLAG
FROM PON_AUCTION_SHIPMENTS_ALL
WHERE AUCTION_HEADER_ID = p_source_auction_header_id
AND SHIPMENT_TYPE = 'QUANTITY BASED'
AND line_number >= p_from_line_number
AND line_number <= p_to_line_number);
INSERT INTO
PON_PRICE_ELEMENTS
( AUCTION_HEADER_ID,
LINE_NUMBER,
LIST_ID,
PRICE_ELEMENT_TYPE_ID,
PRICING_BASIS,
VALUE,
DISPLAY_TARGET_FLAG,
SEQUENCE_NUMBER,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
PF_TYPE,
DISPLAY_TO_SUPPLIERS_FLAG,
NEGATIVE_COST_FACTOR_FLAG
)
(SELECT
p_auction_header_id,
P.LINE_NUMBER,
P.LIST_ID,
P.PRICE_ELEMENT_TYPE_ID,
P.PRICING_BASIS,
P.VALUE,
P.DISPLAY_TARGET_FLAG,
P.SEQUENCE_NUMBER,
SYSDATE,
p_user_id,
SYSDATE,
p_user_id,
PF_TYPE, -- Tranformation project related column
DISPLAY_TO_SUPPLIERS_FLAG, -- Tranformation project related column
NEGATIVE_COST_FACTOR_FLAG
FROM PON_PRICE_ELEMENTS P,
PON_PRICE_ELEMENT_TYPES_VL VL
WHERE P.AUCTION_HEADER_ID = p_source_auction_header_id
AND P.PRICE_ELEMENT_TYPE_ID <> -10
AND P.PRICE_ELEMENT_TYPE_ID = VL.PRICE_ELEMENT_TYPE_ID
AND VL.ENABLED_FLAG = 'Y'
AND P.line_number >= p_from_line_number
AND P.line_number <= p_to_line_number) ;
INSERT INTO
PON_AUCTION_CURRENCY_RATES
(
AUCTION_HEADER_ID,
AUCTION_CURRENCY_CODE,
BID_CURRENCY_CODE,
RATE,
NUMBER_PRICE_DECIMALS,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
RATE_TYPE,
RATE_DATE,
CREATION_DATE,
CREATED_BY,
LIST_ID,
DERIVE_TYPE,
SEQUENCE_NUMBER,
RATE_DSP,
LAST_AMENDMENT_UPDATE,
MODIFIED_FLAG
)
(SELECT
p_auction_header_id, -- AUCTION_HEADER_ID
AUCTION_CURRENCY_CODE,
BID_CURRENCY_CODE,
RATE,
NUMBER_PRICE_DECIMALS,
p_user_id, -- LAST_UPDATED_BY
SYSDATE, -- LAST_UPDATE_DATE
RATE_TYPE,
RATE_DATE,
SYSDATE, -- CREATION_DATE
p_user_id, -- CREATED_BY
LIST_ID,
DERIVE_TYPE,
SEQUENCE_NUMBER,
RATE_DSP,
--
-- AmendmentUpdate attribute value is only carried over
-- only for Amendment copy. It is set to default value 0
-- in all other cases
--
decode (p_copy_type, g_amend_copy,
LAST_AMENDMENT_UPDATE,
0), -- LAST_AMENDMENT_UPDATE
--
-- MODIFIED_FLAG is always set to NULL
--
NULL -- MODIFIED_FLAG
FROM PON_AUCTION_CURRENCY_RATES
WHERE AUCTION_HEADER_ID = p_source_auction_header_id ) ;
l_last_amendment_update PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
SELECT AUCTION_HEADER_ID_ORIG_AMEND
INTO l_auction_header_id_orig_amend
FROM PON_AUCTION_HEADERS_ALL
WHERE AUCTION_HEADER_ID = p_source_auction_header_id;
SELECT LIST_ID,
SEQUENCE,
TRADING_PARTNER_NAME,
TRADING_PARTNER_ID,
TRADING_PARTNER_CONTACT_NAME,
TRADING_PARTNER_CONTACT_ID,
NEW_SUPPLIER_NAME,
NEW_SUPPLIER_CONTACT_FNAME,
NEW_SUPPLIER_CONTACT_LNAME,
NEW_SUPPLIER_EMAIL,
NOTE_TO_NEW_SUPPLIER,
WF_USER_NAME,
INVITATION_ID,
AUCTION_CREATION_DATE,
BID_CURRENCY_CODE,
NUMBER_PRICE_DECIMALS,
RATE,
DERIVE_TYPE,
ADDITIONAL_CONTACT_EMAIL,
ROUND_NUMBER,
SUPP_ACKNOWLEDGEMENT,
ACK_PARTNER_CONTACT_ID,
ACKNOWLEDGEMENT_TIME,
ACK_NOTE_TO_AUCTIONEER,
REGISTRATION_ID,
RATE_DSP,
LAST_AMENDMENT_UPDATE,
VENDOR_SITE_ID,
VENDOR_SITE_CODE,
MODIFIED_FLAG,
ACCESS_TYPE,
REQUESTED_SUPPLIER_ID,
REQUESTED_SUPPLIER_NAME,
REQUESTED_SUPPLIER_CONTACT_ID,
REQUESTED_SUPP_CONTACT_NAME
BULK COLLECT
INTO
l_list_id,
l_sequence,
l_trading_partner_name,
l_trading_partner_id,
l_trading_partner_contact_name,
l_trading_partner_contact_id,
l_new_supplier_name,
l_new_supplier_contact_fname,
l_new_supplier_contact_lname,
l_new_supplier_email,
l_note_to_new_supplier,
l_wf_user_name,
l_invitation_id,
l_auction_creation_date,
l_bid_currency_code,
l_number_price_decimals,
l_rate,
l_derive_type,
l_additional_contact_email,
l_round_number,
l_supp_acknowledgement,
l_ack_partner_contact_id,
l_acknowledgement_time,
l_ack_note_to_auctioneer,
l_registration_id,
l_rate_dsp,
l_last_amendment_update,
l_vendor_site_id,
l_vendor_site_code,
l_modified_flag,
l_access_type,
l_requested_supplier_id,
l_requested_supplier_name,
l_requested_supp_contact_id,
l_requested_supp_contact_name
FROM
(SELECT
PBP.LIST_ID,
PBP.SEQUENCE,
PBP.TRADING_PARTNER_NAME,
PBP.TRADING_PARTNER_ID,
PBP.TRADING_PARTNER_CONTACT_NAME,
PBP.TRADING_PARTNER_CONTACT_ID,
PBP.NEW_SUPPLIER_NAME,
PBP.NEW_SUPPLIER_CONTACT_FNAME,
PBP.NEW_SUPPLIER_CONTACT_LNAME,
PBP.NEW_SUPPLIER_EMAIL,
PBP.NOTE_TO_NEW_SUPPLIER,
PBP.WF_USER_NAME,
PBP.INVITATION_ID,
PBP.AUCTION_CREATION_DATE,
PBP.BID_CURRENCY_CODE,
PBP.NUMBER_PRICE_DECIMALS,
PBP.RATE,
PBP.DERIVE_TYPE,
PBP.ADDITIONAL_CONTACT_EMAIL,
PBP.ROUND_NUMBER,
PBP.SUPP_ACKNOWLEDGEMENT,
PBP.ACK_PARTNER_CONTACT_ID,
PBP.ACKNOWLEDGEMENT_TIME,
PBP.ACK_NOTE_TO_AUCTIONEER,
PBP.REGISTRATION_ID,
PBP.RATE_DSP,
PBP.LAST_AMENDMENT_UPDATE,
PBP.VENDOR_SITE_ID,
PBP.VENDOR_SITE_CODE,
PBP.MODIFIED_FLAG,
PBP.ACCESS_TYPE,
NULL REQUESTED_SUPPLIER_ID,
NULL REQUESTED_SUPPLIER_NAME,
NULL REQUESTED_SUPPLIER_CONTACT_ID,
NULL REQUESTED_SUPP_CONTACT_NAME
FROM PON_BIDDING_PARTIES PBP,
AP_SUPPLIER_SITES_ALL PS,
AP_SUPPLIERS PV
WHERE PBP.AUCTION_HEADER_ID = p_source_auction_header_id
--bug 8613219
-- AND NVL(PBP.from_emd_flag,'N') <> 'Y' -- Added by Lion for EMD on 04/02/2009
AND PV.PARTY_ID = PBP.TRADING_PARTNER_ID
AND PS.VENDOR_ID = PV.VENDOR_ID
AND (PS.PURCHASING_SITE_FLAG = 'Y' OR PS.RFQ_ONLY_SITE_FLAG = 'Y')
AND NVL(PS.INACTIVE_DATE, SYSDATE) >= SYSDATE
AND PS.ORG_ID = p_org_id
AND PS.VENDOR_SITE_ID = PBP.VENDOR_SITE_ID
UNION
SELECT
PBP.LIST_ID,
PBP.SEQUENCE,
PBP.TRADING_PARTNER_NAME,
PBP.TRADING_PARTNER_ID,
PBP.TRADING_PARTNER_CONTACT_NAME,
PBP.TRADING_PARTNER_CONTACT_ID,
PBP.NEW_SUPPLIER_NAME,
PBP.NEW_SUPPLIER_CONTACT_FNAME,
PBP.NEW_SUPPLIER_CONTACT_LNAME,
PBP.NEW_SUPPLIER_EMAIL,
PBP.NOTE_TO_NEW_SUPPLIER,
PBP.WF_USER_NAME,
PBP.INVITATION_ID,
PBP.AUCTION_CREATION_DATE,
PBP.BID_CURRENCY_CODE,
PBP.NUMBER_PRICE_DECIMALS,
PBP.RATE,
PBP.DERIVE_TYPE,
PBP.ADDITIONAL_CONTACT_EMAIL,
PBP.ROUND_NUMBER,
PBP.SUPP_ACKNOWLEDGEMENT,
PBP.ACK_PARTNER_CONTACT_ID,
PBP.ACKNOWLEDGEMENT_TIME,
PBP.ACK_NOTE_TO_AUCTIONEER,
PBP.REGISTRATION_ID,
PBP.RATE_DSP,
PBP.LAST_AMENDMENT_UPDATE,
PBP.VENDOR_SITE_ID,
PBP.VENDOR_SITE_CODE,
PBP.MODIFIED_FLAG,
PBP.ACCESS_TYPE,
PBP.REQUESTED_SUPPLIER_ID,
PBP.REQUESTED_SUPPLIER_NAME,
PBP.REQUESTED_SUPPLIER_CONTACT_ID,
PBP.REQUESTED_SUPP_CONTACT_NAME
FROM PON_BIDDING_PARTIES PBP
WHERE PBP.AUCTION_HEADER_ID = p_source_auction_header_id
--bug 8613219
--AND NVL(PBP.from_emd_flag,'N') <> 'Y'-- Added by Lion for EMD on 04/02/2009
AND (PBP.VENDOR_SITE_ID = -1 OR
PBP.VENDOR_SITE_ID IS NULL) ) A;
SELECT LIST_ID,
SEQUENCE,
TRADING_PARTNER_NAME,
TRADING_PARTNER_ID,
TRADING_PARTNER_CONTACT_NAME,
TRADING_PARTNER_CONTACT_ID,
NEW_SUPPLIER_NAME,
NEW_SUPPLIER_CONTACT_FNAME,
NEW_SUPPLIER_CONTACT_LNAME,
NEW_SUPPLIER_EMAIL,
NOTE_TO_NEW_SUPPLIER,
WF_USER_NAME,
INVITATION_ID,
AUCTION_CREATION_DATE,
BID_CURRENCY_CODE,
NUMBER_PRICE_DECIMALS,
RATE,
DERIVE_TYPE,
ADDITIONAL_CONTACT_EMAIL,
ROUND_NUMBER,
SUPP_ACKNOWLEDGEMENT,
ACK_PARTNER_CONTACT_ID,
ACKNOWLEDGEMENT_TIME,
ACK_NOTE_TO_AUCTIONEER,
REGISTRATION_ID,
RATE_DSP,
LAST_AMENDMENT_UPDATE,
VENDOR_SITE_ID,
VENDOR_SITE_CODE,
MODIFIED_FLAG,
ACCESS_TYPE,
REQUESTED_SUPPLIER_ID,
REQUESTED_SUPPLIER_NAME,
REQUESTED_SUPPLIER_CONTACT_ID,
REQUESTED_SUPP_CONTACT_NAME
BULK COLLECT
INTO
l_list_id,
l_sequence,
l_trading_partner_name,
l_trading_partner_id,
l_trading_partner_contact_name,
l_trading_partner_contact_id,
l_new_supplier_name,
l_new_supplier_contact_fname,
l_new_supplier_contact_lname,
l_new_supplier_email,
l_note_to_new_supplier,
l_wf_user_name,
l_invitation_id,
l_auction_creation_date,
l_bid_currency_code,
l_number_price_decimals,
l_rate,
l_derive_type,
l_additional_contact_email,
l_round_number,
l_supp_acknowledgement,
l_ack_partner_contact_id,
l_acknowledgement_time,
l_ack_note_to_auctioneer,
l_registration_id,
l_rate_dsp,
l_last_amendment_update,
l_vendor_site_id,
l_vendor_site_code,
l_modified_flag,
l_access_type,
l_requested_supplier_id,
l_requested_supplier_name,
l_requested_supp_contact_id,
l_requested_supp_contact_name
FROM
(SELECT
PBP.LIST_ID,
PBP.SEQUENCE,
PBP.TRADING_PARTNER_NAME,
PBP.TRADING_PARTNER_ID,
PBP.TRADING_PARTNER_CONTACT_NAME,
PBP.TRADING_PARTNER_CONTACT_ID,
PBP.NEW_SUPPLIER_NAME,
PBP.NEW_SUPPLIER_CONTACT_FNAME,
PBP.NEW_SUPPLIER_CONTACT_LNAME,
PBP.NEW_SUPPLIER_EMAIL,
PBP.NOTE_TO_NEW_SUPPLIER,
PBP.WF_USER_NAME,
PBP.INVITATION_ID,
PBP.AUCTION_CREATION_DATE,
PBP.BID_CURRENCY_CODE,
PBP.NUMBER_PRICE_DECIMALS,
PBP.RATE,
PBP.DERIVE_TYPE,
PBP.ADDITIONAL_CONTACT_EMAIL,
PBP.ROUND_NUMBER,
PBP.SUPP_ACKNOWLEDGEMENT,
PBP.ACK_PARTNER_CONTACT_ID,
PBP.ACKNOWLEDGEMENT_TIME,
PBP.ACK_NOTE_TO_AUCTIONEER,
PBP.REGISTRATION_ID,
PBP.RATE_DSP,
PBP.LAST_AMENDMENT_UPDATE,
PBP.VENDOR_SITE_ID,
PBP.VENDOR_SITE_CODE,
PBP.MODIFIED_FLAG,
PBP.ACCESS_TYPE,
NULL REQUESTED_SUPPLIER_ID,
NULL REQUESTED_SUPPLIER_NAME,
NULL REQUESTED_SUPPLIER_CONTACT_ID,
NULL REQUESTED_SUPP_CONTACT_NAME
FROM PON_BIDDING_PARTIES PBP,
AP_SUPPLIER_SITES_ALL PS,
AP_SUPPLIERS PV
WHERE PBP.AUCTION_HEADER_ID = p_source_auction_header_id
AND PV.PARTY_ID = PBP.TRADING_PARTNER_ID
AND PS.VENDOR_ID = PV.VENDOR_ID
AND NVL(PBP.from_emd_flag,'N') <> 'Y'-- Added by Lion for EMD on 04/02/2009
AND nvl(PV.start_date_active, sysdate) <= sysdate
AND nvl(PV.end_date_active, sysdate) >= sysdate
AND (PS.PURCHASING_SITE_FLAG = 'Y' OR PS.RFQ_ONLY_SITE_FLAG = 'Y')
AND NVL(PS.INACTIVE_DATE, SYSDATE) >= SYSDATE
AND PS.ORG_ID = p_org_id
AND PS.VENDOR_SITE_ID = PBP.VENDOR_SITE_ID
UNION
-- suppliers with site = -1
SELECT
PBP.LIST_ID,
PBP.SEQUENCE,
PBP.TRADING_PARTNER_NAME,
PBP.TRADING_PARTNER_ID,
PBP.TRADING_PARTNER_CONTACT_NAME,
PBP.TRADING_PARTNER_CONTACT_ID,
PBP.NEW_SUPPLIER_NAME,
PBP.NEW_SUPPLIER_CONTACT_FNAME,
PBP.NEW_SUPPLIER_CONTACT_LNAME,
PBP.NEW_SUPPLIER_EMAIL,
PBP.NOTE_TO_NEW_SUPPLIER,
PBP.WF_USER_NAME,
PBP.INVITATION_ID,
PBP.AUCTION_CREATION_DATE,
PBP.BID_CURRENCY_CODE,
PBP.NUMBER_PRICE_DECIMALS,
PBP.RATE,
PBP.DERIVE_TYPE,
PBP.ADDITIONAL_CONTACT_EMAIL,
PBP.ROUND_NUMBER,
PBP.SUPP_ACKNOWLEDGEMENT,
PBP.ACK_PARTNER_CONTACT_ID,
PBP.ACKNOWLEDGEMENT_TIME,
PBP.ACK_NOTE_TO_AUCTIONEER,
PBP.REGISTRATION_ID,
PBP.RATE_DSP,
PBP.LAST_AMENDMENT_UPDATE,
PBP.VENDOR_SITE_ID,
PBP.VENDOR_SITE_CODE,
PBP.MODIFIED_FLAG,
PBP.ACCESS_TYPE,
NULL REQUESTED_SUPPLIER_ID,
NULL REQUESTED_SUPPLIER_NAME,
NULL REQUESTED_SUPPLIER_CONTACT_ID,
NULL REQUESTED_SUPP_CONTACT_NAME
FROM PON_BIDDING_PARTIES PBP,
AP_SUPPLIERS PV
WHERE PBP.AUCTION_HEADER_ID = p_source_auction_header_id
AND NVL(PBP.from_emd_flag,'N') <> 'Y'-- Added by Lion for EMD on 04/02/2009
AND (PBP.VENDOR_SITE_ID = -1 OR
PBP.VENDOR_SITE_ID IS NULL)
AND PBP.TRADING_PARTNER_ID = PV.PARTY_ID
AND NVL(pv.start_date_active, sysdate) <= sysdate
AND NVL(pv.end_date_active, sysdate) >= sysdate
UNION
-- requested suppliers
SELECT
PBP.LIST_ID,
PBP.SEQUENCE,
PBP.TRADING_PARTNER_NAME,
PBP.TRADING_PARTNER_ID,
PBP.TRADING_PARTNER_CONTACT_NAME,
PBP.TRADING_PARTNER_CONTACT_ID,
PBP.NEW_SUPPLIER_NAME,
PBP.NEW_SUPPLIER_CONTACT_FNAME,
PBP.NEW_SUPPLIER_CONTACT_LNAME,
PBP.NEW_SUPPLIER_EMAIL,
PBP.NOTE_TO_NEW_SUPPLIER,
PBP.WF_USER_NAME,
PBP.INVITATION_ID,
PBP.AUCTION_CREATION_DATE,
PBP.BID_CURRENCY_CODE,
PBP.NUMBER_PRICE_DECIMALS,
PBP.RATE,
PBP.DERIVE_TYPE,
PBP.ADDITIONAL_CONTACT_EMAIL,
PBP.ROUND_NUMBER,
PBP.SUPP_ACKNOWLEDGEMENT,
PBP.ACK_PARTNER_CONTACT_ID,
PBP.ACKNOWLEDGEMENT_TIME,
PBP.ACK_NOTE_TO_AUCTIONEER,
PBP.REGISTRATION_ID,
PBP.RATE_DSP,
PBP.LAST_AMENDMENT_UPDATE,
PBP.VENDOR_SITE_ID,
PBP.VENDOR_SITE_CODE,
PBP.MODIFIED_FLAG,
PBP.ACCESS_TYPE,
PBP.REQUESTED_SUPPLIER_ID,
PBP.REQUESTED_SUPPLIER_NAME,
PBP.REQUESTED_SUPPLIER_CONTACT_ID,
PBP.REQUESTED_SUPP_CONTACT_NAME
FROM PON_BIDDING_PARTIES PBP
WHERE PBP.AUCTION_HEADER_ID = p_source_auction_header_id
AND NVL(PBP.from_emd_flag,'N') <> 'Y'-- Added by Lion for EMD on 04/02/2009
AND PBP.trading_partner_id IS NULL
AND pbp.requested_supplier_id NOT IN (
SELECT supplier_reg_id FROM pos_supplier_registrations
WHERE registration_status = 'REJECTED')
) A;
SELECT LIST_ID,
SEQUENCE,
TRADING_PARTNER_NAME,
TRADING_PARTNER_ID,
TRADING_PARTNER_CONTACT_NAME,
TRADING_PARTNER_CONTACT_ID,
NEW_SUPPLIER_NAME,
NEW_SUPPLIER_CONTACT_FNAME,
NEW_SUPPLIER_CONTACT_LNAME,
NEW_SUPPLIER_EMAIL,
NOTE_TO_NEW_SUPPLIER,
WF_USER_NAME,
INVITATION_ID,
AUCTION_CREATION_DATE,
BID_CURRENCY_CODE,
NUMBER_PRICE_DECIMALS,
RATE,
DERIVE_TYPE,
ADDITIONAL_CONTACT_EMAIL,
ROUND_NUMBER,
SUPP_ACKNOWLEDGEMENT,
ACK_PARTNER_CONTACT_ID,
ACKNOWLEDGEMENT_TIME,
ACK_NOTE_TO_AUCTIONEER,
REGISTRATION_ID,
RATE_DSP,
LAST_AMENDMENT_UPDATE,
VENDOR_SITE_ID,
VENDOR_SITE_CODE,
MODIFIED_FLAG,
ACCESS_TYPE,
REQUESTED_SUPPLIER_ID,
REQUESTED_SUPPLIER_NAME,
REQUESTED_SUPPLIER_CONTACT_ID,
REQUESTED_SUPP_CONTACT_NAME
BULK COLLECT
INTO
l_list_id,
l_sequence,
l_trading_partner_name,
l_trading_partner_id,
l_trading_partner_contact_name,
l_trading_partner_contact_id,
l_new_supplier_name,
l_new_supplier_contact_fname,
l_new_supplier_contact_lname,
l_new_supplier_email,
l_note_to_new_supplier,
l_wf_user_name,
l_invitation_id,
l_auction_creation_date,
l_bid_currency_code,
l_number_price_decimals,
l_rate,
l_derive_type,
l_additional_contact_email,
l_round_number,
l_supp_acknowledgement,
l_ack_partner_contact_id,
l_acknowledgement_time,
l_ack_note_to_auctioneer,
l_registration_id,
l_rate_dsp,
l_last_amendment_update,
l_vendor_site_id,
l_vendor_site_code,
l_modified_flag,
l_access_type,
l_requested_supplier_id,
l_requested_supplier_name,
l_requested_supp_contact_id,
l_requested_supp_contact_name
FROM (SELECT LIST_ID,
SEQUENCE,
TRADING_PARTNER_NAME,
TRADING_PARTNER_ID,
TRADING_PARTNER_CONTACT_NAME,
TRADING_PARTNER_CONTACT_ID,
NEW_SUPPLIER_NAME,
NEW_SUPPLIER_CONTACT_FNAME,
NEW_SUPPLIER_CONTACT_LNAME,
NEW_SUPPLIER_EMAIL,
NOTE_TO_NEW_SUPPLIER,
WF_USER_NAME,
INVITATION_ID,
AUCTION_CREATION_DATE,
BID_CURRENCY_CODE,
NUMBER_PRICE_DECIMALS,
RATE,
DERIVE_TYPE,
ADDITIONAL_CONTACT_EMAIL,
ROUND_NUMBER,
SUPP_ACKNOWLEDGEMENT,
ACK_PARTNER_CONTACT_ID,
ACKNOWLEDGEMENT_TIME,
ACK_NOTE_TO_AUCTIONEER,
REGISTRATION_ID,
RATE_DSP,
LAST_AMENDMENT_UPDATE,
VENDOR_SITE_ID,
VENDOR_SITE_CODE,
MODIFIED_FLAG,
ACCESS_TYPE,
NULL REQUESTED_SUPPLIER_ID,
NULL REQUESTED_SUPPLIER_NAME,
NULL REQUESTED_SUPPLIER_CONTACT_ID,
NULL REQUESTED_SUPP_CONTACT_NAME
FROM PON_BIDDING_PARTIES PBP,
AP_SUPPLIERS PV
WHERE PBP.AUCTION_HEADER_ID = p_source_auction_header_id
--bug 8613219
--AND NVL(PBP.from_emd_flag,'N') <> 'Y'-- Added by Lion for EMD on 04/02/2009
AND (TRADING_PARTNER_ID,TRADING_PARTNER_CONTACT_ID,nvl(VENDOR_SITE_ID,-1))
NOT IN (SELECT PBH.TRADING_PARTNER_ID,
PBH.TRADING_PARTNER_CONTACT_ID,
NVL(PBH.VENDOR_SITE_ID,-1) VENDOR_SITE_ID
FROM PON_BID_HEADERS PBH
WHERE PBH.SHORTLIST_FLAG = 'N'
AND PBH.AUCTION_HEADER_ID IN
(SELECT AUCTION_HEADER_ID
FROM PON_AUCTION_HEADERS_ALL
WHERE AUCTION_HEADER_ID_ORIG_AMEND =
l_auction_header_id_orig_amend)
AND PBH.BID_STATUS IN('ACTIVE','RESUBMISSION'))
AND PBP.TRADING_PARTNER_ID = PV.PARTY_ID
AND nvl(PV.start_date_active, sysdate) <= sysdate
AND nvl(PV.end_date_active, sysdate) >= sysdate
UNION
-- requested suppliers
SELECT LIST_ID,
SEQUENCE,
TRADING_PARTNER_NAME,
TRADING_PARTNER_ID,
TRADING_PARTNER_CONTACT_NAME,
TRADING_PARTNER_CONTACT_ID,
NEW_SUPPLIER_NAME,
NEW_SUPPLIER_CONTACT_FNAME,
NEW_SUPPLIER_CONTACT_LNAME,
NEW_SUPPLIER_EMAIL,
NOTE_TO_NEW_SUPPLIER,
WF_USER_NAME,
INVITATION_ID,
AUCTION_CREATION_DATE,
BID_CURRENCY_CODE,
NUMBER_PRICE_DECIMALS,
RATE,
DERIVE_TYPE,
ADDITIONAL_CONTACT_EMAIL,
ROUND_NUMBER,
SUPP_ACKNOWLEDGEMENT,
ACK_PARTNER_CONTACT_ID,
ACKNOWLEDGEMENT_TIME,
ACK_NOTE_TO_AUCTIONEER,
REGISTRATION_ID,
RATE_DSP,
LAST_AMENDMENT_UPDATE,
VENDOR_SITE_ID,
VENDOR_SITE_CODE,
MODIFIED_FLAG,
ACCESS_TYPE,
REQUESTED_SUPPLIER_ID,
REQUESTED_SUPPLIER_NAME,
REQUESTED_SUPPLIER_CONTACT_ID,
REQUESTED_SUPP_CONTACT_NAME
FROM PON_BIDDING_PARTIES PBP
WHERE PBP.AUCTION_HEADER_ID = p_source_auction_header_id
--bug 8613219
--AND NVL(PBP.from_emd_flag,'N') <> 'Y' -- Added by Lion for EMD on 04/02/2009
AND PBP.trading_partner_id IS NULL
AND PBP.requested_supplier_id NOT IN (
SELECT supplier_reg_id
FROM pos_supplier_registrations
WHERE registration_status = 'REJECTED')
UNION -- suppliers from bid headers
SELECT
-1 as LIST_ID,
to_number(null) as SEQUENCE,
PBH.TRADING_PARTNER_NAME,
PBH.TRADING_PARTNER_ID,
TRADING_PARTNER_CONTACT_NAME,
TRADING_PARTNER_CONTACT_ID,
NULL as NEW_SUPPLIER_NAME,
NULL as NEW_SUPPLIER_CONTACT_FNAME,
NULL as NEW_SUPPLIER_CONTACT_LNAME,
NULL as NEW_SUPPLIER_EMAIL,
NULL as NOTE_TO_NEW_SUPPLIER,
NULL as WF_USER_NAME,
to_number(NULL) as INVITATION_ID,
to_date(NULL) as AUCTION_CREATION_DATE,
NULL as BID_CURRENCY_CODE,
to_number(NULL) as NUMBER_PRICE_DECIMALS,
TO_NUMBER(NULL) as RATE,
NULL as DERIVE_TYPE,
NULL as ADDITIONAL_CONTACT_EMAIL,
l_auction_round_number as ROUND_NUMBER,
NULL as SUPP_ACKNOWLEDGEMENT,
to_number(NULL) as ACK_PARTNER_CONTACT_ID,
to_date(NULL) as ACKNOWLEDGEMENT_TIME,
NULL as ACK_NOTE_TO_AUCTIONEER,
to_number(NULL) as REGISTRATION_ID,
TO_NUMBER(NULL) as RATE_DSP,
0 as LAST_AMENDMENT_UPDATE,
-1 as VENDOR_SITE_ID,
'-1' as VENDOR_SITE_CODE,
to_char(NULL) as MODIFIED_FLAG,
'FULL' as ACCESS_TYPE, -- The default value of ACCESS_TYPE seems to be FULL
NULL REQUESTED_SUPPLIER_ID,
NULL REQUESTED_SUPPLIER_NAME,
NULL REQUESTED_SUPPLIER_CONTACT_ID,
NULL REQUESTED_SUPP_CONTACT_NAME
FROM PON_BID_HEADERS PBH
WHERE PBH.SHORTLIST_FLAG <> 'N'
AND PBH.AUCTION_HEADER_ID IN
(SELECT AUCTION_HEADER_ID
FROM PON_AUCTION_HEADERS_ALL
WHERE AUCTION_HEADER_ID_ORIG_AMEND =
l_auction_header_id_orig_amend
)
AND PBH.BID_STATUS IN ('ACTIVE', 'RESUBMISSION')
AND PBH.TRADING_PARTNER_ID NOT IN
(SELECT NVL(TRADING_PARTNER_ID, -1)
FROM PON_BIDDING_PARTIES
WHERE AUCTION_HEADER_ID = p_source_auction_header_id)
AND PBH.trading_partner_contact_name =
(SELECT MIN(trading_partner_contact_name)
FROM pon_bid_headers pbhinner
WHERE pbhinner.trading_partner_id = pbh.trading_partner_id
AND pbhinner.auction_header_id = pbh.auction_header_id
GROUP BY pbhinner.auction_header_id, pbhinner.trading_partner_id)
GROUP BY TRADING_PARTNER_ID, TRADING_PARTNER_NAME, TRADING_PARTNER_CONTACT_ID, TRADING_PARTNER_CONTACT_NAME
ORDER BY SEQUENCE ASC NULLS LAST) A;
l_last_amendment_update(x) := 0;
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,
NEW_SUPPLIER_NAME,
NEW_SUPPLIER_CONTACT_FNAME,
NEW_SUPPLIER_CONTACT_LNAME,
NEW_SUPPLIER_EMAIL,
NOTE_TO_NEW_SUPPLIER,
WF_USER_NAME,
INVITATION_ID,
CREATION_DATE,
CREATED_BY,
AUCTION_CREATION_DATE,
BID_CURRENCY_CODE,
NUMBER_PRICE_DECIMALS,
RATE,
DERIVE_TYPE,
ADDITIONAL_CONTACT_EMAIL,
ROUND_NUMBER,
SUPP_ACKNOWLEDGEMENT,
ACK_PARTNER_CONTACT_ID,
ACKNOWLEDGEMENT_TIME,
ACK_NOTE_TO_AUCTIONEER,
REGISTRATION_ID,
RATE_DSP,
LAST_AMENDMENT_UPDATE,
VENDOR_SITE_ID,
VENDOR_SITE_CODE,
MODIFIED_FLAG,
ACCESS_TYPE,
REQUESTED_SUPPLIER_ID,
REQUESTED_SUPPLIER_NAME,
REQUESTED_SUPPLIER_CONTACT_ID,
REQUESTED_SUPP_CONTACT_NAME
)
VALUES
(
p_auction_header_id,
l_list_id(x),
SYSDATE ,
p_user_id ,
l_sequence(x),
l_trading_partner_name(x),
l_trading_partner_id(x),
l_trading_partner_contact_name(x),
l_trading_partner_contact_id(x),
l_new_supplier_name(x),
l_new_supplier_contact_fname(x),
l_new_supplier_contact_lname(x),
l_new_supplier_email(x),
l_note_to_new_supplier(x),
l_wf_user_name(x),
l_invitation_id(x),
SYSDATE ,
p_user_id ,
l_auction_creation_date(x),
l_bid_currency_code(x),
l_number_price_decimals(x),
l_rate(x),
l_derive_type(x),
l_additional_contact_email(x),
l_round_number(x),
l_supp_acknowledgement(x),
l_ack_partner_contact_id(x),
l_acknowledgement_time(x),
l_ack_note_to_auctioneer(x),
l_registration_id(x),
l_rate_dsp(x),
l_last_amendment_update(x),
l_vendor_site_id(x),
l_vendor_site_code(x),
l_modified_flag(x),
--in case of large auctions, we do not allow party exclusions
--so we need this decode
decode (g_neg_style_control.large_neg_enabled_flag,'Y','FULL', l_access_type(x)),
l_requested_supplier_id(x),
l_requested_supplier_name(x),
l_requested_supp_contact_id(x),
l_requested_supp_contact_name(x)
) ;
SELECT
DISTINCT PBP.trading_partner_name BULK COLLECT INTO l_inactive_suppliers
FROM PON_BIDDING_PARTIES PBP,
PO_VENDORS PV
WHERE PBP.AUCTION_HEADER_ID = p_source_auction_header_id
AND PV.PARTY_ID = PBP.TRADING_PARTNER_ID
AND ( nvl(pv.start_date_active, sysdate) > sysdate OR
nvl(pv.end_date_active, sysdate) < sysdate );
SELECT
DISTINCT PBP.trading_partner_contact_id BULK COLLECT
INTO l_inactive_supplier_contacts
FROM PON_BIDDING_PARTIES PBP,
HZ_RELATIONSHIPS HZR
WHERE PBP.AUCTION_HEADER_ID = p_auction_header_id
AND HZR.SUBJECT_ID = PBP.TRADING_PARTNER_CONTACT_ID
AND HZR.OBJECT_ID = PBP.TRADING_PARTNER_ID
AND HZR.RELATIONSHIP_CODE = 'CONTACT_OF'
AND ( (nvl(HZR.START_DATE, SYSDATE-1)>= SYSDATE) OR
(nvl(HZR.END_DATE, SYSDATE+1) <= SYSDATE)
);
UPDATE PON_BIDDING_PARTIES
SET trading_partner_contact_name = NULL,
trading_partner_contact_id = NULL
WHERE auction_header_id = p_auction_header_id
AND TRADING_PARTNER_CONTACT_ID = l_inactive_supplier_contacts(x);
SELECT DISTINCT PBP.requested_supplier_name BULK COLLECT INTO l_inactive_suppliers
FROM PON_BIDDING_PARTIES PBP,
Pos_supplier_registrations posreg
WHERE PBP.AUCTION_HEADER_ID = p_source_auction_header_id
AND posreg.SUPPLIER_REG_ID = pbp.REQUESTED_SUPPLIER_ID
AND posreg.REGISTRATION_STATUS = 'REJECTED';
l_last_amendment_update PON_NEG_COPY_DATATYPES_GRP.NUMBER_TYPE;
SELECT
PNTM.LIST_ID,
PNTM.USER_ID,
PNTM.MENU_NAME,
PNTM.MEMBER_TYPE,
PNTM.APPROVER_FLAG,
PNTM.APPROVAL_STATUS,
PNTM.TASK_NAME,
PNTM.TARGET_DATE,
PNTM.COMPLETION_DATE,
P.BUSINESS_GROUP_ID,
P.EFFECTIVE_START_DATE,
P.EFFECTIVE_END_DATE,
U.START_DATE USER_START_DATE,
U.END_DATE USER_END_DATE,
PNTM.LAST_AMENDMENT_UPDATE,
PNTM.MODIFIED_FLAG,
PNTM.LAST_NOTIFIED_DATE,
F.BUSINESS_GROUP_ID
BULK COLLECT
INTO
l_list_id,
l_user_id,
l_menu_name,
l_member_type,
l_approver_flag,
l_approval_status,
l_task_name,
l_target_date,
l_completion_date,
l_business_group_id,
l_effective_start_date,
l_effective_end_date,
l_user_start_date,
l_user_end_date,
l_last_amendment_update,
l_modified_flag,
l_last_notif_date,
l_auc_business_group_id
FROM PON_NEG_TEAM_MEMBERS PNTM,
FND_USER U,
PER_ALL_PEOPLE_F P,
PER_ALL_ASSIGNMENTS_F A,
PER_ALL_POSITIONS S,
PON_AUCTION_HEADERS_ALL AH,
FINANCIALS_SYSTEM_PARAMS_ALL F
WHERE U.USER_ID = PNTM.USER_ID
AND PNTM.AUCTION_HEADER_ID = p_source_auction_header_id
AND AH.AUCTION_HEADER_ID = PNTM.AUCTION_HEADER_ID
AND NVL(F.ORG_ID, -9999) = NVL(AH.ORG_ID, -9999)
AND P.PERSON_ID = U.EMPLOYEE_ID
AND P.EFFECTIVE_END_DATE =
(SELECT MAX(PP.EFFECTIVE_END_DATE)
FROM PER_ALL_PEOPLE_F PP
WHERE PP.PERSON_ID = U.EMPLOYEE_ID)
AND A.PERSON_ID = P.PERSON_ID
AND A.PRIMARY_FLAG = 'Y'
AND ((A.ASSIGNMENT_TYPE = 'E' AND P.CURRENT_EMPLOYEE_FLAG = 'Y') OR
(A.ASSIGNMENT_TYPE = 'C' AND P.CURRENT_NPW_FLAG = 'Y'))
AND A.EFFECTIVE_END_DATE =
(SELECT MAX(AA.EFFECTIVE_END_DATE)
FROM PER_ALL_ASSIGNMENTS_F AA
WHERE AA.PRIMARY_FLAG = 'Y'
AND AA.ASSIGNMENT_TYPE in ('E', 'C')
AND AA.PERSON_ID = P.PERSON_ID)
AND A.POSITION_ID = S.POSITION_ID(+)
AND TRUNC(SYSDATE) BETWEEN P.EFFECTIVE_START_DATE AND P.EFFECTIVE_END_DATE
AND HAS_NEED_TO_COPY_MEMBER(p_user_id, p_mgr_id,p_copy_type,
PNTM.USER_ID, PNTM.MEMBER_TYPE, F.BUSINESS_GROUP_ID,
P.BUSINESS_GROUP_ID, P.EFFECTIVE_START_DATE,
P.EFFECTIVE_END_DATE, U.START_DATE, U.END_DATE) = 'Y';
l_last_amendment_update(x) := 0;
l_last_amendment_update(l_count) := 0;
l_last_amendment_update(l_count) := 0;
l_last_amendment_update(l_count) := 0;
l_last_amendment_update(l_count) := 0;
INSERT INTO
PON_NEG_TEAM_MEMBERS
(
AUCTION_HEADER_ID,
LIST_ID,
USER_ID,
MENU_NAME,
MEMBER_TYPE,
APPROVER_FLAG,
APPROVAL_STATUS,
TASK_NAME,
TARGET_DATE,
COMPLETION_DATE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_AMENDMENT_UPDATE,
MODIFIED_FLAG,
LAST_NOTIFIED_DATE
)
VALUES
(
p_auction_header_id,
l_list_id(x),
l_user_id(x),
l_menu_name(x),
l_member_type(x),
l_approver_flag(x),
l_approval_status(x),
l_task_name(x),
l_target_date(x),
l_completion_date(x),
SYSDATE,
p_user_id,
SYSDATE,
p_user_id,
l_last_amendment_update(x),
l_modified_flag(x),
l_last_notif_date(x)
) ;
SELECT
FULL_NAME
BULK COLLECT
INTO
l_full_name
FROM (SELECT PNTM.USER_ID, P.FULL_NAME
FROM PON_NEG_TEAM_MEMBERS PNTM,
FND_USER U, PER_ALL_PEOPLE_F P
WHERE U.USER_ID = PNTM.USER_ID
AND PNTM.AUCTION_HEADER_ID = p_source_auction_header_id
AND PNTM.MEMBER_TYPE = 'N'
AND P.PERSON_ID = U.EMPLOYEE_ID
MINUS
SELECT PNTM.USER_ID, P.FULL_NAME
FROM PON_NEG_TEAM_MEMBERS PNTM,
FND_USER U, PER_ALL_PEOPLE_F P
WHERE U.USER_ID = PNTM.USER_ID
AND PNTM.AUCTION_HEADER_ID = p_auction_header_id
AND P.PERSON_ID = U.EMPLOYEE_ID);
INSERT INTO PON_PARTY_LINE_EXCLUSIONS
( AUCTION_HEADER_ID,
TRADING_PARTNER_ID,
VENDOR_SITE_ID,
LINE_NUMBER,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
SEQUENCE_NUMBER
)
(SELECT p_auction_header_id, -- AUCTION_HEADER_ID
ple.TRADING_PARTNER_ID,
ple.VENDOR_SITE_ID,
ple.LINE_NUMBER,
SYSDATE, -- CREATION_DATE
p_user_id, -- CREATED_BY
SYSDATE, -- LAST_UPDATE_DATE
p_user_id, -- LAST_UPDATED_BY
p_user_id, -- LAST_UPDATE_LOGIN
ple.SEQUENCE_NUMBER
FROM PON_PARTY_LINE_EXCLUSIONS ple, pon_bidding_parties pbp
WHERE ple.AUCTION_HEADER_ID = p_source_auction_header_id
AND ple.line_number >= p_from_line_number
AND ple.line_number <= p_to_line_number
AND pbp.auction_header_id = p_auction_header_id
AND ple.sequence_number = pbp.sequence
) ;
INSERT INTO PON_PF_SUPPLIER_VALUES
( AUCTION_HEADER_ID,
LINE_NUMBER,
PF_SEQ_NUMBER,
SUPPLIER_SEQ_NUMBER,
VALUE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
(SELECT p_auction_header_id, -- AUCTION_HEADER_ID
PPSV.LINE_NUMBER,
PPSV.PF_SEQ_NUMBER,
PPSV.SUPPLIER_SEQ_NUMBER,
PPSV.VALUE,
SYSDATE, -- CREATION_DATE
p_user_id, -- CREATED_BY
SYSDATE, -- LAST_UPDATE_DATE
p_user_id, -- LAST_UPDATED_BY
p_user_id -- LAST_UPDATE_LOGIN
FROM PON_PF_SUPPLIER_VALUES PPSV,
PON_PRICE_ELEMENTS PPE,
PON_PRICE_ELEMENT_TYPES_VL VL,
PON_BIDDING_PARTIES PBP
WHERE PPSV.AUCTION_HEADER_ID = p_source_auction_header_id AND
PPSV.AUCTION_HEADER_ID = PPE.AUCTION_HEADER_ID AND
PPSV.LINE_NUMBER = PPE.LINE_NUMBER AND
PPSV.PF_SEQ_NUMBER = PPE.SEQUENCE_NUMBER AND
PPE.PRICE_ELEMENT_TYPE_ID = VL.PRICE_ELEMENT_TYPE_ID AND
VL.ENABLED_FLAG = 'Y' AND
PPSV.line_number >= p_from_line_number AND
PPSV.line_number <= p_to_line_number AND
PBP.auction_header_id = p_auction_header_id AND
PBP.sequence = PPSV.SUPPLIER_SEQ_NUMBER
) ;
SELECT PFS.FORM_ID
INTO l_abstract_id
FROM PON_FORMS_SECTIONS PFS
WHERE PFS.FORM_CODE='ABSTRACT';
INSERT INTO PON_FORMS_INSTANCES
( ENTITY_CODE,
ENTITY_PK1,
FORM_ID,
STATUS,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
XML_LAST_SENT_DATE
)
(SELECT
FI.ENTITY_CODE,
TO_CHAR(p_auction_header_id),
FI.FORM_ID,
decode (FI.FORM_ID,
l_abstract_id, NULL,
FI.STATUS),
SYSDATE,
p_user_id,
SYSDATE,
p_user_id,
p_user_id,
XML_LAST_SENT_DATE
FROM PON_FORMS_SECTIONS FS,
PON_FORMS_INSTANCES FI
WHERE FI.ENTITY_CODE = 'PON_AUCTION_HEADERS_ALL'
AND FI.ENTITY_PK1 = TO_CHAR(p_source_auction_header_id)
AND FI.FORM_ID = FS.FORM_ID
AND FS.STATUS = 'ACTIVE');
INSERT INTO PON_FORMS_INSTANCES
( ENTITY_CODE,
ENTITY_PK1,
FORM_ID,
STATUS,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
XML_LAST_SENT_DATE
)
(SELECT
FI.ENTITY_CODE,
TO_CHAR(p_auction_header_id),
FI.FORM_ID,
decode (FI.FORM_ID,
l_abstract_id, NULL,
'NOT_ENTERED'), -- STATUS
SYSDATE,
p_user_id,
SYSDATE,
p_user_id,
p_user_id,
NULL
FROM PON_FORMS_INSTANCES FI,
PON_FORMS_SECTIONS FS
WHERE ENTITY_CODE = 'PON_AUCTION_HEADERS_ALL'
AND ENTITY_PK1 = TO_CHAR(p_source_auction_header_id)
AND FI.FORM_ID = FS.FORM_ID
AND FS.STATUS = 'ACTIVE'
AND (Nvl(l_is_Federal,0)<>1 OR FS.FORM_CODE='ABSTRACT' ));
SELECT
COUNT(1)
INTO
l_inactive_sections
FROM PON_FORMS_INSTANCES FI,
PON_FORMS_SECTIONS FS
WHERE FI.ENTITY_CODE = 'PON_AUCTION_HEADERS_ALL'
AND FI.ENTITY_PK1 = TO_CHAR(p_source_auction_header_id)
AND FI.FORM_ID = FS.FORM_ID
AND FS.STATUS = 'INACTIVE'
AND (Nvl(l_is_Federal,0)<>1 OR FS.FORM_CODE='ABSTRACT' ); -- FedBizOpps changes
SELECT
FI.FORM_ID
FROM PON_FORMS_INSTANCES FI,
PON_FORMS_SECTIONS FS
WHERE FI.ENTITY_CODE = 'PON_AUCTION_HEADERS_ALL'
AND FI.ENTITY_PK1 = TO_CHAR(p_source_auction_header_id)
AND FI.FORM_ID = FS.FORM_ID
AND FS.STATUS = 'ACTIVE'
AND (PON_CLM_UTIL_PKG.IS_NEG_DOCUMENT_FEDERAL(p_auction_header_id) <>1 OR
FS.FORM_CODE='ABSTRACT'); -- Forms won't be copied for solicitations
SELECT
COUNT(1)
INTO
l_has_children
FROM PON_FORM_FIELD_VALUES
WHERE FORM_ID = form.FORM_ID
AND OWNING_ENTITY_CODE = 'PON_AUCTION_HEADERS_ALL'
AND ENTITY_PK1 = TO_CHAR(p_source_auction_header_id)
AND PARENT_FIELD_VALUES_FK = -1;
SELECT
FORM_FIELD_VALUE_ID
INTO
l_orig_parent_field_val_fk
FROM PON_FORM_FIELD_VALUES
WHERE FORM_ID = form.FORM_ID
AND OWNING_ENTITY_CODE = 'PON_AUCTION_HEADERS_ALL'
AND ENTITY_PK1 = TO_CHAR(p_source_auction_header_id)
AND PARENT_FIELD_VALUES_FK = -1;
SELECT
FORM_FIELD_VALUE_ID,
FORM_ID ,
OWNING_ENTITY_CODE ,
ENTITY_PK1 ,
SECTION_ID ,
PARENT_FIELD_VALUES_FK ,
TEXTCOL1 ,
TEXTCOL2 ,
TEXTCOL3 ,
TEXTCOL4 ,
TEXTCOL5 ,
TEXTCOL6 ,
TEXTCOL7 ,
TEXTCOL8 ,
TEXTCOL9 ,
TEXTCOL10,
TEXTCOL11,
TEXTCOL12,
TEXTCOL13,
TEXTCOL14,
TEXTCOL15,
TEXTCOL16,
TEXTCOL17,
TEXTCOL18,
TEXTCOL19,
TEXTCOL20,
TEXTCOL21,
TEXTCOL22,
TEXTCOL23,
TEXTCOL24,
TEXTCOL25,
TEXTCOL26,
TEXTCOL27,
TEXTCOL28,
TEXTCOL29,
TEXTCOL30,
TEXTCOL31,
TEXTCOL32,
TEXTCOL33,
TEXTCOL34,
TEXTCOL35,
TEXTCOL36,
TEXTCOL37,
TEXTCOL38,
TEXTCOL39,
TEXTCOL40,
TEXTCOL41,
TEXTCOL42,
TEXTCOL43,
TEXTCOL44,
TEXTCOL45,
TEXTCOL46,
TEXTCOL47,
TEXTCOL48,
TEXTCOL49,
TEXTCOL50,
TEXTCOL51,
TEXTCOL52,
TEXTCOL53,
TEXTCOL54,
TEXTCOL55,
TEXTCOL56,
TEXTCOL57,
TEXTCOL58,
TEXTCOL59,
TEXTCOL60,
TEXTCOL61,
TEXTCOL62,
TEXTCOL63,
TEXTCOL64,
TEXTCOL65,
TEXTCOL66,
TEXTCOL67,
TEXTCOL68,
TEXTCOL69,
TEXTCOL70,
TEXTCOL71,
TEXTCOL72,
TEXTCOL73,
TEXTCOL74,
TEXTCOL75,
TEXTCOL76,
TEXTCOL77,
TEXTCOL78,
TEXTCOL79,
TEXTCOL80,
TEXTCOL81,
TEXTCOL82,
TEXTCOL83,
TEXTCOL84,
TEXTCOL85,
TEXTCOL86,
TEXTCOL87,
TEXTCOL88,
TEXTCOL89,
TEXTCOL90,
TEXTCOL91,
TEXTCOL92,
TEXTCOL93,
TEXTCOL94,
TEXTCOL95,
TEXTCOL96,
TEXTCOL97,
TEXTCOL98,
TEXTCOL99,
TEXTCOL100 ,
TEXTCOL101 ,
TEXTCOL102 ,
TEXTCOL103 ,
TEXTCOL104 ,
TEXTCOL105 ,
TEXTCOL106 ,
TEXTCOL107 ,
TEXTCOL108 ,
TEXTCOL109 ,
TEXTCOL110 ,
TEXTCOL111 ,
TEXTCOL112 ,
TEXTCOL113 ,
TEXTCOL114 ,
TEXTCOL115 ,
TEXTCOL116 ,
TEXTCOL117 ,
TEXTCOL118 ,
TEXTCOL119 ,
TEXTCOL120 ,
TEXTCOL121 ,
TEXTCOL122 ,
TEXTCOL123 ,
TEXTCOL124 ,
TEXTCOL125 ,
TEXTCOL126 ,
TEXTCOL127 ,
TEXTCOL128 ,
TEXTCOL129 ,
TEXTCOL130 ,
TEXTCOL131 ,
TEXTCOL132 ,
TEXTCOL133 ,
TEXTCOL134 ,
TEXTCOL135 ,
TEXTCOL136 ,
TEXTCOL137 ,
TEXTCOL138 ,
TEXTCOL139 ,
TEXTCOL140 ,
TEXTCOL141 ,
TEXTCOL142 ,
TEXTCOL143 ,
TEXTCOL144 ,
TEXTCOL145 ,
TEXTCOL146 ,
TEXTCOL147 ,
TEXTCOL148 ,
TEXTCOL149 ,
TEXTCOL150 ,
TEXTCOL151 ,
TEXTCOL152 ,
TEXTCOL153 ,
TEXTCOL154 ,
TEXTCOL155 ,
TEXTCOL156 ,
TEXTCOL157 ,
TEXTCOL158 ,
TEXTCOL159 ,
TEXTCOL160 ,
TEXTCOL161 ,
TEXTCOL162 ,
TEXTCOL163 ,
TEXTCOL164 ,
TEXTCOL165 ,
TEXTCOL166 ,
TEXTCOL167 ,
TEXTCOL168 ,
TEXTCOL169 ,
TEXTCOL170 ,
TEXTCOL171 ,
TEXTCOL172 ,
TEXTCOL173 ,
TEXTCOL174 ,
TEXTCOL175 ,
TEXTCOL176 ,
TEXTCOL177 ,
TEXTCOL178 ,
TEXTCOL179 ,
TEXTCOL180 ,
TEXTCOL181 ,
TEXTCOL182 ,
TEXTCOL183 ,
TEXTCOL184 ,
TEXTCOL185 ,
TEXTCOL186 ,
TEXTCOL187 ,
TEXTCOL188 ,
TEXTCOL189 ,
TEXTCOL190 ,
TEXTCOL191 ,
TEXTCOL192 ,
TEXTCOL193 ,
TEXTCOL194 ,
TEXTCOL195 ,
TEXTCOL196 ,
TEXTCOL197 ,
TEXTCOL198 ,
TEXTCOL199 ,
TEXTCOL200 ,
TEXTCOL201 ,
TEXTCOL202 ,
TEXTCOL203 ,
TEXTCOL204 ,
TEXTCOL205 ,
TEXTCOL206 ,
TEXTCOL207 ,
TEXTCOL208 ,
TEXTCOL209 ,
TEXTCOL210 ,
TEXTCOL211 ,
TEXTCOL212 ,
TEXTCOL213 ,
TEXTCOL214 ,
TEXTCOL215 ,
TEXTCOL216 ,
TEXTCOL217 ,
TEXTCOL218 ,
TEXTCOL219 ,
TEXTCOL220 ,
TEXTCOL221 ,
TEXTCOL222 ,
TEXTCOL223 ,
TEXTCOL224 ,
TEXTCOL225 ,
TEXTCOL226 ,
TEXTCOL227 ,
TEXTCOL228 ,
TEXTCOL229 ,
TEXTCOL230 ,
TEXTCOL231 ,
TEXTCOL232 ,
TEXTCOL233 ,
TEXTCOL234 ,
TEXTCOL235 ,
TEXTCOL236 ,
TEXTCOL237 ,
TEXTCOL238 ,
TEXTCOL239 ,
TEXTCOL240 ,
TEXTCOL241 ,
TEXTCOL242 ,
TEXTCOL243 ,
TEXTCOL244 ,
TEXTCOL245 ,
TEXTCOL246 ,
TEXTCOL247 ,
TEXTCOL248 ,
TEXTCOL249 ,
TEXTCOL250 ,
DATECOL1 ,
DATECOL2 ,
DATECOL3 ,
DATECOL4 ,
DATECOL5 ,
DATECOL6 ,
DATECOL7 ,
DATECOL8 ,
DATECOL9 ,
DATECOL10,
DATECOL11,
DATECOL12,
DATECOL13,
DATECOL14,
DATECOL15,
DATECOL16,
DATECOL17,
DATECOL18,
DATECOL19,
DATECOL20,
DATECOL21,
DATECOL22,
DATECOL23,
DATECOL24,
DATECOL25,
DATECOL26,
DATECOL27,
DATECOL28,
DATECOL29,
DATECOL30,
DATECOL31,
DATECOL32,
DATECOL33,
DATECOL34,
DATECOL35,
DATECOL36,
DATECOL37,
DATECOL38,
DATECOL39,
DATECOL40,
DATECOL41,
DATECOL42,
DATECOL43,
DATECOL44,
DATECOL45,
DATECOL46,
DATECOL47,
DATECOL48,
DATECOL49,
DATECOL50 ,
NUMBERCOL1 ,
NUMBERCOL2 ,
NUMBERCOL3 ,
NUMBERCOL4 ,
NUMBERCOL5 ,
NUMBERCOL6 ,
NUMBERCOL7 ,
NUMBERCOL8 ,
NUMBERCOL9 ,
NUMBERCOL10,
NUMBERCOL11,
NUMBERCOL12,
NUMBERCOL13,
NUMBERCOL14,
NUMBERCOL15,
NUMBERCOL16,
NUMBERCOL17,
NUMBERCOL18,
NUMBERCOL19,
NUMBERCOL20,
NUMBERCOL21,
NUMBERCOL22,
NUMBERCOL23,
NUMBERCOL24,
NUMBERCOL25,
NUMBERCOL26,
NUMBERCOL27,
NUMBERCOL28,
NUMBERCOL29,
NUMBERCOL30,
NUMBERCOL31,
NUMBERCOL32,
NUMBERCOL33,
NUMBERCOL34,
NUMBERCOL35,
NUMBERCOL36,
NUMBERCOL37,
NUMBERCOL38,
NUMBERCOL39,
NUMBERCOL40,
NUMBERCOL41,
NUMBERCOL42,
NUMBERCOL43,
NUMBERCOL44,
NUMBERCOL45,
NUMBERCOL46,
NUMBERCOL47,
NUMBERCOL48,
NUMBERCOL49,
NUMBERCOL50,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN
FROM PON_FORM_FIELD_VALUES
WHERE PARENT_FIELD_VALUES_FK = p_orig_parent_fld_values_fk
AND FORM_ID = p_form_id
AND ENTITY_PK1 = p_old_entity_pk1
AND OWNING_ENTITY_CODE='PON_AUCTION_HEADERS_ALL';
SELECT
PON_FORM_FIELD_VALUES_S.NEXTVAL
INTO
l_new_field_values_fk
FROM DUAL;
INSERT INTO
PON_FORM_FIELD_VALUES
(
FORM_FIELD_VALUE_ID,
FORM_ID,
OWNING_ENTITY_CODE,
ENTITY_PK1,
SECTION_ID,
PARENT_FIELD_VALUES_FK,
TEXTCOL1,
TEXTCOL2,
TEXTCOL3,
TEXTCOL4,
TEXTCOL5,
TEXTCOL6,
TEXTCOL7,
TEXTCOL8,
TEXTCOL9,
TEXTCOL10,
TEXTCOL11,
TEXTCOL12,
TEXTCOL13,
TEXTCOL14,
TEXTCOL15,
TEXTCOL16,
TEXTCOL17,
TEXTCOL18,
TEXTCOL19,
TEXTCOL20,
TEXTCOL21,
TEXTCOL22,
TEXTCOL23,
TEXTCOL24,
TEXTCOL25,
TEXTCOL26,
TEXTCOL27,
TEXTCOL28,
TEXTCOL29,
TEXTCOL30,
TEXTCOL31,
TEXTCOL32,
TEXTCOL33,
TEXTCOL34,
TEXTCOL35,
TEXTCOL36,
TEXTCOL37,
TEXTCOL38,
TEXTCOL39,
TEXTCOL40,
TEXTCOL41,
TEXTCOL42,
TEXTCOL43,
TEXTCOL44,
TEXTCOL45,
TEXTCOL46,
TEXTCOL47,
TEXTCOL48,
TEXTCOL49,
TEXTCOL50,
TEXTCOL51,
TEXTCOL52,
TEXTCOL53,
TEXTCOL54,
TEXTCOL55,
TEXTCOL56,
TEXTCOL57,
TEXTCOL58,
TEXTCOL59,
TEXTCOL60,
TEXTCOL61,
TEXTCOL62,
TEXTCOL63,
TEXTCOL64,
TEXTCOL65,
TEXTCOL66,
TEXTCOL67,
TEXTCOL68,
TEXTCOL69,
TEXTCOL70,
TEXTCOL71,
TEXTCOL72,
TEXTCOL73,
TEXTCOL74,
TEXTCOL75,
TEXTCOL76,
TEXTCOL77,
TEXTCOL78,
TEXTCOL79,
TEXTCOL80,
TEXTCOL81,
TEXTCOL82,
TEXTCOL83,
TEXTCOL84,
TEXTCOL85,
TEXTCOL86,
TEXTCOL87,
TEXTCOL88,
TEXTCOL89,
TEXTCOL90,
TEXTCOL91,
TEXTCOL92,
TEXTCOL93,
TEXTCOL94,
TEXTCOL95,
TEXTCOL96,
TEXTCOL97,
TEXTCOL98,
TEXTCOL99,
TEXTCOL100,
TEXTCOL101,
TEXTCOL102,
TEXTCOL103,
TEXTCOL104,
TEXTCOL105,
TEXTCOL106,
TEXTCOL107,
TEXTCOL108,
TEXTCOL109,
TEXTCOL110,
TEXTCOL111,
TEXTCOL112,
TEXTCOL113,
TEXTCOL114,
TEXTCOL115,
TEXTCOL116,
TEXTCOL117,
TEXTCOL118,
TEXTCOL119,
TEXTCOL120,
TEXTCOL121,
TEXTCOL122,
TEXTCOL123,
TEXTCOL124,
TEXTCOL125,
TEXTCOL126,
TEXTCOL127,
TEXTCOL128,
TEXTCOL129,
TEXTCOL130,
TEXTCOL131,
TEXTCOL132,
TEXTCOL133,
TEXTCOL134,
TEXTCOL135,
TEXTCOL136,
TEXTCOL137,
TEXTCOL138,
TEXTCOL139,
TEXTCOL140,
TEXTCOL141,
TEXTCOL142,
TEXTCOL143,
TEXTCOL144,
TEXTCOL145,
TEXTCOL146,
TEXTCOL147,
TEXTCOL148,
TEXTCOL149,
TEXTCOL150,
TEXTCOL151,
TEXTCOL152,
TEXTCOL153,
TEXTCOL154,
TEXTCOL155,
TEXTCOL156,
TEXTCOL157,
TEXTCOL158,
TEXTCOL159,
TEXTCOL160,
TEXTCOL161,
TEXTCOL162,
TEXTCOL163,
TEXTCOL164,
TEXTCOL165,
TEXTCOL166,
TEXTCOL167,
TEXTCOL168,
TEXTCOL169,
TEXTCOL170,
TEXTCOL171,
TEXTCOL172,
TEXTCOL173,
TEXTCOL174,
TEXTCOL175,
TEXTCOL176,
TEXTCOL177,
TEXTCOL178,
TEXTCOL179,
TEXTCOL180,
TEXTCOL181,
TEXTCOL182,
TEXTCOL183,
TEXTCOL184,
TEXTCOL185,
TEXTCOL186,
TEXTCOL187,
TEXTCOL188,
TEXTCOL189,
TEXTCOL190,
TEXTCOL191,
TEXTCOL192,
TEXTCOL193,
TEXTCOL194,
TEXTCOL195,
TEXTCOL196,
TEXTCOL197,
TEXTCOL198,
TEXTCOL199,
TEXTCOL200,
TEXTCOL201,
TEXTCOL202,
TEXTCOL203,
TEXTCOL204,
TEXTCOL205,
TEXTCOL206,
TEXTCOL207,
TEXTCOL208,
TEXTCOL209,
TEXTCOL210,
TEXTCOL211,
TEXTCOL212,
TEXTCOL213,
TEXTCOL214,
TEXTCOL215,
TEXTCOL216,
TEXTCOL217,
TEXTCOL218,
TEXTCOL219,
TEXTCOL220,
TEXTCOL221,
TEXTCOL222,
TEXTCOL223,
TEXTCOL224,
TEXTCOL225,
TEXTCOL226,
TEXTCOL227,
TEXTCOL228,
TEXTCOL229,
TEXTCOL230,
TEXTCOL231,
TEXTCOL232,
TEXTCOL233,
TEXTCOL234,
TEXTCOL235,
TEXTCOL236,
TEXTCOL237,
TEXTCOL238,
TEXTCOL239,
TEXTCOL240,
TEXTCOL241,
TEXTCOL242,
TEXTCOL243,
TEXTCOL244,
TEXTCOL245,
TEXTCOL246,
TEXTCOL247,
TEXTCOL248,
TEXTCOL249,
TEXTCOL250,
DATECOL1,
DATECOL2,
DATECOL3,
DATECOL4,
DATECOL5,
DATECOL6,
DATECOL7,
DATECOL8,
DATECOL9,
DATECOL10,
DATECOL11,
DATECOL12,
DATECOL13,
DATECOL14,
DATECOL15,
DATECOL16,
DATECOL17,
DATECOL18,
DATECOL19,
DATECOL20,
DATECOL21,
DATECOL22,
DATECOL23,
DATECOL24,
DATECOL25,
DATECOL26,
DATECOL27,
DATECOL28,
DATECOL29,
DATECOL30,
DATECOL31,
DATECOL32,
DATECOL33,
DATECOL34,
DATECOL35,
DATECOL36,
DATECOL37,
DATECOL38,
DATECOL39,
DATECOL40,
DATECOL41,
DATECOL42,
DATECOL43,
DATECOL44,
DATECOL45,
DATECOL46,
DATECOL47,
DATECOL48,
DATECOL49,
DATECOL50,
NUMBERCOL1,
NUMBERCOL2,
NUMBERCOL3,
NUMBERCOL4,
NUMBERCOL5,
NUMBERCOL6,
NUMBERCOL7,
NUMBERCOL8,
NUMBERCOL9,
NUMBERCOL10,
NUMBERCOL11,
NUMBERCOL12,
NUMBERCOL13,
NUMBERCOL14,
NUMBERCOL15,
NUMBERCOL16,
NUMBERCOL17,
NUMBERCOL18,
NUMBERCOL19,
NUMBERCOL20,
NUMBERCOL21,
NUMBERCOL22,
NUMBERCOL23,
NUMBERCOL24,
NUMBERCOL25,
NUMBERCOL26,
NUMBERCOL27,
NUMBERCOL28,
NUMBERCOL29,
NUMBERCOL30,
NUMBERCOL31,
NUMBERCOL32,
NUMBERCOL33,
NUMBERCOL34,
NUMBERCOL35,
NUMBERCOL36,
NUMBERCOL37,
NUMBERCOL38,
NUMBERCOL39,
NUMBERCOL40,
NUMBERCOL41,
NUMBERCOL42,
NUMBERCOL43,
NUMBERCOL44,
NUMBERCOL45,
NUMBERCOL46,
NUMBERCOL47,
NUMBERCOL48,
NUMBERCOL49,
NUMBERCOL50,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
VALUES
(
l_new_field_values_fk,
field_value.FORM_ID,
'PON_AUCTION_HEADERS_ALL',
p_new_entity_pk1,
field_value.SECTION_ID,
p_new_parent_field_values_fk,
field_value.TEXTCOL1,
field_value.TEXTCOL2,
field_value.TEXTCOL3,
field_value.TEXTCOL4,
field_value.TEXTCOL5,
field_value.TEXTCOL6,
field_value.TEXTCOL7,
field_value.TEXTCOL8,
field_value.TEXTCOL9,
field_value.TEXTCOL10,
field_value.TEXTCOL11,
field_value.TEXTCOL12,
field_value.TEXTCOL13,
field_value.TEXTCOL14,
field_value.TEXTCOL15,
field_value.TEXTCOL16,
field_value.TEXTCOL17,
field_value.TEXTCOL18,
field_value.TEXTCOL19,
field_value.TEXTCOL20,
field_value.TEXTCOL21,
field_value.TEXTCOL22,
field_value.TEXTCOL23,
field_value.TEXTCOL24,
field_value.TEXTCOL25,
field_value.TEXTCOL26,
field_value.TEXTCOL27,
field_value.TEXTCOL28,
field_value.TEXTCOL29,
field_value.TEXTCOL30,
field_value.TEXTCOL31,
field_value.TEXTCOL32,
field_value.TEXTCOL33,
field_value.TEXTCOL34,
field_value.TEXTCOL35,
field_value.TEXTCOL36,
field_value.TEXTCOL37,
field_value.TEXTCOL38,
field_value.TEXTCOL39,
field_value.TEXTCOL40,
field_value.TEXTCOL41,
field_value.TEXTCOL42,
field_value.TEXTCOL43,
field_value.TEXTCOL44,
field_value.TEXTCOL45,
field_value.TEXTCOL46,
field_value.TEXTCOL47,
field_value.TEXTCOL48,
field_value.TEXTCOL49,
field_value.TEXTCOL50,
field_value.TEXTCOL51,
field_value.TEXTCOL52,
field_value.TEXTCOL53,
field_value.TEXTCOL54,
field_value.TEXTCOL55,
field_value.TEXTCOL56,
field_value.TEXTCOL57,
field_value.TEXTCOL58,
field_value.TEXTCOL59,
field_value.TEXTCOL60,
field_value.TEXTCOL61,
field_value.TEXTCOL62,
field_value.TEXTCOL63,
field_value.TEXTCOL64,
field_value.TEXTCOL65,
field_value.TEXTCOL66,
field_value.TEXTCOL67,
field_value.TEXTCOL68,
field_value.TEXTCOL69,
field_value.TEXTCOL70,
field_value.TEXTCOL71,
field_value.TEXTCOL72,
field_value.TEXTCOL73,
field_value.TEXTCOL74,
field_value.TEXTCOL75,
field_value.TEXTCOL76,
field_value.TEXTCOL77,
field_value.TEXTCOL78,
field_value.TEXTCOL79,
field_value.TEXTCOL80,
field_value.TEXTCOL81,
field_value.TEXTCOL82,
field_value.TEXTCOL83,
field_value.TEXTCOL84,
field_value.TEXTCOL85,
field_value.TEXTCOL86,
field_value.TEXTCOL87,
field_value.TEXTCOL88,
field_value.TEXTCOL89,
field_value.TEXTCOL90,
field_value.TEXTCOL91,
field_value.TEXTCOL92,
field_value.TEXTCOL93,
field_value.TEXTCOL94,
field_value.TEXTCOL95,
field_value.TEXTCOL96,
field_value.TEXTCOL97,
field_value.TEXTCOL98,
field_value.TEXTCOL99,
field_value.TEXTCOL100,
field_value.TEXTCOL101,
field_value.TEXTCOL102,
field_value.TEXTCOL103,
field_value.TEXTCOL104,
field_value.TEXTCOL105,
field_value.TEXTCOL106,
field_value.TEXTCOL107,
field_value.TEXTCOL108,
field_value.TEXTCOL109,
field_value.TEXTCOL110,
field_value.TEXTCOL111,
field_value.TEXTCOL112,
field_value.TEXTCOL113,
field_value.TEXTCOL114,
field_value.TEXTCOL115,
field_value.TEXTCOL116,
field_value.TEXTCOL117,
field_value.TEXTCOL118,
field_value.TEXTCOL119,
field_value.TEXTCOL120,
field_value.TEXTCOL121,
field_value.TEXTCOL122,
field_value.TEXTCOL123,
field_value.TEXTCOL124,
field_value.TEXTCOL125,
field_value.TEXTCOL126,
field_value.TEXTCOL127,
field_value.TEXTCOL128,
field_value.TEXTCOL129,
field_value.TEXTCOL130,
field_value.TEXTCOL131,
field_value.TEXTCOL132,
field_value.TEXTCOL133,
field_value.TEXTCOL134,
field_value.TEXTCOL135,
field_value.TEXTCOL136,
field_value.TEXTCOL137,
field_value.TEXTCOL138,
field_value.TEXTCOL139,
field_value.TEXTCOL140,
field_value.TEXTCOL141,
field_value.TEXTCOL142,
field_value.TEXTCOL143,
field_value.TEXTCOL144,
field_value.TEXTCOL145,
field_value.TEXTCOL146,
field_value.TEXTCOL147,
field_value.TEXTCOL148,
field_value.TEXTCOL149,
field_value.TEXTCOL150,
field_value.TEXTCOL151,
field_value.TEXTCOL152,
field_value.TEXTCOL153,
field_value.TEXTCOL154,
field_value.TEXTCOL155,
field_value.TEXTCOL156,
field_value.TEXTCOL157,
field_value.TEXTCOL158,
field_value.TEXTCOL159,
field_value.TEXTCOL160,
field_value.TEXTCOL161,
field_value.TEXTCOL162,
field_value.TEXTCOL163,
field_value.TEXTCOL164,
field_value.TEXTCOL165,
field_value.TEXTCOL166,
field_value.TEXTCOL167,
field_value.TEXTCOL168,
field_value.TEXTCOL169,
field_value.TEXTCOL170,
field_value.TEXTCOL171,
field_value.TEXTCOL172,
field_value.TEXTCOL173,
field_value.TEXTCOL174,
field_value.TEXTCOL175,
field_value.TEXTCOL176,
field_value.TEXTCOL177,
field_value.TEXTCOL178,
field_value.TEXTCOL179,
field_value.TEXTCOL180,
field_value.TEXTCOL181,
field_value.TEXTCOL182,
field_value.TEXTCOL183,
field_value.TEXTCOL184,
field_value.TEXTCOL185,
field_value.TEXTCOL186,
field_value.TEXTCOL187,
field_value.TEXTCOL188,
field_value.TEXTCOL189,
field_value.TEXTCOL190,
field_value.TEXTCOL191,
field_value.TEXTCOL192,
field_value.TEXTCOL193,
field_value.TEXTCOL194,
field_value.TEXTCOL195,
field_value.TEXTCOL196,
field_value.TEXTCOL197,
field_value.TEXTCOL198,
field_value.TEXTCOL199,
field_value.TEXTCOL200,
field_value.TEXTCOL201,
field_value.TEXTCOL202,
field_value.TEXTCOL203,
field_value.TEXTCOL204,
field_value.TEXTCOL205,
field_value.TEXTCOL206,
field_value.TEXTCOL207,
field_value.TEXTCOL208,
field_value.TEXTCOL209,
field_value.TEXTCOL210,
field_value.TEXTCOL211,
field_value.TEXTCOL212,
field_value.TEXTCOL213,
field_value.TEXTCOL214,
field_value.TEXTCOL215,
field_value.TEXTCOL216,
field_value.TEXTCOL217,
field_value.TEXTCOL218,
field_value.TEXTCOL219,
field_value.TEXTCOL220,
field_value.TEXTCOL221,
field_value.TEXTCOL222,
field_value.TEXTCOL223,
field_value.TEXTCOL224,
field_value.TEXTCOL225,
field_value.TEXTCOL226,
field_value.TEXTCOL227,
field_value.TEXTCOL228,
field_value.TEXTCOL229,
field_value.TEXTCOL230,
field_value.TEXTCOL231,
field_value.TEXTCOL232,
field_value.TEXTCOL233,
field_value.TEXTCOL234,
field_value.TEXTCOL235,
field_value.TEXTCOL236,
field_value.TEXTCOL237,
field_value.TEXTCOL238,
field_value.TEXTCOL239,
field_value.TEXTCOL240,
field_value.TEXTCOL241,
field_value.TEXTCOL242,
field_value.TEXTCOL243,
field_value.TEXTCOL244,
field_value.TEXTCOL245,
field_value.TEXTCOL246,
field_value.TEXTCOL247,
field_value.TEXTCOL248,
field_value.TEXTCOL249,
field_value.TEXTCOL250,
field_value.DATECOL1,
field_value.DATECOL2,
field_value.DATECOL3,
field_value.DATECOL4,
field_value.DATECOL5,
field_value.DATECOL6,
field_value.DATECOL7,
field_value.DATECOL8,
field_value.DATECOL9,
field_value.DATECOL10,
field_value.DATECOL11,
field_value.DATECOL12,
field_value.DATECOL13,
field_value.DATECOL14,
field_value.DATECOL15,
field_value.DATECOL16,
field_value.DATECOL17,
field_value.DATECOL18,
field_value.DATECOL19,
field_value.DATECOL20,
field_value.DATECOL21,
field_value.DATECOL22,
field_value.DATECOL23,
field_value.DATECOL24,
field_value.DATECOL25,
field_value.DATECOL26,
field_value.DATECOL27,
field_value.DATECOL28,
field_value.DATECOL29,
field_value.DATECOL30,
field_value.DATECOL31,
field_value.DATECOL32,
field_value.DATECOL33,
field_value.DATECOL34,
field_value.DATECOL35,
field_value.DATECOL36,
field_value.DATECOL37,
field_value.DATECOL38,
field_value.DATECOL39,
field_value.DATECOL40,
field_value.DATECOL41,
field_value.DATECOL42,
field_value.DATECOL43,
field_value.DATECOL44,
field_value.DATECOL45,
field_value.DATECOL46,
field_value.DATECOL47,
field_value.DATECOL48,
field_value.DATECOL49,
field_value.DATECOL50,
field_value.NUMBERCOL1,
field_value.NUMBERCOL2,
field_value.NUMBERCOL3,
field_value.NUMBERCOL4,
field_value.NUMBERCOL5,
field_value.NUMBERCOL6,
field_value.NUMBERCOL7,
field_value.NUMBERCOL8,
field_value.NUMBERCOL9,
field_value.NUMBERCOL10,
field_value.NUMBERCOL11,
field_value.NUMBERCOL12,
field_value.NUMBERCOL13,
field_value.NUMBERCOL14,
field_value.NUMBERCOL15,
field_value.NUMBERCOL16,
field_value.NUMBERCOL17,
field_value.NUMBERCOL18,
field_value.NUMBERCOL19,
field_value.NUMBERCOL20,
field_value.NUMBERCOL21,
field_value.NUMBERCOL22,
field_value.NUMBERCOL23,
field_value.NUMBERCOL24,
field_value.NUMBERCOL25,
field_value.NUMBERCOL26,
field_value.NUMBERCOL27,
field_value.NUMBERCOL28,
field_value.NUMBERCOL29,
field_value.NUMBERCOL30,
field_value.NUMBERCOL31,
field_value.NUMBERCOL32,
field_value.NUMBERCOL33,
field_value.NUMBERCOL34,
field_value.NUMBERCOL35,
field_value.NUMBERCOL36,
field_value.NUMBERCOL37,
field_value.NUMBERCOL38,
field_value.NUMBERCOL39,
field_value.NUMBERCOL40,
field_value.NUMBERCOL41,
field_value.NUMBERCOL42,
field_value.NUMBERCOL43,
field_value.NUMBERCOL44,
field_value.NUMBERCOL45,
field_value.NUMBERCOL46,
field_value.NUMBERCOL47,
field_value.NUMBERCOL48,
field_value.NUMBERCOL49,
field_value.NUMBERCOL50,
SYSDATE,
p_user_id,
SYSDATE,
p_user_id,
p_user_id);
p_update_clause IN VARCHAR2,
p_retain_attachments IN VARCHAR2,
p_tpc_name IN VARCHAR2,
p_tp_name IN VARCHAR2,
p_source_doctype_id IN NUMBER,
p_org_id IN NUMBER,
p_round_number IN NUMBER,
p_last_amendment_number IN NUMBER,
p_source_doc_num IN VARCHAR2,
p_style_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
CALL_COPY_PF_SUPPLIER_VALUES VARCHAR2(25):='FALSE' ;
l_error_code_update VARCHAR2(2000);
l_error_msg_update VARCHAR2(2000);
select nvl(headers.LARGE_NEG_ENABLED_FLAG,'N'),
headers.AUCTION_STATUS,
nvl(styles.LARGE_NEG_ENABLED_FLAG,'N'),
staggered_closing_interval into IS_LARGE_SOURCE,CURRENT_STATUS,IS_LARGE_DESTINATION,l_staggered_closing_interval
from
PON_AUCTION_HEADERS_ALL headers,
PON_NEGOTIATION_STYLES styles
where
headers.AUCTION_HEADER_ID = p_source_auction_header_id and
styles.STYLE_ID = p_style_id;
LOG_MESSAGE('copy_lines_and_children','Handling the impact of styles on cross copy; selecting the flags for destination auction');
SELECT
NVL(hdr.LINE_MAS_ENABLED_FLAG, 'Y'),
NVL (hdr.LINE_ATTRIBUTE_ENABLED_FLAG, 'Y'),
NVL (hdr.RFI_LINE_ENABLED_FLAG, 'Y'),
NVL(hdr.PRICE_ELEMENT_ENABLED_FLAG, 'Y'),
NVL(hdr.po_style_id, -9999),
doctypes.DOCTYPE_GROUP_NAME,
NVL(hdr.HDR_ATTRIBUTE_ENABLED_FLAG,'Y')
INTO
l_d_mas_enabled_flag,
l_d_line_attr_enabled_flag,
l_d_rfi_line_enabled_flag,
l_d_pe_enabled_flag,
l_d_po_style_id,
l_d_doctype_name,
l_d_hdr_attr_enabled_flag
FROM
PON_AUCTION_HEADERS_ALL hdr,
PON_AUC_DOCTYPES doctypes
WHERE
hdr.auction_header_id = l_auction_header_id AND
doctypes.DOCTYPE_ID = p_doctype_id;
SELECT
hdr.BID_RANKING,
NVL(hdr.LINE_ATTRIBUTE_ENABLED_FLAG, 'Y'),
NVL(hdr.RFI_LINE_ENABLED_FLAG, 'Y'),
NVL(hdr.PF_TYPE_ALLOWED, 'BOTH'),
hdr.CONTRACT_TYPE,
NVL(hdr.GLOBAL_AGREEMENT_FLAG, 'N'),
hdr.ATTRIBUTES_EXIST,
doctypes.DOCTYPE_GROUP_NAME,
NVL(hdr.HAS_HDR_ATTR_FLAG,'N'),
NVL(hdr.HDR_ATTRIBUTE_ENABLED_FLAG,'Y'),
NVL(hdr.PRICE_ELEMENT_ENABLED_FLAG,'Y')
INTO
l_s_bid_ranking,
l_s_line_attr_enabled_flag,
l_s_rfi_line_enabled_flag,
l_s_pf_type_allowed,
l_s_contract_type,
l_s_global_agmt_flag,
l_s_attributes_exist,
l_s_doctype_name,
l_s_has_hdr_attr_flag,
l_s_hdr_attr_enabled_flag,
l_s_pe_enabled_flag
FROM
PON_AUCTION_HEADERS_ALL hdr,
PON_AUC_DOCTYPES doctypes
WHERE
auction_header_id = p_source_auction_header_id AND
doctypes.DOCTYPE_ID = hdr.DOCTYPE_ID;
LOG_MESSAGE('copy_lines_and_children','This is an ordinary auction; Selecting the flags from styles table');
select nvl(max(line_number),0) into l_max_line_number from PON_AUCTION_ITEM_PRICES_ALL
where auction_header_id = p_source_auction_header_id;
select nvl(max_internal_line_num,0) into l_max_line_number from PON_AUCTION_HEADERS_ALL
where auction_header_id = p_source_auction_header_id;
(l_round_number > 1 or l_last_amendment_number > 0 or g_line_deleted = 'Y') then
renumber_lines(l_auction_header_id);
x_msg_data := l_error_msg_update;
SELECT contract_type
INTO l_contract_type
FROM pon_auction_headers_all
WHERE auction_header_id = l_auction_header_id;
DELETE FROM pon_attribute_scores pas
WHERE pas.auction_header_id = l_auction_header_id and
not exists (select null
from pon_auction_attributes
where auction_header_id = pas.auction_header_id and
line_number = pas.line_number and
sequence_number = pas.attribute_sequence_number);
SELECT
DISTINCT VL.NAME
BULK COLLECT
INTO
l_name
FROM PON_PRICE_ELEMENTS P, PON_PRICE_ELEMENT_TYPES_VL VL
WHERE P.AUCTION_HEADER_ID = p_source_auction_header_id
AND P.PRICE_ELEMENT_TYPE_ID = VL.PRICE_ELEMENT_TYPE_ID
AND VL.ENABLED_FLAG = 'N';
PON_NEG_UPDATE_PKG.UPDATE_TO_NEW_DOCUMENT(
p_auction_header_id_curr_doc => l_auction_header_id,
p_doc_number_curr_doc => g_neg_doc_number,
p_auction_header_id_prev_doc => p_source_auction_header_id,
p_auction_origination_code => g_auc_origination_code,
p_is_new => 'Y',
p_is_publish => 'N',
p_transaction_type => l_is_amendment,
p_user_id => l_user_id,
x_error_code => l_error_code_update,
x_error_msg => l_error_msg_update);
IF (l_error_code_update <> 'SUCCESS' ) THEN
-- The way I am adding this error may get changed in the future.
-- So, please be aware of that
FND_MESSAGE.SET_NAME('PON','PON_GENERIC_ERR');
FND_MESSAGE.SET_TOKEN('TOKEN',l_error_code_update||' - '||l_error_msg_update);
LOG_MESSAGE('copy_lines_and_children','Error while updating source negotiation. Error:'||l_error_msg_update);
SELECT
COUNT(LINE_NUMBER) number_of_lines, MAX (DECODE (GROUP_TYPE, 'LOT_LINE', 0, 'GROUP_LINE', 0, SUB_LINE_SEQUENCE_NUMBER)) last_line_number
INTO l_number_of_lines, l_last_line_number
FROM PON_AUCTION_ITEM_PRICES_ALL
WHERE
AUCTION_HEADER_ID = l_auction_header_id;
UPDATE pon_auction_headers_all
SET number_of_lines = l_number_of_lines, LAST_LINE_NUMBER = l_last_line_number
WHERE
AUCTION_HEADER_ID = l_auction_header_id;
LOG_MESSAGE('copy_lines_and_children','Updated NUMBER_OF_LINES and LAST_LINE_NUMBER fields in PON_AUCTION_HEADERS_ALL');
/*SELECT
EXHIBIT_NUMBER,
IS_CDRL,
ASSOCIATED_TO_LINE,
EXHIBIT_DESCRIPTION
BULK COLLECT INTO
l_exhibit_number,l_iscdrl,l_associatedline,l_exhibit_description
FROM pon_auction_exhibit_details
WHERE auction_header_id=p_source_auction_header_id;
INSERT INTO pon_auction_exhibit_details
(AUCTION_HEADER_ID,
EXHIBIT_NUMBER,
IS_CDRL,
ASSOCIATED_TO_LINE,
EXHIBIT_DESCRIPTION,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY)
values
(p_auction_header_id,l_exhibit_number(x),l_iscdrl(x),
l_associatedline(x),l_exhibit_description(x),SYSDATE,p_user_id,SYSDATE,p_user_id);
INSERT INTO pon_auction_exhibit_details
(AUCTION_HEADER_ID,
EXHIBIT_NUMBER,
IS_CDRL,
ASSOCIATED_TO_LINE,
EXHIBIT_DESCRIPTION,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY)
(SELECT
p_auction_header_id,
EXHIBIT_NUMBER,
IS_CDRL,
ASSOCIATED_TO_LINE,
EXHIBIT_DESCRIPTION,
SYSDATE,
p_user_id,
SYSDATE,
p_user_id
FROM pon_auction_exhibit_details
WHERE auction_header_id=p_source_auction_header_id
/* When clauses are not retained, donot copy exhibits of type cdrls. */
AND ((Nvl(p_retain_clauses, 'N') = 'Y') OR
(Nvl(p_retain_clauses, 'N') = 'N' AND IS_CDRL = 'N')));
g_err_loc := '1.1.1 Selecting Bizrules for doctype id:'||p_doctype_id||' and copy type:'||p_copy_type||' with source auc id:'||p_source_auction_header_id;
SELECT DOCTYPE_ID
INTO l_rfi_doctype_id
FROM PON_AUC_DOCTYPES
WHERE DOCTYPE_GROUP_NAME = 'REQUEST_FOR_INFORMATION';
SELECT DOCTYPE_ID
INTO l_rfq_doctype_id
FROM PON_AUC_DOCTYPES
WHERE DOCTYPE_GROUP_NAME = 'REQUEST_FOR_QUOTE';
SELECT DOCTYPE_ID
INTO l_sol_doctype_id
FROM PON_AUC_DOCTYPES
WHERE DOCTYPE_GROUP_NAME = 'SOLICITATION';
SELECT DOCTYPE_ID
INTO l_auction_doctype_id
FROM PON_AUC_DOCTYPES
WHERE DOCTYPE_GROUP_NAME = 'BUYER_AUCTION';
SELECT
--
-- If the bizrule is not applied to a doctype then default it to
-- OPEN_BIDDING.
-- It keeps the source document value if the target doctype_id
-- is same as before. It always sets the value as per the
-- bizrule dictates if it is a cross doctype copy and the original value
-- was OPEN_BIDDING. It will carry over the value in all other cases.
--
decode (R_BID_VISIB.VALIDITY_FLAG,
'N','OPEN_BIDDING',
'Y', decode( p_doctype_id, p_source_doctype_id,
A.BID_VISIBILITY_CODE,
-- So, we are copying accross doctype id
decode(A.BID_VISIBILITY_CODE,
'OPEN_BIDDING', decode( NVL(R_BID_VISIB.FIXED_VALUE,'-1'),
'-1', decode(NVL(R_BID_VISIB.DEFAULT_VALUE,'-1'),
'-1', A.BID_VISIBILITY_CODE,
R_BID_VISIB.DEFAULT_VALUE),
R_BID_VISIB.FIXED_VALUE),
A.BID_VISIBILITY_CODE)
)) BID_VISIBILITY_CODE,
--
-- The BID_SCOPE_CODE code will be defaulted to LINE_LEVEL_BIDDING
-- when it is not applicable to a document scenario. Otherwise the source document value
-- is carried forwarded if it is displayed in the target doctype and defaulted by bizrule
-- only if the source BID_SCOPE_CODE value is NULL (Draft Negotiation Copy).
-- The value will be defaulted to the doctype default when the attribute is not
-- displayable
--
decode (R_BID_SCOPE.VALIDITY_FLAG,
'N','LINE_LEVEL_BIDDING',
'Y', decode( R_BID_SCOPE.display_flag,
'Y', NVL(A.BID_SCOPE_CODE, R_BID_SCOPE.DEFAULT_VALUE),
'N', decode(NVL(R_BID_SCOPE.FIXED_VALUE,'-1'),
'-1', R_BID_SCOPE.DEFAULT_VALUE,
R_BID_SCOPE.FIXED_VALUE))
) BID_SCOPE_CODE,
--
-- CONTRACT_TYPE Column is not applicable for RFI. So, it is NULL
-- by the seeded bizrule (We are not hardcoding it to NULL as before).
-- If it is applicable for the target doctype then check if it was displayable
-- for source doctype_id. If it is true then carry on the old value. If it is false then
-- set it to STANDARD if fixed and default
-- values are NULL for a doctype. This was the logic implemented in
-- the initialize() method of AuctionHeaderALLEOImpl
--
decode (R_CNTRCT.VALIDITY_FLAG,
'N',NULL,
'Y', decode(R_OLD_CNTRCT.display_flag,
'Y', A.CONTRACT_TYPE,
decode(NVL(R_CNTRCT.FIXED_VALUE,'-1'),
'-1', decode(NVL(R_CNTRCT.DEFAULT_VALUE,'-1'),
'-1', 'STANDARD',
R_CNTRCT.DEFAULT_VALUE),
R_CNTRCT.FIXED_VALUE))) CONTRACT_TYPE,
--
-- The PO_START_DATE will be defaulted to NULLwhen it is not applicable to the target
-- document. Otherwise the source document value is carried forwarded if it is displayed
-- in the target doctype scenario. The value will be set to NULL when the attribute is not
-- displayable in the target doctype scenario
--
decode (R_AGRMNT_DATE.VALIDITY_FLAG,
'N',NULL,
'Y',decode(R_AGRMNT_DATE.display_flag,
'Y', A.PO_START_DATE,
'N', NULL)) PO_START_DATE,
--
-- The PO_END_DATE will be defaulted to NULLwhen it is not applicable to the target
-- document. Otherwise the source document value is carried forwarded if it is displayed
-- in the target doctype scenario. The value will be set to NULL when the attribute is not
-- displayable in the target doctype scenario
--
decode (R_AGRMNT_END_DATE.VALIDITY_FLAG,
'N',NULL,
'Y',decode(R_AGRMNT_END_DATE.display_flag,
'Y', A.PO_END_DATE,
'N', NULL)) PO_END_DATE,
decode (R_AGRMNT_AMNT.VALIDITY_FLAG,
'N',NULL,
'Y',decode(R_AGRMNT_AMNT.display_flag,
'Y', A.PO_AGREED_AMOUNT,
'N', NULL)) PO_AGREED_AMOUNT,
--
-- If the bizrule is not applied to a doctype then default it to N
-- It keeps the source document value if the target doctype_id
-- has the display flag on. Otherwise it is always set as per the
-- bizrule dictates. If the bizrule value is NULL then it is set to source
-- document attribute value for simplicity.
--
decode (R_MAN_CLOSE.VALIDITY_FLAG,
'N','N',
'Y',decode( R_MAN_CLOSE.display_flag,
'Y', A.MANUAL_CLOSE_FLAG,
'N', decode(NVL(R_MAN_CLOSE.FIXED_VALUE,'-1'),
'-1', decode(NVL(R_MAN_CLOSE.DEFAULT_VALUE,'-1'),
'-1', A.MANUAL_CLOSE_FLAG,
R_MAN_CLOSE.DEFAULT_VALUE),
R_MAN_CLOSE.FIXED_VALUE))) MANUAL_CLOSE_FLAG,
--
-- If the bizrule is not applied to a doctype then default it to N
-- It keeps the source document value if the target doctype_id
-- has the display flag on. Otherwise it is always set as per the
-- bizrule dictates. If the bizrule value is NULL then it is set to source
-- document attribute value for simplicity.
--
decode (R_MAN_EXTND.VALIDITY_FLAG,
'N','N',
'Y',decode( R_MAN_EXTND.display_flag,
'Y', A.MANUAL_EXTEND_FLAG,
'N', decode(NVL(R_MAN_EXTND.FIXED_VALUE,'-1'),
'-1', decode(NVL(R_MAN_EXTND.DEFAULT_VALUE,'-1'),
'-1', A.MANUAL_EXTEND_FLAG,
R_MAN_EXTND.DEFAULT_VALUE),
R_MAN_EXTND.FIXED_VALUE))) MANUAL_EXTEND_FLAG,
--
-- If the bizrule is not applied to a doctype then default it to N
-- It keeps the source document value if the target doctype_id
-- has the display flag on. Otherwise it is always set as per the
-- bizrule dictates. If the bizrule value is NULL then it is set to source
-- document attribute value for simplicity.
--
decode (R_SHOW_NOTES.VALIDITY_FLAG,
'N','N',
'Y', decode( R_SHOW_NOTES.display_flag,
'Y', A.SHOW_BIDDER_NOTES,
'N', decode(NVL(R_SHOW_NOTES.FIXED_VALUE,'-1'),
'-1', decode(NVL(R_SHOW_NOTES.DEFAULT_VALUE,'-1'),
'-1', A.SHOW_BIDDER_NOTES,
R_SHOW_NOTES.DEFAULT_VALUE),
R_SHOW_NOTES.FIXED_VALUE))) SHOW_BIDDER_NOTES,
--
-- The MULTIPLE_ROUNDS_FLAG flag will be defaulted to N if not applicable to
-- a doctype_id. It keeps the source document value if the target doctype_id
-- has the display flag on. Otherwise it is always set as per the
-- bizrule dictates. If the bizrule value is NULL then it is set to N.
--
decode (R_MULI_ROUND.VALIDITY_FLAG,
'N','N',
'Y', decode( R_MULI_ROUND.display_flag,
'Y', nvl(A.MULTIPLE_ROUNDS_FLAG,'N'),
'N', decode(NVL(R_MULI_ROUND.FIXED_VALUE,'-1'), -- It has got a problem, fixed and default values are different
'-1', decode(NVL(R_MULI_ROUND.DEFAULT_VALUE,'-1'),
'-1', nvl(A.MULTIPLE_ROUNDS_FLAG,'N'),
R_MULI_ROUND.DEFAULT_VALUE),
decode(R_MULI_ROUND.FIXED_VALUE,
'MULTIPLE', 'Y',
'SINGLE', 'N'))
)
) MULTIPLE_ROUNDS_FLAG, -- It is SINGLE for RFQ which seems to be wrong
--
-- The AUTO_EXTEND_FLAG flag will be defaulted to N if not applicable to
-- a doctype_id. It keeps the source document value if the target doctype_id
-- has the display flag on. Otherwise it is always set as per the
-- bizrule dictates. If the bizrule value is NULL then it is set to N.
--
-- also check for neg style
-- if style disables auto extension, set the flag to N
decode (R_AUTO_EXTND.VALIDITY_FLAG,
'N','N',
'Y', decode(g_neg_style_control.auto_extend_enabled_flag,
'N', 'N',
'Y', decode( R_AUTO_EXTND.display_flag,
'Y', A.AUTO_EXTEND_FLAG,
'N', decode(NVL(R_AUTO_EXTND.FIXED_VALUE,'-1'),
'-1', decode(NVL(R_AUTO_EXTND.DEFAULT_VALUE,'-1'),
'-1', 'N',
R_AUTO_EXTND.DEFAULT_VALUE),
R_AUTO_EXTND.FIXED_VALUE)))) AUTO_EXTEND_FLAG,
--
-- The AUTO_EXTEND_ALL_LINES_FLAG flag will be defaulted to N if not applicable to
-- a doctype_id. It keeps the source document value if the target doctype_id
-- has the display flag on. Otherwise it is always set as per the
-- bizrule dictates. If the bizrule value is NULL then it is set to N.
--
decode (R_AUTO_XTN_ALL.VALIDITY_FLAG,
'N','N',
'Y', decode( R_AUTO_XTN_ALL.display_flag,
'Y', A.AUTO_EXTEND_ALL_LINES_FLAG,
'N', decode(NVL(R_AUTO_XTN_ALL.FIXED_VALUE,'-1'),
'-1', decode(NVL(R_AUTO_XTN_ALL.DEFAULT_VALUE,'-1'),
'-1', 'N',
R_AUTO_XTN_ALL.DEFAULT_VALUE),
R_AUTO_XTN_ALL.FIXED_VALUE))) AUTO_EXTEND_ALL_LINES_FLAG,
-- AUTO_EXTEND_MIN_TRIGGER_RANK will be defaulted to 1 if autoextend is not
-- applicable. we do not have a seperate bizrule for this. Instead we use the
-- generic bizrule for the autoextend.
--
decode (R_AUTO_XTN_ALL.VALIDITY_FLAG,
'N',1, A.AUTO_EXTEND_MIN_TRIGGER_RANK) AUTO_EXTEND_MIN_TRIGGER_RANK,
--
-- The AUTO_EXTEND_DURATION flag will be defaulted to NULL if not applicable to
-- a doctype_id. It keeps the source document value if the target doctype_id
-- has the display flag on. Otherwise it is always set as per the
-- bizrule dictates. If the bizrule value is NULL then it is set to the source attribute
-- value.
--
decode (R_AUTO_XTN_LENGTH.VALIDITY_FLAG,
'N',NULL,
'Y',decode( R_AUTO_XTN_LENGTH.display_flag,
'Y', A.AUTO_EXTEND_DURATION,
'N', decode(NVL(R_AUTO_XTN_LENGTH.FIXED_VALUE,'-1'),
'-1', decode(NVL(R_AUTO_XTN_LENGTH.DEFAULT_VALUE,'-1'),
'-1', NULL,
R_AUTO_XTN_LENGTH.DEFAULT_VALUE),
R_AUTO_XTN_LENGTH.FIXED_VALUE))) AUTO_EXTEND_DURATION, -- It is 30 min default hence 20 not hard coded
--
-- The AUTO_EXTEND_TYPE_FLAG flag will be defaulted to NULL if not applicable to
-- a doctype_id. It keeps the source document value if the target doctype_id
-- has the display flag on. Otherwise it is always set as per the
-- bizrule dictates. If the bizrule value is NULL then it is set to the source attribute
-- value.
--
decode (R_AUTO_XTN_TYPE.VALIDITY_FLAG,
'N',NULL,
'Y', decode( R_AUTO_XTN_TYPE.display_flag,
'Y', A.AUTO_EXTEND_TYPE_FLAG,
'N', decode(NVL(R_AUTO_XTN_TYPE.FIXED_VALUE,'-1'),
'-1', decode(NVL(R_AUTO_XTN_TYPE.DEFAULT_VALUE,'-1'),
'-1', NULL,
R_AUTO_XTN_TYPE.DEFAULT_VALUE),
R_AUTO_XTN_TYPE.FIXED_VALUE))) AUTO_EXTEND_TYPE_FLAG, -- It should have some default value
--
-- The GLOBAL_AGREEMENT_FLAG flag will be defaulted to NULL if not applicable to
-- a doctype_id. It keeps the source document value if the target doctype_id
-- has the display flag on. Otherwise it is always set as per the
-- bizrule dictates. If the bizrule value is NULL then it is set to the source attribute
-- value. It is set to NULL in case of RFI later on.
--
decode (R_GLBL_AGREMNT.VALIDITY_FLAG,
'N',NULL,
'Y', decode( R_GLBL_AGREMNT.display_flag,
'Y', A.GLOBAL_AGREEMENT_FLAG,
'N', decode(NVL(R_GLBL_AGREMNT.FIXED_VALUE,'-1'),
'-1', decode(NVL(R_GLBL_AGREMNT.DEFAULT_VALUE,'-1'),
'-1', NULL,
R_GLBL_AGREMNT.DEFAULT_VALUE),
R_GLBL_AGREMNT.FIXED_VALUE))) GLOBAL_AGREEMENT_FLAG,
--
-- The PO_MIN_REL_AMOUNT flag will be defaulted to NULL if not applicable to
-- a doctype_id. It keeps the source document value if the target doctype_id
-- ihas the display flag on. Otherwise it is always set as per the
-- bizrule dictates. If the bizrule value is NULL then it is set to NULL.
-- It is later set to NULL if the current Contract type is STANDARD
--
decode (R_MIN_REL_AMT.VALIDITY_FLAG,
'N',NULL,
'Y', decode( R_MIN_REL_AMT.display_flag,
'Y', A.PO_MIN_REL_AMOUNT,
'N', decode(NVL(R_MIN_REL_AMT.FIXED_VALUE,'-1'),
'-1', decode(NVL(R_MIN_REL_AMT.DEFAULT_VALUE,'-1'),
'-1', NULL,
R_MIN_REL_AMT.DEFAULT_VALUE),
R_MIN_REL_AMT.FIXED_VALUE))) PO_MIN_REL_AMOUNT,
decode (R_ALLOW_EVENT.VALIDITY_FLAG,
'N',NULL,
'Y',A.EVENT_ID) EVENT_ID,
decode (R_ALLOW_EVENT.VALIDITY_FLAG,
'N',NULL,
'Y',A.EVENT_TITLE) EVENT_TITLE,
--
-- The BID_RANKING flag will be defaulted to PRICE_ONLY if not applicable to
-- a doctype_id. It keeps the source document value if the target document
-- still shows the UI for this control. Otherwise it is always set as per the
-- bizrule dictates. If the bizrule value is NULL then it is set to PRICE_ONLY
-- if style disables line MAS, bid_ranking is PRICE_ONLY
decode (R_BID_RANK.VALIDITY_FLAG,
'N','PRICE_ONLY',
'Y',decode(NVL(g_neg_style_control.line_mas_enabled_flag,'N'),
'N', 'PRICE_ONLY',
'Y', decode( R_BID_RANK.display_flag,
'Y', A.BID_RANKING,
'N', decode(NVL(R_BID_RANK.FIXED_VALUE,'-1'),
'-1', decode(NVL(R_BID_RANK.DEFAULT_VALUE,'-1'),
'-1', 'PRICE_ONLY',
R_BID_RANK.DEFAULT_VALUE),
R_BID_RANK.FIXED_VALUE)))) BID_RANKING,
decode (R_BILL_LOC.VALIDITY_FLAG,
'N',NULL,
'Y', decode(R_OLD_BILL_LOC.display_flag,
'Y', A.BILL_TO_LOCATION_ID,
'N', decode(NVL(R_BILL_LOC.FIXED_VALUE,'-1'),
'-1', decode(NVL(R_BILL_LOC.DEFAULT_VALUE,'-1'),
'-1', NULL,
R_BILL_LOC.DEFAULT_VALUE),
R_BILL_LOC.FIXED_VALUE))) BILL_TO_LOCATION_ID, -- Though I feel no one defaults the bill or ship loc id still keeping it
decode (R_SHIP_LOC.VALIDITY_FLAG,
'N',NULL,
'Y', decode(R_OLD_SHIP_LOC.display_flag,
'Y', A.SHIP_TO_LOCATION_ID,
'N', decode(NVL(R_SHIP_LOC.FIXED_VALUE,'-1'),
'-1', decode(NVL(R_SHIP_LOC.DEFAULT_VALUE,'-1'),
'-1', NULL,
R_SHIP_LOC.DEFAULT_VALUE),
R_SHIP_LOC.FIXED_VALUE))) SHIP_TO_LOCATION_ID, -- 22 rule id
decode (R_CARRIER.VALIDITY_FLAG,
'N',NULL,
'Y',decode(R_CARRIER.display_flag,
'Y', A.CARRIER_CODE,
'N', decode(NVL(R_CARRIER.FIXED_VALUE,'-1'),
'-1', decode(NVL(R_CARRIER.DEFAULT_VALUE,'-1'),
'-1', NULL,
R_CARRIER.DEFAULT_VALUE),
R_CARRIER.FIXED_VALUE))) CARRIER_CODE, -- 23 rule id
decode (R_FRIEIGHT_TERMS.VALIDITY_FLAG,
'N',NULL,
'Y', decode(R_FRIEIGHT_TERMS.display_flag,
'Y', A.FREIGHT_TERMS_CODE,
'N', decode(NVL(R_FRIEIGHT_TERMS.FIXED_VALUE,'-1'),
'-1', decode(NVL(R_FRIEIGHT_TERMS.DEFAULT_VALUE,'-1'),
'-1', NULL,
R_FRIEIGHT_TERMS.DEFAULT_VALUE),
R_FRIEIGHT_TERMS.FIXED_VALUE))) FREIGHT_TERMS_CODE,
decode (R_FOB_CODE.VALIDITY_FLAG,
'N',NULL,
'Y', decode(R_FOB_CODE.display_flag,
'Y', A.FOB_CODE,
'N', decode(NVL(R_FOB_CODE.FIXED_VALUE,'-1'),
'-1', decode(NVL(R_FOB_CODE.DEFAULT_VALUE,'-1'),
'-1', NULL,
R_FOB_CODE.DEFAULT_VALUE),
R_FOB_CODE.FIXED_VALUE))) FOB_CODE,
--
-- Defaulted to PUBLIC_BID_LIST if not applicable for any doctype.
-- The value of BID_LIST_TYPE is always taken from the bizrule
-- only if it was displayable in the source document. It is defaulted
-- from bizrule otherwise
--
decode (R_BID_LIST.VALIDITY_FLAG,
'N','PUBLIC_BID_LIST',
'Y', decode( R_BID_LIST.display_flag,
'Y', A.BID_LIST_TYPE,
'N',decode(NVL(R_BID_LIST.FIXED_VALUE,'-1'),
'-1', decode(NVL(R_BID_LIST.DEFAULT_VALUE,'-1'),
'-1', 'PUBLIC_BID_LIST',
R_BID_LIST.DEFAULT_VALUE),
R_BID_LIST.FIXED_VALUE))) BID_LIST_TYPE,
--
-- The BID_FREQUENCY_CODE code will be defaulted to MULTIPLE_BIDS_ALLOWED
-- when it is not applicable to a document. Otherwise the source document value
-- is carried forwarded if displayable and is defaulted by bizrule only if it is not displayable.
-- It is set to the doctype default value when it is NULL but displayable (Draft Negotiation Copy)
--
decode (R_BID_FREQ_CODE.VALIDITY_FLAG,
'N','MULTIPLE_BIDS_ALLOWED',
'Y', decode( R_BID_FREQ_CODE.display_flag,
'Y', NVL(A.BID_FREQUENCY_CODE, R_BID_FREQ_CODE.DEFAULT_VALUE),
'N', decode(NVL(R_BID_FREQ_CODE.FIXED_VALUE,'-1'),
'-1', decode(NVL(R_BID_FREQ_CODE.DEFAULT_VALUE,'-1'),
'-1', 'MULTIPLE_BIDS_ALLOWED',
R_BID_FREQ_CODE.DEFAULT_VALUE),
R_BID_FREQ_CODE.FIXED_VALUE))) BID_FREQUENCY_CODE,
--
-- The FULL_QUANTITY_BID_CODE code will be defaulted to PARTIAL_QTY_BIDS_ALLOWED
-- when it is not applicable to a document. It will be carried over if it is displayable in the
-- destination document.
-- Otherwise it is defaulted to the fixed value of the business rule
-- if there is any (if not displayable)
-- Or else it is carried forwarded and defaulted to default value
-- if the source document value is NULL
--
decode (R_BID_QTY_SCOPE.VALIDITY_FLAG,
'N','FULL_QTY_BIDS_REQD',
'Y', decode( R_BID_QTY_SCOPE.display_flag,
'Y', NVL(A.FULL_QUANTITY_BID_CODE, R_BID_QTY_SCOPE.DEFAULT_VALUE),
'N', decode(NVL(R_BID_QTY_SCOPE.FIXED_VALUE,'-1'),
'-1', decode(NVL(R_BID_QTY_SCOPE.DEFAULT_VALUE,'-1'),
'-1', 'FULL_QTY_BIDS_REQD',
R_BID_QTY_SCOPE.DEFAULT_VALUE),
R_BID_QTY_SCOPE.FIXED_VALUE))) FULL_QUANTITY_BID_CODE,
--
-- The RANK_INDICATOR flag will be defaulted to NONE if not applicable to
-- a doctype_id. It keeps the source document value if it is Amendment.
-- Otherwise it is always set as per the Admin setting. If the doctype is RFI
-- then the value is set to NONE
--
decode (R_RANK_INDICATOR.VALIDITY_FLAG,
'N','NONE',
'Y', A.RANK_INDICATOR) RANK_INDICATOR,
--
-- The SHOW_BIDDER_SCORES code will be carried over.
-- It will be changed to NONE if the BID_RANKING is PRICE_ONLY.
-- But this logic is implemented in the COPY_HEADER_BASIC procedure.
-- It is set to NONE if the destination if RFI later on this procedure.
--
A.SHOW_BIDDER_SCORES ,
--
--
-- The PF_TYPE_ALLOWED column will be defaulted to NONE if not applicable to
-- a doctype_id.
-- If applicable, use the source value if control was applicable in source
-- Otherwise set as per the Admin setting.
--
decode (R_ALLOW_PE.VALIDITY_FLAG,
'N','NONE',
'Y', decode(g_neg_style_control.price_element_enabled_flag,
'N', 'NONE', A.PF_TYPE_ALLOWED)) PF_TYPE_ALLOWED,
--
--
-- There is no direct bizrule for PRICE_DRIVEN_AUCTION_FLAG. Hence it is
-- indirectly populated from BID_CHANGE_TYPE bizrule. Defaulted to N if
-- not applicable for any doctype. It is inherited from the last document if
-- the doctype_ids are same. It is carried over from last document if
-- it is still applicable for the target doctype_id and the target BID_RANKING
-- is not MULTI_ATTRIBUTE_SCORING.
--
decode( p_doctype_id,
p_source_doctype_id, A.PRICE_DRIVEN_AUCTION_FLAG,
decode(R_MIN_BID_CHANGE_TYPE.VALIDITY_FLAG,
'N', 'N',
decode (BID_RANKING,
'MULTI_ATTRIBUTE_SCORING', 'N',
A.PRICE_DRIVEN_AUCTION_FLAG))) PRICE_DRIVEN_AUCTION_FLAG,
--
-- Defaulted to AMOUNT if not applicable for any doctype.
-- The value of MIN_BID_CHANGE_TYPE is always taken from the bizrule
-- only if the source and target doctype_id are different. Set to AMOUNT
-- if it is applicable and there is no bizrule dictated value
--
decode (R_MIN_BID_CHANGE_TYPE.VALIDITY_FLAG,
'N','AMOUNT',
'Y', decode( R_MIN_BID_CHANGE_TYPE.display_flag,
'Y', NVL(A.MIN_BID_CHANGE_TYPE,'AMOUNT'),
'N', decode(NVL(R_MIN_BID_CHANGE_TYPE.FIXED_VALUE,'-1'),
'-1', decode(NVL(R_MIN_BID_CHANGE_TYPE.DEFAULT_VALUE,'-1'),
'-1', 'AMOUNT',
R_MIN_BID_CHANGE_TYPE.DEFAULT_VALUE),
R_MIN_BID_CHANGE_TYPE.FIXED_VALUE))) MIN_BID_CHANGE_TYPE,
decode (R_PAY_TERMS.VALIDITY_FLAG,
'N',NULL,
'Y', decode(R_PAY_TERMS.display_flag,
'Y', A.PAYMENT_TERMS_ID,
'N', decode(NVL(R_PAY_TERMS.FIXED_VALUE,'-1'),
'-1', decode(NVL(R_PAY_TERMS.DEFAULT_VALUE,'-1'),
'-1', NULL,
R_PAY_TERMS.DEFAULT_VALUE),
R_PAY_TERMS.FIXED_VALUE))) PAYMENT_TERMS_ID,
--
-- Defaulted to N if validity flag is NULL.
-- The validity flag is used to decide if the price element
-- is allowed or not
--
decode (nvl(R_ALLOW_PE.VALIDITY_FLAG , 'N'),
'N','N',
'Y') ALLOW_PRICE_ELEMENT,
--
-- Defaulted to N if not applicable for any doctype.
-- if it is applicable and if is displayable then it is set to Y. It is set to N
-- otherwise
--
decode (R_NO_PRICE_QTY.VALIDITY_FLAG,
'N','N',
'Y', decode(NVL(R_NO_PRICE_QTY.DISPLAY_FLAG,'N'),
'Y','Y',
'N')) NO_PRICE_QTY_ITEMS_POSSIBLE,
--
-- Defaulted to N if not applicable for any doctype.
-- if it is applicable and if is displayable then it is set to Y. It is set to N
-- otherwise for all price and price break
--
decode (R_START_PRICE.VALIDITY_FLAG,
'N','N',
'Y', decode(R_START_PRICE.DISPLAY_FLAG,
'Y','Y',
'N')) START_PRICE,
decode (R_RESERVE_PRICE.VALIDITY_FLAG,
'N','N',
'Y', decode(R_RESERVE_PRICE.DISPLAY_FLAG,
'Y','Y',
'N')) RESERVE_PRICE,
decode (R_TARGET_PRICE.VALIDITY_FLAG,
'N','N',
'Y', decode(R_TARGET_PRICE.DISPLAY_FLAG,
'Y','Y',
'N')) TARGET_PRICE,
decode (R_CURRENT_PRICE.VALIDITY_FLAG,
'N','N',
'Y', decode(R_CURRENT_PRICE.DISPLAY_FLAG,
'Y','Y',
'N')) CURRENT_PRICE,
decode (R_BEST_PRICE.VALIDITY_FLAG,
'N','N',
'Y', decode(R_BEST_PRICE.DISPLAY_FLAG,
'Y','Y',
'N')) BEST_PRICE,
decode (R_PRICE_BREAK.VALIDITY_FLAG,
'N','N',
'Y', decode(R_PRICE_BREAK.DISPLAY_FLAG,
'Y','Y',
'N')) PRICE_BREAK,
decode (R_ALLOW_PRICE_DIFF.VALIDITY_FLAG,
'N','N',
'Y', decode(R_ALLOW_PRICE_DIFF.DISPLAY_FLAG,
'Y','Y',
'N')) ALLOW_PRICE_DIFFERENTIAL,
NVL(A.NUMBER_OF_BIDS,0),
--
-- Just check the Fixed Value of the AWARD_TYPE bizrule for the source (not
-- the current one) document type
--
R_AWARD_TYPE.FIXED_VALUE,
A.CURRENCY_CODE,
A.RATE_TYPE,
A.FIRST_LINE_CLOSE_DATE,
A.STAGGERED_CLOSING_INTERVAL,
A.QTY_PRICE_TIERS_ENABLED_FLAG,
A.PRICE_TIERS_INDICATOR,
-- Copy the ALLOW_UNSOL_OFFER_LINES flag from the original docuemnt
-- if biz rule is valid and displayable
decode (R_ALLOW_UNSOL_OFFER_LINES.VALIDITY_FLAG,
'N','N',
'Y', decode( R_ALLOW_UNSOL_OFFER_LINES.display_flag,
'Y', A.ALLOW_UNSOL_OFFER_LINES,
'N')) ALLOW_UNSOL_OFFER_LINES,
-- Copy the ALLOW_WITHDRAW_FLAG flag from the original docuemnt if biz rule is valid and displayable
decode (R_ALLOW_WITHDRAW_FLAG.VALIDITY_FLAG, 'N','N','Y', decode( R_ALLOW_WITHDRAW_FLAG.display_flag,'Y', A.ALLOW_WITHDRAW_FLAG,'N')) ALLOW_WITHDRAW_FLAG,
-- Copy the ALLOW_MULTIPLE_ACTIVE_OFFERS flag from the original docuemnt
-- if biz rule is valid and displayable
decode (R_ALLOW_MULTIPLE_ACTIVE_OFFERS.VALIDITY_FLAG,
'N','N',
'Y', decode( R_ALLOW_MULTIPLE_ACTIVE_OFFERS.display_flag,
'Y', A.ALLOW_MULTIPLE_ACTIVE_OFFERS,
'N')) ALLOW_MULTIPLE_ACTIVE_OFFERS,
-- Copy the ALLOW_STAGGERED_AWARDS flag from the original docuemnt if biz rule is valid and displayable
decode (R_ALLOW_STAGGERED_AWARDS.VALIDITY_FLAG,
'N','N',
'Y', decode( R_ALLOW_STAGGERED_AWARDS.display_flag,
'Y', A.ALLOW_STAGGERED_AWARDS,
'N')) ALLOW_STAGGERED_AWARDS
INTO
t_record.BID_VISIBILITY_CODE,
t_record.BID_SCOPE_CODE,
t_record.CONTRACT_TYPE,
t_record.PO_START_DATE,
t_record.PO_END_DATE,
t_record.PO_AGREED_AMOUNT,
t_record.MANUAL_CLOSE_FLAG,
t_record.MANUAL_EXTEND_FLAG,
t_record.SHOW_BIDDER_NOTES,
t_record.MULTIPLE_ROUNDS_FLAG,
t_record.AUTO_EXTEND_FLAG,
t_record.AUTO_EXTEND_ALL_LINES_FLAG,
t_record.AUTO_EXTEND_MIN_TRIGGER_RANK,
t_record.AUTO_EXTEND_DURATION,
t_record.AUTO_EXTEND_TYPE_FLAG,
t_record.GLOBAL_AGREEMENT_FLAG,
t_record.PO_MIN_REL_AMOUNT,
t_record.EVENT_ID,
t_record.EVENT_TITLE,
t_record.BID_RANKING,
t_record.BILL_TO_LOCATION_ID,
t_record.SHIP_TO_LOCATION_ID,
t_record.CARRIER_CODE,
t_record.FREIGHT_TERMS_CODE,
t_record.FOB_CODE,
t_record.BID_LIST_TYPE,
t_record.BID_FREQUENCY_CODE,
t_record.FULL_QUANTITY_BID_CODE,
t_record.RANK_INDICATOR,
t_record.SHOW_BIDDER_SCORES,
t_record.PF_TYPE_ALLOWED,
t_record.PRICE_DRIVEN_AUCTION_FLAG,
t_record.MIN_BID_CHANGE_TYPE,
t_record.PAYMENT_TERMS_ID,
t_record.ALLOW_PRICE_ELEMENT,
t_record.NO_PRICE_QTY_ITEMS_POSSIBLE,
t_record.START_PRICE,
t_record.RESERVE_PRICE,
t_record.TARGET_PRICE,
t_record.CURRENT_PRICE,
t_record.BEST_PRICE,
t_record.PRICE_BREAK,
t_record.ALLOW_PRICE_DIFFERENTIAL,
t_record.NUMBER_OF_BIDS,
t_record.AWARD_TYPE_RULE_FIXED_VALUE,
t_record.CURRENCY_CODE,
t_record.RATE_TYPE,
t_record.FIRST_LINE_CLOSE_DATE,
t_record.STAGGERED_CLOSING_INTERVAL,
t_record.QTY_PRICE_TIERS_ENABLED_FLAG,
t_record.PRICE_TIERS_INDICATOR,
t_record.ALLOW_UNSOL_OFFER_LINES,
t_record.ALLOW_WITHDRAW_FLAG,
t_record.ALLOW_MULTIPLE_ACTIVE_OFFERS,
t_record.ALLOW_STAGGERED_AWARDS
FROM PON_AUCTION_HEADERS_ALL A,
PON_AUC_BIZRULES BID_VISIB,
PON_AUC_DOCTYPE_RULES R_BID_VISIB,
PON_AUC_BIZRULES BID_SCOPE,
PON_AUC_DOCTYPE_RULES R_BID_SCOPE,
PON_AUC_BIZRULES CNTRCT,
PON_AUC_DOCTYPE_RULES R_CNTRCT,
PON_AUC_DOCTYPE_RULES R_OLD_CNTRCT,
PON_AUC_BIZRULES AGRMNT_DATE,
PON_AUC_DOCTYPE_RULES R_AGRMNT_DATE,
PON_AUC_BIZRULES AGRMNT_END_DATE,
PON_AUC_DOCTYPE_RULES R_AGRMNT_END_DATE,
PON_AUC_BIZRULES AGRMNT_AMNT,
PON_AUC_DOCTYPE_RULES R_AGRMNT_AMNT,
PON_AUC_BIZRULES MAN_CLOSE,
PON_AUC_DOCTYPE_RULES R_MAN_CLOSE,
PON_AUC_BIZRULES MAN_EXTND,
PON_AUC_DOCTYPE_RULES R_MAN_EXTND,
PON_AUC_BIZRULES SHOW_NOTES,
PON_AUC_DOCTYPE_RULES R_SHOW_NOTES,
PON_AUC_BIZRULES MULI_ROUND,
PON_AUC_DOCTYPE_RULES R_MULI_ROUND,
PON_AUC_BIZRULES AUTO_EXTND,
PON_AUC_DOCTYPE_RULES R_AUTO_EXTND,
PON_AUC_BIZRULES AUTO_XTN_ALL,
PON_AUC_DOCTYPE_RULES R_AUTO_XTN_ALL,
PON_AUC_BIZRULES AUTO_XTN_LENGTH,
PON_AUC_DOCTYPE_RULES R_AUTO_XTN_LENGTH,
PON_AUC_BIZRULES AUTO_XTN_TYPE,
PON_AUC_DOCTYPE_RULES R_AUTO_XTN_TYPE,
PON_AUC_BIZRULES GLBL_AGREMNT,
PON_AUC_DOCTYPE_RULES R_GLBL_AGREMNT,
PON_AUC_BIZRULES MIN_REL_AMT,
PON_AUC_DOCTYPE_RULES R_MIN_REL_AMT,
PON_AUC_BIZRULES ALLOW_EVENT,
PON_AUC_DOCTYPE_RULES R_ALLOW_EVENT,
PON_AUC_BIZRULES BID_RANK,
PON_AUC_DOCTYPE_RULES R_BID_RANK,
PON_AUC_BIZRULES BILL_LOC,
PON_AUC_DOCTYPE_RULES R_BILL_LOC,
PON_AUC_DOCTYPE_RULES R_OLD_BILL_LOC,
PON_AUC_BIZRULES SHIP_LOC,
PON_AUC_DOCTYPE_RULES R_SHIP_LOC,
PON_AUC_DOCTYPE_RULES R_OLD_SHIP_LOC,
PON_AUC_BIZRULES CARRIER,
PON_AUC_DOCTYPE_RULES R_CARRIER,
PON_AUC_BIZRULES FRIEIGHT_TERMS,
PON_AUC_DOCTYPE_RULES R_FRIEIGHT_TERMS,
PON_AUC_BIZRULES FOB_CODE,
PON_AUC_DOCTYPE_RULES R_FOB_CODE,
PON_AUC_BIZRULES BID_LIST,
PON_AUC_DOCTYPE_RULES R_BID_LIST,
PON_AUC_BIZRULES BID_FREQ_CODE,
PON_AUC_DOCTYPE_RULES R_BID_FREQ_CODE,
PON_AUC_BIZRULES BID_QTY_SCOPE,
PON_AUC_DOCTYPE_RULES R_BID_QTY_SCOPE,
PON_AUC_BIZRULES RANK_INDICATOR,
PON_AUC_DOCTYPE_RULES R_RANK_INDICATOR,
PON_AUC_BIZRULES SHOW_SCORE,
PON_AUC_DOCTYPE_RULES R_SHOW_SCORE,
PON_AUC_BIZRULES MIN_BID_CHANGE_TYPE,
PON_AUC_DOCTYPE_RULES R_MIN_BID_CHANGE_TYPE,
PON_AUC_BIZRULES PAY_TERMS,
PON_AUC_DOCTYPE_RULES R_PAY_TERMS,
PON_AUC_BIZRULES ALLOW_PE,
PON_AUC_DOCTYPE_RULES R_ALLOW_PE,
PON_AUC_BIZRULES NO_PRICE_QTY,
PON_AUC_DOCTYPE_RULES R_NO_PRICE_QTY,
PON_AUC_BIZRULES START_PRICE,
PON_AUC_DOCTYPE_RULES R_START_PRICE,
PON_AUC_BIZRULES RESERVE_PRICE,
PON_AUC_DOCTYPE_RULES R_RESERVE_PRICE,
PON_AUC_BIZRULES TARGET_PRICE,
PON_AUC_DOCTYPE_RULES R_TARGET_PRICE,
PON_AUC_BIZRULES CURRENT_PRICE,
PON_AUC_DOCTYPE_RULES R_CURRENT_PRICE,
PON_AUC_BIZRULES BEST_PRICE,
PON_AUC_DOCTYPE_RULES R_BEST_PRICE,
PON_AUC_BIZRULES PRICE_BREAK,
PON_AUC_DOCTYPE_RULES R_PRICE_BREAK,
PON_AUC_BIZRULES ALLOW_PRICE_DIFF,
PON_AUC_DOCTYPE_RULES R_ALLOW_PRICE_DIFF,
PON_AUC_BIZRULES AWARD_TYPE,
PON_AUC_DOCTYPE_RULES R_AWARD_TYPE,
PON_AUC_BIZRULES ALLOW_UNSOL_OFFER_LINES,
PON_AUC_DOCTYPE_RULES R_ALLOW_UNSOL_OFFER_LINES,
PON_AUC_BIZRULES ALLOW_WITHDRAW_FLAG,
PON_AUC_DOCTYPE_RULES R_ALLOW_WITHDRAW_FLAG,
PON_AUC_BIZRULES ALLOW_MULTIPLE_ACTIVE_OFFERS,
PON_AUC_DOCTYPE_RULES R_ALLOW_MULTIPLE_ACTIVE_OFFERS,
PON_AUC_BIZRULES ALLOW_STAGGERED_AWARDS,
PON_AUC_DOCTYPE_RULES R_ALLOW_STAGGERED_AWARDS
WHERE A.AUCTION_HEADER_ID = p_source_auction_header_id
AND R_BID_VISIB.DOCTYPE_ID = p_doctype_id
AND BID_VISIB.BIZRULE_ID = R_BID_VISIB.BIZRULE_ID
AND BID_VISIB.NAME = 'BID_VISIBILITY'
AND R_BID_SCOPE.DOCTYPE_ID = p_doctype_id
AND BID_SCOPE.BIZRULE_ID = R_BID_SCOPE.BIZRULE_ID
AND BID_SCOPE.NAME = 'BID_SCOPE'
AND R_CNTRCT.DOCTYPE_ID = p_doctype_id
AND CNTRCT.BIZRULE_ID = R_CNTRCT.BIZRULE_ID
AND CNTRCT.NAME = 'CONTRACT_TYPE'
AND R_OLD_CNTRCT.DOCTYPE_ID = p_source_doctype_id
AND R_OLD_CNTRCT.BIZRULE_ID = CNTRCT.BIZRULE_ID
AND BID_SCOPE.NAME = 'BID_SCOPE'
AND R_AGRMNT_DATE.DOCTYPE_ID = p_doctype_id
AND AGRMNT_DATE.BIZRULE_ID = R_AGRMNT_DATE.BIZRULE_ID
AND AGRMNT_DATE.NAME = 'AGREEMENT_START_DATE'
AND R_AGRMNT_END_DATE.DOCTYPE_ID = p_doctype_id
AND AGRMNT_END_DATE.BIZRULE_ID = R_AGRMNT_END_DATE.BIZRULE_ID
AND AGRMNT_END_DATE.NAME = 'AGREEMENT_END_DATE'
AND R_AGRMNT_AMNT.DOCTYPE_ID = p_doctype_id
AND AGRMNT_AMNT.BIZRULE_ID = R_AGRMNT_AMNT.BIZRULE_ID
AND AGRMNT_AMNT.NAME = 'AGREEMENT_AMOUNT'
AND R_MAN_CLOSE.DOCTYPE_ID = p_doctype_id
AND MAN_CLOSE.BIZRULE_ID = R_MAN_CLOSE.BIZRULE_ID
AND MAN_CLOSE.NAME = 'MANUAL_CLOSE'
AND R_MAN_EXTND.DOCTYPE_ID = p_doctype_id
AND MAN_EXTND.BIZRULE_ID = R_MAN_EXTND.BIZRULE_ID
AND MAN_EXTND.NAME = 'MANUAL_EXTEND'
AND R_SHOW_NOTES.DOCTYPE_ID = p_doctype_id
AND SHOW_NOTES.BIZRULE_ID = R_SHOW_NOTES.BIZRULE_ID
AND SHOW_NOTES.NAME = 'SHOW_BIDDER_NOTES'
AND R_MULI_ROUND.DOCTYPE_ID = p_doctype_id
AND MULI_ROUND.BIZRULE_ID = R_MULI_ROUND.BIZRULE_ID
AND MULI_ROUND.NAME = 'ALLOW_MULTIPLE_ROUNDS'
AND R_AUTO_EXTND.DOCTYPE_ID = p_doctype_id
AND AUTO_EXTND.BIZRULE_ID = R_AUTO_EXTND.BIZRULE_ID
AND AUTO_EXTND.NAME = 'AUTO_EXTENSION'
AND R_AUTO_XTN_ALL.DOCTYPE_ID = p_doctype_id
AND AUTO_XTN_ALL.BIZRULE_ID = R_AUTO_XTN_ALL.BIZRULE_ID
AND AUTO_XTN_ALL.NAME = 'AUTO_EXTEND_ALLLINE'
AND R_AUTO_XTN_LENGTH.DOCTYPE_ID = p_doctype_id
AND AUTO_XTN_LENGTH.BIZRULE_ID = R_AUTO_XTN_LENGTH.BIZRULE_ID
AND AUTO_XTN_LENGTH.NAME = 'AUTO_EXTEND_DURATION'
AND R_AUTO_XTN_TYPE.DOCTYPE_ID = p_doctype_id
AND AUTO_XTN_TYPE.BIZRULE_ID = R_AUTO_XTN_TYPE.BIZRULE_ID
AND AUTO_XTN_TYPE.NAME = 'AUTO_EXTEND_START_TIME'
AND R_GLBL_AGREMNT.DOCTYPE_ID = p_doctype_id
AND GLBL_AGREMNT.BIZRULE_ID = R_GLBL_AGREMNT.BIZRULE_ID
AND GLBL_AGREMNT.NAME = 'GLOBAL_AGREEMENT'
AND R_MIN_REL_AMT.DOCTYPE_ID = p_doctype_id
AND MIN_REL_AMT.BIZRULE_ID = R_MIN_REL_AMT.BIZRULE_ID
AND MIN_REL_AMT.NAME = 'MIN_RELEASE_AMOUNT'
AND R_ALLOW_EVENT.DOCTYPE_ID = p_doctype_id
AND ALLOW_EVENT.BIZRULE_ID = R_ALLOW_EVENT.BIZRULE_ID
AND ALLOW_EVENT.NAME = 'ALLOW_EVENTS'
AND R_BID_RANK.DOCTYPE_ID = p_doctype_id
AND BID_RANK.BIZRULE_ID = R_BID_RANK.BIZRULE_ID
AND BID_RANK.NAME = 'BID_RANKING'
AND R_BILL_LOC.DOCTYPE_ID = p_doctype_id
AND BILL_LOC.BIZRULE_ID = R_BILL_LOC.BIZRULE_ID
AND BILL_LOC.NAME = 'BILL_TO_LOCATION'
AND R_OLD_BILL_LOC.DOCTYPE_ID = p_source_doctype_id
AND R_OLD_BILL_LOC.BIZRULE_ID = BILL_LOC.BIZRULE_ID
AND R_SHIP_LOC.DOCTYPE_ID = p_doctype_id
AND SHIP_LOC.BIZRULE_ID = R_SHIP_LOC.BIZRULE_ID
AND SHIP_LOC.NAME = 'SHIP_TO_LOCATION'
AND R_OLD_SHIP_LOC.DOCTYPE_ID = p_source_doctype_id
AND R_OLD_SHIP_LOC.BIZRULE_ID = SHIP_LOC.BIZRULE_ID
AND R_CARRIER.DOCTYPE_ID = p_doctype_id
AND CARRIER.BIZRULE_ID = R_CARRIER.BIZRULE_ID
AND CARRIER.NAME = 'FREIGHT_CARRIER'
AND R_FRIEIGHT_TERMS.DOCTYPE_ID = p_doctype_id
AND FRIEIGHT_TERMS.BIZRULE_ID = R_FRIEIGHT_TERMS.BIZRULE_ID
AND FRIEIGHT_TERMS.NAME = 'FREIGHT_TERMS'
AND R_FOB_CODE.DOCTYPE_ID = p_doctype_id
AND FOB_CODE.BIZRULE_ID = R_FOB_CODE.BIZRULE_ID
AND FOB_CODE.NAME = 'FOB_TERMS'
AND R_BID_LIST.DOCTYPE_ID = p_doctype_id
AND BID_LIST.BIZRULE_ID = R_BID_LIST.BIZRULE_ID
AND BID_LIST.NAME = 'BID_LIST_TYPE'
AND R_BID_FREQ_CODE.DOCTYPE_ID = p_doctype_id
AND BID_FREQ_CODE.BIZRULE_ID = R_BID_FREQ_CODE.BIZRULE_ID
AND BID_FREQ_CODE.NAME = 'BID_FREQUENCY'
AND R_BID_QTY_SCOPE.DOCTYPE_ID = p_doctype_id
AND BID_QTY_SCOPE.BIZRULE_ID = R_BID_QTY_SCOPE.BIZRULE_ID
AND BID_QTY_SCOPE.NAME = 'BID_QUANTITY_SCOPE'
AND R_RANK_INDICATOR.DOCTYPE_ID = p_doctype_id
AND RANK_INDICATOR.BIZRULE_ID = R_RANK_INDICATOR.BIZRULE_ID
AND RANK_INDICATOR.NAME = 'RANK_INDICATOR'
AND R_SHOW_SCORE.DOCTYPE_ID = p_doctype_id
AND SHOW_SCORE.BIZRULE_ID = R_SHOW_SCORE.BIZRULE_ID
AND SHOW_SCORE.NAME = 'RANK_INDICATOR'
AND R_MIN_BID_CHANGE_TYPE.DOCTYPE_ID = p_doctype_id
AND MIN_BID_CHANGE_TYPE.BIZRULE_ID = R_MIN_BID_CHANGE_TYPE.BIZRULE_ID
AND MIN_BID_CHANGE_TYPE.NAME = 'BID_CHANGE_TYPE'
AND R_PAY_TERMS.DOCTYPE_ID = p_doctype_id
AND PAY_TERMS.BIZRULE_ID = R_PAY_TERMS.BIZRULE_ID
AND PAY_TERMS.NAME = 'PAYMENT_TERMS'
AND R_ALLOW_PE.DOCTYPE_ID = p_doctype_id
AND ALLOW_PE.BIZRULE_ID = R_ALLOW_PE.BIZRULE_ID
AND ALLOW_PE.NAME = 'ALLOW_PRICE_ELEMENT'
AND R_NO_PRICE_QTY.DOCTYPE_ID = p_doctype_id
AND NO_PRICE_QTY.BIZRULE_ID = R_NO_PRICE_QTY.BIZRULE_ID
AND NO_PRICE_QTY.NAME = 'NO_PRICE_QUANTITY_ITEMS'
AND R_START_PRICE.DOCTYPE_ID = p_doctype_id
AND START_PRICE.BIZRULE_ID = R_START_PRICE.BIZRULE_ID
AND START_PRICE.NAME = 'START_PRICE'
AND R_RESERVE_PRICE.DOCTYPE_ID = p_doctype_id
AND RESERVE_PRICE.BIZRULE_ID = R_RESERVE_PRICE.BIZRULE_ID
AND RESERVE_PRICE.NAME = 'RESERVE_PRICE'
AND R_TARGET_PRICE.DOCTYPE_ID = p_doctype_id
AND TARGET_PRICE.BIZRULE_ID = R_TARGET_PRICE.BIZRULE_ID
AND TARGET_PRICE.NAME = 'TARGET_PRICE'
AND R_CURRENT_PRICE.DOCTYPE_ID = p_doctype_id
AND CURRENT_PRICE.BIZRULE_ID = R_CURRENT_PRICE.BIZRULE_ID
AND CURRENT_PRICE.NAME = 'CURRENT_PRICE'
AND R_BEST_PRICE.DOCTYPE_ID = p_doctype_id
AND BEST_PRICE.BIZRULE_ID = R_BEST_PRICE.BIZRULE_ID
AND BEST_PRICE.NAME = 'BEST_PRICE'
AND R_PRICE_BREAK.DOCTYPE_ID = p_doctype_id
AND PRICE_BREAK.BIZRULE_ID = R_PRICE_BREAK.BIZRULE_ID
AND PRICE_BREAK.NAME = 'PRICE_BREAK'
AND R_ALLOW_PRICE_DIFF.DOCTYPE_ID = p_doctype_id
AND ALLOW_PRICE_DIFF.BIZRULE_ID = R_ALLOW_PRICE_DIFF.BIZRULE_ID
AND ALLOW_PRICE_DIFF.NAME = 'ALLOW_PRICE_DIFFERENTIAL'
AND R_AWARD_TYPE.DOCTYPE_ID = p_source_doctype_id
AND AWARD_TYPE.BIZRULE_ID = R_AWARD_TYPE.BIZRULE_ID
AND AWARD_TYPE.NAME = 'AWARD_TYPE'
AND R_ALLOW_UNSOL_OFFER_LINES.DOCTYPE_ID = p_doctype_id
AND ALLOW_UNSOL_OFFER_LINES.BIZRULE_ID = R_ALLOW_UNSOL_OFFER_LINES.BIZRULE_ID
AND ALLOW_UNSOL_OFFER_LINES.NAME = 'ALLOW_UNSOL_OFFER_LINES'
AND R_ALLOW_WITHDRAW_FLAG.DOCTYPE_ID = p_doctype_id
AND ALLOW_WITHDRAW_FLAG.BIZRULE_ID = R_ALLOW_WITHDRAW_FLAG.BIZRULE_ID
AND ALLOW_WITHDRAW_FLAG.NAME = 'ALLOW_WITHDRAW'
AND R_ALLOW_MULTIPLE_ACTIVE_OFFERS.DOCTYPE_ID = p_doctype_id
AND ALLOW_MULTIPLE_ACTIVE_OFFERS.BIZRULE_ID = R_ALLOW_MULTIPLE_ACTIVE_OFFERS.BIZRULE_ID
AND ALLOW_MULTIPLE_ACTIVE_OFFERS.NAME = 'ALLOW_MULTIPLE_ACTIVE_OFFERS'
AND R_ALLOW_STAGGERED_AWARDS.DOCTYPE_ID = p_doctype_id
AND ALLOW_STAGGERED_AWARDS.BIZRULE_ID = R_ALLOW_STAGGERED_AWARDS.BIZRULE_ID
AND ALLOW_STAGGERED_AWARDS.NAME = 'ALLOW_STAGGERED_AWARDS';
SELECT DISTINCT
PSP.DEFAULT_RATE_TYPE,
SOB.CURRENCY_CODE
INTO
l_default_rate_type,
l_default_currency_code
FROM PO_SYSTEM_PARAMETERS_ALL PSP,
FINANCIALS_SYSTEM_PARAMS_ALL FSP,
GL_SETS_OF_BOOKS SOB
WHERE PSP.ORG_ID = FSP.ORG_ID (+)
AND FSP.SET_OF_BOOKS_ID = SOB.SET_OF_BOOKS_ID (+)
AND PSP.ORG_ID = FND_PROFILE.VALUE('ORG_ID');
SELECT COUNT(1)
INTO
l_temp_labor_count
FROM PON_AUCTION_ITEM_PRICES_ALL
WHERE PURCHASE_BASIS = 'TEMP LABOR'
AND AUCTION_HEADER_ID = p_source_auction_header_id;
p_update_clause IN VARCHAR2,
p_retain_attachments IN VARCHAR2,
p_contracts_doctype IN VARCHAR2,
p_contract_type IN VARCHAR2,
p_document_number IN VARCHAR2
)
IS
l_source_doc_id NUMBER;
IF (p_update_clause = 'Y') THEN
l_keep_version := 'N';
SELECT FND_USER.EMPLOYEE_ID
INTO l_auc_contact_id
FROM FND_USER, HZ_RELATIONSHIPS
WHERE FND_USER.USER_ID = FND_GLOBAL.USER_ID()
AND HZ_RELATIONSHIPS.OBJECT_ID = l_site_id
AND HZ_RELATIONSHIPS.SUBJECT_ID = FND_USER.PERSON_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;
select auction_header_id_prev_amend
into l_source_orig_amend_id
from pon_auction_headers_all
where auction_header_id = p_source_auction_header_id;
(SELECT ATTACHED_DOCUMENT_ID,PK1_VALUE
FROM FND_ATTACHED_DOCUMENTS
WHERE pk1_value = to_char(p_source_auction_header_id)
AND ENTITY_NAME = 'PON_AUCTION_HEADERS_ALL'
AND category_id <> (SELECT category_id FROM fnd_document_categories_vl
WHERE NAME = 'PonAmendmentSpecific')
UNION
SELECT ATTACHED_DOCUMENT_ID,PK1_VALUE
FROM FND_ATTACHED_DOCUMENTS
WHERE pk1_value = To_Char(l_source_orig_amend_id)
AND ENTITY_NAME = 'PON_AUCTION_HEADERS_ALL'
AND ATTACHED_DOCUMENT_ID NOT IN
(SELECT Nvl(ORIG_ATTACH_DOC_ID,-1) FROM FND_ATTACHED_DOCUMENTS
WHERE pk1_value = to_char(p_source_auction_header_id))) LOOP
FND_ATTACHED_DOCUMENTS2_PKG.COPY_ATTACHMENTS (
X_from_entity_name => 'PON_AUCTION_HEADERS_ALL',
X_from_pk1_value => base_attach_rec.pk1_value,
X_to_entity_name => 'PON_AUCTION_HEADERS_ALL',
X_to_pk1_value => to_char(p_auction_header_id), -- PK1_VALUE
X_created_by => p_user_id, -- CREATED_BY
X_last_update_login => fnd_global.login_id, -- LAST_UPDATE_LOGIN
X_orig_attach_doc_id => base_attach_rec.ATTACHED_DOCUMENT_ID
);
X_last_update_login => fnd_global.login_id -- LAST_UPDATE_LOGIN
);
select
line_number,
disp_line_number,
document_disp_line_number,
parent_line_number,
sub_line_sequence_number
from
pon_auction_item_prices_all
where
auction_header_id = p_auction_header_id
order by
disp_line_number;
update pon_auction_item_prices_all
set
document_disp_line_number = l_doc_disp_line_number,
sub_line_sequence_number = l_sub_line_sequence_number
where
auction_header_id = p_auction_header_id and
line_number = line.line_number;
SELECT STYLE_ID, 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 8993731
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 8993731
INTO g_neg_style_raw
FROM PON_NEGOTIATION_STYLES
WHERE style_id = p_style_id;
SELECT STYLE_ID, 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 8993731
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 8993731
INTO g_neg_style_raw
FROM PON_AUCTION_HEADERS_ALL
WHERE auction_header_id = p_source_auction_header_id;
SELECT STYLE_ID, 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 8993731
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 8993731
INTO g_neg_style_raw
FROM PON_AUCTION_HEADERS_ALL
WHERE auction_header_id = p_source_auction_header_id;
l_delete_count NUMBER;
select count(1)
into l_delete_count
from pon_auction_item_prices_all
where auction_header_id = p_auction_header_id
and line_number >= p_from_line_number
and line_number <= p_to_line_number
and (p_lot_enabled = 'N'
and group_type in ('LOT', 'LOT_LINE')
or p_group_enabled = 'N'
and group_type in ('GROUP', 'GROUP_LINE'));
if (l_delete_count > 0) then -- {
g_line_deleted := 'Y';
delete from pon_auction_attributes
where auction_header_id = p_auction_header_id
and line_number in
(select ip.line_number
from pon_auction_item_prices_all ip
where ip.auction_header_id = p_auction_header_id
and ip.line_number >= p_from_line_number
and ip.line_number <= p_to_line_number
and (p_lot_enabled = 'N'
and ip.group_type in ('LOT', 'LOT_LINE')
or p_group_enabled = 'N'
and ip.group_type in ('GROUP', 'GROUP_LINE')));
delete from pon_attribute_scores
where auction_header_id = p_auction_header_id
and line_number in
(select ip.line_number
from pon_auction_item_prices_all ip
where ip.auction_header_id = p_auction_header_id
and ip.line_number >= p_from_line_number
and ip.line_number <= p_to_line_number
and (p_lot_enabled = 'N'
and ip.group_type in ('LOT', 'LOT_LINE')
or p_group_enabled = 'N'
and ip.group_type in ('GROUP', 'GROUP_LINE')));
delete from pon_auction_shipments
where auction_header_id = p_auction_header_id
and line_number in
(select ip.line_number
from pon_auction_item_prices_all ip
where ip.auction_header_id = p_auction_header_id
and ip.line_number >= p_from_line_number
and ip.line_number <= p_to_line_number
and (p_lot_enabled = 'N'
and ip.group_type in ('LOT', 'LOT_LINE')
or p_group_enabled = 'N'
and ip.group_type in ('GROUP', 'GROUP_LINE')));
delete from pon_price_differentials
where auction_header_id = p_auction_header_id
and line_number in
(select ip.line_number
from pon_auction_item_prices_all ip
where ip.auction_header_id = p_auction_header_id
and ip.line_number >= p_from_line_number
and ip.line_number <= p_to_line_number
and (p_lot_enabled = 'N'
and ip.group_type in ('LOT', 'LOT_LINE')
or p_group_enabled = 'N'
and ip.group_type in ('GROUP', 'GROUP_LINE')));
delete from pon_price_elements
where auction_header_id = p_auction_header_id
and line_number in
(select ip.line_number
from pon_auction_item_prices_all ip
where ip.auction_header_id = p_auction_header_id
and ip.line_number >= p_from_line_number
and ip.line_number <= p_to_line_number
and (p_lot_enabled = 'N'
and ip.group_type in ('LOT', 'LOT_LINE')
or p_group_enabled = 'N'
and ip.group_type in ('GROUP', 'GROUP_LINE')));
delete from pon_party_line_exclusions
where auction_header_id = p_auction_header_id
and line_number in
(select ip.line_number
from pon_auction_item_prices_all ip
where ip.auction_header_id = p_auction_header_id
and ip.line_number >= p_from_line_number
and ip.line_number <= p_to_line_number
and (p_lot_enabled = 'N'
and ip.group_type in ('LOT', 'LOT_LINE')
or p_group_enabled = 'N'
and ip.group_type in ('GROUP', 'GROUP_LINE')));
delete from pon_pf_supplier_values
where auction_header_id = p_auction_header_id
and line_number in
(select ip.line_number
from pon_auction_item_prices_all ip
where ip.auction_header_id = p_auction_header_id
and ip.line_number >= p_from_line_number
and ip.line_number <= p_to_line_number
and (p_lot_enabled = 'N'
and ip.group_type in ('LOT', 'LOT_LINE')
or p_group_enabled = 'N'
and ip.group_type in ('GROUP', 'GROUP_LINE')));
delete from pon_auction_item_prices_all
where auction_header_id = p_auction_header_id
and line_number >= p_from_line_number
and line_number <= p_to_line_number
and (p_lot_enabled = 'N'
and group_type in ('LOT', 'LOT_LINE')
or p_group_enabled = 'N'
and group_type in ('GROUP', 'GROUP_LINE'));
select large_neg_enabled_flag, auction_status
into l_is_large_neg_enabled, l_current_status
from
pon_auction_headers_all
where
auction_header_id = p_source_auction_hdr_id;
LOG_MESSAGE('PON_LRG_DRAFT_TO_ORD_PF_COPY','inserting rows in pon_large_neg_pf_values for new auction : ' || p_destination_auction_hdr_id || ' whihc is getting copied from auction : ' || p_source_auction_hdr_id);
insert into pon_pf_supplier_values(
auction_header_id,
line_number,
pf_seq_number,
supplier_seq_number,
value,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
select
p_destination_auction_hdr_id,
price_elements.LINE_NUMBER,
price_elements.SEQUENCE_NUMBER,
largeNegPFVal.SUPPLIER_SEQ_NUMBER,
largeNegPFVal.VALUE,
sysdate,
p_user_id,
sysdate,
p_user_id,
fnd_global.login_id
from
pon_price_elements price_elements,
pon_large_neg_pf_values largeNegPFVal
where
price_elements.AUCTION_HEADER_ID = p_source_auction_hdr_id and
largeNegPFVal.AUCTION_HEADER_ID = price_elements.AUCTION_HEADER_ID and
price_elements.PRICE_ELEMENT_TYPE_ID = largeNegPFVal.PRICE_ELEMENT_TYPE_ID and
price_elements.PRICING_BASIS = largeNegPFVal.PRICING_BASIS and
largeNegPFVal.VALUE is not null and
(price_elements.PRICE_ELEMENT_TYPE_ID, price_elements.PRICING_BASIS) in
(
select distinct PRICE_ELEMENT_TYPE_ID, PRICING_BASIS from
pon_price_elements where auction_header_id = p_destination_auction_hdr_id
--we need not have the below where condition because
--because the large_neg_pf_values will have only
--BUYER price factors and the outer select statement
--always returns the BUYER price factor details. It
--is added below in the comment for readability
--and price_elements.pf_type = 'BUYER'
) and
price_elements.line_number >= p_from_line_number and
price_elements.line_number <= p_to_line_number;
LOG_MESSAGE('PON_LRG_DRAFT_TO_LRG_PF_COPY','inserting rows in pon_large_neg_pf_values for new auction : ' || p_destination_auction_hdr_id || ' whihc is getting copied from auction : ' || p_source_auction_hdr_id);
insert into pon_large_neg_pf_values(
auction_header_id,
price_element_type_id,
pricing_basis,
supplier_seq_number,
value,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
(select
p_destination_auction_hdr_id,
p.price_element_type_id,
p.pricing_basis,
p.supplier_seq_number,
p.value,
sysdate,
p_user_id,
sysdate,
p_user_id,
fnd_global.login_id
from pon_large_neg_pf_values p, pon_price_element_types_vl vl
where
p.auction_header_id = p_source_auction_hdr_id and
p.price_element_type_id = vl.price_element_type_id and
vl.enabled_flag = 'Y' and
(p.price_element_type_id,p.pricing_basis) in
(select distinct price_element_type_id,pricing_basis from pon_price_elements
where auction_header_id = p_destination_auction_hdr_id and pf_type = 'BUYER')
);
LOG_MESSAGE('PON_ORD_DRAFT_TO_LRG_PF_COPY','inserting rows in pon_large_neg_pf_values for new auction : ' || p_destination_auction_hdr_id || ' whihc is getting copied from auction : ' || p_source_auction_hdr_id);
insert into pon_large_neg_pf_values(
auction_header_id,
price_element_type_id,
pricing_basis,
supplier_seq_number,
value,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
(
select distinct p_destination_auction_hdr_id,
price_elements.PRICE_ELEMENT_TYPE_ID,
price_elements.PRICING_BASIS,
bidding_parties.sequence,
null,
sysdate,
p_user_id,
sysdate,
p_user_id,
fnd_global.login_id
from
pon_price_elements price_elements,
pon_bidding_parties bidding_parties
where
price_elements.AUCTION_HEADER_ID = p_destination_auction_hdr_id and
price_elements.AUCTION_HEADER_ID = bidding_parties.AUCTION_HEADER_ID and
price_elements.PF_TYPE = 'BUYER'
);
p_update_clause IN VARCHAR2,
p_retain_attachments IN VARCHAR2,
p_large_auction_header_id IN NUMBER,
p_style_id IN NUMBER)
IS
l_auction_header_id NUMBER;
p_update_clause => p_update_clause,
p_retain_attachments => p_retain_attachments,
p_large_auction_header_id => p_large_auction_header_id,
p_style_id => p_style_id,
x_auction_header_id => l_auction_header_id,
x_document_number => l_document_number,
x_request_id => l_request_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
select request_id into l_request_id
from pon_auction_headers_all
where auction_header_id = p_large_auction_header_id;
select document_number into l_source_doc_num from pon_auction_headers_all where auction_Header_id = p_source_auction_header_id;
insert into pon_interface_errors (
ERROR_MESSAGE_NAME,
request_id,
auction_header_id,
application_short_name,
token1_name,
token1_value,
token2_name,
token2_value,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
expiration_date
)
values(
'PON_COPY_ERROR_MSG_'||g_message_suffix,
l_request_id,
p_large_auction_header_id,
'PON',
'DOC_NUM',
l_source_doc_num,
'REQUEST_ID',
l_request_id,
p_user_id,
SYSDATE,
p_user_id,
SYSDATE,
fnd_global.login_id,
sysdate + 7
);
SELECT has_scoring_teams_flag
INTO l_source_has_scoring_teams
FROM pon_auction_headers_all
WHERE auction_header_id = p_source_auction_header_id;
INSERT INTO pon_scoring_teams
(
auction_header_id
,team_id
,team_name
,price_visible_flag
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,orig_team_id
,instruction_text
)
SELECT
p_auction_header_id
,pon_scoring_teams_s.nextval
,old_team.team_name
,old_team.price_visible_flag
,SYSDATE -- creation_date
,p_user_id -- created_by
,SYSDATE -- last_update_date
,p_user_id -- last_updated_by
,fnd_global.login_id -- last_update_login
,old_team.team_id -- orig_team_id
,old_team.instruction_text
FROM
pon_scoring_teams old_team
WHERE old_team.auction_header_id = p_source_auction_header_id;
-- Create the team members. Do not insert any team members who are not
-- valid. The team members who are not valid would not have been
-- copied over to the collaboration team hence joining with that table
-- eliminates the unwanted ones
INSERT INTO pon_scoring_team_members
(
auction_header_id
,team_id
,user_id
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
)
SELECT
p_auction_header_id
,new_team.team_id
,old_members.user_id
,SYSDATE -- creation_date
,p_user_id -- created_by
,SYSDATE -- last_update_date
,p_user_id -- last_updated_by
,fnd_global.login_id -- last_update_login
FROM
pon_scoring_team_members old_members,
pon_scoring_teams old_team,
pon_scoring_teams new_team,
pon_neg_team_members new_collab
WHERE
new_team.auction_header_id = p_auction_header_id
AND old_team.team_id = new_team.orig_team_id
AND old_members.team_id = old_team.team_id
AND new_collab.user_id = old_members.user_id
AND new_collab.auction_header_id = new_team.auction_header_id;
INSERT INTO pon_scoring_team_sections
(
section_id
,auction_header_id
,team_id
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
)
SELECT
new_sections.section_id
,p_auction_header_id
,new_team.team_id
,SYSDATE -- creation_date
,p_user_id -- created_by
,SYSDATE -- last_update_date
,p_user_id -- last_updated_by
,fnd_global.login_id -- last_update_login
FROM
pon_auction_sections new_sections
,pon_scoring_team_sections old_team_sections
,pon_scoring_teams new_team
WHERE
new_sections.auction_header_id = p_auction_header_id
AND old_team_sections.section_id = new_sections.previous_section_id
AND new_team.orig_team_id = old_team_sections.team_id
AND new_team.auction_header_id = new_sections.auction_header_id;
p_update_clause IN VARCHAR2,
p_retain_attachments IN VARCHAR2,
p_large_auction_header_id IN NUMBER,
p_style_id IN NUMBER,
p_is_cancel_amendment IN VARCHAR2,
x_auction_header_id OUT NOCOPY NUMBER,
x_document_number OUT NOCOPY VARCHAR2,
x_request_id OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_auction_header_id NUMBER := null;
p_update_clause => p_update_clause,
p_retain_attachments => p_retain_attachments,
p_large_auction_header_id => p_large_auction_header_id,
p_style_id => p_style_id,
x_auction_header_id => x_auction_header_id,
x_document_number => x_document_number,
x_request_id => x_request_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
update PON_AUCTION_HEADERS_ALL
set AMENDMENT_FLAG = decode(p_copy_type,g_amend_copy,'Y','N'),
CANCEL_AMENDMENT = decode(p_is_cancel_amendment,'Y','Y','N')
where AUCTION_HEADER_ID = x_auction_header_id;
* Updates the Revision column, starting with zero incremented by 1.
* For new round, revision starts with zero.
*/
FUNCTION GET_REVISION_NUMBER(p_auction_header_id IN NUMBER)
RETURN NUMBER IS
l_revision NUMBER;
select
nvl((select (max(revision)+1) from pon_auction_headers_all where auction_header_id_orig_amend =
(select auction_header_id_orig_amend from pon_auction_headers_all where auction_header_id = p_auction_header_id)),0)
INTO l_revision
from dual;
SELECT supp_eval_flag
INTO l_source_is_evaluation
FROM pon_auction_headers_all
WHERE auction_header_id = p_source_auction_header_id;
INSERT INTO pon_evaluation_teams
(
auction_header_id,
team_id,
team_name,
orig_team_id,
instruction_text,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
)
SELECT p_auction_header_id,
pon_evaluation_teams_s.nextval,
old_team.team_name,
old_team.team_id,
old_team.instruction_text,
SYSDATE,
p_user_id,
SYSDATE,
p_user_id,
fnd_global.login_id
FROM pon_evaluation_teams old_team
WHERE old_team.auction_header_id = p_source_auction_header_id;
INSERT INTO pon_evaluation_team_members
(
auction_header_id,
team_id,
user_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
)
SELECT p_auction_header_id,
new_team.team_id,
old_members.user_id,
SYSDATE,
p_user_id,
SYSDATE,
p_user_id,
fnd_global.login_id
FROM pon_evaluation_team_members old_members,
pon_evaluation_teams old_team,
pon_evaluation_teams new_team,
pon_neg_team_members new_collab
WHERE new_team.auction_header_id = p_auction_header_id
AND old_team.team_id = new_team.orig_team_id
AND old_members.team_id = old_team.team_id
AND new_collab.user_id = old_members.user_id
AND new_collab.auction_header_id = new_team.auction_header_id;
INSERT INTO pon_evaluation_team_sections
(
team_id,
section_id,
auction_header_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
)
SELECT new_team.team_id,
new_sections.section_id,
p_auction_header_id,
SYSDATE,
p_user_id,
SYSDATE,
p_user_id,
fnd_global.login_id
FROM pon_auction_sections new_sections,
pon_evaluation_team_sections old_team_sections,
pon_evaluation_teams new_team
WHERE new_sections.auction_header_id = p_auction_header_id
AND old_team_sections.section_id = new_sections.previous_section_id
AND new_team.orig_team_id = old_team_sections.team_id
AND new_team.auction_header_id = new_sections.auction_header_id;
INSERT INTO PON_AUCTION_ATTR_MAPPING_B
(
MAPPING_ID,
AUCTION_HEADER_ID,
LINE_NUMBER,
ATTRIBUTE_LIST_ID,
SEQUENCE_NUMBER,
MAPPING_TYPE,
RESPONSE,
CLASS_SCHEME,
ATTR_GROUP_ID,
ATTR_INT_NAME,
DATA_LEVEL_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE,
MULTI_ROW_CODE,
SECTION_ID
)
SELECT PON.PON_ATTR_MAPPING_S.NEXTVAL,
p_auction_header_id,
LINE_NUMBER,
ATTRIBUTE_LIST_ID,
SEQUENCE_NUMBER,
MAPPING_TYPE,
RESPONSE,
CLASS_SCHEME,
ATTR_GROUP_ID,
ATTR_INT_NAME,
DATA_LEVEL_ID,
SYSDATE,
p_user_id,
fnd_global.login_id,
p_user_id,
SYSDATE,
MULTI_ROW_CODE,
SECTION_ID
FROM PON_AUCTION_ATTR_MAPPING_B
WHERE AUCTION_HEADER_ID = p_source_auction_header_id
AND MAPPING_TYPE = 'DOC_HEADER';
INSERT INTO PON_AUCTION_ATTR_MAPPING_B
(
MAPPING_ID,
AUCTION_HEADER_ID,
LINE_NUMBER,
ATTRIBUTE_LIST_ID,
SEQUENCE_NUMBER,
MAPPING_TYPE,
RESPONSE,
CLASS_SCHEME,
ATTR_GROUP_ID,
ATTR_INT_NAME,
DATA_LEVEL_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE,
MULTI_ROW_CODE,
SECTION_ID
)
SELECT PON.PON_ATTR_MAPPING_S.NEXTVAL,
p_auction_header_id,
LINE_NUMBER,
ATTRIBUTE_LIST_ID,
SEQUENCE_NUMBER,
MAPPING_TYPE,
RESPONSE,
CLASS_SCHEME,
ATTR_GROUP_ID,
ATTR_INT_NAME,
DATA_LEVEL_ID,
SYSDATE,
p_user_id,
fnd_global.login_id,
p_user_id,
SYSDATE,
MULTI_ROW_CODE,
DECODE( B.SECTION_ID, NULL, NULL,
-10000, -10000,
( SELECT SECTION_ID
FROM PON_AUCTION_SECTIONS S
WHERE S.PREVIOUS_SECTION_ID = B.SECTION_ID
AND S.AUCTION_HEADER_ID = p_auction_header_id) )
FROM PON_AUCTION_ATTR_MAPPING_B B
WHERE AUCTION_HEADER_ID = p_source_auction_header_id
AND MAPPING_TYPE IN ('DOC_REQ', 'DOC_SEC_SCORE');
INSERT INTO PON_AUCTION_ATTR_MAPPING_B
(
MAPPING_ID,
AUCTION_HEADER_ID,
LINE_NUMBER,
ATTRIBUTE_LIST_ID,
SEQUENCE_NUMBER,
MAPPING_TYPE,
RESPONSE,
CLASS_SCHEME,
ATTR_GROUP_ID,
ATTR_INT_NAME,
DATA_LEVEL_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE,
MULTI_ROW_CODE,
SECTION_ID
)
SELECT PON.PON_ATTR_MAPPING_S.NEXTVAL,
p_auction_header_id,
LINE_NUMBER,
ATTRIBUTE_LIST_ID,
SEQUENCE_NUMBER,
MAPPING_TYPE,
RESPONSE,
CLASS_SCHEME,
ATTR_GROUP_ID,
ATTR_INT_NAME,
DATA_LEVEL_ID,
SYSDATE,
p_user_id,
fnd_global.login_id,
p_user_id,
SYSDATE,
MULTI_ROW_CODE,
SECTION_ID
FROM PON_AUCTION_ATTR_MAPPING_B
WHERE AUCTION_HEADER_ID = p_source_auction_header_id
AND MAPPING_TYPE = 'CAT_LINE';
INSERT INTO PON_AUCTION_ATTR_MAPPING_B
(
MAPPING_ID,
AUCTION_HEADER_ID,
LINE_NUMBER,
ATTRIBUTE_LIST_ID,
SEQUENCE_NUMBER,
MAPPING_TYPE,
RESPONSE,
CLASS_SCHEME,
ATTR_GROUP_ID,
ATTR_INT_NAME,
DATA_LEVEL_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE,
MULTI_ROW_CODE,
SECTION_ID
)
SELECT PON.PON_ATTR_MAPPING_S.NEXTVAL,
p_auction_header_id,
LINE_NUMBER,
ATTRIBUTE_LIST_ID,
SEQUENCE_NUMBER,
MAPPING_TYPE,
RESPONSE,
CLASS_SCHEME,
ATTR_GROUP_ID,
ATTR_INT_NAME,
DATA_LEVEL_ID,
SYSDATE,
p_user_id,
fnd_global.login_id,
p_user_id,
SYSDATE,
MULTI_ROW_CODE,
SECTION_ID
FROM PON_AUCTION_ATTR_MAPPING_B
WHERE AUCTION_HEADER_ID = p_source_auction_header_id
AND MAPPING_TYPE IN ('ITEM_HEADER', 'ITEM_LINE');
p_last_update_login IN NUMBER DEFAULT NULL ,
p_orig_attach_doc_id IN NUMBER,
x_result OUT NOCOPY VARCHAR2
)
AS
payments_orig_doc_id fnd_attached_documents.orig_attach_doc_id%TYPE;
SELECT orig_attach_doc_id INTO payments_orig_doc_id
FROM fnd_attached_documents
WHERE entity_name = 'PON_AUC_PAYMENTS_SHIPMENTS_AMEND'
AND pk1_value = p_from_pk1_value
AND pk2_value = p_from_pk2_value
AND pk3_value = p_from_pk3_value
AND attached_document_id = p_orig_attach_doc_id;
SELECT pk1_value, pk2_value, pk3_value
INTO l_pk1_value, l_pk2_value, l_pk3_value
FROM fnd_attached_documents
WHERE attached_document_id = payments_orig_doc_id;
X_last_update_login => fnd_global.login_id, -- LAST_UPDATE_LOGIN
X_orig_attach_doc_id => payments_orig_doc_id
);
X_last_update_login => fnd_global.login_id, -- LAST_UPDATE_LOGIN
X_orig_attach_doc_id => p_orig_attach_doc_id
);