Monday, August 25, 2008

Script to get the invoice and supplier details

SELECT ai.invoice_id, ai.invoice_num, ai.invoice_type_lookup_code,
ai.invoice_date, (SELECT segment1
FROM po_headers_all
WHERE po_header_id = ai.po_header_id) po_number,
ai.vendor_id, pv.segment1 vendor_number, pv.vendor_name,
ai.vendor_site_id, pvs.vendor_site_code,
( NVL (ai.invoice_amount, 0)
- NVL (ai.amount_paid, 0)
+ NVL (ai.discount_amount_taken, 0)
) remaining_amount,
ai.invoice_currency_code, ai.exchange_rate, ai.exchange_rate_type,
ai.exchange_date, ai.terms_id, (SELECT NAME
FROM ap_terms
WHERE term_id = ai.terms_id) terms,
ai.description, ai.awt_group_id,
(SELECT awt.NAME
FROM ap_awt_groups awt
WHERE awt.GROUP_ID = ai.awt_group_id) awt_group, ai.last_update_date,
ai.last_updated_by, ai.last_update_login, ai.creation_date,
ai.created_by, ai.attribute_category, ai.attribute1, ai.attribute2,
ai.attribute3, ai.attribute4, ai.attribute5, ai.attribute6,
ai.attribute7, ai.attribute8, ai.attribute9, ai.attribute10,
ai.attribute11, ai.attribute12, ai.attribute13, ai.attribute14,
ai.attribute15, ai.global_attribute_category, ai.global_attribute1,
ai.global_attribute2, ai.global_attribute3, ai.global_attribute4,
ai.global_attribute5, ai.global_attribute6, ai.global_attribute7,
ai.global_attribute8, ai.global_attribute9, ai.global_attribute10,
ai.global_attribute11, ai.global_attribute12, ai.global_attribute13,
ai.global_attribute14, ai.global_attribute15, ai.global_attribute16,
ai.global_attribute17, ai.global_attribute18, ai.global_attribute19,
ai.global_attribute20, ai.payment_cross_rate_type,
ai.payment_cross_rate_date, ai.payment_cross_rate,
ai.payment_currency_code, ai.doc_category_code, ai.voucher_num,
ai.payment_method_lookup_code, ai.pay_group_lookup_code,
ai.goods_received_date, ai.invoice_received_date, ai.gl_date,
ai.accts_pay_code_combination_id, ai.ussgl_transaction_code,
ai.exclusive_payment_flag, ai.org_id, ai.amount_applicable_to_discount,
pvs.email_address, ai.terms_date, ai.requester_id
FROM ap_invoices_all ai, po_vendors pv, po_vendor_sites_all pvs
WHERE 1 = 1
AND ai.vendor_id = pv.vendor_id
AND pvs.vendor_id = pv.vendor_id
AND ai.vendor_site_id = pvs.vendor_site_id

No comments: