, , ,

PL/SQL Block

PL/SQL is a block-structured language, meaning that programs can be divided into logical blocks. Program units can be named or unnamed blocks. Unnamed blocks are known as anonymous blocks. The PL/SQL coding style differs from that of the C, C++, and Java programming languages. For example, curly braces do not delimit blocks in PL/SQL.
A PL/SQL block consists of up to three sections: declarative (optional), executable (required), and exception handling (optional).
Note: In PL/SQL, an error is called an exception.


Executing Statements
DECLARE v_variable VARCHAR2(5);
BEGIN
SELECT column_name INTO v_variable FROM table_name;
EXCEPTION
WHEN exception_name THEN
…
END;

  • Place a semicolon (;) at the end of a SQL statement or PL/SQL control statement.
  • Section keywords DECLARE, BEGIN, and EXCEPTION are not followed by semicolons. END and all other PL/SQL statements require a semicolon to terminate the statement.

Block Types
A PL/SQL program comprises one or more blocks. These blocks can be entirely separate or nested one within another. The basic units (procedures and functions, also known as subprograms, and anonymous blocks) that make up a PL/SQL program are logical blocks, which can contain any number of nested subblocks. Therefore, one block can represent a small part of another block, which in turn can be part of the whole unit of code.

/by
, , ,

Basics of PL/SQL

PL/SQL stands for Procedural Language extension of SQL.
PL/SQL is a combination of SQL along with the procedural features of programming languages. It was developed by Oracle Corporation in the early 90’s to enhance the capabilities of SQL.
PL/SQL Environment
PL/SQL is not an Oracle product in its own right; it is a technology used by the Oracle server and by certain Oracle tools. Blocks of PL/SQL are passed to and processed by a PL/SQL engine, which may reside within the tool or within the Oracle server. The engine that is used depends on where the PL/SQL block is being invoked from. When you submit PL/SQL blocks from a Oracle precompiler such as Pro*C or Pro*Cobol program, userexit, iSQL*Plus, or Server Manager, the PL/SQL engine in the Oracle Server processes them. It separates the SQL statements and sends them individually to the SQL statements executor.

A single transfer is required to send the block from the application to the Oracle Server, thus improving performance, especially in a client-server network. PL/SQL code can also be stored in the Oracle Server as subprograms that can be referenced by any number of applications connected to the database.
Many Oracle tools, including Oracle Developer, have their own PL/SQL engine, which is independent of the engine present in the Oracle Server. The engine filters out SQL statements and sends them individually to the SQL statement executor in the Oracle server. It processes the remaining procedural statements in the procedural statement executor, which is in the PL/SQL engine. The procedural statement executor processes data that is local to the application (that is, data already
inside the client environment, rather than in the database). This reduces the work that is sent to the Oracle server and the number of memory cursors that are required.
Advantages of PL/SQL
These are the advantages of PL/SQL.
Block Structures: PL SQL consists of blocks of code, which can be nested within each other. Each block forms a unit of a task or a logical module. PL/SQL Blocks can be stored in the database and reused.
Procedural Language Capability: PL SQL consists of procedural language constructs such as conditional statements (if else statements) and loops like (FOR loops).
Better Performance: PL SQL engine processes multiple SQL statements simultaneously as a single block, thereby reducing network traffic.
Error Handling: PL/SQL handles errors or exceptions effectively during the execution of a PL/SQL program. Once an exception is caught, specific actions can be taken depending upon the type of the exception or it can be displayed to the user with a message.
Architecture
The PL/SQL language is a robust tool with many options. PL/SQL lets you write code once and deploy it in the database nearest the data. PL/SQL can simplify application development, optimize execution, and improve resource utilization in the database.
The language is a case-insensitive programming language, like SQL. This has led to numerous formatting best practice directions. Rather than repeat those arguments for one style or another, it seems best to recommend you find a style consistent with your organization’s standards and consistently apply it. The PL/SQL code in this book uses uppercase for command words and lowercase for variables, column names, and stored program calls
PL/SQL also supports building SQL statements at run time. Run-time SQL statements are dynamic SQL. You can use two approaches for dynamic SQL: one is Native Dynamic SQL (NDS) and the other is the DBMS_SQL package. The Oracle 11g Database delivers new NDS features and improves execution speed. With this release, you only need to use the DBMS_SQL package when you don’t know the number of columns that your dynamic SQL call requires. Chapter 11 demonstrates dynamic SQL and covers both NDS and the DBMS_SQL package.

/by
, , , ,

Reference Data Sharing


In 11i/R12 there are many configuration data which ‘er shared across all the OUs. Ex: Payment term. It’s good because you are required to define the data only once but the biggest disadvantage is that there is no method to restrict the access. In fusion application a new concept of data sharing is added, where you define reference data set(s) and determine how the data is shared or partitioned.
Reference data sharing facilitates sharing of configuration data such as jobs and payment terms, across organizational divisions (another new organization concept in FA) or business units. Depending on the requirement (specific or common), each business unit can maintain its data at a central location, using a set of values either specific to it or shared by other business units.
This new feature reduces duplication as well as provides the facility to restrict data as required.
From Oracle Guide

