[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| This procedure is used to update posted flag of document such
| as invoice or payment, invoice payment so that user will not
| make changes during accounting process via form.
|
| PRAMETERS
| p_level:
| p_procedure: The procedure's name
| p_debug_info: The log message
| KNOWN ISSUES:
|
| NOTES:
|
|
| MODIFICATION HISTORY
| Date Author Description of Change
|
*===========================================================================*/
PROCEDURE lock_documents_autonomous (
p_event_ids IN l_event_ids_typ,
p_calling_sequence IN VARCHAR2
)
IS
-- PRAGMA AUTONOMOUS_TRANSACTION; bug 7351478
UPDATE ap_payment_history_All APH
SET POSTED_FLAG = 'S'
WHERE APH.accounting_event_id = p_event_ids(i);
UPDATE ap_invoice_payments_all AIP
SET AIP.posted_flag = 'S'
WHERE AIP.accounting_event_id = p_event_ids(i);
UPDATE ap_invoice_distributions_all AID
SET AID.posted_flag = 'S'
WHERE AID.accounting_event_id = p_event_ids(i);
UPDATE ap_self_assessed_tax_dist_all STID
SET STID.posted_flag = 'S'
WHERE STID.accounting_event_id = p_event_ids(i);
UPDATE ap_prepay_history_all APPH
SET POSTED_FLAG = 'S'
WHERE APPH.accounting_event_id = p_event_ids(i);
| parameter is purely informational. This procedure selects from the
| XLA_ENTITY_EVENTS_V view, which does not include events incompatible
| with this parameter. This parameter will never be NULL.
| p_process_category:
| This parameter is the "process category" of the events to account. This
| parameter is purely informational. This procedure selects from the
| XLA_ENTITY_EVENTS_V view, which does not include events incompatible
| with this parameter.Possible values are as following:
| +------------+------------------------------------------+
| | Value | Meaning |
| +------------+------------------------------------------+
| | 'Invoices' | process invoices |
| | 'Payments' | process payments and reconciled payments |
| | 'All' | process everything |
| +------------+------------------------------------------+
| p_end_date
| This parameter is the maximum event date of the events to be processed
| in this run of the accounting. This procedure selects from the
| XLA_ENTITY_EVENTS_V view, which does not include events incompatible
| with this parameter. This parameter will never be NULL.
| p_accounting_mode
| This parameter is the "accounting mode" that the accounting is being
| run in. This parameter will never be NULL.
| +-------+------------------------------------------------------------+
| | Value | Meaning |
| +-------+------------------------------------------------------------+
| | 'D' | The accounting is being run in "draft mode". Draft mode is |
| | | used to examine what the accounting entries would look for |
| | | an event without actually creating the accounting entries. |
| | | without actually creating the accounting entries. |
| | 'F' | The accounting is being run in "final mode". Final mode is |
| | | used to create accounting entries. |
| +-------+------------------------------------------------------------+
| p_valuation_method
| This parameter is unused by AP. This parameter is purely informational.
| This procedure selects from the XLA_ENTITY_EVENTS_V view, which does
| not include events incompatible with this parameter.
| p_security_id_int_1
| This parameter is unused by AP.
| p_security_id_int_2
| This parameter is unused by AP.
| p_security_id_int_3
| This parameter is unused by AP.
| p_security_id_char_1
| This parameter is unused by AP.
| p_security_id_char_2
| This parameter is unused by AP.
| p_security_id_char_3
| This parameter is unused by AP.
| p_report_request_id
| This parameter is the concurrent request ID of the concurrent request
| that is this run of the accounting. This parameter is used to specify
| which events in the XLA_ENTITY_EVENTS_V view are to be accounted in
| this run of the accounting. This parameter will never be NULL.
| KNOWN ISSUES:
|
| NOTES:
| 1) This procedure is run in final mode and draft mode.
| 2) This procedure is run in batch mode but not in document mode.
| 3) This procedure is in its own commit cycle.
|
| MODIFICATION HISTORY
| Date Author Description of Change
|
*===========================================================================*/
PROCEDURE preaccounting
(
p_application_id IN NUMBER,
p_ledger_id IN INTEGER,
p_process_category IN VARCHAR2,
p_end_date IN DATE,
p_accounting_mode IN VARCHAR2,
p_valuation_method IN VARCHAR2,
p_security_id_int_1 IN INTEGER,
p_security_id_int_2 IN INTEGER,
p_security_id_int_3 IN INTEGER,
p_security_id_char_1 IN VARCHAR2,
p_security_id_char_2 IN VARCHAR2,
p_security_id_char_3 IN VARCHAR2,
p_report_request_id IN INTEGER
)
IS
l_debug_info VARCHAR2(240);
SELECT XPAE.event_id event_id,
XPAE.event_type_code event_type_code,
XPAE.SOURCE_ID_INT_1 source_id,
GSOB.sla_ledger_cash_basis_flag cash_basis_flag,
APSP.when_To_Account_pmt,
XPAE.ledger_id ledger_id
FROM XLA_POST_ACCTG_EVENTS_V XPAE,
XLA_TRANSACTION_ENTITIES XTE,
GL_SETS_OF_BOOKS GSOB,
AP_SYSTEM_PARAMETERS_ALL APSP
WHERE XPAE.ledger_id = GSOB.set_of_books_id
AND XPAE.entity_id = XTE.entity_id
AND XTE.application_id = 200
AND XTE.security_id_int_1 = APSP.org_id;
'process all Events building list to update AP_CHECKS for event tyep' ||
l_event_rec.event_type_code ||
'event_id = ' || l_event_rec.event_id;
SELECT APH.matched_flag, AC.status_lookup_code
INTO l_matched_flag, l_status
FROM AP_Payment_History_all APH, AP_CHECKS_all AC
WHERE AC.check_id = APH.check_id
AND APH.accounting_Event_id = l_event_rec.event_id;
'start to update for accrual basis list and count=' ||
l_accrual_event_ids.COUNT;
UPDATE AP_Invoice_Payments_all
SET Posted_Flag = 'Y', Accrual_Posted_Flag = 'Y'
WHERE Accounting_Event_ID = l_accrual_event_ids(num);
UPDATE AP_Invoice_Distributions_all
SET Posted_Flag = 'Y', Accrual_Posted_Flag = 'Y'
WHERE Accounting_Event_ID = l_accrual_event_ids(num)
RETURNING invoice_distribution_id,detail_tax_dist_id
BULK COLLECT INTO l_dbi_key_value_list1,
l_tax_dist_id_list1;
l_dbi_key_value_list1.delete;
l_tax_dist_id_list1.delete;
UPDATE ap_self_assessed_tax_dist_all
SET Posted_Flag = 'Y', Accrual_Posted_Flag = 'Y'
WHERE Accounting_Event_ID = l_accrual_event_ids(num)
RETURNING detail_tax_dist_id
BULK COLLECT INTO l_tax_dist_id_list1;
l_tax_dist_id_list1.delete;
UPDATE AP_Invoice_Payments_all
SET Posted_Flag = 'Y', Accrual_Posted_Flag = 'N', Cash_Posted_Flag = 'Y'
WHERE Accounting_Event_ID = l_cash_event_ids(num);
'update for cash basis prepay event list by event and count=' ||
l_prepay_event_list.COUNT;
UPDATE AP_Invoice_Distributions_ALL AID
SET AID.Posted_Flag = 'Y',
AID.Accrual_Posted_Flag = 'N',
AID.Cash_Posted_Flag = 'Y'
WHERE AID.Accounting_Event_ID = l_prepay_event_list(num)
RETURNING invoice_distribution_id,detail_tax_dist_id
BULK COLLECT INTO l_dbi_key_value_list1,
l_tax_dist_id_list1;
l_dbi_key_value_list1.delete;
l_tax_dist_id_list1.delete;
'update self_assessed tax dists for cash basis prepay event list by event and count=' ||
l_prepay_event_list.COUNT;
UPDATE AP_SELF_ASSESSED_TAX_DIST_ALL STID
SET STID.Posted_Flag = 'Y',
STID.Accrual_Posted_Flag = 'N',
STID.Cash_Posted_Flag = 'Y'
WHERE STID.Accounting_Event_ID = l_prepay_event_list(num)
RETURNING detail_tax_dist_id
BULK COLLECT INTO l_tax_dist_id_list1;
l_tax_dist_id_list1.delete;
'update for cash basis prepay event list by invoice_id and count=' ||
l_prepay_event_list.COUNT;
UPDATE AP_Invoice_Distributions_all AID
SET AID.Cash_Posted_Flag = Derive_Cash_Posted_Flag (
l_prepay_event_list(num),
AID.Invoice_Distribution_ID,
AID.Amount,
l_curr_calling_sequence)
WHERE AID.Invoice_ID = l_invID_list(num)
AND AID.Prepay_Distribution_ID IS NULL
AND AID.prepay_tax_parent_id IS NULL
AND nvl(AID.cancellation_flag,'N') <> 'Y'
RETURNING invoice_distribution_id,detail_tax_dist_id
BULK COLLECT INTO l_dbi_key_value_list1,
l_tax_dist_id_list1;
l_dbi_key_value_list1.delete;
l_tax_dist_id_list1.delete;
'update for cash basis prepay event list by invoice_id and count=' ||
l_prepay_event_list.COUNT;
UPDATE ap_self_assessed_tax_dist_all STID
SET STID.Cash_Posted_Flag = Derive_Cash_Posted_Flag (
l_prepay_event_list(num),
STID.Invoice_Distribution_ID,
STID.Amount,
l_curr_calling_sequence)
WHERE STID.Invoice_ID = l_invID_list(num)
AND STID.Prepay_Distribution_ID IS NULL
AND nvl(STID.cancellation_flag,'N') <> 'Y'
RETURNING detail_tax_dist_id
BULK COLLECT INTO l_tax_dist_id_list1;
l_tax_dist_id_list1.delete;
'update for cash basis payclear event list and count=' ||
l_payclear_event_list.COUNT;
UPDATE AP_Invoice_Distributions_all AID --Bug 4659793
SET AID.Posted_Flag = 'Y', AID.Accrual_Posted_Flag = 'N' ,
AID.Cash_Posted_Flag = Derive_Cash_Posted_Flag (
l_payclear_event_list(num),
AID.Invoice_Distribution_ID,
AID.Amount,
l_curr_calling_sequence),
AID.amount_to_post = AID.amount
-nvl(Get_Amt_Already_Accounted(
l_payclear_event_list(num),
-1,
AID.invoice_distribution_id,
'SQL'),0)
WHERE AID.Invoice_ID IN (SELECT AIP.invoice_id
FROM Ap_Invoice_Payments_All AIP
WHERE AIP.Accounting_Event_ID
= l_payclear_event_list(num))
AND AID.Prepay_Distribution_ID IS NULL
AND AID.prepay_tax_parent_id IS NULL
AND nvl(AID.cancellation_flag,'N') <> 'Y' -- Bug 2587500
RETURNING invoice_distribution_id,detail_tax_dist_id
BULK COLLECT INTO l_dbi_key_value_list1,
l_tax_dist_id_list1;
l_dbi_key_value_list1.delete;
l_tax_dist_id_list1.delete;
'update self_assessed tax dists for cash basis payclear event list and count=' ||
l_payclear_event_list.COUNT;
UPDATE ap_self_assessed_tax_dist_all STID
SET STID.Posted_Flag = 'Y', STID.Accrual_Posted_Flag = 'N' ,
STID.Cash_Posted_Flag = Derive_Cash_Posted_Flag (
l_payclear_event_list(num),
STID.Invoice_Distribution_ID,
STID.Amount,
l_curr_calling_sequence)
WHERE STID.Invoice_ID IN (SELECT AIP.invoice_id
FROM Ap_Invoice_Payments_All AIP
WHERE AIP.Accounting_Event_ID
= l_payclear_event_list(num))
AND STID.Prepay_Distribution_ID IS NULL
AND nvl(STID.cancellation_flag,'N') <> 'Y'
RETURNING detail_tax_dist_id
BULK COLLECT INTO l_tax_dist_id_list1;
l_tax_dist_id_list1.delete;
'update for cash basis other payment event list and count=' ||
l_other_event_list.COUNT;
UPDATE AP_Invoice_Distributions_all AID --Bug 4659793
SET AID.Posted_Flag = 'Y', AID.Accrual_Posted_Flag = 'N' ,
AID.Cash_Posted_Flag = Derive_Cash_Posted_Flag (
l_other_event_list(num),
AID.Invoice_Distribution_ID,
AID.Amount,
l_curr_calling_sequence)
WHERE AID.Invoice_ID IN (SELECT AIP.invoice_id
FROM Ap_Invoice_Payments_All AIP
WHERE AIP.Accounting_Event_ID
= l_other_event_list(num))
AND AID.Prepay_Distribution_ID IS NULL
AND AID.prepay_tax_parent_id IS NULL
AND nvl(AID.cancellation_flag,'N') <> 'Y'
RETURNING invoice_distribution_id,detail_tax_dist_id
BULK COLLECT INTO l_dbi_key_value_list1,
l_tax_dist_id_list1;
l_dbi_key_value_list1.delete;
l_tax_dist_id_list1.delete;
'update self_assessed tax dists for cash basis other payment event list and count=' ||
l_other_event_list.COUNT;
UPDATE ap_self_assessed_tax_dist_all STID
SET STID.Posted_Flag = 'Y', STID.Accrual_Posted_Flag = 'N' ,
STID.Cash_Posted_Flag = Derive_Cash_Posted_Flag (
l_other_event_list(num),
STID.Invoice_Distribution_ID,
STID.Amount,
l_curr_calling_sequence)
WHERE STID.Invoice_ID IN (SELECT AIP.invoice_id
FROM Ap_Invoice_Payments_All AIP
WHERE AIP.Accounting_Event_ID
= l_other_event_list(num))
AND STID.Prepay_Distribution_ID IS NULL
AND nvl(STID.cancellation_flag,'N') <> 'Y'
RETURNING detail_tax_dist_id
BULK COLLECT INTO l_tax_dist_id_list1;
l_tax_dist_id_list1.delete;
'Update the all payment history records POSTED_FLAGs.';
UPDATE ap_payment_history_all APH
SET APH.posted_flag = 'Y'
WHERE APH.accounting_event_id = l_event_list(num);
'Update the prepayment history records POSTED_FLAGs';
UPDATE ap_prepay_history_all APPH
SET APPH.posted_flag = 'Y'
WHERE APPH.accounting_event_id = l_event_list(num);
'Update the check staus for l_check_status_list and count=' ||
l_check_id_list.COUNT;
UPDATE AP_Checks_All
SET status_lookup_code = l_check_status_list(num)
WHERE check_id = l_check_id_list(num)
AND not exists (select 1 from ap_payment_history
where check_id=l_check_id_list(num)
and posted_flag<>'Y');
l_debug_info :='Need to call eTax api to update the posted flag';
zx_api_pub.update_posting_flag(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_return_status_service,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_tax_dist_id_tbl => l_tax_dist_id_final_list );
l_tax_dist_id_final_list.DELETE;
l_process_list.DELETE;
l_event_list.DELETE;
l_accrual_event_ids.DELETE;
l_cash_event_ids.DELETE;
l_prepay_event_list.DELETE;
l_payclear_event_list.DELETE;
l_other_event_list.DELETE;
l_invID_list.DELETE;
l_check_status_list.DELETE;
l_check_id_list.DELETE;
UPDATE ap_payment_history_All APH
SET POSTED_FLAG = 'N'
WHERE APH.accounting_event_id in
( select event_id from xla_events_gt);
UPDATE ap_invoice_distributions_All AID
SET POSTED_FLAG = 'N'
WHERE AID.accounting_event_id in
( select event_id from xla_events_gt);
UPDATE ap_self_assessed_tax_dist_all AID
SET POSTED_FLAG = 'N'
WHERE AID.accounting_event_id in
( select event_id from xla_events_gt);
UPDATE ap_invoice_payments_all AIP
SET POSTED_FLAG = 'N'
WHERE AIP.accounting_event_id in
( select event_id from xla_events_gt);
UPDATE ap_prepay_history_all APPH
SET APPH.posted_flag = 'N'
WHERE APPH.accounting_event_id in
( select event_id from xla_events_gt);
SELECT XEG.event_id
FROM xla_events_gt XEG
WHERE XEG.application_id = 200;
l_debug_info := 'About to call lock_documents to update posted flag';
| parameter is purely informational. This procedure selects from the
| XLA_ENTITY_EVENTS_V view, which does not include events incompatible
| with this parameter. This parameter will never be NULL.
| p_process_category
| This parameter is the "process category" of the events to account.
| This parameter is purely informational. This procedure selects from
| the XLA_ENTITY_EVENTS_V view, which does not include events
| incompatible with this parameter.Possible values are as following:
| +------------+-------------------------------+
| | Value | Meaning |
| +------------+-------------------------------+
| | 'Invoices' | process invoices |
| | 'Payments' | process payments and receipts |
| | 'All' | process everything |
| +------------+-------------------------------+
| p_end_date
| This parameter is the maximum event date of the events to be processed
| in this run of the accounting. This procedure selects from the
| XLA_ENTITY_EVENTS_V view, which does not include events incompatible
| with this parameter. This parameter will never be NULL.
| p_accounting_mode
| This parameter is the "accounting mode" that the accounting is being
| run in. This parameter will never be NULL.
| +-------+-------------------------------------------------------------+
| | Value | Meaning |
| +-------+-------------------------------------------------------------+
| | 'D' | The accounting is being run in "draft mode". Draft mode is |
| | | used to examine what the accounting entries would look for |
| | | an event without actually creating the accounting entries. |
| | 'F' | The accounting is being run in "final mode". Final mode is |
| | | used to create accounting entries. |
| +-------+-------------------------------------------------------------+
| p_valuation_method
| This parameter is unused by AP. This parameter is purely informational
| This procedure selects from the XLA_ENTITY_EVENTS_V view, which does
| not include events incompatible with this parameter.
| p_security_id_int_1
| This parameter is unused by AP.
| p_security_id_int_2
| This parameter is unused by AP.
| p_security_id_int_3
| This parameter is unused by AP.
| p_security_id_char_1
| This parameter is unused by AP.
| p_security_id_char_2
| This parameter is unused by AP.
| p_security_id_char_3
| This parameter is unused by AP.
| p_report_request_id
| This parameter is the concurrent request ID of the concurrent request
| that is this run of the accounting. This parameter is used to specify
| which events in the XLA_ENTITY_EVENTS_V view are to be accounted in
| this run of the accounting. This parameter will never be NULL.
|
| KNOWN ISSUES:
|
| NOTES:
| 1) This procedure is run in final mode and draft mode.
| 2) This procedure is run in batch mode but not in document mode.
| 3) This procedure is in its own commit cycle.
|
| MODIFICATION HISTORY
| Date Author Description of Change
|
*===========================================================================*/
PROCEDURE postaccounting
(
p_application_id IN NUMBER,
p_ledger_id IN INTEGER,
p_process_category IN VARCHAR2,
p_end_date IN DATE,
p_accounting_mode IN VARCHAR2,
p_valuation_method IN VARCHAR2,
p_security_id_int_1 IN INTEGER,
p_security_id_int_2 IN INTEGER,
p_security_id_int_3 IN INTEGER,
p_security_id_char_1 IN VARCHAR2,
p_security_id_char_2 IN VARCHAR2,
p_security_id_char_3 IN VARCHAR2,
p_report_request_id IN INTEGER
)
IS
TYPE l_event_ids_typ IS
TABLE OF NUMBER(15)
INDEX BY PLS_INTEGER;
SELECT XEE.event_id, XEE.event_status_code
FROM xla_events XEE
WHERE XEE.application_id = 200
AND XEE.request_id = p_report_request_id
AND XEE.event_status_code <> 'P';
'Update the payment distributions'' POSTED_FLAGs.';
UPDATE ap_payment_history_all APH
SET APH.POSTED_FLAG = CASE WHEN l_event_status(i) = 'U'
AND EXISTS(SELECT 1
FROM ap_system_parameters asp
WHERE asp.when_to_account_pmt = 'CLEARING ONLY'
-- AND asp.org_id = l_org_ids(i)
AND asp.org_id = aph.org_id
AND aph.accounting_event_id = l_event_ids(i)
AND aph.transaction_type in ('PAYMENT CREATED', 'PAYMENT MATURITY')
)
THEN 'Y'
WHEN l_event_status(i) = 'N' -- Bug 7374984
THEN 'Y'
ELSE 'N'
END
WHERE APH.accounting_event_id = l_event_ids(i);
'Update the prepayment header'' POSTED_FLAGs.';
UPDATE ap_prepay_history_all APPH
SET APPH.posted_flag = 'N'
WHERE APPH.accounting_event_id = l_event_ids(i);
'Update the payments'' POSTED_FLAGs.';
UPDATE ap_invoice_payments_all AIP
SET AIP.POSTED_FLAG = CASE WHEN l_event_status(i) = 'U'
AND EXISTS(SELECT 1
FROM ap_system_parameters asp, ap_payment_history_all aph
WHERE asp.when_to_account_pmt = 'CLEARING ONLY'
--AND asp.org_id = l_org_id(i)
AND asp.org_id = aph.org_id
AND aph.accounting_event_id = l_event_ids(i)
AND aph.transaction_type in ('PAYMENT CREATED', 'PAYMENT MATURITY')
)
THEN 'Y'
ELSE 'N'
END
WHERE AIP.accounting_event_id = l_event_ids(i);
'Update the distributions'' POSTED_FLAGs.';
UPDATE ap_invoice_distributions_all AID
SET AID.posted_flag = 'N'
WHERE AID.accounting_event_id = l_event_ids(i);
UPDATE AP_SELF_ASSESSED_TAX_DIST_ALL STID
SET STID.posted_flag = 'N'
WHERE STID.accounting_event_id = l_event_ids(i);
SELECT APH.Transaction_Type
INTO l_transaction_type
FROM AP_Payment_History_All APH
WHERE APH.Accounting_Event_ID = P_Event_ID;
SELECT SUM(APHD.Invoice_Dist_Amount)
INTO l_paid_acctd_amt
FROM AP_Payment_Hist_Dists APHD,
AP_Payment_History_All APH
WHERE APHD.Invoice_Distribution_ID = p_invoice_distribution_id
AND APH.Posted_Flag = 'Y'
AND APH.Payment_History_ID = APHD.Payment_History_ID
AND APH.Transaction_Type IN ('PAYMENT CLEARING', 'PAYMENT UNCLEARING',
'PAYMENT CLEARING ADJUSTED');
SELECT SUM(APHD.Invoice_Dist_Amount)
INTO l_paid_acctd_amt
FROM AP_Payment_Hist_Dists APHD,
AP_Payment_History_All APH
WHERE APHD.Invoice_Distribution_ID = p_invoice_distribution_id
AND APH.Posted_Flag = 'Y'
AND APH.Payment_History_ID = APHD.Payment_History_ID
AND APH.Transaction_Type IN ('PAYMENT MATURITY', 'PAYMENT MATURITY REVERSED',
'PAYMENT MATURITY ADJUSTED');
SELECT SUM(APHD.Invoice_Dist_Amount)
INTO l_paid_acctd_amt
FROM AP_Payment_Hist_Dists APHD,
AP_Payment_History_All APH
WHERE APHD.Invoice_Distribution_ID = p_invoice_distribution_id
AND APH.Posted_Flag = 'Y'
AND APH.Payment_History_ID = APHD.Payment_History_ID
AND APH.Transaction_Type IN ('PAYMENT CREATED', 'PAYMENT CANCELLED', 'PAYMENT ADJUSTED',
'MANUAL PAYMENT ADJUSTED',
'REFUND RECORDED', 'REFUND ADJUSTED', 'REFUND CANCELLED');
SELECT SUM(APAD.Amount)
INTO l_prepaid_acctd_amt
FROM AP_Prepay_App_Dists APAD,
AP_Prepay_History_All APH
WHERE APAD.Invoice_Distribution_ID = p_invoice_distribution_id
AND APAD.Prepay_History_ID = APH.Prepay_History_ID
AND APH.Posted_Flag = 'Y';