AIM Document List

AIM Document List

Business Process Architecture (BP)

BP.010 Define Business and Process Strategy
BP.020 Catalog and Analyze Potential Changes
BP.030 Determine Data Gathering Requirements
BP.040 Develop Current Process Model
BP.050 Review Leading Practices
BP.060 Develop High-Level Process Vision
BP.070 Develop High-Level Process Design
BP.080 Develop Future Process Model
BP.090 Document Business Procedure


Business Requirements Definition (RD)

RD.010 Identify Current Financial and Operating Structure
RD.020 Conduct Current Business Baseline
RD.030 Establish Process and Mapping Summary
RD.040 Gather Business Volumes and Metrics
RD.050 Gather Business Requirements
RD.060 Determine Audit and Control Requirements
RD.070 Identify Business Availability Requirements
RD.080 Identify Reporting and Information Access Requirements


Business Requirements Mapping

BR.010 Analyze High-Level Gaps
BR.020 Prepare mapping environment
BR.030 Map Business requirements
BR.040 Map Business Data
BR.050 Conduct Integration Fit Analysis
BR.060 Create Information Model
BR.070 Create Reporting Fit Analysis
BR.080 Test Business Solutions
BR.090 Confirm Integrated Business Solutions
BR.100 Define Applications Setup
BR.110 Define security Profiles


Application and Technical Architecture (TA)

TA.010 Define Architecture Requirements and Strategy
TA.020 Identify Current Technical Architecture
TA.030 Develop Preliminary Conceptual Architecture
TA.040 Define Application Architecture
TA.050 Define System Availability Strategy
TA.060 Define Reporting and Information Access Strategy
TA.070 Revise Conceptual Architecture
TA.080 Define Application Security Architecture
TA.090 Define Application and Database Server Architecture
TA.100 Define and Propose Architecture Subsystems
TA.110 Define System Capacity Plan
TA.120 Define Platform and Network Architecture
TA.130 Define Application Deployment Plan
TA.140 Assess Performance Risks
TA.150 Define System Management Procedures


Module Design and Build (MD)

MD.010 Define Application Extension Strategy
MD.020 Define and estimate application extensions
MD.030 Define design standards
MD.040 Define Build Standards
MD.050 Create Application extensions functional design
MD.060 Design Database extensions
MD.070 Create Application extensions technical design
MD.080 Review functional and Technical designs
MD.090 Prepare Development environment
MD.100 Create Database extensions
MD.110 Create Application extension modules
MD.120 Create Installation routines


Data Conversion (CV)

CV.010 Define data conversion requirements and strategy
CV.020 Define Conversion standards
CV.030 Prepare conversion environment
CV.040 Perform conversion data mapping
CV.050 Define manual conversion procedures
CV.060 Design conversion programs
CV.070 Prepare conversion test plans
CV.080 Develop conversion programs
CV.090 Perform conversion unit tests
CV.100 Perform conversion business objects
CV.110 Perform conversion validation tests
CV.120 Install conversion programs
CV.130 Convert and verify data

Documentation (DO)

DO.010 Define documentation requirements and strategy
DO.020 Define Documentation standards and procedures
DO.030 Prepare glossary
DO.040 Prepare documentation environment
DO.050 Produce documentation prototypes and templates
DO.060 Publish user reference manual
DO.070 Publish user guide
DO.080 Publish technical reference manual
DO.090 Publish system management guide


Business System Testing (TE)

TE.010 Define testing requirements and strategy
TE.020 Develop unit test script
TE.030 Develop link test script
TE.040 Develop system test script
TE.050 Develop systems integration test script
TE.060 Prepare testing environments
TE.070 Perform unit test
TE.080 Perform link test
TE.090 perform installation test
TE.100 Prepare key users for testing
TE.110 Perform system test
TE.120 Perform systems integration test
TE.130 Perform Acceptance test

PO Delete

**************** PO Delete ***********************

