[Home] [Help]
PACKAGE BODY: APPS.ARP_TMP_PKG
Source
1 PACKAGE BODY ARP_TMP_PKG AS
2 /* $Header: ARTMPB.pls 120.0 2005/12/23 02:34:04 hyu noship $ */
3
4
5 PROCEDURE workaround_remit_loc_api
6 ( p_location_id IN NUMBER DEFAULT NULL,
7 p_country IN VARCHAR2 DEFAULT NULL,
8 p_ADDRESS1 IN VARCHAR2 DEFAULT NULL,
9 p_CITY IN VARCHAR2 DEFAULT NULL,
10 p_POSTAL_CODE IN VARCHAR2 DEFAULT NULL,
11 p_STATE IN VARCHAR2 DEFAULT NULL,
12 p_PROVINCE IN VARCHAR2 DEFAULT NULL,
13 p_COUNTY IN VARCHAR2 DEFAULT NULL,
14 p_org_id IN NUMBER,
15 x_cust_acct_site_id OUT NOCOPY NUMBER,
16 x_party_site_id OUT NOCOPY NUMBER,
17 x_return_status OUT NOCOPY VARCHAR2,
18 x_msg_data OUT NOCOPY VARCHAR2,
19 x_msg_count OUT NOCOPY NUMBER)
20 IS
21 CURSOR c_loc_1 IS
22 SELECT 'A' FROM hz_locations WHERE location_id = p_location_id;
23
24 CURSOR c_loc_2 IS
25 SELECT location_id FROM hz_locations
26 WHERE country = p_country
27 AND NVL(p_ADDRESS1,'XXX') = NVL(address1,'XXX')
28 AND NVL(p_CITY ,'XXX') = NVL(city,'XXX')
29 AND NVL(p_POSTAL_CODE,'XXX') = NVL(postal_code,'XXX')
30 AND NVL(p_STATE,'XXX') = NVL(state,'XXX')
31 AND NVL(p_PROVINCE,'XXX') = NVL(province,'XXX')
32 AND NVL(p_COUNTY,'XXX') = NVL(county,'XXX');
33
34 l_loc_id_tab DBMS_SQL.NUMBER_TABLE;
35
36 l_loc_id NUMBER;
37
38 psite_rec hz_party_site_v2pub.party_site_rec_type;
39 asite_rec hz_cust_account_site_v2pub.cust_acct_site_rec_type;
40
41 -- x_party_site_id NUMBER;
42 x_party_site_number VARCHAR2(30);
43 l_exist VARCHAR2(10);
44 -- x_cust_acct_site_id NUMBER;
45 BEGIN
46 x_return_status := fnd_api.G_RET_STS_SUCCESS;
47
48 IF p_location_id IS NOT NULL THEN
49
50 OPEN c_loc_1;
51 FETCH c_loc_1 INTO l_exist;
52 IF c_loc_1%NOTFOUND THEN
53 x_return_status := fnd_api.g_ret_sts_error;
54 END IF;
55 CLOSE c_loc_1;
56
57 IF x_return_status = fnd_api.g_ret_sts_error THEN
58 FND_MESSAGE.SET_NAME('AR', 'AR_CUST_API_ERROR');
59 FND_MESSAGE.SET_TOKEN('TEXT','Location with the location_id:'||p_location_id||' does not exist');
60 FND_MSG_PUB.ADD;
61 RAISE FND_API.G_EXC_ERROR;
62 END IF;
63
64 l_loc_id := p_location_id;
65
66 ELSIF p_country IS NULL THEN
67
68 x_return_status := fnd_api.g_ret_sts_error;
69
70 IF x_return_status = fnd_api.g_ret_sts_error THEN
71 FND_MESSAGE.SET_NAME('AR', 'AR_CUST_API_ERROR');
72 FND_MESSAGE.SET_TOKEN('TEXT','Country is a mandatory field if you do not pass the location_id');
73 FND_MSG_PUB.ADD;
74 RAISE FND_API.G_EXC_ERROR;
75 END IF;
76
77 ELSIF (p_ADDRESS1 IS NULL AND
78 p_CITY IS NULL AND
79 p_POSTAL_CODE IS NULL AND
80 p_STATE IS NULL AND
81 p_PROVINCE IS NULL AND
82 p_COUNTY IS NULL )
83 THEN
84 x_return_status := fnd_api.g_ret_sts_error;
85
86 IF x_return_status = fnd_api.g_ret_sts_error THEN
87 FND_MESSAGE.SET_NAME('AR', 'AR_CUST_API_ERROR');
88 FND_MESSAGE.SET_TOKEN('TEXT',
89 'Please enter either a p_location_id or address elements like address1 ...');
90 FND_MSG_PUB.ADD;
91 fnd_msg_pub.count_and_get(
92 p_encoded => fnd_api.g_false,
93 p_count => x_msg_count,
94 p_data => x_msg_data);
95 RAISE FND_API.G_EXC_ERROR;
96 END IF;
97
98 ELSE
99 OPEN c_loc_2;
100 FETCH c_loc_2 BULK COLLECT INTO l_loc_id_tab;
101 CLOSE c_loc_2;
102
103 IF l_loc_id_tab.COUNT = 0 THEN
104
105 x_return_status := fnd_api.g_ret_sts_error;
106
107 IF x_return_status = fnd_api.g_ret_sts_error THEN
108 FND_MESSAGE.SET_NAME('AR', 'AR_CUST_API_ERROR');
109 FND_MESSAGE.SET_TOKEN('TEXT',
110 'The combination of address elements you have provided does not correspond to any location in the DB');
111 FND_MSG_PUB.ADD;
112 fnd_msg_pub.count_and_get(
113 p_encoded => fnd_api.g_false,
114 p_count => x_msg_count,
115 p_data => x_msg_data);
116 RAISE FND_API.G_EXC_ERROR;
117 END IF;
118
119 ELSIF l_loc_id_tab.COUNT > 1 THEN
120
121 x_return_status := fnd_api.g_ret_sts_error;
122
123 IF x_return_status = fnd_api.g_ret_sts_error THEN
124 FND_MESSAGE.SET_NAME('AR', 'AR_CUST_API_ERROR');
125 FND_MESSAGE.SET_TOKEN('TEXT',
126 'The combination of address elements you have provided correspond to more than one location in the DB');
127 FND_MSG_PUB.ADD;
128 fnd_msg_pub.count_and_get(
129 p_encoded => fnd_api.g_false,
130 p_count => x_msg_count,
131 p_data => x_msg_data);
132 RAISE FND_API.G_EXC_ERROR;
133 END IF;
134
135 ELSE
136 l_loc_id := l_loc_id_tab(1);
137
138 END IF;
139
140 END IF;
141
142
143 -- Create party_site
144 psite_rec.party_id := -1;
145 psite_rec.location_id := l_loc_id;
146 psite_rec.created_by_module := 'TCA_FORM_WRAPPER';
147
148
149 HZ_PARTY_SITE_V2PUB.create_party_site (
150 p_party_site_rec => psite_rec,
151 x_party_site_id => x_party_site_id,
152 x_party_site_number => x_party_site_number,
153 x_return_status => x_return_status,
154 x_msg_count => x_msg_count,
155 x_msg_data => x_msg_data );
156
157 IF x_return_status <> fnd_api.g_ret_sts_success THEN
158 RAISE FND_API.G_EXC_ERROR;
159 END IF;
160
161
162 -- Create account site
163 asite_rec.cust_account_id := -1;
164 asite_rec.party_site_id := x_Party_site_id;
165 asite_rec.status := 'A';
166 asite_rec.created_by_module := 'TCA_FORM_WRAPPER';
167 asite_rec.ORG_ID := p_ORG_ID;
168
169
170 HZ_CUST_ACCOUNT_SITE_V2PUB.create_cust_acct_site (
171 p_cust_acct_site_rec => asite_rec,
172 x_cust_acct_site_id => x_cust_acct_site_id,
173 x_return_status => x_return_status,
174 x_msg_count => x_msg_count,
175 x_msg_data => x_msg_data );
176
177 IF x_return_status <> fnd_api.g_ret_sts_success THEN
178 RAISE FND_API.G_EXC_ERROR;
179 END IF;
180
181 EXCEPTION
182
183 WHEN FND_API.G_EXC_ERROR THEN
184 x_return_status := fnd_api.g_ret_sts_error;
185 WHEN OTHERS THEN
186 RAISE;
187
188 END;
189
190 END;