create or replace PACKAGE geozf_apl_autopay_pkg AUTHID CURRENT_USER AS
-- $Header Ver 2.0$
/*******************************************************************************
-- Copyright(c) GE Appliances and Lighting
* FILENAME : GEOZF_APL_AUTOPAY_PKG.sql
*
* FILEPATH : $GEONT_TOP/install/sql
*
* APPLICATION : GE Trade Management
*
* AUTHORS : Sreedhar Kola
*
* CREATION DATE : 02-DEC-2015
*
*
* PURPOSE : For JIRA# ERPOEP-663 Package modified is used If Settlement Method is Check on OFFER Header and Customer Trade Profile doesnot have Vendor Information Custom program wont flag an error message .
* : For JIRA# ERPOEP-3853 To check Frequency , Frequency Unit Based on AutoPay Flag
: For JIRA# ERPOEP-3634 extend Custom Program for Auto Pay
* We have modifed the standard Concurrent program "Claims AutoPay" extended.Standard package name "OZF_AUTOPAY_PVT"
* PARAMETERS : N/A
*
*
* NOTES :
*
*
* PRE-REQUISITES: N/A
****************************************************************************************
*
* CALLING INSTALLATION SCRIPTS
* ----------------------------
*
* The following installation scripts call this script: N/A
*
**********************************************************************************************************************
* CALLED INSTALLATION SCRIPTS
* ---------------------------
*
* The following installation scripts are called by this script: N/A
*
*
**********************************************************************************************************************
*
*
* UPDATION HISTORY :
*
* Vsn Date Who Remarks
*---------------------------------------------------------------------------------------------
* 1.0 02-DEC-2015 Sreedhar Kola JIRA# ERPOEP-3634,ERPOEP-663,ERPOEP-3853
* 1.1 25-SEP-2019 Bixamaiah Dhuvva JIRA# ERPOEP-38097: Creation of claims at offer level instead of product level
* Populate Claim Owner, Claim Type and Claim source fields
* 1.2 25-JUN-2020 Bixamaiah Dhuvva JIRA# ERPOEP-42308: Create claim only for the offers that have all the documentation
* 1.3 28-SEP-2020 Bixamaiah Dhuvva JIRA# ERPOEP-44421: Update credit memo notes
* 1.4 06-APR-2021 Ajay OdetiBixamaiah Dhuvva JIRA# ERPOEP-44805 Notification of Errors - Autopay process to send email on the list of Autopay offers for which the claim was not created
* 1.5 21-MAR-2022 Sri Harsha Jira#OPS-3836 : Changes in Autopay Program to resolve issues for Monthly and Semi-Annual frequency offers
* 1.6 13-APR-2022 Sri Harsha Jira#OPS-3851 : Claim Type should be based on Sales Finance on the Offer
* 1.7 19-MAY-2022 Sri Harsha Jira#OPS-3922 : Autopay changes related to Notetypes at offer level
* 1.8 17-JUN-2022 Sri Harsha Jira#OPS-3933 : Autopay Email Changes to Claims in Open Status
* 1.9 22-JUL-2022 Sri Harsha Jira#OPS-3942 : Autopay Program changes based on Suppress Autopay Email DFF attribute
* 2.0 11-JAN-2023 Anjireddy Bhavanam Jira#ERPOSB-1319 : Autopay not creating claim for 2022 offers once DEC period is closed
*****************************************************************************************************************/
TYPE offer_type IS RECORD(
cust_account_id NUMBER,
amount NUMBER,
offer_id NUMBER,
adjustment_type_id NUMBER);
/*12.2.6 Autopay*/
----------------------------------------------------------------------------------------
--plan_id :For storing the plan ID which meet offer/budget freq criteria
--upd_last_pd_dt_flag : Flag to indicate if last paid date needs to be updated in ozf_offers
--paid_date : Holds the date value to be updated in the ozf_offers table
--create_claim_flag : If claim is to be created or not based on freq met criteria
--frequency_unit : Holds offer freq type (DAYS/WEEKS/MONTHS/QUARTERS/YEAR)
-----------------------------------------------------------------------------------------
TYPE frequency_rec_type IS RECORD(
plan_id NUMBER,
upd_last_pd_dt_flag VARCHAR2(1),
paid_date DATE,
create_claim_flag VARCHAR2(1),
claim_id NUMBER,
frequency_unit VARCHAR2(20),
frequency NUMBER);
freq_rec frequency_rec_type;
TYPE frequency_tbl_typ IS TABLE OF freq_rec%TYPE INDEX BY BINARY_INTEGER;
g_miss_offer_rec offer_type;
TYPE offer_tbl_type IS TABLE OF offer_type INDEX BY BINARY_INTEGER;
g_miss_offer_tbl offer_tbl_type;
FUNCTION get_paid_date(p_autopay_frequency_unit VARCHAR2,
p_autopay_frequency NUMBER,
p_autopay_date DATE) RETURN DATE;
---------------------------------------------------------------------
-- PROCEDURE
-- Create_Claim_for_BD_Offer
--
-- PURPOSE
-- Create a claim for a backdated offer.
--
-- PARAMETERS
-- p_offer_tbl : list of offers info that a claim will be created on.
--
---------------------------------------------------------------------
PROCEDURE create_claim_for_bd_offer(p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
p_commit IN VARCHAR2 := fnd_api.g_false,
p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
p_offer_tbl IN offer_tbl_type);
--------------------------------------------------------------------------------
-- API name : Start_Autopay
-- Type : Public
-- Pre-reqs : None
-- Function : Package that performs auto payment.
-- Parameters :
--
-- IN : p_run_mode IN VARCHAR2 Optional
-- : p_customer_id IN NUMBER Optional
-- : p_relationship_type IN VARCHAR2 Optional
-- : p_related_cust_account_id IN NUMBER Optional
-- : p_buy_group_party_id IN NUMBER Optional
-- : p_select_cust_children_flag IN VARCHAR2 Optional
-- : p_pay_to_customer IN VARCHAR2 Optional
-- : p_fund_id IN NUMBER Optional
-- : p_plan_type IN NUMBER Optional
-- : p_offer_type IN VARCHAR2 Optional
-- : p_plan_id IN NUMBER Optional
-- : p_product_category_id IN NUMBER Optional
-- : p_product_id IN NUMBER Optional
-- : p_end_date IN VARCHAR2 Optional
-- : p_org_id IN NUMBER Optional
--
-- Version : Current version 1.0
--
--------------------------------------------------------------------------------
PROCEDURE start_autopay(errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY NUMBER,
p_org_id IN NUMBER DEFAULT NULL,
p_run_mode IN VARCHAR2 := NULL,
p_customer_id IN NUMBER := NULL,
p_relationship_type IN VARCHAR2 := NULL,
p_related_cust_account_id IN NUMBER := NULL,
p_buy_group_party_id IN NUMBER := NULL,
p_select_cust_children_flag IN VARCHAR2 := 'N',
p_pay_to_customer IN VARCHAR2 := NULL,
p_fund_id IN NUMBER := NULL,
p_plan_type IN VARCHAR2 := NULL,
p_offer_type IN VARCHAR2 := NULL,
p_plan_id IN NUMBER := NULL,
p_product_category_id IN NUMBER := NULL,
p_product_id IN NUMBER := NULL,
p_period_type IN VARCHAR2 := NULL, --added for 12.2.6 autopay
p_period_value IN VARCHAR2 := NULL, --added for 12.2.6 autopay
p_start_date IN VARCHAR2 := NULL, --added for 12.2.6 autopay
p_end_date IN VARCHAR2 := NULL,
p_group_by_offer IN VARCHAR2);
FUNCTION is_offer_eligible(p_list_header_id IN NUMBER) RETURN VARCHAR2; --Version 1.2 changes
--Version 1.3 changes
FUNCTION get_memo_notes(p_autopay_frequency_unit IN VARCHAR2,
p_autopay_frequency IN NUMBER) RETURN VARCHAR2;
END geozf_apl_autopay_pkg;
/
create or replace PACKAGE BODY geozf_apl_autopay_pkg AS
g_module CONSTANT VARCHAR2(30) := 'AUTOPAY';
g_debug_log_dest NUMBER := TO_NUMBER(gefnd_report_util_pkg.get_iface_attribute('OZF_AUTOPAY_DEBUG_LOG_DEST', GEFND_LOG_PKG.DEST_TABLE));
g_update_event CONSTANT VARCHAR2(30) := 'UPDATE';
g_daily CONSTANT VARCHAR2(30) := 'DAYS';
g_weekly CONSTANT VARCHAR2(30) := 'WEEKS';
g_monthly CONSTANT VARCHAR2(30) := 'MONTHS';
g_quarterly CONSTANT VARCHAR2(30) := 'QUARTERS';
g_annual CONSTANT VARCHAR2(30) := 'YEAR';
g_offer_type CONSTANT VARCHAR2(30) := 'OFFR';
g_campaign_type CONSTANT VARCHAR2(30) := 'CAMP';
g_autopay_flag_off CONSTANT VARCHAR2(40) := 'Autopay flag is not turned on.';
g_autopay_period_miss CONSTANT VARCHAR2(40) := 'Autopay period information missing.';
g_autopay_plan_type_err CONSTANT VARCHAR2(40) := 'Can not hanlde this plan type.';
g_claim_setup_id CONSTANT NUMBER := 2001;
g_claim_status CONSTANT VARCHAR2(30) := 'OZF_CLAIM_STATUS';
g_open_status CONSTANT VARCHAR2(30) := 'OPEN';
g_closed_status CONSTANT VARCHAR2(30) := 'CLOSED';
g_dbg_on BOOLEAN := TRUE;
g_open_claim_status VARCHAR2(1) := nvl(fnd_profile.value('OZF_AUTY_CREATE_CLAM_OPEN'), 'N');
ozf_debug_high_on BOOLEAN := fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_debug_high);
ozf_debug_low_on BOOLEAN := fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_debug_low);
-- Variables to store request related details
g_request_id CONSTANT NUMBER := fnd_global.conc_request_id;
g_login_id CONSTANT NUMBER := fnd_global.conc_login_id;
g_program_id CONSTANT NUMBER := fnd_global.conc_program_id;
g_prog_appl_id CONSTANT NUMBER := fnd_global.prog_appl_id;
g_message_tbl gefnd_common_utils_pkg.message_tbl := gefnd_common_utils_pkg.message_tbl();
-- Ver 1.4 ERPOEP-44805 Start
gv_file_name VARCHAR2(1000) := 'Auto_Pay_Claim_Not_Created_Offer_Details.txt';
gv_offer_claim_err_utl utl_file.file_type;
gv_offer_mail_flag VARCHAR2(1) := 'N';
gb_exists BOOLEAN;
gn_size NUMBER;
gn_blk_size NUMBER;
gv_email_id_details VARCHAR2(1000);
gv_file_location VARCHAR2(1000);
gn_req_id NUMBER;
g_sf_usr_exp EXCEPTION; --ver 1.6
gv_nt_file_handle UTL_FILE.file_type; --ver 1.7
gn_note_type_cnt NUMBER :=0; --ver 1.7
gn_err_cnt NUMBER; --ver 1.8
gv_clm_err_msg VARCHAR2(2000); --ver 1.8
gn_iter_cnt NUMBER; --ver 1.8
-- Ver 1.4 ERPOEP-44805 End
---------------------------------------------------------------------
-- Definitions of some packagewise cursors.
---------------------------------------------------------------------
CURSOR g_site_info_csr(p_id IN NUMBER) IS
SELECT trade_profile_id,
cust_account_id,
site_use_id,
payment_method,
vendor_id,
vendor_site_id,
last_paid_date,
autopay_periodicity,
autopay_periodicity_type,
autopay_flag,
claim_threshold,
claim_currency,
org_id
FROM ozf_cust_trd_prfls
WHERE site_use_id = p_id;
--Added this cursor to fetch the primary bill to site if there is no bill to site information in accruals.
CURSOR cust_info_csr(p_cust_acct_id IN NUMBER) IS
SELECT trade_profile_id,
profile.cust_account_id,
s.site_use_id,
profile.payment_method,
profile.vendor_id,
profile.vendor_site_id,
profile.last_paid_date,
profile.autopay_periodicity,
profile.autopay_periodicity_type,
profile.autopay_flag,
profile.claim_threshold,
profile.claim_currency,
profile.org_id
FROM hz_cust_acct_sites a,
hz_cust_site_uses s,
ozf_cust_trd_prfls profile
WHERE a.cust_account_id = p_cust_acct_id
AND a.cust_acct_site_id = s.cust_acct_site_id
AND s.site_use_code = 'BILL_TO'
AND s.primary_flag = 'Y'
AND s.status = 'A'
AND profile.site_use_id = s.site_use_id;
--
CURSOR g_customer_info_csr(p_id IN NUMBER) IS
SELECT trade_profile_id,
cust_account_id,
site_use_id,
payment_method,
vendor_id,
vendor_site_id,
last_paid_date,
autopay_periodicity,
autopay_periodicity_type,
autopay_flag,
claim_threshold,
claim_currency,
org_id
FROM ozf_cust_trd_prfls
WHERE cust_account_id = p_id
AND site_use_id IS NULL;
--
CURSOR g_party_trade_info_csr(p_id IN NUMBER) IS
SELECT trade_profile_id,
cust_account_id,
site_use_id,
payment_method,
vendor_id,
vendor_site_id,
last_paid_date,
autopay_periodicity,
autopay_periodicity_type,
autopay_flag,
claim_threshold,
claim_currency,
org_id
FROM ozf_cust_trd_prfls
WHERE party_id = p_id
AND cust_account_id IS NULL;
--
PROCEDURE add_message(p_message_text IN VARCHAR2,
p_message_type IN VARCHAR2 DEFAULT NULL,
p_list_header_id IN NUMBER DEFAULT NULL,
p_cust_account_id IN NUMBER DEFAULT NULL,
p_offer_code IN VARCHAR2 DEFAULT NULL,
p_offer_name IN VARCHAR2 DEFAULT NULL) IS
CURSOR c_offer_cur IS
SELECT oo.offer_code,
qlh.description
FROM ozf_offers oo,
qp_list_headers_vl qlh
WHERE qlh.list_header_id = oo.qp_list_header_id
AND oo.qp_list_header_id = p_list_header_id;
--
l_offer_code ozf_offers.offer_code%TYPE := p_offer_code;
l_offer_name qp_list_headers_vl.description%TYPE := p_offer_name;
BEGIN
IF l_offer_code IS NULL and p_list_header_id IS NOT NULL THEN
OPEN c_offer_cur;
FETCH c_offer_cur INTO l_offer_code, l_offer_name;
CLOSE c_offer_cur;
END IF;
--
gefnd_common_utils_pkg.add_message(p_message_text => l_offer_code || '~' || l_offer_name || '~' || p_message_text,
p_message_type => NVL(p_message_type, 'E'));
gefnd_log_pkg.error(p_message_text);
/*gefnd_common_utils_pkg.add_message(p_message_tbl => g_message_tbl,
p_message_text => p_offer_code || '~' || p_offer_name || '~' || p_message_text,
p_message_type => NVL(p_message_type, 'E'));*/
END add_message;
--
PROCEDURE send_report(p_email_address IN VARCHAR2 DEFAULT NULL) IS
lv_error_message VARCHAR2(4000);
lv_return_status VARCHAR2(1);
l_ctx dbms_xmlgen.ctxhandle;
l_rc SYS_REFCURSOR;
l_xml CLOB;
l_clob CLOB;
lv_resultout VARCHAR2(1);
lv_err_msg VARCHAR2(4000);
lv_email_address VARCHAR2(400) := p_email_address;
lv_func VARCHAR2(30) := 'SEND_REPORT';
BEGIN
gefnd_log_pkg.push(lv_func);
gefnd_log_pkg.debug('p_email_address', p_email_address);
--
IF p_email_address IS NULL THEN
gefnd_log_pkg.pop(lv_func);
RETURN;
END IF;
--
IF lv_email_address IS NOT NULL THEN
OPEN l_rc FOR
SELECT CURSOR (SELECT 'Here is the list of messages logged for request ID: ' || fnd_global.conc_request_id || '.' header_text
FROM dual) info,
CURSOR (SELECT CASE message_type
WHEN 'E' THEN 'Error'
WHEN 'U' THEN 'Unexpected Error'
WHEN 'I' THEN 'Informational'
WHEN 'W' THEN 'Warning'
ELSE message_type
END "Severity",
regexp_substr(message_text, '(.*?)(~|$)', 1, 1, NULL, 1) "Offer_Code",
regexp_substr(message_text, '(.*?)(~|$)', 1, 2, NULL, 1) "Offer_Name",
regexp_substr(message_text, '(.*?)(~|$)', 1, 3, NULL, 1) "Message_Text",
to_char(TIMESTAMP, 'MM/DD/YYYY HH24:MI:SS') "Timestamp"
FROM TABLE(gefnd_common_utils_pkg.get_messages))"Messages",
CURSOR (SELECT name "Name",
value "Value"
FROM table(gefnd_common_utils_pkg.get_conc_req_details(fnd_global.conc_request_id))
WHERE NAME NOT IN ('Phase', 'Status')) "Request_Details",
CURSOR(SELECT name "Name",
value "Value"
FROM table(gefnd_common_utils_pkg.get_conc_req_params(fnd_global.conc_request_id))) "Parameters"
FROM dual;
l_xml := gefnd_report_util_pkg.get_query_output(l_rc);
--
IF l_rc%ISOPEN THEN
CLOSE l_rc;
END IF;
--
IF l_xml IS NOT NULL THEN
IF xmltype(l_xml).existsNode('//Messages_ROW') > 0 THEN
l_clob := xmltype(l_xml).transform(xmltype(gefnd_report_util_pkg.get_template(gefnd_report_util_pkg.g_template_xml2html))).getclobval();
--
IF l_clob IS NOT NULL THEN
gefnd_log_pkg.debug('Sending email to ' || lv_email_address);
gefnd_report_util_pkg.send_mail(p_from_label => 'ChRM Claim Autopay Notifications',
p_to_addr => lv_email_address,
p_subject => ltrim(gefnd_report_util_pkg.get_db_name || ' ChRM Claim Autopay Messages'),
p_body_html => l_clob,
x_resultout => lv_resultout,
x_err_msg => lv_err_msg);
g_message_tbl.delete;
END IF;
ELSE
gefnd_log_pkg.debug('No messages logged. Not sending e-mail.');
END IF;
END IF;
--
END IF;
gefnd_log_pkg.pop(lv_func);
EXCEPTION
WHEN OTHERS THEN
IF l_rc%ISOPEN THEN
CLOSE l_rc;
END IF;
gefnd_log_pkg.unexpected('Unexpected error occured while sending report...');
gefnd_log_pkg.pop(lv_func);
END send_report;
/**
* Used to get the last paid date for updating in SP,CTP,Offer
*/
FUNCTION get_paid_date(p_autopay_frequency_unit VARCHAR2,
p_autopay_frequency NUMBER,
p_autopay_date DATE) RETURN DATE IS
l_paid_date DATE;
CURSOR c_get_freq_day IS
--SELECT MAX(end_date) FROM ozf_time_day WHERE end_date <= p_autopay_date;
SELECT end_date
FROM (SELECT RANK () OVER(ORDER BY end_date DESC) RNK,
end_date
FROM ozf_time_day
WHERE end_date <= nvl(p_autopay_date, sysdate)
GROUP BY end_date)
WHERE rnk = NVL(p_autopay_frequency, 1);
CURSOR c_get_freq_week IS
--SELECT MAX(end_date) FROM ozf_time_week WHERE end_date <= p_autopay_date;
SELECT end_date
FROM (SELECT RANK () OVER(ORDER BY end_date DESC) RNK,
end_date
FROM ozf_time_week
WHERE end_date <= nvl(p_autopay_date, sysdate)
GROUP BY end_date)
WHERE rnk = NVL(p_autopay_frequency, 1);
CURSOR c_get_freq_month IS
--SELECT MAX(end_date) FROM ozf_time_ent_period WHERE end_date <= p_autopay_date;
/*SELECT end_date
FROM (SELECT RANK () OVER(ORDER BY end_date DESC) RNK,
end_date
FROM ozf_time_ent_period
WHERE end_date <= nvl(p_autopay_date, SYSDATE)
GROUP BY end_date)
WHERE rnk = NVL(p_autopay_frequency, 1);*/
SELECT CASE WHEN p_autopay_frequency between 1 and 6 THEN
(SELECT end_date
FROM (SELECT rank() over(ORDER BY end_date DESC) rnk,
end_date,
ceil(extract(MONTH FROM end_date) / (p_autopay_frequency)) hy
FROM ozf_time_ent_period
WHERE end_date <= to_date(p_autopay_date)
AND ceil(extract(MONTH FROM end_date) / (p_autopay_frequency)) <> ceil(extract(MONTH FROM to_date(p_autopay_date)) / (p_autopay_frequency))
ORDER BY end_date DESC)
WHERE rnk = 1)
WHEN p_autopay_frequency between 7 and 12 THEN
(SELECT MAX(end_date)
FROM (SELECT to_number(to_char(end_date,'MM')) RNK,
end_date
FROM ozf_time_ent_period
WHERE end_date <= nvl(p_autopay_date, SYSDATE)
ORDER BY end_date desc)
WHERE rnk = NVL(p_autopay_frequency, 1))
END as END_DATE from dual; --month query modified as per ver-1.5
CURSOR c_get_freq_qtr IS
--SELECT MAX(end_date) FROM ozf_time_ent_qtr WHERE end_date <= p_autopay_date;
/*SELECT end_date
FROM (SELECT RANK () OVER(ORDER BY end_date DESC) RNK,
end_date
FROM ozf_time_ent_qtr
WHERE end_date <= SYSDATE
GROUP BY end_date)
WHERE rnk = p_autopay_frequency;*/
SELECT end_date
FROM (SELECT rank() over(ORDER BY end_date DESC) rnk,
end_date,
ceil(extract(MONTH FROM end_date) / (3 * p_autopay_frequency)) hy
FROM ozf_time_ent_qtr
WHERE end_date <= to_date(p_autopay_date)
AND ceil(extract(MONTH FROM end_date) / (3 * p_autopay_frequency)) <> ceil(extract(MONTH FROM to_date(p_autopay_date)) / (3 * p_autopay_frequency))
ORDER BY end_date DESC)
WHERE rnk = 1; -- quarter query modified as per ver-1.5
CURSOR c_get_freq_year IS
SELECT MAX(end_date) FROM ozf_time_ent_year WHERE end_date <= p_autopay_date;
BEGIN
IF p_autopay_frequency_unit = 'DAYS' THEN
OPEN c_get_freq_day;
FETCH c_get_freq_day
INTO l_paid_date;
CLOSE c_get_freq_day;
ELSIF p_autopay_frequency_unit = 'WEEKS' THEN
OPEN c_get_freq_week;
FETCH c_get_freq_week
INTO l_paid_date;
CLOSE c_get_freq_week;
ELSIF p_autopay_frequency_unit = 'MONTHS' THEN
OPEN c_get_freq_month;
FETCH c_get_freq_month
INTO l_paid_date;
CLOSE c_get_freq_month;
ELSIF p_autopay_frequency_unit = 'QUARTERS' THEN
OPEN c_get_freq_qtr;
FETCH c_get_freq_qtr
INTO l_paid_date;
CLOSE c_get_freq_qtr;
ELSIF p_autopay_frequency_unit = 'YEAR' THEN
OPEN c_get_freq_year;
FETCH c_get_freq_year
INTO l_paid_date;
CLOSE c_get_freq_year;
END IF;
RETURN l_paid_date;
END get_paid_date;
----------------------------------------------------------------------------------------
--Function chk_global_offer
--Input : Plan ID
--Purpose : If Global offer and Freq type is 'O' then update last paid date only in case
--of the org ID not passed as input parameter
----------------------------------------------------------------------------------------
FUNCTION chk_global_offer(p_plan_id IN NUMBER) RETURN VARCHAR2 IS
offer_category VARCHAR2(1) := 'N';
l_org_id NUMBER;
CURSOR c_get_org_id(cv_paln_id NUMBER) IS
SELECT org_id FROM ozf_offers WHERE qp_list_header_id = cv_paln_id;
BEGIN
OPEN c_get_org_id(p_plan_id);
FETCH c_get_org_id INTO l_org_id;
IF l_org_id IS NULL THEN
offer_category := 'G';
ELSE
offer_category := 'N';
END IF;
RETURN offer_category;
END;
-------------------------------------------------------------------------------
--PROCEDURE get_start_end_date
--12.2.6 Autopay ER
-- to get the start and End date for mentioned period type and value
-- Based on the input parameters period type and value,the start and End DATE
-- are derived from the time structure tables.
-------------------------------------------------------------------------------
PROCEDURE get_start_end_date(p_period_type IN VARCHAR2,
p_period_value IN VARCHAR2,
x_start_date OUT NOCOPY DATE,
x_end_date OUT NOCOPY DATE,
x_return_status OUT NOCOPY VARCHAR2) IS
l_start_date DATE;
l_end_date DATE;
BEGIN
x_return_status := fnd_api.g_ret_sts_success;
--
IF p_period_type = 'DAYS' THEN
SELECT start_date,
end_date
INTO l_start_date,
l_end_date
FROM ozf_time_day
WHERE report_date = p_period_value;
ELSIF p_period_type = 'WEEKS' THEN
SELECT start_date,
end_date
INTO l_start_date,
l_end_date
FROM ozf_time_week
WHERE NAME = p_period_value;
ELSIF p_period_type = 'MONTHS' THEN
SELECT start_date,
end_date
INTO l_start_date,
l_end_date
FROM ozf_time_ent_period
WHERE NAME = p_period_value;
ELSIF p_period_type = 'QUARTERS' THEN
SELECT start_date,
end_date
INTO l_start_date,
l_end_date
FROM ozf_time_ent_qtr
WHERE NAME = p_period_value;
ELSIF p_period_type = 'YEAR' THEN
SELECT start_date,
end_date
INTO l_start_date,
l_end_date
FROM ozf_time_ent_year
WHERE NAME = p_period_value;
END IF;
x_start_date := l_start_date;
x_end_date := l_end_date;
EXCEPTION
WHEN OTHERS THEN
x_start_date := NULL;
x_end_date := NULL;
x_return_status := fnd_api.g_ret_sts_unexp_error;
END get_start_end_date;
--
--Version 1.2 changes start
/**
* Procedure to check whether the offer is eligible for claim processing
*/
FUNCTION is_offer_eligible(p_list_header_id IN NUMBER) RETURN VARCHAR2 IS
--Cursor to check if offer has missing documentation
CURSOR c_offer_cur IS
SELECT offer_id
FROM ozf_offers oo
WHERE qp_list_header_id = p_list_header_id
AND EXISTS (SELECT attribute1
FROM fnd_lookup_values_vl flv
WHERE flv.lookup_type = 'GEOZF_CLAIM_NOTE_CHECK_APL_LT'
AND flv.enabled_flag = 'Y'
AND flv.tag = 'NOTE_TYPE'
AND SYSDATE BETWEEN nvl(flv.start_date_active, SYSDATE - 1) AND nvl(flv.end_date_active, SYSDATE + 1)
MINUS
SELECT flv.attribute1
FROM jtf_notes_b jn,
fnd_lookup_values_vl flv
WHERE jn.source_object_id = oo.qp_list_header_id
AND jn.source_object_code = 'AMS_OFFR'
AND flv.lookup_type = 'GEOZF_CLAIM_NOTE_CHECK_APL_LT'
AND flv.enabled_flag = 'Y'
AND flv.tag = 'NOTE_TYPE'
AND SYSDATE BETWEEN nvl(flv.start_date_active, SYSDATE - 1) AND nvl(flv.end_date_active, SYSDATE + 1)
AND flv.lookup_code = jn.note_type)
UNION ALL
SELECT oo.offer_id
FROM ozf_offers oo,
ams_user_statuses_vl sts,
fnd_lookup_values_vl flv
WHERE oo.qp_list_header_id = p_list_header_id
AND oo.user_status_id = sts.user_status_id
AND sts.system_status_type = 'OZF_OFFER_STATUS'
AND sts.name = flv.meaning
AND flv.lookup_type = 'GEOZF_CLAIM_NOTE_CHECK_APL_LT'
AND flv.enabled_flag = 'Y'
AND flv.tag = 'OFFER_STATUS'
AND SYSDATE BETWEEN NVL(flv.start_date_active, SYSDATE - 1) AND NVL(flv.end_date_active, SYSDATE + 1);
l_rec c_offer_cur%ROWTYPE;
l_end_date DATE;
l_temp NUMBER;
BEGIN
/*OPEN c_offer_cur;
FETCH c_offer_cur INTO l_rec;
CLOSE c_offer_cur;*/--commented as per ver 1.7
--
/*IF l_rec.offer_id IS NOT NULL THEN
add_message(p_message_text => 'Offer documentation is missing',
p_list_header_id => p_list_header_id);
RETURN 'N';
ELSE*/--commented as per ver 1.7
SELECT TRUNC(NVL(end_date_active, SYSDATE + 1)) end_date
INTO l_end_date
FROM qp_list_headers
WHERE list_header_id = p_list_header_id;
--
--If offer end date is in current or future year, return Y
IF (TO_NUMBER(TO_CHAR(l_end_date, 'YYYY')) >= TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY'))
or (TO_NUMBER(TO_CHAR(SYSDATE, 'MM')) = 01 and TO_NUMBER(TO_CHAR(l_end_date, 'YYYY')) >= TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY'))-1)) -- V2.0
THEN
RETURN 'Y';
ELSE
--If offer end date is in open GL period, return Y
SELECT COUNT(1)
INTO l_temp
FROM gl_period_statuses gps,
ozf_sys_parameters_all osp
WHERE gps.application_id = decode(nvl(fnd_profile.value('OZF_CHANNEL_PERIOD_MGMT'), 'N'), 'Y', 682, 101)
AND gps.set_of_books_id = osp.set_of_books_id
AND osp.org_id = fnd_profile.value('ORG_ID')
AND nvl(gps.adjustment_period_flag, 'N') = 'N'
AND gps.closing_status = 'O'
AND TRUNC(gps.start_date, 'MM') = TRUNC(l_end_date, 'MM');
--
IF NVL(l_temp, 0) > 0 THEN
RETURN 'Y';
END IF;
--
--commented as per ver 1.9
/*add_message(p_message_text => 'Offer end date (' || l_end_date || ') is in the past and not in open GL period',
p_list_header_id => p_list_header_id);*/
RETURN 'N';
END IF;
--END IF;--ver 1.7
EXCEPTION
WHEN OTHERS THEN
gefnd_log_pkg.unexpected('Unexpected error occured while checking if the offer is eligible of not. List Header Id: ' || p_list_header_id);
add_message(p_message_text => 'Unexpected error occured while checking if the offer is eligible of not: ' || SQLERRM,
p_list_header_id => p_list_header_id);
RETURN 'N';
END is_offer_eligible;
--Version 1.2 changes end
-----------------------------------------------------------------------------------
--PROCEDURE verify_frequency
--12.2.6 Autopay ER
-- Once Frequency is determined to be of type Offer or SP,then the frequency needs
-- to be verified whether met or not in the currecnt job run. This is done by
-- below procedure
-- Create claim ,Last update date and Update Last paid date details are
--returned as output from this procedure
-------------------------------------------------------------------------------
PROCEDURE verify_frequency(p_autopay_frequency IN NUMBER,
p_autopay_frequency_unit IN VARCHAR2,
p_last_paid_date IN DATE,
p_autopay_date IN DATE,
x_create_claim OUT NOCOPY VARCHAR2,
x_update_last_paid_dt OUT NOCOPY VARCHAR2,
x_paid_date OUT NOCOPY DATE,
x_return_status OUT NOCOPY VARCHAR2)
IS
l_create_claim_flag VARCHAR2(1) := 'N';
l_upd_last_pd_dt_flag VARCHAR2(1) := 'N';
l_paid_date DATE;
lv_func VARCHAR2(30) := 'VERIFY_FREQUENCY';
BEGIN
gefnd_log_pkg.push(lv_func);
gefnd_log_pkg.debug('p_autopay_frequency', p_autopay_frequency);
gefnd_log_pkg.debug('p_autopay_frequency_unit', p_autopay_frequency_unit);
gefnd_log_pkg.debug('p_last_paid_date', p_last_paid_date);
gefnd_log_pkg.debug('p_autopay_date', p_autopay_date);
--
IF p_autopay_frequency_unit = 'DAYS' THEN
IF p_autopay_date >= p_last_paid_date + p_autopay_frequency THEN
l_create_claim_flag := 'Y';
l_upd_last_pd_dt_flag := 'Y';
END IF;
ELSIF p_autopay_frequency_unit = 'WEEKS' THEN
IF p_autopay_date > p_last_paid_date + (p_autopay_frequency * 7) THEN
l_create_claim_flag := 'Y';
l_upd_last_pd_dt_flag := 'Y';
END IF;
ELSIF p_autopay_frequency_unit = 'MONTHS' THEN
IF p_autopay_date >= add_months(p_last_paid_date, p_autopay_frequency) THEN
l_create_claim_flag := 'Y';
l_upd_last_pd_dt_flag := 'Y';
END IF;
ELSIF p_autopay_frequency_unit = 'QUARTERS' THEN
IF p_autopay_date >= add_months(p_last_paid_date, p_autopay_frequency * 3) THEN
l_create_claim_flag := 'Y';
l_upd_last_pd_dt_flag := 'Y';
END IF;
ELSIF p_autopay_frequency_unit = 'YEAR' THEN
IF p_autopay_date >= add_months(p_last_paid_date, p_autopay_frequency * 12) THEN
l_create_claim_flag := 'Y';
l_upd_last_pd_dt_flag := 'Y';
END IF;
ELSE
l_create_claim_flag := 'N';
l_upd_last_pd_dt_flag := 'N';
END IF;
IF l_upd_last_pd_dt_flag = 'Y' THEN
l_paid_date := get_paid_date(p_autopay_frequency_unit, p_autopay_frequency, p_autopay_date);
END IF;
x_create_claim := l_create_claim_flag;
x_update_last_paid_dt := l_upd_last_pd_dt_flag;
x_paid_date := l_paid_date;
x_return_status := fnd_api.g_ret_sts_success;
--
gefnd_log_pkg.debug('x_update_last_paid_dt :' || x_update_last_paid_dt);
gefnd_log_pkg.pop(lv_func);
EXCEPTION
WHEN OTHERS THEN
x_return_status := fnd_api.g_ret_sts_unexp_error;
gefnd_log_pkg.unexpected('Unexpected error occured');
gefnd_log_pkg.pop(lv_func);
END;
---------------------------------------------------------------------------------------
--PROCEDURE check_SP_Frequency
--12.2.6 Autopay ER
--In Case of SP Frequency,if Frequency details are available
--The below procedure checks if Frequency is met or Not
---------------------------------------------------------------------------------------
PROCEDURE check_sp_frequency(p_org_id IN NUMBER,
p_autopay_date IN DATE,
x_paid_date OUT NOCOPY DATE,
x_create_claim OUT NOCOPY VARCHAR2,
x_update_last_paid_date OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2) IS
l_autopay_frequency NUMBER := fnd_api.g_miss_num;
l_autopay_frequency_unit VARCHAR2(30) := fnd_api.g_miss_char;
l_last_paid_date DATE := fnd_api.g_miss_date;
--OUT Parameters
l_create_claim_flag VARCHAR2(1) := 'N';
l_upd_last_pd_dt_flag VARCHAR2(1) := 'N';
l_paid_date DATE := fnd_api.g_miss_date;
l_return_status VARCHAR2(1000);
CURSOR get_sp_frequency(cv_org_id NUMBER) IS
SELECT autopay_periodicity,
autopay_periodicity_type,
last_paid_date
FROM ozf_sys_parameters_all
WHERE org_id = cv_org_id;
lv_func VARCHAR2(30) := 'CHECK_SP_FREQUENCY';
BEGIN
gefnd_log_pkg.push(lv_func);
x_return_status := fnd_api.g_ret_sts_success;
OPEN get_sp_frequency(p_org_id);
FETCH get_sp_frequency
INTO l_autopay_frequency,
l_autopay_frequency_unit,
l_last_paid_date;
IF get_sp_frequency%NOTFOUND OR l_autopay_frequency IS NULL THEN
l_create_claim_flag := 'Y';
l_upd_last_pd_dt_flag := 'N';
IF g_dbg_on THEN
gefnd_log_pkg.debug('SP Frequency Parameters');
gefnd_log_pkg.debug('Org ID:' || p_org_id);
gefnd_log_pkg.debug('Autopay Frequency :' || l_autopay_frequency);
gefnd_log_pkg.debug('Autopay Frequency Unit :' || l_autopay_frequency_unit);
gefnd_log_pkg.debug('Last Paid Date :' || l_last_paid_date);
END IF;
ELSIF l_autopay_frequency IS NOT NULL AND l_autopay_frequency_unit IS NOT NULL AND l_last_paid_date IS NOT NULL THEN
verify_frequency(l_autopay_frequency,
l_autopay_frequency_unit,
l_last_paid_date,
p_autopay_date,
l_create_claim_flag,
l_upd_last_pd_dt_flag,
l_paid_date,
l_return_status);
ELSIF l_autopay_frequency_unit IS NOT NULL AND l_autopay_frequency IS NOT NULL AND l_last_paid_date IS NULL THEN
l_create_claim_flag := 'Y';
-- l_upd_last_pd_dt_flag := 'Y';
l_paid_date := get_paid_date(l_autopay_frequency_unit, l_autopay_frequency, p_autopay_date);
ELSE
l_create_claim_flag := 'N';
l_upd_last_pd_dt_flag := 'N';
END IF;
CLOSE get_sp_frequency;
x_create_claim := l_create_claim_flag;
x_update_last_paid_date := l_upd_last_pd_dt_flag;
gefnd_log_pkg.pop(lv_func);
EXCEPTION
WHEN OTHERS THEN
CLOSE get_sp_frequency;
IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
fnd_message.set_name('OZF', 'OZF_ATPY_SP_FREQ_ERROR');
fnd_msg_pub.add;
END IF;
x_return_status := fnd_api.g_ret_sts_unexp_error;
gefnd_log_pkg.unexpected('Unexpected error occured');
gefnd_log_pkg.pop(lv_func);
END;
---------------------------------------------------------------------------
--PROCEDURE check_offer_budget_frequency
--12.2.6 Autopay ER
--To check if the passed offer or budget.If Frequency met for each offer
--in case of budget,then mark create_claim Y.
------------------------------------------------------------------------------
PROCEDURE check_offer_budget_frequency(p_fund_id IN NUMBER,
p_plan_id IN NUMBER,
p_autopay_date IN DATE,
x_freq_tbl OUT NOCOPY frequency_tbl_typ,
x_return_status OUT NOCOPY VARCHAR2) IS
l_autopay_frequency NUMBER := fnd_api.g_miss_num;
l_autopay_frequency_unit VARCHAR2(30) := fnd_api.g_miss_char;
l_last_paid_date DATE := fnd_api.g_miss_date;
l_create_claim_flag VARCHAR2(1) := 'N';
l_upd_last_pd_dt_flag VARCHAR2(1) := 'N';
l_return_status VARCHAR2(1000);
j NUMBER := 0;
TYPE plan_id_rec_typ IS RECORD(
plan_id NUMBER,
autopay_frequency NUMBER,
autopay_frequency_unit VARCHAR2(30),
last_paid_date DATE); --Normal or Global offer
TYPE plan_id_tbl_typ IS TABLE OF plan_id_rec_typ INDEX BY BINARY_INTEGER;
plan_id_tbl plan_id_tbl_typ;
CURSOR get_offer_frequency(cv_plan_id NUMBER) IS
SELECT qp_list_header_id plan_id,
autopay_frequency,
autopay_frequency_unit,
last_paid_date
FROM ozf_offers
WHERE qp_list_header_id = cv_plan_id;
CURSOR get_budget_offer_frequency(cv_fund_id NUMBER) IS
SELECT qp_list_header_id plan_id,
autopay_frequency,
autopay_frequency_unit,
last_paid_date
FROM ozf_offers
WHERE qp_list_header_id IN (SELECT object_id
FROM ozf_object_fund_summary
WHERE fund_id = cv_fund_id
AND object_type = 'OFFR')
AND status_code NOT IN ('DRAFT', 'PENDING', 'REJECTED', 'PENDING_ACTIVE')
AND offer_type <> 'SCAN_DATA'
AND offer_type IN ('ACCRUAL', 'LUMPSUM', 'VOLUME_OFFER');
CURSOR csr_offer_name(off_id IN NUMBER) IS
SELECT qp.description
FROM qp_list_headers_vl qp
WHERE qp.list_header_id = off_id;
-- Ver 1.4 ERPOEP-44805 Start
CURSOR offer_code_cur(off_id IN NUMBER) IS
SELECT oo.offer_code offer_code
FROM ozf_offers oo,
qp_list_headers_vl qp
WHERE qp.list_header_id = off_id
AND oo.qp_list_header_id = qp.list_header_id
AND ROWNUM = 1;
lv_offer_code VARCHAR2(1000);
-- Ver 1.4 ERPOEP-44805 End
l_plan_name VARCHAR2(100);
l_paid_date DATE;
x NUMBER := 1;
BEGIN
x_return_status := fnd_api.g_ret_sts_success;
plan_id_tbl.delete;
x_freq_tbl.delete;
--
IF p_plan_id IS NOT NULL THEN
IF is_offer_eligible(p_plan_id) = 'Y' THEN --Version 1.2 changes
OPEN get_offer_frequency(p_plan_id);
FETCH get_offer_frequency BULK COLLECT INTO plan_id_tbl;
CLOSE get_offer_frequency;
END IF;
ELSIF p_fund_id IS NOT NULL AND p_plan_id IS NULL THEN
OPEN get_budget_offer_frequency(p_fund_id);
FETCH get_budget_offer_frequency BULK COLLECT INTO plan_id_tbl;
CLOSE get_budget_offer_frequency;
END IF;
--
IF plan_id_tbl.count > 0 THEN
FOR i IN nvl(plan_id_tbl.first, 1) .. nvl(plan_id_tbl.last, 0)
LOOP
--
gefnd_log_pkg.debug('plan_id', plan_id_tbl(i).plan_id);
gefnd_log_pkg.debug('autopay_frequency', plan_id_tbl(i).autopay_frequency);
gefnd_log_pkg.debug('autopay_frequency_unit', plan_id_tbl(i).autopay_frequency_unit);
gefnd_log_pkg.debug('last_paid_date', plan_id_tbl(i).last_paid_date);
--
IF plan_id_tbl(i).autopay_frequency IS NULL OR plan_id_tbl(i).autopay_frequency_unit IS NULL THEN
j := j + 1;
x_freq_tbl(j).create_claim_flag := 'Y';
x_freq_tbl(j).plan_id := plan_id_tbl(i).plan_id;
x_freq_tbl(j).upd_last_pd_dt_flag := 'N';
x_freq_tbl(j).frequency_unit := plan_id_tbl(i).autopay_frequency_unit;
x_freq_tbl(j).frequency := plan_id_tbl(i).autopay_frequency;
ELSIF plan_id_tbl(i).autopay_frequency IS NOT NULL AND
plan_id_tbl(i).autopay_frequency_unit IS NOT NULL AND
plan_id_tbl(i).last_paid_date IS NOT NULL THEN
verify_frequency(p_autopay_frequency => plan_id_tbl(i).autopay_frequency,
p_autopay_frequency_unit => plan_id_tbl(i).autopay_frequency_unit,
p_last_paid_date => plan_id_tbl(i).last_paid_date,
p_autopay_date => p_autopay_date,
x_create_claim => l_create_claim_flag,
x_update_last_paid_dt => l_upd_last_pd_dt_flag,
x_paid_date => l_paid_date,
x_return_status => l_return_status);
--
gefnd_log_pkg.debug('Offer Frequency Parameters');
gefnd_log_pkg.debug('Create Claim:' || l_create_claim_flag);
gefnd_log_pkg.debug('Update last Paid Date :' || l_upd_last_pd_dt_flag);
gefnd_log_pkg.debug('New Last Paid Date :' || l_paid_date);
gefnd_log_pkg.debug('Plan ID :' || plan_id_tbl(i).plan_id);
--
IF l_create_claim_flag = 'Y' THEN
j := j + 1;
x_freq_tbl(j).create_claim_flag := l_create_claim_flag;
x_freq_tbl(j).plan_id := plan_id_tbl(i).plan_id;
x_freq_tbl(j).upd_last_pd_dt_flag := 'N';
x_freq_tbl(j).paid_date := get_paid_date(l_autopay_frequency_unit, l_autopay_frequency, p_autopay_date);
x_freq_tbl(j).frequency_unit := plan_id_tbl(i).autopay_frequency_unit;
x_freq_tbl(j).frequency := plan_id_tbl(i).autopay_frequency;
ELSIF l_create_claim_flag = 'N' THEN
OPEN csr_offer_name(plan_id_tbl(i).plan_id);
FETCH csr_offer_name INTO l_plan_name;
CLOSE csr_offer_name;
fnd_file.put_line(fnd_file.output, 'Offer frequency not met for :' || l_plan_name);
gefnd_log_pkg.debug('Offer frequency not met for :' || l_plan_name);
-- Ver 1.4 ERPOEP-44805 Start
lv_offer_code := NULL;
OPEN offer_code_cur(plan_id_tbl(i).plan_id);
FETCH offer_code_cur INTO lv_offer_code;
CLOSE offer_code_cur;
--utl_file.put_line (gv_offer_claim_err_utl,RPAD(lv_offer_code,14,' ')||'|'||RPAD(l_plan_name,100,' ')||'|'||'Offer Frequency Not met');
add_message(p_message_text => 'Offer frequency not met',
p_offer_code => lv_offer_code,
p_offer_name => l_plan_name);
gv_offer_mail_flag := 'Y';
-- Ver 1.4 ERPOEP-44805 End
END IF;
--
IF l_upd_last_pd_dt_flag = 'Y' THEN
x_freq_tbl(j).paid_date := get_paid_date(plan_id_tbl(i).autopay_frequency_unit, plan_id_tbl(i).autopay_frequency, p_autopay_date);
END IF;
ELSIF plan_id_tbl(i).autopay_frequency IS NOT NULL AND
plan_id_tbl(i).autopay_frequency_unit IS NOT NULL AND
plan_id_tbl(i).last_paid_date IS NULL THEN
j := j + 1;
x_freq_tbl(j).create_claim_flag := 'Y';
x_freq_tbl(j).plan_id := plan_id_tbl(i).plan_id;
x_freq_tbl(j).upd_last_pd_dt_flag := 'N';
x_freq_tbl(j).paid_date := get_paid_date(plan_id_tbl(i).autopay_frequency_unit, plan_id_tbl(i).autopay_frequency, p_autopay_date);
x_freq_tbl(j).frequency_unit := plan_id_tbl(i).autopay_frequency_unit;
x_freq_tbl(j).frequency := plan_id_tbl(i).autopay_frequency;
END IF;
END LOOP;
END IF;
END;
/**
* Based on input parameters, program determines if frequency, period should be considered for claim creation or not
* S - SP Frequency
* C - CTP Frequency
* O - Offer frequency
* P - Period type and value
* N - No frequency
*
*/
PROCEDURE get_frequency_period(p_org_id IN NUMBER DEFAULT NULL,
p_customer_id IN NUMBER := NULL,
p_fund_id IN NUMBER := NULL,
p_plan_id IN NUMBER := NULL,
p_product_category_id IN NUMBER := NULL,
p_product_id IN NUMBER := NULL,
p_plan_type IN VARCHAR2,
p_offer_type IN VARCHAR2,
p_start_date IN VARCHAR2,
p_end_date IN VARCHAR2,
p_period_type IN VARCHAR2,
p_period_value IN VARCHAR2,
x_frequency_period OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2) IS
l_frequency_period VARCHAR2(1) := 'N';
l_plan_org_id NUMBER := NULL;
--
CURSOR c_get_plan_org_id IS
SELECT org_id
FROM ozf_offers
WHERE qp_list_header_id = p_plan_id;
BEGIN
IF p_plan_id IS NOT NULL THEN
OPEN c_get_plan_org_id;
FETCH c_get_plan_org_id INTO l_plan_org_id;
CLOSE c_get_plan_org_id;
END IF;
--
IF p_product_category_id IS NOT NULL OR p_product_id IS NOT NULL OR p_start_date IS NOT NULL OR
p_end_date IS NOT NULL OR (p_period_type IS NOT NULL AND p_period_value IS NOT NULL) OR
(p_customer_id IS NOT NULL AND (p_fund_id IS NOT NULL OR (p_plan_id IS NULL AND p_offer_type IS NOT NULL))) OR
p_plan_type = 'PRIC' OR (p_offer_type IS NOT NULL AND p_plan_id IS NULL) OR
(p_offer_type IS NOT NULL AND p_offer_type NOT IN ('ACCRUAL', 'LUMPSUM', 'VOLUME_OFFER')) THEN
l_frequency_period := 'N';
ELSIF p_customer_id IS NOT NULL THEN
l_frequency_period := 'C';
ELSIF p_customer_id IS NULL AND
(p_fund_id IS NOT NULL OR p_plan_id IS NOT NULL OR
((p_offer_type NOT IN ('ACCRUAL', 'LUMPSUM', 'VOLUME_OFFER') AND p_plan_id IS NOT NULL))) THEN
l_frequency_period := 'O';
ELSIF p_customer_id IS NULL AND p_fund_id IS NULL AND p_plan_id IS NULL THEN
l_frequency_period := 'S';
ELSE
l_frequency_period := 'N';
END IF;
--
IF p_start_date IS NOT NULL OR p_end_date IS NOT NULL OR (p_period_type IS NOT NULL AND p_period_value IS NOT NULL) THEN
l_frequency_period := 'P';
END IF;
x_frequency_period := l_frequency_period;
x_return_status := fnd_api.g_ret_sts_success;
EXCEPTION
WHEN OTHERS THEN
fnd_message.set_name('OZF', 'OZF_INVALID_FREQ_PERIOD');
fnd_msg_pub.add;
x_return_status := fnd_api.g_ret_sts_unexp_error;
END;
/**
* Validates customer info
*/
PROCEDURE validate_customer_info(p_customer_info IN g_customer_info_csr%ROWTYPE,
x_return_status OUT NOCOPY VARCHAR2) IS
CURSOR csr_cust_name(cv_cust_account_id IN NUMBER) IS
SELECT concat(concat(party.party_name, ' ('), concat(ca.account_number, ') '))
FROM hz_cust_accounts ca,
hz_parties party
WHERE ca.party_id = party.party_id
AND ca.cust_account_id = cv_cust_account_id;
--Added cursor for Bug 14757221 , to check if both vendor and vendor site is active
CURSOR csr_active_vendor(cv_vendor_id IN NUMBER,
cv_vendor_site_id IN NUMBER) IS
SELECT 1
FROM po_vendors pv,
po_vendor_sites pvs
WHERE pv.vendor_id = pvs.vendor_id
AND (SYSDATE BETWEEN nvl(pv.start_date_active, SYSDATE - 1) AND nvl(pv.end_date_active, SYSDATE + 1))
AND (pvs.inactive_date IS NULL OR pvs.inactive_date > trunc(SYSDATE))
AND pv.vendor_id = cv_vendor_id
AND pvs.vendor_site_id = cv_vendor_site_id;
l_cust_account_id NUMBER := p_customer_info.cust_account_id;
l_cust_name_num VARCHAR2(390);
l_active_vendor_num NUMBER; --Bug 14757221
BEGIN
-- Initialize API return status to sucess
x_return_status := fnd_api.g_ret_sts_success;
IF p_customer_info.claim_currency IS NULL THEN
IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
OPEN csr_cust_name(l_cust_account_id);
FETCH csr_cust_name
INTO l_cust_name_num;
CLOSE csr_cust_name;
fnd_message.set_name('OZF', 'OZF_CLAIM_ATPY_CURRENCY_MISS');
fnd_message.set_token('ID', l_cust_name_num);
fnd_msg_pub.add;
END IF;
x_return_status := fnd_api.g_ret_sts_unexp_error;
END IF;
--
IF (p_customer_info.payment_method IS NULL AND g_open_claim_status = 'N') THEN
IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
OPEN csr_cust_name(l_cust_account_id);
FETCH csr_cust_name
INTO l_cust_name_num;
CLOSE csr_cust_name;
fnd_message.set_name('OZF', 'OZF_CLAIM_ATPY_PYMTHD_MISS');
fnd_message.set_token('ID', l_cust_name_num);
fnd_msg_pub.add;
END IF;
x_return_status := fnd_api.g_ret_sts_unexp_error;
END IF;
--
IF (p_customer_info.payment_method = 'CHECK' AND g_open_claim_status = 'N') THEN
IF p_customer_info.vendor_id IS NULL OR p_customer_info.vendor_site_id IS NULL THEN
IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
OPEN csr_cust_name(l_cust_account_id);
FETCH csr_cust_name
INTO l_cust_name_num;
CLOSE csr_cust_name;
fnd_message.set_name('OZF', 'OZF_CLAIM_ATPY_VENDOR_MISS');
fnd_message.set_token('ID', l_cust_name_num);
fnd_msg_pub.add;
END IF;
x_return_status := fnd_api.g_ret_sts_unexp_error;
ELSE
OPEN csr_active_vendor(p_customer_info.vendor_id, p_customer_info.vendor_site_id);
FETCH csr_active_vendor
INTO l_active_vendor_num;
CLOSE csr_active_vendor;
IF l_active_vendor_num IS NULL THEN
IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
OPEN csr_cust_name(l_cust_account_id);
FETCH csr_cust_name
INTO l_cust_name_num;
CLOSE csr_cust_name;
fnd_message.set_name('OZF', 'OZF_TRADE_VENDOR_INACTIVE');
fnd_message.set_token('ID', l_cust_name_num);
fnd_msg_pub.add;
END IF;
x_return_status := fnd_api.g_ret_sts_unexp_error;
END IF;
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
x_return_status := fnd_api.g_ret_sts_unexp_error;
IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
fnd_message.set_name('OZF', 'OZF_CLAIM_ATPY_CUSTOMER_ERR');
fnd_msg_pub.add;
END IF;
END validate_customer_info;
--
--Version 1.3 changes start
/**
* Generates credit memo notes based on autopay frequency for a given offer
*/
FUNCTION get_memo_notes(p_autopay_frequency_unit IN VARCHAR2,
p_autopay_frequency IN NUMBER) RETURN VARCHAR2 IS
l_notes VARCHAR2(30);
BEGIN
SELECT CASE p_autopay_frequency_unit
WHEN 'MONTHS' THEN
CASE
WHEN p_autopay_frequency > 1 THEN
'(' || TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE, 'MON'), -1 * p_autopay_frequency), 'Mon') || '-' || TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE, 'MON'), -1), 'Mon') || ')'
WHEN p_autopay_frequency = 1 THEN
TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE, 'MON'), -1), 'Mon')
END
WHEN 'QUARTERS' THEN
CASE
WHEN p_autopay_frequency > 1 THEN
'(' || TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE, 'MON'), -1 * (p_autopay_frequency * 3)), 'Q') || 'Q-' || TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE, 'Q'), -1), 'Q') || 'Q' || ')'
WHEN p_autopay_frequency = 1 THEN
TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE, 'Q'), -1), 'Q') || 'Q'
END
ELSE NULL
END
INTO l_notes
FROM dual;
--
RETURN TRIM(l_notes);
EXCEPTION
WHEN OTHERS THEN
RETURN TRIM(l_notes);
END get_memo_notes;
--Version 1.3 changes end
---------------------------------------------------------------------
-- PROCEDURE
-- create_claim_for_cust
--
-- PURPOSE
-- This procedure creates a claim and its lines for a customer based on the utilization table.
-- It will then settle it based on different payment method.
--
-- PARAMETERS
-- p_customer_info IN g_customer_info_csr%rowtype
-- p_amount IN number,
-- p_mode IN varchar2
-- p_auto_reason_code_id IN number
-- p_auto_claim_type_id IN number
-- p_autopay_periodicity IN number
-- p_autopay_periodicity_type IN VARCHAR2
-- p_offer_payment_method IN VARCHAR2
-- p_funds_util_flt IN OZF_Claim_Accrual_PVT.funds_util_flt_type
-- x_return_status OUT VARCHAR2
--
-- NOTES
---------------------------------------------------------------------
PROCEDURE create_claim_for_cust(p_customer_info IN g_customer_info_csr%ROWTYPE,
p_amount IN NUMBER,
p_mode IN VARCHAR2,
p_set_of_books_id IN NUMBER,
p_auto_reason_code_id IN NUMBER,
p_auto_claim_type_id IN NUMBER,
p_autopay_periodicity IN NUMBER,
p_autopay_periodicity_type IN VARCHAR2,
p_offer_payment_method IN VARCHAR2,
p_sys_autopay_flag IN VARCHAR2,
p_sys_thres_amt IN NUMBER,
p_sys_thres_curr IN VARCHAR2,
p_sys_pay_method IN VARCHAR2,
p_funds_util_flt IN ozf_claim_accrual_pvt.funds_util_flt_type,
p_plan_id IN NUMBER,
p_bill_to_site_use_id IN NUMBER,
x_claim_id OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2) IS
l_amount NUMBER := p_amount;
l_cust_account_id NUMBER;
l_last_pay_date DATE;
l_claim_id NUMBER;
l_claim_rec ozf_claim_pvt.claim_rec_type;
l_claim_settle_rec ozf_claim_pvt.claim_rec_type;
l_funds_util_flt ozf_claim_accrual_pvt.funds_util_flt_type := p_funds_util_flt;
l_plan_type VARCHAR2(30);
ln_prty_id NUMBER;
ln_plan_id NUMBER;
ln_cutacnt_id NUMBER;
ln_cnsl_amt NUMBER;
ln_fund_id NUMBER;
ln_offer_id NUMBER;
ln_list_header_id NUMBER;
ln_req_cnt NUMBER;
ln_clm_amount NUMBER;
ln_acct_id NUMBER;
ln_bsite_use_id NUMBER;
l_to_amount NUMBER;
l_cust_name_num VARCHAR2(390);
l_funds_util_pub_tbl OZF_Claim_Accrual_PVT.funds_util_flt_type;
--
CURSOR csr_cust_name(cv_cust_account_id IN NUMBER) IS
SELECT concat(concat(party.party_name, ' ('), concat(ca.account_number, ') '))
FROM hz_cust_accounts ca,
hz_parties party
WHERE ca.party_id = party.party_id
AND ca.cust_account_id = cv_cust_account_id;
--
CURSOR csr_payto_party_cur(cv_cust_account_id IN NUMBER) IS
SELECT jrr.user_name --Version 1.2 changes
FROM hz_cust_site_uses_all hcsua,
hz_cust_acct_sites_all hcasa,
hz_cust_accounts_all hcaa,
hz_parties hp,
hz_party_sites hps,
hz_locations hl,
hz_party_sites_ext_b hpseb,
ra_salesreps_all rsa,
jtf_rs_resource_extns_vl jrr
WHERE 1 = 1
AND hcsua.site_use_code = 'BILL_TO'
AND hpseb.attr_group_id = (SELECT attr_group_id
FROM ego_attr_groups_v
WHERE attr_group_name = 'SALES_AG'
AND attr_group_type = 'HZ_PARTY_SITES_GROUP')
AND hcaa.party_id = hp.party_id
AND hcaa.cust_account_id = hcasa.cust_account_id
AND hcasa.cust_acct_site_id = hcsua.cust_acct_site_id
AND hp.party_id = hps.party_id
AND hcasa.party_site_id = hps.party_site_id
AND hps.location_id = hl.location_id
AND hpseb.party_site_id = hps.party_site_id
AND hpseb.n_ext_attr1 = rsa.salesrep_id
AND hcsua.status = 'A'
AND hcasa.status = 'A'
AND hcaa.status = 'A'
AND hps.status = 'A'
AND hp.status = 'A'
AND hcaa.cust_account_id = cv_cust_account_id
AND jrr.resource_id = rsa.resource_id;
--
CURSOR csr_delvrty_rm_cur(cv_cust_account_id IN NUMBER) IS
SELECT jrr.user_name --Version 1.2 changes
FROM hz_cust_site_uses_all hcsua,
hz_cust_acct_sites_all hcasa,
hz_cust_accounts_all hcaa,
hz_parties hp,
hz_party_sites hps,
hz_locations hl,
hz_party_sites_ext_b hpseb,
ra_salesreps_all rsa,
jtf_rs_resource_extns_vl jrr
WHERE hcsua.site_use_code = 'BILL_TO'
AND hpseb.attr_group_id = (SELECT attr_group_id
FROM ego_attr_groups_v
WHERE attr_group_name = 'SALES_AG'
AND attr_group_type = 'HZ_PARTY_SITES_GROUP')
AND hcaa.party_id = hp.party_id
AND hcaa.cust_account_id = hcasa.cust_account_id
AND hcasa.cust_acct_site_id = hcsua.cust_acct_site_id
AND hp.party_id = hps.party_id
AND hcasa.party_site_id = hps.party_site_id
AND hps.location_id = hl.location_id
AND hpseb.party_site_id = hps.party_site_id
AND hpseb.n_ext_attr3 = rsa.salesrep_id
AND hcsua.status = 'A'
AND hcasa.status = 'A'
AND hcaa.status = 'A'
AND hps.status = 'A'
AND hp.status = 'A'
AND hcaa.cust_account_id = cv_cust_account_id
AND jrr.resource_id = rsa.resource_id; --Version 1.2 changes
--
CURSOR csr_prty_cnsl_amt_cur(p_plan_id IN NUMBER) IS
SELECT SUM(a.amount) amount,
-- a.amount,
a.fund_id,
ofs.offer_id,
qph.list_header_id,
b.party_id,
c.cust_account_id,
a.bill_to_site_use_id
FROM ozf_offers ofs,
ozf_funds_utilized_all_b a,
hz_parties b,
hz_cust_accounts c,
qp_list_headers_all_b qph
WHERE a.plan_id = p_plan_id
AND a.plan_curr_amount_remaining <> 0
AND a.cust_account_id = c.cust_account_id
AND a.cust_account_id IN (SELECT d.cust_account_id
FROM hz_cust_site_uses_all a,
hz_parties b,
hz_cust_accounts c,
hz_cust_acct_sites_all d
WHERE b.party_id = b.party_id
AND b.party_id = c.party_id
AND a.site_use_code = 'BILL_TO'
AND a.status = 'A'
AND a.cust_acct_site_id = d.cust_acct_site_id
AND c.cust_account_id = d.cust_account_id)
AND a.plan_id = ofs.qp_list_header_id
AND ofs.qp_list_header_id = qph.list_header_id
AND c.party_id = b.party_id
AND a.gl_posted_flag = 'Y'
AND a.utilization_type IN ('ACCRUAL', 'ADJUSTMENT')
AND a.amount <> 0
GROUP BY b.party_id,
a.fund_id,
ofs.offer_id,
qph.list_header_id,
c.cust_account_id,
a.bill_to_site_use_id;
--
CURSOR chrm_acntid_csr_cur(p_prty_id IN NUMBER,
p_cust_acc_id IN NUMBER) IS
SELECT d.cust_account_id
FROM hz_cust_site_uses_all a,
hz_parties b,
hz_cust_accounts c,
hz_cust_acct_sites_all d
WHERE b.party_id = p_prty_id
AND b.party_id = c.party_id
AND a.site_use_code = 'CHRM_PAY_TO'
AND a.status = 'A'
AND a.cust_acct_site_id = d.cust_acct_site_id
AND c.cust_account_id = d.cust_account_id
AND d.cust_account_id = p_cust_acc_id;
--
CURSOR csr_clm_sts_chk_cur(p_clm_amount IN NUMBER,
p_prty_id IN NUMBER,
p_plan_id IN NUMBER) IS
SELECT COUNT(*)
FROM ozf_claims_all oca,
hz_cust_accounts_all hca,
ozf_claim_lines_all ocla
WHERE oca.cust_account_id = hca.cust_account_id
AND oca.claim_id = ocla.claim_id
AND oca.status_code IN ('OPEN', 'PENDING_CLOSE')
AND oca.amount = p_clm_amount
AND hca.party_id = p_prty_id
AND ocla.activity_id = p_plan_id;
--
CURSOR claim_info_csr(p_claim_id IN NUMBER) IS
SELECT object_version_number,
sales_rep_id
FROM ozf_claims_all
WHERE claim_id = p_claim_id;
l_object_version_number NUMBER;
l_return_status VARCHAR2(1);
l_msg_data VARCHAR2(2000);
l_message_Text VARCHAR2(4000);
l_msg_count NUMBER;
l_autopay_periodicity NUMBER;
l_autopay_periodicity_type VARCHAR2(30);
lv_field_asm VARCHAR2(30);
--ln_sso_val_nm NUMBER;
lv_field_rm VARCHAR2(30);
l_ld_cust_nm VARCHAR2(30);
l_ext_val_nm VARCHAR2(30);
lv_field_reqst VARCHAR2(30);
lv_leadcust_name VARCHAR2(2000);
lv_plan_id NUMBER;
ln_sf_err_cnt NUMBER :=0; --ver 1.6
ln_note_typ_cnt NUMBER :=1; --ver 1.7
lv_requestor VARCHAR2(240); --ver 1.7
lv_inputter VARCHAR2(240); --ver 1.7
lv_sales_finance VARCHAR2(240); --ver 1.7
--ver 1.8 start
ln_req_resource_id NUMBER;
lv_requestor_mail VARCHAR2(240);
lv_inputter_mail VARCHAR2(240);
lv_sales_finance_mail VARCHAR2(240);
--
CURSOR csr_ar_system_options IS
SELECT salesrep_required_flag FROM ar_system_parameters;
--
l_salesrep_req_flag VARCHAR2(1);
l_sales_rep_id NUMBER;
l_claim_currency VARCHAR2(10);
--
CURSOR csr_claim_num(cv_claim_id IN NUMBER) IS
SELECT claim_number,
amount,
cust_billto_acct_site_id,
currency_code
FROM ozf_claims
WHERE claim_id = cv_claim_id;
--Added for bug 18402746
CURSOR csr_claim_source_defaults IS
SELECT custom_setup_id,
claim_type_id,
reason_code_id
FROM ozf_claim_def_rules
WHERE claim_class = 'CLAIM'
AND source_object_class = 'PROMOTIONAL_CLAIM'
AND enabled_flag = 'Y';
--
--Version 1.1 changes start
--Cursor to fetch offer detals
CURSOR c_offer_details_cur(p_list_header_id IN NUMBER) IS
SELECT oo.offer_code offer_code,
qp.description offer_name,
qp.attribute9 delivery_method,
qp.attribute10 deliver_to,
qp.attribute11 pay_to,
qp.attribute12 check_memo_notes,
qp.attribute15 deliver_to_sso,
is_offer_eligible(oo.qp_list_header_id) is_eligible
FROM ozf_offers oo,
qp_list_headers_vl qp,
fnd_flex_value_sets ffvs,
fnd_flex_values ffv
WHERE oo.qp_list_header_id = p_list_header_id
AND qp.list_header_id = oo.qp_list_header_id
AND ffvs.flex_value_set_name = 'GEOZF_PAY_TO_APL_VS'
AND ffvs.flex_value_set_id = ffv.flex_value_set_id
AND ffv.enabled_flag = 'Y'
AND SYSDATE BETWEEN nvl(ffv.start_date_active, SYSDATE - 1) AND nvl(ffv.end_date_active, SYSDATE + 1)
AND qp.list_header_id = oo.qp_list_header_id
AND ffv.flex_value = qp.attribute11;
--
--Cursor to fetch claim type, owner details
/*CURSOR c_claim_type_cur(p_cust_account_id IN NUMBER) IS
SELECT 1 seq,
fl.lookup_code sales_channel_code,
ajre.resource_id owner_id,
oct.claim_type_id
FROM fnd_lookup_values_vl fl,
hz_cust_accounts hca,
ozf_claim_types_all_vl oct,
ams_jtf_rs_emp_v ajre
WHERE fl.lookup_type = 'GEOZF_AUTOPAY_CLAIM_OWNER_LT'
AND fl.enabled_flag = 'Y'
AND SYSDATE BETWEEN fl.start_date_active AND NVL(fl.end_date_active, SYSDATE)
AND fl.lookup_code = hca.sales_channel_code
AND ajre.employee_number = fl.tag
AND oct.name = fl.description
AND hca.cust_account_id = p_cust_account_id
UNION ALL
SELECT 2 seq,
fl.lookup_code sales_channel_code,
ajre.resource_id owner_id,
oct.claim_type_id
FROM fnd_lookup_values_vl fl,
ozf_claim_types_all_vl oct,
ams_jtf_rs_emp_v ajre
WHERE fl.lookup_type = 'GEOZF_AUTOPAY_CLAIM_OWNER_LT'
AND fl.enabled_flag = 'Y'
AND SYSDATE BETWEEN fl.start_date_active AND NVL(fl.end_date_active, SYSDATE)
AND fl.lookup_code = 'DEFAULT'
AND ajre.employee_number = fl.tag
AND oct.name = fl.description
ORDER BY 1;*/--commented as per Ver-1.6
--Added below cursor for ver 1.6
CURSOR c_claim_type_cur(p_plan_id IN NUMBER) IS
SELECT 1 seq,
fl.lookup_code sales_finance_sso,
ajre.resource_id owner_id,
oct.claim_type_id,
oct.name --ver 1.7
FROM fnd_lookup_values_vl fl,
ozf_claim_types_all_vl oct,
ams_jtf_rs_emp_v ajre
WHERE fl.lookup_type = 'GEOZF_AUTOPAY_CLAIM_SF_LT'
AND fl.enabled_flag = 'Y'
AND SYSDATE BETWEEN fl.start_date_active AND NVL(fl.end_date_active, SYSDATE)
AND ajre.employee_number = fl.tag
AND oct.name = fl.description
and fl.lookup_code =
(select SOURCE_NUMBER from JTF_RS_RESOURCE_EXTNS_VL jtf, qp_list_headers_all qlh
where jtf.resource_id=qlh.attribute4 and qlh.list_header_id=p_plan_id);
--Added below cursor for ver 1.9
CURSOR c_claim_type_op_cur(p_cust_account_id IN NUMBER) IS
SELECT 1 seq,
fl.lookup_code sales_channel_code,
ajre.resource_id owner_id,
oct.claim_type_id
FROM fnd_lookup_values_vl fl,
hz_cust_accounts hca,
ozf_claim_types_all_vl oct,
ams_jtf_rs_emp_v ajre
WHERE fl.lookup_type = 'GEOZF_AUTOPAY_CLAIM_OWNER_LT'
AND fl.enabled_flag = 'Y'
AND SYSDATE BETWEEN fl.start_date_active AND NVL(fl.end_date_active, SYSDATE)
AND fl.lookup_code = hca.sales_channel_code
AND ajre.employee_number = fl.tag
AND oct.name = fl.description
AND hca.cust_account_id = p_cust_account_id
UNION ALL
SELECT 2 seq,
fl.lookup_code sales_channel_code,
ajre.resource_id owner_id,
oct.claim_type_id
FROM fnd_lookup_values_vl fl,
ozf_claim_types_all_vl oct,
ams_jtf_rs_emp_v ajre
WHERE fl.lookup_type = 'GEOZF_AUTOPAY_CLAIM_OWNER_LT'
AND fl.enabled_flag = 'Y'
AND SYSDATE BETWEEN fl.start_date_active AND NVL(fl.end_date_active, SYSDATE)
AND fl.lookup_code = 'DEFAULT'
AND ajre.employee_number = fl.tag
AND oct.name = fl.description
ORDER BY 1;
--
l_claim_type_rec c_claim_type_cur%ROWTYPE;
l_claim_type_op_rec c_claim_type_op_cur%ROWTYPE; --ver 1.9
l_offer_rec c_offer_details_cur%ROWTYPE;
--Version 1.1 changes end
l_claim_num VARCHAR2(30);
l_claim_amt NUMBER;
l_cust_billto_acct_site_id NUMBER;
l_eligible_flag VARCHAR2(1);
l_claim_pub_rec ozf_claim_pub.claim_rec_type;
l_claim_line_pub_tbl ozf_claim_pub.claim_line_tbl_type;
l_appl_id NUMBER;
l_resp_id NUMBER;
l_user_id NUMBER;
l_org_id NUMBER;
l_x_claim_id NUMBER;
l_seqns_num NUMBER;
l_error_index NUMBER;
-- Request related variables
l_request_id NUMBER := g_request_id;
l_conc_login_id NUMBER := g_login_id;
l_program_id NUMBER := g_program_id;
l_prog_appl_id NUMBER := g_prog_appl_id;
lv_func VARCHAR2(30) := 'CREATE_CLAIM_FOR_CUST';
--Ver 1.9
ln_party_site_id NUMBER;
lv_suppress_mail VARCHAR2(240):='NO';
lv_rm_sso VARCHAR2(30);
ln_rm_res_id NUMBER;
BEGIN
gefnd_log_pkg.push(lv_func);
-- Initialize API return status to sucess
x_return_status := fnd_api.g_ret_sts_success;
gefnd_log_pkg.debug('p_amount', p_amount);
gefnd_log_pkg.debug('p_mode', p_mode);
gefnd_log_pkg.debug('p_auto_reason_code_id', p_auto_reason_code_id);
gefnd_log_pkg.debug('p_auto_claim_type_id', p_auto_claim_type_id);
gefnd_log_pkg.debug('p_autopay_periodicity', p_autopay_periodicity);
gefnd_log_pkg.debug('p_autopay_periodicity_type', p_autopay_periodicity_type);
gefnd_log_pkg.debug('p_offer_payment_method', p_offer_payment_method);
gefnd_log_pkg.debug('p_sys_autopay_flag', p_sys_autopay_flag);
gefnd_log_pkg.debug('p_sys_thres_amt', p_sys_thres_amt);
gefnd_log_pkg.debug('p_sys_thres_curr', p_sys_thres_curr);
gefnd_log_pkg.debug('p_sys_pay_method', p_sys_pay_method);
gefnd_log_pkg.debug('p_plan_id', p_plan_id);
gefnd_log_pkg.debug('p_bill_to_site_use_id', p_bill_to_site_use_id);
gefnd_log_pkg.debug('l_amount', l_amount);
-- For this customer: check whether there is a need to create a claim
-- check sum of acctd_amount from utiliztion only create claims with positvit amount
IF l_amount IS NOT NULL AND l_amount > 0 THEN
l_eligible_flag := fnd_api.g_true; -- default
IF p_mode = 'B' THEN
-- IF the mode is 'Backdated'
l_eligible_flag := fnd_api.g_true;
END IF;
--
gefnd_log_pkg.debug('p_customer_info.claim_threshold', p_customer_info.claim_threshold);
gefnd_log_pkg.debug('p_customer_info.claim_currency', p_customer_info.claim_currency);
gefnd_log_pkg.debug('l_funds_util_flt.utiz_currency_code', l_funds_util_flt.utiz_currency_code);
--
IF p_customer_info.claim_threshold IS NOT NULL AND p_customer_info.claim_currency IS NOT NULL THEN
--code to check if claim currency and cust trade profile currency is same.If different then covert the currency
IF p_customer_info.claim_currency <> l_funds_util_flt.utiz_currency_code THEN
--call convert currency procedure.
ozf_utility_pvt.convert_currency(x_return_status => l_return_status,
p_from_currency => l_funds_util_flt.utiz_currency_code,
p_to_currency => p_customer_info.claim_currency,
p_from_amount => l_amount,
x_to_amount => l_to_amount);
gefnd_log_pkg.debug('l_to_amount :' || l_to_amount);
--
IF l_to_amount >= p_customer_info.claim_threshold THEN
l_eligible_flag := fnd_api.g_true;
ELSE
l_eligible_flag := fnd_api.g_false;
END IF;
ELSE
IF l_amount >= p_customer_info.claim_threshold THEN
l_eligible_flag := fnd_api.g_true;
ELSE
l_eligible_flag := fnd_api.g_false;
END IF;
END IF;
END IF;
--
gefnd_log_pkg.debug('l_eligible_flag', l_eligible_flag);
--
IF l_eligible_flag = fnd_api.g_true THEN
l_claim_rec.claim_class := 'CLAIM';
--Defaults for autopay claims should come from the setup for 'promotional' claim source
OPEN csr_claim_source_defaults;
FETCH csr_claim_source_defaults
INTO l_claim_rec.custom_setup_id,
l_claim_rec.claim_type_id,
l_claim_rec.reason_code_id;
CLOSE csr_claim_source_defaults;
--
gefnd_log_pkg.debug('Cursor csr_claim_source_defaults values');
gefnd_log_pkg.debug(' custom_setup_id', l_claim_rec.custom_setup_id);
gefnd_log_pkg.debug(' claim_type_id', l_claim_rec.claim_type_id);
gefnd_log_pkg.debug(' reason_code_id', l_claim_rec.reason_code_id);
--
--ver 1.9 start
lv_suppress_mail:='NO';
BEGIN
select nvl(attribute13,'NO') into lv_suppress_mail from qp_list_headers_all where LIST_HEADER_ID=p_plan_id;
EXCEPTION WHEN OTHERS THEN
lv_suppress_mail:='NO';
gefnd_log_pkg.debug ('Error while getting attribute13 value for plan id: '||p_plan_id||'Error- '|| SQLERRM);
END;
--if suppress mail is yes, deriving Region manager from TCA
IF (lv_suppress_mail='YES') THEN
ln_rm_res_id := NULL;
BEGIN
select hcas.PARTY_SITE_ID into ln_party_site_id from hz_cust_acct_sites_all hcas , hz_cust_site_uses_all hcsu
where hcas.CUST_ACCT_SITE_ID=hcsu.CUST_ACCT_SITE_ID
and hcsu.SITE_USE_CODE='BILL_TO'
and hcsu.STATUS='A'
and hcas.STATUS='A'
and hcas.org_id=82
and hcas.CUST_ACCOUNT_ID=p_customer_info.cust_account_id
and rownum=1;
SELECT UNIQUE jrse.source_number,jrse.resource_id into lv_rm_sso,ln_rm_res_id
FROM jtf_rs_salesreps jrs,
jtf_rs_resource_extns jrse
WHERE 1 = 1
AND jrs.resource_id = jrse.resource_id
AND SYSDATE BETWEEN jrs.start_date_active AND nvl(jrs.end_date_active,SYSDATE + 1)
AND nvl(jrse.end_date_active,SYSDATE) >= SYSDATE
AND nvl(jrs.status,'A') = 'A'
AND jrs.org_id = 82
AND jrs.salesrep_id = jrs.salesrep_id + 0
AND jrse.resource_id = jrse.resource_id + 0
AND jrse.source_number is not null
AND jrs.salesrep_id = ( SELECT n_ext_attr3
FROM hz_party_sites_ext_b
WHERE attr_group_id = 1058
AND party_site_id = ln_party_site_id
AND party_site_id = party_site_id + 0
AND extension_id = extension_id + 0
);
gefnd_log_pkg.debug('Region Manager SSO ', lv_rm_sso);
EXCEPTION WHEN OTHERS THEN
gefnd_log_pkg.debug ('Exception or TCA RM details not found for plan id: '||p_plan_id||'Error- '|| SQLERRM);
ln_rm_res_id := NULL;
END;
END IF;
--ver 1.9 end
--Version 1.1 changes start
--OPEN c_claim_type_cur(p_customer_info.cust_account_id); --commented as per ver 1.6
IF (lv_suppress_mail='NO') THEN --added as per ver 1.9 --if suppress mail is no, claim type and owner will be derived based on sales finance
OPEN c_claim_type_cur(p_plan_id); --added as per ver 1.6
FETCH c_claim_type_cur INTO l_claim_type_rec;
CLOSE c_claim_type_cur;
--
gefnd_log_pkg.debug('p_customer_info.cust_account_id', p_customer_info.cust_account_id);
gefnd_log_pkg.debug('Cursor c_claim_type_cur values');
gefnd_log_pkg.debug(' owner_id', l_claim_type_rec.owner_id);
gefnd_log_pkg.debug(' claim_type_id', l_claim_type_rec.claim_type_id);
--
--Ver 1.6 start
IF (l_claim_type_rec.owner_id IS NULL) OR (l_claim_type_rec.claim_type_id IS NULL) THEN
l_eligible_flag := FND_API.G_FALSE;
IF(gn_iter_cnt<=1) THEN
add_message(p_message_text => 'Offer Sales Finance is not mapped to Claim Type',
p_list_header_id => p_plan_id);
gv_offer_mail_flag := 'Y';
END IF;
ln_sf_err_cnt := 1;
RETURN; --ver 1.8
END IF;
--Ver 1.6 End
l_claim_rec.owner_id := l_claim_type_rec.owner_id;
l_claim_rec.claim_type_id := nvl(l_claim_type_rec.claim_type_id, nvl(l_claim_rec.claim_type_id, p_auto_claim_type_id));
ELSE --ver-1.9 if suppress mail is yes, claim type and owner will be derived based on sales channel at account level
OPEN c_claim_type_op_cur(p_customer_info.cust_account_id);
FETCH c_claim_type_op_cur INTO l_claim_type_op_rec;
CLOSE c_claim_type_op_cur;
gefnd_log_pkg.debug('p_customer_info.cust_account_id', p_customer_info.cust_account_id);
gefnd_log_pkg.debug('Cursor l_claim_type_op_rec values');
gefnd_log_pkg.debug(' owner_id', l_claim_type_op_rec.owner_id);
gefnd_log_pkg.debug(' claim_type_id', l_claim_type_op_rec.claim_type_id);
l_claim_rec.owner_id := l_claim_type_op_rec.owner_id;
l_claim_rec.claim_type_id := nvl(l_claim_type_op_rec.claim_type_id, nvl(l_claim_rec.claim_type_id, p_auto_claim_type_id));
END IF;
l_claim_rec.reason_code_id := nvl(l_claim_rec.reason_code_id, p_auto_reason_code_id);
--Version 1.1 changes end
-- Modified for FXGL Enhancement
-- The claim currency will be the accrual currency and not the customer
-- currency. As no cross currency associtaion is supported
-- For a particular accrual in X currerncy to be associated
-- claim must also be in X currency
l_claim_rec.currency_code := l_funds_util_flt.utiz_currency_code;
l_claim_rec.cust_account_id := p_customer_info.cust_account_id;
l_claim_rec.cust_billto_acct_site_id := p_customer_info.site_use_id;
l_claim_rec.vendor_id := p_customer_info.vendor_id;
l_claim_rec.vendor_site_id := p_customer_info.vendor_site_id;
-- offer's payment method overrides trade profile
--Added G_MISS_CHAR check and assigned null to payment method in case both offer and trade profile payment methods are null or G_MISS_CHAR
IF p_offer_payment_method IS NOT NULL AND p_offer_payment_method <> fnd_api.g_miss_char THEN
l_claim_rec.payment_method := p_offer_payment_method;
ELSE
IF (p_customer_info.payment_method IS NOT NULL AND p_customer_info.payment_method <> fnd_api.g_miss_char) THEN
l_claim_rec.payment_method := p_customer_info.payment_method;
ELSIF (p_sys_pay_method IS NOT NULL AND p_sys_pay_method <> fnd_api.g_miss_char) THEN
l_claim_rec.payment_method := p_sys_pay_method;
ELSE
l_claim_rec.payment_method := NULL;
END IF;
END IF;
--
gefnd_log_pkg.debug('l_claim_rec.payment_method', l_claim_rec.payment_method);
--
l_claim_rec.created_from := 'AUTOPAY';
--
l_funds_util_flt.cust_account_id := p_customer_info.cust_account_id;
IF p_offer_payment_method IS NOT NULL THEN
l_funds_util_flt.offer_payment_method := p_offer_payment_method;
ELSE
l_funds_util_flt.offer_payment_method := 'NULL';
END IF;
-- 3634,663 Changes Code Start.
--- Start Add Condition Based on JIRA 663 by 502319950
IF p_customer_info.cust_account_id IS NOT NULL AND
l_claim_rec.vendor_id IS NULL AND
l_claim_rec.payment_method = 'CHECK' THEN
l_claim_rec.payment_method := 'CREDIT_MEMO';
END IF;
--- END Add Condition Based on JIRA 663 by 502319950
OPEN c_offer_details_cur(p_plan_id);
FETCH c_offer_details_cur INTO l_offer_rec;
CLOSE c_offer_details_cur;
--
IF l_offer_rec.offer_code IS NOT NULL AND NVL(l_offer_rec.is_eligible, 'Y') = 'N' THEN
l_eligible_flag := FND_API.G_FALSE;
--utl_file.put_line (gv_offer_claim_err_utl,RPAD(l_offer_rec.offer_code,14,' ')||'|'||RPAD(l_offer_rec.offer_name,100,' ')||'|'||'Offer is not eligible for claim creation'); -- Ver 1.4 ERPOEP-44805
add_message(p_message_text => 'Offer is not eligible for claim creation',
p_offer_code => l_offer_rec.offer_code,
p_offer_name => l_offer_rec.offer_name);
gv_offer_mail_flag := 'Y'; -- Ver 1.4 ERPOEP-44805
END IF;
--
IF l_eligible_flag = fnd_api.g_true THEN
l_claim_rec.attribute1 := l_offer_rec.delivery_method;
l_ext_val_nm := l_offer_rec.deliver_to;
l_ld_cust_nm := l_offer_rec.pay_to;
--Version 1.2 changes start
--Version 1.3 changes start
DECLARE
l_suffix VARCHAR2(20);
BEGIN
l_suffix := get_memo_notes(p_autopay_frequency_unit => p_autopay_periodicity_type,
p_autopay_frequency => p_autopay_periodicity);
IF l_suffix IS NOT NULL THEN
l_suffix := ' - ' || l_suffix;
END IF;
--
l_claim_rec.attribute3 := TRIM(substr(l_offer_rec.offer_name, 1, 150 - NVL(length(l_suffix), 0))) || l_suffix;
IF l_claim_rec.payment_method IN ('CHECK', 'EFT') THEN
l_claim_rec.customer_ref_number := TRIM(substr(l_offer_rec.offer_name, 1, 50 - NVL(length(l_suffix), 0))) || l_suffix;
ELSE
l_claim_rec.customer_ref_number := TRIM(substr(l_offer_rec.offer_name, 1, 100 - NVL(length(l_suffix), 0))) || l_suffix;
END IF;
END;
--
IF l_claim_rec.payment_method = 'EFT' THEN
l_claim_rec.attribute4 := NULL;
ELSE
l_claim_rec.attribute4 := l_offer_rec.deliver_to;
END IF;
--Version 1.3 changes end
--Version 1.2 changes end
--
gefnd_log_pkg.debug('Cursor c_offer_details_cur details...');
gefnd_log_pkg.debug(' delivery_method', l_offer_rec.delivery_method);
gefnd_log_pkg.debug(' deliver_to', l_offer_rec.deliver_to);
gefnd_log_pkg.debug(' pay_to', l_offer_rec.pay_to);
gefnd_log_pkg.debug(' offer_name', l_offer_rec.offer_name);
gefnd_log_pkg.debug(' deliver_to_sso', l_offer_rec.deliver_to_sso);
--
IF l_ext_val_nm = 'ASM' OR l_ext_val_nm = 'NAM' THEN
OPEN csr_payto_party_cur(l_claim_rec.cust_account_id);
FETCH csr_payto_party_cur INTO lv_field_asm;
CLOSE csr_payto_party_cur;
--
l_claim_rec.attribute2 := lv_field_asm;
l_claim_rec.attribute_category := 'ChRM Claims';
ELSIF l_ext_val_nm = 'Customer' THEN
l_claim_rec.attribute2 := NULL;
l_claim_rec.attribute_category := 'ChRM Claims';
ELSIF l_ext_val_nm = 'SSO' THEN
l_claim_rec.attribute2 := l_offer_rec.deliver_to_sso;--Version 1.2 changes
l_claim_rec.attribute_category := 'ChRM Claims';
ELSIF l_ext_val_nm = 'RM' THEN
OPEN csr_delvrty_rm_cur(l_claim_rec.cust_account_id);
FETCH csr_delvrty_rm_cur INTO lv_field_rm;
CLOSE csr_delvrty_rm_cur;
--
l_claim_rec.attribute2 := lv_field_rm;
l_claim_rec.attribute_category := 'ChRM Claims';
ELSIF l_ext_val_nm = 'Requestor' THEN
BEGIN
SELECT jrr.user_name --Version 1.2 changes
INTO l_claim_rec.attribute2
FROM jtf_rs_resource_extns_vl jrr,
ozf_offers ooa
WHERE jrr.resource_id = ooa.owner_id
AND ooa.qp_list_header_id = p_plan_id;
EXCEPTION
WHEN OTHERS THEN
l_claim_rec.attribute2 := NULL;
END;
l_claim_rec.attribute_category := 'ChRM Claims';
END IF;
--
IF l_claim_rec.payment_method = 'CREDIT_MEMO' THEN
l_claim_rec.attribute1 := NULL;
l_claim_rec.attribute2 := NULL;
l_claim_rec.attribute3 := NULL;
END IF;
--Adding Condtion for PAY TO AS PARYT
ln_req_cnt := -1;
ln_cnsl_amt := NULL;
ln_offer_id := NULL;
ln_list_header_id := NULL;
ln_prty_id := NULL;
ln_acct_id := NULL;
ln_bsite_use_id := NULL;
--
IF l_ld_cust_nm IS NOT NULL THEN
OPEN csr_prty_cnsl_amt_cur(p_plan_id);
FETCH csr_prty_cnsl_amt_cur
INTO ln_cnsl_amt,
ln_fund_id,
ln_offer_id,
ln_list_header_id,
ln_prty_id,
ln_acct_id,
ln_bsite_use_id;
CLOSE csr_prty_cnsl_amt_cur;
--
OPEN chrm_acntid_csr_cur(ln_prty_id, ln_acct_id);
FETCH chrm_acntid_csr_cur
INTO ln_cutacnt_id;
CLOSE chrm_acntid_csr_cur;
--
OPEN csr_clm_sts_chk_cur(ln_cnsl_amt, ln_offer_id, ln_list_header_id);
FETCH csr_clm_sts_chk_cur
INTO ln_req_cnt;
CLOSE csr_clm_sts_chk_cur;
--
gefnd_log_pkg.debug('lv_req_cnt', ln_req_cnt);
gefnd_log_pkg.debug('lv_cnsl_amt', ln_cnsl_amt);
gefnd_log_pkg.debug('ln_cutacnt_id', ln_cutacnt_id);
gefnd_log_pkg.debug('ln_bsite_use_id', ln_bsite_use_id);
gefnd_log_pkg.debug('ln_fund_id', ln_fund_id);
gefnd_log_pkg.debug('site_use_id', p_customer_info.site_use_id);
gefnd_log_pkg.debug('cust_account_id', p_customer_info.cust_account_id);
--
IF ln_req_cnt = 0 AND p_customer_info.cust_account_id IS NOT NULL THEN --AND p_customer_info.site_use_id IS NOT NULL THEN
gefnd_log_pkg.debug('Assigning Claim record attributes...');
--
l_claim_pub_rec.cust_account_id := l_claim_rec.cust_account_id;
l_claim_pub_rec.claim_class := 'CLAIM';
l_claim_pub_rec.amount := l_amount;
l_claim_pub_rec.acctd_amount := l_amount;
l_claim_pub_rec.user_status_id := 1604;
l_claim_pub_rec.claim_type_id := l_claim_rec.claim_type_id; --Version 1.1 changes
l_claim_pub_rec.reason_code_id := l_claim_rec.reason_code_id; --Version 1.1 changes
l_claim_pub_rec.org_id := fnd_global.org_id;
l_claim_pub_rec.owner_id := l_claim_rec.owner_id; --Version 1.1 changes
l_claim_pub_rec.last_update_date := SYSDATE;
l_claim_pub_rec.last_updated_by := l_user_id;
l_claim_pub_rec.creation_date := SYSDATE;
l_claim_pub_rec.created_by := l_user_id;
l_claim_pub_rec.claim_date := SYSDATE;
l_claim_pub_rec.vendor_id := l_claim_rec.vendor_id;
l_claim_pub_rec.vendor_site_id := l_claim_rec.vendor_site_id;
--l_claim_pub_rec.amount_remaining := 2;
l_claim_pub_rec.offer_id := ln_offer_id;
l_claim_pub_rec.status_code := 'OPEN';
l_claim_pub_rec.set_of_books_id := l_claim_rec.set_of_books_id;
l_claim_pub_rec.exchange_rate := 1;
l_claim_pub_rec.payment_method := l_claim_rec.payment_method;
l_claim_pub_rec.attribute_category := l_claim_rec.attribute_category;
l_claim_pub_rec.attribute1 := l_claim_rec.attribute1;
l_claim_pub_rec.attribute2 := l_claim_rec.attribute2;
l_claim_pub_rec.attribute3 := l_claim_rec.attribute3;
l_claim_pub_rec.attribute4 := l_claim_rec.attribute4; --Version 1.2 changes
l_claim_pub_rec.customer_ref_number:= l_claim_rec.customer_ref_number;
l_claim_pub_rec.attribute5 := 'Autopay';
-- Ver 1.4 ERPOEP-44805 Start
IF l_claim_rec.payment_method = 'EFT' THEN
l_claim_pub_rec.attribute4 := 'EFT';
END IF;
-- Ver 1.4 ERPOEP-44805 Start
--
gefnd_log_pkg.debug('Calling API OZF_CLAIM_PUB.create_claim...');
ozf_claim_pub.create_claim(p_api_version_number => 1.0,
p_init_msg_list => fnd_api.g_true,
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_claim_rec => l_claim_pub_rec,
p_claim_line_tbl => l_claim_line_pub_tbl,
x_claim_id => l_claim_id);
gefnd_log_pkg.debug('API Return Status', l_return_status);
gefnd_log_pkg.debug('Claim Id', l_claim_id);
--
--Version 1.1 changes start
IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
gefnd_log_pkg.debug('API Messages', l_msg_data);
--utl_file.put_line (gv_offer_claim_err_utl,RPAD(l_offer_rec.offer_code,14,' ')||'|'||RPAD(l_offer_rec.offer_name,100,' ')||'|'||'Error Details As Below'); -- Ver 1.4 ERPOEP-44805
gv_offer_mail_flag := 'Y';
l_message_text := NULL;
FOR idx IN 1 .. l_msg_count
LOOP
fnd_msg_pub.get(idx, FND_API.G_FALSE, l_msg_data, l_msg_count);
gefnd_log_pkg.debug(l_msg_data);
l_message_text := l_message_text || l_msg_data ;
--utl_file.put_line (gv_offer_claim_err_utl,l_msg_data); -- Ver 1.4 ERPOEP-44805
END LOOP;
--
add_message(p_message_text => 'Error occured while creating claim: ' || l_message_text,
p_offer_code => l_offer_rec.offer_code,
p_offer_name => l_offer_rec.offer_name);
--utl_file.put_line (gv_offer_claim_err_utl,' '); -- Ver 1.4 ERPOEP-44805
--utl_file.put_line (gv_offer_claim_err_utl,' '); -- Ver 1.4 ERPOEP-44805
x_return_status := FND_API.G_RET_STS_ERROR;
ELSE
l_claim_line_pub_tbl (1).claim_id := l_claim_id;
l_claim_line_pub_tbl (1).claim_currency_amount := l_amount;
-- Offer Assignment
l_claim_line_pub_tbl(1).activity_type := 'OFFR';
l_claim_line_pub_tbl(1).activity_id := ln_list_header_id;
l_claim_line_pub_tbl(1).offer_id := ln_offer_id;
l_claim_line_pub_tbl(1).offer_type := 'ACCRUAL';
l_claim_line_pub_tbl(1).earnings_associated_flag := 'T';
l_claim_line_pub_tbl(1).prorate_earnings_flag := 'F';
l_claim_line_pub_tbl(1).performance_complete_flag := 'F';
l_claim_line_pub_tbl(1).performance_attached_flag := 'F';
l_claim_line_pub_tbl(1).claim_currency_amount := l_claim_pub_rec.acctd_amount;
l_claim_line_pub_tbl(1).acctd_amount := l_claim_pub_rec.acctd_amount;
l_claim_line_pub_tbl(1).set_of_books_id := 2021;
l_claim_line_pub_tbl(1).org_id := fnd_global.org_id;
--added below if condition for ver 1.9 -- to populate RM in attribute2 of claim lines all table
IF (lv_suppress_mail='YES') THEN
l_claim_line_pub_tbl(1).attribute_category := 'ChRM Claim Lines';
l_claim_line_pub_tbl(1).attribute2 := ln_rm_res_id;
END IF;
--
gefnd_log_pkg.debug('Calling API OZF_CLAIM_PUB.create_claim_line_tbl...');
ozf_claim_pub.create_claim_line_tbl(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
p_validation_level => fnd_api.g_valid_level_full,
x_return_status => l_return_status,
x_msg_data => l_msg_data,
x_msg_count => l_msg_count,
p_claim_line_tbl => l_claim_line_pub_tbl,
x_error_index => l_error_index);
gefnd_log_pkg.debug('API Return Status', l_return_status);
gefnd_log_pkg.debug('Claim Line Id', l_claim_line_pub_tbl(1).claim_line_id);
--
IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
gefnd_log_pkg.debug('API Messages', l_msg_data);
l_message_text := NULL;
FOR idx IN 1 .. l_msg_count
LOOP
fnd_msg_pub.get(idx, FND_API.G_FALSE, l_msg_data, l_msg_count);
gefnd_log_pkg.debug(l_msg_data);
l_message_text := l_message_text || l_msg_data ;
END LOOP;
--
add_message(p_message_text => 'Error occured while creating claim line: ' || l_message_text,
p_offer_code => l_offer_rec.offer_code,
p_offer_name => l_offer_rec.offer_name);
x_return_status := FND_API.G_RET_STS_ERROR;
gv_offer_mail_flag := 'Y';
ELSE
FOR Rec IN (SELECT claim_line_id
FROM ozf_claim_lines_all
WHERE claim_id = l_claim_id)
LOOP
gefnd_log_pkg.debug('Calling API OZF_CLAIM_PUB.asso_accruals_to_claim_line... for line: ' || rec.claim_line_id);
ozf_claim_pub.asso_accruals_to_claim_line(p_api_version => 1.0,
p_init_msg_list => FND_API.g_false,
p_commit => FND_API.g_false,
p_validation_level => FND_API.g_valid_level_full,
x_return_status => l_return_status,
x_msg_data => l_msg_count,
x_msg_count => l_msg_data,
p_claim_line_id => rec.claim_line_id);
gefnd_log_pkg.debug('API Return Status', l_return_status);
--
IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
gefnd_log_pkg.debug('API Messages', l_msg_data);
l_message_text := NULL;
FOR idx IN 1 .. l_msg_count
LOOP
fnd_msg_pub.get(idx, FND_API.G_FALSE, l_msg_data, l_msg_count);
gefnd_log_pkg.debug(l_msg_data);
l_message_text := l_message_text || l_msg_data ;
END LOOP;
add_message(p_message_text => 'Error occured while associating claim with lines: ' || l_message_text,
p_offer_code => l_offer_rec.offer_code,
p_offer_name => l_offer_rec.offer_name);
x_return_status := FND_API.G_RET_STS_ERROR;
gv_offer_mail_flag := 'Y';
END IF;
END LOOP;
END IF;
--Version 1.1 changes end
gefnd_log_pkg.debug('API Return Status', l_return_status);
IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
gefnd_log_pkg.debug('API Messages', l_msg_data);
FOR idx IN 1 .. l_msg_count
LOOP
fnd_msg_pub.get(idx, FND_API.G_FALSE, l_msg_data, l_msg_count);
gefnd_log_pkg.debug(l_msg_data);
END LOOP;
x_return_status := FND_API.G_RET_STS_ERROR;
gefnd_log_pkg.pop(lv_func);
RETURN;
END IF;
END IF;
--Version 1.1 changes end
END IF;
ELSE
-- 3634,663 Changes Code End.
gefnd_log_pkg.debug('Calling API OZF_CLAIM_ACCRUAL_PVT.create_claim_for_accruals...');
ozf_claim_accrual_pvt.create_claim_for_accruals(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_false,
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_claim_rec => l_claim_rec,
p_funds_util_flt => l_funds_util_flt,
x_claim_id => l_claim_id);
gefnd_log_pkg.debug('API Return Status', l_return_status);
gefnd_log_pkg.debug('Claim Id', l_claim_id);
--
IF l_return_status <> fnd_api.g_ret_sts_success THEN
x_return_status := fnd_api.g_ret_sts_error;
gefnd_log_pkg.debug('API Message', l_msg_data);
gefnd_log_pkg.pop(lv_func);
RETURN;
END IF;
END IF;
--
gefnd_log_pkg.debug('l_claim_id', l_claim_id);
gefnd_log_pkg.debug('g_open_claim_status', g_open_claim_status);
--
--ver 1.7 Start: --to check if pricing_approval is there at offer level or not
BEGIN
SELECT count(*)
INTO ln_note_typ_cnt
FROM jtf_notes_b jn, fnd_lookup_values_vl flv
WHERE jn.source_object_id = p_plan_id
AND jn.source_object_code = 'AMS_OFFR'
AND flv.lookup_type = 'GEOZF_CLAIM_NOTE_CHECK_APL_LT'
AND flv.enabled_flag = 'Y'
AND flv.tag = 'NOTE_TYPE'
AND flv.ATTRIBUTE1='PRICING_APPROVAL'
AND SYSDATE BETWEEN nvl(flv.start_date_active, SYSDATE - 1) AND nvl(flv.end_date_active, SYSDATE + 1)
AND flv.lookup_code = jn.note_type;
END;
--ver 1.7 End
IF (ln_note_typ_cnt=0) THEN --ver 1.8
gn_err_cnt :=gn_err_cnt+1;
gv_clm_err_msg := gv_clm_err_msg||' '||gn_err_cnt||')'||'Pricing Approval Missing';
END IF;
IF (l_claim_id IS NOT NULL AND l_claim_id <> -1 AND g_open_claim_status = 'N') THEN
-- update to settle the claim
OPEN claim_info_csr(l_claim_id);
FETCH claim_info_csr
INTO l_object_version_number,
l_sales_rep_id;
CLOSE claim_info_csr;
--
gefnd_log_pkg.debug('l_object_version_number', l_object_version_number);
gefnd_log_pkg.debug('l_sales_rep_id', l_sales_rep_id);
--
l_claim_settle_rec.claim_id := l_claim_id;
l_claim_settle_rec.object_version_number := l_object_version_number;
--Ver 1.7 --added if condition so that if pricing_approval not there at offer level claim should be created with open status
--IF(ln_note_typ_cnt<>0) THEN
IF(gn_err_cnt=0 AND lv_suppress_mail='NO') THEN --added for ver 1.8 --added and condition related to suppress mail for ver-1.9
l_claim_settle_rec.user_status_id := to_number(ozf_utility_pvt.get_default_user_status(p_status_type => g_claim_status,
p_status_code => g_closed_status));
ELSE
l_claim_settle_rec.user_status_id := to_number(ozf_utility_pvt.get_default_user_status(p_status_type => g_claim_status,
p_status_code => g_open_status));
END IF;
------------------------------------------------------
-- Sales Credit
-- Default Sales Rep in Claims if "Requires Salesperson" in AR system options.
------------------------------------------------------
IF l_sales_rep_id IS NULL THEN
OPEN csr_ar_system_options;
FETCH csr_ar_system_options
INTO l_salesrep_req_flag;
CLOSE csr_ar_system_options;
--
IF l_salesrep_req_flag = 'Y' THEN
l_claim_settle_rec.sales_rep_id := -3; -- No Sales Credit
END IF;
END IF;
--
l_claim_settle_rec.request_id := l_request_id;
l_claim_settle_rec.program_application_id := l_prog_appl_id;
l_claim_settle_rec.program_id := l_program_id;
l_claim_settle_rec.last_update_login := l_conc_login_id;
l_claim_settle_rec.vendor_id := l_claim_rec.vendor_id;
l_claim_settle_rec.vendor_site_id := l_claim_rec.vendor_site_id;
--
gefnd_log_pkg.debug('Calling API OZF_CLAIM_PVT.update_claim...');
ozf_claim_pvt.update_claim(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_false,
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_claim => l_claim_settle_rec,
p_event => 'UPDATE',
p_mode => ozf_claim_utility_pvt.g_auto_mode,
x_object_version_number => l_object_version_number);
--
gefnd_log_pkg.debug('API Return Status', l_return_status);
gefnd_log_pkg.debug('API Message', l_msg_data);
--
IF l_return_status <> fnd_api.g_ret_sts_success THEN
FOR idx IN 1 .. l_msg_count
LOOP
fnd_msg_pub.get(idx, FND_API.G_FALSE, l_msg_data, l_msg_count);
gefnd_log_pkg.debug(l_msg_data);
END LOOP;
x_return_status := fnd_api.g_ret_sts_error;
gefnd_log_pkg.pop(lv_func);
RETURN;
END IF;
END IF;
ELSE
IF (ln_sf_err_cnt=1) THEN --added if condition as per Ver 1.6
x_claim_id :=-10;
ELSE
add_message(p_message_text => 'Offer is not eligible',
p_offer_code => l_offer_rec.offer_code,
p_offer_name => l_offer_rec.offer_name);
gefnd_log_pkg.error('Offer ' || l_offer_rec.offer_code || ' is not eligible');
END IF;
x_return_status := fnd_api.g_ret_sts_error;
END IF;
ELSE
IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
OPEN csr_cust_name(p_customer_info.cust_account_id);
FETCH csr_cust_name INTO l_cust_name_num;
CLOSE csr_cust_name;
--
fnd_message.set_name('OZF', 'OZF_CLAIM_ATPY_CUST_INELIG');
fnd_message.set_token('ID', l_cust_name_num);
fnd_msg_pub.add;
END IF;
RAISE fnd_api.g_exc_unexpected_error;
END IF;
ELSE
IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
OPEN csr_cust_name(p_customer_info.cust_account_id);
FETCH csr_cust_name INTO l_cust_name_num;
CLOSE csr_cust_name;
--
fnd_message.set_name('OZF', 'OZF_CLAIM_ATPY_AMT_SMALL');
fnd_message.set_token('ID', l_cust_name_num);
fnd_msg_pub.add;
END IF;
RAISE fnd_api.g_exc_unexpected_error;
END IF;
--
IF l_eligible_flag = fnd_api.g_true THEN
OPEN csr_claim_num(l_claim_id);
FETCH csr_claim_num
INTO l_claim_num,
l_claim_amt,
l_cust_billto_acct_site_id,
l_claim_currency;
CLOSE csr_claim_num;
--
gefnd_log_pkg.debug('Claim Number: ' || l_claim_num);
gefnd_log_pkg.debug('Claim Amount: ' || l_claim_amt);
gefnd_log_pkg.debug('Claim Currency: ' || l_claim_currency);
--
IF l_cust_billto_acct_site_id IS NOT NULL OR l_cust_billto_acct_site_id <> 0 THEN
gefnd_log_pkg.debug('The claim is created for bill_to site: ' || l_cust_billto_acct_site_id);
fnd_file.put_line(fnd_file.output, 'The claim is created for bill_to site: ' || l_cust_billto_acct_site_id);
END IF;
--
fnd_file.put_line(fnd_file.output, 'Claim Number' || l_claim_num);
fnd_file.put_line(fnd_file.output, 'Claim Amount' || l_claim_amt);
fnd_file.put_line(fnd_file.output, 'Claim Currency' || l_claim_currency);
--
x_claim_id := l_claim_id;
END IF;
--Ver 1.7 Start
--IF (ln_note_typ_cnt=0) THEN
IF (gn_err_cnt<>0 AND lv_suppress_mail='NO') THEN --added as per ver 1.8 --added and condition related to suppress mail for ver-1.9
gn_note_type_cnt :=1;
--Below block to pull offer requestor, inputter, sales finance
BEGIN
SELECT
resource_id,
TRIM(resource_name),
TRIM(SOURCE_EMAIL)
INTO ln_req_resource_id,lv_requestor, lv_requestor_mail
FROM
jtf_rs_resource_extns_vl
WHERE
resource_id = (select owner_id from ozf_offers where qp_list_header_id=p_plan_id);
SELECT
description,EMAIL_ADDRESS
INTO lv_inputter,lv_inputter_mail
FROM
fnd_user
WHERE
user_id = (select created_by from ozf_offers where qp_list_header_id=p_plan_id);
SELECT
TRIM(resource_name),TRIM(SOURCE_EMAIL)
INTO lv_sales_finance, lv_sales_finance_mail
FROM
jtf_rs_resource_extns_vl
WHERE
resource_id = (select attribute4 from qp_list_headers_all where list_header_id=p_plan_id);
EXCEPTION
WHEN OTHERS THEN
gefnd_log_pkg.debug ('Error while deriving requestor, inputter and SF values for offer: '||l_offer_rec.offer_code||'Error- '|| SQLERRM);
RETURN;
END;
--Below block to write data into file
/*BEGIN
gefnd_log_pkg.debug ('Writing filedata when there is no pricing approval notetype at offer level');
UTL_FILE.put_line (
gv_nt_file_handle,
l_offer_rec.offer_code
|| ','
|| l_offer_rec.offer_name
|| ','
|| l_claim_num
|| ','
|| l_claim_type_rec.name
|| ','
|| l_claim_amt
|| ','
|| REPLACE(lv_requestor,',','')
|| ','
|| REPLACE(lv_inputter,',','')
|| ','
|| REPLACE(lv_sales_finance,',',''));
EXCEPTION
WHEN OTHERS THEN
gefnd_log_pkg.debug ('Error while writing pricing approval missing data into file for offer: '||l_offer_rec.offer_code||'Error- '|| SQLERRM);
RETURN;
END;*/--commented as per ver 1.8
--Below block to insert data into global temp table
BEGIN
gefnd_log_pkg.debug ('Inserting data into global temp table GEOZF_APL_AUTOPAY_CLM_GTBL');
INSERT INTO GEOZF_APL_AUTOPAY_CLM_GTBL (OFFER_CODE,OFFER_NAME,CLAIM_NUMBER,CLAIM_AMOUNT,REQ_RESOURCE_ID,REQUESTOR,INPUTTER,SALES_FINANCE,REQUESTOR_MAIL,INPUTTER_MAIL,SALES_FINANCE_MAIL,ERROR_MSG)
VALUES (l_offer_rec.offer_code,REPLACE(l_offer_rec.offer_name,',',''),l_claim_num,l_claim_amt,ln_req_resource_id,REPLACE(lv_requestor,',',''),REPLACE(lv_inputter,',',''),REPLACE(lv_sales_finance,',',''),lv_requestor_mail,lv_inputter_mail,lv_sales_finance_mail,gv_clm_err_msg);
--COMMIT;
EXCEPTION
WHEN OTHERS THEN
gefnd_log_pkg.debug ('Error while inserting values into temp table for: '||l_offer_rec.offer_code||'Error- '|| SQLERRM);
RETURN;
END;--added as per ver 1.8
END IF;
--ver 1.7 End
gefnd_log_pkg.pop(lv_func);
EXCEPTION
WHEN OTHERS THEN
x_return_status := fnd_api.g_ret_sts_error;
gefnd_log_pkg.pop(lv_func);
END create_claim_for_cust;
---------------------------------------------------------------------
-- PROCEDURE
-- Create_Claim_for_BD_Offer
--
-- PURPOSE
-- Create a claim for a backdated offer.
--
-- PARAMETERS
-- p_offer_tbl : list of offers info that a claim will be created on.
--
---------------------------------------------------------------------
PROCEDURE create_claim_for_bd_offer(p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
p_commit IN VARCHAR2 := fnd_api.g_false,
p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
p_offer_tbl IN offer_tbl_type) IS
l_return_status VARCHAR2(1);
l_msg_data VARCHAR2(2000);
l_msg_count NUMBER;
l_bill_to_site_use_id NUMBER;
l_claim_id NUMBER;
--
CURSOR sys_parameter_info_csr IS
SELECT autopay_flag,
autopay_reason_code_id,
autopay_claim_type_id,
autopay_periodicity,
autopay_periodicity_type,
payment_method,
claim_threshold,
claim_currency,
set_of_books_id
FROM ozf_sys_parameters;
--
l_autopay_flag VARCHAR2(1);
l_auto_reason_code_id NUMBER;
l_auto_claim_type_id NUMBER;
l_autopay_periodicity NUMBER;
l_autopay_periodicity_type VARCHAR2(30);
l_cust_account_id NUMBER;
l_amount NUMBER;
l_sys_pay_method VARCHAR2(15);
l_sys_thes_cur VARCHAR2(30);
l_sys_thes_amt NUMBER;
l_set_of_books_id NUMBER;
--
CURSOR settlement_method_csr(p_id IN NUMBER) IS
SELECT settlement_code
FROM ozf_offer_adjustments_b
WHERE list_header_id = p_id;
l_customer_info g_customer_info_csr%ROWTYPE;
l_funds_util_flt ozf_claim_accrual_pvt.funds_util_flt_type := NULL;
lv_func VARCHAR2(30) := 'CREATE_CLAIM_FOR_BD_OFFER';
BEGIN
gefnd_log_pkg.push(lv_func);
SAVEPOINT bdoffer;
-- get autopay_flag, reason_code_id
OPEN sys_parameter_info_csr;
FETCH sys_parameter_info_csr
INTO l_autopay_flag,
l_auto_reason_code_id,
l_auto_claim_type_id,
l_autopay_periodicity,
l_autopay_periodicity_type,
l_sys_pay_method,
l_sys_thes_amt,
l_sys_thes_cur,
l_set_of_books_id;
CLOSE sys_parameter_info_csr;
--
-- check reason_code and claim_type from sys_parameters.
IF l_auto_reason_code_id IS NULL THEN
IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
fnd_message.set_name('OZF', 'OZF_CLAIM_REASON_CD_MISSING');
fnd_msg_pub.add;
END IF;
RAISE fnd_api.g_exc_unexpected_error;
END IF;
--
IF l_auto_claim_type_id IS NULL THEN
IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
fnd_message.set_name('OZF', 'OZF_CLAIM_CLAIM_TYPE_MISSING');
fnd_msg_pub.add;
END IF;
RAISE fnd_api.g_exc_unexpected_error;
END IF;
-- Loop through p_offer table
FOR i IN p_offer_tbl.first .. p_offer_tbl.count
LOOP
IF l_cust_account_id IS NOT NULL THEN
-- Get customer information
OPEN g_customer_info_csr(l_cust_account_id);
FETCH g_customer_info_csr INTO l_customer_info;
CLOSE g_customer_info_csr;
--
validate_customer_info(p_customer_info => l_customer_info, x_return_status => l_return_status);
-- skip this customer if we can not get all the info.
IF l_return_status = fnd_api.g_ret_sts_error OR l_return_status = fnd_api.g_ret_sts_unexp_error THEN
RAISE fnd_api.g_exc_unexpected_error;
--goto end_loop;
END IF;
--
-- But, we need to overwrite the payment method for the customer;
OPEN settlement_method_csr(p_offer_tbl(i).offer_id);
FETCH settlement_method_csr INTO l_customer_info.payment_method;
CLOSE settlement_method_csr;
--
l_funds_util_flt := NULL;
l_funds_util_flt.activity_id := p_offer_tbl(i).offer_id;
l_funds_util_flt.activity_type := g_offer_type;
l_funds_util_flt.adjustment_type_id := p_offer_tbl(i).adjustment_type_id;
--
create_claim_for_cust(p_customer_info => l_customer_info,
p_amount => p_offer_tbl(i).amount,
p_mode => 'B',
p_set_of_books_id => l_set_of_books_id,
p_auto_reason_code_id => l_auto_reason_code_id,
p_auto_claim_type_id => l_auto_claim_type_id,
p_autopay_periodicity => l_autopay_periodicity,
p_autopay_periodicity_type => l_autopay_periodicity_type,
p_offer_payment_method => NULL,
p_sys_autopay_flag => l_autopay_flag,
p_sys_thres_amt => l_sys_thes_amt,
p_sys_thres_curr => l_sys_thes_cur,
p_sys_pay_method => l_sys_pay_method,
p_funds_util_flt => l_funds_util_flt,
p_plan_id => l_funds_util_flt.activity_id,
p_bill_to_site_use_id => l_bill_to_site_use_id,
x_claim_id => l_claim_id,
x_return_status => l_return_status);
IF l_return_status = fnd_api.g_ret_sts_error THEN
RAISE fnd_api.g_exc_error;
ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
RAISE fnd_api.g_exc_unexpected_error;
END IF;
END IF;
END LOOP;
--
gefnd_log_pkg.pop(lv_func);
EXCEPTION
WHEN fnd_api.g_exc_error THEN
ROLLBACK TO bdoffer;
fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => l_msg_count, p_data => l_msg_data);
gefnd_log_pkg.pop(lv_func);
WHEN fnd_api.g_exc_unexpected_error THEN
ROLLBACK TO bdoffer;
fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => l_msg_count, p_data => l_msg_data);
gefnd_log_pkg.pop(lv_func);
WHEN OTHERS THEN
ROLLBACK TO bdoffer;
fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => l_msg_count, p_data => l_msg_data);
gefnd_log_pkg.pop(lv_func);
END create_claim_for_bd_offer;
--------------------------------------------------------------------------------
-- API name : Start_Autopay
-- Type : Public
-- Pre-reqs : None
-- Function :
-- Parameters :
--
-- IN : p_run_mode IN VARCHAR2 Optional
-- : p_customer_id IN NUMBER Optional
-- : p_relationship_type IN VARCHAR2 Optional
-- : p_related_cust_account_id IN NUMBER Optional
-- : p_buy_group_party_id IN NUMBER Optional
-- : p_select_cust_children_flag IN VARCHAR2 Optional
-- : p_pay_to_customer IN VARCHAR2 Optional
-- : p_fund_id IN NUMBER Optional
-- : p_plan_type IN NUMBER Optional
-- : p_offer_type IN VARCHAR2 Optional
-- : p_plan_id IN NUMBER Optional
-- : p_product_category_id IN NUMBER Optional
-- : p_product_id IN NUMBER Optional
-- : p_end_date IN VARCHAR2 Optional
-- : p_org_id IN NUMBER Optional
--
-- Version : Current version 1.0
--
-- Note: This program automatically creates a claim for a set of customers
-- The customer set is selected based on the input paramter. Also, we will pay a cusomter:
-- if a customer utiliztion amount summation is greater than his threshold_amount
-- or if the current date passes last_paid_date + threshold period.
-- End of Comments
--------------------------------------------------------------------------------
PROCEDURE start_autopay(errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY NUMBER,
p_org_id IN NUMBER DEFAULT NULL,
p_run_mode IN VARCHAR2 := NULL,
p_customer_id IN NUMBER := NULL,
p_relationship_type IN VARCHAR2 := NULL,
p_related_cust_account_id IN NUMBER := NULL,
p_buy_group_party_id IN NUMBER := NULL,
p_select_cust_children_flag IN VARCHAR2 := 'N',
p_pay_to_customer IN VARCHAR2 := NULL,
p_fund_id IN NUMBER := NULL,
p_plan_type IN VARCHAR2 := NULL,
p_offer_type IN VARCHAR2 := NULL,
p_plan_id IN NUMBER := NULL,
p_product_category_id IN NUMBER := NULL,
p_product_id IN NUMBER := NULL,
p_period_type IN VARCHAR2 := NULL,
p_period_value IN VARCHAR2 := NULL,
p_start_date IN VARCHAR2 := NULL,
p_end_date IN VARCHAR2 := NULL,
p_group_by_offer IN VARCHAR2) IS
l_return_status VARCHAR2(1);
l_msg_data VARCHAR2(2000);
l_msg_count NUMBER;
l_sys_pay_method VARCHAR2(15);
l_sys_thes_cur VARCHAR2(30);
l_sys_thes_amt NUMBER;
l_autopay_flag VARCHAR2(1);
lv_autopay_flag VARCHAR2(1) := NULL;
l_auto_reason_code_id NUMBER;
l_auto_claim_type_id NUMBER;
l_autopay_periodicity NUMBER;
l_autopay_periodicity_type VARCHAR2(30);
l_run_mode VARCHAR2(80);
l_pay_to VARCHAR2(80);
l_rel_cust_name VARCHAR2(390);
l_cust_name VARCHAR2(390);
l_rlship VARCHAR2(80);
l_fund_name VARCHAR2(240);
l_plan_type_name VARCHAR2(240);
l_plan_name VARCHAR2(240);
l_prod_cat_name VARCHAR2(240);
l_prod_name VARCHAR2(240);
l_buy_gp_name VARCHAR2(70);
l_members_flag VARCHAR2(80);
l_offer_type_name VARCHAR2(80);
l_bill_to_site_use_id NUMBER;
l_prev_site_use_id NUMBER;
lv_frequency_unit VARCHAR2(30) := NULL;
lv_frequency NUMBER := NULL;
l_claim_id NUMBER;
l_message VARCHAR2(4000);
l_offers_found BOOLEAN;
-- Ver 1.4 Start
CURSOR email_cur IS
SELECT meaning
FROM fnd_lookup_values
WHERE lookup_type = 'GEOZF_APL_SEND_MAIL_DETAILS_LT'
AND LANGUAGE = 'US'
AND enabled_flag = 'Y'
AND SYSDATE BETWEEN nvl(start_date_active, SYSDATE - 1) AND nvl(end_date_active, SYSDATE + 1);
-- Ver 1.4 End
--
CURSOR sys_parameter_info_csr IS
SELECT autopay_flag,
autopay_reason_code_id,
autopay_claim_type_id,
autopay_periodicity,
autopay_periodicity_type,
payment_method,
claim_threshold,
claim_currency,
last_paid_date,
set_of_books_id
FROM ozf_sys_parameters;
--
--Version 1.1 changes
--Cursor to fetch offer details
CURSOR c_offer_cur(p_list_header_id NUMBER DEFAULT NULL) IS
SELECT offer_id,
org_id,
qp_list_header_id plan_id,
autopay_flag,
autopay_frequency_unit,
autopay_frequency
FROM ozf_offers
WHERE qp_list_header_id = NVL(p_list_header_id, qp_list_header_id)
AND autopay_flag = 'Y'
AND status_code = 'ACTIVE' --added as per ver-1.9
AND geozf_apl_autopay_pkg.is_offer_eligible(qp_list_header_id) = 'Y'; --Version 1.2 changes
l_offer_rec c_offer_cur%ROWTYPE;
--
CURSOR c_get_site_name(cv_site_use_id NUMBER) IS
SELECT site_use_id || '(' || location || ')'
FROM hz_cust_site_uses
WHERE site_use_id = cv_site_use_id;
--
CURSOR csr_meaning(lkup_type IN VARCHAR2,
lkup_code IN VARCHAR2) IS
SELECT meaning
FROM ozf_lookups
WHERE lookup_type = lkup_type
AND lookup_code = lkup_code;
--
CURSOR csr_rlship(lkup_code IN VARCHAR2) IS
SELECT ar.meaning
FROM ar_lookups ar
WHERE ar.lookup_type = 'RELATIONSHIP_TYPE'
AND ar.lookup_code = lkup_code;
--
CURSOR csr_members(lkup_type IN VARCHAR2,
lkup_code IN VARCHAR2) IS
SELECT meaning
FROM fnd_lookups
WHERE lookup_type = lkup_type
AND lookup_code = lkup_code;
--
CURSOR csr_fund_name(p_fund_id IN NUMBER) IS
SELECT f.short_name
FROM ozf_funds_vl f
WHERE f.fund_id = p_fund_id;
--
CURSOR csr_offer_name(off_id IN NUMBER) IS
SELECT qp.description
FROM qp_list_headers_vl qp
WHERE qp.list_header_id = off_id;
--
CURSOR csr_prod_cat_name(prod_cat_id IN NUMBER) IS
SELECT mct.description
FROM mtl_categories_tl mct
WHERE mct.category_id = prod_cat_id;
--
CURSOR csr_prod_name(p_product_id IN NUMBER,
p_org_id IN NUMBER) IS
SELECT description
FROM mtl_system_items_kfv
WHERE inventory_item_id = p_product_id
AND organization_id = p_org_id;
--
CURSOR c_get_om_system_param(p_org_id IN NUMBER) IS
SELECT nvl(master_organization_id, fnd_profile.value('AMS_ITEM_ORGANIZATION_ID'))
FROM oe_system_parameters_all
WHERE org_id = p_org_id;
--
CURSOR csr_cust_name(cv_cust_account_id IN NUMBER) IS
SELECT concat(concat(party.party_name, ' ('), concat(ca.account_number, ') '))
FROM hz_cust_accounts ca,
hz_parties party
WHERE ca.party_id = party.party_id
AND ca.cust_account_id = cv_cust_account_id;
--
CURSOR csr_get_party_id(cv_cust_account_id IN NUMBER) IS
SELECT party_id
FROM hz_cust_accounts
WHERE cust_account_id = cv_cust_account_id;
--
CURSOR csr_party_name(cv_party_id IN NUMBER) IS
SELECT party_name
FROM hz_parties
WHERE party_id = cv_party_id;
--
CURSOR csr_offer_pay_name(cv_payment_method IN VARCHAR2) IS
SELECT meaning
FROM ozf_lookups
WHERE lookup_type = 'OZF_AUTOPAY_METHOD'
AND lookup_code = cv_payment_method;
--
--Multiorg Changes
CURSOR operating_unit_csr IS
SELECT ou.organization_id org_id
FROM hr_operating_units ou
WHERE mo_global.check_access(ou.organization_id) = 'Y';
--Added below 2 cursors for ver: 1.8
CURSOR c_requestor_cur IS
SELECT DISTINCT REQ_RESOURCE_ID,REQUESTOR_MAIL FROM GEOZF_APL_AUTOPAY_CLM_GTBL ORDER BY REQ_RESOURCE_ID;
CURSOR c_req_data_cur(cv_resource_id IN NUMBER) IS
SELECT * FROM GEOZF_APL_AUTOPAY_CLM_GTBL where REQ_RESOURCE_ID=cv_resource_id;
l_cursor NUMBER;
--
l_stmt VARCHAR2(3000);
l_funds_util_flt ozf_claim_accrual_pvt.funds_util_flt_type := NULL;
l_cust_account_id NUMBER;
l_amount NUMBER;
l_customer_info g_customer_info_csr%ROWTYPE;
l_cust_name_num VARCHAR2(390);
l_offer_pay_method VARCHAR2(30);
l_offer_pay_name VARCHAR2(80);
l_party_id NUMBER;
l_trade_prf_exist BOOLEAN := FALSE;
l_ignore NUMBER;
l_cust_info_invalid BOOLEAN := FALSE;
l_utiz_currency VARCHAR2(15);
--
TYPE trd_prf_rec_typ IS RECORD(
trade_profile_id NUMBER,
frequency_unit VARCHAR2(30),
frequency NUMBER);
--
TYPE trd_prf_tbl_type IS TABLE OF trd_prf_rec_typ INDEX BY BINARY_INTEGER;
l_trd_prof_tbl trd_prf_tbl_type;
--
i BINARY_INTEGER := 0;
l_plan_id NUMBER;
--Multiorg Changes
m NUMBER := 0;
l_org_id ozf_utility_pvt.operating_units_tbl;
l_currency_rec ozf_claim_accrual_pvt.currency_rec_type;
l_group_by_offer VARCHAR2(1);
l_inv_org_id NUMBER;
--
l_paid_date DATE; -- to store last paid date value to update in SP and CTP table
l_sys_last_paid_date DATE; -- to retrieve the last paid date for SP
l_set_of_books_id NUMBER;
l_upd_last_pd_dt_flag VARCHAR2(1); --flag to indicate if last paid date needs to be updated or not
l_create_claim_flag VARCHAR2(1); --flag to indicate if claim needs to be created or not
l_freq_tbl frequency_tbl_typ; -- Frequency table instance
l_temp_freq_tbl frequency_tbl_typ; -- Temp Frequency table instance
fidx NUMBER;
l_start_date DATE; -- to store the start date for period type and value passed
l_end_date DATE; -- to store the end date for period type and value passed
x NUMBER := 1; -- to store budget count
y NUMBER := 1;
l_plan_org_id NUMBER; --to store org ID of the offer passed as input parameter
l_frequency_period VARCHAR2(1); -- Frequency/Period (N,S,C,O,P)
l_autopay_date DATE; -- Start date of the autopay program
l_frequency_group NUMBER;
l_eligible_flag VARCHAR2(1) := 'Y';
l_success VARCHAR2(1) := 'N';
lv_func VARCHAR2(30) := 'START_AUTOPAY';
l_site_name VARCHAR2(390);
--ver 1.7
lv_body VARCHAR2(500):='The following auto-pay claims are on-hold. Please review and resolve attached errors. Finance will resolve Customer Trade Profile errors. Once resolved, claims will be released for approval to Requestor.';
lv_file_path VARCHAR2(1000);
ln_conc_req_id fnd_concurrent_requests.request_id%TYPE;
lv_support_user_mail VARCHAR2 (2000);
lv_file_name VARCHAR2 (240) := 'Autopay_Claims_On-hold_Action_needed.csv';
lb_finished BOOLEAN;
lv_phase VARCHAR2 (240);
lv_status VARCHAR2 (240);
lv_request_phase VARCHAR2 (240);
lv_request_status VARCHAR2 (240);
lv_message VARCHAR2 (2400);
--ver 1.8
lv_to_mail VARCHAR2 (240);
lv_cc_mail VARCHAR2 (240);
lv_cc_mail1 VARCHAR2 (240);
ln_req_mail_cnt NUMBER;
ln_inp_mail_cnt NUMBER;
ln_sf_mail_cnt NUMBER;
lv_req_tag VARCHAR2(30);
lv_inp_tag VARCHAR2(30);
lv_sf_tag VARCHAR2(30);
BEGIN
gefnd_log_pkg.initialize(p_module => g_module,
p_destination => g_debug_log_dest);
gefnd_log_pkg.push(lv_func);
--
gefnd_log_pkg.debug('p_org_id' ,p_org_id);
gefnd_log_pkg.debug('p_run_mode' ,p_run_mode);
gefnd_log_pkg.debug('p_customer_id' ,p_customer_id);
gefnd_log_pkg.debug('p_relationship_type' ,p_relationship_type);
gefnd_log_pkg.debug('p_related_cust_account_id' ,p_related_cust_account_id);
gefnd_log_pkg.debug('p_buy_group_party_id' ,p_buy_group_party_id);
gefnd_log_pkg.debug('p_select_cust_children_flag' ,p_select_cust_children_flag);
gefnd_log_pkg.debug('p_pay_to_customer' ,p_pay_to_customer);
gefnd_log_pkg.debug('p_fund_id' ,p_fund_id);
gefnd_log_pkg.debug('p_plan_type' ,p_plan_type);
gefnd_log_pkg.debug('p_offer_type' ,p_offer_type);
gefnd_log_pkg.debug('p_plan_id' ,p_plan_id);
gefnd_log_pkg.debug('p_product_category_id' ,p_product_category_id);
gefnd_log_pkg.debug('p_product_id' ,p_product_id);
gefnd_log_pkg.debug('p_period_type' ,p_period_type);
gefnd_log_pkg.debug('p_period_value' ,p_period_value);
gefnd_log_pkg.debug('p_start_date' ,p_start_date);
gefnd_log_pkg.debug('p_end_date' ,p_end_date);
gefnd_log_pkg.debug('p_group_by_offer' ,p_group_by_offer);
-- Ver 1.4 ERPOEP-44805 Start
--gv_offer_claim_err_utl := UTL_FILE.fopen ('GE_CHRM_OFFERS', gv_file_name, 'W');
--utl_file.put_line(gv_offer_claim_err_utl,'Offer Code Offer Name Message');
-- Ver 1.4 ERPOEP-44805 End
--
l_autopay_date := trunc(SYSDATE);
fnd_file.put_line(fnd_file.output, '*------------------------------ Claims Autopay Execution Report ------------------------------*');
fnd_file.put_line(fnd_file.output, 'Execution Starts On: ' || to_char(SYSDATE, 'MM-DD-YYYY HH24:MI:SS'));
fnd_file.put_line(fnd_file.output,'*---------------------------------------------------------------------------------------------*');
--Date input parameters validation
IF (p_period_type IS NOT NULL OR p_period_value IS NOT NULL) AND (p_start_date IS NOT NULL OR p_end_date IS NOT NULL) THEN
fnd_message.set_name('OZF', 'OZF_DATES_PERIOD_ENTERED');
fnd_msg_pub.add;
RAISE fnd_api.g_exc_unexpected_error;
ELSIF p_start_date IS NOT NULL AND p_end_date IS NOT NULL AND p_period_type IS NULL AND p_period_value IS NULL THEN
IF p_start_date > p_end_date THEN
fnd_message.set_name('OZF', 'OZF_STRT_DT_GTR _END_DT');
fnd_msg_pub.add;
RAISE fnd_api.g_exc_unexpected_error;
END IF;
ELSIF p_start_date IS NULL AND p_end_date IS NULL AND p_period_type IS NOT NULL AND p_period_value IS NULL THEN
fnd_message.set_name('OZF', 'OZF_ATPY_PRD_VAL_NOT_ENTRD');
fnd_msg_pub.add;
RAISE fnd_api.g_exc_unexpected_error;
END IF;
--Multiorg Changes
mo_global.init('OZF');
--
IF p_org_id IS NULL THEN
mo_global.set_policy_context('M', NULL);
OPEN operating_unit_csr;
LOOP
FETCH operating_unit_csr INTO l_org_id(m);
m := m + 1;
--
gefnd_log_pkg.debug('Org ID', l_org_id(m));
EXIT WHEN operating_unit_csr%NOTFOUND;
END LOOP;
CLOSE operating_unit_csr;
ELSE
l_org_id(m) := p_org_id;
gefnd_log_pkg.debug('Org ID', l_org_id(m));
END IF;
--
IF p_plan_id IS NOT NULL THEN
OPEN csr_offer_name(p_plan_id);
FETCH csr_offer_name INTO l_plan_name;
CLOSE csr_offer_name;
--
gefnd_log_pkg.debug('Plan Name', l_plan_name);
END IF;
---------------------------------------------------------------------
/* 1. If OU and offer are passed and offer is not Global offer,and OU of offer
and OU passed as input parameter are the same,then no need to loop
through each OU.
2. If OU is passed and offer is not passed,then loop through all eligible billto and offer
of the passed OU.
3.If OU is not passed and offer is passed as input parameter and offer is not Global offer
then if user has access to OU,then proceed with execution,else throw error.*/
-----------------------------------------------------------------------
IF p_plan_id IS NOT NULL THEN
OPEN c_offer_cur(p_plan_id);
FETCH c_offer_cur INTO l_offer_rec;
CLOSE c_offer_cur;
--
IF l_offer_rec.plan_id IS NOT NULL THEN
l_plan_org_id := l_offer_rec.org_id;
gefnd_log_pkg.debug('l_plan_org_id', l_plan_org_id);
--
IF l_plan_org_id IS NOT NULL AND mo_global.check_access(l_plan_org_id) = 'Y' THEN
l_org_id.delete;
l_org_id(m) := l_plan_org_id;
mo_global.set_policy_context('S', l_org_id(m));
ELSIF l_plan_org_id IS NOT NULL AND mo_global.check_access(l_plan_org_id) <> 'Y' THEN
IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
fnd_message.set_name('OZF', 'OZF_OU_ACCESS_NOT_PRMTD');
fnd_message.set_token('OFFER_NAME', l_plan_name);
fnd_msg_pub.add;
END IF;
RAISE fnd_api.g_exc_unexpected_error;
END IF;
--
IF p_org_id IS NOT NULL AND l_plan_org_id IS NOT NULL THEN
IF p_org_id = l_plan_org_id THEN
l_org_id.delete;
l_org_id(m) := l_plan_org_id;
mo_global.set_policy_context('S', l_org_id(m));
ELSE
IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
fnd_message.set_name('OZF', 'OZF_OFFR_OU_NOT_MATCH');
fnd_message.set_token('OFFER_NAME', l_plan_name);
fnd_msg_pub.add;
END IF;
RAISE fnd_api.g_exc_unexpected_error;
END IF;
END IF;
ELSE
l_eligible_flag := 'N';
END IF;
END IF;
--Multiorg Changes
IF l_org_id.count > 0 AND l_eligible_flag = 'Y' THEN
/*--Added below 2 blocks for ver-1.7
BEGIN
gefnd_log_pkg.debug ('Before opening the file in Write mode');
gv_nt_file_handle:= UTL_FILE.fopen ('GE_CHRM_OFFERS',lv_file_name,'W');
EXCEPTION
WHEN OTHERS
THEN
UTL_FILE.fclose (gv_nt_file_handle);
gefnd_log_pkg.debug ('Error while opening the file '|| SQLERRM);
RAISE fnd_api.g_exc_unexpected_error;
END;
BEGIN
gefnd_log_pkg.debug ('Before writing header');
UTL_FILE.put_line (
gv_nt_file_handle,
'Offer Code'
|| ','
|| 'Offer Name'
|| ','
|| 'Claim Number'
|| ','
|| 'Claim Type'
|| ','
|| 'Claim Amount'
|| ','
|| 'Requestor'
|| ','
|| 'Inputter'
|| ','
|| 'Sales Finance');
EXCEPTION
WHEN OTHERS THEN
UTL_FILE.fclose (gv_nt_file_handle);
gefnd_log_pkg.debug ('Unexpected error while writing the Flat file header:- '|| SQLCODE|| '-' || SQLERRM);
RAISE fnd_api.g_exc_unexpected_error;
END;*/--commented for ver 1.8
FOR m IN l_org_id.first .. l_org_id.last
LOOP
BEGIN
SAVEPOINT autopay;
mo_global.set_policy_context('S', l_org_id(m));
-- Write OU info to OUT file
fnd_file.put_line(fnd_file.output, 'Operating Unit: ' || mo_global.get_ou_name(l_org_id(m)));
fnd_file.put_line(fnd_file.output, '-----------------------------------------------------');
--
l_group_by_offer := nvl(p_group_by_offer, 'N');
IF p_run_mode IS NOT NULL THEN
OPEN csr_meaning('OZF_CLAIM_AUTOPAY_RUNMODE', p_run_mode);
FETCH csr_meaning INTO l_run_mode;
CLOSE csr_meaning;
--
gefnd_log_pkg.debug('l_run_mode', l_run_mode);
END IF;
IF p_customer_id IS NOT NULL THEN
OPEN csr_cust_name(p_customer_id);
FETCH csr_cust_name INTO l_cust_name;
CLOSE csr_cust_name;
--
gefnd_log_pkg.debug('l_cust_name', l_cust_name);
--
IF p_relationship_type IS NOT NULL THEN
OPEN csr_rlship(p_relationship_type);
FETCH csr_rlship INTO l_rlship;
CLOSE csr_rlship;
--
gefnd_log_pkg.debug('l_rlship', l_rlship);
--
IF p_related_cust_account_id IS NOT NULL THEN
OPEN csr_cust_name(p_related_cust_account_id);
FETCH csr_cust_name INTO l_rel_cust_name;
CLOSE csr_cust_name;
--
gefnd_log_pkg.debug('l_rel_cust_name', l_rel_cust_name);
--
IF p_pay_to_customer IS NOT NULL THEN
OPEN csr_meaning('OZF_CLAIM_PAYTO_TYPE', p_pay_to_customer);
FETCH csr_meaning INTO l_pay_to;
CLOSE csr_meaning;
--
gefnd_log_pkg.debug('l_pay_to', l_pay_to);
END IF;
END IF;
END IF;
--
IF p_buy_group_party_id IS NOT NULL THEN
OPEN csr_party_name(p_buy_group_party_id);
FETCH csr_party_name INTO l_buy_gp_name;
CLOSE csr_party_name;
--
gefnd_log_pkg.debug('l_buy_gp_name', l_buy_gp_name);
END IF;
--
IF p_select_cust_children_flag IS NOT NULL THEN
OPEN csr_members('YES_NO', p_select_cust_children_flag);
FETCH csr_members INTO l_members_flag;
CLOSE csr_members;
--
gefnd_log_pkg.debug('l_members_flag', l_members_flag);
END IF;
END IF;
--
IF p_fund_id IS NOT NULL THEN
OPEN csr_fund_name(p_fund_id);
FETCH csr_fund_name INTO l_fund_name;
CLOSE csr_fund_name;
--
gefnd_log_pkg.debug('l_fund_name', l_fund_name);
END IF;
IF p_plan_type IS NOT NULL THEN
OPEN csr_meaning('OZF_CLAIM_ASSO_ACT_TYPE', p_plan_type);
FETCH csr_meaning INTO l_plan_type_name;
CLOSE csr_meaning;
--
gefnd_log_pkg.debug('l_plan_type_name', l_plan_type_name);
--
IF p_plan_id IS NOT NULL THEN
OPEN csr_offer_name(p_plan_id);
FETCH csr_offer_name INTO l_plan_name;
CLOSE csr_offer_name;
l_group_by_offer := 'Y';
--
gefnd_log_pkg.debug('l_plan_name', l_plan_name);
END IF;
END IF;
IF p_offer_type IS NOT NULL THEN
OPEN csr_meaning('OZF_OFFER_TYPE', p_offer_type);
FETCH csr_meaning INTO l_offer_type_name;
CLOSE csr_meaning;
--
gefnd_log_pkg.debug('l_offer_type_name', l_offer_type_name);
END IF;
IF p_product_category_id IS NOT NULL THEN
OPEN csr_prod_cat_name(p_product_category_id);
FETCH csr_prod_cat_name INTO l_prod_cat_name;
CLOSE csr_prod_cat_name;
--
gefnd_log_pkg.debug('l_prod_cat_name', l_prod_cat_name);
END IF;
IF p_product_id IS NOT NULL THEN
OPEN c_get_om_system_param(l_org_id(m));
FETCH c_get_om_system_param INTO l_inv_org_id;
CLOSE c_get_om_system_param;
--
--patch start
IF l_inv_org_id IS NULL THEN
l_inv_org_id := NVL(fnd_profile.VALUE_SPECIFIC(name =>'AMS_ITEM_ORGANIZATION_ID',org_id=>l_org_id(m)),
fnd_profile.VALUE_SPECIFIC(name =>'AMS_ITEM_ORGANIZATION_ID',org_id=>-999));
END IF;
--patch end
--
gefnd_log_pkg.debug('l_inv_org_id', l_inv_org_id);
OPEN csr_prod_name(p_product_id, l_inv_org_id);
FETCH csr_prod_name INTO l_prod_name;
CLOSE csr_prod_name;
--
gefnd_log_pkg.debug('l_prod_name', l_prod_name);
END IF;
--
fnd_file.put_line(fnd_file.output, rpad('Run Mode', 40, ' ') || ': ' || l_run_mode);
fnd_file.put_line(fnd_file.output, rpad('Customer Name', 40, ' ') || ': ' || l_cust_name);
fnd_file.put_line(fnd_file.output, rpad('Relationship', 40, ' ') || ': ' || l_rlship);
fnd_file.put_line(fnd_file.output, rpad('Related Customer', 40, ' ') || ': ' || l_rel_cust_name);
fnd_file.put_line(fnd_file.output, rpad('Buying Group and Members', 40, ' ') || ': ' || l_buy_gp_name);
fnd_file.put_line(fnd_file.output, rpad('Include All Member Earnings', 40, ' ') || ': ' || l_members_flag);
fnd_file.put_line(fnd_file.output, rpad('Pay To', 40, ' ') || ': ' || l_pay_to);
fnd_file.put_line(fnd_file.output, rpad('Fund Name', 40, ' ') || ': ' || l_fund_name);
fnd_file.put_line(fnd_file.output, rpad('Activity Type', 40, ' ') || ': ' || l_plan_type_name);
fnd_file.put_line(fnd_file.output, rpad('Offer Type', 40, ' ') || ': ' || l_offer_type_name);
fnd_file.put_line(fnd_file.output, rpad('Activity Name', 40, ' ') || ': ' || l_plan_name);
fnd_file.put_line(fnd_file.output, rpad('Product Category', 40, ' ') || ': ' || l_prod_cat_name);
fnd_file.put_line(fnd_file.output, rpad('Product', 40, ' ') || ': ' || l_prod_name);
fnd_file.put_line(fnd_file.output, rpad('End Date', 40, ' ') || ': ' || p_end_date);
fnd_file.put_line(fnd_file.output, rpad('Group By Offer', 40, ' ') || ': ' || p_group_by_offer);
fnd_file.put_line(fnd_file.output, rpad('Group By Offer changed as Activity Name provided', 40, ' ') || ': ' || l_group_by_offer);
fnd_file.put_line(fnd_file.output, rpad('Start Date', 40, ' ') || ': ' || p_start_date);
fnd_file.put_line(fnd_file.output, rpad('Period Type', 40, ' ') || ': ' || p_period_type);
fnd_file.put_line(fnd_file.output, rpad('Period Value', 40, ' ') || ': ' || p_period_value);
fnd_file.put_line(fnd_file.output, '*---------------------------------------------------------------------------------------------*');
/*------------ Autopay starts ---------------*/
-- get autopay_flag, reason_code_id
OPEN sys_parameter_info_csr;
FETCH sys_parameter_info_csr
INTO l_autopay_flag,
l_auto_reason_code_id,
l_auto_claim_type_id,
l_autopay_periodicity,
l_autopay_periodicity_type,
l_sys_pay_method,
l_sys_thes_amt,
l_sys_thes_cur,
l_sys_last_paid_date,
l_set_of_books_id;
CLOSE sys_parameter_info_csr;
--
IF g_dbg_on THEN
gefnd_log_pkg.debug('l_autopay_flag' ,l_autopay_flag);
gefnd_log_pkg.debug('l_auto_reason_code_id' ,l_auto_reason_code_id);
gefnd_log_pkg.debug('l_auto_claim_type_id' ,l_auto_claim_type_id);
gefnd_log_pkg.debug('l_autopay_periodicity' ,l_autopay_periodicity);
gefnd_log_pkg.debug('l_autopay_periodicity_type' ,l_autopay_periodicity_type);
gefnd_log_pkg.debug('l_sys_pay_method' ,l_sys_pay_method);
gefnd_log_pkg.debug('l_sys_thes_amt' ,l_sys_thes_amt);
gefnd_log_pkg.debug('l_sys_thes_cur' ,l_sys_thes_cur);
gefnd_log_pkg.debug('l_sys_last_paid_date' ,l_sys_last_paid_date);
gefnd_log_pkg.debug('l_set_of_books_id', l_set_of_books_id);
END IF;
-- check reason_code and claim_type from sys_parameters.
IF l_auto_reason_code_id IS NULL THEN
fnd_message.set_name('OZF', 'OZF_CLAIM_REASON_CD_MISSING');
fnd_msg_pub.add;
RAISE fnd_api.g_exc_unexpected_error;
END IF;
--
IF l_auto_claim_type_id IS NULL THEN
fnd_message.set_name('OZF', 'OZF_CLAIM_CLAIM_TYPE_MISSING');
fnd_msg_pub.add;
RAISE fnd_api.g_exc_unexpected_error;
END IF;
--
get_frequency_period(p_org_id => p_org_id,
p_customer_id => p_customer_id,
p_fund_id => p_fund_id,
p_plan_id => p_plan_id,
p_product_category_id => p_product_category_id,
p_product_id => p_product_id,
p_plan_type => p_plan_type,
p_offer_type => p_offer_type,
p_start_date => p_start_date,
p_end_date => p_end_date,
p_period_type => p_period_type,
p_period_value => p_period_value,
x_frequency_period => l_frequency_period,
x_return_status => l_return_status);
gefnd_log_pkg.debug('l_frequency_period', l_frequency_period);
fnd_file.put_line(fnd_file.output, rpad('Frequency/Period', 40, ' ') || ': ' || l_frequency_period);
fnd_file.put_line(fnd_file.output, '');
--
--Version 1.2 changes start
--
IF l_frequency_period IN ('O', 'S') THEN --Offer frequency
IF l_frequency_period = 'O' THEN
FOR rec IN c_offer_cur(p_plan_id)
LOOP
check_offer_budget_frequency(p_fund_id => p_fund_id,
p_plan_id => p_plan_id,
p_autopay_date => l_autopay_date,
x_freq_tbl => l_freq_tbl,
x_return_status => l_return_status);
END LOOP;
ELSE
fidx := 1;
l_freq_tbl.delete;
--
FOR rec IN c_offer_cur
LOOP
l_temp_freq_tbl.delete;
check_offer_budget_frequency(p_fund_id => p_fund_id,
p_plan_id => rec.plan_id,
p_autopay_date => l_autopay_date,
x_freq_tbl => l_temp_freq_tbl,
x_return_status => l_return_status);
IF l_temp_freq_tbl.count > 0 THEN
l_freq_tbl(fidx) := l_temp_freq_tbl(1);
fidx := fidx + 1;
END IF;
END LOOP;
l_frequency_period := 'O';
l_group_by_offer := 'Y';
END IF;
--
IF g_dbg_on THEN
gefnd_log_pkg.debug('l_autopay_date', l_autopay_date);
gefnd_log_pkg.debug('l_freq_tbl.COUNT', l_freq_tbl.COUNT);
FOR fi IN 1 .. l_freq_tbl.COUNT
LOOP
gefnd_log_pkg.debug('plan_id', l_freq_tbl(fi).plan_id);
gefnd_log_pkg.debug('upd_last_pd_dt_flag', l_freq_tbl(fi).upd_last_pd_dt_flag);
gefnd_log_pkg.debug('paid_date', l_freq_tbl(fi).paid_date);
gefnd_log_pkg.debug('create_claim_flag', l_freq_tbl(fi).create_claim_flag);
gefnd_log_pkg.debug('frequency_unit', l_freq_tbl(fi).frequency_unit);
END LOOP;
END IF;
ELSIF l_frequency_period = 'P' THEN --Period type and value
IF p_start_date IS NULL AND p_end_date IS NULL THEN
get_start_end_date(p_period_type => p_period_type,
p_period_value => p_period_value,
x_start_date => l_start_date,
x_end_date => l_end_date,
x_return_status => l_return_status);
l_funds_util_flt.start_date := l_start_date;
l_funds_util_flt.end_date := l_end_date;
--
IF g_dbg_on THEN
gefnd_log_pkg.debug('l_funds_util_flt.start_date', l_funds_util_flt.start_date);
gefnd_log_pkg.debug('l_funds_util_flt.end_date', l_funds_util_flt.end_date);
END IF;
--
IF l_start_date IS NULL AND l_end_date IS NULL THEN
fnd_message.set_name('OZF', 'OZF_ST_END_DT_NOT_FOUND');
fnd_msg_pub.add;
RAISE fnd_api.g_exc_unexpected_error;
END IF;
END IF;
END IF;
-- construct the following sql based on the inputs
l_funds_util_flt.run_mode := p_run_mode;
l_funds_util_flt.utilization_type := NULL;
l_funds_util_flt.offer_type := p_offer_type;
l_funds_util_flt.activity_type := p_plan_type;
l_funds_util_flt.activity_id := p_plan_id;
l_funds_util_flt.fund_id := p_fund_id;
l_funds_util_flt.adjustment_type_id := NULL;
IF p_product_id IS NOT NULL THEN
l_funds_util_flt.product_level_type := 'PRODUCT';
l_funds_util_flt.product_id := p_product_id;
ELSIF p_product_category_id IS NOT NULL THEN
l_funds_util_flt.product_level_type := 'FAMILY';
l_funds_util_flt.product_id := p_product_category_id;
END IF;
-- additional filter conditions
l_funds_util_flt.cust_account_id := p_customer_id;
l_funds_util_flt.relationship_type := p_relationship_type;
l_funds_util_flt.related_cust_account_id := p_related_cust_account_id;
l_funds_util_flt.buy_group_party_id := p_buy_group_party_id;
l_funds_util_flt.select_cust_children_flag := p_select_cust_children_flag;
l_funds_util_flt.pay_to_customer := p_pay_to_customer;
l_funds_util_flt.group_by_offer := nvl(l_group_by_offer, 'N'); --R12
IF l_frequency_period = 'O' OR (p_plan_id IS NOT NULL AND l_plan_org_id IS NULL) THEN
l_funds_util_flt.fund_id := NULL;
END IF;
-- Changes for FXGL Enhancement
-- l_funds_util_flt.utiz_currency_code is null here
-- and all currency records are retrieved but they are
-- grouped by currency_code
-- The amount_remaining is in utiz_curency and not in functional currency
-- Added For Multi Currency - kpatro
l_funds_util_flt.autopay_check := 'AUTOPAY';
l_funds_util_flt.frequency_period := l_frequency_period;
l_funds_util_flt.autopay_date := l_autopay_date;
IF p_start_date IS NOT NULL OR p_end_date IS NOT NULL THEN
l_funds_util_flt.start_date := fnd_date.canonical_to_date(p_start_date);
l_funds_util_flt.end_date := fnd_date.canonical_to_date(p_end_date);
END IF;
IF l_frequency_period = 'O' THEN
IF l_freq_tbl.count > 0 THEN
x := nvl(l_freq_tbl.first, 1);
y := nvl(l_freq_tbl.last, 0);
END IF;
END IF;
--Populating Currency record set
l_currency_rec.universal_currency_code := fnd_profile.value('OZF_TP_COMMON_CURRENCY');
gefnd_log_pkg.debug('x', x);
gefnd_log_pkg.debug('y', y);
FOR j IN x .. y --for no budget scenario x is 1.
LOOP
IF l_frequency_period = 'O' AND l_freq_tbl.count > 0 THEN
l_funds_util_flt.activity_id := l_freq_tbl(j).plan_id;
l_funds_util_flt.end_date := get_paid_date(l_freq_tbl(j).frequency_unit, l_freq_tbl(j).frequency, l_autopay_date); --Version 1.1 changes
gefnd_log_pkg.debug('plan_id', l_freq_tbl(j).plan_id);
END IF;
gn_iter_cnt := 0; --ver 1.8
--
ozf_claim_accrual_pvt.get_utiz_sql_stmt(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_false,
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_summary_view => 'AUTOPAY',
p_funds_util_flt => l_funds_util_flt,
px_currency_rec => l_currency_rec,
p_cust_account_id => p_customer_id,
x_utiz_sql_stmt => l_stmt);
gefnd_log_pkg.debug('l_return_status', l_return_status);
IF l_return_status <> fnd_api.g_ret_sts_success THEN
RAISE fnd_api.g_exc_error;
END IF;
--
IF gefnd_log_pkg.get_current_debug_level >= gefnd_log_pkg.LEVEL_DEBUG THEN
gefnd_report_util_pkg.print_clob(p_clob => FND_DSQL.get_text(TRUE) || chr(10),
p_destination => gefnd_report_util_pkg.G_DEST_LOG);
END IF;
--
l_cursor := dbms_sql.open_cursor;
fnd_dsql.set_cursor(l_cursor);
dbms_sql.parse(l_cursor, l_stmt, dbms_sql.native);
dbms_sql.define_column(l_cursor, 1, l_offer_pay_method, 30);
dbms_sql.define_column(l_cursor, 2, l_amount);
dbms_sql.define_column(l_cursor, 3, l_utiz_currency, 15);
dbms_sql.define_column(l_cursor, 4, l_bill_to_site_use_id);
--
IF p_customer_id IS NULL THEN
dbms_sql.define_column(l_cursor, 5, l_cust_account_id);
IF l_frequency_period IN ('S', 'C', 'O') THEN
dbms_sql.define_column(l_cursor, 6, l_frequency_group);
END IF;
ELSIF p_customer_id IS NOT NULL THEN
IF l_frequency_period IN ('S', 'C', 'O') THEN
dbms_sql.define_column(l_cursor, 5, l_frequency_group);
END IF;
END IF;
--
IF p_customer_id IS NULL AND nvl(l_group_by_offer, 'N') = 'N' THEN
dbms_sql.define_column(l_cursor, 5, l_cust_account_id);
IF l_frequency_period IN ('S', 'C', 'O') THEN
dbms_sql.define_column(l_cursor, 6, l_frequency_group);
END IF;
ELSIF p_customer_id IS NULL AND nvl(l_group_by_offer, 'N') = 'Y' THEN
dbms_sql.define_column(l_cursor, 6, l_plan_id);
IF l_frequency_period IN ('S', 'C', 'O') THEN
dbms_sql.define_column(l_cursor, 7, l_frequency_group);
END IF;
ELSIF nvl(l_group_by_offer, 'N') = 'Y' AND p_customer_id IS NOT NULL THEN
dbms_sql.define_column(l_cursor, 5, l_plan_id);
IF l_frequency_period IN ('S', 'C', 'O') THEN
dbms_sql.define_column(l_cursor, 6, l_frequency_group);
END IF;
END IF;
--
fnd_dsql.do_binds;
l_ignore := dbms_sql.execute(l_cursor);
l_offers_found := FALSE;
--
LOOP
fnd_msg_pub.initialize;
IF dbms_sql.fetch_rows(l_cursor) > 0 THEN
gefnd_log_pkg.debug('Entered in DBMS_SQL.fetch_rows(l_cursor) > 0');
dbms_sql.column_value(l_cursor, 1, l_offer_pay_method);
dbms_sql.column_value(l_cursor, 2, l_amount);
dbms_sql.column_value(l_cursor, 3, l_utiz_currency);
dbms_sql.column_value(l_cursor, 4, l_bill_to_site_use_id);
--
gefnd_log_pkg.debug('l_offer_pay_method', l_offer_pay_method);
gefnd_log_pkg.debug('l_amount', l_amount);
gefnd_log_pkg.debug('l_utiz_currency', l_utiz_currency);
gefnd_log_pkg.debug('l_bill_to_site_use_id', l_bill_to_site_use_id);
--
IF p_customer_id IS NULL THEN
dbms_sql.column_value(l_cursor, 5, l_cust_account_id);
IF l_frequency_period IN ('S', 'C', 'O') THEN
dbms_sql.column_value(l_cursor, 6, l_frequency_group);
l_funds_util_flt.frequency_group := l_frequency_group;
END IF;
ELSE
l_cust_account_id := p_customer_id;
IF l_frequency_period IN ('S', 'C', 'O') THEN
dbms_sql.column_value(l_cursor, 5, l_frequency_group);
l_funds_util_flt.frequency_group := l_frequency_group;
END IF;
END IF;
--
IF nvl(l_group_by_offer, 'N') = 'Y' AND p_customer_id IS NULL THEN
dbms_sql.column_value(l_cursor, 6, l_plan_id);
l_funds_util_flt.activity_id := l_plan_id;
--
IF l_frequency_period IN ('S', 'C', 'O') THEN
dbms_sql.column_value(l_cursor, 7, l_frequency_group);
l_funds_util_flt.frequency_group := l_frequency_group;
END IF;
ELSIF nvl(l_group_by_offer, 'N') = 'Y' AND p_customer_id IS NOT NULL THEN
dbms_sql.column_value(l_cursor, 5, l_plan_id);
l_funds_util_flt.activity_id := l_plan_id;
--
IF l_frequency_period IN ('S', 'C', 'O') THEN
dbms_sql.column_value(l_cursor, 6, l_frequency_group);
l_funds_util_flt.frequency_group := l_frequency_group;
END IF;
ELSIF (nvl(l_group_by_offer, 'N') = 'N' AND p_customer_id IS NOT NULL AND p_plan_id IS NOT NULL) THEN
l_funds_util_flt.activity_id := p_plan_id;
ELSIF (p_fund_id IS NOT NULL AND p_plan_id IS NULL) AND l_freq_tbl.count > 0 THEN
l_funds_util_flt.activity_id := l_freq_tbl(j).plan_id;
ELSE
l_funds_util_flt.activity_id := NULL;
END IF;
-- FXGL Enhancement : Add utiz_currency_code to l_funds_util_flt
-- This is a required filter
-- This will ensure assoc happens for each currency line
l_funds_util_flt.utiz_currency_code := l_utiz_currency;
-- In case of buying group/related customer accruals,
-- the amount can be paid either to buying group/related customer or
-- to claiming customer based on p_pay_to_customer.
IF p_pay_to_customer = 'RELATED' THEN
IF p_related_cust_account_id IS NOT NULL THEN
l_cust_account_id := p_related_cust_account_id;
END IF;
END IF;
--
l_offers_found := TRUE;
--
BEGIN
SAVEPOINT autopay_cust;
--patch start
gn_err_cnt:=0;--ver 1.8
gv_clm_err_msg:= NULL; --ver 1.8
fnd_file.put_line(fnd_file.output, 'Cust Account ID: '|| l_cust_account_id);
fnd_file.put_line(fnd_file.output, 'Bill To Site Use ID: '|| l_bill_to_site_use_id);
--patch end
--Added for 3853 to make autopay flag to true/false
---OEPREP-3853 To check Frequency , Frequency Unit Based on AutoPay Flag
--Version 1.1 changes start
gefnd_log_pkg.debug('QP List Header ID', l_funds_util_flt.activity_id);
IF l_funds_util_flt.activity_id IS NOT NULL THEN
OPEN c_offer_cur(l_funds_util_flt.activity_id);
FETCH c_offer_cur INTO l_offer_rec;
CLOSE c_offer_cur;
END IF;
--
lv_autopay_flag := l_offer_rec.autopay_flag;
lv_frequency := l_offer_rec.autopay_frequency;
lv_frequency_unit := l_offer_rec.autopay_frequency_unit;
--
gefnd_log_pkg.debug('autopay_flag', l_offer_rec.autopay_flag);
gefnd_log_pkg.debug('frequency', lv_frequency);
gefnd_log_pkg.debug('frequency_unit', lv_frequency_unit);
gefnd_log_pkg.debug('lv_autopay_flag', lv_autopay_flag);
--
IF lv_autopay_flag = 'Y' THEN
l_autopay_flag := fnd_api.g_true;
l_customer_info.autopay_flag := fnd_api.g_true;
IF lv_frequency_unit IS NOT NULL AND lv_frequency IS NOT NULL THEN
l_autopay_periodicity := lv_frequency;
l_autopay_periodicity_type := lv_frequency_unit;
END IF;
ELSE
l_autopay_flag := fnd_api.g_false;
END IF;
fnd_file.put_line(fnd_file.output, rpad('Cust Account ID', 40, ' ') || ': ' || l_cust_account_id);
--Ended for 3853 to make autopay flag to true/false
--Version 1.1 changes end
IF l_cust_account_id IS NOT NULL THEN
--R12.1 enhancement the call should once per site instead of onec per account.
--patch start
--IF l_prev_site_use_id IS NULL OR l_bill_to_site_use_id <> l_prev_site_use_id THEN
-- nvl on site_use_id check added for bug 33043476
IF (l_prev_site_use_id IS NULL) OR (nvl(l_bill_to_site_use_id,-9999) <> nvl(l_prev_site_use_id,-9999)) THEN
--patch end
l_cust_name_num := NULL;
l_customer_info := NULL;
l_party_id := NULL;
-- Get customer information for log message purpose
OPEN csr_cust_name(l_cust_account_id);
FETCH csr_cust_name INTO l_cust_name_num;
CLOSE csr_cust_name;
--
OPEN c_get_site_name(l_bill_to_site_use_id);
FETCH c_get_site_name INTO l_site_name;
CLOSE c_get_site_name;
--
fnd_file.put_line(fnd_file.output, rpad('Bill To Site', 40, ' ') || ': ' || l_site_name);
-- 1. get trade profile by site_use_id
gefnd_log_pkg.debug('Bill to Site', l_site_name);
/*
If related cust account is given and pay to customer is RELATED, then check the trade profile of the related customer's bill to site.
If related cust account is given and pay to customer is CUSTOMER, then check the trade profile of the customer's primary bill to/cust account.
*/
IF ((p_related_cust_account_id IS NOT NULL AND p_pay_to_customer = 'RELATED') OR (p_related_cust_account_id IS NULL)) THEN
gefnd_log_pkg.debug('Fetching trade profile for related customer bill to site use. Bill to Site :' || l_site_name);
OPEN g_site_info_csr(l_bill_to_site_use_id);
FETCH g_site_info_csr INTO l_customer_info;
--
IF g_site_info_csr%NOTFOUND THEN
l_trade_prf_exist := FALSE;
ELSE
gefnd_log_pkg.debug('Trade Profile at Bill To site level');
fnd_file.put_line(fnd_file.output, rpad('Trade Profile', 40, ' ') || ': ' || 'Found at Bill To site level');
l_trade_prf_exist := TRUE;
END IF;
CLOSE g_site_info_csr;
ELSIF p_pay_to_customer = 'CUSTOMER' AND p_related_cust_account_id IS NOT NULL THEN
gefnd_log_pkg.debug('Fetching trade profile for customer primary bill to site. l_cust_account_id: ' || l_cust_account_id);
OPEN cust_info_csr(l_cust_account_id);
FETCH cust_info_csr INTO l_customer_info;
--
IF cust_info_csr%NOTFOUND THEN
l_trade_prf_exist := FALSE;
ELSE
gefnd_log_pkg.debug('Trade profile found. Primary Bill to Site');
fnd_file.put_line(fnd_file.output, rpad('Trade Profile', 40, ' ') || ': ' || 'Found at Primary Bill to Site level');
l_trade_prf_exist := TRUE;
END IF;
CLOSE cust_info_csr;
END IF;
-- 2. if trade profile is not there for site,
-- then get trade profile by account level
IF NOT l_trade_prf_exist THEN
gefnd_log_pkg.debug('l_cust_account_id: ' || l_cust_account_id);
OPEN g_customer_info_csr(l_cust_account_id);
FETCH g_customer_info_csr INTO l_customer_info;
--
IF g_customer_info_csr%NOTFOUND THEN
l_trade_prf_exist := FALSE;
ELSE
gefnd_log_pkg.debug('Trade Profile at Account level');
fnd_file.put_line(fnd_file.output, rpad('Trade Profile', 40, ' ') || ': ' || 'Found at Customer Account level');
l_trade_prf_exist := TRUE;
END IF;
CLOSE g_customer_info_csr;
END IF;
-- 3. if trade profile is not there for customer,
-- then get trade profile by party_id level
IF NOT l_trade_prf_exist THEN
OPEN csr_get_party_id(l_cust_account_id);
FETCH csr_get_party_id INTO l_party_id;
CLOSE csr_get_party_id;
--
IF l_party_id IS NOT NULL THEN
gefnd_log_pkg.debug('l_party_id: ' || l_party_id);
OPEN g_party_trade_info_csr(l_party_id);
FETCH g_party_trade_info_csr INTO l_customer_info;
--
IF g_party_trade_info_csr%NOTFOUND THEN
l_trade_prf_exist := FALSE;
ELSE
gefnd_log_pkg.debug('Trade Profile at party level');
fnd_file.put_line(fnd_file.output, rpad('Trade Profile', 40, ' ') || ': ' || 'Found at Party level');
l_trade_prf_exist := TRUE;
END IF;
CLOSE g_party_trade_info_csr;
END IF;
END IF;
--Added this code to fetch the primary bill to in case bill to site is NULL for accruals.
IF NOT l_trade_prf_exist THEN
gefnd_log_pkg.debug('l_cust_account_id: ' || l_cust_account_id);
OPEN cust_info_csr(l_cust_account_id);
FETCH cust_info_csr INTO l_customer_info;
--
IF cust_info_csr%NOTFOUND THEN
l_trade_prf_exist := FALSE;
ELSE
gefnd_log_pkg.debug('Trade profile found. Primary Bill to Site');
fnd_file.put_line(fnd_file.output, rpad('Trade Profile', 40, ' ') || ': ' || 'Found at Primary Bill to Site level');
l_trade_prf_exist := TRUE;
END IF;
CLOSE cust_info_csr;
END IF;
--
l_customer_info.cust_account_id := l_cust_account_id;
-- Added For Multi Currency - kpatro
-- l_customer_info.claim_currency := l_utiz_currency; --commented by ananjaya
-- Fix for ER#17740259
-- The payment method should be picked from offer first, then customer
-- trade profile otherwise from system parameter
IF (l_offer_pay_method IS NOT NULL) THEN
l_customer_info.payment_method := l_offer_pay_method;
ELSIF (l_customer_info.payment_method IS NULL) THEN
l_customer_info.payment_method := l_sys_pay_method;
END IF;
-- Fix for ER#17740259 :Add the validation for payable settlement method
/*IF (l_customer_info.payment_method IN ('CHECK', 'AP_DEBIT', 'AP_DEFAULT', 'EFT', 'WIRE') AND
NOT l_trade_prf_exist AND g_open_claim_status = 'N') THEN
gefnd_log_pkg.debug('Please enable customer trade profile for Customer ' || l_cust_name_num || ' and associate a Vendor for Payables settlement.');
fnd_message.set_name('OZF', 'OZF_ATPY_AP_PAYM_ERROR');
fnd_message.set_token('CUSTOMER_NAME', l_cust_name_num);
fnd_msg_pub.add;
RAISE fnd_api.g_exc_unexpected_error;
END IF;*/--commented as per ver 1.8
--
validate_customer_info(p_customer_info => l_customer_info, x_return_status => l_return_status);
-- skip this customer if we can not get all the info.
IF l_return_status = fnd_api.g_ret_sts_error OR l_return_status = fnd_api.g_ret_sts_unexp_error THEN
l_cust_info_invalid := TRUE;
gefnd_log_pkg.debug('l_cust_info_invalid just ' || l_return_status);
ELSE
l_cust_info_invalid := FALSE;
gefnd_log_pkg.debug('l_cust_info_invalid just2 ' || l_return_status);
END IF;
END IF; --end if for prev bill to <> current bill to
--
l_prev_site_use_id := l_bill_to_site_use_id;
--
IF p_customer_id IS NULL THEN
gefnd_log_pkg.debug('l_cust_name_num' || l_cust_name_num);
fnd_file.put_line(fnd_file.output, rpad('Customer Name', 40, ' ') || ': ' || l_cust_name_num);
END IF;
IF l_offer_pay_method IS NOT NULL THEN
OPEN csr_offer_pay_name(l_offer_pay_method);
FETCH csr_offer_pay_name INTO l_offer_pay_name;
CLOSE csr_offer_pay_name;
fnd_file.put_line(fnd_file.output, rpad('Offer Payment Method', 40, ' ') || ': ' || l_offer_pay_name);
END IF;
--
IF l_cust_info_invalid THEN
gefnd_log_pkg.debug('l_cust_info_invalid ');
END IF;
--If trade profile does not exist then ignore system parameter frequency setup
IF (l_trade_prf_exist OR (l_autopay_flag = fnd_api.g_true)) THEN
IF (l_trade_prf_exist AND l_customer_info.autopay_flag = fnd_api.g_true) OR
(NOT l_trade_prf_exist AND l_autopay_flag = fnd_api.g_true) THEN
gefnd_log_pkg.debug('Calling create_claim_for_cust');
gefnd_log_pkg.debug('l_bill_to_site_use_id', l_bill_to_site_use_id);
gefnd_log_pkg.debug('l_customer_info.site_use_id', l_customer_info.site_use_id);
l_funds_util_flt.bill_to_site_use_id := l_bill_to_site_use_id;
--IF l_customer_info.site_use_id IS NULL THEN
-- l_customer_info.site_use_id := l_bill_to_site_use_id;
--END IF;
--ver 1.8 start
IF (l_customer_info.payment_method IN ('CHECK', 'AP_DEBIT', 'AP_DEFAULT', 'EFT', 'WIRE') AND
NOT l_trade_prf_exist AND g_open_claim_status = 'N') THEN
gn_err_cnt:=gn_err_cnt+1;
gv_clm_err_msg := gv_clm_err_msg||gn_err_cnt||')'||'Customer Trade Profile Error ';
END IF;
--ver 1.8 end
gn_iter_cnt := gn_iter_cnt +1; --ver 1.8
IF(l_bill_to_site_use_id IS NOT NULL) THEN --ver 1.8 added if condition
create_claim_for_cust(p_customer_info => l_customer_info,
p_amount => l_amount,
p_mode => 'N',
p_set_of_books_id => l_set_of_books_id,
p_auto_reason_code_id => l_auto_reason_code_id,
p_auto_claim_type_id => l_auto_claim_type_id,
p_autopay_periodicity => l_autopay_periodicity,
p_autopay_periodicity_type => l_autopay_periodicity_type,
p_offer_payment_method => l_offer_pay_method,
p_sys_autopay_flag => l_autopay_flag,
p_sys_thres_amt => NULL,
p_sys_thres_curr => NULL,
p_sys_pay_method => l_sys_pay_method,
p_funds_util_flt => l_funds_util_flt,
p_plan_id => l_funds_util_flt.activity_id,
p_bill_to_site_use_id => l_bill_to_site_use_id,
x_claim_id => l_claim_id,
x_return_status => l_return_status);
gefnd_log_pkg.debug('l_claim_id', l_claim_id);
gefnd_log_pkg.debug('l_return_status', l_return_status);
IF l_return_status = fnd_api.g_ret_sts_error AND l_claim_id=-10 THEN
RAISE g_sf_usr_exp; --ver 1.6
l_success := 'N';
ELSIF l_return_status = fnd_api.g_ret_sts_error THEN
RAISE fnd_api.g_exc_error;
l_success := 'N';
ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
RAISE fnd_api.g_exc_unexpected_error;
l_success := 'N';
ELSE
IF l_claim_id IS NOT NULL THEN
l_success := 'Y';
ELSE
l_success := 'N';
END IF;
END IF;
--
l_freq_tbl(j).claim_id := l_claim_id;
--
-- Store trade profile for later updation
IF l_trade_prf_exist AND l_frequency_period = 'C' AND l_success = 'Y' THEN
i := i + 1;
l_trd_prof_tbl(i).trade_profile_id := l_customer_info.trade_profile_id;
l_trd_prof_tbl(i).frequency := l_customer_info.autopay_periodicity;
l_trd_prof_tbl(i).frequency_unit := l_customer_info.autopay_periodicity_type;
ELSIF l_frequency_period = 'O' AND l_success = 'Y' THEN
IF (chk_global_offer(l_freq_tbl(j).plan_id) = 'G' AND p_org_id IS NULL) OR
chk_global_offer(l_freq_tbl(j).plan_id) = 'N' THEN
l_freq_tbl(j).upd_last_pd_dt_flag := 'Y';
END IF;
ELSIF l_frequency_period = 'S' AND l_success = 'Y' THEN
l_upd_last_pd_dt_flag := 'Y';
END IF;
fnd_file.put_line(fnd_file.output, rpad('Status', 40, ' ') || ': ' || 'SUCCESS.');
fnd_file.put_line(fnd_file.output, '');
ozf_utility_pvt.write_conc_log;
ELSE
gefnd_log_pkg.debug('Bill to site not exists for customer ' || l_cust_name_num);
l_message := 'Bill to site does not exists at account level for - '|| l_cust_name_num;
add_message(p_message_text => l_message,
p_cust_account_id => l_cust_account_id,
p_list_header_id => l_funds_util_flt.activity_id);
gv_offer_mail_flag := 'Y';
END IF;--ver 1.8
ELSE
gefnd_log_pkg.debug('Start AUTOPAY for customer ' || l_cust_name_num);
l_message := 'Autopay flag is not turned on in Trade Profile for customer ' || l_cust_name_num || ' or Autopay flag is not enabled in System Parameters';
add_message(p_message_text => l_message,
p_cust_account_id => l_cust_account_id,
p_list_header_id => l_funds_util_flt.activity_id);
gefnd_log_pkg.error(l_message);
fnd_file.put_line(fnd_file.output, rpad('Status', 40, ' ') || ': ' || 'FAILED. ' || l_message);
fnd_file.put_line(fnd_file.output, '');
END IF;
ELSE
gefnd_log_pkg.debug('Start AUTOPAY for customer ' || l_cust_name_num);
l_message := 'Trade Profile is not existing for customer ' || l_cust_name_num || ' or System Parameter Autopay is not enabled';
add_message(p_message_text => l_message,
p_cust_account_id => l_cust_account_id,
p_list_header_id => l_funds_util_flt.activity_id);
fnd_file.put_line(fnd_file.output, rpad('Status', 40, ' ') || ': ' || 'FAILED. ' || l_message);
fnd_file.put_line(fnd_file.output, '');
END IF;
END IF;
EXCEPTION
WHEN g_sf_usr_exp THEN --Added exception for ver 1.6
ROLLBACK TO autopay_cust;
ozf_utility_pvt.write_conc_log;
l_message := fnd_msg_pub.get(fnd_msg_pub.count_msg, fnd_api.g_false);
fnd_file.put_line(fnd_file.output, rpad('Status', 40, ' ') || ': ' || 'FAILED. ' || l_message);
fnd_file.put_line(fnd_file.output, '');
WHEN fnd_api.g_exc_error THEN
ROLLBACK TO autopay_cust;
ozf_utility_pvt.write_conc_log;
l_message := fnd_msg_pub.get(fnd_msg_pub.count_msg, fnd_api.g_false);
add_message(p_message_text => l_message,
p_cust_account_id => l_cust_account_id,
p_list_header_id => l_funds_util_flt.activity_id);
fnd_file.put_line(fnd_file.output, rpad('Status', 40, ' ') || ': ' || 'FAILED. ' || l_message);
fnd_file.put_line(fnd_file.output, '');
WHEN fnd_api.g_exc_unexpected_error THEN
ROLLBACK TO autopay_cust;
ozf_utility_pvt.write_conc_log;
l_message := fnd_msg_pub.get(fnd_msg_pub.count_msg, fnd_api.g_false);
add_message(p_message_text => l_message,
p_cust_account_id => l_cust_account_id,
p_list_header_id => l_funds_util_flt.activity_id);
fnd_file.put_line(fnd_file.output, rpad('Status', 40, ' ') || ': ' || 'FAILED. ' || l_message);
fnd_file.put_line(fnd_file.output, '');
WHEN OTHERS THEN
ROLLBACK TO autopay_cust;
IF ozf_debug_low_on THEN
fnd_message.set_name('OZF', 'OZF_API_DEBUG_MESSAGE');
fnd_message.set_token('TEXT', SQLERRM);
fnd_msg_pub.add;
END IF;
ozf_utility_pvt.write_conc_log;
l_message := SQLCODE || SQLERRM;
add_message(p_message_text => l_message,
p_cust_account_id => l_cust_account_id,
p_list_header_id => l_funds_util_flt.activity_id);
fnd_file.put_line(fnd_file.output, rpad('Status', 40, ' ') || ': ' || 'FAILED. ' || l_message);
fnd_file.put_line(fnd_file.output, '');
END;
ELSE
ozf_utility_pvt.write_conc_log;
fnd_file.put_line(fnd_file.output, '');
IF NOT l_offers_found THEN
add_message(p_message_text => 'No eligible offers found to process.');
END IF;
EXIT;
END IF;
END LOOP;
--
dbms_sql.close_cursor(l_cursor);
-- update the trade_profiles together
IF l_frequency_period = 'C' THEN
FOR i IN 1 .. l_trd_prof_tbl.count
LOOP
l_paid_date := get_paid_date(l_trd_prof_tbl(i).frequency_unit, l_trd_prof_tbl(i).frequency, l_autopay_date);
UPDATE ozf_cust_trd_prfls_all
SET last_paid_date = l_paid_date
WHERE trade_profile_id = l_trd_prof_tbl(i).trade_profile_id;
END LOOP;
END IF;
-- Write all messages to a log
ozf_utility_pvt.write_conc_log;
END LOOP; -- end loop for offer frequency
--
IF l_frequency_period = 'O' AND l_freq_tbl.count > 0 THEN
FOR i IN l_freq_tbl.first .. l_freq_tbl.last
LOOP
IF nvl(l_freq_tbl(i).upd_last_pd_dt_flag, 'N') = 'Y' AND l_freq_tbl(i).claim_id IS NOT NULL THEN
UPDATE ozf_offers
SET last_paid_date = l_freq_tbl(i).paid_date
WHERE qp_list_header_id = l_freq_tbl(i).plan_id;
END IF;
END LOOP;
END IF;
fnd_file.put_line(fnd_file.output, '*---------------------------------------------------------------------------------------------*');
fnd_file.put_line(fnd_file.output, 'Execution Status: Successful');
fnd_file.put_line(fnd_file.output, 'Execution Ends On: ' || to_char(SYSDATE, 'MM-DD-YYYY HH24:MI:SS'));
fnd_file.put_line(fnd_file.output, '*---------------------------------------------------------------------------------------------*');
EXCEPTION
WHEN fnd_api.g_exc_error THEN
ROLLBACK TO autopay;
ozf_utility_pvt.write_conc_log;
errbuf := l_msg_data;
retcode := 2;
fnd_file.put_line(fnd_file.output, 'Execution Status: Failure (Error:' || fnd_msg_pub.get(1, fnd_api.g_false) || ')');
fnd_file.put_line(fnd_file.output, 'Execution Ends On: ' || to_char(SYSDATE, 'MM-DD-YYYY HH24:MI:SS'));
fnd_file.put_line(fnd_file.output, '*---------------------------------------------------------------------------------------------*');
gefnd_log_pkg.unexpected('Unexpected error occured');
WHEN fnd_api.g_exc_unexpected_error THEN
ROLLBACK TO autopay;
ozf_utility_pvt.write_conc_log;
errbuf := l_msg_data;
retcode := 1; -- show status as warning if claim type/reason is missing, Fix for 5158782
fnd_file.put_line(fnd_file.output, 'Execution Status: ( Warning:' || fnd_msg_pub.get(1, fnd_api.g_false) || ')');
fnd_file.put_line(fnd_file.output, 'Execution Ends On: ' || to_char(SYSDATE, 'MM-DD-YYYY HH24:MI:SS'));
fnd_file.put_line(fnd_file.output, '*---------------------------------------------------------------------------------------------*');
gefnd_log_pkg.unexpected('Unexpected error occured');
WHEN OTHERS THEN
ROLLBACK TO autopay;
ozf_utility_pvt.write_conc_log;
errbuf := l_msg_data;
retcode := 2;
fnd_file.put_line(fnd_file.output, 'Execution Status: Failure (Error:' || SQLCODE || SQLERRM || ')');
fnd_file.put_line(fnd_file.output, 'Execution Ends On: ' || to_char(SYSDATE, 'MM-DD-YYYY HH24:MI:SS'));
fnd_file.put_line(fnd_file.output, '*---------------------------------------------------------------------------------------------*');
gefnd_log_pkg.unexpected('Unexpected error occured');
END;
--
IF l_frequency_period = 'S' THEN
IF nvl(l_upd_last_pd_dt_flag, 'N') = 'Y' THEN
gefnd_log_pkg.debug('l_org_id(m) Last paid date update' || l_org_id(m));
UPDATE ozf_sys_parameters_all SET last_paid_date = l_paid_date WHERE org_id = l_org_id(m);
END IF;
END IF;
END LOOP;
/*--Added below blocks for ver 1.7
BEGIN
UTL_FILE.fclose (gv_nt_file_handle);
EXCEPTION
WHEN OTHERS THEN
gefnd_log_pkg.debug ('Unexpected error while closing the Flat file:- '|| SQLCODE|| '-' || SQLERRM);
RAISE fnd_api.g_exc_unexpected_error;
END;*/--commented for ver 1.8
--Ver 1.8 Start:
IF (gn_note_type_cnt<>0) THEN
BEGIN
SELECT SUBSTR (VALUE, 1, INSTR (VALUE, ',', 1) - 1)
|| '/outbound/chrmoffers'
INTO lv_file_path
FROM v$parameter
WHERE name LIKE 'utl_file_dir%';
--to mail id list from lookup
FOR to_rec in (SELECT MEANING FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE='GEOZF_APL_AUTOPAY_MAIL_LT'
AND LANGUAGE='US' AND ENABLED_FLAG='Y' AND TAG='TO_MAIL' AND DESCRIPTION='USER_MAIL'
AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE - 1) AND NVL(END_DATE_ACTIVE, SYSDATE + 1))
LOOP
lv_to_mail:=lv_to_mail||','||to_rec.MEANING;
END LOOP;
--cc mail id list from lookup
FOR cc_rec in (SELECT MEANING FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE='GEOZF_APL_AUTOPAY_MAIL_LT'
AND LANGUAGE='US' AND ENABLED_FLAG='Y' AND TAG='CC_MAIL' AND DESCRIPTION='USER_MAIL'
AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE - 1) AND NVL(END_DATE_ACTIVE, SYSDATE + 1))
LOOP
lv_cc_mail1:=lv_cc_mail1||','||cc_rec.MEANING;
END LOOP;
lv_to_mail := rtrim(ltrim(lv_to_mail, ','), ',');
lv_cc_mail1 := rtrim(ltrim(lv_cc_mail1, ','), ',');
--for checking if requestor, inputter, sales finance there in lookup or not
SELECT COUNT(*) INTO ln_req_mail_cnt FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE='GEOZF_APL_AUTOPAY_MAIL_LT'
AND LANGUAGE='US' AND ENABLED_FLAG='Y' AND LOOKUP_CODE='REQUESTOR' AND DESCRIPTION='OFFER_USER'
AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE - 1) AND NVL(END_DATE_ACTIVE, SYSDATE + 1);
SELECT COUNT(*) INTO ln_inp_mail_cnt FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE='GEOZF_APL_AUTOPAY_MAIL_LT'
AND LANGUAGE='US' AND ENABLED_FLAG='Y' AND LOOKUP_CODE='INPUTTER' AND DESCRIPTION='OFFER_USER'
AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE - 1) AND NVL(END_DATE_ACTIVE, SYSDATE + 1);
SELECT COUNT(*) INTO ln_sf_mail_cnt FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE='GEOZF_APL_AUTOPAY_MAIL_LT'
AND LANGUAGE='US' AND ENABLED_FLAG='Y' AND LOOKUP_CODE='SALES FINANCE' AND DESCRIPTION='OFFER_USER'
AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE - 1) AND NVL(END_DATE_ACTIVE, SYSDATE + 1);
IF (ln_req_mail_cnt=1) THEN
SELECT TAG INTO lv_req_tag FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE='GEOZF_APL_AUTOPAY_MAIL_LT'
AND LANGUAGE='US' AND ENABLED_FLAG='Y' AND LOOKUP_CODE='REQUESTOR' AND DESCRIPTION='OFFER_USER'
AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE - 1) AND NVL(END_DATE_ACTIVE, SYSDATE + 1);
END IF;
IF (ln_inp_mail_cnt=1) THEN
SELECT TAG INTO lv_inp_tag FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE='GEOZF_APL_AUTOPAY_MAIL_LT'
AND LANGUAGE='US' AND ENABLED_FLAG='Y' AND LOOKUP_CODE='INPUTTER' AND DESCRIPTION='OFFER_USER'
AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE - 1) AND NVL(END_DATE_ACTIVE, SYSDATE + 1);
END IF;
IF (ln_sf_mail_cnt=1) THEN
SELECT TAG INTO lv_sf_tag FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE='GEOZF_APL_AUTOPAY_MAIL_LT'
AND LANGUAGE='US' AND ENABLED_FLAG='Y' AND LOOKUP_CODE='SALES FINANCE' AND DESCRIPTION='OFFER_USER'
AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE - 1) AND NVL(END_DATE_ACTIVE, SYSDATE + 1);
END IF;
FOR req_rec in c_requestor_cur
loop
lv_support_user_mail:=lv_to_mail;
lv_cc_mail:=lv_cc_mail1;
IF(ln_req_mail_cnt=1) THEN
IF(lv_req_tag='TO_MAIL') THEN
lv_support_user_mail := lv_support_user_mail||','||req_rec.REQUESTOR_MAIL;
ELSE
lv_cc_mail := lv_cc_mail||','||req_rec.REQUESTOR_MAIL;
END IF;
END IF;
IF(ln_inp_mail_cnt=1) THEN
IF(lv_inp_tag='TO_MAIL') THEN
for i in (select distinct INPUTTER_MAIL from GEOZF_APL_AUTOPAY_CLM_GTBL where REQ_RESOURCE_ID=req_rec.REQ_RESOURCE_ID)
loop
lv_support_user_mail := lv_support_user_mail||','||i.INPUTTER_MAIL;
end loop;
ELSE
for i in (select distinct INPUTTER_MAIL from GEOZF_APL_AUTOPAY_CLM_GTBL where REQ_RESOURCE_ID=req_rec.REQ_RESOURCE_ID)
loop
lv_cc_mail := lv_cc_mail||','||i.INPUTTER_MAIL;
end loop;
END IF;
END IF;
IF(ln_sf_mail_cnt=1) THEN
IF(lv_sf_tag='TO_MAIL') THEN
for j in (select distinct SALES_FINANCE_MAIL from GEOZF_APL_AUTOPAY_CLM_GTBL where REQ_RESOURCE_ID=req_rec.REQ_RESOURCE_ID)
loop
lv_support_user_mail := lv_support_user_mail||','||j.SALES_FINANCE_MAIL;
end loop;
ELSE
for j in (select distinct SALES_FINANCE_MAIL from GEOZF_APL_AUTOPAY_CLM_GTBL where REQ_RESOURCE_ID=req_rec.REQ_RESOURCE_ID)
loop
lv_cc_mail := lv_cc_mail||','||j.SALES_FINANCE_MAIL;
end loop;
END IF;
END IF;
lv_support_user_mail := rtrim(ltrim(lv_support_user_mail, ','), ',');
lv_cc_mail := rtrim(ltrim(lv_cc_mail, ','), ',');
--Below block to open file and write data for each requestor
BEGIN
gefnd_log_pkg.debug ('Before opening the file in Write mode');
gv_nt_file_handle:= UTL_FILE.fopen ('GE_CHRM_OFFERS',lv_file_name,'W');
gefnd_log_pkg.debug ('Before writing header');
UTL_FILE.put_line (
gv_nt_file_handle,
'Offer Code'
|| ','
|| 'Offer Name'
|| ','
|| 'Claim Number'
|| ','
|| 'Claim Amount'
|| ','
|| 'Requestor'
|| ','
|| 'Inputter'
|| ','
|| 'Error');
EXCEPTION
WHEN OTHERS THEN
UTL_FILE.fclose (gv_nt_file_handle);
gefnd_log_pkg.debug ('Unexpected error while writing the Flat file header:- '|| SQLCODE|| '-' || SQLERRM);
RAISE fnd_api.g_exc_unexpected_error;
END;
FOR req_dat in c_req_data_cur(req_rec.REQ_RESOURCE_ID)
loop
--Below block to write data into file
BEGIN
gefnd_log_pkg.debug ('Writing filedata of open claims from global temp table');
UTL_FILE.put_line (
gv_nt_file_handle,
req_dat.offer_code
|| ','
|| req_dat.offer_name
|| ','
|| req_dat.claim_number
|| ','
|| req_dat.claim_amount
|| ','
|| req_dat.requestor
|| ','
|| req_dat.inputter
|| ','
|| req_dat.error_msg);
EXCEPTION
WHEN OTHERS THEN
gefnd_log_pkg.debug ('Error while writing data from table into file for offer: '||req_dat.offer_code||'Error- '|| SQLERRM);
RAISE fnd_api.g_exc_unexpected_error;
END;
end loop;
--Below block to close the data file
BEGIN
UTL_FILE.fclose (gv_nt_file_handle);
EXCEPTION
WHEN OTHERS THEN
gefnd_log_pkg.debug ('Unexpected error while closing the Flat file:- '|| SQLCODE|| '-' || SQLERRM);
RAISE fnd_api.g_exc_unexpected_error;
END;
--Mail part
ln_conc_req_id :=
fnd_request.submit_request ('GEFND',
'GEFND_APL_GENERIC_EMAIL',
NULL,
SYSDATE,
FALSE,
lv_support_user_mail, --p_email,
'Y',
lv_file_path, --lv_out_file_path,
lv_file_name, --lv_out_file_name,
lv_cc_mail,
'Autopay - Claims On-hold: Action needed', --p_email_subject,
'N',
lv_body, --p_email_body,
'',
'');
COMMIT;
IF (ln_conc_req_id <> 0)
THEN
LOOP
lb_finished :=
fnd_concurrent.wait_for_request (
request_id => ln_conc_req_id,
INTERVAL => 3,
max_wait => 10,
phase => lv_phase,
status => lv_status,
dev_phase => lv_request_phase,
dev_status => lv_request_status,
MESSAGE => lv_message);
EXIT
WHEN UPPER (lv_phase) = 'COMPLETED' OR UPPER (lv_status) IN ('CANCELLED', 'ERROR', 'TERMINATED');
END LOOP;
END IF;
end loop;
EXCEPTION
WHEN OTHERS THEN gefnd_log_pkg.debug ('Exception while sending email for requestors: '|| SQLCODE|| '-' || SQLERRM);
END;
END IF;
--Ver 1.8 End
--Mail part
/*IF (gn_note_type_cnt<>0) THEN
BEGIN
SELECT SUBSTR (VALUE, 1, INSTR (VALUE, ',', 1) - 1)
|| '/outbound/chrmoffers'
INTO lv_file_path
FROM v$parameter
WHERE name LIKE 'utl_file_dir%';
ln_conc_req_id :=
fnd_request.submit_request ('GEFND',
'GEFND_APL_GENERIC_EMAIL',
NULL,
SYSDATE,
FALSE,
lv_support_user_mail, --p_email,
'Y',
lv_file_path, --lv_out_file_path,
lv_file_name, --lv_out_file_name,
'',
'Autopay - Claims in Open Status - Pricing Approval Note Type Missing', --p_email_subject,
'N',
lv_body, --p_email_body,
'',
'');
COMMIT;
IF (ln_conc_req_id <> 0)
THEN
lb_finished :=
fnd_concurrent.wait_for_request (
request_id => ln_conc_req_id,
INTERVAL => 4,
max_wait => 0,
phase => lv_phase,
status => lv_status,
dev_phase => lv_request_phase,
dev_status => lv_request_status,
MESSAGE => lv_message);
END IF;
EXCEPTION
WHEN OTHERS
THEN
gefnd_log_pkg.debug (
'Exception at Note Type Email part:'
|| SQLCODE
|| '-'
|| SQLERRM);
END;
END IF;*/--commented as per ver 1.8
END IF;
-- Ver 1.4 ERPOEP-44805 Start
--utl_file.fgetattr('GE_CHRM_OFFERS',gv_file_name,gb_exists, gn_size, gn_blk_size);
/*
IF gn_size > 0 THEN
utl_file.fflush(gv_offer_claim_err_utl);
utl_file.fclose(gv_offer_claim_err_utl);
ELSE
gefnd_log_pkg.debug('Zero byte file is created so deleting the file: '||gv_file_name);
utl_file.fclose(gv_offer_claim_err_utl);
IF gv_offer_mail_flag = 'N' THEN
utl_file.fremove('GE_CHRM_OFFERS',gv_file_name);
END IF;
END IF;
--
IF gv_offer_mail_flag = 'Y' THEN
BEGIN
FOR email_rec IN email_cur
LOOP
gv_email_id_details := gv_email_id_details || ',' || email_rec.meaning;
END LOOP;
BEGIN
SELECT directory_path INTO gv_file_location FROM all_directories WHERE directory_name = 'GE_CHRM_OFFERS';
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line(fnd_file.log,
'Exception at the time of getting file path: ' || SQLERRM);
END;
IF gv_email_id_details IS NOT NULL THEN
gn_req_id := fnd_request.submit_request('GEFND', -- Application
'GEFND_GENERIC_EMAILBODY_PRG', -- Program Name
'GEFND Generic EMAIL Body Program', -- Program Description
SYSDATE, -- Start Time (optional)
FALSE, -- Sub Request
gv_email_id_details, -- TO_email_Address
gv_file_location, -- File Path
gv_file_name, -- File Name
'AutoPay ' || g_request_id -- Email Subject
);
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line(fnd_file.log,
'Exception at the time of sending email for claim creation failed offers: ' || SQLERRM);
END;
END IF;*/
FOR email_rec IN email_cur
LOOP
gv_email_id_details := gv_email_id_details || ',' || email_rec.meaning;
END LOOP;
--
gv_email_id_details := rtrim(ltrim(gv_email_id_details, ','), ',');
--
gefnd_log_pkg.debug('gv_email_id_details', gv_email_id_details);
send_report(p_email_address => gv_email_id_details);
-- Ver 1.4 ERPOEP-44805 End
gefnd_log_pkg.pop(lv_func);
EXCEPTION
WHEN fnd_api.g_exc_unexpected_error THEN
l_message := 'Unexpected error occured during AutoPay process: ' || SQLERRM;
gefnd_log_pkg.unexpected(l_message);
add_message(p_message_text => l_message);
retcode := 2;
fnd_file.put_line(fnd_file.output, 'Execution Status: ( Error:' || fnd_msg_pub.get(fnd_msg_pub.count_msg, fnd_api.g_false) || ')');
fnd_file.put_line(fnd_file.output, 'Execution Ends On: ' || to_char(SYSDATE, 'MM-DD-YYYY HH24:MI:SS'));
fnd_file.put_line(fnd_file.output, '*---------------------------------------------------------------------------------------------*');
gefnd_log_pkg.pop(lv_func);
END start_autopay;
END geozf_apl_autopay_pkg;
Telegram: DB developers channel