Product: | PO - Purchasing |
---|---|
Description: | |
Implementation/DBA Data: | APPS.PO_HEADERS_MERGE_V |
SELECT DFT.OWNER_USER_ID
, DFT.OWNER_ROLE
, DFT.STATUS
, DFT.DRAFT_ID
, PHDA.CHANGE_ACCEPTED_FLAG
, PHDA.DELETE_FLAG
, PHDA.EMAIL_ADDRESS
, PHDA.FAX
, PHDA.SUPPLIER_NOTIF_METHOD
, PHDA.AUTO_SOURCING_FLAG
, PHDA.UPDATE_SOURCING_RULES_FLAG
, PHDA.RETRO_PRICE_APPLY_UPDATES_FLAG
, PHDA.RETRO_PRICE_COMM_UPDATES_FLAG
, PHDA.CLOSED_CODE
, PHDA.USSGL_TRANSACTION_CODE
, PHDA.GOVERNMENT_CONTEXT
, PHDA.REQUEST_ID
, PHDA.PROGRAM_APPLICATION_ID
, PHDA.PROGRAM_ID
, PHDA.PROGRAM_UPDATE_DATE
, PHDA.ORG_ID
, PHDA.COMMENTS
, PHDA.REPLY_DATE
, PHDA.REPLY_METHOD_LOOKUP_CODE
, PHDA.RFQ_CLOSE_DATE
, PHDA.QUOTE_TYPE_LOOKUP_CODE
, PHDA.QUOTATION_CLASS_CODE
, PHDA.QUOTE_WARNING_DELAY_UNIT
, PHDA.QUOTE_WARNING_DELAY
, PHDA.QUOTE_VENDOR_QUOTE_NUMBER
, PHDA.ACCEPTANCE_REQUIRED_FLAG
, PHDA.ACCEPTANCE_DUE_DATE
, PHDA.CLOSED_DATE
, PHDA.USER_HOLD_FLAG
, PHDA.APPROVAL_REQUIRED_FLAG
, PHDA.CANCEL_FLAG
, PHDA.FIRM_STATUS_LOOKUP_CODE
, PHDA.FIRM_DATE
, PHDA.FROZEN_FLAG
, PHDA.EDI_PROCESSED_FLAG
, PHDA.EDI_PROCESSED_STATUS
, PHDA.ATTRIBUTE_CATEGORY
, PHDA.ATTRIBUTE1
, PHDA.ATTRIBUTE2
, PHDA.ATTRIBUTE3
, PHDA.ATTRIBUTE4
, PHDA.ATTRIBUTE5
, PHDA.ATTRIBUTE6
, PHDA.ATTRIBUTE7
, PHDA.ATTRIBUTE8
, PHDA.ATTRIBUTE9
, PHDA.ATTRIBUTE10
, PHDA.ATTRIBUTE11
, PHDA.ATTRIBUTE12
, PHDA.ATTRIBUTE13
, PHDA.ATTRIBUTE14
, PHDA.ATTRIBUTE15
, PHDA.CREATED_BY
, PHDA.VENDOR_ID
, PHDA.VENDOR_SITE_ID
, PHDA.VENDOR_CONTACT_ID
, PHDA.SHIP_TO_LOCATION_ID
, PHDA.BILL_TO_LOCATION_ID
, PHDA.TERMS_ID
, PHDA.SHIP_VIA_LOOKUP_CODE
, PHDA.FOB_LOOKUP_CODE
, PHDA.FREIGHT_TERMS_LOOKUP_CODE
, PHDA.STATUS_LOOKUP_CODE
, PHDA.CURRENCY_CODE
, PHDA.RATE_TYPE
, PHDA.RATE_DATE
, PHDA.RATE
, PHDA.FROM_HEADER_ID
, PHDA.FROM_TYPE_LOOKUP_CODE
, PHDA.START_DATE
, PHDA.END_DATE
, PHDA.BLANKET_TOTAL_AMOUNT
, PHDA.AUTHORIZATION_STATUS
, PHDA.REVISION_NUM
, PHDA.REVISED_DATE
, PHDA.APPROVED_FLAG
, PHDA.APPROVED_DATE
, PHDA.AMOUNT_LIMIT
, PHDA.MIN_RELEASE_AMOUNT
, PHDA.NOTE_TO_AUTHORIZER
, PHDA.NOTE_TO_VENDOR
, PHDA.NOTE_TO_RECEIVER
, PHDA.PRINT_COUNT
, PHDA.PRINTED_DATE
, PHDA.VENDOR_ORDER_NUM
, PHDA.CONFIRMING_ORDER_FLAG
, PHDA.PO_HEADER_ID
, PHDA.AGENT_ID
, PHDA.TYPE_LOOKUP_CODE
, PHDA.LAST_UPDATE_DATE
, PHDA.LAST_UPDATED_BY
, PHDA.SEGMENT1
, PHDA.SUMMARY_FLAG
, PHDA.ENABLED_FLAG
, PHDA.SEGMENT2
, PHDA.SEGMENT3
, PHDA.SEGMENT4
, PHDA.SEGMENT5
, PHDA.START_DATE_ACTIVE
, PHDA.END_DATE_ACTIVE
, PHDA.LAST_UPDATE_LOGIN
, PHDA.CREATION_DATE
, PHDA.SUPPLY_AGREEMENT_FLAG
, PHDA.GLOBAL_ATTRIBUTE_CATEGORY
, PHDA.GLOBAL_ATTRIBUTE1
, PHDA.GLOBAL_ATTRIBUTE2
, PHDA.GLOBAL_ATTRIBUTE3
, PHDA.GLOBAL_ATTRIBUTE4
, PHDA.GLOBAL_ATTRIBUTE5
, PHDA.GLOBAL_ATTRIBUTE6
, PHDA.GLOBAL_ATTRIBUTE7
, PHDA.GLOBAL_ATTRIBUTE8
, PHDA.GLOBAL_ATTRIBUTE9
, PHDA.GLOBAL_ATTRIBUTE10
, PHDA.GLOBAL_ATTRIBUTE11
, PHDA.GLOBAL_ATTRIBUTE12
, PHDA.GLOBAL_ATTRIBUTE13
, PHDA.GLOBAL_ATTRIBUTE14
, PHDA.GLOBAL_ATTRIBUTE15
, PHDA.GLOBAL_ATTRIBUTE16
, PHDA.GLOBAL_ATTRIBUTE17
, PHDA.GLOBAL_ATTRIBUTE18
, PHDA.GLOBAL_ATTRIBUTE19
, PHDA.GLOBAL_ATTRIBUTE20
, PHDA.INTERFACE_SOURCE_CODE
, PHDA.REFERENCE_NUM
, PHDA.WF_ITEM_TYPE
, PHDA.WF_ITEM_KEY
, PHDA.PCARD_ID
, PHDA.PRICE_UPDATE_TOLERANCE
, PHDA.MRC_RATE_TYPE
, PHDA.MRC_RATE_DATE
, PHDA.MRC_RATE
, PHDA.PAY_ON_CODE
, PHDA.XML_FLAG
, PHDA.XML_SEND_DATE
, PHDA.XML_CHANGE_SEND_DATE
, PHDA.GLOBAL_AGREEMENT_FLAG
, PHDA.CONSIGNED_CONSUMPTION_FLAG
, PHDA.CBC_ACCOUNTING_DATE
, PHDA.CONSUME_REQ_DEMAND_FLAG
, PHDA.CHANGE_REQUESTED_BY
, PHDA.SHIPPING_CONTROL
, PHDA.CONTERMS_EXIST_FLAG
, PHDA.CONTERMS_ARTICLES_UPD_DATE
, PHDA.CONTERMS_DELIV_UPD_DATE
, PHDA.ENCUMBRANCE_REQUIRED_FLAG
, PHDA.PENDING_SIGNATURE_FLAG
, PHDA.CHANGE_SUMMARY
, PHDA.DOCUMENT_CREATION_METHOD
, PHDA.SUBMIT_DATE
FROM PO_HEADERS_DRAFT_ALL PHDA
, PO_DRAFTS DFT
WHERE PHDA.DRAFT_ID = DFT.DRAFT_ID
AND NVL(PHDA.DELETE_FLAG
, 'N') = 'N'
AND DFT.OWNER_ROLE = PO_GLOBAL.ROLE
AND DFT.STATUS <> 'COMPLETED' UNION ALL SELECT TO_NUMBER(NULL)
, NULL
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, PHA.EMAIL_ADDRESS
, PHA.FAX
, PHA.SUPPLIER_NOTIF_METHOD
, PHA.AUTO_SOURCING_FLAG
, PHA.UPDATE_SOURCING_RULES_FLAG
, PHA.RETRO_PRICE_APPLY_UPDATES_FLAG
, PHA.RETRO_PRICE_COMM_UPDATES_FLAG
, PHA.CLOSED_CODE
, PHA.USSGL_TRANSACTION_CODE
, PHA.GOVERNMENT_CONTEXT
, PHA.REQUEST_ID
, PHA.PROGRAM_APPLICATION_ID
, PHA.PROGRAM_ID
, PHA.PROGRAM_UPDATE_DATE
, PHA.ORG_ID
, PHA.COMMENTS
, PHA.REPLY_DATE
, PHA.REPLY_METHOD_LOOKUP_CODE
, PHA.RFQ_CLOSE_DATE
, PHA.QUOTE_TYPE_LOOKUP_CODE
, PHA.QUOTATION_CLASS_CODE
, PHA.QUOTE_WARNING_DELAY_UNIT
, PHA.QUOTE_WARNING_DELAY
, PHA.QUOTE_VENDOR_QUOTE_NUMBER
, PHA.ACCEPTANCE_REQUIRED_FLAG
, PHA.ACCEPTANCE_DUE_DATE
, PHA.CLOSED_DATE
, PHA.USER_HOLD_FLAG
, PHA.APPROVAL_REQUIRED_FLAG
, PHA.CANCEL_FLAG
, PHA.FIRM_STATUS_LOOKUP_CODE
, PHA.FIRM_DATE
, PHA.FROZEN_FLAG
, PHA.EDI_PROCESSED_FLAG
, PHA.EDI_PROCESSED_STATUS
, PHA.ATTRIBUTE_CATEGORY
, PHA.ATTRIBUTE1
, PHA.ATTRIBUTE2
, PHA.ATTRIBUTE3
, PHA.ATTRIBUTE4
, PHA.ATTRIBUTE5
, PHA.ATTRIBUTE6
, PHA.ATTRIBUTE7
, PHA.ATTRIBUTE8
, PHA.ATTRIBUTE9
, PHA.ATTRIBUTE10
, PHA.ATTRIBUTE11
, PHA.ATTRIBUTE12
, PHA.ATTRIBUTE13
, PHA.ATTRIBUTE14
, PHA.ATTRIBUTE15
, PHA.CREATED_BY
, PHA.VENDOR_ID
, PHA.VENDOR_SITE_ID
, PHA.VENDOR_CONTACT_ID
, PHA.SHIP_TO_LOCATION_ID
, PHA.BILL_TO_LOCATION_ID
, PHA.TERMS_ID
, PHA.SHIP_VIA_LOOKUP_CODE
, PHA.FOB_LOOKUP_CODE
, PHA.FREIGHT_TERMS_LOOKUP_CODE
, PHA.STATUS_LOOKUP_CODE
, PHA.CURRENCY_CODE
, PHA.RATE_TYPE
, PHA.RATE_DATE
, PHA.RATE
, PHA.FROM_HEADER_ID
, PHA.FROM_TYPE_LOOKUP_CODE
, PHA.START_DATE
, PHA.END_DATE
, PHA.BLANKET_TOTAL_AMOUNT
, PHA.AUTHORIZATION_STATUS
, PHA.REVISION_NUM
, PHA.REVISED_DATE
, PHA.APPROVED_FLAG
, PHA.APPROVED_DATE
, PHA.AMOUNT_LIMIT
, PHA.MIN_RELEASE_AMOUNT
, PHA.NOTE_TO_AUTHORIZER
, PHA.NOTE_TO_VENDOR
, PHA.NOTE_TO_RECEIVER
, PHA.PRINT_COUNT
, PHA.PRINTED_DATE
, PHA.VENDOR_ORDER_NUM
, PHA.CONFIRMING_ORDER_FLAG
, PHA.PO_HEADER_ID
, PHA.AGENT_ID
, PHA.TYPE_LOOKUP_CODE
, PHA.LAST_UPDATE_DATE
, PHA.LAST_UPDATED_BY
, PHA.SEGMENT1
, PHA.SUMMARY_FLAG
, PHA.ENABLED_FLAG
, PHA.SEGMENT2
, PHA.SEGMENT3
, PHA.SEGMENT4
, PHA.SEGMENT5
, PHA.START_DATE_ACTIVE
, PHA.END_DATE_ACTIVE
, PHA.LAST_UPDATE_LOGIN
, PHA.CREATION_DATE
, PHA.SUPPLY_AGREEMENT_FLAG
, PHA.GLOBAL_ATTRIBUTE_CATEGORY
, PHA.GLOBAL_ATTRIBUTE1
, PHA.GLOBAL_ATTRIBUTE2
, PHA.GLOBAL_ATTRIBUTE3
, PHA.GLOBAL_ATTRIBUTE4
, PHA.GLOBAL_ATTRIBUTE5
, PHA.GLOBAL_ATTRIBUTE6
, PHA.GLOBAL_ATTRIBUTE7
, PHA.GLOBAL_ATTRIBUTE8
, PHA.GLOBAL_ATTRIBUTE9
, PHA.GLOBAL_ATTRIBUTE10
, PHA.GLOBAL_ATTRIBUTE11
, PHA.GLOBAL_ATTRIBUTE12
, PHA.GLOBAL_ATTRIBUTE13
, PHA.GLOBAL_ATTRIBUTE14
, PHA.GLOBAL_ATTRIBUTE15
, PHA.GLOBAL_ATTRIBUTE16
, PHA.GLOBAL_ATTRIBUTE17
, PHA.GLOBAL_ATTRIBUTE18
, PHA.GLOBAL_ATTRIBUTE19
, PHA.GLOBAL_ATTRIBUTE20
, PHA.INTERFACE_SOURCE_CODE
, PHA.REFERENCE_NUM
, PHA.WF_ITEM_TYPE
, PHA.WF_ITEM_KEY
, PHA.PCARD_ID
, PHA.PRICE_UPDATE_TOLERANCE
, PHA.MRC_RATE_TYPE
, PHA.MRC_RATE_DATE
, PHA.MRC_RATE
, PHA.PAY_ON_CODE
, PHA.XML_FLAG
, PHA.XML_SEND_DATE
, PHA.XML_CHANGE_SEND_DATE
, PHA.GLOBAL_AGREEMENT_FLAG
, PHA.CONSIGNED_CONSUMPTION_FLAG
, PHA.CBC_ACCOUNTING_DATE
, PHA.CONSUME_REQ_DEMAND_FLAG
, PHA.CHANGE_REQUESTED_BY
, PHA.SHIPPING_CONTROL
, PHA.CONTERMS_EXIST_FLAG
, PHA.CONTERMS_ARTICLES_UPD_DATE
, PHA.CONTERMS_DELIV_UPD_DATE
, PHA.ENCUMBRANCE_REQUIRED_FLAG
, PHA.PENDING_SIGNATURE_FLAG
, PHA.CHANGE_SUMMARY
, PHA.DOCUMENT_CREATION_METHOD
, PHA.SUBMIT_DATE
FROM PO_HEADERS_ALL PHA
WHERE NOT EXISTS (SELECT 1
FROM PO_HEADERS_DRAFT_ALL PHDA1
, PO_DRAFTS DFT1
WHERE PHA.PO_HEADER_ID = PHDA1.PO_HEADER_ID
AND PHDA1.DRAFT_ID = DFT1.DRAFT_ID
AND DFT1.OWNER_ROLE = PO_GLOBAL.ROLE
AND DFT1.STATUS <> 'COMPLETED')