[Home] [Help]
PACKAGE BODY: APPS.ITG_SYNCSUPPLIERINBOUND_PVT
Source
1 PACKAGE BODY ITG_SyncSupplierInbound_PVT AS
2 /* ARCS: $Header: itgvssib.pls 120.16 2006/08/31 06:47:44 pvaddana noship $
3 * CVS: itgvssib.pls,v 1.32 2003/01/24 22:09:33 ecoe Exp
4 */
5
6 l_debug_level NUMBER := to_number(nvl(fnd_profile.value('CLN_DEBUG_LEVEL'), '5'));
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ITG_SyncSupplierInbound_PVT';
8 l_address_style varchar2(20);
9 g_action VARCHAR2(100);
10
11
12 -- replace * with column listing
13 PROCEDURE get_vendorsite_rec(
14 x IN OUT NOCOPY AP_VENDOR_PUB_PKG.r_vendor_site_rec_type,
15 p_vendorsite_id IN NUMBER,
16 p_vendor_id IN NUMBER) IS
17 BEGIN
18
19 g_action := 'vendor site details lookup';
20 IF l_debug_level <= 1 then
21 itg_debug_pub.add('Entering get_vendorsite_rec');
22 itg_debug_pub.add('p_vendorsite_id ' || p_vendorsite_id);
23 itg_debug_pub.add('p_vendor_id ' || p_vendor_id);
24 END IF;
25
26 SELECT
27 VENDOR_SITE_ID, VENDOR_ID, VENDOR_SITE_CODE,
28 ATTRIBUTE_CATEGORY, ATTRIBUTE1, ATTRIBUTE2,
29 ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5,
30 ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8,
31 ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11,
32 ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14,
33 ATTRIBUTE15, SHIP_TO_LOCATION_ID, SHIP_TO_LOCATION_CODE,
34 BILL_TO_LOCATION_ID, BILL_TO_LOCATION_CODE, SHIP_VIA_LOOKUP_CODE,
35 ADDRESS_LINE1, ADDRESS_LINE2,
36 ADDRESS_LINE3, ADDRESS_LINE4, CITY,
37 COUNTY, STATE, ZIP, COUNTRY, PROVINCE, AREA_CODE, PHONE,
38 TELEX, FAX_AREA_CODE, FAX, LANGUAGE, INACTIVE_DATE,
39 PURCHASING_SITE_FLAG, PAY_SITE_FLAG, RFQ_ONLY_SITE_FLAG,
40 FOB_LOOKUP_CODE, FREIGHT_TERMS_LOOKUP_CODE, DISTRIBUTION_SET_ID, DISTRIBUTION_SET_NAME,
41 ACCTS_PAY_CCID, PREPAY_CCID, ALWAYS_TAKE_DISC_FLAG,
42 ATTENTION_AR_FLAG, PAY_DATE_BASIS_LOOKUP_CODE, PAY_GROUP_LOOKUP_CODE,
43 HOLD_FUTURE_PAYMENTS_FLAG, HOLD_ALL_PAYMENTS_FLAG,
44 HOLD_REASON, TERMS_DATE_BASIS, TAX_REPORTING_SITE_FLAG,
45 TERMS_ID, TERMS_NAME, EXCLUDE_FREIGHT_FROM_DISC,
46 HOLD_UNMATCHED_INV_FLAG, INVOICE_AMOUNT_LIMIT, CUSTOMER_NUM,
47 PAYMENT_CURRENCY_CODE, PAYMENT_PRIORITY, INVOICE_CURRENCY_CODE,
48 AWT_GROUP_ID, AWT_GROUP_NAME, ALLOW_AWT_FLAG,
49 VALIDATION_NUMBER, CHECK_DIGITS, ADDRESS_STYLE, PAY_ON_CODE,
50 DEFAULT_PAY_SITE_ID, PAY_ON_RECEIPT_SUMMARY_CODE, VENDOR_SITE_CODE_ALT, ADDRESS_LINES_ALT,
51 BANK_CHARGE_BEARER, TP_HEADER_ID, GLOBAL_ATTRIBUTE_CATEGORY,
52 GLOBAL_ATTRIBUTE1, GLOBAL_ATTRIBUTE2, GLOBAL_ATTRIBUTE3,
53 GLOBAL_ATTRIBUTE4, GLOBAL_ATTRIBUTE5, GLOBAL_ATTRIBUTE6,
54 GLOBAL_ATTRIBUTE7, GLOBAL_ATTRIBUTE8, GLOBAL_ATTRIBUTE9,
55 GLOBAL_ATTRIBUTE10, GLOBAL_ATTRIBUTE11, GLOBAL_ATTRIBUTE12,
56 GLOBAL_ATTRIBUTE13, GLOBAL_ATTRIBUTE14, GLOBAL_ATTRIBUTE15,
57 GLOBAL_ATTRIBUTE16, GLOBAL_ATTRIBUTE17, GLOBAL_ATTRIBUTE18,
58 GLOBAL_ATTRIBUTE19, GLOBAL_ATTRIBUTE20, ECE_TP_LOCATION_CODE,
59 PCARD_SITE_FLAG, MATCH_OPTION, FUTURE_DATED_PAYMENT_CCID,
60 CREATE_DEBIT_MEMO_FLAG, COUNTRY_OF_ORIGIN_CODE, SUPPLIER_NOTIF_METHOD, EMAIL_ADDRESS,
61 PRIMARY_PAY_SITE_FLAG, ORG_ID
62 INTO
63 x.VENDOR_SITE_ID, x.VENDOR_ID, x.VENDOR_SITE_CODE,
64 x.ATTRIBUTE_CATEGORY, x.ATTRIBUTE1, x.ATTRIBUTE2,
65 x.ATTRIBUTE3, x.ATTRIBUTE4, x.ATTRIBUTE5,
66 x.ATTRIBUTE6, x.ATTRIBUTE7, x.ATTRIBUTE8,
67 x.ATTRIBUTE9, x.ATTRIBUTE10, x.ATTRIBUTE11,
68 x.ATTRIBUTE12, x.ATTRIBUTE13, x.ATTRIBUTE14,
69 x.ATTRIBUTE15, x.SHIP_TO_LOCATION_ID, x.SHIP_TO_LOCATION_CODE,
70 x.BILL_TO_LOCATION_ID, x.BILL_TO_LOCATION_CODE, x.SHIP_VIA_LOOKUP_CODE,
71 x.ADDRESS_LINE1, x.ADDRESS_LINE2,
72 x.ADDRESS_LINE3, x.ADDRESS_LINE4, x.CITY,
73 x.COUNTY, x.STATE, x.ZIP, x.COUNTRY, x.PROVINCE, x.AREA_CODE, x.PHONE,
74 x.TELEX, x.FAX_AREA_CODE, x.FAX, x.LANGUAGE, x.INACTIVE_DATE,
75 x.PURCHASING_SITE_FLAG, x.PAY_SITE_FLAG, x.RFQ_ONLY_SITE_FLAG,
76 x.FOB_LOOKUP_CODE, x.FREIGHT_TERMS_LOOKUP_CODE,x.DISTRIBUTION_SET_ID, x.DISTRIBUTION_SET_NAME,
77 x.ACCTS_PAY_CODE_COMBINATION_ID, x.PREPAY_CODE_COMBINATION_ID, x.ALWAYS_TAKE_DISC_FLAG,
78 x.ATTENTION_AR_FLAG, x.PAY_DATE_BASIS_LOOKUP_CODE, x.PAY_GROUP_LOOKUP_CODE,
79 x.HOLD_FUTURE_PAYMENTS_FLAG, x.HOLD_ALL_PAYMENTS_FLAG,
80 x.HOLD_REASON, x.TERMS_DATE_BASIS, x.TAX_REPORTING_SITE_FLAG,
81 x.TERMS_ID, x.TERMS_NAME, x.EXCLUDE_FREIGHT_FROM_DISCOUNT,
82 x.HOLD_UNMATCHED_INVOICES_FLAG, x.INVOICE_AMOUNT_LIMIT, x.CUSTOMER_NUM,
83 x.PAYMENT_CURRENCY_CODE, x.PAYMENT_PRIORITY, x.INVOICE_CURRENCY_CODE,
84 x.AWT_GROUP_ID, x.AWT_GROUP_NAME, x.ALLOW_AWT_FLAG,
85 x.VALIDATION_NUMBER, x.CHECK_DIGITS, x.ADDRESS_STYLE, x.PAY_ON_CODE,
86 x.DEFAULT_PAY_SITE_ID, x.PAY_ON_RECEIPT_SUMMARY_CODE, x.VENDOR_SITE_CODE_ALT, x.ADDRESS_LINES_ALT,
87 x.BANK_CHARGE_BEARER, x.TP_HEADER_ID, x.GLOBAL_ATTRIBUTE_CATEGORY,
88 x.GLOBAL_ATTRIBUTE1, x.GLOBAL_ATTRIBUTE2, x.GLOBAL_ATTRIBUTE3,
89 x.GLOBAL_ATTRIBUTE4, x.GLOBAL_ATTRIBUTE5, x.GLOBAL_ATTRIBUTE6,
90 x.GLOBAL_ATTRIBUTE7, x.GLOBAL_ATTRIBUTE8, x.GLOBAL_ATTRIBUTE9,
91 x.GLOBAL_ATTRIBUTE10, x.GLOBAL_ATTRIBUTE11, x.GLOBAL_ATTRIBUTE12,
92 x.GLOBAL_ATTRIBUTE13, x.GLOBAL_ATTRIBUTE14, x.GLOBAL_ATTRIBUTE15,
93 x.GLOBAL_ATTRIBUTE16, x.GLOBAL_ATTRIBUTE17, x.GLOBAL_ATTRIBUTE18,
94 x.GLOBAL_ATTRIBUTE19, x.GLOBAL_ATTRIBUTE20, x.ECE_TP_LOCATION_CODE,
95 x.PCARD_SITE_FLAG, x.MATCH_OPTION, x.FUTURE_DATED_PAYMENT_CCID,
96 x.CREATE_DEBIT_MEMO_FLAG, x.COUNTRY_OF_ORIGIN_CODE, x.SUPPLIER_NOTIF_METHOD, x.EMAIL_ADDRESS,
97 x.PRIMARY_PAY_SITE_FLAG,x.ORG_ID
98 FROM AP_VENDOR_SITES_V
99 WHERE vendor_id = p_vendor_id
100 and vendor_site_id = p_vendorsite_id;
101
102 IF l_debug_level <= 1 then
103 itg_debug_pub.add('Exiting get_vendorsite_rec normal');
104 END IF;
105 EXCEPTION
106 WHEN NO_DATA_FOUND THEN
107 itg_msg.no_vendor_site(p_vendorsite_id);
108 RAISE FND_API.G_EXC_ERROR;
109 WHEN OTHERS THEN
110 IF l_debug_level <= 1 THEN
111 itg_debug_pub.add('Error in get_vendorsite_rec ' || SQLCODE || ' - ' || SQLERRM,1);
112 END IF;
113 RAISE;
114 END;
115
116 -- replace * with column listing
117 PROCEDURE get_vendor_rec(
118 x IN OUT NOCOPY AP_VENDOR_PUB_PKG.r_vendor_rec_type,
119 p_vendor_id IN NUMBER) IS
120 BEGIN
121 g_action := 'vendor details lookup';
122 IF l_debug_level <= 1 then
123 itg_debug_pub.add('Entering get_vendor_rec');
124 itg_debug_pub.add('p_vendor_id ' || p_vendor_id);
125 END IF;
126
127 SELECT
128 VENDOR_ID, VENDOR_NAME, VENDOR_NAME_ALT, SUMMARY_FLAG, ENABLED_FLAG, EMPLOYEE_ID,
129 VENDOR_TYPE_LOOKUP_CODE, CUSTOMER_NUM, ONE_TIME_FLAG, PARENT_VENDOR_ID, MIN_ORDER_AMOUNT,
130 TERMS_ID, SET_OF_BOOKS_ID, ALWAYS_TAKE_DISC_FLAG, PAY_DATE_BASIS_LOOKUP_CODE,
131 PAY_GROUP_LOOKUP_CODE, PAYMENT_PRIORITY, INVOICE_CURRENCY_CODE, PAYMENT_CURRENCY_CODE,
132 INVOICE_AMOUNT_LIMIT, HOLD_ALL_PAYMENTS_FLAG, HOLD_FUTURE_PAYMENTS_FLAG, HOLD_REASON,
133 TYPE_1099, WITHHOLDING_STATUS_LOOKUP_CODE, WITHHOLDING_START_DATE,
134 ORGANIZATION_TYPE_LOOKUP_CODE, START_DATE_ACTIVE, END_DATE_ACTIVE,
135 MINORITY_GROUP_LOOKUP_CODE, WOMEN_OWNED_FLAG, SMALL_BUSINESS_FLAG, HOLD_FLAG,
136 PURCHASING_HOLD_REASON, HOLD_BY, HOLD_DATE, TERMS_DATE_BASIS, INSPECTION_REQUIRED_FLAG,
137 RECEIPT_REQUIRED_FLAG, QTY_RCV_TOLERANCE, QTY_RCV_EXCEPTION_CODE,
138 ENFORCE_SHIP_TO_LOCATION_CODE, DAYS_EARLY_RECEIPT_ALLOWED, DAYS_LATE_RECEIPT_ALLOWED,
139 RECEIPT_DAYS_EXCEPTION_CODE, RECEIVING_ROUTING_ID, ALLOW_SUBSTITUTE_RECEIPTS_FLAG,
140 ALLOW_UNORDERED_RECEIPTS_FLAG, HOLD_UNMATCHED_INVOICES_FLAG, TAX_VERIFICATION_DATE,
141 NAME_CONTROL, STATE_REPORTABLE_FLAG, FEDERAL_REPORTABLE_FLAG, ATTRIBUTE_CATEGORY,
142 ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6,
143 ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12,
144 ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15, AUTO_CALCULATE_INTEREST_FLAG,
145 VALIDATION_NUMBER, EXCLUDE_FREIGHT_FROM_DISCOUNT, TAX_REPORTING_NAME, CHECK_DIGITS,
146 ALLOW_AWT_FLAG, AWT_GROUP_ID, AWT_GROUP_NAME, GLOBAL_ATTRIBUTE1, GLOBAL_ATTRIBUTE2,
147 GLOBAL_ATTRIBUTE3, GLOBAL_ATTRIBUTE4, GLOBAL_ATTRIBUTE5, GLOBAL_ATTRIBUTE6,
148 GLOBAL_ATTRIBUTE7, GLOBAL_ATTRIBUTE8, GLOBAL_ATTRIBUTE9, GLOBAL_ATTRIBUTE10,
149 GLOBAL_ATTRIBUTE11, GLOBAL_ATTRIBUTE12, GLOBAL_ATTRIBUTE13, GLOBAL_ATTRIBUTE14,
150 GLOBAL_ATTRIBUTE15, GLOBAL_ATTRIBUTE16, GLOBAL_ATTRIBUTE17, GLOBAL_ATTRIBUTE18,
151 GLOBAL_ATTRIBUTE19, GLOBAL_ATTRIBUTE20, GLOBAL_ATTRIBUTE_CATEGORY, BANK_CHARGE_BEARER,
152 MATCH_OPTION, CREATE_DEBIT_MEMO_FLAG, TERMS_NAME, NI_NUMBER
153 INTO
154 x.VENDOR_ID, x.VENDOR_NAME, x.VENDOR_NAME_ALT, x.SUMMARY_FLAG, x.ENABLED_FLAG, x.EMPLOYEE_ID,
155 x.VENDOR_TYPE_LOOKUP_CODE, x.CUSTOMER_NUM, x.ONE_TIME_FLAG, x.PARENT_VENDOR_ID, x.MIN_ORDER_AMOUNT,
156 x.TERMS_ID, x.SET_OF_BOOKS_ID, x.ALWAYS_TAKE_DISC_FLAG, x.PAY_DATE_BASIS_LOOKUP_CODE,
157 x.PAY_GROUP_LOOKUP_CODE, x.PAYMENT_PRIORITY, x.INVOICE_CURRENCY_CODE, x.PAYMENT_CURRENCY_CODE,
158 x.INVOICE_AMOUNT_LIMIT, x.HOLD_ALL_PAYMENTS_FLAG, x.HOLD_FUTURE_PAYMENTS_FLAG, x.HOLD_REASON,
159 x.TYPE_1099, x.WITHHOLDING_STATUS_LOOKUP_CODE, x.WITHHOLDING_START_DATE,
160 x.ORGANIZATION_TYPE_LOOKUP_CODE, x.START_DATE_ACTIVE, x.END_DATE_ACTIVE,
161 x.MINORITY_GROUP_LOOKUP_CODE, x.WOMEN_OWNED_FLAG, x.SMALL_BUSINESS_FLAG, x.HOLD_FLAG,
162 x.PURCHASING_HOLD_REASON, x.HOLD_BY, x.HOLD_DATE, x.TERMS_DATE_BASIS, x.INSPECTION_REQUIRED_FLAG,
163 x.RECEIPT_REQUIRED_FLAG, x.QTY_RCV_TOLERANCE, x.QTY_RCV_EXCEPTION_CODE,
164 x.ENFORCE_SHIP_TO_LOCATION_CODE, x.DAYS_EARLY_RECEIPT_ALLOWED, x.DAYS_LATE_RECEIPT_ALLOWED,
165 x.RECEIPT_DAYS_EXCEPTION_CODE, x.RECEIVING_ROUTING_ID, x.ALLOW_SUBSTITUTE_RECEIPTS_FLAG,
166 x.ALLOW_UNORDERED_RECEIPTS_FLAG, x.HOLD_UNMATCHED_INVOICES_FLAG, x.TAX_VERIFICATION_DATE,
167 x.NAME_CONTROL, x.STATE_REPORTABLE_FLAG, x.FEDERAL_REPORTABLE_FLAG, x.ATTRIBUTE_CATEGORY,
168 x.ATTRIBUTE1, x.ATTRIBUTE2, x.ATTRIBUTE3, x.ATTRIBUTE4, x.ATTRIBUTE5, x.ATTRIBUTE6,
169 x.ATTRIBUTE7, x.ATTRIBUTE8, x.ATTRIBUTE9, x.ATTRIBUTE10, x.ATTRIBUTE11, x.ATTRIBUTE12,
170 x.ATTRIBUTE13, x.ATTRIBUTE14, x.ATTRIBUTE15, x.AUTO_CALCULATE_INTEREST_FLAG,
171 x.VALIDATION_NUMBER, x.EXCLUDE_FREIGHT_FROM_DISCOUNT, x.TAX_REPORTING_NAME, x.CHECK_DIGITS,
172 x.ALLOW_AWT_FLAG, x.AWT_GROUP_ID, x.AWT_GROUP_NAME, x.GLOBAL_ATTRIBUTE1, x.GLOBAL_ATTRIBUTE2,
173 x.GLOBAL_ATTRIBUTE3, x.GLOBAL_ATTRIBUTE4, x.GLOBAL_ATTRIBUTE5, x.GLOBAL_ATTRIBUTE6,
174 x.GLOBAL_ATTRIBUTE7, x.GLOBAL_ATTRIBUTE8, x.GLOBAL_ATTRIBUTE9, x.GLOBAL_ATTRIBUTE10,
175 x.GLOBAL_ATTRIBUTE11, x.GLOBAL_ATTRIBUTE12, x.GLOBAL_ATTRIBUTE13, x.GLOBAL_ATTRIBUTE14,
176 x.GLOBAL_ATTRIBUTE15, x.GLOBAL_ATTRIBUTE16, x.GLOBAL_ATTRIBUTE17, x.GLOBAL_ATTRIBUTE18,
177 x.GLOBAL_ATTRIBUTE19, x.GLOBAL_ATTRIBUTE20, x.GLOBAL_ATTRIBUTE_CATEGORY, x.BANK_CHARGE_BEARER,
178 x.MATCH_OPTION, x.CREATE_DEBIT_MEMO_FLAG, x.TERMS_NAME, x.NI_NUMBER
179 FROM ap_vendors_v
180 WHERE vendor_id = p_vendor_id ;
181
182 IF l_debug_level <= 1 then
183 itg_debug_pub.add('Exiting get_vendor_rec normal');
184 END IF;
185 EXCEPTION
186 WHEN NO_DATA_FOUND THEN
187 itg_msg.vendor_not_found(p_vendor_id);
188 RAISE FND_API.G_EXC_ERROR;
189 WHEN OTHERS THEN
190 IF l_debug_level <= 1 THEN
191 itg_debug_pub.add('Error in get_vendor_rec ' || SQLCODE || ' - ' || SQLERRM,1);
192 END IF;
193 RAISE;
194 END;
195
196
197 -- no change
198 FUNCTION Is_vinfo_rec_Missing(
199 p_vinfo_rec IN vinfo_rec_type
200 ) RETURN BOOLEAN IS
201 BEGIN
202 RETURN (p_vinfo_rec.syncind IS NULL AND
203 p_vinfo_rec.vendor_id IS NULL AND
204 p_vinfo_rec.currency IS NULL AND
205 p_vinfo_rec.paymethod IS NULL AND
206 p_vinfo_rec.terms_id IS NULL AND
207 p_vinfo_rec.vat_num IS NULL AND
208 p_vinfo_rec.ctl_date IS NULL AND
209 p_vinfo_rec.addr_style IS NULL);
210 END Is_vinfo_rec_Missing;
211
212 -- no change
213 FUNCTION flag_value(p_flag BOOLEAN) RETURN VARCHAR2 IS
214 BEGIN
215 IF p_flag THEN
216 RETURN 'Y';
217 ELSE
218 RETURN 'N';
219 END IF;
220 END;
221
222
223 PROCEDURE validate_vendor_params(
224 p_syncind IN VARCHAR2, /* 'A', 'C', 'D' */
225 p_name IN VARCHAR2, /* name1 */
226 p_onetime IN VARCHAR2 := NULL, /* onetime */
227 p_partnerid IN VARCHAR2 := NULL, /* partnrid */
228 p_active IN NUMBER := NULL, /* active */
229 p_currency IN VARCHAR2 := NULL, /* currency */
230 p_dunsnumber IN VARCHAR2 := NULL, /* dunsnumber */
231 p_parentid IN NUMBER := NULL, /* parentid */
232 p_paymethod IN VARCHAR2 := NULL, /* paymethod */
233 p_taxid IN VARCHAR2 := NULL, /* taxid */
234 p_termid IN VARCHAR2 := NULL, /* termid */
235 p_us_flag IN VARCHAR2 := 'Y', /* userarea.ref_usflag */
236 p_date IN DATE := NULL, /* controlarea.datetime */
237 p_org IN VARCHAR2 /* MOAC */
238 ) IS
239 l_found NUMBER;
240 l_var VARCHAR2(200);
241 l_param_name VARCHAR2(200);
242 l_param_value VARCHAR2(200);
243 BEGIN
244 g_action := 'Sync-vendor parameter validation';
245
246 IF (l_Debug_Level <= 1) THEN
247 itg_debug_pub.Add(g_action,1);
248 END IF;
249
250 IF p_org IS NULL THEN -- MOAC
251 itg_msg.invalid_org(p_org);
252 RAISE FND_API.G_EXC_ERROR;
253 END IF;
254
255 IF NVL(UPPER(p_syncind), 'z') NOT IN ('A', 'C', 'D') THEN
256 l_param_name := 'SYNCIND';
257 l_param_value := p_syncind;
258 ELSIF p_name IS NULL THEN
259 l_param_name := 'NAME';
260 END IF;
261
262 IF l_param_name IS NOT NULL THEN
263 ITG_MSG.missing_element_value(l_param_name, l_param_value);
264 RAISE FND_API.G_EXC_ERROR;
265 END IF;
266
267 IF p_paymethod IS NOT NULL THEN
268 IF (l_Debug_Level <= 1) THEN
269 itg_debug_pub.Add('SV- Checking paymethod', 1);
270 END IF;
271
272
273 BEGIN
274 SELECT 1
275 INTO l_found
276 FROM ap_lookup_codes
277 WHERE upper(lookup_code) = upper(p_paymethod)
278 AND lookup_type = 'PAYMENT METHOD';
279 EXCEPTION
280 WHEN NO_DATA_FOUND THEN
281 ITG_MSG.missing_element_value('PAYMETHOD', p_paymethod);
282 RAISE FND_API.G_EXC_ERROR;
283 END;
284 END IF;
285
286 IF p_termid IS NOT NULL THEN
287 IF (l_Debug_Level <= 1) THEN
288 itg_debug_pub.Add('SV- Checking termid', 1);
289 END IF;
290
291 BEGIN
292 SELECT term_id
293 INTO l_var
294 FROM ap_terms
295 WHERE upper(name) = upper(p_termid);
296 EXCEPTION
297 WHEN OTHERs THEN
298 ITG_MSG.missing_element_value('TERMID', p_termid);
299 RAISE FND_API.G_EXC_ERROR;
300 END;
301
302 END IF;
303
304 BEGIN
305 l_var := to_number(p_onetime);
306 EXCEPTION
307 WHEN OTHERS THEN
308 ITG_MSG.missing_element_value('ONETIME', p_onetime);
309 RAISE FND_API.G_EXC_ERROR;
310 END;
311
312 IF UPPER(p_syncind) = 'A' THEN
313 select count(*)
314 into l_var
315 from ap_vendors_v
316 Where upper(vendor_name) = upper(p_name);
317
318 IF to_number(l_var) > 0 THEN
319 itg_msg.dup_vendor;
320 RAISE FND_API.G_EXC_ERROR;
321 END IF;
322 END IF;
323
324 IF p_currency IS NOT NULL THEN
325
326 select count(*)
327 into l_var
328 from fnd_currencies
329 where currency_code = nvl(p_currency,'USD');
330
331 IF to_number(l_var) = 0 THEN
332 ITG_MSG.missing_element_value('CURRENCY', p_currency);
333 RAISE FND_API.G_EXC_ERROR;
334 END IF;
335 END IF;
336
337 select count(*)
338 into l_var
339 from HR_ALL_ORGANIZATION_UNITS
340 where organization_id = p_org;
341
342 IF to_number(l_var) = 0 THEN
343 ITG_MSG.missing_element_value('ORGID', p_org);
344 RAISE FND_API.G_EXC_ERROR;
345 END IF;
346
347 -- change
348 select count(*)
349 into l_var
350 from ap_vendors_v
351 where nvl(vendor_number,'@@') = nvl(to_char(p_partnerid),'@@')
352 and nvl(upper(p_name),'@@') <> nvl(Upper(vendor_name),'@@');
353
354 IF to_number(l_var) > 0 THEN
355 itg_msg.sup_number_exists(p_partnerid);
356 RAISE FND_API.G_EXC_ERROR;
357 END IF;
358
359 IF (l_Debug_Level <= 1) THEN
360 itg_debug_pub.Add('Validation complete', 1);
361 END IF;
362 EXCEPTION
363 WHEN OTHERS THEN
364 IF l_debug_level <= 1 THEN
365 itg_debug_pub.add('Error in validate_vendor_params ' || SQLCODE || ' - ' || SQLERRM,1);
366 END IF;
367 RAISE;
368 END;
369
370
371 PROCEDURE default_vendor_params
372 (r_vendor_rec IN OUT NOCOPY AP_VENDOR_PUB_PKG.r_vendor_rec_type,
373 vinfo_rec IN OUT NOCOPY vinfo_rec_type,
374 x_org IN OUT NOCOPY VARCHAR2)
375 IS
376 l_dummy_char VARCHAR2(200);
377 l_dummy_num NUMBER;
378 l_dummy_date DATE;
379 BEGIN
380 g_action := 'defaulting vendor parameters';
381
382 IF (l_Debug_Level <= 1) THEN
383 itg_debug_pub.Add('SV - default vendor parameters', 1);
384 END IF;
385
386 AP_Apxvdmvd_PKG.Initialize(
387 x_user_defined_vendor_num_code => l_dummy_char,
388 x_manual_vendor_num_type => l_dummy_char,
389 x_rfq_only_site_flag => l_dummy_char,
390 x_ship_to_location_id => l_dummy_char,
391 x_ship_to_location_code => l_dummy_char,
392 x_bill_to_location_id => l_dummy_char,
393 x_bill_to_location_code => l_dummy_char,
394 x_fob_lookup_code => l_dummy_char,
395 x_freight_terms_lookup_code => l_dummy_char,
396 x_terms_id => l_dummy_num,
397 x_terms_disp => l_dummy_char,
398 x_always_take_disc_flag => r_vendor_rec.always_take_disc_flag,
399 x_invoice_currency_code => l_dummy_char,
400 x_org_id => x_org,
401 x_set_of_books_id => r_vendor_rec.set_of_books_id,
402 x_short_name => l_dummy_char,
403 x_payment_currency_code => l_dummy_char,
404 x_accts_pay_ccid => l_dummy_char,
405 x_future_dated_payment_ccid => l_dummy_char,
406 x_prepay_code_combination_id => l_dummy_num,
407 x_vendor_pay_group_lookup_code => l_dummy_char,
408 x_sys_auto_calc_int_flag => l_dummy_char,
409 x_terms_date_basis => r_vendor_rec.terms_date_basis,
410 x_terms_date_basis_disp => l_dummy_char,
411 x_chart_of_accounts_id => l_dummy_num,
412 x_fob_lookup_disp => l_dummy_char,
413 x_freight_terms_lookup_disp => l_dummy_char,
414 x_vendor_pay_group_disp => l_dummy_char,
415 x_fin_require_matching => l_dummy_char,
416 x_sys_require_matching => l_dummy_char,
417 x_fin_match_option => l_dummy_char,
418 x_po_create_dm_flag => r_vendor_rec.create_debit_memo_flag,
419 x_exclusive_payment => l_dummy_char,
420 x_vendor_auto_int_default => l_dummy_char,
421 x_inventory_organization_id => l_dummy_num,
422 x_ship_via_lookup_code => l_dummy_char,
423 x_ship_via_disp => l_dummy_char,
424 x_sysdate => l_dummy_date,
425 x_enforce_ship_to_loc_code => r_vendor_rec.enforce_ship_to_location_code,
426 x_receiving_routing_id => r_vendor_rec.receiving_routing_id,
427 x_qty_rcv_tolerance => r_vendor_rec.qty_rcv_tolerance,
428 x_qty_rcv_exception_code => r_vendor_rec.qty_rcv_exception_code,
429 x_days_early_receipt_allowed => r_vendor_rec.days_early_receipt_allowed,
430 x_days_late_receipt_allowed => r_vendor_rec.days_late_receipt_allowed,
431 x_allow_sub_receipts_flag => r_vendor_rec.allow_substitute_receipts_flag,
432 x_allow_unord_receipts_flag => r_vendor_rec.allow_unordered_receipts_flag,
433 x_receipt_days_exception_code => r_vendor_rec.receipt_days_exception_code,
434 x_enforce_ship_to_loc_disp => l_dummy_char,
435 x_qty_rcv_exception_disp => l_dummy_char,
436 x_receipt_days_exception_disp => l_dummy_char,
437 x_receipt_required_flag => r_vendor_rec.receipt_required_flag,
438 x_inspection_required_flag => r_vendor_rec.inspection_required_flag,
439 x_payment_method_lookup_code => l_dummy_char,
440 x_payment_method_disp => l_dummy_char,
441 x_pay_date_basis_lookup_code => r_vendor_rec.pay_date_basis_lookup_code,
442 x_pay_date_basis_disp => l_dummy_char,
443 x_receiving_routing_name => l_dummy_char,
444 x_AP_inst_flag => l_dummy_char,
445 x_PO_inst_flag => l_dummy_char,
446 x_home_country_code => l_dummy_char,
447 x_default_country_code => l_dummy_char,
448 x_default_country_disp => l_dummy_char,
449 x_default_awt_group_id => l_dummy_num,
450 x_default_awt_group_name => l_dummy_char,
451 x_allow_awt_flag => r_vendor_rec.allow_awt_flag,
452 x_base_currency_code => l_dummy_char,
453 x_address_style => vinfo_rec.addr_style,
454 x_use_bank_charge_flag => l_dummy_char,
455 x_bank_charge_bearer => r_vendor_rec.bank_charge_bearer,
456 x_calling_sequence => 'APXVDMVD'
457 );
458
459 IF (l_Debug_Level <= 1) THEN
460 itg_debug_pub.Add('Exiting default vendor parameters', 1);
461 END IF;
462 EXCEPTION
463 WHEN OTHERS THEN
464 IF l_debug_level <= 1 THEN
465 itg_debug_pub.add('Error in default_vendor_params ' || SQLCODE || ' - ' || SQLERRM,1);
466 END IF;
467 itg_msg.apicallret('AP_Apxvdmvd_PKG.Initialize','U',substr((SQLCODE || SQLERRM),1,200));
468 RAISE FND_API.G_EXC_ERROR;
469 END;
470
471
472
473 PROCEDURE Sync_Vendor(
474 x_return_status OUT NOCOPY VARCHAR2, /* VARCHAR2(1) */
475 x_msg_count OUT NOCOPY NUMBER,
476 x_msg_data OUT NOCOPY VARCHAR2, /* VARCHAR2(2000) */
477
478 p_syncind IN VARCHAR2, /* 'A', 'C', 'D' */
479 p_name IN VARCHAR2, /* name1 */
480 p_onetime IN VARCHAR2 := NULL, /* onetime */
481 p_partnerid IN VARCHAR2 := NULL, /* partnrid */
482 p_active IN NUMBER := NULL, /* active */
483 p_currency IN VARCHAR2 := NULL, /* currency */
484 p_dunsnumber IN VARCHAR2 := NULL, /* dunsnumber */
485 p_parentid IN NUMBER := NULL, /* parentid */
486 p_paymethod IN VARCHAR2 := NULL, /* paymethod */
487 p_taxid IN VARCHAR2 := NULL, /* taxid */
488 p_termid IN VARCHAR2 := NULL, /* termid */
489 p_us_flag IN VARCHAR2 := 'Y', /* userarea.ref_usflag */
490 p_date IN DATE := NULL, /* controlarea.datetime */
491 p_org IN VARCHAR2, /* MOAC */
492 x_vinfo_rec OUT NOCOPY vinfo_rec_type
493 ) IS
494 r_vendor_rec AP_VENDOR_PUB_PKG.r_vendor_rec_type;
495 l_org_rec HZ_PARTY_V2PUB.organization_rec_type; --To Fix Bug :5186022
496 l_party_object_version_number NUMBER;
497 l_profile_id number;
498 l_party_number VARCHAR2(30);
499 l_duns_number VARCHAR2(30);
500 l_syncind varchar2(200);
501 l_term_id varchar2(200);
502 l_ret_status varchar2(200);
503 l_ret_count number;
504 l_ret_msg varchar2(2000);
505 l_party_id number;
506 l_num_1099 varchar2(200);
507 l_org varchar2(20);
508 l_override_vendornum NUMBER;
509 BEGIN
510 x_return_status := FND_API.G_RET_STS_SUCCESS;
511 g_action := 'Supplier synchronization';
512 SAVEPOINT Sync_Vendor_PVT;
513
514 -- now in wrapperFND_MSG_PUB.Initialize;
515 IF (l_Debug_Level <= 1) THEN
516 itg_debug_pub.Add('--- Parameters Obtained ---' ,1);
517 itg_debug_pub.Add('SV - Top of procedure.' ,1);
518 itg_debug_pub.Add('SV - p_syncind ' ||p_syncind,1);
519 itg_debug_pub.Add('SV - p_name ' ||p_name,1);
520 itg_debug_pub.Add('SV - p_onetime ' ||p_onetime,1);
521 itg_debug_pub.Add('SV - p_partnerid ' ||p_partnerid,1);
522 itg_debug_pub.Add('SV - p_active ' ||p_active,1);
523 itg_debug_pub.Add('SV - p_currency ' ||p_currency,1);
524 itg_debug_pub.Add('SV - p_dunsnumber ' ||p_dunsnumber,1);
525 itg_debug_pub.Add('SV - p_parentid ' ||p_parentid,1);
526 itg_debug_pub.Add('SV - p_paymethod' ||p_paymethod,1);
527 itg_debug_pub.Add('SV - p_taxid ' ||p_taxid,1);
528 itg_debug_pub.Add('SV - p_termid ' ||p_termid,1);
529 itg_debug_pub.Add('SV - p_us_flag ' ||p_us_flag,1);
530 itg_debug_pub.Add('SV - p_date ' ||p_date,1);
531 itg_debug_pub.Add('SV - org ' ||p_org,1);
532 END IF;
533
534 BEGIN
535 MO_GLOBAL.set_policy_context('S', p_org); -- MOAC
536 EXCEPTION
537 WHEN OTHERS THEN
538 itg_msg.invalid_org(p_org);
539 IF l_debug_level <= 6 THEN
540 itg_debug_pub.Add('MO_GLOBAL.set_policy_context ' || SQLCODE || ' - ' || SQLERRM,6);
541 END IF;
542 RAISE FND_API.G_EXC_ERROR;
543 END;
544
545 IF l_debug_level <= 1 THEN
546 itg_debug_pub.add('Before sync vendor, parameter validation ',1);
547 END IF;
548
549 validate_vendor_params(
550 p_syncind => p_syncind,
551 p_name => p_name,
552 p_onetime => p_onetime,
553 p_partnerid => p_partnerid,
554 p_active => p_active,
555 p_currency => p_currency,
556 p_dunsnumber => p_dunsnumber,
557 p_parentid => p_parentid,
558 p_paymethod => p_paymethod,
559 p_taxid => p_taxid,
560 p_termid => p_termid,
561 p_us_flag => p_us_flag,
562 p_date => p_date,
563 p_org => p_org);
564
565 IF l_debug_level <= 1 THEN
566 itg_debug_pub.add('After sync vendor, parameter validation ',1);
567 END IF;
568
569 l_syncind := UPPER(p_syncind);
570 IF p_termid IS NOT NULL THEN
571 SELECT term_id
572 INTO l_term_id
573 FROM ap_terms
574 WHERE upper(name) = upper(p_termid);
575 ELSE
576 l_term_id := null;
577 END IF;
578
579 IF l_debug_level <= 1 THEN
580 itg_debug_pub.add('SV - Termid - ' || l_term_id ,1);
581 END IF;
582
583 x_vinfo_rec.syncind := UPPER(p_syncind);
584 x_vinfo_rec.currency := p_currency;
585 x_vinfo_rec.paymethod := p_paymethod;
586 x_vinfo_rec.terms_id := l_term_id;
587 x_vinfo_rec.terms_name := p_termid;
588 x_vinfo_rec.ctl_date := NVL(p_date, SYSDATE);
589
590 IF p_us_flag = 'N' THEN
591 x_vinfo_rec.vat_num := p_taxid;
592 ELSE
593 x_vinfo_rec.vat_num := NULL;
594 l_num_1099 := p_taxid;
595 END IF;
596
597 IF l_syncind = 'A' THEN
598
599 l_org := p_org;
600 default_vendor_params(r_vendor_rec,x_vinfo_rec,l_org);
601
602 IF l_debug_level <= 1 THEN
603 itg_debug_pub.add('SV - Vendor params defaulted',1);
604 END IF;
605
606 r_vendor_rec.one_time_flag := flag_value(NVL(to_number(p_onetime), 0) <> 0);
607 r_vendor_rec.summary_flag := flag_value(NVL(p_parentid, 0) <> 0);
608 r_vendor_rec.enabled_flag := flag_value(NVL(p_active, 1) <> 0);
609 r_vendor_rec.terms_id := l_term_id;
610 r_vendor_rec.terms_name := p_termid;
611 r_vendor_rec.invoice_currency_code := p_currency;
612 r_vendor_rec.payment_currency_code := p_currency;
613 r_vendor_rec.segment1 := p_partnerid;
614 r_vendor_rec.vendor_name := p_name;
615 r_vendor_rec.vendor_type_lookup_code := 'VENDOR';
616 r_vendor_rec.parent_vendor_id := p_parentid;
617 r_vendor_rec.payment_priority := '1';
618 r_vendor_rec.match_option := 'P';
619 r_vendor_rec.terms_date_basis := NVL(r_vendor_rec.terms_date_basis, 'Goods Received');
620
621
622 IF l_debug_level <= 1 THEN
623 itg_debug_pub.add('Call to create vendor',1);
624 END IF;
625
626 ap_vendor_pub_pkg.Create_Vendor(
627 p_api_version => '1.0',
628 x_return_status => l_ret_status,
629 x_msg_count => l_ret_count,
630 x_msg_data => l_ret_msg,
631 p_vendor_rec => r_vendor_rec,
632 x_vendor_id => r_vendor_rec.vendor_id,
633 x_party_id => l_party_id);
634
635 x_vinfo_rec.vendor_id := r_vendor_rec.vendor_id;
636
637 IF l_debug_level <= 1 THEN
638 itg_debug_pub.add('Create vendor returns - ' || l_ret_status || ' - ' || l_ret_msg ,1);
639 ITG_Debug_pub.add('SV - vendor_id ' || r_vendor_rec.vendor_id,1);
640 END IF;
641
642 IF l_ret_status <> FND_API.G_RET_STS_SUCCESS THEN
643 IF l_debug_level <= 6 THEN
644 itg_debug_pub.add('Error occured in Create Vendor',6);
645 END IF;
646 itg_msg.apicallret('Ap_vendor_pub_pkg.Create_Vendor',l_ret_status,substr(l_ret_msg,1,200));
647 RAISE FND_API.G_EXC_ERROR;
648 END IF;
649
650 g_action := 'verifying vendor_number';
651 BEGIN
652 SELECT vendor_number
653 INTO l_override_vendornum
654 FROM ap_vendors_v
655 WHERE vendor_id = r_vendor_rec.vendor_id;
656 IF l_debug_level <= 1 THEN
657 itg_debug_pub.Add('SV - segment1 '|| l_override_vendornum ,1);
658 END IF;
659 EXCEPTION
660 WHEN OTHERS THEN
661 IF l_debug_level <= 6 THEN
662 itg_debug_pub.add('Error occured while retrieving vendor_number',6);
663 END IF;
664 RAISE FND_API.G_EXC_ERROR;
665 END;
666
667 itg_debug_pub.Add('SV - p_partnerid '||p_partnerid ,1);
668
669 IF l_override_vendornum <> p_partnerid THEN
670 IF (l_Debug_Level <= 1) THEN
671 itg_debug_pub.Add('SV - Segment1 automatically allocated, overriding.' ,1);
672 END IF;
673 l_syncind := 'C';
674 END IF;
675 -- sync ind <> A (else) condtion block begins
676 ELSE
677 g_action := 'Vendor record update';
678
679 IF (l_Debug_Level <= 1) THEN
680 itg_debug_pub.Add('SV - Changing the vendor info.',1);
681 END IF;
682
683 BEGIN
684 SELECT vendor_id
685 INTO r_vendor_rec.vendor_id
686 FROM ap_vendors_v
687 WHERE vendor_name = p_name;
688
689 x_vinfo_rec.vendor_id := r_vendor_rec.vendor_id;
690
691 IF (l_Debug_Level <= 1) THEN
692 itg_debug_pub.Add('x_vinfo_rec.vendor_id - '|| x_vinfo_rec.vendor_id,1);
693 END IF;
694 EXCEPTION
695 WHEN NO_DATA_FOUND THEN
696 itg_msg.vendor_not_found(p_name);
697 RAISE FND_API.G_EXC_ERROR;
698 END;
699 END IF; -- syncind conidition block ends
700
701
702 /*either direct syncid or syncind=c due to segment mismatch */
703 IF l_syncind = 'C' THEN
704
705 g_action := 'Vendor record update';
706
707 get_vendor_rec(r_vendor_rec,x_vinfo_rec.vendor_id);
708
709 IF (l_Debug_Level <= 1) THEN
710 itg_debug_pub.Add('Obtained vendor details ',1);
711 END IF;
712
713
714 r_vendor_rec.one_time_flag := flag_value(NVL(to_number(p_onetime), 0) <> 0);
715 r_vendor_rec.summary_flag := flag_value(NVL(p_parentid, 0) <> 0);
716 -- 5258978 r_vendor_rec.enabled_flag := flag_value((p_active, 1) <> 0);
717 r_vendor_rec.terms_id := l_term_id;
718 r_vendor_rec.terms_name := p_termid;
719 r_vendor_rec.invoice_currency_code := p_currency;
720 r_vendor_rec.payment_currency_code := p_currency;
721 r_vendor_rec.segment1 := NVL(p_partnerid, r_vendor_rec.segment1);
722 r_vendor_rec.vendor_name := p_name;
723 r_vendor_rec.vendor_type_lookup_code := 'VENDOR';
724 r_vendor_rec.parent_vendor_id := NVL(p_parentid, r_vendor_rec.parent_vendor_id);
725 r_vendor_rec.payment_priority := '1';
726 r_vendor_rec.match_option := 'P';
727 r_vendor_rec.terms_date_basis := NVL(r_vendor_rec.terms_date_basis, 'Goods Received');
728
729 -- 5258978
730 IF NVL(p_active, 1) = 0 THEN
731 r_vendor_rec.enabled_flag := 'N';
732 r_vendor_rec.END_DATE_ACTIVE := sysdate;
733 ELSE
734 r_vendor_rec.END_DATE_ACTIVE := sysdate + 3560;
735 r_vendor_rec.enabled_flag := 'Y';
736 END IF;
737
738 IF l_override_vendornum IS NOT NULL THEN
739 r_vendor_rec.segment1 := p_partnerid;
740 END IF;
741
742
743 IF (l_Debug_Level <= 1) THEN
744 itg_debug_pub.Add('SV - vendor_id'||r_vendor_rec.vendor_id ,1);
745 END IF;
746
747 ap_vendor_pub_pkg.update_vendor(
748 p_api_version => '1.0',
749 x_return_status => l_ret_status,
750 x_msg_count => l_ret_count,
751 x_msg_data => l_ret_msg,
752 p_vendor_rec => r_vendor_rec,
753 p_vendor_id => r_vendor_rec.vendor_id
754 );
755
756 IF l_debug_level <= 1 THEN
757 itg_debug_pub.add('Update vendor returns - ' || l_ret_status || ' - ' || l_ret_msg ,1);
758 ITG_Debug_pub.add('SV - vendor_id ' || r_vendor_rec.vendor_id,1);
759 END IF;
760
761 /*Added following block to fix Bug :5186022 */
762
763 BEGIN
764 SELECT PARTY_ID
765 into l_party_id
766 FROM AP_SUPPLIERS
767 WHERE VENDOR_ID=r_vendor_rec.vendor_id;
768
769 select object_version_number,
770 party_number,
771 duns_number_c
772 into l_party_object_version_number,
773 l_party_number,
774 l_duns_number
775 from hz_parties
776 where party_id=l_party_id;
777
778 IF (l_Debug_Level <= 1) THEN
779 itg_debug_pub.Add('party_id - '|| l_party_id,1);
780 itg_debug_pub.Add('party_object_version_number - '|| l_party_object_version_number,1);
781 itg_debug_pub.Add('party_number - '|| l_party_number,1);
782 itg_debug_pub.Add('party_duns_number - '|| l_duns_number,1);
783
784 END IF;
785
786 EXCEPTION
787 WHEN NO_DATA_FOUND THEN
788 IF (l_Debug_Level <= 1) THEN
789 itg_debug_pub.Add('Couldn''t find party_id from ap_suppliers or obj_ver,party_num,duns_num from hz_parties ');
790 END IF;
791 RAISE FND_API.G_EXC_ERROR;
792 END;
793
794 l_org_rec.duns_number_c := NVL(p_dunsnumber ,l_duns_number);
795 l_org_rec.party_rec.party_number :=l_party_number;
796 l_org_rec.party_rec.party_id := l_party_id;
797 HZ_PARTY_V2PUB.update_organization (
798 p_init_msg_list => FND_API.G_FALSE,
799 p_organization_rec => l_org_rec,
800 p_party_object_version_number => l_party_object_version_number,
801 x_profile_id => l_party_id,
802 x_return_status => l_ret_status,
803 x_msg_count => l_ret_count,
804 x_msg_data => l_ret_msg
805 );
806
807
808
809 IF l_ret_status <> FND_API.G_RET_STS_SUCCESS THEN
810 IF l_debug_level <= 6 THEN
811 itg_debug_pub.add('Error occured in update Vendor or update organization',6);
812 END IF;
813 itg_msg.apicallret('ap_vendor_pub_pkg.Update_vendor or hz_party_v2pub.update_organization returns-',l_ret_status,substr(l_ret_msg,1,200));
814 RAISE FND_API.G_EXC_ERROR;
815 END IF;
816
817
818 END IF;
819
820 IF (l_Debug_Level <= 1) THEN
821 itg_debug_pub.Add('Commiting work',1);
822 END IF;
823
824 COMMIT WORK;
825
826 IF (l_Debug_Level <= 2) THEN
827 itg_debug_pub.Add('EXITING - Sync_Vendor.', 2);
828 END IF;
829 EXCEPTION
830 WHEN FND_API.G_EXC_ERROR THEN
831 ROLLBACK TO Sync_Vendor_PVT;
832 COMMIT;
833 x_return_status := FND_API.G_RET_STS_ERROR;
834 ITG_msg.checked_error(g_action);
835 IF (l_Debug_Level <= 6) THEN
836 itg_debug_pub.Add('EXITING - Sync_Vendor:: ERROR', 6);
837 END IF;
838
839 WHEN OTHERS THEN
840 ROLLBACK TO Sync_Vendor_PVT;
841 COMMIT;
842 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
843 itg_debug.msg('Unexpected error (Vendor sync) - ' || substr(SQLERRM,1,255),true);
844 ITG_msg.unexpected_error(g_action);
845 IF (l_Debug_Level <= 6) THEN
846 itg_debug_pub.Add('EXITING - Sync_Vendor:: ERROR', 6);
847 END IF;
848
849 -- Removed FND_MSG_PUB.Count_And_Get
850 END Sync_Vendor;
851
852
853 PROCEDURE validate_vendorsite_params(
854 p_addrline1 IN VARCHAR2 ,
855 p_addrline2 IN VARCHAR2 ,
856 p_addrline3 IN VARCHAR2 ,
857 p_addrline4 IN VARCHAR2 ,
858 p_city IN VARCHAR2 ,
859 p_country IN VARCHAR2 ,
860 p_county IN VARCHAR2 ,
861 p_site_code IN VARCHAR2 ,
862 p_fax IN VARCHAR2 ,
863 p_zip IN VARCHAR2 ,
864 p_state IN VARCHAR2 ,
865 p_phone IN VARCHAR2 ,
866 p_org IN VARCHAR2 ,
867 p_purch_site IN VARCHAR2 ,
868 p_pay_site IN VARCHAR2 ,
869 p_rfq_site IN VARCHAR2 ,
870 p_pc_site IN VARCHAR2 ,
871 p_vat_code IN VARCHAR2 ,
872 p_vinfo_rec IN vinfo_rec_type
873 )IS
874 l_var NUMBER;
875 l_element VARCHAR2(30);
876 l_value VARCHAR2(30);
877 BEGIN
878 g_action := 'vendor-site parameter validation';
879
880 BEGIN
881 SELECT count(*) INTO l_var
882 FROM FND_TERRITORIES
883 WHERE TERRITORY_CODE = p_country
884 AND OBSOLETE_FLAG = 'N';
885 EXCEPTION
886 WHEN OTHERS THEN
887 itg_msg.missing_element_value('COUNTRY',p_country);
888 RAISE FND_API.G_EXC_ERROR;
889
890 END;
891
892 l_element := null;
893
894 IF NVL(UPPER(p_purch_site),'@') NOT IN ('Y','N') THEN
895 l_element := 'ORACLEITG.PURSITE';
896 l_value := p_purch_site;
897 ELSIF NVL(UPPER(p_pay_site),'@') NOT IN ('Y','N') THEN
898 l_element := 'ORACLEITG.PAYSITE';
899 l_value := p_pay_site;
900 ELSIF NVL(UPPER(p_rfq_site),'@') NOT IN ('Y','N') THEN
901 l_element := 'ORACLEITG.RFQSITE';
902 l_value := p_rfq_site;
903 ELSIF NVL(UPPER(p_pc_site),'@') NOT IN ('Y','N') THEN
904 l_element := 'ORACLEITG.PCSITE';
905 l_value := p_pc_site;
906 ELSIF p_site_code IS NULL THEN
907 l_element := 'SITECODE';
908 l_value := 'null';
909 END IF;
910
911 IF l_element IS NOT NULL THEN
912 itg_msg.missing_element_value(l_element,l_value);
913 RAISE FND_API.G_EXC_ERROR;
914 END IF;
915
916 select count(*)
917 into l_var
918 from HR_ALL_ORGANIZATION_UNITS
919 where organization_id = p_org;
920
921 IF to_number(l_var) = 0 THEN
922 ITG_MSG.missing_element_value('ORGID', p_org);
923 RAISE FND_API.G_EXC_ERROR;
924 END IF;
925
926 END;
927
928 PROCEDURE Sync_VendorSite(
929
930 x_return_status OUT NOCOPY VARCHAR2, /* VARCHAR2(1) */
931 x_msg_count OUT NOCOPY NUMBER,
932 x_msg_data OUT NOCOPY VARCHAR2, /* VARCHAR2(2000) */
933
934 /* TAG: address */
935 p_addrline1 IN VARCHAR2 := NULL, /* addrline index=1 */
936 p_addrline2 IN VARCHAR2 := NULL, /* addrline index=2 */
937 p_addrline3 IN VARCHAR2 := NULL, /* addrline index=3 */
938 p_addrline4 IN VARCHAR2 := NULL, /* addrline index=4 */
939 p_city IN VARCHAR2 := NULL, /* city */
940 p_country IN VARCHAR2 := NULL, /* country */
941 p_county IN VARCHAR2 := NULL, /* county */
942 p_site_code IN VARCHAR2, /* descriptn (key) */
943 p_fax IN VARCHAR2 := NULL, /* fax index=1 */
944 p_zip IN VARCHAR2 := NULL, /* postalcode */
945 p_state IN VARCHAR2 := NULL, /* stateprovn */
946 p_phone IN VARCHAR2 := NULL, /* telephone index=1 */
947 p_org IN VARCHAR2 := NULL,
948 p_purch_site IN VARCHAR2 := NULL, /* userarea.ref_pursite */
949 p_pay_site IN VARCHAR2 := NULL, /* userarea.ref_paysite */
950 p_rfq_site IN VARCHAR2 := NULL, /* userarea.ref_rfqsite */
951 p_pc_site IN VARCHAR2 := NULL, /* userarea.ref_pcsite */
952 p_vat_code IN VARCHAR2 := NULL, /* userarea.ref_vatcode */
953
954 p_vinfo_rec IN vinfo_rec_type
955 ) IS
956 l_vendor_found boolean;
957 l_sob_found boolean;
958 l_return_status varchar2(20);
959 l_ret_msg varchar2(2000);
960 l_msg_count NUMBER;
961 l_vendor_site_id NUMBER;
962 l_party_id NUMBER;
963 l_location_id NUMBER;
964 l_ven_rec ap_vendors_v%ROWTYPE;
965 l_api_name VARCHAR2(50);
966 l_vendorsite_rec ap_vendor_pub_pkg.r_vendor_site_rec_type;
967 p_location_rec HZ_LOCATION_V2PUB.LOCATION_REC_TYPE;
968 p_object_version_number NUMBER;
969 l_ship_to_location_id financials_system_params_all.ship_to_location_id%TYPE;
970 l_bill_to_location_id financials_system_params_all.bill_to_location_id%TYPE;
971 l_ship_via_lookup_code financials_system_params_all.ship_via_lookup_code%TYPE;
972 l_freight_terms_lookup_code financials_system_params_all.freight_terms_lookup_code%TYPE;
973 l_fob_lookup_code financials_system_params_all.fob_lookup_code%TYPE;
974 l_accts_pay_code_comb_id financials_system_params_all.accts_pay_code_combination_id%TYPE;
975 l_prepay_code_comb_id financials_system_params_all.prepay_code_combination_id%TYPE;
976 l_future_dated_pay_ccid NUMBER;
977
978
979 CURSOR sob_csr(p_org VARCHAR2) IS
980 SELECT set_of_books_id
981 FROM org_organization_definitions
982 WHERE organization_id = p_org;
983
984 CURSOR fin_params_csr(p_sob_id NUMBER) IS
985 SELECT ship_to_location_id,
986 bill_to_location_id,
987 ship_via_lookup_code,
988 freight_terms_lookup_code,
989 fob_lookup_code,
990 accts_pay_code_combination_id,
991 prepay_code_combination_id
992 FROM financials_system_params_all
993 WHERE set_of_books_id = p_sob_id;
994
995 BEGIN
996 x_return_status := FND_API.G_RET_STS_SUCCESS;
997 g_action := 'Vendor-site sync';
998
999 SAVEPOINT Sync_VendorSite_PVT;
1000
1001 IF (l_Debug_Level <= 1) THEN
1002 itg_debug_pub.Add('Top of procedure.', 1);
1003 itg_debug_pub.Add('p_addrline1' ||p_addrline1, 1);
1004 itg_debug_pub.Add('p_addrline2' ||p_addrline2, 1);
1005 itg_debug_pub.Add('p_addrline3' ||p_addrline3, 1);
1006 itg_debug_pub.Add('p_addrline4' ||p_addrline4, 1);
1007 itg_debug_pub.Add('p_city' ||p_city, 1 );
1008 itg_debug_pub.Add('p_country' ||p_country, 1 );
1009 itg_debug_pub.Add('p_county' ||p_county, 1);
1010 itg_debug_pub.Add('p_site_code' ||p_site_code, 1);
1011 itg_debug_pub.Add('p_fax' ||p_fax, 1);
1012 itg_debug_pub.Add('p_zip' ||p_zip, 1);
1013 itg_debug_pub.Add('p_state' ||p_state, 1);
1014 itg_debug_pub.Add('p_phone' ||p_phone, 1);
1015 itg_debug_pub.Add('p_purch_site'||p_purch_site, 1);
1016 itg_debug_pub.Add('p_pay_site' ||p_pay_site, 1);
1017 itg_debug_pub.Add('p_pc_site' ||p_pc_site, 1);
1018 itg_debug_pub.Add('p_rfq_site' ||p_rfq_site, 1);
1019 itg_debug_pub.Add('p_vat_code' ||p_vat_code, 1);
1020 itg_debug_pub.Add('p_org ' ||p_org, 1);
1021 END IF;
1022
1023 validate_vendorsite_params(
1024 p_addrline1 => p_addrline1,
1025 p_addrline2 => p_addrline2,
1026 p_addrline3 => p_addrline3,
1027 p_addrline4 => p_addrline4,
1028 p_city => p_city,
1029 p_country => p_country,
1030 p_county => p_county,
1031 p_site_code => p_site_code,
1032 p_fax => p_fax,
1033 p_zip => p_zip,
1034 p_state => p_state,
1035 p_phone => p_phone,
1036 p_org => p_org,
1037 p_purch_site => p_purch_site,
1038 p_pay_site => p_pay_site,
1039 p_rfq_site => p_rfq_site,
1040 p_pc_site => p_pc_site,
1041 p_vat_code => p_vat_code,
1042 p_vinfo_rec => p_vinfo_rec);
1043
1044 BEGIN
1045 IF (l_Debug_Level <= 1) THEN
1046 itg_debug_pub.Add('Retrieving vendor record - ' || p_vinfo_rec.vendor_id ,1);
1047 END IF;
1048
1049 g_action := 'query vendor details';
1050
1051 SELECT *
1052 INTO l_ven_rec
1053 FROM ap_vendors_v
1054 WHERE vendor_id = p_vinfo_rec.vendor_id;
1055
1056 EXCEPTION
1057 WHEN NO_DATA_FOUND THEN
1058 IF (l_Debug_Level <= 1) THEN
1059 itg_debug_pub.Add('Vendor not found - erroring out' ,1);
1060 END IF;
1061 itg_msg.vendor_not_found('vendorid:' ||p_vinfo_rec.vendor_id);
1062 RAISE FND_API.G_EXC_ERROR;
1063 END;
1064
1065
1066 g_action := 'check for vendor site';
1067 BEGIN
1068 SELECT vendor_site_id
1069 INTO l_vendor_site_id
1070 FROM ap_supplier_sites
1071 WHERE vendor_id = p_vinfo_rec.vendor_id
1072 AND upper(vendor_site_code) = UPPER(p_site_code);
1073
1074 l_vendor_found := true;
1075
1076 IF (l_Debug_Level <= 1) THEN
1077 itg_debug_pub.add('Vendor site obtained as - ' || l_vendor_site_id);
1078 END IF;
1079
1080 EXCEPTION
1081 WHEN NO_DATA_FOUND THEN
1082 IF (l_Debug_Level <= 1) THEN
1083 itg_debug_pub.add('Vendor site not found');
1084 END IF;
1085 l_vendor_found := false;
1086 END;
1087
1088 IF p_org IS NOT NULL THEN
1089 IF (l_Debug_Level <= 1) THEN
1090 itg_debug_pub.Add('SVS - Looking up set_of_books_id',1);
1091 END IF;
1092
1093 OPEN sob_csr(p_org);
1094 FETCH sob_csr
1095 INTO l_ven_rec.set_of_books_id;
1096 l_sob_found := sob_csr%FOUND;
1097 CLOSE sob_csr;
1098
1099
1100 IF l_sob_found THEN
1101 IF (l_Debug_Level <= 1) THEN
1102 itg_debug_pub.Add('SVS - Looking up financial params' ,1);
1103 itg_debug_pub.Add('SVS - set_of_books_id'||l_ven_rec.set_of_books_id,1);
1104 END IF;
1105
1106 OPEN fin_params_csr(p_sob_id => l_ven_rec.set_of_books_id);
1107 FETCH fin_params_csr
1108 INTO l_ship_to_location_id,
1109 l_bill_to_location_id,
1110 l_ship_via_lookup_code,
1111 l_freight_terms_lookup_code,
1112 l_fob_lookup_code,
1113 l_accts_pay_code_comb_id,
1114 l_prepay_code_comb_id;
1115 CLOSE fin_params_csr;
1116 END IF;
1117 END IF;
1118
1119
1120
1121 IF NOT l_vendor_found THEN
1122 IF (l_Debug_Level <= 1) THEN
1123 itg_debug_pub.add('Creating vendor record');
1124 END IF;
1125
1126 g_action := 'vendor site creation';
1127 l_vendorsite_rec.vendor_site_code := p_site_code;
1128 l_vendorsite_rec.PHONE := p_phone;
1129 l_vendorsite_rec.FAX := p_fax;
1130 l_vendorsite_rec.COUNTRY := p_country;
1131 l_vendorsite_rec.ADDRESS_LINE1 := p_addrline1;
1132 l_vendorsite_rec.ADDRESS_LINE2 := p_addrline2;
1133 l_vendorsite_rec.ADDRESS_LINE3 := p_addrline3;
1134 l_vendorsite_rec.ADDRESS_LINE4 := p_addrline4;
1135 l_vendorsite_rec.COUNTY := p_county;
1136 l_vendorsite_rec.CITY := p_city;
1137 l_vendorsite_rec.STATE := p_state;
1138 l_vendorsite_rec.ZIP := p_zip;
1139 l_vendorsite_rec.PURCHASING_SITE_FLAG := p_purch_site;
1140 l_vendorsite_rec.RFQ_ONLY_SITE_FLAG := p_rfq_site;
1141 l_vendorsite_rec.PAY_SITE_FLAG := p_pay_site;
1142 l_vendorsite_rec.PCARD_SITE_FLAG := p_pc_site;
1143 l_vendorsite_rec.payment_priority := 2;
1144 l_vendorsite_rec.TERMS_DATE_BASIS := NVL(l_ven_rec.terms_date_basis, 'Goods Received');
1145 l_vendorsite_rec.SHIP_TO_LOCATION_ID := l_ship_to_location_id;
1146 l_vendorsite_rec.BILL_TO_LOCATION_ID := l_bill_to_location_id;
1147 l_vendorsite_rec.SHIP_VIA_LOOKUP_CODE := l_ship_via_lookup_code;
1148 l_vendorsite_rec.FREIGHT_TERMS_LOOKUP_CODE := l_freight_terms_lookup_code;
1149 l_vendorsite_rec.FOB_LOOKUP_CODE := l_fob_lookup_code;
1150 l_vendorsite_rec.ACCTS_PAY_CODE_COMBINATION_ID := l_accts_pay_code_comb_id;
1151 l_vendorsite_rec.PREPAY_CODE_COMBINATION_ID := l_prepay_code_comb_id;
1152 l_vendorsite_rec.PAY_GROUP_LOOKUP_CODE := l_ven_rec.pay_group_lookup_code;
1153 l_vendorsite_rec.PAY_DATE_BASIS_LOOKUP_CODE := l_ven_rec.pay_date_basis_lookup_code;
1154 l_vendorsite_rec.ALWAYS_TAKE_DISC_FLAG := l_ven_rec.always_take_disc_flag;
1155 l_vendorsite_rec.BANK_CHARGE_BEARER := l_ven_rec.bank_charge_bearer;
1156 l_vendorsite_rec.ALLOW_AWT_FLAG := l_ven_rec.allow_awt_flag;
1157 l_vendorsite_rec.FUTURE_DATED_PAYMENT_CCID := l_future_dated_pay_ccid;
1158 l_vendorsite_rec.CREATE_DEBIT_MEMO_FLAG := l_ven_rec.create_debit_memo_flag;
1159 l_vendorsite_rec.ADDRESS_STYLE := p_vinfo_rec.addr_style;
1160 l_vendorsite_rec.INVOICE_CURRENCY_CODE := NVL(p_vinfo_rec.currency, l_ven_rec.invoice_currency_code);
1161 l_vendorsite_rec.PAYMENT_CURRENCY_CODE := NVL(p_vinfo_rec.currency, l_ven_rec.payment_currency_code);
1162 l_vendorsite_rec.TERMS_ID := NVL(p_vinfo_rec.terms_id, l_ven_rec.terms_id);
1163 l_vendorsite_rec.TERMS_NAME := NVL(p_vinfo_rec.terms_name, l_ven_rec.terms_name);
1164 l_vendorsite_rec.match_option := 'P';
1165 l_vendorsite_rec.vendor_id := p_vinfo_rec.vendor_id;
1166 l_vendorsite_rec.org_id := p_org;
1167
1168 l_api_name := 'ap_vendor_pub_pkg.create_vendor_site';
1169 ap_vendor_pub_pkg.create_vendor_site
1170 (
1171 p_api_version => '1.0',
1172 x_return_status => l_return_status,
1173 x_msg_count => l_msg_count,
1174 x_msg_data => l_ret_msg,
1175 p_vendor_site_rec => l_vendorsite_rec,
1176 x_vendor_site_id => l_vendor_site_id,
1177 x_party_site_id => l_party_id,
1178 x_location_id => l_location_id
1179 );
1180
1181 IF (l_Debug_Level <= 1) THEN
1182 itg_debug_pub.add('return from ap_vendors_pub_pkg.create_vendor_site');
1183 itg_debug_pub.add('l_return_status ' || l_return_status);
1184 itg_debug_pub.add('l_msg_count ' || l_msg_count);
1185 itg_debug_pub.add('l_ret_msg ' || l_ret_msg);
1186 itg_debug_pub.add('l_vendor_site_id ' || l_vendor_site_id);
1187 itg_debug_pub.add('l_party_id ' || l_party_id);
1188 itg_debug_pub.add('l_location_id ' || l_location_id);
1189 END IF;
1190
1191 ELSE
1192
1193 g_action := 'vendor site update';
1194 IF (l_Debug_Level <= 1) THEN
1195 itg_debug_pub.add('Updating vendor record');
1196 END IF;
1197
1198 g_action := 'Vendor-site info update';
1199 get_vendorsite_rec(l_vendorsite_rec,l_vendor_site_id,p_vinfo_rec.vendor_id);
1200
1201 IF (l_Debug_Level <= 1) THEN
1202 itg_debug_pub.add('Retrieved old vendor record information');
1203 END IF;
1204 l_vendorsite_rec.vendor_site_code := null;
1205 l_vendorsite_rec.PHONE := NVL(p_phone, l_vendorsite_rec.phone);
1206 l_vendorsite_rec.FAX := NVL(p_fax, l_vendorsite_rec.fax);
1207 l_vendorsite_rec.COUNTRY := NVL(p_country, l_vendorsite_rec.country);
1208 l_vendorsite_rec.ADDRESS_LINE1 := NVL(p_addrline1, l_vendorsite_rec.address_line1);
1209 l_vendorsite_rec.ADDRESS_LINE2 := NVL(p_addrline2, l_vendorsite_rec.address_line2);
1210 l_vendorsite_rec.ADDRESS_LINE3 := NVL(p_addrline3, l_vendorsite_rec.address_line3);
1211 l_vendorsite_rec.ADDRESS_LINE4 := NVL(p_addrline4, l_vendorsite_rec.address_line4);
1212 l_vendorsite_rec.COUNTY := NVL(p_county, l_vendorsite_rec.county);
1213 l_vendorsite_rec.CITY := NVL(p_city, l_vendorsite_rec.city);
1214 l_vendorsite_rec.STATE := NVL(p_state, l_vendorsite_rec.state);
1215 l_vendorsite_rec.ZIP := NVL(p_zip, l_vendorsite_rec.zip);
1216 l_vendorsite_rec.PURCHASING_SITE_FLAG := NVL(p_purch_site, l_vendorsite_rec.purchasing_site_flag);
1217 l_vendorsite_rec.RFQ_ONLY_SITE_FLAG := NVL(p_rfq_site, l_vendorsite_rec.rfq_only_site_flag);
1218 l_vendorsite_rec.PAY_SITE_FLAG := NVL(p_pay_site, l_vendorsite_rec.pay_site_flag);
1219 l_vendorsite_rec.PCARD_SITE_FLAG := NVL(p_pc_site, l_vendorsite_rec.pcard_site_flag);
1220 l_vendorsite_rec.payment_priority := 2;
1221 l_vendorsite_rec.match_option := 'P';
1222 l_vendorsite_rec.TERMS_ID := NVL(p_vinfo_rec.terms_id, l_vendorsite_rec.terms_id);
1223 l_vendorsite_rec.TERMS_NAME := NVL(p_vinfo_rec.terms_name, l_vendorsite_rec.terms_name);
1224 l_vendorsite_rec.PAYMENT_CURRENCY_CODE := NVL(p_vinfo_rec.currency, l_vendorsite_rec.invoice_currency_code);
1225 l_vendorsite_rec.INVOICE_CURRENCY_CODE := NVL(p_vinfo_rec.currency, l_vendorsite_rec.payment_currency_code);
1226 l_vendorsite_rec.org_id := p_org;
1227 l_vendorsite_rec.vendor_site_id := l_vendor_site_id;
1228
1229 IF (l_Debug_Level <= 1) THEN
1230 itg_debug_pub.add('Calling ap_vendors_pub_pkg.update_vendor_site');
1231 END IF;
1232 l_api_name := 'ap_vendor_pub_pkg.update_vendor_site';
1233 ap_vendor_pub_pkg.update_vendor_site
1234 (
1235 p_api_version => '1.0',
1236 x_return_status => l_return_status,
1237 x_msg_count => l_msg_count,
1238 x_msg_data => l_ret_msg,
1239 p_vendor_site_rec => l_vendorsite_rec,
1240 p_vendor_site_id => l_vendor_site_id
1241 );
1242
1243 IF (l_Debug_Level <= 1) THEN
1244 itg_debug_pub.add('Return from ap_vendors_pub_pkg.update_vendor_site');
1245 itg_debug_pub.add('l_return_status ' || l_return_status);
1246 itg_debug_pub.add('l_msg_count ' || l_msg_count);
1247 itg_debug_pub.add('l_ret_msg ' || l_ret_msg);
1248 itg_debug_pub.add('l_vendor_site_id ' || l_vendor_site_id);
1249 END IF;
1250
1251 /* Adding following block to Fix Bug: 5258874 to update supplier site address locations*/
1252 BEGIN
1253 select location_id into p_location_rec.location_id
1254 from ap_supplier_sites
1255 where vendor_id = p_vinfo_rec.vendor_id and vendor_site_id = l_vendor_site_id;
1256 IF (l_Debug_Level <= 1) THEN
1257 itg_debug_pub.Add('location_id - '|| p_location_rec.location_id,1);
1258 END IF;
1259
1260 EXCEPTION
1261 WHEN NO_DATA_FOUND THEN
1262 IF (l_Debug_Level <= 1) THEN
1263 itg_debug_pub.Add('Couldn''t find location_id ');
1264 END IF;
1265 RAISE FND_API.G_EXC_ERROR;
1266 END;
1267
1268
1269 BEGIN
1270 select object_version_number
1271 into p_object_version_number
1272 from hz_locations
1273 where location_id = p_location_rec.location_id;
1274
1275 IF (l_Debug_Level <= 1) THEN
1276 itg_debug_pub.Add('object_version_number - '|| p_object_version_number);
1277 END IF;
1278
1279 EXCEPTION
1280 WHEN NO_DATA_FOUND THEN
1281 IF (l_Debug_Level <= 1) THEN
1282 itg_debug_pub.Add('Couldn''t find object_version_number');
1283 END IF;
1284 RAISE FND_API.G_EXC_ERROR;
1285
1286 END;
1287
1288 p_location_rec.orig_system_reference := '';
1289 --p_location_rec.orig_system :=
1290 p_location_rec.country := NVL(p_country, l_vendorsite_rec.country);
1291 p_location_rec.address1 := NVL(p_addrline1, l_vendorsite_rec.address_line1);
1292 p_location_rec.address2 := NVL(p_addrline2, l_vendorsite_rec.address_line2);
1293 p_location_rec.address3 := NVL(p_addrline3, l_vendorsite_rec.address_line3);
1294 p_location_rec.address4 := NVL(p_addrline4, l_vendorsite_rec.address_line4);
1295 p_location_rec.city := NVL(p_city, l_vendorsite_rec.city);
1296 p_location_rec.postal_code := NVL(p_zip, l_vendorsite_rec.zip);
1297 p_location_rec.state := NVL(p_state, l_vendorsite_rec.state);
1298 p_location_rec.province := NVL(p_state, l_vendorsite_rec.state);
1299 p_location_rec.county := NVL(p_county, l_vendorsite_rec.county);
1300
1301 IF (l_Debug_Level <= 1) THEN
1302 itg_debug_pub.add('Calling hz_location_v2pub.update_location ');
1303
1304 END IF;
1305 l_api_name := 'hz_location_v2pub.update_location';
1306 hz_location_v2pub.update_location
1307 (
1308 p_init_msg_list => FND_API.G_FALSE,
1309 p_location_rec => p_location_rec,
1310 p_object_version_number => p_object_version_number,
1311 x_return_status => l_return_status,
1312 x_msg_count => l_msg_count,
1313 x_msg_data => l_ret_msg
1314
1315 );
1316 IF (l_Debug_Level <= 1) THEN
1317 itg_debug_pub.add('Return from hz_location_v2pub.update_location');
1318 itg_debug_pub.add('l_return_status ' || l_return_status);
1319 itg_debug_pub.add('l_msg_count ' || l_msg_count);
1320 itg_debug_pub.add('l_ret_msg ' || l_ret_msg);
1321 End if;
1322 END IF;
1323
1324 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1325
1326 IF (l_Debug_Level <= 1) THEN
1327 itg_debug_pub.add('Create/Update_vendor_site/update_location API returns - ' || l_return_status || ' - ' || l_ret_msg);
1328 END IF;
1329 itg_msg.apicallret(l_api_name, l_return_status, l_ret_msg);
1330 RAISE FND_API.G_EXC_ERROR;
1331 END IF;
1332
1333 IF (l_Debug_Level <= 1) THEN
1334 itg_debug_pub.Add('Commiting work',1);
1335 END IF;
1336
1337 COMMIT WORK;
1338
1339 IF (l_Debug_Level <= 2) THEN
1340 itg_debug_pub.Add('EXITING - Sync_VendorSite.', 2);
1341 END IF;
1342
1343 EXCEPTION
1344 WHEN FND_API.G_EXC_ERROR THEN
1345 ROLLBACK TO Sync_VendorSite_PVT;
1346 commit;
1347 x_return_status := FND_API.G_RET_STS_ERROR;
1348 ITG_msg.checked_error(g_action);
1349 itg_msg.vendor_site_only;
1350 IF (l_Debug_Level <= 6) THEN
1351 itg_debug_pub.Add('EXITING - Sync_VendorSite :ERROR', 6);
1352 END IF;
1353
1354
1355 WHEN OTHERS THEN
1356 ROLLBACK TO Sync_VendorSite_PVT;
1357 commit;
1358 ITG_msg.unexpected_error(g_action);
1359 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1360 itg_msg.vendor_site_only;
1361 itg_debug.msg('Unexpected error (VendorSite sync) - ' || substr(SQLERRM,1,255),true);
1362 IF (l_Debug_Level <= 6) THEN
1363 itg_debug_pub.Add('EXITING - Sync_VendorSite :ERROR', 6);
1364 END IF;
1365
1366
1367 END Sync_VendorSite;
1368
1369
1370 PROCEDURE Sync_VendorContact(
1371 x_return_status OUT NOCOPY VARCHAR2, /* VARCHAR2(1) */
1372 x_msg_count OUT NOCOPY NUMBER,
1373 x_msg_data OUT NOCOPY VARCHAR2, /* VARCHAR2(2000) */
1374
1375 p_title IN VARCHAR2 := NULL, /* contcttype */
1376 p_first_name IN VARCHAR2 := NULL, /* name index=1 */
1377 p_middle_name IN VARCHAR2 := NULL, /* name index=2 */
1378 p_last_name IN VARCHAR2 := NULL, /* name index=3 */
1379 p_phone IN VARCHAR2 := NULL, /* telephone index=1 */
1380 p_site_code IN VARCHAR2, /* userarea.ref_sitecode */
1381
1382 p_vinfo_rec IN vinfo_rec_type
1383 ) IS
1384 r_vendor_contact_rec ap_vendor_pub_pkg.r_vendor_contact_rec_type;
1385 l_found Boolean;
1386 l_ret_status varchar2(20);
1387 l_msg_data varchar2(2000);
1388 l_ret_msg varchar2(2000);
1389 l_msg_count NUMBER;
1390 l_party_site_id NUMBER;
1391 l_org_contact_id NUMBER;
1392 l_rel_id NUMBER;
1393 l_rel_party_id NUMBER;
1394 l_per_party_id NUMBER;
1395 l_vendor_contact_id NUMBER;
1396 l_vsite_id NUMBER;
1397 l_obj_ver_num NUMBER;
1398 l_party_id NUMBER;
1399 l_profile_id NUMBER;
1400 l_party_rec HZ_PARTY_V2PUB.party_rec_type;
1401 l_per_rec HZ_PARTY_V2PUB.person_rec_type;
1402 BEGIN
1403 x_return_status := FND_API.G_RET_STS_SUCCESS;
1404 g_action := 'Vendor-contact parameter validation';
1405
1406 IF (l_Debug_Level <= 2) THEN
1407 itg_debug_pub.Add('ENTERING - Sync_VendorContact', 2);
1408 itg_debug_pub.Add('p_title - ' || p_title, 2);
1409 itg_debug_pub.Add('p_first_name - ' || p_first_name, 2);
1410 itg_debug_pub.Add('p_middle_name - ' || p_middle_name, 2);
1411 itg_debug_pub.Add('p_last_name - ' || p_last_name, 2);
1412 itg_debug_pub.Add('p_phone - ' || p_phone, 2);
1413 itg_debug_pub.Add('p_site_code - ' || p_site_code, 2);
1414 END IF;
1415
1416 -- sync vendor
1417 SAVEPOINT Sync_VendorContact_PVT;
1418
1419
1420 BEGIN
1421
1422 g_action := 'Vendor-contact information sync';
1423
1424 SELECT vendor_site_id
1425 INTO l_vsite_id
1426 FROM ap_supplier_sites
1427 WHERE UPPER(vendor_site_code) = UPPER(p_site_code)
1428 AND vendor_id = p_vinfo_rec.vendor_id;
1429
1430 IF (l_Debug_Level <= 1) THEN
1431 itg_debug_pub.Add('Getting vendor site ID - ' || l_vsite_id ,1);
1432 END IF;
1433 EXCEPTION
1434 WHEN NO_DATA_FOUND THEN
1435 itg_msg.no_vendor_site('vendor-sitecode:' || p_site_code);
1436 RAISE FND_API.G_EXC_ERROR;
1437 END;
1438
1439 BEGIN
1440
1441 g_action := 'Vendor-contact information sync';
1442
1443 select h.party_id, h.object_version_number
1444 into l_party_id,l_obj_ver_num
1445 from HZ_PARTIES h, ap_supplier_contacts a
1446 where a.per_party_id = h.party_id
1447 and a.vendor_site_id = l_vsite_id
1448 AND NVL(upper(person_first_name), '1') = NVL(upper(p_first_name), '1')
1449 AND NVL(upper(person_middle_name), '1') = NVL(upper(p_middle_name), '1')
1450 AND NVL(upper(person_last_name), '1') = NVL(upper(p_last_name), '1')
1451 AND ROWNUM = 1;
1452
1453 l_found := true;
1454
1455 IF (l_Debug_Level <= 1) THEN
1456 itg_debug_pub.Add('Contact party id - ' || l_party_id ,1);
1457 itg_debug_pub.Add('Contact obj version - ' || l_obj_ver_num ,1);
1458 END IF;
1459
1460
1461 EXCEPTION
1462 WHEN NO_DATA_FOUND THEN
1463 IF (l_Debug_Level <= 5) THEN
1464 itg_debug_pub.Add('SVC - Contact record not found, trying to add it' ,5);
1465 END IF;
1466 l_found := false;
1467 END;
1468
1469
1470
1471 IF NOT l_found THEN
1472 g_action := 'Vendor-contact record creation';
1473 r_vendor_contact_rec.PERSON_FIRST_NAME := p_first_name;
1474 r_vendor_contact_rec.PERSON_MIDDLE_NAME := p_middle_name;
1475 r_vendor_contact_rec.PERSON_LAST_NAME := p_last_name;
1476 r_vendor_contact_rec.PERSON_TITLE := p_title;
1477 r_vendor_contact_rec.PHONE := p_phone;
1478 r_vendor_contact_rec.VENDOR_SITE_CODE := p_site_code;
1479 r_vendor_contact_rec.VENDOR_SITE_ID := l_vsite_id;
1480 r_vendor_contact_rec.VENDOR_ID := p_vinfo_rec.vendor_id;
1481 r_vendor_contact_rec.PERSON_TITLE := p_title;
1482
1483 Ap_vendor_pub_pkg.Create_Vendor_Contact
1484 (
1485 p_api_version => 1.0,
1486 x_return_status => l_ret_status,
1487 x_msg_count => l_msg_count,
1488 x_msg_data => l_msg_data,
1489 p_vendor_contact_rec => r_vendor_contact_rec,
1490 x_vendor_contact_id => l_vendor_contact_id,
1491 x_per_party_id => l_per_party_id,
1492 x_rel_party_id => l_rel_party_id,
1493 x_rel_id => l_rel_id,
1494 x_org_contact_id => l_org_contact_id,
1495 x_party_site_id => l_party_site_id
1496 );
1497
1498 IF l_debug_level <= 1 THEN
1499 itg_debug_pub.Add('Create_Vendor_Contact - ' || l_ret_status || ' - ' || l_msg_data ,1);
1500 itg_debug_pub.Add('l_per_party_id - ' || l_per_party_id,1);
1501 itg_debug_pub.Add('l_rel_party_id - ' || l_rel_party_id,1);
1502 itg_debug_pub.Add('l_rel_id - ' || l_rel_id,1 );
1503 itg_debug_pub.Add('l_org_contact_id - ' || l_org_contact_id,1 );
1504 itg_debug_pub.Add('l_party_site_id - ' || l_party_site_id,1 );
1505 END IF;
1506
1507 IF l_ret_status <> FND_API.G_RET_STS_SUCCESS THEN
1508 itg_msg.apicallret('Ap_vendor_pub_pkg.Create_Vendor_Contact',l_ret_status,l_msg_data);
1509 RAISE FND_API.G_EXC_ERROR;
1510 END IF;
1511 ELSE
1512 g_action := 'Vendor-contact record update';
1513 l_party_rec.party_id := l_party_id;
1514 l_per_rec.person_first_name := p_first_name;
1515 l_per_rec.person_middle_name := p_middle_name;
1516 l_per_rec.person_last_name := p_last_name;
1517 l_per_rec.person_title := p_title;
1518 l_per_rec.created_by_module := 'AP_SUPPLIERS_API';
1519 l_per_rec.application_id := 200;
1520 l_per_rec.party_rec := l_party_rec;
1521
1522 HZ_PARTY_V2PUB.update_person (
1523 p_person_rec => l_per_rec,
1524 p_party_object_version_number => l_obj_ver_num,
1525 x_profile_id => l_profile_id,
1526 x_return_status => l_ret_status,
1527 x_msg_count => l_msg_count,
1528 x_msg_data => l_msg_data);
1529
1530 IF l_debug_level <= 1 THEN
1531 itg_debug_pub.Add('HZ_PARTY_V2PUB.update_person - ' || l_ret_status || ' - ' || l_msg_data ,1);
1532 itg_debug_pub.Add('l_obj_ver_num - ' || l_msg_count,1);
1533 itg_debug_pub.Add('l_profile_id - ' || l_obj_ver_num,1);
1534 itg_debug_pub.Add('l_msg_count - ' || l_profile_id,1 );
1535
1536 END IF;
1537
1538 IF l_ret_status <> FND_API.G_RET_STS_SUCCESS THEN
1539 itg_msg.apicallret('HZ_PARTY_V2PUB.update_person',l_ret_status,l_msg_data);
1540 RAISE FND_API.G_EXC_ERROR;
1541 END IF;
1542
1543 END IF;
1544
1545 IF (l_Debug_Level <= 1) THEN
1546 itg_debug_pub.Add('Committing work' ,1);
1547 END IF;
1548
1549 COMMIT WORK;
1550
1551 IF (l_Debug_Level <= 2) THEN
1552 itg_debug_pub.Add('EXTING - Sync_VendorContact', 2);
1553 END IF;
1554
1555
1556 EXCEPTION
1557 WHEN FND_API.G_EXC_ERROR THEN
1558 ROLLBACK TO Sync_VendorContact_PVT;
1559 commit;
1560 x_return_status := FND_API.G_RET_STS_ERROR;
1561 ITG_msg.checked_error(g_action);
1562 itg_msg.vendor_contact_only;
1563 IF (l_Debug_Level <= 6) THEN
1564 itg_debug_pub.Add('EXTING - Sync_VendorContact :OTHER ERROR', 6);
1565 END IF;
1566
1567
1568 WHEN OTHERS THEN
1569 ROLLBACK TO Sync_VendorContact_PVT;
1570 commit;
1571 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1572 ITG_msg.unexpected_error(g_action);
1573 itg_msg.vendor_contact_only;
1574 itg_debug.msg('Unexpected error (VendorContact sync) - ' || substr(SQLERRM,1,255),true);
1575 IF (l_Debug_Level <= 6) THEN
1576 itg_debug_pub.Add('EXTING - Sync_VendorContact :OTHER ERROR', 6);
1577 END IF;
1578
1579 END Sync_VendorContact;
1580
1581 END ITG_SyncSupplierInbound_PVT;