AWT Invoices of a vendor gnerated by the system in month of may and june, and their payment

--AWT Invoices of a vendor gnerated by the system in month of may and june, and their payment(that is check_date) is made in july or aug or sep of next financial year,
--but thier WHT certificate is given in june of previous financial year.
select v.invoice_date Invoice_Date,c.check_date,v.invoice_amount from
ap_checks_all c,
ap_invoice_payments_all p,
ap_invoices_all     v
where
v.invoice_id=p.invoice_id
and c.check_id=p.check_id
and v.invoice_date  between '1-may-12' and '30-jun-12'
and c.check_date    between '1-jul-12' and '30-aug-12'
and v.invoice_id in (select d.awt_invoice_id   --This Query gives us the awt invoices of vendor, from standard invoices
from
ap_invoices_all v,                             -- distribution that is relation between invoices and distribution along with
                                               --  ap_awt_tax_rates                
ap_invoice_distributions_all d,
ap_awt_tax_rates_all         tr
where
v.invoice_id=d.invoice_id
and tr.tax_rate_id=d.awt_tax_rate_id
and d.line_type_lookup_code='AWT'
and v.invoice_date between &FDATE and &TODATE
and v.invoice_type_lookup_code='STANDARD'
and v.payment_status_flag='Y'
and tr.tax_name=&taxname
and v.vendor_id=&vendId)

Final TAX QUERY

SELECT  v.invoice_id,d.amount,tr.tax_name,tr.tax_rate,tr.comments FROM
Ap_Invoices_All v,
Po_Vendors      pov,
ap_invoice_distributions_all d,
ap_awt_tax_rates_all tr
WHERE
v.vendor_id=pov.vendor_id
and v.invoice_id=d.invoice_id
and d.awt_tax_rate_id=tr.tax_rate_id
and tr.tax_name='ABC 0% Contract'
and v.invoice_id in (SELECT
trim(substr(v.invoice_num,instr(v.invoice_num,'-',1,1)+2,instr(v.invoice_num,'-',1,2)-instr(v.invoice_num,'-',1,1)-2))
FROM
Ap_Checks_All            c,
AP_Invoice_Payments_all  p,
Ap_Invoices_All          v,
Ap_Bank_Branches         b,
Ap_Bank_Accounts_All     ba
WHERE
c.check_date between &FDate and &TDate
and v.vendor_id=1
and v.invoice_type_lookup_code='AWT'
and b.bank_branch_id=ba.bank_branch_id
and c.bank_account_id=ba.bank_account_id
and c.check_id=p.check_id
and v.invoice_id=p.invoice_id
and trim(substr(v.invoice_num,instr(v.invoice_num,'-',1,1)+2,instr(v.invoice_num,'-',1,2)-instr(v.invoice_num,'-',1,1)-2)) in(SELECT trim(v.invoice_id) FROM
Ap_Invoices_All v,
Po_Vendors      pov
WHERE
v.vendor_id=pov.vendor_id
and v.invoice_date between &FDate and &TDate
and pov.segment1=&venID
and v.invoice_type_lookup_code='STANDARD'
)
)

-----



SELECT  -1*sum(d.amount/(tr.tax_rate/100)) InvAmount ,-1*sum(d.amount),tr.tax_name,tr.tax_rate,tr.comments FROM
Ap_Invoices_All v,
Po_Vendors      pov,
ap_invoice_distributions_all d,
ap_awt_tax_rates_all tr
WHERE
v.vendor_id=pov.vendor_id
and v.invoice_id=d.invoice_id
and d.awt_tax_rate_id=tr.tax_rate_id
and v.invoice_id in (SELECT
trim(substr(v.invoice_num,instr(v.invoice_num,'-',1,1)+2,instr(v.invoice_num,'-',1,2)-instr(v.invoice_num,'-',1,1)-2))
FROM
Ap_Checks_All            c,
AP_Invoice_Payments_all  p,
Ap_Invoices_All          v,
Ap_Bank_Branches         b,
Ap_Bank_Accounts_All     ba
WHERE
c.check_date between &FDate and &TDate
and v.vendor_id=1
and v.invoice_type_lookup_code='AWT'
and b.bank_branch_id=ba.bank_branch_id
and c.bank_account_id=ba.bank_account_id
and c.check_id=p.check_id
and v.invoice_id=p.invoice_id
and trim(substr(v.invoice_num,instr(v.invoice_num,'-',1,1)+2,instr(v.invoice_num,'-',1,2)-instr(v.invoice_num,'-',1,1)-2)) in(SELECT trim(v.invoice_id) FROM
Ap_Invoices_All v,
Po_Vendors      pov
WHERE
v.vendor_id=pov.vendor_id
and v.invoice_date between &FDate and &TDate
and pov.segment1=&venID
and v.invoice_type_lookup_code='STANDARD'
)
)
group by tr.tax_name,tr.tax_rate,tr.comments

