Is there way to identify which Sales Order a failed work order was created from?
I can identify errored records with below query, but unable to identify the sales order's associated to these. Business would like to see for the error'd records which Sales Order it is tied to.
SELECT ood.organization_code,
wjs.INTERFACE_ID,
wjs.CREATION_DATE,
wjs.LOAD_TYPE,
wjs.STATUS_TYPE,
wjs.JOB_NAME,
msib.SEGMENT1 ASSEMBLY_ITEM,
wjs.NET_QUANTITY,
wjs.LINE_CODE,
wjs.REQUEST_ID,
wjs.GROUP_ID,
wie.ERROR,
wjs.CREATED_BY_NAME,
wjs.SOURCE_CODE
FROM apps.WIP_JOB_SCHEDULE_INTERFACE wjs,
apps.org_organization_definitions ood,
APPS.MTL_SYSTEM_ITEMS_B msib,
APPS.WIP_INTERFACE_ERRORS wie
WHERE wjs.PROCESS_STATUS = 3
AND wjs.ORGANIZATION_ID = ood.ORGANIZATION_ID
AND wjs.ORGANIZATION_ID = msib.ORGANIZATION_ID
AND ood.ORGANIZATION_ID = msib.ORGANIZATION_ID
AND wjs.PRIMARY_ITEM_ID = msib.INVENTORY_ITEM_ID
AND wjs.LOAD_TYPE IN (1, 3)
AND wie.INTERFACE_ID = wjs.INTERFACE_ID
ORDER BY wjs.CREATION_DATE DESC;