Oracle Reports Interview Questions & Answers 3

31. Can a repeating frame be created without a data group as a base?

No.

32. Can a field be used in a report without it appearing in any data group?

Yes.

33. For a field in a repeating frame, can the source come from the column which does not exist in the data group which forms the base for the frame?

Yes.

34. Is it possible to centre an object horizontally in a repeating frame that has a variable horizontal size?

Yes.

35. If yes, how?

By the use anchors.

36. What are the two repeating frame always associated with matrix object?

One down repeating frame below one across repeating frame.

37. Is it possible to split the print previewer into more than one region?

Yes.

38. Does a grouping done for objects in the layout editor affect the grouping done in 

the data model editor?

No.

39. How can a square be drawn in the layout editor of the report writer?

By using the rectangle tool while pressing the (Constraint) key.

40. To display the page no. for each page on a report what would be the source & logical page no. or & of physical page no.?

& physical page no.

41. What does the term panel refer to with regard to pages?

A panel is the no. of physical pages needed to print one logical page.

42. What is an anchoring object & what is its use?

An anchoring object is a print condition object which used to explicitly or implicitly anchor other objects to itself. 

43. What is a physical page? & what is a logical page?

A physical page is a size of a page. That is output by the printer. The logical page is the size of one page of the actual report as seen in the Previewer.

44. What is the frame & repeating frame?

A frame is a holder for a group of fields. A repeating frame is used to display a set of records when the no. of records that are to displayed is not known before.

<<<Previous

Read More »

SRW Package in Oracle Reports : Part2

SRW.RUN_REPORT:

This procedure synchronously executes the specified report within the context of the currently running report.

SRW.RUN_REPORT (“report=test.rdf … “)

SRW.SET_FIELD:

This procedure sets the value of a character, number, or date field. This is useful when you want to conditionally change a field’s value.

SRW.SET_FIELD (object_id, text CHAR | number NUM | date DATE);

Example:

Suppose you want to conditionally change the number of a field, based on each employee’s salary. In the format trigger for the field, you could type the following:

FUNCTION chgfield
   RETURN BOOLEAN
IS
   tmp   NUMBER;
BEGIN
   IF :sal >= 5000
   THEN
      tmp := :sal * 1.10;
      srw.set_field (0, tmp);
   ELSE
      srw.set_field (0, 4000);
   END IF;

   RETURN (TRUE);
END;

SRW.SET_FIELD should be used only to change the contents of a field’s datatype, not change the field to a different datatype.

Others in Brief:

SRW.SET_FONT_FACE: This procedure specifies font face for a CHAR, DATE, or NUMBER field. SRW.SET_FONT_FACE(‘arial’);

SRW.SET_FONT_SIZE: This procedure specifies font size for a CHAR, DATE, or NUMBER field. SRW.SET_FONT_SIZE(10);

SRW.SET_FONT_STYLE: This procedure specifies font style for a CHAR, DATE, or NUMBER field. SRW.SET_FONT_STYLE(SRW.ITALIC_STYLE);

SRW.SET_FORMAT_MASK: This procedure specifies the format mask for the DATE or NUMBER field. SRW.SET_FORMAT_MASK(‘mask’);

SRW.SET_TEXT_COLOR: This procedure specifies the global text color of the CHAR, DATE, or NUMBER field. SRW.SET_TEXT_COLOR(‘color’);

<<<Previous

Read More »

SRW Package in Oracle Reports

SRW (Sql Report Writer) Package is a built in package in Oracle Reports Builder. It is a collection of PL/SQL constructs that include many functions, procedures, and exceptions you can reference in any of your libraries or reports.

The PL/SQL provided by the SRW package enables you to perform such actions as change the formatting of fields, run reports from within other reports, create customized messages to display in the event of report error, and execute SQL statements. There are nearly 70 functions, procedures, and exceptions are there in this package. Here I am giving brief information and uses of few important functions, procedures, and exceptions.

SRW.MESSAGE:

It is a Procedure that displays a message with the message number and text that you specify. It is mainly used to debug a report in Reports Builder.

SRW.MESSAGE(msg_number NUMBER, msg_text CHAR);

Example:

FUNCTION foo
   RETURN BOOLEAN
IS
BEGIN
   IF :sal < 0
   THEN
      srw.MESSAGE (100, 'Found a negative salary. Check the EMP table.');
      RAISE srw.program_abort;
   ELSE
      :bonus := :sal * .01;
   END IF;

   RETURN (TRUE);
END;

SRW.PROGRAM_ABORT:

This exception stops the report execution and raises the following error message: REP-1419: PL/SQL program aborted. SRW.PROGRAM_ABORT stops report execution when you raise it.

SRW.DO_SQL:

This procedure executes the specified SQL statement from within Reports Builder. The SQL statement can be DDL (statements that define data), or DML (statements that manipulate data). DML statements are usually faster when they are in PL/SQL, instead of in SRW.DO_SQL.

Since you cannot perform DDL statements in PL/SQL, the SRW.DO_SQL packaged procedure is especially useful for performing them within Reports Builder.

Example:

FUNCTION createtable
   RETURN BOOLEAN
IS
BEGIN
   srw.do_sql
      ('CREATE TABLE TEST_EMP (EMPNO NUMBER NOT NULL
     PRIMARY KEY, SAL NUMBER (10,2)) PCTFREE 5 PCTUSED 75'
      );
   RETURN (TRUE);