DECLARE
   lb_result        BOOLEAN;
   l_po_number      VARCHAR2 (100) :='5952001681';-- &po_number;
 
   CURSOR po_csr IS
      SELECT poh.po_header_id
           , poh.type_lookup_code
           , poh.segment1 po_number
        FROM po_headers_all poh 
       WHERE NVL (poh.closed_code, 'OPEN') = 'OPEN'
         AND poh.segment1 = l_po_number;
BEGIN
   FOR po_rec IN po_csr LOOP
       lb_result :=  po_headers_sv1.delete_po
                             ( x_po_header_id     => po_rec.po_header_id
                             , x_type_lookup_code => po_rec.type_lookup_code
                             , p_skip_validation  => 'Y' );

       IF lb_result = TRUE THEN
        --  DBMS_OUTPUT.PUT_LINE('PO Number Deleted '|| l_po_number);
        DBMS_OUTPUT.put_line ( 'Successfully Deleted the PO');
          COMMIT;
       ELSE
         -- DBMS_OUTPUT.PUT_LINE ('API Failed to delete the Purchase Order ' || l_po_number);
         DBMS_OUTPUT.put_line ( 'Failed to Delete the PO');
          ROLLBACK;
       END IF;
   END LOOP;
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE ('Unexpected Exception while deleting PO~' || l_po_number || '~' || SQLERRM);
      ROLLBACK;
END;

PO_DOCUMENT_CONTROL_PUB.CONTROL_DOCUMENT ( Cancel PO Line )

#************** Cancel PO Line *****************#
DECLARE
 v_return_status   VARCHAR2 (10);
 v_msg_data        VARCHAR2(1000);
 v_po_header_id    NUMBER        := 2503180;
 v_doc_subtype     VARCHAR2(10)  := 'STANDARD';
 v_doc_type        VARCHAR2(10)  := 'PO';
 v_org_id          NUMBER        := 894;
 v_action          VARCHAR2(10)  := 'CANCEL';
 v_action_date     DATE          := SYSDATE;
 l_user_id NUMBER :=40231;
 l_resp_id NUMBER :=20707;
 l_appl_id NUMBER :=201;

  BEGIN

 fnd_global.apps_initialize(l_user_id, l_resp_id, l_appl_id);
MO_GLOBAL.INIT('PO');
mo_global.set_policy_context ('S',v_org_id);
 DBMS_OUTPUT.PUT_LINE ('Calling API For Cancelling Documents');
PO_DOCUMENT_CONTROL_PUB.CONTROL_DOCUMENT (p_api_version => 1.0
                                         ,p_init_msg_list  =>fnd_api.g_true 
                                         ,p_commit          =>fnd_api.g_false
                                         ,x_return_status  =>v_return_status
                                         ,p_doc_type      =>v_doc_type
                                         ,p_doc_subtype   =>v_doc_subtype
                                         ,p_doc_id        =>v_po_header_id
                                         ,p_doc_num       =>NULL
                                         ,p_release_id    =>NULL
                                         ,p_release_num   =>NULL
                                         ,p_doc_line_id   =>NULL
                                         ,p_doc_line_num  =>NULL
                                         ,p_doc_line_loc_id =>NULL
                                         ,p_doc_shipment_num => NULL
                                         ,p_action           => v_action
                                         ,p_action_date      => v_action_date
                                         ,p_cancel_reason    => 'OLD PURCHASE ORDER'
                                         ,p_cancel_reqs_flag => 'N'
                                         ,p_print_flag       => NULL
                                         ,p_note_to_vendor   => NULL
                                         ,p_use_gldate       => NULL
                                         ,p_org_id           => v_org_id);
