表形式のデータから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

Peppolのデジタルインボイスを表形式で表示する

 Oracle DatabaseにはXMLを扱う機能が組み込まれています。この機能を使って、デジタル庁が提供しているPeppolのデジタルインボイスのサンプルを表形式で表示してみます。

デジタルインボイスの標準仕様（JP PINT）については、デジタル庁のサイトで説明されています。

デジタルインボイスのサンプルは、こちらのサイトからダウンロードできるファイルpint-jp-resources-dev.zipに含まれています。

pint-jp-resources-dev.zipを展開すると、trn-invoice/example以下に９つのサンプルがあります。これらのファイルをOracle Databaseに読み込み、ファンクションXMLTABLEを使ってXMLから表形式のデータに変換します。

    20945  10-25-2022 00:35   trn-invoice/example/Japan PINT Invoice UBL Example6-CorrInv.xml

     9210  10-25-2022 00:35   trn-invoice/example/Japan PINT Invoice UBL Example2-TaxAcctCur.xml

    16042  10-25-2022 00:35   trn-invoice/example/Japan PINT Invoice UBL Example4-SumInv2.xml

    21151  10-25-2022 00:35   trn-invoice/example/Japan PINT Invoice UBL Example5-AllowanceCharge.xml

    21655  10-25-2022 00:35   trn-invoice/example/Japan PINT Invoice UBL Example9-SumInv1 and O.xml

    19305  10-25-2022 00:35   trn-invoice/example/Japan PINT Invoice UBL Example3-SumInv1.xml

     9817  10-25-2022 00:35   trn-invoice/example/Japan PINT Invoice UBL Example1-minimum.xml

    12551  10-25-2022 00:35   trn-invoice/example/Japan PINT Invoice UBL Example7-Return.Quan.ItPr.xml

    29603  10-25-2022 00:35   trn-invoice/example/Japan PINT Invoice UBL Example.xml

デジタルインボイスであるXMLファイルを保存するための表PEPPOL_DOCUMENTSを作成します。

