Product: | PO - Purchasing |
---|---|
Description: | 10SC ONLY - Retrofitted |
Implementation/DBA Data: | APPS.PO_REQUISITION_LINES_V |
SELECT PRL.ROWID
, PRL.REQUISITION_HEADER_ID
, PRL.REQUISITION_LINE_ID
, PRL.LINE_NUM
, PLT.LINE_TYPE
, PLT.LINE_TYPE_ID
, PRL.ITEM_ID
, PRL.ITEM_REVISION
, PRL.CATEGORY_ID
, PRL.ITEM_DESCRIPTION
, PRL.QUANTITY
, PRL.QUANTITY_RECEIVED
, PRL.QUANTITY_DELIVERED
, PRL.QUANTITY_CANCELLED
, PRL.UNIT_MEAS_LOOKUP_CODE
, PRL.UNIT_PRICE
, PRL.NEED_BY_DATE
, PPF.FULL_NAME
, PRL.NOTE_TO_AGENT
, PRL.SUGGESTED_VENDOR_NAME
, PRL.SUGGESTED_VENDOR_LOCATION
, PRL.SUGGESTED_VENDOR_CONTACT
, PRL.SUGGESTED_VENDOR_PHONE
, PRL.RFQ_REQUIRED_FLAG
, PRL.ON_RFQ_FLAG
, PRL.CANCEL_FLAG
, PRL.MODIFIED_BY_AGENT_FLAG
, POUN.UN_NUMBER
, PHC.HAZARD_CLASS
, PLC1.DISPLAYED_FIELD
, PRL.REFERENCE_NUM
, PRL.URGENT_FLAG
, PRL.JUSTIFICATION
, PRL.NOTE_TO_RECEIVER
, PRL.ENCUMBERED_FLAG
, PRL.DESTINATION_TYPE_CODE
, PRL.SOURCE_TYPE_CODE
, PAPF2.FULL_NAME
, HOU2.NAME
, HRL1.LOCATION_CODE
, PRL.DESTINATION_SUBINVENTORY
, HOUT.NAME
, PRL.SOURCE_SUBINVENTORY
, PRL.ATTRIBUTE1
, PRL.ATTRIBUTE2
, PRL.ATTRIBUTE3
, PRL.ATTRIBUTE4
, PRL.ATTRIBUTE5
, PRL.ATTRIBUTE6
, PRL.ATTRIBUTE7
, PRL.ATTRIBUTE8
, PRL.ATTRIBUTE9
, PRL.ATTRIBUTE10
, PRL.ATTRIBUTE11
, PRL.ATTRIBUTE12
, PRL.ATTRIBUTE13
, PRL.ATTRIBUTE14
, PRL.ATTRIBUTE15
, PRL.ATTRIBUTE_CATEGORY
, NVL(PLT.OUTSIDE_OPERATION_FLAG
, 'N')
, PRL.DELIVER_TO_LOCATION_ID
, PRL.TO_PERSON_ID
, PRL.LAST_UPDATE_DATE
, PRL.LAST_UPDATED_BY
, PRL.LAST_UPDATE_LOGIN
, PRL.CREATION_DATE
, PRL.CREATED_BY
, PRL.SUGGESTED_VENDOR_PRODUCT_CODE
, PRL.UN_NUMBER_ID
, PRL.HAZARD_CLASS_ID
, PRL.CURRENCY_CODE
, PRL.RATE
, PRL.RATE_TYPE
, PRL.RATE_DATE
, PRL.CURRENCY_UNIT_PRICE
, PRL.SUGGESTED_BUYER_ID
, PRL.CLOSED_CODE
, PRL.LINE_LOCATION_ID
, PRL.PARENT_REQ_LINE_ID
, PRL.PURCHASING_AGENT_ID
, PRL.DOCUMENT_TYPE_CODE
, PRL.BLANKET_PO_HEADER_ID
, PRL.BLANKET_PO_LINE_NUM
, PRL.CANCEL_REASON
, PRL.CANCEL_DATE
, PRL.AGENT_RETURN_NOTE
, PRL.CHANGED_AFTER_RESEARCH_FLAG
, PRL.VENDOR_ID
, PRL.VENDOR_SITE_ID
, PRL.VENDOR_CONTACT_ID
, PRL.RESEARCH_AGENT_ID
, PRL.ON_LINE_FLAG
, PRL.WIP_ENTITY_ID
, PRL.WIP_LINE_ID
, PRL.WIP_REPETITIVE_SCHEDULE_ID
, PRL.WIP_OPERATION_SEQ_NUM
, PRL.WIP_RESOURCE_SEQ_NUM
, PRL.DESTINATION_CONTEXT
, PRL.INVENTORY_SOURCE_CONTEXT
, PRL.VENDOR_SOURCE_CONTEXT
, PRL.BOM_RESOURCE_ID
, PRL.USSGL_TRANSACTION_CODE
, PRL.GOVERNMENT_CONTEXT
, PRL.CLOSED_REASON
, PRL.CLOSED_DATE
, PRL.TRANSACTION_REASON_CODE
, PRL.SOURCE_REQ_LINE_ID
, PRL.DESTINATION_ORGANIZATION_ID
, PRL.SOURCE_ORGANIZATION_ID
, PLT.ORDER_TYPE_LOOKUP_CODE
, GDT.USER_CONVERSION_TYPE
, PLC2.DISPLAYED_FIELD
, POLA.CUSTOMER_ID
, POLA.ADDRESS_ID
, POLA.SITE_USE_ID
, PLC3.DISPLAYED_FIELD
, PLC4.DISPLAYED_FIELD
, MSI.INVENTORY_ASSET_FLAG
, MSI.INTERNAL_ORDER_ENABLED_FLAG
, MSI.OUTSIDE_OPERATION_UOM_TYPE
, MSI.ALLOW_ITEM_DESC_UPDATE_FLAG
, MSI.ALLOWED_UNITS_LOOKUP_CODE
, MSI.PURCHASING_ENABLED_FLAG
, DECODE(MSI.INVENTORY_PLANNING_CODE
, NULL
, 'N'
, '6'
, 'N'
, 'Y')
, DECODE(MSI.MRP_PLANNING_CODE
, NULL
, 'N'
, '6'
, 'N'
, 'Y')
, MSIV.ASSET_INVENTORY
, POH.SEGMENT1
, PO_REQ_DIST_SV1.GET_DIST_ACCOUNT(PRL.REQUISITION_LINE_ID)
, MP.ORGANIZATION_CODE
, ATC.NAME
, PRL.TAX_USER_OVERRIDE_FLAG
, PRL.TAX_CODE_ID
, PRL.OKE_CONTRACT_HEADER_ID
, PRL.OKE_CONTRACT_VERSION_ID
, PRL.ITEM_SOURCE_ID
, PRL.SUPPLIER_REF_NUMBER
, PRL.SECONDARY_UNIT_OF_MEASURE
, PRL.SECONDARY_QUANTITY
, PRL.PREFERRED_GRADE
, PRL.SECONDARY_QUANTITY_RECEIVED
, PRL.SECONDARY_QUANTITY_CANCELLED
, PRL.AUCTION_HEADER_ID
, PRL.AUCTION_DISPLAY_NUMBER
, PRL.AUCTION_LINE_NUMBER
, PRL.REQS_IN_POOL_FLAG
, PRL.AMOUNT
, PRL.CURRENCY_AMOUNT
, PLT.PURCHASE_BASIS
, PRL.DROP_SHIP_FLAG
, PRL.SUGGESTED_VENDOR_CONTACT_FAX
, PRL.SUGGESTED_VENDOR_CONTACT_EMAIL
, PLT.MATCHING_BASIS
, PRL.BASE_UNIT_PRICE
, PRL.NEGOTIATED_BY_PREPARER_FLAG
, DECODE(MSI.TRACKING_QUANTITY_IND
, 'PS'
, MSI.SECONDARY_DEFAULT_IND
, NULL)
, MSI.GRADE_CONTROL_FLAG
, PRL.AT_SOURCING_FLAG
, PRL.ORG_ID
FROM PO_LINE_TYPES PLT
, PER_PEOPLE_F PPF
, PO_UN_NUMBERS POUN
, PO_HAZARD_CLASSES PHC
, PO_LOOKUP_CODES PLC1
, PO_LOOKUP_CODES PLC2
, PO_LOOKUP_CODES PLC3
, PO_LOOKUP_CODES PLC4
, MTL_PARAMETERS MP
, PER_ALL_PEOPLE_F PAPF2
, HR_LOCATIONS_ALL_TL HRL1
, HR_ORGANIZATION_UNITS HOU2
, HR_ORG_UNITS_NO_JOIN HOU
, HR_ALL_ORGANIZATION_UNITS_TL HOUT
, MTL_SECONDARY_INVENTORIES MSIV
, MTL_SYSTEM_ITEMS MSI
, PO_LOCATION_ASSOCIATIONS POLA
, GL_DAILY_CONVERSION_TYPES GDT
, AP_TAX_CODES_ALL ATC
, PO_HEADERS_ALL POH
, PO_REQUISITION_LINES PRL
WHERE PLT.LINE_TYPE_ID = PRL.LINE_TYPE_ID
AND PPF.PERSON_ID (+) = PRL.SUGGESTED_BUYER_ID
AND POUN.UN_NUMBER_ID (+) = PRL.UN_NUMBER_ID
AND PHC.HAZARD_CLASS_ID (+) = PRL.HAZARD_CLASS_ID
AND PLC1.LOOKUP_TYPE (+) = 'TRANSACTION REASON'
AND PLC1.LOOKUP_CODE (+) = PRL.TRANSACTION_REASON_CODE
AND PLC2.LOOKUP_CODE (+) = PRL.DOCUMENT_TYPE_CODE
AND PLC2.LOOKUP_TYPE (+) = 'SOURCE DOCUMENT TYPE'
AND PLC3.LOOKUP_CODE = PRL.SOURCE_TYPE_CODE
AND PLC3.LOOKUP_TYPE = 'REQUISITION SOURCE TYPE'
AND PLC4.LOOKUP_CODE = PRL.DESTINATION_TYPE_CODE
AND PLC4.LOOKUP_TYPE = 'DESTINATION TYPE'
AND PAPF2.PERSON_ID = PRL.TO_PERSON_ID
AND TRUNC(SYSDATE) BETWEEN PAPF2.EFFECTIVE_START_DATE
AND PAPF2.EFFECTIVE_END_DATE
AND DECODE(HR_SECURITY.VIEW_ALL
, 'Y'
, 'TRUE'
, HR_SECURITY.SHOW_RECORD('PER_ALL_PEOPLE_F'
, PAPF2.PERSON_ID
, PAPF2.PERSON_TYPE_ID
, PAPF2.EMPLOYEE_NUMBER
, PAPF2.APPLICANT_NUMBER)) = 'TRUE'
AND DECODE(HR_GENERAL.GET_XBG_PROFILE
, 'Y'
, PAPF2.BUSINESS_GROUP_ID
, HR_GENERAL.GET_BUSINESS_GROUP_ID) = PAPF2.BUSINESS_GROUP_ID
AND HOU2.ORGANIZATION_ID = PRL.DESTINATION_ORGANIZATION_ID
AND HRL1.LOCATION_ID(+) = PRL.DELIVER_TO_LOCATION_ID
AND HRL1.LANGUAGE(+) = USERENV('LANG')
AND MSI.INVENTORY_ITEM_ID (+) = PRL.ITEM_ID
AND MSI.ORGANIZATION_ID (+) = PRL.DESTINATION_ORGANIZATION_ID
AND POLA.LOCATION_ID (+) = PRL.DELIVER_TO_LOCATION_ID
AND GDT.CONVERSION_TYPE (+) = PRL.RATE_TYPE
AND HOU.ORGANIZATION_ID (+) = PRL.SOURCE_ORGANIZATION_ID
AND (((PPF.EMPLOYEE_NUMBER IS NOT NULL OR PPF.NPW_NUMBER IS NOT NULL)
AND TRUNC(SYSDATE) BETWEEN PPF.EFFECTIVE_START_DATE
AND PPF.EFFECTIVE_END_DATE) OR PPF.PERSON_ID IS NULL)
AND MSIV.ORGANIZATION_ID (+) = PRL.SOURCE_ORGANIZATION_ID
AND MSIV.SECONDARY_INVENTORY_NAME (+) = PRL.SOURCE_SUBINVENTORY
AND POH.PO_HEADER_ID (+) = PRL.BLANKET_PO_HEADER_ID
AND MP.ORGANIZATION_ID = PRL.DESTINATION_ORGANIZATION_ID
AND PRL.TAX_CODE_ID = ATC.TAX_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'))