FND Design Data [Home] [Help]

View: POS_PO_SUMMARY_V

Product: PO - Purchasing
Description:
Implementation/DBA Data: ViewAPPS.POS_PO_SUMMARY_V
View Text

SELECT 'PO' PO_RELEASE_FLAG
, POH.ACCEPTANCE_DUE_DATE
, POH.APPROVED_DATE
, NVL(POH.AUTHORIZATION_STATUS
, 'INCOMPLETE') AUTHORIZATION_STATUS
, POH.CLOSED_DATE
, POH.COMMENTS
, POH.FIRM_DATE
, POH.NOTE_TO_AUTHORIZER
, POH.NOTE_TO_RECEIVER
, POH.NOTE_TO_VENDOR
, POH.PRINT_COUNT
, POH.PRINTED_DATE
, POH.RATE
, POH.RATE_DATE
, POH.RATE_TYPE
, POH.REVISED_DATE
, POH.REVISION_NUM
, POH.AGENT_ID
, POH.BILL_TO_LOCATION_ID
, POH.FROM_HEADER_ID
, POH.PO_HEADER_ID
, POH.SHIP_TO_LOCATION_ID
, POH.TERMS_ID
, POH.VENDOR_CONTACT_ID
, POH.VENDOR_ID
, POH.VENDOR_SITE_ID
, NVL(POH.CLOSED_CODE
, 'OPEN') CLOSED_CODE
, POH.CURRENCY_CODE
, NVL(POH.FIRM_STATUS_LOOKUP_CODE
, 'N') FIRM_STATUS_LOOKUP_CODE
, POH.FOB_LOOKUP_CODE
, POH.FREIGHT_TERMS_LOOKUP_CODE
, POH.SHIP_VIA_LOOKUP_CODE
, POH.TYPE_LOOKUP_CODE
, POH.ACCEPTANCE_REQUIRED_FLAG
, POH.APPROVAL_REQUIRED_FLAG
, POH.APPROVED_FLAG
, DECODE (POH.CANCEL_FLAG
, 'I'
, NULL
, POH.CANCEL_FLAG) CANCEL_FLAG
, POH.CONFIRMING_ORDER_FLAG
, POH.ENABLED_FLAG
, NVL(POH.FROZEN_FLAG
, 'N') FROZEN_FLAG
, POH.SUMMARY_FLAG
, NVL(POH.USER_HOLD_FLAG
, 'N') USER_HOLD_FLAG
, POH.CREATED_BY
, POH.CREATION_DATE
, POH.LAST_UPDATED_BY
, POH.LAST_UPDATE_DATE
, POH.LAST_UPDATE_LOGIN
, POH.PROGRAM_APPLICATION_ID
, POH.PROGRAM_ID
, POH.PROGRAM_UPDATE_DATE
, POH.REQUEST_ID
, POH.SEGMENT1 PO_NUM
, PO_INQ_SV.GET_PERSON_NAME(POH.AGENT_ID) AGENT_NAME
, V.VENDOR_NAME
, VS.VENDOR_SITE_CODE
, VS.ADDRESS_LINE1
, VS.ADDRESS_LINE2
, VS.ADDRESS_LINE3
, VS.CITY
, VS.STATE
, VS.ZIP
, VS.COUNTRY
, DECODE (VS.PHONE
, NULL
, NULL
, '('||VS.AREA_CODE||') '||VS.PHONE) PHONE
, DECODE (VS.FAX
, NULL
, NULL
, '('||VS.FAX_AREA_CODE||') '||VS.FAX) FAX
, AT.NAME
, PDT.TYPE_NAME TYPE_NAME
, HRL1.LOCATION_CODE SHIP_TO_LOCATION
, HRL1.ADDRESS_LINE_1 SHIP_ADDR1
, HRL1.ADDRESS_LINE_2 SHIP_ADDR2
, HRL1.ADDRESS_LINE_3 SHIP_ADDR3
, HRL1.TOWN_OR_CITY SHIP_CITY
, HRL1.REGION_2 SHIP_STATE
, HRL1.POSTAL_CODE SHIP_ZIP
, HRL1.COUNTRY SHIP_COUNTRY
, HRL2.LOCATION_CODE BILL_TO_LOCATION
, HRL2.ADDRESS_LINE_1 BILL_ADDR1
, HRL2.ADDRESS_LINE_2 BILL_ADDR2
, HRL2.ADDRESS_LINE_3 BILL_ADDR3
, HRL2.TOWN_OR_CITY BILL_CITY
, HRL2.REGION_2 BILL_STATE
, HRL2.POSTAL_CODE BILL_ZIP
, HRL2.COUNTRY BILL_COUNTRY
, POLC2.MEANING FOB_DSP
, POLC3.MEANING FREIGHT_TERMS_DSP
, TO_DATE(NULL) CANCEL_DATE
, NULL CANCEL_REASON
, TO_NUMBER(NULL) CANCELLED_BY
, NULL CANCELLED_BY_NAME
, TO_NUMBER(NULL) HOLD_BY
, TO_DATE(NULL) HOLD_DATE
, NULL HOLD_REASON
, TO_NUMBER(NULL) RELEASE_NUM
, NULL RELEASE_TYPE
, TO_NUMBER(NULL) PO_RELEASE_ID
, DECODE(POH.TYPE_LOOKUP_CODE
, 'BLANKET'
, TO_CHAR(POH.BLANKET_TOTAL_AMOUNT
, FND_CURRENCY.SAFE_GET_FORMAT_MASK(POH.CURRENCY_CODE
, 30))
, TO_CHAR(PO_TOTALS_PO_SV.GET_PO_TOTAL(POH.PO_HEADER_ID)
, FND_CURRENCY.SAFE_GET_FORMAT_MASK(POH.CURRENCY_CODE
, 30))) AMOUNT
, V.ATTRIBUTE14 SUPPLIER_URL
, POA.ACCEPTED_FLAG ACCEPTANCE_FLAG
, POA.ACTION_DATE ACTION_DATE
, POH.USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE
, POH.ORG_ID
, HOU.NAME ORG_NAME
FROM PO_VENDOR_SITES_ALL VS
, PO_VENDORS V
, PO_ACCEPTANCES POA
, HR_LOCATIONS_ALL HRL1
, HR_LOCATIONS_ALL HRL2
, FND_LOOKUP_VALUES_VL POLC2
, FND_LOOKUP_VALUES_VL POLC3
, AP_TERMS AT
, PO_DOCUMENT_TYPES_ALL_TL PDT
, PO_HEADERS_ALL POH
, HR_ALL_ORGANIZATION_UNITS_TL HOU
WHERE POH.TYPE_LOOKUP_CODE IN ('BLANKET'
, 'CONTRACT'
, 'PLANNED'
, 'STANDARD')
AND PDT.DOCUMENT_SUBTYPE = POH.TYPE_LOOKUP_CODE
AND PDT.ORG_ID = POH.ORG_ID
AND PDT.LANGUAGE = USERENV('LANG')
AND PDT.DOCUMENT_TYPE_CODE IN ('PO'
, 'PA')
AND POH.VENDOR_ID = V.VENDOR_ID
AND VS.VENDOR_SITE_ID = POH.VENDOR_SITE_ID
AND POH.APPROVED_FLAG = 'Y'
AND POH.AUTHORIZATION_STATUS NOT IN ('IN PROCESS'
, 'INCOMPLETE'
, 'REQUIRES REAPPROVAL')
AND HRL1.LOCATION_ID = POH.SHIP_TO_LOCATION_ID
AND HRL2.LOCATION_ID = POH.BILL_TO_LOCATION_ID
AND POLC2.LOOKUP_CODE = POH.FOB_LOOKUP_CODE
AND POLC2.LOOKUP_TYPE = 'FOB'
AND POLC2.VIEW_APPLICATION_ID = 201
AND POLC3.LOOKUP_CODE = POH.FREIGHT_TERMS_LOOKUP_CODE
AND POLC3.LOOKUP_TYPE = 'FREIGHT TERMS'
AND POLC3.VIEW_APPLICATION_ID = 201
AND AT.TERM_ID (+) = POH.TERMS_ID
AND POH.PO_HEADER_ID = POA.PO_HEADER_ID (+)
AND POH.REVISION_NUM = POA.REVISION_NUM (+)
AND ( POA.CREATION_DATE = (SELECT MAX(CREATION_DATE)
FROM PO_ACCEPTANCES PA2
WHERE POH.PO_HEADER_ID = PA2.PO_HEADER_ID) OR POA.CREATION_DATE IS NULL)
AND HOU.ORGANIZATION_ID = POH.ORG_ID
AND HOU.LANGUAGE = USERENV('LANG') UNION SELECT 'RELEASE' PO_RELEASE_FLAG
, POR.ACCEPTANCE_DUE_DATE
, POR.APPROVED_DATE
, NVL(POR.AUTHORIZATION_STATUS
, 'INCOMPLETE') AUTHORIZATION_STATUS
, POH.CLOSED_DATE
, POH.COMMENTS
, POR.FIRM_DATE
, POH.NOTE_TO_AUTHORIZER
, POH.NOTE_TO_RECEIVER
, POR.NOTE_TO_VENDOR
, POR.PRINT_COUNT
, POR.PRINTED_DATE
, POH.RATE
, POH.RATE_DATE
, POH.RATE_TYPE
, POR.REVISED_DATE
, POR.REVISION_NUM
, POR.AGENT_ID
, POH.BILL_TO_LOCATION_ID
, POH.FROM_HEADER_ID
, POR.PO_HEADER_ID
, POH.SHIP_TO_LOCATION_ID
, POH.TERMS_ID
, POH.VENDOR_CONTACT_ID
, POH.VENDOR_ID
, POH.VENDOR_SITE_ID
, NVL(POR.CLOSED_CODE
, 'OPEN') CLOSED_CODE
, POH.CURRENCY_CODE
, NVL(POR.FIRM_STATUS_LOOKUP_CODE
, 'N') FIRM_STATUS_LOOKUP_CODE
, POH.FOB_LOOKUP_CODE
, POH.FREIGHT_TERMS_LOOKUP_CODE
, POH.SHIP_VIA_LOOKUP_CODE
, POH.TYPE_LOOKUP_CODE
, POR.ACCEPTANCE_REQUIRED_FLAG
, POH.APPROVAL_REQUIRED_FLAG
, POR.APPROVED_FLAG
, DECODE (POR.CANCEL_FLAG
, 'I'
, NULL
, POR.CANCEL_FLAG) CANCEL_FLAG
, POH.CONFIRMING_ORDER_FLAG
, POH.ENABLED_FLAG
, NVL(POR.FROZEN_FLAG
, 'N') FROZEN_FLAG
, POH.SUMMARY_FLAG
, NVL(POR.HOLD_FLAG
, 'N') USER_HOLD_FLAG
, POR.CREATED_BY
, POR.RELEASE_DATE
, POR.LAST_UPDATED_BY
, POR.LAST_UPDATE_DATE
, POR.LAST_UPDATE_LOGIN
, POR.PROGRAM_APPLICATION_ID
, POR.PROGRAM_ID
, POR.PROGRAM_UPDATE_DATE
, POR.REQUEST_ID
, POH.SEGMENT1 PO_NUM
, PO_INQ_SV.GET_PERSON_NAME(POR.AGENT_ID) AGENT_NAME
, V.VENDOR_NAME
, VS.VENDOR_SITE_CODE
, VS.ADDRESS_LINE1
, VS.ADDRESS_LINE2
, VS.ADDRESS_LINE3
, VS.CITY
, VS.STATE
, VS.ZIP
, VS.COUNTRY
, DECODE (VS.PHONE
, NULL
, NULL
, '('||VS.AREA_CODE||') '||VS.PHONE) PHONE
, DECODE (VS.FAX
, NULL
, NULL
, '('||VS.FAX_AREA_CODE||') '||VS.FAX) FAX
, AT.NAME
, PDT.TYPE_NAME TYPE_NAME
, HRL1.LOCATION_CODE SHIP_TO_LOCATION
, HRL1.ADDRESS_LINE_1 SHIP_ADDR1
, HRL1.ADDRESS_LINE_2 SHIP_ADDR2
, HRL1.ADDRESS_LINE_3 SHIP_ADDR3
, HRL1.TOWN_OR_CITY SHIP_CITY
, HRL1.REGION_2 SHIP_STATE
, HRL1.POSTAL_CODE SHIP_ZIP
, HRL1.COUNTRY SHIP_COUNTRY
, HRL2.LOCATION_CODE BILL_TO_LOCATION
, HRL2.ADDRESS_LINE_1 BILL_ADDR1
, HRL2.ADDRESS_LINE_2 BILL_ADDR2
, HRL2.ADDRESS_LINE_3 BILL_ADDR3
, HRL2.TOWN_OR_CITY BILL_CITY
, HRL2.REGION_2 BILL_STATE
, HRL2.POSTAL_CODE BILL_ZIP
, HRL2.COUNTRY BILL_COUNTRY
, POLC2.MEANING FOB_DSP
, POLC3.MEANING FREIGHT_TERMS_DSP
, POR.CANCEL_DATE
, POR.CANCEL_REASON
, POR.CANCELLED_BY
, PO_INQ_SV.GET_PERSON_NAME(POR.CANCELLED_BY) CANCELLED_BY_NAME
, POR.HOLD_BY
, POR.HOLD_DATE
, POR.HOLD_REASON
, POR.RELEASE_NUM
, POR.RELEASE_TYPE
, POR.PO_RELEASE_ID
, TO_CHAR( PO_TOTALS_PO_SV.GET_RELEASE_TOTAL(POR.PO_RELEASE_ID)
, FND_CURRENCY.SAFE_GET_FORMAT_MASK(POH.CURRENCY_CODE
, 30)) AMOUNT
, V.ATTRIBUTE14 SUPPLIER_URL
, POA.ACCEPTED_FLAG ACCEPTANCE_FLAG
, POA.ACTION_DATE ACTION_DATE
, POH.USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE
, POH.ORG_ID
, HOU.NAME ORG_NAME
FROM PO_VENDOR_SITES_ALL VS
, PO_VENDORS V
, PO_RELEASES_ALL POR
, PO_ACCEPTANCES POA
, HR_LOCATIONS_ALL HRL1
, HR_LOCATIONS_ALL HRL2
, FND_LOOKUP_VALUES_VL POLC2
, FND_LOOKUP_VALUES_VL POLC3
, AP_TERMS AT
, PO_DOCUMENT_TYPES_ALL_TL PDT
, PO_HEADERS_ALL POH
, HR_ALL_ORGANIZATION_UNITS_TL HOU
WHERE POH.PO_HEADER_ID = POR.PO_HEADER_ID
AND PDT.DOCUMENT_SUBTYPE = POH.TYPE_LOOKUP_CODE
AND PDT.ORG_ID = POH.ORG_ID
AND PDT.LANGUAGE = USERENV('LANG')
AND PDT.DOCUMENT_TYPE_CODE IN ('RELEASE')
AND POH.VENDOR_ID = V.VENDOR_ID
AND VS.VENDOR_SITE_ID = POH.VENDOR_SITE_ID
AND POH.TYPE_LOOKUP_CODE = 'BLANKET'
AND POH.APPROVED_FLAG = 'Y'
AND POH.AUTHORIZATION_STATUS NOT IN ('IN PROCESS'
, 'INCOMPLETE'
, 'REQUIRES REAPPROVAL')
AND HRL1.LOCATION_ID = POH.SHIP_TO_LOCATION_ID
AND HRL2.LOCATION_ID = POH.BILL_TO_LOCATION_ID
AND POLC2.LOOKUP_CODE = POH.FOB_LOOKUP_CODE
AND POLC2.LOOKUP_TYPE = 'FOB'
AND POLC2.VIEW_APPLICATION_ID = 201
AND POLC3.LOOKUP_CODE = POH.FREIGHT_TERMS_LOOKUP_CODE
AND POLC3.LOOKUP_TYPE = 'FREIGHT TERMS'
AND POLC3.VIEW_APPLICATION_ID = 201
AND AT.TERM_ID (+) = POH.TERMS_ID
AND POR.PO_HEADER_ID = POA.PO_HEADER_ID (+)
AND POR.PO_RELEASE_ID = POA.PO_RELEASE_ID (+)
AND ( POA.CREATION_DATE = (SELECT MAX(CREATION_DATE)
FROM PO_ACCEPTANCES PA2
WHERE POR.PO_HEADER_ID = PA2.PO_HEADER_ID
AND POR.PO_RELEASE_ID= PA2.PO_RELEASE_ID) OR POA.CREATION_DATE IS NULL)
AND HOU.ORGANIZATION_ID = POH.ORG_ID
AND HOU.LANGUAGE = USERENV('LANG')

