[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;
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
FROM ap_invoice_lines_all
WHERE invoice_id = c_invoice_id
AND line_type_lookup_code = 'AWT'
AND line_source = 'MANUAL LINE ENTRY'
ORDER BY 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'),
nvl(AI.net_of_retainage_flag,'N'), --9356460
nvl(pvs.tolerance_id,ASP.tolerance_id), --added nvl for bug 8425996
nvl(pvs.services_tolerance_id,ASP.services_tolerance_id) --added nvl for bug 8425996
FROM ap_invoices_all AI,
ap_suppliers PV,
ap_supplier_sites_all PVS,
ap_system_parameters_all ASP --added table for bug 8425996
WHERE AI.invoice_id = p_invoice_id
AND AI.vendor_id = PV.vendor_id
AND AI.vendor_site_id = PVS.vendor_site_id
AND ASP.org_id = AI.org_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'),
nvl(AI.net_of_retainage_flag,'N') --9356460
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;
SELECT COUNT(*)
INTO l_manual_awt_exist
FROM ap_invoice_lines_all ail
WHERE ail.invoice_id = l_invoice_rec.invoice_id
AND ail.line_type_lookup_code = 'AWT'
AND ail.line_source = 'MANUAL LINE ENTRY';
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(1)
INTO l_zx_lines_det_fac_count
FROM zx_lines_det_factors
WHERE application_id = 200
AND entity_code = 'AP_INVOICES'
AND trx_id = l_invoice_id
AND event_class_code IN ('STANDARD INVOICES','PREPAYMENT INVOICES','EXPENSE REPORTS')
AND ROWNUM = 1 ;
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_lcm_used
FROM DUAL
WHERE EXISTS
(SELECT 1
FROM AP_INVOICE_DISTRIBUTIONS_ALL 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'
UNION ALL
SELECT 1
FROM AP_INVOICE_DISTRIBUTIONS_ALL aid,
RCV_TRANSACTIONS rt,
AP_INVOICE_DISTRIBUTIONS_ALL aid2
WHERE aid.invoice_id = l_invoice_id
AND aid.corrected_invoice_dist_id = aid2.invoice_distribution_id
AND aid2.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')) AND C.user_releaseable_flag = 'N'));
/* 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 COUNT(1)
INTO l_unfreeze_count
FROM zx_rec_nrec_dist
WHERE application_id = 200
AND entity_code = 'AP_INVOICES'
AND event_class_code IN ('STANDARD INVOICES', 'PREPAYMENT INVOICES', 'EXPENSE REPORTS')
AND trx_id = l_inv_header_rec.invoice_id
AND freeze_flag = 'N';
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 NVL(D.posted_flag, 'N' ) = 'N' -- Bug 9777752
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 NVL(D.posted_flag, 'N' ) = 'N' -- Bug 9777752
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 NVL(D.posted_flag, 'N' ) = 'N' -- Bug 9777752
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 NVL(D.posted_flag, 'N' ) = 'N' -- Bug 9777752
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_AWT_Dist_From_Line';
l_success := AP_INVOICE_DISTRIBUTIONS_PKG.Insert_AWT_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_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 vendor_id,vendor_site_id,
remit_to_supplier_site_id,invoice_type_lookup_code
into l_vendor_id,l_vendor_site_id,
l_remit_to_supplier_site_id,l_invoice_type_lookup_code
from ap_invoices_all
where invoice_id = p_invoice_id;
/* Select vendor_id,vendor_site_id,
remit_to_supplier_site_id,invoice_type_lookup_code
into l_vendor_id,l_vendor_site_id,
l_remit_to_supplier_site_id,l_invoice_type_lookup_code
from ap_invoices_all
where invoice_id = p_invoice_id; */
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'
and nvl(apd.reversal_flag,'N')='N' --added for bug 16061729
/*group by apd.dist_code_combination_id --commented for bug 16061729
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
AND AIL.LINE_TYPE_LOOKUP_CODE not in ('TAX', 'AWT') -- bug 9582952 --bug16090813
-- 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 INVOICE_TYPE_LOOKUP_CODE
INTO l_invoice_type
FROM AP_INVOICES_ALL
WHERE INVOICE_ID=p_invoice_id;
select base_amount, amount -- Bug 12686348: Added column amount
INTO l_base_amt, l_amt -- Bug 12686348: Added column amount
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)
AND line_type_lookup_code <> 'TAX'; -- bug 9582952
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'
AND NVL(reversal_flag, 'N') = 'N' -- Bug 9178329
--Bugfix:4625771
AND related_id IS NULL
AND line_type_lookup_code NOT IN ('NONREC_TAX', 'REC_TAX', 'TRV', 'TERV', 'TIPV', 'AWT') -- bug 9582952 --bug16090813
AND dist_match_type <> 'ADJUSTMENT_CORRECTION'; --Bug#10416960
select base_amount, amount -- Bug 12686348: Added column amount
INTO l_base_amt, l_amt -- Bug 12686348: Added column amount
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)
AND line_type_lookup_code NOT IN ('NONREC_TAX','REC_TAX','TRV','TERV','TIPV'); -- bug 9582952
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;
SELECT count(*)
INTO l_null_event_id_self
FROM ap_self_assessed_tax_dist_all ast
WHERE ast.invoice_id = P_invoice_id
AND ast.accounting_event_id is NULL
AND rownum = 1;
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 +
(SELECT NVL(SUM(nvl(ail1.amount,0)), 0) --Bug 13050996
FROM ap_invoice_lines_all ail1
WHERE ail1.invoice_id=ai.invoice_id
AND ail1.line_type_lookup_code ='AWT')
,nvl(AI.amount_paid,0)
, (0 - sum(nvl(AIL.amount,0))) -- taking the remaining amount to be paid on as part of bug 8339454
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' --Bug 13050996: Uncommented out this line
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,AI.amount_paid
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')
OR /*Bug 9242891: Added below conditions for considering retainage release inv and return matched status*/
(line_type_lookup_code = 'RETAINAGE'
AND retained_invoice_dist_id is not NULL));
| 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 nvl(asp.withholding_date_basis,'INVOICEDATE')
INTO l_withhold_date_basis
FROM ap_system_parameters_all asp,
ap_invoices_all ai
WHERE ai.invoice_id = p_invoice_id
AND ai.org_id = asp.org_id;
SELECT invoice_date
INTO l_withholding_date
FROM ap_invoices
WHERE invoice_id = p_invoice_id;
SELECT gl_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_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_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_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
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 UPDATE_SCHEDULES
| This procedure updates/ recreates payment schedules whenever
| 'Expired Registration' hold releases for an invoice.
============================================================================*/
PROCEDURE UPDATE_SCHEDULES(P_INVOICE_ID IN AP_INVOICES.INVOICE_ID%TYPE,
P_CALLING_SEQUENCE IN VARCHAR2)IS
cursor invoice_cursor is
select AI.terms_id,
AI.last_updated_by,
AI.created_by,
AI.batch_id,
AI.terms_date,
AI.invoice_amount,
nvl(AI.pay_curr_invoice_amount, invoice_amount),
AI.payment_cross_rate,
AI.amount_applicable_to_discount,
AI.payment_method_code,
AI.invoice_currency_code,
AI.payment_currency_code
from ap_invoices AI
where AI.invoice_id = p_invoice_id;
l_last_updated_by ap_invoices.last_updated_by%type;
l_current_calling_sequence := 'AP_APPROVAL_PKG.UPDATE_SCHEDULES <- '|| p_calling_sequence;
l_debug_info := 'Begin UPDATE_SCHEDULES procedure';
select terms_date
into l_old_terms_date
from ap_invoices_all
where invoice_id = p_invoice_id;
Update ap_invoices_all
set terms_date =trunc(sysdate),
invoice_received_date = decode(invoice_received_date,null,null,trunc(sysdate)) --bug9148859
where invoice_id = p_invoice_id;
select count(*)
into l_paid_schd_count
from ap_payment_schedules_all
where invoice_id = p_invoice_id
and nvl(payment_status_flag,'N') <> 'N';
Update ap_payment_schedules_all
set due_date = due_date +(sysdate-l_old_terms_date)
where invoice_id = p_invoice_id
and nvl(payment_status_flag,'N') <> 'Y';
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_amt_applicable_to_discount,
l_payment_method_code,
l_invoice_currency_code,
l_payment_currency_code;
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_amt_applicable_to_discount,
l_payment_method_code,
l_invoice_currency_code,
l_payment_currency_code,
l_current_calling_sequence);
END UPDATE_SCHEDULES;
| 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);
SELECT wf_status,
hold_id
INTO l_old_wf_status,
l_hold_id
FROM ap_holds
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';
FOR c_wf_status IN ( SELECT hold_id
FROM ap_holds
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'
AND wf_status /* Bug 9691312 = 'STARTED' */ IN ( 'STARTED', 'NEGOTIATE' )
)
LOOP
AP_WORKFLOW_PKG.abort_holds_workflow( c_wf_status.hold_id ) ;
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';
UPDATE_SCHEDULES(p_invoice_id,p_calling_sequence);
| 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;
select org_id into l_org_id
from ap_invoices where invoice_id = p_invoice_id;
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',
nvl(g_org_id,l_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
--Bug8340784 Added ITEM to line_type_lookup_code to
--include recoupment amount on an invoice
AND ail.line_type_lookup_code IN ('PREPAY', 'ITEM')
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_distributions aid
WHERE aid.invoice_id = p_invoice_id
AND aid.prepay_distribution_id is not null
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,
org_id --8405589
--Commented for bug#9356460
-- net_of_retainage_flag --8405589
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,
l_org_id --8405589
--Commented for bug#9356460
-- l_net_of_retainage_flag --8405589
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+l_retained_amount
WHERE invoice_id = p_invoice_id;
SELECT sum(amount),
sum(base_amount)
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');
select BASE_CURRENCY_CODE
into l_tmp_base_curr_code
from ap_system_parameters_all asp, ap_invoices_all ai
where asp.org_id = ai.org_id
and ai.invoice_id = p_invoice_id;
UPDATE ap_invoices
SET invoice_amount = l_tmp_invoice_amount,
amount_applicable_to_discount = l_tmp_invoice_amount,
/* bug 13035539 starts */
base_amount =
( CASE
WHEN
invoice_currency_code <> l_tmp_base_curr_code
THEN
l_tmp_base_amount
ELSE
NULL
END),
/* bug 13035539 ends */
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,
aerl.merchant_document_number,--bug14335065 Adding below 5 merchant fields
aerl.merchant_name,
aerl.merchant_reference,
aerl.merchant_tax_reg_number,
aerl.merchant_taxpayer_id,
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,
nvl(S.tolerance_id,ASP.tolerance_id), --Bug8524767
nvl(S.services_tolerance_id,ASP.services_tolerance_id) --Bug8524767
FROM ap_invoices_all I,
ap_supplier_sites_all S,
ap_system_parameters_all ASP --Bug8524767
WHERE I.vendor_site_id = S.vendor_site_id (+)
AND I.validation_request_id = AP_APPROVAL_PKG.G_VALIDATION_REQUEST_ID
AND ASP.org_id = I.org_id
ORDER BY I.org_id;
l_selected_invoices_cursor AP_APPROVAL_PKG.Invoices_Table;
SELECT I.invoice_id, i.invoice_num, i.org_id
FROM ap_invoices_all I
WHERE I.validation_request_id = AP_APPROVAL_PKG.G_VALIDATION_REQUEST_ID
ORDER BY I.org_id;
l_sql_tax_err varchar2(1000) := 'SELECT trx_id FROM zx_errors_gt UNION SELECT invoice_id from ap_errors_gt'; --bug10140354
SELECT trx_id, trx_line_id, message_text
FROM zx_errors_gt
UNION
SELECT invoice_id , reference_key2 , message_text
FROM ap_errors_gt;
SELECT AP_INVOICES_PKG.Get_Approval_Status(
invoice_id,
invoice_amount,
payment_status_flag,
invoice_type_lookup_code)
FROM ap_invoices_all
WHERE invoice_id = p_invoice_id
FOR UPDATE NOWAIT; -- Bug 13374062
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 api
SET validation_request_id = NULL
WHERE validation_request_id IN
( SELECT request_id
FROM fnd_concurrent_requests fcr
WHERE fcr.concurrent_program_id = ( SELECT concurrent_program_id
FROM fnd_concurrent_programs fcp
WHERE fcp.application_id = 200
AND fcp.concurrent_program_name = 'APPRVL'
)
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,
nvl(s.tolerance_id,asp.tolerance_id),nvl(s.services_tolerance_id,asp.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,
ap_system_parameters_all asp
WHERE ai.invoice_id = l_invoice_id
AND ai.vendor_site_id = s.vendor_site_id(+)
AND ai.org_id = asp.org_id;
SELECT COUNT(*)
INTO l_manual_awt_exist
FROM ap_invoice_lines_all ail
WHERE ail.invoice_id = l_invoice_rec.invoice_id
AND ail.line_type_lookup_code = 'AWT'
AND ail.line_source = 'MANUAL LINE ENTRY';
UPDATE ap_invoices_all
SET validation_request_id = NULL
WHERE invoice_id = l_invoice_id;
-- Print_Debug(l_api_name, 'Invoice is locked for update');
AP_Debug_Pkg.Print(g_debug_mode, 'Invoice is locked for update: invoice_id = '|| l_invoice_id );
FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,'Invoice is locked for update: 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 */
--BUG7902867 replace view with base tables in sub queries
-- AP_INVOICES_ALL is replace with ap_invoices
/* BUG 8218038 added nvl condition for historical and payment status flags */
/* Changed the Sql structure to replace the exists with UNION ALL for bug#7584153 */
-- Bug 9777752 : Restructured dynamic SQL
l_sql_stmt :=l_sql_stmt||
'SELECT /*+ dynamic_sampling(2) cardinality(ai,10) */ invoice_id from AP_INVOICES AI -- 7461423
WHERE AI.VALIDATION_REQUEST_ID IS NULL
AND AI.APPROVAL_READY_FLAG <> ''S''
AND AI.INVOICE_TYPE_LOOKUP_CODE<>''INVOICE REQUEST'' /*Bug 16263597*/
AND AI.CANCELLED_DATE IS NULL /* Bug 9777752 */
AND NOT ( NVL(AI.PAYMENT_STATUS_FLAG,''N'') = ''Y'' AND
NVL(AI.HISTORICAL_FLAG,''N'') = ''Y'' AND
NVL(AI.FORCE_REVALIDATION_FLAG,''N'') = ''N'') /*Bug11934187*/
AND EXISTS (
SELECT /*+ PUSH_SUBQ */ 1
FROM DUAL
WHERE UPPER(NVL(AI.SOURCE, ''X'')) <> ''RECURRING INVOICE''
UNION ALL
SELECT 1
FROM DUAL
WHERE UPPER(NVL(AI.SOURCE, ''X'')) = ''RECURRING INVOICE''
AND NOT EXISTS
( SELECT NULL
FROM GL_PERIOD_STATUSES GLPS
WHERE GLPS.APPLICATION_ID = ''200''
AND GLPS.SET_OF_BOOKS_ID = AI.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 EXISTS (
SELECT 1
FROM DUAL
WHERE AI.FORCE_REVALIDATION_FLAG = ''Y''
UNION ALL
SELECT 1
FROM AP_INVOICE_DISTRIBUTIONS_ALL D,
FINANCIALS_SYSTEM_PARAMS_ALL FSP
WHERE D.INVOICE_ID = AI.INVOICE_ID
AND FSP.ORG_ID = AI.ORG_ID
AND FSP.SET_OF_BOOKS_ID = AI.SET_OF_BOOKS_ID
AND (NVL(FSP.PURCH_ENCUMBRANCE_FLAG,''N'') = ''Y'' AND NVL(D.MATCH_STATUS_FLAG,''N'') <> ''A'' OR
(NVL(FSP.PURCH_ENCUMBRANCE_FLAG,''N'') = ''N'' AND NVL(D.MATCH_STATUS_FLAG,''N'') NOT IN (''A'',''T'')))
UNION ALL
SELECT 1
FROM AP_SELF_ASSESSED_TAX_DIST_ALL D,
FINANCIALS_SYSTEM_PARAMS_ALL FSP
WHERE D.INVOICE_ID = AI.INVOICE_ID
AND FSP.ORG_ID = AI.ORG_ID
AND FSP.SET_OF_BOOKS_ID = AI.SET_OF_BOOKS_ID
AND (NVL(FSP.PURCH_ENCUMBRANCE_FLAG,''N'') = ''Y'' AND NVL(D.MATCH_STATUS_FLAG,''N'') <> ''A'' OR
(NVL(FSP.PURCH_ENCUMBRANCE_FLAG,''N'') = ''N'' AND NVL(D.MATCH_STATUS_FLAG,''N'') NOT IN (''A'',''T'')))
AND NOT EXISTS
( SELECT ''Cancelled distributions''
FROM AP_SELF_ASSESSED_TAX_DIST_ALL D2
WHERE D2.INVOICE_ID = D.INVOICE_ID
AND D2.CANCELLATION_FLAG = ''Y''
)
UNION ALL
SELECT 1
FROM AP_HOLDS_ALL 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'',''Expired Registration'',''Amount Funded'',''Quantity Funded'',
''GTAS Mandatory Fields'',''GTAS Data Validation'')) OR /* Bug#13464635 */
(H.HOLD_LOOKUP_CODE IN (SELECT HOLD_LOOKUP_CODE
FROM AP_HOLD_CODES
WHERE POSTABLE_FLAG = ''N''
AND USER_RELEASEABLE_FLAG = ''N''))) /* Bug 14579876 */
AND H.RELEASE_LOOKUP_CODE IS NULL
AND EXISTS
( SELECT ''Lines''
FROM AP_INVOICE_LINES_ALL L2
WHERE L2.INVOICE_ID = H.INVOICE_ID ) --8580790,9112369
UNION ALL
SELECT 1
FROM AP_INVOICE_LINES_ALL AIL
WHERE AIL.INVOICE_ID = AI.INVOICE_ID
/* Bug 9777752 AND AI.CANCELLED_DATE IS NULL */
AND NVL(AIL.DISCARDED_FLAG, ''N'') <> ''Y''
AND NVL(AIL.CANCELLED_FLAG, ''N'') <> ''Y''
AND (AIL.AMOUNT <> 0 OR
(AIL.AMOUNT = 0 AND AIL.GENERATE_DISTS = ''Y'')) --8580790
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
)
)
AND NOT EXISTS
( SELECT /*+ no_push_subq */ ''Cancelled distributions''
FROM AP_INVOICE_DISTRIBUTIONS_ALL D3
WHERE D3.INVOICE_ID = AI.INVOICE_ID
AND D3.CANCELLATION_FLAG = ''Y''
) ' ;
l_sql_stmt :=l_sql_stmt || ' FOR UPDATE SKIP LOCKED';
BULK COLLECT INTO l_selected_invoice_ids;
BULK COLLECT INTO l_selected_invoice_ids LIMIT P_transaction_num;
-- Print_Debug (l_api_name,'No. of Invoices selected for Processing: '||l_selected_invoice_ids.count);
AP_Debug_Pkg.Print(g_debug_mode, 'No. of Invoices selected for Processing: '||l_selected_invoice_ids.count );
FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,'No. of Invoices selected for Processing: '||l_selected_invoice_ids.count);
IF l_selected_invoice_ids.count > 0 THEN
--Bug9436217
FORALL k IN 1..l_selected_invoice_ids.COUNT
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;
AP_Debug_Pkg.Print(g_debug_mode, 'No. of Invoices selected for Processing: '||'Calculate Tax' );
delete from zx_errors_gt; --Flusing the GT Table
delete from ap_errors_gt; --Flusing the AP GT table --bug10140354
Update ap_invoices_all set validation_request_id = NULL where invoice_id = l_r_inv_err(i);
FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,' Rows updated ' || sql%rowcount);
OPEN SELECTED_INVOICES_CURSOR ;
FETCH SELECTED_INVOICES_CURSOR
BULK COLLECT INTO l_selected_invoices_cursor ;
FOR i IN 1..l_selected_invoices_cursor.count
--Bug9436217
LOOP
SAVEPOINT AP_APPROVAL_PKG_SP_INV;
mo_global.set_policy_context('S', l_selected_invoices_cursor(i).org_id);
p_invoice_id => l_selected_invoices_cursor(i).invoice_id,
--Bug9436217
p_calling_mode => 'CALCULATE',
p_all_error_messages => 'N',
p_error_code => l_error_code,
p_calling_sequence => l_curr_calling_sequence);
p_invoice_id => l_selected_invoices_cursor(i).invoice_id,
--Bug9436217
p_calling_sequence => l_curr_calling_sequence);
l_invoice_rec.invoice_id := l_selected_invoices_cursor(i).invoice_id;
l_invoice_rec.invoice_date := l_selected_invoices_cursor(i).invoice_date;
l_invoice_rec.invoice_currency_code := l_selected_invoices_cursor(i).invoice_currency_code;
l_invoice_rec.exchange_rate := l_selected_invoices_cursor(i).exchange_rate;
l_invoice_rec.exchange_rate_type := l_selected_invoices_cursor(i).exchange_rate_type;
l_invoice_rec.exchange_date := l_selected_invoices_cursor(i).exchange_date;
l_invoice_rec.vendor_id := l_selected_invoices_cursor(i).vendor_id;
l_invoice_rec.org_id := l_selected_invoices_cursor(i).org_id;
g_org_id := l_selected_invoices_cursor(i).org_id;
p_base_currency_code => AP_APPROVAL_PKG.G_OPTIONS_TABLE(l_selected_invoices_cursor(i).org_id).base_currency_code,
--Bug9436217
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);
p_invoice_id => l_selected_invoices_cursor(i).invoice_id,
--Bug9436217
p_calling_mode => 'DISTRIBUTE',
p_all_error_messages => 'N',
p_error_code => l_error_code,
p_calling_sequence => l_curr_calling_sequence);
IF l_selected_invoices_cursor(i).invoice_type_lookup_code = 'PAYMENT REQUEST' THEN
--Bug9436217
l_calling_mode := 'PAYMENT REQUEST';
AP_Debug_Pkg.Print(g_debug_mode, 'Approving specified invoice : '||l_selected_invoices_cursor(i).invoice_num );
FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,'Approving specified invoice : '||l_selected_invoices_cursor(i).invoice_num);
IF validate_period(l_selected_invoices_cursor(i).invoice_id) THEN
--Bug9436217
-- Cache Templates
Cache_Tolerance_Templates(
--Bug9436217
l_selected_invoices_cursor(i).tolerance_id,
l_selected_invoices_cursor(i).services_tolerance_id,
--Bug9436217
l_calling_sequence);
SELECT COUNT(*)
INTO l_manual_awt_exist
FROM ap_invoice_lines_all ail
WHERE ail.invoice_id = l_selected_invoices_cursor(i).invoice_id
AND ail.line_type_lookup_code = 'AWT'
AND ail.line_source = 'MANUAL LINE ENTRY';
(p_invoice_rec => l_selected_invoices_cursor(i),
p_base_currency_code => AP_APPROVAL_PKG.G_OPTIONS_TABLE(l_selected_invoices_cursor(i).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,
p_calling_mode => l_calling_mode);
l_selected_invoices_cursor(i).invoice_id,
--Bug9436217
'',
'',
'',
'Y',
l_holds_count,
l_approval_status,
l_funds_return_code,
l_calling_mode,
'APXAPRVL',
p_debug_switch
);
fnd_message.set_token('INV_NUM', l_selected_invoices_cursor(i).invoice_num);
fnd_file.put_line (fnd_file.log, l_approval_error || 'Invoice Validation did not process Invoice Number: '|| l_selected_invoices_cursor(i).invoice_num);
l_selected_invoices_cursor(i).invoice_num);
l_selected_invoices_cursor(i).invoice_num);
fnd_file.put_line (fnd_file.log, l_approval_error || 'Invoice Validation did not process Invoice Number: '|| l_selected_invoices_cursor(i).invoice_num);
l_selected_invoices_cursor(i).invoice_num);
l_selected_invoices_cursor(i).invoice_num);
UPDATE ap_invoices_all
SET validation_request_id = NULL
WHERE validation_request_id = AP_APPROVAL_PKG.G_VALIDATION_REQUEST_ID;
CLOSE SELECTED_INVOICES_CURSOR;
SELECT trx_id, trx_line_id, message_text
FROM zx_errors_gt;
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;
delete from zx_errors_gt; --Flusing GT table
delete from ap_errors_gt; --Flusing the AP GT table --bug10140354
Update ap_invoices_all set validation_request_id = NULL where invoice_id = l_r_inv_err(i);
FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,' Rows updated ' || sql%rowcount);
l_selected_invoices_cursor.DELETE ;
OPEN SELECTED_INVOICES_CURSOR ;
FETCH SELECTED_INVOICES_CURSOR
BULK COLLECT INTO l_selected_invoices_cursor ;
FOR i IN 1..l_selected_invoices_cursor.COUNT LOOP
--Bug9436217
SAVEPOINT AP_APPROVAL_PKG_SP_TAX_DIST;
mo_global.set_policy_context('S',l_selected_invoices_cursor(i).org_id);
AP_Debug_Pkg.Print(g_debug_mode, 'Generate Tax Distributions: '||l_selected_invoices_cursor(i).invoice_id );
FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,'Generate Tax Distributions: '||l_selected_invoices_cursor(i).invoice_id);
p_invoice_id => l_selected_invoices_cursor(i).invoice_id,
--Bug9436217
p_calling_mode => 'DISTRIBUTE',
p_all_error_messages => 'N',
p_error_code => l_error_code,
p_calling_sequence => l_curr_calling_sequence);
IF l_selected_invoices_cursor(i).invoice_type_lookup_code = 'PAYMENT REQUEST' THEN
--Bug9436217
l_calling_mode := 'PAYMENT REQUEST';
AP_Debug_Pkg.Print(g_debug_mode, 'Approving specified invoice : '||l_selected_invoices_cursor(i).invoice_num );
FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,'Approving specified invoice : '||l_selected_invoices_cursor(i).invoice_num);
IF validate_period(l_selected_invoices_cursor(i).invoice_id) THEN
--Bug9436217
-- Cache Templates
Cache_Tolerance_Templates(
--Bug9436217
l_selected_invoices_cursor(i).tolerance_id,
l_selected_invoices_cursor(i).services_tolerance_id,
--Bug9436217
l_calling_sequence);
SELECT COUNT(*)
INTO l_manual_awt_exist
FROM ap_invoice_lines_all ail
WHERE ail.invoice_id = l_selected_invoices_cursor(i).invoice_id
AND ail.line_type_lookup_code = 'AWT'
AND ail.line_source = 'MANUAL LINE ENTRY';
(p_invoice_rec => l_selected_invoices_cursor(i),
p_base_currency_code => AP_APPROVAL_PKG.G_OPTIONS_TABLE(l_selected_invoices_cursor(i).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,
p_calling_mode => l_calling_mode);
l_selected_invoices_cursor(i).invoice_id,
--Bug9436217
'',
'',
'',
'Y',
l_holds_count,
l_approval_status,
l_funds_return_code,
l_calling_mode,
'APXAPRVL',
p_debug_switch
);
fnd_message.set_token('INV_NUM', l_selected_invoices_cursor(i).invoice_num);
l_selected_invoices_cursor(i).invoice_num);
l_selected_invoices_cursor(i).invoice_num );
l_selected_invoices_cursor(i).invoice_num);
l_selected_invoices_cursor(i).invoice_num);
l_selected_invoices_cursor(i).invoice_num );
l_selected_invoices_cursor(i).invoice_num);
UPDATE ap_invoices_all
SET validation_request_id = NULL
WHERE validation_request_id = AP_APPROVAL_PKG.G_VALIDATION_REQUEST_ID;
CLOSE SELECTED_INVOICES_CURSOR ;
SELECT trx_id, trx_line_id, message_text
FROM zx_errors_gt;
AP_APPROVAL_PKG.G_SELECTED_INVOICES.DELETE;
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
-- Bug 11830074 start
l_manual_awt_exist:=0;
SELECT COUNT(*)
INTO l_manual_awt_exist
FROM ap_invoice_lines_all ail
WHERE ail.invoice_id = AP_APPROVAL_PKG.G_SELECTED_INVOICES(i).invoice_id
AND ail.line_type_lookup_code = 'AWT'
AND ail.line_source = 'MANUAL LINE ENTRY';
(p_invoice_rec => AP_APPROVAL_PKG.G_SELECTED_INVOICES(i),
p_base_currency_code => AP_APPROVAL_PKG.G_OPTIONS_TABLE(AP_APPROVAL_PKG.G_SELECTED_INVOICES(i).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,
p_calling_mode => l_calling_mode);
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);
FORALL blk_upd IN 1..l_selected_invoice_ids.COUNT
UPDATE /*+ index(a AP_INVOICES_U1) */ ap_invoices_all a --hint added for bug 13429649
SET validation_request_id = NULL
WHERE invoice_id = l_selected_invoice_ids( blk_upd )
AND validation_request_id IS NOT NULL;
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;
SELECT COUNT(1)
INTO l_disc_chrge_line
FROM ap_holds_all aha
WHERE aha.invoice_id = p_invoice_rec.invoice_id
AND aha.hold_lookup_code = 'CANNOT EXECUTE ALLOCATION'
AND aha.release_lookup_code IS NULL
AND NOT EXISTS ( SELECT 1
FROM ap_invoice_lines_all ail, ap_allocation_rules ALR
WHERE ail.invoice_id = p_invoice_rec.invoice_id
AND ail.line_type_lookup_code in ('FREIGHT','MISCELLANEOUS')
AND nvl(ail.discarded_flag,'N') ='N'
AND ALR.invoice_id = AIL.invoice_id
AND ALR.chrg_invoice_line_number = AIL.line_number
AND ALR.status = 'PENDING')
AND ROWNUM = 1;
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;
SELECT rule_type, status
INTO l_allocation_rule_type, l_allocation_status
FROM ap_allocation_rules
WHERE invoice_id = t_inv_lines_table(i).invoice_id
AND chrg_invoice_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';
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',
'PERIOD CLOSED',
'PROJECT GL DATE CLOSED')
AND release_lookup_code IS NULL
AND invoice_id = p_invoice_id;
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 COUNT(*)
INTO l_regenerate_dist
FROM ap_invoice_distributions_all aid
WHERE aid.invoice_id = t_inv_lines_table(i) .invoice_id
AND aid.invoice_line_number = t_inv_lines_table(i) .line_number
AND (NVL(posted_flag , 'N') <> 'N'
OR NVL(encumbered_flag,'N') <>'N');
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 APS.vendor_id
from IBY_EXT_PAYEE_RELATIONSHIPS IEPR,
AP_SUPPLIERS APS
where IEPR.remit_party_id in (select party_id from ap_suppliers
where vendor_id = p_vendor_id)
and IEPR.party_id = APS.party_id
UNION
select APS.vendor_id
from AP_SUPPLIERS APS
where APS.vendor_id = p_vendor_id;
SELECT count(*)
INTO l_chk_encum
FROM ap_holds AH,
ap_hold_codes AHC
WHERE AH.invoice_id = P_invoice_id
AND AH.hold_lookup_code = AHC.hold_lookup_code
AND AH.release_lookup_code IS NULL
AND AHC.postable_flag = 'N'
AND AH.hold_lookup_code IN ('INSUFFICIENT FUNDS',
'CANT FUNDS CHECK',
'Encumbrance Acctg Fail')
AND rownum < 2;
SELECT count(*)
INTO l_check_encumbrance
FROM ap_invoice_distributions_all
WHERE invoice_id = P_invoice_id
AND nvl(encumbered_flag, 'N') NOT IN ('N','R')
AND rownum < 2;
l_debug_info := 'Select invoice type';
SELECT INVOICE_TYPE_LOOKUP_CODE
INTO l_invoce_type
FROM AP_INVOICES
WHERE INVOICE_ID = P_invoice_id;
l_debug_info := 'Update Global attributes on distributions';
UPDATE AP_INVOICE_DISTRIBUTIONS AID
SET Global_Attribute1 =(CASE
WHEN (AID.PO_DISTRIBUTION_ID IS NOT NULL AND
AID. Global_Attribute1 IS NULL)THEN
(SELECT Global_Attribute1
FROM PO_DISTRIBUTIONS_ALL POD
WHERE POD.PO_DISTRIBUTION_ID = AID.PO_DISTRIBUTION_ID)
WHEN (AID. Global_Attribute1 IS NULL) THEN
(SELECT Global_Attribute1
FROM AP_INVOICE_LINES AIL
WHERE AIL.INVOICE_ID = AID.INVOICE_ID
AND AIL.LINE_NUMBER = AID.INVOICE_LINE_NUMBER
AND AIL. Global_Attribute1 IS NOT NULL)
END),
Global_Attribute2=(CASE
WHEN (AID.PO_DISTRIBUTION_ID IS NOT NULL AND
AID.Global_Attribute2 IS NULL)THEN
(SELECT Global_Attribute2
FROM PO_DISTRIBUTIONS_ALL POD
WHERE POD.PO_DISTRIBUTION_ID = AID.PO_DISTRIBUTION_ID)
WHEN (AID. Global_Attribute2 IS NULL) THEN
(SELECT Global_Attribute2
FROM AP_INVOICE_LINES AIL
WHERE AIL.INVOICE_ID = AID.INVOICE_ID
AND AIL.LINE_NUMBER = AID.INVOICE_LINE_NUMBER
AND AIL.Global_Attribute2 IS NOT NULL)
END),
Global_Attribute3=(CASE
WHEN (AID.PO_DISTRIBUTION_ID IS NOT NULL AND
AID. Global_Attribute3 IS NULL)THEN
(SELECT Global_Attribute3
FROM PO_DISTRIBUTIONS_ALL POD
WHERE POD.PO_DISTRIBUTION_ID = AID.PO_DISTRIBUTION_ID)
WHEN (AID. Global_Attribute3 IS NULL) THEN
(SELECT Global_Attribute3
FROM AP_INVOICE_LINES AIL
WHERE AIL.INVOICE_ID = AID.INVOICE_ID
AND AIL.LINE_NUMBER = AID.INVOICE_LINE_NUMBER
AND AIL.Global_Attribute3 IS NOT NULL)
END),
/*Bug#15977829*/
Global_Attribute_Category=(CASE
WHEN (AID.PO_DISTRIBUTION_ID IS NOT NULL AND
AID. Global_Attribute_Category IS NULL)THEN
(SELECT Global_Attribute_Category
FROM PO_DISTRIBUTIONS_ALL POD
WHERE POD.PO_DISTRIBUTION_ID = AID.PO_DISTRIBUTION_ID)
WHEN (AID. Global_Attribute_Category IS NULL) THEN
(SELECT Global_Attribute_Category
FROM AP_INVOICE_LINES AIL
WHERE AIL.INVOICE_ID = AID.INVOICE_ID
AND AIL.LINE_NUMBER = AID.INVOICE_LINE_NUMBER
AND AIL.Global_Attribute_Category IS NOT NULL)
END)
WHERE AID.INVOICE_ID = P_invoice_id
AND (AID.Global_Attribute1 IS NULL OR
AID. Global_Attribute2 IS NULL OR
AID. Global_Attribute3 IS NULL )
AND AID.LINE_TYPE_LOOKUP_CODE IN ('ITEM','FREIGHT','MISCELLANEOUS','ACCRUAL');