-----
For Payments

SELECT
c.address_line1,
v.invoice_num,
v.invoice_id,
v.invoice_amount,
to_char(c.check_date,'YYYYMMDD')                DepositDate,
b.bank_name,
b.bank_branch_name,
p.amount                                        paymentAmount,
--v.invoice_amount                                TAX_Invoice_Amount,
'YourName' "Name",
'YourAdd' "Address",
'YourNTN'  "NTN",
'YOurDate'  "Date",
'YOurOff' "OfficerName",
'Yours' "Designation"

FROM
Ap_Checks_All            c,
AP_Invoice_Payments_all  p,
Ap_Invoices_All          v,
Ap_Bank_Branches         b,
Ap_Bank_Accounts_All     ba
WHERE
c.check_date between &FDate and &TDate
and v.vendor_id=1
and v.invoice_type_lookup_code='AWT'
and b.bank_branch_id=ba.bank_branch_id
and c.bank_account_id=ba.bank_account_id
and c.check_id=p.check_id
and v.invoice_id=p.invoice_id
and v.invoice_id in (SELECT trim(d.awt_invoice_id) FROM
Ap_Invoices_All v,
Po_Vendors      pov,
ap_invoice_distributions_all d,
ap_awt_tax_rates_all tr
WHERE
v.vendor_id=pov.vendor_id
and tr.tax_name=''
--and  tr.tax_name=''
--and  tr.tax_name=''
--and  tr.tax_name=''
and v.invoice_id=d.invoice_id
and d.awt_tax_rate_id=tr.tax_rate_id
and v.invoice_date between &FDATE and &TDATE
and v.invoice_type_lookup_code='STANDARD'
and pov.SEGMENT1=&vendornum)


-----


function CF_Taxtotal return Number is
inamt  number;
taamt  number;
tanam  varchar2(50);
natr   varchar2(50);
code   varchar2(50);
ntn    varchar2(60);
vname  varchar2(200);
begin
SELECT
 -1*sum(d.amount/(tr.tax_rate/100)) InvAmount ,-1*sum(d.amount) TAXAmount,tr.tax_name,substr(tr.comments,instr(tr.comments,'-',1)+1) nature ,substr(tr.comments,1,instr(tr.comments,'-',1)-1) code,
pov.NUM_1099,pov.VENDOR_NAME into inamt,taamt,tanam,natr,code,ntn,vname
FROM
ap.Ap_Invoices_All v,
Po_Vendors      pov,
ap.ap_invoice_distributions_all d,
ap.ap_awt_tax_rates_all tr
WHERE
v.vendor_id=pov.vendor_id
--and  tr.tax_name='WHT 6% Contract'
and tr.tax_name=:TAX_NAME
and v.invoice_id=d.invoice_id
and d.awt_tax_rate_id=tr.tax_rate_id
and v.invoice_id in (SELECT
rtrim(ltrim(substr(v.invoice_num,instr(v.invoice_num,'-',1,1)+2,instr(v.invoice_num,'-',1,2)-instr(v.invoice_num,'-',1,1)-2)))
FROM
ap.Ap_Checks_All            c,
ap.AP_Invoice_Payments_all  p,
ap.Ap_Invoices_All          v,
ap.Ap_Bank_Branches         b,
ap.Ap_Bank_Accounts_All     ba
WHERE
c.check_date between :FDate and :TDate
and v.vendor_id=1
and v.invoice_type_lookup_code='AWT'
and b.bank_branch_id=ba.bank_branch_id
and c.bank_account_id=ba.bank_account_id
and c.check_id=p.check_id
and v.invoice_id=p.invoice_id
and ltrim(rtrim(substr(v.invoice_num,instr(v.invoice_num,'-',1,1)+2,instr(v.invoice_num,'-',1,2)-instr(v.invoice_num,'-',1,1)-2))) in(SELECT ltrim(v.invoice_id) FROM
Ap_Invoices_All v,
Po_Vendors      pov
WHERE
v.vendor_id=pov.vendor_id
and v.invoice_date between :FDate and :TDate
and pov.segment1=:venID
and v.invoice_type_lookup_code='STANDARD'
)
)
group by
tr.tax_name,tr.tax_rate,tr.comments,
substr(tr.comments,instr(tr.comments,'-',1)+1),substr(tr.comments,1,instr(tr.comments,'-',1)-1),
pov.NUM_1099,pov.VENDOR_NAME;
:CP_vendorinvoice:=inamt;
:CP_coments:=natr;
:CP_code:=code;
:CP_vendortax:=taamt;
:CP_vname:=vname;
:CP_vntn:=ntn;
return 1;
end;

