This discussion is archived
0 Replies Latest reply: Nov 20, 2013 4:42 AM by Raghu Varma RSS

Is it possible to create report using procedure

Raghu Varma Newbie
Currently Being Moderated

Hi All,

 

 

I want to create report in apex.But i do not have sql code.I have one procedure.Is it possible to create report using this procedure in apex.

 

CREATE OR REPLACE PROCEDURE headcsv_prc2

AS

   CURSOR cr_header

   IS

      SELECT ood.organization_code, fm.formula_no AS "FORMULA_NAME",

               fm.formula_vers AS "FORMULA_VERSION",

               fm.formula_desc1 AS "FORMULA_DESC",

               DECODE (fm.formula_status,

                       100, 'New',

                       400, 'Approve for Laboratory Use',

                       700, 'Approve for General',

                       800, 'On Hold',

                       900, 'Frozen',

                       1000, 'Obsolete/Archived'

                      ) AS "FORMULA_STATUS",

               DECODE (fd.line_type, '1', msib.segment1) product,

               DECODE (fd.line_type, '-1', msib.segment1) ingrediant,

               DECODE (fd.line_type, '2', msib.segment1) AS "BY_PRODUCT",

               DECODE (by_product_type,

                       'W', 'Waste',

                       'R', 'Rework',

                       'Y', 'Yield',

                       'S', 'Sample',

                       NULL

                      ) AS "BY_PRODUCT_TYPE"

          FROM org_organization_definitions ood,

               fm_form_mst fm,

               fm_matl_dtl fd,

               mtl_system_items_b msib

         WHERE ood.organization_id = fm.owner_organization_id

           AND fm.owner_organization_id = msib.organization_id

           AND msib.organization_id = fd.organization_id

           AND fd.organization_id = ood.organization_id

           AND fm.formula_id = fd.formula_id

           AND msib.inventory_item_id = fd.inventory_item_id

           -- and fm.FORMULA_NO like'%TEA%'

           AND fd.line_type IN ('1', '2', '-1')

      GROUP BY ood.organization_code,

               fm.formula_no,

               fm.formula_vers,

               fm.formula_desc1,

               fm.formula_status,

               fd.line_type,

               msib.segment1,

               by_product_type

      ORDER BY fm.formula_no, fm.formula_vers;

BEGIN

   DBMS_OUTPUT.put_line (   INITCAP ('ORGANIZATION CODE')

                         || ','

                         || INITCAP ('FORMULA NAME')

                         || ','

                         || INITCAP ('FORMULA VERSION')

                         || ','

                         || INITCAP ('FORMULA DESC')

                         || ','

                         || INITCAP ('FORMULA STATUS')

                         || ','

                         || INITCAP ('PRODUCT')

                         || ','

                         || INITCAP ('INGREDIANT')

                         || ','

                         || INITCAP ('BY PRODUCT')

                         || ','

                         || INITCAP ('BY PRODUCT TYPE')

                        );

   FOR ch IN cr_header

   LOOP

      DBMS_OUTPUT.put_line (   ch.organization_code

                            || ','

                            || ch.formula_name

                            || ','

                            || ch.formula_version

                            || ','

                            || '"'

                            || ch.formula_desc

                            || '"'

                            || ','

                            || ch.formula_status

                            || ','

                            || ch.product

                            || ','

                            || ch.ingrediant

                            || ','

                            || ch.by_product

                            || ','

                            || ch.by_product_type

                           );

   END LOOP;

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line ('Error No: ' || SQLCODE || 'Error Msg: '

                            || SQLERRM

                           );

END;

 

EXEC HeadCSV_Prc2



Thanks,

Raghu

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points