Dilli ERP Notes: Supplier Statement Of Accounts Custom Report Query

/* Formatted on 10-01-2015 04:44:00 PM (QP5 v5.163.1008.3004) */
SELECT *
FROM (SELECT vendor_name,
invoice_date,
gl_date,
invoice_currency_code,
name,
document_type,
supplier_reference,
posting_status,
narration,
document_no,
due_date,
payment_status,
DECODE (document_type,
'DEBIT', inv_amount,
(payment_amount + prepay_applied))
entered_dr,
DECODE (document_type,
'STANDARD', inv_amount,
'CREDIT', inv_amount,
(payment_amount + prepay_applied))
entered_cr,
-- INV_AMOUNT ENTERED_DR,
-- (PAYMENT_AMOUNT + PREPAY_APPLIED) ENTERED_CR,
(inv_amount - (payment_amount + prepay_applied))
entered_balance,
DECODE (document_type,
'DEBIT', (inv_amount * exchange_rate),
( (payment_amount + prepay_applied) * exchange_rate))
functional_dr,
DECODE (document_type,
'STANDARD', inv_amount * exchange_rate,
'CREDIT', inv_amount * exchange_rate,
( (payment_amount + prepay_applied) * exchange_rate))
functional_cr,
-- (INV_AMOUNT * EXCHANGE_RATE) FUNCTIONAL_DR,
-- ( (PAYMENT_AMOUNT + PREPAY_APPLIED) * EXCHANGE_RATE) FUNCTIONAL_CR,
( (inv_amount * exchange_rate)
- ( (payment_amount + prepay_applied) * exchange_rate))
functional_balance
FROM ( SELECT pv.vendor_name vendor_name,
aia.invoice_date invoice_date,
aia.gl_date gl_date,
aia.invoice_currency_code invoice_currency_code,
hou.name,
aia.invoice_type_lookup_code document_type,
-- AIA.INVOICE_ID,
aia.invoice_num supplier_reference,
ap_invoices_pkg.get_posting_status (aia.invoice_id)
posting_status,
NVL (aia.exchange_rate, 1) exchange_rate,
aia.description narration,
aia.doc_sequence_value document_no,
(SELECT MIN (apsa.due_date)
FROM ap.ap_payment_schedules_all apsa
WHERE apsa.invoice_id = aia.invoice_id)
due_date,
DECODE (aia.payment_status_flag,
'N', 'Not Paid',
'P', 'Partially Paid',
'Y', 'Fully Paid')
payment_status,
SUM (aida.amount) inv_amount,
NVL (
(SELECT SUM (aipa.amount)
FROM ap_invoice_payments_all aipa
WHERE 1 = 1 AND aipa.invoice_id = aia.invoice_id
AND TO_DATE (aipa.accounting_date,
'DD-MON-RRRR') <=
TO_DATE (:p_as_of_date,
'DD-MON-RRRR')),
0)
payment_amount,
NVL (
(SELECT SUM (avp.prepay_amount_applied)
FROM ap_view_prepays_fr_prepay_v avp
WHERE 1 = 1 AND avp.invoice_id = aia.invoice_id
AND TO_DATE (avp.accounting_date,
'DD-MON-RRRR') <=
TO_DATE (:p_as_of_date,
'DD-MON-RRRR')),
0)
prepay_applied
FROM ap_invoices_all aia,
ap_invoice_distributions_all aida,
po_vendors pv,
hr_operating_units hou
WHERE 1 = 1
AND aia.invoice_id = aida.invoice_id
AND aia.vendor_id = pv.vendor_id
AND aia.org_id = hou.organization_id
AND aida.line_type_lookup_code IN
('ITEM',
'ACCRUAL',
'ERV',
'IPV',
'FREIGHT',
'MISCELLANEOUS')
AND aia.invoice_type_lookup_code <> 'PREPAYMENT'
AND ap_invoices_pkg.get_posting_status (
aia.invoice_id) = 'Y'
AND aia.org_id = NVL (:org_id, aia.org_id)
AND pv.vendor_name =
NVL (:p_vendor_name, pv.vendor_name)
AND TO_DATE (aida.accounting_date, 'DD-MON-RRRR') <=
TO_DATE (:p_as_of_date, 'DD-MON-RRRR')
-- AND aia.vendor_id = 86014
GROUP BY aia.invoice_id,
aia.vendor_id,
pv.vendor_name,
aia.invoice_date,
aia.gl_date,
aia.invoice_currency_code,
hou.name,
aia.description,
aia.exchange_rate,
aia.doc_sequence_value,
aia.payment_status_flag,
aia.invoice_type_lookup_code,
aia.invoice_num)
UNION ALL
-- =========== PREPAYMENT QUERY ================= --

SELECT vendor_name,
invoice_date,
gl_date,
invoice_currency_code,
name,
document_type,
supplier_reference,
posting_status,
narration,
document_no,
due_date,
payment_status,
inv_amount entered_cr,
prepay_applied entered_dr,
(inv_amount - prepay_applied) entered_balance,
(inv_amount * exchange_rate) functional_cr,
(prepay_applied * exchange_rate) functional_dr,
( (inv_amount * exchange_rate)
- (prepay_applied * exchange_rate))
functional_balance
FROM ( SELECT aps.vendor_name vendor_name,
aia.invoice_date invoice_date,
aia.gl_date gl_date,
aia.invoice_currency_code invoice_currency_code,
hou.name,
aia.invoice_type_lookup_code document_type,
aia.invoice_num supplier_reference,
ap_invoices_pkg.get_posting_status (aia.invoice_id)
posting_status,
NVL (aia.exchange_rate, 1) exchange_rate,
aia.description narration,
aia.doc_sequence_value document_no,
(SELECT MIN (apsa.due_date)
FROM ap.ap_payment_schedules_all apsa
WHERE apsa.invoice_id = aia.invoice_id)
due_date,
DECODE (aia.payment_status_flag,
'N', 'Not Paid',
'P', 'Partially Paid',
'Y', 'Fully Paid')
payment_status,
SUM (aipa.amount) inv_amount,
0 payment_amount,
NVL (
(SELECT SUM (prepay_amount_applied)
FROM ap_view_prepays_fr_prepay_v
WHERE prepay_id = aia.invoice_id
AND TO_DATE (accounting_date,
'DD-MON-RRRR') <=
TO_DATE (:p_as_of_date,
'DD-MON-RRRR')),
0)
prepay_applied
FROM apps.ap_invoices_all aia,
--APPS.AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
apps.ap_invoice_payments_all aipa,
apps.ap_suppliers aps,
apps.ap_supplier_sites_all apsa,
apps.hr_operating_units hou
WHERE aia.invoice_id = aipa.invoice_id
--AND AIDA.INVOICE_ID = AIPA.INVOICE_ID
AND aia.vendor_id = aps.vendor_id
AND aps.vendor_id = apsa.vendor_id
AND aia.vendor_site_id = apsa.vendor_site_id
AND aia.org_id = hou.organization_id
AND aia.invoice_type_lookup_code = 'PREPAYMENT'
AND ap_invoices_pkg.get_posting_status (
aia.invoice_id) = 'Y'
AND aia.org_id = NVL (:org_id, aia.org_id)
AND aps.vendor_name =
NVL (:p_vendor_name, aps.vendor_name)
--AND TO_DATE (AIDA.ACCOUNTING_DATE, 'DD-MON-RRRR') <=
-- TO_DATE (:P_AS_OF_DATE, 'DD-MON-RRRR')
AND TO_DATE (aipa.accounting_date, 'DD-MON-RRRR') <=
TO_DATE (:p_as_of_date, 'DD-MON-RRRR')
GROUP BY aps.vendor_name,
--AIDA.DIST_CODE_COMBINATION_ID,
aia.invoice_type_lookup_code,
aia.invoice_num,
aia.invoice_id,
aia.invoice_date,
aia.gl_date,
hou.name,
aia.invoice_currency_code,
NVL (aia.exchange_rate, 1),
aia.doc_sequence_value,
aia.payment_status_flag,
aia.description))
WHERE functional_balance <> 0;

Dilli ERP Notes

Saturday, 10 January 2015

Supplier Statement Of Accounts Custom Report Query

No comments:

Post a Comment

About Me