VIEW: APPS.PO_REQ_LINES_IN_POOL_SRC_V

Source

View Text - Preformatted

SELECT PRH.REQUISITION_HEADER_ID ,PRH.SEGMENT1 ,PRL.REQUISITION_LINE_ID ,PRL.LINE_NUM ,PRL.QUANTITY ,PRL.NEED_BY_DATE ,PRL.CREATION_DATE ,PRL.MODIFIED_BY_AGENT_FLAG ,PRL.AUCTION_HEADER_ID ,PRL.AUCTION_LINE_NUMBER ,PRH.ORG_ID ,DECODE(TO_CHAR(COUNT(DISTINCT(PRD.PROJECT_ID))),'1', nvl(TO_CHAR(MAX(PAP.SEGMENT1)),TO_CHAR(MAX(PSN.PROJECT_NUMBER ))),'0',TO_CHAR(NULL),'MULTIPLE') ,DECODE(TO_CHAR(COUNT(DISTINCT(PRD.TASK_ID))),'1',TO_CHAR(MAX(PAT.TASK_NUMBER)),'0',TO_CHAR(NULL),'MULTIPLE') FROM PO_REQUISITION_HEADERS_ALL PRH, PO_REQUISITION_LINES_ALL PRL, PO_REQ_DISTRIBUTIONS_ALL PRD, PA_PROJECTS_ALL PAP, PA_TASKS PAT, PJM_SEIBAN_NUMBERS PSN WHERE PRH.REQUISITION_HEADER_ID=PRL.REQUISITION_HEADER_ID AND PRL.REQUISITION_LINE_ID =PRD.REQUISITION_LINE_ID (+) AND PRL.LINE_LOCATION_ID IS NULL AND NVL(PRL.CANCEL_FLAG,'N') = 'N' AND NVL(PRL.CLOSED_CODE,'OPEN') <>'FINALLY CLOSED' AND NVL(PRL.MODIFIED_BY_AGENT_FLAG,'N')='N' AND PRL.SOURCE_TYPE_CODE='VENDOR' AND PRD.PROJECT_ID=PAP.PROJECT_ID(+) AND PRD.PROJECT_ID=PSN.PROJECT_ID(+) AND PRD.TASK_ID=PAT.TASK_ID(+) GROUP BY PRH.REQUISITION_HEADER_ID ,PRH.SEGMENT1 ,PRL.REQUISITION_LINE_ID ,PRL.LINE_NUM ,PRL.QUANTITY ,PRL.NEED_BY_DATE ,PRL.CREATION_DATE ,PRL.MODIFIED_BY_AGENT_FLAG ,PRH.ORG_ID ,PRL.AUCTION_HEADER_ID ,PRL.AUCTION_LINE_NUMBER

View Text - HTML Formatted

SELECT PRH.REQUISITION_HEADER_ID
, PRH.SEGMENT1
, PRL.REQUISITION_LINE_ID
, PRL.LINE_NUM
, PRL.QUANTITY
, PRL.NEED_BY_DATE
, PRL.CREATION_DATE
, PRL.MODIFIED_BY_AGENT_FLAG
, PRL.AUCTION_HEADER_ID
, PRL.AUCTION_LINE_NUMBER
, PRH.ORG_ID
, DECODE(TO_CHAR(COUNT(DISTINCT(PRD.PROJECT_ID)))
, '1'
, NVL(TO_CHAR(MAX(PAP.SEGMENT1))
, TO_CHAR(MAX(PSN.PROJECT_NUMBER )))
, '0'
, TO_CHAR(NULL)
, 'MULTIPLE')
, DECODE(TO_CHAR(COUNT(DISTINCT(PRD.TASK_ID)))
, '1'
, TO_CHAR(MAX(PAT.TASK_NUMBER))
, '0'
, TO_CHAR(NULL)
, 'MULTIPLE')
FROM PO_REQUISITION_HEADERS_ALL PRH
, PO_REQUISITION_LINES_ALL PRL
, PO_REQ_DISTRIBUTIONS_ALL PRD
, PA_PROJECTS_ALL PAP
, PA_TASKS PAT
, PJM_SEIBAN_NUMBERS PSN
WHERE PRH.REQUISITION_HEADER_ID=PRL.REQUISITION_HEADER_ID
AND PRL.REQUISITION_LINE_ID =PRD.REQUISITION_LINE_ID (+)
AND PRL.LINE_LOCATION_ID IS NULL
AND NVL(PRL.CANCEL_FLAG
, 'N') = 'N'
AND NVL(PRL.CLOSED_CODE
, 'OPEN') <>'FINALLY CLOSED'
AND NVL(PRL.MODIFIED_BY_AGENT_FLAG
, 'N')='N'
AND PRL.SOURCE_TYPE_CODE='VENDOR'
AND PRD.PROJECT_ID=PAP.PROJECT_ID(+)
AND PRD.PROJECT_ID=PSN.PROJECT_ID(+)
AND PRD.TASK_ID=PAT.TASK_ID(+) GROUP BY PRH.REQUISITION_HEADER_ID
, PRH.SEGMENT1
, PRL.REQUISITION_LINE_ID
, PRL.LINE_NUM
, PRL.QUANTITY
, PRL.NEED_BY_DATE
, PRL.CREATION_DATE
, PRL.MODIFIED_BY_AGENT_FLAG
, PRH.ORG_ID
, PRL.AUCTION_HEADER_ID
, PRL.AUCTION_LINE_NUMBER