R12 AR设置数据表对应

AR设置的对应表结构如下：

1、设置OU：

     代码为：

SELECT hou.name, hoi.* FROM HR_ALL_ORGANIZATION_UNITS hou, Hr_Organization_Information hoiWHERE hou.organization_id = hoi.organization_id;

2.关联OU与Ledger

代码为：

OU与Ledger关系参考xle_le_ou_ledger_v ；

3. 定义系统选项(Receivables Manager->Setup->System->System Options)

代码为：

SELECT asp.org_id,hou.name,gl.name,gccGain.concatenated_segments gain_account, --Realized Gains AccountgccLoss.concatenated_segments loss_account, --Realized Losses AccountgccTax.concatenated_segments tax_account, --Tax AccountgccRound.concatenated_segments round_account, --Header Rounding Accountasp.run_gl_journal_import_flag, --Automatic Journal Importasp.trx_header_level_rounding, --Header Level Roundingasp.posting_days_per_cycle, --Days Posting Per Cycleasp.change_printed_invoice_flag, --Allow Change To Printed Transactionsasp.invoice_deletion_flag, --Allow Transaction Deletionasp.tax_invoice_print, --Tax Invoice Printing Options--(SELECT description FROM ar_lookups al WHERE al.lookup_type='TAX_PRINTING_OPTION' AND lookup_code = asp.tax_invoice_print)asp.document_seq_gen_level, --Documnt Number Generation Levelasp.allow_late_charges, --Assess Late Chargesasp.ai_max_memory_in_bytes, --Max Memoryasp.ai_log_file_message_level, --Log File Message Levelasp.generate_customer_number, --Automatic Customer Numberingasp.auto_site_numbering, --Automatic Site Numberingrgr.name, --Grouping Rule Nameasp.cer_split_amount, --Spit Amountasp.calc_discount_on_lines_flag, --Discount Basis--(SELECT description FROM ar_lookups al WHERE al.lookup_type='DISCOUNT_BASIS' and al.lookup_code = asp.calc_discount_on_lines_flag)asp.cer_dso_days, --Days in Days Sales Outstanding Calculationasp.partial_discount_flag, --Discount on Partical Paymentasp.site_required_flag, --Required Billing Location For Receiptsasp.print_remit_to, --Print Remit To Addressasp.auto_rec_receipts_per_commit, --Receipts Per Commitasp.auto_rec_invoices_per_commit, --Invoices Per Commitasp.default_cb_due_date, --Chargeback Due Date--(SELEC description FROM ar_lookups al WHERE al.lookup_type='DEFAULT_CB_DUE_DATE' AND al.lookup_code = asp.default_cb_due_date)asp.default_country, --Default Country--(SELECT territory_short_name FROM fnd_territories_vl ftv WHERE ftv.TERRITORY_CODE = asp.default_country)asp.default_territory, --Source of Territory--(SELECT description FROM ar_lookups al WHERE al.lookup_type='TERRITORY_DEFAULT' AND al.lookup_code = asp.default_territory)asp.rule_set_id --Application Rule Set--(SELECT rule_set_name FROM ar_app_rule_sets aars WHERE aars.rule_set_id = asp.rule_site_id) FROM AR_SYSTEM_PARAMETERS_ALL asp,hr_operating_units hou,gl_ledgers gl,RA_GROUPING_RULES rgr,gl_code_combinations_kfv gccGain,gl_code_combinations_kfv gccLoss,gl_code_combinations_kfv gccTax,gl_code_combinations_kfv gccRoundWHERE asp.org_id=81AND asp.org_id = hou.organization_idAND asp.set_of_books_id = gl.ledger_idAND asp.code_combination_id_gain = gccGain.code_combination_idAND asp.code_combination_id_loss = gccLoss.code_combination_idAND asp.location_tax_account = gccTax.code_combination_idAND asp.trx_header_round_ccid = gccRound.Code_Combination_IdAND asp.default_grouping_rule_id = rgr.grouping_rule_id; 本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/meteorlWJ/archive/2009/12/01/4916430.aspx

