[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* Name : Update_Withheld_Amount *
* Purpose : Prorates the withheld amount for each tax name included *
* into the PL/SQL table. These values will also be rounded. *
* *
**************************************************************************/
PROCEDURE Update_Withheld_Amount
(P_Original_Withheld_Amt IN Number,
P_Updated_Withheld_Amt IN Number,
P_Currency_Code IN Varchar2,
P_Calling_Sequence IN Varchar2,
P_Tab_Withhold IN OUT NOCOPY Tab_Withholding);
* 3 Updates the PL/SQL table to store the revised amount *
* *
**************************************************************************/
FUNCTION Get_Revised_Tax_Base_Amount
(P_Rec_AWT_Name IN Rec_AWT_CODE,
P_Tab_Withhold IN OUT NOCOPY Tab_Withholding,
P_Tax_Name_From IN Number,
P_Tax_Name_To IN Number,
P_Taxable_Base_Amount IN Number,
P_Tab_All_Withhold IN Tab_All_Withholding,
P_Calling_Sequence IN Varchar2)
RETURN NUMBER;
SELECT nvl(create_awt_dists_type, 'NEVER'),
nvl(create_awt_invoices_type, 'NEVER')
INTO l_create_distr,
l_create_invoices
FROM ap_system_parameters;
SELECT gps.period_name
INTO l_gl_period_name
FROM gl_period_statuses gps,
ap_system_parameters asp
WHERE gps.application_id = 200
AND gps.set_of_books_id = asp.set_of_books_id
AND trunc(P_AWT_Date) BETWEEN trunc(gps.start_date) AND trunc(gps.end_date) --bug9869654
AND gps.closing_status IN ('O', 'F');
SELECT base_currency_code
INTO l_base_currency_code
FROM ap_system_parameters;
P_Wh_Table.DELETE;
SELECT *
INTO P_Rec_AWT_Type
FROM jl_zz_ap_awt_types
WHERE awt_type_code = P_AWT_Type_Code;
SELECT *
INTO P_Rec_Suppl_AWT_Type
FROM jl_zz_ap_supp_awt_types
WHERE awt_type_code = P_AWT_Type_Code
AND vendor_id = P_Vendor_Id;
SELECT tax_id,
name,
tax_code_combination_id,
awt_period_type,
global_attribute6,
global_attribute7,
global_attribute8,
global_attribute9,
global_attribute10,
global_attribute11,
global_attribute12,
global_attribute13,
global_attribute14,
global_attribute15,
global_attribute16,
global_attribute17,
global_attribute18
INTO P_AWT_Name.Tax_Id,
P_AWT_Name.Name,
P_AWT_Name.Tax_Code_Combination_Id,
P_AWT_Name.AWT_Period_Type,
l_glattr6,
l_glattr7,
l_glattr8,
l_glattr9,
l_glattr10,
l_glattr11,
l_glattr12,
l_glattr13,
l_glattr14,
l_glattr15,
l_glattr16,
l_glattr17,
l_glattr18
FROM ap_tax_codes
WHERE tax_id = P_Tax_Id;
SELECT *
INTO P_Rec_Suppl_AWT_Name
FROM jl_zz_ap_sup_awt_cd jlsc
WHERE jlsc.tax_id = P_Tax_Id -- Argentina AWT code change
AND jlsc.supp_awt_type_id =
(SELECT jlst.supp_awt_type_id
FROM jl_zz_ap_supp_awt_types jlst
WHERE jlst.awt_type_code = P_AWT_Type_Code
AND jlst.vendor_id = P_Vendor_Id)
AND NVL(To_Date(P_CODE_ACCOUNTING_DATE),sysdate) between
NVL(jlsc.effective_start_date,To_Date('01-01-1950', 'DD-MM-YYYY'))
and NVL(jlsc.effective_end_date,To_Date('31-12-9999', 'DD-MM-YYYY'));
SELECT *
INTO P_Rec_Suppl_AWT_Name
FROM jl_zz_ap_sup_awt_cd jlsc
WHERE jlsc.tax_id = P_Tax_Id -- Argentina AWT code change
AND jlsc.supp_awt_type_id =
(SELECT jlst.supp_awt_type_id
FROM jl_zz_ap_supp_awt_types jlst
WHERE jlst.awt_type_code = P_AWT_Type_Code
AND jlst.vendor_id = P_Vendor_Id)
AND NVL(To_Date(P_CODE_ACCOUNTING_DATE),sysdate) between
NVL(jlsc.effective_start_date,To_Date('01-01-1950', 'DD-MM-YYYY'))
and NVL(jlsc.effective_end_date,To_Date('31-12-9999', 'DD-MM-YYYY'));
SELECT *
INTO P_Rec_Suppl_AWT_Name
FROM jl_zz_ap_sup_awt_cd jlsc
WHERE jlsc.tax_id = P_Tax_Id
AND jlsc.supp_awt_type_id =
(SELECT jlst.supp_awt_type_id
FROM jl_zz_ap_supp_awt_types jlst
WHERE jlst.awt_type_code = P_AWT_Type_Code
AND jlst.vendor_id = P_Vendor_Id);
P_Last_Updated_By IN Number Default null,
P_Last_Update_Login IN Number Default null,
P_Program_Application_Id IN Number Default null,
P_Program_Id IN Number Default null,
P_Request_Id IN Number Default null,
P_Calling_Module IN Varchar2 Default null,
P_Checkrun_Name IN Varchar2 Default null,
P_Checkrun_id IN Number Default null,
P_Payment_Num IN Number Default null,
P_Global_Attr_Category IN Varchar2 Default null,
P_NIT_Number IN Varchar2 Default null)
IS
------------------------------
-- Local variables definition
------------------------------
l_invoice_id Number := null;
SAVEPOINT Before_Inserting_Lines;
select line_type_lookup_code, related_id
into l_line_type, l_related_id
from ap_invoice_distributions
where invoice_distribution_id = P_Tab_Withhold(i).invoice_distribution_id;
ROLLBACK TO Before_Inserting_Lines;
Ap_Calc_Withholding_Pkg.Insert_Temp_Distribution
(l_invoice_id,
P_Vendor_Id,
-- By zmohiudd Bug1849986 changed P_Payment_Num to l_Payment_Num
nvl(l_Payment_Num,P_PAYMENT_NUM),
-1, -- Group ID
l_tax_name,
l_tax_code_comb_id,
l_gross_amount,
l_withheld_amount,
P_AWT_Date,
P_GL_Period_Name,
l_awt_period_type,
l_awt_period_name,
-- l_awt_related_id, Commented for bug 6885098
P_Checkrun_Name,
l_tax_rate_id,
null,
P_Base_Currency_Code,
P_Base_Currency_Code,
null, -- Offset
l_calling_sequence,
l_handle_bucket,
P_Last_Updated_By,
P_Last_Update_Login,
P_Program_Application_Id,
P_Program_Id,
P_Request_Id,
P_Calling_Module,
l_invoice_payment_id,
null, -- Invoice exchange rate
P_Global_Attr_Category, -- Global attribute category
null, -- Global attribute1
P_NIT_Number, -- Global Attribute2
null, -- Global Attribute3
null, -- Global Attribute4
l_exemption_amount, -- Global Attribute5
P_checkrun_id => p_checkrun_id,
P_awt_related_id => l_awt_related_id); --Added for 6885098
Update_Withheld_Amount (l_previous_wh_amount,
l_withheld_amount,
P_Currency_Code,
l_calling_sequence,
P_Tab_Withhold);
v_last_update_by NUMBER;
v_last_update_login NUMBER;
SELECT apid.invoice_distribution_id,
apid.po_distribution_id,
apid.global_attribute20 -- What is gdf20?
FROM ap_invoice_distributions apid
WHERE apid.invoice_id = P_Credit_Id
AND apid.invoice_line_number = P_Inv_Line_Num;
v_last_update_by := FND_GLOBAL.User_ID;
v_last_update_login := FND_GLOBAL.Login_Id;
INSERT INTO jl_zz_ap_inv_dis_wh (
inv_distrib_awt_id
,invoice_id
-- Bug 4559478
,invoice_distribution_id
,distribution_line_number
,supp_awt_code_id
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
,org_id
)
SELECT
jl_zz_ap_inv_dis_wh_s.nextval
,P_Credit_Id
,P_distribution_id
-- Bug 4559478 : -99 for distribution_line_number
,-99
,jlid.Supp_Awt_Code_Id
,v_last_update_by
,sysdate
,v_last_update_by
,sysdate
,v_last_update_login
,jlid.org_id
FROM
jl_zz_ap_inv_dis_wh jlid
WHERE jlid.invoice_distribution_id = P_Parent_Dist_ID
AND jlid.invoice_id = P_Invoice_Id;
SELECT poll.ship_to_location_id
INTO l_ship_to_location_id
FROM po_line_locations poll
WHERE line_location_id = (SELECT line_location_id
FROM po_distributions
WHERE po_distribution_id = l_po_distribution_id);
UPDATE ap_invoice_distributions
SET
-- global_attribute3 = l_ship_to_location_id,
global_attribute_category = decode(v_country_code,'AR','JL.AR.APXINWKB.DISTRIBUTIONS',
'CO','JL.CO.APXINWKB.DISTRIBUTIONS','')
where invoice_id = nvl(P_Credit_Id,P_Invoice_Id) -- Bug 2906487, Added an nvl clause.
and invoice_distribution_id = P_distribution_id;
* Name : Jl_Zz_Ap_Ext_Insert_Dist *
* Purpose : Regional Extended Routine for Insertion *
* *
**************************************************************************/
PROCEDURE Jl_Zz_Ap_Ext_Insert_Dist
(P_Invoice_Id IN Number,
P_Invoice_Distribution_id IN Number, -- Add new Column
P_Distribution_Line_Number IN Number,
P_Line_Type IN Varchar2,
P_GL_Date IN Date,
P_Period_Name IN Varchar2,
P_Type_1099 IN Varchar2,
P_Income_Tax_Region IN Varchar2,
P_Amount IN Number,
P_Tax_Code_ID IN Number, -- Add new Column
P_Code_Combination_Id IN Number,
P_PA_Quantity IN Number,
P_Description IN Varchar2,
P_tax_recoverable_flag IN Varchar2, -- Add new Column
P_tax_recovery_rate IN Number, -- Add new Column
P_tax_code_override_flag IN Varchar2, -- Add new Column
P_tax_recovery_override_flag IN Varchar2, -- Add new Column
P_po_distribution_id IN Number, -- Add new Column
P_Attribute_Category IN Varchar2,
P_Attribute1 IN Varchar2,
P_Attribute2 IN Varchar2,
P_Attribute3 IN Varchar2,
P_Attribute4 IN Varchar2,
P_Attribute5 IN Varchar2,
P_Attribute6 IN Varchar2,
P_Attribute7 IN Varchar2,
P_Attribute8 IN Varchar2,
P_Attribute9 IN Varchar2,
P_Attribute10 IN Varchar2,
P_Attribute11 IN Varchar2,
P_Attribute12 IN Varchar2,
P_Attribute13 IN Varchar2,
P_Attribute14 IN Varchar2,
P_Attribute15 IN Varchar2,
P_Calling_Sequence IN Varchar2)
IS
BEGIN
----------------------------------------------------------
-- Stubbed OUT JL will not longer insert in AP Dist Table
-- R12
----------------------------------------------------------
NULL;
END Jl_Zz_Ap_Ext_Insert_Dist;
SELECT period_name
INTO l_period_name
FROM ap_other_periods
WHERE application_id = 200
AND module = 'AWT'
AND period_type = P_Period_Type
AND start_date <= trunc(P_AWT_Date)
AND end_date >= trunc(P_AWT_Date);
SELECT gross_amount_to_date,
withheld_amount_to_date
INTO l_gross_amount_to_date,
l_withheld_amount_to_date
FROM ap_awt_buckets
WHERE period_name = l_period_name
AND tax_name = P_Tax_Name
AND vendor_id = P_Vendor_Id;
SELECT tax_rate,
tax_rate_id,
rate_type,
start_amount,
end_amount,
global_attribute1,
global_attribute2
FROM ap_awt_tax_rates
WHERE tax_name = P_Tax_Name
AND rate_type = 'STANDARD'
AND P_Date BETWEEN nvl(start_date, P_Date - 1)
AND nvl(end_date, P_Date + 1)
ORDER BY start_amount asc;
* Name : Update_Withheld_Amount *
* Purpose : Prorates the withheld amount for each tax name included *
* into the PL/SQL table. These values will also be rounded. *
* *
**************************************************************************/
PROCEDURE Update_Withheld_Amount
(P_Original_Withheld_Amt IN Number,
P_Updated_Withheld_Amt IN Number,
P_Currency_Code IN Varchar2,
P_Calling_Sequence IN Varchar2,
P_Tab_Withhold IN OUT NOCOPY Tab_Withholding)
IS
------------------------------
-- Local variables definition
------------------------------
l_withheld_amount Number := 0;
l_updated_withheld_amt Number;
'Update_Withheld_Amount<--' || P_Calling_Sequence;
l_updated_withheld_amt := Ap_Utilities_Pkg.Ap_Round_Currency
(P_Updated_Withheld_Amt, P_Currency_Code);
l_updated_withheld_amt /
P_Original_Withheld_Amt;
l_withheld_amount := l_updated_withheld_amt -
l_cumulative_amount;
', Updated Withheld Amt= ' || to_char(P_Updated_Withheld_Amt) ||
', Currency Code= ' || P_Currency_Code);
END Update_Withheld_Amount;
* 3 Updates the PL/SQL table to store the revised amount *
* *
**************************************************************************/
FUNCTION Get_Revised_Tax_Base_Amount
(P_Rec_AWT_Name IN Rec_AWT_CODE,
P_Tab_Withhold IN OUT NOCOPY Tab_Withholding,
P_Tax_Name_From IN Number,
P_Tax_Name_To IN Number,
P_Taxable_Base_Amount IN Number,
P_Tab_All_Withhold IN Tab_All_Withholding,
P_Calling_Sequence IN Varchar2)
RETURN NUMBER
IS
------------------------------
-- Local Variables Definition
------------------------------
tab Tab_All_Withholding := P_Tab_All_Withhold;
SELECT dist_code_combination_id
FROM ap_invoice_distributions
WHERE invoice_id = P_Invoice_id
-- added recently
AND NVL(REVERSAL_FLAG,'N') <> 'Y';
SELECT distinct atc.name, atc.tax_code_combination_id
FROM jl_zz_ap_inv_dis_wh jid,
jl_zz_ap_sup_awt_cd jsw,
ap_tax_codes atc
WHERE jid.invoice_id = P_Invoice_Id
AND jsw.supp_awt_code_id = jid.supp_awt_code_id
AND atc.tax_id = jsw.tax_id;
SELECT nvl(liability_post_lookup_code, 'NONE')
INTO l_liability_post_lookup_code
FROM ap_system_parameters;
SELECT dist_code_combination_id
FROM ap_invoice_lines_interface
WHERE invoice_id = P_Invoice_id;
SELECT distinct atc.name, atc.tax_code_combination_id
FROM jl_zz_ap_sup_awt_cd jsw,
jl_zz_ap_supp_awt_types jst,
ap_tax_codes atc,
ap_invoices_interface aii
WHERE aii.invoice_id = P_Invoice_id
AND jst.vendor_id = aii.vendor_id
AND jst.supp_awt_type_id = jsw.supp_awt_type_id
AND atc.tax_id = jsw.tax_id
AND jsw.primary_tax_flag = 'Y';
SELECT nvl(liability_post_lookup_code, 'NONE')
INTO l_liability_post_lookup_code
FROM ap_system_parameters;
SELECT start_date, end_date
INTO l_start_date, l_end_date
FROM ap_other_periods
WHERE application_id = 200
AND module = 'AWT'
AND period_type = P_AWT_Period_Type
AND period_name = l_period_name;
SELECT Tax_Id
INTO l_tax_id
FROM ap_tax_codes
WHERE name = P_Tax_Name;
SELECT NVL(sum(to_number(aid.global_attribute5)),0)
INTO l_exemption_amount
FROM ap_invoices ai,
ap_invoice_distributions aid
WHERE ai.vendor_id = P_Vendor_Id
AND ai.invoice_id = aid.invoice_id
AND trunc(aid.accounting_date) >= l_start_date
AND trunc(aid.accounting_date) <= l_end_date
AND aid.line_type_lookup_code = 'AWT'
AND aid.withholding_tax_code_id = l_tax_id
-- added recently
AND NVL(aid.REVERSAL_FLAG,'N') <> 'Y';