, ,

Oracle Advanced Collections

Q ) What is Oracle Advanced Collections ?

Ans) Oracle Advanced collections is the module which deals primarily with collection of your accounts receivable from the customers. Companies sell to customers on credit and these invoices become due as per the invoice terms. Typically you may specify Net45, which means 100% of the invoice amount is due in forty five days from the date of invoice.

However for some reasons the invoice is not paid on time. Some of the reasons could be invoice not sent to the customer, Invoice wrongly billed, Customer does not have funds or become bandrupt, customer does not agree with invoice amount/terms , invoice sent to the wrong address.

Most companies have billers and collectors managing the AR department. Billers are the first level contracts and they contact the customer to collect these outstanding amounts. The contact method may be mail, email, fax or phone call depends on their company terms.

Oracle AR initially had dunning letters functionality to remind the customers of their dues. The dunning level could be adjusted from a gentle reminder to collections advisory.

When the billers are not able to collect the amounts, the cases get transferred to collectors or at a later date to third party agencies for specialized in collection.

However the functionality was limited in scope which did not meet the modern day requirements. Once has built lot of functionality around Advanced Collections to automate the collection process. Advanced collections was a work bench in Receivables which now become a full fledged module with lot of features and automation.

What are the compones of Advanced Collections?
The key components of advanced collections are Collectors/Territories, Scoring Engine, Universal work queue/collectors work queue, Interaction History, Notes Set up,Dunning/Strategies, fulfillment/xml communication.

Scoring
Scoring calculates a score value for an object in the database. You can score following types of objects seeded in collections, or you can create new scoring objects.

Party, Account, or Bill To : The operational data level at which you do business with your customers.

Transaciton: A transaction created in Oracle Receivables, or originating in Oracle.

Loans or Oracle lease management. When determining delinquency status of transaction, Oracle collections creates a delinquency in a collection table. When a transaction is no longer delinquent, the status becomes current.

Case: A group of leasing contracts for a customer sharing the same bill to address, a private label and other contract parameters. Case only applies if you use Oracle lease management.

Concurrent Programs

The scoring engine harness concurrent program runs from one to five scoring engines. In addition, you can add a concurrent program to scoring engine, and when the scoring engine is run by the score engine harness concurrent program, the related concurrent programs also runs. For example you can add the strategy management concurrent program to assigns collections strategies based on the score results.

Scoring Components

The scoring component uses a select statement or a function to return a score value. For
Example, you can ask for the total number of delinquencies for a customer or how long a
Customer has been doing business with your company. Every scoring engine must have
at least one scoring component.
The values calculated by a scoring component are then assigned scores. Score range
numbers can be positive or negative numbers to two decimal places, and must account
for numbers from -99,999,999 to 99,999,999.. In Oracle Collections a higher score is
generally considered good and a lower score is considered bad.

You may have several Scoring components on which to score. For example some of the
components could be: Customer for ….Years, No of Delinquencies, Amt Outstanding,
Amt Overdue.
You will assign different weights to different components. You use Filters to limit the
scope of a scoring engine. Like you want to score transactions from a state, zip code.
You can use scoring engine to score transactions which will determine the status of
transactions delinquent, pre delinquent, Current, Overdue.
You could use another scoring engine to score customers / Account /Bill To.
A scoring engine harness might include both these scoring engines.

Preconfigured Scoring Engines

Preconfigured Scoring Components

eg:

The following tables describe the preconfigured scoring components for use with scoring engines.

Aging bucket line for account

Description : Identifies the oldest aged transaction for the account.

Object : Account

Type Value : Selects nvl(max(abl.bucket_sequence_num),0) from ar_Aging_buckets ab,ar_Aging_bucket_lines abl, IEX_App_preferences_vl iex, ar_payment_schedules arp, IEX_Delinquencies del where abl.aging_bucket_id =ab.aging_bucket_id and ab.bucket_name=iex.preference_value and iex.preference_name=’DUNNING PLAN AGING BUCKET’ and DEL.Payment_schedule_id=arp.payment_schedule_id and (sysdate-arp.due_Date)between abl.days_start and abl.days_to and del.cust_account_id=:account_id

Function : N

Previous Name : Account Aging Bucket Line.

/by
, , ,

Using Roles to Manage Database Access

When your databases has lots of tables, object privileges can become unwieldy and hard to manage. You can simplify the management of privileges with the use of a database object called a role. A role acts in two capacities in the database. First, the role can act as a focal point for grouping the privileges to execute certain tasks. Second, the role can act as a “virtual user” of a database, to which all the object privileges required to execute a certain job function can be granted, such as data entry, manager review, batch processing, and so on


