VIEW: APPS.FV_PAYABLE_REFUNDS_V

Source

View Text - Preformatted

SELECT apc.vendor_id, apc.vendor_name, po.segment1 vendor_num, apc.vendor_site_code, apc.vendor_site_id, apip.amount refund_amount, apc.check_id, apc.check_number, apc.amount check_amount, apc.check_date refund_gl_date, apip.period_name refund_gl_period, apc.org_id, api.invoice_id, api.invoice_num, api.invoice_amount, api.gl_date invoice_gl_date, apip.set_of_books_id , apip.invoice_payment_id FROM ap_checks_all apc, ap_invoice_payments_all apip, ap_invoices_all api , po_vendors po WHERE apip.set_of_books_id = api.set_of_books_id AND apc.org_id = apip.org_id AND apip.org_id = api.org_id AND apc.check_id = apip.check_id AND apip.invoice_id = api.invoice_id AND po.vendor_id = apc.vendor_id AND api.invoice_type_lookup_code IN ( 'CREDIT','DEBIT') AND apc.payment_type_flag = 'R' AND apip.posted_flag = 'Y' AND apip.reversal_inv_pmt_id IS NULL AND EXISTS (SELECT 1 FROM gl_je_lines gljl, gl_je_headers gljh, ap_invoice_distributions_all apid WHERE gljl.ledger_id = apid.set_of_books_id AND gljl.status = 'P' AND gljh.je_header_id = gljl.je_header_id AND gljh.je_from_sla_flag = 'N' AND gljl.code_combination_id = apid.DIST_CODE_COMBINATION_ID AND apid.invoice_id = api.invoice_id AND gljl.reference_2 = TO_CHAR(api.invoice_id) UNION SELECT 1 FROM gl_je_lines gljl, gl_je_headers gljh, gl_import_references glir, xla_ae_lines xal, xla_distribution_links xdl, ap_invoice_distributions_all apid WHERE gljl.ledger_id = apid.set_of_books_id AND glir.je_batch_id = gljh.je_batch_id AND glir.je_header_id = gljh.je_header_id AND glir.je_line_num = gljl.je_line_num AND gljl.status = 'P' AND gljh.je_header_id = gljl.je_header_id AND gljh.je_from_sla_flag = 'Y' AND gljh.je_source = 'Payables' AND xal.gl_sl_link_id = glir.gl_sl_link_id AND xal.gl_sl_link_table = glir.gl_sl_link_table AND xdl.ae_header_id = xal.ae_header_id AND xdl.ae_line_num = xal.ae_line_num AND xdl.source_distribution_id_num_1 = apid.invoice_distribution_id AND gljl.code_combination_id = apid.DIST_CODE_COMBINATION_ID AND apid.invoice_id = api.invoice_id)

View Text - HTML Formatted

SELECT APC.VENDOR_ID
, APC.VENDOR_NAME
, PO.SEGMENT1 VENDOR_NUM
, APC.VENDOR_SITE_CODE
, APC.VENDOR_SITE_ID
, APIP.AMOUNT REFUND_AMOUNT
, APC.CHECK_ID
, APC.CHECK_NUMBER
, APC.AMOUNT CHECK_AMOUNT
, APC.CHECK_DATE REFUND_GL_DATE
, APIP.PERIOD_NAME REFUND_GL_PERIOD
, APC.ORG_ID
, API.INVOICE_ID
, API.INVOICE_NUM
, API.INVOICE_AMOUNT
, API.GL_DATE INVOICE_GL_DATE
, APIP.SET_OF_BOOKS_ID
, APIP.INVOICE_PAYMENT_ID
FROM AP_CHECKS_ALL APC
, AP_INVOICE_PAYMENTS_ALL APIP
, AP_INVOICES_ALL API
, PO_VENDORS PO
WHERE APIP.SET_OF_BOOKS_ID = API.SET_OF_BOOKS_ID
AND APC.ORG_ID = APIP.ORG_ID
AND APIP.ORG_ID = API.ORG_ID
AND APC.CHECK_ID = APIP.CHECK_ID
AND APIP.INVOICE_ID = API.INVOICE_ID
AND PO.VENDOR_ID = APC.VENDOR_ID
AND API.INVOICE_TYPE_LOOKUP_CODE IN ( 'CREDIT'
, 'DEBIT')
AND APC.PAYMENT_TYPE_FLAG = 'R'
AND APIP.POSTED_FLAG = 'Y'
AND APIP.REVERSAL_INV_PMT_ID IS NULL
AND EXISTS (SELECT 1
FROM GL_JE_LINES GLJL
, GL_JE_HEADERS GLJH
, AP_INVOICE_DISTRIBUTIONS_ALL APID
WHERE GLJL.LEDGER_ID = APID.SET_OF_BOOKS_ID
AND GLJL.STATUS = 'P'
AND GLJH.JE_HEADER_ID = GLJL.JE_HEADER_ID
AND GLJH.JE_FROM_SLA_FLAG = 'N'
AND GLJL.CODE_COMBINATION_ID = APID.DIST_CODE_COMBINATION_ID
AND APID.INVOICE_ID = API.INVOICE_ID
AND GLJL.REFERENCE_2 = TO_CHAR(API.INVOICE_ID) UNION SELECT 1
FROM GL_JE_LINES GLJL
, GL_JE_HEADERS GLJH
, GL_IMPORT_REFERENCES GLIR
, XLA_AE_LINES XAL
, XLA_DISTRIBUTION_LINKS XDL
, AP_INVOICE_DISTRIBUTIONS_ALL APID
WHERE GLJL.LEDGER_ID = APID.SET_OF_BOOKS_ID
AND GLIR.JE_BATCH_ID = GLJH.JE_BATCH_ID
AND GLIR.JE_HEADER_ID = GLJH.JE_HEADER_ID
AND GLIR.JE_LINE_NUM = GLJL.JE_LINE_NUM
AND GLJL.STATUS = 'P'
AND GLJH.JE_HEADER_ID = GLJL.JE_HEADER_ID
AND GLJH.JE_FROM_SLA_FLAG = 'Y'
AND GLJH.JE_SOURCE = 'PAYABLES'
AND XAL.GL_SL_LINK_ID = GLIR.GL_SL_LINK_ID
AND XAL.GL_SL_LINK_TABLE = GLIR.GL_SL_LINK_TABLE
AND XDL.AE_HEADER_ID = XAL.AE_HEADER_ID
AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = APID.INVOICE_DISTRIBUTION_ID
AND GLJL.CODE_COMBINATION_ID = APID.DIST_CODE_COMBINATION_ID
AND APID.INVOICE_ID = API.INVOICE_ID)