I have a procedure I am trying to simplify with a more simplified approach. I am reading from a source, manipulating the data and eventually inserting into another table.
So I am going to ask my first question which is to build my Key which consists of the following
l_FINAL_tabKey := Build_Table_Key(l_FINAL_EffYr, l_FINAL_EffMo, l_FINAL_EffDa, l_FINAL_CVGID, l_FINAL_SeqNum, l_FINAL_polNbr, l_FINAL_Cono);
In my code now I call a function Buiuld_Table_Key and pass a bunch of variables and have them all concatenated and return the string of values concatenated.
So if Im trying to eliminate the loping through of the source data how would I go about building this key
Heres my Insert and what Im trying to do is generate the first value
Im just going to put the value in the Insert statement that I am trying to figure out first.....So the other columns are not there for now(One thing at a time)..I have included the Select statement because that will have the values I need to build the Key
function is in bold.
My question is how can I generate the l_SeqNum (5th param in bold) to increment for each record? Should I do something in the function to increment the number. It needs to be 6 charaters in length starting with '000001' and increment each time?
Insert Into TEMP_BASE_PCOV_POLICY
util.Build_Table_Key(util.GET_DATE_PART('y', c.COV_EFFDT), util.GET_DATE_PART('m', c.COV_EFFDT), util.GET_DATE_PART('d', c.COV_EFFDT),
c.CVG_ID, LPAD(l_SeqNum + 1, 6, '0'), RPAD(c.POLNBR, 18, ' '), SUBSTR(c.CARRID, 3, 3))
Here is the source query I want to use in the Insert statement
c.COV_EFFDT, c.COV_EXPDT, c.CVG_ID, CVG_POL_SEQ,
c.CARRID, car.NAME, c.CARR_ISSUING_STREET, c.CARR_ISSUING_CITY, c.CARR_ISSUING_STATE_CD, c.CARR_ISSUING_ZIP, c.CARR_ISSUING_ADDR_TYPE, c.LCF_IND, c.TXN_CODE,
c.WRAP_UP_IND, '' POLICY_SOLE_PROPRIETOR_IND
Bulk Collect Into l_POLICY_TAB
from COVERAGE c
INNER JOIN CARR car ON c.CARRID = car.CARRID
WHERE COV_EXPDT > l_CONST_covExpDate AND (STATUS != 'V') ORDER BY CARRID ASC, COV_EFFDT ASC;