Supplier Balances Report

QUERY1
-----------------
select
hou.name             OU_NAME,
aps.segment1          VENDOR_CODE,
aps.vendor_name      VENDOR_NAME,
apss.vendor_site_code  SITE_CODE,
hou.organization_id org_id,
apss.vendor_site_id,
gcc.segment3||'-'||gcc.segment4 Account
from ap_suppliers aps
, ap_supplier_sites_all apss
, hr_operating_units hou
,gl_code_combinations gcc
where aps.vendor_id=apss.vendor_id
and apss.org_id=hou.organization_id
and apss.vendor_site_code=nvl(:p_vendor_site_code,apss.vendor_site_code)
and aps.vendor_name between nvl(:p_from_vendor_name,aps.vendor_name) and nvl(:p_to_vendor_name,aps.vendor_name)
and aps.segment1 between nvl(:p_from_vendor_number,aps.segment1) and nvl(:p_to_vendor_number,aps.segment1)
and hou.name=nvl(:p_operating_unit,hou.name)
and gcc.code_combination_id=apss.ACCTS_PAY_CODE_COMBINATION_ID
and nvl(aps.VENDOR_TYPE_LOOKUP_CODE,1)=nvl(:p_vendor_type,NVL(aps.VENDOR_TYPE_LOOKUP_CODE,1))

2)QUERY2(vendor Site Id)
---------------------------------
select  aia.creation_date     "TRX DATE",
        aia.DOC_SEQUENCE_VALUE       "VNo",
        hrou.name             "OU",
        aia.invoice_type_lookup_code "INVOICE_TYPE",
        aia.invoice_num,
        aia.gl_date "INVOICE GL DATE",
        aia.vendor_site_id,
        aia.invoice_id,
        null check_id,
        null invoice_payment_id,
        null check_number,
        null "PAYEMENT GL DATE",
        null bank_account_name,
        aia.invoice_currency_code,
        round(NVL(aia.exchange_rate,1),2) exchange_rate,
decode(aia.invoice_type_lookup_code,'STANDARD',aia.invoice_amount,'FTE_INVOICES',aia.invoice_amount,
             'EXPENSE REPORT',aia.invoice_amount,'MIXED',decode(sign(aia.invoice_amount),1,aia.invoice_amount),0) "ER CREDIT",
decode(aia.invoice_type_lookup_code,'CREDIT',abs(aia.invoice_amount),'DEBIT',abs(aia.invoice_amount),
             'MIXED',decode(sign(aia.invoice_amount),-1,abs(aia.invoice_amount)),0) "ER DEBIT",
        ROUND(DECODE(aia.invoice_type_lookup_code
                    ,'STANDARD',aia.invoice_amount
                    ,'EXPENSE REPORT',aia.invoice_amount
                    ,'MIXED',DECODE(SIGN(aia.invoice_amount),1,abs(aia.invoice_amount),0)
                    ,'FTE_INVOICES',aia.invoice_amount)*nvl(aia.exchange_rate,1)
                    ,2) "FUNC CR",
        ROUND(DECODE(aia.invoice_type_lookup_code
                    ,'CREDIT',abs(aia.invoice_amount)
                    ,'DEBIT',abs(aia.invoice_amount)
                    ,'MIXED',DECODE(SIGN(aia.invoice_amount),-1,abs(aia.invoice_amount),0))*nvl(aia.exchange_rate,1)
                    ,2) "FUNC DR",                            
        0 "Exchange_Rate_Fluxuation",
        glcc.segment4 "GL_ACCOUNT",
        AP_INVOICES_PKG.GET_POSTING_STATUS(aia.invoice_id) "POSTED"
from ap_invoices_all aia,
        hr_operating_units hrou,
        gl_code_combinations glcc
where aia.accts_pay_code_combination_id=glcc.code_combination_id (+)
and aia.org_id=hrou.organization_id
and trunc(aia.gl_date) between :P_FROM_DATE and :P_TO_DATE
and aia.invoice_type_lookup_code in('STANDARD','FTE_INVOICES','MIXED','CREDIT','EXPENSE REPORT','DEBIT')
and nvl(aia.invoice_amount,0)<>0 

union

select  aca.creation_date     "TRX DATE",
        aia.DOC_SEQUENCE_VALUE   "VNo",
        hrou.name             "OU",
        decode (aia.invoice_type_lookup_code,'PREPAYMENT','ADVANCE PAYMENT','PAYMENT')  "INVOICE_TYPE",
        aia.invoice_num,
        aia.invoice_date "INVOICE GL DATE",
        aia.vendor_site_id,
        aia.invoice_id,
        aca.check_id,
        aip.invoice_payment_id,
        aca.check_number,
        aip.accounting_date  "PAYMENT GL DATE",
        aca.bank_account_name,
        aia.invoice_currency_code,
        round(nvl(aip.exchange_rate,1),2) exchange_rate,
        nvl(decode(sign(aip.amount),-1,abs(aip.amount)),0) "ER CREDIT",
        nvl(decode(sign(aip.amount),1,aip.amount),0) "ER DEBIT",
        ROUND(DECODE(SIGN(nvl(aip.amount,0)),-1,ABS(nvl(aip.amount,0)),0)*nvl(AIP.EXCHANGE_RATE,1),2) "FUNC CR",
        ROUND(DECODE(SIGN(nvl(aip.amount,0)),1,nvl(aip.amount,0),0)*nvl(AIP.EXCHANGE_RATE,1),2) "FUNC DR",
        nvl(round((aia.exchange_rate-aip.exchange_rate)*nvl(aip.amount,0),2),0) "Exchange_Rate_Fluxuation",
        glcc.segment4 "GL_ACCOUNT",
        AP_INVOICES_PKG.GET_POSTING_STATUS(aia.invoice_id) "POSTED"
from ap_invoices_all aia,
     ap_invoice_payments_all aip,
     ap_checks_all aca,
     hr_operating_units hrou,
     gl_code_combinations glcc
where aia.invoice_id=aip.invoice_id(+)
and aip.check_id=aca.check_id(+)
and aia.accts_pay_code_combination_id=glcc.code_combination_id (+)
and aia.org_id=hrou.organization_id
and aia.invoice_type_lookup_code in ('STANDARD','PREPAYMENT','MIXED','EXPENSE REPORT','FTE_INVOICES','CREDIT','DEBIT')
and AP_INVOICES_PKG.GET_POSTING_STATUS(aia.invoice_id)='Y'
and trunc(aip.accounting_date) between :P_FROM_DATE and :P_TO_DATE

Formula Column
-----------------------
function CF_3Formula return Number is
begin
    return(:P_SELECT);
end;