Om-cogs--generator 654l5d

ORACLE CORPORATION

Oracle Services, Centers of Expertise

OM: Cost of Goods Sold Generator

CENTER OF EXPERTISE

OM: Cost of Goods Sold Generator

 Oracle Corporation

Table of Contents INTRODUCTION

3

WHY DO YOU NEED A COGS ?

4

INVENTORY INTERFACE/RMA INTERFACE

7

RECEIVABLES INTERFACE

8

WHAT ABOUT DROP SHIPMENTS?

8

COGS GENERATOR

9

COGS Generator Chart of s Set of Books COGS

9 9 10 11

DETERMINING THE CHART OF S

11

LOADING THE WORKFLOW BUILDER DEFINITION

16

Loading a Workflow Definition from a database Saving the Workflow Definition to a File

WORKFLOW BUILDER OBJECTS

16 18

19

Item Type Attributes Processes Functions Lookup Types

19 22 23 26 27

CONCLUDING OBSERVATIONS ABOUT WORKFLOW OBJECTS

29

THE ‘GENERATE DEFAULT ’ PROCESS

30

THE SEEDED COGS GENERATOR FUNCTIONS

32

Get CCID for a line Get CCID from Salesrep’s revenue segment ‘Get CCID from the Model for an option’ and ‘Check if the line is an option line’ Get CCID from the Order Type ID Get CCID From the Item and Selling Operating Unit Get Salesrep’s ID Get the Transaction Type for a Commitment ID

i

32 32 33 34 34 34 36

Upgrade from Flexbuilder

36

THE SEEDED STANDARD FLEXFIELD WORKFLOW FUNCTIONS Copy Segment Value from Code Combination Copy Segment Value from other Structure Code Combination Assign Value to Segment

36 36 37 38

A SIMPLE MODIFICATION TO AN EXISTING PROCESS DEFINITION 39 POINTING THE CHART OF S TO THE NEW PROCESS

42

TROUBLESHOOTING THE WORKFLOW PROCESS

43

Debug parameters in the stored procedures Testing the Workflow Process Using the cogs_11i script Running in Synch versus Debug

43 44 47

CONCLUSION

47

ACKNOWLEDGMENTS

48

APPENDIX A: PROFILING SCRIPT

49

APPENDIX B: COGS_11I.SQL SCRIPT:

51

2

COGS Generator – The New Flexbuilder Richard Mercer Oracle Corporation: Center of Expertise

Introduction The COGS Generator incorporates Oracle Workflow to implement a new method of building a Cost Of Goods Sold (COGS) and was introduced first in Release 11. In Release 11i this same concept is implemented with some variations. These variations were necessary since the data model for Order Management and Shipping Execution in 11i differs from that in Release 11. The Oracle Workflow Builder lets you create, view and modify the building process with simple drag and drop operations. In previous releases (9.X and 10.X) the application uses the Flexbuilder functionality to generate the COGS . Although Flexbuilder offers acceptable results, it is difficult to “see” the flow of the generation. Also, it is difficult to implement a custom solution using Flexbuilder due to the limitations Value Sets offer. In addition, the Flexbuilder solution lacks flexibility in many areas, such as in Multi-Org environments. The details of Flexbuilder go beyond the scope of this paper, and will only be touched on for comparisons to the COGS Generator where applicable. For further reference information about Flexbuilder, please see GSX Note: 66537.1 and the Oracle Flexfield s Guide, Release 10. Thanks to the Workflow Builder graphical front end, the flow of the generation is easy to envision. The ability to call PL/SQL procedures and functions offers an almost unlimited amount of flexibility. This is extremely valuable for PL/SQL developers who can leverage their programming knowledge without burdening themselves with the task of learning Value Set functionality. Workflow Builder is easy to use due to its graphical nature. In addition, troubleshooting a customer’s definition is much more simple, since they can save their configuration from the database to a Workflow Definition file (.wft), which you can then open on your local PC. A deeper knowledge of the Workflow Builder will make this paper more meaningful and is highly recommended, but should not be considered a strict pre-requisite. Detailed functionality of Workflow Builder also goes beyond the scope of this discussion, however the applicable objects incorporated by the COGS Generator will be briefly presented. Refer to the Oracle Workflow Guide Release 2.5 (A75397) for detailed information. In order to become comfortable with this product, you should stop now and load the Workflow Builder 2.5 if you do not currently have it installed. The prior release of Workflow Builder (2.0.x) will not be sufficient for Release 11i, since there have been columns added to some of the key Workflow base tables. The currently certified releases are:

3

Oracle Applications 11.0 11i

Oracle Workflow 2.0.3 2.5

The Workflow Builder can be ed by internal Oracle employees from http://www-apps.us.oracle.com/atg/wf/index.html This is a simple installation and instructions are provided via the web site. This will be required to view the process definition provided by the customer in a .wft file. The intent of this paper is to serve as a more general discussion of the OM: COGS Generator. For a specific example in customizing a process, I would suggest reading the rewritten Appendix E for Release 11 COGS Generator available through Metalink. Please keep in mind however, that until that paper is rewritten for the Release 11i version of the COGS Generator, there will be differences. One final note regarding the differences in Release 11 and Release 11i versions of the Generator; these versions do not share the same Item Type. Details about Item Types are covered later in this document. Release 11 uses the item type called ‘Generate Cost of Goods Sold ’. Release 11i uses the item type ‘OM: Generate Cost of Goods Sold ’. For a Release 11 version of the applications you will only see the ‘Generate Cost of Goods Sold ’ item type. However, for Release 11i you will see both. Please be careful to use the applicable item type.

Why do you need a COGS ? The Cost of Goods Sold is used to determine the profit realized from selling a product. For each item in an inventory organization Oracle Applications has the ability to record the type and amount of costs to maintain the item. To view the associated cost for an item use the Item Costs Details form shown below (Costing: Item Costs, Item Costs).

4

For this particular item you see that there is a total Unit Cost of $26.70 made up of ‘Material’ and ‘Material Overhead’ costs. The COGS shown is the default of the item and not the you will be building to interface to inventory as a sales order issue transaction. The idea behind running a business is to sell the product for more than it costs you to build and maintain it. This is how you realize a profit. If you assume no discounts have been applied to the item then you can derive the price strictly from the price list associated with the sales order (Order Management: Pricing, Price Lists, Price List Setup).

5

To sum up the role of Order Management you can say it acts as a method of distributing product to a customer and in turn, interfacing that information regarding the item to Inventory (to decrement stock) and interface data regarding money to Receivables (to invoice the customer) for recognizing revenue. For completeness sake, assume that the shipped quantity is interfaced to Inventory using the ‘Inventory Interface’, and the selling price and associated data is interfaced to Receivables using the ‘Receivables Interface’. So Inventory knows how much it cost to maintain this item and Receivables knows how much money you made from the item. The question becomes how do you determine the profit realized from the sale of an item? The answer is that the costing module can reconcile these two values. Mathematically, for an item it would look something like: Invoiced Amount - Cost of Goods Sold

$55.00 - $26.70

Profit

$28.30

One such report that reconciles these two values is the ‘Margin Analysis Report’ (Cost: Report, Operational Analysis). In order to run this report you first must run the ‘Margin Analysis Load Run’ program to populate the temporary tables with values. The output reflects a detailed analysis of the above mathematical result.

6

The true benefit, however, in segregating the data in this fashion comes in the FSG report functionality. FSG is the Financial Statement Generator, which enables you to write reports for the purpose of analysis and reporting out of the GL. Fortunately, you can write these reports any way you prefer. The ability to flexibly generate the COGS makes the power of reporting out of the GL much more meaningful. This is a reporting tool you actually build via forms by populating tables for the rows and columns that will be displayed. It is cumbersome to set up but is very flexible for writing reports out of the GL tables and far easier than doing this out of Oracle Reports.

