plsql-sample-code/invoice_api_pkg.pkb at main · mortenbra/plsql-sample-code · 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
141
142
143
144
145
146
147
148
149
150
151
152
create or replace package body invoice_api_pkg
as

  /*

  Purpose:    package handles invoices (API)

  Remarks:

  Date        Who  Description
  ----------  ---  -------------------------------------
  05.11.2020  MBR  Created

  */

function create_invoice (p_customer_id in number,
                         p_amount in number,
                         p_description in varchar2 := null,
                         p_vat_code in varchar2 := null) return number
as
  l_customer                     xy_customer%rowtype;
  l_description                  xy_invoice.invoice_description%type;
  l_vat_amount                   xy_invoice.vat_amount%type;
  l_returnvalue                  xy_invoice.invoice_id%type;
begin

  /*

  Purpose:    create new invoice

  Remarks:

  Date        Who  Description
  ----------  ---  -------------------------------------
  05.11.2020  MBR  Created

  */

  -- 1) validate inputs

  appl_error_pkg.assert (p_customer_id is not null, 'Customer ID must be specified!');
  appl_error_pkg.assert (p_amount is not null, 'Amount must be specified!');
  appl_error_pkg.assert (p_amount > 0, 'Amount must be greater than zero!');

  l_customer := customer_pkg.get_customer (p_customer_id);

  appl_error_pkg.assert (l_customer.customer_id is not null, 'Customer not found!');
  appl_error_pkg.assert (l_customer.customer_status = customer_pkg.c_status_active, 'Customer not active!');

  -- 2) do calculations and derive values

  l_description := coalesce (p_description, invoice_pkg.get_default_invoice_description (invoice_pkg.c_type_standard), 'Untitled');

  if p_vat_code is not null then
    l_vat_amount := nvl (invoice_pkg.get_vat_amount (p_amount, p_vat_code), 0);
  else
    l_vat_amount := 0;
  end if;

  -- TODO: calculate due date

  -- 3) perform main task

  l_returnvalue := invoice_pkg.new_invoice (
    p_customer_id => p_customer_id,
    p_amount => p_amount,
    p_vat_amount => l_vat_amount,
    p_invoice_description => p_description
  );

  -- 4) validate outputs

  appl_error_pkg.assert (l_returnvalue is not null, 'Failed to create invoice!');

  return l_returnvalue;

end create_invoice;


procedure approve_invoice (p_invoice_id in number)
as
  l_invoice                      xy_invoice%rowtype;
begin

  /*

  Purpose:    approve draft invoice

  Remarks:

  Date        Who  Description
  ----------  ---  -------------------------------------
  05.11.2020  MBR  Created

  */

  l_invoice := invoice_pkg.get_invoice (p_invoice_id);

  appl_error_pkg.assert (l_invoice.invoice_id is not null, 'Invoice not found!');
  appl_error_pkg.assert (
    p_condition => l_invoice.invoice_status in (invoice_pkg.c_status_draft, invoice_pkg.c_status_waiting),
    p_error_message => 'Invalid invoice status!'
  );

  l_invoice.invoice_status := invoice_pkg.c_status_approved;
  l_invoice.approved_date := sysdate;

  appl_log_pkg.log ('Setting invoice status for invoice ' || p_invoice_id || '...', p_level => appl_log_pkg.c_log_level_debug);

  invoice_pkg.set_invoice (l_invoice);

  appl_log_pkg.log ('... DONE setting invoice status for invoice ' || p_invoice_id, p_level => appl_log_pkg.c_log_level_debug);

end approve_invoice;


function get_color (p_invoice_id in number := null,
                    p_invoice in xy_invoice%rowtype := null) return varchar2
as
  l_invoice     xy_invoice%rowtype;
  l_returnvalue varchar2(255);
begin

  /*

  Purpose:    get invoice status color

  Remarks:

  Date        Who  Description
  ----------  ---  -------------------------------------
  05.11.2020  MBR  Created

  */

  l_invoice := coalesce (p_invoice, invoice_pkg.get_invoice (p_invoice_id));

  l_returnvalue := case
    when l_invoice.status = invoice_pkg.c_status_draft then 'lightgrey'
    when l_invoice.status = invoice_pkg.c_status_paid and l_invoice.amount is not null then 'green'
    else 'red'
  end;

  return l_returnvalue;

end get_color;


end invoice_api_pkg;
/