COMMIT;
DBMS_OUTPUT.PUT_LINE('The Return Status of the API : '|| v_return_status);
IF v_return_status = fnd_api.g_ret_sts_success THEN
 COMMIT;
 DBMS_OUTPUT.PUT_LINE ('Cancellation of PO is Sucessfull : '||v_po_header_id) ;
 ELSE
  DBMS_OUTPUT.PUT_LINE ('Cancellation of PO Failed ');
 ROLLBACK;
 FOR i IN 1 .. FND_MSG_PUB.COUNT_MSG LOOP
   v_msg_data := FND_MSG_PUB.GET( p_msg_index =>i,p_encoded => 'F');
  DBMS_OUTPUT.PUT_LINE( i|| ') '|| v_msg_data);
  END LOOP;
END IF;
END;

Access to Edit or Add New Agreements (Mass Upload) for any Global BPA from iSupplier Portal R12

1. Enable Global BPA
    Enable Global while creating the BPA for the Supplier you wish while creating the Purchase Order


2. We  can enable the “Edit Agreement” option in the action menu in iSupplier      Portal responsibility as below,

Catalog Blanket Agreements can be flagged as enabled for Supplier Editing through iSupplier Portal. This setup is done either as buyer in the work centre, or as Catalog Administrator, using below,

• Buyer Work Centre > Agreements > Action: Enable for Supplier Authoring
• iProcurement Catalog Administration > Agreements > Enable Supplier Authoring (only those agreements enabled for Catalog Administrator Authoring)
  

   The supplier can then find the agreements under the Orders tab, and select to Edit Agreement to be able to   create or update items either via file upload (OAG XML, cXML, CIF and text file) . After suppliers submit  agreement changes, the buyer will receive a notification. The Buyer will then have the option to approve the change through notification or review and compare the details via the Pending Change page.

3. To setup a supplier user in any Env. we need to follow the below steps : 

1. Set profile POS: External URL to the same as the internal URL (profile POS: Internal URL)
2. Go to Purchasing responsibility > Suppliers
3. Search for a supplier
4. Click Update
5. Click Contact Directory
6. Click Create Contact
7. Enter the contact information
8. Check Create User Account for this Contact
9. Enter username (preferably not the same as the email)
10. Check the responsibilities to assign
11. Click Apply
12. Go to System administrator to reset the password if the email with the password is not received.
13. Log as the supplier user to test iSupplier Feature

Procure to Pay (P2P) Life Cycle

P2P Query :

