AP INVOICE VAT REPORT

select distinct aia.invoice_num
      ,AIA.VENDOR_ID
      ,invoice_date
      ,aia.creation_date
      ,aia.invoice_id
      ,aps.vendor_name
      ,apss.city
      ,item_DESCRIPTION
      ,aila.QUANTITY_INVOICED Qty
      ,aila.UNIT_MEAS_LOOKUP_CODE uom
      ,aid.rcv_transaction_ID
      ,aid.po_distribution_id
      ,jpt.tax_rate
      ,aila.match_type
  from  ap_invoices_all       aia
       ,ap_invoice_lines_all  aila
       ,jai_po_taxes          jpt
       ,ap_suppliers          aps
       ,ap_supplier_sites_all apss
       ,ap_invoice_distributions_All aid
 where aia.invoice_id=aila.invoice_id
   and aia.invoice_id=aid.invoice_id
   and aila.PO_LINE_ID= jpt.PO_LINE_ID
   and TAX_TYPE='VALUE ADDED TAX' and MODVAT_FLAG='Y'
   and aia.vendor_id=aps.vendor_id
   and aia.VENDOR_SITE_ID=apss.VENDOR_SITE_ID
   and aila.LINE_TYPE_LOOKUP_CODE='ITEM'
   and aila.PO_DISTRIBUTION_ID=aid.PO_DISTRIBUTION_ID
   and nvl(aila.RCV_TRANSACTION_ID,1)=nvl(aid.RCV_TRANSACTION_ID,1)
   and aila.cancelled_flag!='Y'
   and aid.DESCRIPTION like '%VAT%'
   and aia.ORG_ID=nvl(:p_org_id,aia.org_id)
   and trunc(aia.invoice_date) between nvl(:p_from_date,trunc(aia.invoice_date)) and nvl(:p_to_date,trunc(aia.invoice_date))
   and trunc(aia.creation_date) between nvl(:p_from_date1,trunc(aia.creation_date)) and nvl(:p_to_date1,trunc(aia.creation_date))
   and trunc(aia.GL_DATE) between nvl(:p_from_date2,trunc(aia.GL_DATE)) and nvl(:p_to_date2,trunc(aia.GL_DATE))
   order by 1

Formula columns
-----------------------------------
1) function CF_NET_AMOUNTFormula return Number is
v_amount number;
v_amount1 number;
v_amount2 number;
begin

      if :match_type='ITEM_TO_RECEIPT' then
        
      select sum(AMOUNT)
    INTO V_AMOUNT
    from ap_invoice_distributions_All
   where INVOICE_id=:INVOICE_ID 
     and rcv_transaction_id=:rcv_transaction_id ;
    elsif :match_type='ITEM_TO_PO' then 
      select sum(AMOUNT)
    INTO V_AMOUNT
    from ap_invoice_distributions_All
   where INVOICE_id=:INVOICE_ID 
     and po_distribution_id=:po_distribution_id  ;
    end if;
    v_AMOUNT:=V_AMOUNT-NVL(:CF_VAT_AMOUNT,0);

       return v_amount;
exception
    when others then
    return null;
end;

2)function CF_TOTALFormula return Number is
V_TOTAL NUMBER;
begin
  V_TOTAL:=NVL(:CF_VAT_AMOUNT,0)+NVL(:CF_NET_AMOUNT,0);
  RETURN V_TOTAL;
end;

3)function CF_VAT_AMOUNTFormula return Number is
V_AMOUNT NUMBER:=0;
begin
    if :match_type='ITEM_TO_RECEIPT' then
        srw.message(100,'transaction'||:rcv_transaction_id||:INVOICE_ID);
  select AMOUNT
    INTO V_AMOUNT
    from ap_invoice_distributions_All
   where INVOICE_id=:INVOICE_ID 
     and rcv_transaction_id=:rcv_transaction_id
     and LINE_TYPE_LOOKUP_CODE='MISCELLANEOUS'
     and description like '%VAT%';
    elsif :match_type='ITEM_TO_PO' then  
        select AMOUNT
    INTO V_AMOUNT
    from ap_invoice_distributions_All
   where INVOICE_id=:INVOICE_ID 
     and po_distribution_id=:po_distribution_id
     and LINE_TYPE_LOOKUP_CODE='MISCELLANEOUS'
     and description like '%VAT%';
   end if; 
    RETURN V_AMOUNT;
EXCEPTION
    WHEN OTHERS THEN
    RETURN NULL;
end;

4)function CF_VAT_REG_NUMFormula return Char is
V_REG_NUM VARCHAR2(100);
begin
  SELECT VAT_REG_NO
    INTO V_REG_NUM
    FROM jai_cmn_vendor_sites
   WHERE VENDOR_ID=:VENDOR_ID
     AND VENDOR_SITE_ID=0;
     return v_reg_num;
EXCEPTION
    WHEN OTHERS THEN
    RETURN NULL;
end;