Using CASE WHEN Oracle SQL

An example demonstrate using CASE WHEN in Oracle Sql.

CREATE TABLE DEPT(
DEPT_ID NUMBER,
DEPT_NAME VARCHAR2(100));

ALTER TABLE DEPT ADD CONSTRAINT DEPT_PK PRIMARY KEY (DEPT_ID);

CREATE TABLE EMP(
EMP_ID NUMBER,
EMP_NAME VARCHAR2(100),
DEPT_ID NUMBER);


ALTER TABLE EMP ADD CONSTRAINT EMP_PK PRIMARY KEY (EMP_ID);

ALTER TABLE EMP ADD CONSTRAINT EMP_FK FOREIGN KEY (DEPT_ID)
REFERENCES DEPT(DEPT_ID);


INSERT INTO DEPT VALUES(1,'Human Resource');

INSERT INTO EMP VALUES(100,'Employee One',1);

INSERT INTO EMP VALUES(101,'Employee Two',null);

COMMIT;

SELECT A.EMP_ID,A.EMP_NAME,A.DEPT_ID,
CASE
WHEN A.DEPT_ID IS NOT NULL THEN
(SELECT DEPT_NAME FROM DEPT WHERE DEPT_ID=A.DEPT_ID)
ELSE
''
END DEPT_NAME
FROM EMP A

Object Programming in Oracle

This article demonstrate how to code object oriented program in oracle. An employee object type is created with two properties EMP_ID and EMP_NAME. Another type TBL_EMP is created used to hold employees. Next employee table named emp is created that has same properties as object OBJ_EMP.

The procedure INSERT_EMP takes TBL_EMP type as its only IN parameter. If the count is greater than zero the program inserts into emp table.


CREATE OR REPLACE TYPE OBJ_EMP AS OBJECT (EMP_ID NUMBER , EMP_NAME VARCHAR2(100));

CREATE OR REPLACE TYPE TBL_EMP AS TABLE OF OBJ_EMP;

create table emp (
emp_id number,
emp_name varchar2(100)
);

alter table emp add constraint emp_pk primary key (emp_id);

CREATE OR REPLACE PROCEDURE INSERT_EMP( P_EMP TBL_EMP)
IS
v_obj_emp OBJ_EMP;
BEGIN

--insert into emp select * from table(p_emp);

IF P_EMP.COUNT > 0 THEN
FOR i IN 1 .. P_EMP.COUNT LOOP
v_obj_emp := P_EMP( i );

INSERT INTO emp ( emp_id,emp_name )
VALUES ( v_obj_emp.emp_id,v_obj_emp.emp_name);
END LOOP;
END IF;


END;
/


The INSERT_EMP procedure is invoked from anonymous block of plsql below. Here an employee object is intialized and table of employee type created. Employee type object t_emp is extend to create a row in the table object. The index of table object start with one which we assign to p_obj_emp object initially created. The t_emp table object is extended again and assigned to newly intialized p_obj_emp. Finally, INSERT_EMP procedure is invoked with t_emp object as parameter.


declare
p_obj_emp obj_emp := new obj_emp(1,'mahesh prajap');
t_emp tbl_emp := new tbl_emp();

begin
t_emp.extend();
t_emp(1):=p_obj_emp;

t_emp.extend();
p_obj_emp.emp_id:=2;
p_obj_emp.emp_name:='aarav prajap';
t_emp(2):=p_obj_emp;
INSERT_EMP(t_emp);
end;
/


The object programming concept in oracle can be evenly used with object oriented programming languages like java. List of objects can be passed directly to oracle procedure so that we can manipulate those passed objects in oracle as tables. There will be need and requirement in business logic that whole object be passed as tables and oracle plsql procedure takes control over the business procedures. The single object procedure call makes multiple call to procedure that makes more connection to oracle database and much of the time may be spent on such database connections.

Document Management System Guide Part 2

Registered user can login to the application through their user name and password. The URL can be obtained from the system administrator of Document Management System.

Customer Registration Customers involved in the business process can be registered. This helps in generating report based on the customer identity.

Document Transaction Steps and flow diagram 1. Document Entry. 2. Document Forward. 3. Document Review. 4. Document Authorize. 5. Document Reject

