peppol_invoice_invoice_line_allowance_charge_v.sql

peppol_invoice_invoice_line_allowance_charge_v.sql

create or replace view peppol_invoice_invoice_line_allowance_charge_v
as
select
    rownum seq_id,
    v.invoice_id,
    v."ID" "InvoiceLine_ID",
    c."ChargeIndicator",
    c."AllowanceChargeReasonCode",
    c."AllowanceChargeReason",
    c."MultiplierFactorNumeric",
    c."Amount",
    c."Amount_@currencyID",
    c."BaseAmount",
    c."BaseAmount_@currencyID"
from peppol_invoice_invoice_line_v v,
xmltable(
    xmlnamespaces(
        default 'urn:oasis:names:specification:ubl:schema:xsd:Invoice-2',
        'urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2' as "cbc",
        'urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2' as "cac",
        'urn:un:unece:uncefact:documentation:2' as "ccts",
        'urn:oasis:names:specification:ubl:schema:xsd:CommonExtensionComponents-2' as "ext",
        'urn:oasis:names:specification:ubl:schema:xsd:QualifiedDatatypes-2' as "qdt",
        'urn:un:unece:uncefact:data:specification:UnqualifiedDataTypesSchemaModule:2' as "udt",
        'http://www.w3.org/2001/XMLSchema' as "xsd",
        'http://www.w3.org/2001/XMLSchema-instance' as "xsi"
    ),
    '/cac:InvoiceLine/cac:AllowanceCharge[*]'
    passing v."InvoiceLine"
    columns
        "ChargeIndicator"               varchar2(4000)  path 'cbc:ChargeIndicator',
        "AllowanceChargeReasonCode"     varchar2(4000)  path 'cbc:AllowanceChargeReasonCode',
        "AllowanceChargeReason"         varchar2(4000)  path 'cbc:AllowanceChargeReason',
        "MultiplierFactorNumeric"       varchar2(4000)  path 'cbc:MultiplierFactorNumeric',
        "Amount"                        number          path 'cbc:Amount',
        "Amount_@currencyID"            varchar2(4000)  path 'cbc:Amount/@currencyID',
        "BaseAmount"                    number          path 'cbc:BaseAmount',
        "BaseAmount_@currencyID"        varchar2(4000)  path 'cbc:BaseAmount/@currencyID'
) c;