表形式のデータからPeppolのデジタルインボイスを生成する

先日の記事Peppolのデジタルインボイスを表形式で表示するにて、XMLのデータを読み込んで表形式に変換するビューを作成しました。今度は逆に表からXMLを生成するビューを作成します。

デジタルインボイスを表形式で表示するために、２０のビューを作成しています。それらを逆にXMLとして表示するためのビューとして、以下の２０のビューを作成します。

PEPPOL_INVOICE_XV

PEPPOL_INVOICE_BILLING_REFERENCE_XV

PEPPOL_INVOICE_ADDITIONAL_DOCUMENT_REFERENCE_XV

PEPPOL_INVOICE_ACCOUNTING_SUPPLIER_PARTY_XV

PEPPOL_INVOICE_ACCOUNTING_CUSTOMER_PARTY_XV

PEPPOL_INVOICE_PAYEE_PARTY_XV

PEPPOL_INVOICE_TAX_REPRESENTATIVE_PARTY_XV

PEPPOL_INVOICE_DELIVERY_XV

PEPPOL_INVOICE_PAYMENT_MEANS_XV

PEPPOL_INVOICE_PAYMENT_TERMS_XV

PEPPOL_INVOICE_PREPAID_PAYMENT_XV

PEPPOL_INVOICE_ALLOWANCE_CHARGE_XV

PEPPOL_INVOICE_TAX_TOTAL_XV

PEPPOL_INVOICE_TAX_TOTAL_TAX_SUBTOTAL_XV

PEPPOL_INVOICE_INVOICE_LINE_XV

PEPPOL_INVOICE_INVOICE_LINE_DOCUMENT_REFERENCE_XV

PEPPOL_INVOICE_INVOICE_LINE_ALLOWANCE_CHARGE_XV

PEPPOL_INVOICE_INVOICE_LINE_ITEM_ADDITIONAL_ITEM_PROPERTY_XV

PEPPOL_INVOICE_INVOICE_LINE_ITEM_CLASSIFIED_TAX_CATEGORY_XV

PEPPOL_INVOICE_INVOICE_LINE_ITEM_COMMODITY_CLASSIFICATION_XV

ビューのDDLは記事の末尾に添付します。

サンプルのデジタルインボイスは、表PEPPOL_DOCUMENTSの列INVOICEにXMLTYPEとして保存しています。表形式にしたデータから再度XML形式のデータを生成し、元のデジタルインボイスと比較することで、XMLと表の間の変換が正しく行われていることを確認します。

表PEPPOL_DOCUMENTSに再変換したXMLを保存する列RESULTを追加します。

alter table peppol_documents add (result xmltype);

列RESULTに再変換したデジタルインボイスを保存します。

update peppol_documents d
set
d.result =
(
    select i."Invoice" from peppol_invoice_xv i 
    where d.id = i.invoice_id
)
;

列INVOICEと列RESULTの差分を確認します。ファンクションXMLDIFFを使用します。

select xmldiff(invoice,result) from peppol_documents

差分の情報が表示されますが、以下のようにほとんどはコメントの差分です。

<xd:delete-node xd:node-type="comment" xd:xpath="/oraxdfns_0:Invoice[1]/comment()[1]"/>

以下のSELECT文を実行し、コメントに関する差分を除いて表示します。

	select
	d.id,
	d.content_filename,
	c.change.getClobVal()
	from
	(
	select
	id,
	content_filename,
	xmldiff(invoice,result) diff
	from peppol_documents
	) d,
	xmltable(
	xmlnamespaces(
	default 'http://xmlns.oracle.com/xdb/xdiff.xsd',
	'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",
	'http://xmlns.oracle.com/xdb/xdiff.xsd' as "xd",
	'urn:oasis:names:specification:ubl:schema:xsd:Invoice-2' as "oraxdfns_0"
	),
	'//*[@xd:node-type != ''comment'']'
	passing d.diff
	columns
	change xmltype path '.'
	) c;

view raw xmldiff.sql hosted with ❤ by GitHub

結果が０行であれば、元のXMLと生成したXMLに違いがないと言えます。

XMLDIFFでは、XML要素の配列の出現順序が異なると差分として報告されます。本来であれば、配列の出現順序を気に掛ける必要はありません。今回はデバッグのためサンプルのデジタルインボイスと完全に一致していることが確認できるように、XMLから表形式にするビューにrownum（列名としてはseq_id）を含めて出現順序を参照できるようにしています。逆に表形式からXMLにする際には、ファンクションXMLAGGの引数としてorder by seq_idを加えています。

上記の調整を行ったビューを使って差分の確認をやり直し、結果が０行（つまりサンプルと一致している）になることは確認しました。

あくまでデジタル庁が提供しているサンプルのデータによる確認を行っただけにはなりますが、今時JSONではなくXMLを操作するコードを書くのも大変なので、手始めになるコードを書いてみました。

Oracle APEXのアプリケーション作成の参考になれば幸いです。

完