Document Transaction Generation This is the document transaction generation module. In the screen shot Document Type, Customer list and forwarder lists is auto populated from the initial setups. User can select appropriate values from the list to generate document transaction. A document may contain multiple files as shown below in the screen shot. Some Useful Notes: 1. Document Type is user defined and parameterized document. It helps to differentiate the type of document generated. As per the configuration, a document number may contain document short string, document date and document serial number.eg AO060720100000000019. 2. “Forward To” user field is the default user the document to be forwarded to. It must not be empty. The block “User Forwarder List” contain list of users in the priority list to be forwarded one by one from left side simultaneously. If a default user, that is user in “Forward To” field do not respond within DOCUMENT TIME OUT (DOCUMENT_TIMEOUT) parameter default set to 30 minutes, then the document is revoked from “Forward To” user (admin) and auto forwarded to first user in User “Forwarded List” (surendras). If “surendars” do not respond within DOCUMENT TIME OUT, then the document is revoked from the user and auto forwarded to user “dhrubab”. During the document auto forward process, whenever any one of the user (admin, surendras, dhrubab or malikad) respond, the users in the forwarder chain are cancelled from the priority list. Then the document is processed as per the action (FORWARD, REVOKE, AUTHORIZE, REJECT) taken by the forwarder user. 3. Document No: It is system generated number to uniquely identify the document being generated. Document number generated depends upon the configuration of document type definition. A Document Number can contain multiples related files with the document. Screen shot show a Bank Guarantee document that contain citizen ship certificate and guarantee fill up form in portable document format. 4. + sign button indicates the addition of the file in the document to be generated. Clik on the + sign button to add new row to the document. 5. – sign button on each row indicates the deletion of the file from the document. Click on the –sign button to delete the current row from the document. 6. Remarks on the each document can be included in the remarks field that should be informative to the user receiving the document. Remarks are also show on document flow report. 7. The application supports files names in portable document format (PDF) for preview during document processing. Files in other formats are not available for preview but they can be downloaded to the local computer and opened as per user demand. It is suggested to process files in portable document format as the time to generate preview for portable document format is much lesser than file downloads. Avoid processing document files other than portable document format (PDF) to minimize bandwidth usage. 8. Extensions of filenames are mandatory in “File Name” field. The system returns error and file could not be transmitted if extension are avoided. For instance, if only name of file “citizen” is placed in “File Name” field, application would return error during file upload process. So, it is required to enter extension of the file with their file name that is “citizen.pdf” in “File Name” field. 9. Files must be stored in Application Home Directory default to “C:\docman”. The default Application Home Directory can be changed upon request to the administrator. 10. “Save” Button saves the files and generates a unique Document Number for transaction. 11. Email alert is sent to the default user (admin) upon Document Transaction Generation. 12. Refer to the screen shot shown below. Screen shot of Document Transaction Entry:

Document Forward/Revoke/Authorize/Reject The Document Authorize/Forward/Reject/Review grid shows the list of document forwarded to login user for approval. Click on the record to display the details of the document. Document file in portable document format (PDF) can be previewed while other file formats need to be downloaded to the local computer. As per the requirement, a document can be processed for: 1. REVIEW document. 2. FORWARD for next level approval. 3. AUTHORIZE document. 4. REJECT document.

User Actions: 1. REVIEW Document Whenever modification is required in the document, it is sent for review. User performing review can be same user that forward the document or any other user in the user list. 2. FORWARD Document Whenever approval is sought from next authority level, then the document is forwarded to the respective user. 3. AUTHORIZE Document Permit the document to carry on the business process. 4. REJECT Documents Disallow to process the business activity. A remark is mandatory while processing the document. The document recipient can track the state of the document and business process from the remarks as well. So, remarks must be precise and clear that can be taken as reference to authenticate the document. Remarks are also shown on document flow report. Screen shot of File preview

Invalid Documents: During document transaction processing, if file name specified in file name field does not exist in Application Home Directory, the resulting document number is marked as Invalid Document. Invalid Documents can be processed from Invalid Document menu from “Invalid Document” Grid as show in the screen shot below. Respective files can be placed in Application Home Directory and process “Upload File” button to validate the resulting document.

Email Notification and Alerts Email Notification in the form of alerts is sent to the user that have been forwarded a document or sent for review. During AUTHORIZE and REJECT of document, email is sent to all the users involved for the document processing. This will help user to know the state of the document as well. Background process for Auto Email Notification can be started and stopped from Email Notify menu.

User Reports Reports are embedded in the application and rendered in Portable Document Format (pdf). Screen shot of List of Documents

Screen shot of Document Flow Report