eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.PO_HEADERS_XML

Source

View Text - Preformatted


SELECT PH.TYPE_LOOKUP_CODE, PH.SEGMENT1, PH.REVISION_NUM, PH.PRINT_COUNT, TO_CHAR(PH.CREATION_DATE,'DD-MON-YYYY HH24:MI:SS') CREATION_DATE, PH.PRINTED_DATE, TO_CHAR(PH.REVISED_DATE,'DD-MON-YYYY HH24:MI:SS') REVISED_DATE, TO_CHAR(PH.START_DATE,'DD-MON-YYYY HH24:MI:SS') START_DATE, TO_CHAR(PH.END_DATE,'DD-MON-YYYY HH24:MI:SS') END_DATE, PH.NOTE_TO_VENDOR, HRE.FIRST_NAME DOCUMENT_BUYER_FIRST_NAME, HRE.LAST_NAME DOCUMENT_BUYER_LAST_NAME, HRE.TITLE DOCUMENT_BUYER_TITLE, PH.AGENT_ID DOCUMENT_BUYER_AGENT_ID, DECODE(NVL(PH.REVISION_NUM, 0),0, NULL, PO_COMMUNICATION_PVT.GETARCBUYERAGENTID(PH.PO_HEADER_ID)) ARCHIVE_BUYER_AGENT_ID, DECODE(NVL(PH.REVISION_NUM, 0),0, NULL, PO_COMMUNICATION_PVT.GETARCBUYERFNAME()) ARCHIVE_BUYER_FIRST_NAME, DECODE(NVL(PH.REVISION_NUM, 0),0, NULL, PO_COMMUNICATION_PVT.GETARCBUYERLNAME()) ARCHIVE_BUYER_LAST_NAME, DECODE(NVL(PH.REVISION_NUM, 0),0, NULL, PO_COMMUNICATION_PVT.GETARCBUYERTITLE()) ARCHIVE_BUYER_TITLE, TO_CHAR(NVL(PH.BLANKET_TOTAL_AMOUNT, ''),PO_COMMUNICATION_PVT.GETFORMATMASK) AMOUNT_AGREED, PH.CANCEL_FLAG, PH.CONFIRMING_ORDER_FLAG, nvl(PH.ACCEPTANCE_REQUIRED_FLAG,'N'), TO_CHAR(PH.ACCEPTANCE_DUE_DATE,'DD-MON-YYYY HH24:MI:SS') ACCEPTANCE_DUE_DATE, FCC.CURRENCY_CODE, FCC.NAME CURRENCY_NAME, TO_CHAR(PH.RATE, PO_COMMUNICATION_PVT.GETFORMATMASK) RATE, NVL(OFC.FREIGHT_CODE_TL, PH.SHIP_VIA_LOOKUP_CODE) SHIP_VIA, PLC1.MEANING FOB, PLC2.MEANING FREIGHT_TERMS, T.NAME PAYMENT_TERMS, NVL(PVS.CUSTOMER_NUM, VN.CUSTOMER_NUM) CUSTOMER_NUM, VN.SEGMENT1 VENDOR_NUM, VN.VENDOR_NAME, PVS.ADDRESS_LINE1 VENDOR_ADDRESS_LINE1, PVS.ADDRESS_LINE2 VENDOR_ADDRESS_LINE2, PVS.ADDRESS_LINE3 VENDOR_ADDRESS_LINE3, PVS.CITY VENDOR_CITY, DECODE(PVS.STATE, NULL, DECODE(PVS.PROVINCE, NULL, PVS.COUNTY, PVS.PROVINCE), PVS.STATE) VENDOR_STATE, PVS.ZIP VENDOR_POSTAL_CODE, FTE3.TERRITORY_SHORT_NAME VENDOR_COUNTRY, PVS.PHONE VENDOR_PHONE, PVC.FIRST_NAME VENDOR_CONTACT_FIRST_NAME, PVC.LAST_NAME VENDOR_CONTACT_LAST_NAME, PLC4.MEANING VENDOR_CONTACT_TITLE, PVC.PHONE VENDOR_CONTACT_PHONE, DECODE(NVL(PH.SHIP_TO_LOCATION_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.GETLOCATIONINFO(PH.SHIP_TO_LOCATION_ID)) SHIP_TO_LOCATION_ID, DECODE(NVL(PH.SHIP_TO_LOCATION_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.GETLOCATIONNAME()) SHIP_TO_LOCATION_NAME, DECODE(NVL(PH.SHIP_TO_LOCATION_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.GETADDRESSLINE1()) SHIP_TO_ADDRESS_LINE1, DECODE(NVL(PH.SHIP_TO_LOCATION_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.GETADDRESSLINE2()) SHIP_TO_ADDRESS_LINE2, DECODE(NVL(PH.SHIP_TO_LOCATION_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.GETADDRESSLINE3()) SHIP_TO_ADDRESS_LINE3, DECODE(NVL(PH.SHIP_TO_LOCATION_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.GETADDRESSLINE4()) SHIP_TO_ADDRESS_LINE4, DECODE(NVL(PH.SHIP_TO_LOCATION_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.GETADDRESSINFO()) SHIP_TO_ADDRESS_INFO, DECODE(NVL(PH.SHIP_TO_LOCATION_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.GETTERRITORYSHORTNAME() )SHIP_TO_COUNTRY, DECODE(NVL(PH.BILL_TO_LOCATION_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.GETLOCATIONINFO(PH.BILL_TO_LOCATION_ID)) BILL_TO_LOCATION_ID, DECODE(NVL(PH.SHIP_TO_LOCATION_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.GETLOCATIONNAME()) BILL_TO_LOCATION_NAME, DECODE(NVL(PH.BILL_TO_LOCATION_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.GETADDRESSLINE1() ) BILL_TO_ADDRESS_LINE1, DECODE(NVL(PH.BILL_TO_LOCATION_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.GETADDRESSLINE2() ) BILL_TO_ADDRESS_LINE2, DECODE(NVL(PH.BILL_TO_LOCATION_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.GETADDRESSLINE3() ) BILL_TO_ADDRESS_LINE3, DECODE(NVL(PH.BILL_TO_LOCATION_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.GETADDRESSLINE4() ) BILL_TO_ADDRESS_LINE4, DECODE(NVL(PH.BILL_TO_LOCATION_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.GETADDRESSINFO()) BILL_TO_ADDRESS_INFO, DECODE(NVL(PH.BILL_TO_LOCATION_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.GETTERRITORYSHORTNAME()) BILL_TO_COUNTRY, PH.ATTRIBUTE1, PH.ATTRIBUTE2, PH.ATTRIBUTE3, PH.ATTRIBUTE4, PH.ATTRIBUTE5, PH.ATTRIBUTE6, PH.ATTRIBUTE7, PH.ATTRIBUTE8, PH.ATTRIBUTE9, PH.ATTRIBUTE10, PH.ATTRIBUTE11, PH.ATTRIBUTE12, PH.ATTRIBUTE13, PH.ATTRIBUTE14, PH.ATTRIBUTE15, PH.VENDOR_SITE_ID, PH.PO_HEADER_ID, DECODE(PH.APPROVED_FLAG,'Y','Y','N') APPROVED_FLAG, PVS.LANGUAGE, PH.VENDOR_ID, PH.CLOSED_CODE, PH.USSGL_TRANSACTION_CODE, PH.GOVERNMENT_CONTEXT, PH.REQUEST_ID, PH.PROGRAM_APPLICATION_ID, PH.PROGRAM_ID, PH.PROGRAM_UPDATE_DATE, PH.ORG_ID, PH.COMMENTS, TO_CHAR(PH.REPLY_DATE,'DD-MON-YYYY HH24:MI:SS') REPLY_DATE, PH.REPLY_METHOD_LOOKUP_CODE, TO_CHAR(PH.RFQ_CLOSE_DATE,'DD-MON-YYYY HH24:MI:SS') RFQ_CLOSE_DATE, PH.QUOTE_TYPE_LOOKUP_CODE, PH.QUOTATION_CLASS_CODE, PH.QUOTE_WARNING_DELAY_UNIT, PH.QUOTE_WARNING_DELAY, PH.QUOTE_VENDOR_QUOTE_NUMBER, TO_CHAR(PH.CLOSED_DATE,'DD-MON-YYYY HH24:MI:SS') CLOSED_DATE, PH.USER_HOLD_FLAG, PH.APPROVAL_REQUIRED_FLAG, PH.FIRM_STATUS_LOOKUP_CODE, TO_CHAR(PH.FIRM_DATE,'DD-MON-YYYY HH24:MI:SS') FIRM_DATE, PH.FROZEN_FLAG, PH.EDI_PROCESSED_FLAG, PH.EDI_PROCESSED_STATUS, PH.ATTRIBUTE_CATEGORY, PH.CREATED_BY, PH.VENDOR_CONTACT_ID, PH.TERMS_ID, PH.FOB_LOOKUP_CODE, PH.FREIGHT_TERMS_LOOKUP_CODE, PH.STATUS_LOOKUP_CODE, PH.RATE_TYPE, TO_CHAR(PH.RATE_DATE,'DD-MON-YYYY HH24:MI:SS') RATE_DATE, PH.FROM_HEADER_ID, PH.FROM_TYPE_LOOKUP_CODE, NVL(PH.AUTHORIZATION_STATUS, 'N') AUTHORIZATION_STATUS, TO_CHAR(PH.APPROVED_DATE,'DD-MON-YYYY HH24:MI:SS') APPROVED_DATE, TO_CHAR(PH.AMOUNT_LIMIT, PO_COMMUNICATION_PVT.GETFORMATMASK) AMOUNT_LIMIT, TO_CHAR(PH.MIN_RELEASE_AMOUNT, PO_COMMUNICATION_PVT.GETFORMATMASK) MIN_RELEASE_AMOUNT, PH.NOTE_TO_AUTHORIZER, PH.NOTE_TO_RECEIVER, PH.VENDOR_ORDER_NUM, TO_CHAR(PH.LAST_UPDATE_DATE,'DD-MON-YYYY HH24:MI:SS') LAST_UPDATE_DATE, PH.LAST_UPDATED_BY, PH.SUMMARY_FLAG, PH.ENABLED_FLAG, PH.SEGMENT2, PH.SEGMENT3, PH.SEGMENT4, PH.SEGMENT5, TO_CHAR(PH.START_DATE_ACTIVE,'DD-MON-YYYY HH24:MI:SS') START_DATE_ACTIVE, TO_CHAR(PH.END_DATE_ACTIVE,'DD-MON-YYYY HH24:MI:SS') END_DATE_ACTIVE, PH.LAST_UPDATE_LOGIN, PH.SUPPLY_AGREEMENT_FLAG, PH.GLOBAL_ATTRIBUTE_CATEGORY, PH.GLOBAL_ATTRIBUTE1, PH.GLOBAL_ATTRIBUTE2, PH.GLOBAL_ATTRIBUTE3, PH.GLOBAL_ATTRIBUTE4, PH.GLOBAL_ATTRIBUTE5, PH.GLOBAL_ATTRIBUTE6, PH.GLOBAL_ATTRIBUTE7, PH.GLOBAL_ATTRIBUTE8, PH.GLOBAL_ATTRIBUTE9, PH.GLOBAL_ATTRIBUTE10, PH.GLOBAL_ATTRIBUTE11, PH.GLOBAL_ATTRIBUTE12, PH.GLOBAL_ATTRIBUTE13, PH.GLOBAL_ATTRIBUTE14, PH.GLOBAL_ATTRIBUTE15, PH.GLOBAL_ATTRIBUTE16, PH.GLOBAL_ATTRIBUTE17, PH.GLOBAL_ATTRIBUTE18, PH.GLOBAL_ATTRIBUTE19, PH.GLOBAL_ATTRIBUTE20, PH.INTERFACE_SOURCE_CODE, PH.REFERENCE_NUM, PH.WF_ITEM_TYPE, PH.WF_ITEM_KEY, PH.PCARD_ID, PH.PRICE_UPDATE_TOLERANCE, PH.MRC_RATE_TYPE, PH.MRC_RATE_DATE, PH.MRC_RATE, PH.PAY_ON_CODE, PH.XML_FLAG, TO_CHAR(PH.XML_SEND_DATE,'DD-MON-YYYY HH24:MI:SS') XML_SEND_DATE, TO_CHAR(PH.XML_CHANGE_SEND_DATE,'DD-MON-YYYY HH24:MI:SS') XML_CHANGE_SEND_DATE, PH.GLOBAL_AGREEMENT_FLAG, PH.CONSIGNED_CONSUMPTION_FLAG, TO_CHAR(PH.CBC_ACCOUNTING_DATE,'DD-MON-YYYY HH24:MI:SS') CBC_ACCOUNTING_DATE, PH.CONSUME_REQ_DEMAND_FLAG, PH.CHANGE_REQUESTED_BY, PLC3.MEANING SHIPPING_CONTROL, PH.CONTERMS_EXIST_FLAG, TO_CHAR(PH.CONTERMS_ARTICLES_UPD_DATE,'DD-MON-YYYY HH24:MI:SS') CONTERMS_ARTICLES_UPD_DATE, TO_CHAR(PH.CONTERMS_DELIV_UPD_DATE,'DD-MON-YYYY HH24:MI:SS') CONTERMS_DELIV_UPD_DATE, nvl(PH.PENDING_SIGNATURE_FLAG,'N'), DECODE(NVL(PH.ORG_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.GETOPERATIONINFO(PH.ORG_ID)) OU_NAME, DECODE(NVL(PH.ORG_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.GETOUADDRESSLINE1()) OU_ADDR1, DECODE(NVL(PH.ORG_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.GETOUADDRESSLINE2()) OU_ADDR2, DECODE(NVL(PH.ORG_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.GETOUADDRESSLINE3()) OU_ADDR3, DECODE(NVL(PH.ORG_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.GETOUTOWNCITY()) OU_TOWN_CITY, DECODE(NVL(PH.ORG_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.GETOUREGION2()) OU_REGION2, DECODE(NVL(PH.ORG_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.GETOUPOSTALCODE()) OU_POSTALCODE, DECODE(NVL(PH.ORG_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.getOUCountry()) OU_COUNTRY, PO_COMMUNICATION_PVT.GETLOCATIONINFO(PA.LOCATION_ID) BUYER_LOCATION_ID, PO_COMMUNICATION_PVT.GETADDRESSLINE1() BUYER_ADDRESS_LINE1, PO_COMMUNICATION_PVT.GETADDRESSLINE2() BUYER_ADDRESS_LINE2, PO_COMMUNICATION_PVT.GETADDRESSLINE3() BUYER_ADDRESS_LINE3, PO_COMMUNICATION_PVT.GETADDRESSLINE4() BUYER_ADDRESS_LINE4, PO_COMMUNICATION_PVT.GETADDRESSINFO() BUYER_CITY_STATE_ZIP, PO_COMMUNICATION_PVT.GETPHONE(PA.AGENT_ID) BUYER_CONTACT_PHONE, PO_COMMUNICATION_PVT.GETEMAIL() BUYER_CONTACT_EMAIL, PO_COMMUNICATION_PVT.GETFAX() BUYER_CONTACT_FAX, PVS.FAX VENDOR_FAX, TO_CHAR(DECODE(PH.TYPE_LOOKUP_CODE,'STANDARD',PO_CORE_S.GET_TOTAL('H',PH.PO_HEADER_ID), NULL), PO_COMMUNICATION_PVT.GETFORMATMASK) TOTAL_AMOUNT, PO_COMMUNICATION_PVT.GETTERRITORYSHORTNAME() BUYER_COUNTRY, PVS.ADDRESS_LINE4 VENDOR_ADDRESS_LINE4, PVS.AREA_CODE VENDOR_AREA_CODE, PVC.AREA_CODE VENDOR_CONTACT_AREA_CODE, DECODE(NVL(PH.ORG_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.getLegalEntityDetails(PH.ORG_ID)) LE_NAME, DECODE(NVL(PH.ORG_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.getLEAddressLine1()) LE_ADDR1, DECODE(NVL(PH.ORG_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.getLEAddressLine2()) LE_ADDR2, DECODE(NVL(PH.ORG_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.getLEAddressLine3()) LE_ADDR3, DECODE(NVL(PH.ORG_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.getLETownOrCity()) LE_TOWN_CITY, DECODE(NVL(PH.ORG_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.getLEStateOrProvince()) LE_STAE_PROVINCE, DECODE(NVL(PH.ORG_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.getLEPostalCode()) LE_POSTALCODE, DECODE(NVL(PH.ORG_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.getLECountry()) LE_COUNTRY, DECODE(PH.CANCEL_FLAG, 'Y' , PO_COMMUNICATION_PVT.getPOCancelDate(PH.PO_HEADER_ID), NULL) CANCEL_DATE, PH.CHANGE_SUMMARY, PH.DOCUMENT_CREATION_METHOD, PH.ENCUMBRANCE_REQUIRED_FLAG, PSL.DISPLAY_NAME STYLE_DISPLAY_NAME FROM FND_LOOKUP_VALUES PLC1, FND_LOOKUP_VALUES PLC2, FND_CURRENCIES_TL FCC, PO_VENDORS VN, PO_VENDOR_SITES_ALL PVS, PO_VENDOR_CONTACTS PVC, PER_ALL_PEOPLE_F HRE, AP_TERMS T, PO_HEADERS_ALL PH, FND_TERRITORIES_TL FTE3, ORG_FREIGHT_TL OFC, PO_AGENTS PA, FND_LOOKUP_VALUES PLC3, PO_DOC_STYLE_LINES_TL PSL, FND_LOOKUP_VALUES PLC4 WHERE VN.VENDOR_ID(+) = PH.VENDOR_ID AND PVS.VENDOR_SITE_ID(+) = PH.VENDOR_SITE_ID AND PH.VENDOR_CONTACT_ID = PVC.VENDOR_CONTACT_ID(+) AND HRE.PERSON_ID = PH.AGENT_ID AND TRUNC(SYSDATE) BETWEEN HRE.EFFECTIVE_START_DATE AND HRE.EFFECTIVE_END_DATE AND PH.TERMS_ID = T.TERM_ID (+) AND PH.TYPE_LOOKUP_CODE IN ('STANDARD','BLANKET','CONTRACT') AND FCC.CURRENCY_CODE = PH.CURRENCY_CODE AND PLC1.LOOKUP_CODE (+) = PH.FOB_LOOKUP_CODE AND PLC1.LOOKUP_TYPE (+) = 'FOB' AND PLC1.LANGUAGE(+) = USERENV('LANG') AND PLC1.VIEW_APPLICATION_ID(+) = 201 AND DECODE(PLC1.LOOKUP_CODE, NULL, 1, PLC1.SECURITY_GROUP_ID) = DECODE(PLC1.LOOKUP_CODE, NULL, 1, FND_GLOBAL.LOOKUP_SECURITY_GROUP(PLC1.LOOKUP_TYPE, PLC1.VIEW_APPLICATION_ID) ) AND PLC2.LOOKUP_CODE (+) = PH.FREIGHT_TERMS_LOOKUP_CODE AND PLC2.LOOKUP_TYPE (+) = 'FREIGHT TERMS' AND PLC2.LANGUAGE(+) = USERENV('LANG') AND PLC2.VIEW_APPLICATION_ID(+) = 201 AND DECODE(PLC2.LOOKUP_CODE, NULL, 1,PLC2.SECURITY_GROUP_ID) = DECODE(PLC2.LOOKUP_CODE, NULL, 1,FND_GLOBAL.LOOKUP_SECURITY_GROUP(PLC2.LOOKUP_TYPE, PLC2.VIEW_APPLICATION_ID)) AND PVS.COUNTRY = FTE3.TERRITORY_CODE (+) AND DECODE(FTE3.TERRITORY_CODE, NULL, '1', FTE3.LANGUAGE) = DECODE(FTE3.TERRITORY_CODE, NULL, '1', USERENV('LANG')) AND OFC.FREIGHT_CODE (+) = PH.SHIP_VIA_LOOKUP_CODE AND OFC.ORGANIZATION_ID (+) = PH.ORG_ID AND PA.AGENT_ID = PH.AGENT_ID AND PLC3.LOOKUP_CODE (+) = PH.SHIPPING_CONTROL AND PLC3.LOOKUP_TYPE (+) = 'SHIPPING CONTROL' AND PLC3.LANGUAGE(+) = USERENV('LANG') AND PLC3.VIEW_APPLICATION_ID(+) = 201 AND DECODE(PLC3.LOOKUP_CODE, NULL, 1, PLC3.SECURITY_GROUP_ID) = DECODE(PLC3.LOOKUP_CODE, NULL, 1, FND_GLOBAL.LOOKUP_SECURITY_GROUP(PLC3.LOOKUP_TYPE, PLC3.VIEW_APPLICATION_ID)) AND FCC.LANGUAGE = USERENV('LANG') AND OFC.LANGUAGE(+) = USERENV('LANG') AND PH.STYLE_ID = PSL.STYLE_ID(+) AND PSL.LANGUAGE(+) = USERENV('LANG') AND PSL.DOCUMENT_SUBTYPE(+) = PH.TYPE_LOOKUP_CODE AND PLC4.LOOKUP_CODE (+) = PVC.PREFIX AND PLC4.LOOKUP_TYPE (+) = 'CONTACT_TITLE' AND PLC4.LANGUAGE(+) = USERENV('LANG') AND PLC4.VIEW_APPLICATION_ID(+) = 222 AND DECODE(PLC4.LOOKUP_CODE, NULL, 1, PLC4.SECURITY_GROUP_ID) = DECODE(PLC4.LOOKUP_CODE, NULL, 1, FND_GLOBAL.LOOKUP_SECURITY_GROUP(PLC4.LOOKUP_TYPE, PLC4.VIEW_APPLICATION_ID))

View Text - HTML Formatted

SELECT PH.TYPE_LOOKUP_CODE
, PH.SEGMENT1
, PH.REVISION_NUM
, PH.PRINT_COUNT
, TO_CHAR(PH.CREATION_DATE
, 'DD-MON-YYYY HH24:MI:SS') CREATION_DATE
, PH.PRINTED_DATE
, TO_CHAR(PH.REVISED_DATE
, 'DD-MON-YYYY HH24:MI:SS') REVISED_DATE
, TO_CHAR(PH.START_DATE
, 'DD-MON-YYYY HH24:MI:SS') START_DATE
, TO_CHAR(PH.END_DATE
, 'DD-MON-YYYY HH24:MI:SS') END_DATE
, PH.NOTE_TO_VENDOR
, HRE.FIRST_NAME DOCUMENT_BUYER_FIRST_NAME
, HRE.LAST_NAME DOCUMENT_BUYER_LAST_NAME
, HRE.TITLE DOCUMENT_BUYER_TITLE
, PH.AGENT_ID DOCUMENT_BUYER_AGENT_ID
, DECODE(NVL(PH.REVISION_NUM
, 0)
, 0
, NULL
, PO_COMMUNICATION_PVT.GETARCBUYERAGENTID(PH.PO_HEADER_ID)) ARCHIVE_BUYER_AGENT_ID
, DECODE(NVL(PH.REVISION_NUM
, 0)
, 0
, NULL
, PO_COMMUNICATION_PVT.GETARCBUYERFNAME()) ARCHIVE_BUYER_FIRST_NAME
, DECODE(NVL(PH.REVISION_NUM
, 0)
, 0
, NULL
, PO_COMMUNICATION_PVT.GETARCBUYERLNAME()) ARCHIVE_BUYER_LAST_NAME
, DECODE(NVL(PH.REVISION_NUM
, 0)
, 0
, NULL
, PO_COMMUNICATION_PVT.GETARCBUYERTITLE()) ARCHIVE_BUYER_TITLE
, TO_CHAR(NVL(PH.BLANKET_TOTAL_AMOUNT
, '')
, PO_COMMUNICATION_PVT.GETFORMATMASK) AMOUNT_AGREED
, PH.CANCEL_FLAG
, PH.CONFIRMING_ORDER_FLAG
, NVL(PH.ACCEPTANCE_REQUIRED_FLAG
, 'N')
, TO_CHAR(PH.ACCEPTANCE_DUE_DATE
, 'DD-MON-YYYY HH24:MI:SS') ACCEPTANCE_DUE_DATE
, FCC.CURRENCY_CODE
, FCC.NAME CURRENCY_NAME
, TO_CHAR(PH.RATE
, PO_COMMUNICATION_PVT.GETFORMATMASK) RATE
, NVL(OFC.FREIGHT_CODE_TL
, PH.SHIP_VIA_LOOKUP_CODE) SHIP_VIA
, PLC1.MEANING FOB
, PLC2.MEANING FREIGHT_TERMS
, T.NAME PAYMENT_TERMS
, NVL(PVS.CUSTOMER_NUM
, VN.CUSTOMER_NUM) CUSTOMER_NUM
, VN.SEGMENT1 VENDOR_NUM
, VN.VENDOR_NAME
, PVS.ADDRESS_LINE1 VENDOR_ADDRESS_LINE1
, PVS.ADDRESS_LINE2 VENDOR_ADDRESS_LINE2
, PVS.ADDRESS_LINE3 VENDOR_ADDRESS_LINE3
, PVS.CITY VENDOR_CITY
, DECODE(PVS.STATE
, NULL
, DECODE(PVS.PROVINCE
, NULL
, PVS.COUNTY
, PVS.PROVINCE)
, PVS.STATE) VENDOR_STATE
, PVS.ZIP VENDOR_POSTAL_CODE
, FTE3.TERRITORY_SHORT_NAME VENDOR_COUNTRY
, PVS.PHONE VENDOR_PHONE
, PVC.FIRST_NAME VENDOR_CONTACT_FIRST_NAME
, PVC.LAST_NAME VENDOR_CONTACT_LAST_NAME
, PLC4.MEANING VENDOR_CONTACT_TITLE
, PVC.PHONE VENDOR_CONTACT_PHONE
, DECODE(NVL(PH.SHIP_TO_LOCATION_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETLOCATIONINFO(PH.SHIP_TO_LOCATION_ID)) SHIP_TO_LOCATION_ID
, DECODE(NVL(PH.SHIP_TO_LOCATION_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETLOCATIONNAME()) SHIP_TO_LOCATION_NAME
, DECODE(NVL(PH.SHIP_TO_LOCATION_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETADDRESSLINE1()) SHIP_TO_ADDRESS_LINE1
, DECODE(NVL(PH.SHIP_TO_LOCATION_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETADDRESSLINE2()) SHIP_TO_ADDRESS_LINE2
, DECODE(NVL(PH.SHIP_TO_LOCATION_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETADDRESSLINE3()) SHIP_TO_ADDRESS_LINE3
, DECODE(NVL(PH.SHIP_TO_LOCATION_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETADDRESSLINE4()) SHIP_TO_ADDRESS_LINE4
, DECODE(NVL(PH.SHIP_TO_LOCATION_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETADDRESSINFO()) SHIP_TO_ADDRESS_INFO
, DECODE(NVL(PH.SHIP_TO_LOCATION_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETTERRITORYSHORTNAME() )SHIP_TO_COUNTRY
, DECODE(NVL(PH.BILL_TO_LOCATION_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETLOCATIONINFO(PH.BILL_TO_LOCATION_ID)) BILL_TO_LOCATION_ID
, DECODE(NVL(PH.SHIP_TO_LOCATION_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETLOCATIONNAME()) BILL_TO_LOCATION_NAME
, DECODE(NVL(PH.BILL_TO_LOCATION_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETADDRESSLINE1() ) BILL_TO_ADDRESS_LINE1
, DECODE(NVL(PH.BILL_TO_LOCATION_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETADDRESSLINE2() ) BILL_TO_ADDRESS_LINE2
, DECODE(NVL(PH.BILL_TO_LOCATION_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETADDRESSLINE3() ) BILL_TO_ADDRESS_LINE3
, DECODE(NVL(PH.BILL_TO_LOCATION_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETADDRESSLINE4() ) BILL_TO_ADDRESS_LINE4
, DECODE(NVL(PH.BILL_TO_LOCATION_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETADDRESSINFO()) BILL_TO_ADDRESS_INFO
, DECODE(NVL(PH.BILL_TO_LOCATION_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETTERRITORYSHORTNAME()) BILL_TO_COUNTRY
, PH.ATTRIBUTE1
, PH.ATTRIBUTE2
, PH.ATTRIBUTE3
, PH.ATTRIBUTE4
, PH.ATTRIBUTE5
, PH.ATTRIBUTE6
, PH.ATTRIBUTE7
, PH.ATTRIBUTE8
, PH.ATTRIBUTE9
, PH.ATTRIBUTE10
, PH.ATTRIBUTE11
, PH.ATTRIBUTE12
, PH.ATTRIBUTE13
, PH.ATTRIBUTE14
, PH.ATTRIBUTE15
, PH.VENDOR_SITE_ID
, PH.PO_HEADER_ID
, DECODE(PH.APPROVED_FLAG
, 'Y'
, 'Y'
, 'N') APPROVED_FLAG
, PVS.LANGUAGE
, PH.VENDOR_ID
, PH.CLOSED_CODE
, PH.USSGL_TRANSACTION_CODE
, PH.GOVERNMENT_CONTEXT
, PH.REQUEST_ID
, PH.PROGRAM_APPLICATION_ID
, PH.PROGRAM_ID
, PH.PROGRAM_UPDATE_DATE
, PH.ORG_ID
, PH.COMMENTS
, TO_CHAR(PH.REPLY_DATE
, 'DD-MON-YYYY HH24:MI:SS') REPLY_DATE
, PH.REPLY_METHOD_LOOKUP_CODE
, TO_CHAR(PH.RFQ_CLOSE_DATE
, 'DD-MON-YYYY HH24:MI:SS') RFQ_CLOSE_DATE
, PH.QUOTE_TYPE_LOOKUP_CODE
, PH.QUOTATION_CLASS_CODE
, PH.QUOTE_WARNING_DELAY_UNIT
, PH.QUOTE_WARNING_DELAY
, PH.QUOTE_VENDOR_QUOTE_NUMBER
, TO_CHAR(PH.CLOSED_DATE
, 'DD-MON-YYYY HH24:MI:SS') CLOSED_DATE
, PH.USER_HOLD_FLAG
, PH.APPROVAL_REQUIRED_FLAG
, PH.FIRM_STATUS_LOOKUP_CODE
, TO_CHAR(PH.FIRM_DATE
, 'DD-MON-YYYY HH24:MI:SS') FIRM_DATE
, PH.FROZEN_FLAG
, PH.EDI_PROCESSED_FLAG
, PH.EDI_PROCESSED_STATUS
, PH.ATTRIBUTE_CATEGORY
, PH.CREATED_BY
, PH.VENDOR_CONTACT_ID
, PH.TERMS_ID
, PH.FOB_LOOKUP_CODE
, PH.FREIGHT_TERMS_LOOKUP_CODE
, PH.STATUS_LOOKUP_CODE
, PH.RATE_TYPE
, TO_CHAR(PH.RATE_DATE
, 'DD-MON-YYYY HH24:MI:SS') RATE_DATE
, PH.FROM_HEADER_ID
, PH.FROM_TYPE_LOOKUP_CODE
, NVL(PH.AUTHORIZATION_STATUS
, 'N') AUTHORIZATION_STATUS
, TO_CHAR(PH.APPROVED_DATE
, 'DD-MON-YYYY HH24:MI:SS') APPROVED_DATE
, TO_CHAR(PH.AMOUNT_LIMIT
, PO_COMMUNICATION_PVT.GETFORMATMASK) AMOUNT_LIMIT
, TO_CHAR(PH.MIN_RELEASE_AMOUNT
, PO_COMMUNICATION_PVT.GETFORMATMASK) MIN_RELEASE_AMOUNT
, PH.NOTE_TO_AUTHORIZER
, PH.NOTE_TO_RECEIVER
, PH.VENDOR_ORDER_NUM
, TO_CHAR(PH.LAST_UPDATE_DATE
, 'DD-MON-YYYY HH24:MI:SS') LAST_UPDATE_DATE
, PH.LAST_UPDATED_BY
, PH.SUMMARY_FLAG
, PH.ENABLED_FLAG
, PH.SEGMENT2
, PH.SEGMENT3
, PH.SEGMENT4
, PH.SEGMENT5
, TO_CHAR(PH.START_DATE_ACTIVE
, 'DD-MON-YYYY HH24:MI:SS') START_DATE_ACTIVE
, TO_CHAR(PH.END_DATE_ACTIVE
, 'DD-MON-YYYY HH24:MI:SS') END_DATE_ACTIVE
, PH.LAST_UPDATE_LOGIN
, PH.SUPPLY_AGREEMENT_FLAG
, PH.GLOBAL_ATTRIBUTE_CATEGORY
, PH.GLOBAL_ATTRIBUTE1
, PH.GLOBAL_ATTRIBUTE2
, PH.GLOBAL_ATTRIBUTE3
, PH.GLOBAL_ATTRIBUTE4
, PH.GLOBAL_ATTRIBUTE5
, PH.GLOBAL_ATTRIBUTE6
, PH.GLOBAL_ATTRIBUTE7
, PH.GLOBAL_ATTRIBUTE8
, PH.GLOBAL_ATTRIBUTE9
, PH.GLOBAL_ATTRIBUTE10
, PH.GLOBAL_ATTRIBUTE11
, PH.GLOBAL_ATTRIBUTE12
, PH.GLOBAL_ATTRIBUTE13
, PH.GLOBAL_ATTRIBUTE14
, PH.GLOBAL_ATTRIBUTE15
, PH.GLOBAL_ATTRIBUTE16
, PH.GLOBAL_ATTRIBUTE17
, PH.GLOBAL_ATTRIBUTE18
, PH.GLOBAL_ATTRIBUTE19
, PH.GLOBAL_ATTRIBUTE20
, PH.INTERFACE_SOURCE_CODE
, PH.REFERENCE_NUM
, PH.WF_ITEM_TYPE
, PH.WF_ITEM_KEY
, PH.PCARD_ID
, PH.PRICE_UPDATE_TOLERANCE
, PH.MRC_RATE_TYPE
, PH.MRC_RATE_DATE
, PH.MRC_RATE
, PH.PAY_ON_CODE
, PH.XML_FLAG
, TO_CHAR(PH.XML_SEND_DATE
, 'DD-MON-YYYY HH24:MI:SS') XML_SEND_DATE
, TO_CHAR(PH.XML_CHANGE_SEND_DATE
, 'DD-MON-YYYY HH24:MI:SS') XML_CHANGE_SEND_DATE
, PH.GLOBAL_AGREEMENT_FLAG
, PH.CONSIGNED_CONSUMPTION_FLAG
, TO_CHAR(PH.CBC_ACCOUNTING_DATE
, 'DD-MON-YYYY HH24:MI:SS') CBC_ACCOUNTING_DATE
, PH.CONSUME_REQ_DEMAND_FLAG
, PH.CHANGE_REQUESTED_BY
, PLC3.MEANING SHIPPING_CONTROL
, PH.CONTERMS_EXIST_FLAG
, TO_CHAR(PH.CONTERMS_ARTICLES_UPD_DATE
, 'DD-MON-YYYY HH24:MI:SS') CONTERMS_ARTICLES_UPD_DATE
, TO_CHAR(PH.CONTERMS_DELIV_UPD_DATE
, 'DD-MON-YYYY HH24:MI:SS') CONTERMS_DELIV_UPD_DATE
, NVL(PH.PENDING_SIGNATURE_FLAG
, 'N')
, DECODE(NVL(PH.ORG_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETOPERATIONINFO(PH.ORG_ID)) OU_NAME
, DECODE(NVL(PH.ORG_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETOUADDRESSLINE1()) OU_ADDR1
, DECODE(NVL(PH.ORG_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETOUADDRESSLINE2()) OU_ADDR2
, DECODE(NVL(PH.ORG_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETOUADDRESSLINE3()) OU_ADDR3
, DECODE(NVL(PH.ORG_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETOUTOWNCITY()) OU_TOWN_CITY
, DECODE(NVL(PH.ORG_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETOUREGION2()) OU_REGION2
, DECODE(NVL(PH.ORG_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETOUPOSTALCODE()) OU_POSTALCODE
, DECODE(NVL(PH.ORG_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETOUCOUNTRY()) OU_COUNTRY
, PO_COMMUNICATION_PVT.GETLOCATIONINFO(PA.LOCATION_ID) BUYER_LOCATION_ID
, PO_COMMUNICATION_PVT.GETADDRESSLINE1() BUYER_ADDRESS_LINE1
, PO_COMMUNICATION_PVT.GETADDRESSLINE2() BUYER_ADDRESS_LINE2
, PO_COMMUNICATION_PVT.GETADDRESSLINE3() BUYER_ADDRESS_LINE3
, PO_COMMUNICATION_PVT.GETADDRESSLINE4() BUYER_ADDRESS_LINE4
, PO_COMMUNICATION_PVT.GETADDRESSINFO() BUYER_CITY_STATE_ZIP
, PO_COMMUNICATION_PVT.GETPHONE(PA.AGENT_ID) BUYER_CONTACT_PHONE
, PO_COMMUNICATION_PVT.GETEMAIL() BUYER_CONTACT_EMAIL
, PO_COMMUNICATION_PVT.GETFAX() BUYER_CONTACT_FAX
, PVS.FAX VENDOR_FAX
, TO_CHAR(DECODE(PH.TYPE_LOOKUP_CODE
, 'STANDARD'
, PO_CORE_S.GET_TOTAL('H'
, PH.PO_HEADER_ID)
, NULL)
, PO_COMMUNICATION_PVT.GETFORMATMASK) TOTAL_AMOUNT
, PO_COMMUNICATION_PVT.GETTERRITORYSHORTNAME() BUYER_COUNTRY
, PVS.ADDRESS_LINE4 VENDOR_ADDRESS_LINE4
, PVS.AREA_CODE VENDOR_AREA_CODE
, PVC.AREA_CODE VENDOR_CONTACT_AREA_CODE
, DECODE(NVL(PH.ORG_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETLEGALENTITYDETAILS(PH.ORG_ID)) LE_NAME
, DECODE(NVL(PH.ORG_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETLEADDRESSLINE1()) LE_ADDR1
, DECODE(NVL(PH.ORG_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETLEADDRESSLINE2()) LE_ADDR2
, DECODE(NVL(PH.ORG_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETLEADDRESSLINE3()) LE_ADDR3
, DECODE(NVL(PH.ORG_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETLETOWNORCITY()) LE_TOWN_CITY
, DECODE(NVL(PH.ORG_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETLESTATEORPROVINCE()) LE_STAE_PROVINCE
, DECODE(NVL(PH.ORG_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETLEPOSTALCODE()) LE_POSTALCODE
, DECODE(NVL(PH.ORG_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETLECOUNTRY()) LE_COUNTRY
, DECODE(PH.CANCEL_FLAG
, 'Y'
, PO_COMMUNICATION_PVT.GETPOCANCELDATE(PH.PO_HEADER_ID)
, NULL) CANCEL_DATE
, PH.CHANGE_SUMMARY
, PH.DOCUMENT_CREATION_METHOD
, PH.ENCUMBRANCE_REQUIRED_FLAG
, PSL.DISPLAY_NAME STYLE_DISPLAY_NAME
FROM FND_LOOKUP_VALUES PLC1
, FND_LOOKUP_VALUES PLC2
, FND_CURRENCIES_TL FCC
, PO_VENDORS VN
, PO_VENDOR_SITES_ALL PVS
, PO_VENDOR_CONTACTS PVC
, PER_ALL_PEOPLE_F HRE
, AP_TERMS T
, PO_HEADERS_ALL PH
, FND_TERRITORIES_TL FTE3
, ORG_FREIGHT_TL OFC
, PO_AGENTS PA
, FND_LOOKUP_VALUES PLC3
, PO_DOC_STYLE_LINES_TL PSL
, FND_LOOKUP_VALUES PLC4
WHERE VN.VENDOR_ID(+) = PH.VENDOR_ID
AND PVS.VENDOR_SITE_ID(+) = PH.VENDOR_SITE_ID
AND PH.VENDOR_CONTACT_ID = PVC.VENDOR_CONTACT_ID(+)
AND HRE.PERSON_ID = PH.AGENT_ID
AND TRUNC(SYSDATE) BETWEEN HRE.EFFECTIVE_START_DATE
AND HRE.EFFECTIVE_END_DATE
AND PH.TERMS_ID = T.TERM_ID (+)
AND PH.TYPE_LOOKUP_CODE IN ('STANDARD'
, 'BLANKET'
, 'CONTRACT')
AND FCC.CURRENCY_CODE = PH.CURRENCY_CODE
AND PLC1.LOOKUP_CODE (+) = PH.FOB_LOOKUP_CODE
AND PLC1.LOOKUP_TYPE (+) = 'FOB'
AND PLC1.LANGUAGE(+) = USERENV('LANG')
AND PLC1.VIEW_APPLICATION_ID(+) = 201
AND DECODE(PLC1.LOOKUP_CODE
, NULL
, 1
, PLC1.SECURITY_GROUP_ID) = DECODE(PLC1.LOOKUP_CODE
, NULL
, 1
, FND_GLOBAL.LOOKUP_SECURITY_GROUP(PLC1.LOOKUP_TYPE
, PLC1.VIEW_APPLICATION_ID) )
AND PLC2.LOOKUP_CODE (+) = PH.FREIGHT_TERMS_LOOKUP_CODE
AND PLC2.LOOKUP_TYPE (+) = 'FREIGHT TERMS'
AND PLC2.LANGUAGE(+) = USERENV('LANG')
AND PLC2.VIEW_APPLICATION_ID(+) = 201
AND DECODE(PLC2.LOOKUP_CODE
, NULL
, 1
, PLC2.SECURITY_GROUP_ID) = DECODE(PLC2.LOOKUP_CODE
, NULL
, 1
, FND_GLOBAL.LOOKUP_SECURITY_GROUP(PLC2.LOOKUP_TYPE
, PLC2.VIEW_APPLICATION_ID))
AND PVS.COUNTRY = FTE3.TERRITORY_CODE (+)
AND DECODE(FTE3.TERRITORY_CODE
, NULL
, '1'
, FTE3.LANGUAGE) = DECODE(FTE3.TERRITORY_CODE
, NULL
, '1'
, USERENV('LANG'))
AND OFC.FREIGHT_CODE (+) = PH.SHIP_VIA_LOOKUP_CODE
AND OFC.ORGANIZATION_ID (+) = PH.ORG_ID
AND PA.AGENT_ID = PH.AGENT_ID
AND PLC3.LOOKUP_CODE (+) = PH.SHIPPING_CONTROL
AND PLC3.LOOKUP_TYPE (+) = 'SHIPPING CONTROL'
AND PLC3.LANGUAGE(+) = USERENV('LANG')
AND PLC3.VIEW_APPLICATION_ID(+) = 201
AND DECODE(PLC3.LOOKUP_CODE
, NULL
, 1
, PLC3.SECURITY_GROUP_ID) = DECODE(PLC3.LOOKUP_CODE
, NULL
, 1
, FND_GLOBAL.LOOKUP_SECURITY_GROUP(PLC3.LOOKUP_TYPE
, PLC3.VIEW_APPLICATION_ID))
AND FCC.LANGUAGE = USERENV('LANG')
AND OFC.LANGUAGE(+) = USERENV('LANG')
AND PH.STYLE_ID = PSL.STYLE_ID(+)
AND PSL.LANGUAGE(+) = USERENV('LANG')
AND PSL.DOCUMENT_SUBTYPE(+) = PH.TYPE_LOOKUP_CODE
AND PLC4.LOOKUP_CODE (+) = PVC.PREFIX
AND PLC4.LOOKUP_TYPE (+) = 'CONTACT_TITLE'
AND PLC4.LANGUAGE(+) = USERENV('LANG')
AND PLC4.VIEW_APPLICATION_ID(+) = 222
AND DECODE(PLC4.LOOKUP_CODE
, NULL
, 1
, PLC4.SECURITY_GROUP_ID) = DECODE(PLC4.LOOKUP_CODE
, NULL
, 1
, FND_GLOBAL.LOOKUP_SECURITY_GROUP(PLC4.LOOKUP_TYPE
, PLC4.VIEW_APPLICATION_ID))