SELECT   DISTINCT
         RH.SEGMENT1 "REQ NUMBER",
         RH.PREPARER_ID,
         RH.CREATION_DATE,
         PH.SEGMENT1 "PO NUMBER",
         RL.ITEM_DESCRIPTION,
         RL.TO_PERSON_ID,
         RL.SOURCE_TYPE_CODE,
         RL.NEED_BY_DATE,
         PH.VENDOR_ID,
         PH.VENDOR_SITE_ID,
         PV.VENDOR_NAME "VENDOR NAME",
         UPPER (PV.VENDOR_TYPE_LOOKUP_CODE) "VENDOR TYPE",
         PVS.VENDOR_SITE_CODE "VENDOR SITE",
         PVS.ADDRESS_LINE1 "ADDRESS",
         PVS.CITY "CITY",
         PVS.COUNTRY "COUNTRY",
         TO_CHAR (TRUNC (PH.CREATION_DATE)) "PO DATE",
         PH.TYPE_LOOKUP_CODE "PO TYPE",
         PD.QUANTITY_ORDERED "QTY ORDERED",
         PD.QUANTITY_CANCELLED "QTY CANCALLED",
         PL.ITEM_DESCRIPTION "ITEM DESCRIPTION",
         PL.UNIT_PRICE "UNIT PRICE",
         (NVL (PD.QUANTITY_ORDERED, 0) - NVL (PD.QUANTITY_CANCELLED, 0))
         * NVL (PL.UNIT_PRICE, 0)
            "PO LINE AMOUNT",
         DECODE (PH.APPROVED_FLAG, 'Y', 'Approved') "PO STATUS",
         PL.ITEM_ID,
         AIA.INVOICE_TYPE_LOOKUP_CODE "INVOICE TYPE",
         AIA.INVOICE_AMOUNT "INVOICE AMOUNT",
         TO_CHAR (TRUNC (AIA.INVOICE_DATE)) "INVOICE DATE",
         AIA.INVOICE_NUM "INVOICE NUMBER",
         (SELECT   DESCRIPTION
            FROM   APPS.AP_TERMS_TL Y
           WHERE   Y.TERM_ID = AIA.TERMS_ID)
            "INVOICE TERMS",
         (SELECT   DECODE (X.MATCH_STATUS_FLAG, 'A', 'Approved')
            FROM   AP.AP_INVOICE_DISTRIBUTIONS_ALL X
           WHERE   X.INVOICE_DISTRIBUTION_ID = AIDA.INVOICE_DISTRIBUTION_ID)
            "INVOICE APPROVED ?",
         AIA.AMOUNT_PAID,
         AIPA.AMOUNT,
         AIA.INVOICE_CURRENCY_CODE,
         AIA.PAYMENT_CURRENCY_CODE,
         AIA.PAYMENT_METHOD_LOOKUP_CODE "PAYMENT METHOD",
         ACA.CHECK_NUMBER "PAYMENT NUMBER",
         TO_CHAR (TRUNC (ACA.CHECK_DATE)) "PAYMENT DATE"
  FROM   APPS.PO_HEADERS_ALL PH,
         APPS.PO_DISTRIBUTIONS_ALL PD,
         APPS.PO_REQ_DISTRIBUTIONS_ALL RD,
         APPS.PO_REQUISITION_LINES_ALL RL,
         APPS.PO_REQUISITION_HEADERS_ALL RH,
         APPS.PO_LINES_ALL PL,
         APPS.PO_VENDORS PV,
         APPS.PO_VENDOR_SITES_ALL PVS,
         APPS.RCV_TRANSACTIONS RT,
         APPS.RCV_SHIPMENT_HEADERS RSH,
         APPS.RCV_SHIPMENT_LINES RSL,
         APPS.AP_INVOICES_ALL AIA,
         APPS.AP_INVOICE_LINES_ALL AILA,
         APPS.AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
         APPS.AP_INVOICE_PAYMENTS_ALL AIPA,
         APPS.AP_CHECKS_ALL ACA
 WHERE       PH.PO_HEADER_ID = PD.PO_HEADER_ID
         AND PD.REQ_DISTRIBUTION_ID = RD.DISTRIBUTION_ID
         AND RD.REQUISITION_LINE_ID = RL.REQUISITION_LINE_ID
         AND RL.REQUISITION_HEADER_ID = RH.REQUISITION_HEADER_ID
         AND PH.PO_HEADER_ID = PL.PO_HEADER_ID
         AND PV.VENDOR_ID(+) = PH.VENDOR_ID
         AND PVS.VENDOR_SITE_ID(+) = PH.VENDOR_SITE_ID
         AND RT.PO_HEADER_ID = PH.PO_HEADER_ID
         AND RT.PO_LINE_ID = PL.PO_LINE_ID
         AND RSH.SHIPMENT_HEADER_ID = RT.SHIPMENT_HEADER_ID
         AND RSL.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
         AND RSL.SHIPMENT_LINE_ID = RT.SHIPMENT_LINE_ID
         AND AILA.PO_HEADER_ID = PH.PO_HEADER_ID
         AND AILA.PO_LINE_ID = PL.PO_LINE_ID
         AND AIA.INVOICE_ID = AILA.INVOICE_ID
         AND AIDA.INVOICE_ID = AILA.INVOICE_ID
         AND AIDA.INVOICE_LINE_NUMBER = AILA.LINE_NUMBER
         AND AIPA.INVOICE_ID = AIA.INVOICE_ID
         AND ACA.CHECK_ID = AIPA.CHECK_ID

Joins and Tables :

 

1. Requisition:

