0 Replies Latest reply: Nov 20, 2013 6:42 AM by Raghu Varma RSS

    Is it possible to create report using procedure

    Raghu Varma

      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