WORKFLOW

Components:

Oracle Workflow is a series of tools designed to facilitate the creation and management of business process models. Oracle Workflow is comprised of:

• Workflow engine – implements the workflow process definitions at runtime monitoring the states and coordinating the routing of process activities
• The workflow engine is a database package containing procedures run on a schedule. The workflow engine recognizes activities meeting specific criteria and only operates on those activities.
• Through the use of standard APIs, background engines can be scheduled to target activities meeting other specified criteria, such as deferred and stuck status activities.
• Business Event System – utilizes Oracle Advanced Queuing infrastructure to communicate information between systems.Employs agents watching for subscriptions to user-defined events.Event information propagated across systems via queues using specified payload (defined data structure).
• Workflow Monitor – allows monitoring and administration of defined workflows via standard web browser capable of supporting Java
• Workflow Builder - graphical tool that facilitates the modeling of business processes with simple drag-and-drop operations

Oracle Workflow Builder:

Oracle Workflow Builder displays a navigator window with a hierarchy tree containing the following tree nodes:

Item Type – classification of the components that comprise the workflow process definition
Attributes – property associated with a given item type that acts as a global variable that can be referenced or updated by any activity within a process or externally through the use of standard APIs
Processes – a collection of activities in a specific relationship
Notifications – allow for definition of how a message will be used within the process model as an activity.
Functions – defined by the PL/SQL or external program it calls, usually performing fully automated process steps

Events – a business event from the Business Event System
Messages – defines the information conveyed in a notification
Lookup Types –a static list of values that can be used by messages and activity attributes

OUTBOUND-UTL PACKAGE

UTL_FILE PACKAGE



The UTL_FILE package provides text file I/O from within PL/SQL. The init.ora file, the initialization parameter UTL_FILE_DIR is set to the accessible directories desired.
UTL_FILE_DIR = directory_name

Function & Procedure Description

• FOPEN A function that opens a file for input or output and returns a file handle used in subsequent I/O operations
• IS_OPEN A function that returns a Boolean value whenever a file handle refers to an open file
• GET_LINE A procedure that reads a line of text from the opened file and places the text in the Output buffer parameter (the maximum size of an input record is 1,023 bytes unlessyou specify a larger size in the overloaded version of FOPEN)
• PUT, PUT_LINE A procedure that writes a text string stored in the buffer parameter to the opened file (no line terminator is appended by put; use new_line to terminate the line, or use PUT_LINE to write a complete line with a terminator)
• PUTF A formatted put procedure with two format specifiers: %s and \n (use %s to substitute a value into the output string. \n is a new line character)
• NEW_LINE Procedure that terminates a line in an output file
• FFLUSH Procedure that writes all data buffered in memory to a file
• FCLOSE Procedure that closes an opened file
• FCLOSE_ALL Procedure that closes all opened file handles for the session
• Exceptions to the UTL_FILE Package Exception Name Description
• INVALID_PATH The file location or filename was invalid.
• INVALID_MODE The OPEN_MODE parameter in FOPEN was invalid.
• INVALID_FILEHANDLE The file handle was invalid.
• INVALID_OPERATION The file could not be opened or operated on as requested.
• READ_ERROR An operating system error occurred during the read operation.
• WRITE_ERROR An operating system error occurred during the write operation.
• INTERNAL_ERROR An unspecified error occurred in PL/SQL.

UTL_FILE procedures can also raise predefined PL/SQL exceptions such as NO_DATA_FOUND or
VALUE_ERROR.


SQL * Loader


D:\oracle\prodora\8.0.6\BIN>SQLLDR scott/tiger@prod control='e:\vbbk\myin.ctl'

New Form Quick Review

New Form Development
Edit PRE‐FORM trigger at form level
FND_STANDARD.FORM_INFO('$Revision: 115.12 $', 'JP Media Form', 'JP','$Date: 2003/12/19 11:02 $', '$Author: appldev $');
app_standard.event('PRE-FORM');
app_window.set_window_position('MYWINDOW', 'FIRST_WINDOW');

Edit the program unit’s APP_CUSTOM body


if (wnd = 'MYWINDOW') then
app_window.close_first_window;
elsif (wnd = '') then
null;
elsif (wnd = '') then
null;
end if;


