API To Create User and Assign Responsibility to the User

DECLARE
   l_user_name                VARCHAR2 (100);
   l_user_password            VARCHAR2 (100) := 'welcome1';
   l_user_start_date          DATE := SYSDATE;
   l_user_end_date            VARCHAR2 (100) := NULL;
   l_password_date            VARCHAR2 (100) := SYSDATE;
   l_password_lifespan_days   NUMBER;
   l_person_id                NUMBER;
   l_email_address            VARCHAR2 (100);
   l_count                    NUMBER;
   l_change_password          BOOLEAN;
   l_user_id                  NUMBER;

   PROCEDURE assign_responsibility (p_user_name IN VARCHAR2)
   IS
      CURSOR c_resp
      IS
         SELECT fav.application_short_name,
                fav.application_name,
                frv.responsibility_key,
                frv.responsibility_name
           FROM fnd_application_vl fav, fnd_responsibility_vl frv
          WHERE     frv.application_id = fav.application_id
                AND frv.responsibility_name IN ('US Receivables Super User',
                                                'US Service Contracts Super User',
                                                'US Order Management Super User',
                                                'Installed Base User');

      l_appl_short_name   fnd_application_vl.application_short_name%TYPE;
      l_resp_name         fnd_responsibility_vl.responsibility_name%TYPE;
      l_resp_key          fnd_responsibility_vl.responsibility_key%TYPE;
      l_description       VARCHAR2 (100)
         := 'Adding Responsibility to the user using script';
      l_count             NUMBER;
   BEGIN
      FOR resp_rec IN c_resp
      LOOP
         SELECT COUNT (1)
           INTO l_count
           FROM fnd_user fuser,
                per_people_f per,
                fnd_user_resp_groups furg,
                fnd_responsibility_tl frt
          WHERE     fuser.employee_id = per.person_id
                AND fuser.user_id = furg.user_id
                AND (   TO_CHAR (fuser.end_date) IS NULL
                     OR fuser.end_date > SYSDATE)
                AND frt.responsibility_id = furg.responsibility_id
                AND (   TO_CHAR (furg.end_date) IS NULL
                     OR furg.end_date > SYSDATE)
                AND frt.language = 'US'
                AND fuser.user_name = p_user_name
                AND frt.responsibility_name = resp_rec.responsibility_name;

         IF l_count = 0
         THEN
            l_appl_short_name := resp_rec.application_short_name;
            l_resp_key := resp_rec.responsibility_key;
            l_resp_name := resp_rec.responsibility_name;


            BEGIN
               fnd_user_pkg.addresp (username         => p_user_name,
                                     resp_app         => l_appl_short_name,
                                     resp_key         => l_resp_key,
                                     security_group   => 'STANDARD',
                                     description      => l_description,
                                     start_date       => SYSDATE,
                                     end_date         => NULL);
               COMMIT;
               DBMS_OUTPUT.put_line (
                     'The responsibility : '
                  || l_resp_name
                  || ' is added to the user '
                  || p_user_name);
            EXCEPTION
               WHEN OTHERS
               THEN
                  DBMS_OUTPUT.put_line (
                        'Responsibility '
                     || l_resp_name
                     || ' IS NOT added to the user '
                     || p_user_name
                     || ' due to '
                     || SQLCODE
                     || '; '
                     || SUBSTR (SQLERRM, 1, 250));
                  DBMS_OUTPUT.put_line ('');
                  ROLLBACK;
            END;
         ELSE
            DBMS_OUTPUT.put_line (
                  'Responsibility : '
               || resp_rec.responsibility_name
               || ' Already assigned');
         END IF;
      END LOOP;
   END;

 
