AP关会计期检查的内容

1、 check if any unconfirmed payment batchesselect 1y'from ap_inv_selection_criteria_all aisc, iby_pay_service_requests ipsr ap_selected_invoices_all asiwhereipsr.call_app_pay_service_req_code(+)=aisccheckrun_nameandtrunc (aisccheck_date) between g_period_start_date andg_period_end_dateand decode(i

2、psr.payment_service_request_id, null,aiscstatus zap_payment_util_pkgget_psr_status(ipsrpayment_service_request_idipsr.payment_service_request_status)not in (1 confirmed 1z * canceled 1z 'quickcheck', 'cancelledno payments', 'terminated*)and aisccheckrun_id = asicheckrun_idand asi

3、.org_id in (select org_id org_id from ap_org_attributes_gt org_gtt)and rownum = 1; check if any unmatured future payments existsselect 1y1from ap_checks_a11 cwhere c . future_pay_due_date is not nulland cstatus_lookup_code = 1 issued 1and cfuture_pay_due_date between g_period_start_dateandg_period_e

4、nd__id in (select org_id org_id fromap_org_attributes_gt org_gtt)andrownum = 1;xla_events_pub_pkg.period_close(p_api_version => 1,x_return_status => p_validation_flag,p_application_id=>g_ap_application_idp_ledger_id=> g_ledger_idf p_period_name=> g_period_name);xla_period_

5、close_exp_pkg.check_period_close 一一 xlaeyym2eaueyouap_period_close_pkgget_unposted_transactionsodm 2 eappao»iaeyw j £一一 -oaaddixlyoepaeyyselect aiinvoice_idzaiinvoice_numzaidoc_sequence_value,aiinvoice_date,aiinvoice_amount,aicancelled_date,aid.invoice distribution id,aid.accounting_event_

6、id,aid.accounting_datezaid.amount,aid.invoice_line_number,aid. dist ribut 匚0口_丄:1110_21111301：from ap_invoices_all air ap_invoice_distributions_m丄丄 aidwhere 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_p

7、eriod_starand g_period_end_dateand aid.posted_f lag in ( 'n * z 1 s * z * p * ) 一一 n=not accounted, s=selected for accounting, p=partially accounted for cash based accountingand aid.set_of_books_id = 2022 ;select aiinvoice_idzastd.invoice_distribution_id,astdaccounting_event_idzastd.account ing_

8、_idzaiinvoice_num,aiinvoice_currency_codezai.vendor_id,aidoc_sequence_value,ai.voucher_numaaiinvoice_date,aiinvoice_amountfrom ap_invoices_allaiap_self_assessed_tax_dist_all astd, ap_org_attributes_gtorg_gttwhere ai.invoice_id = astd.invoice_id一一 bug 7311486 utr report must run even if

9、dates and period is not specifiedand (g_action <> g_action_utr andastdaccounting_date between g_period_start_date and g_pe riod_end_date) or(g_action = g_action_utr andg_period_start_date is not null and g_period_end_date is not null and astdaccounting_date between g_period_start_date and g_pe

10、 riod_end_date) or(g_action = g_action_utr and g_period_start_date is null andg_period_end_date is null and 1=1)一一andastd.accounting_datebetweeng_period_start_date and g_period_end_dateand astd. pos ted_f_ag in ( * n ' z 's',1 p 1 ) - n=not accounted,s=selected for accounting, p=partiall

11、y accounted for cash b ased accountingand astd.set_of_books_id = g_ledger_idand mstdorg_id = org_gttorg_idand (g_action <> g_action_period_close or(g_action = g_action_period_c_ose and rownum = 1) for period close we just need check if any such record exists)；select aiinvoice_idzailline_number

12、zailaccounting_date,ailorg_id,aiinvoice_num,aiinvoice_currency_codeaaivendor_idzaidoc_sequence_value,aivoucher_numzaiinvoice_datezaiinvoice_amount,aicancelled_date,g_src_typ_lines_without_distszg src tab ap inv lines allfrom ap_invoices_allai,ap_invoice_lines_all ail, ap_org_attributes_gt org_gttwhe

13、re ai.invoice_id = ail.invoice_id bug 7311486 utr report must run even if dates and period is not specifiedand ( (g_action <> g_action_utr andailaccounting_date between g_period_start_date and g_period_end_dmte) or(g_action = g_action_utr and g_period_start_date is not null and g_period_end_da

14、te is not null and ailaccounting_date between g_period_start_date and g_period_end_date) or(g_action = g_action_utr and g_period_start_date is null andg_period_end_date is null and 1 = 1)-andailaccounting_datebetweeng_period_start_date and g_period_end_dateand not exists(select 1 -> lines without

15、 distributionsfrom ap_invoice_distributions_all aidwhere aid.invoice_id = ai.invoice_idand aid.invoice_line_number = ail.line_number一-bug 7242216 excluding invoices having discarded lines withno distributionsunionselect 1 from dual where ail.discarded_flag = *y1)and ail.amount <> 0and ai.cance

16、lled_date is nulland ail. set_of_books_id = g_ledger_idand ailorg_id = org_gttorg_idand (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)；select ai.invoice_id,apphaccounting_event_id, apphaccount

17、 ing_datez aiorg_id,aiinvoice_numaaiinvoice_currency_codezai.vendor_id,aidoc_sequence_value,ai.voucher_numaaiinvoice_datezaiinvoice_amount,aicancelled_date,ailegal_entity_id,g_src_typ_unacct_prepay_hi s t,g_src_tab_ap_prepay_hi stfrom ap_invoices_allai,ap_prepay_history_all apphz ap_org_attributes_g

18、t org_gttwhere ai.invoice_id = apph.invoice_id一一 bug 7311486 utr report must run even if dates and period is not specifiedand (g_action <> g_action_utr andapphaccounting_date between g_period_start_date and g_period_end_dmte) or(g_action = g_action_utr andg_period_start_date is not null andg_p

19、eriod_end_date is not null andapphaccount ing_date between g_period_start_date and g_period_end_dmte) or(g_action = g_action_utr and g_period_start_date is null andg_period_end_date is null and 1=1)-andaid.accounting_datebetweeng_period_start_date and g_period_end_dateand apph posted_flmg in (* n

20、9;z * s'z 1 p 1 ) - n=not accounted, s=selected for accountingz p=partially accounted for cash based accountingand apph.accounting_event_id is not nulland ai.set_of_books_id = g_ledger_idand aiorg_id = org_gttorg_idand (g_action <> g_action_period_close or(g_action = g_action_period_close

21、and rownum = 1) for period close we just need check if any such record exists)；select aphpayment_history_id, aphaccounting_event_id, aphaccounting_date, aphcheck_id,aph.transaction_type,aphorg_idzorgsrecon_accounting_flag, accheck_number,acexchange_rate,accheck_date,aclegal_entity_id,acvendor_nameac

22、bank_account_name,acamount,accurrency_codezacvendor_idag_s r c_t yp_u n a c c t_pmt_history,g_src_tab_ap_pmt_historyfrom ap_payment_history_all aph,ap_checks_allac,ap_org_att ributes_gt orgswhere aph.posted_flag in (,1s *)and accheck_id = aph.check_id一一 bug 7311486 utr report must run even if dates

23、and period is not specifiedand (g_action <> g_action_utr andaphaccounting_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 andg_period_end_date is not null andaphaccounting_date between g_period_start_date and g_period_end_d

24、ate) or(g_action = g_action_utr and g_period_start_date is null andg_period_end_date is null and 1=1)and aphaccounting_date between g_period_start_date and g_period_end_dateand _id = _idand (nvl(orgs.when_to_account_pmt z * always *) = 1 always * or (nvl(orgs.when_to_account_pmt, 1 al

25、ways 1) = * clearing only 1 andaph.transaction_type inpayment 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);select aip invoice_payuient_id,

26、aipaccounting_event_id,aipaccounting_dateaipcheck_id,aipamount,aiporg_idzorgs recon_account ing_f lag,accheck_number,ac exchange_3te,accheck_datezaclegal_ent ity_id,acvendor_nameac.bank_account_name,acamount,accurrency_codezac status_lookup_cocie<acvendor_idzg_src_typ_unacct_in v_pmts,g_src_tab_a

27、p_inv_paymentsfrom ap_invoice_payments_a11 aip,ap_checks_allacaap_org_attributes_gt orgswhere aip.posted_flag in (2,'s* )一一 bug 7311486 utr report must run even if dates and period is not specifiedand (g_action <> g_action_utr andaipaccounting_date between g_period_start_date and g_period_

28、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 aipaccounting_date between g_period_start_date and g_period_end_dmte) or(g_action = g_action_utr and g_period_start_date is null andg_period_end_date is null and 1 = 1)-andaipaccounting_dat

29、ebetweeng_period_start_date and g_period_end_dateand _id = orgsorg_idand accheck_id = aipcheck_idand nvl(orgswhen_to_account_pmtf 'always 1) = 1 always 1;select message_textinto ap_period_close_pkgg_orphan_message_textfrom fnd_new_messageswhere language_code = userenv(1 lang 1)and (messag

30、e_name = 'ap_orp han_event s_exis t' and g_action <> g_action_sweep) or(message_name = 'ap_orphan_events_cleaned， and g_action = g_action_sweep)and exists(select 1from xla_eventsxe,xla_transaction_entities_upg xt叭 ap_o rg_att r ibut e s_gtaagtwhere xe.application_id = 200and xte.ap

31、plication_id = 200and xe-event_status_code in (1u t,111)and xeprocess_status_code in ( 1u1z 111)and xe.entity_id = xte.entity_idand xtesecurity_id_int_l = aagtorg_idand (g_action <> g_action_utr andxeevent_date between g_period_start_date and g_period_end_date) or(g_action = g_action_utr andg_per