EXCEPTION
   WHEN srw.do_sql_failure
   THEN
      srw.MESSAGE (100, 'ERROR WHILE CREATING TEST_EMP TABLE.');
      RAISE srw.program_abort;
END;

SRW.DO_SQL_FAILURE:

Reports Builder raises this exception when the SRW.DO_SQL packaged procedure fails. This exception stops the report execution and raises the following error message:

REP-1425: Error running DO_SQL package – REP-msg ORA-msg.

SRW.GET_REPORT_NAME:

This function returns the file name of the report being executed.

SRW.GET_REPORT_NAME (report_name);

Example:

FUNCTION afterpform
   RETURN BOOLEAN
IS
   my_var   VARCHAR2 (80);
BEGIN
   srw.get_report_name (my_var);
   srw.MESSAGE (0, 'Report Filename = ' || my_var);
   RETURN (TRUE);
END;

Continue>>>

Read More »

Report Registration with Parameters in Oracle Apps

Step1)
Develop a report with parameters using RDF Builder.
Download sample RDF from here.

Step2)
Move this report to PO_TOP in server.
Path :  po/12.0.0/reports/US

Step 3) : Creating Executable
Login to Oracle Applications and navigate to executable form like below.
Navigation : Application Developer --> Concurrent -->Executable
Enter Executable, Short name, Application where you moved your report, Execution method as Oracle reports and finally Your execution file name and save your work.

Step 4) Creating Concurrent Program
Navigation : Application Developer --> Concurrent --> Program
Enter Program name, Short name and Application. Give executable name as you created earlier and output format and save the form.

Step5) Attaching Parameters
Click on parameters button in the above window, add parameter as below and make sure that your parameter name in the report should match the Token name like below.

Step 6) Assigning Program to Request Group.
Navigation: System Administrator --> Security --> Responsibility --> Request
Here, I am creating new Request group and assigning concurrent program and save your work.

Step7)
Run the concurrent program from your attached request group responsibility, enter parameter value and submit the request.

The output is

Also read: Report Registration with out Parameters

Read More »

Elemental Inventory Value Oracle Standard Report Query

Query To Get  Totals By Element Of Cost Of an Item :  

