VIEW: APPS.JAI_MASSTAXCHG_DOCNOS_V

Source

View Text - Preformatted

SELECT DISTINCT DOCUMENT_NO, TRUNC(CREATION_DATE) CREATION_DATE,DOCUMENT_TYPE,ORG_ID FROM (SELECT PHA.SEGMENT1 DOCUMENT_NO, PLLA.CREATION_DATE CREATION_DATE, PHA.TYPE_LOOKUP_CODE DOCUMENT_TYPE, PHA.ORG_ID ORG_ID FROM PO_HEADERS_ALL PHA, PO_LINE_LOCATIONS_ALL PLLA WHERE (PLLA.CANCEL_FLAG IS NULL OR PLLA.CANCEL_FLAG <> 'Y') AND (PLLA.CLOSED_CODE IS NULL OR PLLA.CLOSED_CODE IN('OPEN', 'IN PROCESS', 'APPROVED', 'PRE-APPROVED', 'REQUIRES REAPPROVAL', 'INCOMPLETE')) AND PHA.TYPE_LOOKUP_CODE IN ('STANDARD','PLANNED','BLANKET','QUOTATION','RFQ') AND PLLA.PO_HEADER_ID = PHA.PO_HEADER_ID UNION SELECT PHA.SEGMENT1 DOCUMENT_NO, PLA.CREATION_DATE, PHA.TYPE_LOOKUP_CODE, PHA.ORG_ID FROM PO_HEADERS_ALL PHA, PO_LINES_ALL PLA WHERE (PLA.CANCEL_FLAG IS NULL OR PLA.CANCEL_FLAG <> 'Y') AND (PLA.CLOSED_CODE IS NULL OR PLA.CLOSED_CODE IN ('OPEN', 'IN PROCESS', 'APPROVED', 'PRE-APPROVED', 'REQUIRES REAPPROVAL', 'INCOMPLETE')) AND PLA.PO_HEADER_ID = PHA.PO_HEADER_ID AND PHA.TYPE_LOOKUP_CODE IN ('STANDARD','PLANNED','BLANKET','QUOTATION','RFQ') AND NOT EXISTS (SELECT 1 FROM PO_LINE_LOCATIONS_ALL PLLA WHERE PLLA.PO_HEADER_ID = PHA.PO_HEADER_ID) UNION SELECT PRHA.SEGMENT1 DOCUMENT_NO, PRLA.CREATION_DATE, PRHA.TYPE_LOOKUP_CODE, PRHA.ORG_ID FROM PO_REQUISITION_HEADERS_ALL PRHA, PO_REQUISITION_LINES_ALL PRLA WHERE PRLA.REQUISITION_HEADER_ID = PRHA.REQUISITION_HEADER_ID AND (PRLA.CANCEL_FLAG IS NULL OR PRLA.CANCEL_FLAG <> 'Y') AND (PRLA.CLOSED_CODE IS NULL OR PRLA.CLOSED_CODE IN ('OPEN', 'IN PROCESS', 'APPROVED', 'PRE-APPROVED', 'REQUIRES REAPPROVAL', 'INCOMPLETE')) UNION SELECT TO_CHAR(HEAD.BOE_ID) DOCUMENT_NO, HEAD.CREATION_DATE, 'BOE', HEAD.ORG_ID FROM JAI_CMN_BOE_HDRS HEAD WHERE STATUS IN ('NOT_YET_ASSESSED') UNION SELECT TO_CHAR(OOHA.ORDER_NUMBER) DOCUMENT_NO, NVL(OOHA.ORDERED_DATE,OOHA.CREATION_DATE),'SALES_ORDERS',OOHA.ORG_ID FROM OE_ORDER_HEADERS_ALL OOHA, OE_ORDER_LINES_ALL OOLA WHERE OOLA.HEADER_ID = OOHA.HEADER_ID AND OOLA.OPEN_FLAG = 'Y' AND (OOHA.CANCELLED_FLAG IS NULL OR OOHA.CANCELLED_FLAG <> 'Y') AND (OOLA.CANCELLED_QUANTITY IS NULL OR OOLA.CANCELLED_QUANTITY = 0))

View Text - HTML Formatted

