{"id":301,"date":"2015-01-16T19:19:53","date_gmt":"2015-01-16T11:19:53","guid":{"rendered":"http:\/\/www.llku.com\/?p=301"},"modified":"2015-01-16T19:21:19","modified_gmt":"2015-01-16T11:21:19","slug":"%e8%8e%b7%e5%8f%96%e9%93%b6%e8%a1%8c%e8%b4%a6%e6%88%b7%e5%88%86%e9%85%8d%e6%98%8e%e7%bb%86sql","status":"publish","type":"post","link":"https:\/\/www.llku.com\/?p=301","title":{"rendered":"\u83b7\u53d6\u94f6\u884c\u8d26\u6237\u5206\u914d\u660e\u7ec6SQL"},"content":{"rendered":"<p>\n\t&nbsp; &nbsp; \u4eca\u5929\u7ed9\u5ba2\u6237\u63d0\u53d6\u6240\u6709\u94f6\u884c\u8d26\u6237\u7684\u673a\u6784\u5206\u914d\u4fe1\u606f\uff0c\u5199\u4e86\u4ee5\u4e0b\u4e00\u6bb5SQL\u547d\u4ee4\uff0c\u8bb0\u5f55\u4e0b\u6765\u4ee5\u5907\u540e\u7528\uff1a\n<\/p>\n<p>\n\tSELECT CBA.BANK_ACCOUNT_NAME \u8d26\u6237\u540d,<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp;BANK_INFO.BANK_NAME \u94f6\u884c\u540d\u79f0,<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp;BANK_INFO.BRANCH_NAME \u5206\u884c\u540d\u79f0,<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp;BANK_INFO.BANK_ACCOUNT_NUM \u94f6\u884c\u8d26\u6237,<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp;BANKACCTUSEEO.ORG_ID \u673a\u6784ID,<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp;DECODE(BANKACCTUSEEO.LEGAL_ENTITY_ID,<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; NULL,<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FND_ACCESS_CONTROL_UTIL.GET_ORG_NAME(BANKACCTUSEEO.ORG_ID),<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; (SELECT XLE.NAME<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;FROM XLE_ENTITY_PROFILES XLE<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; WHERE XLE.LEGAL_ENTITY_ID = BANKACCTUSEEO.LEGAL_ENTITY_ID)) \u673a\u6784\u63cf\u8ff0,<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp;BANKACCTUSEEO.AP_USE_ENABLE_FLAG \u5e94\u4ed8\u53ef\u7528\u6027,<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp;BANKACCTUSEEO.AR_USE_ENABLE_FLAG \u5e94\u6536\u53ef\u7528\u6027,<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp;BANKACCTUSEEO.END_DATE \u5931\u6548\u65e5\u671f<br \/>\n\t&nbsp; FROM CE_BANK_ACCT_USES_ALL BANKACCTUSEEO,<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp;AR_RECEIVABLES_TRX ARRECEIVABLESTRXEARNED,<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp;AR_RECEIVABLES_TRX ARRECEIVABLESTRXUNEARNED,<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp;AR_RECEIVABLES_TRX ARRECEIVABLESTRXCLAIMINV,<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp;CE_LOOKUPS CL,<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp;FND_LOOKUPS FL,<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp;CE_RECEIVABLE_ACTIVITIES_V RA,<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp;CE_BANK_ACCOUNTS CBA,<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp;(SELECT BANKPARTY.PARTY_NAME &nbsp; BANK_NAME,<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;BRANCHPARTY.PARTY_NAME BRANCH_NAME,<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;TERRITORY_SHORT_NAME &nbsp; COUNTRY,<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;BA.BANK_ACCOUNT_NUM,<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;BRANCHPARTY.PARTY_ID &nbsp; BANK_BRANCH_ID,<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;BA.BANK_ACCOUNT_ID,<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;CL.MEANING &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ACCOUNT_CLASS_MEANING<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FROM HZ_PARTIES &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; BANKPARTY,<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;HZ_PARTIES &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; BRANCHPARTY,<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;HZ_ORGANIZATION_PROFILES BANKORGPROFILE,<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;HZ_ORGANIZATION_PROFILES BRANCHORGPROFILE,<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;HZ_RELATIONSHIPS &nbsp; &nbsp; &nbsp; &nbsp; BRREL,<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;HZ_CODE_ASSIGNMENTS &nbsp; &nbsp; &nbsp;BANKCA,<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;HZ_CODE_ASSIGNMENTS &nbsp; &nbsp; &nbsp;BRANCHCA,<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;CE_LOOKUPS &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CL,<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;FND_TERRITORIES_VL &nbsp; &nbsp; &nbsp; FT,<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;CE_BANK_ACCOUNTS &nbsp; &nbsp; &nbsp; &nbsp; BA<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;WHERE BANKPARTY.PARTY_TYPE = &#39;ORGANIZATION&#39;<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AND BANKPARTY.STATUS = &#39;A&#39;<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AND BANKPARTY.PARTY_ID = BANKORGPROFILE.PARTY_ID<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AND SYSDATE BETWEEN TRUNC(BANKORGPROFILE.EFFECTIVE_START_DATE) AND<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;NVL(TRUNC(BANKORGPROFILE.EFFECTIVE_END_DATE), SYSDATE + 1)<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AND BANKCA.CLASS_CATEGORY = &#39;BANK_INSTITUTION_TYPE&#39;<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AND BANKCA.CLASS_CODE = &#39;BANK&#39;<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AND BANKCA.OWNER_TABLE_NAME = &#39;HZ_PARTIES&#39;<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AND BANKCA.OWNER_TABLE_ID = BANKPARTY.PARTY_ID<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AND NVL(BANKCA.STATUS, &#39;A&#39;) = &#39;A&#39;<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AND BRANCHPARTY.PARTY_TYPE = &#39;ORGANIZATION&#39;<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AND BRANCHPARTY.STATUS = &#39;A&#39;<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AND BRANCHORGPROFILE.PARTY_ID = BRANCHPARTY.PARTY_ID<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AND SYSDATE BETWEEN<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;TRUNC(BRANCHORGPROFILE.EFFECTIVE_START_DATE(+)) AND<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;NVL(TRUNC(BRANCHORGPROFILE.EFFECTIVE_END_DATE(+)),<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;SYSDATE + 1)<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AND BRANCHCA.CLASS_CATEGORY = &#39;BANK_INSTITUTION_TYPE&#39;<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AND BRANCHCA.CLASS_CODE = &#39;BANK_BRANCH&#39;<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AND BRANCHCA.OWNER_TABLE_NAME = &#39;HZ_PARTIES&#39;<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AND BRANCHCA.OWNER_TABLE_ID = BRANCHPARTY.PARTY_ID<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AND NVL(BRANCHCA.STATUS, &#39;A&#39;) = &#39;A&#39;<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AND BRREL.OBJECT_ID = BANKPARTY.PARTY_ID<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AND BRANCHPARTY.PARTY_ID = BRREL.SUBJECT_ID<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AND BRREL.RELATIONSHIP_TYPE = &#39;BANK_AND_BRANCH&#39;<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AND BRREL.RELATIONSHIP_CODE = &#39;BRANCH_OF&#39;<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AND BRREL.STATUS = &#39;A&#39;<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AND BRREL.SUBJECT_TABLE_NAME = &#39;HZ_PARTIES&#39;<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AND BRREL.SUBJECT_TYPE = &#39;ORGANIZATION&#39;<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AND BRREL.OBJECT_TABLE_NAME = &#39;HZ_PARTIES&#39;<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AND BRREL.OBJECT_TYPE = &#39;ORGANIZATION&#39;<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AND CL.LOOKUP_TYPE = &#39;ACCOUNT_CLASSIFICATION&#39;<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AND CL.LOOKUP_CODE = BA.ACCOUNT_CLASSIFICATION<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AND BANKORGPROFILE.HOME_COUNTRY = TERRITORY_CODE<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AND BA.BANK_BRANCH_ID = BRANCHPARTY.PARTY_ID) BANK_INFO<br \/>\n\t&nbsp;WHERE 1 = 1<br \/>\n\t&nbsp; &nbsp;AND BANK_INFO.BANK_ACCOUNT_ID = CBA.BANK_ACCOUNT_ID<br \/>\n\t&nbsp; &nbsp;AND CBA.BANK_ACCOUNT_ID = BANKACCTUSEEO.BANK_ACCOUNT_ID<br \/>\n\t&nbsp; &nbsp;AND BANKACCTUSEEO.EDISC_RECEIVABLES_TRX_ID =<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp;ARRECEIVABLESTRXEARNED.RECEIVABLES_TRX_ID(+)<br \/>\n\t&nbsp; &nbsp;AND BANKACCTUSEEO.UNEDISC_RECEIVABLES_TRX_ID =<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp;ARRECEIVABLESTRXUNEARNED.RECEIVABLES_TRX_ID(+)<br \/>\n\t&nbsp; &nbsp;AND BANKACCTUSEEO.AR_CLAIM_INV_ACT_ID =<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp;ARRECEIVABLESTRXCLAIMINV.RECEIVABLES_TRX_ID(+)<br \/>\n\t&nbsp; &nbsp;AND CL.LOOKUP_TYPE = &#39;ORGANIZATION_TYPE&#39;<br \/>\n\t&nbsp; &nbsp;AND CL.LOOKUP_CODE = &#39;LEGAL_ENTITY&#39;<br \/>\n\t&nbsp; &nbsp;AND FL.LOOKUP_TYPE(+) = &#39;XTR_DEAL_PRICE_MODELS&#39;<br \/>\n\t&nbsp; &nbsp;AND FL.LOOKUP_CODE(+) = BANKACCTUSEEO.PRICING_MODEL<br \/>\n\t&nbsp; &nbsp;AND BANKACCTUSEEO.NEW_AR_RCPTS_RECEIVABLE_TRX_ID =<br \/>\n\t&nbsp; &nbsp; &nbsp; &nbsp;RA.RECEIVABLES_TRX_ID(+);<\/p>\n","protected":false},"excerpt":{"rendered":"<p>&nbsp; &nbsp; \u4eca\u5929\u7ed9\u5ba2\u6237\u63d0\u53d6\u6240\u6709\u94f6\u884c\u8d26\u6237\u7684\u673a\u6784\u5206\u914d\u4fe1\u606f\uff0c\u5199\u4e86\u4ee5\u4e0b\u4e00\u6bb5SQL\u547d\u4ee4\uff0c\u8bb0\u5f55\u4e0b\u6765\u4ee5\u5907\u540e\u7528 &hellip; <a href=\"https:\/\/www.llku.com\/?p=301\" class=\"more-link\">\u7ee7\u7eed\u9605\u8bfb<span class=\"screen-reader-text\">\u83b7\u53d6\u94f6\u884c\u8d26\u6237\u5206\u914d\u660e\u7ec6SQL<\/span> <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[6],"tags":[60],"_links":{"self":[{"href":"https:\/\/www.llku.com\/index.php?rest_route=\/wp\/v2\/posts\/301"}],"collection":[{"href":"https:\/\/www.llku.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.llku.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.llku.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.llku.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=301"}],"version-history":[{"count":2,"href":"https:\/\/www.llku.com\/index.php?rest_route=\/wp\/v2\/posts\/301\/revisions"}],"predecessor-version":[{"id":303,"href":"https:\/\/www.llku.com\/index.php?rest_route=\/wp\/v2\/posts\/301\/revisions\/303"}],"wp:attachment":[{"href":"https:\/\/www.llku.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=301"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.llku.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=301"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.llku.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=301"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}