Supplier Prepayment based on Subledger:
Supplier Prepayment means create a record of advance or down payment to supplier. Supplier prepayment aging will display details which is available and not apply against any payment or invoice till as on date. As on date is compare with subledger accounting date. I am sharing the detailed query of supplier prepayment below.
Subledger tables used
xla_ae_headers
xla_ae_lines
xla_transaction_entities
xla_distribution_links
xla_ae_lines
xla_transaction_entities
xla_distribution_links
AP and Supplier Tables
ap_invoices
poz_suppliers_v
poz_supplier_sites_v
poz_supplier_sites_v
Detailed Query
SELECT prepay.invoice_num prepay_number,
prepay.invoice_date,
SUM (
NVL (d.unrounded_accounted_dr, 0)
- NVL (d.unrounded_accounted_cr, 0))
prepay_amount_remaining,
hp.vendor_name,
FROM xla_ae_headers h,
xla_ae_lines l,
xla_transaction_entities ent,
xla_distribution_links d,
(SELECT DISTINCT aia.invoice_num, ent.entity_id, aia.invoice_date
FROM ap_invoices aia,
xla_transaction_entities ent,
xla_ae_headers h,
xla_ae_lines l,
poz_suppliers_v supplier,
poz_supplier_sites_v sites
WHERE ent.source_id_int_1 = aia.invoice_id
AND supplier.vendor_id = aia.vendor_id
AND sites.vendor_site_id = aia.vendor_site_id
AND ent.application_id = 200
AND aia.invoice_type_lookup_code = 'PREPAYMENT'
AND h.ae_header_id = l.ae_header_id
AND h.entity_id = ent.entity_id
AND ent.application_id = h.application_id
AND aia.org_id IN
(SELECT organization_id
FROM hr_operating_units
WHERE organization_id =
NVL (:p_bu_name, organization_id))
AND supplier.vendor_id =
NVL (:p_party_name, supplier.vendor_id)
AND sites.vendor_site_code =
NVL (:p_supp_site, sites.vendor_site_code)
AND l.accounting_date <= :p_end_date) prepay,
(SELECT DISTINCT SUPPLIER.VENDOR_NAME, SUPPLIER.SEGMENT1, H.ENTITY_ID
FROM ap_invoices aia,
xla_transaction_entities ent,
xla_ae_headers h,
xla_ae_lines l,
poz_suppliers_v supplier,
poz_supplier_sites_v sites
WHERE ent.source_id_int_1 = aia.invoice_id
AND supplier.vendor_id = aia.vendor_id
AND sites.vendor_site_id = aia.vendor_site_id
AND ent.application_id = 200
AND aia.invoice_type_lookup_code = 'PREPAYMENT'
AND h.ae_header_id = l.ae_header_id
AND h.entity_id = ent.entity_id
AND ent.application_id = h.application_id
AND aia.org_id IN
(SELECT organization_id
FROM hr_operating_units
WHERE organization_id =
NVL (:p_bu_name, organization_id))
AND supplier.vendor_id =
NVL (:p_party_name, supplier.vendor_id)
AND sites.vendor_site_code =
NVL (:p_supp_site, sites.vendor_site_code)
AND l.accounting_date <= :p_end_date) hp
WHERE h.ae_header_id = l.ae_header_id
AND h.entity_id = ent.entity_id
AND h.application_id = l.application_id
AND ent.application_id = 200
AND h.ae_header_id = d.ae_header_id
AND l.ae_line_num = d.ae_line_num
AND d.applied_to_entity_id = prepay.entity_id
AND d.applied_to_entity_id = hp.entity_id
AND l.accounting_date <= :p_end_date
GROUP BY prepay.invoice_num,
prepay.invoice_date,
hp.vendor_name
HAVING SUM (NVL (l.accounted_dr, 0) - NVL (l.accounted_cr, 0)) <> 0;
prepay.invoice_date,
SUM (
NVL (d.unrounded_accounted_dr, 0)
- NVL (d.unrounded_accounted_cr, 0))
prepay_amount_remaining,
hp.vendor_name,
FROM xla_ae_headers h,
xla_ae_lines l,
xla_transaction_entities ent,
xla_distribution_links d,
(SELECT DISTINCT aia.invoice_num, ent.entity_id, aia.invoice_date
FROM ap_invoices aia,
xla_transaction_entities ent,
xla_ae_headers h,
xla_ae_lines l,
poz_suppliers_v supplier,
poz_supplier_sites_v sites
WHERE ent.source_id_int_1 = aia.invoice_id
AND supplier.vendor_id = aia.vendor_id
AND sites.vendor_site_id = aia.vendor_site_id
AND ent.application_id = 200
AND aia.invoice_type_lookup_code = 'PREPAYMENT'
AND h.ae_header_id = l.ae_header_id
AND h.entity_id = ent.entity_id
AND ent.application_id = h.application_id
AND aia.org_id IN
(SELECT organization_id
FROM hr_operating_units
WHERE organization_id =
NVL (:p_bu_name, organization_id))
AND supplier.vendor_id =
NVL (:p_party_name, supplier.vendor_id)
AND sites.vendor_site_code =
NVL (:p_supp_site, sites.vendor_site_code)
AND l.accounting_date <= :p_end_date) prepay,
(SELECT DISTINCT SUPPLIER.VENDOR_NAME, SUPPLIER.SEGMENT1, H.ENTITY_ID
FROM ap_invoices aia,
xla_transaction_entities ent,
xla_ae_headers h,
xla_ae_lines l,
poz_suppliers_v supplier,
poz_supplier_sites_v sites
WHERE ent.source_id_int_1 = aia.invoice_id
AND supplier.vendor_id = aia.vendor_id
AND sites.vendor_site_id = aia.vendor_site_id
AND ent.application_id = 200
AND aia.invoice_type_lookup_code = 'PREPAYMENT'
AND h.ae_header_id = l.ae_header_id
AND h.entity_id = ent.entity_id
AND ent.application_id = h.application_id
AND aia.org_id IN
(SELECT organization_id
FROM hr_operating_units
WHERE organization_id =
NVL (:p_bu_name, organization_id))
AND supplier.vendor_id =
NVL (:p_party_name, supplier.vendor_id)
AND sites.vendor_site_code =
NVL (:p_supp_site, sites.vendor_site_code)
AND l.accounting_date <= :p_end_date) hp
WHERE h.ae_header_id = l.ae_header_id
AND h.entity_id = ent.entity_id
AND h.application_id = l.application_id
AND ent.application_id = 200
AND h.ae_header_id = d.ae_header_id
AND l.ae_line_num = d.ae_line_num
AND d.applied_to_entity_id = prepay.entity_id
AND d.applied_to_entity_id = hp.entity_id
AND l.accounting_date <= :p_end_date
GROUP BY prepay.invoice_num,
prepay.invoice_date,
hp.vendor_name
HAVING SUM (NVL (l.accounted_dr, 0) - NVL (l.accounted_cr, 0)) <> 0;