View: PO_REQUISITION_TEMPLATES_V

Product:	PO - Purchasing
Description:	- Retrofitted
Implementation/DBA Data:	APPS.PO_REQUISITION_TEMPLATES_V

View Text

SELECT DISTINCT PRH.EXPRESS_NAME
, PRH.TYPE_LOOKUP_CODE
, PDTL.TYPE_NAME
, PRH.DESCRIPTION
, PRL.SEQUENCE_NUM
, PRL.PO_HEADER_ID
, PH.SEGMENT1
, PH.TYPE_LOOKUP_CODE
, PDTL1.TYPE_NAME
, PH.CURRENCY_CODE
, PH.RATE
, PH.RATE_TYPE
, DCT.USER_CONVERSION_TYPE
, PH.RATE_DATE
, GSB.CURRENCY_CODE
, PRL.PO_LINE_ID
, PL.LINE_NUM
, PL.UNIT_PRICE
, ROUND(PL.UNIT_PRICE * NVL(PH.RATE
, 1)
, 5)
, PRL.ITEM_ID
, PRL.ITEM_DESCRIPTION
, PRL.LINE_TYPE_ID
, PLT.LINE_TYPE
, PRL.ITEM_REVISION
, PRL.CATEGORY_ID
, PRL.UNIT_MEAS_LOOKUP_CODE
, DECODE(PRL.SOURCE_TYPE_CODE
, 'INVENTORY'
, NVL(CIC.ITEM_COST
, 0)
, 'VENDOR'
, PRL.UNIT_PRICE)
, DECODE(PRL.SOURCE_TYPE_CODE
, 'INVENTORY'
, PRL.SOURCE_ORGANIZATION_ID
, 'VENDOR'
, PRL.SUGGESTED_VENDOR_ID)
, DECODE(PRL.SOURCE_TYPE_CODE
, 'INVENTORY'
, HOUT.NAME
, 'VENDOR'
, PV.VENDOR_NAME)
, PRL.SUGGESTED_VENDOR_SITE_ID
, PVS.VENDOR_SITE_CODE
, PRL.SUGGESTED_VENDOR_CONTACT_ID
, DECODE(PRL.SUGGESTED_VENDOR_CONTACT_ID
, NULL
, NULL
, PVC.LAST_NAME|| '
, '||PVC.FIRST_NAME)
, PRL.SUGGESTED_VENDOR_PRODUCT_CODE
, PRL.SUGGESTED_BUYER_ID
, PAPF.FULL_NAME
, PRL.RFQ_REQUIRED_FLAG
, PRL.SOURCE_TYPE_CODE
, PLC.DISPLAYED_FIELD
, MP.ORGANIZATION_CODE
, PRL.SOURCE_SUBINVENTORY
, PLT.OUTSIDE_OPERATION_FLAG
, PLT.ORDER_TYPE_LOOKUP_CODE
, PLC1.DISPLAYED_FIELD
, PRL.AMOUNT
, PLT.PURCHASE_BASIS
, PRL.NEGOTIATED_BY_PREPARER_FLAG
, PLT.MATCHING_BASIS
, PRH.ORG_ID
FROM PO_REQEXPRESS_HEADERS PRH
, PO_REQEXPRESS_LINES_ALL PRL
, PO_DOCUMENT_TYPES_ALL_B PDTB
, PO_DOCUMENT_TYPES_ALL_TL PDTL
, PO_LINE_TYPES PLT
, PO_VENDORS PV
, GL_DAILY_CONVERSION_TYPES DCT
, GL_SETS_OF_BOOKS GSB
, FINANCIALS_SYSTEM_PARAMS_ALL FSP
, PO_VENDOR_SITES_ALL PVS
, PO_VENDOR_CONTACTS PVC
, PER_ALL_PEOPLE_F PAPF
, PO_LOOKUP_CODES PLC
, PO_LOOKUP_CODES PLC1
, HR_ORG_UNITS_NO_JOIN HOU
, HR_ALL_ORGANIZATION_UNITS_TL HOUT
, MTL_PARAMETERS MP
, PO_HEADERS_ALL PH
, PO_LINES_ALL PL
, CST_ITEM_COSTS CIC
, PO_DOCUMENT_TYPES_ALL_B PDTB1
, PO_DOCUMENT_TYPES_ALL_TL PDTL1
WHERE PRH.EXPRESS_NAME = PRL.EXPRESS_NAME
AND PRH.ORG_ID = PRL.ORG_ID
AND PRL.SUGGESTED_VENDOR_ID = PV.VENDOR_ID(+)
AND PRL.SUGGESTED_VENDOR_SITE_ID = PVS.VENDOR_SITE_ID(+)
AND PRL.SUGGESTED_VENDOR_CONTACT_ID = PVC.VENDOR_CONTACT_ID(+)
AND PRL.SUGGESTED_BUYER_ID = PAPF.PERSON_ID(+)
AND PAPF.EMPLOYEE_NUMBER(+) IS NOT NULL
AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE(+)
AND PAPF.EFFECTIVE_END_DATE(+)
AND DECODE(HR_SECURITY.VIEW_ALL
, 'Y'
, 'TRUE'
, HR_SECURITY.SHOW_RECORD('PER_ALL_PEOPLE_F'
, PAPF.PERSON_ID(+)
, PAPF.PERSON_TYPE_ID(+)
, PAPF.EMPLOYEE_NUMBER(+)
, PAPF.APPLICANT_NUMBER(+))) = 'TRUE'
AND DECODE(HR_GENERAL.GET_XBG_PROFILE
, 'Y'
, PAPF.BUSINESS_GROUP_ID(+)
, HR_GENERAL.GET_BUSINESS_GROUP_ID) = PAPF.BUSINESS_GROUP_ID(+)
AND SYSDATE < NVL(PRH.INACTIVE_DATE
, SYSDATE + 1)
AND PDTB.DOCUMENT_TYPE_CODE = 'REQUISITION'
AND PRH.TYPE_LOOKUP_CODE = 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 = PRH.ORG_ID
AND PDTL.LANGUAGE(+) = USERENV('LANG')
AND PRL.LINE_TYPE_ID = PLT.LINE_TYPE_ID
AND PRL.SOURCE_TYPE_CODE = PLC.LOOKUP_CODE
AND PLC.LOOKUP_TYPE = 'REQUISITION SOURCE TYPE'
AND FSP.SET_OF_BOOKS_ID = GSB.SET_OF_BOOKS_ID
AND FSP.ORG_ID = PRH.ORG_ID
AND PRL.SOURCE_ORGANIZATION_ID = HOU.ORGANIZATION_ID(+) AND HOU.ORGANIZATION_ID = HOUT.ORGANIZATION_ID (+)
AND DECODE(HOUT.ORGANIZATION_ID
, NULL
, '1'
, HOUT.LANGUAGE) = DECODE(HOUT.ORGANIZATION_ID
, NULL
, '1'
, USERENV('LANG'))
AND PRL.SOURCE_ORGANIZATION_ID = MP.ORGANIZATION_ID(+)
AND PRL.PO_HEADER_ID = PH.PO_HEADER_ID(+)
AND PH.AUTHORIZATION_STATUS (+) = 'APPROVED'
AND NVL(PH.CANCEL_FLAG
, 'N') <> 'Y'
AND NVL(PH.FROZEN_FLAG
, 'N') <> 'Y'
AND NVL(PH.CLOSED_CODE
, 'OPEN') <> 'FINALLY CLOSED'
AND NVL(PH.GLOBAL_AGREEMENT_FLAG
, 'N') = 'N'
AND TRUNC(NVL(PH.END_DATE
, SYSDATE)) >= TRUNC(SYSDATE)
AND NVL(PH.PO_HEADER_ID
, -1) = NVL(PL.PO_HEADER_ID
, -1)
AND PRL.PO_LINE_ID = PL.PO_LINE_ID(+)
AND NVL(PL.CANCEL_FLAG
, 'N') <> 'Y'
AND NVL(PL.CLOSED_CODE
, 'OPEN') <> 'FINALLY CLOSED'
AND TRUNC(NVL(PL.EXPIRATION_DATE
, SYSDATE)) >= TRUNC(SYSDATE)
AND PH.RATE_TYPE = DCT.CONVERSION_TYPE(+)
AND PRL.ITEM_ID = CIC.INVENTORY_ITEM_ID(+)
AND (PRL.SOURCE_TYPE_CODE IN ('VENDOR') OR (NVL(CIC.COST_TYPE_ID
, MP.PRIMARY_COST_METHOD) = MP.PRIMARY_COST_METHOD
AND MP.COST_ORGANIZATION_ID =NVL( CIC.ORGANIZATION_ID
, MP.COST_ORGANIZATION_ID)))
AND PH.TYPE_LOOKUP_CODE = PDTB1.DOCUMENT_SUBTYPE(+)
AND PDTB1.DOCUMENT_TYPE_CODE(+) = 'PA'
AND PDTB1.DOCUMENT_TYPE_CODE = PDTL1.DOCUMENT_TYPE_CODE(+)
AND PDTB1.DOCUMENT_SUBTYPE = PDTL1.DOCUMENT_SUBTYPE(+)
AND PDTB1.ORG_ID = PDTL1.ORG_ID (+)
AND PH.ORG_ID = PDTB1.ORG_ID(+)
AND PDTL1.LANGUAGE(+) = USERENV('LANG')
AND PLT.ORDER_TYPE_LOOKUP_CODE = PLC1.LOOKUP_CODE
AND PLC1.LOOKUP_TYPE = 'ORDER TYPE'
AND PLT.PURCHASE_BASIS <> 'TEMP LABOR'

Columns