SELECT ROUND (material_cost.VALUE, 2) material,
       ROUND (material_overhead_cost.VALUE, 2) material_overhead,
       ROUND (resource_cost.VALUE, 2) resource_cost,
       ROUND (outside_processing_cost.VALUE, 2) outside_processing,
       ROUND (overhead_cost.VALUE, 2) overhead,
       (  ROUND (material_cost.VALUE, 2)
        + ROUND (material_overhead_cost.VALUE, 2)
        + ROUND (resource_cost.VALUE, 2)
        + ROUND (outside_processing_cost.VALUE, 2)
        + ROUND (overhead_cost.VALUE, 2))
          total,
       outside_processing_cost.organization_id organization_id,
       DECODE (outside_processing_cost.organization_id,
               85, 'MST',
               86, 'PLP',
               87, 'SBP',
               88, 'CSP',
               89, 'SEN',
               101, 'MES')
          organization_code,
       DECODE (outside_processing_cost.organization_id,
               86, '10',
               87, '40',
               88, '30',
               89, '50')
          org_company
  FROM (SELECT --cost_details.item_number,
               --cost_details.organization_id,
               ROUND (SUM (cost_details.VALUE), 2) total
          --cost_details.qty
          FROM (  SELECT x.inventory_item_id,
                         x.organization_id,
                         x.item_number,
                         x.qty,
                         SUM (x.qty * NVL (z.material_overhead_cost, '0'))
                            VALUE
                    FROM (  SELECT b.segment1 AS item_number,
                                   a.organization_id,
                                   a.inventory_item_id,
                                   a.subinventory_code,
                                   SUM (a.primary_transaction_quantity) AS qty
                              FROM mtl_onhand_quantities_detail a,
                                   mtl_system_items_b b
                             WHERE a.inventory_item_id = b.inventory_item_id
                                   AND a.organization_id = b.organization_id
                          GROUP BY b.segment1,
                                   a.organization_id,
                                   a.inventory_item_id,
                                   a.subinventory_code) x,
                         (SELECT a.organization_id,
                                 a.asset_inventory,
                                 a.secondary_inventory_name,
                                 b.segment3 material_account,
                                 f.segment3 material_overhead_account,
                                 c.segment3 resource_account,
                                 d.segment3 overhead_account,
                                 e.segment3 expense_account
                            FROM mtl_secondary_inventories a,
                                 gl_code_combinations b,
                                 gl_code_combinations c,
                                 gl_code_combinations d,
                                 gl_code_combinations e,
                                 gl_code_combinations f
                           WHERE     1 = 1             --a.asset_inventory='1'
                                 AND a.organization_id = P_ORGANIZATION_ID
                                 AND a.material_account = b.code_combination_id
                                 AND a.material_overhead_account =
                                        f.code_combination_id
                                 AND a.resource_account = c.code_combination_id
                                 AND a.overhead_account = d.code_combination_id
                                 AND a.expense_account = e.code_combination_id) y,
                         (SELECT inventory_item_id,
                                 organization_id,
                                 material_cost,
                                 material_overhead_cost,
                                 resource_cost,
                                 overhead_cost,
                                 item_cost
                            FROM cst_item_costs
                           WHERE     1 = 1
                                 AND cost_type_id = 2
                                 AND organization_id = P_ORGANIZATION_ID  --86
                                                                        ) z
                   WHERE     x.organization_id = y.organization_id
                         AND x.subinventory_code = y.secondary_inventory_name
                         AND z.inventory_item_id = x.inventory_item_id
                         AND z.organization_id = x.organization_id
                --and z.material_overhead_cost <> '0'
                GROUP BY x.item_number,
                         x.subinventory_code,
                         x.qty,
                         x.inventory_item_id,
                         x.organization_id,
                         z.material_overhead_cost,
                         y.material_overhead_account
                UNION ALL
                  SELECT x.inventory_item_id,
                         x.organization_id,
                         x.item_number,
                         x.qty,
                         SUM (x.qty * NVL (z.material_cost, '0')) VALUE
                    FROM (  SELECT b.segment1 AS item_number,
                                   a.organization_id,
                                   a.inventory_item_id,
                                   a.subinventory_code,
                                   SUM (a.primary_transaction_quantity) AS qty
                              FROM mtl_onhand_quantities_detail a,
                                   mtl_system_items_b b
                             WHERE a.inventory_item_id = b.inventory_item_id
                                   AND a.organization_id = b.organization_id
                          GROUP BY b.segment1,
                                   a.organization_id,
                                   a.inventory_item_id,
                                   a.subinventory_code) x,
                         (SELECT a.organization_id,
                                 a.asset_inventory,
                                 a.secondary_inventory_name,
                                 b.segment3 material_account,
                                 f.segment3 material_overhead_account,
                                 c.segment3 resource_account,
                                 d.segment3 overhead_account,
                                 e.segment3 expense_account
                            FROM mtl_secondary_inventories a,
                                 gl_code_combinations b,
                                 gl_code_combinations c,
                                 gl_code_combinations d,
                                 gl_code_combinations e,
                                 gl_code_combinations f
                           WHERE     1 = 1             --a.asset_inventory='1'
                                 AND a.organization_id = P_ORGANIZATION_ID
                                 AND a.material_account = b.code_combination_id
                                 AND a.material_overhead_account =
                                        f.code_combination_id
                                 AND a.resource_account = c.code_combination_id
                                 AND a.overhead_account = d.code_combination_id
                                 AND a.expense_account = e.code_combination_id) y,
                         (SELECT inventory_item_id,
                                 organization_id,
                                 material_cost,
                                 material_overhead_cost,
                                 resource_cost,
                                 overhead_cost,
                                 item_cost
                            FROM cst_item_costs
                           WHERE     1 = 1
                                 --inventory_item_id= NVL(:P_INVENTORY_ITEM_ID,inventory_item_id)
                                 AND cost_type_id = 2
                                 AND organization_id = P_ORGANIZATION_ID  --86
                                                                        ) z
                   WHERE     x.organization_id = y.organization_id
                         AND x.subinventory_code = y.secondary_inventory_name
                         AND z.inventory_item_id = x.inventory_item_id
                         AND z.organization_id = x.organization_id
                --and z.material_overhead_cost <> '0'
                GROUP BY x.item_number,
                         x.subinventory_code,
                         x.qty,
                         x.inventory_item_id,
                         x.organization_id,
                         z.material_overhead_cost,
                         y.material_overhead_account
                UNION ALL
                  SELECT x.inventory_item_id,
                         x.organization_id,
                         x.item_number,
                         x.qty,
                         SUM (x.qty * NVL (z.resource_cost, '0')) VALUE
                    FROM (  SELECT b.segment1 AS item_number,
                                   a.organization_id,
                                   a.inventory_item_id,
                                   a.subinventory_code,
                                   SUM (a.primary_transaction_quantity) AS qty
                              FROM mtl_onhand_quantities_detail a,
                                   mtl_system_items_b b
                             WHERE a.inventory_item_id = b.inventory_item_id
                                   AND a.organization_id = b.organization_id
                          GROUP BY b.segment1,
                                   a.organization_id,
                                   a.inventory_item_id,
                                   a.subinventory_code) x,
                         (SELECT a.organization_id,
                                 a.asset_inventory,
                                 a.secondary_inventory_name,
                                 b.segment3 material_account,
                                 f.segment3 material_overhead_account,
                                 c.segment3 resource_account,
                                 d.segment3 overhead_account,
                                 e.segment3 expense_account
                            FROM mtl_secondary_inventories a,
                                 gl_code_combinations b,
                                 gl_code_combinations c,
                                 gl_code_combinations d,
                                 gl_code_combinations e,
                                 gl_code_combinations f
                           WHERE     1 = 1             --a.asset_inventory='1'
                                 AND a.organization_id = P_ORGANIZATION_ID
                                 AND a.material_account = b.code_combination_id
                                 AND a.material_overhead_account =
                                        f.code_combination_id
                                 AND a.resource_account = c.code_combination_id
                                 AND a.overhead_account = d.code_combination_id
                                 AND a.expense_account = e.code_combination_id) y,
                         (SELECT inventory_item_id,
                                 organization_id,
                                 material_cost,
                                 material_overhead_cost,
                                 resource_cost,
                                 overhead_cost,
                                 item_cost
                            FROM cst_item_costs
                           WHERE     1 = 1
                                 AND cost_type_id = 2
                                 AND organization_id = P_ORGANIZATION_ID  --86
                                                                        ) z
                   WHERE     x.organization_id = y.organization_id
                         AND x.subinventory_code = y.secondary_inventory_name
                         AND z.inventory_item_id = x.inventory_item_id
                         AND z.organization_id = x.organization_id
                --and z.material_overhead_cost <> '0'
                GROUP BY x.item_number,
                         x.subinventory_code,
                         x.qty,
                         x.inventory_item_id,
                         x.organization_id,
                         z.material_overhead_cost,
                         y.material_overhead_account
                UNION ALL
                  SELECT x.inventory_item_id,
                         x.organization_id,
                         x.item_number,
                         x.qty,
                         SUM (x.qty * NVL (z.overhead_cost, '0')) VALUE
                    FROM (  SELECT b.segment1 AS item_number,
                                   a.organization_id,
                                   a.inventory_item_id,
                                   a.subinventory_code,
                                   SUM (a.primary_transaction_quantity) AS qty
                              FROM mtl_onhand_quantities_detail a,
                                   mtl_system_items_b b
                             WHERE a.inventory_item_id = b.inventory_item_id
                                   AND a.organization_id = b.organization_id
                          GROUP BY b.segment1,
                                   a.organization_id,
                                   a.inventory_item_id,
                                   a.subinventory_code) x,
                         (SELECT a.organization_id,
                                 a.asset_inventory,
                                 a.secondary_inventory_name,
                                 b.segment3 material_account,
                                 f.segment3 material_overhead_account,
                                 c.segment3 resource_account,
                                 d.segment3 overhead_account,
                                 e.segment3 expense_account
                            FROM mtl_secondary_inventories a,
                                 gl_code_combinations b,
                                 gl_code_combinations c,
                                 gl_code_combinations d,
                                 gl_code_combinations e,
                                 gl_code_combinations f
                           WHERE     1 = 1             --a.asset_inventory='1'
                                 AND a.organization_id = P_ORGANIZATION_ID
                                 AND a.material_account = b.code_combination_id
                                 AND a.material_overhead_account =
                                        f.code_combination_id
                                 AND a.resource_account = c.code_combination_id
                                 AND a.overhead_account = d.code_combination_id
                                 AND a.expense_account = e.code_combination_id) y,
                         (SELECT inventory_item_id,
                                 organization_id,
                                 material_cost,
                                 material_overhead_cost,
                                 resource_cost,
                                 overhead_cost,
                                 item_cost
                            FROM cst_item_costs
                           WHERE     1 = 1
                                 AND cost_type_id = 2
                                 AND organization_id = P_ORGANIZATION_ID  --86
                                                                        ) z
                   WHERE     x.organization_id = y.organization_id
                         AND x.subinventory_code = y.secondary_inventory_name
                         AND z.inventory_item_id = x.inventory_item_id
                         AND z.organization_id = x.organization_id
                --and z.material_overhead_cost <> '0'
                GROUP BY x.item_number,
                         x.subinventory_code,
                         x.qty,
                         x.inventory_item_id,
                         x.organization_id,
                         z.material_overhead_cost,
                         y.material_overhead_account
                UNION ALL
                  SELECT x.inventory_item_id,
                         x.organization_id,
                         x.item_number,
                         x.qty,
                         SUM (x.qty * NVL (z.outside_processing_cost, '0'))
                            VALUE
                    FROM (  SELECT b.segment1 AS item_number,
                                   a.organization_id,
                                   a.inventory_item_id,
                                   a.subinventory_code,
                                   SUM (a.primary_transaction_quantity) AS qty
                              FROM mtl_onhand_quantities_detail a,
                                   mtl_system_items_b b
                             WHERE a.inventory_item_id = b.inventory_item_id
                                   AND a.organization_id = b.organization_id
                          GROUP BY b.segment1,
                                   a.organization_id,
                                   a.inventory_item_id,
                                   a.subinventory_code) x,
                         (SELECT a.organization_id,
                                 a.asset_inventory,
                                 a.secondary_inventory_name,
                                 b.segment3 material_account,
                                 f.segment3 material_overhead_account,
                                 c.segment3 resource_account,
                                 d.segment3 overhead_account,
                                 e.segment3 expense_account
                            FROM mtl_secondary_inventories a,
                                 gl_code_combinations b,
                                 gl_code_combinations c,
                                 gl_code_combinations d,
                                 gl_code_combinations e,
                                 gl_code_combinations f
                           WHERE     1 = 1             --a.asset_inventory='1'
                                 AND a.organization_id = P_ORGANIZATION_ID
                                 AND a.material_account = b.code_combination_id
                                 AND a.material_overhead_account =
                                        f.code_combination_id
                                 AND a.resource_account = c.code_combination_id
                                 AND a.overhead_account = d.code_combination_id
                                 AND a.expense_account = e.code_combination_id) y,
                         (SELECT inventory_item_id,
                                 organization_id,
                                 material_cost,
                                 material_overhead_cost,
                                 resource_cost,
                                 overhead_cost,
                                 outside_processing_cost,
                                 item_cost
                            FROM cst_item_costs
                           WHERE     1 = 1
                                 AND cost_type_id = 2
                                 AND organization_id = P_ORGANIZATION_ID  --86
                                                                        ) z
                   WHERE     x.organization_id = y.organization_id
                         AND x.subinventory_code = y.secondary_inventory_name
                         AND z.inventory_item_id = x.inventory_item_id
                         AND z.organization_id = x.organization_id
                --and z.material_overhead_cost <> '0'
                GROUP BY x.item_number,
                         x.subinventory_code,
                         x.qty,
                         x.inventory_item_id,
                         x.organization_id,
                         z.material_overhead_cost,
                         y.material_overhead_account) cost_details,
               mtl_item_categories mic,
               mtl_parameters mp
         --mtl_material_transactions mmt
         WHERE     1 = 1
               AND cost_details.inventory_item_id = mic.inventory_item_id
               AND cost_details.organization_id = mic.organization_id
               AND cost_details.organization_id = mp.organization_id
               AND mic.organization_id = mp.organization_id
               AND mp.primary_cost_method = 2
               AND mp.default_cost_group_id = 5000
               --and cost_details.inventory_item_id = mmt.inventory_item_id
               --and cost_details.organization_id = mmt.organization_id
               --and trunc (mmt.transaction_date) <= '20-APR-2015'
               AND mic.category_set_id = 1100000046--group by eivr_total.ORGANIZATION_ID,
                                                   --        eivr_total.ORGANIZATION_CODE,
                                                   --        eivr_total.CREATION_DATE,
                                                   --        eivr_total.CREATED_BY,
                                                   --        eivr_total.LAST_UPDATE_DATE,
                                                   --        eivr_total.LAST_UPDATED_BY,
                                                   --        eivr_total.LAST_UPDATE_LOGIN
                                                   --cost_details.item_number,
                                                   --cost_details.organization_id,
                                                   --cost_details.inventory_item_id,
                                                   --cost_details.qty
       ) eivr_total,
       (SELECT SUM (x.qty * NVL (z.material_cost, '0')) VALUE
          FROM (  SELECT b.segment1 AS item_number,
                         a.organization_id,
                         a.inventory_item_id,
                         a.subinventory_code,
                         SUM (a.primary_transaction_quantity) AS qty
                    FROM mtl_onhand_quantities_detail a, mtl_system_items_b b
                   WHERE a.inventory_item_id = b.inventory_item_id
                         AND a.organization_id = b.organization_id
                GROUP BY b.segment1,
                         a.organization_id,
                         a.inventory_item_id,
                         a.subinventory_code) x,
               (SELECT a.organization_id,
                       a.asset_inventory,
                       a.secondary_inventory_name,
                       b.segment3 material_account,
                       f.segment3 material_overhead_account,
                       c.segment3 resource_account,
                       d.segment3 overhead_account,
                       e.segment3 expense_account
                  FROM mtl_secondary_inventories a,
                       gl_code_combinations b,
                       gl_code_combinations c,
                       gl_code_combinations d,
                       gl_code_combinations e,
                       gl_code_combinations f
                 WHERE     1 = 1                       --a.asset_inventory='1'
                       AND a.organization_id = P_ORGANIZATION_ID
                       AND a.material_account = b.code_combination_id
                       AND a.material_overhead_account =
                              f.code_combination_id
                       AND a.resource_account = c.code_combination_id
                       AND a.overhead_account = d.code_combination_id
                       AND a.expense_account = e.code_combination_id) y,
               (SELECT inventory_item_id,
                       organization_id,
                       material_cost,
                       material_overhead_cost,
                       resource_cost,
                       overhead_cost,
                       item_cost
                  FROM cst_item_costs
                 WHERE     1 = 1
                       --inventory_item_id= NVL(:P_INVENTORY_ITEM_ID,inventory_item_id)
                       AND cost_type_id = 2
                       AND organization_id = P_ORGANIZATION_ID            --86
                                                              ) z
         WHERE     x.organization_id = y.organization_id
               AND x.subinventory_code = y.secondary_inventory_name
               AND z.inventory_item_id = x.inventory_item_id
               AND z.organization_id = x.organization_id--and z.material_overhead_cost <> '0'
                                                        --group by x.item_number, x.subinventory_code,x.QTY,x.inventory_item_id,x.organization_id,z.material_overhead_cost,
                                                        --y.material_overhead_account
       ) material_cost,
       (SELECT --x.inventory_item_id
               --,x.organization_id
               --,x.item_number
               --,x.QTY
               SUM (x.qty * NVL (z.material_overhead_cost, '0')) VALUE
          FROM (  SELECT b.segment1 AS item_number,
                         a.organization_id,
                         a.inventory_item_id,
                         a.subinventory_code,
                         SUM (a.primary_transaction_quantity) AS qty
                    FROM mtl_onhand_quantities_detail a, mtl_system_items_b b
                   WHERE a.inventory_item_id = b.inventory_item_id
                         AND a.organization_id = b.organization_id
                GROUP BY b.segment1,
                         a.organization_id,
                         a.inventory_item_id,
                         a.subinventory_code) x,
               (SELECT a.organization_id,
                       a.asset_inventory,
                       a.secondary_inventory_name,
                       b.segment3 material_account,
                       f.segment3 material_overhead_account,
                       c.segment3 resource_account,
                       d.segment3 overhead_account,
                       e.segment3 expense_account
                  FROM mtl_secondary_inventories a,
                       gl_code_combinations b,
                       gl_code_combinations c,
                       gl_code_combinations d,
                       gl_code_combinations e,
                       gl_code_combinations f
                 WHERE     1 = 1                       --a.asset_inventory='1'
                       AND a.organization_id = P_ORGANIZATION_ID
                       AND a.material_account = b.code_combination_id
                       AND a.material_overhead_account =
                              f.code_combination_id
                       AND a.resource_account = c.code_combination_id
                       AND a.overhead_account = d.code_combination_id
                       AND a.expense_account = e.code_combination_id) y,
               (SELECT inventory_item_id,
                       organization_id,
                       material_cost,
                       material_overhead_cost,
                       resource_cost,
                       overhead_cost,
                       item_cost
                  FROM cst_item_costs
                 WHERE     1 = 1
                       AND cost_type_id = 2
                       AND organization_id = P_ORGANIZATION_ID            --86
                                                              ) z
         WHERE     x.organization_id = y.organization_id
               AND x.subinventory_code = y.secondary_inventory_name
               AND z.inventory_item_id = x.inventory_item_id
               AND z.organization_id = x.organization_id--and z.material_overhead_cost <> '0'
                                                        --group by x.item_number, x.subinventory_code,x.QTY,x.inventory_item_id,x.organization_id,z.material_overhead_cost,
                                                        --y.material_overhead_account
       ) material_overhead_cost,
       (SELECT --x.inventory_item_id
               --,x.organization_id
               --,x.item_number
               --,x.QTY
               SUM (x.qty * NVL (z.resource_cost, '0')) VALUE
          FROM (  SELECT b.segment1 AS item_number,
                         a.organization_id,
                         a.inventory_item_id,
                         a.subinventory_code,
                         SUM (a.primary_transaction_quantity) AS qty
                    FROM mtl_onhand_quantities_detail a, mtl_system_items_b b
                   WHERE a.inventory_item_id = b.inventory_item_id
                         AND a.organization_id = b.organization_id
                GROUP BY b.segment1,
                         a.organization_id,
                         a.inventory_item_id,
                         a.subinventory_code) x,
               (SELECT a.organization_id,
                       a.asset_inventory,
                       a.secondary_inventory_name,
                       b.segment3 material_account,
                       f.segment3 material_overhead_account,
                       c.segment3 resource_account,
                       d.segment3 overhead_account,
                       e.segment3 expense_account
                  FROM mtl_secondary_inventories a,
                       gl_code_combinations b,
                       gl_code_combinations c,
                       gl_code_combinations d,
                       gl_code_combinations e,
                       gl_code_combinations f
                 WHERE     1 = 1                       --a.asset_inventory='1'
                       AND a.organization_id = P_ORGANIZATION_ID
                       AND a.material_account = b.code_combination_id
                       AND a.material_overhead_account =
                              f.code_combination_id
                       AND a.resource_account = c.code_combination_id
                       AND a.overhead_account = d.code_combination_id
                       AND a.expense_account = e.code_combination_id) y,
               (SELECT inventory_item_id,
                       organization_id,
                       material_cost,
                       material_overhead_cost,
                       resource_cost,
                       overhead_cost,
                       item_cost
                  FROM cst_item_costs
                 WHERE     1 = 1
                       AND cost_type_id = 2
                       AND organization_id = P_ORGANIZATION_ID            --86
                                                              ) z
         WHERE     x.organization_id = y.organization_id
               AND x.subinventory_code = y.secondary_inventory_name
               AND z.inventory_item_id = x.inventory_item_id
               AND z.organization_id = x.organization_id--and z.material_overhead_cost <> '0'
                                                        --group by x.item_number, x.subinventory_code,x.QTY,x.inventory_item_id,x.organization_id,z.material_overhead_cost,
                                                        --y.material_overhead_account
       ) resource_cost,
       (SELECT --x.inventory_item_id
               --,x.organization_id
               --,x.item_number
               --,x.QTY
               SUM (x.qty * NVL (z.overhead_cost, '0')) VALUE
          FROM (  SELECT b.segment1 AS item_number,
                         a.organization_id,
                         a.inventory_item_id,
                         a.subinventory_code,
                         SUM (a.primary_transaction_quantity) AS qty
                    FROM mtl_onhand_quantities_detail a, mtl_system_items_b b
                   WHERE a.inventory_item_id = b.inventory_item_id
                         AND a.organization_id = b.organization_id
                GROUP BY b.segment1,
                         a.organization_id,
                         a.inventory_item_id,
                         a.subinventory_code) x,
               (SELECT a.organization_id,
                       a.asset_inventory,
                       a.secondary_inventory_name,
                       b.segment3 material_account,
                       f.segment3 material_overhead_account,
                       c.segment3 resource_account,
                       d.segment3 overhead_account,
                       e.segment3 expense_account
                  FROM mtl_secondary_inventories a,
                       gl_code_combinations b,
                       gl_code_combinations c,
                       gl_code_combinations d,
                       gl_code_combinations e,
                       gl_code_combinations f
                 WHERE     1 = 1                       --a.asset_inventory='1'
                       AND a.organization_id = P_ORGANIZATION_ID
                       AND a.material_account = b.code_combination_id
                       AND a.material_overhead_account =
                              f.code_combination_id
                       AND a.resource_account = c.code_combination_id
                       AND a.overhead_account = d.code_combination_id
                       AND a.expense_account = e.code_combination_id) y,
               (SELECT inventory_item_id,
                       organization_id,
                       material_cost,
                       material_overhead_cost,
                       resource_cost,
                       overhead_cost,
                       item_cost
                  FROM cst_item_costs
                 WHERE     1 = 1
                       AND cost_type_id = 2
                       AND organization_id = P_ORGANIZATION_ID            --86
                                                              ) z
         WHERE     x.organization_id = y.organization_id
               AND x.subinventory_code = y.secondary_inventory_name
               AND z.inventory_item_id = x.inventory_item_id
               AND z.organization_id = x.organization_id--and z.material_overhead_cost <> '0'
                                                        --group by x.item_number, x.subinventory_code,x.QTY,x.inventory_item_id,x.organization_id,z.material_overhead_cost,
                                                        --y.material_overhead_account
       ) overhead_cost,
       (  SELECT --x.inventory_item_id
                 x.organization_id,
                 --,x.item_number
                 --,x.QTY
                 SUM (x.qty * NVL (z.outside_processing_cost, '0')) VALUE
            FROM (  SELECT b.segment1 AS item_number,
                           a.organization_id,
                           a.inventory_item_id,
                           a.subinventory_code,
                           SUM (a.primary_transaction_quantity) AS qty
                      FROM mtl_onhand_quantities_detail a, mtl_system_items_b b
                     WHERE a.inventory_item_id = b.inventory_item_id
                           AND a.organization_id = b.organization_id
                  GROUP BY b.segment1,
                           a.organization_id,
                           a.inventory_item_id,
                           a.subinventory_code) x,
                 (SELECT a.organization_id,
                         a.asset_inventory,
                         a.secondary_inventory_name,
                         b.segment3 material_account,
                         f.segment3 material_overhead_account,
                         c.segment3 resource_account,
                         d.segment3 overhead_account,
                         e.segment3 expense_account
                    FROM mtl_secondary_inventories a,
                         gl_code_combinations b,
                         gl_code_combinations c,
                         gl_code_combinations d,
                         gl_code_combinations e,
                         gl_code_combinations f
                   WHERE     1 = 1                     --a.asset_inventory='1'
                         AND a.organization_id = P_ORGANIZATION_ID
                         AND a.material_account = b.code_combination_id
                         AND a.material_overhead_account =
                                f.code_combination_id
                         AND a.resource_account = c.code_combination_id
                         AND a.overhead_account = d.code_combination_id
                         AND a.expense_account = e.code_combination_id) y,
                 (SELECT inventory_item_id,
                         organization_id,
                         material_cost,
                         material_overhead_cost,
                         resource_cost,
                         overhead_cost,
                         outside_processing_cost,
                         item_cost
                    FROM cst_item_costs
                   WHERE     1 = 1
                         AND cost_type_id = 2
                         AND organization_id = P_ORGANIZATION_ID) z
           WHERE     x.organization_id = y.organization_id
                 AND x.subinventory_code = y.secondary_inventory_name
                 AND z.inventory_item_id = x.inventory_item_id
                 AND z.organization_id = x.organization_id
        --and z.material_overhead_cost <> '0'
        GROUP BY x.organization_id--x.item_number, x.subinventory_code,x.QTY,x.inventory_item_id,x.organization_id,z.material_overhead_cost,
                                  --y.material_overhead_account

       ) outside_processing_cost

