thota198
asked on
need of code
i have to send an email to the non-production database and it has to print the banner in the way given below in the placeholder
i defined this placeholder and replaced in the replaced value section below
io_email_replace_vals (179).fieldname := '|V_BANNER|'
IF i_email_cd = 'APPROVAL_COMPLETE_BOM_2T_
io_email_replace_vals (179).fieldvalue :=
'============= THE FOLLOWING SECTION WILL NOT APPEAR ON PRODUCTION ============= ' || CHR(10) ||
'DB = ' ||v_db_name || CHR(10) ||
'TO = <to>' || CHR(10) ||
'CC = <cc>' || CHR(10) ||
'BCC= <bcc>' ;
ELSE
io_email_replace_vals (179).fieldvalue := '';
END IF;
all this is defined in a procedure called replace_placeholder_values
im calling this proc in 'get_email_contents_proc'
so we have to use a replace function for tht
i have tried to give this way its not working
IF v_email_cd = 'APPROVAL_COMPLETE_BOM_2T_
IF v_db_name<>'DMPROD' then
o_email_subject := REPLACE (o_email_subject, v_email_replace_vals (l_cnt).fieldname, v_email_replace_vals (l_cnt).fieldvalue);
o_email_to := REPLACE (o_email_to, v_email_replace_vals (l_cnt).fieldname, v_email_replace_vals (l_cnt).fieldvalue);
o_email_cc := REPLACE (o_email_cc, v_email_replace_vals (l_cnt).fieldname, v_email_replace_vals (l_cnt).fieldvalue);
v_email_replace_vals (l_cnt).fielname := REPLACE (v_email_replace_vals (l_cnt).fieldname,
v_email_replace_vals (l_cnt).fieldvalue := REPLACE ((REPLACE ( v_email_replace_vals (l_cnt).fieldvalue, '<to>', 'sthota@cisco.com')),
'<cc>',
'kuchoudh@cisco.com'
);
o_email_text := clobreplace_fn (o_email_text,
v_email_replace_vals (l_cnt).fieldname,
NVL (v_email_replace_vals (l_cnt).fieldvalue, ' ')
);
END IF;
END IF;
v_email_cd is message type
Can anyone explain why the above replace func is not working
Would be possible to send all the code?
ASKER
this is the code from where the procedure is getting called from ' get_email_contents_proc'
build_replaceable_values_t ab (o_rc => o_rc,
o_reason => o_reason,
o_file_name => o_file_name,
o_file_data => o_file_data,
i_deal_id => i_deal_id,
i_quote_object_id => i_quote_object_id,
i_user_id => i_user_id,
i_approver_seq => i_approver_seq,
i_email_cd => v_email_cd,
io_email_replace_vals => v_email_replace_vals,
i_source_profile_id => i_source_profile_id,
i_order_number => i_order_number
--added for Q2O notifications
);
-- Get Email Data
FOR rec_pdr_email IN pdr_email_cur
LOOP
-- read info into output variables
o_email_from := rec_pdr_email.message_from ;
--- o_email_to := rec_pdr_email.message_to;
--- o_email_cc := rec_pdr_email.message_cc;
o_email_to := TRIM (REPLACE (REPLACE (REPLACE (rec_pdr_email.message_to, CHR (10), ''), CHR (13), ''), CHR (9), ''));
o_email_cc := TRIM (REPLACE (REPLACE (REPLACE (rec_pdr_email.message_cc, CHR (10), ''), CHR (13), ''), CHR (9), ''));
o_email_subject := rec_pdr_email.message_subj ect;
o_email_reply_to := rec_pdr_email.message_repl y_to;
o_email_text := rec_pdr_email.message_form at;
END LOOP;
OPEN check_emer_euro;
FETCH check_emer_euro
INTO v_dummy;
v_exists := check_emer_euro%FOUND;
CLOSE check_emer_euro;
-- If it is EMER/EURO deal, then remove the Approver Email ID from the message subject
-- added by mgarikap for UAT fix.
IF v_exists THEN
IF INSTR (o_email_subject, 'by') > 0 THEN
o_email_subject := SUBSTR (o_email_subject, 0, INSTR (o_email_subject, 'by') - 1);
END IF;
END IF;
IF v_local_flag = 'Y' THEN
o_email_subject := v_email_subject;
o_email_text := v_email_text || '<br>' || '<br>' || '<br>' || o_email_text;
END IF;
-- Replace Place holder fields
IF (v_email_replace_vals.COUN T > 0) THEN
FOR l_cnt IN v_email_replace_vals.FIRST .. v_email_replace_vals.LAST
LOOP
BEGIN
-- Log for debug purpose
-- Q3 FY11 release enhancement from Sherin. Defect # 74372
--taken as enhancement to display partner contact user name in Subject , who has actually reopen the Cisco deal
IF v_email_cd = 'CISCO_DEAL_NOTIFY_REOPENE D_QUOTE_TO _AM' THEN
SELECT cq_pdb_pae_common_pkg.getp aeuserid_f n (updated_by, 'CCO_ID')
INTO v_partner_contact_pae_user _id
FROM cq_deal
WHERE opty_number = i_deal_id;
SELECT user_first_name, user_last_name
INTO v_partner_contact_first_na me, v_partner_contact_last_nam e
FROM pae_user
WHERE user_id = v_partner_contact_pae_user _id;
IF v_partner_contact_first_na me IS NOT NULL AND v_partner_contact_last_nam e IS NOT NULL THEN
o_email_subject := REPLACE ((REPLACE (o_email_subject, '|PARTNER_CONTACT_FIRST_NA ME|', v_partner_contact_first_na me)),
'|PARTNER_CONTACT_LAST_NAM E|',
v_partner_contact_last_nam e
);
END IF;
END IF;
--ends here
----added by sushanth for printing banner in message body for |v_banner|---
IF v_email_cd = 'APPROVAL_COMPLETE_BOM_2T_ FULFILMENT ' THEN
IF v_db_name<>'DMPROD' then
o_email_subject := REPLACE (o_email_subject, v_email_replace_vals (l_cnt).fieldname, v_email_replace_vals (l_cnt).fieldvalue);
o_email_to := REPLACE (o_email_to, v_email_replace_vals (l_cnt).fieldname, v_email_replace_vals (l_cnt).fieldvalue);
o_email_cc := REPLACE (o_email_cc, v_email_replace_vals (l_cnt).fieldname, v_email_replace_vals (l_cnt).fieldvalue);
v_email_replace_vals (l_cnt).fielname := REPLACE (v_email_replace_vals (l_cnt).fieldname,
v_email_replace_vals (l_cnt).fieldvalue := REPLACE ((REPLACE ( v_email_replace_vals (l_cnt).fieldvalue, '<to>', 'sthota@cisco.com')),
'<cc>',
'kuchoudh@cisco.com'
);
o_email_text := clobreplace_fn (o_email_text,
v_email_replace_vals (l_cnt).fieldname,
NVL (v_email_replace_vals (l_cnt).fieldvalue, ' ')
);
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
END IF;
o_email_to := TRIM (c_comma FROM o_email_to);
o_email_cc := TRIM (c_comma FROM o_email_cc);
-- conversion of place holders is over here
build_replaceable_values_t
o_reason => o_reason,
o_file_name => o_file_name,
o_file_data => o_file_data,
i_deal_id => i_deal_id,
i_quote_object_id => i_quote_object_id,
i_user_id => i_user_id,
i_approver_seq => i_approver_seq,
i_email_cd => v_email_cd,
io_email_replace_vals => v_email_replace_vals,
i_source_profile_id => i_source_profile_id,
i_order_number => i_order_number
--added for Q2O notifications
);
-- Get Email Data
FOR rec_pdr_email IN pdr_email_cur
LOOP
-- read info into output variables
o_email_from := rec_pdr_email.message_from
--- o_email_to := rec_pdr_email.message_to;
--- o_email_cc := rec_pdr_email.message_cc;
o_email_to := TRIM (REPLACE (REPLACE (REPLACE (rec_pdr_email.message_to,
o_email_cc := TRIM (REPLACE (REPLACE (REPLACE (rec_pdr_email.message_cc,
o_email_subject := rec_pdr_email.message_subj
o_email_reply_to := rec_pdr_email.message_repl
o_email_text := rec_pdr_email.message_form
END LOOP;
OPEN check_emer_euro;
FETCH check_emer_euro
INTO v_dummy;
v_exists := check_emer_euro%FOUND;
CLOSE check_emer_euro;
-- If it is EMER/EURO deal, then remove the Approver Email ID from the message subject
-- added by mgarikap for UAT fix.
IF v_exists THEN
IF INSTR (o_email_subject, 'by') > 0 THEN
o_email_subject := SUBSTR (o_email_subject, 0, INSTR (o_email_subject, 'by') - 1);
END IF;
END IF;
IF v_local_flag = 'Y' THEN
o_email_subject := v_email_subject;
o_email_text := v_email_text || '<br>' || '<br>' || '<br>' || o_email_text;
END IF;
-- Replace Place holder fields
IF (v_email_replace_vals.COUN
FOR l_cnt IN v_email_replace_vals.FIRST
LOOP
BEGIN
-- Log for debug purpose
-- Q3 FY11 release enhancement from Sherin. Defect # 74372
--taken as enhancement to display partner contact user name in Subject , who has actually reopen the Cisco deal
IF v_email_cd = 'CISCO_DEAL_NOTIFY_REOPENE
SELECT cq_pdb_pae_common_pkg.getp
INTO v_partner_contact_pae_user
FROM cq_deal
WHERE opty_number = i_deal_id;
SELECT user_first_name, user_last_name
INTO v_partner_contact_first_na
FROM pae_user
WHERE user_id = v_partner_contact_pae_user
IF v_partner_contact_first_na
o_email_subject := REPLACE ((REPLACE (o_email_subject, '|PARTNER_CONTACT_FIRST_NA
'|PARTNER_CONTACT_LAST_NAM
v_partner_contact_last_nam
);
END IF;
END IF;
--ends here
----added by sushanth for printing banner in message body for |v_banner|---
IF v_email_cd = 'APPROVAL_COMPLETE_BOM_2T_
IF v_db_name<>'DMPROD' then
o_email_subject := REPLACE (o_email_subject, v_email_replace_vals (l_cnt).fieldname, v_email_replace_vals (l_cnt).fieldvalue);
o_email_to := REPLACE (o_email_to, v_email_replace_vals (l_cnt).fieldname, v_email_replace_vals (l_cnt).fieldvalue);
o_email_cc := REPLACE (o_email_cc, v_email_replace_vals (l_cnt).fieldname, v_email_replace_vals (l_cnt).fieldvalue);
v_email_replace_vals (l_cnt).fielname := REPLACE (v_email_replace_vals (l_cnt).fieldname,
v_email_replace_vals (l_cnt).fieldvalue := REPLACE ((REPLACE ( v_email_replace_vals (l_cnt).fieldvalue, '<to>', 'sthota@cisco.com')),
'<cc>',
'kuchoudh@cisco.com'
);
o_email_text := clobreplace_fn (o_email_text,
v_email_replace_vals (l_cnt).fieldname,
NVL (v_email_replace_vals (l_cnt).fieldvalue, ' ')
);
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
END IF;
o_email_to := TRIM (c_comma FROM o_email_to);
o_email_cc := TRIM (c_comma FROM o_email_cc);
-- conversion of place holders is over here
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Yes, some results from debug would be nice...
By the way, in the code you paste here, this line is incorrect:
v_email_replace_vals (l_cnt).fielname := REPLACE (v_email_replace_vals (l_cnt).fieldname,
Based on that, your code wouldnt even compile.
By the way, in the code you paste here, this line is incorrect:
v_email_replace_vals (l_cnt).fielname := REPLACE (v_email_replace_vals (l_cnt).fieldname,
Based on that, your code wouldnt even compile.