OpenACS

timoh
committed
1.1.2:timoh:20051011152328
on 11 Oct 05
added invoice-cancel permission; added template support for pdf; added payment of invoices
/oracle/acs-mail-nt-create.sql (+30 -11)
109 109  function post_request (
110 110         party_from              in parties.party_id%TYPE,
111 111         party_to                in parties.party_id%TYPE,
112 112         expand_group    in char                         default 'f',
113 113         subject                 in acs_mail_bodies.header_subject%TYPE,
114 114         message                 in varchar2,
115 115         max_retries             in integer                      default 0
116 116  ) return acs_mail_queue_messages.message_id%TYPE
117 117  is
118 118         cursor c_expanded_cur is
119 119                    select email from parties p
120 120                         where p.party_id in (select member_id from group_approved_member_map
121 121                                                                 where group_id = party_to);
122 122         c_request_row   c_expanded_cur%ROWTYPE;
123 123         v_header_from   acs_mail_bodies.header_from%TYPE;
124 124         v_header_to             acs_mail_bodies.header_to%TYPE;
125 125         v_body_id               acs_mail_bodies.body_id%TYPE;
126 126         v_item_id               cr_items.item_id%TYPE;
127 127         v_revision_id   cr_revisions.revision_id%TYPE;
128 128         v_message_id    acs_mail_queue_messages.message_id%TYPE;
  129         v_creation_user acs_objects.creation_user%TYPE;
129 130  begin
130 131         if max_retries <> 0 then
131 132            raise_application_error(-20000,
132 133                         'max_retries parameter not implemented.'
133 134                 );
134 135         end if;
135 136
  137         -- get the sender email address
  138         select max(email) into v_header_from from parties where party_id = party_from;
  139
  140         -- if sender address is null, then use site default OutgoingSender
  141         if v_header_from is null then
  142                 select apm.get_value(package_id, 'OutgoingSender') into v_header_from
  143                 from apm_packages where package_key='acs-kernel';
  144         end if;
  145
  146         -- make sure that this party is in users table. If not, let creation_user
  147         -- be null to prevent integrity constraint violations on acs_objects
  148         select max(user_id) into v_creation_user
  149       from users where user_id = party_from;
  150
  151         -- get the recipient email address
  152         select max(email) into v_header_to from parties where party_id = party_to;
  153
  154         -- do not let any of these addresses be null
  155         if v_header_from is null or v_header_to is null then
  156            raise_application_error(-20000,
  157                         'acs_mail_nt: cannot sent email to blank address or from blank address.'
  158            );
  159         end if;
  160
136 161         -- create a mail body with empty content
137 162
138 163         v_body_id := acs_mail_body.new (
139 164                 body_from => party_from,
140 165                 body_date => sysdate,
141                   header_subject => subject
  166                 header_subject => subject,
  167                 creation_user => v_creation_user
142 168         );
143 169
144 170         -- create a CR item to stick message into
145 171         -- for oracle, we need to stick it in a blob
146 172
147 173         v_item_id := content_item.new (
148 174                 name  => 'acs-mail message' || v_body_id,       
149 175         title => subject,
150 176         text  => message
151 177         );
152 178
153 179         -- content_item__new makes a CR revision. We need to get that revision
154 180         -- and make it live
155 181
156 182         v_revision_id := content_item.get_latest_revision (v_item_id);
157 183         content_item.set_live_revision ( v_revision_id );
158 184
159 185         -- set the content of the message
160 186         acs_mail_body.set_content_object( v_body_id, v_item_id );
161 187
162 188         -- queue the message
163 189         v_message_id := acs_mail_queue_message.new (
164                   body_id       => v_body_id
  190                 body_id       => v_body_id,
  191                 creation_user => v_creation_user
165 192         );
166 193
167           -- get the sender email address
168           select max(email) into v_header_from
169         from parties where party_id = party_from;
170  
171           -- get the recipient email address
172           select email into v_header_to
173             from parties where party_id = party_to;
174  
175 194         -- now put the message into the outgoing queue
176 195         -- i know this seems redundant, but that's the way it was built.
177 196         -- The idea is that you put a generic message into the main queue
178 197         -- without from or to address, and then insert a copy of the message
179 198         -- into the outgoing_queue with the specific from and to address
180 199
181 200     if expand_group = 'f' then
182 201            insert into acs_mail_queue_outgoing
183 202            ( message_id, envelope_from, envelope_to )
184 203            values
185 204            ( v_message_id, v_header_from, v_header_to );
186 205         else
187 206                 -- expand the group
188 207                 -- FIXME: need to check if this is a group and if there are members
189 208                 --        if not, do we need to notify sender?
190 209
191 210                 for c_request_row in c_expanded_cur loop
192 211                         insert into acs_mail_queue_outgoing
193 212                         ( message_id, envelope_from, envelope_to )
194 213                         values