This post is one possible solution for this question in this thread in the forums. The requirement was to auto-create deliveries across the sales orders and launch pick release for the same. When pick releasing is completed, it should create one picking batch (move order) for all these deliveries.

Here are the steps to follow to test the script.

1. Create an item with and assign it to an organization (M1)
2. Create inventory for the same by receiving quantity using Miscellaneous Receipt in inventory.
3. Add item to the price list.
4. Create four different sales orders with different ship to addresses for this item. 
5. Book the orders. Scheduling happens.
6. Now if you navigate to the shipping transactions screen you will see that delivery details are created with the status of Ready To Release for all these orders.
7. Take all the delivery details for each of these orders.
8. Pass them as parameter to the first script that is available here.
9. Once this script is completed, you should see the deliveries are created and assigned to these delivery details. Since all these orders have different ship to addresses, obviously different delivery ids are created.
10. Now use the second script to launch the pick release.Download the script here.
11. This will kick off a concurrent request for Pick Selection List Generation program.
12. Once request is finished successfully, you will see a same move order is created for all the lines.

Of course you can do the same using the screen.

After the step 6 from above use the steps below:

1. If your order numbers in range pass then in From order to To order in the shipping transaction screen and click Find button.
2. You should see four lines (since each order has one line only)
3. Keep the cursor on the first line
4. Hold the shift key
5. Mouse click on the last line
6. This will select all the lines (highlighted in blue)
7. From actions use Autocreate Deliveries and press Go
8. This will create deliveries for all the four lines (different delivery numbers)
9. Select all the lines again as described above
10. From actions Launch Pick Release and Go
11. This will kick off the Pick Selection List Generation program

Remember to review your Delivery Grouping Rules, Pick Releasing Rules, Pick Slip Grouping Rules (assigned to the pick releasing rules)  for the organization where you are going to auto-create deliveries and launch pick release. They play a huge role in this process.

Even if you do not run the first script, second script will call autocreate deliveries (done in the fist script) and then launch pick release automatically. Give it a try.

Please note that for the second script (to launch pick releasing) I had to use group API (wsh_interface_grp) as I could not find any public API for the same. If you find one and tested it successfully, please share here. But the for the first one I used public API wsh_delivery_details_pub.autocreate_deliveries.

This is second article in the series of articles on revenue-COGS matching. As discussed in the first article, with the introduction of deferred COGS accounting, COGS accounting does not take place unless revenue is recognized so that the revenue and COGS recognition happen in the same period. In this article let us discuss customer acceptance process in relation to the revenue COGS matching. In fact  introduction of deferred COGS functionality gave room for this feature.

What is Customer Acceptance and how it is related to Revenue – COGS matching?

Customer acceptance is a process where you cannot recognize revenue and COGS until customer has accepted the delivery. As we have discussed in the earlier article, starting R12, when goods are shipped always Deferred COGS account is debited (instead of actual COGS) irrespective of customer acceptance. As we could delay this recognition, now we can add this extra step of customer acceptance. The following is the table of revenue contingencies that are used in customer acceptance process. Of course we can create custom contingecies, but the removal events are always seeded. So these contigencies are more identified with the removal events rather than contingency name itself.

These contingencies are of three types: 

1. Prevent creation of invoice if the customer does not accept the goods. But as soon the customer accepts the goods, order is interfaced to AR and invoice is created and revenue is immediately recognized. This is called pre-billing acceptance. The line status is placed in Pre-Billing Acceptance status and removal event is customer acceptance and invoice creation.
2. Create invoice but do not recognize revenue until customer accepts the goods. This is called post billing acceptance. Contingencies Explicit acceptance and Installation required, fall into this category. The line status is placed in Post-Billing Acceptance status. Once the customer accepts the goods, revenue is recognized immediately (invoice is already created) and line is closed.
3. The last one is called proof of delivery. The difference between this one and other two is that, this neither can be defaulted into the order line nor can be picked up manually in the LOV when the order line is created. If the order line attributes (like bill to customer or site, AR transaction type associated with order type or line type so on) match the assignment rules set up in for the revenue contingency, delviery contingency is applied when order line is invoiced. Unfortunately at this point of time there is no user interface to record the proof of delivery. This, I am sure on the way in the coming releases.  But there is a group API that can be used to remove this contigency and the sample code for the same is here.

