{"id":53,"date":"2011-04-18T19:32:00","date_gmt":"2011-04-18T19:32:00","guid":{"rendered":"http:\/\/www.sapbpc.sapag.co.in\/?p=53"},"modified":"2011-04-18T19:32:00","modified_gmt":"2011-04-18T19:32:00","slug":"how-to-create-custom-ssis-package-for-unlocking-workstatus-of-selected-base-member-without-unlocking-other-workstatus-of-memebrs-in-sap-bpc-microsoft-platform","status":"publish","type":"post","link":"https:\/\/www.sapbpc.sapag.co.in\/?p=53","title":{"rendered":"How to create custom SSIS package for unlocking workstatus of selected base member without unlocking other workstatus of memebrs in SAP BPC (Microsoft Platform)"},"content":{"rendered":"

SAP BPC has Workstatus Function that user can or can\u2019t submit their data from client according to workstatus state value but some users complain below things.<\/p>\n

a.     If one of the child entity wants to adjust value after all entity\u2019s workstatus set as \u2018locked\u2019,  there is no way to allow submitting data from only that entity. Admin has to unlock all entity so Admin can\u2019t make sure others do not submit data during workstatus is unlocked.<\/p>\n

b.     Even though there is TOPDOWN application parameter exists, it needs to unlock from top member to based member.<\/p>\n

c.     In addition, if user wants to change status, it can\u2019t change it from \u2018Locked(Approved)\u2019 to \u2018Unlocked\u2019. It means if user has 4 states in their workstatus, they have to change 3 times using current UI.<\/p>\n

Tbl<app>Lock table has all workstatus information so user can solve above issues using Data Manager custom package. This blog article will help to create it.<\/p>\n

Note: Create custom package should be done by customers and partners. This document is just helping to create package and doesn\u2019t guarantee its result. SAP strongly recommends testing custom package after it is created by users or partners. <\/strong><\/p>\n

Package will work based on the selected base members.<\/p>\n

For example, if user selects Actual, Budget and 2010.JAN, 2010.FEB, SalesUSNeast, SalesWest it will change below combinations.<\/p>\n

Actual, 2010.JAN, SalesUSNeast<\/p>\n

Actual, 2010.JAN, SalesWest<\/p>\n

Actual, 2010.FEB, SalesUSNeast<\/p>\n

Actual, 2010.FEB, SalesWest<\/p>\n

Budget, 2010.JAN, SalesUSNeast<\/p>\n

Budget, 2010.JAN, SalesWest<\/p>\n

Budget, 2010.FEB, SalesUSNeast<\/p>\n

Budget, 2010.FEB, SalesWest<\/p>\n

Here is detail design of SSIS package.<\/p>\n

a. Package Screen shot<\/strong><\/p>\n

\"image\"<\/p>\n

-. Package will create SQL statement in the script task<\/p>\n

-. ExecuteSQL task will execute that SQL statement<\/p>\n

-. ForeachLoopContainter will execute stored procedure as many as combination of selected members.<\/p>\n

   b. SQL statement for creating all combination of selected base members<\/u><\/strong><\/p>\n

For example, if user selects below members, query and result set will be same as below. <\/p>\n

Category Dimension: ‘Actual’,’Budget’<\/p>\n

Time Dimension: ‘2007.JAN’,’2007.FEB’<\/p>\n

Entity Dimension: ‘SalesUSNeast’,’SalesSouth’<\/p>\n

<Query>
select a.*, b.* , c.* from ( select ‘actual’ as category union select ‘budget’ ) as a, (select ‘2007.JAN’ as [time]union select ‘2007.FEB’ ) as b, (select ‘salesusNeast’ as [entity]union select ‘salesSouth’ ) as c order by category,[time],entity <\/p>\n

<Resultset> <\/p>\n

actual   2007.FEB    salesSouth<\/p>\n

actual   2007.FEB    salesusNeast           <\/p>\n

actual   2007.JAN    salesSouth<\/p>\n

actual   2007.JAN    salesusNeast<\/p>\n

budget   2007.FEB    salesSouth<\/p>\n

budget   2007.FEB    salesusNeast<\/p>\n

budget   2007.JAN    salesSouth<\/p>\n

budget   2007.JAN    salesusNeast<\/p>\n

c.  Stored procedures that needs to be executed in the loop container<\/u><\/strong><\/p>\n

<For ChangeBasememberWorkStatus> <\/p>\n

EXEC up_SetWorkStatusFinance @StatusCode=2,@UpdateBy=N'<Execution user>,@Category=N’ACTUAL’,@Entity=N’Worldwide1′,@Time=N’2006.TOTAL’<\/p>\n

<For ChangeWorkstatuswithChildren > <\/p>\n

EXEC up_SetWorkStatusIncludeChildrenFinance @StatusCode=2,@UpdateBy=N'<Execution User’,@Category=N’ACTUAL’,@Entity=N’Worldwide1′,@Time=N’2006.TOTAL’<\/p>\n

d. <\/strong>Data Manager Script for receiving selected dimension members<\/strong><\/u><\/p>\n

You can define Global variable and use this value for creating SQL statement in the First script task<\/p>\n

GLOBAL(A1,%CATEGORY_SET%)<\/p>\n

       Note: Global variable is case sensitive so please define it as UPPERCASE.<\/strong><\/p>\n

One of my customers created this package with SAP Partner based on this article and used it in the production environment now. Of course, this is not for usual case but might be useful for controlling complex workstaus without affecting workstatus of other entity.<\/p>\n

SAP BPC Tutorials | SAP BPC Training | SAP BPC Interview Questions |SAP BPC Books<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"

SAP BPC has Workstatus Function that user can or can\u2019t submit their data from client according to workstatus state value but some users complain below things. a.     If one of the child entity wants to adjust value after all entity\u2019s … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-53","post","type-post","status-publish","format-standard","hentry","category-sap-bpc-tutorials"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.sapbpc.sapag.co.in\/index.php?rest_route=\/wp\/v2\/posts\/53","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sapbpc.sapag.co.in\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sapbpc.sapag.co.in\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sapbpc.sapag.co.in\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sapbpc.sapag.co.in\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=53"}],"version-history":[{"count":0,"href":"https:\/\/www.sapbpc.sapag.co.in\/index.php?rest_route=\/wp\/v2\/posts\/53\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sapbpc.sapag.co.in\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=53"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sapbpc.sapag.co.in\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=53"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sapbpc.sapag.co.in\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=53"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}