Application Application -> Register
Form Application-> Form
Function Application-> Function
Menu Application-> Menu
Responsibility Security->Responsibility ->Define
User Security-> User -> Define

WHO Columns

CREATED_BY  :NUMBER(15) NOT NULL Keeps track of which user created each row
Value:TO_NUMBER(FND_PROFILE.VALUE(’USER_ID’))
CREATION_DATE :DATE NOT NULL:Stores the date on which each row was created
LAST_UPDATED_BY :NUMBER(15) NOT NULL Keeps track of who last updated each row
Value:TO_NUMBER(FND_PROFILE.VALUE(’USER_ID’))
LAST_UPDATE_DATE: DATE NOT NULL:Stores the date on which each row was last updated
LAST_UPDATED_LOGIN :NUMBER(15):Provides access to Information about the operating system login of the user who last updated each row Value: TO_NUMBER(FND_PROFILE.VALUE(’LOGIN_ID’))

HRMS Tables

HRMS BASE TABLES:(Table _F are date tracked tables)

• PER_ALL_PEOPLE_F: DateTracked table holding personal information for employees, applicants and other people. Primary Key:PERSON_ID .The columns START_DATE,EFFECTIVE_START_DATE and EFFECTIVE_END_DATE are all maintained by DateTrack. The START_DATE is the date when the first record for this person was created.

(Object Version Number: Keeps track of Date change.The triggers maintains the object_version_number column when a record is modified via an interface which does not support the object_version_number explicitly. This ensures the object_version_number is maintained consistently.)
                    Database Triggers:
                 Trigger Name : SSP_PER_T1
                 Trigger Time : AFTER
                 Trigger Level : ROW
                 Trigger Event : UPDATE

                Trigger Name : PER_ALL_PEOPLE_F_OVN
                Trigger Time : BEFORE
                Trigger Level : ROW
                Trigger Event : INSERT, UPDATE

• PER_ALL_ASSIGNMENTS_F:Employee and applicant assignment details.And is the DateTracked table.The ASSIGNMENT_TYPE is E for an employee assignment, and A for an applicant assignment.EX_EMP for an X-Employee.Primary Key:ASSIGNMENT_ID.Employee can have Multiple Assignments But need to hold one primary Assignments.

           Database Triggers
           Trigger Name : PER_ALL_ASSIGNMENTS_F_OVN
           Trigger Time : BEFORE
           Trigger Level : ROW
           Trigger Event : INSERT, UPDATE

• PER_ASSIGNMENT_INFO_TYPES:The definitions of extra information types that may be held against an assignment.Primary_key:INFORMATION_TYPE
• PER_ASS_STATUS_TYPE_AMENDS:Amendments to predefined assignment status types.Primary Key:ASSIGNMENT_STATUS_TYPE_ID.The HRMS system Always checks the PER_ASS_STATUS_TYPE_AMENDS table for the existence of status types before checking the PER_ASSIGNMENT_STATUS_TYPES table.
• PER_GRADES Grade definitions for a business group.Primary Key:GRADE_ID
• PER_JOBS Jobs defined for a Business Group
• PER_PEOPLE_INFO_TYPES The table holds the definitions of extra information types that may be held against a person.
• PER_PERIODS_OF_SERVICE:Period of service details for an employee.Primary_id:PERIOD_OF_SERVICE_ID.

            Database Triggers
           Trigger Name : SSP_PDS_T2
           Trigger Time : AFTER
           Trigger Level : STATEMENT
           Trigger Event : UPDATE

• PER_PERSON_TYPES:holds the list of names that may be used when entering or changing person types.This determines the employee is ex-employee or employee in USER_PERSON_TYPE Column.
• PER_SPINAL_POINTS:stores a list of progression points from a pay scale and their incremental order number .

PayRoll Tables:
PAY_ALL_PAYROLLS_F:is the DateTracked table.Payroll group definitions.DateTracked table holding personal information for employees, applicants and other people.Primary key:PAYROLL_ID.

The definition of the payroll includes the default processing information for consolidation sets,
payment types, cost allocation, and offset dates.
 
PAY_ELEMENT_ENTRIES_F:is the DateTracked table that holds the list of element entries for each assignment
 Primary Key Column:COST_ALLOCATION_KEYFLEX_ID.