Setup and Behavior

These contingencies can be defaulted into order line based on the setup. As shown in the associated table, value for the OM system parameter, Fulfillment Acceptace Required can be set as Yes. This allows acceptance name to be chosen from the LOV in the order lines when an order line is being created. These are hidden fields in the Others tab and folders can be used to open them up. But if contingency assignment rules are setup, say with bill to customer name parameter and value as Business World, and if the order is entered for the same customer as bill to, acceptance contingency automatically gets defaulted once the order line is saved ( this is delayed request in the order line creation and not part of the defaulting rules). The following the list of parameters that can be used to setup the assignment rules.

 In Revenue Management Super User, when these contingencies are assigned to a rule with any of the parameters as shown in the table, order creation process matches the values in the order with the setup rules. If the matching is found, then the contingency is defaulted into the order line. Even if there is no setup done, contingency can be manually assigned to the order line.

 Finally coming to the transactions, if acceptance exists, when the order line shipped line status is placed in one of the statuses mentioned above based on the type of contingency.

Acceptance can be performed by customers themselves or internally by the users. For external users to perform this step, these users should be registered against the customers (as person parties) and should be assigned with a responsibility called Order Information Super User. Once the goods have reached them, they can log in to this customer portal (iStore or iSupport) and accept or reject the goods. Also if they reject them, they can request for RMA. Rest of the process follows as discussed earlier from revenue accounting perspective. For the COGS recognition, the steps mentioned in the part I of this article should be followed.

It is a relief to see this much awaited functionality. We heard our accounting departments complaining about the period mismatches in for our COGS and Revenue accounting for one order. We ship an order on the last day of the month and COGS gets posted to this month, but if the invoice is created with next period’s GL date as the current AR period is closed by the time the order is invoiced. Now all that is changed. Revenue-COGS matching is a standard functionality now. In simple terms, this means, COGS for an order line will be recognized only if the revenue is recognized for that line making sure that the revenue and COGS are posted in the same month.

All of us have spent a lot of time working on COGS accounting workflow to achieve what we want for our clients/companies. In some cases we even customized Revenue accounting generation (avoiding auto accounting logic) by using ra_interface_distributions_all table. We had a handle on accounts generation in this process but not on the actual events of accounting recognition.

We all know this. When we ship the order and run the Interface Trip Stops program, inventory gets reduced and orders get updated to move forward in the workflow to the next activity. Interface Trip Stops program calls the OE_FLEX_COGS_PUB to generate the COGS account as per design. This gets passed on to the mtl_material_transactions table in the distribution_account column. When Cost Manager runs, distribution_account from mtl_material_transactions is picked up to generate accounting as shown.

                                Cr Inventory Material account $100
                                            Dr COGS Account $100

The role of COGS workflow is not changed. It is still the same which generates the account of our choice per workflow design. It still passes the generated account to the mtl_material_transactions table into the distribution_account column. But what changed in R12 is accounting. In order to match Revenue with COGS accounting in terms of timing, COGS account cannot be used at the time shipping. Instead revenue recognition process of the invoice for that order line should generate COGS accounting.

To achieve this, a new account called Deferred COGS account is introduced at the inventory organization parameters level. So when the order shipped instead of the above entries the entry will be

                       Cr Inventory Material account             $100
                                    Dr Deferred COGS Account    $100

When you invoice is this order line, if you have no revenue recognition policies or specialized accounting rules, revenue should be instantly recognized (upon running revenue recognition program).

After revenue is recognized, the following programs need to be run to relieve deferred COGS value and debit actual COGS account.

Record Order Management Transactions: This program collects all the transactions that belong to transaction types Sales order issue and Logical Sales Order Issue which are not costed and the order line is invoiceable. The source table is mtl_material_transactions. This program inserts rows into two tables: cst_cogs_events and cst_revenue_cogs_match_lines. This program is not necessary to run. When not run, Cost Manager will insert rows into these tables. So from implementation considerations, this program is not required to be run.

