[Home] [Help]
PACKAGE BODY: APPS.IEX_TRX_GRID_PVT
Source
1 PACKAGE BODY IEX_TRX_GRID_PVT AS
2 /* $Header: iexvtrcb.pls 120.3 2011/06/01 12:27:11 ehuh ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IEX_TRX_GRID_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'iexvtrcb.pls';
6 G_APPL_ID NUMBER;
7 G_LOGIN_ID NUMBER;
8 G_PROGRAM_ID NUMBER;
9 G_USER_ID NUMBER;
10 G_REQUEST_ID NUMBER;
11
12 PG_DEBUG NUMBER(2);
13
14 PROCEDURE Set_Unpaid_Reason
15 (p_api_version IN NUMBER := 1.0,
16 p_init_msg_list IN VARCHAR2,
17 p_commit IN VARCHAR2,
18 p_validation_level IN NUMBER,
19 x_return_status OUT NOCOPY VARCHAR2,
20 x_msg_count OUT NOCOPY NUMBER,
21 x_msg_data OUT NOCOPY VARCHAR2,
22 p_del_ids IN VARCHAR2,
23 p_unpaid_reason IN VARCHAR2,
24 x_rows_processed OUT NOCOPY NUMBER)
25 IS
26 l_api_version CONSTANT NUMBER := 1.0;
27 l_api_name CONSTANT VARCHAR2(30) := 'SET_UNPAID_REASON';
28 l_return_status VARCHAR2(1);
29 l_msg_count NUMBER;
30 l_msg_data VARCHAR2(32767);
31
32 l_sql_stmt VARCHAR2(32767);
33 l_cursor_id NUMBER;
34 BEGIN
35 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ':begin');
36
37 SAVEPOINT Set_Unpaid_Reason_PVT;
38
39 -- Standard call to check for call compatibility.
40 IF NOT FND_API.Compatible_API_Call (l_api_version,
41 p_api_version,
42 l_api_name,
43 G_PKG_NAME) THEN
44 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
45 END IF;
46
47 -- Check p_init_msg_list
48 IF FND_API.to_Boolean( p_init_msg_list ) THEN
49 FND_MSG_PUB.initialize;
50 END IF;
51
52 x_return_status := 'S';
53
54 l_sql_stmt := 'UPDATE iex_delinquencies_all SET unpaid_reason_code = :b_unpaid_reason ' ||
55 ',last_update_date = SYSDATE ' ||
56 ',last_updated_by = :b_last_updated_by ' ||
57 ',last_update_login = :b_last_update_login ' ||
58 'WHERE delinquency_id IN (' || p_del_ids || ')';
59
60 iex_debug_pub.LogMessage('l_sql_stmt=' || l_sql_stmt);
61
62 l_cursor_id := DBMS_SQL.OPEN_CURSOR;
63 DBMS_SQL.PARSE(l_cursor_id, l_sql_stmt, 1);
64 DBMS_SQL.BIND_VARIABLE(l_cursor_id, 'b_unpaid_reason', p_unpaid_reason);
65 DBMS_SQL.BIND_VARIABLE(l_cursor_id, 'b_last_updated_by', FND_GLOBAL.USER_ID);
66 DBMS_SQL.BIND_VARIABLE(l_cursor_id, 'b_last_update_login', FND_GLOBAL.CONC_LOGIN_ID);
67 x_rows_processed := DBMS_SQL.EXECUTE(l_cursor_id);
68 DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
69
70 -- Standard check of p_commit
71 IF FND_API.To_Boolean(p_commit) THEN
72 COMMIT WORK;
73 END IF;
74
75 -- Standard call to get message count and if count is 1, get message info
76 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
77
78 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ':end');
79 EXCEPTION
80 WHEN FND_API.G_EXC_ERROR THEN
81 ROLLBACK TO Set_Unpaid_Reason_PVT;
82 x_return_status := FND_API.G_RET_STS_ERROR;
83 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
84
85 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
86 ROLLBACK TO Set_Unpaid_Reason_PVT;
87 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
88 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
89
90 WHEN OTHERS THEN
91 ROLLBACK TO Set_Unpaid_Reason_PVT;
92 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
93 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
94 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
95 END IF;
96 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
97 END Set_Unpaid_Reason;
98
99 PROCEDURE SET_STAGED_DUNNING_LEVEL
100 (p_api_version IN NUMBER := 1.0,
101 p_init_msg_list IN VARCHAR2,
102 p_commit IN VARCHAR2,
103 p_validation_level IN NUMBER,
104 p_delinquency_id IN NUMBER,
105 p_stg_dunn_level IN NUMBER,
106 x_return_status OUT NOCOPY VARCHAR2,
107 x_msg_count OUT NOCOPY NUMBER,
108 x_msg_data OUT NOCOPY VARCHAR2)
109 IS
110 l_api_version CONSTANT NUMBER := 1.0;
111 l_api_name CONSTANT VARCHAR2(30) := 'SET_STAGED_DUNNING_LEVEL';
112 l_return_status VARCHAR2(1);
113 l_msg_count NUMBER;
114 l_msg_data VARCHAR2(32767);
115
116 BEGIN
117 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ':begin');
118
119 SAVEPOINT SET_STAGED_DUNN_PVT;
120
121 -- Standard call to check for call compatibility.
122 IF NOT FND_API.Compatible_API_Call (l_api_version,
123 p_api_version,
124 l_api_name,
125 G_PKG_NAME) THEN
126 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
127 END IF;
128
129 -- Check p_init_msg_list
130 IF FND_API.to_Boolean( p_init_msg_list ) THEN
131 FND_MSG_PUB.initialize;
132 END IF;
133
134 x_return_status := 'S';
135
136 UPDATE iex_delinquencies_all
137 SET staged_dunning_level = p_stg_dunn_level
138 ,last_update_date = SYSDATE
139 ,last_updated_by = FND_GLOBAL.USER_ID
140 ,last_update_login = FND_GLOBAL.CONC_LOGIN_ID
141 ,dunning_level_override_date = SYSDATE
142 WHERE delinquency_id = p_delinquency_id;
143
144 -- Standard check of p_commit
145 IF FND_API.To_Boolean(p_commit) THEN
146 COMMIT WORK;
147 END IF;
148
149 -- Standard call to get message count and if count is 1, get message info
150 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
151
152 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ':end');
153 EXCEPTION
154 WHEN FND_API.G_EXC_ERROR THEN
155 ROLLBACK TO SET_STAGED_DUNN_PVT;
156 x_return_status := FND_API.G_RET_STS_ERROR;
157 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
158
159 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
160 ROLLBACK TO SET_STAGED_DUNN_PVT;
161 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
162 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
163
164 WHEN OTHERS THEN
165 ROLLBACK TO SET_STAGED_DUNN_PVT;
166 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
167 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
168 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
169 END IF;
170 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
171
172 END SET_STAGED_DUNNING_LEVEL;
173
174 BEGIN
175 PG_DEBUG := TO_NUMBER(NVL(FND_PROFILE.value('IEX_DEBUG_LEVEL'), '20'));
176 G_APPL_ID := FND_GLOBAL.Prog_Appl_Id;
177 G_LOGIN_ID := FND_GLOBAL.Conc_Login_Id;
178 G_PROGRAM_ID := FND_GLOBAL.Conc_Program_Id;
179 G_USER_ID := FND_GLOBAL.User_Id;
180 G_REQUEST_ID := FND_GLOBAL.Conc_Request_Id;
181 END IEX_TRX_GRID_PVT;