ビューを作成するDDL

	create or replace view peppol_invoice_xv
	as
	select
	i.invoice_id,
	xmlelement("Invoice",
	xmlattributes(
	'urn:oasis:names:specification:ubl:schema:xsd:Invoice-2' as "xmlns",
	'urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2' as "xmlns:cac",
	'urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2' as "xmlns:cbc",
	'urn:un:unece:uncefact:documentation:2' as "xmlns:ccts",
	'urn:oasis:names:specification:ubl:schema:xsd:CommonExtensionComponents-2' as "xmlns:ext",
	'urn:oasis:names:specification:ubl:schema:xsd:QualifiedDatatypes-2' as "xmlns:qdt",
	'urn:un:unece:uncefact:data:specification:UnqualifiedDataTypesSchemaModule:2' as "xmlns:udt",
	'http://www.w3.org/2001/XMLSchema' as "xmlns:xsd",
	'http://www.w3.org/2001/XMLSchema-instance' as "xmlns:xsi"
	),
	xmlconcat(
	xmlforest(
	i."UBLVersionID" as "cbc:UBLVersionID"
	,i."CustomizationID" as "cbc:CustomizationID"
	,i."ProfileID" as "cbc:ProfileID"
	,i."ID" as "cbc:ID"
	,i."IssueDate" as "cbc:IssueDate"
	,i."IssueTime" as "cbc:IssueTime"
	,i."DueDate" as "cbc:DueDate"
	,i."InvoiceTypeCode" as "cbc:InvoiceTypeCode"
	,i."Note" as "cbc:Note"
	,i."TaxPointDate" as "cbc:TaxPointDate"
	,i."DocumentCurrencyCode" as "cbc:DocumentCurrencyCode"
	,i."TaxCurrencyCode" as "cbc:TaxCurrencyCode"
	,i."AccountingCost" as "cbc:AccountingCost"
	,i."BuyerReference" as "cbc:BuyerReference"
	,xmlforest(
	i."InvoicePeriod_StartDate" as "cbc:StartDate"
	,i."InvoicePeriod_EndDate" as "cbc:EndDate"
	,i."InvoicePeriod_DescriptionCode" as "cbc:DescriptionCode"
	) as "cac:InvoicePeriod"
	,xmlforest(
	i."OrderReference_ID" as "cbc:ID"
	,i."OrderReference_SalesOrderID" as "cbc:SalesOrderID"
	) as "cac:OrderReference"
	)
	,ibr."BillingReference"
	,xmlforest(
	xmlforest(
	i."DespatchDocumentReference_ID" as "cbc:ID"
	) as "cac:DespatchDocumentReference"
	,xmlforest(
	i."ReceiptDocumentReference_ID" as "cbc:ID"
	) as "cac:ReceiptDocumentReference"
	,xmlforest(
	i."OriginatorDocumentReference_ID" as "cbc:ID"
	) as "cac:OriginatorDocumentReference"
	,xmlforest(
	i."ContractDocumentReference_ID" as "cbc:ID"
	) as "cac:ContractDocumentReference"
	)
	,iadr."AdditionalDocumentReference"
	,xmlforest(
	xmlforest(
	i."ProjectReference_ID" as "cbc:ID"
	) as "cac:ProjectReference"
	)
	,iasp."AccountingSupplierParty"
	,iacp."AccountingCustomerParty"
	,ipp."PayeeParty"
	,itrp."TaxRepresentativeParty"
	,id."Delivery"
	,ipm."PaymentMeans"
	,ipt."PaymentTerms"
	,ippp."PrepaidPayment"
	,iacg."AllowanceCharge"
	,ittl."TaxTotal"
	,xmlelement("cac:LegalMonetaryTotal",
	xmlconcat(
	case when i."LegalMonetaryTotal_LineExtensionAmount" is null then null else xmlelement("cbc:LineExtensionAmount",
	xmlattributes(i."LegalMonetaryTotal_LineExtensionAmount_@currencyID" as "currencyID"),
	i."LegalMonetaryTotal_LineExtensionAmount"
	) end,
	case when i."LegalMonetaryTotal_TaxExclusiveAmount" is null then null else xmlelement("cbc:TaxExclusiveAmount",
	xmlattributes(i."LegalMonetaryTotal_TaxExclusiveAmount_@currencyID" as "currencyID"),
	i."LegalMonetaryTotal_TaxExclusiveAmount"
	) end,
	case when i."LegalMonetaryTotal_TaxInclusiveAmount" is null then null else xmlelement("cbc:TaxInclusiveAmount",
	xmlattributes(i."LegalMonetaryTotal_TaxInclusiveAmount_@currencyID" as "currencyID"),
	i."LegalMonetaryTotal_TaxInclusiveAmount"
	) end,
	case when i."LegalMonetaryTotal_AllowanceTotalAmount" is null then null else xmlelement("cbc:AllowanceTotalAmount",
	xmlattributes(i."LegalMonetaryTotal_AllowanceTotalAmount_@currencyID" as "currencyID"),
	i."LegalMonetaryTotal_AllowanceTotalAmount"
	) end,
	case when i."LegalMonetaryTotal_ChargeTotalAmount" is null then null else xmlelement("cbc:ChargeTotalAmount",
	xmlattributes(i."LegalMonetaryTotal_ChargeTotalAmount_@currencyID" as "currencyID"),
	i."LegalMonetaryTotal_ChargeTotalAmount"
	) end,
	case when i."LegalMonetaryTotal_PrepaidAmount" is null then null else xmlelement("cbc:PrepaidAmount",
	xmlattributes(i."LegalMonetaryTotal_PrepaidAmount_@currencyID" as "currencyID"),
	i."LegalMonetaryTotal_PrepaidAmount"
	) end,
	case when i."LegalMonetaryTotal_PayableRoundingAmount" is null then null else xmlelement("cbc:PayableRoundingAmount",
	xmlattributes(i."LegalMonetaryTotal_PayableRoundingAmount_@currencyID" as "currencyID"),
	i."LegalMonetaryTotal_PayableRoundingAmount"
	) end,
	case when i."LegalMonetaryTotal_PayableAmount" is null then null else xmlelement("cbc:PayableAmount",
	xmlattributes(i."LegalMonetaryTotal_PayableAmount_@currencyID" as "currencyID"),
	i."LegalMonetaryTotal_PayableAmount"
	) end
	)
	)
	,iiln."InvoiceLine"
	)
	) as "Invoice"
	from peppol_invoice_v i
	left outer join
	(
	select invoice_id, xmlagg("BillingReference") as "BillingReference"
	from peppol_invoice_billing_reference_xv
	group by invoice_id
	) ibr
	on i.invoice_id = ibr.invoice_id
	left outer join
	(
	select invoice_id, xmlagg("AdditionalDocumentReference" order by seq_id) as "AdditionalDocumentReference"
	from peppol_invoice_additional_document_reference_xv
	group by invoice_id
	) iadr
	on i.invoice_id = iadr.invoice_id
	left outer join peppol_invoice_accounting_supplier_party_xv iasp on i.invoice_id = iasp.invoice_id
	left outer join peppol_invoice_accounting_customer_party_xv iacp on i.invoice_id = iacp.invoice_id
	left outer join peppol_invoice_payee_party_xv ipp on i.invoice_id = ipp.invoice_id
	left outer join peppol_invoice_tax_representative_party_xv itrp on i.invoice_id = itrp.invoice_id
	left outer join peppol_invoice_delivery_xv id on i.invoice_id = id.invoice_id
	left outer join
	(
	select invoice_id, xmlagg("PaymentMeans") as "PaymentMeans"
	from peppol_invoice_payment_means_xv
	group by invoice_id
	) ipm
	on i.invoice_id = ipm.invoice_id
	left outer join
	(
	select invoice_id, xmlagg("PaymentTerms") as "PaymentTerms"
	from peppol_invoice_payment_terms_xv
	group by invoice_id
	) ipt
	on i.invoice_id = ipt.invoice_id
	left outer join
	(
	select invoice_id, xmlagg("PrepaidPayment") as "PrepaidPayment"
	from peppol_invoice_prepaid_payment_xv
	group by invoice_id
	) ippp
	on i.invoice_id = ippp.invoice_id
	left outer join
	(
	select invoice_id, xmlagg("AllowanceCharge") as "AllowanceCharge"
	from peppol_invoice_allowance_charge_xv
	group by invoice_id
	) iacg
	on i.invoice_id = iacg.invoice_id
	left outer join
	(
	select invoice_id, xmlagg("TaxTotal") as "TaxTotal"
	from peppol_invoice_tax_total_xv
	group by invoice_id
	) ittl
	on i.invoice_id = ittl.invoice_id
	join
	(
	select invoice_id, xmlagg("InvoiceLine") as "InvoiceLine"
	from peppol_invoice_invoice_line_xv
	group by invoice_id
	) iiln
	on i.invoice_id = iiln.invoice_id
	;

