ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN C6E085D4703651A9E05362FB09050512Y --Fix bug# 14664279 added by Jar.Wang 28/Sep/2012 --Fix bug# 10188478 added by riqi 13-Oct-2010 --BEGIN--- SELECT JCA.DETAILED_CFS_ITEM AS DETAILED_CFS_ITEM ,JCA.SOURCE AS SOURCE ,JCA.THIRD_PARTY_NAME AS THIRD_PARTY_NAME ,NULL AS RECEIPT_PAYMENT_NUMBER ,JCA.REFERENCE_NUMBER AS REFERENCE_NUMBER_ ,JCA.TRX_NUMBER AS TRANSACTION_NUMBER ,JCA.FUNC_AMOUNT AS TRANSACTION_AMOUNT ,JCA.BALANCING_SEGMENT AS BSV ,JCA.DESCRIPTION FROM JA_CN_CFS_ACTIVITIES_ALL JCA ,JA_CN_CFS_ASSIGNMENTS_ALL JCCA ,RG_REPORTS RG ,RG_REPORT_AXES RRA WHERE JCA.LEGAL_ENTITY_ID = :P_LEGAL_ENTITY_ID AND JCA.PERIOD_NAME = :PERIOD_NAME --**** AND JCA.FUNC_AMOUNT <> 0 AND NOT (JCA.TRANSACTION_TYPE = 'SLA' AND JCA.SOURCE IN ('SQLAP', 'AR')) AND JCA.SOURCE = NVL(:P_SOURCE, JCA.SOURCE) AND JCCA.DETAILED_CFS_ITEM = JCA.DETAILED_CFS_ITEM AND JCCA.CHART_OF_ACCOUNTS_ID = :P_CHART_OF_ACCOUNTS_ID AND JCCA.AXIS_SET_ID = RRA.AXIS_SET_ID AND JCCA.AXIS_SEQ = RRA.AXIS_SEQ AND RG.ROW_SET_ID = RRA.AXIS_SET_ID AND RG.REPORT_ID = NVL(:P_REPORT_ID, RG.REPORT_ID) AND JCA.LEDGER_ID = :P_LEDGER_ID AND RRA.AXIS_SEQ = :ROW_ID --**** AND ((:P_BSV IS NOT NULL AND JCA.BALANCING_SEGMENT = :P_BSV) OR (:P_BSV IS NULL AND JCA.BALANCING_SEGMENT IN (SELECT BAL_SEG_VALUE FROM JA_CN_LEDGER_LE_BSV_GT WHERE LEGAL_ENTITY_ID = :P_LEGAL_ENTITY_ID AND LEDGER_ID = :P_LEDGER_ID))) UNION ALL --Fix bug# 10188478 added by riqi 13-Oct-2010 --END--- SELECT JCA.DETAILED_CFS_ITEM AS DETAILED_CFS_ITEM ,JCA.SOURCE AS SOURCE ,PV.VENDOR_NAME AS THIRD_PARTY_NAME ,TO_CHAR(ACA.CHECK_NUMBER) AS RECEIPT_PAYMENT_NUMBER ,JCA.REFERENCE_NUMBER AS REFERENCE_NUMBER_ ,NVL(API.INVOICE_NUM, '') AS TRANSACTION_NUMBER ,DECODE((SELECT XAH.EVENT_TYPE_CODE FROM XLA_AE_HEADERS XAH WHERE XAH.AE_HEADER_ID = JCA.CASH_TRX_ID AND ROWNUM = 1 ), 'PAYMENT UNCLEARED', DECODE(NVL(XDL.UNROUNDED_ACCOUNTED_DR, '-1'), '-1', -1 * XDL.UNROUNDED_ACCOUNTED_CR, XDL.UNROUNDED_ACCOUNTED_DR), DECODE(NVL(XDL.UNROUNDED_ACCOUNTED_DR, '-1'), '-1', XDL.UNROUNDED_ACCOUNTED_CR, -1 * XDL.UNROUNDED_ACCOUNTED_DR) ) AS TRANSACTION_AMOUNT ,JCA.BALANCING_SEGMENT AS BSV ,JCA.DESCRIPTION FROM JA_CN_CFS_ACTIVITIES_ALL JCA ,JA_CN_CFS_ASSIGNMENTS_ALL JCCA ,RG_REPORTS RG ,XLA_TRANSACTION_ENTITIES ENT ,AP_CHECKS_ALL ACA ,PO_VENDORS PV ,XLA_AE_HEADERS AEH ,XLA_DISTRIBUTION_LINKS XDL ,AP_INVOICES_ALL API WHERE JCA.LEGAL_ENTITY_ID = :P_LEGAL_ENTITY_ID AND JCA.LEDGER_ID = :P_LEDGER_ID AND JCA.FUNC_AMOUNT <> 0 AND JCA.TRANSACTION_TYPE = 'SLA' AND JCA.SOURCE = 'SQLAP' AND JCA.SOURCE = NVL(:P_SOURCE, JCA.SOURCE) AND JCA.PERIOD_NAME = :PERIOD_NAME --**** AND ((:P_BSV IS NOT NULL AND JCA.BALANCING_SEGMENT = :P_BSV) OR (:P_BSV IS NULL AND JCA.BALANCING_SEGMENT IN (SELECT BAL_SEG_VALUE FROM JA_CN_LEDGER_LE_BSV_GT WHERE LEGAL_ENTITY_ID = :P_LEGAL_ENTITY_ID AND LEDGER_ID = :P_LEDGER_ID))) AND JCCA.DETAILED_CFS_ITEM = JCA.DETAILED_CFS_ITEM AND JCCA.CHART_OF_ACCOUNTS_ID = :P_CHART_OF_ACCOUNTS_ID AND JCCA.AXIS_SEQ = :ROW_ID --**** AND RG.ROW_SET_ID = JCCA.AXIS_SET_ID AND RG.REPORT_ID = NVL(:P_REPORT_ID, RG.REPORT_ID) AND ENT.APPLICATION_ID = 200 AND ENT.ENTITY_CODE = 'AP_PAYMENTS' AND ACA.CHECK_ID = ENT.SOURCE_ID_INT_1 AND PV.VENDOR_ID(+) = ACA.VENDOR_ID AND AEH.ENTITY_ID = ENT.ENTITY_ID AND AEH.AE_HEADER_ID = JCA.TRX_ID AND AEH.LEDGER_ID = :P_LEDGER_ID AND XDL.AE_HEADER_ID = JCA.TRX_ID AND XDL.AE_LINE_NUM = JCA.TRX_LINE_ID AND API.INVOICE_ID(+) = XDL.APPLIED_TO_SOURCE_ID_NUM_1 UNION ALL SELECT JCA.DETAILED_CFS_ITEM AS DETAILED_CFS_ITEM ,JCA.SOURCE AS SOURCE ,HP.PARTY_NAME AS THIRD_PARTY_NAME ,ACA.RECEIPT_NUMBER AS RECEIPT_PAYMENT_NUMBER ,JCA.REFERENCE_NUMBER AS REFERENCE_NUMBER_ ,NVL(RA.TRX_NUMBER, '') AS TRANSACTION_NUMBER ,DECODE((SELECT XAH.EVENT_TYPE_CODE FROM XLA_AE_HEADERS XAH WHERE XAH.AE_HEADER_ID = JCA.CASH_TRX_ID AND ROWNUM = 1 ), 'RECP_UPDATE', DECODE( --modified by Jixun for bug#16698143 begin /*(SELECT ACH.STATUS FROM AR_CASH_RECEIPT_HISTORY_ALL ACH, XLA_AE_HEADERS AH WHERE AH.AE_HEADER_ID = JCA.CASH_TRX_ID AND AH.EVENT_ID = ACH.EVENT_ID AND ACH.CURRENT_RECORD_FLAG = 'Y' --Fix bug#11777168 added by minwu 08-Mar-2011 AND ROWNUM = 1),*/ DECODE((SELECT COUNT(ACH.CASH_RECEIPT_HISTORY_ID) FROM AR_CASH_RECEIPT_HISTORY_ALL ACH, XLA_AE_HEADERS AH WHERE AH.AE_HEADER_ID = JCA.CASH_TRX_ID AND AH.EVENT_ID = ACH.EVENT_ID), 1, (SELECT ACH.STATUS FROM AR_CASH_RECEIPT_HISTORY_ALL ACH, XLA_AE_HEADERS AH WHERE AH.AE_HEADER_ID = JCA.CASH_TRX_ID AND AH.EVENT_ID = ACH.EVENT_ID), (SELECT ACH.STATUS FROM AR_CASH_RECEIPT_HISTORY_ALL ACH, XLA_AE_HEADERS AH WHERE AH.AE_HEADER_ID = JCA.CASH_TRX_ID AND AH.EVENT_ID = ACH.EVENT_ID AND ACH.CURRENT_RECORD_FLAG = 'Y')), --modified by Jixun for bug#16698143 end 'REMITTED', DECODE(NVL(XDL.UNROUNDED_ACCOUNTED_DR, '-1'), '-1', -1 * XDL.UNROUNDED_ACCOUNTED_CR, XDL.UNROUNDED_ACCOUNTED_DR), DECODE(NVL(XDL.UNROUNDED_ACCOUNTED_DR, '-1'), '-1', XDL.UNROUNDED_ACCOUNTED_CR, -1 * XDL.UNROUNDED_ACCOUNTED_DR) ), DECODE(NVL(XDL.UNROUNDED_ACCOUNTED_DR, '-1'), '-1', XDL.UNROUNDED_ACCOUNTED_CR, -1 * XDL.UNROUNDED_ACCOUNTED_DR) ) AS TRANSACTION_AMOUNT ,JCA.BALANCING_SEGMENT AS BSV ,JCA.DESCRIPTION FROM JA_CN_CFS_ACTIVITIES_ALL JCA ,JA_CN_CFS_ASSIGNMENTS_ALL JCCA ,RG_REPORTS RG ,XLA_TRANSACTION_ENTITIES ENT ,XLA_AE_HEADERS AEH ,AR_CASH_RECEIPTS_ALL ACA ,HZ_CUST_ACCOUNTS CUST ,HZ_PARTIES HP ,XLA_DISTRIBUTION_LINKS XDL ,RA_CUSTOMER_TRX_ALL RA WHERE JCA.LEGAL_ENTITY_ID = :P_LEGAL_ENTITY_ID AND JCA.LEDGER_ID = :P_LEDGER_ID AND JCA.FUNC_AMOUNT <> 0 AND JCA.TRANSACTION_TYPE = 'SLA' AND JCA.SOURCE = 'AR' AND JCA.SOURCE = NVL(:P_SOURCE, JCA.SOURCE) AND JCA.PERIOD_NAME = :PERIOD_NAME --**** AND ((:P_BSV IS NOT NULL AND JCA.BALANCING_SEGMENT = :P_BSV) OR (:P_BSV IS NULL AND JCA.BALANCING_SEGMENT IN (SELECT BAL_SEG_VALUE FROM JA_CN_LEDGER_LE_BSV_GT WHERE LEGAL_ENTITY_ID = :P_LEGAL_ENTITY_ID AND LEDGER_ID = :P_LEDGER_ID))) AND JCCA.DETAILED_CFS_ITEM = JCA.DETAILED_CFS_ITEM AND JCCA.CHART_OF_ACCOUNTS_ID = :P_CHART_OF_ACCOUNTS_ID AND JCCA.AXIS_SEQ = :ROW_ID --**** AND RG.ROW_SET_ID = JCCA.AXIS_SET_ID AND RG.REPORT_ID = NVL(:P_REPORT_ID, RG.REPORT_ID) AND ENT.APPLICATION_ID = 222 AND ENT.ENTITY_CODE = 'RECEIPTS' AND ENT.ENTITY_ID = AEH.ENTITY_ID AND AEH.AE_HEADER_ID = JCA.TRX_ID AND AEH.LEDGER_ID = :P_LEDGER_ID AND ACA.CASH_RECEIPT_ID = ENT.SOURCE_ID_INT_1 AND ACA.PAY_FROM_CUSTOMER = CUST.CUST_ACCOUNT_ID(+) AND CUST.PARTY_ID = HP.PARTY_ID(+) AND XDL.AE_HEADER_ID = JCA.TRX_ID AND XDL.AE_LINE_NUM = JCA.TRX_LINE_ID AND RA.CUSTOMER_TRX_ID(+) = XDL.APPLIED_TO_SOURCE_ID_NUM_1 ORDER BY 2, 3, 5 JA JACNCFSD JA JA キャッシュ・フロー取引明細書プログラム - キャッシュ・フロー詳細レポート Imported from BI Publisher Description: キャッシュ・フロー取引明細書詳細レポート Application: Asia/Pacific Localizations Source: キャッシュ・フロー取引明細書プログラム - キャッシュ・フロー詳細レポート Short Name: JACNCFSD DB package: JA_CN_CFS_REPORT_PKG US JA Cash Flow Statement Program - Cash Flow Detail Imported from BI Publisher Description: Cash Flow Statement Detail Report Application: Asia/Pacific Localizations Source: Cash Flow Statement Program - Cash Flow Detail Report Short Name: JACNCFSD DB package: JA_CN_CFS_REPORT_PKG ZHS JA 现金流量表程序 - 现金流量明细报表 Imported from BI Publisher Description: 现金流量表 - 明细报表 Application: 亚太地区本地化 Source: 现金流量表程序 - 现金流量明细报表 Short Name: JACNCFSD DB package: JA_CN_CFS_REPORT_PKG BI Publisher :l_module_prefix :p_adhoc_prefix :p_bsv :p_chart_of_accounts_id :p_end_period :p_id_flex_code :p_industry :p_ledger_id :p_ledger_name :p_legal_entity :p_legal_entity_id :p_report_id :p_report_name :p_row_id :p_row_name :p_row_set_id :p_source :p_start_period :period_name :row_id 1 -90 :p_legal_entity_id Number fnd_profile.value('JA_CN_LEGAL_ENTITY') Y JA 法的エンティティID 法的エンティティID KO 법인 ID 법인 ID US Legal Entity Id Legal Entity Id ZHS 法人主体标识 法人主体标识 2 -80 :p_legal_entity Char SELECT NAME FROM XLE_ENTITY_PROFILES WHERE LEGAL_ENTITY_ID = fnd_profile.value('JA_CN_LEGAL_ENTITY') JA P_LEGAL_ENTITY 法的エンティティ名 KO P_LEGAL_ENTITY 법인명 US P_LEGAL_ENTITY Legal Entity Name ZHS P_LEGAL_ENTITY 法人主体名称 3 -70 :p_ledger_name Char SELECT NAME FROM GL_LEDGERS WHERE LEDGER_ID = :$flex$.ledger JA 元帳名 元帳名 KO 원장명 원장명 US Ledger Name Ledger Name ZHS 分类帐名称 分类帐名称 4 -60 :p_chart_of_accounts_id LOV Oracle JA_CN_COA_ID Y select chart_of_accounts_id id, chart_of_accounts_id value, null description from gl_access_sets where access_set_id=fnd_profile.value('GL_ACCESS_SET_ID') order by value,description select CHART_OF_ACCOUNTS_ID from GL_ACCESS_SETS WHERE access_set_id = fnd_profile.value('GL_ACCESS_SET_ID') Y JA JA_CN_COA_ID 勘定体系ID US JA_CN_COA_ID Chart of Accounts Id ZHS JA_CN_COA_ID 科目表标识 5 -50 :p_adhoc_prefix LOV Oracle RG_SRS_ADHOC_REPORT_PREFIX Y select meaning id, meaning value, description description from rg_lookups where lookup_type='FSG_ADHOC_REPORT_NAME_PREFIX' and lookup_code='ADHOC_PREFIX' order by value,description SELECT MEANING FROM RG_LOOKUPS WHERE LOOKUP_TYPE = 'FSG_ADHOC_REPORT_NAME_PREFIX' AND LOOKUP_CODE = 'ADHOC_PREFIX' Y JA RG_SRS_ADHOC_REPORT_PREFIX ADHOCプリフィクス US RG_SRS_ADHOC_REPORT_PREFIX ADHOC Prefix ZHS RG_SRS_ADHOC_REPORT_PREFIX ADHOC 前缀 6 -40 :p_industry Char C Y JA RG_SRS_INSTALLATION 産業 US RG_SRS_INSTALLATION Industry ZHS RG_SRS_INSTALLATION 行业 7 -30 :p_id_flex_code Char SELECT DECODE(:$FLEX$.RG_SRS_INSTALLATION,'G', DECODE(fnd_profile.value('ATTRIBUTE_REPORTING'),'Y','GLAT','GLLE'),'GLLE') FROM DUAL JA IDフレックス・コード IDフレックス・コード US Id Flex Code Id Flex Code ZHS 标识弹性域代码 标识弹性域代码 8 -20 :p_report_name Char SELECT NAME FROM RG_REPORTS WHERE REPORT_ID = :$flex$.report JA レポート名 レポート名 US Report Name Report Name ZHS 报表名称 报表名称 9 -10 :p_row_set_id LOV Oracle JA_CN_SRS_RGXROWD Y select axis_set_id id, name value, description description from rg_report_axis_sets where axis_set_type='R' and nvl(id_flex_code,decode(:$flex$.rg_srs_installation,'G',decode(fnd_profile.value('ATTRIBUTE_REPORTING'),'Y','GLAT','GLLE'),'GLLE'))=decode(:$flex$.rg_srs_installation,'G',decode(fnd_profile.value('ATTRIBUTE_REPORTING'),'Y','GLAT','GLLE'),'GLLE') and nvl(structure_id,:$flex$.ja_cn_coa_id)=:$flex$.ja_cn_coa_id and application_id in (101,168) order by value,description SELECT rs.name FROM rg_report_axis_sets rs, rg_reports rp WHERE rp.report_id = :$flex$.report and rp.row_set_id = rs.axis_set_id JA JA_CN_SRS_RGXROWD 行セットID US JA_CN_SRS_RGXROWD Row Set Id ZHS JA_CN_SRS_RGXROWD 行集标识 10 10 :p_ledger_id LOV Oracle JA_CN_LEDGERS Y select l.ledger_id id, l.name value, null description from gl_access_set_ledgers g,gl_ledgers l where g.ledger_id=l.ledger_id and g.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID') order by value,description Y JA 元帳 元帳ID KO 원장 원장 ID US Ledger Ledger ID ZHS 分类账 分类帐标识 11 20 :p_report_id LOV Oracle JA_CN_SRS_RGXRPTD Y select report_id id, name value, description description from rg_reports where nvl(id_flex_code,decode(:$flex$.rg_srs_installation,'G',decode(fnd_profile.value('ATTRIBUTE_REPORTING'),'Y','GLAT','GLLE'),'GLLE'))=decode(:$flex$.rg_srs_installation,'G',decode(fnd_profile.value('ATTRIBUTE_REPORTING'),'Y','GLAT','GLLE'),'GLLE')and nvl(structure_id,:$flex$.ja_cn_coa_id)=:$flex$.ja_cn_coa_id and application_id in (101,168) and name not like substr(:$flex$.rg_srs_adhoc_report_prefix,1,16) ||'%' order by value,description Y JA レポート レポート KO 보고서 보고서 US Report Report ZHS 报表 报表 12 30 :p_row_id LOV Oracle JA_CN_SRS_RGXRNMD Y select axis_seq id, axis_seq value, description description from rg_report_axes where nvl(structure_id,:$flex$.ja_cn_coa_id) =:$flex$.ja_cn_coa_id and axis_set_id=:$flex$.ja_cn_srs_rgxrowd and application_id in (101,168) order by value,description JA 行名 行名 US Row Name Row Name ZHS 行名 行名 13 40 :p_row_name Char SELECT DESCRIPTION FROM RG_REPORT_AXES WHERE AXIS_SEQ = :$flex$.row_name JA 行摘要 行摘要 KO 행 설명 행 설명 US Row Description Row Description ZHS 行说明 行说明 14 50 :p_start_period LOV Oracle JA_CN_SRS_ALL_PERIODS Y select period_name id, period_name value, null description from gl_period_statuses where application_id=101 and ledger_id=:$flex$.ja_cn_ledgers and closing_status not in ('N','F') order by period_year desc,period_num desc SELECT latest_opened_period_name FROM gl_ledgers WHERE ledger_id = :$flex$.ledger Y JA 期間: 自 期間: 自 KO 기간: 부터 기간: 부터 US Period From Period From ZHS 起始自 起始自 15 60 :p_end_period LOV Oracle JA_CN_SRS_ALL_PERIODS Y select period_name id, period_name value, null description from gl_period_statuses where application_id=101 and ledger_id=:$flex$.ja_cn_ledgers and closing_status not in ('N','F') order by period_year desc,period_num desc SELECT latest_opened_period_name FROM gl_ledgers WHERE ledger_id = :$flex$.ledger Y JA 期間: 至 期間: 至 KO 기간: 까지 기간: 까지 US Period To Period To ZHS 终止至 终止至 16 70 :p_source LOV Oracle JA_CN_CASHFLOW_DATA_SOURCES Y select application_short_name id, application_short_name value, application_name description from fnd_application_vl where application_id in (select application_id from ja_cn_cfs_item_mapping_hdrs where chart_of_accounts_id=:$flex$.ja_cn_coa_id) or application_id in (101,435) order by value,description JA ソース ソース KO 출처 출처 US Source Source ZHS 来源 来源 17 80 :p_bsv LOV Oracle JA_CN_CASHFLOW_BSV Y select bsv id, bsv value, null description from ja_cn_cfsbsv_v where (le_id=fnd_profile.value('JA_CN_LEGAL_ENTITY') and lg_id=:$flex$.ja_cn_ledgers)or(le_id is null and (lg_id=:$flex$.ja_cn_ledgers or lg_id in (select ledger_id from gl_alc_ledger_rships_v where application_id=101 and source_ledger_id=:$flex$.ja_cn_ledgers))) order by value,description JA 残高セグメント値 残高セグメント値 KO 잔액 세그먼트 값 잔액 세그먼트 값 US Balance Segment Value Balance Segment Value ZHS 余额段值 余额段值