---

declare
  DOCUMENT_NAME     constant varchar2(128) := 'MDG_UNIFIED_LOCATIONS';
  STORAGE_NAME      constant varchar2(128) := 'unifiedLocations';
  APPLICATION_NAME  constant varchar2(128) := 'MDG';
  l_batch_id  etl.batchId;  
  procedure createIndex(p_columns varchar2) is
  begin
    l_batch_id := etl.startBatch('Create index on ' || DOCUMENT_NAME, etl.BATCH_DDL);
    etl.createIndex(
      p_batch_id      => l_batch_id,
      p_document_name => DOCUMENT_NAME,
      p_columns_list  => p_columns,
      p_bitmap        => false,
      p_unique        => false,
      p_using_at      => APPLICATION_NAME
    );
    etl.finalizeBatch(l_batch_id);
  end;
  procedure virtualColumn(p_column_name in varchar2, p_column_code in varchar2) is
  begin
    l_batch_id := etl.startBatch('Create virtual column on ' || DOCUMENT_NAME, etl.BATCH_DDL);
    etl.registerVirtualColumn(
      p_batch_id              => l_batch_id,
      p_document_name         => DOCUMENT_NAME,
      p_column_name           => p_column_name,
      p_column_type           => etl.VCOLUMN_USER_CODE,
      p_column_code           => p_column_code
    );
    etl.finalizeBatch(l_batch_id);
    etl.waitBatch(l_batch_id);
  end;
  procedure calculationColumn(
                              p_column_name           in varchar2,
                              p_column_code           in varchar2
                             ) is
  begin
    l_batch_id := etl.startBatch('Create calculation column on ' || DOCUMENT_NAME, etl.BATCH_DDL);
    etl.registerCalculationColumn(
      p_batch_id              => l_batch_id,
      p_document_name         => DOCUMENT_NAME,
      p_column_name           => p_column_name,
      p_column_code           => p_column_code
    );
    etl.finalizeBatch(l_batch_id);
    etl.waitBatch(l_batch_id);
  end;
begin  
  -- Register
  etl.registerWarehouse(
    p_document_name         => DOCUMENT_NAME,
    p_storage_name          => STORAGE_NAME,
    p_location              => etl.LOCAL_STORAGE,
    p_short_description     => APPLICATION_NAME,
    p_application           => APPLICATION_NAME
  );
  -- Create structure
  l_batch_id := etl.startBatch('Define ' || DOCUMENT_NAME || ' structure', etl.BATCH_DDL);
  etl.syncStructure(
    p_batch_id      => l_batch_id,
    p_document_name => DOCUMENT_NAME,
    p_source_object => '    
select 
      cast(null as varchar2(32   byte)) "source",
      cast(null as varchar2(45   byte)) "code",
      cast(null as varchar2(5    byte)) "country",
      cast(null as varchar2(1020 byte)) "name",
      cast(null as varchar2(250  byte)) "nameWoDiacritics",
      cast(null as varchar2(64   byte)) "PAPsRegion",
      cast(null as varchar2(16   byte)) "coordinates",
      cast(null as varchar2(1    byte)) "isRail",
      cast(null as varchar2(1    byte)) "isPort", 
      cast(null as varchar2(1    byte)) "isRoad",
      cast(null as varchar2(1    byte)) "isAirport", 
      cast(null as varchar2(1    byte)) "isPostal",
      cast(null as varchar2(1    byte)) "isBorder",      
      cast(null as varchar2(1    byte)) "isCanal",
      cast(null as varchar2(1    byte)) "isBunker",
      cast(null as varchar2(1    byte)) "isHub",
      cast(null as varchar2(1    byte)) "isLoadPort",
      cast(null as number)              "imosId",
      cast(null as varchar2(8    byte)) "imosCode",
      cast(null as varchar2(128  byte)) "imosName",
      cast(null as varchar2(128  byte)) "latitude",
      cast(null as varchar2(128  byte)) "longitude",
      cast(null as varchar2(40   char)) "chineseArea"
from dual'
  );
  etl.finalizeBatch(l_batch_id);
  etl.waitBatch(l_batch_id);
  -- calculation columns
  calculationColumn('description',
    q'[
    trim(
      flowUtils.transliterate("name") || ' (' || "code" || ')' || ' ' || 
      trim(
        trim(both ',' from 
          case when "isPort"      = 'Y' then ', Port'          end ||
          case when "isRail"      = 'Y' then ', Rail'          end ||
          case when "isRoad"      = 'Y' then ', Road'          end ||
          case when "isAirport"   = 'Y' then ', Airport'       end ||
          case when "isPostal"    = 'Y' then ', Postal Office' end ||
          case when "isBorder"    = 'Y' then ', Border'        end
        ) 
      )
    )
    ]'
  );
  -- Indexes
  createIndex('code');
  createIndex('name');
