[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT bk.withheld_amount_to_date
FROM ap_awt_buckets bk
WHERE bk.period_name = PerName
AND bk.tax_name = P_Tax_Name
AND bk.vendor_id = P_Vendor_Id
AND bk.org_id = g_org_id; -- bug 7301484
P_Last_Updated_By IN NUMBER,
P_Last_Update_Login IN NUMBER,
P_Program_Application_Id IN NUMBER,
P_Program_Id IN NUMBER,
P_Request_Id IN NUMBER,
P_Calling_Sequence IN VARCHAR2)
IS
DBG_Loc VARCHAR2(30) := 'Handle_Bucket';
SELECT 'Y'
FROM ap_awt_buckets bk
WHERE bk.period_name = P_Awt_Period_Name
AND bk.tax_name = P_Tax_Name
AND bk.vendor_id = P_Vendor_Id
AND bk.org_id = g_org_id -- bug 7301484
FOR UPDATE;
DO_NOT_UPDATE EXCEPTION;
RAISE DO_NOT_UPDATE;
debug_info := 'Update existing bucket';
UPDATE ap_awt_buckets
SET gross_amount_to_date = gross_amount_to_date +
NVL(P_Amount_Subject, 0),
withheld_amount_to_date = withheld_amount_to_date +
NVL(P_Amount_Withheld, 0),
last_update_date = SYSDATE,
last_updated_by = P_Last_Updated_By,
last_update_login = P_Last_Update_Login,
program_update_date = SYSDATE,
program_application_id = P_Program_Application_Id,
program_id = P_Program_Id,
request_id = P_Request_Id
WHERE CURRENT OF c_get_bucket;
INSERT INTO ap_awt_buckets
(period_name
,tax_name
,vendor_id
,withheld_amount_to_date
,gross_amount_to_date
,last_update_date
,last_updated_by
,last_update_login
,creation_date
,created_by
,program_update_date
,program_application_id
,program_id
,request_id
,org_id /* Bug 3700128. MOAC Project */
)
VALUES (P_Awt_Period_Name
,P_Tax_Name
,P_Vendor_Id
,NVL(P_Amount_Withheld, 0)
,NVL(P_Amount_Subject, 0)
,SYSDATE
,P_Last_Updated_By
,P_Last_Update_Login
,SYSDATE
,P_Last_Updated_By
,SYSDATE
,P_Program_Application_Id
,P_Program_Id
,P_Request_Id
,g_org_id); /* Bug 3700128. MOAC Project */
WHEN DO_NOT_UPDATE THEN
NULL;
PROCEDURE Insert_Temp_Distribution(
InvoiceId IN NUMBER,
SuppId IN NUMBER,
PaymentNum IN NUMBER,
GroupId IN NUMBER,
TaxName IN VARCHAR2,
CodeCombinationId IN NUMBER,
GrossAmount IN NUMBER,
WithheldAmount IN NUMBER,
AwtDate IN DATE,
GLPeriodName IN VARCHAR2,
AwtPeriodType IN VARCHAR2,
AwtPeriodName IN VARCHAR2,
-- P_Awt_Related_Id IN NUMBER DEFAULT NULL, --Bug 6168793
CheckrunName IN VARCHAR2,
WithheldRateId IN NUMBER,
ExchangeRate IN NUMBER,
CurrCode IN VARCHAR2,
BaseCurrCode IN VARCHAR2,
auto_offset_segs IN VARCHAR2,
P_Calling_Sequence IN VARCHAR2,
HandleBucket IN VARCHAR2 DEFAULT 'N',
LastUpdatedBy IN NUMBER DEFAULT NULL,
LastUpdateLogin IN NUMBER DEFAULT NULL,
ProgramApplicationId IN NUMBER DEFAULT NULL,
ProgramId IN NUMBER DEFAULT NULL,
RequestId IN NUMBER DEFAULT NULL,
CallingModule IN VARCHAR2 DEFAULT NULL,
P_Invoice_Payment_Id IN NUMBER DEFAULT NULL,
invoice_exchange_rate IN NUMBER DEFAULT NULL,
GLOBAL_ATTRIBUTE_CATEGORY IN VARCHAR2 DEFAULT NULL,
GLOBAL_ATTRIBUTE1 IN VARCHAR2 DEFAULT NULL,
GLOBAL_ATTRIBUTE2 IN VARCHAR2 DEFAULT NULL,
GLOBAL_ATTRIBUTE3 IN VARCHAR2 DEFAULT NULL,
GLOBAL_ATTRIBUTE4 IN VARCHAR2 DEFAULT NULL,
GLOBAL_ATTRIBUTE5 IN VARCHAR2 DEFAULT NULL,
GLOBAL_ATTRIBUTE6 IN VARCHAR2 DEFAULT NULL,
GLOBAL_ATTRIBUTE7 IN VARCHAR2 DEFAULT NULL,
GLOBAL_ATTRIBUTE8 IN VARCHAR2 DEFAULT NULL,
GLOBAL_ATTRIBUTE9 IN VARCHAR2 DEFAULT NULL,
GLOBAL_ATTRIBUTE10 IN VARCHAR2 DEFAULT NULL,
GLOBAL_ATTRIBUTE11 IN VARCHAR2 DEFAULT NULL,
GLOBAL_ATTRIBUTE12 IN VARCHAR2 DEFAULT NULL,
GLOBAL_ATTRIBUTE13 IN VARCHAR2 DEFAULT NULL,
GLOBAL_ATTRIBUTE14 IN VARCHAR2 DEFAULT NULL,
GLOBAL_ATTRIBUTE15 IN VARCHAR2 DEFAULT NULL,
GLOBAL_ATTRIBUTE16 IN VARCHAR2 DEFAULT NULL,
GLOBAL_ATTRIBUTE17 IN VARCHAR2 DEFAULT NULL,
GLOBAL_ATTRIBUTE18 IN VARCHAR2 DEFAULT NULL,
GLOBAL_ATTRIBUTE19 IN VARCHAR2 DEFAULT NULL,
GLOBAL_ATTRIBUTE20 IN VARCHAR2 DEFAULT NULL,
p_checkrun_id in number default null,
P_Awt_Related_Id IN NUMBER DEFAULT NULL, --bug6524425
UnroundedAWTAmount IN number default null --Bug 10262174
)
IS
base_WT_amount NUMBER;
DBG_Loc VARCHAR2(30) := 'Insert_Temp_Distribution';
SELECT (aid.amount * Withheld_amt / proration_divisor) prorated_awt_amt,
(aid.amount * Unrounded_AWT_Amount / proration_divisor) Unrounded_Protd_AWT_Amount, -- Bug10262174
(NVL(aid.base_amount,amount) * Withheld_amt/ proration_base_divisor) prorated_base_awt_amt,
invoice_distribution_id,
line_type_lookup_code,
related_id,
accounting_event_id,
NVL(historical_flag, 'N') historical_flag
FROM ap_invoice_distributions AID
WHERE aid.invoice_id = p_invoice_id
--Bug 7217385 modified the below line
--AND aid.line_type_lookup_code NOT IN ('AWT','PREPAY')
AND aid.line_type_lookup_code <> 'AWT'
--AND nvl(aid.reversal_flag,'N') <>'Y' --Bug 8731982 --commented for bug 13258653
--Bug6660355
AND (( aid.awt_group_id IS NOT NULL
AND aid.awt_group_id = p_awt_group_id)
OR
( aid.pay_awt_group_id IS NOT NULL
AND aid.pay_awt_group_id = p_awt_group_id));
SELECT SUM(amount), count(1)
FROM ap_invoice_distributions_all aid,
xla_events evnt,
xla_ae_headers xah,
ap_system_parameters_all asp
WHERE aid.accounting_event_id = p_acct_event_id
AND aid.accounting_event_id = evnt.event_id
AND evnt.event_type_code in ('INVOICE ADJUSTED',
'CREDIT MEMO ADJUSTED',
'PREPAYMENT ADJUSTED',
'DEBIT MEMO ADJUSTED')
AND aid.org_id = asp.org_id
AND automatic_offsets_flag = 'N'
AND aid.historical_flag = 'Y'
AND evnt.application_id = 200
AND evnt.event_status_code = 'P'
AND evnt.upg_batch_id > 0
AND evnt.event_id = xah.event_id
AND xah.ledger_id = asp.set_of_books_id
AND xah.application_id = 200
AND xah.upg_batch_id > 0;
current_calling_sequence := 'AP_CALC_WITHHOLDING_PKG.<-Insert_Temp_Distribution' ||
P_Calling_Sequence;
SELECT org_id
INTO g_org_id
FROM AP_Invoices
WHERE Invoice_ID = InvoiceId;
convertion is not required. Hence delete the if condition below */
/* Bug 4721994 commented the below code as rounding should be done after prorating the awt amount*/
-- base_WT_amount := Ap_Utilities_Pkg.ap_round_currency(WithheldAmount,BaseCurrCode); -- R11: Xcurr
SELECT SUM(NVL(AID.amount,0)),
SUM(NVL(NVL(AID.base_amount,aid.amount),0))
INTO l_proration_divisor,
l_proration_base_divisor
FROM ap_invoice_distributions AID
WHERE aid.invoice_id = Invoiceid
--Bug 7217385 modified the below line
--AND aid.line_type_lookup_code NOT IN ('AWT','PREPAY')
AND aid.line_type_lookup_code <> 'AWT'
--Bug6660355
AND (( aid.awt_group_id IS NOT NULL
AND aid.awt_group_id = groupid)
OR
(aid.pay_awt_group_id IS NOT NULL
AND aid.pay_awt_group_id = groupid));
SELECT count(*)
INTO l_dist_grp_count
FROM ap_invoice_distributions_all aid
WHERE aid.invoice_id = InvoiceId
AND aid.accounting_event_id = rec_prorate_awt_lines.accounting_event_id
AND aid.historical_flag = 'Y'
AND ((aid.awt_group_id IS NOT NULL
AND aid.awt_group_id = groupid)
/* bug 13258653 start */
OR (aid.pay_awt_group_id IS NOT NULL
AND aid.pay_awt_group_id = groupid)
/* bug 13258653 end */
);
debug_info := 'Insert INTO ap_awt_temp_distributions';
INSERT INTO ap_awt_temp_distributions_all
(invoice_id
,payment_num
,group_id
,tax_name
,tax_code_combination_id
,gross_amount
,withholding_amount
,base_withholding_amount
,accounting_date
,period_name
,checkrun_name
,tax_rate_id
,invoice_payment_id
,awt_related_id
,GLOBAL_ATTRIBUTE_CATEGORY
,GLOBAL_ATTRIBUTE1
,GLOBAL_ATTRIBUTE2
,GLOBAL_ATTRIBUTE3
,GLOBAL_ATTRIBUTE4
,GLOBAL_ATTRIBUTE5
,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
,GLOBAL_ATTRIBUTE19
,GLOBAL_ATTRIBUTE20
,ORG_ID /* bug 3700128. MOAC Project */
,checkrun_id)
VALUES
(InvoiceId
,PaymentNum
,GroupId
,TaxName
,CodeCombinationId
,GrossAmount
,l_prorated_withheld_amt
,l_prorated_base_withheld_amt
,AwtDate
,GLPeriodName
,CheckrunName
,WithheldRateId
,P_Invoice_Payment_Id
,l_awt_related_id
,GLOBAL_ATTRIBUTE_CATEGORY
,GLOBAL_ATTRIBUTE1
,GLOBAL_ATTRIBUTE2
,GLOBAL_ATTRIBUTE3
,GLOBAL_ATTRIBUTE4
,GLOBAL_ATTRIBUTE5
,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
,GLOBAL_ATTRIBUTE19
,GLOBAL_ATTRIBUTE20
,g_org_id /* Bug 3700128. MOAC Project */
,p_checkrun_id);
UPDATE ap_awt_temp_distributions_all
SET withholding_amount = (withholding_amount +
l_round_withheld_amt),
base_withholding_amount = (base_withholding_amount +
l_round_base_withheld_amt)
WHERE invoice_id = InvoiceId
AND nvl(payment_num , -99) = nvl(PaymentNum , -99)
AND group_id = GroupId
AND tax_name = TaxName
AND tax_code_combination_id = CodeCombinationId
AND gross_amount = GrossAmount
AND withholding_amount = l_prorated_withheld_amt
AND base_withholding_amount = l_prorated_base_withheld_amt
AND accounting_date = AwtDate
AND period_name = GLPeriodName
AND nvl(checkrun_name , '-99') = nvl(CheckrunName , '-99')
AND tax_rate_id = WithheldRateId
AND nvl(invoice_payment_id , -99) = nvl(P_Invoice_Payment_Id , -99)
AND nvl(checkrun_id , -99) = nvl(p_checkrun_id , -99)
AND awt_related_id = l_awt_related_id;
debug_info := 'Insert into ap_awt_temp_distributions';
insert into ap_awt_temp_distributions_all
(invoice_id
,payment_num
,group_id
,tax_name
,tax_code_combination_id
,gross_amount
,withholding_amount
,base_withholding_amount
,accounting_date
,period_name
,checkrun_name
,tax_rate_id
,invoice_payment_id
,awt_related_id --Added Bug 6168793
,GLOBAL_ATTRIBUTE_CATEGORY
,GLOBAL_ATTRIBUTE1
,GLOBAL_ATTRIBUTE2
,GLOBAL_ATTRIBUTE3
,GLOBAL_ATTRIBUTE4
,GLOBAL_ATTRIBUTE5
,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
,GLOBAL_ATTRIBUTE19
,GLOBAL_ATTRIBUTE20
,ORG_ID
,CHECKRUN_ID
)
values
(InvoiceId
,PaymentNum
,GroupId
,TaxName
,CodeCombinationId
,GrossAmount
,Withheld_Amt
,base_WT_amount
,AwtDate
,GLPeriodName
,CheckrunName
,WithheldRateId
,P_Invoice_Payment_Id
,P_Awt_Related_Id --Added Bug 6168793
,GLOBAL_ATTRIBUTE_CATEGORY
,GLOBAL_ATTRIBUTE1
,GLOBAL_ATTRIBUTE2
,GLOBAL_ATTRIBUTE3
,GLOBAL_ATTRIBUTE4
,GLOBAL_ATTRIBUTE5
,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
,GLOBAL_ATTRIBUTE19
,GLOBAL_ATTRIBUTE20
,G_ORG_ID
,P_CHECKRUN_ID --4759533
);
LastUpdatedBy,
LastUpdateLogin,
ProgramApplicationId,
ProgramId,
RequestId,
current_calling_sequence);
END Insert_Temp_Distribution;
P_Last_Updated_By IN NUMBER,
P_Last_Update_Login IN NUMBER,
P_Program_Application_Id IN NUMBER,
P_Program_Id IN NUMBER,
P_Request_Id IN NUMBER,
P_AWT_Success IN OUT NOCOPY VARCHAR2,
P_Calling_Sequence IN VARCHAR2,
P_Invoice_Payment_Id IN NUMBER DEFAULT NULL,
P_checkrun_id in number default null,
p_org_id in number default null) --4742265
IS
/*
Copyright (c) 1995 by Oracle Corporation
NAME
AP_Calculate_AWT_Amounts - First Unit of general Ap_Do_Withholding
DESCRIPTION
Calculate WT amounts that will be stored in temporary distributions
NOTES
This PROCEDURE IS part of the AP_AWT PL/SQL package
HISTORY (YY/MM/DD)
atassoni.it 95/04/26 Code refinements
mhtaylor.uk atassoni.it 95/04/21 First executable version
atassoni.it 95/04/12 Creation
<< Beginning of AP_Calculate_AWT_Amounts program documentation >>
Flow chart of this PROCEDURE:
*--------------------------------*
| BEGIN AP_Calculate_AWT_Amounts |
*--------------------------------*
|
v
*----------------------------------------------------------------------------*
| Set up withholding environment: |
| - Get INVOICE basic information and supplier identification |
| - Get GROUP AMOUNTS and set number of GROUPS found |
| - Calculate TOTAL INVOICE AMOUNT from distribution lines |
| - Adjust figures for possible discount on invoice |
| - Recalculate the amounts subject to WT in proportion to the payment |
| - Set starting group FOR WT calculation, excluding the exempt if existent |
*----------------------------------------------------------------------------*
|
| *---------------------------------*
+->+-> | Loop for each withholding Group |
| *---------------------------------*
| |
| v
| *-------------------------------------------------------*
| | Reset amount subject for tax, rank and rank cumulator |
| *-------------------------------------------------------*
| |
| | *----------------------------*
| +----->+-> | Loop for each tax in group |
| | *----------------------------*
| | |
| | v
| | *----------------------------------------*
| | | Check if tax has valid characteristics |
| | *----------------------------------------*
| | |
| | v
| | *------------------------------------------------------------*
| | | (Re-)Calculate rank, cumulator and amount subject FOR tax |
| | *------------------------------------------------------------*
| | |
| | v
| | *--------------------------------------*
| | | Check for CUSTOM withholding figures | ===> goto next tax
| | *--------------------------------------* when found
| | |
| | v
| | *-------------------------------------------------*
| | | Get withholding figures FOR EXCEPTION AND apply |
| | | Cut Off (VALUES could be NULL) |
| | *-------------------------------------------------*
| | |
| | v
| | *-----------------------------------------*
| | +--| Get withholding figures FOR CERTIFICATE |
| | | | (VALUES could be NULL) |
| | | *-----------------------------------------*
| | |
| | +--> Withholding Tax Rate FOR Certificate IS not NULL?
| |
| | ,'`. *--------------------------------------------*
| | +-- < IF > ------> | Calculate proper WT amount FOR CERTIFICATE |
| | | `.,' Yes | applying Cut Off |
| | | No *--------------------------------------------*
| | v |
| | *----------------------------* | *----------------------------*
| | | Set to NULL the WT | +--> | Confirm WT Rate AND Amount |
| | | amount FOR CERTIFICATE | | (the greater between |
| | *----------------------------* | CertIFicate AND Exception) |
| | | *----------------------------*
| | v |
| | *----------------------------* |
| | | SAVEPOINT | |
| | | ~~~~~~~~~ | |
| | | BEFORE the AMOUNT RANGES | |
| | | have been processed | |
| | *----------------------------* |
| | | |
| | v |
| | *-----------------------------* |
| | | Get withholding figures FOR | |
| | | AMOUNT RANGES, manipulating | |
| | | the database IF necessary | v
| | *-----------------------------* *-------------------------------*
| | | | Insert Temporary Distribution |
| | | | AND Update Bucket |
| | +--> Were RANGES applicable? *-------------------------------*
| | |
| | ,'`. |
| | +--- < IF > -----------+ |
| | | No `.,' Yes | |
| | | v |
| | v *------------------------------------* |
| | *------------------* | Single amounts withheld on RANGES | |
| | | Calculate amount | | have already been INSERTed as tem- | |
| | | withheld in the | | porary distributions, with bucket, | |
| | | normal case, | | while getting withholding figures | |
| | | round AND apply | | FOR ranges (two steps above) | |
| | | CUT OFF | *------------------------------------* |
| | *------------------* | |
| | | +--> WT Amount withheld FOR Ranges |
| | v IS less THEN |
| | *------------------* WT Amount withheld FOR Exception? |
| | | Confirm WT Rate | |
| | | AND Amount (the | ,'`. *---------------------* |
| | | greater between | < IF > ---> | Undo changes due to | |
| | | Normal AND Ex- | `.,' Yes | ranges (rollback to | |
| | | ception) | | | the savepoint) | |
| | *------------------* | No *---------------------* |
| | | | | |
| | | | v |
| | v | *---------------------* |
| | *------------------* | | Insert Temporary | |
| | | Insert Temporary | | | Distribution with | |
| | | Distribution AND | | | EXCEPTION data THEN | |
| | | Update Bucket | | | Update Bucket | |
| | *------------------* | *---------------------* |
| | | | | |
| | | | v |
| | | +----------->+ |
| | | | |
| | +<--------------------------------------+<--------------------+
| | |
| | +--> Is there another Tax in this Group?
| |
| | ,'`.
| | Yes ,' `.
| +-------
| `. ,'
| `.,'
| No |
| +--> Is there another Withholding Group FOR this invoice?
|
| ,'`.
| Yes ,' `.
+---------------
`. ,'
`.,'
No |
v
*------------------------------*
| END AP_Calculate_AWT_Amounts |
*------------------------------*
<< End of AP_Calculate_AWT_Amounts program documentation >>
*/
-- PL/SQL Main Block Constants AND Variables:
currency_code ap_invoices.invoice_currency_code%TYPE;
SELECT name
FROM ap_awt_groups
WHERE (group_id = GroupId);
SELECT detail_posting_allowed_flag,
start_date_active,
end_date_active,
template_id,
enabled_flag,
summary_flag
FROM gl_code_combinations
WHERE CodeCombinationId = code_combination_id;
SELECT tax_rate,
tax_rate_id
FROM ap_awt_tax_rates
WHERE invoice_num = InvNum
AND vendor_id = SuppId
AND vendor_site_id = SuppSiteId
AND tax_name = TaxName
AND OpDate BETWEEN NVL(start_date, OpDate - 1)
AND NVL(end_date, OpDate + 1)
AND rate_type = 'EXCEPTION'
AND org_id = g_org_id; -- bug 7301484
SELECT tax_rate,
tax_rate_id
FROM ap_awt_tax_rates
WHERE tax_name = TaxName
AND OpDate BETWEEN NVL(start_date, OpDate - 1)
AND NVL(end_date, OpDate + 1)
AND rate_type = 'STANDARD'
AND org_id = g_org_id; -- bug 7301484
SELECT tax_rate,
tax_rate_id
FROM ap_awt_tax_rates
WHERE tax_name = TaxName
AND vendor_id = SuppId
AND vendor_site_id = SuppSiteId
AND OpDate BETWEEN NVL(start_date, OpDate - 1)
AND NVL(end_date, OpDate + 1)
AND rate_type = 'CERTIFICATE'
AND org_id = g_org_id -- bug 7301484
ORDER BY priority ASC;
LastUpdatedBy IN NUMBER,
LastUpdateLogin IN NUMBER,
ProgramApplicationId IN NUMBER,
ProgramId IN NUMBER,
RequestId IN NUMBER,
CallingModule IN VARCHAR2,
RangesWTAmount OUT NOCOPY NUMBER,
RangesNumber OUT NOCOPY NUMBER,
RangesRateNOTFOUND OUT NOCOPY BOOLEAN,
RangesINVALID OUT NOCOPY BOOLEAN,
RangesDatesINVALID OUT NOCOPY BOOLEAN,
P_Calling_Sequence IN VARCHAR2,
P_Invoice_Payment_Id IN NUMBER DEFAULT NULL,
auto_offset_segs IN VARCHAR2,
cert_withholding_rate IN NUMBER,
cert_withholding_rate_id IN NUMBER,
p_checkrun_id in number default null)
IS
-- This PROCEDURE also INSERTs temporary distribution lines due
-- to the ranges AND triggers the corresponding bucket INSERT OR
-- UPDATE.
DBG_Loc VARCHAR2(30) := 'Get_Withholding_On_Ranges';
SELECT tax_rate
, tax_rate_id
, start_amount
, end_amount
, NVL(start_date, OpDate - 1) start_date
, NVL(end_date, OpDate + 1) end_date
FROM ap_awt_tax_rates
WHERE tax_name = TaxCode
AND rate_type = 'STANDARD'
AND OpDate BETWEEN NVL(start_date, OpDate - 1)
AND NVL(end_date, OpDate + 1)
AND org_id = g_org_id -- bug 7301484
ORDER BY start_amount asc;
invalid_range_dates := TRUE; -- Selected ranges MUST have identical
SELECT gross_amount_to_date
FROM ap_awt_buckets
WHERE (period_name = AwtPeriodName)
AND (tax_name = TaxName)
AND (vendor_id = SuppId);
select nvl(sum(awt_gross_amount), 0) gross_amount_to_date
from ap_invoice_distributions_all aid
where line_type_lookup_code = 'AWT'
--get the gross amount from minimum awt_related_id when multiple ranges are applied from a tax code
and aid.awt_related_id =
(
select min(aidx.awt_related_id) from ap_invoice_distributions_all aidx
where aidx.invoice_id = aid.invoice_id
and aidx.awt_origin_group_id = aid.awt_origin_group_id
and aidx.awt_invoice_payment_id = aid.awt_invoice_payment_id
)
--get the gross amount from minimum tax code id when multiple tax codes are applied in a tax group
and aid.withholding_tax_code_id =
(
select min(aidx.withholding_tax_code_id) from ap_invoice_distributions_all aidx
where aidx.invoice_id = aid.invoice_id
and aidx.awt_origin_group_id = aid.awt_origin_group_id
and aidx.awt_invoice_payment_id = aid.awt_invoice_payment_id
)
and aid.awt_invoice_payment_id is not null
and aid.invoice_id = p_invoice_id
and aid.awt_origin_group_id = p_group_id;
Insert_Temp_Distribution (
InvoiceId,
SuppId,
PaymentNum,
GroupId,
TaxName,
CodeCombinationId,
current_amount_to_withhold,
current_amount_withheld,
GlDate, /* Changed OpDate with GlDate for bug#6605368 */
GLPeriodName,
AwtPeriodType,
AwtPeriodName,
CheckrunName,
tab_tax_rate_id(k),
ExchangeRate,
CurrCode,
BaseCurrCode,
NULL,
current_calling_sequence,
DO_HANDLE_BUCKET,
LastUpdatedBy,
LastUpdateLogin,
ProgramApplicationId,
ProgramId,
RequestId,
CallingModule,
P_Invoice_Payment_Id,
p_checkrun_id => p_checkrun_id,
UnroundedAWTAmount => Unrounded_Curr_Amt_Withheld -- Bug 10262174
);
SELECT GPS.period_name,
P_Awt_Date
INTO gl_period_name,
gl_awt_date
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 P_Awt_Date BETWEEN GPS.start_date
AND GPS.end_date
AND (
(GPS.closing_status IN ('O', 'F'))
OR
(P_Calling_Module IN ('INVOICE INQUIRY','AWT REPORT'))
)
AND NVL(GPS.ADJUSTMENT_PERIOD_FLAG, 'N') = 'N'
AND ASP.ORG_ID = nvl(P_ORG_ID, asp.org_id); --4742265
SELECT invoice_currency_code,
payment_currency_code,
payment_cross_rate,
exchange_rate,
invoice_num,
vendor_id,
vendor_site_id,
org_id /* Bug 3700128. MOAC Project */
INTO currency_code,
payment_currency_code,
payment_cross_rate,
invoice_exchange_rate,
invoice_number,
supplier_id,
supplier_site_id,
g_org_id /* Bug 3700128. MOAC Project */
FROM ap_invoices_all
WHERE invoice_id = P_Invoice_Id;
SELECT base_currency_code
INTO functional_currency
FROM ap_system_parameters_all
WHERE org_id = g_org_id; --4742265
SELECT D.group_id
,DECODE (SIGN(WTDate - G.inactive_date),
0, 'Y',
1, 'Y', 'N') non_valid_group,
SUM(D.amount * NVL(invoice_exchange_rate,1)) group_amount,
SUM(DECODE (D.line_type_lookup_code,
'TAX', NVL(D.base_amount, D.amount) ,0)) vat_amount,
max(D.accounting_date) max_gl_date /* Added for bug#6605368 */
FROM (select DECODE(AIP.create_awt_dists_type,'BOTH',decode(p_calling_module,'AUTOAPPROVAL',
AID.awt_group_id,AID.pay_awt_group_id),
'PAYMENT',AID.pay_awt_group_id,AID.awt_group_id) group_id,
AID.amount,AID.base_amount,AID.line_type_lookup_code, AID.accounting_date /* Added AID.accounting_date for bug#6605368 */
from ap_invoice_distributions_all AID,ap_system_parameters_all AIP
where AID.invoice_id = InvId
AND AID.org_id = AIP.org_id ) D,
ap_awt_groups G
where D.group_id = G.group_id(+)
AND D.line_type_lookup_code <> 'AWT'
GROUP BY D.group_id,
DECODE ( SIGN(WTDate - G.inactive_date),
0, 'Y',
1, 'Y','N')
HAVING SUM(D.amount) <> 0
ORDER BY DECODE(D.group_id, NULL, 0, 1);
SELECT glps.period_name
INTO tab_max_gl_period(i)
FROM gl_period_statuses glps,
ap_invoices_all ai
WHERE glps.application_id = 200
AND nvl(glps.adjustment_period_flag, 'N') <>'Y'
AND (
(GLPS.closing_status IN ('O', 'F'))
OR
(P_Calling_Module IN ('INVOICE INQUIRY','AWT REPORT'))
)
AND tab_max_gl_date(i) BETWEEN glps.start_date AND glps.end_date
AND ai.set_of_books_id = glps.set_of_books_id
AND ai.invoice_id = p_invoice_id;
SELECT SUM
(NVL(S.discount_amount_available, 0)
+NVL(S.second_disc_amt_available, 0)
+NVL(S.third_disc_amt_available, 0)) discount,
P.disc_is_inv_less_tax_flag
FROM ap_payment_schedules S,
ap_system_parameters P
WHERE S.invoice_id = InvId
/* Next condition is to make ok the projected withholding screen */
AND S.payment_num = NVL(PaymNum, S.payment_num)
AND P.awt_include_discount_amt = 'N'
AND P.org_id = p_org_id
AND P.org_id = s.org_id --4742265
--bug5052436 modifies the above predicate
GROUP BY P.disc_is_inv_less_tax_flag;
SELECT AAGT.rank
, AAGT.tax_name
, ATC.range_amount_basis
, ATC.range_period_basis
, AOP.period_name
, ATC.awt_period_type
, ATC.awt_period_limit
, ATC.inactive_date
, ATC.enabled_flag -- bug 6911736
, ATC.tax_code_combination_id
FROM ap_awt_group_taxes AAGT
, ap_tax_codes ATC
, ap_other_periods AOP
WHERE (AAGT.group_id = GrpId)
AND (AAGT.tax_name = ATC.name)
AND (ATC.tax_type = 'AWT') -- BUG 3665866
AND (AOP.application_id (+) = 200)
AND (AOP.module (+) = 'AWT')
AND (AOP.period_type (+) = ATC.awt_period_type)
AND (AOP.start_date (+) <= TRUNC(gl_awt_date))
AND (AOP.end_date (+) >= TRUNC(gl_awt_date))
AND (AAGT.org_id = ATC.org_id) -- bug 7301484
AND (ATC.org_id = g_org_id) -- bug 7301484
ORDER BY rank ASC, ATC.name;
SELECT MIN(rank)
FROM ap_awt_group_taxes
WHERE group_id = tab_group_id(g);
SELECT tax_rate
FROM ap_awt_tax_rates
WHERE tax_rate_id = TaxRateId;
Insert_Temp_Distribution (P_Invoice_Id,
supplier_id,
P_Payment_Num,
tab_group_id(g),
rec_group_taxes.tax_name,
rec_group_taxes.tax_code_combination_id,
current_amount_subject_for_tax,
custom_wt_amount,
max_gl_dist_date, /* Changed from gl_awt_date to max_gl_dist_date for bug#6605368 */
max_gl_date_period, /* Changed from gl_period_name to max_gl_date_period for bug#6605368 */
rec_group_taxes.awt_period_type,
rec_group_taxes.period_name,
P_Checkrun_Name,
custom_awt_tax_rate_id,
invoice_exchange_rate,
FUNCTIONal_currency,
FUNCTIONal_currency,
NULL,
current_calling_sequence,
DO_HANDLE_BUCKET,
P_Last_Updated_By,
P_Last_Update_Login,
P_Program_Application_Id,
P_Program_Id,
P_Request_Id,
P_Calling_Module,
P_Invoice_Payment_Id,
p_checkrun_id => p_checkrun_id,
UnroundedAWTAmount => unrounded_custom_wt_amount -- Bug 10262174
);
Insert_Temp_Distribution(P_Invoice_Id,
supplier_id,
P_Payment_Num,
tab_group_id(g),
rec_group_taxes.tax_name,
rec_group_taxes.tax_code_combination_id,
current_amount_subject_for_tax,
withheld_amount,
max_gl_dist_date, /* Changed from gl_awt_date to max_gl_dist_date for bug#6605368 */
max_gl_date_period, /* Changed from gl_period_name to max_gl_date_period for bug#6605368 */
rec_group_taxes.awt_period_type,
rec_group_taxes.period_name,
P_Checkrun_Name,
withheld_rate_id,
invoice_exchange_rate,
functional_currency,
functional_currency,
NULL,
current_calling_sequence,
DO_HANDLE_BUCKET,
P_Last_Updated_By,
P_Last_Update_Login,
P_Program_Application_Id,
P_Program_Id,
P_Request_Id,
P_Calling_Module,
P_Invoice_Payment_Id,
p_checkrun_id => p_checkrun_id,
UnroundedAWTAmount => unrounded_withheld_amount -- Bug 10262174
);
P_Last_Updated_By,
P_Last_Update_Login,
P_Program_Application_Id,
P_Program_Id,
P_Request_Id,
P_Calling_Module,
ranges_withholding_amount,
number_of_ranges,
ranges_rate_notfound,
incorrect_ranges,
incorrect_range_dates,
current_calling_sequence,
P_Invoice_Payment_Id,
NULL,
cert_withholding_rate ,
cert_withholding_rate_id,
p_checkrun_id);
Insert_Temp_Distribution (
P_Invoice_Id,
supplier_id,
P_Payment_Num,
tab_group_id(g),
rec_group_taxes.tax_name,
rec_group_taxes.tax_code_combination_id,
current_amount_subject_for_tax,
EXCEPTION_withholding_amount,
max_gl_dist_date, /* Changed from gl_awt_date to max_gl_dist_date for bug#6605368 */
max_gl_date_period, /* Added for bug#6605368 */
--gl_period_name, bug6877813
rec_group_taxes.awt_period_type,
rec_group_taxes.period_name,
P_Checkrun_Name,
EXCEPTION_withholding_rate_id,
invoice_exchange_rate,
FUNCTIONal_currency,
FUNCTIONal_currency,
NULL,
current_calling_sequence,
DO_HANDLE_BUCKET,
P_Last_Updated_By,
P_Last_Update_Login,
P_Program_Application_Id,
P_Program_Id,
P_Request_Id,
P_Calling_Module,
P_Invoice_Payment_Id,
p_checkrun_id => p_checkrun_id,
UnroundedAWTAmount => exception_withholding_amount -- Bug 10262174
);
Insert_Temp_Distribution (
P_Invoice_Id,
supplier_id,
P_Payment_Num,
tab_group_id(g),
rec_group_taxes.tax_name,
rec_group_taxes.tax_code_combination_id,
current_amount_subject_for_tax,
withheld_amount,
max_gl_dist_date, /* Changed from gl_awt_date to max_gl_dist_date for bug#6605368 */
max_gl_date_period, /* Added for bug#6605368 */
--gl_period_name, bug6877813
rec_group_taxes.awt_period_type,
rec_group_taxes.period_name,
P_Checkrun_Name,
withheld_rate_id,
invoice_exchange_rate,
functional_currency,
functional_currency,
NULL,
current_calling_sequence,
DO_HANDLE_BUCKET,
P_Last_Updated_By,
P_Last_Update_Login,
P_Program_Application_Id,
P_Program_Id,
P_Request_Id,
P_Calling_Module,
P_Invoice_Payment_Id,
p_checkrun_id => p_checkrun_id,
UnroundedAWTAmount => unrounded_withheld_amount --bug 10262174
);