[Home] [Help]
PACKAGE BODY: APPS.ITG_SYNCSUPPLIERINBOUND_PVT
Source
1 PACKAGE BODY ITG_SyncSupplierInbound_PVT AS
2 /* ARCS: $Header: itgvssib.pls 120.19 2012/01/09 18:20:10 agunnam ship $
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,
73 x.COUNTY, x.STATE, x.ZIP, x.COUNTRY, x.PROVINCE, x.AREA_CODE, x.PHONE,
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,
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,
154 x.VENDOR_ID, x.VENDOR_NAME, x.VENDOR_NAME_ALT, x.SUMMARY_FLAG, x.ENABLED_FLAG, x.EMPLOYEE_ID,
151 GLOBAL_ATTRIBUTE19, GLOBAL_ATTRIBUTE20, GLOBAL_ATTRIBUTE_CATEGORY, BANK_CHARGE_BEARER,
152 MATCH_OPTION, CREATE_DEBIT_MEMO_FLAG, TERMS_NAME, NI_NUMBER
153 INTO
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
250 IF p_org IS NULL THEN -- MOAC
247 itg_debug_pub.Add(g_action,1);
248 END IF;
249
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,
404 x_accts_pay_ccid => l_dummy_char,
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,
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_create_awt_dists_type => l_dummy_char,
453 x_base_currency_code => l_dummy_char,
454 x_address_style => vinfo_rec.addr_style,
455 x_use_bank_charge_flag => l_dummy_char,
456 x_bank_charge_bearer => r_vendor_rec.bank_charge_bearer,
457 x_employee_id => r_vendor_rec.employee_id,
458 x_calling_sequence => 'APXVDMVD'
459 );
460
461 IF (l_Debug_Level <= 1) THEN
462 itg_debug_pub.Add('Exiting default vendor parameters', 1);
463 END IF;
464 EXCEPTION
465 WHEN OTHERS THEN
466 IF l_debug_level <= 1 THEN
467 itg_debug_pub.add('Error in default_vendor_params ' || SQLCODE || ' - ' || SQLERRM,1);
468 END IF;
469 itg_msg.apicallret('AP_Apxvdmvd_PKG.Initialize','U',substr((SQLCODE || SQLERRM),1,200));
470 RAISE FND_API.G_EXC_ERROR;
471 END;
472
473
474
475 PROCEDURE Sync_Vendor(
476 x_return_status OUT NOCOPY VARCHAR2, /* VARCHAR2(1) */
477 x_msg_count OUT NOCOPY NUMBER,
478 x_msg_data OUT NOCOPY VARCHAR2, /* VARCHAR2(2000) */
479
480 p_syncind IN VARCHAR2, /* 'A', 'C', 'D' */
481 p_name IN VARCHAR2, /* name1 */
482 p_onetime IN VARCHAR2 := NULL, /* onetime */
483 p_partnerid IN VARCHAR2 := NULL, /* partnrid */
484 p_active IN NUMBER := NULL, /* active */
485 p_currency IN VARCHAR2 := NULL, /* currency */
486 p_dunsnumber IN VARCHAR2 := NULL, /* dunsnumber */
487 p_parentid IN NUMBER := NULL, /* parentid */
488 p_paymethod IN VARCHAR2 := NULL, /* paymethod */
489 p_taxid IN VARCHAR2 := NULL, /* taxid */
490 p_termid IN VARCHAR2 := NULL, /* termid */
491 p_us_flag IN VARCHAR2 := 'Y', /* userarea.ref_usflag */
492 p_date IN DATE := NULL, /* controlarea.datetime */
493 p_org IN VARCHAR2, /* MOAC */
494 x_vinfo_rec OUT NOCOPY vinfo_rec_type
495 ) IS
496 r_vendor_rec AP_VENDOR_PUB_PKG.r_vendor_rec_type;
497 l_org_rec HZ_PARTY_V2PUB.organization_rec_type; --To Fix Bug :5186022
498 l_party_object_version_number NUMBER;
502 l_syncind varchar2(200);
499 l_profile_id number;
500 l_party_number VARCHAR2(30);
501 l_duns_number VARCHAR2(30);
503 l_term_id varchar2(200);
504 l_ret_status varchar2(200);
505 l_ret_count number;
506 l_ret_msg varchar2(2000);
507 l_party_id number;
508 l_num_1099 varchar2(200);
509 l_org varchar2(20);
510 l_override_vendornum NUMBER;
511 BEGIN
512 x_return_status := FND_API.G_RET_STS_SUCCESS;
513 g_action := 'Supplier synchronization';
514 SAVEPOINT Sync_Vendor_PVT;
515
516 -- now in wrapperFND_MSG_PUB.Initialize;
517 IF (l_Debug_Level <= 1) THEN
518 itg_debug_pub.Add('--- Parameters Obtained ---' ,1);
519 itg_debug_pub.Add('SV - Top of procedure.' ,1);
520 itg_debug_pub.Add('SV - p_syncind ' ||p_syncind,1);
521 itg_debug_pub.Add('SV - p_name ' ||p_name,1);
522 itg_debug_pub.Add('SV - p_onetime ' ||p_onetime,1);
523 itg_debug_pub.Add('SV - p_partnerid ' ||p_partnerid,1);
524 itg_debug_pub.Add('SV - p_active ' ||p_active,1);
525 itg_debug_pub.Add('SV - p_currency ' ||p_currency,1);
526 itg_debug_pub.Add('SV - p_dunsnumber ' ||p_dunsnumber,1);
527 itg_debug_pub.Add('SV - p_parentid ' ||p_parentid,1);
528 itg_debug_pub.Add('SV - p_paymethod' ||p_paymethod,1);
529 itg_debug_pub.Add('SV - p_taxid ' ||p_taxid,1);
530 itg_debug_pub.Add('SV - p_termid ' ||p_termid,1);
531 itg_debug_pub.Add('SV - p_us_flag ' ||p_us_flag,1);
532 itg_debug_pub.Add('SV - p_date ' ||p_date,1);
533 itg_debug_pub.Add('SV - org ' ||p_org,1);
534 END IF;
535
536 BEGIN
537 MO_GLOBAL.set_policy_context('S', p_org); -- MOAC
538 EXCEPTION
539 WHEN OTHERS THEN
540 itg_msg.invalid_org(p_org);
541 IF l_debug_level <= 6 THEN
542 itg_debug_pub.Add('MO_GLOBAL.set_policy_context ' || SQLCODE || ' - ' || SQLERRM,6);
543 END IF;
544 RAISE FND_API.G_EXC_ERROR;
545 END;
546
547 IF l_debug_level <= 1 THEN
548 itg_debug_pub.add('Before sync vendor, parameter validation ',1);
549 END IF;
550
551 validate_vendor_params(
552 p_syncind => p_syncind,
553 p_name => p_name,
554 p_onetime => p_onetime,
555 p_partnerid => p_partnerid,
556 p_active => p_active,
557 p_currency => p_currency,
558 p_dunsnumber => p_dunsnumber,
559 p_parentid => p_parentid,
560 p_paymethod => p_paymethod,
561 p_taxid => p_taxid,
562 p_termid => p_termid,
563 p_us_flag => p_us_flag,
564 p_date => p_date,
565 p_org => p_org);
566
567 IF l_debug_level <= 1 THEN
568 itg_debug_pub.add('After sync vendor, parameter validation ',1);
569 END IF;
570
571 l_syncind := UPPER(p_syncind);
572 IF p_termid IS NOT NULL THEN
573 SELECT term_id
574 INTO l_term_id
575 FROM ap_terms
576 WHERE upper(name) = upper(p_termid);
577 ELSE
578 l_term_id := null;
579 END IF;
580
581 IF l_debug_level <= 1 THEN
582 itg_debug_pub.add('SV - Termid - ' || l_term_id ,1);
583 END IF;
584
585 x_vinfo_rec.syncind := UPPER(p_syncind);
586 x_vinfo_rec.currency := p_currency;
587 x_vinfo_rec.paymethod := p_paymethod;
588 x_vinfo_rec.terms_id := l_term_id;
589 x_vinfo_rec.terms_name := p_termid;
590 x_vinfo_rec.ctl_date := NVL(p_date, SYSDATE);
591
592 IF p_us_flag = 'N' THEN
593 x_vinfo_rec.vat_num := p_taxid;
594 ELSE
595 x_vinfo_rec.vat_num := NULL;
596 l_num_1099 := p_taxid;
597 END IF;
598
599 IF l_syncind = 'A' THEN
600
601 l_org := p_org;
602 default_vendor_params(r_vendor_rec,x_vinfo_rec,l_org);
603
604 IF l_debug_level <= 1 THEN
605 itg_debug_pub.add('SV - Vendor params defaulted',1);
606 END IF;
607
608 r_vendor_rec.one_time_flag := flag_value(NVL(to_number(p_onetime), 0) <> 0);
609 r_vendor_rec.summary_flag := flag_value(NVL(p_parentid, 0) <> 0);
610 r_vendor_rec.enabled_flag := flag_value(NVL(p_active, 1) <> 0);
611 r_vendor_rec.terms_id := l_term_id;
612 r_vendor_rec.terms_name := p_termid;
613 r_vendor_rec.invoice_currency_code := p_currency;
614 r_vendor_rec.payment_currency_code := p_currency;
615 r_vendor_rec.segment1 := p_partnerid;
616 r_vendor_rec.vendor_name := p_name;
617 r_vendor_rec.vendor_type_lookup_code := 'VENDOR';
618 r_vendor_rec.parent_vendor_id := p_parentid;
619 r_vendor_rec.payment_priority := '1';
620 r_vendor_rec.match_option := 'P';
621 r_vendor_rec.terms_date_basis := NVL(r_vendor_rec.terms_date_basis, 'Goods Received');
622
623
624 IF l_debug_level <= 1 THEN
625 itg_debug_pub.add('Call to create vendor',1);
626 END IF;
627
628 ap_vendor_pub_pkg.Create_Vendor(
629 p_api_version => '1.0',
630 x_return_status => l_ret_status,
631 x_msg_count => l_ret_count,
632 x_msg_data => l_ret_msg,
633 p_vendor_rec => r_vendor_rec,
634 x_vendor_id => r_vendor_rec.vendor_id,
635 x_party_id => l_party_id);
636
640 itg_debug_pub.add('Create vendor returns - ' || l_ret_status || ' - ' || l_ret_msg ,1);
637 x_vinfo_rec.vendor_id := r_vendor_rec.vendor_id;
638
639 IF l_debug_level <= 1 THEN
641 ITG_Debug_pub.add('SV - vendor_id ' || r_vendor_rec.vendor_id,1);
642 END IF;
643
644 IF l_ret_status <> FND_API.G_RET_STS_SUCCESS THEN
645 IF l_debug_level <= 6 THEN
646 itg_debug_pub.add('Error occured in Create Vendor',6);
647 END IF;
648 itg_msg.apicallret('Ap_vendor_pub_pkg.Create_Vendor',l_ret_status,substr(l_ret_msg,1,200));
649 RAISE FND_API.G_EXC_ERROR;
650 END IF;
651
652 g_action := 'verifying vendor_number';
653 BEGIN
654 SELECT vendor_number
655 INTO l_override_vendornum
656 FROM ap_vendors_v
657 WHERE vendor_id = r_vendor_rec.vendor_id;
658 IF l_debug_level <= 1 THEN
659 itg_debug_pub.Add('SV - segment1 '|| l_override_vendornum ,1);
660 END IF;
661 EXCEPTION
662 WHEN OTHERS THEN
663 IF l_debug_level <= 6 THEN
664 itg_debug_pub.add('Error occured while retrieving vendor_number',6);
665 END IF;
666 RAISE FND_API.G_EXC_ERROR;
667 END;
668
669 itg_debug_pub.Add('SV - p_partnerid '||p_partnerid ,1);
670
671 IF l_override_vendornum <> p_partnerid THEN
672 IF (l_Debug_Level <= 1) THEN
673 itg_debug_pub.Add('SV - Segment1 automatically allocated, overriding.' ,1);
674 END IF;
675 l_syncind := 'C';
676 END IF;
677 -- sync ind <> A (else) condtion block begins
678 ELSE
679 g_action := 'Vendor record update';
680
681 IF (l_Debug_Level <= 1) THEN
682 itg_debug_pub.Add('SV - Changing the vendor info.',1);
683 END IF;
684
685 BEGIN
686 SELECT vendor_id
687 INTO r_vendor_rec.vendor_id
688 FROM ap_vendors_v
689 WHERE vendor_name = p_name;
690
691 x_vinfo_rec.vendor_id := r_vendor_rec.vendor_id;
692
693 IF (l_Debug_Level <= 1) THEN
694 itg_debug_pub.Add('x_vinfo_rec.vendor_id - '|| x_vinfo_rec.vendor_id,1);
695 END IF;
696 EXCEPTION
697 WHEN NO_DATA_FOUND THEN
698 itg_msg.vendor_not_found(p_name);
699 RAISE FND_API.G_EXC_ERROR;
700 END;
701 END IF; -- syncind conidition block ends
702
703
704 /*either direct syncid or syncind=c due to segment mismatch */
705 IF l_syncind = 'C' THEN
706
707 g_action := 'Vendor record update';
708
709 get_vendor_rec(r_vendor_rec,x_vinfo_rec.vendor_id);
710
711 IF (l_Debug_Level <= 1) THEN
712 itg_debug_pub.Add('Obtained vendor details ',1);
713 END IF;
714
715
716 r_vendor_rec.one_time_flag := flag_value(NVL(to_number(p_onetime), 0) <> 0);
717 r_vendor_rec.summary_flag := flag_value(NVL(p_parentid, 0) <> 0);
718 -- 5258978 r_vendor_rec.enabled_flag := flag_value((p_active, 1) <> 0);
719 r_vendor_rec.terms_id := l_term_id;
720 r_vendor_rec.terms_name := p_termid;
721 r_vendor_rec.invoice_currency_code := p_currency;
722 r_vendor_rec.payment_currency_code := p_currency;
723 r_vendor_rec.segment1 := NVL(p_partnerid, r_vendor_rec.segment1);
724 r_vendor_rec.vendor_name := p_name;
725 r_vendor_rec.vendor_type_lookup_code := 'VENDOR';
726 r_vendor_rec.parent_vendor_id := NVL(p_parentid, r_vendor_rec.parent_vendor_id);
727 r_vendor_rec.payment_priority := '1';
728 r_vendor_rec.match_option := 'P';
729 r_vendor_rec.terms_date_basis := NVL(r_vendor_rec.terms_date_basis, 'Goods Received');
730
731 -- 5258978
732 IF NVL(p_active, 1) = 0 THEN
733 r_vendor_rec.enabled_flag := 'N';
734 r_vendor_rec.END_DATE_ACTIVE := sysdate;
735 ELSE
736 r_vendor_rec.END_DATE_ACTIVE := sysdate + 3560;
737 r_vendor_rec.enabled_flag := 'Y';
738 END IF;
739
740 IF l_override_vendornum IS NOT NULL THEN
741 r_vendor_rec.segment1 := p_partnerid;
742 END IF;
743
744
745 IF (l_Debug_Level <= 1) THEN
746 itg_debug_pub.Add('SV - vendor_id'||r_vendor_rec.vendor_id ,1);
747 END IF;
748
749 ap_vendor_pub_pkg.update_vendor(
750 p_api_version => '1.0',
751 x_return_status => l_ret_status,
752 x_msg_count => l_ret_count,
753 x_msg_data => l_ret_msg,
754 p_vendor_rec => r_vendor_rec,
755 p_vendor_id => r_vendor_rec.vendor_id
756 );
757
758 IF l_debug_level <= 1 THEN
759 itg_debug_pub.add('Update vendor returns - ' || l_ret_status || ' - ' || l_ret_msg ,1);
760 ITG_Debug_pub.add('SV - vendor_id ' || r_vendor_rec.vendor_id,1);
761 END IF;
762
763 /*Added following block to fix Bug :5186022 */
764
765 BEGIN
766 SELECT PARTY_ID
767 into l_party_id
768 FROM AP_SUPPLIERS
769 WHERE VENDOR_ID=r_vendor_rec.vendor_id;
770
771 select object_version_number,
772 party_number,
773 duns_number_c
774 into l_party_object_version_number,
775 l_party_number,
776 l_duns_number
777 from hz_parties
778 where party_id=l_party_id;
779
783 itg_debug_pub.Add('party_number - '|| l_party_number,1);
780 IF (l_Debug_Level <= 1) THEN
781 itg_debug_pub.Add('party_id - '|| l_party_id,1);
782 itg_debug_pub.Add('party_object_version_number - '|| l_party_object_version_number,1);
784 itg_debug_pub.Add('party_duns_number - '|| l_duns_number,1);
785
786 END IF;
787
788 EXCEPTION
789 WHEN NO_DATA_FOUND THEN
790 IF (l_Debug_Level <= 1) THEN
791 itg_debug_pub.Add('Couldn''t find party_id from ap_suppliers or obj_ver,party_num,duns_num from hz_parties ');
792 END IF;
793 RAISE FND_API.G_EXC_ERROR;
794 END;
795
796 l_org_rec.duns_number_c := NVL(p_dunsnumber ,l_duns_number);
797 l_org_rec.party_rec.party_number :=l_party_number;
798 l_org_rec.party_rec.party_id := l_party_id;
799 HZ_PARTY_V2PUB.update_organization (
800 p_init_msg_list => FND_API.G_FALSE,
801 p_organization_rec => l_org_rec,
802 p_party_object_version_number => l_party_object_version_number,
803 x_profile_id => l_party_id,
804 x_return_status => l_ret_status,
805 x_msg_count => l_ret_count,
806 x_msg_data => l_ret_msg
807 );
808
809
810
811 IF l_ret_status <> FND_API.G_RET_STS_SUCCESS THEN
812 IF l_debug_level <= 6 THEN
813 itg_debug_pub.add('Error occured in update Vendor or update organization',6);
814 END IF;
815 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));
816 RAISE FND_API.G_EXC_ERROR;
817 END IF;
818
819
820 END IF;
821
822 IF (l_Debug_Level <= 1) THEN
823 itg_debug_pub.Add('Commiting work',1);
824 END IF;
825
826 COMMIT WORK;
827
828 IF (l_Debug_Level <= 2) THEN
829 itg_debug_pub.Add('EXITING - Sync_Vendor.', 2);
830 END IF;
831 EXCEPTION
832 WHEN FND_API.G_EXC_ERROR THEN
833 ROLLBACK TO Sync_Vendor_PVT;
834 COMMIT;
835 x_return_status := FND_API.G_RET_STS_ERROR;
836 ITG_msg.checked_error(g_action);
837 IF (l_Debug_Level <= 6) THEN
838 itg_debug_pub.Add('EXITING - Sync_Vendor:: ERROR', 6);
839 END IF;
840
841 WHEN OTHERS THEN
842 ROLLBACK TO Sync_Vendor_PVT;
843 COMMIT;
844 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
845 itg_debug.msg('Unexpected error (Vendor sync) - ' || substr(SQLERRM,1,255),true);
846 ITG_msg.unexpected_error(g_action);
847 IF (l_Debug_Level <= 6) THEN
848 itg_debug_pub.Add('EXITING - Sync_Vendor:: ERROR', 6);
849 END IF;
850
851 -- Removed FND_MSG_PUB.Count_And_Get
852 END Sync_Vendor;
853
854
855 PROCEDURE validate_vendorsite_params(
856 p_addrline1 IN VARCHAR2 ,
857 p_addrline2 IN VARCHAR2 ,
858 p_addrline3 IN VARCHAR2 ,
859 p_addrline4 IN VARCHAR2 ,
860 p_city IN VARCHAR2 ,
861 p_country IN VARCHAR2 ,
862 p_county IN VARCHAR2 ,
863 p_site_code IN VARCHAR2 ,
864 p_fax IN VARCHAR2 ,
865 p_zip IN VARCHAR2 ,
866 p_state IN VARCHAR2 ,
867 p_phone IN VARCHAR2 ,
868 p_org IN VARCHAR2 ,
869 p_purch_site IN VARCHAR2 ,
870 p_pay_site IN VARCHAR2 ,
871 p_rfq_site IN VARCHAR2 ,
872 p_pc_site IN VARCHAR2 ,
873 p_vat_code IN VARCHAR2 ,
874 p_vinfo_rec IN vinfo_rec_type
875 )IS
876 l_var NUMBER;
877 l_element VARCHAR2(30);
878 l_value VARCHAR2(30);
879 BEGIN
880 g_action := 'vendor-site parameter validation';
881
882 BEGIN
883 SELECT count(*) INTO l_var
884 FROM FND_TERRITORIES
885 WHERE TERRITORY_CODE = p_country
886 AND OBSOLETE_FLAG = 'N';
887 EXCEPTION
888 WHEN OTHERS THEN
889 itg_msg.missing_element_value('COUNTRY',p_country);
890 RAISE FND_API.G_EXC_ERROR;
891
892 END;
893
894 l_element := null;
895
896 IF NVL(UPPER(p_purch_site),'@') NOT IN ('Y','N') THEN
897 l_element := 'ORACLEITG.PURSITE';
898 l_value := p_purch_site;
899 ELSIF NVL(UPPER(p_pay_site),'@') NOT IN ('Y','N') THEN
900 l_element := 'ORACLEITG.PAYSITE';
901 l_value := p_pay_site;
902 ELSIF NVL(UPPER(p_rfq_site),'@') NOT IN ('Y','N') THEN
903 l_element := 'ORACLEITG.RFQSITE';
904 l_value := p_rfq_site;
905 ELSIF NVL(UPPER(p_pc_site),'@') NOT IN ('Y','N') THEN
906 l_element := 'ORACLEITG.PCSITE';
907 l_value := p_pc_site;
908 ELSIF p_site_code IS NULL THEN
909 l_element := 'SITECODE';
910 l_value := 'null';
911 END IF;
912
913 IF l_element IS NOT NULL THEN
914 itg_msg.missing_element_value(l_element,l_value);
915 RAISE FND_API.G_EXC_ERROR;
916 END IF;
917
918 select count(*)
919 into l_var
923 IF to_number(l_var) = 0 THEN
920 from HR_ALL_ORGANIZATION_UNITS
921 where organization_id = p_org;
922
924 ITG_MSG.missing_element_value('ORGID', p_org);
925 RAISE FND_API.G_EXC_ERROR;
926 END IF;
927
928 END;
929
930 PROCEDURE Sync_VendorSite(
931
932 x_return_status OUT NOCOPY VARCHAR2, /* VARCHAR2(1) */
933 x_msg_count OUT NOCOPY NUMBER,
934 x_msg_data OUT NOCOPY VARCHAR2, /* VARCHAR2(2000) */
935
936 /* TAG: address */
937 p_addrline1 IN VARCHAR2 := NULL, /* addrline index=1 */
938 p_addrline2 IN VARCHAR2 := NULL, /* addrline index=2 */
939 p_addrline3 IN VARCHAR2 := NULL, /* addrline index=3 */
940 p_addrline4 IN VARCHAR2 := NULL, /* addrline index=4 */
941 p_city IN VARCHAR2 := NULL, /* city */
942 p_country IN VARCHAR2 := NULL, /* country */
943 p_county IN VARCHAR2 := NULL, /* county */
944 p_site_code IN VARCHAR2, /* descriptn (key) */
945 p_fax IN VARCHAR2 := NULL, /* fax index=1 */
946 p_zip IN VARCHAR2 := NULL, /* postalcode */
947 p_state IN VARCHAR2 := NULL, /* stateprovn */
948 p_phone IN VARCHAR2 := NULL, /* telephone index=1 */
949 p_org IN VARCHAR2 := NULL,
950 p_purch_site IN VARCHAR2 := NULL, /* userarea.ref_pursite */
951 p_pay_site IN VARCHAR2 := NULL, /* userarea.ref_paysite */
952 p_rfq_site IN VARCHAR2 := NULL, /* userarea.ref_rfqsite */
953 p_pc_site IN VARCHAR2 := NULL, /* userarea.ref_pcsite */
954 p_vat_code IN VARCHAR2 := NULL, /* userarea.ref_vatcode */
955
956 p_vinfo_rec IN vinfo_rec_type
957 ) IS
958 l_vendor_found boolean;
959 l_sob_found boolean;
960 l_return_status varchar2(20);
961 l_ret_msg varchar2(2000);
962 l_msg_count NUMBER;
963 l_vendor_site_id NUMBER;
964 l_party_id NUMBER;
965 l_location_id NUMBER;
966 l_ven_rec ap_vendors_v%ROWTYPE;
967 l_api_name VARCHAR2(50);
968 l_vendorsite_rec ap_vendor_pub_pkg.r_vendor_site_rec_type;
969 p_location_rec HZ_LOCATION_V2PUB.LOCATION_REC_TYPE;
970 p_object_version_number NUMBER;
971 l_ship_to_location_id financials_system_params_all.ship_to_location_id%TYPE;
972 l_bill_to_location_id financials_system_params_all.bill_to_location_id%TYPE;
973 l_ship_via_lookup_code financials_system_params_all.ship_via_lookup_code%TYPE;
974 l_freight_terms_lookup_code financials_system_params_all.freight_terms_lookup_code%TYPE;
975 l_fob_lookup_code financials_system_params_all.fob_lookup_code%TYPE;
976 l_accts_pay_code_comb_id financials_system_params_all.accts_pay_code_combination_id%TYPE;
977 l_prepay_code_comb_id financials_system_params_all.prepay_code_combination_id%TYPE;
978 l_future_dated_pay_ccid NUMBER;
979
980
981 CURSOR sob_csr(p_org VARCHAR2) IS
982 SELECT set_of_books_id
983 FROM org_organization_definitions
984 WHERE organization_id = p_org;
985
986 CURSOR fin_params_csr(p_sob_id NUMBER) IS
987 SELECT ship_to_location_id,
988 bill_to_location_id,
989 ship_via_lookup_code,
990 freight_terms_lookup_code,
991 fob_lookup_code,
992 accts_pay_code_combination_id,
993 prepay_code_combination_id
994 FROM financials_system_params_all
995 WHERE set_of_books_id = p_sob_id;
996
997 BEGIN
998 x_return_status := FND_API.G_RET_STS_SUCCESS;
999 g_action := 'Vendor-site sync';
1000
1001 SAVEPOINT Sync_VendorSite_PVT;
1002
1003 IF (l_Debug_Level <= 1) THEN
1004 itg_debug_pub.Add('Top of procedure.', 1);
1005 itg_debug_pub.Add('p_addrline1' ||p_addrline1, 1);
1006 itg_debug_pub.Add('p_addrline2' ||p_addrline2, 1);
1007 itg_debug_pub.Add('p_addrline3' ||p_addrline3, 1);
1008 itg_debug_pub.Add('p_addrline4' ||p_addrline4, 1);
1009 itg_debug_pub.Add('p_city' ||p_city, 1 );
1010 itg_debug_pub.Add('p_country' ||p_country, 1 );
1011 itg_debug_pub.Add('p_county' ||p_county, 1);
1012 itg_debug_pub.Add('p_site_code' ||p_site_code, 1);
1013 itg_debug_pub.Add('p_fax' ||p_fax, 1);
1014 itg_debug_pub.Add('p_zip' ||p_zip, 1);
1015 itg_debug_pub.Add('p_state' ||p_state, 1);
1016 itg_debug_pub.Add('p_phone' ||p_phone, 1);
1017 itg_debug_pub.Add('p_purch_site'||p_purch_site, 1);
1018 itg_debug_pub.Add('p_pay_site' ||p_pay_site, 1);
1019 itg_debug_pub.Add('p_pc_site' ||p_pc_site, 1);
1020 itg_debug_pub.Add('p_rfq_site' ||p_rfq_site, 1);
1021 itg_debug_pub.Add('p_vat_code' ||p_vat_code, 1);
1022 itg_debug_pub.Add('p_org ' ||p_org, 1);
1023 END IF;
1024
1025 validate_vendorsite_params(
1026 p_addrline1 => p_addrline1,
1027 p_addrline2 => p_addrline2,
1028 p_addrline3 => p_addrline3,
1029 p_addrline4 => p_addrline4,
1030 p_city => p_city,
1034 p_fax => p_fax,
1031 p_country => p_country,
1032 p_county => p_county,
1033 p_site_code => p_site_code,
1035 p_zip => p_zip,
1036 p_state => p_state,
1037 p_phone => p_phone,
1038 p_org => p_org,
1039 p_purch_site => p_purch_site,
1040 p_pay_site => p_pay_site,
1041 p_rfq_site => p_rfq_site,
1042 p_pc_site => p_pc_site,
1043 p_vat_code => p_vat_code,
1044 p_vinfo_rec => p_vinfo_rec);
1045
1046 BEGIN
1047 IF (l_Debug_Level <= 1) THEN
1048 itg_debug_pub.Add('Retrieving vendor record - ' || p_vinfo_rec.vendor_id ,1);
1049 END IF;
1050
1051 g_action := 'query vendor details';
1052
1053 SELECT *
1054 INTO l_ven_rec
1055 FROM ap_vendors_v
1056 WHERE vendor_id = p_vinfo_rec.vendor_id;
1057
1058 EXCEPTION
1059 WHEN NO_DATA_FOUND THEN
1060 IF (l_Debug_Level <= 1) THEN
1061 itg_debug_pub.Add('Vendor not found - erroring out' ,1);
1062 END IF;
1063 itg_msg.vendor_not_found('vendorid:' ||p_vinfo_rec.vendor_id);
1064 RAISE FND_API.G_EXC_ERROR;
1065 END;
1066
1067
1068 g_action := 'check for vendor site';
1069 BEGIN
1070 SELECT vendor_site_id
1071 INTO l_vendor_site_id
1072 FROM ap_supplier_sites
1073 WHERE vendor_id = p_vinfo_rec.vendor_id
1074 AND upper(vendor_site_code) = UPPER(p_site_code);
1075
1076 l_vendor_found := true;
1077
1078 IF (l_Debug_Level <= 1) THEN
1079 itg_debug_pub.add('Vendor site obtained as - ' || l_vendor_site_id);
1080 END IF;
1081
1082 EXCEPTION
1083 WHEN NO_DATA_FOUND THEN
1084 IF (l_Debug_Level <= 1) THEN
1085 itg_debug_pub.add('Vendor site not found');
1086 END IF;
1087 l_vendor_found := false;
1088 END;
1089
1090 IF p_org IS NOT NULL THEN
1091 IF (l_Debug_Level <= 1) THEN
1092 itg_debug_pub.Add('SVS - Looking up set_of_books_id',1);
1093 END IF;
1094
1095 OPEN sob_csr(p_org);
1096 FETCH sob_csr
1097 INTO l_ven_rec.set_of_books_id;
1098 l_sob_found := sob_csr%FOUND;
1099 CLOSE sob_csr;
1100
1101
1102 IF l_sob_found THEN
1103 IF (l_Debug_Level <= 1) THEN
1104 itg_debug_pub.Add('SVS - Looking up financial params' ,1);
1105 itg_debug_pub.Add('SVS - set_of_books_id'||l_ven_rec.set_of_books_id,1);
1106 END IF;
1107
1108 OPEN fin_params_csr(p_sob_id => l_ven_rec.set_of_books_id);
1109 FETCH fin_params_csr
1110 INTO l_ship_to_location_id,
1111 l_bill_to_location_id,
1112 l_ship_via_lookup_code,
1113 l_freight_terms_lookup_code,
1114 l_fob_lookup_code,
1115 l_accts_pay_code_comb_id,
1116 l_prepay_code_comb_id;
1117 CLOSE fin_params_csr;
1118 END IF;
1119 END IF;
1120
1121
1122
1123 IF NOT l_vendor_found THEN
1124 IF (l_Debug_Level <= 1) THEN
1125 itg_debug_pub.add('Creating vendor record');
1126 END IF;
1127
1128 g_action := 'vendor site creation';
1129 l_vendorsite_rec.vendor_site_code := p_site_code;
1130 l_vendorsite_rec.PHONE := p_phone;
1131 l_vendorsite_rec.FAX := p_fax;
1132 l_vendorsite_rec.COUNTRY := p_country;
1133 l_vendorsite_rec.ADDRESS_LINE1 := p_addrline1;
1134 l_vendorsite_rec.ADDRESS_LINE2 := p_addrline2;
1135 l_vendorsite_rec.ADDRESS_LINE3 := p_addrline3;
1136 l_vendorsite_rec.ADDRESS_LINE4 := p_addrline4;
1137 l_vendorsite_rec.COUNTY := p_county;
1138 l_vendorsite_rec.CITY := p_city;
1139 l_vendorsite_rec.STATE := p_state;
1140 l_vendorsite_rec.ZIP := p_zip;
1141 l_vendorsite_rec.PURCHASING_SITE_FLAG := p_purch_site;
1142 l_vendorsite_rec.RFQ_ONLY_SITE_FLAG := p_rfq_site;
1143 l_vendorsite_rec.PAY_SITE_FLAG := p_pay_site;
1144 l_vendorsite_rec.PCARD_SITE_FLAG := p_pc_site;
1145 l_vendorsite_rec.payment_priority := 2;
1146 l_vendorsite_rec.TERMS_DATE_BASIS := NVL(l_ven_rec.terms_date_basis, 'Goods Received');
1147 l_vendorsite_rec.SHIP_TO_LOCATION_ID := l_ship_to_location_id;
1148 l_vendorsite_rec.BILL_TO_LOCATION_ID := l_bill_to_location_id;
1152 l_vendorsite_rec.ACCTS_PAY_CODE_COMBINATION_ID := l_accts_pay_code_comb_id;
1149 l_vendorsite_rec.SHIP_VIA_LOOKUP_CODE := l_ship_via_lookup_code;
1150 l_vendorsite_rec.FREIGHT_TERMS_LOOKUP_CODE := l_freight_terms_lookup_code;
1151 l_vendorsite_rec.FOB_LOOKUP_CODE := l_fob_lookup_code;
1153 l_vendorsite_rec.PREPAY_CODE_COMBINATION_ID := l_prepay_code_comb_id;
1154 l_vendorsite_rec.PAY_GROUP_LOOKUP_CODE := l_ven_rec.pay_group_lookup_code;
1155 l_vendorsite_rec.PAY_DATE_BASIS_LOOKUP_CODE := l_ven_rec.pay_date_basis_lookup_code;
1156 l_vendorsite_rec.ALWAYS_TAKE_DISC_FLAG := l_ven_rec.always_take_disc_flag;
1157 l_vendorsite_rec.BANK_CHARGE_BEARER := l_ven_rec.bank_charge_bearer;
1158 l_vendorsite_rec.ALLOW_AWT_FLAG := l_ven_rec.allow_awt_flag;
1159 l_vendorsite_rec.FUTURE_DATED_PAYMENT_CCID := l_future_dated_pay_ccid;
1160 l_vendorsite_rec.CREATE_DEBIT_MEMO_FLAG := l_ven_rec.create_debit_memo_flag;
1161 l_vendorsite_rec.ADDRESS_STYLE := p_vinfo_rec.addr_style;
1162 l_vendorsite_rec.INVOICE_CURRENCY_CODE := NVL(p_vinfo_rec.currency, l_ven_rec.invoice_currency_code);
1163 l_vendorsite_rec.PAYMENT_CURRENCY_CODE := NVL(p_vinfo_rec.currency, l_ven_rec.payment_currency_code);
1164 l_vendorsite_rec.TERMS_ID := NVL(p_vinfo_rec.terms_id, l_ven_rec.terms_id);
1165 l_vendorsite_rec.TERMS_NAME := NVL(p_vinfo_rec.terms_name, l_ven_rec.terms_name);
1166 l_vendorsite_rec.match_option := 'P';
1167 l_vendorsite_rec.vendor_id := p_vinfo_rec.vendor_id;
1168 l_vendorsite_rec.org_id := p_org;
1169
1170 l_api_name := 'ap_vendor_pub_pkg.create_vendor_site';
1171 ap_vendor_pub_pkg.create_vendor_site
1172 (
1173 p_api_version => '1.0',
1174 x_return_status => l_return_status,
1175 x_msg_count => l_msg_count,
1176 x_msg_data => l_ret_msg,
1177 p_vendor_site_rec => l_vendorsite_rec,
1178 x_vendor_site_id => l_vendor_site_id,
1179 x_party_site_id => l_party_id,
1180 x_location_id => l_location_id
1181 );
1182
1183 IF (l_Debug_Level <= 1) THEN
1184 itg_debug_pub.add('return from ap_vendors_pub_pkg.create_vendor_site');
1185 itg_debug_pub.add('l_return_status ' || l_return_status);
1186 itg_debug_pub.add('l_msg_count ' || l_msg_count);
1187 itg_debug_pub.add('l_ret_msg ' || l_ret_msg);
1188 itg_debug_pub.add('l_vendor_site_id ' || l_vendor_site_id);
1189 itg_debug_pub.add('l_party_id ' || l_party_id);
1190 itg_debug_pub.add('l_location_id ' || l_location_id);
1191 END IF;
1192
1193 ELSE
1194
1195 g_action := 'vendor site update';
1196 IF (l_Debug_Level <= 1) THEN
1197 itg_debug_pub.add('Updating vendor record');
1198 END IF;
1199
1200 g_action := 'Vendor-site info update';
1201 get_vendorsite_rec(l_vendorsite_rec,l_vendor_site_id,p_vinfo_rec.vendor_id);
1202
1203 IF (l_Debug_Level <= 1) THEN
1204 itg_debug_pub.add('Retrieved old vendor record information');
1205 END IF;
1206 l_vendorsite_rec.vendor_site_code := null;
1207 l_vendorsite_rec.PHONE := NVL(p_phone, l_vendorsite_rec.phone);
1208 l_vendorsite_rec.FAX := NVL(p_fax, l_vendorsite_rec.fax);
1209 l_vendorsite_rec.COUNTRY := NVL(p_country, l_vendorsite_rec.country);
1210 l_vendorsite_rec.ADDRESS_LINE1 := NVL(p_addrline1, l_vendorsite_rec.address_line1);
1211 l_vendorsite_rec.ADDRESS_LINE2 := NVL(p_addrline2, l_vendorsite_rec.address_line2);
1212 l_vendorsite_rec.ADDRESS_LINE3 := NVL(p_addrline3, l_vendorsite_rec.address_line3);
1213 l_vendorsite_rec.ADDRESS_LINE4 := NVL(p_addrline4, l_vendorsite_rec.address_line4);
1214 l_vendorsite_rec.COUNTY := NVL(p_county, l_vendorsite_rec.county);
1215 l_vendorsite_rec.CITY := NVL(p_city, l_vendorsite_rec.city);
1216 l_vendorsite_rec.STATE := NVL(p_state, l_vendorsite_rec.state);
1217 l_vendorsite_rec.ZIP := NVL(p_zip, l_vendorsite_rec.zip);
1218 l_vendorsite_rec.PURCHASING_SITE_FLAG := NVL(p_purch_site, l_vendorsite_rec.purchasing_site_flag);
1219 l_vendorsite_rec.RFQ_ONLY_SITE_FLAG := NVL(p_rfq_site, l_vendorsite_rec.rfq_only_site_flag);
1220 l_vendorsite_rec.PAY_SITE_FLAG := NVL(p_pay_site, l_vendorsite_rec.pay_site_flag);
1221 l_vendorsite_rec.PCARD_SITE_FLAG := NVL(p_pc_site, l_vendorsite_rec.pcard_site_flag);
1222 l_vendorsite_rec.payment_priority := 2;
1223 l_vendorsite_rec.match_option := 'P';
1224 l_vendorsite_rec.TERMS_ID := NVL(p_vinfo_rec.terms_id, l_vendorsite_rec.terms_id);
1228 l_vendorsite_rec.org_id := p_org;
1225 l_vendorsite_rec.TERMS_NAME := NVL(p_vinfo_rec.terms_name, l_vendorsite_rec.terms_name);
1226 l_vendorsite_rec.PAYMENT_CURRENCY_CODE := NVL(p_vinfo_rec.currency, l_vendorsite_rec.invoice_currency_code);
1227 l_vendorsite_rec.INVOICE_CURRENCY_CODE := NVL(p_vinfo_rec.currency, l_vendorsite_rec.payment_currency_code);
1229 l_vendorsite_rec.vendor_site_id := l_vendor_site_id;
1230
1231 IF (l_Debug_Level <= 1) THEN
1232 itg_debug_pub.add('Calling ap_vendors_pub_pkg.update_vendor_site');
1233 END IF;
1234 l_api_name := 'ap_vendor_pub_pkg.update_vendor_site';
1235 ap_vendor_pub_pkg.update_vendor_site
1236 (
1237 p_api_version => '1.0',
1238 x_return_status => l_return_status,
1239 x_msg_count => l_msg_count,
1240 x_msg_data => l_ret_msg,
1241 p_vendor_site_rec => l_vendorsite_rec,
1242 p_vendor_site_id => l_vendor_site_id
1243 );
1244
1245 IF (l_Debug_Level <= 1) THEN
1246 itg_debug_pub.add('Return from ap_vendors_pub_pkg.update_vendor_site');
1247 itg_debug_pub.add('l_return_status ' || l_return_status);
1248 itg_debug_pub.add('l_msg_count ' || l_msg_count);
1249 itg_debug_pub.add('l_ret_msg ' || l_ret_msg);
1250 itg_debug_pub.add('l_vendor_site_id ' || l_vendor_site_id);
1251 END IF;
1252
1253 /* Adding following block to Fix Bug: 5258874 to update supplier site address locations*/
1254 BEGIN
1255 select location_id into p_location_rec.location_id
1256 from ap_supplier_sites
1257 where vendor_id = p_vinfo_rec.vendor_id and vendor_site_id = l_vendor_site_id;
1258 IF (l_Debug_Level <= 1) THEN
1259 itg_debug_pub.Add('location_id - '|| p_location_rec.location_id,1);
1260 END IF;
1261
1262 EXCEPTION
1263 WHEN NO_DATA_FOUND THEN
1264 IF (l_Debug_Level <= 1) THEN
1265 itg_debug_pub.Add('Couldn''t find location_id ');
1266 END IF;
1267 RAISE FND_API.G_EXC_ERROR;
1268 END;
1269
1270
1271 BEGIN
1272 select object_version_number
1273 into p_object_version_number
1274 from hz_locations
1275 where location_id = p_location_rec.location_id;
1276
1277 IF (l_Debug_Level <= 1) THEN
1278 itg_debug_pub.Add('object_version_number - '|| p_object_version_number);
1279 END IF;
1280
1281 EXCEPTION
1282 WHEN NO_DATA_FOUND THEN
1283 IF (l_Debug_Level <= 1) THEN
1284 itg_debug_pub.Add('Couldn''t find object_version_number');
1285 END IF;
1286 RAISE FND_API.G_EXC_ERROR;
1287
1288 END;
1289
1290 p_location_rec.orig_system_reference := '';
1291 --p_location_rec.orig_system :=
1292 p_location_rec.country := NVL(p_country, l_vendorsite_rec.country);
1293 p_location_rec.address1 := NVL(p_addrline1, l_vendorsite_rec.address_line1);
1294 p_location_rec.address2 := NVL(p_addrline2, l_vendorsite_rec.address_line2);
1295 p_location_rec.address3 := NVL(p_addrline3, l_vendorsite_rec.address_line3);
1296 p_location_rec.address4 := NVL(p_addrline4, l_vendorsite_rec.address_line4);
1297 p_location_rec.city := NVL(p_city, l_vendorsite_rec.city);
1298 p_location_rec.postal_code := NVL(p_zip, l_vendorsite_rec.zip);
1299 p_location_rec.state := NVL(p_state, l_vendorsite_rec.state);
1300 p_location_rec.province := NVL(p_state, l_vendorsite_rec.state);
1301 p_location_rec.county := NVL(p_county, l_vendorsite_rec.county);
1302
1303 IF (l_Debug_Level <= 1) THEN
1304 itg_debug_pub.add('Calling hz_location_v2pub.update_location ');
1305
1306 END IF;
1307 l_api_name := 'hz_location_v2pub.update_location';
1308 hz_location_v2pub.update_location
1309 (
1310 p_init_msg_list => FND_API.G_FALSE,
1311 p_location_rec => p_location_rec,
1312 p_object_version_number => p_object_version_number,
1313 x_return_status => l_return_status,
1314 x_msg_count => l_msg_count,
1315 x_msg_data => l_ret_msg
1316
1317 );
1318 IF (l_Debug_Level <= 1) THEN
1319 itg_debug_pub.add('Return from hz_location_v2pub.update_location');
1320 itg_debug_pub.add('l_return_status ' || l_return_status);
1321 itg_debug_pub.add('l_msg_count ' || l_msg_count);
1322 itg_debug_pub.add('l_ret_msg ' || l_ret_msg);
1323 End if;
1324 END IF;
1325
1326 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1327
1328 IF (l_Debug_Level <= 1) THEN
1329 itg_debug_pub.add('Create/Update_vendor_site/update_location API returns - ' || l_return_status || ' - ' || l_ret_msg);
1330 END IF;
1331 itg_msg.apicallret(l_api_name, l_return_status, l_ret_msg);
1332 RAISE FND_API.G_EXC_ERROR;
1333 END IF;
1334
1338
1335 IF (l_Debug_Level <= 1) THEN
1336 itg_debug_pub.Add('Commiting work',1);
1337 END IF;
1339 COMMIT WORK;
1340
1341 IF (l_Debug_Level <= 2) THEN
1342 itg_debug_pub.Add('EXITING - Sync_VendorSite.', 2);
1343 END IF;
1344
1345 EXCEPTION
1346 WHEN FND_API.G_EXC_ERROR THEN
1347 ROLLBACK TO Sync_VendorSite_PVT;
1348 commit;
1349 x_return_status := FND_API.G_RET_STS_ERROR;
1350 ITG_msg.checked_error(g_action);
1351 itg_msg.vendor_site_only;
1352 IF (l_Debug_Level <= 6) THEN
1353 itg_debug_pub.Add('EXITING - Sync_VendorSite :ERROR', 6);
1354 END IF;
1355
1356
1357 WHEN OTHERS THEN
1358 ROLLBACK TO Sync_VendorSite_PVT;
1359 commit;
1360 ITG_msg.unexpected_error(g_action);
1361 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1362 itg_msg.vendor_site_only;
1363 itg_debug.msg('Unexpected error (VendorSite sync) - ' || substr(SQLERRM,1,255),true);
1364 IF (l_Debug_Level <= 6) THEN
1365 itg_debug_pub.Add('EXITING - Sync_VendorSite :ERROR', 6);
1366 END IF;
1367
1368
1369 END Sync_VendorSite;
1370
1371
1372 PROCEDURE Sync_VendorContact(
1373 x_return_status OUT NOCOPY VARCHAR2, /* VARCHAR2(1) */
1374 x_msg_count OUT NOCOPY NUMBER,
1375 x_msg_data OUT NOCOPY VARCHAR2, /* VARCHAR2(2000) */
1376
1377 p_title IN VARCHAR2 := NULL, /* contcttype */
1378 p_first_name IN VARCHAR2 := NULL, /* name index=1 */
1379 p_middle_name IN VARCHAR2 := NULL, /* name index=2 */
1380 p_last_name IN VARCHAR2 := NULL, /* name index=3 */
1381 p_phone IN VARCHAR2 := NULL, /* telephone index=1 */
1382 p_site_code IN VARCHAR2, /* userarea.ref_sitecode */
1383
1384 p_vinfo_rec IN vinfo_rec_type
1385 ) IS
1386 r_vendor_contact_rec ap_vendor_pub_pkg.r_vendor_contact_rec_type;
1387 l_found Boolean;
1388 l_ret_status varchar2(20);
1389 l_msg_data varchar2(2000);
1390 l_ret_msg varchar2(2000);
1391 l_msg_count NUMBER;
1392 l_party_site_id NUMBER;
1393 l_org_contact_id NUMBER;
1394 l_rel_id NUMBER;
1395 l_rel_party_id NUMBER;
1396 l_per_party_id NUMBER;
1397 l_vendor_contact_id NUMBER;
1398 l_vsite_id NUMBER;
1399 l_obj_ver_num NUMBER;
1400 l_party_id NUMBER;
1401 l_profile_id NUMBER;
1402 l_party_rec HZ_PARTY_V2PUB.party_rec_type;
1403 l_per_rec HZ_PARTY_V2PUB.person_rec_type;
1404 BEGIN
1405 x_return_status := FND_API.G_RET_STS_SUCCESS;
1406 g_action := 'Vendor-contact parameter validation';
1407
1408 IF (l_Debug_Level <= 2) THEN
1409 itg_debug_pub.Add('ENTERING - Sync_VendorContact', 2);
1410 itg_debug_pub.Add('p_title - ' || p_title, 2);
1411 itg_debug_pub.Add('p_first_name - ' || p_first_name, 2);
1412 itg_debug_pub.Add('p_middle_name - ' || p_middle_name, 2);
1413 itg_debug_pub.Add('p_last_name - ' || p_last_name, 2);
1414 itg_debug_pub.Add('p_phone - ' || p_phone, 2);
1415 itg_debug_pub.Add('p_site_code - ' || p_site_code, 2);
1416 END IF;
1417
1418 -- sync vendor
1419 SAVEPOINT Sync_VendorContact_PVT;
1420
1421
1422 BEGIN
1423
1424 g_action := 'Vendor-contact information sync';
1425
1426 SELECT vendor_site_id
1427 INTO l_vsite_id
1428 FROM ap_supplier_sites
1429 WHERE UPPER(vendor_site_code) = UPPER(p_site_code)
1430 AND vendor_id = p_vinfo_rec.vendor_id;
1431
1432 IF (l_Debug_Level <= 1) THEN
1433 itg_debug_pub.Add('Getting vendor site ID - ' || l_vsite_id ,1);
1434 END IF;
1435 EXCEPTION
1436 WHEN NO_DATA_FOUND THEN
1437 itg_msg.no_vendor_site('vendor-sitecode:' || p_site_code);
1438 RAISE FND_API.G_EXC_ERROR;
1439 END;
1440
1441 BEGIN
1442
1443 g_action := 'Vendor-contact information sync';
1444
1445 select h.party_id, h.object_version_number
1446 into l_party_id,l_obj_ver_num
1447 from HZ_PARTIES h, ap_supplier_contacts a
1448 where a.per_party_id = h.party_id
1449 and a.vendor_site_id = l_vsite_id
1450 AND NVL(upper(person_first_name), '1') = NVL(upper(p_first_name), '1')
1451 AND NVL(upper(person_middle_name), '1') = NVL(upper(p_middle_name), '1')
1452 AND NVL(upper(person_last_name), '1') = NVL(upper(p_last_name), '1')
1453 AND ROWNUM = 1;
1454
1455 l_found := true;
1456
1457 IF (l_Debug_Level <= 1) THEN
1458 itg_debug_pub.Add('Contact party id - ' || l_party_id ,1);
1459 itg_debug_pub.Add('Contact obj version - ' || l_obj_ver_num ,1);
1463 EXCEPTION
1460 END IF;
1461
1462
1464 WHEN NO_DATA_FOUND THEN
1465 IF (l_Debug_Level <= 5) THEN
1466 itg_debug_pub.Add('SVC - Contact record not found, trying to add it' ,5);
1467 END IF;
1468 l_found := false;
1469 END;
1470
1471
1472
1473 IF NOT l_found THEN
1474 g_action := 'Vendor-contact record creation';
1475 r_vendor_contact_rec.PERSON_FIRST_NAME := p_first_name;
1476 r_vendor_contact_rec.PERSON_MIDDLE_NAME := p_middle_name;
1477 r_vendor_contact_rec.PERSON_LAST_NAME := p_last_name;
1478 r_vendor_contact_rec.PERSON_TITLE := p_title;
1479 r_vendor_contact_rec.PHONE := p_phone;
1480 r_vendor_contact_rec.VENDOR_SITE_CODE := p_site_code;
1481 r_vendor_contact_rec.VENDOR_SITE_ID := l_vsite_id;
1482 r_vendor_contact_rec.VENDOR_ID := p_vinfo_rec.vendor_id;
1483 r_vendor_contact_rec.PERSON_TITLE := p_title;
1484
1485 Ap_vendor_pub_pkg.Create_Vendor_Contact
1486 (
1487 p_api_version => 1.0,
1488 x_return_status => l_ret_status,
1489 x_msg_count => l_msg_count,
1490 x_msg_data => l_msg_data,
1491 p_vendor_contact_rec => r_vendor_contact_rec,
1492 x_vendor_contact_id => l_vendor_contact_id,
1493 x_per_party_id => l_per_party_id,
1494 x_rel_party_id => l_rel_party_id,
1495 x_rel_id => l_rel_id,
1496 x_org_contact_id => l_org_contact_id,
1497 x_party_site_id => l_party_site_id
1498 );
1499
1500 IF l_debug_level <= 1 THEN
1501 itg_debug_pub.Add('Create_Vendor_Contact - ' || l_ret_status || ' - ' || l_msg_data ,1);
1502 itg_debug_pub.Add('l_per_party_id - ' || l_per_party_id,1);
1503 itg_debug_pub.Add('l_rel_party_id - ' || l_rel_party_id,1);
1504 itg_debug_pub.Add('l_rel_id - ' || l_rel_id,1 );
1505 itg_debug_pub.Add('l_org_contact_id - ' || l_org_contact_id,1 );
1506 itg_debug_pub.Add('l_party_site_id - ' || l_party_site_id,1 );
1507 END IF;
1508
1509 IF l_ret_status <> FND_API.G_RET_STS_SUCCESS THEN
1510 itg_msg.apicallret('Ap_vendor_pub_pkg.Create_Vendor_Contact',l_ret_status,l_msg_data);
1511 RAISE FND_API.G_EXC_ERROR;
1512 END IF;
1513 ELSE
1514 g_action := 'Vendor-contact record update';
1515 l_party_rec.party_id := l_party_id;
1516 l_per_rec.person_first_name := p_first_name;
1517 l_per_rec.person_middle_name := p_middle_name;
1518 l_per_rec.person_last_name := p_last_name;
1519 l_per_rec.person_title := p_title;
1520 l_per_rec.created_by_module := 'AP_SUPPLIERS_API';
1521 l_per_rec.application_id := 200;
1522 l_per_rec.party_rec := l_party_rec;
1523
1524 HZ_PARTY_V2PUB.update_person (
1525 p_person_rec => l_per_rec,
1526 p_party_object_version_number => l_obj_ver_num,
1527 x_profile_id => l_profile_id,
1528 x_return_status => l_ret_status,
1529 x_msg_count => l_msg_count,
1530 x_msg_data => l_msg_data);
1531
1532 IF l_debug_level <= 1 THEN
1533 itg_debug_pub.Add('HZ_PARTY_V2PUB.update_person - ' || l_ret_status || ' - ' || l_msg_data ,1);
1534 itg_debug_pub.Add('l_obj_ver_num - ' || l_msg_count,1);
1535 itg_debug_pub.Add('l_profile_id - ' || l_obj_ver_num,1);
1536 itg_debug_pub.Add('l_msg_count - ' || l_profile_id,1 );
1537
1538 END IF;
1539
1540 IF l_ret_status <> FND_API.G_RET_STS_SUCCESS THEN
1541 itg_msg.apicallret('HZ_PARTY_V2PUB.update_person',l_ret_status,l_msg_data);
1542 RAISE FND_API.G_EXC_ERROR;
1543 END IF;
1544
1545 END IF;
1546
1547 IF (l_Debug_Level <= 1) THEN
1548 itg_debug_pub.Add('Committing work' ,1);
1549 END IF;
1550
1551 COMMIT WORK;
1552
1553 IF (l_Debug_Level <= 2) THEN
1554 itg_debug_pub.Add('EXTING - Sync_VendorContact', 2);
1555 END IF;
1556
1557
1558 EXCEPTION
1559 WHEN FND_API.G_EXC_ERROR THEN
1560 ROLLBACK TO Sync_VendorContact_PVT;
1561 commit;
1562 x_return_status := FND_API.G_RET_STS_ERROR;
1563 ITG_msg.checked_error(g_action);
1564 itg_msg.vendor_contact_only;
1565 IF (l_Debug_Level <= 6) THEN
1566 itg_debug_pub.Add('EXTING - Sync_VendorContact :OTHER ERROR', 6);
1567 END IF;
1568
1569
1570 WHEN OTHERS THEN
1571 ROLLBACK TO Sync_VendorContact_PVT;
1572 commit;
1573 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1574 ITG_msg.unexpected_error(g_action);
1575 itg_msg.vendor_contact_only;
1576 itg_debug.msg('Unexpected error (VendorContact sync) - ' || substr(SQLERRM,1,255),true);
1577 IF (l_Debug_Level <= 6) THEN
1578 itg_debug_pub.Add('EXTING - Sync_VendorContact :OTHER ERROR', 6);
1579 END IF;
1580
1581 END Sync_VendorContact;
1582
1583 END ITG_SyncSupplierInbound_PVT;