以下のDDLを実行します。

	create table peppol_documents (
	id number default on null to_number(sys_guid(), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX')
	constraint peppol_documents_id_pk primary key,
	content blob,
	content_filename varchar2(512 char),
	content_mimetype varchar2(512 char),
	content_charset varchar2(512 char),
	content_lastupd date,
	invoice xmltype
	)
	xmltype column invoice store as securefile binary xml (compress low)
	;

view raw peppol_documents.sql hosted with ❤ by GitHub

アップロードしたファイルは列CONTENTにBLOBとして保存します。アップロードしたファイルをBLOBに保存したあとに、BLOBからXMLTYPEに変換し列INVOICEに保存します。

XMLTYPEで保存されているインボイスを表形式に変換します。XMLTABLEはビューから呼び出すように実装しました。以下のビューを作成します。左がビュー名、右が対応しているXML要素です。

XPathと列名の対応の確認を容易にするため、表名や列名に長い名前を付けています。識別子が30バイトまでという制限のある、古いOracle Databaseでは実行できません。

peppol_invoice_v:/Invoice
peppol_invoice_billing_reference_v:/Invoice/cac:BillingReference
peppol_invoice_additional_document_reference_v:/Invoice/cac:AdditionalDocumentReference
peppol_invoice_accounting_supplier_party_v:/Invoice/cac:AccountingSupplierParty
peppol_invoice_accounting_customer_party_v:/Invoice/cac:AccountingCustomerParty
peppol_invoice_payee_party_v:/Invoice/cac:PayeeParty
peppol_invoice_tax_representative_party_v:/Invoice/cac:TaxRepresentativeParty
peppol_invoice_delivery_v:/Invoice/cac:Delivery
peppol_invoice_payment_means_v:/Invoice/cac:PaymentMeans
peppol_invoice_payment_terms_v:/Invoice/cac:PaymentTerms
peppol_invoice_prepaid_payment_v:/Invoice/cac:PrepaidPayment
peppol_invoice_allowance_charge_v:/Invoice/cac:AllowanceCharge
peppol_invoice_tax_total_v:/Invoice/cac:TaxTotal
peppol_invoice_tax_total_tax_subtotal_v:/Invoice/cac:TaxTotal/cac:TaxSubtotal
peppol_invoice_invoice_line_v:/Invoice/cac:InvoiceLine
peppol_invoice_invoice_line_document_reference_v:/Invoice/cac:InvoiceLine/cac:DocumentReference
peppol_invoice_invoice_line_allowance_charge_v:/Invoice/cac:InvoiceLine/cac:AllowanceCharge

peppol_invoice_invoice_line_item_additonal_item_property_v:/Invoice/cac:InvoiceLine/cal:Item/cac:AdditionalItemProperty

peppol_invoice_invoice_line_item_classified_tax_property_v:/Invoice/cac:InvoiceLine/cal:Item/cac:ClassifiedTaxProperty

peppol_invoice_invoice_line_item_commodity_classification_v:/Invoice/cac:InvoiceLine/cal:Item/cac:CommodityClassification

ビューを作成するDDLは、記事の最後に添付します。

作成した表とビューを使って、デジタルインボイスのサンプルのアップロードと表示を行うAPEXアプリケーションを作ります。

アプリケーション作成ウィザードを起動します。名前はPeppolとします。

デフォルトで作成されるホーム・ページを削除し、代わりに対話モード・レポートのページを追加します。

ページ名はDocumentsとします。表またはビューにPEPPOL_DOCUMENTSを選択します。

上記の設定でアプリケーションの作成をクリックします。

アプリケーションが作成されたら、ファイルのアップロードに関係する設定を修正します。

対話モード・レポートのページを開き、列CONTENTを選択します。

BLOB属性のMIMEタイプ列、ファイル名列、最終更新列、文字セット列として、それぞれCONTENT_MIMETYPE、CONTENT_FILENAME、CONTENT_LASTUPD、CONTENT_CHARSETを設定します。

フォームのページを開き、ページ・アイテムP2_CONTENTに同様の設定を行ないます。

ファイルのアップロード後に、BLOBをXMLTYPEに変換し保存するプロセスを作成します。

フォームのページでプロセス・ビューを開き、プロセスの作成を実行します。

作成したプロセスはプロセス・フォームPeppol Documentsとダイアログを閉じるの間に配置します。識別の名前はバイナリXMLで保存、タイプはコードを実行を選択します。

ソースのPL/SQLコードとして、以下のコードを記述します。BLOBのデータを、XMLTYPEに変換して列INVOICEに保存しています。

update peppol_documents 
    set invoice = sys.xmltype(content, NLS_CHARSET_ID('AL32UTF8'))
where id = :P2_ID;

サーバー側の条件のタイプにリクエストは値に含まれるを選択し、値としてCREATE,SAVEを指定します。ボタン作成または変更の適用を押した時に、このプロセスが実行されます。

以上でサンプルのデジタルインボイスのアップロードができるようになりました。

アプリケーションを実行し、サンプルのファイルをアップロードします。

デジタルインボイスの内容を表示するページを作成します。

ページの作成を実行します。

空白のページを選択します。

ページ番号は３、名前はInvoiceとします。ページ・モードは標準で、ナビゲーションはデフォルトのまま、変更しません。

ページの作成をクリックします。

ページInvoiceが作成されます。

最初にページ・プロパティのナビゲーションの保存されていない変更の警告をOFFにします。表示するインボイスを切り替えたときに、警告が表示されないようになります。

表示するインボイスを選択するページ・アイテムを作成します。

識別の名前はP3_INVOICE_ID、タイプとして選択リストを選びます。ラベルはInvoiceとします。

設定の選択時のページ・アクションとして、Redirect and Set Valueを選択します。

LOVのタイプにSQL問合せを選択し、SQL問合せとして以下を記述します。

select content_filename d, id r from peppol_documents

追加値の表示をOFF、NULL値の表示をON、NULL表示値として-- Select Invoice --を記述します。

ビューPEPPOL_INVOICE_Vをソースとした対話モード・レポートを作成します。

作成した対話モード・レポートの識別のタイトルはInvoiceとします。タイプは対話モード・レポートです。ソースの表名にPEPPOL_INVOICE_Vを選択します。

WHERE句に以下を記述し、ページ・アイテムP3_INVOICE_IDで選択されているインボイスの内容が表示されるように限定します。（サンプルは９つだけなので、未選択の場合はすべてのインボイスについて表示します。）

:P3_INVOICE_ID is null or invoice_id = :P3_INVOICE_ID

外観のテンプレートにStandardを選択し、テンプレート・オプションのRemove Body Paddingにチェックを入れます。テンプレートIntractive Reportの場合、レポートのタイトルが表示されません。

この時点でアプリケーションを実行すると、以下のようにビューPEPPOL_INVOICE_Vから参照できる情報がレポートに表示されます。

残り１６個のビューについても、同様に対話モード・レポートを作成します。

重複を使うと作業が早くできます。

以上でアプリケーションは完成です。アプリケーションを実行すると、記事の先頭のGIF動画のように動作します。

今回作成したAPEXアプいケーションのエクスポートを以下に置きました。

https://github.com/ujnak/apexapps/blob/master/exports/peppol.zip

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

完

ビューを作成するDDL

	create or replace view peppol_invoice_v
	as
	/*
	* 参照: https://test-docs.peppol.eu/pint/pint-jp/sb-work-v1/pint-jp-sb/trn-invoice/syntax/ubl-Invoice/
	*/
	select
	d.id invoice_id,
	c."UBLVersionID",
	c."CustomizationID",
	c."ProfileID",
	c."ID",
	c."IssueDate",
	c."IssueTime",
	c."DueDate",
	c."InvoiceTypeCode",
	c."Note",
	c."TaxPointDate",
	c."DocumentCurrencyCode",
	c."TaxCurrencyCode",
	c."AccountingCost",
	c."BuyerReference",
	c."InvoicePeriod_StartDate",
	c."InvoicePeriod_EndDate",
	c."InvoicePeriod_DescriptionCode",
	c."OrderReference_ID",
	c."OrderReference_SalesOrderID",
	c."DespatchDocumentReference_ID",
	c."ReceiptDocumentReference_ID",
	c."OriginatorDocumentReference_ID",
	c."ContractDocumentReference_ID",
	c."ProjectReference_ID",
	c."LegalMonetaryTotal_LineExtensionAmount",
	c."LegalMonetaryTotal_LineExtensionAmount_@currencyID",
	c."LegalMonetaryTotal_TaxExclusiveAmount",
	c."LegalMonetaryTotal_TaxExclusiveAmount_@currencyID",
	c."LegalMonetaryTotal_TaxInclusiveAmount",
	c."LegalMonetaryTotal_TaxInclusiveAmount_@currencyID",
	c."LegalMonetaryTotal_AllowanceTotalAmount",
	c."LegalMonetaryTotal_AllowanceTotalAmount_@currencyID",
	c."LegalMonetaryTotal_ChargeTotalAmount",
	c."LegalMonetaryTotal_ChargeTotalAmount_@currencyID",
	c."LegalMonetaryTotal_PrepaidAmount",
	c."LegalMonetaryTotal_PrepaidAmount_@currencyID",
	c."LegalMonetaryTotal_PayableRoundingAmount",
	c."LegalMonetaryTotal_PayableRoundingAmount_@currencyID",
	c."LegalMonetaryTotal_PayableAmount",
	c."LegalMonetaryTotal_PayableAmount_@currencyID"
	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'
	passing d.invoice
	columns
	"UBLVersionID" varchar2(4000) path 'cbc:UBLVersionID',
	"CustomizationID" varchar2(4000) path 'cbc:CustomizationID',
	"ProfileID" varchar2(4000) path 'cbc:ProfileID',
	"ID" varchar2(4000) path 'cbc:ID',
	"IssueDate" varchar2(4000) path 'cbc:IssueDate',
	"IssueTime" varchar2(4000) path 'cbc:IssueTime',
	"DueDate" varchar2(4000) path 'cbc:DueDate',
	"InvoiceTypeCode" varchar2(4000) path 'cbc:InvoiceTypeCode',
	"Note" clob path 'cbc:Note',
	"TaxPointDate" varchar2(4000) path 'cbc:TaxPointDate',
	"DocumentCurrencyCode" varchar2(4000) path 'cbc:DocumentCurrencyCode',
	"TaxCurrencyCode" varchar2(4000) path 'cbc:TaxCurrencyCode',
	"AccountingCost" varchar2(4000) path 'cbc:AccountingCost',
	"BuyerReference" varchar2(4000) path 'cbc:BuyerReference',
	"InvoicePeriod_StartDate" varchar2(4000) path 'cac:InvoicePeriod/cbc:StartDate',
	"InvoicePeriod_EndDate" varchar2(4000) path 'cac:InvoicePeriod/cbc:EndDate',
	"InvoicePeriod_DescriptionCode" varchar2(4000) path 'cac:InvoicePeriod/cbc:DescriptionCode',
	"OrderReference_ID" varchar2(4000) path 'cac:OrderReference/cbc:ID',
	"OrderReference_SalesOrderID" varchar2(4000) path 'cac:OrderReference/cbc:SalesOrderID',
	"DespatchDocumentReference_ID" varchar2(4000) path 'cac:DespatchDocumentReference/cbc:ID',
	"ReceiptDocumentReference_ID" varchar2(4000) path 'cac:ReceiptDocumentReference/cbc:ID',
	"OriginatorDocumentReference_ID" varchar2(4000) path 'cac:OriginatorDocumentReference/cbc:ID',
	"ContractDocumentReference_ID" varchar2(4000) path 'cac:ContractDocumentReference/cbc:ID',
	"ProjectReference_ID" varchar2(4000) path 'cac:ProjectReference/cbc:ID',
	"LegalMonetaryTotal_LineExtensionAmount" number path 'cac:LegalMonetaryTotal/cbc:LineExtensionAmount',
	"LegalMonetaryTotal_LineExtensionAmount_@currencyID" varchar2(4000) path 'cac:LegalMonetaryTotal/cbc:LineExtensionAmount/@currencyID',
	"LegalMonetaryTotal_TaxExclusiveAmount" number path 'cac:LegalMonetaryTotal/cbc:TaxExclusiveAmount',
	"LegalMonetaryTotal_TaxExclusiveAmount_@currencyID" varchar2(4000) path 'cac:LegalMonetaryTotal/cbc:TaxExclusiveAmount/@currencyID',
	"LegalMonetaryTotal_TaxInclusiveAmount" number path 'cac:LegalMonetaryTotal/cbc:TaxInclusiveAmount',
	"LegalMonetaryTotal_TaxInclusiveAmount_@currencyID" varchar2(4000) path 'cac:LegalMonetaryTotal/cbc:TaxInclusiveAmount/@currencyID',
	"LegalMonetaryTotal_AllowanceTotalAmount" number path 'cac:LegalMonetaryTotal/cbc:AllowanceTotalAmount',
	"LegalMonetaryTotal_AllowanceTotalAmount_@currencyID" varchar2(4000) path 'cac:LegalMonetaryTotal/cbc:AllowanceTotalAmount/@currencyID',
	"LegalMonetaryTotal_ChargeTotalAmount" number path 'cac:LegalMonetaryTotal/cbc:ChargeTotalAmount',
	"LegalMonetaryTotal_ChargeTotalAmount_@currencyID" varchar2(4000) path 'cac:LegalMonetaryTotal/cbc:ChargeTotalAmount/@currencyID',
	"LegalMonetaryTotal_PrepaidAmount" number path 'cac:LegalMonetaryTotal/cbc:PrepaidAmount',
	"LegalMonetaryTotal_PrepaidAmount_@currencyID" varchar2(4000) path 'cac:LegalMonetaryTotal/cbc:PrepaidAmount/@currencyID',
	"LegalMonetaryTotal_PayableRoundingAmount" number path 'cac:LegalMonetaryTotal/cbc:PayableRoundingAmount',
	"LegalMonetaryTotal_PayableRoundingAmount_@currencyID" varchar2(4000) path 'cac:LegalMonetaryTotal/cbc:PayableRoundingAmount/@currencyID',
	"LegalMonetaryTotal_PayableAmount" number path 'cac:LegalMonetaryTotal/cbc:PayableAmount',
	"LegalMonetaryTotal_PayableAmount_@currencyID" varchar2(4000) path 'cac:LegalMonetaryTotal/cbc:PayableAmount/@currencyID'
	) c;

view raw peppol_invoice_v.sql hosted with ❤ by GitHub

	create or replace view peppol_invoice_billing_reference_v
	as
	select
	rownum seq_id,
	d.id invoice_id,
	c."InvoiceDocumentReference_ID",
	c."InvoiceDocumentReference_IssueDate"
	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:BillingReference[*]'
	passing d.invoice
	columns
	"InvoiceDocumentReference_ID" varchar2(4000) path 'cac:InvoiceDocumentReference/cbc:ID',
	"InvoiceDocumentReference_IssueDate" varchar2(4000) path 'cac:InvoiceDocumentReference/cbc:IssueDate'
	) c;

view raw peppol_invoice_billing_reference_v.sql hosted with ❤ by GitHub

	create or replace view peppol_invoice_additional_document_reference_v
	as
	select
	rownum seq_id, -- デバッグのために追加
	d.id invoice_id,
	c."ID",
	c."ID_@schemeID",
	c."DocumentTypeCode",
	c."DocumentDescription",
	c."Attachment_EmbeddedDocumentBinaryObject",
	c."Attachment_EmbeddedDocumentBinaryObject_@mimeCode",
	c."Attachment_EmbeddedDocumentBinaryObject_@filename",
	c."Attachment_ExternalReference"
	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:AdditionalDocumentReference[*]'
	passing d.invoice
	columns
	"ID" varchar2(4000) path 'cbc:ID',
	"ID_@schemeID" varchar2(4000) path 'cbc:ID/@schemeID',
	"DocumentTypeCode" varchar2(4000) path 'cbc:DocumentTypeCode',
	"DocumentDescription" varchar2(4000) path 'cbc:DocumentDescription',
	"Attachment_EmbeddedDocumentBinaryObject" clob path 'cac:Attachment/cbc:EmbeddedDocumentBinaryObject',
	"Attachment_EmbeddedDocumentBinaryObject_@mimeCode" varchar2(4000) path 'cac:Attachment/cbc:EmbeddedDocumentBinaryObject/@mimeCode',
	"Attachment_EmbeddedDocumentBinaryObject_@filename" varchar2(4000) path 'cac:Attachment/cbc:EmbeddedDocumentBinaryObject/@filename',
	"Attachment_ExternalReference" varchar2(4000) path 'cac:Attachment/cac:ExternalReference/cbc:URI'
	) c;

