Open-ILS/src/sql/Pg/upgrade/0566.schema.unAPI_XMLAGG_cleanup.sql

   1 -- Evergreen DB patch XXXX.schema.unAPI_XMLAGG_cleanup.sql
   2 --
   3 -- FIXME: insert description of change, if needed
   4 --
   5 BEGIN;
   6
   7
   8 -- check whether patch can be applied
   9 SELECT evergreen.upgrade_deps_block_check('0566', :eg_version);
  10
  11 CREATE OR REPLACE FUNCTION unapi.bre ( obj_id BIGINT, format TEXT,  ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$
  12 DECLARE
  13     me      biblio.record_entry%ROWTYPE;
  14     layout  unapi.bre_output_layout%ROWTYPE;
  15     xfrm    config.xml_transform%ROWTYPE;
  16     ouid    INT;
  17     tmp_xml TEXT;
  18     top_el  TEXT;
  19     output  XML;
  20     hxml    XML;
  21     axml    XML;
  22 BEGIN
  23
  24     SELECT id INTO ouid FROM actor.org_unit WHERE shortname = org;
  25
  26     IF ouid IS NULL THEN
  27         RETURN NULL::XML;
  28     END IF;
  29
  30     IF format = 'holdings_xml' THEN -- the special case
  31         output := unapi.holdings_xml( obj_id, ouid, org, depth, includes, slimit, soffset, include_xmlns);
  32         RETURN output;
  33     END IF;
  34
  35     SELECT * INTO layout FROM unapi.bre_output_layout WHERE name = format;
  36
  37     IF layout.name IS NULL THEN
  38         RETURN NULL::XML;
  39     END IF;
  40
  41     SELECT * INTO xfrm FROM config.xml_transform WHERE name = layout.transform;
  42
  43     SELECT * INTO me FROM biblio.record_entry WHERE id = obj_id;
  44
  45     -- grab SVF if we need them
  46     IF ('mra' = ANY (includes)) THEN
  47         axml := unapi.mra(obj_id,NULL,NULL,NULL,NULL);
  48     ELSE
  49         axml := NULL::XML;
  50     END IF;
  51
  52     -- grab hodlings if we need them
  53     IF ('holdings_xml' = ANY (includes)) THEN
  54         hxml := unapi.holdings_xml(obj_id, ouid, org, depth, evergreen.array_remove_item_by_value(includes,'holdings_xml'), slimit, soffset, include_xmlns);
  55     ELSE
  56         hxml := NULL::XML;
  57     END IF;
  58
  59
  60     -- generate our item node
  61
  62
  63     IF format = 'marcxml' THEN
  64         tmp_xml := me.marc;
  65         IF tmp_xml !~ E'<marc:' THEN -- If we're not using the prefixed namespace in this record, then remove all declarations of it
  66            tmp_xml := REGEXP_REPLACE(tmp_xml, ' xmlns:marc="http://www.loc.gov/MARC21/slim"', '', 'g');
  67         END IF;
  68     ELSE
  69         tmp_xml := oils_xslt_process(me.marc, xfrm.xslt)::XML;
  70     END IF;
  71
  72     top_el := REGEXP_REPLACE(tmp_xml, E'^.*?<((?:\\S+:)?' || layout.holdings_element || ').*$', E'\\1');
  73
  74     IF axml IS NOT NULL THEN
  75         tmp_xml := REGEXP_REPLACE(tmp_xml, '</' || top_el || '>(.*?)$', axml || '</' || top_el || E'>\\1');
  76     END IF;
  77
  78     IF hxml IS NOT NULL THEN -- XXX how do we configure the holdings position?
  79         tmp_xml := REGEXP_REPLACE(tmp_xml, '</' || top_el || '>(.*?)$', hxml || '</' || top_el || E'>\\1');
  80     END IF;
  81
  82     IF ('bre.unapi' = ANY (includes)) THEN
  83         output := REGEXP_REPLACE(
  84             tmp_xml,
  85             '</' || top_el || '>(.*?)',
  86             XMLELEMENT(
  87                 name abbr,
  88                 XMLATTRIBUTES(
  89                     'http://www.w3.org/1999/xhtml' AS xmlns,
  90                     'unapi-id' AS class,
  91                     'tag:open-ils.org:U2@bre/' || obj_id || '/' || org AS title
  92                 )
  93             )::TEXT || '</' || top_el || E'>\\1'
  94         );
  95     ELSE
  96         output := tmp_xml;
  97     END IF;
  98
  99     output := REGEXP_REPLACE(output::TEXT,E'>\\s+<','><','gs')::XML;
 100     RETURN output;
 101 END;
 102 $F$ LANGUAGE PLPGSQL;
 103
 104 CREATE OR REPLACE FUNCTION unapi.holdings_xml (bid BIGINT, ouid INT, org TEXT, depth INT DEFAULT NULL, includes TEXT[] DEFAULT NULL::TEXT[], slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE) RETURNS XML AS $F$
 105      SELECT  XMLELEMENT(
 106                  name holdings,
 107                  XMLATTRIBUTES(
 108                     CASE WHEN $8 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
 109                     CASE WHEN ('bre' = ANY ($5)) THEN 'tag:open-ils.org:U2@bre/' || $1 || '/' || $3 ELSE NULL END AS id
 110                  ),
 111                  XMLELEMENT(
 112                      name counts,
 113                      (SELECT  XMLAGG(XMLELEMENT::XML) FROM (
 114                          SELECT  XMLELEMENT(
 115                                      name count,
 116                                      XMLATTRIBUTES('public' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
 117                                  )::text
 118                            FROM  asset.opac_ou_record_copy_count($2,  $1)
 119                                      UNION
 120                          SELECT  XMLELEMENT(
 121                                      name count,
 122                                      XMLATTRIBUTES('staff' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
 123                                  )::text
 124                            FROM  asset.staff_ou_record_copy_count($2, $1)
 125                                      ORDER BY 1
 126                      )x)
 127                  ),
 128                  CASE
 129                      WHEN ('bmp' = ANY ($5)) THEN
 130                         XMLELEMENT(
 131                             name monograph_parts,
 132                             (SELECT XMLAGG(bmp) FROM (
 133                                 SELECT  unapi.bmp( id, 'xml', 'monograph_part', evergreen.array_remove_item_by_value( evergreen.array_remove_item_by_value($5,'bre'), 'holdings_xml'), $3, $4, $6, $7, FALSE)
 134                                   FROM  biblio.monograph_part
 135                                   WHERE record = $1
 136                             )x)
 137                         )
 138                      ELSE NULL
 139                  END,
 140                  XMLELEMENT(
 141                      name volumes,
 142                      (SELECT XMLAGG(acn) FROM (
 143                         SELECT  unapi.acn(acn.id,'xml','volume',array_remove_item_by_value( evergreen.array_remove_item_by_value($5,'holdings_xml'),'bre'), $3, $4, $6, $7, FALSE)
 144                           FROM  asset.call_number acn
 145                           WHERE acn.record = $1
 146                                 AND EXISTS (
 147                                     SELECT  1
 148                                       FROM  asset.copy acp
 149                                             JOIN actor.org_unit_descendants(
 150                                                 $2,
 151                                                 (COALESCE(
 152                                                     $4,
 153                                                     (SELECT aout.depth
 154                                                       FROM  actor.org_unit_type aout
 155                                                             JOIN actor.org_unit aou ON (aou.ou_type = aout.id AND aou.id = $2)
 156                                                     )
 157                                                 ))
 158                                             ) aoud ON (acp.circ_lib = aoud.id)
 159                                       LIMIT 1
 160                                )
 161                           ORDER BY label_sortkey
 162                           LIMIT $6
 163                           OFFSET $7
 164                      )x)
 165                  ),
 166                  CASE WHEN ('ssub' = ANY ($5)) THEN
 167                      XMLELEMENT(
 168                          name subscriptions,
 169                          (SELECT XMLAGG(ssub) FROM (
 170                             SELECT  unapi.ssub(id,'xml','subscription','{}'::TEXT[], $3, $4, $6, $7, FALSE)
 171                               FROM  serial.subscription
 172                               WHERE record_entry = $1
 173                         )x)
 174                      )
 175                  ELSE NULL END,
 176                  CASE WHEN ('acp' = ANY ($5)) THEN
 177                      XMLELEMENT(
 178                          name foreign_copies,
 179                          (SELECT XMLAGG(acp) FROM (
 180                             SELECT  unapi.acp(p.target_copy,'xml','copy','{}'::TEXT[], $3, $4, $6, $7, FALSE)
 181                               FROM  biblio.peer_bib_copy_map p
 182                                     JOIN asset.copy c ON (p.target_copy = c.id)
 183                               WHERE NOT c.deleted AND peer_record = $1
 184                         )x)
 185                      )
 186                  ELSE NULL END
 187              );
 188 $F$ LANGUAGE SQL;
 189
 190 CREATE OR REPLACE FUNCTION unapi.ssub ( obj_id BIGINT, format TEXT,  ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$
 191         SELECT  XMLELEMENT(
 192                     name subscription,
 193                     XMLATTRIBUTES(
 194                         CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
 195                         'tag:open-ils.org:U2@ssub/' || id AS id,
 196                         start_date AS start, end_date AS end, expected_date_offset
 197                     ),
 198                     unapi.aou( owning_lib, $2, 'owning_lib', evergreen.array_remove_item_by_value($4,'ssub'), $5, $6, $7, $8),
 199                     XMLELEMENT( name distributions,
 200                         CASE
 201                             WHEN ('sdist' = ANY ($4)) THEN
 202                                 (SELECT XMLAGG(sdist) FROM (
 203                                     SELECT  unapi.sdist( id, 'xml', 'distribution', evergreen.array_remove_item_by_value($4,'ssub'), $5, $6, $7, $8, FALSE)
 204                                       FROM  serial.distribution
 205                                       WHERE subscription = ssub.id
 206                                 )x)
 207                             ELSE NULL
 208                         END
 209                     )
 210                 )
 211           FROM  serial.subscription ssub
 212           WHERE id = $1
 213           GROUP BY id, start_date, end_date, expected_date_offset, owning_lib;
 214 $F$ LANGUAGE SQL;
 215
 216 CREATE OR REPLACE FUNCTION unapi.sdist ( obj_id BIGINT, format TEXT,  ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$
 217         SELECT  XMLELEMENT(
 218                     name distribution,
 219                     XMLATTRIBUTES(
 220                         CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
 221                         'tag:open-ils.org:U2@sdist/' || id AS id,
 222                         'tag:open-ils.org:U2@acn/' || receive_call_number AS receive_call_number,
 223                         'tag:open-ils.org:U2@acn/' || bind_call_number AS bind_call_number,
 224                         unit_label_prefix, label, unit_label_suffix, summary_method
 225                     ),
 226                     unapi.aou( holding_lib, $2, 'holding_lib', evergreen.array_remove_item_by_value($4,'sdist'), $5, $6, $7, $8),
 227                     CASE WHEN subscription IS NOT NULL AND ('ssub' = ANY ($4)) THEN unapi.ssub( subscription, 'xml', 'subscription', evergreen.array_remove_item_by_value($4,'sdist'), $5, $6, $7, $8, FALSE) ELSE NULL END,
 228                     XMLELEMENT( name streams,
 229                         CASE
 230                             WHEN ('sstr' = ANY ($4)) THEN
 231                                 (SELECT XMLAGG(sstr) FROM (
 232                                     SELECT  unapi.sstr( id, 'xml', 'stream', evergreen.array_remove_item_by_value($4,'sdist'), $5, $6, $7, $8, FALSE)
 233                                       FROM  serial.stream
 234                                       WHERE distribution = sdist.id
 235                                 )x)
 236                             ELSE NULL
 237                         END
 238                     ),
 239                     XMLELEMENT( name summaries,
 240                         CASE
 241                             WHEN ('ssum' = ANY ($4)) THEN
 242                                 (SELECT XMLAGG(sbsum) FROM (
 243                                     SELECT  unapi.sbsum( id, 'xml', 'serial_summary', evergreen.array_remove_item_by_value($4,'sdist'), $5, $6, $7, $8, FALSE)
 244                                       FROM  serial.basic_summary
 245                                       WHERE distribution = sdist.id
 246                                 )x)
 247                             ELSE NULL
 248                         END,
 249                         CASE
 250                             WHEN ('ssum' = ANY ($4)) THEN
 251                                 (SELECT XMLAGG(sisum) FROM (
 252                                     SELECT  unapi.sisum( id, 'xml', 'serial_summary', evergreen.array_remove_item_by_value($4,'sdist'), $5, $6, $7, $8, FALSE)
 253                                       FROM  serial.index_summary
 254                                       WHERE distribution = sdist.id
 255                                 )x)
 256                             ELSE NULL
 257                         END,
 258                         CASE
 259                             WHEN ('ssum' = ANY ($4)) THEN
 260                                 (SELECT XMLAGG(sssum) FROM (
 261                                     SELECT  unapi.sssum( id, 'xml', 'serial_summary', evergreen.array_remove_item_by_value($4,'sdist'), $5, $6, $7, $8, FALSE)
 262                                       FROM  serial.supplement_summary
 263                                       WHERE distribution = sdist.id
 264                                 )x)
 265                             ELSE NULL
 266                         END
 267                     )
 268                 )
 269           FROM  serial.distribution sdist
 270           WHERE id = $1
 271           GROUP BY id, label, unit_label_prefix, unit_label_suffix, holding_lib, summary_method, subscription, receive_call_number, bind_call_number;
 272 $F$ LANGUAGE SQL;
 273
 274 CREATE OR REPLACE FUNCTION unapi.sstr ( obj_id BIGINT, format TEXT,  ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$
 275     SELECT  XMLELEMENT(
 276                 name stream,
 277                 XMLATTRIBUTES(
 278                     CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
 279                     'tag:open-ils.org:U2@sstr/' || id AS id,
 280                     routing_label
 281                 ),
 282                 CASE WHEN distribution IS NOT NULL AND ('sdist' = ANY ($4)) THEN unapi.sssum( distribution, 'xml', 'distribtion', evergreen.array_remove_item_by_value($4,'sstr'), $5, $6, $7, $8, FALSE) ELSE NULL END,
 283                 XMLELEMENT( name items,
 284                     CASE
 285                         WHEN ('sitem' = ANY ($4)) THEN
 286                             (SELECT XMLAGG(sitem) FROM (
 287                                 SELECT  unapi.sitem( id, 'xml', 'serial_item', evergreen.array_remove_item_by_value($4,'sstr'), $5, $6, $7, $8, FALSE)
 288                                   FROM  serial.item
 289                                   WHERE stream = sstr.id
 290                             )x)
 291                         ELSE NULL
 292                     END
 293                 )
 294             )
 295       FROM  serial.stream sstr
 296       WHERE id = $1
 297       GROUP BY id, routing_label, distribution;
 298 $F$ LANGUAGE SQL;
 299
 300 CREATE OR REPLACE FUNCTION unapi.siss ( obj_id BIGINT, format TEXT,  ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$
 301     SELECT  XMLELEMENT(
 302                 name issuance,
 303                 XMLATTRIBUTES(
 304                     CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
 305                     'tag:open-ils.org:U2@siss/' || id AS id,
 306                     create_date, edit_date, label, date_published,
 307                     holding_code, holding_type, holding_link_id
 308                 ),
 309                 CASE WHEN subscription IS NOT NULL AND ('ssub' = ANY ($4)) THEN unapi.ssub( subscription, 'xml', 'subscription', evergreen.array_remove_item_by_value($4,'siss'), $5, $6, $7, $8, FALSE) ELSE NULL END,
 310                 XMLELEMENT( name items,
 311                     CASE
 312                         WHEN ('sitem' = ANY ($4)) THEN
 313                             (SELECT XMLAGG(sitem) FROM (
 314                                 SELECT  unapi.sitem( id, 'xml', 'serial_item', evergreen.array_remove_item_by_value($4,'siss'), $5, $6, $7, $8, FALSE)
 315                                   FROM  serial.item
 316                                   WHERE issuance = sstr.id
 317                             )x)
 318                         ELSE NULL
 319                     END
 320                 )
 321             )
 322       FROM  serial.issuance sstr
 323       WHERE id = $1
 324       GROUP BY id, create_date, edit_date, label, date_published, holding_code, holding_type, holding_link_id, subscription;
 325 $F$ LANGUAGE SQL;
 326
 327 CREATE OR REPLACE FUNCTION unapi.sitem ( obj_id BIGINT, format TEXT,  ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$
 328         SELECT  XMLELEMENT(
 329                     name serial_item,
 330                     XMLATTRIBUTES(
 331                         CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
 332                         'tag:open-ils.org:U2@sitem/' || id AS id,
 333                         'tag:open-ils.org:U2@siss/' || issuance AS issuance,
 334                         date_expected, date_received
 335                     ),
 336                     CASE WHEN issuance IS NOT NULL AND ('siss' = ANY ($4)) THEN unapi.siss( issuance, $2, 'issuance', evergreen.array_remove_item_by_value($4,'sitem'), $5, $6, $7, $8, FALSE) ELSE NULL END,
 337                     CASE WHEN stream IS NOT NULL AND ('sstr' = ANY ($4)) THEN unapi.sstr( stream, $2, 'stream', evergreen.array_remove_item_by_value($4,'sitem'), $5, $6, $7, $8, FALSE) ELSE NULL END,
 338                     CASE WHEN unit IS NOT NULL AND ('sunit' = ANY ($4)) THEN unapi.sunit( stream, $2, 'serial_unit', evergreen.array_remove_item_by_value($4,'sitem'), $5, $6, $7, $8, FALSE) ELSE NULL END,
 339                     CASE WHEN uri IS NOT NULL AND ('auri' = ANY ($4)) THEN unapi.auri( uri, $2, 'uri', evergreen.array_remove_item_by_value($4,'sitem'), $5, $6, $7, $8, FALSE) ELSE NULL END
 340 --                    XMLELEMENT( name notes,
 341 --                        CASE
 342 --                            WHEN ('acpn' = ANY ($4)) THEN
 343 --                                (SELECT XMLAGG(acpn) FROM (
 344 --                                    SELECT  unapi.acpn( id, 'xml', 'copy_note', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8)
 345 --                                      FROM  asset.copy_note
 346 --                                      WHERE owning_copy = cp.id AND pub
 347 --                                )x)
 348 --                            ELSE NULL
 349 --                        END
 350 --                    )
 351                 )
 352           FROM  serial.item sitem
 353           WHERE id = $1;
 354 $F$ LANGUAGE SQL;
 355
 356
 357 CREATE OR REPLACE FUNCTION unapi.bmp ( obj_id BIGINT, format TEXT,  ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$
 358         SELECT  XMLELEMENT(
 359                     name monograph_part,
 360                     XMLATTRIBUTES(
 361                         CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
 362                         'tag:open-ils.org:U2@bmp/' || id AS id,
 363                         id AS ident,
 364                         label,
 365                         label_sortkey,
 366                         'tag:open-ils.org:U2@bre/' || record AS record
 367                     ),
 368                     CASE
 369                         WHEN ('acp' = ANY ($4)) THEN
 370                             XMLELEMENT( name copies,
 371                                 (SELECT XMLAGG(acp) FROM (
 372                                     SELECT  unapi.acp( cp.id, 'xml', 'copy', evergreen.array_remove_item_by_value($4,'bmp'), $5, $6, $7, $8, FALSE)
 373                                       FROM  asset.copy cp
 374                                             JOIN asset.copy_part_map cpm ON (cpm.target_copy = cp.id)
 375                                       WHERE cpm.part = $1
 376                                       ORDER BY COALESCE(cp.copy_number,0), cp.barcode
 377                                       LIMIT $7
 378                                       OFFSET $8
 379                                 )x)
 380                             )
 381                         ELSE NULL
 382                     END,
 383                     CASE WHEN ('bre' = ANY ($4)) THEN unapi.bre( record, 'marcxml', 'record', evergreen.array_remove_item_by_value($4,'bmp'), $5, $6, $7, $8, FALSE) ELSE NULL END
 384                 )
 385           FROM  biblio.monograph_part
 386           WHERE id = $1
 387           GROUP BY id, label, label_sortkey, record;
 388 $F$ LANGUAGE SQL;
 389
 390 CREATE OR REPLACE FUNCTION unapi.acp ( obj_id BIGINT, format TEXT,  ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$
 391         SELECT  XMLELEMENT(
 392                     name copy,
 393                     XMLATTRIBUTES(
 394                         CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
 395                         'tag:open-ils.org:U2@acp/' || id AS id,
 396                         create_date, edit_date, copy_number, circulate, deposit,
 397                         ref, holdable, deleted, deposit_amount, price, barcode,
 398                         circ_modifier, circ_as_type, opac_visible
 399                     ),
 400                     unapi.ccs( status, $2, 'status', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE),
 401                     unapi.acl( location, $2, 'location', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE),
 402                     unapi.aou( circ_lib, $2, 'circ_lib', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8),
 403                     unapi.aou( circ_lib, $2, 'circlib', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8),
 404                     CASE WHEN ('acn' = ANY ($4)) THEN unapi.acn( call_number, $2, 'call_number', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE) ELSE NULL END,
 405                     XMLELEMENT( name copy_notes,
 406                         CASE
 407                             WHEN ('acpn' = ANY ($4)) THEN
 408                                 (SELECT XMLAGG(acpn) FROM (
 409                                     SELECT  unapi.acpn( id, 'xml', 'copy_note', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE)
 410                                       FROM  asset.copy_note
 411                                       WHERE owning_copy = cp.id AND pub
 412                                 )x)
 413                             ELSE NULL
 414                         END
 415                     ),
 416                     XMLELEMENT( name statcats,
 417                         CASE
 418                             WHEN ('ascecm' = ANY ($4)) THEN
 419                                 (SELECT XMLAGG(ascecm) FROM (
 420                                     SELECT  unapi.ascecm( stat_cat_entry, 'xml', 'statcat', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE)
 421                                       FROM  asset.stat_cat_entry_copy_map
 422                                       WHERE owning_copy = cp.id
 423                                 )x)
 424                             ELSE NULL
 425                         END
 426                     ),
 427                     XMLELEMENT( name foreign_records,
 428                         CASE
 429                             WHEN ('bre' = ANY ($4)) THEN
 430                                 (SELECT XMLAGG(bre) FROM (
 431                                     SELECT  unapi.bre(peer_record,'marcxml','record','{}'::TEXT[], $5, $6, $7, $8, FALSE)
 432                                       FROM  biblio.peer_bib_copy_map
 433                                       WHERE target_copy = cp.id
 434                                 )x)
 435                             ELSE NULL
 436                         END
 437
 438                     ),
 439                     CASE
 440                         WHEN ('bmp' = ANY ($4)) THEN
 441                             XMLELEMENT( name monograph_parts,
 442                                 (SELECT XMLAGG(bmp) FROM (
 443                                     SELECT  unapi.bmp( part, 'xml', 'monograph_part', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE)
 444                                       FROM  asset.copy_part_map
 445                                       WHERE target_copy = cp.id
 446                                 )x)
 447                             )
 448                         ELSE NULL
 449                     END
 450                 )
 451           FROM  asset.copy cp
 452           WHERE id = $1
 453           GROUP BY id, status, location, circ_lib, call_number, create_date, edit_date, copy_number, circulate, deposit, ref, holdable, deleted, deposit_amount, price, barcode, circ_modifier, circ_as_type, opac_visible;
 454 $F$ LANGUAGE SQL;
 455
 456 CREATE OR REPLACE FUNCTION unapi.sunit ( obj_id BIGINT, format TEXT,  ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit INT DEFAULT NULL, soffset INT DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$
 457         SELECT  XMLELEMENT(
 458                     name serial_unit,
 459                     XMLATTRIBUTES(
 460                         CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
 461                         'tag:open-ils.org:U2@acp/' || id AS id,
 462                         create_date, edit_date, copy_number, circulate, deposit,
 463                         ref, holdable, deleted, deposit_amount, price, barcode,
 464                         circ_modifier, circ_as_type, opac_visible, status_changed_time,
 465                         floating, mint_condition, detailed_contents, sort_key, summary_contents, cost
 466                     ),
 467                     unapi.ccs( status, $2, 'status', evergreen.array_remove_item_by_value( evergreen.array_remove_item_by_value($4,'acp'),'sunit'), $5, $6, $7, $8, FALSE),
 468                     unapi.acl( location, $2, 'location', evergreen.array_remove_item_by_value( evergreen.array_remove_item_by_value($4,'acp'),'sunit'), $5, $6, $7, $8, FALSE),
 469                     unapi.aou( circ_lib, $2, 'circ_lib', evergreen.array_remove_item_by_value( evergreen.array_remove_item_by_value($4,'acp'),'sunit'), $5, $6, $7, $8),
 470                     unapi.aou( circ_lib, $2, 'circlib', evergreen.array_remove_item_by_value( evergreen.array_remove_item_by_value($4,'acp'),'sunit'), $5, $6, $7, $8),
 471                     CASE WHEN ('acn' = ANY ($4)) THEN unapi.acn( call_number, $2, 'call_number', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE) ELSE NULL END,
 472                     XMLELEMENT( name copy_notes,
 473                         CASE
 474                             WHEN ('acpn' = ANY ($4)) THEN
 475                                 (SELECT XMLAGG(acpn) FROM (
 476                                     SELECT  unapi.acpn( id, 'xml', 'copy_note', evergreen.array_remove_item_by_value( evergreen.array_remove_item_by_value($4,'acp'),'sunit'), $5, $6, $7, $8, FALSE)
 477                                       FROM  asset.copy_note
 478                                       WHERE owning_copy = cp.id AND pub
 479                                 )x)
 480                             ELSE NULL
 481                         END
 482                     ),
 483                     XMLELEMENT( name statcats,
 484                         CASE
 485                             WHEN ('ascecm' = ANY ($4)) THEN
 486                                 (SELECT XMLAGG(ascecm) FROM (
 487                                     SELECT  unapi.ascecm( stat_cat_entry, 'xml', 'statcat', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE)
 488                                       FROM  asset.stat_cat_entry_copy_map
 489                                       WHERE owning_copy = cp.id
 490                                 )x)
 491                             ELSE NULL
 492                         END
 493                     ),
 494                     XMLELEMENT( name foreign_records,
 495                         CASE
 496                             WHEN ('bre' = ANY ($4)) THEN
 497                                 (SELECT XMLAGG(bre) FROM (
 498                                     SELECT  unapi.bre(peer_record,'marcxml','record','{}'::TEXT[], $5, $6, $7, $8, FALSE)
 499                                       FROM  biblio.peer_bib_copy_map
 500                                       WHERE target_copy = cp.id
 501                                 )x)
 502                             ELSE NULL
 503                         END
 504
 505                     ),
 506                     CASE
 507                         WHEN ('bmp' = ANY ($4)) THEN
 508                             XMLELEMENT( name monograph_parts,
 509                                 (SELECT XMLAGG(bmp) FROM (
 510                                     SELECT  unapi.bmp( part, 'xml', 'monograph_part', evergreen.array_remove_item_by_value($4,'acp'), $5, $6, $7, $8, FALSE)
 511                                       FROM  asset.copy_part_map
 512                                       WHERE target_copy = cp.id
 513                                 )x)
 514                             )
 515                         ELSE NULL
 516                     END
 517                 )
 518           FROM  serial.unit cp
 519           WHERE id = $1
 520           GROUP BY  id, status, location, circ_lib, call_number, create_date, edit_date, copy_number, circulate, floating, mint_condition,
 521                     deposit, ref, holdable, deleted, deposit_amount, price, barcode, circ_modifier, circ_as_type, opac_visible, status_changed_time, detailed_contents, sort_key, summary_contents, cost;
 522 $F$ LANGUAGE SQL;
 523
 524 COMMIT;
 525