Infolinks

Friday, 29 January 2016

CREATE TABLE EMP DEPT

CREATE TABLE EMP
(EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
DEPTNO NUMBER(2));

INSERT INTO EMP VALUES
(7369, 'SMITH', 'CLERK', 7902,
TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20);
INSERT INTO EMP VALUES
(7499, 'ALLEN', 'SALESMAN', 7698,
TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30);
INSERT INTO EMP VALUES
(7521, 'WARD', 'SALESMAN', 7698,
TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30);
INSERT INTO EMP VALUES
(7566, 'JONES', 'MANAGER', 7839,
TO_DATE('2-APR-1981', 'DD-MON-YYYY'), 2975, NULL, 20);
INSERT INTO EMP VALUES
(7654, 'MARTIN', 'SALESMAN', 7698,
TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
INSERT INTO EMP VALUES
(7698, 'BLAKE', 'MANAGER', 7839,
TO_DATE('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30);
INSERT INTO EMP VALUES
(7782, 'CLARK', 'MANAGER', 7839,
TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10);
INSERT INTO EMP VALUES
(7788, 'SCOTT', 'ANALYST', 7566,
TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES
(7839, 'KING', 'PRESIDENT', NULL,
TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
INSERT INTO EMP VALUES
(7844, 'TURNER', 'SALESMAN', 7698,
TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500, 0, 30);
INSERT INTO EMP VALUES
(7876, 'ADAMS', 'CLERK', 7788,
TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
INSERT INTO EMP VALUES
(7900, 'JAMES', 'CLERK', 7698,
TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 950, NULL, 30);
INSERT INTO EMP VALUES
(7902, 'FORD', 'ANALYST', 7566,
TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES
(7934, 'MILLER', 'CLERK', 7782,
TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);

CREATE TABLE DEPT
(DEPTNO NUMBER(2),
DNAME VARCHAR2(14),
LOC VARCHAR2(13) );

INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');

CREATE TABLE BONUS
(ENAME VARCHAR2(10),
JOB VARCHAR2(9),
SAL NUMBER,
COMM NUMBER);

CREATE TABLE SALGRADE
(GRADE NUMBER,
LOSAL NUMBER,
HISAL NUMBER);

INSERT INTO SALGRADE VALUES (1, 700, 1200);
INSERT INTO SALGRADE VALUES (2, 1201, 1400);
INSERT INTO SALGRADE VALUES (3, 1401, 2000);
INSERT INTO SALGRADE VALUES (4, 2001, 3000);
INSERT INTO SALGRADE VALUES (5, 3001, 9999);

CREATE TABLE DUMMY
(DUMMY NUMBER);

INSERT INTO DUMMY VALUES (0);

COMMIT;

Codd rules

Rule 1: The information rule:

    All information in the database is to be represented in one and only one way,
ie in the form of rows and columns.



Rule 2: The guaranteed access rule:

    All data must be accessible.
   If you are able to store,  you should able to retrieve.

Rule 3: Systematic treatment of null values:

    The DBMS must allow each field to remain null (or empty). Specifically, it must support a representation of "missing information and inapplicable information" that is systematic.

Treatment of null values should be same irrespective of datatype.

Rule 4: Active online catalog based on the relational model:

     That is, users must be able to access the database's structure (catalog) using the same query language that they use to access the database's data.


Rule 5: The comprehensive data sublanguage rule:

   
Rule 6: The view updating rule:

    All views that are theoretically updatable must be updatable by the system.


Rule 7: High-level insert, update, and delete:

    The system must support set-at-a-time insert, update, and delete operators.

 This rule states that insert, update, and delete operations should be supported for any retrievable set rather than just for a single row in a single table.

Rule 8: Physical data independence:

    Changes to the physical level (how the data is stored, whether in arrays or linked lists etc.) must not require a change to an application based on the structure.

Rule 9: Logical data independence:

    Changes to the logical level (tables, columns, rows, and so on) must not require a change to an application based on the structure. Logical data independence is more difficult to achieve than physical data independence.

Rule 10: Integrity independence:

    Integrity constraints must be specified separately from application programs and stored in the catalog. It must be possible to change such constraints as and when appropriate without unnecessarily affecting existing applications.

Rule 11: Distribution independence:

   
Rule 12: The nonsubversion rule:

    If the system provides a low-level (record-at-a-time) interface, then that interface should not subvert the system,





Clustering

Clustering :

It is a mechanism to bind the data together.. to achieve clustering clusters are used...

syntax for creating a cluster :

create cluster <cluster_name>
( cluster_column <data_type>  )

ex :
create cluster deptno_cluster
( deptno number );

Syntax for Creating a cluster Table :

create table <table_name>
(
column_name <data_type>,
.
.
cluster_column <data_type>
)cluster cluster_name( cluster_column )

create table emp_cluster_tab
(
empno number,
ename varchar2(10),
sal number,
deptno number
)cluster deptno_cluster( deptno )

Note : Cluster tables cant be used before indexing the cluster...

Indexes :

Usage : It is used for improving the performance of retrieval of data whenever the search condition is specified is an indexed column..

syntax :

create index <index_name>
on table table_name( column_name )

create index ename_ind on table emp( ename )

select * from emp where ename = 'SMITH'

Rules for using Indexes :

* Table should contain more than 20000 records

* Table row's should be large with more no. of null values in a column..

* If the table's data is more and only 2-4% of its data should be retrieved

* If a column is very frequently used in a where or join condition..

Note :
* The info. about the indexes can be retrieved from user_indexes..

select * from user_indexes where index_name like 'ENAME_IND'

* To retrieve the info. about the columns on which indexes are defined can be retrieved from user_ind_columns..

select column_name from user_ind_columns
where table_name = 'EMP';

unique index : If a column is defined with an unique index then it will implictly define an unique constraint on that column..

syntax :
create unique index <index_name>
on table table_name( column )

create unique index eno_ind on emp( empno )

Cluster Index : To index a cluster it is used..
syntax :
create index <index_name>
on cluster cluster_name;

create index deptno_cluster_index
on cluster deptno_cluster;


insert into emp_cluster_tab
values( 101,'sekhar',15000,10 );

select * from emp_cluster_tab;

Note :
* The information about the clusters can be retrieved from user_clusters..

Abstract Datatypes

Abstract Datatypes:

   Abstract Datatypes are datatypes that consist of one or more subtypes. Rather than being constrained to the standard Oracle datatypes of NUMBER, DATA, and VARCHAR2, abstract datatypes can more accurately describe your data.

Ex:-
     create type ADDRESS_TY as object
      (Street  VARCHAR2(20),
       City    VARCHAR2(10),
       State   CHAR(10),
       Pin     NUMBER);

     Now the datatype ADDRESS_TY is been created. You can use this datatype alongwith other datatypes. For ex. will create a standard datatype for people which contain Name and address of a person.

   create type PERSON_TY as object
    ( Name VARCHAR2(20),
      Address ADDRESS_TY);

Now PERSON_TY contains Name and address of a person  we can use this to create table.

    You can't insert data into PERSON_TY. The reason is straightforward: A datatype describes data, it does not store data. You cannot store data in a NUMBER datatype, and you cannot store data in a datatype that you define, either. To store data, you have to create a table that uses your datatype.

The following command create a table name CUSTOMER.

  create table CUSTOMER
  (Customer_ID NUMBER,
   Person    PERSON_TY);
 
 To Insert rows into CUSTOMER do following

  insert into CUSTOMER VALUES
   (1, PERSON_TY('HARI',ADDRESS_TY('#102 Lokhand wala','mumbai','MH',10101);
 
to select data from CUSTOMER table

    Select customer_ID, c.person.name
     from CUSTOMER C;

  First, Note that the access of the datatype's attribute requires the use of a table alias. A table alias, also known as a correlation variable, allows Oracle to resolve any ambiguity regarding the name of the object being selected. As a column name, Person.Name points to the Name attribute within the PERSON_TY datatype. The format for the column name is
     correlation.Column.Attribute

   to select Street which is availabe in ADDRESS_TY
 correlation.Column.column.Attribute

C.PERSON.ADDRESS.STREET  

Autonomous Transactions

Autonomous Transactions:
------------------------------------------

The easiest way to understand autonomous transactions is to see them in action.

CREATE TABLE at_test (
      id               NUMBER            NOT NULL,
     description  VARCHAR2(50)  NOT NULL
   );

INSERT INTO at_test (id, description) VALUES (1, 'Description for 1');
INSERT INTO at_test (id, description) VALUES (2, 'Description for 2');

SELECT * FROM at_test;

        ID DESCRIPTION
---------- --------------------------------------------------
         1 Description for 1
         2 Description for 2

2 rows selected.


Next, we insert another 8 rows using an anonymous block declared as an autonomous transaction, which contains a commit statement.



DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  FOR i IN 3 .. 10 LOOP
    INSERT INTO at_test (id, description)
    VALUES (i, 'Description for ' || i);
  END LOOP;
  COMMIT;
END;
/

PL/SQL procedure successfully completed.

SELECT * FROM at_test;

        ID DESCRIPTION
---------- --------------------------------------------------
         1 Description for 1
         2 Description for 2
         3 Description for 3
         4 Description for 4
         5 Description for 5
         6 Description for 6
         7 Description for 7
         8 Description for 8
         9 Description for 9
        10 Description for 10

10 rows selected.


As expected, we now have 10 rows in the table. If we now issue a rollback statement we get the following result.


ROLLBACK;
SELECT * FROM at_test;

        ID DESCRIPTION
---------- --------------------------------------------------
         3 Description for 3
         4 Description for 4
         5 Description for 5
         6 Description for 6
         7 Description for 7
         8 Description for 8
         9 Description for 9
        10 Description for 10

8 rows selected.


The 2 rows inserted by our current session (transaction) have been rolled back, while the rows inserted by the autonomous transactions remain.

The presence of the PRAGMA AUTONOMOUS_TRANSACTION compiler directive made the anonymous block run in its own transaction, so the internal commit statement did not affect the calling session.

Major changes in AP - R12

1. Supplier Creation and Maintenance:

In Oracle R12, Suppliers are now part of TCA(Trading Community Architecture), where suppliers are defined as parties and supplier sites as party sites. Each supplier, supplier sites and contact details can be defined globally in TCA level. It means, any changes to supplier/supplier sites/address reflects across Operating Units with out really updating every OU and all the supplier information can be leveraged by multiple Operating Units. Supplier bank information can also be handled at TCA level.

In Release 12, there is a new user interface for suppliers entry and maintenance. When user opens Supplier entry form, Oracle automatically redirects the page to TCA with a jsp form.

Impact of Upgrade:

1. During the upgrade from 11i to R12, TCA party and party sites records are created and updated in TCA for all the existing suppliers and supplier sites.              
2. TCA data model requires country and address information for suppliers, which will be used by E-Business Tax. If there is no country or address information specified to a supplier site, Oracle automatically infer the data based on most frequently used site from the supplier's historical transactions. 
3.Oracle Payables reviews the supplier sites and determines duplicates, based on         supplier,address,city,province, state, country, zip and language and creates only one party site for each distinct supplier site address.
4. In 11i the employees are associated with Payables as internal suppliers to create the payments for their expense reports. During migration of data, for internal suppliers, only party will be created in TCA and employee address will not not be migrated to party site and the data remains in HRMS records for data security.
5. As the supplier objects are moved from AP to TCA, the tables related to supplier, supplier site, and supplier address are obsolete in R12 and views are created with the obsoleted table names to link the information from old tables with information in TCA.

Table Changes:


AP_SUPPLIERS, AP_SUPPLIER_SITES_ALL, AP_SUPPLIER_CONTACTS, AP_SUPPLIER_INT_REJECTIONS are the new tables introduced to replace obsoleted tables PO_VENDORS, PO_VENDOR_SITES_ALL, PO_VENDOR_CONTACTS

2. Invoice Entry & Cancellation:


In Oracle R12, there is an additional level of detail called Invoice lines between Invoice Header and invoice distribution to capture the data related to Items, freight, miscellaneous, Tax, Prepayment or withholding tax. An invoice line can have one or more invoice distributions.
                                                                                                                                                               With the introduction of invoice lines, there is lot of significant improvement in the data flow to other modules which are integrated with Payables.
For example: 1. Fixed Assets use the data stored in the Invoice lines fields such as Manufacturer, Model,  Serial Number, Warranty Number, Asset Book and Asset Category to track the assets. 
                     2. E-Business tax takes information from the AP invoice lines and creates summary and detail  tax lines in E-Business tax repository.
                     3. Sub ledger Accounting require the invoice distributions should be stored at the maximum level of detail. With additional level in the invoice hierarchy, data flow will be improved to the Sub ledger accounting.

Impact of Upgrade:

During a upgrade one invoice line will be created for every distribution for existing data in 11i.


Cancellation of Invoices:

An invoice line may be discarded on its own or as a part of invoice cancellation. A discarded invoice line will have an amount as 0, marked as discarded and creates a negative respective transaction in the distributions. If a line is discarded as a part of invoice cancellation it will be marked as cancelled.

Recurring Invoice:

In Oracle R12,For Recurring invoices,  invoice definition and line definition are introduced in place of template definition.  In line definition additional information such as Item description, Manufacturer and Model number are included. This will be helpful in smooth data flow to the integrated modules. In addition to Invoice definition and line definition few more tabs are introduced namely Tax, Control and Payment. E-business tax uses the data in Tax ad Payment module uses the data from payment tab on recurring invoices window.

Table Changes:

AP_INVOICE_LINES_ALL              New table introduced to represent the data stored in invoice lines
AP_CHRG_ALLOCATIONS_ALL    Obsolete and now distributions itself represent the allocation of charges 


3. Payment Process:

In R12, Oracle Payments is a new module introduced to centralize the payment process into one payment engine, so that multiple applications can leverage the same functionality. In R12 Payables, user can find Payments manager under payment entry, which will re-direct the page to a OAF page. So unlike in 11i, user need to use Payables Payments dashboard to begin the payment process.

Build Payment Batch: 


The first step in the payment process is to submit a Payment Process Request(PPR), which replaces Build payment Batch in Release 11i. The Payments Process Request form enable users to set invoice selection criteria. This form consists of various tabs such as Scheduled payment Selection Criteria, Payment Attributes, User Rates, Processing, Validation failure result and additional Information to specify the required criteria for payment processing.

Format Payment Batch:

Payment Process Request will be formatted automatically after submitting the Payment Process Request form. Users can check the status of PPR under Payment Process Request status.

Confirm Payment Batch: 

When users click on start action icon, the form takes user to Payment Process Instructions tab, where users can confirm the batch if it is printed correctly, by clicking on 'Record Print Status' button. Here the module name changes from Payables to Payments.


>  There are no different processes  for payment and payment batch in R12. Same screen and process can be used for both .
> If user selects Payee under Scheduled payment Selection Criteria tab in the Payment Process Request  then  single payment will be done for a selected payee. If user leaves it blank, payment will be done to all suppliers.

> All Payment related setups are now moved to Oracle Payments.


4. Create Payment Instructions:

In R12 there are two programs to create payment instructions:

Create Electronic Payment Instructions    for Electronic Payments
Create Printed Payment Instructions        for Check payments

We can run these programs automatically or manually by selecting an option in the Build Payment process.

5. Transmit Payment File to Banks:

As part of R12 , there is a check box in the "Payment System" set up . When enabled the check box " Automatic Transmit of File" - This would transfer the file to bank automatically.

There are few new set ups that needs to be done in the R12. 
1. Payment System - External organization ( Internal bank) needs to be set up.
2. Transmission Configuration - This includes the ftp details and how to pull the flat file from a particular location. 

6. Banks:

> Bank accounts are moved into TCA architecture which needs to be defined in R12
> Cash Management now owns Banks Set up Definition.
> All the internal bank accounts of 11.5.10, will be migrated into Centralized Bank model automatically during the upgrade.


7. Transfer journal entries to General Ledger:


Users can transfer journal entries to General Ledger in two ways.                                        
1. Run Create Accounting Program with Transfer to GL option as Yes.                           
2. Run Transfer Journal Entries to GL after running Create Accounting Program with Transfer to GL parameter set to NO or after create accounting online in Final mode.

Create Accounting Program:

Payables Accounting Process is obsolete in R12 and is replaced with Create Accounting program. The create accounting program creates sub ledger journal entries by processing eligible accounting events. The Create Accounting program uses application accounting definitions, which are created in Accounting Method Builder(AMB) to create sub ledger journal entries.

The Create Accounting program
1. Creates and validates sub ledger journal entries.
2. Transfers the final journal entries in the current batch run to General Ledger and starts General Ledger Posting Process.
3. Generates Sub ledger Accounting Program Report.

The create Accounting program creates journal entries in three modes.

Draft: Users can create the journal entries in SLA in draft mode and can review and make changes again.

Final: With this option users can create journal entries in SLA which can not be modified again. Here users need to run Transfer Journal Entries to GL to post the subledger journal entries to GL.

Final Post: With this options users can create the journal entries and post to GL with out using Transfer Journal Entries to GL program.

Transfer Journal Entries to GL:


Payables Transfer to General Ledger program is obsolete in R12 and is replaced with Transfer Journal Entries to GL. The Transfer Journal entries to GL program enables users to transfer eligible journal entries to GL, including those from the previous run that have not yet been transferred to GL.

This program is used when the Create Accounting program is run with Transfer to GL parameter set to NO or after create accounting online in Final mode.
                                                                                                                                                                       
8. Invoice Approval Workflow:

Invoice work flow has been enhanced to include line level invoice approval. Based on rules setup for Payables Invoice Approval Transaction Type in AME, the work flow determines if the invoice Header (invoice document) needs approval or invoice lines needs approval or both. If both invoice lines and document need approval, all the lines of the invoice requiring approval must be approved before the invoice document can be approved.                                                                   
                                                                               
The approval  status both at header level as well as line level shows whether the invoice document or invoice lines need approval or not.      

Setup:

> The item class provided in defining rule in AME determines whether this rule effects the invoice document approval or invoice line approval.
> If item class is given as Header, this rule govern the invoice document approval and if given as line item, would govern the invoice line approval. Remaining all setups in AME are same as in 11i.

Changes in Tables: 

In R12, there are 2 history tables for invoice approval.                                                                                                
   1.     AP_APINV_APPROVERS                Stores the line level approval history             
   2.     AP_INV_APRVL_HIST_ALL              Stores both Header and line level history.
Source for the content is take from:  
http://www.oracleerpappsguide.com/2012/01/major-changes-in-ap-r12.html

Wednesday, 22 July 2015

Workflow Notifications - Override Email address for all Notifications

Some days ago while i was doing the Oracle Alerts for the first time..even though i gave the email address in the Alert i used to not get the email alerts..After investigation i found that since it was a DEV(TESTING) instance the email address was over ridden, so that the user would not get the emails from DEV instance.. So to test the email Alerts for Workflow notifications we need to over-ride the email address in the Workflow.

Please follow the following steps to over-ride the email address.

1. Login to Oracle Applications



2. Navigate to following navigation: System Administrator -> Oracle Applications Manager -> Workflow Manager

3. Once you navigate the below screen would appear.



4. Please click on the green 'Up' button besides the 'Service Component'.

5. Once you click the below screen would appear.


6. To over rider the email address you need to press the 'Set Override Address'

5. Once you click the below screen would appear.


6. Please enter the new email address in the 'New Override Address' and press Submit button. Once you press the submit button an email notification with an id would be sent to the new override email address. Once we enter the Oracle ID in the prompt the email address would be over ridden and all the alerts and notification would be sent to the email address.

Note: We should never leave email address blank in the development environment.