[...]
if (PROCESSED_AMOUNT_INVOICES is null) then begin
PROCESSED_AMOUNT_INVOICES = 0;
end
PROCESSED_AMOUNT_INVOICES = PROCESSED_AMOUNT_INVOICES +
TempTargetAmount;
end
else begin
PROCESSED_AMOUNT_INVOICES = TempTargetAmount;
end
suspend;
end
end ^^
SET TERM ; ^^
SET TERM ^^ ;
CREATE OR ALTER PROCEDURE PROCESSABLE_INVOICE_POSITIONS returns (
PROCESS_TYPE INTEGER,
PROCESS_ID INTEGER,
PROCESS_NO VARCHAR( 100),
POSITION_PROCESS_CHAINID INTEGER,
POSITION_ID INTEGER,
POSITION_AMOUNT DOUBLE PRECISION,
POSITION_PRODUCTID INTEGER,
POSITION_PRODUCTNO VARCHAR( 100),
POSITION_DESCRIPTION VARCHAR(50),
POSITION_PRODUCT_LABEL VARCHAR( 255),
POSITION_PRICE DOUBLE PRECISION,
POSITION_PRODUCT_CATEGORY SMALLINT,
PROCESSED_AMOUNT_OFFERS DOUBLE PRECISION,
PROCESSED_AMOUNT_ORDERS DOUBLE PRECISION,
PROCESSED_AMOUNT_DELIVERIES DOUBLE PRECISION,
PROCESSED_AMOUNT_INVOICES DOUBLE PRECISION,
PROCESSABLE_AMOUNT_OFFERS DOUBLE PRECISION,
PROCESSABLE_AMOUNT_ORDERS DOUBLE PRECISION,
PROCESSABLE_AMOUNT_DELIVERIES DOUBLE PRECISION,
PROCESSABLE_AMOUNT_INVOICES DOUBLE PRECISION,
PROCESS_STATE_OFFERS SMALLINT,
PROCESS_STATE_ORDERS SMALLINT,
PROCESS_STATE_DELIVERIES SMALLINT,
PROCESS_STATE_INVOICES SMALLINT)
AS
declare variable TempTargetAmount double precision;
declare variable TempReferenceAmount double precision;
begin
for
select
cast (3 as integer) as PROCESS_TYPE,
INVOICES_POSITIONS.INVOICEID as PROCESS_ID,
INVOICES_POSITIONS.INVOICENO as PROCESS_NO,
INVOICES_POSITIONS.PROCESS_CHAINID as POSITION_PROCESS_CHAINID,
INVOICES_POSITIONS.ID as POSITION_ID,
INVOICES_POSITIONS.AMOUNT as POSITION_AMOUNT,
INVOICES_POSITIONS.PRODUCTID as POSITION_PRODUCTID,
INVOICES_POSITIONS.PRODUCTNO as POSITION_PRODUCTNO,
INVOICES_POSITIONS.PRODUCT_DESCRIPTION as POSITION_DESCRIPTION,
case when (INVOICES_POSITIONS.PRODUCTID > 0) then
cast('POSITION_PRODUCT_LABEL_WITH_NO ' || INVOICES_POSITIONS.PRODUCTNO || ' ('
|| nullif(INVOICES_POSITIONS.PRODUCT_DESCRIPTION, '') || ')' as varchar(255))
else cast('POSITION_PRODUCT_LABEL ' || ' (' ||
nullif(INVOICES_POSITIONS.PRODUCT_DESCRIPTION, '') || ')' as varchar(255)) end
as POSITION_PRODUCT_LABEL,
INVOICES_POSITIONS.PRICE as POSITION_PRICE,
PRODUCTS.CATEGORY as POSITION_PRODUCT_CATEGORY,
PROCESS_INFO_INVOICE_POSITIONS.PROCESSED_AMOUNT_OFFERS,
PROCESS_INFO_INVOICE_POSITIONS.PROCESSED_AMOUNT_ORDERS,
PROCESS_INFO_INVOICE_POSITIONS.PROCESSED_AMOUNT_DELIVERIES,
PROCESS_INFO_INVOICE_POSITIONS.PROCESSED_AMOUNT_INVOICES,
cast (0 as double precision) as PROCESSABLE_AMOUNT_OFFERS,
cast (0 as double precision) as PROCESSABLE_AMOUNT_ORDERS,
cast (0 as double precision) as PROCESSABLE_AMOUNT_DELIVERIES,
cast (0 as double precision) as PROCESSABLE_AMOUNT_INVOICES,
cast (3 as smallint) as PROCESS_STATE_OFFERS,
cast (3 as smallint) as PROCESS_STATE_ORDERS,
cast (3 as smallint) as PROCESS_STATE_DELIVERIES,
cast (3 as smallint) as PROCESS_STATE_INVOICES
from
INVOICES_POSITIONS
left join PRODUCTS on INVOICES_POSITIONS.PRODUCTID = PRODUCTS.ID
left join PROCESS_INFO_INVOICE_POSITIONS on
INVOICES_POSITIONS.PROCESS_CHAINID =
PROCESS_INFO_INVOICE_POSITIONS.PROCESS_CHAINID
where
(INVOICES_POSITIONS.ID > 0)
into :PROCESS_TYPE, :PROCESS_ID, :PROCESS_NO, :POSITION_PROCESS_CHAINID,
:POSITION_ID, :POSITION_AMOUNT, :POSITION_PRODUCTID, :POSITION_PRODUCTNO,
:POSITION_DESCRIPTION, :POSITION_PRODUCT_LABEL, :POSITION_PRICE,
:POSITION_PRODUCT_CATEGORY,
:PROCESSED_AMOUNT_OFFERS, :PROCESSED_AMOUNT_ORDERS,
:PROCESSED_AMOUNT_DELIVERIES, :PROCESSED_AMOUNT_INVOICES,
:PROCESSABLE_AMOUNT_OFFERS, :PROCESSABLE_AMOUNT_ORDERS,
:PROCESSABLE_AMOUNT_DELIVERIES, :PROCESSABLE_AMOUNT_INVOICES,
:PROCESS_STATE_OFFERS, :PROCESS_STATE_ORDERS,
:PROCESS_STATE_DELIVERIES, :PROCESS_STATE_INVOICES
do begin
TempTargetAmount = 0;
TempReferenceAmount = :POSITION_AMOUNT;
if (TempReferenceAmount is null) then begin
TempReferenceAmount = 1;
end
// Processable amount for offers
if (PROCESSED_AMOUNT_OFFERS > TempReferenceAmount) then begin
PROCESSABLE_AMOUNT_OFFERS = 0;
end
else begin
PROCESSABLE_AMOUNT_OFFERS = TempReferenceAmount -
PROCESSED_AMOUNT_OFFERS;
end
// Process state for offers
if (PROCESSABLE_AMOUNT_OFFERS = 0) then begin
PROCESS_STATE_OFFERS = 2;
end
if ((PROCESSABLE_AMOUNT_OFFERS > 0) and (TempReferenceAmount >