SELECT * FROM PO_REQUISITION_HEADERS_ALL WHERE SEGMENT1='XXXX' --REQUISITION_HEADER_ID SELECT * FROM PO_REQUISITION_LINES_ALL WHERE REQUISITION_HEADER_ID=XXXX  --REQUISITION_LINE_ID SELECT * FROM PO_REQ_DISTRIBUTIONS_ALL WHERE REQUISITION_LINE_ID=XXX

   2. Purchase Order :

             PO_HEADERS_ALL

 

SELECT PO_HEADER_ID FROM PO_HEADERS_ALL WHERE SEGMENT1 =;

SELECT * FROM PO_HEADERS_ALL WHERE PO_HEADER_ID =;

 

PO_LINES_ALL

SELECT * FROM PO_LINES_ALL WHERE PO_HEADER_ID =;

 PO_LINE_LOCATIONS_ALL

 SELECT * FROM PO_LINE_LOCATIONS_ALL WHERE PO_HEADER_ID =;

 

             PO_DISTRIBUTIONS_ALL 

SELECT * FROM PO_DISTRIBUTIONS_ALL WHERE PO_HEADER_ID =;

 PO_RELEASES_ALL

 SELECT * FROM PO_RELEASES_ALL WHERE PO_HEADER_ID =; 

    3. Receiving :

RCV_SHIPMENT_HEADERS

 SELECT * FROM RCV_SHIPMENT_HEADERS WHERE SHIPMENT_HEADER_ID IN

(SELECT SHIPMENT_HEADER_ID FROM RCV_SHIPMENT_LINES

WHERE PO_HEADER_ID =);

 RCV_SHIPMENT_LINES

 SELECT * FROM RCV_SHIPMENT_LINES WHERE PO_HEADER_ID =;

 RCV_TRANSACTIONS 

SELECT * FROM RCV_TRANSACTIONS WHERE PO_HEADER_ID =;

 RCV_ACCOUNTING_EVENTS

 SELECT * FROM RCV_ACCOUNTING_EVENTS WHERE RCV_TRANSACTION_ID IN

(SELECT TRANSACTION_ID FROM RCV_TRANSACTIONS

WHERE PO_HEADER_ID =);

 RCV_RECEIVING_SUB_LEDGER 

SELECT * FROM RCV_RECEIVING_SUB_LEDGER WHERE RCV_TRANSACTION_ID IN

(SELECT TRANSACTION_ID FROM RCV_TRANSACTIONS WHERE PO_HEADER_ID =);

 RCV_SUB_LEDGER_DETAILS

 SELECT * FROM RCV_SUB_LEDGER_DETAILS

WHERE RCV_TRANSACTION_ID IN (SELECT TRANSACTION_ID FROM RCV_TRANSACTIONS

WHERE PO_HEADER_ID =);

     4. Invoices :

AP_INVOICE_DISTRIBUTIONS_ALL

 SELECT * FROM AP_INVOICE_DISTRIBUTIONS_ALL WHERE PO_DISTRIBUTION_ID IN

( SELECT PO_DISTRIBUTION_ID FROM PO_DISTRIBUTIONS_ALL WHERE PO_HEADER_ID =);

 AP_INVOICES_ALL

 SELECT * FROM AP_INVOICES_ALL WHERE INVOICE_ID IN

(SELECT INVOICE_ID FROM AP_INVOICE_DISTRIBUTIONS_ALL WHERE PO_DISTRIBUTION_ID IN

( SELECT PO_DISTRIBUTION_ID FROM PO_DISTRIBUTIONS_ALL WHERE PO_HEADER_ID =));

 AP_INVOICE_LINES_ALL  

 SELECT * FROM AP_INVOICE_LINES_ALL WHERE INVOICE_ID IN

(SELECT INVOICE_ID FROM AP_INVOICE_DISTRIBUTIONS_ALL WHERE PO_DISTRIBUTION_ID IN

( SELECT PO_DISTRIBUTION_ID FROM PO_DISTRIBUTIONS_ALL WHERE PO_HEADER_ID =));

       5. Payments :

         We Can join AP_INVOICE_LINES_ALL with PO_HEADER_ID & PO_LINE_ID from        PO_HEADERS_ALL table.

 