Step 1:  Design and Crate roles is a process that can happen outside the database. A role mechanism can be used to provide authorization. A single person or a group of people can be granted a role or a group of roles. One role can be granted in turn to other roles. By defining different types of roles, administrators can manage access privileges much more easily. You simply sit down and think to yourself, how many different purposes will users be accessing this application for? Once this step is complete, you can create the different roles required to manage the privileges needed for each job function. Let’s say people using the EMP table have two different job roles: those who can query the table and those who can add records to the table. The next step is to create roles that correspond to each activity. This architecture of using roles as a middle layer for granting privileges greatly simplifies administration of user privileges. Let’s take a look at how to create the appropriate roles:
Create role role_name;
Step 2 :  Granting Privileges to Roles
The next step is to grant object privileges to roles. You can accomplish this task using the same command as you would use to grant privileges directly to users—the grant command. The following code block demonstrates how to grant privileges to both our roles:
Grant select on emp to rpt_writer;
Revoking privileges from roles works the same way as it does for users. System privileges are granted and revoked with roles in the exact same way they are with users as well.
Step 3: Granting Roles to Users
Once a role is created and privileges are granted to it, the role can then be granted to users. This step is accomplished with the grant command. Let’s see an example of how this is done:
Grant rpt_writer to turner;
If a role already granted to a user is later granted another privilege, that additional privilege is available to the user immediately. The same statement can be made for privileges revoked from roles already granted to users, too.
Step 4:  Revoking and Dropping Roles
Finally, a role can be revoked by the role’s owner or by a privileged administrative user using the revoke statement, much like revoking privileges:
revoke rpt_writer from turner;
Roles can be deleted from the database using the drop role statement. When a role is dropped, the associated privileges are revoked from the users granted the role. The following code block shows how to eliminate roles from Oracle:
drop role rpt_writer;
Step 5: Modifying Roles if necessary
Let’s say that after we create our roles, we realize that changing records in the EMP table is serious business. To create an added safeguard against someone making a change to the EMP table in error, the DATA_CHANGER role can be altered to require a password by using the alter role identified by statement. Anyone wanting to modify data in EMP with the privileges given via the DATA_CHANGER role must first supply a password. Code for altering the role is shown in the following example:
Alter role data_changer
Identified by highly#secure;
Step 6: Defining User Default Roles
Now user TURNER can execute all the privileges given to him via the RPT_WRITER role, and user FORD can do the same with the privileges given from DATA_CHANGER. Or can he? Recall that we specified that DATA_CHANGER requires a password in order for the grantee to utilize its privileges. Let’s make a little change to FORD’s user ID so that this status will take effect:
alter user ford default role none;
You can use the following keywords in the alter user default role command to define default roles for users: all, all except rolename, and none. Note that users usually cannot issue alter user default role themselves to change their default roles—only a privileged user such as the DBA can do it for them.
Step 7: Enabling the Current Role
FORD knows he is supposed to be able to accomplish this task because he has the DATA_CHANGER role. Then he remembers that this role has a password on it. FORD can use the set role command to enable the DATA_CHANGER role in the following way:
set role data_changer identified by highly#secure;
Now FORD can make the change he needs to make:
SQL> insert into scott.emp (empno, ename, job)
  2  values (1234, ‘SMITHERS’,’MANAGER’);
You must already have been granted the roles that you name in the SET ROLE statement. Also, you can disable all roles with the SET ROLE NONE statement.
/by
, , ,

System/Object Privileges

Privileges are the right to execute particular SQL statements. Two types of privileges exist in Oracle: object privileges and system privileges. Object privileges regulate access to database objects in Oracle, such as querying or changing data in tables and views, creating foreign key constraints and indexes on tables, executing PL/SQL programs, and a handful of other activities. System privileges govern every other type of activity in Oracle, such as connecting to the database, creating tables, creating sequences, creating views, and much, much more.
Privileges are given to users with the grant command, and they are taken away with the revoke command. The ability to grant privileges to other users in the database rests on users who can administer the privileges. The owner of a database object can administer object privileges related to that object, whereas the DBA administers system privileges

Object privileges (Grant Privileges on Tables)
You can grant users various privileges to tables. These privileges can be any combination of select, insert, update, delete, references, alter, and index. Below is an explanation of what each privilege means.

The syntax for granting privileges on a table is:
    grant privileges on object to user;