view raw peppol_invoice_xv.sql hosted with ❤ by GitHub

	create or replace view peppol_invoice_billing_reference_xv
	as
	select
	seq_id,
	invoice_id,
	xmlelement("cac:BillingReference",
	xmlforest(
	xmlforest(
	"InvoiceDocumentReference_ID" as "cbc:ID"
	,"InvoiceDocumentReference_IssueDate" as "cbc:IssueDate"
	) as "cac:InvoiceDocumentReference"
	)
	) as "BillingReference"
	from peppol_invoice_billing_reference_v;

view raw peppol_invoice_billing_reference_xv.sql hosted with ❤ by GitHub

	create or replace view peppol_invoice_additional_document_reference_xv
	as
	select
	seq_id, -- デバッグ用
	invoice_id,
	case when "DocumentTypeCode" = '130' then
	xmlelement("cac:AdditionalDocumentReference",
	xmlconcat(
	xmlelement("cbc:ID",
	xmlattributes("ID_@schemeID" as "schemeID"),
	"ID"
	),
	xmlforest(
	"DocumentTypeCode" as "cbc:DocumentTypeCode"
	)
	)
	)
	else
	xmlelement("cac:AdditionalDocumentReference",
	xmlconcat(
	xmlforest(
	"ID" as "cbc:ID"
	,"DocumentDescription" as "cbc:DocumentDescription"
	)
	,case when ("Attachment_EmbeddedDocumentBinaryObject" is null and "Attachment_ExternalReference" is null) then null else xmlelement("cac:Attachment",
	xmlconcat(
	case when "Attachment_EmbeddedDocumentBinaryObject" is null then null else xmlelement("cbc:EmbeddedDocumentBinaryObject",
	xmlattributes(
	"Attachment_EmbeddedDocumentBinaryObject_@filename" as "filename",
	"Attachment_EmbeddedDocumentBinaryObject_@mimeCode" as "mimeCode"
	),
	"Attachment_EmbeddedDocumentBinaryObject"
	) end
	,xmlforest(
	xmlforest(
	"Attachment_ExternalReference" as "cbc:URI"
	) as "cac:ExternalReference"
	)
	)
	) end
	)
	)
	end "AdditionalDocumentReference"
	from peppol_invoice_additional_document_reference_v;

