SQL

导出AR退款的SQL

begin
fnd_client_info.set_org_context(85);
end;

select tt.customer_id,
tt.customer_name,
tt.customer_number,
tt.gl_date as shoukuan_date,
t.GL_DATE as tuikuan_date,
t.AMOUNT_APPLIED,
t.TRX_NUMBER,
tt.receipt_number
from AR_RECEIVABLE_APPLICATIONS_V t, AR_CASH_RECEIPTS_V tt
where t.TRX_NUMBER = 'Receipt Write-off'
and t.CASH_RECEIPT_ID = tt.cash_receipt_id
and t.GL_DATE > to_date('20050101', 'YYYYMMDD')
and t.GL_DATE < to_date('20050331', 'YYYYMMDD')

;

基于gl_balances 的部门费用SQL

这是一个统计某个会计科目在某个会计期内的费用合计，在SQL中有

and b.segment2 = p_department_id
and b.segment3 = p_account_id

这即是假设你的segment2 为部门段，segment3为会计科目

budget_version_id为预算的ID，可以用select * from gl.gl_budgets t确认相应的预算id

set_of_books_id 的值可以用 select * from gl_sets_of_books确认

该SQL实际证明是完全可*和可信赖的，我们的很多取值都用这个SQL。

select sum(a.period_net_dr - a.period_net_cr)
from apps.gl_balances a, apps.gl_code_combinations b
where b.enabled_flag = 'Y'
and a.set_of_books_id = 1
and a.code_combination_id = b.code_combination_id
and nvl(a.budget_version_id, 1022) = 1022
AND (upper(a.ACTUAL_FLAG) = upper(p_money_type))
AND trim(nvl(a.PERIOD_NAME, 'XXX')) =
trim(to_char(to_date(2005 || '-' || 1, 'YYYY-MM'),
'MON-YY',
'NLS_DATE_LANGUAGE=American'))
and b.segment2 = p_department_id
and b.segment3 = p_account_id

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12122734/viewspace-331203/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12122734/viewspace-331203/