View: POR_VIEW_LINES_ALL_V

Product:	ICX - Oracle iProcurement
Description:	View Requisition Lines view
Implementation/DBA Data:	APPS.POR_VIEW_LINES_ALL_V

View Text

SELECT PRL.REQUISITION_LINE_ID
, PRL.REQUISITION_HEADER_ID
, MC.SEGMENT1
, MSI.SEGMENT1
, PRL.ITEM_DESCRIPTION
, TO_CHAR(PRL.NEED_BY_DATE
, FND_PROFILE.VALUE_WNPS('ICX_DATE_FORMAT_MASK'))
, POR_VIEW_REQS_PKG.GET_ACCOUNT_NUMBER(PRL.REQUISITION_LINE_ID)
, DECODE(PRL.SOURCE_TYPE_CODE
, 'INVENTORY'
, PO_INQ_SV.GET_SO_NUMBER(PRL.REQUISITION_HEADER_ID
, PRL.REQUISITION_LINE_ID)
, PH.SEGMENT1|| DECODE(PR.RELEASE_NUM
, NULL
, ''
, '-' || PR.RELEASE_NUM))
, PRL.SOURCE_TYPE_CODE
, PLC.DISPLAYED_FIELD
, PRL.QUANTITY
, POR_VIEW_REQS_PKG.GET_LINE_TOTAL(PRL.REQUISITION_LINE_ID
, SOB.CURRENCY_CODE)
, MUOMV.UNIT_OF_MEASURE_TL
, PRL.LINE_NUM
, PRL.UNIT_PRICE
, PV.VENDOR_NAME
, PRL.SUGGESTED_VENDOR_PRODUCT_CODE
, PVS.VENDOR_SITE_CODE
, PRL.SUGGESTED_VENDOR_CONTACT
, HRE.FULL_NAME
, HRL.LOCATION_CODE
, DECODE(PRL.DESTINATION_TYPE_CODE
, 'INVENTORY'
, 'Y'
, 'N')
, PRL.DESTINATION_SUBINVENTORY
, PRL.PARENT_REQ_LINE_ID
, POR_VIEW_REQS_PKG.GET_PROJECT_NUMBER(PRL.REQUISITION_LINE_ID)
, POR_VIEW_REQS_PKG.GET_TASK_NAME(PRL.REQUISITION_LINE_ID)
, POR_VIEW_REQS_PKG.GET_EXPENDITURE_TYPE(PRL.REQUISITION_LINE_ID)
, HRE1.FULL_NAME
FROM PO_REQUISITION_LINES_ALL PRL
, PO_LINE_LOCATIONS_ALL PLL
, PO_HEADERS_ALL PH
, PO_RELEASES_ALL PR
, MTL_SYSTEM_ITEMS_KFV MSI
, MTL_CATEGORIES MC
, PO_VENDORS PV
, PO_VENDOR_SITES_ALL PVS
, PER_ALL_PEOPLE_F HRE
, HR_LOCATIONS HRL
, GL_SETS_OF_BOOKS SOB
, FINANCIALS_SYSTEM_PARAMS_ALL FSP
, MTL_UNITS_OF_MEASURE_VL MUOMV
, PER_ALL_PEOPLE_F HRE1
, PO_LOOKUP_CODES PLC
, PO_REQUISITION_HEADERS_ALL PRH
WHERE PRL.LINE_LOCATION_ID = PLL.LINE_LOCATION_ID(+)
AND PLL.PO_HEADER_ID = PH.PO_HEADER_ID(+)
AND PLL.PO_RELEASE_ID = PR.PO_RELEASE_ID(+)
AND PRL.VENDOR_ID = PV.VENDOR_ID(+)
AND PRL.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID(+)
AND SYSDATE BETWEEN HRE.EFFECTIVE_START_DATE
AND HRE.EFFECTIVE_END_DATE
AND HRE.PERSON_ID = PRL.TO_PERSON_ID
AND PRL.DELIVER_TO_LOCATION_ID = HRL.LOCATION_ID
AND PRL.ITEM_ID = MSI.INVENTORY_ITEM_ID(+)
AND PRL.DESTINATION_ORGANIZATION_ID = MSI.ORGANIZATION_ID(+)
AND PRL.CATEGORY_ID = MC.CATEGORY_ID
AND SOB.SET_OF_BOOKS_ID = FSP.SET_OF_BOOKS_ID
AND PRL.SUGGESTED_BUYER_ID=HRE1.PERSON_ID(+)
AND NVL(HRE1.EFFECTIVE_START_DATE
, SYSDATE) <= SYSDATE
AND NVL(HRE1.EFFECTIVE_END_DATE
, SYSDATE) >= SYSDATE
AND NVL(PRL.MODIFIED_BY_AGENT_FLAG
, 'N') = 'N'
AND PRL.UNIT_MEAS_LOOKUP_CODE = MUOMV.UNIT_OF_MEASURE
AND NVL(FSP.ORG_ID
, -1) = NVL(PRL.ORG_ID
, -1)
AND PRH.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_ID
AND PLC.LOOKUP_TYPE(+) = 'REQUISITION TYPE'
AND PLC.LOOKUP_CODE(+) = DECODE(PRL.SOURCE_TYPE_CODE
, 'VENDOR'
, 'PURCHASE'
, 'INTERNAL')

Columns