[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
FUNCTION insert_rejections
(
p_batch_id IN NUMBER,
p_import_request_id IN NUMBER,
p_parent_table IN VARCHAR2,
p_parent_id IN NUMBER,
p_reject_code IN VARCHAR2,
p_last_updated_by IN NUMBER,
p_last_update_login IN NUMBER,
p_calling_sequence IN VARCHAR2
) RETURN BOOLEAN IS
l_current_calling_sequence VARCHAR2(2000);
l_api_name CONSTANT VARCHAR2(100) := 'INSERT_REJECTIONS';
l_current_calling_sequence := 'POS_BATCH_IMPORT_PKG.Insert_rejections<-' ||
p_calling_sequence;
INSERT INTO pos_supplier_int_rejections
(batch_id,
import_request_id,
parent_table,
parent_id,
reject_lookup_code,
last_updated_by,
last_update_date,
last_update_login,
created_by,
creation_date)
VALUES
(p_batch_id,
p_import_request_id,
p_parent_table,
p_parent_id,
p_reject_code,
p_last_updated_by,
SYSDATE,
p_last_update_login,
p_last_updated_by,
SYSDATE);
' Inside INSERT_REJECTIONS EXCEPTION ' ||
' Message: ' || SQLCODE || ' ' || SQLERRM);
END insert_rejections;
SELECT owner_table_id
INTO l_party_id
FROM hz_orig_sys_references hr
WHERE hr.owner_table_name = 'HZ_PARTIES'
AND hr.orig_system = p_orig_system
AND hr.orig_system_reference = p_orig_system_reference
AND hr.status = 'A'
AND nvl(hr.end_date_active, SYSDATE) >= SYSDATE;
l_insert_count NUMBER(10);
UPDATE ap_suppliers_int supp
SET party_id =
(SELECT party_id
FROM hz_orig_sys_references hr
WHERE hr.owner_table_name = 'HZ_PARTIES'
AND hr.orig_system = supp.party_orig_system
AND hr.orig_system_reference =
supp.party_orig_system_reference
AND hr.status = 'A'
AND nvl(hr.end_date_active, SYSDATE) >= SYSDATE)
WHERE party_id IS NULL
AND sdh_batch_id = p_batch_id
AND nvl(status, 'ACTIVE') <> 'PROCESSED';
INSERT INTO hz_imp_parties_int
(batch_id,
party_orig_system,
party_orig_system_reference,
created_by_module,
--application_id,
party_type,
organization_name,
organization_name_phonetic,
jgzz_fiscal_code,
tax_reference,
ceo_name,
ceo_title)
SELECT sdh_batch_id,
party_orig_system,
party_orig_system_reference,
'AP_SUPPLIERS_API',
--200,
'ORGANIZATION',
vendor_name,
vendor_name_alt,
num_1099,
vat_registration_num,
ceo_name,
ceo_title
FROM ap_suppliers_int supp
WHERE /* party_id IS NULL AND */
sdh_batch_id = p_batch_id
AND nvl(status, 'ACTIVE') <> 'PROCESSED'
AND NOT EXISTS (SELECT *
FROM hz_imp_parties_int hp
WHERE hp.batch_id = supp.sdh_batch_id
AND hp.party_orig_system = supp.party_orig_system
AND hp.party_orig_system_reference =
supp.party_orig_system_reference);
l_insert_count := SQL%ROWCOUNT;
' Rows inserted in hz_imp_parties_int: ' ||
l_insert_count);
IF (l_insert_count > 0) THEN
-- Update hz_imp_batch_summary with the count of the records inserted into interface table.
UPDATE hz_imp_batch_summary
SET total_batch_records = total_batch_records + l_insert_count,
total_records_for_import = total_records_for_import +
l_insert_count,
parties_in_batch = parties_in_batch + l_insert_count
WHERE batch_id = p_batch_id;
l_insert_count NUMBER(10);
UPDATE ap_supplier_sites_int supp
SET party_site_id =
(SELECT owner_table_id
FROM hz_orig_sys_references hr
WHERE hr.owner_table_name = 'HZ_PARTY_SITES'
AND hr.party_id = supp.party_id -- Bug 14772702: Same site orig reference can exist under different suppliers
AND hr.orig_system = supp.party_site_orig_system
AND hr.orig_system_reference =
supp.party_site_orig_sys_reference
AND hr.status = 'A'
AND nvl(hr.end_date_active, SYSDATE) >= SYSDATE)
WHERE party_site_id IS NULL
AND sdh_batch_id = p_batch_id
AND nvl(status, 'ACTIVE') <> 'PROCESSED';
INSERT INTO hz_imp_addresses_int
(batch_id,
party_orig_system,
party_orig_system_reference,
site_orig_system,
site_orig_system_reference,
party_site_name,
address1,
address_lines_phonetic,
address2,
address3,
address4,
city,
state,
country,
province,
county,
postal_code,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
language,
party_id,
correct_move_indicator) --BUG 16200981 Specify correct mode when doing update
SELECT sdh_batch_id,
party_orig_system,
party_orig_system_reference,
party_site_orig_system,
party_site_orig_sys_reference,
NVL(party_site_name, vendor_site_code), -- Bug 14088081: should populate party_site_name to TCA table as we already have such column.
address_line1,
address_lines_alt,
address_line2,
address_line3,
address_line4,
city,
state,
country,
province,
county,
zip,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
language_code,
party_id,
'C' --BUG 16200981 Specify correct mode when doing update
FROM (SELECT sdh_batch_id,
party_orig_system,
party_orig_system_reference,
party_site_orig_system,
party_site_orig_sys_reference,
/*nvl(party_site_orig_system, supplier_site_orig_system) party_site_orig_system,
nvl(party_site_orig_sys_reference,
sup_site_orig_system_reference) party_site_orig_sys_reference,*/
vendor_site_code,
party_site_name,
address_line1,
address_lines_alt,
address_line2,
address_line3,
address_line4,
city,
state,
country,
province,
county,
zip,
hz_utility_v2pub.created_by,
hz_utility_v2pub.creation_date,
hz_utility_v2pub.last_updated_by,
hz_utility_v2pub.last_update_date,
hz_utility_v2pub.last_update_login,
LANG.LANGUAGE_CODE,
party_id,
dense_rank() over(PARTITION BY sdh_batch_id, party_site_orig_system, party_site_orig_sys_reference, party_id ORDER BY SUPP.ROWID) rnk
FROM ap_supplier_sites_int supp, FND_LANGUAGES LANG
WHERE /* party_site_id IS NULL AND */
sdh_batch_id = p_batch_id AND supp.language = lang.nls_language(+)
AND nvl(status, 'ACTIVE') <> 'PROCESSED'
AND NOT EXISTS
(SELECT *
FROM hz_imp_addresses_int hp
WHERE hp.batch_id = supp.sdh_batch_id
AND hp.party_orig_system = supp.party_orig_system
AND hp.party_orig_system_reference =
supp.party_orig_system_reference
AND hp.site_orig_system = supp.party_site_orig_system
AND hp.site_orig_system_reference =
supp.party_site_orig_sys_reference))
WHERE rnk = 1;
l_insert_count := SQL%ROWCOUNT;
' Rows inserted in hz_imp_addresses_int: ' ||
l_insert_count);
IF (l_insert_count > 0) THEN
-- Update hz_imp_batch_summary with the count of the records inserted into interface table.
UPDATE hz_imp_batch_summary
SET total_batch_records = total_batch_records + l_insert_count,
total_records_for_import = total_records_for_import +
l_insert_count,
addresses_in_batch = addresses_in_batch + l_insert_count
WHERE batch_id = p_batch_id;
l_insert_count NUMBER;
UPDATE ap_sup_site_contact_int supp
SET per_party_id =
(SELECT party_id
FROM hz_orig_sys_references hr
WHERE hr.owner_table_name = 'HZ_PARTIES'
AND hr.orig_system = supp.contact_orig_system
AND hr.orig_system_reference =
supp.contact_orig_system_reference
AND hr.status = 'A'
AND nvl(hr.end_date_active, SYSDATE) >= SYSDATE)
WHERE per_party_id IS NULL
AND sdh_batch_id = p_batch_id
AND nvl(status, 'ACTIVE') <> 'PROCESSED';
INSERT INTO hz_imp_parties_int
(batch_id,
party_orig_system,
party_orig_system_reference,
created_by_module,
--application_id,
party_type,
organization_name,
organization_name_phonetic,
person_first_name,
person_last_name,
--Bug 13392627
person_middle_name,
person_pre_name_adjunct
--person_title
--jgzz_fiscal_code,
--tax_reference
)
SELECT sdh_batch_id,
contact_orig_system,
contact_orig_system_reference,
'AP_SUPPLIERS_API',
'PERSON',
org_name,
org_name_alt,
first_name,
last_name,
middle_name,
prefix
FROM (SELECT sdh_batch_id,
contact_orig_system,
contact_orig_system_reference,
'AP_SUPPLIERS_API',
--200,
'PERSON',
supp.first_name || ' ' || supp.last_name org_name,
supp.first_name_alt || ' ' || supp.last_name_alt org_name_alt,
supp.first_name,
supp.last_name,
supp.middle_name,
supp.prefix,
dense_rank() over(PARTITION BY sdh_batch_id, contact_orig_system, contact_orig_system_reference, party_id ORDER BY ROWID) rnk
--num_1099,
--vat_registration_num
FROM ap_sup_site_contact_int supp
WHERE /* per_party_id IS NULL AND */
sdh_batch_id = p_batch_id
AND nvl(status, 'ACTIVE') <> 'PROCESSED'
AND NOT EXISTS
(SELECT *
FROM hz_imp_parties_int hp
WHERE hp.batch_id = supp.sdh_batch_id
AND hp.party_orig_system = supp.contact_orig_system
AND hp.party_orig_system_reference =
supp.contact_orig_system_reference))
WHERE rnk = 1;
l_insert_count := SQL%ROWCOUNT;
' Rows inserted in hz_imp_parties_int: ' ||
l_insert_count);
IF (l_insert_count > 0) THEN
-- Update hz_imp_batch_summary with the count of the records inserted into interface table.
UPDATE hz_imp_batch_summary
SET total_batch_records = total_batch_records + l_insert_count,
total_records_for_import = total_records_for_import +
l_insert_count,
parties_in_batch = parties_in_batch + l_insert_count
WHERE batch_id = p_batch_id;
l_insert_count := 0;
INSERT INTO hz_imp_contacts_int
(batch_id,
contact_orig_system,
contact_orig_system_reference,
sub_orig_system,
sub_orig_system_reference,
obj_orig_system,
obj_orig_system_reference,
start_date,
created_by_module,
contact_number,
relationship_type,
relationship_code,
creation_date,
insert_update_flag,
department, --Bug 13392627
JOB_TITLE
)
SELECT sdh_batch_id,
contact_orig_system,
contact_orig_system_reference,
contact_orig_system,
contact_orig_system_reference,
party_orig_system,
party_orig_system_reference,
SYSDATE,
'AP_SUPPLIERS_API',
supp.first_name || ' ' || supp.last_name,
'CONTACT',
'CONTACT_OF',
SYSDATE,
'I',
department,
title
FROM ap_sup_site_contact_int supp
WHERE per_party_id IS NULL
AND sdh_batch_id = p_batch_id
AND nvl(status, 'ACTIVE') <> 'PROCESSED'
AND NOT EXISTS
(SELECT *
FROM hz_imp_contacts_int hp
WHERE hp.batch_id = supp.sdh_batch_id
AND hp.contact_orig_system = supp.contact_orig_system
AND hp.contact_orig_system_reference =
supp.contact_orig_system_reference)
UNION
SELECT sdh_batch_id,
contact_orig_system,
contact_orig_system_reference,
contact_orig_system,
contact_orig_system_reference,
party_orig_system,
party_orig_system_reference,
SYSDATE,
'AP_SUPPLIERS_API',
supp.first_name || ' ' || supp.last_name,
'CONTACT',
'CONTACT_OF',
SYSDATE,
'U',
department,
title
FROM ap_sup_site_contact_int supp
WHERE per_party_id IS NOT NULL
AND sdh_batch_id = p_batch_id
AND nvl(status, 'ACTIVE') <> 'PROCESSED'
AND NOT EXISTS
(SELECT *
FROM hz_imp_contacts_int hp
WHERE hp.batch_id = supp.sdh_batch_id
AND hp.contact_orig_system = supp.contact_orig_system
AND hp.contact_orig_system_reference =
supp.contact_orig_system_reference);
l_insert_count := SQL%ROWCOUNT;
' Rows inserted in hz_imp_contacts_int: ' ||
l_insert_count);
IF (l_insert_count > 0) THEN
-- Update hz_imp_batch_summary with the count of the records inserted into interface table.
UPDATE hz_imp_batch_summary
SET total_batch_records = total_batch_records + l_insert_count,
total_records_for_import = total_records_for_import +
l_insert_count,
contacts_in_batch = contacts_in_batch + l_insert_count
WHERE batch_id = p_batch_id;
SELECT ROWID,
organization_name,
organization_name_phonetic,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
party_id,
party_orig_system,
party_orig_system_reference,
insert_update_flag
FROM hz_imp_parties_int hp
WHERE batch_id = p_batch_id
/* Status for the data should not be E or C
i.e. Errored out or Completed */
AND interface_status IS NULL
AND party_type = 'ORGANIZATION'
AND NOT EXISTS
(SELECT *
FROM ap_suppliers_int supp
WHERE hp.batch_id = supp.sdh_batch_id
AND hp.party_orig_system = supp.party_orig_system
AND hp.party_orig_system_reference =
supp.party_orig_system_reference);
SELECT 1,
vendor_id
FROM ap_suppliers
WHERE party_id = cp_party_id;
DELETE ap_supplier_int_rejections
WHERE parent_table = 'AP_SUPPLIERS_INT';
/* Call validate vendor in update mode */
ap_vendor_pub_pkg.validate_vendor(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_vendor_rec => vendor_rec,
p_mode => 'U',
p_calling_prog => 'NOT ISETUP',
x_party_valid => l_party_valid,
x_payee_valid => l_payee_valid,
p_vendor_id => l_vendor_id);
/* Call validate vendor in insert mode */
ap_vendor_pub_pkg.validate_vendor(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_vendor_rec => vendor_rec,
p_mode => 'I',
p_calling_prog => 'NOT ISETUP',
x_party_valid => l_party_valid,
x_payee_valid => l_payee_valid,
p_vendor_id => l_vendor_id);
INSERT INTO pos_supplier_int_rejections
(batch_id,
import_request_id,
parent_table,
parent_id,
reject_lookup_code,
last_updated_by,
last_update_date,
last_update_login,
created_by,
creation_date)
SELECT p_batch_id,
l_request_id,
parent_table,
parent_id,
reject_lookup_code,
last_updated_by,
last_update_date,
last_update_login,
created_by,
creation_date
FROM ap_supplier_int_rejections
WHERE parent_table = 'AP_SUPPLIERS_INT';
INSERT INTO hz_imp_errors
(creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
program_application_id,
program_id,
program_update_date,
error_id,
batch_id,
request_id,
interface_table_name,
message_name,
token1_name,
token1_value)
VALUES
(SYSDATE,
g_user_id,
SYSDATE,
g_user_id,
g_user_id,
NULL,
NULL,
SYSDATE,
hz_imp_errors_s.nextval,
p_batch_id,
NULL,
'HZ_IMP_PARTIES_INT',
'AR_ALL_DUP_NAME',
'ORGANIZATION_NAME',
party_int_rec.organization_name)
RETURNING error_id INTO l_error_id;
UPDATE hz_imp_parties_int
SET interface_status = 'E',
error_id = l_error_id
WHERE ROWID = party_int_rec.rowid;
PROCEDURE update_contact_dtls
(
p_batch_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_per_party_id NUMBER(15);
SELECT *
FROM ap_sup_site_contact_int supp
WHERE sdh_batch_id = p_batch_id
AND nvl(status, 'ACTIVE') NOT IN ('PROCESSED', 'REMOVED')
AND NOT EXISTS
(SELECT 1
FROM hz_imp_contacts_int party
WHERE batch_id = p_batch_id
AND supp.sdh_batch_id = party.batch_id
AND supp.party_orig_system = party.obj_orig_system
AND supp.party_orig_system_reference =
party.obj_orig_system_reference
AND supp.contact_orig_system = party.contact_orig_system
AND supp.contact_orig_system_reference =
party.contact_orig_system_reference
AND party.interface_status = 'R');
UPDATE ap_sup_site_contact_int
SET party_id = l_org_party_id
WHERE vendor_contact_interface_id =
supp_contact_int_rec.vendor_contact_interface_id
AND sdh_batch_id = p_batch_id;
SELECT party_id
INTO l_per_party_id
FROM hz_parties
WHERE party_name = supp_contact_int_rec.first_name || ' ' ||
supp_contact_int_rec.last_name
AND party_type = 'PERSON';
UPDATE ap_sup_site_contact_int
SET per_party_id = l_per_party_id
WHERE vendor_contact_interface_id =
supp_contact_int_rec.vendor_contact_interface_id
AND sdh_batch_id = p_batch_id;
SELECT relationship_id,
party_id
INTO l_relationship_id,
l_rel_party_id
FROM hz_relationships
WHERE subject_id = l_org_party_id
AND subject_type = 'ORGANIZATION'
AND object_id = l_per_party_id
AND object_type = 'PERSON';
UPDATE ap_sup_site_contact_int
SET relationship_id = l_relationship_id,
rel_party_id = l_rel_party_id
WHERE vendor_contact_interface_id =
supp_contact_int_rec.vendor_contact_interface_id
AND sdh_batch_id = p_batch_id;
SELECT org_contact_id
INTO l_org_contact_id
FROM hz_org_contacts
WHERE party_relationship_id = l_relationship_id;
UPDATE ap_sup_site_contact_int
SET org_contact_id = l_org_contact_id
WHERE vendor_contact_interface_id =
supp_contact_int_rec.vendor_contact_interface_id
AND sdh_batch_id = p_batch_id;
' Message: Inside PROCEDURE update_contact_dtls' ||
' Updated details for vendor_contact_interface_id: ' ||
supp_contact_int_rec.vendor_contact_interface_id ||
' party_id: ' || l_org_party_id || ' per_party_id: ' ||
l_per_party_id || ' relationship_id: ' ||
l_relationship_id || ' rel_party_id: ' ||
l_rel_party_id || ' org_contact_id: ' ||
l_org_contact_id);
' Inside update_contact_dtls EXCEPTION ' ||
' Message: ' || SQLCODE || ' ' || SQLERRM);
END update_contact_dtls;
PROCEDURE update_party_id
(
p_batch_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
BEGIN
-- Initialize API return status to success
x_return_status := fnd_api.g_ret_sts_success;
UPDATE ap_suppliers_int supp
SET supp.party_id =
(SELECT party_id
FROM hz_orig_sys_references hr
WHERE hr.owner_table_name = 'HZ_PARTIES'
AND hr.orig_system = supp.party_orig_system
AND hr.orig_system_reference =
supp.party_orig_system_reference
AND hr.status = 'A'
AND nvl(hr.end_date_active, SYSDATE) >= SYSDATE)
WHERE supp.party_id IS NULL
AND supp.sdh_batch_id = p_batch_id;
' Inside update_party_id EXCEPTION ' ||
' Message: ' || SQLCODE || ' ' || SQLERRM);
END update_party_id;
PROCEDURE update_party_site_id
(
p_batch_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
BEGIN
-- Initialize API return status to success
x_return_status := fnd_api.g_ret_sts_success;
UPDATE ap_supplier_sites_int supp
SET supp.party_id =
(SELECT party_id
FROM hz_orig_sys_references hr
WHERE hr.owner_table_name = 'HZ_PARTIES'
AND hr.orig_system = supp.party_orig_system
AND hr.orig_system_reference =
supp.party_orig_system_reference
AND hr.status = 'A'
AND nvl(hr.end_date_active, SYSDATE) >= SYSDATE)
WHERE supp.party_id IS NULL
AND supp.sdh_batch_id = p_batch_id;
UPDATE ap_supplier_sites_int supp
SET party_site_id =
(SELECT owner_table_id
FROM hz_orig_sys_references hr
WHERE hr.owner_table_name = 'HZ_PARTY_SITES'
AND hr.orig_system = supp.party_site_orig_system
AND hr.orig_system_reference =
supp.party_site_orig_sys_reference
AND hr.status = 'A'
AND nvl(hr.end_date_active, SYSDATE) >= SYSDATE)
WHERE supp.party_site_id IS NULL
AND supp.sdh_batch_id = p_batch_id;
' Inside update_party_site_id EXCEPTION ' ||
' Message: ' || SQLCODE || ' ' || SQLERRM);
END update_party_site_id;
SELECT supplier_numbering_method
INTO l_ven_num_code
FROM ap_product_setup;
SELECT organization_name,
organization_name_phonetic,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
party_id,
party_orig_system,
party_orig_system_reference,
insert_update_flag
FROM hz_imp_parties_int hp
WHERE batch_id = p_batch_id
/* Status for the data should not be E or C
i.e. Errored out or Completed */
AND interface_status IS NULL
AND party_type = 'ORGANIZATION'
AND NOT EXISTS
(SELECT *
FROM ap_suppliers_int supp
WHERE hp.batch_id = supp.sdh_batch_id
AND hp.party_orig_system = supp.party_orig_system
AND hp.party_orig_system_reference =
supp.party_orig_system_reference);
SELECT 1 FROM ap_suppliers WHERE party_id = cp_party_id;
DELETE ap_supplier_int_rejections
WHERE parent_table = 'AP_SUPPLIERS_INT';
/*IF (nvl(party_int_rec.insert_update_flag, 'I') = 'U') THEN*/
IF party_int_rec.insert_update_flag IS NULL THEN
-- Check if the vendor already exists
OPEN check_vendor_exsists(vendor_rec.party_id);
INSERT INTO pos_supplier_int_rejections
(batch_id,
import_request_id,
parent_table,
parent_id,
reject_lookup_code,
last_updated_by,
last_update_date,
last_update_login,
created_by,
creation_date)
SELECT p_batch_id,
l_request_id,
parent_table,
parent_id,
reject_lookup_code,
last_updated_by,
last_update_date,
last_update_login,
created_by,
creation_date
FROM ap_supplier_int_rejections
WHERE parent_table = 'AP_SUPPLIERS_INT';
ELSIF party_int_rec.insert_update_flag = 'I' THEN
/* Call create vendor API to create a supplier */
ap_vendor_pub_pkg.create_vendor(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_false,
p_validation_level => fnd_api.g_valid_level_full,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_vendor_rec => vendor_rec,
x_vendor_id => l_vendor_id,
x_party_id => l_party_id);
INSERT INTO pos_supplier_int_rejections
(batch_id,
import_request_id,
parent_table,
parent_id,
reject_lookup_code,
last_updated_by,
last_update_date,
last_update_login,
created_by,
creation_date)
SELECT p_batch_id,
l_request_id,
parent_table,
parent_id,
reject_lookup_code,
last_updated_by,
last_update_date,
last_update_login,
created_by,
creation_date
FROM ap_supplier_int_rejections
WHERE parent_table = 'AP_SUPPLIERS_INT';
ELSIF party_int_rec.insert_update_flag = 'U' THEN
ap_tca_supplier_sync_pkg.sync_supplier(l_sync_return_status,
l_sync_msg_count,
l_sync_msg_data,
vendor_rec.party_id);
SELECT contact_orig_system,
contact_orig_system_reference
FROM hz_imp_contacts_int hz
WHERE batch_id = p_batch_id
/* Status for the data should not be E or C
i.e. Errored out or Completed */
AND interface_status IS NULL
AND relationship_type = 'CONTACT'
AND relationship_code = 'CONTACT_OF';
SELECT temp_ext_bank_acct_id
FROM iby_temp_ext_bank_accts
WHERE calling_app_unique_ref1 = p_unique_ref
AND status <> 'PROCESSED';
UPDATE ap_suppliers_int
SET status = 'PROCESSED'
WHERE vendor_interface_id = p_vendor_rec.vendor_interface_id
AND sdh_batch_id = p_batch_id;
UPDATE pos_imp_batch_summary
SET total_records_imported = total_records_imported + 1,
total_inserts = total_inserts + 1,
suppliers_inserted = suppliers_inserted + 1,
suppliers_imported = suppliers_imported + 1
WHERE batch_id = p_batch_id;
UPDATE ap_supplier_sites_int
SET vendor_id = l_vendor_id
WHERE vendor_interface_id = p_vendor_rec.vendor_interface_id
AND sdh_batch_id = p_batch_id;
UPDATE ap_sup_site_contact_int
SET vendor_id = l_vendor_id
WHERE vendor_interface_id = p_vendor_rec.vendor_interface_id
AND sdh_batch_id = p_batch_id;
UPDATE pos_business_class_int
SET vendor_id = l_vendor_id
WHERE vendor_interface_id = p_vendor_rec.vendor_interface_id
AND sdh_batch_id = p_batch_id;
UPDATE pos_product_service_int
SET vendor_id = l_vendor_id
WHERE vendor_interface_id = p_vendor_rec.vendor_interface_id
AND sdh_batch_id = p_batch_id;
UPDATE iby_temp_ext_bank_accts
SET ext_payee_id = l_ext_payee_id,
account_owner_party_id = l_party_id
WHERE calling_app_unique_ref1 = p_vendor_rec.vendor_interface_id;
UPDATE iby_temp_ext_bank_accts
SET status = 'PROCESSED'
WHERE temp_ext_bank_acct_id = l_temp_ext_acct_id;
UPDATE iby_temp_ext_bank_accts
SET status = 'REJECTED'
WHERE temp_ext_bank_acct_id = l_temp_ext_acct_id;
IF (insert_rejections(p_batch_id,
l_request_id,
'IBY_TEMP_EXT_BANK_ACCTS',
p_vendor_rec.vendor_interface_id,
'AP_BANK_ACCT_CREATION',
g_user_id,
g_login_id,
'Import_Vendor') <> TRUE) THEN
IF (g_level_procedure >= g_current_runtime_level) THEN
fnd_msg_pub.count_and_get(p_count => l_msg_count,
p_data => l_msg_data);
UPDATE iby_temp_ext_bank_accts
SET status = 'REJECTED'
WHERE temp_ext_bank_acct_id = l_temp_ext_acct_id;
IF (insert_rejections(p_batch_id,
l_request_id,
'IBY_TEMP_EXT_BANK_ACCTS',
p_vendor_rec.vendor_interface_id,
'AP_INVALID_BANK_ACCT_INFO',
g_user_id,
g_login_id,
'Import_Vendor') <> TRUE) THEN
IF (g_level_procedure >= g_current_runtime_level) THEN
fnd_msg_pub.count_and_get(p_count => l_msg_count,
p_data => l_msg_data);
IF (insert_rejections(p_batch_id,
l_request_id,
'AP_SUPPLIERS_INT',
p_vendor_rec.vendor_interface_id,
'AP_PAYEE_CREATION',
g_user_id,
g_login_id,
'Import_Vendor') <> TRUE) THEN
IF (g_level_procedure >= g_current_runtime_level) THEN
fnd_msg_pub.count_and_get(p_count => l_msg_count,
p_data => l_msg_data);
IF (insert_rejections(p_batch_id,
l_request_id,
'AP_SUPPLIERS_INT',
p_vendor_rec.vendor_interface_id,
'AP_INVALID_PAYEE',
g_user_id,
g_login_id,
'Import_Vendor') <> TRUE) THEN
IF (g_level_procedure >= g_current_runtime_level) THEN
fnd_msg_pub.count_and_get(p_count => l_msg_count,
p_data => l_msg_data);
UPDATE ap_suppliers_int
SET status = 'REJECTED'
WHERE vendor_interface_id = p_vendor_rec.vendor_interface_id
AND sdh_batch_id = p_batch_id;
INSERT INTO pos_supplier_int_rejections
(batch_id,
import_request_id,
parent_table,
parent_id,
reject_lookup_code,
last_updated_by,
last_update_date,
last_update_login,
created_by,
creation_date)
SELECT p_batch_id,
l_request_id,
parent_table,
parent_id,
reject_lookup_code,
last_updated_by,
last_update_date,
last_update_login,
created_by,
creation_date
FROM ap_supplier_int_rejections
WHERE parent_table = 'AP_SUPPLIERS_INT'
AND parent_id = p_vendor_rec.vendor_interface_id;
SELECT *
FROM ap_suppliers_int supp
WHERE import_request_id = l_request_id
AND vendor_interface_id IS NOT NULL
AND sdh_batch_id = p_batch_id
AND nvl(status, 'ACTIVE') NOT IN ('PROCESSED', 'REMOVED')
AND NOT EXISTS
(SELECT 1
FROM hz_imp_parties_int party
WHERE batch_id = p_batch_id
AND supp.sdh_batch_id = party.batch_id
AND supp.party_orig_system = party.party_orig_system
AND supp.party_orig_system_reference =
party.party_orig_system_reference
AND party.interface_status = 'R')
ORDER BY segment1;
l_insert_update_flag VARCHAR2(1);
SELECT 1,
vendor_id
FROM ap_suppliers
WHERE party_id = cp_party_id;
DELETE ap_supplier_int_rejections
WHERE parent_table = 'AP_SUPPLIERS_INT';
UPDATE ap_suppliers_int api
SET import_request_id = NULL
WHERE import_request_id IS NOT NULL
AND sdh_batch_id = p_batch_id
AND nvl(status, 'ACTIVE') IN ('ACTIVE', 'REJECTED')
AND EXISTS (SELECT 'Request Completed'
FROM fnd_concurrent_requests fcr
WHERE fcr.request_id = api.import_request_id
AND fcr.phase_code = 'C');
UPDATE ap_suppliers_int
SET import_request_id = l_request_id
WHERE import_request_id IS NULL
AND sdh_batch_id = p_batch_id
AND nvl(status, 'ACTIVE') NOT IN ('PROCESSED', 'REMOVED');
UPDATE ap_suppliers_int supp
SET status = 'REMOVED',
import_request_id = l_request_id
WHERE sdh_batch_id = p_batch_id
AND nvl(status, 'ACTIVE') <> 'PROCESSED'
AND EXISTS
(SELECT 1
FROM hz_imp_parties_int party
WHERE batch_id = p_batch_id
AND supp.sdh_batch_id = party.batch_id
AND supp.party_orig_system = party.party_orig_system
AND supp.party_orig_system_reference =
party.party_orig_system_reference
AND party.interface_status = 'R');
INSERT INTO pos_supplier_int_rejections
(SELECT p_batch_id,
l_request_id,
'AP_SUPPLIERS_INT',
vendor_interface_id,
'POS_INVALID_PARTY_ORIG_SYSTEM',
g_user_id,
SYSDATE,
g_login_id,
g_user_id,
SYSDATE
FROM ap_suppliers_int
WHERE status = 'REMOVED'
AND import_request_id = l_request_id
AND sdh_batch_id = p_batch_id);
UPDATE ap_suppliers_int
SET status = 'REJECTED'
WHERE vendor_interface_id = vendor_int_rec.vendor_interface_id
AND sdh_batch_id = p_batch_id;
IF (insert_rejections(p_batch_id,
l_request_id,
'AP_SUPPLIERS_INT',
vendor_rec.vendor_interface_id,
'AP_INVALID_NUM_1099',
g_user_id,
g_login_id,
'IMPORT_VENDORS') <> TRUE) THEN
IF (g_level_procedure >= g_current_runtime_level) THEN
fnd_msg_pub.count_and_get(p_count => l_msg_count,
p_data => l_msg_data);
UPDATE ap_suppliers_int
SET status = 'REJECTED'
WHERE vendor_interface_id = vendor_rec.vendor_interface_id
AND sdh_batch_id = p_batch_id;
IF (insert_rejections(p_batch_id,
l_request_id,
'AP_SUPPLIERS_INT',
vendor_rec.vendor_interface_id,
'POS_INVALID_PARTY_ORIG_SYSTEM',
g_user_id,
g_login_id,
'IMPORT_VENDORS') <> TRUE) THEN
IF (g_level_procedure >= g_current_runtime_level) THEN
fnd_msg_pub.count_and_get(p_count => l_msg_count,
p_data => l_msg_data);
SELECT insert_update_flag
INTO l_insert_update_flag
FROM hz_imp_parties_int
WHERE batch_id = p_batch_id
AND party_orig_system = vendor_int_rec.party_orig_system
AND party_orig_system_reference =
vendor_int_rec.party_orig_system_reference;
l_insert_update_flag := 'O';
IF (l_insert_update_flag = 'I') THEN
fnd_file.put_line(fnd_file.log,
' Message: Inside PROCEDURE IMPORT_VENDORS' ||
' Calling create_vendor insert_update_flag = I');
' As vendor exists calling ap_vendor_pub_pkg.update_vendor for vendor id : ' ||
l_vendor_id);
ap_vendor_pub_pkg.update_vendor(1.0,
fnd_api.g_false,
fnd_api.g_false,
fnd_api.g_valid_level_full,
l_return_status,
l_msg_count,
l_msg_data,
vendor_rec,
l_vendor_id);
UPDATE ap_suppliers_int
SET status = 'PROCESSED'
WHERE vendor_interface_id = vendor_rec.vendor_interface_id
AND sdh_batch_id = p_batch_id;
UPDATE pos_imp_batch_summary
SET total_records_imported = total_records_imported + 1,
total_updates = total_updates + 1,
suppliers_updated = suppliers_updated + 1,
suppliers_imported = suppliers_imported + 1
WHERE batch_id = p_batch_id;
UPDATE ap_suppliers_int
SET status = 'REJECTED'
WHERE vendor_interface_id = vendor_rec.vendor_interface_id
AND sdh_batch_id = p_batch_id;
INSERT INTO pos_supplier_int_rejections
(batch_id,
import_request_id,
parent_table,
parent_id,
reject_lookup_code,
last_updated_by,
last_update_date,
last_update_login,
created_by,
creation_date)
SELECT p_batch_id,
l_request_id,
parent_table,
parent_id,
reject_lookup_code,
last_updated_by,
last_update_date,
last_update_login,
created_by,
creation_date
FROM ap_supplier_int_rejections
WHERE parent_table = 'AP_SUPPLIERS_INT'
AND parent_id = vendor_rec.vendor_interface_id;
', No. of Messages from update_vendor API: ' ||
l_msg_count ||
', Message From update_vendor API: ' ||
l_msg_data);
' failed in ap_vendor_pub_pkg.update_vendor ' ||
' vendor_interface_id: ' ||
vendor_rec.vendor_interface_id ||
', No. of Messages: ' || l_msg_count ||
', Message: ' || l_msg_data ||
', return status: ' || l_return_status);
' Calling create_vendor insert_update_flag = U');
SELECT temp_ext_bank_acct_id
FROM iby_temp_ext_bank_accts
WHERE calling_app_unique_ref2 = p_unique_ref
AND nvl(status, 'NEW') <> 'PROCESSED';
UPDATE ap_supplier_sites_int
SET status = 'PROCESSED'
WHERE vendor_site_interface_id = p_site_rec.vendor_site_interface_id
AND sdh_batch_id = p_batch_id;
UPDATE pos_imp_batch_summary
SET total_records_imported = total_records_imported + 1,
total_inserts = total_inserts + 1,
sites_inserted = sites_inserted + 1,
sites_imported = sites_imported + 1
WHERE batch_id = p_batch_id;
UPDATE ap_sup_site_contact_int
SET vendor_site_id = l_vendor_site_id
WHERE vendor_id = p_site_rec.vendor_id
AND vendor_site_code = p_site_rec.vendor_site_code
AND (org_id = p_site_rec.org_id OR
operating_unit_name = p_site_rec.org_name)
AND sdh_batch_id = p_batch_id;
SELECT org_id
INTO ext_payee_rec.payer_org_id
FROM po_vendor_sites_all
WHERE vendor_site_id = l_vendor_site_id;
SELECT party_id,
'PAYABLES_DISB'
INTO ext_payee_rec.payee_party_id,
ext_payee_rec.payment_function
FROM po_vendors
WHERE vendor_id = p_site_rec.vendor_id;
UPDATE iby_temp_ext_bank_accts
SET ext_payee_id = l_ext_payee_id,
account_owner_party_id = ext_payee_rec.payee_party_id --bug 6753331
WHERE calling_app_unique_ref2 =
p_site_rec.vendor_site_interface_id;
UPDATE iby_temp_ext_bank_accts
SET status = 'PROCESSED'
WHERE temp_ext_bank_acct_id = l_temp_ext_acct_id;
UPDATE ap_supplier_sites_int
SET status = 'REJECTED'
WHERE vendor_site_interface_id =
p_site_rec.vendor_site_interface_id
AND sdh_batch_id = p_batch_id;
UPDATE iby_temp_ext_bank_accts
SET status = 'REJECTED'
WHERE temp_ext_bank_acct_id = l_temp_ext_acct_id;
IF (insert_rejections(p_batch_id,
l_request_id,
'AP_SUPPLIER_SITES_INT',
p_site_rec.vendor_site_interface_id,
'AP_INVALID_BANK_ACCT_INFO',
g_user_id,
g_login_id,
'Create_Vendor_Site') <> TRUE) THEN
IF (g_level_procedure >= g_current_runtime_level) THEN
fnd_msg_pub.count_and_get(p_count => l_msg_count,
p_data => l_msg_data);
IF (insert_rejections(p_batch_id,
l_request_id,
'AP_SUPPLIER_SITES_INT',
p_site_rec.vendor_site_interface_id,
'AP_PAYEE_CREATION',
g_user_id,
g_login_id,
'Create_Vendor_Site') <> TRUE) THEN
IF (g_level_procedure >= g_current_runtime_level) THEN
fnd_msg_pub.count_and_get(p_count => l_msg_count,
p_data => l_msg_data);
IF (insert_rejections(p_batch_id,
l_request_id,
'AP_SUPPLIER_SITES_INT',
p_site_rec.vendor_site_interface_id,
'AP_INVALID_PAYEE_INFO',
g_user_id,
g_login_id,
'Create_Vendor_Site') <> TRUE) THEN
IF (g_level_procedure >= g_current_runtime_level) THEN
fnd_msg_pub.count_and_get(p_count => l_msg_count,
p_data => l_msg_data);
UPDATE ap_supplier_sites_int
SET status = 'REJECTED'
WHERE vendor_site_interface_id = p_site_rec.vendor_site_interface_id
AND sdh_batch_id = p_batch_id;
INSERT INTO pos_supplier_int_rejections
(batch_id,
import_request_id,
parent_table,
parent_id,
reject_lookup_code,
last_updated_by,
last_update_date,
last_update_login,
created_by,
creation_date)
SELECT p_batch_id,
l_request_id,
parent_table,
parent_id,
decode(reject_lookup_code,
'AP_INCONSISTENT_ADDRESS',
'POS_INCONSISTENT_ADDRESS',
reject_lookup_code),
last_updated_by,
last_update_date,
last_update_login,
created_by,
creation_date
FROM ap_supplier_int_rejections
WHERE parent_table = 'AP_SUPPLIER_SITES_INT'
AND parent_id = p_site_rec.vendor_site_interface_id;
l_insert_update_flag VARCHAR2(1);
SELECT *
FROM ap_supplier_sites_int supp
WHERE import_request_id = l_request_id
AND (org_id IS NOT NULL OR operating_unit_name IS NOT NULL)
AND sdh_batch_id = p_batch_id
AND nvl(status, 'ACTIVE') NOT IN ('PROCESSED', 'REMOVED')
AND NOT EXISTS
(SELECT 1
FROM hz_imp_addresses_int party
WHERE batch_id = p_batch_id
AND supp.sdh_batch_id = party.batch_id
AND supp.party_orig_system = party.party_orig_system
AND supp.party_orig_system_reference =
party.party_orig_system_reference
AND supp.party_site_orig_system = party.site_orig_system
AND supp.party_site_orig_sys_reference =
party.site_orig_system_reference
AND party.interface_status = 'R');
SELECT 1,
vendor_site_id
FROM ap_supplier_sites_all site
WHERE vendor_id = cp_vendor_id
AND vendor_site_code = cp_vendor_site_code;
DELETE ap_supplier_int_rejections
WHERE parent_table = 'AP_SUPPLIER_SITES_INT';
UPDATE ap_supplier_sites_int api
SET import_request_id = NULL
WHERE import_request_id IS NOT NULL
AND sdh_batch_id = p_batch_id
AND nvl(status, 'ACTIVE') IN ('ACTIVE', 'REJECTED')
AND EXISTS (SELECT 'Request Completed'
FROM fnd_concurrent_requests fcr
WHERE fcr.request_id = api.import_request_id
AND fcr.phase_code = 'C');
UPDATE ap_supplier_sites_int
SET import_request_id = l_request_id
WHERE import_request_id IS NULL
AND sdh_batch_id = p_batch_id
AND nvl(status, 'ACTIVE') <> 'PROCESSED';
UPDATE ap_supplier_sites_int
SET status = 'REJECTED',
import_request_id = l_request_id
WHERE (operating_unit_name IS NULL AND org_id IS NULL)
AND sdh_batch_id = p_batch_id
AND nvl(status, 'ACTIVE') <> 'PROCESSED';
INSERT INTO pos_supplier_int_rejections
(SELECT p_batch_id,
l_request_id,
'AP_SUPPLIER_SITES_INT',
vendor_site_interface_id,
'AP_ORG_INFO_NULL',
g_user_id,
SYSDATE,
g_login_id,
g_user_id,
SYSDATE
FROM ap_supplier_sites_int
WHERE status = 'REJECTED'
AND import_request_id = l_request_id
AND (operating_unit_name IS NULL AND org_id IS NULL)
AND sdh_batch_id = p_batch_id);
UPDATE ap_supplier_sites_int supp
SET status = 'REMOVED',
import_request_id = l_request_id
WHERE sdh_batch_id = p_batch_id
AND nvl(status, 'ACTIVE') <> 'PROCESSED'
AND EXISTS
(SELECT 1
FROM hz_imp_addresses_int party
WHERE batch_id = p_batch_id
AND supp.sdh_batch_id = party.batch_id
AND supp.party_orig_system = party.party_orig_system
AND supp.party_orig_system_reference =
party.party_orig_system_reference
AND supp.party_site_orig_system = party.site_orig_system
AND supp.party_site_orig_sys_reference =
party.site_orig_system_reference
AND party.interface_status = 'R');
INSERT INTO pos_supplier_int_rejections
(SELECT p_batch_id,
l_request_id,
'AP_SUPPLIER_SITES_INT',
vendor_site_interface_id,
'POS_INVALID_PARTY_ORIG_SYSTEM',
g_user_id,
SYSDATE,
g_login_id,
g_user_id,
SYSDATE
FROM ap_supplier_sites_int
WHERE status = 'REMOVED'
AND import_request_id = l_request_id
AND sdh_batch_id = p_batch_id);
SELECT vendor_id
INTO site_rec.vendor_id
FROM ap_suppliers supp
WHERE supp.party_id = l_party_id;
UPDATE ap_supplier_sites_int
SET status = 'REJECTED'
WHERE vendor_site_interface_id =
site_int_rec.vendor_site_interface_id
AND sdh_batch_id = p_batch_id;
IF (insert_rejections(p_batch_id,
l_request_id,
'AP_SUPPLIER_SITES_INT',
site_int_rec.vendor_site_interface_id,
'AP_VENDOR_ID_NULL',
g_user_id,
g_login_id,
'IMPORT_VENDOR_SITES') <> TRUE) THEN
IF (g_level_procedure >= g_current_runtime_level) THEN
fnd_msg_pub.count_and_get(p_count => l_msg_count,
p_data => l_msg_data);
SELECT insert_update_flag
INTO l_insert_update_flag
FROM hz_imp_addresses_int
WHERE batch_id = p_batch_id
AND party_orig_system = site_int_rec.party_orig_system
AND party_orig_system_reference =
site_int_rec.party_orig_system_reference
AND site_orig_system = site_int_rec.party_site_orig_system
AND site_orig_system_reference =
site_int_rec.party_site_orig_sys_reference;
l_insert_update_flag := 'O';
' Parameters insert_update_flag: ' ||
l_insert_update_flag ||
' for vendor_site_interface_id: ' ||
site_rec.vendor_site_interface_id);
IF (l_insert_update_flag = 'I') THEN
create_vendor_site(p_batch_id => p_batch_id,
p_site_rec => site_rec,
ext_payee_rec => ext_payee_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
' ap_vendor_pub_pkg.update_vendor_site for vendor_site_id: ' ||
l_vendor_site_id);
ap_vendor_pub_pkg.update_vendor_site(1.0,
fnd_api.g_false,
fnd_api.g_false,
fnd_api.g_valid_level_full,
l_return_status,
l_msg_count,
l_msg_data,
site_rec,
l_vendor_site_id);
UPDATE ap_supplier_sites_int
SET status = 'PROCESSED'
WHERE vendor_site_interface_id =
site_rec.vendor_site_interface_id
AND sdh_batch_id = p_batch_id;
UPDATE pos_imp_batch_summary
SET total_records_imported = total_records_imported + 1,
total_updates = total_updates + 1,
sites_updated = sites_updated + 1,
sites_imported = sites_imported + 1
WHERE batch_id = p_batch_id;
UPDATE ap_supplier_sites_int
SET status = 'REJECTED'
WHERE vendor_site_interface_id =
site_rec.vendor_site_interface_id
AND sdh_batch_id = p_batch_id;
INSERT INTO pos_supplier_int_rejections
(batch_id,
import_request_id,
parent_table,
parent_id,
reject_lookup_code,
last_updated_by,
last_update_date,
last_update_login,
created_by,
creation_date)
SELECT p_batch_id,
l_request_id,
parent_table,
parent_id,
reject_lookup_code,
last_updated_by,
last_update_date,
last_update_login,
created_by,
creation_date
FROM ap_supplier_int_rejections
WHERE parent_table = 'AP_SUPPLIER_SITES_INT'
AND parent_id = site_rec.vendor_site_interface_id;
', No. of Messages from update_vendor_site API: ' ||
l_msg_count ||
', Message From update_vendor_site API: ' ||
l_msg_data);
' failed in ap_vendor_pub_pkg.update_vendor_site ' ||
' vendor_site_interface_id: ' ||
site_rec.vendor_site_interface_id ||
', No. of Messages: ' || l_msg_count ||
', Message: ' || l_msg_data ||
', Return Status: ' || l_return_status);
SELECT 1
FROM ap_supplier_contacts
WHERE org_party_site_id = p_vendor_contact_rec.org_party_site_id
AND per_party_id = p_vendor_contact_rec.per_party_id
AND (inactive_date IS NULL OR inactive_date >= SYSDATE)
AND rownum = 1;
l_insert_update_flag VARCHAR2(1);
SELECT *
FROM ap_sup_site_contact_int supp
WHERE import_request_id = l_request_id
AND (org_id IS NOT NULL OR operating_unit_name IS NOT NULL)
AND last_name IS NOT NULL
AND sdh_batch_id = p_batch_id
AND nvl(status, 'ACTIVE') NOT IN ('PROCESSED', 'REMOVED')
AND NOT EXISTS
(SELECT 1
FROM hz_imp_contacts_int party
WHERE batch_id = p_batch_id
AND supp.sdh_batch_id = party.batch_id
AND supp.party_orig_system = party.obj_orig_system
AND supp.party_orig_system_reference =
party.obj_orig_system_reference
AND supp.contact_orig_system = party.contact_orig_system
AND supp.contact_orig_system_reference =
party.contact_orig_system_reference
AND party.interface_status = 'R');
DELETE ap_supplier_int_rejections
WHERE parent_table = 'AP_SUP_SITE_CONTACT_INT';
UPDATE ap_sup_site_contact_int api
SET import_request_id = NULL
WHERE import_request_id IS NOT NULL
AND sdh_batch_id = p_batch_id
AND nvl(status, 'ACTIVE') IN ('ACTIVE', 'REJECTED')
AND EXISTS (SELECT 'Request Completed'
FROM fnd_concurrent_requests fcr
WHERE fcr.request_id = api.import_request_id
AND fcr.phase_code = 'C');
UPDATE ap_sup_site_contact_int
SET import_request_id = l_request_id
WHERE import_request_id IS NULL
AND sdh_batch_id = p_batch_id
AND nvl(status, 'ACTIVE') <> 'PROCESSED';
UPDATE ap_sup_site_contact_int
SET status = 'REJECTED',
import_request_id = l_request_id
WHERE ((operating_unit_name IS NULL AND org_id IS NULL) OR
(last_name IS NULL))
AND sdh_batch_id = p_batch_id;
INSERT INTO pos_supplier_int_rejections
(SELECT p_batch_id,
l_request_id,
'AP_SUP_SITE_CONTACT_INT',
vendor_contact_interface_id,
'AP_ORG_INFO_NULL',
g_user_id,
SYSDATE,
g_login_id,
g_user_id,
SYSDATE
FROM ap_sup_site_contact_int
WHERE status = 'REJECTED'
AND import_request_id = l_request_id
AND sdh_batch_id = p_batch_id
AND (operating_unit_name IS NULL AND org_id IS NULL)) UNION
(SELECT p_batch_id,
l_request_id,
'AP_SUP_SITE_CONTACT_INT',
vendor_contact_interface_id,
'AP_LAST_NAME_NULL',
g_user_id,
SYSDATE,
g_login_id,
g_user_id,
SYSDATE
FROM ap_sup_site_contact_int
WHERE status = 'REJECTED'
AND import_request_id = l_request_id
AND sdh_batch_id = p_batch_id
AND last_name IS NULL);
UPDATE ap_sup_site_contact_int supp
SET status = 'REMOVED',
import_request_id = l_request_id
WHERE sdh_batch_id = p_batch_id
AND nvl(status, 'ACTIVE') NOT IN ('PROCESSED', 'REMOVED')
AND EXISTS
(SELECT 1
FROM hz_imp_contacts_int party
WHERE batch_id = p_batch_id
AND supp.sdh_batch_id = party.batch_id
AND supp.party_orig_system = party.obj_orig_system
AND supp.party_orig_system_reference =
party.obj_orig_system_reference
AND supp.contact_orig_system = party.contact_orig_system
AND supp.contact_orig_system_reference =
party.contact_orig_system_reference
AND party.interface_status = 'R');
INSERT INTO pos_supplier_int_rejections
(SELECT p_batch_id,
l_request_id,
'AP_SUP_SITE_CONTACT_INT',
vendor_contact_interface_id,
'POS_INVALID_PARTY_ORIG_SYSTEM',
g_user_id,
SYSDATE,
g_login_id,
g_user_id,
SYSDATE
FROM ap_sup_site_contact_int
WHERE status = 'REMOVED'
AND import_request_id = l_request_id
AND sdh_batch_id = p_batch_id);
SELECT vendor_id
INTO vendor_contact_rec.vendor_id
FROM ap_suppliers supp
WHERE supp.party_id = contact_int_rec.party_id;
UPDATE ap_sup_site_contact_int
SET status = 'REJECTED'
WHERE vendor_contact_interface_id =
contact_int_rec.vendor_contact_interface_id
AND sdh_batch_id = p_batch_id;
IF (insert_rejections(p_batch_id,
l_request_id,
'AP_SUP_SITE_CONTACT_INT',
vendor_contact_rec.vendor_contact_interface_id,
'AP_VENDOR_ID_NULL',
g_user_id,
g_login_id,
'IMPORT_VENDOR_CONTACTS') <> TRUE) THEN
IF (g_level_procedure >= g_current_runtime_level) THEN
fnd_msg_pub.count_and_get(p_count => l_msg_count,
p_data => l_msg_data);
SELECT owner_table_id
INTO vendor_contact_rec.org_party_site_id
FROM hz_orig_sys_references hr
WHERE hr.owner_table_name = 'HZ_PARTY_SITES'
AND hr.orig_system = contact_int_rec.party_site_orig_system
AND hr.orig_system_reference =
contact_int_rec.party_site_orig_sys_reference
AND hr.status = 'A'
AND nvl(hr.end_date_active, SYSDATE) >= SYSDATE;
UPDATE ap_sup_site_contact_int
SET status = 'REJECTED'
WHERE vendor_contact_interface_id =
contact_int_rec.vendor_contact_interface_id
AND sdh_batch_id = p_batch_id;
IF (insert_rejections(p_batch_id,
l_request_id,
'AP_SUP_SITE_CONTACT_INT',
vendor_contact_rec.vendor_contact_interface_id,
'POS_INV_PARTY_SITE_ORIG_SYS',
g_user_id,
g_login_id,
'IMPORT_VENDOR_CONTACTS') <> TRUE) THEN
IF (g_level_procedure >= g_current_runtime_level) THEN
fnd_msg_pub.count_and_get(p_count => l_msg_count,
p_data => l_msg_data);
pos_supp_contact_pkg.update_supplier_contact(p_contact_party_id => vendor_contact_rec.per_party_id,
p_vendor_party_id => contact_int_rec.party_id,
p_first_name => vendor_contact_rec.person_first_name,
p_last_name => vendor_contact_rec.person_last_name,
p_middle_name => vendor_contact_rec.person_middle_name,
p_contact_title => vendor_contact_rec.person_title,
p_job_title => NULL,
p_phone_area_code => vendor_contact_rec.area_code,
p_phone_number => vendor_contact_rec.phone,
p_phone_extension => NULL,
p_fax_area_code => vendor_contact_rec.fax_area_code,
p_fax_number => vendor_contact_rec.fax_phone,
p_email_address => vendor_contact_rec.email_address,
p_inactive_date => vendor_contact_rec.inactive_date,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_department => vendor_contact_rec.department);
SELECT 1
INTO l_count
FROM ap_suppliers pv,
hz_relationships hzr,
hz_org_contacts hoc
WHERE pv.vendor_id = vendor_contact_rec.vendor_id
AND hzr.relationship_type = 'CONTACT'
AND hzr.relationship_code = 'CONTACT_OF'
AND hzr.subject_id = vendor_contact_rec.per_party_id
AND hzr.subject_type = 'PERSON'
AND hzr.subject_table_name = 'HZ_PARTIES'
AND hzr.object_type = 'ORGANIZATION'
AND hzr.object_table_name = 'HZ_PARTIES'
AND hzr.object_id = pv.party_id
AND hzr.status = 'A'
AND trunc(SYSDATE) BETWEEN trunc(hzr.start_date) AND
nvl(trunc(hzr.end_date), trunc(SYSDATE + 1))
AND hzr.relationship_id = hoc.party_relationship_id;
UPDATE ap_sup_site_contact_int
SET status = 'REJECTED'
WHERE vendor_contact_interface_id =
contact_int_rec.vendor_contact_interface_id
AND sdh_batch_id = p_batch_id;
IF (insert_rejections(p_batch_id,
l_request_id,
'AP_SUP_SITE_CONTACT_INT',
vendor_contact_rec.vendor_contact_interface_id,
'POS_INVALID_PERSON_PARTY_REL',
g_user_id,
g_login_id,
'IMPORT_VENDOR_CONTACTS') <> TRUE) THEN
IF (g_level_procedure >= g_current_runtime_level) THEN
fnd_msg_pub.count_and_get(p_count => l_msg_count,
p_data => l_msg_data);
SELECT party_site_id
INTO vendor_contact_rec.org_party_site_id
FROM hz_party_sites
WHERE party_id = contact_int_rec.party_id
AND party_site_name = vendor_contact_rec.party_site_name;
UPDATE ap_sup_site_contact_int
SET status = 'REJECTED'
WHERE vendor_contact_interface_id =
contact_int_rec.vendor_contact_interface_id
AND sdh_batch_id = p_batch_id;
IF (insert_rejections(p_batch_id,
l_request_id,
'AP_SUP_SITE_CONTACT_INT',
vendor_contact_rec.vendor_contact_interface_id,
'POS_INVALID_PARTY_SITE_NAME',
g_user_id,
g_login_id,
'IMPORT_VENDOR_CONTACTS') <> TRUE) THEN
IF (g_level_procedure >= g_current_runtime_level) THEN
fnd_msg_pub.count_and_get(p_count => l_msg_count,
p_data => l_msg_data);
UPDATE ap_sup_site_contact_int
SET status = 'PROCESSED'
WHERE vendor_contact_interface_id =
vendor_contact_rec.vendor_contact_interface_id
AND sdh_batch_id = p_batch_id;
SELECT nvl(insert_update_flag, 'U')
INTO l_insert_update_flag
FROM hz_imp_contacts_int
WHERE batch_id = p_batch_id
AND obj_orig_system = contact_int_rec.party_orig_system
AND obj_orig_system_reference =
contact_int_rec.party_orig_system_reference
AND contact_orig_system = contact_int_rec.contact_orig_system
AND contact_orig_system_reference =
contact_int_rec.contact_orig_system_reference;
l_insert_update_flag := 'U';
IF (l_insert_update_flag = 'I') THEN
UPDATE pos_imp_batch_summary
SET total_records_imported = total_records_imported + 1,
total_inserts = total_inserts + 1,
contacts_inserted = contacts_inserted + 1,
contacts_imported = contacts_imported + 1
WHERE batch_id = p_batch_id;
UPDATE pos_imp_batch_summary
SET total_records_imported = total_records_imported + 1,
total_updates = total_updates + 1,
contacts_updated = contacts_updated + 1,
contacts_imported = contacts_imported + 1
WHERE batch_id = p_batch_id;
UPDATE ap_sup_site_contact_int
SET status = 'REJECTED'
WHERE vendor_contact_interface_id =
vendor_contact_rec.vendor_contact_interface_id
AND sdh_batch_id = p_batch_id;
INSERT INTO pos_supplier_int_rejections
(batch_id,
import_request_id,
parent_table,
parent_id,
reject_lookup_code,
last_updated_by,
last_update_date,
last_update_login,
created_by,
creation_date)
SELECT p_batch_id,
l_request_id,
parent_table,
parent_id,
reject_lookup_code,
last_updated_by,
last_update_date,
last_update_login,
created_by,
creation_date
FROM ap_supplier_int_rejections
WHERE parent_table = 'AP_SUP_SITE_CONTACT_INT'
AND parent_id = vendor_contact_rec.vendor_contact_interface_id;
IF (insert_rejections(p_batch_id,
l_request_id,
'POS_PRODUCT_SERVICE_INT',
p_vendor_prodsrv_rec.ps_interface_id,
'POS_INVALID_SEGMENT_DEF',
g_user_id,
g_login_id,
'validate_vendor_prods_services') <> TRUE) THEN
IF (g_level_procedure >= g_current_runtime_level) THEN
fnd_msg_pub.count_and_get(p_count => l_msg_count,
p_data => l_msg_data);
SELECT nvl2(p_vendor_prodsrv_rec.segment1,
p_vendor_prodsrv_rec.segment1 || l_delimiter,
p_vendor_prodsrv_rec.segment1) ||
nvl2(p_vendor_prodsrv_rec.segment2,
p_vendor_prodsrv_rec.segment2 || l_delimiter,
p_vendor_prodsrv_rec.segment2) ||
nvl2(p_vendor_prodsrv_rec.segment3,
p_vendor_prodsrv_rec.segment3 || l_delimiter,
p_vendor_prodsrv_rec.segment3) ||
nvl2(p_vendor_prodsrv_rec.segment4,
p_vendor_prodsrv_rec.segment4 || l_delimiter,
p_vendor_prodsrv_rec.segment4) ||
nvl2(p_vendor_prodsrv_rec.segment5,
p_vendor_prodsrv_rec.segment5 || l_delimiter,
p_vendor_prodsrv_rec.segment5) ||
nvl2(p_vendor_prodsrv_rec.segment6,
p_vendor_prodsrv_rec.segment6 || l_delimiter,
p_vendor_prodsrv_rec.segment6) ||
nvl2(p_vendor_prodsrv_rec.segment7,
p_vendor_prodsrv_rec.segment7 || l_delimiter,
p_vendor_prodsrv_rec.segment7) ||
nvl2(p_vendor_prodsrv_rec.segment8,
p_vendor_prodsrv_rec.segment8 || l_delimiter,
p_vendor_prodsrv_rec.segment8) ||
nvl2(p_vendor_prodsrv_rec.segment9,
p_vendor_prodsrv_rec.segment9 || l_delimiter,
p_vendor_prodsrv_rec.segment9) ||
nvl2(p_vendor_prodsrv_rec.segment10,
p_vendor_prodsrv_rec.segment10 || l_delimiter,
p_vendor_prodsrv_rec.segment10) ||
nvl2(p_vendor_prodsrv_rec.segment11,
p_vendor_prodsrv_rec.segment11 || l_delimiter,
p_vendor_prodsrv_rec.segment11) ||
nvl2(p_vendor_prodsrv_rec.segment12,
p_vendor_prodsrv_rec.segment12 || l_delimiter,
p_vendor_prodsrv_rec.segment12) ||
nvl2(p_vendor_prodsrv_rec.segment13,
p_vendor_prodsrv_rec.segment13 || l_delimiter,
p_vendor_prodsrv_rec.segment13) ||
nvl2(p_vendor_prodsrv_rec.segment14,
p_vendor_prodsrv_rec.segment14 || l_delimiter,
p_vendor_prodsrv_rec.segment14) ||
nvl2(p_vendor_prodsrv_rec.segment15,
p_vendor_prodsrv_rec.segment15 || l_delimiter,
p_vendor_prodsrv_rec.segment15) ||
nvl2(p_vendor_prodsrv_rec.segment16,
p_vendor_prodsrv_rec.segment16 || l_delimiter,
p_vendor_prodsrv_rec.segment16) ||
nvl2(p_vendor_prodsrv_rec.segment17,
p_vendor_prodsrv_rec.segment17 || l_delimiter,
p_vendor_prodsrv_rec.segment17) ||
nvl2(p_vendor_prodsrv_rec.segment18,
p_vendor_prodsrv_rec.segment18 || l_delimiter,
p_vendor_prodsrv_rec.segment18) ||
nvl2(p_vendor_prodsrv_rec.segment19,
p_vendor_prodsrv_rec.segment19 || l_delimiter,
p_vendor_prodsrv_rec.segment19) ||
nvl2(p_vendor_prodsrv_rec.segment20,
p_vendor_prodsrv_rec.segment20 || l_delimiter,
p_vendor_prodsrv_rec.segment20)
INTO l_segment_concat
FROM dual;
IF (insert_rejections(p_batch_id,
l_request_id,
'POS_PRODUCT_SERVICE_INT',
p_vendor_prodsrv_rec.ps_interface_id,
'POS_INVALID_SEGMENT_COUNT',
g_user_id,
g_login_id,
'validate_vendor_prods_services') <> TRUE) THEN
IF (g_level_procedure >= g_current_runtime_level) THEN
fnd_msg_pub.count_and_get(p_count => l_msg_count,
p_data => l_msg_data);
l_sql := 'SELECT ' || l_segment_code || '
FROM POS_PRODUCT_SERVICE_INT
WHERE PS_INTERFACE_ID = ' ||
p_vendor_prodsrv_rec.ps_interface_id;
SELECT nvl(category_id, 0)
INTO l_category_id
FROM mtl_categories_kfv
WHERE category_id IN
(SELECT DISTINCT category_id
FROM mtl_category_set_valid_cats
WHERE category_set_id = l_default_po_category_set_id)
AND concatenated_segments = l_concatenated_segments;
IF (insert_rejections(p_batch_id,
l_request_id,
'POS_PRODUCT_SERVICE_INT',
p_vendor_prodsrv_rec.ps_interface_id,
'POS_INVALID_CATEGORY_ID',
g_user_id,
g_login_id,
'validate_vendor_prods_services') <> TRUE) THEN
IF (g_level_procedure >= g_current_runtime_level) THEN
fnd_msg_pub.count_and_get(p_count => l_msg_count,
p_data => l_msg_data);
IF (insert_rejections(p_batch_id,
l_request_id,
'POS_PRODUCT_SERVICE_INT',
p_vendor_prodsrv_rec.ps_interface_id,
'POS_INVALID_CATEGORY_ID',
g_user_id,
g_login_id,
'validate_vendor_prods_services') <> TRUE) THEN
IF (g_level_procedure >= g_current_runtime_level) THEN
fnd_msg_pub.count_and_get(p_count => l_msg_count,
p_data => l_msg_data);
SELECT mapping_id
INTO l_mapping_id
FROM pos_supplier_mappings
WHERE vendor_id = p_vendor_prodsrv_rec.vendor_id
AND party_id = p_party_id;
SELECT *
FROM pos_product_service_int supp
WHERE import_request_id = l_request_id
AND ps_interface_id IS NOT NULL
AND sdh_batch_id = p_batch_id
AND nvl(status, 'ACTIVE') NOT IN ('PROCESSED', 'REMOVED')
AND NOT EXISTS
(SELECT 1
FROM hz_imp_parties_int party
WHERE batch_id = p_batch_id
AND supp.sdh_batch_id = party.batch_id
AND supp.source_system = party.party_orig_system
AND supp.source_system_reference =
party.party_orig_system_reference
AND party.interface_status = 'R');
UPDATE pos_product_service_int api
SET import_request_id = NULL
WHERE import_request_id IS NOT NULL
AND sdh_batch_id = p_batch_id
AND nvl(status, 'ACTIVE') IN ('ACTIVE', 'REJECTED')
AND EXISTS (SELECT 'Request Completed'
FROM fnd_concurrent_requests fcr
WHERE fcr.request_id = api.import_request_id
AND fcr.phase_code = 'C');
UPDATE pos_product_service_int
SET import_request_id = l_request_id
WHERE import_request_id IS NULL
AND sdh_batch_id = p_batch_id
AND nvl(status, 'ACTIVE') <> 'PROCESSED';
UPDATE pos_product_service_int supp
SET status = 'REMOVED',
import_request_id = l_request_id
WHERE sdh_batch_id = p_batch_id
AND nvl(status, 'ACTIVE') <> 'PROCESSED'
AND EXISTS (SELECT 1
FROM hz_imp_parties_int party
WHERE batch_id = p_batch_id
AND supp.sdh_batch_id = party.batch_id
AND supp.source_system = party.party_orig_system
AND supp.source_system_reference =
party.party_orig_system_reference
AND party.interface_status = 'R');
INSERT INTO pos_supplier_int_rejections
(SELECT p_batch_id,
l_request_id,
'POS_PRODUCT_SERVICE_INT',
ps_interface_id,
'POS_INVALID_PARTY_ORIG_SYSTEM',
g_user_id,
SYSDATE,
g_login_id,
g_user_id,
SYSDATE
FROM pos_product_service_int
WHERE status = 'REMOVED'
AND import_request_id = l_request_id
AND sdh_batch_id = p_batch_id);
SELECT vendor_id
INTO vendor_prod_srv_rec.vendor_id
FROM ap_suppliers supp
WHERE supp.party_id = l_party_id;
UPDATE pos_product_service_int
SET status = 'REJECTED'
WHERE ps_interface_id =
vendor_int_prod_srv_rec.ps_interface_id
AND sdh_batch_id = p_batch_id;
IF (insert_rejections(p_batch_id,
l_request_id,
'POS_PRODUCT_SERVICE_INT',
vendor_int_prod_srv_rec.ps_interface_id,
'AP_VENDOR_ID_NULL',
g_user_id,
g_login_id,
'import_vendor_prods_services') <> TRUE) THEN
IF (g_level_procedure >= g_current_runtime_level) THEN
fnd_msg_pub.count_and_get(p_count => l_msg_count,
p_data => l_msg_data);
SELECT mapping_id
INTO l_mapping_id
FROM pos_supplier_mappings
WHERE vendor_id = vendor_prod_srv_rec.vendor_id
AND party_id = l_party_id;
SELECT rtrim(nvl2(vendor_prod_srv_rec.segment1,
vendor_prod_srv_rec.segment1 || l_delimiter,
vendor_prod_srv_rec.segment1) ||
nvl2(vendor_prod_srv_rec.segment2,
vendor_prod_srv_rec.segment2 || l_delimiter,
vendor_prod_srv_rec.segment2) ||
nvl2(vendor_prod_srv_rec.segment3,
vendor_prod_srv_rec.segment3 || l_delimiter,
vendor_prod_srv_rec.segment3) ||
nvl2(vendor_prod_srv_rec.segment4,
vendor_prod_srv_rec.segment4 || l_delimiter,
vendor_prod_srv_rec.segment4) ||
nvl2(vendor_prod_srv_rec.segment5,
vendor_prod_srv_rec.segment5 || l_delimiter,
vendor_prod_srv_rec.segment5) ||
nvl2(vendor_prod_srv_rec.segment6,
vendor_prod_srv_rec.segment6 || l_delimiter,
vendor_prod_srv_rec.segment6) ||
nvl2(vendor_prod_srv_rec.segment7,
vendor_prod_srv_rec.segment7 || l_delimiter,
vendor_prod_srv_rec.segment7) ||
nvl2(vendor_prod_srv_rec.segment8,
vendor_prod_srv_rec.segment8 || l_delimiter,
vendor_prod_srv_rec.segment8) ||
nvl2(vendor_prod_srv_rec.segment9,
vendor_prod_srv_rec.segment9 || l_delimiter,
vendor_prod_srv_rec.segment9) ||
nvl2(vendor_prod_srv_rec.segment10,
vendor_prod_srv_rec.segment10 || l_delimiter,
vendor_prod_srv_rec.segment10) ||
nvl2(vendor_prod_srv_rec.segment11,
vendor_prod_srv_rec.segment11 || l_delimiter,
vendor_prod_srv_rec.segment11) ||
nvl2(vendor_prod_srv_rec.segment12,
vendor_prod_srv_rec.segment12 || l_delimiter,
vendor_prod_srv_rec.segment12) ||
nvl2(vendor_prod_srv_rec.segment13,
vendor_prod_srv_rec.segment13 || l_delimiter,
vendor_prod_srv_rec.segment13) ||
nvl2(vendor_prod_srv_rec.segment14,
vendor_prod_srv_rec.segment14 || l_delimiter,
vendor_prod_srv_rec.segment14) ||
nvl2(vendor_prod_srv_rec.segment15,
vendor_prod_srv_rec.segment15 || l_delimiter,
vendor_prod_srv_rec.segment15) ||
nvl2(vendor_prod_srv_rec.segment16,
vendor_prod_srv_rec.segment16 || l_delimiter,
vendor_prod_srv_rec.segment16) ||
nvl2(vendor_prod_srv_rec.segment17,
vendor_prod_srv_rec.segment17 || l_delimiter,
vendor_prod_srv_rec.segment17) ||
nvl2(vendor_prod_srv_rec.segment18,
vendor_prod_srv_rec.segment18 || l_delimiter,
vendor_prod_srv_rec.segment18) ||
nvl2(vendor_prod_srv_rec.segment19,
vendor_prod_srv_rec.segment19 || l_delimiter,
vendor_prod_srv_rec.segment19) ||
nvl2(vendor_prod_srv_rec.segment20,
vendor_prod_srv_rec.segment20 || l_delimiter,
vendor_prod_srv_rec.segment20),
l_delimiter)
INTO l_segment_concat
FROM dual;
vendor_int_prod_srv_rec.insert_update_flag := nvl(vendor_int_prod_srv_rec.insert_update_flag,
'I');
vendor_int_prod_srv_rec.insert_update_flag := nvl(vendor_int_prod_srv_rec.insert_update_flag,
'U');
' vendor_int_prod_srv_rec.insert_update_flag: ' ||
vendor_int_prod_srv_rec.insert_update_flag);
IF (vendor_int_prod_srv_rec.insert_update_flag = 'I') THEN
create_vendor_prods_services(p_batch_id => p_batch_id,
p_vendor_prodsrv_rec => vendor_prod_srv_rec,
p_party_id => l_party_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
UPDATE pos_product_service_int
SET status = 'PROCESSED'
WHERE ps_interface_id = vendor_int_prod_srv_rec.ps_interface_id
AND sdh_batch_id = p_batch_id;
UPDATE pos_imp_batch_summary
SET total_records_imported = total_records_imported + 1,
total_inserts = total_inserts + 1,
prod_serv_inserted = prod_serv_inserted + 1,
prod_serv_imported = prod_serv_imported + 1
WHERE batch_id = vendor_int_prod_srv_rec.sdh_batch_id;
UPDATE pos_product_service_int
SET status = 'REJECTED'
WHERE ps_interface_id = vendor_int_prod_srv_rec.ps_interface_id
AND sdh_batch_id = p_batch_id;
IF (insert_rejections(p_batch_id,
l_request_id,
'POS_PRODUCT_SERVICE_INT',
vendor_int_prod_srv_rec.ps_interface_id,
'POS_PROD_SERVICES_CREATION',
g_user_id,
g_login_id,
'import_vendor_prods_services') <> TRUE) THEN
IF (g_level_procedure >= g_current_runtime_level) THEN
fnd_msg_pub.count_and_get(p_count => l_msg_count,
p_data => l_msg_data);
pos_product_service_utl_pkg.update_main_ps_req(p_req_id_tbl => l_req_id_tab,
p_status => 'R',
x_return_status => l_ret_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
pos_product_service_utl_pkg.update_main_ps_req(p_req_id_tbl => l_req_id_tab,
p_status => 'R',
x_return_status => l_ret_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
pos_product_service_utl_pkg.update_main_ps_req(p_req_id_tbl => l_req_id_tab,
p_status => 'A',
x_return_status => l_ret_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
pos_product_service_utl_pkg.update_main_ps_req(p_req_id_tbl => l_req_id_tab,
p_status => 'A',
x_return_status => l_ret_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
pos_product_service_utl_pkg.update_main_ps_req(p_req_id_tbl => l_req_id_tab,
p_status => 'X',
x_return_status => l_ret_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
pos_product_service_utl_pkg.update_main_ps_req(p_req_id_tbl => l_req_id_tab,
p_status => 'X',
x_return_status => l_ret_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
UPDATE pos_product_service_int
SET status = 'PROCESSED'
WHERE ps_interface_id = vendor_int_prod_srv_rec.ps_interface_id
AND sdh_batch_id = p_batch_id;
UPDATE pos_imp_batch_summary
SET total_records_imported = total_records_imported + 1,
total_updates = total_updates + 1,
prod_serv_updated = prod_serv_updated + 1,
prod_serv_imported = prod_serv_imported + 1
WHERE batch_id = vendor_int_prod_srv_rec.sdh_batch_id;
UPDATE pos_product_service_int
SET status = 'REJECTED'
WHERE ps_interface_id = vendor_int_prod_srv_rec.ps_interface_id
AND sdh_batch_id = p_batch_id;
IF (insert_rejections(p_batch_id,
l_request_id,
'POS_PRODUCT_SERVICE_INT',
vendor_int_prod_srv_rec.ps_interface_id,
'POS_PROD_SERVICES_UPDATION',
g_user_id,
g_login_id,
'import_vendor_prods_services') <> TRUE) THEN
IF (g_level_procedure >= g_current_runtime_level) THEN
fnd_msg_pub.count_and_get(p_count => l_msg_count,
p_data => l_msg_data);
SELECT lookup_code
INTO l_dummy_lookup
FROM fnd_lookup_values
WHERE lookup_type = p_vendor_buss_class_rec.lookup_type
AND lookup_code = p_vendor_buss_class_rec.lookup_code
AND enabled_flag = 'Y'
AND nvl(end_date_active, SYSDATE + 1) > SYSDATE
AND LANGUAGE = 'US';
IF (insert_rejections(p_batch_id,
l_request_id,
'POS_BUSINESS_CLASS_INT',
p_vendor_buss_class_rec.business_class_interface_id,
'POS_INVALID_LOOKUP_CODE',
g_user_id,
g_login_id,
'Validate_Vendor_Buss_Class') <> TRUE) THEN
IF (g_level_procedure >= g_current_runtime_level) THEN
fnd_msg_pub.count_and_get(p_count => l_msg_count,
p_data => l_msg_data);
SELECT lookup_code
INTO l_dummy_lookup
FROM fnd_lookup_values_vl
WHERE lookup_type = 'MINORITY GROUP'
AND lookup_code = p_vendor_buss_class_rec.ext_attr_1
AND enabled_flag = 'Y'
AND nvl(end_date_active, SYSDATE + 1) > SYSDATE;
IF (insert_rejections(p_batch_id,
l_request_id,
'POS_BUSINESS_CLASS_INT',
p_vendor_buss_class_rec.business_class_interface_id,
'POS_INVALID_MINOR_LOOKUP_CODE',
g_user_id,
g_login_id,
'Validate_Vendor_Buss_Class') <> TRUE) THEN
IF (g_level_procedure >= g_current_runtime_level) THEN
fnd_msg_pub.count_and_get(p_count => l_msg_count,
p_data => l_msg_data);
SELECT *
FROM pos_business_class_int supp
WHERE import_request_id = l_request_id
AND business_class_interface_id IS NOT NULL
AND sdh_batch_id = p_batch_id
AND nvl(status, 'ACTIVE') NOT IN ('PROCESSED', 'REMOVED')
AND NOT EXISTS
(SELECT 1
FROM hz_imp_parties_int party
WHERE batch_id = p_batch_id
AND supp.sdh_batch_id = party.batch_id
AND supp.source_system = party.party_orig_system
AND supp.source_system_reference =
party.party_orig_system_reference
AND party.interface_status = 'R');
UPDATE pos_business_class_int api
SET import_request_id = NULL
WHERE import_request_id IS NOT NULL
AND sdh_batch_id = p_batch_id
AND nvl(status, 'ACTIVE') IN ('ACTIVE', 'REJECTED')
AND EXISTS (SELECT 'Request Completed'
FROM fnd_concurrent_requests fcr
WHERE fcr.request_id = api.import_request_id
AND fcr.phase_code = 'C');
UPDATE pos_business_class_int
SET import_request_id = l_request_id
WHERE import_request_id IS NULL
AND sdh_batch_id = p_batch_id
AND nvl(status, 'ACTIVE') <> 'PROCESSED';
UPDATE pos_business_class_int supp
SET status = 'REMOVED',
import_request_id = l_request_id
WHERE sdh_batch_id = p_batch_id
AND nvl(status, 'ACTIVE') <> 'PROCESSED'
AND EXISTS (SELECT 1
FROM hz_imp_parties_int party
WHERE batch_id = p_batch_id
AND supp.sdh_batch_id = party.batch_id
AND supp.source_system = party.party_orig_system
AND supp.source_system_reference =
party.party_orig_system_reference
AND party.interface_status = 'R');
INSERT INTO pos_supplier_int_rejections
(SELECT p_batch_id,
l_request_id,
'POS_BUSINESS_CLASS_INT',
business_class_interface_id,
'POS_INVALID_PARTY_ORIG_SYSTEM',
g_user_id,
SYSDATE,
g_login_id,
g_user_id,
SYSDATE
FROM pos_business_class_int
WHERE status = 'REMOVED'
AND import_request_id = l_request_id
AND sdh_batch_id = p_batch_id);
SELECT vendor_id
INTO vendor_buss_class_rec.vendor_id
FROM ap_suppliers supp
WHERE supp.party_id = l_party_id;
UPDATE pos_business_class_int
SET status = 'REJECTED'
WHERE business_class_interface_id =
vendor_int_buss_class_rec.business_class_interface_id
AND sdh_batch_id = p_batch_id;
IF (insert_rejections(p_batch_id,
l_request_id,
'POS_BUSINESS_CLASS_INT',
vendor_int_buss_class_rec.business_class_interface_id,
'AP_VENDOR_ID_NULL',
g_user_id,
g_login_id,
'import_vendor_buss_class') <> TRUE) THEN
IF (g_level_procedure >= g_current_runtime_level) THEN
fnd_msg_pub.count_and_get(p_count => l_msg_count,
p_data => l_msg_data);
SELECT classification_id
INTO l_classification_id
FROM pos_bus_class_attr
WHERE party_id = l_party_id
AND vendor_id = vendor_buss_class_rec.vendor_id
AND lookup_code = vendor_buss_class_rec.lookup_code;
SELECT mapping_id
INTO l_mapping_id
FROM pos_supplier_mappings
WHERE party_id = l_party_id
AND vendor_id = vendor_buss_class_rec.vendor_id;
SELECT bus_class_request_id
INTO l_buss_class_req_id
FROM pos_bus_class_reqs
WHERE mapping_id = l_mapping_id
AND lookup_code = vendor_buss_class_rec.lookup_code;
vendor_int_buss_class_rec.insert_update_flag := nvl(vendor_int_buss_class_rec.insert_update_flag,
'I');
vendor_int_buss_class_rec.insert_update_flag := nvl(vendor_int_buss_class_rec.insert_update_flag,
'U');
' insert_update_flag: ' ||
vendor_int_buss_class_rec.insert_update_flag);
IF (vendor_int_buss_class_rec.insert_update_flag = 'I') THEN
create_vendor_buss_class(p_batch_id => p_batch_id,
p_vendor_buss_class_rec => vendor_buss_class_rec,
p_party_id => l_party_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
UPDATE pos_business_class_int
SET status = 'PROCESSED'
WHERE business_class_interface_id =
vendor_int_buss_class_rec.business_class_interface_id
AND sdh_batch_id = p_batch_id;
UPDATE pos_imp_batch_summary
SET total_records_imported = total_records_imported + 1,
total_inserts = total_inserts + 1,
buss_class_inserted = buss_class_inserted + 1,
buss_class_imported = buss_class_imported + 1
WHERE batch_id = p_batch_id;
UPDATE pos_business_class_int
SET status = 'REJECTED'
WHERE business_class_interface_id =
vendor_int_buss_class_rec.business_class_interface_id
AND sdh_batch_id = p_batch_id;
IF (insert_rejections(p_batch_id,
l_request_id,
'POS_BUSINESS_CLASS_INT',
vendor_int_buss_class_rec.business_class_interface_id,
'POS_BUSS_CLASS_CREATION',
g_user_id,
g_login_id,
'import_vendor_buss_class') <> TRUE) THEN
IF (g_level_procedure >= g_current_runtime_level) THEN
fnd_msg_pub.count_and_get(p_count => l_msg_count,
p_data => l_msg_data);
pos_supp_classification_pkg.update_bus_class_attr(p_party_id => l_party_id,
p_vendor_id => vendor_buss_class_rec.vendor_id,
p_selected => '',
p_classification_id => l_classification_id,
p_request_id => l_buss_class_req_id,
p_lookup_code => vendor_int_buss_class_rec.lookup_code,
p_exp_date => vendor_int_buss_class_rec.expiration_date,
p_cert_num => vendor_int_buss_class_rec.certificate_number,
p_cert_agency => vendor_int_buss_class_rec.certifying_agency,
p_ext_attr_1 => vendor_int_buss_class_rec.ext_attr_1,
p_class_status => vendor_int_buss_class_rec.class_status,
x_classification_id => l_out_classification_id,
x_request_id => l_out_buss_class_req_id,
x_status => l_return_status,
x_exception_msg => l_msg_data);
UPDATE pos_business_class_int
SET status = 'PROCESSED'
WHERE business_class_interface_id =
vendor_int_buss_class_rec.business_class_interface_id
AND sdh_batch_id = p_batch_id;
UPDATE pos_imp_batch_summary
SET total_records_imported = total_records_imported + 1,
total_updates = total_updates + 1,
buss_class_updated = buss_class_updated + 1,
buss_class_imported = buss_class_imported + 1
WHERE batch_id = p_batch_id;
UPDATE pos_business_class_int
SET status = 'REJECTED'
WHERE business_class_interface_id =
vendor_int_buss_class_rec.business_class_interface_id
AND sdh_batch_id = p_batch_id;
IF (insert_rejections(p_batch_id,
l_request_id,
'POS_BUSINESS_CLASS_INT',
vendor_int_buss_class_rec.business_class_interface_id,
'POS_BUSS_CLASS_UPDATION',
g_user_id,
g_login_id,
'import_vendor_buss_class') <> TRUE) THEN
IF (g_level_procedure >= g_current_runtime_level) THEN
fnd_msg_pub.count_and_get(p_count => l_msg_count,
p_data => l_msg_data);
SELECT party_tax_profile_id
INTO x_party_tax_profile_id
FROM zx_party_tax_profile
WHERE party_id = p_tax_profile_rec.party_id
AND party_type_code = 'THIRD_PARTY';
SELECT fndlookup.lookup_code
INTO l_dummy_lookup
FROM fnd_lookups fndlookup
WHERE fndlookup.lookup_type LIKE 'ZX_ROUNDING_LEVEL'
AND nvl(fndlookup.start_date_active, SYSDATE) <= SYSDATE
AND nvl(fndlookup.end_date_active, SYSDATE) >= SYSDATE
AND nvl(fndlookup.enabled_flag, 'N') = 'Y'
AND lookup_code = p_tax_profile_rec.rounding_level_code
ORDER BY fndlookup.lookup_code;
IF (insert_rejections(p_batch_id,
l_request_id,
'POS_PARTY_TAX_PROFILE_INT',
p_tax_profile_rec.rounding_level_code,
'POS_INVALID_ROUNDING_LEVEL',
g_user_id,
g_login_id,
' Validate_Tax_Profile ') <> TRUE) THEN
IF (g_level_procedure >= g_current_runtime_level) THEN
fnd_msg_pub.count_and_get(p_count => l_msg_count,
p_data => l_msg_data);
SELECT fndlookup.lookup_code
INTO l_dummy_lookup
FROM fnd_lookups fndlookup
WHERE fndlookup.lookup_type LIKE 'ZX_ROUNDING_RULE'
AND nvl(fndlookup.start_date_active, SYSDATE) <= SYSDATE
AND nvl(fndlookup.end_date_active, SYSDATE) >= SYSDATE
AND nvl(fndlookup.enabled_flag, 'N') = 'Y'
AND lookup_code = p_tax_profile_rec.rounding_rule_code
ORDER BY fndlookup.lookup_code;
IF (insert_rejections(p_batch_id,
l_request_id,
'POS_PARTY_TAX_PROFILE_INT',
p_tax_profile_rec.rounding_rule_code,
'POS_INVALID_ROUNDING_RULE',
g_user_id,
g_login_id,
' Validate_Tax_Profile ') <> TRUE) THEN
IF (g_level_procedure >= g_current_runtime_level) THEN
fnd_msg_pub.count_and_get(p_count => l_msg_count,
p_data => l_msg_data);
SELECT territory_code
INTO x_country_code
FROM fnd_territories_vl
WHERE territory_short_name = p_tax_profile_rec.country_name;
IF (insert_rejections(p_batch_id,
l_request_id,
' POS_PARTY_TAX_PROFILE_INT',
p_tax_profile_rec.country_name,
'POS_INVALID_COUNTRY_NAME',
g_user_id,
g_login_id,
' Validate_Tax_Profile ') <> TRUE) THEN
IF (g_level_procedure >= g_current_runtime_level) THEN
fnd_msg_pub.count_and_get(p_count => l_msg_count,
p_data => l_msg_data);
SELECT fndlookup.lookup_code
INTO l_dummy_lookup
FROM fnd_lookups fndlookup
WHERE fndlookup.lookup_type LIKE 'ZX_REGISTRATIONS_TYPE'
AND nvl(fndlookup.start_date_active, SYSDATE) <= SYSDATE
AND nvl(fndlookup.end_date_active, SYSDATE) >= SYSDATE
AND nvl(fndlookup.enabled_flag, 'N') = 'Y'
AND lookup_code = p_tax_profile_rec.registration_type_code
ORDER BY fndlookup.lookup_code;
IF (insert_rejections(p_batch_id,
l_request_id,
'POS_PARTY_TAX_PROFILE_INT',
p_tax_profile_rec.registration_type_code,
'POS_INVALID_REGI_TYPE_CODE',
g_user_id,
g_login_id,
' Validate_Tax_Profile ') <> TRUE) THEN
IF (g_level_procedure >= g_current_runtime_level) THEN
fnd_msg_pub.count_and_get(p_count => l_msg_count,
p_data => l_msg_data);
IF (p_tax_profile_rec.insert_update_flag = 'U') THEN
fnd_file.put_line(fnd_file.log, ' Updating Tax profile');
zx_party_tax_profile_pkg.update_row(p_party_tax_profile_id => l_party_tax_profile_id,
p_collecting_authority_flag => NULL,
p_provider_type_code => NULL,
p_create_awt_dists_type_code => NULL,
p_create_awt_invoices_type_cod => NULL,
p_tax_classification_code => NULL,
p_self_assess_flag => NULL,
p_allow_offset_tax_flag => NULL,
p_rep_registration_number => p_tax_profile_rec.rep_registration_number,
p_effective_from_use_le => NULL,
p_record_type_code => NULL,
p_request_id => NULL,
p_attribute1 => NULL,
p_attribute2 => NULL,
p_attribute3 => NULL,
p_attribute4 => NULL,
p_attribute5 => NULL,
p_attribute6 => NULL,
p_attribute7 => NULL,
p_attribute8 => NULL,
p_attribute9 => NULL,
p_attribute10 => NULL,
p_attribute11 => NULL,
p_attribute12 => NULL,
p_attribute13 => NULL,
p_attribute14 => NULL,
p_attribute15 => NULL,
p_attribute_category => NULL,
p_party_id => p_tax_profile_rec.party_id,
p_program_login_id => NULL,
p_party_type_code => 'THIRD_PARTY',
p_supplier_flag => NULL,
p_customer_flag => NULL,
p_site_flag => NULL,
p_process_for_applicability_fl => NULL,
p_rounding_level_code => p_tax_profile_rec.rounding_level_code,
p_rounding_rule_code => p_tax_profile_rec.rounding_rule_code,
p_withholding_start_date => NULL,
p_inclusive_tax_flag => p_tax_profile_rec.inclusive_tax_flag,
p_allow_awt_flag => NULL,
p_use_le_as_subscriber_flag => NULL,
p_legal_establishment_flag => NULL,
p_first_party_le_flag => NULL,
p_reporting_authority_flag => NULL,
x_return_status => l_return_status,
p_registration_type_code => p_tax_profile_rec.registration_type_code,
p_country_code => l_country_code);
zx_party_tax_profile_pkg.insert_row(p_collecting_authority_flag => NULL,
p_provider_type_code => NULL,
p_create_awt_dists_type_code => NULL,
p_create_awt_invoices_type_cod => NULL,
p_tax_classification_code => NULL,
p_self_assess_flag => NULL,
p_allow_offset_tax_flag => NULL,
p_rep_registration_number => p_tax_profile_rec.rep_registration_number,
p_effective_from_use_le => NULL,
p_record_type_code => NULL,
p_request_id => NULL,
p_attribute1 => NULL,
p_attribute2 => NULL,
p_attribute3 => NULL,
p_attribute4 => NULL,
p_attribute5 => NULL,
p_attribute6 => NULL,
p_attribute7 => NULL,
p_attribute8 => NULL,
p_attribute9 => NULL,
p_attribute10 => NULL,
p_attribute11 => NULL,
p_attribute12 => NULL,
p_attribute13 => NULL,
p_attribute14 => NULL,
p_attribute15 => NULL,
p_attribute_category => NULL,
p_party_id => p_tax_profile_rec.party_id,
p_program_login_id => NULL,
p_party_type_code => 'THIRD_PARTY',
p_supplier_flag => NULL,
p_customer_flag => NULL,
p_site_flag => NULL,
p_process_for_applicability_fl => NULL,
p_rounding_level_code => p_tax_profile_rec.rounding_level_code,
p_rounding_rule_code => p_tax_profile_rec.rounding_rule_code,
p_withholding_start_date => NULL,
p_inclusive_tax_flag => p_tax_profile_rec.inclusive_tax_flag,
p_allow_awt_flag => NULL,
p_use_le_as_subscriber_flag => NULL,
p_legal_establishment_flag => NULL,
p_first_party_le_flag => NULL,
p_reporting_authority_flag => NULL,
x_return_status => l_return_status,
p_registration_type_code => p_tax_profile_rec.registration_type_code,
p_country_code => l_country_code);
SELECT party_tax_profile_id
INTO l_party_tax_profile_id
FROM zx_party_tax_profile
WHERE party_id = p_tax_profile_rec.party_id
AND party_type_code = 'THIRD_PARTY';
SELECT fndlookup.lookup_code
INTO l_dummy_lookup
FROM fnd_lookups fndlookup
WHERE fndlookup.lookup_type LIKE 'ZX_REGISTRATIONS_TYPE'
AND nvl(fndlookup.start_date_active, SYSDATE) <= SYSDATE
AND nvl(fndlookup.end_date_active, SYSDATE) >= SYSDATE
AND nvl(fndlookup.enabled_flag, 'N') = 'Y'
AND lookup_code = p_tax_reg_rec.registration_type_code
ORDER BY fndlookup.lookup_code;
IF (insert_rejections(p_batch_id,
l_request_id,
'POS_PARTY_TAX_REG_INT',
p_tax_reg_rec.registration_type_code,
'POS_INVALID_REGI_TYPE_CODE',
g_user_id,
g_login_id,
' Validate_Tax_Registration ') <> TRUE) THEN
IF (g_level_procedure >= g_current_runtime_level) THEN
fnd_msg_pub.count_and_get(p_count => l_msg_count,
p_data => l_msg_data);
SELECT fndlookup.lookup_code
INTO l_dummy_lookup
FROM fnd_lookups fndlookup
WHERE fndlookup.lookup_type LIKE 'ZX_REGISTRATIONS_STATUS'
AND nvl(fndlookup.start_date_active, SYSDATE) <= SYSDATE
AND nvl(fndlookup.end_date_active, SYSDATE) >= SYSDATE
AND nvl(fndlookup.enabled_flag, 'N') = 'Y'
AND lookup_code = p_tax_reg_rec.registration_status_code
ORDER BY fndlookup.lookup_code;
IF (insert_rejections(p_batch_id,
l_request_id,
'POS_PARTY_TAX_REG_INT',
p_tax_reg_rec.registration_status_code,
'POS_INVALID_REG_STATUS_CODE',
g_user_id,
g_login_id,
' Validate_Tax_Registration ') <> TRUE) THEN
IF (g_level_procedure >= g_current_runtime_level) THEN
fnd_msg_pub.count_and_get(p_count => l_msg_count,
p_data => l_msg_data);
SELECT loc.location_id
INTO l_dummy_location
FROM hr_locations loc
WHERE loc.location_code || ':' || ' ' || loc.address_line_1 || ' ' ||
loc.town_or_city || ' ' || loc.region_1 =
p_tax_reg_rec.registration_address
AND legal_address_flag = 'Y'
AND SYSDATE < nvl(inactive_date, SYSDATE + 1);
IF (insert_rejections(p_batch_id,
l_request_id,
'POS_PARTY_TAX_REG_INT',
p_tax_reg_rec.registration_address,
'POS_INVALID_REG_ADDRESS',
g_user_id,
g_login_id,
' Validate_Tax_Registration ') <> TRUE) THEN
IF (g_level_procedure >= g_current_runtime_level) THEN
fnd_msg_pub.count_and_get(p_count => l_msg_count,
p_data => l_msg_data);
SELECT fndlookup.lookup_code
INTO l_dummy_lookup
FROM fnd_lookups fndlookup
WHERE fndlookup.lookup_type LIKE 'ZX_REGISTRATIONS_REASON'
AND nvl(fndlookup.start_date_active, SYSDATE) <= SYSDATE
AND nvl(fndlookup.end_date_active, SYSDATE) >= SYSDATE
AND nvl(fndlookup.enabled_flag, 'N') = 'Y'
AND lookup_code = p_tax_reg_rec.registration_reason_code
ORDER BY fndlookup.lookup_code;
IF (insert_rejections(p_batch_id,
l_request_id,
'POS_PARTY_TAX_REG_INT',
p_tax_reg_rec.registration_reason_code,
'POS_INVALID_REG_REASON_CODE',
g_user_id,
g_login_id,
' Validate_Tax_Registration ') <> TRUE) THEN
IF (g_level_procedure >= g_current_runtime_level) THEN
fnd_msg_pub.count_and_get(p_count => l_msg_count,
p_data => l_msg_data);
SELECT fndlookup.lookup_code
INTO l_dummy_lookup
FROM fnd_lookups fndlookup
WHERE fndlookup.lookup_type LIKE 'ZX_REGISTRATIONS_SOURCE'
AND nvl(fndlookup.start_date_active, SYSDATE) <= SYSDATE
AND nvl(fndlookup.end_date_active, SYSDATE) >= SYSDATE
AND nvl(fndlookup.enabled_flag, 'N') = 'Y'
AND lookup_code = p_tax_reg_rec.registration_source_code
ORDER BY fndlookup.lookup_code;
IF (insert_rejections(p_batch_id,
l_request_id,
'POS_PARTY_TAX_REG_INT',
p_tax_reg_rec.registration_source_code,
'POS_INVALID_REG_SOURCE_CODE',
g_user_id,
g_login_id,
' Validate_Tax_Registration ') <> TRUE) THEN
IF (g_level_procedure >= g_current_runtime_level) THEN
fnd_msg_pub.count_and_get(p_count => l_msg_count,
p_data => l_msg_data);
SELECT fndlookup.lookup_code
INTO l_dummy_lookup
FROM fnd_lookups fndlookup
WHERE fndlookup.lookup_type LIKE 'ZX_ROUNDING_RULE'
AND nvl(fndlookup.start_date_active, SYSDATE) <= SYSDATE
AND nvl(fndlookup.end_date_active, SYSDATE) >= SYSDATE
AND nvl(fndlookup.enabled_flag, 'N') = 'Y'
AND lookup_code = p_tax_reg_rec.rounding_rule_code
ORDER BY fndlookup.lookup_code;
IF (insert_rejections(p_batch_id,
l_request_id,
'POS_PARTY_TAX_REG_INT',
p_tax_reg_rec.rounding_rule_code,
'POS_INVALID_ROUND_RULE_CODE',
g_user_id,
g_login_id,
' Validate_Tax_Registration ') <> TRUE) THEN
IF (g_level_procedure >= g_current_runtime_level) THEN
fnd_msg_pub.count_and_get(p_count => l_msg_count,
p_data => l_msg_data);
SELECT prof.party_id
INTO l_tax_authority_party_id
FROM zx_party_tax_profile prof,
hz_parties hp
WHERE hp.party_id = prof.party_id
AND prof.party_type_code = 'TAX_AUTHORITY'
AND hp.party_name = p_tax_reg_rec.tax_authority_name;
IF (insert_rejections(p_batch_id,
l_request_id,
'POS_PARTY_TAX_REG_INT',
p_tax_reg_rec.rounding_rule_code,
'POS_INVALID_TAX_AUTHORITY',
g_user_id,
g_login_id,
' Validate_Tax_Registration ') <> TRUE) THEN
IF (g_level_procedure >= g_current_runtime_level) THEN
fnd_msg_pub.count_and_get(p_count => l_msg_count,
p_data => l_msg_data);
' Calling zx_registrations_pkg.insert_row');
zx_registrations_pkg.insert_row(p_request_id => NULL,
p_attribute1 => NULL,
p_attribute2 => NULL,
p_attribute3 => NULL,
p_attribute4 => NULL,
p_attribute5 => NULL,
p_attribute6 => NULL,
p_validation_rule => NULL,
p_rounding_rule_code => p_tax_registration_rec.rounding_rule_code,
p_tax_jurisdiction_code => p_tax_registration_rec.tax_jurisdiction_code,
p_self_assess_flag => NULL,
p_registration_status_code => p_tax_registration_rec.registration_status_code,
p_registration_source_code => p_tax_registration_rec.registration_source_code,
p_registration_reason_code => p_tax_registration_rec.registration_reason_code,
p_tax => p_tax_registration_rec.tax,
p_tax_regime_code => p_tax_registration_rec.tax_regime_code,
p_inclusive_tax_flag => p_tax_registration_rec.inclusive_tax_flag,
p_effective_from => p_tax_registration_rec.effective_from,
p_effective_to => p_tax_registration_rec.effective_to,
p_rep_party_tax_name => p_tax_registration_rec.rep_party_tax_name,
p_default_registration_flag => p_tax_registration_rec.default_registration_flag,
p_bank_account_num => NULL,
p_record_type_code => NULL,
p_legal_location_id => l_location_id,
p_tax_authority_id => l_tax_authority_party_id,
p_rep_tax_authority_id => NULL,
p_coll_tax_authority_id => NULL,
p_registration_type_code => p_tax_registration_rec.registration_type_code,
p_registration_number => p_tax_registration_rec.registration_number,
p_party_tax_profile_id => p_tax_registration_rec.tax_profile_id,
p_legal_registration_id => NULL,
p_bank_id => NULL,
p_bank_branch_id => NULL,
p_account_site_id => NULL,
p_attribute14 => NULL,
p_attribute15 => NULL,
p_attribute_category => NULL,
p_program_login_id => NULL,
p_account_id => NULL,
p_tax_classification_code => NULL,
p_attribute7 => NULL,
p_attribute8 => NULL,
p_attribute9 => NULL,
p_attribute10 => NULL,
p_attribute11 => NULL,
p_attribute12 => NULL,
p_attribute13 => NULL,
x_return_status => l_creation_status);
IF (insert_rejections(p_batch_id,
l_request_id,
'POS_PARTY_TAX_REG_INT',
p_tax_registration_rec.registration_type_code,
'POS_INVALID_TAX_REG_INS',
g_user_id,
g_login_id,
' Create_Tax_Registration ') <> TRUE) THEN
IF (g_level_procedure >= g_current_runtime_level) THEN
fnd_msg_pub.count_and_get(p_count => l_msg_count,
p_data => l_msg_data);
PROCEDURE update_tax_registration
(
p_batch_id IN NUMBER,
p_registration_id IN NUMBER,
p_tax_registration_rec IN pos_party_tax_reg_int%ROWTYPE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_location_id NUMBER;
fnd_file.put_line(fnd_file.log, ' Inside update_tax_registration');
' Calling zx_registrations_pkg.update_row');
zx_registrations_pkg.update_row(p_registration_id => p_registration_id,
p_request_id => NULL,
p_attribute1 => NULL,
p_attribute2 => NULL,
p_attribute3 => NULL,
p_attribute4 => NULL,
p_attribute5 => NULL,
p_attribute6 => NULL,
p_validation_rule => NULL,
p_rounding_rule_code => p_tax_registration_rec.rounding_rule_code,
p_tax_jurisdiction_code => p_tax_registration_rec.tax_jurisdiction_code,
p_self_assess_flag => NULL,
p_registration_status_code => p_tax_registration_rec.registration_status_code,
p_registration_source_code => p_tax_registration_rec.registration_source_code,
p_registration_reason_code => p_tax_registration_rec.registration_reason_code,
p_tax => p_tax_registration_rec.tax,
p_tax_regime_code => p_tax_registration_rec.tax_regime_code,
p_inclusive_tax_flag => p_tax_registration_rec.inclusive_tax_flag,
p_effective_from => p_tax_registration_rec.effective_from,
p_effective_to => p_tax_registration_rec.effective_to,
p_rep_party_tax_name => p_tax_registration_rec.rep_party_tax_name,
p_default_registration_flag => p_tax_registration_rec.default_registration_flag,
p_bank_account_num => NULL,
p_record_type_code => NULL,
p_legal_location_id => l_location_id,
p_tax_authority_id => l_tax_authority_party_id,
p_rep_tax_authority_id => NULL,
p_coll_tax_authority_id => NULL,
p_registration_type_code => p_tax_registration_rec.registration_type_code,
p_registration_number => p_tax_registration_rec.registration_number,
p_party_tax_profile_id => p_tax_registration_rec.tax_profile_id,
p_legal_registration_id => NULL,
p_bank_id => NULL,
p_bank_branch_id => NULL,
p_account_site_id => NULL,
p_attribute14 => NULL,
p_attribute15 => NULL,
p_attribute_category => NULL,
p_program_login_id => NULL,
p_account_id => NULL,
p_tax_classification_code => NULL,
p_attribute7 => NULL,
p_attribute8 => NULL,
p_attribute9 => NULL,
p_attribute10 => NULL,
p_attribute11 => NULL,
p_attribute12 => NULL,
p_attribute13 => NULL,
x_return_status => l_creation_status);
IF (insert_rejections(p_batch_id,
l_request_id,
'POS_PARTY_TAX_REG_INT',
p_tax_registration_rec.registration_type_code,
'POS_INVALID_TAX_REG_UPD',
g_user_id,
g_login_id,
' UPDATE_TAX_REGISTRATION ') <> TRUE) THEN
IF (g_level_procedure >= g_current_runtime_level) THEN
fnd_msg_pub.count_and_get(p_count => l_msg_count,
p_data => l_msg_data);
g_module_name || 'UPDATE_TAX_REGISTRATION',
'Msg: ' || l_msg_data);
' Inside update_tax_registration EXCEPTION ' ||
' Message: ' || SQLCODE || ' ' || SQLERRM);
END update_tax_registration;
SELECT owner_id_char
INTO l_classification_type
FROM zx_fc_types_vl
WHERE classification_type_categ_code = 'PARTY_FISCAL_CLASS'
AND SYSDATE BETWEEN nvl(effective_from, SYSDATE) AND
nvl(effective_to, SYSDATE)
AND classification_type_code =
p_fiscal_class_rec.classification_type_code_name;
IF (insert_rejections(p_batch_id,
l_request_id,
'POS_FISCAL_CLASS_INT',
p_fiscal_class_rec.classification_type_code_name,
'POS_INVALID_FISCAL_CLASS_TYPE',
g_user_id,
g_login_id,
' Validate_fiscal_classification ') <> TRUE) THEN
IF (g_level_procedure >= g_current_runtime_level) THEN
fnd_msg_pub.count_and_get(p_count => l_msg_count,
p_data => l_msg_data);
SELECT class_code
INTO l_classification_code
FROM hz_class_code_denorm
WHERE SYSDATE BETWEEN nvl(start_date_active, SYSDATE) AND
nvl(end_date_active, SYSDATE)
AND LANGUAGE = userenv('LANG')
AND class_code_meaning = p_fiscal_class_rec.class_code_name
AND class_category = l_classification_type;
IF (insert_rejections(p_batch_id,
l_request_id,
'POS_FISCAL_CLASS_INT',
p_fiscal_class_rec.classification_type_code_name,
'POS_INVALID_FISCAL_CLASS_NAME',
g_user_id,
g_login_id,
' Validate_fiscal_classification ') <> TRUE) THEN
IF (g_level_procedure >= g_current_runtime_level) THEN
fnd_msg_pub.count_and_get(p_count => l_msg_count,
p_data => l_msg_data);
hz_code_assignments_pkg.insert_row(x_code_assignment_id => l_code_assignment_id,
x_owner_table_name => 'ZX_PARTY_TAX_PROFILE',
x_owner_table_id => p_fiscal_class_rec.tax_profile_id,
x_owner_table_key_1 => NULL,
x_owner_table_key_2 => NULL,
x_owner_table_key_3 => NULL,
x_owner_table_key_4 => NULL,
x_owner_table_key_5 => NULL,
x_class_category => l_classification_type,
x_class_code => l_classification_code,
x_primary_flag => 'N',
x_content_source_type => NULL,
x_start_date_active => p_fiscal_class_rec.effective_from,
x_end_date_active => p_fiscal_class_rec.effective_to,
x_status => NULL,
x_object_version_number => NULL,
x_created_by_module => NULL,
x_rank => NULL,
x_application_id => NULL,
x_actual_content_source => NULL);
UPDATE hz_code_assignments
SET status = NULL
WHERE code_assignment_id = l_code_assignment_id;
/*IF (insert_rejections(p_batch_id,
l_request_id,
'POS_FISCAL_CLASS_INT',
p_tax_registration_rec.registration_type_code,
'AP_INVALID_TAX_REG_INS',
g_user_id,
g_login_id,
' Create_Tax_Registration ') <> TRUE) THEN
IF (g_level_procedure >= g_current_runtime_level) THEN
fnd_msg_pub.count_and_get(p_count => l_msg_count,
p_data => l_msg_data);
PROCEDURE update_fiscal_classification
(
p_batch_id IN NUMBER,
p_fiscal_class_rec IN pos_fiscal_class_int%ROWTYPE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_valid VARCHAR2(100);
fnd_file.put_line(fnd_file.log, ' Inside update_fiscal_classification');
SELECT code_assignment_id,
ROWID
INTO l_code_assignment_id,
l_rowid
FROM hz_code_assignments
WHERE owner_table_name = 'ZX_PARTY_TAX_PROFILE'
AND owner_table_id = p_fiscal_class_rec.tax_profile_id
AND class_category = l_classification_type;
hz_code_assignments_pkg.update_row(x_rowid => l_rowid,
x_code_assignment_id => l_code_assignment_id,
x_owner_table_name => 'ZX_PARTY_TAX_PROFILE',
x_owner_table_id => p_fiscal_class_rec.tax_profile_id,
x_owner_table_key_1 => NULL,
x_owner_table_key_2 => NULL,
x_owner_table_key_3 => NULL,
x_owner_table_key_4 => NULL,
x_owner_table_key_5 => NULL,
x_class_category => l_classification_type,
x_class_code => l_classification_code,
x_primary_flag => 'N',
x_content_source_type => NULL,
x_start_date_active => p_fiscal_class_rec.effective_from,
x_end_date_active => p_fiscal_class_rec.effective_to,
x_status => NULL,
x_object_version_number => NULL,
x_created_by_module => NULL,
x_rank => NULL,
x_application_id => NULL,
x_actual_content_source => NULL);
UPDATE hz_code_assignments
SET status = NULL
WHERE code_assignment_id = l_code_assignment_id;
/*IF (insert_rejections(p_batch_id,
l_request_id,
'POS_FISCAL_CLASS_INT',
p_tax_registration_rec.registration_type_code,
'AP_INVALID_TAX_REG_INS',
g_user_id,
g_login_id,
' Create_Tax_Registration ') <> TRUE) THEN
IF (g_level_procedure >= g_current_runtime_level) THEN
fnd_msg_pub.count_and_get(p_count => l_msg_count,
p_data => l_msg_data);
' Inside update_fiscal_classification EXCEPTION ' ||
' Message: ' || SQLCODE || ' ' || SQLERRM);
END update_fiscal_classification;
SELECT *
FROM pos_party_tax_profile_int supp
WHERE batch_id = p_batch_id
AND nvl(status, 'ACTIVE') NOT IN ('PROCESSED', 'REMOVED')
AND NOT EXISTS
(SELECT 1
FROM hz_imp_parties_int party
WHERE batch_id = p_batch_id
AND supp.batch_id = party.batch_id
AND supp.source_system = party.party_orig_system
AND supp.source_system_reference =
party.party_orig_system_reference
AND party.interface_status = 'R');
SELECT *
FROM pos_party_tax_reg_int supp
WHERE batch_id = p_batch_id
AND nvl(status, 'ACTIVE') NOT IN ('PROCESSED', 'REMOVED')
AND NOT EXISTS
(SELECT 1
FROM hz_imp_parties_int party
WHERE batch_id = p_batch_id
AND supp.batch_id = party.batch_id
AND supp.source_system = party.party_orig_system
AND supp.source_system_reference =
party.party_orig_system_reference
AND party.interface_status = 'R');
SELECT *
FROM pos_fiscal_class_int supp
WHERE batch_id = p_batch_id
AND nvl(status, 'ACTIVE') NOT IN ('PROCESSED', 'REMOVED')
AND NOT EXISTS
(SELECT 1
FROM hz_imp_parties_int party
WHERE batch_id = p_batch_id
AND supp.batch_id = party.batch_id
AND supp.source_system = party.party_orig_system
AND supp.source_system_reference =
party.party_orig_system_reference
AND party.interface_status = 'R');
UPDATE pos_party_tax_profile_int api
SET request_id = NULL
WHERE request_id IS NOT NULL
AND batch_id = p_batch_id
AND nvl(status, 'ACTIVE') IN ('ACTIVE', 'REJECTED')
AND EXISTS (SELECT 'Request Completed'
FROM fnd_concurrent_requests fcr
WHERE fcr.request_id = api.request_id
AND fcr.phase_code = 'C');
UPDATE pos_party_tax_reg_int api
SET request_id = NULL
WHERE request_id IS NOT NULL
AND batch_id = p_batch_id
AND nvl(status, 'ACTIVE') IN ('ACTIVE', 'REJECTED')
AND EXISTS (SELECT 'Request Completed'
FROM fnd_concurrent_requests fcr
WHERE fcr.request_id = api.request_id
AND fcr.phase_code = 'C');
UPDATE pos_fiscal_class_int api
SET request_id = NULL
WHERE request_id IS NOT NULL
AND batch_id = p_batch_id
AND nvl(status, 'ACTIVE') IN ('ACTIVE', 'REJECTED')
AND EXISTS (SELECT 'Request Completed'
FROM fnd_concurrent_requests fcr
WHERE fcr.request_id = api.request_id
AND fcr.phase_code = 'C');
UPDATE pos_party_tax_profile_int
SET request_id = l_request_id
WHERE request_id IS NULL
AND batch_id = p_batch_id
AND nvl(status, 'ACTIVE') <> 'PROCESSED';
UPDATE pos_party_tax_reg_int
SET request_id = l_request_id
WHERE request_id IS NULL
AND batch_id = p_batch_id
AND nvl(status, 'ACTIVE') <> 'PROCESSED';
UPDATE pos_fiscal_class_int
SET request_id = l_request_id
WHERE request_id IS NULL
AND batch_id = p_batch_id
AND nvl(status, 'ACTIVE') <> 'PROCESSED';
UPDATE pos_party_tax_profile_int supp
SET status = 'REMOVED',
request_id = l_request_id
WHERE batch_id = p_batch_id
AND nvl(status, 'ACTIVE') <> 'PROCESSED'
AND EXISTS (SELECT 1
FROM hz_imp_parties_int party
WHERE batch_id = p_batch_id
AND supp.batch_id = party.batch_id
AND supp.source_system = party.party_orig_system
AND supp.source_system_reference =
party.party_orig_system_reference
AND party.interface_status = 'R');
INSERT INTO pos_supplier_int_rejections
(SELECT p_batch_id,
l_request_id,
'POS_PRODUCT_SERVICE_INT',
tax_profile_interface_id,
'POS_INVALID_PARTY_ORIG_SYSTEM',
g_user_id,
SYSDATE,
g_login_id,
g_user_id,
SYSDATE
FROM pos_party_tax_profile_int
WHERE status = 'REMOVED'
AND request_id = l_request_id
AND batch_id = p_batch_id);
UPDATE pos_party_tax_reg_int supp
SET status = 'REMOVED',
request_id = l_request_id
WHERE batch_id = p_batch_id
AND nvl(status, 'ACTIVE') <> 'PROCESSED'
AND EXISTS (SELECT 1
FROM hz_imp_parties_int party
WHERE batch_id = p_batch_id
AND supp.batch_id = party.batch_id
AND supp.source_system = party.party_orig_system
AND supp.source_system_reference =
party.party_orig_system_reference
AND party.interface_status = 'R');
INSERT INTO pos_supplier_int_rejections
(SELECT p_batch_id,
l_request_id,
'POS_PRODUCT_SERVICE_INT',
tax_reg_interface_id,
'POS_INVALID_PARTY_ORIG_SYSTEM',
g_user_id,
SYSDATE,
g_login_id,
g_user_id,
SYSDATE
FROM pos_party_tax_reg_int
WHERE status = 'REMOVED'
AND request_id = l_request_id
AND batch_id = p_batch_id);
UPDATE pos_fiscal_class_int supp
SET status = 'REMOVED',
request_id = l_request_id
WHERE batch_id = p_batch_id
AND nvl(status, 'ACTIVE') <> 'PROCESSED'
AND EXISTS (SELECT 1
FROM hz_imp_parties_int party
WHERE batch_id = p_batch_id
AND supp.batch_id = party.batch_id
AND supp.source_system = party.party_orig_system
AND supp.source_system_reference =
party.party_orig_system_reference
AND party.interface_status = 'R');
INSERT INTO pos_supplier_int_rejections
(SELECT p_batch_id,
l_request_id,
'POS_PRODUCT_SERVICE_INT',
fiscal_class_interface_id,
'POS_INVALID_PARTY_ORIG_SYSTEM',
g_user_id,
SYSDATE,
g_login_id,
g_user_id,
SYSDATE
FROM pos_fiscal_class_int
WHERE status = 'REMOVED'
AND request_id = l_request_id
AND batch_id = p_batch_id);
IF (l_tax_profile_rec_tab(cntr).insert_update_flag IS NULL) THEN
BEGIN
SELECT 'U'
INTO l_tax_profile_rec_tab(cntr).insert_update_flag
FROM zx_party_tax_profile
WHERE party_id = l_tax_profile_rec_tab(cntr).party_id
AND party_type_code = 'THIRD_PARTY';
l_tax_profile_rec_tab(cntr).insert_update_flag := 'I';
' l_tax_profile_rec_tab(cntr).insert_update_flag: ' || l_tax_profile_rec_tab(cntr)
.insert_update_flag);
UPDATE pos_party_tax_reg_int
SET tax_profile_id = l_tax_profile_id
WHERE tax_profile_interface_id = l_tax_profile_rec_tab(cntr)
.tax_profile_interface_id
AND batch_id = p_batch_id;
UPDATE pos_fiscal_class_int
SET tax_profile_id = l_tax_profile_id
WHERE tax_profile_interface_id = l_tax_profile_rec_tab(cntr)
.tax_profile_interface_id
AND batch_id = p_batch_id;
UPDATE pos_party_tax_profile_int
SET status = 'PROCESSED'
WHERE tax_profile_interface_id = l_tax_profile_rec_tab(cntr)
.tax_profile_interface_id
AND batch_id = p_batch_id;
IF (l_tax_profile_rec_tab(cntr).insert_update_flag = 'I') THEN
UPDATE pos_imp_batch_summary
SET total_records_imported = total_records_imported + 1,
total_inserts = total_inserts + 1,
tax_dtls_inserted = tax_dtls_inserted + 1,
tax_dtls_imported = tax_dtls_imported + 1
WHERE batch_id = p_batch_id;
UPDATE pos_imp_batch_summary
SET total_records_imported = total_records_imported + 1,
total_updates = total_updates + 1,
tax_dtls_updated = tax_dtls_updated + 1,
tax_dtls_imported = tax_dtls_imported + 1
WHERE batch_id = p_batch_id;
UPDATE pos_party_tax_profile_int
SET status = 'REJECTED'
WHERE tax_profile_interface_id = l_tax_profile_rec_tab(cntr)
.tax_profile_interface_id
AND batch_id = p_batch_id;
IF (insert_rejections(p_batch_id,
l_request_id,
'POS_PARTY_TAX_PROFILE_INT',
l_tax_profile_rec_tab(cntr)
.tax_profile_interface_id,
'POS_TAX_PROFILE_CREATION',
g_user_id,
g_login_id,
'import_vendor_tax_dtls') <> TRUE) THEN
IF (g_level_procedure >= g_current_runtime_level) THEN
fnd_msg_pub.count_and_get(p_count => l_msg_count,
p_data => l_msg_data);
SELECT party_tax_profile_id
INTO l_tax_profile_id
FROM zx_party_tax_profile
WHERE party_id = l_party_id
AND party_type_code = 'THIRD_PARTY';
IF (l_tax_reg_rec_tab(cntr).insert_update_flag IS NULL) THEN
BEGIN
-- get the registration id
SELECT 'U'
INTO l_tax_reg_rec_tab(cntr).insert_update_flag
FROM zx_registrations
WHERE party_tax_profile_id = l_tax_profile_id
AND tax_regime_code = l_tax_reg_rec_tab(cntr).tax_regime_code
AND SYSDATE BETWEEN effective_from AND
nvl(effective_to, SYSDATE);
l_tax_reg_rec_tab(cntr).insert_update_flag := 'I';
' l_tax_reg_rec_tab(cntr).insert_update_flag: ' || l_tax_reg_rec_tab(cntr)
.insert_update_flag);
IF (l_tax_reg_rec_tab(cntr).insert_update_flag = 'U') THEN
BEGIN
-- get the registration id
SELECT registration_id,
effective_from
INTO l_tax_reg_id,
l_effective_from_date
FROM zx_registrations
WHERE party_tax_profile_id = l_tax_profile_id
AND tax_regime_code = l_tax_reg_rec_tab(cntr).tax_regime_code
AND SYSDATE BETWEEN effective_from AND
nvl(effective_to, SYSDATE);
update_tax_registration(p_batch_id => p_batch_id,
p_registration_id => l_tax_reg_id,
p_tax_registration_rec => l_tax_reg_rec_tab(cntr),
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
UPDATE pos_party_tax_reg_int
SET status = 'PROCESSED'
WHERE tax_reg_interface_id = l_tax_reg_rec_tab(cntr)
.tax_reg_interface_id
AND batch_id = p_batch_id;
UPDATE pos_imp_batch_summary
SET total_records_imported = total_records_imported + 1,
total_updates = total_updates + 1,
tax_dtls_updated = tax_dtls_updated + 1,
tax_dtls_imported = tax_dtls_imported + 1
WHERE batch_id = p_batch_id;
UPDATE pos_party_tax_reg_int
SET status = 'PROCESSED'
WHERE tax_reg_interface_id = l_tax_reg_rec_tab(cntr)
.tax_reg_interface_id
AND batch_id = p_batch_id;
UPDATE pos_imp_batch_summary
SET total_records_imported = total_records_imported + 1,
total_inserts = total_inserts + 1,
tax_dtls_inserted = tax_dtls_inserted + 1,
tax_dtls_imported = tax_dtls_imported + 1
WHERE batch_id = p_batch_id;
UPDATE pos_party_tax_reg_int
SET status = 'REJECTED'
WHERE tax_reg_interface_id = l_tax_reg_rec_tab(cntr)
.tax_reg_interface_id
AND batch_id = p_batch_id;
IF (insert_rejections(p_batch_id,
l_request_id,
'POS_PARTY_TAX_REG_INT',
l_tax_reg_rec_tab(cntr).tax_reg_interface_id,
'POS_TAX_REGISTRATION_CREATION',
g_user_id,
g_login_id,
'import_vendor_tax_dtls') <> TRUE) THEN
IF (g_level_procedure >= g_current_runtime_level) THEN
fnd_msg_pub.count_and_get(p_count => l_msg_count,
p_data => l_msg_data);
SELECT party_tax_profile_id
INTO l_tax_profile_id
FROM zx_party_tax_profile
WHERE party_id = l_party_id
AND party_type_code = 'THIRD_PARTY';
UPDATE pos_fiscal_class_int
SET status = 'REJECTED'
WHERE fiscal_class_interface_id = l_fiscal_class_rec_tab(cntr)
.fiscal_class_interface_id
AND batch_id = p_batch_id;
IF (insert_rejections(p_batch_id,
l_request_id,
'POS_FISCAL_CLASS_INT',
l_fiscal_class_rec_tab(cntr)
.fiscal_class_interface_id,
'POS_INVALID_TAX_PROFILE',
g_user_id,
g_login_id,
'import_vendor_tax_dtls') <> TRUE) THEN
IF (g_level_procedure >= g_current_runtime_level) THEN
fnd_msg_pub.count_and_get(p_count => l_msg_count,
p_data => l_msg_data);
IF (l_fiscal_class_rec_tab(cntr).insert_update_flag IS NULL) THEN
BEGIN
SELECT owner_id_char
INTO l_classification_type
FROM zx_fc_types_vl
WHERE classification_type_categ_code = 'PARTY_FISCAL_CLASS'
AND SYSDATE BETWEEN nvl(effective_from, SYSDATE) AND
nvl(effective_to, SYSDATE)
AND classification_type_code = l_fiscal_class_rec_tab(cntr)
.classification_type_code_name;
SELECT 'U'
INTO l_fiscal_class_rec_tab(cntr).insert_update_flag
FROM hz_code_assignments
WHERE owner_table_name = 'ZX_PARTY_TAX_PROFILE'
AND owner_table_id = l_fiscal_class_rec_tab(cntr)
.tax_profile_id
AND class_category = l_classification_type;
l_fiscal_class_rec_tab(cntr).insert_update_flag := 'I';
' l_fiscal_class_rec_tab(cntr).insert_update_flag: ' || l_fiscal_class_rec_tab(cntr)
.insert_update_flag);
IF (l_fiscal_class_rec_tab(cntr).insert_update_flag = 'I') THEN
create_fiscal_classification(p_batch_id => p_batch_id,
p_fiscal_class_rec => l_fiscal_class_rec_tab(cntr),
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
UPDATE pos_fiscal_class_int
SET status = 'PROCESSED'
WHERE fiscal_class_interface_id = l_fiscal_class_rec_tab(cntr)
.fiscal_class_interface_id
AND batch_id = p_batch_id;
UPDATE pos_imp_batch_summary
SET total_records_imported = total_records_imported + 1,
total_inserts = total_inserts + 1,
tax_dtls_inserted = tax_dtls_inserted + 1,
tax_dtls_imported = tax_dtls_imported + 1
WHERE batch_id = p_batch_id;
update_fiscal_classification(p_batch_id => p_batch_id,
p_fiscal_class_rec => l_fiscal_class_rec_tab(cntr),
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
UPDATE pos_fiscal_class_int
SET status = 'PROCESSED'
WHERE fiscal_class_interface_id = l_fiscal_class_rec_tab(cntr)
.fiscal_class_interface_id
AND batch_id = p_batch_id;
UPDATE pos_imp_batch_summary
SET total_records_imported = total_records_imported + 1,
total_updates = total_updates + 1,
tax_dtls_updated = tax_dtls_updated + 1,
tax_dtls_imported = tax_dtls_imported + 1
WHERE batch_id = p_batch_id;
UPDATE pos_fiscal_class_int
SET status = 'REJECTED'
WHERE fiscal_class_interface_id = l_fiscal_class_rec_tab(cntr)
.fiscal_class_interface_id
AND batch_id = p_batch_id;
IF (insert_rejections(p_batch_id,
l_request_id,
'POS_FISCAL_CLASS_INT',
l_fiscal_class_rec_tab(cntr)
.fiscal_class_interface_id,
'POS_FISCAL_CLASS_CREATION',
g_user_id,
g_login_id,
'import_vendor_tax_dtls') <> TRUE) THEN
IF (g_level_procedure >= g_current_runtime_level) THEN
fnd_msg_pub.count_and_get(p_count => l_msg_count,
p_data => l_msg_data);
SELECT *
FROM pos_bank_account_det_int supp
WHERE batch_id = p_batch_id
AND nvl(interface_status, 'ACTIVE') NOT IN
('PROCESSED', 'REMOVED')
AND NOT EXISTS
(SELECT 1
FROM hz_imp_parties_int party
WHERE batch_id = p_batch_id
AND supp.batch_id = party.batch_id
AND supp.source_system = party.party_orig_system
AND supp.source_system_reference =
party.party_orig_system_reference
AND party.interface_status = 'R');
SELECT *
FROM pos_bank_accnt_owners_int supp
WHERE batch_id = p_batch_id
AND nvl(interface_status, 'ACTIVE') NOT IN
('PROCESSED', 'REMOVED')
AND NOT EXISTS
(SELECT 1
FROM hz_imp_parties_int party
WHERE batch_id = p_batch_id
AND supp.batch_id = party.batch_id
AND supp.source_system = party.party_orig_system
AND supp.source_system_reference =
party.party_orig_system_reference
AND party.interface_status = 'R');
UPDATE pos_bank_account_det_int api
SET request_id = NULL
WHERE request_id IS NOT NULL
AND batch_id = p_batch_id
AND nvl(interface_status, 'ACTIVE') IN ('ACTIVE', 'REJECTED')
AND EXISTS (SELECT 'Request Completed'
FROM fnd_concurrent_requests fcr
WHERE fcr.request_id = api.request_id
AND fcr.phase_code = 'C');
UPDATE pos_bank_accnt_owners_int api
SET request_id = NULL
WHERE request_id IS NOT NULL
AND batch_id = p_batch_id
AND nvl(interface_status, 'ACTIVE') IN ('ACTIVE', 'REJECTED')
AND EXISTS (SELECT 'Request Completed'
FROM fnd_concurrent_requests fcr
WHERE fcr.request_id = api.request_id
AND fcr.phase_code = 'C');
UPDATE pos_bank_account_det_int
SET request_id = l_request_id
WHERE request_id IS NULL
AND batch_id = p_batch_id
AND nvl(interface_status, 'ACTIVE') <> 'PROCESSED';
UPDATE pos_bank_accnt_owners_int
SET request_id = l_request_id
WHERE request_id IS NULL
AND batch_id = p_batch_id
AND nvl(interface_status, 'ACTIVE') <> 'PROCESSED';
UPDATE pos_bank_account_det_int supp
SET interface_status = 'REMOVED',
request_id = l_request_id
WHERE batch_id = p_batch_id
AND nvl(interface_status, 'ACTIVE') <> 'PROCESSED'
AND EXISTS (SELECT 1
FROM hz_imp_parties_int party
WHERE batch_id = p_batch_id
AND supp.batch_id = party.batch_id
AND supp.source_system = party.party_orig_system
AND supp.source_system_reference =
party.party_orig_system_reference
AND party.interface_status = 'R');
INSERT INTO pos_supplier_int_rejections
(SELECT p_batch_id,
l_request_id,
'POS_BANK_ACCOUNT_DET_INT',
bank_account_interface_id,
'POS_INVALID_PARTY_ORIG_SYSTEM',
g_user_id,
SYSDATE,
g_login_id,
g_user_id,
SYSDATE
FROM pos_bank_account_det_int
WHERE interface_status = 'REMOVED'
AND request_id = l_request_id
AND batch_id = p_batch_id);
UPDATE pos_bank_accnt_owners_int supp
SET interface_status = 'REMOVED',
request_id = l_request_id
WHERE batch_id = p_batch_id
AND nvl(interface_status, 'ACTIVE') <> 'PROCESSED'
AND EXISTS (SELECT 1
FROM hz_imp_parties_int party
WHERE batch_id = p_batch_id
AND supp.batch_id = party.batch_id
AND supp.source_system = party.party_orig_system
AND supp.source_system_reference =
party.party_orig_system_reference
AND party.interface_status = 'R');
INSERT INTO pos_supplier_int_rejections
(SELECT p_batch_id,
l_request_id,
'POS_BANK_ACCNT_OWNERS_INT',
bank_acct_owner_interface_id,
'POS_INVALID_PARTY_ORIG_SYSTEM',
g_user_id,
SYSDATE,
g_login_id,
g_user_id,
SYSDATE
FROM pos_bank_accnt_owners_int
WHERE interface_status = 'REMOVED'
AND request_id = l_request_id
AND batch_id = p_batch_id);
UPDATE pos_bank_account_det_int
SET interface_status = 'REJECTED'
WHERE bank_account_interface_id =
bank_account_dtls_rec.bank_account_interface_id
AND batch_id = p_batch_id;
IF (insert_rejections(p_batch_id,
l_request_id,
'POS_BANK_ACCOUNT_DET_INT',
bank_account_dtls_rec.bank_account_interface_id,
'POS_INVALID_BANK_INFO',
g_user_id,
g_login_id,
'IMPORT_VENDOR_BANK_DTLS') <> TRUE) THEN
IF (g_level_procedure >= g_current_runtime_level) THEN
fnd_msg_pub.count_and_get(p_count => l_msg_count,
p_data => l_msg_data);
fnd_file.put_line(fnd_file.log, ' Going for bank update');
SELECT object_version_number
INTO l_new_bank_rec.object_version_number
FROM hz_parties
WHERE party_id = l_bank_id;
iby_ext_bankacct_pub.update_ext_bank(p_api_version => 1,
p_init_msg_list => fnd_api.g_true,
p_ext_bank_rec => l_new_bank_rec,
x_return_status => l_ret_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_response => l_create_bank_resp);
' failed in iby_ext_bankacct_pub.create/update_ext_bank' ||
' Interface_Id: ' ||
bank_account_dtls_rec.bank_account_interface_id ||
', No. of Messages: ' || l_msg_count ||
', Message: Bank doesnt exist');
UPDATE pos_bank_account_det_int
SET interface_status = 'REJECTED'
WHERE bank_account_interface_id =
bank_account_dtls_rec.bank_account_interface_id
AND batch_id = p_batch_id;
IF (insert_rejections(p_batch_id,
l_request_id,
'POS_BANK_ACCOUNT_DET_INT',
bank_account_dtls_rec.bank_account_interface_id,
'POS_INVALID_BANK_INFO',
g_user_id,
g_login_id,
'IMPORT_VENDOR_BANK_DTLS') <> TRUE) THEN
IF (g_level_procedure >= g_current_runtime_level) THEN
fnd_msg_pub.count_and_get(p_count => l_msg_count,
p_data => l_msg_data);
' failed in iby_ext_bankacct_pub.create/update_ext_bank ' ||
' Interface_Id: ' ||
bank_account_dtls_rec.bank_account_interface_id ||
', No. of Messages: ' || l_msg_count ||
', Message: ' || l_msg_data);
UPDATE pos_bank_account_det_int
SET bank_id = l_bank_id
WHERE bank_account_interface_id =
bank_account_dtls_rec.bank_account_interface_id
AND batch_id = p_batch_id;
SELECT ROWID
INTO l_bank_rowid
FROM hz_parties
WHERE party_id = l_bank_id;
hz_parties_pkg.update_row(x_rowid => l_bank_rowid,
x_party_id => l_bank_id,
x_party_number => NULL,
x_party_name => NULL,
x_party_type => NULL,
x_validated_flag => NULL,
x_attribute_category => NULL,
x_attribute1 => NULL,
x_attribute2 => NULL,
x_attribute3 => NULL,
x_attribute4 => NULL,
x_attribute5 => NULL,
x_attribute6 => NULL,
x_attribute7 => NULL,
x_attribute8 => NULL,
x_attribute9 => NULL,
x_attribute10 => NULL,
x_attribute11 => NULL,
x_attribute12 => NULL,
x_attribute13 => NULL,
x_attribute14 => NULL,
x_attribute15 => NULL,
x_attribute16 => NULL,
x_attribute17 => NULL,
x_attribute18 => NULL,
x_attribute19 => NULL,
x_attribute20 => NULL,
x_attribute21 => NULL,
x_attribute22 => NULL,
x_attribute23 => NULL,
x_attribute24 => NULL,
x_orig_system_reference => NULL,
x_sic_code => NULL,
x_hq_branch_ind => NULL,
x_customer_key => NULL,
x_tax_reference => NULL,
x_jgzz_fiscal_code => NULL,
x_person_pre_name_adjunct => NULL,
x_person_first_name => NULL,
x_person_middle_name => NULL,
x_person_last_name => NULL,
x_person_name_suffix => NULL,
x_person_title => NULL,
x_person_academic_title => NULL,
x_person_previous_last_name => NULL,
x_known_as => NULL,
x_person_iden_type => NULL,
x_person_identifier => NULL,
x_group_type => NULL,
x_country => NULL,
x_address1 => bank_account_dtls_rec.bank_address_line1,
x_address2 => bank_account_dtls_rec.bank_address_line2,
x_address3 => bank_account_dtls_rec.bank_address_line3,
x_address4 => NULL,
x_city => bank_account_dtls_rec.bank_city,
x_postal_code => bank_account_dtls_rec.bank_zip,
x_state => bank_account_dtls_rec.bank_state,
x_province => NULL,
x_status => NULL,
x_county => NULL,
x_sic_code_type => NULL,
x_url => NULL,
x_email_address => NULL,
x_analysis_fy => NULL,
x_fiscal_yearend_month => NULL,
x_employees_total => NULL,
x_curr_fy_potential_revenue => NULL,
x_next_fy_potential_revenue => NULL,
x_year_established => NULL,
x_gsa_indicator_flag => NULL,
x_mission_statement => NULL,
x_organization_name_phonetic => NULL,
x_person_first_name_phonetic => NULL,
x_person_last_name_phonetic => NULL,
x_language_name => NULL,
x_category_code => NULL,
x_salutation => NULL,
x_known_as2 => NULL,
x_known_as3 => NULL,
x_known_as4 => NULL,
x_known_as5 => NULL,
x_object_version_number => NULL,
x_duns_number_c => NULL,
x_created_by_module => NULL,
x_application_id => NULL);
UPDATE pos_bank_account_det_int
SET interface_status = 'REJECTED'
WHERE bank_account_interface_id =
bank_account_dtls_rec.bank_account_interface_id
AND batch_id = p_batch_id;
IF (insert_rejections(p_batch_id,
l_request_id,
'POS_BANK_ACCOUNT_DET_INT',
bank_account_dtls_rec.bank_account_interface_id,
'POS_FAILED_BANK_END_DATE',
g_user_id,
g_login_id,
'IMPORT_VENDOR_BANK_DTLS') <> TRUE) THEN
IF (g_level_procedure >= g_current_runtime_level) THEN
fnd_msg_pub.count_and_get(p_count => l_msg_count,
p_data => l_msg_data);
' Update bank end date: ' || l_msg_data);
UPDATE pos_bank_account_det_int
SET interface_status = 'REJECTED'
WHERE bank_account_interface_id =
bank_account_dtls_rec.bank_account_interface_id
AND batch_id = p_batch_id;
IF (insert_rejections(p_batch_id,
l_request_id,
'POS_BANK_ACCOUNT_DET_INT',
bank_account_dtls_rec.bank_account_interface_id,
'POS_INVALID_BRANCH_INFO',
g_user_id,
g_login_id,
'IMPORT_VENDOR_BANK_DTLS') <> TRUE) THEN
IF (g_level_procedure >= g_current_runtime_level) THEN
fnd_msg_pub.count_and_get(p_count => l_msg_count,
p_data => l_msg_data);
' Update bank end date: ' || l_msg_data);
' Message: Going for branch update');
/*SELECT object_version_number
INTO l_new_branch_rec.bch_object_version_number
FROM iby_ext_bank_branches_v
WHERE branch_party_id = l_branch_id;*/
SELECT object_version_number
INTO l_new_branch_rec.bch_object_version_number
FROM hz_parties
WHERE party_id = l_branch_id;
SELECT hca.object_version_number
INTO l_new_branch_rec.typ_object_version_number
FROM hz_code_assignments hca
WHERE hca.class_category = 'BANK_BRANCH_TYPE'
AND hca.owner_table_name = 'HZ_PARTIES'
AND hca.owner_table_id = l_branch_id
--AND hca.primary_flag='Y'
AND SYSDATE BETWEEN start_date_active AND
nvl(end_date_active, SYSDATE + 1)
AND hca.status = 'A';
iby_ext_bankacct_pub.update_ext_bank_branch(p_api_version => 1,
p_init_msg_list => fnd_api.g_true,
p_ext_bank_branch_rec => l_new_branch_rec,
x_return_status => l_ret_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_response => l_create_branch_resp);
' failed in iby_ext_bankacct_pub.create/update_ext_bank_branch' ||
' Interface_Id: ' ||
bank_account_dtls_rec.bank_account_interface_id ||
', No. of Messages: ' || l_msg_count ||
', Message: Branch doesnt exist');
UPDATE pos_bank_account_det_int
SET interface_status = 'REJECTED'
WHERE bank_account_interface_id =
bank_account_dtls_rec.bank_account_interface_id
AND batch_id = p_batch_id;
IF (insert_rejections(p_batch_id,
l_request_id,
'POS_BANK_ACCOUNT_DET_INT',
bank_account_dtls_rec.bank_account_interface_id,
'POS_INVALID_BRANCH_INFO',
g_user_id,
g_login_id,
'IMPORT_VENDOR_BANK_DTLS') <> TRUE) THEN
IF (g_level_procedure >= g_current_runtime_level) THEN
fnd_msg_pub.count_and_get(p_count => l_msg_count,
p_data => l_msg_data);
' Update bank end date: ' || l_msg_data);
' failed in iby_ext_bankacct_pub.create/update_ext_bank_branch' ||
' Interface_Id: ' ||
bank_account_dtls_rec.bank_account_interface_id ||
', No. of Messages: ' || l_msg_count ||
', Message: ' || l_msg_data);
UPDATE pos_bank_account_det_int
SET branch_id = l_branch_id
WHERE bank_account_interface_id =
bank_account_dtls_rec.bank_account_interface_id
AND batch_id = p_batch_id;
SELECT ROWID
INTO l_branch_rowid
FROM hz_parties
WHERE party_id = l_branch_id;
hz_parties_pkg.update_row(x_rowid => l_branch_rowid,
x_party_id => l_branch_id,
x_party_number => NULL,
x_party_name => NULL,
x_party_type => NULL,
x_validated_flag => NULL,
x_attribute_category => NULL,
x_attribute1 => NULL,
x_attribute2 => NULL,
x_attribute3 => NULL,
x_attribute4 => NULL,
x_attribute5 => NULL,
x_attribute6 => NULL,
x_attribute7 => NULL,
x_attribute8 => NULL,
x_attribute9 => NULL,
x_attribute10 => NULL,
x_attribute11 => NULL,
x_attribute12 => NULL,
x_attribute13 => NULL,
x_attribute14 => NULL,
x_attribute15 => NULL,
x_attribute16 => NULL,
x_attribute17 => NULL,
x_attribute18 => NULL,
x_attribute19 => NULL,
x_attribute20 => NULL,
x_attribute21 => NULL,
x_attribute22 => NULL,
x_attribute23 => NULL,
x_attribute24 => NULL,
x_orig_system_reference => NULL,
x_sic_code => NULL,
x_hq_branch_ind => NULL,
x_customer_key => NULL,
x_tax_reference => NULL,
x_jgzz_fiscal_code => NULL,
x_person_pre_name_adjunct => NULL,
x_person_first_name => NULL,
x_person_middle_name => NULL,
x_person_last_name => NULL,
x_person_name_suffix => NULL,
x_person_title => NULL,
x_person_academic_title => NULL,
x_person_previous_last_name => NULL,
x_known_as => NULL,
x_person_iden_type => NULL,
x_person_identifier => NULL,
x_group_type => NULL,
x_country => NULL,
x_address1 => bank_account_dtls_rec.branch_address_line1,
x_address2 => bank_account_dtls_rec.branch_address_line2,
x_address3 => bank_account_dtls_rec.branch_address_line3,
x_address4 => NULL,
x_city => bank_account_dtls_rec.branch_city,
x_postal_code => bank_account_dtls_rec.branch_zip,
x_state => bank_account_dtls_rec.branch_state,
x_province => NULL,
x_status => NULL,
x_county => NULL,
x_sic_code_type => NULL,
x_url => NULL,
x_email_address => NULL,
x_analysis_fy => NULL,
x_fiscal_yearend_month => NULL,
x_employees_total => NULL,
x_curr_fy_potential_revenue => NULL,
x_next_fy_potential_revenue => NULL,
x_year_established => NULL,
x_gsa_indicator_flag => NULL,
x_mission_statement => NULL,
x_organization_name_phonetic => NULL,
x_person_first_name_phonetic => NULL,
x_person_last_name_phonetic => NULL,
x_language_name => NULL,
x_category_code => NULL,
x_salutation => NULL,
x_known_as2 => NULL,
x_known_as3 => NULL,
x_known_as4 => NULL,
x_known_as5 => NULL,
x_object_version_number => NULL,
x_duns_number_c => NULL,
x_created_by_module => NULL,
x_application_id => NULL);
UPDATE pos_bank_account_det_int
SET interface_status = 'REJECTED'
WHERE bank_account_interface_id =
bank_account_dtls_rec.bank_account_interface_id
AND batch_id = p_batch_id;
IF (insert_rejections(p_batch_id,
l_request_id,
'POS_BANK_ACCOUNT_DET_INT',
bank_account_dtls_rec.bank_account_interface_id,
'POS_FAILED_BRANCH_END_DATE',
g_user_id,
g_login_id,
'IMPORT_VENDOR_BANK_DTLS') <> TRUE) THEN
IF (g_level_procedure >= g_current_runtime_level) THEN
fnd_msg_pub.count_and_get(p_count => l_msg_count,
p_data => l_msg_data);
' Update bank end date: ' || l_msg_data);
SELECT owner_table_id
INTO l_party_site_id
FROM hz_orig_sys_references
WHERE orig_system = bank_account_dtls_rec.party_site_orig_sys
AND orig_system_reference =
bank_account_dtls_rec.party_site_orig_sys_ref
AND owner_table_name = 'HZ_PARTY_SITES'
AND nvl(end_date_active, SYSDATE) >= SYSDATE;
SELECT hps.party_site_id
/*, hps.party_site_name, hzl.address1, hzl.address2, hzl.address3, hzl.address4,
hzl.state, hzl.province, hzl.county, hzl.country, fvl.territory_short_name as country_name
, hzl.city , hzl.postal_code*/
INTO l_party_site_id
FROM hz_party_sites hps,
hz_locations hzl,
fnd_territories_vl fvl
WHERE hps.party_id = l_party_id
AND hzl.location_id = hps.location_id
AND hps.status = 'A'
AND fvl.territory_code = hzl.country
AND hps.party_site_name =
bank_account_dtls_rec.party_site_name;
SELECT vendor_site_id,
party_site_id
INTO l_vendor_site_id,
l_party_site_id
FROM ap_supplier_sites_all site,
ap_suppliers vendor
WHERE vendor.vendor_id = site.vendor_id
AND (site.inactive_date > SYSDATE OR site.inactive_date IS NULL)
AND vendor.party_id = l_party_id
AND site.vendor_site_code =
bank_account_dtls_rec.vendor_site_code
AND site.org_id = bank_account_dtls_rec.org_id;
UPDATE pos_bank_account_det_int
SET interface_status = 'REJECTED'
WHERE bank_account_interface_id =
bank_account_dtls_rec.bank_account_interface_id
AND batch_id = p_batch_id;
IF (insert_rejections(p_batch_id,
l_request_id,
'POS_BANK_ACCOUNT_DET_INT',
bank_account_dtls_rec.bank_account_interface_id,
'AP_INVALID_BANK_ACCT_INFO',
g_user_id,
g_login_id,
'IMPORT_VENDOR_BANK_DTLS') <> TRUE) THEN
IF (g_level_procedure >= g_current_runtime_level) THEN
fnd_msg_pub.count_and_get(p_count => l_msg_count,
p_data => l_msg_data);
' Update bank end date: ' || l_msg_data);
bank_account_dtls_rec.insert_update_flag := nvl(bank_account_dtls_rec.insert_update_flag,
'I');
bank_account_dtls_rec.insert_update_flag := nvl(bank_account_dtls_rec.insert_update_flag,
'U');
' bank_account_dtls_rec.insert_update_flag: ' ||
bank_account_dtls_rec.insert_update_flag ||
' l_new_account_rec.alternate_acct_name : ' ||
l_new_account_rec.alternate_acct_name);
bank_account_dtls_rec.insert_update_flag = 'I') THEN
iby_ext_bankacct_pub.create_ext_bank_acct(p_api_version => 1,
p_init_msg_list => fnd_api.g_true,
p_ext_bank_acct_rec => l_new_account_rec,
p_association_level => l_association_level,
p_supplier_site_id => l_vendor_site_id,
p_party_site_id => l_party_site_id,
p_org_id => bank_account_dtls_rec.org_id,
p_org_type => l_org_type,
x_acct_id => l_account_id,
x_return_status => l_ret_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_response => l_create_account_resp);
bank_account_dtls_rec.insert_update_flag = 'U') THEN
---Bug 11819545
--l_new_account_rec.bank_account_id := l_account_id;
bank_account_dtls_rec.insert_update_flag = 'E') THEN
l_new_account_rec.bank_account_id := l_account_id;
SELECT object_version_number
INTO l_new_account_rec.object_version_number
FROM iby_ext_bank_accounts_v
WHERE branch_party_id = l_branch_id
AND bank_party_id = l_bank_id
AND ext_bank_account_id = l_account_id;
iby_ext_bankacct_pub.update_ext_bank_acct(p_api_version => 1,
p_init_msg_list => fnd_api.g_true,
p_ext_bank_acct_rec => l_new_account_rec,
x_return_status => l_ret_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_response => l_create_account_resp);
bank_account_dtls_rec.insert_update_flag = 'U') THEN
-- Account doesnt exist
l_ret_status := fnd_api.g_ret_sts_error;
', Message: Account to be updated, doesnt exist');
UPDATE pos_bank_account_det_int
SET interface_status = 'REJECTED'
WHERE bank_account_interface_id =
bank_account_dtls_rec.bank_account_interface_id
AND batch_id = p_batch_id;
IF (insert_rejections(p_batch_id,
l_request_id,
'POS_BANK_ACCOUNT_DET_INT',
bank_account_dtls_rec.bank_account_interface_id,
'AP_INVALID_BANK_ACCT_INFO',
g_user_id,
g_login_id,
'IMPORT_VENDOR_BANK_DTLS') <> TRUE) THEN
IF (g_level_procedure >= g_current_runtime_level) THEN
fnd_msg_pub.count_and_get(p_count => l_msg_count,
p_data => l_msg_data);
' Update bank end date: ' || l_msg_data);
UPDATE pos_bank_account_det_int
SET bank_account_id = l_account_id
WHERE bank_account_interface_id =
bank_account_dtls_rec.bank_account_interface_id
AND batch_id = p_batch_id;
UPDATE pos_bank_accnt_owners_int
SET bank_id = l_bank_id,
branch_id = l_branch_id,
account_id = l_account_id
WHERE bank_account_interface_id =
bank_account_dtls_rec.bank_account_interface_id
AND batch_id = p_batch_id;
SELECT intermediary_acct_id,
country_code,
bank_name,
city,
bank_code,
branch_number,
bic,
account_number,
check_digits,
iban,
object_version_number
INTO l_new_intermed_acct1_rec.intermediary_acct_id,
l_new_intermed_acct1_rec.country_code,
l_new_intermed_acct1_rec.bank_name,
l_new_intermed_acct1_rec.city,
l_new_intermed_acct1_rec.bank_code,
l_new_intermed_acct1_rec.branch_number,
l_new_intermed_acct1_rec.bic,
l_new_intermed_acct1_rec.account_number,
l_new_intermed_acct1_rec.check_digits,
l_new_intermed_acct1_rec.iban,
l_new_intermed_acct1_rec.object_version_number
FROM (SELECT intermediary_acct_id intermediary_acct_id,
nvl(l_new_intermed_acct1_rec.country_code,
country_code) country_code,
nvl(l_new_intermed_acct1_rec.bank_name, bank_name) bank_name,
nvl(l_new_intermed_acct1_rec.city, city) city,
nvl(l_new_intermed_acct1_rec.bank_code, bank_code) bank_code,
nvl(l_new_intermed_acct1_rec.branch_number,
branch_number) branch_number,
nvl(l_new_intermed_acct1_rec.bic, bic) bic,
nvl(l_new_intermed_acct1_rec.account_number,
account_number) account_number,
nvl(l_new_intermed_acct1_rec.check_digits,
check_digits) check_digits,
nvl(l_new_intermed_acct1_rec.iban, iban) iban,
object_version_number,
dense_rank() over(ORDER BY intermediary_acct_id) rnk
FROM iby_intermediary_accts
WHERE bank_acct_id = l_account_id)
WHERE rnk = 1;
' Message: Going for intermediary account1 update ');
iby_ext_bankacct_pub.update_intermediary_acct(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_intermed_acct_rec => l_new_intermed_acct1_rec,
x_return_status => l_ret_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_response => l_add_intermed_account1_resp);
UPDATE pos_bank_account_det_int
SET interface_status = 'REJECTED'
WHERE bank_account_interface_id =
bank_account_dtls_rec.bank_account_interface_id
AND batch_id = p_batch_id;
IF (insert_rejections(p_batch_id,
l_request_id,
'POS_BANK_ACCOUNT_DET_INT',
bank_account_dtls_rec.bank_account_interface_id,
'POS_FAILED_INTERMEDIARY_ACCT',
g_user_id,
g_login_id,
'IMPORT_VENDOR_BANK_DTLS') <> TRUE) THEN
IF (g_level_procedure >= g_current_runtime_level) THEN
fnd_msg_pub.count_and_get(p_count => l_msg_count,
p_data => l_msg_data);
' Update bank end date: ' || l_msg_data);
' failed in iby_ext_bankacct_pub.create/update_intermediary_acct 1' ||
' Interface_Id: ' ||
bank_account_dtls_rec.bank_account_interface_id ||
', No. of Messages: ' || l_msg_count ||
', Message: ' || l_msg_data);
SELECT intermediary_acct_id,
country_code,
bank_name,
city,
bank_code,
branch_number,
bic,
account_number,
check_digits,
iban,
object_version_number
INTO l_new_intermed_acct2_rec.intermediary_acct_id,
l_new_intermed_acct2_rec.country_code,
l_new_intermed_acct2_rec.bank_name,
l_new_intermed_acct2_rec.city,
l_new_intermed_acct2_rec.bank_code,
l_new_intermed_acct2_rec.branch_number,
l_new_intermed_acct2_rec.bic,
l_new_intermed_acct2_rec.account_number,
l_new_intermed_acct2_rec.check_digits,
l_new_intermed_acct2_rec.iban,
l_new_intermed_acct2_rec.object_version_number
FROM (SELECT intermediary_acct_id intermediary_acct_id,
nvl(l_new_intermed_acct2_rec.country_code,
country_code) country_code,
nvl(l_new_intermed_acct2_rec.bank_name, bank_name) bank_name,
nvl(l_new_intermed_acct2_rec.city, city) city,
nvl(l_new_intermed_acct2_rec.bank_code, bank_code) bank_code,
nvl(l_new_intermed_acct2_rec.branch_number,
branch_number) branch_number,
nvl(l_new_intermed_acct2_rec.bic, bic) bic,
nvl(l_new_intermed_acct2_rec.account_number,
account_number) account_number,
nvl(l_new_intermed_acct2_rec.check_digits,
check_digits) check_digits,
nvl(l_new_intermed_acct2_rec.iban, iban) iban,
object_version_number,
dense_rank() over(ORDER BY intermediary_acct_id) rnk
FROM iby_intermediary_accts
WHERE bank_acct_id = l_account_id)
WHERE rnk = 2;
' Message: Going for intermediary account2 update ');
iby_ext_bankacct_pub.update_intermediary_acct(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_intermed_acct_rec => l_new_intermed_acct2_rec,
x_return_status => l_ret_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_response => l_add_intermed_account1_resp);
UPDATE pos_bank_account_det_int
SET interface_status = 'REJECTED'
WHERE bank_account_interface_id =
bank_account_dtls_rec.bank_account_interface_id
AND batch_id = p_batch_id;
IF (insert_rejections(p_batch_id,
l_request_id,
'POS_BANK_ACCOUNT_DET_INT',
bank_account_dtls_rec.bank_account_interface_id,
'POS_FAILED_INTERMEDIARY_ACCT2',
g_user_id,
g_login_id,
'IMPORT_VENDOR_BANK_DTLS') <> TRUE) THEN
IF (g_level_procedure >= g_current_runtime_level) THEN
fnd_msg_pub.count_and_get(p_count => l_msg_count,
p_data => l_msg_data);
' Update bank end date: ' || l_msg_data);
' failed in iby_ext_bankacct_pub.create/update_intermediary_acct 2' ||
' Interface_Id: ' ||
bank_account_dtls_rec.bank_account_interface_id ||
', No. of Messages: ' || l_msg_count ||
', Message: ' || l_msg_data);
UPDATE pos_bank_account_det_int
SET interface_status = 'PROCESSED'
WHERE bank_account_interface_id =
bank_account_dtls_rec.bank_account_interface_id
AND batch_id = p_batch_id;
IF (bank_account_dtls_rec.insert_update_flag = 'I') THEN
UPDATE pos_imp_batch_summary
SET total_records_imported = total_records_imported + 1,
total_inserts = total_inserts + 1,
bank_detls_inserted = bank_detls_inserted + 1,
bank_detls_imported = bank_detls_imported + 1
WHERE batch_id = p_batch_id;
UPDATE pos_imp_batch_summary
SET total_records_imported = total_records_imported + 1,
total_updates = total_updates + 1,
bank_detls_updated = bank_detls_updated + 1,
bank_detls_imported = bank_detls_imported + 1
WHERE batch_id = p_batch_id;
SELECT party_id
INTO l_account_owner_id
FROM hz_parties
WHERE party_name = account_owners_rec.account_owner_name
AND rownum = 1;
SELECT ext_bank_account_id
INTO l_account_id
FROM iby_ext_bank_accounts_v
WHERE country_code = account_owners_rec.bank_country_code
AND bank_name = account_owners_rec.bank_name
AND (bank_number = account_owners_rec.bank_number OR
account_owners_rec.bank_number IS NULL)
AND bank_branch_name = account_owners_rec.branch_name
AND (branch_number = account_owners_rec.branch_number OR
account_owners_rec.branch_number IS NULL)
AND bank_account_num_electronic =
account_owners_rec.account_number;
UPDATE pos_bank_accnt_owners_int
SET interface_status = 'REJECTED'
WHERE bank_acct_owner_interface_id =
account_owners_rec.bank_acct_owner_interface_id
AND batch_id = p_batch_id;
IF (insert_rejections(p_batch_id,
l_request_id,
'POS_BANK_ACCNT_OWNERS_INT',
bank_account_dtls_rec.bank_account_interface_id,
'POS_ADD_ACCT_OWNER',
g_user_id,
g_login_id,
'import_vendor_bank_dtls') <> TRUE) THEN
IF (g_level_procedure >= g_current_runtime_level) THEN
fnd_msg_pub.count_and_get(p_count => l_msg_count,
p_data => l_msg_data);
UPDATE pos_bank_accnt_owners_int
SET interface_status = 'REJECTED'
WHERE bank_acct_owner_interface_id =
account_owners_rec.bank_acct_owner_interface_id
AND batch_id = p_batch_id;
IF (insert_rejections(p_batch_id,
l_request_id,
'POS_BANK_ACCNT_OWNERS_INT',
bank_account_dtls_rec.bank_account_interface_id,
'POS_ADD_ACCT_OWNER',
g_user_id,
g_login_id,
'import_vendor_bank_dtls') <> TRUE) THEN
IF (g_level_procedure >= g_current_runtime_level) THEN
fnd_msg_pub.count_and_get(p_count => l_msg_count,
p_data => l_msg_data);
SELECT account_owner_id,
object_version_number
INTO l_joint_account_owner_id,
l_obj_version
FROM iby_account_owners
WHERE ext_bank_account_id = l_account_id
AND account_owner_party_id = l_account_owner_id;
SELECT object_version_number
INTO l_obj_version
FROM iby_account_owners
WHERE ext_bank_account_id = l_account_id
AND account_owner_party_id = l_account_owner_id;
UPDATE pos_bank_accnt_owners_int
SET interface_status = 'REJECTED'
WHERE bank_acct_owner_interface_id =
account_owners_rec.bank_acct_owner_interface_id
AND batch_id = p_batch_id;
IF (insert_rejections(p_batch_id,
l_request_id,
'POS_BANK_ACCNT_OWNERS_INT',
bank_account_dtls_rec.bank_account_interface_id,
'POS_ADD_ACCT_OWNER',
g_user_id,
g_login_id,
'import_vendor_bank_dtls') <> TRUE) THEN
IF (g_level_procedure >= g_current_runtime_level) THEN
fnd_msg_pub.count_and_get(p_count => l_msg_count,
p_data => l_msg_data);
UPDATE pos_bank_accnt_owners_int
SET interface_status = 'REJECTED'
WHERE bank_acct_owner_interface_id =
account_owners_rec.bank_acct_owner_interface_id
AND batch_id = p_batch_id;
IF (insert_rejections(p_batch_id,
l_request_id,
'POS_BANK_ACCNT_OWNERS_INT',
bank_account_dtls_rec.bank_account_interface_id,
'POS_PRIMARY_FLAG_NOTSET',
g_user_id,
g_login_id,
'import_vendor_bank_dtls') <> TRUE) THEN
IF (g_level_procedure >= g_current_runtime_level) THEN
fnd_msg_pub.count_and_get(p_count => l_msg_count,
p_data => l_msg_data);
UPDATE pos_bank_accnt_owners_int
SET interface_status = 'PROCESSED'
WHERE bank_acct_owner_interface_id =
account_owners_rec.bank_acct_owner_interface_id
AND batch_id = p_batch_id;
UPDATE pos_imp_batch_summary
SET total_records_imported = total_records_imported + 1,
total_inserts = total_inserts + 1,
bank_detls_inserted = bank_detls_inserted + 1,
bank_detls_imported = bank_detls_imported + 1
WHERE batch_id = p_batch_id;
UPDATE pos_imp_batch_summary
SET import_status = 'PROCESSING',
main_conc_status = 'PROCESSING',
batch_status = 'PROCESSING',
import_req_id = l_request_id
/*,main_conc_req_id = l_request_id*/
WHERE batch_id = p_batch_id;
UPDATE pos_imp_batch_summary
SET import_status = 'COMPL_ERRORS',
main_conc_status = 'COMPLETED',
batch_status = 'ACTION_REQUIRED'
WHERE batch_id = p_batch_id;
UPDATE hz_imp_batch_summary
SET what_if_flag = decode(p_import_run_option, 'WHAT_IF', 'Y', 'N')
WHERE batch_id = p_batch_id;
UPDATE pos_imp_batch_summary
SET import_status = 'PENDING',
main_conc_status = 'PENDING',
batch_status = 'PROCESSING',
import_req_id = l_request_id
WHERE batch_id = p_batch_id;
UPDATE pos_imp_batch_summary
SET import_status = 'PENDING',
main_conc_status = 'COMPLETED',
batch_status = 'PENDING'
WHERE batch_id = p_batch_id;
UPDATE hz_imp_batch_summary
SET import_status = 'COMPL_ERRORS',
main_conc_status = 'COMPLETED',
batch_status = 'ACTION_REQUIRED'
WHERE batch_id = p_batch_id;
SELECT batch_status,
import_status,
what_if_flag
INTO l_batch_status,
l_import_status,
l_what_if_flag
FROM hz_imp_batch_summary
WHERE batch_id = p_batch_id;
UPDATE pos_imp_batch_summary
SET import_status = 'PENDING',
main_conc_status = 'PENDING',
batch_status = 'PROCESSING',
import_req_id = l_request_id
WHERE batch_id = p_batch_id;
SELECT batch_status,
import_status,
main_conc_status
INTO l_batch_status,
l_import_status,
l_main_conc_status
FROM hz_imp_batch_summary
WHERE batch_id = p_batch_id;
UPDATE hz_imp_batch_summary
SET total_inserts = parties_inserted + addresses_inserted +
addressuses_inserted + contactpoints_inserted +
contacts_inserted + contactroles_inserted +
codeassigns_inserted + relationships_inserted +
creditratings_inserted + finreports_inserted +
finnumbers_inserted,
total_updates = parties_updated + addresses_updated +
addressuses_updated + contactpoints_updated +
contacts_updated + contactroles_updated +
codeassigns_updated + relationships_updated +
creditratings_updated + finreports_updated +
finnumbers_updated
WHERE batch_id = p_batch_id;
UPDATE pos_imp_batch_summary
SET import_status = 'PROCESSING',
main_conc_status = 'PROCESSING',
batch_status = 'PROCESSING'
WHERE batch_id = p_batch_id;
UPDATE pos_imp_batch_summary
SET import_status = 'COMPL_ERRORS',
main_conc_status = 'COMPLETED',
batch_status = 'ACTION_REQUIRED'
WHERE batch_id = p_batch_id;
update_party_id(p_batch_id => p_batch_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
' From update_party_id API.');
errbuf := 'WARNING****** Unexpected error occured in update party id program.';
SELECT COUNT(1)
INTO l_error_count
FROM ap_suppliers_int
WHERE sdh_batch_id = p_batch_id
AND nvl(status, 'ACTIVE') = 'REJECTED'
AND rownum = 1;
update_party_site_id(p_batch_id => p_batch_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
' From update_party_id API.');
errbuf := 'WARNING****** Unexpected error occured in update party id program.';
SELECT COUNT(1)
INTO l_error_count
FROM ap_supplier_sites_int
WHERE sdh_batch_id = p_batch_id
AND nvl(status, 'ACTIVE') = 'REJECTED'
AND rownum = 1;
update_contact_dtls(p_batch_id => p_batch_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
' From update_contact_dtls API.');
errbuf := 'WARNING****** Unexpected error occured in update contact details program.';
SELECT COUNT(1)
INTO l_error_count
FROM ap_sup_site_contact_int
WHERE sdh_batch_id = p_batch_id
AND nvl(status, 'ACTIVE') = 'REJECTED'
AND rownum = 1;
SELECT COUNT(1)
INTO l_error_count
FROM pos_product_service_int
WHERE sdh_batch_id = p_batch_id
AND nvl(status, 'ACTIVE') = 'REJECTED'
AND rownum = 1;
SELECT COUNT(1)
INTO l_error_count
FROM pos_business_class_int
WHERE sdh_batch_id = p_batch_id
AND nvl(status, 'ACTIVE') = 'REJECTED'
AND rownum = 1;
SELECT COUNT(1)
INTO l_error_count
FROM pos_party_tax_profile_int
WHERE batch_id = p_batch_id
AND nvl(status, 'ACTIVE') = 'REJECTED'
AND rownum = 1;
SELECT COUNT(1)
INTO l_error_count
FROM pos_party_tax_reg_int
WHERE batch_id = p_batch_id
AND nvl(status, 'ACTIVE') = 'REJECTED'
AND rownum = 1;
SELECT COUNT(1)
INTO l_error_count
FROM pos_fiscal_class_int
WHERE batch_id = p_batch_id
AND nvl(status, 'ACTIVE') = 'REJECTED'
AND rownum = 1;
SELECT COUNT(1)
INTO l_error_count
FROM pos_bank_account_det_int
WHERE batch_id = p_batch_id
AND nvl(interface_status, 'ACTIVE') = 'REJECTED'
AND rownum = 1;
SELECT COUNT(1)
INTO l_error_count
FROM pos_bank_accnt_owners_int
WHERE batch_id = p_batch_id
AND nvl(interface_status, 'ACTIVE') = 'REJECTED'
AND rownum = 1;
SELECT COUNT(1)
INTO l_error_count
FROM POS_SUPP_PROF_EXT_INTF
WHERE BATCH_ID = P_BATCH_ID
AND nvl(PROCESS_STATUS, 1) = 3;
SELECT COUNT(1)
INTO l_uda_imported_count
FROM POS_SUPP_PROF_EXT_INTF
WHERE BATCH_ID = P_BATCH_ID
AND NVL(PROCESS_STATUS, 1) = 4;
UPDATE pos_imp_batch_summary
SET import_status = 'COMPLETED',
main_conc_status = 'COMPLETED',
batch_status = 'COMPLETED',
total_records_imported = total_records_imported + l_uda_imported_count
WHERE batch_id = p_batch_id;
UPDATE pos_imp_batch_summary
SET import_status = 'COMPL_ERRORS',
main_conc_status = 'COMPLETED',
batch_status = 'ACTION_REQUIRED',
total_records_imported = total_records_imported + l_uda_imported_count
WHERE batch_id = p_batch_id;
UPDATE pos_imp_batch_summary
SET import_status = 'PENDING',
main_conc_status = 'PENDING',
batch_status = 'PROCESSING',
total_records_imported = total_records_imported + l_uda_imported_count
WHERE batch_id = p_batch_id;
UPDATE pos_imp_batch_summary
SET import_status = 'PENDING',
main_conc_status = 'PENDING',
batch_status = 'PENDING',
total_records_imported = total_records_imported + l_uda_imported_count
WHERE batch_id = p_batch_id;
SELECT COUNT(1)
INTO l_error_count
FROM POS_SUPP_PROF_EXT_INTF
WHERE BATCH_ID = P_BATCH_ID
AND nvl(PROCESS_STATUS, 1) = 3;
SELECT COUNT(1)
INTO l_uda_imported_count
FROM POS_SUPP_PROF_EXT_INTF
WHERE BATCH_ID = P_BATCH_ID
AND NVL(PROCESS_STATUS, 1) = 4;