PAY_ELEMENT_ENTRY_VALUES_F:is the DateTracked table that holds the actual values entered for a specific element entry.
PAY_ELEMENT_LINKS_F:is the DateTracked table that holds the eligibility rules that link elements to groups of employees.

• Database Triggers

           Trigger Name : PAY_ELEMENT_LINKS_T1
           Trigger Time : AFTER
           Trigger Level : ROW
           Trigger Event : UPDATE
PAY_ELEMENT_SETS:Has the list of selected or rejected elements for a particular payroll.With the category of its definition like tax,allowance.
PAY_INPUT_VALUES_F:is the DateTracked table that holds the definitions of the input values associated with a specific element.Primary Key:INPUT_VALUE_ID
PAY_ORG_PAYMENT_METHODS_F:Payment methods used by a Business Group.is the DateTracked table.It includes details of the payment type (cash, check/cheque, magnetic tape) and the source bank account.
PAY_ORG_PAY_METHOD_USAGES_F:Details of personal payment methods for individual employees are held in.
PAY_PAYMENT_TYPES:Types of payment that can be processed by the system.
PAY_PAYROLL_ACTIONS:Holds information about a payroll process., including their type and all the parameters passed to them.
PAY_PEOPLE_GROUPS:contains the people group flexfield segment values for individual employee assignments.
PAY_RUN_RESULTS:the run results from processing each element entry. There is always a single run result for each entry.
PAY_RUN_RESULT_VALUES:Result values from processing a single element entry.
   
Other Important tables:
PER_ABSENCE_ATTENDANCES:Records of absence, or attendance for an employee.
PER_ABSENCE_ATTENDANCE_TYPES User defined absence types.

PER_ABS_ATTENDANCE_REASONS Valid absence reasons for an absence type.
PAY_GL_INTERFACE Costed details to be passed to the General Ledger.
PAY_COST_ALLOCATIONS_F Cost allocation details for an assignment.
PAY_COST_ALLOCATION_KEYFLEX Cost Allocation key flexfield combinations table. allocation details at the assignment level.
PAY_COSTS:Cost details and values for run results.

HRMS BASICS

Functional setups Needed:



❑ Administration:  Define Key Flexfields,Descriptive Flexfields,Extra Information Types(EITs), currencies, “View All” HRMS User, lookups and Application Data Exchange (ADE).

❑ Work Structures: Define Key Flexfields with organizations, jobs, positions, grades and payrolls

❑ Compensation and Benefits (Optional):Payroll elements descriptions are included.compensation elements, input value validation, balances,formulas, salary administration, absence management/accruals of paid time off and element sets.

❑ Total Compensation: (Optional)Includes online benefits services, benefits eligibility, eligibility factors, life events, program setup and flex credits calculations.

❑ People and Assignments: (Required)Includes person types, assignment statuses and special personal information.

❑ Specific Business Functions: (Optional)Includes human resource budgets, evaluation systems and requirements matching.Also includes Workers’ Compensation,provincial medical, and vacation banking.

❑ Career and Succession Management: (Optional)Includes recruitment, career management, evaluation and appraisals and succession planning.

Few Concepts To be Noted:-
There are six Key Flexfield Structures you must define before you can


Define a business group in Oracle HRMS. These are:
• Job
• Position
• Grade
• People Group
• Cost Allocation
• Competence


DateTrack:

Keeping History for change in records.i.e The details of a employee needs to be chaged at a point of time,the change can be made with the history of date and time.
This change can be activated form the date that we want and can ahev the previous records also.
The date tracked columns are Effective_Start_date and Effective_end_date.
The types available are:


1.UPDATE – Updated values are written to the database as a new row, effective from today until 31–DEC–4712. The old values remain effective up to and including yesterday.
2.CORRECTION – The updated values override the old record values and inherit the same effective dates.
if UPDATE was selected, DateTrack checks whether the record being updated has already had future updates entered.

• UPDATE_CHANGE_INSERT (Insert) – The changes that the user makes remain in effect until the effective end date of the current record. At that point the future scheduled changes take
effect.
• UPDATE_OVERRIDE (Replace) – The user’s changes take effect from now until the end date of the last record in the future. All future dated changes are deleted.

Delete The record

