[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(amount,0))
into l_invoice_amount_remaining
from ap_invoice_lines_all
where invoice_id = p_invoice_id
and 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_invoice_id IN NUMBER, -- Bug 6394865
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'
AND ai.invoice_currency_code = cv_invoice_currency_code -- Bug 6394865
AND ai.payment_currency_code = cv_payment_currency_code -- Bug 6394865
ORDER BY ai.gl_date,
ai.invoice_id,
ail.line_number;
p_prepay_appl_info.DELETE;
select invoice_currency_code, payment_currency_code
into l_invoice_currency_code, l_payment_currency_code
from ap_invoices where invoice_id = p_invoice_id; -- Bug 6394865
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_import_invoice_id, -- Bug 6394865
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 AIP.reversal_inv_pmt_id IS NULL -- bug8971713
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'
ORDER BY nvl(prepay_amount_remaining,total_dist_amount); -- 7834255
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'
ORDER BY nvl(prepay_amount_remaining,total_dist_amount); -- 7834255
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. p_prepay_invoice_id: '||
p_prepay_invoice_id|| ',p_prepay_line_num: '||p_prepay_line_num||',p_invoice_id: '||
p_invoice_id||',l_prepay_ln_number: '||l_prepay_ln_number;
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_invoice_line_number: '||
l_invoice_line_number;
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(4000); --Changed length from 100 to 4000 (8534097)
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,
PAY_AWT_GROUP_ID) --Bug 9058369
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',
trunc(p_gl_date),--8532204
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,
pay_awt_group_id --Bug 9058369
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,
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,
pay_awt_group_id) --Bug 9058369
SELECT
trunc(p_prepay_dist_info(l_loop_counter).PREPAY_ACCOUNTING_DATE), --8532204
'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,
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,
PAY_AWT_GROUP_ID --Bug 9058369
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);
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_all dp
SET dp.prepay_amount_remaining = dp.total_dist_amount +
(SELECT SUM(
(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),
dp.last_updated_by = FND_GLOBAL.user_id, /*Bug10101705: Added who columns*/
dp.last_update_date = SYSDATE,/*Bug10101705: Added who columns*/
dp.last_update_login = FND_GLOBAL.login_id /*Bug10101705: Added who columns*/
WHERE dp.invoice_id = p_prepay_invoice_id
AND (dp.invoice_line_number = p_prepay_line_num
OR 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));
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(4000); --Changed length from 100 to 4000 (8534097)
SELECT aps.payment_num,
DECODE(p_appl_type, 'UNAPPLICATION', ((aps.gross_amount / ai.invoice_amount ) *
(ai.invoice_amount - nvl(ap_invoices_pkg.get_amount_withheld(p_invoice_id),0)) - aps.amount_remaining),/*Bug10012482*/
aps.amount_remaining),
ai.invoice_amount /*Bug10012482*/
FROM ap_payment_schedules aps,
ap_invoices_all ai
WHERE ai.invoice_id = aps.invoice_id
AND ai.invoice_id = p_invoice_id
AND (aps.payment_status_flag||'' = 'P'
OR aps.payment_status_flag||'' = DECODE(p_appl_type, 'UNAPPLICATION', 'Y', 'N'))
ORDER BY DECODE(p_appl_type,
'UNAPPLICATION', DECODE(aps.payment_status_flag,'P',1,'Y',2,3),
DECODE(NVL(aps.hold_flag,'N'),'N',1,2)),
DECODE(p_appl_type,
'UNAPPLICATION', aps.due_date,
NULL) DESC,
DECODE(p_appl_type,
'APPLICATION', aps.due_date,
NULL),
DECODE(p_appl_type,
'UNAPPLICATION', DECODE(aps.hold_flag,'N',1,'Y',2,3),
DECODE(NVL(aps.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(+)');
l_debug_info := 'Update ap_payment_schedule for the recoupments';
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',
/*Bug10012482:Taking AWT amounts into account*/
((gross_amount / l_total_gross_amount) * (l_total_gross_amount -
nvl(ap_invoices_pkg.get_amount_withheld(p_invoice_id),0))), '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 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',
/*Bug10012482:Taking AWT amounts into account*/
((gross_amount / l_total_gross_amount) * (l_total_gross_amount -
nvl(ap_invoices_pkg.get_amount_withheld(p_invoice_id),0))), '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,
/*Bug10012482:Taking AWT amounts into account*/
((gross_amount / l_total_gross_amount )
* (l_total_gross_amount -
nvl(ap_invoices_pkg.get_amount_withheld(p_invoice_id),0)))),
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;
/*Bug 9643901: Moved the unused select to fetch amount paid, from after update to before
and used it to check that amount paid does not go negative in case of unapplication.
Similar check is present for amount remaining update on payment schedule above.*/
SELECT nvl(amount_paid,0)
into l_amount_paid
from ap_invoices
where invoice_id=p_invoice_id;
UPDATE ap_invoices
SET amount_paid = l_amount_paid , /*Bug 9643901: replaced with local variable as set above*/
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;
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);