Paymix to BEE Conversion Problem 1

[jbasti]

We currently have a VB/SQL 2000 program that populates the Paymix tables in Oracle with timecard records. However, we need to convert this program to populate the BEE tables in 11i. I planned on changing the data to the proper format then append it directly to the BEE tables but I am running into problems. I have changed the SQL stored procedures to create records for the PAY_BATCH_HEADERS & PAY_BATCH_LINES tables but I am unable to figure out how to create the batch and batch line IDs. I tried to used the max value in the table and then increment it for the new records but run into duplicate index problems when users manually enter batches within Oracle.

I have heard of the Oracle APIs but I am not sure how I can use them since we do not use Oracle Developer.

If anyone knows how Oracle generates the new Batch Ids or how to use the Oracle API within VB your help would be greatly appreciated. Thanks.

Jeff

 

[jimbopalmer]

Our code uses the sequences that oracle uses, in this snippet of code check the lines that include NEXTVAL that gets you oracle next sequence value. Our project was called TA, (time and Attendance) which is where that constant came from

-- FUNCTION create_batch_head
-- create new batch
-- return batch_id
--
FUNCTION create_batch_head ( p_business_group_id IN NUMBER,
p_element_name IN VARCHAR2,
p_end_date IN DATE )
RETURN NUMBER IS
--
--
l_batch_id number := 0;
l_batch_ref varchar2(30) := null;
l_batch_source varchar2(30) := 'TA';
l_batch_name varchar2(30) := null;
--
begin
--
--
select PAY_BATCH_HEADERS_S.NEXTVAL
INTO l_batch_id
from dual;
--
l_batch_name :=
to_char(p_end_date,'DD-MON-YYYY')||'-'||to_char(l_batch_id);


l_batch_ref := substr(p_element_name,1,30);
--
--
insert into PAY_BATCH_HEADERS values (
l_batch_id,
p_business_group_id,
l_batch_name,
'U',
'I',
l_batch_ref,
l_batch_source,
NULL,
NULL,
'Y',
'N',
sysdate,
0,
0,
0,
sysdate);
--
RETURN l_batch_id;
--
END create_batch_head;

--
-- Create Batch Lines
--
FUNCTION create_batch_line (
P_batch_id NUMBER,
P_assignment_id NUMBER,
P_assignment_number VARCHAR2,
P_element_name VARCHAR2,
P_element_type_id NUMBER,
P_hours NUMBER,
P_hours_pos NUMBER,
P_attribute1 VARCHAR2,
P_sequence NUMBER,
P_end_date DATE,
P_ccid NUMBER )
RETURN NUMBER IS
--
--
cursor csr_ckf_details (P_ccid number) is
select *
from pay_cost_allocation_keyflex
where cost_allocation_keyflex_id = P_ccid;
--
l_cost_rec csr_ckf_details%ROWTYPE;
--
l_line_id NUMBER := 0;
l_batch_status VARCHAR2(30) := 'U';
l_entry_type VARCHAR2(1) := 'E';
--
--
Begin
--
IF P_ccid is not null
--
THEN
OPEN csr_ckf_details (P_ccid );
FETCH csr_ckf_details INTO l_cost_rec;
IF csr_ckf_details%NOTFOUND
THEN
RETURN -99;
END IF;
END IF;
---
--
select pay_batch_lines_s.nextval
into l_line_id
from dual;
--
--
def_table(P_hours_pos) := P_hours;
--
IF P_ccid is not null
THEN
insert into pay_batch_lines values (
l_line_id,
p_ccid,
p_element_type_id,
p_assignment_id,
p_batch_id,
l_batch_status,
p_assignment_number,
P_sequence,
l_cost_rec.CONCATENATED_SEGMENTS,
P_end_date,
p_element_name,
l_entry_type,
Null,
l_cost_rec.SEGMENT1,
l_cost_rec.SEGMENT2,
l_cost_rec.SEGMENT3,
l_cost_rec.SEGMENT4,
l_cost_rec.SEGMENT5,
l_cost_rec.SEGMENT6,
l_cost_rec.SEGMENT7,
l_cost_rec.SEGMENT8,
l_cost_rec.SEGMENT9,
l_cost_rec.SEGMENT10,
l_cost_rec.SEGMENT11,
l_cost_rec.SEGMENT12,
l_cost_rec.SEGMENT13,
l_cost_rec.SEGMENT14,
l_cost_rec.SEGMENT15,
l_cost_rec.SEGMENT16,
l_cost_rec.SEGMENT17,
l_cost_rec.SEGMENT18,
l_cost_rec.SEGMENT19,
l_cost_rec.SEGMENT20,
l_cost_rec.SEGMENT21,
l_cost_rec.SEGMENT22,
l_cost_rec.SEGMENT23,
l_cost_rec.SEGMENT24,
l_cost_rec.SEGMENT25,
l_cost_rec.SEGMENT26,
l_cost_rec.SEGMENT27,
l_cost_rec.SEGMENT28,
l_cost_rec.SEGMENT29,
l_cost_rec.SEGMENT30,
def_table(1),
def_table(2),
def_table(3),
def_table(4),
def_table(5),
def_table(6),
def_table(7),
def_table(8),
def_table(9),
def_table(10),
def_table(11),
def_table(12),
def_table(13),
def_table(14),
def_table(15),
null,
P_attribute1,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null);
CLOSE csr_ckf_details;
---
ELSE
insert into pay_batch_lines values (
l_line_id,
p_ccid,
p_element_type_id,
p_assignment_id,
p_batch_id,
l_batch_status,
p_assignment_number,
P_sequence,
Null,
P_end_date,
p_element_name,
l_entry_type,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
Null,
def_table(1),
def_table(2),
def_table(3),
def_table(4),
def_table(5),
def_table(6),
def_table(7),
def_table(8),
def_table(9),
def_table(10),
def_table(11),
def_table(12),
def_table(13),
def_table(14),
def_table(15),
null,
P_attribute1,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null);
END IF;
--
--
RETURN l_line_id;
--
--
END create_batch_line; I tried to remain child-like, all I acheived was childish.

 

[jbasti]

Thanks for the help.