Collect Revenue Recognition Information: This program collects invoice line information of the order line after the revenue is recognized. The source tables are ra_customer_trx_lines_all and ra_cust_trx_line_gl_dist_all. It will check the percentage of the revenue recognized (we can recognize revenue partially for a specific order line based on accounting rule or contingency rules) and inserts that information into this table: cst_revenue_recognition_lines. Also the table cst_revenue_cogs_control table is updated with the latest run date with high date of this parameter, which is used in the next run of the same program.

Generate COGS Recognition Events: The role of this program is to record a logical material transaction, which is used to create final COGS entry. This program takes information from the above tables and creates one logical inventory transaction in mtl_material_transactions with a new transaction type called COGS Recognition. In the same program these transactions will be costed (not by the cost manager) creating the following accounting entries. The COGS account in this entry is taken from the distribution_account in mtl_material_transactions table (which was generated earlier by COGS workflow).

                                Cr Deferred account                     $100
                                              Dr COGS Account             $100

This is the concept in simple terms. There are different cases (well documented in the Cost Management User Guide) in this same flow which, I will take one at a time to discuss in the coming posts.

SQL statements that help understand the data model are here.

This article gives you simple steps to load large sales orders in Order Management using Excel Spreasheet.

We arrived at this solution using some good features available in Oracle Database and Oracle Applications. Here are the steps.

1. We create order header only (using sales order form) with all the necessary information like customer, order type, price list, sales person, sales credits, warehouse and the other values as deemed necessary.
2. Now we create line information in Excel worksheet (a template is created). We enter values for the fields like Item, quantity, price list name (if that is different from header), Line Type, Sales Person (different from header), any DFF segments, Shipping Instructions if any, warehouse name so on. We can have as many fields as that can fit in the order lines table. But instead of entering all the values, we use defaulting rules to default a lot of values from the header.
3. In this worksheet there is a simple marco written that we use to generate another worksheet in the same file for the entered rows.This macro does some basic validations on the information entered in the worksheet and generates the worksheet with this statement for each row.
   1.  insert into custom_lines_load_tbl  (column1,column2,….) values ( field1,field2,……)
4. Now using these rows with this statement (generated worksheet) we create a simple text file (copy and paste).
5. As a next step we upload this file as an attachment to the order header that we have created in the step number 1.
6. Now we run a custom program to create order lines into this order. This program takes order number as a parameter. This program reads the file contents line by line and executes the insert statements. This is the heart of the solution which is to read the contents of the file which is stored as LOB and executing the statements. After all the insert statements are executed successfully, the program calls oe_order_pub API to create order lines.
7. As you can see the program uses this feature : reading the file which is stored in the database as LOB line by line. Here is the code snippet to read the attached file stored as a file.

PROCEDURE parse_and_load_file (
      ERROR_CODE       OUT      VARCHAR2,
      retcode          OUT      NUMBER,
      p_order_number   IN       NUMBER
   )
   IS
      fil             BLOB;
      pos             INTEGER         := 0;
      amt             BINARY_INTEGER  := 32767;
      buf             RAW (2000);
      l_start         RAW (3000);
      l_end           RAW (3000);
      l_start_pos     INTEGER;
      l_length        INTEGER;
      nth             INTEGER         := 1;
      i               INTEGER         := 2;
      l_text          VARCHAR2 (4000);
      l_end_pos       INTEGER;
      l_plsql_block   VARCHAR2 (4000);
      k               INTEGER;
      l_write_buff    RAW (2000);
      l_text1         VARCHAR2 (4000);
      x_errbuf        VARCHAR2 (4000);
      x_retcode       NUMBER;
   BEGIN
      BEGIN
    begin
    delete custom_lines_upload_tbl
    where order_number=p_order_number
    and line_Id is null; –this is to make sure we do not load duplicate data
    exception when others then
    null;
    end;

