[...]
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 >
PROCESSABLE_AMOUNT_OFFERS)) then begin
PROCESS_STATE_OFFERS = 4;
end
// Processable amount for orders
if (PROCESSED_AMOUNT_ORDERS > TempReferenceAmount) then begin
PROCESSABLE_AMOUNT_ORDERS = 0;
end
else begin
PROCESSABLE_AMOUNT_ORDERS = TempReferenceAmount -
PROCESSED_AMOUNT_ORDERS;
end
// Process state for orders
if (PROCESSABLE_AMOUNT_ORDERS = 0) then begin
PROCESS_STATE_ORDERS = 2;
end
if ((PROCESSABLE_AMOUNT_ORDERS > 0) and (TempReferenceAmount >
PROCESSABLE_AMOUNT_ORDERS)) then begin
PROCESS_STATE_ORDERS = 4;
end
// Processable amount for deliveries
if (PROCESSED_AMOUNT_DELIVERIES > TempReferenceAmount) then begin
PROCESSABLE_AMOUNT_DELIVERIES = 0;
end
else begin
PROCESSABLE_AMOUNT_DELIVERIES = TempReferenceAmount -
PROCESSED_AMOUNT_DELIVERIES;
end
// Process state for deliveries
if (PROCESSABLE_AMOUNT_DELIVERIES = 0) then begin
PROCESS_STATE_DELIVERIES = 2;
end
if ((PROCESSABLE_AMOUNT_DELIVERIES > 0) and (TempReferenceAmount >
PROCESSABLE_AMOUNT_DELIVERIES)) then begin
PROCESS_STATE_DELIVERIES = 4;
end
// Processable amount for invoices
if (PROCESSED_AMOUNT_INVOICES > TempReferenceAmount) then begin
PROCESSABLE_AMOUNT_INVOICES = 0;
end
else begin
PROCESSABLE_AMOUNT_INVOICES = TempReferenceAmount -
PROCESSED_AMOUNT_INVOICES;
end
// Process state for invoices
if (PROCESSABLE_AMOUNT_INVOICES = 0) then begin
PROCESS_STATE_INVOICES = 2;
end
if ((PROCESSABLE_AMOUNT_INVOICES > 0) and (TempReferenceAmount >
PROCESSABLE_AMOUNT_INVOICES)) then begin
PROCESS_STATE_INVOICES = 4;
end
suspend;
end
end ^^
SET TERM ; ^^
SET TERM ^^ ;
CREATE PROCEDURE PROCESS_SUMMARY_INVOICES returns (
PROCESS_ID Integer,
POSITION_COUNT SmallInt,
TOTAL_PROCESSED_AMOUNT_OFFERS Double Precision,
TOTAL_PROCESSED_AMOUNT_ORDERS Double Precision,
TOTAL_PROCESSED_AMOUNT_DELIVER Double Precision,
TOTAL_PROCESSED_AMOUNT_INVOICES Double Precision,
SUM_PROCESS_STATE_OFFERS SmallInt,
SUM_PROCESS_STATE_ORDERS SmallInt,
SUM_PROCESS_STATE_DELIVERIES SmallInt,
SUM_PROCESS_STATE_INVOICES SmallInt,
DCOUNT_PROCESS_STATE_OFFERS SmallInt,
DCOUNT_PROCESS_STATE_ORDERS SmallInt,
DCOUNT_PROCESS_STATE_DELIVERIES SmallInt,
DCOUNT_PROCESS_STATE_INVOICES SmallInt)
AS
begin
for
select
PROCESS_ID,
count(POSITION_ID) as POSITION_COUNT,
sum(PROCESSED_AMOUNT_OFFERS) as TOTAL_PROCESSED_AMOUNT_OFFERS,
sum(PROCESSED_AMOUNT_ORDERS) as TOTAL_PROCESSED_AMOUNT_ORDERS,
sum(PROCESSED_AMOUNT_DELIVERIES) as TOTAL_PROCESSED_AMOUNT_DELIVER,
sum(PROCESSED_AMOUNT_INVOICES) as TOTAL_PROCESSED_AMOUNT_INVOICES,
sum(PROCESS_STATE_OFFERS) as SUM_PROCESS_STATE_OFFERS,
sum(PROCESS_STATE_ORDERS) as SUM_PROCESS_STATE_ORDERS,
sum(PROCESS_STATE_DELIVERIES) as SUM_PROCESS_STATE_DELIVERIES,
sum(PROCESS_STATE_INVOICES) as SUM_PROCESS_STATE_INVOICES,
count(distinct PROCESS_STATE_OFFERS) as DCOUNT_PROCESS_STATE_OFFERS,
count(distinct PROCESS_STATE_ORDERS) as DCOUNT_PROCESS_STATE_ORDERS,
count(distinct PROCESS_STATE_DELIVERIES) as
DCOUNT_PROCESS_STATE_DELIVERIES,
count(distinct PROCESS_STATE_INVOICES) as DCOUNT_PROCESS_STATE_INVOICES
from
PROCESSABLE_INVOICE_POSITIONS
group by
PROCESS_ID
order by
PROCESS_ID
into :PROCESS_ID, :POSITION_COUNT, :TOTAL_PROCESSED_AMOUNT_OFFERS,
:TOTAL_PROCESSED_AMOUNT_ORDERS, :TOTAL_PROCESSED_AMOUNT_DELIVER,
:TOTAL_PROCESSED_AMOUNT_INVOICES, :SUM_PROCESS_STATE_OFFERS,
:SUM_PROCESS_STATE_ORDERS, :SUM_PROCESS_STATE_DELIVERIES,
:SUM_PROCESS_STATE_INVOICES, :DCOUNT_PROCESS_STATE_OFFERS,
:DCOUNT_PROCESS_STATE_ORDERS, :DCOUNT_PROCESS_STATE_DELIVERIES,
:DCOUNT_PROCESS_STATE_INVOICES
do begin
suspend;
end
end ^^
SET TERM ; ^^
SET TERM ^^ ;
CREATE PROCEDURE PROCESS_DETAILS_INVOICES returns (
PROCESS_TYPE Integer,
PROCESS_ID Integer,
PROCESS_DATE Timestamp,
PROCESS_NO VarChar(100),
PROCESS_CONTACTID Integer,
PROCESS_CONTACT_NAME1 VarChar(150),
PROCESS_CONTACT_NAME2 VarChar(150),
PROCESS_LABEL VarChar(512),
CONTACT_CONTACTNO VarChar(100),
CONTACT_CATEGORY SmallInt,
CONTACT_LABEL VarChar(512),
POSITION_COUNT SmallInt,
SUM_PROCESS_STATE_OFFERS SmallInt,
SUM_PROCESS_STATE_ORDERS SmallInt,
SUM_PROCESS_STATE_DELIVERIES SmallInt,
SUM_PROCESS_STATE_INVOICES SmallInt,
DCOUNT_PROCESS_STATE_OFFERS SmallInt,
DCOUNT_PROCESS_STATE_ORDERS SmallInt,
DCOUNT_PROCESS_STATE_DELIVERIES SmallInt,
DCOUNT_PROCESS_STATE_INVOICES SmallInt,
PROCESSED_AMOUNT_OFFERS Double Precision,
PROCESSED_AMOUNT_ORDERS Double Precision,
PROCESSED_AMOUNT_DELIVERIES Double Precision,
PROCESSED_AMOUNT_INVOICES Double Precision,
PROCESS_STATE_OFFERS SmallInt,
PROCESS_STATE_ORDERS SmallInt,
PROCESS_STATE_DELIVERIES SmallInt,
PROCESS_STATE_INVOICES SmallInt)
AS
begin
for
select
cast (3 as integer) as PROCESS_TYPE,
INVOICES.ID as PROCESS_ID,
INVOICES.INVOICE_DATE as PROCESS_DATE,
INVOICES.INVOICENO as PROCESS_NO,
INVOICES.CONTACTID as PROCESS_CONTACTID,
INVOICES.NAME1 as PROCESS_CONTACT_NAME1,
INVOICES.NAME2 as PROCESS_CONTACT_NAME2,
cast('PROCESS_3_LABEL_WITH_NO ' || INVOICES.INVOICENO ||
coalesce(' DATE_FROM ' || extract(day from
INVOICES.INVOICE_DATE)||'.'||extract(month from
INVOICES.INVOICE_DATE)||'.'||extract(year from INVOICES.INVOICE_DATE), '') ||
coalesce(' FOR_CONTACT ' || nullif(INVOICES.NAME1, ''), '') || coalesce('; ' ||
nullif(INVOICES.NAME2, ''), '') as varchar(512)) as PROCESS_LABEL,
CONTACTS.CONTACTNO as CONTACT_CONTACTNO,
CONTACTS.CATEGORY as CONTACT_CATEGORY,
case when (INVOICES.CONTACTID > 0) then
cast('CONTACT_LABEL_WITH_NO ' || CONTACTS.CONTACTNO || coalesce(' (' ||
nullif(CONTACTS.NAME1, ''), '') || coalesce('; ' || nullif(CONTACTS.NAME2, ''),
'') || coalesce('; ' || nullif(CONTACTS.NAME3, ''), '') || ')' as varchar(512))
else cast('CONTACT_LABEL ' || coalesce(' (' || nullif(INVOICES.NAME1, ''), '')
|| coalesce('; ' || nullif(INVOICES.NAME2, ''), '') || coalesce('; ' ||
nullif(INVOICES.NAME3, ''), '') || ')' as varchar(512)) end as CONTACT_LABEL,
PROCESS_SUMMARY_INVOICES.POSITION_COUNT,
PROCESS_SUMMARY_INVOICES.SUM_PROCESS_STATE_OFFERS,
PROCESS_SUMMARY_INVOICES.SUM_PROCESS_STATE_ORDERS,
PROCESS_SUMMARY_INVOICES.SUM_PROCESS_STATE_DELIVERIES,
PROCESS_SUMMARY_INVOICES.SUM_PROCESS_STATE_INVOICES,
PROCESS_SUMMARY_INVOICES.DCOUNT_PROCESS_STATE_OFFERS,
PROCESS_SUMMARY_INVOICES.DCOUNT_PROCESS_STATE_ORDERS,
PROCESS_SUMMARY_INVOICES.DCOUNT_PROCESS_STATE_DELIVERIES,
PROCESS_SUMMARY_INVOICES.DCOUNT_PROCESS_STATE_INVOICES,
PROCESS_SUMMARY_INVOICES.TOTAL_PROCESSED_AMOUNT_OFFERS as
PROCESSED_AMOUNT_OFFERS,
PROCESS_SUMMARY_INVOICES.TOTAL_PROCESSED_AMOUNT_ORDERS as
PROCESSED_AMOUNT_ORDERS,
PROCESS_SUMMARY_INVOICES.TOTAL_PROCESSED_AMOUNT_DELIVER as
PROCESSED_AMOUNT_DELIVERIES,
PROCESS_SUMMARY_INVOICES.TOTAL_PROCESSED_AMOUNT_INVOICES as
PROCESSED_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
left join CONTACTS on INVOICES.CONTACTID = CONTACTS.ID
inner join PROCESS_SUMMARY_INVOICES on INVOICES.ID =
PROCESS_SUMMARY_INVOICES.PROCESS_ID
where
(INVOICES.ID > 0)
order by
INVOICES.ID
into :PROCESS_TYPE, :PROCESS_ID, :PROCESS_DATE, :PROCESS_NO,
:PROCESS_CONTACTID, :PROCESS_CONTACT_NAME1, :PROCESS_CONTACT_NAME2,
:PROCESS_LABEL, :CONTACT_CONTACTNO, :CONTACT_CATEGORY, :CONTACT_LABEL,
:POSITION_COUNT,
:SUM_PROCESS_STATE_OFFERS, :SUM_PROCESS_STATE_ORDERS,
:SUM_PROCESS_STATE_DELIVERIES, :SUM_PROCESS_STATE_INVOICES,
:DCOUNT_PROCESS_STATE_OFFERS, :DCOUNT_PROCESS_STATE_ORDERS,
:DCOUNT_PROCESS_STATE_DELIVERIES, :DCOUNT_PROCESS_STATE_INVOICES,
:PROCESSED_AMOUNT_OFFERS, :PROCESSED_AMOUNT_ORDERS,
:PROCESSED_AMOUNT_DELIVERIES, :PROCESSED_AMOUNT_INVOICES,
:PROCESS_STATE_OFFERS, :PROCESS_STATE_ORDERS,
:PROCESS_STATE_DELIVERIES, :PROCESS_STATE_INVOICES
do begin
if (:DCOUNT_PROCESS_STATE_OFFERS = 1) then begin
PROCESS_STATE_OFFERS = trunc(:SUM_PROCESS_STATE_OFFERS /
:POSITION_COUNT);
end
else begin
PROCESS_STATE_OFFERS = 4;
end
if (:DCOUNT_PROCESS_STATE_ORDERS = 1) then begin
PROCESS_STATE_ORDERS = trunc(:SUM_PROCESS_STATE_ORDERS /
:POSITION_COUNT);
end
else begin
PROCESS_STATE_ORDERS = 4;
end
if (:DCOUNT_PROCESS_STATE_DELIVERIES = 1) then begin
PROCESS_STATE_DELIVERIES = trunc(:SUM_PROCESS_STATE_DELIVERIES /
:POSITION_COUNT);
end
else begin
PROCESS_STATE_DELIVERIES = 4;
end
if (:DCOUNT_PROCESS_STATE_INVOICES = 1) then begin
PROCESS_STATE_INVOICES = trunc(:SUM_PROCESS_STATE_INVOICES /
:POSITION_COUNT);
end
else begin
PROCESS_STATE_INVOICES = 4;
end
suspend;
end
end ^^
SET TERM ; ^^
/*
**********************************************************************************************************************
* Second requirement (see description)
**********************************************************************************************************************
*/
create or alter view V_PROCESSABLE_POSITIONS
as
select
OFFERS.OFFER_DATE as PROCESS_DATE,
OFFERS.CONTACTID as PROCESS_CONTACTID,
OFFERS.NAME1 as PROCESS_CONTACT_NAME1,
OFFERS.NAME2 as PROCESS_CONTACT_NAME2,
cast('PROCESS_0_LABEL_WITH_NO ' || OFFERS.OFFERNO || coalesce(' DATE_FROM '
|| extract(day from OFFERS.OFFER_DATE)||'.'||extract(month from
OFFERS.OFFER_DATE)||'.'||extract(year from OFFERS.OFFER_DATE), '') ||
coalesce(' FOR_CONTACT ' || nullif(OFFERS.NAME1, ''), '') || coalesce('; ' ||
nullif(OFFERS.NAME2, ''), '') as varchar(512)) as PROCESS_LABEL,
CONTACTS.CONTACTNO as CONTACT_CONTACTNO,
CONTACTS.CONTACT_CATEGORY,
case when (OFFERS.CONTACTID > 0) then cast('CONTACT_LABEL_WITH_NO ' ||
CONTACTS.CONTACTNO || coalesce(' (' || nullif(CONTACTS.NAME1, ''), '') ||
coalesce('; ' || nullif(CONTACTS.NAME2, ''), '') || coalesce('; ' ||
nullif(CONTACTS.NAME3, ''), '') || ')' as varchar(512)) else
cast('CONTACT_LABEL ' || coalesce(' (' || nullif(OFFERS.NAME1, ''), '') ||
coalesce('; ' || nullif(OFFERS.NAME2, ''), '') || coalesce('; ' ||
nullif(OFFERS.NAME3, ''), '') || ')' as varchar(512)) end as CONTACT_LABEL,
PROCESSABLE_OFFERS_POSITIONS.*
from
OFFERS
left join CONTACTS on OFFERS.CONTACTID = CONTACTS.ID
inner join PROCESSABLE_OFFERS_POSITIONS on OFFERS.ID =
PROCESSABLE_OFFERS_POSITIONS.PROCESS_ID
union all
select
ORDERS.ORDER_DATE as PROCESS_DATE,
ORDERS.CONTACTID as PROCESS_CONTACTID,
ORDERS.NAME1 as PROCESS_CONTACT_NAME1,
ORDERS.NAME2 as PROCESS_CONTACT_NAME2,
cast('PROCESS_1_LABEL_WITH_NO ' || ORDERS.ORDERNO || coalesce(' DATE_FROM '
|| extract(day from ORDERS.ORDER_DATE)||'.'||extract(month from
ORDERS.ORDER_DATE)||'.'||extract(year from ORDERS.ORDER_DATE), '') ||
coalesce(' FOR_CONTACT ' || nullif(ORDERS.NAME1, ''), '') || coalesce('; ' ||
nullif(ORDERS.NAME2, ''), '') as varchar(512)) as PROCESS_LABEL,
CONTACTS.CONTACTNO as CONTACT_CONTACTNO,
CONTACTS.CONTACT_CATEGORY,
case when (ORDERS.CONTACTID > 0) then cast('CONTACT_LABEL_WITH_NO ' ||
CONTACTS.CONTACTNO || coalesce(' (' || nullif(CONTACTS.NAME1, ''), '') ||
coalesce('; ' || nullif(CONTACTS.NAME2, ''), '') || coalesce('; ' ||
nullif(CONTACTS.NAME3, ''), '') || ')' as varchar(512)) else
cast('CONTACT_LABEL ' || coalesce(' (' || nullif(ORDERS.NAME1, ''), '') ||
coalesce('; ' || nullif(ORDERS.NAME2, ''), '') || coalesce('; ' ||
nullif(ORDERS.NAME3, ''), '') || ')' as varchar(512)) end as CONTACT_LABEL,
PROCESSABLE_ORDERS_POSITIONS.*
from
ORDERS
left join CONTACTS on ORDERS.CONTACTID = CONTACTS.ID
inner join PROCESSABLE_ORDERS_POSITIONS on ORDERS.ID =
PROCESSABLE_ORDERS_POSITIONS.PROCESS_ID
union all
select
DELIVERIES.DELIVERY_DATE as PROCESS_DATE,
DELIVERIES.CONTACTID as PROCESS_CONTACTID,
DELIVERIES.NAME1 as PROCESS_CONTACT_NAME1,
DELIVERIES.NAME2 as PROCESS_CONTACT_NAME2,
cast('PROCESS_2_LABEL_WITH_NO ' || DELIVERIES.DELIVERYNO || coalesce('
DATE_FROM ' || extract(day from DELIVERIES.DELIVERY_DATE)||'.'||extract(month
from DELIVERIES.DELIVERY_DATE)||'.'||extract(year from
DELIVERIES.DELIVERY_DATE), '') || coalesce(' FOR_CONTACT ' ||
nullif(DELIVERIES.NAME1, ''), '') || coalesce('; ' || nullif(DELIVERIES.NAME2,
''), '') as varchar(512)) as PROCESS_LABEL,
CONTACTS.CONTACTNO as CONTACT_CONTACTNO,
CONTACTS.CONTACT_CATEGORY,
case when (DELIVERIES.CONTACTID > 0) then cast('CONTACT_LABEL_WITH_NO ' ||
CONTACTS.CONTACTNO || coalesce(' (' || nullif(CONTACTS.NAME1, ''), '') ||
coalesce('; ' || nullif(CONTACTS.NAME2, ''), '') || coalesce('; ' ||
nullif(CONTACTS.NAME3, ''), '') || ')' as varchar(512)) else
cast('CONTACT_LABEL ' || coalesce(' (' || nullif(DELIVERIES.NAME1, ''), '') ||
coalesce('; ' || nullif(DELIVERIES.NAME2, ''), '') || coalesce('; ' ||
nullif(DELIVERIES.NAME3, ''), '') || ')' as varchar(512)) end as CONTACT_LABEL,
PROCESSABLE_DELIVR_POSITIONS.*
from
DELIVERIES
left join CONTACTS on DELIVERIES.CONTACTID = CONTACTS.ID
inner join PROCESSABLE_DELIVR_POSITIONS on DELIVERIES.ID =
PROCESSABLE_DELIVR_POSITIONS.PROCESS_ID
union all
select
INVOICES.INVOICE_DATE as PROCESS_DATE,
INVOICES.CONTACTID as PROCESS_CONTACTID,
INVOICES.NAME1 as PROCESS_CONTACT_NAME1,
INVOICES.NAME2 as PROCESS_CONTACT_NAME2,
cast('PROCESS_3_LABEL_WITH_NO ' || INVOICES.INVOICENO || coalesce('
DATE_FROM ' || extract(day from INVOICES.INVOICE_DATE)||'.'||extract(month from
INVOICES.INVOICE_DATE)||'.'||extract(year from INVOICES.INVOICE_DATE), '') ||
coalesce(' FOR_CONTACT ' || nullif(INVOICES.NAME1, ''), '') || coalesce('; ' ||
nullif(INVOICES.NAME2, ''), '') as varchar(512)) as PROCESS_LABEL,
CONTACTS.CONTACTNO as CONTACT_CONTACTNO,
CONTACTS.CONTACT_CATEGORY,
case when (INVOICES.CONTACTID > 0) then cast('CONTACT_LABEL_WITH_NO ' ||
CONTACTS.CONTACTNO || coalesce(' (' || nullif(CONTACTS.NAME1, ''), '') ||
coalesce('; ' || nullif(CONTACTS.NAME2, ''), '') || coalesce('; ' ||
nullif(CONTACTS.NAME3, ''), '') || ')' as varchar(512)) else
cast('CONTACT_LABEL ' || coalesce(' (' || nullif(INVOICES.NAME1, ''), '') ||
coalesce('; ' || nullif(INVOICES.NAME2, ''), '') || coalesce('; ' ||
nullif(INVOICES.NAME3, ''), '') || ')' as varchar(512)) end as CONTACT_LABEL,
PROCESSABLE_INVOICE_POSITIONS.*
from
INVOICES
left join CONTACTS on INVOICES.CONTACTID = CONTACTS.ID
inner join PROCESSABLE_INVOICE_POSITIONS on INVOICES.ID =
PROCESSABLE_INVOICE_POSITIONS.PROCESS_ID
;
