--AR模块常用函数
FUNCTION get_fnd_user_name (
p_user_id IN NUMBER )
return VARCHAR2 IS CURSOR c_user_name IS
SELECT user_name
FROM fnd_user
WHERE user_id = p_user_id
AND sysdate between start_date and nvl(end_date,SYSDATE); l_user_name fnd_user.user_name%type;
BEGIN OPEN c_user_name; FETCH c_user_name INTO l_user_name; IF c_user_name%NOTFOUND THEN
l_user_name := null;
END IF; CLOSE c_user_name; RETURN l_user_name;
END; FUNCTION get_person_based_on_resource ( l_resource_id IN NUMBER)
return NUMBER IS
l_person_id per_people_f.person_id%type;
CURSOR c_person_id IS
SELECT c.source_id
FROM jtf_rs_resource_extns c
WHERE c.category = 'EMPLOYEE'
AND c.resource_id = l_resource_id; BEGIN OPEN c_person_id; FETCH c_person_id INTO l_person_id; IF c_person_id%NOTFOUND THEN
l_person_id := null;
END IF; CLOSE c_person_id; return l_person_id; END; /*========================================================================
| PUBLIC FUNCTION
| get_limit_currency()
| DESCRIPTION
| This function takes in some parameters and fills in the appropriate
| values regarding which currency is returned.
|
| PSEUDO CODE/LOGIC
|
| PARAMETERS
| p_party_id IN Party Id
| p_cust_account_id IN Customer Account Id
| p_cust_acct_site_id IN Customer Account Site Id
| p_trx_currency_code IN Transaction Currency Code
|
| KNOWN ISSUES
|
| NOTES
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 10-Jun-2003 M.Senthil Created
|
*=======================================================================*/
PROCEDURE get_limit_currency(
p_party_id IN NUMBER,
p_cust_account_id IN NUMBER,
p_cust_acct_site_id IN NUMBER,
p_trx_currency_code IN VARCHAR2,
p_limit_curr_code OUT nocopy VARCHAR2,
p_trx_limit OUT nocopy NUMBER,
p_overall_limit OUT nocopy NUMBER,
p_cust_acct_profile_amt_id OUT nocopy NUMBER,
p_global_exposure_flag OUT nocopy VARCHAR2,
p_include_all_flag OUT nocopy VARCHAR2,
p_usage_curr_tbl OUT nocopy HZ_CREDIT_USAGES_PKG.curr_tbl_type,
p_excl_curr_list OUT nocopy VARCHAR2
) IS l_entity_type VARCHAR2(30);
l_entity_id NUMBER(15);
l_limit_currency_code ar_cmgt_credit_requests.limit_currency%type;
l_analysis_level VARCHAR2(1); BEGIN
l_analysis_level := AR_CMGT_UTIL.find_analysis_level(p_party_id,
p_cust_account_id, p_cust_acct_site_id);
IF ( l_analysis_level = 'P')
THEN
l_entity_type := 'PARTY';
l_entity_id := p_party_id;
ELSIF ( l_analysis_level = 'A')
THEN
l_entity_type := 'CUSTOMER';
l_entity_id := p_cust_account_id;
ELSIF ( l_analysis_level = 'S')
THEN
l_entity_type := 'SITE';
l_entity_id := p_cust_acct_site_id;
END IF; hz_credit_usages_pkg.Get_Limit_Currency_usages (
p_entity_type => l_entity_type,
p_entity_id => l_entity_id,
p_trx_curr_code => p_trx_currency_code,
x_limit_curr_code => p_limit_curr_code,
x_trx_limit => p_trx_limit,
x_overall_limit => p_overall_limit,
x_cust_acct_profile_amt_id => p_cust_acct_profile_amt_id,
x_global_exposure_flag => p_global_exposure_flag,
x_include_all_flag => p_include_all_flag,
x_usage_curr_tbl => p_usage_curr_tbl,
x_excl_curr_list => p_excl_curr_list); END; FUNCTION get_score_summary(p_case_folder_id IN NUMBER)
RETURN NUMBER IS
l_score_total NUMBER := 0;
BEGIN SELECT SUM(score)
INTO l_score_total
FROM ar_cmgt_cf_dtls
WHERE case_folder_id=p_case_folder_id; RETURN l_score_total;
EXCEPTION
WHEN others THEN
RETURN l_score_total; END; FUNCTION get_requestor_name(p_requestor_id IN NUMBER)
RETURN VARCHAR2 IS l_requestor_name per_all_people_f.full_name%TYPE; CURSOR crequestorName IS
SELECT FULL_NAME
FROM PER_ALL_PEOPLE_F
WHERE sysdate between effective_start_date and effective_end_date
and person_id = p_requestor_id; BEGIN IF p_requestor_id IS NOT NULL THEN OPEN cRequestorName; FETCH cRequestorName INTO l_requestor_name; CLOSE cRequestorName; END IF; RETURN l_requestor_name; END; --==========================================================================
-- FUNCTION NAME:
--
-- Get_Arinvoice_Amount Public
--
-- DESCRIPTION:
--
-- This Function is to get taxable amount of an AR transaction per VAT tax
-- type and GT currency code defind in GTA 'system options' form
--
-- PARAMETERS:
-- In: p_org_id identifier of operating unit
-- p_customer_trx_id identifier of AR transaction
--
-- Return: NUMBER
--
-- DESIGN REFERENCES:
-- GTA_Reports_TD.doc
--
-- CHANGE HISTORY:
--
-- 8-MAY-2005: Qiang Li Created
-- 25-Nov-2005: Donghai Wang update code due to ebtax requirement
--===========================================================================
FUNCTION Get_Arinvoice_Amount
(p_org_id IN NUMBER
,p_customer_trx_id IN NUMBER
)
RETURN NUMBER
IS
l_procedure_name VARCHAR2(30) := 'Get_Arinvoice_Amount';
l_dbg_level NUMBER := fnd_log.g_current_runtime_level;
l_proc_level NUMBER := fnd_log.level_procedure; l_tax_type_code zx_lines.tax_type_code%TYPE;
l_gt_currency_code fnd_currencies.currency_code%TYPE;
l_ar_taxable_amount NUMBER; CURSOR c_tax_type_code
IS
SELECT
vat_tax_type_code
,gt_currency_code
FROM
ar_gta_system_parameters_all
WHERE org_id=p_org_id; CURSOR c_ar_taxable_amount
IS
SELECT
NVL(SUM(taxable_amt_tax_curr),0)
FROM
zx_lines
WHERE application_id = 222
AND trx_id=p_customer_trx_id
AND trx_level_type='LINE'
AND entity_code='TRANSACTIONS'
AND tax_type_code=l_tax_type_code
AND tax_currency_code=l_gt_currency_code
AND event_class_code IN ('INVOICE','CREDIT_MEMO','DEBIT_MEMO'); --Donghai Wang bug5212702 May-17,2006 BEGIN
--logging for debug
IF (l_proc_level >= l_dbg_level)
THEN
fnd_log.STRING(l_proc_level
,g_module_prefix || l_procedure_name || '.begin'
,'enter function');
END IF;--(l_proc_level >= l_dbg_level) --Get Vat tax type and GT currency code defined in GTA system options form
--for current operating unit
OPEN c_tax_type_code;
FETCH c_tax_type_code INTO l_tax_type_code,l_gt_currency_code;
CLOSE c_tax_type_code; --Get total taxable amount of lines for an AR transactions
OPEN c_ar_taxable_amount;
FETCH c_ar_taxable_amount INTO l_ar_taxable_amount;
CLOSE c_ar_taxable_amount; --logging for debug
IF (l_proc_level >= l_dbg_level)
THEN
fnd_log.STRING(l_proc_level
,g_module_prefix || l_procedure_name || '.end'
,'end function');
END IF; --(l_proc_level >= l_dbg_level) RETURN l_ar_taxable_amount;
END Get_Arinvoice_Amount; --==========================================================================
-- FUNCTION NAME:
--
-- Get_Arinvoice_Tax_Amount Public
--
-- DESCRIPTION:
--
-- This Function is to get tax amount of an AR transaction per VAT tax
-- type and GT currency code defind in GTA 'system options' form
--
-- PARAMETERS:
-- In: p_org_id identifier of operating unit
-- p_customer_trx_id identifier of AR transaction
--
-- Return: Number
--
-- DESIGN REFERENCES:
-- GTA_Reports_TD.doc
--
-- CHANGE HISTORY:
--
-- 8-MAY-2005: Qiang Li Created
-- 25-Nov-2005: Donghai Wang update code due to ebtax requirement
--===========================================================================
FUNCTION Get_Arinvoice_Tax_Amount
(p_org_id IN NUMBER
,p_customer_trx_id IN NUMBER
)
RETURN NUMBER
IS
l_procedure_name VARCHAR2(30) := 'Get_Arinvoice_Tax_Amount';
l_dbg_level NUMBER := fnd_log.g_current_runtime_level;
l_proc_level NUMBER := fnd_log.level_procedure; l_tax_type_code zx_lines.tax_type_code%TYPE;
l_gt_currency_code fnd_currencies.currency_code%TYPE;
l_ar_tax_amount NUMBER; CURSOR c_tax_type_code
IS
SELECT
vat_tax_type_code
,gt_currency_code
FROM
ar_gta_system_parameters_all
WHERE org_id=p_org_id; CURSOR c_ar_tax_amount
IS
SELECT
NVL(SUM(tax_amt_tax_curr),0)
FROM
zx_lines
WHERE application_id = 222
AND trx_id=p_customer_trx_id
AND trx_level_type='LINE'
AND entity_code='TRANSACTIONS'
AND tax_type_code=l_tax_type_code
AND tax_currency_code=l_gt_currency_code
AND event_class_code IN ('INVOICE','CREDIT_MEMO','DEBIT_MEMO'); --Donghai Wang bug5212702 May-17,2006; BEGIN
--logging for debug
IF (l_proc_level >= l_dbg_level)
THEN
fnd_log.STRING(l_proc_level
,g_module_prefix || l_procedure_name || '.begin'
,'enter function');
END IF;--(l_proc_level >= l_dbg_level) --Get Vat tax type and GT currency code defined in GTA system options form
--for current operating unit
OPEN c_tax_type_code;
FETCH c_tax_type_code INTO l_tax_type_code,l_gt_currency_code;
CLOSE c_tax_type_code; --Get total VAT tax amount of AR transaction
OPEN c_ar_tax_amount;
FETCH c_ar_tax_amount INTO l_ar_tax_amount ;
CLOSE c_ar_tax_amount; --logging for debug
IF (l_proc_level >= l_dbg_level)
THEN
fnd_log.STRING(l_proc_level
,g_module_prefix || l_procedure_name || '.end'
,'end function');
END IF; --(l_proc_level >= l_dbg_level) RETURN l_ar_tax_amount;
END Get_Arinvoice_Tax_Amount; --==========================================================================
-- PROCEDURE NAME:
--
-- Get_New_TRX_Num Private
--
-- DESCRIPTION:
--
-- This procedure is to get a new trx number
--
-- PARAMETERS:
-- In: p_trx_id Identifier of AR transaction
-- p_group_number Group number
-- p_version_number Version
-- p_org_id Identifier of operating unit
--
-- Out: x_gta_trx_number Number of GTA invoice
--
-- DESIGN REFERENCES:
-- GTA-TRANSFER-PROGRAM-TD.doc
--
-- CHANGE HISTORY:
--
-- 23-MAy-2005: Jim.zheng Creation
--
--===========================================================================
PROCEDURE get_new_trx_num
(p_trx_id IN VARCHAR2
,p_group_number IN VARCHAR2
,p_version_number IN VARCHAR2
,x_gta_trx_number OUT NOCOPY VARCHAR2
)
IS
boundary VARCHAR2(1) := '-'; BEGIN
x_gta_trx_number := p_trx_id || boundary || p_group_number || boundary ||
p_version_number;
END get_new_trx_num; --==========================================================================
-- FUNCTION NAME:
--
-- Format_Date Public
--
-- DESCRIPTION:
--
-- This funtion is to get appropriate format string for
-- a given date according the ICX_DATE_FORMAT_MASK profile
--
-- PARAMETERS:
-- In: p_date The date to be formate
--
-- Return: VARCHAR2
--
-- DESIGN REFERENCES:
-- GTA_Reports_TD.doc
--
-- CHANGE HISTORY:
--
-- 23-MAy-2005: Qiang Li Creation
--
--===========================================================================
FUNCTION format_date(p_date IN DATE) RETURN VARCHAR2 IS
l_procedure_name VARCHAR2(30) := 'Format_Date';
l_dbg_level NUMBER := fnd_log.g_current_runtime_level;
l_proc_level NUMBER := fnd_log.level_procedure;
l_ret VARCHAR(40); l_date_format fnd_profile_option_values.profile_option_value%TYPE := NULL; BEGIN
--logging for debug
IF (l_proc_level >= l_dbg_level)
THEN
fnd_log.STRING(l_proc_level
,g_module_prefix || l_procedure_name || '.begin'
,'enter function');
END IF; fnd_profile.get('ICX_DATE_FORMAT_MASK'
,l_date_format);
l_ret := to_char(p_date
,nvl(l_date_format
,'Rrrr-Mm-Dd')); --logging for debug
IF (l_proc_level >= l_dbg_level)
THEN
fnd_log.STRING(l_proc_level
,g_module_prefix || l_procedure_name || '.end'
,'end function');
END IF; RETURN l_ret;
END format_date; --==========================================================================
-- FUNCTION NAME:
--
-- Get_Primary_Phone_Number Public
--
-- DESCRIPTION:
--
-- This procedure is to get primary phone number for a given customer
--
-- PARAMETERS:
-- In: p_customer_id Customer identifier
--
-- Return: VARCHAR2
--
-- DESIGN REFERENCES:
-- GTA_Reports_TD.doc
--
-- CHANGE HISTORY:
--
-- 23-May-2005: Donghai Wang Created
-- 26-Jun-2006: Donghai Wang In the cursor c_phone_number, add sub
-- query to fetch party_id by
-- "bill to customer id" passed in,instead
-- of using "bill to customer id"
-- directly.
-- 21-May-2006 Donghai Wang Fix the bug 5263009
--
--===========================================================================
FUNCTION get_primary_phone_number
(p_customer_id IN NUMBER
)
RETURN VARCHAR2
IS
l_customer_id hz_parties.party_id%TYPE := p_customer_id;
l_phone_number hz_contact_points.phone_number%TYPE; --Fix bug 5263009, Donghai Wang
--Add the sub query to get party id by customer id
CURSOR c_phone_number
IS
SELECT
hcp.phone_number
FROM
hz_contact_points hcp
WHERE hcp.contact_point_type = 'PHONE'
AND hcp.owner_table_name = 'HZ_PARTIES'
AND hcp.owner_table_id = (SELECT
party_id
FROM
hz_cust_accounts_all
WHERE cust_account_id=l_customer_id
)
AND hcp.primary_flag = 'Y'; l_procedure_name VARCHAR2(30) := 'Get_Primary_Phone_Number';
l_dbg_level NUMBER := fnd_log.g_current_runtime_level;
l_proc_level NUMBER := fnd_log.level_procedure;
BEGIN --logging for debug
IF (l_proc_level >= l_dbg_level)
THEN
fnd_log.STRING(l_proc_level
,g_module_prefix || l_procedure_name || '.begin'
,'Enter function');
END IF; --l_proc_level>=l_dbg_level)
OPEN c_phone_number;
FETCH c_phone_number
INTO l_phone_number;
CLOSE c_phone_number; --logging for debug
IF (l_proc_level >= l_dbg_level)
THEN
fnd_log.STRING(l_proc_level
,g_module_prefix || l_procedure_name || '.End'
,'Exit function');
END IF; --l_proc_level>=l_dbg_level) RETURN(l_phone_number);
END get_primary_phone_number; --==========================================================================
-- FUNCTION NAME:
--
-- Get_Operatingunit Public
--
-- DESCRIPTION:
--
-- This function is to get operating unit for a given org_id
--
-- PARAMETERS:
-- In: p_org_id Identifier of Operating Unit
--
-- Return: VARCHAR2
--
-- DESIGN REFERENCES:
-- GTA_Reports_TD.doc
--
-- CHANGE HISTORY:
--
-- 23-MAy-2005: Qiang Li Creation
-- 26-Dec-2005: Qiang Li fix a performance issue
--=========================================================================
FUNCTION get_operatingunit(p_org_id IN NUMBER) RETURN VARCHAR2 IS
l_procedure_name VARCHAR2(30) := 'Get_OperatingUnit';
l_dbg_level NUMBER := fnd_log.g_current_runtime_level;
l_proc_level NUMBER := fnd_log.level_procedure; l_operating_unit hr_operating_units.NAME%TYPE;
CURSOR c_operating_unit IS
SELECT OTL.NAME
FROM HR_ALL_ORGANIZATION_UNITS O
, HR_ALL_ORGANIZATION_UNITS_TL OTL
WHERE O.ORGANIZATION_ID = OTL.ORGANIZATION_ID
AND OTL.LANGUAGE = userenv('LANG')
AND O.ORGANIZATION_ID = p_org_id; BEGIN
--logging for debug
IF (l_proc_level >= l_dbg_level)
THEN
fnd_log.STRING(l_proc_level
,g_module_prefix || l_procedure_name || '.begin'
,'enter function');
END IF; OPEN c_operating_unit;
FETCH
c_operating_unit
INTO
l_operating_unit; CLOSE c_operating_unit; --logging for debug
IF (l_proc_level >= l_dbg_level)
THEN
fnd_log.STRING(l_proc_level
,g_module_prefix || l_procedure_name || '.end'
,'end function');
END IF; RETURN(l_operating_unit);
END get_operatingunit; --==========================================================================
-- FUNCTION NAME:
--
-- Get_Customer_Name Public
--
-- DESCRIPTION:
--
-- This function is to get Customer name for a given customer id
--
-- PARAMETERS:
-- In: p_customer_id customer identifier
--
-- Return: VARCHAR2
--
-- DESIGN REFERENCES:
-- GTA_Reports_TD.doc
--
-- CHANGE HISTORY:
--
-- 23-MAy-2005: Qiang Li Creation
--
--=========================================================================
FUNCTION get_customer_name
(p_customer_id IN NUMBER)
RETURN VARCHAR2
IS
l_procedure_name VARCHAR2(30) := 'Get_Customer_Name';
l_dbg_level NUMBER := fnd_log.g_current_runtime_level;
l_proc_level NUMBER := fnd_log.level_procedure; l_customer_name hz_parties.party_name%TYPE;
CURSOR c_customer_name IS
SELECT
p.party_name
FROM
hz_parties p
,hz_cust_accounts a
WHERE a.cust_account_id = p_customer_id
AND p.party_id = a.party_id; BEGIN
--logging for debug
IF (l_proc_level >= l_dbg_level)
THEN
fnd_log.STRING(l_proc_level
,g_module_prefix || l_procedure_name || '.begin'
,'enter function');
END IF; OPEN c_customer_name; FETCH
c_customer_name
INTO
l_customer_name; CLOSE c_customer_name; --logging for debug
IF (l_proc_level >= l_dbg_level)
THEN
fnd_log.STRING(l_proc_level
,g_module_prefix || l_procedure_name || '.end'
,'end function');
END IF; RETURN(l_customer_name);
END get_customer_name; --==========================================================================
-- FUNCTION NAME:
--
-- Get_Arline_Amount Public
--
-- DESCRIPTION:
--
-- This function is used to get line amount per Golden Tax currency for
-- one AR line
--
--
-- PARAMETERS:
-- In: p_org_id identifier of operating unit
-- p_customer_trx_line_id AR line identifier
--
-- Return: NUMBER
--
-- DESIGN REFERENCES:
-- GTA_Reports_TD.doc
--
-- CHANGE HISTORY:
--
-- 13-Jun-2005: Donghai Wang Creation
-- 24-Nov-2005: Modify program logic to get line amount per Golden
-- Tax currency from the table zx_lines
--
--=========================================================================
FUNCTION Get_Arline_Amount
(p_org_id IN NUMBER
,p_customer_trx_line_id IN NUMBER
)
RETURN NUMBER
IS
l_tax_type_code zx_lines.tax_type_code%TYPE;
l_arline_amount NUMBER;
l_gt_currency_code fnd_currencies.currency_code%TYPE;
l_trx_id ra_customer_trx_all.customer_trx_id%TYPE; CURSOR c_tax_type_code
IS
SELECT
vat_tax_type_code
,gt_currency_code
FROM
ar_gta_system_parameters_all
WHERE org_id=p_org_id; --CURSOR c_ar_line_taxable_amount --Donghai Wang bug5212702 May-17,2006
CURSOR c_ar_line_taxable_amount(pc_trx_id NUMBER)--Donghai Wang bug5212702 May-17,2006
IS
SELECT
taxable_amt_tax_curr
FROM
zx_lines
WHERE trx_line_id=p_customer_trx_line_id
AND entity_code='TRANSACTIONS'
AND application_id = 222
AND trx_level_type='LINE'
AND tax_type_code=l_tax_type_code
AND tax_currency_code=l_gt_currency_code
AND event_class_code IN ('INVOICE','CREDIT_MEMO','DEBIT_MEMO')--Donghai Wang bug5212702 May-17,2006
AND trx_id=pc_trx_id
ORDER BY tax_line_id; l_dbg_level NUMBER := fnd_log.g_current_runtime_level;
l_proc_level NUMBER := fnd_log.level_procedure;
l_procedure_name VARCHAR2(30) := 'Get_Arline_Amount'; BEGIN
--logging for debug
IF (l_proc_level >= l_dbg_level)
THEN
fnd_log.STRING(l_proc_level
,g_module_prefix || l_procedure_name || '.begin'
,'Enter function');
END IF; --l_proc_level>=l_dbg_level) --Get Vat tax type defined in GTA system options form for current
--operating unit
OPEN c_tax_type_code;
FETCH c_tax_type_code INTO l_tax_type_code,l_gt_currency_code;
CLOSE c_tax_type_code; --Get taxable amount per Golden Tax Currency for one AR line
--Donghai Wang bug5212702 May-17,2006
--OPEN c_ar_line_taxable_amount; SELECT customer_trx_id
INTO l_trx_id
FROM ra_customer_trx_lines_all
WHERE customer_trx_line_id=p_customer_trx_line_id; OPEN c_ar_line_taxable_amount(l_trx_id);
--Donghai Wang bug5212702 May-17,2006 FETCH c_ar_line_taxable_amount INTO l_arline_amount;
CLOSE c_ar_line_taxable_amount; --logging for debug
IF (l_proc_level >= l_dbg_level)
THEN
fnd_log.STRING(l_proc_level
,g_module_prefix || l_procedure_name || '.End'
,'Exit function');
END IF; --l_proc_level>=l_dbg_level) RETURN(l_arline_amount); END Get_Arline_Amount; --==========================================================================
-- FUNCTION NAME:
--
-- Get_Arline_Vattax_Amount Public
--
-- DESCRIPTION:
--
-- This function is used to get VAT amount based on one AR line
-- per Golden Tax currency
--
-- PARAMETERS:
-- In: p_org_id Identifier of operating unit
-- p_customer_trx_line_id AR line identifier
--
-- Return: NUMBER
--
-- DESIGN REFERENCES:
-- GTA_Reports_TD.doc
--
-- CHANGE HISTORY:
--
-- 13-Jun-2005: Donghai Wang Creation
-- 24-Nov-2005: Donghai Wang Add a new parameter 'p_org_id' and
-- replace dummy code to real code
--
--=========================================================================
FUNCTION Get_Arline_Vattax_Amount
(p_org_id IN NUMBER
,p_customer_trx_line_id IN NUMBER
)
RETURN NUMBER
IS
l_tax_type_code zx_lines.tax_type_code%TYPE;
l_arline_vatamount NUMBER;
l_gt_currency_code fnd_currencies.currency_code%TYPE;
l_trx_id ra_customer_trx_all.customer_trx_id%TYPE;--Donghai Wang bug5212702 May-17,2006 CURSOR c_tax_type_code
IS
SELECT
vat_tax_type_code
,gt_currency_code
FROM
ar_gta_system_parameters_all
WHERE org_id=p_org_id; --CURSOR c_ar_line_vatamount--Donghai Wang bug5212702 May-17,2006
CURSOR c_ar_line_vatamount(pc_trx_id NUMBER)--Donghai Wang bug5212702 May-17,2006
IS
SELECT
tax_amt_tax_curr
FROM
zx_lines
WHERE trx_line_id=p_customer_trx_line_id
AND entity_code='TRANSACTIONS'
AND application_id = 222
AND trx_level_type='LINE'
AND tax_type_code=l_tax_type_code
AND tax_currency_code=l_gt_currency_code
AND event_class_code IN ('INVOICE','CREDIT_MEMO','DEBIT_MEMO')--Donghai Wang bug5212702 May-17,2006
AND trx_id=pc_trx_id
ORDER BY tax_line_id; l_dbg_level NUMBER := fnd_log.g_current_runtime_level;
l_proc_level NUMBER := fnd_log.level_procedure;
l_procedure_name VARCHAR2(30) := 'Get_Arline_Vattax_Amount'; BEGIN
--logging for debug
IF (l_proc_level >= l_dbg_level)
THEN
fnd_log.STRING(l_proc_level
,g_module_prefix || l_procedure_name || '.begin'
,'Enter function');
END IF; --l_proc_level>=l_dbg_level) --Get Vat tax type defined in GTA system options form for current
--operating unit
OPEN c_tax_type_code;
FETCH c_tax_type_code INTO l_tax_type_code,l_gt_currency_code;
CLOSE c_tax_type_code; --Get tax amount per Golden Tax Currency for one AR line --Donghai Wang bug5212702 May-17,2006 SELECT customer_trx_id
INTO l_trx_id
FROM ra_customer_trx_lines_all
WHERE customer_trx_line_id=p_customer_trx_line_id;
--OPEN c_ar_line_vatamount;
OPEN c_ar_line_vatamount(l_trx_id); --Donghai Wang bug5212702 May-17,2006 FETCH c_ar_line_vatamount INTO l_arline_vatamount;
CLOSE c_ar_line_vatamount; --logging for debug
IF (l_proc_level >= l_dbg_level)
THEN
fnd_log.STRING(l_proc_level
,g_module_prefix || l_procedure_name || '.End'
,'Exit function');
END IF; --l_proc_level>=l_dbg_level) RETURN(l_arline_vatamount);
END Get_Arline_Vattax_Amount; --==========================================================================
-- FUNCTION NAME:
--
-- Get_Arline_Vattax_Rate Public
--
-- DESCRIPTION:
--
-- This function is used to get VAT rate for one AR line
--
-- PARAMETERS:
-- In: p_org_id Identifier of Operating Unit
-- p_customer_trx_line_id AR line identifier
--
-- Return: NUMBER
--
-- DESIGN REFERENCES:
-- GTA_Reports_TD.doc
--
-- CHANGE HISTORY:
--
-- 13-Jun-2005: Donghai Wang Creation
-- 24-Nov-2005: Donghai Wang Add a new parameter 'p_org_id' and
-- replace dummy code to real code
--
--=========================================================================
FUNCTION Get_Arline_Vattax_Rate
(p_org_id IN NUMBER
,p_customer_trx_line_id IN NUMBER
)
RETURN NUMBER
IS
l_tax_type_code zx_lines.tax_type_code%TYPE;
l_tax_rate NUMBER;
l_gt_currency_code fnd_currencies.currency_code%TYPE;
l_trx_id ra_customer_trx_all.customer_trx_id%TYPE;--Donghai Wang bug5212702 May-17,2006 CURSOR c_tax_type_code
IS
SELECT
vat_tax_type_code
,gt_currency_code
FROM
ar_gta_system_parameters_all
WHERE org_id=p_org_id; --CURSOR c_ar_line_tax_rate --Donghai Wang bug5212702 May-17,2006
CURSOR c_ar_line_tax_rate(pc_trx_id NUMBER) --Donghai Wang bug5212702 May-17,2006
IS
SELECT
tax_rate
FROM
zx_lines
WHERE trx_line_id=p_customer_trx_line_id
AND entity_code='TRANSACTIONS'
AND application_id = 222
AND trx_level_type='LINE'
AND tax_type_code=l_tax_type_code
AND tax_currency_code=l_gt_currency_code
AND event_class_code IN ('INVOICE','CREDIT_MEMO','DEBIT_MEMO')--Donghai Wang bug5212702 May-17,2006
AND trx_id=pc_trx_id --Donghai Wang bug5212702 May-17,2006
ORDER BY tax_line_id; l_dbg_level NUMBER := fnd_log.g_current_runtime_level;
l_proc_level NUMBER := fnd_log.level_procedure;
l_procedure_name VARCHAR2(30) := 'Get_Arline_Vattax_Rate'; BEGIN
--logging for debug
IF (l_proc_level >= l_dbg_level)
THEN
fnd_log.STRING(l_proc_level
,g_module_prefix || l_procedure_name || '.begin'
,'Enter function');
END IF; --l_proc_level>=l_dbg_level) --Get Vat tax type defined in GTA system options form for current
--operating unit
OPEN c_tax_type_code;
FETCH c_tax_type_code INTO l_tax_type_code,l_gt_currency_code;
CLOSE c_tax_type_code; --Get tax rate for one AR line
--Donghai Wang bug5212702 May-17,2006
SELECT customer_trx_id
INTO l_trx_id
FROM ra_customer_trx_lines_all
WHERE customer_trx_line_id=p_customer_trx_line_id; --OPEN c_ar_line_tax_rate;
OPEN c_ar_line_tax_rate(l_trx_id);
--Donghai Wang bug5212702 May-17,2006 FETCH c_ar_line_tax_rate INTO l_tax_rate;
CLOSE c_ar_line_tax_rate; --logging for debug
IF (l_proc_level >= l_dbg_level)
THEN
fnd_log.STRING(l_proc_level
,g_module_prefix || l_procedure_name || '.End'
,'Exit function');
END IF; --l_proc_level>=l_dbg_level) RETURN(l_tax_rate/100);
END Get_Arline_Vattax_Rate; --==========================================================================
-- Procedure NAME:
--
-- get_bank_info Public
--
-- DESCRIPTION:
--
-- This function get bank infomations by cust_Trx_id, if the bank info from AR
-- is null. then get bank infomations by customer_id
--
-- PARAMETERS:
-- In:
-- p_customer_trx_id IN NUMBER
-- p_trxn_extension_id IN NUMBER
-- OUT:
-- x_bank_name OUT NOCOPY VARCHAR2
-- x_bank_branch_name OUT NOCOPY VARCHAR2
-- x_bank_account_name OUT NOCOPY VARCHAR2
-- x_bank_account_num OUT NOCOPY VARCHAR2
--
--
-- DESIGN REFERENCES:
--
--
-- CHANGE HISTORY:
--
-- 17-AUG-2005: JIM.Zheng Created
-- 31-Apr2009: Yao Zhang Changed for bug 8234250
-- 16-Jun-2009 Yao Zhang Changed for bug 8605196
--===========================================================================
PROCEDURE Get_Bank_Info
( p_customer_trx_id IN NUMBER
, p_org_id IN NUMBER
, x_bank_name OUT NOCOPY VARCHAR2
, x_bank_branch_name OUT NOCOPY VARCHAR2
, x_bank_account_name OUT NOCOPY VARCHAR2
, x_bank_account_num OUT NOCOPY VARCHAR2
)
IS
l_procedure_name VARCHAR2(30) := 'Get_Bank_Info'; l_bill_to_customer_id ra_customer_trx_all.bill_to_customer_id%TYPE;
----Yao Zhang add begin for bug#8404856
l_bill_to_site_use_id ra_customer_trx_all.bill_to_site_use_id%TYPE;
l_valid_customer_id ra_customer_trx_all.bill_to_customer_id%TYPE;
l_valid_site_use_id ra_customer_trx_all.bill_to_site_use_id%TYPE;
----Yao Zhang add end for bug#8404856 l_site_use_id hz_cust_site_uses.SITE_USE_ID%TYPE;
l_cust_acct_site_id hz_cust_acct_sites.CUST_ACCT_SITE_ID%TYPE;
l_currency_code ar_gta_system_parameters_all.gt_currency_code%TYPE;
l_error_string VARCHAR2(500); l_paying_customer_id ra_customer_trx_all.paying_customer_id%TYPE;
l_paying_site_use_id ra_customer_trx_all.paying_site_use_id%TYPE;
l_paying_site_id hz_cust_acct_sites.CUST_ACCT_SITE_ID%TYPE;
l_paying_party_id HZ_CUST_ACCOUNTS.party_id%TYPE;
l_ext_payer_id IBY_EXTERNAL_PAYERS_ALL.ext_payer_id%TYPE;
l_bank_account_name IBY_EXT_BANK_ACCOUNTS.bank_account_name%TYPE;
l_bank_account_num IBY_EXT_BANK_ACCOUNTS.bank_account_num%TYPE;
l_bank_id IBY_EXT_BANK_ACCOUNTS.bank_id%TYPE;
l_bank_branch_id IBY_EXT_BANK_ACCOUNTS.branch_id%TYPE;
l_bank_name HZ_PARTIES.party_name%TYPE;
l_bank_branch_name HZ_PARTIES.party_name%TYPE;
l_trxn_extension_id ra_customer_trx_all.payment_trxn_extension_id%TYPE; l_instrument_id IBY_EXT_BANK_ACCOUNTS.ext_bank_account_id%TYPE; BEGIN
IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
THEN
fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
, G_MODULE_PREFIX || l_procedure_name
,'begin Procedure. ');
END IF; BEGIN
SELECT
gt_currency_code
INTO
l_currency_code
FROM
ar_gta_system_parameters_all
WHERE org_id=p_org_id; EXCEPTION
WHEN no_data_found THEN
--report AR_GTA_MISSING_ERROR
fnd_message.set_name('AR', 'AR_GTA_MISSING_ERROR');
l_error_string := fnd_message.get();
-- output this error
fnd_file.put_line(fnd_file.output, '<?xml version="1.0" encoding="UTF-8" ?>
<TransferReport>
<ReportFailed>Y</ReportFailed>
<ReportFailedMsg>'||l_error_string||'</ReportFailedMsg>
<TransferReport>'); IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
THEN
fnd_log.STRING(fnd_log.LEVEL_UNEXPECTED
, G_MODULE_PREFIX || l_procedure_name
, l_error_string);
END IF;
RAISE;
END; BEGIN
SELECT
h.paying_customer_id
,h.paying_site_use_id
,h.payment_trxn_extension_id
--Yao Zhang add begin for bug#8404856
,h.bill_to_customer_id
,h.bill_to_site_use_id
--Yao Zhang add end for bug#8404856
INTO
l_paying_customer_id
, l_paying_site_use_id
, l_trxn_extension_id
--Yao Zhang add for bug#8404856
, l_bill_to_customer_id
, l_bill_to_site_use_id
--Yao Zhang add end for bug#8404856
FROM
ra_customer_trx_all h WHERE h.customer_trx_id = p_customer_trx_id ;
EXCEPTION
WHEN no_data_found THEN
IF(FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
THEN
fnd_log.STRING(fnd_log.LEVEL_EXCEPTION
, G_MODULE_PREFIX || l_procedure_name
, 'no date found when select header info');
END IF;
END; -- select bank information
IF (l_paying_customer_id is not null) and (l_trxn_extension_id IS NOT NULL)--yao zhang changed for bug 8234250
THEN BEGIN
SELECT
u.instrument_id
, b.bank_account_name
--Modified by Yao begin for bug#8605196 to support Bank name in Chinese
--, b.bank_name
, decode(bhp.organization_name_phonetic
,null, bhp.party_name
,bhp.organization_name_phonetic)
--, b.bank_branch_name
, decode(brhp.organization_name_phonetic
,null, brhp.party_name
,brhp.organization_name_phonetic)
--Modified by Yao for bug#8605196 end to support Bank name in Chinese
INTO
l_instrument_id
, l_bank_account_name
, l_bank_name
, l_bank_branch_name
FROM IBY_CREDITCARD C,
IBY_CREDITCARD_ISSUERS_VL I,
IBY_EXT_BANK_ACCOUNTS_V B,
IBY_FNDCPT_PMT_CHNNLS_VL P,
IBY_FNDCPT_TX_EXTENSIONS X,
IBY_FNDCPT_TX_OPERATIONS OP,
IBY_PMT_INSTR_USES_ALL U,
HZ_PARTIES HZP,
FND_APPLICATION A,
--Add by Yao for bug#8605196 to support bank name in Chinese
HZ_PARTIES bhp,
HZ_PARTIES brhp
WHERE (x.instr_assignment_id = u.instrument_payment_use_id(+))
AND (DECODE(u.instrument_type, 'CREDITCARD', u.instrument_id, NULL) =
c.instrid(+))
AND (DECODE(u.instrument_type, 'BANKACCOUNT', u.instrument_id, NULL) =
b.bank_account_id(+))
AND (x.payment_channel_code = p.payment_channel_code)
AND (c.card_issuer_code = i.card_issuer_code(+))
AND (x.trxn_extension_id = op.trxn_extension_id(+))
AND (c.card_owner_id = hzp.party_id(+))
AND (x.origin_application_id = a.application_id)
AND x.trxn_extension_id = l_trxn_extension_id
--Add by Yao for bug#8605196 to support bank name in Chinese
AND b.bank_party_id=bhp.party_id(+)
AND b.branch_party_id=brhp.party_id(+); EXCEPTION
WHEN no_data_found THEN
IF(FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
THEN
fnd_log.STRING(fnd_log.LEVEL_EXCEPTION
, G_MODULE_PREFIX || l_procedure_name
, 'no date found when select bank information');
END IF;
END; BEGIN
SELECT
bank_account_num
INTO
l_bank_account_num
FROM
IBY_EXT_BANK_ACCOUNTS
WHERE
ext_bank_account_id = l_instrument_id;
EXCEPTION
WHEN no_data_found THEN
IF(FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
THEN
fnd_log.STRING(fnd_log.LEVEL_EXCEPTION
, G_MODULE_PREFIX || l_procedure_name
, 'no date found when select bank information');
END IF;
END; END IF;/*l_trxn_extension_id IS NOT NULL*/ -- if the bank information come from AR is null. then select bank info by customer!
IF l_bank_account_num IS NULL
THEN
-- get bank info by paying customer id and paying site use id.
--Yao Zhang add begin for bug#8404856
IF l_paying_customer_id IS NOT NULL
THEN
l_valid_customer_id:=l_paying_customer_id;
l_valid_site_use_id:=l_paying_site_use_id;
ELSE
l_valid_customer_id:=l_bill_to_customer_id;
l_valid_site_use_id:=l_bill_to_site_use_id;
END IF;
--Yao Zhang add end for bug#8404856 BEGIN -- get party id of paying customer
SELECT
party_id
INTO
l_paying_party_id
FROM
HZ_CUST_ACCOUNTS
WHERE
CUST_ACCOUNT_ID = l_valid_customer_id ;--Yao Zhang modified for bug#8404856 -- get ext_payer_id by party id , site account id , site use id and org id.
SELECT
ext_payer_id
INTO
l_ext_payer_id
FROM
IBY_EXTERNAL_PAYERS_ALL
WHERE party_id = l_paying_party_id
AND CUST_ACCOUNT_ID = l_valid_customer_id--Yao Zhang modified for bug#8404856
AND ACCT_SITE_USE_ID =l_valid_site_use_id--Yao Zhang modified for bug#8404856
AND ORG_ID = p_org_id -- org id
AND org_type = 'OPERATING_UNIT' -- ou
AND payment_function = 'CUSTOMER_PAYMENT'; -- get bank account name and bank account num
SELECT
bank_account_name
, bank_account_num
, bank_id
, branch_id
INTO
l_bank_account_name
, l_bank_account_num
, l_bank_id
, l_bank_branch_id
FROM (SELECT ibybanks.bank_account_name
, ibybanks.bank_account_num
, ibybanks.bank_id
, ibybanks.branch_id
FROM IBY_PMT_INSTR_USES_ALL ExtPartyInstrumentsEO
, IBY_EXT_BANK_ACCOUNTS ibybanks
WHERE ibybanks.EXT_BANK_ACCOUNT_ID = ExtPartyInstrumentsEO.instrument_id
AND ExtPartyInstrumentsEO.INSTRUMENT_TYPE = 'BANKACCOUNT'
AND ExtPartyInstrumentsEO.EXT_PMT_PARTY_ID = l_ext_payer_id
AND ExtPartyInstrumentsEO.PAYMENT_FUNCTION = 'CUSTOMER_PAYMENT'
AND (ibybanks.currency_code = l_currency_code OR ibybanks.currency_code IS NULL)
AND SYSDATE BETWEEN nvl(ExtPartyInstrumentsEO.START_DATE, to_date('1900-01-01','RRRR-MM-DD'))
AND nvl(ExtPartyInstrumentsEO.END_DATE, to_date('3000-01-01','RRRR-MM-DD'))
ORDER BY ibybanks.currency_code,ExtPartyInstrumentsEO.ORDER_OF_PREFERENCE)
WHERE ROWNUM =1; -- get bank name
--Modified begin by Yao for bug#8605196 to support bank name in Chinese
SELECT
decode(organization_name_phonetic
,null, party_name
,organization_name_phonetic)
--Modified end by Yao for bug#8605196 to support bank name in Chinese
INTO
l_bank_name
FROM
HZ_PARTIES
WHERE
party_id = l_bank_id; -- get bank branch name
SELECT
--Modified begin by Yao for bug#8605196 to support bank name in Chinese
decode(organization_name_phonetic
,null, party_name
,organization_name_phonetic)
--Modified end by Yao for bug#8605196 to support bank name in Chinese
INTO
l_bank_branch_name
FROM
HZ_PARTIES
WHERE party_id = l_bank_branch_id; EXCEPTION
WHEN no_data_found THEN
IF(FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
THEN
fnd_log.STRING(fnd_log.LEVEL_EXCEPTION
, G_MODULE_PREFIX || l_procedure_name
, 'no date found when select bank information');
END IF;
END;/*l_apba_bank_account_num IS NULL*/ END IF; x_bank_name := l_bank_name;
x_bank_branch_name := l_bank_branch_name;
x_bank_account_num := l_bank_account_num;
x_bank_account_name := l_bank_account_name; IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
THEN
fnd_log.STRING(fnd_log.LEVEL_PROCEDURE
, G_MODULE_PREFIX || l_procedure_name
,'End Procedure. ');
END IF; EXCEPTION
WHEN OTHERS THEN
IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
THEN
FND_LOG.string( FND_LOG.LEVEL_UNEXPECTED
, G_MODULE_PREFIX || l_procedure_name || '. OTHER_EXCEPTION '
, Sqlcode||Sqlerrm);
END IF;
RAISE;
END Get_Bank_Info;