{"id":1850,"date":"2011-04-17T14:53:17","date_gmt":"2011-04-17T09:23:17","guid":{"rendered":"http:\/\/sendyoursmiles.com\/?p=1850"},"modified":"2011-04-17T14:53:17","modified_gmt":"2011-04-17T09:23:17","slug":"workflow-tables-and-queries","status":"publish","type":"post","link":"https:\/\/erpschools.com\/erps\/workflow-tools\/workflow-tables-and-queries","title":{"rendered":"Workflow Tables and Queries"},"content":{"rendered":"

This articles contains all the table information related to Oracle Workflows and queries joining these tables.<\/p>\n

WORKFLOW TABLES<\/p>\n


\nSELECT * FROM WF_USER_ROLE_ASSIGNMENTS
\nSELECT * FROM WF_USER_ROLES
\nSELECT * FROM WF_ROLES
\nSELECT * FROM WF_ITEMS
\nSELECT * FROM WF_ITEM_ATTRIBUTES
\nSELECT * FROM WF_ITEM_ATTRIBUTE_VALUES
\nSELECT * FROM WF_ITEM_ATTRIBUTES_TL
\nSELECT * FROM WF_ACTIVITIES
\nSELECT * FROM WF_ACTIVITIES_TL
\nSELECT * FROM WF_ACTIVITY_ATTRIBUTES
\nSELECT * FROM WF_ACTIVITY_ATTRIBUTES_TL
\nSELECT * FROM WF_ACTIVITY_TRANSITIONS
\nSELECT * FROM WF_DEFERRED--WF_CONTROL<\/p>\n

SELECT * FROM WF_ACTIVITY_ATTR_VALUES
\nWHERE NAME LIKE '%MASTER%'
\nAND PROCESS_ACTIVITY_ID
\nIN(
\nSELECT *-- PROCESS_ACTIVITY
\n FROM WF_ITEM_ACTIVITY_STATUSES
\nWHERE ITEM_TYPE = 'ERP'
\nAND ITEM_KEY ='63865'
\n)<\/p>\n

SELECT * FROM WF_ITEM_TYPES
\nSELECT * FROM WF_LOOKUPS_TL<\/p>\n

SELECT * FROM WF_NOTIFICATIONS
\nWHERE MESSAGE_TYPE ='ERP'
\nORDER BY BEGIN_DATE DESC<\/p>\n

SELECT * FROM WF_NOTIFICATION_ATTRIBUTES
\nSELECT * FROM WF_MESSAGES
\nSELECT * FROM WF_MESSAGES_TL
\nSELECT * FROM WF_MESSAGE_ATTRIBUTES
\nSELECT * FROM WF_MESSAGE_ATTRIBUTES_TL
\nSELECT * FROM WF_ETS
\nSELECT * FROM WF_PROCESS_ACTIVITIES
\n<\/code><\/p>\n

LIST OF ACTIVITIES FOR AN ITEMTYPE
\nAccepts Workflow itemtype \/ shortname as input parameter and will all the activities involved along with the status and user name to whom the current activity is assigned.<\/p>\n


\nSELECT A.ITEM_KEY,
\n B.ACTIVITY_NAME,
\n A.ACTIVITY_STATUS,
\n A.ACTIVITY_RESULT_CODE,
\n A.ASSIGNED_USER,
\n A.BEGIN_DATE,
\n A.END_DATE
\nFROM WF_ITEM_ACTIVITY_STATUSES A,
\n WF_PROCESS_ACTIVITIES B
\nWHERE A.PROCESS_ACTIVITY = B.INSTANCE_ID(+)
\nAND B.PROCESS_ITEM_TYPE = A.ITEM_TYPE
\nAND A.ITEM_TYPE = 'ERP'
\nAND A.ITEM_KEY = 64077
\nAND ACTIVITY_NAME IN ('PLANNING','PURCHASING','MFGFINANCE','CSD','TAX')
\n<\/code><\/p>\n

TO FIND FROM HOW MANY DAYS AN ACTIVITY IS PENDING<\/p>\n

Accepts workflow itemtype and activity as input variables and the results will provide the time frame explaining from how long the activity is pending along with the username whose action is req<\/p>\n


\nSELECT B.ACTIVITY_NAME,
\n TRUNC(SYSDATE) - TRUNC(BEGIN_DATE) PENDING_FROM_NO_OF_DAYS,
\n COUNT(B.ACTIVITY_NAME) TOTAL_PENDING
\nFROM WF_ITEM_ACTIVITY_STATUSES A,
\n WF_PROCESS_ACTIVITIES B
\nWHERE A.PROCESS_ACTIVITY = B.INSTANCE_ID
\nAND B.PROCESS_ITEM_TYPE = A.ITEM_TYPE
\nAND A.ITEM_TYPE = 'ERP'
\n--AND A.ITEM_KEY = 1131
\nAND END_DATE IS NULL
\nAND ACTIVITY_STATUS != 'ERROR'
\nAND ACTIVITY_NAME IN ('PLANNING','PURCHASING','MFGFINANCE','CSD','TAX')
\nGROUP BY ACTIVITY_NAME,
\nTRUNC(SYSDATE) - TRUNC(BEGIN_DATE)
\nORDER BY ACTIVITY_NAME,
\nPENDING_FROM_NO_OF_DAYS
\n<\/code><\/p>\n

LIST OF ACTIVITIES THAT ARE PENDING FROM N DAYS<\/p>\n

<\/p>\n

SELECT SUM(TOTAL_PENDING) PENDING_LESS_THAN_5DAYS
\nFROM
\n(SELECT B.ACTIVITY_NAME,
\n TRUNC(SYSDATE) - TRUNC(BEGIN_DATE) PENDING_FROM_NO_OF_DAYS,
\n COUNT(B.ACTIVITY_NAME) TOTAL_PENDING
\nFROM WF_ITEM_ACTIVITY_STATUSES A,
\n WF_PROCESS_ACTIVITIES B
\nWHERE A.PROCESS_ACTIVITY = B.INSTANCE_ID
\nAND B.PROCESS_ITEM_TYPE = A.ITEM_TYPE
\nAND A.ITEM_TYPE = 'ERP'
\n--AND A.ITEM_KEY = 1131
\nAND END_DATE IS NULL
\nAND ACTIVITY_STATUS != 'ERROR'
\nAND ACTIVITY_NAME IN ('PLANNING','PURCHASING','MFGFINANCE','CSD','TAX')
\nGROUP BY ACTIVITY_NAME,
\nTRUNC(SYSDATE) - TRUNC(BEGIN_DATE)
\nORDER BY ACTIVITY_NAME,
\n PENDING_FROM_NO_OF_DAYS ) FIVE_DAYS
\nWHERE FIVE_DAYS.PENDING_FROM_NO_OF_DAYS < 5\n\n<\/code><\/p>\n","protected":false},"excerpt":{"rendered":"

This articles contains all the table information related to Oracle Workflows and queries joining these tables. WORKFLOW TABLES SELECT * FROM WF_USER_ROLE_ASSIGNMENTS SELECT * FROM WF_USER_ROLES SELECT * FROM WF_ROLES SELECT * FROM WF_ITEMS SELECT * FROM WF_ITEM_ATTRIBUTES SELECT * FROM WF_ITEM_ATTRIBUTE_VALUES SELECT * FROM WF_ITEM_ATTRIBUTES_TL SELECT * FROM WF_ACTIVITIES SELECT * FROM WF_ACTIVITIES_TL SELECT […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[76],"tags":[],"class_list":["post-1850","post","type-post","status-publish","format-standard","hentry","category-workflow-tools"],"yoast_head":"\noracle apps workflow tables scripts<\/title>\n<meta name=\"description\" content=\"oracle apps workflow scripts tables\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/erpschools.com\/erps\/workflow-tools\/workflow-tables-and-queries\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Prudhvi\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/erpschools.com\/erps\/workflow-tools\/workflow-tables-and-queries#article\",\"isPartOf\":{\"@id\":\"https:\/\/erpschools.com\/erps\/workflow-tools\/workflow-tables-and-queries\"},\"author\":{\"name\":\"Prudhvi\",\"@id\":\"https:\/\/erpschools.com\/erps\/#\/schema\/person\/dbed9bb7fb66aa7a700fc565da024512\"},\"headline\":\"Workflow Tables and Queries\",\"datePublished\":\"2011-04-17T09:23:17+00:00\",\"dateModified\":\"2011-04-17T09:23:17+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/erpschools.com\/erps\/workflow-tools\/workflow-tables-and-queries\"},\"wordCount\":106,\"commentCount\":3,\"publisher\":{\"@id\":\"https:\/\/erpschools.com\/erps\/#organization\"},\"articleSection\":[\"Workflow\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/erpschools.com\/erps\/workflow-tools\/workflow-tables-and-queries#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/erpschools.com\/erps\/workflow-tools\/workflow-tables-and-queries\",\"url\":\"https:\/\/erpschools.com\/erps\/workflow-tools\/workflow-tables-and-queries\",\"name\":\"oracle apps workflow tables scripts\",\"isPartOf\":{\"@id\":\"https:\/\/erpschools.com\/erps\/#website\"},\"datePublished\":\"2011-04-17T09:23:17+00:00\",\"dateModified\":\"2011-04-17T09:23:17+00:00\",\"description\":\"oracle apps workflow scripts tables\",\"breadcrumb\":{\"@id\":\"https:\/\/erpschools.com\/erps\/workflow-tools\/workflow-tables-and-queries#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/erpschools.com\/erps\/workflow-tools\/workflow-tables-and-queries\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/erpschools.com\/erps\/workflow-tools\/workflow-tables-and-queries#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/erpschools.com\/erps\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Workflow Tables and Queries\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/erpschools.com\/erps\/#website\",\"url\":\"https:\/\/erpschools.com\/erps\/\",\"name\":\"erpSchools\",\"description\":\"Oracle Apps\",\"publisher\":{\"@id\":\"https:\/\/erpschools.com\/erps\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/erpschools.com\/erps\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/erpschools.com\/erps\/#organization\",\"name\":\"erpSchools\",\"url\":\"https:\/\/erpschools.com\/erps\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/erpschools.com\/erps\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/erpschools.com\/erps\/wp-content\/uploads\/img\/erps_logo7.png\",\"contentUrl\":\"https:\/\/erpschools.com\/erps\/wp-content\/uploads\/img\/erps_logo7.png\",\"width\":250,\"height\":60,\"caption\":\"erpSchools\"},\"image\":{\"@id\":\"https:\/\/erpschools.com\/erps\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"http:\/\/facebook.com\/erpschools\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/erpschools.com\/erps\/#\/schema\/person\/dbed9bb7fb66aa7a700fc565da024512\",\"name\":\"Prudhvi\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/erpschools.com\/erps\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/488cec3605845b95cb20e60c67a8f5c7e74b65a305525c8006315d524f120db9?s=96&d=blank&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/488cec3605845b95cb20e60c67a8f5c7e74b65a305525c8006315d524f120db9?s=96&d=blank&r=g\",\"caption\":\"Prudhvi\"},\"sameAs\":[\"http:\/\/www.erpschools.com\"],\"url\":\"https:\/\/erpschools.com\/erps\/author\/prudhvi\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"oracle apps workflow tables scripts","description":"oracle apps workflow scripts tables","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/erpschools.com\/erps\/workflow-tools\/workflow-tables-and-queries","twitter_misc":{"Written by":"Prudhvi","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/erpschools.com\/erps\/workflow-tools\/workflow-tables-and-queries#article","isPartOf":{"@id":"https:\/\/erpschools.com\/erps\/workflow-tools\/workflow-tables-and-queries"},"author":{"name":"Prudhvi","@id":"https:\/\/erpschools.com\/erps\/#\/schema\/person\/dbed9bb7fb66aa7a700fc565da024512"},"headline":"Workflow Tables and Queries","datePublished":"2011-04-17T09:23:17+00:00","dateModified":"2011-04-17T09:23:17+00:00","mainEntityOfPage":{"@id":"https:\/\/erpschools.com\/erps\/workflow-tools\/workflow-tables-and-queries"},"wordCount":106,"commentCount":3,"publisher":{"@id":"https:\/\/erpschools.com\/erps\/#organization"},"articleSection":["Workflow"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/erpschools.com\/erps\/workflow-tools\/workflow-tables-and-queries#respond"]}]},{"@type":"WebPage","@id":"https:\/\/erpschools.com\/erps\/workflow-tools\/workflow-tables-and-queries","url":"https:\/\/erpschools.com\/erps\/workflow-tools\/workflow-tables-and-queries","name":"oracle apps workflow tables scripts","isPartOf":{"@id":"https:\/\/erpschools.com\/erps\/#website"},"datePublished":"2011-04-17T09:23:17+00:00","dateModified":"2011-04-17T09:23:17+00:00","description":"oracle apps workflow scripts tables","breadcrumb":{"@id":"https:\/\/erpschools.com\/erps\/workflow-tools\/workflow-tables-and-queries#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/erpschools.com\/erps\/workflow-tools\/workflow-tables-and-queries"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/erpschools.com\/erps\/workflow-tools\/workflow-tables-and-queries#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/erpschools.com\/erps"},{"@type":"ListItem","position":2,"name":"Workflow Tables and Queries"}]},{"@type":"WebSite","@id":"https:\/\/erpschools.com\/erps\/#website","url":"https:\/\/erpschools.com\/erps\/","name":"erpSchools","description":"Oracle Apps","publisher":{"@id":"https:\/\/erpschools.com\/erps\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/erpschools.com\/erps\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/erpschools.com\/erps\/#organization","name":"erpSchools","url":"https:\/\/erpschools.com\/erps\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/erpschools.com\/erps\/#\/schema\/logo\/image\/","url":"https:\/\/erpschools.com\/erps\/wp-content\/uploads\/img\/erps_logo7.png","contentUrl":"https:\/\/erpschools.com\/erps\/wp-content\/uploads\/img\/erps_logo7.png","width":250,"height":60,"caption":"erpSchools"},"image":{"@id":"https:\/\/erpschools.com\/erps\/#\/schema\/logo\/image\/"},"sameAs":["http:\/\/facebook.com\/erpschools"]},{"@type":"Person","@id":"https:\/\/erpschools.com\/erps\/#\/schema\/person\/dbed9bb7fb66aa7a700fc565da024512","name":"Prudhvi","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/erpschools.com\/erps\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/488cec3605845b95cb20e60c67a8f5c7e74b65a305525c8006315d524f120db9?s=96&d=blank&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/488cec3605845b95cb20e60c67a8f5c7e74b65a305525c8006315d524f120db9?s=96&d=blank&r=g","caption":"Prudhvi"},"sameAs":["http:\/\/www.erpschools.com"],"url":"https:\/\/erpschools.com\/erps\/author\/prudhvi"}]}},"_links":{"self":[{"href":"https:\/\/erpschools.com\/erps\/wp-json\/wp\/v2\/posts\/1850","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/erpschools.com\/erps\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/erpschools.com\/erps\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/erpschools.com\/erps\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/erpschools.com\/erps\/wp-json\/wp\/v2\/comments?post=1850"}],"version-history":[{"count":0,"href":"https:\/\/erpschools.com\/erps\/wp-json\/wp\/v2\/posts\/1850\/revisions"}],"wp:attachment":[{"href":"https:\/\/erpschools.com\/erps\/wp-json\/wp\/v2\/media?parent=1850"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/erpschools.com\/erps\/wp-json\/wp\/v2\/categories?post=1850"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/erpschools.com\/erps\/wp-json\/wp\/v2\/tags?post=1850"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}