FOR VIEW LIST OF PAYMENTS:

            SELECT * FROM AP_INVOICE_PAYMENTS_ALL;

            SELECT * FROM AP_PAYMENT_SCHEDULES_ALL;

 FOR CHECK’S INFORMATION:

            SELECT * FROM AP_CHECKS_ALL;

 FOR CHECK FORMAT:

            SELECT * FROM AP_CHECK_FORMATS;

            SELECT * FROM AP_CHECKRUN_CONC_PROCESSES_ALL;

PO Approval Hierarchy Query -R12

SELECT DISTINCT
pah.object_id,
pha.segment1 AS PO_NUMBER,
pah.Action_Code,
pah.Action_Date,
papf.full_name AS performed_by,
pah.Note,
pha.amount_limit,
pha.currency_code,
pha.rate,
pha.blanket_total_amount,
(pha.rate * pha.blanket_total_amount) AS BLANKET_TOTAL_AMOUNT_CAD,
abc.second_sign,
pah.object_revision_num AS Revision_Number
FROM po_action_history pah,
per_all_people_f papf,
po_headers_all pha,
( SELECT object_id,
Action_Code,
object_revision_num,
CASE WHEN COUNT (Action_Code) <= 1 THEN 'N' ELSE 'Y' END
AS SECOND_SIGN
FROM po_action_history
WHERE 1 = 1
AND Action_Code = 'FORWARD'
AND object_sub_type_code = 'BLANKET'
GROUP BY object_id, Action_Code, object_revision_num
HAVING COUNT (Action_Code) > 0) abc
WHERE pah.action_code = 'APPROVE'
AND pah.employee_id = papf.person_id
AND pah.object_id = pha.po_header_id
AND pah.object_id = abc.object_id
AND pah.object_revision_num = abc.object_revision_num
AND pha.segment1 = 'XX_PO_NUMBER' -- PO Number
AND pah.object_sub_type_code = 'BLANKET'
ORDER BY pha.segment1 ASC, pah.object_revision_num

D2K Reports Basics

D2K Reports Basics:

Introduction to Oracle Reports Builder:

Oracle Reports Builder is a powerful enterprise reporting tool used to build reports that dynamically retrieve data from the database, format, display and print quality reports. Reports can be stored in File or Database (Report Builder Tables).

Report file storage formats:

.rdf Report :

• Binary File Full report definition (includes source code and comments)
• Modifiable through Builder. Binary, executable Portable if transferred as binary.
• PL/SQL recompiles on Open/Run

.rep Report :

• Binary Run-Only File
• No source code or comments. Not modifiable binary, executable.
• Report Executables

You can use the Reports File Converter (rwcon60) to convert a .RDF to a .REP file. RWCON60 Report Converter/Compiler [File => Administration => Compile (rdf torep)/Convert]

Oracle Reports Builder Tools:

Oracle Reports Builder comes with the following components

• Object Navigator
• Property Palette
• Data Model Editor
• Layout Model Editor
• Parameter Form Editor

Object Navigator:
The Object Navigator shows a hierarchical view of objects in the report. Each item listed is called a node and represents an Object or type of object the report can contain or reference.

Property Palette:
A Property Palette is a window that displays the settings for defining an Oracle reports object.

Data Model Editor:
To specify data for a report, a data model should be defined. A data model is composed of some or all of the following data definition objects.

Queries:
Queries are SQL Select statements that fetch data from the oracle database.
These statements are fired each time the report is run.

Groups:
Groups determine the hierarchy of data appearing in the report and are primarily used to group columns selected in the query. Oracle report automatically creates a group for each query.

Data Columns:
Data columns contain the data values for a report. Default data columns,corresponding to the table columns included in each query’s SELECT list are automatically created by oracle reports. Each column is placed in the group associated with the query that selected the column.

Formula Columns:
Formulas can be entered in formula columns to create computed columns.
Formulas can be written using PL/SQL syntax. Formula columns are generally preceded by CF_ to distinguish from other columns.

