[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE no_action_pmt_event_update
( p_check_id IN NUMBER,
p_event_type_code IN VARCHAR2,
p_accounting_date IN DATE,
p_accounting_event_id IN NUMBER,
p_calling_sequence IN VARCHAR2
);
PROCEDURE Insert_Prepayment_Header
( p_invoice_id IN NUMBER,
p_invoice_line_number IN NUMBER,
p_accounting_event_id IN NUMBER,
p_accounting_date IN DATE,
p_invoice_adjustment_id IN NUMBER,
p_calling_sequence IN VARCHAR2
);
PROCEDURE Update_Prepayment_Header
( p_invoice_id IN NUMBER,
p_invoice_line_number IN NUMBER,
p_accounting_event_id IN NUMBER,
p_accounting_date IN DATE,
p_transaction_type IN VARCHAR2,
p_calling_sequence IN VARCHAR2
);
l_log_msg := 'comment out procedure no_action_pmt_event_update';
no_action_pmt_event_update(p_check_id => p_doc_id,
p_event_type_code => l_event_type,
p_accounting_date => p_accounting_date,
p_accounting_event_id => p_accounting_event_id,
p_calling_sequence => l_curr_calling_sequence);
l_log_msg := 'After calling procedure no_action_pmt_event_update';
l_log_msg := 'comment out calling procedure no_action_pmt_event_update';
no_action_pmt_event_update
( p_check_id => p_doc_id,
p_event_type_code => PAYMENT_UNCLEARED_TYPE,
p_accounting_date => p_accounting_date,
p_accounting_event_id => p_accounting_event_id,
p_calling_sequence => l_curr_calling_sequence
);
l_log_msg := 'After calling procedure no_action_pmt_event_update';
l_log_msg := 'comment out procedure no_action_pmt_event_update';
no_action_pmt_event_update
( p_check_id => p_doc_id,
p_event_type_code => PAYMENT_MATURITY_REVERSED_TYPE,
p_accounting_date => p_accounting_date,
p_accounting_event_id => p_accounting_event_id,
p_calling_sequence => l_curr_calling_sequence
);
l_log_msg := 'After Calling procedure no_pmt_event_update';
SELECT accounting_date accounting_date,
sum(amount) dist_amount,
decode (line_type_lookup_code, 'PREPAY','PREPAY','OTHER') dist_type,
-- decode (line_type_lookup_code, 'PREPAY',
-- invoice_distribution_id, -1) invoice_distribution_id,
-- Bug 6931461: invoice_line_number invoice_line_number
decode(line_type_lookup_code, 'PREPAY', invoice_line_number, 1) invoice_line_number
FROM ap_invoice_distributions AID, financials_system_parameters FSP
WHERE AID.invoice_id = P_invoice_id
AND AID.awt_invoice_payment_id is NULL
AND AID.org_id = FSP.org_id -- Bug 4516136
AND AID.set_of_books_id = FSP.set_of_books_id -- Bug 5608968 Avoid full index scan of fsp
AND (AID.prepay_distribution_id IS NULL -- prepay_tax_parent_id obsoleted
OR AID.charge_Applicable_to_dist_id is NULL) --Added for bug 4643339
AND AID.accounting_event_id is NULL
AND NVL(AID.cancellation_flag, 'N') = 'N' -- replaced cancellation_date
AND (
(nvl(FSP.purch_encumbrance_flag,'N') = 'N'
AND match_Status_flag IN ('T','A')
)
OR
((nvl(FSP.purch_encumbrance_flag,'N') = 'Y'
AND match_Status_flag = 'A')))
-- since 'OTHER' comes before 'PREPAY' alphabetically, a prepayment
-- event will not be created first
GROUP BY accounting_date,
decode (line_type_lookup_code, 'PREPAY','PREPAY','OTHER'),
-- Bug 6718967. Fix to create two events for prepayment applied
-- and unapplied.
decode (line_type_lookup_code, 'PREPAY',
decode(nvl(parent_reversal_id,-99), -99, 1, 2), 3),
-- decode (line_type_lookup_code, 'PREPAY',
-- invoice_distribution_id, -1),
decode(line_type_lookup_code, 'PREPAY', invoice_line_number, 1) --Bug 6931461
ORDER BY dist_type, accounting_date;
SELECT APPH.accounting_event_id
FROM AP_PREPAY_HISTORY_ALL APPH, AP_INVOICE_DISTRIBUTIONS AID
WHERE APPH.related_prepay_app_Event_id = l_prepay_app_event_id
AND APPH.invoice_adjustment_event_id = AID.accounting_event_id
AND nvl(APPH.posted_flag,'N') = 'N'
AND AID.accounting_date = l_accounting_date;
SELECT COUNT(distinct(accounting_event_id))
INTO l_event_num
FROM ap_invoice_distributions
WHERE invoice_id = p_invoice_id;
SELECT distinct(AID.accounting_event_id)
INTO l_accounting_event_id
FROM ap_invoice_distributions AID,
ap_invoice_lines AIL
WHERE AID.accounting_date = event_dist_rec.accounting_date
AND AIL.invoice_id = P_invoice_id
AND AIL.line_number = event_dist_rec.invoice_line_number
AND AIL.invoice_id = AID.invoice_id
AND AIL.line_number = AID.invoice_line_number
AND AID.accounting_date = AIL.accounting_date
AND AID.line_type_lookup_code = 'PREPAY'
AND nvl(posted_flag,'N') = 'N'
AND sign(AID.amount) = sign(event_dist_rec.dist_amount)
AND AID.accounting_event_id IS NOT NULL;
Update_Prepayment_Header
(p_invoice_id ,
p_invoice_line_number => event_dist_rec.invoice_line_number,
p_accounting_event_id => l_accounting_event_id,
p_accounting_date => event_dist_rec.accounting_date,
p_transaction_type => l_event_type,
p_calling_sequence => l_curr_calling_sequence);
l_log_msg := 'After calling procedure update_prepayment_header';
SELECT MAX(AID2.accounting_event_id)
INTO l_prepay_app_event_id
FROM AP_INVOICE_DISTRIBUTIONS AID, AP_INVOICE_DISTRIBUTIONS AID2
WHERE AID.invoice_id = p_invoice_id
AND AID.invoice_line_number = event_dist_rec.invoice_line_number
AND AID.parent_reversal_id = AID2.invoice_distribution_id
AND AID.accounting_date = AID2.accounting_date
AND nvl(AID2.posted_flag,'N') = 'N';
l_log_msg := 'After calling procedure get_insert_info executed';
l_log_msg := 'Before calling AP_XLA_EVENTS_PKG.UPDATE_EVENT';
AP_XLA_EVENTS_PKG.UPDATE_EVENT
(p_event_source_info => l_event_source_info,
p_event_id => l_Accounting_Event_id,
p_event_type_code => 'PREPAYMENT UNAPPLIED',
p_event_date => NULL,
p_event_status_code => XLA_EVENTS_PUB_PKG.C_EVENT_NOACTION,
p_valuation_method => NULL,
p_security_context => l_event_security_context,
p_calling_sequence => l_curr_calling_sequence);
l_log_msg := 'After calling AP_XLA_EVENTS_PKG.UPDATE_EVENT';
l_log_msg := 'Before calling AP_XLA_EVENTS_PKG.UPDATE_EVENT';
AP_XLA_EVENTS_PKG.UPDATE_EVENT
(p_event_source_info => l_event_source_info,
p_event_id => l_prepay_app_event_id,
p_event_type_code => 'PREPAYMENT APPLIED',
p_event_date => NULL,
p_event_status_code => XLA_EVENTS_PUB_PKG.C_EVENT_NOACTION,
p_valuation_method => NULL,
p_security_context => l_event_security_context,
p_calling_sequence => l_curr_calling_sequence);
l_log_msg := 'After calling AP_XLA_EVENTS_PKG.UPDATE_EVENT';
AP_XLA_EVENTS_PKG.UPDATE_EVENT
(p_event_source_info => l_event_source_info,
p_event_id => l_prepay_app_event_id,
p_event_type_code => 'PREPAYMENT APPLIED',
p_event_date => NULL,
p_event_status_code => XLA_EVENTS_PUB_PKG.C_EVENT_NOACTION,
p_valuation_method => NULL,
p_security_context => l_event_security_context,
p_calling_sequence => l_curr_calling_sequence);
l_log_msg := 'After calling AP_XLA_EVENTS_PKG.UPDATE_EVENT';
SELECT DECODE ( AI.invoice_type_lookup_code,
'CREDIT', CREDIT_MEMO_VALIDATED_TYPE,
'DEBIT', DEBIT_MEMO_VALIDATED_TYPE,
'PREPAYMENT', PREPAYMENT_VALIDATED_TYPE,
INVOICE_VALIDATED_TYPE) event_type,
DECODE ( AI.invoice_type_lookup_code,
'CREDIT', CREDIT_MEMOS_CLASS,
'DEBIT', DEBIT_MEMOS_CLASS,
'PREPAYMENT', PREPAYMENTS_CLASS,
INVOICES_CLASS) event_class
INTO l_event_type, l_event_class
FROM ap_invoices_all AI
WHERE AI.invoice_id = p_invoice_id;
SELECT DECODE ( AI.invoice_type_lookup_code,
'CREDIT', CREDIT_MEMO_ADJUSTED_TYPE,
'DEBIT', DEBIT_MEMO_ADJUSTED_TYPE,
'PREPAYMENT', PREPAYMENT_ADJUSTED_TYPE,
INVOICE_ADJUSTED_TYPE) event_type,
DECODE ( AI.invoice_type_lookup_code,
'CREDIT', CREDIT_MEMOS_CLASS,
'DEBIT', DEBIT_MEMOS_CLASS,
'PREPAYMENT', PREPAYMENTS_CLASS,
INVOICES_CLASS) event_class
INTO l_event_type, l_event_class
FROM ap_invoices_all AI
WHERE AI.invoice_id = p_invoice_id;
SELECT MAX(accounting_event_id)
INTO l_accounting_event_id
FROM ap_invoice_distributions
WHERE invoice_id = p_invoice_id
AND NVL(posted_flag, 'N') <> 'Y'
AND line_type_lookup_code <> 'PREPAY'
AND prepay_distribution_id is NULL --for prepay tax
AND accounting_date = event_dist_rec.accounting_date
AND awt_invoice_payment_id is null; -- Bug 7410001
SELECT MAX(accounting_event_id)
INTO l_prepay_event_id
FROM ap_invoice_distributions AID
WHERE AID.invoice_id = p_invoice_id
AND AID.line_type_lookup_code = 'PREPAY'
AND AID.amount < 0
AND AID.posted_flag = 'Y';
SELECT MAX(accounting_event_id)
INTO l_pay_accounting_event_id
FROM ap_invoice_payments AIP
WHERE AIP.invoice_id = p_invoice_id
AND AIP.posted_flag = 'Y';
l_log_msg := 'Before calling AP_XLA_EVENTS_PKG.UPDATE_EVENT_STATUS';
AP_XLA_EVENTS_PKG.UPDATE_EVENT_STATUS
( p_event_source_info => l_event_source_info,
p_event_class_code => l_event_class,
p_event_type_code => l_event_type,
p_event_date => event_dist_rec.accounting_date,
p_event_status_code => l_event_status,
p_valuation_method => NULL,
p_security_context => l_event_security_context,
p_calling_sequence => l_curr_calling_sequence
);
l_log_msg := 'After calling AP_XLA_EVENTS_PKG.UPDATE_EVENT_STATUS';
UPDATE ap_invoice_distributions
SET accounting_event_id = l_accounting_event_id
WHERE invoice_id = p_invoice_id
AND accounting_date = event_dist_rec.accounting_date
AND awt_invoice_payment_id IS NULL
AND line_type_lookup_code <> 'PREPAY'
AND prepay_distribution_id IS NULL --prepay_tax_parent_id obsoleted
AND accounting_event_id IS NULL
AND nvl(cancellation_flag, 'N') = 'N'; -- Bug 5455054: Added the cancellation_flag check.
UPDATE ap_self_assessed_tax_dist_all
SET accounting_event_id = l_accounting_event_id
WHERE invoice_id = p_invoice_id
AND accounting_date = event_dist_rec.accounting_date
AND awt_invoice_payment_id IS NULL
AND line_type_lookup_code <> 'PREPAY'
AND prepay_distribution_id IS NULL
AND accounting_event_id IS NULL
AND nvl(cancellation_flag, 'N') = 'N';
UPDATE ap_invoice_distributions
SET accounting_event_id = l_accounting_event_id
WHERE accounting_event_id IS NULL
AND invoice_id = p_invoice_id
AND invoice_line_number = event_dist_rec.invoice_line_number
AND accounting_date = event_dist_rec.accounting_date
AND line_type_lookup_code = 'PREPAY'
-- Bug 6718967
AND sign(amount) = sign(event_dist_rec.dist_amount)
RETURNING invoice_distribution_id BULK COLLECT INTO l_inv_dist_tab;
UPDATE ap_invoice_distributions_all
SET accounting_event_id = l_accounting_event_id
WHERE line_type_lookup_code in ('REC_TAX','NONREC_TAX',
'TRV','TIPV','TERV') --Bug5455985
AND accounting_event_id IS NULL
AND charge_applicable_to_dist_id = l_inv_dist_tab(i);
SELECT accounting_date,
accounting_event_id,
transaction_type
FROM ap_payment_history
WHERE check_id = p_check_id
AND posted_flag <> 'Y'
AND transaction_type IN (PAYMENT_MATURTY_RVRSL_TRX_TYPE, PAYMENT_MATURTY_TRX_TYPE,
PAYMENT_MATURITY_ADJUSTED_TYPE, PAYMENT_CLEARED_TRX_TYPE,
PAYMENT_UNCLEARED_TRX_TYPE, PAYMENT_CLEARING_ADJUSTED_TYPE);
select count(*)
into l_matured_events_count
from AP_PAYMENT_HISTORY APH
where check_id = P_check_id
and transaction_type = PAYMENT_MATURTY_TRX_TYPE;
select count(*)
into l_unmatured_events_count
from ap_payment_history APH
where check_id = p_check_id
and transaction_type = PAYMENT_MATURTY_RVRSL_TRX_TYPE;
select amount,
currency_code,
maturity_exchange_rate_type,
maturity_exchange_date,
maturity_exchange_Rate
into l_amount,
l_currency_code,
l_maturity_exchange_rate_type,
l_maturity_exchange_date,
l_maturity_exchange_Rate
from ap_Checks
where check_id = P_check_id;
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => FND_GLOBAL.user_id,
X_LAST_UPDATE_LOGIN => FND_GLOBAL.login_id,
X_PROGRAM_UPDATE_DATE => NULL,
X_PROGRAM_APPLICATION_ID => NULL,
X_PROGRAM_ID => NULL,
X_REQUEST_ID => NULL,
X_CALLING_SEQUENCE => l_curr_calling_sequence);
AP_XLA_EVENTS_PKG.UPDATE_EVENT
( p_event_source_info => l_event_source_info,
p_event_id => l_accounting_event_id,
p_event_type_code => NULL,
p_event_date => NULL,
p_event_status_code => XLA_EVENTS_PUB_PKG.C_EVENT_NOACTION,
p_valuation_method => NULL,
p_security_context => l_event_security_context,
p_calling_sequence => l_curr_calling_sequence
);
l_log_msg := 'End of call to AP_XLA_EVENTS_PKG.UPDATE_EVENT';
| PROCEDURE - UPDATE_INVOICE_EVENT_STATUS (PUBLIC)
|
| DESCRIPTION
| Update invoice event's status
|
| PRAMETERS
| p_invoice_id: Invoice ID
| p_calling_sequence: Debug information
|
| KNOWN ISSUES:
|
| NOTES:
|
| MODIFICATION HISTORY
| Date Author Description of Change
|
*===========================================================================*/
PROCEDURE update_invoice_events_status(
p_invoice_id IN NUMBER,
p_calling_sequence IN VARCHAR2)
IS
TYPE t_check_ids IS TABLE OF NUMBER(15) INDEX BY PLS_INTEGER;
l_procedure_name CONSTANT VARCHAR2(30) := 'update_invoice_events_status';
' -> AP_ACCOUNTING_EVENTS_PKG.UPDATE_INVOICE_EVENTS_STATUS';
SELECT count(accounting_Event_id)
INTO l_event_count
FROM AP_INVOICE_DISTRIBUTIONS AID
WHERE AID.accounting_Event_id is not null
AND AID.invoice_id = P_Invoice_id;
SELECT distinct(AID.accounting_event_id)
FROM ap_invoice_distributions AID
WHERE AID.invoice_id = p_invoice_id
AND AID.accounting_event_id IS NOT NULL;
l_do_updates_flag BOOLEAN;
l_do_updates_flag :=
AP_XLA_EVENTS_PKG.EVENT_EXISTS
( p_event_source_info => l_event_source_info,
p_event_class_code => NULL,
p_event_type_code => NULL,
p_event_date => NULL,
p_event_status_code => XLA_EVENTS_PUB_PKG.C_EVENT_INCOMPLETE,
p_event_number => NULL,
p_valuation_method => NULL,
p_security_context => l_event_security_context,
p_calling_sequence => l_curr_calling_sequence
);
l_do_updates_flag :=
AP_XLA_EVENTS_PKG.EVENT_EXISTS
( p_event_source_info => l_event_source_info,
p_event_class_code => NULL,
p_event_type_code => NULL,
p_event_date => NULL,
p_event_status_code => XLA_EVENTS_PUB_PKG.C_EVENT_UNPROCESSED,
p_event_number => NULL,
p_valuation_method => NULL,
p_security_context => l_event_security_context,
p_calling_sequence => l_curr_calling_sequence
);
IF (l_do_updates_flag) THEN
OPEN l_invoice_distributions_cur;
select count(*)
into l_count
from ap_invoice_distributions_all
where accounting_event_id = l_accounting_event_ids(i)
and invoice_id = l_event_source_info.source_id_int_1
and awt_invoice_payment_id is not null;
select ac.check_id
into l_check_id
from ap_invoice_payments_all aip,
ap_checks_all ac
where aip.check_id=ac.check_id
and aip.accounting_event_id = l_accounting_event_ids(i)
and aip.invoice_id=l_event_source_info.source_id_int_1;
l_log_msg := 'Before AP_XLA_EVENTS_PKG.UPDATE_EVENT_STATUS';
AP_XLA_EVENTS_PKG.UPDATE_EVENT_STATUS
( p_event_source_info => l_event_source_info,
p_event_class_code => null,
p_event_type_code => null,
p_event_date => null,
p_event_status_code => l_invoice_event_status,
p_valuation_method => NULL,
p_security_context => l_event_security_context,
p_calling_sequence => l_curr_calling_sequence
);
END IF; -- l_do_updates_flag
SELECT distinct(AIP.check_id),
APH.accounting_event_id
FROM ap_invoice_payments AIP, ap_payment_history_all APH
WHERE AIP.invoice_id = p_invoice_id
AND AIP.accounting_event_id IS NOT NULL
and AIP.check_id = APH.check_id
and APH.accounting_event_id is not null
ORDER BY AIP.check_id;
l_do_updates_flag BOOLEAN;
l_do_updates_flag :=
AP_XLA_EVENTS_PKG.EVENT_EXISTS
( p_event_source_info => l_event_source_info,
p_event_class_code => NULL,
p_event_type_code => NULL,
p_event_date => NULL,
p_event_status_code => XLA_EVENTS_PUB_PKG.C_EVENT_INCOMPLETE,
p_event_number => NULL,
p_valuation_method => NULL,
p_security_context => l_event_security_context,
p_calling_sequence => l_curr_calling_sequence
);
l_do_updates_flag :=
AP_XLA_EVENTS_PKG.EVENT_EXISTS
( p_event_source_info => l_event_source_info,
p_event_class_code => NULL,
p_event_type_code => NULL,
p_event_date => NULL,
p_event_status_code => XLA_EVENTS_PUB_PKG.C_EVENT_UNPROCESSED,
p_event_number => NULL,
p_valuation_method => NULL,
p_security_context => l_event_security_context,
p_calling_sequence => l_curr_calling_sequence
);
IF (l_do_updates_flag) THEN
l_log_msg := 'Before calling AP_XLA_EVENTS_PKG.GET_EVENT_INFO';
l_log_msg := 'Before AP_XLA_EVENTS_PKG.UPDATE_EVENT_INFO';
AP_XLA_EVENTS_PKG.UPDATE_EVENT_STATUS
( p_event_source_info => l_event_source_info,
p_event_class_code => null,
p_event_type_code => null,
p_event_date => null,
p_event_status_code => l_payment_event_status,
p_valuation_method => NULL,
p_security_context => l_event_security_context,
p_calling_sequence => l_curr_calling_sequence
);
l_log_msg := 'After AP_XLA_EVENTS_PKG.UPDATE_EVENT_INFO';
END IF; -- l_do_updates_flag
END update_invoice_events_status;
| PROCEDURE - UPDATE_PAYMENT_EVENTS_STATUS (PUBLIC)
|
| DESCRIPTION
| Update payment event's status
|
| PRAMETERS
| p_check_id: Check ID
| p_calling_sequence: Debug information
|
| KNOWN ISSUES:
|
| NOTES:
|
| MODIFICATION HISTORY
| Date Author Description of Change
|
*===========================================================================*/
PROCEDURE update_payment_events_status (
p_check_id IN NUMBER,
p_calling_sequence IN VARCHAR2)
IS
TYPE t_invoice_ids IS TABLE OF NUMBER(15) INDEX BY PLS_INTEGER;
l_procedure_name CONSTANT VARCHAR2(30) := 'UPDATE_PAYMENT_EVENTS_STATUS';
' -> AP_ACCOUNTING_EVENTS_PKG.UPDATE_PAYMENT_EVENTS_STATUS';
SELECT distinct(AID.invoice_id),
AID.accounting_event_id
FROM ap_invoice_payments AIP,
ap_invoice_distributions AID
WHERE AIP.invoice_id = AID.invoice_id
AND AIP.check_id = p_check_id
AND AID.accounting_event_id IS NOT NULL
ORDER BY AID.invoice_id;
l_do_updates_flag BOOLEAN;
l_do_updates_flag :=
AP_XLA_EVENTS_PKG.EVENT_EXISTS
( p_event_source_info => l_event_source_info,
p_event_class_code => NULL,
p_event_type_code => NULL,
p_event_date => NULL,
p_event_status_code => XLA_EVENTS_PUB_PKG.C_EVENT_INCOMPLETE,
p_event_number => NULL,
p_valuation_method => NULL,
p_security_context => l_event_security_context,
p_calling_sequence => l_curr_calling_sequence
);
l_do_updates_flag :=
AP_XLA_EVENTS_PKG.EVENT_EXISTS
( p_event_source_info => l_event_source_info,
p_event_class_code => NULL,
p_event_type_code => NULL,
p_event_date => NULL,
p_event_status_code => XLA_EVENTS_PUB_PKG.C_EVENT_UNPROCESSED,
p_event_number => NULL,
p_valuation_method => NULL,
p_security_context => l_event_security_context,
p_calling_sequence => l_curr_calling_sequence);
IF (l_do_updates_flag) THEN
l_log_msg := 'Before calling AP_XLA_EVENTS_PKG.GET_EVENT_INFO';
select count(*)
into l_count
from ap_invoice_distributions_all
where accounting_event_id = l_accounting_event_ids(i)
and invoice_id = l_event_source_info.source_id_int_1
and awt_invoice_payment_id is not null;
select ac.check_id
into l_check_id
from ap_invoice_payments_all aip,
ap_checks_all ac
where aip.check_id=ac.check_id
and aip.accounting_event_id = l_accounting_event_ids(i)
and aip.invoice_id=l_event_source_info.source_id_int_1;
l_log_msg := 'Before AP_XLA_EVENTS_PKG.UPDATE_EVENT_STATUS';
AP_XLA_EVENTS_PKG.UPDATE_EVENT_STATUS
( p_event_source_info => l_event_source_info,
p_event_class_code => null,
p_event_type_code => null,
p_event_date => null,
p_event_status_code => l_invoice_event_status,
p_valuation_method => NULL,
p_security_context => l_event_security_context,
p_calling_sequence => l_curr_calling_sequence);
l_log_msg := 'After AP_XLA_EVENTS_PKG.UPDATE_EVENT_STATUS';
END IF; -- l_do_updates_flag
SELECT distinct(AIP.accounting_event_id)
FROM ap_invoice_payments AIP
WHERE AIP.check_id = p_check_id
AND AIP.accounting_event_id IS NOT NULL;
l_do_updates_flag BOOLEAN;
l_do_updates_flag :=
AP_XLA_EVENTS_PKG.EVENT_EXISTS
( p_event_source_info => l_event_source_info,
p_event_class_code => NULL,
p_event_type_code => NULL,
p_event_date => NULL,
p_event_status_code => XLA_EVENTS_PUB_PKG.C_EVENT_INCOMPLETE,
p_event_number => NULL,
p_valuation_method => NULL,
p_security_context => l_event_security_context,
p_calling_sequence => l_curr_calling_sequence
);
l_do_updates_flag :=
AP_XLA_EVENTS_PKG.EVENT_EXISTS
( p_event_source_info => l_event_source_info,
p_event_class_code => NULL,
p_event_type_code => NULL,
p_event_date => NULL,
p_event_status_code => XLA_EVENTS_PUB_PKG.C_EVENT_UNPROCESSED,
p_event_number => NULL,
p_valuation_method => NULL,
p_security_context => l_event_security_context,
p_calling_sequence => l_curr_calling_sequence
);
IF (l_do_updates_flag) THEN
OPEN l_invoice_payments_cur;
l_log_msg := 'Before AP_XLA_EVENTS_PKG.UPDATE_EVENT_STATUS';
AP_XLA_EVENTS_PKG.UPDATE_EVENT_STATUS
( p_event_source_info => l_event_source_info,
p_event_class_code =>
get_event_class
(
p_event_type => l_event_info.event_type_code,
p_calling_sequence => l_curr_calling_sequence
),
p_event_type_code => l_event_info.event_type_code,
p_event_date => l_event_info.event_date,
p_event_status_code => l_payment_event_status,
p_valuation_method => NULL,
p_security_context => l_event_security_context,
p_calling_sequence => l_curr_calling_sequence
);
l_log_msg := 'After AP_XLA_EVENTS_PKG.UPDATE_EVENT_STATUS';
END IF; -- l_do_updates_flag
END update_payment_events_status;
| PROCEDURE - update_pmt_batch_event_status
|
| DESCRIPTION
| Update accounting events for 'PAYMENT BATCH' type
|
| PRAMETERS
| p_completed_pmts_group_id: payment request name
| p_accounting_date:Event Date
| p_org_id : org id for each small batch inside the payment request
| p_set_of_books_id: the ledger id for this ou
| p_calling_sequence: Debug information
|
| KNOWN ISSUES:
|
| NOTES:
|
| MODIFICATION HISTORY
| Date Author Description of Change
|
*===========================================================================*/
PROCEDURE update_pmt_batch_event_status(
p_checkrun_name IN VARCHAR2,
p_completed_pmts_group_id IN NUMBER,
p_org_id IN NUMBER,
p_calling_sequence IN VARCHAR2)
IS
l_record_count NUMBER;
l_procedure_name CONSTANT VARCHAR2(40) := 'update_payment_batch_event_status';
' -> AP_ACCOUNTING_EVENTS_PKG.update_payment_batch_event_status';
SELECT COUNT(*)
INTO l_record_count
FROM XLA_EVENTS_INT_GT XEG
WHERE XEG.application_id = 200
AND XEG.entity_code = PAYMENTS_ENTITY
AND XEG.event_type_code = PAYMENT_CREATED_TYPE;
l_log_msg := 'update the gt table set event status = no action';
UPDATE XLA_EVENTS_INT_GT XEG
SET event_status_code = XLA_EVENTS_PUB_PKG.C_EVENT_NOACTION
WHERE XEG.application_id = 200
AND XEG.source_id_int_1 IN
( SELECT AC.check_id
FROM AP_CHECKS_ALL AC
WHERE AC.checkrun_name = p_checkrun_name
and AC.completed_pmts_group_id = p_completed_pmts_group_id
and AC.org_id = p_org_id );
l_log_msg := 'update the gt table in success and call the api';
XLA_EVENTS_PUB_PKG.update_bulk_event_statuses(p_application_id => '200');
l_log_msg := 'after calling xla update status api';
END update_pmt_batch_event_status;
DELETE XLA_EVENTS_INT_GT XEG
WHERE application_id = 200;
INSERT INTO XLA_EVENTS_INT_GT (
application_id,
ledger_id,
legal_entity_id,
entity_code,
transaction_number,
source_id_int_1,
transaction_date,
security_id_int_1,
event_type_code,
event_date,
event_status_code )
(SELECT
'200',
p_set_of_books_id,
ac.legal_entity_id,
PAYMENTS_ENTITY,
ac.check_number,
ac.check_id,
ac.check_date,
ac.org_id,
l_event_type,
p_accounting_date,
l_event_status
FROM ap_checks_all ac
WHERE ac.checkrun_name = p_checkrun_name
AND ac.completed_pmts_group_id = p_completed_pmts_group_id
AND ac.org_id = p_org_id);
SELECT COUNT(*)
INTO l_count
FROM XLA_EVENTS_INT_GT;
l_log_msg := 'unknown exception when try to insert into xla gt table';
l_log_msg := 'after insert into xla_event_gt table';
l_log_msg := 'Right before insert into ap_payment_history table';
INSERT INTO ap_payment_history_all
( payment_history_id,
check_id,
accounting_date,
transaction_type,
posted_flag,
trx_bank_amount,
errors_bank_amount,
charges_bank_amount,
bank_currency_code,
bank_to_base_xrate_type,
bank_to_base_xrate_date,
bank_to_base_xrate,
trx_pmt_amount,
errors_pmt_amount,
charges_pmt_amount,
pmt_currency_code,
pmt_to_base_xrate_type,
pmt_to_base_xrate_date,
pmt_to_base_xrate,
trx_base_amount,
errors_base_amount,
charges_base_amount,
matched_flag,
rev_pmt_hist_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
program_update_date,
program_application_id,
program_id,
accounting_event_id,
request_id,
org_id,
related_event_id ) -- Bug 5015973 -- Bug 5658623: Adding hint
( select /*+ Leading(xeg) index(ac ap_checks_u1) */
ap_payment_history_s.NEXTVAL, -- payment_history_id
ac.check_id, -- check_id
trunc(p_accounting_date), -- accounting_date bug6602676
l_event_type, -- transaction_type
'N', -- posted_flag
NULL, -- trx_bank_amount
NULL, -- errors_bank_amount
NULL, -- charges_bank_amount
NULL, -- bank_currency_code
NULL, -- bank_to_base_xrate_type
NULL, -- bank_to_base_xrate_date
NULL, -- bank_to_base_xrate
ac.amount, -- trx_pmt_amount
NULL, -- errors_pmt_amount
NULL, -- charges_pmt_amount
ac.currency_code, -- pmt_currency_code
ac.exchange_rate_type, -- pmt_to_base_xrate_type
ac.exchange_date, -- pmt_to_base_xrate_date
ac.exchange_rate, -- pmt_to_base_xrate
NVL(ac.base_amount, ac.amount), -- trx_base_amount
NULL, -- errors_base_amount
NULL, -- charges_base_amount
NULL, -- matched_flag
NULL, -- rev_pmt_hist_id
SYSDATE, -- creation_date
FND_GLOBAL.user_id, -- created_by
SYSDATE, -- last_update_date
FND_GLOBAL.user_id, -- last_updated_by
FND_GLOBAL.login_id, -- last_update_login
SYSDATE, -- program_update_date
NULL, -- program_application_id
NULL, -- program_id
XEG.event_id, -- accounting_event_id
NULL, -- request_id
ac.org_id, -- org_id
XEG.event_id -- related_event_id
from ap_checks_all ac,
xla_events_int_gt xeg
where ac.completed_pmts_group_id = p_completed_pmts_group_id
and ac.org_id = p_org_id
and xeg.source_id_int_1 = ac.check_id);
SELECT COUNT(*)
INTO l_count
FROM ap_payment_history_all aph,
ap_checks_all ac
WHERE aph.check_id = ac.check_id
AND ac.completed_pmts_group_id = p_completed_pmts_group_id
AND ac.org_id = p_org_id;
SELECT accounting_event_id
INTO l_count
FROM ap_payment_history_all aph,
ap_checks_all ac
WHERE aph.check_id = ac.check_id
AND ac.completed_pmts_group_id = p_completed_pmts_group_id
AND ac.org_id = p_org_id
AND rownum = 1;
l_log_msg := 'unknown exception when try to insert ap_payment_history table';
l_log_msg := 'After insert ap_payment_history_table and end the procedure';
| PROCEDURE - UPDATE_AWT_INT_DISTS (PUBLIC)
|
| DESCRIPTION
| This procedure is called by the payment event creation procs (EXCEPT for
| pmt batches). Stamp the event_id on all awt invoice distributions and
| on interest invoice distributions that were created by the payment for
| which this event is being created.It will also stamp the Payment
| Clearing Accounting event_id on the records in AIP when
| when_to_acct_pmt = clrg only
|
| PRAMETERS
| p_event_type: Event type
| p_check_id: Check ID
| p_event_id: Event ID
| p_calling_sequence: Debug information
|
| KNOWN ISSUES:
|
| NOTES:
|
| MODIFICATION HISTORY
| Date Author Description of Change
|
*===========================================================================*/
PROCEDURE update_awt_int_dists (
p_event_type IN VARCHAR2,
p_check_id IN NUMBER,
p_event_id IN NUMBER,
p_calling_sequence IN VARCHAR2)
IS
l_curr_calling_sequence VARCHAR2(2000);
l_procedure_name CONSTANT VARCHAR2(30) := 'UPDATE_AWT_INT_DISTS';
p_calling_sequence || ' -> AP_ACCOUNTING_EVENTS_PKG.UPDATE_AWT_INT_DISTS';
l_log_msg := 'Update ap_invoice_distribution, set accounting_event_id ='
||to_char(p_event_id);
UPDATE ap_invoice_distributions D
SET D.accounting_event_id = p_event_id
WHERE D.accounting_event_id IS NULL
AND D.awt_invoice_payment_id IN
(SELECT AIP1.invoice_payment_id
FROM ap_invoice_payments AIP1
WHERE AIP1.accounting_event_id = p_event_id
AND AIP1.check_id = p_check_id);
l_log_msg := 'Update ap_invoice_distribution, set accounting_event_id =
'||to_char(p_event_id);
UPDATE ap_invoice_distributions_all D
SET D.accounting_event_id = p_event_id
WHERE D.accounting_event_id IS NULL
AND D.invoice_id IN
(SELECT AI.invoice_id
FROM ap_invoice_payments_all AIP2,
ap_invoices_all AI
WHERE AI.invoice_id = AIP2.invoice_id
AND AIP2.check_id = p_check_id
AND AIP2.accounting_event_id = p_event_id
AND AI.invoice_type_lookup_code = 'INTEREST');
END update_awt_int_dists;
| PROCEDURE - BATCH_UPDATE_PAYMENT_INFO (PUBLIC)
|
| DESCRIPTION
| This procedure is called from appbip.lpc only when when_to_acct_pmt=
| ALWAYS. It is used to update awt and int inv dists with accoutning
| event id.
|
| PRAMETERS
| p_checkrun_name: Payment batch's name
| p_calling_sequence: Debug information
|
| KNOWN ISSUES:
|
| NOTES:
|
| MODIFICATION HISTORY
| Date Author Description of Change
|
*===========================================================================*/
PROCEDURE batch_update_payment_info(
p_checkrun_name IN VARCHAR2,
p_completed_pmts_group_id IN NUMBER,
p_org_id IN NUMBER,
p_calling_sequence IN VARCHAR2 DEFAULT NULL)
IS
CURSOR get_payment_info IS
SELECT AC.check_id,
AIP.accounting_event_id -- Bug3343314
FROM ap_checks AC,
ap_invoice_payments AIP -- Bug3343314
WHERE AC.check_id = AIP.check_id -- Bug3343314
AND AC.checkrun_name = p_checkrun_name
AND AC.status_lookup_code NOT IN ('OVERFLOW', 'SET UP')
AND AC.completed_pmts_group_id = p_completed_pmts_group_id
AND AC.org_id = p_org_id
AND AIP.posted_flag <> 'Y'; -- Bug3343314
l_procedure_name CONSTANT VARCHAR2(30) := 'BATCH_UPDATE_PAYMENT_INFO';
' -> AP_ACCOUNTING_EVENTS_PKG.BATCH_UPDATE_PAYMENT_INFO';
UPDATE ap_invoice_distributions AID
SET AID.accounting_event_id = l_accounting_event_ids(i)
WHERE AID.accounting_event_id IS NULL
AND AID.awt_invoice_payment_id IN
(SELECT AIP.invoice_payment_id
FROM ap_invoice_payments AIP
WHERE AIP.check_id = l_check_ids(i) AND
AIP.accounting_event_id = l_accounting_event_ids(i));
UPDATE ap_invoice_distributions_all AID
SET AID.accounting_event_id = l_accounting_event_ids(i)
WHERE AID.accounting_event_id IS NULL
AND AID.invoice_id IN
( SELECT AI.invoice_id
FROM ap_invoice_payments_all AIP,
ap_invoices_all AI
WHERE AIP.invoice_id = AI.invoice_id
AND AIP.accounting_event_id = l_accounting_event_ids(i)
AND AIP.check_id = l_check_ids(i)
AND AI.invoice_type_lookup_code = 'INTEREST');
END batch_update_payment_info;
l_procedure_name CONSTANT VARCHAR2(30) := 'BATCH_UPDATE_PAYMENT_INFO';
SELECT nvl(purch_encumbrance_flag,'N')
INTO l_purch_enc_flag
FROM financials_system_parameters FSP,
AP_INVOICES INV
WHERE INV.org_id = FSP.org_id
AND INV.set_of_books_id = FSP.set_of_books_id -- Bug 5608968 Avoid full index scan of fsp
AND INV.invoice_id = p_source_id;
SELECT count(*)
INTO l_count
FROM ap_invoice_distributions AID1
WHERE AID1.invoice_id = p_source_id
AND nvl(AID1.match_status_flag,'N') NOT IN ('T','A');
SELECT count(*)
INTO l_count
FROM ap_invoice_distributions AID
WHERE AID.invoice_id = p_source_id
AND nvl(AID.match_status_flag,'N') <> 'A';
SELECT nvl(purch_encumbrance_flag,'N')
INTO l_purch_enc_flag
FROM financials_system_parameters FSP,
AP_CHECKS AC
WHERE AC.org_id = FSP.org_id
AND AC.check_id = p_source_id;
SELECT count(*)
INTO l_count
FROM ap_invoice_distributions AID1
WHERE AID1.invoice_id in (SELECT invoice_id
FROM ap_invoice_payments
WHERE check_id = p_source_id)
AND nvl(AID1.match_status_flag,'N') NOT IN ('T','A');
SELECT count(*)
INTO l_count
FROM ap_invoice_distributions AID
WHERE AID.invoice_id in (SELECT invoice_id
FROM ap_invoice_payments
WHERE check_id = p_source_id)
AND nvl(AID.match_status_flag,'N') <> 'A' ;
SELECT count(*)
INTO l_count
FROM ap_holds H, ap_hold_codes C
WHERE H.invoice_id = p_source_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'));
SELECT count(*)
INTO l_count
FROM ap_holds H, ap_hold_codes C
WHERE H.invoice_id in (SELECT invoice_id
FROM ap_invoice_payments
WHERE check_id = p_source_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'));
l_last_updated_by NUMBER(15);
SELECT distinct(AIP.accounting_event_id)
FROM ap_invoice_payments AIP,
ap_checks AC
WHERE AIP.check_id = p_check_id
AND AIP.check_id = AC.check_id
AND nvl(AIP.posted_flag, 'N') = 'N'
AND AC.void_date IS NULL;
l_procedure_name CONSTANT VARCHAR2(30) := 'BATCH_UPDATE_PAYMENT_INFO';
SELECT exchange_rate_type,
exchange_date,
exchange_rate,
currency_code,
creation_date,
last_updated_by
INTO l_exchange_rate_type,
l_exchange_rate_date,
l_exchange_rate,
l_currency_code,
l_creation_date,
l_last_updated_by
FROM AP_CHECKS AC
WHERE AC.check_id = P_check_id;
l_log_msg := 'Before AP_RECONCILIATION_PKG.INSERT_PAYMENT_HISTORY';
AP_RECONCILIATION_PKG.insert_payment_history
(
x_check_id => p_check_id,
x_transaction_type => p_event_type,
x_accounting_date => p_accounting_date,
x_trx_bank_amount => NULL,
x_errors_bank_amount => NULL,
x_charges_bank_amount => NULL,
x_bank_currency_code => NULL,
x_bank_to_base_xrate_type => NULL,
x_bank_to_base_xrate_date => NULL,
x_bank_to_base_xrate => NULL,
x_trx_pmt_amount => 0,
x_errors_pmt_amount => NULL,
x_charges_pmt_amount => NULL,
x_pmt_currency_code => l_currency_code,
x_pmt_to_base_xrate_type => l_exchange_rate_type,
x_pmt_to_base_xrate_date => l_exchange_rate_date,
x_pmt_to_base_xrate => l_exchange_rate,
x_trx_base_amount => 0,
x_errors_base_amount => NULL,
x_charges_base_amount => NULL,
x_matched_flag => NULL,
x_rev_pmt_hist_id => NULL,
x_org_id => l_org_id, -- bug 4578865
x_creation_date => SYSDATE,
x_created_by => l_last_updated_by,
x_last_update_date => SYSDATE,
x_last_updated_by => l_last_updated_by,
x_last_update_login => l_last_updated_by,
x_program_update_date => NULL,
x_program_application_id => NULL,
x_program_id => NULL,
x_request_id => NULL,
x_calling_sequence => l_curr_calling_sequence,
x_accounting_event_id => l_accounting_event_id
);
l_log_msg := 'After AP_RECONCILIATION_PKG.INSERT_PAYMENT_HISTORY';
SELECT AID.invoice_id,
AID.accounting_date,
AID.invoice_line_number
FROM ap_invoice_distributions AID
WHERE AID.invoice_id = p_invoice_id
AND AID.line_type_lookup_code = 'PREPAY'
AND AID.amount < 0
AND AID.posted_flag = 'Y'
AND NVL(AID.reversal_flag, 'N') <> 'Y'
AND AID.parent_reversal_id IS NULL
AND NVL(AID.Historical_Flag, 'N') <> 'Y';
SELECT DISTINCT AIP.check_id,
AIP.accounting_date
FROM ap_invoice_payments AIP
WHERE AIP.invoice_id = p_invoice_id
AND AIP.posted_flag = 'Y'
AND NVL(AIP.reversal_flag, 'N') <> 'Y'
AND AIP.reversal_inv_pmt_id IS NULL
-- Bug 6890810. Added the subquery
AND NOT EXISTS (SELECT 'Upgraded Payment'
FROM AP_Payment_History APH
WHERE APH.Check_ID = AIP.Check_ID
AND NVL(APH.Historical_Flag, 'N') = 'Y');
SELECT DISTINCT APH.check_id,
APH.accounting_date
FROM ap_payment_history APH,
ap_invoice_payments AIP
WHERE AIP.invoice_id = p_invoice_id
AND AIP.check_id = APH.check_id
AND APH.transaction_type = 'PAYMENT CLEARING'
AND AIP.posted_flag = 'Y'
/* bug # 7604906. If the payment clearing is not
accounted, payment clearing adjustment should not
create */
AND APH.posted_flag = 'Y'
/* bug # 7604906 End */
AND NVL(APH.Historical_Flag, 'N') <> 'Y' -- Bug 6890810
AND NOT EXISTS( SELECT 'PAYMENT UNCLEARING EXISTS'
FROM ap_payment_history APH1
WHERE APH1.rev_pmt_hist_id = APH.payment_history_id
AND APH1.transaction_type = 'PAYMENT UNCLEARING');
SELECT DISTINCT APH.check_id,
APH.accounting_date
FROM ap_payment_history APH,
ap_invoice_payments AIP
WHERE AIP.invoice_id = p_invoice_id
AND AIP.check_id = APH.check_id
AND APH.transaction_type = 'PAYMENT MATURITY'
AND AIP.posted_flag = 'Y'
AND NVL(APH.Historical_Flag, 'N') <> 'Y' -- Bug 6890810
AND NOT EXISTS( SELECT 'PAYMENT MATURITY REVERSAL EXISTS'
FROM ap_payment_history APH1
WHERE APH1.rev_pmt_hist_id = APH.payment_history_id
AND APH1.transaction_type = 'PAYMENT MATURITY REVERSAL');
SELECT ac.exchange_rate_type,
ac.exchange_date,
ac.exchange_rate,
ac.currency_code,
ac.creation_date,
ac.last_updated_by,
ac.org_id,
ac.payment_type_flag,
asp.automatic_offsets_flag
FROM ap_checks_all ac,
ap_system_parameters_all asp ---7209263 added automatic offsets flag
WHERE ac.check_id = p_check_id
AND ac.org_id=asp.org_id;
l_last_updated_by NUMBER(15);
l_log_msg := 'Before calling procedure Insert_Prepayment_Header';
Insert_Prepayment_Header
(p_invoice_id => l_prepay_app_invoice_ids(i),
p_invoice_line_number => l_prepay_app_invoice_line_num(i),
p_accounting_event_id => l_accounting_event_id,
p_accounting_date => p_accounting_date, -- Bug 6996047
p_invoice_adjustment_id => l_adj_accounting_event_id,
p_calling_sequence => l_curr_calling_sequence);
l_log_msg := 'After calling procedure Insert_prepayment_Header';
l_last_updated_by,
l_org_id,
l_pay_type,
l_automatic_offsets_flag; ---7209263
l_log_msg := 'Before AP_RECONCILIATION_PKG.INSERT_PAYMENT_HISTORY';
AP_RECONCILIATION_PKG.insert_payment_history
(
x_check_id => l_pay_check_ids(i),
x_transaction_type => l_event_type ,
x_accounting_date => p_accounting_date, -- Bug 6996047
x_trx_bank_amount => NULL,
x_errors_bank_amount => NULL,
x_charges_bank_amount => NULL,
x_bank_currency_code => NULL,
x_bank_to_base_xrate_type => NULL,
x_bank_to_base_xrate_date => NULL,
x_bank_to_base_xrate => NULL,
x_trx_pmt_amount => 0,
x_errors_pmt_amount => NULL,
x_charges_pmt_amount => NULL,
x_pmt_currency_code => l_currency_code,
x_pmt_to_base_xrate_type => l_exchange_rate_type,
x_pmt_to_base_xrate_date => l_exchange_rate_date,
x_pmt_to_base_xrate => l_exchange_rate,
x_trx_base_amount => 0,
x_errors_base_amount => NULL,
x_charges_base_amount => NULL,
x_matched_flag => NULL,
x_rev_pmt_hist_id => NULL,
x_org_id => l_org_id, -- bug 4578865
x_creation_date => SYSDATE,
x_created_by => l_last_updated_by,
x_last_update_date => SYSDATE,
x_last_updated_by => l_last_updated_by,
x_last_update_login => l_last_updated_by,
x_program_update_date => NULL,
x_program_application_id => NULL,
x_program_id => NULL,
x_request_id => NULL,
x_calling_sequence => l_curr_calling_sequence,
x_accounting_event_id => l_accounting_event_id,
x_invoice_adjustment_event_id => l_adj_accounting_event_id -- bug fix 5694577
);
l_log_msg := 'After AP_RECONCILIATION_PKG.INSERT_PAYMENT_HISTORY';
SELECT AC.exchange_rate_type,
AC.exchange_date,
AC.exchange_rate,
AC.creation_date,
AC.last_updated_by,
AC.org_id,
asp.automatic_offsets_flag
INTO l_exchange_rate_type,
l_exchange_rate_date,
l_exchange_rate,
l_creation_date,
l_last_updated_by,
l_org_id,
l_automatic_offsets_flag --7209263
FROM AP_CHECKS AC, ap_system_parameters_all asp
WHERE AC.check_id = l_pay_clear_check_ids(i) --bug 7278341 l_pay_check_ids(i)
AND AC.org_id=asp.org_id;
l_last_updated_by,
l_org_id,
l_pay_type,
l_automatic_offsets_flag;
SELECT aph.bank_currency_code
INTO l_bank_curr_code
FROM ap_payment_history_all APH
WHERE APH.check_id = l_pay_clear_check_ids(i)
AND APH.transaction_type = 'PAYMENT CLEARING'
--added for bug 7614505
AND NOT EXISTS( SELECT 'PAYMENT UNCLEARING EXISTS'
FROM ap_payment_history_all APH1
WHERE APH1.rev_pmt_hist_id = APH.payment_history_id
AND APH1.transaction_type = 'PAYMENT UNCLEARING'
AND APH1.check_id = APH.check_id);
/* SELECT exchange_rate_type,
exchange_date,
exchange_rate,
creation_date,
last_updated_by,
org_id
INTO l_exchange_rate_type,
l_exchange_rate_date,
l_exchange_rate,
l_creation_date,
l_last_updated_by,
l_org_id
FROM AP_CHECKS AC
WHERE AC.check_id = l_pay_check_ids(i);
l_log_msg := 'Before AP_RECONCILIATION_PKG.INSERT_PAYMENT_HISTORY';
AP_RECONCILIATION_PKG.insert_payment_history
(
x_check_id => l_pay_clear_check_ids(i),
x_transaction_type => 'PAYMENT CLEARING ADJUSTED',
x_accounting_date => p_accounting_date, -- Bug 6996047
x_trx_bank_amount => NULL,
x_errors_bank_amount => NULL,
x_charges_bank_amount => NULL,
x_bank_currency_code => l_bank_curr_code,
x_bank_to_base_xrate_type => NULL,
x_bank_to_base_xrate_date => NULL,
x_bank_to_base_xrate => NULL,
x_trx_pmt_amount => 0,
x_errors_pmt_amount => NULL,
x_charges_pmt_amount => NULL,
x_pmt_currency_code => l_currency_code,
x_pmt_to_base_xrate_type => l_exchange_rate_type,
x_pmt_to_base_xrate_date => l_exchange_rate_date,
x_pmt_to_base_xrate => l_exchange_rate,
x_trx_base_amount => 0,
x_errors_base_amount => NULL,
x_charges_base_amount => NULL,
x_matched_flag => NULL,
x_rev_pmt_hist_id => NULL,
x_org_id => l_org_id, -- bug 4578865
x_creation_date => SYSDATE,
x_created_by => l_last_updated_by,
x_last_update_date => SYSDATE,
x_last_updated_by => l_last_updated_by,
x_last_update_login => l_last_updated_by,
x_program_update_date => NULL,
x_program_application_id => NULL,
x_program_id => NULL,
x_request_id => NULL,
x_calling_sequence => l_curr_calling_sequence,
x_accounting_event_id => l_accounting_event_id,
x_invoice_adjustment_event_id => l_adj_accounting_event_id --bug6710016
);
l_log_msg := 'After AP_RECONCILIATION_PKG.INSERT_PAYMENT_HISTORY ';
SELECT AC.exchange_rate_type,
AC.exchange_date,
AC.exchange_rate,
AC.creation_date,
AC.last_updated_by,
AC.org_id,
asp.automatic_offsets_flag
INTO l_exchange_rate_type,
l_exchange_rate_date,
l_exchange_rate,
l_creation_date,
l_last_updated_by,
l_org_id,
l_automatic_offsets_flag --7209263
FROM AP_CHECKS AC, ap_system_parameters_all asp
WHERE AC.check_id = l_pay_mat_check_ids(i)--bug 7278341 l_pay_check_ids(i)
AND AC.org_id=asp.org_id;
/* SELECT exchange_rate_type,
exchange_date,
exchange_rate,
creation_date,
last_updated_by,
org_id
INTO l_exchange_rate_type,
l_exchange_rate_date,
l_exchange_rate,
l_creation_date,
l_last_updated_by,
l_org_id
FROM AP_CHECKS AC
WHERE AC.check_id = l_pay_check_ids(i);
l_log_msg := 'Before AP_RECONCILIATION_PKG.INSERT_PAYMENT_HISTORY';
AP_RECONCILIATION_PKG.insert_payment_history
(
x_check_id => l_pay_mat_check_ids(i),
x_transaction_type => 'PAYMENT MATURITY ADJUSTED',
x_accounting_date => p_accounting_date, -- Bug 6996047
x_trx_bank_amount => NULL,
x_errors_bank_amount => NULL,
x_charges_bank_amount => NULL,
x_bank_currency_code => NULL,
x_bank_to_base_xrate_type => NULL,
x_bank_to_base_xrate_date => NULL,
x_bank_to_base_xrate => NULL,
x_trx_pmt_amount => 0,
x_errors_pmt_amount => NULL,
x_charges_pmt_amount => NULL,
x_pmt_currency_code => l_currency_code,
x_pmt_to_base_xrate_type => l_exchange_rate_type,
x_pmt_to_base_xrate_date => l_exchange_rate_date,
x_pmt_to_base_xrate => l_exchange_rate,
x_trx_base_amount => 0,
x_errors_base_amount => NULL,
x_charges_base_amount => NULL,
x_matched_flag => NULL,
x_rev_pmt_hist_id => NULL,
x_org_id => l_org_id, -- bug 4578865
x_creation_date => SYSDATE,
x_created_by => l_last_updated_by,
x_last_update_date => SYSDATE,
x_last_updated_by => l_last_updated_by,
x_last_update_login => l_last_updated_by,
x_program_update_date => NULL,
x_program_application_id => NULL,
x_program_id => NULL,
x_request_id => NULL,
x_calling_sequence => l_curr_calling_sequence,
x_accounting_event_id => l_accounting_event_id,
x_invoice_adjustment_event_id => l_adj_accounting_event_id --bug6710016
);
l_log_msg := 'After AP_RECONCILIATION_PKG.INSERT_PAYMENT_HISTORY';
SELECT accounting_date
FROM ap_invoice_distributions
WHERE invoice_id = p_invoice_id
AND awt_invoice_payment_id IS NULL
AND prepay_distribution_id IS NULL --prepay_tax_parent_id obsoleted
AND accounting_event_id IS NULL
AND cancellation_flag = 'Y'
GROUP BY accounting_date
ORDER BY accounting_date;
SELECT accounting_event_id
FROM ap_invoice_distributions
WHERE invoice_id = p_invoice_id
AND line_type_lookup_code = 'PREPAY'
AND amount <= 0;
SELECT accounting_date
FROM ap_invoice_distributions
WHERE invoice_id = p_invoice_id
AND line_type_lookup_code <> 'PREPAY'
AND prepay_distribution_id IS NULL --prepay_tax_parent_id obsoleted
GROUP BY accounting_date;
SELECT accounting_event_id
FROM ap_invoice_distributions
WHERE invoice_id = p_invoice_id
AND posted_flag <> 'Y';
SELECT DECODE
( AI.invoice_type_lookup_code,
'CREDIT', CREDIT_MEMO_CANCELLED_TYPE,
'DEBIT', DEBIT_MEMO_CANCELLED_TYPE,
'PREPAYMENT', PREPAYMENT_CANCELLED_TYPE,
INVOICE_CANCELLED_TYPE
) event_type,
DECODE
( AI.invoice_type_lookup_code,
'CREDIT', CREDIT_MEMOS_CLASS,
'DEBIT', DEBIT_MEMOS_CLASS,
'PREPAYMENT', PREPAYMENTS_CLASS,
INVOICES_CLASS
) event_class
INTO l_event_type,
l_event_class
FROM ap_invoices_all AI
WHERE AI.invoice_id = p_invoice_id;
UPDATE ap_invoice_distributions
SET accounting_event_id = l_accounting_event_ids(i)
WHERE invoice_id = p_invoice_id
AND accounting_date = l_accounting_event_dates(i)
AND awt_invoice_payment_id IS NULL
AND line_type_lookup_code <> 'PREPAY'
AND prepay_distribution_id IS NULL --prepay_tax_parent_id obsoleted
AND accounting_event_id IS NULL
AND cancellation_flag = 'Y';
UPDATE ap_self_assessed_tax_dist_all
SET accounting_event_id = l_accounting_event_ids(i)
WHERE invoice_id = p_invoice_id
AND accounting_date = l_accounting_event_dates(i)
AND awt_invoice_payment_id IS NULL
AND line_type_lookup_code <> 'PREPAY'
AND prepay_distribution_id IS NULL --prepay_tax_parent_id obsoleted
AND accounting_event_id IS NULL
AND cancellation_flag = 'Y';
| PROCEDURE - NO_ACTION_PMT_EVENT_UPDATE (PRIVATE)
|
| DESCRIPTION
| This procedure is used to create 'No Action' event in SLA.'No Action'
| status indicate that it is not necessary to create any accounting for a
| given event. 'No Action' event will be picked up by the SLA accounting
| process, but no accounting lines will be created.
|
| PRAMETERS
| p_check_id: Check ID
| p_event_type_code: Event Type
| p_accounting_date: Accounting date
| p_accounting_event_id: Accounting event whose status will be
| stamped as 'No Action'
| p_calling_sequence: Debug information
|
| KNOWN ISSUES:
|
| NOTES:
|
| MODIFICATION HISTORY
| Date Author Description of Change
|
*===========================================================================*/
PROCEDURE no_action_pmt_event_update(
p_check_id IN NUMBER,
p_event_type_code IN VARCHAR2,
p_accounting_date IN DATE,
p_accounting_event_id IN NUMBER,
p_calling_sequence IN VARCHAR2)
IS
-- Bug 4748638
CURSOR aip_event_id_count IS
SELECT accounting_event_id
FROM ap_invoice_payments
WHERE check_id = p_check_id
GROUP BY accounting_event_id;
l_procedure_name CONSTANT VARCHAR2(30) := 'NO_ACTION_PMT_EVENT_UPDATE';
' -> AP_ACCOUNTING_EVENTS_PKG.NO_ACTION_PMT_EVENT_UPDATE';
SELECT COUNT(*)
INTO l_processed_events
FROM ap_invoice_payments
WHERE posted_flag = 'Y'
AND check_id = p_check_id;
SELECT check_date
INTO l_accounting_date
FROM ap_checks
WHERE check_id = p_check_id;
l_log_msg :='Before calling P_XLA_EVENTS_PKG.UPDATE_EVENT_STATUS';
AP_XLA_EVENTS_PKG.UPDATE_EVENT_STATUS
( p_event_source_info => l_event_source_info,
p_event_class_code => l_event_class,
p_event_type_code => p_event_type_code,
p_event_date => l_accounting_date,
p_event_status_code => XLA_EVENTS_PUB_PKG.C_EVENT_NOACTION,
p_valuation_method => NULL,
p_security_context => l_event_security_context,
p_calling_sequence => l_curr_calling_sequence
);
l_log_msg :='After calling AP_XLA_EVENTS_PKG.UPDATE_EVENT_STATUS';
SELECT APH.accounting_date
INTO l_accounting_date
FROM ap_payment_history APH
WHERE APH.payment_history_id =
(SELECT max(payment_history_id)
FROM ap_payment_history APH2
WHERE APH2.check_id = p_check_id
AND APH2.posted_flag = 'N'
AND APH2.transaction_type = 'PAYMENT CLEARING');
l_log_msg :='Before calling AP_XLA_EVENTS_PKG.UPDATE_EVENT_STATUS';
AP_XLA_EVENTS_PKG.UPDATE_EVENT_STATUS
( p_event_source_info => l_event_source_info,
p_event_class_code => RECONCILED_PAYMENTS_CLASS,
p_event_type_code => p_event_type_code,
p_event_date => l_accounting_date,
p_event_status_code => XLA_EVENTS_PUB_PKG.C_EVENT_NOACTION,
p_valuation_method => NULL,
p_security_context => l_event_security_context,
p_calling_sequence => l_curr_calling_sequence
);
l_log_msg :='After calling AP_XLA_EVENTS_PKG.UPDATE_EVENT_STATUS';
SELECT APH.accounting_date
INTO l_accounting_date
FROM ap_payment_history APH
WHERE payment_history_id =
(SELECT max(payment_history_id)
FROM ap_payment_history APH2
WHERE APH2.check_id = p_check_id
AND APH2.posted_flag = 'N'
AND APH2.transaction_type = 'PAYMENT MATURITY');
l_log_msg :='Before calling AP_XLA_EVENTS_PKG.UPDATE_EVENT_STATUS';
AP_XLA_EVENTS_PKG.UPDATE_EVENT_STATUS
( p_event_source_info => l_event_source_info,
p_event_class_code => FUTURE_DATED_PAYMENTS_CLASS,
p_event_type_code => p_event_type_code,
p_event_date => l_accounting_date,
p_event_status_code => XLA_EVENTS_PUB_PKG.C_EVENT_NOACTION,
p_valuation_method => NULL,
p_security_context => l_event_security_context,
p_calling_sequence => l_curr_calling_sequence
);
l_log_msg :='After calling AP_XLA_EVENTS_PKG.UPDATE_EVENT_STATUS';
END no_action_pmt_event_update;
| This procedure is called by APXTRSWP.rdf (UPDATE_ACCTG_DATES).This
| procedure is used to sweep accounting events from one accounting period
| to another.
|
| PRAMETERS
| p_ledger_id: Current ledger ID
| p_period_name: Old accounting period
| p_from_date: The start date of sweeping
| p_to_date: The end date of the sweeping
| p_sweep_to_date: The new event date
| p_calling_sequence: Debug information
|
| KNOWN ISSUES:
|
| NOTES:
|
| MODIFICATION HISTORY
| Date Author Description of Change
|
*===========================================================================*/
PROCEDURE multi_org_events_sweep (
p_ledger_id IN NUMBER,
p_period_name IN VARCHAR2,
p_from_date IN DATE,
p_to_date IN DATE,
p_sweep_to_date IN DATE,
p_calling_sequence IN VARCHAR2 )
IS
TYPE t_event_ids IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
SELECT AID.accounting_event_id,
AID.invoice_id,
AID.org_id,
AI.legal_entity_id,
AID.set_of_books_id ledger_id
FROM ap_invoice_distributions_all AID,
ap_invoices_all AI
WHERE AP_UTILITIES_PKG.GET_CURRENT_GL_DATE(TRUNC(AID.accounting_date), aid.org_id) --bug5956469
= p_period_name
AND aid.posted_flag IN ('N', 'S') -- Bug 6869699
AND aid.org_id = ai.org_id
AND AID.org_id IN
( SELECT ASP.org_id
FROM hr_organization_information OI,
--hr_all_organization_units_tl LE, --bug6392886
hr_all_organization_units_tl OU,
ap_system_parameters_all ASP
WHERE ASP.org_id = OI.organization_id
AND OU.organization_id = OI.organization_id
AND OI.org_information_context = 'Operating Unit Information'
AND DECODE(LTRIM(OI.org_information3, '0123456789'), NULL,
TO_NUMBER(OI.org_information3), NULL) = p_ledger_id
--bug6392886
/*AND DECODE(LTRIM(OI.org_information2, '0123456789'), NULL,
TO_NUMBER(OI.org_information2), NULL) = LE.organization_id*/
AND OU.organization_id = OI.organization_id
AND OU.language = USERENV('LANG')
--AND LE.language = USERENV('LANG')
) AND AID.invoice_id = AI.invoice_id
AND AID.accounting_event_id is not NULL;--Bug6320053
SELECT AID.accounting_event_id,
AID.invoice_id,
AID.org_id,
AI.legal_entity_id,
AID.set_of_books_id ledger_id
FROM ap_invoice_distributions_all AID,
ap_invoices_all AI
WHERE AID.accounting_date BETWEEN p_from_date AND p_to_date
AND aid.posted_flag IN ('N', 'S') -- Bug 6869699
AND aid.org_id = ai.org_id
AND AID.org_id IN
( SELECT ASP.org_id
FROM hr_organization_information OI,
--hr_all_organization_units_tl LE, --bug6392886
hr_all_organization_units_tl OU,
ap_system_parameters_all ASP
WHERE ASP.org_id = OI.organization_id
AND OU.organization_id = OI.organization_id
AND OI.org_information_context = 'Operating Unit Information'
AND DECODE(LTRIM(OI.org_information3, '0123456789'), NULL,
TO_NUMBER(OI.org_information3), NULL) = p_ledger_id
--bug6392886
/*AND DECODE(LTRIM(OI.org_information2, '0123456789'), NULL,
TO_NUMBER(OI.org_information2), NULL) = LE.organization_id*/
AND OU.organization_id = OI.organization_id
AND OU.language = USERENV('LANG')
--AND LE.language = USERENV('LANG')
) AND AID.invoice_id = AI.invoice_id
AND AID.accounting_event_id is not NULL ;--Bug6320053
SELECT APH.accounting_event_id,
APH.check_id check_id,
APH.org_id,
AC.legal_entity_id,
( SELECT AIP.set_of_books_id
FROM ap_invoice_payments_all AIP
WHERE AIP.check_id = APH.check_id
AND ROWNUM = 1
) ledger_id
FROM
ap_payment_history_all APH,
ap_checks_all AC
WHERE
APH.check_id = AC.check_id
AND AP_UTILITIES_PKG.GET_CURRENT_GL_DATE(TRUNC(APH.accounting_date), aph.org_id) --bug5956469
= p_period_name
AND aph.posted_flag IN ('N', 'S') -- Bug 6869699
and ac.org_id = aph.org_id
AND APH.org_id IN
( SELECT ASP.org_id
FROM hr_organization_information OI,
--hr_all_organization_units_tl LE, --bug6392886
hr_all_organization_units_tl OU,
ap_system_parameters_all ASP
WHERE ASP.org_id = OI.organization_id
AND OU.organization_id = OI.organization_id
AND OI.org_information_context = 'Operating Unit Information'
AND DECODE(LTRIM(OI.org_information3,'0123456789'), NULL,
TO_NUMBER(OI.org_information3), NULL) = p_ledger_id
--bug6392886
/*AND DECODE(LTRIM(OI.org_information2,'0123456789'), NULL,
TO_NUMBER(OI.org_information2), NULL) = LE.organization_id */
AND OU.organization_id = OI.organization_id
AND OU.language = USERENV('LANG')
--AND LE.language = USERENV('LANG')
)
AND APH.accounting_event_id is not NULL ;--Bug6320053
SELECT APH.accounting_event_id,
APH.check_id check_id,
APH.org_id,
AC.legal_entity_id,
( SELECT AIP.set_of_books_id
FROM ap_invoice_payments_all AIP
WHERE AIP.check_id = APH.check_id
AND ROWNUM = 1
) ledger_id
FROM
ap_payment_history_all APH,
ap_checks_all AC
WHERE
APH.check_id = AC.check_id
AND APH.accounting_date BETWEEN p_from_date AND p_to_date
AND aph.posted_flag IN ('N', 'S') -- Bug 6869699
and ac.org_id = aph.org_id
AND APH.org_id IN
( SELECT ASP.org_id
FROM hr_organization_information OI,
--hr_all_organization_units_tl LE, bug6392886
hr_all_organization_units_tl OU,
ap_system_parameters_all ASP
WHERE ASP.org_id = OI.organization_id
AND OU.organization_id = OI.organization_id
AND OI.org_information_context = 'Operating Unit Information'
AND DECODE(LTRIM(OI.org_information3,'0123456789'), NULL,
TO_NUMBER(OI.org_information3), NULL) = p_ledger_id
--bug6392886
/*AND DECODE(LTRIM(OI.org_information2,'0123456789'), NULL,
TO_NUMBER(OI.org_information2), NULL) = LE.organization_id*/
AND OU.organization_id = OI.organization_id
AND OU.language = USERENV('LANG')
--AND LE.language = USERENV('LANG')
)
AND APH.accounting_event_id is not NULL ;--Bug6320053
l_log_msg :='Before calling procedure AP_XLA_EVENTS_PKG.UPDATE_EVENT';
AP_XLA_EVENTS_PKG.UPDATE_EVENT
( p_event_source_info => l_event_source_info,
p_event_id => l_event_ids(i),
p_event_type_code => NULL,
p_event_date => p_sweep_to_date,
p_event_status_code => NULL,
p_valuation_method => NULL,
p_security_context => l_event_security_context,
p_calling_sequence => l_curr_calling_sequence
);
UPDATE xla_ae_headers aeh
SET aeh.accounting_date = p_sweep_to_date,
aeh.period_name = AP_UTILITIES_PKG.get_gl_period_name(
p_sweep_to_date,
l_org_ids(i)),
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id
WHERE aeh.event_id = l_event_ids(i)
AND application_id = 200
AND gl_transfer_status_code <> 'Y';
UPDATE xla_ae_lines ael
SET ael.accounting_date = p_sweep_to_date,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.user_id
WHERE ael.ae_header_id in (
SELECT aeh.ae_header_id
FROM xla_ae_headers aeh
WHERE aeh.event_id = l_event_ids(i)
AND aeh.application_id = 200
AND aeh.gl_transfer_status_code <> 'Y');
l_log_msg :='After calling procedure AP_XLA_EVENTS_PKG.UPDATE_EVENT';
l_log_msg :='Before calling procedure AP_XLA_EVENTS_PKG.UPDATE_EVENT';
AP_XLA_EVENTS_PKG.UPDATE_EVENT
( p_event_source_info => l_event_source_info,
p_event_id => l_event_ids(i),
p_event_type_code => NULL,
p_event_date => p_sweep_to_date,
p_event_status_code => NULL,
p_valuation_method => NULL,
p_security_context => l_event_security_context,
p_calling_sequence => l_curr_calling_sequence
);
UPDATE xla_ae_headers aeh
SET aeh.accounting_date = p_sweep_to_date,
aeh.period_name = AP_UTILITIES_PKG.get_gl_period_name(
p_sweep_to_date,
l_org_ids(i)),
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id
WHERE aeh.event_id = l_event_ids(i)
AND application_id = 200
AND gl_transfer_status_code <> 'Y';
UPDATE xla_ae_lines ael
SET ael.accounting_date = p_sweep_to_date,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.user_id
WHERE ael.ae_header_id in (
SELECT aeh.ae_header_id
FROM xla_ae_headers aeh
WHERE aeh.event_id = l_event_ids(i)
AND aeh.application_id = 200
AND aeh.gl_transfer_status_code <> 'Y');
l_log_msg :='After calling procedure AP_XLA_EVENTS_PKG.UPDATE_EVENT';
| This procedure is called by APXTRSWP.rdf (UPDATE_ACCTG_DATES).This
| procedure is used to sweep accounting events from one accounting period
| to another.
|
| PRAMETERS:
| p_period_name: Old period's name
| p_from_date: The start date to sweep
| p_to_date: The end date to sweep
| p_sweep_to_date: New event date
| p_calling_sequence: Debug information
|
| KNOWN ISSUES:
|
| NOTES:
|
| MODIFICATION HISTORY
| Date Author Description of Change
|
*===========================================================================*/
PROCEDURE single_org_events_sweep(
p_period_name IN VARCHAR2,
p_from_date IN DATE,
p_to_date IN DATE,
p_sweep_to_date IN DATE,
p_calling_sequence IN VARCHAR2)
IS
TYPE t_event_ids IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
SELECT AID.accounting_event_id,
AID.invoice_id,
AID.org_id,
AI.legal_entity_id,
AID.set_of_books_id ledger_id
FROM ap_invoice_distributions AID,
ap_invoices AI
WHERE ( ( p_period_name IS NULL AND
AID.accounting_date BETWEEN p_from_date AND p_to_date)
OR
( p_period_name IS NOT NULL AND
AP_UTILITIES_PKG.GET_CURRENT_GL_DATE(TRUNC(AID.accounting_date), aid.org_id) --bug5956469
= p_period_name
)
)
AND AID.posted_flag <> 'Y'
AND AID.invoice_id = AI.invoice_id
AND AID.accounting_event_id is not NULL;--Bug6320053
SELECT APH.accounting_event_id,
APH.check_id,
APH.org_id,
AC.legal_entity_id,
( SELECT AIP.set_of_books_id
FROM ap_invoice_payments_all AIP
WHERE AIP.check_id = APH.check_id
AND ROWNUM = 1
) ledger_id
FROM ap_payment_history_all APH,
ap_checks_all AC
WHERE APH.check_id = AC.check_id
AND APH.org_id = AC.org_id
AND AP_UTILITIES_PKG.GET_CURRENT_GL_DATE(TRUNC(APH.accounting_date), aph.org_id) --bug5956469
= p_period_name
AND aph.posted_flag IN ('N', 'S') -- Bug 6869699
AND APH.accounting_event_id is not NULL;--Bug6320053
SELECT APH.accounting_event_id,
APH.check_id,
APH.org_id,
AC.legal_entity_id,
( SELECT AIP.set_of_books_id
FROM ap_invoice_payments_all AIP
WHERE AIP.check_id = APH.check_id
AND ROWNUM = 1
) ledger_id
FROM ap_payment_history_all APH,
ap_checks_all AC
WHERE APH.check_id = AC.check_id
AND APH.org_id = AC.org_id
AND APH.accounting_date BETWEEN p_from_date AND p_to_date
AND aph.posted_flag IN ('N', 'S') -- Bug 6869699
AND APH.accounting_event_id is not NULL;--Bug6320053
l_log_msg :='Before calling procedure AP_XLA_EVENTS_PKG.UPDATE_EVENT';
AP_XLA_EVENTS_PKG.UPDATE_EVENT
( p_event_source_info => l_event_source_info,
p_event_id => l_event_ids(i),
p_event_type_code => NULL,
p_event_date => p_sweep_to_date,
p_event_status_code => NULL,
p_valuation_method => NULL,
p_security_context => l_event_security_context,
p_calling_sequence => l_curr_calling_sequence
);
UPDATE xla_ae_headers aeh
SET aeh.accounting_date = p_sweep_to_date,
aeh.period_name = AP_UTILITIES_PKG.get_gl_period_name(
p_sweep_to_date,
l_org_ids(i)),
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id
WHERE aeh.event_id = l_event_ids(i)
AND application_id = 200
AND gl_transfer_status_code <> 'Y';
UPDATE xla_ae_lines ael
SET ael.accounting_date = p_sweep_to_date,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.user_id
WHERE ael.ae_header_id in (
SELECT aeh.ae_header_id
FROM xla_ae_headers aeh
WHERE aeh.event_id = l_event_ids(i)
AND aeh.application_id = 200
AND aeh.gl_transfer_status_code <> 'Y');
l_log_msg :='After calling procedure AP_XLA_EVENTS_PKG.UPDATE_EVENT';
l_log_msg :='After calling procedure AP_XLA_EVENTS_PKG.UPDATE_EVENT';
AP_XLA_EVENTS_PKG.UPDATE_EVENT
( p_event_source_info => l_event_source_info,
p_event_id => l_event_ids(i),
p_event_type_code => NULL,
p_event_date => p_sweep_to_date,
p_event_status_code => NULL,
p_valuation_method => NULL,
p_security_context => l_event_security_context,
p_calling_sequence => l_curr_calling_sequence
);
UPDATE xla_ae_headers aeh
SET aeh.accounting_date = p_sweep_to_date,
aeh.period_name = AP_UTILITIES_PKG.get_gl_period_name(
p_sweep_to_date,
l_org_ids(i)),
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id
WHERE aeh.event_id = l_event_ids(i)
AND application_id = 200
AND gl_transfer_status_code <> 'Y';
UPDATE xla_ae_lines ael
SET ael.accounting_date = p_sweep_to_date,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.user_id
WHERE ael.ae_header_id in (
SELECT aeh.ae_header_id
FROM xla_ae_headers aeh
WHERE aeh.event_id = l_event_ids(i)
AND aeh.application_id = 200
AND aeh.gl_transfer_status_code <> 'Y');
l_log_msg :='After calling procedure AP_XLA_EVENTS_PKG.UPDATE_EVENT';
select invoice_num
into l_invoice_num
from ap_invoices_all --bug6705052
where invoice_id = p_invoice_id;
select check_number
into l_check_number
from ap_checks_all --bug6705052
where check_id = p_check_id;
SELECT
AI.org_id,
AI.legal_entity_id,
AI.set_of_books_id,
AI.invoice_date
INTO
p_org_id,
p_legal_entity_id,
p_ledger_id,
p_transaction_date
FROM
ap_invoices_all AI --bug6705052
WHERE
AI.invoice_id = p_invoice_id;
SELECT AC.org_id,
AC.legal_entity_id,
ASP.set_of_books_id
INTO p_org_id,
p_legal_entity_id,
p_ledger_id
FROM ap_checks_all AC, --bug6705052
ap_system_parameters_all ASP --bug6705052
WHERE AC.check_id = p_check_id
AND nvl(AC.org_id,-99) = nvl(ASP.org_id,-99);
| PROCEDURE - UPDATE_PREPAYMENT_HEADER(PRIVATE)
|
| DESCRIPTION
| This procedure is used to update prepayment header information
|
| PRAMETERS:
| p_invoice_id: Invoice ID
| p_invoice_line_number: Invoice Line Number
| p_accounting_event_id: Accounting Event ID
| p_calling_sequence: Debug information
|
*==========================================================================*/
PROCEDURE Update_Prepayment_Header(
p_invoice_id IN NUMBER,
p_invoice_line_number IN NUMBER,
p_accounting_event_id IN NUMBER,
p_accounting_date IN DATE,
p_transaction_type IN VARCHAR2,
p_calling_sequence IN VARCHAR2)
IS
l_curr_calling_sequence VARCHAR2(2000);
l_procedure_name CONSTANT VARCHAR2(30) := 'Update_Prepayment_Header';
' -> AP_ACCOUNTING_EVENTS_PKG.UPDATE_PREPAYMENT_HEADER';
UPDATE AP_Prepay_History
SET Accounting_Event_ID = p_accounting_event_id
WHERE Invoice_ID = p_invoice_id
AND Invoice_Line_Number = p_invoice_line_number
AND Accounting_Date = p_accounting_date
AND Transaction_Type = p_transaction_type
AND Accounting_Event_ID IS NULL
RETURNING Prepay_History_ID
BULK COLLECT INTO l_prepay_hist_tab;
UPDATE AP_Prepay_App_Dists APAD
SET Accounting_Event_ID = p_accounting_event_id
WHERE Prepay_History_ID = l_prepay_hist_tab(i);
END Update_Prepayment_Header;
| PROCEDURE - INSERT_PREPAYMENT_HEADER(PRIVATE)
|
| DESCRIPTION
| This procedure is used to insert prepayment header information
|
| PRAMETERS:
| p_invoice_id: Invoice ID
| p_invoice_line_number: Invoice Line Number
| p_accounting_event_id: Accounting Event ID
| p_invoice_adjustment_id: Invoice Adjustment ID
| p_calling_sequence: Debug information
|
| KNOWN ISSUES:
|
| NOTES:
|
| MODIFICATION HISTORY
| Date Author Description of Change
|
*==========================================================================*/
PROCEDURE Insert_Prepayment_Header(
p_invoice_id IN NUMBER,
p_invoice_line_number IN NUMBER,
p_accounting_event_id IN NUMBER,
p_accounting_date IN DATE,
p_invoice_adjustment_id IN NUMBER,
p_calling_sequence IN VARCHAR2)
IS
l_curr_calling_sequence VARCHAR2(2000);
l_procedure_name CONSTANT VARCHAR2(30) := 'Insert_Prepayment_Header';
SELECT AIL.amount,
AIL.invoice_id,
AIL.line_number,
AIL.org_id
FROM ap_invoice_lines AIL,
ap_invoice_distributions AID,
ap_invoice_distributions AID1
WHERE AID.invoice_id = p_invoice_id
AND AID.invoice_line_number = p_invoice_line_number
AND AID.line_type_lookup_code = 'PREPAY'
AND AID.prepay_distribution_id = AID1.invoice_distribution_id
AND AIL.invoice_id = AID1.invoice_id
AND AIL.line_number = AID1.invoice_line_number
GROUP BY ail.invoice_id,ail.line_number,ail.org_id,ail.amount;
' -> AP_ACCOUNTING_EVENTS_PKG.INSERT_PREPAYMENT_HEADER';
SELECT AIL.amount,
AIL.prepay_invoice_id,
AIL.prepay_line_number,
AIL.org_id
INTO l_sum_amount,
l_prepay_invoice_id,
l_prepay_line_number,
l_org_id
FROM ap_invoice_lines AIL
WHERE AIL.invoice_id = P_invoice_id
AND AIL.line_type_lookup_code = 'PREPAY'
AND AIL.line_number = P_invoice_line_number;
SELECT min(accounting_Event_id)
INTO l_related_prepay_app_event_id
FROM AP_INVOICE_DISTRIBUTIONS AID
WHERE AID.line_type_lookup_code = 'PREPAY'
AND nvl(posted_flag,'N') = 'Y'
AND nvl(AID.amount,0) < 0
AND AID.invoice_id = P_invoice_id
AND AID.invoice_line_number = P_invoice_line_number;
INSERT INTO AP_PREPAY_HISTORY_ALL
( PREPAY_HISTORY_ID
,PREPAY_INVOICE_ID
,PREPAY_LINE_NUM
,ACCOUNTING_EVENT_ID
,HISTORICAL_FLAG
,INVOICE_ID
,INVOICE_LINE_NUMBER
,ACCOUNTING_DATE
,INVOICE_ADJUSTMENT_EVENT_ID
,ORG_ID
,POSTED_FLAG
,RELATED_PREPAY_APP_EVENT_ID
,TRANSACTION_TYPE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,CREATED_BY
,CREATION_DATE
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE
,REQUEST_ID)
VALUES
(AP_PREPAY_HISTORY_S.nextval
,l_prepay_invoice_id
,l_prepay_line_number
,p_Accounting_event_id
,'N'
,P_INVOICE_ID
,p_invoice_line_number
,p_accounting_date
,p_invoice_adjustment_id
,l_org_id
,'N'
,l_related_prepay_app_event_id
,L_TRANSACTION_TYPE
,FND_GLOBAL.user_id
,sysdate
,FND_GLOBAL.login_id
,FND_GLOBAL.user_id
,sysdate
,null
,null
,null
,null);
SELECT min(accounting_Event_id)
INTO l_related_prepay_app_event_id
FROM AP_INVOICE_DISTRIBUTIONS AID
WHERE AID.line_type_lookup_code = 'PREPAY'
AND nvl(posted_flag,'N') = 'Y'
AND nvl(AID.amount,0) < 0
AND AID.invoice_id = P_invoice_id
AND AID.invoice_line_number = P_invoice_line_number;
INSERT INTO AP_PREPAY_HISTORY_ALL
( PREPAY_HISTORY_ID
,PREPAY_INVOICE_ID
,PREPAY_LINE_NUM
,ACCOUNTING_EVENT_ID
,ACCOUNTING_DATE
,HISTORICAL_FLAG
,INVOICE_ID
,INVOICE_ADJUSTMENT_EVENT_ID
,ORG_ID
,POSTED_FLAG
,RELATED_PREPAY_APP_EVENT_ID
,TRANSACTION_TYPE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,CREATED_BY
,CREATION_DATE
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE
,REQUEST_ID)
VALUES
(AP_PREPAY_HISTORY_S.nextval
,l_prepay_invoice_id
,l_prepay_line_number
,p_Accounting_event_id
,p_accounting_date
,'N'
,P_INVOICE_ID
,p_invoice_adjustment_id
,l_org_id
,'N'
,l_related_prepay_app_event_id
,L_TRANSACTION_TYPE
,FND_GLOBAL.user_id
,sysdate
,FND_GLOBAL.login_id
,FND_GLOBAL.user_id
,sysdate
,null
,null
,null
,null);
END Insert_Prepayment_Header;