view raw peppol_invoice_additional_document_reference_xv.sql hosted with ❤ by GitHub

	create or replace view peppol_invoice_accounting_supplier_party_xv
	as
	select
	invoice_id
	,xmlelement("cac:AccountingSupplierParty"
	,xmlelement("cac:Party"
	,xmlconcat(
	xmlelement("cbc:EndpointID",
	xmlattributes("Party_EndpointID_@schemeID" as "schemeID")
	,"Party_EndpointID"
	)
	/*
	* PartyIdentificationは @schemeID = "SEPA" 以外では 0..n で、仕様上は複数値になっている。
	* このサンプル・コードは複数値の対応を省略している。
	*/
	,case when "Party_PartyIdentification_ID" is null then null else xmlelement("cac:PartyIdentification",
	xmlelement("cbc:ID",
	xmlattributes("Party_PartyIdentification_ID_@schemeID" as "schemeID"),
	"Party_PartyIdentification_ID"
	)
	) end
	,xmlforest(
	xmlforest(
	"Party_PartyName_Name" as "cbc:Name"
	) as "cac:PartyName"
	)
	,xmlforest(
	xmlforest(
	"Party_PostalAddress_StreetName" as "cbc:StreetName"
	,"Party_PostalAddress_AdditionalStreetName" as "cbc:AdditionalStreetName"
	,"Party_PostalAddress_CityName" as "cbc:CityName"
	,"Party_PostalAddress_PostalZone" as "cbc:PostalZone"
	,"Party_PostalAddress_CountrySubentity" as "cbc:CountrySubentity"
	,xmlforest(
	"Party_PostalAddress_AddressLine_Line" as "cbc:Line"
	) as "cac:AddressLine"
	,xmlforest(
	"Party_PostalAddress_Country_IdentificationCode" as "cbc:IdentificationCode"
	) as "cac:Country"
	) as "cac:PostalAddress"
	)
	,xmlforest(
	xmlforest(
	"Party_PartyTaxScheme_CompanyID" as "cbc:CompanyID"
	,xmlforest(
	"Party_PartyTaxScheme_TaxScheme_ID" as "cbc:ID"
	) as "cac:TaxScheme"
	) as "cac:PartyTaxScheme"
	)
	,xmlelement("cac:PartyLegalEntity",
	xmlconcat(
	xmlforest(
	"Party_PartyLegalEntity_RegistrationName" as "cbc:RegistrationName"
	)
	,case when "Party_PartyLegalEntity_CompanyID" is null then null else xmlelement("cbc:CompanyID",
	xmlattributes("Party_PartyLegalEntity_CompanyID_@schemeID" as "schemeID"),
	"Party_PartyLegalEntity_CompanyID"
	) end
	,xmlforest(
	"Party_PartyLegalEntity_CompanyLegalForm" as "cbc:CompanyLegalForm"
	)
	)
	)
	,xmlforest(
	xmlforest(
	"Party_Contact_Name" as "cbc:Name"
	,"Party_Contact_Telephone" as "cbc:Telephone"
	,"Party_Contact_ElectronicMail" as "cbc:ElectronicMail"
	) as "cac:Contact"
	)
	)
	)
	) as "AccountingSupplierParty"
	from peppol_invoice_accounting_supplier_party_v;

view raw peppol_invoice_accounting_supplier_party_xv.sql hosted with ❤ by GitHub

	create or replace view peppol_invoice_accounting_customer_party_xv
	as
	select
	invoice_id
	,xmlelement("cac:AccountingCustomerParty"
	,xmlelement("cac:Party"
	,xmlconcat(
	xmlelement("cbc:EndpointID",
	xmlattributes("Party_EndpointID_@schemeID" as "schemeID")
	,"Party_EndpointID"
	)
	/*
	* PartyIdentificationは @schemeID = "SEPA" 以外では 0..n で、仕様上は複数値になっている。
	* このサンプル・コードは複数値の対応を省略している。
	*/
	,case when "Party_PartyIdentification_ID" is null then null else xmlelement("cac:PartyIdentification",
	xmlelement("cbc:ID",
	xmlattributes("Party_PartyIdentification_ID_@schemeID" as "schemeID"),
	"Party_PartyIdentification_ID"
	)
	) end
	,xmlforest(
	xmlforest(
	"Party_PartyName_Name" as "cbc:Name"
	) as "cac:PartyName"
	)
	,xmlforest(
	xmlforest(
	"Party_PostalAddress_StreetName" as "cbc:StreetName"
	,"Party_PostalAddress_AdditionalStreetName" as "cbc:AdditionalStreetName"
	,"Party_PostalAddress_CityName" as "cbc:CityName"
	,"Party_PostalAddress_PostalZone" as "cbc:PostalZone"
	,"Party_PostalAddress_CountrySubentity" as "cbc:CountrySubentity"
	,xmlforest(
	"Party_PostalAddress_AddressLine_Line" as "cbc:Line"
	) as "cac:AddressLine"
	,xmlforest(
	"Party_PostalAddress_Country_IdentificationCode" as "cbc:IdentificationCode"
	) as "cac:Country"
	) as "cac:PostalAddress"
	)
	,xmlforest(
	xmlforest(
	"Party_PartyTaxScheme_CompanyID" as "cbc:CompanyID"
	,xmlforest(
	"Party_PartyTaxScheme_TaxScheme_ID" as "cbc:ID"
	) as "cac:TaxScheme"
	) as "cac:PartyTaxScheme"
	)
	,xmlelement("cac:PartyLegalEntity",
	xmlconcat(
	xmlforest(
	"Party_PartyLegalEntity_RegistrationName" as "cbc:RegistrationName"
	)
	,case when "Party_PartyLegalEntity_CompanyID" is null then null else xmlelement("cbc:CompanyID",
	xmlattributes("Party_PartyLegalEntity_CompanyID_@schemeID" as "schemeID"),
	"Party_PartyLegalEntity_CompanyID"
	) end
	,xmlforest(
	"Party_PartyLegalEntity_CompanyLegalForm" as "cbc:CompanyLegalForm"
	)
	)
	)
	,xmlforest(
	xmlforest(
	"Party_Contact_Name" as "cbc:Name"
	,"Party_Contact_Telephone" as "cbc:Telephone"
	,"Party_Contact_ElectronicMail" as "cbc:ElectronicMail"
	) as "cac:Contact"
	)
	)
	)
	) as "AccountingCustomerParty"
	from peppol_invoice_accounting_customer_party_v;