BEGIN
   fnd_global.apps_initialize (17959, 20420, 1);

   FOR i IN 1 .. 200
   LOOP
      l_user_name := 'TESTUSER' || i;
      fnd_user_pkg.createuser (
         x_user_name                => l_user_name,
         x_owner                    => NULL,
         x_unencrypted_password     => l_user_password,
         x_start_date               => l_user_start_date,
         x_end_date                 => l_user_end_date,
         x_password_date            => l_password_date,
         x_password_lifespan_days   => l_password_lifespan_days,
         x_employee_id              => 12345,
         x_email_address            => 'xxxx@gmail.com');

      COMMIT;

      SELECT user_id
        INTO l_user_id
        FROM fnd_user
       WHERE user_name = l_user_name;

      IF l_user_id IS NOT NULL AND l_user_id > 0
      THEN
         assign_responsibility (l_user_name);

         BEGIN
            jtf_auth_bulkload_pkg.assign_role (l_user_name,
                                               'CSI_NORMAL_USER');
            jtf_auth_bulkload_pkg.assign_role (l_user_name, 'CSI_ADMIN_USER');
            jtf_auth_bulkload_pkg.assign_role (l_user_name,
                                               'CSI_READ_ONLY_USER');
            COMMIT;
            DBMS_OUTPUT.put_line ('Assign IB Roles Successfully!!!');
         END;

         l_change_password :=
            fnd_user_pkg.changepassword (l_user_name, 'welcome123');

         IF l_change_password
         THEN
            DBMS_OUTPUT.put_line ('Password Changed successfully!!!');
            COMMIT;
         ELSE
            DBMS_OUTPUT.put_line ('Unable to Change Password!!!');
            ROLLBACK;
         END IF;
      ELSE
         DBMS_OUTPUT.put_line (
               'User : '
            || l_user_name
            || ' Not found, hence unable to assign the responsibilities');
      END IF;

      l_user_name := NULL;
   END LOOP;

   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      ROLLBACK;
      DBMS_OUTPUT.put_line (SQLERRM);
END;
/

What is Record to Reporting Cycle – R2R

Record to report or R2R is the management process for providing strategic, financial and operational feedback to understand how a business is performing.

This process involves collecting, transforming and delivering relevant, timely and accurate information to all stakeholders inside and outside the organization, to provide insight into how their expectations have been met.

It covers the steps involved in preparing and reporting the overall accounts which are typically stored in a general or nominal ledger and managed by a comptroller. The detailed steps involved are:

• data extraction
• data collection
• data validation
• data transformation (generation of voucher)
• voucher posting (to general ledger)
• storing vouchers in de-normalized and compressed format
• generating analysis account trial balance or consolidated analysis account trial balance
• generating user-defined financial and management reports

In ERP systems , This is part  month end  process in which basically user will close all the periods  and import all the journals in GL from other modules
(Payable, Receivable, Fixed Assets etc.). Then run all the month end GL reports like Trial Balance, Account Analysis  etc.

Following are broader division of activity involved in Record to Report Cycle

Reconciliations

• Intercompany
• Multiple systems
• Bank
• Subsidiary to main set of books
• Customer
• Vendor

General Accounting

• Journal Entries
• Intercompany accounting
• Book closures
• Trial Balance preparation
• Accruals
• Consolidations
• Cost Accounting

Record to Report process might seem simple but it require considerable efforts. It is the only process scrutinized by outside auditors who draw conclusions about controls in place during financial close and reporting cycle.

How to Programmatically close PO using PO_ACTIONS API

PO_ACTIONS.CLOSE_PO is the API used to programmatically close/final close Purchase Order. We have to pass P_ACTION parameter value as 'CLOSE'/'FINALLY CLOSE' depending on the requirement to close a Purchase Order.

Before applying the API, we are taking a PO as CLOSE_CODE  as 'CLOSED' for testing purpose and query the purchase order in Backend. The result of PO is given in snapshot below. 

Below are the code to execute the api.

Sample:-

/* Formatted on 2016/10/18 12:08 (Formatter Plus v4.8.8) */

DECLARE

   x_action         CONSTANT VARCHAR2 (20)  := 'FINALLY CLOSE';

   -- Change this parameter as per requirement

   x_calling_mode   CONSTANT VARCHAR2 (2)   := 'PO';

   x_conc_flag      CONSTANT VARCHAR2 (1)   := 'N';

   x_return_code_h           VARCHAR2 (100);

   x_auto_close     CONSTANT VARCHAR2 (1)   := 'N';

   x_origin_doc_id           NUMBER;

   x_returned                BOOLEAN        := NULL;

