Product: | PON - Sourcing |
---|---|
Description: | Common View on pon_auction_headers_all |
Implementation/DBA Data: | APPS.PON_AUCTION_HEADERS_ALL_V |
SELECT AH.AUCTION_HEADER_ID
, AH.AUCTION_TITLE
, AH.AUCTION_STATUS
, AH.AUCTION_STATUS_NAME
, AH.AWARD_STATUS
, AH.AWARD_STATUS_NAME
, AH.AUCTION_TYPE
, AH.CONTRACT_TYPE
, AH.TRADING_PARTNER_CONTACT_NAME
, AH.TRADING_PARTNER_CONTACT_ID
, AH.TRADING_PARTNER_NAME
, AH.TRADING_PARTNER_NAME_UPPER
, AH.TRADING_PARTNER_ID
, AH.SHIP_TO_LOCATION_ID
, AH.BILL_TO_LOCATION_ID
, AH.OPEN_BIDDING_DATE
, DECODE ( NVL( AH.IS_PAUSED
, 'N')
, 'Y'
, ( SYSDATE + ( AH.CLOSE_BIDDING_DATE - AH.LAST_PAUSE_DATE ) )
, AH.CLOSE_BIDDING_DATE )CLOSE_BIDDING_DATE
, AH.ORIGINAL_CLOSE_BIDDING_DATE
, AH.VIEW_BY_DATE
, AH.AWARD_BY_DATE
, AH.PUBLISH_DATE
, AH.CLOSE_DATE
, AH.CANCEL_DATE
, AH.TIME_ZONE
, AH.BID_VISIBILITY_CODE
, AH.BID_LIST_TYPE
, AH.BID_FREQUENCY_CODE
, AH.BID_SCOPE_CODE
, AH.AUTO_EXTEND_FLAG
, AH.AUTO_EXTEND_NUMBER
, AH.NUMBER_OF_EXTENSIONS
, AH.NUMBER_OF_BIDS
, AH.MIN_BID_DECREMENT
, AH.PRICE_DRIVEN_AUCTION_FLAG
, AH.PAYMENT_TERMS_ID
, AH.FREIGHT_TERMS_CODE
, AH.FOB_CODE
, AH.CARRIER_CODE
, AH.CURRENCY_CODE
, AH.RATE_TYPE
, AH.RATE_DATE
, AH.RATE
, AH.NOTE_TO_BIDDERS
, AH.ATTACHMENT_FLAG
, AH.LANGUAGE_CODE
, AH.CREATION_DATE
, AH.CREATED_BY
, AH.LAST_UPDATE_DATE
, AH.LAST_UPDATED_BY
, AH.WF_ITEM_KEY
, AH.WF_ROLE_NAME
, AH.AUTO_EXTEND_ALL_LINES_FLAG
, AH.MIN_BID_INCREMENT
, AH.ALLOW_OTHER_BID_CURRENCY_FLAG
, AH.SHIPPING_TERMS_CODE
, AH.SHIPPING_TERMS
, AH.AUTO_EXTEND_DURATION
, AH.PROXY_BID_ALLOWED_FLAG
, AH.PUBLISH_RATES_TO_BIDDERS_FLAG
, AH.ATTRIBUTES_EXIST
, AH.ORDER_NUMBER
, AH.DOCUMENT_TRACKING_ID
, AH.PO_TXN_FLAG
, AH.EVENT_ID
, AH.EVENT_TITLE
, AH.SEALED_AUCTION_STATUS
, AH.SEALED_ACTUAL_UNLOCK_DATE
, AH.SEALED_ACTUAL_UNSEAL_DATE
, AH.SEALED_UNLOCK_TP_CONTACT_ID
, AH.SEALED_UNSEAL_TP_CONTACT_ID
, AH.MODE_OF_TRANSPORT
, AH.MODE_OF_TRANSPORT_CODE
, AH.PO_START_DATE
, AH.PO_END_DATE
, AH.PO_AGREED_AMOUNT
, AH.MIN_BID_CHANGE_TYPE
, AH.FULL_QUANTITY_BID_CODE
, AH.NUMBER_PRICE_DECIMALS
, AH.AUTO_EXTEND_TYPE_FLAG
, AH.AUCTION_ORIGINATION_CODE
, AH.MULTIPLE_ROUNDS_FLAG
, AH.AUCTION_HEADER_ID_ORIG_ROUND
, AH.AUCTION_HEADER_ID_PREV_ROUND
, AH.AUCTION_ROUND_NUMBER
, AH.MANUAL_CLOSE_FLAG
, AH.MANUAL_EXTEND_FLAG
, AH.AUTOEXTEND_CHANGED_FLAG
, AH.DOCTYPE_ID
, AH.OFFER_TYPE
, AH.APPROVAL_REQUIRED_FLAG
, AH.MAX_RESPONSES
, AH.RESPONSE_ALLOWED_FLAG
, AH.FOB_NEG_FLAG
, AH.CARRIER_NEG_FLAG
, AH.FREIGHT_TERMS_NEG_FLAG
, AH.MAX_RESPONSE_ITERATIONS
, AH.PAYMENT_TERMS_NEG_FLAG
, AH.MODE_OF_TRANSPORT_NEG_FLAG
, AH.CONTRACT_ID
, AH.CONTRACT_VERSION_NUM
, AH.SHIPPING_TERMS_NEG_FLAG
, AH.SHIPPING_METHOD_NEG_FLAG
, AH.USE_REGIONAL_PRICING_FLAG
, AH.SHOW_BIDDER_NOTES
, AH.DERIVE_TYPE
, AH.PRE_DELETE_AUCTION_STATUS
, AH.DRAFT_LOCKED
, AH.DRAFT_LOCKED_BY
, AH.DRAFT_LOCKED_BY_CONTACT_ID
, AH.DRAFT_LOCKED_DATE
, AH.DRAFT_UNLOCKED_BY
, AH.DRAFT_UNLOCKED_BY_CONTACT_ID
, AH.DRAFT_UNLOCKED_DATE
, AH.MAX_LINE_NUMBER
, AH.BID_RANKING
, AH.RANK_INDICATOR
, AH.SHOW_BIDDER_SCORES
, AH.OPEN_AUCTION_NOW_FLAG
, AH.PUBLISH_AUCTION_NOW_FLAG
, AH.TEMPLATE_ID
, AH.REMINDER_DATE
, AH.ORG_ID
, AH.BUYER_ID
, AH.MANUAL_EDIT_FLAG
, AH.WF_PONCOMPL_ITEM_KEY
, AH.HAS_PE_FOR_ALL_ITEMS
, AH.HAS_PRICE_ELEMENTS
, AH.PO_MIN_REL_AMOUNT
, AH.GLOBAL_AGREEMENT_FLAG
, AH.OUTCOME_STATUS
, AH.SOURCE_REQS_FLAG
, AH.AWARD_COMPLETE_DATE
, AH.WF_PONCOMPL_CURRENT_ROUND
, AH.SECURITY_LEVEL_CODE
, AH.WF_APPROVAL_ITEM_KEY
, AH.APPROVAL_STATUS
, AH.SOURCE_DOC_ID
, AH.SOURCE_DOC_NUMBER
, AH.SOURCE_DOC_MSG
, AH.SOURCE_DOC_LINE_MSG
, AH.SOURCE_DOC_MSG_APP
, (AH.AUCTION_HEADER_ID_ORIG_ROUND *100000000) + NVL(AH.AUCTION_ROUND_NUMBER
, 0)*10000+NVL(AH.AMENDMENT_NUMBER
, 0) AUCTION_SORTCOL
, PON_LOCALE_PKG.PARTY_DISPLAY_NAME(HZ.PERSON_FIRST_NAME
, HZ.PERSON_LAST_NAME
, HZ.PERSON_MIDDLE_NAME
, FL.MEANING
, HZ.PERSON_NAME_SUFFIX
, USERENV('LANG')) CREATED_BY_FULL_NAME
, HAOU.NAME
, AH.DOCUMENT_NUMBER
, DECODE(AUCTION_STATUS
, 'CANCELLED'
, 'CANCELLED'
, 'AMENDED'
, 'AMENDED'
, 'DRAFT'
, 'DRAFT'
, DECODE( NVL( AH.IS_PAUSED
, 'N')
, 'Y'
, 'PAUSED'
, DECODE(SIGN(NVL(VIEW_BY_DATE
, OPEN_BIDDING_DATE) - SYSDATE)
, 1
, 'SUBMITTED'
, DECODE(SIGN(OPEN_BIDDING_DATE - SYSDATE)
, 1
, 'PREVIEW'
, DECODE(SIGN(CLOSE_BIDDING_DATE - SYSDATE)
, 1
, 'ACTIVE'
, DECODE(AUCTION_STATUS
, 'ACTIVE'
, DECODE(AWARD_STATUS
, 'NO'
, DECODE(AWARD_APPROVAL_STATUS
, 'APPROVED'
, 'AWARD_APPROVED'
, 'REJECTED'
, 'AWARD_REJECTED'
, 'INPROCESS'
, 'AWARD_APPROVAL_INPROCESS'
, 'CLOSED')
, 'PARTIAL'
, DECODE(AWARD_APPROVAL_STATUS
, 'APPROVED'
, 'AWARD_APPROVED'
, 'REJECTED'
, 'AWARD_REJECTED'
, 'INPROCESS'
, 'AWARD_APPROVAL_INPROCESS'
, 'AWARD_IN_PROG')
, 'AWARDED'
, DECODE(AWARD_APPROVAL_STATUS
, 'APPROVED'
, 'AWARD_APPROVED'
, 'REJECTED'
, 'AWARD_REJECTED'
, 'INPROCESS'
, 'AWARD_APPROVAL_INPROCESS'
, 'AWARD_IN_PROG'))
, 'AUCTION_CLOSED'
, DECODE(AWARD_STATUS
, 'NO'
, 'ROUND_COMPLETED'
, 'QUALIFIED'
, 'RFI_COMPLETED'
, DECODE(OUTCOME_STATUS
, 'NOT_ALLOCATED'
, 'AWARD_COMPLETED'
, 'PARTIALLY_ALLOCATED'
, 'ALLOCATION_IN_PROG'
, 'ALLOCATED'
, 'ALLOCATION_IN_PROG'
, 'ALLOCATION_NOT_REQUIRED'
, 'AWARD_COMPLETED'
, OUTCOME_STATUS)))))))) NEGOTIATION_STATUS
, AH.SHARE_AWARD_DECISION
, AH.DESCRIPTION
, AH.TEMPLATE_SCOPE
, AH.TEMPLATE_STATUS
, AH.IS_TEMPLATE_FLAG
, AH.AMENDMENT_NUMBER
, AH.AMENDMENT_DESCRIPTION
, AH.AUCTION_HEADER_ID_ORIG_AMEND
, AH.AUCTION_HEADER_ID_PREV_AMEND
, AH.AWARD_APPROVAL_FLAG
, AH.AWARD_APPROVAL_STATUS
, AH.HDR_ATTR_ENABLE_WEIGHTS
, AH.HDR_ATTR_DISPLAY_SCORE
, AH.HDR_ATTR_MAXIMUM_SCORE
, AH.ATTRIBUTE_LINE_NUMBER
, AH.CONTERMS_EXIST_FLAG
, AH.CONTERMS_ARTICLES_UPD_DATE
, AH.CONTERMS_DELIV_UPD_DATE
, AH.AWARD_MODE
, AH.HAS_HDR_ATTR_FLAG
, AH.AWARD_DATE
, DECODE(AUCTION_STATUS
, 'CANCELLED'
, 'CANCELLED'
, 'AMENDED'
, 'AMENDED'
, 'DRAFT'
, 'DRAFT'
, DECODE(NVL(AH.IS_PAUSED
, 'N')
, 'Y'
, 'PAUSED'
, DECODE(SIGN(NVL(VIEW_BY_DATE
, OPEN_BIDDING_DATE) - SYSDATE)
, 1
, 'SUBMITTED'
, DECODE(SIGN(OPEN_BIDDING_DATE-SYSDATE)
, 1
, 'PREVIEW'
, DECODE(SIGN(CLOSE_BIDDING_DATE - SYSDATE)
, 1
, 'ACTIVE'
, 'CLOSED'))))) SUPPL_NEGOTIATION_STATUS
, AH.MAX_BID_COLOR_SEQUENCE_ID
, AH.HAS_ITEMS_FLAG
, AH.PF_TYPE_ALLOWED
, AH.SUPPLIER_VIEW_TYPE
, AH.IS_PAUSED
, AH.PAUSE_REMARKS
, AH.LAST_PAUSE_DATE
, AH.CLOSE_BIDDING_DATE ACTUAL_CLOSE_BIDDING_DATE
, AH.MAX_DOCUMENT_LINE_NUM
, AH.ABSTRACT_STATUS
, AH.ABSTRACT_DETAILS
, AH.INCLUDE_PDF_IN_EXTERNAL_PAGE
, AH.TEAM_SCORING_ENABLED_FLAG
, AH.HAS_SCORING_TEAMS_FLAG
, AH.SCORING_LOCK_DATE
, AH.SCORING_LOCK_TP_CONTACT_ID
, AH.STYLE_ID
, AH.LINE_ATTRIBUTE_ENABLED_FLAG
, AH.LINE_MAS_ENABLED_FLAG
, AH.PRICE_ELEMENT_ENABLED_FLAG
, AH.RFI_LINE_ENABLED_FLAG
, AH.LOT_ENABLED_FLAG
, AH.GROUP_ENABLED_FLAG
, AH.LARGE_NEG_ENABLED_FLAG
, AH.HDR_ATTRIBUTE_ENABLED_FLAG
, AH.NEG_TEAM_ENABLED_FLAG
, AH.PROXY_BIDDING_ENABLED_FLAG
, AH.POWER_BIDDING_ENABLED_FLAG
, AH.AUTO_EXTEND_ENABLED_FLAG
, AH.PO_STYLE_ID
, AH.PROGRESS_PAYMENT_TYPE
, AH.ADVANCE_NEGOTIABLE_FLAG
, AH.RECOUPMENT_NEGOTIABLE_FLAG
, AH.PROGRESS_PYMT_NEGOTIABLE_FLAG
, AH.RETAINAGE_NEGOTIABLE_FLAG
, AH.MAX_RETAINAGE_NEGOTIABLE_FLAG
, AH.SUPPLIER_ENTERABLE_PYMT_FLAG
, AH.PROJECT_ID
, AH.REQUEST_ID
, AH.REQUEST_DATE
, AH.REQUESTED_BY
, AH.IMPORT_FILE_NAME
, AH.NUMBER_OF_LINES
, AH.LAST_LINE_NUMBER
, AH.GLOBAL_TEMPLATE_FLAG
, AH.CONTRACT_TEMPLATE_ID
, AH.PRICE_BREAK_RESPONSE
, AH.BID_DECREMENT_METHOD
, AH.DISPLAY_BEST_PRICE_BLIND_FLAG
, AH.STAGGERED_CLOSING_INTERVAL
, AH.FIRST_LINE_CLOSE_DATE
, AH.ENFORCE_PREVRND_BID_PRICE_FLAG
, AH.AUTO_EXTEND_MIN_TRIGGER_RANK
, AH.PRICE_TIERS_INDICATOR
, AH.QTY_PRICE_TIERS_ENABLED_FLAG
, AH.TWO_PART_FLAG
, AH.TECHNICAL_LOCK_STATUS
, AH.TECHNICAL_EVALUATION_STATUS
, AH.TECHNICAL_ACTUAL_UNLOCK_DATE
, AH.TECHNICAL_ACTUAL_UNSEAL_DATE
, AH.TECHNICAL_UNLOCK_TP_CONTACT_ID
, AH.TECHNICAL_UNSEAL_TP_CONTACT_ID
FROM FND_LOOKUP_VALUES FL
, HR_ALL_ORGANIZATION_UNITS_TL HAOU
, HZ_PARTIES HZ
, PON_AUCTION_HEADERS_ALL AH
WHERE HAOU.ORGANIZATION_ID(+) = AH.ORG_ID
AND HAOU.LANGUAGE(+) =USERENV('LANG')
AND HZ.PARTY_ID(+) = AH.TRADING_PARTNER_CONTACT_ID
AND FL.LOOKUP_TYPE(+) = 'CONTACT_TITLE'
AND FL.LOOKUP_CODE(+) = HZ.PERSON_PRE_NAME_ADJUNCT
AND FL.VIEW_APPLICATION_ID(+) = 222
AND FL.SECURITY_GROUP_ID(+) = 0
AND FL.LANGUAGE(+) = USERENV('LANG')