--AR模块常用函数FUNCTION get_fnd_user_name ( p_user_id IN NUMBER ) return VARCHAR2 ISCURSOR 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 ) ISl_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 ISl_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;EXCEPTIONWHEN others THEN RETURN l_score_total;END;FUNCTION get_requestor_name(p_requestor_id IN NUMBER)RETURN VARCHAR2 ISl_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 NUMBERISl_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_codeISSELECT vat_tax_type_code ,gt_currency_codeFROM ar_gta_system_parameters_allWHERE org_id=p_org_id;CURSOR c_ar_taxable_amountISSELECT NVL(SUM(taxable_amt_tax_curr),0)FROM zx_linesWHERE 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,2006BEGIN --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 NUMBERISl_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_codeISSELECT vat_tax_type_code ,gt_currency_codeFROM ar_gta_system_parameters_allWHERE org_id=p_org_id;CURSOR c_ar_tax_amountISSELECT NVL(SUM(tax_amt_tax_curr),0)FROM zx_linesWHERE 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)ISboundary 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 ISl_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 VARCHAR2ISl_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 idCURSOR c_phone_numberISSELECT hcp.phone_numberFROM hz_contact_points hcpWHERE 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 VARCHAR2ISl_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 NUMBERISl_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_codeISSELECT vat_tax_type_code ,gt_currency_codeFROM ar_gta_system_parameters_allWHERE org_id=p_org_id;--CURSOR c_ar_line_taxable_amount --Donghai Wang bug5212702 May-17,2006CURSOR c_ar_line_taxable_amount(pc_trx_id NUMBER)--Donghai Wang bug5212702 May-17,2006ISSELECT taxable_amt_tax_currFROM zx_linesWHERE 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_idORDER 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 NUMBERISl_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,2006CURSOR c_tax_type_codeISSELECT vat_tax_type_code ,gt_currency_codeFROM ar_gta_system_parameters_allWHERE org_id=p_org_id;--CURSOR c_ar_line_vatamount--Donghai Wang bug5212702 May-17,2006CURSOR c_ar_line_vatamount(pc_trx_id NUMBER)--Donghai Wang bug5212702 May-17,2006ISSELECT tax_amt_tax_currFROM zx_linesWHERE 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_idORDER 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 NUMBERISl_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,2006CURSOR c_tax_type_codeISSELECT vat_tax_type_code ,gt_currency_codeFROM ar_gta_system_parameters_allWHERE org_id=p_org_id;--CURSOR c_ar_line_tax_rate --Donghai Wang bug5212702 May-17,2006CURSOR c_ar_line_tax_rate(pc_trx_id NUMBER) --Donghai Wang bug5212702 May-17,2006ISSELECT tax_rateFROM zx_linesWHERE 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,2006ORDER 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)ISl_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#8404856l_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#8404856l_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;