SELECT DISTINCT DOCUMENT_NO
, TRUNC(CREATION_DATE) CREATION_DATE
, DOCUMENT_TYPE
, ORG_ID
FROM (SELECT PHA.SEGMENT1 DOCUMENT_NO
, PLLA.CREATION_DATE CREATION_DATE
, PHA.TYPE_LOOKUP_CODE DOCUMENT_TYPE
, PHA.ORG_ID ORG_ID
FROM PO_HEADERS_ALL PHA
, PO_LINE_LOCATIONS_ALL PLLA
WHERE (PLLA.CANCEL_FLAG IS NULL OR PLLA.CANCEL_FLAG <> 'Y')
AND (PLLA.CLOSED_CODE IS NULL OR PLLA.CLOSED_CODE IN('OPEN'
, 'IN PROCESS'
, 'APPROVED'
, 'PRE-APPROVED'
, 'REQUIRES REAPPROVAL'
, 'INCOMPLETE'))
AND PHA.TYPE_LOOKUP_CODE IN ('STANDARD'
, 'PLANNED'
, 'BLANKET'
, 'QUOTATION'
, 'RFQ')
AND PLLA.PO_HEADER_ID = PHA.PO_HEADER_ID UNION SELECT PHA.SEGMENT1 DOCUMENT_NO
, PLA.CREATION_DATE
, PHA.TYPE_LOOKUP_CODE
, PHA.ORG_ID
FROM PO_HEADERS_ALL PHA
, PO_LINES_ALL PLA
WHERE (PLA.CANCEL_FLAG IS NULL OR PLA.CANCEL_FLAG <> 'Y')
AND (PLA.CLOSED_CODE IS NULL OR PLA.CLOSED_CODE IN ('OPEN'
, 'IN PROCESS'
, 'APPROVED'
, 'PRE-APPROVED'
, 'REQUIRES REAPPROVAL'
, 'INCOMPLETE'))
AND PLA.PO_HEADER_ID = PHA.PO_HEADER_ID
AND PHA.TYPE_LOOKUP_CODE IN ('STANDARD'
, 'PLANNED'
, 'BLANKET'
, 'QUOTATION'
, 'RFQ')
AND NOT EXISTS (SELECT 1
FROM PO_LINE_LOCATIONS_ALL PLLA
WHERE PLLA.PO_HEADER_ID = PHA.PO_HEADER_ID) UNION SELECT PRHA.SEGMENT1 DOCUMENT_NO
, PRLA.CREATION_DATE
, PRHA.TYPE_LOOKUP_CODE
, PRHA.ORG_ID
FROM PO_REQUISITION_HEADERS_ALL PRHA
, PO_REQUISITION_LINES_ALL PRLA
WHERE PRLA.REQUISITION_HEADER_ID = PRHA.REQUISITION_HEADER_ID
AND (PRLA.CANCEL_FLAG IS NULL OR PRLA.CANCEL_FLAG <> 'Y')
AND (PRLA.CLOSED_CODE IS NULL OR PRLA.CLOSED_CODE IN ('OPEN'
, 'IN PROCESS'
, 'APPROVED'
, 'PRE-APPROVED'
, 'REQUIRES REAPPROVAL'
, 'INCOMPLETE')) UNION SELECT TO_CHAR(HEAD.BOE_ID) DOCUMENT_NO
, HEAD.CREATION_DATE
, 'BOE'
, HEAD.ORG_ID
FROM JAI_CMN_BOE_HDRS HEAD
WHERE STATUS IN ('NOT_YET_ASSESSED') UNION SELECT TO_CHAR(OOHA.ORDER_NUMBER) DOCUMENT_NO
, NVL(OOHA.ORDERED_DATE
, OOHA.CREATION_DATE)
, 'SALES_ORDERS'
, OOHA.ORG_ID
FROM OE_ORDER_HEADERS_ALL OOHA
, OE_ORDER_LINES_ALL OOLA
WHERE OOLA.HEADER_ID = OOHA.HEADER_ID
AND OOLA.OPEN_FLAG = 'Y'
AND (OOHA.CANCELLED_FLAG IS NULL OR OOHA.CANCELLED_FLAG <> 'Y')
AND (OOLA.CANCELLED_QUANTITY IS NULL OR OOLA.CANCELLED_QUANTITY = 0))