WIP Discrete Job Dispatch

Description
Categories: BI Publisher, Manufacturing
Application: Work in Process
Source: Discrete Job Dispatch Report (XML)
Short Name: WIPDJDSP_XML
DB package: WIP_WIPDJDSP_XMLP_PKG
SELECT TO_CHAR(WO.FIRST_UNIT_START_DATE)  Sched_Op_Start_Date,
       TO_CHAR(WO.FIRST_UNIT_START_DATE, 'HH24:MI') Sched_Op_Start_Time,
       TO_CHAR(WO.LAST_UNIT_COMPLETION_DATE)  Sched_Op_Completion_Date,
       TO_CHAR(WO.LAST_UNIT_COMPLETION_DATE,'HH24:MI')  Sched_Op_Completion_Time,
       BD.DEPARTMENT_CODE Dept_Name,
       BD.DESCRIPTION Dept_Description,
       DJ.SCHEDULED_COMPLETION_DATE Sched_Job_Complete_Date,
       WE.WIP_ENTITY_NAME Job_Name,
       DECODE(WE.PRIMARY_ITEM_ID,NULL,NULL,&P_ITEM_DATA) C_ITEM_DATA,
       WO.OPERATION_SEQ_NUM Op_Seq,
       WO.DESCRIPTION Op_Description,
       WO.SCHEDULED_QUANTITY Quantity_Scheduled,
       WO.QUANTITY_COMPLETED Quantity_Completed,
       WO.QUANTITY_IN_QUEUE Quantity_In_Queue,
       WO.QUANTITY_RUNNING Quantity_Running,
       WO.WIP_ENTITY_ID we_id,
       DJ.STATUS_TYPE Status_Type,
       DJ.BUILD_SEQUENCE Build_Sequence, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_item_value', 'INV', 'MSTK', 101, SI.ORGANIZATION_ID, SI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') C_ITEM_VALUE, 
	WIP_WIPDJDSP_XMLP_PKG.C_quantity_in_prevFormula(WO.WIP_ENTITY_ID,WO.OPERATION_SEQ_NUM) C_quantity_in_prev,
	--&quantity_rem_in_prev C_quantity_in_prev,
	--decode(DJ.STATUS_TYPE,1,WO.SCHEDULED_QUANTITY,(WO.QUANTITY_IN_QUEUE+WO.QUANTITY_RUNNING+WIP_WIPDJDSP_XMLP_PKG.C_quantity_in_prevFormula(WO.WIP_ENTITY_ID,WO.OPERATION_SEQ_NUM))) Quantity_remaining,
	decode(DJ.STATUS_TYPE,1,WO.SCHEDULED_QUANTITY,(nvl(WO.QUANTITY_IN_QUEUE,0)+nvl(WO.QUANTITY_RUNNING,0)+nvl(WIP_WIPDJDSP_XMLP_PKG.C_quantity_in_prevFormula(WO.WIP_ENTITY_ID,WO.OPERATION_SEQ_NUM),0))) Quantity_remaining,
	--WIP_WIPDJDSP_XMLP_PKG.quantity_remainingformula(DJ.STATUS_TYPE, WO.SCHEDULED_QUANTITY, WO.QUANTITY_IN_QUEUE, WO.QUANTITY_RUNNING, WIP_WIPDJDSP_XMLP_PKG.C_quantity_in_prevFormula(WO.WIP_ENTITY_ID,WO.OPERATION_SEQ_NUM)) Quantity_remaining,
	WIP_WIPDJDSP_XMLP_PKG.quantity_rem_in_prev_p quantity_rem_in_prev
FROM   MTL_SYSTEM_ITEMS SI,
       WIP_SCHEDULE_GROUPS SG,
       WIP_ENTITIES WE,
       WIP_DISCRETE_JOBS DJ,
       BOM_DEPARTMENTS BD,
       WIP_OPERATIONS WO
WHERE  WO.ORGANIZATION_ID = :ORG_ID
AND    BD.ORGANIZATION_ID = :ORG_ID
AND    WE.ORGANIZATION_ID = :ORG_ID
AND    SG.SCHEDULE_GROUP_ID &P_SG_OUTER = DJ.SCHEDULE_GROUP_ID
AND    SG.ORGANIZATION_ID &P_SG_OUTER = DJ.ORGANIZATION_ID
AND    DJ.ORGANIZATION_ID = :ORG_ID
AND    SI.ORGANIZATION_ID (+) = :ORG_ID
&C_Date_Limiter
&C_Depart_Limiter
&C_Schedule_Group
AND    BD.DEPARTMENT_ID = WO.DEPARTMENT_ID
AND    WO.WIP_ENTITY_ID = DJ.WIP_ENTITY_ID
AND    WE.PRIMARY_ITEM_ID = SI.INVENTORY_ITEM_ID (+)
AND    WE.WIP_ENTITY_ID = DJ.WIP_ENTITY_ID
AND    WE.ENTITY_TYPE = 1
&C_Status_Limiter
AND    WO.SCHEDULED_QUANTITY > WO.QUANTITY_COMPLETED
&Order_By
Parameter Name SQL text Validation
Quantity Field Precision
 
Number
Org_Id
 
Number
Schedule Group
 
LOV Oracle
To
 
Date
Dates From
 
Date
To
 
LOV Oracle
Departments From
 
LOV Oracle
Include Unreleased Jobs
 
LOV Oracle
Sort By
 
LOV Oracle
Ask a question