When deleting a record, the user is prompted for the type of delete.
There are four options, as follows:
• DELETE (End Date) – The record has its effective end date set to today’s date.
• ZAP (Purge) – This is the total delete. All records matching the key value, whatever their date stamps, are deleted.
• FUTURE CHANGE (All) – This choice causes any future dated changes to the current record,to be removed. The current record has its effective end date set to 31–DEC–4712.
• DELETE NEXT CHANGE (Next Change) – This choice causes the next change to the current DateTracked record to be removed.

Basic Commands 3

ALTER TABLE


The ALTER TABLE statement is used to add or drop columns in an existing table.

• ALTER TABLE table_name ADD column_name datatype;
• ALTER TABLE table_name DROP COLUMN column_name;

To add a column named "City" in the "Person" table:

• ALTER TABLE Person ADD City varchar(30);
• ALTER TABLE Person DROP COLUMN Address;

SQL Functions


Types of Functions




There are several basic types and categories of functions in SQL. The basic types of functions are:


• Aggregate Functions


• Scalar functions



Aggregate functions




Aggregate functions operate against a collection of values, but return a single value.


Function Description




AVG(column)


select avg(salary) from employees;


Returns the average value of a column


COUNT(column)


Returns the number of rows (without a NULL value) of a column


COUNT(*)


Returns the number of selected rows


FIRST(column) Returns the value of the first record in a specified field


LAST(column) Returns the value of the last record in a specified field


MAX(column)


Returns the highest value of a column


MIN(column)


Returns the lowest value of a column


STDEV(column)


STDEVP(column)


SUM(column)


Returns the total sum of a column


VAR(column)


VARP(column)




SQL GROUP BY and HAVING


SELECT column,SUM(column) FROM table GROUP BY column;


all the coumns selected should be called in group by.


SELECT column,SUM(column) FROM table GROUP BY column HAVING SUM(column) condition value

• SELECT Company,SUM(Amount) FROM Sales GROUP BY Company HAVING SUM(Amount)>10000;

Basic Commands 2

Referring to Two Tables:always compare two tables which have one referencing column as common;

• SELECT Employees.Name, Orders.Product FROM Employees, Orders WHERE employees.Employee_ID=Orders.Employee_ID;--both column should match(mostly primary keys will be ref)
• SELECT Employees.Name FROM Employees, Orders WHERE Employees.Employee_ID=Orders.Employee_ID AND Orders.Product='Printer';-Comparing two tables and taking out condition from one table;

Using Joins:join used to compare & combine the results of two tables.

The INNER JOIN returns all rows from both tables where there is a match. If there are rows in Employees that do not have matches in Orders, those rows will not be listed.

• SELECT Employees.Name, Orders.Product FROM Employees INNER JOIN Orders ON Employees.Employee_ID=Orders.Employee_ID;

The LEFT JOIN returns all the rows from the first table (Employees), even if there are no matches in the second table (Orders). If there are rows in Employees that do not have matches in Orders, those rows also will be listed.

• SELECT Employees.Name, Orders.Product FROM Employees LEFT JOIN Orders ON Employees.Employee_ID=Orders.Employee_ID;

The RIGHT JOIN returns all the rows from the second table (Orders), even if there are no matches in the first table (Employees). If there had been any rows in Orders that did not have matches in Employees, those rows also would have been listed.

• SELECT Employees.Name, Orders.Product FROM Employees RIGHT JOIN Orders ON Employees.Employee_ID=Orders.Employee_ID;

SQL UNION and UNION ALL

The UNION command is used to select related information from two tables, much like the JOIN command. However, when using the UNION command all selected columns need to be of the same data type excluding Duplicate values

• SELECT E_Name FROM Employees_Norway UNION SELECT E_Name FROM Employees_USA;

The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values including Duplicate Values.

• SELECT E_Name FROM Employees_Norway UNION ALL SELECT E_Name FROM Employees_USA;

SQL Create Database, Table, and Index

• CREATE DATABASE database_name
• CREATE TABLE table_name(  column_name1 data_type,column_name2 data_type);
• CREATE TABLE Person
  
  (
  
  LastName varchar,
  
  FirstName varchar,
  
  Address varchar,
  
  Age int
  
  );

Create Index


