[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT start_date, end_date, closing_status
FROM gl_period_statuses
WHERE period_name = cp_period_name
AND application_id = G_AP_APPLICATION_ID
AND set_of_books_id = g_ledger_id
and (cp_include_adj_period is null or (nvl(adjustment_period_flag,'N') = cp_include_adj_period));
select name, (SELECT DECODE(count(1), 0, 'Y', 'N')
FROM xla_ledger_relationships_v xlr,
gl_ledgers gl
WHERE xlr.primary_ledger_id = gsob.set_of_books_id
AND xlr.relationship_enabled_flag = 'Y'
AND gl.sla_ledger_cash_basis_flag <> 'Y'
AND xlr.ledger_id = gl.ledger_id
AND EXISTS (SELECT 1
FROM xla_ledger_options xlo
WHERE application_id = 200
AND DECODE(xlr.ledger_category_code
,'ALC',xlr.ledger_id
,xlo.ledger_id) = xlr.ledger_id
AND DECODE(xlr.ledger_category_code
,'SECONDARY',xlo.capture_event_flag
,'N') = 'N'
AND DECODE(xlr.ledger_category_code
,'ALC','Y'
,xlo.enabled_flag) = 'Y'
)
) sla_ledger_cash_basis_flag
from gl_sets_of_books gsob
where set_of_books_id = g_ledger_id;
SELECT closing_status
INTO g_period_status
FROM gl_period_statuses
WHERE g_period_start_date BETWEEN start_date AND end_date
AND g_period_end_date BETWEEN start_date AND end_date
AND application_id = G_AP_APPLICATION_ID
AND set_of_books_id = g_ledger_id;
insert into ap_org_attributes_gt
(org_name
,org_id
,recon_accounting_flag
,when_to_account_pmt
,set_of_books_id
)
values (r_org.operating_unit_name
,r_org.org_id
,r_org.recon_accounting_flag
,r_org.when_to_account_pmt
,r_org.set_of_books_id
);
insert into ap_period_close_excps_gt
( invoice_id
,invoice_distribution_id
,invoice_payment_id -- 7318763
,accounting_event_id
,accounting_date
,org_id
,invoice_num
,invoice_currency_code
,party_id
,vendor_id
,doc_sequence_value
,voucher_num
,invoice_date
,invoice_amount
,cancelled_date
,match_status_flag
,legal_entity_id
,po_distribution_id
,amount
,detail_tax_dist_id
,invoice_line_number
,source_type
,source_table_name
)
select ai.invoice_id
,aid.invoice_distribution_id
,aid.awt_invoice_payment_id -- 7318763
,aid.accounting_event_id
,aid.accounting_date
,aid.org_id
,ai.invoice_num
,ai.invoice_currency_code
,ai.party_id
,ai.vendor_id
,ai.doc_sequence_value
,ai.voucher_num
,ai.invoice_date
,ai.invoice_amount
,ai.cancelled_date
,aid.match_status_flag
,ai.legal_entity_id
,aid.po_distribution_id
,aid.amount
,aid.detail_tax_dist_id
,aid.invoice_line_number
,G_SRC_TYP_UNACCT_DISTS
,G_SRC_TAB_AP_INV_DISTS_ALL
from
ap_invoices_all ai
,ap_invoice_distributions_all aid
,ap_org_attributes_gt org_gtt
where
ai.invoice_id = aid.invoice_id
and ( aid.accounting_date between g_period_start_date and g_period_end_date)
and aid.posted_flag in ('N' , 'S', 'P') -- N=Not Accounted, S=Selected for Accounting, P=Partially Accounted for CASH based accounting
and aid.set_of_books_id = g_ledger_id
and aid.org_id = org_gtt.org_id
and ( g_action <> G_ACTION_PERIOD_CLOSE
OR (g_action = G_ACTION_PERIOD_CLOSE and ROWNUM = 1 ) -- for period close we just need check if any such record exists
)
and ai.approval_ready_flag <> 'S' --bug 9224843
and not exists
(select 1
from ap_payment_history_all aph
where aph.accounting_event_id = aid.accounting_event_id
and ai.invoice_type_lookup_code = 'INTEREST'
and nvl(org_gtt.when_to_account_pmt, 'ALWAYS') = 'CLEARING ONLY'
and aph.transaction_type not in ('PAYMENT CLEARING', 'PAYMENT UNCLEARING'))
UNION
-- added for the bug11881258
select ai.invoice_id
,aid.invoice_distribution_id
,aid.awt_invoice_payment_id -- 7318763
,aid.bc_event_id
,aid.accounting_date
,aid.org_id
,ai.invoice_num
,ai.invoice_currency_code
,ai.party_id
,ai.vendor_id
,ai.doc_sequence_value
,ai.voucher_num
,ai.invoice_date
,ai.invoice_amount
,ai.cancelled_date
,aid.match_status_flag
,ai.legal_entity_id
,aid.po_distribution_id
,aid.amount
,aid.detail_tax_dist_id
,aid.invoice_line_number
,G_SRC_TYP_UNACCT_DISTS
,G_SRC_TAB_AP_INV_DISTS_ALL
from ap_invoices_all ai
,ap_invoice_distributions_all aid
,ap_org_attributes_gt org_gtt
where ai.invoice_id = aid.invoice_id
and ( aid.accounting_date between g_period_start_date and g_period_end_date)
and aid.posted_flag in ('N' , 'S', 'P')
and aid.bc_event_id is not null
and nvl(aid.encumbered_flag, 'N') in ('N', 'H', 'P')
and aid.set_of_books_id = g_ledger_id
and aid.org_id = org_gtt.org_id
and ( g_action <> G_ACTION_PERIOD_CLOSE
OR (g_action = G_ACTION_PERIOD_CLOSE and ROWNUM = 1 )
)
and ai.approval_ready_flag <> 'S'
and not exists
(select 1
from ap_payment_history_all aph
where aph.accounting_event_id = aid.accounting_event_id
and ai.invoice_type_lookup_code = 'INTEREST'
and nvl(org_gtt.when_to_account_pmt, 'ALWAYS') = 'CLEARING ONLY'
and aph.transaction_type not in ('PAYMENT CLEARING', 'PAYMENT UNCLEARING'))
;
debug ('Total records inserted in ap_period_close_excps_gt for source_type='||G_SRC_TYP_UNACCT_DISTS||' is:'||l_rowcount);
insert into ap_period_close_excps_gt
( invoice_id
,invoice_distribution_id
,accounting_event_id
,accounting_date
,org_id
,invoice_num
,invoice_currency_code
,party_id
,vendor_id
,doc_sequence_value
,voucher_num
,invoice_date
,invoice_amount
,cancelled_date
,match_status_flag
,legal_entity_id
,po_distribution_id
,amount
,detail_tax_dist_id
,source_type
,source_table_name
)
select ai.invoice_id
,astd.invoice_distribution_id
,astd.accounting_event_id
,astd.accounting_date
,astd.org_id
,ai.invoice_num
,ai.invoice_currency_code
,ai.party_id
,ai.vendor_id
,ai.doc_sequence_value
,ai.voucher_num
,ai.invoice_date
,ai.invoice_amount
,ai.cancelled_date
,astd.match_status_flag
,ai.legal_entity_id
,astd.po_distribution_id
,astd.amount
,astd.detail_tax_dist_id
,G_SRC_TYP_UNACCT_DISTS
,G_SRC_TAB_AP_SELF_TAX_DIST_ALL
from
ap_invoices_all ai
,ap_self_assessed_tax_dist_all astd
,ap_org_attributes_gt org_gtt
where
ai.invoice_id = astd.invoice_id
and (astd.accounting_date between g_period_start_date and g_period_end_date )
and astd.posted_flag in ('N' , 'S', 'P') -- N=Not Accounted, S=Selected for Accounting, P=Partially Accounted for CASH based accounting
and astd.set_of_books_id = g_ledger_id
and astd.org_id = org_gtt.org_id
and ( g_action <> G_ACTION_PERIOD_CLOSE
OR (g_action = G_ACTION_PERIOD_CLOSE and ROWNUM = 1 ) -- for period close we just need check if any such record exists
)
and ai.approval_ready_flag <> 'S'
union
-- added for the bug11881258
select ai.invoice_id
,astd.invoice_distribution_id
,astd.bc_event_id
,astd.accounting_date
,astd.org_id
,ai.invoice_num
,ai.invoice_currency_code
,ai.party_id
,ai.vendor_id
,ai.doc_sequence_value
,ai.voucher_num
,ai.invoice_date
,ai.invoice_amount
,ai.cancelled_date
,astd.match_status_flag
,ai.legal_entity_id
,astd.po_distribution_id
,astd.amount
,astd.detail_tax_dist_id
,G_SRC_TYP_UNACCT_DISTS
,G_SRC_TAB_AP_SELF_TAX_DIST_ALL
from
ap_invoices_all ai
,ap_self_assessed_tax_dist_all astd
,ap_org_attributes_gt org_gtt
where
ai.invoice_id = astd.invoice_id
and (astd.accounting_date between g_period_start_date and g_period_end_date )
and astd.posted_flag in ('N' , 'S', 'P')
and astd.bc_event_id is not null
and nvl(astd.encumbered_flag, 'N') in ('N', 'H', 'P')
and astd.set_of_books_id = g_ledger_id
and astd.org_id = org_gtt.org_id
and ( g_action <> G_ACTION_PERIOD_CLOSE
OR (g_action = G_ACTION_PERIOD_CLOSE and ROWNUM = 1 )
)
and ai.approval_ready_flag <> 'S';
debug ('Total records inserted in ap_period_close_excps_gt for source_type='||G_SRC_TYP_UNACCT_DISTS||'for table='||G_SRC_TAB_AP_SELF_TAX_DIST_ALL
|| ' is:'||l_rowcount);
/* bug 11702640 Made changes to the select statement below */
insert into ap_period_close_excps_gt
( invoice_id
,invoice_line_number
,accounting_date
,org_id
,invoice_num
,invoice_currency_code
,party_id
,vendor_id
,doc_sequence_value
,voucher_num
,invoice_date
,invoice_amount
,cancelled_date
,source_type
,source_table_name
)
select /*+ leading(org_gtt,ail,aid,ai) */ ai.invoice_id
,ail.line_number
,ail.accounting_date
,ail.org_id
,ai.invoice_num
,ai.invoice_currency_code
,ai.party_id
,ai.vendor_id
,ai.doc_sequence_value
,ai.voucher_num
,ai.invoice_date
,ai.invoice_amount
,ai.cancelled_date
,G_SRC_TYP_LINES_WITHOUT_DISTS
,G_SRC_TAB_AP_INV_LINES_ALL
from
ap_invoices_all ai
,ap_invoice_lines_all ail
,ap_org_attributes_gt org_gtt
where
ai.invoice_id = ail.invoice_id
and (ail.accounting_date between g_period_start_date and g_period_end_date)
and not exists (select /*+ nl_aj */ 1 --> lines without distributions
from ap_invoice_distributions_all aid
where aid.invoice_id = ail.invoice_id
and aid.invoice_line_number = ail.line_number
and aid.org_id = org_gtt.org_id
)
--Bug 7242216 Excluding invoices having discarded lines with
--no distributions
and ail.discarded_flag <> 'Y'
/* Bug 14660916 */
and ( (ail.amount <> 0 )
OR
(ail.amount = 0
and (ail.default_dist_ccid is not NULL
or ail.distribution_set_id is not null ))) /* Bug 14660916 */
and ai.cancelled_date is null
and ail.set_of_books_id = g_ledger_id
and ail.org_id = org_gtt.org_id
and ( g_action <> G_ACTION_PERIOD_CLOSE
OR (g_action = G_ACTION_PERIOD_CLOSE and ROWNUM = 1 ) -- for period close we just need check if any such record exists
)
and ai.approval_ready_flag <> 'S'; --bug 9224843
debug ('Total records inserted in ap_period_close_excps_gt for source_type='||G_SRC_TYP_LINES_WITHOUT_DISTS||' is:'||l_rowcount);
insert into ap_period_close_excps_gt
( invoice_id
,accounting_event_id
,accounting_date
,org_id
,invoice_num
,invoice_currency_code
,party_id
,vendor_id
,doc_sequence_value
,voucher_num
,invoice_date
,invoice_amount
,cancelled_date
,legal_entity_id
,source_type
,source_table_name
)
select ai.invoice_id
,apph.accounting_event_id
,apph.accounting_date
,ai.org_id
,ai.invoice_num
,ai.invoice_currency_code
,ai.party_id
,ai.vendor_id
,ai.doc_sequence_value
,ai.voucher_num
,ai.invoice_date
,ai.invoice_amount
,ai.cancelled_date
,ai.legal_entity_id
,G_SRC_TYP_UNACCT_PREPAY_HIST
,G_SRC_TAB_AP_PREPAY_HIST
from ap_invoices_all ai
,ap_prepay_history_all apph
,ap_org_attributes_gt org_gtt
where
ai.invoice_id = apph.invoice_id
and ( apph.accounting_date between g_period_start_date and g_period_end_date)
and apph.posted_flag in ('N' , 'S', 'P') -- N=Not Accounted, S=Selected for Accounting, P=Partially Accounted for CASH based accounting
and apph.accounting_event_id IS NOT NULL
and ai.set_of_books_id = g_ledger_id
-- and ai.org_id = org_gtt.org_id commented for 13416897
and apph.org_id = org_gtt.org_id -- new condition for 13416897
and ( g_action <> G_ACTION_PERIOD_CLOSE
OR (g_action = G_ACTION_PERIOD_CLOSE and ROWNUM = 1 ) -- for period close we just need check if any such record exists
);
debug ('Total records inserted in ap_period_close_excps_gt for source_type='||G_SRC_TYP_UNACCT_PREPAY_HIST||' is:'||l_rowcount);
INSERT INTO AP_PERIOD_CLOSE_EXCPS_GT
(payment_history_id
,accounting_event_id
,accounting_date
,check_id
,transaction_type
,org_id
,recon_accounting_flag
,check_number
,exchange_rate
,check_date
,legal_entity_id
,vendor_name
,bank_account_name
,check_amount
,currency_code
,party_id
,vendor_id
,source_type
,source_table_name
)
SELECT aph.payment_history_id,
aph.accounting_event_id,
aph.accounting_date,
aph.check_id,
aph.transaction_type,
aph.org_id,
orgs.recon_accounting_flag,
ac.check_number,
ac.exchange_rate,
ac.check_date,
ac.legal_entity_id,
ac.vendor_name,
ac.bank_account_name,
--ac.amount, --bug 7416004
decode(aph.transaction_type,'PAYMENT CANCELLED',(-1*ac.amount),
'REFUND CANCELLED',(-1*ac.amount),
ac.amount),
ac.currency_code,
ac.party_id,
ac.vendor_id
,G_SRC_TYP_UNACCT_PMT_HISTORY
,G_SRC_TAB_AP_PMT_HISTORY
FROM ap_payment_history_all aph,
ap_checks_all ac,
ap_org_attributes_gt orgs
WHERE aph.posted_flag IN ('N','S')
AND ac.check_id = aph.check_id
and (aph.accounting_date between g_period_start_date and g_period_end_date)
AND aph.org_id = orgs.org_id
AND ( NVL(orgs.when_to_account_pmt, 'ALWAYS') = 'ALWAYS' or
(NVL(orgs.when_to_account_pmt, 'ALWAYS') = 'CLEARING ONLY' and
aph.transaction_type in ('PAYMENT CLEARING', 'PAYMENT UNCLEARING')))
and ( g_action <> G_ACTION_PERIOD_CLOSE
OR (g_action = G_ACTION_PERIOD_CLOSE and ROWNUM = 1 ) -- for period close we just need check if any such record exists
);
debug ('Total records inserted in ap_period_close_excps_gt for source_type='||G_SRC_TYP_UNACCT_PMT_HISTORY||' is:'||l_rowcount);
insert into ap_period_close_excps_gt
(invoice_payment_id
,accounting_event_id
,accounting_date
,check_id
,payment_amount
,org_id
,recon_accounting_flag
,check_number
,exchange_rate
,check_date
,legal_entity_id
,vendor_name
,bank_account_name
,check_amount
,currency_code
,status_lookup_code
,party_id
,vendor_id
,source_type
,source_table_name
)
SELECT aip.invoice_payment_id,
aip.accounting_event_id,
aip.accounting_date,
aip.check_id,
aip.amount,
aip.org_id,
orgs.recon_accounting_flag,
ac.check_number,
ac.exchange_rate,
ac.check_date,
ac.legal_entity_id,
ac.vendor_name,
ac.bank_account_name,
ac.amount,
ac.currency_code,
ac.status_lookup_code,
ac.party_id,
ac.vendor_id
,G_SRC_TYP_UNACCT_INV_PMTS
,G_SRC_TAB_AP_INV_PAYMENTS
FROM ap_invoice_payments_all aip,
ap_checks_All ac,
ap_org_attributes_gt orgs
WHERE aip.posted_flag IN ('N','S')
and (aip.accounting_date between g_period_start_date and g_period_end_date)
AND aip.org_id = orgs.org_id
AND ac.check_id = aip.check_id
AND NVL(orgs.when_to_account_pmt, 'ALWAYS') = 'ALWAYS';
debug ('Total records inserted in ap_period_close_excps_gt for source_type='||G_SRC_TYP_UNACCT_INV_PMTS||' is:'||l_rowcount);
/*Bug#7649020: Fetching data from SLA cursors and inserting into GT tables.
* If action is period closure returing after check of one record.
* If action is other than period closure (UTR,PCER,SWEEP), inserting
* data fetched by cursor into GT table.
*
*Bug#8240910: SLA cursor modified to fetch data over a date range instead
* of period name as the reports can be submitted over any date range and
* not specifically over a period. Modified the call to SLA cursor to pass
* start date and end date instead of passing period name
*/
IF g_action = G_ACTION_PERIOD_CLOSE THEN
OPEN xla_period_close_exp_pkg.period_close_hdr_date_cur(200,g_ledger_id,g_period_start_date,g_period_end_date);
INSERT INTO ap_period_close_excps_gt
(accounting_event_id
,accounting_date
,org_id
,legal_entity_id
,invoice_num
,invoice_id
,invoice_date
,check_number
,check_id
,check_date
,event_type_code
,entity_code
,source_type
,source_table_name
,party_id /*Bug 9721897*/
,vendor_id /*Bug 9721897*/
) values
(xla_headers_untransfered(i).event_id
,xla_headers_untransfered(i).event_date
,xla_headers_untransfered(i).security_id_int_1
,xla_headers_untransfered(i).legal_entity_id
,CASE WHEN xla_headers_untransfered(i).entity_code IN ('AP_INVOICES','MANUAL')
THEN xla_headers_untransfered(i).transaction_number
ELSE NULL END
,CASE WHEN xla_headers_untransfered(i).entity_code IN ('AP_INVOICES','MANUAL')
THEN xla_headers_untransfered(i).source_id_int_1
ELSE NULL END
,CASE WHEN xla_headers_untransfered(i).entity_code IN ('AP_INVOICES','MANUAL')
THEN xla_headers_untransfered(i).transaction_date
ELSE NULL END
,decode(xla_headers_untransfered(i).entity_code,'AP_PAYMENTS',xla_headers_untransfered(i).transaction_number,NULL)
,decode(xla_headers_untransfered(i).entity_code,'AP_PAYMENTS',xla_headers_untransfered(i).source_id_int_1,NULL)
,decode(xla_headers_untransfered(i).entity_code,'AP_PAYMENTS',xla_headers_untransfered(i).transaction_date,NULL)
,xla_headers_untransfered(i).event_type_code
,xla_headers_untransfered(i).entity_code
,G_SRC_TYP_UNTRANSFERED_HEADERS
,G_SRC_TAB_XLA_AE_HEADERS
,(select party_id from ap_invoices_all
where invoice_id = xla_headers_untransfered(i).source_id_int_1
and 'AP_INVOICES' = xla_headers_untransfered(i).entity_code
union
select party_id from ap_checks_all
where check_id = xla_headers_untransfered(i).source_id_int_1
and 'AP_PAYMENTS' = xla_headers_untransfered(i).entity_code) /*Bug 9721897*/
,(select vendor_id from ap_invoices_all
where invoice_id = xla_headers_untransfered(i).source_id_int_1
and 'AP_INVOICES' = xla_headers_untransfered(i).entity_code
union
select vendor_id from ap_checks_all
where check_id = xla_headers_untransfered(i).source_id_int_1
and 'AP_PAYMENTS' = xla_headers_untransfered(i).entity_code) /*Bug 9721897*/
);
INSERT WHEN NOT EXISTS (SELECT accounting_event_id
FROM ap_period_close_excps_gt
WHERE accounting_event_id = xla_events_unacct(i).event_id)
AND xla_events_unacct(i).entity_code='AP_INVOICES' THEN
INTO ap_period_close_excps_gt
( invoice_id
,invoice_distribution_id
,invoice_payment_id -- 7318763
,accounting_event_id
,accounting_date
,org_id
,invoice_num
,invoice_currency_code
,party_id
,vendor_id
,doc_sequence_value
,voucher_num
,invoice_date
,invoice_amount
,cancelled_date
,match_status_flag
,legal_entity_id
,po_distribution_id
,amount
,detail_tax_dist_id
,invoice_line_number
,event_type_code
,entity_code
,source_type
,source_table_name
)
SELECT xla_events_unacct(i).source_id_int_1
,aid.invoice_distribution_id
,aid.awt_invoice_payment_id -- 7318763
,aid.accounting_event_id
,aid.accounting_date
,aid.org_id
,xla_events_unacct(i).transaction_number
,ai.invoice_currency_code
,ai.party_id
,ai.vendor_id
,ai.doc_sequence_value
,ai.voucher_num
,xla_events_unacct(i).transaction_date
,ai.invoice_amount
,ai.cancelled_date
,aid.match_status_flag
,xla_events_unacct(i).legal_entity_id
,aid.po_distribution_id
,aid.amount
,aid.detail_tax_dist_id
,aid.invoice_line_number
,xla_events_unacct(i).event_type_code
,xla_events_unacct(i).entity_code
,G_SRC_TYP_OTHER_EXCPS
,G_SRC_TAB_AP_INV_DISTS_ALL
FROM ap_invoices_all ai
,ap_invoice_distributions_all aid
,ap_org_attributes_gt org_gtt
WHERE aid.invoice_id = ai.invoice_id(+)
AND aid.set_of_books_id = g_ledger_id
AND aid.org_id = org_gtt.org_id
AND aid.accounting_event_id = xla_events_unacct(i).event_id
UNION ALL
SELECT xla_events_unacct(i).source_id_int_1
,astd.invoice_distribution_id
,NULL invoice_payment_id
,astd.accounting_event_id
,astd.accounting_date
,astd.org_id
,xla_events_unacct(i).transaction_number
,ai.invoice_currency_code
,ai.party_id
,ai.vendor_id
,ai.doc_sequence_value
,ai.voucher_num
,xla_events_unacct(i).transaction_date
,ai.invoice_amount
,ai.cancelled_date
,astd.match_status_flag
,xla_events_unacct(i).legal_entity_id
,astd.po_distribution_id
,astd.amount
,astd.detail_tax_dist_id
,NULL invoice_line_number
,xla_events_unacct(i).event_type_code
,xla_events_unacct(i).entity_code
,G_SRC_TYP_OTHER_EXCPS
,G_SRC_TAB_AP_SELF_TAX_DIST_ALL
FROM ap_invoices_all ai
,ap_self_assessed_tax_dist_all astd
,ap_org_attributes_gt org_gtt
WHERE astd.invoice_id = ai.invoice_id(+)
AND astd.set_of_books_id = g_ledger_id
AND astd.org_id = org_gtt.org_id
AND astd.accounting_event_id = xla_events_unacct(i).event_id
UNION ALL
SELECT xla_events_unacct(i).source_id_int_1
,NULL invoice_distribution_id
,NULL invoice_payment_id
,apph.accounting_event_id
,apph.accounting_date
,ai.org_id
,xla_events_unacct(i).transaction_number
,ai.invoice_currency_code
,ai.party_id
,ai.vendor_id
,ai.doc_sequence_value
,ai.voucher_num
,xla_events_unacct(i).transaction_date
,ai.invoice_amount
,ai.cancelled_date
,NULL match_status_flag
,NULL po_distribution_id
,NULL amount
,NULL detail_tax_dist_id
,NULL invoice_line_number
,xla_events_unacct(i).legal_entity_id
,xla_events_unacct(i).event_type_code
,xla_events_unacct(i).entity_code
,G_SRC_TYP_OTHER_EXCPS
,G_SRC_TAB_AP_PREPAY_HIST
FROM ap_invoices_all ai
,ap_prepay_history_all apph
,ap_org_attributes_gt org_gtt
WHERE apph.invoice_id = ai.invoice_id(+)
AND apph.accounting_event_id IS NOT NULL
AND ai.set_of_books_id = g_ledger_id
AND apph.org_id = org_gtt.org_id
AND apph.accounting_event_id = xla_events_unacct(i).event_id;
INSERT WHEN NOT EXISTS (SELECT accounting_event_id
FROM ap_period_close_excps_gt
WHERE accounting_event_id = xla_events_unacct(i).event_id)
AND xla_events_unacct(i).entity_code='AP_PAYMENTS' then
INTO AP_PERIOD_CLOSE_EXCPS_GT
(payment_history_id
,accounting_event_id
,accounting_date
,check_id
,transaction_type
,org_id
,recon_accounting_flag
,check_number
,exchange_rate
,check_date
,legal_entity_id
,vendor_name
,bank_account_name
,check_amount
,currency_code
,party_id
,vendor_id
,event_type_code
,entity_code
,source_type
,source_table_name
)
SELECT aph.payment_history_id,
aph.accounting_event_id,
aph.accounting_date,
xla_events_unacct(i).source_id_int_1,
aph.transaction_type,
aph.org_id,
orgs.recon_accounting_flag,
xla_events_unacct(i).transaction_number,
ac.exchange_rate,
xla_events_unacct(i).transaction_date,
xla_events_unacct(i).legal_entity_id,
ac.vendor_name,
ac.bank_account_name,
ac.amount,
ac.currency_code,
ac.party_id,
ac.vendor_id
,xla_events_unacct(i).event_type_code
,xla_events_unacct(i).entity_code
,G_SRC_TYP_OTHER_EXCPS
,G_SRC_TAB_AP_PMT_HISTORY
FROM ap_payment_history_all aph,
ap_checks_all ac,
ap_org_attributes_gt orgs
WHERE aph.check_id = ac.check_id(+)
AND aph.org_id = orgs.org_id
AND ( NVL(orgs.when_to_account_pmt, 'ALWAYS') = 'ALWAYS' or
(NVL(orgs.when_to_account_pmt, 'ALWAYS') = 'CLEARING ONLY' and
aph.transaction_type in ('PAYMENT CLEARING', 'PAYMENT UNCLEARING')))
AND aph.accounting_event_id = xla_events_unacct(i).event_id;
INSERT WHEN NOT EXISTS (SELECT accounting_event_id
FROM ap_period_close_excps_gt
WHERE accounting_event_id = xla_events_unacct(i).event_id)
THEN
INTO ap_period_close_excps_gt
(accounting_event_id
,accounting_date
,org_id
,legal_entity_id
,invoice_num
,invoice_id
,invoice_date
,check_number
,check_id
,check_date
,event_type_code
,entity_code
,source_type
,source_table_name
,party_id /*Bug 9721897*/
,vendor_id /*Bug 9721897*/
)
SELECT xla_events_unacct(i).event_id
,xla_events_unacct(i).event_date
,xla_events_unacct(i).security_id_int_1
,xla_events_unacct(i).legal_entity_id
,CASE WHEN xla_events_unacct(i).entity_code IN ('AP_INVOICES','MANUAL')
THEN xla_events_unacct(i).transaction_number
ELSE NULL END
,CASE WHEN xla_events_unacct(i).entity_code IN ('AP_INVOICES','MANUAL')
THEN xla_events_unacct(i).source_id_int_1
ELSE NULL END
,CASE WHEN xla_events_unacct(i).entity_code IN ('AP_INVOICES','MANUAL')
THEN xla_events_unacct(i).transaction_date
ELSE NULL END
,decode(xla_events_unacct(i).entity_code,'AP_PAYMENTS',xla_events_unacct(i).transaction_number,NULL)
,decode(xla_events_unacct(i).entity_code,'AP_PAYMENTS',xla_events_unacct(i).source_id_int_1,NULL)
,decode(xla_events_unacct(i).entity_code,'AP_PAYMENTS',xla_events_unacct(i).transaction_date,NULL)
,xla_events_unacct(i).event_type_code
,xla_events_unacct(i).entity_code
,G_SRC_TYP_OTHER_EXCPS
,'ORPHAN_EVENTS'
,(select party_id from ap_invoices_all
where invoice_id = xla_events_unacct(i).source_id_int_1
and 'AP_INVOICES' = xla_events_unacct(i).entity_code
union
select party_id from ap_checks_all
where check_id = xla_events_unacct(i).source_id_int_1
and 'AP_PAYMENTS' = xla_events_unacct(i).entity_code) party_id /*Bug 9721897*/
,(select vendor_id from ap_invoices_all
where invoice_id = xla_events_unacct(i).source_id_int_1
and 'AP_INVOICES' = xla_events_unacct(i).entity_code
union
select vendor_id from ap_checks_all
where check_id = xla_events_unacct(i).source_id_int_1
and 'AP_PAYMENTS' = xla_events_unacct(i).entity_code) vendor_id /*Bug 9721897*/
FROM DUAL
where xla_events_unacct(i).security_id_int_1 in (Select org_id
from ap_org_attributes_gt orgs) ; /*Bug 14596406 */
SELECT meaning
into lv_name
FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = 'FND_MO_REPORTING_LEVEL'
and lookup_code = g_reporting_level;
select org_name
from ap_org_attributes_gt
where org_id = g_org_id;
select count(1)
from ap_org_attributes_gt all_orgs
where org_id not in (select org_id from ap_system_parameters);
SELECT 'Y'
FROM ap_inv_selection_criteria_all AISC,
iby_pay_service_requests IPSR ,
ap_selected_invoices_all ASI
WHERE IPSR.call_app_pay_service_req_code (+) = AISC.checkrun_name
AND trunc(aisc.check_date) between g_period_start_date and g_period_end_date
AND DECODE(IPSR.payment_service_request_id, NULL,
AISC.status,
AP_PAYMENT_UTIL_PKG.get_psr_status(IPSR.payment_service_request_id,
IPSR.payment_service_request_status) )
NOT IN ('CONFIRMED','CANCELED','QUICKCHECK', 'CANCELLED NO PAYMENTS', 'TERMINATED')
AND aisc.checkrun_id = asi.checkrun_id
AND asi.org_id in (select org_id org_id from ap_org_attributes_gt org_gtt)
AND rownum = 1;
select 'Y'
from ap_checks_all c
where c.future_pay_due_date is not null
and c.status_lookup_code = 'ISSUED'
and c.future_pay_due_date between g_period_start_date
and g_period_end_date
and c.org_id in (select org_id org_id from ap_org_attributes_gt org_gtt)
and rownum = 1;
select set_of_books_id ledger_id
from ap_system_parameters_all
where org_id = g_org_id;
SELECT min(start_date), max(end_date)
INTO l_min_date,l_max_date
FROM gl_period_statuses
WHERE application_id = G_AP_APPLICATION_ID
AND set_of_books_id = g_ledger_id
AND closing_status in ('C','O','F');
PSA_AP_BC_PVT.delete_events(
p_init_msg_list => 'F',
p_ledger_id => g_ledger_id,
p_start_date => g_period_start_date,
p_end_date => g_period_end_date,
p_calling_sequence => 'ap_period_close_pkg.validate_action',
x_return_status => p_validation_flag,
x_msg_count =>l_msg_count,
x_msg_data => p_validation_message
);
/* select count(*) --commented this peice of code 7318763
into l_count
from ap_invoice_distributions_all
where accounting_event_id = p_event_id
and invoice_id = p_trans_id
and awt_invoice_payment_id is not null; */
select DISTINCT ac.check_id, --bug9649978
ac.check_number
into l_check_id, l_check_number
from ap_invoice_payments_all aip,
ap_checks_all ac
where aip.check_id=ac.check_id
and aip.accounting_event_id = p_event_id
and aip.invoice_id= p_trans_id;
| FUNCTION - UPDATE_PO_CLOSE_DATE
|
| DESCRIPTION
| This function is used to sweep closed date of PO Shipment and Headers
| to an open date in next accounting period for unaccounted invoice
| distributions matched to these shipments.
|
|
| PRAMETERS
|
|
| KNOWN ISSUES:
|
| NOTES:
|
| MODIFICATION HISTORY
| Date Author Description of Change
| 14-MAR-08 PRANPAUL New
*===========================================================================*/
FUNCTION update_po_close_date RETURN BOOLEAN IS
BEGIN
UPDATE po_headers_all POH
SET POH.closed_date = g_sweep_to_date
WHERE po_header_id in (SELECT PLL.PO_HEADER_ID
FROM PO_LINE_LOCATIONS_ALL PLL,
PO_DISTRIBUTIONS_ALL PD,
AP_PERIOD_CLOSE_EXCPS_GT GT
WHERE PLL.LINE_LOCATION_ID = PD.LINE_LOCATION_ID
AND PD.PO_DISTRIBUTION_ID = GT.PO_DISTRIBUTION_ID
AND GT.SOURCE_TYPE = G_SRC_TYP_UNACCT_DISTS
AND GT.SOURCE_TABLE_NAME in ( G_SRC_TAB_AP_INV_DISTS_ALL,
G_SRC_TAB_AP_SELF_TAX_DIST_ALL)
AND ( PLL.CLOSED_DATE IS NOT NULL
AND PLL.CLOSED_DATE < g_sweep_to_date )
GROUP BY PLL.PO_HEADER_ID, GT.PO_DISTRIBUTION_ID
HAVING SUM(GT.AMOUNT) > 0)
AND ( POH.CLOSED_DATE IS NOT NULL
AND POH.CLOSED_DATE < g_sweep_to_date );
debug ('update_po_close_date: total records updated in po_headers_all:'||sql%rowcount);
UPDATE po_line_locations_all
SET closed_date = g_sweep_to_date
WHERE line_location_id in (SELECT PLL.LINE_LOCATION_ID
FROM PO_LINE_LOCATIONS_ALL PLL,
PO_DISTRIBUTIONS_ALL PD,
AP_PERIOD_CLOSE_EXCPS_GT GT
WHERE PLL.LINE_LOCATION_ID = PD.LINE_LOCATION_ID
AND PD.PO_DISTRIBUTION_ID = GT.PO_DISTRIBUTION_ID
AND GT.SOURCE_TYPE = G_SRC_TYP_UNACCT_DISTS
AND GT.SOURCE_TABLE_NAME in ( G_SRC_TAB_AP_INV_DISTS_ALL,
G_SRC_TAB_AP_SELF_TAX_DIST_ALL)
AND ( PLL.CLOSED_DATE IS NOT NULL
AND PLL.CLOSED_DATE < g_sweep_to_date )
GROUP BY PLL.LINE_LOCATION_ID, GT.PO_DISTRIBUTION_ID
HAVING SUM(GT.AMOUNT) > 0);
debug ('update_po_close_date: total records updated in po_line_locations_all:'||sql%rowcount);
debug ('EXCEPTION: update_po_close_date: '||sqlerrm);
| FUNCTION - UPDATE_EBTAX_DISTS
|
| DESCRIPTION
| This function is used to sweep all eBtax distributions to
| to an open date in next accounting period for unaccounted tax
| distributions generated by eBtax.
|
|
| PRAMETERS
|
|
| KNOWN ISSUES:
|
| NOTES:
|
| MODIFICATION HISTORY
| Date Author Description of Change
| 14-MAR-08 PRANPAUL New
*===========================================================================*/
FUNCTION update_ebtax_dists RETURN BOOLEAN IS
l_return_status varchar2(20);
INSERT into ZX_TAX_DIST_ID_GT
(SELECT detail_tax_dist_id
FROM ap_period_close_excps_gt
WHERE detail_tax_dist_id is not null
AND source_type = G_SRC_TYP_UNACCT_DISTS
AND source_table_name in ( G_SRC_TAB_AP_INV_DISTS_ALL,
G_SRC_TAB_AP_SELF_TAX_DIST_ALL));
debug ('update_ebtax_dists: total records inserted in ZX_TAX_DIST_ID_GT: '||sql%rowcount);
ZX_API_PUB.Update_Tax_dist_gl_date (
1.0,
FND_API.G_TRUE,
FND_API.G_FALSE,
FND_API.G_VALID_LEVEL_FULL,
l_return_status,
l_msg_count,
l_msg_data,
g_sweep_to_date );
debug ('update_ebtax_dists: l_return_status='||l_return_status||';l_msg_data='||l_msg_data||';l_msg_count='||l_msg_count );
debug ('EXCEPTION: update_ebtax_dists: '||sqlerrm);
| PROCEDURE - UPDATE_XLA_EVENTS
|
| DESCRIPTION
| This procedure is used to sweep accounting events from one accounting period
| to another.
|
|
| PRAMETERS
|
| p_sweep_to_date: The new event date
| p_calling_sequence: Debug information
|
| KNOWN ISSUES:
|
| NOTES:
|
| MODIFICATION HISTORY
| Date Author Description of Change
| 14-MAR-08 PRANPAUL New
*===========================================================================*/
PROCEDURE update_xla_events (
p_calling_sequence IN VARCHAR2,
p_success OUT NOCOPY BOOLEAN)
IS
TYPE t_event_ids IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
SELECT gt.accounting_event_id accounting_event_id,
decode (gt.source_table_name
,G_SRC_TAB_AP_INV_DISTS_ALL, gt.invoice_id
,G_SRC_TAB_AP_PREPAY_HIST, gt.invoice_id
,G_SRC_TAB_AP_SELF_TAX_DIST_ALL, gt.invoice_id
,G_SRC_TAB_AP_PMT_HISTORY, gt.check_id
) trans_id,
gt.org_id org_id,
gt.legal_entity_id legal_entity_id,
decode (gt.source_table_name
,G_SRC_TAB_AP_INV_DISTS_ALL, gt.invoice_num
,G_SRC_TAB_AP_PREPAY_HIST, gt.invoice_num
,G_SRC_TAB_AP_SELF_TAX_DIST_ALL, gt.invoice_num
,G_SRC_TAB_AP_PMT_HISTORY, gt.check_number
)trans_num,
decode(gt.source_table_name
,G_SRC_TAB_AP_INV_DISTS_ALL, 'INV'
,G_SRC_TAB_AP_PREPAY_HIST, 'INV'
,G_SRC_TAB_AP_SELF_TAX_DIST_ALL, 'INV'
,G_SRC_TAB_AP_PMT_HISTORY,'PMT'
) source
,invoice_payment_id -- 7318763
FROM ap_period_close_excps_gt gt
WHERE gt.source_type in (G_SRC_TYP_UNACCT_DISTS, G_SRC_TYP_UNACCT_PMT_HISTORY,
G_SRC_TYP_UNACCT_PREPAY_HIST)
AND gt.source_table_name in (G_SRC_TAB_AP_INV_DISTS_ALL, G_SRC_TAB_AP_PMT_HISTORY,
G_SRC_TAB_AP_SELF_TAX_DIST_ALL, G_SRC_TAB_AP_PREPAY_HIST)
AND gt.accounting_event_id is NOT NULL
AND NOT EXISTS
(SELECT 'check if invoice dist has payment event'
FROM AP_Payment_History_ALL APH
WHERE APH.Accounting_Event_ID = GT.Accounting_Event_ID
AND GT.Accounting_Event_ID IS NOT NULL
AND GT.Source_Type = G_SRC_TYP_UNACCT_DISTS);
debug ('begin update_xla_events: Bulk fetch cursor c_events');
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 = 200;
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 => g_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 = g_sweep_to_date,
aeh.period_name = g_sweep_to_period,
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');
debug ('end update_xla_events');
debug ('EXCEPTION: update_xla_events: '|| sqlerrm);
END update_xla_events;
| FUNCTION - UPDATE_AP_ACCT_DATE
|
| DESCRIPTION
| This function is used to sweep invoice distributions, lines and
| payment, payment history records 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
| 14-MAR-08 PRANPAUL New
*===========================================================================*/
FUNCTION update_ap_acct_date RETURN BOOLEAN IS
type typ_number_tab is table of number (15) index by binary_integer;
UPDATE ap_invoice_distributions_all aid
SET accounting_date = g_sweep_to_date,
period_name = g_sweep_to_period,
last_update_date = sysdate,
last_updated_by = 5
WHERE aid.invoice_distribution_id in (SELECT gt.invoice_distribution_id
FROM ap_period_close_excps_gt gt
WHERE gt.source_type = G_SRC_TYP_UNACCT_DISTS
AND gt.source_table_name = G_SRC_TAB_AP_INV_DISTS_ALL)
AND aid.posted_flag in ('N','S','P') --Bug 9045217
returning invoice_distribution_id bulk collect into l_dbi_key_value_list;
debug ('update_ap_acct_date: total records updated in ap_invoice_distributions_all: '||sql%rowcount);
update /*+index (gt AP_PERIOD_CLOSE_EXCPS_GT_N3)*/ ap_period_close_excps_gt gt --Bug 9045217
set process_status_flag = 'Y'
where invoice_distribution_id = l_dbi_key_value_list(i)
AND gt.source_type = G_SRC_TYP_UNACCT_DISTS
AND gt.source_table_name = G_SRC_TAB_AP_INV_DISTS_ALL; -- 7318763
p_calling_sequence => 'AP_PERIOD_CLOSE_PKG.update_ap_acct_date');
debug ('update_ap_acct_date: total distributions processed in ap_period_close_excps_gt: '||l_dbi_key_value_list.count);
UPDATE ap_invoice_lines_all ail
SET accounting_date = g_sweep_to_date,
period_name = g_sweep_to_period,
last_update_date = sysdate,
last_updated_by = 5
WHERE (ail.invoice_id, ail.line_number)
in (SELECT /*+index (gt AP_PERIOD_CLOSE_EXCPS_GT_N3)*/ gt.invoice_id, gt.invoice_line_number --Bug 9045217
FROM ap_period_close_excps_gt gt
WHERE gt.invoice_distribution_id = l_dbi_key_value_list(i)
AND gt.source_type = G_SRC_TYP_UNACCT_DISTS
AND gt.source_table_name = G_SRC_TAB_AP_INV_DISTS_ALL); -- 7318763
debug ('update_ap_acct_date: total lines processed in ap_invoice_lines_all: '||l_dbi_key_value_list.count);
l_dbi_key_value_list.delete;
UPDATE ap_self_assessed_tax_dist_all astd
SET accounting_date = g_sweep_to_date,
period_name = g_sweep_to_period,
last_update_date = sysdate,
last_updated_by = 5
WHERE astd.invoice_distribution_id
in (SELECT gt.invoice_distribution_id
FROM ap_period_close_excps_gt gt
WHERE gt.source_type = G_SRC_TYP_UNACCT_DISTS
AND gt.source_table_name = G_SRC_TAB_AP_SELF_TAX_DIST_ALL)
AND astd.posted_flag <> 'Y'
returning invoice_distribution_id bulk collect into ltab_id;
debug ('update_ap_acct_date: total records updated in ap_self_assessed_tax_dist_all: '||sql%rowcount);
update /*+index (gt AP_PERIOD_CLOSE_EXCPS_GT_N3)*/ ap_period_close_excps_gt gt --Bug 9045217
set process_status_flag = 'Y'
where invoice_distribution_id = ltab_id(i)
AND gt.source_type = G_SRC_TYP_UNACCT_DISTS
AND gt.source_table_name = G_SRC_TAB_AP_SELF_TAX_DIST_ALL; -- 7318763
debug ('update_ap_acct_date: total self assessed tax distributions processed in ap_period_close_excps_gt: '||ltab_id.count);
ltab_id.delete;
UPDATE ap_invoice_lines_all ail
SET accounting_date = g_sweep_to_date,
period_name = g_sweep_to_period,
last_update_date = sysdate,
last_updated_by = 5
WHERE (ail.invoice_id,ail.line_number) in
(SELECT gt.invoice_id, gt.invoice_line_number
FROM ap_period_close_excps_gt gt
WHERE gt.source_type = G_SRC_TYP_LINES_WITHOUT_DISTS
AND gt.source_table_name = G_SRC_TAB_AP_INV_LINES_ALL)
returning ail.invoice_id, ail.line_number bulk collect into ltab_id, ltab_line_num;
debug ('update_ap_acct_date: total records updated in ap_invoice_lines_all: '||sql%rowcount);
update /*+index (gt AP_PERIOD_CLOSE_EXCPS_GT_N4)*/ap_period_close_excps_gt gt --Bug 9045217
set process_status_flag = 'Y'
where invoice_id = ltab_id(i)
and invoice_line_number = ltab_line_num(i)
AND gt.source_type =G_SRC_TYP_LINES_WITHOUT_DISTS
AND gt.source_table_name = G_SRC_TAB_AP_INV_LINES_ALL; -- 7318763
debug ('update_ap_acct_date: total invoice lines processed in ap_period_close_excps_gt: '||ltab_id.count );
ltab_id.delete;
UPDATE ap_invoice_payments_all aip
SET accounting_date = g_sweep_to_date,
period_name = g_sweep_to_period,
last_update_date = sysdate,
last_updated_by = 5
WHERE aip.invoice_payment_id in (SELECT gt.invoice_payment_id
FROM ap_period_close_excps_gt gt
WHERE gt.source_type = G_SRC_TYP_UNACCT_INV_PMTS
AND gt.source_table_name = G_SRC_TAB_AP_INV_PAYMENTS)
AND aip.posted_flag <> 'Y'
returning invoice_payment_id, accounting_event_id bulk collect into ltab_id,Itab_event_id; --Bug 9045217
debug ('update_ap_acct_date: total records updated in ap_invoice_payments_all: '||sql%rowcount);
update ap_period_close_excps_gt gt
set process_status_flag = 'Y'
where invoice_payment_id = ltab_id(i)
AND gt.accounting_event_id = Itab_event_id(i) --Bug 9045217
AND gt.source_type = G_SRC_TYP_UNACCT_INV_PMTS
AND gt.source_table_name = G_SRC_TAB_AP_INV_PAYMENTS; -- 7318763
debug ('update_ap_acct_date: total invoice payments processed in ap_period_close_excps_gt: '||ltab_id.count );
ltab_id.delete;
Itab_event_id.delete; --Bug 9045217
UPDATE ap_payment_history_all aph
SET accounting_date = g_sweep_to_date,
last_update_date = sysdate,
last_updated_by = 5
WHERE aph.payment_history_id in (SELECT gt.payment_history_id
FROM ap_period_close_excps_gt gt
WHERE gt.source_type = G_SRC_TYP_UNACCT_PMT_HISTORY
AND gt.source_table_name = G_SRC_TAB_AP_PMT_HISTORY)
AND aph.posted_flag <> 'Y'
returning aph.payment_history_id,aph.accounting_event_id bulk collect into ltab_id,Itab_event_id; --Bug 9045217
debug ('update_ap_acct_date: total records updated in ap_payment_history_all: '||sql%rowcount);
update ap_period_close_excps_gt gt
set process_status_flag = 'Y'
where payment_history_id = ltab_id(i)
AND gt.accounting_event_id = Itab_event_id(i) --Bug 9045217
AND gt.source_type = G_SRC_TYP_UNACCT_PMT_HISTORY
AND gt.source_table_name = G_SRC_TAB_AP_PMT_HISTORY; -- 7318763
debug ('update_ap_acct_date: total payment history processed in ap_period_close_excps_gt: '||ltab_id.count );
ltab_id.delete;
Itab_event_id.delete; --Bug 9045217
UPDATE ap_prepay_history_all apph
SET accounting_date = g_sweep_to_date,
last_update_date = sysdate,
last_updated_by = 5
WHERE apph.accounting_event_id in (SELECT gt.accounting_event_id
FROM ap_period_close_excps_gt gt
WHERE gt.source_type = G_SRC_TYP_UNACCT_PREPAY_HIST
AND gt.source_table_name = G_SRC_TAB_AP_PREPAY_HIST
AND gt.accounting_event_id IS NOT NULL)
AND apph.posted_flag <> 'Y'
returning apph.accounting_event_id bulk collect into ltab_id;
debug ('update_ap_acct_date: total records updated in ap_prepay_history_all: '||sql%rowcount);
update ap_period_close_excps_gt gt
set process_status_flag = 'Y'
where accounting_event_id = ltab_id(i)
AND gt.source_type = G_SRC_TYP_UNACCT_PREPAY_HIST
AND gt.source_table_name = G_SRC_TAB_AP_PREPAY_HIST;
debug ('update_ap_acct_date: total prepay history processed in ap_period_close_excps_gt: '||ltab_id.count );
ltab_id.delete;
l_success := update_po_close_date;
print ('Failure in update_po_close_date while updating PO shipments');
update_xla_events('AP_PERIOD_CLOSE_EXCP_PKG.DO_SWEEP',
l_success);
print ('Failure in update_xla_events while updating XLA unaccounted events');
l_success := update_ebtax_dists;
print ('Failure in update_ebtax_dists while updating tax distributions in eBtax');
l_success := update_ap_acct_date;
print ('Failure in update_ap_acct_date while updating payables invoices and payments');
PSA_AP_BC_PVT.delete_events(
p_init_msg_list => 'F',
p_ledger_id => g_ledger_id,
p_start_date => g_period_start_date,
p_end_date => g_period_end_date,
p_calling_sequence => 'ap_period_close_pkg.process_period',
x_return_status => p_process_flag,
x_msg_count =>l_msg_count,
x_msg_data => p_process_message
);