view raw peppol_invoice_additional_document_reference_v.sql hosted with ❤ by GitHub

	create or replace view peppol_invoice_accounting_supplier_party_v
	as
	select
	d.id invoice_id,
	c."Party_EndpointID",
	c."Party_EndpointID_@schemeID",
	c."Party_PartyIdentification_ID",
	c."Party_PartyIdentification_ID_@schemeID",
	c."Party_PartyName_Name",
	c."Party_PostalAddress_StreetName",
	c."Party_PostalAddress_AdditionalStreetName",
	c."Party_PostalAddress_CityName",
	c."Party_PostalAddress_PostalZone",
	c."Party_PostalAddress_CountrySubentity",
	c."Party_PostalAddress_AddressLine_Line",
	c."Party_PostalAddress_Country_IdentificationCode",
	c."Party_PartyTaxScheme_CompanyID",
	c."Party_PartyTaxScheme_TaxScheme_ID",
	c."Party_PartyLegalEntity_RegistrationName",
	c."Party_PartyLegalEntity_CompanyID",
	c."Party_PartyLegalEntity_CompanyID_@schemeID",
	c."Party_PartyLegalEntity_CompanyLegalForm",
	c."Party_Contact_Name",
	c."Party_Contact_Telephone",
	c."Party_Contact_ElectronicMail"
	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:AccountingSupplierParty'
	passing d.invoice
	columns
	"Party_EndpointID" varchar2(4000) path 'cac:Party/cbc:EndpointID',
	"Party_EndpointID_@schemeID" varchar2(4000) path 'cac:Party/cbc:EndpointID/@schemeID',
	/*
	* PartyIdentificationは @schemeID = "SEPA" 以外では 0..n で、仕様上は複数値になっている。
	* このサンプル・コードは複数値の対応を省略している。
	*/
	"Party_PartyIdentification_ID" varchar2(4000) path 'cac:Party/cac:PartyIdentification/cbc:ID',
	"Party_PartyIdentification_ID_@schemeID" varchar2(4000) path 'cac:Party/cac:PartyIdentification/cbc:ID/@schemeID',
	"Party_PartyName_Name" varchar2(4000) path 'cac:Party/cac:PartyName/cbc:Name',
	"Party_PostalAddress_StreetName" varchar2(4000) path 'cac:Party/cac:PostalAddress/cbc:StreetName',
	"Party_PostalAddress_AdditionalStreetName" varchar2(4000) path 'cac:Party/cac:PostalAddress/cbc:AdditionalStreetName',
	"Party_PostalAddress_CityName" varchar2(4000) path 'cac:Party/cac:PostalAddress/cbc:CityName',
	"Party_PostalAddress_PostalZone" varchar2(4000) path 'cac:Party/cac:PostalAddress/cbc:PostalZone',
	"Party_PostalAddress_CountrySubentity" varchar2(4000) path 'cac:Party/cac:PostalAddress/cbc:CountrySubentity',
	"Party_PostalAddress_AddressLine_Line" varchar2(4000) path 'cac:Party/cac:PostalAddress/cac:AddressLine/cbc:Line',
	"Party_PostalAddress_Country_IdentificationCode" varchar2(4000) path 'cac:Party/cac:PostalAddress/cac:Country/cbc:IdentificationCode',
	"Party_PartyTaxScheme_CompanyID" varchar2(4000) path 'cac:Party/cac:PartyTaxScheme/cbc:CompanyID',
	"Party_PartyTaxScheme_TaxScheme_ID" varchar2(4000) path 'cac:Party/cac:PartyTaxScheme/cac:TaxScheme/cbc:ID',
	"Party_PartyLegalEntity_RegistrationName" varchar2(4000) path 'cac:Party/cac:PartyLegalEntity/cbc:RegistrationName',
	"Party_PartyLegalEntity_CompanyID" varchar2(4000) path 'cac:Party/cac:PartyLegalEntity/cbc:CompanyID',
	"Party_PartyLegalEntity_CompanyID_@schemeID" varchar2(4000) path 'cac:Party/cac:PartyLegalEntity/cbc:CompanyID/@schemeID',
	"Party_PartyLegalEntity_CompanyLegalForm" varchar2(4000) path 'cac:Party/cac:PartyLegalEntity/cbc:CompanyLegalForm',
	"Party_Contact_Name" varchar2(4000) path 'cac:Party/cac:Contact/cbc:Name',
	"Party_Contact_Telephone" varchar2(4000) path 'cac:Party/cac:Contact/cbc:Telephone',
	"Party_Contact_ElectronicMail" varchar2(4000) path 'cac:Party/cac:Contact/cbc:ElectronicMail'
	) c;

view raw peppol_invoice_accounting_supplier_party_v.sql hosted with ❤ by GitHub

	create or replace view peppol_invoice_accounting_customer_party_v
	as
	select
	d.id invoice_id,
	c."Party_EndpointID",
	c."Party_EndpointID_@schemeID",
	c."Party_PartyIdentification_ID",
	c."Party_PartyIdentification_ID_@schemeID",
	c."Party_PartyName_Name",
	c."Party_PostalAddress_StreetName",
	c."Party_PostalAddress_AdditionalStreetName",
	c."Party_PostalAddress_CityName",
	c."Party_PostalAddress_PostalZone",
	c."Party_PostalAddress_CountrySubentity",
	c."Party_PostalAddress_AddressLine_Line",
	c."Party_PostalAddress_Country_IdentificationCode",
	c."Party_PartyTaxScheme_CompanyID",
	c."Party_PartyTaxScheme_TaxScheme_ID",
	c."Party_PartyLegalEntity_RegistrationName",
	c."Party_PartyLegalEntity_CompanyID",
	c."Party_PartyLegalEntity_CompanyID_@schemeID",
	c."Party_PartyLegalEntity_CompanyLegalForm",
	c."Party_Contact_Name",
	c."Party_Contact_Telephone",
	c."Party_Contact_ElectronicMail"
	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:AccountingCustomerParty'
	passing d.invoice
	columns
	"Party_EndpointID" varchar2(4000) path 'cac:Party/cbc:EndpointID',
	"Party_EndpointID_@schemeID" varchar2(4000) path 'cac:Party/cbc:EndpointID/@schemeID',
	/*
	* PartyIdentificationは @schemeID = "SEPA" 以外では 0..n で、仕様上は複数値になっている。
	* このサンプル・コードは複数値の対応を省略している。
	*/
	"Party_PartyIdentification_ID" varchar2(4000) path 'cac:Party/cac:PartyIdentification/cbc:ID',
	"Party_PartyIdentification_ID_@schemeID" varchar2(4000) path 'cac:Party/cac:PartyIdentification/cbc:ID/@schemeID',
	"Party_PartyName_Name" varchar2(4000) path 'cac:Party/cac:PartyName/cbc:Name',
	"Party_PostalAddress_StreetName" varchar2(4000) path 'cac:Party/cac:PostalAddress/cbc:StreetName',
	"Party_PostalAddress_AdditionalStreetName" varchar2(4000) path 'cac:Party/cac:PostalAddress/cbc:AdditionalStreetName',
	"Party_PostalAddress_CityName" varchar2(4000) path 'cac:Party/cac:PostalAddress/cbc:CityName',
	"Party_PostalAddress_PostalZone" varchar2(4000) path 'cac:Party/cac:PostalAddress/cbc:PostalZone',
	"Party_PostalAddress_CountrySubentity" varchar2(4000) path 'cac:Party/cac:PostalAddress/cbc:CountrySubentity',
	"Party_PostalAddress_AddressLine_Line" varchar2(4000) path 'cac:Party/cac:PostalAddress/cac:AddressLine/cbc:Line',
	"Party_PostalAddress_Country_IdentificationCode" varchar2(4000) path 'cac:Party/cac:PostalAddress/cac:Country/cbc:IdentificationCode',
	"Party_PartyTaxScheme_CompanyID" varchar2(4000) path 'cac:Party/cac:PartyTaxScheme/cbc:CompanyID',
	"Party_PartyTaxScheme_TaxScheme_ID" varchar2(4000) path 'cac:Party/cac:PartyTaxScheme/cac:TaxScheme/cbc:ID',
	"Party_PartyLegalEntity_RegistrationName" varchar2(4000) path 'cac:Party/cac:PartyLegalEntity/cbc:RegistrationName',
	"Party_PartyLegalEntity_CompanyID" varchar2(4000) path 'cac:Party/cac:PartyLegalEntity/cbc:CompanyID',
	"Party_PartyLegalEntity_CompanyID_@schemeID" varchar2(4000) path 'cac:Party/cac:PartyLegalEntity/cbc:CompanyID/@schemeID',
	"Party_PartyLegalEntity_CompanyLegalForm" varchar2(4000) path 'cac:Party/cac:PartyLegalEntity/cbc:CompanyLegalForm',
	"Party_Contact_Name" varchar2(4000) path 'cac:Party/cac:Contact/cbc:Name',
	"Party_Contact_Telephone" varchar2(4000) path 'cac:Party/cac:Contact/cbc:Telephone',
	"Party_Contact_ElectronicMail" varchar2(4000) path 'cac:Party/cac:Contact/cbc:ElectronicMail'
	) c;

