Purpose of this article is to have the import scripts available when manual owner invoices should be imported in bulk in an implementation context.

The following template can be used for preparing data:

Scripts to use

Import

DECLARE
  m_resort_id      NUMBER ;
  m_owner_id       NUMBER ;
  m_DEBIT_CARD_ID  NUMBER ;
  m_invoice_id     NUMBER ;
  ledger2          NUMBER ;
  ledger1          NUMBER;
  m_cost_center_id NUMBER ;
  m_count NUMBER ;
  n number :=0;
BEGIN
  FOR x IN
  ( SELECT * FROM ext_owneR_invoice1003 where campingid !=3000134 --and INVOICE_LINE_DESCRIPTION != 'Pitches'
  )
  LOOP
  begin
    SELECT resort_id INTO m_resort_id FROM resort WHERE code = to_char(x.CAMPINGID);
    if x.ledger1='0'  or x.ledger1 is null  then
      ledger1 := null;
      else 
      SELECT ledger_id
      INTO ledger1
      FROM ledger
      WHERE loweR(trim(name)) = loweR(trim(x.Ledger1));
      end if ;
    if x.Ledger2 ='0' or x.Ledger2 is null 
    then 
    ledger2:= null ;
    else 
      SELECT ledger_id
    INTO ledger2
    FROM ledger
    WHERE loweR(trim(LEDGER_NUMBER)) = loweR(trim(x.Ledger2));
    end if ;
    if x.cost_center is null or x.cost_center ='0' then 
 
    m_cost_center_id := null;
    else 
    SELECT cost_center_id
    INTO m_cost_center_id
    FROM cost_center
    WHERE loweR(trim(COST_CENTER_NUMBER)) = loweR(trim(x.cost_center));
    end if ;
     SELECT count(*) into m_count
    FROM owner o
    JOIN ownership oi
    ON oi.owner_id = o.owner_id
    JOIN objecti18n ob
    ON ob.object_id = oi.object_id
    JOIN resourcebase rb
    ON rb.resource_id = ob.asset_id
    WHERE o.lastname LIKE '%(r)%'
    AND o.archiveD_from IS NULL
    AND (oi.end_date    >= sysdate
    OR oi.end_date      IS NULL)
    AND rb.resorT_id     = m_resorT_id ;
   if m_count != 0 then 
    SELECT DISTINCT o.owneR_id
    INTO m_owner_id
    FROM owner o
    JOIN ownership oi
    ON oi.owner_id = o.owner_id
    JOIN objecti18n ob
    ON ob.object_id = oi.object_id
    JOIN resourcebase rb
    ON rb.resource_id = ob.asset_id
    WHERE o.lastname LIKE '%(r)%'
    AND o.archiveD_from IS NULL
    AND (oi.end_date    >= sysdate
    OR oi.end_date      IS NULL)
    AND rb.resorT_id     = m_resorT_id ;
    SELECT DEBIT_CARD_ID
    INTO m_DEBIT_CARD_ID
    FROM owner_debit_card
    WHERE owner_id =m_owner_id
    AND resorT_id  = m_resorT_id ;
    else 
    m_owner_id := null;
    m_DEBIT_CARD_ID := null;
    end if ;
    DBMS_OUTPUT.PUT_LINE('ledger1:'||ledger1);
    DBMS_OUTPUT.PUT_LINE('m_owner_id:'||m_owner_id);
       DBMS_OUTPUT.PUT_LINE('m_DEBIT_CARD_ID:'||m_DEBIT_CARD_ID);
    INSERT
    INTO OWNER_SETTLEMENT_INVOICE
      (
        resorT_id ,
        INVOICE_NUMBER,
        INVOICE_DATE,
        INVOICE_DESCRIPTION,
        INVOICE_AMOUNT,
        INVOICE_TYPE,
        OWNER_DEBIT_CARD_ID
      )
      VALUES
      (
        m_resort_id,
        replace(x.REFNR,'-',''),
        to_date(x.INVOICE_DATE,'mm/dd/yyyy'),
        x.DESCRIPTION,
        (x.AMOUNT),
        'm',
        m_DEBIT_CARD_ID
      )
    RETURNING OWNER_SETTLEMENT_INVOICE_ID
    INTO m_invoice_id ;
    INSERT
    INTO OWNER_SETTLEMENT_ENTRY
      (
        invoice_id,
        OWNER_ID,
        ENTRY_DATE,
        RESORT_ID,
        OWNER_AMOUNT,
        DESCRIPTION,
        INVOICENUMBER,
        TYPE ,
        BASE_LEDGER_ID,
        BASE_SECOND_LEDGER_ID,
        BASE_COST_CENTER_ID
      )
      VALUES
      (
        m_invoice_id ,
        m_OWNER_ID,
        sysdate ,
        m_RESORT_ID,
        x.amount,
        x.Invoice_line_description,
        x.INVOICE_NUMBER,
        x.Invoice_type,
        ledger1 ,
        ledger2,
        m_cost_center_id
      );
      n:=n+1;
         logger.error('invoice_inserted100321 :===- '||m_invoice_id);
      exception when others then 
         logger.error('invoice_not_inserted100321 :===- '||x.DESCRIPTION || ','||x.INVOICE_LINE_DESCRIPTION);
      end ;
  END LOOP;
END ;
/
select * from ext_owneR_invoice1003 
minus 
select distinct  e.* from OWNER_SETTLEMENT_INVOICE o
join resort r 
on r.resorT_id = o.resorT_id 
join ext_owneR_invoice1003 e 
on E.CAMPINGID = r.code ;
select * from log where message like 'invoice_inserted100321 :===-%';
select message from log where message like 'invoice_not_inserted100321 :===-%';

Table

create table ext_owneR_invoice1003(
RefNr varchar2(500),	
Partner_name  varchar2(500),	
CampingID	 number ,
Cost_unit	  varchar2(500),
Camping_name	 varchar2(500),
Payer	 varchar2(500),
Invoice_number	 varchar2(500),
Invoice_date	 varchar2(500),
Invoice_type	 varchar2(500),
Description	 varchar2(500),
Amount	 varchar2(500),
Currency	 varchar2(500),
Invoice_line_description	 varchar2(500),
Ledger1	 varchar2(500),
cost_center	 varchar2(500),
Ledger2		 varchar2(500)
);
drop table ext_owneR_invoice1003;
select * from ext_owneR_invoice1003;

Browser not supported