Wednesday, 5 December 2012

Example How to query Tax and Invoices in Oracle Apps


SELECT
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,
p.amount                                        paymentAmount,
v.invoice_amount                                TAX_Invoice_Amount,
'Your Name' "Name",
'Your Address' "Address",
'Your Number' "UN",
'Your Date' "Date",
'Your Office' "OfficerName",
'Your Designation' "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 '1-jul-2009' and '30-Jun-2010'
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 substr(v.invoice_num,19,6) in(SELECT v.invoice_id FROM
Ap_Invoices_All v
WHERE
v.invoice_date between '1-jul-2009' and '30-Jun-2010'
and v.vendor_id='0000'
and v.invoice_type_lookup_code='STANDARD'
)

No comments:

Post a Comment