gitonibmi/itmdtl.sql at main · ravisankar-PIO/gitonibmi · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
-- --------------------------------------------------------------------------//
-- Created By.......: Programmers.io @ 2023                                  //
-- Create Date......: 2023/09/05                                             //
-- Developer........: Ravisankar Pandian                                     //
-- Description......: Update ITMMASTF table                                  //
-- -----------------------------------------------------------------------------
-- MODIFICATION LOG:
-- -----------------------------------------------------------------------------
-- Date    | Mod_ID | Developer  | Case and Description
-- --------|--------|------------|----------------------------------------------
--         |        |            |
-- -----------------------------------------------------------------------------
-- Compilation Instruction
-- -----------------------------------------------------------------------------
-- RUNSQLSTM SRCFILE(RAVISANKAR/QSQLSRC) SRCMBR(ITMDTL)
-- COMMIT(*NONE) DFTRDBCOL(RAVISANKAR)
-- -----------------------------------------------------------------------------
Create Or Replace Procedure Itmdtl
(

    In  Item_Num    int,
    In  Item_Des    char(50),
    In  Item_Qty    numeric(5),
    In  Item_Prc    numeric(6),
    In  User_Nam    char(10),
    In  Prog_Nam    char(10),
    In  Mode        numeric(1),
    Out Opr_Flag    char(1)

)


Language SQL
Result Sets 0
Modifies SQL Data
Specific Itmdtl
-- Program type sub


--  ------------------------------------------------------------------
--  * SQL COMPILE OPTIONS                                            *
--  ------------------------------------------------------------------

 SET OPTION  DATFMT = *ISO,
  DLYPRP = *YES,
  DBGVIEW = *SOURCE,
  USRPRF = *OWNER,
  DYNUSRPRF = *OWNER,
  COMMIT = *NONE

Begin
-- -----------------------------------------------------------------
-- *DECLATATIONS                                                   *
-- -----------------------------------------------------------------
    Declare w_sql       varchar(2000) default ' ';
    Declare w_Error     varchar(1000) default ' ';
    Declare success condition for sqlstate '38001';

    Declare Exit Handler for SqlException
    Begin
        case Mode
            when 1 then
            set w_Error = 'Error During Add';

            When 3 then
            set w_Error = 'Error During Copy';

            When 4 then
            set w_Error = 'Error During Delete';

            else
            set w_error = 'Unknown';

        end case;
        Insert into SQLERRLOG Values(Prog_Nam, User_Nam, w_sql, current_timestamp);
    End;


    Declare Continue Handler for success
    Begin
        Insert into SQLERRLOG Values(Prog_Nam, User_Nam, w_sql, current_timestamp);
    End;
    -------------------------------------------------------------------
    -- *MAIN LOGIC OF PROGRAM                                          *
    -------------------------------------------------------------------

    Case Mode
        When 1 then
            set w_sql =       'Insert into ITMMASTF Values ('
                        ||          trim(char(Item_Num))     || ', '''
                        ||   Item_Des || ''' , '
                        ||          trim(char(Item_Qty))      || ', '
                        ||          trim(char(Item_prc))      || ', '''
                        ||   User_Nam || ''' , '
                        ||          'current_Timestamp'      || ', '''
                        ||   Prog_Nam || ''' , '''
                        ||   User_Nam || ''' , '
                        ||          'current_Timestamp'      || ', '''
                        ||   Prog_Nam || ''' )' ;
            signal sqlstate '38001';


        When 3 then
            set w_sql =       'Insert into ITMMASTF Values ('
                        ||          trim(char(Item_Num))     || ', '''
                        ||   Item_Des || ''' , '
                        ||          trim(char(Item_Qty))      || ', '
                        ||          trim(char(Item_prc))      || ', '''
                        ||   User_Nam || ''' , '
                        ||          'current_Timestamp'      || ', '''
                        ||   Prog_Nam || ''' , '''
                        ||   User_Nam || ''' , '
                        ||          'current_Timestamp'      || ', '''
                        ||   Prog_Nam || ''' )' ;
            signal sqlstate '38001';

        When 2 then
            set w_sql =       'Update ITMMASTF Set'
                        || 'ITDESC = ''' || Item_Des         || ''' , '
                        || 'ITQTY = '       ||          trim(char(Item_Qty))      || ', '
                        || 'ITPRICE = '     ||          trim(char(Item_prc))      || ', '
                        || 'UPUSR = '''  || User_Nam         || ''' , '
                        || 'UPDAT = '       ||          'current_Timestamp'      || ', '
                        || 'UPPGM = '''  || Prog_Nam         || ''' '
                        || 'Where ITNUM = ' ||          trim(char(Item_Num))     || ')';
            signal sqlstate '38001';

        When 4 then
            set w_sql =       'Delete from ITMMASTF '
                        || 'Where ITNUM = ' ||          trim(char(Item_Num))     || ')';
            signal sqlstate '38001';
        else
            set Opr_Flag = '0';
    End Case;


End;