4、定义付款条件（Receivables Manager->Setup->Transactions->Payment Terms）

代码为：

SELECT rt.name,rt.description,rt.base_amount,rt.calc_discount_on_lines_flag, --Discount Basis--(SELECT description FROM ar_lookups al WHERE al.lookup_type='DISCOUNT_BASIS' AND al.lookup_code = rt.calc_discount_on_lines_flag)rt.first_installment_code ,--Installment Options--(SELECT description FROM ar_lookups al WHERE al.lookup_type='INSTALLMENT_OPTION' AND al.lookup_code = rt.first_installment_code)rt.start_date_active,rt.end_date_active,rtl.sequence_num,rtl.relative_amount,rtl.due_daysFROM RA_TERMS_vl rt,ra_terms_lines rtlWHERE rt.term_id = rtl.term_idAND rt.term_id =4; 本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/meteorlWJ/archive/2009/12/01/4916430.aspx

5、打开会计期(Receivables Manager->Contorls->Accounting->Open/Close Periods)

代码为：

SELECT gp.period_set_name,gp.period_name,gp.start_date,gp.end_date,gps.closing_statusFROM GL_PERIODS GP,GL_PERIOD_STATUSES GPSWHERE gp.period_name = gps.period_name AND gp.period_set_name like '%EDG%';

6、应收->事务处理
代码为:

SELECT rct.customer_trx_id, rct.trx_number, rct.trx_date, rbs.name source, rctt.name type, rctt.type class, --Class (FORM中定义了值列表对应数据INV-Invoice;CB-Chargeback;CM-Credit Memo;DM-Debit Memo; --DEP-Deposit;GUAR-Guarantee) gd.gl_date, rct.invoice_currency_code, hca.account_number bill_to_number, hp.party_name bill_to_name, hcsu.location bill_to_location, hl.address1, hl.address2, DECODE(hcas.CUST_ACCT_SITE_ID, NULL, NULL, ARH_ADDR_PKG.FORMAT_LAST_ADDRESS_LINE(hl.ADDRESS_STYLE, hl.ADDRESS3, hl.ADDRESS4, hl.CITY, hl.COUNTY, hl.STATE, hl.PROVINCE, ft.TERRITORY_SHORT_NAME, hl.POSTAL_CODE)) customer_address3,ARPT_SQL_FUNC_UTIL.GET_TERM_DETAILS(rct.TERM_ID, 'NAME') payment_term FROM ra_customer_trx_all rct, ra_batch_sources_all rbs, ra_cust_trx_types_all rctt, RA_CUST_TRX_LINE_GL_DIST_all GD, hz_cust_accounts hca, hz_parties hp, hz_cust_site_uses_all hcsu, hz_cust_acct_sites_all hcas, hz_party_sites hps, hz_locations hl, fnd_territories_vl ft, ra_customer_trx_lines_all rctl, rctl.line_number, rctl.description, NVL(rCTL.QUANTITY_CREDITED, rCTL.QUANTITY_INVOICED) quantity, rctl.unit_selling_price WHERE rct.customer_trx_id = 1002 AND rct.batch_source_id = rbs.batch_source_id AND rct.org_id = rbs.org_id AND rct.cust_trx_type_id = rctt.cust_trx_type_id AND rct.org_id = rctt.org_id AND rct.org_id = gd.org_id AND rct.CUSTOMER_TRX_ID = GD.CUSTOMER_TRX_ID AND 'REC' = GD.ACCOUNT_CLASS AND 'Y' = GD.LATEST_REC_FLAG AND rct.bill_to_customer_id = hca.cust_account_id AND hca.party_id = hp.party_id AND rct.bill_to_site_use_id = hcsu.site_use_id AND rct.org_id = hcsu.org_id AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id AND rct.org_id = hcas.org_id AND hcas.party_site_id = hps.party_site_id AND hps.location_id = hl.location_id AND hl.country = ft.TERRITORY_CODE(+) AND rct.customer_trx_id = rctl.customer_trx_id AND rct.org_id = rctl.org_id; 本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/meteorlWJ/archive/2009/12/01/4916430.aspx