Reference data sets are logical groups of reference data that can be accessed by various transactional entities depending on the business context. Oracle Fusion Applications contains a common reference data set as well as an enterprise set that may be used as a default set. Depending on your business requirement you can create and maintain additional reference data sets, while continuing to use the common reference data set.

Ex: The senior management can decide to use a single AP payment method data set throughout the enterprise & let business unit’s managers define their own payment formats.
Partitioning
The partitioning of reference data and creation of data sets enable you to create reference entities across tables or lookup types, and share modular information and data processing options among business units. With the help of partitioning, you can choose to create separate sets and subsets for each business unit depending upon its business requirement, or create common sets or subsets to enable sharing reference data between several business units, without the need for duplicating the reference data. Partitioning provides you the flexibility to handle the reference data in a way appropriate to your business needs.
The figure illustrates the reference data sharing method (assignment to one set only, with common values) where the user can access the data assigned to a specific set in a particular business unit, as well as access the data assigned to the common set.

/by
, , , , ,

Recurring Journals in GL

Define recurring journal formulas for transactions that you repeat every accounting period, such as accruals, depreciation charges, and allocations. Your formulas can be simple or complex. Each formula can use fixed amounts and/or account balances, including standard, end-of-day, or average balances, actual or budget amounts, statistics, and period-to-date or year-to-date balances from the current period, prior period, or same period last year. You can quickly create new recurring formulas by copying and modifying existing formulas.
You can use recurring journals to create three types of journal entries:
Skeleton Journal Entries: Skeleton entries affect the same accounts each period, but have different posting amounts. After you generate skeleton journal entries, you can edit the unposted journal batch using the Enter Journals form and enter the journal line amounts.
Skeleton journal entries are useful with statistical information whenever you want to record journals for actual transactions based on statistical amounts, such as headcount, units sold, inflation rates, or other growth factors. For example, if you want to enter headcount for each cost center every period, you can define a skeleton entry with your headcount accounts. After you generate the skeleton entries, enter the actual headcount amounts before posting the batch. 
Standard Recurring Journal Entries: Standard recurring journal entries use the same accounts and amounts each period.
Recurring Journal Formula Entries: Formula entries use formulas to calculate journal amounts that vary from period to period.

        Important: If you use summary accounts in your recurring journals, General Ledger maintains references to those summary account templates, even if you delete then recreate the summary accounts.

        /by
        , , , ,

        Oracle Procure to Pay (P2P) and Order To Cash(O2C) Cycles

        The different types of ordering methods followed across the organizations are discussed below and must follow one of these categories:

        Make to Order:

        A business production strategy that typically allows consumers to purchase products that are customized to their specifications. The make to order (MTO) strategy only manufactures the end product once the customer places the order. This creates additional wait time for the consumer to receive the product, but allows for more flexible customization compared to purchasing from retailers’ shelves.

        Make To Stock:

        A traditional production strategy used by businesses to match production with consumer demand forecasts. The make-to-stock (MTS) method forecasts demand to determine how much stock should be produced. If demand for the product can be accurately forecasted, the MTS strategy can be an efficient choice.

        Assemble to order:

        A business production strategy where product ordered by customers can be produced quickly and are customizable to a certain extent. The assemble-to-order (ATO) strategy requires that the basic parts for the product are already manufactured but not yet assembled. Once an order is received, the parts are assembled quickly and sent to the customer.

        Make To Assemble:

        A manufacturing production strategy where a company stocks the basic components of a product based on demand forecasts, but does not assemble them until the customer places an order. This allows for order customization. MTA production is basically a hybrid of two other major types of manufacturing production strategies: make to stock (MTS) and make to order (MTO).
        Oracle has developed this ERP solution which truly covers these both cycles Purchase to Pay and Order to Cash. Oracle EBS comprises of the Standard Core Business Management applications like General Ledger, Payables, Receivables, Cash Management, Fixed asset, Purchasing, Order Management, Inventory, Discrete Manufacturing, Process Manufacturing , HRMS and many more. The application mentioned are so integrated that it handles the beginning to end of both Assets (Order Management and Receivables) and Liabilities (Purchasing and Payables). These Assets and Liabilities are finally pushed and calculated in Oracle General Ledger. The base or the heart of Oracle EBS is Oracle General Ledger. Let me  call GL an intrinsic.

        Order to Cash Cycle:

        Order to Cash means Customer’s Order Placing to Vendor’s Cash Receiving. When your final product is ready to be sold, you market it. The customer gets fascinated with the marketing campaign and decides to buy your product and from here starts the O2C cycle.
        1. Oracle Order Management: Customer places the order.
        2. Oracle Order Management: You enter the customer order
        3. Oracle Inventory: Check the available unit and the quantity ordered by the customer.
        4. Oracle Order Management: You ship the product to customer site and decreases the Finished Goods inventory.
        5. Oracle Receivables: The customer receives the product and you invoice the customer.
        6. Oracle General Ledger: You record your revenue and receivables.
        7. Oracle Receivables: The customer pays and you receive the cash/check.
        8. Oracle Cash Management: Oracle Receivables sends the customer receipt for Bank Reconciliation. After reconciliation, Oracle Cash Management send the actual bank balance or Oracle General Ledger.
        9. Oracle General Ledger: You have the actual bank balance.

        Following Accounting entries will be generated for O2C

        • Sales order creation – No entries
        • Pick release:
          Inventory Stage A/c…………………Debit
          Inventory Finished goods a/c……..Credit
        • Ship confirm:
          Cogs A/c ……………………………Debit
          Inventory Organization a/c………Credit
        • Receviable:
          Receviable A/c………………………Debit
          Revenue A/c………………………Credit
          Tax ………………..…………………Credit
          Freight…………..….……………….Credit
        • Cash:
          Cash A/c Dr…………………………Debit
          Receivable A/c……………………….Credit

        Procure to Pay:

        Procure to Pay means Procuring Raw Materials, or semi finished goods required to manufacture the final or finished Goods to Paying the Supplier from whom the material was purchased. But this is not just two steps. It involves many steps which are mentioned below:
        1. Oracle Purchasing: You enter Suppliers of different materials and products you want to purchase to  manufacture a finished good that your organization plans to sell.
        2. Oracle Purchasing: You prepare a Request for Quotation (RFQ) and send it to different suppliers to get the best and/or economical price for the product.
        3. Oracle Purchasing: Suppliers sends their quotations and you upload those quotations in Oracle Purchasing to get the best three quotes and further to get the one best quote.
        4. Oracle Purchasing: You prepare a Purchase Order(PO) against the best RFQ to buy the goods from the supplier who quoted the suitable price and sends the PO to that supplier
        5. Oracle Purchasing: The supplier receives the confirmation of purchase from PO and ships the ordered goods. You receive the goods enter a Goods Received Note (GRN) in Oracle Purchasing.
        6. Oracle Inventory / Oracle Assets: It’s up to you whether you want to receive the goods at your head office or you Inventory directly. In either case you move the received goods to your different Raw Material Inventory from Oracle Purchasing to Oracle Inventory and the Item Count increases. If the item is Asset Type then it will move to Oracle Assets at the time of Invoice creation in Oracle Payables.
        7. Oracle General Ledger: Once you move the goods to Oracle Inventory, it sends the Material Accounting to Oracle General Ledger.
        8. Oracle Payables: After this the supplier sends you the invoice for the purchased goods and you Enter or Match the invoice against the PO from Oracle Purchasing in Oracle Payables. As said before, if the item is Asset in nature then it will move to Oracle Asset.
        9. Oracle General Ledger: When you enter the invoice it means that you have created a Liability against that supplier and also you have recorded the expense incurred or asset purchased. Oracle Payables sends the invoice accounting to Oracle General Ledger.
        10. Oracle Payables: You pay the invoice and settle the Liability.
        11. Oracle General Ledger: The liability is settled and your cash movement account is updated.
        12. Oracle Cash Management: As you pay the invoice Oracle Payables sends the payment information to Oracle Cash Management for Bank Reconciliation. Once reconciled, Oracle Cash Management sends the updated Bank/Cash accounting entry to Oracle General Ledger.
        13. Oracle General Ledger: Your cash at bank is updated with actual balance.
        14. Oracle Process Manufacturing(OPM) / Oracle Discrete Manufacturing(ODM): You start the manufacturing of your final product. Both OPM or ODM requests the different raw materials from you inventory organizations and manufactures a finished good.
        15. Oracle Inventory: As the raw materials are issued to OPM and ODM the inventory sends the issuing material accounting to General Ledger and decreases the Item Count from the Raw Material Store. As the finished good is prepared, Oracle Inventory receives the finished good in Finished Good Store and increase the Item Count.

        Following Accounting entries will be generated for P2P:

        • Purchase Requisition creation No entry
        • Purchase Order creation no entry
        • Inventory Receipt:
          Inventory A/c…………….Debit
          AP Accrual A/C………Credit(This A/c We are giving in Financial Option)
        • At the time of Matching the Invoice with Purchase Order
          AP Accrual A/c………….Debit
          Supplier A/c…………..Credit
        • At the time of making payment to supplier
          Supplier A/C…………… Debit
          Bank A/c…………….Credit
        This is how the P2P and O2C cycle works, but this is not the only way, obviously there are many other applications with different cycles.
        /by