For example, if you wanted to grant select, insert, update, and delete privileges on a table called suppliers to a user name smithj, you would execute the following statement:
    grant select, insert, update, delete on suppliers to smithj;
You can also use the all keyword to indicate that you wish all permissions to be granted. For example:
    grant all on suppliers to smithj;
The keyword all can be use as a consolidated method for granting object privileges related to a table. Note that all in this context is not a privilege; it is merely a specification for all object privileges for a database object
If you wanted to grant select access on your table to all users, you could grant the privileges to the public keyword. For example:
    grant select on suppliers to public;
Revoke Privileges on Tables
Once you have granted privileges, you may need to revoke some or all of these privileges. To do this, you can execute a revoke command. You can revoke any combination of select, insert, update, delete, references, alter, and index.
The syntax for revoking privileges on a table is:
    revoke privileges on object from user;
//  Grant this on this to this
For example, if you wanted to revoke delete privileges on a table called suppliers from a user named anderson, you would execute the following statement:
    revoke delete on suppliers from anderson;
If you wanted to revoke all privileges on a table, you could use the all keyword. For example:
    revoke all on suppliers from anderson;
If you had granted privileges to public (all users) and you wanted to revoke these privileges, you could execute the following statement:
    revoke all on suppliers from public;

System Privileges

Several categories of system privileges relate to each object. Those categories determine the scope of ability that the privilege grantee will have. The classes or categories of system privileges are listed in this section. Note that in the following subtopics, the privilege itself gives the ability to perform the action against your own database objects, and the any keyword refers to the ability to perform the action against any database object of that type in Oracle.
Database Access These privileges control who accesses the database, when he or she can access it, and what he or she can do regarding management of his or her own session. Privileges include create session, alter session, and restricted session. Users These privileges are used to manage users on the Oracle database. Typically, these privileges are reserved for DBAs or security administrators. Privileges include create user, become user, alter user, and drop user.
Tables You already know that tables store data in the Oracle database. These privileges govern which users can create and maintain tables. The privileges include create table, create any table, alter any table, backup any table, drop any table, lock any table, comment any table, select any table, insert any table, update any table, and delete any table. The create table or create any table privilege also enables you to drop the table. The create table privilege also bestows the ability to create indexes on the table and to run the analyze command on the table. To be able to truncate a table, you must have the drop any table privilege granted to you.
Indexes You already know that indexes are used to improve SQL statement performance on tables containing lots of row data. The privileges include create any index, alter any index, and drop any index. You should note that no create index system privilege exists. The create table privilege also enables you to alter and drop indexes that you own and that are associated with the table.
Synonyms A synonym is a database object that enables you to reference another object by a different name. A public synonym means that the synonym is available to every user in the database for the same purpose. The privileges include create synonym, create any synonym, drop any synonym, create public synonym, and drop public synonym. The create synonym privilege also enables you to alter and drop synonyms that you own.
Views You already know that a view is an object containing a SQL statement that behaves like a table in Oracle, except that it stores no data. The privileges include create view, create any view, and drop any view. The create view privilege also enables you to alter and drop views that you own.
Sequences You already know that a sequence is an object in Oracle that generates numbers according to rules you can define. Privileges include create sequence, create any sequence, alter any sequence, drop any sequence, and select any sequence. The create sequence privilege also enables you to drop sequences that you own.
Roles Roles are objects that can be used for simplified privilege management. You create a role, grant privileges to it, and then grant the role to users. Privileges include create role, drop any role, grant any role, and alter any role.

Transactions These privileges are for resolving in-doubt distributed transactions being processed on the Oracle database. Privileges include force transaction and force any transaction.
PL/SQL There are many different types of PL/SQL blocks in Oracle. These privileges enable you to create, run, and manage those different types of blocks. Privileges include create procedure, create any procedure, alter any procedure, drop any procedure, and execute any procedure. The create procedure privilege also enables you to alter and drop PL/SQL blocks that you own.
Triggers A trigger is a PL/SQL block in Oracle that executes when a specified DML activity occurs on the table to which the trigger is associated. Privileges include create trigger, create any trigger, alter any trigger, and drop any trigger. The create trigger privilege also enables you to alter and drop triggers that you own.
Examples
grant create session to turner;
// Grant this to this
revoke create session from turner;
// Revoke this from this
Open to the Public
Another aspect of privileges and access to the database involves a special user on the database. This user is called PUBLIC. If a system privilege or object privilege is granted to the PUBLIC user, then every user in the database has that privilege. Typically, it is not advised that the DBA should grant many privileges or roles to PUBLIC, because if a privilege or role ever needs to be revoked, then every stored package, procedure, or function will need to be recompiled. Let’s take a look:

