--应收发票主表
select *
from ra_customer_trx_all a
where a.trx_number = '156640'
and a.org_id = 236;
--应收发票行表
select b.extended_amount --不含税原币金额
,b.extended_acctd_amount --不含税本币金额
,b.gross_unit_selling_price -- 含税单价
,b.gross_extended_amount --含税金额
,b.revenue_amount --收入金额
,b.unit_selling_price --不含税单价
,b.unit_standard_price
,b.*
from ra_customer_trx_lines_all b
where b.customer_trx_id = 1449740;
--发票分配表
select c.amount --不含税原原币金额
,c.acctd_amount --不含税本币金额
,c.*
from ra_cust_trx_line_gl_dist_all c
where c.customer_trx_id = 1449519;
--发票应收科目
select rctlgda.*
from ra_customer_trx_all rca
,ra_cust_trx_line_gl_dist_all rctlgda
where rca.customer_trx_id = rctlgda.customer_trx_id
and rctlgda.account_class = 'REC'
and rca.customer_trx_id = 1337786;
--发票收入和税科目
select rctlgda.*
from ra_customer_trx_all rca
,ra_cust_trx_line_gl_dist_all rctlgda
where rca.customer_trx_id = rctlgda.customer_trx_id
and rctlgda.account_class <> 'REC'
and rca.customer_trx_id = 1337786;
--或
select rctlgda.*
from ra_customer_trx_all rca
,ra_customer_trx_lines_all rcl
,ra_cust_trx_line_gl_dist_all rctlgda
where rca.customer_trx_id = rcl.customer_trx_id
and rcl.customer_trx_line_id = rctlgda.customer_trx_line_id
and rca.customer_trx_id = 1337786;
--发票收款计划表
select arp.amount_due_original --发票原始金额(含税)
,arp.tax_original --发票原始税额
,arp.tax_remaining --发票税余额
,arp.amount_applied --发票收款金额
,arp.amount_line_items_original --发票行原始金额
,arp.amount_line_items_remaining --发票行余额
,arp.amount_due_remaining --到期余额
,arp.acctd_amount_due_remaining --本币到期余额
,arp.amount_adjusted --发票调整金额
,arp.amount_credited --发票做过贷项通知单金额
,arp.freight_original
,arp.freight_remaining
,arp.discount_original
,arp.discount_remaining
,arp.discount_taken_earned
,arp.discount_taken_unearned
,arp.receivables_charges_charged
,arp.receivables_charges_remaining
,arp.*
from ar_payment_schedules_all arp
where arp.customer_trx_id = 1485432 --Num: 80210055
--发票是否做过贷项
select *
from ra_customer_trx_all rca
where rca.previous_customer_trx_id = 1337786; --ra_customer_trx_all.customer_trx_id
--发票现金收款金额
select *
from ar_receivable_applications_all ara
where ara.applied_customer_trx_id = 1337785
and ara.application_type = 'CASH'
and ara.status = 'APP';
--发票被贷项通知单核销金额
select *
from ar_receivable_applications_all ara
where ara.applied_customer_trx_id = 1337786
and ara.application_type = 'CM'
and ara.status = 'APP';
--发票调整金额
select adj.*
from ar_adjustments_all adj
,ra_customer_trx_all rct
where rct.customer_trx_id = adj.customer_trx_id
and adj.status = 'A'
and rct.customer_trx_id = 1337785;
--收款录入后产生的信息如下:
--收款主表
select *
from ar_cash_receipts_all acra
where acra.receipt_number = '20120106001';
--收款历史记录表
select *
from ar_cash_receipt_history_all acrh
where acrh.cash_receipt_id = 304387;
--收款事务处理表
select *
from ar_receivable_applications_all ara
where ara.cash_receipt_id = 304387;
--收款分配表
--收款录入时产生两条记录，SOURCE_ID分别对应
--SOURCE_TABLE为 'RA' 时SOURCE_ID对应的是AR_RECEIVABLE_APPLICATIONS_ALL.RECEIVABLE_APPLICATION_ID,为CRH时SOURCE_ID对应的是AR_CASH_RECEIPT_HISTORY_ALL.CASH_RECEIPT_HISTORY_ID
--下面SQL获取的是收款录入时产生的未核销科目及收款核销时产生的应收账款和未核销科目
select *
from ar_distributions_all ada
where ada.source_id in
(select ara.receivable_application_id
from ar_receivable_applications_all ara
where ara.cash_receipt_id = 304387)
and ada.source_table = 'RA';
--收款录入时产生两条分录：现金和未核销，下面语句获取的是现金科目
select *
from ar_distributions_all ada
where ada.source_id in
(select acrh.cash_receipt_history_id
from ar_cash_receipt_history_all acrh
where acrh.cash_receipt_id = 304387)
and ada.source_table = 'CRH';
--应收发票收货方 收单方 到期日等
select ct.customer_trx_id customer_trx_id
,ct.trx_number trx_number
,ct.old_trx_number old_trx_number
,ct_rel.trx_number ct_related_trx_number
,ct.recurred_from_trx_number ct_model_trx_number
,ct.trx_date trx_date
,arpt_sql_func_util.get_first_real_due_date(ct.customer_trx_id
,ct.term_id
,ct.trx_date) term_due_date --到期日
,ct.previous_customer_trx_id previous_customer_trx_id
,ct.initial_customer_trx_id initial_customer_trx_id
,ct.related_batch_source_id related_batch_source_id
,ct.related_customer_trx_id related_customer_trx_id
,ct.cust_trx_type_id cust_trx_type_id
,ct.batch_id batch_id
,ct.batch_source_id batch_source_id
,ct.reason_code reason_code
,ct.term_id term_id
,ct.primary_salesrep_id primary_salesrep_id
,ct.agreement_id agreement_id
,ct.credit_method_for_rules credit_method_for_rules
,ct.credit_method_for_installments credit_method_for_installments
,ct.receipt_method_id receipt_method_id
,ct.invoicing_rule_id invoicing_rule_id
,ct.ship_via ship_via
,ct.fob_point fob_point
,ct.finance_charges
,ct.complete_flag complete_flag
,ct.customer_bank_account_id customer_bank_account_id
,ct.recurred_from_trx_number recurred_from_trx_number
,ct.status_trx status_trx
,ct.default_tax_exempt_flag default_tax_exempt_flag
,ct.sold_to_customer_id sold_to_customer_id
,ct.sold_to_site_use_id sold_to_site_use_id
,ct.sold_to_contact_id sold_to_contact_id
,ct.bill_to_customer_id bill_to_customer_id
,ct.bill_to_site_use_id bill_to_site_use_id
,raa_bill.cust_acct_site_id raa_bill_to_address_id
,ct.bill_to_contact_id bill_to_contact_id
,rac_bill_party.jgzz_fiscal_code bill_to_taxpayer_id
,ct.ship_to_customer_id
,ct.ship_to_site_use_id
,raa_ship.cust_acct_site_id raa_ship_to_address_id
,ct.ship_to_contact_id
,rac_ship_party.jgzz_fiscal_code ship_to_taxpayer_id
,ct.remit_to_address_id
,ct.invoice_currency_code
,ct.created_from
,ct.set_of_books_id
,ct.printing_original_date
,ct.printing_last_printed
,ct.printing_option
,ct.printing_count
,ct.printing_pending
,ct.last_printed_sequence_num
,ct.purchase_order
,ct.purchase_order_revision
,ct.purchase_order_date
,ct.customer_reference
,ct.customer_reference_date
,ct.comments
,ct.internal_notes
,ct.exchange_rate_type
,ct.exchange_date
,ct.exchange_rate
,ct.territory_id
,ct.end_date_commitment
,ct.start_date_commitment
,ct.orig_system_batch_name
,ct.ship_date_actual
,ct.waybill_number
,ct.doc_sequence_id
,ct.doc_sequence_value
,ct.paying_customer_id
,ct.paying_site_use_id
,ct.default_ussgl_transaction_code
,ct.last_update_date
,ct.last_updated_by
,ct.creation_date
,ct.created_by
,ct.last_update_login
,ct.request_id
,rac_bill_party.party_name rac_bill_to_customer_name --收单方
,rac_bill.account_number rac_bill_to_customer_num
,su_bill.location su_bill_to_location --收单地点
,raa_bill_loc.address1 raa_bill_to_address1 --收单地址
,raa_bill_loc.address2 raa_bill_to_address2
,raa_bill_loc.address3 raa_bill_to_address3_db
,decode(raa_bill.cust_acct_site_id
,null
,null
,arh_addr_pkg.format_last_address_line(raa_bill_loc.address_style
,raa_bill_loc.address3
,raa_bill_loc.address4
,raa_bill_loc.city
,raa_bill_loc.county
,raa_bill_loc.state
,raa_bill_loc.province
,ft_bill.territory_short_name
,raa_bill_loc.postal_code)) raa_bill_to_address3
,raa_bill_loc.city raa_bill_to_city
,raa_bill_loc.county raa_bill_to_county
,raa_bill_loc.state raa_bill_to_state
,raa_bill_loc.province raa_bill_to_province
,raa_bill_loc.postal_code raa_bill_to_postal_code
,ft_bill.territory_short_name ft_bill_to_country
,decode(raa_bill.cust_acct_site_id
,null
,null
,arh_addr_pkg.arxtw_format_address(raa_bill_loc.address_style
,raa_bill_loc.address1
,raa_bill_loc.address2
,raa_bill_loc.address3
,raa_bill_loc.address4
,raa_bill_loc.city
,raa_bill_loc.county
,raa_bill_loc.state
,raa_bill_loc.province
,raa_bill_loc.postal_code
,ft_bill.territory_short_name)) raa_bill_to_concat_address
,decode(substrb(raco_bill_party.person_last_name
,1
,50)
,null
,substrb(raco_bill_party.person_first_name
,1
,40)
,substrb(raco_bill_party.person_last_name
,1
,50) || ', ' || substrb(raco_bill_party.person_first_name
,1
,40)) raco_bill_to_contact_name
,rac_ship_party.party_name rac_ship_to_customer_name --收货方
,rac_ship.account_number rac_ship_to_customer_num
,su_ship.location su_ship_to_location --收货地点
,raa_ship_loc.address1 raa_ship_to_address1 --收货地址
,raa_ship_loc.address2 raa_ship_to_address2
,raa_ship_loc.address3 raa_ship_to_address3_db
,decode(raa_ship.cust_acct_site_id
,null
,null
,arh_addr_pkg.format_last_address_line(raa_ship_loc.address_style
,raa_ship_loc.address3
,raa_ship_loc.address4
,raa_ship_loc.city
,raa_ship_loc.county
,raa_ship_loc.state
,raa_ship_loc.province
,ft_ship.territory_short_name
,raa_ship_loc.postal_code)) raa_ship_to_address3
,raa_ship_loc.city raa_ship_to_city
,raa_ship_loc.county raa_ship_to_county
,raa_ship_loc.state raa_ship_to_state
,raa_ship_loc.province raa_ship_to_province
,raa_ship_loc.postal_code raa_ship_to_postal_code
,ft_ship.territory_short_name ft_ship_to_country
,decode(raa_ship.cust_acct_site_id
,null
,null
,arh_addr_pkg.arxtw_format_address(raa_ship_loc.address_style
,raa_ship_loc.address1
,raa_ship_loc.address2
,raa_ship_loc.address3
,raa_ship_loc.address4
,raa_ship_loc.city
,raa_ship_loc.county
,raa_ship_loc.state
,raa_ship_loc.province
,raa_ship_loc.postal_code
,ft_ship.territory_short_name)) raa_ship_to_concat_address
,decode(substrb(raco_ship_party.person_last_name
,1
,50)
,null
,substrb(raco_ship_party.person_first_name
,1
,40)
,substrb(raco_ship_party.person_last_name
,1
,50) || ', ' || substrb(raco_ship_party.person_first_name
,1
,40)) raco_ship_to_contact_name
,rac_sold_party.party_name rac_sold_to_customer_name --采购方
,rac_sold.account_number rac_sold_to_customer_num
,rac_paying_party.party_name rac_paying_customer_name --付款客户
,rac_paying.account_number rac_paying_customer_num
,su_paying.location su_paying_customer_location
,raa_remit_loc.address1 raa_remit_to_address1
,raa_remit_loc.address2 raa_remit_to_address2
,raa_remit_loc.address3 raa_remit_to_address3_db
,decode(raa_remit.cust_acct_site_id
,null
,null
,arh_addr_pkg.format_last_address_line(raa_remit_loc.address_style
,raa_remit_loc.address3
,raa_remit_loc.address4
,raa_remit_loc.city
,raa_remit_loc.county
,raa_remit_loc.state
,raa_remit_loc.province
,ft_remit.territory_short_name
,raa_remit_loc.postal_code)) raa_remit_to_address3
,raa_remit_loc.city raa_remit_to_city
,raa_remit_loc.county raa_remit_to_county
,raa_remit_loc.state raa_remit_to_state
,raa_remit_loc.province raa_remit_to_province
,raa_remit_loc.postal_code raa_remit_to_postal_code
,ft_remit.territory_short_name ft_remit_to_country
,decode(raa_remit.cust_acct_site_id
,null
,null
,arh_addr_pkg.arxtw_format_address(raa_remit_loc.address_style
,raa_remit_loc.address1
,raa_remit_loc.address2
,raa_remit_loc.address3
,raa_remit_loc.address4
,raa_remit_loc.city
,raa_remit_loc.county
,raa_remit_loc.state
,raa_remit_loc.province
,raa_remit_loc.postal_code
,ft_remit.territory_short_name)) raa_concat_remit_to_address
,apba.bank_account_name apba_bank_account_name
,decode(nvl(fnd_profile.value('AR_MASK_BANK_ACCOUNT_NUMBERS')
,'F')
,'N'
,apba.bank_account_num
,'F'
,rpad(substrb(apba.bank_account_num
,1
,4)
,length(apba.bank_account_num)
,'*')
,'L'
,lpad(substrb(apba.bank_account_num
,-4)
,length(apba.bank_account_num)
,'*')) apba_bank_account_num
,apba.inactive_date apba_inactive_date
,apb.bank_name apb_customer_bank_name
,apb.bank_branch_name apb_customer_bank_branch_name
,arm.name arm_receipt_method_name
,arm.payment_type_code arm_payment_type_code
,arc.creation_method_code arc_creation_method_code
,bs.name bs_batch_source_name
,bs.auto_trx_numbering_flag bs_auto_trx_numbering_flag
,bs.copy_doc_number_flag bs_copy_doc_number_flag
,rab.name rab_batch_name
,ctt.name ctt_type_name
,ctt.type ctt_class
,arpt_sql_func_util.get_salesrep_name_number(ct.primary_salesrep_id
,'NAME') ras_primary_salesrep_name
,arpt_sql_func_util.get_salesrep_name_number(ct.primary_salesrep_id
,'NUMBER') ras_primary_salesrep_num
,rat.name rat_term_name
,rat.in_use rat_term_in_use_flag
,soa.name soa_agreement_name
,orf.description of_ship_via_name
,orf.organization_id of_organization_id
,al_fob.meaning al_fob_meaning
,al_tax.meaning al_default_tax_exempt_flag
,ct.ct_reference ct_reference
,gd.gl_date gd_gl_date
,gdct.user_conversion_type gdct_user_exchange_rate_type
,arpt_sql_func_util.get_cb_invoice(ct.customer_trx_id
,ctt.type) ct_invoice_for_cb
,arpt_sql_func_util.get_dispute_amount(ct.customer_trx_id
,ctt.type
,ctt.accounting_affect_flag) ps_dispute_amount
,arpt_sql_func_util.get_dispute_date(ct.customer_trx_id
,ctt.type
,ctt.accounting_affect_flag) ps_dispute_date
,arpt_sql_func_util.get_max_dispute_date(ct.customer_trx_id
,ctt.type
,ctt.accounting_affect_flag) dh_max_dispute_date
,arpt_sql_func_util.get_revenue_recog_run_flag(ct.customer_trx_id
,ct.invoicing_rule_id) rev_recog_run_flag
,arpt_sql_func_util.get_posted_flag(ct.customer_trx_id
,ctt.post_to_gl
,ct.complete_flag) posted_flag --过账标识？
,arpt_sql_func_util.get_selected_for_payment_flag(ct.customer_trx_id
,ctt.accounting_affect_flag
,ct.complete_flag) selected_for_payment_flag
,arpt_sql_func_util.get_activity_flag(ct.customer_trx_id
,ctt.accounting_affect_flag
,ct.complete_flag
,ctt.type
,ct.initial_customer_trx_id
,ct.previous_customer_trx_id) activity_flag
,ctt.post_to_gl ctt_post_to_gl_flag
,ctt.accounting_affect_flag ctt_open_receivables_flag
,ctt.allow_freight_flag ctt_allow_freight_flag
,ctt.creation_sign ctt_creation_sign
,ctt.allow_overapplication_flag ctt_allow_overapplication_flag
,ctt.natural_application_only_flag ctt_natural_app_only_flag
,ctt.tax_calculation_flag ctt_tax_calculation_flag
,ctt.default_status ctt_default_status
,ctt.default_term ctt_default_term
,ctt.default_printing_option ctt_default_printing_option
,decode(ct.invoicing_rule_id
,null
,'N'
,'Y') rules_flag
,decode(ct.printing_last_printed
,null
,'N'
,'Y') printed_flag
,decode(ct.previous_customer_trx_id
,null
,'N'
,'Y') cm_against_trx_flag
,su_bill.status site_status
,rac_bill.status customer_status
,arpt_sql_func_util.get_override_terms(ct.bill_to_customer_id
,ct.bill_to_site_use_id) override_terms
,decode(ct.initial_customer_trx_id
,null
,decode(ctt.type
,'DEP'
,'N'
,'GUAR'
,'N'
,'CB'
,'N'
,'Y')
,'Y') commitments_exist_flag
,decode(ct.agreement_id
,null
,decode(ctt.type
,'CM'
,'N'
,arpt_sql_func_util.get_agreements_exist_flag(ct.bill_to_customer_id
,ct.trx_date))
,'Y') agreements_exist_flag
,fnd_attachment_util_pkg.get_atchmt_exists('RA_CUSTOMER_TRX'
,ct.customer_trx_id) atchmt_flag
,ct.reversed_cash_receipt_id reversed_cash_receipt_id
from ra_cust_trx_line_gl_dist_all gd
,ra_customer_trx_all ct
,hz_cust_accounts rac_bill
,hz_parties rac_bill_party
,hz_cust_accounts rac_ship
,hz_parties rac_ship_party
,hz_cust_accounts rac_sold
,hz_parties rac_sold_party
,hz_cust_accounts rac_paying
,hz_parties rac_paying_party
,hz_cust_site_uses_all su_bill
,hz_cust_site_uses_all su_ship
,hz_cust_site_uses_all su_paying
,fnd_territories_vl ft_bill
,fnd_territories_vl ft_ship
,fnd_territories_vl ft_remit
,hz_cust_acct_sites_all raa_bill
,hz_party_sites raa_bill_ps
,hz_locations raa_bill_loc
,hz_cust_acct_sites_all raa_ship
,hz_party_sites raa_ship_ps
,hz_locations raa_ship_loc
,hz_cust_acct_sites_all raa_remit
,hz_party_sites raa_remit_ps
,hz_locations raa_remit_loc
,hz_cust_account_roles raco_ship
,hz_parties raco_ship_party
,hz_relationships raco_ship_rel
,hz_cust_account_roles raco_bill
,hz_parties raco_bill_party
,hz_relationships raco_bill_rel
,ap_bank_accounts_all apba
,ap_bank_branches apb
,ar_receipt_methods arm
,ar_receipt_classes arc
,ra_batch_sources_all bs
,ra_batches_all rab
,ra_cust_trx_types_all ctt
,ra_terms rat
,so_agreements soa
,org_freight orf
,gl_daily_conversion_types gdct
,ra_customer_trx_all ct_rel
,ar_lookups al_fob
,ar_lookups al_tax
where ct.customer_trx_id = gd.customer_trx_id
and 'REC' = gd.account_class
and 'Y' = gd.latest_rec_flag
and ct.related_customer_trx_id = ct_rel.customer_trx_id(+)
and ct.bill_to_customer_id = rac_bill.cust_account_id
and rac_bill.party_id = rac_bill_party.party_id
and ct.ship_to_customer_id = rac_ship.cust_account_id(+)
and rac_ship.party_id = rac_ship_party.party_id(+)
and ct.sold_to_customer_id = rac_sold.cust_account_id(+)
and rac_sold.party_id = rac_sold_party.party_id(+)
and ct.paying_customer_id = rac_paying.cust_account_id(+)
and rac_paying.party_id = rac_paying_party.party_id(+)
and ct.bill_to_site_use_id = su_bill.site_use_id
and ct.ship_to_site_use_id = su_ship.site_use_id(+)
and ct.paying_site_use_id = su_paying.site_use_id(+)
and su_bill.cust_acct_site_id = raa_bill.cust_acct_site_id
and raa_bill.party_site_id = raa_bill_ps.party_site_id
and raa_bill_loc.location_id = raa_bill_ps.location_id
and su_ship.cust_acct_site_id = raa_ship.cust_acct_site_id(+)
and raa_ship.party_site_id = raa_ship_ps.party_site_id(+)
and raa_ship_loc.location_id(+) = raa_ship_ps.location_id
and ct.bill_to_contact_id = raco_bill.cust_account_role_id(+)
and raco_bill.party_id = raco_bill_rel.party_id(+)
and raco_bill_rel.subject_table_name(+) = 'HZ_PARTIES'
and raco_bill_rel.object_table_name(+) = 'HZ_PARTIES'
and raco_bill_rel.directional_flag(+) = 'F'
and raco_bill.role_type(+) = 'CONTACT'
and raco_bill_rel.subject_id = raco_bill_party.party_id(+)
and ct.ship_to_contact_id = raco_ship.cust_account_role_id(+)
and raco_ship.party_id = raco_ship_rel.party_id(+)
and raco_ship_rel.subject_table_name(+) = 'HZ_PARTIES'
and raco_ship_rel.object_table_name(+) = 'HZ_PARTIES'
and raco_ship_rel.directional_flag(+) = 'F'
and raco_ship.role_type(+) = 'CONTACT'
and raco_ship_rel.subject_id = raco_ship_party.party_id(+)
and ct.remit_to_address_id = raa_remit.cust_acct_site_id(+)
and raa_remit.party_site_id = raa_remit_ps.party_site_id(+)
and raa_remit_loc.location_id(+) = raa_remit_ps.location_id
and raa_bill_loc.country = ft_bill.territory_code(+)
and raa_ship_loc.country = ft_ship.territory_code(+)
and raa_remit_loc.country = ft_remit.territory_code(+)
and ct.customer_bank_account_id = apba.bank_account_id(+)
and apba.bank_branch_id = apb.bank_branch_id(+)
and ct.receipt_method_id = arm.receipt_method_id(+)
and arm.receipt_class_id = arc.receipt_class_id(+)
and ct.batch_source_id = bs.batch_source_id
and ct.batch_id = rab.batch_id(+)
and ct.cust_trx_type_id = ctt.cust_trx_type_id
and ctt.type <> 'BR'
and ct.term_id = rat.term_id(+)
and ct.agreement_id = soa.agreement_id(+)
and ct.exchange_rate_type = gdct.conversion_type(+)
and 'FOB' = al_fob.lookup_type(+)
and ct.fob_point = al_fob.lookup_code(+)
and ct.ship_via = orf.freight_code(+)
and ct.org_id = orf.organization_id(+)
and 'TAX_CONTROL_FLAG' = al_tax.lookup_type(+)
and ct.default_tax_exempt_flag = al_tax.lookup_code(+)
and raco_ship_rel.status(+) = 'A'
and raco_bill_rel.status(+) = 'A'
and ct.customer_trx_id = 1485432;
--发票收款应用界面脚本
select app.cash_receipt_id cash_receipt_id
,ps_inv.customer_trx_id customer_trx_id
,to_number('') cm_customer_trx_id
,app.last_update_date last_update_date
,app.last_updated_by last_updated_by
,app.creation_date creation_date
,app.created_by created_by
,app.last_update_login last_update_login
,app.program_application_id program_application_id
,app.program_id program_id
,app.program_update_date program_update_date
,app.request_id request_id
,cr.receipt_number receipt_number
,'Y' applied_flag
,ps_inv.customer_id customer_id
,substrb(party.party_name
,1
,50) customer_name
,cust.account_number customer_number
,ps_inv.trx_number trx_number
,to_number(decode(sign(app.applied_payment_schedule_id)
,-1
,null
,ps_inv.terms_sequence_number)) installment
,app.amount_applied amount_applied
,nvl(app.amount_applied_from
,app.amount_applied) amount_applied_from
,app.trans_to_receipt_rate trans_to_receipt_rate
,to_number(decode(sign(app.applied_payment_schedule_id)
,-1
,null
,nvl(app.earned_discount_taken
,0) + nvl(app.unearned_discount_taken
,0))) discount
,app.earned_discount_taken discounts_earned
,app.unearned_discount_taken discounts_unearned
,ps_inv.discount_taken_earned discount_taken_earned
,ps_inv.discount_taken_unearned discount_taken_unearned
,to_number(decode(sign(app.applied_payment_schedule_id)
,-1
,null
,ps_inv.amount_due_remaining)) amount_due_remaining
,decode(sign(app.applied_payment_schedule_id)
,-1
,to_date(null)
,ps_inv.due_date) due_date
,ps_inv.status status
,ps_inv.term_id term_id
,decode(sign(app.applied_payment_schedule_id)
,-1
,null
,l_class.meaning) trx_class_name
,ps_inv.class trx_class_code
,ctt.name trx_type_name
,ctt.cust_trx_type_id cust_trx_type_id
,ct.trx_date trx_date
,su.location location_name
,ct.bill_to_site_use_id bill_to_site_use_id
,to_number(decode(sign(app.applied_payment_schedule_id)
,-1
,null
,trunc(app.apply_date) - trunc(ps_inv.due_date))) days_late
,ctl.line_number line_number
,ctl.customer_trx_line_id customer_trx_line_id
,app.apply_date apply_date
,app.gl_date gl_date
,app.gl_posted_date gl_posted_date
,app.reversal_gl_date reversal_gl_date
,ps_inv.exchange_rate exchange_rate
,decode(sign(app.applied_payment_schedule_id)
,-1
,cr.currency_code
,ps_inv.invoice_currency_code) invoice_currency_code
,ps_inv.amount_due_original amount_due_original
,ps_inv.amount_in_dispute amount_in_dispute
,ps_inv.amount_line_items_original amount_line_items_original
,to_number(decode(sign(app.applied_payment_schedule_id)
,-1
,null
,ps_inv.acctd_amount_due_remaining)) acctd_amount_due_remaining
,app.acctd_amount_applied_to acctd_amount_applied_to
,app.acctd_amount_applied_from acctd_amount_applied_from
,to_number(decode(sign(app.applied_payment_schedule_id)
,-1
,null
,app.acctd_amount_applied_from -
nvl(app.acctd_amount_applied_to
,app.acctd_amount_applied_from))) exchange_gain_loss
,ps_inv.discount_remaining discount_remaining
,arpt_sql_func_util.get_term_details(ps_inv.term_id
,'CALC_DISCOUNT_ON_LINES_FLAG') calc_discount_on_lines_flag
,arpt_sql_func_util.get_term_details(ps_inv.term_id
,'PARTIAL_DISCOUNT_FLAG') partial_discount_flag
,ctt.allow_overapplication_flag allow_overapplication_flag
,ctt.natural_application_only_flag natural_application_only_flag
,ctt.creation_sign creation_sign
,ps_inv.payment_schedule_id applied_payment_schedule_id
,app.ussgl_transaction_code ussgl_transaction_code
,app.ussgl_transaction_code_context ussgl_transaction_code_context
,ct.purchase_order purchase_order
,ct.doc_sequence_id trx_doc_sequence_id
,ct.doc_sequence_value trx_doc_sequence_value
,bs.name trx_batch_source_name
,ps_inv.amount_adjusted amount_adjusted
,ps_inv.amount_adjusted_pending amount_adjusted_pending
,ps_inv.amount_line_items_remaining amount_line_items_remaining
,ps_inv.freight_original freight_original
,ps_inv.freight_remaining freight_remaining
,ps_inv.receivables_charges_remaining receivables_charges_remaining
,ps_inv.tax_original tax_original
,ps_inv.tax_remaining tax_remaining
,ps_inv.selected_for_receipt_batch_id selected_for_receipt_batch_id
,app.receivable_application_id receivable_application_id
,app.secondary_application_ref_id secondary_application_ref_id
,app.comments
,ctt.attribute10 transaction_category
,app.receivables_trx_id receivables_trx_id
,decode(ps_inv.class
,'PMT'
,art.name
,decode(sign(app.applied_payment_schedule_id)
,-1
,art.name
,null)) rec_activity_name
,app.application_ref_type
,arpt_sql_func_util.get_lookup_meaning(decode(app.applied_payment_schedule_id
,-7
,'AR_PREPAYMENT_TYPE'
,'APPLICATION_REF_TYPE')
,app.application_ref_type) application_ref_type_meaning
,app.application_ref_id
,app.application_ref_num
,app.payment_set_id
,app.application_ref_reason
,decode(app.secondary_application_ref_id
,null
,decode(ps_inv.class
,'CM'
,arpt_sql_func_util.get_lookup_meaning('CREDIT_MEMO_REASON'
,ct.reason_code)
,arpt_sql_func_util.get_lookup_meaning('INVOICING_REASON'
,ct.reason_code))
,arp_deduction.get_tm_oracle_reason(app.secondary_application_ref_id)) application_ref_reason_meaning
,app.customer_reference
,app.customer_reason
,app.applied_rec_app_id
,app.secondary_application_ref_type secondary_application_ref_type
,app.secondary_application_ref_num secondary_application_ref_num
from ar_receivable_applications_all app
,ar_cash_receipts_all cr
,ar_payment_schedules_all ps_inv
,hz_cust_accounts cust
,hz_parties party
,ra_customer_trx_all ct
,ra_cust_trx_types_all ctt
,ra_customer_trx_lines_all ctl
,ra_batch_sources_all bs
,hz_cust_site_uses_all su
,ar_cons_inv_all ci
,ar_lookups l_class
,ar_receivables_trx_all art
where app.display = 'Y'
and app.cash_receipt_id = cr.cash_receipt_id
and ct.customer_trx_id(+) = ps_inv.customer_trx_id
and bs.batch_source_id(+) = ct.batch_source_id
and ctt.cust_trx_type_id(+) = ps_inv.cust_trx_type_id
and cust.cust_account_id(+) = ps_inv.customer_id
and cust.party_id = party.party_id(+)
and su.site_use_id(+) = ps_inv.customer_site_use_id
and ctl.customer_trx_line_id(+) = app.applied_customer_trx_line_id
and ps_inv.class = l_class.lookup_code
and l_class.lookup_type = 'INV/CM'
and ci.cons_inv_id(+) = ps_inv.cons_inv_id
and app.applied_payment_schedule_id = ps_inv.payment_schedule_id
and art.receivables_trx_id(+) = app.receivables_trx_id
and cr.cash_receipt_id = 294508;
--应收发票及收款产生的会计科目
--发票产生的会计科目
select gp.period_name
,a.currency_code
,mew_get_transaction_party_f(143
,a.third_party_number
,'C') third_party_number
,gcc.segment4 || '.' || gcc.segment5 concatenated_segments
,nvl(a.entered_dr
,0) entered_dr
,nvl(a.entered_cr
,0) entered_cr
,nvl(a.accounted_dr
,0) accounted_dr
,nvl(a.accounted_cr
,0) accounted_cr
from xla_ar_inv_ael_sl_v a
,gl_code_combinations gcc
,gl_period_statuses gp
where a.application_id = 222
and a.set_of_books_id = 143
and a.trx_hdr_table = 'CT'
-- AND A.TRX_HDR_ID = 1484132
and a.org_id between 236 and 242
and gp.period_name = p_period
and (gcc.segment4 = p_account or p_account is null)
and a.code_combination_id = gcc.code_combination_id
and a.accounting_date >= gp.start_date
and a.accounting_date < gp.end_date + 1
and gp.application_id = 222
and gp.set_of_books_id = 143
and a.gl_transfer_status = 'Y'
union all
--收款产生的会计科目
select gp.period_name
,a.currency_code
,mew_get_transaction_party_f(143
,a.third_party_number
,'C') third_party_number
,gcc.segment4 || '.' || gcc.segment5 concatenated_segments
,nvl(a.entered_dr
,0) entered_dr
,nvl(a.entered_cr
,0) entered_cr
,nvl(a.accounted_dr
,0) accounted_dr
,nvl(a.accounted_cr
,0) accounted_cr
from xla_ar_rec_ael_sl_v a
,gl_code_combinations gcc
,gl_period_statuses gp
where a.application_id = 222
and a.set_of_books_id = 143
and a.trx_hdr_table = 'CR'
-- AND A.TRX_HDR_ID = 294467
and a.code_combination_id = gcc.code_combination_id
and a.accounting_date >= gp.start_date
and a.accounting_date < gp.end_date + 1
and a.org_id between 236 and 242
and gp.period_name = p_period
and (gcc.segment4 = p_account or p_account is null)
and gp.application_id = 222
and gp.set_of_books_id = 143
and a.gl_transfer_status = 'Y'
--应收模块发票及收款数据与总账的关联即如何由总账追溯到应收
--从总账日记账追溯到应收发票
select gjl.je_line_num
,gjl.entered_dr
,gjl.entered_cr
,gjl.accounted_dr
,gjl.accounted_cr
,gjl.stat_amount
,gjl.description
,gjl.translation_rate
,gjl.translation_amount
,gjl.ussgl_transaction_code
,gjl.set_of_books_id
,gjl.code_combination_id
,gjl.row_id
,gjl.status
,gjl.period_name
,gjl.ignore_rate_flag
,gjl.effective_date
,gjl.reference_1
,gjl.reference_2
,gjl.reference_3
,gjl.reference_4
,gjl.reference_5
,gjl.reference_6
,gjl.reference_7
,gjl.reference_8
,gjl.reference_9
,gjl.reference_10
,gjl.context
,gjl.attribute1
,gjl.attribute2
,gjl.attribute3
,gjl.attribute4
,gjl.attribute5
,gjl.attribute6
,gjl.attribute7
,gjl.attribute8
,gjl.attribute9
,gjl.attribute10
,gjl.context2
,gjl.attribute11
,gjl.attribute12
,gjl.attribute13
,gjl.attribute14
,gjl.attribute15
,gjl.attribute16
,gjl.attribute17
,gjl.attribute18
,gjl.attribute19
,gjl.attribute20
,gjl.context3
,gjl.tax_code
,gjl.invoice_date
,gjl.invoice_identifier
,gjl.invoice_amount
,gjl.no1
,gjl.taxable_line_flag
,gjl.tax_type_code
,gjl.tax_code_id
,gjl.tax_rounding_rule_code
,gjl.amount_includes_tax_flag
,gjl.tax_document_identifier
,gjl.tax_document_date
,gjl.tax_customer_name
,gjl.tax_customer_reference
,gjl.tax_registration_number
,gjl.tax_line_flag
,gjl.tax_group_id
,gjl.context4
,gjl.jgzz_recon_context
,gjl.jgzz_recon_status
,gjl.jgzz_recon_date
,gjl.jgzz_recon_id
,gjl.jgzz_recon_ref
,gjl.je_header_id
,gjl.global_attribute1
,gjl.global_attribute2
,gjl.global_attribute3
,gjl.global_attribute4
,gjl.global_attribute5
,gjl.global_attribute6
,gjl.global_attribute7
,gjl.global_attribute8
,gjl.global_attribute9
,gjl.global_attribute10
,gjl.global_attribute_category
,gjl.subledger_doc_sequence_id
,gjl.subledger_doc_sequence_value
,gjl.creation_date
,gjl.created_by
,gjl.last_update_date
,gjl.last_updated_by
,gjl.last_update_login
,gjl.wad_row_id
,gjl.transaction_type
,gjl.transaction_detail_type
,gjl.exception_flag
,gjl.amounts_filled_flag
,gjl.to_currency_code
,gjl.translated_dr
,gjl.translated_cr
from gl_je_lines_v gjl
,ra_customer_trx_all rcta
,ra_cust_trx_line_gl_dist_all rclgda
where (je_header_id = 3332300)
and description like '%80210595%' --事务处理编号
and rclgda.cust_trx_line_gl_dist_id = gjl.reference_3
and gjl.reference_4 = rcta.trx_number
and gjl.reference_2 = rcta.customer_trx_id
and gjl.subledger_doc_sequence_id = rcta.doc_sequence_id
and gjl.subledger_doc_sequence_value = rcta.doc_sequence_value
order by je_line_num;
--从总账日记账追溯到应收收款
select gjl.je_line_num
,gjl.entered_dr
,gjl.entered_cr
,gjl.accounted_dr
,gjl.accounted_cr
,gjl.stat_amount
,gjl.description
,gjl.translation_rate
,gjl.translation_amount
,gjl.ussgl_transaction_code
,gjl.set_of_books_id
,gjl.code_combination_id
,gjl.row_id
,gjl.status
,gjl.period_name
,gjl.ignore_rate_flag
,gjl.effective_date
,gjl.reference_1
,gjl.reference_2
,gjl.reference_3
,gjl.reference_4
,gjl.reference_5
,gjl.reference_6
,gjl.reference_7
,gjl.reference_8
,gjl.reference_9
,gjl.reference_10
,gjl.context
,gjl.attribute1
,gjl.attribute2
,gjl.attribute3
,gjl.attribute4
,gjl.attribute5
,gjl.attribute6
,gjl.attribute7
,gjl.attribute8
,gjl.attribute9
,gjl.attribute10
,gjl.context2
,gjl.attribute11
,gjl.attribute12
,gjl.attribute13
,gjl.attribute14
,gjl.attribute15
,gjl.attribute16
,gjl.attribute17
,gjl.attribute18
,gjl.attribute19
,gjl.attribute20
,gjl.context3
,gjl.tax_code
,gjl.invoice_date
,gjl.invoice_identifier
,gjl.invoice_amount
,gjl.no1
,gjl.taxable_line_flag
,gjl.tax_type_code
,gjl.tax_code_id
,gjl.tax_rounding_rule_code
,gjl.amount_includes_tax_flag
,gjl.tax_document_identifier
,gjl.tax_document_date
,gjl.tax_customer_name
,gjl.tax_customer_reference
,gjl.tax_registration_number
,gjl.tax_line_flag
,gjl.tax_group_id
,gjl.context4
,gjl.jgzz_recon_context
,gjl.jgzz_recon_status
,gjl.jgzz_recon_date
,gjl.jgzz_recon_id
,gjl.jgzz_recon_ref
,gjl.je_header_id
,gjl.global_attribute1
,gjl.global_attribute2
,gjl.global_attribute3
,gjl.global_attribute4
,gjl.global_attribute5
,gjl.global_attribute6
,gjl.global_attribute7
,gjl.global_attribute8
,gjl.global_attribute9
,gjl.global_attribute10
,gjl.global_attribute_category
,gjl.subledger_doc_sequence_id
,gjl.subledger_doc_sequence_value
,gjl.creation_date
,gjl.created_by
,gjl.last_update_date
,gjl.last_updated_by
,gjl.last_update_login
,gjl.wad_row_id
,gjl.transaction_type
,gjl.transaction_detail_type
,gjl.exception_flag
,gjl.amounts_filled_flag
,gjl.to_currency_code
,gjl.translated_dr
,gjl.translated_cr
,ada.line_id
from gl_je_lines_v gjl
,ar_cash_receipts_all acra
,ar_distributions_all ada
where (gjl.je_header_id = 3332299)
and gjl.description like '%20120113001%' --收款编号
and gjl.subledger_doc_sequence_id = acra.doc_sequence_id
and gjl.subledger_doc_sequence_value = acra.doc_sequence_value
and ada.line_id = gjl.reference_3 --收款分配ID
order by je_line_num;
--AR与OM关联关系
interface_line_context : 'ORDER ENTRY'
interface_line_attribute1: order_number
interface_line_attribute2: order_type
interface_line_attribute3: delivery_id
interface_line_attribute4: waybill
interface_line_attribute5: count
interface_line_attribute6: order line id
interface_line_attribute10 warehouse id