[Home] [Help]
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;