Indices are created in an existing table to locate rows more quickly and efficiently.
A Unique Index


Creates a unique index on a table. A unique index means that two rows cannot have the same index value.

• CREATE UNIQUE INDEX index_name ON table_name (column_name);
• CREATE INDEX PersonIndex ON Person (LastName);

SQL Drop Index, Table and Database:

• DROP INDEX index_name ON table_name;
• DROP TABLE table_name;

Difference between TRUNCATE, DELETE and DROP commands

DELETE


The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it. Note that this operation will cause all DELETE triggers on the table to fire.

TRUNCATE


TRUNCATE removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUCATE is faster and doesn't use as much undo space as a DELETE.

DROP


The DROP command removes a table from the database. All the tables' rows, indexes and privileges will also be removed. No DML triggers will be fired. The operation cannot be rolled back.





 

  

 

Basic commands 1

SQL SELECT Statement

• SELECT * FROM table_name; --(selects all columns in the table)
• SELECT column_name(s) FROM table_name;
• SELECT LastName,FirstName FROM Persons;

The SELECT DISTINCT Statement


To SELECT unique values from a table eliminating the duplicates.

• SELECT DISTINCT column_name(s) FROM table_name; 
• SELECT DISTINCT Company FROM Orders;

SQL WHERE Clause

 The where clause is used for conditional selection of statements.

 

•  SELECT column FROM table WHERE column operator value;
•  SELECT * FROM Persons WHERE City='Sandnes';

 You have give the selection Keyword inside the quotes.

 you can use different types of condition in where clause

Operator Description

= Equal
<> Not equal

 > Greater than

< Less than

 >= Greater than or equal

 <= Less than or equal

 BETWEEN Between an inclusive range

 LIKE Search for a pattern

 IN If you know the exact value you want to return for at least one of the columns

 The LIKE Condition

• SELECT column FROM table WHERE column LIKE pattern;

Can use percentage symbol to initate the query to retrive sort based on first letter or last letter;

• SELECT * FROM Persons WHERE FirstName LIKE 'O%';

• SELECT * FROM Persons WHERE FirstName LIKE '%a';

• SELECT * FROM Persons WHERE FirstName LIKE '%la%'

Can add and condition in where clause to add more then one condition

  

SELECT CompanyName, ContactName FROM customers WHERE CompanyName > 'g' AND ContactName > 'g'

 SQL INSERT INTO Statement

Inserts values into the table

• INSERT INTO table_name VALUES (value1, value2,....);

• INSERT INTO table_name (column1, column2,...)VALUES (value1, value2,....);

 SQL UPDATE Statement

 Update the existing column value in a table

• UPDATE table_name SET column_name = new_value WHERE column_name = some_value;

• UPDATE Person SET FirstName = 'abc' WHERE LastName = 'efg'

• UPDATE Person SET Address = 'Dubai', City = 'Karama' WHERE LastName = 'EFG'

SQL DELETE Statement

• DELETE FROM table_name WHERE column_name = some_value;

• DELETE FROM Person WHERE LastName = 'EFG';

• DELETE FROM table_name

or

DELETE * FROM table_name

 Sorting in sql

SQL ORDER BY

• SELECT Company, OrderNumber FROM Orders ORDER BY Company;

• SELECT Company, OrderNumber FROM Orders ORDER BY Company, OrderNumber;

• SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC;

• SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC, OrderNumber ASC;

SQL AND & OR
AND and OR join two or more conditions in a WHERE clause.

The AND operator displays a row if ALL conditions listed are true. The OR operator displays a row if ANY of the conditions listed are true.

• SELECT * FROM Persons WHERE FirstName='Tove' AND LastName='Svendson';

• SELECT * FROM Persons WHERE firstname='Tove' OR lastname='Svendson';

• SELECT * FROM Persons WHERE (FirstName='Tove' OR FirstName='Stephen') AND LastName='Svendson';

SQL IN

 The IN operator may be used if you know the exact value you want to return for at least one of the columns.

 • SELECT column_name FROM table_name WHERE column_name IN (value1,value2,..)

• SELECT * FROM Persons WHERE LastName IN ('Hansen','Pettersen')

SQL BETWEEN

• The BETWEEN ... AND operator selects a range of data between two values. These values can be numbers, text, or dates.