BEGIN

  

   apps.mo_global.set_policy_context ('S', 204);

   fnd_global.apps_initialize (1015932, 50578, 201);

   DBMS_OUTPUT.put_line

             ('Calling PO_Actions.close_po for Closing/Finally Closing PO =>');

   x_returned :=

      po_actions.close_po (p_docid              => 131583,

                           p_doctyp             => 'PO',

                           p_docsubtyp          => 'STANDARD',

                           p_lineid             => NULL,

                           p_shipid             => NULL,

                           p_action             => x_action,

                           p_reason             => NULL,

                           p_calling_mode       => x_calling_mode,

                           p_conc_flag          => x_conc_flag,

                           p_return_code        => x_return_code_h,

                           p_auto_close         => x_auto_close,

                           p_action_date        => SYSDATE,

                           p_origin_doc_id      => NULL

                          );

   COMMIT;

   IF x_returned = TRUE

   THEN

      DBMS_OUTPUT.put_line

                   ('Purchase Order which just got Closed to Finally Closed. ');

      DBMS_OUTPUT.put_line (x_return_code_h);

   ELSE

      DBMS_OUTPUT.put_line

                      ('API Failed to Close/Finally Close the Purchase Order');

   END IF;

END;

After applying the API, the status of given PO is  changed successfully.

Drop Shipment Process in a Picture

What is Scheduling, Reservation , ATP and few common Terms in Sales Order Shipping.

Before we start understanding scheduling we need to know certain terms and how they are derived in Oracle.

Terminology
Understanding the following terms will help you understand how scheduling works
in OM.

Actual Arrival Date - The date the order line arrives at the customer site.

Actual Ship Date - The date the order line is shipped. This date is recorded by the
ship confirm action.

Arrival Set - A set of order lines which arrive at the same time at the destination.

Available to Promise (ATP) - The quantity of current on-hand stock, outstanding
receipts and planned production not already committed to sales orders or other
sources of demand.

ATP Date - The date that a requested quantity will be available to promise.

Delivery Lead Time - Time (in days) for items to reach the customer once they are
shipped.

There are two ways to help system calculate this date.

1) Create a location for the Ship-to address and assign it as the internal location and then define inter-location transit time
2)Create a Zone/Region and then assign the inter-location transit time

Demand - Requests which consume inventory such as sales orders. Discrete
manufacturing work orders and flow manufacturing schedules place demand for
component items, and sales orders place demand for finished goods.

Promise Date - The date on which you agree you can ship the products to your
customer or that your customer will receive the products. This field is for tracking
purposes only. It may be defaulted from the schedule ship date or the schedule
arrival date.

Request Date - The date the customer requests that the products be either
shipped or received.

Reservation - A guaranteed allotment of product to a specific sales order. Once
reserved, the product cannot be allocated to any other source of demand. Also
known as a hard reservation.

Reservation Time Fence - Time (in days) before the schedule date, within which a
line should be automatically reserved.

This is Set by Profile option OM:Reservation time fence

Schedule Arrival Date - The date returned by the system on which your customer
can receive the products.

* Schedule Arrival Date = Schedule Ship Date + Delivery Lead Time

Schedule Ship Date - The date returned by the system on which you can ship the
products.

Ship Set - A set of lines which will be shipped together from the same warehouse
to the same location.

Sourcing - Selecting the warehouse for the order lines.

Supply - Incoming inventory. Some Oracle transactions that generate supply are
purchase orders, discrete manufacturing work orders and flow manufacturing
schedules.

Latest Acceptable date (LAD):
LAD is populated only when the Latest Schedule Limit (LSL) is provided at the time of order creation.

Latest Schedule Limit is defaulted from the site level or customer level based on defaulting rules.


* Latest Acceptable Date = Request date + Latest Schedule Limit

