[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, sla_ledger_cash_basis_flag
from gl_sets_of_books
where 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
-- bug 7311486 UTR report must run even if dates and period is not specified
and ( aid.accounting_date between g_period_start_date and g_period_end_date)
--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
);
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
-- bug 7311486 UTR report must run even if dates and period is not specified
-- and ( aid.accounting_date between g_period_start_date and g_period_end_date)
--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
);
debug ('Total records inserted in ap_period_close_excps_gt for source_type='||G_SRC_TYP_UNACCT_DISTS||' is:'||sql%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
-- bug 7311486 UTR report must run even if dates and period is not specified
and (
astd.accounting_date between g_period_start_date and g_period_end_date )
--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
);
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
-- bug 7311486 UTR report must run even if dates and period is not specified
and (
astd.accounting_date between g_period_start_date and g_period_end_date )
--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
);
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:'||sql%rowcount);
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(ail) */ 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
-- bug 7311486 UTR report must run even if dates and period is not specified
and (ail.accounting_date between g_period_start_date and g_period_end_date)
--and ail.accounting_date between g_period_start_date and g_period_end_date
and not exists (select 1 --> lines without distributions
from ap_invoice_distributions_all aid
where aid.invoice_id = ai.invoice_id
and aid.invoice_line_number = ail.line_number
--Bug 7242216 Excluding invoices having discarded lines with
--no distributions
union
select 1 from dual where ail.discarded_flag = 'Y'
)
and ail.amount <> 0
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
);
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(ail) */ 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
-- bug 7311486 UTR report must run even if dates and period is not specified
-- and (ail.accounting_date between g_period_start_date and g_period_end_date)
--and ail.accounting_date between g_period_start_date and g_period_end_date
and not exists (select 1 --> lines without distributions
from ap_invoice_distributions_all aid
where aid.invoice_id = ai.invoice_id
and aid.invoice_line_number = ail.line_number
--Bug 7242216 Excluding invoices having discarded lines with
--no distributions
union
select 1 from dual where ail.discarded_flag = 'Y'
)
and ail.amount <> 0
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
);
debug ('Total records inserted in ap_period_close_excps_gt for source_type='||G_SRC_TYP_LINES_WITHOUT_DISTS||' is:'||sql%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
-- bug 7311486 UTR report must run even if dates and period is not specified
and ( apph.accounting_date between g_period_start_date and g_period_end_date)
--and aid.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
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
);
insert into ap_period_close_excps_gt
( invoice_id
,accounting_event_id
,accounting_date
,org_id
,invoice_num
,invoice_currency_code
,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.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
-- bug 7311486 UTR report must run even if dates and period is not specified
-- and ( apph.accounting_date between g_period_start_date and g_period_end_date)
--and aid.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
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:'||sql%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,
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
-- bug 7311486 UTR report must run even if dates and period is not specified
and (
(g_action <> G_ACTION_UTR and aph.accounting_date between g_period_start_date and g_period_end_date)
or (g_action = G_ACTION_UTR and g_period_start_date is not null and g_period_end_date is not null and aph.accounting_date between g_period_start_date and g_period_end_date)
or (g_action = G_ACTION_UTR and g_period_start_date is null and g_period_end_date is null and 1=1)
)
--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:'||sql%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')
-- bug 7311486 UTR report must run even if dates and period is not specified
and (
(g_action <> G_ACTION_UTR and aip.accounting_date between g_period_start_date and g_period_end_date)
or (g_action = G_ACTION_UTR and g_period_start_date is not null and g_period_end_date is not null and aip.accounting_date between g_period_start_date and g_period_end_date)
or (g_action = G_ACTION_UTR and g_period_start_date is null and g_period_end_date is null and 1=1)
)
--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:'||sql%rowcount);
SELECT message_text
INTO AP_PERIOD_CLOSE_PKG.g_orphan_message_text
FROM fnd_new_messages
WHERE language_code = userenv('LANG')
AND ((message_name = 'AP_ORPHAN_EVENTS_EXIST' AND
g_action <> G_ACTION_SWEEP) OR
(message_name = 'AP_ORPHAN_EVENTS_CLEANED' AND
g_action = G_ACTION_SWEEP))
AND EXISTS
(SELECT 1
FROM xla_events xe,
xla_transaction_entities_upg xte,
ap_org_attributes_gt aagt
WHERE xe.application_id = 200
AND xte.application_id = 200
AND xe.event_status_code IN ('U', 'I')
AND xe.process_status_code IN ('U', 'I')
AND xe.entity_id = xte.entity_id
AND xte.security_id_int_1 = aagt.org_id
AND ((g_action <> G_ACTION_UTR AND
xe.event_date BETWEEN g_period_start_date
AND g_period_end_date) OR
(g_action = G_ACTION_UTR AND
g_period_start_date IS NOT NULL AND
g_period_end_date IS NOT NULL AND
xe.event_date BETWEEN g_period_start_date
AND g_period_end_date) OR
(g_action = G_ACTION_UTR AND
g_period_start_date IS NULL AND
g_period_end_date IS NULL))
AND NOT EXISTS
(SELECT 1
FROM ap_invoice_distributions_all aid
WHERE aid.accounting_event_id = xe.event_id)
AND NOT EXISTS
(SELECT 1
FROM ap_invoice_distributions_all aid
WHERE aid.bc_event_id = xe.event_id)
AND NOT EXISTS
(SELECT 1
FROM ap_self_assessed_tax_dist_all aid
WHERE aid.accounting_event_id = xe.event_id)
AND NOT EXISTS
(SELECT 1
FROM ap_self_assessed_tax_dist_all aid
WHERE aid.bc_event_id = xe.event_id)
AND NOT EXISTS
(SELECT 1
FROM ap_prepay_history_all apph
WHERE apph.accounting_event_id = xe.event_id)
AND NOT EXISTS
(SELECT 1
FROM ap_prepay_history_all apph
WHERE apph.bc_event_id = xe.event_id)
AND NOT EXISTS
(SELECT 1
FROM ap_invoice_payments_all aip
WHERE aip.accounting_event_id = xe.event_id)
AND NOT EXISTS
(SELECT 1
FROM ap_payment_history_all aph
WHERE aph.accounting_event_id = xe.event_id)
AND event_type_code <> 'MANUAL');
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;
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 ac.check_id, 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;
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 <> 'Y'
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 ap_period_close_excps_gt gt
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 gt.invoice_id, gt.invoice_line_number
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 ap_period_close_excps_gt gt
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 ap_period_close_excps_gt gt
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 bulk collect into ltab_id;
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.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;
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 bulk collect into ltab_id;
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.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;
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;
| FUNCTION - DELETE_ORPHAN_EVENTS
|
| DESCRIPTION
| This function is used to delete the Orphan events in the
| system, for the current ledger which lie within the start
| and end dates.
|
|
| PRAMETERS
|
|
| KNOWN ISSUES:
|
| NOTES:
|
| MODIFICATION HISTORY
| Date Author Description of Change
| 14-MAR-08 GAGRAWAL New
*===========================================================================*/
FUNCTION delete_orphan_events RETURN BOOLEAN IS
TYPE orphan_events_tab IS
TABLE OF xla_events.event_id%TYPE
INDEX BY BINARY_INTEGER;
SELECT xe.event_id,
xah.ae_header_id
FROM xla_events xe,
xla_transaction_entities_upg xte,
xla_ae_headers xah
WHERE xe.application_id = 200
AND xte.application_id = 200
AND xah.application_id(+) = 200
AND xe.entity_id = xte.entity_id
AND xe.event_status_code IN ('U','I')
AND xe.process_status_code IN ('U','I')
AND xte.ledger_id = g_ledger_id
AND xe.event_id = xah.event_id(+)
AND xe.event_date BETWEEN g_period_start_date
AND g_period_end_date
AND NOT EXISTS
(SELECT 1
FROM ap_invoice_distributions_all aid
WHERE aid.accounting_event_id = xe.event_id)
AND NOT EXISTS
(SELECT 1
FROM ap_invoice_distributions_all aid
WHERE aid.bc_event_id = xe.event_id)
AND NOT EXISTS
(SELECT 1
FROM ap_self_assessed_tax_dist_all aid
WHERE aid.accounting_event_id = xe.event_id)
AND NOT EXISTS
(SELECT 1
FROM ap_self_assessed_tax_dist_all aid
WHERE aid.bc_event_id = xe.event_id)
AND NOT EXISTS
(SELECT 1
FROM ap_prepay_history_all apph
WHERE apph.accounting_event_id = xe.event_id)
AND NOT EXISTS
(SELECT 1
FROM ap_prepay_history_all apph
WHERE apph.bc_event_id = xe.event_id)
AND NOT EXISTS
(SELECT 1
FROM ap_invoice_payments_all aip
WHERE aip.accounting_event_id = xe.event_id)
AND NOT EXISTS
(SELECT 1
FROM ap_payment_history_all aph
WHERE aph.accounting_event_id = xe.event_id)
AND xe.event_type_code <> 'MANUAL';
DELETE FROM xla_distribution_links
WHERE application_id = 200
AND ae_header_id = l_orphan_headers_data(i)
AND l_orphan_headers_data(i) IS NOT NULL;
DELETE FROM xla_ae_lines
WHERE application_id = 200
AND ae_header_id = l_orphan_headers_data(i)
AND l_orphan_headers_data(i) IS NOT NULL;
DELETE FROM xla_ae_headers
WHERE application_id = 200
AND ae_header_id = l_orphan_headers_data(i)
AND l_orphan_headers_data(i) IS NOT NULL;
DELETE FROM xla_events
WHERE application_id = 200
AND event_id = l_orphan_events_data(i);
debug('all deletes successful, returning true');
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');
l_success := delete_orphan_events;
print ('Failure in delete_orphan_events while deleting the orphan events');