De logistiek dienstverlener ODTH maakt gebruik van SAP voor haar logistieke diensten. Het SAP-systeem kan gevoed worden met voorgedefinieerde Excel-formaten zoals:
- INB: aankondiging goederenontvangst op magazijn,
- PICK: verzoek om goederen te verzenden.
Deze Excel-formaten kunnen eenvoudig uit Exact Online opgebouwd worden met de bijgevoegde Invantive Cloud modules. Deze modules dienen geregistreerd te worden binnen een Invantive Cloud-applicatie en kunnen daarna gestart worden. Ook dient een Exact Online database geregistreerd te zijn. Voor het gebruik van een Invantive Cloud-applicatie zie Excel download uit Exact Online in eigen formaat met een cloud app. Voor het aanmaken van een Exact Online database zie de volgende video:
INB: Aankondiging goederenontvangst op magazijn
Het invoerscherm ziet er als volgt uit:
Het eindresultaat is bijvoorbeeld:
Om de volledige modulecode te zien: klik op openvouwen.
declare
--
-- Input parameters through URL GET parameter.
--
p_run boolean;
p_order_number int32;
--
-- Constants.
--
g_division_code int64 := 123456;
--
-- Local variables.
--
l_order_number varchar2;
l_workbook excel_workbook;
l_worksheet excel_worksheet;
l_binary blob;
l_excel_row number;
l_found boolean;
begin
--
-- Get whether to run the program. When not specified or false, display a parameter form.
--
p_run := cast(cloud_http.get_request_query_parameter_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="get" >');
cloud_http.append_line_to_response_body_text('<ul>');
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('<li><label for="p_order_number">' || translate_resources('{res:itgen_eol_order_number}') || '</label></li><li><input type="number" id="p_order_number" name="p_order_number" value="' || coalesce(to_char(p_order_number), '16000747') || '" required/></li>');
cloud_http.append_line_to_response_body_text('<li><input type="submit" value="INB.xlsx maken"/></li>');
cloud_http.append_line_to_response_body_text('</ul>');
cloud_http.append_line_to_response_body_text('</form>');
else
--
-- Process form.
--
l_order_number := to_number(cloud_http.get_request_query_parameter_value('p_order_number'));
--
if l_order_number is null
then
raise_application_error('sample001', 'The order number is missing.', 'Please add a parameter p_order_number to the URL.');
end if;
--
-- Choose company.
--
use select code, 'eol' from SystemDivisions@eol where code = g_division_code;
--
-- Place results in an Excel spreadsheet, consisting of a header with the delivery lines.
--
l_workbook := excel.new();
--
l_worksheet := excel.add_worksheet
( l_workbook
, 'Purchase Order ' || to_char(l_order_number)
);
--
l_found := false;
for r
in
( select por.PurchaseOrderId
, to_char(por.OrderNumber) || 'INB.xlsx'
filename
label 'Filename'
, por.OrderNumber
label 'Unique Reference Number'
, por.ReceiptDate
label 'Delivery Date'
, por.YourRef
label 'Container'
from ExactOnlineREST..PurchaseOrders@eol por
where por.OrderNumber = l_order_number
)
loop
excel.set_cell_contents(l_worksheet, 'A1', 'Unique Reference Number');
excel.set_cell_contents(l_worksheet, 'B1', l_order_number);
--
excel.set_cell_contents(l_worksheet, 'A2', 'Delivery Date');
excel.set_cell_contents(l_worksheet, 'B2', r.ReceiptDate);
--
excel.set_cell_contents(l_worksheet, 'A3', 'Container');
excel.set_cell_contents(l_worksheet, 'B3', r.YourRef);
--
excel.set_cell_contents(l_worksheet, 'B4', 'Order Overview');
--
excel.set_cell_contents(l_worksheet, 'A6', 'Code');
excel.set_cell_contents(l_worksheet, 'B6', 'Description');
excel.set_cell_contents(l_worksheet, 'C6', 'Delivered QTY');
excel.set_cell_contents(l_worksheet, 'D6', 'Lot Number');
--
l_excel_row := 7;
for r_line
in
( select ple.ItemCode
label 'Code'
, ple.ItemDescription
label 'Description'
, ple.Quantity
label 'Delivered QTY'
, 'xxx'
LotNumber
label 'Lot Number'
from ExactOnlineREST..PurchaseOrderLines@eol ple
where ple.PurchaseOrderId = r.PurchaseOrderId
order
by ple.LineNumber
)
loop
excel.set_cell_contents(l_worksheet, 'A' || to_char(l_excel_row), r_line.ItemCode);
excel.set_cell_contents(l_worksheet, 'B' || to_char(l_excel_row), r_line.ItemDescription);
excel.set_cell_contents(l_worksheet, 'C' || to_char(l_excel_row), r_line.Quantity);
excel.set_cell_contents(l_worksheet, 'D' || to_char(l_excel_row), r_line.LotNumber);
l_excel_row := l_excel_row + 1;
end loop;
--
--
-- 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="' || r.filename || '"');
--
l_found := true;
end loop;
--
if l_found = false
then
raise_application_error
( 'sample002'
, 'There is no purchase order with number ' || to_char(l_order_number) || ' found in division ' || to_char(g_division_code) || '.'
, natural_key => to_char(p_order_number)
);
end if;
end if;
end;
PICK: verzoek om goederen te verzenden
Het invoerscherm ziet er als volgt uit:
Het eindresultaat is bijvoorbeeld:
Om de volledige modulecode te zien: klik op openvouwen.
declare
--
-- Input parameters through URL GET parameter.
--
p_run boolean;
p_delivery_number int32;
--
-- Constants.
--
g_division_code int64 := 123456;
--
-- Local variables.
--
l_delivery_number varchar2;
l_workbook excel_workbook;
l_worksheet excel_worksheet;
l_binary blob;
l_excel_row number;
l_found boolean;
begin
--
-- When p_run is not set or false, display the parameter form. Otherwise create the Excel workbook.
--
p_run := cast(cloud_http.get_request_query_parameter_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="get" >');
cloud_http.append_line_to_response_body_text('<ul>');
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('<li><label for="p_delivery_number">' || translate_resources('{res:itgen_eol_delivery_number}') || '</label></li><li><input type="number" id="p_delivery_number" name="p_delivery_number" value="' || coalesce(to_char(p_delivery_number), '16001780') || '" required/></li>');
cloud_http.append_line_to_response_body_text('<li><input type="submit" value="PICK.xlsx maken"/></li>');
cloud_http.append_line_to_response_body_text('</ul>');
cloud_http.append_line_to_response_body_text('</form>');
else
--
-- Process form.
--
l_delivery_number := to_number(cloud_http.get_request_query_parameter_value('p_delivery_number'));
--
if l_delivery_number is null
then
raise_application_error('sample003', 'The delivery number is missing.', 'Please add a parameter p_delivery_number to the URL.');
end if;
--
-- Choose company.
--
use select code, 'eol' from SystemDivisions@eol where code = g_division_code;
--
-- Retrieve results.
--
l_workbook := excel.new();
--
l_worksheet := excel.add_worksheet
( l_workbook
, 'Pick Order ' || to_char(l_delivery_number)
);
--
l_found := false;
for r
in
( select gdy.EntryId
, to_char(gdy.DeliveryNumber) || 'PICK.xlsx'
filename
, gdy.DeliveryNumber
label 'Pick Note Number'
, gdy.DeliveryAccountName
label 'Customer Name'
, ads.AddressLine1
|| case
when ads.AddressLine2 is not null
then ', ' || ads.AddressLine2
end
street
label 'Street'
, ads.Postcode
label 'Postal Code'
, ads.City
label 'Address'
, ads.Country
label 'Country'
, gdy.DeliveryDate
label 'Pick Note Date'
from ExactOnlineREST..GoodsDeliveries@eol gdy
join ExactOnlineREST..Addresses@eol ads
on ads.Id = gdy.DeliveryAddress
where gdy.DeliveryNumber = l_delivery_number
)
loop
excel.set_cell_contents(l_worksheet, 'A1', 'Pick Note Number');
excel.set_cell_contents(l_worksheet, 'B1', l_delivery_number);
--
excel.set_cell_contents(l_worksheet, 'A2', 'Customer Name');
excel.set_cell_contents(l_worksheet, 'B2', r.DeliveryAccountName);
--
excel.set_cell_contents(l_worksheet, 'A3', 'Street');
excel.set_cell_contents(l_worksheet, 'B3', r.Street);
--
excel.set_cell_contents(l_worksheet, 'A4', 'Postal Code');
excel.set_cell_contents(l_worksheet, 'B4', r.Postcode);
--
excel.set_cell_contents(l_worksheet, 'A5', 'Address');
excel.set_cell_contents(l_worksheet, 'B5', r.City);
--
excel.set_cell_contents(l_worksheet, 'A6', 'Country');
excel.set_cell_contents(l_worksheet, 'B6', r.Country);
--
excel.set_cell_contents(l_worksheet, 'A7', 'Pick Note Date');
excel.set_cell_contents(l_worksheet, 'B7', r.DeliveryDate);
--
excel.set_cell_contents(l_worksheet, 'A8', 'Order Overview');
--
excel.set_cell_contents(l_worksheet, 'A10', 'Part');
excel.set_cell_contents(l_worksheet, 'B10', 'Description');
excel.set_cell_contents(l_worksheet, 'C10', 'Pick Note Qty');
excel.set_cell_contents(l_worksheet, 'D10', 'Lot N*');
--
l_excel_row := 11;
for r_line
in
( select ItemCode
label 'Part'
, ItemDescription
label 'Description'
, QuantityDelivered
label 'Pick Note Qty'
, 'xxx'
LotNumber
label 'Lot N*'
from ExactOnlineREST..GoodsDeliveryLines@eol gle
where gle.EntryId = r.EntryId
order
by gle.LineNumber
)
loop
excel.set_cell_contents(l_worksheet, 'A' || to_char(l_excel_row), r_line.ItemCode);
excel.set_cell_contents(l_worksheet, 'B' || to_char(l_excel_row), r_line.ItemDescription);
excel.set_cell_contents(l_worksheet, 'C' || to_char(l_excel_row), r_line.QuantityDelivered);
excel.set_cell_contents(l_worksheet, 'D' || to_char(l_excel_row), r_line.LotNumber);
l_excel_row := l_excel_row + 1;
end loop;
--
--
-- 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="' || r.filename || '"');
--
l_found := true;
end loop;
--
if l_found = false
then
raise_application_error
( 'sample004'
, 'There is no pick order with number '
|| to_char(l_delivery_number)
|| ' found in division '
|| to_char(g_division_code)
|| '.'
, natural_key => to_char(l_delivery_number)
);
end if;
end if;
end;