To check if there is any unprocessed material
MOVE _TRANSACTION_ID Txn_mov_id,
UNIT _OF_MEASURE Uom,AUTO _TRANSACT_CODE ATCode,
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.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_TRANSA CTIONS 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,
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,
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_ORGAN IZATION_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;
Check out the download on resolving issues related to Period Close Inventory Pending Transaction.
