[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Insert_Data (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_table_name IN VARCHAR2,
p_prim_key_name IN VARCHAR2,
p_seq_name IN VARCHAR2,
p_str_col_names IN char_data_set_type_w,
p_num_col_names IN char_data_set_type_w,
p_str_data IN char_data_set_type_w,
p_num_data IN num_data_set_type_w,
p_obj_version_num IN NUMBER := NULL, -- if NULL, do not insert this column
p_last_update_date_flag IN VARCHAR2 := FND_API.G_TRUE,-- need this column?
p_last_update_by_flag IN VARCHAR2 := FND_API.G_TRUE,-- need this column?
p_creation_date_flag IN VARCHAR2 := FND_API.G_TRUE,-- need this column?
p_created_by_flag IN VARCHAR2 := FND_API.G_TRUE,-- need this column?
p_ls_update_log_flag IN VARCHAR2 := FND_API.G_TRUE,-- need this column?
p_col_names IN char_data_set_type_w,
p_data IN char_data_set_type_w,
p_error_rows IN num_data_set_type_w,
p_row_count IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
);
PROCEDURE Mark_Insert_Lead_Errors (
p_import_list_header_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2
)
IS
L_API_NAME CONSTANT VARCHAR2(30) := 'Mark_Insert_Lead_Errors';
SELECT batch_id
FROM ams_imp_list_headers_all
WHERE import_list_header_id = p_import_list_header_id;
SELECT imp_list_header_number, import_interface_id,
DECODE (load_status,
'SUCCESS', 'SUCCESS',
'DUPLICATE', 'DUPLICATE',
'ERROR') load_status
FROM as_import_interface
WHERE batch_id = p_batch_id
AND load_status IN ('ERROR', 'SUCCESS', 'T-ERROR', 'UNEXP_ERROR', 'DUPLICATE');
SELECT SUBSTR (error_text, 0, L_MAX_ERROR_TXT_LENGTH) error_text
FROM as_lead_import_errors
WHERE -- batch_id = p_batch_id AND
import_interface_id = p_imp_interface_id
ORDER BY LEAD_IMPORT_ERROR_ID DESC;
UPDATE ams_imp_source_lines
SET load_status = import_interface_data_rec.load_status
WHERE import_source_line_id = import_interface_data_rec.imp_list_header_number;
--need to insert one row to the error table
IF import_interface_data_rec.load_status <> 'SUCCESS' THEN
OPEN c_lead_error_txt (l_batch_id,
import_interface_data_rec.import_interface_id);
END Mark_Insert_Lead_Errors;
l_deleted_flag varchar2_4000_set_type;
l_program_update_date varchar2_4000_set_type;
IS SELECT batch_id
FROM ams_imp_list_headers_all
WHERE import_list_header_id = p_list_header_id;
IS SELECT import_source_line_id, import_list_header_id,source_system,
lead_note, promotion_code,customer_name, sic_code, analysis_fy, customer_category_code,
fiscal_yearend_month, num_of_employees,potential_revenue_curr_fy,
potential_revenue_next_fy,customer_rank,tax_reference,
year_established,addr_do_not_mail_flag,url,cont_do_not_mail_flag,
country,address1,address2,address3,address4,city,postal_code,
state,province,county,email_address,sex_code,salutation,last_name,
title,first_name,job_title,phone_number,phone_status,phone_type,
area_code,extension,middle_initial,job_title_code,mail_stop,
fax_number,fax_area_code,do_not_email_flag,do_not_fax_flag,do_not_phone_flag,
contact_role_code,channel_code,budget_amount,budget_status_code,currency_code,
decision_timeframe_code,close_reason,parent_project,description,vehicle_response_code,
interest_type_id_1,primary_interest_code_id_1,secondary_interest_code_id_1,
inventory_item_id_1,organization_id_1,uom_code_1,quantity_1,budget_amount_1,
source_promotion_id_1,offer_id_1,interest_type_id_2,primary_interest_code_id_2,
secondary_interest_code_id_2,inventory_item_id_2,organization_id_2,
uom_code_2,quantity_2,budget_amount_2,source_promotion_id_2,offer_id_2,interest_type_id_3,
primary_interest_code_id_3,secondary_interest_code_id_3,inventory_item_id_3,
organization_id_3,uom_code_3,quantity_3,budget_amount_3,source_promotion_id_3,
offer_id_3,interest_type_id_4,primary_interest_code_id_4,secondary_interest_code_id_4,
inventory_item_id_4,organization_id_4,uom_code_4,quantity_4,budget_amount_4,
source_promotion_id_4,offer_id_4,interest_type_id_5,primary_interest_code_id_5,
secondary_interest_code_id_5,inventory_item_id_5,organization_id_5,uom_code_5,
quantity_5,budget_amount_5,source_promotion_id_5,offer_id_5,orig_system_reference,
orig_system_code,urgent_flag,accept_flag,assign_date,assign_sales_group_id,
assign_to_person_id,assign_to_salesforce_id,auto_assignment_type,deleted_flag,
import_flag,keep_flag,prm_assignment_type,qualified_flag,reject_reason_code,
scorecard_id,primary_contact_flag,address_effective_date,address_lines_phonetic,
address_style,content_source_type,loc_description,loc_hierarchy_id,fa_location_id,
FLOOR,house_number,LANGUAGE,location_directions,po_box_number,
--location_position,
postal_plus4_code,sales_tax_geocode,sales_tax_inside_city_limits,short_description,
street,street_number,street_suffix,suite,time_zone,loc_validated_flag,duns_number,
group_type,gsa_indicator_flag,hq_branch_ind,jgzz_fiscal_code,known_as,known_as2,
known_as3,known_as4,known_as5,language_name,last_ordered_date,mission_statement,
organization_name_phonetic,party_number,person_first_name_phonetic,person_iden_type,
person_identifier,person_last_name_phonetic,person_name_suffix,person_previous_last_name,
party_reference_use_flag,sic_code_type,tax_name,total_num_of_orders,total_ordered_amount,
parties_validated_flag,ps_uses_comments,primary_per_type,site_use_type,addressee,
mailstop,party_site_name,party_site_number,org_cnt_comments,contact_number,
decision_maker_flag,department,department_code,rank,promotion_id,role_level,
primary_contact_per_role_type,cnt_pnt_content_source_type,phone_country_code,
fax_country_code,phone_calling_calendar,cnt_pnt_time_zone,
raw_phone_number,email_format,fax_extension,org_cnt_title,offer_id,request_id,
program_application_id,program_id,program_update_date,load_error_message,
phone_id,contact_party_id,security_group_id,net_worth,lead_number,prm_sales_lead_type,
prm_exec_sponsor_flag,prm_prj_lead_in_place_flag,incumbent_partner_party_id,
incumbent_partner_resource_id,prm_ind_classification_code,party_type,
party_id,party_site_id,location_id,rel_party_id,sales_lead_id,customer_key,
address_key,contact_key,new_party_flag,new_loc_flag,new_ps_flag,new_rel_flag,
new_con_flag,lead_rank_id, marketing_score, PERSON_INITIALS, LEAD_DATE,
CATEGORY_ID_1, CATEGORY_ID_2, CATEGORY_ID_3, CATEGORY_ID_4,
CATEGORY_ID_5, SALES_METHODOLOGY_ID, DUNS_NUMBER_C,
SOURCE_SECONDARY_REFERENCE,
NOTE_TYPE
FROM ams_lead_mapping_v
WHERE import_list_header_id = p_list_header_id
AND load_status IN ('ACTIVE', 'RELOAD');
l_deleted_flag,
l_import_flag,
l_keep_flag,
l_prm_assignment_type,
l_qualified_flag,
l_reject_reason_code,
l_scorecard_id,
l_primary_contact_flag,
l_address_effective_date,
l_address_lines_phonetic,
l_address_style,
l_content_source_type,
l_loc_description,
l_loc_hierarchy_id,
l_fa_location_id,
l_floor,
l_house_number,
l_language,
l_location_directions,
l_po_box_number,
--l_location_position,
l_postal_plus4_code,
l_sales_tax_geocode,
l_sales_tax_inside_city_limits ,
l_short_description,
l_street,
l_street_number,
l_street_suffix,
l_suite,
l_time_zone,
l_loc_validated_flag,
l_duns_number,
l_group_type,
l_gsa_indicator_flag,
l_hq_branch_ind,
l_jgzz_fiscal_code,
l_known_as,
l_known_as2,
l_known_as3,
l_known_as4,
l_known_as5,
l_language_name,
l_last_ordered_date,
l_mission_statement,
l_organization_name_phonetic,
l_party_number,
l_person_first_name_phonetic,
l_person_iden_type,
l_person_identifier,
l_person_last_name_phonetic,
l_person_name_suffix,
l_person_previous_last_name,
l_party_reference_use_flag,
l_sic_code_type,
l_tax_name,
l_total_num_of_orders,
l_total_ordered_amount,
l_parties_validated_flag,
l_ps_uses_comments,
l_primary_per_type,
l_site_use_type,
l_addressee,
l_mailstop,
l_party_site_name,
l_party_site_number,
l_org_cnt_comments,
l_contact_number,
l_decision_maker_flag,
l_department,
l_department_code,
l_rank,
l_promotion_id,
l_role_level,
primary_contact_per_role_type ,
l_cnt_pnt_content_source_type,
l_phone_country_code,
l_fax_country_code,
l_phone_calling_calendar,
l_cnt_pnt_time_zone,
l_raw_phone_number,
l_email_format,
l_fax_extension,
l_org_cnt_title,
l_offer_id,
l_request_id,
l_program_application_id,
l_program_id,
l_program_update_date,
l_load_error_message,
l_phone_id,
l_contact_party_id,
l_security_group_id,
l_net_worth,
l_lead_number,
l_prm_sales_lead_type,
l_prm_exec_sponsor_flag,
l_prm_prj_lead_in_place_flag,
l_incumbent_partner_party_id,
lincumbent_partner_resource_id ,
l_prm_ind_classification_code,
l_party_type,
l_party_id,
l_party_site_id,
l_location_id,
l_rel_party_id,
l_sales_lead_id,
l_customer_key,
l_address_key,
l_contact_key,
l_new_party_flag,
l_new_loc_flag,
l_new_ps_flag,
l_new_rel_flag,
l_new_con_flag,
l_lead_rank_id,
l_marketing_score,
l_PERSON_INITIALS,
l_LEAD_DATE,
l_CATEGORY_ID_1,
l_CATEGORY_ID_2,
l_CATEGORY_ID_3,
l_CATEGORY_ID_4,
l_CATEGORY_ID_5,
l_SALES_METHODOLOGY_ID,
l_DUNS_NUMBER_C,
l_SOURCE_SECONDARY_REFERENCE,
l_NOTE_TYPE
LIMIT L_MAX_ROW_COUNT;
SELECT as_import_interface_s.NEXTVAL INTO l_interface_id(i)
FROM dual;
INSERT INTO as_import_interface
(import_interface_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
imp_list_header_number,
source_system,
lead_note,
promotion_code,
customer_name,
sic_code,
analysis_fy,
customer_category_code,
fiscal_yearend_month,
num_of_employees,
potential_revenue_curr_fy,
potential_revenue_next_fy,
customer_rank,
tax_reference,
year_established,
addr_do_not_mail_flag,
url,
cont_do_not_mail_flag,
country,
address1,
address2,
address3,
address4,
city,
postal_code,
state,
province,
county,
email_address,
sex_code,
salutation,
last_name,
title,
first_name,
job_title,
phone_number,
phone_status,
phone_type,
area_code,
extension,
middle_initial,
job_title_code,
mail_stop,
fax_number,
fax_area_code,
do_not_email_flag,
do_not_fax_flag,
do_not_phone_flag,
contact_role_code,
channel_code,
budget_amount,
budget_status_code,
currency_code,
decision_timeframe_code,
close_reason,
parent_project,
description,
vehicle_response_code,
interest_type_id_1,
primary_interest_code_id_1,
secondary_interest_code_id_1,
inventory_item_id_1,
organization_id_1,
uom_code_1,
quantity_1,
budget_amount_1,
source_promotion_id_1,
offer_id_1,
interest_type_id_2,
primary_interest_code_id_2,
secondary_interest_code_id_2,
inventory_item_id_2,
organization_id_2,
uom_code_2,
quantity_2,
budget_amount_2,
source_promotion_id_2,
offer_id_2,
interest_type_id_3,
primary_interest_code_id_3,
secondary_interest_code_id_3,
inventory_item_id_3,
organization_id_3,
uom_code_3,
quantity_3,
budget_amount_3,
source_promotion_id_3,
offer_id_3,
interest_type_id_4,
primary_interest_code_id_4,
secondary_interest_code_id_4,
inventory_item_id_4,
organization_id_4,
uom_code_4,
quantity_4,
budget_amount_4,
source_promotion_id_4,
offer_id_4,
interest_type_id_5,
primary_interest_code_id_5,
secondary_interest_code_id_5,
inventory_item_id_5,
organization_id_5,uom_code_5,
quantity_5,
budget_amount_5,
source_promotion_id_5,
offer_id_5,
orig_system_reference,
orig_system_code,
urgent_flag,
accept_flag,
assign_date, -- date
assign_sales_group_id,
assign_to_person_id,
assign_to_salesforce_id,
auto_assignment_type,
deleted_flag,
import_flag,
keep_flag,
prm_assignment_type,
qualified_flag,
reject_reason_code,
scorecard_id,
primary_contact_flag,
address_effective_date, -- date
address_lines_phonetic,
address_style,
content_source_type,
loc_description,
loc_hierarchy_id,
fa_location_id,
FLOOR,
house_number,
LANGUAGE,
location_directions,
po_box_number,
--location_position,
postal_plus4_code,
sales_tax_geocode,
sales_tax_inside_city_limits,
short_description,
street,
street_number,
street_suffix,
suite,
time_zone,
loc_validated_flag,
duns_number,
group_type,
gsa_indicator_flag,
hq_branch_ind,
jgzz_fiscal_code,
known_as,
known_as2,
known_as3,
known_as4,
known_as5,
language_name,
last_ordered_date, -- date
mission_statement,
organization_name_phonetic,
party_number,
person_first_name_phonetic,
person_iden_type,
person_identifier,
person_last_name_phonetic,
person_name_suffix,
person_previous_last_name,
party_reference_use_flag,
sic_code_type,
tax_name,
total_num_of_orders,
total_ordered_amount,
parties_validated_flag,
ps_uses_comments,
primary_per_type,
site_use_type,
addressee,
mailstop,
party_site_name,
party_site_number,
org_cnt_comments,
contact_number,
decision_maker_flag,
department,
department_code,
rank,
promotion_id,
role_level,
primary_contact_per_role_type,
cnt_pnt_content_source_type,
phone_country_code,
fax_country_code,
phone_calling_calendar,
cnt_pnt_time_zone,
raw_phone_number,
email_format,
fax_extension,
org_cnt_title,
offer_id,
request_id,
program_application_id,
program_id,
program_update_date, -- date
load_error_message,
phone_id,
contact_party_id,
security_group_id,
net_worth,
lead_number,
prm_sales_lead_type,
prm_exec_sponsor_flag,
prm_prj_lead_in_place_flag,
incumbent_partner_party_id,
incumbent_partner_resource_id,
prm_ind_classification_code,
party_type,
party_id,
party_site_id,
location_id,
rel_party_id,
sales_lead_id,
customer_key,
address_key,
contact_key,
new_party_flag,
new_loc_flag,
new_ps_flag,
new_rel_flag,
new_con_flag,
lead_rank_id,
marketing_score,
PERSON_INITIALS,
LEAD_DATE,
load_type,
load_date,
load_status,
status_code,
batch_id,
SOURCE_PRIMARY_REFERENCE,
CATEGORY_ID_1,
CATEGORY_ID_2,
CATEGORY_ID_3,
CATEGORY_ID_4,
CATEGORY_ID_5,
SALES_METHODOLOGY_ID,
DUNS_NUMBER_C,
SOURCE_SECONDARY_REFERENCE,
NOTE_TYPE)
VALUES
(l_interface_id(i),
l_current_date,
l_user_id,
l_current_date,
l_user_id,
l_login_id,
l_import_source_line_id(i),
LTRIM(RTRIM(l_source_system(i))),
LTRIM(RTRIM(l_lead_note(i))),
LTRIM(RTRIM(l_promotion_code(i))),
LTRIM(RTRIM(l_customer_name(i))),
LTRIM(RTRIM(l_sic_code(i))),
LTRIM(RTRIM(l_analysis_fy(i))),
LTRIM(RTRIM(l_customer_category_code(i))),
LTRIM(RTRIM(l_fiscal_yearend_month(i))),
LTRIM(RTRIM(l_num_of_employees(i))),
LTRIM(RTRIM(l_potential_revenue_curr_fy(i))),
LTRIM(RTRIM(l_potential_revenue_next_fy(i))),
LTRIM(RTRIM(l_customer_rank(i))),
LTRIM(RTRIM(l_tax_reference(i))),
LTRIM(RTRIM(l_year_established(i))),
LTRIM(RTRIM(l_addr_do_not_mail_flag(i))),
LTRIM(RTRIM(l_url(i))),
LTRIM(RTRIM(l_cont_do_not_mail_flag(i))),
LTRIM(RTRIM(l_country(i))),
LTRIM(RTRIM(l_address1(i))),
LTRIM(RTRIM(l_address2(i))),
LTRIM(RTRIM(l_address3(i))),
LTRIM(RTRIM(l_address4(i))),
LTRIM(RTRIM(l_city(i))),
LTRIM(RTRIM(l_postal_code(i))),
LTRIM(RTRIM(l_state(i))),
LTRIM(RTRIM(l_province(i))),
LTRIM(RTRIM(l_county(i))),
LTRIM(RTRIM(l_email_address(i))),
LTRIM(RTRIM(l_sex_code(i))),
LTRIM(RTRIM(l_salutation(i))),
LTRIM(RTRIM(l_last_name(i))),
LTRIM(RTRIM(l_title(i))),
LTRIM(RTRIM(l_first_name(i))),
LTRIM(RTRIM(l_job_title(i))),
LTRIM(RTRIM(l_phone_number(i))),
LTRIM(RTRIM(l_phone_status(i))),
LTRIM(RTRIM(l_phone_type(i))),
LTRIM(RTRIM(l_area_code(i))),
LTRIM(RTRIM(l_extension(i))),
LTRIM(RTRIM(l_middle_initial(i))),
LTRIM(RTRIM(l_job_title_code(i))),
LTRIM(RTRIM(l_mail_stop(i))),
LTRIM(RTRIM(l_fax_number(i))),
LTRIM(RTRIM(l_fax_area_code(i))),
LTRIM(RTRIM(l_do_not_email_flag(i))),
LTRIM(RTRIM(l_do_not_fax_flag(i))),
LTRIM(RTRIM(l_do_not_phone_flag(i))),
LTRIM(RTRIM(l_contact_role_code(i))),
LTRIM(RTRIM(l_channel_code(i))),
LTRIM(RTRIM(l_budget_amount(i))),
LTRIM(RTRIM(l_budget_status_code(i))),
LTRIM(RTRIM(l_currency_code(i))),
LTRIM(RTRIM(l_decision_timeframe_code(i))),
LTRIM(RTRIM(l_close_reason(i))),
LTRIM(RTRIM(l_parent_project(i))),
LTRIM(RTRIM(l_description(i))),
LTRIM(RTRIM(l_vehicle_response_code(i))),
LTRIM(RTRIM(l_interest_type_id_1(i))),
LTRIM(RTRIM(l_primary_interest_code_id_1(i))),
LTRIM(RTRIM(l_secondary_interest_code_id_1(i))),
LTRIM(RTRIM(l_inventory_item_id_1(i))),
LTRIM(RTRIM(l_organization_id_1(i))),
LTRIM(RTRIM(l_uom_code_1(i))),
LTRIM(RTRIM(l_quantity_1(i))),
LTRIM(RTRIM(l_budget_amount_1(i))),
LTRIM(RTRIM(l_source_promotion_id_1(i))),
LTRIM(RTRIM(l_offer_id_1(i))),
LTRIM(RTRIM(l_interest_type_id_2(i))),
LTRIM(RTRIM(l_primary_interest_code_id_2(i))),
LTRIM(RTRIM(l_secondary_interest_code_id_2(i))),
LTRIM(RTRIM(l_inventory_item_id_2(i))),
LTRIM(RTRIM(l_organization_id_2(i))),
LTRIM(RTRIM(l_uom_code_2(i))),
LTRIM(RTRIM(l_quantity_2(i))),
LTRIM(RTRIM(l_budget_amount_2(i))),
LTRIM(RTRIM(l_source_promotion_id_2(i))),
LTRIM(RTRIM(l_offer_id_2(i))),
LTRIM(RTRIM(l_interest_type_id_3(i))),
LTRIM(RTRIM(l_primary_interest_code_id_3(i))),
LTRIM(RTRIM(l_secondary_interest_code_id_3(i))),
LTRIM(RTRIM(l_inventory_item_id_3(i))),
LTRIM(RTRIM(l_organization_id_3(i))),
LTRIM(RTRIM(l_uom_code_3(i))),
LTRIM(RTRIM(l_quantity_3(i))),
LTRIM(RTRIM(l_budget_amount_3(i))),
LTRIM(RTRIM(l_source_promotion_id_3(i))),
LTRIM(RTRIM(l_offer_id_3(i))),
LTRIM(RTRIM(l_interest_type_id_4(i))),
LTRIM(RTRIM(l_primary_interest_code_id_4(i))),
LTRIM(RTRIM(l_secondary_interest_code_id_4(i))),
LTRIM(RTRIM(l_inventory_item_id_4(i))),
LTRIM(RTRIM(l_organization_id_4(i))),
LTRIM(RTRIM(l_uom_code_4(i))),
LTRIM(RTRIM(l_quantity_4(i))),
LTRIM(RTRIM(l_budget_amount_4(i))),
LTRIM(RTRIM(l_source_promotion_id_4(i))),
LTRIM(RTRIM(l_offer_id_4(i))),
LTRIM(RTRIM(l_interest_type_id_5(i))),
LTRIM(RTRIM(l_primary_interest_code_id_5(i))),
LTRIM(RTRIM(l_secondary_interest_code_id_5(i))),
LTRIM(RTRIM(l_inventory_item_id_5(i))),
LTRIM(RTRIM(l_organization_id_5(i))),
LTRIM(RTRIM(l_uom_code_5(i))),
LTRIM(RTRIM(l_quantity_5(i))),
LTRIM(RTRIM(l_budget_amount_5(i))),
LTRIM(RTRIM(l_source_promotion_id_5(i))),
LTRIM(RTRIM(l_offer_id_5(i))),
LTRIM(RTRIM(l_orig_system_reference(i))),
LTRIM(RTRIM(l_orig_system_code(i))),
LTRIM(RTRIM(l_urgent_flag(i))),
LTRIM(RTRIM(l_accept_flag(i))),
TO_DATE(l_assign_date(i), 'MM/DD/YYYY'),
LTRIM(RTRIM(l_assign_sales_group_id(i))),
LTRIM(RTRIM(l_assign_to_person_id(i))),
LTRIM(RTRIM(l_assign_to_salesforce_id(i))),
LTRIM(RTRIM(l_auto_assignment_type(i))),
LTRIM(RTRIM(l_deleted_flag(i))),
LTRIM(RTRIM(l_import_flag(i))),
LTRIM(RTRIM(l_keep_flag(i))),
LTRIM(RTRIM(l_prm_assignment_type(i))),
LTRIM(RTRIM(l_qualified_flag(i))),
LTRIM(RTRIM(l_reject_reason_code(i))),
LTRIM(RTRIM(l_scorecard_id(i))),
LTRIM(RTRIM(l_primary_contact_flag(i))),
TO_DATE(l_address_effective_date(i), 'MM/DD/YYYY'),
LTRIM(RTRIM(l_address_lines_phonetic(i))),
LTRIM(RTRIM(l_address_style(i))),
LTRIM(RTRIM(l_content_source_type(i))),
LTRIM(RTRIM(l_loc_description(i))),
LTRIM(RTRIM(l_loc_hierarchy_id(i))),
LTRIM(RTRIM(l_fa_location_id(i))),
LTRIM(RTRIM(l_floor(i))),
LTRIM(RTRIM(l_house_number(i))),
LTRIM(RTRIM(l_language(i))),
LTRIM(RTRIM(l_location_directions(i))),
LTRIM(RTRIM(l_po_box_number(i))),
--location_position(i))),
LTRIM(RTRIM(l_postal_plus4_code(i))),
LTRIM(RTRIM(l_sales_tax_geocode(i))),
LTRIM(RTRIM(l_sales_tax_inside_city_limits(i))),
LTRIM(RTRIM(l_short_description(i))),
LTRIM(RTRIM(l_street(i))),
LTRIM(RTRIM(l_street_number(i))),
LTRIM(RTRIM(l_street_suffix(i))),
LTRIM(RTRIM(l_suite(i))),
LTRIM(RTRIM(l_time_zone(i))),
LTRIM(RTRIM(l_loc_validated_flag(i))),
LTRIM(RTRIM(l_duns_number(i))),
LTRIM(RTRIM(l_group_type(i))),
LTRIM(RTRIM(l_gsa_indicator_flag(i))),
LTRIM(RTRIM(l_hq_branch_ind(i))),
LTRIM(RTRIM(l_jgzz_fiscal_code(i))),
LTRIM(RTRIM(l_known_as(i))),
LTRIM(RTRIM(l_known_as2(i))),
LTRIM(RTRIM(l_known_as3(i))),
LTRIM(RTRIM(l_known_as4(i))),
LTRIM(RTRIM(l_known_as5(i))),
LTRIM(RTRIM(l_language_name(i))),
TO_DATE(l_last_ordered_date(i), 'MM/DD/YYYY'),
LTRIM(RTRIM(l_mission_statement(i))),
LTRIM(RTRIM(l_organization_name_phonetic(i))),
LTRIM(RTRIM(l_party_number(i))),
LTRIM(RTRIM(l_person_first_name_phonetic(i))),
LTRIM(RTRIM(l_person_iden_type(i))),
LTRIM(RTRIM(l_person_identifier(i))),
LTRIM(RTRIM(l_person_last_name_phonetic(i))),
LTRIM(RTRIM(l_person_name_suffix(i))),
LTRIM(RTRIM(l_person_previous_last_name(i))),
LTRIM(RTRIM(l_party_reference_use_flag(i))),
LTRIM(RTRIM(l_sic_code_type(i))),
LTRIM(RTRIM(l_tax_name(i))),
LTRIM(RTRIM(l_total_num_of_orders(i))),
LTRIM(RTRIM(l_total_ordered_amount(i))),
LTRIM(RTRIM(l_parties_validated_flag(i))),
LTRIM(RTRIM(l_ps_uses_comments(i))),
LTRIM(RTRIM(l_primary_per_type(i))),
LTRIM(RTRIM(l_site_use_type(i))),
LTRIM(RTRIM(l_addressee(i))),
LTRIM(RTRIM(l_mailstop(i))),
LTRIM(RTRIM(l_party_site_name(i))),
LTRIM(RTRIM(l_party_site_number(i))),
LTRIM(RTRIM(l_org_cnt_comments(i))),
LTRIM(RTRIM(l_contact_number(i))),
LTRIM(RTRIM(l_decision_maker_flag(i))),
LTRIM(RTRIM(l_department(i))),
LTRIM(RTRIM(l_department_code(i))),
LTRIM(RTRIM(l_rank(i))),
LTRIM(RTRIM(l_promotion_id(i))),
LTRIM(RTRIM(l_role_level(i))),
LTRIM(RTRIM(primary_contact_per_role_type(i))),
LTRIM(RTRIM(l_cnt_pnt_content_source_type(i))),
LTRIM(RTRIM(l_phone_country_code(i))),
LTRIM(RTRIM(l_fax_country_code(i))),
LTRIM(RTRIM(l_phone_calling_calendar(i))),
LTRIM(RTRIM(l_cnt_pnt_time_zone(i))),
LTRIM(RTRIM(l_raw_phone_number(i))),
LTRIM(RTRIM(l_email_format(i))),
LTRIM(RTRIM(l_fax_extension(i))),
LTRIM(RTRIM(l_org_cnt_title(i))),
LTRIM(RTRIM(l_offer_id(i))),
LTRIM(RTRIM(l_request_id(i))),
LTRIM(RTRIM(l_program_application_id(i))),
LTRIM(RTRIM(l_program_id(i))),
TO_DATE(l_program_update_date(i), 'MM/DD/YYYY'),
LTRIM(RTRIM(l_load_error_message(i))),
LTRIM(RTRIM(l_phone_id(i))),
LTRIM(RTRIM(l_contact_party_id(i))),
LTRIM(RTRIM(l_security_group_id(i))),
LTRIM(RTRIM(l_net_worth(i))),
LTRIM(RTRIM(l_lead_number(i))),
LTRIM(RTRIM(l_prm_sales_lead_type(i))),
LTRIM(RTRIM(l_prm_exec_sponsor_flag(i))),
LTRIM(RTRIM(l_prm_prj_lead_in_place_flag(i))),
LTRIM(RTRIM(l_incumbent_partner_party_id(i))),
LTRIM(RTRIM(lincumbent_partner_resource_id(i))),
LTRIM(RTRIM(l_prm_ind_classification_code(i))),
LTRIM(RTRIM(l_party_type(i))),
LTRIM(RTRIM(l_party_id(i))),
LTRIM(RTRIM(l_party_site_id(i))),
LTRIM(RTRIM(l_location_id(i))),
LTRIM(RTRIM(l_rel_party_id(i))),
LTRIM(RTRIM(l_sales_lead_id(i))),
LTRIM(RTRIM(l_customer_key(i))),
LTRIM(RTRIM(l_address_key(i))),
LTRIM(RTRIM(l_contact_key(i))),
LTRIM(RTRIM(l_new_party_flag(i))),
LTRIM(RTRIM(l_new_loc_flag(i))),
LTRIM(RTRIM(l_new_ps_flag(i))),
LTRIM(RTRIM(l_new_rel_flag(i))),
LTRIM(RTRIM(l_new_con_flag(i))),
LTRIM(RTRIM(l_lead_rank_id(i))),
LTRIM(RTRIM(l_marketing_score(i))),
LTRIM(RTRIM(l_PERSON_INITIALS(i))),
LTRIM(RTRIM(l_LEAD_DATE(i))),
l_LOAD_TYPE,
L_LOAD_DATE,
L_LOAD_STATUS,
L_STATUS_CODE,
l_batch_id,
l_import_source_line_id(i),
LTRIM(RTRIM(l_CATEGORY_ID_1(i))),
LTRIM(RTRIM(l_CATEGORY_ID_2(i))),
LTRIM(RTRIM(l_CATEGORY_ID_3(i))),
LTRIM(RTRIM(l_CATEGORY_ID_4(i))),
LTRIM(RTRIM(l_CATEGORY_ID_5(i))),
LTRIM(RTRIM(l_SALES_METHODOLOGY_ID(i))),
LTRIM(RTRIM(l_DUNS_NUMBER_C(i))),
LTRIM(RTRIM(l_SOURCE_SECONDARY_REFERENCE(i))),
LTRIM(RTRIM(l_NOTE_TYPE(i))));
PROCEDURE Insert_Lead_Data (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
p_commit IN VARCHAR2 := FND_API.G_TRUE,
p_import_list_header_id IN NUMBER,
p_data IN char_data_set_type_w,
p_error_rows IN num_data_set_type_w,
p_row_count IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
--
-- Standard API information constants.
--
L_API_VERSION CONSTANT NUMBER := 1.0;
L_API_NAME CONSTANT VARCHAR2(30) := 'Insert_Lead_Data';
SELECT user_status_id
FROM ams_user_statuses_vl
WHERE UPPER(system_status_type) = 'AMS_IMPORT_STATUS'
AND UPPER(system_status_code) = 'STAGED'
AND default_flag = 'Y';
SELECT A.field_column_name, B.batch_id
FROM ams_list_src_fields A, ams_imp_list_headers_all b
WHERE b.IMPORT_LIST_HEADER_ID = l_import_list_header_id
AND b.LIST_SOURCE_TYPE_ID = A.LIST_SOURCE_TYPE_ID
ORDER BY LIST_SOURCE_FIELD_ID;
SELECT dedupe_flag
FROM ams_imp_list_headers_all
WHERE import_list_header_id = l_import_list_header_id;
Insert_Data (
p_api_version => 1.0,
p_table_name => l_field_table_name,
p_prim_key_name => 'IMPORT_SOURCE_LINE_ID',
p_seq_name => 'AMS_IMP_SOURCE_LINES_S',
p_str_col_names => l_str_col_names,
p_num_col_names => l_num_col_names,
p_str_data => l_str_data,
p_num_data => l_num_data,
p_col_names => l_columns,
p_data => p_data,
p_error_rows => p_error_rows,
p_row_count => l_row_count,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => l_msg_data);
UPDATE ams_imp_list_headers_all
SET status_code = 'STAGED',
--loaded_no_of_rows = p_row_count,
loaded_date = SYSDATE, user_status_id = l_status_id
WHERE import_list_header_id = p_import_list_header_id;
UPDATE ams_imp_list_headers_all
SET status_code = 'STAGED',
--loaded_no_of_rows = p_row_count,
loaded_date = SYSDATE
WHERE import_list_header_id = p_import_list_header_id;
END Insert_Lead_Data;
PROCEDURE Insert_List_Data (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
p_commit IN VARCHAR2 := FND_API.G_TRUE,
p_import_list_header_id IN NUMBER,
p_data IN char_data_set_type_w,
p_row_count IN NUMBER,
p_error_rows IN num_data_set_type_w,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
--
-- Standard API information constants.
--
L_API_VERSION CONSTANT NUMBER := 1.0;
L_API_NAME CONSTANT VARCHAR2(30) := 'Insert_List_Data';
SELECT user_status_id
FROM ams_user_statuses_vl
WHERE UPPER(system_status_type) = 'AMS_IMPORT_STATUS'
AND UPPER(system_status_code) = 'STAGED'
AND default_flag = 'Y';
SELECT A.field_column_name, A.FIELD_TABLE_NAME, B.BATCH_ID
FROM ams_list_src_fields A, ams_imp_list_headers_all b
WHERE b.IMPORT_LIST_HEADER_ID = l_import_list_header_id
AND b.LIST_SOURCE_TYPE_ID = A.LIST_SOURCE_TYPE_ID
ORDER BY LIST_SOURCE_FIELD_ID;
SELECT dedupe_flag
FROM ams_imp_list_headers_all
WHERE import_list_header_id = l_import_list_header_id;
Insert_Data (
p_api_version => 1.0,
p_table_name => l_field_table_name,
p_prim_key_name => 'IMPORT_SOURCE_LINE_ID',
p_seq_name => 'AMS_IMP_SOURCE_LINES_S',
p_str_col_names => l_str_col_names,
p_num_col_names => l_num_col_names,
p_str_data => l_str_data,
p_num_data => l_num_data,
p_col_names => l_columns,
p_data => p_data,
p_error_rows => p_error_rows,
p_row_count => l_row_count,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => l_msg_data);
UPDATE ams_imp_list_headers_all
SET status_code = 'STAGED',
--loaded_no_of_rows = p_row_count,
loaded_date = SYSDATE, user_status_id = l_status_id
WHERE import_list_header_id = p_import_list_header_id;
UPDATE ams_imp_list_headers_all
SET status_code = 'STAGED',
--loaded_no_of_rows = p_row_count,
loaded_date = SYSDATE
WHERE import_list_header_id = p_import_list_header_id;
END Insert_List_Data;
PROCEDURE Insert_Data (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_table_name IN VARCHAR2,
p_prim_key_name IN VARCHAR2,
p_seq_name IN VARCHAR2,
p_str_col_names IN char_data_set_type_w,
p_num_col_names IN char_data_set_type_w,
p_str_data IN char_data_set_type_w,
p_num_data IN num_data_set_type_w,
p_obj_version_num IN NUMBER := NULL, -- if NULL, do not insert this column
p_last_update_date_flag IN VARCHAR2 := FND_API.G_TRUE,-- need this column?
p_last_update_by_flag IN VARCHAR2 := FND_API.G_TRUE,-- need this column?
p_creation_date_flag IN VARCHAR2 := FND_API.G_TRUE,-- need this column?
p_created_by_flag IN VARCHAR2 := FND_API.G_TRUE,-- need this column?
p_ls_update_log_flag IN VARCHAR2 := FND_API.G_TRUE,-- need this column?
p_col_names IN char_data_set_type_w,
p_data IN char_data_set_type_w,
p_error_rows IN num_data_set_type_w,
p_row_count IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
--
-- Standard API information constants.
--
L_API_VERSION CONSTANT NUMBER := 1.0;
L_API_NAME CONSTANT VARCHAR2(30) := 'Insert_Data';
l_tab_name_clause1 VARCHAR2(8000) := 'INSERT INTO ';
SELECT SEQUENCE_OWNER INTO l_seq_owner
FROM sys.ALL_SEQUENCES WHERE SEQUENCE_NAME = l_seq_name;
'SELECT ' || l_seq_name || '.NEXTVAL FROM DUAL' INTO l_start_seq_num;
|| 'LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN,';
--LAST_UPDATE_DATE
dbms_sql.bind_variable (g_cursor, ':r2', l_date);
--LAST_UPDATED_BY
dbms_sql.bind_variable (g_cursor, ':r3', l_user_id);
--LAST_UPDATE_LOGIN
dbms_sql.bind_variable (g_cursor, ':r6', l_login_id);
END Insert_Data;
SELECT 1
FROM AMS_LIST_IMPORT_ERRORS
WHERE LIST_IMPORT_ERROR_ID = l_list_imp_id;
SELECT AMS_LIST_IMPORT_ERRORS_S.NEXTVAL INTO l_list_imp_id
FROM DUAL;
INSERT INTO AMS_LIST_IMPORT_ERRORS (
LIST_IMPORT_ERROR_ID,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
IMPORT_LIST_HEADER_ID,
IMPORT_SOURCE_LINE_ID,
IMPORT_TYPE,
COL1,
ERROR_TYPE,
SECURITY_GROUP_ID,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
BATCH_ID
)
VALUES (
l_list_imp_id,
FND_GLOBAL.User_ID,
l_date,
l_date,
FND_GLOBAL.User_ID,
FND_GLOBAL.Conc_Login_ID,
p_list_imp_error_rec.IMPORT_LIST_HEADER_ID,
p_list_imp_error_rec.IMPORT_SOURCE_LINE_ID,
p_list_imp_error_rec.IMPORT_TYPE,
p_list_imp_error_rec.COL1,
p_list_imp_error_rec.ERROR_TYPE,
p_list_imp_error_rec.SECURITY_GROUP_ID,
p_list_imp_error_rec.REQUEST_ID,
p_list_imp_error_rec.PROGRAM_APPLICATION_ID,
p_list_imp_error_rec.PROGRAM_ID,
p_list_imp_error_rec.PROGRAM_UPDATE_DATE,
p_list_imp_error_rec.BATCH_ID
);