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 ; ^