[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
dbetanco 11/12/98 Update
dbetanco 19/01/98 Update Include the Del_Wh_Def Proc.
*/
-- =====================================================================
-- P R I V A T E O B J E C T S
-- =====================================================================
--
-- Insert_AWT_Default insert in jl_zz_ap_inv_dis_wh_all the withholdings after the validation.
--
-- Define Package Level Debug Variable and Assign the Profile
DEBUG_Var varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
PROCEDURE Insert_AWT_Default(
P_Invoice_Id IN ap_invoices_all.invoice_id%TYPE,
P_Inv_Dist_Id IN ap_invoice_distributions_all.invoice_distribution_id%TYPE,
P_Supp_Awt_Code_Id IN jl_zz_ap_sup_awt_cd.supp_awt_code_id%TYPE,
p_calling_sequence IN VARCHAR2,
P_Org_Id IN jl_zz_ap_sup_awt_cd.org_id%TYPE) IS
Seq_Inv_Dis_Awt_Id NUMBER;
l_debug_loc VARCHAR2(30) := ' Insert_AWT_Default ';
v_last_update_by NUMBER;
v_last_update_login NUMBER;
FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Insert_AWT_Default','Start PROCEDURE Insert_AWT_Default');
FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Insert_AWT_Default','Parameters are :');
FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Insert_AWT_Default',' P_Invoice_Id='||P_Invoice_Id);
FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Insert_AWT_Default',' P_Inv_Dist_Id='||P_Inv_Dist_Id);
FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Insert_AWT_Default',' P_Supp_Awt_Code_Id='||P_Supp_Awt_Code_Id);
FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Insert_AWT_Default',' p_calling_sequence='||p_calling_sequence);
FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Insert_AWT_Default',' P_Org_Id='||P_Org_Id);
FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Insert_AWT_Default',' P_Invoice_Id='||P_Invoice_Id);
FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Insert_AWT_Default',' Seq_Inv_Dis_Awt_Id='||Seq_Inv_Dis_Awt_Id);
FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Insert_AWT_Default',' l_debug_loc='||l_debug_loc);
FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Insert_AWT_Default',' l_curr_calling_sequence='||l_curr_calling_sequence);
FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Insert_AWT_Default',' l_debug_info='||l_debug_info);
FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Insert_AWT_Default',' v_last_update_by='||v_last_update_by);
FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Insert_AWT_Default',' v_last_update_login='||v_last_update_login);
l_debug_info := 'Insert rejection information to ap_interface_rejections';
v_last_update_by := FND_GLOBAL.User_ID;
v_last_update_login := FND_GLOBAL.Login_Id;
SELECT jl_zz_ap_inv_dis_wh_s.nextval
INTO Seq_Inv_Dis_Awt_Id
FROM dual;
FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Insert_AWT_Default','Inserting this record to JL_ZZ_AP_INV_DIS_WH_ALL with inv_distrib_awt_id='||Seq_Inv_Dis_Awt_Id);
INSERT INTO jl_zz_ap_inv_dis_wh (
inv_distrib_awt_id
,invoice_id
-- Bug 4559472
,distribution_line_number
,invoice_distribution_id
,supp_awt_code_id
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
,org_id -- Add org_id for MOAC
)
VALUES (
Seq_Inv_Dis_Awt_Id
,P_Invoice_Id
-- Bug 4559472
--,P_Dis_Line_Number
-- Populate distribution_line_number with -99 for R12 records
-- as it is NOT NULL column in jl_zz_ap_inv_dis_wh_all
,-99
, P_Inv_Dist_Id
,P_Supp_Awt_Code_Id
,v_last_update_by
,sysdate
,v_last_update_by
,sysdate
,v_last_update_login
,P_Org_Id -- Add org_id for MOAC
);
FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Insert_AWT_Default','End PROCEDURE Insert_AWT_Default');
FND_LOG.STRING(G_LEVEL_EXCEPTION, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Insert_AWT_Default','Exception Occured in PROCEDURE Insert_AWT_Default');
||', Last Updated By = '||to_char(v_last_update_by)
||', Last Update Date = '||to_char(v_last_update_login));
SELECT territorial_flag
FROM jl_ar_ap_provinces
WHERE province_code = P_Province_Code;
SELECT Vendor_Id
FROM po_vendors
WHERE segment1 = P_Tax_Payer_Id; -- R12 KI : Need to uptake PTP?
SELECT Vendor_Id
FROM ap_invoices
WHERE invoice_id = P_Invoice_Id;
SELECT awt_type_code
FROM jl_zz_ap_comp_awt_types
WHERE legal_entity_id = PC_Legal_Entity_ID
--location_id = PC_Location_ID
AND wh_agent_flag = 'Y';
SELECT legal_entity_id
FROM ap_invoices
WHERE invoice_id = P_Invoice_ID;
SELECT legal_entity_id
INTO l_legal_entity_id
FROM ap_invoices
WHERE invoice_id = P_Invoice_ID;
select decode((select 'TRUE'
from ap_invoices AP
where AP.invoice_id = P_Invoice_ID
and EXISTS (select /*+ NO_UNNEST */ 'x'
from jl_zz_ap_comp_awt_types
where legal_entity_id = AP.legal_entity_id
and wh_agent_flag = 'Y'
and awt_type_code = P_Awt_Type_Code)
and ROWNUM=1), 'TRUE', 'TRUE','FALSE')
into V_FND_TYPE
from DUAL;
SELECT global_attribute8, -- Type ITEM
global_attribute9, -- Type FREIGHT
global_attribute10,-- Type MISCELLANEOUS
global_attribute11 -- Type TAX
FROM ap_tax_codes
WHERE tax_id =v_tax_id;
The Procedure Province_zone_city is used to select region_1,region_2,town_or_city from
hr_locations for a ship_to_location_id.
-------------------------------------------------------------------*/
PROCEDURE Province_Zone_City
(p_ship_to_location_id hr_locations_all.location_id%TYPE
,v_hr_zone out NOCOPY hr_locations_all.region_1%TYPE
,v_hr_province out NOCOPY hr_locations_all.region_2%TYPE
,v_city_code out NOCOPY hr_locations_all.town_or_city%TYPE) is
CURSOR cur_province_zone_city IS
SELECT region_1, region_2, town_or_city
FROM hr_locations_all
WHERE location_id = p_ship_to_location_id;
The Procedure Del_Wh_Def Delete the records in JL_ZZ_AP_INV_DIS_WH
for the Invoice_ID Parameter and the Dis_Lin_Number.
-------------------------------------------------------------------*/
--
-- R12 KI
--
PROCEDURE Del_Wh_Def
(
p_inv_dist_id ap_invoice_distributions_all.invoice_distribution_id%TYPE
) IS
Begin
/*
DELETE JL_ZZ_AP_INV_DIS_WH
WHERE invoice_id = P_Invoice_Id
AND invoice_distribution_id = P_Dis_Lin_Num;
DELETE jl_zz_ap_inv_dis_wh
WHERE invoice_distribution_id = p_inv_dist_id;
SELECT swt.supp_awt_type_id ,
swt.awt_type_code,
swc.supp_awt_code_id,
swc.org_id, -- Add Org_ID for MOAC
tca.tax_id,
tca.global_attribute7, -- Zone
awt.jurisdiction_type,
awt.province_code,
awt.city_code
FROM jl_zz_ap_supp_awt_types swt,
jl_zz_ap_sup_awt_cd swc,
ap_tax_codes_ALL tca, -- Add _ALL for MOAC
jl_zz_ap_awt_types awt
WHERE swt.vendor_id = C_vendor_id -- Select only for this Supplier
AND swt.wh_subject_flag = 'Y' -- Supp subject to the withholding tax type
AND swc.supp_awt_type_id = swt.supp_awt_type_id -- Join
AND swc.tax_id = tca.tax_id -- Join
--bug 11666467: Included TRUNC
AND (trunc(tca.inactive_date) > trunc(sysdate) -- Verify Tax Name Inactive Date
OR tca.inactive_date IS NULL)
AND swc.primary_tax_flag = 'Y' -- Verify the Primary Withholding Tax
AND awt.awt_type_code = swt.awt_type_code -- Join
--bug 11666467: Included TRUNC
AND trunc(sysdate) between trunc(nvl(swc.effective_start_date,sysdate))
and trunc(nvl(swc.effective_end_date,sysdate))
; -- Argentine AWT ER 6624809
FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Supp_Wh_Def_Line','Calling Insert_AWT_Default - 1');
Insert_AWT_Default
(P_Invoice_Id
-- Bug 4559472
-- ,P_Dis_Lin_Num
, p_inv_dist_id
, db_reg. supp_awt_code_id
, p_calling_sequence
, db_reg.org_id ); -- Add org_Id for MOAC
FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Supp_Wh_Def_Line','Calling Insert_AWT_Default - 2');
Insert_AWT_Default
(P_Invoice_Id
-- Bug 4559472
-- ,P_Dis_Lin_Num
, p_inv_dist_id
, db_reg. supp_awt_code_id
, p_calling_sequence
, db_reg.org_id ); -- Add org_Id for MOAC
FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Supp_Wh_Def_Line','Calling Insert_AWT_Default - 3');
Insert_AWT_Default
(P_Invoice_Id
-- Bug 4559472
-- ,P_Dis_Lin_Num
, p_inv_dist_id
, db_reg. supp_awt_code_id
, p_calling_sequence
, db_reg.org_id ); -- Add org_Id for MOAC
FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Supp_Wh_Def_Line','Selecting City Code from Geography Model:');
select geography_code
into v_hr_city_code
from hz_geographies
where geography_name = v_hr_city
and geography_element2_code = v_hr_province
and geography_type='CITY'
--bug 11666467: Start
and country_code = v_country_code
and trunc(sysdate) between trunc(nvl(start_date,sysdate))
and trunc(nvl(end_date,sysdate));
FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Supp_Wh_Def_Line','Calling Insert_AWT_Default - 4');
Insert_AWT_Default
(P_Invoice_Id
-- Bug 4559472
-- ,P_Dis_Lin_Num
, p_inv_dist_id
, db_reg. supp_awt_code_id
, p_calling_sequence
, db_reg.org_id ); -- Add org_Id for MOAC
FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Supp_Wh_Def_Line','Calling Insert_AWT_Default - 5');
Insert_AWT_Default
(P_Invoice_Id
-- Bug 4559472
-- ,P_Dis_Lin_Num
, p_inv_dist_id
, db_reg. supp_awt_code_id
, p_calling_sequence
, db_reg.org_id ); -- Add org_Id for MOAC
SELECT invoice_distribution_id
FROM ap_invoice_distributions
WHERE invoice_id = P_Invoice_ID
AND invoice_line_number = P_Inv_Line_Num;
SELECT Supp_Awt_Code_Id,
org_id
FROM jl_zz_ap_inv_dis_wh
WHERE invoice_id = p_invoice_id
AND invoice_distribution_id = p_related_dist_id;
v_last_update_login number := FND_GLOBAL.Login_Id;
v_last_update_by number := FND_GLOBAL.User_ID;
SELECT COUNT(*)
INTO DistWithholdings
From jl_zz_ap_inv_dis_wh
Where invoice_id = P_Invoice_ID
And invoice_distribution_id = P_Inv_Dist_Id;
FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Supp_Wh_Def','INSERT INTO jl_zz_ap_inv_dis_wh (...');
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_Invoice_Id
,P_Inv_Dist_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 apid.global_attribute2 -- Taxpayer Id for Colombia
,apid.global_attribute3 -- Ship to Location Argentina/Colombia
-- , apil.ship_to_location_id -- Ship to Location Argentina/Colombia
, apid.line_type_lookup_code -- Line Type
INTO v_tax_payer_id,
v_ship_to_loc,
v_line_type
FROM AP_Invoice_Distributions apid,
AP_Invoice_Lines apil
WHERE apid.invoice_id = p_invoice_id
AND apid.invoice_distribution_id = p_inv_dist_id
AND apid.invoice_line_number = apil.line_number
AND apid.invoice_id = apil.invoice_id;
SELECT apid.global_attribute2 -- Taxpayer Id for Colombia
,apid.global_attribute3 -- Ship to Location Argentina/Colombia
-- , apil.ship_to_location_id -- Ship to Location Argentina/Colombia
, apid.line_type_lookup_code -- Line Type
INTO v_tax_payer_id,
v_ship_to_loc,
v_line_type
FROM AP_Invoice_Distributions apid,
AP_Invoice_Lines apil
WHERE apid.invoice_id = P_Invoice_Id
AND apid.invoice_distribution_id = db_reg.invoice_distribution_id
AND apid.invoice_line_number = apil.line_number
AND apid.invoice_id = apil.invoice_id;
SELECT COUNT(*)
INTO DistWithholdings
FROM jl_zz_ap_inv_dis_wh
WHERE invoice_id = P_Invoice_ID
AND invoice_distribution_id = db_reg.invoice_distribution_id;
SELECT related_id
INTO v_related_dist_id
FROM ap_invoice_distributions
WHERE invoice_id = P_Invoice_Id
AND invoice_distribution_id = db_reg.invoice_distribution_id;
INSERT INTO jl_zz_ap_inv_dis_wh (
inv_distrib_awt_id
,invoice_id
,distribution_line_number
,invoice_distribution_id
,supp_awt_code_id
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
,org_id -- Add org_id for MOAC
)
VALUES (
jl_zz_ap_inv_dis_wh_s.nextval
,P_Invoice_Id
,-99
, db_reg.invoice_distribution_id
,l_def_wh_dist.Supp_Awt_Code_Id
,v_last_update_by
,sysdate
,v_last_update_by
,sysdate
,v_last_update_login
,l_def_wh_dist.Org_Id
);
FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Supp_Wh_Def','Inside call to ap_approval_pkg and Distwith =0 and line type in variance, inserted record into jl_zz_ap_inv_dis_wh table ');
,P_last_update_login Number
,P_Calling_Sequence Varchar2
) IS
l_prepay_id Number;
FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Carry_Withholdings_Prepay',' P_last_update_login='||P_last_update_login);
SELECT invoice_id,
invoice_distribution_id
INTO l_prepay_id,
l_prepay_dist_line_num
FROM ap_invoice_distributions
WHERE invoice_distribution_id = P_prepay_dist_id;
FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Carry_Withholdings_Prepay','INSERT INTO jl_zz_ap_inv_dis_wh ...');
INSERT INTO jl_zz_ap_inv_dis_wh
(INV_DISTRIB_AWT_ID,
INVOICE_ID,
distribution_line_number, -- Bug 4559474
invoice_distribution_id, -- Bug 4559474
SUPP_AWT_CODE_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
ORG_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15)
SELECT
JL_ZZ_AP_INV_DIS_WH_S.nextval,
P_Invoice_Id,
-99, -- Bug 4559474
p_inv_dist_id, -- Bug 4559474
idw.supp_awt_code_id,
P_user_id,
SYSDATE,
DECODE(P_last_update_login,-999,P_user_id,P_last_update_login),
SYSDATE,
DECODE(P_last_update_login,-999,P_user_id,P_last_update_login),
idw.ORG_ID,
idw.ATTRIBUTE_CATEGORY,
idw.ATTRIBUTE1,
idw.ATTRIBUTE2,
idw.ATTRIBUTE3,
idw.ATTRIBUTE4,
idw.ATTRIBUTE5,
idw.ATTRIBUTE6,
idw.ATTRIBUTE7,
idw.ATTRIBUTE8,
idw.ATTRIBUTE9,
idw.ATTRIBUTE10,
idw.ATTRIBUTE11,
idw.ATTRIBUTE12,
idw.ATTRIBUTE13,
idw.ATTRIBUTE14,
idw.ATTRIBUTE15
FROM jl_zz_ap_inv_dis_wh idw
WHERE idw.invoice_distribution_id = p_prepay_dist_id; -- Bug 4559474
SELECT invoice_distribution_id
FROM ap_invoice_distributions
WHERE invoice_id = P_Invoice_ID;
SELECT apid.global_attribute2 -- Taxpayer Id for Colombia
,apid.global_attribute3 -- Ship to Location Argentina/Colombia
,apid.line_type_lookup_code -- Line Type
INTO v_tax_payer_id,
v_ship_to_loc,
v_line_type
FROM AP_Invoice_Distributions apid,
AP_Invoice_Lines apil
WHERE apid.invoice_id = P_Invoice_Id
AND apid.invoice_distribution_id = db_reg.invoice_distribution_id
AND apil.line_number = apid.invoice_line_number
AND apid.invoice_id = apil.invoice_id;
Select org_id, invoice_type_lookup_code
From ap_invoices
Where invoice_id = x_invoice_Id;
Select ship_to_location_id, line_type_lookup_code
From ap_invoice_lines
Where invoice_id = x_invoice_id
And line_number = x_inv_line_num;
Select JG_ZZ_SHARED_PKG.GET_COUNTRY(l_org_id, null)
Into l_country_code
From dual;
Update ap_invoice_distributions
Set global_attribute3 = l_ship_to_location,
global_attribute_category = l_gdf_category
Where invoice_id = P_Invoice_Id
-- Bug 13960680 Start
And invoice_line_number = P_Inv_Line_Num
-- Bug 13960680 End
And invoice_distribution_id = P_Inv_Dist_Id
And (global_attribute3 is null or global_attribute_category is null);
FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Upd_Dist_Gdf','Distribution Updated' );