[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT --classification_id,
--vendor_id,
lookup_type,
lookup_code,
start_date_active,
end_date_active,
status,
ext_attr_1,
expiration_date,
certificate_number,
certifying_agency,
class_status,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5
INTO --x_buss_class_rec.classification_id,
--x_buss_class_rec.vendor_id,
x_buss_class_rec.lookup_type,
x_buss_class_rec.lookup_code,
x_buss_class_rec.start_date_active,
x_buss_class_rec.end_date_active,
x_buss_class_rec.status,
x_buss_class_rec.ext_attr_1,
x_buss_class_rec.expiration_date,
x_buss_class_rec.certificate_number,
x_buss_class_rec.certifying_agency,
x_buss_class_rec.class_status,
x_buss_class_rec.attribute1,
x_buss_class_rec.attribute2,
x_buss_class_rec.attribute3,
x_buss_class_rec.attribute4,
x_buss_class_rec.attribute5
FROM pos_bus_class_attr
WHERE party_id = p_party_id
AND classification_id = p_classification_id;
UPDATE pos_bus_class_attr
SET status = 'M',
last_update_date = hz_utility_pub.last_update_date,
last_updated_by = hz_utility_pub.user_id,
last_update_login = hz_utility_pub.last_update_login
WHERE party_id = p_from_fk_id
AND classification_id = p_from_id;
SELECT vendor_id
INTO l_to_vendor_id
FROM ap_suppliers
WHERE party_id = p_to_fk_id;
DELETE FROM pos_supplier_mappings a
WHERE a.party_id = p_to_fk_id
AND a.vendor_id <> l_to_vendor_id
AND EXISTS (SELECT 1
FROM pos_supplier_mappings b
WHERE b.party_id = p_to_fk_id
AND vendor_id = l_to_vendor_id);
UPDATE pos_supplier_mappings
SET vendor_id = l_to_vendor_id,
last_updated_by = fnd_global.user_id,
last_update_date = SYSDATE,
last_update_login = fnd_global.login_id
WHERE mapping_id IN (SELECT mapping_id
FROM pos_supplier_mappings
WHERE party_id = p_to_fk_id);
SELECT COUNT(1)
INTO l_count
FROM pos_bus_class_attr attr_from,
pos_bus_class_attr attr_to
WHERE attr_from.lookup_code = attr_to.lookup_code
AND attr_from.lookup_type = attr_to.lookup_type
/*AND nvl(attr_from.ext_attr_1, ' ') = nvl(attr_to.ext_attr_1, ' ')*/
AND attr_from.party_id = p_from_fk_id
AND attr_to.party_id = p_to_fk_id
AND attr_from.classification_id = p_from_id;
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 rtrim(nvl2(p_vendor_prodsrv_rec.segment1,
p_vendor_prodsrv_rec.segment1 || '.',
p_vendor_prodsrv_rec.segment1) ||
nvl2(p_vendor_prodsrv_rec.segment2,
p_vendor_prodsrv_rec.segment2 || '.',
p_vendor_prodsrv_rec.segment2) ||
nvl2(p_vendor_prodsrv_rec.segment3,
p_vendor_prodsrv_rec.segment3 || '.',
p_vendor_prodsrv_rec.segment3) ||
nvl2(p_vendor_prodsrv_rec.segment4,
p_vendor_prodsrv_rec.segment4 || '.',
p_vendor_prodsrv_rec.segment4) ||
nvl2(p_vendor_prodsrv_rec.segment5,
p_vendor_prodsrv_rec.segment5 || '.',
p_vendor_prodsrv_rec.segment5) ||
nvl2(p_vendor_prodsrv_rec.segment6,
p_vendor_prodsrv_rec.segment6 || '.',
p_vendor_prodsrv_rec.segment6) ||
nvl2(p_vendor_prodsrv_rec.segment7,
p_vendor_prodsrv_rec.segment7 || '.',
p_vendor_prodsrv_rec.segment7) ||
nvl2(p_vendor_prodsrv_rec.segment8,
p_vendor_prodsrv_rec.segment8 || '.',
p_vendor_prodsrv_rec.segment8) ||
nvl2(p_vendor_prodsrv_rec.segment9,
p_vendor_prodsrv_rec.segment9 || '.',
p_vendor_prodsrv_rec.segment9) ||
nvl2(p_vendor_prodsrv_rec.segment10,
p_vendor_prodsrv_rec.segment10 || '.',
p_vendor_prodsrv_rec.segment10) ||
nvl2(p_vendor_prodsrv_rec.segment11,
p_vendor_prodsrv_rec.segment11 || '.',
p_vendor_prodsrv_rec.segment11) ||
nvl2(p_vendor_prodsrv_rec.segment12,
p_vendor_prodsrv_rec.segment12 || '.',
p_vendor_prodsrv_rec.segment12) ||
nvl2(p_vendor_prodsrv_rec.segment13,
p_vendor_prodsrv_rec.segment13 || '.',
p_vendor_prodsrv_rec.segment13) ||
nvl2(p_vendor_prodsrv_rec.segment14,
p_vendor_prodsrv_rec.segment14 || '.',
p_vendor_prodsrv_rec.segment14) ||
nvl2(p_vendor_prodsrv_rec.segment15,
p_vendor_prodsrv_rec.segment15 || '.',
p_vendor_prodsrv_rec.segment15) ||
nvl2(p_vendor_prodsrv_rec.segment16,
p_vendor_prodsrv_rec.segment16 || '.',
p_vendor_prodsrv_rec.segment16) ||
nvl2(p_vendor_prodsrv_rec.segment17,
p_vendor_prodsrv_rec.segment17 || '.',
p_vendor_prodsrv_rec.segment17) ||
nvl2(p_vendor_prodsrv_rec.segment18,
p_vendor_prodsrv_rec.segment18 || '.',
p_vendor_prodsrv_rec.segment18) ||
nvl2(p_vendor_prodsrv_rec.segment19,
p_vendor_prodsrv_rec.segment19 || '.',
p_vendor_prodsrv_rec.segment19) ||
nvl2(p_vendor_prodsrv_rec.segment20,
p_vendor_prodsrv_rec.segment20 || '.',
p_vendor_prodsrv_rec.segment20),
'.')
INTO l_segment_concat
FROM dual;
SELECT --classification_id,
p_to_vendor_id,
segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
segment14,
segment15,
segment16,
segment17,
segment18,
segment19,
segment20,
status,
segment_definition
INTO --x_prod_services_rec . classification_id,
x_prod_services_rec.vendor_id,
x_prod_services_rec.segment1,
x_prod_services_rec.segment2,
x_prod_services_rec.segment3,
x_prod_services_rec.segment4,
x_prod_services_rec.segment5,
x_prod_services_rec.segment6,
x_prod_services_rec.segment7,
x_prod_services_rec.segment8,
x_prod_services_rec.segment9,
x_prod_services_rec.segment10,
x_prod_services_rec.segment11,
x_prod_services_rec.segment12,
x_prod_services_rec.segment13,
x_prod_services_rec.segment14,
x_prod_services_rec.segment15,
x_prod_services_rec.segment16,
x_prod_services_rec.segment17,
x_prod_services_rec.segment18,
x_prod_services_rec.segment19,
x_prod_services_rec.segment20,
x_prod_services_rec.status,
x_prod_services_rec.segment_definition
FROM pos_sup_products_services
WHERE vendor_id = p_from_vendor_id
AND classification_id = p_classification_id
/* Picking the products and services that are not already
associated with the To Party*/
AND (segment1, segment2, segment3, segment4, segment5, segment6,
segment7, segment8, segment9, segment10, segment11, segment12,
segment13, segment14, segment15, segment16, segment17,
segment18, segment19, segment20, segment_definition) NOT IN
(SELECT segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
segment14,
segment15,
segment16,
segment17,
segment18,
segment19,
segment20,
segment_definition
FROM pos_sup_products_services
WHERE vendor_id = p_to_vendor_id);
SELECT vendor_id
INTO l_from_vendor_id
FROM ap_suppliers
WHERE party_id = p_from_fk_id;
SELECT vendor_id
INTO l_to_vendor_id
FROM ap_suppliers
WHERE party_id = p_to_fk_id;
UPDATE pos_sup_products_services
SET status = 'M',
last_update_date = SYSDATE,
last_updated_by = hz_utility_pub.user_id,
last_update_login = hz_utility_pub.last_update_login
WHERE vendor_id = l_from_vendor_id
AND classification_id = p_from_id;
DELETE FROM pos_supplier_mappings a
WHERE a.party_id = p_to_fk_id
AND a.vendor_id <> l_to_vendor_id
AND EXISTS (SELECT 1
FROM pos_supplier_mappings b
WHERE b.party_id = p_to_fk_id
AND vendor_id = l_to_vendor_id);
UPDATE pos_supplier_mappings
SET vendor_id = l_to_vendor_id,
last_updated_by = fnd_global.user_id,
last_update_date = SYSDATE,
last_update_login = fnd_global.login_id
WHERE mapping_id IN (SELECT mapping_id
FROM pos_supplier_mappings
WHERE party_id = p_to_fk_id);
SELECT paymentinstrumentuseseo.instrument_type,
paymentinstrumentuseseo.instrument_id,
paymentinstrumentuseseo.payment_function
FROM iby_pmt_instr_uses_all paymentinstrumentuseseo
--iby_ext_bank_accounts_v ibyextbankaccts
WHERE --paymentinstrumentuseseo.instrument_id = ibyextbankaccts.bank_account_id AND
paymentinstrumentuseseo.instrument_type = 'BANKACCOUNT'
--Enforce the IBY_PMT_INSTR_USES_ALL_N1 index PAYMENT_FLOW,EXT_PMT_PARTY_ID
AND paymentinstrumentuseseo.payment_flow IN
(SELECT lookup_code FROM fnd_lookup_values
WHERE lookup_type = 'IBY_PAYMENT_FLOW'
AND language = userenv('LANG'))
AND ext_pmt_party_id IN
(SELECT ext_payee_id
FROM iby_external_payees_all
WHERE payee_party_id = p_from_party
AND org_id IS NULL
AND party_site_id IS NULL
AND supplier_site_id IS NULL)
AND /* Excluding the accounts that are already associated*/
paymentinstrumentuseseo.instrument_id NOT IN
(SELECT instrument_id
FROM iby_external_payees_all extpayee,
iby_pmt_instr_uses_all instr
WHERE extpayee.payee_party_id = p_to_party
AND extpayee.org_id IS NULL
AND extpayee.party_site_id IS NULL
AND extpayee.supplier_site_id IS NULL
AND extpayee.ext_payee_id = ext_pmt_party_id
AND instr.instrument_type = 'BANKACCOUNT');
SELECT *
FROM pos_supp_prof_ext_b
WHERE attr_group_id = p_attribute_group_id
AND data_level_id = p_data_level_id
AND party_id IN ( -- Only from parties
SELECT from_party_id
FROM hz_merge_parties
WHERE batch_id = p_batch_id
--AND merge_status = 'DONE'
AND merge_type <> 'SAME_PARTY_MERGE'
AND to_party_id = p_to_party_id)
ORDER BY last_update_date;
SELECT *
FROM (SELECT *
FROM pos_supp_prof_ext_b
WHERE attr_group_id = p_attribute_group_id
AND data_level_id = p_data_level_id
AND party_id IN
(SELECT from_party_id
FROM hz_merge_parties
WHERE batch_id = p_batch_id
AND merge_type <> 'SAME_PARTY_MERGE'
AND to_party_id = p_to_party_id)
ORDER BY last_update_date)
UNION ALL (SELECT *
FROM pos_supp_prof_ext_b
WHERE attr_group_id = p_attribute_group_id
AND data_level_id = p_data_level_id
AND party_id = p_to_party_id);
SELECT *
FROM pos_supp_prof_ext_b
WHERE attr_group_id = p_attribute_group_id
AND data_level_id = p_data_level_id
AND (party_id, pk1_value) IN
(SELECT fromparty.party_id,
merge_from_entity_id
FROM hz_merge_party_details site,
hz_party_sites fromparty,
hz_party_sites toparty,
hz_merge_parties batch
WHERE fromparty.party_site_id = merge_from_entity_id
AND toparty.party_site_id = merge_to_entity_id
AND batch.batch_id = p_batch_id
AND batch.batch_party_id = site.batch_party_id
AND merge_to_entity_id IN
(SELECT merge_to_entity_id
FROM hz_merge_party_details site2
WHERE site2.batch_party_id = site.batch_party_id
AND merge_from_entity_id = p_from_party_id))
ORDER BY last_update_date;
SELECT *
FROM (SELECT *
FROM pos_supp_prof_ext_b
WHERE attr_group_id = p_attribute_group_id
AND data_level_id = p_data_level_id
AND (party_id, pk1_value) IN
(SELECT fromparty.party_id,
merge_from_entity_id
FROM hz_merge_party_details site,
hz_party_sites fromparty,
hz_party_sites toparty,
hz_merge_parties batch
WHERE fromparty.party_site_id = merge_from_entity_id
AND toparty.party_site_id = merge_to_entity_id
AND batch.batch_id = p_batch_id
AND batch.batch_party_id = site.batch_party_id
AND merge_to_entity_id IN
(SELECT merge_to_entity_id
FROM hz_merge_party_details site2
WHERE site2.batch_party_id =
site.batch_party_id
AND merge_from_entity_id = p_from_party_id))
ORDER BY last_update_date)
UNION ALL
SELECT *
FROM pos_supp_prof_ext_b
WHERE attr_group_id = p_attribute_group_id
AND data_level_id = p_data_level_id
AND pk1_value = p_to_party_id;
SELECT *
FROM pos_supp_prof_ext_b
WHERE attr_group_id = p_attribute_group_id
AND data_level_id = p_data_level_id
AND pk2_value = p_from_party_id
ORDER BY last_update_date;
SELECT *
FROM pos_supp_prof_ext_b
WHERE attr_group_id = p_attribute_group_id
AND data_level_id = p_data_level_id
AND pk2_value = p_from_party_id
UNION ALL
SELECT *
FROM pos_supp_prof_ext_b
WHERE attr_group_id = p_attribute_group_id
AND data_level_id = p_data_level_id
AND pk2_value = p_to_party_id;
SELECT attr_name,
database_column
FROM ego_attrs_v
WHERE application_id = 177
AND attr_group_name = p_attribute_group_name
AND attr_group_type = p_attribute_group_type;
SELECT database_column
FROM ego_attrs_v
WHERE application_id = 177
AND attr_group_name = p_attribute_group_name
AND attr_group_type = p_attribute_group_type
AND unique_key_flag = 'Y';
SELECT party_id
INTO l_party_id
FROM hz_party_sites
WHERE party_site_id = p_to_party_id;
SELECT party_id
INTO l_party_id
FROM ap_suppliers
WHERE vendor_id = p_vendor_id;
SELECT party_site_id
INTO l_party_site_id
FROM ap_supplier_sites_all
WHERE vendor_site_id = p_to_party_id;
l_sql := 'SELECT pos1.extension_id to_ext_id
FROM pos_supp_prof_ext_b pos1,
pos_supp_prof_ext_b pos2
WHERE pos1.attr_group_id = ' || p_attribute_group_id ||
' AND pos1.data_level_id = ' || p_data_level_id ||
' AND pos1.attr_group_id = pos2.attr_group_id
AND pos1.data_level_id = pos2.data_level_id
AND pos2.party_id = ' || p_uda_rec_tbl(datacntr)
.party_id || ' AND pos2.extension_id = ' || p_uda_rec_tbl(datacntr)
.extension_id || ' AND pos1.party_id = ' ||
p_to_party_id || where_clause;
l_sql := 'SELECT pos1.extension_id to_ext_id
FROM pos_supp_prof_ext_b pos1,
pos_supp_prof_ext_b pos2
WHERE pos1.attr_group_id = ' || p_attribute_group_id ||
' AND pos1.data_level_id = ' || p_data_level_id ||
' AND pos1.attr_group_id = pos2.attr_group_id
AND pos1.data_level_id = pos2.data_level_id
AND pos2.party_id = ' || p_uda_rec_tbl(datacntr)
.party_id || ' AND pos2.extension_id = ' || p_uda_rec_tbl(datacntr)
.extension_id || ' AND pos2.pk1_value = ' || p_uda_rec_tbl(datacntr)
.pk1_value || ' AND pos1.pk1_value = ' || p_to_party_id ||
where_clause;
l_sql := 'SELECT pos1.extension_id to_ext_id
FROM pos_supp_prof_ext_b pos1,
pos_supp_prof_ext_b pos2
WHERE pos1.attr_group_id = ' || p_attribute_group_id ||
' AND pos1.data_level_id = ' || p_data_level_id ||
' AND pos1.attr_group_id = pos2.attr_group_id
AND pos1.data_level_id = pos2.data_level_id
AND pos2.party_id = ' || p_uda_rec_tbl(datacntr)
.party_id || ' AND pos2.extension_id = ' || p_uda_rec_tbl(datacntr)
.extension_id || ' AND pos2.pk2_value = ' || p_uda_rec_tbl(datacntr)
.pk2_value || ' AND pos1.pk2_value = ' || p_to_party_id ||
where_clause;
l_sql := 'SELECT *
FROM pos_supp_prof_ext_b
WHERE party_id = ' || p_uda_rec_tbl(datacntr)
.party_id || ' AND extension_id = ' || p_uda_rec_tbl(datacntr)
.extension_id ||
' UNION ALL
SELECT *
FROM pos_supp_prof_ext_b
WHERE party_id = ' ||
p_to_party_id || ' AND extension_id = ' || l_ext1;
l_sql := 'SELECT *
FROM pos_supp_prof_ext_b
WHERE pk1_value = ' || p_uda_rec_tbl(datacntr)
.pk1_value || ' AND extension_id = ' || p_uda_rec_tbl(datacntr)
.extension_id ||
' UNION ALL
SELECT *
FROM pos_supp_prof_ext_b
WHERE pk1_value = ' ||
p_to_party_id || ' AND extension_id = ' || l_ext1;
l_sql := 'SELECT *
FROM pos_supp_prof_ext_b
WHERE pk2_value = ' || p_uda_rec_tbl(datacntr)
.pk2_value || ' AND extension_id = ' || p_uda_rec_tbl(datacntr)
.extension_id ||
' UNION ALL
SELECT *
FROM pos_supp_prof_ext_b
WHERE pk2_value = ' ||
p_to_party_id || ' AND extension_id = ' || l_ext1;
l_sql := 'SELECT *
FROM pos_supp_prof_ext_b
WHERE party_id = ' || p_to_party_id ||
' AND extension_id = ' || l_ext1 ||
'UNION ALL
SELECT *
FROM pos_supp_prof_ext_b
WHERE party_id = ' || p_uda_rec_tbl(datacntr)
.party_id || ' AND extension_id = ' || p_uda_rec_tbl(datacntr)
.extension_id;
l_sql := 'SELECT *
FROM pos_supp_prof_ext_b
WHERE pk1_value = ' ||
p_to_party_id || ' AND extension_id = ' || l_ext1 ||
' UNION ALL
SELECT *
FROM pos_supp_prof_ext_b
WHERE pk1_value = ' || p_uda_rec_tbl(datacntr)
.pk1_value || ' AND extension_id = ' || p_uda_rec_tbl(datacntr)
.extension_id;
l_sql := 'SELECT *
FROM pos_supp_prof_ext_b
WHERE pk2_value = ' ||
p_to_party_id || ' AND extension_id = ' || l_ext1 ||
' UNION ALL
SELECT *
FROM pos_supp_prof_ext_b
WHERE pk2_value = ' || p_uda_rec_tbl(datacntr)
.pk2_value || ' AND extension_id = ' || p_uda_rec_tbl(datacntr)
.extension_id;
ego_user_attrs_data_pvt.g_update_mode --TRANSACTION_TYPE
);
ego_user_attrs_data_pvt.g_update_mode --TRANSACTION_TYPE
);
ego_user_attrs_data_pvt.g_update_mode --TRANSACTION_TYPE
);
/* This API works in 2 modes Insert and Update. If run in the update mode then
it would update the UDA data for the to_party_id that is passed.
If run in the insert mode, it would insert the data for the party */
/* Build the Primary key Name value pairs */
IF (p_data_level_id = 17701) THEN
l_pk_column_values := ego_col_name_value_pair_array(ego_col_name_value_pair_obj('PARTY_ID',
to_char(p_to_party_id)));
SELECT party_id
INTO l_party_id
FROM hz_party_sites
WHERE party_site_id = p_to_party_id;
SELECT party_id
INTO l_party_id
FROM ap_suppliers
WHERE vendor_id = p_vendor_id;
SELECT party_site_id
INTO l_party_site_id
FROM ap_supplier_sites_all
WHERE vendor_site_id = p_to_party_id;
IF (p_mode = 'UPDATE') THEN
/* Build the Row Object */
IF (p_data_level_id = 17701) THEN
l_attributes_row_table(l_attributes_row_table.last) := ego_user_attr_row_obj(l_row_identifier,
p_attribute_group_id,
177,
'POS_SUPP_PROFMGMT_GROUP', --'SDH_SUPP_PROFMGMT_GROUP',
p_attribute_group_name, --p_attribute_group_name,
'SUPP_LEVEL', -- data level
'N',
NULL,
NULL,
NULL,
NULL,
ego_user_attrs_data_pvt.g_update_mode --TRANSACTION_TYPE
);
ego_user_attrs_data_pvt.g_update_mode --TRANSACTION_TYPE
);
ego_user_attrs_data_pvt.g_update_mode --TRANSACTION_TYPE
);
SELECT ag.attr_group_id,
ag.application_id,
eas.data_level_id,
ag.multi_row,
eas.attr_group_type,
ag.descriptive_flex_context_code,
ag.descriptive_flexfield_name,
eas.classification_code
FROM ego_fnd_dsc_flx_ctx_ext ag,
ego_obj_attr_grp_assocs_v eas
WHERE ag.application_id = 177
AND ag.attr_group_id = eas.attr_group_id
AND eas.application_id = ag.application_id
AND eas.data_level_int_name = 'SUPP_LEVEL';
SELECT COUNT(1)
INTO l_to_party_cntr
FROM pos_supplier_uda_merge_gtt
WHERE batch_id = p_batch_id
AND to_party_id = p_to_fk_id
AND rownum = 1;
INSERT INTO pos_supplier_uda_merge_gtt
(batch_id, to_party_id)
VALUES
(p_batch_id, p_to_fk_id);
/* Update the status of all the records in the bath to
PERFORM_UDA_MERGE*/
/* Get the attribute group data from the cursor
get_supplier_attribute_groups*/
OPEN get_supplier_attribute_groups;
SELECT COUNT(1)
INTO l_party_count
FROM pos_supp_prof_ext_b
WHERE attr_group_id = attributes_group_coll(cntr).attr_group_id
AND party_id = p_to_fk_id
AND rownum = 1;
/* Do the insert */
do_uda_row_dml(p_attribute_group_id => attributes_group_coll(cntr)
.attr_group_id,
p_data_level_id => attributes_group_coll(cntr)
.data_level_id,
p_attribute_group_type => attributes_group_coll(cntr)
.attr_group_type,
p_attribute_group_name => attributes_group_coll(cntr)
.descriptive_flex_context_code,
p_to_party_id => p_to_fk_id,
p_mode => 'INSERT',
p_uda_rec => merged_uda_rec,
p_class_code => attributes_group_coll(cntr)
.classification_code,
x_return_status => l_ret_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
/* Do the update */
do_uda_row_dml(p_attribute_group_id => attributes_group_coll(cntr)
.attr_group_id,
p_data_level_id => attributes_group_coll(cntr)
.data_level_id,
p_attribute_group_type => attributes_group_coll(cntr)
.attr_group_type,
p_attribute_group_name => attributes_group_coll(cntr)
.descriptive_flex_context_code,
p_to_party_id => p_to_fk_id,
p_mode => 'UPDATE',
p_uda_rec => merged_uda_rec,
p_class_code => attributes_group_coll(cntr)
.classification_code,
x_return_status => l_ret_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
/* Insert the rows obtained */
do_uda_row_dml(p_attribute_group_id => attributes_group_coll(cntr)
.attr_group_id,
p_data_level_id => attributes_group_coll(cntr)
.data_level_id,
p_attribute_group_type => attributes_group_coll(cntr)
.attr_group_type,
p_attribute_group_name => attributes_group_coll(cntr)
.descriptive_flex_context_code,
p_to_party_id => p_to_fk_id,
p_mode => 'INSERT',
p_uda_rec_tbl => l_uda_rec_tbl,
p_class_code => attributes_group_coll(cntr)
.classification_code,
x_return_status => l_ret_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
SELECT ag.attr_group_id,
ag.application_id,
eas.data_level_id,
ag.multi_row,
eas.attr_group_type,
ag.descriptive_flex_context_code,
ag.descriptive_flexfield_name,
eas.classification_code
FROM ego_fnd_dsc_flx_ctx_ext ag,
ego_obj_attr_grp_assocs_v eas
WHERE ag.application_id = 177
AND ag.attr_group_id = eas.attr_group_id
AND eas.application_id = ag.application_id
AND eas.data_level_int_name = 'SUPP_ADDR_SITE_LEVEL';
/* Update the status of all the records in the bath to
PERFORM_UDA_MERGE*/
/* Get the attribute group data from the cursor
get_supplier_attribute_groups*/
OPEN get_supplier_attribute_groups;
SELECT COUNT(1)
INTO l_party_count
FROM pos_supp_prof_ext_b
WHERE attr_group_id = attributes_group_coll(cntr).attr_group_id
AND pk2_value = p_to_fk_id
AND rownum = 1;
/* Do the insert */
do_uda_row_dml(p_attribute_group_id => attributes_group_coll(cntr)
.attr_group_id,
p_data_level_id => attributes_group_coll(cntr)
.data_level_id,
p_attribute_group_type => attributes_group_coll(cntr)
.attr_group_type,
p_attribute_group_name => attributes_group_coll(cntr)
.descriptive_flex_context_code,
p_to_party_id => p_to_fk_id,
p_mode => 'INSERT',
p_uda_rec => merged_uda_rec,
p_class_code => attributes_group_coll(cntr)
.classification_code,
p_vendor_id => p_from_id,
x_return_status => l_ret_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
/* Do the update */
do_uda_row_dml(p_attribute_group_id => attributes_group_coll(cntr)
.attr_group_id,
p_data_level_id => attributes_group_coll(cntr)
.data_level_id,
p_attribute_group_type => attributes_group_coll(cntr)
.attr_group_type,
p_attribute_group_name => attributes_group_coll(cntr)
.descriptive_flex_context_code,
p_to_party_id => p_to_fk_id,
p_mode => 'UPDATE',
p_uda_rec => merged_uda_rec,
p_class_code => attributes_group_coll(cntr)
.classification_code,
p_vendor_id => p_from_id,
x_return_status => l_ret_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
/* Insert the rows obtained */
do_uda_row_dml(p_attribute_group_id => attributes_group_coll(cntr)
.attr_group_id,
p_data_level_id => attributes_group_coll(cntr)
.data_level_id,
p_attribute_group_type => attributes_group_coll(cntr)
.attr_group_type,
p_attribute_group_name => attributes_group_coll(cntr)
.descriptive_flex_context_code,
p_to_party_id => p_to_fk_id,
p_mode => 'INSERT',
p_uda_rec_tbl => l_uda_rec_tbl,
p_class_code => attributes_group_coll(cntr)
.classification_code,
p_vendor_id => p_from_id,
x_return_status => l_ret_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
SELECT ag.attr_group_id,
ag.application_id,
eas.data_level_id,
ag.multi_row,
eas.attr_group_type,
ag.descriptive_flex_context_code,
ag.descriptive_flexfield_name,
eas.classification_code,
eas.data_level_int_name
FROM ego_fnd_dsc_flx_ctx_ext ag,
ego_obj_attr_grp_assocs_v eas
WHERE ag.application_id = 177
AND ag.attr_group_id = eas.attr_group_id
AND eas.application_id = ag.application_id
AND eas.data_level_int_name = 'SUPP_ADDR_LEVEL';
SELECT COUNT(1)
INTO l_to_party_cntr
FROM pos_supplier_uda_merge_gtt
WHERE batch_id = p_batch_id
AND to_party_id = p_to_fk_id
AND rownum = 1;
INSERT INTO pos_supplier_uda_merge_gtt
(batch_id, to_party_id)
VALUES
(p_batch_id, p_to_fk_id);
/* Update the status of all the records in the bath to
PERFORM_UDA_MERGE*/
/* Get the attribute group data from the cursor
get_supplier_attribute_groups*/
OPEN get_supplier_attribute_groups;
SELECT COUNT(1)
INTO l_party_count
FROM pos_supp_prof_ext_b
WHERE attr_group_id = attributes_group_coll(cntr).attr_group_id
AND pk1_value = p_to_fk_id
AND rownum = 1;
/* Do the insert */
do_uda_row_dml(p_attribute_group_id => attributes_group_coll(cntr)
.attr_group_id,
p_data_level_id => attributes_group_coll(cntr)
.data_level_id,
p_attribute_group_type => attributes_group_coll(cntr)
.attr_group_type,
p_attribute_group_name => attributes_group_coll(cntr)
.descriptive_flex_context_code,
p_to_party_id => p_to_fk_id,
p_mode => 'INSERT',
p_uda_rec => merged_uda_rec,
p_class_code => attributes_group_coll(cntr)
.classification_code,
x_return_status => l_ret_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
/* Do the update */
do_uda_row_dml(p_attribute_group_id => attributes_group_coll(cntr)
.attr_group_id,
p_data_level_id => attributes_group_coll(cntr)
.data_level_id,
p_attribute_group_type => attributes_group_coll(cntr)
.attr_group_type,
p_attribute_group_name => attributes_group_coll(cntr)
.descriptive_flex_context_code,
p_to_party_id => p_to_fk_id,
p_mode => 'UPDATE',
p_uda_rec => merged_uda_rec,
p_class_code => attributes_group_coll(cntr)
.classification_code,
x_return_status => l_ret_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
/* Insert the rows obtained */
do_uda_row_dml(p_attribute_group_id => attributes_group_coll(cntr)
.attr_group_id,
p_data_level_id => attributes_group_coll(cntr)
.data_level_id,
p_attribute_group_type => attributes_group_coll(cntr)
.attr_group_type,
p_attribute_group_name => attributes_group_coll(cntr)
.descriptive_flex_context_code,
p_to_party_id => p_to_fk_id,
p_mode => 'INSERT',
p_uda_rec_tbl => l_uda_rec_tbl,
p_class_code => attributes_group_coll(cntr)
.classification_code,
x_return_status => l_ret_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
UPDATE ap_suppliers
SET end_date_active = SYSDATE
WHERE party_id = p_from_fk_id;
SELECT COUNT(vendor_id)
INTO l_to_vendor_id
FROM ap_suppliers
WHERE party_id = p_to_fk_id;
SELECT orig_system_reference
INTO l_party_orig_system_ref
FROM hz_orig_sys_references hr
WHERE hr.owner_table_name = 'HZ_PARTIES'
AND owner_table_id = p_to_fk_id
AND party_id = p_to_fk_id
AND hr.status = 'A'
AND (hr.reason_code <> 'MERGED' OR hr.reason_code IS NULL)
AND nvl(hr.end_date_active, SYSDATE) >= SYSDATE;