Read More »

Types of Reports in Oracle Applications

Types of Reports in Oracle Applications

Oracle Reports(D2k): This is the most used tool for reporting on Oracle Applications. Most of reports customization are built with this tool. Once customized the output of the report can be in Excel (Not group By Report), word, Acrobat documents or text format.

Oracle Discoverer:This is an intuitive tool for creating reports and performing on-line analysis.The flexibility of this tool allows the user to create cross tab reports that perform like pivot tables in Excel

Oracle XML Publisher: This is a new Oracle tool for reporting. It enables users to utilize a familiar desktop tool, like MS Word or MS Excel, to create and maintain their own report, XML Publisher merges the custom templates with the concurrent request extracts data to generate output in RTF, PDF, HTML and EXCEL.

Business Intelligence System (BI): This tool provides helps managers to take the right decision with the daily data that is uploaded on their systems.

This tools to provide high level information to run their business such as the profitability of a particular business unit. 

FSG Reports (Financial Statement Generator): Very powerful report building tool for Oracle General Ledger. Benefits of using this tool are that a user can generate financial reports, and schedule reports to run automatically. The only Disadvantage of this tool is that it is only available for the general ledger responsibility and can be used to see only financial account balances.

RXi Report:(Variable reports) :With this tool a user has the ability to print the same report with multiple layouts.This tool is most used on Oracle Financials Applications.