view raw peppol_invoice_accounting_customer_party_v.sql hosted with ❤ by GitHub

	create or replace view peppol_invoice_payee_party_v
	as
	select
	d.id invoice_id,
	c."PartyIdentification_ID",
	c."PartyIdentification_ID_@schemeID",
	c."PartyName_Name",
	c."PartyLegalEntity_CompanyID",
	c."PartyLegalEntity_CompanyID_@schemeID"
	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:PayeeParty'
	passing d.invoice
	columns
	"PartyIdentification_ID" varchar2(4000) path 'cac:PartyIdentification/cbc:ID',
	"PartyIdentification_ID_@schemeID" varchar2(4000) path 'cac:PartyIdentification/cbc:ID/@schemeID',
	"PartyName_Name" varchar2(4000) path 'cac:PartyName/cbc:Name',
	"PartyLegalEntity_CompanyID" varchar2(4000) path 'cac:PartyLegalEntity/cbc:CompanyID',
	"PartyLegalEntity_CompanyID_@schemeID" varchar2(4000) path 'cac:PartyLegalEntity/cbc:CompanyID/@schemeID'
	) c;

view raw peppol_invoice_payee_party_v.sql hosted with ❤ by GitHub

	create or replace view peppol_invoice_tax_representative_party_v
	as
	select
	d.id invoice_id,
	c."PartyName_Name",
	c."PostalAddress_StreetName",
	c."PostalAddress_AdditionalStreetName",
	c."PostalAddress_CityName",
	c."PostalAddress_PostalZone",
	c."PostalAddress_CountrySubentity",
	c."PostalAddress_AddressLine_Line",
	c."PostalAddress_Country_IdentificationCode",
	c."PartyTaxScheme_CompanyID",
	c."PartyTaxScheme_TaxScheme_ID"
	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:TaxRepresentativeParty'
	passing d.invoice
	columns
	"PartyName_Name" varchar2(4000) path 'cac:PartyName/cbc:Name',
	"PostalAddress_StreetName" varchar2(4000) path 'cac:PostalAddress/cbc:StreetName',
	"PostalAddress_AdditionalStreetName" varchar2(4000) path 'cac:PostalAddress/cbc:AdditionalStreetName',
	"PostalAddress_CityName" varchar2(4000) path 'cac:PostalAddress/cbc:CityName',
	"PostalAddress_PostalZone" varchar2(4000) path 'cac:PostalAddress/cbc:PostalZone',
	"PostalAddress_CountrySubentity" varchar2(4000) path 'cac:PostalAddress/cbc:CountrySubentity',
	"PostalAddress_AddressLine_Line" varchar2(4000) path 'cac:PostalAddress/cac:AddressLine/cbc:Line',
	"PostalAddress_Country_IdentificationCode" varchar2(4000) path 'cac:PostalAddress/cac:Country/cbc:IdentificationCode',
	"PartyTaxScheme_CompanyID" varchar2(4000) path 'cac:PartyTaxScheme/cbc:CompanyID',
	"PartyTaxScheme_TaxScheme_ID" varchar2(4000) path 'cac:PartyTaxScheme/cac:TaxScheme/cbc:ID'
	) c;

view raw peppol_invoice_tax_representative_party_v.sql hosted with ❤ by GitHub

	create or replace view peppol_invoice_delivery_v
	as
	select
	d.id invoice_id,
	c."ActualDeliveryDate",
	c."DeliveryLocation_ID",
	c."DeliveryLocation_ID_@schemeID",
	c."DeliveryLocation_StreetName",
	c."DeliveryLocation_AdditionalStreetName",
	c."DeliveryLocation_CityName",
	c."DeliveryLocation_PostalZone",
	c."DeliveryLocation_CountrySubentity",
	c."DeliveryLocation_AddressLine_Line",
	c."DeliveryLocation_Country_IdentificationCode",
	c."DeliveryParty_PartyName_Name"
	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:Delivery'
	passing d.invoice
	columns
	"ActualDeliveryDate" varchar2(4000) path 'cbc:ActualDeliveryDate',
	"DeliveryLocation_ID" varchar2(4000) path 'cac:DeliveryLocation/cbc:ID',
	"DeliveryLocation_ID_@schemeID" varchar2(4000) path 'cac:DeliveryLocation/cbc:ID/@schemeID',
	"DeliveryLocation_StreetName" varchar2(4000) path 'cac:DeliveryLocation/cac:Address/cbc:StreetName',
	"DeliveryLocation_AdditionalStreetName" varchar2(4000) path 'cac:DeliveryLocation/cac:Address/cbc:AdditionalStreetName',
	"DeliveryLocation_CityName" varchar2(4000) path 'cac:DeliveryLocation/cac:Address/cbc:CityName',
	"DeliveryLocation_PostalZone" varchar2(4000) path 'cac:DeliveryLocation/cac:Address/cbc:PostalZone',
	"DeliveryLocation_CountrySubentity" varchar2(4000) path 'cac:DeliveryLocation/cac:Address/cbc:CountrySubentity',
	"DeliveryLocation_AddressLine_Line" varchar2(4000) path 'cac:DeliveryLocation/cac:Address/cac:AddressLine/cbc:Line',
	"DeliveryLocation_Country_IdentificationCode" varchar2(4000) path 'cac:DeliveryLocation/cac:Address/cac:Country/cbc:IdentificationCode',
	"DeliveryParty_PartyName_Name" varchar2(4000) path 'cac:DeliveryParty/cac:PartyName/cbc:Name'
	) c;

view raw peppol_invoice_delivery_v.sql hosted with ❤ by GitHub

	create or replace view peppol_invoice_payment_means_v
	as
	select
	rownum seq_id,
	d.id invoice_id,
	c."ID",
	c."PaymentMeansCode",
	c."PaymentMeansCode_@name",
	c."PaymentID",
	c."CardAccount_PrimaryAccountNumberID",
	c."CardAccount_NetworkID",
	c."CardAccount_HolderName",
	c."PayeeFinancialAccount_ID",
	c."PayeeFinancialAccount_Name",
	c."PayeeFinancialAccount_FinancialInstitutionBranch_ID",
	c."PayeeFinancialAccount_FinancialInstitutionBranch_StreetName",
	c."PayeeFinancialAccount_FinancialInstitutionBranch_AdditionalStreetName",
	c."PayeeFinancialAccount_FinancialInstitutionBranch_CityName",
	c."PayeeFinancialAccount_FinancialInstitutionBranch_PostalZone",
	c."PayeeFinancialAccount_FinancialInstitutionBranch_CountrySubentity",
	c."PayeeFinancialAccount_FinancialInstitutionBranch_AddressLine_Line",
	c."PayeeFinancialAccount_FinancialInstitutionBranch_Country_IdentificationCode",
	c."PaymentMandate_ID",
	c."PaymentMandate_PayerFinancialAccount_ID"
	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:PaymentMeans[*]'
	passing d.invoice
	columns
	"ID" varchar2(4000) path 'cbc:ID',
	"PaymentMeansCode" varchar2(4000) path 'cbc:PaymentMeansCode',
	"PaymentMeansCode_@name" varchar2(4000) path 'cbc:PaymentMeansCode/@name',
	/*
	* PaymentIDは0..nなので、仕様としては複数持てる。
	*/
	"PaymentID" varchar2(4000) path 'cbc:PaymentID',
	"CardAccount_PrimaryAccountNumberID" varchar2(4000) path 'cac:CardAccount/cbc:PrimaryAccountNumberID',
	"CardAccount_NetworkID" varchar2(4000) path 'cac:CardAccount/cbc:NetworkID',
	"CardAccount_HolderName" varchar2(4000) path 'cac:CardAccount/cbc:HolderName',
	"PayeeFinancialAccount_ID" varchar2(4000) path 'cac:PayeeFinancialAccount/cbc:ID',
	"PayeeFinancialAccount_Name" varchar2(4000) path 'cac:PayeeFinancialAccount/cbc:Name',
	"PayeeFinancialAccount_FinancialInstitutionBranch_ID" varchar2(4000) path 'cac:PayeeFinancialAccount/cac:FinancialInstitutionBranch/cbc:ID',
	"PayeeFinancialAccount_FinancialInstitutionBranch_StreetName" varchar2(4000) path 'cac:PayeeFinancialAccount/cac:FinancialInstitutionBranch/cac:Address/cbc:StreetName',
	"PayeeFinancialAccount_FinancialInstitutionBranch_AdditionalStreetName" varchar2(4000) path 'cac:PayeeFinancialAccount/cac:FinancialInstitutionBranch/cac:Address/cbc:AdditionalStreetName',
	"PayeeFinancialAccount_FinancialInstitutionBranch_CityName" varchar2(4000) path 'cac:PayeeFinancialAccount/cac:FinancialInstitutionBranch/cac:Address/cbc:CityName',
	"PayeeFinancialAccount_FinancialInstitutionBranch_PostalZone" varchar2(4000) path 'cac:PayeeFinancialAccount/cac:FinancialInstitutionBranch/cac:Address/cbc:PostalZone',
	"PayeeFinancialAccount_FinancialInstitutionBranch_CountrySubentity" varchar2(4000) path 'cac:PayeeFinancialAccount/cac:FinancialInstitutionBranch/cac:Address/cbc:CountrySubentity',
	"PayeeFinancialAccount_FinancialInstitutionBranch_AddressLine_Line" varchar2(4000) path 'cac:PayeeFinancialAccount/cac:FinancialInstitutionBranch/cac:Address/cac:AddressLine/cbc:Line',
	"PayeeFinancialAccount_FinancialInstitutionBranch_Country_IdentificationCode" varchar2(4000) path 'cac:PayeeFinancialAccount/cac:FinancialInstitutionBranch/cac:Address/cac:Country/cbc:IdentificationCode',
	"PaymentMandate_ID" varchar2(4000) path 'cac:PaymentMandate/cbc:ID',
	"PaymentMandate_PayerFinancialAccount_ID" varchar2(4000) path 'cac:PaymentMandate/cac:PayerFinancialAccount/cbc:ID'
	) c;

