[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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_AWT_Success IN OUT NOCOPY Varchar2,
P_Calling_Sequence IN Varchar2
);
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_Last_Updated_By IN Number,
P_Last_Update_Login IN Number,
P_Program_Application_Id IN Number Default Null,
P_Program_Id IN Number Default Null,
P_Request_Id IN Number Default Null,
P_AWT_Success OUT NOCOPY Varchar2
)
IS
------------------------
-- Variables Definition
------------------------
l_awt_flag ap_invoices.awt_flag%TYPE;
SELECT
nvl(awt_flag,'N') awt_flag,
invoice_currency_code,
invoice_type_lookup_code ,
gl_date -- bug: 8770258
-- As AWT_DATE=GL_DATE for colombia(Approval time AWT Generation)
INTO
l_awt_flag,
l_inv_curr_code,
l_invoice_type_lookup_code,
l_AWT_DATE -- bug: 8770258
FROM
ap_invoices
WHERE
invoice_id = P_Invoice_Id;
SELECT
nvl(create_awt_dists_type, 'NEVER'),
nvl(create_awt_invoices_type, 'NEVER')
INTO
l_create_dists,
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(l_AWT_Date) between
trunc(GPS.start_date) and trunc(GPS.end_date)
and GPS.closing_status IN ('O', 'F')
and nvl(gps.ADJUSTMENT_PERIOD_FLAG, 'N') = 'N';
P_Last_Updated_By,
P_Last_Update_Login,
P_Program_Application_Id,
P_Program_Id,
P_Request_Id,
l_AWT_success,
current_calling_sequence
);
P_Last_Updated_By,
P_Last_Update_Login,
P_Program_Application_Id,
P_Program_Id,
P_Request_Id,
current_calling_sequence
);
P_Last_Updated_By,
P_Last_Update_Login,
P_Program_Application_Id,
P_Program_Id,
P_Request_Id,
current_calling_sequence,
P_Calling_Module
);
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_AWT_Success IN OUT NOCOPY Varchar2,
P_Calling_Sequence IN Varchar2
)
IS
-------------------------------
-- Local Variables Definition
-------------------------------
l_previous_awt_type_code Varchar2(30);
SELECT
distinct nvl(substr(apid.global_attribute2,1,30),pove.segment1) nit
FROM
ap_invoices apin,
ap_invoice_distributions apid,
po_vendors pove
WHERE
apid.invoice_id = apin.invoice_id
AND pove.vendor_id = apin.vendor_id
AND apin.invoice_id = Inv_Id;
SELECT
jlst.awt_type_code awt_type_code,
jlsc.tax_id tax_id,
apin.invoice_id invoice_id,
pove2.vendor_id vendor_id,
apid.invoice_distribution_id invoice_distribution_id,
nvl(apin.base_amount, apin.invoice_amount) invoice_amount,
nvl(apid.base_amount, apid.amount) line_amount
FROM
jl_zz_ap_inv_dis_wh jlwh,
ap_invoices apin,
ap_invoice_distributions apid,
jl_zz_ap_supp_awt_types jlst,
jl_zz_ap_sup_awt_cd jlsc,
jl_zz_ap_awt_types jlat,
po_vendors pove,
po_vendors pove2
WHERE
apid.invoice_id = jlwh.invoice_id
AND apid.invoice_distribution_id = jlwh.invoice_distribution_id
AND apin.invoice_id = apid.invoice_id
AND pove.vendor_id = apin.vendor_id
AND pove2.segment1 = nvl(apid.global_attribute2,pove.segment1)
AND jlwh.supp_awt_code_id = jlsc.supp_awt_code_id
AND jlsc.supp_awt_type_id = jlst.supp_awt_type_id
AND jlat.awt_type_code = jlst.awt_type_code
AND jlwh.invoice_id = Inv_Id
AND nvl(apid.global_attribute2,pove.segment1)= Nit
AND NVL(apid.reversal_flag, 'N') <> 'Y' -- bug 7693731 Colombia AWT reverse
ORDER BY
jlat.user_defined_formula_flag,
jlst.awt_type_code,
jlsc.tax_id;
debug_info := 'Open Cursor to select all NIT within the invoice';
debug_info := 'Fetch cursor for each NIT selected';
tab_invoice_wh.DELETE;
-- Opens the cursor to select all the withholdings to process
---------------------------------------------------------------
debug_info := 'Open cursor for all the withholdings with same NIT';
-- Inserts temporary distribution lines
-----------------------------------------------------
Process_Withholdings (l_current_vendor_id,
rec_awt_type,
rec_suppl_awt_type,
P_AWT_Date,
l_gl_period_name,
l_base_currency_code,
l_user_defd_formula_exists,
l_nit,
tab_withholdings,
tab_invoice_wh,
P_AWT_Success,
P_Last_Updated_By,
P_Last_Update_Login,
P_Program_Application_Id,
P_Program_Id,
P_Request_Id,
P_Calling_Module
);
-- Inserts temporary distribution lines
-----------------------------------------------------
Process_Withholdings (l_current_vendor_id,
rec_awt_type,
rec_suppl_awt_type,
P_AWT_Date,
l_gl_period_name,
l_base_currency_code,
l_user_defd_formula_exists,
l_nit,
tab_withholdings,
tab_invoice_wh,
P_AWT_Success,
P_Last_Updated_By,
P_Last_Update_Login,
P_Program_Application_Id,
P_Program_Id,
P_Request_Id,
P_Calling_Module
);
debug_info := 'Close cursor for all selected NIT';
SELECT
jlat.user_defined_formula_flag user_defined_formula_flag
FROM
jl_zz_ap_inv_dis_wh jlwh,
ap_invoices apin,
ap_invoice_distributions apid,
jl_zz_ap_supp_awt_types jlst,
jl_zz_ap_sup_awt_cd jlsc,
jl_zz_ap_awt_types jlat,
po_vendors pove
WHERE
apid.invoice_id = jlwh.invoice_id
AND apid.invoice_distribution_id = jlwh.invoice_distribution_id
AND apin.invoice_id = apid.invoice_id
AND pove.vendor_id = apin.vendor_id
AND jlwh.supp_awt_code_id = jlsc.supp_awt_code_id
AND jlsc.supp_awt_type_id = jlst.supp_awt_type_id
AND jlat.awt_type_code = jlst.awt_type_code
AND jlwh.invoice_id = Inv_Id
AND nvl(apid.global_attribute2,pove.segment1)= Nit
AND nvl(jlat.user_defined_formula_flag,'N') = 'Y';
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
)
IS
l_revised_amount_flag Boolean := FALSE;
P_Last_Updated_By,
P_Last_Update_Login,
P_Program_Application_Id,
P_Program_Id,
P_Request_Id,
P_Calling_Module,
null, -- checkrun name
null, -- checkrun id
null, -- payment number
'JL.CO.APXINWKB.DISTRIBUTIONS',
P_NIT_Number
);