Read More »

Oracle Reports Interview Questions & Answers 2

16. To execute row from being displayed that still use column in the row which property can be used?

Format trigger.

17. Is it possible to set a filter condition in a cross product group in matrix reports?

No.

18. If a break order is set on a column would it effect columns which are under the column?

No.

19. With which function of summary item is the compute at options required?

percentage of total functions.

20. What is the purpose of the product order option in the column property sheet?

To specify the order of individual group evaluation in a cross products.

21. Can a formula column be obtained through a select statement?

Yes.

22. Can a formula column referred to columns in higher group?

Yes.

23. How can a break order be created on a column in an existing group?

By dragging the column outside the group.

24. What are the types of calculated columns available?

Summary, Formula, Placeholder column.

25. What is the use of place holder column?

A placeholder column is used to hold a calculated values at a specified place rather than allowing is to appear in the actual row where it has to appeared.

26. What is the use of hidden column?

A hidden column is used to when a column has to embed into boilerplate text.

27. What is the use of break group?

A break group is used to display one record for one group ones. While multiple related records in other group can be displayed.

28. If two groups are not linked in the data model editor, what is the hierarchy between them?

Two group that is above are the left most rank higher than the group that is to right or below it.

29. The join defined by the default data link is an outer join yes or no?

Yes.

