[...] 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 ;