PACKAGE rpt2xls 
IS 
  -- Font style constants 
  bold CONSTANT BINARY_INTEGER := 1; 
  italic CONSTANT BINARY_INTEGER := 2; 
  underline CONSTANT BINARY_INTEGER := 4; 
  -- Horizontal alignment constants 
  SUBTYPE xlhalign IS BINARY_INTEGER; 
  center CONSTANT XLHALIGN := -4108; 
  centeracrossselection CONSTANT XLHALIGN := 7; 
  distributed CONSTANT XLHALIGN := -4117; 
  fill CONSTANT XLHALIGN := 5; 
  general CONSTANT XLHALIGN := 1; 
  justify CONSTANT XLHALIGN := -4130; 
  left CONSTANT XLHALIGN := -4131; 
  right CONSTANT XLHALIGN := -4152; 
  -- vertical alignment constants 
  SUBTYPE xlvalign IS BINARY_INTEGER; 
  top CONSTANT XLVALIGN := -4160; 
  bottom CONSTANT XLVALIGN := 25; 
  PROCEDURE put_cell( 
    colno     BINARY_INTEGER, 
    cellvalue IN VARCHAR2, 
    position  IN VARCHAR2 DEFAULT NULL, 
    rowno     IN BINARY_INTEGER DEFAULT NULL, 
    fontname  IN VARCHAR2 DEFAULT NULL, 
    fontsize  IN BINARY_INTEGER DEFAULT NULL, 
    fontstyle IN BINARY_INTEGER DEFAULT NULL, 
    fontcolor IN BINARY_INTEGER DEFAULT NULL, 
    bgrcolor  IN BINARY_INTEGER DEFAULT NULL, 
    format    IN VARCHAR2 DEFAULT NULL, 
    align     IN XLHALIGN DEFAULT NULL, 
    valign    IN XLVALIGN DEFAULT NULL ); 
  PROCEDURE new_line; 
  PROCEDURE previous_line; 
  PROCEDURE delete_line ( 
    p_value IN NUMBER ); 
  PROCEDURE RUN; 
  PROCEDURE release_memory; 
END; 

