R12 AR設定資料表對應

AR設定的對應表結構如下：

1、設定OU：

     代碼為：

SELECT hou.name, hoi.*

FROM HR_ALL_ORGANIZATION_UNITS hou, Hr_Organization_Information hoi

WHERE 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 Account

gccLoss.concatenated_segments loss_account, --Realized Losses Account

gccTax.concatenated_segments tax_account, --Tax Account

gccRound.concatenated_segments round_account, --Header Rounding Account

asp.run_gl_journal_import_flag, --Automatic Journal Import

asp.trx_header_level_rounding, --Header Level Rounding

asp.posting_days_per_cycle, --Days Posting Per Cycle

asp.change_printed_invoice_flag, --Allow Change To Printed Transactions

asp.invoice_deletion_flag, --Allow Transaction Deletion

asp.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 Level

asp.allow_late_charges, --Assess Late Charges

asp.ai_max_memory_in_bytes, --Max Memory

asp.ai_log_file_message_level, --Log File Message Level

asp.generate_customer_number, --Automatic Customer Numbering

asp.auto_site_numbering, --Automatic Site Numbering

rgr.name, --Grouping Rule Name

asp.cer_split_amount, --Spit Amount

asp.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 Calculation

asp.partial_discount_flag, --Discount on Partical Payment

asp.site_required_flag, --Required Billing Location For Receipts

asp.print_remit_to, --Print Remit To Address

asp.auto_rec_receipts_per_commit, --Receipts Per Commit

asp.auto_rec_invoices_per_commit, --Invoices Per Commit

asp.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 gccRound

WHERE asp.org_id=81

AND asp.org_id = hou.organization_id

AND asp.set_of_books_id = gl.ledger_id

AND asp.code_combination_id_gain = gccGain.code_combination_id

AND asp.code_combination_id_loss = gccLoss.code_combination_id

AND asp.location_tax_account = gccTax.code_combination_id

AND asp.trx_header_round_ccid = gccRound.Code_Combination_Id

AND 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_days

FROM RA_TERMS_vl rt,

ra_terms_lines rtl

WHERE rt.term_id = rtl.term_id

AND 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_status

FROM GL_PERIODS GP,

GL_PERIOD_STATUSES GPS

WHERE 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