Request Date Type - Possible values are arrival and ship. If the value is arrival
then the request date and promise date will be considered arrival dates by the
system; if the value is ship then they will be considered ship dates. The request
date type can be defaulted from the customer information to the order, and
the user can change it on the order if required.
 Latest Schedule Limit - This field can contain any numeric positive integer
value. When you enter an order line, the latest acceptable date will be
calculated by adding the latest schedule limit to the request date. When the
scheduling action occurs, the schedule date will only be returned if it is
between the requested date and the latest acceptable date. If it is not within
this range, the scheduling action fails.




WHAT HAPPENS ON SCHEDULING:


Scheduling is an action performed on an order line or a group of lines. The action
does the following -



• Determines the source (warehouse) for the order line. If the warehouse is
entered on the line, either manually or using defaulting rules, the scheduling
action uses the requested warehouse and the other scheduling results are based
on it. If the warehouse is blank, the scheduling action determines the best
warehouse based on the sourcing rules.
• Determines the schedule ship date, the schedule arrival date, the delivery lead
time and the shipping method.
• Makes the line visible to the planning applications and consumes supply for
the item. When a line is successfully scheduled the
VISIBLE_DEMAND_FLAG is set to Yes.
• If the reservation time fence is set and the schedule ship date is within the
reservation time fence, automatically reserves the line.

The request date may be either the requested ship date or the requested arrival date
depending on the request date type of the customer. If the customer’s request
dates are requested arrival dates, the scheduling action calls MRP’s scheduling API
with the requested arrival date. The API returns the first date on or after the
requested arrival date that the items could arrive at the customer location, and
enters that date into the scheduled arrival date field for the line(s). The schedule
ship date is calculated by subtracting the delivery lead time (number of days for
items to reach the customer once they ship) from the schedule arrival date. If the
shipping network has not been defined for this combination of locations, the
delivery lead time will be considered 0 days and the schedule ship date and
schedule arrival date will be the same.
If a user enters a schedule ship date on the order line before performing the
schedule action, when the schedule action occurs the system tries to schedule on
that date. If it can’t, the schedule action fails.
You can define for each customer the delivery window in days that they will accept
by entering the latest schedule limit on the customer form. When you enter an
order line, the latest acceptable date is calculated by adding the latest schedule limit
to the request date. When the scheduling action occurs, the schedule date will only
be returned if it is between the requested date and the latest acceptable date. If it
is not within this range, the scheduling action fails. For example, suppose that you
have a customer who only accepts orders that ship within 5 days of the request
date. You would enter 5 in the latest schedule limit fields on the Order
Management tab of the customer form. When you enter an order line, if the
request date is September 10, the latest acceptable date would be September 15.
When the scheduling action occurs, if the schedule date returned is not in the date
range of September 10 through September 15, the schedule request fails.


NUMBER OF WAYS TO SCHEDULE:


• Autoschedule - The line is scheduled when it is saved. A line can be saved
manually by the user or will automatically be saved when the user leaves the
line. If either the Autoschedule check box on the order transaction type is
checked or the OM: Autoschedule profile option is Yes, the sales order will be
opened in Autoschedule mode. You can turn Autoschedule on or off from
the sales order form by going to the Tools menu. Note that if autoschedule is
turned on the availability window is automatically displayed when the sales
order form is opened. The user can close the availability window, but the lines
will still be autoscheduled unless the autoschedule check box on the tools
menu is unchecked.

• Manual - You can access the scheduling sub menu either by selecting schedule
from the list of activities on the tools menu or by placing your cursor on a line
and pressing the right mouse button. Selecting schedule from these menus
will trigger the scheduling action. If the action is selected from the order
header tab, all the lines on the order will be scheduled. If the action is selected
from the lines tab, it applies only to the line or group of lines selected.

• Scheduling Concurrent Program - This program selects all lines which are
eligible for scheduling and attempts to schedule them. The user can select
orders based on the order number







CALCULATING THE ATP:




ATP will be automatically calculated during scheduling, and may be calculated
manually by pressing the Availability button on the line items tab of the sales order
form.

