SELECT a.company_code,
a.company_name,
a.gl_account_nbr,
a.clearing_date,
a.clear_account_document_nbr,
a.doc_assignment,
a.fiscal_year,
a.gl_document_nbr,
a.document_item_nbr,
a.doc_posting_date,
a.document_date,
a.document_currency,
a.doc_reference,
a.document_type,
a.fiscal_period,
a.doc_posting_key,
a.doc_account_debit_or_credit,
a.business_scope,
a.doc_tax_code,
a.doc_local_currency_amount,
a.doc_currency_amount,
a.tax_amount_standard,
a.doc_currency_tax,
a.doc_item_text,
a.doc_source_order_nbr,
a.plant_code,
a.cost_center,
a.doc_payment_starting_date,
a.doc_open_item_management,
a.doc_value_date,
a.bpm_order_no,
a.process_code,
a.paymethod,
a.paystract,
a.zshare_ctr,
a.cl_document_nbr,
a.handling_accountant,
a.handling_accountant_name,
a.z_proid,
a.pernr,
a.dr_amount,
a.cr_amount,
a.paymethod_name,
a.gl_account_name,
a.anti_accounting_mark,
a.enter_time,
a.wb_debit_amount,
a.wb_crebit_amount,
a.ts_ms
– a.cnt
FROM ( SELECT a_1.company_code,
a_1.company_name,
a_1.gl_account_nbr,
a_1.clearing_date,
a_1.clear_account_document_nbr,
a_1.doc_assignment,
a_1.fiscal_year,
a_1.gl_document_nbr,
a_1.document_item_nbr,
a_1.doc_posting_date,
a_1.document_date,
a_1.document_currency,
a_1.doc_reference,
a_1.document_type,
a_1.fiscal_period,
a_1.doc_posting_key,
a_1.doc_account_debit_or_credit,
a_1.business_scope,
a_1.doc_tax_code,
a_1.doc_local_currency_amount,
a_1.doc_currency_amount,
a_1.tax_amount_standard,
a_1.doc_currency_tax,
a_1.doc_item_text,
a_1.doc_source_order_nbr,
a_1.plant_code,
a_1.cost_center,
a_1.doc_payment_starting_date,
a_1.doc_open_item_management,
a_1.doc_value_date,
a_1.bpm_order_no,
head.procode AS process_code,
payment.paymethod,
a_1.paystract,
a_1.zshare_ctr,
a_1.cl_document_nbr,
CASE
WHEN head.pernrjb IS NOT NULL AND head.pernrjb <> ‘’::text THEN head.pernrjb
ELSE bkpf.usnam
END AS handling_accountant,
pa001.sname AS handling_accountant_name,
head.proid AS z_proid,
head.pernr,
a_1.dr_amount,
a_1.cr_amount,
payment.paymethodtxt AS paymethod_name,
a_1.gl_account_name,
a_1.anti_accounting_mark,
a_1.enter_time,
a_1.wb_debit_amount,
a_1.wb_crebit_amount,
a_1.ts_ms
– row_number() OVER (PARTITION BY a_1.company_code, a_1.gl_account_nbr, a_1.fiscal_year, a_1.fiscal_period, a_1.gl_document_nbr, a_1.document_item_nbr ORDER BY head.procode DESC) AS cnt
FROM table_01 a_1
LEFT JOIN dwd_ztbpm_payment_view payment ON a_1.bpm_order_no = payment.orderno
LEFT JOIN dwd_ztfi_trpay_view trpay ON a_1.bpm_order_no = trpay.orderno
LEFT JOIN dwd_bkpf_r bkpf ON bkpf.bukrs = a_1.company_code AND bkpf.gjahr = a_1.fiscal_year AND bkpf.belnr = a_1.gl_document_nbr
LEFT JOIN (select orderno,procode,pernrjb,proid,pernr from dwd_ztbpm_post_head_r group by procode,pernrjb,proid,pernr,orderno) head ON a_1.bpm_order_no = head.orderno
LEFT JOIN (select pernr,sname from dwd_pa0001_r group by sname,pernr) pa001 ON pa001.pernr = head.pernrjb) a
– WHERE a.cnt = 1
where EXISTS ( select *from (select max(procode)procode,bsis.bukrs as bukrs,hkont,gjahr,monat,bsis.belnr,buzei from
(select orderno,procode,pernrjb,proid,pernr,reserve2,bukrs,belnr,zyear from dwd_ztbpm_post_head_r group by procode,pernrjb,proid,pernr,orderno,reserve2,bukrs,belnr,zyear)head_1
right JOIN
(select bukrs,hkont,gjahr,monat,belnr,buzei,xblnr from dwd_bsis_r)bsis
ON
btrim(bsis.xblnr) = btrim(head_1.reserve2) AND
bsis.bukrs = head_1.bukrs AND bsis.belnr = head_1.belnr AND bsis.gjahr = head_1.zyear
group by bsis.bukrs,hkont,gjahr,monat,bsis.belnr,buzei
)b
– where
– a.company_code=b.bukrs and a.gl_account_nbr=b.hkont and a.fiscal_year=b.gjahr and a.fiscal_period=b.monat
– and a.gl_document_nbr=b.belnr and a.document_item_nbr=b.buzei
)
and company_code=‘3000’ and process_code=‘F115’ 这种写法为什么和用rownumber获得最大值最后得到的数据量不一致 怎样改使用exists才能让数据量一致
最新发布
本文介绍了解决MB1C过程中遇到的过账期间限制问题，通过TCODE:OB52维护了公司1000的过账期间，并将截止年份更新至2011年。
扫一扫
1006
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
