WIP Job and Schedule Interface

select
  wjsi.source_code
, nvl(ood.organization_code, wjsi.organization_code) organization_code
, nvl(we.wip_entity_name,wjsi.job_name) job_name
, nvl(&P_FLEXDATA,' ') description
, msi.inventory_item_id
,nvl(wjsi.status_type,wdj.status_type) status_type
, nvl(wjsi.start_quantity,wdj.start_quantity) start_quantity
, to_char(nvl(wjsi.first_unit_start_date,wdj.scheduled_start_date))  
||' '||
  to_char(nvl(wjsi.first_unit_start_date,wdj.scheduled_start_date),
 'HH24:MI') start_date
, to_char(nvl(wjsi.last_unit_completion_date,
                       wdj.scheduled_completion_date))||' '||
  to_char(nvl(wjsi.last_unit_completion_date,
                       wdj.scheduled_completion_date),'HH24:MI') completion_date
,wjsi.process_phase pphase
, ml1.meaning process_phase
, ml2.meaning process_status
, wjsi.interface_id interface_id2, 
	WIP_WIPMLINT_XMLP_PKG.c_status_typeformula(nvl ( wjsi.status_type , wdj.status_type )) C_STATUS_TYPE, 
	WIP_WIPMLINT_XMLP_PKG.c_flexfield2formula() C_FLEXFIELD2, 
	WIP_WIPMLINT_XMLP_PKG.c_fail_resformula() C_Fail_Res, 
	WIP_WIPMLINT_XMLP_PKG.c_success_rescheduleformula(wjsi.process_phase) C_SUCCESS_RESCHEDULE
from 
  mtl_system_items msi 
, org_organization_definitions ood
, mfg_lookups ml1
, mfg_lookups ml2
, wip_entities we 
, wip_job_schedule_interface wjsi
, wip_discrete_jobs wdj
where wjsi.load_type = 3
and msi.inventory_item_id(+)=we.primary_item_id
and msi.organization_id(+)=we.organization_id
and ood.organization_id(+)=wjsi.organization_id
and ml1.lookup_code(+) = wjsi.process_phase
and ml1.lookup_type(+) = 'WIP_ML_PROCESS_PHASE'
and ml2.lookup_code(+) = wjsi.process_status
and ml2.lookup_type(+) = 'WIP_PROCESS_STATUS'
and wdj.wip_entity_id(+) = wjsi.wip_entity_id
and wdj.organization_id(+) = wjsi.organization_id
and we.wip_entity_id(+) = wjsi.wip_entity_id
&C_GROUP_ID