view raw peppol_invoice_accounting_customer_party_xv.sql hosted with ❤ by GitHub

	create or replace view peppol_invoice_payee_party_xv
	as
	select
	invoice_id
	,xmlelement("cac:PayeeParty",
	xmlconcat(
	case when "PartyIdentification_ID" is null then null else xmlelement("cac:PartyIdentification",
	xmlelement("cbc:ID",
	xmlattributes("PartyIdentification_ID_@schemeID" as "schemeID"),
	"PartyIdentification_ID"
	)
	) end,
	xmlforest(
	xmlforest(
	"PartyName_Name" as "cbc:Name"
	) as "cac:PartyName"
	),
	case when "PartyLegalEntity_CompanyID" is null then null else xmlelement("cac:PartyLegalEntity",
	xmlelement("cbc:CompanyID",
	xmlattributes("PartyLegalEntity_CompanyID_@schemeID" as "schemeID"),
	"PartyLegalEntity_CompanyID"
	)
	) end
	)
	) as "PayeeParty"
	from peppol_invoice_payee_party_v;

view raw peppol_invoice_payee_party_xv.sql hosted with ❤ by GitHub

	create or replace view peppol_invoice_tax_representative_party_xv
	as
	select
	invoice_id
	,xmlelement("cac:TaxRepresentativeParty",
	xmlforest(
	xmlforest(
	"PartyName_Name" as "cbc:Name"
	) as "cac:PartyName"
	,xmlforest(
	"PostalAddress_StreetName" as "cbc:StreetName"
	,"PostalAddress_AdditionalStreetName" as "cbc:AdditionalStreetName"
	,"PostalAddress_CityName" as "cbc:CityName"
	,"PostalAddress_PostalZone" as "cbc:PostalZone"
	,"PostalAddress_CountrySubentity" as "cbc:CountrySubentity"
	,xmlforest(
	"PostalAddress_AddressLine_Line" as "cbc:Line"
	) as "cac:AddressLine"
	,xmlforest(
	"PostalAddress_Country_IdentificationCode" as "cbc:IdentificationCode"
	) as "cac:Country"
	) as "cac:PostalAddress"
	,xmlforest(
	"PartyTaxScheme_CompanyID" as "cbc:CompanyID"
	,xmlforest(
	"PartyTaxScheme_TaxScheme_ID" as "cbc:ID"
	) as "cac:TaxScheme"
	) as "cac:PartyTaxScheme"
	)
	) as "TaxRepresentativeParty"
	from peppol_invoice_tax_representative_party_v;

view raw peppol_invoice_tax_representative_party_xv.sql hosted with ❤ by GitHub

	create or replace view peppol_invoice_delivery_xv
	as
	select
	invoice_id
	,xmlelement("cac:Delivery",
	xmlconcat(
	xmlforest(
	"ActualDeliveryDate" as "cbc:ActualDeliveryDate"
	)
	,case when ("DeliveryLocation_ID" is null and "DeliveryLocation_Country_IdentificationCode" is null) then null else xmlelement("cac:DeliveryLocation",
	xmlconcat(
	case when "DeliveryLocation_ID" is null then null else xmlelement("cbc:ID",
	xmlattributes("DeliveryLocation_ID_@schemeID" as "schemeID"),
	"DeliveryLocation_ID"
	) end
	,xmlforest(
	xmlforest(
	"DeliveryLocation_StreetName" as "cbc:StreetName"
	,"DeliveryLocation_AdditionalStreetName" as "cbc:AdditionalStreetName"
	,"DeliveryLocation_CityName" as "cbc:CityName"
	,"DeliveryLocation_PostalZone" as "cbc:PostalZone"
	,"DeliveryLocation_CountrySubentity" as "cbc:CountrySubentity"
	,xmlforest(
	"DeliveryLocation_AddressLine_Line" as "cbc:Line"
	) as "cac:AddressLine"
	,xmlforest(
	"DeliveryLocation_Country_IdentificationCode" as "cbc:IdentificationCode"
	) as "cac:Country"
	) as "cac:Address"
	)
	)
	) end
	,xmlforest(
	xmlforest(
	xmlforest(
	"DeliveryParty_PartyName_Name" as "cbc:Name"
	) as "cac:PartyName"
	) as "cac:DeliveryParty"
	)
	)
	) as "Delivery"
	from peppol_invoice_delivery_v;