view raw peppol_invoice_payment_means_v.sql hosted with ❤ by GitHub

	create or replace view peppol_invoice_payment_terms_v
	as
	select
	rownum seq_id,
	d.id invoice_id,
	c."PaymentMeansID",
	c."Note",
	c."Amount",
	c."InstallmentDueDate"
	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:PaymentTerms[*]'
	passing d.invoice
	columns
	"PaymentMeansID" varchar2(4000) path 'cbc:PaymentMeansID',
	"Note" clob path 'cbc:Note',
	"Amount" number path 'cbc:Amount',
	"InstallmentDueDate" varchar2(4000) path 'cbc:InstallmentDueDate'
	) c;

view raw peppol_invoice_payment_terms_v.sql hosted with ❤ by GitHub

	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;

view raw peppol_invoice_prepaid_payment_v.sql hosted with ❤ by GitHub

	create or replace view peppol_invoice_allowance_charge_v
	as
	select
	rownum seq_id,
	d.id invoice_id,
	c."ChargeIndicator",
	c."AllowanceChargeReasonCode",
	c."AllowanceChargeReason",
	c."MultiplierFactorNumeric",
	c."Amount",
	c."Amount_@currencyID",
	c."BaseAmount",
	c."BaseAmount_@currencyID",
	c."TaxCategory_ID",
	c."TaxCategory_Percent",
	c."TaxCategory_TaxScheme_ID"
	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:AllowanceCharge[*]'
	passing d.invoice
	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',
	"TaxCategory_ID" varchar2(4000) path 'cac:TaxCategory/cbc:ID',
	"TaxCategory_Percent" number path 'cac:TaxCategory/cbc:Percent',
	"TaxCategory_TaxScheme_ID" varchar2(4000) path 'cac:TaxCategory/cac:TaxScheme/cbc:ID'
	) c;

view raw peppol_invoice_allowance_charge_v.sql hosted with ❤ by GitHub

	create or replace view peppol_invoice_tax_total_v
	as
	select
	rownum seq_id,
	d.id invoice_id,
	c."TaxTotal",
	c."TaxAmount",
	c."@currencyID"
	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:TaxTotal[*]'
	passing d.invoice
	columns
	"TaxTotal" xmltype path '.',
	"TaxAmount" number path 'cbc:TaxAmount',
	"@currencyID" varchar2(4000) path 'cbc:TaxAmount/@currencyID'
	) c;

view raw peppol_invoice_tax_total_v.sql hosted with ❤ by GitHub

	create or replace view peppol_invoice_tax_total_tax_subtotal_v
	as
	select
	rownum seq_id,
	v.invoice_id,
	v."@currencyID",
	c."TaxableAmount",
	c."TaxableAmount_@currencyID",
	c."TaxAmount",
	c."TaxAmount_@currencyID",
	c."TaxCategory_ID",
	c."TaxCategory_Percent",
	c."TaxCategory_TaxScheme_ID"
	from peppol_invoice_tax_total_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:TaxTotal/cac:TaxSubtotal[*]'
	passing v."TaxTotal"
	columns
	"TaxableAmount" number path 'cbc:TaxableAmount',
	"TaxableAmount_@currencyID" varchar2(4000) path 'cbc:TaxableAmount/@currencyID',
	"TaxAmount" number path 'cbc:TaxAmount',
	"TaxAmount_@currencyID" varchar2(4000) path 'cbc:TaxAmount/@currencyID',
	"TaxCategory_ID" varchar2(4000) path 'cac:TaxCategory/cbc:ID',
	"TaxCategory_Percent" number path 'cac:TaxCategory/cbc:Percent',
	"TaxCategory_TaxScheme_ID" varchar2(4000) path 'cac:TaxCategory/cac:TaxScheme/cbc:ID'
	) c;

view raw peppol_invoice_tax_total_tax_subtotal_v.sql hosted with ❤ by GitHub

	create or replace view peppol_invoice_invoice_line_v
	as
	select
	rownum seq_id, -- デバッグ用
	d.id invoice_id,
	c."InvoiceLine",
	c."ID",
	c."Note",
	c."InvoicedQuantity",
	c."InvoicedQuantity_@unitCode",
	c."LineExtensionAmount",
	c."LineExtensionAmount_@currencyID",
	c."AccountingCost",
	c."InvoicePeriod_StartDate",
	c."InvoicePeriod_EndDate",
	c."OrderLineReference_LineID",
	c."OrderLineReference_OrderReference_ID",
	c."DespatchLineReference_LineID",
	c."DespatchLineReference_DocumentReference_ID",
	c."Item_Description",
	c."Item_Name",
	c."Item_BuyersItemIdentification_ID",
	c."Item_SellersItemIdentification_ID",
	c."Item_StandardItemIdentification_ID",
	c."Item_StandardItemIdentification_ID_@schemeID",
	c."Item_OriginCountry_IdentificationCode",
	c."Price_PriceAmount",
	c."Price_PriceAmount_@currencyID",
	c."Price_BaseQuantity",
	c."Price_BaseQuantity_@unitCode",
	c."Price_AllowanceCharge_ChargeIndicator",
	c."Price_AllowanceCharge_Amount",
	c."Price_AllowanceCharge_Amount_@currencyID",
	c."Price_AllowanceCharge_BaseAmount",
	c."Price_AllowanceCharge_BaseAmount_@currencyID"
	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:InvoiceLine[*]'
	passing d.invoice
	columns
	"InvoiceLine" xmltype path '.',
	"ID" varchar2(4000) path 'cbc:ID',
	"Note" clob path 'cbc:Note',
	"InvoicedQuantity" number path 'cbc:InvoicedQuantity',
	"InvoicedQuantity_@unitCode" varchar2(4000) path 'cbc:InvoicedQuantity/@unitCode',
	"LineExtensionAmount" number path 'cbc:LineExtensionAmount',
	"LineExtensionAmount_@currencyID" varchar2(4000) path 'cbc:LineExtensionAmount/@currencyID',
	"AccountingCost" varchar2(4000) path 'cbc:AccountingCost',
	"InvoicePeriod_StartDate" varchar2(4000) path 'cac:InvoicePeriod/cbc:StartDate',
	"InvoicePeriod_EndDate" varchar2(4000) path 'cac:InvoicePeriod/cbc:EndDate',
	"OrderLineReference_LineID" varchar2(4000) path 'cac:OrderLineReference/cbc:LineID',
	"OrderLineReference_OrderReference_ID" varchar2(4000) path 'cac:OrderLineReference/cac:OrderReference/cbc:ID',
	"DespatchLineReference_LineID" varchar2(4000) path 'cac:DespatchLineReference/cbc:LineID',
	"DespatchLineReference_DocumentReference_ID" varchar2(4000) path 'cac:DespatchLineReference/cac:DocumentReference/cbc:ID',
	"Item_Description" varchar2(4000) path 'cac:Item/cbc:Description',
	"Item_Name" varchar2(4000) path 'cac:Item/cbc:Name',
	"Item_BuyersItemIdentification_ID" varchar2(4000) path 'cac:Item/cac:BuyersItemIdentification/cbc:ID',
	"Item_SellersItemIdentification_ID" varchar2(4000) path 'cac:Item/cac:SellersItemIdentification/cbc:ID',
	"Item_StandardItemIdentification_ID" varchar2(4000) path 'cac:Item/cac:StandardItemIdentification/cbc:ID',
	"Item_StandardItemIdentification_ID_@schemeID" varchar2(4000) path 'cac:Item/cac:StandardItemIdentification/cbc:ID/@schemeID',
	"Item_OriginCountry_IdentificationCode" varchar2(4000) path 'cac:Item/cac:OriginCountry/cbc:IdentificationCode',
	"Price_PriceAmount" number path 'cac:Price/cbc:PriceAmount',
	"Price_PriceAmount_@currencyID" varchar2(4000) path 'cac:Price/cbc:PriceAmount/@currencyID',
	"Price_BaseQuantity" number path 'cac:Price/cbc:BaseQuantity',
	"Price_BaseQuantity_@unitCode" varchar2(4000) path 'cac:Price/cbc:BaseQuantity/@unitCode',
	"Price_AllowanceCharge_ChargeIndicator" varchar2(4000) path 'cac:Price/cac:AllowanceCharge/cbc:ChargeIndicator',
	"Price_AllowanceCharge_Amount" number path 'cac:Price/cac:AllowanceCharge/cbc:Amount',
	"Price_AllowanceCharge_Amount_@currencyID" varchar2(4000) path 'cac:Price/cac:AllowanceCharge/cbc:Amount/@currencyID',
	"Price_AllowanceCharge_BaseAmount" number path 'cac:Price/cac:AllowanceCharge/cbc:BaseAmount',
	"Price_AllowanceCharge_BaseAmount_@currencyID" varchar2(4000) path 'cac:Price/cac:AllowanceCharge/cbc:BaseAmount/@currencyID'
	) c;

