View: PO_NEGOTIATED_SOURCES_V

Product:	PO - Purchasing
Description:	Negotiated Sources
Implementation/DBA Data:	APPS.PO_NEGOTIATED_SOURCES_V

View Text

SELECT PH.PO_HEADER_ID
, PH.VENDOR_ID
, PV.VENDOR_NAME
, PH.VENDOR_CONTACT_ID
, DECODE(PH.VENDOR_CONTACT_ID
, NULL
, NULL
, PVC.LAST_NAME||'
, '|| PVC.FIRST_NAME)
, PV.PARENT_VENDOR_ID
, PV1.VENDOR_NAME
, PV.HOLD_FLAG
, DECODE(PGOA.PO_HEADER_ID
, NULL
, PVSA.VENDOR_SITE_ID
, PVSA2.VENDOR_SITE_ID)
, DECODE(PGOA.PO_HEADER_ID
, NULL
, PVSA.VENDOR_SITE_CODE
, PVSA2.VENDOR_SITE_CODE)
, PV.WOMEN_OWNED_FLAG
, PV.SMALL_BUSINESS_FLAG
, PV.MINORITY_GROUP_LOOKUP_CODE
, PLC1.DISPLAYED_FIELD
, PH.TYPE_LOOKUP_CODE
, PDTL.TYPE_NAME
, DECODE(PH.TYPE_LOOKUP_CODE
, 'QUOTATION'
, PH.STATUS_LOOKUP_CODE
, 'BLANKET'
, PH.AUTHORIZATION_STATUS
, 'PLANNED'
, PH.AUTHORIZATION_STATUS)
, PLC.DISPLAYED_FIELD
, PH.SEGMENT1
, PH.AGENT_ID
, PPF.FULL_NAME
, PH.TERMS_ID
, AT.NAME
, PH.SHIP_VIA_LOOKUP_CODE
, PH.FREIGHT_TERMS_LOOKUP_CODE
, PH.FOB_LOOKUP_CODE
, PH.CURRENCY_CODE
, PH.QUOTE_VENDOR_QUOTE_NUMBER
, PH.APPROVAL_REQUIRED_FLAG
, PH.FROM_HEADER_ID
, PH1.SEGMENT1
, PH.START_DATE
, PH.END_DATE
, PH.BLANKET_TOTAL_AMOUNT
, PH.AMOUNT_LIMIT
, PH.MIN_RELEASE_AMOUNT
, PL.PO_LINE_ID
, PL.LINE_NUM
, PL.LINE_TYPE_ID
, PLT.LINE_TYPE
, PL.LINE_NUM||'-'||PLL.SHIPMENT_NUM
, PL.ITEM_ID
, PL.ITEM_REVISION
, PL.CATEGORY_ID
, PL.ITEM_DESCRIPTION
, PL.UNIT_MEAS_LOOKUP_CODE
, PL.QUANTITY
, PL.UNIT_PRICE
, PL.VENDOR_PRODUCT_NUM
, PL.QUANTITY_COMMITTED
, PL.MIN_ORDER_QUANTITY
, PL.MAX_ORDER_QUANTITY
, PLL.LINE_LOCATION_ID
, PLL.QUANTITY - NVL(PLL.QUANTITY_CANCELLED
, 0)
, PLL.PRICE_OVERRIDE
, PLL.UNIT_MEAS_LOOKUP_CODE
, PLL.SHIP_TO_LOCATION_ID
, HL.LOCATION_CODE
, PLL.SHIP_TO_ORGANIZATION_ID
, MP.ORGANIZATION_CODE
, HOUT.NAME
, PLL.SHIP_VIA_LOOKUP_CODE
, PLL.FOB_LOOKUP_CODE
, PLL.FREIGHT_TERMS_LOOKUP_CODE
, PLL.TERMS_ID
, AT1.NAME
, PLL.START_DATE
, PLL.END_DATE
, PLL.LEAD_TIME
, PLL.LEAD_TIME_UNIT
, GSB.CURRENCY_CODE
, PLT.OUTSIDE_OPERATION_FLAG
, PLT.ORDER_TYPE_LOOKUP_CODE
, PLC2.DISPLAYED_FIELD
, PL.SECONDARY_UNIT_OF_MEASURE
, PL.SECONDARY_QUANTITY
, PLL.SECONDARY_UNIT_OF_MEASURE
, PLL.SECONDARY_QUANTITY - NVL( PLL.SECONDARY_QUANTITY_CANCELLED
, 0)
, DECODE(PH.TYPE_LOOKUP_CODE
, 'QUOTATION'
, NULL
, 'BLANKET'
, PL.PREFERRED_GRADE
, 'PLANNED'
, PLL.PREFERRED_GRADE)
, PH.GLOBAL_AGREEMENT_FLAG
, PH.ORG_ID
, PL.AMOUNT
, PL.JOB_ID
, PLT.PURCHASE_BASIS
, PGOA.ORGANIZATION_ID
, PGOA.PURCHASING_ORG_ID
, DECODE(PH.TYPE_LOOKUP_CODE
, 'QUOTATION'
, 'Y'
, PL.NEGOTIATED_BY_PREPARER_FLAG)
, PLT.MATCHING_BASIS
, PH.ORG_ID
FROM PO_HEADERS_ALL PH
, PO_HEADERS_ALL PH1
, PO_LINES_ALL PL
, PO_LINE_LOCATIONS_ALL PLL
, PO_DOCUMENT_TYPES_ALL_B PDTB
, PO_DOCUMENT_TYPES_ALL_TL PDTL
, PO_LOOKUP_CODES PLC
, PO_LOOKUP_CODES PLC1
, PO_LOOKUP_CODES PLC2
, PER_ALL_PEOPLE_F PPF
, GL_SETS_OF_BOOKS GSB
, FINANCIALS_SYSTEM_PARAMETERS FSP
, HR_ALL_ORGANIZATION_UNITS_TL HOUT
, MTL_PARAMETERS MP
, HR_LOCATIONS_ALL_TL HL
, PO_VENDORS PV
, PO_VENDORS PV1
, PO_VENDOR_SITES_ALL PVSA
, PO_VENDOR_SITES_ALL PVSA2
, PO_VENDOR_CONTACTS PVC
, AP_TERMS AT
, AP_TERMS AT1
, PO_LINE_TYPES PLT
, PO_GA_ORG_ASSIGNMENTS PGOA
, PO_SYSTEM_PARAMETERS PSP
WHERE PH.TYPE_LOOKUP_CODE IN ('BLANKET'
, 'QUOTATION'
, 'PLANNED')
AND (PH.GLOBAL_AGREEMENT_FLAG = 'Y' OR PH.ORG_ID = PSP.ORG_ID)
AND PH.PO_HEADER_ID = PL.PO_HEADER_ID
AND PL.PO_LINE_ID = PLL.PO_LINE_ID
AND NVL(PLL.SHIPMENT_TYPE
, 'PRICE BREAK') IN ('PRICE BREAK'
, 'QUOTATION'
, 'PLANNED')
AND (( ( ( PH.TYPE_LOOKUP_CODE = 'BLANKET'
AND PH.AUTHORIZATION_STATUS = 'APPROVED') OR ( PH.TYPE_LOOKUP_CODE = 'PLANNED'
AND PH.AUTHORIZATION_STATUS IN ('APPROVED'
, 'PRE-APPROVED')) )
AND PH.APPROVED_DATE IS NOT NULL
AND NVL(PH.CANCEL_FLAG
, 'N') != 'Y'
AND NVL(PH.FROZEN_FLAG
, 'N') != 'Y'
AND NVL(PH.CLOSED_CODE
, 'OPEN') != 'FINALLY CLOSED'
AND NVL(PL.CLOSED_CODE
, 'OPEN') != 'FINALLY CLOSED'
AND NVL(PL.CANCEL_FLAG
, 'N') != 'Y'
AND NVL(PLL.CANCEL_FLAG
, 'N') != 'Y'
AND NVL(PLL.CLOSED_CODE
, 'OPEN') != 'FINALLY CLOSED') OR ( PH.STATUS_LOOKUP_CODE = 'A'
AND PH.QUOTATION_CLASS_CODE = 'CATALOG'))
AND (TRUNC(SYSDATE) BETWEEN NVL(PH.START_DATE
, TRUNC(SYSDATE) )
AND NVL(PH.END_DATE
, TRUNC(SYSDATE) ) OR (TRUNC(PH.START_DATE) IS NOT NULL
AND TRUNC(SYSDATE) <= NVL(TRUNC(PH.START_DATE)
, TRUNC(SYSDATE))))
AND (TRUNC(SYSDATE) BETWEEN NVL(PLL.START_DATE
, TRUNC(SYSDATE) - 1)
AND NVL(PLL.END_DATE
, TRUNC(SYSDATE) + 1) OR (TRUNC(PLL.START_DATE) IS NOT NULL
AND TRUNC(SYSDATE) <= NVL(TRUNC(PLL.START_DATE)
, TRUNC(SYSDATE))))
AND NVL(TRUNC(PL.EXPIRATION_DATE)
, TRUNC(SYSDATE)) >=TRUNC(SYSDATE)
AND PH.VENDOR_ID = PV.VENDOR_ID
AND PH.VENDOR_SITE_ID = PVSA.VENDOR_SITE_ID(+)
AND PH.PO_HEADER_ID = PGOA.PO_HEADER_ID (+)
AND PGOA.ENABLED_FLAG (+) = 'Y'
AND (PGOA.PO_HEADER_ID IS NULL OR (PGOA.ORGANIZATION_ID = PSP.ORG_ID OR PGOA.PURCHASING_ORG_ID = PSP.ORG_ID ) )
AND PGOA.VENDOR_SITE_ID = PVSA2.VENDOR_SITE_ID (+)
AND ( ( PVSA2.VENDOR_ID = PV.VENDOR_ID
AND NVL(PVSA2.RFQ_ONLY_SITE_FLAG
, 'N') = 'N'
AND TRUNC(SYSDATE) < TRUNC(NVL(PVSA2.INACTIVE_DATE
, SYSDATE+1)) ) OR ( PVSA.VENDOR_ID = PV.VENDOR_ID
AND NVL(PVSA.RFQ_ONLY_SITE_FLAG
, 'N') = 'N'
AND TRUNC(SYSDATE) < TRUNC(NVL(PVSA.INACTIVE_DATE
, SYSDATE+1)) ) OR ( PH.VENDOR_SITE_ID IS NULL ) )
AND PV.PARENT_VENDOR_ID = PV1.VENDOR_ID(+)
AND PH.VENDOR_CONTACT_ID = PVC.VENDOR_CONTACT_ID(+)
AND PV.MINORITY_GROUP_LOOKUP_CODE = PLC1.LOOKUP_CODE(+)
AND PLC1.LOOKUP_TYPE(+) = 'MINORITY GROUP'
AND PH.AGENT_ID = PPF.PERSON_ID
AND TRUNC(SYSDATE) BETWEEN NVL(PPF.EFFECTIVE_START_DATE
, TRUNC(SYSDATE) - 1)
AND NVL(PPF.EFFECTIVE_END_DATE
, TRUNC(SYSDATE) + 1)
AND PH.TERMS_ID = AT.TERM_ID(+)
AND PH.FROM_HEADER_ID = PH1.PO_HEADER_ID(+)
AND DECODE(PH.TYPE_LOOKUP_CODE
, 'QUOTATION'
, 'QUOTATION'
, 'BLANKET'
, 'PA'
, 'PLANNED'
, 'PO') = PDTB.DOCUMENT_TYPE_CODE
AND DECODE(PH.TYPE_LOOKUP_CODE
, 'QUOTATION'
, PH.QUOTE_TYPE_LOOKUP_CODE
, 'BLANKET'
, 'BLANKET'
, 'PLANNED'
, 'PLANNED') = PDTB.DOCUMENT_SUBTYPE
AND PDTB.DOCUMENT_TYPE_CODE = PDTL.DOCUMENT_TYPE_CODE(+)
AND PDTB.DOCUMENT_SUBTYPE = PDTL.DOCUMENT_SUBTYPE(+)
AND PDTB.ORG_ID = PDTL.ORG_ID
AND PDTB.ORG_ID = PSP.ORG_ID
AND PDTL.LANGUAGE(+) = USERENV('LANG')
AND DECODE(PH.TYPE_LOOKUP_CODE
, 'QUOTATION'
, PH.STATUS_LOOKUP_CODE
, 'BLANKET'
, PH.AUTHORIZATION_STATUS
, 'PLANNED'
, PH.AUTHORIZATION_STATUS) = PLC.LOOKUP_CODE
AND DECODE(PH.TYPE_LOOKUP_CODE
, 'QUOTATION'
, 'RFQ/QUOTE STATUS'
, 'BLANKET'
, 'AUTHORIZATION STATUS'
, 'PLANNED'
, 'AUTHORIZATION STATUS') = PLC.LOOKUP_TYPE
AND PL.LINE_TYPE_ID = PLT.LINE_TYPE_ID
AND PLL.SHIP_TO_LOCATION_ID = HL.LOCATION_ID(+)
AND HL.LANGUAGE(+) = USERENV('LANG')
AND PLL.SHIP_TO_ORGANIZATION_ID = HOUT.ORGANIZATION_ID (+)
AND HOUT.LANGUAGE (+) = USERENV('LANG')
AND PLL.TERMS_ID = AT1.TERM_ID(+)
AND FSP.SET_OF_BOOKS_ID = GSB.SET_OF_BOOKS_ID
AND PLL.SHIP_TO_ORGANIZATION_ID = MP.ORGANIZATION_ID(+)
AND PLT.ORDER_TYPE_LOOKUP_CODE = PLC2.LOOKUP_CODE
AND PLC2.LOOKUP_TYPE = 'ORDER TYPE'
AND PSP.ORG_ID = FSP.ORG_ID UNION SELECT PH.PO_HEADER_ID
, PH.VENDOR_ID
, PV.VENDOR_NAME
, PH.VENDOR_CONTACT_ID
, DECODE(PH.VENDOR_CONTACT_ID
, NULL
, NULL
, PVC.LAST_NAME||'
, '||PVC.FIRST_NAME)
, PV.PARENT_VENDOR_ID
, PV1.VENDOR_NAME
, PV.HOLD_FLAG
, DECODE(PGOA.PO_HEADER_ID
, NULL
, PVSA.VENDOR_SITE_ID
, PVSA2.VENDOR_SITE_ID)
, DECODE(PGOA.PO_HEADER_ID
, NULL
, PVSA.VENDOR_SITE_CODE
, PVSA2.VENDOR_SITE_CODE)
, PV.WOMEN_OWNED_FLAG
, PV.SMALL_BUSINESS_FLAG
, PV.MINORITY_GROUP_LOOKUP_CODE
, PLC1.DISPLAYED_FIELD
, PH.TYPE_LOOKUP_CODE
, PDTL.TYPE_NAME
, DECODE(PH.TYPE_LOOKUP_CODE
, 'QUOTATION'
, PH.STATUS_LOOKUP_CODE
, 'BLANKET'
, PH.AUTHORIZATION_STATUS
, 'PLANNED'
, PH.AUTHORIZATION_STATUS)
, PLC.DISPLAYED_FIELD
, PH.SEGMENT1
, PH.AGENT_ID
, PPF.FULL_NAME
, PH.TERMS_ID
, AT.NAME
, PH.SHIP_VIA_LOOKUP_CODE
, PH.FREIGHT_TERMS_LOOKUP_CODE
, PH.FOB_LOOKUP_CODE
, PH.CURRENCY_CODE
, PH.QUOTE_VENDOR_QUOTE_NUMBER
, PH.APPROVAL_REQUIRED_FLAG
, PH.FROM_HEADER_ID
, PH1.SEGMENT1
, PH.START_DATE
, PH.END_DATE
, PH.BLANKET_TOTAL_AMOUNT
, PH.AMOUNT_LIMIT
, PH.MIN_RELEASE_AMOUNT
, PL.PO_LINE_ID
, PL.LINE_NUM
, PL.LINE_TYPE_ID
, PLT.LINE_TYPE
, TO_CHAR(PL.LINE_NUM)
, PL.ITEM_ID
, PL.ITEM_REVISION
, PL.CATEGORY_ID
, PL.ITEM_DESCRIPTION
, PL.UNIT_MEAS_LOOKUP_CODE
, PL.QUANTITY
, PL.UNIT_PRICE
, PL.VENDOR_PRODUCT_NUM
, PL.QUANTITY_COMMITTED
, PL.MIN_ORDER_QUANTITY
, PL.MAX_ORDER_QUANTITY
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, NULL
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_DATE(NULL)
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, NULL
, GSB.CURRENCY_CODE
, PLT.OUTSIDE_OPERATION_FLAG
, PLT.ORDER_TYPE_LOOKUP_CODE
, PLC2.DISPLAYED_FIELD
, PL.SECONDARY_UNIT_OF_MEASURE
, PL.SECONDARY_QUANTITY
, NULL
, TO_NUMBER(NULL)
, PL.PREFERRED_GRADE
, PH.GLOBAL_AGREEMENT_FLAG
, PH.ORG_ID
, PL.AMOUNT
, PL.JOB_ID
, PLT.PURCHASE_BASIS
, PGOA.ORGANIZATION_ID
, PGOA.PURCHASING_ORG_ID
, DECODE(PH.TYPE_LOOKUP_CODE
, 'QUOTATION'
, 'Y'
, PL.NEGOTIATED_BY_PREPARER_FLAG)
, PLT.MATCHING_BASIS
, PH.ORG_ID
FROM PO_HEADERS_ALL PH
, PO_HEADERS_ALL PH1
, PO_LINES_ALL PL
, PO_DOCUMENT_TYPES_ALL_B PDTB
, PO_DOCUMENT_TYPES_ALL_TL PDTL
, PO_LOOKUP_CODES PLC
, PO_LOOKUP_CODES PLC1
, PO_LOOKUP_CODES PLC2
, PER_ALL_PEOPLE_F PPF
, GL_SETS_OF_BOOKS GSB
, FINANCIALS_SYSTEM_PARAMETERS FSP
, PO_VENDORS PV
, PO_VENDORS PV1
, PO_VENDOR_SITES_ALL PVSA
, PO_VENDOR_SITES_ALL PVSA2
, PO_VENDOR_CONTACTS PVC
, AP_TERMS AT
, PO_LINE_TYPES PLT
, PO_GA_ORG_ASSIGNMENTS PGOA
, PO_SYSTEM_PARAMETERS PSP
WHERE PH.TYPE_LOOKUP_CODE IN ('BLANKET'
, 'QUOTATION'
, 'PLANNED')
AND (PH.GLOBAL_AGREEMENT_FLAG = 'Y' OR PH.ORG_ID = PSP.ORG_ID )
AND PH.PO_HEADER_ID = PL.PO_HEADER_ID
AND NVL(TRUNC(PL.EXPIRATION_DATE)
, TRUNC(SYSDATE)) >=TRUNC(SYSDATE)
AND (( ( ( PH.TYPE_LOOKUP_CODE = 'BLANKET'
AND PH.AUTHORIZATION_STATUS = 'APPROVED') OR ( PH.TYPE_LOOKUP_CODE = 'PLANNED'
AND PH.AUTHORIZATION_STATUS IN ('APPROVED'
, 'PRE-APPROVED')) )
AND PH.APPROVED_DATE IS NOT NULL
AND NVL(PH.CANCEL_FLAG
, 'N') != 'Y'
AND NVL(PH.FROZEN_FLAG
, 'N') != 'Y'
AND NVL(PH.CLOSED_CODE
, 'OPEN') != 'FINALLY CLOSED'
AND NVL(PL.CLOSED_CODE
, 'OPEN') != 'FINALLY CLOSED'
AND NVL(PL.CANCEL_FLAG
, 'N') != 'Y') OR ( PH.STATUS_LOOKUP_CODE = 'A'
AND PH.QUOTATION_CLASS_CODE = 'CATALOG'))
AND (TRUNC(SYSDATE) BETWEEN NVL(PH.START_DATE
, TRUNC(SYSDATE) - 1)
AND NVL(PH.END_DATE
, TRUNC(SYSDATE) + 1) OR (TRUNC(PH.START_DATE) IS NOT NULL
AND TRUNC(SYSDATE) <= NVL(TRUNC(PH.START_DATE)
, TRUNC(SYSDATE))))
AND PH.VENDOR_ID = PV.VENDOR_ID
AND PH.VENDOR_SITE_ID = PVSA.VENDOR_SITE_ID(+)
AND PH.PO_HEADER_ID = PGOA.PO_HEADER_ID (+)
AND PGOA.ENABLED_FLAG (+) = 'Y'
AND (PGOA.PO_HEADER_ID IS NULL OR (PGOA.ORGANIZATION_ID = PSP.ORG_ID OR PGOA.PURCHASING_ORG_ID = PSP.ORG_ID ) )
AND PGOA.VENDOR_SITE_ID = PVSA2.VENDOR_SITE_ID (+)
AND ( ( PVSA2.VENDOR_ID = PV.VENDOR_ID
AND NVL(PVSA2.RFQ_ONLY_SITE_FLAG
, 'N') = 'N'
AND TRUNC(SYSDATE) < TRUNC(NVL(PVSA2.INACTIVE_DATE
, SYSDATE+1)) ) OR ( PVSA.VENDOR_ID = PV.VENDOR_ID
AND NVL(PVSA.RFQ_ONLY_SITE_FLAG
, 'N') = 'N'
AND TRUNC(SYSDATE) < TRUNC(NVL(PVSA.INACTIVE_DATE
, SYSDATE+1)) ) OR ( PH.VENDOR_SITE_ID IS NULL ) )
AND PV.PARENT_VENDOR_ID = PV1.VENDOR_ID(+)
AND PH.VENDOR_CONTACT_ID = PVC.VENDOR_CONTACT_ID(+)
AND PV.MINORITY_GROUP_LOOKUP_CODE = PLC1.LOOKUP_CODE(+)
AND PLC1.LOOKUP_TYPE(+) = 'MINORITY GROUP'
AND PH.AGENT_ID = PPF.PERSON_ID
AND TRUNC(SYSDATE) BETWEEN NVL(PPF.EFFECTIVE_START_DATE
, TRUNC(SYSDATE) - 1)
AND NVL(PPF.EFFECTIVE_END_DATE
, TRUNC(SYSDATE) + 1)
AND PH.TERMS_ID = AT.TERM_ID(+)
AND PH.FROM_HEADER_ID = PH1.PO_HEADER_ID(+)
AND DECODE(PH.TYPE_LOOKUP_CODE
, 'QUOTATION'
, 'QUOTATION'
, 'BLANKET'
, 'PA'
, 'PLANNED'
, 'PO') = PDTB.DOCUMENT_TYPE_CODE
AND DECODE(PH.TYPE_LOOKUP_CODE
, 'QUOTATION'
, PH.QUOTE_TYPE_LOOKUP_CODE
, 'BLANKET'
, 'BLANKET'
, 'PLANNED'
, 'PLANNED') = PDTB.DOCUMENT_SUBTYPE
AND PDTB.DOCUMENT_TYPE_CODE = PDTL.DOCUMENT_TYPE_CODE(+)
AND PDTB.DOCUMENT_SUBTYPE = PDTL.DOCUMENT_SUBTYPE(+)
AND PDTB.ORG_ID = PDTL.ORG_ID
AND PDTB.ORG_ID = PSP.ORG_ID
AND PDTL.LANGUAGE(+) = USERENV('LANG')
AND DECODE(PH.TYPE_LOOKUP_CODE
, 'QUOTATION'
, PH.STATUS_LOOKUP_CODE
, 'BLANKET'
, PH.AUTHORIZATION_STATUS
, 'PLANNED'
, PH.AUTHORIZATION_STATUS) = PLC.LOOKUP_CODE
AND DECODE(PH.TYPE_LOOKUP_CODE
, 'QUOTATION'
, 'RFQ/QUOTE STATUS'
, 'BLANKET'
, 'AUTHORIZATION STATUS'
, 'PLANNED'
, 'AUTHORIZATION STATUS') = PLC.LOOKUP_TYPE
AND PL.LINE_TYPE_ID = PLT.LINE_TYPE_ID
AND FSP.SET_OF_BOOKS_ID = GSB.SET_OF_BOOKS_ID
AND PLT.ORDER_TYPE_LOOKUP_CODE = PLC2.LOOKUP_CODE
AND PLC2.LOOKUP_TYPE = 'ORDER TYPE'
AND PSP.ORG_ID = FSP.ORG_ID

Columns