• SELECT column_name FROM table_name WHERE column_name BETWEEN value1 AND value2;

• SELECT * FROM Persons WHERE Salary BETWEEN '10000' AND '150000';

• SELECT * FROM Persons WHERE Salary NOT BETWEEN '10000' AND '150000';

SQL Alias

can assign different names to the columns and tables

• SELECT column AS column_alias FROM table;

• SELECT last_name as NAME from emp;

• SELECT LastName, FirstName FROM Persons AS Employees;

SQL INTRO

SQL is  Structured Query Language to acces database For Manipulations and Analysis.
SQL works with database programs like MS Access, DB2, Informix, MS SQL Server, Oracle, Sybase, etc.


Queries are Categorised as

• SQL Data Manipulation Language (DML)
• SQL Data Definition Language (DDL)
• SQL Tansaction Control Language(TCL)
• Data Control Language (DCL)

1.SQL Data Manipulation Language (DML)



SQL (Structured Query Language) is a syntax for executing queries. But the SQL language also includes a syntax to update, insert, and delete records.


These query and update commands together form the Data Manipulation Language (DML) part of SQL:


• SELECT - extracts data from a database table


• UPDATE - updates data in a database table


• DELETE - deletes data from a database table


• INSERT INTO - inserts new data into a database table




2.SQL Data Definition Language (DDL)




The Data Definition Language (DDL) part of SQL permits database tables to be created or deleted. We can also define indexes (keys), specify links between tables, and impose constraints between database tables.


The most important DDL statements in SQL are:


• CREATE TABLE - creates a new database table


• ALTER TABLE - alters (changes) a database table


• DROP TABLE - deletes a database table


• CREATE INDEX - creates an index (search key)


• DROP INDEX - deletes an index


3.Transaction Control Language (TCL) - These SQL commands are used for managing changes affecting the data. These commands are COMMIT, ROLLBACK, and SAVEPOINT.




4.Data Control Language (DCL) - These SQL commands are used for providing security to database objects. These commands are GRANT and REVOKE.

Setting who columns on validation

Set Who columns in pre-insert:
BEGIN
DATA BLOCK NAME;
FND_STANDARD.SET_WHO;
PACKAGE.VALIDATE_APPROVAL;(Define this procedure in program units)
END;
Set Who columns in pre-update:

begin

XX_VALIDATE1;--procedure 1
XX_VALIDATE2;--procedure 2
fnd_standard.set_who;
PACKAGE.VALIDATE_APPROVAL;(Define this procedure in program units)
end;
Set triggers at when-new-record-instance-level
DECLARE
v_count number;

v_count2 number;

BEGIN

SELECT count(*) into v_count

FROM fnd_lookup_values
WHERE lookup_type = ‘Receipt Number’(count records ad new in db)

and sysdate between start_date_active and nvl(end_date_active,sysdate)

and lookup_code=fnd_global.user_id;
IF v_count = 0 then
--set datablock and procedure to validate SET_ITEM_PROPERTY('XXGOD_JOB_CARD.APPROVE',ENABLED,PROPERTY_FALSE);
END IF;
IF v_count = 1 THEN

--SET_ITEM_PROPERTY('XXGOD_QUOTATION_HDR1.SALES_ORDER',ENABLED,PROPERTY_FALSE);

IF :XXGOD_JOB_CARD.APPROVALS ='Submitted' THEN

SET_ITEM_PROPERTY('XXGOD_JOB_CARD.APPROVE',ENABLED,PROPERTY_TRUE);

ELSE

SET_ITEM_PROPERTY('XXGOD_JOB_CARD.APPROVE',ENABLED,PROPERTY_FALSE);
END IF;
END IF;
END;
Validating Procedure and Package:


PACKAGE XX_VALIDATE IS
PROCEDURE VALIDATE_APPROVAL;
PROCEDURE VALIDATE_APPROVAL1;
PROCEDURE XVALID_DESC_PRO(EVENT IN VARCHAR2);
END;




PACKAGE BODY XX_VALIDATE IS
PROCEDURE VALIDATE_APPROVAL IS

BEGIN

IF :DATA_BLOCK_NAME.APPROVALS IS NULL THEN

FND_MESSAGE.SET_STRING('Status needs to be selected');

