Purpose of this article is to have the import scripts available when manual owner invoices should be imported in bulk in an implementation context.
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;