[E-Bussiness Suite] Query Invoice with Accounting by Date

Deskripsi
     Query Invoice with Accounting (Debit,Credit,COA,Liabilities) plus additional display related PR , PO , RR , Invoice , Voucher Payment (STATUS : Negotiable and Reconciled Unaccounted) search by Invoice GL_DATE.

Howto
1. Execute Show View Hidden Here , because we need some oracle view.
2. Run Query
select *
from
(
select distinct PRHA.segment1 as PR
                    , PHA.segment1 as PO
                    , pv.VENDOR_NAME as Supplier
                    ,RVHV.RECEIPT_NUM as RECEIPT_NUMBER
                   ,AI.INVOICE_NUM as INVOICE
                   ,AC.DOC_SEQUENCE_VALUE as VOUCHER_PAYMENT
                   --,AID.PA_ADDITION_FLAG
                   --,AID.PROJECT_ACCOUNTING_CONTEXT
                  ,AID.DISTRIBUTION_LINE_NUMBER as DIST_LINE_NUM
                  ,AID.DESCRIPTION
                  ,AID.AMOUNT
                  ,AELA.AE_LINE_TYPE_CODE
                  ,GCC.SEGMENT1 AS ACCOUNT
                  ,AELA.ACCOUNTED_DR
                  ,AELA.ACCOUNTED_CR
     
from ap_invoices_all ai
    ,AP_INVOICE_PAYMENTS_V AIP
    ,AP_CHECKS_all AC
    ,ap_invoice_distributions AID
    ,po_distributions PD
    ,po_headers_all PHA
    ,po_line_locations_all PLLA
    ,po_lines_all PLA
    ,po_requisition_headers_all PRHA
    ,po_requisition_lines_all PRLA
    ,RCV_VRC_HDS_V RVHV
    ,RCV_SHIPMENT_LINES RSL
    ,ap_ae_lines_all aela
    ,gl_code_combinations GCC
    ,po_vendors PV
 
where ai.gl_date >= to_date('01/05/2013','dd/mm/yyyy')
and ai.gl_date <= to_date('02/05/2013','dd/mm/yyyy')
and ai.org_id = '0'
AND AI.INVOICE_ID = AIP.INVOICE_ID(+)
AND AIP.CHECK_ID = AC.CHECK_ID(+)
AND AI.INVOICE_ID = AID.INVOICE_ID(+)
AND AID.po_distribution_id = PD.po_distribution_id(+)
AND PD.po_header_id = PHA.po_header_id(+)
and PHA.po_header_id = PLA.po_header_id(+)
and PLA.po_line_id = plla.po_line_id(+)
and prha.requisition_header_id(+) = PRLA.requisition_header_id
and PRLA.line_location_id(+) = PLLA.line_location_id
AND PHA.po_header_id = RSL.po_header_id (+)
AND RSL.shipment_header_id = RVHV.shipment_header_id(+)
AND AELA.SOURCE_ID = AID.INVOICE_DISTRIBUTION_ID
AND GCC.CODE_COMBINATION_ID = AELA.CODE_COMBINATION_ID
AND PHA.VENDOR_ID = PV.VENDOR_ID(+)
AND (AC.STATUS_LOOKUP_CODE = 'RECONCILED UNACCOUNTED' OR AC.STATUS_LOOKUP_CODE = 'NEGOTIABLE')
--AND AID.TAX_RECOVERABLE_FLAG = 'N'
--AND AID.LINE_TYPE_LOOKUP_CODE <> 'TAX'
--AND AID.FINAL_MATCH_FLAG is null
--AND AID.DISTRIBUTION_LINE_NUMBER = '2'
UNION ALL
select distinct NULL as PR
               ,NULL as PO
               ,NULL as Supplier
               ,NULL as RECEIPT_NUMBER
               ,ai.INVOICE_NUM as INVOICE
               ,NULL as VOUCHER_PAYMENT
               ,AELA.AE_LINE_NUMBER as DIST_LINE_NUM
               ,NULL
               ,NULL
               ,AELA.AE_LINE_TYPE_CODE
               ,GCC.SEGMENT1 AS ACCOUNT
               ,AELA.ACCOUNTED_DR
               ,AELA.ACCOUNTED_CR
             
from AP_INVOICES_ALL AI
    ,AP_AE_LINES_ALL AELA
    ,gl_code_combinations GCC
 
where ai.gl_date >= to_date('01/05/2013','dd/mm/yyyy')
and ai.gl_date <= to_date('02/05/2013','dd/mm/yyyy')
and aela.SOURCE_ID = ai.INVOICE_ID(+)
and ae_line_type_code = 'LIABILITY'
and aela.ACCOUNTED_CR is not null
AND GCC.CODE_COMBINATION_ID = AELA.CODE_COMBINATION_ID
) data

order by 5,7