应收收款界面信息获取

2016年12月1日小潘留下评论

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

经验闲谈

Oracle PLSQL 行合并

2016年11月15日小潘留下评论

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

转载文章（档）

Oracle EBS系统物料信息变更BUG

2016年11月10日小潘留下评论

物料名称在中文环境（英文环境政策）下更新后，mtl_system_items_b和mtl_system_items_tl这两个表的数据不一致，这是EBS系统BUG，Oracle至今没解决。因此，取物料名称等相关信息，从mtl_system_items_tl表取值最准确。当然为了使基表mtl_system_items_b信息同步，可以通过写程序去定时同步更新相关的字段数据。

转载文章（档）

Oracle EBS多OU总结

2016年11月2日小潘留下评论

(一) 多OU总结

1. Form多OU实现

1) 创建一个Table,以CUX_AP_CHECK_HEADER_ALL为例

2) 创建Table的两个Synonym(一个不含_ALL,一个以_ALL结尾)：CUX_AP_CHECK_HEADER和CUX_AP_CHECK_HEADER_ALL

3) 给不含_ALL的Synonym：CUX_AP_CHECK_HEADER加上组织屏蔽的策略函数

dbms_rls.add_policy(object_name => 'CUX_AP_CHECK_HEADER',

policy_name => 'ORG_SEC',

policy_function => 'MO_GLOBAL.ORG_SECURITY',

policy_type => dbms_rls.shared_context_sensitive);

4) 在不含_ALL的Synonym的基础上创建视图: CUX_AP_CHECK_HEADER_V

5) 进入FORM时(pre-form触发器)添加代码：

mo_global.init(&p_appl_shortname);–p_appl_shortname为应用简称

6) 当选择某个OU时(一般在when_validate_item触发器)中添加代码：mo_global.set_policy_context('S',&p_org_id);–p_org_id为OU的id

2. Report多OU实现

1) 给并发程序设置业务实体模式：单个，多个和空(默认)。一般设置为‘单个’

业务实体模式对应表fnd_concurrent_programs中的multi_org_category字段

2) 得到当前OU的值。

使用：mo_global.get_current_org_id或者fnd_global.org_id

3) 在报表的参数和报表的逻辑中加上OU的限制

3. GL数据的多OU实现

1) 得到当前OU的值。

使用：mo_global.get_current_org_id或者fnd_global.org_id

2) 根据OU的值得到部门段的值：

DECLARE

l_segment1 VARCHAR2(150);–部门段

BEGIN

SELECT o3.attribute5

INTO l_segment1

FROM hr_all_organization_units o,

hr_all_organization_units_tl otl,

hr_organization_information o2,

hr_organization_information o3

WHERE o.organization_id = o2.organization_id

AND o.organization_id = o3.organization_id

AND o2.org_information_context = 'CLASS'

AND o3.org_information_context = 'Operating Unit Information'

AND o2.org_information1 = 'OPERATING_UNIT'

AND o2.org_information2 = 'Y'

and o.organization_id = otl.organization_id

and o.organization_id = &p_org_id –OU id

AND otl.LANGUAGE = USERENV('LANG');

END;

3) 将步骤2得到的值作为限制条件：

SELECT gl_code_combinations gcc WHERE gcc.segment1 = l_segment1;

4. Interface多OU总结

1) 给并发程序设置业务实体模式：单个，多个和空(默认)。业务实体模式对应表fnd_concurrent_programs中的multi_org_category字段

2) 如果接口的导入程序中OU作为一个参数，则应该将所有的OU作一次循环。

5. 多OU实现扩展知识

1) 给客户化应用注册和取消MOAC的控制

fnd_mo_product_init_pkg.register_application(注册应用)

fnd_mo_product_init_pkg.remove_application(取消应用)

查看支持MOAC的应用SQL:

SELECT * FROM fnd_mo_product_init;

2) 给数据库对象注册和取消策略-policy

dbms_rls.add_policy(注册策略)

dbms_rls.drop_policy(取消策略)

3) 多OU 涉及到的表

a) 查看数据库对象是否增加了策略-policy

SELECT * FROM dba_policies;

b) 查看当前session所能访问的OU

SELECT * FROM mo_glob_org_access_tmp;

c) 查看当前session应用上下文(context)的值(说明：OU的值保存在context中)

SELECT * FROM dba_context dc WHERE dc.namespace LIKE 'MULTI%';

MOAC使用的应用程序上下文：MULTI_ORG，MULTI_ORG2

(二) 多帐套总结

1. 客户化开发中的多帐套屏蔽

1) 得到当前OU的值。

使用：mo_global.get_current_org_id或者fnd_global.org_id

2) 根据组织id得到帐套id和公司名称。SQL语句为：

DECLARE

l_org_information3 VARCHAR2(150);–帐套id

l_company_desc VARCHAR2(150);–公司中文描述

BEGIN

SELECT o3.org_information3,o3.attribute3

INTO l_org_information3，l_company_desc

FROM hr_all_organization_units o,

hr_all_organization_units_tl otl,

hr_organization_information o2,

hr_organization_information o3

WHERE o.organization_id = o2.organization_id

AND o.organization_id = o3.organization_id

AND o2.org_information_context || '' = 'CLASS'

AND o3.org_information_context = 'Operating Unit Information'

AND o2.org_information1 = 'OPERATING_UNIT'

AND o2.org_information2 = 'Y'

AND o.organization_id = otl.organization_id

AND otl.language = USERENV('LANG')

AND o.organization_id = &p_org_id;–OU id

END;

3) 得到本位币，SQL语句为：

DECLARE

l_local_currency_code VARCHAR2(15);–本位币

BEGIN

SELECT gsob.currency_code

INTO l_local_currency_code

FROM gl_sets_of_books gsob, hr_operating_units hou

WHERE gsob.set_of_books_id = hou.set_of_books_id

AND hou.organization_id = &p_org_id;–OU ID

END;

4) 在程序中加上帐套和本位币的限制

2. 多帐套实现扩展

1) 得到帐套的SQL语句为：

SELECT * FROM gl_ledgers;

2) 得到法人的SQL语句为：

SELECT * FROM xle_entity_profiles;

转载地址：http://blog.csdn.net/cunxiyuan108/article/details/6454450

技术笔记（小潘的技术记录博客）

备案信息：
@2012-2024 www.llku.com 版权所有ICP证：
蜀ICP备18026986号-1
网站支持：
阿里云 ECS

一	二	三	四	五	六	日
				1	2	3
4	5	6	7	8	9	10
11	12	13	14	15	16	17
18	19	20	21	22	23	24
25	26	27	28	29	30

路路库

应收收款界面信息获取

Oracle PLSQL 行合并

推荐博客地址

Oracle EBS系统物料信息变更BUG

Oracle EBS多OU总结

技术笔记（小潘的技术记录博客）