Inventory Interface/RMA Interface A few more details regarding the interfaces to inventory are needed at this time. First, consider the interface for a sales order after shipments have been made to a customer. This is the previously mentioned Inventory Interface. When an item is shipped to the customer, information regarding the shipped quantity is ed to the inventory module through the MTL_TRANSACTIONS_INTERFACE table. Among the data ed to inventory includes the shipped quantity, shipped date, picking information, as you can obviously imagine. However, there is an often overlooked, but important piece of information populated into the DISTRIBUTION__ID column of this table. This is the ID that identifies the Cost Of Goods Sold (COGS) for this transaction. When the Inventory Interface inserts a row for a transaction, it only provides the COGS to record cost against; it does not insert the dollar figure of the cost itself. Rather, this amount is calculated and recorded when the transaction is picked up out of the Interface tables and processed by the Material Transaction Manager. At this point you should realize that the purpose of the COGS GENERATOR is to derive the value to place into the MTL_TRANSACTIONS_INTERFACE.DISTRIBUTION__ID column. In other words, the COGS Generator derives the to record the Cost Of Goods Sold against. The itself is made up of segments, which are covered in more detail later. What happens when the customer sends product back to the inventory organization using a Return Material Authorization (RMA)? In a typical scenario, you would receive the product and restock it using the inventory module, and refund the customer using a Credit Memo in Receivables. When the RMA is entered in Order Management and booked, the data is made visible to the receiving clerks in the warehouse; information such as expected quantity to receive, RMA number, Item ID, etc. In Release 11i, the same COGS Generator process is called when the item is received into inventory via the Receipts Form. The COGS Generator in both cases derives this ID. When the RMA has been copied or references a Sales Order, an attempt is made to derive the COGS from the sales order information and not the RMA information. This makes perfect sense, because if you log data against an for the outgoing sales 7

order, you would want to ensure that for the return you offset the data in the same .

Receivables Interface If it is the responsibility of the Order Management process to the ID to the Inventory module, then it would likewise make sense that it would have the same responsibility when interfacing to Receivables. However, this is not the case. When Order Management interfaces invoice information to Receivables, it does so through the RA_INTERFACE_LINES_ALL table. These lines are then picked up on the Receivables side by a program called AutoInvoice. Order Management does not distribution data as it does to Inventory. Receivables has an interface table called RA_INTERFACE_DISTRIBUTIONS_ALL, which can optionally be used and is designed specifically to distribute revenue among s. Order Management does not insert rows into this distribution interface table during the interface process. When the AutoInvoice program does not find rows in the RA_INTERFACE_DISTRIBUTIONS_ALL table related to transactions in the RA_INTERFACE_LINES_ALL table, it provides a default distribution and applies it to the transaction line. Some customers have requested that the software offer the ability to build the and it to the applicable interface table(s). However, at this time, this is considered a customization that can be handled by a trigger on the RA_INTERFACE_LINES_ALL table.

What about Drop Shipments? The ability to process Drop Shipments is relatively new functionality. A Drop Shipment involves entering a sales order as an ‘External’ source type. This causes a purchase order to be issued for the sales order line so a third party vendor may ship the product directly to the customer. In order to recognize the fact that the items have been delivered (so you can invoice the customer), you must perform a ‘receipt’ into a phantom subinventory. At the same time a transaction out of the phantom subinventory must be recorded to keep the On-hand quantity netted out to zero. These transactions ‘into’ and ‘out of’ the inventory organization are processed by the Receiving Transaction Manager and Material Transaction Manager respectively. The transaction recorded going ‘out of’ the inventory organization is done in the same way as a normally shipped line, by using the MTL_TRANSACTIONS_INTERFACE table. As mentioned before, this is the table that contains the DISTRIBUTION__ID for recording Cost of Goods Sold ing information. The Receiving Transaction Manager inputs this record into the MTL_TRANSACTIONS_INTERFACE table upon recognizing the receipt as a Drop

8

Shipment. The value for the DISTRIBUTION__ID column is derived by calling the COGS Generator Workflow process in the same fashion as the Inventory Interface would.

COGS Generator At this time it would be prudent to ensure you understand the key used throughout this paper. COGS Generator This is the workflow process that has replaced the Flexbuilder functionality of Release 10. It uses the workflow processing engine to derive the Cost of Goods Sold for a transaction interfaced to Inventory from Order Management/Shipping. Chart of s The Chart of s is the ing structure definition and you will often hear this referred to as the ‘structure’ or ‘ing structure’. From this you define the number of segments making up an , the name of the segments and the data limitations of the segments. For instance in the VISION Database you use the ‘Operations ing Flex’ as the Chart of s structure for the set of books referenced. The segment definitions can be retrieved from the Define Key Flexfield Segments form (Setup ⇒ Financials ⇒ Flexfields ⇒ Key ⇒ Segments). By choosing the flashlight and picking the ‘ing Flexfield’ you will see the ‘Operations ing Flex’. Next choosing the Segments button reveals the following:

9

This structure consists of 5 segments each with its own name. The ‘Column’ name maps to the column name in the GL_CODE_COMBINATIONS table. Many times you will see a reference to the ID_FLEX_NUM. When you see this for an ID_FLEX_CODE = ‘GL#’ (ing Flexfield), the ID_FLEX_NUM represents the specific structure for the Chart of s. In this case the ID_FLEX_NUM = 101 for the ‘Operations ing Flex’. Hint: use the Examine utility to find this information while in the screen above. Set of Books The set of books is where the Chart of s is determined for the referenced responsibility. Three entities make the Set of Books unique, and are referred to as the 3 C’s: Currency, Calendar and Chart of s. The Set of Books for the Inventory Organization (Warehouse) into which you will be interfacing the COGS ID needs to be determined. The Oracle Order Management application uses the Set of Books tied to the operating unit viewed from the Define Organizations form. This is important for segments derived from the Operating Unit level (i.e. those from Multi-Org partitioned tables) such as the Order Type COGS and Salesperson Revenue . The ‘Order Management, Super’ responsibility is tied to the ‘Vision Operations’ operating Unit, which uses the ‘Vision Operations (USA)’ Set of Books.

10

COGS This is the Cost Of Goods Sold which is the that maps to a row in the GL_CODE_COMBINATIONS table. It is identified by a CODE_COMBINATION_ID (CCID), having a unique combination of SEGMENT values. Assume that a segment combination for the COGS was derived to be 01.540.5360.0000.000. The CCID can be found using the following statement: SQL> select code_combination_id from gl_code_combinations where segment1 = '01' and segment2 = '540' and segment3 = '5360' and segment4 = '0000' and segment5 = '000' and chart_of_s_id = 101; CODE_COMBINATION_ID ------------------16801

Determining the Chart of s You should now review how to find out the Chart of s referred to by a particular shipment transaction. The steps for determining this are: Get the value of the Set of Books for the inventory organization for which you wish to interface the transactions. Get this from the Organization Definitions form. (Inv: Setup ⇒ Organization ⇒ Organizations). Query the name of the inventory organization desired, then with the cursor on the ‘Inventory Organization’ organization classification, click the ‘Others’ button. Choose ‘ing Information’. Tab once and you will see the following screen where the Set of Books for this inventory organization is assigned.

11

Note: It may be necessary to determine the Set of Books for the Order Management responsibility for which the order is assigned. Accomplish this task in the same way as with the inventory organization as demonstrated above, except place the cursor on the ‘Operating Unit’ line rather than the ‘Inventory Organization’ line.

Query this Set of Books up in the Define Set of Books form (Setup ⇒ Financials ⇒ Books). Record the value for the ‘Chart of s’.

12

Now query up the Chart of s in the Define Key Flexfield Segments form (Setup ⇒ Financials ⇒ Flexfields ⇒ Key ⇒ Segments) to find the number and type of segments for this ing structure. (See figure in the previous section). Finally, find the workflow process this chart of s uses when building the COGS from the Generator Processes form (Setup ⇒ Financials ⇒ Flexfields ⇒ Key ⇒ s)

13

Scripts for finding the above objects follows: First get the SET_OF_BOOKS_ID for the inventory organization (warehouse) from the following scripts: Column warehouse format a30; column set_of_books_id format a16; select ood. organization_id, organization_name warehouse, org_information1 set_of_books_id From org_organization_definitions ood, Hr_organization_information hoi Where ood.organization_id = hoi.organization_id and ood.inventory_enabled_flag = 'Y' and hoi.org_information_context = 'ing Information' order by ood.organization_id; SQL> / ORGANIZATION_ID --------------204 207 208 209 210 229 458 498

