[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| FUNCTION - UPDATE_AR_ACCT_DATE
|
| DESCRIPTION
| This function is used to sweep invoice, receipt, adjustment,
| bills receivable distributions to an open date in next accounting
| period that are unaccounted in the current period.
|
|
| PRAMETERS
|
|
| KNOWN ISSUES:
|
| NOTES:
|
| MODIFICATION HISTORY
| Date Author Description of Change
*===========================================================================*/
FUNCTION update_ar_acct_date RETURN BOOLEAN IS
ln_conc_program_id fnd_concurrent_programs.concurrent_program_id%TYPE;
SELECT concurrent_program_id
INTO ln_conc_program_id
FROM fnd_concurrent_programs
WHERE concurrent_program_name='ARTRXSWP';
UPDATE ra_cust_trx_line_gl_dist_all
SET gl_date = g_sweep_to_date,
program_id = ln_conc_program_id,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.user_id
WHERE cust_trx_line_gl_dist_id in (SELECT gt.cust_trx_line_gl_dist_id
FROM ar_period_close_excps_gt gt
WHERE gt.document_type = G_SRC_TYP_UNACCT_TRX
AND EXISTS
( SELECT 'X' FROM AR_SYSTEM_PARAMETERS where org_id = gt.org_id))
AND posting_control_id = -3;
arp_standard.debug ('update_ar_acct_date: total records updated in ra_cust_trx_line_gl_dist_all: '||sql%rowcount);
UPDATE ar_receivable_applications_all
SET gl_date = g_sweep_to_date,
program_id = ln_conc_program_id,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.user_id
WHERE receivable_application_id in (SELECT gt.dist_source_id
FROM ar_period_close_excps_gt gt
WHERE gt.document_type = G_SRC_TYP_UNACCT_TRX
AND gt.dist_source_table = 'RA'
AND EXISTS
( SELECT 'X' FROM AR_SYSTEM_PARAMETERS where org_id = gt.org_id))
AND posting_control_id = -3;
arp_standard.debug ('update_ar_acct_date: total records updated in ar_receivable_applications_all: '||sql%rowcount);
UPDATE ar_cash_receipt_history_all
SET gl_date = g_sweep_to_date,
program_id = ln_conc_program_id,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.user_id
WHERE cash_receipt_history_id in (SELECT gt.dist_source_id
FROM ar_period_close_excps_gt gt
WHERE gt.document_type = G_SRC_TYP_UNACCT_RCT
AND gt.dist_source_table = 'CRH'
AND EXISTS
( SELECT 'X' FROM AR_SYSTEM_PARAMETERS where org_id = gt.org_id))
AND posting_control_id = -3;
arp_standard.debug ('update_ar_acct_date: total records updated in ar_cash_receipt_history_all: '||sql%rowcount);
UPDATE ar_receivable_applications_all
SET gl_date = g_sweep_to_date,
program_id = ln_conc_program_id,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.user_id
WHERE receivable_application_id in (SELECT gt.dist_source_id
FROM ar_period_close_excps_gt gt
WHERE gt.document_type = G_SRC_TYP_UNACCT_RCT
AND gt.dist_source_table = 'RA'
AND EXISTS
( SELECT 'X' FROM AR_SYSTEM_PARAMETERS where org_id = gt.org_id))
AND posting_control_id = -3;
arp_standard.debug ('update_ar_acct_date: total records updated in ar_receivable_applications_all: '||sql%rowcount);
UPDATE ar_misc_cash_distributions_all
SET gl_date = g_sweep_to_date,
program_id = ln_conc_program_id,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.user_id
WHERE misc_cash_distribution_id in (SELECT gt.dist_source_id
FROM ar_period_close_excps_gt gt
WHERE gt.document_type = G_SRC_TYP_UNACCT_RCT
AND gt.dist_source_table = 'MCD'
AND EXISTS
( SELECT 'X' FROM AR_SYSTEM_PARAMETERS where org_id = gt.org_id))
AND posting_control_id = -3;
arp_standard.debug ('update_ar_acct_date: total records updated in ar_misc_cash_distributions_all: '||sql%rowcount);
UPDATE ar_adjustments_all
SET gl_date = g_sweep_to_date,
program_id = ln_conc_program_id,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.user_id
WHERE adjustment_id in (SELECT gt.dist_source_id
FROM ar_period_close_excps_gt gt
WHERE gt.document_type = G_SRC_TYP_UNACCT_ADJ
AND gt.dist_source_table = 'ADJ'
AND EXISTS
( SELECT 'X' FROM AR_SYSTEM_PARAMETERS where org_id = gt.org_id))
AND posting_control_id = -3;
arp_standard.debug ('update_ar_acct_date: total records updated in ar_adjustments_all: '||sql%rowcount);
UPDATE ar_transaction_history_all
SET gl_date = g_sweep_to_date,
program_id = ln_conc_program_id,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.user_id
WHERE transaction_history_id in (SELECT gt.dist_source_id
FROM ar_period_close_excps_gt gt
WHERE gt.document_type = G_SRC_TYP_UNACCT_BR
AND gt.dist_source_table = 'TH'
AND EXISTS
( SELECT 'X' FROM AR_SYSTEM_PARAMETERS where org_id = gt.org_id))
AND posting_control_id = -3;
arp_standard.debug ('update_ar_acct_date: total records updated in ar_transaction_history_all: '||sql%rowcount);
<>
-- TRX
update ar_payment_schedules_all ps
set ps.gl_date = ( select nvl(min(gl_date), ps.gl_date)
from ra_cust_trx_line_gl_dist_all
where customer_trx_id = ps.customer_trx_id
and latest_rec_flag = 'Y'),
ps.gl_date_closed = decode(ps.status,
'CL', (SELECT MAX(a.gl_date)
from (
select psi.payment_schedule_id payment_schedule_id, max(ra.gl_date) gl_date
from ar_receivable_applications_all ra, ar_period_close_excps_gt psi
where ra.status = 'APP'
and ra.payment_schedule_id = psi.payment_schedule_id
group by psi.payment_schedule_id
union all
select psi.payment_schedule_id payment_schedule_id, max(ra.gl_date) gl_date
from ar_receivable_applications_all ra, ar_period_close_excps_gt psi
where ra.status = 'APP'
and ra.applied_payment_schedule_id = psi.payment_schedule_id
group by psi.payment_schedule_id
union all
select psi.payment_schedule_id payment_schedule_id, max(adj.gl_date) gl_date
from ar_adjustments_all adj, ar_period_close_excps_gt psi
where adj.status = 'A'
and adj.amount <> 0
and adj.payment_schedule_id = psi.payment_schedule_id
group by psi.payment_schedule_id
) a, ar_payment_schedules_all ps2
where ps2.payment_schedule_id = a.payment_schedule_id
and ps2.payment_schedule_id = ps.payment_schedule_id
group by a.payment_schedule_id
),
ps.gl_date_closed),
last_update_date = sysdate
where payment_schedule_id in (select payment_schedule_id from ar_period_close_excps_gt gt
WHERE EXISTS
( SELECT 'X' FROM AR_SYSTEM_PARAMETERS where org_id = gt.org_id))
and class not in ('PMT', 'BR')
and trunc(ps.gl_date) <> ( select nvl(min(gl_date), ps.gl_date)
from ra_cust_trx_line_gl_dist_all
where customer_trx_id = ps.customer_trx_id
and latest_rec_flag = 'Y'
);
update ar_payment_schedules_all ps
set ps.gl_date_closed = g_sweep_to_date,
last_update_date = sysdate
where payment_schedule_id in (select rai.applied_payment_schedule_id
from ar_receivable_applications_all rai, ar_period_close_excps_gt gt
where rai.payment_schedule_id = gt.payment_schedule_id
and rai.application_type = 'CASH'
and rai.applied_payment_schedule_id is not null
and rai.applied_payment_schedule_id > 0
and gt.document_type = G_SRC_TYP_UNACCT_RCT
and gt.dist_source_table = 'RA'
AND EXISTS
( SELECT 'X' FROM AR_SYSTEM_PARAMETERS where org_id = gt.org_id)
union
select rai.payment_schedule_id
from ar_receivable_applications_all rai, ar_period_close_excps_gt gt
where rai.payment_schedule_id = gt.payment_schedule_id
and rai.application_type = 'CASH'
and gt.document_type = G_SRC_TYP_UNACCT_RCT
and gt.dist_source_table = 'RA'
AND EXISTS
( SELECT 'X' FROM AR_SYSTEM_PARAMETERS where org_id = gt.org_id)
union
select rai.payment_schedule_id
from ar_receivable_applications_all rai, ar_period_close_excps_gt gt
where rai.payment_schedule_id = gt.payment_schedule_id
and rai.application_type = 'CM'
and rai.payment_schedule_id is not null
and gt.document_type = G_SRC_TYP_UNACCT_TRX
and gt.dist_source_table = 'RA'
AND EXISTS
( SELECT 'X' FROM AR_SYSTEM_PARAMETERS where org_id = gt.org_id)
union
select rai.applied_payment_schedule_id
from ar_receivable_applications_all rai, ar_period_close_excps_gt gt
where rai.payment_schedule_id = gt.payment_schedule_id
and rai.application_type = 'CM'
and rai.payment_schedule_id is not null
and gt.document_type = G_SRC_TYP_UNACCT_TRX
and gt.dist_source_table = 'RA'
AND EXISTS
( SELECT 'X' FROM AR_SYSTEM_PARAMETERS where org_id = gt.org_id)
union
select adji.payment_schedule_id
from ar_adjustments_all adji, ar_period_close_excps_gt gt
where adji.payment_schedule_id = gt.payment_schedule_id
and adji.adjustment_id = gt.adjustment_id
and gt.document_type = G_SRC_TYP_UNACCT_ADJ
and gt.dist_source_table = 'ADJ'
AND EXISTS
( SELECT 'X' FROM AR_SYSTEM_PARAMETERS where org_id = gt.org_id))
and ps.status = 'CL'
and ps.gl_date_closed < g_sweep_to_date
;
update ar_payment_schedules_all ps
set ps.gl_date = ( select nvl(min(gl_date), ps.gl_date)
from ar_cash_receipt_history_all
where cash_receipt_id = ps.cash_receipt_id
and first_posted_record_flag = 'Y'),
ps.gl_date_closed = decode(ps.status,
'CL', (SELECT MAX(a.gl_date)
from (
select psi.payment_schedule_id payment_schedule_id, max(ra.gl_date) gl_date
from ar_receivable_applications_all ra, ar_period_close_excps_gt psi
where ra.payment_schedule_id = psi.payment_schedule_id
group by psi.payment_schedule_id
) a, ar_payment_schedules_all ps2
where ps2.payment_schedule_id = a.payment_schedule_id
and ps2.payment_schedule_id = ps.payment_schedule_id
group by a.payment_schedule_id
),
ps.gl_date_closed),
last_update_date = sysdate
where payment_schedule_id in (select payment_schedule_id from ar_period_close_excps_gt gt
WHERE EXISTS
( SELECT 'X' FROM AR_SYSTEM_PARAMETERS where org_id = gt.org_id))
and class = 'PMT'
and trunc(ps.gl_date) <> ( select nvl(min(gl_date), ps.gl_date)
from ar_cash_receipt_history_all
where cash_receipt_id = ps.cash_receipt_id
and first_posted_record_flag = 'Y'
);
update ar_payment_schedules_all ps
set ps.gl_date = ( select nvl(min(gl_date), ps.gl_date)
from ar_transaction_history_all
where customer_trx_id = ps.customer_trx_id
and first_posted_record_flag = 'Y'),
ps.gl_date_closed = decode(ps.status,
'CL', (SELECT MAX(a.gl_date)
from (
select psi.payment_schedule_id payment_schedule_id, max(ra.gl_date) gl_date
from ar_receivable_applications_all ra, ar_period_close_excps_gt psi
where ra.status = 'APP'
and ra.payment_schedule_id = psi.payment_schedule_id
group by psi.payment_schedule_id
union all
select psi.payment_schedule_id payment_schedule_id, max(ra.gl_date) gl_date
from ar_receivable_applications_all ra, ar_period_close_excps_gt psi
where ra.status = 'APP'
and ra.applied_payment_schedule_id = psi.payment_schedule_id
group by psi.payment_schedule_id
union all
select psi.payment_schedule_id payment_schedule_id, max(adj.gl_date) gl_date
from ar_adjustments_all adj, ar_period_close_excps_gt psi
where adj.status = 'A'
and adj.amount <> 0
and adj.payment_schedule_id = psi.payment_schedule_id
group by psi.payment_schedule_id
) a, ar_payment_schedules_all ps2
where ps2.payment_schedule_id = a.payment_schedule_id
and ps2.payment_schedule_id = ps.payment_schedule_id
group by a.payment_schedule_id
),
ps.gl_date_closed),
last_update_date = sysdate
where payment_schedule_id in (select payment_schedule_id from ar_period_close_excps_gt gt
WHERE EXISTS
( SELECT 'X' FROM AR_SYSTEM_PARAMETERS where org_id = gt.org_id))
and class = 'BR'
and trunc(ps.gl_date) <> ( select nvl(min(gl_date), ps.gl_date)
from ar_transaction_history_all
where customer_trx_id = ps.customer_trx_id
and first_posted_record_flag = 'Y'
);
| PROCEDURE - UPDATE_XLA_EVENTS
|
| DESCRIPTION
| This procedure is used to sweep accounting events from one accounting period
| to another.
|
|
| PRAMETERS
|
| KNOWN ISSUES:
|
| NOTES:
|
| MODIFICATION HISTORY
| Date Author Description of Change
*===========================================================================*/
FUNCTION update_xla_events RETURN BOOLEAN IS
TYPE t_event_ids IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
SELECT gt.event_id,
DECODE (gt.document_type
,G_SRC_TYP_UNACCT_TRX, gt.customer_trx_id
,G_SRC_TYP_UNACCT_RCT, gt.cash_receipt_id
,G_SRC_TYP_UNACCT_ADJ, gt.adjustment_id
,G_SRC_TYP_UNACCT_BR, gt.customer_trx_id
) trans_id,
gt.org_id org_id,
gt.legal_entity_id legal_entity_id,
decode (gt.document_type
,G_SRC_TYP_UNACCT_TRX, gt.trx_number
,G_SRC_TYP_UNACCT_RCT, gt.receipt_number
,G_SRC_TYP_UNACCT_ADJ, gt.adjustment_number
,G_SRC_TYP_UNACCT_BR, gt.trx_number
)trans_num,
decode(gt.document_type
,G_SRC_TYP_UNACCT_TRX, 'TRANSACTIONS'
,G_SRC_TYP_UNACCT_RCT, 'RECEIPTS'
,G_SRC_TYP_UNACCT_ADJ, 'ADJUSTMENTS'
,G_SRC_TYP_UNACCT_BR, 'BILLS_RECEIVABLE'
) entity_code
FROM ar_period_close_excps_gt gt
WHERE gt.event_id is NOT NULL
AND gt.document_type <> G_SRC_TYP_OTHER_EXCPS
AND EXISTS
( SELECT 'X' FROM AR_SYSTEM_PARAMETERS where org_id = gt.org_id);
arp_standard.debug ('begin update_xla_events: Bulk fetch cursor c_events');
arp_standard.debug ('update_xla_events: l_event_ids.count='||l_event_ids.count );
SELECT event_id, event_status_code
INTO l_xla_event, l_xla_event_status
FROM xla_events
WHERE event_id = l_event_ids(i)
AND application_id = 222;
XLA_EVENTS_PUB_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 => g_sweep_to_date,
p_event_status_code => NULL,
p_valuation_method => NULL,
p_security_context => l_event_security_context
);
UPDATE xla_ae_headers aeh
SET aeh.accounting_date = g_sweep_to_date,
aeh.period_name = g_sweep_period_name,
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'
AND accounting_entry_status_code <> 'F';
UPDATE xla_ae_lines ael
SET ael.accounting_date = g_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'
AND aeh.accounting_entry_status_code <> 'F');
arp_standard.debug ('end update_xla_events');
arp_standard.debug ('EXCEPTION: update_xla_events: '|| sqlerrm);
l_success := update_xla_events;
arp_standard.debug ('Failure in update_xla_events while updating XLA unaccounted events');
l_success := update_ar_acct_date;
arp_standard.debug ('Failure in update_ar_acct_date while updating payables invoices and payments');
insert into ar_period_close_excps_gt
( document_type
, customer_trx_id
, trx_number
, cash_receipt_id
, receipt_number
, adjustment_id
, adjustment_number
, transaction_history_id
, cust_trx_line_gl_dist_id
, account_class
, dist_line_id
, dist_source_id
, dist_source_table
, dist_source_type
, event_id
, gl_date
, amount_dr
, amount_cr
, acctd_amount_dr
, acctd_amount_cr
, org_id
, legal_entity_id
, currency_code
, customer_id
, payment_schedule_id
, applied_payment_schedule_id
)
select G_SRC_TYP_UNACCT_TRX
, ct.customer_trx_id
, ct.trx_number
, null
, null
, null
, null
, null
, gld.cust_trx_line_gl_dist_id
, gld.account_class
, null
, null
, null
, null
, gld.event_id
, gld.gl_date
, DECODE(gld.account_class, 'REC',decode(sign(gld.amount), -1 , 0 ,gld.amount),
decode(sign(gld.amount), -1, abs(gld.amount),0)
) amount_dr
, DECODE(gld.account_class, 'REC',decode(sign(gld.amount), -1 ,abs(gld.amount),0),
decode(sign(gld.amount), -1, 0,gld.amount)
) amount_cr
, DECODE(gld.account_class, 'REC',decode(sign(gld.acctd_amount), -1 , 0 ,gld.acctd_amount),
decode(sign(gld.acctd_amount), -1, abs(gld.acctd_amount),0)
) acctd_amount_dr
, DECODE(gld.account_class, 'REC',decode(sign(gld.acctd_amount), -1 ,abs(gld.acctd_amount),0),
decode(sign(gld.acctd_amount), -1, 0,gld.acctd_amount)
) acctd_amount_cr
, gld.org_id
, ct.legal_entity_id
, ct.invoice_currency_code
, ct.bill_to_customer_id
, ps.payment_schedule_id
, NULL
from
ra_customer_trx_all ct
,ra_cust_trx_line_gl_dist_all gld
,xla_events xle
,ar_payment_schedules_all ps
WHERE ct.complete_flag = 'Y'
and ct.customer_trx_id = gld.customer_trx_id
and gld.account_set_flag = 'N'
and gld.gl_date between g_period_start_date and g_period_end_date
and gld.posting_control_id = -3
and gld.set_of_books_id = g_ledger_id
and ct.customer_trx_id = ps.customer_trx_id
and xle.event_id = gld.event_id
and xle.event_status_code in ('U')
and xle.process_status_code IN ('I','R');
arp_standard.debug ('Total records inserted in ar_period_close_excps_gt for source_type='||G_SRC_TYP_UNACCT_TRX||' is:'||l_rowcount);
insert into ar_period_close_excps_gt
( document_type
, customer_trx_id
, trx_number
, cash_receipt_id
, receipt_number
, adjustment_id
, adjustment_number
, transaction_history_id
, cust_trx_line_gl_dist_id
, account_class
, dist_line_id
, dist_source_id
, dist_source_table
, dist_source_type
, event_id
, gl_date
, amount_dr
, amount_cr
, acctd_amount_dr
, acctd_amount_cr
, org_id
, legal_entity_id
, currency_code
, customer_id
, payment_schedule_id
, applied_payment_schedule_id
)
select G_SRC_TYP_UNACCT_TRX
, ct.customer_trx_id
, ct.trx_number
, null
, null
, null
, null
, null
, null
, null
, ard.line_id
, ard.source_id
, ard.source_table
, ard.source_type
, ra.event_id
, ra.gl_date
, ard.amount_dr
, ard.amount_cr
, ard.acctd_amount_dr
, ard.acctd_amount_cr
, ra.org_id
, ct.legal_entity_id
, ct.invoice_currency_code
, ct.bill_to_customer_id
, ps.payment_schedule_id
, ra.applied_payment_schedule_id
from ra_customer_trx_all ct
, ar_receivable_applications_all ra
, xla_events xle
, ar_distributions_all ard
, ar_payment_schedules_all ps
WHERE ct.complete_flag = 'Y'
and ct.customer_trx_id = ra.customer_trx_id
and ra.gl_date between g_period_start_date and g_period_end_date
and ra.posting_control_id = -3
and NVL(ra.postable, 'Y') = 'Y'
and ra.set_of_books_id = g_ledger_id
and ra.receivable_application_id = ard.source_id
and ard.source_table = 'RA'
and ct.customer_trx_id = ps.customer_trx_id
and xle.event_id = ra.event_id
and xle.event_status_code in ('U')
and xle.process_status_code IN ('I','R');
arp_standard.debug ('Total records inserted in ar_period_close_excps_gt for source_type='||G_SRC_TYP_UNACCT_TRX||' is:'||l_rowcount);
insert into ar_period_close_excps_gt
( document_type
, customer_trx_id
, trx_number
, cash_receipt_id
, receipt_number
, adjustment_id
, adjustment_number
, transaction_history_id
, cust_trx_line_gl_dist_id
, account_class
, dist_line_id
, dist_source_id
, dist_source_table
, dist_source_type
, event_id
, gl_date
, amount_dr
, amount_cr
, acctd_amount_dr
, acctd_amount_cr
, org_id
, currency_code
, customer_id
, payment_schedule_id
, applied_payment_schedule_id
)
select G_SRC_TYP_UNACCT_RCT
, null
, null
, cr.cash_receipt_id
, cr.receipt_number
, null
, null
, null
, null
, null
, ard.line_id
, ard.source_id
, ard.source_table
, ard.source_type
, crh.event_id
, crh.gl_date
, ard.amount_dr
, ard.amount_cr
, ard.acctd_amount_dr
, ard.acctd_amount_cr
, crh.org_id
, cr.currency_code
, cr.pay_from_customer
, ps.payment_schedule_id
, null
FROM ar_cash_receipts_all cr
, ar_cash_receipt_history_all crh
, xla_events xle
, ar_distributions_all ard
, ar_payment_schedules_all ps
WHERE cr.cash_receipt_id = crh.cash_receipt_id
and cr.set_of_books_id = g_ledger_id
and crh.gl_date between g_period_start_date and g_period_end_date
and crh.posting_control_id = -3
and NVL(crh.postable_flag, 'Y') = 'Y'
and crh.cash_receipt_history_id = ard.source_id
and ard.source_table = 'CRH'
and cr.cash_receipt_id = ps.cash_receipt_id
and xle.event_id = crh.event_id
and xle.event_status_code in ('U')
and xle.process_status_code IN ('I','R');
arp_standard.debug ('Total records inserted in ar_period_close_excps_gt for cash receipts source_type='||G_SRC_TYP_UNACCT_RCT||' for table= CRH is:'||l_rowcount);
insert into ar_period_close_excps_gt
( document_type
, customer_trx_id
, trx_number
, cash_receipt_id
, receipt_number
, adjustment_id
, adjustment_number
, transaction_history_id
, cust_trx_line_gl_dist_id
, account_class
, dist_line_id
, dist_source_id
, dist_source_table
, dist_source_type
, event_id
, gl_date
, amount_dr
, amount_cr
, acctd_amount_dr
, acctd_amount_cr
, org_id
, currency_code
, customer_id
, payment_schedule_id
, applied_payment_schedule_id
)
select G_SRC_TYP_UNACCT_RCT
, null
, null
, cr.cash_receipt_id
, cr.receipt_number
, null
, null
, null
, null
, null
, ard.line_id
, ard.source_id
, ard.source_table
, ard.source_type
, crh.event_id
, crh.gl_date
, ard.amount_dr
, ard.amount_cr
, ard.acctd_amount_dr
, ard.acctd_amount_cr
, crh.org_id
, cr.currency_code
, cr.pay_from_customer
, null
, null
FROM ar_cash_receipts_all cr
, ar_cash_receipt_history_all crh
, xla_events xle
, ar_distributions_all ard
WHERE cr.type = 'MISC'
and cr.cash_receipt_id = crh.cash_receipt_id
and cr.set_of_books_id = g_ledger_id
and crh.gl_date between g_period_start_date and g_period_end_date
and crh.posting_control_id = -3
and NVL(crh.postable_flag, 'Y') = 'Y'
and crh.cash_receipt_history_id = ard.source_id
and ard.source_table = 'CRH'
and xle.event_id = crh.event_id
and xle.event_status_code in ('U')
and xle.process_status_code IN ('I','R');
arp_standard.debug ('Total records inserted in ar_period_close_excps_gt for MCD receipts source_type='||G_SRC_TYP_UNACCT_RCT||' for table= CRH is:'||l_rowcount);
insert into ar_period_close_excps_gt
( document_type
, customer_trx_id
, trx_number
, cash_receipt_id
, receipt_number
, adjustment_id
, adjustment_number
, transaction_history_id
, cust_trx_line_gl_dist_id
, account_class
, dist_line_id
, dist_source_id
, dist_source_table
, dist_source_type
, event_id
, gl_date
, amount_dr
, amount_cr
, acctd_amount_dr
, acctd_amount_cr
, org_id
, currency_code
, customer_id
, payment_schedule_id
, applied_payment_schedule_id
)
select G_SRC_TYP_UNACCT_RCT
, null
, null
, cr.cash_receipt_id
, cr.receipt_number
, null
, null
, null
, null
, null
, ard.line_id
, ard.source_id
, ard.source_table
, ard.source_type
, ra.event_id
, ra.gl_date
, ard.amount_dr
, ard.amount_cr
, ard.acctd_amount_dr
, ard.acctd_amount_cr
, ra.org_id
, cr.currency_code
, cr.pay_from_customer
, ps.payment_schedule_id
, ra.applied_payment_schedule_id
FROM ar_cash_receipts_all cr
, ar_receivable_applications_all ra
, xla_events xle
, ar_distributions_all ard
, ar_payment_schedules_all ps
WHERE cr.cash_receipt_id = ra.cash_receipt_id
and cr.set_of_books_id = g_ledger_id
and ra.gl_date between g_period_start_date and g_period_end_date
and ra.posting_control_id = -3
and NVL(ra.postable, 'Y') = 'Y'
and ra.receivable_application_id = ard.source_id
and ard.source_table = 'RA'
and cr.cash_receipt_id = ps.cash_receipt_id
and xle.event_id = ra.event_id
and xle.event_status_code in ('U')
and xle.process_status_code IN ('I','R');
arp_standard.debug ('Total records inserted in ar_period_close_excps_gt for source_type='||G_SRC_TYP_UNACCT_RCT||' for table= RA is:'||l_rowcount);
insert into ar_period_close_excps_gt
( document_type
, customer_trx_id
, trx_number
, cash_receipt_id
, receipt_number
, adjustment_id
, adjustment_number
, transaction_history_id
, cust_trx_line_gl_dist_id
, account_class
, dist_line_id
, dist_source_id
, dist_source_table
, dist_source_type
, event_id
, gl_date
, amount_dr
, amount_cr
, acctd_amount_dr
, acctd_amount_cr
, org_id
, currency_code
, customer_id
, payment_schedule_id
, applied_payment_schedule_id
)
select G_SRC_TYP_UNACCT_RCT
, null
, null
, cr.cash_receipt_id
, cr.receipt_number
, null
, null
, null
, null
, null
, ard.line_id
, ard.source_id
, ard.source_table
, ard.source_type
, mcd.event_id
, mcd.gl_date
, ard.amount_dr
, ard.amount_cr
, ard.acctd_amount_dr
, ard.acctd_amount_cr
, mcd.org_id
, cr.currency_code
, cr.pay_from_customer
, null
, null
FROM ar_cash_receipts_all cr
, ar_misc_cash_distributions_all mcd
, xla_events xle
, ar_distributions_all ard
WHERE cr.cash_receipt_id = mcd.cash_receipt_id
and cr.set_of_books_id = g_ledger_id
and mcd.gl_date between g_period_start_date and g_period_end_date
and mcd.posting_control_id = -3
and mcd.misc_cash_distribution_id = ard.source_id
and ard.source_table = 'MCD'
and xle.event_id = mcd.event_id
and xle.event_status_code in ('U')
and xle.process_status_code IN ('I','R');
arp_standard.debug ('Total records inserted in ar_period_close_excps_gt for source_type='||G_SRC_TYP_UNACCT_RCT||' for table= MCD is:'||l_rowcount);
insert into ar_period_close_excps_gt
( document_type
, customer_trx_id
, trx_number
, cash_receipt_id
, receipt_number
, adjustment_id
, adjustment_number
, transaction_history_id
, cust_trx_line_gl_dist_id
, account_class
, dist_line_id
, dist_source_id
, dist_source_table
, dist_source_type
, event_id
, gl_date
, amount_dr
, amount_cr
, acctd_amount_dr
, acctd_amount_cr
, org_id
, currency_code
, customer_id
, payment_schedule_id
, applied_payment_schedule_id
)
select G_SRC_TYP_UNACCT_ADJ
, ct.customer_trx_id
, ct.trx_number
, null
, null
, adj.adjustment_id
, adj.adjustment_number
, null
, null
, null
, ard.line_id
, ard.source_id
, ard.source_table
, ard.source_type
, adj.event_id
, adj.gl_date
, ard.amount_dr
, ard.amount_cr
, ard.acctd_amount_dr
, ard.acctd_amount_cr
, adj.org_id
, ct.invoice_currency_code
, ct.bill_to_customer_id
, ps.payment_schedule_id
, null
FROM ar_adjustments_all adj
, ar_distributions_all ard
, xla_events xle
, ra_customer_trx_all ct
, ar_payment_schedules_all ps
WHERE adj.set_of_books_id = g_ledger_id
and adj.gl_date between g_period_start_date and g_period_end_date
and adj.posting_control_id = -3
and NVL(postable, 'Y') = 'Y'
and adj.adjustment_id = ard.source_id
and ard.source_table = 'ADJ'
and adj.customer_trx_id = ct.customer_trx_id
and ct.customer_trx_id = ps.customer_trx_id
and xle.event_id = adj.event_id
and xle.event_status_code in ('U')
and xle.process_status_code IN ('I','R');
arp_standard.debug ('Total records inserted in ar_period_close_excps_gt for source_type='||G_SRC_TYP_UNACCT_ADJ||' is:'||l_rowcount);
insert into ar_period_close_excps_gt
( document_type
, customer_trx_id
, trx_number
, cash_receipt_id
, receipt_number
, adjustment_id
, adjustment_number
, transaction_history_id
, cust_trx_line_gl_dist_id
, account_class
, dist_line_id
, dist_source_id
, dist_source_table
, dist_source_type
, event_id
, gl_date
, amount_dr
, amount_cr
, acctd_amount_dr
, acctd_amount_cr
, org_id
, currency_code
, customer_id
, payment_schedule_id
, applied_payment_schedule_id
)
select G_SRC_TYP_UNACCT_BR
, ct.customer_trx_id
, ct.trx_number
, null
, null
, null
, null
, null
, null
, null
, ard.line_id
, ard.source_id
, ard.source_table
, ard.source_type
, th.event_id
, th.gl_date
, ard.amount_dr
, ard.amount_cr
, ard.acctd_amount_dr
, ard.acctd_amount_cr
, th.org_id
, ct.invoice_currency_code
, ct.drawee_id
, ps.payment_schedule_id
, null
FROM ar_transaction_history_all th
, xla_events xle
, ar_distributions_all ard
, ra_customer_trx_all ct
, ar_payment_schedules_all ps
WHERE th.gl_date between g_period_start_date and g_period_end_date
and th.posting_control_id = -3
and NVL(th.postable_flag, 'Y') = 'Y'
and th.transaction_history_id = ard.source_id
and ard.source_table = 'TH'
and th.customer_trx_id = ct.customer_trx_id
and ct.set_of_books_id = g_ledger_id
and ct.customer_trx_id = ps.customer_trx_id
and xle.event_id = th.event_id
and xle.event_status_code in ('U')
and xle.process_status_code IN ('I','R');
arp_standard.debug ('Total records inserted in ar_period_close_excps_gt for source_type='||G_SRC_TYP_UNACCT_BR||' is:'||l_rowcount);
<>
UPDATE ar_period_close_excps_gt
SET document_type = G_SRC_TYP_OTHER_EXCPS
WHERE customer_trx_id IN (SELECT pce.customer_trx_id
FROM ar_period_close_excps_gt pce, ar_receivable_applications_all ra
WHERE pce.document_type = G_SRC_TYP_UNACCT_TRX
AND pce.account_class = 'REC'
AND pce.customer_trx_id = ra.customer_trx_id
AND ra.gl_date between pce.gl_date and g_period_end_date
AND ra.posting_control_id <> -3
UNION
SELECT pce.customer_trx_id
FROM ar_period_close_excps_gt pce, ar_receivable_applications_all ra
WHERE pce.document_type = G_SRC_TYP_UNACCT_TRX
AND pce.account_class = 'REC'
AND pce.customer_trx_id = ra.applied_customer_trx_id
AND ra.gl_date between pce.gl_date and g_period_end_date
AND ra.posting_control_id <> -3
);
<>
UPDATE ar_period_close_excps_gt
SET document_type = G_SRC_TYP_OTHER_EXCPS
WHERE cash_receipt_id IN (SELECT pce.customer_trx_id
FROM ar_period_close_excps_gt pce, ar_receivable_applications_all ra
WHERE pce.document_type = G_SRC_TYP_UNACCT_RCT
AND ra.receivable_application_id = pce.dist_source_id
AND pce.dist_source_table = 'RA'
AND ra.gl_date between pce.gl_date and g_period_end_date
AND ra.posting_control_id <> -3
UNION
SELECT pce.customer_trx_id
FROM ar_period_close_excps_gt pce, ar_cash_receipt_history_all crh
WHERE pce.document_type = G_SRC_TYP_UNACCT_RCT
AND crh.cash_receipt_history_id = pce.dist_source_id
AND pce.dist_source_table = 'CRH'
AND crh.gl_date between pce.gl_date and g_period_end_date
AND crh.posting_control_id <> -3
);
SELECT start_date, end_date, closing_status
FROM gl_period_statuses
WHERE period_name = p_period_name
AND application_id = G_AR_APPLICATION_ID
AND set_of_books_id = g_ledger_id
AND (p_include_adj_period is null or (nvl(adjustment_period_flag,'N') = p_include_adj_period));
SELECT set_of_books_id
INTO g_ledger_id
FROM ar_system_parameters_all
WHERE org_id = p_reporting_entity_id;
select name into l_ledger from GL_SETS_OF_BOOKS
where set_of_books_id=g_ledger_id ;