1 PACKAGE BODY IEX_ROUTING_PVT AS
2 /* $Header: iexvroub.pls 120.0 2004/01/24 03:28:34 appldev noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IEX_ROUTING_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'iexvroub.pls';
6 G_APPL_ID NUMBER := FND_GLOBAL.Prog_Appl_Id;
7 G_LOGIN_ID NUMBER := FND_GLOBAL.Conc_Login_Id;
8 G_PROGRAM_ID NUMBER := FND_GLOBAL.Conc_Program_Id;
9 G_USER_ID NUMBER := FND_GLOBAL.User_Id;
10 G_REQUEST_ID NUMBER := FND_GLOBAL.Conc_Request_Id;
11
12 PG_DEBUG NUMBER(2) := TO_NUMBER(NVL(FND_PROFILE.value('IEX_DEBUG_LEVEL'), '20'));
13
14 PROCEDURE isCustomerOverdue ( p_api_version in number,
15 p_init_msg_list in varchar2 default fnd_api.g_false,
16 p_commit in varchar2 default fnd_api.g_false,
17 p_validation_level in number default fnd_api.g_valid_level_full,
18 x_return_status out NOCOPY varchar2,
19 x_msg_count out NOCOPY number,
20 x_msg_data out NOCOPY varchar2,
21 p_customer_id in number,
22 p_customer_overdue out NOCOPY boolean)
23
24 AS
25 l_api_name constant varchar2(30) := 'isCustomerOverdue';
26 l_api_version constant number := 1.0;
27 l_blank_chr varchar2(1) := FND_API.G_MISS_CHAR;
28 i number := 0;
29
30 /* Begin - Andre Araujo - Change the test to iex_delinquencies
31 CURSOR c_CustomerOverdue IS
32 SELECT customer_number
33 ,customer_id
34 ,dso
35 ,pastdue_invoices
36 ,pastdue_balance
37 FROM ar_customer_accounts
38 WHERE customer_id = p_customer_id
39 AND acctd_or_entered = 'A'
40 AND customer_or_location = 'C';
41 */
42
43
44 BEGIN
45 /* Standard start of API savepoint */
46 SAVEPOINT IEX_ROUTING_PVT;
47
48 /* Standard call to check for call compatibility */
49 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
50 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
51 END IF;
52
53 /* Initialize message list if p_init_msg_list is set to TRUE */
54 IF FND_API.To_Boolean(p_init_msg_list) THEN
55 FND_MSG_PUB.initialize;
56 END IF;
57
58 /* Initialize API return status to success */
59
60 x_return_status := FND_API.G_RET_STS_SUCCESS;
61
62 /* API body */
63 /* Begin - Andre Araujo - Adapt the api to iex_delinquencies_all
64 i := 0;
65 p_customer_overdue := FALSE;
66
67 FOR v_isOverdue IN c_CustomerOverdue
68 LOOP
69 i := i + 1;
70 if (v_isOverdue.pastdue_invoices > 0) or (v_isOverdue.pastdue_balance > 0) then
71 p_customer_overdue := TRUE;
72 end if;
73 END LOOP;
74 */
75 SELECT count(1)
76 into i
77 FROM iex_delinquencies_all
78 WHERE party_cust_id = p_customer_id;
79
80 if(i > 0) then
81 p_customer_overdue := TRUE;
82 end if;
83
84 /* End of API body */
85
86 /* Standard check of p_commit */
87 IF FND_API.To_Boolean(p_commit) THEN
88 COMMIT WORK;
89 END IF;
90
91 /* Standard call to get message count and if count is 1, get message info */
92 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
93 EXCEPTION
94 WHEN FND_API.G_EXC_ERROR THEN
95 ROLLBACK TO IEX_ROUTING_PVT;
96 x_return_status := FND_API.G_RET_STS_ERROR;
97 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
98 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
99 ROLLBACK TO IEX_ROUTING_PVT;
100 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
101 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
102 WHEN OTHERS THEN
103 ROLLBACK TO IEX_ROUTING_PVT;
104 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
105 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
106 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
107 END IF;
108 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
109
110 END isCustomerOverdue;
111
112 PROCEDURE getCollectors ( p_api_version in number,
113 p_init_msg_list in varchar2 default fnd_api.g_false,
114 p_commit in varchar2 default fnd_api.g_false,
115 p_validation_level in number default fnd_api.g_valid_level_full,
116 x_return_status out NOCOPY varchar2,
117 x_msg_count out NOCOPY number,
118 x_msg_data out NOCOPY varchar2,
119 p_customer_id in number,
120 p_collectors out NOCOPY iex_collectors_tbl_type)
121
122 AS
123
124 l_api_name constant varchar2(30) := 'getCollectors';
125 l_api_version constant number := 1.0;
126 l_blank_chr varchar2(1) := FND_API.G_MISS_CHAR;
127 i number := 0;
128
129 CURSOR c_Collectors IS
130 select distinct collector_id
131 from jtf_customer_profiles_v prof, jtf_cust_accounts_all_v acct
132 where acct.party_id = p_customer_id
133 and prof.cust_account_id = acct.cust_account_id;
134
135 BEGIN
136 /* Standard start of API savepoint */
137 SAVEPOINT IEX_ROUTING_PVT;
138
139 /* Standard call to check for call compatibility */
140 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
141 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
142 END IF;
143
144 /* Initialize message list if p_init_msg_list is set to TRUE */
145 IF FND_API.To_Boolean(p_init_msg_list) THEN
146 FND_MSG_PUB.initialize;
147 END IF;
148
149 /* Initialize API return status to success */
150
151 x_return_status := FND_API.G_RET_STS_SUCCESS;
152
153 /* API body */
154 i := 0;
155 FOR v_Collectors IN c_Collectors
156 LOOP
157 i := i + 1;
158 p_collectors(i) := v_Collectors.collector_id;
159 END LOOP;
160
161 /* End of API body */
162
163 /* Standard check of p_commit */
164 IF FND_API.To_Boolean(p_commit) THEN
165 COMMIT WORK;
166 END IF;
167
168 /* Standard call to get message count and if count is 1, get message info */
169 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
170 EXCEPTION
171 WHEN FND_API.G_EXC_ERROR THEN
172 ROLLBACK TO IEX_ROUTING_PVT;
173 x_return_status := FND_API.G_RET_STS_ERROR;
174 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
175 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
176 ROLLBACK TO IEX_ROUTING_PVT;
177 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
178 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
179 WHEN OTHERS THEN
180 ROLLBACK TO IEX_ROUTING_PVT;
181 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
182 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
183 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
184 END IF;
185 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
186
187 END getCollectors;
188
189 END IEX_ROUTING_PVT;
[Home] [Help]