View: JAI_MASSTAXCHG_DOCNOS_V

Product:	JA - Asia/Pacific Localizations
Description:
Implementation/DBA Data:	APPS.JAI_MASSTAXCHG_DOCNOS_V

View Text

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))

Columns