A sample validation in APEX 4.2

We can create validations in APEX which is similar to WHEN-VALIDATE-RECORD trigger in Oracle forms. The following example explains it briefly.

A Form on a Table with Report is already created based on the table EMP as shown in the following screenshot.

Report:

Form:

A record can be updated by clicking on edit link in report. We are going to create a validation like any employee with department number 10 should not be allowed to update.

Click on Edit Page in Form. Then we will be taken to following screen.

In the Page Rendering section, right click on validations and click create.

Choose validation level as Page in the following screen.

Give the validation name in following screen.

Select validation type as SQL in following screen.

Select the type of validation we want to create as NOT Exists. Now the validation will check for any record before saving whether it has the department number as 10. If the employee belongs to department 10 then it will throw error as ‘Update not allowed’.

 

Choose the Save button from the When Button Pressed list. It will ensure that any record which got updated will be validated before saving.

Click on Create Validation to finally create the validation. Validation will be created as shown below.

An instance of how the error will fire when trying to update a record with employee number 10 is shown in the following screenshot.

Oracle R12 - Query for fetching RFQ details

SELECT   SUM (poll.quantity) quantity, msi.segment1 item_code,
         msi.description item_desc, msi.primary_uom_code uom,
         poh.po_header_id, poh.org_id,
            SUBSTR (hou.NAME, 1, 3)
         || '/'
         || poh.attribute1
         || '/'
         || poh.segment1 rfq_num,
         TO_CHAR (TRUNC (poh.creation_date),'DD-MON-YYYY') creation_date, aps.vendor_name,
         apss.address_line1, apss.address_line2, apss.address_line3,
         apss.city, apss.zip
    FROM po_headers_all poh,
         po_lines_all pol,
         po_line_locations_all poll,
         mtl_system_items_b msi,
         hr_operating_units hou,
         po_rfq_vendors prv,
         ap_suppliers aps,
         ap_supplier_sites_all apss
   WHERE poh.type_lookup_code = 'RFQ'
     AND poh.po_header_id = :p_rfq_num
     AND prv.vendor_id = :p_vendor_id
     AND poh.po_header_id = pol.po_header_id
     AND pol.po_line_id = poll.po_line_id
     AND poll.ship_to_organization_id = msi.organization_id
     AND pol.item_id = msi.inventory_item_id
     AND poh.org_id = hou.organization_id
     AND prv.po_header_id = poh.po_header_id
     AND aps.vendor_id = prv.vendor_id
     AND apss.vendor_site_id = prv.vendor_site_id
GROUP BY msi.segment1,
         msi.description,
         msi.primary_uom_code,
         poh.po_header_id,
         poh.org_id,
         NAME,
         poh.attribute1,
         poh.segment1,
         poh.creation_date,
         aps.vendor_name,
         apss.address_line1,
         apss.address_line2,
         apss.address_line3,
         apss.city,
         apss.zip

Personalize Function not available to this responsibility

You may be getting the following message when click on Help > Diagnostics > Custom Code > Personalize.

 Function not available to this responsibility.

Solution:

Navigation : System Administrator > Profile > System

Query for the profile : Utilities:Diagnostics and set this to Yes