Posts
DENSE_RANK Function:
Returns the rank of a value in a group of values.
A built in analytic function which is used to rank a record within a group of rows.
Return type is number and serves for both aggregate and analytic purpose in SQL.
Rows with equal values for the ranking criteria receive the same rank.
The ranks are consecutive. No ranks are skipped if there are ranks with multiple items.
Examples:
1. Query to return the dense_rank for a $50000 salary(Single Column DENSE_RANK)
SELECT DENSE_RANK(50000) WITHIN GROUP(ORDER BY salary DESC NULLS LAST) SAL_RANKFROM employees;
2. Query to return the dense_rank for an employee with a salary of $50,000
and a commission of 10%$(Multiple Column DENSE_RANK)
SELECT DENSE_RANK(10,50000) WITHIN...
Friday, June 30, 2017
RANK In Oracle/ PL-SQL
RANK Function:
Returns the rank of a value in a group of values.
A built in analytic function which is used to rank a record within a group of rows.
Return type is number and serves for both aggregate and analytic purpose in SQL.
Rows with equal values for the ranking criteria receive the same rank.
Ties are assigned the same rank, with the next ranking(s) skipped. So, if we have 3 items at rank 2, the next rank listed would be ranked 5.
Examples:
1. Query to return the rank for a $50000 salary(Single Column RANK)
SELECT RANK(50000) WITHIN GROUP(ORDER BY salary DESC NULLS LAST) SAL_RANKFROM employees;
2. Query to return the rank for an employee with a salary of $50,000 and a commission of 10%$(Multiple Column RANK)
SELECT RANK(.10,50000) WITHIN...
Friday, June 23, 2017
Bulk Collect - Save Exceptions
The SAVE EXCEPTIONS clause will record any exception during the bulk operation, and still continue processing.
BULK COLLECT construct is used to work with batches of data rather than single record at a time. Whenever we have to deal with large amount of data, bulk collect provides considerable performance improvement.
Declare
cursor cur_emp
is
select * from Emp;
type array is table of c%rowtype;
l_data array;
dml_errors EXCEPTION;
PRAGMA exception_init(dml_errors, -24381);
l_errors number;
l_errno number;
l_msg varchar2(4000);
l_idx number;
Begin
open cur_emp;
loop
fetch cur_emp bulk collect into l_data limit 100;
begin forall i in 1 .. l_data.count...
Approval Management(AME) in Oracle Order Management
Oracle Approvals Management (AME) is a self‐service web application that enables us to define business rules governing the process for approving transactions in Oracle applications that have integrated AME.
In R12.2.2, Order Management integrates with Oracle Approval Management (AME) to leverage the robust capabilities of the approval rules engine to dynamically determine the list of approvers for Quotes, Sales Agreements and Returns (RMA).
Order Management has been integrated with Oracle Approval Management for the workflow "Negotiation Flow - Generic with Approval" and "Order Flow - Mixed or Return with Approval" to provide...
Negotiation Quotes - Offer Expiration Date and send notification prior to default value
The Flow for the Quote Offer Expiration process is like below:
->We can limit the validity of the Quote to a specific period and assign an offer expiration date.
->The workflow updates status at expiration, and expired transactions may be purged. This date can be manually entered, or defaulted by using defaulting rules and selecting a defaulting condition for negotiation.
->If we select the system variable and enter sysdate+7, the expiration date will default 7 days from the current system date. Notifications can be generated prior to expiration.
->An offer expiration date is necessary to ensure successful closure...
Quote Negotiation Workflow- Quote To Order
There are 3 seeded negotiation flows:
Negotiation Flow - Simple: This workflow does not require any approvals nor customer acceptance. However the quote can either expire or get lost if it does not progress to being converted to an order.
Negotiation Flow—Generic: Simple negotiation flow, without approval. Prepares quote document, get customer final acceptance, convert quote to the Sales Order.
Negotiation Flow—Generic with Approval: Flow with Approval. Prepare quote document, get management approval, get customer final acceptance, and convert the quote to an order.
These flows can be associated to transaction types for both...
Thursday, June 22, 2017
Oracle Regular Expression
Regular expressions specify patterns to search for in string data using standardized syntax conventions. A regular expression can specify complex patterns of character sequences. For example, the following regular expression:
a(b|c)d
searches for the pattern: 'a', followed by either 'b' or 'c', then followed by 'd'. This regular expression matches both 'abd' and 'acd'.
Oracle Database 11g offers five regular expression functions as below:
REGEXP_LIKE
REGEXP_SUBSTR
REGEXP_REPLACE
REGEXP_INSTR
REGEXP_COUNT
REGEXP_LIKE(source, regexp, modes) :
This function searches a character column for a pattern.
source parameter - is the string or column the regex should be matched against.
regexp parameter - is a string with the regular expression.
modes parameter...
Saturday, June 17, 2017
Process Flow for a Service order in Order Management
There might be some requirements, where we need to bill the customer for the products we sold. But we don't have to ship the product.
Example:
The customer bought a Warranty for a product.
The customer has requested for a service of a product
Oracle Order Management enables us to utilize Oracle Workflow to manage the service lines in a sales order. Steps to achieve the above requirement.
1. Create a service item
2. Create a order type with the line type as BILL ONLY
3. Create a sales order with the order type created above
4. Process the sales order to create...
Friday, June 16, 2017
Create a Service Item in Oracle
To create a service item:
Open Oracle Inventory and navigate to Inventory. Select Items, and then select Master Items.
If prompted, enter an Organization.The Master Items window appears.
Enter an item.
Enter a description.
From the Main tab:
Select the User Item Type, Service Pgm / Warranty.
Select a unit of measure (UOM), from the Primary LOV.Note: You set up a service using a time UOM, such as Year, not a quantity UOM, such as Each.
Select the Order Management tab:
Select the Customer Ordered check box.The Customer Orders Enabled check box is automatically selected.
Select the OE...
Thursday, June 15, 2017
Query - Get Supply-Demand for an Item for an org (As in Oracle Supply Demand Form)
--Reserved Sales Orders
SELECT
d.requirement_date Required_date ,
ml.meaning Supply_Demand_Type,
to_char(ooha.order_number) Identifier,
-1 * ( d.primary_uom_quantity - GREATEST (NVL (d.reservation_quantity, 0), d.completed_quantity) ) quantity,
oola.line_id,
oola.request_date,
wda.delivery_id
FROM
mtl_parameters p,
mtl_system_items i,
bom_calendar_dates c,
mtl_demand d,
mfg_lookups ml,
(
SELECT
DECODE (demand_source_type, 2, DECODE (reservation_type, 1, 2, 3, DECODE
(supply_source_type, 5, 23, 31), 9 ), 8, DECODE (reservation_type, 1, 21,
22), demand_source_type) supply_demand_source_type,
demand_id
...