view raw peppol_invoice_invoice_line_v.sql hosted with ❤ by GitHub

	create or replace view peppol_invoice_invoice_line_document_reference_v
	as
	select
	rownum seq_id,
	v.invoice_id,
	v."ID" "InvoiceLine_ID",
	c."ID",
	c."ID_@schemeID",
	c."DocumentTypeCode"
	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:DocumentReference[*]'
	passing v."InvoiceLine"
	columns
	"ID" varchar2(4000) path 'cbc:ID',
	"ID_@schemeID" varchar2(4000) path 'cbc:ID/@schemeID',
	"DocumentTypeCode" varchar2(4000) path 'cbc:DocumentTypeCode'
	) c;

view raw peppol_invoice_invoice_line_document_reference_v.sql hosted with ❤ by GitHub

	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;

view raw peppol_invoice_invoice_line_allowance_charge_v.sql hosted with ❤ by GitHub

	create or replace view peppol_invoice_invoice_line_item_additional_item_property_v
	as
	select
	rownum seq_id,
	v.invoice_id,
	v."ID" "InvoiceLine_ID",
	c."Name",
	c."Value"
	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:Item/cac:AdditionalItemProperty[*]'
	passing v."InvoiceLine"
	columns
	"Name" varchar2(4000) path 'cbc:Name',
	"Value" varchar2(4000) path 'cbc:Value'
	) c;

view raw peppol_invoice_invoice_line_item_additional_item_property_v.sql hosted with ❤ by GitHub

	create or replace view peppol_invoice_invoice_line_item_classified_tax_category_v
	as
	select
	rownum seq_id,
	v.invoice_id,
	v."ID" "InvoiceLine_ID",
	c."ID",
	c."Percent",
	c."PerUnitAmount",
	c."TaxScheme_ID"
	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:Item/cac:ClassifiedTaxCategory[*]'
	passing v."InvoiceLine"
	columns
	"ID" varchar2(4000) path 'cbc:ID',
	"Percent" number path 'cbc:Percent',
	"PerUnitAmount" number path 'cbc:PerUnitAmount',
	"TaxScheme_ID" varchar2(4000) path 'cac:TaxScheme/cbc:ID'
	) c;

view raw peppol_invoice_invoice_line_item_classified_tax_category_v.sql hosted with ❤ by GitHub

	create or replace view peppol_invoice_invoice_line_item_commodity_classification_v
	as
	select
	rownum seq_id,
	v.invoice_id,
	v."ID" "InvoiceLine_ID",
	c."ItemClassificationCode",
	c."ItemClassificationCode_@listID",
	c."ItemClassificationCode_@listVersionID"
	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:Item/cac:CommodityClassification[*]'
	passing v."InvoiceLine"
	columns
	"ItemClassificationCode" varchar2(4000) path 'cbc:ItemClassificationCode',
	"ItemClassificationCode_@listID" varchar2(4000) path 'cbc:ItemClassificationCode/@listID',
	"ItemClassificationCode_@listVersionID" varchar2(4000) path 'cbc:ItemClassificationCode/@listVersionID'
	) c;

view raw peppol_invoice_invoice_line_item_commodity_classification_v.sql hosted with ❤ by GitHub

Oracle APEXアプリケーションのコピーライトとライセンス

 Oracle APEXのアプリケーションはSQLファイルまたはZIPファイル形式でエクスポートすることができます。エクスポートするファイルには、コピーライトに関する情報を含めることができます。

アプリケーション定義のコピーライト・バナーに設定する文章により、エクスポートしたアプリケーションの著作権を明示できます。

単一の.sqlファイルではなく、ZIPファイルでエクスポートするとコピーライトの出力が分かりやすいです。ZIPを展開するとlicense.txtというファイルが含まれており、このファイルにコピーライトが記述されています。

% ls -l

total 24

drwxr-xr-x@ 13 **********  staff   416  3 10 22:23 application

-rw-rw-rw-@  1 **********  staff    35  3 10 13:23 copyright.txt

-rw-rw-rw-@  1 **********  staff  7689  3 10 13:23 install.sql

% 

同じようにエクスポートするファイルに関係しますが、サポートするオブジェクトのインストールにメッセージという項目があります。

ライセンスのメッセージが設定されていると、アプリケーションのインポート時に確認を求められます。

このアプリケーションをインポートしようとすると、以下の確認を求められます。ライセンスについて同意しないと、インストールを継続することはできません。

作成したOracle APEXアプリケーションのエクスポートを、広く共有する際に設定可能なオプションになります。

完

APEX_UTIL.HOST_URL、ORDS別名、アプリケーション別名を確認するアプリ

 Oracle APEXのページやORDSのRESTサービスを呼び出す直リンクを作成したい場合があります。APEXアプリケーションが動作しているホスト名やワークスペース名などを文字列として取得する必要があるため、通常はAPEX_UTIL.HOST_URLを呼び出します。

HOST_URLに与える引数p_optionには、NULL（引数なし）、SCRIPT、APEX_PATH、IMGPREがあります。マニュアルにどのような値が返ってくるか説明はあります。しかし、返される値はOracle APEXがどのようにインストールおよび構成されているか依存するため、実際にどのような値が返ってくるか、今ひとつ分かりにくいです。

結局、APEXが動作している環境で呼び出して確認するのが手っ取り早いので、以下のPL/SQLコードを書きました。動的コンテンツのリージョンのソースになります。HOST_URL以外にAPEXワークスペース名、ORDS別名、アプリケーション別名なども印刷しています。

	declare
	l_clob clob;
	l_result varchar2(4000);
	procedure append(
	p_clob in out clob
	,p_string in varchar2
	,p_bold in boolean default false)
	as
	begin
	if p_bold then
	p_clob := p_clob || '<p><b>' || p_string || '</b></p>';
	else
	p_clob := p_clob || '<p>' || p_string || '</p>';
	end if;
	end append;
	begin
	l_clob := '<p><b>APEXの場合はapex_page.get_urlがあるため、apex_util.host_urlが必要なケースは少ない。</b></p>';
	l_result := apex_util.host_url();
	append(l_clob, q'~apex_util.host_url()~', true);
	append(l_clob, l_result);
	l_result := apex_util.host_url('SCRIPT');
	append(l_clob, q'~apex_util.host_url('SCRIPT')~', true);
	append(l_clob, l_result);
	l_result := apex_util.host_url('APEX_PATH');
	append(l_clob, q'~apex_util.host_url('APEX_PATH')~', true);
	append(l_clob, l_result);
	l_result := apex_util.host_url('IMGPRE');
	append(l_clob, q'~apex_util.host_url('IMGPRE')~', true);
	append(l_clob, l_result);
	/* ワークスペース名 */
	select workspace into l_result from apex_workspaces where workspace_id = :WORKSPACE_ID;
	append(l_clob, q'~ワークスペース名~', true);
	append(l_clob, q'~select workspace into l_result from apex_workspaces where workspace_id = :WORKSPACE_ID~', true);
	append(l_clob, l_result);
	/* ORDS別名 */
	select pattern into l_result from user_ords_schemas where parsing_schema = sys_context('USERENV','CURRENT_USER');
	append(l_clob, q'~ORDS別名~', true);
	append(l_clob, q'~select pattern into l_result from user_ords_schemas where parsing_schema = sys_context('USERENV','CURRENT_USER')~', true);
	append(l_clob, l_result);
	/* アプリケーション別名 */
	l_result := utl_url.escape(lower(:APP_ALIAS), false, 'AL32UTF8');
	append(l_clob, q'~アプリケーション別名 - ただし日本語などを設定しなければ lower(:APP_ALIAS)で十分~', true);
	append(l_clob, q'~utl_url.escape(lower(:APP_ALIAS), false, 'AL32UTF8')~', true);
	append(l_clob, l_result);
	/* それ以外の確認方法 */
	l_result := owa_util.get_cgi_env('X-APEX-BASE');
	append(l_clob, q'~owa_util.get_cgi_env('X-APEX-BASE')~', true);
	append(l_clob, l_result);
	l_result := owa_util.get_cgi_env('SCRIPT_NAME');
	append(l_clob, q'~owa_util.get_cgi_env('SCRIPT_NAME')~', true);
	append(l_clob, l_result);
	l_result := owa_util.get_cgi_env('SCRIPT_PREFIX');
	append(l_clob, q'~owa_util.get_cgi_env('SCRIPT_PREFIX')~', true);
	append(l_clob, l_result);
	l_result := owa_util.get_cgi_env('PATH_INFO');
	append(l_clob, q'~owa_util.get_cgi_env('PATH_INFO')~', true);
	append(l_clob, l_result);
	l_result := owa_util.get_cgi_env('PATH_PREFIX');
	append(l_clob, q'~owa_util.get_cgi_env('PATH_PREFIX')~', true);
	append(l_clob, l_result);
	return l_clob;
	end;

