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;