SELECT file_data
  INTO fil
  FROM fnd_lobs
 WHERE file_id =
          (SELECT media_id
             FROM fnd_documents_vl
            WHERE document_id IN (
                     SELECT a.document_id
                       FROM fnd_attached_documents a, fnd_documents b
                      WHERE a.document_id = b.document_id
                        AND b.category_id =
                                     (SELECT category_id
                                        FROM fnd_document_categories_tl
                                       WHERE user_name = ‘Custom Lines Upload’)
                        AND entity_name = ‘OE_ORDER_HEADERS’
                        AND pk1_value = (SELECT header_id
                                           FROM oe_order_headers_all
                                          WHERE order_number = p_order_number)))
         FOR UPDATE;
      EXCEPTION
         WHEN OTHERS
         THEN
            debug_log
               (   ‘Order :’
                || p_order_number
                || ‘ does not have any file attached to it to load. Please attach the file and then try running this program’
               );
      END;

      k := DBMS_LOB.ISOPEN (fil);

      — debug_log(’Checking whether the file is already open’);
      IF k = 1
      THEN
         –debug_log(’file is already open. Closing it’);
         DBMS_LOB.CLOSE (fil);
      END IF;

      BEGIN
         –debug_log(’Opening the file’);
         DBMS_LOB.OPEN (fil, DBMS_LOB.lob_readonly);
         k := DBMS_LOB.ISOPEN (fil);
      EXCEPTION
         WHEN OTHERS
         THEN
            debug_log
                    (’Exception occured in checking whether the file is open’);
      END;

      IF k = 1
      THEN
         LOOP
            l_start := UTL_RAW.cast_to_raw (’insert’);
            l_end := UTL_RAW.cast_to_raw (’;');
            l_start_pos := DBMS_LOB.INSTR (fil, l_start, 1, nth);
            pos := DBMS_LOB.INSTR (fil, l_end, 1, nth);
            buf := ”;
            –debug_log(’Start position is :’||l_start_pos);
            –debug_log(’End position is :’||pos);
            l_end_pos := (pos + 1) - (l_start_pos);
            –debug_log(’Length of the string is :’||l_end_pos);
            –debug_log(’Reading the file’);
            DBMS_LOB.READ (fil, l_end_pos, l_start_pos, buf);
            l_text := UTL_RAW.cast_to_varchar2 (buf);
            l_plsql_block :=
                  ‘begin’
               || fnd_global.NEWLINE ()
               || l_text
               || fnd_global.NEWLINE ()
               || ‘commit;’
               || fnd_global.NEWLINE ()
               || ‘exception when others then’
               || fnd_global.NEWLINE ()
               ||
                  –’debug_log(”Exception occured in processing the line”);’||fnd_global.NewLine()||
                  ‘NULL;’
               || fnd_global.NEWLINE ()
               || ‘end;’;

            –debug_log(’———————————————–’);
            –debug_log(l_plsql_block);
            –debug_log(’———————————————–’);
            BEGIN
            –Now execute the dynamic pl/sql block to insert rows into custom table from the file
               EXECUTE IMMEDIATE l_plsql_block;
            EXCEPTION
               WHEN OTHERS
               THEN
                  –debug_log(’Exception Occured’);
                  –debug_log(l_plsql_block);
                  –debug_log(’Exception is :’||substrb(sqlerrm, 1, 55));
                  NULL;
            END;

            nth := nth + 1;
         END LOOP;

         DBMS_LOB.CLOSE (fil);
      END IF;

      COMMIT;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         k := NULL;
         k := DBMS_LOB.ISOPEN (fil);

         IF k = 1
         THEN
            DBMS_LOB.CLOSE (fil);
         END IF;
      WHEN OTHERS
      THEN
         BEGIN
            — debug_log(’End of File reached in Exception. Closing file’);
            k := NULL;
            k := DBMS_LOB.ISOPEN (fil);

            IF k = 1
            THEN
               DBMS_LOB.CLOSE (fil);
               COMMIT;
            END IF;
         END;

         –Here call the oe_order_pub API to create the lines passing the line table from this custom table.
   END parse_and_load_file;

 This articles provides insight into the functionality behind the table ASO_ORDER_FEEDBACK_T and information on how we regained 100GB space by performing certain tasks.

My client is into four years of implementation with a lot of applications implemented in ERP and CRM areas. While performing database audit, our DBA team found that the table ASO_ORDER_FEEDBACK_T was growing at an alarming rate (size was close to 100GB). They wanted the to understand the functionality behind this table and recommendations, if any, can be made to contain the growth. So, before we took any action we had to perform research and recommend an action plan to manage the growth. Here is the simple summary of what we found.

Functional Analysis

As a standard feature in Order Management there is a queue generated whenever a sales order is booked. This table holds the queue which has complete information about a sales order. This is a good feature if we want to execute an action in some other application as soon the order is booked. As a seeded funcationality this queue table holds information which is used by some applications with in Oracle Applications. But as an extension we can always use this queue if we want to communicate with any application of our choice. As an example if we have some custom shipping application and we want to send messages to that system as soon the order is booked, we can make use of this feature. Here is how it works.

This queue is built as soon the order is booked which happens in the private api that is used to create orders: oe_order_pvt.process_order. This in turn calls the package  oe_service_util.notify_oc which builts the queue. This simple article addresses the following questions:

1. Who subscribed to this message?
2. Who consumes this message queue?
3. Can we turn off this so that we can contain this table’s growth?
4. What is the impact of turning this off?
5. Can we add our own application get messages from order?

Subscribers

Subscribers to this message can be located in the look up type ASO_ORDER_FEEDBACK_CRM_APPS. All the active lookup codes (enabled flag is checked and end date is less than or equal to today’s date) in this look up type are the subscribers. The number of queues built in this table for each order is equal to the number of active subscribers.You can imagine the growth of this table if you are booking a large volume of the orders on a daily basis and have no consumers for this message based on your business needs. Oracle Applications come with four standard subscribers for this queue that get auto installed: CN (Incentive Compensation), CS (Cusotmer Service and actually used by Install Base), OKS (Service Contracts) and OZF (Funds & Budgets or popularly known as Trade Management as well as Oracle Marketing).

Who Consumes these messages?

The following programs and packages consume the queue when you run the programs. If you do not run these programs obviously the queue grows as soon an order is booked and never gets dequeued.

• CS or Install Base queue, when consumed, creates/updates the install base of old fulfilled order lines that have been fulfilled prior to 11.5.6 upgrade. One time usage.

• Between Oracle Marketing and Fund & Budgets, the OZF queue is consumed, depending on which program and version of applicatons is used. This basically eats into the budgeted funds of the marketing campaign allocated in these applications. These accruals come as price adjustments or promotions from order management. Please make sure you take an expert’s opinion in these applications for more informaton.

• CN queue is consumed when you collect orders to pay commission for the orders that are booked. This obviously suggests of a business process where booked orders are used as a source to pay commisssion to the sales force.

• When OKS queue is consumed, it creates a service contract (Service Agreement or Extended Warranty or Warranty depending on the profile option OKS: OM interface Contract Category setup) from order management when a service item is associated to a line that creates/updates the install base. The association of Service Item to the Inventory Items that is trackable in Install Base is done using Service tab in the order lines.

Please refer to respective product documentation for more information on these programs and their functionality.

How to generate a custom queue?

All we need to do is to subscribe your application to this queue. To achieve we enter our custom application name (for example SHIP)  as a lookup code in the lookup type ASO_ORDER_FEEDBACK_CRM_APPS. This will generate the queue into this code as soon the order is booked. To consume this we can use the package aso_order_feedback_pub.get_notice with this application name (SHIP) to read the queue followed by actual work we want to do using the information available in this queue like shipping a custom application.

How to contain the growth of this table if we do not have any consuming application and what is the impact?

Obviously the simplest way to contain this is by disabling the subscribers in the lookup type.But before doing so we should make sure we have no need to run the programs that are mentioned above, otherwise it can cause issues with a number of applications. If we have no business need to run the program, we can safely disable them. This will stop further growth though you are entering new orders and booking them.

What do we do with my existing data in the queue table if we do not have any need to run the programs?

Please refer to the metalink note 181410.1 for more information on this.

What we did?

Following the instructions in the above note, first we dropped the queue. Then after careful analysis we disabled the subscribers to this queue as we do not use them. Then we recreated the queue.