Create Sproc (loop) To Insert Data

Need to create Sproc(loop) to insert data..We have 2 table:

- BE Table
Name Null Type
---------------------- -------- --------------
BID NOT NULL NUMBER(10)
BUSINESSENTITYTYPEID NOT NULL NUMBER(10)
PARENTID NUMBER(10)
VERSION NOT NULL NUMBER(10)
FULLNAME VARCHAR2(255)
PHONE VARCHAR2(255)
FAX VARCHAR2(255)
[code]....

Whenever the BE table is read it gives more than 80K records from cust_id=1 and so on and becomes a bit slow ..

Now, when we copy the records from BE to CUSTPAYMETHOD (assume 30k). Next time when we read the table it should show us the customerid from 3001based on New Customer's(user created) Customerid.

CUSTID column is logically linked with BID column (FK not enforced) We don't have to pass parameter as we know the cust_id=1,flag for suspended='Y' and Eff_date between date1 and date2..We have accept from/to Period.Probably a loop to insert...This particular SP will run in all env and the id is going to be different how we are going to handle this...

We want to have a procedure. This is to purge BE table.

1) Insert a new parameter CUST_PMNT_MTHD_RETENTION_PERIOD into system_parameter table(which is only a parameter table) -- this can be done outside of Proc.

2)Create a new Customer(user id) through the Customer application and then find out the custID for the new customer -- this can be done outside of Proc.

SP should be for:

- Store Proc will Update the CustID in the CustPayMethod table to the new customer ID (that was created) when the EFFECTIVESTARTDATE older than CUST_PMNT_MTHD_RETENTION_PERIOD(from system_parameter table) days and suspended=’Y’.

- Copy records with custid=1(anonymous user) and startdata older than retention_period and suspended='Y' to Table CUSTPAYMETHOD

Eventually,delete the record from BE table.BusinessEntity(parent) table is information about customer and customerpaymentmethod(child) table is about payment mode(how payment is made by customer).

Assume If BID=1 then we can find Cust_id=1(which can have many records as the payment mode may change because the customer pay by different mode(credit,debit etc).We have to use loop and commit every 200 or 500 records.

FIrst we have to update the table with new cust_id and then insert...