VIEW: APPS.PO_REQUISITION_TEMPLATES_V

Source

View Text - Preformatted

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'

View Text - HTML Formatted

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'