如何用sql实现AP_payments中应付余额与GL_balance对应科目余额相同
問:
如何用sql实现ap_payments中的应付帐款与gl_balance中对应的应付科目中的同一币种的应付额相等?
我的意思就是如何从ap_payments中获取截止某一会计期间的应付帐款,但总数应该与同时的GL_balances中的应付帐款相等(同一币种)
答:
这个不是SQL该做的事情,AP明细帐与GL总帐余额能否一值和方案、用户操作有很大关系,比如是否有GL手工帐,其他模块有没有使用“应付”科目等
AP中,某科目某币种的应付账款余额,可以从账龄分析表得到,可以参考其运算逻辑
--给一个ap aging 的sql 供各位参考……,AP 应该与gl balance的余额有可能不会完全相同,因为可能有尾差产生 select e.segment4||e.segment5,a.description, b.vendor_name||'('||b.segment1||')',a.doc_sequence_value, a. invoice_num, a.invoice_currency_code, nvl(a.exchange_rate,1) exchange_rate, a.invoice_type_lookup_code, f.name, to_char(a.terms_date,'DD-MON-YYYY') term_date, to_char(c.due_date,'DD-MON-YYYY') due_date, to_char(a.gl_date,'DD-MON-YYYY') acct_date, to_char(a.gl_date,'mm') month, to_number(to_char(a.gl_date,'YYYY')) year, a.invoice_amount-sum(nvl(d.amount,0))-nvl(g.amt,0) amt_remaining_ori, decode(a.base_amount,0,decode(a.invoice_type_lookup_code,'DEBIT',a.invoice_amount,a.base_amount),null,a.invoice_amount,a.base_amount)-sum(nvl(d.invoice_base_amount,nvl(d.amount,0)))-nvl(g.base_amt,0)+ sum(nvl(i.accounted_dr,0)-nvl(i.accounted_cr,0))+sum(-nvl(h.accounted_cr,0)) amt_remaining , decode(a.invoice_currency_code,'USD',round((a.invoice_amount-sum(nvl(d.amount,0))-nvl(g.amt,0))*:p_ex_rate,2)- ( decode(a.base_amount,0,decode(a.invoice_type_lookup_code,'DEBIT',a.invoice_amount,a.base_amount),null,a.invoice_amount,a.base_amount)-sum(nvl(d.invoice_base_amount,nvl(d.amount,0)))-nvl(g.base_amt,0)+ sum(nvl(i.accounted_dr,0)-nvl(i.accounted_cr,0))+sum(-nvl(h.accounted_cr,0))),0) add_value, decode(a.invoice_currency_code,'USD',round((a.invoice_amount-sum(nvl(d.amount,0))-nvl(g.amt,0))*:p_ex_rate,2), decode(a.base_amount,0,decode(a.invoice_type_lookup_code,'DEBIT',a.invoice_amount,a.base_amount),null,a.invoice_amount,a.base_amount)-sum(nvl(d.invoice_base_amount,nvl(d.amount,0)))-nvl(g.base_amt,0)+ sum(nvl(i.accounted_dr,0)-nvl(i.accounted_cr,0))+sum(-nvl(h.accounted_cr,0))) add_value_whole, c.due_date-:p_as_of_date due_days from ap.ap_invoices_all a, ap.ap_terms_tl f, po.po_vendors b, (select invoice_id, max(due_date) due_date from ap.ap_payment_schedules_all group by invoice_id) c, ap.ap_ae_lines_all h, ap.ap_ae_lines_all i, ap.ap_invoice_payments_all d, gl.gl_code_combinations e, (select (0 - sum(nvl(amount,0))) amt,(0-sum(nvl(base_amount,nvl(amount,0)))) base_amt,invoice_id from ap.ap_invoice_distributions_all where (line_type_lookup_code = 'PREPAY' or line_type_lookup_code = 'TAX' and prepay_tax_parent_id is not null) and accounting_date<= :p_as_of_date group by invoice_id) g where a.org_id=:p_org_id and a.vendor_id=b.vendor_id and ap_fun_get_validation(a.invoice_id)='Y' and c.invoice_id=a.invoice_id and a.invoice_id=d.invoice_id(+) and a.invoice_id=g.invoice_id(+) and d.accounting_date(+)<= :p_as_of_date and d.invoice_payment_id=h.source_id(+) and h.ae_line_type_code(+)='WRITEOFF' and i.source_table(+)='AP_INVOICE_PAYMENTS' and d.invoice_payment_id=i.source_id(+) and i.ae_line_type_code(+)= 'ROUNDING' and a.gl_date<= :p_as_of_date and e.code_combination_id=a.accts_pay_code_combination_id and f.term_id=a.terms_id group by a.invoice_id, a.description,b.vendor_name||'('||b.segment1||')',a.doc_sequence_value,a.invoice_num,a.invoice_currency_code, a.invoice_amount,nvl(a.exchange_rate,1), a.invoice_type_lookup_code,decode(a.base_amount,0,decode(a.invoice_type_lookup_code,'DEBIT',a.invoice_amount,a.base_amount),null,a.invoice_amount,a.base_amount), c.due_date,a.gl_date,e.segment4||e.segment5, f.name, a.terms_date,nvl(g.amt,0),nvl(g.base_amt,0) having decode(a.base_amount,0,decode(a.invoice_type_lookup_code,'DEBIT',a.invoice_amount,a.base_amount),null,a.invoice_amount,a.base_amount)-sum(nvl(d.invoice_base_amount,nvl(d.amount,0)))-nvl(g.base_amt,0)+sum(nvl(i.accounted_dr,0)-nvl(i.accounted_cr,0))+sum(-nvl(h.accounted_cr,0)) <>0 order by e.segment4||e.segment5,b.vendor_name||'('||b.segment1||')'; CREATE OR REPLACE FUNCTION Ap_Fun_Get_Validation(INV_ID Number) RETURN VARCHAR2 IS cnt number; cnt1 number; cnt2 number; BEGIN select count(*) into cnt from ap.ap_invoices_all a, ap.ap_invoice_distributions_all b where a.invoice_id=inv_id and a.invoice_id=b.invoice_id and (b.match_status_flag in ('T','N') or b.match_status_flag is null); if cnt>0 then select count(*) into cnt1 from ap.ap_holds_all c where c.invoice_id=inv_id; select count(*) into cnt2 from ap.ap_holds_all d where d.invoice_id=inv_id and d.release_lookup_code='APPROVED'; if cnt1<>0 and cnt1=cnt2 then cnt:=0; end if; end if;