WAREHOUSE SET_OF_BOOKS_ID ------------------------------ ---------------Vision Operations 1 Seattle Manufacturing 1 Chicago Subassembly Plant 1 Boston Manufacturing 1 Miami Distribution Center 1 Singapore Distribution Center 21 Vision Services 62 Vision ADB 42

14

600 602 603 604 605 606

Vision Project Mfg Atlanta Manufacturing Los Angeles Manufacturing Cleveland Manufacturing San Diego Manufacturing Dallas Manufacturing

102 102 102 102 102 1

14 rows selected.

Suppose you would like to get the chart of s for the SET_OF_BOOKS_ID=1. COLUMN S_OF_B_ID FORMAT 9999999; Select NAME SET_OF_BOOKS, SET_OF_BOOKS_ID S_OF_B_ID, CHART_OF_S_ID, CHART_OF_S_NAME From GL_SETS_OF_BOOKS_V WHERE SET_OF_BOOKS_ID = &SET_OF_BOOKS_ID; Enter value for set_of_books_id: 1 SET_OF_BOOKS SOFB_ID CHART_OF_S_ID CHART_OF_S_NAME ------------------------------ --------- -------------------- --------------------------Vision Operations (USA) 1 101 Operations ing Flex

Now you see the CHART_OF_S_ID and the name of ‘Operations ing Flex’. If you would like to see the segment information for this structure, then use: SQL> select name, set_of_books_id, chart_of_s_id from gl_sets_of_books SQL> / NAME SET_OF_BOOKS_ID CHART_OF_S_ID ------------------------------ --------------- -------------------Vision Operations (USA) 1 101 Vision Distribution (SNG) 21 50195 Vision Corporation (UK) 22 50194 Vision ADB (USA) 42 50214 Vision Services (USA) 62 50234 Vision ADB Holdings (UK) 82 50274 Vision Project Mfg (MRC) 102 50317 Vision Project Mfg (EURO Rpt) 103 50317 Vision Project Mfg (USA Rpt.) 104 50317 9 rows selected.

Next, get the segment definitions for the Chart of s for the Vision Operations (USA) which is CHART_OF_S_ID = 101. select segment_num, segment_name from fnd_id_flex_segments_vl where enabled_flag='Y' and id_flex_code = 'GL#'

15

and id_flex_num = &Chart_of_s_id order by segment_num; Enter value for chart_of_s_id: 101 SEGMENT_NUM ----------1 2 3 4 5

SEGMENT_NAME -----------------------------Company Department Sub- Product

5 rows selected.

Finally find the workflow process assigned to this chart of s for the COGS Generator. select id_flex_num, wf_process_name from fnd_flex_workflow_processes where id_flex_code = 'GL#' and wf_item_type = 'SHPFLXWF' and id_flex_num = &chart_of_s_id; ID_FLEX_NUM WF_PROCESS_NAME ----------- -----------------------------101 DEFAULT__GENERATION

A single script for retrieving this information is provided in Appendix A of this paper.

Loading the Workflow Builder Definition Workflow Builder is a Client side GUI application and can be used to define process flows for use with Oracle Applications. There are 2 methods of loading workflow definitions into Workflow Builder: from the database, and from a file. As mentioned in the introduction, troubleshooting a customer’s database workflow definition is made easier since you can load their workflow definition file (.wft) into your client Workflow Builder application. This is achieved by having the customer load their workflow definition in from their database, and saving it to their client as a file. This creates an ASCII file on the customer’s client PC which is then emailed or FTP’d to you. You simply open up this transferred .wft file in your client Workflow Builder application. Loading a Workflow Definition from a database In order to load the workflow definition file from a database, start the Workflow Builder application on your client. Choose the open folder icon from the Navigator or choose File ⇒ Open… from the menu. The following window appears:

16

The default load source will be from a File. Instead, choose the Database radio button and enter the , and Connect fields for the application database for which you are deg the workflow definition. Clicking on the OK button will display all the workflow Item Types loaded into the database. (NOTE: If this connection is not successful, your system or database and ask them to set up your tnsnames.ora file properly to connect to the desired instance). You want to see the OM: Generate Cost of Goods Sold definition. Choose this definition from the Hidden list and click on the <<Show button. This moves the item to the Visible list as follows.

17

Click the OK button and the workflow definition is loaded into memory on your client.

Although this does not look very interesting at this point, rest assured there is more to come. Currently you are only concerned with loading and saving the definition. When modifications are made to this definition you have the ability to save the changes back to the database. This would be done by simply choosing the save icon or choose File ⇒ Save from the menu. Saving the Workflow Definition to a File Now that you have the workflow definition in memory on the system, you can save it off on your client as an ASCII file with a .wft extension. In order to do this you choose File ⇒ Save As…from the Menu. A similar form as the Open function is displayed. This time you will choose the File option and enter, or Browse the desired name of the file as shown.

18

This is a file that can be delivered via email, ftp, etc. for review by others.

