1. 未确认的付款批
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;
2. 到期未议付应付票据
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;
3. Check XLA data
xla_events_pub_pkg.period_close(P_API_VERSION => 1
, X_RETURN_STATUS => p_validation_flag
, P_APPLICATION_ID => G_AP_APPLICATION_ID
, P_LEDGER_ID => g_ledger_id
, P_PERIOD_NAME => g_period_name);
实际检查的SQL
SELECT rownum seq_id,
xle.event_id,
xle.entity_id,
xte.entity_code,
xte.source_id_int_1,
xte.transaction_number,
xle.event_date
FROM xla_events xle,
xla.xla_transaction_entities xte,
gl_ledger_relationships glr1,
gl_ledger_relationships glr2,
xla_ledger_options xlo
WHERE xle.entity_id = xte.entity_id
AND xle.application_id = xte.application_id
AND xle.event_date BETWEEN g_period_start AND g_period_end
AND xle.event_status_code IN ('I', 'U')
AND xle.process_status_code IN ('I', 'U', 'R', 'D', 'E')
AND glr2.target_ledger_id = g_set_of_books_id
AND glr2.source_ledger_id = glr1.source_ledger_id
AND glr2.application_id = glr1.application_id
AND glr1.target_ledger_id = xlo.ledger_id
AND xle.application_id = xlo.application_id
AND xlo.capture_event_flag = 'Y'
AND (glr1.target_ledger_id = xte.ledger_id OR
glr1.primary_ledger_id = xte.ledger_id)
AND (glr1.relationship_type_code = 'SUBLEDGER' OR
(glr1.target_ledger_category_code = 'PRIMARY' AND
glr1.relationship_type_code = 'NONE'))
AND glr2.application_id = 101
AND xte.application_id = 200
AP月结关会计期间检查逻辑
最新推荐文章于 2022-05-17 11:10:46 发布
4. 未过账发票分配行
SELECT rownum seq_id,
ai.invoice_id,
ai.invoice_num,
ai.invoice_date,
ai.invoice_amount,
ai.cancelled_date,
aid.invoice_distribution_id,
aid.accounting_event_id,
aid.accounting_date,
aid.amount,
aid.invoice_line_number,
aid.distribution_line_number,
hou.NAME org_name
FROM ap_invoices_all ai,
ap_invoice_distributions_all aid,
hr_operating_units hou
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 AND
g_period_end
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_set_of_books_id
AND ai.org_id = hou.organization_id
5.发票无分配行
SELECT rownum seq_id,
ai.invoice_id,
ail.line_number,
ail.accounting_date,
ai.invoice_num,
ai.invoice_date,
ai.invoice_amount,
hou.NAME org_name
FROM ap_invoices_all ai,
ap_invoice_lines_all ail,
hr_operating_units hou
WHERE ai.invoice_id = ail.invoice_id
AND ail.accounting_date BETWEEN g_period_start AND
g_period_end
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
--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_set_of_books_id
AND ai.org_id = hou.organization_id
6. 未过账预付款历史记录
SELECT rownum seq_id,
ai.invoice_id,
apph.accounting_event_id,
apph.accounting_date,
ai.invoice_num,
ai.invoice_date,
ai.invoice_amount,
ai.cancelled_date,
hou.NAME org_name
FROM ap_invoices_all ai,
ap_prepay_history_all apph,
hr_operating_units hou
WHERE ai.invoice_id = apph.invoice_id
AND apph.accounting_date BETWEEN g_period_start AND
g_period_end
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_set_of_books_id
AND ai.org_id = hou.organization_id
7.未过账付款历史记录
SELECT rownum seq_id,
aca.check_id,
hou.NAME org_name,
aca.doc_sequence_value,
p.payment_history_id,
p.trx_pmt_amount
FROM ap_payment_history_all p,
ap_system_parameters_all asp,
ap_checks_all aca,
hr_operating_units hou
WHERE aca.check_id = p.check_id
AND aca.org_id = hou.organization_id
AND p.accounting_date BETWEEN g_period_start AND
g_period_end
AND p.posted_flag IN ('N', 'S')
AND ((nvl(asp.when_to_account_pmt, 'ALWAYS') =
'CLEARING ONLY' AND
p.transaction_type IN
('PAYMENT CLEARING', 'PAYMENT UNCLEARING')) OR
nvl(asp.when_to_account_pmt, 'ALWAYS') = 'ALWAYS')
AND p.org_id = asp.org_id
AND asp.set_of_books_id = g_set_of_books_id
8. 未过账付款行
SELECT rownum seq_id,
aip.invoice_payment_id,
aip.accounting_event_id,
aip.accounting_date,
aip.check_id,
aip.amount aip_amount,
ac.check_number,
ac.check_date,
ac.amount ac_amount,
hou.NAME org_name
FROM ap_invoice_payments_all aip,
ap_checks_all ac,
ap_system_parameters_all asp,
hr_operating_units hou
WHERE aip.posted_flag IN ('N', 'S')
AND aip.accounting_date BETWEEN g_period_start AND
g_period_end
AND aip.org_id = asp.org_id
AND asp.set_of_books_id = g_set_of_books_id
AND ac.check_id = aip.check_id
AND nvl(asp.when_to_account_pmt, 'ALWAYS') = 'ALWAYS'
AND aip.org_id = hou.organization_id