{"id":764,"date":"2013-04-03T16:42:00","date_gmt":"2013-04-03T08:42:00","guid":{"rendered":"http:\/\/j178.mtgbb.com\/?p=764"},"modified":"2013-12-20T16:11:43","modified_gmt":"2013-12-20T08:11:43","slug":"discoverer-%e7%9b%b8%e9%97%9c-sql","status":"publish","type":"post","link":"https:\/\/j178.mtgbb.com\/?p=764","title":{"rendered":"Discoverer \u76f8\u95dc SQL"},"content":{"rendered":"<p>\u53c3\u8003\u8cc7\u6599: <a href=\"http:\/\/www.jaggy.com\/community\/message\/1242\" title=\"\u9019\u88e1\" target=\"_blank\">\u9019\u88e1<\/a><\/p>\n<p>1. \u67e5\u8a62\u6240\u6709 report \u6e05\u55ae<br \/>\n2. \u7d71\u8a08\u4f7f\u7528\u7387<br \/>\n3. \u67e5\u8a62\u6240\u6709 report \u8207 \u53ef\u4f7f\u7528\u6b0a\u9650<\/p>\n<p><!--more--><\/p>\n<p>1. \u67e5\u8a62\u6240\u6709 report \u6e05\u55ae<\/p>\n<pre lang=\"oracle8\">\r\nSELECT d.doc_id,\r\n       d.doc_name,\r\n       d.doc_description,\r\n       d.doc_updated_date,\r\n       decode(substr(d.doc_updated_by, 1, 1),\r\n              '#',\r\n              (SELECT fu.user_name\r\n                 FROM fnd_user fu\r\n                WHERE '#' || fu.user_id = d.doc_created_by),\r\n              d.doc_updated_by) doc_updated_by,\r\n       d.doc_created_date,\r\n       decode(substr(d.doc_created_by, 1, 1),\r\n              '#',\r\n              (SELECT fu.user_name\r\n                 FROM fnd_user fu\r\n                WHERE '#' || fu.user_id = d.doc_created_by),\r\n              d.doc_created_by) doc_created_by\r\n  FROM eul_us.eul4_documents d\r\n WHERE d.doc_created_date > '15-JUN-2008'\r\n ORDER BY d.doc_id\r\n<\/pre>\n<p>2. \u7d71\u8a08\u4f7f\u7528\u7387<\/p>\n<pre lang=\"oracle8\">\r\nSELECT decode(substr(a.qs_doc_owner, 1, 1),\r\n              '#',\r\n              (SELECT fu.user_name\r\n                 FROM fnd_user fu\r\n                WHERE '#' || fu.user_id = a.qs_doc_owner),\r\n              a.qs_doc_owner) owner,\r\n       a.qs_doc_name workbook,\r\n       a.qs_doc_details worksheet,\r\n       COUNT(1) run_count,\r\n       MAX(a.qs_created_date) last_used,\r\n       MIN(a.qs_created_date) first_used\r\n  FROM eul_us.eul4_qpp_stats a, eul_us.eul4_documents b\r\n WHERE a.qs_created_date > '01-JAN-2007' --ONLY recent reports\r\n       AND a.qs_doc_name = b.doc_name\r\n GROUP BY a.qs_doc_owner, a.qs_doc_name, a.qs_doc_details\r\n ORDER BY run_count DESC\r\n<\/pre>\n<p>3. \u67e5\u8a62\u6240\u6709 report \u8207 \u53ef\u4f7f\u7528\u6b0a\u9650<\/p>\n<pre lang=\"oracle8\">\r\nSELECT d.doc_id,\r\n       fu.user_name          workbook_owner,\r\n       d.doc_name            workbook_name, --, u.eu_username\r\n       r.responsibility_name shared_to_resp,\r\n       su.user_name          shared_to_user --, ap.*\r\n  FROM eul_us.eul4_access_privs ap,\r\n       eul_us.eul4_documents d,\r\n       eul_us.eul4_eul_users u,\r\n       (SELECT ('#' || responsibility_id || '#' || application_id) ora_resp,\r\n               responsibility_name\r\n          FROM fnd_responsibility_tl) r,\r\n       (SELECT ('#' || user_id) ora_user, user_name FROM fnd_user) su,\r\n       fnd_user fu\r\n WHERE 1 = 1\r\n       AND ap.gd_doc_id IS NOT NULL\r\n       AND ap.gd_doc_id = d.doc_id\r\n       AND ap.ap_eu_id = u.eu_id\r\n       AND r.ora_resp(+) = u.eu_username\r\n       AND su.ora_user(+) = u.eu_username\r\n       AND d.doc_created_by = '#' || fu.user_id\r\n ORDER BY 2, 3;\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>\u53c3\u8003\u8cc7\u6599: \u9019\u88e1 1. \u67e5\u8a62\u6240\u6709 report \u6e05\u55ae 2. \u7d71\u8a08\u4f7f\u7528\u7387 3. \u67e5\u8a62\u6240\u6709 report \u8207 \u53ef\u4f7f &hellip;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[22,12],"tags":[49,57],"class_list":["post-764","post","type-post","status-publish","format-standard","hentry","category-oracle-erp","category-database","tag-discoverer","tag-oracle-erp"],"_links":{"self":[{"href":"https:\/\/j178.mtgbb.com\/index.php?rest_route=\/wp\/v2\/posts\/764","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/j178.mtgbb.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/j178.mtgbb.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/j178.mtgbb.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/j178.mtgbb.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=764"}],"version-history":[{"count":0,"href":"https:\/\/j178.mtgbb.com\/index.php?rest_route=\/wp\/v2\/posts\/764\/revisions"}],"wp:attachment":[{"href":"https:\/\/j178.mtgbb.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=764"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/j178.mtgbb.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=764"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/j178.mtgbb.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=764"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}