Workflow Builder Objects In order to better understand the entities of the workflow process definition, consider an analogy between these objects and what they represent from a programming perspective. In short, the Item Type would represent the main program call, Attributes represent global variables, Processes are the subprograms, and Functions represent private or public functions for the subprogram. A full description of each of these components exceeds the scope of this paper. For an in depth definition of each of these objects, please refer to the Oracle Workflow Guide Release 2.5 (Part No: (A75397). Item Type By expanding the Navigator for the apps@z11i icon, you will see 2 Item Types. They are the ‘OM: Generate Cost of Goods Sold ’ and the ‘Standard Flexfield Workflow’ as shown:

19

There is one item type for the ‘OM: Generate Cost of Goods Sold ’. It has an internal name of OECOGS. This is the item type that encomes all processes designed to build the COGS when interfacing transactions from Order Management to Inventory. It may contain multiple Processes, mostly because you may desire to build s differently for each Chart of s. Think of this Item Type as the main procedure call to the workflow definition when making a call to build the COGS for a transaction from OE/Shipping to the inventory application. From this main procedure call it will have enough information to decide which of the process flows to use for the Chart of s being referenced. In order to see the Properties of the Item Type, right click on the ‘OM: Generate Cost of Goods Sold ’ icon in the Navigator and choose Properties. You will see the following:

20

The other Item Type displayed is the ‘Standard Flexfield Workflow’. Think of it as the global procedure calls, which are available not only to the ‘OM: Generate Cost of Goods Sold ’, but to all other generator item types. These will have common functions, such as, copying a segment value to the destination , validation of the segment combinations, etc. So, this Item Type contains the public functions available for use by all other processes. Beneath the item types you will find all other entities which make Workflow Builder complete. They include the Attributes, Processes, Notifications, Functions, Messages and Lookup Types. Of these the OM: Generate Cost of Goods Sold uses the Attributes, Processes, Functions and Lookup Types (See the following screen shot).

21

Attributes The best way to understand how the workflow Attributes are used is to think of them as global variables. Through the use of function calls you may assign specific data values to these Attributes for use by other function calls within the Processes. For instance, assume you wish to retrieve segments based on an tied to the Receivables Transaction Type of a Commitment. In order to retrieve the , you must first derive the Transaction Type. You do this with 2 function calls. The first is to determine the Transaction Type, followed by feeding this value to the second function (custom) to find the CCID. The first function must store the value for use by the second function. This is where the Attributes enter the picture. In this particular case the Attribute used is the ‘Transaction Type’ highlighted below.

As you can see there are many more Attributes available. Of these, 12 of them are assigned values when a CCID is requested to be built by the Inventory Interface, and include: Commitment ID Customer ID

22

Order Category Header ID Line ID Order Type ID Organization ID (warehouse ID) Org_id (Operating Unit) Chart of s ID Salesrep Id Inventory Item Id Option Flag As with any variable within a normal program, once the Attribute is assigned a new value, the old value is gone. To see the properties of the Attribute right click on the applicable value and choose Properties.

Finally, it is possible to create custom Attributes. Suppose you wished to store the ‘Line Type Code’ for the line of the sales order for use by a custom Function. You further decide that this value may be needed by a number of Functions within your process flow. It would be prudent then to create an Attribute called ‘Line Type Code’ and employ it to hold the value of the line type. Processes Processes are used to define the Function flow employed to derive the segments of the COGS . For instance, you may wish to derive the first segment of an from the COGS assigned to an order type of the sales order. If there is no value there then you may wish to grab the value from the first segment assigned to the COGS associated with the item on the line. The process would show a flow calling for the order type first and then alternately the item COGS . Expanding the Processes icon reveals the following seeded processes:

23

Customers who have upgraded from version 10 of Oracle Applications and wish to simply use the rules as they were defined in FlexBuilder may use the ‘Generate Using FlexBuilder Rules’ process. This requires some upgrade steps to fulfill this functionality, and can simplify the implementation of Release 11; however, there is no flexibility for modifying the COGS generation in the future using this approach. The ‘Generate Default ’ is a seeded process which builds the COGS in the same manner as the Default definition in Flexbuilder. That is, it retrieves all segments from the COGS assigned to the item in the shipping inventory organization. All Chart of structures call this Process by default. A process is assigned to the Chart of s in the ‘ Generator Processes’ form. This small exercise will be covered in more depth later. By double-clicking on the ‘Generate Default ’ process (see below), you can see the simple process flow. At this point, do not be concerned with the meaning of each function call within this process, but rather just make the point visually that this is where the flow of the generation is defined.

24

Just as with Attributes, or any other object in Workflow, there is the ability to create additional Processes. This is what makes this approach so appealing; it allows an unlimited number of configurations for the generation. And as with other objects, Properties are available for the Processes using the right mouse button (see the screen shot below). Note: The Runnable checkbox of the properties must be set in order to assign this process to a Chart of s.

25

Functions Of all the workflow attributes reviewed in this paper this is the most appropriately named for the programming analogy being made. Functions in the workflow paradigm are analogous to functions from a programming perspective. These functions are incorporated into the workflow Process by dragging them from the Navigator onto an open Process flow.

A workflow Function is represented by an icon, however, it maps to a PL/SQL procedure or function call. So, experienced PL/SQL programmers may leverage their vast knowledge store in generating a flexible and powerful workflow definition. In order to trace an existing icon to its PS/SQL function call, choose Properties from the right mouse button which reveals the following display:

26

This screenshot shows the underlying function for the ‘Get CCID for a line’ Function to be OE_FLEX_COGS_PUB.GET_COST_SALE_ITEM_DERIVED, where OE_FLEX_COGS_PUB is the package name and GET_COST_SALE_ITEM_DERIVED is the function name. What is the purpose, then, in having Functions for both the ‘OM: Generate Cost of Goods Sold ’ and the ‘Standard Flexfield Workflow’? What you can observe here is an analogy to public and private functions. The functions for the ‘OM: Generate Cost of Goods Sold ’ represent private functions, meaning that their intended purpose is only for the generation of a COGS . The ‘Standard Flexfield Workflow’ Functions have a much broader intent. They are not only implemented when defining COGS workflows, but also any workflow process that is used to generate an (i.e. ‘PO Generator’, etc.), and therefore they can be equated to public functions. The purpose of each function is a key element in understanding the COGS Generator, therefore, each seeded function will later be described independently. Expanding the Function icon on the navigator displays the following: Functions may accept input variables in the form of Attributes. This falls in line with the programming analogy. Just as programming functions may accept input variables, so too the Workflow Functions may be fed input values. This is done simply by dragging Attributes from the Navigator onto the Function within the same Navigator. Lookup Types Lookup Types are not implemented as part of the seeded entities in the ‘OM: Generate Cost of Goods Sold ’ Item Type, but are implemented as part of the ‘Standard Flexfield Workflow’. They may, however, be an important element for the purpose of customizing the Generator. The Lookup Types may be likened from the programmer’s perspective to Data Types. Data Types are used to limit the values a

27

variable may be assigned. For instance, assume a custom function is designed to return an order type name of ‘Domestic’, ‘International’ or ‘Default’. To create a condition that would only return one of these three values would fit the need for a Lookup Type. The details of creating this Lookup Type exceed the scope of this paper; instead, refer to the white paper entitled ‘Appendix E Addendum’ for details in accomplishing this task. The finished product of such a custom Lookup Type would look like the following screenshot:

Note: For those intimately familiar with Independent Value Sets as they are used with Flexfields, it may be easier to equate Lookup Types with them. Just as Independent Value Sets are used to constrain the values for a segment, so too, Lookup Types are used to constrain acceptable return values for a Function. As just alluded to, Lookup Types are commonly associated with results returned from a Function. If you look at the properties of the Function as displayed earlier, you will notice a field labeled ‘Result Type’ which contains a value called ‘Flexfield Result’. This is a common Lookup Type associated with Functions. It essentially states that the

28

Function is expecting a value returned which is defined within the Lookup Type named ‘Flexfield Result’. In the world of programming a Data Type would be created, and then a Variable would be declared, which would use this Data Type. Likewise, in Workflow a Lookup Type is defined and then an Attribute uses this Lookup Type. that the Workflow Attribute was equated to a programming Variable, and the Workflow Lookup Type was equated to a programming Data Type. So, just as a Data Type is used in a declaration of a Variable, a Lookup Type may be used in the declaration of an Attribute. As an example of an Attribute using a Lookup Type, take the example of creating an Attribute which would only use values assigned to the ‘Order Type’ Lookup Type previously examined. You can create an Attribute called ‘Order Type Name’ and assign it a ‘Type’ of ‘Lookup Type’ with a value of ‘Order Type’. See the following example:

Concluding Observations about Workflow Objects In order to quickly review how all of these objects are hierarchically defined, consider the following broad observations: Item Types are at the top of the hierarchy and envelope all Processes for a workflow definition regarding a specific task (in this case, the task of building a COGS ). Each Process may use multiple Functions to achieve its desired task. Many Functions may be dragged from the Navigator to the Processes flow definition. Each Function has the ability to accept input by incorporating Attributes, or use these Attributes within the body of the PL/SQL function or procedure. Functions may restrict the applicable return values by associating it to a Lookup Type. Finally, Attributes may also constrain the data it may incorporate through the use of Lookup Types.

29

The ‘Generate Default ’ Process Before continuing, it would be prudent to take an example workflow process and explore it. The ‘Generate Cost of Goods Sold ’ item type is delivered with 2 predefined workflow processes. The ‘Generate Default Process’ will be explored. It is the simplest scenario for deriving the COGS , in that it derives all segments from the COGS assigned to the item being transacted. This is the same default source used with previous versions of the application implementing FlexBuilder. Double-click on the ‘Generate Default Process’ and you will see the following design screen.

It can easily be seen that this process is made up of 6 function calls. If you were to compare this more closely with the functions in the navigator you would observe that only one of these functions (‘Get CCID for a line’) is sourced from the ‘OM: Generate Cost of Goods Sold ’ Item Type. The remaining 5 are sourced from the ‘Standard Flexfield Workflow’ Item Type. All Workflow processes for Generation begin with the ‘Start generating Code Combination’, and a successful completion will conclude with ‘End generating Code Combination’. Both of these functions do very little other than setting the provisions for debugging output. After the ‘Start generating Code Combination’ function, the flow is directed to the heart of the process, ‘Get CCID for a line’. When looking at the PL/SQL code for this function (OE_FLEX_COGS_PUB.GET_COST_SALE_ITEM_DERIVED) you see that the ‘Generated CCID’ Attribute is loaded with the COGS associated with the item as defined in the shipping inventory organization. NOTE: The code which calls the COGS Generator process takes into consideration whether 30

this is an RMA, regular sales order, or an RMA which references a sales order, and feeds in the applicable line_id. The function ‘Get CCID for a line’ has a return Lookup Type associated with it called ‘Flexfield Result’. If the function returns a ‘Failure’, then an abnormal exception was encountered and the flow is directed to the error handling function, ‘Abort generating Code Combination’. In this case the error message is output to the log file and the workflow process ends. A data integrity issue may cause this condition to be raised. With a successful completion of the function, however, the Attribute ‘Generated CCID’ will be populated and the flow is directed to the function ‘Copy Values from Code Combination’. This function will simply transfer all Segments from the just assigned to the ‘Generated CCID’ Attribute to the Segments for the COGS . Note: These segments are actually hidden Attributes stored in the database, called FND_FLEX_SEGMENT1, FND_FLEX_SEGMENT2, etc. In order for the public function ‘Copy Values from Code Combination’ in the ‘Standard Flexfield Workflow’ item type to retrieve the Segments from the assigned to the ‘Generated CCID’ attribute in the ‘Generate Cost of Goods Sold ’, it must be fed in as input to the function. If you open the properties sheet for the icon as it exists within the process form, and then choose the Attribute Values tab, you can see how this is achieved. The input value called ‘Code Combination ID’ defaults to a Constant with no value as seen in the Before condition. This means a NULL value is getting fed to the function. This, of course, is not your intention. What is desired is to offer the ‘Generated

Before

After

CCID’ as input. To do this, change the ‘Constant’ value to ‘Item Attribute’ and choose a Value of ‘Generated CCID’. See the After condition above: Notice that the icon symbolizes that all source segments (top 3 yellow boxes) are being copied (shown by the arrows) to the destination segments (bottom 3 yellow boxes). Just before ending the execution of the process, a validation must be done on the generated segment combination. This operation is performed in the ‘Validate

31

Code Combination’ function. Essentially, this takes the segments defaulted to the FND_FLEX_SEGMENT# attributes along with the specified chart of s and determines if a row exists in the GL_CODE_COMBINATIONS table for that combination. In this case since all values were defaulted from the same location, it would be highly unlikely that this would fail validation. Finally, the program would exit normally via the ‘End Generating Code Combination’ Function.

The Seeded COGS Generator Functions Now that a simple process has been investigated, hopefully a stronger understanding has been founded. With that as an assumption, it allows for further probing into the other seeded functions within the ‘OM: Generate Cost of Goods Sold ’ Item Type. These functions are ed, if left unmodified, and can be used to design more flexible workflow processes for the COGS generation. These functions can be divided into 4 categories: those for retrieval of a CCID ( ID), those for setting Attribute values, one for Upgrading from Flexbuilder, and a Branching condition. Function Get CCID for a line Get CCID from Salesrep's revenue segment Get CCID from the Model for an option Get CCID from the Order Type ID Get CCID from the item and selling operating unit Get Salesrep's id Get Transaction Type for a Commitment ID Upgrade from Flexbuilder Check if the line is an option line

Category Generate CCID Generate CCID Generate CCID Generate CCID Generate CCID Set Attribute Value Set Attribute Value Upgrade From Flexbuilder Branching Condition

Get CCID for a line This function will return the CCID assigned to an item as it exists in the shipping warehouse. The intent of this function is to retrieve the CCID from the Cost of Goods Sold assigned to the item on the sales order line within the shipping inventory organization. Given the Line_id of the sales order line, this information is easily derived. To see the value for this from the application requires querying the item up in the Define Items form within the shipping inventory organization, then navigating to the Costing region. The CCID return equates to the Cost of Goods Sold displayed. Get CCID from Salesrep’s revenue segment This function will return the CCID assigned to Salesperson’s Revenue . The intent of this function is to retrieve the value for the ‘Salesrep ID’ attribute, and use it to determine the Revenue . Because the salesrep attribute is one that is populated during the initialization process, it does not need to be derived again. This differs from 32

Release 11, where the ‘Get Salesrep Id’ function needed to be called within the workflow process in order to set this attribute.

‘Get CCID from the Model for an option’ and ‘Check if the line is an option line’ The intent of this function is to return the CCID for the COGS of the top-level item rather than the component beneath the model that is being interfaced. The best way to demonstrate is with an example. Suppose you have a PTO model called PTO-1 and the bill of material has items beneath it, which include OPT-1. If OPT-1 is interfaced to inventory using this function, then the CCID returned would be the COGS for the PTO-1, rather than the OPT-1. Since this function only returns a value for the CCID if the line is beneath a model, then it is a pre-requisite to first test if the option flag is set. Otherwise, the function will return an error for standard order lines. There is a seeded function used to branch on this condition called ‘Check if the line is an option line’. To clarify the workflow process might look similar to the following:

33

Get CCID from the Order Type ID This function will return the CCID for the Cost of Goods Sold assigned to the order type (A.K.A. OM Transaction Type) of the order. This is a simple function, but should be used with care in the unusual event that the chart of s assigned to the Order Type of the order differs from the chart of s used by the shipping inventory organization. In order to see the Cost of Good Sold from within the application, navigate to the Define Transaction Types form (OE: Setup ⇒ Orders ⇒ Transaction Types ⇒ Define). Go to the Finance Region and the field will be displayed. Get CCID From the Item and Selling Operating Unit This function by its title is a bit of an oxy-moron. Items are never defined in an Operating Unit and therefore getting the CCID for an Item in the Selling Operating Unit can be unpredictable. Items are only defined in an Inventory Organization. The code at one time would try to retrieve the CCID from the item definition where the Organization_id for the item is equal to the ORG_ID for the operating unit of the line. Upon this realization, development has resolved this issue, and the function performs identically to the ‘Get CCID for a line’ function as of version 115.8 of OEXWCGSB.pls. It is a safer approach to avoid using this function. Get Salesrep’s ID As mentioned previously, in Release 11 this function was used in conjunction with the ‘Get CCID from a Salesrep’s revenue segment’. In fact, one was useless without the other. An argument was made that the two could be combined into one function. This is 34

partially what is done in Release 11i. Now, the salesrep Id is populated when the process is initialized. However, the salesrep is set based on the salesrep tied to the line of the order. More often then not, the salesrep is tied to the header of the order and not the line of the order. Therefore, in most cases there will be no value in the ‘Salesrep Id’ attribute after initialization. In Release 11i, it is probably safer to call both functions out as we did in Release 11. Implementation of such a process would look like the following process.

When using this function, the Salesrep’s ID is derived by choosing the salesperson having the min salesrep_id for the salesreps that have the max quota sales credits. That is a confusing mouthful, so take it one piece at a time. First, take the maximum sales credit percentage found for sales credits assigned to this line. This may return more than 1 row if the sales credits were split evenly among multiple salespersons. In this case an assumption is made that the salesperson with the lowest Salesrep ID has seniority, since they were entered into the database prior to the others. This narrows the list down to only one salesperson. If there are no line level sales credits then the same task is performed for sales credits assigned to the header of the order. For those who would rather see the code for this derivation, it looks like: SELECT INTO FROM WHERE AND

SALESREP_ID l_salesrep_id OE_SALES_CREDITS LINE_ID = L_ORDER_LINE_ID SALESREP_ID = (SELECT MIN(SALESREP_ID)

35

FROM OE_SALES_CREDITS SC , OE_SALES_CREDIT_TYPES SCT WHERE SC.LINE_ID = L_ORDER_LINE_ID AND SC.SALES_CREDIT_TYPE_ID = SCT.SALES_CREDIT_TYPE_ID AND SCT.QUOTA_FLAG = 'Y' AND SC.PERCENT = (SELECT MAX(PERCENT) FROM OE_SALES_CREDITS WHERE LINE_ID = L_ORDER_LINE_ID) ) AND ROWNUM = 1;

Get the Transaction Type for a Commitment ID This infrequently used attribute assignment derives the receivables transaction type for a Commitment ID. It loads a value into the ‘Transaction Type’ attribute based on the Commitment ID. Although this is a provided function and attribute, no other seeded function uses this Attribute as a source of information. This would be valuable for those wanting to source the COGS from the Revenue or Receivables assigned to a receivables transaction type. This would still require a custom function to retrieve the actual CCID from the transaction type, but half the work is done using this function. Upgrade from Flexbuilder This function is unique from all others. It is provides the bridge between the old Flexbuilder generator and the new Workflow processes. The intent here is to allow those customers upgrading from release 10 of the applications to use their Flexbuilder rules for building the COGS . Upgrade steps are required to implement this feature fully. Rather than elaborating on those here, refer to the Oracle Applications Upgrade Manual for Release 11i (A87325). Specifically refer to chapter 3, Oracle Flexbuilder/ Generator Tasks.

The Seeded Standard Flexfield Workflow Functions A brief word about the Standard Flexfield Workflow Functions is in order to obtain a complete understanding of the functionality and power of the Generator. The ‘Copy Values from Code Combination’ function has previously been reviewed, therefore it will be omitted here. The functions reviewed here will be: • •

•

Copy Segment Value from Code Combination Copy Segment Value from other Structure Code Combination Assign Value to Segment

Copy Segment Value from Code Combination This function is used to copy only one segment from the source to the destination . For instance, if the scenario existed where Segment2 of the COGS required that the source to come from Segment2 of the Order Type COGS , this function would be used. Please note that the icon for this Function is very descriptive. It shows the top three yellow boxes representing the source segments, and the bottom three yellow boxes representing the destination segments. The important element is the single arrow,

36

pointing from one segment to the other. As you recall this differs in the icon for ‘Copy Values from Code Combination’ in that there is only one arrow as opposed to three arrows, describing the fact that only one segment is copied instead of all segments. When using this function, there are attribute values that need to be specified. In this case, the source is input by setting the attribute value of Code Combination ID to an Item Attribute with a value of ‘Generated CCID’. In addition, the case sensitive segment name for the segment is specified. To assign the single segment called ‘Department’ from the source to the destination (COGS) , the attribute values would be modified to look like:

Please make a special note that the ‘Segment’ value for the segment name is case sensitive. If the value assigned were ‘DEPARTMENT’ or ‘department’ the assignment of this segment would fail. Copy Segment Value from other Structure Code Combination This function makes provisions for copying a segment value from a specified CCID ( ID). This would be used when the derivation of the source cannot be produced from one of the existing functions. The Attribute Values (function input) for this function look like:

37

Each of the named attribute values would be specified using this scenario. The ‘Structure Number’ is the Chart of s ID. ‘The Code Combination ID’ is the specific CCID for this chart of s. With these 2 values specified a single row in the GL_CODE_COMBINATIONS table is identified, which then allows for the individual segment values to be retrieved. This is where the ‘Segment Identifier’ and ‘Segment’ come into play. With the ‘Segment Identifier’ assigned a value of ‘Name’, then the ‘Segment’ attribute value would specify the case sensitive name of the segment within the chart of structure. For instance, the name of Segment2 in the ‘Vision Operations (USA)’ ing Flexfield is ‘Department’. Therefore in order to extract the value of the segment2 for this structure for the Code Combination ID = 1102, the attributes would be set to:

Assign Value to Segment

38

In some cases, there may be a desire to assign a specific value to a segment. For this situation, the ‘Assign Value to Segment’ function would be used. Suppose segment1, which has a segment name of ‘Company’, is to always have a value of ‘01’. This can be achieved using this function by specifying the attribute values for the instance of this function as:

The icon is descriptive of the function being performed. It shows an arrow feeding into a single block or segment.

A Simple Modification to an Existing Process Definition Now that all the elements have been reviewed, you can make a small modification to the ‘OM: Generate Default ’ process. The modification will source the ‘Department’ segment from the Order Type COGS . All other segment values will be derived from the ‘Get CCID for a line’. The first step is to copy the existing process to a new process. Please do not simply modify the existing Default process. When doing the copy, an initial error is raised stating

363: Could not set activity properties for OECOGS/DEFAULT__GENERATION. 310: Duplicate internal name.

After accepting this error message, a property sheet will display. Change the values to unique values as shown and apply:

39

Now there is a copied process, ‘Generate New Default ’, which may be modified independently of the ‘Generate Default ’. Double-click on the new process from within the Navigator and continue by deleting the link between the ‘Start generating Code Combination’ and the ‘Get CCID for a line’ function. Insert between them the function ‘Get CCID from the Order Type’ by dragging it from the Navigator to the process screen. Using the right mouse button, draw a line connecting the ‘Start generating Code Combination’ to the ‘Get CCID from the Order Type’ function. Next drag the ‘Copy Segment Value from Code Combination’ function onto the process sheet and continue by linking it from the ‘Get CCID from the Order Type’. Choose the ‘Success’ value when displayed. The ‘Failure’ result is handled by drawing a link between the ‘Get CCID for the Order Type’ to the ‘Abort generating Code Combination’. At this point in time, set the attribute values for the ‘Copy Segment Value from Code Combination’ by right-clicking on the function within the process sheet and choosing Properties. The values to set include the ‘Code Combination ID’ and the ‘Segment’ attribute values as shown:

40

The next step is to link the ‘Copy Segment Value from Code Combination’ to the ‘Get CCID for a line’ by dragging a line between the 2 using the right mouse button. One final modification is needed, which will demonstrate the use of a common attribute value called ‘Replace existing value’. Go to the properties sheet for the instance of ‘Copy Values from Code Combination’ which comes after the ‘Get CCID for a line’ function, and choose the Attribute Values tab. Change the ‘Replace existing value’ to ‘False’. This prevents the ‘Department’ segment from getting overwritten after it has been assigned a value from the Order Type COGS .

41

Save this process definition off to the database. The final product would look like:

Pointing the Chart of s to the New Process Saving new Workflow Process Definitions to the database is all well and good, but it is of no benefit unless the Chart of s structure can invoke it. The relationship between the Chart of s structure and the Workflow Process for the ‘OM: Generate Cost of Good Sold’ item type is made in the ‘ Generator Processes’ form (OM: Setup ⇒ Financials ⇒ Flexfields ⇒ Key ⇒ s). Using the flashlight icon choose the applicable Chart of s. Then assign the new process to the ‘Generate Cost of Goods Sold ’ Item Type as shown:

42

Troubleshooting the Workflow Process Much of the following section is intended for internal Oracle analysts. However, although the tools mentioned may not be available to everyone, the troubleshooting methods remain the same. Debug parameters in the stored procedures Embedded within the stored procedures invoked by the Workflow process are debug variables. These variables come seeded in a disabled state, but when they are enabled they log valuable information to the standard output. In the case of the Inventory Interface, these debug messages would be logged to the Request Log when submitted as a concurrent request. This request log becomes a key element in troubleshooting problems with the COGS generation. The package name, filename, and variable for each of the packages are displayed in the following table. Package FND_FLEX_WORKFLOW FND_FLEX_WORKFLOW_APIS

Filename AFFFWKFB.pls AFFFWKAB.pls

Variable Debug_fnd_flex_workflow Debug_fnd_flex_workflow_apis

In addition to these hard coded parameters, there is a profile called ‘OM: Debug Level’, which enables debug messages to be output in the concurrent request log. Set ‘OM: Debug Level’ = 5 to output these applicable debug messages. By using the script in Appendix A, you can find how the profile is currently set. 43

An example of the type of output logged will be demonstrated in the following section when testing the using the cogs_11i.sql script. Testing the Workflow Process Using the cogs_11i script The cogs_11i.sql script was designed to allow cursory testing of the COGS Generator without requiring the to process a sales order line through Inventory Interface. Use of this script should not be construed as a full and complete unit test. The only requirement is that there be an entered order with a line to reference. The is prompted for the required input. The body of the script can be found in the Appendix B of this paper. Updates to the script can be requested by sending an email to [email protected]. There are a few notes regarding this script. • For each test run it is recommended that you begin a new session in SQL*Plus. The need for this stems from the fact that values are kept in memory for the Generated CCID as well as other elements and will get used in subsequent runs. This may be a side affect of a bug where attribute values were not getting reset when run in SYNCH mode. • The output is split up into sections and is not a strict chronological output of the execution. An example run of the cogs_11i.sql for your modified process follows: SQL> @cogs_11i PL/SQL procedure successfully completed. Enter Order Number : 43606 ORG_ID HEADER_ID ---------- ---------204 43343 Enter Org_id : 204 PL/SQL procedure successfully completed. TRANSACTION_TYPE_ID NAME ------------------- -----------------------------1000 Standard Enter Transaction_Type_ID : 1000 LINE_ID LINE_NUMBER INVENTORY_ITEM_ID ORDERED_QUANTITY ---------- ----------- ----------------- ---------------48085 2 149 1 48074 1 3481 1 48073 1 3479 1 48075 1 3487 1 48076 1 3485 1 48077 1 3379 1 48078 1 3381 1 48079 1 3391 2

44

48080 48081 48082

1 1 1

3385 3483 3459

1 1 1

11 rows selected Enter Line_ID : 48082 ********************************************** X_ORDER_LINE_ID = 48082 **************************************************** * OUTPUT FROM WORKFLOW DEBUG SETTINGS * **************************************************** START FND_FLEX_WORKFLOW.INITIALIZE APPLICATION_SHORT_NAME = SQLGL CODE = GL# NUM = 101 ITEMTYPE = OECOGS ITEMKEY GENERATED IS #SYNCH PROCESS SELECTED IS NEW_DEFAULT__GENERATION APPLICATION ID IS 101 NUMBER OF SEGMENTS IS 5 START FND_FLEX_WORKFLOW.GENERATE ITEMTYPE = OECOGS ITEMKEY = #SYNCH INSERT_IF_NEW = FALSE START FND_FLEX_WORKFLOW_APIS.START_GENERATION START FND_FLEX_WORKFLOW_APIS.COPY_SEGMENT_FROM_COMBINATION APPLICATION SHORT NAME = SQLGL APPLICATION ID = 101 CODE = GL# NUM = 101 SEGMENT_IDENTIFIER = NAME SEGMENT = Department CCID = 12951 REPLACE_CURRENT_VALUE = FALSE START FND_FLEX_WORKFLOW_APIS.GET_SEGMENT_NUMBER APPLICATION_ID = 101 CODE = GL# NUM = 101 SEGMENT = Department SEGMENT NUMBER IS 2 VALUE ASSIGNED : 510 START FND_FLEX_WORKFLOW_APIS.COPY_FROM_COMBINATION APPLICATION SHORT NAME = SQLGL APPLICATION ID = 101 CODE = GL# NUM = 101 CCID = 12952 REPLACE_CURRENT_VALUE = FALSE VALUE ASSIGNED TO SEGMENT 1 IS 01 VALUE ASSIGNED TO SEGMENT 3 IS 5110 VALUE ASSIGNED TO SEGMENT 4 IS 0000 VALUE ASSIGNED TO SEGMENT 5 IS 000 START FND_FLEX_WORKFLOW_APIS.VALIDATE_COMBINATION APPLICATION SHORT NAME = SQLGL APPLICATION ID = 101 CODE = GL# NUM = 101 NSEGMENTS = 5 VALIDATION_TYPE = GENERATE_CCID DYNAMIC_INSERTS_ALLOWED = TRUE INSERT IF NEW COMBINATION = FALSE FND_FLEX_SEGMENT1 IS 01 FND_FLEX_SEGMENT2 IS 510 FND_FLEX_SEGMENT3 IS 5110 FND_FLEX_SEGMENT4 IS 0000 FND_FLEX_SEGMENT5 IS 000 CONCATENATED SEGMENTS TO BE VALIDATED IS 01-510-5110-0000-000 VALIDATION SUCCEEDED CCID IS 12951 START FND_FLEX_WORKFLOW_APIS.END_GENERATION

45

PROCESS STATUS IS COMPLETE PROCESS RESULT IS SUCCESS CCID IS 12951 CONCATENATED SEGMENTS IS 01-510-5110-0000-000 CONCATENATED IDS IS 01-510-5110-0000-000 CONCATENATED DESCRIPTIONS IS Operations-Vision Operations In-Cost of Sales-No Sub -No Product NEW COMBINATION IS FALSE VALIDATION_STATUS IS VALID **************************************************** * OUTPUT FROM OM: DEBUG LEVEL PROFILE SETTING * **************************************************** Entering OE_Flex_Cogs_Pub.Start_process : 48082 Chart Of s Id : 101 Item Key : #SYNCH Initilizing Workflow Item Attributes Calling FND_ELEX_WORKFLOW.GENERATE from START_PROCESS Entering OE_Flex_Cogs_Pub.GET_ORDER_TYPE_DERIVED Item Type : OECOGS Item Key : #SYNCH Activity Id : 166172 funcmode : RUN Input Paramerers : Order Type ID :1000 Output : Generated CCID :12951 Entering OE_Flex_Cogs_Pub.GET_COST_SALE_ITEM_DERIVED Item Type : OECOGS Item Key : #SYNCH Activity Id : 166170 funcmode : RUN Input Paramerers : Line id :48082 Organization id :207 Output : Generated CCID :12952 Exiting from OE_Flex_COGS_Pub.Get_Cost_Sale_Item_Derived Return CCID : 12951 Concat Segments : 01-510-5110-0000-000 Concat Id : 01-510-5110-0000-000 Concat Descriptions : Operations-Vision Operations In-Cost of Sales-No Sub -No Product Error Message : **************************************************** * SUMMARY OF RETURNED RESULTS * **************************************************** Output Parameters: Debug Level = 5 X_RETURN_CCID = 12951 X_CONCAT_SEGS = 01-510-5110-0000-000 X_CONCAT_IDS = 01-510-5110-0000-000 X_CONCAT_DESCRS = Operations-Vision Operations In-Cost of Sales-No Sub -No Product X_ERRMSG = **************************************************** PL/SQL procedure successfully completed.

It is not necessarily a simple task in interpreting this output, because of the 2 different types debugging which are enabled. Setting the debug variables in the 2 stored procedures as outlined above causes the debugging to go directly to the screen when they are executed. However, when setting the profile option ‘OM: Debug Level’ to 5, the messages are stored in a temporary table and are output together afterward. This can make the output confusing.

46

If this becomes too much of a challenge, then I recommend you obtain the debugging messages by running the Inventory Interface. In this case, the debugging comes out in its correct order, and use the cogs_11i script to get a good idea of whether the COGS Generator is erroring. As an exercise, you should try to understand how the process flow designed in Workflow Builder manifests itself in this output. Specific highlights are recognizing the calls to initialize the 11 parameters, identifying the calls to get the CCID for the Order type, and the assignment of the one segment. Then, the call to get the CCID for the item and the assignment of these values to the destination COGS . Finally, the resultant CCID and concatenated segments are displayed. Running in Synch versus Debug When executing a process to generate an there is an option to run Debug Mode, which is controlled by the profile option ‘ Generator:Run in Debug Mode’. When debugging is enabled and attributes are assigned values, the transaction is given a unique key and the values are stored in the database. In the spooled output of the cogs_11i run above, the unique key is identified near the beginning as ‘L_ITEMKEY = 6003’. When the debugging is disabled, the L_ITEMKEY will be #SYNCH. In the latter case there will be no history of values assigned to the attributes on the database after the process has completed, unless the process ends in an error. This keeps the size of the workflow tables from growing at an enormous rate.

Conclusion Hopefully this paper has helped clarify the functionality and flexibility of the Generator using Workflow and the Workflow Builder. For those interested in furthering their knowledge of the Release 11 COGS Generator, the ‘Appendix E Addendum’ is recommended. It offers the details and corrections for the Appendix E documentation in the Oracle Order Management/Shipping Release 11 ’s Guide, entitled Using the Generator. Topics for further discussion would include: • Default Error Process • Protection Level • Database Workflow Objects • Detailed analysis of workflow scripts under $APPL_TOP • Mapping Attribute Values, Item Keys, etc to the COGS Generator Item Type Any constructive criticism is welcome and may be sent to [email protected]. Note: This is unofficial Oracle Documentation. Use this information at your own risk.

47

Acknowledgments A special thanks to Chris Goode for explaining the use and functionality of the FSG reports.

48

Appendix A: Profiling script ------------------------------------------------------------------- This is a script to profile the source of information which -- the workflow uses to build a Cost of Goods Sold -----------------------------------------------------------------set linesize 155; set pagesize 200; set off; col Profile format a20; col Level format a14; col Value format a10; col App format a3; col Responsibility format a30; col format a8; col UPDATED_BY format a8; COLUMN S_OF_B_ID FORMAT 9999999; Column warehouse format a30; column set_of_books_id format a16; prompt prompt prompt select

=========================================== VALUES FOR THE SET OF BOOKS PROFILE OPTION: =========================================== fpo.profile_option_name Profile, fpov.profile_option_value Value, decode(fpov.level_id,10001, 'SITE',10002, 'APPLICATION',10003, 'RESPONSIBILITY', 10004,'')"LEVEL", fa.application_short_name App, fr.responsibility_name Responsibility, fu._name "" from fnd_profile_option_values fpov, fnd_profile_options fpo, fnd_application fa, fnd_responsibility_vl fr, fnd_ fu, fnd_s fl where fpo.profile_option_id=fpov.profile_option_id and fa.application_id(+)=fpov.level_value and fr.application_id(+)=fpov.level_value_application_id and fr.responsibility_id(+)=fpov.level_value and fu._id(+)=fpov.level_value and fl._id(+) = fpov.LAST_UPDATE_ and fpo.profile_option_name = 'ONT_DEBUG_LEVEL' order by 1,3 / prompt =============================================== prompt SET OF BOOKS INFO FOR INVENTORY ORGANIZATIONS: prompt =============================================== select ood. organization_id, organization_name warehouse, 49

org_information1 set_of_books_id From org_organization_definitions ood, Hr_organization_information hoi Where ood.organization_id = hoi.organization_id and ood.inventory_enabled_flag = 'Y' and hoi.org_information_context = 'ing Information' order by ood.organization_id / column set_of_books_id format 9999999; prompt ======================================== prompt CHART OF INFO FOR SET OF BOOKS: prompt ======================================== Select NAME SET_OF_BOOKS, SET_OF_BOOKS_ID S_OF_B_ID, CHART_OF_S_ID, CHART_OF_S_NAME From GL_SETS_OF_BOOKS_V WHERE SET_OF_BOOKS_ID = &SET_OF_BOOKS_ID / prompt ===================================================== prompt FLEXFIELD SEGMENT INFO FOR CHART OF ACCTS STRUCTURE: prompt ===================================================== select segment_num, segment_name from fnd_id_flex_segments_vl where enabled_flag='Y' and id_flex_code = 'GL#' and id_flex_num = &&Chart_of_s_id order by segment_num / prompt ================================================= prompt WORKFLOW PROCESS ASSIGNED TO CHART OF S: prompt ================================================= select id_flex_num, wf_process_name from fnd_flex_workflow_processes where id_flex_code = 'GL#' and wf_item_type = 'OECOGS' and id_flex_num = &&Chart_of_s_id /

50

Appendix B: cogs_11i.sql Script: --- testcogs_11i.sql version 1.00 --- Created by Rich Mercer version 1.00 --- This is a script used to test the COGS Generator. -- Please keep in mind that you may have to exit SQL*Plus after each run -- I believe this has to do in running in synchronous(non-debug) mode. --- NOTE: It is safest to exit SQL*Plus after each test run since values from -The previous run seem to be kept in memory. --- This test assumes that previous validation for the transactions have been made where -- applicable. For instance: -Item is Shippable -Item is Returnable for RMA's -Item is Stock Enabled -Item is Transactable -set pagesize 50 set recsep off set serveroutput on set off column act_complete format a18 column act_start format a18 column program format a35 variable p_Order_number variable P_ORDER_TYPE_ID variable P_Line_ID variable P_LINE_DETAIL_ID variable P_PICKING_LINE_DTL_ID variable p_ORG_ID --variable x_fnd_debug

varchar2; number; number; number; number; number; varchar2;

-- fnd_flex_workflow.debug_on; execute oe_debug_pub.initialize; accept p_Order_number prompt "Enter Order Number : " select ORG_ID, header_id from oe_order_headers_all where order_number = &p_Order_Number; accept p_ORG_ID prompt "Enter Org_id : " execute fnd_client_info.set_org_context('&p_org_id'); select ot.transaction_type_id , name from oe_order_headers h, oe_transaction_types_v ot where h.order_type_id = ot.transaction_type_id and h.order_number = &p_order_number; accept p_order_type_id prompt "Enter Transaction_Type_ID : " select line_id, line_number, inventory_item_id, ordered_quantity from oe_order_lines l, oe_order_headers h where l.header_id = h.header_id and h.order_type_id = &p_order_type_id and h.order_number = &p_order_number; accept p_line_ID prompt "Enter Line_ID : " Declare CCIDGOOD X_RETURN_CCID

varchar2(1); number;

51

X_CONCAT_SEGS X_CONCAT_IDS X_CONCAT_DESCRS X_MSG_COUNT X_ERRMSG

varchar2(250); varchar2(250); varchar2(250); number; varchar2(1000);

X_ORDER_LINE_ID X_FLEX_NUMBER X_DEBUG_FILE x_fnd_debug x_status

number; number; varchar2(100); varchar2(32000); boolean;

begin dbms_output.enable(100000); X_ORDER_LINE_ID := &p_Line_id; DBMS_OUTPUT.PUT_LINE('**********************************************'); DBMS_OUTPUT.PUT_LINE('X_ORDER_LINE_ID = ' || to_char(X_ORDER_LINE_ID)); --X_DEBUG_FILE := OE_DEBUG_PUB.Set_Debug_Mode('FILE'); X_DEBUG_FILE := OE_DEBUG_PUB.Set_Debug_Mode('TABLE'); OE_DEBUG_PUB.SetDebugLevel(5); --DBMS_OUTPUT.PUT_LINE('Debug File

= ' || X_DEBUG_FILE);

DBMS_OUTPUT.PUT_LINE('****************************************************'); DBMS_OUTPUT.PUT_LINE('* OUTPUT FROM WORKFLOW DEBUG SETTINGS *'); DBMS_OUTPUT.PUT_LINE('****************************************************'); CCIDGOOD := OE_Flex_Cogs_Pub.START_PROCESS( 1.0, X_ORDER_LINE_ID, X_RETURN_CCID, X_CONCAT_SEGS, X_CONCAT_IDS, X_CONCAT_DESCRS, X_MSG_COUNT, X_ERRMSG); DBMS_OUTPUT.PUT_LINE('****************************************************'); DBMS_OUTPUT.PUT_LINE('* OUTPUT FROM OM: DEBUG LEVEL PROFILE SETTING *'); DBMS_OUTPUT.PUT_LINE('****************************************************'); for i in 1..OE_DEBUG_PUB.g_debug_count loop dbms_output.put_line(OE_DEBUG_PUB.G_debug_tbl(i)); end loop; OE_DEBUG_PUB.DEBUG_OFF; DBMS_OUTPUT.PUT_LINE('****************************************************'); DBMS_OUTPUT.PUT_LINE('* SUMMARY OF RETURNED RESULTS *'); DBMS_OUTPUT.PUT_LINE('****************************************************'); DBMS_OUTPUT.PUT_LINE('Output Parameters: '); --DBMS_OUTPUT.PUT_LINE('Debug = ' || OE_DEBUG_PUB.G_DEBUG); DBMS_OUTPUT.PUT_LINE('Debug Level = ' || to_char(OE_DEBUG_PUB.G_DEBUG_LEVEL)); --DBMS_OUTPUT.PUT_LINE('Debug File = ' || OE_DEBUG_PUB.G_DIR||'/'|| -OE_DEBUG_PUB.G_FILE); DBMS_OUTPUT.PUT_LINE('X_RETURN_CCID = ' || to_char(X_RETURN_CCID)); DBMS_OUTPUT.PUT_LINE('X_CONCAT_SEGS = ' || X_CONCAT_SEGS); DBMS_OUTPUT.PUT_LINE('X_CONCAT_IDS = ' || X_CONCAT_IDS); DBMS_OUTPUT.PUT_LINE('X_CONCAT_DESCRS = ' || X_CONCAT_DESCRS); DBMS_OUTPUT.PUT_LINE('X_ERRMSG = ' || X_ERRMSG); DBMS_OUTPUT.PUT_LINE('****************************************************'); end; /

52