目标：查询标准客户地点层收件人存储的视图或基表
1. 进入相应页面，找到需要查询的字段，如下图
2. 点击全部展开，ctrl+f搜索“收件人”，，如下图所示
3. 获取“HzPuiPartySiteVO”信息及相应字段信息“Addresse”，如下图所示
4. 点击全部隐藏及业务组件，如下图所示
5. 在打开的业务组件，根据关键字“HzPuiPartySiteVO”搜索结果，如下图所示
6. 点击搜索出结果连接，即可找到相应所需sql命令，如下图所示
SELECT CR.CASH_RECEIPT_ID CASH_RECEIPT_ID,
CRH_CURRENT.CASH_RECEIPT_HISTORY_ID CASH_RECEIPT_HISTORY_ID,
CR.AMOUNT AMOUNT,
CRH_CURRENT.ACCTD_AMOUNT FUNCTIONAL_AMOUNT,
CRH_CURRENT.AMOUNT NET_AMOUNT,
CR.RECEIPT_NUMBER RECEIPT_NUMBER,
CR.RECEIPT_DATE RECEIPT_DATE,
CR.TYPE TYPE,
CR.STATUS RECEIPT_STATUS,
ARPT_SQL_FUNC_UTIL.get_lookup_meaning('CHECK_STATUS', CR.STATUS) RECEIPT_STATUS_DSP,
CR.COMMENTS COMMENTS,
CR.MISC_PAYMENT_SOURCE MISC_PAYMENT_SOURCE,
CR.EXCHANGE_RATE EXCHANGE_RATE,
CR.EXCHANGE_DATE EXCHANGE_RATE_DATE,
CR.EXCHANGE_RATE_TYPE EXCHANGE_RATE_TYPE,
GL_DCT.USER_CONVERSION_TYPE EXCHANGE_RATE_TYPE_DSP,
CR.DOC_SEQUENCE_ID DOC_SEQUENCE_ID,
CR.DOC_SEQUENCE_VALUE DOCUMENT_NUMBER,
CR.USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE,
CR.CUSTOMER_RECEIPT_REFERENCE CUSTOMER_RECEIPT_REFERENCE,
REC_METHOD.NAME PAYMENT_METHOD_DSP,
REC_METHOD.PAYMENT_CHANNEL_CODE PAYMENT_TYPE_CODE,
REC_METHOD.RECEIPT_METHOD_ID RECEIPT_METHOD_ID,
RC.NAME RECEIPT_CLASS_DSP,
RC.BILL_OF_EXCHANGE_FLAG BILL_OF_EXCHANGE_FLAG,
RC.CREATION_METHOD_CODE CREATION_METHOD_CODE,
CR.PAY_FROM_CUSTOMER CUSTOMER_ID,
RTRIM(RTRIM(SUBSTRB(PARTY.PARTY_NAME, 1, 50)), to_multi_byte(' ')) CUSTOMER_NAME,
CUST.ACCOUNT_NUMBER CUSTOMER_NUMBER,
arp_etax_util.get_taxpayer_masked_value(PARTY.JGZZ_FISCAL_CODE) TAXPAYER_ID,
SITE_USES.LOCATION LOCATION,
CR.CUSTOMER_SITE_USE_ID CUSTOMER_SITE_USE_ID,
CR.CUSTOMER_BANK_ACCOUNT_ID CUSTOMER_BANK_ACCOUNT_ID,
CR.CUSTOMER_BANK_BRANCH_ID CUSTOMER_BANK_BRANCH_ID,
DECODE(RC.CREATION_METHOD_CODE,
'MANUAL',
EBA.BANK_ACCOUNT_NUMBER,
null) CUSTOMER_BANK_ACCOUNT,
DECODE(RC.CREATION_METHOD_CODE,
'MANUAL',
EBA.BANK_ACCOUNT_NUMBER,
null) CUSTOMER_BANK_ACCOUNT_NUM,
DECODE(RC.CREATION_METHOD_CODE, 'MANUAL', EBA.BANK_NAME, NULL) CUSTOMER_BANK_NAME,
DECODE(RC.CREATION_METHOD_CODE, 'MANUAL', EBA.BANK_BRANCH_NAME, NULL) CUSTOMER_BANK_BRANCH,
CRH_FIRST_POSTED.BATCH_ID BATCH_ID,
DECODE(RC.CREATION_METHOD_CODE, 'BR', BAT_BR.NAME, BAT.NAME) BATCH_NAME /* 20-APR-2000 J Rautiainen BR Implementation */,
DIST_SET.DISTRIBUTION_SET_NAME DISTRIBUTION_SET,
CR.DISTRIBUTION_SET_ID DISTRIBUTION_SET_ID,
CR.DEPOSIT_DATE DEPOSIT_DATE,
CR.REFERENCE_TYPE REFERENCE_TYPE,
CR.VAT_TAX_ID VAT_TAX_ID,
VAT.TAX_RATE_CODE TAX_CODE,
CR.TAX_RATE TAX_RATE,
NVL(ARP_MISC_CASH_DIST.MISC_CASH_TAX_LINE_CCID_IN_ARD(CR.CASH_RECEIPT_ID),
accounts.TAX_ACCOUNT_CCID) TAX_ACCOUNT_ID,
VAT.ALLOW_ADHOC_TAX_RATE_FLAG ADHOC_FLAG,
L_REF_TYPE.MEANING REFERENCE_TYPE_DSP,
CR.REFERENCE_ID REFERENCE_ID,
CR.REMIT_BANK_ACCT_USE_ID REMIT_BANK_ACCT_USE_ID,
BB.BANK_NAME REMIT_BANK_NAME,
BB.BRANCH_PARTY_ID REMITTANCE_BANK_BRANCH_ID,
BB.BANK_BRANCH_NAME REMIT_BANK_BRANCH,
CBA.CURRENCY_CODE REMIT_BANK_CURRENCY,
CRH_CURRENT.FACTOR_DISCOUNT_AMOUNT FACTOR_DISCOUNT_AMOUNT,
PS.DUE_DATE MATURITY_DATE,
PS.PAYMENT_SCHEDULE_ID PAYMENT_SCHEDULE_ID,
ARPT_SQL_FUNC_UTIL.get_lookup_meaning('RECEIPT_CREATION_STATUS',
CRH_CURRENT.STATUS) STATE_DSP,
CRH_CURRENT.STATUS STATE,
CRH_CURRENT.GL_POSTED_DATE POSTED_DATE,
REC_TRX.NAME ACTIVITY,
REC_TRX.TAX_CODE_SOURCE TAX_CODE_SOURCE,
CR.RECEIVABLES_TRX_ID RECEIVABLES_TRX_ID,
CRH_CURRENT.GL_POSTED_DATE GL_POSTED_DATE,
CRH_CURRENT.POSTING_CONTROL_ID POSTING_CONTROL_ID,
CRH_FIRST_POSTED.GL_DATE GL_DATE,
CR.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY,
CR.REVERSAL_DATE REVERSAL_DATE,
DECODE(CR.REVERSAL_CATEGORY, NULL, NULL, L_REV_CAT.MEANING) REVERSAL_CATEGORY_DSP,
CR.REVERSAL_CATEGORY REVERSAL_CATEGORY,
DECODE(CR.REVERSAL_CATEGORY, NULL, NULL, L_REV_CAT.DESCRIPTION) CATEGORY_DESCRIPTION,
CR.REVERSAL_COMMENTS REVERSAL_COMMENTS,
DECODE(CR.REVERSAL_REASON_CODE, NULL, NULL, L_REV_REASON.MEANING) REVERSAL_REASON,
CR.REVERSAL_REASON_CODE REVERSAL_REASON_CODE,
DECODE(CR.REVERSAL_REASON_CODE, NULL, NULL, L_REV_REASON.DESCRIPTION) REVERSAL_REASON_DESCRIPTION,
REM_BAT.NAME REMIT_BATCH,
REM_BAT.BATCH_ID REMIT_BATCH_ID,
NVL(CR.OVERRIDE_REMIT_ACCOUNT_FLAG, 'Y') OVERRIDE_REMIT_BANK,
NVL(- (PS.AMOUNT_APPLIED), 0) APPLIED_AMOUNT
FROM CE_BANK_ACCOUNTS CBA,
CE_BANK_ACCT_USES_OU REMIT_BANK,
ZX_RATES_B VAT,
ZX_ACCOUNTS ACCOUNTS,
HZ_CUST_ACCOUNTS CUST,
HZ_PARTIES PARTY,
AR_RECEIPT_METHODS REC_METHOD,
AR_RECEIPT_CLASSES RC,
HZ_CUST_SITE_USES_ALL SITE_USES,
AR_LOOKUPS CRH_NOTE_STATUS,
AR_LOOKUPS L_REV_CAT,
AR_LOOKUPS L_REV_REASON,
AR_LOOKUPS L_REF_TYPE,
GL_DAILY_CONVERSION_TYPES GL_DCT,
AR_CASH_RECEIPT_HISTORY_ALL CRH_REM,
AR_BATCHES_ALL REM_BAT,
AR_RECEIVABLES_TRX_ALL REC_TRX,
AR_DISTRIBUTION_SETS_ALL DIST_SET,
AR_PAYMENT_SCHEDULES_ALL PS,
AR_CASH_RECEIPT_HISTORY_ALL CRH_CURRENT,
AR_BATCHES_ALL BAT,
AR_BATCHES_ALL BAT_BR,
AR_CASH_RECEIPTS_ALL CR,
AR_CASH_RECEIPT_HISTORY_ALL CRH_FIRST_POSTED,
HZ_PARTIES NotesBranchParty,
HZ_PARTIES NotesBankParty,
HZ_RELATIONSHIPS NotesBRRel,
CE_BANK_BRANCHES_V BB,
IBY_EXT_BANK_ACCOUNTS_V EBA
WHERE CR.PAY_FROM_CUSTOMER = CUST.CUST_ACCOUNT_ID(+)
AND CUST.PARTY_ID = PARTY.PARTY_ID(+)
AND CRH_NOTE_STATUS.LOOKUP_TYPE(+) = 'AR_NOTE_STATUS'
AND CRH_NOTE_STATUS.LOOKUP_CODE(+) = CRH_CURRENT.NOTE_STATUS
AND REMIT_BANK.BANK_ACCT_USE_ID(+) = CR.REMIT_BANK_ACCT_USE_ID
AND REMIT_BANK.ORG_ID(+) = CR.ORG_ID
AND VAT.TAX_RATE_ID(+) = CR.VAT_TAX_ID
AND ACCOUNTS.TAX_ACCOUNT_ENTITY_ID(+) = CR.VAT_TAX_ID
AND accounts.tax_account_entity_code(+) = 'RATES'
AND CR.RECEIPT_METHOD_ID = REC_METHOD.RECEIPT_METHOD_ID
AND REC_METHOD.RECEIPT_CLASS_ID = RC.RECEIPT_CLASS_ID
AND CR.CUSTOMER_SITE_USE_ID = SITE_USES.SITE_USE_ID(+)
AND CR.ORG_ID = SITE_USES.ORG_ID(+)
AND CR.RECEIVABLES_TRX_ID = REC_TRX.RECEIVABLES_TRX_ID(+)
AND CR.ORG_ID = REC_TRX.ORG_ID(+)
AND CR.DISTRIBUTION_SET_ID = DIST_SET.DISTRIBUTION_SET_ID(+)
AND CR.ORG_ID = DIST_SET.ORG_ID(+)
AND L_REV_CAT.LOOKUP_TYPE(+) = 'REVERSAL_CATEGORY_TYPE'
AND L_REV_CAT.LOOKUP_CODE(+) = CR.REVERSAL_CATEGORY
AND L_REV_REASON.LOOKUP_TYPE(+) = 'CKAJST_REASON'
AND L_REV_REASON.LOOKUP_CODE(+) = CR.REVERSAL_REASON_CODE
AND L_REF_TYPE.LOOKUP_CODE(+) = CR.REFERENCE_TYPE
AND L_REF_TYPE.LOOKUP_TYPE(+) = 'CB_REFERENCE_TYPE'
AND GL_DCT.CONVERSION_TYPE(+) = CR.EXCHANGE_RATE_TYPE
AND CRH_REM.CASH_RECEIPT_ID(+) = CR.CASH_RECEIPT_ID
AND CRH_REM.ORG_ID(+) = CR.ORG_ID
AND NOT EXISTS
(SELECT /*+ INDEX(CRH3 AR_CASH_RECEIPT_HISTORY_N1) */
CASH_RECEIPT_HISTORY_ID
FROM AR_CASH_RECEIPT_HISTORY_ALL CRH3
WHERE CRH3.STATUS = 'REMITTED'
AND CRH3.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
AND CRH3.CASH_RECEIPT_HISTORY_ID <
CRH_REM.CASH_RECEIPT_HISTORY_ID)
AND CRH_REM.STATUS(+) = 'REMITTED'
AND CRH_REM.BATCH_ID = REM_BAT.BATCH_ID(+)
AND CRH_REM.ORG_ID = REM_BAT.ORG_ID(+)
AND REM_BAT.TYPE(+) = 'REMITTANCE'
AND PS.CASH_RECEIPT_ID(+) = CR.CASH_RECEIPT_ID
AND PS.ORG_ID(+) = CR.ORG_ID
AND CRH_CURRENT.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
AND CRH_CURRENT.ORG_ID = CR.ORG_ID
AND CRH_CURRENT.CURRENT_RECORD_FLAG = NVL('Y', CR.RECEIPT_NUMBER)
AND CRH_FIRST_POSTED.BATCH_ID = BAT.BATCH_ID(+)
AND CRH_FIRST_POSTED.ORG_ID = BAT.ORG_ID(+)
AND BAT.TYPE(+) = 'MANUAL'
AND CRH_FIRST_POSTED.CASH_RECEIPT_ID(+) = CR.CASH_RECEIPT_ID
AND CRH_FIRST_POSTED.ORG_ID(+) = CR.ORG_ID
AND CRH_FIRST_POSTED.FIRST_POSTED_RECORD_FLAG(+) = 'Y'
AND CRH_FIRST_POSTED.BATCH_ID = BAT_BR.BATCH_ID(+)
AND CRH_FIRST_POSTED.ORG_ID = BAT_BR.ORG_ID(+)
AND BAT_BR.TYPE(+) = 'BR'
AND NotesBranchParty.party_id(+) = CR.issuer_bank_branch_id
AND NotesBRRel.object_id = NotesBankParty.party_id(+)
AND NotesBRRel.subject_id(+) = NotesBranchParty.party_id
AND NotesBRRel.relationship_type(+) = 'BANK_AND_BRANCH'
AND NotesBRRel.relationship_code(+) = 'BRANCH_OF'
AND NotesBRRel.subject_table_name(+) = 'HZ_PARTIES'
AND NotesBRRel.subject_type(+) = 'ORGANIZATION'
AND NotesBRRel.object_table_name(+) = 'HZ_PARTIES'
AND NotesBRRel.object_type(+) = 'ORGANIZATION'
AND remit_bank.bank_account_id = CBA.bank_account_id(+)
AND BB.BRANCH_PARTY_ID(+) = CBA.BANK_BRANCH_ID
AND EBA.ext_bank_account_id(+) = CR.CUSTOMER_BANK_ACCOUNT_ID
AND cr.org_id = accounts.internal_organization_id(+)
and CR.RECEIPT_NUMBER = 'FL3959195-6';
select created_by, translate(ltrim(text, '/'), '*/', '*,') researcherlist
from (select row_number() over(partition by created_by order by created_by, lvl desc) rn,
created_by,
text
from (select created_by,
level lvl,
sys_connect_by_path(c_researcher_code, '/') text
from (select created_by,
user_name as c_researcher_code,
row_number() over(partition by created_by order by created_by, user_name) x
from fnd_user
order by created_by, user_name) a
connect by created_by = prior created_by
and x – 1 = prior x))
where rn = 1
order by created_by;
SQL分析：
1、利用 “ROW_NUMBER () OVER (PARTITION BY……” 为按“创建人”汇总后的数据行添加组内序号
2、“SYS_CONNECT_BY_PATH” 按组内序号相邻关系，为每一层进行不同行的“名称”叠加
3、再次利用“创建人”进行组内分组，但按第二部中的层次排倒序，增加调整后等级
4、取所有调整后等级为1的结果，即为所要求的数据行
其他参考：http://www.ningoo.net/html/2008/how_to_do_string_aggregate_on_oracle.html
物料名称在中文环境（英文环境政策）下更新后，mtl_system_items_b和mtl_system_items_tl这两个表的数据不一致，这是EBS系统BUG，Oracle至今没解决。因此，取物料名称等相关信息，从mtl_system_items_tl表取值最准确。当然为了使基表mtl_system_items_b信息同步，可以通过写程序去定时同步更新相关的字段数据。