30. How can a text file be attached to a report while creating in the report writer?

By using the link file property in the layout boiler plate property sheet.

<<<Previous     Continue>>>

Read More »

D2K Reports basics part2

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.

D2K Reports Basics Part1

Read More »

D2K Reports Basics Part1

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.

D2K Reports Basics Part 2

Read More »

FND tables for Reports

FND tables for Reports

select * from fnd_concurrent_programs where CONCURRENT_PROGRAM_NAME='POXRQOBO'

select OWNER,OBJECT_NAME,OBJECT_TYPE from all_objects where object_name like'FND_CONCURRENT_P%' --AND OBJECT_TYPE='TABLE%'

select OWNER,OBJECT_NAME,OBJECT_TYPE from all_objects where  OBJECT_TYPE='TABLE' AND object_name like'FND_CONCURRENT_P%'--OBJECT_TYPE='TABLE%'

SELECT * FROM FND_EXECUTABLES

SELECT EXECUTABLE_NAME,EXECUTION_FILE_NAME FROM FND_EXECUTABLES

SELECT * FROM FND_CONCURRENT_REQUESTS

SELECT * FROM FND_REQUEST_GROUPS

SELECT REQUEST_GROUP_NAME,DESCRIPTION FROM FND_REQUEST_GROUPS

