[Home] [Help]
PACKAGE BODY: APPS.IEX_TRX_GRID_PUB
Source
1 PACKAGE BODY IEX_TRX_GRID_PUB AS
2 /* $Header: iexptrcb.pls 120.1 2010/12/24 09:23:32 snuthala noship $ */
3 /*#
4 * Set UNPAID_REASON_CODE to table IEX_DELINQUENCIES_ALL.
5 * @rep:scope internal
6 * @rep:product IEX
7 * @rep:displayname Set_Unpaid_Reason
8 * @rep:lifecycle active
9 * @rep:compatibility S
10 * @rep:category BUSINESS_ENTITY IEX_DELINQUENCIES_ALL
11 */
12
13 /*#
14 * Set UNPAID_REASON_CODE to table IEX_DELINQUENCIES_ALL.
15 * @param p_api_version API Version Number
16 * @param p_init_msg_list Intialize Message Stack
17 * @param p_commit Commit flag
18 * @param p_validation_level Validation level
19 * @param x_return_status API return status
20 * @param x_msg_count Number of error messages
21 * @param x_msg_data Error message data
22 * @param p_del_ids Delinquency identifier
23 * @param p_unpaid_reason Unpaid_reason_code Possible values should comes from iex_lookups_v with lookup_type 'IEX_UNPAID_REASON'.
24 * @param x_rows_processed Number of rows updated
25 * @rep:scope internal
26 * @rep:displayname Set_Unpaid_Reason
27 * @rep:lifecycle active
28 * @rep:compatibility S
29 */
30
31 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IEX_TRX_GRID_PUB';
32 G_FILE_NAME CONSTANT VARCHAR2(12) := 'iexptrcb.pls';
33 G_APPL_ID NUMBER;
34 G_LOGIN_ID NUMBER;
35 G_PROGRAM_ID NUMBER;
36 G_USER_ID NUMBER;
37 G_REQUEST_ID NUMBER;
38
39 PG_DEBUG NUMBER(2);
40
41 PROCEDURE Set_Unpaid_Reason
42 (p_api_version IN NUMBER := 1.0,
43 p_init_msg_list IN VARCHAR2 := 'T',
44 p_commit IN VARCHAR2 ,
45 p_validation_level IN NUMBER := 100,
46 x_return_status OUT NOCOPY VARCHAR2,
47 x_msg_count OUT NOCOPY NUMBER,
48 x_msg_data OUT NOCOPY VARCHAR2,
49 p_del_ids IN VARCHAR2,
50 p_unpaid_reason IN VARCHAR2,
51 x_rows_processed OUT NOCOPY NUMBER)
52 IS
53 l_api_version CONSTANT NUMBER := 1.0;
54 l_api_name CONSTANT VARCHAR2(30) := 'SET_UNPAID_REASON';
55 l_return_status VARCHAR2(1);
56 l_msg_count NUMBER;
57 l_msg_data VARCHAR2(32767);
58 l_Init_Msg_List VARCHAR2(10);
59 l_Commit VARCHAR2(10);
60 l_validation_level NUMBER;
61
62 l_cnt NUMBER :=0 ;
63 l_sql_stmt VARCHAR2(32767);
64 l_cursor_id NUMBER;
65 l_last_updated_by number := FND_GLOBAL.USER_ID;
66 l_last_update_login number := nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),0);
67
68 Cursor check_code(c_unpaid_reason_code varchar2) is
69 select count(*) from iex_lookups_v where lookup_type = 'IEX_UNPAID_REASON'
70 and lookup_code = c_unpaid_reason_code
71 and enabled_flag = 'Y';
72
73
74
75 BEGIN
76 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ':begin');
77
78 -- initialize variable
79 l_Init_Msg_List := P_Init_Msg_List;
80 l_Commit := P_Commit;
81 l_validation_level := p_validation_level;
82 if (l_Init_msg_List is null) then
83 l_Init_Msg_List := FND_API.G_FALSE;
84 end if;
85 if (l_Commit is null) then
86 l_Commit := FND_API.G_TRUE;
87 end if;
88 if (l_validation_level is null) then
89 l_validation_level := FND_API.G_VALID_LEVEL_FULL;
90 end if;
91
92 SAVEPOINT Set_Unpaid_Reason_PUB;
93
94 -- Standard call to check for call compatibility.
95 IF NOT FND_API.Compatible_API_Call (l_api_version,
96 p_api_version,
97 l_api_name,
98 G_PKG_NAME) THEN
99 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
100 END IF;
101
102 -- Check p_init_msg_list
103 IF FND_API.to_Boolean( p_init_msg_list ) THEN
104 FND_MSG_PUB.initialize;
105 END IF;
106
107 x_return_status := 'S';
108
109 -- Check Unpaid_reason_code
110 begin
111
112 open check_code(p_unpaid_reason);
113 fetch check_code into l_cnt;
114
115 if (check_code%NOTFOUND) or (l_cnt = 0) then
116 fnd_message.set_name('IEX', 'IEX_API_ALL_INVALID_ARGUMENT');
117 fnd_message.set_token('API_NAME', l_api_name);
118 fnd_message.set_token('VALUE', p_unpaid_reason);
119 fnd_message.set_token('PARAMETER', 'p_unpaid_reason');
120 FND_MSG_PUB.Add;
121
122 close check_code;
123 RAISE FND_API.G_EXC_ERROR;
124 return;
125 end if;
126 close check_code;
127
128 exception
129 when others then
130 fnd_message.set_name('IEX', 'IEX_API_ALL_INVALID_ARGUMENT');
131 fnd_message.set_token('API_NAME', l_api_name);
132 fnd_message.set_token('VALUE', p_unpaid_reason);
133 fnd_message.set_token('PARAMETER', 'p_unpaid_reason');
134 FND_MSG_PUB.Add;
135
136 RAISE FND_API.G_EXC_ERROR;
137 return;
138 end;
139
140
141 l_sql_stmt := 'UPDATE iex_delinquencies_all SET unpaid_reason_code = :b_unpaid_reason ' ||
142 ' ,last_update_date = to_date(''' || sysdate || ''' , ''DD-MON-RR'')' ||
143 ' ,last_update_login = ' || l_last_update_login ||
144 ' , last_updated_by =' || l_last_updated_by ||
145 ' WHERE delinquency_id IN (' || p_del_ids || ')';
146
147 iex_debug_pub.LogMessage('l_sql_stmt=' || l_sql_stmt);
148
149 l_cursor_id := DBMS_SQL.OPEN_CURSOR;
150 DBMS_SQL.PARSE(l_cursor_id, l_sql_stmt, 1);
151 DBMS_SQL.BIND_VARIABLE(l_cursor_id, 'b_unpaid_reason', p_unpaid_reason);
152 x_rows_processed := DBMS_SQL.EXECUTE(l_cursor_id);
153 DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
154
155 -- Standard check of p_commit
156 IF FND_API.To_Boolean(p_commit) THEN
157 COMMIT WORK;
158 END IF;
159
160 -- Standard call to get message count and if count is 1, get message info
161 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
162
163 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ':end');
164 EXCEPTION
165 WHEN FND_API.G_EXC_ERROR THEN
166 ROLLBACK TO Set_Unpaid_Reason_PUB;
167 x_return_status := FND_API.G_RET_STS_ERROR;
168 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
169
170 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
171 ROLLBACK TO Set_Unpaid_Reason_PUB;
172 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
173 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
174
175 WHEN OTHERS THEN
176 ROLLBACK TO Set_Unpaid_Reason_PUB;
177 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
178 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
179 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
180 END IF;
181 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
182 END Set_Unpaid_Reason;
183 BEGIN
184 PG_DEBUG := TO_NUMBER(NVL(FND_PROFILE.value('IEX_DEBUG_LEVEL'), '20'));
185 G_APPL_ID := FND_GLOBAL.Prog_Appl_Id;
186 G_LOGIN_ID := FND_GLOBAL.Conc_Login_Id;
187 G_PROGRAM_ID := FND_GLOBAL.Conc_Program_Id;
188 G_USER_ID := FND_GLOBAL.User_Id;
189 G_REQUEST_ID := FND_GLOBAL.Conc_Request_Id;
190 END IEX_TRX_GRID_PUB;