end;
/

grant select on "unifiedLocations" to xxmdg with grant option;


declare
    DOCUMENT_NAME    constant varchar2(128) := xxwh_sap_domestic_invoice_pkg.DOCUMENT_NAME;
    STORAGE_NAME     constant varchar2(128) := xxwh_sap_domestic_invoice_pkg.STORAGE_NAME;
    APPLICATION_NAME constant varchar2(128) := xxwh_sap_domestic_invoice_pkg.APPLICATION_NAME;
    l_batch_id                etl.batchId;
begin
    etl.registerWarehouse
    (
        p_document_name     => DOCUMENT_NAME,
        p_storage_name      => STORAGE_NAME,
        p_location          => etl.LOCAL_STORAGE,
        p_short_description => APPLICATION_NAME,
        p_application       => APPLICATION_NAME
    );

    l_batch_id := etl.startBatch('Sync ' || DOCUMENT_NAME || ' structure');

    etl.syncStructure
    (
        p_batch_id      => l_batch_id,
        p_document_name => DOCUMENT_NAME,
        p_source_object =>
        '
            select
                cast(null as varchar2(10   char)) as "systemMandant",
                cast(null as varchar2(10   char)) as "billingDocument",
                cast(null as number             ) as "billingDocumentItem",
                cast(null as varchar2(10   char)) as "saledDocument",
                cast(null as number             ) as "salesDocumentItem",
                cast(null as varchar2(40   char)) as "materialNumber",
                cast(null as number             ) as "actualBilledQuantity",
                cast(null as varchar2(4    char)) as "plant",
                cast(null as varchar2(40   char)) as "referenceDocumentNumber",
                cast(null as number             ) as "referenceDocumentItem",
                cast(null as number             ) as "value",
                cast(null as number             ) as "amount",
                cast(null as varchar2(2    char)) as "division",
                cast(null as varchar2(18   char)) as "assignmentNumber",
                cast(null as varchar2(4    char)) as "salesOrganization",
                cast(null as varchar2(10   char)) as "soldToParty",
                cast(null as varchar2(4    char)) as "companyCode",
                cast(null as varchar2(10   char)) as "accountingDocumentNumber",
                cast(null as number             ) as "fiscalYear",
                cast(null as date               ) as "billingDate",
                cast(null as varchar2(6    char)) as "currency",
                cast(null as varchar2(10   char)) as "conditionDocumentNumber",
                cast(null as varchar2(3    char)) as "Incoterms1",
                cast(null as varchar2(4    char)) as "billingType",
                cast(null as varchar2(1    char)) as "billingCategory",
                cast(null as varchar2(4    char)) as "sdDocumentCategory",
                cast(null as number             ) as "changedOn",
                cast(null as varchar2(1    char)) as "isCanceled",
                cast(null as varchar2(10   char)) as "correctionInvoiceId",
                cast(null as varchar2(10   char)) as "correctionInvoiceReference",
                cast(null as varchar2(1    char)) as "isThirdParty",
                cast(null as varchar2(10   char)) as "canceledBillingDocumentId",
                cast(null as date               ) as "creationDate",
                cast(null as number             ) as "valueTotal",
				cast(null as varchar2(4    char)) as "billingStatus",
				cast(null as varchar2(4    char)) as "accountingTransferStatus",
				cast(null as date               ) as "lastChangeDate",
				cast(null as varchar2(48   char)) as "createdBy",
				cast(null as varchar2(16   char)) as "itemCategory",
				cast(null as varchar2(80   char)) as "itemCategoryName",
				cast(null as varchar2(160  char)) as "billingTypeName",
				cast(null as varchar2(48   char)) as "lastChangedBy"
            from
                dual
        '
    );

    etl.finalizeBatch(l_batch_id);
    etl.waitBatch(l_batch_id);
    etlExternal.register
    (
        document_name => DOCUMENT_NAME,
        area          => 'PIPO',
        merge_list    => 'billingDocument,billingDocumentItem',
        post_process  => xxwh_sap_domestic_invoice_pkg.fn('postProcessPO')
    );
end;
/

grant select, insert on SAP_DOMESTIC_INVOICE_PIPO_PIPO_EC to XXSAPPO;