财务

1、验证科目组合

IF (NOT fnd_flex_keyval.validate_segs(operation => 'CHECK_COMBINATION',
                                      appl_short_name => 'SQLGL',
                                      key_flex_code => 'GL#',
                                      structure_number => 50394,
                                      concat_segments => '0.0.530101.0.0.1006001.0.0',
                                      validation_date => sysdate)) THEN
  -- return the error message
  RETURN fnd_flex_keyval.error_message;
END IF;

创建科目

方法一：fnd_flex_ext.get_ccid(application_short_name => 'SQLGL',  
                                                 key_flex_code          => 'GL#',  
                                                 structure_number       => l_id_flex_num,  
                                                 validation_date        => to_char(SYSDATE,  
                                                                                   apps.fnd_flex_ext.DATE_FORMAT),  
                                                 concatenated_segments  => x_concatenated_seg);
方法二：fnd_flex_keyval.validate_segs('CREATE_COMBINATION' --'CREATE_COMB_NO_AT'
                                       ,'SQLGL'
                                        ,'GL#'
                                        ,lv_chart_of_accounts_id
                                        ,lv_concatenated_segments);

2、参考文档
http://www.onejava.net/article/oracle/gl/contents.htm

3、弹性域
FND_KEY_FLEX.Define() 定义弹性域
FND_FLEX_EXT.GET_SEGS() 获取科目节段组合
fnd_flex_ext.get_ccid() 获取ID
FND_FLEX_KEYVAL.validate_ccid验证ccid的有效性
FND_FLEX_KEYVAL.concatenated_descriptions 得到ccid的描述
FND_KEY_FLEX.Update_Definition()

4、AP发票状态

--invoice status
SELECT ap_invoices_pkg.get_approval_status(ai.invoice_id,
                                           ai.invoice_amount,
                                           ai.payment_status_flag,
                                           ai.invoice_type_lookup_code) approval_status_lookup_code
  FROM ap_invoices_all ai
 WHERE ai.invoice_num = :invoice_num
--invoice status display
SELECT *
  FROM ap_lookup_codes alc
 WHERE alc.lookup_type = 'NLS TRANSLATION'
   AND alc.lookup_code = :approval_status_lookup_code

5、表

--内部收、付款相关
 select * from ce_bank_accounts --银行账户
 select * from ce_bank_acct_uses_ou_v--账户访问

--外部收、付款相关
--供应商银行账户
SELECT ibybanks.bank_account_id,
           ibybanks.bank_account_number,
           ibybanks.bank_account_name,
           ibybanks.bank_account_type
    /*       ibybanks.bank_name, --银行 
    ibybanks.bank_branch_name, --分行
    ibybanks.bank_account_num_electronic, --银行帐号
    ibybanks.**/
      INTO l_bank_account_id,
           l_bank_account_num,
           l_bank_account_name,
           l_bank_account_type
      FROM iby_external_payers_all iepa,
           iby_pmt_instr_uses_all  ipiua,
           iby_ext_bank_accounts_v ibybanks
     WHERE 1 = 1 --iepa.payment_function = 'CUSTOMER_PAYMENT'
          -- AND iepa.cust_account_id = p_customer_id --客户帐号ID
       AND ibybanks.bank_account_id = ipiua.instrument_id
          -- AND ipiua.instrument_type = 'BANKACCOUNT'
       AND ipiua.ext_pmt_party_id = iepa.ext_payer_id
          --AND ipiua.payment_function = 'CUSTOMER_PAYMENT'
       AND SYSDATE BETWEEN ibybanks.start_date AND
           nvl(ibybanks.end_date, SYSDATE + 1);

6、连接
http://www.360doc.com/content/15/1212/21/18225909_519929806.shtml

7、汇率

--获得汇率 Api
  Ln_Rate := Gl_Currency_Api.Get_Rate(x_From_Currency   => Lv_From_Currency_Code
                                     ,x_To_Currency     => Lv_To_Currency_Code
                                     ,x_Conversion_Date => Ld_Rate_Date
                                     ,x_Conversion_Type => Lv_Conversion_Type);

8、常用包

ap_invoices_utility_pkg
ap_utilities_pkg