Tax Query_Collective

SELECT  tr.tax_name,tr.tax_rate,tr.comments,d.line_type_lookup_code,-1*sum(d.amount),d.awt_tax_rate_id FROM
Ap_Invoices_All              v,      
AP_INVOICE_DISTRIBUTIONS_ALL d,
AP_AWT_TAX_RATES_ALL         tr
where
v.invoice_id=d.invoice_id
and d.awt_tax_rate_id=tr.tax_rate_id
and v.invoice_date between '1-jul-11' and '30-jun-12'
and d.line_type_lookup_code='AWT'
and v.vendor_id=0000.
group by tr.tax_name,tr.tax_rate,tr.comments,d.line_type_lookup_code,d.awt_tax_rate_id

Payments Query

function CF_InvoiceTotal return Number is

invamt number;

begin

select sum(v.invoice_amount) into invamt from

ap_invoices_all v,

ap.ap_suppliers  s

where

v.vendor_id=s.vendor_id

and v.invoice_date between :FDATE and :TDATE

and v.invoice_type_lookup_code='STANDARD'

and s.segment1=:VENID;

--return invamt

:CP_vendorinvoice:=invamt;

return 1;

end;

function CF_Taxtotal return Number is

coment varchar2(255);

code   varchar2(10);

taxamt number;

city varchar2(50);

venname varchar2(50);

ntn varchar2(60);

begin

SELECT substr(tr.comments,instr(tr.comments,'-',1)+1),substr(tr.comments,1,instr(tr.comments,'-',1)-1),-1*sum(d.amount),ss.city,s.vendor_name,s.num_1099 into coment,code,taxamt,city,venname,ntn  FROM

ap.Ap_Invoices_All              v,      

ap.AP_INVOICE_DISTRIBUTIONS_ALL d,

ap.AP_AWT_TAX_RATES_ALL         tr,

ap.Ap_Suppliers                 s,

ap.Ap_Supplier_Sites_All        ss

where

v.invoice_id=d.invoice_id

and d.awt_tax_rate_id=tr.tax_rate_id

and s.vendor_id=v.vendor_id

and ss.vendor_id=v.vendor_id

and v.invoice_date between :FDATE and :TDATE

and d.line_type_lookup_code='AWT'

and s.segment1=:VENID

group by tr.comments,ss.city,s.vendor_name,s.num_1099;       

:CP_coments:=coment;

:CP_code:=code;            

:CP_vendortax:=taxamt;

:CP_city:=city;

:CP_vname:=venname;

:CP_vntn:=ntn;

return 1;

end;

function CF_spelltaxFormula return varchar2 is

tax number;

sptax varchar2(500);

begin

 tax:=:CP_vendortax;    

 select w_spell_number(tax) into sptax  from dual;

 return sptax;

end;

Quey for Payments,Banks

SELECT

c.address_line1,

--v.vendor_id,

--v.invoice_num,

--v.invoice_id,

--v.invoice_amount,

to_char(c.check_date,'YYYYMMDD')                DepositDate,

b.bank_name,

b.bank_branch_name,

sum(p.amount)                                        paymentAmount,

--v.invoice_amount                                TAX_Invoice_Amount,

' "Name",

'' "Address",

''  "",

''  "Date",

'' "OfficerName",

'' "Designation"

FROM

Ap_Checks_All            c,

AP_Invoice_Payments_all  p,

Ap_Invoices_All          v,

Ap_Bank_Branches         b,

Ap_Bank_Accounts_All     ba

WHERE

v.invoice_date between :FDate and :TDate

and v.vendor_id=1

and v.invoice_type_lookup_code='AWT'

and b.bank_branch_id=ba.bank_branch_id

and c.bank_account_id=ba.bank_account_id

and c.check_id=p.check_id

and v.invoice_id=p.invoice_id

and trim(substr(v.invoice_num,instr(v.invoice_num,'-',1,1)+2,instr(v.invoice_num,'-',1,2)-instr(v.invoice_num,'-',1,1)-2)) in(SELECT trim(v.invoice_id) FROM

Ap_Invoices_All v,

Po_Vendors      pov

WHERE

v.vendor_id=pov.vendor_id

and v.invoice_date between :FDate and :TDate

--and v.vendor_id=:venID

and pov.segment1=:venID

and v.invoice_type_lookup_code='STANDARD'

)

having sum(p.amount) <> 0

group by

c.address_line1,

v.vendor_id,

to_char(c.check_date,'YYYYMMDD'),

b.bank_name,

b.bank_branch_name