oracle-EBS-PL/sql AP GL总账付款凭证包括采购订单发票明细

财务时常用到

EBS AP Payments GL Voucher Invoice

sql如下：

select distinct ac.org_id,gcc.segment1,
ass.SEGMENT1 vendor_number,
ass.VENDOR_NAME,
gjb.name batchname,
gjh.NAME journalname,
gjh.DOC_SEQUENCE_VALUE glvoucherNumber,
ac.check_id,
TO_CHAR(ac.check_date,'yyyy-mm-dd') check_date,
ac.check_number,
ac.DOC_SEQUENCE_VALUE apvoucherNumber ,
--gcc.segment3,
ac.amount,
-- nvl(xl.accounted_dr,0) accounted_dr,
-- nvl(xl.accounted_cr,0) accounted_cr,
xte.entity_code,
gir.je_header_id,
gjh.period_name,
xh.event_type_code,Xte.TRANSACTION_NUMBER
,ck.AMOUNT pay_amount_detail
,ck.INVOICE_ID
,ck.CHECK_STATUS
,poh.po_number
,poh.RECEIPT_NUM
,poh.BILL_QUANTITY
,poh.BILL_AMOUNT
,poh.BILL_PRICE
from xla.xla_ae_headers xh,
xla.xla_ae_lines xl,
xla.xla_transaction_entities xte,
gl.gl_code_combinations gcc,
ap.ap_checks_all ac,
gl.gl_import_references gir,
gl.gl_je_headers gjh,
gl.gl_je_batches gjb,
xla.xla_events xea,
ap_suppliers ass ,
(
SELECT
AIP.ACCOUNTING_DATE ACCOUNTING_DATE ,
AIP.ACCRUAL_POSTED_FLAG ACCRUAL_POSTED_FLAG ,
AIP.ACCTS_PAY_CODE_COMBINATION_ID ACCTS_PAY_CODE_COMBINATION_ID ,
AIP.AMOUNT AMOUNT ,
AIP.ASSETS_ADDITION_FLAG ASSETS_ADDITION_FLAG ,
AIP.ASSET_CODE_COMBINATION_ID ASSET_CODE_COMBINATION_ID ,
AIP.BANK_ACCOUNT_NUM BANK_ACCOUNT_NUM ,
AIP.CASH_POSTED_FLAG CASH_POSTED_FLAG ,
AIP.CHECK_ID CHECK_ID ,
AIP.CREATED_BY CREATED_BY ,
AIP.CREATION_DATE CREATION_DATE ,
AIP.EXCHANGE_DATE EXCHANGE_DATE ,
AIP.EXCHANGE_RATE_TYPE EXCHANGE_RATE_TYPE ,
AIP.FUTURE_PAY_CODE_COMBINATION_ID FUTURE_PAY_CODE_COMBINATION_ID ,
AIP.FUTURE_PAY_POSTED_FLAG FUTURE_PAY_POSTED_FLAG ,
AIP.GAIN_CODE_COMBINATION_ID GAIN_CODE_COMBINATION_ID ,
AIP.INVOICE_BASE_AMOUNT INVOICE_BASE_AMOUNT ,
AIP.INVOICE_ID INVOICE_ID ,
AIP.INVOICE_PAYMENT_ID INVOICE_PAYMENT_ID ,
AIP.JE_BATCH_ID JE_BATCH_ID ,
AIP.LAST_UPDATED_BY LAST_UPDATED_BY ,
AIP.LAST_UPDATE_DATE LAST_UPDATE_DATE ,
AIP.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN ,
AIP.LOSS_CODE_COMBINATION_ID LOSS_CODE_COMBINATION_ID ,
AIP.PAYMENT_BASE_AMOUNT PAYMENT_BASE_AMOUNT ,
AIP.PAYMENT_NUM PAYMENT_NUM ,
AIP.PERIOD_NAME PERIOD_NAME ,
AIP.POSTED_FLAG POSTED_FLAG ,
AIP.SET_OF_BOOKS_ID SET_OF_BOOKS_ID ,
AIP.INVOICE_PAYMENT_TYPE INVOICE_PAYMENT_TYPE ,
AIP.OTHER_INVOICE_ID OTHER_INVOICE_ID ,
AC.CHECK_NUMBER CHECK_NUMBER ,
AC.CHECK_DATE CHECK_DATE ,
AC.AMOUNT CHECK_AMOUNT ,
IBY1.payment_method_name CHECK_TYPE ,
ALC2.DISPLAYED_FIELD CHECK_STATUS ,
ALC3.DISPLAYED_FIELD PAYMENT_TYPE ,
AI.INVOICE_NUM INVOICE_NUM ,
AI.INVOICE_DATE INVOICE_DATE ,
AI.INVOICE_AMOUNT INVOICE_AMOUNT ,
AI.AMOUNT_PAID AMOUNT_PAID ,
AI.DESCRIPTION DESCRIPTION ,
GJB.NAME JE_BATCH_NAME ,
GSOB.NAME SET_OF_BOOKS_NAME ,
AIP.ORG_ID ORG_ID ,
AC.PAYMENT_ID ,
AIP.REVERSAL_INV_PMT_ID,
AIP.REVERSAL_FLAG

FROM AP_INVOICE_PAYMENTS_all AIP,
AP_INVOICES_ALL AI,
AP_CHECKS_ALL AC,
iby_payment_methods_vl iby1,
AP_LOOKUP_CODES ALC2,
AP_LOOKUP_CODES ALC3,
GL_JE_BATCHES GJB,
GL_SETS_OF_BOOKS GSOB,
AP_PAYMENT_SCHEDULES_ALL APS
WHERE AIP.INVOICE_ID = AI.INVOICE_ID
AND AIP.CHECK_ID = AC.CHECK_ID
AND IBY1.PAYMENT_METHOD_CODE (+) = AC.PAYMENT_METHOD_CODE
AND ALC2.LOOKUP_TYPE (+) = 'CHECK STATE'
AND ALC2.LOOKUP_CODE (+) = AC.STATUS_LOOKUP_CODE
AND ALC3.LOOKUP_TYPE (+) = 'NLS TRANSLATION'
AND ALC3.LOOKUP_CODE (+) = AIP.INVOICE_PAYMENT_TYPE
AND AIP.JE_BATCH_ID = GJB.JE_BATCH_ID (+)
AND AIP.SET_OF_BOOKS_ID = GSOB.SET_OF_BOOKS_ID
AND APS.INVOICE_ID = AIP.INVOICE_ID
AND APS.PAYMENT_NUM = AIP.PAYMENT_NUM
) ck,
(
SELECT AIA.org_id,PHA.segment1 po_number,PDA.PO_LINE_ID,AIA.invoice_id,RSH.RECEIPT_NUM, SUM(AID.QUANTITY_INVOICED) BILL_QUANTITY , SUM(AID.AMOUNT) BILL_AMOUNT,SUM(AID.unit_price) BILL_price
FROM AP_INVOICE_DISTRIBUTIONS_ALL AID
, AP_INVOICES_ALL AIA
, PO_VENDORS POV
, GL_CODE_COMBINATIONS_KFV GCC
, PO_DISTRIBUTIONS_ALL PDA
, PO_HEADERS_ALL PHA
, (SELECT TRANSACTION_ID, SHIPMENT_HEADER_ID
FROM RCV_TRANSACTIONS WHERE TRANSACTION_TYPE = 'RECEIVE') RCV
, RCV_SHIPMENT_HEADERS RSH
WHERE AID.RCV_TRANSACTION_ID IS NOT NULL
AND AID.PO_DISTRIBUTION_ID IS NOT NULL
AND AID.BASE_AMOUNT IS NOT NULL
AND AID.INVOICE_ID = AIA.INVOICE_ID
AND AIA.VENDOR_ID = POV.VENDOR_ID
AND nvl(REVERSAL_FLAG,'-') <>'Y'
AND AID.DIST_CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND AID.PO_DISTRIBUTION_ID = PDA.PO_DISTRIBUTION_ID (+)
AND PDA.PO_HEADER_ID = PHA.PO_HEADER_ID (+)
AND AID.RCV_TRANSACTION_ID = RCV.TRANSACTION_ID (+)
AND RCV.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID (+)
--and RSH.RECEIPT_NUM=30000148
--and AIA.org_id =94
group by AIA.org_id,PHA.segment1,RSH.RECEIPT_NUM, PDA.PO_LINE_ID ,AIA.invoice_id
) poh
where xh.ae_header_id = xl.ae_header_id(+)
and ac.check_id = xte.Source_Id_Int_1(+)
And xte.Entity_Id = xea.Entity_Id(+)
And xte.Application_Id = xea.Application_Id(+)
And xea.Event_Id = xh.Event_Id (+)
And xea.Application_Id= xh.Application_Id (+)

and gir.gl_sl_link_id(+) = xl.gl_sl_link_id
and gir.je_header_id = gjh.je_header_id(+)
and xl.code_combination_id = gcc.code_combination_id(+)
and gjh.je_batch_id = gjb.je_batch_id(+)
and ass.vendor_id = ac.vendor_id
and xte.entity_code = 'AP_PAYMENTS'
and gjb.PARENT_JE_BATCH_ID is null
and ac.CHECK_ID = ck.CHECK_ID
and ck.org_id = poh.org_id(+) and ck.invoice_id = poh.invoice_id(+)
--and lvmou.ou_id = ac.org_id
and gcc.segment1 = 2608
--and ac.CHECK_NUMBER in (1000002) --1000150
--and ac.org_id = 94
--and poh.po_number = '20000266'
and TO_CHAR(ac.check_date,'yyyy-mm-dd') between '2020-03-30' and '2020-03-31'
order by ass.VENDOR_NAME,ac.CHECK_ID,poh.receipt_num