There are several setup steps required for ATP calculations to work. 

1)ATP rules must be defined to determine the sources of supply and demand which are
included in the calculation. 

2)The ATP rules must be associated with items and/or Inventory organizations.

3)The data collection program must be run. A concurrent process known as data
collection must be run to summarize the supply and demand picture. This
program is part of the Oracle Advanced Planning and Scheduling application.

The ATP calculation is then performed on the summary tables.


ATP Rules are created in the Inventory module. They indicate which sources of
supply and demand to consider when calculating ATP. They can be assigned to
inventory organizations and items. If an ATP rule is assigned to an item that is
used. If the ATP rule for the item is blank, then the ATP rule for the inventory
organization is used. 

You must define sourcing rules if you want ATP to determine the warehouse for
your order lines. Once sourcing rules are defined, they must be assigned to
particular items, categories and/or inventory organizations. You do this using
assignment sets. 

For scheduling to work in OM you must successfully run the data collection
concurrent request set. As previously stated, calculating ATP must happen almost
instantaneously, but searching through all the possible sources of supply and
demand to calculate ATP is very complex. Therefore, a concurrent process known
as data collection must be run to summarize the supply and demand picture. The
ATP calculation is then performed on the summary tables. To run the data
collection request set, choose Scheduling -> Collect Data from the OM navigation
menu. There are two programs in the request set. Enter parameters for both and
submit the set. The Planning Data Pull program has a parameter named Complete
Refresh. If this is yes, then the collection will select all scheduling related
information from the relevant tables. If it’s no, then only the updated information
will be selected. For details on running the data collection programs see the Oracle
Advanced Supply Chain Planning and Oracle Global ATP Server User’s Guide.
The scheduling level on the order transaction type determines what type of
scheduling is allowed. The possible values for this setting are ATP Only, No
Reservations and Allow All Scheduling Actions. If the value is ATP Only then you
will not be able to schedule or reserve lines on the order. If the value is No
Reservations then you can perform all scheduling functions except for reserving
inventory. If the value is Allow All Scheduling Actions or NULL then all
scheduling functions can be performed.





EXAMPLES OF SCHEDULING

Example 1:

The warehouse for the order is defaulted from the ship to site. A shipping
network is defined for this warehouse/ship to combination with the shipping
method of UPS ground, and the transportation lead time is 5 days. The customer
requests the shipment as soon as possible, so the request date is entered as today’s
Scheduling in Oracle Order Management Page 15
date. On-hand inventory is available to fulfill the order. Autoschedule is on, and
the reservation time fence is 5 days.
The user enters an order line with the item, quantity and request date. When the
line is saved, because autoschedule is on, it is automatically scheduled for the
requested warehouse with a schedule ship date of today and a schedule arrival date
of today plus 5 days. Because the schedule ship date is within the reservation time
fence the line is also automatically reserved.

Example 2:

No warehouse is defaulted or entered for the order. No shipping network is
defined for the customer. The customer requests the shipment as soon as
possible, so the request date is entered as today’s date. There is no inventory
available to fulfill the order, but there is a work order scheduled for completion in
10 days, and your ATP rule includes work orders as a source of supply.
Autoschedule is off, but the line level workflow process has the scheduling activity
immediately after booking as a synchronous activity.
The user enters an order line with the item, quantity and request date and saves the
line. Because autoschedule is off, no scheduling action occurs at this time. The
user enters additional lines and then books the order. As soon as the order is
booked, the scheduling activity from the workflow executes. The warehouse is
determined by the sourcing rules. The schedule ship date is today + 10 days (the
day that the work order is scheduled to complete.) The schedule arrival date is the
same as the schedule ship date, because the shipping network is not defined for
this combination of customer, warehouse and ship method.

Example 3:

