Leute,
Ich habe den Orakelcode beigefügt, der diese enthält
FÜR ALLE, BULK COLLECT, BULK COLLECT LIMIT, BULK AUSNAHMEN, GOTO . Ich stecke in der Migration zu Postgresql fest. Hinzugefügt – # 1, # 2 #3 … #8 Ich stecke dort fest. Ich brauche Hilfe
Versucht, aber in der Mitte stecken geblieben. Dachte, nur Orakelcode zu geben.
create or replace PROCEDURE Spr_abc_Customer_Statement(v_Ismonthly_Run IN CHAR ,v_Pole_Id IN NUMBER) AS
v_Pol_Main_Lang CHAR(1);
v_Input_Date VARCHAR2(10);
CURSOR Cur_Cc_No IS
SELECT Cc_No_705
,Cc_Stmt_Payor_Level_705
,Cc_Stmt_Multi_Lang_705
FROM Cc_Organization
WHERE Cc_State_Req_Sw_705 = 'Y'
AND Nvl(Cc_Pole_Id_705, 0) = v_Pole_Id;
---- #1
TYPE Cc_No_705_Tab IS TABLE OF Cc_Organization.Cc_No_705%TYPE;
t_Cc_No_705 Cc_No_705_Tab;
TYPE Cc_Stmt_Payor_Level_705_Tab IS TABLE OF Cc_Organization.Cc_Stmt_Payor_Level_705%TYPE;
t_Cc_Stmt_Payor_Level_705 Cc_Stmt_Payor_Level_705_Tab;
TYPE Cc_Stmt_Multi_Lang_705_Tab IS TABLE OF Cc_Organization.Cc_Stmt_Multi_Lang_705%TYPE;
t_Stmt_Multi_Lang_705 Cc_Stmt_Multi_Lang_705_Tab;
-- Main Cursor
CURSOR Cur_Invoice IS
select * from abc.test where id = 67767;
-- #2
TYPE t_Rec IS RECORD(
Cust_Parent_Level_Flag Dbms_Sql.Varchar2_Table
,Inv_Date Dbms_Sql.Number_Table);
Cur_Invoice_Record t_Rec;
CURSOR Cur_Letter_Sob IS
SELECT Nvl(Letter_Desc_789, ' ') Letter_Desc
FROM Letter_And_Statement_Detail
WHERE Letter_No_789 = 'STMNT'
ORDER BY Letter_Code_789
,Letter_Line_No_789;
Cur_Letter_Rec_Sob Cur_Letter_Sob%ROWTYPE;
-- ~~ -- ~~ -- ~~ -- ~~ -- ~~ -- ~~ -- ~~ -- ~~ -- ~~ -- ~~ -- ~~ -- ~~
CURSOR Cur_Letter_Sob_Acgrp IS
SELECT Nvl(Letter_Desc_789, ' ') Letter_Desc
,Nvl(Letter_Skip_Nl_789, 'N') Skip_Nl
FROM Letter_And_Statement_Detail
WHERE Letter_No_789 = 'STMNT';
Cur_Letter_Rec_Sob_Acgrp Cur_Letter_Sob_Acgrp%ROWTYPE;
CURSOR Cur_Letter_Sob_Acgrp_Curr IS
SELECT Nvl(Letter_Desc_789, ' ') Letter_Desc
,Nvl(Letter_Skip_Nl_789, 'N') Skip_Nl
FROM Letter_And_Statement_Detail
WHERE Letter_No_789 = 'STMNT'
AND Letter_Lang_789 = v_Cust_Lang
ORDER BY Letter_Code_789
,Letter_Line_No_789;
Cur_Letter_Rec_Sob_Acgrp_Curr Cur_Letter_Sob_Acgrp_Curr%ROWTYPE;
CURSOR Cur_Ageing IS
select abc,bde from test.abc where id =909090;
Cur_Ageing_Record Cur_Ageing%ROWTYPE;
-- ~~ -- ~~ -- ~~ -- ~~ -- ~~ -- ~~ -- ~~ -- ~~ -- ~~ -- ~~ -- ~~ -- ~~
v_Limit NUMBER := 15000;
BEGIN
SELECT Nvl(Pol_Main_Lang_799, ' ') Pol_Main_Lang
INTO v_Pol_Main_Lang
FROM System_Policy;
v_Temp_Cust_No := ' ';
Inv_Stat_Amt := 0;
-- #3 A
IF Cur_Cc_No%ISOPEN THEN
CLOSE Cur_Cc_No;
END IF;
OPEN Cur_Cc_No;
FETCH Cur_Cc_No BULK COLLECT
INTO t_Cc_No_705
,t_Cc_Stmt_Payor_Level_705
,t_Stmt_Multi_Lang_705;
CLOSE Cur_Cc_No;
-- #3 B
FOR j IN 1 .. t_Cc_No_705.Count
LOOP
v_Cc_No_705 := t_Cc_No_705(j);
v_Cc_Stmt_Payor_Level_705 := Nvl(t_Cc_Stmt_Payor_Level_705(j), ' ');
v_Cc_Stmt_Multi_Lang_705 := Nvl(t_Stmt_Multi_Lang_705(j), 'N');
<<loop_Cur_Invoice>>
BEGIN
IF Cur_Invoice%ISOPEN THEN
CLOSE Cur_Invoice;
END IF;
OPEN Cur_Invoice;
LOOP
EXIT WHEN Cur_Invoice%NOTFOUND;
--#4
FETCH Cur_Invoice BULK COLLECT
INTO Cur_Invoice_Record LIMIT v_Limit;
FOR i IN 1 .. Cur_Invoice_Record.Cust_Cc_No.Count
LOOP
v_Letter_Desc := NULL;
Licnt := 0;
v_Cc_Owner := Cur_Invoice_Record.Cust_Cc_No(i);
BEGIN
SELECT Cc_Manager_Coll_Name_705
INTO v_Coll_Name
,
FROM Cc_Organization Cc
,Personnel p
WHERE Cc_No_705 = v_Cc_Owner;
EXCEPTION
WHEN No_Data_Found THEN
v_Coll_Name := '';
END;
BEGIN
SELECT Email_Id_Formatted
INTO v_From_Email_Id
FROM Email_Statement
WHERE TRIM(Email_Id) = TRIM(Cur_Invoice_Record.Cust_From_Emailid(i));
EXCEPTION
WHEN No_Data_Found THEN
v_From_Email_Id := '';
END;
IF Cur_Invoice_Record.Other_Table_Tc_Flag3_794(i) IS NULL OR
Cur_Invoice_Record.Other_Table_Tc_Flag3_794(i) <> 'Y' THEN
v_Inv_Cust_No := Cur_Invoice_Record.Inv_Cust_No_720(i);
v_Cust_Last_Stmt_Dt := '';
v_Cust_Last_Cheq_Amt := 0;
IF v_Set_Of_Book <> v_Temp_Sb THEN
v_Temp_Sb := v_Set_Of_Book;
END IF;
SELECT COUNT(*)
INTO v_Cc_Count
FROM Dtld_Cores_Conf_Cnc_Connector
WHERE Dtld_Cores_Conf_Id =999999;
--Get Remit To Address
BEGIN
IF Cur_Invoice_Record.Cust_Remit_To_No(i) = ' ' THEN
<<backtoremit>>
BEGIN
IF v_Cc_Count > 0 THEN
v_Temp_Set_Of_Book := v_Set_Of_Book;
SELECT COUNT(1)
INTO v_Remit_Cnt
FROM Remit_To_Address
WHERE Remit_To_Cc_No_712 = v_Cc_Owner AND Remit_To_Cust_Currency_712 = Cur_Invoice_Record.Currency(i);
IF v_Remit_Cnt = 0 THEN
v_Temp_Set_Of_Book := ' ';
SELECT COUNT(1)
INTO v_Remit_Cnt
FROM Remit_To_Address
WHERE Remit_To_Cc_No_712 = v_Cc_Owner
AND Remit_To_Set_Of_Book_712 = ' '
AND Remit_To_Cust_Currency_712 =
Cur_Invoice_Record.Currency(i);
END IF;
IF v_Remit_Cnt = 0 THEN
SELECT Cc_Owner_No_705
INTO v_Cc_Owner
FROM Cc_Organization
WHERE Cc_No_705 = v_Cc_Owner;
GOTO Backtoremit;
END IF;
SELECT Nvl(Remit_To_Name_712, ' ') Remit_To_Name
INTO v_Remit_To_Name
FROM Remit_To_Address
WHERE Remit_To_Cc_No_712 = v_Cc_Owner
AND Remit_To_Set_Of_Book_712 = v_Temp_Set_Of_Book;
ELSE
--#5
<<backtoremit1>>
v_Temp_Set_Of_Book := v_Set_Of_Book;
SELECT COUNT(1)
INTO v_Remit_Cnt
FROM Remit_To_Address
WHERE Remit_To_Cc_No_712 = v_Cc_Owner
AND Remit_To_Cust_Currency_712 = ' ';
IF v_Remit_Cnt = 0 THEN
v_Temp_Set_Of_Book := ' ';
SELECT COUNT(1)
INTO v_Remit_Cnt
FROM Remit_To_Address
WHERE Remit_To_Cc_No_712 = v_Cc_Owner;
END IF;
IF v_Remit_Cnt = 0 THEN
SELECT Cc_Owner_No_705
INTO v_Cc_Owner
FROM Cc_Organization
WHERE Cc_No_705 = v_Cc_Owner;
--#6
GOTO Backtoremit1;
END IF;
SELECT Nvl(Remit_To_Name_712, ' ') Remit_To_Name
INTO v_Remit_To_Name
FROM Remit_To_Address
WHERE Remit_To_Cc_No_712 = v_Cc_Owner;
END IF;
EXCEPTION
WHEN OTHERS THEN
SELECT Cc_Owner_No_705
INTO v_Cc_Owner
FROM Cc_Organization
WHERE Cc_No_705 = v_Cc_Owner;
GOTO Backtoremit;
END;
ELSE
SELECT Nvl(Cremit_To_Name_781, ' ')
INTO v_Remit_To_Name
WHERE Cremit_To_No_781 = Cur_Invoice_Record.Cust_Remit_To_No(i);
END IF;
EXCEPTION
WHEN No_Data_Found THEN
v_Remit_To_Name := '';
WHEN OTHERS THEN
Dbms_Output.Put_Line(SQLCODE);
Dbms_Output.Put_Line(SQLERRM);
END;
SELECT COUNT(1)
INTO v_Cnt
FROM Letter_And_Statement_Header
WHERE Letter_No_788 = 'STMNT'
AND Letter_Lang_788 = v_Cust_Lang;
IF v_Cnt = 0 THEN
v_Remark := ' ';
ELSE
v_Remark := v_Set_Of_Book;
END IF;
v_Acct_Group := Cur_Invoice_Record.Acct_Group(i);
OPEN Cur_Letter_Sob_Acgrp_Curr;
LOOP
FETCH Cur_Letter_Sob_Acgrp_Curr
INTO Cur_Letter_Rec_Sob_Acgrp_Curr;
EXIT WHEN Cur_Letter_Sob_Acgrp_Curr%NOTFOUND;
IF Licnt = 0 THEN
v_Letter_Desc := Cur_Letter_Rec_Sob_Acgrp_Curr.Letter_Desc;
Licnt := Licnt + 1;
ELSE
IF Cur_Letter_Rec_Sob_Acgrp_Curr.Skip_Nl = 'Y' THEN
v_Letter_Desc := v_Letter_Desc || ' ' ||
Cur_Letter_Rec_Sob_Acgrp_Curr.Letter_Desc;
ELSE
v_Letter_Desc := v_Letter_Desc || Chr(10) || Chr(13) ||
Cur_Letter_Rec_Sob_Acgrp_Curr.Letter_Desc;
END IF;
END IF;
END LOOP;
CLOSE Cur_Letter_Sob_Acgrp_Curr;
-- ~~ -- ~~ -- ~~ -- ~~ -- ~~ -- ~~ -- ~~ -- ~~ -- ~~ -- ~~ -- ~~ -- ~~
IF v_Letter_Desc IS NULL THEN
OPEN Cur_Letter_Sob_Acgrp;
LOOP
FETCH Cur_Letter_Sob_Acgrp
INTO Cur_Letter_Rec_Sob_Acgrp;
EXIT WHEN Cur_Letter_Sob_Acgrp%NOTFOUND;
IF Licnt = 0 THEN
v_Letter_Desc := Cur_Letter_Rec_Sob_Acgrp.Letter_Desc;
Licnt := Licnt + 1;
ELSE
IF Cur_Letter_Rec_Sob_Acgrp.Skip_Nl = 'Y' THEN
v_Letter_Desc := v_Letter_Desc || ' ' ||
Cur_Letter_Rec_Sob_Acgrp.Letter_Desc;
ELSE
v_Letter_Desc := v_Letter_Desc || Chr(10) || Chr(13) ||
Cur_Letter_Rec_Sob_Acgrp.Letter_Desc;
END IF;
END IF;
END LOOP;
CLOSE Cur_Letter_Sob_Acgrp;
END IF;
IF v_Letter_Desc IS NULL THEN
Licnt := 0;
OPEN Cur_Letter_Sob;
LOOP
FETCH Cur_Letter_Sob
INTO Cur_Letter_Rec_Sob;
EXIT WHEN Cur_Letter_Sob%NOTFOUND;
IF (Cur_Letter_Rec_Sob.Letter_Desc IS NOT NULL AND Licnt < 5) THEN
v_Letter_Desc := v_Letter_Desc || ' ' ||
Cur_Letter_Rec_Sob.Letter_Desc;
Licnt := Licnt + 1;
END IF;
END LOOP;
CLOSE Cur_Letter_Sob;
END IF;
-- ~~ -- ~~ -- ~~ -- ~~ -- ~~ -- ~~ -- ~~ -- ~~ -- ~~ -- ~~ -- ~~ -- ~~
v_Inv_No := Cur_Invoice_Record.Inv_No(i);
IF v_Set_Of_Book = 'J' AND v_Cc_Owner = 'CJ04' THEN
v_Letter_Desc := v_Letter_Desc ||
' IF PAYMENT IS ALREADY MADE PLEASE IGNORE THIS STATEMENT.';
ELSIF v_Set_Of_Book = 'A' THEN
v_Letter_Desc := v_Letter_Desc ||
' FOR ACCURATE AND PROMPT CREDITING OF YOUR PAYMENTS, PLEASE SEND ALL REMITTANCE INSTRUCTIONS TO OUR EMAIL ADDRESS LISTED BELOW. PLEASE INCLUDE YOUR ACCOUNT NUMBER AND INVOICES TO BE PAID. EMAIL ADDRESS: Aviationstatements.WCS@ge.com';
ELSIF v_Co_Hyphen = 'Y' AND
Cur_Invoice_Record.Inv_Payment_Sched_Id(i) <> ' ' THEN
v_Inv_No := Cur_Invoice_Record.Inv_Payment_Sched_Id(i);
END IF;
-- ~~ -- ~~ -- ~~ -- ~~ -- ~~ -- ~~ -- ~~ -- ~~ -- ~~ -- ~~ -- ~~ -- ~~
BEGIN
SELECT Nvl(Err_Mess_Desc_783, ' ')
INTO v_Attention
FROM Error_Message
WHERE Err_Mess_No_783 = '369';
EXCEPTION
WHEN No_Data_Found THEN
v_Attention := '';
END;
Mdblbalance := 0;
Mdblinvoicework := Cur_Invoice_Record.Inv_Amt(i) -
Cur_Invoice_Record.Inv_Amt_Paid(i);
IF (Cur_Invoice_Record.Inv_Set_Book(i) = 'T' AND
Cur_Invoice_Record.Cust_Int_Rate_Sw(i) = 'Y' AND
To_Date(Cur_Invoice_Record.Inv_Due_Date(i)) < To_Date(v_Input_Date) AND
Mdblrptamtdue > 0) THEN
Mdblrptamtdue := (Mdblrptamtdue * 1.02);
END IF;
Mdbloutstandamt := Mdblinvoicework;
--Last payment details
IF Cur_Invoice_Record.Cust_Cd_Mess_Sw(i) = 'Y' THEN
v_Cust_Last_Cheq_Amt := Cur_Invoice_Record.Cust_Last_Cheq_Amt(i);
BEGIN
SELECT Nvl(Rpt_Desc_782, ' ')
INTO v_Chq_Desc
FROM Report_Description
WHERE Rpt_Lang_782 = Cur_Invoice_Record.Cust_Lang(i);
EXCEPTION
WHEN No_Data_Found THEN
v_Chq_Desc := '';
END;
END IF;
BEGIN
SELECT MAX(Connection_Arc_No_690)
INTO v_Conn_Arc_No
FROM Arc_Invoice_Connector
WHERE Connection_Cust_No_690 = v_Inv_Cust_No;
SELECT Arc_No_729 || Arc_Sub_Tc_729
INTO v_Arc_No
FROM Arc
WHERE Arc_No_729 = v_Conn_Arc_No;
EXCEPTION
WHEN No_Data_Found THEN
v_Conn_Arc_No := 0;
v_Arc_No := '';
END;
IF Cur_Invoice_Record.Inv_Due_Date(i) <> 99999999 THEN
-- Ageing Cursor
OPEN Cur_Ageing;
LOOP
FETCH Cur_Ageing
INTO Cur_Ageing_Record;
EXIT WHEN Cur_Ageing%NOTFOUND;
Midaysoverdue := To_Date(Miageingdate, 'YYYYMMDD') -
To_Date(Cur_Ageing_Record.Inv_Due_Date, 'YYYYMMDD');
END LOOP;
CLOSE Cur_Ageing;
END IF;
IF Midaysoverdue <= -31 THEN
Mdblnotdueamt := Mdblnotdueamt + Mdbloutstandamt;
Connection_Ageing_Cat := '1';
ELSIF Midaysoverdue > 0 THEN
Mdblpastdue := Mdblpastdue + Mdbloutstandamt;
Connection_Ageing_Cat := '3';
ELSIF (Midaysoverdue > -31 AND Midaysoverdue <= 0) THEN
Mdblcurrentamt := Mdblcurrentamt + Mdbloutstandamt;
Connection_Ageing_Cat := '2';
END IF;
v_Cust_Tot_Inv := v_Cust_Tot_Inv + 1; -- updating invoice count
IF v_Temp_Mod_Cust_No = ' ' THEN
v_Temp_Mod_Cust_No := v_Inv_Cust_No;
v_Temp_Inv_Cnt := v_Cust_No_Detail;
ELSIF v_Temp_Mod_Cust_No = (v_Inv_Cust_No) THEN
v_Temp_Mod_Cust_No := v_Inv_Cust_No;
ELSIF v_Temp_Mod_Cust_No <> (v_Inv_Cust_No) THEN
IF v_Temp_Inv_Cnt <> (v_Cust_Tot_Inv - 1) THEN
IF v_Tmp_Sb = ' ' THEN
v_Tmp_Sb := v_Set_Of_Book;
UPDATE Temp_Customer_Statement
SET Custtotinv = v_Cust_Tot_Inv - 1
WHERE Cust_No = v_Temp_Mod_Cust_No
AND Setofbook = v_Tmp_Sb;
ELSIF v_Tmp_Sb = v_Set_Of_Book THEN
v_Tmp_Sb := v_Set_Of_Book;
UPDATE Temp_Customer_Statement t
SET t.Custtotinv = v_Cust_Tot_Inv - 1
WHERE Cust_No = v_Temp_Mod_Cust_No
AND Setofbook = v_Tmp_Sb;
ELSIF v_Tmp_Sb <> v_Set_Of_Book THEN
UPDATE Temp_Customer_Statement t
SET t.Custtotinv = v_Cust_Tot_Inv - 1
WHERE Cust_No = v_Temp_Mod_Cust_No
AND Setofbook = v_Tmp_Sb;
END IF;
END IF;
v_Cust_Tot_Inv := 1;
Inv_Stat_Notdue_Amt := 0;
Mdblbalance := Mdblbalance + Mdblinvoicework;
Mdblinvoiceworktrans := Cur_Invoice_Record.Inv_Trans_Amt(i) -
v_Dbltransamtpaid;
END IF;
-- ~~ -- ~~ -- ~~ -- ~~ -- ~~ -- ~~ -- ~~ -- ~~ -- ~~ -- ~~ -- ~~ -- ~~
v_Cust_Addr_Line6 := Cur_Invoice_Record.Cust_Addr_Line6(i);
IF v_Cc_Stmt_Payor_Level_705 = 'Y' THEN
IF v_Inv_Cust_No <> Cur_Invoice_Record.Cust_High_Payor_No(i) THEN
v_Inv_Cust_No := Cur_Invoice_Record.Cust_High_Payor_No(i);
SELECT Nvl(Cust_Long_Name_717, ' ') Cust_Long_Name
INTO v_Cust_Long_Name
FROM Customer c
,Customer_Address Ca
WHERE Ca.Cust_No_682 = c.Cust_No_717;
END IF;
END IF;
-- ~~ -- ~~ -- ~~ -- ~~ -- ~~ -- ~~ -- ~~ -- ~~ -- ~~ -- ~~ -- ~~ -- ~~
IF Cur_Invoice_Record.Inv_Set_Book(i) = 'Q' AND
Cur_Invoice_Record.Cust_Cc_No(i) IN
('SB75', 'SB76', 'CO51', 'CO52', 'CO53', 'CO54') THEN
IF v_Cust_Addr_Line3 = ' ' THEN
v_Cust_Addr_Line2 := ' '; -- it will contain country and postal code, so clear it.
ELSE
IF Length(TRIM(REPLACE(v_Cust_Addr_Line2
,Cur_Invoice_Record.Cust_City(i)))) > 0 THEN
v_Cust_Addr_Line2 := REPLACE(v_Cust_Addr_Line2
,Cur_Invoice_Record.Cust_City(i)
,' ');
END IF;
v_Cust_Addr_Line3 := REPLACE(v_Cust_Addr_Line3
,Cur_Invoice_Record.Cust_City(i)
,' ');
IF v_Cust_Addr_Line2 = ' ' AND v_Cust_Addr_Line3 <> ' ' THEN
v_Cust_Addr_Line2 := v_Cust_Addr_Line3;
v_Cust_Addr_Line3 := ' ';
END IF;
END IF;
v_Cust_Addr_Line3 := Cur_Invoice_Record.Cust_Postal_Code(i) || ' ' ||
Cur_Invoice_Record.Cust_City(i);
v_Cust_Addr_Line4 := Cur_Invoice_Record.Cust_Country_Name(i);
-- ~~ -- ~~ -- ~~ -- ~~ -- ~~ -- ~~ -- ~~ -- ~~ -- ~~ -- ~~ -- ~~ -- ~~
IF v_Cust_Addr_Line2 = ' ' OR v_Cust_Addr_Line3 = ' ' THEN
IF v_Cust_Addr_Line2 = ' ' AND v_Cust_Addr_Line3 <> ' ' THEN
v_Cust_Addr_Line4 := ' ';
ELSIF v_Cust_Addr_Line2 <> ' ' AND v_Cust_Addr_Line3 = ' ' AND
v_Cust_Addr_Line4 <> ' ' THEN
v_Cust_Addr_Line4 := ' ';
ELSIF v_Cust_Addr_Line3 = ' ' AND v_Cust_Addr_Line4 <> ' ' THEN
v_Cust_Addr_Line4 := ' ';
END IF;
END IF;
ELSE
v_Cust_Addr_Line4 := ' ';
END IF;
-- ~~ -- ~~ -- ~~ -- ~~ -- ~~ -- ~~ -- ~~ -- ~~ -- ~~ -- ~~ -- ~~ -- ~~
IF v_Cc_Stmt_Multi_Lang_705 = 'N' THEN
v_Cust_Lang := 'E';
END IF;
-- ~~ -- ~~ -- ~~ -- ~~ -- ~~ -- ~~ -- ~~ -- ~~ -- ~~ -- ~~ -- ~~ -- ~~
v_Cust_Addr_Line := '';
IF v_Cust_Addr_Line5 IS NOT NULL AND v_Cust_Addr_Line5 <> ' ' THEN
v_Cust_Addr_Line := v_Cust_Addr_Line5;
END IF;
IF v_Cust_Addr_Line6 IS NOT NULL AND v_Cust_Addr_Line6 <> ' ' THEN
IF v_Cust_Addr_Line IS NULL OR v_Cust_Addr_Line = ' ' THEN
ELSE
IF v_Cust_Addr_Line3 IS NULL OR v_Cust_Addr_Line3 = ' ' THEN
v_Cust_Addr_Line3 := v_Cust_Addr_Line2;
ELSE
v_Cust_Addr_Line := v_Cust_Addr_Line || Chr(10) || Chr(13) ||
v_Cust_Addr_Line6;
END IF;
END IF;
END IF;
IF v_Cust_Addr_Line IS NOT NULL AND v_Cust_Addr_Line <> ' ' THEN
IF v_Cust_Addr_Line3 IS NULL OR v_Cust_Addr_Line3 = ' ' THEN
ELSE
v_Cust_Addr_Line3 := v_Cust_Addr_Line || Chr(10) || Chr(13) ||
v_Cust_Addr_Line3;
END IF;
END IF;
v_Cust_Parent_Level_Flag := Cur_Invoice_Record.Cust_Parent_Level_Flag(i);
INSERT INTO Temp_Customer_Statement
(Cust_Contact
,Ardw_Remit_To_Addr5
)
VALUES
(Cur_Invoice_Record.Cust_Contact(i)
,Cur_Invoice_Record.Ardw_Remit_To_Addr5(i)
);
END IF;
END LOOP;
COMMIT;
END LOOP;
IF Cur_Invoice%ISOPEN THEN
CLOSE Cur_Invoice;
END IF;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
v_Err_Num := SQLCODE;
v_Err_Msg := Substr(SQLERRM, 1, 100);
IF Cur_Invoice%ISOPEN THEN
CLOSE Cur_Invoice;
END IF;
INSERT INTO Load_Err
VALUES
(v_Err_Num
,v_Err_Msg
,To_Number(To_Char(SYSDATE, 'YYYYMMDD'))
,'ELECT_STMT'
,'Cust No -' || v_Inv_Cust_No || '-Finder_No-' || v_Inv_Finder_No ||
'-Spr_Gcars_Customer_Statement-Insert-Temp_Customer_Statement');
COMMIT;
END Loop_Cur_Invoice;
END LOOP;
<<upd_Temp_Invduedate>>
DECLARE
CURSOR Cur_Temp_Invduedate IS
SELECT Cust_No
,Invfinderno
FROM Temp_Customer_Statement
,Invoice
WHERE Inv_Cust_No_720 = Cust_No
AND Inv_Disc_Rec_720 IN ('DD', 'DR');
TYPE t_Rec_Upd_Temp IS RECORD(
Cust_No Dbms_Sql.Varchar2_Table
,Invfinderno Dbms_Sql.Number_Table);
l_Rec_Temp t_Rec_Upd_Temp;
Err_Num NUMBER;
Err_Msg VARCHAR2(100);
v_Num_Errors BINARY_INTEGER := 0;
p_Cust_No VARCHAR2(200);
p_Finderno BINARY_INTEGER;
BEGIN
IF Cur_Temp_Invduedate%ISOPEN THEN
CLOSE Cur_Temp_Invduedate;
END IF;
OPEN Cur_Temp_Invduedate;
LOOP
EXIT WHEN Cur_Temp_Invduedate%NOTFOUND;
FETCH Cur_Temp_Invduedate BULK COLLECT
INTO l_Rec_Temp LIMIT v_Limit;
--#7
FORALL i IN 1 .. l_Rec_Temp.Cust_No.Count --SAVE EXCEPTIONS
UPDATE Temp_Customer_Statement
SET Invduedate = Invduedate || '*'
WHERE Cust_No = l_Rec_Temp.Cust_No(i)
AND Invfinderno = l_Rec_Temp.Invfinderno(i);
COMMIT;
END LOOP;
COMMIT;
IF Cur_Temp_Invduedate%ISOPEN THEN
CLOSE Cur_Temp_Invduedate;
END IF;
--#8
EXCEPTION
WHEN OTHERS THEN
v_Num_Errors := SQL%Bulk_Exceptions.Count;
IF Cur_Temp_Invduedate%ISOPEN THEN
CLOSE Cur_Temp_Invduedate;
END IF;
FOR Err IN 1 .. v_Num_Errors
LOOP
p_Cust_No := l_Rec_Temp.Cust_No(SQL%BULK_EXCEPTIONS(Err).Error_Index);
p_Finderno := l_Rec_Temp.Invfinderno(SQL%BULK_EXCEPTIONS(Err).Error_Index);
Err_Num := SQL%BULK_EXCEPTIONS(Err).Error_Index;
Err_Msg := Substr(SQLERRM(-1 * SQL%BULK_EXCEPTIONS(Err).Error_Code), 1, 100);
INSERT INTO Load_Err
VALUES
(Err_Num
,Err_Msg
,To_Number(To_Char(SYSDATE, 'YYYYMMDD'))
,'ELECT_STMT'
,'Cust No -' || p_Cust_No || '-Finder no-' || p_Finderno ||
'- Spr_abc_Customer_Statement - Update Temp_Customer_Statement for INVDUEDATE');
END LOOP;
COMMIT;
END Upd_Temp_Invduedate;
EXCEPTION
WHEN OTHERS THEN
Dbms_Output.Put_Line(v_Inv_Cust_No);
Dbms_Output.Put_Line(v_Inv_Finder_No);
Dbms_Output.Put_Line(SQLCODE);
Dbms_Output.Put_Line(SQLERRM);
IF Cur_Invoice%ISOPEN THEN
CLOSE Cur_Invoice;
END IF;
v_Err_Num := SQLCODE;
v_Err_Msg := Substr(SQLERRM, 1, 100);
ROLLBACK;
RAISE;
END;