GRANT select, update, insert ON emp TO public;
Tip    
Roles can be granted to the PUBLIC user as well. We’ll talk more about roles in the next discussion.
Granting Object Privileges All at Once
The keyword all can be use as a consolidated method for granting object privileges related to a table. Note that all in this context is not a privilege; it is merely a specification for all object privileges for a database object. The following code block shows how all is used:
GRANT ALL ON emp TO turner;

Giving Administrative Ability along with Privileges
When another user grants you a privilege, you then have the ability to perform whatever task the privilege enables you to do. However, you usually can’t grant the privilege to others, nor can you relinquish the privilege without help of the user who granted the privilege to you. If you want some additional power to administer the privilege granted to you, the user who gave you the privilege must also give you administrative control over that privilege. For example, let’s say KING now completely trusts TURNER to manage the creation of tables (a system privilege) and wants to give him access to the EMP table (an object privilege). Therefore, KING tells SCOTT to give TURNER administrative control over both these privileges, as follows:
GRANT CREATE TABLE TO turner WITH ADMIN OPTION;
GRANT SELECT, UPDATE ON turner TO SPANKY WITH GRANT OPTION;
Tip    
The GRANT OPTION is not valid when granting an object privilege to a role.
A system privilege or role can be granted with the ADMIN OPTION. A grantee with this option has several expanded capabilities. The grantee can grant or revoke the system privilege or role to or from any user or other role in the database. However, a user cannot revoke a role from himself. The grantee can further grant the system privilege or role with the ADMIN OPTION. The grantee of a role can alter or drop the role.
Finally, if a role is granted using the with admin option clause, the grantee can alter the role or even remove it. You’ll learn more about roles in the next discussion.
You can grant INSERT, UPDATE, or REFERENCES privileges on individual columns in a table.

Cascading Effects

No cascading effects of revoking system privileges from users occur.
when an object privilege is revoked from a grantor of that privilege, all grantees receiving the privilege from the grantor also lose the privilege. However, in cases where the object privilege involves update, insert, or delete, if subsequent grantees have made changes to data using the privilege, the rows already changed don’t get magically transformed back the way they were before

/by
, , ,

User Access Control

In this chapter, you will learn about and demonstrate knowledge in the following areas of user access and privileges in the Oracle database:

  • Creating users
  • Granting and revoking object privileges
  • Using roles to manage database access

The basic Oracle database security model consists of two parts. The first part consists of password authentication for all users of the Oracle database. Password authentication is available either directly from the Oracle server or from the operating system supporting the Oracle database. When Oracle’s own authentication system is used, password information is stored in Oracle in an encrypted format. The second part of the Oracle security model consists of controlling which database objects a user may access, the level of access a user may have to these objects, and whether a user has the authority to place new objects into the Oracle database. At a high level, these controls are referred to as privileges. We’ll talk about privileges and database access later in this section.
Create Users
The most basic version of the command for creating users defines only the user we want to create, along with a password, as seen in the following example:
Create User USERNAME identified by PASSWORD;
create user turner identified by ike;
Tip :
The user does not have privileges at this point. The DBA can then grant privileges to the user. The privileges determine the actions that the user can do with the objects in the database. Also, usernames can be up to 30 characters in length and can contain alphanumeric characters as well as the $, #, and _ characters.


/by
, , ,

Create DATABASE LINK

A database link is a schema object in one database that enables you to access objects on another database. The other database need not be an Oracle Database system. However, to access non-Oracle systems you must use Oracle Heterogeneous Services.
You might want to create a database link, for instance, if you want the data in a remote database updated when the local database is updated. Here’s how to accomplish this:
The first thing you need to do is to create a database link pointing to the other location. The database link can be created with a command similar to the following:
    CREATE [SHARED | PUBLIC] DATABASE LINK <link-name>
    [CONNECT TO <username> IDENTIFIED BY <password>]
    [USING <connect-string>];
To create a fixed user database link, a username and password to connect with must be specified. For example:
    CREATE PUBLIC DATABASE LINK BASING
    CONNECT TO USRCRY IDENTIFIED BY CRYPWD
    USING BASING_ORA;
    Select count(*) from table@basing;
You’ll want to give the database link a better name, use the appropriate userid/password to connect to the remote database, and configure your TNSNAMES.ORA file with a TNS alias to point to that database.
/by