ITPub博客

首页 > Linux操作系统 > Linux操作系统 > AR设置数据表对应

AR设置数据表对应

原创 Linux操作系统 作者:csweiliang 时间:2009-12-28 20:27:22 0 删除 编辑

不保证完全正确

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;
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;

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;

 

 

 

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

请登录后发表评论 登录
全部评论

注册时间:2009-05-09

  • 博文量
    23
  • 访问量
    42418