-- ===================== 
PACKAGE BODY rpt2xls 
IS 
  TYPE excelcell IS RECORD( 
    rowno BINARY_INTEGER, 
    colno BINARY_INTEGER, 
    val VARCHAR2(2000), 
    fontname VARCHAR2(20), 
    fontsize BINARY_INTEGER, 
    fontstyle BINARY_INTEGER, 
    fontcolor BINARY_INTEGER, 
    bgrcolor BINARY_INTEGER, 
    format VARCHAR2(60), 
    align XLHALIGN, 
    valign XLVALIGN ); 
  TYPE excelcells 
    IS TABLE OF EXCELCELL; 
  cell       EXCELCELLS := Excelcells(); 
  currentrow BINARY_INTEGER := 8; 
  cellrow    BINARY_INTEGER; 
  rowvalue   BINARY_INTEGER; 
  PROCEDURE New_line 
  IS 
  BEGIN 
      currentrow := currentrow + 1; 
  END; 
  PROCEDURE Previous_line 
  IS 
  BEGIN 
      currentrow := currentrow - 1; 
  END; 
  PROCEDURE Delete_line (p_value IN NUMBER) 
  IS 
  BEGIN 
      currentrow := currentrow - p_value; 
  END; 
  PROCEDURE Put_cell(colno     BINARY_INTEGER, 
                     cellvalue IN VARCHAR2, 
                     position  IN VARCHAR2 DEFAULT NULL, 
                     rowno     IN BINARY_INTEGER DEFAULT NULL, 
                     fontname  IN VARCHAR2 DEFAULT NULL, 
                     fontsize  IN BINARY_INTEGER DEFAULT NULL, 
                     fontstyle IN BINARY_INTEGER DEFAULT NULL, 
                     fontcolor IN BINARY_INTEGER DEFAULT NULL, 
                     bgrcolor  IN BINARY_INTEGER DEFAULT NULL, 
                     format    IN VARCHAR2 DEFAULT NULL, 
                     align     IN XLHALIGN DEFAULT NULL, 
                     valign    IN XLVALIGN DEFAULT NULL) 
  IS 
  BEGIN 
      IF position = 'H' THEN 
        rowvalue := rowno; 
      ELSE 
        rowvalue := currentrow; 
      END IF; 

      cell.extend; 

      Cell(cell.last).rowno := rowvalue; 

      Cell(cell.last).colno := colno; 

      Cell(cell.last).val := cellvalue; 

      Cell(cell.last).fontname := fontname; 

      Cell(cell.last).fontsize := fontsize; 

      Cell(cell.last).fontstyle := fontstyle; 

      Cell(cell.last).fontcolor := fontcolor; 

      Cell(cell.last).bgrcolor := bgrcolor; 

      Cell(cell.last).format := format; 

      Cell(cell.last).align := align; 

      Cell(cell.last).valign := valign; 
  END; 
  PROCEDURE RUN 
  IS 
    application  ole2.obj_type; 
    workbooks    ole2.obj_type; 
    workbook     ole2.obj_type; 
    worksheets   ole2.obj_type; 
    worksheet    ole2.obj_type; 
    workcell     ole2.obj_type; 
    workcolumn   ole2.obj_type; 
    workfont     ole2.obj_type; 
    workinterior ole2.obj_type; 
    arglist      ole2.list_type; 
  BEGIN 
      application := ole2.Create_obj('Excel.Application'); 

      ole2.Set_property(application, 'Visible', 1); 

      workbooks := ole2.Get_obj_property(application, 'Workbooks'); 

      workbook := ole2.Invoke_obj(workbooks, 'Add'); 

      worksheets := ole2.Get_obj_property(workbook, 'Worksheets'); 

      worksheet := ole2.Get_obj_property(application, 'ActiveSheet'); 

      FOR i IN cell.first .. cell.last LOOP 
          IF Cell(i).val IS NOT NULL THEN 
            arglist := ole2.create_arglist; 

            ole2.Add_arg(arglist, Cell(i).rowno); 

            ole2.Add_arg(arglist, Cell(i).colno); 

            workcell := ole2.Get_obj_property(worksheet, 'Cells', arglist); 

            ole2.Destroy_arglist(arglist); 

            ole2.Set_property(workcell, 'Value', Cell(i).val); 

            ole2.Set_property(workcell, 'NumberFormat', Cell(i).format); 

            IF Cell(i).align IS NOT NULL THEN 
              ole2.Set_property(workcell, 'HorizontalAlignment', Cell(i).align); 
            END IF; 

            IF Cell(i).valign IS NOT NULL THEN 
              ole2.Set_property(workcell, 'VerticalAlignment', Cell(i).valign); 
            END IF; 

            workfont := ole2.Get_obj_property(workcell, 'Font'); 

            workinterior := ole2.Get_obj_property(workcell, 'Interior'); 

            IF Cell(i).fontname IS NOT NULL THEN 
              ole2.Set_property(workfont, 'Name', Cell(i).fontname); 
            END IF; 

            IF Cell(i).fontsize IS NOT NULL THEN 
              ole2.Set_property(workfont, 'Size', Cell(i).fontsize); 
            END IF; 

            IF MOD(Cell(i).fontstyle, 2) = 1 THEN 
              ole2.Set_property(workfont, 'Bold', 1); 
            END IF; 

            IF MOD(Cell(i).fontstyle, 4) > 2 THEN 
              ole2.Set_property(workfont, 'Italic', 1); 
            END IF; 

            IF MOD(Cell(i).fontstyle, 8) > 4 THEN 
              ole2.Set_property(workfont, 'Underline', 2); 
            END IF; 

            IF Cell(i).fontcolor IS NOT NULL THEN 
              ole2.Set_property(workfont, 'ColorIndex', Cell(i).fontcolor); 
            END IF; 

            IF Cell(i).bgrcolor IS NOT NULL THEN 
              ole2.Set_property(workinterior, 'ColorIndex', Cell(i).bgrcolor); 
            END IF; 

            ole2.Release_obj(workinterior); 

            ole2.Release_obj(workfont); 

            ole2.Release_obj(workcell); 
          END IF; 
      END LOOP; 

      arglist := ole2.create_arglist; 

      ole2.Add_arg(arglist, 'A:Z'); 

      workcolumn := ole2.Get_obj_property(worksheet, 'Columns', arglist); 

      ole2.Destroy_arglist(arglist); 

      ole2.Invoke(workcolumn, 'AutoFit'); 

      ole2.Release_obj(workcolumn); 

      ole2.Release_obj(worksheet); 

      ole2.Release_obj(worksheets); 

      ole2.Release_obj(workbook); 

      ole2.Release_obj(workbooks); 

      ole2.Release_obj(application); 
  END; 
  PROCEDURE Release_memory 
  IS 
  BEGIN 
      cell := Excelcells(); 

      currentrow := 0; 

      sys.dbms_session.free_unused_user_memory; 
  END; 
END; 

-- ================ 
-- button action trigger 
------------------------ 
PROCEDURE Exp_to_exlbuttonaction 
IS 
BEGIN 
    -- IF :P_EXCEL_OPTION = 'Y' THEN 
    rpt2xls.RUN; 
--END IF; 
END; 