Columns

Name
PO_RELEASE_FLAG
ACCEPTANCE_DUE_DATE
APPROVED_DATE
AUTHORIZATION_STATUS
CLOSED_DATE
COMMENTS
FIRM_DATE
NOTE_TO_AUTHORIZER
NOTE_TO_RECEIVER
NOTE_TO_VENDOR
PRINT_COUNT
PRINTED_DATE
RATE
RATE_DATE
RATE_TYPE
REVISED_DATE
REVISION_NUM
AGENT_ID
BILL_TO_LOCATION_ID
FROM_HEADER_ID
PO_HEADER_ID
SHIP_TO_LOCATION_ID
TERMS_ID
VENDOR_CONTACT_ID
VENDOR_ID
VENDOR_SITE_ID
CLOSED_CODE
CURRENCY_CODE
FIRM_STATUS_LOOKUP_CODE
FOB_LOOKUP_CODE
FREIGHT_TERMS_LOOKUP_CODE
SHIP_VIA_LOOKUP_CODE
TYPE_LOOKUP_CODE
ACCEPTANCE_REQUIRED_FLAG
APPROVAL_REQUIRED_FLAG
APPROVED_FLAG
CANCEL_FLAG
CONFIRMING_ORDER_FLAG
ENABLED_FLAG
FROZEN_FLAG
SUMMARY_FLAG
USER_HOLD_FLAG
CREATED_BY
CREATION_DATE
LAST_UPDATED_BY
LAST_UPDATE_DATE
LAST_UPDATE_LOGIN
PROGRAM_APPLICATION_ID
PROGRAM_ID
PROGRAM_UPDATE_DATE
REQUEST_ID
PO_NUM
AGENT_NAME
VENDOR_NAME
VENDOR_SITE_CODE
ADDRESS_LINE1
ADDRESS_LINE2
ADDRESS_LINE3
CITY
STATE
ZIP
COUNTRY
PHONE
FAX
NAME
TYPE_NAME
SHIP_TO_LOCATION
SHIP_ADDR1
SHIP_ADDR2
SHIP_ADDR3
SHIP_CITY
SHIP_STATE
SHIP_ZIP
SHIP_COUNTRY
BILL_TO_LOCATION
BILL_ADDR1
BILL_ADDR2
BILL_ADDR3
BILL_CITY
BILL_STATE
BILL_ZIP
BILL_COUNTRY
FOB_DSP
FREIGHT_TERMS_DSP
CANCEL_DATE
CANCEL_REASON
CANCELLED_BY
CANCELLED_BY_NAME
HOLD_BY
HOLD_DATE
HOLD_REASON
RELEASE_NUM
RELEASE_TYPE
PO_RELEASE_ID
AMOUNT
SUPPLIER_URL
ACCEPTANCE_FLAG
ACTION_DATE
USSGL_TRANSACTION_CODE
ORG_ID
ORG_NAME