PACKAGE BODY: APPS.IES_TRANSACTION_UTIL_PKG

Source

1 PACKAGE BODY ies_transaction_util_pkg AS
2   /* $Header: iestrnb.pls 120.0 2005/06/03 07:34:55 appldev noship $ */
3   G_PKG_NAME CONSTANT VARCHAR2(30) := 'ies_transaction_util_pkg';
4 
5 
6   /* PRIVATE PROCEDURE */
7 
8   PROCEDURE delete_old_data(p_transaction_Id IN NUMBER) IS
9      panelDataStmt VARCHAR2(2000);
10      questionDataStmt VARCHAR2(2000);
11   BEGIN
12      questionDataStmt := 'delete from ies_question_data where transaction_id = :1';
13      execute immediate questionDataStmt using p_transaction_id;
14 
15      panelDataStmt := 'delete from ies_panel_data where transaction_id = :1';
16      execute immediate panelDataStmt using p_transaction_id;
17 
18   END;
19 
20   FUNCTION get_transaction_status(p_transaction_id IN NUMBER) return NUMBER IS
21     l_status NUMBER;
22 
23     TYPE transaction_status_type IS REF CURSOR;
24     trans_status transaction_status_type;
25   BEGIN
26     OPEN trans_status FOR
27     'SELECT nvl(status, 0)
28        FROM ies_transactions
29       WHERE transaction_id = :id for update' using p_transaction_id;
30 
31     FETCH trans_status INTO l_status;
32     CLOSE trans_status;
33     return l_status;
34   END;
35 
36 
37   /* PUBLIC PROCEDURES */
38 
39   PROCEDURE getTemporaryCLOB (x_clob OUT NOCOPY  CLOB) IS
40   BEGIN
41     DBMS_LOB.CreateTemporary(x_clob, TRUE, DBMS_LOB.CALL);
42   END;
43 
44 
45   FUNCTION  getRestartXMLData(p_transaction_Id IN NUMBER) RETURN CLOB IS
46     TYPE  restartXML_Type IS REF CURSOR;
47     restart   restartXML_Type;
48 
49     x_clob    CLOB;
50     l_status  NUMBER;
51   BEGIN
52     l_status := get_transaction_status(p_transaction_id);
53     if (l_status = 2) then
54        raise_application_error(-20001, 'Error in restart');
55     end if;
56 
57     OPEN restart FOR
58     'SELECT restart_data
59        FROM ies_transactions
60       WHERE transaction_id = :id' using p_transaction_Id;
61 
62     FETCH restart INTO x_clob;
63     CLOSE restart;
64     return x_clob;
65   END;
66 
67   PROCEDURE Update_Transaction(p_transaction_Id in number) IS
68     sqlstmt   VARCHAR2(2000);
69   BEGIN
70     sqlStmt := 'update ies_transactions set status = null where transaction_id = :id';
71     execute immediate sqlStmt using p_transaction_id;
72   END;
73 
74   PROCEDURE Update_Transaction(p_transaction_Id in number,
75                                p_status IN NUMBER,
76                                p_restart_clob IN CLOB,
77                                p_user_id IN NUMBER) IS
78     sqlstmt   VARCHAR2(2000);
79   BEGIN
80     delete_Old_Data(p_transaction_Id);
81     sqlStmt := 'update ies_transactions set status = :1,
82                                       restart_data = :2,
83                                   last_update_date = sysdate,
84                                   end_time = sysdate,
85                                   last_updated_by = :3 where transaction_id = :id';
86     execute immediate sqlStmt using p_status, p_restart_clob, p_user_id, p_transaction_id;
87   END;
88 
89   FUNCTION insert_transaction(p_user_Id IN NUMBER,
90                                p_dscript_Id IN NUMBER) RETURN NUMBER IS
91     transactionId NUMBER;
92     sqlstmt VARCHAR2(2000);
93   BEGIN
94     SELECT ies_transactions_s.nextval INTO transactionId FROM dual;
95 
96     sqlstmt := 'insert into ies_transactions(transaction_id, created_by, creation_date, agent_id, dscript_id, start_time)
97                          values(:1, :2, sysdate, :3, :4, sysdate)';
98     execute immediate sqlstmt using transactionId, p_user_id, p_user_id, p_dscript_id;
99     return transactionId;
100   END;
101 
102 
103 
104 
105 END ies_transaction_util_pkg;