Oracle Apps Inventory period closure monitoring scripts
select transaction_header_id Txn_hdr_id,
mmtt.transaction_temp_id Txn_tmp_id,
mmtt.inventory_item_id Inv_Item_id,
revision Rev,
locator_id Loc_id,
mtlt.LOT_NUMBER Lot_Number,
msnt.fm_serial_number FmSerial_Num,
msnt.to_serial_number ToSerial_Num,
mmtt.transaction_quantity Txn_qty,
mmtt.primary_quantity Pri_qty,
transaction_uom Uom,
transaction_cost Txn_cst,
transaction_type_id Txn_typ_id,
transaction_action_id Txn_act_id,
transaction_source_type_id Txn_sty_id,
transaction_source_id Txn_src_id,
transaction_date Txn_date,
rcv_transaction_id Txn_rcv_id,
move_order_line_id Txn_mov_id,
completion_transaction_id Txn_com_id,
process_flag Pflg,
lock_flag Lflg,
transaction_mode TMode,
mmtt.request_id Txn_req_id,
-- Trnx Info
transfer_subinventory Xfr_subinv,
transfer_to_location Xfr_Loc,
pick_slip_number Pick_slip,
picking_line_id Pick_lineid,
reservation_id Reserv_id,
wms_task_type Wms_task,
standard_operation_id Std_Oprid,
mmtt.error_code Error_code,
error_explanation Error_explanation
FROM mtl_material_transactions_temp mmtt,
mtl_transaction_lots_temp mtlt,
mtl_serial_numbers_temp msnt
WHERE ORGANIZATION_ID = :v_org_id
AND ACCT_PERIOD_ID = :v_period_id
AND nvl(transaction_status,0) <> 2
AND (mtlt.transaction_temp_id (+) = mmtt.transaction_temp_id
AND msnt.transaction_temp_id (+) = mmtt.transaction_temp_id)
ORDER BY TRANSACTION_DATE, mmtt.CREATION_DATE, mmtt.transaction_temp_id;
To check if there is any uncosted material
select TRANSACTION_ID Txn_id,
inventory_item_id Inv_Item_id,
revision Rev,
subinventory_code Subinv_code,
locator_id Loc_id,
transaction_quantity Txn_qty,
primary_quantity Pri_qty,
transaction_uom Uom,
transaction_type_id Txn_typ_id,
transaction_action_id Txn_act_id,
transaction_source_type_id Txn_sty_id,
transaction_source_id Txn_src_id,
transaction_quantity Txn_qty,
transaction_uom Uom,
transaction_date Txn_date,
transfer_transaction_id Txn_xfr_id,
transfer_subinventory Xfr_subinv,
TRANSACTION_GROUP_ID Txn_grp_id,
SOURCE_CODE Src_code,
SOURCE_LINE_ID Src_line_id,
request_id Txn_req_id,
error_code Error_code,
error_explanation Error_explanation
from MTL_MATERIAL_TRANSACTIONS mmt
where organization_id = :v_org_id
and acct_period_id = :v_period_id
and costed_flag is not null
ORDER BY TRANSACTION_DATE, CREATION_DATE, transaction_id;
To check if there is any PENDING WIP COSTING
SELECT wcti.TRANSACTION_ID Txn_id,
PRIMARY_ITEM_ID Assembly_id,
WIP_ENTITY_ID Wip_entity_id,
substr(WIP_ENTITY_NAME,1,40) Wip_entity_name,
ENTITY_TYPE EType,
REPETITIVE_SCHEDULE_ID Rep_Sch_id,
TRANSACTION_DATE Txn_date,
TRANSACTION_QUANTITY Txn_qty,
TRANSACTION_UOM Uom,
TRANSACTION_TYPE TType,
AUTOCHARGE_TYPE AChrg,
BASIS_TYPE BType,
RESOURCE_TYPE RType,
STANDARD_RATE_FLAG SFlg,
wcti.REQUEST_ID Txn_req_id,
GROUP_ID Grp_id,
OPERATION_SEQ_NUM Op_SeqNum,
RESOURCE_SEQ_NUM Re_SeqNum,
RESOURCE_ID Resrc_Id,
COMPLETION_TRANSACTION_ID Txn_com_id,
MOVE_TRANSACTION_ID Txn_mov_id,
PROCESS_PHASE PPhase,
PROCESS_STATUS PStatus,
SOURCE_CODE Src_code,
SOURCE_LINE_ID Src_line_id,
ERROR_COLUMN Error_column,
ERROR_MESSAGE Error_Message
from wip_cost_txn_interface wcti,
wip_txn_interface_errors wtie
WHERE ORGANIZATION_ID = :v_org_id
AND ACCT_PERIOD_ID = :v_period_id
AND wtie.transaction_id (+) = wcti.transaction_id
ORDER BY TRANSACTION_DATE, wcti.CREATION_DATE, wcti.transaction_id;
To check if there is any uncosted WMS Transactions
select transaction_id Txn_id,
transaction_type_id Txn_typ_id,
TRANSACTION_DATE Txn_date,
status Staus,
GROUP_ID Grp_id,
request_id Txn_req_id,
error_message Error_Message
FROM apps.WSM_SPLIT_MERGE_TRANSACTIONS
WHERE ORGANIZATION_ID in (123,234,345,456,567,678)
AND COSTED <> 4
AND TRUNC(TRANSACTION_DATE) < (trunc(to_date('01-NOV-10'))+1)
order by TRANSACTION_DATE;
To check if there is any Pending WMS Interface
select header_id Txn_hdr_id,
transaction_type_id Txn_typ_id,
transaction_date Txn_date,
process_status PStatus,
transaction_id Txn_id,
group_id Grp_id,
request_id Txn_req_id,
error_message Error_Message
from apps.wsm_split_merge_txn_interface
WHERE ORGANIZATION_ID in (123,234,345,456,567,678)
AND PROCESS_STATUS <> 4
AND TRUNC(TRANSACTION_DATE) < (trunc(to_date('01-NOV-10'))+1)
ORDER BY TRANSACTION_DATE;
To check for Unprocessed Shipping Transactions
select source_header_number Src_Hdr_num,
source_line_number Src_line_num,
wdd.delivery_detail_id DelvryDetailId
from apps.wsh_delivery_details wdd,
apps.wsh_delivery_assignments wda,
apps.wsh_new_deliveries wnd,
apps.wsh_delivery_legs wdl,
apps.wsh_trip_stops wts
where wdd.source_code = 'OE'
and wdd.released_status = 'C'
and wdd.inv_interfaced_flag in ('N' ,'P')
and wdd.organization_id in (123,234,345,456,567,678)
and wda.delivery_detail_id = wdd.delivery_detail_id
and wnd.delivery_id = wda.delivery_id
and wnd.status_code in ('CL','IT')
and wdl.delivery_id = wnd.delivery_id
and trunc(wts.actual_departure_date) between to_date('01-NOV-10')
and to_date('29-JUN-03')
and wdl.pick_up_stop_id = wts.stop_id;
To check for Pending Receiving
SELECT INTERFACE_TRANSACTION_ID Txn_Iface_id,
HEADER_INTERFACE_ID Hdr_Iface_id,
Item_id Inv_Item_id,
GROUP_ID Grp_id,
TRANSACTION_TYPE TranType,
TRANSACTION_DATE Txn_date,
PROCESSING_STATUS_CODE PSCOde,
PROCESSING_MODE_CODE PMCode,
TRANSACTION_STATUS_CODE TSCODE,
QUANTITY Txn_qty,
UNIT_OF_MEASURE Uom,
AUTO_TRANSACT_CODE ATCode,
RECEIPT_SOURCE_CODE RSCode,
DESTINATION_TYPE_CODE DTCode,
SOURCE_DOCUMENT_CODE SDCode,
CURRENCY_CODE CCode,
DOCUMENT_NUM Doc_num,
SHIP_TO_LOCATION_ID STLId,
PARENT_TRANSACTION_ID Prt_Txn_id,
PO_HEADER_ID Po_Hdr_id,
PO_LINE_ID PO_Line_id,
VENDOR_ID VendorId,
VENDOR_SITE_ID VendorSiteId,
OE_ORDER_HEADER_ID OeHdr_id,
OE_ORDER_LINE_ID OeLine_id,
VALIDATION_FLAG VFlag,
SUBINVENTORY SubInventory
FROM apps.RCV_TRANSACTIONS_INTERFACE
WHERE TO_ORGANIZATION_ID in (123,234,345,456,567,678)
AND TRUNC(TRANSACTION_DATE) < (trunc(to_date ('01-NOV-10'))+1)
AND DESTINATION_TYPE_CODE = 'INVENTORY'
ORDER BY TRANSACTION_DATE;
To check for Pending Material
select mti.transaction_interface_id Txn_IFace_id,
transaction_header_id Txn_hdr_id,
inventory_item_id Inv_Item_id,
revision Rev,
subinventory_code Subinv_code,
locator_id Loc_id,
mtli.LOT_NUMBER Lot_Number,
msni.fm_serial_number FmSerial_Num,
msni.to_serial_number ToSerial_Num,
mti.transaction_quantity Txn_qty,
mti.primary_quantity Pri_qty,
transaction_uom Uom,
transaction_cost Txn_cst,
transaction_type_id Txn_typ_id,
transaction_action_id Txn_act_id,
transaction_source_type_id Txn_sty_id,
transaction_source_id Txn_src_id,
transaction_date Txn_date,
transfer_subinventory Xfr_subinv,
transfer_organization Xfr_OrgId,
mti.request_id Txn_req_id,
mti.source_code Src_code,
mti.source_line_id Src_line_id,
source_header_id Src_Hdr_id,
mti.process_flag PFlag,
decode(to_char(nvl(mti.process_flag,0)),
'1','Ready',
'2','Not Ready',
'3','Error',
to_char(mti.process_flag)) Pflag_Desc,
transaction_mode TMode,
decode(transaction_mode,
'2','Immediate',
'3','Background',
to_char(transaction_mode)) TMode_desc,
lock_flag LFlag,
decode(lock_flag,
'1','Locked',
'2','Not Locked') LFlag_desc,
mti.error_code Error_code,
error_explanation Error_explanation
from apps.mtl_transactions_interface mti,
apps.mtl_serial_numbers_interface msni,
apps.mtl_transaction_lots_interface mtli
where ORGANIZATION_ID = :v_org_id
AND (ACCT_PERIOD_ID = :v_period_id
OR (ACCT_PERIOD_ID IS NULL
AND TRUNC(TRANSACTION_DATE) < (trunc(to_date(:v_closing_to_date))+1)))
AND mti.PROCESS_FLAG <> 9
AND (mtli.transaction_interface_id (+) = mti.transaction_interface_id
AND msni.transaction_interface_id (+) = mti.transaction_interface_id)
ORDER BY TRANSACTION_DATE;
To check for PENDING SHOP FLOOR MOVE
SELECT wmti.TRANSACTION_ID Txn_id,
PRIMARY_ITEM_ID Assembly_id,
WIP_ENTITY_ID Wip_entity_id,
WIP_ENTITY_NAME Wip_entity_name,
ENTITY_TYPE EType,
REPETITIVE_SCHEDULE_ID Rep_Sch_id,
TRANSACTION_DATE Txn_date,
TRANSACTION_QUANTITY Txn_qty,
TRANSACTION_UOM Uom,
PRIMARY_QUANTITY PQty,
PRIMARY_UOM PUom,
TRANSACTION_TYPE TType,
FM_OPERATION_SEQ_NUM Fmopseq,
FM_INTRAOPERATION_STEP_TYPE Fmopstep,
TO_OPERATION_SEQ_NUM Toopseq,
TO_INTRAOPERATION_STEP_TYPE Toopstep,
OVERCOMPLETION_TRANSACTION_QTY Txn_ocom_qty,
OVERCOMPLETION_TRANSACTION_ID Txn_ocom_id,
SCRAP_ACCOUNT_ID ScrpAccId,
GROUP_ID Grp_id,
wmti.REQUEST_ID Txn_req_id,
PROCESS_PHASE PPhase,
PROCESS_STATUS PStatus,
SOURCE_CODE Src_code,
SOURCE_LINE_ID Src_line_id,
ERROR_COLUMN Error_column,
ERROR_MESSAGE Error_Message
from wip_move_txn_interface wmti,
wip_txn_interface_errors wtie
where ORGANIZATION_ID = :v_org_id
AND (ACCT_PERIOD_ID = :v_period_id
OR (ACCT_PERIOD_ID IS NULL
AND TRUNC(TRANSACTION_DATE) < (TRUNC(to_date(:v_closing_to_date))+ 1)))
AND wtie.transaction_id (+) = wmti.transaction_id
ORDER BY TRANSACTION_DATE, wmti.CREATION_DATE, wmti.transaction_id;======================================================================
Oracle Apps Financials period closure monitoring scripts
Accounts Payable:
To find whether any orphan entry exist (this prevents period close)
Select *
from apps.ap_ae_lines_all
Where ae_header_id in(
Select ae_header_id
from apps.ap_ae_headers_all
Where org_id in (select organization_id
from apps.hr_operating_units
Where name like 'XYZ%')
and gl_transfer_flag='N'
And GL_TRANSFER_RUN_ID=-1
And set_of_books_id in (select set_of_books_id
from apps.hr_operating_units
Where name like 'XYZ%')
);
Select *
from apps.ap_ae_headers_all
Where org_id in (select organization_id
from apps.hr_operating_units
Where name like 'GEMSAS%')
and gl_transfer_flag='N'
And GL_TRANSFER_RUN_ID=-1
And set_of_books_id in (select organization_id
from apps.hr_operating_units
Where name like 'XYZ%');
If
the above 2 queries returns row, run the AP transfer to GL. Re-run the
query. If still it returns rows, then check whether the
accounting_event_id exists in ap_accounting_events_all. If not, then
these are orphan entries and needs to be deleted.
To
check whether there are any transactions accounted with error or
accounted but not getting transferred to GL (this prevents period close)
select 'AP Accounting Errors(This Needs Your Attention)',
ouname ,
count (*),
null,
ou ,
aph.ae_header_id
from
(select hrou.NAME ouname,
apl.AE_LINE_ID,
aph.ae_header_id,
aph.ORG_ID ou,
apl.GL_TRANSFER_ERROR_CODE,
apl.ACCOUNTING_ERROR_CODE,
aph.GL_TRANSFER_RUN_ID
from apps.ap_ae_headers_all aph,
apps.AP_AE_LINES_ALL apl,
apps.hr_operating_units hrou,
gl.gl_sets_of_books sob
where sob.SET_OF_BOOKS_ID = aph.SET_OF_BOOKS_ID
and sob.SET_OF_BOOKS_ID=hrou.SET_OF_BOOKS_ID
and aph.AE_HEADER_ID = apl.AE_HEADER_ID
and aph.ORG_ID=hrou.ORGANIZATION_ID
and aph.PERIOD_NAME =to_char(sysdate,'MM-MON-YY')
and (apl.GL_TRANSFER_ERROR_CODE is not null or apl.ACCOUNTING_ERROR_CODE is not null) )
group by ouname,ou, ae_header_id;
To find any payment batch that is either not confirmed or cancelled (this prevents period close)
select a.CHECKRUN_NAME ,
a.status ,
a.org_id,b.NAME
from
apps.ap_inv_selection_criteria_all a,
apps.hr_all_organization_units b
where status not in (‘CANCELED’,’CONFIRMED’,’QUICKCHECK’)
and a.org_id=b.organization_id
To find whether all future dated payment batches are swept to the next period.
These does
not prevent period close but causes no accounting for these checks for
Maturity event. Need data fixes in these cases to update the accounting
date to current period and then account those.
Select * from apps.ap_payment_history_all
Where org_id in (select organization_id from apps.hr_operating_units
Where name like ‘GEMSAS%’)
And posted_flag = 'N'
And accounting_event_id is null;
Account Receivable:
To find any unposted items. All these prevents period close
select FCR.REQUEST_ID
,substr(resp.RESPONSIBILITY_NAME ,1,50) RESPONSIBILITY_NAME
,TO_CHAR(fcr.REQUESTED_START_DATE, 'DD-MON-YYYY HH24:MI:SS')
,fcr.REQUEST_DATE
, SUBSTR(USERS.DESCRIPTION,1,30) USER_NAME
,SUBSTR(FCP.USER_CONCURRENT_PROGRAM_NAME,1,55) program_name
FROM
APPS.FND_CONCURRENT_REQUESTS fcr,
APPS.FND_CONCURRENT_PROGRAMS_TL fcp,
fnd_user users,
fnd_responsibility_tl resp
where
fcr.PHASE_CODE = 'C' AND -- COMPLETED ;'P' AND --Pending
fcr.STATUS_CODE = 'C' AND -- NORMAL ;'I' AND --Incomplete
fcr.RESPONSIBILITY_ID = resp.RESPONSIBILITY_ID AND
FCR.REQUESTED_BY = USERs.USER_ID AND
FCP.USER_CONCURRENT_PROGRAM_NAME = 'Unposted Items Report from GL Transfer Program' --'General Ledger Transfer Program'
and fcp.CONCURRENT_PROGRAM_ID = fcr.CONCURRENT_PROGRAM_ID
AND fcr.REQUEST_DATE > (sysdate - 1)
order by fcr.REQUEST_DATE desc ;
To find whether any receipts are stuck in the ar_interim table. This prevents period close.
select * from apps.AR_INTERIM_CASH_RECEIPTS_ALL;
select * from apps.AR_INTERIM_CASH_RCPT_LINES_ALL;
Others:
- Check the ar_interface before closing the periods and ensure that you run auto invoice for all the orgs. This can be triggered through an SRS Engine request set.
- Before running the postings, ensure that the GL interface errors, if any, are cleared.
- Ensure that the Future Payment Maturity Date Report is run before the AP chain runs.
General Ledger
Monitor
regularly the gl_interface table for any gl unposted records. If
anything is stuck with Error message, follow-up with the users to clear
them up.
No comments:
Post a Comment