PACKAGE BODY: APPS.IES_TRANSACTION_SEGMENTS_PKG

Source

1 PACKAGE BODY ies_transaction_segments_pkg AS
2   /* $Header: iestrsb.pls 115.5 2003/06/06 20:16:25 prkotha noship $ */
3   G_PKG_NAME CONSTANT VARCHAR2(30) := 'ies_transaction_segments_pkg';
4 
5 
6   /* PRIVATE FUNCTION */
7 
8   FUNCTION getSegmentId(p_transaction_id IN NUMBER) return NUMBER IS
9     segmentId NUMBER;
10 
11     TYPE segment_type IS REF CURSOR;
12     segment segment_type;
13   BEGIN
14     OPEN segment FOR
15     'SELECT max(segment_id)
16        FROM ies_trans_segments
17       WHERE transaction_id = :id' using p_transaction_id;
18 
19     FETCH segment INTO segmentId;
20     CLOSE segment;
21     return segmentId;
22   END;
23 
24   /* PUBLIC PROCEDURES */
25 
26   PROCEDURE create_Segment (p_transaction_id IN NUMBER,
27                             p_user_id IN number,
28                             p_dscript_id IN NUMBER,
29                             x_id OUT NOCOPY  NUMBER) IS
30     sqlstmt   VARCHAR2(2000);
31     transactionId NUMBER;
32     seqval        NUMBER;
33   BEGIN
34     if (p_transaction_id = 0) then
35         transactionId := ies_transaction_util_pkg.insert_transaction(p_user_id, p_dscript_id);
36     else
37         ies_transaction_util_pkg.update_transaction(p_transaction_id, 2, empty_clob(), p_user_id);
38         transactionId := p_transaction_id;
39     end if;
40 
41     execute immediate 'select ies_trans_segments_s.nextval from dual' into seqval;
42 
43     sqlStmt := 'insert into ies_trans_segments
44                                    (segment_id,
45                                     transaction_id,
46                                     created_by,
47                                     creation_date,
48                                     start_time)
49                             values (:seq,
50                                     :p_transaction_id,
51                                     :p_user_id,
52                                     :1,
53                                     :2)';
54     execute immediate sqlStmt using seqval, transactionId, p_user_id, sysdate, sysdate;
55     x_id := transactionId;
56 
57   END;
58 
59 
60    PROCEDURE update_Segment (p_transaction_id IN NUMBER,
61                              p_status IN NUMBER,
62                              p_restart_clob IN CLOB,
63                              p_user_id IN NUMBER) IS
64     sqlstmt   VARCHAR2(2000);
65     segmentId number;
66   BEGIN
67     segmentId := getSegmentId(p_transaction_id);
68     ies_transaction_util_pkg.update_transaction(p_transaction_id,
69                                             p_status,
70                                             p_restart_clob,
71                                             p_user_id);
72     sqlStmt := 'update ies_trans_segments set end_time = :1,
73                                                last_update_date = :2,
74                                                last_updated_by = :3 where segment_id = :id';
75     execute immediate sqlStmt using sysdate, sysdate, p_user_id, segmentId;
76   END;
77 
78 
79 
80 END ies_transaction_segments_pkg;