DBA Data[Home] [Help]

VIEW: APPS.CE_PO_FC_REQUISITIONS_V

Source

View Text - Preformatted

SELECT PRH.SEGMENT1, /* 1 */ DECODE(PRL.MATCHING_BASIS, 'AMOUNT', (NVL(PRD.REQ_LINE_AMOUNT,0)), (NVL(PRL.UNIT_PRICE,0) * PRD.REQ_LINE_QUANTITY)), /* 2 */ Decode(prl.matching_basis,'AMOUNT',(Nvl(prd.req_line_currency_amount,0) + Nvl(prd.recoverable_tax,0) + Nvl(prd.nonrecoverable_tax,0)), ((Nvl(prl.currency_unit_price,prl.unit_price) * prd.req_line_quantity + Nvl(prd.recoverable_tax,0) + Nvl(prd.nonrecoverable_tax,0)))), /* 3 */ NVL(NVL(PRL.CURRENCY_CODE,FSP.INVOICE_CURRENCY_CODE),SOB.CURRENCY_CODE), /* 4 */ NVL(PRL.NEED_BY_DATE, PRL.CREATION_DATE), /* 5 */ NVL(PRH.AUTHORIZATION_STATUS,'INCOMPLETE'), /* 6 */ PRL.ORG_ID /* 7 */ , PRD.PROJECT_ID, /* 8 */ PRL.LINE_NUM, /* 9 */ PRD.DISTRIBUTION_ID, /* 10 */ PRL.ORDER_TYPE_LOOKUP_CODE, /* 11 */ PRL.PURCHASE_BASIS, /* 12 */ PRL.MATCHING_BASIS /* 13 */ FROM PO_DOCUMENT_TYPES_ALL PDT, PO_REQ_DISTRIBUTIONS_ALL PRD, PO_REQUISITION_LINES_ALL PRL, PO_REQUISITION_HEADERS_ALL PRH, FINANCIALS_SYSTEM_PARAMS_ALL FSP, GL_SETS_OF_BOOKS SOB WHERE FSP.SET_OF_BOOKS_ID = PRD.SET_OF_BOOKS_ID AND (FSP.ORG_ID = PRD.ORG_ID OR FSP.ORG_ID IS NULL) AND SOB.SET_OF_BOOKS_ID = PRD.SET_OF_BOOKS_ID AND PRH.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_ID AND (PRH.ORG_ID = PRL.ORG_ID OR PRH.ORG_ID IS NULL) AND PRL.REQUISITION_LINE_ID = PRD.REQUISITION_LINE_ID AND (PRH.ORG_ID = PDT.ORG_ID OR PRH.ORG_ID IS NULL) AND PRH.TYPE_LOOKUP_CODE = 'PURCHASE' AND PRH.TYPE_LOOKUP_CODE = PDT.DOCUMENT_SUBTYPE AND PDT.DOCUMENT_TYPE_CODE = 'REQUISITION' AND PRH.AUTHORIZATION_STATUS NOT IN ('CANCELLED','REJECTED') AND NVL(PRL.CLOSED_CODE,'APPROVED') NOT IN ('CANCELLED','FROZEN', 'FINALLY CLOSED') AND PRL.SOURCE_TYPE_CODE = 'VENDOR' AND PRL.LINE_LOCATION_ID IS NULL AND NVL(PRL.MODIFIED_BY_AGENT_FLAG,'N') = 'N' AND NVL(PRL.CANCEL_FLAG,'N') = 'N' AND NVL(PRL.PURCHASE_BASIS, 'GOODS') NOT IN ('TEMP LABOR')
View Text - HTML Formatted

SELECT PRH.SEGMENT1
, /* 1 */ DECODE(PRL.MATCHING_BASIS
, 'AMOUNT'
, (NVL(PRD.REQ_LINE_AMOUNT
, 0))
, (NVL(PRL.UNIT_PRICE
, 0) * PRD.REQ_LINE_QUANTITY))
, /* 2 */ DECODE(PRL.MATCHING_BASIS
, 'AMOUNT'
, (NVL(PRD.REQ_LINE_CURRENCY_AMOUNT
, 0) + NVL(PRD.RECOVERABLE_TAX
, 0) + NVL(PRD.NONRECOVERABLE_TAX
, 0))
, ((NVL(PRL.CURRENCY_UNIT_PRICE
, PRL.UNIT_PRICE) * PRD.REQ_LINE_QUANTITY + NVL(PRD.RECOVERABLE_TAX
, 0) + NVL(PRD.NONRECOVERABLE_TAX
, 0))))
, /* 3 */ NVL(NVL(PRL.CURRENCY_CODE
, FSP.INVOICE_CURRENCY_CODE)
, SOB.CURRENCY_CODE)
, /* 4 */ NVL(PRL.NEED_BY_DATE
, PRL.CREATION_DATE)
, /* 5 */ NVL(PRH.AUTHORIZATION_STATUS
, 'INCOMPLETE')
, /* 6 */ PRL.ORG_ID /* 7 */
, PRD.PROJECT_ID
, /* 8 */ PRL.LINE_NUM
, /* 9 */ PRD.DISTRIBUTION_ID
, /* 10 */ PRL.ORDER_TYPE_LOOKUP_CODE
, /* 11 */ PRL.PURCHASE_BASIS
, /* 12 */ PRL.MATCHING_BASIS /* 13 */
FROM PO_DOCUMENT_TYPES_ALL PDT
, PO_REQ_DISTRIBUTIONS_ALL PRD
, PO_REQUISITION_LINES_ALL PRL
, PO_REQUISITION_HEADERS_ALL PRH
, FINANCIALS_SYSTEM_PARAMS_ALL FSP
, GL_SETS_OF_BOOKS SOB
WHERE FSP.SET_OF_BOOKS_ID = PRD.SET_OF_BOOKS_ID
AND (FSP.ORG_ID = PRD.ORG_ID OR FSP.ORG_ID IS NULL)
AND SOB.SET_OF_BOOKS_ID = PRD.SET_OF_BOOKS_ID
AND PRH.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_ID
AND (PRH.ORG_ID = PRL.ORG_ID OR PRH.ORG_ID IS NULL)
AND PRL.REQUISITION_LINE_ID = PRD.REQUISITION_LINE_ID
AND (PRH.ORG_ID = PDT.ORG_ID OR PRH.ORG_ID IS NULL)
AND PRH.TYPE_LOOKUP_CODE = 'PURCHASE'
AND PRH.TYPE_LOOKUP_CODE = PDT.DOCUMENT_SUBTYPE
AND PDT.DOCUMENT_TYPE_CODE = 'REQUISITION'
AND PRH.AUTHORIZATION_STATUS NOT IN ('CANCELLED'
, 'REJECTED')
AND NVL(PRL.CLOSED_CODE
, 'APPROVED') NOT IN ('CANCELLED'
, 'FROZEN'
, 'FINALLY CLOSED')
AND PRL.SOURCE_TYPE_CODE = 'VENDOR'
AND PRL.LINE_LOCATION_ID IS NULL
AND NVL(PRL.MODIFIED_BY_AGENT_FLAG
, 'N') = 'N'
AND NVL(PRL.CANCEL_FLAG
, 'N') = 'N'
AND NVL(PRL.PURCHASE_BASIS
, 'GOODS') NOT IN ('TEMP LABOR')