[Home] [Help]
PACKAGE BODY: APPS.RCV_UTILITIES
Source
1 PACKAGE BODY RCV_UTILITIES AS
2 /* $Header: RCVUTILB.pls 120.1.12010000.4 2010/01/25 23:40:36 vthevark noship $*/
3
4 -- Read the profile option that enables/disables the debug log
5 g_asn_debug VARCHAR2(1) := asn_debug.is_debug_on; -- Bug 9152790
6
7 /* This API is called by AP in their Supplier Merge program */
8
9 PROCEDURE Merge_Vendor
10 ( p_commit IN VARCHAR2 default FND_API.G_FALSE,
11 x_return_status OUT NOCOPY VARCHAR2,
12 x_msg_count OUT NOCOPY NUMBER,
13 x_msg_data OUT NOCOPY VARCHAR2,
14 p_vendor_id IN NUMBER,
15 p_vendor_site_id IN NUMBER,
16 p_dup_vendor_id IN NUMBER,
17 p_dup_vendor_site_id IN NUMBER
18 ) IS
19
20 l_last_updated_by NUMBER;
21
22 BEGIN
23
24 IF (g_asn_debug = 'Y') THEN
25 asn_debug.put_line('p_vendor_id: ' || p_vendor_id);
26 asn_debug.put_line('p_vendor_site_id ' || p_vendor_site_id);
27 asn_debug.put_line('p_dup_vendor_id ' || p_dup_vendor_id);
28 asn_debug.put_line('p_dup_vendor_site_id ' || p_dup_vendor_site_id);
29 END IF;
30
31 -- Initialize API return status to success
32 x_return_status := FND_API.G_RET_STS_SUCCESS;
33 l_last_updated_by := FND_GLOBAL.user_id;
34
35 /* Per discussion with AP:
36 - value of message count should be 0 and data should be null
37 FND_MSG_PUB.cound_and_get should not be called to fetch the values of count and data.
38 - No RCV specific messages will be returned from this api
39 */
40
41 x_msg_count := 0;
42 x_msg_data := null;
43
44 -- Updating rcv_shipment_headers
45 UPDATE rcv_shipment_headers
46 SET vendor_id = p_vendor_id,
47 vendor_site_id = p_vendor_site_id,
48 last_updated_by = l_last_updated_by,
49 last_update_date = sysdate
50 WHERE receipt_source_code = 'VENDOR'
51 AND vendor_id = p_dup_vendor_id
52 AND vendor_site_id is not null
53 AND vendor_site_id = p_dup_vendor_site_id ;
54
55 UPDATE rcv_shipment_headers
56 SET vendor_id = p_vendor_id,
57 last_updated_by = l_last_updated_by,
58 last_update_date = sysdate
59 WHERE receipt_source_code = 'VENDOR'
60 AND vendor_id = p_dup_vendor_id
61 AND vendor_site_id is null;
62
63 IF (g_asn_debug = 'Y') THEN
64 asn_debug.put_line('Updating rcv_shipment_headers: count =' || sql%rowcount);
65 END IF;
66
67 -- Updating rcv_transactions
68 UPDATE rcv_transactions
69 SET vendor_id = p_vendor_id,
70 vendor_site_id = p_vendor_site_id,
71 last_updated_by = l_last_updated_by,
72 last_update_date = sysdate
73 WHERE source_document_code = 'PO'
74 AND vendor_id = p_dup_vendor_id
75 AND vendor_site_id = p_dup_vendor_site_id ;
76
77 IF (g_asn_debug = 'Y') THEN
78 asn_debug.put_line('After updating rcv_transactions: count =' || sql%rowcount);
79 END IF;
80
81 -- Updating rcv_headers_interface
82 UPDATE rcv_headers_interface
83 SET vendor_id = p_vendor_id,
84 vendor_site_id = p_vendor_site_id,
85 last_updated_by = l_last_updated_by,
86 last_update_date = sysdate
87 WHERE receipt_source_code = 'VENDOR'
88 AND vendor_id = p_dup_vendor_id
89 AND vendor_site_id is not null
90 AND vendor_site_id = p_dup_vendor_site_id ;
91
92 UPDATE rcv_headers_interface
93 SET vendor_id = p_vendor_id,
94 last_updated_by = l_last_updated_by,
95 last_update_date = sysdate
96 WHERE receipt_source_code = 'VENDOR'
97 AND vendor_id = p_dup_vendor_id
98 AND vendor_site_id is null;
99
100 IF (g_asn_debug = 'Y') THEN
101 asn_debug.put_line('After updating rcv_headers_interface: count =' || sql%rowcount);
102 END IF;
103
104 -- Updating rcv_transactions_interface
105 UPDATE rcv_transactions_interface
106 SET vendor_id = p_vendor_id,
107 vendor_site_id = p_vendor_site_id,
108 last_updated_by = l_last_updated_by,
109 last_update_date = sysdate
110 WHERE source_document_code = 'PO'
111 AND vendor_id = p_dup_vendor_id
112 AND vendor_site_id is not null
113 AND vendor_site_id = p_dup_vendor_site_id ;
114
115 UPDATE rcv_transactions_interface
116 SET vendor_id = p_vendor_id,
117 last_updated_by = l_last_updated_by,
118 last_update_date = sysdate
119 WHERE source_document_code = 'PO'
120 AND vendor_id = p_dup_vendor_id
121 AND vendor_site_id is null;
122
123 IF (g_asn_debug = 'Y') THEN
124 asn_debug.put_line('After updating rcv_transactions_interface: count =' || sql%rowcount);
125 END IF;
126
127 IF (g_asn_debug = 'Y') THEN
128 asn_debug.put_line('x_return_status: ' || x_return_status);
129 asn_debug.put_line('x_msg_count: ' || x_msg_count);
130 asn_debug.put_line('x_msg_data: ' || x_msg_data);
131 END IF;
132
133 IF FND_API.To_Boolean( p_commit ) THEN
134 COMMIT WORK;
135 END IF;
136
137 EXCEPTION
138 WHEN OTHERS THEN
139 ROLLBACK;
140 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
141
142 IF (g_asn_debug = 'Y') THEN
143 asn_debug.put_line('x_return_status: ' || x_return_status);
144 asn_debug.put_line('x_msg_count: ' || x_msg_count);
145 asn_debug.put_line('x_msg_data: ' || x_msg_data);
146 END IF;
147
148 END Merge_Vendor;
149
150
151 -- Bug 7579045: This API is used by AP for AP-LCM integration.
152 PROCEDURE Get_RtLcmInfo
153 ( p_rcv_transaction_id IN NUMBER,
154 x_lcm_account_id OUT NOCOPY NUMBER,
155 x_tax_variance_account_id OUT NOCOPY NUMBER,
156 x_def_charges_account_id OUT NOCOPY NUMBER,
157 x_exchange_variance_account_id OUT NOCOPY NUMBER,
158 x_inv_variance_account_id OUT NOCOPY NUMBER
159 ) IS
160
161 x_progress VARCHAR2(3) := '000';
162 x_pll_id NUMBER;
163 x_org_id NUMBER;
164 x_lcm_flag VARCHAR2(1);
165
166 BEGIN
167 IF (g_asn_debug = 'Y') THEN
168 asn_debug.put_line('In Get_RtLcmInfo: p_rcv_transaction_id' || p_rcv_transaction_id);
169 END IF;
170
171 SELECT rt.po_line_location_id,
172 rt.organization_id,
173 nvl(pll.lcm_flag,'N')
174 INTO x_pll_id,
175 x_org_id,
176 x_lcm_flag
177 FROM rcv_transactions rt,
178 po_line_locations_all pll
179 WHERE rt.po_line_location_id is not null
180 AND rt.po_line_location_id = pll.line_location_id
181 AND transaction_id = p_rcv_transaction_id;
182
183 IF (g_asn_debug = 'Y') THEN
184 asn_debug.put_line('x_progress' || x_progress);
185 asn_debug.put_line('x_pll_id' || x_pll_id);
186 asn_debug.put_line('x_org_id' || x_org_id);
187 asn_debug.put_line('x_lcm_flag' || x_lcm_flag);
188 END IF;
189
190 x_progress := '010';
191
192 IF (x_lcm_flag = 'Y') THEN
193 SELECT lcm_account_id,
194 tax_variance_account_id,
195 def_charges_account_id,
196 exchange_variance_account_id,
197 inv_variance_account_id
198 INTO x_lcm_account_id,
199 x_tax_variance_account_id,
200 x_def_charges_account_id,
201 x_exchange_variance_account_id,
202 x_inv_variance_account_id
203 FROM rcv_parameters
204 WHERE organization_id = x_org_id;
205 END IF;
206
207 IF (g_asn_debug = 'Y') THEN
208 asn_debug.put_line('x_lcm_account_id' || x_lcm_account_id);
209 asn_debug.put_line('x_tax_variance_account_id' || x_tax_variance_account_id);
210 asn_debug.put_line('x_def_charges_account_id' || x_def_charges_account_id);
211 asn_debug.put_line('x_exchange_variance_account_id' || x_exchange_variance_account_id);
212 asn_debug.put_line('x_inv_variance_account_id' || x_inv_variance_account_id);
213 END IF;
214
215 EXCEPTION
216 WHEN OTHERS THEN
217 x_lcm_account_id := null;
218 x_tax_variance_account_id := null;
219 x_def_charges_account_id := null;
220 x_exchange_variance_account_id := null;
221 x_inv_variance_account_id := null;
222
223 IF (g_asn_debug = 'Y') THEN
224 asn_debug.put_line('Error in Get_RtLcmInfo: ' || x_progress);
225 END IF;
226 END Get_RtLcmInfo;
227
228 END RCV_UTILITIES;