SELECT
POH.SEGMENT1 PO_NUMBER,
POH.TYPE_LOOKUP_CODE "Document Type Code",
POS.STYLE_NAME "Style Display Name",
PRC_BU.BU_NAME "PROCUREMENT BU",
REQ_BU.BU_NAME "REQUISTIONING BU",
LE.NAME "Sold-to Legal Entity",
BILL_BU.BU_NAME "BILL TO BU",
(SELECT PPF.FULL_NAME FROM PER_PERSON_NAMES_F PPF
WHERE POH.AGENT_ID = PPF.PERSON_ID
AND PPF.NAME_TYPE='GLOBAL'
AND TRUNC(SYSDATE) BETWEEN NVL(PPF.EFFECTIVE_START_DATE,SYSDATE-1) AND NVL(PPF.EFFECTIVE_END_DATE,SYSDATE+1)
) Buyer,
POH.CURRENCY_CODE ,
POH.RATE ,
POH.RATE_TYPE ,
TO_CHAR(POH.RATE_DATE,'YYYY/MM/DD') "Rate Date",
POH.COMMENTS ,
HRLB.LOCATION_NAME "Bill-to Location",
HRLS.LOCATION_NAME "Ship-to Location",
SUP.VENDOR_NAME "Supplier",
SUP.SEGMENT1 "Supplier Number",
SITE.VENDOR_SITE_CODE "Supplier Site",
CONT.PARTY_NAME "Supplier Contact",
POH.VENDOR_ORDER_NUM "Vendor Order Num",
POH.FOB_LOOKUP_CODE "FOB",
POH.FREIGHT_TERMS_LOOKUP_CODE "Freight Terms",
POH.PAY_ON_CODE "Pay On Code",
TERM.NAME "Payment Terms",
POH.ACCEPTANCE_REQUIRED_FLAG "Required Acknowledgment",
POH.ACCEPTANCE_WITHIN_DAYS "Acknowledge Within (Days)",
POH.SUPPLIER_NOTIF_METHOD "Communication Method",
POH.EMAIL_ADDRESS "To Email",
POH.CONFIRMING_ORDER_FLAG "Confirming order",
POH.NOTE_TO_RECEIVER "Note to Receiver",
POH.NOTE_TO_VENDOR "Note to Supplier",
POH.DEFAULT_TAXATION_COUNTRY "Default Taxation Country Code",
POH.TAX_DOCUMENT_SUBTYPE "Tax Document Subtype Code" ,
NVL((SELECT PEA.EMAIL_ADDRESS FROM PER_EMAIL_ADDRESSES PEA
WHERE 1=1
AND POH.AGENT_ID =PEA.PERSON_ID
AND PEA.EMAIL_TYPE='W1'
AND SYSDATE BETWEEN PEA.DATE_FROM AND NVL(PEA.DATE_TO,SYSDATE)
),
(SELECT PEA.EMAIL_ADDRESS FROM PER_EMAIL_ADDRESSES PEA
WHERE 1=1
AND POH.AGENT_ID =PEA.PERSON_ID
AND PEA.EMAIL_TYPE='H1'
AND SYSDATE BETWEEN PEA.DATE_FROM AND NVL(PEA.DATE_TO,SYSDATE)
)) "Buyer E-mail",
POH.MODE_OF_TRANSPORT "Mode of Transport",
POH.SERVICE_LEVEL "Service Level",
POH.FIRST_PTY_REG_ID "First Party Tax Registration Number",
POH.THIRD_PTY_REG_ID "Third Party Tax Registration Number",
POH.BUYER_MANAGED_TRANSPORT_FLAG "Buyer Managed Transportation",
POH.MASTER_CONTRACT_ID "Master Contract Number",
NULL MASTER_CONTRACT_TYPE,
POH.CC_EMAIL_ADDRESS "Cc Email",
POH.BCC_EMAIL_ADDRESS "Bcc Email",
POH.DOCUMENT_STATUS "Document Status",
POH.PO_HEADER_ID,
POH.CREATED_BY "Created BY",
TO_CHAR(POH.CREATION_DATE,'YYYY/MM/DD') "Creation Date",
POH.LAST_UPDATED_BY,
TO_CHAR(POH.LAST_UPDATE_DATE,'YYYY/MM/DD') "LAST_UPDATE_DATE",
POH.OBJECT_VERSION_NUMBER
FROM
PO_HEADERS_ALL POH,
HZ_PARTIES PARTY,
AP_TERMS_TL TERM,
POZ_SUPPLIERS_V SUP,
POZ_SUPPLIER_SITES_V SITE,
POZ_SUPPLIER_CONTACTS_V CONT,
HR_LOCATIONS_ALL HRLS,
HR_LOCATIONS_ALL HRLB,
XLE_ENTITY_PROFILES LE,
FUN_ALL_BUSINESS_UNITS_V BILL_BU,
FUN_ALL_BUSINESS_UNITS_V PRC_BU,
FUN_ALL_BUSINESS_UNITS_V REQ_BU,
PO_DOC_STYLE_HEADERS POS
WHERE 1=1
AND POH.CARRIER_ID = PARTY.PARTY_ID (+)
AND POS.STYLE_ID=POH.STYLE_ID
AND POH.VENDOR_ID = SUP.VENDOR_ID
AND POH.VENDOR_SITE_ID=SITE.VENDOR_SITE_ID
AND SUP.VENDOR_ID=SITE.VENDOR_ID
AND POH.VENDOR_CONTACT_ID=CONT.VENDOR_CONTACT_ID(+)
AND POH.PRC_BU_ID = PRC_BU.BU_ID(+)
AND POH.REQ_BU_ID = REQ_BU.BU_ID(+)
AND POH.BILLTO_BU_ID = BILL_BU.BU_ID(+)
AND POH.SHIP_TO_LOCATION_ID = HRLS.LOCATION_ID(+)
AND POH.BILL_TO_LOCATION_ID = HRLB.LOCATION_ID(+)
AND POH.SOLDTO_LE_ID = LE.LEGAL_ENTITY_ID(+)
AND POH.TERMS_ID=TERM.TERM_ID(+)
AND TERM.LANGUAGE(+) = 'US'
AND POH.TYPE_LOOKUP_CODE='STANDARD'
ORDER BY POH.SEGMENT1