FND_MESSAGE.SHOW;

RAISE FORM_TRIGGER_FAILURE;

END IF;

IF :DATA_BLOCK_NAME.APPROVALS!='New'THEN

FND_MESSAGE.SET_STRING('This is a new Job card,select the status as New');
FND_MESSAGE.SHOW;

RAISE FORM_TRIGGER_FAILURE;

ELSE

XX_VALIDATE_1(CALLING ANOTHER PROCEDURE);
END IF;

EXCEPTION

WHEN OTHERS THEN
FND_MESSAGE.ERROR;

RAISE FORM_TRIGGER_FAILURE;
END;
---------------------------------------------------------------------------------------------

PROCEDURE VALIDATE_APPROVAL1 IS
CURSOR C1 IS SELECT APPROVALS FROM table_name WHERE JOB_ID=:data_bolck.item;
v1 VARCHAR2(20);

BEGIN

OPEN C1;

FETCH C1 INTO v1;

IF V1!=:DATA_BLOCK_NAME.APPROVALS THEN

IF V1='New' AND :DATA_BLOCK_NAME.APPROVALS!='Submitted' THEN
FND_MESSAGE.SET_STRING('This is a new Job card,needs to be Submitted');

FND_MESSAGE.SHOW;

RAISE FORM_TRIGGER_FAILURE;

END IF;
IF V1='Submitted' and :DATA_BLOCK_NAME.APPROVALS not in('Approved','Rejected') THEN
FND_MESSAGE.SET_STRING('Submitted Job card,needs to be Approved or Rejected');
FND_MESSAGE.SHOW;
raise form_trigger_failure;

END IF;


IF V1='Rejected' and :DATA_BLOCK_NAME.APPROVALS not in( 'Rejected','Submitted','Closed') THEN
FND_MESSAGE.SET_STRING('Rejected Quotation can only be Submitted or Closed');
FND_MESSAGE.SHOW;
raise form_trigger_failure;
END IF;
if v1='Approved' and :DATA_BLOCK_NAME.approvals not in('Inactive','Active') then
fnd_message.set_string('Approved Job can be Active or Inactive');
fnd_message.show;
raise form_trigger_failure;

end if;


IF v1='Inactive' and :DATA_BLOCK_NAME.approvals!='Active' then
FND_MESSAGE.SET_STRING('Select the status as Active');
FND_MESSAGE.SHOW;
raise form_trigger_failure;

END IF;


IF v1='Active' and : DATA_BLOCK_NAME .approvals!='Closed' then
FND_MESSAGE.SET_STRING('Active Job card has to be Closed');
FND_MESSAGE.SHOW;
raise form_trigger_failure;
END IF;
if v1='Closed' and : DATA_BLOCK_NAME.approvals!='Closed' then
FND_MESSAGE.SET_STRING('Closed Job card can not be modified');
FND_MESSAGE.SHOW;
raise form_trigger_failure;
end if;
END IF;
CLOSE C1;
EXCEPTION
WHEN OTHERS THEN
FND_MESSAGE.ERROR;
RAISE FORM_TRIGGER_FAILURE;
END;


PROCEDURE XGOD_DESC_PRO(EVENT IN VARCHAR2)
IS
BEGIN
IF EVENT='PRE-FORM' THEN

FND_DESCR_FLEX.DEFINE(BLOCK => 'XXGOD_JOB_CARD',FIELD => 'DFF1',
APPL_SHORT_NAME => 'PO',DESC_FLEX_NAME => 'JobCardDFV');
END IF;
IF EVENT='WHEN-NEW-FORM-INSTANCE' THEN

FND_DESCR_FLEX.DEFINE(BLOCK => 'XXGOD_JOB_CARD',FIELD => 'DFF1',APPL_SHORT_NAME => 'PO',DESC_FLEX_NAME => 'JobCardDFV');

FND_DESCR_FLEX.DEFINE(BLOCK => 'XXGOD_JOB_CARD',FIELD => 'DFF',
APPL_SHORT_NAME => 'PO',DESC_FLEX_NAME => 'JobCardDFF1');

END IF;

EXCEPTION

WHEN OTHERS THEN

RAISE FORM_TRIGGER_FAILURE;
END;
END;