view raw report-apex-util-host-url.sql hosted with ❤ by GitHub

APEXアプリケーションに組み込んで実行すると、以下のように表示されます。

このアプリケーションのエクスポートを以下に置きました。

https://github.com/ujnak/apexapps/blob/master/exports/apex-util-host-url.zip

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

完

rinna株式会社のText to Image -v2のAPIを使用する

rinna株式会社が提供しているText to Image - v2のAPIをOracle APEXのアプリケーションから呼び出して、テキストから画像を生成してみます。音声か画像かの違いなので、以前のText to Speech - v2を呼び出すAPEXアプリケーションとほとんど同じです。

手順として異なる部分に絞って、アプリケーションの作成作業を紹介します。

生成した画像は表RINNA_TEXT_TO_IMAGESに保存します。以下のクイックSQLのモデルから作成します。

# prefix: rinna
# auditcols: true
text_to_images
    text clob
    content_type vc16
    image blob
    scale num
    nsfwContentDetected vc1
    type vc80

画像を取り出すRESTサービスのソースは以下になります。

select content_type, image from rinna_text_to_images where id = :id

テンプレートはimage、メソッドはGET、ソース・タイプはMedia Resourceです。完全なURLはコピーしておきます。

APEXアプリケーションを作成します。

名前はText to Image、追加する対話モード・レポートのページ名はImages、ソースとして表RINNA_TEXT_TO_IMAGESを指定します。

ホーム・ページにテキスト（APIの引数promptsになる）を入力するページ・アイテムを作成します。

識別の名前はP1_TEXT、タイプはテキスト領域、検証の必須の値はONにします。

スケールを指定するページ・アイテムを作成します。

識別の名前はP1_SCALE、タイプは数値フィールド、ラベルはscaleとします。設定の最小値に0、最大値に20を設定し、設定の必須の値をONにします。デフォルトの静的値として7.5を設定します。

Text to Imageを実行するボタンを作成します。

識別のボタン名はTEXT_TO_IMAGE、ラベルはText To Image、動作のアクションとして、デフォルトのページの送信を選択します。

生成された画像を表示するページ・アイテムを作成します。

識別の名前はP1_IMAGE、タイプとしてイメージの表示を選択します。ラベルは画像、設定の基準としてImage URL stored in Page Item Valueを選択します。

アイテムの配置は以上です。

ボタンTEXT_TO_IMAGEをクリックしたときに実行されるプロセスを作成します。

作成したプロセスの識別の名前はText to Imageとします。タイプはコードを実行です。ソースのPL/SQLコードとして、以下を記述します。ワークスペース名がapexdevと決め打ちにしているので、その部分は変更が必要です。

	declare
	l_request json_object_t;
	l_request_clob clob;
	l_response json_object_t;
	l_response_clob clob;
	l_id rinna_text_to_images.id%type;
	l_image clob;
	l_image_blob blob;
	l_nsfwContentDetected rinna_text_to_images.nsfwContentDetected%type;
	l_type rinna_text_to_images.type%type;
	begin
	/*
	* Text To Image V2のリクエストを作成する。
	*
	* 参照: https://developers.rinna.co.jp/api-details#api=z05-text2image-jsd
	*/
	l_request := json_object_t();
	l_request.put('prompts', :P1_TEXT);
	l_request.put('scale', to_number(:P1_SCALE));
	l_request_clob := l_request.to_clob();
	-- apex_debug.info(l_request_clob);
	/*
	* Text To Image の呼び出し。
	*/
	apex_web_service.clear_request_headers;
	apex_web_service.set_request_headers('Content-Type','application/json', p_reset => false);
	apex_web_service.set_request_headers('Cache-Control','no-cache', p_reset => false);
	l_response_clob := apex_web_service.make_rest_request(
	p_url => 'https://api.rinna.co.jp/models/tti/v2'
	,p_http_method => 'POST'
	,p_body => l_request_clob
	,p_credential_static_id => 'RINNA_DEVELOPER_KEY'
	);
	if apex_web_service.g_status_code <> 200 then
	apex_debug.info('REST API Failed, %s, %s', apex_web_service.g_status_code, l_response_clob);
	raise_application_error(-20001, 'Text To Image = ' || apex_web_service.g_status_code);
	end if;
	/*
	* レスポンスから生成された画像を取得する。
	*/
	l_response := json_object_t(l_response_clob);
	l_image := l_response.get_clob('image');
	l_image_blob := null;
	if instr(l_image, 'data:image/png;base64,') <> 1 then
	-- image/pngではない？ 記録して無視する。
	apex_debug.info(l_response_clob);
	else
	/* APIが生成する画像はimage/pngであることを常に期待している。 */
	l_image := substr(l_image, length('data:image/png;base64,')+1);
	l_image_blob := apex_web_service.clobbase642blob(l_image);
	end if;
	l_nsfwContentDetected := 'N';
	if l_response.get_boolean('nsfwContentDetected') then
	l_nsfwContentDetected := 'Y';
	end if;
	l_type := l_response.get_string('type');
	/*
	* テキストと画像の両方を保存する。
	*/
	insert into rinna_text_to_images(text, scale, image, content_type, nsfwContentDetected, type)
	values(:P1_TEXT, :P1_SCALE, l_image_blob, 'image/png', l_nsfwContentDetected, l_type)
	returning id into l_id;
	/* ワークスペース名はapexdevであると仮定。違う場合は変更する。 */
	:P1_IMAGE := apex_util.host_url() || '/ords/apexdev/rinna/image?id=' || l_id;
	end;

view raw rinna-text-to-image.sql hosted with ❤ by GitHub

サーバー側の条件のボタン押下時にTEXT_TO_IMAGEを選択します。

ページ・デザイナで対話モード・レポートのページを開き、列IMAGEのタイプをイメージの表示に変更します。BLOB属性のMIMEタイプ列としてCONTENT_TYPEを指定します。

以上でアプリケーションは完成です。

アプリケーションを実行すると記事の先頭のような動作になります。

作成したAPEXアプリケーションのエクスポートを以下に置きました。

https://github.com/ujnak/apexapps/blob/master/exports/text-to-image.zip

rinna株式以外より提供されているAPIのTerms of UseおよびFAQなどは、利用者ご自身にて確認していだくようお願いします。FAQには、APIは現在、試験運用中で検証を除く業務用利用は禁止しております、と記載されています。

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

完

LlamaIndexにChatGPT(gpt-3.5-turbo)を使ったチャット・サーバーを作る

 以前に作成したOracle Cloudのコンピュート・インスタンスにLlamaIndexをインストールしてチャット・サーバーを作成し、Oracle APEXのアプリケーションから呼び出してみました。

以下の記事を参照しています。

Llama Index（GPT Index）にDevelopersIOの記事を100件読み込ませて質問してみた

https://dev.classmethod.jp/articles/llama-index-developersio-articles/

LlamaIndex で ChatGPT API を試す
https://note.com/npaka/n/ncbb858cf11c3

LangChain の チャットモデル (ChatGPTの新しい抽象化) を試す

https://note.com/npaka/n/n403fc29a02c7
LlamaIndex クイックスタートガイド

https://note.com/npaka/n/n8c3867a55837

チャット・サーバーの実行には、Oracle CloudのAlways Free（無料）で作成できるAmpere A1の4OCPU, 24GBのコンピュート・インスタンスを使用しています。

このサーバーは、以前にOpenAIのWisperを実装したときに作成したものです。

OpenAI Whisperを使った文字起こしアプリの作成(2) - UbuntuへのWhisperの実装

http://apexugj.blogspot.com/2023/01/openai-whisper-2-ubuntu.html
OpenAI Whisperを使った文字起こしアプリの作成(3) - Flaskを使ったAPIサーバー

http://apexugj.blogspot.com/2023/01/openai-whisper-3-api.html

