AP月结检查脚本

ccpwufan

我经常用，省时省力。
--Check receipt but Pay on Receipt not run
SELECT DISTINCT rcv.organization_id, poh.segment1, rsh.receipt_num
           FROM apps.rcv_transactions rcv,
                apps.po_headers_all poh,
                apps.rcv_shipment_headers rsh
          WHERE rcv.destination_type_code = 'RECEIVING'
            AND rcv.invoice_status_code <> 'INVOICED'
            AND rcv.po_header_id = poh.po_header_id
            AND rsh.shipment_header_id = rcv.shipment_header_id
            AND poh.pay_on_code = 'RECEIPT'
            --AND rcv.transaction_type <> 'RETURN TO VENDOR'
            AND rcv.transaction_type = 'RECEIVE' ---modified line
            AND.....
            
            
--AP Interface Error
select invoice_num, ORG_ID, status from apps.ap_invoices_interface where status='REJECTED' and ....

--Unposted Invoice Distributions
select distinct invoice_id, accounting_date,
period_name, posted_flag, org_id, set_of_books_id
from apps.ap_invoice_distributions_all
where posted_flag = 'N'
and ...
--Unposted Payment Distributions
select invoice_id, accounting_date,
period_name, posted_flag, org_id, set_of_books_id
from apps.ap_invoice_payments_all
where posted_flag = 'N'
and org_id= ....
--Untransfered Journal Entries
select accounting_event_id, accounting_date, org_id,
PERIOD_NAME, gl_transfer_flag, set_of_books_id
from apps.ap_ae_headers_all
where gl_transfer_flag <> 'Y'
and ....
--Unconfirmed Payment Batch
select checkrun_name, org_id
from apps.ap_inv_selection_criteria_all
where STATUS NOT IN ('CONFIRMED','CANCELED','QUICKCHECK'  )
and (org_id is null) and ....
--AP bill qty <> Receipt Qty
SELECT distinct poh.segment1, poh.org_id
FROM apps.po_headers_all poh, apps.po_lines_all pol, apps.po_line_locations_all poll, apps.rcv_shipment_headers rsh, apps.rcv_transactions trx
WHERE poh.po_header_id = pol.po_header_id AND pol.po_line_id = poll.po_line_id
AND trx.po_header_id = poh.po_header_id AND trx.transaction_type = 'RETURN TO VENDOR'
AND trx.shipment_header_id = rsh.shipment_header_id
AND poll.quantity_received < poll.quantity_billed AND ...
--Ungenerated Intercompany AP
select rah.interface_header_attribute4, rah.trx_number from apps.ra_customer_trx_all rah
where rah.interface_header_context='INTERCOMPANY' and rah.trx_number not in (
select invoice_num from apps.AP_EXPENSE_REPORT_HEADERS_ALL) and rah.customer_trx_id in
(select distinct customer_trx_id from apps.ra_customer_trx_lines_all where EXTENDED_AMOUNT<>0)
and .....

szjwm

看下，到底是做什么用的~~

szjwm

确实好东西，谢谢楼主

xtaihuijian

不明白是什么意思啊？

toad

这是11版的

花刺

说不定用的着，顶下，别沉了

keslion

谢谢，好玩意

i_star

收藏下

dsp_chenfx

:/guzhang

Polaris-star

mark下，以后再看。

tsai1021

^^   

jeffreyszz

楼主第一QUERY ： Check receipt but Pay on Receipt not run
是不是检查那些，REICEPT HAS BEEN RECEIVED , BUT NOT BE INVOCIED IN AP
JUST WANT TO CONFIRM
我也做月结，但AP 这块我们是不做的，所以想请教下楼主

lillian25

的确不错    好东西

chentong1983

學習一下！！！