以下例子中的IP和实例名均是随意所为。
单节点：
LLKU=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = LLKU)
)
)
双节点：
LLKU=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = 10.0.0.1)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP) (HOST = 10.0.0.2)(PORT = 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME =LLKU)
)
)
有时候，为了方便对历史版本修改的追踪，需要对不同版本的RDF文件进行对比，但是RDF类型的文件无法直接进行对比，需要转换成可对比文件，比如TXT,XML等类型的文件。很多人不清楚有没有办法转换成此类类型的文件，其实Oracle Report Builder提供了内置的转换功能，为了方便大家更加明了的操作，于是写下简单的操作文档，以备后用。
select ai.invoice_id 发票ID,
ai.invoice_num 发票编号,
ail.line_number 发票行号,
ap_prepay_utils_pkg.get_line_prepay_amt_remaining(ail.invoice_id,
ail.line_number) 待核销金额,
aps.vendor_name 供应商名称
from ap_invoices_all ai,
ap_invoice_lines_all ail,
ap_invoice_distributions_all aid,
gl_code_combinations gcc,
ap_suppliers aps
where 1 = 1
and ai.invoice_id = ail.invoice_id
and aps.vendor_id = ai.vendor_id
and ail.invoice_id = aid.invoice_id
and ail.line_number = aid.invoice_line_number
and aps.enabled_flag = 'Y'
and (aps.end_date_active is null or aps.end_date_active > sysdate)
and ai.invoice_type_lookup_code = 'PREPAYMENT'
–获取为可用状态的预付款发票,即已经对预付款系统内付款完成
and ap_invoices_utility_pkg.get_approval_status(ai.invoice_id,
ai.invoice_amount,
ai.payment_status_flag,
ai.invoice_type_lookup_code) =
'AVAILABLE'
and decode(ai.invoice_type_lookup_code,
'PREPAYMENT',
decode(ai.earliest_settlement_date,
'',
'PERMANENT',
'TEMPORARY')) = 'TEMPORARY'
and ai.accts_pay_code_combination_id = gcc.code_combination_id;
刚刚写一接口的时候，外围系统需要传入一个字符串来作为查询条件查询数据，最后由财务系统返回数据至外围系统，但传入的一个变量数据是一个整体，需要截取获取目标字符串才能作为查询条件，于是写一函数，记录于此以备后用：
–通过传入的字符串并根据分隔符截取出目标字符串(分隔符为',',可自定义分隔符)
declare
p_str varchar2(1000) := 'a,c,g';
l_count number := 0;
l_str varchar2(100); –目标字符串数据
begin
–首先获取字符串特别字符的数量
select nvl(length(regexp_replace(p_str, '[^,]', '')) + 1, 0)
into l_count
from dual;
–如果只有一个字符段，则不处理
if l_count = 0 then
dbms_output.put_line('Data: ' || p_str);
end if;
–如果有多个字符段，则分割
if l_count <> 0 then
for i in 1 .. l_count loop
select regexp_substr(p_str, '[^,]+', 1, i) as str
into l_str
from dual;
dbms_output.put_line('Data' || i || ':' || l_str);
end loop;
end if;
exception
when others then
dbms_output.put_line('error code :' || sqlcode);
dbms_output.put_line('error msg :' || sqlerrm);
end;
今天给客户提取所有银行账户的机构分配信息，写了以下一段SQL命令，记录下来以备后用：
SELECT CBA.BANK_ACCOUNT_NAME 账户名,
BANK_INFO.BANK_NAME 银行名称,
BANK_INFO.BRANCH_NAME 分行名称,
BANK_INFO.BANK_ACCOUNT_NUM 银行账户,
BANKACCTUSEEO.ORG_ID 机构ID,
DECODE(BANKACCTUSEEO.LEGAL_ENTITY_ID,
NULL,
FND_ACCESS_CONTROL_UTIL.GET_ORG_NAME(BANKACCTUSEEO.ORG_ID),
(SELECT XLE.NAME
FROM XLE_ENTITY_PROFILES XLE
WHERE XLE.LEGAL_ENTITY_ID = BANKACCTUSEEO.LEGAL_ENTITY_ID)) 机构描述,
BANKACCTUSEEO.AP_USE_ENABLE_FLAG 应付可用性,
BANKACCTUSEEO.AR_USE_ENABLE_FLAG 应收可用性,
BANKACCTUSEEO.END_DATE 失效日期
FROM CE_BANK_ACCT_USES_ALL BANKACCTUSEEO,
AR_RECEIVABLES_TRX ARRECEIVABLESTRXEARNED,
AR_RECEIVABLES_TRX ARRECEIVABLESTRXUNEARNED,
AR_RECEIVABLES_TRX ARRECEIVABLESTRXCLAIMINV,
CE_LOOKUPS CL,
FND_LOOKUPS FL,
CE_RECEIVABLE_ACTIVITIES_V RA,
CE_BANK_ACCOUNTS CBA,
(SELECT BANKPARTY.PARTY_NAME BANK_NAME,
BRANCHPARTY.PARTY_NAME BRANCH_NAME,
TERRITORY_SHORT_NAME COUNTRY,
BA.BANK_ACCOUNT_NUM,
BRANCHPARTY.PARTY_ID BANK_BRANCH_ID,
BA.BANK_ACCOUNT_ID,
CL.MEANING ACCOUNT_CLASS_MEANING
FROM HZ_PARTIES BANKPARTY,
HZ_PARTIES BRANCHPARTY,
HZ_ORGANIZATION_PROFILES BANKORGPROFILE,
HZ_ORGANIZATION_PROFILES BRANCHORGPROFILE,
HZ_RELATIONSHIPS BRREL,
HZ_CODE_ASSIGNMENTS BANKCA,
HZ_CODE_ASSIGNMENTS BRANCHCA,
CE_LOOKUPS CL,
FND_TERRITORIES_VL FT,
CE_BANK_ACCOUNTS BA
WHERE BANKPARTY.PARTY_TYPE = 'ORGANIZATION'
AND BANKPARTY.STATUS = 'A'
AND BANKPARTY.PARTY_ID = BANKORGPROFILE.PARTY_ID
AND SYSDATE BETWEEN TRUNC(BANKORGPROFILE.EFFECTIVE_START_DATE) AND
NVL(TRUNC(BANKORGPROFILE.EFFECTIVE_END_DATE), SYSDATE + 1)
AND BANKCA.CLASS_CATEGORY = 'BANK_INSTITUTION_TYPE'
AND BANKCA.CLASS_CODE = 'BANK'
AND BANKCA.OWNER_TABLE_NAME = 'HZ_PARTIES'
AND BANKCA.OWNER_TABLE_ID = BANKPARTY.PARTY_ID
AND NVL(BANKCA.STATUS, 'A') = 'A'
AND BRANCHPARTY.PARTY_TYPE = 'ORGANIZATION'
AND BRANCHPARTY.STATUS = 'A'
AND BRANCHORGPROFILE.PARTY_ID = BRANCHPARTY.PARTY_ID
AND SYSDATE BETWEEN
TRUNC(BRANCHORGPROFILE.EFFECTIVE_START_DATE(+)) AND
NVL(TRUNC(BRANCHORGPROFILE.EFFECTIVE_END_DATE(+)),
SYSDATE + 1)
AND BRANCHCA.CLASS_CATEGORY = 'BANK_INSTITUTION_TYPE'
AND BRANCHCA.CLASS_CODE = 'BANK_BRANCH'
AND BRANCHCA.OWNER_TABLE_NAME = 'HZ_PARTIES'
AND BRANCHCA.OWNER_TABLE_ID = BRANCHPARTY.PARTY_ID
AND NVL(BRANCHCA.STATUS, 'A') = 'A'
AND BRREL.OBJECT_ID = BANKPARTY.PARTY_ID
AND BRANCHPARTY.PARTY_ID = BRREL.SUBJECT_ID
AND BRREL.RELATIONSHIP_TYPE = 'BANK_AND_BRANCH'
AND BRREL.RELATIONSHIP_CODE = 'BRANCH_OF'
AND BRREL.STATUS = 'A'
AND BRREL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND BRREL.SUBJECT_TYPE = 'ORGANIZATION'
AND BRREL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND BRREL.OBJECT_TYPE = 'ORGANIZATION'
AND CL.LOOKUP_TYPE = 'ACCOUNT_CLASSIFICATION'
AND CL.LOOKUP_CODE = BA.ACCOUNT_CLASSIFICATION
AND BANKORGPROFILE.HOME_COUNTRY = TERRITORY_CODE
AND BA.BANK_BRANCH_ID = BRANCHPARTY.PARTY_ID) BANK_INFO
WHERE 1 = 1
AND BANK_INFO.BANK_ACCOUNT_ID = CBA.BANK_ACCOUNT_ID
AND CBA.BANK_ACCOUNT_ID = BANKACCTUSEEO.BANK_ACCOUNT_ID
AND BANKACCTUSEEO.EDISC_RECEIVABLES_TRX_ID =
ARRECEIVABLESTRXEARNED.RECEIVABLES_TRX_ID(+)
AND BANKACCTUSEEO.UNEDISC_RECEIVABLES_TRX_ID =
ARRECEIVABLESTRXUNEARNED.RECEIVABLES_TRX_ID(+)
AND BANKACCTUSEEO.AR_CLAIM_INV_ACT_ID =
ARRECEIVABLESTRXCLAIMINV.RECEIVABLES_TRX_ID(+)
AND CL.LOOKUP_TYPE = 'ORGANIZATION_TYPE'
AND CL.LOOKUP_CODE = 'LEGAL_ENTITY'
AND FL.LOOKUP_TYPE(+) = 'XTR_DEAL_PRICE_MODELS'
AND FL.LOOKUP_CODE(+) = BANKACCTUSEEO.PRICING_MODEL
AND BANKACCTUSEEO.NEW_AR_RCPTS_RECEIVABLE_TRX_ID =
RA.RECEIVABLES_TRX_ID(+);