Wisperの実装は不要ですが、コンピュート・インスタンスへのホスト名の割り当て（DNSでIPアドレスの解決ができる）および、サーバー証明書の発行までは準備作業として必要です。

以上の準備ができているところから作業を始めます。

Ubuntuのインスタンスにsshでログインします。作業はすべてホーム・ディレクトリ上で行います。

llama-indexをインストールします。

pip install llama-index

ubuntu@mywhisper2:~$ pip install llama-index

Processing ./.cache/pip/wheels/57/26/ea/fc7cbfb104ac458cb33bfd2610d0e5ca597b184af49e73c1bf/llama_index-0.4.23-py3-none-any.whl

Requirement already satisfied: numpy in /usr/local/lib/python3.8/dist-packages (from llama-index) (1.23.5)

Requirement already satisfied: langchain in ./.local/lib/python3.8/site-packages (from llama-index) (0.0.104)

[中略]

Requirement already satisfied: six>=1.5 in /usr/lib/python3/dist-packages (from python-dateutil>=2.8.1->pandas->llama-index) (1.14.0)

Requirement already satisfied: idna>=2.0 in /usr/lib/python3/dist-packages (from yarl<2.0,>=1.0->aiohttp<4.0.0,>=3.8.3->langchain->llama-index) (2.8)

Installing collected packages: llama-index

Successfully installed llama-index-0.4.23

ubuntu@mywhisper2:~$ 

ディレクトリdataを作成します。このディレクトリの下に、チャット・サーバーが知識として使用するデータを配置します。

mkdir data

ubuntu@mywhisper2:~$ mkdir data

ubuntu@mywhisper2:~$ 

ディレクトリdataの下にapex_introduction.txtを配置しました。Oracle APEXの紹介が記述されています。このデータはこの通りでなくても全く問題ないので、それぞれ興味のあるデータを配置されると良いでしょう。

ファイルchat-server.pyとして、以下の内容が記述されたファイルを作成します。index.jsonがあれば作成済みのインデックスとして読み込みます。無い場合はインデックスの作成を行います。そのため、初回実行時は起動に時間がかかります。また、ディレクトリdata以下の内容を更新した場合は、index.jsonを削除してchat-servcer.pyを再起動する必要があります。

/chatにPOSTされたJSONドキュメント{ "query": "問合せ文字列" }を受け取って、応答であるJSONドキュメント{ "answer": "応答となる文字列" }を返します。

	import logging
	import sys
	import os
	import json
	from llama_index import GPTSimpleVectorIndex, SimpleDirectoryReader
	from llama_index.langchain_helpers.chatgpt import ChatGPTLLMPredictor
	from flask import Flask, request
	# ログレベルと出力先の設定
	logging.basicConfig(stream=sys.stdout, level=logging.INFO, force=True)
	logging.getLogger().addHandler(logging.StreamHandler(stream=sys.stdout))
	# インデックスの作成。
	# ファイルindex.jsonがないときだけdataディレクトリの内容を元に
	# 索引を作成する。
	index_file = "index.json"
	index = None
	if os.path.exists(index_file):
	index = GPTSimpleVectorIndex.load_from_disk(index_file)
	else:
	llm_predictor = ChatGPTLLMPredictor(
	# systemメッセージを変更すると、回答の口調を変更できる。
	prepend_messages = [
	{"role": "system", "content": "You are a helpful assistant."},
	]
	)
	#
	documents = SimpleDirectoryReader('data').load_data()
	index = GPTSimpleVectorIndex(
	documents=documents,
	llm_predictor=llm_predictor
	)
	index.save_to_disk(index_file)
	# /chatの呼び出しに対する処理
	app = Flask(__name__)
	@app.route('/chat', methods=['POST'])
	def chat():
	answer_dict = {}
	if request.method == 'POST':
	query = request.json['query']
	print("received query", query)
	answer = index.query(query)
	print("generated answer", answer)
	if answer.response:
	answer_dict["answer"] = str(answer)
	answer_json = json.dumps(answer_dict, ensure_ascii=False)
	# answer_json = json.dumps(answer_dict)
	print("response string", answer_json)
	return answer_json
	if __name__ == "__main__":
	app.run(host='0.0.0.0', port=8443, ssl_context=('./certs/fullchain.pem', './certs/privkey.pem'), debug=True)

view raw chat-server.py hosted with ❤ by GitHub

環境変数OPENAI_API_KEYにOpenAIのAPIキーを設定して、chat-server.pyを実行します。あらかじめディレクトリcertsの下にfullchain.pem、privkey.pemを作成しておきます。

ubuntu@mywhisper2:~$ export OPENAI_API_KEY=OpenAIのAPIキー

ubuntu@mywhisper2:~$ python chat-server.py 

/usr/lib/python3/dist-packages/requests/__init__.py:89: RequestsDependencyWarning: urllib3 (1.26.13) or chardet (3.0.4) doesn't match a supported version!

  warnings.warn("urllib3 ({}) or chardet ({}) doesn't match a supported "

 * Serving Flask app 'chat-server'

 * Debug mode: on

INFO:werkzeug:WARNING: This is a development server. Do not use it in a production deployment. Use a production WSGI server instead.

 * Running on all addresses (0.0.0.0)

 * Running on https://127.0.0.1:8443

 * Running on https://10.0.0.131:8443

WARNING: This is a development server. Do not use it in a production deployment. Use a production WSGI server instead.

 * Running on all addresses (0.0.0.0)

 * Running on https://127.0.0.1:8443

 * Running on https://10.0.0.131:8443

INFO:werkzeug:Press CTRL+C to quit

Press CTRL+C to quit

INFO:werkzeug: * Restarting with stat

 * Restarting with stat

/usr/lib/python3/dist-packages/requests/__init__.py:89: RequestsDependencyWarning: urllib3 (1.26.13) or chardet (3.0.4) doesn't match a supported version!

  warnings.warn("urllib3 ({}) or chardet ({}) doesn't match a supported "

WARNING:werkzeug: * Debugger is active!

 * Debugger is active!

INFO:werkzeug: * Debugger PIN: 197-750-656

 * Debugger PIN: 197-750-656

以上でチャット・サーバーが実行されました。

このサーバーに質問するOracle APEXのアプリケーションを作成します。

アプリケーション作成ウィザードを起動し、空のアプリケーションを作成します。名前はChat Clientとします。

アプリケーションが作成されたらアプリケーション定義を開き、置換文字列G_ENDPOINTとして、チャット・サーバーの呼び出しURLを設定します。以下のようなURLになります。

https://チャット・サーバーのホスト名/chat

ページ・デザイナでホーム・ページを開きます。

問合せ文字列を入力するページ・アイテムを作成します。

識別の名前はP1_QUERY、タイプはテキスト領域、ラベルはQueryとします。

問合せを送信するボタンを作成します。

識別のボタン名はSUBMIT、ラベルはSubmit、動作のアクションはデフォルトのページの送信とします。

チャット・サーバーが返す応答を印刷するページ・アイテムを作成します。

識別の名前はP1_ANSWER、タイプはテキスト領域、ラベルはAnswerとします。

プロセス・ビューを開き、チャット・サーバーにリクエストを送信するプロセスを作成します。

識別の名前はQuery on Chat Server、タイプはコードの実行を選択します。ソースのPL/SQLコードとして以下を記述します。

	declare
	l_query json_object_t;
	l_query_clob clob;
	l_answer_clob clob;
	l_answer json_object_t;
	begin
	l_query := json_object_t();
	l_query.put('query', :P1_QUERY);
	l_query_clob := l_query.to_clob();
	apex_web_service.clear_request_headers;
	apex_web_service.set_request_headers('Content-Type', 'application/json');
	l_answer_clob := apex_web_service.make_rest_request(
	p_url => :G_ENDPOINT
	,p_http_method => 'POST'
	,p_body => l_query_clob
	);
	if apex_web_service.g_status_code <> 200 then
	apex_debug.info('Chat Server Error %s, %s',
	apex_web_service.g_status_code
	,l_answer_clob);
	raise_application_error(-20001, 'Chat Server Error = '
	|| apex_web_service.g_status_code);
	end if;
	l_answer := json_object_t(l_answer_clob);
	:P1_ANSWER := l_answer.get_string('answer');
	end;

view raw query-on-chat-server.sql hosted with ❤ by GitHub

サーバー側の条件のボタン押下時として、SUBMITを選択します。

以上でアプリケーションは完成です。実行すると記事の最初のGIF動画のように動作します。サーバー側では、以下のようなメッセージが表示されます。

クラスChatGPTLLMPredictorの引数prepend_messagesとして、ロール（system, user, assistant）の異なる履歴が設定できるようですが、それも含めてGPTSimpleVectorIndexでインデックスを作成するコードになっています。ChatGPTのように履歴を含めて問合せをする場合、毎回インデックスの作成が必要になり効率が良くないように見えます。今のところ、どうすればよいか分かりません。

今回作成したアプリケーションのエクスポートを以下に置きました。

https://github.com/ujnak/apexapps/blob/master/exports/llama-index-chat-client.zip

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

完