select gjl.je_header_id,
gjl.je_line_num,
gjh.je_source,
gjh.name,
gjh.doc_sequence_value,
xag.third_party_id vendor_id,
xag.third_party_name vendor_name,
xag.third_party_sub_id vendor_site_id,
xag.third_party_sub_name vendor_address,
null gl_desc,
xag.trx_type_name invoice_desc, --发票说明（摘要）
gjh.default_effective_date, --GL日期
gcc.segment4, --科目
xag.entered_dr,
xag.entered_cr
from gl_je_headers_v gjh,
gl_je_lines_v gjl,
(select jeh.je_header_id je_header_id,
r.je_line_num je_line_num,
222 application_id,
adj.org_id org_id,
trxact.name trx_type_name,
ard.amount_dr entered_dr,
ard.amount_cr entered_cr,
ard.third_party_id third_party_id,
cust_acct.account_number third_party_number,
substrb(party.party_name, 1, 50) third_party_name,
ard.third_party_sub_id third_party_sub_id,
su.location third_party_sub_name
from gl_je_headers jeh,
gl_import_references r,
ar_distributions_all ard,
ar_adjustments_all adj,
ra_customer_trx_all ct,
ra_customer_trx_all ct_chg,
ar_receivables_trx_all trxact,
hz_cust_site_uses_all su,
hz_cust_accounts cust_acct,
hz_parties party,
ra_cust_trx_types_all ctt,
fnd_document_sequences seq,
gl_daily_conversion_types glct
where ard.source_table = 'ADJ'
and ard.source_id = adj.adjustment_id
and nvl(ard.org_id, -99) = nvl(adj.org_id, -99)
and glct.conversion_type(+) = ard.currency_conversion_type
and seq.doc_sequence_id(+) = adj.doc_sequence_id
and ct.customer_trx_id = adj.customer_trx_id
and nvl(ct.org_id, -99) = nvl(adj.org_id, -99)
and nvl(ctt.org_id, -99) = nvl(ct.org_id, -99)
and ctt.cust_trx_type_id = ct.cust_trx_type_id
and su.site_use_id = ard.third_party_sub_id
and nvl(su.org_id, -99) = nvl(ard.org_id, -99)
and cust_acct.cust_account_id = ard.third_party_id
and cust_acct.party_id = party.party_id
and ct_chg.customer_trx_id(+) = adj.chargeback_customer_trx_id
and nvl(ct_chg.org_id(+), -99) = nvl(adj.org_id, -99)
and trxact.receivables_trx_id(+) = adj.receivables_trx_id
and nvl(trxact.org_id(+), -99) = nvl(adj.org_id, -99)
and ard.line_id = to_number(r.reference_3)
and r.je_header_id = jeh.je_header_id
and jeh.je_category = 'Adjustment') xag,
gl_code_combinations_kfv gcc
where xag.application_id = 222
and gjh.je_source = 'Receivables' --应收款管理系统
and gjl.je_header_id = gjh.je_header_id
and gjh.status = 'P'
and gjl.code_combination_id = gcc.code_combination_id
and gcc.segment2 = nvl('02', gcc.segment2) --事业部段，02表示液态奶事业部
and gcc.segment4 = '22410302' --客护保证金 Normal 0 7.8 磅 0 2 false false false MicrosoftInternetExplorer4
and xag.je_header_id = gjl.je_header_id
and xag.je_line_num = gjl.je_line_num
and xag.third_party_id = nvl(p_vendor_name, xag.third_party_id) --供应商
and xag.third_party_sub_id = nvl(p_vendor_site, xag.third_party_sub_id) --地点
and gjh.period_name = '2009-01'
