[E-Bussiness Suite] Query Invoice - Line Distribution with Accounting

Deskripsi
     Untuk menampilkan data PR - PO - RR - Invoice - Payment dan difilter bedasarkan GL Invoice serta ditambah data tambahan dari invoice seperti line_distribution , amount , debit , credit ,  Chart of Account tapi tidak menampilkan 'Liabilities'

Howto
1. Activate Show View Hidden
2. Query this sql below

select distinct PRHA.segment1 as PR
              , PHA.segment1 as PO
              ,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
              ,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
    
-- change your date here , or you can create input     
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 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'
order by AI.INVOICE_NUM,PHA.segment1,RVHV.RECEIPT_NUM,AID.DISTRIBUTION_LINE_NUMBER;