ECC Project Manufacturing, Line Schedules

Categories: Enterprise Command Center
Imported from Enterprise Command Center
Description: Project Manufacturing Line Schedules
Dataset Key: pjm-line-schedules
Security Procedure: PJM_ECC_DATASECURITY_PKG_PUB.GetFilterAttributeValues

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Contact us to schedule a demo or if you need help with the installation

select * from ( SELECT    LS.ECC_SPEC_ID,    'PROJ_LS' AS RECORD_TYPE,    GREATEST(PP.LAST_UPDATE_DATE,PT.LAST_UPDATE_DATE,LS.LAST_UPDATE_DATE) AS ECC_LAST_UPDATE_DATE,    PP.PROJECT_ID AS PROJECT_ID,      PT.TASK_ID AS TASK_ID,       PP.SEGMENT1 AS PROJECT_NUMBER,    PP.ORG_ID AS ORG_ID,    PT.TASK_NUMBER AS TASK_NUMBER,    LS.LINE_ID AS LINE_ID,    LS.LINE_CODE AS LINE_CODE,    LS.PLANNED_QUANTITY AS PLANNED_QUANTITY,    LS.QUANTITY_COMPLETED AS QUANTITY_COMPLETED,    LS.VARIANCE_QUANTITY AS VARIANCE_QUANTITY,   LS.ORGANIZATION_ID AS ORGANIZATION_ID FROM PA_PROJECTS_ALL PP, PA_TASKS PT,    ( SELECT         'PROJ_LS' || '-' || PROJECT_ID || '-' || NVL(TO_CHAR(TASK_ID), 'NO_TASK') || '-' || LINE_ID AS ECC_SPEC_ID,         MAX(LAST_UPDATE_DATE) AS LAST_UPDATE_DATE,         PROJECT_ID AS PROJECT_ID,         TASK_ID AS TASK_ID, 	  	    ORGANIZATION_ID AS ORGANIZATION_ID,        LINE_ID AS LINE_ID,         LINE_CODE AS LINE_CODE,         SUM(NVL(PLANNED_QUANTITY, 0)) AS PLANNED_QUANTITY,         SUM(NVL(QUANTITY_COMPLETED, 0)) AS QUANTITY_COMPLETED,   	    SUM(NVL(PLANNED_QUANTITY, 0)) - SUM(NVL(QUANTITY_COMPLETED, 0)) AS VARIANCE_QUANTITY       FROM (  		  SELECT   job.wip_entity_id as wip_entity_id,		  			job.organization_id as organization_id,	  			line.line_id as line_id,			  			LINE.LINE_CODE as line_code,		  			pp1.PROJECT_ID as project_id, 		  			job.TASK_ID as task_id, 			  			JOB.START_QUANTITY AS planned_quantity,   			JOB.QUANTITY_COMPLETED,   			GREATEST( NVL( JOB.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY'))  				,NVL( LINE.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY'))  				,NVL( PP1.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY'))  				,NVL( PT1.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) ) AS LAST_UPDATE_DATE           FROM WIP_DISCRETE_JOBS JOB, WIP_LINES LINE,    		       pa_projects_all pp1, pa_tasks pt1               , ( select distinct project_id from pjm_project_parameters ) p31                          WHERE  		       PP1.PROJECT_TYPE <> 'AWARD_PROJECT'   		       AND PP1.TEMPLATE_FLAG = 'N'                and job.project_id = p31.project_id  		       and job.project_id = pp1.project_id     		       and job.task_id = pt1.task_id		  		       and pp1.project_id = pt1.project_id	  			   and pp1.start_date >= NVL(to_date('2021/12/12','RRRR/MM/DD HH24:MI:SS'), pp1.start_date)              AND JOB.LINE_ID = LINE.LINE_ID   		       and job.organization_id = line.organization_id  			               UNION ALL           SELECT   flow.wip_entity_id,		  			   flow.organization_id,	  			   line.LINE_ID,			  			   LINE.LINE_CODE, 		  			   PP2.PROJECT_ID, 		  			   flow.TASK_ID, 			  			   FLOW.PLANNED_QUANTITY,   			   FLOW.QUANTITY_COMPLETED,   			   GREATEST( NVL( FLOW.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY'))  				,NVL( LINE.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY'))  				,NVL( PP2.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY'))  				,NVL( PT2.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) ) AS LAST_UPDATE_DATE           FROM  WIP_FLOW_SCHEDULES FLOW,  WIP_LINES LINE ,  		        pa_projects_all pp2, pa_tasks pt2                , ( select distinct project_id from pjm_project_parameters ) p32                           WHERE PP2.PROJECT_TYPE <> 'AWARD_PROJECT'   		       AND PP2.TEMPLATE_FLAG = 'N'                and pp2.project_id = p32.project_id  		       and flow.project_id = pp2.project_id     		       and flow.task_id	= pt2.task_id   		       and pp2.project_id = pt2.project_id   			   and pp2.start_date >= NVL(to_date('2021/12/12','RRRR/MM/DD HH24:MI:SS'), pp2.start_date)              AND FLOW.LINE_ID = LINE.LINE_ID  		       and flow.organization_id = line.organization_id                           )         GROUP BY         	PROJECT_ID,         	TASK_ID,  	    	LINE_ID,  	    	organization_id,  	    	LINE_CODE    ) LS   WHERE PP.PROJECT_TYPE <> 'AWARD_PROJECT'     AND PP.TEMPLATE_FLAG = 'N'   	and pp.start_date >= NVL(to_date('2021/12/12','RRRR/MM/DD HH24:MI:SS'), pp.start_date)   AND PP.PROJECT_ID = LS.PROJECT_ID     AND PT.TASK_ID(+) = LS.TASK_ID  )  
) x
Parameter Name SQL text Validation
Operating Unit
x.org_id in (select haouv.organization_id from hr_all_organization_units_vl haouv where