view raw peppol_invoice_delivery_xv.sql hosted with ❤ by GitHub

	create or replace view peppol_invoice_payment_means_xv
	as
	select
	seq_id,
	invoice_id,
	xmlelement("cac:PaymentMeans",
	xmlconcat(
	xmlforest(
	"ID" as "cbc:ID"
	),
	xmlelement("cbc:PaymentMeansCode",
	xmlattributes("PaymentMeansCode_@name" as "name"),
	"PaymentMeansCode"
	),
	xmlforest(
	/*
	* PaymentIDは0..nなので、仕様としては複数持てる。未対応。
	*/
	"PaymentID" as "cbc:PaymentID"
	,xmlforest(
	"CardAccount_PrimaryAccountNumberID" as "cbc:PrimaryAccountNumberID"
	,"CardAccount_NetworkID" as "cbc:NetworkID"
	,"CardAccount_HolderName" as "cbc:HolderName"
	) as "cac:CardAccount"
	,xmlforest(
	"PayeeFinancialAccount_ID" as "cbc:ID"
	,"PayeeFinancialAccount_Name" as "cbc:Name"
	,xmlforest(
	"PayeeFinancialAccount_FinancialInstitutionBranch_ID" as "cbc:ID"
	,xmlforest(
	"PayeeFinancialAccount_FinancialInstitutionBranch_StreetName" as "cbc:StreetName"
	,"PayeeFinancialAccount_FinancialInstitutionBranch_CityName" as "cbc:CityName"
	,"PayeeFinancialAccount_FinancialInstitutionBranch_PostalZone" as "cbc:PostalZone"
	,"PayeeFinancialAccount_FinancialInstitutionBranch_CountrySubentity" as "cbc:CountrySubentity"
	,xmlforest(
	"PayeeFinancialAccount_FinancialInstitutionBranch_AddressLine_Line" as "cbc:Line"
	) as "cac:AddressLine"
	,xmlforest(
	"PayeeFinancialAccount_FinancialInstitutionBranch_Country_IdentificationCode" as "cbc:IdentificationCode"
	) as "cac:Country"
	) as "cac:Address"
	) as "cac:FinancialInstitutionBranch"
	) as "cac:PayeeFinancialAccount"
	,xmlforest(
	"PaymentMandate_ID" as "cbc:ID"
	,xmlforest(
	"PaymentMandate_PayerFinancialAccount_ID" as "cbc:ID"
	) as "cac:PayerFinancialAccount"
	) as "cac:PaymentMandate"
	)
	)
	) as "PaymentMeans"
	from peppol_invoice_payment_means_v;

view raw peppol_invoice_payment_means_xv.sql hosted with ❤ by GitHub

	create or replace view peppol_invoice_payment_terms_xv
	as
	select
	seq_id,
	invoice_id,
	xmlelement("cac:PaymentTerms",
	xmlforest(
	"PaymentMeansID" as "cbc:PaymentMeansID"
	,"Note" as "cbc:Note"
	,"Amount" as "cbc:Amount"
	,"InstallmentDueDate" as "cbc:InstallmentDueDate"
	)
	) as "PaymentTerms"
	from peppol_invoice_payment_terms_v;

view raw peppol_invoice_payment_terms_xv.sql hosted with ❤ by GitHub

	create or replace view peppol_invoice_prepaid_payment_xv
	as
	select
	seq_id,
	invoice_id,
	xmlelement("cac:PrepaidPayment",
	xmlforest(
	"ID" as "cbc:ID"
	,"PaidAmount" as "cbc:PaidAmount"
	,"ReceivedDate" as "cbc:ReceivedDate"
	,"InstructionID" as "cbc:InstructionID"
	)
	) as "PrepaidPayment"
	from peppol_invoice_prepaid_payment_v;

view raw peppol_invoice_prepaid_payment_xv.sql hosted with ❤ by GitHub

	create or replace view peppol_invoice_allowance_charge_xv
	as
	select
	seq_id,
	invoice_id,
	xmlelement("cac:AllowanceCharge",
	xmlconcat(
	xmlforest(
	"ChargeIndicator" as "cbc:ChargeIndicator"
	,"AllowanceChargeReasonCode" as "cbc:AllowanceChargeReasonCode"
	,"AllowanceChargeReason" as "cbc:AllowanceChargeReason"
	,"MultiplierFactorNumeric" as "cbc:MultiplierFactorNumeric"
	)
	,xmlelement("cbc:Amount",
	xmlattributes("Amount_@currencyID" as "currencyID"),
	"Amount"
	)
	,case when "BaseAmount" is null then null else xmlelement("cbc:BaseAmount",
	xmlattributes("BaseAmount_@currencyID" as "currencyID"),
	"BaseAmount"
	) end
	,xmlforest(
	xmlforest(
	"TaxCategory_ID" as "cbc:ID"
	,"TaxCategory_Percent" as "cbc:Percent"
	,xmlforest(
	"TaxCategory_TaxScheme_ID" as "cbc:ID"
	) as "cac:TaxScheme"
	) as "cac:TaxCategory"
	)
	)
	) as "AllowanceCharge"
	from peppol_invoice_allowance_charge_v;

view raw peppol_invoice_allowance_charge_xv.sql hosted with ❤ by GitHub

	create or replace view peppol_invoice_tax_total_xv
	as
	select
	tt.seq_id,
	tt.invoice_id,
	tt."@currencyID"
	,xmlelement("cac:TaxTotal",
	xmlconcat(
	xmlelement("cbc:TaxAmount"
	,xmlattributes(tt."@currencyID" as "currencyID")
	,tt."TaxAmount"
	)
	,tts."TaxSubtotal"
	)
	) as "TaxTotal"
	from peppol_invoice_tax_total_v tt
	join
	(
	select invoice_id, "@currencyID", xmlagg("TaxSubtotal" order by seq_id) as "TaxSubtotal"
	from peppol_invoice_tax_total_tax_subtotal_xv
	group by invoice_id, "@currencyID"
	) tts
	on tt.invoice_id = tts.invoice_id and tt."@currencyID" = tts."@currencyID";

