[Home] [Help]
PACKAGE BODY: APPS.IEX_TRX_GRID_PVT
Source
1 PACKAGE BODY IEX_TRX_GRID_PVT AS
2 /* $Header: iexvtrcb.pls 120.1 2006/05/24 03:59:07 jypark noship $ */
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 BEGIN
99 PG_DEBUG := TO_NUMBER(NVL(FND_PROFILE.value('IEX_DEBUG_LEVEL'), '20'));
100 G_APPL_ID := FND_GLOBAL.Prog_Appl_Id;
101 G_LOGIN_ID := FND_GLOBAL.Conc_Login_Id;
102 G_PROGRAM_ID := FND_GLOBAL.Conc_Program_Id;
103 G_USER_ID := FND_GLOBAL.User_Id;
104 G_REQUEST_ID := FND_GLOBAL.Conc_Request_Id;
105 END IEX_TRX_GRID_PVT;