Summary Columns:
Summary columns are used for calculating summary information like sum, average etc. This column uses a set of predefined oracle aggregate functions.

Summary columns are generally preceded by CS_ to distinguish them from other columns.

Placeholder Column:
Place holder column is the name of variable which can hold a calculated value like (sum, avg..) or the value can be set by function or by a pl/sql block. Use of place holder column for aggrigate function is not advised, for that use summary column.

Data Links:
Data links are used to establish parent-child relationships between queries and groups via column matching.

Layout Model Editor:
A report layout editor contains the following layout objects

Frames:
Frames surround other layout objects, enabling control of multiple objects simultaneously

Repeating Frames:
Repeating frames acts as placeholders for groups (I.e repeating values) and present rows of data retrieved from the database. Repeating frames repeat as often as the number of rows retrieved.

Fields:
Fields acts as placeholders for columns values. They define the formatting attributes for all columns displayed in the report.

Boilerplate:
Boilerplate consists of text (label of the column) and graphics that appear in a report each time it is run.

Anchor:
Anchors fasten an edge of one object to an edge of another object, ensuring that they maintain their relative positions.

Parameter Form Editor:
Parameter form is a runtime form used to accept inputs from the user.

Parameters:
Parameters are variables for a report that accept input from the user at runtime. These parameter values can then be used in the SQL select statements to retrieve data conditionally. Oracle reports creates a set of system parameters at runtime namely report destination type, number of copies etc.

Report Wizard:

• When we create a default Tabular Report using report wizard, the wizard will take you through the below mentioned pages
• Report Style Tabular, Form-Like, Mailing Label, Form Letter, Group Left, Group

Above, Matrix, Matrix with Group

• Query Type Choose whether to build a SQL query or an Express query.
• Data Enter a SELECT statement to retrieve the report data.
• Displayed Fields Select the fields that you want to display in the output.
• Fields to Total Select the fields that you want to summarize.
• Labels for Fields Alter the labels that appear for each field and the width of each field.
• Template Select the template that you want to use for this report. A template contains standard information such as company logo, date, and so on.

Note: The above steps are different for each report style.

Group Left & Have an additional page: ‘Groups’

Group Above styles

Matrix Reports styles Have 3 additional pages:
 ‘Matrix Rows’ ‘Columns’ ‘Cells’ Mailing Label & Have 4 pages: ‘Report Style’ ‘Data’
Form Letter styles ‘Text’ ‘Template’

The difference between Mailing Labels and Form Letters is, Mailing Label shows multiple records on one page while Form Letter shows one record on each page.

Triggers in Reports:

Types of Triggers:

Formula Triggers: Formula triggers are PL/SQL functions that populate columns of type Formula.

Format Triggers: Format triggers are PL/SQL functions executed before the object is formatted. These triggers are used to dynamically change the formatting attributes and used to conditionally print and not to print a report column value. These triggers return Boolean values TRUE or FALSE. If the return value of the format trigger is FALSE, the value is not displayed.

Action Triggers: Action triggers are used to perform user-defined action. These triggers do not return any value.

Validation Triggers: Validation triggers are PL/SQL functions that are executed when a parameter value is entered and the cursor moves to the next parameter. These triggers return Boolean value TRUE / FALSE.

Report Triggers: Report triggers enable execution of PL/SQL functions at specific time during execution and formatting of report.

Trigger Firing Sequence:

Before Parameter :
Fires before the Runtime Parameter Form are displayed. Can access the PL/SQL
global variables, report level columns and manipulate accordingly.

After Parameter:
Fires after the Runtime Parameter form are displayed. Used to validate the parameter values.

Before Report:
Fires before the report is executed but after the queries is parsed and date is fetched.

Between Pages:
Fires before each page of the report are formatted, except the very first page.
This page is used to customize page formatting.

After Report:
Fires after the report previewer are exited, or after report output is sent to a specified destination.