view raw peppol_invoice_tax_total_xv.sql hosted with ❤ by GitHub

	create or replace view peppol_invoice_tax_total_tax_subtotal_xv
	as
	select
	seq_id,
	invoice_id,
	"@currencyID"
	,xmlelement("cac:TaxSubtotal"
	,xmlconcat(
	case when "TaxableAmount" is null then null else xmlelement("cbc:TaxableAmount",
	xmlattributes("TaxableAmount_@currencyID" as "currencyID"),
	"TaxableAmount"
	) end,
	case when "TaxAmount" is null then null else xmlelement("cbc:TaxAmount",
	xmlattributes("TaxAmount_@currencyID" as "currencyID"),
	"TaxAmount"
	) end,
	xmlforest(
	xmlforest(
	"TaxCategory_ID" as "cbc:ID"
	,"TaxCategory_Percent" as "cbc:Percent"
	,xmlforest(
	"TaxCategory_TaxScheme_ID" as "cbc:ID"
	) as "cac:TaxScheme"
	) as "cac:TaxCategory"
	)
	)
	) "TaxSubtotal"
	from peppol_invoice_tax_total_tax_subtotal_v;

view raw peppol_invoice_tax_total_tax_subtotal_xv.sql hosted with ❤ by GitHub

	create or replace view peppol_invoice_invoice_line_xv
	as
	select
	seq_id
	,iil.invoice_id
	,iil."ID"
	,xmlelement("cac:InvoiceLine",
	xmlconcat(
	xmlforest(
	iil."ID" as "cbc:ID"
	,iil."Note" as "cbc:Note"
	)
	,xmlelement("cbc:InvoicedQuantity",
	xmlattributes(iil."InvoicedQuantity_@unitCode" as "unitCode"),
	iil."InvoicedQuantity"
	)
	,xmlelement("cbc:LineExtensionAmount",
	xmlattributes(iil."LineExtensionAmount_@currencyID" as "currencyID"),
	iil."LineExtensionAmount"
	)
	,xmlforest(
	iil."AccountingCost" as "cbc:AccountingCost"
	,xmlforest(
	iil."InvoicePeriod_StartDate" as "cbc:StartDate"
	,iil."InvoicePeriod_EndDate" as "cbc:EndDate"
	) as "cac:InvoicePeriod"
	,xmlforest(
	iil."OrderLineReference_LineID" as "cbc:LineID"
	,xmlforest(
	iil."OrderLineReference_OrderReference_ID" as "cbc:ID"
	) as "cac:OrderReference"
	) as "cac:OrderLineReference"
	,xmlforest(
	iil."DespatchLineReference_LineID" as "cbc:LineID"
	,xmlforest(
	iil."DespatchLineReference_DocumentReference_ID" as "cbc:ID"
	) as "cac:DocumentReference"
	) as "cac:DespatchLineReference"
	)
	,iildr."DocumentReference"
	,iilac."AllowanceCharge"
	,xmlelement("cac:Item",
	xmlconcat(
	xmlforest(
	iil."Item_Description" as "cbc:Description"
	,iil."Item_Name" as "cbc:Name"
	,xmlforest(
	iil."Item_BuyersItemIdentification_ID" as "cbc:ID"
	) as "cac:BuyersItemIdentification"
	,xmlforest(
	iil."Item_SellersItemIdentification_ID" as "cbc:ID"
	) as "cac:SellersItemIdentification"
	),
	case when iil."Item_StandardItemIdentification_ID" is null then null else xmlelement("cac:StandardItemIdentification",
	xmlelement("cbc:ID",
	xmlattributes(iil."Item_StandardItemIdentification_ID_@schemeID" as "schemeID"),
	iil."Item_StandardItemIdentification_ID"
	)
	) end,
	xmlforest(
	xmlforest(
	iil."Item_OriginCountry_IdentificationCode" as "cbc:IdentificationCode"
	) as "cac:OriginCountry"
	)
	,iilicc."CommodityClassification"
	,iilictc."ClassifiedTaxCategory"
	,iiliaip."AdditionalItemProperty"
	)
	)
	,xmlelement("cac:Price",
	xmlconcat(
	case when iil."Price_PriceAmount" is null then null else xmlelement("cbc:PriceAmount",
	xmlattributes(iil."Price_PriceAmount_@currencyID" as "currencyID"),
	iil."Price_PriceAmount"
	) end
	,case when iil."Price_BaseQuantity" is null then null else xmlelement("cbc:BaseQuantity",
	xmlattributes(iil."Price_BaseQuantity_@unitCode" as "unitCode"),
	iil."Price_BaseQuantity"
	) end
	,case when "Price_AllowanceCharge_ChargeIndicator" is null then null else xmlelement("cac:AllowanceCharge",
	xmlconcat(
	xmlforest(
	iil."Price_AllowanceCharge_ChargeIndicator" as "cbc:ChargeIndicator"
	)
	,case when iil."Price_AllowanceCharge_Amount" is null then null else xmlelement("cbc:Amount",
	xmlattributes(iil."Price_AllowanceCharge_Amount_@currencyID" as "currencyID"),
	iil."Price_AllowanceCharge_Amount"
	) end
	,case when iil."Price_AllowanceCharge_BaseAmount" is null then null else xmlelement("cbc:BaseAmount",
	xmlattributes(iil."Price_AllowanceCharge_BaseAmount_@currencyID" as "currencyID"),
	iil."Price_AllowanceCharge_BaseAmount"
	) end
	)
	) end
	)
	)
	)
	) as "InvoiceLine"
	from peppol_invoice_invoice_line_v iil
	left outer join
	(
	select invoice_id, "InvoiceLine_ID", xmlagg("DocumentReference") as "DocumentReference"
	from peppol_invoice_invoice_line_document_reference_xv
	group by invoice_id, "InvoiceLine_ID"
	) iildr
	on iil.invoice_id = iildr.invoice_id and iil."ID" = iildr."InvoiceLine_ID"
	left outer join
	(
	select invoice_id, "InvoiceLine_ID", xmlagg("AllowanceCharge" order by seq_id) as "AllowanceCharge"
	from peppol_invoice_invoice_line_allowance_charge_xv
	group by invoice_id, "InvoiceLine_ID"
	) iilac
	on iil.invoice_id = iilac.invoice_id and iil."ID" = iilac."InvoiceLine_ID"
	left outer join
	(
	select invoice_id, "InvoiceLine_ID", xmlagg("CommodityClassification") as "CommodityClassification"
	from peppol_invoice_invoice_line_item_commodity_classification_xv
	group by invoice_id, "InvoiceLine_ID"
	) iilicc
	on iil.invoice_id = iilicc.invoice_id and iil."ID" = iilicc."InvoiceLine_ID"
	left outer join
	(
	select invoice_id, "InvoiceLine_ID", xmlagg("ClassifiedTaxCategory") as "ClassifiedTaxCategory"
	from peppol_invoice_invoice_line_item_classified_tax_category_xv
	group by invoice_id, "InvoiceLine_ID"
	) iilictc
	on iil.invoice_id = iilictc.invoice_id and iil."ID" = iilictc."InvoiceLine_ID"
	left outer join
	(
	select invoice_id, "InvoiceLine_ID", xmlagg("AdditionalItemProperty") as "AdditionalItemProperty"
	from peppol_invoice_invoice_line_item_additional_item_property_xv
	group by invoice_id, "InvoiceLine_ID"
	) iiliaip
	on iil.invoice_id = iiliaip.invoice_id and iil."ID" = iiliaip."InvoiceLine_ID"
	;