The warehouse is defaulted from the customer ship to site. No shipping network
is defined for the customer. The customer requests the shipment as soon as
possible, so the request date is entered as today’s date. There is no inventory
available to fulfill the order, and there are no work orders or purchase orders for
the items. Your ATP rule specifies an infinite supply time frame of 30 days. The
customer has a Latest Schedule Limit of 10 days. Autoschedule is off, but the line
level workflow process has the scheduling activity immediately after booking as a
synchronous activity.
The user enters an order line with the item, quantity and request date and saves the
line. Because autoschedule is off, no scheduling action occurs at this time. The
user enters additional lines and then books the order. As soon as the order is
booked, the scheduling activity from the workflow executes. The ATP date is
calculated to be today + 30 days because of the infinite supply days of the ATP
rule. However, the Earliest Acceptable Date is today + 10 days because of the
customer setup. So the scheduling activity fails, the user sees an error message,
and the line remains at the workflow activity of Schedule - Eligible until a source
of supply can be created or until the Latest Acceptable Date is changed. Then the

line can be scheduled by either manually progressing the line or running the
scheduling concurrent program.

PROFILE OPTIONS THAT PLAY CRUCIAL ROLE


• OM: Schedule Lines on Hold - Possible values are yes and no. If this field is
yes, the scheduling action processes order lines even if the order or line is on
hold. If no the scheduling action will fail.

• OM: Autoschedule - Possible values are yes and no. If this field is yes the
availability window is displayed when the sales order form is opened and
scheduling occurs automatically as each order line is saved.

• OM: Reservation Time Fence - This may be any positive integer numeric
value. When a line is scheduled it is also automatically reserved whenever the
schedule date is within the reservation time fence.

• OM: Auto Push Group Date - Possible values are yes and no. If the value is
yes and a line is added to a scheduled configuration, and the new line cannot
be scheduled on the date that the rest of the configuration is scheduled, then
the system will try to reschedule the complete configuration at a different time.
If the value is no and the new line cannot be scheduled, then scheduling for
the new line will fail and the rest of the configuration will not be affected.

• MRP:ATP Assignment Set - This can be any valid assignment set which is
defined in the MRP application. It specifies the assignment set that will be
used for calculating ATP. Assignment sets are mentioned later in this section.

• INV: Capable to Promise - Possible values are Enable Product Family ATP
and CTP; Enable Product Family ATP; Enable ATP; Enable PL/SQL based
ATP with Planning Output; and Enable PL/SQL based ATP without
Planning Output. This profile option indicates whether and how to enable the
CTP calculation. For ATP to work in OM, the value must be Enable
PL/SQL based ATP without Planning Output.

RESERVATIONS

In OM, you can reserve on-hand inventory to a sales order. Reserved inventory
cannot be used for any other purpose. The reserved quantity for a sales order line
is displayed on the shipping tab. You may reserve part or all of the ordered
quantity.

A line must be scheduled before it can be reserved. If you try to reserve an
unscheduled line, the system will first try to schedule the line. If the line is
successfully scheduled then the system will try to reserve it.

There are two ways to reserve from the sales order form. 
1)You can select reserve from the scheduling option under the tools menu 
2) select reserve from the scheduling sub menu which is displayed when you press the right mouse button.
If you are on an order line the line will be reserved. If you are on the header, all
the lines will be reserved.

Reservations are performed automatically whenever a line is scheduled and the
schedule date is within the reservation time fence.
 For example, suppose the today’s date is November 25th. An order line is scheduled for December 1st, which is 6 days away. If the reservation time fence is 10, the line will be reserved because
6 < 10. If the reservation time fence is 2, the line will not be reserved because 6 >
2. If the reservation time fence is NULL, then lines will not be automatically
reserved. The reservation time fence is set using the profile option OM:
Reservation Time Fence.

When you create reservations manually on the sales order form or automatically
using the reservation time fence, the items are reserved at the warehouse level with
no inventory details specified. You can specify inventory details for a reservation
by using inventory’s reservation details form. To access the form from the sales
order form, go to the tools menu and select scheduling. From the list of options
select Reservation Details. A form will appear which allows you to reserve by lot,
revision, subinventory and/or locator. You can only access the reservation details
form for lines that are scheduled.