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
No comments:
Post a Comment