-- ==================== 
FUNCTION B_48formattrigger 
RETURN BOOLEAN 
IS 
BEGIN 
    rpt2xls.Put_cell(6, 'Run Date :', position => 'H', rowno => 1, 
    fontcolor => 1, 
          fontsize => 8, fontstyle => rpt2xls.bold); 

    RETURN ( TRUE ); 
END; 

-- ============ 
-- heading  
----------- 
FUNCTION B_3formattrigger 
RETURN BOOLEAN 
IS 
BEGIN 
    rpt2xls.Put_cell(2, 'Service Tracking Report', position => 'H', rowno => 1, 
          bgrcolor => 15, fontcolor => 1, fontsize => 15, fontstyle => 
    rpt2xls.bold); 

    RETURN ( TRUE ); 
END; 

-- ============== 
-- header param 
-------------- 
FUNCTION B_51formattrigger 
RETURN BOOLEAN 
IS 
BEGIN 
    rpt2xls.Put_cell(1, 'Location :', position => 'H', rowno => 4, 
    fontcolor => 1, 
          fontsize => 8, fontstyle => rpt2xls.bold); 

    RETURN ( TRUE ); 
END; 

FUNCTION F_locationformattrigger 
RETURN BOOLEAN 
IS 
BEGIN 
    rpt2xls.Put_cell(2, :cf_loccode, position => 'H', rowno => 4, fontcolor => 1 
    , 
          fontsize => 8); 

    RETURN ( TRUE ); 
END; 

-- =============== 
-- label 
FUNCTION B_10formattrigger 
RETURN BOOLEAN 
IS 
BEGIN 
    rpt2xls.Put_cell(1, 'Non Cash Description', position => 'H', rowno => 7, 
          bgrcolor => 15, fontcolor => 1, fontsize => 8, 
    fontstyle => rpt2xls.bold); 

    rpt2xls.Put_cell(2, 'AR Code', position => 'H', rowno => 7, bgrcolor => 15, 
    fontcolor => 1, fontsize => 8, fontstyle => rpt2xls.bold); 

    rpt2xls.Put_cell(3, 'Location', position => 'H', rowno => 7, bgrcolor => 15, 
    fontcolor => 1, fontsize => 8, fontstyle => rpt2xls.bold); 

    rpt2xls.Put_cell(4, 'Created By', position => 'H', rowno => 7, 
    bgrcolor => 15, 
    fontcolor => 1, fontsize => 8, fontstyle => rpt2xls.bold); 

    rpt2xls.Put_cell(5, 'Created On', position => 'H', rowno => 7, 
    bgrcolor => 15, 
    fontcolor => 1, fontsize => 8, fontstyle => rpt2xls.bold); 

    rpt2xls.Put_cell(6, 'Status', position => 'H', rowno => 7, bgrcolor => 15, 
    fontcolor => 1, fontsize => 8, fontstyle => rpt2xls.bold); 

    rpt2xls.Put_cell(7, 'Modified By', position => 'H', rowno => 7, 
    bgrcolor => 15, 
    fontcolor => 1, fontsize => 8, fontstyle => rpt2xls.bold); 

    rpt2xls.Put_cell(8, 'Modified On', position => 'H', rowno => 7, 
    bgrcolor => 15, 
    fontcolor => 1, fontsize => 8, fontstyle => rpt2xls.bold); 

    RETURN ( TRUE ); 
END; 

-- ============= 
-- data 
--------- 
FUNCTION B_7formattrigger 
RETURN BOOLEAN 
IS 
BEGIN 
    rpt2xls.Put_cell(1, :BRAND_code, fontcolor => 1, fontsize => 8); 

    rpt2xls.Put_cell(2, :GROUP_code, fontcolor => 1, fontsize => 8); 

    rpt2xls.Put_cell(3, :LEVEL_1, fontcolor => 1, fontsize => 8); 

    rpt2xls.Put_cell(4, :CF_SUPPLIER, fontcolor => 1, fontsize => 8); 

    rpt2xls.Put_cell(5, :Item_code, fontcolor => 1, fontsize => 8); 

    rpt2xls.Put_cell(6, :item_short_descripton, fontcolor => 1, fontsize => 8); 

    rpt2xls.Put_cell(7, Chr(39) 
                        ||:Item_bar_code, fontcolor => 1, fontsize => 8); 

    rpt2xls.Put_cell(8, Chr(39) 
                        ||:CF_PARENT_CODE, fontcolor => 1, fontsize => 8); 

    rpt2xls.Put_cell(9, :status, fontcolor => 1, fontsize => 8); 

    rpt2xls.Put_cell(10, :CF_CLASSIFICATION_DESC, fontcolor => 1, fontsize => 8) 
    ; 

    rpt2xls.Put_cell(11, :DYNAMIC_STATUS, fontcolor => 1, fontsize => 8); 

    rpt2xls.new_line; 

    RETURN ( FALSE ); 
END;