We gebruiken een module om XML-inkooporders om te zetten in verkooporders voor Exact Online. Sinds een aantal dagen werkt deze module niet meer.
Bij het uitvoeren van de module verschijnt de volgende foutmelding:
An error occured - bffc9f60-e6e9-48cf-ae39-09b8e6a9c79b
itgengpr015: Onbekende tabel ‘INBOUNDPURCHASEORDERS’.
Ik vermoed dat hier hetzelfde issue speelt als bij Itgengpr015 melding bij gebruik net aangemaakte tabel vanuit PSQL blok op App Online
De tabel inboundpurchaseorders wordt in-memory aangemaakt en vervolgens gebruikt, maar daar lijkt iets mis te gaan.
Hoe kunnen we dit het beste oplossen binnen het PSQL-blok?
declare
p_run boolean;
--
g_division_code int64 := 2847907;
--
l_filename varchar2;
l_payload blob;
l_workbook excel_workbook;
l_worksheet excel_worksheet;
l_binary blob;
l_cnt number;
l_cnt_missing_item number;
l_cnt_missing_account number;
l_list_missing_items varchar2;
l_list_missing_accounts varchar2;
begin
--
-- Get parameters.
--
p_run := cast(cloud_http. get_request_form_value('p_run') as boolean);
--
if coalesce(p_run, false) = false
then
--
-- Enter parameters.
--
cloud_http.set_use_template(true);
cloud_http.set_template_step_name(translate_resources('{res:itgen_parameters}'));
cloud_http.append_line_to_response_body_text('<form method="post" enctype="multipart/form-data">');
cloud_http.append_line_to_response_body_text('<input type="hidden" id="p_run" name="p_run" value="true"/>');
cloud_http.append_line_to_response_body_text('<ul>');
cloud_http.append_line_to_response_body_text('<li><label for="p_file">' || translate_resources('{res:itgen_file}') || '</label></li><li><input type="file" id="p_file" name="p_file" accept=".xml" required/></li>');
cloud_http.append_line_to_response_body_text('<li><input type="submit" value="Inlezen in Exact Online"/></li>');
cloud_http.append_line_to_response_body_text('</ul>');
cloud_http.append_line_to_response_body_text('</form>');
else
--
-- Process form.
--
l_filename := cloud_http.get_request_form_file_name('p_file');
--
if l_filename is null
then
raise_application_error('sample001', 'The file name is empty.');
end if;
--
l_payload := cloud_http.get_request_form_file_contents('p_file');
--
if l_payload is null
then
raise_application_error('sample002', 'The file payload is empty.');
end if;
--
-- Choose company.
--
use select code, 'eol' from systemdivisions@eol where code = g_division_code;
--
-- Find available parties with GLN-number.
--
create or replace table AccountsAvailable@inmemorystorage
as
select act.Division
, act.GlnNumber
, act.Code
, act.Id
from AccountsIncremental act
where act.GlnNumber is not null
;
--
-- Make all items persistent.
--
create or replace table ItemsAvailable@inmemorystorage
as
select itm.Division
, itm.Code
, upper(itm.Code) CodeUC
, itm.Id
, itm.Description
from ItemsIncremental@eol itm
;
--
-- Convert Exact Globe format to a table with order lines.
--
create or replace table InboundPurchaseOrders@inmemorystorage
as
select odr.*
, upper(odr.ItemCode) ItemCodeUC
from xmltable
( '/eExact/Orders/Order/OrderLine'
passing l_payload
columns OrderNumber varchar2 path '../@number'
, LineNumber int32 path '@lineNo'
, Quantity int32 path 'Quantity'
, ItemCode varchar2 path 'ItemCode'
, PriceCurrency varchar2 path 'Price/Currency/@code'
, PriceValue decimal path 'Price/Value'
, DeliveryDate date path 'Delivery/Date'
, Description varchar2 path 'Description'
, OrderRef varchar2 path '../OurRef'
, OrderFirstName varchar2 path '../Resource/FirstName'
, OrderLastName varchar2 path '../Resource/LastName'
, OrderAtCreditorNumber varchar2 path '../OrderedAt/Creditor/@number'
, OrderAtCreditorCode varchar2 path '../OrderedAt/Creditor/@code'
, OrderAtCreditorName varchar2 path '../OrderedAt/Creditor/Name'
, OrderAtDate date path '../OrderedAt/Date'
, DeliverToWarehouesCode varchar2 path '../DeliverTo/Warehouse/@code'
, DeliverToGlnNumber varchar2 path '../DeliverTo/Address/AddressLine2'
) odr
;
--
-- Check that there is at least one purchase order line.
--
select count(*)
into l_cnt
from InboundPurchaseOrders@inmemorystorage
;
if l_cnt = 0
then
raise_application_error('XXMOV002', 'No purchase orders could be extracted from the file.');
end if;
--
-- Add missing unique IDs from Exact Online.
--
create or replace table InboundPurchaseOrdersMatched@inmemorystorage
as
select itm.Division
, itm.Id ItemID
, act.Code DeliverToAccountCode
, act.Id DeliverToAccountId
, odr.*
from InboundPurchaseOrders@inmemorystorage odr
left
outer
join AccountsAvailable@inmemorystorage act
on act.GlnNumber = odr.DeliverToGlnNumber
left
outer
join ItemsAvailable@inmemorystorage itm
on itm.CodeUC = odr.ItemCodeUC
;
--
-- Check that all entries in the purchase order lines could be found:
--
-- * item exists,
-- * GLN number is known.
--
select count(*)
cnt
, sum(case when ItemId is null then 1 else 0 end)
cnt_missing_item
, sum(case when DeliverToAccountId is null then 1 else 0 end)
cnt_missing_account
, listagg(distinct case when ItemId is null then ItemCodeUC else null end, ', ')
list_missing_items
, listagg(distinct case when DeliverToAccountId is null then DeliverToGlnNumber else null end, ', ')
list_missing_accounts
into l_cnt
, l_cnt_missing_item
, l_cnt_missing_account
, l_list_missing_items
, l_list_missing_accounts
from InboundPurchaseOrdersMatched@inmemorystorage
;
if l_cnt = 0
then
raise_application_error('XXMOV002', 'No inbound purchase orders could be matched with available customers and items.');
end if;
if l_cnt_missing_item > 0
then
raise_application_error('XXMOV003', 'There are ' || to_char(l_cnt_missing_item) || ' lines with unknown items in the file: ' || l_list_missing_items || '.');
end if;
if l_cnt_missing_account > 0
then
raise_application_error('XXMOV004', 'There are ' || to_char(l_cnt_missing_account) || ' lines with unknown accounts in the file with GLN: ' || l_list_missing_accounts || '.');
end if;
--
-- Convert order and order lines to XML format.
--
create or replace table sourcelinesxml@inmemorystorage
as
select src.Division
, '<SalesOrder>'
|| chr(13)
|| '<OrderedBy ID="{'
|| to_char(src.DeliverToAccountId)
|| '}" code="'
|| src.DeliverToAccountCode
|| '" />'
|| chr(13)
|| '<DeliverTo ID="{'
|| to_char(src.DeliverToAccountId)
|| '}" code="'
|| src.DeliverToAccountCode
|| '" />'
|| chr(13)
|| '<InvoiceTo ID="{'
|| to_char(src.DeliverToAccountId)
|| '}" code="'
|| src.DeliverToAccountCode
|| '" />'
|| xmlelement('YourRef', src.OrderRef, false)
head
, '<SalesOrderLine line="'
|| to_char(src.LineNumber)
|| '">'
|| xmlelement('Description', src.description, false)
|| '<Item code="'
|| src.ItemCode
|| '" ID="'
|| to_char(src.ItemID)
|| '" />'
|| xmlelement('Quantity', src.quantity, false)
|| xmlelement('DeliveryDate', src.DeliveryDate, false)
|| '<UnitPrice>'
|| '<Currency code="' || src.PriceCurrency || '" />'
|| '<Value code="' || to_char(src.PriceValue) || '" />'
|| '</UnitPrice>'
|| '</SalesOrderLine>'
line
from InboundPurchaseOrdersMatched@inmemorystorage src
;
--
-- Prepare XML upload.
--
create or replace table xmlupload@inmemorystorage
as
select 'SalesOrders' topic
, division_code
, null orig_system_reference
, cast('origsystemgroup' as varchar2 null) orig_system_group
, xmlformat
( '<?xml version="1.0" encoding="utf-8"?>'
|| '<eExact xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="eExact-XML.xsd">'
|| xmlcomment('For company ' || division_code)
|| xmlcomment('Original system group ' || 'origsystemgroup')
|| '<SalesOrders>'
|| xml
|| '</SalesOrders>'
|| '</eExact>'
)
filecontents
from ( select division_code
, listagg
( head
|| lines
|| '</SalesOrder>'
, ''
)
xml
from ( select brl.Division division_code
, brl.head
, listagg(brl.line, '') lines
from sourcelinesxml@inmemorystorage brl
group
by brl.head
, brl.division
)
group
by division_code
)
;
--
-- Upload into Exact Online.
--
insert into UploadXMLTopics@eol
( topic
, payload
, division_code
, orig_system_reference
, orig_system_group
, fragment_payload_flag
, fragment_max_size_characters
, fail_on_error
)
select topic
, filecontents
, to_char(division_code)
, orig_system_reference
, orig_system_group
--
-- Try to split into pieces of 25 KB at most.
--
, true fragment_payload_flag
, 25000 fragment_max_size_characters
--
-- Returns error through the user interface, not as an HTTP 500 error.
--
, false fail_on_error
from xmlupload@inmemorystorage
order
by division_code
, orig_system_reference
, orig_system_group
;
--
-- Retrieve results.
--
l_workbook := excel.new();
--
-- Add a worksheet with a title.
--
l_worksheet := excel.add_worksheet
( l_workbook
, 'Parameters'
);
excel.set_cell_contents(l_worksheet, 'A1', 'Dump of Exact Online import results using Invantive Cloud');
excel.set_cell_contents(l_worksheet, 'A2', 'Created ' || to_char(sysdate));
--
create or replace table output@inmemorystorage
as
select /*+ result_set_name("Results") */
uxt.transaction_id
, uxt.successful
, uxt.division_code
, uxt.orig_system_reference
, uxt.orig_system_group
, uxt.date_started_utc
, uxt.date_ended_utc
, substr(uxt.payload, 1, 32767)
payload_32k
label "{res:itgen_eol_payload}"
, substr(uxt.result, 1, 32767)
result_32k
label "{res:itgen_result}"
, substr(uxt.result_errors, 1, 32767)
result_errors_32k
label "{res:itgen_eol_result_errors}"
, substr(uxt.payload_retry_failed, 1, 32767)
payload_retry_failed
label "{res:itgen_eol_payload_retry_failed}"
from UploadXMLTopicFragments@eol uxt
order
by uxt.transaction_id desc
;
--
-- Generate output.
--
l_worksheet := excel.add_worksheet
( l_workbook
, 'Upload Results'
);
excel.fill_using_query(l_worksheet, 'select * from output@inmemorystorage');
--
-- Retrieve the resulting XLSX-file.
--
l_binary := excel.export_to_xlsx(l_workbook);
--
-- Return the XLSX file to the user.
--
cloud_http.set_response_body_binary(l_binary);
cloud_http.set_response_content_type('application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
cloud_http.set_response_header_value('Content-Disposition', 'attachment; filename="upload-results-' || basename(l_filename) || '.xlsx"');
end if;
end;