[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DECODE(COUNT(*),
0, 'INCONSISTENT PREPAY SUPPL', NULL)
FROM ap_invoices ai
WHERE ai.invoice_num = cv_prepay_num
AND ai.vendor_id = cv_vendor_id;
SELECT DECODE(COUNT(*),
0, 'INCONSISTENT PREPAY CURR', NULL)
FROM ap_invoices ai
WHERE invoice_num = cv_prepay_num
AND vendor_id = cv_vendor_id
AND cv_base_currency_code =
(SELECT base_currency_code
FROM ap_system_parameters)
AND ai.invoice_currency_code = cv_invoice_currency_code
AND ai.payment_currency_code = NVL(cv_payment_currency_code,
cv_invoice_currency_code);
SELECT count(*)
INTO l_count
FROM ap_invoices ai
WHERE ai.invoice_num = p_prepay_num
AND ai.invoice_type_lookup_code = 'PREPAYMENT'
AND ai.payment_status_flag = 'Y'
AND ai.vendor_id = p_vendor_id
AND NVL(ai.earliest_settlement_date,sysdate+1) <= SYSDATE;
SELECT invoice_id
INTO p_prepay_invoice_id
FROM ap_invoices
WHERE invoice_num = p_prepay_num
AND vendor_id = p_vendor_id;
SELECT DECODE(COUNT(*),
0, 'INVALID PREPAY LINE NUM', NULL)
FROM ap_invoices ai,
ap_invoice_lines ail
WHERE ai.invoice_num = cv_prepay_num
AND ail.line_number = cv_prepay_line_num
AND ail.invoice_id = ai.invoice_id
AND ai.invoice_type_lookup_code = 'PREPAYMENT'
AND ai.payment_status_flag = 'Y'
AND ai.vendor_id = cv_vendor_id
AND NVL(ai.earliest_settlement_date,sysdate+1) <= SYSDATE
AND ail.line_type_lookup_code = 'ITEM'
AND NVL(ail.discarded_flag,'N') <> 'Y';
SELECT SUM(NVL(aid.prepay_amount_remaining, aid.total_dist_amount))
FROM ap_invoice_lines_all ail,
ap_invoice_distributions_all aid
WHERE ail.invoice_id = cv_prepay_invoice_id
AND ail.line_number = cv_prepay_line_num
AND NVL(ail.line_selected_for_appl_flag,'N') <> 'Y'
AND aid.invoice_id = ail.invoice_id
AND aid.invoice_line_number = ail.line_number
AND aid.line_type_lookup_code IN ('ITEM', 'ACCRUAL', 'REC_TAX', 'NONREC_TAX')
-- Included tax distributions for inclusive tax if any
AND NVL(aid.reversal_flag,'N') <> 'Y';
SELECT SUM(NVL(aid.prepay_amount_remaining, aid.total_dist_amount))
FROM ap_invoice_lines_all ail,
ap_invoice_distributions_all aid
WHERE ail.invoice_id = cv_prepay_invoice_id
AND ail.line_type_lookup_code <> 'TAX'
AND NVL(ail.line_selected_for_appl_flag,'N') <> 'Y'
AND aid.invoice_id = ail.invoice_id
AND aid.invoice_line_number = ail.line_number
AND aid.line_type_lookup_code IN ('ITEM', 'ACCRUAL', 'REC_TAX', 'NONREC_TAX')
AND NVL(aid.reversal_flag,'N') <> 'Y';
SELECT SUM(NVL(aid.prepay_amount_remaining, aid.total_dist_amount))
FROM ap_invoices ai,
ap_invoice_lines ail,
ap_invoice_distributions aid
WHERE ai.vendor_id = cv_vendor_id
AND ai.invoice_type_lookup_code = 'PREPAYMENT'
AND ai.payment_status_flag = 'Y'
AND NVL(ai.earliest_settlement_date,sysdate+1) <= SYSDATE
AND ail.invoice_id = ai.invoice_id
AND ail.line_type_lookup_code = 'ITEM'
-- this will make sure exclusive TAX lines are not included
AND NVL(ail.discarded_flag,'N') <> 'Y'
AND NVL(ail.line_selected_for_appl_flag,'N') <> 'Y'
AND aid.invoice_id = ail.invoice_id
AND aid.invoice_line_number = ail.line_number
AND aid.line_type_lookup_code IN ('ITEM', 'ACCRUAL', 'REC_TAX', 'NONREC_TAX')
AND NVL(aid.reversal_flag,'N') <> 'Y';
SELECT G.period_name
FROM gl_period_statuses G,
ap_system_parameters P
WHERE G.application_id = 200
AND G.set_of_books_id = P.set_of_books_id
AND TRUNC(cv_gl_date) BETWEEN G.start_date AND
G.end_date
AND G.closing_status IN ('O', 'F')
AND NVL(G.adjustment_period_flag, 'N') = 'N';
SELECT SUM(NVL(aid.amount,0))
INTO l_invoice_amount_remaining
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
--Contract Payments: Although Recoupment is modelled as 'PREPAY' dists
--amount_recouped will effect the invoice_amount_remaining
--regardless of invoice_includes_prepay_flag.
AND ((ail.line_type_lookup_code = 'PREPAY' and
((aid.line_type_lookup_code <> 'PREPAY'
and aid.prepay_distribution_id IS NULL
)
or NVL(ail.invoice_includes_prepay_flag,'N') = 'Y'
)
) OR
(ail.line_type_lookup_code <> 'PREPAY')
);
SELECT SUM(NVL(aid.prepay_amount_remaining, aid.total_dist_amount))
INTO l_prepay_amount_remaining
FROM ap_invoice_lines_all ail,
ap_invoice_distributions_all aid
WHERE ail.invoice_id = p_prepay_invoice_id
AND ail.line_number = p_prepay_line_num
AND ail.line_type_lookup_code = 'ITEM'
AND NVL(ail.discarded_flag,'N') <> 'Y'
AND NVL(ail.line_selected_for_appl_flag,'N') <> 'Y'
AND aid.invoice_id = ail.invoice_id
AND aid.invoice_line_number = ail.line_number
AND aid.line_type_lookup_code
IN ('ITEM','ACCRUAL','REC_TAX','NONREC_TAX')
AND nvl(aid.reversal_flag,'N') <> 'Y';
SELECT SUM(NVL(aid.prepay_amount_remaining, aid.total_dist_amount))
INTO l_prepay_amount_remaining
FROM ap_invoice_lines_all ail,
ap_invoice_distributions_all aid
WHERE ail.invoice_id = p_prepay_invoice_id
AND ail.line_type_lookup_code = 'ITEM'
AND NVL(ail.discarded_flag,'N') <> 'Y'
AND NVL(ail.line_selected_for_appl_flag, 'N') <> 'Y'
AND aid.invoice_id = ail.invoice_id
AND aid.invoice_line_number = ail.line_number
AND aid.line_type_lookup_code
IN ( 'ITEM','ACCRUAL','REC_TAX','NONREC_TAX')
AND NVL(aid.reversal_flag,'N') <> 'Y';
SELECT SUM(NVL(aid.prepay_amount_remaining, aid.total_dist_amount))
INTO l_prepay_amount_remaining
FROM ap_invoices ai,
ap_invoice_lines ail,
ap_invoice_distributions aid
WHERE ai.vendor_id = p_vendor_id
AND ai.invoice_type_lookup_code = 'PREPAYMENT'
AND nvl(ai.earliest_settlement_date,sysdate+1) <= SYSDATE
AND ai.payment_status_flag = 'Y'
AND ail.invoice_id = ai.invoice_id
AND ail.line_type_lookup_code = 'ITEM'
AND NVL(ail.discarded_flag,'N') <> 'Y'
AND NVL(ail.line_selected_for_appl_flag, 'N') <> 'Y'
AND aid.invoice_id = ail.invoice_id
AND aid.invoice_line_number = ail.line_number
AND aid.line_type_lookup_code
IN ( 'ITEM','ACCRUAL','REC_TAX','NONREC_TAX')
AND NVL(aid.reversal_flag,'N') <> 'Y';
PROCEDURE Select_Lines_For_Application (
p_prepay_case_name IN VARCHAR2,
p_prepay_invoice_id IN NUMBER,
p_prepay_line_num IN NUMBER,
p_apply_amount IN NUMBER,
p_vendor_id IN NUMBER,
p_calling_sequence IN VARCHAR2,
p_request_id IN NUMBER,
p_prepay_appl_info OUT NOCOPY ap_prepay_pkg.prepay_appl_tab)
IS
l_application_result BOOLEAN;
SELECT ai.invoice_id,
ail.line_number,
AP_Prepay_Utils_PKG.Get_Line_Prepay_AMT_Remaining
(ail.invoice_id,
ail.line_number)
FROM ap_invoices_all ai,
ap_invoice_lines_all ail
WHERE ai.invoice_id = cv_prepay_invoice_id
AND ail.invoice_id = ai.invoice_id
AND AP_Prepay_Utils_PKG.Get_Line_Prepay_AMT_Remaining
(ail.invoice_id,
ail.line_number) > 0
AND ail.line_type_lookup_code = 'ITEM'
AND NVL(ail.discarded_flag,'N') <> 'Y'
AND NVL(ail.line_selected_for_appl_flag,'N') <> 'Y'
ORDER BY ail.line_number;
SELECT ai.invoice_id,
ail.line_number,
AP_Prepay_Utils_PKG.Get_Line_Prepay_AMT_Remaining
(ail.invoice_id,
ail.line_number)
FROM ap_invoices ai,
ap_invoice_lines ail
WHERE ai.vendor_id = cv_vendor_id
AND ai.invoice_type_lookup_code = 'PREPAYMENT'
AND ai.payment_status_flag = 'Y'
AND NVL(ai.earliest_settlement_date,SYSDATE+1) <= SYSDATE
AND ail.invoice_id = ai.invoice_id
AND AP_Prepay_Utils_PKG.Get_Line_Prepay_AMT_Remaining
(ail.invoice_id,
ail.line_number) > 0
AND ail.line_type_lookup_code = 'ITEM'
AND NVL(ail.discarded_flag,'N') <> 'Y'
AND NVL(ail.line_selected_for_appl_flag,'N') <> 'Y'
ORDER BY ai.gl_date,
ai.invoice_id,
ail.line_number;
p_prepay_appl_info.DELETE;
END Select_Lines_For_Application;
Select_Lines_For_Application (
p_prepay_case_name,
p_prepay_invoice_id,
p_prepay_line_num,
l_apply_amount,
p_vendor_id,
p_calling_sequence,
p_request_id,
p_prepay_appl_info);
p_last_update_login IN NUMBER,
p_calling_sequence IN VARCHAR2,
p_prepay_appl_log OUT NOCOPY ap_prepay_pkg.Prepay_Appl_Log_Tab)
IS
l_prepay_apply_amount NUMBER;
p_last_update_login,
p_calling_sequence,
'PREPAYMENT APPLICATION',
l_error_message) = FALSE ) THEN
p_prepay_appl_log(l_loop_counter).success := 'N';
P_LAST_UPDATE_LOGIN IN NUMBER,
P_CALLING_SEQUENCE IN VARCHAR2,
/*Contract Payments*/
P_CALLING_MODE IN VARCHAR2 DEFAULT 'PREPAYMENT APPLICATION',
P_ERROR_MESSAGE OUT NOCOPY VARCHAR2) RETURN BOOLEAN
IS
l_base_currency_code ap_system_parameters_all.base_currency_code%TYPE;
SELECT base_currency_code
FROM ap_system_parameters;
SELECT batch_id,
invoice_currency_code,
exchange_rate,
exchange_date,
exchange_rate_type,
payment_currency_code,
payment_cross_rate_date,
payment_cross_rate_type
FROM AP_Invoices
WHERE invoice_id = CV_Std_Invoice_ID;
SELECT invoice_currency_code,
exchange_rate,
exchange_date,
exchange_rate_type,
payment_currency_code,
payment_cross_rate_date,
payment_cross_rate_type
FROM AP_Invoices
WHERE invoice_id = CV_PPay_Invoice_ID;
SELECT ail.amount,
NVL(ail.base_amount,0),
/*
Decode(p_calling_mode,'PREPAYMENT APPLICATION',
AP_Prepay_Utils_PKG.get_line_prepay_amt_remaining(
ail.invoice_id,
ail.line_number),
'RECOUPMENT',
AP_Prepay_Utils_Pkg.get_ln_prep_amt_remain_recoup(
ail.invoice_id,
ail.line_number)
),
*/
AP_Prepay_Utils_PKG.get_line_prepay_amt_remaining(
ail.invoice_id,
ail.line_number),
ail.quantity_invoiced,
ail.pa_quantity,
ail.stat_amount,
ail.po_line_location_id,
ail.po_distribution_id,
ail.rcv_transaction_id,
ail.unit_meas_lookup_code,
plt.matching_basis
FROM AP_invoice_lines ail,
po_lines pl, /* Amount Based Matching. PO related tables and conditions */
po_line_locations pll,
po_line_types_b plt --bug 5056269
-- po_line_types_tl T --bug 5119694
WHERE invoice_id = CV_PPAY_Invoice_ID
AND line_number = CV_PPAY_LINE_NUM
AND ail.po_line_location_id = pll.line_location_id(+)
AND pll.po_line_id = pl.po_line_id(+)
AND pl.line_type_id = plt.line_type_id(+);
SELECT AC.currency_code,
AC.exchange_rate_type,
AC.exchange_date,
AC.exchange_rate
FROM AP_checks_all AC,
AP_invoice_payments_all AIP
WHERE AC.check_id = AIP.check_id
AND AIP.invoice_id = CV_PPAY_Invoice_ID
AND NOT EXISTS (SELECT 'Invoice payment has been reversed'
FROM AP_invoice_payments_all AIP2
WHERE AIP2.reversal_inv_pmt_id = AIP.invoice_payment_id
AND AIP2.check_id = AC.check_id);
SELECT invoice_distribution_id,
total_dist_amount,
total_dist_base_amount,
nvl(prepay_amount_remaining,total_dist_amount),
po_distribution_id,
rcv_transaction_id,
quantity_invoiced,
stat_amount,
pa_quantity,
p_gl_date,
p_period_name,
global_attribute_category,
'PREPAY' line_type_lookup_code
FROM ap_invoice_distributions
WHERE invoice_id = CV_PPAY_Invoice_ID
AND invoice_line_number = CV_PPAY_LINE_NUM
AND line_type_lookup_code IN ('ITEM', 'ACCRUAL')
AND NVL(prepay_amount_remaining,total_dist_amount) > 0
--AND NVL(prepay_amount_remaining,0) > 0
AND NVL(reversal_flag,'N') <> 'Y';
SELECT invoice_distribution_id,
total_dist_amount,
total_dist_base_amount,
nvl(prepay_amount_remaining,total_dist_amount),
po_distribution_id,
rcv_transaction_id,
quantity_invoiced,
stat_amount,
pa_quantity,
p_gl_date,
p_period_name,
global_attribute_category,
decode(line_type_lookup_code,'ITEM','PREPAY',
'ACCRUAL','PREPAY',line_type_lookup_code) line_type_lookup_code,
decode(line_type_lookup_code,'NONREC_TAX',charge_applicable_to_dist_id,
'REC_TAX',charge_applicable_to_dist_id,NULL) parent_chrg_appl_to_dist_id,
decode(line_type_lookup_code,'TERV',related_id, 'TIPV', related_id,
'TRV',related_id, NULL) parent_related_id
FROM ap_invoice_distributions
WHERE invoice_id = CV_PPAY_Invoice_ID
AND invoice_line_number = CV_PPAY_LINE_NUM
AND line_type_lookup_code IN ('ITEM','ACCRUAL')
AND NVL(prepay_amount_remaining,total_dist_amount) > 0
AND NVL(reversal_flag,'N') <> 'Y';
l_debug_info := 'Get the Required Line Information for the Selected '||
'Prepayment Invoice Line';
SELECT NVL( ABS(SUM(quantity_invoiced)), 0 ),
NVL( ABS(SUM(stat_amount)), 0 ),
NVL( ABS(SUM(pa_quantity)), 0 )
INTO l_prepay_ln_s_quant_invoiced,
l_prepay_ln_s_stat_amount,
l_prepay_ln_s_pa_quantity
FROM ap_invoice_lines
WHERE prepay_invoice_id = p_prepay_invoice_id
AND prepay_line_number = p_prepay_line_num;
SELECT NVL(MAX (line_number),0) + 1
INTO l_prepay_ln_number
FROM ap_invoice_lines
WHERE invoice_id = p_invoice_id;
l_debug_info := 'Call Ap_Prepay_Pkg.Insert_Prepay_Line';
l_dummy := AP_PREPAY_PKG.INSERT_PREPAY_LINE(
p_prepay_invoice_id,
p_prepay_line_num,
p_invoice_id,
l_prepay_ln_number,
l_apply_amount,
l_prepay_ln_base_amount,
p_gl_date,
p_period_name,
p_prepay_included,
l_prepay_ln_quantity_invoiced,
l_prepay_ln_stat_amount,
l_prepay_ln_pa_quantity,
p_user_id,
p_last_update_login,
p_calling_sequence,
p_error_message);
p_line_number_to_delete => NULL,
p_Interface_Invoice_Id => NULL,
p_all_error_messages => 'N',
p_error_code => p_error_message,
p_calling_sequence => l_current_calling_sequence)) THEN
RAISE tax_exception;
SELECT NVL(included_tax_amount, 0)
INTO l_inclusive_tax_amount
FROM ap_invoice_lines_all
WHERE invoice_id = p_invoice_id
AND line_number = l_prepay_ln_number;
SELECT NVL(MAX(distribution_line_number),0)
INTO l_max_dist_number
FROM ap_invoice_distributions
WHERE invoice_id = p_invoice_id
AND invoice_line_number = l_prepay_ln_number;
SELECT NVL(MAX(distribution_line_number),0)
INTO l_max_dist_number
FROM ap_invoice_distributions
WHERE invoice_id = p_invoice_id
AND invoice_line_number = p_invoice_line_number ;
SELECT NVL( ABS(SUM(quantity_invoiced)), 0 ),
NVL( ABS(SUM(stat_amount)), 0 ),
NVL( ABS(SUM(pa_quantity)), 0 )
INTO l_prepay_dist_s_quant_invoiced,
l_prepay_dist_s_stat_amount,
l_prepay_dist_s_pa_quantity
FROM ap_invoice_distributions
WHERE prepay_distribution_id =
l_prepay_dist_info(l_loop_variable).PREPAY_DISTRIBUTION_ID;
l_debug_info := 'Call Ap_Prepay_Pkg.Insert_Prepay_Dists';
l_dummy := AP_PREPAY_PKG.INSERT_PREPAY_DISTS(
p_prepay_invoice_id,
p_prepay_line_num,
p_invoice_id,
l_std_inv_batch_id,
l_invoice_line_number,
l_prepay_dist_info,
p_user_id,
p_last_update_login,
p_calling_sequence,
p_error_message);
p_line_number_to_delete => NULL,
p_Interface_Invoice_Id => NULL,
p_all_error_messages => 'N',
p_error_code => p_error_message,
p_calling_sequence => l_current_calling_sequence)) THEN
RAISE tax_exception;
l_debug_info := 'Update parent PREPAY line amount if required';
SELECT SUM(NVL(aid.prepay_tax_diff_amount, 0))
INTO l_prepay_tax_diff_amt
FROM ap_invoice_distributions_all aid
WHERE aid.invoice_id = p_invoice_id
AND aid.invoice_line_number = l_prepay_ln_number
AND aid.line_type_lookup_code = 'NONREC_TAX'
AND NVL(aid.reversal_flag,'N') <> 'Y';
l_debug_info := 'Update PREPAY line amount and base amount '||
'including the prepay tax difference';
UPDATE ap_invoice_lines_all ail
SET amount = l_apply_amount,
base_amount = l_prepay_ln_base_amount
WHERE ail.invoice_id = p_invoice_id
AND ail.line_number = l_prepay_ln_number;
l_debug_info := 'Update Prepayment distributions';
l_dummy := AP_PREPAY_PKG.Update_Prepayment(
l_prepay_dist_info,
p_prepay_invoice_id,
p_prepay_line_num,
p_invoice_id,
l_invoice_line_number,
'APPLICATION',
p_calling_mode,
p_calling_sequence,
p_error_message);
l_debug_info := 'Update_PO_Receipt_Info';
l_dummy := AP_PREPAY_PKG.Update_PO_Receipt_Info(
l_prepay_dist_info,
p_prepay_invoice_id,
p_prepay_line_num,
p_invoice_id,
l_invoice_line_number,
l_ppay_ln_po_line_location_id,
l_ppay_ln_uom,
'APPLICATION',
l_ppay_ln_match_basis,
p_calling_sequence,
p_error_message);
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
AND exists( select 1 from ap_invoice_distributions_all aid1
where aid1.invoice_id=p_invoice_id
and aid1.invoice_line_number=l_prepay_ln_number
and aid1.invoice_distribution_id=aid.charge_applicable_to_dist_id);
l_debug_info := 'Update Payment Schedules';
l_dummy := AP_PREPAY_PKG.Update_Payment_Schedule(
p_invoice_id,
p_prepay_invoice_id,
p_prepay_line_num,
l_ppay_apply_amt_in_pay_curr,
'APPLICATION',
l_ppay_inv_pay_curr_code,
p_user_id,
p_last_update_login,
p_calling_sequence,
p_calling_mode,
p_error_message);
||', P_LAST_UPDATE_LOGIN = '||P_LAST_UPDATE_LOGIN);
FUNCTION Insert_Prepay_Line(
p_prepay_invoice_id IN NUMBER,
p_prepay_line_num IN NUMBER,
p_invoice_id IN NUMBER,
p_prepay_line_number IN NUMBER,
p_amount_to_apply IN NUMBER,
p_base_amount_to_apply IN NUMBER,
p_gl_date IN DATE,
p_period_name IN VARCHAR2,
p_prepay_included IN VARCHAR2,
p_quantity_invoiced IN NUMBER,
p_stat_amount IN NUMBER,
p_pa_quantity IN NUMBER,
p_user_id IN NUMBER,
p_last_update_login IN NUMBER,
p_calling_sequence IN VARCHAR2,
p_error_message OUT NOCOPY VARCHAR2)
RETURN BOOLEAN IS
l_debug_info VARCHAR2(100);
l_api_name := 'Insert_Prepay_Line';
l_current_calling_sequence := 'Insert_Prepay_Line<-'
||p_calling_sequence;
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_PREPAY_PKG.Insert_Prepay_Line(+)');
l_debug_info := 'Insert PREPAY Line';
INSERT INTO AP_Invoice_Lines(
INVOICE_ID,
LINE_NUMBER,
LINE_TYPE_LOOKUP_CODE,
REQUESTER_ID,
DESCRIPTION,
LINE_SOURCE,
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,
-- USSGL_TRANSACTION_CODE, - Bug 4277744
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,
LINE_SELECTED_FOR_APPL_FLAG,
PREPAY_APPL_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,
--ETAX: Invwkb
SHIP_TO_LOCATION_ID,
PRIMARY_INTENDED_USE,
PRODUCT_FISC_CLASSIFICATION,
TRX_BUSINESS_CATEGORY,
PRODUCT_TYPE,
PRODUCT_CATEGORY,
USER_DEFINED_FISC_CLASS,
PURCHASING_CATEGORY_ID,
ORG_ID)
SELECT
p_invoice_id,
p_prepay_line_number,
'PREPAY',
NULL,
description,
'PREPAY APPL',
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
'D',
match_type,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
'N',
p_gl_date,
p_period_name,
'N',
NULL,
NULL,
NULL,
NULL,
set_of_books_id,
(-1 * p_amount_to_apply),
(-1 * p_base_amount_to_apply),
rounding_amt,
p_quantity_invoiced,
unit_meas_lookup_code,
unit_price,
'NOT REQUIRED',
-- ussgl_transaction_code, - Bug 4277744
'N',
0,
0,
0,
'N',
income_tax_region,
type_1099,
p_stat_amount,
invoice_id,
line_number,
p_prepay_included,
NULL,
NULL,
po_header_id,
po_line_id,
po_release_id,
po_line_location_id,
po_distribution_id,
rcv_transaction_id,
final_match_flag,
'N',
asset_book_type_code,
asset_category_id,
project_id,
task_id,
expenditure_type,
expenditure_item_date,
expenditure_organization_id,
p_pa_quantity,
NULL,
NULL,
NULL,
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,
SYSDATE,
p_user_id,
p_user_id,
SYSDATE,
p_last_update_login,
program_application_id,
program_id,
program_update_date,
request_id,
'N',
NULL,
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,
--ETAX: Invwkb
ship_to_location_id,
primary_intended_use,
product_fisc_classification,
trx_business_category,
product_type,
product_category,
user_defined_fisc_class,
purchasing_category_id,
org_id
FROM ap_invoice_lines
WHERE invoice_id = p_prepay_invoice_id
AND line_number = p_prepay_line_num;
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_PREPAY_PKG.Insert_Prepay_Line(-)');
||', P_LAST_UPDATE_LOGIN = '||P_LAST_UPDATE_LOGIN);
END Insert_Prepay_Line;
FUNCTION Insert_Prepay_Dists(
P_prepay_invoice_id IN NUMBER,
P_prepay_line_num IN NUMBER,
P_invoice_id IN NUMBER,
P_batch_id IN NUMBER,
P_line_number IN NUMBER,
P_prepay_dist_info IN OUT NOCOPY AP_PREPAY_PKG.Prepay_Dist_Tab_Type,
P_user_id IN NUMBER,
P_last_update_login IN NUMBER,
P_calling_sequence IN VARCHAR2,
P_error_message OUT NOCOPY VARCHAR2)
RETURN BOOLEAN IS
l_loop_counter BINARY_INTEGER;
l_api_name := 'Insert_Prepay_Dists';
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_PREPAY_PKG.Insert_Prepay_Dists(+)');
l_debug_info := 'Insert PREPAY Distributions';
SELECT invoice_includes_prepay_flag
INTO l_invoice_includes_prepay_flag
FROM ap_invoice_lines
WHERE invoice_id=p_invoice_id
AND line_number=p_line_number;
SELECT ap_invoice_distributions_s.NEXTVAL
INTO p_prepay_dist_info(l_loop_counter).invoice_distribution_id
FROM sys.dual; -- Check if it's better to use sequence.CURRVAL instead of dual.
SELECT invoice_distribution_id
INTO p_prepay_dist_info(l_loop_counter).charge_applicable_to_dist_id
FROM ap_invoice_distributions
WHERE invoice_id = p_invoice_id
AND invoice_line_number = p_line_number
AND line_type_lookup_code = 'PREPAY'
and prepay_distribution_id = p_prepay_dist_info(l_loop_counter).parent_chrg_appl_to_dist_id;
SELECT invoice_distribution_id
INTO p_prepay_dist_info(l_loop_counter).related_id
FROM ap_invoice_distributions
WHERE invoice_id = p_invoice_id
AND invoice_line_number = p_line_number
AND line_type_lookup_code in ('NONREC_TAX','REC_TAX')
AND prepay_distribution_id = p_prepay_dist_info(l_loop_counter).parent_related_id;
l_debug_info := 'Insert into ap_invoice_distributions';
INSERT INTO AP_INVOICE_DISTRIBUTIONS
(ACCOUNTING_DATE,
ACCRUAL_POSTED_FLAG,
ASSETS_ADDITION_FLAG,
ASSETS_TRACKING_FLAG,
CASH_POSTED_FLAG,
DISTRIBUTION_LINE_NUMBER,
DIST_CODE_COMBINATION_ID,
INVOICE_ID,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LINE_TYPE_LOOKUP_CODE,
PERIOD_NAME,
SET_OF_BOOKS_ID,
ACCTS_PAY_CODE_COMBINATION_ID,
AMOUNT,
BASE_AMOUNT,
BATCH_ID,
CREATED_BY,
CREATION_DATE,
DESCRIPTION,
FINAL_MATCH_FLAG,
INCOME_TAX_REGION ,
LAST_UPDATE_LOGIN,
MATCH_STATUS_FLAG,
POSTED_FLAG,
PO_DISTRIBUTION_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
QUANTITY_INVOICED,
REQUEST_ID,
REVERSAL_FLAG,
TYPE_1099,
UNIT_PRICE,
ENCUMBERED_FLAG ,
STAT_AMOUNT,
AMOUNT_TO_POST,
ATTRIBUTE1,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE_CATEGORY,
BASE_AMOUNT_TO_POST,
EXPENDITURE_ITEM_DATE,
EXPENDITURE_ORGANIZATION_ID,
EXPENDITURE_TYPE,
PARENT_INVOICE_ID ,
PA_ADDITION_FLAG,
PA_QUANTITY,
POSTED_AMOUNT,
POSTED_BASE_AMOUNT,
PREPAY_AMOUNT_REMAINING,
PROJECT_ID,
TASK_ID ,
-- USSGL_TRANSACTION_CODE, - Bug 4277744
-- USSGL_TRX_CODE_CONTEXT, - Bug 4277744
QUANTITY_VARIANCE ,
BASE_QUANTITY_VARIANCE,
PACKET_ID,
AWT_FLAG,
AWT_GROUP_ID,
--Bug 7277786 added PAY_AWT_GROUP_ID
PAY_AWT_GROUP_ID,
AWT_TAX_RATE_ID,
AWT_GROSS_AMOUNT,
AWT_INVOICE_ID,
AWT_ORIGIN_GROUP_ID,
REFERENCE_1,
REFERENCE_2,
AWT_INVOICE_PAYMENT_ID,
GLOBAL_ATTRIBUTE_CATEGORY,
GLOBAL_ATTRIBUTE1 ,
GLOBAL_ATTRIBUTE2 ,
GLOBAL_ATTRIBUTE3 ,
GLOBAL_ATTRIBUTE4 ,
GLOBAL_ATTRIBUTE5 ,
GLOBAL_ATTRIBUTE6 ,
GLOBAL_ATTRIBUTE7 ,
GLOBAL_ATTRIBUTE8 ,
GLOBAL_ATTRIBUTE9 ,
GLOBAL_ATTRIBUTE10,
GLOBAL_ATTRIBUTE11,
GLOBAL_ATTRIBUTE12,
GLOBAL_ATTRIBUTE13,
GLOBAL_ATTRIBUTE14,
GLOBAL_ATTRIBUTE15,
GLOBAL_ATTRIBUTE16,
GLOBAL_ATTRIBUTE17,
GLOBAL_ATTRIBUTE18,
GLOBAL_ATTRIBUTE19,
GLOBAL_ATTRIBUTE20,
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 ,
AWARD_ID,
CREDIT_CARD_TRX_ID,
DIST_MATCH_TYPE,
RCV_TRANSACTION_ID,
INVOICE_DISTRIBUTION_ID ,
PARENT_REVERSAL_ID,
TAX_RECOVERABLE_FLAG,
TAX_CODE_ID,
MERCHANT_DOCUMENT_NUMBER,
MERCHANT_NAME ,
MERCHANT_REFERENCE,
MERCHANT_TAX_REG_NUMBER,
MERCHANT_TAXPAYER_ID,
COUNTRY_OF_SUPPLY,
MATCHED_UOM_LOOKUP_CODE,
GMS_BURDENABLE_RAW_COST,
ACCOUNTING_EVENT_ID,
PREPAY_DISTRIBUTION_ID,
UPGRADE_POSTED_AMT,
UPGRADE_BASE_POSTED_AMT,
INVENTORY_TRANSFER_STATUS,
COMPANY_PREPAID_INVOICE_ID,
CC_REVERSAL_FLAG,
AWT_WITHHELD_AMT,
PRICE_CORRECT_INV_ID,
PRICE_CORRECT_QTY,
PA_CMT_XFACE_FLAG,
CANCELLATION_FLAG,
INVOICE_LINE_NUMBER,
ROUNDING_AMT,
CHARGE_APPLICABLE_TO_DIST_ID ,
CORRECTED_INVOICE_DIST_ID,
CORRECTED_QUANTITY,
RELATED_ID,
JE_BATCH_ID,
CASH_JE_BATCH_ID ,
INVOICE_PRICE_VARIANCE,
BASE_INVOICE_PRICE_VARIANCE,
PRICE_ADJUSTMENT_FLAG,
PRICE_VAR_CODE_COMBINATION_ID,
RATE_VAR_CODE_COMBINATION_ID,
EXCHANGE_RATE_VARIANCE,
AMOUNT_ENCUMBERED ,
BASE_AMOUNT_ENCUMBERED,
QUANTITY_UNENCUMBERED,
EARLIEST_SETTLEMENT_DATE,
OTHER_INVOICE_ID,
LINE_GROUP_NUMBER ,
REQ_DISTRIBUTION_ID,
PROJECT_ACCOUNTING_CONTEXT,
PA_CC_AR_INVOICE_ID,
PA_CC_AR_INVOICE_LINE_NUM,
PA_CC_PROCESSED_CODE ,
ASSET_BOOK_TYPE_CODE ,
ASSET_CATEGORY_ID ,
DISTRIBUTION_CLASS,
FINAL_PAYMENT_ROUNDING,
AMOUNT_AT_PREPAY_XRATE,
AMOUNT_AT_PREPAY_PAY_XRATE,
--ETAX: Invwkb
INTENDED_USE,
--Freight and Special Charges
rcv_charge_addition_flag,
invoice_includes_prepay_flag, --Bug5224996
org_id)
SELECT
p_prepay_dist_info(l_loop_counter).PREPAY_ACCOUNTING_DATE,
'N',
'U',
ASSETS_TRACKING_FLAG,
'N',
p_prepay_dist_info(l_loop_counter).PREPAY_DIST_LINE_NUMBER,
DIST_CODE_COMBINATION_ID,
p_invoice_id,
p_user_id,
SYSDATE,
p_prepay_dist_info(l_loop_counter).LINE_TYPE_LOOKUP_CODE,
p_prepay_dist_info(l_loop_counter).PREPAY_PERIOD_NAME,
SET_OF_BOOKS_ID,
NULL,
(- 1 * p_prepay_dist_info(l_loop_counter).PREPAY_APPLY_AMOUNT),
(-1 * p_prepay_dist_info(l_loop_counter).PREPAY_BASE_AMOUNT),
p_batch_id,
p_user_id,
SYSDATE,
DESCRIPTION,
NULL,
INCOME_TAX_REGION ,
p_last_update_login,
Null,
'N',
PO_DISTRIBUTION_ID,
program_application_id,
program_id,
SYSDATE,
p_prepay_dist_info(l_loop_counter).PREPAY_QUANTITY_INVOICED,
request_id,
'N',
TYPE_1099,
UNIT_PRICE,
'N' ,
p_prepay_dist_info(l_loop_counter).PREPAY_STAT_AMOUNT,
NULL,
ATTRIBUTE1,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE_CATEGORY,
NULL,
EXPENDITURE_ITEM_DATE,
EXPENDITURE_ORGANIZATION_ID,
EXPENDITURE_TYPE,
NULL,
--bugfix:4924696
DECODE(pa_addition_flag,'E','E','N'),
p_prepay_dist_info(l_loop_counter).PREPAY_PA_QUANTITY,
NULL,
NULL,
NULL,
PROJECT_ID,
TASK_ID ,
-- USSGL_TRANSACTION_CODE, - Bug 4277744
-- USSGL_TRX_CODE_CONTEXT, - Bug 4277744
NULL,
NULL,
NULL,
NULL,
AWT_GROUP_ID,
--Bug 7277786 Added PAY_AWT_GROUP_ID
PAY_AWT_GROUP_ID,
AWT_TAX_RATE_ID,
AWT_GROSS_AMOUNT,
AWT_INVOICE_ID,
AWT_ORIGIN_GROUP_ID,
REFERENCE_1,
REFERENCE_2,
AWT_INVOICE_PAYMENT_ID,
GLOBAL_ATTRIBUTE_CATEGORY,
GLOBAL_ATTRIBUTE1 ,
GLOBAL_ATTRIBUTE2 ,
GLOBAL_ATTRIBUTE3 ,
GLOBAL_ATTRIBUTE4 ,
GLOBAL_ATTRIBUTE5 ,
GLOBAL_ATTRIBUTE6 ,
GLOBAL_ATTRIBUTE7 ,
GLOBAL_ATTRIBUTE8 ,
GLOBAL_ATTRIBUTE9 ,
GLOBAL_ATTRIBUTE10,
GLOBAL_ATTRIBUTE11,
GLOBAL_ATTRIBUTE12,
GLOBAL_ATTRIBUTE13,
GLOBAL_ATTRIBUTE14,
GLOBAL_ATTRIBUTE15,
GLOBAL_ATTRIBUTE16,
GLOBAL_ATTRIBUTE17,
GLOBAL_ATTRIBUTE18,
GLOBAL_ATTRIBUTE19,
GLOBAL_ATTRIBUTE20,
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 ,
AWARD_ID,
CREDIT_CARD_TRX_ID,
DIST_MATCH_TYPE,
RCV_TRANSACTION_ID,
p_prepay_dist_info(l_loop_counter).INVOICE_DISTRIBUTION_ID,
NULL,
TAX_RECOVERABLE_FLAG,
TAX_CODE_ID,
MERCHANT_DOCUMENT_NUMBER,
MERCHANT_NAME ,
MERCHANT_REFERENCE,
MERCHANT_TAX_REG_NUMBER,
MERCHANT_TAXPAYER_ID,
COUNTRY_OF_SUPPLY,
MATCHED_UOM_LOOKUP_CODE,
NULL,
NULL,
p_prepay_dist_info(l_loop_counter).PREPAY_DISTRIBUTION_ID,
NULL,
NULL,
'N',
COMPANY_PREPAID_INVOICE_ID,
CC_REVERSAL_FLAG,
NULL,
PRICE_CORRECT_INV_ID,
PRICE_CORRECT_QTY,
PA_CMT_XFACE_FLAG,
'N',
p_line_number,
ROUNDING_AMT,
p_prepay_dist_info(l_loop_counter).charge_applicable_to_dist_id,
NULL,
NULL,
p_prepay_dist_info(l_loop_counter).related_id,
NULL,
NULL,
INVOICE_PRICE_VARIANCE,
BASE_INVOICE_PRICE_VARIANCE,
PRICE_ADJUSTMENT_FLAG,
PRICE_VAR_CODE_COMBINATION_ID,
RATE_VAR_CODE_COMBINATION_ID,
EXCHANGE_RATE_VARIANCE,
AMOUNT_ENCUMBERED ,
BASE_AMOUNT_ENCUMBERED,
QUANTITY_UNENCUMBERED,
EARLIEST_SETTLEMENT_DATE,
NULL,
LINE_GROUP_NUMBER ,
REQ_DISTRIBUTION_ID,
PROJECT_ACCOUNTING_CONTEXT,
NULL,
NULL,
NULL,
ASSET_BOOK_TYPE_CODE ,
ASSET_CATEGORY_ID ,
'PERMANENT',
NULL,
(-1 * p_prepay_dist_info(l_loop_counter).PREPAY_BASE_AMT_PPAY_XRATE),
(-1 * p_prepay_dist_info(l_loop_counter).PREPAY_BASE_AMT_PPAY_PAY_XRATE),
--ETAX: Invwkb
INTENDED_USE,
'N',
l_invoice_includes_prepay_flag, --Bug5224996
ORG_ID
FROM ap_invoice_distributions
WHERE invoice_distribution_id = p_prepay_dist_info(l_loop_counter).PREPAY_DISTRIBUTION_ID;
p_last_update_login => p_last_update_login,
p_calling_sequence => p_calling_sequence );
l_debug_info := 'Update global context code';
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_PREPAY_PKG.Insert_Prepay_Dists(-)');
||', P_LAST_UPDATE_LOGIN = '||P_LAST_UPDATE_LOGIN);
END Insert_Prepay_Dists;
FUNCTION Update_Prepayment(
p_prepay_dist_info IN AP_PREPAY_PKG.Prepay_Dist_Tab_Type,
p_prepay_invoice_id IN NUMBER,
p_prepay_line_num IN NUMBER,
p_invoice_id IN NUMBER,
p_invoice_line_num IN NUMBER,
p_appl_type IN VARCHAR2,
p_calling_mode IN VARCHAR2 DEFAULT 'PREPAYMENT APPLICATION',
p_calling_sequence IN VARCHAR2,
P_error_message OUT NOCOPY VARCHAR2)
RETURN BOOLEAN IS
l_current_calling_sequence VARCHAR2(2000);
CURSOR C_Update_Prepayments IS
SELECT prepay_distribution_id,
ABS(amount)
FROM ap_invoice_distributions_all
WHERE invoice_id = p_invoice_id
AND invoice_line_number = p_invoice_line_num
AND NVL(reversal_flag,'N') = 'Y'
AND parent_reversal_id IS NOT NULL;
l_api_name := 'Update_Prepayment';
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_PREPAY_PKG.Update_Prepayment(+)');
l_current_calling_sequence := 'update_prepayment<-' ||p_calling_sequence;
l_debug_info := 'Update Prepayment Info';
UPDATE ap_invoice_distributions
SET prepay_amount_remaining =
NVL(prepay_amount_remaining, total_dist_amount) -
p_prepay_dist_info(l_loop_counter).PREPAY_APPLY_AMOUNT
WHERE invoice_distribution_id =
p_prepay_dist_info(l_loop_counter).PREPAY_DISTRIBUTION_ID;
l_debug_info := 'Update prepayment_amt_remaining: '|| ' Invoice ID: '||p_prepay_invoice_id
|| ' Line Num: '||p_prepay_line_num;
UPDATE ap_invoice_distributions_all dp
SET dp.prepay_amount_remaining =
(SELECT SUM(NVL(dp.prepay_amount_remaining, dp.total_dist_amount) +
(NVL(ds.amount, 0) + NVL(ds.prepay_tax_diff_amount, 0)))
FROM ap_invoice_distributions_all ds
WHERE ds.prepay_distribution_id = dp.invoice_distribution_id)
WHERE dp.invoice_id = p_prepay_invoice_id
AND dp.line_type_lookup_code IN ('REC_TAX','NONREC_TAX')
AND exists (select 'Exclusive Prepay Tax Line'
from ap_allocation_rule_lines arl
where arl.invoice_id = p_prepay_invoice_id
and arl.to_invoice_line_number = p_prepay_line_num
and arl.chrg_invoice_line_number = dp.invoice_line_number);
UPDATE ap_invoice_distributions_all dp
SET dp.prepay_amount_remaining = NVL(dp.prepay_amount_remaining, dp.total_dist_amount) + /*Bug 7372625*/
(SELECT SUM(/*NVL(dp.prepay_amount_remaining, dp.total_dist_amount) +*/
(NVL(ds.amount, 0) + NVL(ds.prepay_tax_diff_amount, 0)))
FROM ap_invoice_distributions_all ds
WHERE ds.prepay_distribution_id = dp.invoice_distribution_id)
WHERE dp.invoice_id = p_prepay_invoice_id
AND dp.invoice_line_number = p_prepay_line_num
AND dp.line_type_lookup_code IN ('REC_TAX','NONREC_TAX');
l_debug_info := 'Open Cursor C_Update_Prepayments';
OPEN C_Update_Prepayments;
l_debug_info := 'Fetch C_Update_Prepayments';
FETCH C_Update_Prepayments INTO
l_prepay_distribution_id,
l_apply_amount;
EXIT WHEN C_Update_Prepayments%NOTFOUND;
UPDATE ap_invoice_distributions
SET prepay_amount_remaining = prepay_amount_remaining +
l_apply_amount
WHERE invoice_distribution_id = l_prepay_distribution_id;
CLOSE C_Update_Prepayments;
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_PREPAY_PKG.Update_Prepayment(-)');
END Update_Prepayment;
FUNCTION Update_PO_Receipt_Info(
p_prepay_dist_info IN AP_PREPAY_PKG.Prepay_Dist_Tab_Type,
p_prepay_invoice_id IN NUMBER,
p_prepay_line_num IN NUMBER,
p_invoice_id IN NUMBER,
p_invoice_line_num IN NUMBER,
p_po_line_location_id IN NUMBER,
p_matched_UOM_lookup_code IN VARCHAR2,
p_appl_type IN VARCHAR2,
p_match_basis IN VARCHAR2,
p_calling_sequence IN VARCHAR2,
p_error_message OUT NOCOPY VARCHAR2)
RETURN BOOLEAN IS
l_current_calling_sequence VARCHAR2(2000);
CURSOR C_PO_Receipt_Update IS
SELECT ail.po_line_location_id,
ail.unit_meas_lookup_code,
aid.rcv_transaction_id,
aid.po_distribution_id,
aid.amount,
aid.quantity_invoiced,
plt.matching_basis
FROM ap_invoice_distributions aid,
ap_invoice_lines ail,
po_lines pl, /* Amount Based Matching. PO related tables and conditions */
po_line_locations pll,
po_line_types_b plt, --bug 5056269
po_line_types_tl T
WHERE aid.invoice_id = ail.invoice_id
AND aid.invoice_line_number = ail.line_number
AND aid.invoice_id = p_invoice_id
AND aid.invoice_line_number = p_invoice_line_num
AND NVL(aid.reversal_flag,'N') = 'Y'
AND aid.parent_invoice_id IS NOT NULL
AND ail.po_line_location_id = pll.line_location_id(+)
and pll.po_line_id = pl.po_line_id(+)
and pl.line_type_id = plt.line_type_id(+)
and plt.LINE_TYPE_ID = T.LINE_TYPE_ID
and T.LANGUAGE = userenv('LANG');
l_api_name := 'Update_Po_Receipt_Info';
l_current_calling_sequence := 'update_po_receipt_info<-'||
p_calling_sequence;
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_PREPAY_PKG.Update_PO_Receipt_Info(+)');
--to update the recouped_amounts as oppose to billed columns...
l_po_ap_dist_rec.add_change(
p_po_distribution_id => p_prepay_dist_info(l_loop_counter).ppay_po_distribution_id,
p_uom_code => p_matched_uom_lookup_code,
p_quantity_billed => NULL,
p_amount_billed => NULL,
p_quantity_financed => NULL,
p_amount_financed => NULL,
p_quantity_recouped => (-1) *p_prepay_dist_info(l_loop_counter).prepay_quantity_invoiced ,
p_amount_recouped => p_prepay_dist_info(l_loop_counter).prepay_apply_amount,
p_retainage_withheld_amt => NULL,
p_retainage_released_amt => NULL);
RCV_BILL_UPDATING_SV.ap_update_rcv_transactions(
p_prepay_dist_info(l_loop_counter).ppay_rcv_transaction_id,
p_prepay_dist_info(l_loop_counter).prepay_quantity_invoiced,
p_matched_UOM_lookup_code,
(-1) * p_prepay_dist_info(l_loop_counter).prepay_apply_amount,
p_match_basis);
p_last_update_login => NULL,
p_request_id => NULL
);
OPEN C_PO_Receipt_Update;
FETCH C_PO_Receipt_Update INTO
l_po_line_location_id,
l_unit_meas_lookup_code,
l_rcv_transaction_id,
l_po_distribution_id,
l_apply_amount,
l_quantity_invoiced,
l_match_basis
;
EXIT WHEN C_PO_Receipt_Update%NOTFOUND;
RCV_BILL_UPDATING_SV.ap_update_rcv_transactions(
l_rcv_transaction_id,
l_quantity_invoiced,
l_unit_meas_lookup_code,
l_apply_amount,
l_match_basis);
CLOSE C_PO_Receipt_Update;
p_last_update_login => NULL,
p_request_id => NULL
);
l_debug_info := 'Call the PO_AP_INVOICE_MATCH_GRP to update the Po Distributions and Po Line Locations';
PO_AP_INVOICE_MATCH_GRP.Update_Document_Ap_Values(
P_Api_Version => 1.0,
P_Line_Loc_Changes_Rec => l_po_ap_line_loc_rec,
P_Dist_Changes_Rec => l_po_ap_dist_rec,
X_Return_Status => l_return_status,
X_Msg_Data => l_msg_data);
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_PREPAY_PKG.Update_PO_Receipt_Info(-)');
END Update_PO_Receipt_Info;
FUNCTION Update_Payment_Schedule(
p_invoice_id IN NUMBER,
p_prepay_invoice_id IN NUMBER,
p_prepay_line_num IN NUMBER,
p_apply_amount IN NUMBER,
p_appl_type 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_calling_mode IN VARCHAR2 DEFAULT NULL,
p_error_message OUT NOCOPY VARCHAR2)
RETURN BOOLEAN IS
l_debug_info VARCHAR2(100);
SELECT payment_num,
DECODE(p_appl_type,
'UNAPPLICATION', gross_amount - amount_remaining,
amount_remaining)
FROM ap_payment_schedules
WHERE invoice_id = p_invoice_id
AND (payment_status_flag||'' = 'P'
OR payment_status_flag||'' = DECODE(p_appl_type, 'UNAPPLICATION', 'Y', 'N'))
ORDER BY DECODE(p_appl_type,
'UNAPPLICATION', DECODE(payment_status_flag,'P',1,'Y',2,3),
DECODE(NVL(hold_flag,'N'),'N',1,2)),
DECODE(p_appl_type,
'UNAPPLICATION', due_date,
NULL) DESC,
DECODE(p_appl_type,
'APPLICATION', due_date,
NULL),
DECODE(p_appl_type,
'UNAPPLICATION', DECODE(hold_flag,'N',1,'Y',2,3),
DECODE(NVL(payment_status_flag,'N'),'P',1,'N',2,3));
l_api_name := 'Update_Payment_Schedule';
l_current_calling_sequence := 'update_payment_schedule<-'||
p_calling_sequence;
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_PREPAY_PKG.Update_Payment_Schedule(+)');
* 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_appl_type,
'APPLICATION',0,
gross_amount),
payment_status_flag = DECODE(p_appl_type,
'APPLICATION','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;
l_debug_info := 'Update ap_invoices to reflect the amount applied p_apply_amount, p_invoice_id '||p_apply_amount||','||p_invoice_id;
UPDATE ap_invoices
SET amount_paid = nvl(amount_paid,0) + p_apply_amount ,
payment_status_flag =
AP_INVOICES_UTILITY_PKG.get_payment_status(p_invoice_id ),
last_update_date = SYSDATE,
last_updated_by = P_user_id,
last_update_login = p_last_update_login
WHERE invoice_id = p_invoice_id;
SELECT amount_paid
into l_amount_paid
from ap_invoices
where invoice_id=p_invoice_id;
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_PREPAY_PKG.Update_Payment_Schedule(-)');
||' LAST_UPDATE_LOGIN = '||TO_CHAR(p_last_update_login)
||' PAYMENT_CURRENCY_CODE = '||p_payment_currency_code);
END update_payment_schedule;
P_last_update_login IN NUMBER,
P_calling_sequence IN VARCHAR2,
P_error_message OUT NOCOPY VARCHAR2)
RETURN BOOLEAN
IS
-- Prepayment Invoice Related Variables
l_ppay_inv_curr_code ap_invoices_all.invoice_currency_code%TYPE;
SELECT invoice_currency_code,
payment_currency_code,
payment_cross_rate_date,
payment_cross_rate_type
FROM AP_Invoices
WHERE invoice_id = CV_PPay_Invoice_ID;
SELECT *
FROM AP_INVOICE_LINES
WHERE invoice_id = CV_Invoice_ID
AND line_number = CV_line_num;
P_last_updated_by => p_user_id,
P_last_update_login => p_last_update_login,
P_error_code => l_error_code,
P_Token => l_token,
P_calling_sequence => l_current_calling_sequence);
p_line_number_to_delete => NULL,
P_Interface_Invoice_Id => NULL,
p_all_error_messages => 'N',
p_error_code => p_error_message,
p_calling_sequence => l_current_calling_sequence)) THEN
RAISE tax_exception;
p_line_number_to_delete => NULL,
P_Interface_Invoice_Id => NULL,
p_all_error_messages => 'N',
p_error_code => p_error_message,
p_calling_sequence => l_current_calling_sequence)) THEN
RAISE tax_exception;
l_dummy := AP_PREPAY_PKG.Update_Prepayment(
l_prepay_dist_info,
p_prepay_invoice_id,
p_prepay_line_num,
p_invoice_id,
p_line_num,
'UNAPPLICATION',
NULL, --p_calling_mode
p_calling_sequence,
p_error_message);
l_dummy := AP_PREPAY_PKG.Update_PO_Receipt_Info(
l_prepay_dist_info,
p_prepay_invoice_id,
p_prepay_line_num,
p_invoice_id,
p_line_num,
NULL,
NULL,
'UNAPPLICATION',
NULL,
p_calling_sequence,
p_error_message);
l_dummy := AP_PREPAY_PKG.Update_Payment_Schedule (
p_invoice_id,
p_prepay_invoice_id,
p_prepay_line_num,
l_ppay_apply_amt_in_pay_curr,
'UNAPPLICATION',
l_ppay_inv_pay_curr_code,
p_user_id,
p_last_update_login,
p_calling_sequence,
NULL,
p_error_message);
||', P_LAST_UPDATE_LOGIN = '||P_LAST_UPDATE_LOGIN);
p_last_update_login IN NUMBER,
p_calling_sequence IN VARCHAR2,
p_error_message OUT NOCOPY VARCHAR2)
RETURN BOOLEAN IS
l_prepay_dist_info AP_PREPAY_PKG.PREPAY_DIST_TAB_TYPE;
SELECT ai.invoice_id
FROM ap_invoices ai
WHERE ai.vendor_id = cv_vendor_id
AND ai.invoice_num = cv_prepay_num
AND ai.invoice_type_lookup_code = 'PREPAYMENT'
AND ai.payment_status_flag = 'Y'
AND NVL(ai.earliest_settlement_date,sysdate+1) <= SYSDATE;
SELECT ai.invoice_num,
ail.line_number,
AP_Prepay_Utils_PKG.get_line_prepay_amt_remaining
(ail.invoice_id,
ail.line_number)
FROM ap_invoices ai,
ap_invoice_lines ail
WHERE ai.invoice_id = cv_prepay_invoice_id
AND ail.invoice_id = ai.invoice_id
AND AP_Prepay_Utils_PKG.get_line_prepay_amt_remaining
(ail.invoice_id,
ail.line_number) > 0
AND ail.line_type_lookup_code = 'ITEM'
AND NVL(ail.discarded_flag,'N') <> 'Y'
ORDER BY ail.line_number;
p_last_update_login,
l_current_calling_sequence,
'PREPAYMENT APPLICATION',
p_error_message);
||', P_LAST_UPDATE_LOGIN = '||P_LAST_UPDATE_LOGIN);