[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Update_Inv_Dists_To_Approved(
p_invoice_id IN NUMBER,
p_user_id IN NUMBER,
p_calling_sequence IN VARCHAR2);
PROCEDURE Update_Inv_Dists_To_Selected(
p_invoice_id IN NUMBER,
P_line_number IN NUMBER,
p_run_option IN VARCHAR2,
p_calling_sequence IN VARCHAR2);
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_system_user IN NUMBER,
p_holds IN OUT NOCOPY HOLDSARRAY,
p_holds_count IN OUT NOCOPY COUNTARRAY,
p_release_count IN OUT NOCOPY COUNTARRAY,
p_calling_sequence IN VARCHAR2);
PROCEDURE Update_Total_Dist_Amount(
p_invoice_id IN NUMBER,
p_calling_sequence IN VARCHAR2);
PROCEDURE update_payment_schedule_prepay(
p_invoice_id IN NUMBER,
p_apply_amount IN NUMBER,
p_amount_positive IN VARCHAR2,
p_payment_currency_code IN VARCHAR2,
p_user_id IN NUMBER,
p_last_update_login IN NUMBER,
p_calling_sequence IN VARCHAR2);
p_last_updated_by IN NUMBER,
p_last_update_login IN NUMBER,
p_calling_sequence IN VARCHAR2);
PROCEDURE Update_Pay_Sched_For_Awt(p_invoice_id IN NUMBER,
p_last_updated_by IN NUMBER,
p_last_update_login IN NUMBER,
p_calling_sequence IN VARCHAR2);
SELECT INVOICE_ID,
LINE_NUMBER,
LINE_TYPE_LOOKUP_CODE,
REQUESTER_ID,
DESCRIPTION,
LINE_SOURCE,
ORG_ID,
LINE_GROUP_NUMBER,
INVENTORY_ITEM_ID,
ITEM_DESCRIPTION,
SERIAL_NUMBER,
MANUFACTURER,
MODEL_NUMBER,
WARRANTY_NUMBER,
GENERATE_DISTS,
MATCH_TYPE,
DISTRIBUTION_SET_ID,
ACCOUNT_SEGMENT,
BALANCING_SEGMENT,
COST_CENTER_SEGMENT,
OVERLAY_DIST_CODE_CONCAT,
DEFAULT_DIST_CCID,
PRORATE_ACROSS_ALL_ITEMS,
ACCOUNTING_DATE,
PERIOD_NAME ,
DEFERRED_ACCTG_FLAG ,
DEF_ACCTG_START_DATE ,
DEF_ACCTG_END_DATE,
DEF_ACCTG_NUMBER_OF_PERIODS,
DEF_ACCTG_PERIOD_TYPE ,
SET_OF_BOOKS_ID,
AMOUNT,
BASE_AMOUNT,
ROUNDING_AMT,
QUANTITY_INVOICED,
UNIT_MEAS_LOOKUP_CODE ,
UNIT_PRICE,
WFAPPROVAL_STATUS,
DISCARDED_FLAG,
ORIGINAL_AMOUNT,
ORIGINAL_BASE_AMOUNT ,
ORIGINAL_ROUNDING_AMT ,
CANCELLED_FLAG ,
INCOME_TAX_REGION,
TYPE_1099 ,
STAT_AMOUNT ,
PREPAY_INVOICE_ID ,
PREPAY_LINE_NUMBER ,
INVOICE_INCLUDES_PREPAY_FLAG ,
CORRECTED_INV_ID ,
CORRECTED_LINE_NUMBER ,
PO_HEADER_ID,
PO_LINE_ID ,
PO_RELEASE_ID ,
PO_LINE_LOCATION_ID ,
PO_DISTRIBUTION_ID,
RCV_TRANSACTION_ID,
FINAL_MATCH_FLAG,
ASSETS_TRACKING_FLAG ,
ASSET_BOOK_TYPE_CODE ,
ASSET_CATEGORY_ID ,
PROJECT_ID ,
TASK_ID ,
EXPENDITURE_TYPE ,
EXPENDITURE_ITEM_DATE ,
EXPENDITURE_ORGANIZATION_ID ,
PA_QUANTITY,
PA_CC_AR_INVOICE_ID ,
PA_CC_AR_INVOICE_LINE_NUM ,
PA_CC_PROCESSED_CODE ,
AWARD_ID,
AWT_GROUP_ID ,
REFERENCE_1 ,
REFERENCE_2 ,
RECEIPT_VERIFIED_FLAG ,
RECEIPT_REQUIRED_FLAG ,
RECEIPT_MISSING_FLAG ,
JUSTIFICATION ,
EXPENSE_GROUP ,
START_EXPENSE_DATE ,
END_EXPENSE_DATE ,
RECEIPT_CURRENCY_CODE ,
RECEIPT_CONVERSION_RATE,
RECEIPT_CURRENCY_AMOUNT ,
DAILY_AMOUNT ,
WEB_PARAMETER_ID ,
ADJUSTMENT_REASON ,
MERCHANT_DOCUMENT_NUMBER ,
MERCHANT_NAME ,
MERCHANT_REFERENCE ,
MERCHANT_TAX_REG_NUMBER,
MERCHANT_TAXPAYER_ID ,
COUNTRY_OF_SUPPLY,
CREDIT_CARD_TRX_ID ,
COMPANY_PREPAID_INVOICE_ID,
CC_REVERSAL_FLAG ,
CREATION_DATE ,
CREATED_BY,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN ,
PROGRAM_APPLICATION_ID ,
PROGRAM_ID ,
PROGRAM_UPDATE_DATE,
REQUEST_ID ,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8,
ATTRIBUTE9 ,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13 ,
ATTRIBUTE14,
ATTRIBUTE15,
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 ,
INCLUDED_TAX_AMOUNT,
PRIMARY_INTENDED_USE,
APPLICATION_ID,
PRODUCT_TABLE,
REFERENCE_KEY1,
REFERENCE_KEY2,
REFERENCE_KEY3,
REFERENCE_KEY4,
REFERENCE_KEY5,
SHIP_TO_LOCATION_ID,
PAY_AWT_GROUP_ID --bug 7022001
FROM ap_invoice_lines_all
WHERE invoice_id = c_invoice_id
ORDER BY decode(line_type_lookup_code,'ITEM',1,2), line_number;
last_updated_by ap_holds_all.last_updated_by%type,
responsibility_id ap_holds_all.responsibility_id%type);
| p_begin_invoice_date Begin Invoice Date (Selection criteria)
| p_end_invoice_date End of Invoice Date (Selection criteria)
| p_pay_group Pay Group(Select criteria for Batch Approval)
| p_invoice_id Invoice_id
| p_entered_by Entered_by User id
| p_set_of_books_id Set of books id
| (Selection criteria for Batch Approval)
| p_trace_option
| p_conc_flag Indicate whether the approval process is a
| concurrent process or not or if it is online
| p_holds_count Return Hold Count of invoice (For Online
| Approval called by invoice workbench)
| p_approval_status Return Approval Status of invoice
| (For Online Approval called by form)
| p_calling_sequence Debugging string to indicate path of module
| calls to be printed out upon error.
| p_debug_switch Debug switch to be turned on or off
|
| PROGRAM FLOW
|
| Retrieve system variables to be used by Approval Program
| For each invoice
| IF invoice needs approving (i.e. not the case where run_option is 'New'
| and the invoice doesn't have any unapproved distributions)
| IF Accrual Basis is being used
| IF automatic offsets is enabled
| Populate Invoice Dist liability account
| Calculate Tax (Etax API) which will determine the tax amt and
| whether it is inclusive or exclusive...
| Check Line Variance
| Calculate Base Amount and round at Line level
| Call Etax api to 'Calculate Tax', which might return exclusive tax lines
| and/or inclusive tax amount.
| Open a Lines Cursor - loop for each Line
| If inclusive tax is returned by tax calculation api, then create taxable
| distributions for (line_amount - inclusive tax amount).
| If Line need to generate distributions
| check sufficient line data
| Generate distributions
| end if
| Update Invoice Distributions as selected for approval
| Execute Distribution variance check
| IPV/ERV creation and valid ERV ccid check
| Close Line Cursor if no more line to check
| Call Etax api 'Determine Recovery' to create Tax Distributions for the invoice.
| Open a Lines Cursor - loop for each Line
| Base amount calculation and rounding at Distribution Level for line
| Close Line Cursor if no more line to check
| Execute General Invoice Checks
| Get invoice matched status
| IF invoice is matched
| Execute Quantity Variance Check
| Execute Matched Checks
| Execute PO Final Close Check
| Validate Invoice for Tax (etax api), which will validate
| the document for tax information.
| IF invoice is not a matched prepayment
| Execute Funds Control (Funds Reservation)
| Execute Withholding Tax
| Update Invoice Dists to Appropriate Approval Status
| End Loop
| Accounting Event Generation
| IF Recalculate Payment Schedule Option is enabled
| Execute Due Date Sweeper
| If online approval then
| Calculate Invoice Hold Count and Release Count
| Print out appropriate Return Message
| End If
|
| KNOWN ISSUES:
| p_begin_invoice_date,
| p_end_invoice_date,
| p_pay_group,
| p_entered_by,
| p_set_of_books_id,
| p_trace_option
| are not needed here in this
| procedure. The logic of selecting all invoices included in a batch
| is in Invoice Validation Report. Code clean up should be done when
| invoice work bench form is being modified. Now is modified to have
| default value so that these two parameters can be omitted.
*============================================================================*/
PROCEDURE Approve(
p_run_option IN VARCHAR2,
p_invoice_batch_id IN NUMBER,
p_begin_invoice_date IN DATE DEFAULT NULL,
p_end_invoice_date IN DATE DEFAULT NULL,
p_vendor_id IN NUMBER,
p_pay_group IN VARCHAR2,
p_invoice_id IN NUMBER,
p_entered_by IN NUMBER,
p_set_of_books_id IN NUMBER,
p_trace_option IN VARCHAR2,
p_conc_flag IN VARCHAR2,
p_holds_count IN OUT NOCOPY NUMBER,
p_approval_status IN OUT NOCOPY VARCHAR2,
p_funds_return_code OUT NOCOPY VARCHAR2,
p_calling_mode IN VARCHAR2 DEFAULT 'APPROVE',
p_calling_sequence IN VARCHAR2,
p_debug_switch IN VARCHAR2 DEFAULT 'N',
p_budget_control IN VARCHAR2 DEFAULT 'Y',
p_commit IN VARCHAR2 DEFAULT 'Y') IS
CURSOR approve_invoice_cur IS
SELECT AI.invoice_id,
AI.invoice_num,
AI.invoice_amount,
AI.base_amount,
AI.exchange_rate,
AI.invoice_currency_code,
PVS.invoice_amount_limit,
nvl(PVS.hold_future_payments_flag,'N'),
AI.invoice_type_lookup_code,
AI.exchange_date,
AI.exchange_rate_type,
AI.vendor_id,
AI.invoice_date,
AI.org_id,
nvl(AI.disc_is_inv_less_tax_flag,'N'),
nvl(AI.exclude_freight_from_discount,'N'),
pvs.tolerance_id,
pvs.services_tolerance_id
FROM ap_invoices_all AI,
ap_suppliers PV,
ap_supplier_sites_all PVS
WHERE AI.invoice_id = p_invoice_id
AND AI.vendor_id = PV.vendor_id
AND AI.vendor_site_id = PVS.vendor_site_id;
SELECT AI.invoice_id,
AI.invoice_num,
AI.invoice_amount,
AI.base_amount,
AI.exchange_rate,
AI.invoice_currency_code,
NULL, -- invoice_amount_limit,
'N', -- hold_future_payments_flag
AI.invoice_type_lookup_code,
AI.exchange_date,
AI.exchange_rate_type,
AI.vendor_id,
AI.invoice_date,
AI.org_id,
nvl(AI.disc_is_inv_less_tax_flag,'N'),
nvl(AI.exclude_freight_from_discount,'N')
FROM ap_invoices_all AI
WHERE AI.invoice_id = p_invoice_id;
SELECT invoice_type_lookup_code
FROM ap_invoices_all
WHERE invoice_id = p_invoice_id;
g_org_holds.delete;
Update_Inv_Dists_To_Selected( l_invoice_id,
null ,
p_run_option,
l_curr_calling_sequence);
l_debug_info := 'Update Invoice Distributions to SELECTED';
Update_Inv_Dists_To_Selected(
l_invoice_id,
t_inv_lines_table(i).line_number,
p_run_option,
l_curr_calling_sequence);
t_inv_lines_table.DELETE;
SELECT SUM(amount)
INTO l_dist_total
FROM ap_invoice_distributions
WHERE invoice_id = l_invoice_id
AND ( (line_type_lookup_code NOT IN ('PREPAY','AWT') AND
prepay_tax_parent_id IS NULL) OR
(line_type_lookup_code = 'PREPAY' AND
nvl(invoice_includes_prepay_flag,'N') = 'Y') OR
(line_type_lookup_code IN ('REC_TAX', 'NONREC_TAX',
'TERV','TIPV','TRV') AND
nvl(invoice_includes_prepay_flag,'N') = 'Y' AND
prepay_tax_parent_id IS NOT NULL)
);
SELECT invoice_amount, base_amount
INTO l_inv_amount , l_inv_base_amount
FROM ap_invoices
WHERE invoice_id = l_invoice_id;
SELECT COUNT('X')
INTO l_item_count
FROM ap_invoice_distributions
WHERE invoice_id = l_invoice_id AND
line_type_lookup_code = 'ITEM';
SELECT SUM(base_amount)
INTO l_base_dist_total
FROM ap_invoice_distributions
WHERE invoice_id = l_invoice_id
AND (
(line_type_lookup_code NOT IN ('PREPAY','AWT') AND
prepay_tax_parent_id IS NULL) OR
(line_type_lookup_code = 'PREPAY' AND
nvl(invoice_includes_prepay_flag,'N') = 'Y') OR
(line_type_lookup_code IN ('REC_TAX', 'NONREC_TAX',
'TERV','TIPV','TRV') AND
nvl(invoice_includes_prepay_flag,'N') = 'Y' AND
prepay_tax_parent_id IS NOT NULL)
);
UPDATE ap_invoice_distributions
SET base_amount = base_amount - (l_base_dist_total - l_inv_base_amount)
WHERE invoice_id = l_invoice_id
AND invoice_distribution_id = (
SELECT MAX(AID1.invoice_distribution_id)
FROM ap_invoice_distributions AID1
WHERE AID1.invoice_id = l_invoice_id
AND AID1.line_type_lookup_code = 'ITEM'
/* Bug 3784909. Folowing two lines Added */
AND NVL(AID1.reversal_flag, 'N') <> 'Y'
AND NVL(AID1.posted_flag, 'N') = 'N'
AND ABS(AID1.amount) = (
SELECT MAX(ABS(AID2.amount))
FROM ap_invoice_distributions AID2
WHERE AID2.invoice_id = l_invoice_id
AND AID2.line_type_lookup_code = 'ITEM'
-- Bug 3784909. Folowing two lines Added
AND NVL(AID2.reversal_flag, 'N') <> 'Y'
AND NVL(AID2.posted_flag, 'N') = 'N'));
UPDATE ap_invoice_distributions
SET base_amount = base_amount - (l_base_dist_total - l_inv_base_amount)
WHERE invoice_id = l_invoice_id
AND invoice_distribution_id = (
SELECT MAX(AID3.invoice_distribution_id)
FROM ap_invoice_distributions AID3
WHERE AID3.invoice_id = l_invoice_id
AND AID3.line_type_lookup_code
IN ('FREIGHT','MISCELLANEOUS')
AND
/* Bug 3784909. Folowing two lines Added */
NVL(AID3.reversal_flag, 'N') = 'N'
AND NVL(AID3.posted_flag, 'N') = 'N'
AND ABS(AID3.amount) = (
SELECT MAX(ABS(AID4.amount))
FROM ap_invoice_distributions AID4
WHERE AID4.invoice_id = l_invoice_id
AND AID4.line_type_lookup_code
IN('FREIGHT','MISCELLANEOUS')
--Bug 3784909. Folowing two lines Added
AND NVL(AID4.reversal_flag, 'N') <> 'Y'
AND NVL(AID4.posted_flag, 'N') = 'N'));
l_debug_info := l_row_count||' rows updated.';
l_debug_info := 'No rows Updated';
l_debug_info := 'Update Total Distribution Amount';
Update_Total_Dist_Amount(l_invoice_id,
l_curr_calling_sequence);
SELECT invoice_type_lookup_code
INTO l_invoice_type_lookup_code
FROM ap_invoices_all
WHERE invoice_id = l_invoice_id;
SELECT count(*)
INTO l_prepay_dist_count
FROM ap_invoice_distributions_all
WHERE Invoice_ID = l_invoice_id
AND Line_Type_Lookup_Code = 'PREPAY'
AND Accounting_Event_ID IS NULL;
select count(*)
INTO l_encumbrance_exists
FROM ap_invoice_distributions aid
WHERE nvl(aid.encumbered_flag,'N') not in ('N','R') ----added check for 'R' due to bug 7264524
AND aid.invoice_id = l_invoice_id;
SELECT 'Y'
INTO l_lcm_used
FROM DUAL
WHERE EXISTS
(SELECT 1 FROM AP_INVOICE_DISTRIBUTIONS aid, RCV_TRANSACTIONS rt
WHERE aid.invoice_id = l_invoice_id
AND aid.rcv_transaction_id = rt.transaction_id
AND rt.lcm_shipment_line_id IS NOT NULL
AND aid.match_status_flag = 'S');
SELECT 'Y'
INTO l_unpostable_holds_exist
FROM dual
WHERE EXISTS (SELECT 1
FROM ap_holds H, ap_hold_codes C
WHERE H.invoice_id = l_invoice_id
AND H.hold_lookup_code = C.hold_lookup_code
AND ((H.release_lookup_code IS NULL)
AND ((C.postable_flag = 'N') OR (C.postable_flag = 'X'))));
/* The condition above is same as the one used in Update_Inv_Dists_To_Approved
procedure. However, we removed encumbrance checks.*/
EXCEPTION
WHEN NO_DATA_FOUND THEN NULL;
l_debug_info := 'Update Invoice Distributions to APPROVED';
Update_Inv_Dists_To_Approved(
l_invoice_id,
l_user_id,
l_curr_calling_sequence);
SELECT DECODE(NVL((MAX(aps.last_update_date)- MIN(aps.creation_date)),0),
0,'N','Y')
INTO l_diff_flag
FROM ap_payment_schedules aps
WHERE aps.invoice_id = l_invoice_id;
l_debug_info := 'Update force_revalidation_flag to No';
UPDATE ap_invoices_all
SET force_revalidation_flag = 'N'
WHERE invoice_id = l_invoice_id;
SELECT *
INTO l_inv_header_rec
FROM ap_invoices_all
WHERE invoice_id = P_Invoice_Id;
SELECT distinct accounting_date acc_date
FROM ap_invoice_lines_all
WHERE invoice_id = p_invoice_id
and NVL(generate_dists,'N') <> 'D';
SELECT upper(nvl(source, 'X')), org_id
FROM ap_invoices_all
WHERE invoice_id = p_invoice_id;
SELECT count(*)
into p_invoice_line_count
from ap_invoice_lines
where invoice_id = p_invoice_id;
SELECT nvl(sp.set_of_books_id, -1),
nvl(recalc_pay_schedule_flag, 'N'),
nvl(sp.rate_var_gain_ccid, -1),
nvl(sp.rate_var_loss_ccid, -1),
nvl(sp.base_currency_code, 'USD'),
nvl(fp.inv_encumbrance_type_id, -1),
nvl(fp.purch_encumbrance_type_id, -1),
nvl(sp.receipt_acceptance_days, 0),
nvl(gl_date_from_receipt_flag, 'S')
INTO p_set_of_books_id,
p_recalc_pay_sched_flag,
p_sys_xrate_gain_ccid,
p_sys_xrate_loss_ccid,
p_base_currency_code,
p_inv_enc_type_id,
p_purch_enc_type_id,
p_receipt_acc_days,
p_gl_date_from_receipt_flag
FROM ap_system_parameters_all sp,
financials_system_params_all fp,
gl_sets_of_books gls
WHERE sp.org_id = p_org_id
AND fp.org_id = sp.org_id
AND sp.set_of_books_id = gls.set_of_books_id;
SELECT count(*)
INTO l_unapproved_dist_exists
FROM ap_invoice_distributions_all
WHERE invoice_id = p_invoice_id
AND (nvl(match_status_flag, 'N')) = 'N'
AND rownum = 1;
SELECT 'Y'
INTO l_undistributed_line_exists
FROM ap_invoice_lines_all L
WHERE L.invoice_id = p_invoice_id
AND L.amount <>
(SELECT NVL(SUM(NVL(aid.amount,0)),0)
FROM ap_invoice_distributions_all aid
WHERE aid.invoice_id = L.invoice_id
AND aid.invoice_line_number = L.line_number);
| PROCEDURE Update_Inv_Dists_To_Selected
|
| Procedure given the invoice_id, invoice line number and run option,
| updates the invoice distributions to be selected for approval depending
| on the run option.
| If the run_option is 'New' then we only select distributions that have
| never been processed by approval, otherwise we select all distributions
| that have not successfully been approved.
|
| PARAMETERS
| p_invoice_id - invoice id
| p_line_number - invoice line number
| p_run_option
| p_calling_sequence
|
| KNOWN ISSUES:
|
| NOTES:
|
| MODIFICATION HISTORY
| Date Author Description of Change
|
*============================================================================*/
PROCEDURE Update_Inv_Dists_To_Selected(
p_invoice_id IN NUMBER,
p_line_number IN NUMBER,
p_run_option IN VARCHAR2,
p_calling_sequence IN VARCHAR2) IS
l_debug_loc VARCHAR2(30) := 'Update_Inv_Dists_To_Selected';
UPDATE ap_invoice_distributions_all D
SET match_status_flag = 'S'
WHERE NVL(match_status_flag, 'N') = 'N'
AND D.invoice_id = p_invoice_id
AND D.invoice_line_number = p_line_number;
UPDATE ap_self_assessed_tax_dist_all D
SET match_status_flag = 'S'
WHERE NVL(match_status_flag, 'N') = 'N'
AND D.invoice_id = p_invoice_id
AND D.invoice_line_number = p_line_number;
UPDATE ap_invoice_distributions_all D
SET match_status_flag = 'S'
WHERE NVL(match_status_flag, '!') <> 'A'
AND D.invoice_id = p_invoice_id;
UPDATE ap_self_assessed_tax_dist_all D
SET match_status_flag = 'S'
WHERE NVL(match_status_flag, '!') <> 'A'
AND D.invoice_id = p_invoice_id;
END Update_Inv_Dists_To_Selected;
SELECT ALOC.rule_type
FROM ap_invoice_lines AIL,
ap_allocation_rules ALOC
WHERE AIL.invoice_id = p_inv_line_rec.invoice_id
AND AIL.line_number = p_inv_line_rec.line_number
AND AIL.invoice_id = ALOC.invoice_id
AND AIL.line_number = ALOC.chrg_invoice_line_number(+);
SELECT ALOC.rule_type
FROM ap_invoice_lines AIL,
ap_allocation_rules ALOC
WHERE AIL.invoice_id = p_inv_line_rec.invoice_id
AND AIL.line_number = p_inv_line_rec.line_number
AND AIL.invoice_id = ALOC.invoice_id
AND AIL.line_number = ALOC.chrg_invoice_line_number(+);
l_debug_info := 'Execute_Dist_Generation_Check - insert from dist set';
l_success := AP_INVOICE_LINES_PKG.Insert_From_Dist_Set(
X_invoice_id => p_inv_line_rec.invoice_id,
X_line_number => p_inv_line_rec.line_number,
X_GL_Date => p_inv_line_rec.accounting_date,
X_Period_Name => p_inv_line_rec.period_name,
X_Skeleton_Allowed => 'Y', -- Bug 4928285
X_Generate_Dists => p_inv_line_rec.generate_dists,
X_Generate_Permanent => p_generate_permanent,
X_Error_Code => l_error_code,
X_Debug_Info => l_debug_info,
X_Debug_Context => l_debug_context,
X_Msg_Application => l_msg_application,
X_Msg_Data => l_msg_data,
X_calling_sequence => l_curr_calling_sequence);
l_success := AP_INVOICE_DISTRIBUTIONS_PKG.Insert_Charge_From_Alloc(
X_invoice_id => p_inv_line_rec.invoice_id,
X_line_number => p_inv_line_rec.line_number,
X_Generate_Permanent => p_generate_permanent,
X_Validate_Info => TRUE,
X_Error_Code => l_error_code,
X_Debug_Info => l_debug_info,
X_Debug_Context => l_debug_context,
X_Msg_Application => l_msg_application,
X_Msg_Data => l_msg_data,
X_Calling_Sequence => l_curr_calling_sequence );
'Insert_Charge_From_Alloc error ' || l_error_code;
l_debug_info := 'Execute_Dist_Generation_Check - Insert_Single_Dist_From_Line';
l_success := AP_INVOICE_DISTRIBUTIONS_PKG.Insert_Single_Dist_From_Line(
X_batch_id => p_batch_id,
X_invoice_id => p_inv_line_rec.invoice_id,
X_invoice_date => p_invoice_date,
X_vendor_id => p_vendor_id,
X_invoice_currency => p_invoice_currency,
X_exchange_rate => p_exchange_rate,
X_exchange_rate_type => p_exchange_rate_type,
X_exchange_date => p_exchange_date,
X_line_number => p_inv_line_rec.line_number,
X_invoice_lines_rec => NULL,
X_line_source => 'VALIDATION',
X_Generate_Permanent => p_generate_permanent,
X_Validate_Info => TRUE,
X_Error_Code => l_error_code,
X_Debug_Info => l_debug_info,
X_Debug_Context => l_debug_context,
X_Msg_Application => l_msg_application,
X_Msg_Data => l_msg_data,
X_Calling_Sequence => l_curr_calling_sequence);
l_debug_info := 'Execute_Dist_Generation_Check-insert from dist'
|| ' set has error - ' || l_error_code ;
SELECT hold_lookup_code
INTO l_hold_code
FROM ap_holds_all
WHERE invoice_id = p_inv_line_rec.invoice_id
AND hold_lookup_code in ('DISTRIBUTION SET INACTIVE','SKELETON DISTRIBUTION SET',
'CANNOT OVERLAY ACCOUNT','INVALID DEFAULT ACCOUNT')
AND release_lookup_code IS NULL;
SELECT hold_lookup_code
INTO l_hold_code
FROM ap_holds_all
WHERE invoice_id = p_inv_line_rec.invoice_id
AND hold_lookup_code = 'CANNOT EXECUTE ALLOCATION'
AND release_lookup_code IS NULL;
SELECT hold_lookup_code
INTO l_hold_code
FROM ap_holds_all
WHERE invoice_id = p_inv_line_rec.invoice_id
AND hold_lookup_code in ('CANNOT OVERLAY ACCOUNT','INVALID DEFAULT ACCOUNT',
'PERIOD CLOSED','PROJECT GL DATE CLOSED')
AND release_lookup_code IS NULL;
SELECT D.dist_code_combination_id, D.accounting_date
FROM ap_invoice_distributions D
WHERE D.invoice_id = p_invoice_id
AND D.posted_flag||'' in ('N', 'P')
AND ((EXISTS (select 'x'
from gl_code_combinations C
where D.dist_code_combination_id = C.code_combination_id (+)
and (C.code_combination_id is null
or C.detail_posting_allowed_flag = 'N'
or C.start_date_active > D.accounting_date
or C.end_date_active < D.accounting_date
or C.template_id is not null
or C.enabled_flag <> 'Y'
or C.summary_flag <> 'N'
)))
OR (D.dist_code_combination_id = -1))
AND ROWNUM = 1;
SELECT 'Y'
FROM gl_code_combinations glcc
WHERE glcc.code_combination_id = c_ccid
AND glcc.alternate_code_combination_id IS NOT NULL
AND EXISTS
(
SELECT 'Account Valid'
FROM gl_code_combinations a
WHERE a.code_combination_id = glcc.alternate_code_combination_id
AND a.enabled_flag = 'Y'
AND a.detail_posting_allowed_flag = 'Y'
AND c_acct_date BETWEEN NVL(a.start_date_active, c_acct_date)
AND NVL(a.end_date_active, c_acct_date)
);
SELECT 'PO REQUIRED'
FROM ap_invoices_all api, ap_supplier_sites pov
WHERE EXISTS (select /*+ index(apd AP_INVOICE_DISTRIBUTIONS_U1) */ 'X'
from ap_invoice_distributions_all apd
where apd.invoice_id = api.invoice_id
and apd.line_type_lookup_code in ( 'ITEM', 'ACCRUAL')
and apd.po_distribution_id is null
and apd.pa_addition_flag <> 'T'
group by apd.dist_code_combination_id
HAVING sum(apd.amount) <> 0)
AND nvl(pov.hold_unmatched_invoices_flag, 'X') = 'Y'
AND api.invoice_type_lookup_code not in ('PREPAYMENT', 'INTEREST')
AND api.vendor_site_id = pov.vendor_site_id
AND api.invoice_id = p_invoice_id;
SELECT 'Foreign Invoice without exchange rate'
FROM ap_invoices I
WHERE I.invoice_id = p_invoice_id
AND I.invoice_currency_code <> p_base_currency_code
AND I.exchange_rate is null;
SELECT 'Distribution needs to be verified. '
FROM DUAL
WHERE EXISTS (
SELECT 'Dist Total <> Invoice Line Amount'
FROM ap_invoice_lines_all AIL, ap_invoice_distributions_all D
-- WHERE AIL.invoice_id = D.invoice_id
WHERE AIL.invoice_id = D.invoice_id(+)
AND AIL.line_number = nvl(p_invoice_line_number, AIL.line_number) --bug6661773
AND AIL.invoice_id = p_invoice_id
-- AND AIL.line_number = D.invoice_line_number
AND AIL.line_number = D.invoice_line_number(+)
-- AND (D.line_type_lookup_code <> 'RETAINAGE'
AND (NVL(D.line_type_lookup_code, 'ITEM') <> 'RETAINAGE'
OR (AIL.line_type_lookup_code = 'RETAINAGE RELEASE'
and D.line_type_lookup_code = 'RETAINAGE'))
AND (AIL.line_type_lookup_code
NOT IN ('ITEM', 'RETAINAGE RELEASE')
or (AIL.line_type_lookup_code
IN ('ITEM', 'RETAINAGE RELEASE')
and (D.prepay_distribution_id IS NULL
or (D.prepay_distribution_id IS NOT NULL
and D.line_type_lookup_code NOT IN ('PREPAY', 'REC_TAX', 'NONREC_TAX')))))
/*
AND (ail.line_type_lookup_code <> 'ITEM'
OR (d.line_type_lookup_code <> 'PREPAY'
and d.prepay_tax_parent_id IS NULL)
)
*/
GROUP BY AIL.invoice_id, AIL.line_number, AIL.amount
HAVING AIL.amount <> nvl(SUM(nvl(D.amount,0)),0));
SELECT invoice_amount
INTO l_inv_amount
FROM ap_invoices_all ai
WHERE ai.invoice_id = p_invoice_id;
SELECT count(*) INTO l_dist_count
FROM ap_invoice_distributions_all aid
WHERE aid.invoice_id = p_invoice_id
AND ((aid.line_type_lookup_code <> 'PREPAY'
AND aid.prepay_tax_parent_id IS NULL)
OR nvl(invoice_includes_prepay_flag,'N') = 'Y')
AND rownum =1; --Perf 6759699
SELECT 'Line Total <> Invoice Amount'
FROM ap_invoice_lines_all AIL, ap_invoices_all A
WHERE AIL.invoice_id = A.invoice_id
AND AIL.invoice_id = p_invoice_id
AND ((AIL.line_type_lookup_code <> 'TAX'
and (AIL.line_type_lookup_code NOT IN ('AWT','PREPAY')
or NVL(AIL.invoice_includes_prepay_flag,'N') = 'Y') OR
(AIL.line_type_lookup_code = 'TAX'
/* bug 5222316 */
and (AIL.prepay_invoice_id IS NULL
or (AIL.prepay_invoice_id is not null
and NVL(AIL.invoice_includes_prepay_flag, 'N') = 'Y')))))
-- and AIL.prepay_invoice_id IS NULL)))
GROUP BY A.invoice_id, A.invoice_amount, A.net_of_retainage_flag
HAVING A.invoice_amount <>
nvl(SUM(nvl(AIL.amount,0) + decode(A.net_of_retainage_flag,
'Y', nvl(AIL.retained_amount,0),0)),0);
SELECT invoice_amount,org_id,set_of_books_id
INTO l_inv_amount,l_org_id,l_set_of_books_id
FROM ap_invoices ai
WHERE ai.invoice_id = p_invoice_id;
SELECT count(*)
INTO l_line_count
FROM ap_invoice_lines ail
WHERE ail.invoice_id = p_invoice_id
AND (ail.line_type_lookup_code NOT IN ('PREPAY','AWT')
OR nvl(invoice_includes_prepay_flag,'N') = 'Y');
l_debug_info := 'Update Invoice Lines Base Amount';
UPDATE AP_INVOICE_LINES AIL
SET AIL.base_amount = DECODE(p_base_currency_code, p_invoice_currency_code,
NULL,
ap_utilities_pkg.ap_round_currency(
amount * p_exchange_rate,
p_base_currency_code)),
AIL.last_update_date = SYSDATE,
AIL.last_updated_by = FND_GLOBAL.user_id,
AIL.last_update_login = FND_GLOBAL.login_id
WHERE AIL.invoice_id = p_invoice_id
-- Bug 6621883
AND (EXISTS ( SELECT 'NOT POSTED'
FROM ap_invoice_distributions_all D
WHERE D.invoice_id = AIL.invoice_id
AND D.invoice_line_number = AIL.line_number
AND NVL(D.posted_flag, 'N') = 'N' )
OR NOT EXISTS (SELECT 'DIST DOES NOT EXIST'
FROM ap_invoice_distributions_all D1
WHERE D1.invoice_id = AIL.invoice_id
AND D1.invoice_line_number = AIL.line_number
AND AIL.amount IS NOT NULL
)
)
--Retropricing: Adjustment Correction lines on the PPA should be
-- excluded. Base amounts on zero amount adjustment lines adjustment
-- correction lines on the PPA is handled while creating PPA Docs.
--Bugfix:4625349, modified the AND clause
AND
( line_type_lookup_code <> 'RETROITEM' OR
(line_type_lookup_code = 'RETROITEM' and
match_type <> 'ADJUSTMENT_CORRECTION')
);
select base_amount
INTO l_base_amt
FROM AP_INVOICE_LINES
WHERE invoice_id = p_invoice_id
AND line_number = l_round_inv_line_numbers(i);
UPDATE AP_INVOICE_LINES
SET base_amount = l_base_amt,
rounding_amt = ABS( NVL(l_modified_line_rounding_amt, 0) ),
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.login_id
WHERE invoice_id = p_invoice_id
AND line_number = l_round_inv_line_numbers(i);
l_debug_info := 'Update Distribution Base Amounts';
UPDATE AP_INVOICE_DISTRIBUTIONS
SET base_amount = DECODE(p_base_currency_code, p_invoice_currency_code,
NULL, ap_utilities_pkg.ap_round_currency(
amount * p_invoice_exchange_rate,
p_base_currency_code)),
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.login_id
WHERE invoice_id = p_invoice_id
AND invoice_line_number = p_invoice_line_number
AND NVL(posted_flag,'N') = 'N'
--Bugfix:4625771
AND related_id IS NULL;
select base_amount
INTO l_base_amt
FROM AP_INVOICE_DISTRIBUTIONS
WHERE invoice_id = p_invoice_id
AND invoice_line_number = p_invoice_line_number
AND invoice_distribution_id = l_round_dist_id_list(i);
UPDATE AP_INVOICE_DISTRIBUTIONS
SET base_amount = l_base_amt,
rounding_amt = ABS( l_modified_dist_rounding_amt ),
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.login_id
WHERE invoice_distribution_id = l_round_dist_id_list(i);
SELECT count(*)
INTO l_null_event_id
FROM ap_invoice_distributions aid
WHERE aid.invoice_id = P_invoice_id
AND aid.accounting_event_id is NULL;
AP_ACCOUNTING_EVENTS_PKG.Update_Invoice_Events_Status(
p_invoice_id => p_invoice_id,
p_calling_sequence => l_curr_calling_sequence);
SELECT AI.invoice_amount, (0 - sum(nvl(AIL.amount,0)))
FROM ap_invoices_all AI, ap_invoice_lines_all AIL
WHERE AI.invoice_id = p_invoice_id
AND AIL.invoice_id = AI.invoice_id
AND AIL.invoice_includes_prepay_flag = 'N'
AND AIL.line_type_lookup_code IN ('PREPAY', 'TAX')
AND AIL.prepay_invoice_id IS NOT NULL
AND AIL.prepay_line_number IS NOT NULL
GROUP BY AI.invoice_id, AI.invoice_amount
Having sum(nvl(AIL.amount,0)) <>0; --Bug5724818
SELECT ap_utilities_pkg.get_auto_offsets_segments(
aid.dist_code_combination_id)
FROM ap_invoice_distributions aid
WHERE aid.invoice_id = p_invoice_id
AND aid.line_type_lookup_code = 'AWT'
AND aid.awt_flag = 'M';
SELECT ap_utilities_pkg.get_auto_offsets_segments(
aid.dist_code_combination_id)
FROM ap_invoice_distributions_all aid, ap_invoice_lines_all ail
WHERE ail.invoice_id = p_invoice_id
AND ail.invoice_id = aid.invoice_id
AND ail.line_number = aid.invoice_line_number
AND ((aid.line_type_lookup_code not in ('AWT','PREPAY')
AND aid.prepay_distribution_id IS NULL)
OR NVL(ail.invoice_includes_prepay_flag,'N') = 'Y');
SELECT count(*)
INTO l_matched_count
FROM ap_invoice_distributions
WHERE invoice_id = p_invoice_id
AND po_distribution_id is not null
AND line_type_lookup_code in ( 'ITEM', 'ACCRUAL', 'IPV');
| p_last_updated_by : Column Who Info
| p_last_update_login : Column Who Info
| p_program_application_id : Column Who Info
| p_program_id : Column Who Info
| p_request_id : Column Who Info
| p_system_user : Approval Program User Id
| p_holds : Hold Array
| p_holds_count : Holds Count Array
| p_release_count : Release Count Array
| p_calling_sequence : Debugging string to indicate path of module
| calls to be printed out upon error.
|
| Program Flow:
| -------------
|
| Check if okay to call Withholding Routine
| invoice has at lease on distribution with a withholding tax group
| invoice has not already been withheld by the system
| invoice has no user non-releaseable holds (ther than AWT ERROR)
| invoice has no manual withholding lines
| IF okay then call AP_DO_WITHHOLDING package on the invoice
| Depending on whether withholding is successful or not, place or
| or release the 'AWT ERROR' with the new error reason.
| (If the invoice already has the hold we want to release the old one and
| replace the hold with the new error reason)
|============================================================================ */
PROCEDURE Withhold_Tax_On(
p_invoice_id IN NUMBER,
p_gl_date_from_receipt IN VARCHAR2,
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_system_user IN NUMBER,
p_holds IN OUT NOCOPY HOLDSARRAY,
p_holds_count IN OUT NOCOPY COUNTARRAY,
p_release_count IN OUT NOCOPY COUNTARRAY,
p_calling_sequence IN VARCHAR2)
IS
l_ok_to_withhold VARCHAR2(30);
SELECT 'OK to call Withholding Routine',
(AI.invoice_amount * NVL(AI.exchange_rate, 1)),
AI.invoice_num
INTO l_ok_to_withhold,
l_withholding_amount,
l_invoice_num
FROM ap_invoices_all AI
WHERE AI.invoice_id = p_invoice_id
AND EXISTS (SELECT 'At least 1 dist has an AWT Group'
FROM ap_invoice_distributions_all AID1
WHERE AID1.invoice_id = AI.invoice_id
AND AID1.awt_group_id IS NOT NULL)
AND NOT EXISTS (SELECT 'Unreleased System holds exist'
FROM ap_holds AH,
ap_hold_codes AHC
WHERE AH.invoice_id = AI.invoice_id
AND AH.release_lookup_code IS NULL
AND AH.hold_lookup_code <> 'AWT ERROR'
AND AH.hold_lookup_code = AHC.hold_lookup_code
AND AHC.user_releaseable_flag = 'N')
AND NOT EXISTS (SELECT 'Manual AWT lines exist'
FROM ap_invoice_distributions_all AID
WHERE AID.invoice_id = AI.invoice_id
AND AID.line_type_lookup_code = 'AWT'
AND AID.awt_flag IN ('M', 'O'));
/* SELECT MAX(accounting_date)
INTO l_withholding_date
FROM ap_invoice_distributions
WHERE invoice_id = p_invoice_id
AND awt_group_id IS NOT NULL; */
SELECT invoice_date
INTO l_withholding_date
FROM ap_invoices
WHERE invoice_id = p_invoice_id;
p_last_updated_by,
p_last_update_login,
p_program_application_id,
p_program_id,
p_request_id,
l_return_string);
SELECT 'OK to call Withholding Routine',
(AI.invoice_amount * NVL(AI.exchange_rate,1)),
AI.invoice_num
INTO l_ok_to_withhold,
l_withholding_amount,
l_invoice_num
FROM ap_invoices_all AI
WHERE AI.invoice_id = p_invoice_id
AND NOT EXISTS (SELECT 'Unreleased System holds exist'
FROM ap_holds AH,
ap_hold_codes AHC
WHERE AH.invoice_id = AI.invoice_id
AND AH.release_lookup_code IS NULL
AND AH.hold_lookup_code <> 'AWT ERROR'
AND AH.hold_lookup_code = AHC.hold_lookup_code
AND AHC.user_releaseable_flag = 'N')
AND NOT EXISTS (SELECT 'Manual AWT lines exist'
FROM ap_invoice_distributions_all AID
WHERE AID.invoice_id = AI.invoice_id
AND AID.line_type_lookup_code = 'AWT'
AND AID.awt_flag IN ('M', 'O'));
SELECT MAX(accounting_date)
INTO l_withholding_date
FROM ap_invoice_distributions
WHERE invoice_id = p_invoice_id;
p_last_updated_by,
p_last_update_login,
p_program_application_id,
p_program_id,
p_request_id,
l_return_string);
||', Packet_id = '|| p_last_updated_by
||', Fundscheck mode = '|| p_last_update_login
||', Dist_line_num = '|| to_char(p_program_application_id)
||', Dist_line_num = '|| to_char(p_program_id)
||', Dist_line_num = '|| to_char(p_request_id));
| PROCEDURE UPDATE_INV_DISTS_TO_APPROVED
| Procedure that updates the invoice distribution match_status_flag to
| 'A' if encumbered or has no postable holds or is a reversal line,
| otherwise if the invoice has postable holds then the match_status_flag
| remains a 'T'.
|
| PARAMETERS
| p_invoice_id
| p_user_id
| p_calling_sequence
|
| KNOWN ISSUES:
|
| NOTES:
|
| MODIFICATION HISTORY
| Date Author Description of Change
|
*============================================================================*/
PROCEDURE Update_Inv_Dists_To_Approved(
p_invoice_id IN NUMBER,
p_user_id IN NUMBER,
p_calling_sequence IN VARCHAR2) IS
l_debug_loc VARCHAR2(30) := 'Update_Inv_Dists_To_Approved';
l_debug_info := 'Set selected dists match_status_flag to tested';
UPDATE ap_invoice_distributions D
SET match_status_flag = 'T',
last_update_date = SYSDATE,
last_updated_by = p_user_id,
program_application_id = decode(fnd_global.prog_appl_id,
-1,null,
fnd_global.prog_appl_id),
request_id = decode(fnd_global.conc_request_id,
-1,null, fnd_global.conc_request_id),
program_id = decode(fnd_global.conc_program_id,
-1,null, fnd_global.conc_program_id),
program_update_date = decode(fnd_global.conc_program_id,
-1,null, SYSDATE)
WHERE match_status_flag = 'S'
AND D.invoice_id = p_invoice_id;
UPDATE ap_self_assessed_tax_dist_all D
SET match_status_flag = 'T',
last_update_date = SYSDATE,
last_updated_by = p_user_id,
program_application_id = decode(fnd_global.prog_appl_id,
-1,null,
fnd_global.prog_appl_id),
request_id = decode(fnd_global.conc_request_id,
-1,null, fnd_global.conc_request_id),
program_id = decode(fnd_global.conc_program_id,
-1,null, fnd_global.conc_program_id),
program_update_date = decode(fnd_global.conc_program_id,
-1,null, SYSDATE)
WHERE match_status_flag = 'S'
AND D.invoice_id = p_invoice_id;
UPDATE ap_invoice_distributions D
SET match_status_flag = 'A',
packet_id = ''
WHERE match_status_flag = 'T'
AND D.invoice_id = p_invoice_id
AND ((NOT EXISTS
(SELECT invoice_id
FROM ap_holds H, ap_hold_codes C
WHERE H.invoice_id = D.invoice_id
AND H.hold_lookup_code = C.hold_lookup_code
AND ((H.release_lookup_code IS NULL) AND
((C.postable_flag = 'N') OR
(C.postable_flag = 'X')))))
OR (D.line_type_lookup_code<>'AWT' and
(nvl(D.encumbered_flag, 'N') in ('Y','W','D','X','R','T')))
OR (D.line_type_lookup_code='AWT' and
(nvl(D.encumbered_flag, 'N') in ('Y','W','D','X','R')))); -- BUG 4340061
UPDATE ap_self_assessed_tax_dist_all D
SET match_status_flag = 'A',
packet_id = ''
WHERE match_status_flag = 'T'
AND D.invoice_id = p_invoice_id
AND ((NOT EXISTS
(SELECT invoice_id
FROM ap_holds H, ap_hold_codes C
WHERE H.invoice_id = D.invoice_id
AND H.hold_lookup_code = C.hold_lookup_code
AND ((H.release_lookup_code IS NULL) AND
((C.postable_flag = 'N') OR
(C.postable_flag = 'X')))))
OR (D.line_type_lookup_code<>'AWT' and
(nvl(D.encumbered_flag, 'N') in ('Y','W','D','X','R','T')))
OR (D.line_type_lookup_code='AWT' and
(nvl(D.encumbered_flag, 'N') in ('Y','W','D','X','R')))); -- BUG 4340061
END Update_Inv_Dists_To_Approved;
UPDATE ap_holds
SET responsibility_id = NULL
WHERE invoice_id = p_invoice_id
AND hold_lookup_code = 'INSUFFICIENT FUNDS';
and g_holds_tab(i).last_updated_by <> p_system_user)) then
p_status := g_holds_tab(i).hold_status;
p_user_id := g_holds_tab(i).last_updated_by;
and g_holds_tab(i).last_updated_by <> p_system_user
and g_holds_tab(i).responsibility_id is not null)) then
p_status := g_holds_tab(i).hold_status;
p_user_id := g_holds_tab(i).last_updated_by;
| Procedure to release a hold from an invoice and update the release
| count array.
|
| PARAMETERS
| p_invoice_id
| p_line_location_id: Line Location Id
| p_rcv_transaction_id
| p_hold_lookup_code
| p_holds
| p_release_count
| p_calling_sequence: Debugging string to indicate path of module calls
| to be printed out upon error.
|
| KNOWN ISSUES:
|
| NOTES:
|
| MODIFICATION HISTORY
| Date Author Description of Change
|
*============================================================================*/
PROCEDURE Release_Hold(
p_invoice_id IN NUMBER,
p_line_location_id IN NUMBER,
p_rcv_transaction_id IN NUMBER,
p_hold_lookup_code IN VARCHAR2,
p_holds IN OUT NOCOPY HOLDSARRAY,
p_release_count IN OUT NOCOPY COUNTARRAY,
p_calling_sequence IN VARCHAR2) IS
l_release_lookup_code VARCHAR2(30);
UPDATE ap_holds
SET release_lookup_code = l_release_lookup_code,
release_reason = (SELECT description
FROM ap_lookup_codes
WHERE lookup_code = l_release_lookup_code
AND lookup_type = 'HOLD CODE'),
last_update_date = sysdate,
last_updated_by = 5,
status_flag = 'R'
WHERE invoice_id = p_invoice_id
AND nvl(line_location_id, -1) = nvl(p_line_location_id, -1)
AND nvl(rcv_transaction_id, -1) = nvl(rcv_transaction_id, -1)
AND hold_lookup_code = p_hold_lookup_code
AND nvl(status_flag, 'x') <> 'x';
| Procedure to Set an Invoice on Hold and update the hold count array.
|
| PARAMETERS
| p_invoice_id
| p_line_location_id: Line Location Id
| p_rcv_transaction_id
| p_hold_lookup_code
| p_hold_reason
| p_holds
| p_hold_count
| p_calling_sequence: Debugging string to indicate path of module calls
| to be printed out upon error.
|
| KNOWN ISSUES:
|
| NOTES:
|
| MODIFICATION HISTORY
| Date Author Description of Change
|
*============================================================================*/
PROCEDURE Set_Hold(
p_invoice_id IN NUMBER,
p_line_location_id IN NUMBER,
p_rcv_transaction_id IN NUMBER,
p_hold_lookup_code IN VARCHAR2,
p_hold_reason IN VARCHAR2,
p_holds IN OUT NOCOPY HOLDSARRAY,
p_holds_count IN OUT NOCOPY COUNTARRAY,
p_calling_sequence IN VARCHAR2) IS
l_debug_loc VARCHAR2(30) := 'Set_Hold';
l_debug_info := 'Inserting Into AP_HOLDS';
SELECT ap_holds_s.nextval
INTO l_hold_id
FROM DUAL;
INSERT INTO ap_holds (
invoice_id,
line_location_id,
rcv_transaction_id,
hold_lookup_code,
last_update_date,
last_updated_by,
creation_date,
created_by,
held_by,
hold_date,
hold_reason,
status_flag,
org_id, /* Bug 3700128. MOAC Project */
hold_id) -- added for Negotiation Project
(SELECT p_invoice_id,
p_line_location_id,
p_rcv_transaction_id,
p_hold_lookup_code,
sysdate,
5,
sysdate,
5,
5,
sysdate,
substrb(nvl(p_hold_reason, description),1,240),
'S',
g_org_id, /* Bug 3700128. MOAC Project */
l_hold_id -- Added for Negotiation.
FROM ap_lookup_codes
WHERE lookup_code = p_hold_lookup_code
AND lookup_type = 'HOLD CODE');
l_debug_info := 'Select to see if the hold is user releaseable hold';
SELECT user_releaseable_flag,
initiate_workflow_flag
INTO l_user_releaseable_flag,
l_initiate_workflow_flag
FROM ap_hold_codes
WHERE hold_lookup_code = p_hold_lookup_code;
p_release_lookup_code := 'VENDOR UPDATED';
SELECT AP_INVOICES_PKG.Get_Holds_Count(invoice_id),
AP_INVOICES_PKG.Get_Approval_Status(
invoice_id,
invoice_amount,
payment_status_flag,
invoice_type_lookup_code)
FROM ap_invoices
WHERE invoice_id = p_invoice_id;
PROCEDURE Update_Total_Dist_Amount(
p_invoice_id IN NUMBER,
p_calling_sequence IN VARCHAR2) IS
l_debug_info VARCHAR2(1000);
l_debug_loc VARCHAR2(30) := 'Update_Total_Dist_Amount';
l_debug_info := 'Update Total Dist Amount';
UPDATE ap_invoice_distributions_all id1
SET (id1.total_dist_amount,
id1.total_dist_base_amount) = (SELECT SUM(NVL(id2.amount,0)),
SUM(NVL(id2.base_amount,0))
FROM ap_invoice_distributions_all id2
WHERE id2.invoice_distribution_id =
id1.invoice_distribution_id
OR id2.related_id =
id1.invoice_distribution_id)
WHERE id1.invoice_id = p_invoice_id
AND id1.line_type_lookup_code NOT IN ('IPV','ERV','TIPV','TRV','TERV');
END Update_Total_Dist_Amount;
l_last_updated_by NUMBER;
SELECT aid.invoice_id invoice_id,
aid.amount*-1 amount,
l_pay_curr_code pay_currency,
aid.last_updated_by user_id,
aid.last_update_login last_update_login,
l_curr_calling_sequence calling_sequence
FROM ap_invoice_distributions aid,
ap_invoice_lines ail
WHERE aid.invoice_id = p_invoice_id
AND ail.invoice_id = aid.invoice_id
AND ail.line_number = aid.invoice_line_number
--bugfix:5609186
AND ail.line_type_lookup_code = 'PREPAY'
AND aid.amount <> 0
AND (aid.line_type_lookup_code = 'PREPAY'
OR aid.charge_applicable_to_dist_id in
(SELECT invoice_distribution_id
FROM ap_invoice_distributions
WHERE line_type_lookup_code = 'PREPAY'
AND invoice_id = p_invoice_id))
AND NVL(aid.invoice_includes_prepay_flag, 'N') <> 'Y';
SELECT 'Y'
INTO l_payment_status
FROM ap_invoice_payments
WHERE invoice_id = p_invoice_id
AND nvl(reversal_flag,'N') <> 'Y'
AND rownum<2;
SELECT 'Y'
INTO l_prepayment_app_exists
FROM ap_invoice_lines
WHERE invoice_id = p_invoice_id
AND line_type_lookup_code = 'PREPAY'
AND rownum < 2;
SELECT amount_applicable_to_discount,
decode(p_exclude_tax_from_discount,
'Y',nvl(total_tax_amount,0),0),
nvl(validated_tax_amount,0),
terms_id,
last_updated_by,
created_by,
batch_id,
terms_date,
invoice_amount,
nvl(pay_curr_invoice_amount, invoice_amount),
payment_cross_rate,
payment_method_code, --4552701
invoice_currency_code,
payment_currency_code,
invoice_date
INTO l_discountable_amount,
l_total_tax_amount,
l_validated_amount,
l_terms_id,
l_last_updated_by,
l_created_by,
l_batch_id,
l_terms_date,
l_invoice_amount,
l_pay_curr_invoice_amount,
l_payment_cross_rate,
l_payment_method,
l_invoice_curr_code,
l_pay_curr_code,
l_invoice_date
FROM ap_invoices
WHERE invoice_id = p_invoice_id;
SELECT NVL(SUM(nvl(ail.amount,0)),0)
INTO l_total_freight_amount
FROM ap_invoice_lines ail
WHERE ail.invoice_id = p_invoice_id
AND ail.line_type_lookup_code = 'FREIGHT'
AND nvl(ail.discarded_flag,'N') <> 'Y';
l_last_updated_by,
l_created_by,
null,
l_batch_id,
l_terms_date,
l_invoice_amount,
l_pay_curr_invoice_amount,
l_payment_cross_rate,
l_new_discountable_amount,
l_payment_method,
l_invoice_curr_code,
l_pay_curr_code,
l_curr_calling_sequence);
l_debug_info := 'Update Pay Schedules if Prepayment APP Exists';
SELECT sum(aid.amount)
INTO l_prepay_excl_tax_amt
FROM ap_invoice_lines_all ail,ap_invoice_distributions_all aid
WHERE ail.line_type_lookup_code='TAX'
AND ail.invoice_id=p_invoice_id
AND aid.invoice_id=ail.invoice_id
AND aid.invoice_line_number=ail.line_number
AND ail.prepay_line_number is not null;
Update_Payment_Schedule_Prepay(
p_invoice_id,
l_total_amount,
l_total_amount_positive,
v_prepay_dist_rec(1).pay_currency,
v_prepay_dist_rec(1).user_id,
v_prepay_dist_rec(1).last_update_login,
v_prepay_dist_rec(1).calling_sequence);
Update_Pay_Sched_For_Awt( p_invoice_id,
5,
5,
l_curr_calling_sequence);
l_debug_info := 'Update Invoice Header';
UPDATE ap_invoices
SET amount_applicable_to_discount = l_new_discountable_amount,
validated_tax_amount = l_total_tax_amount+l_total_freight_amount
WHERE invoice_id = p_invoice_id;
SELECT sum(nvl(amount,0)),
sum(nvl(base_amount,0))
INTO l_tmp_invoice_amount,
l_tmp_base_amount
FROM ap_invoice_distributions_all
WHERE invoice_id = p_invoice_id
AND ((line_type_lookup_code NOT IN ('PREPAY', 'AWT')
AND prepay_distribution_id IS NULL)
OR nvl(invoice_includes_prepay_flag,'N') = 'Y');
UPDATE ap_invoices
SET invoice_amount = l_tmp_invoice_amount,
amount_applicable_to_discount = l_tmp_invoice_amount,
base_amount = l_tmp_base_amount,
pay_curr_invoice_amount = decode(invoice_currency_code,
nvl(payment_currency_code,invoice_currency_code), l_tmp_invoice_amount,
gl_currency_api.convert_amount
(invoice_currency_code,
nvl(payment_currency_code,invoice_currency_code),
payment_cross_rate_date,
payment_cross_rate_type,
l_tmp_invoice_amount))
WHERE invoice_id = p_invoice_id;
SELECT amount_applicable_to_discount,
nvl(total_tax_amount,0),
nvl(validated_tax_amount,0),
terms_id,
last_updated_by,
created_by,
batch_id,
terms_date,
invoice_amount,
pay_curr_invoice_amount,
payment_cross_rate,
payment_method_code, --4552701
invoice_currency_code,
payment_currency_code
INTO l_discountable_amount,
l_total_tax_amount,
l_validated_amount,
l_terms_id,
l_last_updated_by,
l_created_by,
l_batch_id,
l_terms_date,
l_invoice_amount,
l_pay_curr_invoice_amount,
l_payment_cross_rate,
l_payment_method,
l_invoice_curr_code,
l_pay_curr_code
FROM ap_invoices
WHERE invoice_id = p_invoice_id;
l_last_updated_by,
l_created_by,
null,
l_batch_id,
l_terms_date,
l_invoice_amount,
l_pay_curr_invoice_amount,
l_payment_cross_rate,
l_discountable_amount,
l_payment_method,
l_invoice_curr_code,
l_pay_curr_code,
l_curr_calling_sequence);
PROCEDURE update_payment_schedule_prepay(
p_invoice_id IN NUMBER,
p_apply_amount IN NUMBER,
p_amount_positive IN VARCHAR2,
p_payment_currency_code IN VARCHAR2,
p_user_id IN NUMBER,
p_last_update_login IN NUMBER,
p_calling_sequence IN VARCHAR2) AS
l_debug_info VARCHAR2(1000);
SELECT payment_num,
DECODE(p_amount_positive,
'N', gross_amount - amount_remaining,
amount_remaining)
--
-- If unapplying prepayment, we want to get the amount paid, else
-- we want to get amount remaining so we won't overapply.
--
FROM ap_payment_schedules
WHERE invoice_id = p_invoice_id
AND (payment_status_flag||'' = 'P'
OR payment_status_flag||'' = DECODE(p_amount_positive, 'N', 'Y', 'N'))
ORDER BY DECODE(p_amount_positive,
'N', DECODE(payment_status_flag,'P',1,'Y',2,3),
DECODE(NVL(hold_flag,'N'),'N',1,2)),
DECODE(p_amount_positive,
'N', due_date,
NULL) DESC,
DECODE(p_amount_positive,
'Y', due_date,
NULL),
DECODE(p_amount_positive,
'N', DECODE(hold_flag,'N',1,'Y',2,3),
DECODE(NVL(payment_status_flag,'N'),'P',1,'N',2,3));
l_current_calling_sequence := 'update_payment_schedule_prepay<-'||
p_calling_sequence;
* Update the amount remaining for this payment schedule line so that: *
* (amount remaining - apply amount remaining). *
+-----------------------------------------------------------------------*/
l_debug_info := 'Update ap_payment_schedule for the invoice, case 1';
UPDATE ap_payment_schedules
SET amount_remaining = (amount_remaining -
ap_utilities_pkg.ap_round_currency(
l_apply_amount_remaining,
p_payment_currency_code)),
payment_status_flag =
DECODE(amount_remaining -
ap_utilities_pkg.ap_round_currency(
l_apply_amount_remaining,
p_payment_currency_code),
0,'Y',
gross_amount, 'N',
'P'),
last_update_date = SYSDATE,
last_updated_by = p_user_id,
last_update_login = p_last_update_login
WHERE invoice_id = p_invoice_id
AND payment_num = l_cursor_payment_num;
* Update the amount_remaining to 0 and amount_apply_remaining become *
* (amount_apply - amount_remaining(this line)), then go to next *
* schedule line. *
*----------------------------------------------------------------------*/
l_debug_info := 'Update ap_payment_schedule for the invoice, case 2';
UPDATE ap_payment_schedules
SET amount_remaining = DECODE(p_amount_positive,
'Y', 0,
gross_amount),
payment_status_flag = DECODE(p_amount_positive,
'Y', 'Y',
'N'),
last_update_date = SYSDATE,
last_updated_by = p_user_id,
last_update_login = p_last_update_login
WHERE invoice_id = p_invoice_id
AND payment_num = l_cursor_payment_num;
||' Last_update_login = '||TO_CHAR(p_last_update_login)
||' Payment_Currency_code = '||p_payment_currency_code);
END update_payment_schedule_prepay;
p_last_updated_by IN NUMBER,
p_last_update_login IN NUMBER,
p_calling_sequence IN VARCHAR2) IS
l_manual_awt_amount ap_invoice_distributions.amount%TYPE :=0;
SELECT sum( nvl(amount, 0) )
INTO l_manual_awt_amount
FROM ap_invoice_distributions
WHERE invoice_id = p_invoice_id
AND nvl(match_status_flag, 'N') in ('N','T') -- BUG 4340061
AND line_type_lookup_code = 'AWT'
AND awt_flag in ('M', 'O');
SELECT sum(nvl(amount_remaining,0)), sum(nvl(gross_amount,0))
INTO l_inv_amt_remaining, l_gross_amount
FROM ap_payment_schedules
WHERE invoice_id = p_invoice_id;
SELECT payment_cross_rate,
payment_currency_code,
invoice_type_lookup_code --Bug 1724924
INTO l_payment_cross_rate,
l_pay_curr_code,
l_invoice_type --Bug 1724924
FROM ap_invoices
WHERE invoice_id = p_invoice_id;
update ap_payment_schedules
set amount_remaining = 0
where invoice_id = p_invoice_id;
update ap_payment_schedules
set amount_remaining = (amount_remaining +
ap_utilities_pkg.ap_round_currency(
(amount_remaining * (l_manual_awt_amount/l_inv_amt_remaining)
* l_payment_cross_rate), l_pay_curr_code ) )
where invoice_id = p_invoice_id;
update ap_payment_schedules
set amount_remaining =
(amount_remaining +
ap_utilities_pkg.ap_round_currency(
(gross_amount * (l_manual_awt_amount/l_gross_amount)
* l_payment_cross_rate), l_pay_curr_code) ),
payment_status_flag = DECODE(payment_status_flag,
'Y','P',payment_status_flag)
where invoice_id = p_invoice_id;
update ap_invoices
set payment_status_flag = DECODE(payment_status_flag,
'Y','P',payment_status_flag)
where invoice_id = p_invoice_id;
PROCEDURE Update_Pay_Sched_For_Awt(p_invoice_id IN NUMBER,
p_last_updated_by IN NUMBER,
p_last_update_login IN NUMBER,
p_calling_sequence IN VARCHAR2) IS
CURSOR Update_payment_schedule IS
SELECT payment_num,gross_amount,amount_remaining
FROM ap_payment_schedules
WHERE invoice_id=p_invoice_id;
l_debug_loc VARCHAR2(30) := 'Update_Pay_Sched_For_Awt';
SELECT (0 - sum(nvl(amount,0)))
INTO l_automatic_awt_amount
FROM ap_invoice_distributions
WHERE invoice_id = p_invoice_id
AND line_type_lookup_code = 'AWT'
AND awt_flag = 'A';
SELECT payment_cross_rate,
payment_currency_code,
invoice_type_lookup_code
INTO l_payment_cross_rate,
l_pay_curr_code,
l_invoice_type
FROM ap_invoices
WHERE invoice_id = p_invoice_id;
OPEN Update_payment_schedule;
FETCH Update_payment_schedule into l_payment_num,l_gross_amount,l_inv_amt_remaining;
EXIT WHEN Update_payment_schedule%NOTFOUND;
SELECT nvl(ap_utilities_pkg.ap_round_currency(
l_automatic_awt_amount*
ai.payment_cross_rate,l_pay_curr_code),0)*
l_gross_amount/decode(ai.pay_curr_invoice_amount, 0, 1,
nvl(ai.pay_curr_invoice_amount, 1))
into l_wt_amt_to_subtract
from ap_invoices ai
where ai.invoice_id=p_invoice_id;
update ap_payment_schedules
set amount_remaining = 0
where invoice_id = p_invoice_id
and payment_num=l_payment_num;
update ap_payment_schedules
set amount_remaining = (amount_remaining -
ap_utilities_pkg.ap_round_currency(
(amount_remaining * ( l_wt_amt_to_subtract/l_inv_amt_remaining)
* l_payment_cross_rate), l_pay_curr_code ) )
where invoice_id = p_invoice_id
and payment_num=l_payment_num;
update ap_payment_schedules
set payment_status_flag ='Y'
where invoice_id = p_invoice_id
and payment_num=l_payment_num
and amount_remaining = 0
and nvl(payment_status_flag,'N') <> 'Y';
CLOSE Update_payment_schedule;
', Calling module = ' || 'Update_Pay_Sched_For_Awt' );
END Update_Pay_Sched_For_Awt;
l_last_updated_by NUMBER;
select count(*)
into l_schedule_count
from ap_payment_schedules_all
where invoice_id = p_invoice_id;
SELECT invoice_amount, -- TODO: amount_applicable_to_discount,
terms_id,
last_updated_by,
created_by,
batch_id,
terms_date,
invoice_amount,
nvl(pay_curr_invoice_amount, invoice_amount),
payment_cross_rate,
payment_method_code, --4552701
invoice_currency_code,
payment_currency_code
INTO l_discountable_amount,
l_terms_id,
l_last_updated_by,
l_created_by,
l_batch_id,
l_terms_date,
l_invoice_amount,
l_pay_curr_invoice_amount,
l_payment_cross_rate,
l_payment_method,
l_invoice_curr_code,
l_pay_curr_code
FROM ap_invoices
WHERE invoice_id = p_invoice_id;
l_last_updated_by,
l_created_by,
null, -- TODO: why payment_priority is null?
l_batch_id,
l_terms_date,
l_invoice_amount,
l_pay_curr_invoice_amount,
l_payment_cross_rate,
l_discountable_amount,
l_payment_method,
l_invoice_curr_code,
l_pay_curr_code,
l_curr_calling_sequence);
SELECT registration_api,
registration_view
INTO x_registration_api,
x_registration_view
FROM ap_product_registrations
WHERE application_id = 200
AND reg_application_id = p_application_id
AND registration_event_type = 'DISTRIBUTION_GENERATION';
SELECT nvl(aerd.org_id,aerl.org_id), --Bug5867415
aerd.sequence_num,
aerd.code_combination_id,
aerd.amount,
aerd.project_id,
aerd.task_id,
aerd.award_id,
aerl.pa_quantity, -- bug6699834
aerd.expenditure_organization_id,
--bugfix:4939074
aerl.expenditure_type,
aerl.expenditure_item_date,
aerd.receipt_currency_amount, --bug6520882
aerd.receipt_currency_code,
aerd.receipt_conversion_rate
FROM ap_exp_report_dists_all aerd,
ap_expense_report_lines_all aerl
WHERE aerd.report_header_id = p_invoice_line_rec.reference_key1
AND aerd.report_line_id = p_invoice_line_rec.reference_key2
AND aerd.report_line_id = aerl.report_line_id
AND aerd.report_header_id = aerl.report_header_id
ORDER BY report_distribution_id;
SELECT ap_invoice_distributions_s.nextval
INTO l_dist_tab(i).invoice_distribution_id
FROM DUAL;
SELECT account_type
INTO l_account_type
FROM gl_code_combinations
WHERE code_combination_id = l_dist_tab(i).dist_code_combination_id;
l_dist_tab(i).last_update_date := SYSDATE;
l_dist_tab(i).last_update_login := FND_GLOBAL.login_id;
l_dist_tab(i).last_updated_by := FND_GLOBAL.user_id;
l_debug_info := 'Bulk Insert into ap_invoice_distributions';
INSERT INTO ap_invoice_distributions
VALUES l_dist_tab(j);
UPDATE AP_INVOICE_LINES
SET GENERATE_DISTS = 'D'
WHERE invoice_id = p_invoice_line_rec.invoice_id
AND line_number = p_invoice_line_rec.line_number;
l_selected_invoice_ids invoiceIDTab; -- 7461423
CURSOR SELECTED_INVOICES_CURSOR IS
SELECT
I.invoice_id,
I.invoice_num,
I.org_id,
I.invoice_amount,
I.base_amount,
I.exchange_rate,
I.invoice_currency_code,
S.invoice_amount_limit,
nvl(S.hold_future_payments_flag,'N') hold_future_payments_flag,
I.invoice_type_lookup_code,
I.exchange_date,
I.exchange_rate_type,
I.vendor_id,
I.invoice_date,
nvl(I.disc_is_inv_less_tax_flag,'N') disc_is_inv_less_tax_flag,
nvl(I.exclude_freight_from_discount,'N') exclude_freight_from_discount,
S.tolerance_id,
s.services_tolerance_id
FROM ap_invoices_all I,
ap_supplier_sites_all S
WHERE I.vendor_site_id = S.vendor_site_id (+)
AND I.validation_request_id = AP_APPROVAL_PKG.G_VALIDATION_REQUEST_ID
ORDER BY I.org_id;
SELECT I.invoice_id, i.invoice_num, i.org_id
FROM ap_invoices_all I,
ap_supplier_sites_all S
WHERE I.vendor_site_id = S.vendor_site_id (+)
AND I.validation_request_id = AP_APPROVAL_PKG.G_VALIDATION_REQUEST_ID
ORDER BY I.org_id;
g_org_holds.delete;
UPDATE ap_invoices api
SET validation_request_id = NULL
WHERE validation_request_id IS NOT NULL
AND EXISTS
( SELECT 'Request Completed'
FROM fnd_concurrent_requests fcr
WHERE fcr.request_id = api.validation_request_id
AND fcr.phase_code = 'C' );
UPDATE ap_invoices_all ai
SET ai.validation_request_id = p_conc_request_id
WHERE ai.invoice_id = l_invoice_id
AND ai.validation_request_id IS NULL
/*bug 7029877 Invoice saved but not submitted*/
AND ai.approval_ready_flag <>'S'
AND EXISTS (select ail.invoice_id
from ap_invoice_lines_all ail
where ail.invoice_id = ai.invoice_id) ;
SELECT
ai.invoice_num, ai.org_id, ai.invoice_type_lookup_code, ai.validation_request_id,
ai.invoice_id, ai.invoice_date, ai.invoice_currency_code, ai.exchange_rate,
ai.exchange_rate_type, ai.exchange_date, ai.vendor_id, ai.org_id, s.tolerance_id,
s.services_tolerance_id
INTO
l_invoice_num, l_sel_org_id, l_sel_invoice_type, l_validation_request_id,
l_invoice_id, l_invoice_date, l_invoice_currency_code, l_exchange_rate,
l_exchange_rate_type, l_exchange_date, l_vendor_id, l_org_id, l_tolerance_id,
l_services_tolerance_id
FROM ap_invoices_all ai,
ap_supplier_sites_all s
WHERE ai.invoice_id = l_invoice_id
AND ai.vendor_site_id = s.vendor_site_id(+);
UPDATE ap_invoices_all
SET validation_request_id = NULL
WHERE invoice_id = l_invoice_id;
/*bug6858309 modified this dynamic update to filter out
recurring invoices havign GL DATE in never open period*/
/* Added for bug#7270053 Start */
l_sql_stmt :=l_sql_stmt||
'SELECT invoice_id from AP_INVOICES_ALL AI '|| -- 7461423
' WHERE AI.VALIDATION_REQUEST_ID IS NULL '||
' AND AI.APPROVAL_READY_FLAG <> ''S'' '||
' AND (UPPER(NVL(AI.SOURCE, ''X'')) <> ''RECURRING INVOICE'' OR '||
' (UPPER(NVL(AI.SOURCE, ''X'')) = ''RECURRING INVOICE'' AND NOT EXISTS '||
' (SELECT NULL '||
' FROM GL_PERIOD_STATUSES GLPS, AP_SYSTEM_PARAMETERS SP '||
' WHERE GLPS.APPLICATION_ID = ''200'' '||
' AND SP.ORG_ID = AI.ORG_ID '||
' AND GLPS.SET_OF_BOOKS_ID = SP.SET_OF_BOOKS_ID '||
' AND TRUNC(AI.GL_DATE) BETWEEN GLPS.START_DATE AND GLPS.END_DATE '||
' AND NVL(GLPS.ADJUSTMENT_PERIOD_FLAG, ''N'') = ''N'' '||
' AND GLPS.CLOSING_STATUS = ''N''))) '||
' AND NOT (AI.PAYMENT_STATUS_FLAG = ''Y'' AND '||
' AI.HISTORICAL_FLAG = ''Y'') '||
' AND ((( ( EXISTS '||
' (SELECT 1 '||
' FROM AP_INVOICE_DISTRIBUTIONS D '||
' WHERE D.INVOICE_ID = AI.INVOICE_ID '||
' AND NVL(D.MATCH_STATUS_FLAG, ''N'') <> ''A''' ||
' ) '||
' OR EXISTS '||
' (SELECT ''Unreleased Hold exists'' '||
' FROM AP_HOLDS H '||
' WHERE H.INVOICE_ID = AI.INVOICE_ID '||
' AND H.HOLD_LOOKUP_CODE IN '||
' (''QTY ORD'', ''QTY REC'', ''AMT ORD'', ''AMT REC'', ''QUALITY'', '||
' ''PRICE'', ''TAX DIFFERENCE'', ''CURRENCY DIFFERENCE'', '||
' ''REC EXCEPTION'', ''TAX VARIANCE'', ''PO NOT APPROVED'', '||
' ''PO REQUIRED'', ''MAX SHIP AMOUNT'', ''MAX RATE AMOUNT'', '||
' ''MAX TOTAL AMOUNT'', ''TAX AMOUNT RANGE'', ''MAX QTY ORD'', '||
' ''MAX QTY REC'', ''MAX AMT ORD'', ''MAX AMT REC'', '||
' ''CANT CLOSE PO'', ''CANT TRY PO CLOSE'', ''LINE VARIANCE'', '||
' ''CANT FUNDS CHECK'') '||
' AND H.RELEASE_LOOKUP_CODE IS NULL ' ||
' ) '||
' ) ' ||
' OR '||
' (AI.FORCE_REVALIDATION_FLAG = ''Y'')'||
' ) '||
' AND NOT EXISTS '||
' (SELECT ''Cancelled distributions'' '||
' FROM AP_INVOICE_DISTRIBUTIONS_ALL D2 '||
' WHERE D2.INVOICE_ID = AI.INVOICE_ID '||
' AND D2.CANCELLATION_FLAG = ''Y'''||
' )'||
' ) '||
' OR EXISTS '||
' (SELECT 1 '||
' FROM AP_INVOICE_LINES AIL '||
' WHERE AIL.INVOICE_ID = AI.INVOICE_ID '||
' AND AI.CANCELLED_DATE IS NULL '||
' AND NVL(AIL.DISCARDED_FLAG, ''N'') <> ''Y'' '||
' AND NVL(AIL.CANCELLED_FLAG, ''N'') <> ''Y'' '||
' AND NOT EXISTS '||
' (SELECT /*+ NO_UNNEST */ '||
' ''distributed line'' '||
' FROM AP_INVOICE_DISTRIBUTIONS_ALL D5 '||
' WHERE D5.INVOICE_ID = AIL.INVOICE_ID '||
' AND D5.INVOICE_LINE_NUMBER = AIL.LINE_NUMBER))) ' ;
l_sql_stmt :=l_sql_stmt || ' FOR UPDATE SKIP LOCKED';
BULK COLLECT INTO l_selected_invoice_ids;
IF l_selected_invoice_ids.count > 0 THEN
FOR k IN 1..l_selected_invoice_ids.count
LOOP
UPDATE ap_invoices_all
SET validation_request_id = AP_APPROVAL_PKG.G_VALIDATION_REQUEST_ID
WHERE invoice_id = l_selected_invoice_ids(k)
AND validation_request_id IS NULL;
UPDATE ap_invoices_all
SET validation_request_id = NULL
WHERE validation_request_id IS NOT NULL
AND invoice_id IN (SELECT invoice_id
FROM ap_invoices_all
WHERE validation_request_id = AP_APPROVAL_PKG.G_VALIDATION_REQUEST_ID);
UPDATE ap_invoices_all
SET validation_request_id = AP_APPROVAL_PKG.G_VALIDATION_REQUEST_ID
WHERE invoice_id = l_selc_inv_cursor_blk_err(i).invoice_id;
SELECT
ai.invoice_num, ai.org_id, ai.invoice_type_lookup_code, ai.validation_request_id,
ai.invoice_id, ai.invoice_date, ai.invoice_currency_code, ai.exchange_rate,
ai.exchange_rate_type, ai.exchange_date, ai.vendor_id, ai.org_id, s.tolerance_id,
s.services_tolerance_id
INTO
l_invoice_num, l_sel_org_id, l_sel_invoice_type, l_validation_request_id,
l_invoice_id, l_invoice_date, l_invoice_currency_code, l_exchange_rate,
l_exchange_rate_type, l_exchange_date, l_vendor_id, l_org_id, l_tolerance_id,
l_services_tolerance_id
FROM
ap_invoices_all ai,
ap_supplier_sites_all s
WHERE
ai.invoice_id = l_selc_inv_cursor_blk_err(i).invoice_id
AND
ai.vendor_site_id = s.vendor_site_id(+);
-- BUG 7509921 Update Validation Request id to null
UPDATE ap_invoices_all
SET validation_request_id = NULL
WHERE invoice_id = l_selc_inv_cursor_blk_err(i).invoice_id;
OPEN SELECTED_INVOICES_CURSOR;
FETCH SELECTED_INVOICES_CURSOR
BULK COLLECT INTO AP_APPROVAL_PKG.G_SELECTED_INVOICES
LIMIT l_commit_size;
EXIT WHEN SELECTED_INVOICES_CURSOR%NOTFOUND
AND AP_APPROVAL_PKG.G_SELECTED_INVOICES.COUNT <= 0;
FOR i IN 1..AP_APPROVAL_PKG.G_SELECTED_INVOICES.COUNT
LOOP
-- Set Policy
IF AP_APPROVAL_PKG.G_SELECTED_INVOICES(i).org_id <> nvl(l_old_org_id, -3115) THEN
mo_global.set_policy_context
('S', AP_APPROVAL_PKG.G_SELECTED_INVOICES(i).org_id);
l_old_org_id := AP_APPROVAL_PKG.G_SELECTED_INVOICES(i).org_id;
p_invoice_id => AP_APPROVAL_PKG.G_SELECTED_INVOICES(i).invoice_id,
p_calling_sequence => l_curr_calling_sequence);
g_org_id := AP_APPROVAL_PKG.G_SELECTED_INVOICES(i).org_id;
(p_invoice_rec => AP_APPROVAL_PKG.G_SELECTED_INVOICES(i),
p_base_currency_code => AP_APPROVAL_PKG.G_OPTIONS_TABLE(g_org_id).base_currency_code,
p_inv_batch_id => p_inv_batch_id,
p_run_option => p_run_option,
p_calling_sequence => l_curr_calling_sequence,
x_error_code => l_error_code);
AP_APPROVAL_PKG.G_SELECTED_INVOICES.DELETE;
CLOSE SELECTED_INVOICES_CURSOR;
UPDATE ap_invoices_all
SET validation_request_id = NULL
WHERE validation_request_id IS NOT NULL
AND invoice_id IN (SELECT invoice_id
FROM ap_invoices_all
WHERE validation_request_id = AP_APPROVAL_PKG.G_VALIDATION_REQUEST_ID);
UPDATE ap_invoices_all
SET validation_request_id = AP_APPROVAL_PKG.G_VALIDATION_REQUEST_ID
WHERE invoice_id = l_selc_inv_cursor_blk_err(i).invoice_id;
UPDATE ap_invoices_all
SET validation_request_id = NULL
WHERE invoice_id = l_selc_inv_cursor_blk_err(i).invoice_id;
OPEN SELECTED_INVOICES_CURSOR;
FETCH SELECTED_INVOICES_CURSOR
BULK COLLECT INTO AP_APPROVAL_PKG.G_SELECTED_INVOICES
LIMIT l_commit_size;
EXIT WHEN SELECTED_INVOICES_CURSOR%NOTFOUND
and AP_APPROVAL_PKG.G_SELECTED_INVOICES.COUNT <= 0;
FOR i IN 1..AP_APPROVAL_PKG.G_SELECTED_INVOICES.COUNT LOOP
-- Set Policy
IF AP_APPROVAL_PKG.G_SELECTED_INVOICES(i).org_id <> nvl(l_old_org_id, -3115) THEN
mo_global.set_policy_context('S', AP_APPROVAL_PKG.G_SELECTED_INVOICES(i).org_id);
l_old_org_id := AP_APPROVAL_PKG.G_SELECTED_INVOICES(i).org_id;
IF AP_APPROVAL_PKG.G_SELECTED_INVOICES(i).invoice_type_lookup_code = 'PAYMENT REQUEST' THEN
l_calling_mode := 'PAYMENT REQUEST';
AP_APPROVAL_PKG.G_SELECTED_INVOICES(i).tolerance_id,
AP_APPROVAL_PKG.G_SELECTED_INVOICES(i).services_tolerance_id,
l_calling_sequence);
AP_APPROVAL_PKG.G_SELECTED_INVOICES(i).invoice_id) THEN
--Removed the hardcoded value of p_budget_control, bug6356402
AP_APPROVAL_PKG.approve(
'',
'',
'',
'',
'',
'',
AP_APPROVAL_PKG.G_SELECTED_INVOICES(i).invoice_id,
'',
'',
'',
'Y',
l_holds_count,
l_approval_status,
l_funds_return_code,
l_calling_mode,
'APXAPRVL',
p_debug_switch
);
fnd_message.set_token('INV_NUM', AP_APPROVAL_PKG.G_SELECTED_INVOICES(i).invoice_num);
UPDATE ap_invoices_all
SET validation_request_id = NULL
WHERE invoice_id = AP_APPROVAL_PKG.G_SELECTED_INVOICES(i).invoice_id;
AP_APPROVAL_PKG.G_SELECTED_INVOICES.DELETE;
CLOSE SELECTED_INVOICES_CURSOR;
DELETE FROM ap_temp_approval_gt;
INSERT INTO ap_temp_approval_gt VALUES g_org_holds(i);
SELECT count(*) into p_report_holds_count
FROM ap_temp_approval_gt
WHERE number_holds_placed <> 0
OR number_holds_released <> 0;
SELECT nvl(gls.chart_of_accounts_id, -1) chart_of_accounts_id,
nvl(sp.set_of_books_id, -1) set_of_books_id,
nvl(sp.automatic_offsets_flag, 'N') automatic_offsets_flag,
nvl(recalc_pay_schedule_flag, 'N') recalc_pay_schedule_flag,
sp.liability_post_lookup_code liability_post_lookup_code,
nvl(sp.rate_var_gain_ccid, -1) rate_var_gain_ccid,
nvl(sp.rate_var_loss_ccid, -1) rate_var_loss_ccid,
nvl(sp.base_currency_code, 'USD') base_currency_code,
nvl(sp.match_on_tax_flag, 'N') match_on_tax_flag,
nvl(sp.enforce_tax_from_account, 'N') enforce_tax_from_account,
nvl(fp.inv_encumbrance_type_id, -1) inv_encumbrance_type_id,
nvl(fp.purch_encumbrance_type_id, -1) purch_encumbrance_type_id,
nvl(fp.receipt_acceptance_days, 0) receipt_acceptance_days,
nvl(gl_date_from_receipt_flag, 'S') gl_date_from_receipt_flag,
accounting_method_option,
secondary_accounting_method,
nvl(fp.cash_basis_enc_nr_tax, 'EXCLUDE RECOVERABLE TAX') cash_basis_enc_nr_tax,
nvl(fp.non_recoverable_tax_flag, 'N') non_recoverable_tax_flag,
nvl(disc_is_inv_less_tax_flag,'N') disc_is_inv_less_tax_flag,
fp.org_id org_id,
5 System_User,
fnd_global.user_id User_Id
FROM ap_system_parameters_all sp,
financials_system_params_all fp,
gl_sets_of_books gls,
Mo_Glob_Org_Access_Tmp mo
WHERE sp.set_of_books_id = gls.set_of_books_id
AND sp.org_id = fp.org_id
AND mo.organization_id = fp.org_id;
SELECT decode( price_tolerance, NULL, NULL,
(1 + (price_tolerance/100))),
decode(quantity_tolerance, NULL, NULL,
(1 + (quantity_tolerance/100))),
decode( qty_received_tolerance, NULL, NULL,
(1 + (qty_received_tolerance/100))),
max_qty_ord_tolerance,
max_qty_rec_tolerance,
ship_amt_tolerance,
rate_amt_tolerance,
total_amt_tolerance
INTO G_GOODS_TOLERANCES(p_tolerance_id).price_tolerance,
G_GOODS_TOLERANCES(p_tolerance_id).quantity_tolerance,
G_GOODS_TOLERANCES(p_tolerance_id).qty_received_tolerance,
G_GOODS_TOLERANCES(p_tolerance_id).max_qty_ord_tolerance,
G_GOODS_TOLERANCES(p_tolerance_id).max_qty_rec_tolerance,
G_GOODS_TOLERANCES(p_tolerance_id).ship_amt_tolerance,
G_GOODS_TOLERANCES(p_tolerance_id).rate_amt_tolerance,
G_GOODS_TOLERANCES(p_tolerance_id).total_amt_tolerance
FROM ap_tolerance_templates
WHERE tolerance_id = p_tolerance_id;
SELECT decode(quantity_tolerance, NULL, NULL,
(1 + (quantity_tolerance/100))),
decode( qty_received_tolerance, NULL, NULL,
(1 + (qty_received_tolerance/100))),
max_qty_ord_tolerance,
max_qty_rec_tolerance,
ship_amt_tolerance,
rate_amt_tolerance,
total_amt_tolerance
INTO G_SERVICES_TOLERANCES(p_services_tolerance_id).amount_tolerance,
G_SERVICES_TOLERANCES(p_services_tolerance_id).amt_received_tolerance,
G_SERVICES_TOLERANCES(p_services_tolerance_id).max_amt_ord_tolerance,
G_SERVICES_TOLERANCES(p_services_tolerance_id).max_amt_rec_tolerance,
G_SERVICES_TOLERANCES(p_services_tolerance_id).ser_ship_amt_tolerance,
G_SERVICES_TOLERANCES(p_services_tolerance_id).ser_rate_amt_tolerance,
G_SERVICES_TOLERANCES(p_services_tolerance_id).ser_total_amt_tolerance
FROM ap_tolerance_templates
WHERE tolerance_id = p_services_tolerance_id;
SELECT hold_lookup_code
FROM ap_holds_all
WHERE hold_lookup_code IN ('DISTRIBUTION SET INACTIVE',
'SKELETON DISTRIBUTION SET',
'CANNOT OVERLAY ACCOUNT',
'INVALID DEFAULT ACCOUNT',
'CANNOT EXECUTE ALLOCATION',
'CANNOT OVERLAY ACCOUNT',
'INVALID DEFAULT ACCOUNT',
'PERIOD CLOSED',
'PROJECT GL DATE CLOSED')
AND release_lookup_code IS NULL
AND invoice_id = p_invoice_id;
/* is inserted for the Invoice, then the generate_dist flag */
/* for the line is set to 'D', and hence the code */
/* Execute_Dist_Generation_Check would no longer be called */
/*
/* As such, when there are no lines on the invoice */
/* for which the generate_dist flag is NOT 'D' then we would */
/* release all the holds on the Invoice which are put during */
/* the dist generation process : bug6783517 */
l_debug_info := 'Checking if there exists a non d line';
SELECT 'Y'
INTO l_not_exist_nond_line
FROM ap_invoice_lines_all ail
WHERE ail.invoice_id = p_invoice_rec.invoice_id
AND nvl(ail.generate_dists, 'N') <> 'D'
AND rownum < 2;
Update_Inv_Dists_To_Selected( p_invoice_rec.invoice_id,
null ,
p_run_option,
l_curr_calling_sequence);
SELECT nvl(prorate_across_all_items,'N')
INTO l_prorate_across_all_items
FROM ap_invoice_lines_all
WHERE invoice_id = t_inv_lines_table(i).invoice_id
AND line_number = t_inv_lines_table(i).line_number;
update ap_invoices_all
set amount_paid = nvl(amount_paid,0) + abs(l_recouped_amount)
,pay_curr_invoice_amount = ap_utilities_pkg.ap_round_currency
(pay_curr_invoice_amount * payment_cross_rate,
payment_currency_code)
where invoice_id = t_inv_lines_table(i).invoice_id;
l_debug_info := 'Update Invoice Distributions to SELECTED';
Update_Inv_Dists_To_Selected(
t_inv_lines_table(i).invoice_id,
t_inv_lines_table(i).line_number,
p_run_option,
l_curr_calling_sequence);
update ap_invoices_all
set amount_applicable_to_discount = amount_applicable_to_discount + l_retained_amount
,pay_curr_invoice_amount = ap_utilities_pkg.ap_round_currency
((invoice_amount + l_retained_amount) * payment_cross_rate,
payment_currency_code)
where invoice_id = p_invoice_rec.invoice_id
and nvl(net_of_retainage_flag, 'N') <> 'Y';
Select hold_lookup_code,
decode(release_lookup_code, NULL, 'ALREADY ON HOLD',
'RELEASED BY USER') hold_status,
invoice_id,
hold_reason,
release_lookup_code,
line_location_id,
rcv_transaction_id,
last_updated_by,
responsibility_id
From ap_holds
Where invoice_id = c_invoice_id
Order By 1, 2 DESC;
g_holds_tab.delete;
l_debug_info := 'Update Org Hold Count';