×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Paymix to BEE Conversion Problem

Paymix to BEE Conversion Problem

Paymix to BEE Conversion Problem

(OP)
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

RE: Paymix to BEE Conversion Problem

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.

RE: Paymix to BEE Conversion Problem

(OP)
Thanks for the help.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close