SET TERM ^ ; create or alter procedure SP_SPR_SESPA_PERIOD_REPORT ( FROM_DATE timestamp, TO_DATE timestamp, DEL_TYPE_ID integer, TRADE_OBJECT_TYPE integer) returns ( ARTICLE_ID integer, P_REPORT_DATE timestamp, P_DRUG_CODE varchar(20), P_QUANTITY integer, P_TRANSACTION_TYPE integer, P_AVAILABLE_QTY integer, P_BATCH_NO varchar(20), P_DRUG_PRODUCT_CODE varchar(50), P_COUNTRY varchar(20), P_RECIPIENT_CODE varchar(100), SUPPLIER_ID integer, CUSTOMER_ID integer, P_EXPORT_PLANNED_DATE timestamp, P_ACTOR_TRN_NO varchar(20), PARCEL_ID integer, DELIVERY_ITEM_ID integer, LOCAL_TABLE_INDEX integer, LOCAL_DOCUMENT_ITEM_ID integer, SESPA_TRANSACTION_ID varchar(20), STATUS integer, ERROR_STATUS varchar(50), DOC_TYPE_INFO_ID integer, DOC_TYPE_NAME varchar(100), P_POSTCODE varchar(10), P_RCPT_POSTCODE varchar(10), P_NOTES varchar(200), ATOMIC_FACTOR integer, REPORT_DATE_AS_DATE date) as BEGIN FOR SELECT SP.ARTICLE_ID, SP.P_REPORT_DATE, SP.P_DRUG_CODE, SP.P_QUANTITY, SP.P_TRANSACTION_TYPE, SP.P_AVAILABLE_QTY, SP.P_BATCH_NO, SP.P_DRUG_PRODUCT_CODE, SP.P_COUNTRY, SP.P_RECIPIENT_CODE, SP.SUPPLIER_ID, SP.CUSTOMER_ID, SP.P_EXPORT_PLANNED_DATE, SP.P_ACTOR_TRN_NO, SP.PARCEL_ID, SP.DELIVERY_ITEM_ID, SP.LOCAL_TABLE_INDEX, SP.LOCAL_DOCUMENT_ITEM_ID, ST.SESPA_TRANSACTION_ID, ST.STATUS, ST.ERROR_STATUS, SP.DOC_TYPE_INFO_ID, SP.DOC_TYPE_NAME, SP.P_POSTCODE, SP.P_RCPT_POSTCODE, SP.P_NOTES, SP.ATOMIC_FACTOR, CAST(SP.P_REPORT_DATE AS DATE) FROM SP_SPR_SESPA_REPORT1(:FROM_DATE, :TO_DATE + 1, :DEL_TYPE_ID, :TRADE_OBJECT_TYPE) SP LEFT OUTER JOIN SESPA_TRANSACTION ST ON (ST.LOCAL_TABLE_INDEX = SP.LOCAL_TABLE_INDEX) AND (ST.LOCAL_DOCUMENT_ITEM_ID = SP.LOCAL_DOCUMENT_ITEM_ID) -- ORDER BY SP.P_REPORT_DATE INTO :ARTICLE_ID, :P_REPORT_DATE, :P_DRUG_CODE, :P_QUANTITY, :P_TRANSACTION_TYPE, :P_AVAILABLE_QTY, :P_BATCH_NO, :P_DRUG_PRODUCT_CODE, :P_COUNTRY, :P_RECIPIENT_CODE, :SUPPLIER_ID, :CUSTOMER_ID, :P_EXPORT_PLANNED_DATE, :P_ACTOR_TRN_NO, :PARCEL_ID, :DELIVERY_ITEM_ID, :LOCAL_TABLE_INDEX, :LOCAL_DOCUMENT_ITEM_ID, :SESPA_TRANSACTION_ID, :STATUS, :ERROR_STATUS, :DOC_TYPE_INFO_ID, :DOC_TYPE_NAME, :P_POSTCODE, :P_RCPT_POSTCODE, :P_NOTES, :ATOMIC_FACTOR, :REPORT_DATE_AS_DATE DO SUSPEND; END^ SET TERM ; ^