Intransit Time Conversion

Using the below query we can find the intransit time and other details.

SELECT mism.ship_method f01
, mism.intransit_time f02
, mism.default_flag f03
, mism.from_organization_id f04
, ood.organization_name f05
, mism.to_organization_id f06
, ood1.organization_name f07
, mism.from_location_id f08
, mism.to_location_id f09
, mism.last_update_date f10
, mism.last_updated_by f11
, mism.creation_date f12
, mism.created_by f13
, mism.last_update_login f14
, mism.attribute_category f15
, mism.to_region_id f16
, mism.destination_type f17
, 'CREATE' f18
FROM mtl_interorg_ship_methods mism
, org_organization_definitions ood
, org_organization_definitions ood1
WHERE mism.from_organization_id = ood.organization_id
AND mism.to_organization_id = ood1.organization_id
AND NOT EXISTS(
SELECT 'X'
FROM xxcofi_time_extract_tbl xxtime
WHERE mism.from_organization_id = xxtime.from_organization_ID
AND mism.to_organization_id = xxtime.to_organization_id
AND mism.intransit_time = xxtime.intransit_time)
-- To exclude the records which alre already processed in staging table
UNION
SELECT DISTINCT NULL
, 1
, NULL
, TO_NUMBER(vendor_site_code) f05
, vendor_site_code f05
, organization_id F06
, substr(dest_org_code,1,4) f07
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, 'CREATE'
FROM xxcofi_sourcing_dc_to_vendor xxsource
WHERE NOT EXISTS(
SELECT 'X'
FROM xxcofi_time_extract_tbl xxtime
WHERE xxsource.vendor_site_code = xxtime.from_organization_name
AND xxsource.organization_id = xxtime.to_organization_id
AND 1 = xxtime.intransit_time);
-- To exclude the records which alre already processed in staging table

Installbase Creation

To create install base for an item you need to run the Install base creation program.
EX: "COFI IB Creation"

For cross checking we can use the below query to find whether install base is created for a particular sales order.

select * from csi.csi_item_instances
where last_oe_order_line_id IN (
select line_id from oe_order_lines_all
where header_id IN (
select header_id from oe_order_headers_all
where order_number = '1560102'));


With the following query, we can findout if there is any error with the transaction.
-------------------------------------------------------------------------------------

select * from csi.CSI_TXN_ERRORS where TRANSACTION_ID IN (SELECT transaction_id--, trx_source_line_id
FROM mtl_material_transactions
WHERE trx_source_line_id IN (select line_id from oe_order_lines_all
where header_id IN (select header_id from oe_order_headers_all
where order_number = '1560102'))
AND transaction_type_id = 33)

If there is no error for this transaction then it may be in the Interface table. TO put the record from the Interface table to the base table. We can run the

following PRogram

Concurrent PRogram Name: Resubmit Interface Process

responsibility--> cofi install base admin

If there is any error in the error table then we need to clear the log and we need to run the above program.