SELECT * FROM FND_RESPONSIBILITY   NATURAL JOIN FND_REQUEST_GROUPS

select OWNER,OBJECT_NAME,OBJECT_TYPE from all_objects where  OBJECT_TYPE='TABLE' AND object_name like'FND_RES%'

SELECT B.FND_RESPONSIBILITY,B.REQUEST_GROUP_NAME FROM FND_RESPONSIBILITY  A NATURAL JOIN FND_REQUEST_GROUPS B

Read More »

Reports in Purchase Order

REPORTS OPTIONS FOR ACCESSING PURCHASE ORDER INFORMATION

There are 15-16 reports provide the access PO information in reporting format.Lets summarize some of them, these listing are based on the availability as per Oracle 11i10.2.

1. Purchase Order Detail Report

This report can be used to review all, specific standard, or planned purchase orders. This is used for review the open purchase orders to determine how much you still have to receive and how much your supplier has already billed you.

2. Printed Change Orders (Landscape) Report

This report is used to print changed purchase orders in landscape format. Oracle Purchase provides a default format for the preprinted form.

3.Printed Change Orders (Portrait) Report

This report can be used to provides the same information as the landscape version, but printed in portrait format.

4.Printed Purchase Orders (Landscape Report)

This report can be used to print purchase orders in landscape format.Oracle Purchase provides a default format for the preprinted form.

