peppol_invoice_prepaid_payment_v.sql

peppol_invoice_prepaid_payment_v.sql

create or replace view peppol_invoice_prepaid_payment_v
as
select 
    rownum seq_id,
    d.id invoice_id,
    c."ID",
    c."PaidAmount",
    c."ReceivedDate",
    c."InstructionID"
from peppol_documents d,
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"
    ),
    '/Invoice/cac:PrepaidPayment[*]'
    passing d.invoice
    columns
        "ID"                    varchar2(4000)  path 'cbc:ID',
        "PaidAmount"            number          path 'cbc:PaidAmount',
        "ReceivedDate"          varchar2(4000)  path 'cbc:ReceivedDate',
        "InstructionID"         varchar2(4000)  path 'cbc:InstructionID'
) c;