view raw peppol_invoice_invoice_line_xv.sql hosted with ❤ by GitHub

	create or replace view peppol_invoice_invoice_line_document_reference_xv
	as
	select
	seq_id,
	invoice_id,
	"InvoiceLine_ID"
	,xmlelement("cac:DocumentReference",
	xmlconcat(
	case when "ID" is null then null else xmlelement("cbc:ID",
	xmlattributes("ID_@schemeID" as "schemeID"),
	"ID"
	) end,
	xmlforest(
	"DocumentTypeCode" as "cbc:DocumentTypeCode"
	)
	)
	) "DocumentReference"
	from peppol_invoice_invoice_line_document_reference_v;

view raw peppol_invoice_invoice_line_document_reference_xv.sql hosted with ❤ by GitHub

	create or replace view peppol_invoice_invoice_line_allowance_charge_xv
	as
	select
	seq_id,
	invoice_id,
	"InvoiceLine_ID"
	,xmlelement("cac:AllowanceCharge",
	xmlconcat(
	xmlforest(
	"ChargeIndicator" as "cbc:ChargeIndicator"
	,"AllowanceChargeReasonCode" as "cbc:AllowanceChargeReasonCode"
	,"AllowanceChargeReason" as "cbc:AllowanceChargeReason"
	,"MultiplierFactorNumeric" as "cbc:MultiplierFactorNumeric"
	)
	,xmlelement("cbc:Amount",
	xmlattributes("Amount_@currencyID" as "currencyID"),
	"Amount"
	)
	,case when "BaseAmount" is null then null else xmlelement("cbc:BaseAmount",
	xmlattributes("BaseAmount_@currencyID" as "currencyID"),
	"BaseAmount"
	) end
	)
	) "AllowanceCharge"
	from peppol_invoice_invoice_line_allowance_charge_v;

view raw peppol_invoice_invoice_line_allowance_charge_xv.sql hosted with ❤ by GitHub

	create or replace view peppol_invoice_invoice_line_item_additional_item_property_xv
	as
	select
	seq_id,
	invoice_id,
	"InvoiceLine_ID"
	,xmlforest(
	xmlforest(
	"Name" as "cbc:Name"
	,"Value" as "cbc:Value"
	) as "cac:AdditionalItemProperty"
	) "AdditionalItemProperty"
	from peppol_invoice_invoice_line_item_additional_item_property_v;

view raw peppol_invoice_invoice_line_item_additional_item_property_xv.sql hosted with ❤ by GitHub

	create or replace view peppol_invoice_invoice_line_item_classified_tax_category_xv
	as
	select
	seq_id,
	invoice_id,
	"InvoiceLine_ID"
	,xmlforest(
	xmlforest(
	"ID" as "cbc:ID"
	,"Percent" as "cbc:Percent"
	,"PerUnitAmount" as "cbc:PerUnitAmount"
	,xmlforest(
	"TaxScheme_ID" as "cbc:ID"
	) as "cac:TaxScheme"
	) as "cac:ClassifiedTaxCategory"
	) "ClassifiedTaxCategory"
	from peppol_invoice_invoice_line_item_classified_tax_category_v;

view raw peppol_invoice_invoice_line_item_classified_tax_category_xv.sql hosted with ❤ by GitHub

	create or replace view peppol_invoice_invoice_line_item_commodity_classification_xv
	as
	select
	seq_id,
	invoice_id,
	"InvoiceLine_ID"
	,xmlelement("cac:CommodityClassification",
	case when "ItemClassificationCode" is null then null else xmlelement("cbc:ItemClassificationCode",
	xmlattributes(
	"ItemClassificationCode_@listID" as "listID",
	"ItemClassificationCode_@listVersionID" as "listVersionID"
	),
	"ItemClassificationCode"
	) end
	) "CommodityClassification"
	from peppol_invoice_invoice_line_item_commodity_classification_v;

view raw peppol_invoice_invoice_line_item_commodity_classification_xv.sql hosted with ❤ by GitHub