5.Printed Purchase Orders (Portrait) Report

This report contains the same information as the landscape version, but is printed in portrait format.

6.Blanket and Planned PO Status Report

This report can be used to review purchase order transactions for blanket purchase agreements and planned purchase orders. Information will print for releases existing against the orders. If no release exists, only the purchase order header information is printed.

7.Contract Status Report

This review the status of your contract and list purchase order information regarding each contract using this report.

8.Purchase Agreement Audit Report

This report is used to review purchase order transactions for items normally required to buy using blanket purchase agreements. Oracle Purchase shows you when you purchased items using a standard purchase order that you could have purchased using a blanket purchase agreement.

9.Purchase Order and Releases Detail Report

This report can be used to review detail information for your blanket purchase agreements and planned purchase orders. The quantity ordered, receive and billed and the quantity due to be received are displayed so you can monitor the status Purchase Order Commitment by Period Report

10.Open Purchase Orders Report (by Buyer)

This report is used to review all or specific open purchase orders for specific buyers.

11.Open Purchase Order Report (by Cost Centre)

This report can be used to review all or specific open purchase orders relating to order charged to one or more cost centre suing this report.

12.Purchasing Activity Register Report

This report can be used to review purchase order monetary activity carried out for a time interval, such as a day or month.

13.Purchase Order Distribution Detail Report

This report can be used to review account distribution for a purchase order.

14.Purchase Summary Report by Category

This report can used to review the number of orders you place with vendors for a given category of item.

15.Cancelled Purchase Orders Report

This is used to review cancelled purchase orders.

16. Standard Notes Listing

This is used to review standard notes.

Read More »